[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sh.organization_id,
-- Bug #7661019
sh.ship_num||'.'||TO_CHAR(slg.ship_line_group_num) ship_num,
sh.ship_header_id,
ship_date,
slg.ship_line_group_id,
slg.party_id,
slg.party_site_id,
sh.org_id,
slg.src_type_code
FROM inl_ship_headers sh,
inl_ship_line_groups slg
WHERE sh.ship_header_id = slg.ship_header_id
AND sh.ship_header_id = l_ship_header_id
AND slg.ship_line_group_id = l_ship_line_group_id
AND slg.src_type_code = 'PO';
asn_debug.put_line('Entering RCV_INSERT_FROM_INL.insert_rcv_tables' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS'));
update RCV_TRANSACTIONS_INTERFACE RTI
set PROCESSING_STATUS_CODE = 'PENDING'
where RTI.header_interface_id = ( select rhi.header_interface_id
from rcv_headers_interface rhi
where rhi.header_interface_id = rti.header_interface_id
and rhi.group_id = rti.group_id
and rhi.receipt_header_id = p_int_rec(i).shipment_header_id)
and RTI.PROCESSING_STATUS_CODE = 'INSERTING';
asn_debug.put_line ('no of rows updated: '|| SQL%ROWCOUNT);
update rcv_headers_interface
set receipt_header_id = NULL
where receipt_header_id = p_int_rec(i).shipment_header_id;
SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO l_header_interface_id
FROM dual;
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO l_group_id
FROM dual;
SELECT apv.vendor_id,
aps.vendor_site_id
INTO l_vendor_id,
l_vendor_site_id
FROM ap_supplier_sites aps,
ap_suppliers apv
WHERE aps.party_site_id = l_rcv_header.party_site_id
AND apv.party_id = l_rcv_header.party_id
AND aps.org_id = l_rcv_header.org_id;
INSERT INTO RCV_HEADERS_INTERFACE
(header_interface_id,
group_id,
processing_status_code,
receipt_source_code,
transaction_type,
ship_to_organization_id,
notice_creation_date,
vendor_id,
vendor_site_id,
validation_flag,
shipped_date,
shipment_num,
asn_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES
(l_header_interface_id,
l_group_id,
l_processing_status_code,
l_receipt_source_code,
l_transaction_type,
l_rcv_header.organization_id,
l_notice_creation_date,
l_vendor_id,
l_vendor_site_id,
l_validation_flag,
l_rcv_header.ship_date,
l_rcv_header.ship_num,
'LCM',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
asn_debug.put_line ('inserted header');
SELECT pll.po_header_id, pll.po_line_id, pll.po_release_id
INTO l_po_header_id, l_po_line_id, l_po_release_id
FROM po_line_locations pll,
po_headers ph
WHERE ph.po_header_id = pll.po_header_id
AND pll.line_location_id = p_int_rec(i).ship_line_source_id;
SELECT UNIT_OF_MEASURE
INTO l_primary_uom
FROM mtl_units_of_measure_vl
WHERE uom_code = p_int_rec(i).primary_uom_code;
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
INTO l_interface_transaction_id
FROM dual;
INSERT INTO rcv_transactions_interface
(interface_transaction_id, -- 01
group_id, -- 02
lpn_group_id, -- 03
transaction_type, -- 04
transaction_date, -- 05
processing_status_code, -- 06
processing_mode_code, -- 07
transaction_status_code, -- 08
quantity, -- 09
uom_code, -- 10
ship_to_location_id, -- 11
vendor_item_num, -- 12
interface_source_code, -- 13
interface_source_line_id, -- 14
item_id, -- 15
item_num, -- 16
item_description, -- 17
receipt_source_code, -- 18
vendor_id, -- 19
vendor_site_id, -- 20
source_document_code, -- 21
po_header_id, -- 22
po_line_id, -- 23
po_release_id, -- 24
po_line_location_id, -- 25
header_interface_id, -- 26
validation_flag, -- 27
org_id, -- 28
to_organization_id, -- 29
location_id, -- 30
deliver_to_location_id, -- 31
last_update_date, -- 32
last_updated_by, -- 33
creation_date, -- 34
created_by, -- 35
last_update_login, -- 36
lcm_shipment_line_id, -- 37
unit_landed_cost, -- 38
auto_transact_code, -- 39
primary_quantity, -- 40 /* Bug 8210608 */
primary_unit_of_measure) -- 41 /* Bug 8210608 */
VALUES(
l_interface_transaction_id, -- 01
l_group_id, -- 02
l_group_id, -- 03
l_transaction_type, -- 04
sysdate, -- 05
l_processing_status_code, -- 06
l_processing_mode_code, -- 07
l_transaction_status_code, -- 08
p_int_rec(i).txn_qty, -- 09
p_int_rec(i).txn_uom_code, -- 10
p_int_rec(i).location_id, -- 11
NULL, -- 12
l_interface_source_code, -- 13
p_int_rec(i).ship_line_id, -- 14
p_int_rec(i).inventory_item_id, -- 15
NULL, -- 16
p_int_rec(i).item_description, -- 17
l_receipt_source_code, -- 18
l_vendor_id, -- 19
l_vendor_site_id, -- 20
p_int_rec(i).src_type_code, -- 21
l_po_header_id, -- 22
l_po_line_id, -- 23
l_po_release_id, -- 24
p_int_rec(i).ship_line_source_id, -- 25
l_header_interface_id, -- 26
l_validation_flag, -- 27
p_int_rec(i).org_id, -- 28
p_int_rec(i).organization_id, -- 29
p_int_rec(i).location_id, -- 30
p_int_rec(i).location_id, -- 31
sysdate, -- 32
fnd_global.user_id, -- 33
sysdate, -- 34
fnd_global.user_id, -- 35
fnd_global.login_id, -- 36
p_int_rec(i).ship_line_id, -- 37
p_int_rec(i).unit_landed_cost, -- 38
'SHIP', -- 39
p_int_rec(i).primary_qty, -- 40 /* Bug 8210608 */
l_primary_uom); -- 41 /* Bug 8210608 */
asn_debug.put_line('inserted line');
END insert_rcv_tables;
END RCV_INSERT_FROM_INL;