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_PDUE

Source


1 PACKAGE BODY FII_AR_OPEN_REC_PDUE AS
2 /* $Header: FIIARDBIPDB.pls 120.15 2007/05/15 20:51:17 vkazhipu ship $ */
3 
4 
5 
6 PROCEDURE get_rec_pdue (
7         p_page_parameter_tbl    IN  BIS_PMV_PAGE_PARAMETER_TBL,
8         open_rec_sql             OUT NOCOPY VARCHAR2,
9         open_rec_output          OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
10 IS
11 
12 l_party_where 		VARCHAR2(250);
13 l_parent_party_where 	VARCHAR2(250);
14 l_collector_where	VARCHAR2(250);
15 l_cust_acct_where	VARCHAR2(250);
16 l_cust_self_drill	VARCHAR2(500);
17 l_past_due_rec_drill	VARCHAR2(500);
18 l_open_rec_drill	VARCHAR2(500);
19 l_select		VARCHAR2(10000);
20 l_group_by 		VARCHAR2(100) := NULL;
21 l_order_by		VARCHAR2(250);
22 l_order_column		VARCHAR2(250);
23 l_self                  VARCHAR2(25);
24 l_party_select		VARCHAR2(50);
25 l_party_groupby		VARCHAR2(50);
26 l_gt_hint varchar2(500);
27 BEGIN
28 
29 fii_ar_util_pkg.reset_globals;
30 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
31 fii_ar_util_pkg.populate_summary_gt_tables;
32 
33 
34 
35 l_gt_hint := ' leading(gt) cardinality(gt 1) ';
36 
37 /* Dynamically generating the where clause for PARTY_ID*/
38 IF (fii_ar_util_pkg.g_party_id <> '-111' OR
39 	fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
40 	l_party_where := ' AND f.party_id   = t.party_id ';
41 END IF;
42 
43 /* Dynamically generating the where clause for PARENT_PARTY_ID */
44 IF(fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
45 	l_parent_party_where := 'AND f.parent_party_id = t.parent_party_id ';
46 END IF;
47 
48 /* Dynamically generating the where clause for COLLECTOR_ID*/
49 IF (fii_ar_util_pkg.g_collector_id <> '-111' OR
50 	fii_ar_util_pkg.g_view_by = 'FII_COLLECTOR+FII_COLLECTOR') THEN
51 	l_collector_where := 'AND f.collector_id = t.collector_id ';
52 END IF;
53 
54 l_party_select := ' ';
55 l_party_groupby := ' ';
56 
57 /* Drills */
58 IF (fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS' AND fii_ar_util_pkg.g_is_hierarchical_flag='Y') THEN
59 
60 l_self := ' t.is_self_flag,';
61 
62 	l_past_due_rec_drill:=
63 	' DECODE(FII_AR_PDUE_REC,0,'''',DECODE(inline_view.is_self_flag, ''Y'',''pFunctionName=FII_AR_PDUE_REC_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'',DECODE(inline_view.is_leaf_flag, ''Y'' ,
64 ''pFunctionName=FII_AR_PDUE_REC_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'', ''pFunctionName=FII_AR_OPEN_REC_PDUE&VIEW_BY_NAME=VIEW_BY_ID
65 &VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y''))) ';
66 
67 	l_open_rec_drill :=
68 	' DECODE(FII_AR_OPEN_REC,0,'''',DECODE(inline_view.is_self_flag, ''Y'',''pFunctionName=FII_AR_OPEN_REC_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'',DECODE(inline_view.is_leaf_flag, ''Y'' ,
69 ''pFunctionName=FII_AR_OPEN_REC_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID'', ''pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID
70 &VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y''))) ';
71 
72 /* Drills to children on Customer dimension */
73 	l_cust_self_drill :=' DECODE(inline_view.is_self_flag,''Y'','''', DECODE(inline_view.is_leaf_flag, ''Y'','''',''pFunctionName=FII_AR_OPEN_REC_PDUE&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y'')) ';
74 
75 	l_group_by := ' inline_view.is_self_flag,inline_view.viewby,inline_view.is_leaf_flag,inline_view.viewby_id ';
76 
77 ELSIF 	(fii_ar_util_pkg.g_view_by= 'FII_COLLECTOR+FII_COLLECTOR'
78 	OR 	fii_ar_util_pkg.g_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' ) THEN
79  IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
80 /* Calls Open Receivables: Percent Past Due - View by Customer Account*/
81 	l_past_due_rec_drill := ' DECODE(FII_AR_PDUE_REC,0,'''',''pFunctionName=FII_AR_OPEN_REC_PDUE&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'') ';
82 
83 /* Calls Open Receivables Summary - View by Customer Account*/
84 	l_open_rec_drill := ' DECODE(FII_AR_OPEN_REC,0,'''',''pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y'') ';
85  ELSE
86   /* Calls Open Receivables: Percent Past Due - View by Customer*/
87 	l_past_due_rec_drill := ' DECODE(FII_AR_PDUE_REC,0,'''',''pFunctionName=FII_AR_OPEN_REC_PDUE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ';
88 
89 /* Calls Open Receivables Summary - View by Customer*/
90 	l_open_rec_drill := ' DECODE(FII_AR_OPEN_REC,0,'''',''pFunctionName=FII_AR_OPEN_REC_SUMMARY&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'') ';
91 
92 
93  END IF;
94 	l_cust_self_drill := ''' ''';
95 	l_group_by := 'inline_view.viewby, inline_view.viewby_id ';
96 
97 ELSIF (fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS') THEN
98   l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
99 	l_party_select := ' t.party_id party_id, ';
100 	l_party_groupby := ',t.party_id ';
101 
102 /* Dynamically generating the where clause for Customer Account */
103         l_cust_acct_where := 'AND f.cust_account_id = t.cust_account_id';
104 
105 /* Calls Past Due Receivales Detail report  */
106         l_past_due_rec_drill := '
107 DECODE(FII_AR_PDUE_REC,0,'''',''pFunctionName=FII_AR_PDUE_REC_DTL&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&pParamIds=Y'') ';
108 
109 /* Calls Open Receivables Detail report  */
110         l_open_rec_drill := '
111 DECODE(FII_AR_OPEN_REC,0,'''',''pFunctionName=FII_AR_OPEN_REC_DTL&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=''||party_id||''&FII_AR_CUST_ACCOUNT=VIEWBYID&pParamIds=Y'') ';
112 
113 	l_cust_self_drill := ''' ''';
114 
115         l_group_by := 'inline_view.viewby,
116 inline_view.viewby_id,inline_view.party_id ';
117 
118 ELSE
119 
120 /* Calls Past Due Receivales Detail report  */
121 	l_past_due_rec_drill := ' DECODE(FII_AR_PDUE_REC,0,'''',''pFunctionName=FII_AR_PDUE_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&pParamIds=Y'') ';
122 
123 /* Calls Open Receivables Detail report  */
124 	l_open_rec_drill := ' DECODE(FII_AR_OPEN_REC,0,'''',''pFunctionName=FII_AR_OPEN_REC_DTL&FII_AR_CUST_ACCOUNT=VIEWBYID&pParamIds=Y'') ';
125 	l_cust_self_drill := ''' ''';
126 
127 	l_group_by := 'inline_view.viewby, inline_view.viewby_id ';
128 END IF;
129 
130 
131 IF INSTR(fii_ar_util_pkg.g_order_by,',') <> 0 THEN
132    l_order_by := 'ORDER BY NVL(FII_AR_PERC_OPEN_REC, -999999999) DESC';
133 
134 ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN
135    l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
136    l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
137 ELSE
138    l_order_by := '&ORDER_BY_CLAUSE';
139 END IF;
140 
141 
142 l_select :=
143 ' SELECT  inline_view.viewby  viewby,
144 viewby_id    VIEWBYID,
145 (FII_AR_PRIOR_PDUE_REC/NULLIF(abs(FII_AR_PRIOR_OPEN_REC),0))*100 FII_AR_PERC_PRIOR_OPEN_REC,
146 (FII_AR_PDUE_REC/NULLIF(abs(FII_AR_OPEN_REC),0))*100 FII_AR_PERC_OPEN_REC_G,
147 (FII_AR_PDUE_REC/NULLIF(abs(FII_AR_OPEN_REC),0))*100 FII_AR_PERC_OPEN_REC,
148 FII_AR_PDUE_REC,
149 FII_AR_OPEN_REC,
150 FII_AR_PRIOR_PDUE_REC,
151 FII_AR_PRIOR_OPEN_REC,
152 '||l_open_rec_drill||' FII_AR_OPEN_REC_DRILL,
153 '||l_past_due_rec_drill||' FII_AR_PDUE_REC_DRILL,
154 '||l_cust_self_drill||' FII_AR_CUST_SELF_DRILL,
155 SUM(FII_AR_PDUE_REC) over() FII_AR_PDUE_REC_GT,
156 SUM(FII_AR_OPEN_REC) over() FII_AR_OPEN_REC_GT,
157 CASE WHEN SUM(FII_AR_OPEN_REC) over() = 0 THEN NULL
158 ELSE (SUM(FII_AR_PDUE_REC) over()/SUM(FII_AR_OPEN_REC) over()) *100 END FII_AR_PERC_OPEN_REC_GT
159 FROM (
160 SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
161 t.is_leaf_flag,
162 '||l_self||'
163 t.viewby viewby,
164 viewby_code viewby_id ,
165 '||l_party_select||'
166 SUM(DECODE(t.report_date, :ASOF_DATE , past_due_open_amount , NULL ) )   FII_AR_PDUE_REC,
167 SUM(DECODE(t.report_date, :ASOF_DATE, total_open_amount, NULL ) )   FII_AR_OPEN_REC,
168 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, past_due_open_amount, NULL ) )   FII_AR_PRIOR_PDUE_REC,
169 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, total_open_amount, NULL ) )   FII_AR_PRIOR_OPEN_REC
170 FROM FII_AR_NET_REC'||fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
171 ( SELECT  /*+ no_merge '||l_gt_hint|| ' */ *
172 	FROM 	fii_time_structures cal, '||fii_ar_util_pkg.get_from_statement||' gt
173 	WHERE report_date in (:ASOF_DATE,  :PREVIOUS_ASOF_DATE)
174 	AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
175 	AND '  ||fii_ar_util_pkg.get_where_statement||' ) t
176 WHERE  f.time_id = t.time_id
177 AND f.period_type_id = t.period_type_id
178 AND f.org_id = t.org_id
179 AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_parent_party_where||l_party_where||l_collector_where|| l_cust_acct_where||'
180 GROUP BY '||l_self||' t.is_leaf_flag, t.viewby, viewby_code'||l_party_groupby||')  inline_view
181 GROUP BY '||l_group_by||', FII_AR_OPEN_REC,FII_AR_PDUE_REC, FII_AR_PRIOR_OPEN_REC, FII_AR_PRIOR_PDUE_REC
182 '||l_order_by||' ';
183 
184 fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, open_rec_sql, open_rec_output);
185 
186 END get_rec_pdue;
187 
188 END FII_AR_OPEN_REC_PDUE;
189