eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FII_AR_OPEN_REC_SUMMARY

Source


1 PACKAGE BODY FII_AR_OPEN_REC_SUMMARY AS
2 /* $Header: FIIARDBIORSB.pls 120.17 2007/05/15 20:50:37 vkazhipu ship $ */
3 
4 PROCEDURE get_open_rec_sum (p_page_parameter_tbl IN         BIS_PMV_PAGE_PARAMETER_TBL,
5                             open_rec_sum_sql     OUT NOCOPY VARCHAR2,
6                             open_rec_sum_output  OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7 IS
8 
9 l_party_select		VARCHAR2(100);
10 l_party_group_by	VARCHAR2(100);
11 l_party_where 		VARCHAR2(250);
12 l_parent_party_where 	VARCHAR2(250);
13 l_collector_where	VARCHAR2(250);
14 l_cust_acct_where	VARCHAR2(250);
15 
16 l_cust_drill            VARCHAR2(500);
17 l_open_drill            VARCHAR2(500);
18 l_pdue_drill            VARCHAR2(500);
19 l_curr_drill            VARCHAR2(500);
20 
21 l_select		VARCHAR2(10000);
22 l_group_by 		VARCHAR2(100) := NULL;
23 l_order_by		VARCHAR2(250);
24 l_order_column		VARCHAR2(250);
25 l_self                  VARCHAR2(30);
26 l_gt_hint varchar2(500);
27 
28 
29 BEGIN
30 
31 -- init all variables
32 fii_ar_util_pkg.reset_globals;
33 
34 -- get global variables assigned
35 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
36 
37 -- populate dimension combinations in global temp tables
38 fii_ar_util_pkg.populate_summary_gt_tables;
39 
40 -- get viewby id (party_id, cust_account_id, org_id, collector_id)
41 -- fii_ar_util_pkg.get_viewby_id(l_viewby_id);
42 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
43 -- generate where clause for party_id
44 IF (fii_ar_util_pkg.g_party_id <> '-111' OR
45 	fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
46 	l_party_where := ' AND f.party_id   = t.party_id ';
47 END IF;
48 
49 -- generate where clause for parent_party_id
50 IF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
51 	l_parent_party_where := 'AND f.parent_party_id = t.parent_party_id ';
52 END IF;
53 
54 -- generate where clause for collector_id
55 IF (fii_ar_util_pkg.g_collector_id <> '-111' OR
56 	fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN
57 	l_collector_where := 'AND f.collector_id = t.collector_id ';
58 END IF;
59 
60 -- generate where clause for cust_account_id
61 IF (fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
62 	l_cust_acct_where := 'AND f.cust_account_id = t.cust_account_id';
63 	l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
64 END IF;
65 
66 
67 -- handle drills
68 IF (fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS' AND fii_ar_util_pkg.g_is_hierarchical_flag='Y') THEN
69 
70 l_self := ' t.is_self_flag,';
71 l_open_drill :=
72 ' DECODE(FII_AR_ORS_OPEN_REC_AMT,0,'''', DECODE(inline_view.is_leaf_flag, ''Y'',''pFunctionName=FII_AR_OPEN_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',
73 ''pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'')) ';
74 
75 l_pdue_drill :=
76 ' DECODE(FII_AR_ORS_PDUE_REC_AMT,0,'''', DECODE(inline_view.is_leaf_flag, ''Y'',''pFunctionName=FII_AR_PDUE_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',
77 ''pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'')) ';
78 
79 l_curr_drill :=
80 ' DECODE(FII_AR_ORS_CURR_REC_AMT,0,'''', DECODE(inline_view.is_leaf_flag, ''Y'',''pFunctionName=FII_AR_CURR_REC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',
81 ''pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'')) ';
82 
83 l_cust_drill :=
84 ' DECODE(inline_view.is_self_flag,''Y'','''', DECODE(inline_view.is_leaf_flag, ''Y'','''',''pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y'')) ';
85 
86 
87 -- Add SELF to Customer description for self nodes
88 --l_viewby:= 'DECODE(inline_view.parent_party_id,inline_view.viewby, DECODE(inline_view.is_leaf_flag, ''Y'', inline_view.viewby, inline_view.viewby '||'||'||'''('||fii_ar_util_pkg.g_self_msg||')'''||'), inline_view.viewby) ';
89 l_group_by := ' inline_view.is_self_flag, inline_view.viewby, inline_view.is_leaf_flag, inline_view.viewby_code ';
90 
91 ELSIF (fii_ar_util_pkg.g_view_by= 'FII_COLLECTOR+FII_COLLECTOR'
92 	OR 	fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS') THEN
93  IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
94 		l_open_drill :=
95 			' DECODE(FII_AR_ORS_OPEN_REC_AMT,0,'''',''pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'') ';
96 
97 		l_pdue_drill :=
98 			' DECODE(FII_AR_ORS_PDUE_REC_AMT,0,'''',''pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'') ';
99 
100 		l_curr_drill :=
101 		' DECODE(FII_AR_ORS_CURR_REC_AMT,0,'''',''pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'') ';
102  ELSE
103    l_open_drill :=
104 			' DECODE(FII_AR_ORS_OPEN_REC_AMT,0,'''',''pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ';
105 
106 		l_pdue_drill :=
107 			' DECODE(FII_AR_ORS_PDUE_REC_AMT,0,'''',''pFunctionName=FII_AR_PASTDUE_REC_AGING&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ';
108 
109 		l_curr_drill :=
110 		' DECODE(FII_AR_ORS_CURR_REC_AMT,0,'''',''pFunctionName=FII_AR_CURR_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ';
111 
112 END IF;
113 l_cust_drill := '''''';
114 
115 l_group_by := 'inline_view.viewby, inline_view.viewby_code ';
116 
117 ELSE -- this is the case of (fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS')
118 
119 l_party_select   := ' t.party_id,';
120 l_party_group_by := ' t.party_id, ';
121 
122 l_open_drill :=
123 ' DECODE(FII_AR_ORS_OPEN_REC_AMT,0,'''',''pFunctionName=FII_AR_OPEN_REC_DTL&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&pParamIds=Y'') ';
124 
125 l_pdue_drill :=
126 ' DECODE(FII_AR_ORS_PDUE_REC_AMT,0,'''',''pFunctionName=FII_AR_PDUE_REC_DTL&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&pParamIds=Y'') ';
127 
128 l_curr_drill :=
129 ' DECODE(FII_AR_ORS_CURR_REC_AMT,0,'''',''pFunctionName=FII_AR_CURR_REC_DTL&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&pParamIds=Y'') ';
130 
131 l_cust_drill := '''''';
132 
133 l_group_by := 'inline_view.viewby, inline_view.viewby_code, inline_view.party_id ';
134 
135 END IF;
136 
137 
138 IF INSTR(fii_ar_util_pkg.g_order_by,',') <> 0 THEN
139 
140    /*This means no particular sort column is selected in the report
141    So sort on the default column in descending order
142    NVL is added to make sure the null values appear last*/
143 
144    l_order_by := 'ORDER BY NVL(FII_AR_ORS_OPEN_REC_AMT, -999999999) DESC';
145 
146 ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN
147 
148    /*This means a particular sort column is clicked to have descending order
149    in which case we would want all the null values to appear last in the
150    report so add an NVL to that column*/
151 
152    l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
153    l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
154 
155 ELSE
156 
157    /*This is the case when user has asked for an ascending order sort.
158    Use PMV's order by clause*/
159 
160    l_order_by := '&ORDER_BY_CLAUSE';
161 
162 END IF;
163 
164 l_select :=
165 ' SELECT
166 viewby       VIEWBY,
167 viewby_code  VIEWBYID,
168 FII_AR_ORS_OPEN_REC_AMT,
169 FII_AR_ORS_PDUE_REC_AMT,
170 FII_AR_ORS_CURR_REC_AMT,
171 FII_AR_ORS_OPEN_REC_CT,
172 FII_AR_ORS_PDUE_REC_CT,
173 FII_AR_ORS_CURR_REC_CT,
174 FII_AR_ORS_OPEN_REC_WTD_TRM,
175 FII_AR_ORS_PDUE_REC_PERCENT,
176 FII_AR_ORS_PDUE_REC_WTD_DDSO,
177 SUM(FII_AR_ORS_OPEN_REC_AMT) over() FII_AR_ORS_OPEN_R_AMT_GT,
178 SUM(FII_AR_ORS_PDUE_REC_AMT) over() FII_AR_ORS_PDUE_R_AMT_GT,
179 SUM(FII_AR_ORS_CURR_REC_AMT) over() FII_AR_ORS_CURR_R_AMT_GT,
180 SUM(FII_AR_ORS_OPEN_REC_CT)  over() FII_AR_ORS_OPEN_R_CT_GT,
181 SUM(FII_AR_ORS_PDUE_REC_CT)  over() FII_AR_ORS_PDUE_R_CT_GT,
182 SUM(FII_AR_ORS_CURR_REC_CT)  over() FII_AR_ORS_CURR_R_CT_GT,
183 CASE WHEN sum(FII_AR_ORS_OPEN_REC_AMT) over() = 0
184      THEN NULL
185      ELSE sum(FII_AR_ORS_OPEN_REC_WTD_TRM_N) over() / sum(FII_AR_ORS_OPEN_REC_AMT) over()
186      END  FII_AR_ORS_OPEN_R_WTD_TRM_GT,
187 CASE WHEN sum(FII_AR_ORS_OPEN_REC_AMT) over() = 0
188      THEN NULL
189      ELSE (sum(FII_AR_ORS_PDUE_REC_AMT) over() / sum(FII_AR_ORS_OPEN_REC_AMT) over()) * 100
190 	 END FII_AR_ORS_PDUE_R_PERCENT_GT,
191 CASE WHEN sum(FII_AR_ORS_PDUE_REC_AMT) over() = 0
192      THEN NULL
193      ELSE (sum(FII_AR_ORS_PDUE_REC_AMT) over()
194 	       * to_number(to_char(&BIS_CURRENT_ASOF_DATE,''J''))
195 	       - sum(FII_AR_ORS_PDUE_REC_WTD_DDSO_N) over()) / sum(FII_AR_ORS_PDUE_REC_AMT) over()
196      END FII_AR_ORS_PDUE_R_WTD_DDSO_GT,
197 '|| l_cust_drill ||' FII_AR_ORS_CUST_DRILL,
198 '|| l_open_drill ||' FII_AR_ORS_OPEN_DRILL,
199 '|| l_pdue_drill ||' FII_AR_ORS_PDUE_DRILL,
200 '|| l_curr_drill ||' FII_AR_ORS_CURR_DRILL
201 FROM (
202 SELECT  /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
203 t.is_leaf_flag,
204 '||l_self||'
205 '||l_party_select||'
206 t.viewby,
207 t.viewby_code,
208 sum(total_open_amount)                       FII_AR_ORS_OPEN_REC_AMT,
209 sum(past_due_open_amount)                         FII_AR_ORS_PDUE_REC_AMT,
210 sum(current_open_amount)                     FII_AR_ORS_CURR_REC_AMT,
211 sum(total_open_count)                        FII_AR_ORS_OPEN_REC_CT,
212 sum(past_due_count)                          FII_AR_ORS_PDUE_REC_CT,
213 sum(current_open_count)                      FII_AR_ORS_CURR_REC_CT,
214 sum(wtd_terms_out_open_num)                  FII_AR_ORS_OPEN_REC_WTD_TRM_N,
215 sum(wtd_DDSO_due_num)                        FII_AR_ORS_PDUE_REC_WTD_DDSO_N,
216 CASE WHEN abs(sum(total_open_amount)) = 0
217      THEN NULL
218      ELSE sum(wtd_terms_out_open_num) / abs(sum(total_open_amount))
219      END  FII_AR_ORS_OPEN_REC_WTD_TRM,
220 CASE WHEN abs(sum(total_open_amount)) = 0
221      THEN NULL
222      ELSE sum(past_due_open_amount) / abs(sum(total_open_amount)) * 100
223      END  FII_AR_ORS_PDUE_REC_PERCENT,
224 CASE WHEN abs(sum(past_due_open_amount)) = 0
225      THEN NULL
226      ELSE (sum(past_due_open_amount)
227 	       * to_number(to_char(&BIS_CURRENT_ASOF_DATE ,''J''))
228 	       - sum(wtd_DDSO_due_num)) / abs(sum(past_due_open_amount))
229      END FII_AR_ORS_PDUE_REC_WTD_DDSO
230 FROM FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
231      (
232      SELECT /*+ no_merge '||l_gt_hint|| ' */ *
233        FROM fii_time_structures cal,
234             '||fii_ar_util_pkg.get_from_statement||' gt
235       WHERE report_date = :ASOF_DATE
236         AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
237         AND '  ||fii_ar_util_pkg.get_where_statement||'
238 	  ) t
239 WHERE  f.time_id = t.time_id
240   AND  f.period_type_id = t.period_type_id
241   AND  f.org_id = t.org_id
242   AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_parent_party_where||l_party_where||l_collector_where|| l_cust_acct_where||'
243 GROUP BY  t.is_leaf_flag, '||l_self||l_party_group_by||' t.viewby, t.viewby_code)  inline_view
244 GROUP BY '||l_group_by||',
245 FII_AR_ORS_OPEN_REC_AMT,
246 FII_AR_ORS_PDUE_REC_AMT,
247 FII_AR_ORS_CURR_REC_AMT,
248 FII_AR_ORS_OPEN_REC_CT,
249 FII_AR_ORS_PDUE_REC_CT,
250 FII_AR_ORS_CURR_REC_CT,
251 FII_AR_ORS_OPEN_REC_WTD_TRM_N,
252 FII_AR_ORS_OPEN_REC_WTD_TRM,
253 FII_AR_ORS_PDUE_REC_PERCENT,
254 FII_AR_ORS_PDUE_REC_WTD_DDSO_N,
255 FII_AR_ORS_PDUE_REC_WTD_DDSO
256 '||l_order_by||' ';
257 
258 fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, open_rec_sum_sql, open_rec_sum_output);
259 
260 END get_open_rec_sum;
261 
262 END FII_AR_OPEN_REC_SUMMARY;