The following lines contain the word 'select', 'insert', 'update' or 'delete':
OE_MSG_PUB.update_msg_context(
p_header_id => l_line_rec.header_id
,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
,p_change_sequence => l_line_rec.change_sequence
,p_source_document_id => l_line_rec.source_document_id
,p_source_document_line_id => l_line_rec.source_document_line_id
,p_order_source_id => l_line_rec.order_source_id
,p_source_document_type_id => l_line_rec.source_document_type_id);
SELECT MTL_TRANSACTIONS_ENABLED_FLAG
INTO l_transactable_flag
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
SELECT /* MOAC_SQL_CHANGE */ h.order_number,ot.name,h.ordered_date
INTO l_order_number,l_order_type_name,l_ordered_date
FROM oe_order_headers_all h, oe_order_types_v ot
WHERE h.header_id = l_line_rec.header_id AND
ot.order_type_id = h.order_type_id;
SELECT DECODE(OE_SYS_PARAMETERS.value('TRX_DATE_FOR_INV_IFACE'),
'C',SYSDATE,
'S',nvl(l_line_rec.schedule_ship_date,SYSDATE),
l_ordered_date)
INTO l_trx_date_for_inv_iface
FROM DUAL;
SELECT revision_qty_control_code, lot_control_code, serial_number_control_code
INTO l_revision_code, l_lot_code, l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
oe_debug_pub.add( 'INV IFACE: REVISION/LOT SELECT FAILURE' , 5 ) ;
SELECT mtl_material_transactions_s.nextval
INTO l_transaction_header_id
FROM dual;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items_b
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
OE_DEBUG_PUB.Add('Error in selecting Primary UOM code',1);
GMI_Reservation_Util.update_opm_trxns(
l_rsv_tbl(I).reservation_id
,l_line_rec.inventory_item_id
,l_line_rec.ship_from_org_id
,l_return_status
,opm_msg_count
,opm_msg_data);
oe_debug_pub.add( 'INV IFACE: FAILED TO UPDATE OPM TRXNS' , 5 ) ;
SELECT stock_locator_control_code
INTO l_stock_locator_control_code
FROM mtl_parameters
WHERE organization_id = l_line_rec.ship_from_org_id ;
SELECT locator_type
INTO l_locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_rsv_tbl(I).subinventory_code
AND organization_id = l_line_rec.ship_from_org_id;
SELECT location_control_code
INTO l_location_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
SELECT oe_transactions_iface_s.nextval
INTO l_source_line_id
FROM dual;
SELECT mtl_material_transactions_s.nextval
INTO l_serial_transaction_temp_id
FROM dual;
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SERIAL_TRANSACTION_TEMP_ID,
ERROR_CODE,
PROCESS_FLAG )
VALUES
(
FND_PROFILE.VALUE('ONT_SOURCE_CODE'),
l_source_line_id,
l_transaction_interface_id,
l_rsv_tbl(I).lot_number,
(-1 * l_rsv_tbl(I).reservation_quantity),
(-1 * l_rsv_tbl(I).secondary_reservation_quantity), -- INVCONV
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
l_serial_transaction_temp_id, --ER 8419373
null,
'Y');
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
(
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER ,
--TO_SERIAL_NUMBER ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
ERROR_CODE,
PROCESS_FLAG )
VALUES
(
profile_values.oe_source_code,
l_source_line_id,
nvl(l_serial_transaction_temp_id,l_transaction_interface_id),
l_serial_number_tbl(J).serial_number,
--l_serial_number_tbl(J).serial_number,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
null,
1);
oe_debug_pub.add( 'INV IFACE: INSERTING RECORD - 1' , 1 ) ;
oe_debug_pub.add( 'LAST_UPDATE_DATE :' || SYSDATE , 5 ) ;
oe_debug_pub.add( 'LAST_UPDATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
LOCK_FLAG,
TRANSACTION_HEADER_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
TRANSACTION_DATE,
ORGANIZATION_ID,
ACCT_PERIOD_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSACTION_SOURCE_ID,
DSP_SEGMENT1,
DSP_SEGMENT2,
DSP_SEGMENT3,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
DISTRIBUTION_ACCOUNT_ID,
DST_SEGMENT1,
DST_SEGMENT2,
DST_SEGMENT3,
DST_SEGMENT4,
DST_SEGMENT5,
DST_SEGMENT6,
DST_SEGMENT7,
DST_SEGMENT8,
DST_SEGMENT9,
DST_SEGMENT10,
DST_SEGMENT11,
DST_SEGMENT12,
DST_SEGMENT13,
DST_SEGMENT14,
DST_SEGMENT15,
DST_SEGMENT16,
DST_SEGMENT17,
DST_SEGMENT18,
DST_SEGMENT19,
DST_SEGMENT20,
DST_SEGMENT21,
DST_SEGMENT22,
DST_SEGMENT23,
DST_SEGMENT24,
DST_SEGMENT25,
DST_SEGMENT26,
DST_SEGMENT27,
DST_SEGMENT28,
DST_SEGMENT29,
DST_SEGMENT30,
TRANSACTION_REFERENCE,
TRX_SOURCE_LINE_ID,
TRX_SOURCE_DELIVERY_ID,
REVISION,
LOCATOR_ID,
LOC_SEGMENT1,
LOC_SEGMENT2,
LOC_SEGMENT3,
LOC_SEGMENT4,
REQUIRED_FLAG,
PICKING_LINE_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
SHIP_TO_LOCATION_ID,
REQUISITION_LINE_ID,
TRANSACTION_UOM,
TRANSACTION_INTERFACE_ID,
DEMAND_ID,
SHIPMENT_NUMBER,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
ENCUMBRANCE_ACCOUNT,
ENCUMBRANCE_AMOUNT,
--CONTENT_LPN_ID, -- added for bug 6313351
LPN_ID, --added for bug 8658984
PROJECT_ID,
TASK_ID)
SELECT
profile_values.oe_source_code,
l_source_line_id,
l_transaction_reference,
1, /* PROCESS_FLAG */
3, /* TRANSACTION_MODE */
2, /* LOCK_FLAG */
l_transaction_header_id,
l_line_rec.inventory_item_id,
l_rsv_tbl(I).subinventory_code,
(-1 * l_rsv_tbl(I).reservation_quantity),
(-1 * l_rsv_tbl(I).secondary_reservation_quantity), -- INVCONV
l_trx_date_for_inv_iface/*l_ordered_date*/, --bug5897965 l_ordered_date commented
l_rsv_tbl(I).organization_id,
null,
sysdate,
profile_values.user_id,
sysdate,
profile_values.user_id,
l_sales_order_id, /* transaction_source_id */
l_order_number,
l_order_type_name,
profile_values.oe_source_code,
2,
1,
33,
l_trans_acc,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
l_transaction_reference,
l_line_rec.line_id,
null,
l_rsv_tbl(I).revision,
l_rsv_tbl(I).locator_id,
null,
null,
null,
null,
null,
null, /* l_shipment_line_id */
null, /* l_dest_subinv */
null, /* l_to_org_id */
null, /* l_location_id */
null, /* l_req_line_id */
L_RSV_TBL(I).reservation_uom_code , --l_line_rec.order_quantity_uom, --9743423
l_transaction_interface_id, /* interface_id */
null,
null,
null,
null,
null,
null,
null, /* l_budget_acct_id */
null, /* l_unit_price * p_transaction_detail_qty */
l_rsv_tbl(I).lpn_id, -- added for bug 6313351
l_line_rec.project_id,
l_line_rec.task_id
FROM gl_code_combinations
WHERE code_combination_id = l_trans_acc;
SELECT mtl_material_transactions_s.nextval
INTO l_transaction_interface_id
FROM dual;
oe_debug_pub.add( 'INV IFACE: SUBINV IS NULL OR MISS_CHAR FOR INSERT - 2' , 5 ) ;
SELECT stock_locator_control_code
INTO l_stock_locator_control_code
FROM mtl_parameters
WHERE organization_id = l_line_rec.ship_from_org_id;
oe_debug_pub.add( 'INV IFACE: LOCATOR CONTROL CODE SELECT FAILURE' , 5 ) ;
SELECT locator_type
INTO l_locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_line_rec.subinventory
AND organization_id = l_line_rec.ship_from_org_id;
oe_debug_pub.add( 'INV IFACE: LOCATOR TYPE SELECT FAILURE' , 5 ) ;
SELECT location_control_code
INTO l_location_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
SELECT oe_transactions_iface_s.nextval
INTO l_source_line_id
FROM dual;
oe_debug_pub.add( 'INV IFACE: INSERTING RECORD - 2' , 5 ) ;
oe_debug_pub.add( 'LAST_UPDATE_DATE :' || SYSDATE , 5 ) ;
oe_debug_pub.add( 'LAST_UPDATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
LOCK_FLAG,
TRANSACTION_HEADER_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
TRANSACTION_DATE,
ORGANIZATION_ID,
ACCT_PERIOD_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSACTION_SOURCE_ID,
DSP_SEGMENT1,
DSP_SEGMENT2,
DSP_SEGMENT3,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
DISTRIBUTION_ACCOUNT_ID,
DST_SEGMENT1,
DST_SEGMENT2,
DST_SEGMENT3,
DST_SEGMENT4,
DST_SEGMENT5,
DST_SEGMENT6,
DST_SEGMENT7,
DST_SEGMENT8,
DST_SEGMENT9,
DST_SEGMENT10,
DST_SEGMENT11,
DST_SEGMENT12,
DST_SEGMENT13,
DST_SEGMENT14,
DST_SEGMENT15,
DST_SEGMENT16,
DST_SEGMENT17,
DST_SEGMENT18,
DST_SEGMENT19,
DST_SEGMENT20,
DST_SEGMENT21,
DST_SEGMENT22,
DST_SEGMENT23,
DST_SEGMENT24,
DST_SEGMENT25,
DST_SEGMENT26,
DST_SEGMENT27,
DST_SEGMENT28,
DST_SEGMENT29,
DST_SEGMENT30,
TRANSACTION_REFERENCE,
TRX_SOURCE_LINE_ID,
TRX_SOURCE_DELIVERY_ID,
REVISION,
LOCATOR_ID,
LOC_SEGMENT1,
LOC_SEGMENT2,
LOC_SEGMENT3,
LOC_SEGMENT4,
REQUIRED_FLAG,
PICKING_LINE_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
SHIP_TO_LOCATION_ID,
REQUISITION_LINE_ID,
TRANSACTION_UOM,
TRANSACTION_INTERFACE_ID,
DEMAND_ID,
SHIPMENT_NUMBER,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
ENCUMBRANCE_ACCOUNT,
ENCUMBRANCE_AMOUNT,
PROJECT_ID,
TASK_ID)
SELECT
profile_values.oe_source_code,
l_source_line_id,
l_transaction_reference,
1, /* PROCESS_FLAG */
3, /* TRANSACTION_MODE */
2, /* LOCK_FLAG */
l_transaction_header_id,
l_line_rec.inventory_item_id,
l_line_rec.subinventory,
(-1 * decode(reservation_flag, 'Y', l_remained_qty, l_line_rec.ordered_quantity)),
(-1 * decode(reservation_flag, 'Y', l_remained_qty2, l_line_rec.ordered_quantity2)), -- INVCONV
l_trx_date_for_inv_iface/*l_ordered_date*/, --bug5897965 l_ordered_date commented
l_line_rec.ship_from_org_id,
null,
sysdate,
profile_values.user_id,
sysdate,
profile_values.user_id,
l_sales_order_id, /* transaction_source_id */
l_order_number,
l_order_type_name,
profile_values.oe_source_code,
2, /* l_trx_source_type_id */
1, /* l_trx_action_id */
33, /* l_trx_type_code */
l_trans_acc,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
l_transaction_reference,
l_line_rec.line_id,
null,
null, /* revision */
null, /* locator */
null,
null,
null,
null,
null,
null, /* l_shipment_line_id */
null, /* l_dest_subinv */
null, /* l_to_org_id */
null, /* l_location_id */
null, /* l_req_line_id */
l_line_rec.order_quantity_uom,
l_transaction_interface_id,
null,
null,
null,
null,
null,
null,
null, /* l_budget_acct_id */
null, /* l_unit_price * p_transaction_detail_qty */
l_line_rec.project_id,
l_line_rec.task_id
FROM gl_code_combinations
WHERE code_combination_id = l_trans_acc;
oe_debug_pub.add( 'INV IFACE: FINISH INSERTING - 2 , CALLING UPDATE_FLOW_STATUS_CODE' , 1 ) ;
/* update flow_status_code */
OE_ORDER_WF_UTIL.Update_Flow_Status_Code(p_line_id => p_line_id
, p_flow_status_code => 'INVENTORY_INTERFACED'
, x_return_status => l_return_status);
oe_debug_pub.add( 'INV IFACE: UPDATE FLOW STATUS CODE FAILED' , 5 ) ;
/* bug 4659103: update of visible_demand_flag code removed */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'INV IFACE: EXITING INVENTORY_INTERFACE' , 1 ) ;