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