[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;
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 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;
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,
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
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_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
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);