[Home] [Help]
9: ,p_recipe_rout_resc_tbl IN gmd_recipe_fetch_pub.oprn_resc_tbl
10: ,p_resc_parameters_tbl IN gmd_recipe_fetch_pub.recp_resc_proc_param_tbl
11: ,p_recipe_rout_matl_tbl IN gmd_recipe_fetch_pub.recipe_rout_matl_tbl
12: ,p_routing_depd_tbl IN gmd_recipe_fetch_pub.routing_depd_tbl
13: ,p_gme_batch_header_rec IN gme_batch_header%ROWTYPE
14: ,p_use_workday_cal IN VARCHAR2
15: ,p_contiguity_override IN VARCHAR2
16: ,x_return_status OUT NOCOPY VARCHAR2
17: ,p_ignore_qty_below_cap IN VARCHAR2
45: INDEX BY BINARY_INTEGER;
46:
47: i PLS_INTEGER;
48: j PLS_INTEGER;
49: l_batch_id gme_batch_header.batch_id%TYPE;
50: l_step_tbl gmd_auto_step_calc.step_rec_tbl;
51: l_batchstep_id gme_batch_steps.batchstep_id%TYPE;
52: l_last_batchstep_id gme_batch_steps.batchstep_id%TYPE;
53: l_qty gme_batch_steps.plan_step_qty%TYPE;
99: l_step_plan_start_date DATE;
100: l_step_plan_cmplt_date DATE;
101: l_rsrc_start_date DATE;
102: l_rsrc_cmplt_date DATE;
103: l_gme_batch_header gme_batch_header%ROWTYPE;
104:
105: CURSOR cur_get_batch_steps (v_batch_id NUMBER, v_count NUMBER DEFAULT 0)
106: IS
107: SELECT batchstep_id, plan_step_qty, plan_charges
139: FROM gmd_recipe_validity_rules
140: WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
141:
142: CURSOR cur_step_dates (
143: v_batch_id gme_batch_header.batch_id%TYPE
144: ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
145: IS
146: SELECT plan_start_date, plan_cmplt_date
147: FROM gme_batch_steps
147: FROM gme_batch_steps
148: WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
149:
150: CURSOR cur_step_plan_cmplt_date (
151: v_batch_id gme_batch_header.batch_id%TYPE
152: ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
153: IS
154: SELECT plan_cmplt_date
155: FROM gme_batch_steps
189: gme_debug.put_line ('BEGIN Create_step');
190: END IF;
191:
192: x_return_status := fnd_api.g_ret_sts_success;
193: l_batch_id := p_gme_batch_header_rec.batch_id;
194:
195: IF (p_gme_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_batch) THEN
196: l_doc_type := 'PROD';
197: ELSE
191:
192: x_return_status := fnd_api.g_ret_sts_success;
193: l_batch_id := p_gme_batch_header_rec.batch_id;
194:
195: IF (p_gme_batch_header_rec.batch_type = gme_common_pvt.g_doc_type_batch) THEN
196: l_doc_type := 'PROD';
197: ELSE
198: l_doc_type := 'FPO';
199: END IF;
197: ELSE
198: l_doc_type := 'FPO';
199: END IF;
200:
201: OPEN cur_recipe_id (p_gme_batch_header_rec.recipe_validity_rule_id);
202:
203: FETCH cur_recipe_id
204: INTO l_recipe_id;
205:
256: l_gme_batch_steps (j).plan_start_date := gme_common_pvt.g_timestamp;
257: l_gme_batch_steps (j).plan_cmplt_date := gme_common_pvt.g_timestamp;
258: l_gme_batch_steps (j).plan_charges := 0;
259:
260: IF p_gme_batch_header_rec.enforce_step_dependency = 1 THEN
261: l_gme_batch_steps (j).steprelease_type := 1;
262: ELSE
263: l_gme_batch_steps (j).steprelease_type :=
264: p_recipe_rout_step_tbl (i).steprelease_type;
308: l_gme_batch_steps (j).text_code := l_gme_text_code;
309: END IF;
310: END IF;
311:
312: gmd_gme_int.check_qc(p_routingid => p_gme_batch_header_rec.routing_id,
313: p_routingstepid => l_gme_batch_steps(j).routingstep_id,
314: p_recipeid => l_recipe_id,
315: p_organization_id => p_gme_batch_header_rec.organization_id,
316: p_resultout => l_qc_status);
311:
312: gmd_gme_int.check_qc(p_routingid => p_gme_batch_header_rec.routing_id,
313: p_routingstepid => l_gme_batch_steps(j).routingstep_id,
314: p_recipeid => l_recipe_id,
315: p_organization_id => p_gme_batch_header_rec.organization_id,
316: p_resultout => l_qc_status);
317: IF (l_qc_status = 'S') THEN
318: l_gme_batch_steps(j).quality_status := 2;
319: END IF;
497: END IF;
498:
499: l_gme_batch_step_resources (j).batch_id := l_batch_id;
500: l_gme_batch_step_resources (j).organization_id :=
501: p_gme_batch_header_rec.organization_id;
502: l_gme_batch_step_resources (j).resources :=
503: p_recipe_rout_resc_tbl (i).resources;
504: l_gme_batch_step_resources (j).cost_analysis_code :=
505: p_recipe_rout_resc_tbl (i).cost_analysis_code;
968: -- ALSO, don't need to call auto_step_calc, because there are no dependencies tied in with this step... and there
969: -- are no item_step_associations either... so do nothing...
970: NULL;
971: ELSE
972: IF (p_gme_batch_header_rec.automatic_step_calculation = 1) THEN
973: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
974: gme_debug.put_line ('auto step calc for batch id '||l_batch_id);
975: gme_debug.put_line ('CALLING gmd_auto_step_calc.calc_step_qty. Org is '||p_gme_batch_header_rec.organization_id);
976: END IF;
971: ELSE
972: IF (p_gme_batch_header_rec.automatic_step_calculation = 1) THEN
973: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
974: gme_debug.put_line ('auto step calc for batch id '||l_batch_id);
975: gme_debug.put_line ('CALLING gmd_auto_step_calc.calc_step_qty. Org is '||p_gme_batch_header_rec.organization_id);
976: END IF;
977:
978: gmd_auto_step_calc.calc_step_qty
979: (p_parent_id => l_batch_id
981: ,p_msg_count => l_msg_count
982: ,p_msg_stack => l_msg_stack
983: ,p_return_status => l_return_status
984: ,p_called_from_batch => 1
985: ,p_organization_id => p_gme_batch_header_rec.organization_id);
986: ELSE
987: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
988: gme_debug.put_line ('NO auto step calc...');
989: gme_debug.put_line ('CALLING calc_step_qty ...');
1185: ,plan_rsrc_qty =
1186: ROUND (plan_rsrc_qty * l_activity_factor, 32)
1187: WHERE batchstep_activity_id = l_batchstep_activity_id;
1188:
1189: IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
1190: FOR l_rec IN cur_get_resources (l_batchstep_activity_id) LOOP
1191: UPDATE gme_resource_txns
1192: SET resource_usage =
1193: ROUND ( l_rec.plan_rsrc_usage
1197: AND doc_type = l_doc_type
1198: AND line_id = l_rec.batchstep_resource_id;
1199: END LOOP; /* FOR resources */
1200: END IF;
1201: /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
1202:
1203: FETCH cur_get_step_activities
1204: INTO l_batchstep_activity_id, l_activity_factor;
1205: END LOOP; /* WHILE cur_get_step_activities%FOUND */
1270: ,plan_rsrc_qty =
1271: ROUND (plan_rsrc_qty * l_activity_factor, 32)
1272: WHERE batchstep_activity_id = l_batchstep_activity_id;
1273:
1274: IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
1275: FOR l_rec IN cur_get_resources (l_batchstep_activity_id) LOOP
1276: UPDATE gme_resource_txns
1277: SET resource_usage =
1278: ROUND ( l_rec.plan_rsrc_usage
1282: AND doc_type = l_doc_type
1283: AND line_id = l_rec.batchstep_resource_id;
1284: END LOOP; /* FOR resources */
1285: END IF;
1286: /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
1287:
1288: FETCH cur_get_activities
1289: INTO l_batchstep_activity_id, l_activity_factor;
1290: END LOOP; /* WHILE cur_get_activities%FOUND */
1317:
1318: IF ( p_recipe_rout_step_tbl.COUNT = 1
1319: AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
1320: gme_create_step_pvt.calc_dates
1321: (p_gme_batch_header_rec => p_gme_batch_header_rec
1322: ,p_use_workday_cal => p_use_workday_cal
1323: ,p_contiguity_override => p_contiguity_override
1324: ,p_return_status => l_return_status
1325: ,p_step_id => l_last_batchstep_id
1330: RAISE error_calc_dates;
1331: END IF;
1332: ELSE
1333: gme_create_step_pvt.calc_dates
1334: (p_gme_batch_header_rec => p_gme_batch_header_rec
1335: ,p_use_workday_cal => p_use_workday_cal
1336: ,p_contiguity_override => p_contiguity_override
1337: ,p_return_status => l_return_status);
1338: END IF;
1342: END IF;
1343:
1344: IF ( p_recipe_rout_step_tbl.COUNT = 1
1345: AND p_recipe_rout_step_tbl (1).routingstep_id IS NULL) THEN
1346: OPEN cur_step_dates (p_gme_batch_header_rec.batch_id
1347: ,l_last_batchstep_id);
1348:
1349: FETCH cur_step_dates
1350: INTO l_step_plan_start_date, l_step_plan_cmplt_date;
1367: || TO_CHAR (p_step_cmplt_date
1368: ,'DD-MON-YYYY HH24:MI:SS') );
1369: gme_debug.put_line
1370: ( 'start,cmplt dates for batch are'
1371: || TO_CHAR (p_gme_batch_header_rec.plan_start_date
1372: ,'DD-MON-YYYY HH24:MI:SS')
1373: || TO_CHAR (p_gme_batch_header_rec.plan_cmplt_date
1374: ,'DD-MON-YYYY HH24:MI:SS') );
1375: END IF;
1369: gme_debug.put_line
1370: ( 'start,cmplt dates for batch are'
1371: || TO_CHAR (p_gme_batch_header_rec.plan_start_date
1372: ,'DD-MON-YYYY HH24:MI:SS')
1373: || TO_CHAR (p_gme_batch_header_rec.plan_cmplt_date
1374: ,'DD-MON-YYYY HH24:MI:SS') );
1375: END IF;
1376:
1377: IF (p_step_cmplt_date IS NOT NULL)
1386: SET plan_cmplt_date = p_step_cmplt_date
1387: ,last_updated_by = gme_common_pvt.g_user_ident
1388: ,last_update_date = gme_common_pvt.g_timestamp
1389: ,last_update_login = gme_common_pvt.g_login_id
1390: WHERE batch_id = p_gme_batch_header_rec.batch_id
1391: AND batchstep_id = l_last_batchstep_id;
1392: ELSIF (p_step_cmplt_date < l_step_plan_cmplt_date) THEN
1393: l_gme_batch_header.batch_id := p_gme_batch_header_rec.batch_id;
1394: l_gme_batch_header.batch_type :=
1389: ,last_update_login = gme_common_pvt.g_login_id
1390: WHERE batch_id = p_gme_batch_header_rec.batch_id
1391: AND batchstep_id = l_last_batchstep_id;
1392: ELSIF (p_step_cmplt_date < l_step_plan_cmplt_date) THEN
1393: l_gme_batch_header.batch_id := p_gme_batch_header_rec.batch_id;
1394: l_gme_batch_header.batch_type :=
1395: p_gme_batch_header_rec.batch_type;
1396: l_gme_batch_header.plan_cmplt_date := p_step_cmplt_date;
1397: gme_reschedule_batch_pvt.truncate_date
1390: WHERE batch_id = p_gme_batch_header_rec.batch_id
1391: AND batchstep_id = l_last_batchstep_id;
1392: ELSIF (p_step_cmplt_date < l_step_plan_cmplt_date) THEN
1393: l_gme_batch_header.batch_id := p_gme_batch_header_rec.batch_id;
1394: l_gme_batch_header.batch_type :=
1395: p_gme_batch_header_rec.batch_type;
1396: l_gme_batch_header.plan_cmplt_date := p_step_cmplt_date;
1397: gme_reschedule_batch_pvt.truncate_date
1398: (p_batch_header_rec => l_gme_batch_header
1391: AND batchstep_id = l_last_batchstep_id;
1392: ELSIF (p_step_cmplt_date < l_step_plan_cmplt_date) THEN
1393: l_gme_batch_header.batch_id := p_gme_batch_header_rec.batch_id;
1394: l_gme_batch_header.batch_type :=
1395: p_gme_batch_header_rec.batch_type;
1396: l_gme_batch_header.plan_cmplt_date := p_step_cmplt_date;
1397: gme_reschedule_batch_pvt.truncate_date
1398: (p_batch_header_rec => l_gme_batch_header
1399: ,p_date => 1
1392: ELSIF (p_step_cmplt_date < l_step_plan_cmplt_date) THEN
1393: l_gme_batch_header.batch_id := p_gme_batch_header_rec.batch_id;
1394: l_gme_batch_header.batch_type :=
1395: p_gme_batch_header_rec.batch_type;
1396: l_gme_batch_header.plan_cmplt_date := p_step_cmplt_date;
1397: gme_reschedule_batch_pvt.truncate_date
1398: (p_batch_header_rec => l_gme_batch_header
1399: ,p_date => 1
1400: ,p_batchstep_id => l_last_batchstep_id
1394: l_gme_batch_header.batch_type :=
1395: p_gme_batch_header_rec.batch_type;
1396: l_gme_batch_header.plan_cmplt_date := p_step_cmplt_date;
1397: gme_reschedule_batch_pvt.truncate_date
1398: (p_batch_header_rec => l_gme_batch_header
1399: ,p_date => 1
1400: ,p_batchstep_id => l_last_batchstep_id
1401: ,x_return_status => l_return_status);
1402:
1417: || TO_CHAR (l_step_plan_cmplt_date, 'DD-MON-YYYY HH24:MI:SS') );
1418: END IF;
1419:
1420: --checking against the batch dates
1421: IF (l_step_plan_start_date < p_gme_batch_header_rec.plan_start_date) THEN
1422: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1423: gme_debug.put_line
1424: ('l_step_plan_start_date diff from batch start date');
1425: END IF;
1424: ('l_step_plan_start_date diff from batch start date');
1425: END IF;
1426:
1427: gme_reschedule_batch_pvt.truncate_date
1428: (p_batch_header_rec => p_gme_batch_header_rec
1429: ,p_date => 0
1430: ,p_batchstep_id => l_last_batchstep_id
1431: ,x_return_status => l_return_status);
1432:
1433: IF l_return_status <> x_return_status THEN
1434: RAISE error_truncate_date;
1435: END IF;
1436: END IF;
1437: /* (l_step_plan_start_date < p_gme_batch_header_rec.plan_start_date) */
1438:
1439: IF (l_step_plan_cmplt_date > p_gme_batch_header_rec.plan_cmplt_date) THEN
1440: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1441: gme_debug.put_line
1435: END IF;
1436: END IF;
1437: /* (l_step_plan_start_date < p_gme_batch_header_rec.plan_start_date) */
1438:
1439: IF (l_step_plan_cmplt_date > p_gme_batch_header_rec.plan_cmplt_date) THEN
1440: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1441: gme_debug.put_line
1442: ('l_step_plan_cmplt_date diff from batch cmplt date');
1443: END IF;
1442: ('l_step_plan_cmplt_date diff from batch cmplt date');
1443: END IF;
1444:
1445: gme_reschedule_batch_pvt.truncate_date
1446: (p_batch_header_rec => p_gme_batch_header_rec
1447: ,p_date => 1
1448: ,p_batchstep_id => l_last_batchstep_id
1449: ,x_return_status => l_return_status);
1450:
1451: IF l_return_status <> x_return_status THEN
1452: RAISE error_truncate_date;
1453: END IF;
1454: END IF;
1455: /* (l_step_plan_cmplt_date > p_gme_batch_header_rec.plan_cmplt_date) */
1456: END IF; /* IF (p_recipe_rout_step_tbl.COUNT = 1 AND */
1457:
1458: OPEN cur_get_batchstep_ids (p_gme_batch_header_rec.batch_id);
1459:
1454: END IF;
1455: /* (l_step_plan_cmplt_date > p_gme_batch_header_rec.plan_cmplt_date) */
1456: END IF; /* IF (p_recipe_rout_step_tbl.COUNT = 1 AND */
1457:
1458: OPEN cur_get_batchstep_ids (p_gme_batch_header_rec.batch_id);
1459:
1460: FETCH cur_get_batchstep_ids
1461: BULK COLLECT INTO l_batchstep_ids_tab;
1462:
1464:
1465: FOR i IN 1 .. l_batchstep_ids_tab.COUNT LOOP
1466: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1467: gme_debug.put_line ( 'batch,step ids are'
1468: || p_gme_batch_header_rec.batch_id
1469: || l_batchstep_ids_tab (i) );
1470: END IF;
1471:
1472: OPEN cur_is_charge_associated (p_gme_batch_header_rec.batch_id
1468: || p_gme_batch_header_rec.batch_id
1469: || l_batchstep_ids_tab (i) );
1470: END IF;
1471:
1472: OPEN cur_is_charge_associated (p_gme_batch_header_rec.batch_id
1473: ,l_batchstep_ids_tab (i) );
1474:
1475: FETCH cur_is_charge_associated
1476: INTO l_resources;
1478: IF cur_is_charge_associated%FOUND THEN
1479: CLOSE cur_is_charge_associated;
1480:
1481: OPEN cur_get_resource_dates (l_resources
1482: ,p_gme_batch_header_rec.batch_id
1483: ,l_batchstep_ids_tab (i) );
1484:
1485: FETCH cur_get_resource_dates
1486: INTO l_rsrc_start_date, l_rsrc_cmplt_date;
1498:
1499: UPDATE gme_batch_step_charges
1500: SET plan_start_date = l_rsrc_start_date
1501: ,plan_cmplt_date = l_rsrc_cmplt_date
1502: WHERE batch_id = p_gme_batch_header_rec.batch_id
1503: AND batchstep_id = l_batchstep_ids_tab (i);
1504: ELSE
1505: CLOSE cur_is_charge_associated;
1506: END IF;
1735: -- This PL/SQL function is responsible for calculating max step
1736: -- dates based on shop calendar or otherwise
1737: --
1738: -- REQUIREMENTS
1739: -- p_gme_batch_header_rec non null value.
1740: -- SYNOPSIS:
1741: --
1742: --===================================================================== */
1743: FUNCTION get_max_step_date (
2048: -- DESCRIPTION:
2049: -- This PL/SQL procedure is responsible for calculating dates for the batch
2050: --
2051: -- REQUIREMENTS
2052: -- p_gme_batch_header_rec non null value.
2053: -- SYNOPSIS:
2054: -- calc_dates (p_gme_batch_header_rec,p_use_workday_cal, X_return_status);
2055: -- -- Pawan Kumar bug 823188 added for shop calendar
2056: -- Added additional parameters for the shop calendar implementation.
2050: --
2051: -- REQUIREMENTS
2052: -- p_gme_batch_header_rec non null value.
2053: -- SYNOPSIS:
2054: -- calc_dates (p_gme_batch_header_rec,p_use_workday_cal, X_return_status);
2055: -- -- Pawan Kumar bug 823188 added for shop calendar
2056: -- Added additional parameters for the shop calendar implementation.
2057: --===================================================================== */
2058: PROCEDURE calc_dates (
2055: -- -- Pawan Kumar bug 823188 added for shop calendar
2056: -- Added additional parameters for the shop calendar implementation.
2057: --===================================================================== */
2058: PROCEDURE calc_dates (
2059: p_gme_batch_header_rec IN gme_batch_header%ROWTYPE
2060: ,p_use_workday_cal IN VARCHAR2
2061: ,p_contiguity_override IN VARCHAR2
2062: ,p_return_status OUT NOCOPY VARCHAR2
2063: ,p_step_id IN gme_batch_steps.batchstep_id%TYPE
2076: x_step_row gme_batch_steps%ROWTYPE;
2077: x_max_act_date DATE;
2078: x_max_rsrc_date DATE;
2079: l_return_status VARCHAR2 (1);
2080: x_routing_id gme_batch_header.routing_id%TYPE;
2081: x_gmd_step_tbl gmd_auto_step_calc.step_rec_tbl;
2082: l_batch_id gme_batch_header.batch_id%TYPE;
2083: x_batch_duration NUMBER;
2084: x_batch_start_date DATE;
2078: x_max_rsrc_date DATE;
2079: l_return_status VARCHAR2 (1);
2080: x_routing_id gme_batch_header.routing_id%TYPE;
2081: x_gmd_step_tbl gmd_auto_step_calc.step_rec_tbl;
2082: l_batch_id gme_batch_header.batch_id%TYPE;
2083: x_batch_duration NUMBER;
2084: x_batch_start_date DATE;
2085: x_step_start_date DATE;
2086: x_step_no gme_batch_steps.batchstep_no%TYPE;
2125: FROM gme_batch_step_activities
2126: WHERE batchstep_activity_id = v_batchstep_activity_id;
2127:
2128: CURSOR cur_recipe_validity_rule (
2129: p_recipe_validity_rule_id gme_batch_header.recipe_validity_rule_id%TYPE)
2130: IS
2131: SELECT inventory_item_id
2132: FROM gmd_recipe_validity_rules
2133: WHERE recipe_validity_rule_id = p_recipe_validity_rule_id;
2156: gme_debug.put_line ('p_contiguity_override is '||p_contiguity_override); -- Bug 13582990
2157: END IF;
2158:
2159: p_return_status := fnd_api.g_ret_sts_success;
2160: l_batch_id := p_gme_batch_header_rec.batch_id;
2161:
2162: IF (p_gme_batch_header_rec.batch_type = 0) THEN
2163: l_doc_type := 'PROD';
2164: ELSE
2158:
2159: p_return_status := fnd_api.g_ret_sts_success;
2160: l_batch_id := p_gme_batch_header_rec.batch_id;
2161:
2162: IF (p_gme_batch_header_rec.batch_type = 0) THEN
2163: l_doc_type := 'PROD';
2164: ELSE
2165: l_doc_type := 'FPO';
2166: END IF;
2176: IF l_calendar_code IS NULL THEN
2177: l_use_workday_cal := fnd_api.g_false;
2178: ELSE
2179: OPEN cur_recipe_validity_rule
2180: (p_gme_batch_header_rec.recipe_validity_rule_id);
2181:
2182: FETCH cur_recipe_validity_rule
2183: INTO l_item_id;
2184:
2242: (x_gmd_step_tbl (i).step_no) );
2243: END IF;
2244: END LOOP;
2245:
2246: x_batch_start_date := p_gme_batch_header_rec.plan_start_date;
2247: x_step_start_date := p_plan_start_date;
2248:
2249: IF (p_step_id IS NOT NULL) THEN
2250: -- This is a step insert... calc the start date with the cmplt date and duration...
2305: ,'DD-MON-YYYY HH24:MI:SS') );
2306: END IF;
2307: END IF;
2308: ELSE
2309: IF (p_gme_batch_header_rec.plan_start_date IS NOT NULL) THEN
2310: calc_longest_time (l_batch_id
2311: ,l_step_duration_tab
2312: ,x_batch_duration
2313: ,l_return_status);
2315: IF l_use_workday_cal = fnd_api.g_true THEN
2316: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2317: gme_debug.put_line ('calling get_contiguous_periods from point 2 calc_dates'); -- Bug 13582990
2318: gme_debug.put_line ('start date is not null used for START DATE parm is '||
2319: TO_CHAR(p_gme_batch_header_rec.plan_start_date,'DD-MON-YYYY HH24:MI:SS'));
2320: gme_debug.put_line ('duration passed in is '||x_batch_duration); -- Bug 13582990
2321: gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2322: END IF;
2323:
2323:
2324: gmp_calendar_api.get_contiguous_periods
2325: (p_api_version => 1
2326: ,p_init_msg_list => TRUE
2327: ,p_start_date => p_gme_batch_header_rec.plan_start_date
2328: ,p_end_date => NULL
2329: ,p_calendar_code => l_calendar_code
2330: ,p_duration => x_batch_duration
2331: ,p_output_tbl => l_contig_period_tbl
2347: END IF;
2348: END IF;
2349: END IF;
2350:
2351: x_batch_start_date := p_gme_batch_header_rec.plan_start_date;
2352: ELSIF (p_gme_batch_header_rec.plan_start_date IS NULL)
2353: AND (p_gme_batch_header_rec.plan_cmplt_date IS NOT NULL) THEN
2354: -- calc longest time for the entire batch if we're only given cmplt date...
2355: calc_longest_time (l_batch_id
2348: END IF;
2349: END IF;
2350:
2351: x_batch_start_date := p_gme_batch_header_rec.plan_start_date;
2352: ELSIF (p_gme_batch_header_rec.plan_start_date IS NULL)
2353: AND (p_gme_batch_header_rec.plan_cmplt_date IS NOT NULL) THEN
2354: -- calc longest time for the entire batch if we're only given cmplt date...
2355: calc_longest_time (l_batch_id
2356: ,l_step_duration_tab
2349: END IF;
2350:
2351: x_batch_start_date := p_gme_batch_header_rec.plan_start_date;
2352: ELSIF (p_gme_batch_header_rec.plan_start_date IS NULL)
2353: AND (p_gme_batch_header_rec.plan_cmplt_date IS NOT NULL) THEN
2354: -- calc longest time for the entire batch if we're only given cmplt date...
2355: calc_longest_time (l_batch_id
2356: ,l_step_duration_tab
2357: ,x_batch_duration
2361: gme_debug.put_line ('duration of batch is ' || x_batch_duration);
2362: gme_debug.put_line
2363: ( 'batch completion date is '
2364: || TO_CHAR
2365: (p_gme_batch_header_rec.plan_cmplt_date
2366: ,'DD-MON-YYYY HH24:MI:SS') );
2367: END IF;
2368:
2369: IF l_use_workday_cal = fnd_api.g_true THEN
2370: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2371: gme_debug.put_line ('calling get_contiguous_periods from point 3 calc_dates'); -- Bug 13582990
2372: -- gme_debug.put_line ('end date null ' || l_use_workday_cal);
2373: gme_debug.put_line ('plan_cmplt_date is not null used for END DATE parm is '||
2374: TO_CHAR(p_gme_batch_header_rec.plan_cmplt_date,'DD-MON-YYYY HH24:MI:SS'));
2375: gme_debug.put_line ('duration passed in is '||x_batch_duration); -- Bug 13582990
2376: gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2377: END IF;
2378:
2379: gmp_calendar_api.get_contiguous_periods
2380: (p_api_version => 1
2381: ,p_init_msg_list => TRUE
2382: ,p_start_date => NULL
2383: ,p_end_date => p_gme_batch_header_rec.plan_cmplt_date
2384: ,p_calendar_code => l_calendar_code
2385: ,p_duration => x_batch_duration
2386: ,p_output_tbl => l_contig_period_tbl
2387: ,x_return_status => l_return_status);
2405: x_batch_start_date :=
2406: l_contig_period_tbl (l_cal_count).start_date;
2407: ELSE
2408: x_batch_start_date :=
2409: p_gme_batch_header_rec.plan_cmplt_date
2410: - x_batch_duration / 24;
2411: END IF;
2412:
2413: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2414: gme_debug.put_line ( 'calculated batch start date is '
2415: || TO_CHAR (x_batch_start_date
2416: ,'DD-MON-YYYY HH24:MI:SS') );
2417: END IF;
2418: ELSIF (p_gme_batch_header_rec.plan_start_date IS NULL)
2419: AND (p_gme_batch_header_rec.plan_cmplt_date IS NULL) THEN
2420: calc_longest_time (l_batch_id
2421: ,l_step_duration_tab
2422: ,x_batch_duration
2415: || TO_CHAR (x_batch_start_date
2416: ,'DD-MON-YYYY HH24:MI:SS') );
2417: END IF;
2418: ELSIF (p_gme_batch_header_rec.plan_start_date IS NULL)
2419: AND (p_gme_batch_header_rec.plan_cmplt_date IS NULL) THEN
2420: calc_longest_time (l_batch_id
2421: ,l_step_duration_tab
2422: ,x_batch_duration
2423: ,l_return_status);
2426: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2427: gme_debug.put_line ('calling get_contiguous_periods from point 4 calc_dates'); -- Bug 13582990
2428: gme_debug.put_line ('Both dates null. p_contiguity_override is '|| p_contiguity_override); -- Bug 13582990
2429: gme_debug.put_line ('batch completion is '||
2430: TO_CHAR(p_gme_batch_header_rec.plan_cmplt_date,'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
2431: gme_debug.put_line ('start date passed in gme_common_pvt.g_timestamp is '||
2432: TO_CHAR(gme_common_pvt.g_timestamp,'DD-MON-YYYY HH24:MI:SS')); -- Bug 13582990
2433: gme_debug.put_line ('duration passed in is '||x_batch_duration); -- Bug 13582990
2434: gme_debug.put_line ('p_calendar_code passed in is '||l_calendar_code); -- Bug 13582990
2572: l_batchstep_activity_id := x_act_tab (j).batchstep_activity_id;
2573: RAISE no_resources;
2574: END IF;
2575:
2576: IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
2577: x_rsrc_txns_tab.DELETE;
2578: x_cur_row := 0;
2579: END IF;
2580: /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
2576: IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
2577: x_rsrc_txns_tab.DELETE;
2578: x_cur_row := 0;
2579: END IF;
2580: /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
2581:
2582: FOR k IN x_rsrc_tab.FIRST .. x_rsrc_tab.LAST LOOP
2583: IF l_use_workday_cal = fnd_api.g_true THEN
2584: x_rsrc_tab (k).plan_start_date :=
2648:
2649: x_rsrc_tab (k).plan_cmplt_date :=
2650: l_contig_period_tbl (l_cal_count).end_date;
2651:
2652: IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
2653: FOR m IN 1 .. l_cal_count LOOP
2654:
2655: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2656: gme_debug.put_line ('rsrc loop iteration is '||m); -- Bug 13582990
2668:
2669: -- Bug 9212573 - Now pass parameter p_trans_count. This will tell
2670: -- underlying routine if there is only one transaction required and
2671: -- therefore it can derive the txn usage with a simpler algorithm.
2672: insert_resource_txns (p_gme_batch_header_rec => p_gme_batch_header_rec
2673: ,p_doc_type => l_doc_type
2674: ,p_batch_step_resources_rec => x_rsrc_tab (k)
2675: ,p_trans_count => l_cal_count
2676: ,x_return_status => l_return_status);
2673: ,p_doc_type => l_doc_type
2674: ,p_batch_step_resources_rec => x_rsrc_tab (k)
2675: ,p_trans_count => l_cal_count
2676: ,x_return_status => l_return_status);
2677: /* insert_resource_txns (p_gme_batch_header_rec
2678: ,l_doc_type
2679: ,x_rsrc_tab (k)
2680: ,l_return_status); */
2681:
2687: x_rsrc_tab (k).plan_start_date :=
2688: l_contig_period_tbl (1).start_date;
2689: END IF;
2690:
2691: /* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
2692: IF k = x_rsrc_tab.FIRST
2693: OR x_rsrc_tab (k).plan_cmplt_date > x_max_rsrc_date THEN
2694: x_max_rsrc_date := x_rsrc_tab (k).plan_cmplt_date;
2695: END IF;
2755: INTO min_start_date
2756: FROM gme_batch_steps
2757: WHERE batch_id = l_batch_id;
2758:
2759: IF p_gme_batch_header_rec.batch_status = 2 THEN
2760: -- Pawan Kumar made following changes for bug 5015873
2761: -- min_start_date := p_gme_batch_header_rec.plan_start_date;
2762: UPDATE gme_batch_header
2763: SET plan_cmplt_date = max_cmplt_date
2757: WHERE batch_id = l_batch_id;
2758:
2759: IF p_gme_batch_header_rec.batch_status = 2 THEN
2760: -- Pawan Kumar made following changes for bug 5015873
2761: -- min_start_date := p_gme_batch_header_rec.plan_start_date;
2762: UPDATE gme_batch_header
2763: SET plan_cmplt_date = max_cmplt_date
2764: ,last_updated_by = gme_common_pvt.g_user_ident
2765: ,last_update_date = gme_common_pvt.g_timestamp
2758:
2759: IF p_gme_batch_header_rec.batch_status = 2 THEN
2760: -- Pawan Kumar made following changes for bug 5015873
2761: -- min_start_date := p_gme_batch_header_rec.plan_start_date;
2762: UPDATE gme_batch_header
2763: SET plan_cmplt_date = max_cmplt_date
2764: ,last_updated_by = gme_common_pvt.g_user_ident
2765: ,last_update_date = gme_common_pvt.g_timestamp
2766: ,last_update_login = gme_common_pvt.g_login_id
2765: ,last_update_date = gme_common_pvt.g_timestamp
2766: ,last_update_login = gme_common_pvt.g_login_id
2767: WHERE batch_id = l_batch_id;
2768: ELSE
2769: UPDATE gme_batch_header
2770: SET plan_start_date = min_start_date
2771: ,plan_cmplt_date = max_cmplt_date
2772: ,last_updated_by = gme_common_pvt.g_user_ident
2773: ,last_update_date = gme_common_pvt.g_timestamp
2774: ,last_update_login = gme_common_pvt.g_login_id
2775: WHERE batch_id = l_batch_id;
2776: END IF;
2777:
2778: /* UPDATE gme_batch_header
2779: SET plan_start_date = min_start_date
2780: ,plan_cmplt_date = max_cmplt_date
2781: ,last_updated_by = gme_common_pvt.g_user_ident
2782: ,last_update_date = gme_common_pvt.g_timestamp
2782: ,last_update_date = gme_common_pvt.g_timestamp
2783: ,last_update_login = gme_common_pvt.g_login_id
2784: WHERE batch_id = l_batch_id;*/
2785:
2786: UPDATE gme_batch_header
2787: SET due_date = max_cmplt_date
2788: ,last_updated_by = gme_common_pvt.g_user_ident
2789: ,last_update_date = gme_common_pvt.g_timestamp
2790: ,last_update_login = gme_common_pvt.g_login_id
2876: -- Renamed this r 12 function with "_orig". Hierarchical query does not handle data
2877: -- where there are no step dependencies. If that is figured out someday, this code
2878: -- could be reinstated.
2879: --================================================================================= */
2880: PROCEDURE calc_longest_time_orig(l_batch_id IN gme_batch_header.batch_id%TYPE,
2881: l_step_duration_tab IN step_duration_tab,
2882: x_batch_duration OUT NOCOPY NUMBER,
2883: x_return_status OUT NOCOPY VARCHAR2) IS
2884:
3025: -- Renamed this r 12 function with "_orig". Hierarchical query does not handle data
3026: -- where there are no step dependencies.
3027: --================================================================================= */
3028: PROCEDURE calc_longest_time (
3029: l_batch_id IN gme_batch_header.batch_id%TYPE,
3030: l_step_duration_tab IN step_duration_tab,
3031: x_batch_duration OUT NOCOPY NUMBER,
3032: x_return_status OUT NOCOPY VARCHAR2
3033: ) IS
3344: ,p_return_status OUT NOCOPY VARCHAR2
3345: ,p_called_from_batch IN NUMBER DEFAULT 1)
3346: IS
3347: x_step_rows NUMBER;
3348: x_routing_id gme_batch_header.routing_id%TYPE;
3349: x_step_qty NUMBER;
3350: x_new_factor NUMBER;
3351: x_uom_class mtl_units_of_measure.uom_class%TYPE;
3352: x_scale_factor NUMBER;
3792: RETURN 0;
3793: END get_max_duration;
3794:
3795: PROCEDURE insert_resource_txns (
3796: p_gme_batch_header_rec IN gme_batch_header%ROWTYPE
3797: ,p_doc_type IN VARCHAR2
3798: ,p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE
3799: ,p_trans_count IN NUMBER
3800: ,x_return_status OUT NOCOPY VARCHAR2)
3836: p_batch_step_resources_rec.plan_rsrc_usage
3837: / p_batch_step_resources_rec.plan_rsrc_count;
3838: END IF;
3839:
3840: IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
3841: l_gme_resource_txns.doc_id := p_batch_step_resources_rec.batch_id;
3842: l_gme_resource_txns.doc_type := p_doc_type;
3843: l_gme_resource_txns.organization_id :=
3844: p_gme_batch_header_rec.organization_id;
3840: IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
3841: l_gme_resource_txns.doc_id := p_batch_step_resources_rec.batch_id;
3842: l_gme_resource_txns.doc_type := p_doc_type;
3843: l_gme_resource_txns.organization_id :=
3844: p_gme_batch_header_rec.organization_id;
3845: l_gme_resource_txns.line_type := 0;
3846: l_gme_resource_txns.line_id :=
3847: p_batch_step_resources_rec.batchstep_resource_id;
3848: l_gme_resource_txns.resources :=