[Home] [Help]
1946: ,COUNT(DISTINCT cartonization_id)
1947: INTO l_loc_count
1948: ,l_to_lpn_count
1949: FROM mtl_material_transactions_temp mmtt
1950: ,mtl_txn_request_lines mtrl
1951: WHERE mmtt.move_order_line_id = mtrl.line_id
1952: AND mtrl.lpn_id = l_lpn_id_tab(i);
1953: l_progress := '150';
1954:
2034: INTO l_qty_disc_count
2035: FROM dual
2036: WHERE EXISTS (
2037: SELECT mtrl.line_id
2038: FROM mtl_txn_request_lines mtrl
2039: ,mtl_txn_request_headers mtrh
2040: WHERE 1 = 1
2041: AND mtrl.header_id = mtrh.header_id
2042: AND mtrh.move_order_type = 6
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
2758: mmtt.error_code,
2759: mmtt.error_explanation
2760: --R12 End
2761: FROM mtl_material_transactions_temp mmtt
2762: ,mtl_txn_request_lines mtrl
2763: ,wms_dispatched_tasks wdt
2764: --,mtl_item_locations milk
2765: ,mtl_system_items_kfv msik
2766: ,mtl_txn_request_headers mtrh
3132: -- along with the MMTTs of the contents of all its child LPNs also.
3133: --Bug5723418.Performance fix.Moved wlpn to FROM clause. Added hint
3134: -- Performane fix as a part of bug 7143123
3135: CURSOR c_all_mmtt_cursor IS
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,
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
3145: AND MTRL.LPN_ID = WLPN.LPN_ID ;
3315: -- along with the MMTTs of the contents of all its child LPNs also.
3316: --Bug5723418.Performance fix.Moved wlpn to FROM clause.Also added hint
3317: -- Performane fix as a part of bug 7143123
3318: CURSOR c_all_mmtt_cursor IS
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,
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
3328: AND MTRL.LPN_ID = WLPN.LPN_ID ;
4587: -- along with the MMTTs of the contents of all its child LPNs also.
4588: --Bug5723418.Performance fix.Moved wlpn to FROM clause.
4589: -- Performane fix as a part of bug 7143123
4590: CURSOR c_all_mmtt_cursor IS
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,
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
4600: AND MTRL.LPN_ID = WLPN.LPN_ID ;
4838: -- This cursor will get the MMTTs assoicated with the move order line passed
4839: CURSOR c_mol_mmtt_cursor IS
4840: SELECT mmtt.transaction_temp_id
4841: FROM mtl_material_transactions_temp mmtt
4842: ,mtl_txn_request_lines mtrl
4843: WHERE mtrl.line_id = mmtt.move_order_line_id
4844: AND mtrl.line_id = p_mo_line_id
4845: AND mtrl.line_status = 7
4846: AND mtrl.organization_id = p_org_id;
5080: -- Cleanup shouldn't be called if there is not WDT and hence added a join with WDT.
5081: CURSOR c_mol_mmtt_cursor IS
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
5115: -- have drop active op plan or tasks that have wdt status 4 (loaded).
5116: CURSOR c_all_mmtt_cursor IS
5117: SELECT mmtt.transaction_temp_id
5118: FROM wms_dispatched_tasks wdt
5119: ,mtl_txn_request_lines mtrl
5120: ,mtl_material_transactions_temp mmtt
5121: ,mtl_txn_request_headers mtrh
5122: WHERE mtrh.header_id = mtrl.header_id
5123: AND mtrh.move_order_type = 6
5148: AND p_drop_all <> 'Y'
5149: UNION ALL
5150: SELECT mmtt.transaction_temp_id
5151: FROM wms_dispatched_tasks wdt
5152: ,mtl_txn_request_lines mtrl
5153: ,mtl_material_transactions_temp mmtt
5154: ,mtl_txn_request_headers mtrh
5155: WHERE mtrh.header_id = mtrl.header_id
5156: AND mtrh.move_order_type = 6
5364: , l_mmtt_item_id
5365: , l_mol_uom
5366: , l_wms_process_flag
5367: FROM mtl_material_transactions_temp mmtt
5368: , mtl_txn_request_lines mtrl
5369: WHERE mmtt.transaction_temp_id = l_tempid_tab(i)
5370: AND mmtt.move_order_line_id = mtrl.line_id;
5371: EXCEPTION
5372: WHEN OTHERS THEN
5437: END IF;
5438:
5439: BEGIN
5440: --update MOL
5441: UPDATE mtl_txn_request_lines mol
5442: SET quantity = quantity - l_mmtt_qty_mol_uom
5443: , quantity_detailed = quantity_detailed - l_mmtt_qty_mol_uom
5444: , line_status = Decode(quantity-Nvl(quantity_delivered,0)-l_mmtt_qty_mol_uom
5445: ,0
5489: END IF;--END IF l_op_plan_id IS NULL AND l_lpn_context = g_lpn_context_inv) THEN
5490:
5491: IF (l_wms_process_flag = 2) THEN
5492: BEGIN
5493: UPDATE mtl_txn_request_lines
5494: SET wms_process_flag = 1
5495: WHERE line_id = l_mol_id;
5496: EXCEPTION
5497: WHEN OTHERS THEN
5580: END IF;--IF (p_call_type = g_ct_inspect_b4_tm) THEN
5581:
5582: --BUG 5075410
5583: BEGIN
5584: UPDATE mtl_txn_request_lines
5585: SET wms_process_flag = 1
5586: WHERE line_id IN (SELECT move_order_line_id
5587: FROM mtl_material_transactions_temp
5588: WHERE transaction_temp_id = l_tempid_tab(i))
5828: SELECT DISTINCT 'Y'
5829: , wooi.last_updated_by
5830: INTO l_drop_active
5831: , l_userid
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
5918: SELECT 'Y' INTO x_loaded_status FROM dual where exists
5919: (SELECT 1 from
5920: wms_dispatched_tasks W,
5921: mtl_material_transactions_temp t,
5922: mtl_txn_request_lines M
5923: WHERE m.lpn_id = p_lpn_id
5924: AND m.organization_id = p_org_id
5925: AND m.line_status <> 5
5926: AND m.quantity-Nvl(m.quantity_delivered,0)>0
6868: END IF;
6869:
6870: --BUG 4345714: If any line is not detailed, then fail.
6871: BEGIN
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
6870: --BUG 4345714: If any line is not detailed, then fail.
6871: BEGIN
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
6958: FROM mtl_material_transactions_temp mmtt
6959: WHERE mmtt.organization_id = p_org_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
7127: wms_process_flag NUMBER,
7128: inspect_status NUMBER)
7129: IS
7130: BEGIN
7131: UPDATE mtl_txn_request_lines
7132: SET Reference_id = decode(ReferenceId,-9999,referenceId,NULL,NULL,referenceId),
7133: Reference = decode(Reference,'-9999',reference,NULL,NULL,reference),
7134: Reference_type_code = decode(Reference_type_code,'-9999',reference_type_code,NULL,NULL,reference_type_code),
7135: lpn_id = decode(lpn_id,-9999,lpn_id,NULL,NULL,lpn_id),
8111:
8112:
8113: --BUG 4345714: Change the way quantities is validated from 11.5.10
8114: BEGIN
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
8113: --BUG 4345714: Change the way quantities is validated from 11.5.10
8114: BEGIN
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
8184: mmtt.transaction_action_id = 27)) AND
8185: mmtt.organization_id = l_org_id AND
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
8226: WHERE -- suggestion mmtts?
8227: mmtt.organization_id = l_org_id AND
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
8398:
8399: -- Update WMS_PROCESS_FLAG of MOL to 2
8400: BEGIN
8401: UPDATE
8402: mtl_txn_request_lines
8403: SET
8404: wms_process_flag = 2
8405: WHERE
8406: lpn_id IN (SELECT wlpn.lpn_id
8825:
8826:
8827: BEGIN
8828: UPDATE
8829: mtl_txn_request_lines
8830: SET
8831: lpn_id = p_into_lpn_id
8832: WHERE
8833: lpn_id = p_from_lpn_id;
9171: END IF;
9172:
9173: BEGIN
9174: UPDATE
9175: mtl_txn_request_lines
9176: SET
9177: lpn_id = p_into_lpn_id
9178: WHERE
9179: lpn_id = p_from_lpn_id;
9320: , mtrl.secondary_quantity --OPM Convergence
9321: , NVL(mtrl.secondary_quantity_detailed, 0) --OPM Convergence
9322: , NVL(mtrl.secondary_quantity_delivered, 0) --OPM Convergence
9323: , mtrl.secondary_uom_code --OPM Convergence
9324: FROM mtl_txn_request_lines mtrl
9325: WHERE mtrl.line_id IN (SELECT DISTINCT gtmp.move_order_line_id
9326: FROM wms_putaway_group_tasks_gtmp gtmp
9327: WHERE gtmp.group_id = p_group_id
9328: AND gtmp.transaction_header_id = p_txn_header_id
9340: , mtrl.secondary_quantity --OPM Convergence
9341: , NVL(mtrl.secondary_quantity_detailed, 0) --OPM Convergence
9342: , NVL(mtrl.secondary_quantity_delivered, 0) --OPM Convergence
9343: , mtrl.secondary_uom_code --OPM Convergence
9344: FROM mtl_txn_request_lines mtrl
9345: WHERE mtrl.line_id IN (SELECT DISTINCT gtmp.move_order_line_id
9346: FROM wms_putaway_group_tasks_gtmp gtmp
9347: WHERE gtmp.group_id = p_group_id
9348: AND gtmp.transaction_header_id = p_txn_header_id
9397: , mmtt.secondary_transaction_quantity --OPM Convergence
9398: , mtrl.inspection_status
9399: , msi.primary_uom_code
9400: FROM mtl_material_transactions_temp mmtt
9401: , mtl_txn_request_lines mtrl
9402: , mtl_txn_request_headers mtrh
9403: , mtl_system_items_kfv msi
9404: , ( /*5723418*/
9405: SELECT wlpn.lpn_id
10062: BEGIN
10063: SELECT SUM(mmtt.primary_quantity)
10064: INTO l_prim_qty_consumable
10065: FROM mtl_material_transactions_temp mmtt
10066: , mtl_txn_request_lines mtrl
10067: WHERE mmtt.transaction_header_id = p_txn_header_id
10068: AND mtrl.line_id = l_mol_line_id
10069: AND mtrl.line_id = mmtt.move_order_line_id;
10070: EXCEPTION
11532: */
11533: (EXISTS (SELECT 'LOADED_BY_SAME_USER'
11534: FROM mtl_material_transactions_temp mmtt,
11535: wms_dispatched_tasks wdt,--5650113
11536: mtl_txn_request_lines mtrl--5650113
11537: WHERE mmtt.organization_id = p_organization_id
11538: AND mmtt.transaction_temp_id = wdt.transaction_temp_id
11539: AND wdt.organization_id = p_organization_id
11540: AND wdt.task_type = 2
12072: INTO
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
12197: l_backorder_delivery_ids,
12198: l_crossdock_types,
12199: l_wip_supply_types
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
12292: , mmtt.locator_id
12293: , mmtt.transaction_quantity
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
12473:
12474: CURSOR c_lpn_bo_dd_id (v_lpn_id NUMBER)IS
12475: SELECT mtrl.backorder_delivery_detail_id,
12476: wda.delivery_id
12477: FROM mtl_txn_request_lines mtrl,
12478: wsh_delivery_assignments_v wda
12479: WHERE lpn_id = v_lpn_id
12480: AND mtrl.backorder_delivery_detail_id = wda.delivery_detail_id;
12481:
12638: mmtt.inventory_item_id,
12639: mol.crossdock_type,
12640: mol.backorder_delivery_detail_id
12641: FROM mtl_material_transactions_temp mmtt,
12642: mtl_txn_request_lines mol
12643: WHERE mmtt.move_order_line_id(+) = mol.line_id
12644: AND mmtt.wms_task_type=2
12645: AND mol.organization_id = mmtt.organization_id(+)
12646: AND mol.organization_id=p_org_id
12645: AND mol.organization_id = mmtt.organization_id(+)
12646: AND mol.organization_id=p_org_id
12647: AND mol.lpn_id = p_lpn
12648: AND mol.LINE_STATUS <> 5
12649: AND EXISTS (SELECT 1 FROM mtl_txn_request_lines
12650: WHERE lpn_id = p_lpn
12651: AND BACKORDER_DELIVERY_DETAIL_ID IS NOT NULL);
12652:
12653: l_mmtt_rec c_mmtt_cursor%ROWTYPE;
12844:
12845: END IF;
12846: l_progress := 170;
12847:
12848: UPDATE mtl_txn_request_lines SET quantity_detailed=NULL WHERE line_id=l_mmtt_rec.move_order_line_id;
12849:
12850: l_progress := 180;
12851:
12852: END LOOP;