[Home] [Help]
27: );
28: END DEBUG;
29:
30:
31: --This program populates the WMS_ELS_EXP_RESOURCE table, which is the base requirement
32: --for Expected Resource Requirements Analysis . WMS_ELS_EXP_RESOURCE table essentially
33: --has all the information for all future work that is expected in a Warehouse whether
34: --it is an Inbound, Outbound, Warehousing or Manufacturing activity. Based on this
35: --information the expected resource requirement will be calculated.
28: END DEBUG;
29:
30:
31: --This program populates the WMS_ELS_EXP_RESOURCE table, which is the base requirement
32: --for Expected Resource Requirements Analysis . WMS_ELS_EXP_RESOURCE table essentially
33: --has all the information for all future work that is expected in a Warehouse whether
34: --it is an Inbound, Outbound, Warehousing or Manufacturing activity. Based on this
35: --information the expected resource requirement will be calculated.
36: --The following is list if the inputs for populating this table.
81: IF g_debug=1 THEN
82: debug('Before populating work for Inbound(PO,REQ,SHIPMENT) ','POPULATE_EXPECTED_WORK');
83: END IF;
84:
85: INSERT INTO WMS_ELS_EXP_RESOURCE
86: (els_exp_resource_id ,
87: organization_id,
88: activity_id,
89: activity_detail_id,
103: created_by,
104: creation_Date
105: )
106: select
107: WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
108: to_organization_id,
109: 1,--Inbound
110: 1,--Recieve
111: 1,--Reciept
172: END IF;
173:
174: l_num_rows_inserted := 0;
175:
176: insert into WMS_ELS_EXP_RESOURCE
177: (
178: els_exp_resource_id,
179: organization_id,
180: activity_id,
194: last_update_login,
195: created_by,
196: creation_Date
197: )
198: select wms_els_exp_resource_s.nextval,
199: ship_from_org_id,
200: 1, -- Inbound
201: 1, -- Recieving
202: 1, -- Reciept
260: END IF;
261:
262: l_num_rows_inserted := 0;
263:
264: INSERT INTO WMS_ELS_EXP_RESOURCE
265: (els_exp_resource_id ,
266: organization_id,
267: activity_id,
268: activity_detail_id,
279: created_by,
280: creation_Date
281: )
282: select
283: WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
284: organization_id,
285: 1,--Inbound
286: 2,--Putaway
287: 3,--Drop
333: END IF;
334:
335: l_num_rows_inserted := 0;
336:
337: INSERT INTO WMS_ELS_EXP_RESOURCE
338: (els_exp_resource_id ,
339: organization_id,
340: activity_id,
341: activity_detail_id,
352: created_by,
353: creation_Date
354: )
355: select
356: WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
357: organization_id,
358: 1,--Inbound
359: 2,--Putaway
360: 3,--Drop
406: END IF;
407:
408: l_num_rows_inserted := 0;
409:
410: INSERT INTO WMS_ELS_EXP_RESOURCE
411: (els_exp_resource_id ,
412: organization_id,
413: activity_id,
414: activity_detail_id,
425: created_by,
426: creation_Date
427: )
428: select
429: WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
430: organization_id,
431: 1,--Inbound
432: 2,--Putaway
433: 3,--Drop
479: END IF;
480:
481: l_num_rows_inserted := 0;
482:
483: INSERT INTO WMS_ELS_EXP_RESOURCE
484: (els_exp_resource_id ,
485: organization_id,
486: activity_id,
487: activity_detail_id,
498: created_by,
499: creation_Date
500: )
501: select
502: WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
503: organization_id,
504: 1,--Inbound
505: 2,--Putaway
506: 3,--Drop
553: END IF;
554:
555: l_num_rows_inserted := 0;
556:
557: INSERT INTO WMS_ELS_EXP_RESOURCE
558: (els_exp_resource_id ,
559: organization_id,
560: activity_id,
561: activity_detail_id,
571: created_by,
572: creation_Date
573: )
574: select
575: WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
576: organization_id,
577: 2,-- Manufacturing
578: 2,-- putaway
579: 3,--DROP
625: l_num_rows_inserted := 0;
626: -- removed the join with mtl_cc_schedule_requests because the
627: -- the counting tasks would be in pending state as soon as requests are generated.
628:
629: insert into WMS_ELS_EXP_RESOURCE
630: ( els_exp_resource_id ,
631: organization_id,
632: activity_id,
633: activity_detail_id,
644: last_update_login,
645: created_by,
646: creation_Date
647: )
648: select wms_els_exp_resource_s.nextval,
649: mcce.organization_id,
650: 4,-- Warehousing
651: 5,-- Counting
652: 4,--Count
701: END IF;
702:
703: l_num_rows_inserted := 0;
704:
705: insert into WMS_ELS_EXP_RESOURCE
706: ( els_exp_resource_id ,
707: organization_id,
708: activity_id,
709: activity_detail_id,
723: last_update_login,
724: created_by,
725: creation_Date
726: )
727: select wms_els_exp_resource_s.nextval,
728: mmtt.organization_id,
729: (CASE when (
730: ( Transaction_Type_Id = 52
731: and Transaction_Action_Id =28
941: END IF;
942:
943: l_num_rows_inserted := 0;
944:
945: insert into WMS_ELS_EXP_RESOURCE
946: ( els_exp_resource_id ,
947: organization_id,
948: activity_id,
949: activity_detail_id,
963: last_update_login,
964: created_by,
965: creation_Date
966: )
967: select wms_els_exp_resource_s.nextval,
968: mmtt.organization_id,
969: (CASE when (
970: ( Transaction_Type_Id = 52
971: and Transaction_Action_Id =28
1183: END IF;
1184:
1185: l_num_rows_inserted := 0;
1186:
1187: insert into WMS_ELS_EXP_RESOURCE
1188: ( els_exp_resource_id ,
1189: organization_id,
1190: activity_id,
1191: activity_detail_id,
1205: last_update_login,
1206: created_by,
1207: creation_Date
1208: )
1209: select wms_els_exp_resource_s.nextval,
1210: mmtt.organization_id,
1211: (CASE when (
1212: ( Transaction_Type_Id = 52
1213: and Transaction_Action_Id =28
1394: END IF;
1395:
1396: l_num_rows_inserted := 0;
1397:
1398: insert into WMS_ELS_EXP_RESOURCE
1399: ( els_exp_resource_id ,
1400: organization_id,
1401: activity_id,
1402: activity_detail_id,
1416: last_update_login,
1417: created_by,
1418: creation_Date
1419: )
1420: select wms_els_exp_resource_s.nextval,
1421: mmtt.organization_id,
1422: (CASE when (
1423: ( Transaction_Type_Id = 52
1424: and Transaction_Action_Id =28
1591: END IF;
1592:
1593: l_num_rows_inserted := 0;
1594:
1595: insert into WMS_ELS_EXP_RESOURCE
1596: ( els_exp_resource_id ,
1597: organization_id,
1598: activity_id,
1599: activity_detail_id,
1613: last_update_login,
1614: created_by,
1615: creation_Date
1616: )
1617: select wms_els_exp_resource_s.nextval,
1618: mmtt2.organization_id,
1619: (CASE when (
1620: ( mmtt2.Transaction_Type_Id = 52
1621: and mmtt2.Transaction_Action_Id =28
1805: END IF;
1806:
1807: l_num_rows_inserted := 0;
1808:
1809: INSERT INTO WMS_ELS_EXP_RESOURCE
1810: (els_exp_resource_id ,
1811: organization_id,
1812: activity_id,
1813: activity_detail_id,
1825: last_update_login,
1826: created_by,
1827: creation_Date
1828: )
1829: SELECT WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
1830: organization_id,
1831: activity_id,
1832: activity_detail_id,
1833: operation_id,
1902:
1903: l_num_rows_inserted := 0;
1904:
1905:
1906: INSERT INTO WMS_ELS_EXP_RESOURCE
1907: (els_exp_resource_id ,
1908: organization_id,
1909: activity_id,
1910: activity_detail_id,
1923: last_update_login,
1924: created_by,
1925: creation_Date
1926: )
1927: select WMS_ELS_EXP_RESOURCE_S.NEXTVAL,
1928: organization_id,
1929: activity_id,
1930: activity_detail_id,
1931: operation_id,
2021:
2022:
2023: -- We would do the following in this procedure
2024:
2025: --Delete all the rows that are already populated in the WMS_ELS_EXP_RESOURCE for
2026: --that organization. This is done so that no old rows are left in the table and the
2027: --table can be freshly populated with expected work. This also ensures that all the
2028: --tasks and expected work that is already done is flushed out and is not accounted any more.
2029:
2026: --that organization. This is done so that no old rows are left in the table and the
2027: --table can be freshly populated with expected work. This also ensures that all the
2028: --tasks and expected work that is already done is flushed out and is not accounted any more.
2029:
2030: -- Populate the WMS_ELS_EXP_RESOURCE table with the fresh set of expected work for
2031: --the given data period(populated in the global setup).This will be done by calling
2032: --the program WMS_ELS_EXPECTED_RES. Populate_Expecetd_Work.
2033:
2034:
2031: --the given data period(populated in the global setup).This will be done by calling
2032: --the program WMS_ELS_EXPECTED_RES. Populate_Expecetd_Work.
2033:
2034:
2035: -- Do the matching of the rows in WMS_ELS_EXP_RESOURCE table with the setup rows
2036: --in WMS_ELS_INDIVIDUAL_TASKS_B using the where clause for that setup row(dynamic SQL),
2037: --starting with the setup row having the least sequence number. Once a match is found
2038: --stamp the Estimated_time column in WMS_ELS_EXP_RESOURCE table with the time required
2039: --to complete the transaction. Also stamp the Expecetd_Resource column based on the global
2034:
2035: -- Do the matching of the rows in WMS_ELS_EXP_RESOURCE table with the setup rows
2036: --in WMS_ELS_INDIVIDUAL_TASKS_B using the where clause for that setup row(dynamic SQL),
2037: --starting with the setup row having the least sequence number. Once a match is found
2038: --stamp the Estimated_time column in WMS_ELS_EXP_RESOURCE table with the time required
2039: --to complete the transaction. Also stamp the Expecetd_Resource column based on the global
2040: --setup.
2041:
2042: -- The parameters p_data_period_unit, p_data_period_value,
2137: IF g_debug=1 THEN
2138: debug('Before inserting the data into Global temporary table','MATCH_RATE_EXP_RESOURCE');
2139: END IF;
2140:
2141: INSERT INTO WMS_ELS_EXP_RESOURCE_GTEMP
2142: (SELECT els_data_id
2143: , source_header_id
2144: , source_line_id
2145: , activity_id
2144: , source_line_id
2145: , activity_id
2146: , activity_detail_id
2147: , operation_id
2148: FROM wms_els_exp_resource
2149: WHERE organization_id = p_org_id
2150: AND els_data_id IS NOT NULL);
2151:
2152: IF g_debug=1 THEN
2152: IF g_debug=1 THEN
2153: debug('After inserting the data in Global temporary table','MATCH_RATE_EXP_RESOURCE');
2154: END IF;
2155:
2156: --Delete all the rows that are already populated in the WMS_ELS_EXP_RESOURCE for
2157: --that organization. This is done so that no old rows are left in the table and the
2158: --table can be freshly populated with expected work. This also ensures that all the
2159: --tasks and expected work that is already done is flushed out and is not accounted any more.
2160:
2157: --that organization. This is done so that no old rows are left in the table and the
2158: --table can be freshly populated with expected work. This also ensures that all the
2159: --tasks and expected work that is already done is flushed out and is not accounted any more.
2160:
2161: DELETE FROM WMS_ELS_EXP_RESOURCE WHERE organization_id = p_org_id;
2162:
2163: -- Populate the WMS_ELS_EXP_RESOURCE table with the fresh set of expected work for
2164: --the given data period(populated in the global setup).This will be done by calling
2165: --the program WMS_ELS_EXPECTED_RES. Populate_Expecetd_Work.
2159: --tasks and expected work that is already done is flushed out and is not accounted any more.
2160:
2161: DELETE FROM WMS_ELS_EXP_RESOURCE WHERE organization_id = p_org_id;
2162:
2163: -- Populate the WMS_ELS_EXP_RESOURCE table with the fresh set of expected work for
2164: --the given data period(populated in the global setup).This will be done by calling
2165: --the program WMS_ELS_EXPECTED_RES. Populate_Expecetd_Work.
2166:
2167: POPULATE_EXPECTED_WORK
2197:
2198: -- Added for the bug # 5169490
2199: -- Update the number of matched transactions in wms_els_individual_tasks_b
2200: -- i.e, Deduct the matched transaction count for the tasks/record that has been
2201: -- populated again in wms_els_exp_resource table.
2202:
2203: IF g_debug=1 THEN
2204: debug('Before updating the data in wms_els_individual_tasks_b table','MATCH_RATE_EXP_RESOURCE');
2205: END IF;
2205: END IF;
2206:
2207: UPDATE wms_els_individual_tasks_b weitb
2208: SET num_trx_matched = num_trx_matched - (SELECT count(weerg.els_data_id)
2209: FROM wms_els_exp_resource_gtemp weerg
2210: , wms_els_exp_resource weer
2211: WHERE weerg.source_header_id = weer.source_header_id
2212: AND weerg.source_line_id = weer.source_line_id
2213: AND weerg.activity_id = weer.activity_id
2206:
2207: UPDATE wms_els_individual_tasks_b weitb
2208: SET num_trx_matched = num_trx_matched - (SELECT count(weerg.els_data_id)
2209: FROM wms_els_exp_resource_gtemp weerg
2210: , wms_els_exp_resource weer
2211: WHERE weerg.source_header_id = weer.source_header_id
2212: AND weerg.source_line_id = weer.source_line_id
2213: AND weerg.activity_id = weer.activity_id
2214: AND weerg.activity_detail_id = weer.activity_detail_id
2223: debug('After updating the data in wms_els_individual_tasks_b table','MATCH_RATE_EXP_RESOURCE');
2224: END IF;
2225:
2226:
2227: -- Now once all this is done Do the matching of the rows in WMS_ELS_EXP_RESOURCE table
2228: --with the setup rows
2229: --in WMS_ELS_INDIVIDUAL_TASKS_B using the where clause built dynamically (dynamic SQL),
2230: --starting with the setup row having the least sequence number. Once a match is found
2231: --stamp the Estimated_time column in WMS_ELS_EXP_RESOURCE table with the time required
2227: -- Now once all this is done Do the matching of the rows in WMS_ELS_EXP_RESOURCE table
2228: --with the setup rows
2229: --in WMS_ELS_INDIVIDUAL_TASKS_B using the where clause built dynamically (dynamic SQL),
2230: --starting with the setup row having the least sequence number. Once a match is found
2231: --stamp the Estimated_time column in WMS_ELS_EXP_RESOURCE table with the time required
2232: --to complete the transaction. Also stamp the Expecetd_Resource column based on the global
2233: --setup.
2234:
2235:
2378: l_where_clause := l_where_clause || ' AND group_id = :group_id ';
2379: END IF;
2380: */
2381:
2382: l_sql :='UPDATE wms_els_exp_resource SET '
2383: ||' els_data_id = :els_data_id'
2384: ||' ,source_zone_id = :source_zone'
2385: ||' ,destination_zone_id = :destination_zone'
2386: ||' ,item_category_id = :item_category'
2552: -- now update all txns having els_data_id as NULL as non_attributed
2553: l_update_count := NULL;
2554:
2555:
2556: UPDATE wms_els_exp_resource SET unattributed_flag = 1
2557: WHERE els_data_id IS NULL AND organization_id = p_org_id;
2558:
2559: l_update_count := SQL%ROWCOUNT;
2560: