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