[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(RT.po_distribution_id, NULL, RT.amount * (POD.amount_ordered/POLL.amount),RT.amount)
INTO l_transaction_amount
FROM rcv_transactions RT,
po_distributions POD,
po_line_locations POLL
WHERE RT.transaction_id = p_rcv_accttxn.rcv_transaction_id
AND POD.po_distribution_id = p_rcv_accttxn.po_distribution_id
AND POLL.line_location_id = p_rcv_accttxn.po_line_location_id;
SELECT APID.amount
INTO l_transaction_amount
FROM ap_invoice_distributions APID
WHERE APID.invoice_distribution_id = p_rcv_accttxn.inv_distribution_id;
SELECT RT.transaction_type, RT.parent_transaction_id
INTO l_rcv_txn_type, l_parent_txn_id
FROM rcv_transactions RT
WHERE RT.transaction_id = p_rcv_accttxn.rcv_transaction_id;
SELECT PARENT.transaction_type
INTO l_par_rcv_txn_type
FROM rcv_transactions PARENT
WHERE PARENT.transaction_id =l_parent_txn_id;
SELECT POD.amount_ordered, POD.amount_delivered
INTO l_po_amount_ordered, l_po_amount_delivered
FROM po_distributions POD
WHERE po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT count(*)
INTO l_account_flag
FROM gl_code_combinations GCC,
cst_organization_definitions COD
WHERE COD.operating_unit = p_org_id
AND COD.chart_of_accounts_id = GCC.chart_of_accounts_id
AND GCC.code_combination_id = l_dist_acct_id;
SELECT DECODE (rt.po_distribution_id,
NULL, rt.source_doc_quantity
* pod.quantity_ordered
/ poll.quantity,
rt.source_doc_quantity
)
INTO l_source_doc_quantity
FROM rcv_transactions rt,
po_line_locations poll,
po_distributions pod
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id
AND poll.line_location_id = p_rcv_accttxn.po_line_location_id
AND pod.po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT DECODE (rt.po_distribution_id,
NULL, l_source_doc_quantity
* pod.quantity_ordered
/ poll.quantity,
l_source_doc_quantity
)
INTO l_source_doc_quantity
FROM rcv_transactions rt,
po_line_locations poll,
po_distributions pod
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id
AND poll.line_location_id = p_rcv_accttxn.po_line_location_id
AND pod.po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT rt.transaction_type, rt.parent_transaction_id
INTO l_rcv_txn_type, l_parent_txn_id
FROM rcv_transactions rt
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id;
SELECT PARENT.transaction_type
INTO l_par_rcv_txn_type
FROM rcv_transactions PARENT
WHERE PARENT.transaction_id = l_parent_txn_id;
SELECT pod.quantity_ordered, pod.quantity_delivered
INTO l_po_quantity_ordered, l_po_quantity_delivered
FROM po_distributions pod
WHERE pod.po_distribution_id = p_rcv_accttxn.po_distribution_id;
/* Bug #3333610. Receiving updates quantity delivered prior to calling the transactions API.
Consequently, we should subtract the current quantity from the quantity delivered to
get the quantity that has been delivered previously. */
l_stmt_num := 90;
SELECT COUNT (*)
INTO l_item_exists
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_rcv_accttxn.item_id
AND msi.organization_id = p_rcv_accttxn.organization_id;
SELECT msi.inventory_asset_flag
INTO l_asset_flag
FROM mtl_system_items msi, po_line_locations poll
WHERE msi.inventory_item_id = p_rcv_accttxn.item_id
AND msi.organization_id = poll.ship_to_organization_id
AND poll.line_location_id = p_rcv_accttxn.po_line_location_id;
SELECT muom.uom_code
INTO l_transaction_uom
FROM rcv_transactions rt, mtl_units_of_measure muom
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id
AND muom.unit_of_measure = rt.source_doc_unit_of_measure;
SELECT poll.price_override
INTO l_unit_price
FROM po_line_locations poll
WHERE poll.line_location_id = p_rcv_accttxn.po_line_location_id;
SELECT apid.unit_price
INTO l_unit_price
FROM ap_invoice_distributions apid
WHERE apid.invoice_distribution_id =
p_rcv_accttxn.inv_distribution_id;
SELECT l_non_recoverable_tax / pod.amount_ordered,
l_recoverable_tax / pod.amount_ordered
INTO l_unit_nr_tax,
l_unit_rec_tax
FROM po_distributions pod
WHERE pod.po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT l_non_recoverable_tax / pod.quantity_ordered,
l_recoverable_tax / pod.quantity_ordered
INTO l_unit_nr_tax,
l_unit_rec_tax
FROM po_distributions pod
WHERE pod.po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT l_old_non_recoverable_tax / pod.quantity_ordered,
l_old_recoverable_tax / pod.quantity_ordered
INTO l_prior_nr_tax,
l_prior_rec_tax
FROM po_distributions pod
WHERE po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT uom_code
INTO l_source_doc_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_event_rec.source_doc_uom;
SELECT uom_code
INTO l_trx_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_event_rec.transaction_uom;
SELECT puom.uom_code, puom.unit_of_measure
INTO l_primary_uom_code, l_primary_uom
FROM mtl_units_of_measure tuom, mtl_units_of_measure puom
WHERE tuom.unit_of_measure = p_event_rec.source_doc_uom
AND tuom.uom_class = puom.uom_class
AND puom.base_uom_flag = 'Y';
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_event_rec.organization_id
AND inventory_item_id = l_item_id;
SELECT unit_of_measure
INTO l_primary_uom
FROM mtl_units_of_measure
WHERE uom_code = l_primary_uom_code;
SELECT line_location_id
INTO l_po_line_location_id
FROM po_distributions
WHERE po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT match_option
INTO l_match_option
FROM po_line_locations
WHERE line_location_id = l_po_line_location_id;
SELECT NVL (pod.rate, 1), poh.rate_type,
pod.rate_date
INTO l_currency_conversion_rate, l_currency_conversion_type,
l_currency_conversion_date
FROM po_distributions pod, po_headers poh
WHERE pod.po_distribution_id = p_rcv_accttxn.po_distribution_id
AND poh.po_header_id = pod.po_header_id;
SELECT rt.currency_conversion_rate, rt.currency_conversion_type,
rt.currency_conversion_date
INTO l_currency_conversion_rate, l_currency_conversion_type,
l_currency_conversion_date
FROM rcv_transactions rt
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id;
SELECT pod.accrual_account_id, pod.code_combination_id,
NVL (pod.dest_charge_account_id, pod.code_combination_id),
pod.budget_account_id
INTO l_pod_accrual_acct_id, l_pod_ccid,
l_dest_pod_ccid,
l_pod_budget_acct_id
FROM po_distributions pod
WHERE pod.po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT receiving_account_id, clearing_account_id,
retroprice_adj_account_id
INTO l_receiving_insp_acct_id, l_clearing_acct_id,
l_retroprice_adj_acct_id
FROM rcv_parameters
WHERE organization_id = p_rcv_accttxn.organization_id;
SELECT parent_trx.transaction_type
INTO l_parent_trx_type
FROM rcv_transactions trx, rcv_transactions parent_trx
WHERE trx.transaction_id = p_rcv_accttxn.rcv_transaction_id
AND trx.parent_transaction_id = parent_trx.transaction_id;
SELECT cost_of_sales_account
INTO l_ic_coss_acct_id
FROM mtl_parameters mp
WHERE mp.organization_id = p_rcv_accttxn.organization_id;
SELECT cost_of_sales_account
INTO l_ic_coss_acct_id
FROM mtl_parameters mp
WHERE mp.organization_id = p_rcv_accttxn.organization_id;
SELECT rt.transaction_type, rt.parent_transaction_id
INTO l_trx_type, l_parent_trx_id
FROM rcv_transactions rt
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id;
SELECT parent_trx.transaction_type
INTO l_parent_trx_type
FROM rcv_transactions parent_trx
WHERE parent_trx.transaction_id = l_parent_trx_id;
SELECT ussgl_transaction_code
INTO l_ussgl_tc
FROM po_distributions
WHERE po_distribution_id = p_rcv_accttxn.po_distribution_id;
SELECT rsl.ussgl_transaction_code
INTO l_ussgl_tc
FROM rcv_transactions rt, rcv_shipment_lines rsl
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id;
SELECT NVL (fsp.purch_encumbrance_flag, 'N')
INTO l_encumbrance_flag
FROM financials_system_parameters fsp
WHERE fsp.set_of_books_id = p_rcv_ledger_id;
SELECT p_rcv_accttxn.organization_id, p_rcv_accttxn.item_id,
p_txn_type_id, rt.po_header_id,
p_sign * ABS (p_rcv_accttxn.transaction_quantity),
p_rcv_accttxn.trx_uom_code,
p_sign * ABS (p_rcv_accttxn.primary_quantity),
rt.transaction_date,
DECODE (NVL (fc.minimum_accountable_unit, 0),
0, ROUND (l_unit_price * p_rcv_accttxn.primary_quantity,
fc.PRECISION
)
* p_rcv_accttxn.currency_conversion_rate
/ p_rcv_accttxn.primary_quantity,
ROUND ( l_unit_price
* p_rcv_accttxn.primary_quantity
/ fc.minimum_accountable_unit
)
* fc.minimum_accountable_unit
* p_rcv_accttxn.currency_conversion_rate
/ p_rcv_accttxn.primary_quantity
),
'RCV', rt.transaction_id,
rt.transaction_id,
p_transfer_organization_id, NULL,
--pod.project_id, remove these 2 because projects will cause failure in inv's create_logical_txns
NULL,
--pod.task_id, since they are only expected values in the org that does the deliver
poll.ship_to_location_id,
1, p_rcv_accttxn.trx_flow_header_id,
DECODE (NVL (fc.minimum_accountable_unit, 0),
0, ROUND ( p_intercompany_price
* p_rcv_accttxn.primary_quantity,
fc.PRECISION
)
/ p_rcv_accttxn.primary_quantity,
ROUND ( p_intercompany_price
* p_rcv_accttxn.primary_quantity
/ fc.minimum_accountable_unit
)
* fc.minimum_accountable_unit
/ p_rcv_accttxn.primary_quantity
),
p_intercompany_curr_code,
p_acct_id, 'N',
NULL, NULL,
p_parent_txn_flag, NULL
INTO l_inv_trx.organization_id, l_inv_trx.inventory_item_id,
l_inv_trx.transaction_type_id, l_inv_trx.transaction_source_id,
l_inv_trx.transaction_quantity,
l_inv_trx.transaction_uom,
l_inv_trx.primary_quantity,
l_inv_trx.transaction_date,
l_inv_trx.transaction_cost,
l_inv_trx.source_code, l_inv_trx.source_line_id,
l_inv_trx.rcv_transaction_id,
l_inv_trx.transfer_organization_id, l_inv_trx.project_id,
l_inv_trx.task_id, l_inv_trx.ship_to_location_id,
l_inv_trx.transaction_mode, l_inv_trx.trx_flow_header_id,
l_inv_trx.intercompany_cost,
l_inv_trx.intercompany_currency_code,
l_inv_trx.distribution_account_id, l_inv_trx.costed_flag,
l_inv_trx.subinventory_code, l_inv_trx.locator_id,
l_inv_trx.parent_transaction_flag, l_inv_trx.trx_source_line_id
FROM rcv_transactions rt,
po_lines pol,
po_line_locations poll,
po_distributions pod,
fnd_currencies fc
WHERE rt.transaction_id = p_rcv_accttxn.rcv_transaction_id
AND pol.po_line_id = p_rcv_accttxn.po_line_id
AND poll.line_location_id = p_rcv_accttxn.po_line_location_id
AND pod.po_distribution_id = p_rcv_accttxn.po_distribution_id
AND fc.currency_code = p_rcv_accttxn.currency_code;
SELECT TO_NUMBER (org_information2)
INTO l_le_id
FROM hr_organization_information
WHERE organization_id = p_rcv_accttxn.organization_id
AND org_information_context = 'Accounting Information';
SELECT acct_period_id
INTO l_inv_trx.acct_period_id
FROM org_acct_periods
WHERE organization_id = p_rcv_accttxn.organization_id
AND l_le_txn_date BETWEEN period_start_date AND schedule_close_date
AND open_flag = 'Y';
PROCEDURE insert_mmt (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER
:= fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rcv_accttxn_tbl IN gmf_rcv_accounting_pkg.rcv_accttxn_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Insert_MMT';
SAVEPOINT insert_mmt_pvt;
'Insert_MMT <<'
);
SELECT transaction_type
INTO l_rcv_txn_type
FROM rcv_transactions
WHERE transaction_id = p_rcv_accttxn_tbl (l_ctr).rcv_transaction_id;
'Insert_MMT >>'
);
ROLLBACK TO insert_mmt_pvt;
ROLLBACK TO insert_mmt_pvt;
ROLLBACK TO insert_mmt_pvt;
ROLLBACK TO insert_mmt_pvt;
END insert_mmt;
PROCEDURE insert_txn (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2
:= fnd_api.g_false,
p_commit IN VARCHAR2
:= fnd_api.g_false,
p_validation_level IN NUMBER
:= fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_event_source IN VARCHAR2,
p_event_type_id IN NUMBER,
p_rcv_transaction_id IN NUMBER,
p_inv_distribution_id IN NUMBER,
p_po_distribution_id IN NUMBER,
p_direct_delivery_flag IN VARCHAR2,
p_gl_group_id IN NUMBER,
p_cross_ou_flag IN VARCHAR2,
p_procurement_org_flag IN VARCHAR2,
p_ship_to_org_flag IN VARCHAR2,
p_drop_ship_flag IN NUMBER,
p_org_id IN NUMBER,
p_organization_id IN NUMBER,
p_transfer_org_id IN NUMBER,
p_transfer_organization_id IN NUMBER,
p_trx_flow_header_id IN NUMBER,
p_transaction_forward_flow_rec inv_transaction_flow_pub.mtl_transaction_flow_rec_type,
p_transaction_reverse_flow_rec inv_transaction_flow_pub.mtl_transaction_flow_rec_type,
p_unit_price IN NUMBER,
p_prior_unit_price IN NUMBER,
x_rcv_accttxn OUT NOCOPY gmf_rcv_accounting_pkg.rcv_accttxn_rec_type
)
IS
c_log_module CONSTANT VARCHAR2 (30) := 'Insert_Txn';
l_api_name CONSTANT VARCHAR2 (30) := 'Insert_Txn';
SAVEPOINT insert_txn_pvt;
'Insert_Txn <<'
);
'Insert_Txn : PARAMETERS 1:'
|| ' p_event_source : '
|| p_event_source
|| ' p_event_type_id : '
|| p_event_type_id
|| ' p_rcv_transaction_id : '
|| p_rcv_transaction_id
|| ' p_inv_distribution_id : '
|| p_inv_distribution_id
|| ' p_po_distribution_id : '
|| p_po_distribution_id
|| ' p_direct_delivery_flag : '
|| p_direct_delivery_flag
|| ' p_gl_group_id : '
|| p_gl_group_id
|| ' p_cross_ou_flag : '
|| p_cross_ou_flag;
'Insert_Txn : PARAMETERS 2:'
|| ' p_procurement_org_flag : '
|| p_procurement_org_flag
|| ' p_ship_to_org_flag : '
|| p_ship_to_org_flag
|| ' p_drop_ship_flag : '
|| p_drop_ship_flag
|| ' p_org_id : '
|| p_org_id
|| ' p_organization_id : '
|| p_organization_id
|| ' p_transfer_org_id : '
|| p_transfer_org_id
|| ' p_transfer_organization_id : '
|| p_transfer_organization_id
|| ' p_trx_flow_header_id : '
|| p_trx_flow_header_id
|| ' p_unit_price : '
|| p_unit_price
|| ' p_prior_unit_price : '
|| p_prior_unit_price;
SELECT pod.po_header_id, pol.po_line_id,
pod.po_distribution_id,
pod.destination_type_code,
poll.line_location_id,
SYSDATE, pol.item_id,
apid.quantity_invoiced,
poll.unit_meas_lookup_code, poh.currency_code
INTO l_rcv_accttxn.po_header_id, l_rcv_accttxn.po_line_id,
l_rcv_accttxn.po_distribution_id,
l_rcv_accttxn.destination_type_code,
l_rcv_accttxn.po_line_location_id,
l_rcv_accttxn.transaction_date, l_rcv_accttxn.item_id,
l_rcv_accttxn.source_doc_quantity,
l_rcv_accttxn.source_doc_uom, l_rcv_accttxn.currency_code
FROM ap_invoice_distributions apid,
po_distributions pod,
po_line_locations poll,
po_lines pol,
po_headers poh
WHERE apid.invoice_distribution_id = p_inv_distribution_id
AND pod.po_distribution_id = apid.po_distribution_id
AND pod.line_location_id = poll.line_location_id
AND pol.po_line_id = poll.po_line_id
AND poh.po_header_id = pod.po_header_id;
SELECT rt.po_header_id, rt.po_line_id,
rt.po_line_location_id,
rt.transaction_date, pol.item_id,
poll.ship_to_organization_id, rt.unit_of_measure,
rt.source_doc_unit_of_measure, poh.currency_code,
pod.destination_type_code
INTO l_rcv_accttxn.po_header_id, l_rcv_accttxn.po_line_id,
l_rcv_accttxn.po_line_location_id,
l_rcv_accttxn.transaction_date, l_rcv_accttxn.item_id,
l_dest_org_id, l_rcv_accttxn.transaction_uom,
l_rcv_accttxn.source_doc_uom, l_rcv_accttxn.currency_code,
l_rcv_accttxn.destination_type_code
FROM rcv_transactions rt,
po_lines pol,
po_line_locations poll,
po_headers poh,
po_distributions pod
WHERE rt.transaction_id = p_rcv_transaction_id
AND poh.po_header_id = rt.po_header_id
AND pol.po_line_id = rt.po_line_id
AND poll.line_location_id = rt.po_line_location_id
AND pod.po_distribution_id = p_po_distribution_id;
SELECT set_of_books_id
INTO l_rcv_accttxn.ledger_id
FROM cst_organization_definitions cod
WHERE organization_id = p_organization_id;
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
SELECT DECODE (poll.matching_basis, 'AMOUNT', 'Y', 'N')
INTO l_rcv_accttxn.service_flag
FROM po_line_locations poll
WHERE poll.line_location_id = l_rcv_accttxn.po_line_location_id;
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txnt : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn : '
|| l_stmt_num
|| ' : '
|| l_api_message
);
'Insert_Txn >>'
);
ROLLBACK TO insert_txn_pvt;
ROLLBACK TO insert_txn_pvt;
ROLLBACK TO insert_txn_pvt;
'Insert_Txn '
|| l_stmt_num
|| ' : '
|| SUBSTR (SQLERRM, 1, 200)
);
END insert_txn;
* Adapted from Insert_Txn
** ========================================== **/
PROCEDURE insert_txn2 (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER
:= fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rcv_accttxn_tbl IN gmf_rcv_accounting_pkg.rcv_accttxn_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Insert_Txn2';
SAVEPOINT insert_txn2_pvt;
'Insert_Txn2 <<'
);
'Inserting '
|| p_rcv_accttxn_tbl.COUNT
|| ' transactions into GRAT';
SELECT NVL (poll.accrue_on_receipt_flag, 'N')
INTO l_accrue_on_receipt_flag
FROM po_line_locations poll
WHERE poll.line_location_id =
p_rcv_accttxn_tbl (l_ctr_first).po_line_location_id;
<>
FOR i IN p_rcv_accttxn_tbl.FIRST .. p_rcv_accttxn_tbl.LAST
LOOP
l_stmt_num := 30;
SELECT gmf_rcv_accounting_txns_s.NEXTVAL
INTO l_accounting_txn_id
FROM DUAL;
INSERT INTO gmf_rcv_accounting_txns
(accounting_txn_id,
rcv_transaction_id,
event_type,
event_source,
event_source_id,
ledger_id,
org_id,
transfer_org_id,
organization_id,
transfer_organization_id,
debit_account_id,
credit_account_id,
transaction_date,
transaction_quantity,
transaction_unit_of_measure,
source_doc_quantity,
source_doc_unit_of_measure,
primary_quantity,
primary_unit_of_measure,
inventory_item_id,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
intercompany_pricing_option,
unit_price,
transaction_amount,
prior_unit_price,
nr_tax,
rec_tax, nr_tax_amount, rec_tax_amount,
prior_nr_tax,
prior_rec_tax,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date, accounted_flag,
procurement_org_flag,
cross_ou_flag,
trx_flow_header_id, invoiced_flag, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, request_id,
program_application_id, program_id,
program_udpate_date
)
VALUES (gmf_rcv_accounting_txns_s.NEXTVAL,
DECODE (p_rcv_accttxn_tbl (i).event_source,
'INVOICEMATCH', p_rcv_accttxn_tbl (i).inv_distribution_id,
p_rcv_accttxn_tbl (i).rcv_transaction_id
),
p_rcv_accttxn_tbl (i).event_type_id,
p_rcv_accttxn_tbl (i).event_source,
DECODE (p_rcv_accttxn_tbl (i).event_source,
'INVOICEMATCH', p_rcv_accttxn_tbl (i).inv_distribution_id,
p_rcv_accttxn_tbl (i).rcv_transaction_id
),
p_rcv_accttxn_tbl (i).ledger_id,
p_rcv_accttxn_tbl (i).org_id,
p_rcv_accttxn_tbl (i).transfer_org_id,
p_rcv_accttxn_tbl (i).organization_id,
p_rcv_accttxn_tbl (i).transfer_organization_id,
p_rcv_accttxn_tbl (i).debit_account_id,
p_rcv_accttxn_tbl (i).credit_account_id,
p_rcv_accttxn_tbl (i).transaction_date,
DECODE (p_rcv_accttxn_tbl (i).service_flag,
'N', p_rcv_accttxn_tbl (i).transaction_quantity,
NULL
),
p_rcv_accttxn_tbl (i).transaction_uom,
DECODE (p_rcv_accttxn_tbl (i).service_flag,
'N', p_rcv_accttxn_tbl (i).source_doc_quantity,
NULL
),
p_rcv_accttxn_tbl (i).source_doc_uom,
DECODE (p_rcv_accttxn_tbl (i).service_flag,
'N', p_rcv_accttxn_tbl (i).primary_quantity,
NULL
),
p_rcv_accttxn_tbl (i).primary_uom,
p_rcv_accttxn_tbl (i).item_id,
p_rcv_accttxn_tbl (i).po_header_id,
p_rcv_accttxn_tbl (i).po_release_id,
p_rcv_accttxn_tbl (i).po_line_id,
p_rcv_accttxn_tbl (i).po_line_location_id,
p_rcv_accttxn_tbl (i).po_distribution_id,
p_rcv_accttxn_tbl (i).intercompany_pricing_option,
DECODE (p_rcv_accttxn_tbl (i).service_flag,
'N', p_rcv_accttxn_tbl (i).unit_price
+ p_rcv_accttxn_tbl (i).unit_nr_tax,
NULL
),
DECODE (p_rcv_accttxn_tbl (i).event_source,
'RETROPRICE', p_rcv_accttxn_tbl (i).prior_unit_price
+ p_rcv_accttxn_tbl (i).prior_nr_tax,
NULL
),
p_rcv_accttxn_tbl (i).prior_unit_price,
p_rcv_accttxn_tbl (i).unit_nr_tax,
p_rcv_accttxn_tbl (i).unit_rec_tax, NULL, NULL,
p_rcv_accttxn_tbl (i).prior_nr_tax,
p_rcv_accttxn_tbl (i).prior_rec_tax,
p_rcv_accttxn_tbl (i).currency_code,
p_rcv_accttxn_tbl (i).currency_conversion_type,
p_rcv_accttxn_tbl (i).currency_conversion_rate,
p_rcv_accttxn_tbl (i).currency_conversion_date, 'N',
p_rcv_accttxn_tbl (i).procurement_org_flag,
p_rcv_accttxn_tbl (i).cross_ou_flag,
p_rcv_accttxn_tbl (i).trx_flow_header_id, 'Y', SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
fnd_global.login_id, fnd_global.conc_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
SYSDATE
);
'Inserted '
|| SQL%ROWCOUNT
|| 'rows in GRAT for org '
|| p_rcv_accttxn_tbl (i).org_id;
END LOOP grat_insert;
'Insert_Txn2 >>'
);
ROLLBACK TO insert_txn2_pvt;
ROLLBACK TO insert_txn2_pvt;
ROLLBACK TO insert_txn2_pvt;
'Insert_Txn2 : '
|| l_stmt_num
|| ' : '
|| SUBSTR (SQLERRM, 1, 200)
);
END insert_txn2;
SELECT po_distribution_id, destination_type_code, project_id
FROM po_distributions pod
WHERE pod.po_distribution_id =
NVL (p_po_distribution_id, pod.po_distribution_id)
AND pod.line_location_id = p_po_line_location_id;
SELECT rt.po_header_id, rt.po_line_id, rt.po_line_location_id,
rt.po_distribution_id, rt.transaction_date,
NVL (rt.dropship_type_code, 3), poh.org_id,
poll.ship_to_organization_id, pol.item_id, pol.category_id,
pol.project_id, NVL (poll.accrue_on_receipt_flag, 'N')
INTO l_po_header_id, l_po_line_id, l_po_line_location_id,
l_po_distribution_id, l_rcv_trx_date,
l_drop_ship_flag, l_po_org_id,
l_rcv_organization_id, l_item_id, l_category_id,
l_project_id, l_accrual_flag
FROM po_headers poh,
po_line_locations poll,
po_lines pol,
rcv_transactions rt
WHERE rt.transaction_id = p_rcv_transaction_id
AND poh.po_header_id = rt.po_header_id
AND poll.line_location_id = rt.po_line_location_id
AND pol.po_line_id = rt.po_line_id;
SELECT operating_unit, set_of_books_id
INTO l_rcv_org_id, l_rcv_ledger_id
FROM cst_organization_definitions cod
WHERE organization_id = l_rcv_organization_id;
SELECT set_of_books_id
INTO l_po_ledger_id
FROM financials_system_parameters;
insert_txn
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => logical_receive,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => rec_pod.po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'N',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_transaction_flows_tbl
(l_counter).from_org_id,
p_organization_id => l_transaction_flows_tbl
(l_counter).from_organization_id,
p_transfer_org_id => l_transfer_org_id,
p_transfer_organization_id => l_transfer_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => l_transaction_forward_flow_rec,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => intercompany_invoice,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => rec_pod.po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => NULL,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'N',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_transaction_flows_tbl
(l_counter).from_org_id,
p_organization_id => l_transaction_flows_tbl
(l_counter).from_organization_id,
p_transfer_org_id => l_transaction_flows_tbl
(l_counter).to_org_id,
p_transfer_organization_id => l_transaction_flows_tbl
(l_counter).to_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => l_transaction_forward_flow_rec,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => logical_receive,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => rec_pod.po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'Y',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_org_id,
p_organization_id => l_rcv_organization_id,
p_transfer_org_id => l_transfer_org_id,
p_transfer_organization_id => l_transfer_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => l_transaction_forward_flow_rec,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
SELECT DECODE (rt.transaction_type, 'CORRECT', correct, receive)
INTO l_event_type_id
FROM rcv_transactions rt
WHERE transaction_id = p_rcv_transaction_id;
insert_txn
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => l_event_type_id,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => rec_pod.po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'Y',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_org_id,
p_organization_id => l_rcv_organization_id,
p_transfer_org_id => l_transfer_org_id,
p_transfer_organization_id => l_transfer_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => l_transaction_forward_flow_rec,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
'Inserting transactions into GRAT'
);
insert_txn2 (x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_accttxn_tbl => l_rcv_accttxn_tbl
);
l_api_message := 'Error inserting transactions into GRAT';
'Inserting transactions into MMT'
);
insert_mmt (p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_accttxn_tbl => l_rcv_accttxn_tbl
);
l_api_message := 'Error inserting transactions into MMT';
SELECT rt.po_header_id, rt.po_distribution_id,
pod.destination_type_code, rt.transaction_date,
NVL (rt.dropship_type_code, 3), poh.org_id,
poll.ship_to_organization_id, pol.category_id, pol.project_id,
NVL (poll.accrue_on_receipt_flag, 'N')
INTO l_po_header_id, l_po_distribution_id,
l_destination_type, l_rcv_trx_date,
l_drop_ship_flag, l_po_org_id,
l_rcv_organization_id, l_category_id, l_project_id,
l_accrual_flag
FROM po_headers poh,
po_line_locations poll,
po_lines pol,
po_distributions pod,
rcv_transactions rt
WHERE rt.transaction_id = p_rcv_transaction_id
AND poh.po_header_id = rt.po_header_id
AND poll.line_location_id = rt.po_line_location_id
AND pol.po_line_id = rt.po_line_id
AND pod.po_distribution_id = rt.po_distribution_id;
SELECT operating_unit, set_of_books_id
INTO l_rcv_org_id, l_rcv_ledger_id
FROM org_organization_definitions
WHERE organization_id = l_rcv_organization_id;
SELECT set_of_books_id
INTO l_po_ledger_id
FROM financials_system_parameters;
SELECT DECODE (rt.transaction_type, 'CORRECT', correct, deliver)
INTO l_event_type_id
FROM rcv_transactions rt
WHERE transaction_id = p_rcv_transaction_id;
insert_txn
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => l_event_type_id,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => l_po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'Y',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_rcv_org_id,
p_organization_id => l_rcv_organization_id,
p_transfer_org_id => l_transfer_org_id,
p_transfer_organization_id => l_transfer_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => NULL,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => encumbrance_reversal,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => l_po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'Y',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_rcv_org_id,
p_organization_id => l_rcv_organization_id,
p_transfer_org_id => NULL,
p_transfer_organization_id => NULL,
p_trx_flow_header_id => NULL,
p_transaction_forward_flow_rec => NULL,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
'Inserting txns into GRAT'
);
insert_txn2 (x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_accttxn_tbl => l_rcv_accttxn_tbl
);
l_api_message := 'Error inserting txns into GRAT';
SELECT rt.po_header_id, rt.po_distribution_id,
pod.destination_type_code, rt.transaction_date,
NVL (rt.dropship_type_code, 3), poh.org_id,
poll.ship_to_organization_id, pol.category_id, pol.project_id,
NVL (poll.accrue_on_receipt_flag, 'N')
INTO l_po_header_id, l_po_distribution_id,
l_destination_type, l_rcv_trx_date,
l_drop_ship_flag, l_po_org_id,
l_rcv_organization_id, l_category_id, l_project_id,
l_accrual_flag
FROM po_headers poh,
po_line_locations poll,
po_lines pol,
po_distributions pod,
rcv_transactions rt
WHERE rt.transaction_id = p_rcv_transaction_id
AND poh.po_header_id = rt.po_header_id
AND poll.line_location_id = rt.po_line_location_id
AND pol.po_line_id = rt.po_line_id
AND pod.po_distribution_id = rt.po_distribution_id;
SELECT operating_unit, set_of_books_id
INTO l_rcv_org_id, l_rcv_ledger_id
FROM cst_organization_definitions cod
WHERE organization_id = l_rcv_organization_id;
SELECT set_of_books_id
INTO l_po_ledger_id
FROM financials_system_parameters;
SELECT DECODE (rt.transaction_type,
'CORRECT', correct,
return_to_receiving
)
INTO l_event_type_id
FROM rcv_transactions rt
WHERE transaction_id = p_rcv_transaction_id;
insert_txn
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => l_event_type_id,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => l_po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'Y',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_rcv_org_id,
p_organization_id => l_rcv_organization_id,
p_transfer_org_id => l_transfer_org_id,
p_transfer_organization_id => l_transfer_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => NULL,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => encumbrance_reversal,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => l_po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'Y',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_rcv_org_id,
p_organization_id => l_rcv_organization_id,
p_transfer_org_id => NULL,
p_transfer_organization_id => NULL,
p_trx_flow_header_id => NULL,
p_transaction_forward_flow_rec => NULL,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
'Inserting transactions into GRAT'
);
insert_txn2 (x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_accttxn_tbl => l_rcv_accttxn_tbl
);
l_api_message := 'Error inserting transactions into GRAT';
SELECT po_distribution_id, destination_type_code, project_id
FROM po_distributions pod
WHERE pod.po_distribution_id =
NVL (p_po_distribution_id, pod.po_distribution_id)
AND pod.line_location_id = p_po_line_location_id;
SELECT rt.po_header_id, rt.po_line_id, rt.po_line_location_id,
rt.po_distribution_id, rt.transaction_date,
NVL (rt.dropship_type_code, 3), poh.org_id,
poll.ship_to_organization_id, pol.item_id, pol.category_id,
pol.project_id, NVL (poll.accrue_on_receipt_flag, 'N')
INTO l_po_header_id, l_po_line_id, l_po_line_location_id,
l_po_distribution_id, l_rcv_trx_date,
l_drop_ship_flag, l_po_org_id,
l_rcv_organization_id, l_item_id, l_category_id,
l_project_id, l_accrual_flag
FROM po_headers poh,
po_line_locations poll,
po_lines pol,
rcv_transactions rt
WHERE rt.transaction_id = p_rcv_transaction_id
AND poh.po_header_id = rt.po_header_id
AND poll.line_location_id = rt.po_line_location_id
AND pol.po_line_id = rt.po_line_id;
SELECT operating_unit, set_of_books_id
INTO l_rcv_org_id, l_rcv_ledger_id
FROM cst_organization_definitions cod
WHERE organization_id = l_rcv_organization_id;
SELECT set_of_books_id
INTO l_po_ledger_id
FROM financials_system_parameters;
insert_txn
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => logical_return_to_vendor,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => rec_pod.po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'N',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_transaction_flows_tbl
(l_counter).from_org_id,
p_organization_id => l_transaction_flows_tbl
(l_counter).from_organization_id,
p_transfer_org_id => l_transfer_org_id,
p_transfer_organization_id => l_transfer_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => l_transaction_forward_flow_rec,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => intercompany_reversal,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => rec_pod.po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => NULL,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'N',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_transaction_flows_tbl
(l_counter).from_org_id,
p_organization_id => l_transaction_flows_tbl
(l_counter).from_organization_id,
p_transfer_org_id => l_transaction_flows_tbl
(l_counter).to_org_id,
p_transfer_organization_id => l_transaction_flows_tbl
(l_counter).to_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => l_transaction_forward_flow_rec,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
SELECT DECODE (rt.transaction_type,
'CORRECT', correct,
return_to_vendor
)
INTO l_event_type_id
FROM rcv_transactions rt
WHERE transaction_id = p_rcv_transaction_id;
insert_txn
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RECEIVING',
p_event_type_id => l_event_type_id,
p_rcv_transaction_id => p_rcv_transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => rec_pod.po_distribution_id,
p_direct_delivery_flag => p_direct_delivery_flag,
p_gl_group_id => p_gl_group_id,
p_cross_ou_flag => l_cross_ou_flag,
p_procurement_org_flag => l_procurement_org_flag,
p_ship_to_org_flag => 'Y',
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_org_id,
p_organization_id => l_rcv_organization_id,
p_transfer_org_id => l_transfer_org_id,
p_transfer_organization_id => l_transfer_organization_id,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => l_transaction_forward_flow_rec,
p_transaction_reverse_flow_rec => l_transaction_reverse_flow_rec,
p_unit_price => NULL,
p_prior_unit_price => NULL,
x_rcv_accttxn => l_rcv_accttxn
);
'Inserting transactions into GRAT'
);
insert_txn2 (x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_accttxn_tbl => l_rcv_accttxn_tbl
);
l_api_message := 'Error inserting transactions into GRAT';
'Inserting transactions into MMT'
);
insert_mmt (p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_accttxn_tbl => l_rcv_accttxn_tbl
);
l_api_message := 'Error inserting transactions into MMT';
SELECT rt.consigned_flag, rt.source_document_code, rt.transaction_type,
rt.parent_transaction_id, rt.po_header_id,
rt.po_line_location_id --12i Complex Work Procurement
INTO l_consigned_flag, l_source_doc_code, l_transaction_type,
l_parent_trx_id, l_po_header_id,
l_po_line_location_id --12i Complex Work Procurement
FROM rcv_transactions rt
WHERE transaction_id = p_rcv_transaction_id;
SELECT shipment_type
INTO l_shipment_type
FROM po_line_locations
WHERE line_location_id = l_po_line_location_id;
SELECT transaction_type, parent_transaction_id
INTO l_parent_trx_type, l_grparent_trx_id
FROM rcv_transactions
WHERE transaction_id = l_parent_trx_id;
SELECT transaction_type
INTO l_grparent_trx_type
FROM rcv_transactions
WHERE transaction_id = l_grparent_trx_id;
SELECT a.transaction_id, a.organization_id
FROM rcv_transactions a, mtl_parameters b
WHERE (
(a.transaction_type = 'RECEIVE' AND a.parent_transaction_id = -1)
OR
a.transaction_type = 'MATCH'
)
AND NVL (a.consigned_flag, 'N') <> 'Y'
AND a.po_header_id = p_po_header_id
AND a.organization_id = b.organization_id
AND NVL(b.process_enabled_flag, 'N') = 'Y'
AND a.po_line_location_id = p_po_line_location_id
AND NVL (a.po_release_id, -1) = NVL (p_po_release_id, -1);
SELECT a.transaction_id, a.po_distribution_id
FROM rcv_transactions a, mtl_parameters b
WHERE a.transaction_type = 'DELIVER'
AND a.organization_id = b.organization_id
AND NVL(b.process_enabled_flag, 'N') = 'Y'
AND a.parent_transaction_id = l_par_txn;
SELECT pod.po_distribution_id
FROM po_distributions pod,
po_line_locations poll,
rcv_transactions rt
WHERE pod.line_location_id = poll.line_location_id
AND poll.line_location_id = rt.po_line_location_id
AND rt.transaction_id = l_rcv_txn;
SELECT nvl(b.process_enabled_flag, 'N')
INTO l_process_enabled_flag
FROM po_line_locations_all a,
mtl_parameters b
WHERE a.line_location_id = p_po_line_location_id
AND b.organization_id = a.ship_to_organization_id;
SELECT poll.matching_basis, poll.shipment_type
INTO l_matching_basis, l_shipment_type
FROM po_line_locations poll
WHERE poll.line_location_id = p_po_line_location_id;
SELECT org_id
INTO l_proc_operating_unit
FROM po_headers
WHERE po_header_id = p_po_header_id;
SELECT COUNT (*)
INTO l_rae_count
FROM gmf_rcv_accounting_txns
WHERE rcv_transaction_id = c_par_txn.transaction_id;
SELECT grat.organization_id, grat.trx_flow_header_id,
NVL (rt.dropship_type_code, 3)
INTO l_organization_id, l_trx_flow_header_id,
l_drop_ship_flag
FROM gmf_rcv_accounting_txns grat, rcv_transactions rt
WHERE grat.rcv_transaction_id = c_par_txn.transaction_id
AND rt.transaction_id = grat.rcv_transaction_id
AND grat.procurement_org_flag = 'Y'
AND ROWNUM = 1;
SELECT NVL (po_distribution_id, -1)
INTO l_po_distribution_id
FROM rcv_transactions
WHERE transaction_id = c_par_txn.transaction_id;
insert_txn (x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RETROPRICE',
p_event_type_id => adjust_receive,
p_rcv_transaction_id => c_par_txn.transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => l_po_distribution_id,
p_direct_delivery_flag => NULL,
p_gl_group_id => NULL,
p_cross_ou_flag => NULL,
p_procurement_org_flag => 'Y',
p_ship_to_org_flag => NULL,
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_proc_operating_unit,
p_organization_id => l_organization_id,
p_transfer_org_id => NULL,
p_transfer_organization_id => NULL,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => NULL,
p_transaction_reverse_flow_rec => NULL,
p_unit_price => p_new_po_price,
p_prior_unit_price => p_old_po_price,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RETROPRICE',
p_event_type_id => adjust_receive,
p_rcv_transaction_id => c_par_txn.transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => c_po_dist.po_distribution_id,
p_direct_delivery_flag => NULL,
p_gl_group_id => NULL,
p_cross_ou_flag => NULL,
p_procurement_org_flag => 'Y',
p_ship_to_org_flag => NULL,
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_proc_operating_unit,
p_organization_id => l_organization_id,
p_transfer_org_id => NULL,
p_transfer_organization_id => NULL,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => NULL,
p_transaction_reverse_flow_rec => NULL,
p_unit_price => p_new_po_price,
p_prior_unit_price => p_old_po_price,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn
(x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_event_source => 'RETROPRICE',
p_event_type_id => adjust_deliver,
p_rcv_transaction_id => c_del_txn.transaction_id,
p_inv_distribution_id => NULL,
p_po_distribution_id => c_del_txn.po_distribution_id,
p_direct_delivery_flag => NULL,
p_gl_group_id => NULL,
p_cross_ou_flag => NULL,
p_procurement_org_flag => 'Y',
p_ship_to_org_flag => NULL,
p_drop_ship_flag => l_drop_ship_flag,
p_org_id => l_proc_operating_unit,
p_organization_id => l_organization_id,
p_transfer_org_id => NULL,
p_transfer_organization_id => NULL,
p_trx_flow_header_id => l_trx_flow_header_id,
p_transaction_forward_flow_rec => NULL,
p_transaction_reverse_flow_rec => NULL,
p_unit_price => p_new_po_price,
p_prior_unit_price => p_old_po_price,
x_rcv_accttxn => l_rcv_accttxn
);
insert_txn2 (x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rcv_accttxn_tbl => l_rcv_accttxn_tbl
);
l_api_message := 'Error inserting Transactions into GRAT';