[Home] [Help]
1106: -- with a subquery which is how we are inserting the split MOL record.
1107: -- The alternative is to store each of the columns in a variable but that
1108: -- is not ideal.
1109: BEGIN
1110: INSERT INTO mtl_txn_request_lines
1111: (LINE_ID
1112: ,HEADER_ID
1113: ,LINE_NUMBER
1114: ,ORGANIZATION_ID
1204: ,OPERATION_SEQ_NUM
1205: ,WIP_SUPPLY_TYPE
1206: )
1207: (SELECT
1208: mtl_txn_request_lines_s.NEXTVAL -- LINE_ID
1209: ,HEADER_ID
1210: ,mtrl_max.line_num --LINE_NUMBER
1211: ,ORGANIZATION_ID
1212: ,INVENTORY_ITEM_ID
1303: ,l_wip_entity_id --WIP_ENTITY_ID
1304: ,l_repetitive_schedule_id --REPETITIVE_SCHEDULE_ID
1305: ,l_operation_seq_num --OPERATION_SEQ_NUM
1306: ,l_wip_supply_type --WIP_SUPPLY_TYPE
1307: FROM mtl_txn_request_lines mtrl, (SELECT MAX(line_number) + 1 AS line_num
1308: FROM mtl_txn_request_lines
1309: WHERE header_id = l_mol_header_id) mtrl_max
1310: WHERE mtrl.line_id = l_mol_line_id);
1311: EXCEPTION
1304: ,l_repetitive_schedule_id --REPETITIVE_SCHEDULE_ID
1305: ,l_operation_seq_num --OPERATION_SEQ_NUM
1306: ,l_wip_supply_type --WIP_SUPPLY_TYPE
1307: FROM mtl_txn_request_lines mtrl, (SELECT MAX(line_number) + 1 AS line_num
1308: FROM mtl_txn_request_lines
1309: WHERE header_id = l_mol_header_id) mtrl_max
1310: WHERE mtrl.line_id = l_mol_line_id);
1311: EXCEPTION
1312: WHEN OTHERS THEN
1316: -- Raise an exception. The caller will do the rollback, cleanups,
1317: -- and decide where to goto next.
1318: x_error_code := 'DB';
1319: RAISE FND_API.G_EXC_ERROR;
1320: END; -- End inserting split MOL record into MTL_TXN_REQUEST_LINES
1321:
1322: -- Retrieve the split MOL line ID we have just inserted above.
1323: -- We cannot use the RETURNING clause since a sub-query was used for the insert.
1324: -- As of 10g, this is not a supported feature.
1324: -- As of 10g, this is not a supported feature.
1325: BEGIN
1326: SELECT line_id
1327: INTO l_split_mol_line_id
1328: FROM mtl_txn_request_lines
1329: WHERE header_id = l_mol_header_id
1330: AND ROWNUM = 1
1331: ORDER BY line_number DESC;
1332: EXCEPTION
1345: END IF;
1346:
1347: -- Update the quantity on the original MOL record.
1348: BEGIN
1349: UPDATE mtl_txn_request_lines SET
1350: quantity = l_mol_qty - l_atd_qty,
1351: primary_quantity = l_mol_prim_qty - l_atd_prim_qty,
1352: secondary_quantity = l_mol_qty2 - l_atd_mol_qty2
1353: WHERE line_id = l_mol_line_id;
1388: -- {{
1389: -- Test for MOL qty = available to detail qty. MOL record should be
1390: -- crossdocked properly. }}11
1391: BEGIN
1392: UPDATE mtl_txn_request_lines SET
1393: backorder_delivery_detail_id = l_backorder_detail_id,
1394: crossdock_type = l_crossdock_type,
1395: wip_entity_id = l_wip_entity_id,
1396: repetitive_schedule_id = l_repetitive_schedule_id,
1980: mtrl.secondary_uom_code AS secondary_uom_code,
1981: mtrl.project_id AS project_id,
1982: mtrl.task_id AS task_id,
1983: mtrl.lpn_id AS lpn_id
1984: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
1985: wms_license_plate_numbers wlpn
1986: WHERE mtrl.header_id = mtrh.header_id
1987: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
1988: AND mtrl.organization_id = l_organization_id
1987: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
1988: AND mtrl.organization_id = l_organization_id
1989: AND mtrl.inventory_item_id = l_inventory_item_id
1990: -- Modified the line below to use an IN instead of <> so the
1991: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
1992: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
1993: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
1994: inv_globals.g_to_status_approved)
1995: AND mtrl.backorder_delivery_detail_id IS NULL
2294: mtrl.secondary_uom_code AS secondary_uom_code,
2295: mtrl.project_id AS project_id,
2296: mtrl.task_id AS task_id,
2297: mtrl.lpn_id AS lpn_id
2298: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
2299: wms_license_plate_numbers wlpn
2300: WHERE mtrl.header_id = mtrh.header_id
2301: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
2302: AND mtrl.organization_id = l_organization_id
2301: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
2302: AND mtrl.organization_id = l_organization_id
2303: AND mtrl.inventory_item_id = l_inventory_item_id
2304: -- Modified the line below to use an IN instead of <> so the
2305: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
2306: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
2307: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
2308: inv_globals.g_to_status_approved)
2309: AND mtrl.backorder_delivery_detail_id IS NULL
6434: NVL(msi.reservable_type, 1) AS reservable_type,
6435: NVL(msi.lot_control_code, 1) AS lot_control_code,
6436: NVL(msi.lot_divisible_flag, 'Y') AS lot_divisible_flag,
6437: wlpn.lpn_context AS lpn_context
6438: FROM mtl_txn_request_lines mtrl, mtl_system_items msi, wms_license_plate_numbers wlpn
6439: WHERE mtrl.line_id = p_move_order_line_id
6440: AND mtrl.organization_id = l_organization_id
6441: -- Modified the line below to use an IN instead of <>
6442: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
6481: p_wip_entity_id NUMBER,
6482: p_operation_seq_num NUMBER,
6483: p_repetitive_schedule_id NUMBER) IS
6484: SELECT mtrl.line_id
6485: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
6486: wms_license_plate_numbers wlpn
6487: WHERE mtrl.header_id = mtrh.header_id
6488: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
6489: AND mtrl.organization_id = l_organization_id
6488: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
6489: AND mtrl.organization_id = l_organization_id
6490: AND mtrl.inventory_item_id = l_inventory_item_id
6491: -- Modified the line below to use an IN instead of <> so the
6492: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
6493: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
6494: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
6495: inv_globals.g_to_status_approved)
6496: AND mtrl.backorder_delivery_detail_id IS NULL
6842: p_repetitive_schedule_id NUMBER,
6843: p_demand_source_header_id NUMBER,
6844: p_demand_source_line_id NUMBER) IS
6845: SELECT NVL(SUM(mtrl.primary_quantity), 0)
6846: FROM mtl_txn_request_lines mtrl, wsh_delivery_details wdd,
6847: wms_license_plate_numbers wlpn
6848: WHERE mtrl.organization_id = l_organization_id
6849: AND mtrl.inventory_item_id = l_inventory_item_id
6850: -- Modified the line below to use an IN instead of <> so the
6847: wms_license_plate_numbers wlpn
6848: WHERE mtrl.organization_id = l_organization_id
6849: AND mtrl.inventory_item_id = l_inventory_item_id
6850: -- Modified the line below to use an IN instead of <> so the
6851: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
6852: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
6853: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
6854: inv_globals.g_to_status_approved)
6855: AND NVL(mtrl.quantity_delivered, 0) = 0