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_PAID_REC_DTL_PKG

Source


1 PACKAGE BODY fii_ar_paid_rec_dtl_pkg AS
2 /* $Header: FIIARDBIPRDB.pls 120.26 2007/07/03 20:21:34 mmanasse ship $ */
3 
4 PROCEDURE get_paid_rec_dtl(
5 	p_page_parameter_tbl			IN		BIS_PMV_PAGE_PARAMETER_TBL,
6 	p_paid_rec_dtl_sql			OUT NOCOPY	VARCHAR2,
7 	p_paid_rec_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 util pkg
33 l_order_column          VARCHAR2(100);          -- Variable to store the order by column
34 l_col_curr_suffix	VARCHAR2(100); 		-- Variable to store the suffix for columns based on currency
35 l_sysdate		VARCHAR2(20);		-- Variable to store sysdate for sending it to child report
36 
37 BEGIN
38 
39 	-- Reads the parameters from the parameter portlet
40 	fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
41 
42 	--Logic for sorting
43 	l_order_by_util := fii_ar_util_pkg.g_order_by;
44 
45 	IF instr(substr(l_order_by_util,-3), 'ASC') <> 0 THEN
46 	-- Ascending order sorting on any column
47 		l_order_by := '&ORDER_BY_CLAUSE';
48 	ELSIF instr(l_order_by_util, 'FII_AR_PAID_AMT DESC') <> 0 THEN
49 	-- Default sorting
50 		l_order_by := 'ORDER BY NVL(FII_AR_PAID_AMT, -999999999) DESC';
51 	ELSIF instr(l_order_by_util, 'DATE') <> 0 THEN
52 	-- Descending order sorting on Date column
53 		l_order_by := '&ORDER_BY_CLAUSE';
54 	ELSIF instr(l_order_by_util, 'BINARY') <> 0 THEN
55 	-- Descending order sorting on Text column
56 		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'));
57 		l_order_by := 'ORDER BY NVL(' || l_order_column || ', ''      '') DESC';
58 	ELSE
59 	-- Descending order sorting on Amount column
60 		l_order_column := substr(l_order_by_util,1,instr(l_order_by_util, ' DESC'));
61 		l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
62 	END IF;
63 
64 	l_col_curr_suffix := fii_ar_util_pkg.g_col_curr_suffix;
65 
66 	SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
67 
68 	-- Constructing the pmv sql query
69 	sqlstmt := '
70 	SELECT
71 		hzca.account_number FII_AR_ACCT_NUM,
72 		FII_AR_TRAN_NUM,
73 		lk.meaning FII_AR_TRAN_CLASS,
74 		ractt.DESCRIPTION FII_AR_TRAN_TYPE,
75 		FII_AR_TRAN_DATE,
76 		FII_AR_GL_DATE,
77 		FII_AR_FIRST_DUE_DATE,
78 		FII_AR_TRAN_AMT,
79 		FII_AR_ORIG_AMT,
80 		FII_AR_PAID_AMT,
81 		FII_AR_ADJUST_AMT,
82 		FII_AR_BALANCE_AMT,
83 		FII_AR_DISC_TAKEN_AMT,
84 		ratt.description FII_AR_TERMS,
85 		rabs.description FII_AR_SOURCE,
86 		decode(FII_AR_TRAN_CLASS, ''INV'',
87 			''pFunctionName=ARBPA_TM_REAL_PREVIEW&retainBN=Y&retainAM=Y&addBreadCrumb=Y&TermsSequenceNumber=1&CustomerTrxId=''
88 			|| customer_trx_id || ''&pParamIds=Y'', '''') FII_AR_TRAN_NUM_DRILL,
89 		 decode(lk.lookup_code,''PMT'','''',''pFunctionName=FII_AR_SCHD_PMT_DISCNT&FII_AR_CUST_TRX_ID='' || customer_trx_id ||
90 		''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_CURRENCIES='||'''||FII_AR_TRAN_CURR||'''||'&BIS_PMV_DRILL_CODE_FII_AR_CUST_ACCOUNT=FII_AR_ACCT_NUM&pParamIds=Y'') FII_AR_FIRST_DUE_DATE_DRILL,
91 		''pFunctionName=FII_AR_APP_RCT_DTL&TRX_NUM='' || customer_trx_id || ''&BIS_PMV_DRILL_CODE_FII_AR_CASH_RECEIPT_ID=''
92 		|| :CASH_RECEIPT_ID || ''&pParamIds=Y'' FII_AR_PAID_AMT_DRILL,
93 		decode(FII_AR_BALANCE_AMT, NULL, '''', ''AS_OF_DATE=' || l_sysdate || '&pFunctionName=FII_AR_INV_ACT_HISTORY&FII_AR_CUST_TRX_ID=''
94 		|| outer_inner_query.customer_trx_id || ''&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS='' || outer_inner_query.FII_AR_TRAN_CLASS
95 		|| ''&BIS_PMV_DRILL_CODE_FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM&FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&pParamIds=Y'') FII_AR_BALANCE_AMT_DRILL,
96 		decode(outer_inner_query.order_ref_number, NULL, '''',
97 			''pFunctionName=ONT_PORTAL_ORDERDETAILS&HeaderId='' || (select ooh.header_id
98 				from oe_order_headers_all ooh
99 				where outer_inner_query.order_ref_number    = to_char(ooh.order_number) )) FII_AR_SOURCE_DRILL,
100 		sum(FII_AR_ORIG_AMT) over() FII_AR_GT_ORIG_AMT,
101 		sum(FII_AR_PAID_AMT) over() FII_AR_GT_PAID_AMT,
102 		sum(FII_AR_ADJUST_AMT) over() FII_AR_GT_ADJUST_AMT,
103 		sum(FII_AR_BALANCE_AMT) over() FII_AR_GT_BALANCE_AMT,
104 		sum(FII_AR_DISC_TAKEN_AMT) over() FII_AR_GT_DISC_TAKEN_AMT
105 	FROM
106 	(
107 		SELECT
108 			inner_query.customer_trx_id, max(inner_query.cust_trx_type_id) cust_trx_type_id, max(inner_query.term_id) term_id,
109 			max(inner_query.batch_source_id) batch_source_id,max(inner_query.bill_to_customer_id)bill_to_customer_id,
110 			max(inner_query.order_ref_number) order_ref_number,
111 			max(inner_query.invoice_currency_code) invoice_currency_code,
112 			max(FII_AR_TRAN_NUM) FII_AR_TRAN_NUM,
113 			max(FII_AR_TRAN_CLASS) FII_AR_TRAN_CLASS,
114 			max(inner_query.org_id) org_id,
115 			max(FII_AR_TRAN_DATE) FII_AR_TRAN_DATE,
116 			max(FII_AR_GL_DATE) FII_AR_GL_DATE,
117 			max(FII_AR_FIRST_DUE_DATE) FII_AR_FIRST_DUE_DATE,
118 			max(inner_query.invoice_currency_code) || '' '' || to_char(sum(FII_AR_TRAN_AMT),''999,999,999,999'') FII_AR_TRAN_AMT,
119 			sum(FII_AR_ORIG_AMT) FII_AR_ORIG_AMT,
120 			SUM(fII_AR_PAID_AMT) FII_AR_PAID_AMT,
121 			sum(FII_AR_ADJUST_AMT) FII_AR_ADJUST_AMT,
122 			sum(FII_AR_BALANCE_AMT) FII_AR_BALANCE_AMT,
123 			sum(FII_AR_DISC_TAKEN_AMT) FII_AR_DISC_TAKEN_AMT,
124 			max(inner_query.invoice_currency_code) FII_AR_TRAN_CURR
125 		FROM
126 		(
127 			SELECT
128 				f.customer_trx_id, f.org_id, f.cust_trx_type_id, f.term_id, f.batch_source_id, f.bill_to_customer_id,
129 				f.invoice_currency_code,
130 				f.order_ref_number order_ref_number,
131 				f.transaction_number FII_AR_TRAN_NUM,
132 				f.class FII_AR_TRAN_CLASS,
133 				f.TRX_DATE FII_AR_TRAN_DATE,
134 				f.GL_DATE FII_AR_GL_DATE,
135 				min(f.DUE_DATE) FII_AR_FIRST_DUE_DATE,
136 				sum(f.amount_due_original_trx) FII_AR_TRAN_AMT,
137 				sum(f.amount_due_original' || l_col_curr_suffix || ') FII_AR_ORIG_AMT,
138 				sum(h.amount_applied_trx' || l_col_curr_suffix || ') FII_AR_PAID_AMT,
139 				NULL FII_AR_ADJUST_AMT,
140 				NULL FII_AR_BALANCE_AMT,
141 				sum(f.earned_discount_amount' || l_col_curr_suffix || ') + sum(f.unearned_discount_amount' || l_col_curr_suffix || ') FII_AR_DISC_TAKEN_AMT
142 			FROM
143 				fii_ar_pmt_schedules_f f,
144 				(select h1.applied_customer_trx_id, sum(h1.amount_applied_trx_prim) amount_applied_trx_prim,
145                                         sum(h1.amount_applied_trx_sec) amount_applied_trx_sec
146                                  from fii_ar_receipts_f h1
147 				 where h1.cash_receipt_id = :CASH_RECEIPT_ID
148 				 and h1.application_status = ''APP''
149 				 and h1.filter_date <= :ASOF_DATE
150 				 and h1.applied_customer_trx_id IS NOT NULL
151 				 group by h1.applied_customer_trx_id) h
152 			WHERE
153 				f.customer_trx_id = h.applied_customer_trx_id
154 				AND f.filter_date <= :ASOF_DATE
155 			GROUP BY f.customer_trx_id, f.order_ref_number, f.bill_to_customer_id, f.transaction_number, f.class, f.org_id,
156 				f.cust_trx_type_id, f.TRX_DATE, f.GL_DATE, f.invoice_currency_code, f.term_id, f.batch_source_id
157 			UNION ALL
158 			SELECT
159 				f.customer_trx_id, f.org_id, f.cust_trx_type_id, f.term_id, f.batch_source_id, f.bill_to_customer_id,
160 				f.invoice_currency_code,
161 				f.order_ref_number order_ref_number,
162 				f.transaction_number FII_AR_TRAN_NUM,
163 				f.class FII_AR_TRAN_CLASS,
164 				f.TRX_DATE FII_AR_TRAN_DATE,
165 				f.GL_DATE FII_AR_GL_DATE,
166 				min(f.DUE_DATE) FII_AR_FIRST_DUE_DATE,
167 				sum(f.amount_due_original_trx) FII_AR_TRAN_AMT,
168 				sum(f.amount_due_original' || l_col_curr_suffix || ') FII_AR_ORIG_AMT,
169 				sum(h.amount_applied_trx' || l_col_curr_suffix || ') FII_AR_PAID_AMT,
170 				NULL FII_AR_ADJUST_AMT,
171 				NULL FII_AR_BALANCE_AMT,
172 				sum(f.earned_discount_amount' || l_col_curr_suffix || ') + sum(f.unearned_discount_amount' || l_col_curr_suffix || ') FII_AR_DISC_TAKEN_AMT
173 			FROM
174 				fii_ar_pmt_schedules_f f,
175 				(select h1.payment_schedule_id, sum(h1.amount_applied_trx_prim) amount_applied_trx_prim,
176                                         sum(h1.amount_applied_trx_sec) amount_applied_trx_sec
177                                  from fii_ar_receipts_f h1
178 				 where h1.cash_receipt_id = :CASH_RECEIPT_ID
179 				 and h1.application_status = ''APP''
180 				 and h1.filter_date <= :ASOF_DATE
181 				 and h1.applied_customer_trx_id IS NULL
182 				 group by h1.payment_schedule_id) h
183 			WHERE
184 				f.payment_schedule_id = h.payment_schedule_id
185 				AND f.filter_date <= :ASOF_DATE
186 			GROUP BY f.customer_trx_id, f.order_ref_number, f.bill_to_customer_id, f.transaction_number, f.class, f.org_id,
187 				f.cust_trx_type_id, f.TRX_DATE, f.GL_DATE, f.invoice_currency_code, f.term_id, f.batch_source_id
188 			UNION ALL
189 			SELECT
190 				f.customer_trx_id, NULL org_id, NULL cust_trx_type_id, NULL term_id, NULL batch_source_id, NULL bill_to_customer_id,
191 				NULL invoice_currency_code,
192 				NULL order_ref_number,
193 				NULL FII_AR_TRAN_NUM,
194 				NULL FII_AR_TRAN_CLASS,
195 				NULL FII_AR_TRAN_DATE,
196 				NULL FII_AR_GL_DATE,
197 				NULL FII_AR_FIRST_DUE_DATE,
198 				NULL FII_AR_TRAN_AMT,
199 				NULL FII_AR_ORIG_AMT,
200 				NULL FII_AR_PAID_AMT,
201 				decode(ag.adjustment_id, NULL, NULL, sum(ag.current_bucket_1_amount' || l_col_curr_suffix || ')
202 					+ sum(ag.current_bucket_2_amount' || l_col_curr_suffix || ')
203 					+ sum(ag.current_bucket_3_amount' || l_col_curr_suffix || ')
204 					+ sum(ag.past_due_bucket_1_amount' || l_col_curr_suffix || ')
205 					+ sum(ag.past_due_bucket_2_amount' || l_col_curr_suffix || ')
206 					+ sum(ag.past_due_bucket_3_amount' || l_col_curr_suffix || ')
207 					+ sum(ag.past_due_bucket_4_amount' || l_col_curr_suffix || ')
208 					+ sum(ag.past_due_bucket_5_amount' || l_col_curr_suffix || ')
209 					+ sum(ag.past_due_bucket_6_amount' || l_col_curr_suffix || ')
210 					+ sum(ag.past_due_bucket_7_amount' || l_col_curr_suffix || ')) FII_AR_ADJUST_AMT,
211 				decode(f.class, ''CM'', sum(ag.on_acct_credit_amount' || l_col_curr_suffix || '),
212 					sum(ag.current_bucket_1_amount' || l_col_curr_suffix || '
213 					+ ag.current_bucket_2_amount' || l_col_curr_suffix || '
214 					+ ag.current_bucket_3_amount' || l_col_curr_suffix || '
215 					+ ag.past_due_bucket_1_amount' || l_col_curr_suffix || '
216 					+ ag.past_due_bucket_2_amount' || l_col_curr_suffix || '
217 					+ ag.past_due_bucket_3_amount' || l_col_curr_suffix || '
218 					+ ag.past_due_bucket_4_amount' || l_col_curr_suffix || '
219 					+ ag.past_due_bucket_5_amount' || l_col_curr_suffix || '
220 					+ ag.past_due_bucket_6_amount' || l_col_curr_suffix || '
221 					+ ag.past_due_bucket_7_amount' || l_col_curr_suffix || ')) FII_AR_BALANCE_AMT,
222 				NULL FII_AR_DISC_TAKEN_AMT
223 			FROM
224 				fii_ar_pmt_schedules_f f,
225 				fii_ar_aging_receivables ag,
226 				(select h1.applied_customer_trx_id
227                                  from fii_ar_receipts_f h1
228 				 where h1.cash_receipt_id = :CASH_RECEIPT_ID
229 				 and h1.application_status = ''APP''
230 				 and h1.filter_date <= :ASOF_DATE
231 				 and h1.applied_customer_trx_id IS NOT NULL
232 				 group by h1.applied_customer_trx_id) h
233 
234 			WHERE
235 				f.customer_trx_id = h.applied_customer_trx_id
236 				AND ag.event_date <= :ASOF_DATE
237 				AND f.payment_schedule_id = ag.payment_schedule_id
238 				AND f.filter_date <= :ASOF_DATE
239 				GROUP BY f.customer_trx_id, ag.adjustment_id, f.class
240 			UNION ALL
241 			SELECT
242 				f.customer_trx_id, NULL org_id, NULL cust_trx_type_id, NULL term_id, NULL batch_source_id, NULL bill_to_customer_id,
243 				NULL invoice_currency_code,
244 				NULL order_ref_number,
245 				NULL FII_AR_TRAN_NUM,
246 				NULL FII_AR_TRAN_CLASS,
247 				NULL FII_AR_TRAN_DATE,
248 				NULL FII_AR_GL_DATE,
249 				NULL FII_AR_FIRST_DUE_DATE,
250 				NULL FII_AR_TRAN_AMT,
251 				NULL FII_AR_ORIG_AMT,
252 				NULL FII_AR_PAID_AMT,
253 				decode(ag.adjustment_id, NULL, NULL, sum(ag.current_bucket_1_amount' || l_col_curr_suffix || ')
254 					+ sum(ag.current_bucket_2_amount' || l_col_curr_suffix || ')
255 					+ sum(ag.current_bucket_3_amount' || l_col_curr_suffix || ')
256 					+ sum(ag.past_due_bucket_1_amount' || l_col_curr_suffix || ')
257 					+ sum(ag.past_due_bucket_2_amount' || l_col_curr_suffix || ')
258 					+ sum(ag.past_due_bucket_3_amount' || l_col_curr_suffix || ')
259 					+ sum(ag.past_due_bucket_4_amount' || l_col_curr_suffix || ')
260 					+ sum(ag.past_due_bucket_5_amount' || l_col_curr_suffix || ')
261 					+ sum(ag.past_due_bucket_6_amount' || l_col_curr_suffix || ')
262 					+ sum(ag.past_due_bucket_7_amount' || l_col_curr_suffix || ')) FII_AR_ADJUST_AMT,
263 				decode(f.class, ''CM'', sum(ag.on_acct_credit_amount' || l_col_curr_suffix || '),
264 					sum(ag.current_bucket_1_amount' || l_col_curr_suffix || '
265 					+ ag.current_bucket_2_amount' || l_col_curr_suffix || '
266 					+ ag.current_bucket_3_amount' || l_col_curr_suffix || '
267 					+ ag.past_due_bucket_1_amount' || l_col_curr_suffix || '
268 					+ ag.past_due_bucket_2_amount' || l_col_curr_suffix || '
269 					+ ag.past_due_bucket_3_amount' || l_col_curr_suffix || '
270 					+ ag.past_due_bucket_4_amount' || l_col_curr_suffix || '
271 					+ ag.past_due_bucket_5_amount' || l_col_curr_suffix || '
272 					+ ag.past_due_bucket_6_amount' || l_col_curr_suffix || '
273 					+ ag.past_due_bucket_7_amount' || l_col_curr_suffix || ')) FII_AR_BALANCE_AMT,
274 				NULL FII_AR_DISC_TAKEN_AMT
275 			FROM
276 				fii_ar_pmt_schedules_f f,
277 				fii_ar_aging_receivables ag,
278 				(select h1.payment_schedule_id
279                                  from fii_ar_receipts_f h1
280 				 where h1.cash_receipt_id = :CASH_RECEIPT_ID
281 				 and h1.application_status = ''APP''
282 				 and h1.filter_date <= :ASOF_DATE
283 				 and h1.applied_customer_trx_id IS NULL
284 				 group by h1.payment_schedule_id) h
285 			WHERE
286 				f.payment_schedule_id = h.payment_schedule_id
287 				AND ag.event_date <= :ASOF_DATE
288 				AND f.payment_schedule_id = ag.payment_schedule_id
289 				AND f.filter_date <= :ASOF_DATE
290 				GROUP BY f.customer_trx_id, ag.adjustment_id, f.class
291 		) inner_query
292 		GROUP BY inner_query.customer_trx_id
293 	)outer_inner_query,
294 		ar_lookups lk,
295 		ra_cust_trx_types_all ractt,
296 		ra_terms_tl ratt,
297 		ra_batch_sources_all rabs,
298 		hz_cust_accounts hzca
299 	WHERE
300 		outer_inner_query.FII_AR_TRAN_CLASS = lk.lookup_code
301 		AND lk.lookup_type= ''INV/CM/ADJ''
302 		AND outer_inner_query.cust_trx_type_id = ractt.cust_trx_type_id(+)
303 		AND outer_inner_query.FII_AR_TRAN_CLASS = ractt.type(+)
304 		AND outer_inner_query.org_id = ractt.org_id(+)
305 		AND outer_inner_query.term_id = ratt.term_id(+)
306 		AND ratt.language(+) = USERENV(''LANG'')
307 		AND outer_inner_query.batch_source_id = rabs.batch_source_id(+)
308 		AND outer_inner_query.org_id = rabs.org_id(+)
309 		AND outer_inner_query.bill_to_customer_id = hzca.cust_account_id
310 	' || l_order_by;
311 
312 	-- Calling the bind_variable API
313 	fii_ar_util_pkg.bind_variable(
314 		p_sqlstmt 		=> sqlstmt,
315 		p_page_parameter_tbl 	=> p_page_parameter_tbl,
316 		p_sql_output 		=> p_paid_rec_dtl_sql,
317 		p_bind_output_table 	=> p_paid_rec_dtl_output
318 	);
319 
320 END get_paid_rec_dtl;
321 
322 END fii_ar_paid_rec_dtl_pkg;
323