[Home] [Help]
195: BEGIN
196: --Get Max line number
197: SELECT MAX(line_number)
198: INTO l_line_num
199: FROM mtl_txn_request_lines
200: WHERE header_id = p_header_id;
201:
202: l_header_id := p_header_id;
203: l_org_id := p_org_id;
349: , to_cost_group_id
350: INTO l1
351: , l2
352: , l3
353: FROM mtl_txn_request_lines
354: WHERE line_id = l_cg_line;
355:
356: IF (l_debug = 1) THEN
357: mydebug('create_mo_line: Act Sub: ' || l1);
544: INTO l_project_comingle
545: FROM DUAL
546: WHERE EXISTS(
547: SELECT 1
548: FROM mtl_txn_request_lines
549: WHERE lpn_id = p_lpn
550: AND organization_id = p_org_id
551: AND line_status <> inv_globals.g_to_status_closed
552: AND NVL(project_id, -1) <> NVL(p_project_id, -1)
743: , to_cost_group_id
744: INTO l1
745: , l2
746: , l3
747: FROM mtl_txn_request_lines
748: WHERE line_id = l_cg_line;
749:
750: IF (l_debug = 1) THEN
751: mydebug('create_mo: Act Sub: ' || l1);
975: , NVL(crossdock_type, 1)
976: , mtrl.to_subinventory_code
977: , mtrl.to_locator_id
978: , mtrl.reference_detail_id
979: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
980: WHERE mtrl.lpn_id = p_lpn_id
981: AND mtrl.organization_id = p_org_id
982: AND mtrl.header_id = mtrh.header_id
983: AND mtrl.line_status <> inv_globals.g_to_status_closed
995: , NVL(crossdock_type, 1)
996: , mtrl.to_subinventory_code
997: , mtrl.to_locator_id
998: , mtrl.reference_detail_id
999: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
1000: WHERE mtrl.lpn_id = p_lpn_id
1001: AND mtrl.organization_id = p_org_id
1002: AND mtrl.header_id = mtrh.header_id
1003: AND mtrl.line_status <> inv_globals.g_to_status_closed
1016: , mol.to_locator_id mol_loc_id
1017: , mol.backorder_delivery_detail_id
1018: , mmtt.operation_plan_id
1019: , mol.inspection_status
1020: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
1021: WHERE mmtt.lpn_id = l_lpn_id
1022: -- Added following line for ATF_J
1023: AND mmtt.move_order_line_id = NVL(p_move_order_line_id, mmtt.move_order_line_id)
1024: AND mmtt.organization_id = p_org_id
1208: INTO l_rcount
1209: FROM DUAL
1210: WHERE EXISTS(
1211: SELECT 1
1212: FROM mtl_txn_request_lines l, mtl_txn_request_headers h
1213: WHERE l.lpn_id = l_lpn_id
1214: AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- added for ATF_J
1215: AND NVL(l.quantity_delivered, 0) < l.quantity -- added for ATF_J
1216: AND l.organization_id = p_org_id
1334: SELECT transaction_temp_id
1335: FROM mtl_material_transactions_temp
1336: WHERE move_order_line_id IN(
1337: SELECT mol.line_id
1338: FROM mtl_txn_request_lines mol
1339: WHERE mol.lpn_id = l_lpn_id
1340: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1341: AND mol.organization_id = p_org_id
1342: AND mol.quantity_detailed > 0
1357: FROM mtl_material_transactions_temp mmtt
1358: WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
1359: AND mmtt.move_order_line_id IN(
1360: SELECT mol.line_id
1361: FROM mtl_txn_request_lines mol
1362: WHERE mol.lpn_id = l_lpn_id
1363: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1364: AND mol.organization_id = p_org_id
1365: AND mol.quantity_detailed > 0
1371:
1372: DELETE FROM mtl_material_transactions_temp
1373: WHERE move_order_line_id IN(
1374: SELECT mol.line_id
1375: FROM mtl_txn_request_lines mol
1376: WHERE mol.lpn_id = l_lpn_id
1377: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1378: AND mol.organization_id = p_org_id
1379: AND mol.quantity_detailed > 0
1381: FROM mtl_txn_request_headers moh
1382: WHERE mol.header_id = moh.header_id
1383: AND moh.move_order_type = inv_globals.g_move_order_put_away));
1384:
1385: UPDATE mtl_txn_request_lines mol
1386: SET mol.line_status = inv_globals.g_to_status_closed
1387: WHERE mol.lpn_id = l_lpn_id
1388: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1389: AND mol.organization_id = p_org_id
1525: IF (l_debug = 1) THEN
1526: mydebug('Manual Drop: Updating Move orders to : Sub: ' || p_subinventory || ' loc : '||p_locator_id );
1527: END IF;
1528:
1529: UPDATE mtl_txn_request_lines mol
1530: SET to_subinventory_code = p_subinventory
1531: , to_locator_id = p_locator_id
1532: , quantity_detailed = NULL
1533: WHERE organization_id = p_org_id
1823: FROM
1824: wms_lpn_contents
1825: GROUP BY parent_lpn_id, inventory_item_id, revision, lot_number,uom_code,organization_id--BUG 4607833
1826: ) wlc, -- sub-query is necessary because there could be more than one wlc record for the same inventory_item_id, revision, lot_number
1827: mtl_txn_request_lines mol
1828: WHERE wlc.parent_lpn_id = mol.lpn_id
1829: AND wlc.inventory_item_id = mol.inventory_item_id
1830: -- AND wlc.uom_code = mol.uom_code -- Bug fix 3200526
1831: AND wlc.organization_id = mol.organization_id --Bug 4607833
2011:
2012:
2013: IF (l_ref_detail_id IS NOT NULL) THEN
2014: BEGIN
2015: UPDATE mtl_txn_request_lines
2016: SET reference_detail_id = NULL
2017: WHERE line_id = l_line_id;
2018: EXCEPTION
2019: WHEN OTHERS THEN
2093: mydebug('suggestions_pub: After calling inv_ppengine_pvt.create_suggestions');
2094: mydebug('suggestions_pub: l_return_status = ' || l_return_status);
2095: END IF;
2096:
2097: -- Update Qty detailed in Mtl_txn_request_lines
2098: -- Bug fix 2271470
2099: -- Need to consider quantity_delivered
2100:
2101: UPDATE mtl_txn_request_lines mol
2097: -- Update Qty detailed in Mtl_txn_request_lines
2098: -- Bug fix 2271470
2099: -- Need to consider quantity_delivered
2100:
2101: UPDATE mtl_txn_request_lines mol
2102: SET mol.quantity_detailed = (SELECT NVL(mol.quantity_delivered, 0) + NVL(SUM(mmtt.transaction_quantity), 0)
2103: FROM mtl_material_transactions_temp mmtt
2104: WHERE mmtt.move_order_line_id = l_line_id
2105: AND transaction_action_id NOT IN (50, 51, 52)
2209:
2210: /* nsinghi - added the If statement to update transaction_source_id in MMTT for Process Orgs. */
2211: IF (l_txn_source_type_id = 5 AND l_process_flag = 'Y') THEN
2212: SELECT txn_source_id INTO l_transaction_source_id
2213: FROM mtl_txn_request_lines
2214: WHERE line_id = l_line_id;
2215:
2216: UPDATE mtl_material_transactions_temp
2217: SET flow_schedule = 'N'
2324: -- set output variables
2325: -- Get total number of rows detailed
2326: SELECT COUNT(t.transaction_temp_id)
2327: INTO l_rows_detailed
2328: FROM mtl_material_transactions_temp t, mtl_txn_request_lines l
2329: WHERE l.lpn_id = l_lpn_id
2330: AND l.organization_id = p_org_id
2331: AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- Added for ATF_J
2332: AND l.line_id = t.move_order_line_id;
3709:
3710: , l_cost_group_id -- BUG 4134432
3711: , l_secondary_uom_code --OPM Convergence
3712:
3713: FROM mtl_txn_request_lines mol
3714: , mtl_material_transactions_temp mmtt
3715: WHERE mmtt.transaction_temp_id = p_temp_id
3716: AND mmtt.move_order_line_id = mol.line_id;
3717:
5112: END IF;
5113:
5114: --Update the wms_process_flag to 2 so that one can work with
5115: --with this move order line from elsewhere
5116: UPDATE mtl_txn_request_lines
5117: SET wms_process_flag = 2
5118: WHERE line_id = l_mo_line_id;
5119:
5120: --Storage subinventory - Call Deliver API
5734: END;*/
5735:
5736: --mydebug('After updating mmtt');
5737: -- Update MTL_TXN_REQUESTS_TABLE
5738: -- Since the uom code in mtl_txn_request_lines will always
5739: -- be the same as the transaction_uom in mmtt, no need to do the
5740: -- conversion again
5741: UPDATE mtl_txn_request_lines
5742: SET quantity_detailed = quantity_detailed - l_qty_diff
5737: -- Update MTL_TXN_REQUESTS_TABLE
5738: -- Since the uom code in mtl_txn_request_lines will always
5739: -- be the same as the transaction_uom in mmtt, no need to do the
5740: -- conversion again
5741: UPDATE mtl_txn_request_lines
5742: SET quantity_detailed = quantity_detailed - l_qty_diff
5743: WHERE line_id = l_line_id
5744: AND organization_id = l_org_id;
5745: --mydebug('After updating mol');
5806: -- This cursor will get the MMTTs assoicated with the LPN passed
5807: CURSOR c_lpn_mmtt_cursor IS
5808: SELECT mmtt.transaction_temp_id
5809: FROM mtl_material_transactions_temp mmtt
5810: ,mtl_txn_request_lines mtrl
5811: WHERE mtrl.line_id = mmtt.move_order_line_id
5812: AND mtrl.line_status = 7
5813: AND mtrl.lpn_id = p_lpn_id
5814: AND mtrl.organization_id = p_org_id;
5911: SELECT 1
5912: INTO l_mo_cnt
5913: FROM DUAL
5914: WHERE EXISTS(SELECT 1
5915: FROM mtl_txn_request_lines
5916: WHERE lpn_id = l_lpn_id
5917: AND organization_id = l_org_id
5918: );
5919: EXCEPTION
5938: SELECT 1
5939: INTO l_process_flag_cnt
5940: FROM DUAL
5941: WHERE EXISTS(SELECT 1
5942: FROM mtl_txn_request_lines
5943: WHERE lpn_id = l_lpn_id
5944: AND organization_id = l_org_id
5945: AND NVL(wms_process_flag, 1) = 2
5946: AND line_status <> 5); -- 3773255
6167: WHERE EXISTS(
6168: SELECT 1
6169: FROM wms_dispatched_tasks wdt
6170: , mtl_material_transactions_temp mmtt
6171: , mtl_txn_request_lines mtrl
6172: WHERE mtrl.lpn_id = l_lpn_id
6173: AND mtrl.organization_id = l_org_id
6174: AND mtrl.line_id = mmtt.move_order_line_id
6175: AND wdt.transaction_temp_id = mmtt.transaction_temp_id
6179: -- AND wdt.person_id = p_user_id));
6180: /*
6181: SELECT 1 INTO l_mo_cnt2 FROM DUAL WHERE exists
6182: (SELECT 1
6183: FROM mtl_txn_request_lines mol,
6184: mtl_material_transactions_temp mmtt
6185: , wms_dispatched_tasks wdt
6186: WHERE mol.lpn_id=l_lpn_id
6187: AND mol.organization_id=l_org_id
6726: AND lot_number = l_lot
6727: AND current_status = 4
6728: AND current_organization_id = l_org_id
6729: AND EXISTS (SELECT 1
6730: FROM mtl_txn_request_lines mol
6731: WHERE mol.line_id = l_mmtt_line_id
6732: AND mol.REFERENCE = 'ORDER_LINE_ID');
6733: END LOOP;
6734: CLOSE c_serial_number;
6755: AND inventory_item_id = l_item_id
6756: AND current_status = 4
6757: AND current_organization_id = l_org_id
6758: AND EXISTS (SELECT 1
6759: FROM mtl_txn_request_lines mol
6760: WHERE mol.line_id = l_mmtt_line_id
6761: AND mol.REFERENCE = 'ORDER_LINE_ID');
6762: END LOOP;
6763: CLOSE c_serial_number;
7005: ELSE -- p_from_lpn = p_to_lpn
7006: BEGIN
7007: SELECT COUNT(1)
7008: INTO l_count
7009: FROM mtl_txn_request_lines mol
7010: WHERE mol.lpn_id = l_to_lpn_id
7011: AND mol.line_status <> inv_globals.g_to_status_closed
7012: AND (
7013: (mol.quantity - NVL(mol.quantity_delivered, 0)) > (SELECT SUM(mmtt.transaction_quantity)
7350: -- Cursor added to retrieve valid MO header IDs so we can avoid
7351: -- the hash join problem.
7352: CURSOR c_header_id IS
7353: SELECT moh.header_id
7354: FROM mtl_txn_request_headers moh, mtl_txn_request_lines mol
7355: WHERE moh.move_order_type = inv_globals.g_move_order_put_away
7356: AND moh.header_id = mol.header_id
7357: AND mol.lpn_id = p_lpn_id;
7358: l_header_id NUMBER;
7468:
7469: EXIT WHEN lpn_contents_csr%NOTFOUND;
7470:
7471: -- Close existing move orders detailed.
7472: UPDATE mtl_txn_request_lines mol
7473: SET mol.line_status = inv_globals.g_to_status_closed
7474: WHERE mol.lpn_id = l_lpn_id
7475: AND mol.organization_id = p_org_id
7476: AND mol.quantity_detailed > 0
7628:
7629: CURSOR qty_check_cursor IS
7630: SELECT mtrl.primary_quantity
7631: , mmtt.primary_quantity
7632: FROM mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
7633: WHERE mtrl.organization_id = p_organization_id
7634: AND mtrl.lpn_id = l_lpn_id
7635: AND mmtt.move_order_line_id = mtrl.line_id;
7636:
7736: (SELECT transaction_temp_id
7737: FROM mtl_material_transactions_temp
7738: WHERE move_order_line_id IN
7739: (SELECT line_id
7740: FROM mtl_txn_request_lines
7741: WHERE organization_id = p_organization_id
7742: AND lpn_id = l_lpn_id)
7743: );
7744:
7747: (SELECT transaction_temp_id
7748: FROM mtl_material_transactions_temp
7749: WHERE move_order_line_id IN
7750: (SELECT line_id
7751: FROM mtl_txn_request_lines
7752: WHERE organization_id = p_organization_id
7753: AND lpn_id = l_lpn_id));
7754:
7755: DELETE FROM mtl_material_transactions_temp
7754:
7755: DELETE FROM mtl_material_transactions_temp
7756: WHERE move_order_line_id IN
7757: (SELECT line_id
7758: FROM mtl_txn_request_lines
7759: WHERE organization_id = p_organization_id
7760: AND lpn_id = l_lpn_id);
7761:
7762: IF (l_debug = 1) THEN
7768: -- Update the move order lines with the user inputted sub/loc.
7769: -- Also null out the quantity detailed in case the user enters
7770: -- a loc, validates it against rules, but then goes back and changes
7771: -- the sub/loc before processing it.
7772: UPDATE mtl_txn_request_lines
7773: SET to_subinventory_code = p_subinventory
7774: , to_locator_id = p_locator_id
7775: , quantity_detailed = NULL
7776: WHERE organization_id = p_organization_id
7828:
7829: -- Get the number of move order lines for this given LPN
7830: SELECT COUNT(1)
7831: INTO l_mo_lines_count
7832: FROM mtl_txn_request_lines
7833: WHERE organization_id = p_organization_id
7834: AND lpn_id = l_lpn_id
7835: AND line_status = 7;
7836:
7842:
7843: -- Check to see if MMTT allocations were created
7844: SELECT COUNT(mmtt.transaction_temp_id)
7845: INTO l_mmtt_lines_count
7846: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
7847: WHERE mtrl.lpn_id = l_lpn_id
7848: AND mtrl.organization_id = p_organization_id
7849: AND NVL(mmtt.wms_task_type, 0) <> -1
7850: AND mtrl.line_id = mmtt.move_order_line_id
8107: , mol.txn_source_id
8108: , mol.transaction_type_id
8109: , mol.transaction_source_type_id
8110: , mol.to_cost_group_id
8111: FROM mtl_txn_request_lines mol
8112: WHERE mol.organization_id = p_organization_id
8113: AND mol.header_id IN (SELECT moh.header_id
8114: FROM mtl_txn_request_headers moh
8115: WHERE moh.move_order_type = inv_globals.g_move_order_put_away
8225: l_mmtt_mol_id_tab,
8226: l_mmtt_sec_qty_tab, --OPM Convergence
8227: l_mmtt_sec_uom_tab --OPM Convergence
8228: FROM mtl_material_transactions_temp mmtt,
8229: mtl_txn_request_lines mtrl
8230: WHERE mmtt.organization_id = p_organization_id
8231: AND mmtt.move_order_line_id = mtrl.line_id
8232: AND mtrl.lpn_id IN (SELECT lpn_id
8233: FROM wms_license_plate_numbers
8481:
8482: --Need to update MOL.quantity_detailed
8483: --Because in WMSOPIBB.COMPLETE, it will deduct
8484: --MMTT.TRANSACTION_QUANTITY from MOL.QUANITY_DETIALED
8485: UPDATE mtl_txn_request_lines
8486: SET quantity_detailed = Nvl(quantity_detailed,0)+quantity
8487: WHERE line_id = v_mo_line.line_id;
8488:
8489: -- Update the MMTT record with the move order line,
8568: DELETE FROM mtl_transaction_lots_temp
8569: WHERE transaction_temp_id = l_mmtt_id_tab(i);
8570:
8571: FOR i IN 1..l_mmtt_id_tab.COUNT LOOP
8572: UPDATE mtl_txn_request_lines
8573: SET quantity_detailed = Nvl(quantity_detailed,0)
8574: -Decode(uom_code
8575: ,l_mmtt_uom_tab(i)
8576: ,l_mmtt_qty_tab(i)
8673: CURSOR l_item_txn_cursor IS
8674: SELECT inventory_item_id
8675: , lot_number
8676: , transaction_type_id
8677: FROM mtl_txn_request_lines
8678: WHERE organization_id = p_organization_id
8679: AND lpn_id = p_lpn_id;
8680:
8681: CURSOR l_serial_cursor IS
8921: , mmtt.transaction_uom
8922: , mmtt.parent_line_id --6962664
8923: , mmtt.transaction_header_id --6962664
8924: FROM mtl_material_transactions_temp mmtt,
8925: mtl_txn_request_lines mtrl
8926: WHERE mmtt.move_order_line_id = mtrl.line_id
8927: AND mmtt.organization_id = p_organization_id
8928: AND mmtt.lpn_id = l_current_lpn_id
8929: AND NVL(mmtt.wms_task_type, 0) <> -1;
9101: INTO x_crossdock
9102: FROM dual
9103: WHERE exists
9104: (SELECT 1
9105: FROM mtl_txn_request_lines
9106: WHERE lpn_id = p_lpn_id
9107: AND organization_id = p_organization_id
9108: AND backorder_delivery_detail_id IS NOT NULL);
9109: EXCEPTION