1 PACKAGE BODY AR_RAXCBR_XMLP_PKG AS
2 /* $Header: RAXCBRB.pls 120.2 2008/01/08 15:17:39 abraghun noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 /*SRW.USER_EXIT('FND SRWINIT');*/null;
8
9
10 /*srw.message ('100', 'BeforeReport: Get Source Code');*/null;
11
12
13
14
15 oe_profile.get('SO_SOURCE_CODE', P_SO_SOURCE_CODE);
16
17 /*srw.message ('100', 'BeforeReport: Source Code: ' || P_SO_SOURCE_CODE);*/null;
18
19
20
21
22 get_boiler_plates ;
23
24
25
26
27
28 return (TRUE);
29 end;
30
31 function AfterReport return boolean is
32 begin
33
34 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
35 return (TRUE);
36 end;
37
38 function report_nameformula(Company_Name in varchar2) return varchar2 is
39 begin
40
41 DECLARE
42 l_report_name VARCHAR2(80);
43 BEGIN
44 RP_Company_Name := Company_Name;
45 SELECT substr(cp.user_concurrent_program_name, 1, 80)
46 INTO l_report_name
47 FROM FND_CONCURRENT_PROGRAMS_VL cp,
48 FND_CONCURRENT_REQUESTS cr
49 WHERE cr.request_id = P_CONC_REQUEST_ID
50 AND cp.application_id = cr.program_application_id
51 AND cp.concurrent_program_id = cr.concurrent_program_id;
52
53 RP_Report_Name := l_report_name;
54
55 RETURN(l_report_name);
56 EXCEPTION
57 WHEN NO_DATA_FOUND
58 THEN RP_REPORT_NAME := 'Commitment Balance Report';
59 RETURN('Commitment Balance Report');
60 END;
61 RETURN NULL; end;
62
63 function c_adjusted_amount_childformula(source in varchar2, trx_class in varchar2, child_customer_trx_id in number, commit_type in varchar2, customer_trx_id1 in number) return number is
64
65 adjusted_amount Number;
66 begin
67
68 /*srw.reference(customer_trx_id);*/null;
69
70 /*srw.reference(child_customer_trx_id);*/null;
71
72
73 IF source = 'PS' THEN
74 IF trx_class = 'CM' THEN
75 select nvl(sum(extended_amount),0)
76 into adjusted_amount
77 from ra_customer_trx trx, ra_customer_trx_lines line
78 where trx.customer_trx_id = line.customer_trx_id
79 and trx.customer_trx_id = child_customer_trx_id
80 and complete_flag = 'Y';
81
82 RETURN( -adjusted_amount);
83
84 ELSE
85 IF commit_type = 'GUAR' THEN
86 select nvl(sum(amount), 0)
87 into adjusted_amount
88 from ar_adjustments
89 where customer_trx_id = customer_trx_id1
90 and subsequent_trx_id = child_customer_trx_id
91 and adjustment_type = 'C';
92
93 RETURN( -adjusted_amount);
94 ELSE
95 select nvl(sum(amount),0)
96 into adjusted_amount
97 from ar_adjustments
98 where customer_trx_id = child_customer_trx_id
99 and subsequent_trx_id is null
100 and adjustment_type = 'C';
101
102 RETURN( -adjusted_amount);
103 END IF;
104 END IF;
105 END IF;
106 return(0);
107
108 end;
109
110 function c_oe_amountformula(customer_trx_id1 in number) return number is
111
112 oe_amount number;
113 unbooked_amt number;
114
115 begin
116
117
118
119 /*srw.reference(customer_trx_id);*/null;
120
121
122
123 oe_amount := NVL(OE_PAYMENTS_UTIL.Get_Uninvoiced_Commitment_Bal(customer_trx_id1), 0);
124
125
126
127 if p_unbooked = 'N' then
128 select
129 nvl(sum(nvl(pay.commitment_applied_amount, 0) -
130 nvl(pay.commitment_interfaced_amount,0)),0) om_commitment_amt
131 into unbooked_amt
132 from oe_order_lines lin,
133 oe_payments pay
134 where lin.header_id = pay.header_id(+)
135 and lin.line_id = pay.line_id(+)
136 and lin.commitment_id = customer_trx_id1
137 and NVL(lin.open_flag,'Y') = 'Y'
138 and nvl(lin.booked_flag,'N') = 'N';
139
140 oe_amount := oe_amount - unbooked_amt;
141
142 end if;
143
144 select NVL(oe_amount,0) +
145 NVL( SUM(NVL(i.promised_commitment_amount,
146 i.amount)), 0)
147 into oe_amount
148 from ra_interface_lines i,
149 ra_customer_trx_lines l
150 where nvl(interface_status,
151 'A') <> 'P'
152 and i.line_type = 'LINE'
153 and i.reference_line_id = l.customer_trx_line_id
154 and l.customer_trx_id = customer_trx_id1
155 and i.interface_line_context = p_so_source_code
156 and (EXISTS
157 ( select 'valid_trx_type'
158 from ra_cust_trx_types ty
159 where (i.cust_trx_type_name = ty.name OR
160 i.cust_trx_type_id = ty.cust_trx_type_id)
161 AND ty.type = 'INV')
162 OR EXISTS (select 'valid sub_trx_type'
163 from ra_customer_trx trx,
164 ra_cust_trx_types ty
165 where trx.customer_trx_id = customer_trx_id1
166 and trx.cust_trx_type_id = ty.cust_trx_type_id
167 and (i.cust_trx_type_name is null AND
168 i.cust_trx_type_id is null AND
169 ty.subsequent_trx_type_id is not null)));
170 return(oe_amount);
171 end;
172
173 function c_commitment_balanceformula(commitment_amount in number, customer_trx_id in number, commit_type in varchar2) return varchar2 is
174 begin
175
176 /*srw.reference(customer_trx_id);*/null;
177
178 /*srw.reference(commit_type);*/null;
179
180
181 RETURN( commitment_amount -
182 arp_bal_util.calc_commitment_balance( customer_trx_id,
183 commit_type,
184 'N',
185 'N',
186 'XYZ' )
187 );
188 end;
189
190 function AfterPForm return boolean is
191 begin
192
193
194 BEGIN
195
196 IF p_commitment_low IS NOT NULL THEN
197 lp_commitment_low := ' and trx.trx_number >= :p_commitment_low ';
198 END IF;
199
200 IF p_commitment_high IS NOT NULL THEN
201 lp_commitment_high := ' and trx.trx_number <= :p_commitment_high ';
202 END IF;
203
204 IF p_currency_code_low IS NOT NULL THEN
205 lp_currency_code_low := ' and trx.invoice_currency_code >= :p_currency_code_low ';
206 END IF;
207
208 IF p_currency_code_high IS NOT NULL THEN
209 lp_currency_code_high := ' and trx.invoice_currency_code <= :p_currency_code_high ';
210 END IF;
211
212 IF p_agreement_name_low IS NOT NULL THEN
213 lp_agreement_name_low := ' and agree.name is not null ' ||
214 ' and agree.name >= :p_agreement_name_low ';
215 END IF;
216
217 IF p_agreement_name_high IS NOT NULL THEN
218 lp_agreement_name_high := ' and agree.name is not null ' ||
219 ' and agree.name <= :p_agreement_name_high ';
220 END IF;
221
222 IF p_customer_number_low IS NOT NULL THEN
223 lp_customer_number_low := ' and cust.account_number >= :p_customer_number_low ';
224 END IF;
225
226 IF p_customer_number_high IS NOT NULL THEN
227 lp_customer_number_high := ' and cust.account_number <= :p_customer_number_high ';
228 END IF;
229
230 IF p_customer_name_low IS NOT NULL THEN
231 lp_customer_name_low := ' and party.party_name >= :p_customer_name_low ';
232 END IF;
233
234 IF p_customer_name_high IS NOT NULL THEN
235 lp_customer_name_high := ' and party.party_name <= :p_customer_name_high ';
236 END IF;
237
238 IF p_commitment_type_low IS NOT NULL THEN
239 lp_commitment_type_low := ' and type.name >= :p_commitment_type_low ';
240 END IF;
241
242 IF p_commitment_type_high IS NOT NULL THEN
243 lp_commitment_type_high := ' and type.name <= :p_commitment_type_high ';
244 END IF;
245
246 IF p_end_date_low IS NOT NULL THEN
247 p_end_date_low_1:=to_char(to_date(p_end_date_low,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YY');
248 lp_end_date_low := ' and nvl(trx.end_date_commitment,to_date(:p_end_date_low,''YYYY/MM/DD HH24:MI:SS'')) >= to_date(:p_end_date_low,''YYYY/MM/DD HH24:MI:SS'')';
249 END IF;
250
251
252 IF p_end_date_high IS NOT NULL THEN
253 p_end_date_high_1:=to_char(to_date(p_end_date_high,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YY');
254 lp_end_date_high := ' and nvl(trx.end_date_commitment,to_date(:p_end_date_high,''YYYY/MM/DD HH24:MI:SS'')) <= to_date(:p_end_date_high,''YYYY/MM/DD HH24:MI:SS'') ';
255 END IF;
256
257 IF p_gl_date_low IS NOT NULL THEN
258 lp_gl_date_low := ' and nvl(lgd.gl_date,:p_gl_date_low) >= :p_gl_date_low ';
259 END IF;
260
261
262 IF p_gl_date_high IS NOT NULL THEN
263 lp_gl_date_high := ' and nvl(lgd.gl_date,:p_gl_date_high) <= :p_gl_date_high ';
264 END IF;
265
266 IF p_trx_date_low IS NOT NULL THEN
267 lp_trx_date_low := ' and nvl(trx.trx_date,:p_trx_date_low) >= :p_trx_date_low ';
268 END IF;
269
270
271 IF p_trx_date_high IS NOT NULL THEN
272 lp_trx_date_high := ' and nvl(trx.trx_date,:p_trx_date_high) <= :p_trx_date_high ';
273 END IF;
274
275 if p_unbooked = 'N' THEN
276 lp_unbooked := ' and nvl(lin.booked_flag,''N'') = ''Y'' ';
277 else
278 lp_unbooked := ' ';
279 end if;
280
281 END; return (TRUE);
282 end;
283
284 procedure get_lookup_meaning(p_lookup_type in VARCHAR2,
285 p_lookup_code in VARCHAR2,
286 p_lookup_meaning in out NOCOPY VARCHAR2)
287 is
288
289 w_meaning varchar2(80);
290
291 begin
292
293 select meaning
294 into w_meaning
295 from fnd_lookups
296 where lookup_type = p_lookup_type
297 and lookup_code = p_lookup_code ;
298
299 p_lookup_meaning := w_meaning ;
300
301 exception
302 when no_data_found then
303 p_lookup_meaning := null ;
304
305 end ;
306
307 procedure get_boiler_plates is
308
309 w_industry_code varchar2(20);
310 w_industry_stat varchar2(20);
311
312 begin
313
314 if fnd_installation.get(0, 0,
315 w_industry_stat,
316 w_industry_code) then
317 if w_industry_code = 'C' then
318 c_salesrep_title := null ;
319 c_salesorder_title := null ;
320 else
321 get_lookup_meaning('IND_SALES_REP',
322 w_industry_code,
323 c_salesrep_title);
324 get_lookup_meaning('IND_SALES_ORDER',
325 w_industry_code,
326 c_salesorder_title);
327 end if;
328 end if;
329
330 c_industry_code := w_Industry_code ;
331
332 end ;
333
334 function set_display_for_core(p_field_name in VARCHAR2)
335 return boolean is
336
337 begin
338
339 if c_industry_code = 'C' then
340 return(TRUE);
341 elsif p_field_name = 'SALESREP' then
342 if c_salesrep_title is not null then
343 return(FALSE);
344 else
345 return(TRUE);
346 end if;
347 elsif p_field_name = 'SALESORDER' then
348 if c_salesorder_title is not null then
349 return(FALSE);
350 else
351 return(TRUE);
352 end if;
353 end if;
354
355 RETURN NULL; end;
356
357 function set_display_for_gov(p_field_name in VARCHAR2)
358 return boolean is
359
360 begin
361
362
363 if c_industry_code = 'C' then
364 return(FALSE);
365 elsif p_field_name = 'SALESREP' then
366 if c_salesrep_title is not null then
367 return(TRUE);
368 else
369 return(FALSE);
370 end if;
371 elsif p_field_name = 'SALESORDER' then
372 if c_salesorder_title is not null then
373 return(TRUE);
374 else
375 return(FALSE);
376 end if;
377 end if;
378
379 RETURN NULL; end ;
380
381 function C_Order_ByFormula return Char is
382 order_meaning AR_LOOKUPS.MEANING%TYPE;
383 begin
384
385 SELECT
386 MEANING
387 INTO order_meaning
388 FROM AR_LOOKUPS
389 WHERE LOOKUP_TYPE = 'SORT_BY_RAXCBR'
390 AND UPPER(LOOKUP_CODE) = UPPER(P_SORT_BY);
391
392 RETURN (ORDER_MEANING);
393
394 EXCEPTION
395 WHEN NO_DATA_FOUND THEN
396 RETURN(P_SORT_BY);
397
398 end;
399
400 function c_commitment_remformula(commitment_amount in number, c_commitment_balance in varchar2, c_oe_amount in number) return number is
401 commitment_remaining Number;
402 begin
403
404 /*srw.reference(commitment_amount);*/null;
405
406 /*srw.reference(c_commitment_balance);*/null;
407
408 /*srw.reference(c_oe_amount);*/null;
409
410
411 commitment_remaining := nvl(commitment_amount,0)
412 - nvl(c_commitment_balance,0)
413 - nvl(c_oe_amount,0);
414
415 RETURN(commitment_remaining);
416 end;
417
418 function c_adj_amt_cmformula(source1 in varchar2, commit_type in varchar2, child_customer_trx_id in number, child_cm_customer_trx_id in number, customer_trx_id1 in number) return number is
419 adjusted_amount Number;
420 begin
421
422 /*srw.reference(customer_trx_id);*/null;
423
424 /*srw.reference(child_customer_trx_id);*/null;
425
426
427 IF source1 = 'PS' THEN
428 IF commit_type = 'DEP' THEN
429 select nvl(sum(amount),0)
430 into adjusted_amount
431 from ar_adjustments
432 where customer_trx_id = child_customer_trx_id
433 and subsequent_trx_id = child_cm_customer_trx_id
434 and adjustment_type = 'C';
435
436
437 RETURN( -adjusted_amount);
438 ELSE
439 select nvl(sum(amount), 0)
440 into adjusted_amount
441 from ar_adjustments
442 where customer_trx_id = customer_trx_id1
443 and subsequent_trx_id = child_cm_customer_trx_id
444 and adjustment_type = 'C';
445
446 RETURN( -adjusted_amount);
447 END IF;
448 ELSE
449 return(0);
450 END IF;
451
452
453 end;
454
455 function c_sum_invoiced_amount_arformul(sum_invoiced_amount_inv in number, sum_invoiced_amount_cm in number) return number is
456 begin
457 /*srw.reference(sum_invoiced_amount_inv);*/null;
458
459 /*srw.reference(sum_invoiced_amount_cm);*/null;
460
461 return(nvl(sum_invoiced_amount_inv,0) + nvl(sum_invoiced_amount_cm,0) );
462 end;
463
464 function c_sum_tax_amount_arformula(sum_tax_amount_inv in number, sum_tax_amount_cm in number) return number is
465 begin
466 /*srw.reference(sum_tax_amount_inv);*/null;
467
468 /*srw.reference(sum_tax_amount_cm);*/null;
469
470 return(nvl(sum_tax_amount_inv,0) + nvl(sum_tax_amount_cm,0) );
471 end;
472
473 function c_sum_freight_amount_arformula(sum_freight_amount_inv in number, sum_freight_amount_cm in number) return number is
474 begin
475 /*srw.reference(sum_freight_amount_inv);*/null;
476
477 /*srw.reference(sum_freight_amount_cm);*/null;
478
479 return(nvl(sum_freight_amount_inv,0) + nvl(sum_freight_amount_cm,0) );
480 end;
481
482 function c_sum_line_amount_arformula(sum_line_amount_inv in number, sum_line_amount_cm in number) return number is
483 begin
484 /*srw.reference(sum_line_amount_inv);*/null;
485
486 /*srw.reference(sum_line_amount_cm);*/null;
487
488 return(nvl(sum_line_amount_inv,0) + nvl(sum_line_amount_cm,0) );
489 end;
490
491 function c_sum_adjusted_amount_arformul(sum_adjusted_amount_inv in number, sum_adjusted_amount_cm in number) return number is
492 begin
493 /*srw.reference(sum_adjusted_amount_inv);*/null;
494
495 /*srw.reference(sum_adjusted_amount_cm);*/null;
496
497 return(nvl(sum_adjusted_amount_inv,0) + nvl(sum_adjusted_amount_cm,0) );
498 end;
499
500 function c_sum_bal_amount_arformula(sum_bal_amount_inv in number, sum_bal_amount_cm in number) return number is
501 begin
502 /*srw.reference(sum_bal_amount_inv);*/null;
503
504 /*srw.reference(sum_bal_amount_cm);*/null;
505
506 return(nvl(sum_bal_amount_inv,0) + nvl(sum_bal_amount_cm,0));
507 end;
508
509 function C_FORMAT_LEVELFormula return Char is
510 meaning FND_LOOKUP_VALUES_VL.meaning%TYPE;
511 begin
512
513 SELECT
514 MEANING
515 INTO meaning
516 FROM FND_LOOKUP_VALUES_VL
517 WHERE LOOKUP_TYPE = 'RAXCBR_FORMAT'
518 AND UPPER(LOOKUP_CODE) = UPPER(P_LEVEL);
519
520 RETURN (meaning);
521
522 EXCEPTION
523 WHEN NO_DATA_FOUND THEN
524 RETURN(P_Level);
525
526
527
528
529 end;
530
531 --Functions to refer Oracle report placeholders--
532
533 Function RP_COMPANY_NAME_p return varchar2 is
534 Begin
535 return RP_COMPANY_NAME;
536 END;
537 Function RP_REPORT_NAME_p return varchar2 is
538 Begin
539 return RP_REPORT_NAME;
540 END;
541 Function RP_DATA_FOUND_p return varchar2 is
542 Begin
543 return RP_DATA_FOUND;
544 END;
545 Function c_industry_code_p return varchar2 is
546 Begin
547 return c_industry_code;
548 END;
549 Function c_salesrep_title_p return varchar2 is
550 Begin
551 return c_salesrep_title;
552 END;
553 Function c_salesorder_title_p return varchar2 is
554 Begin
555 return c_salesorder_title;
556 END;
557 END AR_RAXCBR_XMLP_PKG ;
[Home] [Help]