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_NET_REC_SUM_PKG

Source


1 PACKAGE BODY fii_ar_net_rec_sum_pkg AS
2 /* $Header: FIIARDBINRB.pls 120.18 2007/05/15 20:50:02 vkazhipu ship $ */
3 
4 PROCEDURE get_net_rec_sum(
5 	p_page_parameter_tbl			IN		BIS_PMV_PAGE_PARAMETER_TBL,
6 	p_net_rec_sum_sql			OUT NOCOPY	VARCHAR2,
7 	p_net_rec_sum_output			OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL
8 ) IS
9 
10 sqlstmt 		VARCHAR2(24000);	-- Variable that stores the final SQL query
11 l_view_by		VARCHAR2(240);		-- Variable to store the viewby based on viewby selected in the report
12 l_collector_where	VARCHAR2(240);		-- Variable to store the dynamic collector filter
13 l_customer_where	VARCHAR2(240);		-- Variable to store the dynamic customer filter
14 l_customer_acc_where	VARCHAR2(240);		-- Variable to store the dynamic customer account filter
15 l_child_party_where	VARCHAR2(240);          -- Variable to store the dynamic party id filter
16 l_cust_acc_drill	VARCHAR2(1000);		-- Variable to store drill parameter to view report at customer account level
17 l_cust_drill		VARCHAR2(1000);		-- Variable to store self-drill parameter to view report to explore child nodes
18 l_inv_drill		VARCHAR2(1000);		-- Variable to store drill parameter for Invoice column
19 l_dm_drill		VARCHAR2(1000);		-- Variable to store drill parameter for Debit Memo column
20 l_cb_drill		VARCHAR2(1000);         -- Variable to store drill parameter for Chargeback column
21 l_unapp_drill		VARCHAR2(1000);         -- Variable to store drill parameter for Unapplied column
22 l_unapp_sum_drill	VARCHAR2(1000);         -- Variable to store drill parameter for Unapplied column
23 l_viewby_drill 		VARCHAR2(1000);         -- Variable to store drill parameter for View By column
24 l_amt_inv_drill 	VARCHAR2(1000);         -- Variable to store drill parameter for Invoice column
25 l_amt_dm_drill 		VARCHAR2(1000);         -- Variable to store drill parameter for Debit Memo column
26 l_amt_cb_drill 		VARCHAR2(1000);         -- Variable to store drill parameter for Chargeback column
27 l_amt_unapp_drill 	VARCHAR2(1000);         -- Variable to store drill parameter for Unapplied column
28 l_global_start_date	VARCHAR2(240);		-- Variable to store the globla start date
29 l_order_by              VARCHAR2(240);          -- Variable to store the order by clause
30 l_order_column          VARCHAR2(100);          -- Variable to store the order by column
31 l_self_flag_where	VARCHAR2(100);		-- Variable to retrieve is_self_flag dynamically
32 l_self_flag_where_d	VARCHAR2(100);		-- Variable to retrieve is_self_flag dynamically for dummy purpose
33 l_gt_hint varchar2(500);
34 l_cust_acc_drill_1	VARCHAR2(1000);		-- Variable to store drill parameter to view report at customer account level
35 l_unapp_sum_drill_1	VARCHAR2(1000);         -- Variable to store drill parameter for Unapplied column
36 BEGIN
37 
38 	-- Clear global parameters AND read the new parameters
39 	-- Sets all g_% variables to its default values
40 	fii_ar_util_pkg.reset_globals;
41 
42 	-- Reads the parameters from the parameter portlet
43 	fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
44   l_gt_hint := ' leading(gt) cardinality(gt 1) ';
45 	-- Populates the security related global temporary tables (fii_ar_summary_gt)
46 	fii_ar_util_pkg.populate_summary_gt_tables;
47 
48 	l_view_by := fii_ar_util_pkg.g_view_by;
49 
50 	-- Adding Filter on collector_id only if Collector is 'All'
51 	IF fii_ar_util_pkg.g_collector_id <> '-111' OR l_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
52 		l_collector_where := ' AND f.collector_id = v.collector_id';
53 	ELSE
54 		l_collector_where := '';
55 	END IF;
56 
57 	-- Adding Filter on party_id
58 	IF (fii_ar_util_pkg.g_party_id <> '-111' OR fii_ar_util_pkg.g_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
59 		l_child_party_where := ' AND f.party_id   = v.party_id ';
60 	ELSE
61 		l_child_party_where := '';
62 	END IF;
63 
64 	-- Defining the drills
65 	l_cust_acc_drill 	:= '''pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
66 	l_cust_drill 		:= '''pFunctionName=FII_AR_NET_REC_SUM&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY''';
67 	l_inv_drill 		:= '''pFunctionName=FII_AR_OPEN_INV_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
68 	l_dm_drill 		:= '''pFunctionName=FII_AR_OPEN_DM_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
69 	l_cb_drill 		:= '''pFunctionName=FII_AR_OPEN_CB_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
70 	l_unapp_drill 		:= '''pFunctionName=FII_AR_UNAPP_RCT_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
71 	l_unapp_sum_drill 	:= '''pFunctionName=FII_AR_UNAPP_RCT_SUMMARY&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
72 
73 	l_self_flag_where := ', v.is_self_flag';
74 	l_self_flag_where_d := ', NULL is_self_flag';
75 
76 	IF l_view_by = 'ORGANIZATION+FII_OPERATING_UNITS' OR l_view_by = 'FII_COLLECTOR+FII_COLLECTOR' THEN
77 		l_viewby_drill 		:= '''''';
78 		IF (fii_ar_util_pkg.g_party_id <> '-111') THEN
79 		 l_cust_acc_drill 	:= '''pFunctionName=FII_AR_NET_REC_SUM&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
80 		 l_unapp_sum_drill 	:= '''pFunctionName=FII_AR_UNAPP_RCT_SUMMARY&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
81 		ELSE
82 		 l_cust_acc_drill 	:= '''pFunctionName=FII_AR_NET_REC_SUM&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
83 		 l_unapp_sum_drill 	:= '''pFunctionName=FII_AR_UNAPP_RCT_SUMMARY&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID''';
84 		END IF;
85 		l_amt_inv_drill 	:= 'DECODE(sum(inline_query.inv_amount), 0, '''', ' || l_cust_acc_drill || ')';
86 		l_amt_dm_drill 		:= 'DECODE(sum(inline_query.dm_amount), 0, '''', ' || l_cust_acc_drill || ')';
87 		l_amt_cb_drill 		:= 'DECODE(sum(inline_query.cb_amount), 0, '''', ' || l_cust_acc_drill || ')';
88 		l_amt_unapp_drill 	:= 'DECODE(nvl(sum(inline_query.unapp_amount),0), 0, '''', ' || l_unapp_sum_drill || ')';
89 	ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMER_ACCOUNTS' THEN
90 	  l_gt_hint := ' leading(gt.gt) cardinality(gt.gt 1) ';
91 		l_viewby_drill 		:= '''''';
92 		l_amt_inv_drill 	:=  'DECODE(sum(inline_query.inv_amount), 0, '''', ' ||
93 '''pFunctionName=FII_AR_OPEN_INV_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS='' || max(inline_query.party_id) || ''&FII_AR_CUST_ACCOUNT='' || inline_query.viewby_code)';
94 		l_amt_dm_drill 		:= 'DECODE(sum(inline_query.dm_amount), 0, '''', ' ||
95 '''pFunctionName=FII_AR_OPEN_DM_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS='' || max(inline_query.party_id) || ''&FII_AR_CUST_ACCOUNT='' || inline_query.viewby_code)';
96 		l_amt_cb_drill 		:= 'DECODE(sum(inline_query.cb_amount), 0, '''', ' ||
97 '''pFunctionName=FII_AR_OPEN_CB_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS='' || max(inline_query.party_id) || ''&FII_AR_CUST_ACCOUNT='' || inline_query.viewby_code)';
98 		l_amt_unapp_drill 	:=  'DECODE(nvl(sum(inline_query.unapp_amount),0), 0, '''', ' ||
99 '''pFunctionName=FII_AR_UNAPP_RCT_DTL&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS='' || max(inline_query.party_id) || ''&FII_AR_CUST_ACCOUNT='' || inline_query.viewby_code)';
100 		-- Adding Filter on customer account only when viewby is Customer account
101 		l_customer_acc_where 	:= ' AND f.cust_account_id = v.cust_account_id';
102 		l_self_flag_where := '';
103 		l_self_flag_where_d := '';
104 	ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMERS' THEN
105 		l_viewby_drill 		:= 'DECODE(max(inline_query.is_leaf_flag), ''Y'', '''', DECODE(max(inline_query.is_self_flag), ''Y'', '''',' || l_cust_drill || '))';
106 		l_amt_inv_drill 	:= 'DECODE(sum(inline_query.inv_amount), 0, '''', DECODE(max(inline_query.is_leaf_flag), ''Y'',' || l_inv_drill || ', DECODE(max(inline_query.is_self_flag), ''Y'', ' || l_inv_drill || ',' || l_cust_acc_drill || ')))';
107 		l_amt_dm_drill 		:= 'DECODE(sum(inline_query.dm_amount), 0, '''', DECODE(max(inline_query.is_leaf_flag), ''Y'',' || l_dm_drill || ', DECODE(max(inline_query.is_self_flag), ''Y'', ' || l_dm_drill || ',' || l_cust_acc_drill ||  ')))';
108 		l_amt_cb_drill 		:= 'DECODE(sum(inline_query.cb_amount), 0, '''', DECODE(max(inline_query.is_leaf_flag), ''Y'',' || l_cb_drill || ', DECODE(max(inline_query.is_self_flag), ''Y'', ' || l_cb_drill || ',' || l_cust_acc_drill || ')))';
109 		l_amt_unapp_drill 	:= 'DECODE(nvl(sum(inline_query.unapp_amount),0), 0, '''', DECODE(max(inline_query.is_leaf_flag), ''Y'',' || l_unapp_drill || ', DECODE(max(inline_query.is_self_flag), ''Y'', ' || l_unapp_drill || ',' || l_unapp_sum_drill || ')))';
110 		-- Defining the view by column and the group by clause when viewby is Customer
111 		l_customer_where 	:= ' AND f.parent_party_id = v.parent_party_id';
112 	END IF;
113 
114         -- Constructing the ORDER BY clause
115         IF instr(fii_ar_util_pkg.g_order_by,',') <> 0 THEN
116                 l_order_by := ' ORDER BY NVL(FII_AR_NET_REC_AMT, -999999999) DESC';
117         ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN
118                 l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
119                 l_order_by := ' ORDER BY NVL(' || l_order_column || ', -999999999) DESC';
120         ELSE
121                 l_order_by := ' &ORDER_BY_CLAUSE';
122         END IF;
123 
124 	-- Constructing the pmv sql query
125 	sqlstmt := '
126 	SELECT
127 		inline_query.viewby					VIEWBY,
128 		inline_query.viewby_code	 			VIEWBYID,
129 		sum(inline_query.inv_amount) + sum(inline_query.dm_amount) + sum(inline_query.cb_amount) + sum(inline_query.br_amount)
130 		+ sum(inline_query.dep_amount) + sum(inline_query.on_account_credit_amount) - sum(inline_query.unapp_dep_amount)
131 		- nvl(sum(inline_query.unapp_amount), 0) - sum(inline_query.on_account_cash_amount) - sum(inline_query.claim_amount)
132 		- sum(inline_query.prepayment_amount)	 		FII_AR_NET_REC_AMT,
133 		sum(inline_query.inv_amount)				FII_AR_INV_AMT,
134 		sum(inline_query.dm_amount)				FII_AR_DEB_MEMO_AMT,
135 		sum(inline_query.cb_amount)				FII_AR_CHARGEBACK_AMT,
136 		sum(inline_query.br_amount)				FII_AR_BILLS_REC_AMT,
137 		sum(inline_query.dep_amount)				FII_AR_UNP_DEP_AMT,
138 		sum(inline_query.on_account_credit_amount)		FII_AR_ON_ACC_CREDIT_AMT,
139 		sum(inline_query.unapp_dep_amount)			FII_AR_UNAPP_DEP_AMT,
140 		nvl(sum(inline_query.unapp_amount), 0)			FII_AR_UNAPP_AMT,
141 		sum(inline_query.on_account_cash_amount)		FII_AR_ON_ACC_CASH_AMT,
142 		sum(inline_query.claim_amount)				FII_AR_CLAIMS_AMT,
143 		sum(inline_query.prepayment_amount)			FII_AR_PREPAYMENT_AMT,
144 		' || l_viewby_drill || '				FII_AR_VIEW_BY_DRILL,
145 		' || l_amt_inv_drill || '				FII_AR_INV_AMT_DRILL,
146 		' || l_amt_dm_drill || '				FII_AR_DEB_MEMO_AMT_DRILL,
147 		' || l_amt_cb_drill || '				FII_AR_CHARGEBACK_AMT_DRILL,
148 		' || l_amt_unapp_drill || '				FII_AR_UNAPP_AMT_DRILL,
149 		sum(sum(inline_query.inv_amount)) over() + sum(sum(inline_query.dm_amount)) over()
150 		+ sum(sum(inline_query.cb_amount)) over() + sum(sum(inline_query.br_amount)) over()
151 		+ sum(sum(inline_query.dep_amount)) over() + sum(sum(inline_query.on_account_credit_amount)) over()
152 		- sum(sum(inline_query.unapp_dep_amount)) over() - nvl(sum(sum(inline_query.unapp_amount)) over(), 0)
153 		- sum(sum(inline_query.on_account_cash_amount)) over() - sum(sum(inline_query.claim_amount)) over()
154 		- sum(sum(inline_query.prepayment_amount)) over() FII_AR_GT_NET_REC_AMT,
155                 sum(sum(inline_query.inv_amount)) over() 		FII_AR_GT_INV_AMT,
156 		sum(sum(inline_query.dm_amount)) over()			FII_AR_GT_DEB_MEMO_AMT,
157 		sum(sum(inline_query.cb_amount)) over()			FII_AR_GT_CHARGEBACK_AMT,
158 		sum(sum(inline_query.br_amount)) over()			FII_AR_GT_BILLS_REC_AMT,
159 		sum(sum(inline_query.dep_amount)) over()		FII_AR_GT_UNP_DEP_AMT,
160 		sum(sum(inline_query.on_account_credit_amount)) over()	FII_AR_GT_ON_ACC_CREDIT_AMT,
161 		sum(sum(inline_query.unapp_dep_amount)) over()		FII_AR_GT_UNAPP_DEP_AMT,
162 		nvl(sum(sum(inline_query.unapp_amount)) over(), 0)	FII_AR_GT_UNAPP_AMT,
163 		sum(sum(inline_query.on_account_cash_amount)) over()	FII_AR_GT_ON_ACC_CASH_AMT,
164 		sum(sum(inline_query.claim_amount)) over()		FII_AR_GT_CLAIMS_AMT,
165 		sum(sum(inline_query.prepayment_amount)) over()		FII_AR_GT_PREPAYMENT_AMT
166 	FROM
167 	(
168 		SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
169 			v.viewby, v.viewby_code,
170 			f.inv_amount, f.dm_amount, f.cb_amount, f.br_amount, f.dep_amount,
171 			f.on_account_credit_amount, f.unapp_dep_amount, NULL unapp_amount,
172 			f.on_account_cash_amount, f.claim_amount, f.prepayment_amount
173 			' || l_self_flag_where || ', v.is_leaf_flag, f.party_id
174 		FROM
175 			fii_ar_net_rec' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
176 			(
177 				SELECT /*+ no_merge '||l_gt_hint|| ' */ *
178 				FROM 	fii_time_structures cal,
179 					' || fii_ar_util_pkg.get_from_statement || ' gt
180 				WHERE 	cal.report_date = :ASOF_DATE
181 					AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
182 					AND ' || fii_ar_util_pkg.get_where_statement || '
183 			) v
184 		WHERE
185 			f.time_id = v.time_id
186 			AND f.period_type_id = v.period_type_id
187 			AND f.org_id = v.org_id
188 			AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_collector_where
189 			|| l_customer_where
190 			|| l_child_party_where
191 			|| l_customer_acc_where || '
192 		UNION ALL
193 		SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
194 			v.viewby, v.viewby_code,
195 			NULL inv_amount, NULL dm_amount, NULL cb_amount, NULL br_amount, NULL dep_amount,
196 			NULL on_account_credit_amount, NULL unapp_dep_amount, f.unapp_amount,
197 			NULL on_account_cash_amount, NULL claim_amount, NULL prepayment_amount
198 			' || l_self_flag_where_d || ', NULL is_leaf_flag, NULL party_id
199 		FROM
200 			fii_ar_rct_aging' || fii_ar_util_pkg.g_cust_suffix || '_mv' || fii_ar_util_pkg.g_curr_suffix || ' f,
201 			(
202 				SELECT /*+ no_merge '||l_gt_hint|| ' */ *
203 				FROM 	fii_time_structures cal,
204 					' || fii_ar_util_pkg.get_from_statement || ' gt
205 				WHERE 	cal.report_date = :ASOF_DATE
206 					AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
207 					AND ' || fii_ar_util_pkg.get_where_statement || '
208 			) v
209 		WHERE
210 			f.time_id = v.time_id
211 			AND f.period_type_id = v.period_type_id
212 			AND f.org_id = v.org_id
213 			AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '
214 			|| l_collector_where
215 			|| l_customer_where
216 			|| l_child_party_where
217 			|| l_customer_acc_where || '
218 	) inline_query
219 	GROUP BY inline_query.viewby_code, inline_query.viewby'
220 	|| l_order_by;
221 
222 	-- Calling the bind_variable API
223 	fii_ar_util_pkg.bind_variable(
224 		p_sqlstmt 		=> sqlstmt,
225 		p_page_parameter_tbl 	=> p_page_parameter_tbl,
226 		p_sql_output 		=> p_net_rec_sum_sql,
227 		p_bind_output_table 	=> p_net_rec_sum_output
228 	);
229 
230 END get_net_rec_sum;
231 
232 END fii_ar_net_rec_sum_pkg;
233