[Home] [Help]
PACKAGE BODY: APPS.ARRX_RC
Source
1 PACKAGE BODY ARRX_RC as
2 /* $Header: ARRXRCB.pls 120.44.12020000.2 2012/09/05 06:13:30 riqi ship $ */
3
4 -- create generic procedure to reset to NULL all var type variables
5 procedure init_var is
6
7 begin
8 /* Bug 5724171*/
9 var.len1 := NULL;
10 var.len2 := NULL;
11 var.p_batch_name_low := NULL;
12 var.p_batch_name_high := NULL;
13
14 var.p_customer_name_low := NULL;
15 var.p_customer_name_high := NULL;
16 var.p_customer_number_low := NULL;
17 var.p_customer_number_high := NULL;
18
19 var.p_apply_date_low := NULL;
20 var.p_apply_date_high := NULL;
21 var.p_deposit_date_low := NULL;
22 var.p_deposit_date_high := NULL;
23 var.p_receipt_date_low := NULL;
24 var.p_receipt_date_high := NULL;
25 var.p_gl_date_low := NULL;
26 var.p_gl_date_high := NULL;
27
28 var.p_receipt_status_low := NULL;
29 var.p_receipt_status_high := NULL;
30 var.p_receipt_number_low := NULL;
31 var.p_receipt_number_high := NULL;
32
33 var.p_invoice_type_low := NULL;
34 var.p_invoice_type_high := NULL;
35 var.p_invoice_number_low := NULL;
36 var.p_invoice_number_high := NULL;
37
38 var.p_currency_code := NULL;
39 var.p_bank_account_name := NULL;
40 var.p_payment_method := NULL;
41 var.p_confirmed_flag := NULL;
42
43 var.p_doc_sequence_name := NULL;
44 var.p_doc_sequence_number_from := NULL;
45 var.p_doc_sequence_number_to := NULL;
46
47 var.request_id := NULL;
48 var.p_reporting_level := NULL;
49 var.p_reporting_entity_id := NULL;
50 var.p_sob_id := NULL;
51 var.p_coa_id := NULL;
52 var.p_co_seg_low := NULL;
53 var.p_co_seg_high := NULL;
54 var.ca_sob_type := NULL;
55 var.ca_sob_id := NULL;
56
57 var.calling_program := NULL;
58 end;
59
60 ----------------------------
61 -- Consolidated Before Report
62 -----------------------------
63 procedure before_report
64 is
65
66 -- make the following variable global, so they can be passed around procedures
67 -- parameter based where clauses
68 BATCH_NAME_WHERE varchar2(500);
69 CUSTOMER_NAME_WHERE varchar2(500);
70 DEPOSIT_DATE_WHERE varchar2(500);
71 RECEIPT_STATUS_WHERE varchar2(500);
72 RECEIPT_NUMBER_WHERE varchar2(500);
73 INVOICE_NUMBER_WHERE varchar2(500); --Bug 1579930
74 RECEIPT_DATE_WHERE varchar2(500);
75 CURRENCY_CODE_WHERE varchar2(500);
76 BANK_NAME_WHERE varchar2(500);
77 PAYMENT_METHOD_WHERE varchar2(500);
78 CONFIRMED_FLAG_WHERE varchar2(500);
79 CRH_GL_DATE_WHERE varchar2(500);
80 INCRH_GL_DATE_WHERE varchar2(500);
81 CRHNEW_GL_DATE_WHERE varchar2(500);
82 RA_GL_DATE_WHERE varchar2(500);
83 DOC_SEQUENCE_NAME_WHERE varchar2(500);
84 DOC_SEQUENCE_NUMBER_WHERE varchar2(500);
85 CO_SEG_WHERE VARCHAR2(500);
86 APPLY_DATE_WHERE varchar2(500);
87 GL_DATE_WHERE varchar2(500);
88 CUST_NUM_WHERE varchar2(500);
89 INV_TYPE_WHERE varchar2(500);
90 SHOW_BILL_WHERE varchar2(100);
91 SHOW_BILL_FROM varchar2(100);
92 BILL_FLAG varchar2(1);
93 MCD_GL_DATE_WHERE varchar2(500); -- bug 2328165
94
95 -- decode strings used in assign_column
96 DECODE_CR_TYPE varchar2(500);
97 DECODE_CR_STATUS varchar2(500);
98 DECODE_CR_REFERENCE_TYPE varchar2(500);
99 DECODE_CRH_STATUS varchar2(500);
100 DECODE_CRH_AMOUNT varchar2(500);
101 DECODE_CRH_ACCTD_AMOUNT varchar2(500);
102 DECODE_CRH_FD_AMOUNT varchar2(500);
103 DECODE_CRH_AFD_AMOUNT varchar2(500);
104 DECODE_RA_STATUS varchar2(200);
105
106
107 -- multi-org where clauses
108 L_CR_ORG_WHERE VARCHAR2(500);
109 L_TAX_ORG_WHERE VARCHAR2(500);
110 L_ABA_ORG_WHERE VARCHAR2(500);
111 L_CRH_ORG_WHERE VARCHAR2(500);
112 L_CUST_ORG_WHERE VARCHAR2(500);
113 L_CRHFIRST_ORG_WHERE VARCHAR2(500);
114 L_BATCHFIRST_ORG_WHERE VARCHAR2(500);
115 L_RA_ORG_WHERE VARCHAR2(500);
116 L_MISC_ORG_WHERE VARCHAR2(500);
117 L_INCRH_ORG_WHERE VARCHAR2(500);
118 L_CRHNEW_ORG_WHERE VARCHAR2(500);
119 L_BATCH_ORG_WHERE VARCHAR2(500);
120 L_CI_ORG_WHERE VARCHAR2(500);
121 L_TRX_ORG_WHERE VARCHAR2(500);
122 L_REC_ORG_WHERE VARCHAR2(500);
123 L_RAC_ORG_WHERE VARCHAR2(500);
124 L_PS_ORG_WHERE VARCHAR2(500);
125 L_MCD_ORG_WHERE VARCHAR2(500);
126 L_ARD_ORG_WHERE VARCHAR2(500);
127 L_BS_ORG_WHERE VARCHAR2(500);
128
129 rec_gain_loss varchar2(200);
130 applied_total varchar2(200);
131 tag number; /* bug 5724171*/
132
133
134 begin
135 fa_rx_util_pkg.debug('arrx_rc.before_report()+');
136
137 -- define common lexical params and assign_column
138
139 fa_rx_util_pkg.debug('Get SOB ID'||var.books_id);
140
141 /*Bug 5244313
142 IF var.ca_sob_type = 'P'
143 THEN
144 var.books_id := arp_global.sysparam.set_of_books_id;
145 ELSE
146 var.books_id := var.ca_sob_id;
147 END IF;
148
149 */
150 --
151 -- Get CHART_OF_ACCOUNTS_ID
152 --
153 fa_rx_util_pkg.debug('Get COA ID');
154
155 select CHART_OF_ACCOUNTS_ID,CURRENCY_CODE,NAME
156 into var.chart_of_accounts_id,var.currency_code,var.org_name
157 from GL_SETS_OF_BOOKS
158 where SET_OF_BOOKS_ID = var.books_id;
159
160 --
161 -- Figure out NOCOPY the where clause for the parameters
162 --
163 fa_rx_util_pkg.debug('Build Where clauses based on parameters');
164
165 IF var.p_batch_name_low IS NULL AND var.p_batch_name_high IS NULL THEN
166 BATCH_NAME_WHERE := NULL;
167 ELSIF var.p_batch_name_low IS NULL THEN
168 BATCH_NAME_WHERE := ' AND BATCHFIRST.NAME <= :p_batch_name_high';
169 ELSIF var.p_batch_name_high IS NULL THEN
170 BATCH_NAME_WHERE := ' AND BATCHFIRST.NAME >= :p_batch_name_low';
171 ELSE
172 BATCH_NAME_WHERE := ' AND BATCHFIRST.NAME BETWEEN :p_batch_name_low AND :p_batch_name_high';
173 END IF;
174
175 /* bug 5724171*/
176 -- strip customer number
177 /* Bug 7165910 */
178 IF var.p_customer_name_low IS NOT NULL then
179 tag := instrb(var.p_customer_name_low,'(',-1,1) - 1;
180 If tag >= 0 then
181 var.p_customer_name_low := substrb(var.p_customer_name_low,1, tag);
182 End If;
183 var.len1 := lengthb(var.p_customer_name_low);
184 END IF;
185 IF var.p_customer_name_high IS NOT NULL THEN
186 tag := instrb(var.p_customer_name_high,'(',-1,1) - 1;
187 If tag >= 0 then
188 var.p_customer_name_high := substrb(var.p_customer_name_high,1,tag);
189 End If;
190 var.len2 := lengthb(var.p_customer_name_high);
191 END IF;
192
193 IF var.p_customer_name_low IS NULL AND var.p_customer_name_high IS NULL THEN
194 CUSTOMER_NAME_WHERE := NULL;
195 ELSIF var.p_customer_name_low IS NULL THEN
196 CUSTOMER_NAME_WHERE := ' AND substrb(PARTY.PARTY_NAME,1,:len2) <= :p_customer_name_high';
197 ELSIF var.p_customer_name_high IS NULL THEN
198 CUSTOMER_NAME_WHERE := ' AND substrb(PARTY.PARTY_NAME,1,:len1) >= :p_customer_name_low';
199 ELSE
200 CUSTOMER_NAME_WHERE := ' AND substrb(PARTY.PARTY_NAME,1,:len1) >= :p_customer_name_low ' ||
201 ' AND substrb(PARTY.PARTY_NAME,1,:len2) <= :p_customer_name_high';
202 END IF;
203
204 fa_rx_util_pkg.debug('CUSTOMER_NAME_WHERE = ' || CUSTOMER_NAME_WHERE);
205
206 IF var.p_deposit_date_low IS NULL AND var.p_deposit_date_high IS NULL THEN
207 DEPOSIT_DATE_WHERE := NULL;
208 ELSIF var.p_deposit_date_low IS NULL THEN
209 DEPOSIT_DATE_WHERE := ' AND CR.DEPOSIT_DATE <= :p_deposit_date_high';
210 ELSIF var.p_deposit_date_high IS NULL THEN
211 DEPOSIT_DATE_WHERE := ' AND CR.DEPOSIT_DATE >= :p_deposit_date_low';
212 ELSE
213 DEPOSIT_DATE_WHERE := ' AND CR.DEPOSIT_DATE BETWEEN :p_deposit_date_low AND :p_deposit_date_high';
214 END IF;
215
216 IF var.p_receipt_status_low IS NULL AND var.p_receipt_status_high IS NULL THEN
217 RECEIPT_STATUS_WHERE := NULL;
218 ELSIF var.p_receipt_status_low IS NULL THEN
219 RECEIPT_STATUS_WHERE := ' AND CR.STATUS <= :p_receipt_status_high';
220 ELSIF var.p_receipt_status_high IS NULL THEN
221 RECEIPT_STATUS_WHERE := ' AND CR.STATUS >= :p_receipt_status_low';
222 ELSE
223 RECEIPT_STATUS_WHERE := ' AND CR.STATUS BETWEEN :p_receipt_status_low AND :p_receipt_status_high';
224 END IF;
225
226 IF var.p_receipt_number_low IS NULL AND var.p_receipt_number_high IS NULL THEN
227 RECEIPT_NUMBER_WHERE := NULL;
228 ELSIF var.p_receipt_number_low IS NULL THEN
229 RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER <= :p_receipt_number_high';
230 ELSIF var.p_receipt_number_high IS NULL THEN
231 RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER >= :p_receipt_number_low';
232 ELSE
233 RECEIPT_NUMBER_WHERE := ' AND CR.RECEIPT_NUMBER BETWEEN :p_receipt_number_low AND :p_receipt_number_high';
234 END IF;
235
236 IF var.p_invoice_number_low IS NULL AND var.p_invoice_number_high IS NULL THEN
237 INVOICE_NUMBER_WHERE := NULL;
238 ELSIF var.p_invoice_number_low IS NULL THEN
239 INVOICE_NUMBER_WHERE := ' AND TRX.TRX_NUMBER <= :p_invoice_number_high';
240 ELSIF var.p_invoice_number_high IS NULL THEN
241 INVOICE_NUMBER_WHERE := ' AND TRX.TRX_NUMBER >= :p_invoice_number_low';
242 ELSE
243 INVOICE_NUMBER_WHERE := ' AND TRX.TRX_NUMBER BETWEEN :p_invoice_number_low AND :p_invoice_number_high';
244 END IF;
245
246 IF var.p_receipt_date_low IS NULL AND var.p_receipt_date_high IS NULL THEN
247 RECEIPT_DATE_WHERE := NULL;
248 ELSIF var.p_receipt_date_low IS NULL THEN
249 RECEIPT_DATE_WHERE := ' AND CR.RECEIPT_DATE <= :p_receipt_date_high';
250 ELSIF var.p_receipt_date_high IS NULL THEN
251 RECEIPT_DATE_WHERE := ' AND CR.RECEIPT_DATE >= :p_receipt_date_low';
252 ELSE
253 RECEIPT_DATE_WHERE := ' AND CR.RECEIPT_DATE BETWEEN :p_receipt_date_low AND :p_receipt_date_high';
254 END IF;
255
256 IF var.p_currency_code IS NULL THEN
257 CURRENCY_CODE_WHERE := NULL;
258 ELSE
259 CURRENCY_CODE_WHERE := ' AND CR.CURRENCY_CODE = :p_currency_code';
260 END IF;
261
262 IF var.p_bank_account_name IS NULL THEN
263 BANK_NAME_WHERE := NULL;
264 ELSE
265 BANK_NAME_WHERE := ' AND CBA.BANK_ACCOUNT_NAME = :p_bank_account_name';
266 END IF;
267
268 IF var.p_payment_method IS NULL THEN
269 PAYMENT_METHOD_WHERE := NULL;
270 ELSE
271 PAYMENT_METHOD_WHERE := ' AND ARM.NAME = :p_payment_method';
272 END IF;
273
274 IF var.p_confirmed_flag IS NULL THEN
275 CONFIRMED_FLAG_WHERE := NULL;
276 ELSE
277 CONFIRMED_FLAG_WHERE := ' AND nvl(CR.CONFIRMED_FLAG,''Y'') = :p_confirmed_flag';
278 END IF;
279
280
281 -- reset next variables to NULL, for APPLIED receipts, we want to use gl_date range against RA and not CRH
282 CRH_GL_DATE_WHERE := NULL;
283 INCRH_GL_DATE_WHERE := NULL;
284 RA_GL_DATE_WHERE := NULL;
285 CRHNEW_GL_DATE_WHERE := NULL;
286
287 IF var.p_gl_date_low IS NULL AND var.p_gl_date_high IS NULL THEN
288 CRH_GL_DATE_WHERE := NULL;
289 INCRH_GL_DATE_WHERE := NULL;
290 CRHNEW_GL_DATE_WHERE := NULL;
291 ELSIF var.p_gl_date_low IS NULL THEN
292 if var.calling_program <> 'APPLIED' THEN
293 CRH_GL_DATE_WHERE := ' AND CRH.GL_DATE <= :p_gl_date_high';
294 INCRH_GL_DATE_WHERE := ' AND INCRH.GL_DATE <= :p_gl_date_high';
295 CRHNEW_GL_DATE_WHERE := ' AND CRHNEW.GL_DATE(+) <= :p_gl_date_high';
296
297 else
298 RA_GL_DATE_WHERE := ' AND RA.GL_DATE <= :p_gl_date_high';
299 end if;
300 ELSIF var.p_gl_date_high IS NULL THEN
301 if var.calling_program <> 'APPLIED' THEN
302 CRH_GL_DATE_WHERE := ' AND CRH.GL_DATE >= :p_gl_date_low';
303 INCRH_GL_DATE_WHERE := ' AND INCRH.GL_DATE >= :p_gl_date_low';
304 CRHNEW_GL_DATE_WHERE := ' AND CRHNEW.GL_DATE(+) >= :p_gl_date_low';
305
306 else
307 RA_GL_DATE_WHERE := ' AND RA.GL_DATE >= :p_gl_date_low';
308 end if;
309 ELSE
310 if var.calling_program <> 'APPLIED' THEN
311 CRH_GL_DATE_WHERE := ' AND CRH.GL_DATE BETWEEN :p_gl_date_low AND :p_gl_date_high';
312 INCRH_GL_DATE_WHERE := ' AND INCRH.GL_DATE BETWEEN :p_gl_date_low AND :p_gl_date_high';
313 CRHNEW_GL_DATE_WHERE := ' AND CRHNEW.GL_DATE(+) BETWEEN :p_gl_date_low AND :p_gl_date_high';
314
315 else
316 RA_GL_DATE_WHERE := ' AND RA.GL_DATE BETWEEN :p_gl_date_low AND :p_gl_date_high';
317 end if;
318 END IF;
319
320 IF nvl(var.calling_program ,'not receipt') = 'RECEIPT' THEN
321 IF var.p_gl_date_low IS NULL THEN
322 var.c_gl_date_low := TO_DATE('1800-01-01','YYYY-MM-DD');
323 ELSE
324 var.c_gl_date_low := var.p_gl_date_low;
325 END IF;
326 END IF;
327
328
329 IF var.p_doc_sequence_name is not null THEN
330 DOC_SEQUENCE_NAME_WHERE := ' AND DOCSEQ.DOC_SEQUENCE_ID = '''|| var.p_doc_sequence_name ||'''';
331 ELSE
332 DOC_SEQUENCE_NAME_WHERE := null;
333 END IF;
334
335 IF var.p_doc_sequence_number_from IS NULL and var.p_doc_sequence_number_from is NULL THEN
336 DOC_SEQUENCE_NUMBER_WHERE := NULL;
337 ELSIF var.p_doc_sequence_number_from IS NULL THEN
338 DOC_SEQUENCE_NUMBER_WHERE := ' AND CR.DOC_SEQUENCE_VALUE <= '''|| var.p_doc_sequence_number_to ||'''';
339 ELSIF var.p_doc_sequence_number_to IS NULL THEN
340 DOC_SEQUENCE_NUMBER_WHERE := ' AND CR.DOC_SEQUENCE_VALUE >= '''|| var.p_doc_sequence_number_from ||'''';
341 ELSE
342 DOC_SEQUENCE_NUMBER_WHERE := ' AND CR.DOC_SEQUENCE_VALUE between '''|| var.p_doc_sequence_number_from ||
343 ''' AND '''|| var.p_doc_sequence_number_to ||'''';
344 END IF;
345
346 fa_rx_util_pkg.debug('Define DECODE strings');
347
348 DECODE_CR_TYPE := 'DECODE(CR.TYPE,''CASH'',:L_CASH,''MISC'',:L_MISC)';
349 DECODE_CR_STATUS := 'DECODE(CR.STATUS,''APP'',:L_APP,''NSF'',:L_NSF,''REV'',:L_REV,''STOP'',:L_STOP, ' ||
350 ' ''UNAPP'',:L_UNAPP,''UNID'',:L_UNID)';
351 DECODE_CR_REFERENCE_TYPE := 'DECODE(CR.REFERENCE_TYPE,''PAYMENT'',:L_PAYMENT,''RECEIPT'',:L_RECEIPT,''REMITTANCE'',:L_REMITTANCE)';
352 DECODE_CRH_STATUS := 'DECODE(CRH.STATUS,''APPROVED'',:L_APPROVED,''CLEARED'',:L_CLEARED,''CONFIRMED'',:L_CONFIRMED, ' ||
353 ' ''REMITTED'',:L_REMITTED,''REVERSED'',:L_REVERSED)';
354 IF nvl(var.calling_program,'not receipt') = 'RECEIPT' THEN
355 DECODE_CRH_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'', decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID,'||
356 ' 0 , (NVL(CRHLAST.AMOUNT,0))* -1)'||
357 ',decode(CRHLAST.CASH_RECEIPT_HISTORY_ID, CRH.CASH_RECEIPT_HISTORY_ID, (CRH.AMOUNT),'||
358 ' (CRH.AMOUNT -(NVL(CRHLAST.AMOUNT,0)))))';
359 DECODE_CRH_ACCTD_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'', decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID,'||
360 ' 0, (NVL(CRHLAST.ACCTD_AMOUNT,0))* -1)'||
361 ' ,decode(CRHLAST.CASH_RECEIPT_HISTORY_ID, CRH.CASH_RECEIPT_HISTORY_ID, (CRH.ACCTD_AMOUNT)'||
362 ' , (CRH.ACCTD_AMOUNT -(NVL(CRHLAST.ACCTD_AMOUNT,0)))))';
363 DECODE_CRH_FD_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'', decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID,'||
364 ' 0, (NVL(CRHLAST.FACTOR_DISCOUNT_AMOUNT,0))* -1)'||
365 ',decode(CRHLAST.CASH_RECEIPT_HISTORY_ID ,CRH.CASH_RECEIPT_HISTORY_ID, NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0)'||
366 ', (NVL(CRH.FACTOR_DISCOUNT_AMOUNT,0) -(NVL(CRHLAST.FACTOR_DISCOUNT_AMOUNT,0)))))';
367 DECODE_CRH_AFD_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'', decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID,'||
368 '0, (NVL(CRHLAST.ACCTD_FACTOR_DISCOUNT_AMOUNT,0))* -1)'||
369 ',decode(CRHLAST.CASH_RECEIPT_HISTORY_ID ,CRH.CASH_RECEIPT_HISTORY_ID, NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0)'||
370 ', (NVL(CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0) -(NVL(CRHLAST.ACCTD_FACTOR_DISCOUNT_AMOUNT,0))))) ';
371 ELSE
372 DECODE_CRH_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'',CRH.AMOUNT*-1,CRH.AMOUNT)';
373 DECODE_CRH_ACCTD_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'',CRH.ACCTD_AMOUNT*-1,CRH.ACCTD_AMOUNT)';
374 DECODE_CRH_FD_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'',CRH.FACTOR_DISCOUNT_AMOUNT*-1,CRH.FACTOR_DISCOUNT_AMOUNT)';
375 DECODE_CRH_AFD_AMOUNT := 'DECODE(CRH.STATUS,''REVERSED'',CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT*-1,CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT)';
376 END IF;
377
378 fa_rx_util_pkg.debug('Start Assign_columns');
379 -- fa_rx_util_pkg.assign_column(unique seq#, select field, field in itf, into variable, type, len);
380
381 fa_rx_util_pkg.assign_column('10 ',null ,'ORGANIZATION_NAME' ,
382 'arrx_rc.var.organization_name' ,'VARCHAR2', 50);
383 fa_rx_util_pkg.assign_column('20 ',null ,'FUNCTIONAL_CURRENCY_CODE' ,
384 'arrx_rc.var.functional_currency_code' ,'VARCHAR2', 15);
385 fa_rx_util_pkg.assign_column('30 ','CR.STATUS' ,null ,
386 'arrx_rc.var.cr_status' ,'VARCHAR2', 40);
387 fa_rx_util_pkg.assign_column('40 ','CRH.STATUS' ,null ,
388 'arrx_rc.var.crh_status' ,'VARCHAR2', 40);
389 fa_rx_util_pkg.assign_column('50 ','DECODE(CRH.STATUS, ''REVERSED'', BATCHFIRST.BATCH_ID, BATCH.BATCH_ID)','BATCH_ID' ,
390 'arrx_rc.var.batch_id' ,'NUMBER');
391 fa_rx_util_pkg.assign_column('60 ','DECODE(CRH.STATUS, ''REVERSED'', BATCHFIRST.NAME, BATCH.NAME)','BATCH_NAME' ,
392 'arrx_rc.var.batch_name' ,'VARCHAR2', 20);
393 fa_rx_util_pkg.assign_column('70 ','CR.CASH_RECEIPT_ID' ,'CASH_RECEIPT_ID' ,
394 'arrx_rc.var.cash_receipt_id' ,'NUMBER');
395 fa_rx_util_pkg.assign_column('80 ','CR.RECEIPT_NUMBER' ,'RECEIPT_NUMBER' ,
396 'arrx_rc.var.receipt_number' ,'VARCHAR2', 30);
397 fa_rx_util_pkg.assign_column('90 ','CR.CURRENCY_CODE' ,'RECEIPT_CURRENCY_CODE' ,
398 'arrx_rc.var.receipt_currency_code' ,'VARCHAR2', 15);
399 fa_rx_util_pkg.assign_column('100','CR.EXCHANGE_RATE' ,'EXCHANGE_RATE' ,
400 'arrx_rc.var.exchange_rate' ,'NUMBER');
401 fa_rx_util_pkg.assign_column('110','CR.EXCHANGE_DATE' ,'EXCHANGE_DATE' ,
402 'arrx_rc.var.exchange_date' ,'DATE');
403 fa_rx_util_pkg.assign_column('120','CR.EXCHANGE_RATE_TYPE' ,'EXCHANGE_TYPE' ,
404 'arrx_rc.var.exchange_type' ,'VARCHAR2', 30);
405 fa_rx_util_pkg.assign_column('130','DOCSEQ.NAME' ,'DOC_SEQUENCE_NAME' ,
406 'arrx_rc.var.doc_sequence_name' ,'VARCHAR2', 30);
407 fa_rx_util_pkg.assign_column('140','CR.DOC_SEQUENCE_VALUE' ,'DOC_SEQUENCE_VALUE' ,
408 'arrx_rc.var.doc_sequence_value' ,'NUMBER');
409 fa_rx_util_pkg.assign_column('150','CR.DEPOSIT_DATE' ,'DEPOSIT_DATE' ,
410 'arrx_rc.var.deposit_date' ,'DATE');
411 fa_rx_util_pkg.assign_column('160','CR.RECEIPT_DATE' ,'RECEIPT_DATE' ,
412 'arrx_rc.var.receipt_date' ,'DATE');
413 fa_rx_util_pkg.assign_column('170',DECODE_CR_TYPE ,'RECEIPT_TYPE' ,
414 'arrx_rc.var.receipt_type' ,'VARCHAR2', 30);
415 fa_rx_util_pkg.assign_column('180',DECODE_CR_STATUS ,'RECEIPT_STATUS' ,
416 'arrx_rc.var.receipt_status' ,'VARCHAR2', 40);
417 fa_rx_util_pkg.assign_column('190','CR.MISC_PAYMENT_SOURCE' ,'MISC_PAYMENT_SOURCE' ,
418 'arrx_rc.var.misc_payment_source' ,'VARCHAR2', 30);
419 fa_rx_util_pkg.assign_column('200','TAX.TAX_CODE' ,'TAX_CODE' ,
420 'arrx_rc.var.tax_code' ,'VARCHAR2', 50);
421 fa_rx_util_pkg.assign_column('210',DECODE_CR_REFERENCE_TYPE ,'REFERENCE_TYPE' ,
422 'arrx_rc.var.reference_type' ,'VARCHAR2', 30);
423 fa_rx_util_pkg.assign_column('220','CR.ANTICIPATED_CLEARING_DATE' ,'ANTICIPATED_CLEARING_DATE' ,
424 'arrx_rc.var.anticipated_clearing_date' ,'DATE');
425 fa_rx_util_pkg.assign_column('230','ABB.BANK_NAME' ,'BANK_NAME' ,
426 'arrx_rc.var.bank_name' ,'VARCHAR2', 60);
427 fa_rx_util_pkg.assign_column('240','ABB.BANK_NAME_ALT' ,'BANK_NAME_ALT' ,
428 'arrx_rc.var.bank_name_alt' ,'VARCHAR2',320);
429 fa_rx_util_pkg.assign_column('250','ABB.BANK_BRANCH_NAME' ,'BANK_BRANCH_NAME' ,
430 'arrx_rc.var.bank_branch_name' ,'VARCHAR2', 60);
431 fa_rx_util_pkg.assign_column('260','ABB.BANK_BRANCH_NAME_ALT' ,'BANK_BRANCH_NAME_ALT' ,
432 'arrx_rc.var.bank_branch_name_alt' ,'VARCHAR2',320);
433 fa_rx_util_pkg.assign_column('270','ABB.BANK_NUMBER' ,'BANK_NUMBER' ,
434 'arrx_rc.var.bank_number' ,'VARCHAR2', 30);
435 fa_rx_util_pkg.assign_column('280','ABB.BRANCH_NUMBER' ,'BANK_BRANCH_NUMBER' ,
436 'arrx_rc.var.bank_branch_number' ,'VARCHAR2', 25);
437 fa_rx_util_pkg.assign_column('290','CBA.BANK_ACCOUNT_NAME' ,'BANK_ACCOUNT_NAME' ,
438 'arrx_rc.var.bank_account_name' ,'VARCHAR2', 80);
439 fa_rx_util_pkg.assign_column('300','CBA.BANK_ACCOUNT_NAME_ALT' ,'BANK_ACCOUNT_NAME_ALT' ,
440 'arrx_rc.var.bank_account_name_alt' ,'VARCHAR2',320);
441 fa_rx_util_pkg.assign_column('310','CBA.CURRENCY_CODE' ,'BANK_ACCOUNT_CURRENCY' ,
442 'arrx_rc.var.bank_account_currency' ,'VARCHAR2', 15);
443 fa_rx_util_pkg.assign_column('320','ARM.NAME' ,'RECEIPT_METHOD' ,
444 'arrx_rc.var.receipt_method' ,'VARCHAR2', 30);
445 fa_rx_util_pkg.assign_column('330','CRH.CASH_RECEIPT_HISTORY_ID' ,'CASH_RECEIPT_HISTORY_ID' ,
446 'arrx_rc.var.cash_receipt_history_id' ,'NUMBER');
447 fa_rx_util_pkg.assign_column('340','CRH.GL_DATE' ,'GL_DATE' ,
448 'arrx_rc.var.gl_date' ,'DATE');
449 fa_rx_util_pkg.assign_column('350',DECODE_CRH_AMOUNT ,'RECEIPT_AMOUNT' ,
450 'arrx_rc.var.receipt_amount' ,'NUMBER');
451 fa_rx_util_pkg.assign_column('360',DECODE_CRH_STATUS ,'RECEIPT_HISTORY_STATUS' ,
452 'arrx_rc.var.receipt_history_status' ,'VARCHAR2', 40);
453 fa_rx_util_pkg.assign_column('370',DECODE_CRH_ACCTD_AMOUNT ,'ACCTD_RECEIPT_AMOUNT' ,
454 'arrx_rc.var.acctd_receipt_amount' ,'NUMBER');
455 fa_rx_util_pkg.assign_column('380',DECODE_CRH_FD_AMOUNT ,'FACTOR_DISCOUNT_AMOUNT' ,
456 'arrx_rc.var.factor_discount_amount' ,'NUMBER');
457 fa_rx_util_pkg.assign_column('390',DECODE_CRH_AFD_AMOUNT ,'ACCTD_FACTOR_DISCOUNT_AMOUNT' ,
458 'arrx_rc.var.acctd_factor_discount_amount' ,'NUMBER');
459 fa_rx_util_pkg.assign_column('400','CC.CODE_COMBINATION_ID' ,'ACCOUNT_CODE_COMBINATION_ID' ,
460 'arrx_rc.var.account_code_combination_id' ,'NUMBER');
461 fa_rx_util_pkg.assign_column('410',null ,'DEBIT_ACCOUNT' ,
462 'arrx_rc.var.debit_account' ,'VARCHAR2',240);
463 fa_rx_util_pkg.assign_column('420',null ,'DEBIT_ACCOUNT_DESC' ,
464 'arrx_rc.var.debit_account_desc' ,'VARCHAR2',240);
465 fa_rx_util_pkg.assign_column('430',null ,'DEBIT_BALANCING' ,
466 'arrx_rc.var.debit_balancing' ,'VARCHAR2',240);
467 fa_rx_util_pkg.assign_column('440',null ,'DEBIT_BALANCING_DESC' ,
468 'arrx_rc.var.debit_balancing_desc' ,'VARCHAR2',240);
469 fa_rx_util_pkg.assign_column('450',null ,'DEBIT_NATACCT' ,
470 'arrx_rc.var.debit_natacct' ,'VARCHAR2',240);
471 fa_rx_util_pkg.assign_column('460',null ,'DEBIT_NATACCT_DESC' ,
472 'arrx_rc.var.debit_natacct_desc' ,'VARCHAR2',240);
473 fa_rx_util_pkg.assign_column('470','CUST.CUST_ACCOUNT_ID' ,'CUSTOMER_ID' ,
474 'arrx_rc.var.customer_id' ,'NUMBER');
475 /* bug 5724171*/
476 fa_rx_util_pkg.assign_column('480','SUBSTRB(PARTY.PARTY_NAME,1,240)' ,'CUSTOMER_NAME' ,
477 'arrx_rc.var.customer_name' ,'VARCHAR2', 240);
478 fa_rx_util_pkg.assign_column('490','DECODE(PARTY.PARTY_TYPE, ''ORGANIZATION'',PARTY.ORGANIZATION_NAME_PHONETIC, NULL)','CUSTOMER_NAME_ALT',
479 'arrx_rc.var.customer_name_alt' ,'VARCHAR2',320);
480 fa_rx_util_pkg.assign_column('500','CUST.ACCOUNT_NUMBER' ,'CUSTOMER_NUMBER' ,
481 'arrx_rc.var.customer_number' ,'VARCHAR2', 30);
482
483
484
485 -- define FROM_CLAUSE
486 IF var.calling_program in ('RECEIPT','APPLIED','MISC','ACTUAL') then
487
488 fa_rx_util_pkg.debug('Define FROM_CLAUSE using _ALL tables');
489
490 IF NVL(var.ca_sob_type,'P') = 'P' THEN
491 fa_rx_util_pkg.From_Clause :=
492 'AR_CASH_RECEIPTS CR,
493 FND_DOCUMENT_SEQUENCES DOCSEQ,
494 AR_VAT_TAX TAX,
495 CE_BANK_ACCOUNTS CBA,
496 CE_BANK_ACCT_USES_ALL ABA,
497 CE_BANK_BRANCHES_V ABB,
498 AR_RECEIPT_METHODS ARM,
499 AR_CASH_RECEIPT_HISTORY CRH,
500 GL_CODE_COMBINATIONS CC,
501 HZ_CUST_ACCOUNTS CUST,
502 HZ_PARTIES PARTY,
503 AR_BATCHES BATCH,
504 AR_CASH_RECEIPT_HISTORY CRHFIRST,
505 AR_BATCHES BATCHFIRST';
506 ELSE
507 fa_rx_util_pkg.From_Clause :=
508 'AR_CASH_RECEIPTS_ALL_MRC_V CR,
509 FND_DOCUMENT_SEQUENCES DOCSEQ,
510 AR_VAT_TAX_ALL TAX,
511 CE_BANK_ACCOUNTS CBA,
512 CE_BANK_ACCT_USES_ALL ABA,
513 CE_BANK_BRANCHES_V ABB,
514 AR_RECEIPT_METHODS ARM,
515 AR_CASH_RECEIPT_HIST_ALL_MRC_V CRH,
516 GL_CODE_COMBINATIONS CC,
517 HZ_CUST_ACCOUNTS_ALL CUST,
518 HZ_PARTIES PARTY,
519 AR_BATCHES_ALL_MRC_V BATCH,
520 AR_CASH_RECEIPT_HIST_ALL_MRC_V CRHFIRST,
521 AR_BATCHES_ALL_MRC_V BATCHFIRST';
522 END IF;
523
524 ELSE
525
526 fa_rx_util_pkg.debug('Define FROM_CLAUSE using org-striped views');
527
528 IF NVL(var.ca_sob_type,'P') = 'P' THEN
529 fa_rx_util_pkg.From_Clause :=
530 'AR_CASH_RECEIPTS CR,
531 FND_DOCUMENT_SEQUENCES DOCSEQ,
532 AR_VAT_TAX TAX,
533 -- AP_BANK_ACCOUNTS ABA,
534 -- AP_BANK_BRANCHES ABB,
535 CE_BANK_ACCOUNTS CBA,
536 CE_BANK_ACCT_USES_ALL ABA,
537 CE_BANK_BRANCHES_V ABB,
538 AR_RECEIPT_METHODS ARM,
539 AR_CASH_RECEIPT_HISTORY CRH,
540 GL_CODE_COMBINATIONS CC,
541 HZ_CUST_ACCOUNTS CUST,
542 HZ_PARTIES PARTY,
543 AR_BATCHES BATCH,
544 AR_CASH_RECEIPT_HISTORY CRHFIRST,
545 AR_BATCHES BATCHFIRST';
546 ELSE
547 fa_rx_util_pkg.From_Clause :=
548 'AR_CASH_RECEIPTS_MRC_V CR,
549 FND_DOCUMENT_SEQUENCES DOCSEQ,
550 AR_VAT_TAX TAX,
551 -- AP_BANK_ACCOUNTS ABA,
552 -- AP_BANK_BRANCHES ABB,
553 CE_BANK_ACCOUNTS CBA,
554 CE_BANK_ACCT_USES_ALL ABA,
555 CE_BANK_BRANCHES_V ABB,
556 AR_RECEIPT_METHODS ARM,
557 AR_CASH_RECEIPT_HIST_MRC_V CRH,
558 GL_CODE_COMBINATIONS CC,
559 HZ_CUST_ACCOUNTS CUST,
560 HZ_PARTIES PARTY,
561 AR_BATCHES_MRC_V BATCH,
562 AR_CASH_RECEIPT_HIST_MRC_V CRHFIRST,
563 AR_BATCHES_MRC_V BATCHFIRST';
564 END IF;
565
566 END IF;
567
568 IF nvl(var.calling_program,'not receipt') = 'RECEIPT'
569 THEN
570 fa_rx_util_pkg.debug('Define FROM_CLAUSE using _ALL tables for RECEIPT');
571
572 IF NVL(var.ca_sob_type,'P') = 'P' THEN
573 fa_rx_util_pkg.From_Clause :=
574 fa_rx_util_pkg.From_Clause ||
575 ',AR_CASH_RECEIPT_HISTORY CRHLAST';
576 ELSE
577 fa_rx_util_pkg.From_Clause :=
578 fa_rx_util_pkg.From_Clause ||
579 ',AR_CASH_RECEIPT_HIST_MRC_V CRHLAST';
580 END IF;
581
582 END IF;
583
584
585 fa_rx_util_pkg.debug('Define WHERE_CLAUSE');
586
587 fa_rx_util_pkg.Where_Clause := '
588 CR.CASH_RECEIPT_ID = CRHFIRST.CASH_RECEIPT_ID
589 AND CRHFIRST.FIRST_POSTED_RECORD_FLAG = ''Y''
590 AND CRHFIRST.BATCH_ID = BATCHFIRST.BATCH_ID(+)
591 AND CRH.BATCH_ID = BATCH.BATCH_ID(+)
592 AND CR.DOC_SEQUENCE_ID = DOCSEQ.DOC_SEQUENCE_ID(+)
593 AND CR.VAT_TAX_ID = TAX.VAT_TAX_ID(+)
594 AND CR.REMIT_BANK_ACCT_USE_ID = ABA.BANK_ACCT_USE_ID
595 AND CBA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
596 AND ABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
597 AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
598 AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
599 AND CC.CODE_COMBINATION_ID = CRH.ACCOUNT_CODE_COMBINATION_ID
600 AND CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+)
601 AND CUST.PARTY_ID = PARTY.PARTY_ID(+) '||
602 BATCH_NAME_WHERE ||' '||
603 CUSTOMER_NAME_WHERE ||' '||
604 DEPOSIT_DATE_WHERE ||' '||
605 RECEIPT_STATUS_WHERE ||' '||
606 RECEIPT_NUMBER_WHERE ||' '||
607 INVOICE_NUMBER_WHERE ||' '||
608 RECEIPT_DATE_WHERE ||' '||
609 CURRENCY_CODE_WHERE ||' '||
610 BANK_NAME_WHERE ||' '||
611 PAYMENT_METHOD_WHERE ||' '||
612 DOC_SEQUENCE_NUMBER_WHERE || ' ' ||
613 DOC_SEQUENCE_NAME_WHERE || ' ' ||
614 CONFIRMED_FLAG_WHERE || ' ' ||
615 CRH_GL_DATE_WHERE;
616
617 if var.calling_program in ('RECEIPT','APPLIED','MISC','ACTUAL') THEN
618
619 fa_rx_util_pkg.debug('Define Multi-org logic');
620
621 --Bug 5244313 added the var.books_id in the where clause to pickup the sob based on the reporting context
622 -- if p_sob_id is null
623 select CHART_OF_ACCOUNTS_ID,CURRENCY_CODE,NAME
624 into var.p_coa_id,var.functional_currency_code,var.organization_name
625 from GL_SETS_OF_BOOKS
626 where SET_OF_BOOKS_ID =nvl( var.p_sob_id,var.books_id);
627
628 fa_rx_util_pkg.debug('Set of Books ID : '||var.p_sob_id);
629 fa_rx_util_pkg.debug('Chart of Accounts ID : '||var.p_coa_id);
630 fa_rx_util_pkg.debug('Functional Currency : '||var.functional_currency_code);
631 fa_rx_util_pkg.debug('Organization Name : '||var.organization_name);
632
633 XLA_MO_REPORTING_API.Initialize(var.p_reporting_level, var.p_reporting_entity_id, 'AUTO');
634
635 L_CR_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CR','push_subq');
636 L_TAX_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('TAX','push_subq');
637 L_ABA_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('ABA','push_subq');
638 L_CRH_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CRH','push_subq');
639 L_CUST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CUST','push_subq');
640 L_CRHFIRST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CRHFIRST','push_subq');
641 L_BATCHFIRST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('BATCHFIRST','push_subq');
642 L_BATCH_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('BATCH','push_subq');
643 L_RA_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('RA','push_subq');
644 L_MISC_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('MISC','push_subq');
645 L_INCRH_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('INCRH','push_subq');
646 L_CRHNEW_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CRHNEW','push_subq');
647
648 L_CI_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CI','push_subq');
649 L_RA_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('RA','push_subq');
650 L_TRX_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('TRX','push_subq');
651 L_REC_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('REC','push_subq');
652 L_RAC_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('RAC','push_subq');
653 L_PS_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('PS','push_subq');
654 L_MCD_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('MCD','push_subq');
655 L_ARD_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('ARD','push_subq');
656 L_BS_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('BS','push_subq');
657
658 IF var.p_co_seg_low IS NULL AND var.p_co_seg_high IS NULL THEN
659 CO_SEG_WHERE := NULL;
660 ELSIF var.p_co_seg_low IS NULL THEN
661 CO_SEG_WHERE := ' AND ' ||
662 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
663 p_id_flex_code => 'GL#',
664 p_id_flex_num => var.p_coa_id,
665 p_table_alias => 'CC',
666 p_mode => 'WHERE',
667 p_qualifier => 'GL_BALANCING',
668 p_function => '<=',
669 p_operand1 => var.p_co_seg_high);
670 ELSIF var.p_co_seg_high IS NULL THEN
671 CO_SEG_WHERE := ' AND ' ||
672 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
673 p_id_flex_code => 'GL#',
674 p_id_flex_num => var.p_coa_id,
675 p_table_alias => 'CC',
676 p_mode => 'WHERE',
677 p_qualifier => 'GL_BALANCING',
678 p_function => '>=',
679 p_operand1 => var.p_co_seg_low);
680 ELSE
681 CO_SEG_WHERE := ' AND ' ||
682 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
683 p_id_flex_code => 'GL#',
684 p_id_flex_num => var.p_coa_id,
685 p_table_alias => 'CC',
686 p_mode => 'WHERE',
687 p_qualifier => 'GL_BALANCING',
688 p_function => 'BETWEEN',
689 p_operand1 => var.p_co_seg_low,
690 p_operand2 => var.p_co_seg_high);
691 END IF;
692
693 end if;
694
695 if var.calling_program = 'RECEIPT' then
696
697 fa_rx_util_pkg.debug('Define additional WHERE_CLAUSE for Receipts Register');
698
699
700 IF NVL(var.ca_sob_type,'P') = 'P' THEN
701
702 fa_rx_util_pkg.Where_Clause :=
703 fa_rx_util_pkg.Where_Clause ||
704 ' AND CRH.CASH_RECEIPT_HISTORY_ID IN (
705 SELECT NVL(MAX(CRHNEW.CASH_RECEIPT_HISTORY_ID ), MAX(INCRH.CASH_RECEIPT_HISTORY_ID))
706 FROM AR_CASH_RECEIPT_HISTORY INCRH ,
707 AR_CASH_RECEIPT_HISTORY CRHNEW
708 WHERE INCRH.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
709 AND CRHNEW.CASH_RECEIPT_ID(+) = INCRH.CASH_RECEIPT_ID
710 AND CRHNEW.CURRENT_RECORD_FLAG(+) = ''Y'' ' ||
711 INCRH_GL_DATE_WHERE ||
712 CRHNEW_GL_DATE_WHERE || ')
713 AND CRHLAST.CASH_RECEIPT_HISTORY_ID = (
714 SELECT nvl(MAX(INCRH2.CASH_RECEIPT_HISTORY_ID),CRH.CASH_RECEIPT_HISTORY_ID)
715 FROM AR_CASH_RECEIPT_HISTORY INCRH2
716 WHERE INCRH2.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID '||
717 ' AND INCRH2.GL_DATE < :c_gl_date_low)
718 AND CRH.CASH_RECEIPT_ID = CRHLAST.CASH_RECEIPT_ID
719 AND (decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID, CRH.ACCTD_AMOUNT +1
720 , CRHLAST.ACCTD_AMOUNT) <> CRH.ACCTD_AMOUNT
721 OR decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID,NVL( CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0) + 1
722 ,nvl(CRHLAST.ACCTD_FACTOR_DISCOUNT_AMOUNT,0)) <>nvl( CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0)
723 OR CRH.STATUS=''REVERSED'')' || CO_SEG_WHERE;
724 ELSE
725
726 fa_rx_util_pkg.Where_Clause :=
727 fa_rx_util_pkg.Where_Clause ||
728 ' AND CRH.CASH_RECEIPT_HISTORY_ID IN (
729 SELECT MAX(INCRH.CASH_RECEIPT_HISTORY_ID)
730 FROM AR_CASH_RECEIPT_HIST_ALL_MRC_V INCRH
731 WHERE INCRH.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
732 AND INCRH.GL_DATE BETWEEN :p_gl_date_low AND :p_gl_date_high)
733 AND CRHLAST.CASH_RECEIPT_HISTORY_ID = (
734 SELECT nvl(MAX(INCRH2.CASH_RECEIPT_HISTORY_ID),CRH.CASH_RECEIPT_HISTORY_ID)
735 FROM AR_CASH_RECEIPT_HIST_MRC_V INCRH2
736 WHERE INCRH2.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID '||
737 ' AND INCRH2.GL_DATE < :c_gl_date_low)
738 AND CRH.CASH_RECEIPT_ID = CRHLAST.CASH_RECEIPT_HISTORY_ID
739 AND (decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID, CRH.ACCTD_AMOUNT +1
740 , CRHLAST.ACCTD_AMOUNT) <> CRH.ACCTD_AMOUNT
741 OR decode(CRHLAST.CASH_RECEIPT_HISTORY_ID , CRH.CASH_RECEIPT_HISTORY_ID,NVL( CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0) + 1
742 ,nvl(CRHLAST.ACCTD_FACTOR_DISCOUNT_AMOUNT,0)) <>nvl( CRH.ACCTD_FACTOR_DISCOUNT_AMOUNT,0)
743 OR CRH.STATUS=''REVERSED'')' || CO_SEG_WHERE;
744 END IF;
745 /*
746 fa_rx_util_pkg.Where_Clause :=
747 fa_rx_util_pkg.Where_Clause || ' ' ||
748 L_CR_ORG_WHERE || ' ' ||
749 L_TAX_ORG_WHERE || ' ' ||
750 L_ABA_ORG_WHERE || ' ' ||
751 L_CRH_ORG_WHERE || ' ' ||
752 L_CUST_ORG_WHERE || ' ' ||
753 L_BATCH_ORG_WHERE || ' ' ||
754 L_CRHFIRST_ORG_WHERE || ' ' ||
755 L_BATCHFIRST_ORG_WHERE;
756 */
757
758 -----------------------------------------
759 ELSIF var.calling_program = 'ACTUAL' then
760 -----------------------------------------
761
762 fa_rx_util_pkg.debug('Define additional ASSIGN_COLUMN for Actual Receipts Register');
763
764 DECODE_RA_STATUS := 'DECODE(RA.STATUS,''ACC'',:L_ACCO,''APP'',:L_APPL,''UNAPP'',:L_UNAPPL,''UNID'',:L_UNIDE)';
765
766 fa_rx_util_pkg.assign_column('A1 ','RA.RECEIVABLE_APPLICATION_ID' ,'RECEIVABLE_APPLICATION_ID',
767 'arrx_rc.var.receivable_application_id' ,'NUMBER');
768 fa_rx_util_pkg.assign_column('A2 ','RA.APPLY_DATE','APPLY_DATE',
769 'arrx_rc.var.apply_date' ,'DATE');
770 fa_rx_util_pkg.assign_column('A3 ',DECODE_RA_STATUS,'APPLICATION_STATUS',
771 'arrx_rc.var.application_status' ,'VARCHAR2', 20);
772 fa_rx_util_pkg.assign_column('A4 ','RA.AMOUNT_APPLIED' ,'AMOUNT_APPLIED_TO',
773 'arrx_rc.var.amount_applied_to' ,'NUMBER');
774 fa_rx_util_pkg.assign_column('A5 ','NVL(RA.AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED)' ,'AMOUNT_APPLIED_FROM',
775 'arrx_rc.var.amount_applied_from' ,'NUMBER');
776 fa_rx_util_pkg.assign_column('A6 ','NVL(RA.ACCTD_AMOUNT_APPLIED_TO,RA.ACCTD_AMOUNT_APPLIED_FROM)' ,'ACCTD_AMOUNT_APPLIED_TO',
777 'arrx_rc.var.acctd_amount_applied_to' ,'NUMBER');
778 fa_rx_util_pkg.assign_column('A7 ','RA.ACCTD_AMOUNT_APPLIED_FROM' ,'ACCTD_AMOUNT_APPLIED_FROM',
779 'arrx_rc.var.acctd_amount_applied_from' ,'NUMBER');
780 fa_rx_util_pkg.assign_column('A8 ','RA.EARNED_DISCOUNT_TAKEN' ,'EARNED_DISCOUNT_TAKEN',
781 'arrx_rc.var.earned_discount_taken' ,'NUMBER');
782 fa_rx_util_pkg.assign_column('A9 ','RA.UNEARNED_DISCOUNT_TAKEN','UNEARNED_DISCOUNT_TAKEN',
783 'arrx_rc.var.unearned_discount_taken' ,'NUMBER');
784 fa_rx_util_pkg.assign_column('A10','RA.ACCTD_EARNED_DISCOUNT_TAKEN' ,'ACCTD_EARNED_DISCOUNT_TAKEN',
785 'arrx_rc.var.acctd_earned_discount_taken' ,'NUMBER');
786 fa_rx_util_pkg.assign_column('A11','RA.ACCTD_UNEARNED_DISCOUNT_TAKEN' ,'ACCTD_UNEARNED_DISCOUNT_TAKEN',
787 'arrx_rc.var.acctd_unearned_discount_taken' ,'NUMBER');
788 fa_rx_util_pkg.assign_column('A12','TRX.CUSTOMER_TRX_ID' ,'APPLIED_CUSTOMER_TRX_ID',
789 'arrx_rc.var.applied_customer_trx_id' ,'NUMBER');
790 fa_rx_util_pkg.assign_column('A13','DECODE(RA.STATUS, ''ACC'', :l_trx_on_acc, TRX.TRX_NUMBER)','TRX_NUMBER' ,
791 'arrx_rc.var.trx_number','VARCHAR2', 20);
792 fa_rx_util_pkg.assign_column('A14','TRX.INVOICE_CURRENCY_CODE','TRX_CURRENCY_CODE',
793 'arrx_rc.var.trx_currency_code' ,'VARCHAR2', 15);
794 fa_rx_util_pkg.assign_column('A15','TRX.TRX_DATE','TRX_DATE',
795 'arrx_rc.var.trx_date','DATE');
796 fa_rx_util_pkg.assign_column('A16','REC.AMOUNT','TRX_AMOUNT' ,
797 'arrx_rc.var.trx_amount','NUMBER');
798 fa_rx_util_pkg.assign_column('A17','REC.ACCTD_AMOUNT' ,'ACCTD_TRX_AMOUNT',
799 'arrx_rc.var.acctd_trx_amount' ,'NUMBER');
800
801 fa_rx_util_pkg.debug('Define additional FROM_CLAUSE for Actual Receipts Register');
802
803 IF NVL(var.ca_sob_type,'P') = 'P' THEN
804 fa_rx_util_pkg.From_Clause :=
805 fa_rx_util_pkg.From_Clause || ',
806 AR_RECEIVABLE_APPLICATIONS RA,
807 RA_CUSTOMER_TRX TRX,
808 RA_CUST_TRX_LINE_GL_DIST REC';
809 ELSE
810 fa_rx_util_pkg.From_Clause :=
811 fa_rx_util_pkg.From_Clause || ',
812 AR_RECEIVABLE_APPS_ALL_MRC_V RA,
813 RA_CUSTOMER_TRX_ALL_MRC_V TRX,
814 RA_TRX_LINE_GL_DIST_ALL_MRC_V REC';
815 END IF;
816
817 fa_rx_util_pkg.debug('Define additional WHERE_CLAUSE for Actual Receipts Register');
818
819 fa_rx_util_pkg.Where_Clause :=
820 fa_rx_util_pkg.Where_Clause || '
821 AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
822 AND RA.DISPLAY = ''Y''
823 AND RA.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID(+)
824 AND RA.APPLIED_CUSTOMER_TRX_ID = REC.CUSTOMER_TRX_ID(+)
825 AND REC.LATEST_REC_FLAG(+) = ''Y''
826 AND CRH.FIRST_POSTED_RECORD_FLAG = ''Y'' ';
827
828 fa_rx_util_pkg.debug('Define additional WHERE_CLAUSE for Actual Receipts Register Pos2');
829 /*
830 fa_rx_util_pkg.Where_Clause :=
831 fa_rx_util_pkg.Where_Clause || ' ' ||
832 L_CR_ORG_WHERE || ' ' ||
833 L_TAX_ORG_WHERE || ' ' ||
834 L_ABA_ORG_WHERE || ' ' ||
835 L_CRH_ORG_WHERE || ' ' ||
836 L_CUST_ORG_WHERE || ' ' ||
837 L_BATCH_ORG_WHERE || ' ' ||
838 L_CRHFIRST_ORG_WHERE || ' ' ||
839 L_BATCHFIRST_ORG_WHERE || ' ' ||
840 L_RA_ORG_WHERE || ' ' ||
841 L_TRX_ORG_WHERE || ' ' ||
842 L_REC_ORG_WHERE ;
843 */
844 ------------------------------------------
845 ELSIF var.calling_program = 'APPLIED' then
846 ------------------------------------------
847
848 fa_rx_util_pkg.debug('Define additional WHERE_CLAUSE for Applied Receipts Register');
849
850 IF var.p_apply_date_low IS NULL AND var.p_apply_date_high IS NULL THEN
851 APPLY_DATE_WHERE := NULL;
852 ELSIF var.p_apply_date_low IS NULL THEN
853 APPLY_DATE_WHERE := ' AND RA.APPLY_DATE <= :p_apply_date_high';
854 ELSIF var.p_apply_date_high IS NULL THEN
855 APPLY_DATE_WHERE := ' AND RA.APPLY_DATE >= :p_apply_date_low';
856 ELSE
857 APPLY_DATE_WHERE := ' AND RA.APPLY_DATE BETWEEN :p_apply_date_low AND :p_apply_date_high';
858 END IF;
859
860 IF var.p_co_seg_low IS NULL AND var.p_co_seg_high IS NULL THEN
861 CO_SEG_WHERE := NULL;
862 ELSIF var.p_co_seg_low IS NULL THEN
863 CO_SEG_WHERE := ' AND ' ||
864 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
865 p_id_flex_code => 'GL#',
866 p_id_flex_num => var.chart_of_accounts_id,
867 p_table_alias => 'CC2',
868 p_mode => 'WHERE',
869 p_qualifier => 'GL_BALANCING',
870 p_function => '<=',
871 p_operand1 => var.p_co_seg_high);
872 ELSIF var.p_co_seg_high IS NULL THEN
873 CO_SEG_WHERE := ' AND ' ||
874 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
875 p_id_flex_code => 'GL#',
876 p_id_flex_num => var.chart_of_accounts_id,
877 p_table_alias => 'CC2',
878 p_mode => 'WHERE',
879 p_qualifier => 'GL_BALANCING',
880 p_function => '>=',
881 p_operand1 => var.p_co_seg_low);
882 ELSE
883 CO_SEG_WHERE := ' AND ' ||
884 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
885 p_id_flex_code => 'GL#',
886 p_id_flex_num => var.chart_of_accounts_id,
887 p_table_alias => 'CC2',
888 p_mode => 'WHERE',
889 p_qualifier => 'GL_BALANCING',
890 p_function => 'BETWEEN',
891 p_operand1 => var.p_co_seg_low,
892 p_operand2 => var.p_co_seg_high);
893 END IF;
894
895 IF var.p_receipt_gl_date_low IS NULL AND var.p_receipt_gl_date_high IS NULL THEN
896 GL_DATE_WHERE := NULL;
897 ELSIF var.p_receipt_gl_date_low IS NULL THEN
898 GL_DATE_WHERE := ' AND RA.GL_DATE <= :p_receipt_gl_date_high';
899 ELSIF var.p_receipt_gl_date_high IS NULL THEN
900 GL_DATE_WHERE := ' AND RA.GL_DATE >= :p_receipt_gl_date_low';
901 ELSE
902 GL_DATE_WHERE := ' AND RA.GL_DATE BETWEEN :p_receipt_gl_date_low AND :p_receipt_gl_date_high';
903 END IF;
904
905 IF var.p_customer_number_low IS NULL AND var.p_customer_number_high IS NULL THEN
906 CUST_NUM_WHERE := NULL;
907 ELSIF var.p_customer_number_low IS NULL THEN
908 CUST_NUM_WHERE := ' AND CUST.ACCOUNT_NUMBER <= :p_customer_number_high';
909 ELSIF var.p_customer_number_high IS NULL THEN
910 CUST_NUM_WHERE := ' AND CUST.ACCOUNT_NUMBER >= :p_customer_number_low';
911 ELSE
912 CUST_NUM_WHERE := ' AND CUST.ACCOUNT_NUMBER BETWEEN :p_customer_number_low AND :p_customer_number_high';
913 END IF;
914
915 if var.p_invoice_type_low IS NULL AND var.p_invoice_type_high IS NUll THEN
916 INV_TYPE_WHERE :=NULL;
917 ELSIF var.p_invoice_type_low IS NULL THEN
918 INV_TYPE_WHERE := ' AND arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'') <= :p_invoice_type_high';
919 ELSIF var.p_invoice_type_high IS NULL THEN
920 INV_TYPE_WHERE := ' AND arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'') >= :p_invoice_type_low';
921 ELSE
922 INV_TYPE_WHERE :=' AND arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'') ' ||
923 ' BETWEEN :p_invoice_type_low AND :p_invoice_type_high';
924 END IF;
925
926 rec_gain_loss := 'NVL(RA.ACCTD_AMOUNT_APPLIED_FROM - RA.ACCTD_AMOUNT_APPLIED_TO,0)';
927 applied_total :='NVL(RA.ACCTD_AMOUNT_APPLIED_TO,0)';
928
929 ar_setup.get( name => 'AR_SHOW_BILLING_NUMBER', val => BILL_FLAG );
930 IF (BILL_FLAG = 'Y') THEN
931 SHOW_BILL_WHERE := ' AND ps.cons_inv_id = ci.cons_inv_id(+)';
932 SHOW_BILL_FROM := ', ar_cons_inv ci ';
933 ELSE
934 SHOW_BILL_WHERE := NULL;
935 SHOW_BILL_FROM := NULL;
936 L_CI_ORG_WHERE := NULL;
937 END IF;
938
939 fa_rx_util_pkg.debug('Define additional ASSIGN_COLUMNS for Applied Receipts Register');
940
941 fa_rx_util_pkg.assign_column('A1 ','RA.RECEIVABLE_APPLICATION_ID' ,'RECEIVABLE_APPLICATION_ID',
942 'arrx_rc.var.receivable_application_id' ,'NUMBER');
943 fa_rx_util_pkg.assign_column('A2 ','RA.APPLY_DATE' ,'APPLY_DATE' ,
944 'arrx_rc.var.apply_date','DATE');
945 fa_rx_util_pkg.assign_column('A3 ','RAC.ACCOUNT_NUMBER' ,'RELATED_CUSTOMER' ,
946 'arrx_rc.var.related_customer','VARCHAR2', 50);
947 fa_rx_util_pkg.assign_column('A4 ','NVL(RA.AMOUNT_APPLIED,0)','AMOUNT_APPLIED_TO',
948 'arrx_rc.var.amount_applied_to' ,'NUMBER');
949 fa_rx_util_pkg.assign_column('A5 ','NVL(RA.AMOUNT_APPLIED_FROM,RA.AMOUNT_APPLIED)' ,'AMOUNT_APPLIED_FROM',
950 'arrx_rc.var.amount_applied_from' ,'NUMBER');
951 fa_rx_util_pkg.assign_column('A6 ',applied_total ,'ACCTD_AMOUNT_APPLIED_TO',
952 'arrx_rc.var.acctd_amount_applied_to' ,'NUMBER');
953 fa_rx_util_pkg.assign_column('A7 ','NVL(RA.ACCTD_AMOUNT_APPLIED_FROM,0)' ,'ACCTD_AMOUNT_APPLIED_FROM',
954 'arrx_rc.var.acctd_amount_applied_from' ,'NUMBER');
955 fa_rx_util_pkg.assign_column('A8 ','NVL(RA.EARNED_DISCOUNT_TAKEN,0)' ,'EARNED_DISCOUNT_TAKEN',
956 'arrx_rc.var.earned_discount_taken' ,'NUMBER');
957 fa_rx_util_pkg.assign_column('A9 ','NVL(RA.UNEARNED_DISCOUNT_TAKEN,0)' ,'UNEARNED_DISCOUNT_TAKEN',
958 'arrx_rc.var.unearned_discount_taken' ,'NUMBER');
959 fa_rx_util_pkg.assign_column('A10','NVL(RA.ACCTD_EARNED_DISCOUNT_TAKEN,0)' ,'ACCTD_EARNED_DISCOUNT_TAKEN',
960 'arrx_rc.var.acctd_earned_discount_taken' ,'NUMBER');
961 fa_rx_util_pkg.assign_column('A11','NVL(RA.ACCTD_UNEARNED_DISCOUNT_TAKEN,0)' ,'ACCTD_UNEARNED_DISCOUNT_TAKEN',
962 'arrx_rc.var.acctd_unearned_discount_taken' ,'NUMBER');
963 fa_rx_util_pkg.assign_column('A12','TRX.CUSTOMER_TRX_ID' ,'APPLIED_CUSTOMER_TRX_ID',
964 'arrx_rc.var.applied_customer_trx_id' ,'NUMBER');
965
966 IF (BILL_FLAG = 'Y') THEN
967 fa_rx_util_pkg.assign_column('A13','decode(ra.status, ''ACC'', :L_ONACCOUNT
968 , decode(ci.cons_billing_number, null, ps.trx_number
969 , substrb(rtrim(ci.cons_billing_number)||''/''||rtrim(to_char(trx.trx_number)),1,30)))' ,'TRX_NUMBER',
970 'arrx_rc.var.trx_number' ,'VARCHAR2', 30);-- bug 5767413
971 ELSE
972 fa_rx_util_pkg.assign_column('A13','decode(ra.status, ''ACC'', :L_ONACCOUNT, PS.TRX_NUMBER)','TRX_NUMBER' ,
973 'arrx_rc.var.trx_number','VARCHAR2', 30);--bug 5767413
974 END IF;
975
976 fa_rx_util_pkg.assign_column('A14','TRX.INVOICE_CURRENCY_CODE' ,'TRX_CURRENCY_CODE',
977 'arrx_rc.var.trx_currency_code' ,'VARCHAR2', 15);
978 fa_rx_util_pkg.assign_column('A15','TRX.TRX_DATE' ,'TRX_DATE' ,
979 'arrx_rc.var.trx_date','DATE');
980 fa_rx_util_pkg.assign_column('A16','NVL(REC.AMOUNT,0)' ,'TRX_AMOUNT' ,
981 'arrx_rc.var.trx_amount','NUMBER');
982 fa_rx_util_pkg.assign_column('A17','NVL(REC.ACCTD_AMOUNT,0)' ,'ACCTD_TRX_AMOUNT',
983 'arrx_rc.var.acctd_trx_amount' ,'NUMBER');
984 fa_rx_util_pkg.assign_column('A18',rec_gain_loss ,'RECEIPT_GAIN_LOSS',
985 'arrx_rc.var.receipt_gain_loss' ,'NUMBER');
986 -- bug 1821300. Override RR gl_date with ARR gl_date to avoid ora-00957
987 fa_rx_util_pkg.assign_column('340','RA.GL_DATE' ,'GL_DATE' ,
988 'arrx_rc.var.gl_date','DATE');
989 -- bug 2379856. Override CRH.ccid with RA.ccid
990 fa_rx_util_pkg.assign_column('400','CC2.CODE_COMBINATION_ID' ,'ACCOUNT_CODE_COMBINATION_ID',
991 'arrx_rc.var.account_code_combination_id' ,'NUMBER');
992 fa_rx_util_pkg.assign_column('500','BS.NAME' ,'BATCH_SOURCE',
993 'arrx_rc.var.batch_source' ,'VARCHAR2',50);
994
995 fa_rx_util_pkg.debug('Define additional FROM_CLAUSE for Applied Receipts Register');
996
997 IF NVL(var.ca_sob_type,'P') = 'P' THEN
998 fa_rx_util_pkg.From_Clause :=
999 fa_rx_util_pkg.From_Clause ||
1000 ', AR_RECEIVABLE_APPLICATIONS RA,
1001 RA_CUSTOMER_TRX TRX,
1002 RA_CUST_TRX_LINE_GL_DIST REC,
1003 HZ_CUST_ACCOUNTS RAC ,
1004 AR_PAYMENT_SCHEDULES PS ,
1005 AR_BATCH_SOURCES BS ,
1006 GL_CODE_COMBINATIONS CC2 ' || SHOW_BILL_FROM;
1007 ELSE
1008 fa_rx_util_pkg.From_Clause :=
1009 fa_rx_util_pkg.From_Clause ||
1010 ', AR_RECEIVABLE_APPS_ALL_MRC_V RA,
1011 RA_CUSTOMER_TRX_ALL_MRC_V TRX,
1012 RA_TRX_LINE_GL_DIST_ALL_MRC_V REC,
1013 HZ_CUST_ACCOUNTS_ALL RAC ,
1014 AR_PAYMENT_SCHEDULES_ALL_MRC_V PS ,
1015 GL_CODE_COMBINATIONS CC2 ' || SHOW_BILL_FROM;
1016 END IF;
1017
1018 fa_rx_util_pkg.Where_Clause :=
1019 fa_rx_util_pkg.Where_Clause ||
1020 ' AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1021 AND ( RA.STATUS = ''APP'' OR
1022 (RA.STATUS = ''ACTIVITY'' AND
1023 RA.RECEIVABLES_TRX_ID = -16))
1024 AND RA.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID(+)
1025 AND RA.APPLIED_CUSTOMER_TRX_ID = REC.CUSTOMER_TRX_ID(+)
1026 AND NVL(BATCH.BATCH_SOURCE_ID,-1) = BS.BATCH_SOURCE_ID(+)
1027 AND RA.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID(+)
1028 AND PS.CUSTOMER_ID = RAC.CUST_ACCOUNT_ID(+)
1029 AND REC.LATEST_REC_FLAG(+) = ''Y''
1030 AND RA.CODE_COMBINATION_ID = CC2.CODE_COMBINATION_ID
1031 AND CRH.FIRST_POSTED_RECORD_FLAG = ''Y'' ' ||
1032 APPLY_DATE_WHERE ||
1033 GL_DATE_WHERE ||
1034 RA_GL_DATE_WHERE ||
1035 CUST_NUM_WHERE ||
1036 INV_TYPE_WHERE ||
1037 CO_SEG_WHERE ||
1038 SHOW_BILL_WHERE || ' ';
1039
1040 IF (var.p_receipt_gl_date_low IS NOT NULL AND var.p_receipt_gl_date_high IS NOT NULL)
1041 OR
1042 (var.p_gl_date_low IS NOT NULL AND var.p_gl_date_high IS NOT NULL) THEN
1043 fa_rx_util_pkg.Hint_Clause := '/*+ leading(ra) */';
1044 END IF;
1045
1046 ---------------------------------------
1047 ELSIF var.calling_program = 'MISC' then
1048 ---------------------------------------
1049
1050 fa_rx_util_pkg.debug('Define additional ASSIGN_COLUMNS for Misc Receipts Register');
1051
1052 fa_rx_util_pkg.assign_column('A1 ','decode(ARD.SOURCE_TYPE,''TAX'',NULL,MCD.PERCENT)' ,'MISC_PERCENT',
1053 'arrx_rc.var.percent' ,'NUMBER');
1054 -- bug5444415
1055 IF arp_global.sysparam.accounting_method='ACCRUAL' THEN
1056 fa_rx_util_pkg.assign_column('A2 ','decode(SIGN(MCD.AMOUNT), 1, ARD.AMOUNT_CR,-1, SIGN(MCD.AMOUNT) * ARD.AMOUNT_DR, 0)' ,'MISC_AMOUNT' ,
1057 'arrx_rc.var.misc_amount' ,'NUMBER');
1058 ELSE
1059 fa_rx_util_pkg.assign_column('A2 ','MCD.AMOUNT','MISC_AMOUNT' ,'arrx_rc.var.misc_amount' ,'NUMBER');
1060 END IF ;
1061 fa_rx_util_pkg.assign_column('A3 ','BS.NAME' ,'BATCH_SOURCE',
1062 'arrx_rc.var.batch_source' ,'VARCHAR2',50);
1063 /* Bugfix 2842928. Override CRH.ccid with ARD.ccid */
1064 fa_rx_util_pkg.assign_column('400','CC2.CODE_COMBINATION_ID' ,'ACCOUNT_CODE_COMBINATION_ID',
1065 'arrx_rc.var.account_code_combination_id' ,'NUMBER');
1066 /* Bugfix 2842928. Tax Code should be displayed only for TAX lines. */
1067 fa_rx_util_pkg.assign_column('200','decode(ARD.SOURCE_TYPE,''TAX'', TAX.TAX_CODE, NULL)' ,'TAX_CODE',
1068 'arrx_rc.var.tax_code' ,'VARCHAR2', 50);
1069
1070 fa_rx_util_pkg.debug('Define additional FROM_CLAUSE for Misc Receipts Register');
1071
1072 /* Bugfix 2842928. Added GL_CODE_COMBINATIONS CC2, AR_DISTRIBUTIONS ARD */
1073 fa_rx_util_pkg.From_Clause :=
1074 fa_rx_util_pkg.From_Clause ||
1075 ' ,AR_MISC_CASH_DISTRIBUTIONS MCD,
1076 AR_DISTRIBUTIONS ARD,
1077 GL_CODE_COMBINATIONS CC2,
1078 AR_BATCH_SOURCES BS ';
1079
1080 IF var.p_gl_date_low IS NULL AND var.p_gl_date_high IS NULL THEN
1081 MCD_GL_DATE_WHERE := NULL;
1082 ELSIF var.p_gl_date_low IS NULL THEN
1083 MCD_GL_DATE_WHERE := ' AND MCD.GL_DATE <= :p_gl_date_high';
1084 ELSIF var.p_gl_date_high IS NULL THEN
1085 MCD_GL_DATE_WHERE := ' AND MCD.GL_DATE >= :p_gl_date_low';
1086 ELSE
1087 MCD_GL_DATE_WHERE := ' AND MCD.GL_DATE BETWEEN :p_gl_date_low AND :p_gl_date_high';
1088 END IF;
1089 /*bug 5030073-5039469*/
1090 IF var.p_co_seg_low IS NULL AND var.p_co_seg_high IS NULL THEN
1091 CO_SEG_WHERE := NULL;
1092 ELSIF var.p_co_seg_low IS NULL THEN
1093 CO_SEG_WHERE := ' AND ' ||
1094 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
1095 p_id_flex_code => 'GL#',
1096 p_id_flex_num => var.chart_of_accounts_id,
1097 p_table_alias => 'CC2',
1098 p_mode => 'WHERE',
1099 p_qualifier => 'GL_BALANCING',
1100 p_function => '<=',
1101 p_operand1 => var.p_co_seg_high);
1102 ELSIF var.p_co_seg_high IS NULL THEN
1103 CO_SEG_WHERE := ' AND ' ||
1104 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
1105 p_id_flex_code => 'GL#',
1106 p_id_flex_num => var.chart_of_accounts_id,
1107 p_table_alias => 'CC2',
1108 p_mode => 'WHERE',
1109 p_qualifier => 'GL_BALANCING',
1110 p_function => '>=',
1111 p_operand1 => var.p_co_seg_low);
1112 ELSE
1113 CO_SEG_WHERE := ' AND ' ||
1114 FA_RX_FLEX_PKG.FLEX_SQL(p_application_id => 101,
1115 p_id_flex_code => 'GL#',
1116 p_id_flex_num => var.chart_of_accounts_id,
1117 p_table_alias => 'CC2',
1118 p_mode => 'WHERE',
1119 p_qualifier => 'GL_BALANCING',
1120 p_function => 'BETWEEN',
1121 p_operand1 => var.p_co_seg_low,
1122 p_operand2 => var.p_co_seg_high);
1123 END IF;
1124
1125 -- Bug 2328165. Removed "CR.REVERSAL_DATE IS NULL" so that reversed
1126 -- receipts shows up, added MCD_GL_DATE_WHERE to restrict the appearance
1127 -- of the receipt.
1128
1129 -- Bug 2514857
1130 -- Added " CRH.FIRST_POSTED_RECORD_FLAG = 'Y' "
1131 /* Bugfix 2842928. */
1132 -- Bug 3376034
1133 -- Modified (CRH.FIRST_POSTED_RECORD_FLAG = ''Y'' OR CRH.STATUS =''REVERSED'') '
1134
1135 /*bug 5444415, Changed ARD.SOURCE_ID to ARD.SOURCE_ID(+), ARD.SOURCE_TABLE to ARD.SOURCE_TABLE(+) and
1136 ARD.CODE_COMBINATION_ID to MCD.CODE_COMBINATION_ID */
1137 /*bug 5030073-5039469 Added Co_seg_where*/
1138 fa_rx_util_pkg.Where_Clause :=
1139 fa_rx_util_pkg.Where_Clause ||
1140 ' AND MCD.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID ' ||
1141 ' AND MCD.MISC_CASH_DISTRIBUTION_ID = ARD.SOURCE_ID(+) AND ARD.SOURCE_TABLE(+) = ''MCD'''||
1142 ' AND ARD.CODE_COMBINATION_ID = CC2.CODE_COMBINATION_ID'||
1143 ' AND NVL(BATCH.BATCH_SOURCE_ID,-1) = BS.BATCH_SOURCE_ID(+) ' ||
1144 ' AND CR.TYPE = ''MISC'' ' ||
1145 ' AND NVL(BATCH.ORG_ID, -1) = BS.ORG_ID(+)' || /*BUG 12619843*/
1146 ' AND ((CRH.CURRENT_RECORD_FLAG = ''Y'' AND CRH.STATUS = ''REVERSED'')
1147 OR (CRH.CASH_RECEIPT_HISTORY_ID IN (
1148 SELECT NVL(CRHNEW.CASH_RECEIPT_HISTORY_ID, INCRH.CASH_RECEIPT_HISTORY_ID)
1149 FROM AR_CASH_RECEIPT_HISTORY INCRH ,
1150 AR_CASH_RECEIPT_HISTORY CRHNEW
1151 WHERE INCRH.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
1152 AND INCRH.FIRST_POSTED_RECORD_FLAG = ''Y''
1153 AND CRHNEW.CASH_RECEIPT_ID(+) = INCRH.CASH_RECEIPT_ID
1154 AND CRHNEW.CURRENT_RECORD_FLAG(+) = ''Y''
1155 AND CRHNEW.STATUS(+) <> ''REVERSED''
1156 AND INCRH.STATUS <> ''REVERSED'' ' ||
1157 INCRH_GL_DATE_WHERE ||
1158 CRHNEW_GL_DATE_WHERE ||
1159 ' ))) ' ||
1160 CO_SEG_WHERE ||' ' ||
1161 MCD_GL_DATE_WHERE || ' ';
1162 END IF;
1163
1164
1165 fa_rx_util_pkg.debug('arrx_rc.before_report()-');
1166
1167 end before_report;
1168
1169 procedure bind(c in integer)
1170 is
1171 L_CASH varchar2(80);
1172 L_MISC varchar2(80);
1173 L_PAYMENT varchar2(80);
1174 L_RECEIPT varchar2(80);
1175 L_REMITTANCE varchar2(80);
1176 L_APPROVED varchar2(80);
1177 L_CLEARED varchar2(80);
1178 L_CONFIRMED varchar2(80);
1179 L_REMITTED varchar2(80);
1180 L_REVERSED varchar2(80);
1181 L_ACCO varchar2(80);
1182 L_APPL varchar2(80);
1183 L_UNAPPL varchar2(80);
1184 L_UNIDE varchar2(80);
1185 L_ONACCOUNT VARCHAR2(80);
1186
1187 begin
1188 fa_rx_util_pkg.debug('Define BIND variables');
1189
1190 -- Bug 4219081
1191
1192 if var.calling_program not in ('RECEIPT','APPLIED','MISC','ACTUAL') THEN
1193
1194 IF var.p_reporting_level = 3000 then
1195 IF var.p_reporting_entity_id IS NOT NULL THEN
1196 dbms_sql.bind_variable(c, 'p_reporting_entity_id', var.p_reporting_entity_id);
1197 END IF;
1198 END IF;
1199 END IF;
1200
1201 IF var.p_deposit_date_low IS NOT NULL THEN
1202 dbms_sql.bind_variable(c, 'p_deposit_date_low', var.p_deposit_date_low);
1203 END IF;
1204 IF var.p_deposit_date_high IS NOT NULL THEN
1205 dbms_sql.bind_variable(c, 'p_deposit_date_high', var.p_deposit_date_high);
1206 END IF;
1207
1208 IF var.p_receipt_date_low IS NOT NULL THEN
1209 dbms_sql.bind_variable(c, 'p_receipt_date_low', var.p_receipt_date_low);
1210 END IF;
1211 IF var.p_receipt_date_high IS NOT NULL THEN
1212 dbms_sql.bind_variable(c, 'p_receipt_date_high', var.p_receipt_date_high);
1213 END IF;
1214
1215 IF var.p_gl_date_low IS NOT NULL THEN
1216 dbms_sql.bind_variable(c, 'p_gl_date_low', var.p_gl_date_low);
1217 END IF;
1218 IF var.p_gl_date_high IS NOT NULL THEN
1219 dbms_sql.bind_variable(c, 'p_gl_date_high', var.p_gl_date_high);
1220 END IF;
1221
1222 IF nvl(var.calling_program ,'not receipt') = 'RECEIPT' THEN
1223 dbms_sql.bind_variable(c, 'c_gl_date_low', var.c_gl_date_low);
1224 END IF;
1225
1226 IF var.p_batch_name_low IS NOT NULL THEN
1227 dbms_sql.bind_variable(c, 'p_batch_name_low', var.p_batch_name_low);
1228 END IF;
1229 IF var.p_batch_name_high IS NOT NULL THEN
1230 dbms_sql.bind_variable(c, 'p_batch_name_high', var.p_batch_name_high);
1231 END IF;
1232 /* bug 5724171*/
1233 IF var.p_customer_name_low IS NOT NULL THEN
1234 dbms_sql.bind_variable(c, 'p_customer_name_low', var.p_customer_name_low);
1235 dbms_sql.bind_variable(c, 'len1',var.len1);
1236 END IF;
1237 IF var.p_customer_name_high IS NOT NULL THEN
1238 dbms_sql.bind_variable(c, 'p_customer_name_high', var.p_customer_name_high);
1239 dbms_sql.bind_variable(c, 'len2',var.len2);
1240 END IF;
1241
1242 IF var.p_receipt_status_low IS NOT NULL THEN
1243 dbms_sql.bind_variable(c, 'p_receipt_status_low', var.p_receipt_status_low);
1244 END IF;
1245 IF var.p_receipt_status_high IS NOT NULL THEN
1246 dbms_sql.bind_variable(c, 'p_receipt_status_high', var.p_receipt_status_high);
1247 END IF;
1248
1249 IF var.p_receipt_number_low IS NOT NULL THEN
1250 dbms_sql.bind_variable(c, 'p_receipt_number_low', var.p_receipt_number_low);
1251 END IF;
1252 IF var.p_receipt_number_high IS NOT NULL THEN
1253 dbms_sql.bind_variable(c, 'p_receipt_number_high', var.p_receipt_number_high);
1254 END IF;
1255
1256 IF var.p_invoice_number_low IS NOT NULL THEN
1257 dbms_sql.bind_variable(c, 'p_invoice_number_low', var.p_invoice_number_low);
1258 END IF;
1259 IF var.p_invoice_number_high IS NOT NULL THEN
1260 dbms_sql.bind_variable(c, 'p_invoice_number_high', var.p_invoice_number_high);
1261 END IF;
1262
1263 IF var.p_currency_code IS NOT NULL THEN
1264 dbms_sql.bind_variable(c, 'p_currency_code', var.p_currency_code);
1265 END IF;
1266
1267 IF var.p_bank_account_name IS NOT NULL THEN
1268 dbms_sql.bind_variable(c, 'p_bank_account_name', var.p_bank_account_name);
1269 END IF;
1270
1271 IF var.p_payment_method IS NOT NULL THEN
1272 dbms_sql.bind_variable(c, 'p_payment_method', var.p_payment_method);
1273 END IF;
1274
1275 IF var.p_confirmed_flag IS NOT NULL THEN
1276 dbms_sql.bind_variable(c, 'p_confirmed_flag', var.p_confirmed_flag);
1277 END IF;
1278
1279 select MEANING into L_CASH from ar_lookups where lookup_type='PAYMENT_CATEGORY_TYPE' and lookup_code='CASH';
1280 select MEANING into L_MISC from ar_lookups where lookup_type='PAYMENT_CATEGORY_TYPE' and lookup_code='MISC';
1281 select MEANING into var.l_app from ar_lookups where lookup_type='CHECK_STATUS' and lookup_code='APP';
1282 select MEANING into var.l_nsf from ar_lookups where lookup_type='CHECK_STATUS' and lookup_code='NSF';
1283 select MEANING into var.l_rev from ar_lookups where lookup_type='CHECK_STATUS' and lookup_code='REV';
1284 select MEANING into var.l_stop from ar_lookups where lookup_type='CHECK_STATUS' and lookup_code='STOP';
1285 select MEANING into var.l_unapp from ar_lookups where lookup_type='CHECK_STATUS' and lookup_code='UNAPP';
1286 select MEANING into var.l_unid from ar_lookups where lookup_type='CHECK_STATUS' and lookup_code='UNID';
1287 select MEANING into L_PAYMENT from ar_lookups where lookup_type='CB_REFERENCE_TYPE' and lookup_code='PAYMENT';
1288 select MEANING into L_RECEIPT from ar_lookups where lookup_type='CB_REFERENCE_TYPE' and lookup_code='RECEIPT';
1289 select MEANING into L_REMITTANCE from ar_lookups where lookup_type='CB_REFERENCE_TYPE' and lookup_code='REMITTANCE';
1290 select MEANING into L_APPROVED from ar_lookups where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='APPROVED';
1291 select MEANING into L_CLEARED from ar_lookups where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='CLEARED';
1292 select MEANING into L_CONFIRMED from ar_lookups where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='CONFIRMED';
1293 select MEANING into L_REMITTED from ar_lookups where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='REMITTED';
1294 select MEANING into L_REVERSED from ar_lookups where lookup_type='RECEIPT_CREATION_STATUS' and lookup_code='REVERSED';
1295
1296 dbms_sql.bind_variable(c, 'L_CASH' , L_CASH);
1297 dbms_sql.bind_variable(c, 'L_MISC' , L_MISC);
1298 dbms_sql.bind_variable(c, 'L_APP' , var.l_app);
1299 dbms_sql.bind_variable(c, 'L_NSF' , var.l_nsf);
1300 dbms_sql.bind_variable(c, 'L_REV' , var.l_rev);
1301 dbms_sql.bind_variable(c, 'L_STOP' , var.l_stop);
1302 dbms_sql.bind_variable(c, 'L_UNAPP' , var.l_unapp);
1303 dbms_sql.bind_variable(c, 'L_UNID' , var.l_unid);
1304 dbms_sql.bind_variable(c, 'L_PAYMENT' , L_PAYMENT);
1305 dbms_sql.bind_variable(c, 'L_RECEIPT' , L_RECEIPT);
1306 dbms_sql.bind_variable(c, 'L_REMITTANCE' , L_REMITTANCE);
1307 dbms_sql.bind_variable(c, 'L_APPROVED' , L_APPROVED);
1308 dbms_sql.bind_variable(c, 'L_CLEARED' , L_CLEARED);
1309 dbms_sql.bind_variable(c, 'L_CONFIRMED' , L_CONFIRMED);
1310 dbms_sql.bind_variable(c, 'L_REMITTED' , L_REMITTED);
1311 dbms_sql.bind_variable(c, 'L_REVERSED' , L_REVERSED);
1312
1313 if var.calling_program = 'ACTUAL' then
1314
1315 fa_rx_util_pkg.debug('Define additional BIND variables for Actual Receipts Register');
1316
1317 select MEANING into L_ACCO from ar_lookups where lookup_type='PAYMENT_TYPE' and lookup_code='ACC';
1318 select MEANING into L_APPL from ar_lookups where lookup_type='PAYMENT_TYPE' and lookup_code='APP';
1319 select MEANING into L_UNAPPL from ar_lookups where lookup_type='PAYMENT_TYPE' and lookup_code='UNAPP';
1320 select MEANING into L_UNIDE from ar_lookups where lookup_type='PAYMENT_TYPE' and lookup_code='UNID';
1321
1322 dbms_sql.bind_variable(c, 'L_ACCO' , L_ACCO);
1323 dbms_sql.bind_variable(c, 'L_APPL' , L_APPL);
1324 dbms_sql.bind_variable(c, 'L_UNAPPL' , L_UNAPPL);
1325 dbms_sql.bind_variable(c, 'L_UNIDE' , L_UNIDE);
1326 dbms_sql.bind_variable(c, 'l_trx_on_acc', l_acco);
1327
1328 elsif var.calling_program = 'APPLIED' then
1329
1330 fa_rx_util_pkg.debug('Define additional BIND variables for Applied Receipts Register');
1331
1332 SELECT substrb(meaning,1,20) INTO L_ONACCOUNT FROM ar_lookups WHERE lookup_type='PAYMENT_TYPE' AND lookup_code='ACC';
1333
1334 dbms_sql.bind_variable(c, 'L_ONACCOUNT' , L_ONACCOUNT);
1335
1336 IF var.p_apply_date_low IS NOT NULL THEN
1337 dbms_sql.bind_variable(c, 'p_apply_date_low', var.p_apply_date_low);
1338 END IF;
1339 IF var.p_apply_date_high IS NOT NULL THEN
1340 dbms_sql.bind_variable(c, 'p_apply_date_high', var.p_apply_date_high);
1341 END IF;
1342
1343 IF var.p_customer_number_low IS NOT NULL THEN
1344 dbms_sql.bind_variable(c, 'p_customer_number_low', var.p_customer_number_low);
1345 END IF;
1346 IF var.p_customer_number_high IS NOT NULL THEN
1347 dbms_sql.bind_variable(c, 'p_customer_number_high', var.p_customer_number_high);
1348 END IF;
1349
1350 IF var.p_invoice_type_low IS NOT NULL THEN
1351 dbms_sql.bind_variable(c,'p_invoice_type_low', var.p_invoice_type_low);
1352 END IF;
1353 IF var.p_invoice_type_high IS NOT NULL THEN
1354 dbms_sql.bind_variable(c, 'p_invoice_type_high',var.p_invoice_type_high);
1355 END IF;
1356
1357 end if;
1358
1359
1360 end bind;
1361
1362 procedure after_fetch
1363 is
1364 begin
1365 fa_rx_util_pkg.debug('Get Flexfield value and Description');
1366
1367 var.functional_currency_code := var.currency_code;
1368 var.organization_name := var.org_name;
1369
1370 IF NVL(var.ca_sob_type,'P') = 'P' THEN
1371 IF var.cr_status in ('REV', 'NSF', 'STOP') AND
1372 var.crh_status <> 'REVERSED' THEN
1373 select decode(
1374 sum(decode(status,'UNID', amount_applied,0)),
1375 0,
1376 decode(sum(decode(status,'UNAPP', amount_applied,0))
1377 , 0 , var.l_app , var.l_unapp),
1378 var.l_unid)
1379 into var.receipt_status
1380 from ar_receivable_applications
1381 where cash_receipt_id = var.cash_receipt_id;
1382 END IF;
1383 ELSE
1384 IF var.cr_status in ('REV', 'NSF', 'STOP') AND
1385 var.crh_status <> 'REVERSED' THEN
1386 select decode(
1387 sum(decode(status,'UNID', amount_applied,0)),
1388 0,
1389 decode(sum(decode(status,'UNAPP', amount_applied,0))
1390 , 0 , var.l_app , var.l_unapp),
1391 var.l_unid)
1392 into var.receipt_status
1393 from ar_receivable_apps_mrc_v
1394 where cash_receipt_id = var.cash_receipt_id;
1395 END IF;
1396 END IF;
1397
1398 var.debit_account := fa_rx_flex_pkg.get_value(
1399 p_application_id => 101,
1400 p_id_flex_code => 'GL#',
1401 p_id_flex_num => var.chart_of_accounts_id,
1402 p_qualifier => 'ALL',
1403 p_ccid => var.account_code_combination_id);
1404
1405 var.debit_account_desc := substrb(fa_rx_flex_pkg.get_description (
1406 p_application_id => 101,
1407 p_id_flex_code => 'GL#',
1408 p_id_flex_num => var.chart_of_accounts_id,
1409 p_qualifier => 'ALL',
1410 p_data => var.debit_account),1,240);
1411
1412 var.debit_balancing := fa_rx_flex_pkg.get_value(
1413 p_application_id => 101,
1414 p_id_flex_code => 'GL#',
1415 p_id_flex_num => var.chart_of_accounts_id,
1416 p_qualifier => 'GL_BALANCING',
1417 p_ccid => var.account_code_combination_id);
1418
1419 var.debit_balancing_desc := substrb(fa_rx_flex_pkg.get_description(
1420 p_application_id => 101,
1421 p_id_flex_code => 'GL#',
1422 p_id_flex_num => var.chart_of_accounts_id,
1423 p_qualifier => 'GL_BALANCING',
1424 p_data => var.debit_balancing),1,240);
1425
1426 var.debit_natacct := fa_rx_flex_pkg.get_value(
1427 p_application_id => 101,
1428 p_id_flex_code => 'GL#',
1429 p_id_flex_num => var.chart_of_accounts_id,
1430 p_qualifier => 'GL_ACCOUNT',
1431 p_ccid => var.account_code_combination_id);
1432
1433 var.debit_natacct_desc := substrb(fa_rx_flex_pkg.get_description(
1434 p_application_id => 101,
1435 p_id_flex_code => 'GL#',
1436 p_id_flex_num => var.chart_of_accounts_id,
1437 p_qualifier => 'GL_ACCOUNT',
1438 p_data => var.debit_natacct),1,240);
1439
1440 end after_fetch;
1441
1442 -------------------
1443 -- Receipt Register
1444 -------------------
1445 procedure arrc_rep (
1446 p_reporting_level IN VARCHAR2,
1447 p_reporting_entity_id IN NUMBER,
1448 p_sob_id IN NUMBER,
1449 p_coa_id IN NUMBER,
1450 p_co_seg_low IN VARCHAR2,
1451 p_co_seg_high IN VARCHAR2,
1452 p_gl_date_low in date,
1453 p_gl_date_high in date,
1454 p_currency_code in varchar2,
1455 p_batch_name_low in varchar2,
1456 p_batch_name_high in varchar2,
1457 p_customer_name_low in varchar2,
1458 p_customer_name_high in varchar2,
1459 p_deposit_date_low in date,
1460 p_deposit_date_high in date,
1461 p_receipt_status_low in varchar2,
1462 p_receipt_status_high in varchar2,
1463 p_receipt_number_low in varchar2,
1464 p_receipt_number_high in varchar2,
1465 p_invoice_number_low in varchar2, --Bug 1579930
1466 p_invoice_number_high in varchar2, --Bug 1579930
1467 p_receipt_date_low in date,
1468 p_receipt_date_high in date,
1469 p_bank_account_name in varchar2,
1470 p_payment_method in varchar2,
1471 p_confirmed_flag in varchar2,
1472 p_doc_sequence_name in varchar2,
1473 p_doc_sequence_number_from in number,
1474 p_doc_sequence_number_to in number,
1475 request_id in number,
1476 retcode out NOCOPY number,
1477 errbuf out NOCOPY varchar2)
1478 is
1479 l_profile_rsob_id NUMBER := NULL;
1480 l_client_info_rsob_id NUMBER := NULL;
1481 begin
1482 fa_rx_util_pkg.debug('arrx_rc.arrc_rep()+');
1483
1484 -- initialize all var to null
1485 arrx_rc.init_var;
1486
1487 -- Assign parameters to global variable
1488 -- These values will be used within the arrc_before_report trigger
1489 var.calling_program := 'RECEIPT';
1490 var.p_reporting_level := p_reporting_level;
1491 var.p_reporting_entity_id := p_reporting_entity_id;
1492 var.p_sob_id := p_sob_id;
1493 var.p_coa_id := p_coa_id;
1494 var.p_co_seg_low := p_co_seg_low;
1495 var.p_co_seg_high := p_co_seg_high;
1496 var.p_gl_date_low := Trunc(p_gl_date_low);
1497 var.p_gl_date_high := Trunc(p_gl_date_high)+1-1/24/60/60;
1498 var.p_currency_code := p_currency_code;
1499 var.p_batch_name_low := p_batch_name_low;
1500 var.p_batch_name_high := p_batch_name_high;
1501 var.p_customer_name_low := p_customer_name_low;
1502 var.p_customer_name_high := p_customer_name_high;
1503 var.p_deposit_date_low := Trunc(p_deposit_date_low);
1504 var.p_deposit_date_high := Trunc(p_deposit_date_high)+1-1/24/60/60;
1505 var.p_receipt_status_low := p_receipt_status_low;
1506 var.p_receipt_status_high := p_receipt_status_high;
1507 var.p_receipt_number_low := p_receipt_number_low;
1508 var.p_receipt_number_high := p_receipt_number_high;
1509 var.p_invoice_number_low := p_invoice_number_low; --Bug 1579930
1510 var.p_invoice_number_high := p_invoice_number_high; --Bug 1579930
1511 var.p_receipt_date_low := Trunc(p_receipt_date_low);
1512 var.p_receipt_date_high := Trunc(p_receipt_date_high)+1-1/24/60/60;
1513 var.p_bank_account_name := p_bank_account_name;
1514 var.p_payment_method := p_payment_method;
1515 var.p_confirmed_flag := p_confirmed_flag;
1516 var.p_doc_sequence_name := p_doc_sequence_name;
1517 var.p_doc_sequence_number_from := p_doc_sequence_number_from;
1518 var.p_doc_sequence_number_to := p_doc_sequence_number_to;
1519 var.request_id := request_id;
1520
1521
1522 /* Bug 5244313 Setting the SOB based on the Reporting context */
1523
1524 if p_reporting_level = 1000 then
1525 var.books_id := p_reporting_entity_id;
1526 mo_global.init('AR');
1527 mo_global.set_policy_context('M',null);
1528
1529 elsif p_reporting_level = 3000 then
1530
1531 select set_of_books_id
1532 into var.books_id
1533 from ar_system_parameters_all
1534 where org_id = p_reporting_entity_id;
1535
1536 mo_global.init('AR');
1537 mo_global.set_policy_context('S',p_reporting_entity_id);
1538
1539 end if;
1540
1541 fa_rx_util_pkg.debug('p_reporting_level = '||var.p_reporting_level);
1542 fa_rx_util_pkg.debug('p_reporting_entity_id = '||var.p_reporting_entity_id);
1543 fa_rx_util_pkg.debug('p_sob_id = '||var.p_sob_id);
1544 fa_rx_util_pkg.debug('p_coa_id = '||var.p_coa_id);
1545 fa_rx_util_pkg.debug('p_co_seg_low = '||var.p_co_seg_low);
1546 fa_rx_util_pkg.debug('p_co_seg_high = '||var.p_co_seg_high);
1547 fa_rx_util_pkg.debug('p_gl_date_from = '||var.p_gl_date_low);
1548 fa_rx_util_pkg.debug('p_gl_date_to = '||var.p_gl_date_high);
1549 fa_rx_util_pkg.debug('p_entered_currency = '||var.p_currency_code);
1550 fa_rx_util_pkg.debug('p_batch_name_low = '||var.p_batch_name_low);
1551 fa_rx_util_pkg.debug('p_batch_name_high = '||var.p_batch_name_high);
1552 fa_rx_util_pkg.debug('p_customer_name_low = '||var.p_customer_name_low);
1553 fa_rx_util_pkg.debug('p_customer_name_high = '||var.p_customer_name_high);
1554 fa_rx_util_pkg.debug('p_deposit_date_low = '||var.p_deposit_date_low);
1555 fa_rx_util_pkg.debug('p_deposit_date_high = '||var.p_deposit_date_high);
1556 fa_rx_util_pkg.debug('p_receipt_status_low = '||var.p_receipt_status_low);
1557 fa_rx_util_pkg.debug('p_receipt_status_high = '||var.p_receipt_status_high);
1558 fa_rx_util_pkg.debug('p_receipt_number_low = '||var.p_receipt_number_low);
1559 fa_rx_util_pkg.debug('p_receipt_number_high = '||var.p_receipt_number_high);
1560 fa_rx_util_pkg.debug('p_invoice_number_low = '||var.p_invoice_number_low);
1561 fa_rx_util_pkg.debug('p_invoice_number_high = '||var.p_invoice_number_high);
1562 fa_rx_util_pkg.debug('p_receipt_date_low = '||var.p_receipt_date_low);
1563 fa_rx_util_pkg.debug('p_receipt_date_high = '||var.p_receipt_date_high);
1564 fa_rx_util_pkg.debug('p_bank_account_name = '||var.p_bank_account_name);
1565 fa_rx_util_pkg.debug('p_payment_method = '||var.p_payment_method);
1566 fa_rx_util_pkg.debug('p_confirmed_flag = '||var.p_confirmed_flag);
1567 fa_rx_util_pkg.debug('p_doc_sequence_name = '||var.p_doc_sequence_name);
1568 fa_rx_util_pkg.debug('p_doc_sequence_number_from = '||var.p_doc_sequence_number_from);
1569 fa_rx_util_pkg.debug('p_doc_sequence_number_to = '||var.p_doc_sequence_number_to);
1570 fa_rx_util_pkg.debug('request_id = '||var.request_id);
1571
1572 /* Set the appropriate sob type into the global variable var.ca_sob_type */
1573 select to_number(nvl(replace(substr(userenv('CLIENT_INFO'),45,10),' '),-99))
1574 into l_client_info_rsob_id
1575 from dual;
1576
1577 fnd_profile.get('MRC_REPORTING_SOB_ID', l_profile_rsob_id);
1578 IF (l_client_info_rsob_id = NVL(l_profile_rsob_id,-1)) OR
1579 (l_client_info_rsob_id = -99)
1580 THEN
1581 fa_rx_util_pkg.debug('Setting the sob type to P');
1582 var.ca_sob_type := 'P';
1583 ELSE
1584 fa_rx_util_pkg.debug('Setting the sob type to R');
1585 var.ca_sob_id := l_client_info_rsob_id;
1586 var.ca_sob_type := 'R';
1587 END IF;
1588 --
1589 -- Initialize request
1590 fa_rx_util_pkg.debug('Initializing the request');
1591 fa_rx_util_pkg.init_request('arrx_rc.arrc_rep',request_id,'AR_RECEIPTS_REP_ITF');
1592
1593 fa_rx_util_pkg.assign_report('AR RECEIPTS',
1594 true,
1595 'arrx_rc.before_report;',
1596 'arrx_rc.bind(:CURSOR_SELECT);',
1597 'arrx_rc.after_fetch;',
1598 null);
1599
1600 --
1601 -- Run the report.
1602 -- Make sure to pass the p_calling_proc assigned from within this procedure ('arrx_rc.arrc_rep')
1603 fa_rx_util_pkg.run_report('arrx_rc.arrc_rep', retcode, errbuf);
1604
1605 fa_rx_util_pkg.debug('arrx_rc.arrc_rep()-');
1606
1607 exception
1608 when others then
1609 fa_rx_util_pkg.log(sqlcode);
1610 fa_rx_util_pkg.log(sqlerrm);
1611 fa_rx_util_pkg.debug(sqlcode);
1612 fa_rx_util_pkg.debug(sqlerrm);
1613 fa_rx_util_pkg.debug('arrx_rc.arrc_rep(EXCEPTION)-');
1614 end arrc_rep;
1615
1616 ------------------
1617 -- Actual Register
1618 ------------------
1619 procedure arrc_rep_actual (
1620 p_reporting_level IN varchar2,
1621 p_reporting_entity_id IN NUMBER,
1622 p_sob_id IN NUMBER,
1623 p_coa_id IN NUMBER,
1624 p_batch_name_low in varchar2,
1625 p_batch_name_high in varchar2,
1626 p_customer_name_low in varchar2,
1627 p_customer_name_high in varchar2,
1628 p_deposit_date_low in date,
1629 p_deposit_date_high in date,
1630 p_receipt_status_low in varchar2,
1631 p_receipt_status_high in varchar2,
1632 p_receipt_number_low in varchar2,
1633 p_receipt_number_high in varchar2,
1634 p_receipt_date_low in date,
1635 p_receipt_date_high in date,
1636 p_gl_date_low in date,
1637 p_gl_date_high in date,
1638 p_currency_code in varchar2,
1639 p_bank_account_name in varchar2,
1640 p_payment_method in varchar2,
1641 p_confirmed_flag in varchar2,
1642 request_id in number,
1643 retcode out NOCOPY number,
1644 errbuf out NOCOPY varchar2)
1645 is
1646 l_profile_rsob_id NUMBER := NULL;
1647 l_client_info_rsob_id NUMBER := NULL;
1648 begin
1649 fa_rx_util_pkg.debug('arrx_rc.arrc_rep_actual()+');
1650
1651 -- initialize all var to null
1652 arrx_rc.init_var;
1653
1654 --
1655 -- Assign parameters to global variable
1656 -- These values will be used within the before_report trigger
1657 var.calling_program := 'ACTUAL';
1658 var.p_reporting_level := p_reporting_level;
1659 var.p_reporting_entity_id := p_reporting_entity_id;
1660 var.p_sob_id := p_sob_id;
1661 var.p_coa_id := p_coa_id;
1662 var.ca_sob_type := 'P';
1663 var.p_batch_name_low := p_batch_name_low;
1664 var.p_batch_name_high := p_batch_name_high;
1665 var.p_customer_name_low := p_customer_name_low;
1666 var.p_customer_name_high := p_customer_name_high;
1667 var.p_deposit_date_low := Trunc(p_deposit_date_low);
1668 var.p_deposit_date_high := Trunc(p_deposit_date_high)+1-1/24/60/60;
1669 var.p_receipt_status_low := p_receipt_status_low;
1670 var.p_receipt_status_high := p_receipt_status_high;
1671 var.p_receipt_number_low := p_receipt_number_low;
1672 var.p_receipt_number_high := p_receipt_number_high;
1673 var.p_receipt_date_low := Trunc(p_receipt_date_low);
1674 var.p_receipt_date_high := Trunc(p_receipt_date_high)+1-1/24/60/60;
1675 var.p_gl_date_low := Trunc(p_gl_date_low);
1676 var.p_gl_date_high := Trunc(p_gl_date_high)+1-1/24/60/60;
1677 var.p_currency_code := p_currency_code;
1678 var.p_bank_account_name := p_bank_account_name;
1679 var.p_payment_method := p_payment_method;
1680 var.p_confirmed_flag := p_confirmed_flag;
1681 var.request_id := request_id;
1682
1683 if p_reporting_level = 1000 then
1684 var.books_id := p_reporting_entity_id;
1685 mo_global.init('AR');
1686 mo_global.set_policy_context('M',null);
1687
1688 elsif p_reporting_level = 3000 then
1689 select set_of_books_id
1690 into var.books_id
1691 from ar_system_parameters_all
1692 where org_id = var.p_reporting_entity_id;
1693
1694 mo_global.init('AR');
1695 mo_global.set_policy_context('S',var.p_reporting_entity_id);
1696
1697 end if;
1698 /* Set the appropriate sob type into the global variable var.ca_sob_type */
1699 select to_number(nvl(replace(substr(userenv('CLIENT_INFO'),45,10),' '),-99))
1700 into l_client_info_rsob_id from dual;
1701 fnd_profile.get('MRC_REPORTING_SOB_ID', l_profile_rsob_id);
1702 IF (l_client_info_rsob_id = NVL(l_profile_rsob_id,-1)) OR
1703 (l_client_info_rsob_id = -99)
1704 THEN
1705 fa_rx_util_pkg.debug('Setting the sob type to P');
1706 fa_rx_util_pkg.debug(l_client_info_rsob_id);
1707 var.ca_sob_type := 'P';
1708 ELSE
1709 fa_rx_util_pkg.debug('Setting the sob type to R');
1710 var.ca_sob_id := l_client_info_rsob_id;
1711 var.ca_sob_type := 'R';
1712 END IF;
1713
1714 -- Initialize request
1715 fa_rx_util_pkg.init_request('arrx_rc.arrc_rep_actual',request_id,'AR_RECEIPTS_REP_ITF');
1716
1717 --
1718 -- Assign triggers specific to this report
1719 -- Make sure that you make your assignment to the correct section ('AR RECEIPTS')
1720 fa_rx_util_pkg.assign_report('AR RECEIPTS',
1721 true,
1722 'arrx_rc.before_report;',
1723 'arrx_rc.bind(:CURSOR_SELECT);',
1724 'arrx_rc.after_fetch;',
1725 null);
1726
1727 --
1728 -- Run the report.
1729 -- Make sure to pass the p_calling_proc assigned from within this procedure ('arrx_rc.arrc_rep_actual')
1730 fa_rx_util_pkg.run_report('arrx_rc.arrc_rep_actual', retcode, errbuf);
1731
1732 fa_rx_util_pkg.debug('arrx_rc.arrc_rep_actual()-');
1733
1734 exception
1735 when others then
1736 fa_rx_util_pkg.log(sqlcode);
1737 fa_rx_util_pkg.log(sqlerrm);
1738 fa_rx_util_pkg.debug(sqlcode);
1739 fa_rx_util_pkg.debug(sqlerrm);
1740 fa_rx_util_pkg.debug('arrx_rc.arrc_rep_actual(EXCEPTION)-');
1741 end arrc_rep_actual;
1742
1743 -------------------
1744 -- Applied Register
1745 -------------------
1746 procedure arar_rep (
1747 p_reporting_level IN VARCHAR2,
1748 p_reporting_entity_id IN NUMBER,
1749 p_sob_id IN NUMBER,
1750 p_coa_id in number,
1751 p_co_seg_low in varchar2,
1752 p_co_seg_high in varchar2,
1753 p_gl_date_low in date,
1754 p_gl_date_high in date,
1755 p_currency_code in varchar2,
1756 p_batch_name_low in varchar2,
1757 p_batch_name_high in varchar2,
1758 p_customer_name_low in varchar2,
1759 p_customer_name_high in varchar2,
1760 p_customer_number_low in varchar2,
1761 p_customer_number_high in varchar2,
1762 p_apply_date_low in date,
1763 p_apply_date_high in date,
1764 p_receipt_number_low in varchar2,
1765 p_receipt_number_high in varchar2,
1766 p_invoice_number_low in varchar2,
1767 p_invoice_number_high in varchar2,
1768 p_invoice_type_low in varchar2,
1769 p_invoice_type_high in varchar2,
1770 request_id in number,
1771 retcode out NOCOPY number,
1772 errbuf out NOCOPY varchar2)
1773 is
1774 l_profile_rsob_id NUMBER := NULL;
1775 l_client_info_rsob_id NUMBER := NULL;
1776
1777
1778 begin
1779
1780 fa_rx_util_pkg.debug('arrx_rc.arar_rep()+');
1781
1782 -- initialize all var to null
1783 arrx_rc.init_var;
1784
1785 --5255926
1786 if p_reporting_level = 1000 then
1787 var.books_id := p_reporting_entity_id;
1788 mo_global.init('AR');
1789 mo_global.set_policy_context('M',null);
1790
1791 elsif p_reporting_level = 3000 then
1792
1793 select set_of_books_id
1794 into var.books_id
1795 from ar_system_parameters_all
1796 where org_id = p_reporting_entity_id;
1797
1798 mo_global.init('AR');
1799 mo_global.set_policy_context('S',p_reporting_entity_id);
1800
1801 end if;
1802
1803
1804 --
1805 -- Assign parameters to global variable
1806 -- These values will be used within the before_report trigger
1807
1808 var.calling_program := 'APPLIED';
1809 var.p_reporting_level := p_reporting_level;
1810 var.p_reporting_entity_id := p_reporting_entity_id;
1811 var.p_sob_id := p_sob_id;
1812 var.p_coa_id := p_coa_id;
1813 var.p_co_seg_low := p_co_seg_low;
1814 var.p_co_seg_high := p_co_seg_high;
1815 var.p_gl_date_low := Trunc(p_gl_date_low);
1816 var.p_gl_date_high := Trunc(p_gl_date_high)+1-1/24/60/60;
1817 var.p_currency_code := p_currency_code;
1818 var.p_batch_name_low := p_batch_name_low;
1819 var.p_batch_name_high := p_batch_name_high;
1820 var.p_customer_name_low := p_customer_name_low;
1821 var.p_customer_name_high := p_customer_name_high;
1822 var.p_customer_number_low := p_customer_number_low;
1823 var.p_customer_number_high := p_customer_number_high;
1824 var.p_apply_date_low := p_apply_date_low;
1825 var.p_apply_date_high := p_apply_date_high;
1826 var.p_receipt_number_low := p_receipt_number_low;
1827 var.p_receipt_number_high := p_receipt_number_high;
1828 var.p_invoice_number_low := p_invoice_number_low; --Bug 1579930
1829 var.p_invoice_number_high := p_invoice_number_high; --Bug 1579930
1830 var.p_invoice_type_low := p_invoice_type_low;
1831 var.p_invoice_type_high := p_invoice_type_high;
1832 var.request_id := request_id;
1833
1834 SELECT TO_NUMBER(NVL( REPLACE(SUBSTRB(USERENV('CLIENT_INFO'),45,10),' '),-99))
1835 INTO l_client_info_rsob_id
1836 FROM dual;
1837
1838 fnd_profile.get('MRC_REPORTING_SOB_ID', l_profile_rsob_id);
1839 IF (l_client_info_rsob_id = NVL(l_profile_rsob_id,-1)) OR
1840 (l_client_info_rsob_id = -99)
1841 THEN
1842 var.ca_sob_type := 'P';
1843 ELSE
1844 var.ca_sob_id := l_client_info_rsob_id;
1845 var.ca_sob_type := 'R';
1846 END IF;
1847
1848 --
1849 -- Initialize request
1850 fa_rx_util_pkg.init_request('arrx_rc.arar_rep',request_id,'AR_RECEIPTS_REP_ITF');
1851
1852 -- Assign triggers specific to this report
1853 -- Make sure that you make your assignment to the correct section ('AR RECEIPTS')
1854
1855 fa_rx_util_pkg.assign_report('AR RECEIPTS',
1856 true,
1857 'arrx_rc.before_report;',
1858 'arrx_rc.bind(:CURSOR_SELECT);',
1859 'arrx_rc.after_fetch;',
1860 null);
1861 --
1862 -- Run the report.
1863 -- Make sure to pass the p_calling_proc assigned from within this procedure
1864 fa_rx_util_pkg.run_report('arrx_rc.arar_rep', retcode, errbuf);
1865
1866 fa_rx_util_pkg.debug('arrx_rc.arar_rep()-');
1867 exception
1868 when others then
1869 fa_rx_util_pkg.log(sqlcode);
1870 fa_rx_util_pkg.log(sqlerrm);
1871 fa_rx_util_pkg.debug(sqlcode);
1872 fa_rx_util_pkg.debug(sqlerrm);
1873 end arar_rep;
1874
1875 -----------------------------------
1876 -- Miscellaneous Receipts Register
1877 -----------------------------------
1878 procedure armtr_rep (
1879 p_reporting_level IN VARCHAR2,
1880 p_reporting_entity_id IN NUMBER,
1881 p_sob_id IN NUMBER,
1882 p_coa_id in number,
1883 p_co_seg_low in varchar2,
1884 p_co_seg_high in varchar2,
1885 p_gl_date_low in date,
1886 p_gl_date_high in date,
1887 p_currency_code in varchar2,
1888 p_batch_name_low in varchar2,
1889 p_batch_name_high in varchar2,
1890 p_deposit_date_low in date,
1891 p_deposit_date_high in date,
1892 p_receipt_number_low in varchar2,
1893 p_receipt_number_high in varchar2,
1894 p_doc_sequence_name in varchar2,
1895 p_doc_sequence_number_from in number,
1896 p_doc_sequence_number_to in number,
1897 request_id in number,
1898 retcode out NOCOPY number,
1899 errbuf out NOCOPY varchar2)
1900 is
1901 begin
1902
1903 fa_rx_util_pkg.debug('arrx_rc.arar_rep()+');
1904
1905 -- initialize all var to null
1906 arrx_rc.init_var;
1907 --
1908 -- Assign parameters to global variable
1909 -- These values will be used within the before_report trigger
1910
1911 var.calling_program := 'MISC';
1912 var.p_reporting_level := p_reporting_level;
1913 var.p_reporting_entity_id := p_reporting_entity_id;
1914 var.p_sob_id := p_sob_id;
1915 var.p_coa_id := p_coa_id;
1916 var.p_co_seg_low := p_co_seg_low;
1917 var.p_co_seg_high := p_co_seg_high;
1918 var.p_gl_date_low := Trunc(p_gl_date_low);
1919 var.p_gl_date_high := Trunc(p_gl_date_high)+1-1/24/60/60;
1920 var.p_currency_code := p_currency_code;
1921 var.p_batch_name_low := p_batch_name_low;
1922 var.p_batch_name_high := p_batch_name_high;
1923 var.p_deposit_date_low := Trunc(p_deposit_date_low);
1924 var.p_deposit_date_high := Trunc(p_deposit_date_high)+1-1/24/60/60;
1925 var.p_receipt_number_low := p_receipt_number_low;
1926 var.p_receipt_number_high := p_receipt_number_high;
1927 var.p_doc_sequence_name := p_doc_sequence_name;
1928 var.p_doc_sequence_number_from := p_doc_sequence_number_from;
1929 var.p_doc_sequence_number_to := p_doc_sequence_number_to;
1930 var.request_id := request_id;
1931
1932
1933 /* Bug 5255942 Setting the SOB based on the Reporting context */
1934
1935 if p_reporting_level = 1000 then
1936 var.books_id := p_reporting_entity_id;
1937 mo_global.init('AR');
1938 mo_global.set_policy_context('M',null);
1939 elsif p_reporting_level = 3000 then
1940 select set_of_books_id
1941 into var.books_id
1942 from ar_system_parameters_all
1943 where org_id = p_reporting_entity_id;
1944 mo_global.init('AR');
1945 mo_global.set_policy_context('S',p_reporting_entity_id);
1946 end if;
1947
1948
1949 /*
1950 Compatibility with MRC changes. Treat other reports as regular and
1951 not to use any MRC views.
1952 */
1953 var.ca_sob_type := 'P';
1954
1955 --
1956 -- Initialize request
1957 fa_rx_util_pkg.init_request('arrx_rc.armtr_rep',request_id,'AR_RECEIPTS_REP_ITF');
1958
1959 fa_rx_util_pkg.assign_report('AR RECEIPTS',
1960 true,
1961 'arrx_rc.before_report;',
1962 'arrx_rc.bind(:CURSOR_SELECT);',
1963 'arrx_rc.after_fetch;',
1964 null);
1965 --
1966 -- Run the report.
1967 -- Make sure to pass the p_calling_proc assigned from within this procedure
1968 fa_rx_util_pkg.run_report('arrx_rc.armtr_rep', retcode, errbuf);
1969
1970 exception
1971 when others then
1972 fa_rx_util_pkg.log(sqlcode);
1973 fa_rx_util_pkg.log(sqlerrm);
1974 fa_rx_util_pkg.debug(sqlcode);
1975 fa_rx_util_pkg.debug(sqlerrm);
1976 end armtr_rep;
1977
1978 end ARRX_RC;
1979