with xtb as (
select /*+ parallel(xtb) leading(xtb) no_merge */
xtb.definition_code tb_code,
xtdv.name tb_name,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
xtb.code_combination_id,
xtb.source_application_id,
sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_unrounded_cr,0) - nvl(xtb.entered_unrounded_dr,0))) entered_unrounded_orig_amount,
sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0) - nvl(xtb.entered_rounded_dr,0))) entered_rounded_orig_amount,
sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_unrounded_cr,0) - nvl(xtb.acctd_unrounded_dr,0))) acctd_unrounded_orig_amount,
sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0) - nvl(xtb.acctd_rounded_dr,0)) ) acctd_rounded_orig_amount,
sum (nvl(xtb.entered_unrounded_cr,0)) - sum(nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
sum (nvl(xtb.entered_rounded_cr,0)) - sum(nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,
sum (nvl(xtb.acctd_unrounded_cr,0)) - sum(nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,
sum (nvl(xtb.acctd_rounded_cr,0)) - sum(nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,
case when sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0) - nvl(xtb.entered_rounded_dr,0)))=0 then 1
else
sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0) - nvl(xtb.acctd_rounded_dr,0)) ) /
sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0) - nvl(xtb.entered_rounded_dr,0)))
end conversion_rate,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value,
dte.as_of_date
from
xla_trial_balances xtb,
xla_tb_definitions_vl xtdv,
(
select
gl.ledger_id,
gl.name ledger_name,
:p_as_of_date as_of_date
from
gl_ledgers gl
where
:p_as_of_date is not null and
(
nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N' or
fnd_profile.value('XLA_USE_LEDGER_SECURITY')='Y' and
gl.ledger_id in (
select
gasa.ledger_id
from
gl_access_sets gas,
gl_access_set_assignments gasa
where
gas.access_set_id=gasa.access_set_id and
(
gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or
gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1')
)
)
)
union
select
x.ledger_id,
x.name ledger_name,
x.end_date as_of_date
from
(
select
1-rank() over (partition by gl.name order by ((glp.period_year * 100000) + glp.period_num) desc) relative_period,
gl.ledger_id,
gl.name,
glp.end_date
from
gl_ledgers gl,
gl_periods glp,
gl_periods glpc
where
:p_as_of_date is null and
:p_relative_period is not null and
(
nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N' or
fnd_profile.value('XLA_USE_LEDGER_SECURITY')='Y' and
gl.ledger_id in (
select
gasa.ledger_id
from
gl_access_sets gas,
gl_access_set_assignments gasa
where
gas.access_set_id=gasa.access_set_id and
(
gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or
gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1')
)
)
) and
gl.period_set_name=glp.period_set_name and
gl.accounted_period_type=glp.period_type and
glp.adjustment_period_flag='N' and
gl.period_set_name=glpc.period_set_name and
gl.accounted_period_type=glpc.period_type and
glpc.adjustment_period_flag='N' and
trunc(sysdate) between glpc.start_date and glpc.end_date and
glp.start_date<=glpc.start_date
) x
where
x.relative_period=to_number(:p_relative_period)
) dte
where
2=2 and
xtdv.definition_code in
(
select
xtd.definition_code
from
xla_tb_defn_je_sources xtd,
xla_subledgers xs
where
xtd.je_source_name=xs.je_source_name and
xs.application_id=200
) and
xtb.ledger_id=dte.ledger_id and
xtb.definition_code=xtdv.definition_code and
xtb.source_application_id=200 and
xtb.gl_date between to_date('01/01/1950','DD/MM/YYYY') and dte.as_of_date and
xtdv.enabled_flag='Y'
group by
xtb.definition_code,
xtdv.name,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id),
xtb.code_combination_id ,
xtb.source_application_id,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value,
dte.as_of_date
having
sum(nvl(xtb.acctd_rounded_cr,0)) <> sum (nvl(xtb.acctd_rounded_dr,0))
),
ap_inv as (
select
xtb.as_of_date,
xtb.tb_code,
xtb.tb_name,
xtb.ledger_id,
gl.name ledger_name,
gl.short_name ledger_short_name,
gl.currency_code ledger_currency,
haouv.name operating_unit,
xtb.source_application_id,
xtb.entity_id source_entity_id,
xte.entity_code source_entity_code,
xte.security_id_int_1 org_id,
xte.transaction_number source_transaction_number,
xetv.name source_trx_type,
xtb.code_combination_id,
xtb.entered_unrounded_orig_amount,
xtb.entered_rounded_orig_amount,
xtb.acctd_unrounded_orig_amount,
xtb.acctd_rounded_orig_amount,
xtb.entered_unrounded_rem_amount,
xtb.entered_rounded_rem_amount,
xtb.acctd_unrounded_rem_amount,
xtb.acctd_rounded_rem_amount,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value,
xtb.entity_id applied_to_entity_id,
xtb.party_id third_party_number,
hp.party_name third_party_name,
hps.party_site_name third_party_site_name,
ftv2.territory_short_name third_party_site_country,
aps.segment1 vendor_number,
aps.vendor_name,
xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) supplier_type,
assa.vendor_site_code vendor_site,
ftv1.territory_short_name vendor_site_country,
aia.invoice_num invoice_number,
aia.doc_sequence_value invoice_document_number,
aia.invoice_date,
aia.gl_date invoice_gl_date,
aia.cancelled_date invoice_cancelled_date,
aia.source invoice_source,
xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type,
aia.description invoice_description,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status,
xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200) invoice_payment_status,
xxen_util.meaning(aia.pay_group_lookup_code,'PAY GROUP',201) pay_group,
(select ipmv.payment_method_name from iby_payment_methods_vl ipmv where ipmv.payment_method_code=aia.payment_method_code) payment_method,
aia.dispute_reason,
apsa.iby_hold_reason payment_hold_reason,
aia.invoice_currency_code invoice_currency,
aia.invoice_amount,
nvl(aia.base_amount,aia.invoice_amount) invoice_amount_functional,
(select atv.name from ap_terms_vl atv where aia.terms_id=atv.term_id) payment_terms,
(select sum(ail.amount) from ap_invoice_lines_all ail where ail.invoice_id=aia.invoice_id and ail.line_type_lookup_code='TAX') invoice_tax_amount,
(select listagg(x.tax_classification_code,', ') within group (order by x.tax_classification_code) from (select distinct ail.invoice_id, ail.tax_classification_code from ap_invoice_lines_all ail where ail.tax_classification_code is not null) x where x.invoice_id=aia.invoice_id) tax_classification_code,
(select rtrim(xmlagg(xmlelement(e,pha.segment1||','||chr(10)) order by pha.segment1).extract('//text()').getclobval(),','||chr(10)) from (select distinct ail.invoice_id, ail.po_header_id from ap_invoice_lines_all ail where ail.po_header_id is not null) x, po_headers_all pha where x.invoice_id=aia.invoice_id and x.po_header_id=pha.po_header_id) po_number,
case when aia.invoice_currency_code<>gl.currency_code then aia.exchange_rate end invoice_exchange_rate,
case when aia.invoice_currency_code<>gl.currency_code then (select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type=aia.exchange_rate_type) end invoice_exchange_rate_type,
case when aia.invoice_currency_code<>gl.currency_code then aia.exchange_date end invoice_exchange_rate_date,
aia.invoice_id,
aia.vendor_id,
aia.vendor_site_id,
apsa.payment_num,
apsa.due_date,
ceil(xtb.as_of_date-apsa.due_date) days_due,
case
when xtb.acctd_rounded_rem_amount=0 then 0
when count(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id)=1 then xtb.acctd_rounded_rem_amount
-- multiple payment schedules, but invoice is cancelled or over paid, then allocate to the first payment schedule only
when aia.invoice_amount=0 or sign(xtb.acctd_rounded_rem_amount)<>sign(aia.invoice_amount) then
case when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num) then xtb.acctd_rounded_rem_amount else 0 end
-- multiple paymement schedules. Consume amount remaining from latest payment schedule to first
when nvl(sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),
abs(xtb.acctd_rounded_rem_amount))<=abs(xtb.acctd_rounded_rem_amount)
then case
when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num) -- 1st payment schedule - consume all remaining amount
then sign(xtb.acctd_rounded_rem_amount) * (nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.acctd_rounded_rem_amount)))
when nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),
abs(xtb.acctd_rounded_rem_amount))>=abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))
then sign(xtb.acctd_rounded_rem_amount) * abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))
else sign(xtb.acctd_rounded_rem_amount) * (nvl(abs(xtb.acctd_rounded_rem_amount) - sum(abs(round(apsa.gross_amount*xtb.conversion_rate,fcv.precision))) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.acctd_rounded_rem_amount)))
end
else 0 -- all remaining amount already consumed
end ps_amount_remaining,
case
when xtb.entered_rounded_rem_amount=0 then 0
when count(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id)=1 then xtb.entered_rounded_rem_amount
when aia.invoice_amount=0 or sign(xtb.entered_rounded_rem_amount)<>sign(aia.invoice_amount) then
case when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num) then xtb.entered_rounded_rem_amount else 0 end
when nvl(sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),
abs(xtb.entered_rounded_rem_amount))<=abs(xtb.entered_rounded_rem_amount)
then case
when apsa.payment_num=first_value(apsa.payment_num) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num)
then sign(xtb.entered_rounded_rem_amount) * (nvl(abs(xtb.entered_rounded_rem_amount) - sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.entered_rounded_rem_amount)))
when nvl(abs(xtb.entered_rounded_rem_amount) - sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),
abs(xtb.entered_rounded_rem_amount))>=abs(apsa.gross_amount)
then sign(xtb.entered_rounded_rem_amount) * abs(apsa.gross_amount)
else sign(xtb.entered_rounded_rem_amount) * (nvl(abs(xtb.entered_rounded_rem_amount) - sum(abs(apsa.gross_amount)) over (partition by aia.invoice_id,xtb.tb_code,xtb.code_combination_id order by apsa.payment_num desc rows between unbounded preceding and 1 preceding),abs(xtb.entered_rounded_rem_amount)))
end
else 0
end ps_entered_amt_remaining,
decode(gl.currency_code,:p_reval_currency,1,(select gdr.conversion_rate from gl_daily_conversion_types gdct, gl_daily_rates gdr where gl.currency_code=gdr.from_currency and gdr.to_currency=:p_reval_currency and :p_reval_conv_date=gdr.conversion_date and gdct.user_conversion_type=:p_reval_conv_type and gdct.conversion_type=gdr.conversion_type)) reval_conv_rate,
cbbv.bank_name remit_to_bank_name,
cbbv.bank_number remit_to_bank_number,
cbbv.bank_branch_name remit_to_branch_name,
cbbv.branch_number remit_to_branch_number,
cbbv.country remit_to_branch_country,
ieba.masked_bank_account_num remit_to_account_num,
ieba.masked_iban remit_to_iban,
cbbv.eft_swift_code remit_to_bic,
xxen_util.meaning(ap_invoices_pkg.get_wfapproval_status(aia.invoice_id,aia.org_id),'AP_WFAPPROVAL_STATUS',200) approval_status,
(select count(*)
from
ap_holds_all aha
where
aha.invoice_id=aia.invoice_id and
aha.release_lookup_code is null
) invoice_holds_count,
(select count(*)
from ap_payment_schedules_all apsa
where apsa.invoice_id = aia.invoice_id
and apsa.hold_flag = 'Y'
) scheduled_payment_holds,
xxen_util.yes(assa.hold_future_payments_flag) hold_future_payments,
xxen_util.yes(assa.hold_all_payments_flag) hold_all_payments
from
xtb,
xla_transaction_entities xte,
xla_entity_types_vl xetv,
ap_invoices_all aia,
ap_payment_schedules_all apsa,
ap_suppliers aps,
ap_supplier_sites_all assa,
fnd_territories_vl ftv1,
hz_parties hp,
hz_party_sites hps,
hz_locations hl,
fnd_territories_vl ftv2,
gl_ledgers gl,
fnd_currencies_vl fcv,
hr_all_organization_units_vl haouv,
iby_ext_bank_accounts ieba,
ce_bank_branches_v cbbv
where
xtb.entity_id=xte.entity_id and
xtb.source_application_id=xte.application_id and
xte.entity_code='AP_INVOICES' and
xte.entity_code=xetv.entity_code and
xte.application_id=xetv.application_id and
nvl(xte.source_id_int_1,-99)=aia.invoice_id and
nvl(xte.source_id_int_1,-99)=apsa.invoice_id and
aia.org_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and
aia.vendor_id=aps.vendor_id(+) and
aia.vendor_site_id=assa.vendor_site_id(+) and
assa.country=ftv1.territory_code(+) and
aia.party_id=hp.party_id and
aia.party_site_id=hps.party_site_id(+) and
(aps.employee_id is not null or hps.party_site_id is not null) and
hps.location_id=hl.location_id(+) and
hl.country=ftv2.territory_code(+) and
xtb.ledger_id=gl.ledger_id and
gl.currency_code=fcv.currency_code and
haouv.organization_id=aia.org_id and
apsa.external_bank_account_id=ieba.ext_bank_account_id(+) and
ieba.branch_id=cbbv.branch_party_id(+) and
ieba.bank_id=cbbv.bank_party_id(+)
)
--
-- Main Query Starts Here
--
select
ap_inv.ledger_name,
ap_inv.tb_name trial_balance_name,
ap_inv.ledger_currency,
gcck.concatenated_segments account,
ap_inv.third_party_name,
ap_inv.third_party_number,
ap_inv.third_party_site_name,
ap_inv.third_party_site_country,
ap_inv.vendor_number,
ap_inv.vendor_name,
ap_inv.supplier_type,
ap_inv.vendor_site,
ap_inv.vendor_site_country,
ap_inv.operating_unit,
ap_inv.source_trx_type transaction_type,
ap_inv.source_transaction_number transaction_number,
ap_inv.invoice_document_number,
ap_inv.invoice_date,
ap_inv.invoice_gl_date,
ap_inv.invoice_cancelled_date,
ap_inv.invoice_source,
ap_inv.invoice_type,
ap_inv.invoice_description,
ap_inv.invoice_status,
ap_inv.invoice_payment_status,
ap_inv.approval_status,
ap_inv.invoice_holds_count,
ap_inv.scheduled_payment_holds,
ap_inv.hold_future_payments,
ap_inv.hold_all_payments,
ap_inv.pay_group,
ap_inv.payment_terms,
ap_inv.payment_method,
ap_inv.dispute_reason,
ap_inv.payment_hold_reason,
ap_inv.invoice_currency,
ap_inv.invoice_tax_amount,
ap_inv.tax_classification_code,
ap_inv.po_number,
ap_inv.invoice_exchange_rate,
ap_inv.invoice_exchange_rate_type,
ap_inv.invoice_exchange_rate_date,
case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.invoice_amount end invoice_amount,
case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.invoice_amount_functional end invoice_amount_functional,
case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_orig_amount end transaction_original_amount,
case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.acctd_rounded_rem_amount end transaction_remaining_amount,
case when ap_inv.payment_num=first_value(ap_inv.payment_num) over (partition by ap_inv.invoice_id,ap_inv.tb_code,ap_inv.code_combination_id order by ap_inv.payment_num) then ap_inv.entered_rounded_rem_amount end inv_currency_remaining_amount,
ap_inv.payment_num,
ap_inv.as_of_date,
ap_inv.due_date,
ap_inv.days_due,
&aging_bucket_cols
&reval_columns
ap_inv.balancing_segment_value balancing_segment,
gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_BALANCING',ap_inv.balancing_segment_value) balancing_segment_desc,
ap_inv.natural_account_segment_value account_segment,
gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_ACCOUNT',ap_inv.natural_account_segment_value) account_segment_desc,
ap_inv.cost_center_segment_value cost_center_segment,
gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'FA_COST_CTR',ap_inv.cost_center_segment_value) cost_center_segment_desc,
ap_inv.remit_to_bank_name,
ap_inv.remit_to_bank_number,
ap_inv.remit_to_branch_name,
ap_inv.remit_to_branch_number,
ap_inv.remit_to_branch_country,
ap_inv.remit_to_account_num,
ap_inv.remit_to_iban,
ap_inv.remit_to_bic,
&il_reg_columns
&il_columns_amt
&il_columns_rate
&il_columns_rec
&il_columns_tp
ap_inv.invoice_id
from
ap_inv,
gl_code_combinations_kfv gcck
&il_from
where
1=1 and
ap_inv.code_combination_id=gcck.code_combination_id(+) and
ap_inv.ps_amount_remaining<>0
&il_where
order by
ap_inv.ledger_name,
ap_inv.tb_name,
gcck.concatenated_segments,
ap_inv.third_party_name,
ap_inv.third_party_number,
ap_inv.invoice_gl_date,
ap_inv.invoice_id,
ap_inv.payment_num |
