-- Changed references to the table ap_payment_distributions to use ap_ae_lines
SELECT
/* the Trigger in the Repeating frame eliminates fully paid transactions; */
&ACCT_FLEX_BAL_SEG C_BALANCING,
&ACCT_FLEX_ACC_SEG C_ACCOUNT,
&P_FLEXDATA C_FLEXDATA,
i.invoice_type_lookup_code C_INVOICE_TYPE,
alc.displayed_field C_INVOICE_TYPE_LOOKUP,
i.invoice_num C_INVOICE_NUM,
i.invoice_currency_code C_CURRENCY_CODE,
i.invoice_date C_INVOICE_DATE,
pv.vendor_name C_VENDOR_NAME,
pv.segment1 C_VENDOR_NUMBER,
pvs.vendor_site_code C_VENDOR_SITE,
sum(nvl(aid.amount,0)) C_OP_ORIGINAL_AMOUNT,
(nvl(i.exchange_rate,0)) C_EXCHANGE_RATE,
sum(nvl(aid.amount,0)) C_OP_FUNCTIONAL_AMOUNT,
sum(nvl(aid.amount,0)) C_OP_REVAL_AMOUNT,
i.invoice_id C_INVOICE_ID
FROM
ap_invoice_distributions aid,
ap_invoices i,
gl_code_combinations glcc,
ap_lookup_codes alc,
po_vendors pv,
po_vendor_sites pvs
WHERE
--and i.invoice_currency_code <> :C_BASE_CURRENCY_CODE
-- if you want only open items in foreign currency
aid.invoice_id = i.invoice_id
&C_DATE2_FLAG
and i.accts_pay_code_combination_id = glcc.code_combination_id (+)
and i.invoice_type_lookup_code = alc.lookup_code
and alc.lookup_type = 'INVOICE TYPE'
&lp_bal_segment_low
&lp_bal_segment_high
and i.vendor_id = pv.vendor_id
and i.vendor_site_id = pvs.vendor_site_id
and pv.vendor_id = pvs.vendor_id
and :P_TRNS_TO_GL = 'N'
and aid.match_status_flag = 'A' -- invoice is approved
-- &LP_CLEARED
-- GROUP BY 1,2,3,5,6,7,8,12,16,9,10,11,4
GROUP BY
&ACCT_FLEX_BAL_SEG,
&ACCT_FLEX_ACC_SEG,
&P_FLEXDATA,
alc.displayed_field,
i.invoice_num,
i.invoice_currency_code,
i.invoice_date,
i.freight_amount,
i.exchange_rate,
i.invoice_id,
pv.vendor_name,
pv.segment1,
pvs.vendor_site_code,
i.invoice_type_lookup_code
UNION
SELECT
/* Select all payments which has gl date <= Revaluation Period, but the associated invoice has gl date >= Revaluation Period */
&ACCT_FLEX_BAL_SEG C_BALANCING,
&ACCT_FLEX_ACC_SEG C_ACCOUNT,
&P_FLEXDATA C_FLEXDATA,
'Payment' C_INVOICE_TYPE,
NULL C_INVOICE_TYPE_LOOKUP,
to_char(ac.check_number) C_INVOICE_NUM,
ac.currency_code C_CURRENCY_CODE,
ac.check_date C_INVOICE_DATE,
pv.vendor_name C_VENDOR_NAME,
pv.segment1 C_VENDOR_NUMBER,
pvs.vendor_site_code C_VENDOR_SITE,
Sum(nvl(aip.amount,0) * -1) C_OP_ORIGINAL_AMOUNT,
(nvl(i.exchange_rate,0)) C_EXCHANGE_RATE,
Sum(nvl(aip.amount,0) * -1) C_OP_FUNCTIONAL_AMOUNT,
Sum(nvl(aip.amount,0) * -1) C_OP_REVAL_AMOUNT,
--Bug1936235
--i.invoice_id C_INVOICE_ID
ac.check_id C_INVOICE_ID
FROM
ap_checks ac,
ap_invoices i,
ap_invoice_payments aip,
gl_code_combinations glcc,
po_vendors pv,
po_vendor_sites pvs
WHERE
--and i.invoice_currency_code <> :C_BASE_CURRENCY_CODE
-- if you want only open items in foreign currency
aip.check_id = ac.check_id
and aip.invoice_id = i.invoice_id
and aip.accounting_date <= :C_ACC_PERIOD_END_DATE
and aip.accounting_date >= :C_ACC_PERIOD_END_DATE
and i.accts_pay_code_combination_id = glcc.code_combination_id (+)
&lp_bal_segment_low
&lp_bal_segment_high
and i.vendor_id = pv.vendor_id
and i.vendor_site_id = pvs.vendor_site_id
and pv.vendor_id = pvs.vendor_id
and :P_TRNS_TO_GL = 'N'
&LP_CLEARED
GROUP BY
&ACCT_FLEX_BAL_SEG,
&ACCT_FLEX_ACC_SEG,
&P_FLEXDATA,
ac.check_number,
ac.currency_code,
ac.check_date,
i.exchange_rate,
--Bug1936235
--i.invoice_id,
ac.check_id,
pv.vendor_name,
pv.segment1,
pvs.vendor_site_code
--Bug1936235
--i.invoice_type_lookup_code,
--aip.amount
-- ORDER BY 1,3,4,6,7,8,9,10
/* The two selects below are only executed if the user wants transactions */
/* that are posted to GL they use the ap subledger accountig tables */
UNION
SELECT
/* the Trigger in the Repeating frame eliminates fully paid transactions; */
&ACCT_FLEX_BAL_SEG C_BALANCING,
&ACCT_FLEX_ACC_SEG C_ACCOUNT,
&P_FLEXDATA C_FLEXDATA,
i.invoice_type_lookup_code C_INVOICE_TYPE,
alc.displayed_field C_INVOICE_TYPE_LOOKUP,
i.invoice_num C_INVOICE_NUM,
i.invoice_currency_code C_CURRENCY_CODE,
i.invoice_date C_INVOICE_DATE,
pv.vendor_name C_VENDOR_NAME,
pv.segment1 C_VENDOR_NUMBER,
pvs.vendor_site_code C_VENDOR_SITE,
sum(nvl(nvl(ael.entered_dr * -1,ael.entered_cr),0)) C_OP_ORIGINAL_AMOUNT,
ael.currency_conversion_rate C_EXCHANGE_RATE,
sum(nvl(nvl(ael.entered_dr * -1,ael.entered_cr),0)) C_OP_FUNCTIONAL_AMOUNT,
sum(nvl(nvl(ael.entered_dr * -1,ael.entered_cr),0)) C_OP_REVAL_AMOUNT,
ael.source_id C_INVOICE_ID
FROM
--ap_invoice_distributions aid,
ap_invoices i,
gl_code_combinations glcc,
ap_lookup_codes alc,
po_vendors pv,
po_vendor_sites pvs,
ap_ae_lines ael,
ap_ae_headers aeh
WHERE
--and i.invoice_currency_code <> :C_BASE_CURRENCY_CODE -- if you want only open items in foreign currency
--aid.invoice_id = i.invoice_id
i.accts_pay_code_combination_id = glcc.code_combination_id (+)
and aeh.accounting_date <= :C_ACC_PERIOD_END_DATE
and alc.lookup_type = 'INVOICE TYPE'
and i.invoice_type_lookup_code = alc.lookup_code
&lp_bal_segment_low
&lp_bal_segment_high
and ael.third_party_id = pv.vendor_id
and ael.third_party_sub_id = pvs.vendor_site_id
and pv.vendor_id = pvs.vendor_id
and upper(ael.source_table) = 'AP_INVOICES'
and ael.source_id = i.invoice_id
and ael.ae_header_id = aeh.ae_header_id
and aeh.gl_transfer_flag = 'Y'
and :P_TRNS_TO_GL = 'Y'
--Bug1818396
and aeh.set_of_books_id = :P_SET_OF_BOOKS_ID
--&LP_CLEARED
GROUP BY
&ACCT_FLEX_BAL_SEG,
&ACCT_FLEX_ACC_SEG,
&P_FLEXDATA,
alc.displayed_field,
i.invoice_num,
i.invoice_currency_code,
i.invoice_date,
i.freight_amount,
ael.currency_conversion_rate,
ael.source_id,
pv.vendor_name,
pv.segment1,
pvs.vendor_site_code,
i.invoice_type_lookup_code
--nvl(nvl(ael.entered_dr * -1,ael.entered_cr),0)
UNION
SELECT
/* Select all payments which has gl date <= Revaluation Period, but the associated invoice has gl date >= Revaluation Period */
&ACCT_FLEX_BAL_SEG C_BALANCING,
&ACCT_FLEX_ACC_SEG C_ACCOUNT,
&P_FLEXDATA C_FLEXDATA,
'Payment' C_INVOICE_TYPE,
NULL C_INVOICE_TYPE_LOOKUP,
to_char(ac.check_number) C_INVOICE_NUM,
ac.currency_code C_CURRENCY_CODE,
ac.check_date C_INVOICE_DATE,
pv.vendor_name C_VENDOR_NAME,
pv.segment1 C_VENDOR_NUMBER,
pvs.vendor_site_code C_VENDOR_SITE,
Sum(nvl(nvl(ael.entered_dr,ael.entered_cr),0) * -1) C_OP_ORIGINAL_AMOUNT,
nvl(ael.currency_conversion_rate,0) C_EXCHANGE_RATE,
Sum(nvl(nvl(ael.entered_dr,ael.entered_cr),0) * -1) C_OP_FUNCTIONAL_AMOUNT,
Sum(nvl(nvl(ael.entered_dr,ael.entered_cr),0) * -1) C_OP_REVAL_AMOUNT,
--Bug1936235
--i.invoice_id C_INVOICE_ID
ac.check_id C_INVOICE_ID
FROM
ap_checks ac,
ap_invoices i,
ap_invoice_payments aip,
gl_code_combinations glcc,
po_vendors pv,
po_vendor_sites pvs,
ap_ae_lines ael,
ap_ae_headers aeh
WHERE
--and i.invoice_currency_code <> :C_BASE_CURRENCY_CODE
--if you want only open items in foreign currency
aip.check_id = ac.check_id
and aip.invoice_id = i.invoice_id
and aeh.accounting_date <= :C_ACC_PERIOD_END_DATE
and i.accts_pay_code_combination_id = glcc.code_combination_id (+)
/* bug 1732033 adding the check for invoice gl_date See Bug for Details*/
and i.gl_date > :C_ACC_PERIOD_END_DATE
&lp_bal_segment_low
&lp_bal_segment_high
and ael.third_party_id = pv.vendor_id
and ael.third_party_sub_id = pvs.vendor_site_id
and pv.vendor_id = pvs.vendor_id
and upper(ael.source_table) = 'AP_CHECKS'
--bug1905853 Commenting the following line as the source_id should be compared with check_id
--and ael.source_id = i.invoice_id
and ael.source_id = ac.check_id
and ael.ae_header_id = aeh.ae_header_id
and aeh.gl_transfer_flag = 'Y'
and :P_TRNS_TO_GL = 'Y'
--Bug1818396
and aeh.set_of_books_id = :P_SET_OF_BOOKS_ID
&LP_CLEARED
GROUP BY
&ACCT_FLEX_BAL_SEG,
&ACCT_FLEX_ACC_SEG,
&P_FLEXDATA,
ac.check_number,
ac.currency_code,
ac.check_date,
ael.currency_conversion_rate,
--Bug1936235
--i.invoice_id,
ac.check_id,
pv.vendor_name,
pv.segment1,
pvs.vendor_site_code
--Bug1936235
--i.invoice_type_lookup_code,
--nvl(nvl(ael.entered_dr,ael.entered_cr),0)
-- ORDER BY 1,3,4,6,7,8,9,10