[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
INTO g_accounting_info(p_org_id)
FROM hr_organization_information hoi
, gl_sets_of_books gsob
, financials_system_params_all fsp
WHERE hoi.organization_id = p_org_id
AND hoi.org_information_context||'' = 'Accounting Information'
AND (fsp.org_id IS NULL OR hoi.org_information3 = TO_CHAR(fsp.org_id))
AND fsp.set_of_books_id = gsob.set_of_books_id;
SELECT oap.open_flag
INTO l_open_flag
FROM org_acct_periods oap
WHERE oap.organization_id = p_org_id
AND (trunc(p_trx_date)
BETWEEN trunc(oap.period_start_date) AND
trunc (oap.schedule_close_date));
SELECT fob_point
FROM mtl_interorg_parameters
WHERE from_organization_id = l_from_org_id
AND to_organization_id = l_to_org_id;
SELECT rcv_transactions_s.NEXTVAL
INTO l_rt_row.transaction_id
FROM DUAL;
have the value that is updated by Inventory.There may be other fields that
would have got updated.So fetching all the values directly from rti and then
use them to populate rcv_transactions table.
*/
-- l_rti_row := RCV_TABLE_FUNCTIONS.get_rti_row_from_id( p_rti_id );
select *
into l_rti_row
from rcv_transactions_interface
where interface_transaction_id = p_rti_id;
select nvl(authorization_status,'N') auth_status, nvl(approved_flag,'N') app_flag
into l_po_auth_status, l_po_approved_flag
from po_headers_all ph
where ph.po_header_id = l_rti_row.po_header_id;
select nvl(approved_flag,'N') app_flag, nvl(price_override, -1.0) new_price
into l_pll_approved_flag, l_po_unit_price
from po_line_locations_all pll
where pll.line_location_id = l_rti_row.po_line_location_id;
update rcv_transactions_interface
set po_unit_price = l_po_unit_price
where interface_transaction_id = l_rti_row.interface_transaction_id;
Changing the Created_by and Last_Updated_by from user_id to whats in RTI record.
Commented old lines below and add new ones.
*/
l_rt_row.creation_date := SYSDATE;
l_rt_row.last_update_date := SYSDATE;
--l_rt_row.last_updated_by := user_id;
l_rt_row.last_updated_by := l_rti_row.last_updated_by;
l_rt_row.last_update_login := l_rti_row.last_update_login;
l_rt_row.program_update_date := SYSDATE;
select archive_external_revision_code
into l_archive_ext_rev_code
from po_document_types
where document_type_code = 'PO'
and document_subtype = (select type_lookup_code from po_headers
where po_header_id = l_rti_row.po_header_id);
select price_override
into l_rt_row.po_unit_price
from po_line_locations_archive
where line_location_id = l_rti_row.po_line_location_id
and nvl(latest_external_flag,'N') = 'Y';
select gsob.currency_code into
l_rt_row.currency_code
from hr_organization_information hoi,
financials_system_params_all fsp,
gl_sets_of_books gsob
where hoi.organization_id = l_rti_row.to_organization_id
and hoi.org_information_context||'' = 'Accounting Information'
and (fsp.org_id is null OR hoi.org_information3 = to_char(fsp.org_id))
and hoi.org_information1 = to_char(fsp.set_of_books_id)
and fsp.set_of_books_id = gsob.set_of_books_id;
SELECT country_of_origin_code
INTO l_rt_row.country_of_origin_code
FROM rcv_transactions
WHERE transaction_id = l_rt_row.parent_transaction_id;
asn_debug.put_line('Inserting RT row (' || l_rt_row.transaction_type || ')...');
/* GSCC errors come up when we use the foll. insert.
* Changing to use the full insert stmts.
INSERT INTO RCV_TRANSACTIONS
VALUES l_rt_row;
* Added exception handler to catch the exception when insertion into rcv_transactions
* fails due to exception raised in the triggers(for eg; India Localisation triggers) on
* rcv_transactions table. Similarly added exception handler for insertion into
* po_note_references table. To this rvthinns() function as whole, added one exception handler.
* While storing sqlerrm in the x_message_data getting only the first 200 bytes.
* without that unhandled exception is raised while copying the sqlerrm. And moreover
* in rvtth.lpc rvthinns(), x_msg_data is defined to store only 200 bytes.
*/
BEGIN --Bug: 6487371
INSERT INTO rcv_transactions
(transaction_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
interface_source_code,
interface_source_line_id,
user_entered_flag,
transaction_type,
transaction_date,
source_document_code,
destination_type_code,
location_id,
quantity,
unit_of_measure,
uom_code,
primary_quantity,
primary_unit_of_measure,
source_doc_quantity,
source_doc_unit_of_measure,
shipment_header_id,
shipment_line_id,
parent_transaction_id,
employee_id,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_revision_num,
requisition_line_id,
req_distribution_id,
po_unit_price,
currency_code,
currency_conversion_rate,
currency_conversion_date,
currency_conversion_type,
routing_header_id,
routing_step_id,
substitute_unordered_code,
receipt_exception_flag,
inspection_status_code,
inspection_quality_code,
vendor_id,
vendor_site_id,
vendor_lot_num,
organization_id,
from_subinventory, /*FPJ WMS change */
from_locator_id,
subinventory,
locator_id,
rma_reference,
deliver_to_person_id,
deliver_to_location_id,
department_code,
wip_entity_id,
wip_line_id,
wip_repetitive_schedule_id,
wip_operation_seq_num,
wip_resource_seq_num,
bom_resource_id,
inv_transaction_id,
reason_id,
destination_context,
comments,
interface_transaction_id,
group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
movement_id,
invoice_status_code, /* BUG 551612 */
qa_collection_id,
mvt_stat_status,
country_of_origin_code,
oe_order_header_id,
oe_order_line_id,
customer_id,
customer_site_id,
lpn_id,
transfer_lpn_id,
mobile_txn,
secondary_quantity,
secondary_unit_of_measure,
secondary_uom_code, --Bug 8273466
consigned_flag, /**/
lpn_group_id, /*FPJ WMS */
amount,
job_id,
timecard_id,
timecard_ovn,
project_id,
task_id,
requested_amount, --Complex work
material_stored_amount, -- Complex Work
replenish_order_line_id, -- Bug 5367699
source_transaction_num, -- Bug 5842219
lcm_shipment_line_id, -- lcm changes
unit_landed_cost, -- lcm changes
lcm_adjustment_num ) -- changes for LCM-OPM integration project
VALUES
(l_rt_row.transaction_id,
l_rt_row.last_update_date,
l_rt_row.last_updated_by,
l_rt_row.created_by,
l_rt_row.creation_date,
l_rt_row.last_update_login,
l_rt_row.request_id,
l_rt_row.program_application_id,
l_rt_row.program_id,
l_rt_row.program_update_date,
l_rt_row.interface_source_code,
l_rt_row.interface_source_line_id,
l_rt_row.user_entered_flag,
l_rt_row.transaction_type,
l_rt_row.transaction_date,
l_rt_row.source_document_code,
l_rt_row.destination_type_code,
l_rt_row.location_id,
l_rt_row.quantity,
l_rt_row.unit_of_measure,
l_rt_row.uom_code,
l_rt_row.primary_quantity,
l_rt_row.primary_unit_of_measure,
l_rt_row.source_doc_quantity,
l_rt_row.source_doc_unit_of_measure,
l_rt_row.shipment_header_id,
l_rt_row.shipment_line_id,
l_rt_row.parent_transaction_id,
l_rt_row.employee_id,
l_rt_row.po_header_id,
l_rt_row.po_release_id,
l_rt_row.po_line_id,
l_rt_row.po_line_location_id,
l_rt_row.po_distribution_id,
l_rt_row.po_revision_num,
l_rt_row.requisition_line_id,
l_rt_row.req_distribution_id,
l_rt_row.po_unit_price,
l_rt_row.currency_code,
l_rt_row.currency_conversion_rate,
l_rt_row.currency_conversion_date,
l_rt_row.currency_conversion_type,
l_rt_row.routing_header_id,
l_rt_row.routing_step_id,
l_rt_row.substitute_unordered_code,
l_rt_row.receipt_exception_flag,
l_rt_row.inspection_status_code,
l_rt_row.inspection_quality_code,
l_rt_row.vendor_id,
l_rt_row.vendor_site_id,
l_rt_row.vendor_lot_num,
l_rt_row.organization_id,
l_rt_row.from_subinventory, /*FPJ WMS change */
l_rt_row.from_locator_id,
l_rt_row.subinventory,
l_rt_row.locator_id,
l_rt_row.rma_reference,
l_rt_row.deliver_to_person_id,
l_rt_row.deliver_to_location_id,
l_rt_row.department_code,
l_rt_row.wip_entity_id,
l_rt_row.wip_line_id,
l_rt_row.wip_repetitive_schedule_id,
l_rt_row.wip_operation_seq_num,
l_rt_row.wip_resource_seq_num,
l_rt_row.bom_resource_id,
l_rt_row.inv_transaction_id,
l_rt_row.reason_id,
l_rt_row.destination_context,
l_rt_row.comments,
l_rt_row.interface_transaction_id,
l_rt_row.group_id,
l_rt_row.attribute_category,
l_rt_row.attribute1,
l_rt_row.attribute2,
l_rt_row.attribute3,
l_rt_row.attribute4,
l_rt_row.attribute5,
l_rt_row.attribute6,
l_rt_row.attribute7,
l_rt_row.attribute8,
l_rt_row.attribute9,
l_rt_row.attribute10,
l_rt_row.attribute11,
l_rt_row.attribute12,
l_rt_row.attribute13,
l_rt_row.attribute14,
l_rt_row.attribute15,
l_rt_row.movement_id,
l_rt_row.invoice_status_code, /* BUG 551612 */
l_rt_row.qa_collection_id,
l_rt_row.mvt_stat_status,
l_rt_row.country_of_origin_code,
l_rt_row.oe_order_header_id,
l_rt_row.oe_order_line_id,
l_rt_row.customer_id,
l_rt_row.customer_site_id,
l_rt_row.lpn_id,
l_rt_row.transfer_lpn_id,
l_rt_row.mobile_txn,
l_rt_row.secondary_quantity,
l_rt_row.secondary_unit_of_measure,
l_rti_row.secondary_uom_code, -- Bug 8273466
l_rt_row.consigned_flag, /**/
l_rt_row.lpn_group_id, /*FPJ WMS */
l_rt_row.amount,
l_rt_row.job_id,
l_rt_row.timecard_id,
l_rt_row.timecard_ovn,
l_rt_row.project_id,
l_rt_row.task_id,
l_rt_row.requested_amount, --Complex work
l_rt_row.material_stored_amount, -- Complex Work
l_rt_row.replenish_order_line_id, -- Bug 5367699
l_rt_row.source_transaction_num, -- Bug 5842219
l_rt_row.lcm_shipment_line_id, -- lcm changes
l_rt_row.unit_landed_cost, -- lcm changes
decode(l_rt_row.lcm_shipment_line_id, null, null,nvl(l_rt_row.lcm_adjustment_num,0)) );--changes for LCM-OPM integration project
asn_debug.put_line('Error occured while inserting into rcv_transactions...'||sqlerrm);
* we need to update the parent_transaction_id of
* children with this new transaction id since it will
* not be populated at the pre-processor stage.
* Update only those rows which has parent_transaction_id
* as null since if the user has populated parent_transaction_id
* and parent_interface_txn_id, then we dont want to override
* it.
*/
UPDATE rcv_transactions_interface
SET parent_transaction_id = l_rt_row.transaction_id
, shipment_line_id = l_rt_row.shipment_line_id
WHERE parent_interface_txn_id = l_rti_row.interface_transaction_id
AND parent_transaction_id IS NULL;
UPDATE po_note_references
SET table_name = 'RCV_TRANSACTIONS',
column_name = 'TRANSACTION_ID',
foreign_id = l_rt_row.transaction_id
WHERE table_name = 'RCV_TRANSACTIONS_INTERFACE'
AND column_name = 'INTERFACE_TRANSACTION_ID'
AND foreign_id = l_rti_row.interface_transaction_id;
INSERT INTO po_note_references
(po_note_reference_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
po_note_id,
table_name,
column_name,
foreign_id,
sequence_num,
storage_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
SELECT po_note_references_s.nextval,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
po_note_id,
table_name,
column_name,
l_rt_row.transaction_id,
sequence_num,
storage_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM po_note_references
WHERE table_name = 'RCV_TRANSACTIONS'
AND column_name = 'TRANSACTION_ID'
AND foreign_id = p_receive_id;
asn_debug.put_line('Error while inserting into po_note_references...'||sqlerrm);