[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
distributions created for a specific PO distribution is selected. If the receipt quantity is
0 then quantity invoiced equals the total invoice quantity divided by the number of invoices.
If receipt quantity greater than 0 then the greatest of receipt quantity and total invoice
quantity is distributed to the number of invoices.
*/
FUNCTION get_rcpt_qty(p_po_dist in number,
p_qty_ordered in number,
p_qty_cancel in number,
p_qty_billed in number,
p_module in varchar2,
--Pa.M Added below parameters
p_po_line_id in number,
p_project_id in number,
p_task_id in number,
p_ccid in number,
-- Bug 3556021 : Added for retroactive price adjustment
p_pa_quantity IN NUMBER,
p_inv_source IN VARCHAR2,
p_line_type_lookup_code IN VARCHAR2 ,
p_matching_basis in VARCHAR2 default null, -- Bug 3642604
p_nrtax_amt in number default null, -- Bug 3642604
P_CASH_BASIS_ACCTG in varchar2 default 'N',
p_accrue_on_receipt_flag IN varchar2 default NULL /* Bug 5014034 */
) RETURN NUMBER
IS
l_rcpt_qty number;
select sum(apd.quantity_invoiced)
into l_qty_billed
from po_distributions_all pod,
ap_invoice_distributions_all apd
where pod.po_distribution_id = p_po_dist
and pod.po_distribution_id = apd.po_distribution_id
and NVL(pod.accrue_on_receipt_flag,'N') = 'Y'
and apd.pa_addition_flag = 'Y'
and apd.line_type_lookup_code in ( 'ACCRUAL', 'ITEM', 'PREPAY', 'NONREC_TAX' )
and nvl(apd.quantity_invoiced,0) <> 0 ;
Select nvl(Sum(nvl(ENTERED_NR_TAX,0)),0)
Into l_RcptNrTaxCosts
from rcv_transactions a, rcv_receiving_sub_ledger c
where a.po_distribution_id = p_po_dist
and ((a.destination_type_code = 'EXPENSE' ) or
(a.destination_type_code = 'RECEIVING' and
a.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
and c.pa_addition_flag = 'Y'
and c.rcv_transaction_id = a.transaction_id
and c.code_combination_id = p_ccid
and c.actual_flag = 'A';
/* select sum(nvl(denom_raw_cost,0))
into l_EiCost
from pa_expenditure_items_all ei
where ei.project_id = p_project_id
and ei.task_id = p_task_id
and ei.po_line_id = p_po_line_id
and cost_distributed_flag = 'Y';
Select sum(nvl(cdl.denom_raw_cost,0))
into l_EiCost
from pa_cost_distribution_lines_all cdl
, pa_expenditure_items_all ei
where cdl.expenditure_item_id = ei.expenditure_item_id
/* and ei.project_id = p_project_id
and ei.task_id = p_task_id commented for bug:6979249*/
and ei.po_line_id = p_po_line_id;
Select count(*), Sum(nvl(Amount_Ordered,0) + nvl(NonRecoverable_Tax,0) - nvl(Amount_Cancelled,0) -
((nvl(NonRecoverable_Tax,0) * nvl(amount_billed,0) / nvl(amount_ordered,1)) * l_calc_ap_tax)
)
Into l_PoLineDistCnt, l_PoLineDistCosts
From Po_Distributions_All Pod
Where Pod.Project_Id = P_Project_Id
And Pod.distribution_type <> 'PREPAYMENT'
And Pod.Task_Id = P_Task_Id
And Pod.Po_Line_Id = P_Po_Line_Id;
select /*+ Index(c RCV_RECEIVING_SUB_LEDGER_N1) */sum(decode(a.destination_type_code,
'EXPENSE',
decode(transaction_type,
'RETURN TO RECEIVING',
-1 * (decode(c.pa_addition_flag,
'Y',
(nvl(c.entered_dr,0)-nvl(c.entered_cr,0)),
'I',
(nvl(c.entered_dr,0)-nvl(c.entered_cr,0)-nvl(c.entered_nr_tax,0)))),
(decode(c.pa_addition_flag,
'Y',
(nvl(c.entered_dr,0)-nvl(c.entered_cr,0)),
'I',
(nvl(c.entered_dr,0)-nvl(c.entered_cr,0)-nvl(c.entered_nr_tax,0))))),
'RECEIVING',
-1 * (decode(c.pa_addition_flag,
'Y',
(nvl(c.entered_dr,0)-nvl(c.entered_cr,0)),
'I',
(nvl(c.entered_dr,0)-nvl(c.entered_cr,0)-nvl(c.entered_nr_tax,0))))))
into l_rcpt_qty
from rcv_transactions a,
rcv_receiving_sub_ledger c
where a.po_distribution_id = p_po_dist
and ((a.destination_type_code = 'EXPENSE') or
(a.destination_type_code = 'RECEIVING' and
a.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
and c.pa_addition_flag in ('Y', 'I')
and c.rcv_transaction_id = a.transaction_id
and c.actual_flag = 'A';
/*bug 5946201 - We need a specific SELECT for eIB items for reason mentioned in the bug*/
IF (Is_eIB_item(p_po_dist) = 'Y' AND g_accrue_on_receipt_flag = 'N') THEN
select sum(decode(destination_type_code, 'EXPENSE', decode(transaction_type,'RETURN TO RECEIVING',-1 * quantity, quantity),'RECEIVING',-1 * quantity))
into l_rcpt_qty
from rcv_transactions a
where a.po_distribution_id = p_po_dist
and ((a.destination_type_code = 'EXPENSE' ) or
(a.destination_type_code = 'RECEIVING' and a.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
and a.pa_addition_flag in ('Y','I') ;
ELSE /* for 1.all eIB items with accrue at receipt checked and 2.All non-eIB items existing SELECT is fine */
/* Added index hint as part of bug 6408874 */
select sum(decode(destination_type_code,
'EXPENSE',
decode(transaction_type,
'RETURN TO RECEIVING',
-1 * quantity,
quantity),
'RECEIVING',
-1 * quantity))
into l_rcpt_qty
from rcv_transactions a
where a.po_distribution_id = p_po_dist
and ((a.destination_type_code = 'EXPENSE' ) or
(a.destination_type_code = 'RECEIVING' and
a.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
and EXISTS ( SELECT /*+ Index(rcv_sub RCV_RECEIVING_SUB_LEDGER_N1) */ rcv_sub.rcv_transaction_id
FROM rcv_receiving_sub_ledger rcv_sub
WHERE rcv_sub.rcv_transaction_id = a.transaction_id
AND rcv_sub.pa_addition_flag in ('Y', 'I'));
select count(*),
SUM(dist.pa_quantity*(SUM(nvl(paydist.paid_base_amount,paydist.amount))/(nvl(dist.base_amount,dist.amount))))
into l_inv_num, l_tot_inv_qty
from ap_invoice_distributions_all dist,
ap_payment_hist_dists paydist,
ap_invoices_all inv
where dist.po_distribution_id = p_po_dist
and dist.charge_applicable_to_dist_id is null --R12 change
and dist.related_id is null -- R12 change
and dist. line_type_lookup_code <> 'REC_TAX'
and paydist.pa_addition_flag ='N'
and inv.invoice_id = dist.invoice_id
and dist.invoice_distribution_id = paydist.invoice_distribution_id
and NVL(inv.source,'X') <> 'PPA'
-- 4905546
-- ap_payment_hist_dists has discounts and cash records. we need to include quantity
-- only for the payment otherwise quantity would double because of discount.
-- adding the criteria to filter discounts for payment.
and paydist.pay_dist_lookup_code = 'CASH'
group by dist.invoice_distribution_id,NVL(dist.base_amount,dist.amount), dist.pa_quantity;
select count(*), sum(dist.pa_quantity)
into l_inv_num, l_tot_inv_qty
from ap_invoice_distributions_all dist,
ap_invoices_all inv
where dist.po_distribution_id = p_po_dist
-- and dist.line_type_lookup_code = 'ITEM' --R12 change
and dist. line_type_lookup_code <> 'REC_TAX'
and nvl(reversal_flag,'N') <> 'Y' /* Bug 5673779 */
and dist.charge_applicable_to_dist_id is null --R12 change
and dist.related_id is null --R12 change
and dist.pa_addition_flag not in ('Z','T','E','Y') /** Added for bug 3167288 **/
and inv.invoice_id = dist.invoice_id -- Bug 3556021
and NVL(inv.source,'X') <> 'PPA'; -- Bug 3556021
If called from the AP view, first the receipt amount interfaced to PA is selected.
Then the invoice amount omiting the variances is selected in addition to the number
of invoice created for a given po distribution.
If the pa_addition_flag is 'F', meaning the variance amount has been interfaced to PA
then the variance amount is not considered as a commitment, else it is considered for
AP commitment.
The AP commitment is calculated as the invoice amount excluding the variance, receipt
amount. And then we add the variance if it has not yet been transferred to PA.
Since its hard to figure out which receipt matches to which invoice, we always divide
the total invoice amount exculding the receipt amount by the number of invoices
created for a po distribution.
*/
FUNCTION get_inv_cmt(p_po_dist in number,
p_denom_amt_flag in varchar2,
p_pa_add_flag in varchar2,
p_var_amt in number,
p_ccid in number,
p_module in varchar2,
p_invoice_id in number DEFAULT NULL , /* Added for Bug 3394153 */
p_dist_line_num in number DEFAULT NULL, /* Added for Bug 3394153 */
p_inv_dist_id in number DEFAULT NULL, /* Added for Bug 3394153 */
P_CASH_BASIS_ACCTG varchar2 default 'N'
) RETURN NUMBER
IS
l_rcpt_amt number;
select sum(decode(c.pa_addition_flag, 'Y', (nvl(accounted_dr,0)-nvl(accounted_cr,0)),
'I', ((nvl(accounted_dr,0)-nvl(accounted_cr,0))-(nvl(accounted_nr_tax,0))
)))
into l_rcpt_amt
from rcv_transactions a, rcv_receiving_sub_ledger c
where a.po_distribution_id = p_po_dist
and ((a.destination_type_code = 'EXPENSE' ) or
(a.destination_type_code = 'RECEIVING' and
a.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
and c.pa_addition_flag in ('Y', 'I')
and c.rcv_transaction_id = a.transaction_id
and c.code_combination_id = p_ccid
and c.actual_flag = 'A';
select sum(decode(c.pa_addition_flag, 'Y', (nvl(entered_dr,0)-nvl(entered_cr,0)),
'I', ((nvl(entered_dr,0)-nvl(entered_cr,0))-(nvl(entered_nr_tax,0))
)))
into l_rcpt_amt
from rcv_transactions a, rcv_receiving_sub_ledger c
where a.po_distribution_id = p_po_dist
and ((a.destination_type_code = 'EXPENSE' ) or
(a.destination_type_code = 'RECEIVING' and
a.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
and c.pa_addition_flag in ('Y', 'I')
and c.rcv_transaction_id = a.transaction_id
and c.code_combination_id = p_ccid
and c.actual_flag = 'A';
/* Bug 3761335 : removed the exchange_rate_varaince from the select clause if
p_denom_amt_flag is 'Y' , exchange_rate_varaince should not be considered in case of
transaction currency amount , it should be considered only in case of
functional currency amount */
if (p_module = 'AP') then
if(l_rcpt_amt is NOT NULL) Then /* Bug 3394153 :Added If Condition. */
select count(*),
sum(decode(p_denom_amt_flag,
'Y', amount,
'N', nvl(base_amount,amount)))
into l_inv_num, l_inv_amt
from ap_invoice_distributions_all
where po_distribution_id = p_po_dist
--and line_type_lookup_code = 'ITEM'
and line_type_lookup_code <> 'REC_TAX' -- R12 change
and nvl(reversal_flag,'N') <> 'Y' /* Bug 5673779 */
and pa_addition_flag not in ('Z','T','E','Y', 'G') /** Added for bug 3167288 **/
;
select sum(decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', amount,
'N', nvl(base_amount,amount)))
into l_inv_amt
from ap_invoice_distributions_all
where po_distribution_id = p_po_dist
and invoice_id = p_invoice_id
-- and distribution_line_number = p_dist_line_num --R12 change
and invoice_distribution_id = p_inv_dist_id -- R12 change
and line_type_lookup_code <> 'REC_TAX' --R12 change
and nvl(reversal_flag,'N') <> 'Y' /* Bug 5673779 */
and pa_addition_flag not in ('Z','T','E','Y', 'G' ) ;
select sum( decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', paydist.amount,
'N', nvl(paydist.paid_base_amount,paydist.amount)))
into l_pay_amt
from ap_payment_hist_dists paydist
where paydist.invoice_distribution_id = p_inv_dist_id
and NVL(paydist.pa_addition_flag,'N') <> 'N'
and paydist.pay_dist_lookup_code = 'CASH';
select sum( decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', paydist1.amount,
'N', nvl(paydist1.paid_base_amount,paydist1.amount)))
into l_disc_amt
from ap_payment_hist_dists paydist1,
ap_payment_hist_dists paydist2
where paydist1.invoice_distribution_id = p_inv_dist_id
and paydist2.invoice_distribution_id = p_inv_dist_id
and paydist2.invoice_distribution_id = paydist1.invoice_distribution_id
and paydist2.payment_history_id = paydist1.payment_history_id
and paydist2.invoice_payment_id = paydist1.invoice_payment_id
and paydist2.pay_dist_lookup_code = 'CASH'
and paydist1.pay_dist_lookup_code IN ( 'DISCOUNT' )
and NVL(paydist2.pa_addition_flag,'N') <> 'N' ;
select sum( decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', ppaydist.amount,
'N', nvl(ppaydist.base_amount,ppaydist.amount)))
into l_prepay_amt
from ap_prepay_app_dists ppaydist
where ppaydist.invoice_distribution_id = p_inv_dist_id
and NVL(ppaydist.pa_addition_flag,'N') <> 'N' ;
select decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', dist.amount,
'N', nvl(dist.base_amount,dist.amount))
into l_inv_amt
from ap_invoice_distributions_all dist
where invoice_id = p_invoice_id
and dist.invoice_distribution_id = p_inv_dist_id ;
select sum( decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', paydist.amount,
'N', nvl(paydist.paid_base_amount,paydist.amount)))
into l_pay_amt
from ap_payment_hist_dists paydist
where paydist.invoice_distribution_id = p_inv_dist_id
and paydist.pay_dist_lookup_code = 'CASH'
and NVL(paydist.pa_addition_flag,'N') <> 'N' ;
select sum( decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', paydist1.amount,
'N', nvl(paydist1.paid_base_amount,paydist1.amount)))
into l_disc_amt
from ap_payment_hist_dists paydist1,
ap_payment_hist_dists paydist2
where paydist1.invoice_distribution_id = p_inv_dist_id
and paydist2.invoice_distribution_id = p_inv_dist_id
and paydist2.invoice_distribution_id = paydist1.invoice_distribution_id
and paydist2.payment_history_id = paydist1.payment_history_id
and paydist2.invoice_payment_id = paydist1.invoice_payment_id
and paydist2.pay_dist_lookup_code = 'CASH'
and paydist1.pay_dist_lookup_code IN ( 'DISCOUNT' )
and NVL(paydist2.pa_addition_flag,'N') <> 'N' ;
select sum( decode(p_denom_amt_flag, /* Bug 4015448. Added Dummy Sum */
'Y', ppaydist.amount,
'N', nvl(ppaydist.base_amount,ppaydist.amount)))
into l_prepay_amt
from ap_prepay_app_dists ppaydist
where ppaydist.invoice_distribution_id = p_inv_dist_id
and NVL(ppaydist.pa_addition_flag,'N') <> 'N' ;
SELECT 1
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id
AND invoice_type_lookup_code <> 'EXPENSE REPORT'
AND nvl(source, 'xx' ) NOT IN ('PA_COST_ADJUSTMENTS');
SELECT exchange_rate
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id ;
select nvl(pll.matching_basis, 'QUANTITY') /* modified for bug bug 3496492 */
into l_po_line_type
from po_lines_all po_line,
po_distributions_all po_dist,
po_line_locations_all pll
where Po_dist.distribution_type <> 'PREPAYMENT'
and pll.po_line_id = po_line.po_line_id
and po_line.po_line_id = po_dist.po_line_id
and po_dist.po_distribution_id = p_po_dist;
Select decode(p_forqty,'Y', decode(l_po_line_type,'AMOUNT',l_ret_val,p_pa_quantity),l_ret_val)
into l_ret_val
from dual;
select distinct msi.comms_nl_trackable_flag,nvl(pod.accrue_on_receipt_flag,'N')
into l_flag,g_accrue_on_receipt_flag
from
mtl_system_items msi ,
po_distributions_all pod,
po_lines_all pol
where
msi.inventory_item_id=pol.item_id
and pol.po_line_id=pod.po_line_id
and pod.po_distribution_id = p_po_dist_id;