1 PACKAGE BODY fii_ar_top_pdue_pkg AS
2 /* $Header: FIIARDBITPDB.pls 120.11.12000000.1 2007/02/23 02:29:21 applrt ship $ */
3
4 -- This package will provide sql statements to retrieve data for Top Past Due Customers Report
5
6 PROCEDURE get_top_pdue_cst(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
7 top_pdue_cst_sql out NOCOPY VARCHAR2, top_pdue_cst_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 --Sql Statement
10 sqlstmt VARCHAR2(32767);
11
12 --Variables for where clauses
13 l_collector_where VARCHAR2(300) := '';
14 l_party_where VARCHAR2(32000) := '';
15
16 --For Sorting
17 l_order_by varchar2(500);
18 l_order_column varchar2(100);
19 l_parent_select varchar2(500);
20 l_security_profile_id NUMBER;
21 l_security_org_id NUMBER;
22 l_all_org_flag VARCHAR2(30);
23 l_business_group_id NUMBER;
24 l_org_where VARCHAR2(1000);
25 l_org_specific_where VARCHAR2(1000);
26 l_page_refresh_date varchar2(500);
27
28 BEGIN
29
30 fii_ar_util_pkg.reset_globals;
31
32 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
33
34 fii_ar_util_pkg.g_view_by := 'CUSTOMER+FII_CUSTOMERS';
35
36 --populating page refresh date
37 FII_AR_UTIL_PKG.get_page_refresh_date;
38
39 IF fii_ar_util_pkg.g_is_hierarchical_flag = 'Y' THEN
40
41 fii_ar_util_pkg.g_cust_suffix := '_agrt';
42
43 ELSE
44
45 fii_ar_util_pkg.g_cust_suffix := '_base';
46
47 END IF;
48
49 /*========================= Party Clause Start =====================================*/
50
51 -- When Aggregate table is used
52
53 if ( fii_ar_util_pkg.g_is_hierarchical_flag = 'Y') then
54
55 --if more than one party is selected
56
57 if (fii_ar_util_pkg.g_count_parent_party_id > 1) then
58
59 l_party_where := ' AND cust_next_level_party_id in ( &CUSTOMER+FII_CUSTOMERS )
60 and cust_parent_party_id <> cust_child_party_id ';
61
62 -- if no party is selected
63
64 elsif (fii_ar_util_pkg.g_party_id = '-111') then
65
66 l_party_where := ' AND cust_parent_party_id = -999 ';
67
68 else
69
70 -- if only one party is selected
71
72 l_party_where := ' AND cust_parent_party_id in (&CUSTOMER+FII_CUSTOMERS) ';
73
74 end if;
75
76 else --if base table is used
77
78 if (fii_ar_util_pkg.g_party_id = '-111') then
79
80 l_party_where := ' AND cust_parent_party_id = cust_child_party_id ';
81
82 else
83
84 l_party_where := ' AND cust_parent_party_id in ( &CUSTOMER+FII_CUSTOMERS )';
85 end if;
86
87 end if;
88 /*========================= Party Clause End =====================================*/
89
90
91 /*========================Org Security Clause Start===========================*/
92
93 l_security_profile_id := fii_ar_util_pkg.get_sec_profile;
94 l_security_org_id := fnd_profile.value('ORG_ID');
95
96
97 IF l_security_profile_id is not null AND l_security_profile_id <> -1 THEN
98
99 SELECT view_all_organizations_flag, business_group_id
100 INTO l_all_org_flag, l_business_group_id
101 FROM per_security_profiles
102 WHERE security_profile_id = l_security_profile_id;
103
104
105 IF fii_ar_util_pkg.g_org_id = -111 THEN
106 l_org_specific_where := NULL;
107 ELSE
108 l_org_specific_where := ' AND per.organization_id= '||fii_ar_util_pkg.g_org_id;
109 END IF;
110
111 IF l_all_org_flag = 'Y' and l_business_group_id is NOT NULL THEN
112
113 l_org_where := ' and f.org_id in (SELECT per.organization_id
114 FROM hr_operating_units per, ar_system_parameters_all ar
115 WHERE per.business_group_id = '||l_business_group_id ||'
116 AND per.organization_id = ar.org_id '||l_org_specific_where||') ';
117
118 ELSIF l_all_org_flag = 'Y' and l_business_group_id is NULL THEN
119
120 l_org_where := ' and f.org_id in (SELECT per.organization_id
121 FROM hr_operating_units per
122 WHERE 1=1 '||l_org_specific_where||') ';
123
124 ELSE
125
126 l_org_where := ' and f.org_id in (SELECT organization_id
127 FROM per_organization_list per, ar_system_parameters_all ar
128 WHERE per.security_profile_id = '||l_security_profile_id ||'
129 AND per.organization_id = ar.org_id '||l_org_specific_where||') ';
130 END IF;
131
132 ELSIF l_security_org_id is not null THEN
133
134 l_org_where := NULL;
135
136 IF fii_ar_util_pkg.g_org_id =-111 OR fii_ar_util_pkg.g_org_id = l_security_org_id THEN
137 l_org_where := ' and f.org_id = '||l_security_org_id||' ';
138 ELSE
139 l_org_where := ' and f.org_id = -1 ';
140 END IF;
141
142 ELSE
143 l_org_where := ' and f.org_id = -1 ';
144
145
146 END IF;
147
148
149 /*===============================Org Security Clause End====================*/
150
151
152 --Frame the order by clause for the report sql
153 IF(instr(fii_ar_util_pkg.g_order_by,',') <> 0) THEN
154
155 /*This means no particular sort column is selected in the report
156 So sort on the default column in descending order
157 NVL is added to make sure the null values appear last*/
158
159 l_order_by := 'ORDER BY NVL(''FII_AR_PASTDUE_REC_AMT'', -999999999) DESC';
160
161 ELSIF(instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0)THEN
162
163 /*This means a particular sort column is clicked to have descending order
164 in which case we would want all the null values to appear last in the
165 report so add an NVL to that column*/
166
167 l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
168 l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
169
170 ELSE
171
172 /*This is the case when user has asked for an ascending order sort.
173 Use PMV's order by clause*/
174
175 l_order_by := '&ORDER_BY_CLAUSE';
176
177 END IF;
178
179
180 --getting page refresh date
181
182 l_page_refresh_date := to_char(fii_ar_util_pkg.g_page_refresh_date,'dd/mm/yyyy');
183
184 --Setting up the where clauses based on the Parameter for Collector Dimension
185 IF (fii_ar_util_pkg.g_collector_id <> '-111') THEN
186 l_collector_where := 'AND f.collector_id = :COLLECTOR_ID ';
187 END IF;
188
189
190 sqlstmt := 'SELECT inline_view.view_by VIEWBY,
191 inline_view.viewby_code VIEWBYID,
192 inline_view.view_by FII_AR_TOP_PDUE_VIEW_BY,
193 SUM(FII_AR_PASTDUE_REC_AMT) FII_AR_PASTDUE_REC_AMT,
194 (SUM(FII_AR_DISPUTE_AMT)/NULLIF(SUM(FII_AR_PASTDUE_REC_AMT),0))*100 FII_AR_DISPUTE_PER,
195 (SUM(FII_AR_PASTDUE_REC_AMT) * to_number(to_char(:PAGE_REFRESH_DATE , ''J'')) - SUM(FII_AR_WEIGHTED_DDSO_NUM))/NULLIF(SUM(FII_AR_PASTDUE_REC_AMT),0) FII_AR_WEIGHTED_DDSO,
196 (SUM(FII_AR_PASTDUE_REC_AMT)/NULLIF((SUM(FII_AR_PASTDUE_REC_AMT) + to_number(SUM(FII_AR_CURRENT_OPEN_AMT))),0)) *100 FII_AR_OPEN_REC_PER,
197 (SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_CURRENT_OPEN_AMT)) FII_AR_OPEN_REC_AMT,
198 (SUM(FII_AR_WEIGHTED_TO_NUM)/NULLIF((SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_CURRENT_OPEN_AMT)),0)) FII_AR_WEIGHTED_TO,
199 SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() FII_AR_GT_PASTDUE_REC_AMT,
200 (SUM(SUM(FII_AR_DISPUTE_AMT))over()/NULLIF(SUM(SUM(FII_AR_PASTDUE_REC_AMT))over(),0))*100 FII_AR_GT_DISPUTE_PCT_TOTAL,
201 (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() * to_number(to_char(:PAGE_REFRESH_DATE , ''J'')) - SUM(SUM(FII_AR_WEIGHTED_DDSO_NUM)) over())/NULLIF(SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER(),0) FII_AR_GT_WEIGHTED_DDSO,
202 (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER()/NULLIF((SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER() + SUM(SUM(FII_AR_CURRENT_OPEN_AMT)) OVER()),0)) *100 FII_AR_GT_PER_OPEN_REC,
203 (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() + SUM(SUM(FII_AR_CURRENT_OPEN_AMT)) over()) FII_AR_GT_OPEN_REC_AMT,
204 (SUM(SUM(FII_AR_WEIGHTED_TO_NUM)) OVER()/NULLIF((SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER() + SUM(SUM(FII_AR_CURRENT_OPEN_AMT)) OVER()),0)) FII_AR_GT_WEIGHTED_TO,
205 DECODE(SUM(FII_AR_PASTDUE_REC_AMT),0,'''',DECODE(NVL(SUM(FII_AR_PASTDUE_REC_AMT), -99999),-99999, '''',''pFunctionName=FII_AR_PASTDUE_REC_AGING'||
206 '&FII_CUSTOMER=VIEWBYID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&BIS_PMV_DRILL_CODE_AS_OF_DATE='||l_page_refresh_date||'&pParamIds=Y'')) FII_AR_PDUE_REC_DRILL,
207 DECODE((SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_CURRENT_OPEN_AMT)),0,'''',
208 DECODE(NVL((SUM(FII_AR_PASTDUE_REC_AMT) +
209 SUM(FII_AR_CURRENT_OPEN_AMT)), -99999),-99999, '''',''pFunctionName=FII_AR_OPEN_REC_SUMMARY&FII_CUSTOMER=VIEWBYID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS'||
210 '&BIS_PMV_DRILL_CODE_AS_OF_DATE='||l_page_refresh_date||'&pParamIds=Y'')) FII_AR_OPEN_REC_DRILL,
211 DECODE(inline_view.is_self_flag, ''Y'' , '''', DECODE(inline_view.is_leaf_flag, ''Y'','''', ''pFunctionName=FII_AR_TOP_PDUE_CUSTOMER&FII_CUSTOMER=VIEWBYID&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y'')) FII_AR_CUST_SELF_DRILL
212 from (
213 SELECT VIEW_BY view_by, viewby_code,
214 is_self_flag is_self_flag,
215 is_leaf_flag is_leaf_flag,
216 sum(past_due_open_amount'||fii_ar_util_pkg.g_col_curr_suffix||') FII_AR_PASTDUE_REC_AMT,
217 sum(wtd_terms_out_open_num'||fii_ar_util_pkg.g_col_curr_suffix||') FII_AR_WEIGHTED_TO_NUM,
218 sum(wtd_ddso_due_num'||fii_ar_util_pkg.g_col_curr_suffix||') FII_AR_WEIGHTED_DDSO_NUM,
219 sum(current_open_amount'||fii_ar_util_pkg.g_col_curr_suffix||') FII_AR_CURRENT_OPEN_AMT,
220 sum(past_due_dispute_amount'||fii_ar_util_pkg.g_col_curr_suffix||') FII_AR_DISPUTE_AMT
221 FROM FII_AR_TPDUE'|| fii_ar_util_pkg.g_cust_suffix ||'_F f
222 WHERE 1=1
223 '||l_party_where||l_collector_where||l_org_where||'
224 GROUP BY viewby_code, VIEW_BY, is_self_flag, is_leaf_flag
225 )inline_view
226 GROUP BY viewby_code, VIEW_BY, is_self_flag, is_leaf_flag '||l_order_by;
227
228
229 fii_ar_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, top_pdue_cst_sql, top_pdue_cst_output);
230
231 END get_top_pdue_cst;
232
233 END fii_ar_top_pdue_pkg;
234
[Home] [Help]