[Home] [Help]
PACKAGE BODY: APPS.ARRX_ADJ
Source
1 PACKAGE BODY ARRX_ADJ as
2 /* $Header: ARRXADJB.pls 120.29 2011/06/14 02:25:14 chuansha ship $ */
3
4 -- make following vars global :
5 CO_SEG_WHERE varchar2(500);
6 ACCOUNTING_METHOD varchar2(30);
7
8 -- define the following function to get total amount from ar_Distributions
9 -- table and do away with breakdown listing per account which is not
10 -- necessary in a register type report
11
12 -- bug 4214787 : remove string that sets dist.org_id because this is not always
13 -- the correct condition when reporting level is NOT Operating Unit
14 function dist_details (adj_id in NUMBER, coa_id in NUMBER, rep_id in NUMBER, ret_type in VARCHAR2)
15 --Bug fix 5595083, replacing the reference of var.p_coa_id to var.chart_of_accounts_id
16 RETURN NUMBER IS
17
18 l_stmt VARCHAR2(2000);
19 ret_amt NUMBER;
20
21 begin
22
23 l_stmt :=
24 'select decode( ''' || ret_type || ''',
25 ''ENTERED'', sum(round(nvl(dist.amount_cr,(dist.amount_dr)*-1),2)),
26 ''ACCTD'', sum(nvl(dist.acctd_amount_cr,(dist.acctd_amount_dr)*-1)))
27 from ar_distributions_all dist, gl_code_combinations glc
28 where dist.source_id = ' || adj_id ||
29 ' and dist.source_Table = ''ADJ''
30 and dist.source_Type in ( ''ADJ'',
31 ''TAX'',
32 ''FINCHRG'',
33 ''ADJ_NON_REC_TAX'',
34 ''DEFERRED_TAX'',
35 ''FINCHRG_NON_REC_TAX'')
36 and glc.code_combination_id = dist.code_combination_id
37 and glc.chart_of_accounts_id = ' || coa_id ||
38 ' ' || co_seg_where;
39
40 execute immediate l_stmt into ret_amt;
41
42 return ret_amt;
43 end;
44
45 -- define a function that determines all ccid in ar_distributions for this
46 -- adjustment have the same balancing segment
47
48 -- bug 4214787 : remove string that sets dist.org_id because this is not always
49 -- the correct condition when reporting level is NOT Operating Unit
50 function dist_ccid (adj_id in number, coa_id in NUMBER, rep_id in NUMBER) RETURN
51 NUMBER IS
52
53 TYPE cur_typ IS REF CURSOR;
54 l_stmt VARCHAR2(2000);
55 ret_amt NUMBER;
56 c_dist cur_typ;
57 bal_seg1 varchar2(30);
58 bal_seg2 varchar2(30);
59 use_ccid number;
60 begin
61
62 l_stmt := 'select FA_RX_FLEX_PKG.GET_VALUE(101,''GL#'', ' || coa_id || ', ''GL_BALANCING'',dist.CODE_COMBINATION_ID), ' ||
63 ' dist.code_Combination_id from ar_distributions_all dist, gl_code_combinations glc where dist.source_id = ' || adj_id ||
64 ' and dist.source_Table = ''ADJ'' and dist.source_Type = ''REC'' ' ||' and glc.code_combination_id = dist.code_combination_id and glc.chart_of_accounts_id = ' ||
65 coa_id || ' ' || co_seg_where;
66
67 open c_dist for l_stmt;
68 bal_seg1 := '-1';
69 bal_seg2 := '-1';
70
71 loop
72
73 FETCH c_dist into bal_seg1, use_ccid;
74 EXIT WHEN c_dist%NOTFOUND;
75
76 if bal_seg1 <> bal_seg2 AND bal_seg2 <> -1 then
77 -- multiple bal segs for this adjustment
78 return -666;
79 else
80 bal_seg2 := bal_seg1;
81 end if;
82
83 end loop;
84
85 close c_dist;
86 return use_ccid;
87
88 exception
89 when no_data_found then
90
91 l_stmt := 'select adj.code_combination_id from ar_adjustments_all adj, gl_code_combinations glc ' ||
92 ' where adj.adjustment_id = ' || adj_id ||
93 ' and glc.code_combination_id = adj.code_combination_id ' ||
94 ' and glc.chart_of_accounts_id = ' || coa_id || ' ' || co_seg_where;
95
96 execute immediate l_stmt into use_ccid;
97 return use_ccid;
98 end;
99 --
100 -- Main AR Adjustments RX Report function
101 --
102 procedure aradj_rep (
103 request_id in number,
104 p_reporting_level in number,
105 p_reporting_entity in number,
106 p_sob_id in number,
107 p_coa_id in number,
108 p_co_seg_low in varchar2,
109 p_co_seg_high in varchar2,
110 p_gl_date_low in date,
111 p_gl_date_high in date,
112 p_currency_code_low in varchar2,
113 p_currency_code_high in varchar2,
114 p_trx_date_low in date,
115 p_trx_date_high in date,
116 p_due_date_low in date,
117 p_due_date_high in date,
118 p_invoice_type_low in varchar2,
119 p_invoice_type_high in varchar2,
120 p_adj_type_low in varchar2,
121 p_adj_type_high in varchar2,
122 p_doc_seq_name in varchar2,
123 p_doc_seq_low in number,
124 p_doc_seq_high in number,
125 retcode out NOCOPY number,
126 errbuf out NOCOPY varchar2)
127 is
128
129 begin
130
131 -- Asssign parameters to global variable
132 -- These values will be used within the before_report trigger
133
134 var.p_reporting_level := p_reporting_level;
135 var.p_reporting_entity_id := p_reporting_entity;
136 var.p_sob_id := p_sob_id;
137 var.request_id := request_id;
138 var.p_coa_id := p_coa_id;
139 var.p_gl_date_low := p_gl_date_low;
140 var.p_gl_date_high := p_gl_date_high;
141 var.p_trx_date_low := p_trx_date_low;
142 var.p_trx_date_high := p_trx_date_high;
143 var.p_due_date_low := p_due_date_low;
144 var.p_due_date_high := p_due_date_high;
145 var.p_invoice_type_low := p_invoice_type_low;
146 var.p_invoice_type_high := p_invoice_type_high;
147 var.p_adj_type_low := p_adj_type_low;
148 var.p_adj_type_high := p_adj_type_high;
149 var.p_currency_code_low := p_currency_code_low;
150 var.p_currency_code_high := p_currency_code_high;
151 var.p_co_seg_low := p_co_seg_low;
152 var.p_co_seg_high := p_co_seg_high;
153 var.p_doc_seq_name := p_doc_seq_name;
154 var.p_doc_seq_low := p_doc_seq_low;
155 var.p_doc_seq_high := p_doc_seq_high;
156 fa_rx_util_pkg.enable_debug;
157 --
158 -- Initialize request
159 fa_rx_util_pkg.init_request('arrx_adj.aradj_rep',request_id,'AR_ADJUSTMENTS_REP_ITF');
160
161
162 /* Bug 5244313 Setting the SOB based on the Reporting context */
163 if p_reporting_level = 1000 then
164 var.books_id := p_reporting_entity;
165 mo_global.init('AR');
166 mo_global.set_policy_context('M',null);
167
168 elsif p_reporting_level = 3000 then
169
170 select set_of_books_id
171 into var.books_id
172 from ar_system_parameters_all
173 where org_id = p_reporting_entity;
174
175 mo_global.init('AR');
176 mo_global.set_policy_context('S',p_reporting_entity);
177
178 end if;
179
180 --
181 -- Assign report triggers for this report.
182 -- This report has one section called AR RECEIPT
183 -- NOTE:
184 -- before_report is assigned 'arrx_adj.adj_before_report;'
185 -- bind is assigned 'arrx_rc.bind(:CURSOR_SELECT);'
186 -- Each trigger event is assigned with the full procedure name (including package name).
187 -- They end with a ';'.
188 -- The bind trigger requires one host variable ':CURSOR_SELECT'.
189 fa_rx_util_pkg.assign_report('AR ADJUSTMENTS',
190 true,
191 'arrx_adj.aradj_before_report;',
192 'arrx_adj.aradj_bind(:CURSOR_SELECT);',
193 'arrx_adj.aradj_after_fetch;',
194 null);
195
196 --
197 -- Run the report. Make sure to pass as parameter the same
198 -- value passed to p_calling_proc in init_request().
199 fa_rx_util_pkg.run_report('arrx_adj.aradj_rep', retcode, errbuf);
200
201 fa_rx_util_pkg.debug('arrx_adj.aradj_rep()-');
202
203 exception
204 when others then
205 fa_rx_util_pkg.log(sqlcode);
206 fa_rx_util_pkg.log(sqlerrm);
207 fa_rx_util_pkg.debug(sqlcode);
208 fa_rx_util_pkg.debug(sqlerrm);
209 fa_rx_util_pkg.debug('arrx_adj.aradj_rep(EXCEPTION)-');
210 end aradj_rep;
211
212
213 -- This is the before trigger for the main Adj Report ---
214
215 -- bug 4214787 :
216 -- a) use arpt_sql_func_util to get trx type information
217 -- b) remove join to RA_CUST_TRX_TYPES and AR_RECEIVABLES_TRX
218
219 procedure aradj_before_report
220 is
221 CURRENCY_CODE_WHERE varchar2(500);
222 INVOICE_TYPE_WHERE varchar2(500);
223 DUE_DATE_WHERE varchar2(500);
224 TRX_DATE_WHERE varchar2(500);
225 ADJ_TYPE_WHERE varchar2(500);
226 GL_DATE_WHERE varchar2(500);
227 REC_BALANCING_WHERE varchar2(500);
228 ADJ_ACCT_WHERE varchar2(800);
229 SEQ_NAME_WHERE varchar2(100);
230 SEQ_NUMBER_WHERE varchar2(100);
231
232 OPER varchar2(10);
233 OP1 varchar2(25);
234 OP2 varchar2(25);
235
236 SORTBY_DECODE varchar2(300); /*bug5968198*/
237 D_OR_I_DECODE varchar2(200);
238 ADJ_CLASS_DECODE varchar2(200);
239 POSTABLE_DECODE varchar2(100);
240
241 BALANCING_ORDER_BY varchar2(100);
242
243 -- Bug 2099632
244 SHOW_BILL_WHERE varchar2(100);
245 SHOW_BILL_FROM varchar2(100);
246 BILL_FLAG varchar2(1);
247
248 L_CUST_ORG_WHERE VARCHAR2(500);
249 L_PAY_ORG_WHERE VARCHAR2(500);
250 L_ADJ_ORG_WHERE VARCHAR2(500);
251 L_CI_ORG_WHERE VARCHAR2(500);
252 L_TRX_ORG_WHERE VARCHAR2(500);
253 L_SYSPARAM_ORG_WHERE VARCHAR2(500);--New variable for bug fix 5595083
254 acct_stmt VARCHAR2(600);--New variable for bug fix 5595083
255
256 DIST_ENTERED VARCHAR2(500);
257 DIST_ACCTD VARCHAR2(500);
258 DIST_CCID_STR VARCHAR2(500);
259
260 begin
261
262 fa_rx_util_pkg.debug('arrx_adj.adj_before_report()+');
263
264 --
265 -- Get Profile GL_SET_OF_BKS_ID
266 --
267 fa_rx_util_pkg.debug('GL_GET_PROFILE_BKS_ID');
268
269 -- bug 5244313
270 -- var.books_id := arp_global.sysparam.set_of_books_id;
271
272 --
273 -- Get CHART_OF_ACCOUNTS_ID
274 --
275 fa_rx_util_pkg.debug('GL_GET_CHART_OF_ACCOUNTS_ID');
276
277 select CHART_OF_ACCOUNTS_ID,CURRENCY_CODE,NAME
278 into var.chart_of_accounts_id,var.functional_currency_code,var.organization_name
279 from GL_SETS_OF_BOOKS
280 where SET_OF_BOOKS_ID = var.books_id;
281
282 fa_rx_util_pkg.debug('Chart of Accounts ID : '||var.chart_of_accounts_id);
283 fa_rx_util_pkg.debug('Functional Currency : '||var.functional_currency_code);
284 fa_rx_util_pkg.debug('Organization Name : '||var.organization_name);
285
286 -- Get Accounting Method --5244313
287 --bug fix 5595083
288 --Changing the logic of getting the accounting_method. The accounting_method should belong to the ledger for which the report is being generated.
289 --SELECT distinct ACCOUNTING_METHOD
290 --INTO ACCOUNTING_METHOD
291 --FROM ar_system_parameters;
292
293 XLA_MO_REPORTING_API.Initialize(var.p_reporting_level, var.p_reporting_entity_id, 'AUTO');
294
295 L_CUST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CUST',NULL);
296 L_PAY_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('PAY',NULL);
297 L_ADJ_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('ADJ',NULL);
298 L_TRX_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('TRX',NULL);
299
300 --Bug fix 5595083 starts
301 L_SYSPARAM_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('SYSPARAM',NULL);
302 acct_stmt := 'select distinct ACCOUNTING_METHOD FROM ar_system_parameters_all SYSPARAM where ACCOUNTING_METHOD is not null ' || L_SYSPARAM_ORG_WHERE ;
303 IF var.p_reporting_level = 3000 then
304 Select decode(nvl(sla_ledger_cash_basis_flag, 'N'),'N', 'ACCRUAL', 'CASH')
305 into ACCOUNTING_METHOD
306 from gl_ledgers
307 where ledger_id = arp_global.sysparam.set_of_books_id;
308 ELSE
309 /*Bug 11825918 -- Report run for a particular Ledger*/
310 Select decode(nvl(sla_ledger_cash_basis_flag, 'N'),'N', 'ACCRUAL', 'CASH')
311 into ACCOUNTING_METHOD
312 from gl_ledgers
313 where ledger_id = var.p_reporting_entity_id;
314 END IF;
315 --Bug fix 5595083 ends
316 --
317 -- Figure out NOCOPY the where clause for the parameters
318 --
319 fa_rx_util_pkg.debug('AR_GET_PARAMETERS');
320
321 --
322 --CURRENCY_CODE where clause
323 --
324
325 IF var.p_currency_code_low IS NULL AND var.p_currency_code_high IS NULL THEN
326 CURRENCY_CODE_WHERE := NULL;
327 ELSIF var.p_currency_code_low IS NULL THEN
328 CURRENCY_CODE_WHERE := ' AND TRX.INVOICE_CURRENCY_CODE <= :p_currency_code_high';
329 ELSIF var.p_currency_code_high IS NULL THEN
330 CURRENCY_CODE_WHERE := ' AND TRX.INVOICE_CURRENCY_CODE >= :p_currency_code_low';
331 ELSE
332 CURRENCY_CODE_WHERE := ' AND TRX.INVOICE_CURRENCY_CODE BETWEEN :p_currency_code_low AND :p_currency_code_high';
333 END IF;
334
335 --
336 -- INVOICE_TYPE where clause
337 --
338
339 IF var.p_invoice_type_low IS NULL AND var.p_invoice_type_high IS NULL THEN
340 INVOICE_TYPE_WHERE := NULL;
341 ELSIF var.p_invoice_type_low IS NULL THEN
342 INVOICE_TYPE_WHERE := ' AND arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'',trx.org_id) <= :p_invoice_type_high';
343 ELSIF var.p_invoice_type_high IS NULL THEN
344 INVOICE_TYPE_WHERE := ' AND arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'', trx.org_id) >= :p_invoice_type_low';
345 ELSE
346 INVOICE_TYPE_WHERE := ' AND arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'',trx.org_id) ' ||
347 ' BETWEEN :p_invoice_type_low AND :p_invoice_type_high';
348 END IF;
349
350 --
351 -- TRX date where clause--
352 --
353 IF var.p_trx_date_low IS NULL AND var.p_trx_date_high IS NULL THEN
354 TRX_DATE_WHERE := NULL;
355 ELSIF var.p_trx_date_low IS NULL THEN
356 TRX_DATE_WHERE := ' AND TRX.TRX_DATE <= :p_trx_date_high';
357 ELSIF var.p_trx_date_high IS NULL THEN
358 TRX_DATE_WHERE := ' AND TRX.TRX_DATE >= :p_trx_date_low';
359 ELSE
360 TRX_DATE_WHERE := ' AND TRX.TRX_DATE BETWEEN :p_trx_date_low AND :p_trx_date_high';
361 END IF;
362
363 --
364 -- DUE_DATE where clause
365 --
366
367 IF var.p_due_date_low IS NULL AND var.p_due_date_high IS NULL THEN
368 DUE_DATE_WHERE := NULL;
369 ELSIF var.p_due_date_low IS NULL THEN
370 DUE_DATE_WHERE := ' AND PAY.DUE_DATE <= :p_due_date_high';
371 ELSIF var.p_due_date_high IS NULL THEN
372 DUE_DATE_WHERE := ' AND PAY.DUE_DATE >= :p_due_date_low';
373 ELSE
374 DUE_DATE_WHERE := ' AND PAY.DUE_DATE BETWEEN :p_due_date_low AND :p_due_date_high';
375 END IF;
376
377 IF var.p_co_seg_low IS NULL AND var.p_co_seg_high IS NULL THEN
378 OPER := NULL;
379 ELSIF var.p_co_seg_low IS NULL THEN
380 OPER := '<=';
381 OP1 := var.p_co_seg_high;
382 OP2 := NULL;
383 ELSIF var.p_co_seg_high IS NULL THEN
384 OPER := '>=';
385 OP1 := var.p_co_seg_low;
386 OP2 := NULL;
387 ELSE
388 OPER := 'BETWEEN';
389 OP1 := var.p_co_seg_low;
390 OP2 := var.p_co_seg_high;
391 END IF;
392
393 IF OPER IS NULL THEN
394 CO_SEG_WHERE := NULL;
395 ELSE
396 CO_SEG_WHERE := ' AND '||
397 FA_RX_FLEX_PKG.FLEX_SQL(
398 p_application_id => 101,
399 p_id_flex_code => 'GL#',
400 p_id_flex_num => var.chart_of_accounts_id,
401 p_table_alias => 'glc',
402 p_mode => 'WHERE',
403 p_qualifier => 'GL_BALANCING',
404 p_function => OPER,
405 p_operand1 => OP1,
406 p_operand2 => OP2);
407 END IF;
408
409 --
410 -- ADJ_TYPE_WHERE clause
411 --
412
413 IF var.p_adj_type_low IS NULL AND var.p_adj_type_high IS NULL THEN
414 ADJ_TYPE_WHERE := NULL;
415 ELSIF var.p_adj_type_low IS NULL THEN
416 ADJ_TYPE_WHERE := ' AND ADJ.TYPE <= :p_adj_type_high';
417 ELSIF var.p_adj_type_high IS NULL THEN
418 ADJ_TYPE_WHERE := ' AND ADJ.TYPE >= :p_adj_type_low';
419 ELSE
420 ADJ_TYPE_WHERE := ' AND ADJ.TYPE BETWEEN :p_adj_type_low AND :p_adj_type_high';
421 END IF;
422
423 --
424 -- GL_DATE_WHERE clause
425 --
426
427 IF var.p_gl_date_low IS NULL AND var.p_gl_date_high IS NULL THEN
428 GL_DATE_WHERE := NULL;
429 ELSIF var.p_gl_date_low IS NULL THEN
430 GL_DATE_WHERE := ' AND ADJ.GL_DATE <= :p_gl_date_high';
431 ELSIF var.p_gl_date_high IS NULL THEN
432 GL_DATE_WHERE := ' AND ADJ.GL_DATE >= :p_gl_date_low';
433 ELSE
434 GL_DATE_WHERE := ' AND ADJ.GL_DATE BETWEEN :p_gl_date_low AND :p_gl_date_high';
435 END IF;
436
437 --
438 -- Doc Name Where
439 --
440
441 IF var.p_doc_seq_name is not null then
442 SEQ_NAME_WHERE := ' AND adj.doc_sequence_id = :p_doc_seq_name ';
443 ELSE
444 SEQ_NAME_WHERE := NULL;
445 END IF;
446
447 --
448 -- Doc Number Where
449 --
450
451 IF var.p_doc_seq_low is not NULL and var.p_doc_seq_high is not null then
452 SEQ_NUMBER_WHERE := ' AND adj.doc_sequence_value BETWEEN :p_doc_seq_low AND :p_doc_seq_high ';
453 ELSIF var.p_doc_seq_low is not null then
454 SEQ_NUMBER_WHERE := ' AND adj.doc_sequence_value >= :p_doc_seq_low ';
455 ELSIF var.p_doc_seq_high is not null then
456 SEQ_NUMBER_WHERE := ' AND adj.doc_sequence_value <= :p_doc_seq_high ';
457 ELSE
458 SEQ_NUMBER_WHERE := NULL;
459 END IF;
460
461 -- Bug 2099632
462 -- SHOW_BILL_WHERE
463
464 -- Bug 2209444 Changed fnd_profile to ar_setup procedure
465 ar_setup.get( name => 'AR_SHOW_BILLING_NUMBER',
466 val => BILL_FLAG );
467 IF (BILL_FLAG = 'Y') THEN
468 SHOW_BILL_WHERE := 'AND pay.cons_inv_id = ci.cons_inv_id(+)';
469 SHOW_BILL_FROM := ', ar_cons_inv_all ci ';
470 L_CI_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CI',NULL);
471 ELSE
472 SHOW_BILL_WHERE := NULL;
473 SHOW_BILL_FROM := NULL;
474 L_CI_ORG_WHERE := NULL;
475 END IF;
476
477 --
478 -- Define DECODE statements
479 --
480 /*bug5968198*/
481 SORTBY_DECODE := 'decode(upper(:p_order_by),''CUSTOMER'', decode(UPPER(party.party_type), ''ORGANIZATION'', org.organization_name,
482 ''PERSON'', per.person_name, party.party_name),''INVOICE NUMBER'', trx.trx_number,trx.trx_number)';
483
484 D_OR_I_DECODE := 'decode(adj.adjustment_type,''C'',decode( arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''TYPE'',trx.org_id), ''GUAR'', ''I'', ''D''),'''')';
485
486 POSTABLE_DECODE := 'decode(adj.postable, ''Y'', :c_Yes, :c_No)';
487
488 ADJ_CLASS_DECODE := 'decode(adj.adjustment_type, ''C'', look.meaning, ' ||
489 'decode(arpt_sql_func_util.get_rec_trx_type(adj.receivables_trx_id), ''FINCHRG'',''Finance'',''Adjustment''))';
490 --
491 -- Assign SELECT list
492 --
493
494 fa_rx_util_pkg.debug('ARTX_ASSIGN_SELECT_LIST');
495
496 -->>SELECT_START<<--
497
498 fa_rx_util_pkg.assign_column('10',NULL ,'ORGANIZATION_NAME' ,'arrx_adj.var.organization_name' ,'VARCHAR2',50);
499 fa_rx_util_pkg.assign_column('20',NULL ,'FUNCTIONAL_CURRENCY_CODE' ,'arrx_adj.var.functional_currency_code' ,'VARCHAR2',15);
500 fa_rx_util_pkg.assign_column('30',POSTABLE_DECODE ,'POSTABLE' ,'arrx_adj.var.postable' ,'VARCHAR2',15);
501 fa_rx_util_pkg.assign_column('40','trx.invoice_currency_code' ,'ADJ_CURRENCY_CODE' ,'arrx_adj.var.adj_currency_code' ,'VARCHAR2',15);
502 fa_rx_util_pkg.assign_column('50','1' ,'CONS' ,'arrx_adj.var.cons' ,'VARCHAR2',15);
503 /*fa_rx_util_pkg.assign_column('60',SORTBY_DECODE ,'SORTBY' ,'arrx_adj.var.sortby' ,'VARCHAR2',30);*/
504 fa_rx_util_pkg.assign_column('60',' arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'',trx.org_id)' ,'ADJ_NAME' ,'arrx_adj.var.adj_name' ,'VARCHAR2',30);
505 fa_rx_util_pkg.assign_column('70',D_OR_I_DECODE ,'D_OR_I' ,'arrx_adj.var.d_or_i' ,'VARCHAR2',6);
506 IF (BILL_FLAG = 'Y') THEN
507 fa_rx_util_pkg.assign_column('80','decode(ci.cons_billing_number, null, trx.trx_number, SUBSTRB(trx.trx_number||''/''||rtrim(ci.cons_billing_number),1,36))' ,'TRX_NUMBER' ,'arrx_adj.var.trx_number' ,'VARCHAR2',36);--bug4612433
508 ELSE
509 fa_rx_util_pkg.assign_column('80','trx.trx_number' ,'TRX_NUMBER' ,'arrx_adj.var.trx_number' ,'VARCHAR2',36);--bug4612433
510 END IF;
511 fa_rx_util_pkg.assign_column('90','pay.due_date' ,'DUE_DATE' ,'arrx_adj.var.due_date' ,'DATE');
512 fa_rx_util_pkg.assign_column('100','adj.gl_date' ,'GL_DATE' ,'arrx_adj.var.gl_date' ,'DATE');
513 -- Bug 1371540 Aug 2000: changed reference from Adjustment_id to Adjustment_number: jskhan
514 fa_rx_util_pkg.assign_column('110','adj.adjustment_number' ,'ADJ_NUMBER' ,'arrx_adj.var.adj_number' ,'VARCHAR2',20);
515 fa_rx_util_pkg.assign_column('120',ADJ_CLASS_DECODE ,'ADJ_CLASS' ,'arrx_adj.var.adj_class' ,'VARCHAR2',30);
516 fa_rx_util_pkg.assign_column('130','adj.type' ,'ADJ_TYPE_CODE' ,'arrx_adj.var.adj_type_code' ,'VARCHAR2',30);
517 fa_rx_util_pkg.assign_column('140','ladjtype.meaning' ,'ADJ_TYPE_MEANING' ,'arrx_adj.var.adj_type_meaning' ,'VARCHAR2',30);
518 /*bug5968198 changed to retrieve customer name based on party_type.*/
519 fa_rx_util_pkg.assign_column('150','substrb(decode(UPPER(party.party_type), ''ORGANIZATION'', org.organization_name, ''PERSON'',
520 per.person_name, party.party_name) ,1,50)' ,'CUSTOMER_NAME' ,'arrx_adj.var.customer_name' ,'VARCHAR2',50);
521 fa_rx_util_pkg.assign_column('160','cust.account_number' ,'CUSTOMER_NUMBER' ,'arrx_adj.var.customer_number' ,'VARCHAR2',30);
522 fa_rx_util_pkg.assign_column('170','cust.cust_account_id' ,'CUSTOMER_ID' ,'arrx_adj.var.customer_id' ,'NUMBER');
523 fa_rx_util_pkg.assign_column('180','trx.trx_date' ,'TRX_DATE' ,'arrx_adj.var.trx_date' ,'DATE');
524
525 if accounting_method = 'ACCRUAL' then
526
527 DIST_ENTERED := 'arrx_adj.dist_details(adj.adjustment_id, ' || var.chart_of_accounts_id ||
528 ',' || var.p_reporting_entity_id || ', ''ENTERED'')';
529 DIST_ACCTD := 'arrx_adj.dist_details(adj.adjustment_id, ' || var.chart_of_accounts_id ||
530 ',' || var.p_reporting_entity_id || ', ''ACCTD'')';
531 DIST_CCID_STR := 'arrx_adj.dist_ccid(adj.adjustment_id, ' || var.chart_of_accounts_id ||
532 ',' || var.p_reporting_entity_id || ')';
533 fa_rx_util_pkg.assign_column('190',
534 DIST_ENTERED,
535 'ADJ_AMOUNT',
536 'arrx_adj.var.adj_amount',
537 'NUMBER');
538 fa_rx_util_pkg.assign_column('200',
539 DIST_ACCTD,
540 'ACCTD_ADJ_AMOUNT',
541 'arrx_adj.var.acctd_adj_amount',
542 'NUMBER');
543 fa_rx_util_pkg.assign_column('210',
544 DIST_CCID_STR,
545 'ACCOUNT_CODE_COMBINATION_ID',
546 'arrx_adj.var.account_code_combination_id',
547 'VARCHAR2',240);
548 else
549 fa_rx_util_pkg.assign_column('190',
550 'round(adj.amount,2)',
551 'ADJ_AMOUNT',
552 'arrx_adj.var.adj_amount',
553 'NUMBER');
554 fa_rx_util_pkg.assign_column('200',
555 'adj.acctd_amount',
556 'ACCTD_ADJ_AMOUNT',
557 'arrx_adj.var.acctd_adj_amount',
558 'NUMBER');
559 fa_rx_util_pkg.assign_column('210',
560 'glc.code_combination_id',
561 'ACCOUNT_CODE_COMBINATION_ID',
562 'arrx_adj.var.account_code_combination_id',
563 'VARCHAR2',240);
564 end if;
565
566
567 fa_rx_util_pkg.assign_column('230',null ,'DEBIT_ACCOUNT_DESC' ,'arrx_adj.var.debit_account_desc' ,'VARCHAR2',240);
568 fa_rx_util_pkg.assign_column('240',null ,'DEBIT_BALANCING' ,'arrx_adj.var.debit_balancing' ,'VARCHAR2',240);
569 fa_rx_util_pkg.assign_column('250',null ,'DEBIT_BALANCING_DESC' ,'arrx_adj.var.debit_balancing_desc' ,'VARCHAR2',240);
570 fa_rx_util_pkg.assign_column('260',null ,'DEBIT_NATACCT' ,'arrx_adj.var.debit_natacct' ,'VARCHAR2',240);
571 fa_rx_util_pkg.assign_column('270',null ,'DEBIT_NATACCT_DESC' ,'arrx_adj.var.debit_natacct_desc' ,'VARCHAR2',240);
572 fa_rx_util_pkg.assign_column('280','nvl(adj.doc_sequence_value,'''')' ,'DOC_SEQUENCE_VALUE' ,'arrx_adj.var.doc_seq_value' ,'NUMBER');
573 fa_rx_util_pkg.assign_column('290',null ,'DOC_SEQUENCE_NAME' ,'arrx_adj.var.doc_seq_name' ,'VARCHAR2',30);
574
575 --
576 -- Assign FROM clause
577 --
578
579 fa_rx_util_pkg.debug('Assign FROM Clause using ALL tables');
580 -- Bug 1719611 Tim Dexter - added ar_distributions
581 fa_rx_util_pkg.from_clause := 'hz_cust_accounts_all cust,
582 hz_parties party,
583 ar_lookups ladjtype,
584 ar_payment_schedules_all pay,
585 ra_customer_trx_all trx,
586 ar_adjustments_all adj,
587 ar_lookups look,
588 hz_organization_profiles org, /*bug5968198*/
589 hz_person_profiles per' /*bug5968198*/
590 || SHOW_BILL_FROM;
591
592 /*
593 if accounting_method <> 'ACCRUAL' then
594 fa_rx_util_pkg.from_clause := fa_rx_util_pkg.from_clause ||
595 ',gl_code_combinations glc ';
596 end if;
597 */
598 /* Start FP Bug 5724794 - Bug 4619624 introduce join with GL_code_combination for segment based search */
599 if accounting_method = 'CASH' THEN
600 fa_rx_util_pkg.from_clause := fa_rx_util_pkg.from_clause ||
601 ',gl_code_combinations glc ';
602 elsif accounting_method = 'ACCRUAL' AND CO_SEG_WHERE IS NOT NULL THEN
603 fa_rx_util_pkg.from_clause := fa_rx_util_pkg.from_clause ||
604 ',gl_code_combinations glc , ar_distributions dist_all';
605
606 end if;
607 /* End FP Bug 5724794 SPDIXIT */
608
609 --
610 -- Assign WHERE clause
611 --
612 -- Bug 1385105 added 'and adj.receivables_trx_id <> -15' to exclude these adjustments
613
614 fa_rx_util_pkg.debug('AR_ASSIGN_WHERE_CLAUSE');
615 fa_rx_util_pkg.where_clause := 'trx.complete_flag = ''Y''
616 and cust.cust_account_id = trx.bill_to_customer_id
617 and cust.party_id = party.party_id
618 and trx.set_of_books_id = :set_of_books_id
619 and trx.customer_trx_id = pay.customer_trx_id
620 and pay.payment_schedule_id = adj.payment_schedule_id
621 and nvl(adj.status, ''A'') = ''A''
622 and arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''TYPE'',trx.org_id)
623 in (''INV'',''DEP'',''GUAR'',''CM'',''DM'',''CB'')
624 and look.lookup_type = ''INV/CM''
625 and look.lookup_code = arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''TYPE'',trx.org_id)
626 and adj.adjustment_id > 0
627 and adj.receivables_trx_id is not null
628 and adj.receivables_trx_id <> -15
629 and adj.type = ladjtype.lookup_code
630 and ladjtype.lookup_type = ''ADJUSTMENT_TYPE''
631 and party.party_id = org.party_id(+) /*bug5968198*/
632 and party.party_id = per.party_id(+) /*bug5968198*/
633 and (trx.trx_date between NVL(trunc(org.effective_start_date), trx.trx_date)
634 and NVL(trunc(org.effective_end_date), trx.trx_date)
635 OR (trx.trx_date < (select min(trunc(org1.effective_start_date)) from
636 hz_organization_profiles org1 where org1.party_id = party.party_id)
637 AND (trunc(trx.creation_date) between NVL(trunc(org.effective_start_date),
638 trunc(trx.creation_date)) and NVL(trunc(org.effective_end_date), trunc(trx.creation_date))
639 OR (trunc(trx.creation_date) < (select min(trunc(org1.effective_start_date))
640 from hz_organization_profiles org1 where org1.party_id = party.party_id)
641 AND trunc(org.effective_end_date) is NULL))))/*bug5968198*/ /*bug6674534*//*Bug7206486*/
642 and (trx.trx_date between NVL(trunc(per.effective_start_date), trx.trx_date)
643 and NVL(trunc(per.effective_end_date), trx.trx_date)
644 OR (trx.trx_date < (select min(trunc(per1.effective_start_date)) from
645 hz_person_profiles per1 where per1.party_id = party.party_id)
646 AND (trunc(trx.creation_date) between NVL(trunc(per.effective_start_date),
647 trunc(trx.creation_date)) and NVL(trunc(per.effective_end_date), trunc(trx.creation_date))
648 OR (trunc(trx.creation_date) < (select min(trunc(per1.effective_start_date))
649 from hz_person_profiles per1 where per1.party_id = party.party_id)
650 AND trunc(per.effective_end_date) is NULL)))) ' || /*bug5968198*/ /*bug6674534*/
651 CURRENCY_CODE_WHERE || ' ' ||
652 INVOICE_TYPE_WHERE || ' ' ||
653 DUE_DATE_WHERE || ' ' ||
654 TRX_DATE_WHERE || ' ' ||
655 ADJ_TYPE_WHERE || ' ' ||
656 GL_DATE_WHERE || ' ' ||
657 ADJ_ACCT_WHERE || ' ' ||
658 SEQ_NAME_WHERE || ' ' ||
659 SEQ_NUMBER_WHERE || ' ' ||
660 SHOW_BILL_WHERE || ' ' ||
661 L_CUST_ORG_WHERE || ' ' ||
662 L_PAY_ORG_WHERE || ' ' ||
663 L_ADJ_ORG_WHERE || ' ' ||
664 L_TRX_ORG_WHERE;
665
666 /* Start FP Bug 5724794 - Changed for bug 4619624 for joining with gl_code_combinations */
667 if accounting_method = 'CASH' THEN
668 fa_rx_util_pkg.where_clause := fa_rx_util_pkg.where_clause ||
669 ' and adj.code_combination_id = glc.code_combination_id
670 and glc.chart_of_accounts_id = :p_coa_id ' ||
671 CO_SEG_WHERE ;
672 elsif accounting_method = 'ACCRUAL' AND CO_SEG_WHERE IS NOT NULL THEN
673 fa_rx_util_pkg.where_clause := fa_rx_util_pkg.where_clause ||
674 'and glc.code_combination_id = dist_all.code_combination_id
675 and glc.chart_of_accounts_id = :p_coa_id
676 and dist_all.source_id = adj.adjustment_id
677 and dist_all.source_table = ''ADJ''
678 and dist_all.source_type in ( ''REC'') ' ||
679 ' ' || CO_SEG_WHERE ;
680
681 end if;
682
683 /*
684 if accounting_method <> 'ACCRUAL' then
685 fa_rx_util_pkg.where_clause := fa_rx_util_pkg.where_clause ||
686 ' and adj.code_combination_id = glc.code_combination_id
687 and glc.chart_of_accounts_id = :p_coa_id ' ||
688 CO_SEG_WHERE ;
689 end if;
690 */ /* End FP Bug 5724794 SPDIXIT*/
691
692 -- Assign ORDER BY clause
693 --
694
695 fa_rx_util_pkg.debug('AR_ASSIGN_ORDER_BY_CLAUSE');
696
697 fa_rx_util_pkg.order_by_clause := ' arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''POST'',trx.org_id) ,
698 arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,''NAME'',trx.org_id),
699 trx.trx_number,
700 pay.due_date,
701 adj.adjustment_number';
702 --Bug 1371540 jskhan changing adjustment_id for adjustment_number
703 -- adj.adjustment_id';
704
705
706 fa_rx_util_pkg.log('from clause ' || fa_rx_util_pkg.from_clause );
707 fa_rx_util_pkg.log('Where clause ' || fa_rx_util_pkg.where_clause );
708
709 fa_rx_util_pkg.debug('arrx_adj.adj_before_report()-');
710
711 end aradj_before_report;
712
713
714
715 --
716 -- Bind trigger for main Adjustment Register Report
717 --
718
719 procedure aradj_bind (c in integer)
720 is
721
722 l_Yes VARCHAR2(80);
723 l_No VARCHAR2(80);
724
725 begin
726 fa_rx_util_pkg.debug('AR_GET_BIND');
727 --
728 -- Binding vars that appear in SELECT statement depending on input params
729 --
730
731 -- Bug 4214787 : only bind p_reporting_entity_id when reporting level is Operating Unit
732 IF var.p_reporting_level = 3000 then
733 IF var.p_reporting_entity_id IS NOT NULL THEN
734 dbms_sql.bind_variable(c, 'p_reporting_entity_id', var.p_reporting_entity_id);
735 END IF;
736 END IF;
737
738 IF var.p_currency_code_low IS NOT NULL THEN
739 dbms_sql.bind_variable(c, 'p_currency_code_low', var.p_currency_code_low);
740 END IF;
741
742 IF var.p_currency_code_high IS NOT NULL THEN
743 dbms_sql.bind_variable(c, 'p_currency_code_high', var.p_currency_code_high);
744 END IF;
745
746 IF var.p_invoice_type_low IS NOT NULL THEN
747 dbms_sql.bind_variable(c, 'p_invoice_type_low', var.p_invoice_type_low);
748 END IF;
749
750 IF var.p_invoice_type_high IS NOT NULL THEN
751 dbms_sql.bind_variable(c, 'p_invoice_type_high', var.p_invoice_type_high);
752 END IF;
753
754
755 IF var.p_trx_date_low IS NOT NULL THEN
756 dbms_sql.bind_variable(c, 'p_trx_date_low', var.p_trx_date_low);
757 END IF;
758
759 IF var.p_trx_date_high IS NOT NULL THEN
760 dbms_sql.bind_variable(c, 'p_trx_date_high', var.p_trx_date_high);
761 END IF;
762
763
764 IF var.p_due_date_low IS NOT NULL THEN
765 dbms_sql.bind_variable(c, 'p_due_date_low', var.p_due_date_low);
766 END IF;
767
768 IF var.p_due_date_high IS NOT NULL THEN
769 dbms_sql.bind_variable(c, 'p_due_date_high', var.p_due_date_high);
770 END IF;
771
772
773
774 IF var.p_adj_type_low IS NOT NULL THEN
775 dbms_sql.bind_variable(c, 'p_adj_type_low', var.p_adj_type_low);
776 END IF;
777
778 IF var.p_adj_type_high IS NOT NULL THEN
779 dbms_sql.bind_variable(c, 'p_adj_type_high', var.p_adj_type_high);
780 END IF;
781
782 IF var.p_gl_date_low IS NOT NULL THEN
783 dbms_sql.bind_variable(c, 'p_gl_date_low', var.p_gl_date_low);
784 END IF;
785
786 IF var.p_gl_date_high IS NOT NULL THEN
787 dbms_sql.bind_variable(c, 'p_gl_date_high', var.p_gl_date_high);
788 END IF;
789
790 IF var.p_doc_seq_name IS NOT NULL THEN
791 dbms_sql.bind_variable(c, 'p_doc_seq_name',var.p_doc_seq_name);
792 END IF;
793
794 IF var.p_doc_seq_low IS NOT NULL THEN
795 dbms_sql.bind_variable(c, 'p_doc_seq_low',var.p_doc_seq_low);
796 END IF;
797
798 IF var.p_doc_seq_high IS NOT NULL THEN
799 dbms_sql.bind_variable(c, 'p_doc_seq_high',var.p_doc_seq_high);
800 END IF;
801
802 select meaning into l_Yes from AR_LOOKUPS
803 where lookup_type = 'YES/NO' and lookup_code = 'Y';
804
805 select meaning into l_No from AR_LOOKUPS
806 where lookup_type = 'YES/NO' and lookup_code = 'N';
807
808 dbms_sql.bind_variable(c, 'c_Yes', l_Yes);
809 dbms_sql.bind_variable(c, 'c_No', l_No);
810 /* Start FP Bug 5724794 */
811 if accounting_method = 'CASH' OR CO_SEG_WHERE IS NOT NULL then
812 -- if accounting_method <> 'ACCRUAL' then /* End FP Bug 5724794 SPDIXIT */
813 dbms_sql.bind_variable(c,'p_coa_id',var.chart_of_accounts_id);
814 end if;
815
816 dbms_sql.bind_variable(c,'set_of_books_id',var.books_id);
817
818 end aradj_bind;
819
820 --
821 -- After Fetch trigger
822 --
823
824 procedure aradj_after_fetch
825 is
826 begin
827 /* bug4230953 removed because var.currency_code and var.org_name are null
828 var.functional_currency_code := var.currency_code;
829 var.organization_name := var.org_name;
830 */
831
832
833 fa_rx_util_pkg.debug('var.account_code_combination_id = ' || var.account_code_combination_id);
834 fa_rx_util_pkg.debug('var.adj_number = ' || var.adj_number);
835 --
836 -- Assign acount data
837 --
838
839 /*
840 var.debit_account := fa_rx_flex_pkg.get_value(
841 p_application_id => 101,
842 p_id_flex_code => 'GL#',
843 p_id_flex_num => var.chart_of_accounts_id,
844 p_qualifier => 'ALL',
845 p_ccid => var.account_code_combination_id);
846
847 var.debit_account_desc := substrb(fa_rx_flex_pkg.get_description(
848 p_application_id => 101,
849 p_id_flex_code => 'GL#',
850 p_id_flex_num => var.chart_of_accounts_id,
851 p_qualifier => 'ALL',
852 p_data => var.debit_account),1,240);
853 */
854
855 var.debit_balancing := fa_rx_flex_pkg.get_value(
856 p_application_id => 101,
857 p_id_flex_code => 'GL#',
858 p_id_flex_num => var.chart_of_accounts_id,
859 p_qualifier => 'GL_BALANCING',
860 p_ccid => var.account_code_combination_id);
861
862 var.debit_balancing_desc:= substrb(fa_rx_flex_pkg.get_description(
863 p_application_id => 101,
864 p_id_flex_code => 'GL#',
865 p_id_flex_num => var.chart_of_accounts_id,
866 p_qualifier => 'GL_BALANCING',
867 p_data => var.debit_balancing),1,240);
868 /*
869 var.debit_natacct := fa_rx_flex_pkg.get_value(
870 p_application_id => 101,
871 p_id_flex_code => 'GL#',
872 p_id_flex_num => var.chart_of_accounts_id,
873 p_qualifier => 'GL_ACCOUNT',
874 p_ccid => var.account_code_combination_id);
875
876 var.debit_natacct_desc := substrb(fa_rx_flex_pkg.get_description(
877 p_application_id => 101,
878 p_id_flex_code => 'GL#',
879 p_id_flex_num => var.chart_of_accounts_id,
880 p_qualifier => 'GL_ACCOUNT',
881 p_data => var.debit_natacct),1,240);
882 */
883 --
884
885 end aradj_after_fetch;
886
887 end ARRX_ADJ;