[Home] [Help]
PACKAGE BODY: APPS.FII_AR_DSO_PKG
Source
1 PACKAGE BODY fii_ar_dso_pkg AS
2 /* $Header: FIIARDBIDSOB.pls 120.27 2007/05/15 20:48:52 vkazhipu ship $ */
3
4 -- Function to return the DSO period for displaying in the parameter portlet
5 FUNCTION get_dso_period_param RETURN VARCHAR2 IS
6 period VARCHAR2(50);
7 BEGIN
8 period := fii_ar_util_pkg.get_dso_period_profile;
9
10 IF period <> -1 THEN
11 period := period || ' Days';
12 END IF;
13
14 RETURN period;
15 END get_dso_period_param;
16
17 -- Function to return the list of Receivables category enabled in the Receivables Setup page
18 FUNCTION get_net_rec_column RETURN VARCHAR2 IS
19 net_rec_col VARCHAR2(500);
20 BEGIN
21 -- Flags from Receivable Setup
22 fii_ar_util_pkg.get_dso_table_values;
23
24 -- Adding columns that are enabled in the Receivables Setup page
25 net_rec_col := '';
26 g_open_rec_column_dso := '';
27 g_open_rec_column_dsot := '';
28 g_hit_rct_aging := 'N';
29 FOR a IN fii_ar_util_pkg.g_dso_table.FIRST..fii_ar_util_pkg.g_dso_table.LAST LOOP
30 CASE
31 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'INV' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
32 net_rec_col := net_rec_col || ' + sum(f.inv_amount)';
33 g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.inv_amount)';
34 g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.inv_amount)';
35 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
36 net_rec_col := net_rec_col || ' + sum(f.dm_amount)';
37 g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.dm_amount)';
38 g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.dm_amount)';
39 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
40 net_rec_col := net_rec_col || ' + sum(f.cb_amount)';
41 g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.cb_amount)';
42 g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.cb_amount)';
43 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'BR' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
44 net_rec_col := net_rec_col || ' + sum(f.br_amount)';
45 g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.br_amount)';
46 g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.br_amount)';
47 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'DEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
48 net_rec_col := net_rec_col || ' + sum(f.dep_amount)';
49 g_open_rec_column_dso := g_open_rec_column_dso || ' + sum(f.dep_amount)';
50 g_open_rec_column_dsot := g_open_rec_column_dsot || ' + sum(inline_query.dep_amount)';
51 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'CM' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
52 net_rec_col := net_rec_col || ' + sum(f.on_account_credit_amount)';
53 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNDEP' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
54 net_rec_col := net_rec_col || ' - sum(f.unapp_dep_amount)';
55 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'UNREC' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
56 g_hit_rct_aging := 'Y';
57 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OACB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
58 net_rec_col := net_rec_col || ' - sum(f.on_account_cash_amount)';
59 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'OCB' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
60 net_rec_col := net_rec_col || ' - sum(f.claim_amount)';
61 WHEN fii_ar_util_pkg.g_dso_table(a).dso_type = 'PREPAY' AND fii_ar_util_pkg.g_dso_table(a).dso_value = 'Y' THEN
62 net_rec_col := net_rec_col || ' - sum(f.prepayment_amount)';
63 ELSE
64 NULL;
65 END CASE;
66 END LOOP;
67
68 -- If none of the category is enabled in setup page, it returns NULL
69 IF net_rec_col = '' THEN
70 net_rec_col := 'NULL';
71 ELSE
72 net_rec_col := '0' || net_rec_col;
73 END IF;
74
75 IF g_open_rec_column_dso IS NOT NULL THEN
76 g_open_rec_column_dso := '0' || g_open_rec_column_dso;
77 END IF;
78
79 IF g_open_rec_column_dsot IS NOT NULL THEN
80 g_open_rec_column_dsot := '0' || g_open_rec_column_dsot;
81 END IF;
82
83 RETURN net_rec_col;
84 END get_net_rec_column;
85
86 PROCEDURE get_dso(
87 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
88 p_dso_sql OUT NOCOPY VARCHAR2,
89 p_dso_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
90 ) IS
91
92 sqlstmt VARCHAR2(20000); -- Variable that stores the final SQL query
93 l_view_by VARCHAR2(240); -- Variable to store the viewby based on viewby selected in the report
94 l_customer_where VARCHAR2(240); -- Variable to store the dynamic customer filter
95 l_customer_acc_where VARCHAR2(240); -- Variable to store the dynamic customer account filter
96 l_industry_where VARCHAR2(240); -- Variable to store the dynamic industry filter
97 l_child_party_where VARCHAR2(240); -- Variable to store the dynamic party id filter
98 l_cust_drill VARCHAR2(1000); -- Variable to store self-drill parameter to view report to explore child nodes
99 l_net_rec_sum_drill VARCHAR2(1000); -- Variable to store the drill to net receivables summary report
100 l_net_rec_column VARCHAR2(1000); -- Variable to store the columns of categories enabled in receivables setup page
101 l_group_by VARCHAR2(240); -- Variable to store the group by clause
102 l_order_by VARCHAR2(240); -- Variable to store the order by clause
103 l_order_column VARCHAR2(100); -- Variable to store the order by column
104 l_unapp_query VARCHAR2(2000); -- Variable to store the query that returns the unapplied amount
105 l_gt_hint varchar2(500);
106
107
108 BEGIN
109
110 -- Clear global parameters AND read the new parameters
111 -- Sets all g_% variables to its default values
112 fii_ar_util_pkg.reset_globals;
113
114 -- Reads the parameters from the parameter portlet
115 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
116
117 -- Populates the security related global temporary tables (fii_ar_summary_gt)
118 fii_ar_util_pkg.populate_summary_gt_tables;
119
120 -- Gets the view by
121 l_view_by := fii_ar_util_pkg.g_view_by;
122
123 -- Adding columns that are enabled in the Receivables Setup page
124 l_net_rec_column := get_net_rec_column;
125
126 l_customer_acc_where := '';
127 l_group_by := '';
128 l_customer_where := '';
129 l_cust_drill := '''''';
130 l_net_rec_sum_drill := 'DECODE(' || g_open_rec_column_dso || ', 0, '''', ''pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'')';
131 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
132
133 IF l_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
134 -- filter for customer account
135 l_customer_acc_where := ' AND f.cust_account_id = v.cust_account_id';
136 l_net_rec_sum_drill := '''''';
137 l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
138 ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
139 -- Defining the group by clause, the filter on parent_party id and customer drill when viewby is Customer
140 l_group_by := ', v.is_self_flag, v.is_leaf_flag';
141 l_customer_where := ' AND f.parent_party_id = v.parent_party_id';
142 l_cust_drill := 'DECODE(v.is_leaf_flag, ''Y'', '''', DECODE(v.is_self_flag, ''Y'', '''', ''pFunctionName=FII_AR_DSO&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY''))';
143 ELSE
144 IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
145 l_net_rec_sum_drill := 'DECODE(' || g_open_rec_column_dso || ', 0, '''', ''pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'')';
146 ELSE
147 l_net_rec_sum_drill := 'DECODE(' || g_open_rec_column_dso || ', 0, '''', ''pFunctionName=FII_AR_NET_REC_SUM&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'')';
148 END IF;
149 END IF;
150
151 -- Defining industry where clause for specific industry or viewby is Industry
152 IF (fii_ar_util_pkg.g_industry_id <> '-111' AND l_view_by <> 'CUSTOMER+FII_CUSTOMERS')
153 OR l_view_by = 'FII_TRADING_PARTNER_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
154 l_industry_where := ' AND v.class_code = f.class_code AND v.class_category = f.class_category';
155 ELSE
156 l_industry_where := '';
157 END IF;
158
159 -- Adding Filter on party_id
160 IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
161 l_child_party_where := ' AND f.party_id = v.party_id ';
162 ELSE
163 l_child_party_where := '';
164 END IF;
165
166 -- Constructing the ORDER BY clause
167 IF instr(fii_ar_util_pkg.g_order_by,',') <> 0 THEN
168 IF instr(fii_ar_util_pkg.g_order_by,'VIEWBY') <> 0 THEN
169 l_order_by := ' ORDER BY ' || fii_ar_util_pkg.g_order_by;
170 ELSE
171 l_order_by := ' ORDER BY NVL(FII_AR_DSO, -999999999) DESC';
172 END IF;
173 ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN
174 l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
175 l_order_by := ' ORDER BY NVL(' || l_order_column || ', -999999999) DESC';
176 ELSE
177 l_order_by := ' &ORDER_BY_CLAUSE';
178 END IF;
179
180 IF g_hit_rct_aging = 'Y' THEN
181 l_unapp_query := '
182 UNION ALL
183 SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ v.viewby VIEWBY,
184 v.viewby_code VIEWBYID,
185 -sum(f.unapp_amount) FII_AR_NET_REC_AMT,
186 NULL FII_AR_BILLED_AMT,
187 NULL FII_AR_VIEW_BY_DRILL,
188 NULL FII_AR_NET_REC_AMT_DRILL
189 FROM fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
190 (
191 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||l_gt_hint|| ' */ *
192 FROM fii_time_structures cal,
193 ' || fii_ar_util_pkg.get_from_statement || ' gt
194 WHERE cal.report_date = :ASOF_DATE
195 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE)
196 AND ' || fii_ar_util_pkg.get_where_statement || '
197 ) v
198 WHERE f.time_id = v.time_id
199 AND f.period_type_id = v.period_type_id
200 AND f.org_id = v.org_id
201 AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '
202 || l_customer_where
203 || l_child_party_where
204 || l_customer_acc_where
205 || l_industry_where || '
206 GROUP BY v.viewby_code, v.VIEWBY' || l_group_by || ' , v.record_type_id';
207 ELSE
208 l_unapp_query := '';
209 END IF;
210
211 -- Constructing the pmv sql query
212 sqlstmt := '
213 SELECT VIEWBY,
214 VIEWBYID,
215 round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_DSO,
216 sum(FII_AR_NET_REC_AMT) FII_AR_NET_REC_AMT,
217 sum(FII_AR_BILLED_AMT) FII_AR_BILLED_AMT,
218 max(FII_AR_VIEW_BY_DRILL) FII_AR_VIEW_BY_DRILL,
219 max(FII_AR_NET_REC_AMT_DRILL) FII_AR_NET_REC_AMT_DRILL,
220 (sum(sum(FII_AR_NET_REC_AMT)) over() * :DSO_PERIOD / NULLIF(sum(sum(FII_AR_BILLED_AMT)) over(),0)) FII_AR_GT_DSO,
221 sum(sum(FII_AR_NET_REC_AMT)) over() FII_AR_GT_NET_REC_AMT,
222 sum(sum(FII_AR_BILLED_AMT)) over() FII_AR_GT_BILLED_AMT
223 FROM
224 (
225 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
226 v.viewby VIEWBY,
227 v.viewby_code VIEWBYID,
228 CASE WHEN bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
229 ' || l_net_rec_column || '
230 ELSE
231 NULL
232 END FII_AR_NET_REC_AMT,
233 CASE WHEN bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
234 sum(f.billed_amount)
235 ELSE
236 NULL
237 END FII_AR_BILLED_AMT,
238 ' || l_cust_drill || ' FII_AR_VIEW_BY_DRILL,
239 ' || l_net_rec_sum_drill || ' FII_AR_NET_REC_AMT_DRILL
240 FROM fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
241 (
242 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) '||l_gt_hint|| ' */ *
243 FROM fii_time_structures cal,
244 ' || fii_ar_util_pkg.get_from_statement || ' gt
245 WHERE cal.report_date = :ASOF_DATE
246 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
247 OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
248 AND ' || fii_ar_util_pkg.get_where_statement || '
249 ) v
250 WHERE f.time_id = v.time_id
251 AND f.period_type_id = v.period_type_id
252 AND f.org_id = v.org_id
253 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_customer_where
254 || l_child_party_where
255 || l_customer_acc_where
256 || l_industry_where || '
257 GROUP BY v.viewby_code, v.VIEWBY' || l_group_by || ' , v.record_type_id
258 ' || l_unapp_query || '
259 ) inline_query
260 GROUP BY VIEWBYID, VIEWBY
261 ' || l_order_by;
262
263 -- Calling the bind_variable API
264 fii_ar_util_pkg.bind_variable(
265 p_sqlstmt => sqlstmt,
266 p_Page_parameter_tbl => p_page_parameter_tbl,
267 p_sql_output => p_dso_sql,
268 p_bind_output_table => p_dso_output
269 );
270
271 END get_dso;
272
273 PROCEDURE get_dso_trend(
274 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
275 p_dso_sql OUT NOCOPY VARCHAR2,
276 p_dso_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
277 ) IS
278
279 sqlstmt VARCHAR2(20000); -- Variable that stores the final SQL query
280 l_customer_where VARCHAR2(240); -- Variable to store the dynamic customer filter
281 l_industry_where VARCHAR2(240); -- Variable to store the dynamic industry filter
282 l_child_party_where VARCHAR2(240); -- Variable to store the dynamic party id filter
283 l_net_rec_sum_drill VARCHAR2(1000); -- Variable to store the drill to net receivables summary report
284 l_net_rec_column VARCHAR2(1000); -- Variable to store the columns of categories enabled in receivables setup page
285 l_curr_query VARCHAR2(2000); -- Variable to store the query to return the values for current period
286 l_unapp_amount_query VARCHAR2(2000); -- Variable to store the query to retrieve unapplied amount
287 l_curr_unapp_query VARCHAR2(2000); -- Variable to store the query to return unapplied amount for the current month
288
289
290 BEGIN
291
292 -- Clear global parameters AND read the new parameters
293 -- Sets all g_% variables to its default values
294 fii_ar_util_pkg.reset_globals;
295
296 -- Reads the parameters from the parameter portlet
297 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
298
299 -- Populates the security related global temporary tables (fii_ar_summary_gt)
300 fii_ar_util_pkg.populate_summary_gt_tables;
301
302 l_net_rec_sum_drill := 'pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID';
303
304 -- Adding Filter on party_id when Customer is NOT All
305 IF fii_ar_util_pkg.g_party_id <> '-111' THEN
306 l_child_party_where := ' AND f.party_id = v.party_id ';
307 ELSE
308 l_child_party_where := '';
309 END IF;
310
311 -- Defining industry where clause for specific industry when Industry is NOT All
312 IF fii_ar_util_pkg.g_industry_id <> '-111' THEN
313 l_industry_where := ' AND v.class_code = f.class_code AND v.class_category = f.class_category';
314 ELSE
315 l_industry_where := '';
316 END IF;
317
318 -- Adding columns that are enabled in the Receivables Setup page
319 l_net_rec_column := get_net_rec_column;
320
321 IF fii_ar_util_pkg.g_as_of_date <> LAST_DAY(fii_ar_util_pkg.g_as_of_date) THEN
322 l_curr_query := '
323 UNION ALL
324 -- Query to return data for current month
325 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
326 v.sequence FII_EFFECTIVE_NUM,
327 CASE WHEN bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
328 ' || l_net_rec_column || '
329 ELSE
330 NULL
331 END FII_AR_NET_REC_AMT,
332 NULL FII_AR_NET_REC_PRIOR_AMT,
333 CASE WHEN bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
334 sum(f.billed_amount)
335 ELSE
336 NULL
337 END FII_AR_BILLED_AMT,
338 NULL FII_AR_BILLED_PRIOR_AMT,
339 sum(f.inv_amount) inv_amount, sum(f.dm_amount) dm_amount, sum(f.cb_amount) cb_amount, sum(f.br_amount) br_amount, sum(f.dep_amount) dep_amount
340 FROM
341 fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
342 (
343 SELECT /*+ no_merge cardinality(gt 1)*/ *
344 FROM fii_ar_summary_gt gt,
345 (
346 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
347 FROM fii_time_ent_period per, fii_time_structures cal
348 WHERE per.end_date = last_day(:ASOF_DATE)
349 AND cal.report_date = :ASOF_DATE
350 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
351 OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
352 ) cal_per
353 ) v
354 WHERE
355 f.time_id = v.time_id
356 AND f.period_type_id = v.period_type_id
357 AND f.org_id = v.org_id
358 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_child_party_where || '
359 ' || l_industry_where || '
360 GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
361 l_curr_unapp_query := '
362 UNION ALL
363 -- Query to return unapplied amount for current month
364 SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
365 v.sequence FII_EFFECTIVE_NUM,
366 -sum(f.unapp_amount) FII_AR_NET_REC_AMT,
367 NULL FII_AR_NET_REC_PRIOR_AMT,
368 NULL FII_AR_BILLED_AMT,
369 NULL FII_AR_BILLED_PRIOR_AMT,
370 NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount
371 FROM
372 fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
373 (
374 SELECT /*+ no_merge cardinality(gt 1) */ *
375 FROM fii_ar_summary_gt gt,
376 (
377 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
378 FROM fii_time_ent_period per, fii_time_structures cal
379 WHERE per.end_date = last_day(:ASOF_DATE)
380 AND cal.report_date = :ASOF_DATE
381 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE)
382 ) cal_per
383 ) v
384 WHERE
385 f.time_id = v.time_id
386 AND f.period_type_id = v.period_type_id
387 AND f.org_id = v.org_id
388 AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' ' || l_child_party_where || '
389 ' || l_industry_where || '
390 GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
391 ELSE
392 l_curr_query := '';
393 l_curr_unapp_query := '';
394 END IF;
395
396 IF g_hit_rct_aging = 'Y' THEN
397 l_unapp_amount_query := '
398 UNION ALL
399 -- Query to return unapplied amount for months other than current month
400 SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
401 v.sequence FII_EFFECTIVE_NUM,
402 CASE WHEN v.start_date > :SD_PRIOR THEN
403 -sum(f.unapp_amount) ELSE NULL END FII_AR_NET_REC_AMT,
404 CASE WHEN v.end_date <= last_day(:SD_PRIOR) THEN
405 -sum(f.unapp_amount) ELSE NULL END FII_AR_NET_REC_PRIOR_AMT,
406 NULL FII_AR_BILLED_AMT,
407 NULL FII_AR_BILLED_PRIOR_AMT,
408 NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount
409 FROM
410 fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
411 (
412 SELECT /*+ no_merge cardinality(gt 1) */ *
413 FROM fii_ar_summary_gt gt,
414 (
415 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
416 FROM fii_time_ent_period per, fii_time_structures cal
417 WHERE per.start_date > :SD_PRIOR_PRIOR AND per.end_date <= :ASOF_DATE
418 AND cal.report_date = per.end_date
419 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE)
420 ) cal_per
421 ) v
422 WHERE
423 f.time_id = v.time_id
424 AND f.period_type_id = v.period_type_id
425 AND f.org_id = v.org_id
426 AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' ' || l_child_party_where || '
427 ' || l_industry_where || '
428 GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id';
429 ELSE
430 l_unapp_amount_query := '';
431 END IF;
432
433 sqlstmt := '
434 SELECT
435 cy_per.name VIEWBY,
436 round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_DSO,
437 sum(FII_AR_NET_REC_AMT) FII_AR_NET_REC_AMT,
438 sum(FII_AR_BILLED_AMT) FII_AR_BILLED_AMT,
439 round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_DSO_G,
440 round(sum(FII_AR_NET_REC_PRIOR_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_PRIOR_AMT),0)) FII_AR_PRIOR_DSO_G,
441 round(sum(FII_AR_NET_REC_AMT) * :DSO_PERIOD / NULLIF(sum(FII_AR_BILLED_AMT),0)) FII_AR_CURRENT_DSO_G,
442 CASE WHEN :ASOF_DATE >= cy_per.start_date AND :ASOF_DATE <= cy_per.end_date THEN
443 DECODE(' || g_open_rec_column_dsot || ', 0, '''', ''' || l_net_rec_sum_drill || ''')
444 ELSE
445 ''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''
446 END FII_AR_NET_REC_AMT_DRILL
447 FROM
448 fii_time_ent_period cy_per,
449 (
450 -- Query to return data for months other than current month
451 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
452 v.sequence FII_EFFECTIVE_NUM,
453 CASE WHEN v.start_date > :SD_PRIOR AND bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
454 ' || l_net_rec_column || '
455 ELSE
456 NULL
457 END FII_AR_NET_REC_AMT,
458 CASE WHEN v.end_date <= last_day(:SD_PRIOR) AND bitand(v.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
459 ' || l_net_rec_column || '
460 ELSE
461 NULL
462 END FII_AR_NET_REC_PRIOR_AMT,
463 CASE WHEN v.start_date > :SD_PRIOR AND bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
464 sum(f.billed_amount)
465 ELSE
466 NULL
467 END FII_AR_BILLED_AMT,
468 CASE WHEN v.end_date <= last_day(:SD_PRIOR) AND bitand(v.record_type_id, :DSO_BITAND) = :DSO_BITAND THEN
469 sum(f.billed_amount)
470 ELSE
471 NULL
472 END FII_AR_BILLED_PRIOR_AMT,
473 sum(f.inv_amount) inv_amount, sum(f.dm_amount) dm_amount, sum(f.cb_amount) cb_amount, sum(f.br_amount) br_amount, sum(f.dep_amount) dep_amount
474 FROM
475 fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
476 (
477 SELECT /*+ no_merge cardinality(gt 1) */ *
478 FROM fii_ar_summary_gt gt,
479 (
480 SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) */ per.sequence, cal.time_id, cal.period_type_id, per.start_date, per.end_date, cal.record_type_id
481 FROM fii_time_ent_period per, fii_time_structures cal
482 WHERE per.start_date > :SD_PRIOR_PRIOR AND per.end_date <= :ASOF_DATE
483 AND cal.report_date = per.end_date
484 AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
485 OR bitand(cal.record_type_id, :DSO_BITAND) = :DSO_BITAND)
486 ) cal_per
487 ) v
488 WHERE
489 f.time_id = v.time_id
490 AND f.period_type_id = v.period_type_id
491 AND f.org_id = v.org_id
492 AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_child_party_where || '
493 ' || l_industry_where || '
494 GROUP BY v.sequence, v.start_date, v.end_date, v.record_type_id
495 ' || l_curr_query || '
496 ' || l_unapp_amount_query || '
497 ' || l_curr_unapp_query || '
498 ) inline_query
499 WHERE
500 cy_per.start_date <= :ASOF_DATE
501 AND cy_per.start_date > :SD_PRIOR
502 AND cy_per.sequence = inline_query.fii_effective_num (+)
503 GROUP BY inline_query.fii_effective_num, cy_per.sequence, cy_per.start_date, cy_per.name, cy_per.end_date
504 ORDER BY cy_per.start_date';
505
506 -- Calling the bind_variable API
507 fii_ar_util_pkg.bind_variable(
508 p_sqlstmt => sqlstmt,
509 p_Page_parameter_tbl => p_page_parameter_tbl,
510 p_sql_output => p_dso_sql,
511 p_bind_output_table => p_dso_output
512 );
513
514 END get_dso_trend;
515
516 END fii_ar_dso_pkg;
517