[Home] [Help]
82: TYPE lpn_name_tab IS TABLE OF WMS_LICENSE_PLATE_NUMBERS.LICENSE_PLATE_NUMBER%TYPE
83: INDEX BY BINARY_INTEGER;
84: TYPE sub_name_tab IS TABLE OF MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE
85: INDEX BY BINARY_INTEGER;
86: TYPE row_type_tab IS TABLE OF WMS_PUTAWAY_GROUP_TASKS_GTMP.ROW_TYPE%TYPE
87: INDEX BY BINARY_INTEGER;
88: TYPE item_tab IS TABLE OF MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE
89: INDEX BY BINARY_INTEGER;
90:
219:
220:
221: /**
222: * This function will get the count of number of rows in the
223: * global temporary table WMS_PUTAWAY_GROUP_TASKS_GTMP
224: * The rows counted will depend on the mode for which it is called
225: * Mode Meaning
226: * ----------------------------------------------------------------
227: * 1 Get the count of all the rows in the temp table
255: -- Get the count of all rows in the temp table
256: l_progress := '100';
257: SELECT count(ROWID)
258: INTO l_rec_count
259: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP;
260: l_progress := '110';
261:
262: ELSIF (p_mode = 2) THEN
263:
264: -- Get the count of all 'All Task' Rows in the temp table
265: l_progress := '200';
266: SELECT count(ROWID)
267: INTO l_rec_count
268: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
269: WHERE row_type = G_ROW_TP_ALL_TASK;
270: l_progress := '210';
271:
272: ELSIF (p_mode = 3) THEN
274: -- Get the count of all 'Group Task' Rows in the temp table
275: l_progress := '300';
276: SELECT count(ROWID)
277: INTO l_rec_count
278: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
279: WHERE row_type = G_ROW_TP_GROUP_TASK;
280: l_progress := '310';
281:
282: ELSIF (p_mode = 4) THEN
284: -- Get the count of all 'LPN Task' Rows in the temp table
285: l_progress := '400';
286: SELECT count(ROWID)
287: INTO l_rec_count
288: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
289: WHERE row_type = G_ROW_TP_LPN_TASK;
290: l_progress := '410';
291:
292: ELSE
399:
400:
401: /**
402: * This function will remove all the records in the temporary
403: * table WMS_PUTAWAY_GROUP_TASKS_GTMP
404: * The rows that has to be deleted depends on the i/p parameter
405: * p_del_type
406: *
407: * p_del_type Action
434:
435: -- This cursor will select the dummy rows in the temp table which can be deleted
436: CURSOR c_dummy_task IS
437: SELECT ROWID
438: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
439: WHERE row_type = G_ROW_TP_LPN_TASK;
440:
441:
442: BEGIN
466: RETURN -1;
467: END IF;
468:
469: l_progress := '110';
470: DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
471: WHERE group_id = p_group_id;
472: l_progress := '120';
473:
474: -- Get the number of rows processed by the above SQL
503:
504:
505: -- Bulk delete the dummy records
506: FORALL i IN 1 .. l_rowid_tab.COUNT
507: DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
508: WHERE ROWID = l_rowid_tab(i);
509:
510: l_progress := '260';
511: END LOOP; --dummy_task_cursor
515: l_progress := '280';
516:
517:
518: ELSIF p_del_type = 3 THEN
519: --Delete all records from WMS_PUTAWAY_GROUP_TASKS_GTMP
520:
521: l_progress := '305';
522: IF (l_debug = 1) THEN
523: DEBUG(' Deleting all the rows from the global temp table',l_proc_name,9);
523: DEBUG(' Deleting all the rows from the global temp table',l_proc_name,9);
524: END IF;
525:
526: l_progress := '310';
527: DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP;
528: l_progress := '320';
529:
530: -- Get the number of rows processed by the above SQL
531: l_progress := '330';
546: RETURN -1;
547: END IF;
548:
549: l_progress := '410';
550: DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
551: WHERE group_id = p_group_id
552: AND (PROCESS_FLAG = 'Y' OR ROW_TYPE = G_ROW_TP_GROUP_TASK);
553: l_progress := '420';
554:
590:
591:
592: /**
593: * This function will remove all the processed records in the temporary
594: * table WMS_PUTAWAY_GROUP_TASKS_GTMP and insert the newly created tasks
595: * during a partial putaway
596: * @param p_group_id Group ID for which the records has to be deleted
597:
598:
697: AND mtrl.header_id = mtrh.header_id
698: AND mtrh.move_order_type = 6
699: AND mmtt.organization_id = msik.organization_id
700: AND mmtt.inventory_item_id = msik.inventory_item_id
701: AND mmtt.transaction_temp_id NOT IN (SELECT TRANSACTION_TEMP_ID FROM WMS_PUTAWAY_GROUP_TASKS_GTMP)) LOOP
702:
703: IF (l_debug = 1) THEN
704: DEBUG('Calling activate_operation_instance with ...',l_proc_name,9);
705: DEBUG('p_source_task_id => ' || gtmp_rec.transaction_temp_id,l_proc_name,9);
734: RAISE fnd_api.g_exc_error;
735: END IF;
736:
737:
738: INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
739: (
740: ORGANIZATION_ID
741: ,TRANSACTION_TEMP_ID
742: ,TRANSACTION_HEADER_ID
879: FOR grp_rec IN (SELECT MIN(ROWID) row_id
880: ,MIN(drop_order) drop_order
881: ,SUM(transaction_quantity) transaction_quantity
882: ,SUM(primary_quantity) primary_quantity
883: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
884: WHERE row_type = G_ROW_TP_ALL_TASK
885: AND drop_type = G_DT_ITEM_DROP
886: AND group_id=p_group_id
887: AND NOT (crossdock_type > 1
895: ,lot_number
896: ,dest_locator
897: ,into_lpn_id) LOOP
898:
899: INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
900: (
901: ORGANIZATION_ID
902: ,TRANSACTION_TEMP_ID
903: ,TRANSACTION_HEADER_ID
1025: ,WIP_SCHEDULE
1026: ,WIP_ASSEMBLY
1027: ,SECONDARY_QUANTITY -- 9037915
1028: ,SECONDARY_UOM -- 9037915
1029: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1030: WHERE ROWID = grp_rec.row_id;
1031:
1032: END LOOP;
1033: l_progress := '300';
1037: FROM dual;
1038:
1039: l_progress := '310';
1040:
1041: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
1042: SET transaction_header_id = l_txn_header_id
1043: WHERE group_id = p_group_id
1044: RETURNING transaction_temp_id
1045: BULK COLLECT INTO l_txn_temp_id_tab;
1046:
1047: l_progress := '320';
1048:
1049: IF (l_debug = 1) THEN
1050: DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id = ' || l_txn_header_id ||
1051: ' and group_id = ' || p_group_id
1052: ,l_proc_name,9);
1053: END IF;
1054:
1062:
1063: l_progress := '340';
1064:
1065: IF (l_debug = 1) THEN
1066: DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id= ' || l_txn_header_id ,l_proc_name,9);
1067: END IF;
1068:
1069: l_progress := '350';
1070:
1131: l_txn_temp_id_tab num_tab;
1132:
1133: CURSOR c_all_group_tasks_cursor IS
1134: SELECT drop_order
1135: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1136: WHERE row_type = G_ROW_TP_GROUP_TASK;
1137:
1138: BEGIN
1139:
1160: INTO l_txn_header_id
1161: FROM dual;
1162: l_progress := '140';
1163:
1164: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
1165: SET transaction_header_id = l_txn_header_id
1166: ,group_id = l_drop_order
1167: WHERE drop_order = l_drop_order
1168: RETURNING transaction_temp_id
1170: l_progress := '150';
1171:
1172: l_rec_count := l_rec_count + SQL%rowcount;
1173: IF (l_debug = 1) THEN
1174: DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id = ' || l_txn_header_id ||
1175: ' and group_id = ' || l_drop_order
1176: ,l_proc_name,9);
1177: END IF;
1178: l_progress := '160';
1185: l_progress := '170';
1186:
1187: l_rec_count := l_rec_count + SQL%rowcount;
1188: IF (l_debug = 1) THEN
1189: DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id= ' || l_txn_header_id ,l_proc_name,9);
1190: END IF;
1191: l_progress := '180';
1192:
1193:
1279: l_progress := '160';
1280:
1281: SELECT MIN(drop_order)
1282: INTO l_min_drop_order
1283: FROM wms_putaway_group_tasks_gtmp
1284: WHERE lpn_id = p_lpn_id
1285: OR lpn_id IN ( SELECT lpn_id
1286: FROM wms_license_plate_numbers
1287: START WITH lpn_id = p_lpn_id
1289: );
1290: l_progress := '170';
1291:
1292: --Update the cosolidated lpn for the child LPNs with the lpn passed
1293: /* UPDATE wms_putaway_group_tasks_gtmp
1294: SET consolidated_lpn_id = p_lpn_id
1295: ,consolidated_lpn_name = l_lpn_name
1296: ,drop_order = p_drop_order
1297: WHERE lpn_id IN (SELECT lpn_id
1302:
1303:
1304: -- Do bulk update for all LPN and all itz child the consolidated LPN and the min drop order in that group
1305: FORALL i IN 1 .. l_lpn_id_tab.COUNT
1306: UPDATE wms_putaway_group_tasks_gtmp
1307: SET consolidated_lpn_id = p_lpn_id
1308: ,consolidated_lpn_name = p_lpn_name
1309: ,drop_order = l_min_drop_order
1310: WHERE lpn_id = l_lpn_id_tab(i);
1369: -- Select the parent lpn id of the passed lpn
1370: l_progress := '100';
1371: SELECT parent_lpn_id
1372: INTO l_parent_lpn_id
1373: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1374: WHERE lpn_id = p_lpn_id
1375: AND ROWNUM < 2;
1376: l_progress := '110';
1377:
1376: l_progress := '110';
1377:
1378: -- Set the drop type to 'ID' for all the contents of the lpn passed
1379: l_progress := '120';
1380: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
1381: SET drop_type = G_DT_ITEM_DROP
1382: WHERE lpn_id = p_lpn_id;
1383: l_progress := '130';
1384:
1483: l_progress := '110';
1484:
1485: -- This will bulk insert consolidated records for group tasks into the temp table
1486: FORALL i IN 1 .. l_rowid_tab.COUNT
1487: INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
1488: (
1489: ORGANIZATION_ID
1490: ,TRANSACTION_TEMP_ID
1491: ,TRANSACTION_HEADER_ID
1624: ,l_error_code_tab(i) --R12
1625: ,l_drop_lpn_option_tab(i) --R12
1626: ,l_consolidation_method_id_tab(i) --R12
1627: ,sub_lpn_controlled_flag --R12
1628: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1629: WHERE ROWID = l_rowid_tab(i);
1630: l_progress := '120';
1631:
1632: l_rec_count := SQL%rowcount;
1687: ,MAX(show_message) --R12
1688: ,MAX(error_code) --R12
1689: ,MAX(drop_lpn_option) --R12: This value should be unique per group
1690: ,MAX(consolidation_method_id) --R12: This value should be unique per group
1691: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1692: WHERE row_type IN (G_ROW_TP_ALL_TASK, G_ROW_TP_LPN_TASK)
1693: AND drop_type = G_DT_CONSOLIDATED_DROP
1694: GROUP BY drop_order;
1695:
1750: l_rec_count := l_rec_count + l_cur_count;
1751: l_progress := '235';
1752:
1753: IF (l_debug = 1) THEN
1754: DEBUG( l_cur_count|| ' Group task Row(s) inserted in WMS_PUTAWAY_GROUP_TASKS_GTMP '
1755: ,l_proc_name,9);
1756: END IF;
1757: l_progress := '240';
1758:
1866: ,MAX(show_message)
1867: ,MAX(error_code)
1868: ,MAX(drop_lpn_option) --R12: This value should be unique per group
1869: ,MAX(consolidation_method_id) --R12: This value should be unique per group
1870: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1871: WHERE row_type = G_ROW_TP_ALL_TASK
1872: AND drop_type = G_DT_ITEM_DROP
1873: AND NOT (crossdock_type > 1
1874: AND backorder_delivery_detail > 0
1895: ,MAX(show_message)
1896: ,MAX(error_code)
1897: ,MAX(drop_lpn_option) --R12: This value should be unique per group
1898: ,MAX(consolidation_method_id) --R12: This value should be unique per group
1899: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1900: WHERE row_type = G_ROW_TP_ALL_TASK
1901: AND drop_type = G_DT_ITEM_DROP
1902: AND crossdock_type > 1
1903: AND backorder_delivery_detail > 0
1936: ,WIP_ENTITY_TYPE
1937: ,WIP_START_DATE
1938: ,WIP_SCHEDULE
1939: ,WIP_ASSEMBLY
1940: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
1941: WHERE drop_type = G_DT_ITEM_DROP
1942: AND row_type = G_ROW_TP_GROUP_TASK;
1943:
1944: BEGIN
2000: l_rec_count := l_rec_count + l_cur_count;
2001: l_progress := '235';
2002:
2003: IF (l_debug = 1) THEN
2004: DEBUG( l_cur_count|| ' Group task Row(s) inserted in WMS_PUTAWAY_GROUP_TASKS_GTMP '
2005: ,l_proc_name,9);
2006: END IF;
2007: l_progress := '240';
2008:
2060: l_rec_count := l_rec_count + l_cur_count;
2061: l_progress := '335';
2062:
2063: IF (l_debug = 1) THEN
2064: DEBUG( l_cur_count|| ' Group task Row(s) inserted in WMS_PUTAWAY_GROUP_TASKS_GTMP for crossdocked to WIP ISSUE tasks'
2065: ,l_proc_name,9);
2066: END IF;
2067: l_progress := '340';
2068:
2105: l_progress := '620';
2106:
2107:
2108: FORALL i IN 1 ..l_lpn_id_tab.COUNT
2109: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
2110: SET drop_order = l_out_drop_order_tab(i)
2111: WHERE drop_type = G_DT_ITEM_DROP
2112: AND lpn_id = l_lpn_id_tab(i)
2113: AND inventory_item_id = l_inventory_item_id_tab(i)
2130: l_progress := '640';
2131:
2132: l_rec_count := l_rec_count + sql%rowcount;
2133: IF (l_debug = 1) THEN
2134: DEBUG(sql%rowcount || ' Group task Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP '
2135: ,l_proc_name,9);
2136: END IF;
2137:
2138: l_progress := '650';
2316: -- For which the drop type is 'CD'
2317: CURSOR c_lpn_level_cursor IS
2318: SELECT DISTINCT
2319: lpn_level
2320: FROM wms_putaway_group_tasks_gtmp
2321: WHERE drop_type = G_DT_CONSOLIDATED_DROP
2322: ORDER BY lpn_level DESC;
2323:
2324: -- This cursor will get all the LPNs, their dest sub/loc and its parent lpn id where drop_type is CD
2342: ,WIP_ENTITY_TYPE
2343: ,WIP_START_DATE
2344: ,WIP_SCHEDULE
2345: ,WIP_ASSEMBLY
2346: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
2347: WHERE drop_type = G_DT_CONSOLIDATED_DROP
2348: AND lpn_level = v_lpn_level;
2349:
2350: -- This cursor will get the rowid, dest sub/loc for the lpn passed where drop type is CD
2364: ,WIP_ENTITY_TYPE
2365: ,WIP_START_DATE
2366: ,WIP_SCHEDULE
2367: ,WIP_ASSEMBLY
2368: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
2369: WHERE lpn_id = v_parent_lpn_id
2370: AND drop_type = G_DT_CONSOLIDATED_DROP
2371: AND ROWNUM < 2;
2372:
2379: CURSOR c_group_tasks_order_by IS
2380: SELECT wpgtt.ROWID
2381: ,ROWNUM
2382: ,wpgtt.lpn_id lpn_id
2383: FROM wms_putaway_group_tasks_gtmp wpgtt
2384: ORDER BY wpgtt.sub_dropping_order,
2385: wpgtt.dest_subinventory,
2386: wpgtt.loc_dropping_order,
2387: wpgtt.LOCATOR,
2465: END IF;
2466: END;
2467:
2468: -- update the drop order and LPN columns back to temp table
2469: UPDATE wms_putaway_group_tasks_gtmp
2470: SET drop_order = l_drop_order
2471: ,lpn_name = l_lpn_name_tab(i)
2472: ,parent_lpn_id = l_parent_lpn_id_tab(i)
2473: ,parent_lpn_name = l_parent_lpn_name_tab(i)
2737: ,l_wip_entity_type_count
2738: ,l_wip_start_date_count
2739: ,l_wip_schedule_count
2740: ,l_wip_assembly_count
2741: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
2742: WHERE row_type = G_ROW_TP_ALL_TASK
2743: AND crossdock_type > 1
2744: AND backorder_delivery_detail > 0
2745: AND wip_supply_type = 1
2829: (l_wip_supply_type <> 1) ) THEN
2830: l_progress := '350';
2831:
2832: -- Dest sub/loc of the parent is null and hence update the dest sub/loc with that of childs
2833: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
2834: SET dest_subinventory = l_dest_sub_tab(i)
2835: ,dest_locator = l_dest_loc_tab(i)
2836: ,into_lpn_id = l_into_lpn_id_tab(i)
2837: ,into_lpn_name = l_into_lpn_name_tab(i)
2966: BEGIN
2967: UPDATE mtl_material_transactions_temp
2968: SET cartonization_id = NULL
2969: WHERE transaction_temp_id IN (SELECT gtmp.transaction_temp_id
2970: FROM wms_putaway_group_tasks_gtmp gtmp
2971: WHERE gtmp.consolidation_method_id = 1 --???MDC
2972: AND gtmp.drop_type = g_dt_item_drop);
2973:
2974: IF (l_debug = 1) THEN
2974: IF (l_debug = 1) THEN
2975: DEBUG('Num of MMTT rows updated:' ||SQL%rowcount,l_proc_name,4);
2976: END IF;
2977:
2978: UPDATE wms_putaway_group_tasks_gtmp
2979: SET consolidation_method_id = 0
2980: WHERE consolidation_method_id = 1
2981: AND drop_type = g_dt_item_drop;
2982:
3033:
3034:
3035: /**
3036: * This function will populate the global temporary table
3037: * WMS_PUTAWAY_GROUP_TASKS_GTMP with the required data and
3038: * will return the count of rows in the temporary table as
3039: * output.
3040:
3041: * This method will also insert the dummy records for those
3153: ,transaction_temp_id
3154: ,organization_id
3155: ,Nvl(crossdock_type,0)
3156: ,Nvl(wip_supply_type,0)
3157: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
3158: WHERE 1 = 1
3159: AND row_type = G_ROW_TP_ALL_TASK
3160: AND crossdock_type > 1
3161: AND backorder_delivery_detail > 0;
3239: IF p_item_drop_flag <> 'Y' THEN
3240:
3241: l_progress := '45';
3242: -- LPN don't have contents, so insert a dummy row
3243: INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
3244: (
3245: ORGANIZATION_ID
3246: ,LPN_ID
3247: ,PARENT_LPN_ID
3275: -- LPN has contents so query from MTRL,WDT and insert into temp table
3276:
3277: l_progress := '50';
3278: --Data has to be ordered in the temp table later before processing, because ORDER BY during insert is not supported
3279: INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
3280: (
3281: ORGANIZATION_ID
3282: ,TRANSACTION_TEMP_ID
3283: ,TRANSACTION_HEADER_ID
3601: DEBUG('WIP Entity Type = ' || l_wip_entity_type_tab(i) ,l_proc_name,1);
3602: END IF;
3603:
3604: IF (l_debug = 1) THEN
3605: DEBUG('Updating WMS_PUTAWAY_GROUP_TASKS_GTMP with WIP_ENTITY_TYPE and BACKORDER_DELIVERY_DETAIL. Temp id is - ' || l_tempid_tab(i) ,l_proc_name,9);
3606: END IF;
3607: -- Update back the OPM Batch details in to the temp table
3608: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
3609: SET WIP_ENTITY_TYPE = l_wip_entity_type_tab(i),
3604: IF (l_debug = 1) THEN
3605: DEBUG('Updating WMS_PUTAWAY_GROUP_TASKS_GTMP with WIP_ENTITY_TYPE and BACKORDER_DELIVERY_DETAIL. Temp id is - ' || l_tempid_tab(i) ,l_proc_name,9);
3606: END IF;
3607: -- Update back the OPM Batch details in to the temp table
3608: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
3609: SET WIP_ENTITY_TYPE = l_wip_entity_type_tab(i),
3610: BACKORDER_DELIVERY_DETAIL = l_wip_entity_type_tab(i)
3611: WHERE transaction_temp_id = l_tempid_tab(i);
3612:
3638: IF (l_debug = 1) THEN
3639: DEBUG('Resetting the xdock related cols for temp_id ' || l_tempid_tab(i) ,l_proc_name,9);
3640: END IF;
3641:
3642: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
3643: SET wip_supply_type = 0
3644: ,backorder_delivery_detail = 0
3645: ,crossdock_type = 0
3646: WHERE transaction_temp_id = l_tempid_tab(i);
3668: l_progress := '370';
3669:
3670: -- Update back these details into the temp table
3671: FORALL i IN 1 .. l_rowid_tab.COUNT
3672: UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
3673: SET WIP_JOB = l_job_tab(i)
3674: ,WIP_LINE = l_line_tab(i)
3675: ,WIP_DEPT = l_dept_tab(i)
3676: ,WIP_OP_SEQ = l_operation_seq_num_tab(i)
4352: SELECT ROWID
4353: ,transaction_temp_id
4354: ,show_message
4355: ,error_code
4356: FROM wms_putaway_group_tasks_gtmp
4357: WHERE row_type = G_ROW_TP_ALL_TASK;
4358:
4359: -- This cursor will get the MMTT details for the temp id passed
4360: CURSOR c_mmtt_cursor(v_transaction_temp_id NUMBER) IS
4512: END IF;
4513:
4514: -- Do the bulk update on the temp table with the ATF suggested dest sub/loc/lpn
4515: FORALL i IN 1 .. l_tempid_tab.COUNT
4516: UPDATE wms_putaway_group_tasks_gtmp
4517: SET dest_subinventory = l_dest_sub_tab(i)
4518: ,dest_locator = l_dest_loc_tab(i)
4519: ,into_lpn_id = l_into_lpn_id_tab(i)
4520: ,into_lpn_name = l_into_lpn_name_tab(i)
4653: BULK COLLECT
4654: INTO l_lpn_id_tab
4655: FROM WMS_LICENSE_PLATE_NUMBERS
4656: WHERE lpn_id IN ( SELECT DISTINCT lpn_id
4657: FROM wms_putaway_group_tasks_gtmp
4658: WHERE row_type = G_ROW_TP_ALL_TASK
4659: AND lpn_context <> 3
4660: )
4661: FOR UPDATE NOWAIT;
4745: SELECT DISTINCT
4746: lpn_id
4747: ,lpn_name
4748: ,lpn_level
4749: FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
4750: WHERE consolidated_lpn_id IS NULL
4751: AND drop_type = G_DT_CONSOLIDATED_DROP
4752: ORDER BY lpn_level ASC;
4753:
5806:
5807: -- This cursor will get the MMTTs assoicated with the group_id passed from the temp table
5808: CURSOR c_tmp_mmtt_cursor IS
5809: SELECT transaction_temp_id
5810: FROM wms_putaway_group_tasks_gtmp
5811: WHERE group_id = p_group_id
5812: AND row_type = G_ROW_TP_ALL_TASK;
5813:
5814: --BUG 5075410: The c_all_mmtt_cursor is used in the following scenarios:
10191: , NVL(mtrl.secondary_quantity_delivered, 0) --OPM Convergence
10192: , mtrl.secondary_uom_code --OPM Convergence
10193: FROM mtl_txn_request_lines mtrl
10194: WHERE mtrl.line_id IN (SELECT DISTINCT gtmp.move_order_line_id
10195: FROM wms_putaway_group_tasks_gtmp gtmp
10196: WHERE gtmp.group_id = p_group_id
10197: AND gtmp.transaction_header_id = p_txn_header_id
10198: AND gtmp.row_type = g_row_tp_all_task)
10199: AND mtrl.line_status <> 5 ; -- 9037915
10212: , NVL(mtrl.secondary_quantity_delivered, 0) --OPM Convergence
10213: , mtrl.secondary_uom_code --OPM Convergence
10214: FROM mtl_txn_request_lines mtrl
10215: WHERE mtrl.line_id IN (SELECT DISTINCT gtmp.move_order_line_id
10216: FROM wms_putaway_group_tasks_gtmp gtmp
10217: WHERE gtmp.group_id = p_group_id
10218: AND gtmp.transaction_header_id = p_txn_header_id
10219: AND gtmp.row_type = g_row_tp_all_task)
10220: AND Nvl(mtrl.lot_number,'&*_') = Nvl(v_lot_number,'&*_')
10238: , secondary_quantity -- OPM Convergence
10239: , secondary_uom -- 9037915
10240: , inspection_status
10241: , primary_uom_code
10242: FROM wms_putaway_group_tasks_gtmp
10243: WHERE group_id = p_group_id
10244: AND transaction_header_id = p_txn_header_id
10245: AND row_type = G_ROW_TP_ALL_TASK
10246: AND ( (v_disc = 'N') --no qty idsc
10634: , l_lot_control_code
10635: , l_serial_control_code
10636: , l_grp_sec_quantity
10637: , l_grp_sec_uom
10638: FROM wms_putaway_group_tasks_gtmp
10639: WHERE group_id = p_group_id
10640: AND transaction_header_id = p_txn_header_id
10641: AND row_type = G_ROW_TP_GROUP_TASK;
10642:
10857: l_progress := '80';
10858:
10859: --Now that we are ready with quantity calculations, mark the all tasks
10860: --in the current group for this move order line
10861: UPDATE wms_putaway_group_tasks_gtmp
10862: SET process_flag = 'Y'
10863: WHERE group_id = p_group_id
10864: AND transaction_header_id = p_txn_header_id
10865: AND move_order_line_id = l_mol_line_id;
10992: END IF;
10993:
10994: --Now that we are ready with quantity calculations, mark the all tasks
10995: --in the current group for this move order line
10996: UPDATE wms_putaway_group_tasks_gtmp
10997: SET process_flag = 'Y'
10998: WHERE group_id = p_group_id
10999: AND transaction_header_id = p_txn_header_id
11000: AND move_order_line_id = l_mol_line_id;
12690: /*Validating for Correct BO dd ID from Gtmp*/
12691: BEGIN
12692: SELECT wpgt2.BACKORDER_DELIVERY_DETAIL
12693: INTO l_bo_d_frm_id
12694: FROM wms_putaway_group_tasks_gtmp wpgt1 , wms_putaway_group_tasks_gtmp wpgt2
12695: WHERE wpgt1.backorder_delivery_detail = p_backorder_delivery_detail_id
12696: AND wpgt2.transaction_header_id = wpgt1.transaction_header_id
12697: AND wpgt2.row_type = 'All Task'
12698: AND wpgt1.row_type = 'Group Task'
12809: -- Check there are no child LPNs and there is only one item left for Item drop
12810: BEGIN
12811: select count(DISTINCT inventory_item_id),count(DISTINCT lot_number),count(DISTINCT revision)
12812: into l_item_count,l_lot_count,l_rev_count
12813: from WMS_PUTAWAY_GROUP_TASKS_GTMP wpgtg
12814: where lpn_id = p_lpn_id
12815: and drop_type='ID'
12816: and row_type = 'Group Task'
12817: and not exists
12833: END IF;
12834:
12835: -- check LPN is fully allocated.
12836: select sum(primary_quantity) INTO l_qty_gtmp
12837: from WMS_PUTAWAY_GROUP_TASKS_GTMP
12838: where lpn_id = p_lpn_id
12839: and row_type = 'Group Task'
12840: and drop_type = 'ID';
12841: