[Home] [Help]
PACKAGE BODY: APPS.AR_RAXSKI_XMLP_PKG
Source
1 PACKAGE BODY AR_RAXSKI_XMLP_PKG AS
2 /* $Header: RAXSKIB.pls 120.0 2007/12/27 14:33:11 abraghun noship $ */
3
4 function report_nameformula(Company_Name in varchar2) return varchar2 is
5 begin
6
7 DECLARE
8 l_report_name VARCHAR2(80);
9 BEGIN
10 RP_Company_Name := Company_Name;
11 SELECT SUBSTR(cp.user_concurrent_program_name, 1, 80)
12 INTO l_report_name
13 FROM FND_CONCURRENT_PROGRAMS_VL cp,
14 FND_CONCURRENT_REQUESTS cr
15 WHERE cr.request_id = P_CONC_REQUEST_ID
16 AND cp.application_id = cr.program_application_id
17 AND cp.concurrent_program_id = cr.concurrent_program_id;
18
19 RP_Report_Name := l_report_name;
20 RP_Report_Name := substr(RP_Report_Name,1,instr(RP_Report_Name,' (XML)'));
21
22 RETURN(l_report_name);
23 EXCEPTION
24 WHEN NO_DATA_FOUND
25 THEN RP_REPORT_NAME := 'Key Indicators for Revenue Accounting';
26 RETURN('Key Indicators for Revenue Accounting');
27 END;
28 RETURN NULL; end;
29
30 function BeforeReport return boolean is
31 begin
32 begin
33
34
35 null;
36
37
38 end;
39 return (TRUE);
40 end;
41 function AfterPForm return boolean is
42 begin
43 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
44
45 Begin
46 SELECT
47 TO_DATE(ARGUMENT3,'yyyy/mm/dd hh24:mi:ss'),TO_DATE(ARGUMENT4,'yyyy/mm/dd hh24:mi:ss') ,
48 TO_DATE(ARGUMENT5,'yyyy/mm/dd hh24:mi:ss'),TO_DATE(ARGUMENT6,'yyyy/mm/dd hh24:mi:ss')
49 INTO P_START_DATE,P_END_DATE,P_PRIOR_START_DATE,P_PRIOR_END_DATE
50 FROM FND_CONCURRENT_REQUESTS
51 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
52 EXCEPTION WHEN OTHERS THEN
53 RETURN NULL;
54 end;
55
56 /*SRW.USER_EXIT('FND SRWINIT');*/null;
57 IF p_start_date IS NULL THEN
58 p_start_date := to_date('01-01-1953','DD-MM-YYYY');
59 END IF;
60 IF p_prior_start_date IS NULL THEN
61 p_prior_start_date := to_date('01-01-1953','DD-MM-YYYY');
62 END IF;
63 IF p_end_date IS NULL THEN
64 p_end_date := to_date('31-12-4712','DD-MM-YYYY');
65 END IF;
66 IF p_prior_end_date IS NULL THEN
67 p_prior_end_date := to_date('31-12-4712','DD-MM-YYYY');
68 END IF;
69
70 if p_start_currency_code is not null then
71 lp_start_currency_code := ' and currency_code >= :p_start_currency_code ';
72 end if;
73
74 if p_end_currency_code is not null then
75 lp_end_currency_code := ' and currency_code <= :p_end_currency_code ';
76 end if;
77
78 if p_prior_start_date <> to_date('01-01-1953','DD-MM-YYYY')
79 AND p_start_date <> to_date('01-01-1953','DD-MM-YYYY') then
80 lp_start_date := ' and trx_date >= least(:p_prior_start_date,
81 :p_start_date) ';
82 end if;
83
84 if p_prior_end_date <> to_date('31-12-4712','DD-MM-YYYY')
85 AND p_end_date <> to_date('31-12-4712','DD-MM-YYYY') then
86 lp_end_date := ' and trx_date <= greatest(:p_prior_end_date,
87 :p_end_date) ';
88 end if;
89
90 return (TRUE);
91 end;
92
93 function Sub_TitleFormula return VARCHAR2 is
94 begin
95
96 begin
97 RP_SUB_TITLE := ' ';
98 return(' ');
99 end;
100
101 RETURN NULL; end;
102
103 function Sel_CustomersFormula return Number is
104 begin
105
106 declare
107 /*v_customers number(10);
108 prior_v_customers number(10);
109 cust_total_number number(10);
110 prior_v_inactive_count number(10);
111 v_inactive_count number(10);
112 v_addresses number(10);
113 prior_v_addresses number(10);
114 address_total_number number(10);
115 inactive_count_total_number number(10);
116 customer_change VARCHAR2(10);
117 address_change VARCHAR2(10);
118 inactive_count_change VARCHAR2(10);*/
119 v_customers_t number(10);
120 prior_v_customers_t number(10);
121 cust_total_number_t number(10);
122 prior_v_inactive_count_t number(10);
123 v_inactive_count_t number(10);
124 v_addresses_t number(10);
125 prior_v_addresses_t number(10);
126 address_total_number_t number(10);
127 inactive_count_total_number_t number(10);
128 customer_change_t VARCHAR2(10);
129 address_change_t VARCHAR2(10);
130 inactive_count_change_t VARCHAR2(10);
131 begin
132 select count(*)
133 into v_customers_t
134 from hz_cust_accounts
135 where creation_date between p_start_date
136 and p_end_date + (86399/86400);
137
138
139 select count(*)
140 into prior_v_customers_t
141 from hz_cust_accounts
142 where creation_date between p_prior_start_date
143 and p_prior_end_date + (86399/86400);
144
145
146 select count(*),
147 NVL(sum(decode(status,'I',1,0)),0)
148 into cust_total_number_t,
149 inactive_count_total_number_t
150 from hz_cust_accounts;
151
152
153 select NVL(sum(decode(status,'I',1,0)),0)
154 into prior_v_inactive_count_t
155 from hz_cust_accounts
156 where last_update_date between
157 p_prior_start_date
158 and p_prior_end_date + (86399/86400);
159
160
161 select NVL(sum(decode(status,'I',1,0)),0)
162 into v_inactive_count_t
163 from hz_cust_accounts
164 where last_update_date between p_start_date
165 and p_end_date + (86399/86400);
166
167
168
169 select count(*)
170 into v_addresses_t
171 from hz_cust_acct_sites acct_site,
172 hz_party_sites party_site,
173 hz_loc_assignments loc_assign
174 where acct_site.party_site_id = party_site.party_site_id
175 and party_site.location_id = loc_assign.location_id
176 and nvl(acct_site.org_id, -99) = nvl(loc_assign.org_id,-99)
177 and acct_site.creation_date between p_start_date
178 and p_end_date + (86399/86400);
179
180
181 select count(*)
182 into prior_v_addresses_t
183 from hz_cust_acct_sites acct_site,
184 hz_party_sites party_site,
185 hz_loc_assignments loc_assign
186 where acct_site.party_site_id = party_site.party_site_id
187 and party_site.location_id = loc_assign.location_id
188 and nvl(acct_site.org_id, -99) = nvl(loc_assign.org_id,-99)
189 and acct_site.creation_date between p_prior_start_date
190 and p_prior_end_date + (86399/86400);
191
192
193 select count(*)
194 into address_total_number_t
195 from hz_cust_acct_sites acct_site,
196 hz_party_sites party_site,
197 hz_loc_assignments loc_assign
198 where acct_site.party_site_id = party_site.party_site_id
199 and party_site.location_id = loc_assign.location_id
200 and nvl(acct_site.org_id, -99) = nvl(loc_assign.org_id,-99);
201
202
203 /*v_customers := v_customers;
204 prior_v_customers := prior_v_customers;
205 cust_total_number := cust_total_number;
206 prior_v_inactive_count := prior_v_inactive_count;
207 v_inactive_count := v_inactive_count;
208 v_addresses := v_addresses;
209 prior_v_addresses := prior_v_addresses;
210 address_total_number := address_total_number;
211 inactive_count_total_number := inactive_count_total_number;*/
212 v_customers := v_customers_t;
213 prior_v_customers := prior_v_customers_t;
214 cust_total_number := cust_total_number_t;
215 prior_v_inactive_count := prior_v_inactive_count_t;
216 v_inactive_count := v_inactive_count_t;
217 v_addresses := v_addresses_t;
218 prior_v_addresses := prior_v_addresses_t;
219 address_total_number := address_total_number_t;
220 inactive_count_total_number := inactive_count_total_number_t;
221
222 if (prior_v_customers <> 0 ) then
223 customer_change := to_char(round(((v_customers - prior_v_customers)/prior_v_customers * 100),2),'999D99');
224 else
225 customer_change := TO_CHAR(0, '0D00');
226 end if;
227
228 if (prior_v_addresses <> 0 ) then
229 address_change := to_char(round(((v_addresses - prior_v_addresses)/prior_v_addresses * 100),2),'999D99') ;
230 else
231 address_change := TO_CHAR(0, '0D00');
232 end if;
233
234 if (prior_v_inactive_count <> 0 ) then
235 inactive_count_change := to_char(round(((v_inactive_count - prior_v_inactive_count)/prior_v_inactive_count * 100),2),'999D99') ;
236 else
237 inactive_count_change := TO_CHAR(0, '0D00') ;
238 end if;
239
240 return(1);
241
242 exception
243 when NO_DATA_FOUND then
244 return(0);
245 end;
246 RETURN NULL; end;
247
248 --function sel_invoicesformula(inv_type in number, invoice_currency_code in varchar2, inv_sum in number) return number is
249 function sel_invoicesformula(inv_type in number, invoice_currency_code_t in varchar2, inv_sum in number) return number is
250 begin
251
252 declare
253 -- current_inv_sum number(38,2);
254 -- current_inv_period number(10);
255
256
257
258 current_inv_sum1 number(38,2);
259 current_inv_period1 number(10);
260 current_inv_sum2 number(38,2);
261 current_inv_period2 number(10);
262
263 -- prior_inv_sum number(38,2);
264 -- prior_inv_period number(10);
265
266
267
268 prior_inv_sum1 number(38,2);
269 prior_inv_period1 number(10);
270 prior_inv_sum2 number(38,2);
271 prior_inv_period2 number(10);
272
273 -- inv_sum_tf number(38,2);
274
275 current_inv_sum_t number(38,2);
276 current_inv_period_t number(10);
277 prior_inv_sum_t number(38,2);
278 prior_inv_period_t number(10);
279 inv_sum_tf_t number(38,2);
280
281
282 begin
283
284 /*srw.reference(invoice_currency_code);*/null;
285
286 /*srw.reference(inv_type);*/null;
287
288 /*srw.reference(inv_sum);*/null;
289
290
291
292
293
294 IF p_start_date = to_date('01-01-1953','DD-MM-YYYY')
295 AND p_end_date = to_date('31-12-4712','DD-MM-YYYY') THEN
296
297 select sum(nvl(b.acctd_amount,0)),
298 NVL( count( distinct ( a.customer_trx_id )), 0 )
299 into current_inv_sum_t, current_inv_period_t
300 from ra_cust_trx_line_gl_dist b,
301 ra_customer_trx a,
302 ra_cust_trx_types c
303 where complete_flag = 'Y'
304 and c.type in ('INV','DM','DEP','CB')
305 and a.cust_trx_type_id = inv_type
306 and a.customer_trx_id = b.customer_trx_id
307 and b.account_class = 'REC'
308 and b.latest_rec_flag = 'Y'
309 and a.cust_trx_type_id = c.cust_trx_type_id
310 and nvl(b.gl_date,a.trx_date) between p_start_date
311 and p_end_date + (86399/86400)
312 -- and a.invoice_currency_code = invoice_currency_code;
313 and a.invoice_currency_code = invoice_currency_code_t;
314
315 ELSE
316
317
318
319 select sum(nvl(b.acctd_amount,0)) amount,
320 nvl(count(distinct(a.customer_trx_id)),0) trx_id
321 into current_inv_sum1, current_inv_period1
322 from ra_cust_trx_line_gl_dist b,
323 ra_customer_trx a,
324 ra_cust_trx_types c
325 where complete_flag = 'Y'
326 and c.type in ('INV','DM','DEP','CB')
327 and a.cust_trx_type_id = inv_type
328 and a.customer_trx_id = b.customer_trx_id
329 and b.account_class = 'REC'
330 and b.latest_rec_flag = 'Y'
331 and a.cust_trx_type_id = c.cust_trx_type_id
332 and b.gl_date is not null
333 and b.gl_date between p_start_date
334 and p_end_date + (86399/86400)
335 --and a.invoice_currency_code = invoice_currency_code ;
336 and a.invoice_currency_code = invoice_currency_code_t ;
337
338
339 select sum(nvl(b.acctd_amount,0)) amount,
340 nvl(count(distinct(a.customer_trx_id)),0) trx_id
341 into current_inv_sum2, current_inv_period2
342 from ra_cust_trx_line_gl_dist b,
343 ra_customer_trx a,
344 ra_cust_trx_types c
345 where complete_flag = 'Y'
346 and c.type in ('INV','DM','DEP','CB')
347 and a.cust_trx_type_id = inv_type
348 and a.customer_trx_id = b.customer_trx_id
349 and b.account_class = 'REC'
350 and b.latest_rec_flag = 'Y'
351 and a.cust_trx_type_id = c.cust_trx_type_id
352 and b.gl_date is null
353 and a.trx_date between p_start_date
354 and p_end_date + (86399/86400)
355 --and a.invoice_currency_code = invoice_currency_code ;
356 and a.invoice_currency_code = invoice_currency_code_t ;
357
358 current_inv_sum_t := nvl(current_inv_sum1,0) + nvl(current_inv_sum2,0) ;
359 current_inv_period_t := nvl(current_inv_period1,0) + nvl(current_inv_period2,0);
360
361
365 END IF;
362
363 IF current_inv_sum1 IS NULL and current_inv_sum2 is null THEN
364 current_inv_sum := null;
366
367 END IF;
368
369
370 IF p_prior_start_date = to_date('01-01-1953','DD-MM-YYYY')
371 AND p_prior_end_date = to_date('31-12-4712','DD-MM-YYYY') THEN
372
373
374 select sum(nvl(b.acctd_amount,0)),
375 NVL( count( distinct ( a.customer_trx_id )), 0 )
376 into prior_inv_sum_t, prior_inv_period_t
377 from ra_cust_trx_line_gl_dist b,
378 ra_customer_trx a,
379 ra_cust_trx_types c
380 where complete_flag = 'Y'
381 and c.type in ('INV','DM','DEP','CB')
382 and a.cust_trx_type_id = inv_type
383 and a.customer_trx_id = b.customer_trx_id
384 and b.account_class = 'REC'
385 and b.latest_rec_flag = 'Y'
386 and a.cust_trx_type_id = c.cust_trx_type_id
387 and nvl(b.gl_date,a.trx_date) between p_prior_start_date
388 and p_prior_end_date + (86399/86400)
389 --and a.invoice_currency_code = invoice_currency_code;
390 and a.invoice_currency_code = invoice_currency_code_t;
391
392 ELSE
393
394
395 select sum(nvl(b.acctd_amount,0)) amount,
396 nvl(count(distinct(a.customer_trx_id)),0) trx_id
397 into prior_inv_sum1, prior_inv_period1
398 from ra_cust_trx_line_gl_dist b,
399 ra_customer_trx a,
400 ra_cust_trx_types c
401 where complete_flag = 'Y'
402 and c.type in ('INV','DM','DEP','CB')
403 and a.cust_trx_type_id = inv_type
404 and a.customer_trx_id = b.customer_trx_id
405 and b.account_class = 'REC'
406 and b.latest_rec_flag = 'Y'
407 and a.cust_trx_type_id = c.cust_trx_type_id
408 and b.gl_date is not null
409 and b.gl_date between p_prior_start_date
410 and p_prior_end_date + (86399/86400)
411 --and a.invoice_currency_code = invoice_currency_code ;
412 and a.invoice_currency_code = invoice_currency_code_t ;
413
414
415 select sum(nvl(b.acctd_amount,0)) amount,
416 nvl(count(distinct(a.customer_trx_id)),0) trx_id
417 into prior_inv_sum2, prior_inv_period2
418 from ra_cust_trx_line_gl_dist b,
419 ra_customer_trx a,
420 ra_cust_trx_types c
421 where complete_flag = 'Y'
422 and c.type in ('INV','DM','DEP','CB')
423 and a.cust_trx_type_id = inv_type
424 and a.customer_trx_id = b.customer_trx_id
425 and b.account_class = 'REC'
426 and b.latest_rec_flag = 'Y'
427 and a.cust_trx_type_id = c.cust_trx_type_id
428 and b.gl_date is null
429 and a.trx_date between p_prior_start_date
430 and p_prior_end_date + (86399/86400)
431 --and a.invoice_currency_code = invoice_currency_code ;
432 and a.invoice_currency_code = invoice_currency_code_t ;
433
434 prior_inv_sum_t := nvl(prior_inv_sum1,0) + nvl(prior_inv_sum2,0);
435 prior_inv_period_t := nvl(prior_inv_period1,0) + nvl(prior_inv_period2,0) ;
436
437
438
439 IF prior_inv_sum1 is null and prior_inv_sum2 is null THEN
440 prior_inv_sum_t := null;
441 END IF;
442
443
444
445 END IF;
446
447 /*current_inv_period := current_inv_period;
448 prior_inv_period := prior_inv_period;*/
449 current_inv_period := current_inv_period_t;
450 prior_inv_period := prior_inv_period_t;
451
452 if prior_inv_period <> 0 then
453 p_percent_change := round(((current_inv_period - prior_inv_period)/prior_inv_period * 100),2);
454 else
455 p_percent_change := 0.00;
456 end if;
457
458 -- current_inv_sum := current_inv_sum;
459 -- prior_inv_sum := prior_inv_sum;
460 current_inv_sum := current_inv_sum_t;
461 prior_inv_sum := prior_inv_sum_t;
462
463
464 if prior_inv_sum <> 0 then
465 a_percent_change := round(((current_inv_sum - prior_inv_sum)/prior_inv_sum * 100),2);
466 else
467 a_percent_change := 0.00;
468 end if;
469
470 inv_sum_tf_t := inv_sum;
471 inv_sum_tf := inv_sum_tf_t;
472 /* inv_sum_tf := inv_sum;
473 inv_sum_tf := inv_sum_tf;*/
474
475
476 return(1);
477
478 exception
479 when NO_DATA_FOUND then
480 return(0);
481 end;
482
483 RETURN NULL; end;
484
485 --function sel_trxformula(reason in varchar2, invoice_currency_code in varchar2, all_sum in number) return number is
486 function sel_trxformula(reason in varchar2, invoice_currency_code_t in varchar2, all_sum_t in number) return number is
487 begin
488
489 declare
490
491 --current_period number(10);
492 --current_sum number(38,2);
493
494 --prior_period number(10);
495
496 --prior_sum number(38,2);
497
498 --all_sum_ctf number(38,2);
499 /* added as fix*/
500 current_period_t number(10);
501 current_sum_t number(38,2);
502 prior_period_t number(10);
503 prior_sum_t number(38,2);
504 all_sum_ctf_t number(38,2);
505 /* fix ends */
506
507 current_period1 number(10);
508 current_sum1 number(38,2);
509 current_period2 number(10);
510 current_sum2 number(38,2);
511
512 prior_period1 number(10);
513 prior_sum1 number(38,2);
514 prior_period2 number(10);
518 begin
515 prior_sum2 number(38,2);
516
517
519
520
521
522
523 IF p_start_date = to_date('01-01-1953','DD-MM-YYYY')
524 AND p_end_date = to_date('31-12-4712','DD-MM-YYYY') THEN
525
526 select NVL( count( distinct( a.customer_trx_id )), 0),
527 sum(gld.acctd_amount)
528 --into current_period, current_sum
529 into current_period_t, current_sum_t
530 from ra_cust_trx_line_gl_dist gld,
531 ra_customer_trx a,
532 ra_cust_trx_types c
533 where complete_flag = 'Y'
534 and c.type = 'CM'
535 and nvl(a.reason_code,'0') = reason
536 and a.customer_trx_id = gld.customer_trx_id
537 and gld.account_class = 'REC'
538 and gld.latest_rec_flag = 'Y'
539 and a.cust_trx_type_id = c.cust_trx_type_id
540 and nvl(gld.gl_date,a.trx_date) between p_start_date
541 and p_end_date + (86399/86400)
542 --and a.invoice_currency_code = invoice_currency_code;
543 and a.invoice_currency_code = invoice_currency_code_t;
544
545 ELSE
546
547
548 select nvl(count( distinct (a.customer_trx_id)),0) cust_trx_id,
549 sum(nvl(gld.acctd_amount,0)) amount
550 into current_period1, current_sum1
551 from ra_cust_trx_line_gl_dist gld,
552 ra_customer_trx a,
553 ra_cust_trx_types c
554 where complete_flag = 'Y'
555 and c.type = 'CM'
556 and nvl(a.reason_code,'0') = reason
557 and a.customer_trx_id = gld.customer_trx_id
558 and gld.account_class = 'REC'
559 and gld.latest_rec_flag = 'Y'
560 and a.cust_trx_type_id = c.cust_trx_type_id
561 and gld.gl_date is not null
562 and gld.gl_date between p_start_date
563 and p_end_date + (86399/86400)
564 --and a.invoice_currency_code = invoice_currency_code ;
565 and a.invoice_currency_code = invoice_currency_code_t ;
566
567
568 select nvl(count( distinct (a.customer_trx_id)),0) cust_trx_id,
569 sum(nvl(gld.acctd_amount,0)) amount
570 into current_period2, current_sum2
571 from ra_cust_trx_line_gl_dist gld,
572 ra_customer_trx a,
573 ra_cust_trx_types c
574 where complete_flag = 'Y'
575 and c.type = 'CM'
576 and nvl(a.reason_code,'0') = reason
577 and a.customer_trx_id = gld.customer_trx_id
578 and gld.account_class = 'REC'
579 and gld.latest_rec_flag = 'Y'
580 and a.cust_trx_type_id = c.cust_trx_type_id
581 and gld.gl_date is null
582 and a.trx_date between p_start_date
583 and p_end_date + (86399/86400)
584 --and a.invoice_currency_code = invoice_currency_code ;
585 and a.invoice_currency_code = invoice_currency_code_t ;
586
587 current_period_t := nvl(current_period1,0) + nvl(current_period2,0) ;
588 current_sum_t := nvl(current_sum1,0) + nvl(current_sum2 ,0);
589
590
591
592 IF current_sum1 is null and current_sum2 is null THEN
593 current_sum_t := null;
594 END IF;
595 END IF;
596
597
598
599
600 IF p_prior_start_date = to_date('01-01-1953','DD-MM-YYYY')
601 AND p_prior_end_date = to_date('31-12-4712','DD-MM-YYYY') THEN
602
603 select NVL( count( distinct( a.customer_trx_id )), 0),
604 sum(gld.acctd_amount)
605 --into prior_period, prior_sum
606 into prior_period_t, prior_sum_t
607 from ra_cust_trx_line_gl_dist gld,
608 ra_customer_trx a,
609 ra_cust_trx_types c
610 where complete_flag = 'Y'
611 and c.type = 'CM'
612 and nvl(a.reason_code,'0') = reason
613 and a.customer_trx_id = gld.customer_trx_id
614 and gld.account_class = 'REC'
615 and gld.latest_rec_flag = 'Y'
616 and a.cust_trx_type_id = c.cust_trx_type_id
617 and nvl(gld.gl_date,a.trx_date) between p_prior_start_date
618 and p_prior_end_date + (86399/86400)
619 --and a.invoice_currency_code = invoice_currency_code;
620 and a.invoice_currency_code = invoice_currency_code_t;
621
622 ELSE
623
624
625
626 select nvl(count(distinct(a.customer_trx_id)),0) cust_trx_id,
627 sum(nvl(gld.acctd_amount ,0)) amount
628 into prior_period1, prior_sum1
629 from ra_cust_trx_line_gl_dist gld,
630 ra_customer_trx a,
631 ra_cust_trx_types c
632 where complete_flag = 'Y'
633 and c.type = 'CM'
634 and nvl(a.reason_code,'0') = reason
635 and a.customer_trx_id = gld.customer_trx_id
636 and gld.account_class = 'REC'
637 and gld.latest_rec_flag = 'Y'
638 and a.cust_trx_type_id = c.cust_trx_type_id
639 and gld.gl_date is not null
640 and gld.gl_date between p_prior_start_date
641 and p_prior_end_date + (86399/86400)
642 --and a.invoice_currency_code = invoice_currency_code ;
643 and a.invoice_currency_code = invoice_currency_code_t ;
644
645 select nvl(count(distinct(a.customer_trx_id)),0) cust_trx_id,
646 sum(nvl(gld.acctd_amount,0)) amount
647 into prior_period2, prior_sum2
651 where complete_flag = 'Y'
648 from ra_cust_trx_line_gl_dist gld,
649 ra_customer_trx a,
650 ra_cust_trx_types c
652 and c.type = 'CM'
653 and nvl(a.reason_code,'0') = reason
654 and a.customer_trx_id = gld.customer_trx_id
655 and gld.account_class = 'REC'
656 and gld.latest_rec_flag = 'Y'
657 and a.cust_trx_type_id = c.cust_trx_type_id
658 and gld.gl_date is null
659 and a.trx_date between p_prior_start_date
660 and p_prior_end_date + (86399/86400)
661 --and a.invoice_currency_code = invoice_currency_code ;
662 and a.invoice_currency_code = invoice_currency_code_t ;
663
664 prior_period_t := nvl(prior_period1,0) + nvl(prior_period2,0) ;
665 prior_sum_t := nvl(prior_sum1,0) + nvl(prior_sum2,0) ;
666
667
668
669 IF prior_sum1 is null and prior_sum2 is null THEN
670 prior_sum_t := null;
671 END IF;
672
673 END IF;
674
675 current_period := current_period_t;
676 prior_period := prior_period_t;
677
678 if prior_period <> 0 then
679 c_percent_change := round(((current_period - prior_period)/prior_period * 100),2);
680 else
681 c_percent_change := 0.00;
682 end if;
683
684 current_sum := current_sum_t;
685 prior_sum := prior_sum_t;
686
687 if prior_sum <> 0 then
688 s_percent_change := round(((current_sum - prior_sum)/prior_sum * 100),2);
689 else
690 s_percent_change := 0.00;
691 end if;
692
693 all_sum_ctf_t := all_sum_t;
694 --all_sum_ctf := all_sum_ctf;
695 all_sum_ctf := all_sum_ctf_t;
696
697 return(1);
698
699 exception
700 when NO_DATA_FOUND then
701 return(0);
702 end;
703
704
705
706
707
708 RETURN NULL; end;
709
710 function CF_NO_REASONFormula return VARCHAR2 is
711 no_reason VARCHAR2(80);
712 begin
713 select meaning
714 into no_reason
715 from ar_lookups
716 where lookup_code = 'NO REASON'
717 and lookup_type = 'CREDIT_MEMO_REASON';
718 return(no_reason);
719 end;
720
721 function sum_p_percent_change1formula(sum_prior_inv_period in number, sum_current_inv_period in number) return number is
722 begin
723
724 /*srw.reference(sum_current_inv_period);*/null;
725
726 /*srw.reference(sum_prior_inv_period);*/null;
727
728 IF nvl(sum_prior_inv_period,0) <> 0
729 THEN
730 return(round(((sum_current_inv_period - sum_prior_inv_period)/sum_prior_inv_period ) * 100, 2)) ;
731 ELSE
732 return(0);
733 END IF;
734 end;
735
736 function sum_c_percent_change1formula(sum_prior_period in number, sum_current_period in number) return number is
737 begin
738
739 /*srw.reference(sum_current_period);*/null;
740
741 /*srw.reference(sum_prior_period);*/null;
742
743 IF nvl(sum_prior_period,0) <> 0
744 THEN
745 return(round(((sum_current_period - sum_prior_period)/sum_prior_period ) * 100, 2)) ;
746 ELSE
747 return(0);
748 END IF;
749 end;
750
751 function sum_a_percent_change1formula(sum_prior_inv_sum in number, sum_current_inv_sum in number) return number is
752 begin
753
754 /*srw.reference(sum_current_inv_sum);*/null;
755
756 /*srw.reference(sum_prior_inv_sum);*/null;
757
758 IF nvl(sum_prior_inv_sum,0) <> 0
759 THEN
760 return(round(((sum_current_inv_sum - sum_prior_inv_sum)/sum_prior_inv_sum ) * 100, 2)) ;
761 ELSE
762 return(0);
763 END IF;
764 end;
765
766 function sum_s_percent_change1formula(sum_prior_sum in number, sum_current_sum in number) return number is
767 begin
768
769 /*srw.reference(sum_current_sum);*/null;
770
771 /*srw.reference(sum_prior_sum);*/null;
772
773 IF nvl(sum_prior_sum,0) <> 0
774 THEN
775 return(round(((sum_current_sum - sum_prior_sum)/sum_prior_sum ) * 100, 2)) ;
776 ELSE
777 return(0);
778 END IF;
779 end;
780
781 function AfterReport return boolean is
782 begin
783 /*srw.user_exit('FND SRWEXIT');*/null;
784
785 return (TRUE);
786 end;
787
788 --Functions to refer Oracle report placeholders--
789
790 Function Current_Inv_Period_p return number is
791 Begin
792 return Current_Inv_Period;
793 END;
794 Function Prior_Inv_Period_p return number is
795 Begin
796 return Prior_Inv_Period;
797 END;
798 Function P_Percent_Change_p return number is
799 Begin
800 return P_Percent_Change;
801 END;
802 Function Current_Inv_Sum_p return number is
803 Begin
804 return Current_Inv_Sum;
805 END;
806 Function Prior_Inv_Sum_p return number is
807 Begin
808 return Prior_Inv_Sum;
809 END;
810 Function A_Percent_Change_p return number is
811 Begin
812 return A_Percent_Change;
813 END;
814 Function Inv_Sum_TF_p return number is
815 Begin
816 return Inv_Sum_TF;
817 END;
818 Function Current_Period_p return number is
819 Begin
820 return Current_Period;
821 END;
822 Function Prior_Period_p return number is
823 Begin
824 return Prior_Period;
825 END;
826 Function C_Percent_Change_p return number is
827 Begin
828 return C_Percent_Change;
829 END;
830 Function Current_Sum_p return number is
831 Begin
832 return Current_Sum;
833 END;
837 END;
834 Function Prior_Sum_p return number is
835 Begin
836 return Prior_Sum;
838 Function S_Percent_Change_p return number is
839 Begin
840 return S_Percent_Change;
841 END;
842 Function All_Sum_CTF_p return number is
843 Begin
844 return All_Sum_CTF;
845 END;
846 Function RP_COMPANY_NAME_p return varchar2 is
847 Begin
848 return RP_COMPANY_NAME;
849 END;
850 Function RP_REPORT_NAME_p return varchar2 is
851 Begin
852 return RP_REPORT_NAME;
853 END;
854 Function RP_DATA_FOUND_p return varchar2 is
855 Begin
856 return RP_DATA_FOUND;
857 END;
858 Function RP_SUB_TITLE_p return varchar2 is
859 Begin
860 return RP_SUB_TITLE;
861 END;
862 Function V_Addresses_p return number is
863 Begin
864 return V_Addresses;
865 END;
866 Function Prior_V_Addresses_p return number is
867 Begin
868 return Prior_V_Addresses;
869 END;
870 Function Address_Total_Number_p return number is
871 Begin
872 return Address_Total_Number;
873 END;
874 Function V_Customers_p return number is
875 Begin
876 return V_Customers;
877 END;
878 Function Prior_V_Customers_p return number is
879 Begin
880 return Prior_V_Customers;
881 END;
882 Function Cust_Total_Number_p return number is
883 Begin
884 return Cust_Total_Number;
885 END;
886 Function V_Inactive_Count_p return number is
887 Begin
888 return V_Inactive_Count;
889 END;
890 Function Customer_Change_p return varchar2 is
891 Begin
892 return Customer_Change;
893 END;
894 Function Address_Change_p return varchar2 is
895 Begin
896 return Address_Change;
897 END;
898 Function Prior_V_Inactive_Count_p return number is
899 Begin
900 return Prior_V_Inactive_Count;
901 END;
902 Function Inactive_Count_Total_Number_p return number is
903 Begin
904 return Inactive_Count_Total_Number;
905 END;
906 Function Inactive_Count_Change_p return varchar2 is
907 Begin
908 return Inactive_Count_Change;
909 END;
910 END AR_RAXSKI_XMLP_PKG ;
911