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