[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT precision,
minimum_accountable_unit
INTO l_precision,
l_mau
FROM fnd_currencies
WHERE currency_code = x_currency_code;
SELECT gl.currency_code
INTO currency
FROM gl_sets_of_books gl,
pa_implementations_all i
-- WHERE NVL(i.org_id,-99) = NVL(x_org_id,-99)
WHERE i.org_id = x_org_id -- x_org_id also taken from Implementation table.
AND i.set_of_books_id = gl.set_of_books_id;
SELECT set_of_books_id
INTO sob_id
FROM pa_implementations_all i
-- WHERE NVL(i.org_id,-99) = NVL(x_org_id,-99);
SELECT set_of_books_id
INTO sob_id
FROM pa_implementations;
SELECT mrc_sob_type_code
INTO sob_type
FROM gl_sets_of_books gl
WHERE gl.set_of_books_id = x_set_of_books_id;
SELECT mrc_sob_type_code
INTO sob_type
FROM gl_sets_of_books gl,
pa_implementations imp
WHERE gl.set_of_books_id = imp.set_of_books_id;
x_err_stage := ' Select from pa_expenditure_items_all';
SELECT eia.orig_transaction_reference,
nvl(eia.adjusted_expenditure_item_id, transferred_from_exp_item_id),
eia.system_linkage_function,
eia.expenditure_item_date,
--Bug#1078399
--New parameter x_txn_source added in eiid_details() - to be used to
--check whether the EI is an imported-one or not.
eia.transaction_source
INTO x_orig_trx,
x_adj_item,
x_linkage,
x_ei_date,
--Bug#1078399
x_txn_source
FROM pa_expenditure_items_all eia
WHERE eia.expenditure_item_id = x_eiid;
x_err_stage := ' Select from pa_expenditure_items_all';
SELECT eia.orig_transaction_reference,
nvl(eia.adjusted_expenditure_item_id, transferred_from_exp_item_id),
eia.system_linkage_function,
eia.expenditure_item_date
INTO x_orig_trx,
x_adj_item,
x_linkage,
x_ei_date
FROM pa_expenditure_items_all eia
WHERE eia.expenditure_item_id = x_eiid;
SELECT max(line_num)
INTO out_line
FROM pa_mc_cost_dist_lines_all
WHERE expenditure_item_id = x_eiid
AND set_of_books_id = x_sob
AND line_type||'' = 'R';
SELECT max(line_num)
INTO out_line
FROM pa_mc_cust_rdl_all
WHERE expenditure_item_id = x_eiid
AND set_of_books_id = x_sob;
x_err_stage := ' Select from pa_mc_cost_dist_lines_all';
SELECT exchange_rate,
conversion_date,
rate_type
INTO x_exchange_rate,
x_exchange_date,
x_exchange_rate_type
FROM pa_mc_cost_dist_lines_all
WHERE expenditure_item_id = x_adj_item
AND line_num = x_line_num
AND set_of_books_id = x_set_of_books_id;
x_err_stage := ' Select from pa_mc_exp_items_all';
SELECT cost_exchange_rate,
cost_conversion_date,
cost_rate_type
INTO x_exchange_rate,
x_exchange_date,
x_exchange_rate_type
FROM pa_mc_exp_items_all
WHERE expenditure_item_id = x_exp_item_id
AND set_of_books_id = x_set_of_books_id;
x_err_stage := ' Select from pa_mc_cost_dist_lines_all';
SELECT amount,
quantity,
exchange_rate,
conversion_date,
rate_type
INTO x_amount,
x_quantity,
x_exchange_rate,
x_exchange_date,
x_exchange_rate_type
FROM pa_mc_cost_dist_lines_all
WHERE expenditure_item_id = x_exp_item_id
AND line_num = x_line_num
AND set_of_books_id = x_set_of_books_id;
x_err_stage := ' Select from pa_mc_cost_dist_lines_all';
SELECT amount,
NVL(burdened_cost,0),
NVL(exchange_rate,0),
conversion_date,
rate_type
INTO x_raw_cost,
x_burdened_cost,
x_exchange_rate,
x_exchange_date,
x_exchange_rate_type
FROM pa_mc_cost_dist_lines_all
WHERE set_of_books_id = x_set_of_books_id
AND expenditure_item_id = x_exp_item_id
AND line_num = ( select max(line_num)
from PA_COST_DISTRIBUTION_LINES_ALL
where expenditure_item_id = x_exp_item_id
and line_type = 'R' );
x_err_stage := ' Select from pa_mc_cust_rdl_all ';
SELECT SUM(amount),
SUM(NVL(bill_amount,0)),
Min(NVL(exchange_rate,0))
INTO x_revenue,
x_bill_amount,
x_exchange_rate
FROM pa_mc_cust_rdl_all
WHERE set_of_books_id = x_set_of_books_id
AND expenditure_item_id = x_exp_item_id;
x_err_stage := ' Select from pa_mc_cust_rdl_all';
SELECT exchange_rate,
conversion_date
INTO x_exchange_rate,
x_exchange_date
FROM pa_mc_cust_rdl_all
WHERE expenditure_item_id = x_adj_item
AND line_num = x_line_num
AND set_of_books_id = x_set_of_books_id;
x_err_stage := ' Select from pa_mc_exp_items_all';
select RAW_COST,
RAW_COST_RATE,
BURDEN_COST,
BURDEN_COST_RATE,
BILL_AMOUNT,
BILL_RATE,
ACCRUED_REVENUE,
ACCRUAL_RATE,
TRANSFER_PRICE,
ADJUSTED_RATE,
COST_EXCHANGE_RATE,
COST_CONVERSION_DATE,
COST_RATE_TYPE
INTO x_raw_cost,
x_raw_cost_rate ,
x_burden_cost,
x_burden_cost_rate ,
x_bill_amount,
x_bill_rate ,
x_accrued_revenue,
x_accrual_rate ,
x_transfer_price,
x_adjusted_rate,
x_exchange_rate,
x_exchange_date,
x_exchange_rate_type
FROM PA_MC_EXP_ITEMS_ALL
WHERE SET_OF_BOOKS_ID = x_set_of_books_id
AND EXPENDITURE_ITEM_ID = nvl(x_adj_exp_item_id,x_xfer_exp_item_id);
x_err_stage := ' Select from pa_mc_exp_items_all';
select RAW_COST,
RAW_COST_RATE,
BURDEN_COST,
BURDEN_COST_RATE,
BILL_AMOUNT,
BILL_RATE,
ACCRUED_REVENUE,
ACCRUAL_RATE,
TRANSFER_PRICE,
ADJUSTED_RATE,
COST_EXCHANGE_RATE,
COST_CONVERSION_DATE,
COST_RATE_TYPE,
RAW_REVENUE, /*3024103*/
ADJUSTED_REVENUE,/*3024103*/
FORECAST_REVENUE/*3024103*/
INTO x_raw_cost,
x_raw_cost_rate ,
x_burden_cost,
x_burden_cost_rate ,
x_bill_amount,
x_bill_rate ,
x_accrued_revenue,
x_accrual_rate ,
x_transfer_price,
x_adjusted_rate,
x_exchange_rate,
x_exchange_date,
x_exchange_rate_type,
x_raw_revenue,/*3024103*/
x_adj_revenue,/*3024103*/
x_forecast_revenue /*3024103*/
FROM PA_MC_EXP_ITEMS_ALL
WHERE SET_OF_BOOKS_ID = x_set_of_books_id
AND EXPENDITURE_ITEM_ID = nvl(x_adj_exp_item_id,x_xfer_exp_item_id);
x_err_stage := ' Select from pa_mc_events';
SELECT rate_type,
exchange_rate,
conversion_date,
projfunc_inv_exchange_rate,
projfunc_inv_rate_date,
bill_amount,
revenue_amount
INTO x_rev_rate_type,
x_rev_exchange_rate,
x_rev_exchange_date,
x_inv_exchange_rate,
x_inv_exchange_date,
x_bill_amount,
x_revenue_amount
FROM PA_MC_EVENTS
WHERE SET_OF_BOOKS_ID = x_set_of_books_id
AND PROJECT_ID = x_project_id
AND EVENT_NUM = x_event_num
AND nvl(TASK_ID,-99) = nvl(x_task_id, -99);
x_err_stage := ' Select from pa_mc_txn_interface_all';
SELECT mc.raw_cost,
mc.raw_cost_rate,
mc.burdened_cost,
mc.burdened_cost_rate,
mc.exchange_rate,
mc.conversion_date,
mc.rate_type
INTO x_raw_cost,
x_raw_cost_rate,
x_burden_cost,
x_burden_cost_rate,
x_exchange_rate,
x_exchange_date,
x_exchange_rate_type
FROM pa_mc_txn_interface_all mc,
pa_transaction_interface_all txn
WHERE mc.txn_interface_id = txn.txn_interface_id
AND txn.expenditure_item_id = x_exp_item_id
AND mc.set_of_books_id = x_set_of_books_id;
x_err_stage := ' Select from pa_cost_distribution_lines_all';
SELECT cdl.system_reference2,
cdl.system_reference3
INTO x_ref2, -- invoice id
x_ref3 -- line num
FROM pa_cost_distribution_lines_all cdl
WHERE cdl.expenditure_item_id = x_eiid
AND rownum = 1;
x_err_stage := ' Select from ap_mc_invoice_dists';
SELECT mc.exchange_rate, -- Bug3056201
-- Bug3056201 decode(NVL(ap.BASE_INVOICE_PRICE_VARIANCE,0),0,mc.EXCHANGE_RATE,
-- (mc.BASE_INVOICE_PRICE_VARIANCE/ap.BASE_INVOICE_PRICE_VARIANCE)) exchange_rate,
nvl(mc.exchange_date,ap.exchange_date) exchange_date,
mc.exchange_rate_type,
nvl(nvl(mc.BASE_INVOICE_PRICE_VARIANCE,
GL_MC_CURRENCY_PKG.CurrRound(ap.amount_variance * nvl(mc.exchange_rate,1),l_reporting_curr_code)),0) amount,/*Bug 4292891*/
sob.currency_code
INTO x_exchange_rate,
x_exchange_date,
x_exchange_rate_type,
x_amount,
l_reporting_curr_code
FROM gl_sets_of_books sob,
ap_mc_invoice_dists mc,
ap_invoice_distributions ap
WHERE ap.invoice_id = x_invoice_id
AND ap.distribution_line_number = x_line_num
AND mc.invoice_id = ap.invoice_id
AND mc.distribution_line_number = ap.distribution_line_number
AND mc.set_of_books_Id = x_sob
AND mc.set_of_books_id = sob.set_of_books_id;
SELECT /*mc.exchange_rate, Bug3056201 */ /*reverted for bug 3927230 */
decode(NVL(ap.EXCHANGE_RATE_VARIANCE,0),0,mc.EXCHANGE_RATE,
(mc.EXCHANGE_RATE_VARIANCE/ap.EXCHANGE_RATE_VARIANCE)) exchange_rate,
nvl(mc.exchange_date,ap.exchange_date) exchange_date,
mc.exchange_rate_type,
nvl(mc.EXCHANGE_RATE_VARIANCE,0) amount /*bug 4292891*/
INTO x_exchange_rate,
x_exchange_date,
x_exchange_rate_type,
x_amount
FROM ap_mc_invoice_dists mc,
ap_invoice_distributions ap
WHERE ap.invoice_id = x_invoice_id
AND ap.distribution_line_number = x_line_num
AND mc.invoice_id = ap.invoice_id
AND mc.distribution_line_number = ap.distribution_line_number
AND mc.set_of_books_Id = x_sob;
SELECT mc.exchange_rate, -- Bug3056201
-- Bug3056201 decode(NVL(ap.base_amount,0),0,mc.exchange_rate,
-- (NVL(mc.base_amount,mc.amount)/ap.base_amount)) exchange_rate,
NVL(mc.exchange_date,ap.exchange_date) exchange_date,
mc.exchange_rate_type,
decode(mc.base_amount,NULL,NVL(mc.amount,0),mc.base_amount) amount /*Bug 4292891 */
INTO x_exchange_rate,
x_exchange_date,
x_exchange_rate_type,
x_amount
FROM ap_mc_invoice_dists mc,
ap_invoice_distributions ap
WHERE ap.invoice_id = x_invoice_id
AND ap.distribution_line_number = x_line_num
AND mc.invoice_id = ap.invoice_id
AND mc.distribution_line_number = ap.distribution_line_number
AND mc.set_of_books_Id = x_sob;
SELECT sum(nvl(amount,0))
INTO rdl_amt
FROM pa_mc_cust_rdl_all
WHERE project_id = x_project_id
AND draft_revenue_num = x_dr_num
AND set_of_books_id = x_sob;
SELECT sum(nvl(amount,0))
INTO rdl_amt_event
FROM pa_mc_cust_event_rdl_all
WHERE project_id = x_project_id
AND draft_revenue_num = x_dr_num
AND set_of_books_id = x_sob;
SELECT sum(nvl(amount,0))
INTO inv_amt
FROM pa_mc_draft_inv_items
WHERE project_id = x_project_id
AND draft_invoice_num = x_di_num
AND line_num = x_line_num
AND set_of_books_id = x_sob;
SELECT sum(nvl(bill_amount,0))
INTO cust_rdl_amt
FROM pa_mc_cust_rdl_all
WHERE project_id = x_project_id
AND draft_invoice_num = x_di_num
AND draft_invoice_item_line_num = x_line_num
AND set_of_books_id = x_sob;
SELECT sum(nvl(mcdii.bill_amount,0))
INTO ic_rdl_amt
FROM pa_mc_draft_inv_details_all mcdii ,
pa_draft_invoice_details_all dii
WHERE dii.project_id = x_project_id
AND dii.draft_invoice_num = x_di_num
AND dii.draft_invoice_line_num = x_line_num
AND dii.draft_invoice_detail_id = mcdii.draft_invoice_detail_id
AND mcdii.set_of_books_id = x_sob;
SELECT sum(nvl(amount,0))
INTO rdl_amt
FROM pa_mc_cust_event_rdl_all
WHERE project_id = x_project_id
AND draft_invoice_num = x_di_num
AND draft_invoice_item_line_num = x_line_num
AND set_of_books_Id = x_sob;
SELECT sum(nvl(bill_amount,0))
INTO rdl_amt
FROM pa_mc_events
WHERE project_id = x_project_id
AND nvl(task_id,-99) = nvl(x_task_id,-99)
AND event_num = x_event_num
AND set_of_books_id = x_sob;
SELECT completion_date
INTO event_date
FROM pa_events
WHERE project_id = x_project_id
AND nvl(task_Id, -99) = nvl(x_task_id, -99)
AND event_num = x_event_num;
SELECT sum(nvl(rdl.amount,0))
INTO rdl_amt
FROM pa_mc_cust_rdl_all rdl
-- pa_implementations imp -- Fix for Perf Bug 2695336
WHERE rdl.project_id = x_project_id
AND rdl.draft_revenue_num = x_draft_revenue_num
AND rdl.draft_revenue_item_line_num = x_draft_revenue_item_line_num
AND rdl.set_of_books_id = NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO'), 45,10) ), -99);
SELECT sum(nvl(erdl.amount,0))
INTO erdl_amt
FROM pa_mc_cust_event_rdl_all erdl,
pa_cust_event_rdl_all cerdl
-- pa_implementations imp -- Fix for Perf Bug 2695336
WHERE cerdl.project_id = x_project_id
AND cerdl.draft_revenue_num = x_draft_revenue_num
AND cerdl.draft_revenue_item_line_num = x_draft_revenue_item_line_num
AND erdl.set_of_books_id = NVL(TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO'), 45,10) ), -99)
AND cerdl.project_id = erdl.project_id
AND cerdl.event_num = erdl.event_num
AND NVL(cerdl.task_id,-99) = NVL(erdl.task_id,-99)
AND cerdl.line_num = erdl.line_num;
SELECT org_id
INTO orgid
FROM pa_projects_all
WHERE project_id = x_project_id;
SELECT (inv.amount/invorg.amount)
INTO wo_factor
FROM pa_draft_invoice_items invorg,
pa_draft_invoice_items inv
WHERE inv.project_id = x_project_id
AND inv.draft_invoice_num = x_di_num
AND inv.line_num = 1
AND invorg.project_id = inv.project_id
AND invorg.draft_invoice_num = x_di_num_org
AND invorg.line_num = inv.line_num;
SELECT NVL(canceled_flag,'N')
INTO cancel_flag
FROM pa_draft_invoices
WHERE project_id = x_project_id
AND draft_invoice_num = x_di_num;
SELECT sum(mii.amount)
INTO rtn_amount
FROM pa_mc_draft_inv_items mii,
pa_draft_invoice_items ii
WHERE ii.project_id = x_project_id
AND ii.draft_invoice_num = x_di_num
AND ii.invoice_line_type <> 'RETENTION'
AND mii.set_of_books_id = x_sob_id
AND mii.project_id = x_project_id
AND mii.draft_invoice_num = x_di_num
AND mii.line_num = ii.line_num;
PAAPIMP_PKG.G_err_stage := ' Insert into pa_mc_txn_interface_all';
PAAPIMP_PKG.write_log(LOG, 'Inserting transaction source: '||p_transaction_source||
'system_ref2 : ' ||p_system_reference2||
'system_ref3 : ' ||p_system_reference3 ||
'system_ref4: ' ||p_system_reference4||
' into pa_mc_txn_interface_all......');
--select NVL(org_id,-99)
select org_id
into l_org_id
from pa_implementations;
SELECT txn_interface_id,
denom_burdened_cost,
denom_raw_cost,
--removed, should get from MRC table acct_rate_type,
--removed, should get from MRC table acct_exchange_rate,
denom_currency_code
into l_txn_interface_id,
l_burdened_cost,
l_txn_raw_cost,
--removed l_exchange_rate_type,
--removed l_exchange_rate,
l_currency
from pa_transaction_interface_all
where interface_id = p_interface_id
and cdl_system_reference1 = to_char(p_system_reference1)
and cdl_system_reference2 = to_char(p_system_reference2)
and cdl_system_reference3 = to_char(p_system_reference3)
and transaction_source||'' = p_transaction_source;
SELECT txn_interface_id,
denom_burdened_cost,
denom_raw_cost,
denom_currency_code
into l_txn_interface_id,
l_burdened_cost,
l_txn_raw_cost,
l_currency
from pa_transaction_interface_all
where interface_id = p_interface_id
and cdl_system_reference1 = to_char(p_system_reference1)
and cdl_system_reference2 = to_char(p_system_reference2)
and cdl_system_reference3 = to_char(p_system_reference3)
and cdl_system_reference4 = p_system_reference4
and transaction_source||''= p_transaction_source;
SELECT nvl(b.exchange_date,a.invoice_date)
INTO l_exchange_date
FROM ap_invoices_all a,
ap_invoice_distributions_all b
WHERE a.invoice_id = p_system_reference2
AND a.invoice_id = b.invoice_id
AND b.distribution_line_number = p_system_reference3;
SELECT nvl(b.exchange_date,a.invoice_date)
INTO l_exchange_date
FROM ap_invoices_all a,
ap_invoice_distributions_all b,
ap_invoice_payments c
WHERE c.invoice_payment_id = l_inv_pay_id
AND c.invoice_id = p_system_reference2
AND c.invoice_id = b.invoice_id
AND a.invoice_id = b.invoice_id
AND b.distribution_line_number = p_system_reference3;
SELECT nvl(a.currency_conversion_date,a.transaction_date)
INTO l_exchange_date
FROM rcv_transactions a
WHERE a.po_distribution_id = p_system_reference3
AND a.transaction_id = l_rcv_txn_id;
PAAPIMP_PKG.write_log(LOG,'before inserting into pa_mc_txn_interface_all table');
PAAPIMP_PKG.G_err_stage := 'INSERT RECORD INTO PA_MC_TXN_INTERFACE_ALL';
PAAPIMP_PKG.write_log(LOG,'insert SOBID:'||g_rsob_tab(i).rsob_id||
'insert txn_interface_id:'||l_txn_interface_id||
'insert raw_cost:'||l_raw_cost||
'exchange rate:'||l_exchange_rate);
INSERT INTO pa_mc_txn_interface_all (
set_of_books_id ,
txn_interface_id ,
raw_cost ,
raw_cost_rate ,
burdened_cost ,
burdened_cost_rate ,
currency_code ,
exchange_rate ,
conversion_date )
VALUES (
g_rsob_tab(i).rsob_id,
l_txn_interface_id ,
l_raw_cost ,
NULL ,
l_burdened_cost ,
NULL ,
g_rsob_tab(i).rcurrency_code ,
l_exchange_rate ,
l_exchange_date );
PAAPIMP_PKG.write_log(LOG,'after inserting');
PAAPIMP_PKG.G_TRANSACTION_REJECTION_CODE := 'PA_INSERT_MRC_FAILED';
PAAPIMP_PKG.write_log(LOG, 'Inserting system reference2: ' || to_char(p_system_reference2) ||
' system reference3: ' || to_char(p_system_reference3) ||
' into pa_mc_txn_interface_all failed in stage: ' || PAAPIMP_PKG.G_err_stage);
SELECT amount,
acct_tp_exchange_rate,
acct_tp_rate_date,
acct_tp_rate_type
FROM pa_mc_cc_dist_lines_all
WHERE set_of_books_id = x_set_of_books_id
AND expenditure_item_id = x_exp_item_id
AND line_num = ( select max(line_num)
from PA_CC_DIST_LINES_ALL
where expenditure_item_id = x_exp_item_id
and line_type = 'BL' );
x_err_stage := ' Select from pa_mc_cc_dist_lines_all';
SELECT SUM(bill_amount),
Min(acct_exchange_rate),
Min(acct_rate_type),
Min(acct_rate_date)
FROM pa_mc_draft_inv_details_all
WHERE set_of_books_id = x_set_of_books_id
AND draft_invoice_detail_id in
(select draft_invoice_detail_id
from pa_draft_invoice_details_all
where expenditure_item_id = x_exp_item_id) ;
x_err_stage := ' Select from pa_mc_draft_inv_details_all';
x_err_stage := ' Select from rcv_mc_rec_sub_ledger';
/* for the amount that we are selecting, we need to see whether the transaction is an
EXPENSE or RECEIVING transactions. EXPENSE means we take the positive value of dr column
minus the tax amount while RECEIVNG transaction means it is a return, so we take the
negative of the cr column plus the tax amount */
SELECT mcsub.CURRENCY_CONVERSION_RATE exchange_rate, --Bug#3218750
-- Bug#3218750 decode(NVL(rcvsub.ACCOUNTED_DR,0),0,mcsub.CURRENCY_CONVERSION_RATE,
-- (mcsub.ACCOUNTED_DR/rcvsub.accounted_dr )) exchange_rate,
nvl(mcsub.CURRENCY_CONVERSION_DATE,mctxn.CURRENCY_CONVERSION_DATE) exchange_date,
nvl(mctxn.CURRENCY_CONVERSION_TYPE,rcvtxn.CURRENCY_CONVERSION_TYPE) excahnge_rate_type,
decode(rcvtxn.transaction_type,
'RETURN TO RECEIVING',(-nvl(mcsub.accounted_cr,0)+nvl(mcsub.accounted_nr_tax,0)),
'RETURN TO VENDOR',(-nvl(mcsub.accounted_cr,0)+nvl(mcsub.accounted_nr_tax,0)),
(nvl(mcsub.ACCOUNTED_DR,0)-nvl(mcsub.accounted_nr_tax,0))) amount -- Bug 40571541 Added Nvl() clause for accounted_cr or accounted_cr as only one can be populated at a time
INTO x_exchange_rate,
x_exchange_date,
x_exchange_rate_type,
x_amount
FROM rcv_transactions rcvtxn,
rcv_receiving_sub_ledger rcvsub,
rcv_mc_rec_sub_ledger mcsub,
rcv_mc_transactions mctxn,
po_distributions po_dist
WHERE rcvtxn.transaction_id = l_rcv_txn_id
AND rcvtxn.po_distribution_id = x_po_dist_id
AND rcvtxn.po_distribution_id = po_dist.po_distribution_id
AND po_dist.code_combination_id = rcvsub.code_combination_id
AND po_dist.code_combination_id = mcsub.code_combination_id
AND rcvsub.actual_flag = 'A'
AND mcsub.actual_flag = 'A'
AND rcvtxn.transaction_id = rcvsub.rcv_transaction_id
AND rcvtxn.transaction_id = mctxn.transaction_id
AND rcvtxn.transaction_id = mcsub.RCV_TRANSACTION_ID
AND mctxn.SET_OF_BOOKS_ID = x_sob
AND mcsub.SET_OF_BOOKS_ID = x_sob
AND rcvsub.accounting_event_id = nvl(x_acct_evt_id, rcvsub.accounting_event_id) -- pricing changes
AND mcsub.accounting_event_id = nvl(x_acct_evt_id, mcsub.accounting_event_id); -- pricing changes
SELECT mcsub.CURRENCY_CONVERSION_RATE exchange_rate, --Bug#3218750
-- Bug#3218750 decode(NVL(rcvsub.ACCOUNTED_DR,0),0,mcsub.CURRENCY_CONVERSION_RATE,
-- Bug#3218750 (mcsub.ACCOUNTED_DR/rcvsub.accounted_dr)) exchange_rate,
nvl(mcsub.CURRENCY_CONVERSION_DATE,mctxn.CURRENCY_CONVERSION_DATE) exchange_date,
nvl(mctxn.CURRENCY_CONVERSION_TYPE,rcvtxn.CURRENCY_CONVERSION_TYPE) excahnge_rate_type,
decode(rcvtxn.transaction_type,
'RETURN TO RECEIVING',nvl(-mcsub.accounted_nr_tax,0),
'RETURN TO VENDOR',nvl(-mcsub.accounted_nr_tax,0),
nvl(mcsub.accounted_nr_tax,0)) amount /* Bug 4292891 */
INTO x_exchange_rate,
x_exchange_date,
x_exchange_rate_type,
x_amount
FROM rcv_transactions rcvtxn,
rcv_receiving_sub_ledger rcvsub,
rcv_mc_rec_sub_ledger mcsub,
rcv_mc_transactions mctxn,
po_distributions po_dist
WHERE rcvtxn.transaction_id = l_rcv_txn_id
AND rcvtxn.po_distribution_id = x_po_dist_id
AND rcvtxn.po_distribution_id = po_dist.po_distribution_id
AND po_dist.code_combination_id = rcvsub.code_combination_id
AND po_dist.code_combination_id = mcsub.code_combination_id
AND rcvsub.actual_flag = 'A'
AND mcsub.actual_flag = 'A'
AND rcvtxn.transaction_id = rcvsub.rcv_transaction_id
AND rcvtxn.transaction_id = mctxn.transaction_id
AND rcvtxn.transaction_id = mcsub.RCV_TRANSACTION_ID
AND mctxn.SET_OF_BOOKS_ID = x_sob
AND mcsub.SET_OF_BOOKS_ID = x_sob
AND rcvsub.accounting_event_id = nvl(x_acct_evt_id, rcvsub.accounting_event_id) -- pricing changes
AND mcsub.accounting_event_id = nvl(x_acct_evt_id, mcsub.accounting_event_id); -- pricing changes
x_err_stage := ' Select from pa_expenditure_items_all';
SELECT eia.orig_transaction_reference,
nvl(eia.adjusted_expenditure_item_id, transferred_from_exp_item_id),
eia.system_linkage_function,
eia.expenditure_item_date,
eia.transaction_source,
eia.burden_cost,
eia.posted_projfunc_burdened_cost
INTO x_orig_trx,
x_adj_item,
x_linkage,
x_ei_date,
x_txn_source,
x_ei_burdened_cost,
x_ei_burdened_delta
FROM pa_expenditure_items_all eia
WHERE eia.expenditure_item_id = x_eiid;