[Home] [Help]
103: SELECT mmtt.move_order_line_id
104: , mmtt.transaction_source_type_id
105: , mmtt.transaction_action_id
106: , mmtt.wms_task_type
107: FROM mtl_material_transactions_temp mmtt
108: WHERE mmtt.transaction_temp_id = p_txn_tmp_id;
109:
110: CURSOR c_mo_type
111: ( p_mo_line_id IN NUMBER
253: ) IS
254: SELECT wda.delivery_id
255: FROM wsh_delivery_assignments_v wda,
256: wsh_delivery_details_ob_grp_v wdd,
257: mtl_material_transactions_temp mmtt
258: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
259: AND wdd.move_order_line_id = mmtt.move_order_line_id
260: AND wdd.organization_id = mmtt.organization_id
261: AND mmtt.transaction_temp_id = p_tmp_id;
277: SELECT wda.delivery_id
278: INTO x_delivery_id
279: FROM wsh_delivery_assignments_v wda,
280: wsh_delivery_details_ob_grp_v wdd,
281: mtl_material_transactions_temp mmtt
282: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
283: AND wdd.organization_id = mmtt.organization_id
284: AND mmtt.transaction_temp_id = p_temp_id
285: AND mmtt.transfer_lpn_id = wdd.lpn_id
482: CURSOR c_get_parent_id
483: ( p_temp_id IN NUMBER
484: ) IS
485: SELECT mmtt.parent_line_id
486: FROM mtl_material_transactions_temp mmtt
487: WHERE mmtt.transaction_temp_id = p_temp_id;
488:
489: CURSOR c_get_task_type
490: ( p_temp_id IN NUMBER
653: , wdt.last_updated_by
654: , wdt.created_by
655: , wdt.task_type
656: , wdt.loaded_time
657: FROM mtl_material_transactions_temp pmmtt
658: , wms_dispatched_tasks wdt
659: WHERE pmmtt.organization_id = p_org_id
660: AND pmmtt.transfer_lpn_id = p_lpn_id
661: AND pmmtt.transaction_temp_id = NVL(pmmtt.parent_line_id,0)
730: , c_parent_task_rec.task_type
731: , c_parent_task_rec.loaded_time
732: , mmtt.operation_plan_id
733: , mmtt.move_order_line_id
734: FROM mtl_material_transactions_temp mmtt
735: WHERE mmtt.parent_line_id = c_parent_task_rec.transaction_temp_id
736: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
737: AND NOT EXISTS
738: ( SELECT 'x'
805: SELECT 'x'
806: FROM dual
807: WHERE EXISTS
808: ( SELECT 'x'
809: FROM mtl_material_transactions_temp mmtt
810: WHERE mmtt.organization_id = p_org_id
811: AND mmtt.transfer_lpn_id = p_lpn_id
812: )
813: --Added for Bug 6717052
815: SELECT 'x'
816: FROM dual
817: WHERE EXISTS
818: ( SELECT 'x'
819: FROM mtl_material_transactions_temp mmtt
820: WHERE mmtt.organization_id = p_org_id
821: AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
822: WHERE wlpn.outermost_lpn_id = p_lpn_id));
823:
827: , p_lpn_id IN NUMBER
828: ) IS
829: SELECT mmtt.transaction_temp_id
830: , mmtt.transaction_type_id
831: FROM mtl_material_transactions_temp mmtt
832: WHERE mmtt.organization_id = p_org_id
833: AND mmtt.transfer_lpn_id = p_lpn_id
834: AND (mmtt.parent_line_id IS NULL
835: OR
841: --Added for Bug 6717052
842: UNION ALL
843: SELECT mmtt.transaction_temp_id
844: , mmtt.transaction_type_id
845: FROM mtl_material_transactions_temp mmtt
846: WHERE mmtt.organization_id = p_org_id
847: AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
848: WHERE wlpn.outermost_lpn_id = p_lpn_id)
849: AND (mmtt.parent_line_id IS NULL
864: ||';'||
865: to_char(mtrl.line_status)
866: )
867: )
868: FROM mtl_material_transactions_temp mmtt
869: , mtl_txn_request_lines mtrl
870: , mtl_txn_request_headers mtrh
871: WHERE mmtt.organization_id = p_org_id
872: --Modified for Bug 6717052
879: ( p_org_id IN NUMBER
880: , p_lpn_id IN NUMBER
881: ) IS
882: SELECT COUNT (DISTINCT mmtt.transaction_action_id)
883: FROM mtl_material_transactions_temp mmtt
884: WHERE mmtt.organization_id = p_org_id
885: --Added for Bug 6717052
886: AND (mmtt.transfer_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
887: WHERE wlpn.outermost_lpn_id = p_lpn_id))
900: ) IS
901: SELECT mmtt.transaction_type_id
902: , mmtt.transaction_source_type_id
903: , mmtt.transaction_action_id
904: FROM mtl_material_transactions_temp mmtt
905: WHERE mmtt.organization_id = p_org_id
906: AND mmtt.transfer_lpn_id = p_lpn_id
907: AND (mmtt.parent_line_id IS NULL
908: OR
915: UNION ALL
916: SELECT mmtt.transaction_type_id
917: , mmtt.transaction_source_type_id
918: , mmtt.transaction_action_id
919: FROM mtl_material_transactions_temp mmtt
920: WHERE mmtt.organization_id = p_org_id
921: AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
922: WHERE wlpn.outermost_lpn_id = p_lpn_id)
923: AND (mmtt.parent_line_id IS NULL
942: )
943: )
944: )
945: )
946: FROM mtl_material_transactions_temp mmtt
947: , mtl_txn_request_lines mtrl
948: , wsh_delivery_details_ob_grp_v wdd
949: , wsh_delivery_assignments_v wda
950: WHERE mmtt.organization_id = p_org_id
974: ||';'||
975: to_char(mtrl.reference_id)
976: )
977: )
978: FROM mtl_material_transactions_temp mmtt
979: , mtl_txn_request_lines mtrl
980: WHERE mmtt.organization_id = p_org_id
981: AND mmtt.transfer_lpn_id = p_lpn_id
982: AND mmtt.move_order_line_id = mtrl.line_id;
990: ||';'||
991: to_char(mmtt.transfer_to_location)
992: )
993: )
994: FROM mtl_material_transactions_temp mmtt
995: WHERE mmtt.organization_id = p_org_id
996: AND mmtt.transfer_lpn_id = p_lpn_id
997: AND ( (mmtt.parent_line_id IS NOT NULL
998: AND
1478: , mmtt.revision
1479: , mmtt.primary_quantity
1480: , mmtt.parent_line_id
1481: , mmtt.serial_allocated_flag
1482: FROM mtl_material_transactions_temp mmtt
1483: WHERE mmtt.transaction_temp_id = p_temp_id;
1484:
1485: c_mmtt_rec c_mmtt_info%ROWTYPE;
1486:
1515: SELECT 'x'
1516: FROM dual
1517: WHERE EXISTS
1518: ( SELECT 'x'
1519: FROM mtl_material_transactions_temp mmtt
1520: WHERE mmtt.organization_id = p_org_id
1521: AND mmtt.transfer_lpn_id = p_outer_lpn_id
1522: AND mmtt.parent_line_id = mmtt.transaction_temp_id
1523: AND mmtt.content_lpn_id = p_lpn_id
2178: -- on the child record
2179: -- Bug6913674 removed SUB & LOC from being nulled out
2180: IF c_mmtt_rec.parent_line_id IS NOT NULL
2181: THEN
2182: UPDATE mtl_material_transactions_temp mmtt
2183: SET lpn_id = NULL
2184: , content_lpn_id = NULL
2185: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
2186: END IF;
2235: INTO l_dummy
2236: FROM dual
2237: WHERE EXISTS
2238: ( SELECT 'x'
2239: FROM mtl_material_transactions_temp mmtt
2240: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2241: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2242: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2243: AND mmtt.transaction_quantity > 0
2257: INTO l_dummy
2258: FROM dual
2259: WHERE EXISTS
2260: ( SELECT 'x'
2261: FROM mtl_material_transactions_temp mmtt
2262: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2263: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2264: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2265: AND mmtt.transaction_quantity > 0
2304: IF l_inner_lpn_exists = 'Y'
2305: THEN
2306: --Bug6913674
2307: SELECT mmtt.content_lpn_id INTO l_inner_lpn_id
2308: FROM mtl_material_transactions_temp mmtt
2309: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2310: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2311: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2312: AND mmtt.transaction_quantity > 0
2326: , last_update_date = SYSDATE
2327: , last_updated_by = fnd_global.user_id
2328: WHERE lpn_id IN
2329: ( SELECT mmtt.content_lpn_id
2330: FROM mtl_material_transactions_temp mmtt
2331: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2332: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2333: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2334: AND mmtt.content_lpn_id IS NOT NULL
2354: , last_update_date = SYSDATE
2355: , last_updated_by = fnd_global.user_id
2356: WHERE lpn_id IN
2357: ( SELECT mmtt.content_lpn_id
2358: FROM mtl_material_transactions_temp mmtt
2359: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2360: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2361: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2362: AND mmtt.content_lpn_id IS NOT NULL
2494: , l_api_name
2495: );
2496: END IF;
2497:
2498: INSERT INTO mtl_material_transactions_temp
2499: ( transaction_header_id
2500: , transaction_temp_id
2501: , source_code
2502: , source_line_id
2882: , NULL
2883: , operation_plan_id
2884: , move_order_header_id
2885: , serial_allocated_flag
2886: FROM mtl_material_transactions_temp
2887: WHERE transaction_temp_id = p_temp_id
2888: );
2889:
2890: g_current_drop_lpn.current_drop_list(l_new_temp_id) := 'PENDING';
2938: , wdt.loaded_time
2939: , mmtt.operation_plan_id
2940: , mmtt.move_order_line_id
2941: FROM wms_dispatched_tasks wdt
2942: , mtl_material_transactions_temp mmtt
2943: WHERE mmtt.transaction_temp_id = p_temp_id
2944: AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2945: );
2946:
3188: END IF;
3189: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3190: END IF;
3191:
3192: UPDATE mtl_material_transactions_temp
3193: SET transaction_quantity = transaction_quantity
3194: - lot_dtl_rec.transaction_quantity
3195: , primary_quantity = primary_quantity
3196: - lot_dtl_rec.primary_quantity
3208: , l_api_name
3209: );
3210: END IF;
3211:
3212: UPDATE mtl_material_transactions_temp
3213: SET transaction_quantity = lot_dtl_rec.transaction_quantity
3214: , primary_quantity = lot_dtl_rec.primary_quantity
3215: , reservation_quantity = DECODE( reservation_quantity
3216: , NULL, NULL
3354: , DECODE( mmtt.parent_line_id
3355: , NULL, 0
3356: , 1
3357: ) parent_line_id
3358: FROM mtl_material_transactions_temp mmtt
3359: , mtl_secondary_inventories msi
3360: , mtl_item_locations mil
3361: , mtl_txn_request_lines mtrl
3362: , wsh_delivery_details_ob_grp_v wdd
3400: , DECODE( mmtt.parent_line_id
3401: , NULL, 0
3402: , 1
3403: ) parent_line_id
3404: FROM mtl_material_transactions_temp mmtt
3405: , mtl_secondary_inventories msi
3406: , mtl_item_locations mil
3407: , mtl_txn_request_lines mtrl
3408: , mtl_txn_request_headers mtrh
3444: , DECODE( mmtt.parent_line_id
3445: , NULL, 0
3446: , 1
3447: ) parent_line_id
3448: FROM mtl_material_transactions_temp mmtt
3449: , mtl_secondary_inventories msi
3450: , mtl_item_locations mil
3451: , mtl_txn_request_lines mtrl
3452: , mtl_txn_request_headers mtrh
3488: , DECODE( mmtt.parent_line_id
3489: , NULL, 0
3490: , 1
3491: ) parent_line_id
3492: FROM mtl_material_transactions_temp mmtt
3493: , mtl_txn_request_lines mtrl
3494: WHERE mmtt.organization_id = p_org_id
3495: AND mmtt.transfer_lpn_id = p_lpn_id
3496: AND mmtt.transaction_action_id = 1
3523: , DECODE( mmtt.parent_line_id
3524: , NULL, 0
3525: , 1
3526: ) parent_line_id
3527: FROM mtl_material_transactions_temp mmtt
3528: , mtl_txn_request_lines mtrl
3529: WHERE mmtt.organization_id = p_org_id
3530: AND mmtt.transfer_lpn_id = p_lpn_id
3531: AND mmtt.transaction_action_id IN (2,28)
3557: , DECODE( mmtt.parent_line_id
3558: , NULL, 0
3559: , 1
3560: ) parent_line_id
3561: FROM mtl_material_transactions_temp mmtt
3562: WHERE mmtt.organization_id = p_org_id
3563: AND mmtt.transfer_lpn_id = p_lpn_id
3564: AND mmtt.transaction_action_id = 2
3565: AND mmtt.move_order_line_id IS NULL
3579: CURSOR c_get_txn_type
3580: ( p_temp_id IN NUMBER
3581: ) IS
3582: SELECT mmtt.transaction_type_id
3583: FROM mtl_material_transactions_temp mmtt
3584: WHERE mmtt.transaction_temp_id = p_temp_id;
3585:
3586: BEGIN
3587:
3975: CURSOR c_get_entity_type
3976: ( p_temp_id IN NUMBER
3977: ) IS
3978: SELECT wip_entity_type
3979: FROM mtl_material_transactions_temp mmtt
3980: , mtl_txn_request_lines mtrl
3981: , wip_entities we
3982: WHERE mmtt.transaction_temp_id = p_temp_id
3983: AND mmtt.transaction_source_type_id = 5
4138: decode(transaction_action_id, 28,mmtt.cartonization_id, NULL)
4139: --mmtt.cartonization_id has the LPN suggested by MDC
4140: --IT is applicable only to MDC case for staging transfer PickDrops
4141: -- For all others, it should be null
4142: FROM mtl_material_transactions_temp mmtt
4143: WHERE mmtt.transaction_temp_id = p_temp_id;
4144:
4145: BEGIN
4146: x_return_status := fnd_api.g_ret_sts_success;
4465: SELECT mmtt.primary_quantity
4466: , mmtt.inventory_item_id
4467: , mmtt.organization_id
4468: , mmtt.revision
4469: FROM mtl_material_transactions_temp mmtt
4470: WHERE mmtt.transaction_temp_id = p_tmp_id;
4471:
4472: mmtt_info_rec c_get_mmtt_info%ROWTYPE;
4473:
4812: , mmtt.primary_quantity
4813: , mmtt.subinventory_code
4814: , mmtt.locator_id
4815: FROM wms_license_plate_numbers wlpn
4816: , mtl_material_transactions_temp mmtt
4817: WHERE wlpn.license_plate_number = p_inner_lpn
4818: AND wlpn.organization_id = p_org_id
4819: AND mmtt.transfer_lpn_id = p_outer_lpn_id
4820: AND mmtt.organization_id = p_org_id
4945: , l_api_name
4946: );
4947: END IF;
4948:
4949: UPDATE mtl_material_transactions_temp mmtt
4950: SET lpn_id = l_inner_lpn_id
4951: , transfer_lpn_id = DECODE( x_ret_code
4952: , 2, l_inner_lpn_id
4953: , NULL
5074: , l_api_name
5075: );
5076: END IF;
5077:
5078: UPDATE mtl_material_transactions_temp mmtt
5079: SET lpn_id = l_inner_lpn_id
5080: , transfer_lpn_id = l_inner_lpn_id
5081: , subinventory_code = l_from_sub
5082: , locator_id = l_from_loc_id
5125: END IF;
5126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5127: END IF;
5128:
5129: UPDATE mtl_material_transactions_temp
5130: SET transaction_quantity = DECODE( transaction_uom
5131: , p_primary_uom, l_remaining_qty
5132: , inv_convert.inv_um_convert
5133: ( p_item_id
5154: , l_api_name
5155: );
5156: END IF;
5157:
5158: UPDATE mtl_material_transactions_temp
5159: SET transaction_quantity = transaction_quantity - l_txn_qty
5160: , primary_quantity = primary_quantity - l_remaining_qty
5161: , reservation_quantity = DECODE( reservation_quantity
5162: , NULL, NULL
5243: END IF; -- checking LPN avail qty against entered qty
5244:
5245: IF x_ret_code IN (2,3)
5246: THEN
5247: UPDATE mtl_material_transactions_temp mmtt
5248: SET content_lpn_id = NULL
5249: , transaction_quantity = 0
5250: , primary_quantity = 0
5251: , reservation_quantity = 0
5250: , primary_quantity = 0
5251: , reservation_quantity = 0
5252: WHERE mmtt.transaction_temp_id = l_parent_temp_id;
5253: ELSE
5254: UPDATE mtl_material_transactions_temp mmtt
5255: SET transaction_quantity = DECODE( transaction_uom
5256: , p_primary_uom, (primary_quantity - l_tot_mmtt_qty)
5257: , inv_convert.inv_um_convert
5258: ( p_item_id
5279: INTO l_dummy
5280: FROM dual
5281: WHERE EXISTS
5282: ( SELECT 'x'
5283: FROM mtl_material_transactions_temp mmtt
5284: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
5285: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
5286: AND mmtt.parent_line_id = mmtt.transaction_temp_id
5287: AND mmtt.transaction_quantity > 0
5373: , p_itm_id IN NUMBER
5374: , p_rev IN VARCHAR2
5375: ) IS
5376: SELECT NVL(SUM(mmtt.primary_quantity),0)
5377: FROM mtl_material_transactions_temp mmtt
5378: WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
5379: AND mmtt.organization_id = p_org_id
5380: AND mmtt.parent_line_id = mmtt.transaction_temp_id
5381: AND mmtt.transaction_quantity > 0
5390: CURSOR c_get_mmtt_qty
5391: ( p_temp_id IN NUMBER
5392: ) IS
5393: SELECT primary_quantity
5394: FROM mtl_material_transactions_temp
5395: WHERE transaction_temp_id = p_temp_id;
5396:
5397:
5398: CURSOR c_get_parents
5401: , p_itm_id IN NUMBER
5402: , p_rev IN VARCHAR2
5403: ) IS
5404: SELECT mmtt.transaction_temp_id
5405: FROM mtl_material_transactions_temp mmtt
5406: WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
5407: AND mmtt.organization_id = p_org_id
5408: AND mmtt.parent_line_id = mmtt.transaction_temp_id
5409: AND mmtt.transaction_quantity > 0
5422: , mmtt.content_lpn_id
5423: , mmtt.subinventory_code
5424: , mmtt.locator_id
5425: , mmtt.primary_quantity
5426: FROM mtl_material_transactions_temp mmtt
5427: WHERE mmtt.transaction_temp_id = p_temp_id;
5428:
5429: parent_rec c_get_parent_attributes%ROWTYPE;
5430:
5581: END IF;
5582: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5583: END IF;
5584:
5585: UPDATE mtl_material_transactions_temp
5586: SET transaction_quantity = DECODE( transaction_uom
5587: , p_primary_uom, l_remaining_qty
5588: , inv_convert.inv_um_convert
5589: ( p_item_id
5610: , l_api_name
5611: );
5612: END IF;
5613:
5614: UPDATE mtl_material_transactions_temp
5615: SET transaction_quantity = transaction_quantity - l_txn_qty
5616: , primary_quantity = primary_quantity - l_remaining_qty
5617: , reservation_quantity = DECODE( reservation_quantity
5618: , NULL, NULL
5720: , l_api_name
5721: );
5722: END IF;
5723:
5724: UPDATE mtl_material_transactions_temp mmtt
5725: SET lpn_id = NVL(parent_rec.content_lpn_id,parent_rec.lpn_id)
5726: , subinventory_code = parent_rec.subinventory_code
5727: , locator_id = parent_rec.locator_id
5728: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
5748: , l_api_name
5749: );
5750: END IF;
5751:
5752: UPDATE mtl_material_transactions_temp mmtt
5753: SET transaction_quantity = DECODE( transaction_uom
5754: , p_primary_uom, (primary_quantity - l_mmtt_qty)
5755: , inv_convert.inv_um_convert
5756: ( p_item_id
5786: END IF;
5787: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5788: END IF;
5789:
5790: UPDATE mtl_material_transactions_temp
5791: SET transaction_quantity = DECODE( transaction_uom
5792: , p_primary_uom, l_remaining_qty
5793: , inv_convert.inv_um_convert
5794: ( p_item_id
5815: , l_api_name
5816: );
5817: END IF;
5818:
5819: UPDATE mtl_material_transactions_temp
5820: SET transaction_quantity = transaction_quantity - l_txn_qty
5821: , primary_quantity = primary_quantity - l_remaining_qty
5822: , reservation_quantity = DECODE( reservation_quantity
5823: , NULL, NULL
5840:
5841: jj := jj + 1;
5842: l_process_tbl(jj) := l_new_temp_id;
5843:
5844: UPDATE mtl_material_transactions_temp mmtt
5845: SET transaction_quantity = 0
5846: , primary_quantity = 0
5847: , reservation_quantity = 0
5848: WHERE mmtt.transaction_temp_id = l_parent_temp_id;
5904: INTO l_dummy
5905: FROM dual
5906: WHERE EXISTS
5907: ( SELECT 'x'
5908: FROM mtl_material_transactions_temp mmtt
5909: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
5910: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
5911: AND mmtt.parent_line_id = mmtt.transaction_temp_id
5912: AND mmtt.transaction_quantity > 0
6005: , p_org_id IN NUMBER
6006: ) IS
6007: SELECT wlpn.lpn_id
6008: FROM wms_license_plate_numbers wlpn
6009: , mtl_material_transactions_temp mmtt
6010: WHERE wlpn.license_plate_number = p_inner_lpn
6011: AND wlpn.organization_id = p_org_id
6012: AND mmtt.transfer_lpn_id = p_outer_lpn_id
6013: AND mmtt.organization_id = p_org_id
6021: , p_serial_num IN VARCHAR2
6022: ) IS
6023: SELECT msnt.rowid
6024: , mmtt.transaction_temp_id
6025: FROM mtl_material_transactions_temp mmtt
6026: , mtl_serial_numbers_temp msnt
6027: WHERE mmtt.organization_id = p_org_id
6028: AND mmtt.transfer_lpn_id = p_xfer_lpn_id
6029: AND mmtt.transaction_temp_id = mmtt.parent_line_id
6043: , p_lpn_id IN NUMBER
6044: ) IS
6045: SELECT msnt.rowid
6046: , mmtt.transaction_temp_id
6047: FROM mtl_material_transactions_temp mmtt
6048: , mtl_serial_numbers_temp msnt
6049: WHERE mmtt.organization_id = p_org_id
6050: AND mmtt.transfer_lpn_id = p_xfer_lpn_id
6051: AND mmtt.transaction_temp_id = mmtt.parent_line_id
6062: , p_lpn_id IN NUMBER
6063: ) IS
6064: SELECT msnt.rowid
6065: , mmtt.transaction_temp_id transaction_temp_id
6066: FROM mtl_material_transactions_temp mmtt
6067: , mtl_transaction_lots_temp mtlt
6068: , mtl_serial_numbers_temp msnt
6069: WHERE mmtt.organization_id = p_org_id
6070: AND mmtt.transfer_lpn_id = p_xfer_lpn_id
6081: CURSOR c_get_parent
6082: ( p_temp_id IN NUMBER
6083: ) IS
6084: SELECT parent_line_id
6085: FROM mtl_material_transactions_temp mmtt
6086: WHERE mmtt.transaction_temp_id = p_temp_id;
6087:
6088:
6089: CURSOR c_get_parent_attributes
6092: SELECT mmtt.lpn_id
6093: , mmtt.content_lpn_id
6094: , mmtt.subinventory_code
6095: , mmtt.locator_id
6096: FROM mtl_material_transactions_temp mmtt
6097: WHERE mmtt.transaction_temp_id = p_temp_id;
6098:
6099: parent_rec c_get_parent_attributes%ROWTYPE;
6100:
6104: , p_lot_num IN VARCHAR2
6105: ) IS
6106: SELECT mtlt.primary_quantity
6107: , COUNT(msnt.fm_serial_number) srl_count
6108: FROM mtl_material_transactions_temp mmtt
6109: , mtl_transaction_lots_temp mtlt
6110: , mtl_serial_numbers_temp msnt
6111: WHERE mmtt.transaction_temp_id = p_temp_id
6112: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
6119: ( p_temp_id IN NUMBER
6120: ) IS
6121: SELECT mmtt.primary_quantity
6122: , COUNT(msnt.fm_serial_number) srl_count
6123: FROM mtl_material_transactions_temp mmtt
6124: , mtl_serial_numbers_temp msnt
6125: WHERE mmtt.transaction_temp_id = p_temp_id
6126: AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id
6127: GROUP BY mmtt.primary_quantity;
6391: END IF;
6392: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6393: END IF;
6394:
6395: UPDATE mtl_material_transactions_temp
6396: SET transaction_quantity = transaction_quantity - 1
6397: , primary_quantity = primary_quantity - 1
6398: , reservation_quantity = DECODE( reservation_quantity
6399: , NULL, NULL
6435: OPEN c_get_parent_attributes (lot_msnt_rec.transaction_temp_id);
6436: FETCH c_get_parent_attributes INTO parent_rec;
6437: CLOSE c_get_parent_attributes;
6438:
6439: UPDATE mtl_material_transactions_temp
6440: SET transaction_quantity = 1
6441: , primary_quantity = 1
6442: , reservation_quantity = DECODE( reservation_quantity
6443: , NULL, NULL
6722: OPEN c_get_parent_attributes (msnt_rec.transaction_temp_id);
6723: FETCH c_get_parent_attributes INTO parent_rec;
6724: CLOSE c_get_parent_attributes;
6725:
6726: UPDATE mtl_material_transactions_temp
6727: SET transaction_quantity = 1
6728: , primary_quantity = 1
6729: , reservation_quantity = DECODE( reservation_quantity
6730: , NULL, NULL
6764: OPEN c_get_parent_attributes (msnt_rec.transaction_temp_id);
6765: FETCH c_get_parent_attributes INTO parent_rec;
6766: CLOSE c_get_parent_attributes;
6767:
6768: UPDATE mtl_material_transactions_temp
6769: SET subinventory_code = parent_rec.subinventory_code
6770: , locator_id = parent_rec.locator_id
6771: , lpn_id = NVL(parent_rec.content_lpn_id,parent_rec.lpn_id)
6772: WHERE transaction_temp_id = l_transaction_temp_id;
6863: --
6864: -- Now update the parent
6865: --
6866: BEGIN
6867: UPDATE mtl_material_transactions_temp
6868: SET transaction_quantity = transaction_quantity - 1
6869: , primary_quantity = primary_quantity - 1
6870: WHERE transaction_temp_id = l_parent_line_id;
6871:
6908:
6909: BEGIN
6910: SELECT mmtt.transaction_header_id
6911: INTO l_txn_header_id
6912: FROM mtl_material_transactions_temp mmtt
6913: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
6914: EXCEPTION
6915: WHEN OTHERS THEN
6916: IF l_debug = 1 THEN
7091: INTO l_dummy
7092: FROM dual
7093: WHERE EXISTS
7094: ( SELECT 'x'
7095: FROM mtl_material_transactions_temp mmtt
7096: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
7097: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
7098: AND mmtt.parent_line_id = mmtt.transaction_temp_id
7099: AND mmtt.transaction_quantity > 0
7160: SELECT mmtt.transaction_temp_id
7161: , wdt.suggested_dest_subinventory
7162: , wdt.suggested_dest_locator_id
7163: FROM ( SELECT mmtt2.transaction_temp_id
7164: FROM mtl_material_transactions_temp mmtt2
7165: WHERE mmtt2.organization_id = p_org_id
7166: AND mmtt2.transfer_lpn_id = p_lpn_id
7167: AND mmtt2.parent_line_id IS NULL
7168: UNION
7166: AND mmtt2.transfer_lpn_id = p_lpn_id
7167: AND mmtt2.parent_line_id IS NULL
7168: UNION
7169: SELECT mmtt3.transaction_temp_id
7170: FROM mtl_material_transactions_temp mmtt3
7171: WHERE mmtt3.parent_line_id IS NOT NULL
7172: AND mmtt3.transaction_temp_id <> mmtt3.parent_line_id
7173: AND mmtt3.transaction_temp_id IN
7174: ( SELECT mmtt4.transaction_temp_id
7171: WHERE mmtt3.parent_line_id IS NOT NULL
7172: AND mmtt3.transaction_temp_id <> mmtt3.parent_line_id
7173: AND mmtt3.transaction_temp_id IN
7174: ( SELECT mmtt4.transaction_temp_id
7175: FROM mtl_material_transactions_temp mmtt4
7176: WHERE mmtt4.organization_id = p_org_id
7177: AND mmtt4.transfer_lpn_id = p_lpn_id
7178: START WITH
7179: mmtt4.transaction_temp_id = mmtt4.parent_line_id
7243: FETCH c_get_temp_ids BULK COLLECT INTO v_temp_id_tbl, v_sub_tbl, v_loc_id_tbl;
7244: CLOSE c_get_temp_ids;
7245:
7246: FORALL ii IN v_temp_id_tbl.FIRST..v_temp_id_tbl.LAST
7247: UPDATE mtl_material_transactions_temp
7248: SET transfer_subinventory = v_sub_tbl(ii)
7249: , transfer_to_location = v_loc_id_tbl(ii)
7250: WHERE transaction_temp_id = v_temp_id_tbl(ii);
7251:
7266: -- Bug 4884284: Delete child WDT records
7267: DELETE wms_dispatched_tasks
7268: WHERE transaction_temp_id IN
7269: ( SELECT transaction_temp_id
7270: FROM mtl_material_transactions_temp mmtt
7271: WHERE mmtt.parent_line_id IS NOT NULL
7272: AND mmtt.transaction_temp_id <> NVL(mmtt.parent_line_id,0)
7273: AND mmtt.transaction_temp_id IN
7274: ( SELECT mmtt2.transaction_temp_id
7271: WHERE mmtt.parent_line_id IS NOT NULL
7272: AND mmtt.transaction_temp_id <> NVL(mmtt.parent_line_id,0)
7273: AND mmtt.transaction_temp_id IN
7274: ( SELECT mmtt2.transaction_temp_id
7275: FROM mtl_material_transactions_temp mmtt2
7276: WHERE mmtt2.organization_id = p_organization_id
7277: AND mmtt2.transfer_lpn_id = p_transfer_lpn_id
7278: START WITH
7279: mmtt2.transaction_temp_id = mmtt2.parent_line_id
7308: , last_update_date = SYSDATE
7309: , last_updated_by = fnd_global.user_id
7310: WHERE wlpn1.lpn_id IN
7311: ( SELECT mmtt.content_lpn_id
7312: FROM mtl_material_transactions_temp mmtt
7313: , wms_license_plate_numbers wlpn2
7314: WHERE mmtt.transfer_lpn_id = p_transfer_lpn_id
7315: AND mmtt.organization_id = p_organization_id
7316: AND mmtt.parent_line_id = mmtt.transaction_temp_id
7410: SELECT mmtt.transfer_subinventory
7411: , mmtt.transfer_to_location
7412: , mtrl.carton_grouping_id
7413: , wda.delivery_id
7414: FROM mtl_material_transactions_temp mmtt
7415: , mtl_txn_request_lines mtrl
7416: , wsh_delivery_details_ob_grp_v wdd
7417: , wsh_delivery_assignments_v wda
7418: WHERE mmtt.organization_id = p_org_id
7444: CURSOR c_get_cur_carton_grp
7445: ( p_temp_id IN NUMBER
7446: ) IS
7447: SELECT mtrl.carton_grouping_id
7448: FROM mtl_material_transactions_temp mmtt
7449: , mtl_txn_request_lines mtrl
7450: WHERE mmtt.transaction_temp_id = p_temp_id
7451: AND mmtt.move_order_line_id = mtrl.line_id;
7452:
7694: END IF;
7695:
7696: WHILE (ii <= jj)
7697: LOOP
7698: UPDATE mtl_material_transactions_temp mmtt
7699: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
7700: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
7701: RETURNING primary_quantity
7702: INTO l_primary_qty;
7705: THEN
7706: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
7707: := 'DONE';
7708: ELSE
7709: DELETE mtl_material_transactions_temp
7710: WHERE transaction_temp_id = l_transaction_temp_id;
7711:
7712: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
7713:
7775: END IF;
7776:
7777: WHILE (ii <= jj)
7778: LOOP
7779: UPDATE mtl_material_transactions_temp mmtt
7780: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
7781: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
7782: RETURNING primary_quantity
7783: INTO l_primary_qty;
7786: THEN
7787: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
7788: := 'DONE';
7789: ELSE
7790: DELETE mtl_material_transactions_temp
7791: WHERE transaction_temp_id = l_transaction_temp_id;
7792:
7793: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
7794:
7856: END IF;
7857:
7858: WHILE (ii <= jj)
7859: LOOP
7860: UPDATE mtl_material_transactions_temp mmtt
7861: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
7862: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
7863: RETURNING primary_quantity
7864: INTO l_primary_qty;
7867: THEN
7868: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
7869: := 'DONE';
7870: ELSE
7871: DELETE mtl_material_transactions_temp
7872: WHERE transaction_temp_id = l_transaction_temp_id;
7873:
7874: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
7875:
7938: END IF;
7939:
7940: WHILE (ii <= jj)
7941: LOOP
7942: UPDATE mtl_material_transactions_temp mmtt
7943: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
7944: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
7945: RETURNING primary_quantity
7946: INTO l_primary_qty;
7949: THEN
7950: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
7951: := 'DONE';
7952: ELSE
7953: DELETE mtl_material_transactions_temp
7954: WHERE transaction_temp_id = l_transaction_temp_id;
7955:
7956: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
7957:
8045:
8046: BEGIN
8047: SELECT mmtt.transaction_temp_id
8048: INTO l_child_temp_id
8049: FROM mtl_material_transactions_temp mmtt
8050: WHERE mmtt.parent_line_id = p_parent_temp_id
8051: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8052: AND ROWNUM < 2;
8053:
8080: , l_api_name
8081: );
8082: END IF;
8083:
8084: INSERT INTO mtl_material_transactions_temp
8085: ( transaction_header_id
8086: , transaction_temp_id
8087: , source_code
8088: , source_line_id
8466: , p_lpn_id
8467: , operation_plan_id
8468: , transaction_header_id
8469: , l_pack_temp_id
8470: FROM mtl_material_transactions_temp
8471: WHERE transaction_temp_id = l_child_temp_id
8472: );
8473:
8474: x_pack_temp_id := l_pack_temp_id;
8532: , p_lpn_id IN NUMBER
8533: ) IS
8534: -- Material unpacked from nested LPNs
8535: SELECT mmtt.transaction_temp_id
8536: FROM mtl_material_transactions_temp mmtt
8537: WHERE mmtt.organization_id = p_org_id
8538: AND mmtt.transfer_lpn_id = p_lpn_id
8539: AND mmtt.parent_line_id IS NOT NULL
8540: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8541: AND mmtt.content_lpn_id IS NULL
8542: AND mmtt.lpn_id IS NOT NULL
8543: AND EXISTS
8544: ( SELECT 'x'
8545: FROM mtl_material_transactions_temp mmtt2
8546: WHERE mmtt2.organization_id = p_org_id
8547: AND mmtt2.transfer_lpn_id = p_lpn_id
8548: AND mmtt2.content_lpn_id = mmtt.lpn_id
8549: AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
8551: )
8552: UNION ALL
8553: -- Nested LPNs
8554: SELECT mmtt.transaction_temp_id
8555: FROM mtl_material_transactions_temp mmtt
8556: WHERE mmtt.organization_id = p_org_id
8557: AND mmtt.transfer_lpn_id = p_lpn_id
8558: AND mmtt.parent_line_id IS NOT NULL
8559: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8560: AND mmtt.content_lpn_id IS NULL
8561: AND mmtt.lpn_id IS NOT NULL
8562: AND EXISTS
8563: ( SELECT 'x'
8564: FROM mtl_material_transactions_temp mmtt2
8565: WHERE mmtt2.organization_id = p_org_id
8566: AND mmtt2.transfer_lpn_id = p_lpn_id
8567: AND mmtt2.content_lpn_id = mmtt.lpn_id
8568: AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
8570: )
8571: UNION ALL
8572: -- Picked from LPN that is not a nested LPN
8573: SELECT mmtt.transaction_temp_id
8574: FROM mtl_material_transactions_temp mmtt
8575: WHERE mmtt.organization_id = p_org_id
8576: AND mmtt.transfer_lpn_id = p_lpn_id
8577: AND mmtt.parent_line_id IS NOT NULL
8578: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8579: AND mmtt.content_lpn_id IS NULL
8580: AND mmtt.lpn_id IS NOT NULL
8581: AND NOT EXISTS
8582: ( SELECT 'x'
8583: FROM mtl_material_transactions_temp mmtt2
8584: WHERE mmtt2.organization_id = p_org_id
8585: AND mmtt2.transfer_lpn_id = p_lpn_id
8586: AND mmtt2.content_lpn_id = mmtt.lpn_id
8587: AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
8588: )
8589: UNION ALL
8590: -- Loose material
8591: SELECT mmtt.transaction_temp_id
8592: FROM mtl_material_transactions_temp mmtt
8593: WHERE mmtt.organization_id = p_org_id
8594: AND mmtt.transfer_lpn_id = p_lpn_id
8595: AND mmtt.parent_line_id IS NOT NULL
8596: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8603: , p_lpn_id IN NUMBER
8604: ) IS
8605: SELECT mmtt.transaction_temp_id
8606: , mmtt.content_lpn_id
8607: FROM mtl_material_transactions_temp mmtt
8608: WHERE mmtt.organization_id = p_org_id
8609: AND mmtt.transfer_lpn_id = p_lpn_id
8610: AND mmtt.parent_line_id = mmtt.transaction_temp_id
8611: AND mmtt.content_lpn_id IS NOT NULL;
8614: CURSOR c_chk_child_records
8615: ( p_parent_temp_id IN NUMBER
8616: ) IS
8617: SELECT 'x'
8618: FROM mtl_material_transactions_temp mmtt
8619: WHERE mmtt.parent_line_id = p_parent_temp_id
8620: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8621: AND mmtt.transaction_action_id in (2,28)
8622: AND EXISTS
8709: CLOSE c_chk_child_records;
8710:
8711: IF nested_lpn_rec.content_lpn_id <> p_xfer_lpn_id
8712: THEN
8713: UPDATE mtl_material_transactions_temp mmtt
8714: SET mmtt.transfer_lpn_id = nested_lpn_rec.content_lpn_id
8715: , mmtt.lpn_id = NVL(mmtt.lpn_id,nested_lpn_rec.content_lpn_id) -- Bug 5620764
8716: WHERE mmtt.parent_line_id = nested_lpn_rec.transaction_temp_id
8717: AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
8942: mmtt.revision ,
8943: mmtt.subinventory_code,
8944: mmtt.locator_id,
8945: mmtt.transaction_date
8946: FROM mtl_material_transactions_temp mmtt
8947: WHERE mmtt.organization_id = p_org_id
8948: AND mmtt.transfer_lpn_id = p_lpn_id
8949: AND mmtt.parent_line_id IS NULL
8950: AND mmtt.content_lpn_id IS NULL
8950: AND mmtt.content_lpn_id IS NULL
8951: AND mmtt.lpn_id IS NOT NULL
8952: AND EXISTS
8953: ( SELECT 'x'
8954: FROM mtl_material_transactions_temp mmtt2
8955: WHERE mmtt2.organization_id = p_org_id
8956: AND mmtt2.transfer_lpn_id = p_lpn_id
8957: AND mmtt2.content_lpn_id = mmtt.lpn_id
8958: AND mmtt2.transaction_temp_id <> mmtt.transaction_temp_id
8965: mmtt.revision ,
8966: mmtt.subinventory_code,
8967: mmtt.locator_id,
8968: mmtt.transaction_date
8969: FROM mtl_material_transactions_temp mmtt
8970: WHERE mmtt.organization_id = p_org_id
8971: AND mmtt.transfer_lpn_id = p_lpn_id
8972: AND mmtt.parent_line_id IS NULL
8973: AND mmtt.content_lpn_id IS NOT NULL
8979: mmtt.revision ,
8980: mmtt.subinventory_code,
8981: mmtt.locator_id,
8982: mmtt.transaction_date
8983: FROM mtl_material_transactions_temp mmtt
8984: WHERE mmtt.organization_id = p_org_id
8985: AND mmtt.transfer_lpn_id = p_lpn_id
8986: AND mmtt.parent_line_id IS NULL
8987: AND mmtt.content_lpn_id IS NULL
8987: AND mmtt.content_lpn_id IS NULL
8988: AND mmtt.lpn_id IS NOT NULL
8989: AND NOT EXISTS
8990: ( SELECT 'x'
8991: FROM mtl_material_transactions_temp mmtt2
8992: WHERE mmtt2.organization_id = p_org_id
8993: AND mmtt2.transfer_lpn_id = p_lpn_id
8994: AND mmtt2.content_lpn_id = mmtt.lpn_id
8995: AND mmtt2.transaction_temp_id <> mmtt.transaction_temp_id
9002: mmtt.revision ,
9003: mmtt.subinventory_code,
9004: mmtt.locator_id,
9005: mmtt.transaction_date
9006: FROM mtl_material_transactions_temp mmtt
9007: WHERE mmtt.organization_id = p_org_id
9008: AND mmtt.transfer_lpn_id IN (SELECT lpn_id FROM wms_license_plate_numbers
9009: WHERE outermost_lpn_id = p_lpn_id AND lpn_id <> outermost_lpn_id)
9010: AND mmtt.parent_line_id IS NULL
9016: mmtt.revision ,
9017: mmtt.subinventory_code,
9018: mmtt.locator_id,
9019: mmtt.transaction_date
9020: FROM mtl_material_transactions_temp mmtt
9021: WHERE mmtt.organization_id = p_org_id
9022: AND mmtt.transfer_lpn_id = p_lpn_id
9023: AND mmtt.parent_line_id IS NULL
9024: AND mmtt.content_lpn_id IS NULL
9031: ( p_temp_id IN NUMBER
9032: ) IS
9033: SELECT mmtt.parent_line_id
9034: , mmtt.transaction_type_id
9035: FROM mtl_material_transactions_temp mmtt
9036: WHERE mmtt.transaction_temp_id = p_temp_id;
9037:
9038:
9039: CURSOR c_get_parent_tasks
9041: , p_lpn_id IN NUMBER
9042: ) IS
9043: SELECT mmtt.transaction_temp_id
9044: , wdt.task_id
9045: FROM mtl_material_transactions_temp mmtt
9046: , wms_dispatched_tasks wdt
9047: WHERE mmtt.organization_id = p_org_id
9048: AND mmtt.transfer_lpn_id = p_lpn_id
9049: AND mmtt.parent_line_id IS NOT NULL
9054: CURSOR c_get_srl_alloc_stat
9055: ( p_temp_id IN NUMBER
9056: ) IS
9057: SELECT mmtt.serial_allocated_flag
9058: FROM mtl_material_transactions_temp mmtt
9059: WHERE transaction_temp_id = p_temp_id;
9060:
9061: BEGIN
9062: x_return_status := fnd_api.g_ret_sts_success;
9297: mmtt.inventory_item_id,
9298: mmtt.transaction_uom,
9299: mmtt.transaction_quantity
9300: INTO l_curr_xfer_lpn_id, l_inventory_item_id, l_transaction_uom, l_transaction_qty
9301: FROM mtl_material_transactions_temp mmtt
9302: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
9303:
9304: IF l_debug = 1 THEN
9305: print_debug
9494: , SYSDATE
9495: , mmtt.operation_plan_id
9496: , mmtt.move_order_line_id
9497: FROM wms_dispatched_tasks wdt
9498: , mtl_material_transactions_temp mmtt
9499: WHERE wdt.transaction_temp_id = l_parent_line_id
9500: AND mmtt.transaction_temp_id = l_transaction_temp_id
9501: AND rownum = 1
9502: );
9540: --
9541: -- Update parent line to NULL
9542: --
9543: BEGIN
9544: UPDATE mtl_material_transactions_temp mmtt
9545: SET mmtt.parent_line_id = NULL
9546: WHERE transaction_temp_id = l_transaction_temp_id;
9547:
9548: IF l_debug = 1 THEN
9610:
9611: BEGIN
9612: IF l_txn_type_id = INV_GLOBALS.G_TYPE_CONTAINER_PACK
9613: THEN
9614: UPDATE mtl_material_transactions_temp mmtt
9615: SET mmtt.transaction_header_id = l_txn_header_id
9616: , mmtt.subinventory_code = p_subinventory
9617: , mmtt.locator_id = p_loc_id
9618: , mmtt.transfer_subinventory = p_subinventory
9631: , l_api_name
9632: );
9633: END IF;
9634: ELSIF l_txn_type_id = 2 THEN -- Sub Transfer Transaction created by bulk overpick
9635: UPDATE mtl_material_transactions_temp mmtt
9636: SET mmtt.transfer_subinventory = p_subinventory,
9637: mmtt.transfer_to_location = p_loc_id,
9638: mmtt.transaction_batch_id = l_txn_header_id,
9639: mmtt.transaction_batch_seq = l_batch_seq_id
9648: ', xfer loc to ' || to_char(p_loc_id)
9649: , l_api_name);
9650: END IF;
9651: ELSE
9652: UPDATE mtl_material_transactions_temp mmtt
9653: SET mmtt.transaction_batch_seq = l_batch_seq_id
9654: WHERE transaction_temp_id = l_transaction_temp_id;
9655:
9656: IF l_debug = 1 THEN
9688: l_transaction_temp_id := l_temp_tbl(ii);
9689: BEGIN
9690: SELECT mmtt.transfer_lpn_id
9691: INTO l_next_xfer_lpn_id
9692: FROM mtl_material_transactions_temp mmtt
9693: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
9694:
9695: IF l_debug = 1 THEN
9696: print_debug
10054: ( p_org_id IN NUMBER
10055: , p_lpn_id IN NUMBER
10056: ) IS
10057: SELECT mmtt.transaction_temp_id
10058: FROM mtl_material_transactions_temp mmtt
10059: WHERE mmtt.organization_id = p_org_id
10060: AND mmtt.transfer_lpn_id = p_lpn_id
10061: AND ( (mmtt.parent_line_id IS NOT NULL
10062: AND
10456: CURSOR c_xfer_lpn_id
10457: ( p_temp_id IN NUMBER
10458: ) IS
10459: SELECT mmtt.transfer_lpn_id
10460: FROM mtl_material_transactions_temp mmtt
10461: WHERE mmtt.transaction_temp_id = p_temp_id;
10462:
10463:
10464: CURSOR c_drop_delivery
10780: AND rownum = 1 ;
10781:
10782: SELECT wdd.delivery_detail_id
10783: INTO l_line_rows(2)
10784: FROM wsh_delivery_details_ob_grp_v wdd, Mtl_material_transactions_temp mmtt
10785: WHERE mmtt.move_order_line_id = wdd.move_order_line_id
10786: AND wdd.organization_id = mmtt.organization_id
10787: AND mmtt.organization_id= p_organization_id
10788: AND mmtt.transfer_lpn_id= p_transfer_lpn_id