[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Removed the TRUNC function from all SELECT statments.
ii)In query4 Gain or Loss source AP_INVOICE_DISTRIBUTIONS,
the join condition between ap_invoices_all and
ap_invoice_distributions_all was missing. Corrected the same.
6. 22/01/2004 Aparajita for Bug#3392495. Version#618.1
Added code to reduce the invoice amount by discount amount already given.
Changes in cursor C_invoices Query 1.
7. 04/06/2004 Added by vchallur for bug#3663549 version 115.1
added code so that it can free from invoice entry need not start
with disribution_line_number=1
8. 03/08/2004 Aparajita for bug#2839228. Version#115.2
Modified cursor C_vendor_site_id to select vendor site id from po_vendor_sites_all
considering the operating unit also. This was needed as vendor site code for the
same vendor may be same accross operating units..
9. 05/11/2004 Sanjikum for bug # 3896940, Version 115.3
Following changes are made in query 1 of cursor C_invoices
a) In the query's column Credit_val, changed the column from api.Invoice_amt to z.amt_val
b) In the From clause, added the inline view Z
c) In the where clause, added the join "z.invoice_id = api.invoice_id"
10. 05/11/2004 Sanjikum for bug # 4030311, Version 115.4
Following changes are made in query 1 of cursor C_invoices
a) In the inline view Z, added a condition - ap_invoice_distributions_all.Line_type_lookup_code <> 'PREPAY'
11. 12/01/2005 For bug4035943. Added by LGOPALSA
Modified the calculation for balances as per the query
change.
Added the accounted_dr and accounted_cr for GAIN/LOSS
lines as they will have the correct values with respect
to functional currency. Thus we can avoid any
rounding errors on further calculation.
(1) Added invoice_type_lookup_code in all the query
(2) Changed the column selection for exchange rate in query 2
(3) Selected zero for acct_dr except for LOSS lines
(4) Selected accounted_dr from core accounting tables which will make the
calculation easier and also avoids the rounding problems.
(5) Added the validation for 'LOSS' in calculating the credit balance.
(6) Rounded the value to 2 decimal places as this is showing .01 while
displaying closing balance. We need to round it before sending the
values for displaying credit and debite values
12. 14-Jun-2005 rchandan for bug#4428980, Version 116.2
Modified the object to remove literals from DML statements and CURSORS.
As part OF R12 Initiative Inventory conversion the OPM code is commented
13 07/12/2005 Hjujjuru for the bug 4866533 File version 120.3
added the who columns in the insert into table JAI_PO_REP_PRRG_T
Dependencies Due to this bug:-
None
----------------------------------------------------------------------------------------------------------------------- */
v_exchange_rate NUMBER;
SELECT api.invoice_type_lookup_code,
DECODE(api.invoice_type_lookup_code,
'CREDIT',
0,
z.amt_val - nvl(discount_amount_taken, 0)
) credit_val,
0 acct_cr,
api.exchange_rate exchange_rate,
api.exchange_rate_type exchange_rate_type,
api.invoice_currency_code invoice_currency_code,
api.exchange_date exchange_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
(SELECT NVL(SUM(apd.amount),0) amt_val,
api.invoice_id
FROM ap_invoices_all api,
ap_invoice_distributions_all apd
WHERE api.invoice_id = apd.invoice_id
AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
AND apd.match_status_flag = 'A'
AND api.vendor_id = p_vendor_id
AND api.vendor_site_id = v_vendor_site_id
AND apd.accounting_date < p_bal_date
AND (api.org_id = p_org_id or api.org_id is null)
AND apd.line_type_lookup_code <> lv_prepay
GROUP BY api.invoice_id) z
WHERE z.invoice_id = api.invoice_id
AND api.invoice_id = apd.invoice_id
AND apd.rowid = (select rowid
from ap_invoice_distributions_all
where rownum=1
and invoice_id=apd.invoice_id
AND match_status_flag = 'A'
AND accounting_date < p_bal_date)
AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
AND apd.match_status_flag = 'A'
AND api.vendor_id = p_vendor_id
AND api.vendor_site_id = v_vendor_site_id
AND apd.accounting_date < p_bal_date
AND (api.org_id = p_org_id or api.org_id is null)
AND ((api.invoice_type_lookup_code <> lv_debit)--rchandan for bug#4428980
or
(
(api.invoice_type_lookup_code = lv_debit) --rchandan for bug#4428980
and
( not exists
(Select '1'
from ap_invoice_payments_all app,
ap_checks_all apc
where app.check_id = apc.check_id
and app.invoice_id = api.invoice_id
and apc.payment_type_flag = 'R'
)
)
)
)
UNION ALL
-- query 2
SELECT api.invoice_type_lookup_code,
DECODE(api.invoice_type_lookup_code,'CREDIT',
DECODE(status_lookup_code,'VOIDED',
app.amount, ABS(app.amount) ), 0) credit_val,
0 acct_cr,
apc.exchange_rate exchange_rate,
apc.exchange_rate_type exchange_rate_type,
api.payment_currency_code invoice_currency_code,
apc.exchange_date exchange_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
ap_invoice_payments_all app,
ap_checks_all apc
WHERE api.invoice_id = apd.invoice_id
AND apd.distribution_line_number = (select distribution_line_number from ap_invoice_distributions_all
where rownum=1
and invoice_id=apd.invoice_id
AND apd.match_status_flag='A')
AND app.invoice_id = api.invoice_id
AND app.check_id = apc.check_id
AND apc.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,lv_rec_unacc,lv_reconciled,lv_cleared_unacc ) --rchandan for bug#4428980
AND apd.match_status_flag='A'
AND api.vendor_id = p_vendor_id
AND api.vendor_site_id = v_vendor_site_id
AND app.accounting_date < trunc(p_bal_date)
AND ( api.org_id = p_org_id or api.org_id is null )
UNION ALL
-- query 3
/* Gain or Loss source AP_INVOICES */
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'LOSS' invoice_type_lookup_code,
0 credit_val,
DECODE(xal.accounting_class_code,'LOSS', accounted_dr,0) acct_cr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
FROM xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api
WHERE xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
xte.source_id_int_1 = api.invoice_id AND
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
(api.org_id = p_org_id or api.org_id is null )
union all
-- Query 4
/* Gain or Loss source AP_INVOICE_DISTRIBUTIONS */
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'LOSS' invoice_type_lookup_code,
0 credit_val,
DECODE(xal.accounting_class_code,'LOSS', accounted_dr,0) acct_cr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api,
ap_invoice_distributions_all apd
where xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
xte.source_id_int_1 = api.invoice_id AND
api.invoice_id = apd.invoice_id AND
apd.accounting_event_id = xah.event_id AND
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
( api.org_id = p_org_id or api.org_id is null )
union all
-- Query 5
/* Gain or Loss source AP_CHECKS */
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'LOSS' invoice_type_lookup_code,
0 credit_val,
DECODE(xal.accounting_class_code,'LOSS', accounted_dr,0) acct_cr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api,
ap_checks_all ac ,
ap_invoice_payments_all app
where xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
xte.source_id_int_1 = ac.check_id AND
xah.event_id = app.accounting_event_id AND
api.invoice_id = app.invoice_id AND
app.check_id = ac.check_id AND
ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,
lv_rec_unacc,lv_reconciled,lv_cleared_unacc) AND
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
(api.org_id = p_org_id or api.org_id is null )
union all
-- Query 6
/* Gain or Loss source AP_INVOICE_PAYMENTS*/
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'LOSS' invoice_type_lookup_code,
0 credit_val,
DECODE(xal.accounting_class_code,'LOSS', accounted_dr,0) acct_cr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api,
ap_checks_all ac ,
ap_invoice_payments_all app
where xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
xte.source_id_int_1 = ac.check_id AND
xah.event_id = app.accounting_event_id AND
api.invoice_id = app.invoice_id AND
app.check_id = ac.check_id AND
ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,lv_rec_unacc,lv_reconciled,lv_cleared_unacc ) AND--rchandan for bug#4428980
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
(api.org_id = p_org_id or api.org_id is null )
;
SELECT vendor_site_id
FROM po_vendor_sites_all
WHERE vendor_id = p_vendor_id
AND org_id = p_org_id /* Added for Bug 2839228 */
AND vendor_site_code = p_vendor_site_code ;
Removed the TRUNC function from all SELECT statments.
ii)In query4 Gain or Loss source AP_INVOICE_DISTRIBUTIONS, the join condition
between ap_invoices_all and ap_invoice_distributions_all was missing.
Corrected the same.
6. 22/01/2004 Aparajita for Bug#3392495. Version#115.1
Added code to reduce the invoice amount by discount amount already given.
Changes in cursor C_invoices Query 1.
7. 04/06/2004 Added by vchallur for bug#3663549 version 115.1
added code so that it can free from invoice entry need not start with
disribution_line_number=1
8. 03/08/2004 Aparajita for bug#2839228. Version#115.2
Modified cursor C_vendor_site_id to select vendor site id from po_vendor_sites_all
considering the operating unit also. This was needed as vendor site code for the
same vendor may be same accross operating units..
9. 05/11/2004 Sanjikum for bug # 3896940, Version 115.3
Following changes are made in query 1 of cursor C_invoices
a) In the query's column Credit_val, changed the column from api.Invoice_amt to z.amt_val
b) In the From clause, added the inline view Z
c) In the where clause, added the join "z.invoice_id = api.invoice_id"
10. 05/11/2004 Sanjikum for bug # 4030311, Version 115.4
Following changes are made in query 1 of cursor C_invoices
a) In the inline view Z, added a condition - ap_invoice_distributions_all.Line_type_lookup_code <> 'PREPAY'
11. 12/01/2005 For bug4035943. Added by LGOPALSA
Modified the calculation for balances as per the
query change.
Added the accounted_dr and accounted_cr for GAIN/LOSS
lines as they will have the correct values with respect
to functional currency. Thus, we can avoid any rounding
errors on further calculation.
(1) Added invoice_type_lookup_code in all the query
(2) Changed the column selection for exchange rate in
query 2
(3) Selected zero for acct_cr except for GAIN lines
(4) Selected accounted_cr from core accounting tables
which will make the calculation easier and also
avoids the rounding problems.
(5) Added the validation for 'GAIN' in calculating the debit balance.
(6) Rounded the value to 2 decimal places as this is showing .01 while
displaying closing balance. We need to round it before sending the
values for displaying credit and debite values
----------------------------------------------------------------------------------------------*/
v_exchange_rate NUMBER;
SELECT api.invoice_type_lookup_code,
DECODE(api.invoice_type_lookup_code,
'CREDIT',
ABS(z.amt_val) - abs(nvl(discount_amount_taken, 0)), -- Changed from api.Invoice_amt to z.amt_val for Bug#3896940
0
) debit_val,
/* Bug4035943. Added by LGOPALSA */
0 acct_dr,
api.exchange_rate exchange_rate,
api.exchange_rate_type exchange_rate_type,
api.invoice_currency_code invoice_currency_code,
api.exchange_date exchange_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
(SELECT NVL(SUM(apd.amount),0) amt_val, /* Bug#3390665*/
api.invoice_id
FROM ap_invoices_all api,
ap_invoice_distributions_all apd
WHERE api.invoice_id = apd.invoice_id
AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
AND apd.match_status_flag = 'A'
AND api.vendor_id = p_vendor_id
AND api.vendor_site_id = v_vendor_site_id
AND apd.accounting_date < p_bal_date
AND (api.org_id = p_org_id or api.org_id is null)
AND apd.line_type_lookup_code <> lv_prepay --Added by Sanjikum for Bug # 4030311 --rchandan for bug#4428980
GROUP BY api.invoice_id) z -- Added the Inline view for Bug # 3896940, as sum of amount was required in place of invoice amount
WHERE z.invoice_id = api.invoice_id --Added the condition for Bug # 3896940
AND api.invoice_id = apd.invoice_id
AND apd.rowid = (select rowid
from ap_invoice_distributions_all
where rownum=1
and invoice_id=apd.invoice_id
AND match_status_flag = 'A'
AND accounting_date < p_bal_date)
AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
AND apd.match_status_flag = 'A'
AND api.vendor_id = p_vendor_id
AND api.vendor_site_id = v_vendor_site_id
AND apd.accounting_date < p_bal_date
AND (api.org_id = p_org_id or api.org_id is null) -- added by Aparajita on 26-sep-2002 for bug # 2574262
/* Following and clause added by Aparajita on 17/12/2002 for bug # 2668999 */
AND ((api.invoice_type_lookup_code <> lv_debit)
or
(
(api.invoice_type_lookup_code = lv_debit)
and
( not exists
(Select '1'
from ap_invoice_payments_all app,
ap_checks_all apc
where app.check_id = apc.check_id
and app.invoice_id = api.invoice_id
and apc.payment_type_flag = 'R'
)
)
)
)
UNION ALL
-- query 2
SELECT api.invoice_type_lookup_code,
DECODE(api.invoice_type_lookup_code,'CREDIT', 0, app.amount) debit_val,
/* Bug4035943. Added by LGOPALSA */
0 acct_dr,
/* Bug 4035943. Also need to select the exchange rate details from
checks rather that invoices for payments */
apc.exchange_rate exchange_rate,
apc.exchange_rate_type exchange_rate_type,
api.payment_currency_code invoice_currency_code,
apc.exchange_date exchange_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
ap_invoice_payments_all app,
ap_checks_all apc
WHERE api.invoice_id = apd.invoice_id
AND apd.distribution_line_number = (select distribution_line_number from ap_invoice_distributions_all
where rownum=1
and invoice_id=apd.invoice_id
AND apd.match_status_flag='A')
/*added by vchallur for bug#3663549*/
AND app.invoice_id = api.invoice_id
AND app.check_id = apc.check_id
AND apc.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,lv_rec_unacc,lv_reconciled,lv_cleared_unacc ) --rchandan for bug#4428980
AND apd.match_status_flag='A'
AND api.vendor_id = p_vendor_id
AND api.vendor_site_id = v_vendor_site_id
AND app.accounting_date < p_bal_date
AND ( api.org_id = p_org_id or api.org_id is null ) -- added by Aparajita on 26-sep-2002 for bug # 2574262
UNION ALL
-- query 3
/* Gain or Loss source AP_INVOICES */
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'GAIN' invoice_type_lookup_code,
0 debit_val,
DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api
where xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
(api.org_id = p_org_id or api.org_id is null )
union all
-- Query 4
/* Gain or Loss source AP_INVOICE_DISTRIBUTIONS */
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'GAIN' invoice_type_lookup_code,
0 debit_val,
DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api,
ap_invoice_distributions_all apd
WHERE xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
xte.source_id_int_1 = api.invoice_id AND
apd.accounting_event_id = xah.event_id AND
api.invoice_id = apd.invoice_id AND
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
( api.org_id = p_org_id or api.org_id is null )
union all
-- Query 5
/* Gain or Loss source AP_CHECKS */
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'GAIN' invoice_type_lookup_code,
0 debit_val,
DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api,
ap_checks_all ac ,
ap_invoice_payments_all app
where xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
xte.source_id_int_1 = ac.check_id AND
xah.event_id = app.accounting_event_id AND
api.invoice_id = app.invoice_id AND
app.check_id = ac.check_id AND
ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,
lv_rec_unacc,lv_reconciled,
lv_cleared_unacc ) AND --rchandan for bug#4428980
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
(api.org_id = p_org_id or api.org_id is null )
union all
-- Query 6
/* Gain or Loss source AP_INVOICE_PAYMENTS*/
/* Bug 4997569. Added by Lakshmi Gopalsami
(1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
and xla_ae_lines instead of xla_ae_lines.
(2) Changed ae_line_type_code to accounting_class_code
(3) Also added xla_transaction_entities to get the entity_id and
source_int_id_1 so that it can be joined with transaction tables.
*/
select 'GAIN' invoice_type_lookup_code,
0 debit_val,
DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
xal.currency_conversion_rate exchange_rate ,
xal.currency_conversion_type exchange_rate_type,
xal.currency_code invoice_currency_code,
xal.currency_conversion_date exchange_date
from xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_invoices_all api,
ap_checks_all ac ,
ap_invoice_payments_all app
where xal.application_id = 200 AND
xal.ae_header_id = xah.ae_header_id AND
xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
xah.application_id = 200 AND
xah.entity_id = xte.entity_id AND
xte.application_id = 200 AND
xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
xte.source_id_int_1 = ac.check_id AND
xah.event_id = app.accounting_event_id AND
api.invoice_id = app.invoice_id AND
app.check_id = ac.check_id AND
ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,
lv_rec_unacc,lv_reconciled,
lv_cleared_unacc ) AND --rchandan for bug#4428980
api.vendor_id = p_vendor_id AND
api.vendor_site_id = v_vendor_site_id AND
xah.ACCOUNTING_DATE < p_bal_date AND
(api.org_id = p_org_id or api.org_id is null )
;
SELECT vendor_site_id
FROM po_vendor_sites_all
WHERE vendor_id = p_vendor_id
AND org_id = p_org_id /* Added for Bug 2839228 */
AND vendor_site_code = p_vendor_site_code ;
select JAI_PO_REP_PRRG_T_RUNNO_S.nextval
from dual;
cursor c_inv_select_cursor( c_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE ) IS --rchandan for bug#4428980
select invoice_id, invoice_num, org_id, vendor_id, vendor_site_id, invoice_date,
invoice_currency_code, nvl(exchange_rate,1) exchange_rate, voucher_num
from ap_invoices_all aia
where cancelled_date is null
and (p_vendor_id is null or vendor_id = p_vendor_id)
and (p_vendor_site_id is null or vendor_site_id = p_vendor_site_id)
and (p_org_id is null or org_id = p_org_id)
and exists
(select '1'
from ap_invoice_distributions_all
where invoice_id = aia.invoice_id
and line_type_lookup_code = c_line_type_lookup_code
and po_distribution_id is not null
and nvl(reversal_flag, 'N') <> 'Y'
and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
and accounting_date <= p_invoice_date_to /* Modified by Ramananda for bug:4071409 */
);
select
distribution_line_number,
po_distribution_id,
rcv_transaction_id,
amount,
invoice_distribution_id,
invoice_line_number
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and line_type_lookup_code = c_line_type_lookup_code
and po_distribution_id is not null
and nvl(reversal_flag, 'N') <> 'Y'
and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
and accounting_date <= p_invoice_date_to; /* Modified by Ramananda for bug:4071409 */
select
po_header_id,
segment1,
trunc(creation_date) po_date
from po_headers_all
where po_header_id =
( select po_header_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id);
select release_num, release_date
from po_releases_all
where po_release_id in
(
select po_release_id
from po_line_locations_all
where (po_header_id, po_line_id, line_location_id ) in
(
select po_header_id, po_line_id, line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id
)
);
select receipt_num, trunc(creation_date) receipt_date
from rcv_shipment_headers
where shipment_header_id =
( select shipment_header_id
from rcv_transactions
where transaction_id = p_transaction_id);
select distribution_line_number, tax_id
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and parent_invoice_distribution_id = p_parent_distribution_id
and po_distribution_id = p_po_distribution_id
union
select distribution_line_number, tax_id
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and parent_invoice_distribution_id is null
and po_distribution_id is null
and (po_header_id, po_line_id, line_location_id)
in
(
select po_header_id, po_line_id, line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id
);
select upper(tax_type) tax_type
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
select amount
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and distribution_line_number = p_distribution_line_number
and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
and accounting_date <= p_invoice_date_to; /* Modified by Ramananda for bug:4071409 */
select tax_id, upper(tax_type) tax_type, currency, tax_amount
from JAI_RCV_LINE_TAXES
where (shipment_header_id, shipment_line_id)
in
(select shipment_header_id, shipment_line_id
from rcv_transactions
where transaction_id = p_rcv_transaction_id)
and tax_id not in
(
select tax_id
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and parent_invoice_distribution_id = p_parent_distribution_id
and po_distribution_id = p_po_distribution_id
union
select tax_id
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and parent_invoice_distribution_id is null
and po_distribution_id is null
and (po_header_id, po_line_id, line_location_id)
in
(
select po_header_id, po_line_id, line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id
)
)
;
select tax_id, upper(tax_type) tax_type, currency, tax_amount
from JAI_PO_TAXES
where (po_header_id, po_line_id, line_location_id)
in
(select po_header_id, po_line_id, line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id)
and tax_id not in
(
select tax_id
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and parent_invoice_distribution_id = p_parent_distribution_id
and po_distribution_id = p_po_distribution_id
union
select tax_id
from JAI_AP_MATCH_INV_TAXES
where invoice_id = p_invoice_id
and parent_invoice_distribution_id is null
and po_distribution_id is null
and (po_header_id, po_line_id, line_location_id)
in
(
select po_header_id, po_line_id, line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id
)
);
Depending on the input parameter, all invoices are selected.
Taxes that have been already brought over to payable invoice
as 'miscellaneous' distribution lines are considered by their tax
type.
For each line the taxes from the corresponding Receipt / PO are
again considered for any tax that is not brought over to AP. This is
possible as third party taxes and taxes like cvd and customs are not brought
over to AP. These taxes are also grouped by their tax type. These taxes
from purchasing side are checked for apportion factor for changes in Quantity,
Price and UOM for each line. Each tax line's currency is also compared against
invoice currency and is converted to invoice currency if required.
Taxes are grouped as follows,
excise
customs
cvd
cst
lst
freight
octroi
others
2 31/12/2004 Created by Ramananda for bug#4071409. Version#115.1
Issue:-
The report JAINPRRG.rdf calls this procedure jai_ap_rpt_prrg_pkg.process_report.
A set of from and to dates are being passed to this report.Currently the report
picks up the invoices based on these parameters and the details of these
picked up invoices are displayed in the report
Reason:-
Invoice date is checked against the input date parameters to pick the invoices
Fix:-
Accounting date is used against the input date parameters to pick the invoices
Dependency due to this bug:-
None
Future Dependencies For the release Of this Object:-
==================================================
Please add a row in the section below only if your bug introduces a dependency
like,spec change/ A new call to a object/A datamodel change.
--------------------------------------------------------------------------------
Version Bug Dependencies (including other objects like files if any)
--------------------------------------------------------------------------------
115.0 3633078 Datamodel dependencies
--------------------------------------------------------------------------------- */
-- get the run_no
v_statement_id:= 1;
for c_inv_select_rec in c_inv_select_cursor('ITEM') loop--rchandan for bug#4428980
v_statement_id:= 3;
for c_item_lines_rec in c_inv_item_lines(c_inv_select_rec.invoice_id,'ITEM') loop
v_statement_id:= 4;
c_inv_select_rec.invoice_id,
c_item_lines_rec.invoice_distribution_id,
c_item_lines_rec.po_distribution_id)
loop
v_statement_id:= 8;
(c_inv_select_rec.invoice_id, c_get_tax_from_ap_rec.distribution_line_number);
v_conversion_factor := jai_ap_utils_pkg.get_apportion_factor(c_inv_select_rec.invoice_id,c_item_lines_rec.invoice_line_number);
c_inv_select_rec.invoice_id,
c_item_lines_rec.invoice_distribution_id,
c_item_lines_rec.po_distribution_id,
c_item_lines_rec.rcv_transaction_id
)
loop
v_statement_id:= 13;
if c_inv_select_rec.invoice_currency_code <> c_receipt_tax_rec.currency then
v_tax_amt := v_tax_amt / c_inv_select_rec.exchange_rate;
c_inv_select_rec.invoice_id,
c_item_lines_rec.invoice_distribution_id,
c_item_lines_rec.po_distribution_id,
c_item_lines_rec.rcv_transaction_id
)
loop
v_statement_id:= 16;
if c_inv_select_rec.invoice_currency_code <> c_get_tax_from_po_rec.currency then
v_tax_amt := v_tax_amt / c_inv_select_rec.exchange_rate;
insert into JAI_PO_REP_PRRG_T
(
run_no,
org_id,
vendor_id,
vendor_site_id,
invoice_id,
invoice_num,
invoice_date,
invoice_currency_code,
exchange_rate,
voucher_num,
distribution_line_number,
po_number,
po_header_id,
po_creation_date,
po_distribution_id,
po_release_num,
receipt_number,
receipt_date,
rcv_transaction_id,
line_amount,
excise,
customs,
cvd,
cst,
lst,
freight,
octroi,
others,
-- added, Harshita for Bug 4866533
created_by,
creation_date,
last_updated_by,
last_update_date
)
values
(
v_run_no,
c_inv_select_rec.org_id ,
c_inv_select_rec.vendor_id,
c_inv_select_rec.vendor_site_id,
c_inv_select_rec.invoice_id,
c_inv_select_rec.invoice_num,
c_inv_select_rec.invoice_date,
c_inv_select_rec.invoice_currency_code,
c_inv_select_rec.exchange_rate,
c_inv_select_rec.voucher_num,
c_item_lines_rec.distribution_line_number,
v_po_number,
v_po_header_id,
nvl(v_po_release_date, v_po_date),
c_item_lines_rec.po_distribution_id,
nvl(v_po_release_num, 0),
v_receipt_num,
v_receipt_date,
c_item_lines_rec.rcv_transaction_id,
c_item_lines_rec.amount,
v_excise_ap + v_excise_po,
v_customs_ap + v_customs_po,
v_cvd_ap + v_cvd_po,
v_cst_ap + v_cst_po,
v_lst_ap + v_lst_po,
v_freight_ap + v_freight_po,
v_octroi_ap + v_octroi_po,
v_others_ap + v_others_po,
-- added, Harshita for Bug 4866533
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
);
end loop;-- c_inv_select_cursor