[Home] [Help]
1214:
1215: --Initialize error message stack by default
1216: FND_MSG_PUB.Initialize;
1217:
1218: FND_FILE.put_line(FND_FILE.log, 'At the start of the PLSQL procedure UPDATE_REQUIREMENTS_FOR_GOP');
1219:
1220: --Get the profile value
1221: SELECT TO_NUMBER(FND_PROFILE.VALUE('AHL_GOP_ATP_PLANNING_WINDOW'))
1222: INTO l_planning_window
1223: FROM DUAL;
1224:
1225: --If Planning window is not defined, or if its null
1226: IF(l_planning_window IS NULL) THEN
1227: FND_FILE.put_line(FND_FILE.log, 'Planning Window is not defined');
1228: RAISE FND_API.G_EXC_ERROR;
1229: END IF;
1230:
1231: --Revert ATP_FLAG for demands falling outside planning window due to change in required date
1234: WHERE status = 'ACTIVE'
1235: AND requested_date > sysdate + l_planning_window
1236: AND ATP_FLAG = 'Y';
1237:
1238: FND_FILE.put_line(FND_FILE.log, 'Number of demands whose ATP_FLAG is reverted back to N:' || SQL%ROWCOUNT);
1239:
1240: --Iterate through the demands falling within the planning window
1241: FOR l_demand_rec IN get_demand_details(l_planning_window)
1242: LOOP
1242: LOOP
1243: --Update the flag only if it is not updated already
1244: IF(l_demand_rec.atp_flag IS NULL OR l_demand_rec.atp_flag <> 'Y') THEN
1245:
1246: FND_FILE.put_line(FND_FILE.log,
1247: 'Updating ATP flag for ASM ID: ' || l_demand_rec.scheduled_material_id);
1248:
1249: --Update ATP flag of this demand row
1250: UPDATE ahl_schedule_materials
1264:
1265: --Call SCHEDULE_MATERIALS in buckets, say 1000
1266: IF (i = l_bucket_size) THEN
1267: --Call API to schedule material requirement for the same
1268: FND_FILE.put_line(FND_FILE.log, 'Calling schedule_material API for a set of ' || l_bucket_size || ' demands');
1269:
1270: AHL_MM_MTL_MGT_PVT.SCHEDULE_MATERIALS(p_api_version => 1.0
1271: , p_init_msg_list => FND_API.G_FALSE
1272: , p_commit => FND_API.G_FALSE
1278: , x_msg_count => l_msg_count
1279: , x_msg_data => l_msg_data
1280: );
1281: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1282: fnd_file.put_line(fnd_file.log, 'Error after calling schedule_material API');
1283: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1284: RAISE FND_API.G_EXC_ERROR;
1285: ELSE
1286: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1290: FOR j in l_x_mtl_avl_schld_tbl.FIRST..l_x_mtl_avl_schld_tbl.LAST
1291: LOOP
1292: --Log only failed cases
1293: IF (l_x_mtl_avl_schld_tbl(j).ERROR_CODE <> 0) THEN
1294: fnd_file.put_line(fnd_file.log, 'Workorder: ' || l_wo_name_tbl(k)
1295: || ', Available Qty: ' || l_x_mtl_avl_schld_tbl(j).AVAILABLE_QUANTITY
1296: || ', Exception Date: ' || l_x_mtl_avl_schld_tbl(j).AVL_SCH_DATE
1297: || ', Error Code: ' || l_x_mtl_avl_schld_tbl(j).ERROR_CODE
1298: || ', Error Message: ' || l_x_mtl_avl_schld_tbl(j).ERROR_MESSAGE);
1325: --Perform the scheduling for last set of demands, if any
1326: IF(l_p_sch_mtl_id_tbl.COUNT > 0) THEN
1327:
1328: --Call API to schedule material requirement for the same
1329: FND_FILE.put_line(FND_FILE.log, 'Calling schedule_material API for last set of demands');
1330:
1331: AHL_MM_MTL_MGT_PVT.SCHEDULE_MATERIALS(p_api_version => 1.0
1332: , p_init_msg_list => FND_API.G_FALSE
1333: , p_commit => FND_API.G_FALSE
1340: , x_msg_data => l_msg_data
1341: );
1342:
1343: IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1344: fnd_file.put_line(fnd_file.log, 'Error after calling schedule_material API');
1345: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1346: RAISE FND_API.G_EXC_ERROR;
1347: ELSE
1348: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1352: FOR j in l_x_mtl_avl_schld_tbl.FIRST..l_x_mtl_avl_schld_tbl.LAST
1353: LOOP
1354: --Log only failed cases
1355: IF (l_x_mtl_avl_schld_tbl(j).ERROR_CODE <> 0) THEN
1356: fnd_file.put_line(fnd_file.log, 'Workorder: ' || l_wo_name_tbl(k)
1357: || ', Available Qty: ' || l_x_mtl_avl_schld_tbl(j).AVAILABLE_QUANTITY
1358: || ', Exception Date: ' || l_x_mtl_avl_schld_tbl(j).AVL_SCH_DATE
1359: || ', Error Code: ' || l_x_mtl_avl_schld_tbl(j).ERROR_CODE
1360: || ', Error Message: ' || l_x_mtl_avl_schld_tbl(j).ERROR_MESSAGE);
1370: ELSE
1371: ROLLBACK TO FLAG_REQUIREMENTS_SP;
1372: END IF;
1373:
1374: fnd_file.put_line(fnd_file.log, 'At the end of PL SQL procedure UPDATE_REQUIREMENTS_FOR_GOP');
1375:
1376: EXCEPTION
1377: WHEN FND_API.G_EXC_ERROR THEN
1378: ROLLBACK TO FLAG_REQUIREMENTS_SP;
1386: p_encoded => FND_API.G_FALSE,
1387: p_data => l_err_msg,
1388: p_msg_index_out => l_msg_index_out);
1389:
1390: fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
1391: END LOOP;
1392: END IF;
1393:
1394: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1403: p_encoded => FND_API.G_FALSE,
1404: p_data => l_err_msg,
1405: p_msg_index_out => l_msg_index_out);
1406:
1407: fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
1408: END LOOP;
1409: END IF;
1410:
1411: WHEN OTHERS THEN
1425: p_encoded => FND_API.G_FALSE,
1426: p_data => l_err_msg,
1427: p_msg_index_out => l_msg_index_out);
1428:
1429: fnd_file.put_line(FND_FILE.LOG, 'Error message-'||l_msg_index_out||':' || l_err_msg);
1430: END LOOP;
1431: END IF;
1432:
1433: END UPDATE_REQUIREMENTS_FOR_GOP;