[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_INTF_TBLS(
x_return_status => x_return_status,
p_request_group_id => l_request_group_id
);
DELETE_INTF_TBLS(
x_return_status => x_return_status,
p_request_group_id => l_request_group_id
);
/* description : Inserts records into open interface tables for receiving. */
/* Called from : CSD_RCV_PVT.RECEIVE_ITEM api */
/* Input Parm : p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
/* default value is fnd_api.g_false */
/* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
/* fnd_api.g_false */
/* p_validation_level NUMBER Optional API uses this parameter to determine which */
/* validation steps must be done and which steps */
/* should be skipped. */
/* p_receive_rec CSD_RECEIVE_UTIL.RCV_REC_TYPE Required */
/* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
/* fnd_api.g_ret_sts_success (success) */
/* fnd_api.g_ret_sts_error (error) */
/* fnd_api.g_ret_sts_unexp_error (unexpected) */
/* x_msg_count NUMBER Number of messages in the message stack */
/* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
/* x_request_group_id NUMBER Required */
/*-----------------------------------------------------------------------------------------------------------*/
PROCEDURE populate_rcv_intf_tbls (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_receive_tbl IN csd_receive_util.rcv_tbl_type,
x_request_group_id OUT NOCOPY NUMBER
)
IS
pragma AUTONOMOUS_TRANSACTION;
select org_id
from oe_order_lines_all
where line_id = p_order_line_id;
'Inserting header interface table data'
);
INSERT INTO rcv_headers_interface
(header_interface_id,
GROUP_ID,
ship_to_organization_id,
expected_receipt_date, last_update_date,
last_updated_by, last_update_login, creation_date,
created_by, validation_flag, processing_status_code,
receipt_source_code, transaction_type,
-- added for internal orders.
shipped_Date,
shipment_num
)
VALUES (rcv_headers_interface_s.NEXTVAL,
rcv_interface_groups_s.NEXTVAL,
p_receive_tbl (1).to_organization_id,
p_receive_tbl (1).expected_receipt_date, SYSDATE,
fnd_global.user_id, fnd_global.login_id, SYSDATE,
fnd_global.user_id, l_validation_flag, l_process_sts_pending,
l_receipt_source_code, l_txn_Type_new,
-- added for internal orders.
p_receive_tbl (1).shipped_date,
p_receive_tbl (1).shipment_number
)
RETURNING header_interface_id, GROUP_ID
INTO l_hdr_intf_id, x_request_group_id;
'UPDATE RCV_HEADERS_INTERFACE SET HEADER_INTERFACE_ID=HEADER_INTERFACE_ID';
'Inserting transactions interface table data'
);
INSERT INTO rcv_transactions_interface
(interface_transaction_id, header_interface_id,
GROUP_ID, transaction_date,
quantity, unit_of_measure,
oe_order_header_id,
document_num,
item_id,
item_revision,
to_organization_id,
ship_to_location_id,
subinventory, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, validation_flag,
source_document_code, interface_source_code,
auto_transact_code,
receipt_source_code,
transaction_type,
processing_status_code,
processing_mode_code,
transaction_status_code,
-- new columns to be updated,
category_id, uom_code,
employee_id,
primary_quantity,
primary_unit_of_measure,
routing_header_id, routing_step_id,
inspection_status_code,
destination_type_code, expected_receipt_date,
destination_context,
use_mtl_lot,
use_mtl_serial,
source_doc_quantity,
source_doc_unit_of_measure, oe_order_line_id,
--po_unit_price,
currency_code,
customer_id,
customer_site_id,
-- added for internal orders
requisition_line_id,
shipped_date,
shipment_num,
from_organization_id,
--location_id,
locator_id, --bug#7509332, 12.1 FP, subhat
deliver_to_location_id,
shipment_header_id,
shipment_line_id,
org_id -- MOAC change Bug#4245577
)
VALUES (rcv_transactions_interface_s.NEXTVAL, l_hdr_intf_id,
x_request_group_id, p_receive_rec.transaction_date,
p_receive_rec.quantity, p_receive_rec.unit_of_measure,
p_receive_rec.order_header_id,
p_receive_rec.doc_number,
p_receive_rec.inventory_item_id,
p_receive_rec.item_revision,
p_receive_rec.to_organization_id,
p_receive_rec.ship_to_location_id,
p_receive_rec.subinventory, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
fnd_global.login_id, 'Y',
l_source_document_code
, 'RCV' --Interface_source_Code
, 'DELIVER' -- auto _Transact_Code
, l_receipt_source_Code --receipt_source_code
, 'RECEIVE' --Transaction_type
, 'PENDING' -- processing_status_Code
, 'ONLINE' --processing_mode _Code
, 'PENDING' --transaction_status_Code
, p_receive_rec.category_id
, p_receive_rec.uom_code
, p_receive_rec.employee_id
, p_receive_rec.quantity -- Primary quantity
, p_receive_rec.primary_unit_of_measure -- primary unit of measure.
, 1------------temp--------- p_receive_rec.routing_header_id
, 1
,'NOT INSPECTED' -- inspection status code
,'INVENTORY' -- destination_type code
, SYSDATE,
'INVENTORY' -- destination_context
,
p_receive_rec.lot_control_code,
p_receive_rec.serial_control_code,
p_receive_rec.quantity -- Source doc quantity
,
p_receive_rec.unit_of_measure, -- source doc unit_of measure
p_receive_rec.order_line_id,
p_receive_rec.currency_code,
p_receive_rec.customer_id,
p_receive_rec.customer_site_id,
-- added for internal orders
p_receive_rec.requisition_line_id,
p_Receive_rec.shipped_date,
p_Receive_rec.shipment_number,
p_Receive_rec.from_organization_id,
p_Receive_rec.locator_id,
p_Receive_rec.deliver_to_location_id,
p_Receive_rec.shipment_header_id,
p_Receive_rec.shipment_line_id,
l_org_id -- MOAC change Bug#4245577
)
RETURNING interface_transaction_id
INTO l_intf_txn_id;
' UPDATE RCV_TRANSACTIONS_INTERFACE SET OE_ORDER_NUM ='
|| p_receive_rec.order_number
|| ' WHERE INTERFACE_TRANSACTION_ID='
|| l_intf_txn_id;
' UPDATE RCV_TRANSACTIONS_INTERFACE SET OE_ORDER_NUM = :1'
|| ' WHERE INTERFACE_TRANSACTION_ID=:2' ;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_tmp_id
FROM DUAL;
'Inserting lot interface table data for ['
|| l_intf_txn_id
|| ']lot number['
|| p_receive_rec.lot_number
|| ']'
);
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, source_code,
source_line_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
lot_number, lot_expiration_date,
transaction_quantity, primary_quantity,
serial_transaction_temp_id
)
VALUES (l_intf_txn_id, l_source_code,
l_source_line_id, SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id, fnd_global.login_id,
p_receive_rec.lot_number, l_lot_expiration_date,
p_receive_rec.quantity, p_receive_rec.quantity,
l_txn_tmp_id
);
'UPDATE mtl_transaction_lots_interface SET product_code=''RCV'' ';
'Inserting serial interface table data for ['
|| l_intf_txn_id
|| ']serial number['
|| p_receive_rec.serial_number
|| ']'
);
INSERT INTO mtl_serial_numbers_interface
(transaction_interface_id, source_code,
source_line_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
fm_serial_number,
to_serial_number,
process_flag
)
VALUES (l_txn_tmp_id, l_source_code,
l_source_line_id, SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id, fnd_global.login_id,
p_receive_rec.serial_number,
p_receive_rec.serial_number,
l_process_flag
);
'UPDATE mtl_serial_numbers_interface SET product_code=''RCV'' ';
/* procedure name: DELETE_INTF_TBLS */
/* description : Deletes records in RCV_HEADERS_INTERFACE, RCV_TRANSACTIONS_INTERFACE., PO_INTERFACE_ERRORS*/
/* MTL_TRANSACTION_LOTS_INTERFACE_TBL, MTL_SERIAL_NUMBERS_INTERFACE_TBL tables. */
/* Called from : receive_item api */
/* Input Parm : */
/* p_request_group_id NUMBER Required */
/* p_interface_transaction_Id NUMBER Required */
/* p_interface_header_Id NUMBER Required */
/* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
/* fnd_api.g_ret_sts_success (success) */
/* fnd_api.g_ret_sts_error (error) */
/* fnd_api.g_ret_sts_unexp_error (unexpected) */
/*-----------------------------------------------------------------------------------------------------------*/
PROCEDURE delete_intf_tbls (
x_return_status OUT NOCOPY VARCHAR2,
p_request_group_id IN NUMBER
)
IS
pragma AUTONOMOUS_TRANSACTION;
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Intf_Tbls';
SELECT HEADER_INTERFACE_ID
FROM RCV_HEADERS_INTERFACE
WHERE GROUP_ID = p_group_id;
SELECT INTERFACE_TRANSACTION_ID
FROM RCV_TRANSACTIONS_INTERFACE
WHERE GROUP_ID = p_group_id;
'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS.BEGIN',
'Entered Delete_Intf_Tbls'
);
DELETE FROM po_interface_errors err
WHERE err.interface_header_id = l_hdr_Rec.header_interface_id;
DELETE FROM po_interface_errors err
WHERE err.interface_transaction_id = l_txn_rec.interface_transaction_id;
DELETE FROM mtl_transaction_lots_interface
WHERE TRANSACTION_INTERFACE_ID = l_txn_rec.interface_transaction_Id
RETURNING SERIAL_TRANSACTION_TEMP_ID into l_txn_temp_id;
DELETE FROM mtl_serial_numbers_interface
WHERE (TRANSACTION_INTERFACE_ID = l_txn_rec.interface_transaction_Id
OR TRANSACTION_INTERFACE_ID = l_txn_temp_id);
'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
'Deleting from the headers table'
);
DELETE FROM rcv_headers_interface
WHERE GROUP_ID = p_request_group_id;
'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
'Deleting from the detail table'
);
DELETE FROM rcv_transactions_interface
WHERE GROUP_ID = p_request_group_id;
'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS.END',
'Leaving DELETE_INTF_TBLS'
);
'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
'EXC_UNEXPECTED_ERROR in delete_intf_tbls'
);
'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
'SQL Message in delete_intf_tbls[' || SQLERRM || ']'
);
END delete_intf_tbls;
SELECT COUNT (1)
INTO l_rec_count
FROM rcv_transactions_interface
WHERE GROUP_ID = p_group_id;