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_TRX_ACT_DTL_PKG

Source


1 PACKAGE BODY fii_ar_trx_act_dtl_pkg AS
2 /* $Header: FIIARDBITADB.pls 120.27 2007/07/03 20:17:33 mmanasse ship $ */
3 
4 PROCEDURE get_trx_act_dtl(
5 	p_page_parameter_tbl			IN		BIS_PMV_PAGE_PARAMETER_TBL,
6 	p_trx_act_dtl_sql			OUT NOCOPY	VARCHAR2,
7 	p_trx_act_dtl_output			OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL
8 ) IS
9 
10 sqlstmt 		VARCHAR2(24000);	-- Variable that stores the final SQL query
11 l_viewby_id		VARCHAR2(240);		-- Variable to store the viewby_id based on viewby selected in the report
12 l_view_by		VARCHAR2(240);		-- Variable to store the viewby based on viewby selected in the report
13 l_collector_where	VARCHAR2(240);		-- Variable to store the dynamic collector filter
14 l_customer_where	VARCHAR2(240);		-- Variable to store the dynamic customer filter
15 l_customer_acc_where	VARCHAR2(240);		-- Variable to store the dynamic customer account filter
16 l_child_party_where	VARCHAR2(240);          -- Variable to store the dynamic party id filter
17 l_cust_acc_drill	VARCHAR2(1000);		-- Variable to store drill parameter to view report at customer account level
18 l_cust_drill		VARCHAR2(1000);		-- Variable to store self-drill parameter to view report to explore child nodes
19 l_inv_drill		VARCHAR2(1000);		-- Variable to store drill parameter for Invoice column
20 l_dm_drill		VARCHAR2(1000);		-- Variable to store drill parameter for Debit Memo column
21 l_cb_drill		VARCHAR2(1000);         -- Variable to store drill parameter for Chargeback column
22 l_unapp_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_viewby		VARCHAR2(240);		-- Variable to store the view by column description clause
30 l_group_by		VARCHAR2(240);		-- Variable to store the group by clause
31 l_order_by              VARCHAR2(240);          -- Variable to store the order by clause
32 l_order_by_util         VARCHAR2(240);          -- Variable to store the order by clause returned from the util pkg
33 l_order_column          VARCHAR2(100);          -- Variable to store the order by column
34 l_where_clause		VARCHAR2(1000);		-- Variable to store the WHERE clause dynamically
35 l_from_clause		VARCHAR2(1000);		-- Variable to store the FROM clause dynamically
36 l_cust_account		VARCHAR2(30);		-- Variable to store Customer Account Id passed from the parent report
37 l_function_name		VARCHAR2(100);		-- Variable to store the function name that is being called
38 l_sysdate		VARCHAR2(30);		-- Variable to store sysdate for sending it to child report
39 l_balance_drill		VARCHAR2(2000);		-- Variable to store the drill on balance column
40 l_balance_col		VARCHAR2(1000); 	-- Variable to store Balance column source dynamically
41 l_col_curr_suffix	VARCHAR2(100);		-- Variable to store the currency suffix
42 
43 BEGIN
44 
45 	-- Reads the parameters from the parameter portlet
46 	fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
47 
48 	-- Defaulting the View By to OU
49 	fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
50 
51 	-- Populates the security related global temporary tables (fii_ar_summary_gt)
52 	fii_ar_util_pkg.populate_summary_gt_tables;
53 
54 	--Logic for sorting
55 	l_order_by_util := fii_ar_util_pkg.g_order_by;
56 
57 	IF instr(substr(l_order_by_util,-3), 'ASC') <> 0 THEN
58 	-- Ascending order sorting on any column
59 		l_order_by := '&ORDER_BY_CLAUSE';
60 	ELSIF instr(l_order_by_util, 'FII_AR_ORIG_AMT DESC') <> 0 THEN
61 	-- Default sorting
62 		l_order_by := 'ORDER BY NVL(FII_AR_ORIG_AMT, -999999999) DESC';
63 	ELSIF instr(l_order_by_util, 'DATE') <> 0 THEN
64 	-- Descending order sorting on Date column
65 		l_order_by := '&ORDER_BY_CLAUSE';
66 	ELSIF instr(l_order_by_util, 'BINARY') <> 0 THEN
67 	-- Descending order sorting on Text column
68 		l_order_column := substr(l_order_by_util, instr(l_order_by_util, 'FII'), instr(l_order_by_util, ',') - instr(l_order_by_util, 'FII'));
69 		l_order_by := 'ORDER BY NVL(' || l_order_column || ', ''      '') DESC';
70 	ELSE
71 	-- Descending order sorting on Amount column
72 		l_order_column := substr(l_order_by_util,1,instr(l_order_by_util, ' DESC'));
73 		l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
74 	END IF;
75 
76 	l_cust_account := fii_ar_util_pkg.g_cust_account;
77 
78 	-- Adding filter when one or multiple customers selected but not All
79 	IF fii_ar_util_pkg.g_party_id <> '-111' THEN
80 		l_where_clause := l_where_clause || ' AND hzca.party_id = gt.party_id AND hzca.party_id = :PARTY_ID';
81 	END IF;
82 
83 	-- Adding filter when a single collector is selected
84 	IF fii_ar_util_pkg.g_collector_id <> '-111' THEN
85 		l_from_clause := ', fii_collectors coll';
86 		l_where_clause := l_where_clause
87 			|| ' AND coll.collector_id = gt.collector_id
88 			AND coll.site_use_id = f.bill_to_site_use_id
89 			AND coll.cust_account_id = f.bill_to_customer_id';
90 	END IF;
91 
92 	-- Adding filter when a single industry is selected
93 	IF fii_ar_util_pkg.g_industry_id <> '-111' THEN
94 		l_from_clause := l_from_clause || ' , fii_party_mkt_class ind';
95 		l_where_clause := l_where_clause || ' AND ind.class_code = gt.class_code
96 			AND ind.party_id = hzca.party_id
97 			AND hzca.cust_account_id = f.bill_to_customer_id';
98 	END IF;
99 
100 	IF l_cust_account IS NULL THEN
101 		l_cust_account := ' NULL ';
102 	ELSE
103 		l_where_clause := l_where_clause || ' AND f.bill_to_customer_id = :CUST_ACCOUNT';
104 	END IF;
105 
106 	l_function_name := fii_ar_util_pkg.g_function_name;
107 
108 	l_col_curr_suffix := fii_ar_util_pkg.g_col_curr_suffix;
109 
110 	l_balance_col := '
111 					sum(ag.current_bucket_1_amount' || l_col_curr_suffix || '
112 					+ ag.current_bucket_2_amount' || l_col_curr_suffix || '
113 					+ ag.current_bucket_3_amount' || l_col_curr_suffix || '
114 					+ ag.past_due_bucket_1_amount' || l_col_curr_suffix || '
115 					+ ag.past_due_bucket_2_amount' || l_col_curr_suffix || '
116 					+ ag.past_due_bucket_3_amount' || l_col_curr_suffix || '
117 					+ ag.past_due_bucket_4_amount' || l_col_curr_suffix || '
118 					+ ag.past_due_bucket_5_amount' || l_col_curr_suffix || '
119 					+ ag.past_due_bucket_6_amount' || l_col_curr_suffix || '
120 					+ ag.past_due_bucket_7_amount' || l_col_curr_suffix || ')';
121 
122 	IF l_function_name = 'FII_AR_INV_ACT_DTL' THEN
123 		l_where_clause := l_where_clause || ' AND f.class = ''INV''';
124 	ELSIF l_function_name = 'FII_AR_DM_ACT_DTL' THEN
125 		l_where_clause := l_where_clause || ' AND f.class = ''DM''';
126 	ELSIF l_function_name = 'FII_AR_CB_ACT_DTL' THEN
127 		l_where_clause := l_where_clause || ' AND f.class = ''CB''';
128 	ELSIF l_function_name = 'FII_AR_BILLING_ACT_DTL' THEN
129 		l_where_clause := l_where_clause || ' AND f.class IN (''INV'',''DM'',''CB'',''CM'',''DEP'',''BR'') ';
130 		l_balance_col := '
131 				decode(f.class, ''CM'', sum(ag.on_acct_credit_amount' || l_col_curr_suffix || '),
132 					sum(ag.current_bucket_1_amount' || l_col_curr_suffix || '
133 					+ ag.current_bucket_2_amount' || l_col_curr_suffix || '
134 					+ ag.current_bucket_3_amount' || l_col_curr_suffix || '
135 					+ ag.past_due_bucket_1_amount' || l_col_curr_suffix || '
136 					+ ag.past_due_bucket_2_amount' || l_col_curr_suffix || '
137 					+ ag.past_due_bucket_3_amount' || l_col_curr_suffix || '
138 					+ ag.past_due_bucket_4_amount' || l_col_curr_suffix || '
139 					+ ag.past_due_bucket_5_amount' || l_col_curr_suffix || '
140 					+ ag.past_due_bucket_6_amount' || l_col_curr_suffix || '
141 					+ ag.past_due_bucket_7_amount' || l_col_curr_suffix || '))';
142 	END IF;
143 
144 	SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
145 
146 	l_balance_drill := 'DECODE(outer_inner_query.FII_AR_BALANCE_AMT, NULL, NULL, DECODE(outer_inner_query.FII_AR_TRAN_CLASS,''INV'',
147 ''AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AR_INV_ACT_HISTORY&FII_AR_CUST_TRX_ID=''||customer_trx_id
148 ||''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=''||outer_inner_query.FII_AR_TRAN_CLASS
149 ||''&BIS_PMV_DRILL_CODE_FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM&FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&pParamIds=Y'',
150 ''BR'',
151 ''AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AR_BR_ACT_HISTORY&FII_AR_CUST_TRX_ID=''||customer_trx_id
152 ||''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=''||outer_inner_query.FII_AR_TRAN_CLASS
153 ||''&BIS_PMV_DRILL_CODE_FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM&FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&pParamIds=Y'',
154 ''CB'',
155 ''AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AR_CB_ACT_HISTORY&FII_AR_CUST_TRX_ID=''||customer_trx_id
156 ||''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=''||outer_inner_query.FII_AR_TRAN_CLASS
157 ||''&BIS_PMV_DRILL_CODE_FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM&FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&pParamIds=Y'',
158 ''CM'',
159 ''AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AR_CM_ACT_HISTORY&FII_AR_CUST_TRX_ID=''||customer_trx_id
160 ||''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=''||outer_inner_query.FII_AR_TRAN_CLASS
161 ||''&BIS_PMV_DRILL_CODE_FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM&FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&pParamIds=Y'',
162 ''DEP'',
163 ''AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AR_DEP_ACT_HISTORY&FII_AR_CUST_TRX_ID=''||customer_trx_id
164 ||''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=''||outer_inner_query.FII_AR_TRAN_CLASS
165 ||''&BIS_PMV_DRILL_CODE_FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM&FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&pParamIds=Y'',
166 ''DM'',
167 ''AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AR_DM_ACT_HISTORY&FII_AR_CUST_TRX_ID=''||customer_trx_id
168 ||''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=''||outer_inner_query.FII_AR_TRAN_CLASS
169 ||''&BIS_PMV_DRILL_CODE_FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM&FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&pParamIds=Y'', '''' ))';
170 
171 
172 	-- Constructing the pmv sql query
173 	sqlstmt := '
174 	SELECT
175 		FII_AR_ACCT_NUM,
176 		FII_AR_TRAN_NUM,
177 		lk.meaning FII_AR_TRAN_CLASS,
178 		FII_AR_TRAN_TYPE,
179 		FII_AR_TRAN_DATE,
180 		FII_AR_GL_DATE,
181 		FII_AR_FIRST_DUE_DATE,
182 		FII_AR_TRAN_AMT,
183 		FII_AR_ORIG_AMT,
184 		FII_AR_BALANCE_AMT,
185 		FII_AR_TERMS,
186 		FII_AR_SOURCE,
187 		decode(FII_AR_TRAN_CLASS, ''INV'',
188 		''pFunctionName=ARBPA_TM_REAL_PREVIEW&retainBN=Y&retainAM=Y&addBreadCrumb=Y&TermsSequenceNumber=1&CustomerTrxId=''
189 		|| customer_trx_id  || ''&orgId='' || outer_inner_query.org_id || ''&pParamIds=Y'', '''') FII_AR_TRAN_NUM_DRILL,
190 		''AS_OF_DATE=' || l_sysdate ||
191 '&pFunctionName=FII_AR_SCHD_PMT_DISCNT&BIS_PMV_DRILL_CODE_FII_AR_CUST_ACCOUNT=FII_AR_ACCT_NUM&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_CURRENCIES='||'''||FII_AR_TRAN_CURR||'''||'&FII_AR_CUST_TRX_ID=''
192 || customer_trx_id || ''&pParamIds=Y'' FII_AR_FIRST_DUE_DATE_DRILL,
193 		' || l_balance_drill || ' FII_AR_BALANCE_AMT_DRILL,
194 
195 		decode(outer_inner_query.order_ref_number, NULL, '''', ''pFunctionName=ONT_PORTAL_ORDERDETAILS&HeaderId='' || ooh.header_id) FII_AR_SOURCE_DRILL,
196 		sum(FII_AR_ORIG_AMT) over() FII_AR_GT_ORIG_AMT,
197 		sum(FII_AR_BALANCE_AMT) over() FII_AR_GT_BALANCE_AMT,
198 		' || l_cust_account || ' FII_AR_CUST_ACCOUNT,
199 		FII_AR_TRAN_CURR
200 	FROM
201 	(
202 		SELECT
203 			inner_query.customer_trx_id, inner_query.org_id,
204 			inner_query.order_ref_number order_ref_number,
205 			FII_AR_ACCT_NUM,
206 			FII_AR_TRAN_NUM,
207 			FII_AR_TRAN_CLASS,
208 			FII_AR_TRAN_TYPE,
209 			FII_AR_TRAN_DATE,
210 			FII_AR_GL_DATE,
211 			FII_AR_FIRST_DUE_DATE,
212 			inner_query.invoice_currency_code || '' '' || to_char(sum(FII_AR_TRAN_AMT),''999,999,999,999'') FII_AR_TRAN_AMT,
213 			sum(FII_AR_ORIG_AMT) FII_AR_ORIG_AMT,
214 			sum(FII_AR_BALANCE_AMT) FII_AR_BALANCE_AMT,
215 			FII_AR_TERMS,
216 			FII_AR_SOURCE,
217 			inner_query.invoice_currency_code FII_AR_TRAN_CURR
218 		FROM
219 		(
220 			SELECT /*+ leading(gt) cardinality(gt 1) */
221 				f.customer_trx_id, f.org_id,
222 				f.invoice_currency_code,
223 				f.order_ref_number order_ref_number,
224 				hzca.account_number	FII_AR_ACCT_NUM,
225 				f.transaction_number FII_AR_TRAN_NUM,
226 				f.class FII_AR_TRAN_CLASS,
227 				ractt.DESCRIPTION FII_AR_TRAN_TYPE,
228 				f.TRX_DATE FII_AR_TRAN_DATE,
229 				f.GL_DATE FII_AR_GL_DATE,
230 				min(f.DUE_DATE) FII_AR_FIRST_DUE_DATE,
231 				sum(decode(ag.action, ''Transaction'', decode(ag.billing_activity_flag, ''Y'', f.amount_due_original_trx,0),0)) FII_AR_TRAN_AMT,
232 				sum(decode(ag.action, ''Transaction'', decode(ag.billing_activity_flag, ''Y'',f.amount_due_original' || l_col_curr_suffix || ',0),0)) FII_AR_ORIG_AMT,
233 					' || l_balance_col || ' FII_AR_BALANCE_AMT,
234 				ratt.description FII_AR_TERMS,
235 				rabs.description FII_AR_SOURCE
236 			FROM
237 				fii_ar_pmt_schedules_f f,
238 				ra_cust_trx_types_all ractt,
239 				ra_terms_tl ratt,
240 				ra_batch_sources_all rabs,
241 				hz_cust_accounts hzca,
242 				fii_ar_aging_receivables ag,
243 				fii_ar_summary_gt gt
244 				' || l_from_clause || '
245 			WHERE
246 				f.cust_trx_type_id = ractt.cust_trx_type_id(+)
247 				AND f.class = ractt.type
248 				AND f.org_id = ractt.org_id
249 				AND f.term_id = ratt.term_id(+)
250 				AND nvl(ratt.language,USERENV(''LANG'')) = USERENV(''LANG'')
251 				AND f.batch_source_id = rabs.batch_source_id
252 				AND f.org_id = rabs.org_id
253 				AND gt.org_id = f.org_id
254 				AND f.bill_to_customer_id = hzca.cust_account_id
255 				AND f.filter_date BETWEEN :CURR_PERIOD_START AND
256 				:ASOF_DATE
257 				AND ag.event_date BETWEEN :CURR_PERIOD_START AND
258 				:ASOF_DATE
259 				AND f.payment_schedule_id = ag.payment_schedule_id
260 				AND f.org_id = ag.org_id
261 				--AND ag.action IN (''Transaction'', ''Adjustment'', ''Application'') // Commented for Bug # 5176544 fix
262 				' || l_where_clause || '
263 			GROUP BY f.customer_trx_id, f.order_ref_number, hzca.account_number, f.transaction_number, f.class, ractt.DESCRIPTION,
264 					f.TRX_DATE, f.GL_DATE, f.invoice_currency_code, ratt.description, rabs.description, f.org_id
265 	        ) inner_query
266         	GROUP BY inner_query.customer_trx_id, inner_query.order_ref_number, FII_AR_ACCT_NUM, FII_AR_TRAN_NUM, FII_AR_TRAN_CLASS,
267 			FII_AR_TRAN_TYPE, FII_AR_TRAN_DATE, FII_AR_GL_DATE, FII_AR_FIRST_DUE_DATE, inner_query.invoice_currency_code, FII_AR_TERMS, FII_AR_SOURCE, inner_query.org_id
268 	)outer_inner_query,
269 		ar_lookups lk,
270 		oe_order_headers_all ooh
271 	WHERE
272 		outer_inner_query.FII_AR_TRAN_CLASS = lk.lookup_code
273 		AND lk.lookup_type= ''INV/CM/ADJ''
274 		AND outer_inner_query.order_ref_number = to_char(ooh.order_number(+))
275 	' || l_order_by;
276 
277 	-- Calling the bind_variable API
278 	fii_ar_util_pkg.bind_variable(
279 		p_sqlstmt 		=> sqlstmt,
280 		p_page_parameter_tbl 	=> p_page_parameter_tbl,
281 		p_sql_output 		=> p_trx_act_dtl_sql,
282 		p_bind_output_table 	=> p_trx_act_dtl_output
283 	);
284 
285 END get_trx_act_dtl;
286 
287 END fii_ar_trx_act_dtl_pkg;
288