[Home] [Help]
PACKAGE BODY: APPS.ARRX_TX
Source
1 PACKAGE BODY ARRX_TX as
2 /* $Header: ARRXTXB.pls 120.15 2007/12/19 05:54:25 bsuri ship $ */
3
4 --
5 -- Main AR TRANSACTION RX Report function
6 --
7 -- bug3940958 added new parameters
8 procedure artx_rep (
9 completed_flag in varchar2,
10 posted_flag in varchar2,
11 start_gl_date in date,
12 end_gl_date in date,
13 start_transaction_date in date,
14 end_transaction_date in date,
15 start_transaction_type in varchar2,
16 end_transaction_type in varchar2,
17 start_transaction_class in varchar2,
18 end_transaction_class in varchar2,
19 start_balancing_segment in varchar2,
20 end_balancing_segment in varchar2,
21 start_bill_to_customer_name in varchar2,
22 end_bill_to_customer_name in varchar2,
23 start_currency in varchar2,
24 end_currency in varchar2,
25 payment_method in varchar2,
26 doc_sequence_name in varchar2,
27 doc_sequence_number_from in number,
28 doc_sequence_number_to in number,
29 start_bill_to_customer_number in varchar2,
30 end_bill_to_customer_number in varchar2,
31 reporting_level IN VARCHAR2,
32 reporting_entity_id IN NUMBER,
33 start_account in VARCHAR2,
34 end_account in VARCHAR2,
35 batch_source_name in VARCHAR2,
36 transaction_class in varchar2,
37 request_id in number,
38 retcode out NOCOPY number,
39 errbuf out NOCOPY varchar2)
40 is
41 l_profile_rsob_id NUMBER := NULL;
42 l_client_info_rsob_id NUMBER := NULL;
43 l_client_info_org_id NUMBER := NULL;
44 begin
45 fa_rx_util_pkg.debug('arrx_tx.artx_rep()+');
46
47 --
48 -- Assign parameters to global variable
49 -- These values will be used within the before_report trigger
50 -- bug4214582 added nvl for completed_flag
51 var.completed_flag := nvl(completed_flag,'Y');
52 var.posted_flag := posted_flag;
53 var.start_gl_date := start_gl_date;
54 var.end_gl_date := end_gl_date;
55 var.start_transaction_date := Trunc(start_transaction_date);
56 var.end_transaction_date := Trunc(end_transaction_date)+1-1/24/60/60;
57 var.start_transaction_type := start_transaction_type;
58 var.end_transaction_type := end_transaction_type;
59 var.start_transaction_class := start_transaction_class;
60 var.end_transaction_class := end_transaction_class;
61 var.start_balancing_segment := start_balancing_segment;
62 var.end_balancing_segment := end_balancing_segment;
63 var.start_bill_to_customer_name := start_bill_to_customer_name;
64 var.end_bill_to_customer_name := end_bill_to_customer_name;
65 var.start_currency := start_currency;
66 var.end_currency := end_currency;
67 var.payment_method := payment_method;
68 var.doc_sequence_name := doc_sequence_name;
69 var.doc_sequence_number_from := doc_sequence_number_from;
70 var.doc_sequence_number_to := doc_sequence_number_to;
71 var.start_bill_to_customer_number := start_bill_to_customer_number;
72 var.end_bill_to_customer_number := end_bill_to_customer_number;
73 var.request_id := request_id;
74
75 SELECT TO_NUMBER(NVL( REPLACE(SUBSTRB(USERENV('CLIENT_INFO'),1,10),' '),-99))
76 INTO l_client_info_org_id
77 FROM dual;
78
79 var.reporting_level := nvl(reporting_level,'3000');
80 var.reporting_entity_id := nvl(reporting_entity_id,l_client_info_org_id);
81 var.start_account := start_account;
82 var.end_account := end_account;
83 var.batch_source_name := batch_source_name;
84 var.transaction_class := transaction_class;
85 /*
86 * Bug 2498344 - MRC Reporting project
87 * Set the appropriate sob type into the global variable var.ca_sob_type.
88 *
89 * value Case
90 * ===== ========
91 * P (When run for primary book) OR (When run for reporting
92 * book and from APPS_MRC schema)
93 * R When run for reporting book from APPS schema
94 *
95 */
96
97 /*
98 * Bug fix 2801076
99 * Using replace to change spaces to null when RSOB not set
100 */
101 SELECT TO_NUMBER(NVL( REPLACE(SUBSTRB(USERENV('CLIENT_INFO'),45,10),' '),-99))
102 INTO l_client_info_rsob_id
103 FROM dual;
104
105 fnd_profile.get('MRC_REPORTING_SOB_ID', l_profile_rsob_id);
106 IF (l_client_info_rsob_id = NVL(l_profile_rsob_id,-1)) OR
107 (l_client_info_rsob_id = -99)
108 THEN
109 var.ca_sob_type := 'P';
110 ELSE
111 var.ca_sob_id := l_client_info_rsob_id;
112 var.ca_sob_type := 'R';
113 END IF;
114
115
116 fa_rx_util_pkg.debug('completed_flag = '|| var.completed_flag);
117 fa_rx_util_pkg.debug('posted_flag = '|| var.posted_flag);
118 fa_rx_util_pkg.debug('start_gl_date = '|| var.start_gl_date);
119 fa_rx_util_pkg.debug('end_gl_date = '|| var.end_gl_date);
120 fa_rx_util_pkg.debug('start_transaction_date = '|| var.start_transaction_date);
121 fa_rx_util_pkg.debug('end_transaction_date = '|| var.end_transaction_date);
122 fa_rx_util_pkg.debug('start_transaction_type = '|| var.start_transaction_type);
123 fa_rx_util_pkg.debug('end_transaction_type = '|| var.end_transaction_type);
124 fa_rx_util_pkg.debug('start_transaction_class = '|| var.start_transaction_class);
125 fa_rx_util_pkg.debug('end_transaction_class = '|| var.end_transaction_class);
126 fa_rx_util_pkg.debug('start_balancing_segment = '|| var.start_balancing_segment);
127 fa_rx_util_pkg.debug('end_balancing_segment = '|| var.end_balancing_segment);
128 fa_rx_util_pkg.debug('start_bill_to_customer_name = '|| var.start_bill_to_customer_name);
129 fa_rx_util_pkg.debug('end_bill_to_customer_name = '|| var.end_bill_to_customer_name);
130 fa_rx_util_pkg.debug('start_currency = '|| var.start_currency);
131 fa_rx_util_pkg.debug('end_currency = '|| var.end_currency);
132 fa_rx_util_pkg.debug('payment_method = '|| var.payment_method);
133 fa_rx_util_pkg.debug('doc_sequence_name = '|| var.doc_sequence_name);
134 fa_rx_util_pkg.debug('doc_sequence_number_from = '|| var.doc_sequence_number_from);
135 fa_rx_util_pkg.debug('doc_sequence_number_to = '|| var.doc_sequence_number_to);
136 fa_rx_util_pkg.debug('start_bill_to_customer_number = '|| var.start_bill_to_customer_number);
137 fa_rx_util_pkg.debug('end_bill_to_customer_number = '|| var.end_bill_to_customer_number);
138 fa_rx_util_pkg.debug('request_id = '|| var.request_id);
139
140 -- bug3940958 added
141 fa_rx_util_pkg.debug('start_account = '|| var.start_account);
142 fa_rx_util_pkg.debug('end_account = '|| var.end_account);
143 fa_rx_util_pkg.debug('batch_source_name = '|| var.batch_source_name);
144 fa_rx_util_pkg.debug('transaction_class = '|| var.start_transaction_class);
145
146 -- Start Bug 5571594 - Added modification to code change of bug 5244313
147 -- changing in-parameter variable with local variable
148 -- Bug 5244313 Setting the Org context based on the Reporting context
149 if var.reporting_level= 1000 then
150
151 var.books_id := var.reporting_entity_id;
152 mo_global.init('AR');
153 mo_global.set_policy_context('M',null);
154
155 elsif var.reporting_level=3000 then
156
157 select set_of_books_id
158 into var.books_id
159 from ar_system_parameters_all
160 where org_id = var.reporting_entity_id;
161
162 mo_global.init('AR');
163 mo_global.set_policy_context('S',var.reporting_entity_id);
164 END IF;
165 --End bug 5571594 SPDIXIT
166 -- Initialize request
167 fa_rx_util_pkg.init_request('arrx_tx.artx_rep',request_id,'AR_TRANSACTIONS_REP_ITF');
168
169 --
170 -- Assign report triggers for this report.
171 -- This report has one section called AR TRANSACTION
172 -- NOTE:
173 -- before_report is assigned 'arrx_tx.before_report;'
174 -- bind is assigned 'arrx_tx.bind(:CURSOR_SELECT);'
175 -- Each trigger event is assigned with the full procedure name (including package name).
176 -- They end with a ';'.
177 -- The bind trigger requires one host variable ':CURSOR_SELECT'.
178 fa_rx_util_pkg.assign_report('AR TRANSACTIONS',
179 true,
180 'arrx_tx.before_report;',
181 'arrx_tx.bind(:CURSOR_SELECT);',
182 'arrx_tx.after_fetch;',
183 null);
184
185 --
186 -- Run the report. Make sure to pass as parameter the same
187 -- value passed to p_calling_proc in init_request().
188 fa_rx_util_pkg.run_report('arrx_tx.artx_rep', retcode, errbuf);
189
190 fa_rx_util_pkg.debug('arrx_tx.artx_rep()-');
191
192 exception
193 when others then
194 fa_rx_util_pkg.log(sqlcode);
195 fa_rx_util_pkg.log(sqlerrm);
196 fa_rx_util_pkg.debug(sqlcode);
197 fa_rx_util_pkg.debug(sqlerrm);
198 fa_rx_util_pkg.debug('arrx_tx.artx_rep(EXCEPTION)-');
199 end artx_rep;
200
201
202 --
203 -- This is the before report trigger for the main arrx_tx report.
204 procedure before_report
205 is
206 REC_ACCOUNT_SEL varchar2(2000); /*4653230*/
207 REC_BALANCE_SEL varchar2(2000); /*4653230*/
208 REC_NATURAL_SEL varchar2(2000); /*4653230*/
209 COMPLETE_FLAG_WHERE varchar2(500);
210 POSTED_FLAG_WHERE varchar2(500);
211 REC_GL_DATE_WHERE varchar2(500);
212 TRX_DATE_WHERE varchar2(500);
213 TRX_TYPE_WHERE varchar2(500);
214 TRX_CLASS_WHERE varchar2(500);
215 REC_BALANCING_WHERE varchar2(500);
216 BILL_TO_CUSTOMER_NAME_WHERE varchar2(500);
217 CURRENCY_CODE_WHERE varchar2(500);
218 PAYMENT_METHOD_WHERE varchar2(500);
219 SELECT_BILL_NUMBER varchar2(500);
220 DOC_SEQUENCE_NAME_WHERE varchar2(500);
221 DOC_SEQUENCE_NUMBER_WHERE varchar2(500);
222 BILL_TO_CUSTOMER_NUMBER_WHERE varchar2(500);
223 OPER varchar2(10);
224 OP1 varchar2(2000); /*4653230*/
225 OP2 varchar2(2000); /*4653230*/
226
227 -- bug3940958 added
228 L_RECDIST_ORG_WHERE varchar2(500);
229 L_CT_ORG_WHERE varchar2(500);
230 L_TRX_TYPE_ORG_WHERE varchar2(500);
231 L_BILL_TO_ORG_WHERE varchar2(500);
232 L_BS_ORG_WHERE varchar2(500);
233 BATCH_SOURCE_WHERE varchar2(500);
234 REC_ACCOUNT_WHERE varchar2(4500); /*4653230*/
235 begin
236 fa_rx_util_pkg.debug('arrx_tx.before_report()+');
237
238 --
239 -- Get Profile GL_SET_OF_BKS_ID
240 --
241 fa_rx_util_pkg.debug('GL_GET_PROFILE_BKS_ID');
242 /* bug2018415 replace fnd_profile call with arp_global.sysparam
243 fnd_profile.get(
244 name => 'GL_SET_OF_BKS_ID',
245 val => var.books_id);
246 */
247 /*
248 * Bug 2498344 - MRC Reporting project
249 * Set var.books_id either from sysparam or the sob_id passed
250 * depending on sob_type
251 */
252 /*Bug 5244313
253
254 IF var.ca_sob_type = 'P'
255 THEN
256 var.books_id := arp_global.sysparam.set_of_books_id;
257
258 -- Bug:3302771
259 var.tax_header_level_flag :=arp_global.sysparam.tax_header_level_flag;
260
261 ELSE
262 var.books_id := var.ca_sob_id;
263
264 -- bug:3256137
265 -- Get TAX_HEADER_LEVEL_FLAG
266 --
267 select TAX_HEADER_LEVEL_FLAG
268 into var.tax_header_level_flag
269 from AR_SYSTEM_PARAMETERS_MRC_V;
270 END IF;
271 */
272
273
274 --
275 -- Get CHART_OF_ACCOUNTS_ID
276 --
277 fa_rx_util_pkg.debug('GL_GET_CHART_OF_ACCOUNTS_ID');
278
279 -- bug:3256137
280 select CHART_OF_ACCOUNTS_ID
281 ,NAME
282 ,CURRENCY_CODE
283 into var.chart_of_accounts_id
284 ,var.organization_name
285 ,var.functional_currency_code
286 from GL_SETS_OF_BOOKS
287 where SET_OF_BOOKS_ID = var.books_id;
288
289
290
291 --
292 -- Figure out NOCOPY the where clause for the parameters
293 --
294 fa_rx_util_pkg.debug('AR_GET_PARAMETERS');
295
296 -- bug3940958 added for cross-org
297 if var.reporting_entity_id <> -99 then
298 XLA_MO_REPORTING_API.Initialize(var.reporting_level, var.reporting_entity_id, 'AUTO');
299
300 L_RECDIST_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('RECDIST',NULL);
301 L_CT_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('CT',NULL);
302 L_TRX_TYPE_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('TRX_TYPE',NULL);
303 L_BILL_TO_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('BILL_TO',NULL);
304 L_BS_ORG_WHERE := XLA_MO_REPORTING_API.Get_Predicate('BS',NULL);
305 end if;
306
307
308
309 IF var.completed_flag IS NULL THEN
310 COMPLETE_FLAG_WHERE := NULL;
311 ELSE
312 COMPLETE_FLAG_WHERE := ' AND CT.COMPLETE_FLAG = '''|| var.completed_flag ||'''';
313 END IF;
314
315
316 IF var.posted_flag IS NULL THEN
317 POSTED_FLAG_WHERE := NULL;
318 ELSIF var.posted_flag = 'Y' THEN
319 POSTED_FLAG_WHERE := ' AND RECDIST.POSTING_CONTROL_ID <> -3';
320 ELSE
321 POSTED_FLAG_WHERE := ' AND RECDIST.POSTING_CONTROL_ID = -3';
322 END IF;
323
324
325
326 /* Modifying for bug 1740514 */
327 IF var.start_gl_date IS NULL AND var.end_gl_date IS NULL THEN
328 REC_GL_DATE_WHERE := NULL;
329 ELSIF var.start_gl_date IS NULL THEN
330 REC_GL_DATE_WHERE := ' AND ((RECDIST.GL_DATE <= :end_gl_date) OR (CT.TRX_DATE <= :end_gl_date AND RECDIST.GL_DATE IS NULL)) ';
331 ELSIF var.end_gl_date IS NULL THEN
332 REC_GL_DATE_WHERE := ' AND ((RECDIST.GL_DATE >= :start_gl_date) OR (CT.TRX_DATE >= :start_gl_date AND RECDIST.GL_DATE IS NULL))';
333 ELSE
334 REC_GL_DATE_WHERE := ' AND ((RECDIST.GL_DATE BETWEEN :start_gl_date AND :end_gl_date) OR (CT.TRX_DATE BETWEEN :start_gl_date AND :end_gl_date AND RECDIST.GL_DATE IS NULL))';
335 END IF;
336
337
338
339 IF var.start_transaction_date IS NULL AND var.end_transaction_date IS NULL THEN
340 TRX_DATE_WHERE := NULL;
341 ELSIF var.start_transaction_date IS NULL THEN
342 TRX_DATE_WHERE := ' AND CT.TRX_DATE <= :end_transaction_date';
343 ELSIF var.end_transaction_date IS NULL THEN
344 TRX_DATE_WHERE := ' AND CT.TRX_DATE >= :start_transaction_date';
345 ELSE
346 TRX_DATE_WHERE := ' AND CT.TRX_DATE BETWEEN :start_transaction_date AND :end_transaction_date';
347 END IF;
348
349
350
351
352 IF var.start_transaction_type IS NULL AND var.end_transaction_type IS NULL THEN
353 TRX_TYPE_WHERE := NULL;
354 ELSIF var.start_transaction_type IS NULL THEN
355 TRX_TYPE_WHERE := ' AND TRX_TYPE.NAME <= :end_transaction_type ';
356 ELSIF var.end_transaction_type IS NULL THEN
357 TRX_TYPE_WHERE := ' AND TRX_TYPE.NAME >= :start_transaction_type ';
358 ELSE
359 TRX_TYPE_WHERE := ' AND TRX_TYPE.NAME BETWEEN :start_transaction_type AND :end_transaction_type ';
360 END IF;
361
362 IF var.start_transaction_class IS NULL AND var.end_transaction_class IS NULL THEN
363 TRX_CLASS_WHERE := NULL;
364 ELSIF var.start_transaction_class IS NULL THEN
365 TRX_CLASS_WHERE := ' AND TRX_TYPE.TYPE <= :end_transaction_class ';
366 ELSIF var.end_transaction_class IS NULL THEN
367 TRX_CLASS_WHERE := ' AND TRX_TYPE.TYPE >= :start_transaction_class ';
368 ELSE
369 TRX_CLASS_WHERE := ' AND TRX_TYPE.TYPE BETWEEN :start_transaction_class AND :end_transaction_class ';
370 END IF;
371
372 IF var.start_balancing_segment IS NULL AND var.end_balancing_segment IS NULL THEN
373 OPER := NULL;
374 ELSIF var.start_balancing_segment IS NULL THEN
375 OPER := '<=';
376 OP1 := var.end_balancing_segment;
377 OP2 := NULL;
378 ELSIF var.end_balancing_segment IS NULL THEN
382 ELSE
379 OPER := '>=';
380 OP1 := var.start_balancing_segment;
381 OP2 := NULL;
383 OPER := 'BETWEEN';
384 OP1 := var.start_balancing_segment;
385 OP2 := var.end_balancing_segment;
386 END IF;
387
388
389 IF OPER IS NULL THEN
390 REC_BALANCING_WHERE := NULL;
391 ELSE
392 REC_BALANCING_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 => 'CCRECDIST',
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 -- bug3940958 added for new parameters
406 IF var.start_account IS NULL AND var.end_account IS NULL THEN
407 OPER := NULL;
408 ELSIF var.start_account IS NULL THEN
409 OPER := '<=';
410 OP1 := var.end_account;
411 OP2 := NULL;
412 ELSIF var.end_account IS NULL THEN
413 OPER := '>=';
414 OP1 := var.start_account;
415 OP2 := NULL;
416 ELSE
417 OPER := 'BETWEEN';
418 OP1 := var.start_account;
419 OP2 := var.end_account;
420 END IF;
421 IF OPER IS NULL THEN
422 REC_ACCOUNT_WHERE := NULL;
423 ELSE
424 REC_ACCOUNT_WHERE := ' AND '||
425 FA_RX_FLEX_PKG.FLEX_SQL(
426 p_application_id => 101,
427 p_id_flex_code => 'GL#',
428 p_id_flex_num => var.chart_of_accounts_id,
429 p_table_alias => 'CCRECDIST',
430 p_mode => 'WHERE',
431 p_qualifier => 'ALL',
432 p_function => OPER,
433 p_operand1 => OP1,
434 p_operand2 => OP2);
435 END IF;
436
437
438 --begin for bug 1814839: used bind variable instead of converting to string
439 IF var.start_bill_to_customer_name IS NULL AND var.end_bill_to_customer_name IS NULL THEN
440 BILL_TO_CUSTOMER_NAME_WHERE := NULL;
441 ELSIF var.start_bill_to_customer_name IS NULL THEN
442 BILL_TO_CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME <= :end_bill_to_customer_name ';
443 ELSIF var.end_bill_to_customer_name IS NULL THEN
444 BILL_TO_CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME >= :start_bill_to_customer_name ';
445 ELSE
446 BILL_TO_CUSTOMER_NAME_WHERE := ' AND PARTY.PARTY_NAME BETWEEN :start_bill_to_customer_name and :end_bill_to_customer_name ';
447 END IF;
448 --end for bug 1814839
449
450 IF var.start_bill_to_customer_number IS NULL AND var.end_bill_to_customer_number IS NULL THEN
451 BILL_TO_CUSTOMER_NUMBER_WHERE := NULL;
452 ELSIF var.start_bill_to_customer_number IS NULL THEN
453 BILL_TO_CUSTOMER_NUMBER_WHERE := ' AND BILL_TO.ACCOUNT_NUMBER <= :end_bill_to_customer_number ';
454 ELSIF var.end_bill_to_customer_number IS NULL THEN
455 BILL_TO_CUSTOMER_NUMBER_WHERE := ' AND BILL_TO.ACCOUNT_NUMBER >= :start_bill_to_customer_number ';
456 ELSE
457 BILL_TO_CUSTOMER_NUMBER_WHERE := ' AND BILL_TO.ACCOUNT_NUMBER BETWEEN :start_bill_to_customer_number AND :end_bill_to_customer_number ';
458 END IF;
459
460 IF var.start_currency IS NULL AND var.end_currency IS NULL THEN
461 CURRENCY_CODE_WHERE := NULL;
462 ELSIF var.start_currency IS NULL THEN
463 CURRENCY_CODE_WHERE := ' AND CT.INVOICE_CURRENCY_CODE <= :end_currency ';
464 ELSIF var.end_currency IS NULL THEN
465 CURRENCY_CODE_WHERE := ' AND CT.INVOICE_CURRENCY_CODE >= :start_currency ';
466 ELSE
467 CURRENCY_CODE_WHERE := ' AND CT.INVOICE_CURRENCY_CODE BETWEEN :start_currency AND :end_currency ';
468 END IF;
469
470 IF var.payment_method IS NULL THEN
471 PAYMENT_METHOD_WHERE := NULL;
472 ELSE
473 PAYMENT_METHOD_WHERE := ' AND METHODS.NAME = :payment_method ';
474 END IF;
475
476 --
477 -- DOCUMENT WHERE Clauses
478 --
479 /* For bug 2252811 changed the where clause to retrieve based on doc_sequence_id
480 since var.doc_sequence_name has doc_sequence_id */
481
482 IF var.doc_sequence_name is not null THEN
483 DOC_SEQUENCE_NAME_WHERE := ' AND DOC_SEQ.doc_sequence_id= :doc_sequence_name ';
484 ELSE
485 DOC_SEQUENCE_NAME_WHERE := null;
486 END IF;
487
488 IF var.doc_sequence_number_from IS NOT NULL THEN
489 IF var.doc_sequence_number_to IS NOT NULL THEN
490 DOC_SEQUENCE_NUMBER_WHERE := ' AND CT.DOC_SEQUENCE_VALUE between :doc_sequence_number_from AND :doc_sequence_number_to ';
491 ELSE
492 DOC_SEQUENCE_NUMBER_WHERE := ' AND CT.DOC_SEQUENCE_VALUE >= :doc_sequence_number_from ';
493 END IF;
494 ELSE
495 IF var.doc_sequence_number_to IS NOT NULL THEN
496 DOC_SEQUENCE_NUMBER_WHERE := ' AND CT.DOC_SEQUENCE_VALUE <= :doc_sequence_number_to ';
497 ELSE
498 DOC_SEQUENCE_NUMBER_WHERE := NULL;
499 END IF;
500 END IF;
501
505 ELSE
502 -- bug3940958 modified
503 IF var.transaction_class IS NULL THEN
504 TRX_CLASS_WHERE := NULL;
506 TRX_CLASS_WHERE := ' AND TRX_TYPE.TYPE = :transaction_class ';
507 END IF;
508
509 -- bug3940958 added for new parameter
510 IF var.batch_source_name IS NULL THEN
511 BATCH_SOURCE_WHERE := NULL;
512 ELSE
513 BATCH_SOURCE_WHERE := ' AND (CT.ORG_ID, CT.BATCH_SOURCE_ID) ' ||
514 'in (select ' ||
515 'org_id, batch_source_id ' ||
516 'from ra_batch_sources_all BS ' ||
517 'where name = :batch_source_name ' ||
518 L_BS_ORG_WHERE || ' ) ';
519 END IF;
520
521
522 --
523 -- Get BILLING NUMBER Function
524 --
525 fa_rx_util_pkg.debug('AR_GET_BILLING_NUMBER');
526 /* fnd_profile.get(
527 name => 'AR_SHOW_BILLING_NUMBER',
528 val => var.bill_flag);
529 */
530
531 /*Commented for Bug 5244313
532 var.bill_flag := NVL(ar_setup.value('AR_SHOW_BILLING_NUMBER',null),'N');
533 var.bill_flag := 'N';
534
535 -- Null will be replaced with org_id, for x-org scenario
536
537 IF var.bill_flag = 'N' THEN
538 SELECT_BILL_NUMBER := null;
539 ELSE
540 */
541 SELECT_BILL_NUMBER := 'DECODE(SYSPARAM.SHOW_BILLING_NUMBER_FLAG,''Y'',ARRX_TX.GET_CONS_BILL_NUMBER(CT.CUSTOMER_TRX_ID),NULL) ';
542
543 -- END IF;
544
545
546
547 --
548 -- Flex SQL
549 --
550 REC_ACCOUNT_SEL :=
551 FA_RX_FLEX_PKG.FLEX_SQL(
552 p_application_id => 101,
553 p_id_flex_code => 'GL#',
554 p_id_flex_num => var.chart_of_accounts_id,
555 p_table_alias => 'CCRECDIST',
556 p_mode => 'SELECT',
557 p_qualifier => 'ALL');
558 REC_BALANCE_SEL :=
559 FA_RX_FLEX_PKG.FLEX_SQL(
560 p_application_id => 101,
561 p_id_flex_code => 'GL#',
562 p_id_flex_num => var.chart_of_accounts_id,
563 p_table_alias => 'CCRECDIST',
564 p_mode => 'SELECT',
565 p_qualifier => 'GL_BALANCING');
566 REC_NATURAL_SEL :=
567 FA_RX_FLEX_PKG.FLEX_SQL(
568 p_application_id => 101,
569 p_id_flex_code => 'GL#',
570 p_id_flex_num => var.chart_of_accounts_id,
571 p_table_alias => 'CCRECDIST',
572 p_mode => 'SELECT',
573 p_qualifier => 'GL_ACCOUNT');
574
575 --6506811
576 var.tax_header_level_flag := NVL(var.tax_header_level_flag,'N');
577 --
578 -- Assign SELECT list
579 --
580 fa_rx_util_pkg.debug('ARTX_ASSIGN_SELECT_LIST');
581
582 -- fa_rx_util_pkg.assign_column(#, select, insert, place, type, len);
583 -->>SELECT_START<<--
584 fa_rx_util_pkg.assign_column('10 ','CCRECDIST.CODE_COMBINATION_ID', null, 'arrx_tx.var.ccid', 'NUMBER');
585 fa_rx_util_pkg.assign_column('20 ',null, 'ORGANIZATION_NAME', 'arrx_tx.var.organization_name', 'VARCHAR2', 30);
586 fa_rx_util_pkg.assign_column('30 ',null, 'FUNCTIONAL_CURRENCY_CODE', 'arrx_tx.var.functional_currency_code', 'VARCHAR2', 15);
587 fa_rx_util_pkg.assign_column('40 ','CT.CUSTOMER_TRX_ID', 'CUSTOMER_TRX_ID', 'arrx_tx.var.customer_trx_id', 'NUMBER');
588 fa_rx_util_pkg.assign_column('50 ','CT.TRX_NUMBER', 'TRX_NUMBER', 'arrx_tx.var.trx_number', 'VARCHAR2', 20);
589 fa_rx_util_pkg.assign_column('60 ',SELECT_BILL_NUMBER, 'CONS_BILL_NUMBER', 'arrx_tx.var.cons_bill_number', 'VARCHAR2', 30);
590 fa_rx_util_pkg.assign_column('70 ','RECDIST.CUST_TRX_LINE_GL_DIST_ID', 'REC_CUST_TRX_LINE_GL_DIST_ID','arrx_tx.var.rec_cust_trx_line_gl_dist_id','NUMBER');
591 fa_rx_util_pkg.assign_column('80 ',REC_ACCOUNT_SEL, 'REC_ACCOUNT', 'arrx_tx.var.rec_account', 'VARCHAR2',240);
592 fa_rx_util_pkg.assign_column('90 ',null, 'REC_ACCOUNT_DESC', 'arrx_tx.var.rec_account_desc', 'VARCHAR2',240);
593 fa_rx_util_pkg.assign_column('100',REC_BALANCE_SEL, 'REC_BALANCE', 'arrx_tx.var.rec_balance', 'VARCHAR2',240);
594 fa_rx_util_pkg.assign_column('110',null, 'REC_BALANCE_DESC', 'arrx_tx.var.rec_balance_desc', 'VARCHAR2',240);
595 fa_rx_util_pkg.assign_column('120',REC_NATURAL_SEL, 'REC_NATACCT', 'arrx_tx.var.rec_natacct', 'VARCHAR2',240);
596 fa_rx_util_pkg.assign_column('130',null, 'REC_NATACCT_DESC', 'arrx_tx.var.rec_natacct_desc', 'VARCHAR2',240);
597 fa_rx_util_pkg.assign_column('140','DECODE(RECDIST.GL_DATE,NULL,:NO,:YES)', 'REC_POSTABLE_FLAG', 'arrx_tx.var.rec_postable_flag', 'VARCHAR2', 10);
598 fa_rx_util_pkg.assign_column('150','ARRX_TX.LAST_UPDATED_BY(CT.CUSTOMER_TRX_ID)', 'TRX_LAST_UPDATED_BY', 'arrx_tx.var.trx_last_updated_by', 'NUMBER');
602 fa_rx_util_pkg.assign_column('190','CT.EXCHANGE_RATE', 'EXCHANGE_RATE', 'arrx_tx.var.exchange_rate', 'NUMBER');
599 fa_rx_util_pkg.assign_column('160','ARRX_TX.LAST_UPDATE_DATE(CT.CUSTOMER_TRX_ID)','TRX_LAST_UPDATE_DATE', 'arrx_tx.var.trx_last_update_date', 'DATE');
600 fa_rx_util_pkg.assign_column('170','CT.TRX_DATE', 'TRX_DATE', 'arrx_tx.var.trx_date', 'DATE');
601 fa_rx_util_pkg.assign_column('180','CT.INVOICE_CURRENCY_CODE', 'TRX_CURRENCY', 'arrx_tx.var.trx_currency', 'VARCHAR2', 15);
603 fa_rx_util_pkg.assign_column('200','CT.EXCHANGE_DATE', 'EXCHANGE_DATE', 'arrx_tx.var.exchange_date', 'DATE');
604 fa_rx_util_pkg.assign_column('210','CT.EXCHANGE_RATE_TYPE', 'EXCHANGE_TYPE', 'arrx_tx.var.exchange_type', 'VARCHAR2', 30);
605 fa_rx_util_pkg.assign_column('220','RECDIST.GL_DATE', 'RECEIVABLES_GL_DATE', 'arrx_tx.var.receivables_gl_date', 'DATE');
606 -- bug4274502 added nvl because AutoInvoice does not set term_due_date
607 fa_rx_util_pkg.assign_column('230','NVL(CT.TERM_DUE_DATE,ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE(CT.CUSTOMER_TRX_ID, CT.TERM_ID, CT.TRX_DATE)) ', 'TRX_DUE_DATE', 'arrx_tx.var.trx_due_date', 'DATE');
608 fa_rx_util_pkg.assign_column('240', null, 'TAX_HEADER_LEVEL_FLAG', 'arrx_tx.var.tax_header_level_flag', 'VARCHAR2', 1);
609 fa_rx_util_pkg.assign_column('250','CT.DOC_SEQUENCE_VALUE', 'DOC_SEQUENCE_VALUE', 'arrx_tx.var.doc_sequence_value', 'NUMBER');
610 fa_rx_util_pkg.assign_column('260','RECDIST.AMOUNT', 'TRX_AMOUNT', 'arrx_tx.var.trx_amount', 'NUMBER');
611 fa_rx_util_pkg.assign_column('270','RECDIST.ACCTD_AMOUNT', 'TRX_ACCTD_AMOUNT', 'arrx_tx.var.trx_acctd_amount', 'NUMBER');
612 fa_rx_util_pkg.assign_column('280','CT.SHIP_TO_CUSTOMER_ID', 'SHIP_TO_CUSTOMER_ID', 'arrx_tx.var.ship_to_customer_id', 'NUMBER', 15);
613 fa_rx_util_pkg.assign_column('290','CT.SHIP_TO_SITE_USE_ID', 'SHIP_TO_SITE_USE_ID', 'arrx_tx.var.ship_to_site_use_id', 'NUMBER', 15);
614 fa_rx_util_pkg.assign_column('300','CT.BILL_TO_CUSTOMER_ID', 'BILL_TO_CUSTOMER_ID', 'arrx_tx.var.bill_to_customer_id', 'NUMBER', 15);
615 fa_rx_util_pkg.assign_column('310','CT.BILL_TO_SITE_USE_ID', 'BILL_TO_SITE_USE_ID', 'arrx_tx.var.bill_to_site_use_id', 'NUMBER', 15);
616 fa_rx_util_pkg.assign_column('320','CT.CUST_TRX_TYPE_ID', 'CUST_TRX_TYPE_ID', 'arrx_tx.var.cust_trx_type_id', 'NUMBER', 15);
617 fa_rx_util_pkg.assign_column('330','CT.TERM_ID', 'TERM_ID', 'arrx_tx.var.term_id', 'NUMBER', 15);
618 fa_rx_util_pkg.assign_column('340','CT.DOC_SEQUENCE_ID', 'DOC_SEQUENCE_ID', 'arrx_tx.var.doc_sequence_id', 'NUMBER', 15);
619 fa_rx_util_pkg.assign_column('350','CT.RECEIPT_METHOD_ID', 'RECEIPT_METHOD_ID', 'arrx_tx.var.receipt_method_id', 'NUMBER', 15);
620 fa_rx_util_pkg.assign_column('360','CT.ORG_ID', 'ORG_ID', 'arrx_tx.var.org_id', 'NUMBER', 15);
621 -- bug3940958 added batch_id and batch_source_id
622 fa_rx_util_pkg.assign_column('370','CT.BATCH_ID', 'BATCH_ID', 'arrx_tx.var.batch_id', 'NUMBER', 15);
623 fa_rx_util_pkg.assign_column('380','CT.BATCH_SOURCE_ID', 'BATCH_SOURCE_ID', 'arrx_tx.var.batch_source_id', 'NUMBER', 15);
624 -->>SELECT_END<<--
625
626
627 --
628 -- Assign From Clause
629 --
630 fa_rx_util_pkg.debug('AR_ASSIGN_FORM_CLAUSE');
631
632 -- bug3940958 changed to _ALL for cross-org
633 IF var.ca_sob_type = 'P'
634 THEN
635 fa_rx_util_pkg.From_Clause := 'RA_CUST_TRX_LINE_GL_DIST_ALL RECDIST,
636 RA_CUSTOMER_TRX_ALL CT,
637 RA_CUST_TRX_TYPES_ALL TRX_TYPE,
638 HZ_CUST_ACCOUNTS_ALL BILL_TO,
639 HZ_PARTIES PARTY,
640 GL_CODE_COMBINATIONS CCRECDIST,
641 AR_RECEIPT_METHODS METHODS,
642 FND_DOCUMENT_SEQUENCES DOC_SEQ,
643 AR_SYSTEM_PARAMETERS_ALL SYSPARAM';
644 ELSE
645 fa_rx_util_pkg.From_Clause := 'RA_TRX_LINE_GL_DIST_ALL_MRC_V RECDIST,
646 RA_CUSTOMER_TRX_ALL_MRC_V CT,
647 RA_CUST_TRX_TYPES_ALL TRX_TYPE,
648 HZ_CUST_ACCOUNTS_ALL BILL_TO,
649 HZ_PARTIES PARTY,
650 GL_CODE_COMBINATIONS CCRECDIST,
651 AR_RECEIPT_METHODS METHODS,
652 FND_DOCUMENT_SEQUENCES DOC_SEQ';
653 END IF;
654
655 --
656 -- Assign Where Clause (including the where clause from the parameters)
657 --
658 fa_rx_util_pkg.debug('AR_ASSIGN_WHERE_CLAUSE');
659
660
661 -- bug:3256137
662
663 -- bug3940958 added where condition for cross-org and new paramereters
664 fa_rx_util_pkg.Where_Clause := 'CT.CUST_TRX_TYPE_ID = TRX_TYPE.CUST_TRX_TYPE_ID
665 AND CT.BILL_TO_CUSTOMER_ID = BILL_TO.CUST_ACCOUNT_ID
666 AND BILL_TO.PARTY_ID = PARTY.PARTY_ID
667 AND CT.receipt_method_id = METHODS.receipt_method_id(+)
671 AND RECDIST.CODE_COMBINATION_ID = CCRECDIST.CODE_COMBINATION_ID
668 AND CT.CUSTOMER_TRX_ID = RECDIST.CUSTOMER_TRX_ID
669 AND RECDIST.ACCOUNT_CLASS = ''REC''
670 AND RECDIST.LATEST_REC_FLAG = ''Y''
672 AND CT.DOC_SEQUENCE_ID = DOC_SEQ.DOC_SEQUENCE_ID(+)
673 AND NVL(CT.ORG_ID, -99) = NVL(SYSPARAM.ORG_ID,-99)
674 AND NVL(CT.ORG_ID, -99) = NVL(TRX_TYPE.ORG_ID, -99) '||
675 COMPLETE_FLAG_WHERE ||' '||
676 POSTED_FLAG_WHERE ||' '||
677 REC_GL_DATE_WHERE ||' '||
678 TRX_DATE_WHERE ||' '||
679 TRX_TYPE_WHERE ||' '||
680 TRX_CLASS_WHERE ||' '||
681 REC_BALANCING_WHERE ||' '||
682 BILL_TO_CUSTOMER_NAME_WHERE ||' '||
683 BILL_TO_CUSTOMER_NUMBER_WHERE ||' '||
684 CURRENCY_CODE_WHERE ||' '||
685 PAYMENT_METHOD_WHERE || ' ' ||
686 DOC_SEQUENCE_NAME_WHERE || ' ' ||
687 DOC_SEQUENCE_NUMBER_WHERE || ' ' ||
688 L_RECDIST_ORG_WHERE || ' ' ||
689 L_CT_ORG_WHERE || ' ' ||
690 L_TRX_TYPE_ORG_WHERE || ' ' ||
691 L_BILL_TO_ORG_WHERE || ' ' ||
692 BATCH_SOURCE_WHERE || ' ' ||
693 REC_ACCOUNT_WHERE ;
694
695 fa_rx_util_pkg.debug('arrx_tx.before_report()-');
696
697 end before_report;
698
699
700 --
701 -- This is the bind trigger for the main artx_rep report
702 procedure bind(c in integer)
703 is
704 b_type_a varchar2(240);
705 b_j_type_n varchar2(80);
706 YES_NO_Y varchar2(80);
707 YES_NO_N varchar2(80);
708 begin
709 fa_rx_util_pkg.debug('AR_GET_BIND');
710 --
711 -- These bind variables(Date Type) were included in the WHERE clause
712 --
713 IF var.start_gl_date IS NOT NULL THEN
714 dbms_sql.bind_variable(c, 'start_gl_date', var.start_gl_date);
715 END IF;
716 IF var.end_gl_date IS NOT NULL THEN
717 dbms_sql.bind_variable(c, 'end_gl_date', var.end_gl_date);
718 END IF;
719
720 --begin for bug 1814839
721
722 IF var.start_bill_to_customer_name IS NULL AND var.end_bill_to_customer_name IS NULL THEN
723 NULL;
724 ELSIF var.start_bill_to_customer_name IS NULL THEN
725 dbms_sql.bind_variable(c, 'end_bill_to_customer_name', var.end_bill_to_customer_name);
726 ELSIF var.end_bill_to_customer_name IS NULL THEN
727 dbms_sql.bind_variable(c, 'start_bill_to_customer_name', var.start_bill_to_customer_name);
728 ELSE
729 dbms_sql.bind_variable(c, 'end_bill_to_customer_name', var.end_bill_to_customer_name);
730 dbms_sql.bind_variable(c, 'start_bill_to_customer_name', var.start_bill_to_customer_name);
731
732 END IF;
733
734 --end for bug 1814839
735
736 IF var.start_transaction_date IS NOT NULL THEN
737 dbms_sql.bind_variable(c, 'start_transaction_date', var.start_transaction_date);
738 END IF;
739 IF var.end_transaction_date IS NOT NULL THEN
740 dbms_sql.bind_variable(c, 'end_transaction_date', var.end_transaction_date);
741 END IF;
742
743 select MEANING into YES_NO_Y from ar_lookups
744 where lookup_type = 'YES/NO' and LOOKUP_CODE = 'Y';
745 select MEANING into YES_NO_N from ar_lookups
746 where lookup_type = 'YES/NO' and LOOKUP_CODE = 'N';
747 dbms_sql.bind_variable(c, 'YES', YES_NO_Y);
748 dbms_sql.bind_variable(c, 'NO', YES_NO_N);
749
750 -- Bug 1988421
751 IF var.start_transaction_type IS NOT NULL THEN
752 dbms_sql.bind_variable(c, 'start_transaction_type', var.start_transaction_type);
753 END IF;
754 IF var.end_transaction_type IS NOT NULL THEN
755 dbms_sql.bind_variable(c, 'end_transaction_type', var.end_transaction_type);
756 END IF;
757 IF var.start_transaction_class IS NOT NULL THEN
758 dbms_sql.bind_variable(c, 'start_transaction_class', var.start_transaction_class);
759 END IF;
760 IF var.end_transaction_class IS NOT NULL THEN
761 dbms_sql.bind_variable(c, 'end_transaction_class', var.end_transaction_class);
762 END IF;
763 IF var.start_bill_to_customer_number IS NOT NULL THEN
764 dbms_sql.bind_variable(c, 'start_bill_to_customer_number', var.start_bill_to_customer_number);
765 END IF;
766 IF var.end_bill_to_customer_number IS NOT NULL THEN
767 dbms_sql.bind_variable(c, 'end_bill_to_customer_number', var.end_bill_to_customer_number);
768 END IF;
769 IF var.start_currency IS NOT NULL THEN
770 dbms_sql.bind_variable(c, 'start_currency', var.start_currency);
771 END IF;
772 IF var.end_currency IS NOT NULL THEN
773 dbms_sql.bind_variable(c, 'end_currency', var.end_currency);
774 END IF;
775 IF var.payment_method IS NOT NULL THEN
776 dbms_sql.bind_variable(c, 'payment_method', var.payment_method);
777 END IF;
778 IF var.doc_sequence_name IS NOT NULL THEN
779 dbms_sql.bind_variable(c, 'doc_sequence_name', var.doc_sequence_name);
780 END IF;
781 IF var.doc_sequence_number_from IS NOT NULL THEN
782 dbms_sql.bind_variable(c, 'doc_sequence_number_from', var.doc_sequence_number_from);
783 END IF;
784 IF var.doc_sequence_number_to IS NOT NULL THEN
785 dbms_sql.bind_variable(c, 'doc_sequence_number_to', var.doc_sequence_number_to);
786 END IF;
787
788 -- bug3940958 added for new binds
792 END IF;
789 -- p_reporting_entity_id is used only for operating unit level
790 IF var.reporting_level = '3000' THEN
791 dbms_sql.bind_variable(c, 'p_reporting_entity_id', var.reporting_entity_id);
793
794 IF var.batch_source_name IS NOT NULL THEN
795 dbms_sql.bind_variable(c, 'batch_source_name', var.batch_source_name);
796 END IF;
797
798 IF var.transaction_class IS NOT NULL THEN
799 dbms_sql.bind_variable(c, 'transaction_class', var.transaction_class);
800 END IF;
801 end bind;
802
803
804 --
805 -- This is the after fetch trigger for the main artx_rep report
806 procedure after_fetch
807 is
808 begin
809 --
810 -- Get FLEX FIELD VALUE and DESCRIPTION
811 --
812 fa_rx_util_pkg.debug('GL_GET_FLEX_KEYWORD');
813
814 /* var.rec_account := fa_rx_flex_pkg.get_value(
815 p_application_id => 101,
816 p_id_flex_code => 'GL#',
817 p_id_flex_num => var.chart_of_accounts_id,
818 p_qualifier => 'ALL',
819 p_ccid => var.ccid);*/
820
821 var.rec_account_desc := substrb(fa_rx_flex_pkg.get_description(
822 p_application_id => 101,
823 p_id_flex_code => 'GL#',
824 p_id_flex_num => var.chart_of_accounts_id,
825 p_qualifier => 'ALL',
826 p_data => var.rec_account),1,240);
827
828 /* var.rec_balance := fa_rx_flex_pkg.get_value(
829 p_application_id => 101,
830 p_id_flex_code => 'GL#',
831 p_id_flex_num => var.chart_of_accounts_id,
832 p_qualifier => 'GL_BALANCING',
833 p_ccid => var.ccid);*/
834
835 var.rec_balance_desc := substrb(fa_rx_flex_pkg.get_description(
836 p_application_id => 101,
837 p_id_flex_code => 'GL#',
838 p_id_flex_num => var.chart_of_accounts_id,
839 p_qualifier => 'GL_BALANCING',
840 p_data => var.rec_balance),1,240);
841
842 /* var.rec_natacct := fa_rx_flex_pkg.get_value(
843 p_application_id => 101,
844 p_id_flex_code => 'GL#',
845 p_id_flex_num => var.chart_of_accounts_id,
846 p_qualifier => 'GL_ACCOUNT',
847 p_ccid => var.ccid);*/
848
849 var.rec_natacct_desc := substrb(fa_rx_flex_pkg.get_description(
850 p_application_id => 101,
851 p_id_flex_code => 'GL#',
852 p_id_flex_num => var.chart_of_accounts_id,
853 p_qualifier => 'GL_ACCOUNT',
854 p_data => var.rec_natacct),1,240);
855
856 end after_fetch;
857
858
859 --
860 -- Main AR TRANSACTION CHECK RX Report function(Plug-In)
861 --
862 procedure artx_rep_check (
863 completed_flag in varchar2,
864 posted_flag in varchar2,
865 start_gl_date in date,
866 end_gl_date in date,
867 start_transaction_date in date,
868 end_transaction_date in date,
869 start_transaction_type in varchar2,
870 end_transaction_type in varchar2,
871 start_transaction_class in varchar2,
872 end_transaction_class in varchar2,
873 start_balancing_segment in varchar2,
874 end_balancing_segment in varchar2,
875 start_bill_to_customer_name in varchar2,
876 end_bill_to_customer_name in varchar2,
877 start_currency in varchar2,
878 end_currency in varchar2,
879 payment_method in varchar2,
880 start_update_date in date,
881 end_update_date in date,
882 last_updated_by in number,
883 request_id in number,
884 retcode out NOCOPY number,
885 errbuf out NOCOPY varchar2)
886 is
887
888 -- Document sequence parameter declarations
889 doc_sequence_name varchar2(30) := NULL;
890 doc_sequence_number_from number := NULL;
891 doc_sequence_number_to number := NULL;
892 -- Customer Number parameter declarations
893 start_bill_to_customer_number varchar2(30) := NULL;
894 end_bill_to_customer_number varchar2(30) := NULL;
895 begin
896 fa_rx_util_pkg.debug('arrx_tx.artx_rep_check()+');
897
898 --
899 -- Assign parameters to global variable
900 -- These values will be used within the before_report trigger
901
902 var.start_update_date := Trunc(start_update_date);
903 var.end_update_date := Trunc(end_update_date)+1-1/24/60/60;
904 var.last_updated_by := last_updated_by;
905
906 fa_rx_util_pkg.debug('start_update_date = '|| var.start_update_date);
907 fa_rx_util_pkg.debug('end_update_date = '|| var.end_update_date);
908 fa_rx_util_pkg.debug('last_updated_by = '|| var.last_updated_by);
909
910 --
914 --
911 -- Initialize request
912 fa_rx_util_pkg.init_request('arrx_tx.artx_rep_check',request_id,'AR_TRANSACTIONS_REP_ITF');
913
915 -- Call the main journal report
916
917 -- bug3940958 added some parameters
918 arrx_tx.artx_rep(
919 completed_flag,
920 posted_flag,
921 start_gl_date,
922 end_gl_date,
923 start_transaction_date,
924 end_transaction_date,
925 start_transaction_type,
926 end_transaction_type,
927 start_transaction_class,
928 end_transaction_class,
929 start_balancing_segment,
930 end_balancing_segment,
931 start_bill_to_customer_name,
932 end_bill_to_customer_name,
933 start_currency,
934 end_currency,
935 payment_method,
936 doc_sequence_name,
937 doc_sequence_number_from,
938 doc_sequence_number_to,
939 start_bill_to_customer_number,
940 end_bill_to_customer_number,
941 null,
942 null,
943 null,
944 null,
945 null,
946 null,
947 request_id,
948 retcode,
949 errbuf);
950
951
952 --
953 -- Assign triggers specific to this report
954 -- Make sure that you make your assignment to the correct section ('AR TRANSACTION')
955 fa_rx_util_pkg.assign_report('AR TRANSACTIONS',
956 true,
957 'arrx_tx.check_before_report;',
958 'arrx_tx.check_bind(:CURSOR_SELECT);',
959 'arrx_tx.check_after_fetch;',
960 null);
961
962 --
963 -- Run the report.
964 -- Make sure to pass the p_calling_proc assigned from within this procedure ('arrx_tx.artx_rep_check')
965 fa_rx_util_pkg.run_report('arrx_tx.artx_rep_check', retcode, errbuf);
966
967 fa_rx_util_pkg.debug('arrx_tx.artx_rep_check()-');
968
969 exception
970 when others then
971 fa_rx_util_pkg.log(sqlcode);
972 fa_rx_util_pkg.log(sqlerrm);
973 fa_rx_util_pkg.debug(sqlcode);
974 fa_rx_util_pkg.debug(sqlerrm);
975 fa_rx_util_pkg.debug('arrx_tx.artx_rep_check(EXCEPTION)-');
976 end artx_rep_check;
977
978
979 --
980 -- This is the before report trigger for the artx_rep_check report.
981 procedure check_before_report
982 is
983 CC_ACCOUNT_SEL varchar2(500);
984 CC_BALANCE_SEL varchar2(500);
985 CC_NATURAL_SEL varchar2(500);
986
987 decode_inv varchar2(500);
988 get_item varchar2(500);
989 LAST_UPDATE_WHERE varchar2(500);
990 begin
991 fa_rx_util_pkg.debug('arrx_tx.check_before_report()+');
992
993 fa_rx_util_pkg.debug('GL_GET_PROFILE_SO_FLEX_CODE');
994
995 oe_profile.get(
996 name => 'SO_ID_FLEX_CODE',
997 val => var.so_id_flex_code);
998
999
1000 fa_rx_util_pkg.debug('GL_GET_PROFILE_SO_ORG_ID');
1001
1002 oe_profile.get(
1003 name => 'SO_ORGANIZATION_ID',
1004 val => var.so_organization_id);
1005
1006 get_item := fa_rx_flex_pkg.flex_sql(
1007 p_application_id => 401,
1008 p_id_flex_code => var.so_id_flex_code,
1009 p_id_flex_num => null,
1010 p_table_alias => 'ITEM',
1011 p_mode => 'SELECT',
1012 p_qualifier => 'ALL');
1013
1014 decode_inv := 'DECODE(CTL.INVENTORY_ITEM_ID,NULL,DECODE(CTL.MEMO_LINE_ID,NULL,CTL.DESCRIPTION,MEMO.NAME),
1015 '|| get_item ||' ) ';
1016
1017 --
1018 -- Flex SQL
1019 --
1020 CC_ACCOUNT_SEL :=
1021 FA_RX_FLEX_PKG.FLEX_SQL(
1022 p_application_id => 101,
1023 p_id_flex_code => 'GL#',
1024 p_id_flex_num => var.chart_of_accounts_id,
1025 p_table_alias => 'CCDIST',
1026 p_mode => 'SELECT',
1027 p_qualifier => 'ALL');
1028 CC_BALANCE_SEL :=
1029 FA_RX_FLEX_PKG.FLEX_SQL(
1030 p_application_id => 101,
1031 p_id_flex_code => 'GL#',
1032 p_id_flex_num => var.chart_of_accounts_id,
1033 p_table_alias => 'CCDIST',
1034 p_mode => 'SELECT',
1035 p_qualifier => 'GL_BALANCING');
1036 CC_NATURAL_SEL :=
1037 FA_RX_FLEX_PKG.FLEX_SQL(
1038 p_application_id => 101,
1039 p_id_flex_code => 'GL#',
1040 p_id_flex_num => var.chart_of_accounts_id,
1041 p_table_alias => 'CCDIST',
1042 p_mode => 'SELECT',
1043 p_qualifier => 'GL_ACCOUNT');
1044
1045 --
1046 -- Assign another column specific to this report
1047 fa_rx_util_pkg.debug('AR_ADD_SELECT_COLUMNS');
1048
1049 fa_rx_util_pkg.assign_column('c1 ','CCDIST.CODE_COMBINATION_ID', null, 'arrx_tx.var.ccid2', 'NUMBER');
1053 fa_rx_util_pkg.assign_column('c5 ','DIST.CUST_TRX_LINE_GL_DIST_ID', 'CUST_TRX_LINE_GL_DIST_ID', 'arrx_tx.var.cust_trx_line_gl_dist_id', 'NUMBER');
1050 fa_rx_util_pkg.assign_column('c2 ','CTL.CUSTOMER_TRX_LINE_ID', 'CUSTOMER_TRX_LINE_ID', 'arrx_tx.var.customer_trx_line_id', 'NUMBER');
1051 fa_rx_util_pkg.assign_column('c3 ','CTL.LINK_TO_CUST_TRX_LINE_ID', 'LINK_TO_CUST_TRX_LINE_ID', 'arrx_tx.var.link_to_cust_trx_line_id', 'NUMBER');
1052 fa_rx_util_pkg.assign_column('c4 ',decode_inv, 'INVENTORY_ITEM', 'arrx_tx.var.inventory_item', 'VARCHAR2',240);
1054 fa_rx_util_pkg.assign_column('c6 ',CC_ACCOUNT_SEL, 'ACCOUNT', 'arrx_tx.var.account', 'VARCHAR2',240);
1055 fa_rx_util_pkg.assign_column('c7 ',null, 'ACCOUNT_DESC', 'arrx_tx.var.account_desc', 'VARCHAR2',240);
1056 fa_rx_util_pkg.assign_column('c8 ',CC_BALANCE_SEL, 'BALANCE', 'arrx_tx.var.balance', 'VARCHAR2',240);
1057 fa_rx_util_pkg.assign_column('c9 ',null, 'BALANCE_DESC', 'arrx_tx.var.balance_desc', 'VARCHAR2',240);
1058 fa_rx_util_pkg.assign_column('c10',CC_NATURAL_SEL, 'NATACCT', 'arrx_tx.var.natacct', 'VARCHAR2',240);
1059 fa_rx_util_pkg.assign_column('c11',null, 'NATACCT_DESC', 'arrx_tx.var.natacct_desc', 'VARCHAR2',240);
1060
1061 --
1062 -- Add to the FROM clause specific to this report
1063 fa_rx_util_pkg.debug('AR_ADD_FORM_CLAUSE');
1064 fa_rx_util_pkg.From_Clause :=
1065 fa_rx_util_pkg.From_Clause || ',
1066 RA_CUSTOMER_TRX_LINES CTL,
1067 RA_CUST_TRX_LINE_GL_DIST DIST,
1068 MTL_SYSTEM_ITEMS ITEM,
1069 AR_MEMO_LINES MEMO,
1070 GL_CODE_COMBINATIONS CCDIST';
1071
1072 --
1073 -- Add to the WHERE clause specific to this report
1074 fa_rx_util_pkg.debug('AR_ADD_WHERE_CLAUSE');
1075
1076 IF var.last_updated_by is null THEN
1077 LAST_UPDATE_WHERE := ' AND ARRX_TX.WHERE_LAST_UPDATE(CT.CUSTOMER_TRX_ID,null,
1078 :start_update_date,:end_update_date) = ''Y'' ';
1079 ELSE
1080 LAST_UPDATE_WHERE := ' AND ARRX_TX.WHERE_LAST_UPDATE(CT.CUSTOMER_TRX_ID,'|| var.last_updated_by ||',
1081 :start_update_date,:end_update_date) = ''Y'' ';
1082 END IF;
1083
1084 fa_rx_util_pkg.Where_Clause :=
1085 fa_rx_util_pkg.Where_Clause || '
1086 AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
1087 AND ITEM.ORGANIZATION_ID(+) = '|| var.so_organization_id ||'
1088 AND CTL.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID(+)
1089 AND CTL.MEMO_LINE_ID = MEMO.MEMO_LINE_ID(+)
1090 AND CT.CUSTOMER_TRX_ID = DIST.CUSTOMER_TRX_ID
1091 AND CTL.CUSTOMER_TRX_LINE_ID = DIST.CUSTOMER_TRX_LINE_ID
1092 AND DIST.ACCOUNT_SET_FLAG = ''N''
1093 AND DIST.CODE_COMBINATION_ID = CCDIST.CODE_COMBINATION_ID '||
1094 LAST_UPDATE_WHERE;
1095
1096 fa_rx_util_pkg.debug('arrx_tx.check_before_report()-');
1097
1098 end check_before_report;
1099
1100
1101 --
1102 -- This is the bind trigger for the main artx_rep report
1103 procedure check_bind(c in integer)
1104 is
1105 begin
1106 fa_rx_util_pkg.debug('AR_GET_BIND');
1107 --
1108 -- These bind variables(Date Type) were included in the WHERE clause
1109 --
1110 dbms_sql.bind_variable(c, 'start_update_date', var.start_update_date);
1111 dbms_sql.bind_variable(c, 'end_update_date', var.end_update_date);
1112
1113 end check_bind;
1114
1115
1116 --
1117 -- This is the after fetch trigger for the main artx_rep report
1118 procedure check_after_fetch
1119 is
1120 begin
1121
1122 --
1123 -- Get FLEX FIELD VALUE and DESCRIPTION
1124 --
1125 fa_rx_util_pkg.debug('AR_GET_FLEX_KEYWORD');
1126
1127 /* var.account := fa_rx_flex_pkg.get_value(
1128 p_application_id => 101,
1129 p_id_flex_code => 'GL#',
1130 p_id_flex_num => var.chart_of_accounts_id,
1131 p_qualifier => 'ALL',
1132 p_ccid => var.ccid2);*/
1133
1134 var.account_desc := substrb(fa_rx_flex_pkg.get_description(
1135 p_application_id => 101,
1136 p_id_flex_code => 'GL#',
1137 p_id_flex_num => var.chart_of_accounts_id,
1138 p_qualifier => 'ALL',
1139 p_data => var.account), 1, 240);
1140
1141 /* var.balance := fa_rx_flex_pkg.get_value(
1142 p_application_id => 101,
1143 p_id_flex_code => 'GL#',
1144 p_id_flex_num => var.chart_of_accounts_id,
1145 p_qualifier => 'GL_BALANCING',
1146 p_ccid => var.ccid2);*/
1147
1148 var.balance_desc := substrb(fa_rx_flex_pkg.get_description(
1149 p_application_id => 101,
1153 p_data => var.balance), 1, 240);
1150 p_id_flex_code => 'GL#',
1151 p_id_flex_num => var.chart_of_accounts_id,
1152 p_qualifier => 'GL_BALANCING',
1154
1155 /* var.natacct := fa_rx_flex_pkg.get_value(
1156 p_application_id => 101,
1157 p_id_flex_code => 'GL#',
1158 p_id_flex_num => var.chart_of_accounts_id,
1159 p_qualifier => 'GL_ACCOUNT',
1160 p_ccid => var.ccid2);*/
1161
1162 var.natacct_desc := substrb(fa_rx_flex_pkg.get_description(
1163 p_application_id => 101,
1164 p_id_flex_code => 'GL#',
1165 p_id_flex_num => var.chart_of_accounts_id,
1166 p_qualifier => 'GL_ACCOUNT',
1167 p_data => var.natacct), 1, 240);
1168
1169 end check_after_fetch;
1170
1171
1172 --
1173 -- Main AR TRANSACTION FORECAST RX Report function(Plug-In)
1174 --
1175 procedure artx_rep_forecast(
1176 completed_flag in varchar2,
1177 posted_flag in varchar2,
1178 start_gl_date in date,
1179 end_gl_date in date,
1180 start_transaction_date in date,
1181 end_transaction_date in date,
1182 start_transaction_type in varchar2,
1183 end_transaction_type in varchar2,
1184 start_transaction_class in varchar2,
1185 end_transaction_class in varchar2,
1186 start_balancing_segment in varchar2,
1187 end_balancing_segment in varchar2,
1188 start_bill_to_customer_name in varchar2,
1189 end_bill_to_customer_name in varchar2,
1190 start_currency in varchar2,
1191 end_currency in varchar2,
1192 payment_method in varchar2,
1193 start_due_date in date,
1194 end_due_date in date,
1195 request_id in number,
1196 retcode out NOCOPY number,
1197 errbuf out NOCOPY varchar2)
1198 is
1199
1200 -- Document sequence parameter declarations
1201 doc_sequence_name varchar2(30) := NULL;
1202 doc_sequence_number_from number := NULL;
1203 doc_sequence_number_to number := NULL;
1204 -- Customer Number parameter declarations
1205 start_bill_to_customer_number varchar2(30) := NULL;
1206 end_bill_to_customer_number varchar2(30) := NULL;
1207
1208 begin
1209 fa_rx_util_pkg.debug('arrx_tx.artx_rep_forecast()+');
1210
1211 --
1212 -- Assign parameters to global variable
1213 -- These values will be used within the before_report trigger
1214
1215 var.start_due_date := Trunc(start_due_date);
1216 var.end_due_date := Trunc(end_due_date)+1-1/24/60/60;
1217
1218 fa_rx_util_pkg.debug('start_due_date = '|| var.start_due_date);
1219 fa_rx_util_pkg.debug('end_due_date = '|| var.end_due_date);
1220
1221 --
1222 -- Initialize request
1223 fa_rx_util_pkg.init_request('arrx_tx.artx_rep_forecast',request_id,'AR_TRANSACTIONS_REP_ITF');
1224
1225 --
1226 -- Call the main journal report
1227
1228 -- bug3940958 added some parameters
1229 arrx_tx.artx_rep(
1230 completed_flag,
1231 posted_flag,
1232 start_gl_date,
1233 end_gl_date,
1234 start_transaction_date,
1235 end_transaction_date,
1236 start_transaction_type,
1237 end_transaction_type,
1238 start_transaction_class,
1239 end_transaction_class,
1240 start_balancing_segment,
1241 end_balancing_segment,
1242 start_bill_to_customer_name,
1243 end_bill_to_customer_name,
1244 start_currency,
1245 end_currency,
1246 payment_method,
1247 doc_sequence_name,
1248 doc_sequence_number_from,
1249 doc_sequence_number_to,
1250 start_bill_to_customer_number,
1251 end_bill_to_customer_number,
1252 null,
1253 null,
1254 null,
1255 null,
1256 null,
1257 null,
1258 request_id,
1259 retcode,
1260 errbuf);
1261
1262 --
1263 -- Assign triggers specific to this report
1264 -- Make sure that you make your assignment to the correct section ('AR TRANSACTION')
1265 fa_rx_util_pkg.assign_report('AR TRANSACTIONS',
1266 true,
1267 'arrx_tx.forecast_before_report;',
1268 'arrx_tx.forecast_bind(:CURSOR_SELECT);',
1269 null,
1270 null);
1271
1272 --
1273 -- Run the report.
1274 -- Make sure to pass the p_calling_proc assigned from within this procedure ('arrx_tx.artx_rep_forecast')
1275 fa_rx_util_pkg.run_report('arrx_tx.artx_rep_forecast', retcode, errbuf);
1276
1277 fa_rx_util_pkg.debug('arrx_tx.artx_rep_forecast()-');
1278
1279 exception
1280 when others then
1281 fa_rx_util_pkg.log(sqlcode);
1282 fa_rx_util_pkg.log(sqlerrm);
1283 fa_rx_util_pkg.debug(sqlcode);
1284 fa_rx_util_pkg.debug(sqlerrm);
1285 fa_rx_util_pkg.debug('arrx_tx.artx_rep_forecast(EXCEPTION)-');
1286 end artx_rep_forecast;
1287
1288 --
1289 -- This is the before report trigger for the artx_rep_forecast report.
1290 procedure forecast_before_report
1291 is
1292 SCHEDULE_DUE_DATE_WHERE varchar2(500);
1293 begin
1297 -- Assign another column specific to this report
1294 fa_rx_util_pkg.debug('arrx_tx.forecast_before_report()+');
1295
1296 --
1298 fa_rx_util_pkg.debug('AR_ADD_SELECT_COLUMNS');
1299
1300 fa_rx_util_pkg.assign_column('f1 ','PS.PAYMENT_SCHEDULE_ID', 'TRX_PAYMENT_SCHEDULE_ID', 'arrx_tx.var.trx_payment_schedule_id', 'NUMBER');
1301 fa_rx_util_pkg.assign_column('60 ','CONS_INV.CONS_BILLING_NUMBER', 'CONS_BILL_NUMBER', 'arrx_tx.var.cons_bill_number', 'VARCHAR2', 30);
1302
1303 --
1304 -- Add to the FROM clause specific to this report
1305 fa_rx_util_pkg.debug('AR_ADD_FORM_CLAUSE');
1306 fa_rx_util_pkg.From_Clause := '
1307 AR_PAYMENT_SCHEDULES PS,'||
1308 fa_rx_util_pkg.From_Clause || ',
1309 AR_CONS_INV CONS_INV';
1310
1311 --
1312 -- Add to the WHERE clause specific to this report
1313 fa_rx_util_pkg.debug('AR_ADD_WHERE_CLAUSE');
1314
1315 IF var.start_due_date IS NULL AND var.end_due_date IS NULL THEN
1316 SCHEDULE_DUE_DATE_WHERE := NULL;
1317 ELSIF var.start_due_date IS NULL THEN
1318 SCHEDULE_DUE_DATE_WHERE := ' AND PS.DUE_DATE <= :end_due_date';
1319 ELSIF var.end_due_date IS NULL THEN
1320 SCHEDULE_DUE_DATE_WHERE := ' AND PS.DUE_DATE >= :start_due_date';
1321 ELSE
1322 SCHEDULE_DUE_DATE_WHERE := ' AND PS.DUE_DATE BETWEEN :start_due_date AND :end_due_date';
1323 END IF;
1324
1325 fa_rx_util_pkg.Where_Clause :=
1326 fa_rx_util_pkg.Where_Clause || '
1327 AND CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
1328 AND PS.STATUS = ''OP''
1329 AND PS.CONS_INV_ID = CONS_INV.CONS_INV_ID(+) '||
1330 SCHEDULE_DUE_DATE_WHERE;
1331
1332 fa_rx_util_pkg.debug('arrx_tx.forecast_before_report()-');
1333
1334 end forecast_before_report;
1335
1336
1337 --
1338 -- This is the bind trigger for the main artx_rep report
1339 procedure forecast_bind(c in integer)
1340 is
1341 begin
1342 fa_rx_util_pkg.debug('AR_GET_BIND');
1343 --
1344 -- These bind variables(Date Type) were included in the WHERE clause
1345 --
1346 IF var.start_due_date IS NOT NULL THEN
1347 dbms_sql.bind_variable(c, 'start_due_date', var.start_due_date);
1348 END IF;
1349 IF var.end_due_date IS NOT NULL THEN
1350 dbms_sql.bind_variable(c, 'end_due_date', var.end_due_date);
1351 END IF;
1352
1353 end forecast_bind;
1354
1355
1356 --
1357 -- Main AR SALES REGISTER RX Report function(Plug-In)
1358 --
1359 procedure artx_sales_rep (
1360 completed_flag in varchar2,
1361 posted_flag in varchar2,
1362 transaction_type in varchar2,
1363 line_invoice in varchar2,
1364 start_invoice_num in varchar2,
1365 end_invoice_num in varchar2,
1366 doc_sequence_name in varchar2,
1367 start_doc_sequence_value in number,
1368 end_doc_sequence_value in number,
1369 start_gl_date in date,
1370 end_gl_date in date,
1371 start_company_segment in varchar2,
1372 end_company_segment in varchar2,
1373 start_rec_nat_acct in varchar2,
1374 end_rec_nat_acct in varchar2,
1375 start_account in varchar2,
1376 end_account in varchar2,
1377 start_currency in varchar2,
1378 end_currency in varchar2,
1379 start_amount in number,
1380 end_amount in number,
1381 start_customer_name in varchar2,
1382 end_customer_name in varchar2,
1383 start_customer_number in varchar2,
1384 end_customer_number in varchar2,
1385 request_id in number,
1386 retcode out NOCOPY number,
1387 errbuf out NOCOPY varchar2)
1388 is
1389
1390 -- Some parameter for main procedure declarations
1391 start_transaction_date date := to_date(NULL);
1392 end_transaction_date date := to_date(NULL);
1393 start_transaction_class varchar2(20) := NULL;
1394 end_transaction_class varchar2(20) := NULL;
1395 payment_method varchar2(30) := NULL;
1396
1397 begin
1398 fa_rx_util_pkg.debug('arrx_tx.artx_sales_rep()+');
1399 --
1400 -- Set global variables (This variable will be used in before report trigger.)
1401 --
1402 var.line_invoice := line_invoice;
1403 var.start_invoice_num := start_invoice_num;
1404 var.end_invoice_num := end_invoice_num;
1405 var.start_rec_nat_acct := start_rec_nat_acct;
1406 var.end_rec_nat_acct := end_rec_nat_acct;
1407 var.start_account := start_account;
1408 var.end_account := end_account;
1409 var.start_amount := start_amount;
1410 var.end_amount := end_amount;
1411
1412 --
1413 -- Initialize request
1414 fa_rx_util_pkg.init_request('arrx_tx.artx_sales_rep',request_id,'AR_TRANSACTIONS_REP_ITF');
1415
1416 --
1417 -- Call the main journal report
1418
1419 -- bug3940958 added new parameters
1420 arrx_tx.artx_rep(
1421 completed_flag,
1422 posted_flag,
1423 start_gl_date,
1424 end_gl_date,
1425 start_transaction_date,
1429 start_transaction_class,
1426 end_transaction_date,
1427 transaction_type,
1428 transaction_type,
1430 end_transaction_class,
1431 start_company_segment,
1432 end_company_segment,
1433 start_customer_name,
1434 end_customer_name,
1435 start_currency,
1436 end_currency,
1437 payment_method,
1438 doc_sequence_name,
1439 start_doc_sequence_value,
1440 end_doc_sequence_value,
1441 start_customer_number,
1442 end_customer_number,
1443 null,
1444 null,
1445 start_account,-- Start bug 5571594
1446 end_account,-- End bug 5571594 SPDIXIT
1447 null,
1448 null,
1449 request_id,
1450 retcode,
1451 errbuf);
1452
1453 --
1454 -- Assign triggers specific to this report
1455 -- Make sure that you make your assignment to the correct section ('AR TRANSACTION')
1456 fa_rx_util_pkg.assign_report('AR TRANSACTIONS',
1457 true,
1458 'arrx_tx.sales_before_report;',
1459 'arrx_tx.sales_bind(:CURSOR_SELECT);',
1460 'arrx_tx.sales_after_fetch;',
1461 null);
1462
1463 --
1464 -- Run the report.
1465 -- Make sure to pass the p_calling_proc assigned from within this procedure ('arrx_tx.artx_sales_rep')
1466 fa_rx_util_pkg.run_report('arrx_tx.artx_sales_rep', retcode, errbuf);
1467
1468 fa_rx_util_pkg.debug('arrx_tx.artx_sales_rep()-');
1469
1470 exception
1471 when others then
1472 fa_rx_util_pkg.log(sqlcode);
1473 fa_rx_util_pkg.log(sqlerrm);
1474 fa_rx_util_pkg.debug(sqlcode);
1475 fa_rx_util_pkg.debug(sqlerrm);
1476 fa_rx_util_pkg.debug('arrx_tx.artx_sales_rep(EXCEPTION)-');
1477 end artx_sales_rep;
1478
1479
1480 --
1481 -- This is the before report trigger for the artx_sales_rep report.
1482 procedure sales_before_report
1483 is
1484 CC_ACCOUNT_SEL varchar2(500);
1485 CC_BALANCE_SEL varchar2(500);
1486 CC_NATURAL_SEL varchar2(500);
1487
1488 decode_inv varchar2(500);
1489 get_item varchar2(500);
1490 LAST_UPDATE_WHERE varchar2(500);
1491
1492 transaction_number_where varchar2(1000); -- where-clause statement for transaction numbers(invoice numbers)
1493 natural_account_where varchar2(1000); -- where-clause statement for receivables natural accounts
1494 account_where varchar2(1000); -- where-clause statement for line accounts
1495 amount_where varchar2(1000); -- where-clause statement for line amounts
1496 line_select_statement varchar2(1000); -- where-clause statement for sub-query of line information
1497
1498 begin
1499 fa_rx_util_pkg.debug('arrx_tx.sales_before_report()+');
1500
1501 fa_rx_util_pkg.debug('GL_GET_PROFILE_SO_FLEX_CODE');
1502
1503 oe_profile.get(
1504 name => 'SO_ID_FLEX_CODE',
1505 val => var.so_id_flex_code);
1506
1507
1508 fa_rx_util_pkg.debug('GL_GET_PROFILE_SO_ORG_ID');
1509
1510 oe_profile.get(
1511 name => 'SO_ORGANIZATION_ID',
1512 val => var.so_organization_id);
1513
1514 get_item := fa_rx_flex_pkg.flex_sql(
1515 p_application_id => 401,
1516 p_id_flex_code => var.so_id_flex_code,
1517 p_id_flex_num => null,
1518 p_table_alias => 'ITEM',
1519 p_mode => 'SELECT',
1520 p_qualifier => 'ALL');
1521
1522 decode_inv := 'DECODE(CTL.INVENTORY_ITEM_ID,NULL,DECODE(CTL.MEMO_LINE_ID,NULL,CTL.DESCRIPTION,MEMO.NAME),
1523 '|| get_item ||' ) ';
1524
1525 --
1526 -- Flex SQL for Select columns
1527 --
1528 CC_ACCOUNT_SEL :=
1529 FA_RX_FLEX_PKG.FLEX_SQL(
1530 p_application_id => 101,
1531 p_id_flex_code => 'GL#',
1532 p_id_flex_num => var.chart_of_accounts_id,
1533 p_table_alias => 'CCDIST',
1534 p_mode => 'SELECT',
1535 p_qualifier => 'ALL');
1536 CC_BALANCE_SEL :=
1537 FA_RX_FLEX_PKG.FLEX_SQL(
1538 p_application_id => 101,
1539 p_id_flex_code => 'GL#',
1540 p_id_flex_num => var.chart_of_accounts_id,
1541 p_table_alias => 'CCDIST',
1542 p_mode => 'SELECT',
1543 p_qualifier => 'GL_BALANCING');
1544 CC_NATURAL_SEL :=
1545 FA_RX_FLEX_PKG.FLEX_SQL(
1546 p_application_id => 101,
1547 p_id_flex_code => 'GL#',
1548 p_id_flex_num => var.chart_of_accounts_id,
1549 p_table_alias => 'CCDIST',
1550 p_mode => 'SELECT',
1551 p_qualifier => 'GL_ACCOUNT');
1552
1553 --
1554 -- Create some Where-Clause
1555 -- Invoice Number parameters
1556 if (var.start_invoice_num is NULL) and (var.end_invoice_num is NULL) then
1557 transaction_number_where := NULL;
1558 elsif var.start_invoice_num is NULL then
1562 else
1559 transaction_number_where := ' AND CT.TRX_NUMBER <= :end_invoice_num ';
1560 elsif var.end_invoice_num is NULL then
1561 transaction_number_where := ' AND CT.TRX_NUMBER >= :start_invoice_num ';
1563 transaction_number_where := ' AND CT.TRX_NUMBER between :start_invoice_num and :end_invoice_num ';
1564 end if;
1565
1566 -- Receivables Natural Account parameters
1567 if (var.start_rec_nat_acct is NULL) and (var.end_rec_nat_acct is NULL) then
1568 natural_account_where := NULL;
1569 elsif var.start_rec_nat_acct is NULL then
1570 natural_account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1571 p_application_id => 101,
1572 p_id_flex_code => 'GL#',
1573 p_id_flex_num => var.chart_of_accounts_id,
1574 p_table_alias => 'CCRECDIST',
1575 p_mode => 'WHERE',
1576 p_qualifier => 'GL_ACCOUNT',
1577 P_function => '<=',
1578 p_operand1 => var.end_rec_nat_acct);
1579 elsif var.end_rec_nat_acct is NULL then
1580 natural_account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1581 p_application_id => 101,
1582 p_id_flex_code => 'GL#',
1583 p_id_flex_num => var.chart_of_accounts_id,
1584 p_table_alias => 'CCRECDIST',
1585 p_mode => 'WHERE',
1586 p_qualifier => 'GL_ACCOUNT',
1587 p_function => '>=',
1588 p_operand1 => var.start_rec_nat_acct);
1589 else
1590 natural_account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1591 p_application_id => 101,
1592 p_id_flex_code => 'GL#',
1593 p_id_flex_num => var.chart_of_accounts_id,
1594 p_table_alias => 'CCRECDIST',
1595 p_mode => 'WHERE',
1596 p_qualifier => 'GL_ACCOUNT',
1597 p_function => 'BETWEEN',
1598 p_operand1 => var.start_rec_nat_acct,
1599 p_operand2 => var.end_rec_nat_acct);
1600 end if;
1601
1602 -- Line Account parameters
1603 if var.line_invoice = 'LINE' then
1604 if (var.start_account is NULL) and (var.end_account is NULL) then
1605 account_where := NULL;
1606 elsif var.start_account is NULL then
1607 account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1608 p_application_id => 101,
1609 p_id_flex_code => 'GL#',
1610 p_id_flex_num => var.chart_of_accounts_id,
1611 p_table_alias => 'CCDIST', -- This alias is used in main select statement
1612 p_mode => 'WHERE',
1613 p_qualifier => 'ALL',
1614 p_function => '<=',
1615 p_operand1 => var.end_account);
1616 elsif var.end_account is NULL then
1617 account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1618 p_application_id => 101,
1619 p_id_flex_code => 'GL#',
1620 p_id_flex_num => var.chart_of_accounts_id,
1621 p_table_alias => 'CCDIST', -- This alias is used in main select statement
1622 p_mode => 'WHERE',
1623 p_qualifier => 'ALL',
1624 p_function => '>=',
1625 p_operand1 => var.start_account);
1626 else
1627 account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1628 p_application_id => 101,
1629 p_id_flex_code => 'GL#',
1630 p_id_flex_num => var.chart_of_accounts_id,
1631 p_table_alias => 'CCDIST', -- This alias is used in main select statement
1632 p_mode => 'WHERE',
1633 p_qualifier => 'ALL',
1634 p_function => 'BETWEEN',
1635 p_operand1 => var.start_account,
1636 p_operand2 => var.end_account);
1637 end if;
1638 else
1639 if (var.start_account is NULL) and (var.end_account is NULL) then
1640 account_where := NULL;
1641 elsif var.start_account is NULL then
1642 account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1643 p_application_id => 101,
1644 p_id_flex_code => 'GL#',
1645 p_id_flex_num => var.chart_of_accounts_id,
1646 p_table_alias => 'LINEGL', -- This alias is used in sub query.
1647 p_mode => 'WHERE',
1648 p_qualifier => 'ALL',
1649 p_function => '<=',
1650 p_operand1 => var.end_account);
1651 elsif var.end_account is NULL then
1652 account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1653 p_application_id => 101,
1654 p_id_flex_code => 'GL#',
1655 p_id_flex_num => var.chart_of_accounts_id,
1656 p_table_alias => 'LINEGL', -- This alias is used in sub query.
1657 p_mode => 'WHERE',
1658 p_qualifier => 'ALL',
1659 p_function => '>=',
1660 p_operand1 => var.start_account);
1664 p_id_flex_code => 'GL#',
1661 else
1662 account_where := ' AND '|| FA_RX_FLEX_PKG.FLEX_SQL(
1663 p_application_id => 101,
1665 p_id_flex_num => var.chart_of_accounts_id,
1666 p_table_alias => 'LINEGL', -- This alias is used in sub query.
1667 p_mode => 'WHERE',
1668 p_qualifier => 'ALL',
1669 p_function => 'BETWEEN',
1670 p_operand1 => var.start_account,
1671 p_operand2 => var.end_account);
1672 end if;
1673 end if;
1674
1675 -- Amount parameters
1676 -- This parameters depend on a line_invoice parameter.
1677 -- When parameter is 'LINE', this amount parameter will be used to select only lines matched with the condition,
1678 -- so where clause uses CTL, which is used in main select statement, as an alias.
1679 -- When parameter is 'INVOICE', this amount parameter will be used to select lines matched with the condition
1680 -- in a sub query statement. That sub query will return customer_trx_ids which identify the invoice
1681 -- information. So where clause uses LINE, which is used in sub query statement, as an alias.
1682 --
1683 if var.line_invoice = 'LINE' then
1684 if (to_char(var.start_amount) is NULL) and (to_char(var.end_amount) is NULL) then
1685 amount_where := NULL;
1686 elsif to_char(var.start_amount) is NULL then
1687 amount_where := ' AND DIST.ACCTD_AMOUNT :end_amount ';
1688 elsif to_char(var.end_amount) is NULL then
1689 amount_where := ' AND DIST.ACCTD_AMOUNT >= :start_amount ';
1690 else
1691 amount_where := ' AND DIST.ACCTD_AMOUNT between :start_amount and :end_amount ';
1692 end if;
1693 else
1694 if (to_char(var.start_amount) is NULL) and (to_char(var.end_amount) is NULL) then
1695 amount_where := NULL;
1696 elsif to_char(var.start_amount) is NULL then
1697 amount_where := ' AND LINEDIST.ACCTD_AMOUNT <= :end_amount ';
1698 elsif to_char(var.end_amount) is NULL then
1699 amount_where := ' AND LINEDIST.ACCTD_AMOUNT >= :start_amount ';
1700 else
1701 amount_where := ' AND LINEDIST.ACCTD_AMOUNT between :start_amount and :end_amount ';
1702 end if;
1703 end if;
1704
1705 --
1706 -- Assign another column specific to this report
1707 fa_rx_util_pkg.debug('AR_ADD_SELECT_COLUMNS');
1708
1709 fa_rx_util_pkg.assign_column('c1 ','CCDIST.CODE_COMBINATION_ID', null, 'arrx_tx.var.ccid2', 'NUMBER');
1710 fa_rx_util_pkg.assign_column('c2 ','CTL.CUSTOMER_TRX_LINE_ID', 'CUSTOMER_TRX_LINE_ID', 'arrx_tx.var.customer_trx_line_id', 'NUMBER');
1711 fa_rx_util_pkg.assign_column('c3 ','CTL.LINK_TO_CUST_TRX_LINE_ID', 'LINK_TO_CUST_TRX_LINE_ID', 'arrx_tx.var.link_to_cust_trx_line_id', 'NUMBER');
1712 fa_rx_util_pkg.assign_column('c4 ',decode_inv, 'INVENTORY_ITEM', 'arrx_tx.var.inventory_item', 'VARCHAR2',240);
1713 fa_rx_util_pkg.assign_column('c5 ','DIST.CUST_TRX_LINE_GL_DIST_ID', 'CUST_TRX_LINE_GL_DIST_ID', 'arrx_tx.var.cust_trx_line_gl_dist_id', 'NUMBER');
1714 fa_rx_util_pkg.assign_column('c6 ',CC_ACCOUNT_SEL, 'ACCOUNT', 'arrx_tx.var.account', 'VARCHAR2',240);
1715 fa_rx_util_pkg.assign_column('c7 ',null, 'ACCOUNT_DESC', 'arrx_tx.var.account_desc', 'VARCHAR2',240);
1716 fa_rx_util_pkg.assign_column('c8 ',CC_BALANCE_SEL, 'BALANCE', 'arrx_tx.var.balance', 'VARCHAR2',240);
1717 fa_rx_util_pkg.assign_column('c9 ',null, 'BALANCE_DESC', 'arrx_tx.var.balance_desc', 'VARCHAR2',240);
1718 fa_rx_util_pkg.assign_column('c10',CC_NATURAL_SEL, 'NATACCT', 'arrx_tx.var.natacct', 'VARCHAR2',240);
1719 fa_rx_util_pkg.assign_column('c11',null, 'NATACCT_DESC', 'arrx_tx.var.natacct_desc', 'VARCHAR2',240);
1720 fa_rx_util_pkg.assign_column('c12','ITEM.DESCRIPTION', 'ITEM_DESCRIPTION', 'arrx_tx.var.item_description', 'VARCHAR2',240);
1721
1722 --
1723 -- Add to the FROM clause specific to this report
1724 fa_rx_util_pkg.debug('AR_ADD_FORM_CLAUSE');
1725 fa_rx_util_pkg.From_Clause :=
1726 fa_rx_util_pkg.From_Clause || ',
1727 RA_CUSTOMER_TRX_LINES CTL,
1728 RA_CUST_TRX_LINE_GL_DIST DIST,
1729 MTL_SYSTEM_ITEMS ITEM,
1730 AR_MEMO_LINES MEMO,
1731 GL_CODE_COMBINATIONS CCDIST';
1732
1733 --
1734 -- Add to the WHERE clause specific to this report
1735 fa_rx_util_pkg.debug('AR_ADD_WHERE_CLAUSE');
1736
1737 if var.line_invoice = 'LINE' then
1738 fa_rx_util_pkg.Where_Clause :=
1739 fa_rx_util_pkg.Where_Clause || '
1740 AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID'||
1741 transaction_number_where ||
1742 natural_account_where ||
1743 account_where ||'
1744 AND ITEM.ORGANIZATION_ID(+) = '|| var.so_organization_id ||'
1745 AND CTL.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID(+)
1746 AND CTL.MEMO_LINE_ID = MEMO.MEMO_LINE_ID(+)
1747 AND CT.CUSTOMER_TRX_ID = DIST.CUSTOMER_TRX_ID
1748 AND CTL.CUSTOMER_TRX_LINE_ID = DIST.CUSTOMER_TRX_LINE_ID'||
1749 amount_where ||'
1753 -- create sub-query to select customer transaction id to pickup invoice information
1750 AND DIST.ACCOUNT_SET_FLAG = ''N''
1751 AND DIST.CODE_COMBINATION_ID = CCDIST.CODE_COMBINATION_ID ';
1752 else
1754 -- which includes lines matched with specified line parameters.
1755
1756 --Bug:3825294
1757 if (account_where is not null or amount_where is not null)
1758 then
1759 line_select_statement :=
1760 ' AND CT.CUSTOMER_TRX_ID in '||
1761 '(select distinct line.customer_trx_id
1762 from ra_cust_trx_line_gl_dist linedist,
1763 ra_customer_trx_lines line,
1764 gl_code_combinations linegl
1765 where linedist.account_class <> ''REC''
1766 and linedist.customer_trx_line_id = line.customer_trx_line_id
1767 and linedist.code_combination_id = linegl.code_combination_id
1768 and linedist.account_set_flag = ''N'''||
1769 account_where ||
1770 amount_where ||')';
1771 else
1772 line_select_statement := null;
1773 end if;
1774
1775 fa_rx_util_pkg.Where_Clause :=
1776 fa_rx_util_pkg.Where_Clause ||
1777 line_select_statement ||
1778 transaction_number_where ||
1779 natural_account_where ||'
1780 AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
1781 AND ITEM.ORGANIZATION_ID(+) = '|| var.so_organization_id ||'
1782 AND CTL.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID(+)
1783 AND CTL.MEMO_LINE_ID = MEMO.MEMO_LINE_ID(+)
1784 AND CT.CUSTOMER_TRX_ID = DIST.CUSTOMER_TRX_ID
1785 AND CTL.CUSTOMER_TRX_LINE_ID = DIST.CUSTOMER_TRX_LINE_ID
1786 AND DIST.ACCOUNT_SET_FLAG = ''N''
1787 AND DIST.CODE_COMBINATION_ID = CCDIST.CODE_COMBINATION_ID ';
1788 end if;
1789
1790 fa_rx_util_pkg.debug('arrx_tx.sales_before_report()-');
1791
1792 end sales_before_report;
1793
1794 procedure sales_bind(c in integer)
1795 is
1796 begin
1797 fa_rx_util_pkg.debug('AR_GET_BIND');
1798 --
1799 -- These bind variables(Date Type) were included in the WHERE clause
1800 --
1801 IF var.start_invoice_num IS NOT NULL THEN
1802 dbms_sql.bind_variable(c, 'start_invoice_num', var.start_invoice_num);
1803 END IF;
1804 IF var.end_invoice_num IS NOT NULL THEN
1805 dbms_sql.bind_variable(c, 'end_invoice_num', var.end_invoice_num);
1806 END IF;
1807 IF var.start_amount IS NOT NULL THEN
1808 dbms_sql.bind_variable(c, 'start_amount', var.start_amount);
1809 END IF;
1810 IF var.end_amount IS NOT NULL THEN
1811 dbms_sql.bind_variable(c, 'end_amount', var.end_amount);
1812 END IF;
1813
1814 end sales_bind;
1815
1816 --
1817 -- This is the after fetch trigger for the main artx_rep report
1818 procedure sales_after_fetch
1819 is
1820 begin
1821
1822 --
1823 -- Get FLEX FIELD VALUE and DESCRIPTION
1824 --
1825 fa_rx_util_pkg.debug('AR_GET_FLEX_KEYWORD');
1826
1827 var.account_desc := substrb(fa_rx_flex_pkg.get_description(
1828 p_application_id => 101,
1829 p_id_flex_code => 'GL#',
1830 p_id_flex_num => var.chart_of_accounts_id,
1831 p_qualifier => 'ALL',
1832 p_data => var.account), 1, 240);
1833
1834 var.balance_desc := substrb(fa_rx_flex_pkg.get_description(
1835 p_application_id => 101,
1836 p_id_flex_code => 'GL#',
1837 p_id_flex_num => var.chart_of_accounts_id,
1838 p_qualifier => 'GL_BALANCING',
1839 p_data => var.balance), 1, 240);
1840
1841 var.natacct_desc := substrb(fa_rx_flex_pkg.get_description(
1842 p_application_id => 101,
1843 p_id_flex_code => 'GL#',
1844 p_id_flex_num => var.chart_of_accounts_id,
1845 p_qualifier => 'GL_ACCOUNT',
1846 p_data => var.natacct), 1, 240);
1847
1848 end sales_after_fetch;
1849
1850
1851 Function GET_CONS_BILL_NUMBER(P_CUSTOMER_TRX_ID in number)
1852 return VARCHAR2
1853 is
1854 CURSOR CONS_BILL(CTID IN NUMBER) IS
1855 SELECT CONS_INV.CONS_BILLING_NUMBER
1856 FROM AR_PAYMENT_SCHEDULES PS, AR_CONS_INV CONS_INV
1857 WHERE PS.CONS_INV_ID = CONS_INV.CONS_INV_ID
1858 AND PS.STATUS = 'OP'
1859 AND PS.CUSTOMER_TRX_ID = CTID;
1860
1861 L_CONS_BILL_NUMBER varchar2(30);
1862 begin
1863 OPEN CONS_BILL(P_CUSTOMER_TRX_ID);
1864 FETCH CONS_BILL INTO L_CONS_BILL_NUMBER;
1865 IF CONS_BILL%NOTFOUND THEN
1866 L_CONS_BILL_NUMBER := NULL;
1867 END IF;
1868 CLOSE CONS_BILL;
1869
1870 RETURN L_CONS_BILL_NUMBER;
1871 end GET_CONS_BILL_NUMBER;
1872
1873 procedure GET_LAST_UPDATE(P_CUSTOMER_TRX_ID in number)
1874 is
1875 cursor H is
1879 order by last_update_date desc;
1876 select last_update_date,last_updated_by
1877 from ra_customer_trx
1878 where customer_trx_id = P_CUSTOMER_TRX_ID
1880
1881 cursor L is
1882 select last_update_date,last_updated_by
1883 from ra_customer_trx_lines
1884 where customer_trx_id = P_CUSTOMER_TRX_ID
1885 order by last_update_date desc;
1886
1887 cursor D is
1888 select last_update_date,last_updated_by
1889 from ra_cust_trx_line_gl_dist
1890 where customer_trx_id = P_CUSTOMER_TRX_ID
1891 and ((account_class = 'REC' and latest_rec_flag = 'Y')
1892 or (account_class <> 'REC' and account_set_flag = 'N'))
1893 order by last_update_date desc;
1894
1895 HEADER_DATE date;
1896 HEADER_BY number;
1897 LINE_DATE date;
1898 LINE_BY number;
1899 DIST_DATE date;
1900 DIST_BY number;
1901 begin
1902 var.ctid := P_CUSTOMER_TRX_ID;
1903
1904 OPEN H;
1905 FETCH H INTO HEADER_DATE, HEADER_BY;
1906 CLOSE H;
1907
1908 OPEN L;
1909 FETCH L INTO LINE_DATE, LINE_BY;
1910 CLOSE L;
1911
1912 OPEN D;
1913 FETCH D INTO DIST_DATE, DIST_BY;
1914 CLOSE D;
1915
1916 IF HEADER_DATE > NVL(LINE_DATE,HEADER_DATE-1) AND HEADER_DATE > NVL(DIST_DATE,HEADER_DATE-1) THEN
1917 var.update_date := HEADER_DATE;
1918 var.user_id := HEADER_BY;
1919 ELSIF LINE_DATE > NVL(HEADER_DATE,LINE_DATE-1) AND LINE_DATE > NVL(DIST_DATE,LINE_DATE-1) THEN
1920 var.update_date := LINE_DATE;
1921 var.user_id := LINE_BY;
1922 ELSE
1923 var.update_date := DIST_DATE;
1924 var.user_id := DIST_BY;
1925 END IF;
1926
1927 EXCEPTION
1928 WHEN OTHERS THEN
1929 var.user_id := -1;
1930 END GET_LAST_UPDATE;
1931
1932
1933 function LAST_UPDATED_BY(P_CUSTOMER_TRX_ID in number)
1934 return number
1935 is
1936 begin
1937 IF P_CUSTOMER_TRX_ID <> var.ctid THEN
1938 GET_LAST_UPDATE(P_CUSTOMER_TRX_ID);
1939 END IF;
1940
1941 return var.user_id;
1942 end LAST_UPDATED_BY;
1943
1944
1945 function LAST_UPDATE_DATE(P_CUSTOMER_TRX_ID in number)
1946 return date
1947 is
1948 begin
1949 IF P_CUSTOMER_TRX_ID <> var.ctid THEN
1950 GET_LAST_UPDATE(P_CUSTOMER_TRX_ID);
1951 END IF;
1952
1953 return var.update_date;
1954 end LAST_UPDATE_DATE;
1955
1956
1957 function WHERE_LAST_UPDATE(P_CUSTOMER_TRX_ID in number, P_LAST_UPDATED_BY in number, P_START_UPDATE_DATE in date, P_END_UPDATE_DATE in date)
1958 return varchar2
1959 is
1960 begin
1961 IF P_CUSTOMER_TRX_ID <> var.ctid THEN
1962 GET_LAST_UPDATE(P_CUSTOMER_TRX_ID);
1963 END IF;
1964
1965 IF var.update_date BETWEEN P_START_UPDATE_DATE AND P_END_UPDATE_DATE THEN
1966 IF P_LAST_UPDATED_BY IS NULL THEN
1967 return 'Y';
1968 ELSIF P_LAST_UPDATED_BY = var.user_id THEN
1969 return 'Y';
1970 END IF;
1971 END IF;
1972
1973 return 'N';
1974 end WHERE_LAST_UPDATE;
1975
1976 end ARRX_TX;