[Home] [Help]
676: mmtt.primary_quantity,
677: mmtt.secondary_transaction_quantity, -- 9037915
678: mmtt.secondary_uom_code -- 9037915
679: FROM mtl_material_transactions_temp mmtt
680: ,mtl_txn_request_lines mtrl
681: ,wms_dispatched_tasks wdt
682: --,mtl_item_locations milk
683: ,mtl_system_items_kfv msik
684: ,mtl_txn_request_headers mtrh
2567: ,COUNT(DISTINCT cartonization_id)
2568: INTO l_loc_count
2569: ,l_to_lpn_count
2570: FROM mtl_material_transactions_temp mmtt
2571: ,mtl_txn_request_lines mtrl
2572: WHERE mmtt.move_order_line_id = mtrl.line_id
2573: AND mtrl.lpn_id = l_lpn_id_tab(i);
2574: l_progress := '150';
2575:
2655: INTO l_qty_disc_count
2656: FROM dual
2657: WHERE EXISTS (
2658: SELECT mtrl.line_id
2659: FROM mtl_txn_request_lines mtrl
2660: ,mtl_txn_request_headers mtrh
2661: WHERE 1 = 1
2662: AND mtrl.header_id = mtrh.header_id
2663: AND mtrh.move_order_type = 6
3129: FROM wms_license_plate_numbers wln
3130: START WITH LPN_ID IN (
3131: SELECT DISTINCT wln2.outermost_lpn_id
3132: FROM wms_dispatched_tasks wdt
3133: ,mtl_txn_request_lines mtrl
3134: ,mtl_material_transactions_temp mmtt
3135: ,mtl_txn_request_headers mtrh
3136: ,wms_license_plate_numbers wln2
3137: WHERE mtrh.header_id = mtrl.header_id
3403: mmtt.error_code,
3404: mmtt.error_explanation
3405: --R12 End
3406: FROM mtl_material_transactions_temp mmtt
3407: ,mtl_txn_request_lines mtrl
3408: ,wms_dispatched_tasks wdt
3409: --,mtl_item_locations milk
3410: ,mtl_system_items_kfv msik
3411: ,mtl_txn_request_headers mtrh
3833: -- along with the MMTTs of the contents of all its child LPNs also.
3834: --Bug5723418.Performance fix.Moved wlpn to FROM clause. Added hint
3835: -- Performane fix as a part of bug 7143123
3836: CURSOR c_all_mmtt_cursor IS -- Bug 7453925
3837: SELECT /*+ ORDERED USE_NL(mtrl mmtt) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
3838: mmtt.transaction_temp_id
3839: FROM ( SELECT lpn_id FROM wms_license_plate_numbers
3840: START WITH lpn_id = p_lpn_id
3841: CONNECT BY PRIOR lpn_id = parent_lpn_id
3839: FROM ( SELECT lpn_id FROM wms_license_plate_numbers
3840: START WITH lpn_id = p_lpn_id
3841: CONNECT BY PRIOR lpn_id = parent_lpn_id
3842: ) wlpn,
3843: mtl_txn_request_lines mtrl,
3844: mtl_material_transactions_temp mmtt
3845: WHERE mtrl.line_id = mmtt.move_order_line_id
3846: AND mtrl.line_status = 7
3847: AND mtrl.lpn_id = wlpn.lpn_id ;
4018: -- along with the MMTTs of the contents of all its child LPNs also.
4019: --Bug5723418.Performance fix.Moved wlpn to FROM clause.Also added hint
4020: -- Performane fix as a part of bug 7143123
4021: CURSOR c_all_mmtt_cursor IS -- Bug 7453925
4022: SELECT /*+ ORDERED USE_NL(mtrl mmtt) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
4023: mmtt.transaction_temp_id
4024: FROM ( SELECT lpn_id FROM wms_license_plate_numbers
4025: START WITH lpn_id = p_lpn_id
4026: CONNECT BY PRIOR lpn_id = parent_lpn_id
4024: FROM ( SELECT lpn_id FROM wms_license_plate_numbers
4025: START WITH lpn_id = p_lpn_id
4026: CONNECT BY PRIOR lpn_id = parent_lpn_id
4027: ) wlpn,
4028: mtl_txn_request_lines mtrl,
4029: mtl_material_transactions_temp mmtt
4030: WHERE mtrl.line_id = mmtt.move_order_line_id
4031: AND mtrl.line_status = 7
4032: AND mtrl.lpn_id = wlpn.lpn_id ;
5292: -- along with the MMTTs of the contents of all its child LPNs also.
5293: --Bug5723418.Performance fix.Moved wlpn to FROM clause.
5294: -- Performane fix as a part of bug 7143123
5295: CURSOR c_all_mmtt_cursor IS
5296: SELECT /*+ ORDERED USE_NL(WLPN MTRL MMTT) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
5297: MMTT.TRANSACTION_TEMP_ID
5298: FROM
5299: ( SELECT LPN_ID
5300: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
5297: MMTT.TRANSACTION_TEMP_ID
5298: FROM
5299: ( SELECT LPN_ID
5300: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
5301: MTL_TXN_REQUEST_LINES MTRL,
5302: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
5303: WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
5304: AND LINE_STATUS = 7
5305: AND MTRL.LPN_ID = WLPN.LPN_ID ;
5543: -- This cursor will get the MMTTs assoicated with the move order line passed
5544: CURSOR c_mol_mmtt_cursor IS
5545: SELECT mmtt.transaction_temp_id
5546: FROM mtl_material_transactions_temp mmtt
5547: ,mtl_txn_request_lines mtrl
5548: WHERE mtrl.line_id = mmtt.move_order_line_id
5549: AND mtrl.line_id = p_mo_line_id
5550: AND mtrl.line_status = 7
5551: AND mtrl.organization_id = p_org_id;
5787: -- Cleanup shouldn't be called if there is not WDT and hence added a join with WDT.
5788: CURSOR c_mol_mmtt_cursor IS
5789: SELECT mmtt.transaction_temp_id
5790: FROM mtl_material_transactions_temp mmtt
5791: ,mtl_txn_request_lines mtrl
5792: ,wms_dispatched_tasks wdt
5793: ,(SELECT lpn_id FROM wms_license_plate_numbers /*Bug5723418.*/
5794: START WITH lpn_id = p_lpn_id
5795: CONNECT BY PRIOR lpn_id = parent_lpn_id
5822: -- have drop active op plan or tasks that have wdt status 4 (loaded).
5823: CURSOR c_all_mmtt_cursor IS
5824: SELECT mmtt.transaction_temp_id
5825: FROM wms_dispatched_tasks wdt
5826: ,mtl_txn_request_lines mtrl
5827: ,mtl_material_transactions_temp mmtt
5828: ,mtl_txn_request_headers mtrh
5829: WHERE mtrh.header_id = mtrl.header_id
5830: AND mtrh.move_order_type = 6
5855: AND p_drop_all <> 'Y'
5856: UNION ALL
5857: SELECT mmtt.transaction_temp_id
5858: FROM wms_dispatched_tasks wdt
5859: ,mtl_txn_request_lines mtrl
5860: ,mtl_material_transactions_temp mmtt
5861: ,mtl_txn_request_headers mtrh
5862: WHERE mtrh.header_id = mtrl.header_id
5863: AND mtrh.move_order_type = 6
6093: , l_mmtt_item_id
6094: , l_mol_uom
6095: , l_wms_process_flag
6096: FROM mtl_material_transactions_temp mmtt
6097: , mtl_txn_request_lines mtrl
6098: WHERE mmtt.transaction_temp_id = l_tempid_tab(i)
6099: AND mmtt.move_order_line_id = mtrl.line_id;
6100: EXCEPTION
6101: WHEN OTHERS THEN
6166: END IF;
6167:
6168: BEGIN
6169: --update MOL
6170: UPDATE mtl_txn_request_lines mol
6171: SET quantity = quantity - l_mmtt_qty_mol_uom
6172: , quantity_detailed = quantity_detailed - l_mmtt_qty_mol_uom
6173: , line_status = Decode(quantity-Nvl(quantity_delivered,0)-l_mmtt_qty_mol_uom
6174: ,0
6218: END IF;--END IF l_op_plan_id IS NULL AND l_lpn_context = g_lpn_context_inv) THEN
6219:
6220: IF (l_wms_process_flag = 2) THEN
6221: BEGIN
6222: UPDATE mtl_txn_request_lines
6223: SET wms_process_flag = 1
6224: WHERE line_id = l_mol_id;
6225: EXCEPTION
6226: WHEN OTHERS THEN
6309: END IF;--IF (p_call_type = g_ct_inspect_b4_tm) THEN
6310:
6311: --BUG 5075410
6312: BEGIN
6313: UPDATE mtl_txn_request_lines
6314: SET wms_process_flag = 1
6315: WHERE line_id IN (SELECT move_order_line_id
6316: FROM mtl_material_transactions_temp
6317: WHERE transaction_temp_id = l_tempid_tab(i))
6560: SELECT DISTINCT 'Y'
6561: , wooi.last_updated_by
6562: INTO l_drop_active
6563: , l_userid
6564: FROM mtl_txn_request_lines mtrl,
6565: mtl_material_transactions_temp mmtt,
6566: wms_op_operation_instances wooi ,
6567: (SELECT wlpn.lpn_id /*5723418*/
6568: FROM wms_license_plate_numbers wlpn
6650: SELECT 'Y' INTO x_loaded_status FROM dual where exists
6651: (SELECT 1 from
6652: wms_dispatched_tasks W,
6653: mtl_material_transactions_temp t,
6654: mtl_txn_request_lines M
6655: WHERE m.lpn_id = p_lpn_id
6656: AND m.organization_id = p_org_id
6657: AND m.line_status <> 5
6658: AND m.quantity-Nvl(m.quantity_delivered,0)>0
6676: l_mmtt_qty := 0;
6677: SELECT Sum(mmtt.primary_quantity) INTO l_mmtt_qty
6678: FROM wms_dispatched_tasks W,
6679: mtl_material_transactions_temp mmtt,
6680: mtl_txn_request_lines M
6681: WHERE m.lpn_id = p_lpn_id
6682: AND m.organization_id = x.organization_id
6683: AND m.line_status <> 5
6684: AND m.quantity-Nvl(m.quantity_delivered,0)>0
7730: END IF;
7731:
7732: --BUG 4345714: If any line is not detailed, then fail.
7733: BEGIN
7734: SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
7735: INTO l_discrepancy
7736: FROM
7737: (SELECT wlpn.lpn_id /*5723418*/
7738: FROM wms_license_plate_numbers wlpn
7737: (SELECT wlpn.lpn_id /*5723418*/
7738: FROM wms_license_plate_numbers wlpn
7739: START WITH wlpn.lpn_id = p_from_lpn_id
7740: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn,
7741: mtl_txn_request_lines mtrl -- Bug 14372071
7742: WHERE mtrl.lpn_id = wlpn.lpn_id
7743: AND mtrl.line_status = 7
7744: AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) <> Nvl(mtrl.quantity_detailed,0)
7745: AND mtrl.organization_id = p_org_id;
7820: FROM mtl_material_transactions_temp mmtt
7821: WHERE mmtt.organization_id = p_org_id
7822: AND mmtt.move_order_line_id IN
7823: ( SELECT mtrl.line_id
7824: FROM mtl_txn_request_lines mtrl ,
7825: (SELECT wlpn.lpn_id /* 5723418 */
7826: FROM wms_license_plate_numbers wlpn
7827: START WITH wlpn.lpn_id = p_from_lpn_id
7828: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn
7989: wms_process_flag NUMBER,
7990: inspect_status NUMBER)
7991: IS
7992: BEGIN
7993: UPDATE mtl_txn_request_lines
7994: SET Reference_id = decode(ReferenceId,-9999,referenceId,NULL,NULL,referenceId),
7995: Reference = decode(Reference,'-9999',reference,NULL,NULL,reference),
7996: Reference_type_code = decode(Reference_type_code,'-9999',reference_type_code,NULL,NULL,reference_type_code),
7997: lpn_id = decode(lpn_id,-9999,lpn_id,NULL,NULL,lpn_id),
8975:
8976:
8977: --BUG 4345714: Change the way quantities is validated from 11.5.10
8978: BEGIN
8979: SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
8980: INTO l_count
8981: FROM
8982: (SELECT wlpn.lpn_id /*5723418*/
8983: FROM wms_license_plate_numbers wlpn
8982: (SELECT wlpn.lpn_id /*5723418*/
8983: FROM wms_license_plate_numbers wlpn
8984: START WITH wlpn.lpn_id = p_from_lpn_id
8985: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn,
8986: mtl_txn_request_lines mtrl -- Bug 14372071
8987: WHERE mtrl.lpn_id = wlpn.lpn_id
8988: AND mtrl.line_status = 7
8989: AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) <> Nvl(mtrl.quantity_detailed,0)
8990: AND mtrl.organization_id = l_org_id;
9049: mmtt.transaction_action_id = 27)) AND
9050: mmtt.organization_id = l_org_id AND
9051: mmtt.move_order_line_id --BUG 3435079: use org_id for performace reason
9052: IN ( SELECT mtrl.line_id
9053: FROM mtl_txn_request_lines mtrl,
9054: ( SELECT wlpn.lpn_id /*5723418*/
9055: FROM wms_license_plate_numbers wlpn
9056: START WITH wlpn.lpn_id = p_from_lpn_id
9057: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
9091: WHERE -- suggestion mmtts?
9092: mmtt.organization_id = l_org_id AND
9093: mmtt.move_order_line_id --BUG 3435079: use org_id for performace reason
9094: IN ( SELECT mtrl.line_id
9095: FROM mtl_txn_request_lines mtrl ,
9096: (SELECT wlpn.lpn_id /*5723418*/
9097: FROM wms_license_plate_numbers wlpn
9098: START WITH wlpn.lpn_id = p_from_lpn_id
9099: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
9263:
9264: -- Update WMS_PROCESS_FLAG of MOL to 2
9265: BEGIN
9266: UPDATE
9267: mtl_txn_request_lines
9268: SET
9269: wms_process_flag = 2
9270: WHERE
9271: lpn_id IN (SELECT wlpn.lpn_id
9690:
9691:
9692: BEGIN
9693: UPDATE
9694: mtl_txn_request_lines
9695: SET
9696: lpn_id = p_into_lpn_id
9697: WHERE
9698: lpn_id = p_from_lpn_id;
10040: END IF;
10041:
10042: BEGIN
10043: UPDATE
10044: mtl_txn_request_lines
10045: SET
10046: lpn_id = p_into_lpn_id
10047: WHERE
10048: lpn_id = p_from_lpn_id;
10189: , mtrl.secondary_quantity --OPM Convergence
10190: , NVL(mtrl.secondary_quantity_detailed, 0) --OPM Convergence
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
10210: , mtrl.secondary_quantity --OPM Convergence
10211: , NVL(mtrl.secondary_quantity_detailed, 0) --OPM Convergence
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
10269: , mmtt.secondary_uom_code -- 9037915
10270: , mtrl.inspection_status
10271: , msi.primary_uom_code
10272: FROM mtl_material_transactions_temp mmtt
10273: , mtl_txn_request_lines mtrl
10274: , mtl_txn_request_headers mtrh
10275: , mtl_system_items_kfv msi
10276: , ( /*5723418*/
10277: SELECT wlpn.lpn_id
10946: SUM(mmtt.secondary_transaction_quantity) -- 9037915
10947: INTO l_prim_qty_consumable,
10948: l_sec_qty_consumable
10949: FROM mtl_material_transactions_temp mmtt
10950: , mtl_txn_request_lines mtrl
10951: WHERE mmtt.transaction_header_id = p_txn_header_id
10952: AND mtrl.line_id = l_mol_line_id
10953: AND mtrl.line_id = mmtt.move_order_line_id;
10954: EXCEPTION
12511: */
12512: (EXISTS (SELECT 'LOADED_BY_SAME_USER'
12513: FROM mtl_material_transactions_temp mmtt,
12514: wms_dispatched_tasks wdt,--5650113
12515: mtl_txn_request_lines mtrl--5650113
12516: WHERE mmtt.organization_id = p_organization_id
12517: AND mmtt.transaction_temp_id = wdt.transaction_temp_id
12518: AND wdt.organization_id = p_organization_id
12519: AND wdt.task_type = 2
12721: ELSE
12722: BEGIN
12723: SELECT MAX(mtrl.backorder_delivery_detail_id)
12724: INTO l_bo_d_to_id
12725: FROM mtl_txn_request_lines mtrl
12726: WHERE EXISTS (SELECT 1
12727: FROM rcv_transactions_interface rti, mtl_material_transactions_temp mmtt
12728: WHERE rti.transfer_lpn_id = l_into_lpn_id
12729: AND rti.mmtt_temp_id = mmtt.transaction_temp_id
13174: INTO
13175: l_into_lpn_project_id
13176: ,l_into_lpn_task_id
13177: FROM
13178: mtl_txn_request_lines mtrl, (SELECT lpn_id
13179: FROM wms_license_plate_numbers
13180: START WITH lpn_id = l_into_lpn_id
13181: CONNECT BY PRIOR lpn_id = parent_lpn_id
13182: ) wlpn2
13300:
13301: CURSOR c_all_mmtt_cursor(l_org_id NUMBER)
13302: IS
13303: SELECT
13304: /*+ ORDERED USE_NL(mtrl mmtt) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
13305: mmtt.transaction_temp_id
13306: FROM
13307: (
13308: SELECT
13313: START WITH lpn_id = p_lpn_id
13314: CONNECT BY PRIOR lpn_id = parent_lpn_id
13315: )
13316: wlpn,
13317: mtl_txn_request_lines mtrl,
13318: mtl_material_transactions_temp mmtt
13319: WHERE
13320: (
13321: mtrl.line_id = mmtt.move_order_line_id
13371: NVL(SUM(quantity-NVL(quantity_delivered,0)),0) mtrl_quantity,
13372: inventory_item_id,
13373: uom_code
13374: FROM
13375: mtl_txn_request_lines
13376: WHERE
13377: lpn_id IN
13378: (
13379: SELECT
13439: (
13440: SELECT
13441: 1
13442: FROM
13443: mtl_txn_request_lines
13444: WHERE
13445: lpn_id = wlc.parent_lpn_id
13446: AND organization_id = wlc.organization_id
13447: AND inventory_item_id = wlc.inventory_item_id
13453: SELECT
13454: 1
13455: FROM
13456: wms_license_plate_numbers wlpn,
13457: mtl_txn_request_lines mtrl
13458: WHERE
13459: wlpn.lpn_id = mtrl.lpn_id
13460: AND
13461: (
13479: IS
13480: SELECT
13481: 1
13482: FROM
13483: mtl_txn_request_lines mtrl,
13484: wms_lpn_contents wlc
13485: WHERE
13486: mtrl.lpn_id = wlc.parent_lpn_id
13487: AND mtrl.lot_number IS NOT NULL
13698: AND transaction_type <> 'SHIP'
13699: AND processing_status_code <> 'WSH_INTERFACED';
13700:
13701: UPDATE
13702: mtl_txn_request_lines
13703: SET
13704: quantity_delivered = 0,
13705: quantity_detailed = 0,
13706: line_status = 7,
13710: (
13711: SELECT
13712: line_id
13713: FROM
13714: mtl_txn_request_lines
13715: WHERE
13716: lpn_id IN
13717: (
13718: SELECT
13770: (
13771: SELECT
13772: line_id
13773: FROM
13774: mtl_txn_request_lines
13775: WHERE
13776: lpn_id IN
13777: (
13778: SELECT
13803: (
13804: SELECT
13805: line_id
13806: FROM
13807: mtl_txn_request_lines
13808: WHERE
13809: lpn_id IN
13810: (
13811: SELECT
13836: (
13837: SELECT
13838: line_id
13839: FROM
13840: mtl_txn_request_lines
13841: WHERE
13842: lpn_id IN
13843: (
13844: SELECT
13869: (
13870: SELECT
13871: line_id
13872: FROM
13873: mtl_txn_request_lines
13874: WHERE
13875: lpn_id IN
13876: (
13877: SELECT
13934: (
13935: SELECT
13936: line_id
13937: FROM
13938: mtl_txn_request_lines
13939: WHERE
13940: lpn_id IN
13941: (
13942: SELECT
13978: (
13979: SELECT
13980: line_id
13981: FROM
13982: mtl_txn_request_lines
13983: WHERE
13984: lpn_id IN
13985: (
13986: SELECT
14044: (
14045: SELECT
14046: line_id
14047: FROM
14048: mtl_txn_request_lines
14049: WHERE
14050: lpn_id IN
14051: (
14052: SELECT
14078: (
14079: SELECT
14080: line_id
14081: FROM
14082: mtl_txn_request_lines
14083: WHERE
14084: lpn_id IN
14085: (
14086: SELECT
14108: AND transaction_type <> 'SHIP'
14109: AND PROCESSING_STATUS_CODE <> 'WSH_INTERFACED';
14110: --update MTRL
14111: UPDATE
14112: mtl_txn_request_lines
14113: SET
14114: quantity_delivered = 0,
14115: quantity_detailed = 0,
14116: line_status = 7,
14120: (
14121: SELECT
14122: line_id
14123: FROM
14124: mtl_txn_request_lines
14125: WHERE
14126: lpn_id IN
14127: (
14128: SELECT
14270: l_backorder_delivery_ids,
14271: l_crossdock_types,
14272: l_wip_supply_types
14273: FROM
14274: mtl_txn_request_lines mol,
14275: mtl_material_transactions_temp mmtt ,
14276: (SELECT wlpn.lpn_id /*5723418*/
14277: FROM wms_license_plate_numbers wlpn
14278: START WITH wlpn.lpn_id = p_lpn_id
14365: , mmtt.locator_id
14366: , mmtt.transaction_quantity
14367: , mmtt.transaction_uom
14368: FROM mtl_material_transactions_temp mmtt,
14369: mtl_txn_request_lines mtrl ,
14370: (SELECT lpn_id /*5723418*/
14371: FROM wms_license_plate_numbers
14372: START WITH lpn_id = p_lpn_id
14373: CONNECT BY PRIOR lpn_id = parent_lpn_id) wlpn
14547:
14548: --Bug#6968476.Added xdock type.SO xdk type can be NULL
14549: CURSOR c_lpn_bo_dd_id (v_lpn_id NUMBER)IS
14550: SELECT NVL(backorder_delivery_detail_id,-1) , nvl(crossdock_type,1)
14551: FROM mtl_txn_request_lines
14552: WHERE lpn_id = v_lpn_id
14553: AND line_status = 7 ; --9505772 need only open lines.
14554:
14555: l_count NUMBER;
14771: mmtt.inventory_item_id,
14772: mol.crossdock_type,
14773: mol.backorder_delivery_detail_id
14774: FROM mtl_material_transactions_temp mmtt,
14775: mtl_txn_request_lines mol
14776: WHERE mmtt.move_order_line_id(+) = mol.line_id
14777: AND mmtt.wms_task_type=2
14778: AND mol.organization_id = mmtt.organization_id(+)
14779: AND mol.organization_id=p_org_id
14778: AND mol.organization_id = mmtt.organization_id(+)
14779: AND mol.organization_id=p_org_id
14780: AND mol.lpn_id = p_lpn
14781: AND mol.LINE_STATUS <> 5
14782: AND EXISTS (SELECT 1 FROM mtl_txn_request_lines
14783: WHERE lpn_id = p_lpn
14784: AND BACKORDER_DELIVERY_DETAIL_ID IS NOT NULL);
14785:
14786: l_mmtt_rec c_mmtt_cursor%ROWTYPE;
14977:
14978: END IF;
14979: l_progress := 170;
14980:
14981: UPDATE mtl_txn_request_lines SET quantity_detailed=NULL WHERE line_id=l_mmtt_rec.move_order_line_id;
14982:
14983: l_progress := 180;
14984:
14985: END LOOP;