-
Notifications
You must be signed in to change notification settings - Fork 60
Expand file tree
/
Copy pathAP Invoice Payments.sql
More file actions
136 lines (135 loc) · 6.92 KB
/
AP Invoice Payments.sql
File metadata and controls
136 lines (135 loc) · 6.92 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
/*************************************************************************/
/* */
/* (c) 2010-2024 Enginatics GmbH */
/* www.enginatics.com */
/* */
/*************************************************************************/
-- Report Name: AP Invoice Payments
-- Description: Supplier invoice payment details.
There can be multiple payments per invoice and one document/check can be used to pay different payments and invoices. To allow reconciling payment with invoices and checks, invoice and check level amounts are shown on the last payment record only and are blank for multiple/duplicate records.
-- Excel Examle Output: https://www.enginatics.com/example/ap-invoice-payments/
-- Library Link: https://www.enginatics.com/reports/ap-invoice-payments/
-- Run Report: https://demo.enginatics.com/
select
gl.name ledger,
gl.currency_code ledger_currency,
hou.name operating_unit,
aps.vendor_name supplier,
aps.segment1 supplier_number,
xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) supplier_type,
assa.vendor_site_code site_code,
assa.address_line1,
assa.address_line2,
assa.city,
assa.state,
assa.zip,
assa.county,
nvl(ftv.territory_short_name,assa.country) country,
aia.invoice_num invoice_number,
xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type,
aia.description invoice_description,
aia.invoice_date,
aia.gl_date invoice_gl_date,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),aia.invoice_amount) invoice_amount,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),decode(aia.invoice_currency_code,gl.currency_code,aia.invoice_amount,aia.base_amount)) invoice_amount_functional,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),aia.invoice_amount)*decode(aca.currency_code,'USD',1,gdr.conversion_rate) invoice_amount_usd,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),aia.amount_paid) invoice_amount_paid,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),sum(apsa.amount_remaining) over (partition by apsa.invoice_id)) invoice_amount_remaining,
aia.invoice_currency_code invoice_currency,
atv.name payment_term,
apsa.due_date invoice_due_date,
trunc(max(aca.check_date) over (partition by aipa.invoice_id,aipa.payment_num)-apsa.due_date) days_to_pay,
(select ipp.payment_profile_name from iby_payment_profiles ipp where aca.payment_profile_id=ipp.payment_profile_id) ppr_profile,
aisca.checkrun_name ppr_description,
aisca.creation_date ppr_date,
ipmv.payment_method_name payment_method,
aca.currency_code payment_currency,
aca.check_number document_number,
trunc(aca.check_date) payment_date,
aipa.accounting_date accounting_date,
xxen_util.meaning(aca.status_lookup_code,'CHECK STATE',200) check_state,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.amount) check_amount,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),decode(aca.currency_code,gl.currency_code,aca.amount,aca.base_amount)) check_amount_functional,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.amount*decode(aca.currency_code,'USD',1,gdr.conversion_rate)) check_amount_usd,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.cleared_amount) check_cleared_amount,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.cleared_amount*nvl(aca.exchange_rate,1)) check_cleared_amount_funct,
decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.cleared_amount*decode(aca.currency_code,'USD',1,gdr.conversion_rate)) check_cleared_amount_usd,
aipa.amount payment_amount,
decode(aca.currency_code,gl.currency_code,aipa.amount,aipa.payment_base_amount) payment_amount_functional,
aipa.amount*decode(aca.currency_code,'USD',1,gdr.conversion_rate) payment_amount_usd,
aipa.discount_taken,
aipa.discount_lost,
cbbv.bank_name,
cbbv.eft_swift_code swift_code,
cba.bank_account_name,
cba.masked_account_num bank_account_num,
cba.masked_iban iban,
aipa.exchange_date,
nvl(aca.exchange_rate,1) exchange_rate,
aipa.exchange_rate_type,
decode(aipa.reversal_flag,'Y',xxen_util.meaning(aipa.reversal_flag,'YES_NO',0)) reversal_flag,
decode(aipa.assets_addition_flag,'Y',xxen_util.meaning(aipa.reversal_flag,'YES_NO',0)) assets_addition_flag,
gcck.concatenated_segments asset_account,
(select gjb.name from gl_je_batches gjb where aipa.je_batch_id=gjb.je_batch_id) je_batch_name,
count(*) over (partition by aipa.invoice_id) payments_per_invoice,
count(*) over (partition by aipa.check_id) payments_per_check,
(
select
rtrim(xmlagg(xmlelement(e,gcck.concatenated_segments||','||chr(10))).extract('//text()').getclobval(),','||chr(10)) dist_acct
from
(select distinct aida.dist_code_combination_id from ap_invoice_distributions_all aida where aipa.invoice_id=aida.invoice_id) aida,
gl_code_combinations_kfv gcck
where
aida.dist_code_combination_id=gcck.code_combination_id
) distribution_account,
xxen_util.user_name(aipa.created_by) created_by,
xxen_util.client_time(aipa.creation_date) creation_date,
xxen_util.user_name(aipa.last_updated_by) last_updated_by,
xxen_util.client_time(aipa.last_update_date) last_update_date,
aipa.payment_num payment_number,
aipa.invoice_payment_id,
aipa.check_id,
aipa.invoice_id
from
gl_ledgers gl,
hr_operating_units hou,
ap_invoice_payments_all aipa,
ap_checks_all aca,
gl_daily_rates gdr,
ap_invoices_all aia,
ap_suppliers aps,
ap_supplier_sites_all assa,
fnd_territories_vl ftv,
ap_terms_vl atv,
ce_bank_acct_uses_all cbaua,
ce_bank_accounts cba,
ce_bank_branches_v cbbv,
iby_payment_methods_vl ipmv,
gl_code_combinations_kfv gcck,
ap_payment_schedules_all apsa,
ap_inv_selection_criteria_all aisca
where
1=1 and
gl.ledger_id=aipa.set_of_books_id and
hou.organization_id=aipa.org_id and
aipa.check_id=aca.check_id and
aca.currency_code=gdr.from_currency(+) and
gdr.to_currency(+)='USD' and
aca.check_date=gdr.conversion_date(+) and
gdr.conversion_type(+)='Corporate' and
aipa.invoice_id=aia.invoice_id and
aia.vendor_id=aps.vendor_id and
aia.vendor_site_id=assa.vendor_site_id and
assa.country=ftv.territory_code(+) and
aps.terms_id=atv.term_id(+) and
aca.ce_bank_acct_use_id=cbaua.bank_acct_use_id(+) and
cbaua.bank_account_id=cba.bank_account_id(+) and
cba.bank_branch_id=cbbv.branch_party_id(+) and
aca.payment_method_code=ipmv.payment_method_code(+) and
aipa.asset_code_combination_id=gcck.code_combination_id(+) and
aipa.invoice_id=apsa.invoice_id and
aipa.payment_num=apsa.payment_num and
aca.checkrun_id=aisca.checkrun_id (+)
order by
aipa.invoice_id desc,
aipa.invoice_payment_id desc