[Home] [Help]
113: SELECT mmtt.move_order_line_id
114: , mmtt.transaction_source_type_id
115: , mmtt.transaction_action_id
116: , mmtt.wms_task_type
117: FROM mtl_material_transactions_temp mmtt
118: WHERE mmtt.transaction_temp_id = p_txn_tmp_id;
119:
120: CURSOR c_mo_type
121: ( p_mo_line_id IN NUMBER
263: ) IS
264: SELECT wda.delivery_id
265: FROM wsh_delivery_assignments_v wda,
266: wsh_delivery_details_ob_grp_v wdd,
267: mtl_material_transactions_temp mmtt
268: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
269: AND wdd.move_order_line_id = mmtt.move_order_line_id
270: AND wdd.organization_id = mmtt.organization_id
271: AND mmtt.transaction_temp_id = p_tmp_id;
287: SELECT wda.delivery_id
288: INTO x_delivery_id
289: FROM wsh_delivery_assignments_v wda,
290: wsh_delivery_details_ob_grp_v wdd,
291: mtl_material_transactions_temp mmtt
292: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
293: AND wdd.organization_id = mmtt.organization_id
294: AND mmtt.transaction_temp_id = p_temp_id
295: AND mmtt.transfer_lpn_id = wdd.lpn_id
492: CURSOR c_get_parent_id
493: ( p_temp_id IN NUMBER
494: ) IS
495: SELECT mmtt.parent_line_id
496: FROM mtl_material_transactions_temp mmtt
497: WHERE mmtt.transaction_temp_id = p_temp_id;
498:
499: CURSOR c_get_task_type
500: ( p_temp_id IN NUMBER
663: , wdt.last_updated_by
664: , wdt.created_by
665: , wdt.task_type
666: , wdt.loaded_time
667: FROM mtl_material_transactions_temp pmmtt
668: , wms_dispatched_tasks wdt
669: WHERE pmmtt.organization_id = p_org_id
670: AND pmmtt.transfer_lpn_id = p_lpn_id
671: AND pmmtt.transaction_temp_id = NVL(pmmtt.parent_line_id,0)
740: , c_parent_task_rec.task_type
741: , c_parent_task_rec.loaded_time
742: , mmtt.operation_plan_id
743: , mmtt.move_order_line_id
744: FROM mtl_material_transactions_temp mmtt
745: WHERE mmtt.parent_line_id = c_parent_task_rec.transaction_temp_id
746: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
747: AND NOT EXISTS
748: ( SELECT 'x'
822: SELECT 'x'
823: FROM dual
824: WHERE EXISTS
825: ( SELECT 'x'
826: FROM mtl_material_transactions_temp mmtt
827: WHERE mmtt.organization_id = p_org_id
828: AND mmtt.transfer_lpn_id = p_lpn_id
829: )
830: --Added for Bug 6717052
832: SELECT 'x'
833: FROM dual
834: WHERE EXISTS
835: ( SELECT 'x'
836: FROM mtl_material_transactions_temp mmtt
837: WHERE mmtt.organization_id = p_org_id
838: AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
839: WHERE wlpn.outermost_lpn_id = p_lpn_id));
840:
844: , p_lpn_id IN NUMBER
845: ) IS
846: SELECT mmtt.transaction_temp_id
847: , mmtt.transaction_type_id
848: FROM mtl_material_transactions_temp mmtt
849: WHERE mmtt.organization_id = p_org_id
850: AND mmtt.transfer_lpn_id = p_lpn_id
851: AND (mmtt.parent_line_id IS NULL
852: OR
858: --Added for Bug 6717052
859: UNION ALL
860: SELECT mmtt.transaction_temp_id
861: , mmtt.transaction_type_id
862: FROM mtl_material_transactions_temp mmtt
863: WHERE mmtt.organization_id = p_org_id
864: AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
865: WHERE wlpn.outermost_lpn_id = p_lpn_id)
866: AND (mmtt.parent_line_id IS NULL
881: ||';'||
882: to_char(mtrl.line_status)
883: )
884: )
885: FROM mtl_material_transactions_temp mmtt
886: , mtl_txn_request_lines mtrl
887: , mtl_txn_request_headers mtrh
888: WHERE mmtt.organization_id = p_org_id
889: --Modified for Bug 6717052
896: ( p_org_id IN NUMBER
897: , p_lpn_id IN NUMBER
898: ) IS
899: SELECT COUNT (DISTINCT mmtt.transaction_action_id)
900: FROM mtl_material_transactions_temp mmtt
901: WHERE mmtt.organization_id = p_org_id
902: --Added for Bug 6717052
903: AND (mmtt.transfer_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
904: WHERE wlpn.outermost_lpn_id = p_lpn_id))
917: ) IS
918: SELECT mmtt.transaction_type_id
919: , mmtt.transaction_source_type_id
920: , mmtt.transaction_action_id
921: FROM mtl_material_transactions_temp mmtt
922: WHERE mmtt.organization_id = p_org_id
923: AND mmtt.transfer_lpn_id = p_lpn_id
924: AND (mmtt.parent_line_id IS NULL
925: OR
932: UNION ALL
933: SELECT mmtt.transaction_type_id
934: , mmtt.transaction_source_type_id
935: , mmtt.transaction_action_id
936: FROM mtl_material_transactions_temp mmtt
937: WHERE mmtt.organization_id = p_org_id
938: AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
939: WHERE wlpn.outermost_lpn_id = p_lpn_id)
940: AND (mmtt.parent_line_id IS NULL
959: )
960: )
961: )
962: )
963: FROM mtl_material_transactions_temp mmtt
964: , mtl_txn_request_lines mtrl
965: , wsh_delivery_details_ob_grp_v wdd
966: , wsh_delivery_assignments_v wda
967: WHERE mmtt.organization_id = p_org_id
986: ( p_org_id IN NUMBER
987: , p_lpn_id IN NUMBER
988: ) IS
989: SELECT Count(DISTINCT wda.delivery_id)
990: FROM mtl_material_transactions_temp mmtt
991: , mtl_txn_request_lines mtrl
992: , wsh_delivery_details_ob_grp_v wdd
993: , wsh_delivery_assignments_v wda
994: WHERE mmtt.organization_id = p_org_id
1012: ( p_org_id IN NUMBER
1013: , p_lpn_id IN NUMBER
1014: ) IS
1015: SELECT DISTINCT delivery_detail_id
1016: FROM mtl_material_transactions_temp mmtt
1017: , mtl_txn_request_lines mtrl
1018: , wsh_delivery_details wdd
1019: WHERE mmtt.organization_id = p_org_id
1020: AND mmtt.transfer_lpn_id = p_lpn_id
1041: ||';'||
1042: to_char(mtrl.reference_id)
1043: )
1044: )
1045: FROM mtl_material_transactions_temp mmtt
1046: , mtl_txn_request_lines mtrl
1047: WHERE mmtt.organization_id = p_org_id
1048: AND mmtt.transfer_lpn_id = p_lpn_id
1049: AND mmtt.move_order_line_id = mtrl.line_id;
1053: ( p_org_id IN NUMBER
1054: , p_lpn_id IN NUMBER
1055: ) IS
1056: SELECT Decode (Count(DISTINCT transfer_subinventory||' '||transfer_to_location),1,'N','Y')
1057: FROM mtl_material_transactions_temp mmtt
1058: WHERE mmtt.organization_id = p_org_id
1059: AND mmtt.transfer_lpn_id = p_lpn_id
1060: AND ((mmtt.parent_line_id IS NOT NULL
1061: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
1074: ||';'||
1075: To_Char(Decode(g_chk_mult_subinv,'N','1',Decode(g_suggestion_drop,'NONE','1',mmtt.INVENTORY_ITEM_ID||';'||mmtt.revision))) -- Added for bug 12853197
1076: )
1077: )
1078: FROM mtl_material_transactions_temp mmtt
1079: WHERE mmtt.organization_id = p_org_id
1080: AND mmtt.transfer_lpn_id = p_lpn_id
1081: AND ( (mmtt.parent_line_id IS NOT NULL
1082: AND
1619: , mmtt.revision
1620: , mmtt.primary_quantity
1621: , mmtt.parent_line_id
1622: , mmtt.serial_allocated_flag
1623: FROM mtl_material_transactions_temp mmtt
1624: WHERE mmtt.transaction_temp_id = p_temp_id;
1625:
1626: c_mmtt_rec c_mmtt_info%ROWTYPE;
1627:
1656: SELECT 'x'
1657: FROM dual
1658: WHERE EXISTS
1659: ( SELECT 'x'
1660: FROM mtl_material_transactions_temp mmtt
1661: WHERE mmtt.organization_id = p_org_id
1662: AND mmtt.transfer_lpn_id = p_outer_lpn_id
1663: AND mmtt.parent_line_id = mmtt.transaction_temp_id
1664: AND mmtt.content_lpn_id = p_lpn_id
2321: -- on the child record
2322: -- Bug6913674 removed SUB & LOC from being nulled out
2323: IF c_mmtt_rec.parent_line_id IS NOT NULL
2324: THEN
2325: UPDATE mtl_material_transactions_temp mmtt
2326: SET lpn_id = NULL
2327: , content_lpn_id = NULL
2328: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
2329: END IF;
2378: INTO l_dummy
2379: FROM dual
2380: WHERE EXISTS
2381: ( SELECT 'x'
2382: FROM mtl_material_transactions_temp mmtt
2383: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2384: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2385: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2386: AND mmtt.transaction_quantity > 0
2400: INTO l_dummy
2401: FROM dual
2402: WHERE EXISTS
2403: ( SELECT 'x'
2404: FROM mtl_material_transactions_temp mmtt
2405: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2406: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2407: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2408: AND mmtt.transaction_quantity > 0
2447: IF l_inner_lpn_exists = 'Y'
2448: THEN
2449: --Bug6913674
2450: SELECT mmtt.content_lpn_id INTO l_inner_lpn_id
2451: FROM mtl_material_transactions_temp mmtt
2452: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2453: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2454: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2455: AND mmtt.transaction_quantity > 0
2469: , last_update_date = SYSDATE
2470: , last_updated_by = fnd_global.user_id
2471: WHERE lpn_id IN
2472: ( SELECT mmtt.content_lpn_id
2473: FROM mtl_material_transactions_temp mmtt
2474: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2475: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2476: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2477: AND mmtt.content_lpn_id IS NOT NULL
2497: , last_update_date = SYSDATE
2498: , last_updated_by = fnd_global.user_id
2499: WHERE lpn_id IN
2500: ( SELECT mmtt.content_lpn_id
2501: FROM mtl_material_transactions_temp mmtt
2502: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
2503: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
2504: AND mmtt.parent_line_id = mmtt.transaction_temp_id
2505: AND mmtt.content_lpn_id IS NOT NULL
2637: , l_api_name
2638: );
2639: END IF;
2640:
2641: INSERT INTO mtl_material_transactions_temp
2642: ( transaction_header_id
2643: , transaction_temp_id
2644: , source_code
2645: , source_line_id
3025: , NULL
3026: , operation_plan_id
3027: , move_order_header_id
3028: , serial_allocated_flag
3029: FROM mtl_material_transactions_temp
3030: WHERE transaction_temp_id = p_temp_id
3031: );
3032:
3033: g_current_drop_lpn.current_drop_list(l_new_temp_id) := 'PENDING';
3081: , wdt.loaded_time
3082: , mmtt.operation_plan_id
3083: , mmtt.move_order_line_id
3084: FROM wms_dispatched_tasks wdt
3085: , mtl_material_transactions_temp mmtt
3086: WHERE mmtt.transaction_temp_id = p_temp_id
3087: AND wdt.transaction_temp_id = mmtt.transaction_temp_id
3088: );
3089:
3164: END LOOP;
3165:
3166: l_transaction_temp_id:=NULL;
3167:
3168: l_mmtt_cur_str :='SELECT mmtt.TRANSACTION_TEMP_ID FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt '||
3169: ' WHERE mmtt.TRANSACTION_TEMP_ID=wdt.TRANSACTION_TEMP_ID(+) and mmtt.TRANSACTION_TEMP_ID in ('||l_temp_ids||') ORDER BY wdt.loaded_time';
3170:
3171: ii := 1;
3172:
3368: END IF;
3369: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3370: END IF;
3371:
3372: UPDATE mtl_material_transactions_temp
3373: SET transaction_quantity = transaction_quantity
3374: - lot_dtl_rec.transaction_quantity
3375: , primary_quantity = primary_quantity
3376: - lot_dtl_rec.primary_quantity
3388: , l_api_name
3389: );
3390: END IF;
3391:
3392: UPDATE mtl_material_transactions_temp
3393: SET transaction_quantity = lot_dtl_rec.transaction_quantity
3394: , primary_quantity = lot_dtl_rec.primary_quantity
3395: , reservation_quantity = DECODE( reservation_quantity
3396: , NULL, NULL
3541: , DECODE( mmtt.parent_line_id
3542: , NULL, 0
3543: , 1
3544: ) parent_line_id
3545: FROM mtl_material_transactions_temp mmtt
3546: , mtl_secondary_inventories msi
3547: , mtl_item_locations mil
3548: , mtl_txn_request_lines mtrl
3549: , wsh_delivery_details_ob_grp_v wdd
3589: , DECODE( mmtt.parent_line_id
3590: , NULL, 0
3591: , 1
3592: ) parent_line_id
3593: FROM mtl_material_transactions_temp mmtt
3594: , mtl_secondary_inventories msi
3595: , mtl_item_locations mil
3596: , mtl_txn_request_lines mtrl
3597: , mtl_txn_request_headers mtrh
3637: , DECODE( mmtt.parent_line_id
3638: , NULL, 0
3639: , 1
3640: ) parent_line_id
3641: FROM mtl_material_transactions_temp mmtt
3642: , mtl_secondary_inventories msi
3643: , mtl_item_locations mil
3644: , mtl_txn_request_lines mtrl
3645: , mtl_txn_request_headers mtrh
3683: , DECODE( mmtt.parent_line_id
3684: , NULL, 0
3685: , 1
3686: ) parent_line_id
3687: FROM mtl_material_transactions_temp mmtt
3688: , mtl_txn_request_lines mtrl
3689: WHERE mmtt.organization_id = p_org_id
3690: AND mmtt.transfer_lpn_id = p_lpn_id
3691: AND mmtt.transaction_action_id = 1
3720: , DECODE( mmtt.parent_line_id
3721: , NULL, 0
3722: , 1
3723: ) parent_line_id
3724: FROM mtl_material_transactions_temp mmtt
3725: , mtl_txn_request_lines mtrl
3726: WHERE mmtt.organization_id = p_org_id
3727: AND mmtt.transfer_lpn_id = p_lpn_id
3728: AND mmtt.transaction_action_id IN (2,28)
3756: , DECODE( mmtt.parent_line_id
3757: , NULL, 0
3758: , 1
3759: ) parent_line_id
3760: FROM mtl_material_transactions_temp mmtt
3761: WHERE mmtt.organization_id = p_org_id
3762: AND mmtt.transfer_lpn_id = p_lpn_id
3763: AND mmtt.transaction_action_id = 2
3764: AND mmtt.move_order_line_id IS NULL
3778: CURSOR c_get_txn_type
3779: ( p_temp_id IN NUMBER
3780: ) IS
3781: SELECT mmtt.transaction_type_id
3782: FROM mtl_material_transactions_temp mmtt
3783: WHERE mmtt.transaction_temp_id = p_temp_id;
3784:
3785: BEGIN
3786:
4253: CURSOR c_get_entity_type
4254: ( p_temp_id IN NUMBER
4255: ) IS
4256: SELECT wip_entity_type
4257: FROM mtl_material_transactions_temp mmtt
4258: , mtl_txn_request_lines mtrl
4259: , wip_entities we
4260: WHERE mmtt.transaction_temp_id = p_temp_id
4261: AND mmtt.transaction_source_type_id = 5
4416: decode(transaction_action_id, 28,mmtt.cartonization_id, NULL)
4417: --mmtt.cartonization_id has the LPN suggested by MDC
4418: --IT is applicable only to MDC case for staging transfer PickDrops
4419: -- For all others, it should be null
4420: FROM mtl_material_transactions_temp mmtt
4421: WHERE mmtt.transaction_temp_id = p_temp_id;
4422:
4423: BEGIN
4424: x_return_status := fnd_api.g_ret_sts_success;
4743: SELECT mmtt.primary_quantity
4744: , mmtt.inventory_item_id
4745: , mmtt.organization_id
4746: , mmtt.revision
4747: FROM mtl_material_transactions_temp mmtt
4748: WHERE mmtt.transaction_temp_id = p_tmp_id;
4749:
4750: mmtt_info_rec c_get_mmtt_info%ROWTYPE;
4751:
5090: , mmtt.primary_quantity
5091: , mmtt.subinventory_code
5092: , mmtt.locator_id
5093: FROM wms_license_plate_numbers wlpn
5094: , mtl_material_transactions_temp mmtt
5095: WHERE wlpn.license_plate_number = p_inner_lpn
5096: AND wlpn.organization_id = p_org_id
5097: AND mmtt.transfer_lpn_id = p_outer_lpn_id
5098: AND mmtt.organization_id = p_org_id
5223: , l_api_name
5224: );
5225: END IF;
5226:
5227: UPDATE mtl_material_transactions_temp mmtt
5228: SET lpn_id = l_inner_lpn_id
5229: , transfer_lpn_id = DECODE( x_ret_code
5230: , 2, l_inner_lpn_id
5231: , NULL
5352: , l_api_name
5353: );
5354: END IF;
5355:
5356: UPDATE mtl_material_transactions_temp mmtt
5357: SET lpn_id = l_inner_lpn_id
5358: , transfer_lpn_id = l_inner_lpn_id
5359: , subinventory_code = l_from_sub
5360: , locator_id = l_from_loc_id
5403: END IF;
5404: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5405: END IF;
5406:
5407: UPDATE mtl_material_transactions_temp
5408: SET transaction_quantity = DECODE( transaction_uom
5409: , p_primary_uom, l_remaining_qty
5410: , inv_convert.inv_um_convert
5411: ( p_item_id
5432: , l_api_name
5433: );
5434: END IF;
5435:
5436: UPDATE mtl_material_transactions_temp
5437: SET transaction_quantity = transaction_quantity - l_txn_qty
5438: , primary_quantity = primary_quantity - l_remaining_qty
5439: , reservation_quantity = DECODE( reservation_quantity
5440: , NULL, NULL
5521: END IF; -- checking LPN avail qty against entered qty
5522:
5523: IF x_ret_code IN (2,3)
5524: THEN
5525: UPDATE mtl_material_transactions_temp mmtt
5526: SET content_lpn_id = NULL
5527: , transaction_quantity = 0
5528: , primary_quantity = 0
5529: , reservation_quantity = 0
5528: , primary_quantity = 0
5529: , reservation_quantity = 0
5530: WHERE mmtt.transaction_temp_id = l_parent_temp_id;
5531: ELSE
5532: UPDATE mtl_material_transactions_temp mmtt
5533: SET transaction_quantity = DECODE( transaction_uom
5534: , p_primary_uom, (primary_quantity - l_tot_mmtt_qty)
5535: , inv_convert.inv_um_convert
5536: ( p_item_id
5557: INTO l_dummy
5558: FROM dual
5559: WHERE EXISTS
5560: ( SELECT 'x'
5561: FROM mtl_material_transactions_temp mmtt
5562: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
5563: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
5564: AND mmtt.parent_line_id = mmtt.transaction_temp_id
5565: AND mmtt.transaction_quantity > 0
5651: , p_itm_id IN NUMBER
5652: , p_rev IN VARCHAR2
5653: ) IS
5654: SELECT NVL(SUM(mmtt.primary_quantity),0)
5655: FROM mtl_material_transactions_temp mmtt
5656: WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
5657: AND mmtt.organization_id = p_org_id
5658: AND mmtt.parent_line_id = mmtt.transaction_temp_id
5659: AND mmtt.transaction_quantity > 0
5668: CURSOR c_get_mmtt_qty
5669: ( p_temp_id IN NUMBER
5670: ) IS
5671: SELECT primary_quantity
5672: FROM mtl_material_transactions_temp
5673: WHERE transaction_temp_id = p_temp_id;
5674:
5675:
5676: CURSOR c_get_parents
5679: , p_itm_id IN NUMBER
5680: , p_rev IN VARCHAR2
5681: ) IS
5682: SELECT mmtt.transaction_temp_id
5683: FROM mtl_material_transactions_temp mmtt
5684: WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
5685: AND mmtt.organization_id = p_org_id
5686: AND mmtt.parent_line_id = mmtt.transaction_temp_id
5687: AND mmtt.transaction_quantity > 0
5700: , mmtt.content_lpn_id
5701: , mmtt.subinventory_code
5702: , mmtt.locator_id
5703: , mmtt.primary_quantity
5704: FROM mtl_material_transactions_temp mmtt
5705: WHERE mmtt.transaction_temp_id = p_temp_id;
5706:
5707: parent_rec c_get_parent_attributes%ROWTYPE;
5708:
5859: END IF;
5860: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5861: END IF;
5862:
5863: UPDATE mtl_material_transactions_temp
5864: SET transaction_quantity = DECODE( transaction_uom
5865: , p_primary_uom, l_remaining_qty
5866: , inv_convert.inv_um_convert
5867: ( p_item_id
5888: , l_api_name
5889: );
5890: END IF;
5891:
5892: UPDATE mtl_material_transactions_temp
5893: SET transaction_quantity = transaction_quantity - l_txn_qty
5894: , primary_quantity = primary_quantity - l_remaining_qty
5895: , reservation_quantity = DECODE( reservation_quantity
5896: , NULL, NULL
5998: , l_api_name
5999: );
6000: END IF;
6001:
6002: UPDATE mtl_material_transactions_temp mmtt
6003: SET lpn_id = NVL(parent_rec.content_lpn_id,parent_rec.lpn_id)
6004: , subinventory_code = parent_rec.subinventory_code
6005: , locator_id = parent_rec.locator_id
6006: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
6026: , l_api_name
6027: );
6028: END IF;
6029:
6030: UPDATE mtl_material_transactions_temp mmtt
6031: SET transaction_quantity = DECODE( transaction_uom
6032: , p_primary_uom, (primary_quantity - l_mmtt_qty)
6033: , inv_convert.inv_um_convert
6034: ( p_item_id
6064: END IF;
6065: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6066: END IF;
6067:
6068: UPDATE mtl_material_transactions_temp
6069: SET transaction_quantity = DECODE( transaction_uom
6070: , p_primary_uom, l_remaining_qty
6071: , inv_convert.inv_um_convert
6072: ( p_item_id
6093: , l_api_name
6094: );
6095: END IF;
6096:
6097: UPDATE mtl_material_transactions_temp
6098: SET transaction_quantity = transaction_quantity - l_txn_qty
6099: , primary_quantity = primary_quantity - l_remaining_qty
6100: , reservation_quantity = DECODE( reservation_quantity
6101: , NULL, NULL
6118:
6119: jj := jj + 1;
6120: l_process_tbl(jj) := l_new_temp_id;
6121:
6122: UPDATE mtl_material_transactions_temp mmtt
6123: SET transaction_quantity = 0
6124: , primary_quantity = 0
6125: , reservation_quantity = 0
6126: WHERE mmtt.transaction_temp_id = l_parent_temp_id;
6182: INTO l_dummy
6183: FROM dual
6184: WHERE EXISTS
6185: ( SELECT 'x'
6186: FROM mtl_material_transactions_temp mmtt
6187: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
6188: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
6189: AND mmtt.parent_line_id = mmtt.transaction_temp_id
6190: AND mmtt.transaction_quantity > 0
6283: , p_org_id IN NUMBER
6284: ) IS
6285: SELECT wlpn.lpn_id
6286: FROM wms_license_plate_numbers wlpn
6287: , mtl_material_transactions_temp mmtt
6288: WHERE wlpn.license_plate_number = p_inner_lpn
6289: AND wlpn.organization_id = p_org_id
6290: AND mmtt.transfer_lpn_id = p_outer_lpn_id
6291: AND mmtt.organization_id = p_org_id
6299: , p_serial_num IN VARCHAR2
6300: ) IS
6301: SELECT msnt.rowid
6302: , mmtt.transaction_temp_id
6303: FROM mtl_material_transactions_temp mmtt
6304: , mtl_serial_numbers_temp msnt
6305: WHERE mmtt.organization_id = p_org_id
6306: AND mmtt.transfer_lpn_id = p_xfer_lpn_id
6307: AND mmtt.transaction_temp_id = mmtt.parent_line_id
6321: , p_lpn_id IN NUMBER
6322: ) IS
6323: SELECT msnt.rowid
6324: , mmtt.transaction_temp_id
6325: FROM mtl_material_transactions_temp mmtt
6326: , mtl_serial_numbers_temp msnt
6327: WHERE mmtt.organization_id = p_org_id
6328: AND mmtt.transfer_lpn_id = p_xfer_lpn_id
6329: AND mmtt.transaction_temp_id = mmtt.parent_line_id
6340: , p_lpn_id IN NUMBER
6341: ) IS
6342: SELECT msnt.rowid
6343: , mmtt.transaction_temp_id transaction_temp_id
6344: FROM mtl_material_transactions_temp mmtt
6345: , mtl_transaction_lots_temp mtlt
6346: , mtl_serial_numbers_temp msnt
6347: WHERE mmtt.organization_id = p_org_id
6348: AND mmtt.transfer_lpn_id = p_xfer_lpn_id
6359: CURSOR c_get_parent
6360: ( p_temp_id IN NUMBER
6361: ) IS
6362: SELECT parent_line_id
6363: FROM mtl_material_transactions_temp mmtt
6364: WHERE mmtt.transaction_temp_id = p_temp_id;
6365:
6366:
6367: CURSOR c_get_parent_attributes
6370: SELECT mmtt.lpn_id
6371: , mmtt.content_lpn_id
6372: , mmtt.subinventory_code
6373: , mmtt.locator_id
6374: FROM mtl_material_transactions_temp mmtt
6375: WHERE mmtt.transaction_temp_id = p_temp_id;
6376:
6377: parent_rec c_get_parent_attributes%ROWTYPE;
6378:
6382: , p_lot_num IN VARCHAR2
6383: ) IS
6384: SELECT mtlt.primary_quantity
6385: , COUNT(msnt.fm_serial_number) srl_count
6386: FROM mtl_material_transactions_temp mmtt
6387: , mtl_transaction_lots_temp mtlt
6388: , mtl_serial_numbers_temp msnt
6389: WHERE mmtt.transaction_temp_id = p_temp_id
6390: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
6397: ( p_temp_id IN NUMBER
6398: ) IS
6399: SELECT mmtt.primary_quantity
6400: , COUNT(msnt.fm_serial_number) srl_count
6401: FROM mtl_material_transactions_temp mmtt
6402: , mtl_serial_numbers_temp msnt
6403: WHERE mmtt.transaction_temp_id = p_temp_id
6404: AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id
6405: GROUP BY mmtt.primary_quantity;
6669: END IF;
6670: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6671: END IF;
6672:
6673: UPDATE mtl_material_transactions_temp
6674: SET transaction_quantity = transaction_quantity - 1
6675: , primary_quantity = primary_quantity - 1
6676: , reservation_quantity = DECODE( reservation_quantity
6677: , NULL, NULL
6713: OPEN c_get_parent_attributes (lot_msnt_rec.transaction_temp_id);
6714: FETCH c_get_parent_attributes INTO parent_rec;
6715: CLOSE c_get_parent_attributes;
6716:
6717: UPDATE mtl_material_transactions_temp
6718: SET transaction_quantity = 1
6719: , primary_quantity = 1
6720: , reservation_quantity = DECODE( reservation_quantity
6721: , NULL, NULL
7000: OPEN c_get_parent_attributes (msnt_rec.transaction_temp_id);
7001: FETCH c_get_parent_attributes INTO parent_rec;
7002: CLOSE c_get_parent_attributes;
7003:
7004: UPDATE mtl_material_transactions_temp
7005: SET transaction_quantity = 1
7006: , primary_quantity = 1
7007: , reservation_quantity = DECODE( reservation_quantity
7008: , NULL, NULL
7042: OPEN c_get_parent_attributes (msnt_rec.transaction_temp_id);
7043: FETCH c_get_parent_attributes INTO parent_rec;
7044: CLOSE c_get_parent_attributes;
7045:
7046: UPDATE mtl_material_transactions_temp
7047: SET subinventory_code = parent_rec.subinventory_code
7048: , locator_id = parent_rec.locator_id
7049: , lpn_id = NVL(parent_rec.content_lpn_id,parent_rec.lpn_id)
7050: WHERE transaction_temp_id = l_transaction_temp_id;
7141: --
7142: -- Now update the parent
7143: --
7144: BEGIN
7145: UPDATE mtl_material_transactions_temp
7146: SET transaction_quantity = transaction_quantity - 1
7147: , primary_quantity = primary_quantity - 1
7148: WHERE transaction_temp_id = l_parent_line_id;
7149:
7186:
7187: BEGIN
7188: SELECT mmtt.transaction_header_id
7189: INTO l_txn_header_id
7190: FROM mtl_material_transactions_temp mmtt
7191: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
7192: EXCEPTION
7193: WHEN OTHERS THEN
7194: IF l_debug = 1 THEN
7369: INTO l_dummy
7370: FROM dual
7371: WHERE EXISTS
7372: ( SELECT 'x'
7373: FROM mtl_material_transactions_temp mmtt
7374: WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
7375: AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
7376: AND mmtt.parent_line_id = mmtt.transaction_temp_id
7377: AND mmtt.transaction_quantity > 0
7438: SELECT mmtt.transaction_temp_id
7439: , wdt.suggested_dest_subinventory
7440: , wdt.suggested_dest_locator_id
7441: FROM ( SELECT mmtt2.transaction_temp_id
7442: FROM mtl_material_transactions_temp mmtt2
7443: WHERE mmtt2.organization_id = p_org_id
7444: AND mmtt2.transfer_lpn_id = p_lpn_id
7445: AND mmtt2.parent_line_id IS NULL
7446: UNION
7444: AND mmtt2.transfer_lpn_id = p_lpn_id
7445: AND mmtt2.parent_line_id IS NULL
7446: UNION
7447: SELECT mmtt3.transaction_temp_id
7448: FROM mtl_material_transactions_temp mmtt3
7449: WHERE mmtt3.parent_line_id IS NOT NULL
7450: AND mmtt3.transaction_temp_id <> mmtt3.parent_line_id
7451: AND mmtt3.transaction_temp_id IN
7452: ( SELECT mmtt4.transaction_temp_id
7449: WHERE mmtt3.parent_line_id IS NOT NULL
7450: AND mmtt3.transaction_temp_id <> mmtt3.parent_line_id
7451: AND mmtt3.transaction_temp_id IN
7452: ( SELECT mmtt4.transaction_temp_id
7453: FROM mtl_material_transactions_temp mmtt4
7454: WHERE mmtt4.organization_id = p_org_id
7455: AND mmtt4.transfer_lpn_id = p_lpn_id
7456: START WITH
7457: mmtt4.transaction_temp_id = mmtt4.parent_line_id
7521: FETCH c_get_temp_ids BULK COLLECT INTO v_temp_id_tbl, v_sub_tbl, v_loc_id_tbl;
7522: CLOSE c_get_temp_ids;
7523:
7524: FORALL ii IN v_temp_id_tbl.FIRST..v_temp_id_tbl.LAST
7525: UPDATE mtl_material_transactions_temp
7526: SET transfer_subinventory = v_sub_tbl(ii)
7527: , transfer_to_location = v_loc_id_tbl(ii)
7528: WHERE transaction_temp_id = v_temp_id_tbl(ii);
7529:
7544: -- Bug 4884284: Delete child WDT records
7545: DELETE wms_dispatched_tasks
7546: WHERE transaction_temp_id IN
7547: ( SELECT transaction_temp_id
7548: FROM mtl_material_transactions_temp mmtt
7549: WHERE mmtt.parent_line_id IS NOT NULL
7550: AND mmtt.transaction_temp_id <> NVL(mmtt.parent_line_id,0)
7551: AND mmtt.transaction_temp_id IN
7552: ( SELECT mmtt2.transaction_temp_id
7549: WHERE mmtt.parent_line_id IS NOT NULL
7550: AND mmtt.transaction_temp_id <> NVL(mmtt.parent_line_id,0)
7551: AND mmtt.transaction_temp_id IN
7552: ( SELECT mmtt2.transaction_temp_id
7553: FROM mtl_material_transactions_temp mmtt2
7554: WHERE mmtt2.organization_id = p_organization_id
7555: AND mmtt2.transfer_lpn_id = p_transfer_lpn_id
7556: START WITH
7557: mmtt2.transaction_temp_id = mmtt2.parent_line_id
7586: , last_update_date = SYSDATE
7587: , last_updated_by = fnd_global.user_id
7588: WHERE wlpn1.lpn_id IN
7589: ( SELECT mmtt.content_lpn_id
7590: FROM mtl_material_transactions_temp mmtt
7591: , wms_license_plate_numbers wlpn2
7592: WHERE mmtt.transfer_lpn_id = p_transfer_lpn_id
7593: AND mmtt.organization_id = p_organization_id
7594: AND mmtt.parent_line_id = mmtt.transaction_temp_id
7688: SELECT mmtt.transfer_subinventory
7689: , mmtt.transfer_to_location
7690: , mtrl.carton_grouping_id
7691: , wda.delivery_id
7692: FROM mtl_material_transactions_temp mmtt
7693: , mtl_txn_request_lines mtrl
7694: , wsh_delivery_details_ob_grp_v wdd
7695: , wsh_delivery_assignments_v wda
7696: WHERE mmtt.organization_id = p_org_id
7722: CURSOR c_get_cur_carton_grp
7723: ( p_temp_id IN NUMBER
7724: ) IS
7725: SELECT mtrl.carton_grouping_id
7726: FROM mtl_material_transactions_temp mmtt
7727: , mtl_txn_request_lines mtrl
7728: WHERE mmtt.transaction_temp_id = p_temp_id
7729: AND mmtt.move_order_line_id = mtrl.line_id;
7730:
7988: END IF;
7989:
7990: WHILE (ii <= jj)
7991: LOOP
7992: UPDATE mtl_material_transactions_temp mmtt
7993: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
7994: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
7995: RETURNING primary_quantity
7996: INTO l_primary_qty;
7999: THEN
8000: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
8001: := 'DONE';
8002: ELSE
8003: DELETE mtl_material_transactions_temp
8004: WHERE transaction_temp_id = l_transaction_temp_id;
8005:
8006: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
8007:
8069: END IF;
8070:
8071: WHILE (ii <= jj)
8072: LOOP
8073: UPDATE mtl_material_transactions_temp mmtt
8074: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
8075: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
8076: RETURNING primary_quantity
8077: INTO l_primary_qty;
8080: THEN
8081: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
8082: := 'DONE';
8083: ELSE
8084: DELETE mtl_material_transactions_temp
8085: WHERE transaction_temp_id = l_transaction_temp_id;
8086:
8087: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
8088:
8150: END IF;
8151:
8152: WHILE (ii <= jj)
8153: LOOP
8154: UPDATE mtl_material_transactions_temp mmtt
8155: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
8156: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
8157: RETURNING primary_quantity
8158: INTO l_primary_qty;
8161: THEN
8162: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
8163: := 'DONE';
8164: ELSE
8165: DELETE mtl_material_transactions_temp
8166: WHERE transaction_temp_id = l_transaction_temp_id;
8167:
8168: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
8169:
8232: END IF;
8233:
8234: WHILE (ii <= jj)
8235: LOOP
8236: UPDATE mtl_material_transactions_temp mmtt
8237: SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
8238: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
8239: RETURNING primary_quantity
8240: INTO l_primary_qty;
8243: THEN
8244: g_current_drop_lpn.current_drop_list(l_transaction_temp_id)
8245: := 'DONE';
8246: ELSE
8247: DELETE mtl_material_transactions_temp
8248: WHERE transaction_temp_id = l_transaction_temp_id;
8249:
8250: g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
8251:
8339:
8340: BEGIN
8341: SELECT mmtt.transaction_temp_id
8342: INTO l_child_temp_id
8343: FROM mtl_material_transactions_temp mmtt
8344: WHERE mmtt.parent_line_id = p_parent_temp_id
8345: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8346: AND ROWNUM < 2;
8347:
8374: , l_api_name
8375: );
8376: END IF;
8377:
8378: INSERT INTO mtl_material_transactions_temp
8379: ( transaction_header_id
8380: , transaction_temp_id
8381: , source_code
8382: , source_line_id
8760: , p_lpn_id
8761: , operation_plan_id
8762: , transaction_header_id
8763: , l_pack_temp_id
8764: FROM mtl_material_transactions_temp
8765: WHERE transaction_temp_id = l_child_temp_id
8766: );
8767:
8768: x_pack_temp_id := l_pack_temp_id;
8826: , p_lpn_id IN NUMBER
8827: ) IS
8828: -- Material unpacked from nested LPNs
8829: SELECT mmtt.transaction_temp_id
8830: FROM mtl_material_transactions_temp mmtt
8831: WHERE mmtt.organization_id = p_org_id
8832: AND mmtt.transfer_lpn_id = p_lpn_id
8833: AND mmtt.parent_line_id IS NOT NULL
8834: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8835: AND mmtt.content_lpn_id IS NULL
8836: AND mmtt.lpn_id IS NOT NULL
8837: AND EXISTS
8838: ( SELECT 'x'
8839: FROM mtl_material_transactions_temp mmtt2
8840: WHERE mmtt2.organization_id = p_org_id
8841: AND mmtt2.transfer_lpn_id = p_lpn_id
8842: AND mmtt2.content_lpn_id = mmtt.lpn_id
8843: AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
8845: )
8846: UNION ALL
8847: -- Nested LPNs
8848: SELECT mmtt.transaction_temp_id
8849: FROM mtl_material_transactions_temp mmtt
8850: WHERE mmtt.organization_id = p_org_id
8851: AND mmtt.transfer_lpn_id = p_lpn_id
8852: AND mmtt.parent_line_id IS NOT NULL
8853: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8854: AND mmtt.content_lpn_id IS NULL
8855: AND mmtt.lpn_id IS NOT NULL
8856: AND EXISTS
8857: ( SELECT 'x'
8858: FROM mtl_material_transactions_temp mmtt2
8859: WHERE mmtt2.organization_id = p_org_id
8860: AND mmtt2.transfer_lpn_id = p_lpn_id
8861: AND mmtt2.content_lpn_id = mmtt.lpn_id
8862: AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
8864: )
8865: UNION ALL
8866: -- Picked from LPN that is not a nested LPN
8867: SELECT mmtt.transaction_temp_id
8868: FROM mtl_material_transactions_temp mmtt
8869: WHERE mmtt.organization_id = p_org_id
8870: AND mmtt.transfer_lpn_id = p_lpn_id
8871: AND mmtt.parent_line_id IS NOT NULL
8872: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8873: AND mmtt.content_lpn_id IS NULL
8874: AND mmtt.lpn_id IS NOT NULL
8875: AND NOT EXISTS
8876: ( SELECT 'x'
8877: FROM mtl_material_transactions_temp mmtt2
8878: WHERE mmtt2.organization_id = p_org_id
8879: AND mmtt2.transfer_lpn_id = p_lpn_id
8880: AND mmtt2.content_lpn_id = mmtt.lpn_id
8881: AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
8882: )
8883: UNION ALL
8884: -- Loose material
8885: SELECT mmtt.transaction_temp_id
8886: FROM mtl_material_transactions_temp mmtt
8887: WHERE mmtt.organization_id = p_org_id
8888: AND mmtt.transfer_lpn_id = p_lpn_id
8889: AND mmtt.parent_line_id IS NOT NULL
8890: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8897: , p_lpn_id IN NUMBER
8898: ) IS
8899: SELECT mmtt.transaction_temp_id
8900: , mmtt.content_lpn_id
8901: FROM mtl_material_transactions_temp mmtt
8902: WHERE mmtt.organization_id = p_org_id
8903: AND mmtt.transfer_lpn_id = p_lpn_id
8904: AND mmtt.parent_line_id = mmtt.transaction_temp_id
8905: AND mmtt.content_lpn_id IS NOT NULL;
8908: CURSOR c_chk_child_records
8909: ( p_parent_temp_id IN NUMBER
8910: ) IS
8911: SELECT 'x'
8912: FROM mtl_material_transactions_temp mmtt
8913: WHERE mmtt.parent_line_id = p_parent_temp_id
8914: AND mmtt.parent_line_id <> mmtt.transaction_temp_id
8915: AND mmtt.transaction_action_id in (2,28)
8916: AND EXISTS
9003: CLOSE c_chk_child_records;
9004:
9005: IF nested_lpn_rec.content_lpn_id <> p_xfer_lpn_id
9006: THEN
9007: UPDATE mtl_material_transactions_temp mmtt
9008: SET mmtt.transfer_lpn_id = nested_lpn_rec.content_lpn_id
9009: , mmtt.lpn_id = NVL(mmtt.lpn_id,nested_lpn_rec.content_lpn_id) -- Bug 5620764
9010: WHERE mmtt.parent_line_id = nested_lpn_rec.transaction_temp_id
9011: AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
9237: mmtt.revision ,
9238: mmtt.subinventory_code,
9239: mmtt.locator_id,
9240: mmtt.transaction_date
9241: FROM mtl_material_transactions_temp mmtt
9242: WHERE mmtt.organization_id = p_org_id
9243: AND mmtt.transfer_lpn_id = p_lpn_id
9244: AND mmtt.parent_line_id IS NULL
9245: AND mmtt.content_lpn_id IS NULL
9245: AND mmtt.content_lpn_id IS NULL
9246: AND mmtt.lpn_id IS NOT NULL
9247: AND EXISTS
9248: ( SELECT 'x'
9249: FROM mtl_material_transactions_temp mmtt2
9250: WHERE mmtt2.organization_id = p_org_id
9251: AND mmtt2.transfer_lpn_id = p_lpn_id
9252: AND mmtt2.content_lpn_id = mmtt.lpn_id
9253: AND mmtt2.transaction_temp_id <> mmtt.transaction_temp_id
9260: mmtt.revision ,
9261: mmtt.subinventory_code,
9262: mmtt.locator_id,
9263: mmtt.transaction_date
9264: FROM mtl_material_transactions_temp mmtt
9265: WHERE mmtt.organization_id = p_org_id
9266: AND mmtt.transfer_lpn_id = p_lpn_id
9267: AND mmtt.parent_line_id IS NULL
9268: AND mmtt.content_lpn_id IS NOT NULL
9274: mmtt.revision ,
9275: mmtt.subinventory_code,
9276: mmtt.locator_id,
9277: mmtt.transaction_date
9278: FROM mtl_material_transactions_temp mmtt
9279: WHERE mmtt.organization_id = p_org_id
9280: AND mmtt.transfer_lpn_id = p_lpn_id
9281: AND mmtt.parent_line_id IS NULL
9282: AND mmtt.content_lpn_id IS NULL
9282: AND mmtt.content_lpn_id IS NULL
9283: AND mmtt.lpn_id IS NOT NULL
9284: AND NOT EXISTS
9285: ( SELECT 'x'
9286: FROM mtl_material_transactions_temp mmtt2
9287: WHERE mmtt2.organization_id = p_org_id
9288: AND mmtt2.transfer_lpn_id = p_lpn_id
9289: AND mmtt2.content_lpn_id = mmtt.lpn_id
9290: AND mmtt2.transaction_temp_id <> mmtt.transaction_temp_id
9297: mmtt.revision ,
9298: mmtt.subinventory_code,
9299: mmtt.locator_id,
9300: mmtt.transaction_date
9301: FROM mtl_material_transactions_temp mmtt
9302: WHERE mmtt.organization_id = p_org_id
9303: AND mmtt.transfer_lpn_id IN (SELECT lpn_id FROM wms_license_plate_numbers
9304: WHERE outermost_lpn_id = p_lpn_id AND lpn_id <> outermost_lpn_id)
9305: AND mmtt.parent_line_id IS NULL
9311: mmtt.revision ,
9312: mmtt.subinventory_code,
9313: mmtt.locator_id,
9314: mmtt.transaction_date
9315: FROM mtl_material_transactions_temp mmtt
9316: WHERE mmtt.organization_id = p_org_id
9317: AND mmtt.transfer_lpn_id = p_lpn_id
9318: AND mmtt.parent_line_id IS NULL
9319: AND mmtt.content_lpn_id IS NULL
9334: ( p_temp_id IN NUMBER
9335: ) IS
9336: SELECT mmtt.parent_line_id
9337: , mmtt.transaction_type_id
9338: FROM mtl_material_transactions_temp mmtt
9339: WHERE mmtt.transaction_temp_id = p_temp_id;
9340:
9341:
9342: CURSOR c_get_parent_tasks
9344: , p_lpn_id IN NUMBER
9345: ) IS
9346: SELECT mmtt.transaction_temp_id
9347: , wdt.task_id
9348: FROM mtl_material_transactions_temp mmtt
9349: , wms_dispatched_tasks wdt
9350: WHERE mmtt.organization_id = p_org_id
9351: AND mmtt.transfer_lpn_id = p_lpn_id
9352: AND mmtt.parent_line_id IS NOT NULL
9357: CURSOR c_get_srl_alloc_stat
9358: ( p_temp_id IN NUMBER
9359: ) IS
9360: SELECT mmtt.serial_allocated_flag
9361: FROM mtl_material_transactions_temp mmtt
9362: WHERE transaction_temp_id = p_temp_id;
9363:
9364: BEGIN
9365: x_return_status := fnd_api.g_ret_sts_success;
9616: mmtt.inventory_item_id,
9617: mmtt.transaction_uom,
9618: mmtt.transaction_quantity
9619: INTO l_curr_xfer_lpn_id, l_inventory_item_id, l_transaction_uom, l_transaction_qty
9620: FROM mtl_material_transactions_temp mmtt
9621: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
9622:
9623: IF l_debug = 1 THEN
9624: print_debug
9817: , mmtt.move_order_line_id
9818: , mmtt.transfer_subinventory --bug 7319244
9819: , mmtt.transfer_to_location --bug 7319244
9820: FROM wms_dispatched_tasks wdt
9821: , mtl_material_transactions_temp mmtt
9822: WHERE wdt.transaction_temp_id = l_parent_line_id
9823: AND mmtt.transaction_temp_id = l_transaction_temp_id
9824: AND rownum = 1
9825: );
9863: --
9864: -- Update parent line to NULL
9865: --
9866: BEGIN
9867: UPDATE mtl_material_transactions_temp mmtt
9868: SET mmtt.parent_line_id = NULL
9869: WHERE transaction_temp_id = l_transaction_temp_id;
9870:
9871: IF l_debug = 1 THEN
9933:
9934: BEGIN
9935: IF l_txn_type_id = INV_GLOBALS.G_TYPE_CONTAINER_PACK
9936: THEN
9937: UPDATE mtl_material_transactions_temp mmtt
9938: SET mmtt.transaction_header_id = l_txn_header_id
9939: , mmtt.subinventory_code = p_subinventory
9940: , mmtt.locator_id = p_loc_id
9941: , mmtt.transfer_subinventory = p_subinventory
9954: , l_api_name
9955: );
9956: END IF;
9957: ELSIF l_txn_type_id = 2 THEN -- Sub Transfer Transaction created by bulk overpick
9958: UPDATE mtl_material_transactions_temp mmtt
9959: SET mmtt.transfer_subinventory = p_subinventory,
9960: mmtt.transfer_to_location = p_loc_id,
9961: mmtt.transaction_batch_id = l_txn_header_id,
9962: mmtt.transaction_batch_seq = l_batch_seq_id
9971: ', xfer loc to ' || to_char(p_loc_id)
9972: , l_api_name);
9973: END IF;
9974: ELSE
9975: UPDATE mtl_material_transactions_temp mmtt
9976: SET mmtt.transaction_batch_seq = l_batch_seq_id
9977: WHERE transaction_temp_id = l_transaction_temp_id;
9978:
9979: IF l_debug = 1 THEN
10011: l_transaction_temp_id := l_temp_tbl(ii);
10012: BEGIN
10013: SELECT mmtt.transfer_lpn_id
10014: INTO l_next_xfer_lpn_id
10015: FROM mtl_material_transactions_temp mmtt
10016: WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
10017:
10018: IF l_debug = 1 THEN
10019: print_debug
10377: ( p_org_id IN NUMBER
10378: , p_lpn_id IN NUMBER
10379: ) IS
10380: SELECT mmtt.transaction_temp_id
10381: FROM mtl_material_transactions_temp mmtt
10382: WHERE mmtt.organization_id = p_org_id
10383: AND mmtt.transfer_lpn_id = p_lpn_id
10384: AND ( (mmtt.parent_line_id IS NOT NULL
10385: AND
10779: CURSOR c_xfer_lpn_id
10780: ( p_temp_id IN NUMBER
10781: ) IS
10782: SELECT mmtt.transfer_lpn_id
10783: FROM mtl_material_transactions_temp mmtt
10784: WHERE mmtt.transaction_temp_id = p_temp_id;
10785:
10786:
10787: CURSOR c_drop_delivery
11103: AND rownum = 1 ;
11104:
11105: SELECT wdd.delivery_detail_id
11106: INTO l_line_rows(2)
11107: FROM wsh_delivery_details_ob_grp_v wdd, Mtl_material_transactions_temp mmtt
11108: WHERE mmtt.move_order_line_id = wdd.move_order_line_id
11109: AND wdd.organization_id = mmtt.organization_id
11110: AND mmtt.organization_id= p_organization_id
11111: AND mmtt.transfer_lpn_id= p_transfer_lpn_id