-
Notifications
You must be signed in to change notification settings - Fork 61
Expand file tree
/
Copy pathAR Open Items Revaluation.sql
More file actions
284 lines (282 loc) · 11.9 KB
/
AR Open Items Revaluation.sql
File metadata and controls
284 lines (282 loc) · 11.9 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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
/*************************************************************************/
/* */
/* (c) 2010-2024 Enginatics GmbH */
/* www.enginatics.com */
/* */
/*************************************************************************/
-- Report Name: AR Open Items Revaluation
-- Description: Imported from BI Publisher
Description: Open Items Revaluation Report
Application: Receivables
Source: Open Items Revaluation Report (XML)
Short Name: ARXINREV_XML
DB package: AR_ARXINREV_XMLP_PKG
-- Excel Examle Output: https://www.enginatics.com/example/ar-open-items-revaluation/
-- Library Link: https://www.enginatics.com/reports/ar-open-items-revaluation/
-- Run Report: https://demo.enginatics.com/
select
trx.c_ledger "Ledger"
, trx.c_balancing "Balancing Segment"
, trx.c_account "Account Segment"
, trx.c_flexdata "Accounting Flexfield"
, trx.c_desc_all "Accounting Flexfield Desc."
&lp_report_format_cols
from
( select
ps.c_ccid
, ps.c_balancing
, ps.c_account
, ps.c_flexdata
, ps.c_cust_name
, ps.c_cust_number
, ps.c_cust_city
, ps.c_trx_id
, ps.c_pay_id
, ps.c_trx_number
, ps.c_inv_type
, ps.c_inv_date
, ps.c_due_date
, ps.c_curr
, ps.c_pay_amount
, ps.c_exchange_rate
, ps.c_type
, ps.c_revaluate_yes_no
, ps.c_inv_gl_posted_date
, ps.c_previous_cust_trx_id
, ps.c_trx_length
, ps.c_ledger
, ps.c_operating_unit
, ps.c_desc_all
--
, ps.c_eop_rate
, ps.c_receiptsformula
, ps.c_adjustformula
, ps.c_cmformula
, ps.c_cm1formula
--
, (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) c_open_orig
, case when ps.p_minimum_accountable_unit is null
then round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate),ps.p_precision)
else round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit
end calc_open_func
--
, case when ps.p_minimum_accountable_unit is null
then round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate),ps.p_precision)
else round(((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit
end calc_eop_amount
--
-- , case
-- when ps.c_eop_rate is null
-- then to_number(null)
-- when ps.c_eop_rate < ps.c_exchange_rate
-- then (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate
-- else (nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate
-- end calc_open_rev
, case when ps.c_eop_rate is null
then
to_number(null)
else
case when ps.p_minimum_accountable_unit is null
then round((((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) - ((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate)),ps.p_precision)
else round((((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_eop_rate) - ((nvl(ps.c_pay_amount,0) - nvl(ps.c_receiptsformula,0) + nvl(ps.c_adjustformula,0) - nvl(ps.c_cmformula,0) + nvl(ps.c_cm1formula,0)) * ps.c_exchange_rate)) / ps.p_minimum_accountable_unit) * ps.p_minimum_accountable_unit
end
end calc_open_rev
from
(select
dist.code_combination_id c_ccid
, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') c_balancing
, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') c_account
, fnd_flex_xml_publisher_apis.process_kff_combination_1('p_flexdata', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'VALUE') c_flexdata
, substrb(party.party_name,1,50) c_cust_name
, cust.account_number c_cust_number
, loc.city c_cust_city
, trx.customer_trx_id c_trx_id
, pay.payment_schedule_id c_pay_id
, ltrim(rtrim(trx.trx_number)) c_trx_number
, typ.name c_inv_type
, trx.trx_date c_inv_date
, pay.due_date c_due_date
, pay.invoice_currency_code c_curr
, pay.amount_due_original c_pay_amount
, nvl(trx.exchange_rate,1.00) c_exchange_rate
, typ.type c_type
, typ.global_attribute1 c_revaluate_yes_no
, dist.gl_posted_date c_inv_gl_posted_date
, nvl(previous_customer_trx_id,0) c_previous_cust_trx_id
, length(ltrim(rtrim(nvl(to_char(doc_sequence_value),trx.trx_number)))) c_trx_length
, sob.name c_ledger
, fc.precision p_precision
, fc.minimum_accountable_unit p_minimum_accountable_unit
, haouv.name c_operating_unit
, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') c_desc_all
--
, case
when pay.invoice_currency_code = sob.currency_code
then 1
when typ.type = 'DEP'
then nvl (trx.exchange_rate,1)
when :p_rate_type_lookup = 'PERIOD'
then (select /*+ push_pred */ decode(tr.eop_rate, 0,0, 1/tr.eop_rate)
from gl_translation_rates tr
where tr.set_of_books_id = sob.set_of_books_id
and tr.to_currency_code = pay.invoice_currency_code
and upper(tr.period_name) = upper(:p_revaluation_period)
and tr.actual_flag = 'A'
)
else case
when gl_currency_api.get_rate_sql(pay.invoice_currency_code,sob.currency_code,:p_rate_date,:p_daily_rate_type) < 0
then null
else gl_currency_api.get_rate_sql(pay.invoice_currency_code,sob.currency_code,:p_rate_date,:p_daily_rate_type)
end
end c_eop_rate
--
, case when :p_cleared = 'N'
then
(select /*+ push_pred */
sum(nvl(app2.amount_applied,0) + nvl(app2.earned_discount_taken,0) + nvl(app2.unearned_discount_taken,0)) receipt_amt
from
ar_receivable_applications app2
where
app2.applied_payment_schedule_id = pay.payment_schedule_id
and app2.status = 'APP'
and app2.gl_date <= gps.end_date
and app2.application_type='CASH'
and not exists
(select 'reversed'
from ar_cash_receipt_history crh
where app2.cash_receipt_id = crh.cash_receipt_id
and crh.status = 'REVERSED'
and crh.gl_date <= gps.end_date
)
)
else
(select /*+ push_pred */
sum(nvl(app2.amount_applied,0) + nvl(app2.earned_discount_taken,0) + nvl(app2.unearned_discount_taken,0)) receipt_amt
from
ar_receivable_applications app2
where
app2.applied_payment_schedule_id = pay.payment_schedule_id
and app2.status = 'APP'
and app2.gl_date <= gps.end_date
and app2.application_type='CASH'
and exists
(select 'cleared cleared receipt'
from ar_cash_receipt_history crh
where app2.cash_receipt_id = crh.cash_receipt_id
and crh.status = 'CLEARED'
and crh.gl_date <= gps.end_date
and nvl(crh.reversal_gl_date,gps.end_date+1) > gps.end_date
)
)
end c_receiptsformula
--
, (select /*+ push_pred */
sum(adj2.amount) adjustment_amt
from
ar_adjustments adj2
where
adj2.payment_schedule_id = pay.payment_schedule_id
and adj2.gl_date <= gps.end_date
and adj2.status = 'A'
) c_adjustformula
--
, (select /*+ push_pred */
sum(nvl(app2.amount_applied,0)) cm_amt
from
ar_receivable_applications app2
where
app2.applied_payment_schedule_id = pay.payment_schedule_id
and app2.gl_date <= gps.end_date
and app2.status ='APP'
and app2.application_type = 'CM'
) c_cmformula
--
, (select /*+ push_pred */
sum(nvl(app2.amount_applied,0)) cm_amt
from
ar_receivable_applications app2
where
app2.payment_schedule_id = pay.payment_schedule_id
and app2.gl_date <= gps.end_date
and (app2.status ='APP' or (app2.status='ACTIVITY' and app2.applied_payment_schedule_id = -8))
and app2.application_type = 'CM'
) c_cm1formula
from
ra_cust_trx_types typ
, hz_cust_accounts cust
, hz_parties party
, hz_locations loc
, hz_cust_acct_sites acct_site
, hz_party_sites party_site
, hz_cust_site_uses site
, gl_code_combinations cc
, gl_sets_of_books sob
, fnd_currencies fc
, hr_all_organization_units_vl haouv
, ra_customer_trx trx
, ar_xla_ctlgd_lines_v dist
, ar_payment_schedules pay
, (select /*+ push_pred */
gps.set_of_books_id,gps.end_date
from
gl_period_statuses gps
where
gps.application_id = 222
and gps.period_name = :p_revaluation_period
) gps
where
1=1
and pay.gl_date <= gps.end_date
and cust.cust_account_id = pay.customer_id
and cust.party_id = party.party_id
and trx.customer_trx_id = pay.customer_trx_id
and pay.customer_site_use_id = site.site_use_id(+)
and site.cust_acct_site_id = acct_site.cust_acct_site_id(+)
and acct_site.party_site_id = party_site.party_site_id(+)
and party_site.location_id = loc.location_id(+)
and dist.customer_trx_id = trx.customer_trx_id
and dist.account_class = 'REC'
and dist.latest_rec_flag = 'Y'
and dist.account_set_flag = 'N'
and trx.complete_flag = 'Y'
and trx.set_of_books_id = sob.set_of_books_id
and sob.currency_code = fc.currency_code
and trx.org_id = haouv.organization_id
and typ.cust_trx_type_id = pay.cust_trx_type_id
and typ.org_id = pay.org_id
and dist.code_combination_id = cc.code_combination_id
and dist.set_of_books_id = sob.set_of_books_id
and ar_arxinrev_xmlp_pkg.c_daily_rate_lookup_error_p
= 'N'
and gps.set_of_books_id = sob.set_of_books_id
and case when :p_rate_type_lookup = 'DAILY' and (:p_daily_rate_type is null or :p_rate_date is null)
then 'Y' else 'N'
end = 'N'
) ps
order by
c_ledger
, c_balancing
, c_account
, c_flexdata
, c_ccid
, c_cust_name
, c_cust_number
, c_cust_number
, c_trx_length
, c_trx_number
, c_inv_gl_posted_date
, c_revaluate_yes_no
, c_inv_type
, c_inv_date
, c_due_date
, c_trx_id
, c_exchange_rate
, c_type
, c_pay_id
, c_curr
, c_pay_amount
) trx
where
trx.c_open_orig != 0
and (trx.c_type != 'CM' or (trx.c_type = 'CM' and trx.c_previous_cust_trx_id = 0))
&lp_group_by_cols