-- 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