[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_UTILS_PKG
Source
1 package body AR_BPA_UTILS_PKG as
2 /* $Header: ARBPAUTB.pls 120.11 2010/09/24 11:03:08 rviriyal noship $*/
3
4 PROCEDURE debug (
5 p_message IN VARCHAR2,
6 p_log_level IN NUMBER default FND_LOG.LEVEL_STATEMENT,
7 p_module_name IN VARCHAR2 default 'ar.plsql.ar_bpa_utils_pkg') IS
8
9 BEGIN
10 if ( (pg_debug = 'Y') and ( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )) then
11 FND_LOG.string(p_log_level,p_module_name, p_message);
12 end if;
13 END;
14
15
16 FUNCTION fn_get_header_level_so ( p_customer_trx_id IN number ) return varchar2 AS
17 cursor c_linesSO is
18 select distinct ctl.sales_order
19 from ra_customer_trx_lines ctl
20 where ctl.customer_trx_id = p_customer_trx_id
21 and ctl.line_type = 'LINE';
22
23 l_count_sales_orders number := 0;
24 l_so_number varchar2(30) := null;
25 BEGIN
26
27 FOR crec in c_linesSO
28 LOOP
29 l_count_sales_orders := l_count_sales_orders + 1;
30 if (l_count_sales_orders > 1) then
31 l_so_number := '-1'; -- multiple value
32 exit; -- break
33 else
34 l_so_number := crec.sales_order;
35 end if;
36 END LOOP;
37
38 return l_so_number;
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 return null;
43 END fn_get_header_level_so;
44
45 FUNCTION fn_get_header_level_co ( p_customer_trx_id IN number ) return varchar2 AS
46 cursor c_linesCO is
47 select distinct ctl.interface_line_attribute1
48 from ra_customer_trx_lines ctl
49 where ctl.customer_trx_id = p_customer_trx_id
50 and ctl.line_type = 'LINE';
51
52 l_count_contract_numbers number := 0;
53 l_co_number varchar2(30) := null;
54 BEGIN
55
56 FOR crec in c_linesCO
57 LOOP
58 l_count_contract_numbers := l_count_contract_numbers + 1;
59 if (l_count_contract_numbers > 1) then
60 l_co_number := '-1'; -- multiple value
61 exit; -- break
62 else
63 l_co_number := crec.interface_line_attribute1;
64 end if;
65 END LOOP;
66
67 return l_co_number;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 return null;
72 END fn_get_header_level_co;
73
74 FUNCTION fn_get_billing_line_level ( p_customer_trx_id IN number ) return varchar2 AS
75 cursor c_headerlevel is
76 select 1
77 from ra_customer_trx ctl
78 where ctl.customer_trx_id = p_customer_trx_id
79 and ctl.interface_header_context = 'OKS CONTRACTS';
80 cursor c_linelevel is
81 select ctl.INVOICED_LINE_ACCTG_LEVEL
82 from ra_customer_trx_lines ctl
83 where ctl.customer_trx_id = p_customer_trx_id
84 and ctl.line_type = 'LINE'
85 and ctl.interface_line_context = 'OKS CONTRACTS'
86 and ctl.interface_line_attribute9 = 'Service';
87 l_line_level varchar2(30) := null;
88 BEGIN
89 FOR crec in c_headerlevel
90 LOOP
91 -- if we are here, it means it is an OKS contract invoice.
92 l_line_level := 'D';
93 exit; -- break
94 END LOOP;
95
96 if (l_line_level = 'D') then
97 FOR crec1 in c_linelevel
98 LOOP
99 l_line_level := crec1.INVOICED_LINE_ACCTG_LEVEL;
100 exit; -- break
101 END LOOP;
102 end if;
103 return l_line_level;
104 EXCEPTION
105 WHEN OTHERS THEN
106 return null;
107 END fn_get_billing_line_level;
108
109 FUNCTION fn_get_profile_class_name ( p_customer_trx_id IN number ) return varchar2 AS
110 cursor c_profileclass1 is
111 select hzc.name
112 from ra_customer_trx trx,
113 hz_customer_profiles hzp,
114 hz_cust_profile_classes hzc
115 where trx.customer_trx_id = p_customer_trx_id
116 and trx.bill_to_customer_id = hzp.cust_account_id
117 and trx.bill_to_site_use_id = hzp.site_use_id
118 and hzp.profile_class_id = hzc.profile_class_id;
119
120 cursor c_profileclass2 is
121 select hzc.name
122 from ra_customer_trx trx,
123 hz_customer_profiles hzp,
124 hz_cust_profile_classes hzc
125 where trx.customer_trx_id = p_customer_trx_id
126 and trx.bill_to_customer_id = hzp.cust_account_id
127 and hzp.site_use_id is null
128 and hzp.profile_class_id = hzc.profile_class_id;
129
130 l_profile_class_name varchar2(30) := null;
131 BEGIN
132
133 FOR crec in c_profileclass1
134 LOOP
135 l_profile_class_name := crec.name;
136 END LOOP;
137
138 if (l_profile_class_name is null) then
139 FOR crec1 in c_profileclass2
140 LOOP
141 l_profile_class_name := crec1.name;
142 END LOOP;
143
144 end if;
145 return l_profile_class_name;
146
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 return null;
151 END fn_get_profile_class_name;
152
153 FUNCTION fn_get_tax_printing_option ( p_bill_to_site_use_id IN number, p_bill_to_customer_id in number ) return varchar2 AS
154 cursor c_tax_printing_option1 is
155 select cp_site.tax_printing_option
156 from hz_customer_profiles cp_site
157 where cp_site.site_use_id = p_bill_to_site_use_id
158 and cp_site.cust_account_id = p_bill_to_customer_id;
159
160 cursor c_tax_printing_option2 is
161 select cp_cust.tax_printing_option
162 from hz_customer_profiles cp_cust
163 where cp_cust.cust_account_id = p_bill_to_customer_id
164 and cp_cust.site_use_id is null;
165
166 cursor c_tax_printing_option3 is
167 SELECT tax_invoice_print from AR_SYSTEM_PARAMETERS;
168
169 l_tax_printing_option hz_customer_profiles.tax_printing_option%TYPE := null;
170 BEGIN
171 FOR crec in c_tax_printing_option1
172 LOOP
173 l_tax_printing_option := crec.tax_printing_option;
174 END LOOP;
175
176 if (l_tax_printing_option is null) then
177 FOR crec1 in c_tax_printing_option2
178 LOOP
179 l_tax_printing_option := crec1.tax_printing_option;
180 END LOOP;
181 end if;
182
183 if (l_tax_printing_option is null) then
184 FOR crec2 in c_tax_printing_option3
185 LOOP
186 l_tax_printing_option := crec2.tax_invoice_print;
187 END LOOP;
188 end if;
189
190 return l_tax_printing_option;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 return null;
195 END fn_get_tax_printing_option;
196
197 -- it only works for OKS so far, need extend it to support dynamic data source
198 -- and future integration with other source products.
199 FUNCTION fn_trx_has_groups ( p_customer_trx_id IN number ) return varchar2 AS
200 CURSOR c_trx_has_groups IS
201 SELECT 1
202 FROM ra_customer_trx_lines
203 WHERE customer_trx_id = p_customer_trx_id
204 AND line_type = 'LINE'
205 and (source_data_key1 is not null
206 or source_data_key2 is not null
207 or source_data_key3 is not null
208 or source_data_key4 is not null
209 or source_data_key5 is not null
210 );
211 l_trx_has_groups varchar2(10) := 'N';
212
213 BEGIN
214 l_trx_has_groups := 'N';
215 FOR crec in c_trx_has_groups
216 LOOP
217 -- if we are here, it means at least one line has a grouping key.
218 l_trx_has_groups := 'Y';
219 exit; -- break
220 END LOOP;
221 return l_trx_has_groups;
222 EXCEPTION
223 WHEN OTHERS THEN
224 return null;
225 END fn_trx_has_groups;
226
227 FUNCTION fn_get_line_taxrate( p_customer_trx_line_id IN number ) return varchar2 AS
228 cursor c_linetax is
229 SELECT to_char(round(ctl.tax_rate,4)) line_tax_rate
230 from ra_customer_trx_lines ctl
231 where
232 ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
233 and ctl.line_type = 'TAX'
234 and rownum = 1;
235
236 l_taxrate varchar2(30) := null;
237 BEGIN
238
239 FOR crec in c_linetax
240 LOOP
241 l_taxrate := crec.line_tax_rate;
242 END LOOP;
243
244 return l_taxrate;
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 return null;
249 END fn_get_line_taxrate;
250
251 FUNCTION fn_get_line_taxname( p_customer_trx_line_id IN number ) return varchar2 AS
252 cursor c_linetax is
253 SELECT v.tax_rate_name as printed_tax_name
254 from ra_customer_trx_lines ctl,
255 zx_rates_vl v
256 where
257 ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
258 and ctl.line_type = 'TAX'
259 and ctl.vat_tax_id = v.tax_rate_id(+)
260 and rownum = 1;
261
262 l_taxname varchar2(80) := null;
263 BEGIN
264
265 FOR crec in c_linetax
266 LOOP
267 l_taxname := crec.printed_tax_name;
268 END LOOP;
269
270 return l_taxname;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 return null;
275 END fn_get_line_taxname;
276
277 FUNCTION fn_get_line_taxcode( p_customer_trx_line_id IN number ) return varchar2 AS
278 cursor c_linetax is
279 SELECT v.tax_rate_code as tax_code
280 from ra_customer_trx_lines ctl,
281 zx_rates_vl v
282 where
283 ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
284 and ctl.line_type = 'TAX'
285 and ctl.vat_tax_id = v.tax_rate_id(+)
286 and rownum = 1;
287
288 l_taxcode varchar2(30) := null;
289 BEGIN
290
291 FOR crec in c_linetax
292 LOOP
293 l_taxcode := crec.tax_code;
294 END LOOP;
295
296 return l_taxcode;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 return null;
301 END fn_get_line_taxcode;
302
303 FUNCTION fn_get_group_taxrate (p_customer_trx_id IN number, id in number, bcl_id in number) return varchar2 AS
304 cursor c_lines is
305 SELECT lines.customer_trx_line_id
306 from ra_customer_trx_lines lines
307 where
308 lines.customer_trx_id = p_customer_trx_id
309 and lines.line_type = 'LINE'
310 and source_data_key1 = id
311 and source_data_key2 = bcl_id
312 and rownum = 1;
313
314 cursor c_linetax (id number) is
315 SELECT to_char(round(ctl.tax_rate,4)) line_tax_rate
316 from ra_customer_trx_lines ctl
317 where
318 ctl.link_to_cust_trx_line_id = id
319 and ctl.line_type = 'TAX'
320 and rownum = 1;
321
322 l_line_id number := 0;
323 l_taxrate varchar2(30) := null;
324 BEGIN
325
326 FOR crec in c_lines
327 LOOP
328 l_line_id := crec.customer_trx_line_id;
329 END LOOP;
330
331 if (l_line_id <> 0) then
332 BEGIN
333 FOR crec1 in c_linetax(l_line_id)
334 LOOP
335 l_taxrate := crec1.line_tax_rate;
336 END LOOP;
337 EXCEPTION
338 WHEN OTHERS THEN
339 return NULL;
340 END;
341 end if;
342 return l_taxrate;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 return null;
347 END fn_get_group_taxrate;
348
349 FUNCTION fn_get_group_taxname (p_customer_trx_id IN number, id in number, bcl_id in number) return varchar2 AS
350 cursor c_lines is
351 SELECT lines.customer_trx_line_id
352 from ra_customer_trx_lines lines
353 where
354 lines.customer_trx_id = p_customer_trx_id
355 and lines.line_type = 'LINE'
356 and source_data_key1 = id
357 and source_data_key2 = bcl_id
358 and rownum = 1;
359
360 cursor c_linetax (id number) is
361 SELECT v.tax_rate_name as printed_tax_name
362 from ra_customer_trx_lines ctl,
363 zx_rates_vl v
364 where
365 ctl.link_to_cust_trx_line_id = id
366 and ctl.line_type = 'TAX'
367 and ctl.vat_tax_id = v.tax_rate_id(+)
368 and rownum = 1;
369
370 l_line_id number := 0;
371 l_taxname varchar2(30) := null;
372 BEGIN
373
374 FOR crec in c_lines
375 LOOP
376 l_line_id := crec.customer_trx_line_id;
377 END LOOP;
378
379 if (l_line_id <> 0) then
380 BEGIN
381 FOR crec1 in c_linetax(l_line_id)
382 LOOP
383 l_taxname := crec1.printed_tax_name;
384 END LOOP;
385 EXCEPTION
386 WHEN OTHERS THEN
387 return NULL;
388 END;
389 end if;
390 return l_taxname;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 return null;
395 END fn_get_group_taxname;
396
397 FUNCTION fn_get_group_taxcode (p_customer_trx_id IN number, id in number, bcl_id in number) return varchar2 AS
398 cursor c_lines is
399 SELECT lines.customer_trx_line_id
400 from ra_customer_trx_lines lines
401 where
402 lines.customer_trx_id = p_customer_trx_id
403 and lines.line_type = 'LINE'
404 and source_data_key1 = id
405 and source_data_key2 = bcl_id
406 and rownum = 1;
407
408 cursor c_linetax (id number) is
409 SELECT v.tax_rate_code as tax_code
410 from ra_customer_trx_lines ctl,
411 zx_rates_vl v
412 where
413 ctl.link_to_cust_trx_line_id = id
414 and ctl.line_type = 'TAX'
415 and ctl.vat_tax_id = v.tax_rate_id(+)
416 and rownum = 1;
417
418 l_line_id number := 0;
419 l_taxcode varchar2(30) := null;
420 BEGIN
421
422 FOR crec in c_lines
423 LOOP
424 l_line_id := crec.customer_trx_line_id;
425 END LOOP;
426
427 if (l_line_id <> 0) then
428 BEGIN
429 FOR crec1 in c_linetax(l_line_id)
430 LOOP
431 l_taxcode := crec1.tax_code;
432 END LOOP;
433 EXCEPTION
434 WHEN OTHERS THEN
435 return NULL;
436 END;
437 end if;
438 return l_taxcode;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 return null;
443 END fn_get_group_taxcode;
444
445 FUNCTION fn_get_group_taxyn (p_customer_trx_id IN number, id in number, bcl_id in number) return varchar2 AS
446 cursor c_lines is
447 SELECT lines.customer_trx_line_id
448 from ra_customer_trx_lines lines
449 where
450 lines.customer_trx_id = p_customer_trx_id
451 and lines.line_type = 'LINE'
452 and source_data_key1 = id
453 and source_data_key2 = bcl_id
454 and rownum = 1;
455
456 l_line_id number := 0;
457 BEGIN
458
459 FOR crec in c_lines
460 LOOP
461 l_line_id := crec.customer_trx_line_id;
462 END LOOP;
463
464 return AR_INVOICE_SQL_FUNC_PUB.get_taxyn (l_line_id);
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 return null;
469 END fn_get_group_taxyn;
470
471 FUNCTION fn_get_line_description ( p_customer_trx_line_id IN number) return varchar2 AS
472 cursor c_linetax is
473 SELECT
474 ctl.line_type,
475 nvl(ctl.translated_description,ctl.description) line_description,
476 ctl.tax_rate,
477 ctl.vat_tax_id,
478 ctl.tax_exemption_id,
479 ctl.sales_tax_id,
480 ctl.tax_precedence
481 from ra_customer_trx_lines ctl
482 where ctl.customer_trx_line_id = p_customer_trx_line_id;
483
484 description varchar2(2000) := null;
485 line_type ra_customer_trx_lines.LINE_TYPE%TYPE;
486 line_description ra_customer_trx_lines.DESCRIPTION%TYPE;
487 tax_rate ra_customer_trx_lines.TAX_RATE%TYPE;
488 vat_tax_id ra_customer_trx_lines.VAT_TAX_ID%TYPE;
489 tax_exemption_id ra_customer_trx_lines.TAX_EXEMPTION_ID%TYPE;
490 location_rate_id ra_customer_trx_lines.SALES_TAX_ID%TYPE;
491 tax_precedence ra_customer_trx_lines.TAX_PRECEDENCE%TYPE;
492
493 BEGIN
494 open c_linetax;
495 fetch c_linetax into line_type, line_description, tax_rate, vat_tax_id, tax_exemption_id, location_rate_id, tax_precedence;
496 CLOSE c_linetax;
497
498 if ( line_type = 'TAX' ) then
499 return get_tax_description(tax_rate, vat_tax_id, tax_exemption_id,location_rate_id, tax_precedence, '');
500 else
501 return line_description;
502 end if;
503
504 return description;
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 return null;
509 END fn_get_line_description;
510
511 /* Return contact name */
512 /*
513 Bug#10112558
514 Description: CUSTOMER CONTACT FOR PERSON WITHOUT FIRST NAME ARE PRINTED WITH A LEADING SPACE
515 Modified on: 21 Sep 2010
516 Modified by: rviriyal
517 */
518
519 FUNCTION fn_get_contact_name (p_contact_id IN NUMBER) return varchar2 IS
520 CURSOR c_cont_name( id in number ) IS
521 SELECT party.person_first_name fname
522 ,party.person_last_name lname
523 FROM hz_cust_account_roles acct_role,
524 hz_relationships rel,
525 hz_parties party
526 WHERE acct_role.cust_account_role_id = id
527 AND acct_role.ROLE_TYPE = 'CONTACT'
528 AND acct_role.party_id = rel.party_id
529 AND rel.subject_id = party.party_id
530 AND rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
531 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
532 AND rel.DIRECTIONAL_FLAG = 'F';
533
534 l_contact_name varchar2(240) := null;
535 BEGIN
536 FOR crec IN c_cont_name(p_contact_id)
537 LOOP
538 if(crec.fname is null) then
539 l_contact_name := crec.lname;
540 else
541 l_contact_name := crec.fname ||' '|| crec.lname;
542 end if;
543 exit; -- break
544 END LOOP;
545 return l_contact_name;
546
547 EXCEPTION
548 WHEN OTHERS THEN
549 return null;
550 END;
551
552
553 /* Return contact phone */
554 FUNCTION fn_get_phone (p_contact_id IN NUMBER) return varchar2 IS
555 CURSOR c_cont_phone( id in number ) IS
556 select min(decode(cont_point.contact_point_type,'TLX',
557 cont_point.telex_number, cont_point.phone_number)) contact_phone
558 from hz_contact_points cont_point,
559 hz_cust_account_roles acct_role
560 where acct_role.cust_account_role_id = id
561 and acct_role.party_id = cont_point.owner_table_id
562 and cont_point.owner_table_name = 'HZ_PARTIES'
563 and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'GEN';
564 l_phone varchar2(240) := null;
565 BEGIN
566 FOR crec IN c_cont_phone(p_contact_id)
567 LOOP
568 l_phone := crec.contact_phone;
569 exit; -- break
570 END LOOP;
571
572 return l_phone;
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 return null;
577 END;
578
579 /* Return contact fax */
580 FUNCTION fn_get_fax (p_contact_id IN NUMBER) return varchar2 IS
581 CURSOR c_cont_fax( id in number ) IS
582 select min(decode(cont_point.contact_point_type,'TLX',
583 cont_point.telex_number, cont_point.phone_number)) contact_fax
584 from hz_contact_points cont_point,
585 hz_cust_account_roles acct_role
586 where acct_role.cust_account_role_id = id
587 and acct_role.party_id = cont_point.owner_table_id
588 and cont_point.owner_table_name = 'HZ_PARTIES'
589 and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'FAX';
590 l_fax varchar2(240) := null;
591 BEGIN
592 FOR crec IN c_cont_fax(p_contact_id)
593 LOOP
594 l_fax := crec.contact_fax;
595 exit; -- break
596 END LOOP;
597
598 return l_fax;
599
600 EXCEPTION
601 WHEN OTHERS THEN
602 return null;
603 END;
604
605 function get_tax_description(
606 tax_rate in number,
607 vat_tax_id in number,
608 tax_exemption_id in number,
609 location_rate_id in number,
610 tax_precedence in number,
611 D_euro_taxable_amount in varchar2 ) return varchar2 is
612
613 d varchar2(240);
614 e varchar2(240);
615
616 cursor tax_type_c( id in number ) is
617 select t.tax_type_code,t.compounding_precedence
618 from zx_taxes_vl t,zx_rates_vl r
619 where t.tax_regime_code = r.tax_regime_code
620 and t.tax = r.tax
621 and r.tax_rate_id = id;
622
623 cursor vat_tax_c( id in number ) is
624 select tax_rate_code as tax_code, tax_rate_name as printed_tax_name from zx_rates_vl where tax_rate_id = id;
625
626 cursor exem_c( id in number ) is
627 select
628 decode(nvl(product_id,-999),-999,'CUSTOMER','ITEM') as exemption_type,
629 rate_modifier as percent_exempt
630 from zx_exemptions
631 where tax_exemption_id = id;
632
633 cursor tax_type_name_c(code in varchar2) is
634 select meaning
635 from fnd_lookups
636 where lookup_type = 'ZX_TAX_TYPE_CATEGORY'
637 and lookup_code = code;
638
639
640 vcode varchar2(60);
641 vname varchar2(200);
642 vcode_name varchar2(60);
643 vtype_code varchar2(60);
644 vprecedence number;
645 vtaxtypename varchar(100);
646
647 etype varchar2( 30 );
648 pexempt number;
649
650 begin
651 d := null;
652 vtype_code := null;
653
654 if vat_tax_id is not null
655 then
656 open tax_type_c(vat_tax_id);
657 fetch tax_type_c into vtype_code, vprecedence;
658
659 if tax_type_c%NOTFOUND
660 then
661 open tax_type_name_c(vtype_code);
662 fetch tax_type_name_c into vtaxtypename;
663 d := vtaxtypename||' @ '||ltrim(to_char(tax_rate, '990D99'));
664 close tax_type_name_c;
665 else
666 if (vtype_code = 'SALES')
667 then
668 d := d || ' ' || arp_standard.fnd_message( 'AR_REPORTS_SALES_TAX', 'TAX_RATE', ltrim(to_char(tax_rate, '9990D00')));
669 ELSE
670 if ( vtype_code = 'VAT' or (vtype_code is null)) then
671 open vat_tax_c(vat_tax_id);
672 fetch vat_tax_c into vcode, vname;
673
674 vcode_name := vcode; -- Print Tax Code
675 d := arp_standard.fnd_message( 'AR_REPORTS_VAT_TAX', 'TAX_CODE', rpad(VCODE_NAME,10), 'EURO_TAXABLE_AMOUNT', '', 'TAX_RATE', ltrim(to_char(tax_rate, '990D99')));
676
677 close vat_tax_c;
678 end if;
679 END IF;
680 end if;
681
682 close tax_type_c;
683 end if;
684
685 if tax_exemption_id is not null and ( vat_tax_id is null )
686 then
687 open exem_c( tax_exemption_id );
688 fetch exem_c into etype, pexempt;
689 if exem_c%NOTFOUND
690 then
691 d := d || ' ' || arp_standard.fnd_message('AR_IP_NO_TAX_EXEM_ID',
692 'TAX_EXEMPTION_ID', ltrim(to_char( tax_exemption_id )));
693 else
694 d := d || ' ' || arp_standard.fnd_message( 'AR_IP_TAX_EXEMPTION', 'EXEMPTION_TYPE', initcap(etype),
695 'PERCENT_EXEMPT', ltrim(to_char(pexempt, '990D99')));
696 end if;
697 close exem_c;
698
699 end if;
700
701 if vprecedence is not null
702 then
703 d := d || ' ' || arp_standard.fnd_message( 'AR_REPORTS_PRECEDENCE') || ' ' ||
704 ltrim(to_char( vprecedence,'9990') );
705 end if;
706
707 -- If you know what tax it is but no message generated so far, we create a general message.
708 if vtype_code is not null and d is null then
709 open tax_type_name_c(vtype_code);
710 fetch tax_type_name_c into vtaxtypename;
711 d := vtaxtypename||' @ '||ltrim(to_char(tax_rate, '990D99'));
712 close tax_type_name_c;
713
714 end if;
715
716 return( ltrim(d, ' '));
717
718 EXCEPTION
719 WHEN OTHERS THEN
720 return null;
721
722 end ;
723
724 /* Create duplicate content areas and area_items_map while duplicating a template.
725 Template row is already duplicated in the framework and will get committed
726 along with these inserts.
727 */
728 procedure create_dup_areas(
729 p_orig_template_id IN NUMBER,
730 p_dup_template_id IN NUMBER
731 ) IS
732
733
734 cursor c_org_ca_b IS
735 select
736 CA.ITEM_LABEL_STYLE,
737 CA.ITEM_VALUE_STYLE,
738 CA.CONTENT_DISP_PROMPT_STYLE,
739 CA.INVOICE_LINE_TYPE,
740 CA.AREA_CODE,
741 CA.PARENT_AREA_CODE,
742 CA.LINE_REGION_FLAG,
743 CA.CONTENT_COUNT,
744 CA.CONTENT_AREA_RIGHT_SPACE,
745 CA.CONTENT_AREA_TOP_SPACE,
746 CA.CONTENT_AREA_BOTTOM_SPACE,
747 CA.DISPLAY_LEVEL,
748 CA.CONTENT_TYPE,
749 CA.CONTENT_ORIENTATION,
750 CA.CONTENT_STYLE_ID,
751 CA.ITEM_ID,
752 CA.URL_ID,
753 CA.DISPLAY_SEQUENCE,
754 CA.CONTENT_AREA_WIDTH,
755 CA.CONTENT_AREA_LEFT_SPACE,
756 CA.ITEM_COLUMN_WIDTH,
757 CA.CONTENT_AREA_ID
758 from ar_bpa_content_areas_b CA
759 where CA.template_id = p_orig_template_id
760 order by CA.content_area_id;
761
762 l_new_ca_id number := 0;
763 l_user_id number := -1;
764 BEGIN
765
766 /*
767 1. insert into ca_b, ca_tl
768 2. insert into area_items
769 */
770 FOR cabrec in c_org_ca_b
771 LOOP
772 select ar_bpa_content_areas_s.nextval
773 into l_new_ca_id
774 from dual;
775
776 insert into AR_BPA_CONTENT_AREAS_B (
777 ITEM_LABEL_STYLE,
778 ITEM_VALUE_STYLE,
779 CONTENT_DISP_PROMPT_STYLE,
780 INVOICE_LINE_TYPE,
781 AREA_CODE,
782 PARENT_AREA_CODE,
783 LINE_REGION_FLAG,
784 CONTENT_COUNT,
785 CONTENT_AREA_RIGHT_SPACE,
786 CONTENT_AREA_TOP_SPACE,
787 CONTENT_AREA_BOTTOM_SPACE,
788 CONTENT_AREA_ID,
789 DISPLAY_LEVEL,
790 CONTENT_TYPE,
791 CONTENT_ORIENTATION,
792 TEMPLATE_ID,
793 CONTENT_STYLE_ID,
794 ITEM_ID,
795 URL_ID,
796 DISPLAY_SEQUENCE,
797 CONTENT_AREA_WIDTH,
798 CONTENT_AREA_LEFT_SPACE,
799 CREATION_DATE,
800 CREATED_BY,
801 LAST_UPDATE_DATE,
802 LAST_UPDATED_BY,
803 LAST_UPDATE_LOGIN,
804 ITEM_COLUMN_WIDTH
805 ) values (
806 cabrec.ITEM_LABEL_STYLE,
807 cabrec.ITEM_VALUE_STYLE,
808 cabrec.CONTENT_DISP_PROMPT_STYLE,
809 cabrec.INVOICE_LINE_TYPE,
810 cabrec.AREA_CODE,
811 cabrec.PARENT_AREA_CODE,
812 cabrec.LINE_REGION_FLAG,
813 cabrec.CONTENT_COUNT,
814 cabrec.CONTENT_AREA_RIGHT_SPACE,
815 cabrec.CONTENT_AREA_TOP_SPACE,
816 cabrec.CONTENT_AREA_BOTTOM_SPACE,
817 l_new_ca_id,
818 cabrec.DISPLAY_LEVEL,
819 cabrec.CONTENT_TYPE,
820 cabrec.CONTENT_ORIENTATION,
821 p_dup_template_id,
822 cabrec.CONTENT_STYLE_ID,
823 cabrec.ITEM_ID,
824 cabrec.URL_ID,
825 cabrec.DISPLAY_SEQUENCE,
826 cabrec.CONTENT_AREA_WIDTH,
827 cabrec.CONTENT_AREA_LEFT_SPACE,
828 sysdate,
829 l_user_id,
830 sysdate,
831 l_user_id,
832 l_user_id,
833 cabrec.ITEM_COLUMN_WIDTH
834 );
835
836 insert into AR_BPA_CONTENT_AREAS_TL (
837 CONTENT_AREA_ID,
838 CONTENT_AREA_NAME,
839 CONTENT_DISPLAY_PROMPT,
840 CREATED_BY,
841 CREATION_DATE,
842 LAST_UPDATED_BY,
843 LAST_UPDATE_DATE,
844 LAST_UPDATE_LOGIN,
845 LANGUAGE,
846 SOURCE_LANG
847 ) select
848 l_new_ca_id,
849 CONTENT_AREA_NAME,
850 CONTENT_DISPLAY_PROMPT,
851 l_user_id,
852 sysdate,
853 l_user_id,
854 sysdate,
855 l_user_id,
856 LANGUAGE,
857 SOURCE_LANG
858 from AR_BPA_CONTENT_AREAS_TL
859 where CONTENT_AREA_ID = cabrec.CONTENT_AREA_ID;
860 END LOOP;
861
862 /* insert item_id of original template into ar_bpa_area_items_map table */
863 insert into ar_bpa_area_items
864 ( area_item_id,
865 template_id,
866 parent_area_code,
867 display_level,
868 secondary_app_id,
869 item_id,
870 display_sequence,
871 data_source_id,
872 flexfield_item_flag,
873 created_by,
874 creation_date,
875 last_updated_by,
876 last_update_date,
877 last_update_login)
878 select ar_bpa_area_items_s.nextval,
879 p_dup_template_id,
880 ca.parent_area_code,
881 ca.display_level,
882 decode(item.seeded_application_id,222,-1, item.seeded_application_id),
883 ca.item_id,
884 ca.display_sequence,
885 item.data_source_id,
886 item.flexfield_item_flag,
887 l_user_id,
888 sysdate,
889 l_user_id,
890 sysdate,
891 l_user_id
892 from ar_bpa_content_areas_b ca , ar_bpa_items_vl item
893 where ca.template_id = p_orig_template_id
894 and ca.item_id is not null
895 and ca.item_id = item.item_id
896 ;
897
898 EXCEPTION
899 WHEN OTHERS THEN
900 RAISE;
901 END create_dup_areas;
902
903 procedure DELETE_FLEXFIELD_ITEMS (
904 P_DATASRC_APP_ID in NUMBER
905 ) is
906 begin
907 delete from AR_BPA_ITEMS_TL
908 where item_id in (select item_id from ar_bpa_items_b
909 where SEEDED_APPLICATION_ID = P_DATASRC_APP_ID);
910
911 delete from AR_BPA_ITEMS_B
912 where SEEDED_APPLICATION_ID = P_DATASRC_APP_ID;
913
914 end DELETE_FLEXFIELD_ITEMS;
915
916 procedure UPDATE_VIEW_ITEM (
917 P_ITEM_ID in NUMBER default null,
918 P_ITEM_CODE in VARCHAR2,
919 P_DISPLAY_LEVEL in VARCHAR2,
920 P_DATA_SOURCE_ID in NUMBER,
921 P_DISPLAY_ENABLED_FLAG in VARCHAR2,
922 P_SEEDED_APPLICATION_ID in NUMBER,
923 P_DATA_TYPE in VARCHAR2,
924 P_COLUMN_NAME in VARCHAR2,
925 P_ITEM_NAME in VARCHAR2,
926 P_DISPLAY_PROMPT in VARCHAR2,
927 P_ITEM_DESCRIPTION in VARCHAR2,
928 P_FLEXFIELD_ITEM_FLAG in VARCHAR2,
929 P_AMOUNT_ITEM_FLAG IN VARCHAR2,
930 P_ASSIGNMENT_ENABLED_FLAG IN VARCHAR2,
931 P_DISPLAYED_MULTI_LEVEL_FLAG IN VARCHAR2,
932 P_TAX_ITEM_FLAG in VARCHAR2,
933 P_TOTALS_ENABLED_FLAG in VARCHAR2,
934 P_LINK_ENABLED_FLAG in VARCHAR2,
935 P_ITEM_TYPE in VARCHAR2
936 ) is
937 row_id varchar2(64);
938 item_id number := -1;
939 l_data_source_id number;
940 begin
941 l_data_source_id := p_data_source_id;
942 if (p_data_source_id = -1)
943 then
944 l_data_source_id := null;
945 end if;
946
947 AR_BPA_ITEMS_PKG.UPDATE_ROW (
948 X_AMOUNT_ITEM_FLAG => P_AMOUNT_ITEM_FLAG,
949 X_ASSIGNMENT_ENABLED_FLAG => P_ASSIGNMENT_ENABLED_FLAG,
950 X_DATA_SOURCE_ID => l_DATA_SOURCE_ID,
951 X_DISPLAY_ENABLED_FLAG => P_DISPLAY_ENABLED_FLAG,
952 X_DISPLAY_LEVEL => P_DISPLAY_LEVEL,
953 X_DISPLAY_PROMPT => P_DISPLAY_PROMPT,
954 X_ITEM_CODE => P_ITEM_CODE,
955 X_ITEM_DESCRIPTION => P_ITEM_DESCRIPTION,
956 X_ITEM_ID => P_ITEM_ID,
957 X_ITEM_IMAGE_FILENAME => null,
958 X_ITEM_MESSAGE_NAME => null,
959 X_ITEM_NAME => P_ITEM_NAME,
960 X_ITEM_SOURCE => 'P',
961 X_ITEM_TEXT_VALUE => null,
962 X_ITEM_TYPE => P_ITEM_TYPE,
963 X_URL_ID => null,
964 X_LAST_UPDATE_DATE => sysdate,
965 X_LAST_UPDATED_BY => 0,
966 X_LAST_UPDATE_LOGIN => 0,
967 X_DISPLAYED_MULTI_LEVEL_FLAG => P_DISPLAYED_MULTI_LEVEL_FLAG,
968 X_SEEDED_APPLICATION_ID => P_SEEDED_APPLICATION_ID,
969 X_TAX_ITEM_FLAG => P_TAX_ITEM_FLAG,
970 X_TOTALS_ENABLED_FLAG => P_TOTALS_ENABLED_FLAG,
971 X_LINK_ENABLED_FLAG => P_LINK_ENABLED_FLAG,
972 X_DATA_TYPE => P_DATA_TYPE,
973 X_COLUMN_NAME => P_COLUMN_NAME,
974 X_URLCFG_ENABLED_FLAG => null,
975 X_FLEXFIELD_ITEM_FLAG => P_FLEXFIELD_ITEM_FLAG);
976 exception
977 when NO_DATA_FOUND then
978 select ar_bpa_items_s.nextval
979 INTO item_id
980 from dual;
981 AR_BPA_ITEMS_PKG.INSERT_ROW (
982 X_ROWID => row_id,
983 X_AMOUNT_ITEM_FLAG => P_AMOUNT_ITEM_FLAG,
984 X_ASSIGNMENT_ENABLED_FLAG => P_ASSIGNMENT_ENABLED_FLAG,
985 X_DATA_SOURCE_ID => l_DATA_SOURCE_ID,
986 X_DISPLAY_ENABLED_FLAG => P_DISPLAY_ENABLED_FLAG,
987 X_DISPLAY_LEVEL => P_DISPLAY_LEVEL,
988 X_DISPLAY_PROMPT => P_DISPLAY_PROMPT,
989 X_ITEM_CODE => P_ITEM_CODE,
990 X_ITEM_DESCRIPTION => P_ITEM_DESCRIPTION,
991 X_ITEM_ID => item_id,
992 X_ITEM_IMAGE_FILENAME => null,
993 X_ITEM_MESSAGE_NAME => null,
994 X_ITEM_NAME => P_ITEM_NAME,
995 X_ITEM_SOURCE => 'P',
996 X_ITEM_TEXT_VALUE => null,
997 X_ITEM_TYPE => P_ITEM_TYPE,
998 X_URL_ID => null,
999 X_CREATION_DATE => sysdate,
1000 X_CREATED_BY => 0,
1001 X_LAST_UPDATE_DATE => sysdate,
1002 X_LAST_UPDATED_BY => 0,
1003 X_LAST_UPDATE_LOGIN => 0,
1004 X_DISPLAYED_MULTI_LEVEL_FLAG => P_DISPLAYED_MULTI_LEVEL_FLAG,
1005 X_SEEDED_APPLICATION_ID => P_SEEDED_APPLICATION_ID,
1006 X_TAX_ITEM_FLAG => P_TAX_ITEM_FLAG,
1007 X_TOTALS_ENABLED_FLAG => P_TOTALS_ENABLED_FLAG,
1008 X_LINK_ENABLED_FLAG => P_LINK_ENABLED_FLAG,
1009 X_DATA_TYPE => P_DATA_TYPE,
1010 X_COLUMN_NAME => P_COLUMN_NAME,
1011 X_URLCFG_ENABLED_FLAG => null,
1012 X_FLEXFIELD_ITEM_FLAG => P_FLEXFIELD_ITEM_FLAG);
1013 end UPDATE_VIEW_ITEM;
1014
1015 procedure DELETE_VIEW_ITEM (
1016 P_ITEM_ID in NUMBER
1017 ) is
1018 begin
1019 AR_BPA_ITEMS_PKG.DELETE_ROW(
1020 X_ITEM_ID => P_ITEM_ID );
1021 end DELETE_VIEW_ITEM;
1022
1023 begin
1024
1025 --arp_global.init_global;
1026 --arp_standard.init_standard;
1027 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
1028
1029 end AR_BPA_UTILS_PKG;