DBA Data[Home] [Help]

PACKAGE BODY: APPS.FII_AR_TRAN_DETAIL

Source


1 PACKAGE BODY FII_AR_TRAN_DETAIL AS
2 /* $Header: FIIARDBITDB.pls 120.32.12000000.2 2007/04/09 20:25:44 vkazhipu ship $ */
3 
4 
5 
6 PROCEDURE get_tran_detail (
7         p_page_parameter_tbl    IN  BIS_PMV_PAGE_PARAMETER_TBL,
8         tran_detail_sql             OUT NOCOPY VARCHAR2,
9         tran_detail_output          OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
10 IS
11 
12 l_select		VARCHAR2(15000);
13 l_party_where 		VARCHAR2(1000);
14 l_collector_from 	VARCHAR2(100);
15 l_collector_where	VARCHAR2(1000);
16 l_industry_from		VARCHAR2(100);
17 l_industry_where	VARCHAR2(1000);
18 l_func_where		VARCHAR2(1000);
19 l_cust_acc_where	VARCHAR2(250);
20 l_bucket		VARCHAR2(2);
21 l_bal_select		VARCHAR2(1000);
22 l_bal_where		VARCHAR2(100);
23 l_tran_num_drill	VARCHAR2(500);
24 l_first_due_date_drill	VARCHAR2(500);
25 l_balance_drill		VARCHAR2(5000);
26 l_source_drill		VARCHAR2(500);
27 l_order_by              VARCHAR2(250);
28 l_order_column          VARCHAR2(250);
29 l_inag_rng		VARCHAR2(250);
30 
31 
32 BEGIN
33 
34 fii_ar_util_pkg.reset_globals;
35 fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
36 fii_ar_util_pkg.g_view_by := 'ORGANIZATION+FII_OPERATING_UNITS';
37 fii_ar_util_pkg.populate_summary_gt_tables;
38 
39 IF fii_ar_util_pkg.g_party_id = '-111' THEN
40 	l_party_where := ' ';
41 ELSE
42  IF fii_ar_util_pkg.g_count_parent_party_id > 1 THEN
43    l_party_where := ' AND hz_cust.party_id IN ('||fii_ar_util_pkg.g_party_id||') AND hz_cust.party_id = gt.party_id ';
44  ELSE
45    l_party_where := ' AND hz_cust.party_id =  :PARTY_ID AND hz_cust.party_id =
46 gt.party_id ';
47  END IF;
48 END IF;
49 
50 
51 IF fii_ar_util_pkg.g_collector_id = '-111' THEN
52 	l_collector_where := ' ';
53 	l_collector_from := ' ';
54 ELSE
55 	l_collector_from := ' ,fii_collectors coll ';
56 	l_collector_where := ' AND coll.collector_id = gt.collector_id
57 AND coll.site_use_id = f.bill_to_site_use_id
58 AND coll.cust_account_id =  f.bill_to_customer_id ';
59 
60 END IF;
61 
62 IF fii_ar_util_pkg.g_industry_id = '-111' THEN
63 	l_industry_where := ' ';
64 	l_industry_from := ' ';
65 ELSE
66 	l_industry_from := ' ,fii_party_mkt_class ind ';
67 	l_industry_where := ' AND ind.class_code = gt.class_code
68 AND ind.party_id =  hz_cust.party_id
69 AND hz_cust.cust_account_id = f.bill_to_customer_id ';
70 
71 END IF;
72 
73 IF fii_ar_util_pkg.g_cust_account IS NULL OR
74 fii_ar_util_pkg.g_cust_account = '-111' THEN
75   l_cust_acc_where := ' ';
76 ELSE
77   l_cust_acc_where :=  ' AND f.bill_to_customer_id= :CUST_ACCOUNT  ';
78 END IF;
79 
80 l_inag_rng := 'NULL ';
81 
82 IF fii_ar_util_pkg.g_function_name = 'FII_AR_OPEN_REC_DTL' THEN
83 /* Open Receivables Detail*/
84 l_func_where := ' AND f.class IN (''INV'',''DM'',''CB'',''DEP'',''BR'') ';
85 
86 l_bal_select :=
87 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
88 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
89 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
90 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
91 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
92 
93 l_bal_where := ' AND FII_AR_BALANCE_AMT <>0 ';
94 
95 ELSIF  fii_ar_util_pkg.g_function_name = 'FII_AR_PDUE_REC_DTL' THEN
96 /* Past Due Receivable Detail */
97 l_func_where := ' AND f.class IN (''INV'',''DM'',''CB'',''DEP'',''BR'') ';
98 
99 l_inag_rng := '
100 SUM(r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
101 +r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
102 +r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
103 +r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||
104 ') ';
105 
106 l_bal_select :=
107 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
108 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
109 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
110 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
111 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
112 
113 l_bal_where := ' AND FII_AR_INAGE_RNG_AMT  <>0 ';
114 
115 ELSIF  fii_ar_util_pkg.g_function_name = 'FII_AR_CURR_REC_DTL' THEN
116 /* Current  Receivable Detail */
117 l_func_where := ' AND f.class IN (''INV'',''DM'',''CB'',''DEP'',''BR'') ';
118 
119 l_inag_rng := '
120 SUM(r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
121 + r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||
122 ') ';
123 
124 l_bal_select :=
125 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
126 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
127 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
128 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
129 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
130 
131 l_bal_where := ' AND FII_AR_INAGE_RNG_AMT <>0 ';
132 
133 ELSIF  fii_ar_util_pkg.g_function_name = 'FII_AR_REC_PDUE_BUCKET' THEN
134 
135 l_bucket := fii_ar_util_pkg.g_bucket_num;
136 
137 /* Receivable Bucket X Days Past Due*/
138 l_func_where := ' AND f.class IN (''INV'',''DM'',''CB'',''DEP'',''BR'') ';
139 l_inag_rng := ' SUM(r.past_due_bucket_'||l_bucket||'_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
140 
141 l_bal_select :=
142 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
143 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
144 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
145 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
146 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
147 
148 l_bal_where := ' AND FII_AR_INAGE_RNG_AMT <> 0 ';
149 
150 ELSIF fii_ar_util_pkg.g_function_name = 'FII_AR_REC_DUE_BUCKET' THEN
151 
152 l_bucket := fii_ar_util_pkg.g_bucket_num;
153 
154 /* Receivable Due in Bucket X Days*/
155 l_func_where := ' AND f.class IN (''INV'',''DM'',''CB'',''DEP'',''BR'') ';
156 l_inag_rng := ' SUM(r.current_bucket_'||l_bucket||'_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
157 
158 l_bal_select :=
159 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
160 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
161 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
162 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
163 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
164 
165 l_bal_where := ' AND FII_AR_INAGE_RNG_AMT <> 0 ';
166 
167 ELSIF  fii_ar_util_pkg.g_function_name = 'FII_AR_OPEN_INV_DTL' THEN
168 /* Invoices Detail */
169 
170 l_func_where := ' AND f.class = ''INV'' ';
171 
172 --l_inag_rng := ' SUM(r.past_due_bucket_'||l_bucket||'_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
173 
174 l_bal_select :=
175 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
176 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
177 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
178 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
179 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
180 
181 l_bal_where := ' AND FII_AR_BALANCE_AMT <>0 ';
182 
183 ELSIF  fii_ar_util_pkg.g_function_name = 'FII_AR_OPEN_DM_DTL' THEN
184 /* Debit Memo Detail */
185 
186 l_func_where :=' AND f.class = ''DM'' ';
187 
188 l_bal_select :=
189 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
190 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
191 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
192 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
193 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
194 
195 l_bal_where := ' AND FII_AR_BALANCE_AMT <>0 ';
196 
197 ELSIF  fii_ar_util_pkg.g_function_name = 'FII_AR_OPEN_CB_DTL' THEN
198 /* Charge Back  Detail */
199 l_func_where := ' AND f.class = ''CB'' ';
200 
201 l_bal_select :=
202 ' (r.current_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.current_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
203 +r.current_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_1_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
204 +r.past_due_bucket_2_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_3_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
205 +r.past_due_bucket_4_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_5_amount'||fii_ar_util_pkg.g_col_curr_suffix||'
206 +r.past_due_bucket_6_amount'||fii_ar_util_pkg.g_col_curr_suffix||'+r.past_due_bucket_7_amount'||fii_ar_util_pkg.g_col_curr_suffix||') ';
207 
208 l_bal_where := ' AND FII_AR_BALANCE_AMT <>0 ';
209 
210 ELSE
211 l_func_where := NULL;
212 l_bal_select := NULL;
213 l_bal_where := NULL;
214 
215 END IF;
216 
217 l_tran_num_drill :=' DECODE(f.class,''INV'', '||
218 '''pFunctionName=ARBPA_TM_REAL_PREVIEW&retainBN=Y&retainAM=Y&addBreadCrumb=Y&TermsSequenceNumber=FII_AR_TERM_SEQ_NUM'||
219 '&CustomerTrxId=FII_AR_CUST_TRX_ID&orgId=''||f.org_id||''&pParamIds=Y'','''') ';
220 
221 l_first_due_date_drill :=
222 'pFunctionName=FII_AR_SCHD_PMT_DISCNT&FII_AR_CUST_TRX_ID=FII_AR_CUST_TRX_ID&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_CURRENCIES='||'''||FII_AR_TRAN_CURR||'''||
223 '&BIS_PMV_DRILL_CODE_FII_AR_CUST_ACCOUNT=FII_AR_ACCT_NUM&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y';
224 
225 l_balance_drill :=
226 'DECODE(f.class,''INV'',
227 ''pFunctionName=FII_AR_INV_ACT_HISTORY&FII_AR_CUST_TRX_ID=FII_AR_CUST_TRX_ID&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=FII_AR_TRAN_CLASS_CODE&FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM
228 &FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y'',
229 ''BR'',
230 ''pFunctionName=FII_AR_BR_ACT_HISTORY&FII_AR_CUST_TRX_ID=FII_AR_CUST_TRX_ID&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=FII_AR_TRAN_CLASS_CODE&FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM
231 &FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y'',
232 ''CB'',
233 ''pFunctionName=FII_AR_CB_ACT_HISTORY&FII_AR_CUST_TRX_ID=FII_AR_CUST_TRX_ID&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=FII_AR_TRAN_CLASS_CODE&FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM
234 &FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y'',
235 ''CM'',
236 ''pFunctionName=FII_AR_CM_ACT_HISTORY&FII_AR_CUST_TRX_ID=FII_AR_CUST_TRX_ID&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=FII_AR_TRAN_CLASS_CODE&FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM
237 &FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y'',
238 ''DEP'',
239 ''pFunctionName=FII_AR_DEP_ACT_HISTORY&FII_AR_CUST_TRX_ID=FII_AR_CUST_TRX_ID&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=FII_AR_TRAN_CLASS_CODE&FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM
240 &FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y'',
241 ''DM'',
242 ''pFunctionName=FII_AR_DM_ACT_HISTORY&FII_AR_CUST_TRX_ID=FII_AR_CUST_TRX_ID&FII_AR_TRAN_NUM=FII_AR_TRAN_NUM&FII_AR_TRAN_CLASS=FII_AR_TRAN_CLASS_CODE&FII_AR_ACCOUNT_NUM=FII_AR_ACCT_NUM
243 &FII_AR_TRAN_CURR=FII_AR_TRAN_CURR&BIS_PMV_DRILL_CODE_AS_OF_DATE=sysdate&pParamIds=Y'', '' '' )
244 ';
245 
246 l_source_drill := ' DECODE(f.order_ref_number,NULL,'''', ''pFunctionName=ONT_PORTAL_ORDERDETAILS&HeaderId=''||(select ooh.header_id
247 				from oe_order_headers_all ooh
248 				where f.order_ref_number    =
249 to_char(ooh.order_number) and rownum=1  ) ) ';
250 
251 IF INSTR(fii_ar_util_pkg.g_order_by,',') <> 0 AND
252 INSTR(SUBSTR(fii_ar_util_pkg.g_order_by,1,25),'FII_AR_BALANCE_AMT DESC') <> 0 THEN
253 
254    l_order_by := 'ORDER BY NVL(FII_AR_BALANCE_AMT, -999999999) DESC';
255 
256 ELSIF instr(fii_ar_util_pkg.g_order_by, ' DESC') <> 0 THEN
257    l_order_column := substr(fii_ar_util_pkg.g_order_by,1,instr(fii_ar_util_pkg.g_order_by, ' DESC'));
258 
259    IF INSTR(fii_ar_util_pkg.g_order_by,'DATE') = 0 AND
260       INSTR(fii_ar_util_pkg.g_order_by, 'BINARY') = 0 THEN
261    l_order_by := 'ORDER BY NVL('|| l_order_column ||', -999999999) DESC';
262    ELSIF  INSTR(fii_ar_util_pkg.g_order_by, 'BINARY') <> 0 THEN
263    l_order_column := SUBSTR(fii_ar_util_pkg.g_order_by,
264 	INSTR(fii_ar_util_pkg.g_order_by,'FII'), INSTR(fii_ar_util_pkg.g_order_by,',') -
265 	INSTR(fii_ar_util_pkg.g_order_by,'FII'));
266    l_order_by := 'ORDER BY NVL(' || l_order_column || ', ''    '') DESC';
267    ELSE
268    l_order_by := 'ORDER BY '|| l_order_column ||' DESC';
269    END IF;
270 
271 ELSE
272    l_order_by := '&ORDER_BY_CLAUSE';
273 END IF;
274 
275 
276 l_select := '
277 SELECT
278 FII_AR_CUST_TRX_ID,
279 FII_AR_ACCT_NUM,
280 FII_AR_TRAN_NUM,
281 FII_AR_TERM_SEQ_NUM,
282 NULL FII_AR_ORD_HDR_ID,
283 FII_AR_TRAN_CLASS,
284 FII_AR_TRAN_CLASS_CODE,
285 FII_AR_TRAN_TYPE,
286 FII_AR_TRAN_DATE,
287 FII_AR_GL_DATE,
288 FII_AR_FIRST_DUE_DATE,
289 FII_AR_TRAN_CURR,
290 FII_AR_TRAN_AMT,
291 FII_AR_ORIG_AMT,
292 FII_AR_INAGE_RNG_AMT,
293 FII_AR_BALANCE_AMT,
294 FII_AR_DISPUTE_AMT,
295 FII_AR_TERMS,
296 FII_AR_SOURCE,
297 SUM(FII_AR_ORIG_AMT) over()     FII_AR_ORIG_AMT_GT,
298 SUM(FII_AR_INAGE_RNG_AMT) over() FII_AR_INAGE_RNG_AMT_GT,
299 SUM(FII_AR_BALANCE_AMT) over()  FII_AR_BALANCE_AMT_GT,
300 SUM(FII_AR_DISPUTE_AMT) over()  FII_AR_DISPUTE_AMT_GT,
301 FII_AR_TRAN_NUM_DRILL,
302 FII_AR_FIRST_DUE_DATE_DRILL,
303 FII_AR_BALANCE_AMT_DRILL,
304 FII_AR_SOURCE_DRILL
305 FROM
306 (
307 SELECT
308 f.customer_trx_id 	FII_AR_CUST_TRX_ID,
309 FII_AR_ACCT_NUM,
310 FII_AR_TRAN_NUM,
311 rlk.meaning             FII_AR_TRAN_CLASS,
312 f.class			FII_AR_TRAN_CLASS_CODE,
313 ctype.description       FII_AR_TRAN_TYPE,
314 FII_AR_TERM_SEQ_NUM,
315 FII_AR_TRAN_DATE,
316 FII_AR_GL_DATE,
317 FII_AR_FIRST_DUE_DATE,
318 FII_AR_TRAN_CURR,
319 FII_AR_TRAN_AMT,
320 FII_AR_ORIG_AMT,
321 FII_AR_INAGE_RNG_AMT,
322 FII_AR_BALANCE_AMT,
323 FII_AR_DISPUTE_AMT,
324 rterm.description       FII_AR_TERMS,
325 rsource.description     FII_AR_SOURCE,
326 '||l_tran_num_drill||' FII_AR_TRAN_NUM_DRILL,
327 '''||l_first_due_date_drill||''' FII_AR_FIRST_DUE_DATE_DRILL,
328 '||l_balance_drill||' FII_AR_BALANCE_AMT_DRILL,
329 '||l_source_drill||' FII_AR_SOURCE_DRILL
330 FROM
331 (SELECT customer_trx_id,SUM(current_dispute_amount_prim+past_due_dispute_amount_prim) FII_AR_DISPUTE_AMT
332 FROM fii_ar_aging_disputes d
333 WHERE event_date <= :ASOF_DATE GROUP BY customer_trx_id) d,
334 ra_cust_trx_types_all ctype,
335 ar_lookups rlk,
336 ra_terms_tl rterm,
337 ra_batch_sources_all rsource,
338 (select /*+ leading(gt) cardinality(gt 1) */
339 f.customer_trx_id,
340 hz_cust.account_number          FII_AR_ACCT_NUM,
341 f.transaction_number            FII_AR_TRAN_NUM,
342 f.class,
343 1 				FII_AR_TERM_SEQ_NUM,
344 f.order_ref_number,
345 f.cust_trx_type_id,
346 MIN(f.trx_date)                      FII_AR_TRAN_DATE,
347 MIN(f.gl_date)                       FII_AR_GL_DATE,
348 MIN(f.due_date)                 FII_AR_FIRST_DUE_DATE,
349 f.invoice_currency_code  	FII_AR_TRAN_CURR,
350 f.invoice_currency_code||'' ''||TO_CHAR(SUM(case when aging_flag = ''N'' and action = ''Transaction''
351                                                    then f.amount_due_original_trx
352                                                  else 0 end),''999,999,999,999'') FII_AR_TRAN_AMT,
353 SUM(case when aging_flag = ''N'' and action = ''Transaction''
354              then f.amount_due_original'||fii_ar_util_pkg.g_col_curr_suffix||'
355          else 0 end) FII_AR_ORIG_AMT,
356  '||l_inag_rng||' FII_AR_INAGE_RNG_AMT,
357 SUM( '||l_bal_select||') FII_AR_BALANCE_AMT,
358 f.term_id,
359 f.batch_source_id,
360 f.bill_to_customer_id,
361 f.org_id
362 FROM
363 fii_ar_pmt_schedules_f f,
364 fii_ar_aging_receivables r,
365 hz_cust_accounts hz_cust,
366 fii_ar_summary_gt gt
367 '||l_collector_from||l_industry_from||'
368 WHERE f.bill_to_customer_id= hz_cust.cust_account_id
369 AND f.filter_date <=  :ASOF_DATE
370 AND r.event_date  <=  :ASOF_DATE
371 AND f.payment_schedule_id = r.payment_schedule_id
372 AND f.org_id = r.org_id
373 AND f.org_id = gt.org_id
374 '||l_party_where||l_cust_acc_where||l_collector_where||l_industry_where||l_func_where||'
375 group by f.customer_trx_id,f.org_id, f.transaction_number, f.class,
376 f.order_ref_number,
377 f.cust_trx_type_id,
378 f.invoice_currency_code,
379 f.term_id, f.batch_source_id,
380 f.bill_to_customer_id, hz_cust.account_number
381  ) f
382 WHERE f.cust_trx_type_id = ctype.cust_trx_type_id
383  '||l_bal_where||'
384 AND f.org_id=ctype.org_id
385 AND f.customer_trx_id = d.customer_trx_id(+)
386 AND f.class = rlk.lookup_code
387 AND rlk.lookup_type= ''INV/CM/ADJ''
388 AND f.term_id = rterm.term_id(+)
389 AND DECODE(rterm.term_id, NULL, USERENV(''LANG''),rterm.language) = USERENV(''LANG'')
390 AND f.batch_source_id = rsource.batch_source_id
391 AND f.org_id=rsource.org_id
392 ) '||l_order_by||'
393 ';
394 
395 fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, tran_detail_sql, tran_detail_output);
396 
397 END get_tran_detail;
398 
399 END FII_AR_TRAN_DETAIL;
400