[Home] [Help]
78:
79: -- Define collections for commonly used datatypes. These collections will be
80: -- used for bulk fetch.
81:
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
632:
633: --This cursor will fetch all the child LPNs for the LPN Passed
634: CURSOR c_child_lpn_cursor IS
635: SELECT lpn_id
636: FROM wms_license_plate_numbers
637: START WITH lpn_id = p_lpn_id
638: CONNECT BY PRIOR lpn_id = parent_lpn_id;
639:
640: BEGIN
661: INTO l_min_drop_order
662: FROM wms_putaway_group_tasks_gtmp
663: WHERE lpn_id = p_lpn_id
664: OR lpn_id IN ( SELECT lpn_id
665: FROM wms_license_plate_numbers
666: START WITH lpn_id = p_lpn_id
667: CONNECT BY PRIOR lpn_id = parent_lpn_id
668: );
669: l_progress := '170';
673: SET consolidated_lpn_id = p_lpn_id
674: ,consolidated_lpn_name = l_lpn_name
675: ,drop_order = p_drop_order
676: WHERE lpn_id IN (SELECT lpn_id
677: FROM wms_license_plate_numbers
678: START WITH lpn_id = p_lpn_id
679: CONNECT BY PRIOR lpn_id = parent_lpn_id
680: );*/
681:
1821: ,l_parent_lpn_name_tab(i)
1822: ,l_outermost_lpn_id_tab(i)
1823: ,l_outermost_lpn_name_tab(i)
1824: FROM
1825: wms_license_plate_numbers wlpn1
1826: ,wms_license_plate_numbers wlpn2
1827: ,wms_license_plate_numbers wlpn3
1828: WHERE
1829: wlpn1.lpn_id = l_wpgtt_lpn_id_tab(i)
1822: ,l_outermost_lpn_id_tab(i)
1823: ,l_outermost_lpn_name_tab(i)
1824: FROM
1825: wms_license_plate_numbers wlpn1
1826: ,wms_license_plate_numbers wlpn2
1827: ,wms_license_plate_numbers wlpn3
1828: WHERE
1829: wlpn1.lpn_id = l_wpgtt_lpn_id_tab(i)
1830: AND wlpn1.outermost_lpn_id = wlpn3.lpn_id
1823: ,l_outermost_lpn_name_tab(i)
1824: FROM
1825: wms_license_plate_numbers wlpn1
1826: ,wms_license_plate_numbers wlpn2
1827: ,wms_license_plate_numbers wlpn3
1828: WHERE
1829: wlpn1.lpn_id = l_wpgtt_lpn_id_tab(i)
1830: AND wlpn1.outermost_lpn_id = wlpn3.lpn_id
1831: AND wlpn2.lpn_id(+) = wlpn1.parent_lpn_id;
2493: SELECT lpn_id
2494: ,parent_lpn_id
2495: ,LEVEL
2496: ,lpn_context
2497: FROM wms_license_plate_numbers wln
2498: START WITH LPN_ID IN (
2499: SELECT wln.lpn_id
2500: FROM wms_license_plate_numbers wln
2501: WHERE p_drop_type <> G_DT_DROP_ALL
2496: ,lpn_context
2497: FROM wms_license_plate_numbers wln
2498: START WITH LPN_ID IN (
2499: SELECT wln.lpn_id
2500: FROM wms_license_plate_numbers wln
2501: WHERE p_drop_type <> G_DT_DROP_ALL
2502: AND wln.lpn_id = p_lpn_id
2503: UNION ALL
2504: SELECT wln.outermost_lpn_id
2505: FROM wms_dispatched_tasks wdt
2506: ,mtl_txn_request_lines mtrl
2507: ,mtl_material_transactions_temp mmtt
2508: ,mtl_txn_request_headers mtrh
2509: ,wms_license_plate_numbers wln
2510: WHERE p_drop_type = G_DT_DROP_ALL
2511: AND mtrh.header_id = mtrl.header_id
2512: AND mtrh.move_order_type = 6
2513: AND mtrl.line_status = 7
2763: ,wms_dispatched_tasks wdt
2764: --,mtl_item_locations milk
2765: ,mtl_system_items_kfv msik
2766: ,mtl_txn_request_headers mtrh
2767: ,wms_license_plate_numbers wln
2768: --,mtl_secondary_inventories msi
2769: WHERE wdt.organization_id = p_org_id
2770: -- kajain
2771: -- added the decode since a lpn loaded by someone
3136: SELECT /*+ ORDERED USE_NL(WLPN MTRL MMTT) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
3137: MMTT.TRANSACTION_TEMP_ID
3138: FROM
3139: ( SELECT LPN_ID
3140: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
3141: MTL_TXN_REQUEST_LINES MTRL,
3142: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
3143: WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
3144: AND LINE_STATUS = 7
3319: SELECT /*+ ORDERED USE_NL(WLPN MTRL MMTT) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
3320: MMTT.TRANSACTION_TEMP_ID
3321: FROM
3322: ( SELECT LPN_ID
3323: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
3324: MTL_TXN_REQUEST_LINES MTRL,
3325: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
3326: WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
3327: AND LINE_STATUS = 7
3660: ,msi.dropping_order sub_dropping_order
3661: ,milk.dropping_order loc_dropping_order
3662: ,Nvl(msi.lpn_controlled_flag,2) lpn_controlled_flag
3663: FROM mtl_material_transactions_temp mmtt
3664: ,wms_license_plate_numbers wlpn
3665: ,mtl_secondary_inventories msi
3666: ,mtl_item_locations_kfv milk
3667: WHERE mmtt.transaction_temp_id = v_transaction_temp_id
3668: AND mmtt.cartonization_id = wlpn.lpn_id(+)
3946: -- So that other user(s) can't work on this LPN.
3947: SELECT lpn_id
3948: BULK COLLECT
3949: INTO l_lpn_id_tab
3950: FROM WMS_LICENSE_PLATE_NUMBERS
3951: WHERE lpn_id IN ( SELECT DISTINCT lpn_id
3952: FROM wms_putaway_group_tasks_gtmp
3953: WHERE row_type = G_ROW_TP_ALL_TASK
3954: AND lpn_context <> 3
4103:
4104: --BUG 3495726 Issue 25: Unpack from parent LPN first
4105: SELECT parent_lpn_id
4106: INTO l_parent_lpn_id
4107: FROM wms_license_plate_numbers
4108: WHERE lpn_id = p_lpn_id;
4109:
4110: IF (l_parent_lpn_id IS NOT NULL) THEN
4111: IF (l_debug = 1) THEN
4591: SELECT /*+ ORDERED USE_NL(WLPN MTRL MMTT) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
4592: MMTT.TRANSACTION_TEMP_ID
4593: FROM
4594: ( SELECT LPN_ID
4595: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
4596: MTL_TXN_REQUEST_LINES MTRL,
4597: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
4598: WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
4599: AND LINE_STATUS = 7
5082: SELECT mmtt.transaction_temp_id
5083: FROM mtl_material_transactions_temp mmtt
5084: ,mtl_txn_request_lines mtrl
5085: ,wms_dispatched_tasks wdt
5086: ,(SELECT lpn_id FROM wms_license_plate_numbers /*Bug5723418.*/
5087: START WITH lpn_id = p_lpn_id
5088: CONNECT BY PRIOR lpn_id = parent_lpn_id
5089: ) wlpn
5090: WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
5132: (p_group_id = -1))
5133: AND wdt.STATUS = 4
5134: AND wdt.task_type = 2
5135: AND mtrl.lpn_id IN (SELECT lpn_id
5136: FROM wms_license_plate_numbers
5137: START WITH lpn_id = l_outermost_lpn_id
5138: CONNECT BY PRIOR lpn_id = parent_lpn_id
5139: )
5140: AND ((mmtt.operation_plan_id IS NOT NULL
5204: SELECT outermost_lpn_id
5205: , lpn_context
5206: INTO l_outermost_lpn_id
5207: , l_lpn_context
5208: FROM wms_license_plate_numbers
5209: WHERE lpn_id = p_lpn_id;
5210: EXCEPTION
5211: WHEN OTHERS THEN
5212: IF (l_debug = 1) THEN
5379: IF (l_lpn_context IS NULL) THEN
5380: BEGIN
5381: SELECT lpn_context
5382: INTO l_lpn_context
5383: FROM wms_license_plate_numbers
5384: WHERE lpn_id = p_lpn_id;
5385: EXCEPTION
5386: WHEN OTHERS THEN
5387: IF (l_debug = 1) THEN
5758: l_parent_lpn_state VARCHAR2(1) := NULL; -- 'V' Valid, 'I' Inspect 'U' INVALID.
5759:
5760: CURSOR lpn_cursor IS
5761: SELECT lpn_id
5762: FROM wms_license_plate_numbers
5763: WHERE lpn_id <> p_lpn_id
5764: START WITH lpn_id = p_lpn_id
5765: CONNECT BY parent_lpn_id = PRIOR lpn_id;
5766: BEGIN
5780:
5781: -- Get LPN Context
5782: SELECT lpn_context,subinventory_code,locator_id
5783: INTO x_lpn_context,l_lpn_sub,l_lpn_loc
5784: FROM wms_license_plate_numbers
5785: WHERE lpn_id = p_lpn_id;
5786:
5787: --BUG 3473899: p_mode = 2 => it is coming from MANUAL_LOAD
5788: --check the material status, and see if a sub transfer is allowed
5832: FROM mtl_txn_request_lines mtrl,
5833: mtl_material_transactions_temp mmtt,
5834: wms_op_operation_instances wooi ,
5835: (SELECT wlpn.lpn_id /*5723418*/
5836: FROM wms_license_plate_numbers wlpn
5837: START WITH wlpn.lpn_id = p_lpn_id
5838: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn
5839: WHERE mtrl.lpn_id = wlpn.lpn_id
5840: AND mmtt.move_order_line_id = mtrl.line_id
6222: l_msg_count NUMBER;
6223: l_msg_data VARCHAR2(1200);
6224: CURSOR lpn_cursor IS
6225: SELECT lpn_id
6226: FROM wms_license_plate_numbers
6227: START WITH lpn_id = p_lpn_id
6228: CONNECT BY parent_lpn_id = PRIOR lpn_id;
6229: BEGIN
6230:
6539: END IF;
6540:
6541: SELECT parent_lpn_id,lpn_context
6542: INTO l_parent_lpn_id,l_lpn_context
6543: FROM wms_license_plate_numbers
6544: WHERE lpn_id = p_from_lpn_id;
6545:
6546: IF (p_to_lpn_id IS NOT NULL AND p_to_lpn_id <> 0) THEN
6547: l_progress := 20;
6547: l_progress := 20;
6548: BEGIN
6549: SELECT lpn_context,subinventory_code,locator_id
6550: INTO l_to_context,l_to_sub,l_to_loc
6551: FROM wms_license_plate_numbers
6552: WHERE lpn_id = p_to_lpn_id;
6553: EXCEPTION
6554: WHEN OTHERS THEN
6555: l_to_context := -1;
6872: SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
6873: INTO l_discrepancy
6874: FROM mtl_txn_request_lines mtrl ,
6875: (SELECT wlpn.lpn_id /*5723418*/
6876: FROM wms_license_plate_numbers wlpn
6877: START WITH wlpn.lpn_id = p_from_lpn_id
6878: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn
6879:
6880: WHERE mtrl.lpn_id = wlpn.lpn_id
6960: AND mmtt.move_order_line_id IN
6961: ( SELECT mtrl.line_id
6962: FROM mtl_txn_request_lines mtrl ,
6963: (SELECT wlpn.lpn_id /* 5723418 */
6964: FROM wms_license_plate_numbers wlpn
6965: START WITH wlpn.lpn_id = p_from_lpn_id
6966: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn
6967: WHERE mtrl.organization_id = p_org_id
6968: AND mtrl.lpn_id = wlpn.lpn_id );
7500:
7501:
7502: CURSOR lpn_cursor IS
7503: SELECT lpn_id
7504: FROM wms_license_plate_numbers
7505: WHERE parent_lpn_id = p_from_lpn_id;
7506:
7507: l_batch_seq NUMBER := p_batch_seq;
7508:
7525: ,locator_id
7526: INTO p_parent_lpn_id
7527: ,l_lpn_sub
7528: ,l_lpn_loc
7529: FROM wms_license_plate_numbers
7530: WHERE lpn_id = p_from_lpn_id;
7531: EXCEPTION
7532: WHEN OTHERS THEN
7533: IF (l_debug = 1) THEN
7762: l_lpn_id NUMBER;
7763:
7764: CURSOR lpn_cursor IS
7765: SELECT lpn_id
7766: FROM wms_license_plate_numbers
7767: WHERE parent_lpn_id = p_from_lpn_id;
7768:
7769: l_batch_seq NUMBER := p_batch_seq; --BUG 3544918
7770: BEGIN
8000: ,l_org_id
8001: ,l_sub_code
8002: ,l_loc_id
8003: FROM
8004: wms_license_plate_numbers
8005: WHERE
8006: lpn_id = p_from_lpn_id;
8007: EXCEPTION
8008: WHEN OTHERS THEN
8006: lpn_id = p_from_lpn_id;
8007: EXCEPTION
8008: WHEN OTHERS THEN
8009: IF (l_debug = 1) THEN
8010: DEBUG('from LPN not found in WMS_LICENSE_PLATE_NUMBERS',
8011: 'transfer_contents',9);
8012: END IF;
8013: fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
8014: fnd_msg_pub.ADD;
8026: l_into_context
8027: ,l_to_sub
8028: ,l_to_loc
8029: FROM
8030: wms_license_plate_numbers
8031: WHERE
8032: lpn_id = p_into_lpn_id;
8033: EXCEPTION
8034: WHEN OTHERS THEN
8032: lpn_id = p_into_lpn_id;
8033: EXCEPTION
8034: WHEN OTHERS THEN
8035: IF (l_debug = 1) THEN
8036: DEBUG('INTO LPN not found in WMS_LICENSE_PLATE_NUMBERS',
8037: 'transfer_contents',9);
8038: END IF;
8039: fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
8040: fnd_msg_pub.ADD;
8115: SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
8116: INTO l_count
8117: FROM mtl_txn_request_lines mtrl ,
8118: (SELECT wlpn.lpn_id /*5723418*/
8119: FROM wms_license_plate_numbers wlpn
8120: START WITH wlpn.lpn_id = p_from_lpn_id
8121: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
8122: WHERE mtrl.lpn_id = wlpn.lpn_id
8123: AND mtrl.line_status = 7
8186: mmtt.move_order_line_id --BUG 3435079: use org_id for performace reason
8187: IN ( SELECT mtrl.line_id
8188: FROM mtl_txn_request_lines mtrl,
8189: ( SELECT wlpn.lpn_id /*5723418*/
8190: FROM wms_license_plate_numbers wlpn
8191: START WITH wlpn.lpn_id = p_from_lpn_id
8192: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
8193: WHERE mtrl.organization_id = l_org_id
8194: --BUG 3435079: use org_id for performace reason
8228: mmtt.move_order_line_id --BUG 3435079: use org_id for performace reason
8229: IN ( SELECT mtrl.line_id
8230: FROM mtl_txn_request_lines mtrl ,
8231: (SELECT wlpn.lpn_id /*5723418*/
8232: FROM wms_license_plate_numbers wlpn
8233: START WITH wlpn.lpn_id = p_from_lpn_id
8234: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
8235: WHERE mtrl.organization_id = l_org_id AND --BUG 3435079: use org_id for performace reason
8236: mtrl.lpn_id = wlpn.lpn_id );
8403: SET
8404: wms_process_flag = 2
8405: WHERE
8406: lpn_id IN (SELECT wlpn.lpn_id
8407: FROM wms_license_plate_numbers wlpn
8408: START WITH wlpn.lpn_id = p_from_lpn_id
8409: CONNECT BY PRIOR wlpn.lpn_id =
8410: wlpn.parent_lpn_id);
8411: EXCEPTION
8462:
8463: BEGIN
8464: SELECT lpn_id
8465: bulk collect INTO l_lpn_ids
8466: FROM wms_license_plate_numbers
8467: WHERE parent_lpn_id = p_from_lpn_id;
8468: EXCEPTION
8469: WHEN OTHERS THEN
8470: IF (l_debug = 1) THEN
8711: -- Now deal with Nested LPN. For now, there should be none
8712: BEGIN
8713: SELECT lpn_id
8714: bulk collect INTO l_lpn_ids
8715: FROM wms_license_plate_numbers
8716: WHERE parent_lpn_id = p_from_lpn_id;
8717: EXCEPTION
8718: WHEN OTHERS THEN
8719: IF (l_debug = 1) THEN
8917: -- Deal with Nested LPN first
8918: BEGIN
8919: SELECT lpn_id
8920: bulk collect INTO l_lpn_ids
8921: FROM wms_license_plate_numbers
8922: WHERE parent_lpn_id = p_from_lpn_id;
8923: EXCEPTION
8924: WHEN OTHERS THEN
8925: IF (l_debug = 1) THEN
9402: , mtl_txn_request_headers mtrh
9403: , mtl_system_items_kfv msi
9404: , ( /*5723418*/
9405: SELECT wlpn.lpn_id
9406: FROM wms_license_plate_numbers wlpn
9407: START WITH wlpn.lpn_id = p_lpn_id
9408: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id
9409: ) wlpn
9410: WHERE mtrh.organization_id = p_organization_id
9485: l_subinventory_code mtl_secondary_inventories.secondary_inventory_name%TYPE;
9486: l_locator_id NUMBER; --Locator for complete_putaway
9487: l_lpn_id NUMBER; --p_LPN_ID for complete_putaway???--check
9488: l_to_lpn_id NUMBER; --To LPN ID -- for pack/unpack
9489: l_to_lpn_name wms_license_plate_numbers.license_plate_number%TYPE;
9490: l_revision mtl_item_revisions.revision%TYPE; --p_rev for complete_putaway
9491: l_lot_number mtl_lot_numbers.lot_number%type; --p_lot for complete_putaway
9492: l_mmtt_prm_qty NUMBER; --Lot quantity in primary uom
9493: l_lot_expiration_date DATE; --Lot expiration date
10859: ELSE
10860: --Replace this with LPN_NAME from global_temp table for this task
10861: SELECT license_plate_number
10862: INTO l_to_lpn_name
10863: FROM wms_license_plate_numbers
10864: WHERE lpn_id = l_mol_lpn_id_tbl(i);
10865: END IF; --END IF check MOL LPN and confirmed LPN
10866: END IF; --END IF check lpn_controlled_flag for the sub
10867:
11365:
11366: -- Get the required LPN values
11367: SELECT lpn_context, subinventory_code, locator_id
11368: INTO l_lpn_context, l_lpn_sub, l_lpn_loc_id
11369: FROM wms_license_plate_numbers
11370: WHERE organization_id = p_organization_id
11371: AND lpn_id = p_lpn_id;
11372: l_progress := '20';
11373: IF (l_debug = 1) THEN
11381: BEGIN
11382: SELECT 1, lpn_id, subinventory_code, locator_id, lpn_context,outermost_lpn_id
11383: INTO l_count, l_into_lpn_id, l_into_lpn_sub, l_into_lpn_loc_id, l_into_lpn_context,
11384: l_into_outermost_lpn_id
11385: FROM wms_license_plate_numbers
11386: WHERE license_plate_number = p_into_lpn
11387: AND organization_id = p_organization_id
11388: FOR UPDATE OF lpn_id NOWAIT;
11389: -- lock so no other user can load into this lpn
11495: select NVL(count(1),0)
11496: into l_lpn_has_material
11497: FROM wms_lpn_contents
11498: WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
11499: FROM wms_license_plate_numbers wlpn1
11500: START WITH wlpn1.lpn_id = l_into_outermost_lpn_id
11501: CONNECT BY PRIOR wlpn1.lpn_id = wlpn1.parent_lpn_id);
11502:
11503: SELECT 1
11504: INTO l_count
11505: FROM dual
11506: WHERE EXISTS
11507: (SELECT 'INTO_LPN_EXISTS'
11508: FROM wms_license_plate_numbers wlpn
11509: WHERE wlpn.organization_id = p_organization_id
11510: AND wlpn.lpn_id <> p_lpn_id
11511: AND wlpn.lpn_id = l_into_lpn_id
11512: AND (wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
11515: ( l_lpn_has_material = 0
11516: --NOT EXISTS (SELECT 'LPN_HAS_MATERIAL'
11517: --FROM wms_lpn_contents
11518: --WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
11519: -- FROM wms_license_plate_numbers wlpn1
11520: -- START WITH wlpn1.lpn_id = wlpn.outermost_lpn_id
11521: -- CONNECT BY PRIOR wlpn1.lpn_id = wlpn1.parent_lpn_id)
11522: --)
11523: )
11541: AND wdt.status = 4
11542: AND wdt.person_id = p_employee_id
11543: AND mtrl.line_id = mmtt.move_order_line_id--5650113
11544: AND mtrl.lpn_id IN (SELECT lpn_id--5650113
11545: FROM wms_license_plate_numbers
11546: START WITH lpn_id = wlpn.outermost_lpn_id
11547: CONNECT BY PRIOR lpn_id = parent_lpn_id
11548: )
11549: )
11714: and drop_type='ID'
11715: and row_type = 'Group Task'
11716: and not exists
11717: (select 1
11718: from wms_license_plate_numbers wln
11719: where wln.parent_lpn_id = p_lpn_id);
11720: EXCEPTION
11721: WHEN OTHERS THEN
11722: l_item_count :=2;
11744:
11745: IF (l_qty_gtmp<>l_qty_wlc) THEN
11746: IF (l_debug = 1) THEN
11747: debug(' quantity from global temp table ' || l_qty_gtmp);
11748: debug(' quantity from wms license plate Numbers ' || l_qty_wlc);
11749: debug('Given LPN is not fully allocated!','validate_into_lpn',9);
11750: END IF;
11751: x_validation_passed := 'N';
11752: RETURN;
11765: SELECT 1 INTO l_count
11766: FROM DUAL
11767: WHERE EXISTS (
11768: SELECT 'INTO_LPN_EXISTS'
11769: FROM wms_license_plate_numbers wlpn
11770: WHERE wlpn.organization_id = p_organization_id
11771: AND wlpn.lpn_id = l_into_lpn_id
11772: AND (wlpn.lpn_context = 5 OR
11773: ((wlpn.lpn_context = 1 AND l_lpn_context IN (1,2) ) OR
11781: AND wdt.organization_id = p_organization_id
11782: AND wdt.task_type = 2
11783: AND wdt.status = 4
11784: AND mmtt.lpn_id IN (SELECT wlpn2.lpn_id
11785: FROM wms_license_plate_numbers wlpn2
11786: START WITH wlpn2.lpn_id = wlpn.outermost_lpn_id
11787: CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
11788: )
11789: )
12073: l_into_lpn_project_id
12074: ,l_into_lpn_task_id
12075: FROM
12076: mtl_txn_request_lines mtrl, (SELECT lpn_id
12077: FROM wms_license_plate_numbers
12078: START WITH lpn_id = 1
12079: CONNECT BY PRIOR lpn_id = parent_lpn_id
12080: ) wlpn2
12081: WHERE
12200: FROM
12201: mtl_txn_request_lines mol,
12202: mtl_material_transactions_temp mmtt ,
12203: (SELECT wlpn.lpn_id /*5723418*/
12204: FROM wms_license_plate_numbers wlpn
12205: START WITH wlpn.lpn_id = p_lpn_id
12206: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
12207: WHERE mol.lpn_id = wlpn.lpn_id
12208: AND mol.organization_id = p_organization_id
12294: , mmtt.transaction_uom
12295: FROM mtl_material_transactions_temp mmtt,
12296: mtl_txn_request_lines mtrl ,
12297: (SELECT lpn_id /*5723418*/
12298: FROM wms_license_plate_numbers
12299: START WITH lpn_id = p_lpn_id
12300: CONNECT BY PRIOR lpn_id = parent_lpn_id) wlpn
12301: WHERE mmtt.move_order_line_id = mtrl.line_id
12302: AND mmtt.organization_id = p_organization_id
12622:
12623:
12624: CURSOR lpn_cursor IS
12625: SELECT lpn_id
12626: FROM wms_license_plate_numbers
12627: START WITH lpn_id = p_lpn_id
12628: CONNECT BY parent_lpn_id = PRIOR lpn_id;
12629:
12630: CURSOR c_mmtt_cursor(p_lpn number) IS
12667:
12668: -- Get LPN Context
12669: SELECT lpn_context
12670: INTO l_lpn_context
12671: FROM wms_license_plate_numbers
12672: WHERE lpn_id = p_lpn_id;
12673:
12674: l_progress := 20;
12675: