Scribd
Upload
250 views11 pages

Report SQL 1

This document contains a SQL query that is selecting data from various tables to retrieve invoice and payment information. The query is joining data from tables like ap_invoices_all, ap_invoice_lines_all, ap_invoice_distributions_all, poz_suppliers, and hz_parties to return fields like invoice numbers, amounts, suppliers, dates, and payment details. It is filtering the results based on parameters like supplier, business unit, voucher number, source, and date range. The large nested query is consolidating invoice attachment URLs from different tables.

Uploaded by

balasuk
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
250 views11 pages

Report SQL 1

This document contains a SQL query that is selecting data from various tables to retrieve invoice and payment information. The query is joining data from tables like ap_invoices_all, ap_invoice_lines_all, ap_invoice_distributions_all, poz_suppliers, and hz_parties to return fields like invoice numbers, amounts, suppliers, dates, and payment details. It is filtering the results based on parameters like supplier, business unit, voucher number, source, and date range. The large nested query is consolidating invoice attachment URLs from different tables.

Uploaded by

balasuk
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 11

select

hou.name business_unit,
aia.invoice_id,
aia.invoice_num invoice_number,
aia.source invoice_source_code,
aia.vendor_id,
aia.set_of_books_id,
aia.vendor_site_id,
aia.invoice_currency_code,
aid.amount amount,
aid.base_amount base_amount,
aia.description invoice_description,
ail.description invoice_line_description,
ail.line_type_lookup_code line_type,
ail.quantity_invoiced quantity_invoiced,
ail.attribute1 merchant_name,
aid.amount invoice_distribution_amount,
aid.period_name accounting_period,
to_char(aia.doc_sequence_value) voucher_number,
to_char(aia.invoice_date, 'MM/DD/YYYY') invoice_date,
to_char(aid.accounting_date, 'MM/DD/YYYY') invoice_accounting_date,
to_char(aia.creation_date, 'MM/DD/YYYY') invoice_creation_date,
aia.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG,
ail.stat_amount stat_amount,
nvl(ail.cancelled_flag, 'N') cancelled_flag,
aid.posted_flag posted_flag,
aid.dist_code_combination_id,
pv.segment1 supplier_number,
hp.party_name supplier_Name,
(case
when aia.SOURCE ='CPE' or aia.SOURCE = 'CPI' then
substr(aia.DESCRIPTION, 1,
(INSTR( aia.DESCRIPTION,'|') -1))
else
' '
end) "Coupa_ID" ,
C.URL "Onbase_URL",
(case
when aia.SOURCE ='CPE' then
'https://yrcw.coupahost.com/expense_reports/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1))
when aia.SOURCE = 'CPI' then
'https://yrcw.coupahost.com/invoices/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1 ))
else
' '
end) "Coupa_URL"
FROM
( select B.PK1_VALUE as "PK1_VALUE", LISTAGG( A.url, chr(13) )
WITHIN GROUP (ORDER BY B.PK1_VALUE ) as "URL"
From FND_ATTACHED_DOCUMENTS
B, FND_DOCUMENTS_VL A
where A.DOCUMENT_ATTRIBUTES
is null
AND
A.DOCUMENT_ID = B.DOCUMENT_ID
AND
A.DATATYPE_CODE in ('WEB_PAGE')
GROUP BY B.PK1_VALUE
order by B.PK1_VALUE) C,
poz_suppliers pv,
poz_supplier_sites_all_m sia,
hz_parties hp,
hr_all_organization_units hou,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
where aia.invoice_id = C.PK1_VALUE (+)
and sia.vendor_id = pv.vendor_id
and sia.vendor_site_id = aia.vendor_site_id
and sia.prc_bu_id = hou.organization_id
AND pv.vendor_id = aia.vendor_id
AND pv.party_id = hp.party_id
and (pv.segment1 IN (:supplier_number) or least(:supplier_number) is
null)
and (hou.name IN (:business_unit) or least(:business_unit) is null)
and (aia.doc_sequence_value IN (:voucher_number) or
least(:voucher_number) is null)
and (aia.source IN (:source) or least(:source) is null)
and (aia.invoice_num IN (:invoice_num) or least(:invoice_num) is
null)
AND aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
AND ail.invoice_id = aid.invoice_id
and ((least(:period_start) is null and least(:period_end) is null) or
( trunc(aid.ACCOUNTING_DATE) between to_date('01-'||:period_start ,'dd-mm-yyyy' )
and last_Day(to_date('01-'|| :period_end ,'dd-mm-yyyy' ))))

select
apsa.invoice_id invoice_id,
to_char(ipa.creation_date, 'MM/DD/YYYY') Payment_Creation_date,
to_char(ipa.PAYMENT_DATE, 'MM/DD/YYYY') Paid_date,
aca.PAYMENT_METHOD_CODE "payment_method_code",
ipa.PAYMENT_ID "Payment ID",
aca.check_number,
aca.status_lookup_code payment_status
from
iby_payments_all ipa,
ap_checks_all aca,
AP_PAYMENT_SCHEDULES_ALL apsa,
ap_invoice_payments_all aipa
Where
ipa.payment_id = aca.payment_id
AND aca.check_id = aipa.check_id
and apsa.PAYMENT_NUM = aipa.PAYMENT_NUM
AND apsa.invoice_id = aipa.invoice_id
and aipa.REVERSAL_INV_PMT_ID is null
and aipa.REVERSAL_FLAG is null

SELECT M.voucher_number voucher_number,


M.invoice_number invoice_number,
M.invoice_date invoice_date,
M.invoice_accounting_date invoice_accounting_date,
M.invoice_creation_date invoice_creation_date,
M.supplier_number supplier_number,
M.supplier_name supplier,
M.accounting_period accounting_period,
gcc.segment1 company_code,
gcc.segment2 account_code,
d.acctdescription account_description,
--gl_flexfields_pkg.get_description_sql(gcc.CHART_OF_ACCOUNTS_ID, 2, gcc.segment2 )

gcc.segment3 function,
gcc.segment4 cost_center,
gcc.segment5 project,
gcc.segment6 intercompany,
M.merchant_name merchant_name,
M.invoice_currency_code,
M.invoice_distribution_amount invoice_distribution_amount,
M.invoice_source_code invoice_source_code,
M.business_unit business_unit,
M.invoice_description invoice_description,
M.invoice_line_description invoice_line_description,
M.line_type line_type,
M.quantity_invoiced quantity_invoiced,
to_char(ipa.creation_date, 'MM/DD/YYYY') Payment_Creation_date,
to_char(ipa.PAYMENT_DATE, 'MM/DD/YYYY') Paid_date,
aca.PAYMENT_METHOD_CODE "payment_method_code",
ipa.PAYMENT_ID "Payment ID",
aca.check_number,
aca.status_lookup_code payment_status,
decode(M.invoice_currency_code, led.currency_code, M.amount,
M.base_amount) base_invoice_dist_amount,
led.currency_code base_currency_code,
M.stat_amount,
M.Coupa_ID "Coupa_ID" ,
M.Onbase_URL "URL",
M.Coupa_URL "Coupa_URL"
FROM
gl_code_combinations gcc,
gl_ledgers led,
ap_checks_all aca,
iby_payments_all ipa,
ap_invoice_payments_all aipa,
AP_PAYMENT_SCHEDULES_ALL apsa,
(select v2.flex_value flex_value, max(v2t.description) acctdescription
from fnd_flex_value_sets s2,
fnd_flex_values v2,
fnd_flex_values_tl v2t
where s2.flex_value_set_id = v2.flex_value_set_id
and v2t.flex_value_id = v2.flex_value_id
and upper(s2.flex_value_set_name) = 'ACCOUNT YRCW'
group by v2.flex_value
) D,
(
select
hou.name business_unit,
aia.invoice_id invoice_id,
aia.invoice_num invoice_number,
aia.source invoice_source_code,
aia.vendor_id vendor_id,
aia.set_of_books_id set_of_books_id,
aia.vendor_site_id vendor_site_id,
aia.invoice_currency_code invoice_currency_code,
aid.amount amount,
aid.base_amount base_amount,
aia.description invoice_description,
ail.description invoice_line_description,
ail.line_type_lookup_code line_type,
ail.quantity_invoiced quantity_invoiced,
ail.attribute1 merchant_name,
aid.amount invoice_distribution_amount,
aid.period_name accounting_period,
to_char(aia.doc_sequence_value) voucher_number,
to_char(aia.invoice_date, 'MM/DD/YYYY') invoice_date,
to_char(aid.accounting_date, 'MM/DD/YYYY') invoice_accounting_date,
to_char(aia.creation_date, 'MM/DD/YYYY') invoice_creation_date,
aia.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG,
ail.stat_amount stat_amount,
nvl(ail.cancelled_flag, 'N') cancelled_flag,
aid.posted_flag posted_flag,
aid.dist_code_combination_id,
pv.segment1 supplier_number,
hp.party_name supplier_Name,
(case
when aia.SOURCE ='CPE' or aia.SOURCE = 'CPI' then
substr(aia.DESCRIPTION, 1,
(INSTR( aia.DESCRIPTION,'|') -1))
else
' '
end) Coupa_ID ,
C.URL Onbase_URL ,
(case
when aia.SOURCE ='CPE' then
'https://yrcw.coupahost.com/expense_reports/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1))
when aia.SOURCE = 'CPI' then
'https://yrcw.coupahost.com/invoices/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1 ))
else
' '
end) Coupa_URL
FROM
( select B.PK1_VALUE as "PK1_VALUE", LISTAGG( A.url, chr(13) )
WITHIN GROUP (ORDER BY B.PK1_VALUE ) as "URL"
From FND_ATTACHED_DOCUMENTS
B, FND_DOCUMENTS_VL A
where A.DOCUMENT_ATTRIBUTES
is null
AND
A.DOCUMENT_ID = B.DOCUMENT_ID
AND
A.DATATYPE_CODE in ('WEB_PAGE')
GROUP BY B.PK1_VALUE
order by B.PK1_VALUE) C,
poz_suppliers pv,
poz_supplier_sites_all_m sia,
hz_parties hp,
hr_all_organization_units hou,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
where aia.invoice_id = C.PK1_VALUE (+)
and sia.vendor_id = pv.vendor_id
and sia.vendor_site_id = aia.vendor_site_id
and sia.prc_bu_id = hou.organization_id
AND pv.vendor_id = aia.vendor_id
AND pv.party_id = hp.party_id
and (pv.segment1 IN (:supplier_number) or least(:supplier_number) is
null)
and (hou.name IN (:business_unit) or least(:business_unit) is null)
and (aia.doc_sequence_value IN (:voucher_number) or
least(:voucher_number) is null)
and (aia.source IN (:source) or least(:source) is null)
and (aia.invoice_num IN (:invoice_num) or least(:invoice_num) is
null)
AND aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
AND ail.invoice_id = aid.invoice_id
and ((least(:period_start) is null and least(:period_end) is null) or
( trunc(aid.ACCOUNTING_DATE) between to_date('01-'||:period_start ,'dd-mm-yyyy' )
and last_Day(to_date('01-'|| :period_end ,'dd-mm-yyyy' ))))
)M
WHERE led.ledger_id = M.set_of_books_id
and apsa.invoice_id = M.invoice_id
and apsa.PAYMENT_NUM = aipa.PAYMENT_NUM
AND ipa.payment_id = aca.payment_id
AND aca.check_id = aipa.check_id
and aipa.REVERSAL_INV_PMT_ID is null
and aipa.REVERSAL_FLAG is null
and aipa.invoice_id = M.invoice_id
and D.flex_value = gcc.segment2
AND gcc.code_combination_id = M.dist_code_combination_id
and (gcc.segment1 IN (:company) or least(:company ) is null)
and (gcc.segment2 IN (:account) or least(:account) is null)
and (gcc.segment3 IN (:function) or least(:function) is null)
and (gcc.segment4 IN (:Cost_Center) or least(:Cost_Center) is null)
and (gcc.segment6 IN (:interComp) or least(:interComp) is null)
and M.cancelled_flag = 'N'
and M.posted_flag = 'Y'
union all
SELECT M.voucher_number voucher_number,
M.invoice_number invoice_number,
M.invoice_date invoice_date,
M.invoice_accounting_date invoice_accounting_date,
M.invoice_creation_date invoice_creation_date,
M.supplier_number supplier_number,
M.supplier_name supplier,
M.accounting_period accounting_period,
gcc.segment1 company_code,
gcc.segment2 account_code,
d.acctdescription account_description,
--gl_flexfields_pkg.get_description_sql(gcc.CHART_OF_ACCOUNTS_ID, 2, gcc.segment2 )

gcc.segment3 function,
gcc.segment4 cost_center,
gcc.segment5 project,
gcc.segment6 intercompany,
M.merchant_name merchant_name,
M.invoice_currency_code,
M.invoice_distribution_amount invoice_distribution_amount,
M.invoice_source_code invoice_source_code,
M.business_unit business_unit,
M.invoice_description invoice_description,
M.invoice_line_description invoice_line_description,
M.line_type line_type,
M.quantity_invoiced quantity_invoiced,
null Payment_Creation_date,
null Paid_date,
null "payment_method_code",
null "Payment ID",
null check_number,
null payment_status,
decode(M.invoice_currency_code, led.currency_code, M.amount,
M.base_amount) base_invoice_dist_amount,
led.currency_code base_currency_code,
M.stat_amount,
M.Coupa_ID "Coupa_ID" ,
M.Onbase_URL "URL",
M.Coupa_URL "Coupa_URL"
FROM
gl_code_combinations gcc,
gl_ledgers led,
(select v2.flex_value flex_value, max(v2t.description) acctdescription
from fnd_flex_value_sets s2,
fnd_flex_values v2,
fnd_flex_values_tl v2t
where s2.flex_value_set_id = v2.flex_value_set_id
and v2t.flex_value_id = v2.flex_value_id
and upper(s2.flex_value_set_name) = 'ACCOUNT YRCW'
group by v2.flex_value
) D,
(
select
hou.name business_unit,
aia.invoice_id invoice_id,
aia.invoice_num invoice_number,
aia.source invoice_source_code,
aia.vendor_id vendor_id,
aia.set_of_books_id set_of_books_id,
aia.vendor_site_id vendor_site_id,
aia.invoice_currency_code invoice_currency_code,
aid.amount amount,
aid.base_amount base_amount,
aia.description invoice_description,
ail.description invoice_line_description,
ail.line_type_lookup_code line_type,
ail.quantity_invoiced quantity_invoiced,
ail.attribute1 merchant_name,
aid.amount invoice_distribution_amount,
aid.period_name accounting_period,
to_char(aia.doc_sequence_value) voucher_number,
to_char(aia.invoice_date, 'MM/DD/YYYY') invoice_date,
to_char(aid.accounting_date, 'MM/DD/YYYY') invoice_accounting_date,
to_char(aia.creation_date, 'MM/DD/YYYY') invoice_creation_date,
aia.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG,
ail.stat_amount stat_amount,
nvl(ail.cancelled_flag, 'N') cancelled_flag,
aid.posted_flag posted_flag,
aid.dist_code_combination_id,
pv.segment1 supplier_number,
hp.party_name supplier_Name,
(case
when aia.SOURCE ='CPE' or aia.SOURCE = 'CPI' then
substr(aia.DESCRIPTION, 1,
(INSTR( aia.DESCRIPTION,'|') -1))
else
' '
end) Coupa_ID ,
C.URL Onbase_URL ,
(case
when aia.SOURCE ='CPE' then
'https://yrcw.coupahost.com/expense_reports/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1))
when aia.SOURCE = 'CPI' then
'https://yrcw.coupahost.com/invoices/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1 ))
else
' '
end) Coupa_URL
FROM
( select B.PK1_VALUE as "PK1_VALUE", LISTAGG( A.url, chr(13) )
WITHIN GROUP (ORDER BY B.PK1_VALUE ) as "URL"
From FND_ATTACHED_DOCUMENTS
B, FND_DOCUMENTS_VL A
where A.DOCUMENT_ATTRIBUTES
is null
AND
A.DOCUMENT_ID = B.DOCUMENT_ID
AND
A.DATATYPE_CODE in ('WEB_PAGE')
GROUP BY B.PK1_VALUE
order by B.PK1_VALUE) C,
poz_suppliers pv,
poz_supplier_sites_all_m sia,
hz_parties hp,
hr_all_organization_units hou,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
where aia.invoice_id = C.PK1_VALUE (+)
and sia.vendor_id = pv.vendor_id
and sia.vendor_site_id = aia.vendor_site_id
and sia.prc_bu_id = hou.organization_id
AND pv.vendor_id = aia.vendor_id
AND pv.party_id = hp.party_id
and (pv.segment1 IN (:supplier_number) or least(:supplier_number) is
null)
and (hou.name IN (:business_unit) or least(:business_unit) is null)
and (aia.doc_sequence_value IN (:voucher_number) or
least(:voucher_number) is null)
and (aia.source IN (:source) or least(:source) is null)
and (aia.invoice_num IN (:invoice_num) or least(:invoice_num) is
null)
AND aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
AND ail.invoice_id = aid.invoice_id
and ((least(:period_start) is null and least(:period_end) is null) or
( trunc(aid.ACCOUNTING_DATE) between to_date('01-'||:period_start ,'dd-mm-yyyy' )
and last_Day(to_date('01-'|| :period_end ,'dd-mm-yyyy' ))))
)M
WHERE led.ledger_id = M.set_of_books_id
and D.flex_value = gcc.segment2
AND gcc.code_combination_id = M.dist_code_combination_id
and (gcc.segment1 IN (:company) or least(:company ) is null)
and (gcc.segment2 IN (:account) or least(:account) is null)
and (gcc.segment3 IN (:function) or least(:function) is null)
and (gcc.segment4 IN (:Cost_Center) or least(:Cost_Center) is null)
and (gcc.segment6 IN (:interComp) or least(:interComp) is null)
and M.PAYMENT_STATUS_FLAG = 'N'
and M.cancelled_flag = 'N'
and M.posted_flag = 'Y'
order by account_code, supplier_number, invoice_number

