[Home] [Help]
PACKAGE BODY: APPS.ARRX_OTH
Source
1 PACKAGE BODY ARRX_OTH as
2 /* $Header: ARRXOTHB.pls 120.8 2006/07/25 12:05:51 ggadhams noship $ */
3
4 --
5 -- Main Other Receipt Applications Report function
6 --
7 procedure oth_rec_app (
8 request_id in number,
9 p_reporting_level in number,
10 p_reporting_entity in number,
11 p_sob_id in number,
12 p_coa_id in number,
13 p_co_seg_low in varchar2,
14 p_co_seg_high in varchar2,
15 p_gl_date_low in date,
16 p_gl_date_high in date,
17 p_currency_code in varchar2,
18 p_customer_name_low in varchar2,
19 p_customer_name_high in varchar2,
20 p_customer_number_low in varchar2,
21 p_customer_number_high in varchar2,
22 p_receipt_date_low in date,
23 p_receipt_date_high in date,
24 p_apply_date_low in date,
25 p_apply_date_high in date,
26 p_remit_batch_low in varchar2,
27 p_remit_batch_high in varchar2,
28 p_receipt_batch_low in varchar2,
29 p_receipt_batch_high in varchar2,
30 p_receipt_number_low in varchar2,
31 p_receipt_number_high in varchar2,
32 p_app_type in varchar2,
33 retcode out NOCOPY number,
34 errbuf out NOCOPY varchar2)
35 is
36
37 begin
38
39 -- Asssign parameters to global variable
40 -- These values will be used within the before_report trigger
41
42 var.p_reporting_level := p_reporting_level;
43 var.p_reporting_entity_id := p_reporting_entity;
44 var.p_sob_id := p_sob_id;
45 var.request_id := request_id;
46 var.p_coa_id := p_coa_id;
47 var.p_gl_date_low := p_gl_date_low;
48 var.p_gl_date_high := p_gl_date_high;
49 var.p_currency_code := p_currency_code;
50 var.p_co_seg_low := p_co_seg_low;
51 var.p_co_seg_high := p_co_seg_high;
52 var.p_customer_name_low := p_customer_name_low;
53 var.p_customer_name_high := p_customer_name_high;
54 var.p_customer_number_low := p_customer_number_low;
55 var.p_customer_number_high := p_customer_number_high;
56 var.p_receipt_date_low := p_receipt_date_low;
57 var.p_receipt_date_high := p_receipt_date_high;
58 var.p_apply_date_low := p_apply_date_low;
59 var.p_apply_date_high := p_apply_date_high;
60 var.p_remit_batch_low := p_remit_batch_low;
61 var.p_remit_batch_high := p_remit_batch_high;
62 var.p_receipt_batch_low := p_receipt_batch_low;
63 var.p_receipt_batch_high := p_receipt_batch_high;
64 var.p_receipt_number_low := p_receipt_number_low;
65 var.p_receipt_number_high := p_receipt_number_high;
66 var.p_app_type := p_app_type;
67
68
69 --Bug 5373461
70 if p_reporting_level = 1000 then
71 var.books_id := p_reporting_entity;
72 elsif p_reporting_level =3000 then
73 select set_of_books_id
74 into var.books_id
75 from ar_system_parameters_all
76 where org_id = p_reporting_entity;
77 end if;
78 --
79 -- Initialize request
80 fa_rx_util_pkg.init_request('arrx_oth.oth_rec_app',request_id,'AR_RECEIPTS_REP_ITF');
81
82 --
83 -- Assign report triggers for this report.
84 -- This report has one section called AR OTHERREC
85 -- NOTE:
86 -- before_report is assigned 'arrx_oth.before_report;'
87 -- bind is assigned 'arrx_rc.bind(:CURSOR_SELECT);'
88 -- Each trigger event is assigned with the full procedure name (including package name).
89 -- They end with a ';'.
90 -- The bind trigger requires one host variable ':CURSOR_SELECT'.
91 fa_rx_util_pkg.assign_report('AR OTHERREC',
92 true,
93 'arrx_oth.before_report;',
94 'arrx_oth.bind(:CURSOR_SELECT);',
95 'arrx_oth.after_fetch;',
96 null);
97
98 --
99 -- Run the report. Make sure to pass as parameter the same
100 -- value passed to p_calling_proc in init_request().
101 fa_rx_util_pkg.run_report('arrx_oth.oth_rec_app', retcode, errbuf);
102
103 fa_rx_util_pkg.debug('arrx_oth.oth_rec_app()-');
104
105 exception
106 when others then
107 fa_rx_util_pkg.log(sqlcode);
108 fa_rx_util_pkg.log(sqlerrm);
109 fa_rx_util_pkg.debug(sqlcode);
110 fa_rx_util_pkg.debug(sqlerrm);
111 fa_rx_util_pkg.debug('arrx_oth.oth_rec_app(EXCEPTION)-');
112 end oth_rec_app;
113
114
115 -- This is the before trigger for the main Adj Report ---
116
117 procedure before_report
118 is
119 CO_SEG_WHERE varchar2(500);
120 GL_DATE_WHERE varchar2(500);
121 CURRENCY_CODE_WHERE varchar2(500);
122 CUSTOMER_NAME_WHERE varchar2(500);
123 CUSTOMER_NUMBER_WHERE varchar2(500);
124 RECEIPT_DATE_WHERE varchar2(500);
125 APPLY_DATE_WHERE varchar2(500);
126 REMIT_BATCH_WHERE varchar2(500);
127 RECEIPT_BATCH_WHERE varchar2(500);
128 RECEIPT_NUMBER_WHERE varchar2(500);
129 APP_TYPE_WHERE varchar2(500);
130
131 ACCT_FLEX varchar2(500);
132 DECODE_ACT_NAME varchar2(500);
133 DECODE_REF_TYPE varchar2(500);
134 DECODE_CURRENCY varchar2(500);
135
136 OPER varchar2(10);
137 OP1 varchar2(25);
138 OP2 varchar2(25);
139
140 SORTBY_DECODE varchar2(200);
141 D_OR_I_DECODE varchar2(200);
142 ADJ_CLASS_DECODE varchar2(200);
143 POSTABLE_DECODE varchar2(100);
144
145 BALANCING_ORDER_BY varchar2(100);
146
147 -- Bug 2099632
148 SHOW_BILL_WHERE varchar2(100);
149 SHOW_BILL_FROM varchar2(100);
150 BILL_FLAG varchar2(1);
151
152 -- Bug 2155885
153 ACCOUNTING_METHOD_FLAG varchar2(30);
154
155 L_APP_ORG_WHERE varchar2(500);
156 L_CR_ORG_WHERE varchar2(500);
157 L_CRH_ORG_WHERE varchar2(500);
158 L_PS_ORG_WHERE varchar2(500);
159 L_CUST_ORG_WHERE varchar2(500);
160 L_BS_ORG_WHERE varchar2(500);
161 L_RB_ORG_WHERE varchar2(500);
162 L_AB_ORG_WHERE varchar2(500);
163 L_BSFIRST_ORG_WHERE varchar2(500);
164 L_CRHFIRST_ORG_WHERE varchar2(500);
165 L_RBFIRST_ORG_WHERE varchar2(500);
166 begin
167
168 fa_rx_util_pkg.debug('arrx_oth.before_report()+');
169
170 --
171 -- Get Profile GL_SET_OF_BKS_ID
172 --
173 fa_rx_util_pkg.debug('GL_GET_PROFILE_BKS_ID');
174
175 --Bug5373461
176 -- var.books_id := arp_global.sysparam.set_of_books_id;
177
178 --
179 -- Get CHART_OF_ACCOUNTS_ID
180 --
181 fa_rx_util_pkg.debug('GL_GET_CHART_OF_ACCOUNTS_ID');
182
183 select CHART_OF_ACCOUNTS_ID,CURRENCY_CODE,NAME
184 into var.chart_of_accounts_id,var.functional_currency_code,var.organization_name
185 from GL_SETS_OF_BOOKS
186 where SET_OF_BOOKS_ID = var.books_id;
187
188 fa_rx_util_pkg.debug('Chart of Accounts ID : '||var.chart_of_accounts_id);
189 fa_rx_util_pkg.debug('Functional Currency : '||var.functional_currency_code);
190 fa_rx_util_pkg.debug('Organization Name : '||var.organization_name);
191
192 XLA_MO_REPORTING_API.Initialize(var.p_reporting_level, var.p_reporting_entity_id, 'AUTO');
193
194 L_APP_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('APP',NULL);
195 L_CR_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CR',NULL);
196 L_CRH_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CRH',NULL);
197 -- L_PS_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('PS',NULL);
198 L_CUST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CUST',NULL);
199 L_BS_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('BS',NULL);
200 L_RB_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('RB',NULL);
201 L_AB_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CBA',NULL);
202 L_BSFIRST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('BSFIRST',NULL);
203 L_CRHFIRST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CRHFIRST',NULL);
204 L_RBFIRST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('RBFIRST',NULL);
205 --
206 -- Figure out NOCOPY the where clause for the parameters
207 --
208 fa_rx_util_pkg.debug('AR_GET_PARAMETERS');
209
210 -- CO_SEG_WHERE clause
211 IF var.p_co_seg_low IS NULL AND var.p_co_seg_high IS NULL THEN
212 OPER := NULL;
213 ELSIF var.p_co_seg_low IS NULL THEN
214 OPER := '<=';
215 OP1 := var.p_co_seg_high;
216 OP2 := NULL;
217 ELSIF var.p_co_seg_high IS NULL THEN
218 OPER := '>=';
219 OP1 := var.p_co_seg_low;
220 OP2 := NULL;
221 ELSE
222 OPER := 'BETWEEN';
223 OP1 := var.p_co_seg_low;
224 OP2 := var.p_co_seg_high;
225 END IF;
226 IF OPER IS NULL THEN
227 CO_SEG_WHERE := NULL;
228 ELSE
229 CO_SEG_WHERE := ' AND '||
230 FA_RX_FLEX_PKG.FLEX_SQL(
231 p_application_id => 101,
232 p_id_flex_code => 'GL#',
233 p_id_flex_num => var.chart_of_accounts_id,
234 p_table_alias => 'glc',
235 p_mode => 'WHERE',
236 p_qualifier => 'GL_BALANCING',
237 p_function => OPER,
238 p_operand1 => OP1,
239 p_operand2 => OP2);
240 END IF;
241
242 -- GL_DATE_WHERE clause
243 IF var.p_gl_date_low IS NULL AND var.p_gl_date_high IS NULL THEN
244 GL_DATE_WHERE := NULL;
245 ELSIF var.p_gl_date_low IS NULL THEN
246 GL_DATE_WHERE := ' AND APP.GL_DATE <= :p_gl_date_high';
247 ELSIF var.p_gl_date_high IS NULL THEN
248 GL_DATE_WHERE := ' AND APP.GL_DATE >= :p_gl_date_low';
249 ELSE
250 GL_DATE_WHERE := ' AND APP.GL_DATE BETWEEN :p_gl_date_low AND :p_gl_date_high';
251 END IF;
252
253 --CURRENCY_CODE where clause
254 IF var.p_currency_code IS NULL THEN
255 CURRENCY_CODE_WHERE := NULL;
256 ELSE
257 CURRENCY_CODE_WHERE := ' AND CR.CURRENCY_CODE = :p_currency_code';
258 END IF;
259
260 -- CUSTOMER_NAME where clause
261 IF var.p_customer_name_low IS NULL AND var.p_customer_name_high IS NULL THEN
262 CUSTOMER_NAME_WHERE := NULL;
263 ELSIF var.p_customer_name_low IS NULL THEN
264 CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME <= :p_customer_name_high';
265 ELSIF var.p_customer_name_high IS NULL THEN
266 CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME >= :p_customer_name_low';
267 ELSE
268 CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME BETWEEN :p_customer_name_low AND :p_customer_name_high';
269 END IF;
270
271 -- CUSTOMER_NUMBER where clause
272 IF var.p_customer_number_low IS NULL AND var.p_customer_number_high IS NULL THEN
273 CUSTOMER_NUMBER_WHERE := NULL;
274 ELSIF var.p_customer_number_low IS NULL THEN
275 CUSTOMER_NUMBER_WHERE := ' AND CUST.ACCOUNT_NUMBER <= :p_customer_number_high';
276 ELSIF var.p_customer_number_high IS NULL THEN
277 CUSTOMER_NUMBER_WHERE := ' AND CUST.ACCOUNT_NUMBER >= :p_customer_number_low';
278 ELSE
279 CUSTOMER_NUMBER_WHERE := ' AND CUST.ACCOUNT_NUMBER BETWEEN :p_customer_number_low AND :p_customer_number_high';
280 END IF;
281
282 -- RECEIPT_DATE where clause
283 IF var.p_receipt_date_low IS NULL AND var.p_receipt_date_high IS NULL THEN
284 RECEIPT_DATE_WHERE := NULL;
285 ELSIF var.p_receipt_date_low IS NULL THEN
286 RECEIPT_DATE_WHERE := ' AND CR.RECEIPT_DATE <= :p_receipt_date_high';
287 --bug 5397276 changed elsif
288 -- ELSIF var.p_customer_number_high IS NULL THEN
289 ELSIF var.p_receipt_date_high IS NULL THEN
290 RECEIPT_DATE_WHERE := ' AND CR.RECEIPT_DATE >= :p_receipt_date_low';
291 ELSE
292 RECEIPT_DATE_WHERE := ' AND CR.RECEIPT_DATE BETWEEN :p_receipt_date_low AND :p_receipt_date_high';
293 END IF;
294
295 -- APPLY_DATE where clause
296 IF var.p_apply_date_low IS NULL AND var.p_apply_date_high IS NULL THEN
297 APPLY_DATE_WHERE := NULL;
298 ELSIF var.p_apply_date_low IS NULL THEN
302 ELSE
299 APPLY_DATE_WHERE := ' AND APP.APPLY_DATE <= :p_apply_date_high';
300 ELSIF var.p_apply_date_high IS NULL THEN
301 APPLY_DATE_WHERE := ' AND APP.APPLY_DATE >= :p_apply_date_low';
303 APPLY_DATE_WHERE := ' AND APP.APPLY_DATE BETWEEN :p_apply_date_low AND :p_apply_date_high';
304 END IF;
305
306 -- REMIT_BATCH where clause
307 IF var.p_remit_batch_low IS NULL and var.p_remit_batch_high IS NULL THEN
308 REMIT_BATCH_WHERE := NULL;
309 ELSIF var.p_remit_batch_low is NULL THEN
310 REMIT_BATCH_WHERE := ' AND RB.NAME <= :p_remit_batch_high';
311 ELSIF var.p_remit_batch_high is NULL THEN
312 REMIT_BATCH_WHERE := ' AND RB.NAME >= :p_remit_batch_low';
313 ELSE
314 REMIT_BATCH_WHERE := ' AND RB.NAME BETWEEN :p_remit_batch_low and :p_remit_batch_high';
315 END IF;
316
317 -- RECEIPT_BATCH where clause
318 IF var.p_receipt_batch_low IS NULL and var.p_receipt_batch_high IS NULL THEN
319 RECEIPT_BATCH_WHERE := NULL;
320 ELSIF var.p_receipt_batch_low is NULL THEN
321 RECEIPT_BATCH_WHERE := ' AND RBFIRST.NAME <= :p_receipt_batch_high';
322 ELSIF var.p_receipt_batch_high is NULL THEN
323 RECEIPT_BATCH_WHERE := ' AND RBFIRST.NAME >= :p_receipt_batch_low';
324 ELSE
325 RECEIPT_BATCH_WHERE := ' AND RBFIRST.NAME BETWEEN :p_receipt_batch_low and :p_receipt_batch_high';
326 END IF;
327
328 -- RECEIPT_NUMBER where clause
329 IF var.p_receipt_number_low IS NULL and var.p_receipt_number_high IS NULL THEN
330 RECEIPT_NUMBER_WHERE := NULL;
331 ELSIF var.p_receipt_number_low is NULL THEN
332 RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER <= :p_receipt_number_high';
333 ELSIF var.p_receipt_number_high is NULL THEN
334 RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER >= :p_receipt_number_low';
335 ELSE
336 RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER BETWEEN :p_receipt_number_low and :p_receipt_number_high';
337 END IF;
338
339 -- APP_TYPE where clause
340 IF var.p_app_type IS NULL THEN
341 APP_TYPE_WHERE := NULL;
342 ELSE
343 APP_TYPE_WHERE := ' AND PS.TRX_NUMBER = :p_app_type';
344 END IF;
345
346 -- DECODE/LONG statements
347 ACCT_FLEX := 'FA_RX_FLEX_PKG.GET_VALUE(101,''GL#'',GLS.CHART_OF_ACCOUNTS_ID, ''ALL'',APP.CODE_COMBINATION_ID) ACCOUNTING_FLEX_FIELD ';
348 DECODE_ACT_NAME := 'DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID), -1, ' ||
349 ' arpt_sql_func_util.get_rec_trx_type(app.receivables_trx_id,''NAME''),NULL)';
350 DECODE_REF_TYPE := 'arpt_sql_func_util.get_lookup_meaning(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, -7, ''AR_PREPAYMENT_TYPE'', ' ||
351 ' ''APPLICATION_REF_TYPE''), APP.APPLICATION_REF_TYPE) ';
352 DECODE_CURRENCY := 'DECODE(:P_ENTERED_CURRENCY,NULL,:P_FUNCTIONAL_CURRENCY,CR.CURRENCY_CODE)';
353
354 -- Assign SELECT list
355 -- sequence, select, field in itf, into, type, len
356 fa_rx_util_pkg.debug('ARTX_ASSIGN_SELECT_LIST');
357
358 fa_rx_util_pkg.assign_column('10',NULL,'ORGANIZATION_NAME',
359 'arrx_oth.var.organization_name','VARCHAR2',50);
360 fa_rx_util_pkg.assign_column('20',NULL,'FUNCTIONAL_CURRENCY_CODE',
361 'arrx_oth.var.functional_currency_code','VARCHAR2',15);
362 fa_rx_util_pkg.assign_column('30',ACCT_FLEX,'ACCOUNTING_FLEXFIELD',
363 'arrx_oth.var.accounting_flexfield','VARCHAR2',4000);
364 fa_rx_util_pkg.assign_column('40','glc.code_combination_id','ACCOUNT_CODE_COMBINATION_ID',
365 'arrx_oth.var.code_combination_id','NUMBER');
366 fa_rx_util_pkg.assign_column('50','AB.BANK_ACCOUNT_NUM','ACCOUNT_NUMBER',
367 'arrx_oth.var.bank_account_number','VARCHAR2',30);
368 fa_rx_util_pkg.assign_column('60','APP.ACCTD_AMOUNT_APPLIED_FROM','ACCTD_AMOUNT_APPLIED_FROM',
369 'arrx_oth.var.acctd_amount_applied_from','NUMBER');
370 fa_rx_util_pkg.assign_column('70','APP.ACCTD_AMOUNT_APPLIED_TO','ACCTD_AMOUNT_APPLIED_TO',
371 'arrx_oth.var.acctd_amount_applied_to','NUMBER');
372 fa_rx_util_pkg.assign_column('80',DECODE_ACT_NAME ,'ACTIVITY_NAME',
373 'arrx_oth.var.activity_name','VARCHAR2',50);
374 fa_rx_util_pkg.assign_column('90','APP.AMOUNT_APPLIED','AMOUNT_APPLIED',
375 'arrx_oth.var.amount_applied','NUMBER');
376 fa_rx_util_pkg.assign_column('100','APP.APPLICATION_REF_NUM','APPLICATION_REF_NUMBER',
377 'arrx_oth.var.application_ref_num','VARCHAR2',30);
378 fa_rx_util_pkg.assign_column('110',DECODE_REF_TYPE,'APPLICATION_REF_TYPE',
379 'arrx_oth.var.application_ref_type','VARCHAR2',80);
380 fa_rx_util_pkg.assign_column('120','APP.STATUS','APPLICATION_STATUS',
381 'arrx_oth.var.application_status','VARCHAR2',20);
382 fa_rx_util_pkg.assign_column('130','APP.APPLY_DATE','APPLY_DATE',
383 'arrx_oth.var.apply_date','DATE');
384 fa_rx_util_pkg.assign_column('140', 'RBFIRST.BATCH_ID','BATCH_ID',
385 'arrx_oth.var.batch_id','NUMBER');
386 fa_rx_util_pkg.assign_column('150','RBFIRST.NAME','BATCH_NAME',
387 'arrx_oth.var.batch_name','VARCHAR2',20);
388 fa_rx_util_pkg.assign_column('160','BSFIRST.NAME','BATCH_SOURCE',
389 'arrx_oth.var.batch_source','VARCHAR2',50);
390 fa_rx_util_pkg.assign_column('170','CR.CASH_RECEIPT_ID','CASH_RECEIPT_ID',
391 'arrx_oth.var.cash_receipt_id','NUMBER');
392 fa_rx_util_pkg.assign_column('180' ,'SUBSTRB(PARTY.PARTY_NAME,1,50)','CUSTOMER_NAME',
393 'arrx_oth.var.customer_name','VARCHAR2',50);
394 fa_rx_util_pkg.assign_column('190','CUST.ACCOUNT_NUMBER','CUSTOMER_NUMBER',
398 fa_rx_util_pkg.assign_column('210',DECODE_CURRENCY,'FORMAT_CURRENCY_CODE',
395 'arrx_oth.var.customer_number','VARCHAR2',30);
396 fa_rx_util_pkg.assign_column('200',null,'DEBIT_BALANCING',
397 'arrx_oth.var.debit_balancing','VARCHAR2',240);
399 'arrx_oth.var.format_currency_code','VARCHAR2',15);
400 fa_rx_util_pkg.assign_column('220','APP.GL_DATE','GL_DATE',
401 'arrx_oth.var.gl_date','DATE');
402 fa_rx_util_pkg.assign_column('230','CR.CURRENCY_CODE','RECEIPT_CURRENCY_CODE',
403 'arrx_oth.var.receipt_currency_code','VARCHAR2',15);
404 fa_rx_util_pkg.assign_column('240','CR.RECEIPT_DATE','RECEIPT_DATE',
405 'arrx_oth.var.receipt_date','DATE');
406 fa_rx_util_pkg.assign_column('250','CR.RECEIPT_NUMBER','RECEIPT_NUMBER',
407 'arrx_oth.var.receipt_number','VARCHAR2',30);
408 fa_rx_util_pkg.assign_column('260','CR.STATUS','RECEIPT_STATUS',
409 'arrx_oth.var.receipt_status','VARCHAR2',40);
410 fa_rx_util_pkg.assign_column('270','CR.TYPE','RECEIPT_TYPE',
411 'arrx_oth.var.receipt_type','VARCHAR2',30);
412 fa_rx_util_pkg.assign_column('275','CR.AMOUNT','RECEIPT_AMOUNT',
413 'arrx_oth.var.receipt_amount','NUMBER');
414 fa_rx_util_pkg.assign_column('280','RB.NAME','REMIT_BATCH_NAME',
415 'arrx_oth.var.remit_batch_name','VARCHAR2',20);
416
417 -- Assign FROM clause
418
419 fa_rx_util_pkg.debug('Assign FROM Clause using ALL tables');
420 fa_rx_util_pkg.from_clause := ' AR_RECEIVABLE_APPLICATIONS_ALL APP
421 , AR_CASH_RECEIPTS_ALL CR
422 , AR_CASH_RECEIPT_HISTORY_ALL CRH
423 , AR_PAYMENT_SCHEDULES_ALL PS
424 , HZ_CUST_ACCOUNTS_ALL CUST
425 , HZ_PARTIES PARTY
426 , AR_BATCH_SOURCES_ALL BS
427 , AR_BATCHES_ALL RB
428 , GL_SETS_OF_BOOKS GLS
429 , GL_CODE_COMBINATIONS GLC
430 -- , AP_BANK_ACCOUNTS_ALL AB
431 -- , AP_BANK_BRANCHES BB
432 , CE_BANK_ACCOUNTS AB
433 , CE_BANK_ACCT_USES CBA
434 , CE_BANK_BRANCHES_V BB
435 , AR_BATCHES_ALL RBFIRST
436 , AR_CASH_RECEIPT_HISTORY_ALL CRHFIRST
437 , AR_BATCH_SOURCES_ALL BSFIRST';
438
439 -- Assign WHERE clause
440 fa_rx_util_pkg.debug('AR_ASSIGN_WHERE_CLAUSE');
441 fa_rx_util_pkg.where_clause := '
442 APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
443 AND APP.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
444 AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
445 AND CRH.CURRENT_RECORD_FLAG = ''Y''
446 AND APP.STATUS IN (''ACTIVITY'',''OTHER ACC'')
447 AND APP.AMOUNT_APPLIED <> 0
448 AND APP.DISPLAY = ''Y''
449 AND BS.BATCH_SOURCE_ID (+) = RB.BATCH_SOURCE_ID
450 AND RB.BATCH_ID (+) = CR.SELECTED_REMITTANCE_BATCH_ID
451 -- AND AB.BANK_ACCOUNT_ID (+) = CR.REMITTANCE_BANK_ACCOUNT_ID
452 AND CBA.BANK_ACCT_USE_ID(+) = CR.REMIT_BANK_ACCT_USE_ID
453 AND CBA.BANK_ACCOUNT_ID = AB.BANK_ACCOUNT_ID
454 AND BB.BRANCH_PARTY_ID = AB.BANK_BRANCH_ID
455 AND CUST.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
456 AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
457 AND GLS.SET_OF_BOOKS_ID = CR.SET_OF_BOOKS_ID
458 AND GLC.CODE_COMBINATION_ID(+) = APP.CODE_COMBINATION_ID
459 AND CR.CASH_RECEIPT_ID = CRHFIRST.CASH_RECEIPT_ID
460 AND CRHFIRST.FIRST_POSTED_RECORD_FLAG = ''Y''
461 AND CRHFIRST.BATCH_ID = RBFIRST.BATCH_ID(+)
462 AND BSFIRST.BATCH_SOURCE_ID(+) = RBFIRST.BATCH_SOURCE_ID ' ||
463 CO_SEG_WHERE || ' ' ||
464 GL_DATE_WHERE || ' ' ||
465 CURRENCY_CODE_WHERE || ' ' ||
466 CUSTOMER_NAME_WHERE || ' ' ||
467 CUSTOMER_NUMBER_WHERE || ' ' ||
468 RECEIPT_DATE_WHERE || ' ' ||
469 APPLY_DATE_WHERE || ' ' ||
470 REMIT_BATCH_WHERE || ' ' ||
471 RECEIPT_BATCH_WHERE || ' ' ||
472 RECEIPT_NUMBER_WHERE || ' ' ||
473 APP_TYPE_WHERE || ' ' ||
474 L_APP_ORG_WHERE || ' ' ||
475 L_CR_ORG_WHERE || ' ' ||
476 L_CRH_ORG_WHERE || ' ' ||
477 -- L_PS_ORG_WHERE || ' ' ||
478 L_CUST_ORG_WHERE || ' ' ||
479 L_BS_ORG_WHERE || ' ' ||
480 L_RB_ORG_WHERE || ' ' ||
481 L_AB_ORG_WHERE || ' ' ||
482 L_BSFIRST_ORG_WHERE || ' ' ||
483 L_CRHFIRST_ORG_WHERE || ' ' ||
484 L_RBFIRST_ORG_WHERE;
485
486 fa_rx_util_pkg.debug('arrx_oth.adj_before_report()-');
487
488 end before_report;
489
490 --
491 -- Bind trigger for main Other Receipt Applications Report
492 --
493
494 procedure bind (c in integer)
495 is
496
497 begin
498 fa_rx_util_pkg.debug('AR_GET_BIND');
499 --
500 -- Binding vars that appear in SELECT statement depending on input params
501 --
502 IF var.p_reporting_level = 3000 THEN
503 IF var.p_reporting_entity_id IS NOT NULL THEN
504 dbms_sql.bind_variable(c, 'p_reporting_entity_id', var.p_reporting_entity_id);
505 END IF;
506 END IF;
507
508 IF var.p_currency_code IS NOT NULL THEN
509 dbms_sql.bind_variable(c, 'p_currency_code', var.p_currency_code);
510 END IF;
511
512 IF var.p_gl_date_low IS NOT NULL THEN
513 dbms_sql.bind_variable(c, 'p_gl_date_low', var.p_gl_date_low);
514 END IF;
515
516 IF var.p_gl_date_high IS NOT NULL THEN
517 dbms_sql.bind_variable(c, 'p_gl_date_high', var.p_gl_date_high);
518 END IF;
519
520 IF var.p_customer_name_low IS NOT NULL THEN
521 dbms_sql.bind_variable(c, 'p_customer_name_low', var.p_customer_name_low);
522 END IF;
523
524 IF var.p_customer_name_high IS NOT NULL THEN
525 dbms_sql.bind_variable(c, 'p_customer_name_high', var.p_customer_name_high);
526 END IF;
527
528 IF var.p_customer_number_low IS NOT NULL THEN
529 dbms_sql.bind_variable(c, 'p_customer_number_low', var.p_customer_number_low);
530 END IF;
531
532 IF var.p_customer_number_high IS NOT NULL THEN
533 dbms_sql.bind_variable(c, 'p_customer_number_high', var.p_customer_number_high);
534 END IF;
535
536 IF var.p_receipt_date_low IS NOT NULL THEN
537 dbms_sql.bind_variable(c, 'p_receipt_date_low', var.p_receipt_date_low);
538 END IF;
539
540 IF var.p_receipt_date_high IS NOT NULL THEN
541 dbms_sql.bind_variable(c, 'p_receipt_date_high', var.p_receipt_date_high);
542 END IF;
543
544 IF var.p_apply_date_low IS NOT NULL THEN
545 dbms_sql.bind_variable(c, 'p_apply_date_low', var.p_apply_date_low);
546 END IF;
547
548 IF var.p_apply_date_high IS NOT NULL THEN
549 dbms_sql.bind_variable(c, 'p_apply_date_high', var.p_apply_date_high);
550 END IF;
551
552 IF var.p_remit_batch_low IS NOT NULL THEN
553 dbms_sql.bind_variable(c, 'p_remit_batch_low', var.p_remit_batch_low);
554 END IF;
555
556 IF var.p_remit_batch_high IS NOT NULL THEN
557 dbms_sql.bind_variable(c, 'p_remit_batch_high', var.p_remit_batch_high);
558 END IF;
559
560 IF var.p_receipt_batch_low IS NOT NULL THEN
561 dbms_sql.bind_variable(c, 'p_receipt_batch_low', var.p_receipt_batch_low);
562 END IF;
563
564 IF var.p_receipt_batch_high IS NOT NULL THEN
565 dbms_sql.bind_variable(c, 'p_receipt_batch_high', var.p_receipt_batch_high);
566 END IF;
567
568 IF var.p_receipt_number_low IS NOT NULL THEN
569 dbms_sql.bind_variable(c, 'p_receipt_number_low', var.p_receipt_number_low);
570 END IF;
571
572 IF var.p_receipt_number_high IS NOT NULL THEN
573 dbms_sql.bind_variable(c, 'p_receipt_number_high', var.p_receipt_number_high);
574 END IF;
575
576 IF var.p_app_type IS NOT NULL THEN
577 dbms_sql.bind_variable(c, 'p_app_type', var.p_app_type);
578 END IF;
579
580 dbms_sql.bind_variable(c, 'P_ENTERED_CURRENCY',var.p_currency_code);
581 dbms_sql.bind_variable(c, 'P_FUNCTIONAL_CURRENCY',var.functional_currency_code);
582
583 end bind;
584
585 --
586 -- After Fetch trigger
587 --
588
589 procedure after_fetch
590 is
591 begin
592
593 --
594 -- Assign acount data
595 --
596
597 var.debit_balancing := fa_rx_flex_pkg.get_value(
598 p_application_id => 101,
599 p_id_flex_code => 'GL#',
600 p_id_flex_num => var.chart_of_accounts_id,
601 p_qualifier => 'GL_BALANCING',
602 p_ccid => var.code_combination_id);
603
604 --
605
606 end after_fetch;
607
608 end ARRX_OTH;