[Home] [Help]
1130: -- with a subquery which is how we are inserting the split MOL record.
1131: -- The alternative is to store each of the columns in a variable but that
1132: -- is not ideal.
1133: BEGIN
1134: INSERT INTO mtl_txn_request_lines
1135: (LINE_ID
1136: ,HEADER_ID
1137: ,LINE_NUMBER
1138: ,ORGANIZATION_ID
1228: ,OPERATION_SEQ_NUM
1229: ,WIP_SUPPLY_TYPE
1230: )
1231: (SELECT
1232: mtl_txn_request_lines_s.NEXTVAL -- LINE_ID
1233: ,HEADER_ID
1234: ,mtrl_max.line_num --LINE_NUMBER
1235: ,ORGANIZATION_ID
1236: ,INVENTORY_ITEM_ID
1327: ,l_wip_entity_id --WIP_ENTITY_ID
1328: ,l_repetitive_schedule_id --REPETITIVE_SCHEDULE_ID
1329: ,l_operation_seq_num --OPERATION_SEQ_NUM
1330: ,l_wip_supply_type --WIP_SUPPLY_TYPE
1331: FROM mtl_txn_request_lines mtrl, (SELECT MAX(line_number) + 1 AS line_num
1332: FROM mtl_txn_request_lines
1333: WHERE header_id = l_mol_header_id) mtrl_max
1334: WHERE mtrl.line_id = l_mol_line_id);
1335: EXCEPTION
1328: ,l_repetitive_schedule_id --REPETITIVE_SCHEDULE_ID
1329: ,l_operation_seq_num --OPERATION_SEQ_NUM
1330: ,l_wip_supply_type --WIP_SUPPLY_TYPE
1331: FROM mtl_txn_request_lines mtrl, (SELECT MAX(line_number) + 1 AS line_num
1332: FROM mtl_txn_request_lines
1333: WHERE header_id = l_mol_header_id) mtrl_max
1334: WHERE mtrl.line_id = l_mol_line_id);
1335: EXCEPTION
1336: WHEN OTHERS THEN
1340: -- Raise an exception. The caller will do the rollback, cleanups,
1341: -- and decide where to goto next.
1342: x_error_code := 'DB';
1343: RAISE FND_API.G_EXC_ERROR;
1344: END; -- End inserting split MOL record into MTL_TXN_REQUEST_LINES
1345:
1346: -- Retrieve the split MOL line ID we have just inserted above.
1347: -- We cannot use the RETURNING clause since a sub-query was used for the insert.
1348: -- As of 10g, this is not a supported feature.
1348: -- As of 10g, this is not a supported feature.
1349: BEGIN
1350: SELECT line_id
1351: INTO l_split_mol_line_id
1352: FROM mtl_txn_request_lines
1353: WHERE header_id = l_mol_header_id
1354: AND ROWNUM = 1
1355: ORDER BY line_number DESC;
1356: EXCEPTION
1369: END IF;
1370:
1371: -- Update the quantity on the original MOL record.
1372: BEGIN
1373: UPDATE mtl_txn_request_lines SET
1374: quantity = l_mol_qty - l_atd_qty,
1375: primary_quantity = l_mol_prim_qty - l_atd_prim_qty,
1376: secondary_quantity = l_mol_qty2 - l_atd_mol_qty2
1377: WHERE line_id = l_mol_line_id;
1412: -- {{
1413: -- Test for MOL qty = available to detail qty. MOL record should be
1414: -- crossdocked properly. }}11
1415: BEGIN
1416: UPDATE mtl_txn_request_lines SET
1417: backorder_delivery_detail_id = l_backorder_detail_id,
1418: crossdock_type = l_crossdock_type,
1419: wip_entity_id = l_wip_entity_id,
1420: repetitive_schedule_id = l_repetitive_schedule_id,
2005: mtrl.secondary_uom_code AS secondary_uom_code,
2006: mtrl.project_id AS project_id,
2007: mtrl.task_id AS task_id,
2008: mtrl.lpn_id AS lpn_id
2009: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
2010: wms_license_plate_numbers wlpn
2011: WHERE mtrl.header_id = mtrh.header_id
2012: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
2013: AND mtrl.organization_id = l_organization_id
2012: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
2013: AND mtrl.organization_id = l_organization_id
2014: AND mtrl.inventory_item_id = l_inventory_item_id
2015: -- Modified the line below to use an IN instead of <> so the
2016: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
2017: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
2018: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
2019: inv_globals.g_to_status_approved)
2020: AND mtrl.backorder_delivery_detail_id IS NULL
2319: mtrl.secondary_uom_code AS secondary_uom_code,
2320: mtrl.project_id AS project_id,
2321: mtrl.task_id AS task_id,
2322: mtrl.lpn_id AS lpn_id
2323: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
2324: wms_license_plate_numbers wlpn
2325: WHERE mtrl.header_id = mtrh.header_id
2326: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
2327: AND mtrl.organization_id = l_organization_id
2326: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
2327: AND mtrl.organization_id = l_organization_id
2328: AND mtrl.inventory_item_id = l_inventory_item_id
2329: -- Modified the line below to use an IN instead of <> so the
2330: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
2331: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
2332: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
2333: inv_globals.g_to_status_approved)
2334: AND mtrl.backorder_delivery_detail_id IS NULL
6609: NVL(msi.reservable_type, 1) AS reservable_type,
6610: NVL(msi.lot_control_code, 1) AS lot_control_code,
6611: NVL(msi.lot_divisible_flag, 'Y') AS lot_divisible_flag,
6612: wlpn.lpn_context AS lpn_context
6613: FROM mtl_txn_request_lines mtrl, mtl_system_items msi, wms_license_plate_numbers wlpn
6614: WHERE mtrl.line_id = p_move_order_line_id
6615: AND mtrl.organization_id = l_organization_id
6616: -- Modified the line below to use an IN instead of <>
6617: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
6656: p_wip_entity_id NUMBER,
6657: p_operation_seq_num NUMBER,
6658: p_repetitive_schedule_id NUMBER) IS
6659: SELECT mtrl.line_id
6660: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh,
6661: wms_license_plate_numbers wlpn
6662: WHERE mtrl.header_id = mtrh.header_id
6663: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
6664: AND mtrl.organization_id = l_organization_id
6663: AND mtrh.move_order_type = inv_globals.g_move_order_put_away
6664: AND mtrl.organization_id = l_organization_id
6665: AND mtrl.inventory_item_id = l_inventory_item_id
6666: -- Modified the line below to use an IN instead of <> so the
6667: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
6668: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
6669: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
6670: inv_globals.g_to_status_approved)
6671: AND mtrl.backorder_delivery_detail_id IS NULL
7017: p_repetitive_schedule_id NUMBER,
7018: p_demand_source_header_id NUMBER,
7019: p_demand_source_line_id NUMBER) IS
7020: SELECT NVL(SUM(mtrl.primary_quantity), 0)
7021: FROM mtl_txn_request_lines mtrl, wsh_delivery_details wdd,
7022: wms_license_plate_numbers wlpn
7023: WHERE mtrl.organization_id = l_organization_id
7024: AND mtrl.inventory_item_id = l_inventory_item_id
7025: -- Modified the line below to use an IN instead of <> so the
7022: wms_license_plate_numbers wlpn
7023: WHERE mtrl.organization_id = l_organization_id
7024: AND mtrl.inventory_item_id = l_inventory_item_id
7025: -- Modified the line below to use an IN instead of <> so the
7026: -- index MTL_TXN_REQUEST_LINES_N10 on MTRL is more likely to be used.
7027: -- AND mtrl.line_status <> inv_globals.g_to_status_closed
7028: AND mtrl.line_status IN (inv_globals.g_to_status_preapproved,
7029: inv_globals.g_to_status_approved)
7030: AND NVL(mtrl.quantity_delivered, 0) = 0