SELECT M.voucher_number voucher_number,


M.invoice_number invoice_number,
M.invoice_date invoice_date,
M.invoice_accounting_date invoice_accounting_date,
M.invoice_creation_date invoice_creation_date,
M.supplier_number supplier_number,
M.supplier_name supplier,
M.accounting_period accounting_period,
gcc.segment1 company_code,
gcc.segment2 account_code,
d.acctdescription account_description,
--gl_flexfields_pkg.get_description_sql(gcc.CHART_OF_ACCOUNTS_ID, 2, gcc.segment2 )

gcc.segment3 function,
gcc.segment4 cost_center,
gcc.segment5 project,
gcc.segment6 intercompany,
M.merchant_name merchant_name,
M.invoice_currency_code,
M.invoice_distribution_amount invoice_distribution_amount,
M.invoice_source_code invoice_source_code,
M.business_unit business_unit,
M.invoice_description invoice_description,
M.invoice_line_description invoice_line_description,
M.line_type line_type,
M.quantity_invoiced quantity_invoiced,
N.Payment_Creation_date Payment_Creation_date,
N.Paid_date Paid_date,
N.PAYMENT_METHOD_CODE "payment_method_code",
N.PAYMENT_ID "Payment ID",
N.check_number check_number,
N.payment_status payment_status,
decode(M.invoice_currency_code, led.currency_code, M.amount,
M.base_amount) base_invoice_dist_amount,
led.currency_code base_currency_code,
M.stat_amount,
M.Coupa_ID "Coupa_ID" ,
M.Onbase_URL "URL",
M.Coupa_URL "Coupa_URL"
FROM
gl_code_combinations gcc,
gl_ledgers led,

(select v2.flex_value flex_value, max(v2t.description) acctdescription


from fnd_flex_value_sets s2,
fnd_flex_values v2,
fnd_flex_values_tl v2t
where s2.flex_value_set_id = v2.flex_value_set_id
and v2t.flex_value_id = v2.flex_value_id
and upper(s2.flex_value_set_name) = 'ACCOUNT YRCW'
group by v2.flex_value
) D,
(
select
hou.name business_unit,
aia.invoice_id invoice_id,
aia.invoice_num invoice_number,
aia.source invoice_source_code,
aia.vendor_id vendor_id,
aia.set_of_books_id set_of_books_id,
aia.vendor_site_id vendor_site_id,
aia.invoice_currency_code invoice_currency_code,
aid.amount amount,
aid.base_amount base_amount,
aia.description invoice_description,
ail.description invoice_line_description,
ail.line_type_lookup_code line_type,
ail.quantity_invoiced quantity_invoiced,
ail.attribute1 merchant_name,
aid.amount invoice_distribution_amount,
aid.period_name accounting_period,
to_char(aia.doc_sequence_value) voucher_number,
to_char(aia.invoice_date, 'MM/DD/YYYY') invoice_date,
to_char(aid.accounting_date, 'MM/DD/YYYY') invoice_accounting_date,
to_char(aia.creation_date, 'MM/DD/YYYY') invoice_creation_date,
aia.PAYMENT_STATUS_FLAG PAYMENT_STATUS_FLAG,
ail.stat_amount stat_amount,
nvl(ail.cancelled_flag, 'N') cancelled_flag,
aid.posted_flag posted_flag,
aid.dist_code_combination_id,
pv.segment1 supplier_number,
hp.party_name supplier_Name,
(case
when aia.SOURCE ='CPE' or aia.SOURCE = 'CPI' then
substr(aia.DESCRIPTION, 1,
(INSTR( aia.DESCRIPTION,'|') -1))
else
' '
end) Coupa_ID ,
C.URL Onbase_URL ,
(case
when aia.SOURCE ='CPE' then
'https://yrcw.coupahost.com/expense_reports/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1))
when aia.SOURCE = 'CPI' then
'https://yrcw.coupahost.com/invoices/' ||
substr(aia.DESCRIPTION, 1,(INSTR( aia.DESCRIPTION,'|') -1 ))
else
' '
end) Coupa_URL
FROM
( select B.PK1_VALUE as "PK1_VALUE", LISTAGG( A.url, chr(13) )
WITHIN GROUP (ORDER BY B.PK1_VALUE ) as "URL"
From FND_ATTACHED_DOCUMENTS
B, FND_DOCUMENTS_VL A
where A.DOCUMENT_ATTRIBUTES
is null
AND
A.DOCUMENT_ID = B.DOCUMENT_ID
AND
A.DATATYPE_CODE in ('WEB_PAGE')
GROUP BY B.PK1_VALUE
order by B.PK1_VALUE) C,
poz_suppliers pv,
poz_supplier_sites_all_m sia,
hz_parties hp,
hr_all_organization_units hou,
ap_invoices_all aia,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
where aia.invoice_id = C.PK1_VALUE (+)
and sia.vendor_id = pv.vendor_id
and sia.vendor_site_id = aia.vendor_site_id
and sia.prc_bu_id = hou.organization_id
AND pv.vendor_id = aia.vendor_id
AND pv.party_id = hp.party_id
and (pv.segment1 IN (:supplier_number) or least(:supplier_number) is
null)
and (hou.name IN (:business_unit) or least(:business_unit) is null)
and (aia.doc_sequence_value IN (:voucher_number) or
least(:voucher_number) is null)
and (aia.source IN (:source) or least(:source) is null)
and (aia.invoice_num IN (:invoice_num) or least(:invoice_num) is
null)
AND aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
AND ail.invoice_id = aid.invoice_id
and ((least(:period_start) is null and least(:period_end) is null) or
( trunc(aid.ACCOUNTING_DATE) between to_date('01-'||:period_start ,'dd-mm-yyyy' )
and last_Day(to_date('01-'|| :period_end ,'dd-mm-yyyy' ))))
)M,
(select
apsa.invoice_id invoice_id,
to_char(ipa.creation_date, 'MM/DD/YYYY') Payment_Creation_date,
to_char(ipa.PAYMENT_DATE, 'MM/DD/YYYY') Paid_date,
aca.PAYMENT_METHOD_CODE payment_method_code,
ipa.PAYMENT_ID PAYMENT_ID,
aca.check_number,
aca.status_lookup_code payment_status
from
iby_payments_all ipa,
ap_checks_all aca,
AP_PAYMENT_SCHEDULES_ALL apsa,
ap_invoice_payments_all aipa
Where
ipa.payment_id = aca.payment_id
AND aca.check_id = aipa.check_id
and apsa.PAYMENT_NUM = aipa.PAYMENT_NUM
AND apsa.invoice_id = aipa.invoice_id
and aipa.REVERSAL_INV_PMT_ID is null
and aipa.REVERSAL_FLAG is null) N
WHERE led.ledger_id = M.set_of_books_id
and N.invoice_id (+) = M.invoice_id
and D.flex_value = gcc.segment2
AND gcc.code_combination_id = M.dist_code_combination_id
and (gcc.segment1 IN (:company) or least(:company ) is null)
and (gcc.segment2 IN (:account) or least(:account) is null)
and (gcc.segment3 IN (:function) or least(:function) is null)
and (gcc.segment4 IN (:Cost_Center) or least(:Cost_Center) is null)
and (gcc.segment6 IN (:interComp) or least(:interComp) is null)
and M.cancelled_flag = 'N'
and M.posted_flag = 'Y'
order by account_code, supplier_number, invoice_number

You might also like