[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_UTILS_PKG
Source
1 package body AR_BPA_UTILS_PKG as
2 /* $Header: ARBPAUTB.pls 120.8 2006/06/20 21:41:15 lishao 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 FUNCTION fn_get_contact_name (p_contact_id IN NUMBER) return varchar2 IS
513 CURSOR c_cont_name( id in number ) IS
514 SELECT party.person_first_name ||' '|| party.person_last_name attn
515 FROM hz_cust_account_roles acct_role,
516 hz_relationships rel,
517 hz_parties party
518 WHERE acct_role.cust_account_role_id = id
519 AND acct_role.ROLE_TYPE = 'CONTACT'
520 AND acct_role.party_id = rel.party_id
521 AND rel.subject_id = party.party_id
522 AND rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
523 AND rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
524 AND rel.DIRECTIONAL_FLAG = 'F';
525
526 l_contact_name varchar2(240) := null;
527 BEGIN
528 FOR crec IN c_cont_name(p_contact_id)
529 LOOP
530 l_contact_name := crec.attn;
531 exit; -- break
532 END LOOP;
533 return l_contact_name;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 return null;
538 END;
539
540 /* Return contact phone */
541 FUNCTION fn_get_phone (p_contact_id IN NUMBER) return varchar2 IS
542 CURSOR c_cont_phone( id in number ) IS
543 select min(decode(cont_point.contact_point_type,'TLX',
544 cont_point.telex_number, cont_point.phone_number)) contact_phone
545 from hz_contact_points cont_point,
546 hz_cust_account_roles acct_role
547 where acct_role.cust_account_role_id = id
548 and acct_role.party_id = cont_point.owner_table_id
549 and cont_point.owner_table_name = 'HZ_PARTIES'
550 and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'GEN';
551 l_phone varchar2(240) := null;
552 BEGIN
553 FOR crec IN c_cont_phone(p_contact_id)
554 LOOP
555 l_phone := crec.contact_phone;
556 exit; -- break
557 END LOOP;
558
559 return l_phone;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 return null;
564 END;
565
566 /* Return contact fax */
567 FUNCTION fn_get_fax (p_contact_id IN NUMBER) return varchar2 IS
568 CURSOR c_cont_fax( id in number ) IS
569 select min(decode(cont_point.contact_point_type,'TLX',
570 cont_point.telex_number, cont_point.phone_number)) contact_fax
571 from hz_contact_points cont_point,
572 hz_cust_account_roles acct_role
573 where acct_role.cust_account_role_id = id
574 and acct_role.party_id = cont_point.owner_table_id
575 and cont_point.owner_table_name = 'HZ_PARTIES'
576 and nvl(cont_point.phone_line_type, cont_point.contact_point_type) = 'FAX';
577 l_fax varchar2(240) := null;
578 BEGIN
579 FOR crec IN c_cont_fax(p_contact_id)
580 LOOP
581 l_fax := crec.contact_fax;
582 exit; -- break
583 END LOOP;
584
585 return l_fax;
586
587 EXCEPTION
588 WHEN OTHERS THEN
589 return null;
590 END;
591
592 function get_tax_description(
593 tax_rate in number,
594 vat_tax_id in number,
595 tax_exemption_id in number,
596 location_rate_id in number,
597 tax_precedence in number,
598 D_euro_taxable_amount in varchar2 ) return varchar2 is
599
600 d varchar2(240);
601 e varchar2(240);
602
603 cursor tax_type_c( id in number ) is
604 select t.tax_type_code,t.compounding_precedence
605 from zx_taxes_vl t,zx_rates_vl r
606 where t.tax_regime_code = r.tax_regime_code
607 and t.tax = r.tax
608 and r.tax_rate_id = id;
609
610 cursor vat_tax_c( id in number ) is
611 select tax_rate_code as tax_code, tax_rate_name as printed_tax_name from zx_rates_vl where tax_rate_id = id;
612
613 cursor exem_c( id in number ) is
614 select
615 decode(nvl(product_id,-999),-999,'CUSTOMER','ITEM') as exemption_type,
616 rate_modifier as percent_exempt
617 from zx_exemptions
618 where tax_exemption_id = id;
619
620 cursor tax_type_name_c(code in varchar2) is
621 select meaning
622 from fnd_lookups
623 where lookup_type = 'ZX_TAX_TYPE_CATEGORY'
624 and lookup_code = code;
625
626
627 vcode varchar2(60);
628 vname varchar2(200);
629 vcode_name varchar2(60);
630 vtype_code varchar2(60);
631 vprecedence number;
632 vtaxtypename varchar(100);
633
634 etype varchar2( 30 );
635 pexempt number;
636
637 begin
638 d := null;
639 vtype_code := null;
640
641 if vat_tax_id is not null
642 then
643 open tax_type_c(vat_tax_id);
644 fetch tax_type_c into vtype_code, vprecedence;
645
646 if tax_type_c%NOTFOUND
647 then
648 open tax_type_name_c(vtype_code);
649 fetch tax_type_name_c into vtaxtypename;
650 d := vtaxtypename||' @ '||ltrim(to_char(tax_rate, '990D99'));
651 close tax_type_name_c;
652 else
653 if (vtype_code = 'SALES')
654 then
655 d := d || ' ' || arp_standard.fnd_message( 'AR_REPORTS_SALES_TAX', 'TAX_RATE', ltrim(to_char(tax_rate, '9990D00')));
656 ELSE
657 if ( vtype_code = 'VAT' or (vtype_code is null)) then
658 open vat_tax_c(vat_tax_id);
659 fetch vat_tax_c into vcode, vname;
660
661 vcode_name := vcode; -- Print Tax Code
662 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')));
663
664 close vat_tax_c;
665 end if;
666 END IF;
667 end if;
668
669 close tax_type_c;
670 end if;
671
672 if tax_exemption_id is not null and ( vat_tax_id is null )
673 then
674 open exem_c( tax_exemption_id );
675 fetch exem_c into etype, pexempt;
676 if exem_c%NOTFOUND
677 then
678 d := d || ' ' || arp_standard.fnd_message('AR_IP_NO_TAX_EXEM_ID',
679 'TAX_EXEMPTION_ID', ltrim(to_char( tax_exemption_id )));
680 else
681 d := d || ' ' || arp_standard.fnd_message( 'AR_IP_TAX_EXEMPTION', 'EXEMPTION_TYPE', initcap(etype),
682 'PERCENT_EXEMPT', ltrim(to_char(pexempt, '990D99')));
683 end if;
684 close exem_c;
685
686 end if;
687
688 if vprecedence is not null
689 then
690 d := d || ' ' || arp_standard.fnd_message( 'AR_REPORTS_PRECEDENCE') || ' ' ||
691 ltrim(to_char( vprecedence,'9990') );
692 end if;
693
694 -- If you know what tax it is but no message generated so far, we create a general message.
695 if vtype_code is not null and d is null then
696 open tax_type_name_c(vtype_code);
697 fetch tax_type_name_c into vtaxtypename;
698 d := vtaxtypename||' @ '||ltrim(to_char(tax_rate, '990D99'));
699 close tax_type_name_c;
700
701 end if;
702
703 return( ltrim(d, ' '));
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 return null;
708
709 end ;
710
711 /* Create duplicate content areas and area_items_map while duplicating a template.
712 Template row is already duplicated in the framework and will get committed
713 along with these inserts.
714 */
715 procedure create_dup_areas(
716 p_orig_template_id IN NUMBER,
717 p_dup_template_id IN NUMBER
718 ) IS
719
720
721 cursor c_org_ca_b IS
722 select
723 CA.ITEM_LABEL_STYLE,
724 CA.ITEM_VALUE_STYLE,
725 CA.CONTENT_DISP_PROMPT_STYLE,
726 CA.INVOICE_LINE_TYPE,
727 CA.AREA_CODE,
728 CA.PARENT_AREA_CODE,
729 CA.LINE_REGION_FLAG,
730 CA.CONTENT_COUNT,
731 CA.CONTENT_AREA_RIGHT_SPACE,
732 CA.CONTENT_AREA_TOP_SPACE,
733 CA.CONTENT_AREA_BOTTOM_SPACE,
734 CA.DISPLAY_LEVEL,
735 CA.CONTENT_TYPE,
736 CA.CONTENT_ORIENTATION,
737 CA.CONTENT_STYLE_ID,
738 CA.ITEM_ID,
739 CA.URL_ID,
740 CA.DISPLAY_SEQUENCE,
741 CA.CONTENT_AREA_WIDTH,
742 CA.CONTENT_AREA_LEFT_SPACE,
743 CA.ITEM_COLUMN_WIDTH,
744 CA.CONTENT_AREA_ID
745 from ar_bpa_content_areas_b CA
746 where CA.template_id = p_orig_template_id
747 order by CA.content_area_id;
748
749 l_new_ca_id number := 0;
750 l_user_id number := -1;
751 BEGIN
752
753 /*
754 1. insert into ca_b, ca_tl
755 2. insert into area_items
756 */
757 FOR cabrec in c_org_ca_b
758 LOOP
759 select ar_bpa_content_areas_s.nextval
760 into l_new_ca_id
761 from dual;
762
763 insert into AR_BPA_CONTENT_AREAS_B (
764 ITEM_LABEL_STYLE,
765 ITEM_VALUE_STYLE,
766 CONTENT_DISP_PROMPT_STYLE,
767 INVOICE_LINE_TYPE,
768 AREA_CODE,
769 PARENT_AREA_CODE,
770 LINE_REGION_FLAG,
771 CONTENT_COUNT,
772 CONTENT_AREA_RIGHT_SPACE,
773 CONTENT_AREA_TOP_SPACE,
774 CONTENT_AREA_BOTTOM_SPACE,
775 CONTENT_AREA_ID,
776 DISPLAY_LEVEL,
777 CONTENT_TYPE,
778 CONTENT_ORIENTATION,
779 TEMPLATE_ID,
780 CONTENT_STYLE_ID,
781 ITEM_ID,
782 URL_ID,
783 DISPLAY_SEQUENCE,
784 CONTENT_AREA_WIDTH,
785 CONTENT_AREA_LEFT_SPACE,
786 CREATION_DATE,
787 CREATED_BY,
788 LAST_UPDATE_DATE,
789 LAST_UPDATED_BY,
790 LAST_UPDATE_LOGIN,
791 ITEM_COLUMN_WIDTH
792 ) values (
793 cabrec.ITEM_LABEL_STYLE,
794 cabrec.ITEM_VALUE_STYLE,
795 cabrec.CONTENT_DISP_PROMPT_STYLE,
796 cabrec.INVOICE_LINE_TYPE,
797 cabrec.AREA_CODE,
798 cabrec.PARENT_AREA_CODE,
799 cabrec.LINE_REGION_FLAG,
800 cabrec.CONTENT_COUNT,
801 cabrec.CONTENT_AREA_RIGHT_SPACE,
802 cabrec.CONTENT_AREA_TOP_SPACE,
803 cabrec.CONTENT_AREA_BOTTOM_SPACE,
804 l_new_ca_id,
805 cabrec.DISPLAY_LEVEL,
806 cabrec.CONTENT_TYPE,
807 cabrec.CONTENT_ORIENTATION,
808 p_dup_template_id,
809 cabrec.CONTENT_STYLE_ID,
810 cabrec.ITEM_ID,
811 cabrec.URL_ID,
812 cabrec.DISPLAY_SEQUENCE,
813 cabrec.CONTENT_AREA_WIDTH,
814 cabrec.CONTENT_AREA_LEFT_SPACE,
815 sysdate,
816 l_user_id,
817 sysdate,
818 l_user_id,
819 l_user_id,
820 cabrec.ITEM_COLUMN_WIDTH
821 );
822
823 insert into AR_BPA_CONTENT_AREAS_TL (
824 CONTENT_AREA_ID,
825 CONTENT_AREA_NAME,
826 CONTENT_DISPLAY_PROMPT,
827 CREATED_BY,
828 CREATION_DATE,
829 LAST_UPDATED_BY,
830 LAST_UPDATE_DATE,
831 LAST_UPDATE_LOGIN,
832 LANGUAGE,
833 SOURCE_LANG
834 ) select
835 l_new_ca_id,
836 CONTENT_AREA_NAME,
837 CONTENT_DISPLAY_PROMPT,
838 l_user_id,
839 sysdate,
840 l_user_id,
841 sysdate,
842 l_user_id,
843 LANGUAGE,
844 SOURCE_LANG
845 from AR_BPA_CONTENT_AREAS_TL
846 where CONTENT_AREA_ID = cabrec.CONTENT_AREA_ID;
847 END LOOP;
848
849 /* insert item_id of original template into ar_bpa_area_items_map table */
850 insert into ar_bpa_area_items
851 ( area_item_id,
852 template_id,
853 parent_area_code,
854 display_level,
855 secondary_app_id,
856 item_id,
857 display_sequence,
858 data_source_id,
859 flexfield_item_flag,
860 created_by,
861 creation_date,
862 last_updated_by,
863 last_update_date,
864 last_update_login)
865 select ar_bpa_area_items_s.nextval,
866 p_dup_template_id,
867 ca.parent_area_code,
868 ca.display_level,
869 decode(item.seeded_application_id,222,-1, item.seeded_application_id),
870 ca.item_id,
871 ca.display_sequence,
872 item.data_source_id,
873 item.flexfield_item_flag,
874 l_user_id,
875 sysdate,
876 l_user_id,
877 sysdate,
878 l_user_id
879 from ar_bpa_content_areas_b ca , ar_bpa_items_vl item
880 where ca.template_id = p_orig_template_id
881 and ca.item_id is not null
882 and ca.item_id = item.item_id
883 ;
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 RAISE;
888 END create_dup_areas;
889
890 procedure DELETE_FLEXFIELD_ITEMS (
891 P_DATASRC_APP_ID in NUMBER
892 ) is
893 begin
894 delete from AR_BPA_ITEMS_TL
895 where item_id in (select item_id from ar_bpa_items_b
896 where SEEDED_APPLICATION_ID = P_DATASRC_APP_ID);
897
898 delete from AR_BPA_ITEMS_B
899 where SEEDED_APPLICATION_ID = P_DATASRC_APP_ID;
900
901 end DELETE_FLEXFIELD_ITEMS;
902
903 procedure UPDATE_VIEW_ITEM (
904 P_ITEM_ID in NUMBER default null,
905 P_ITEM_CODE in VARCHAR2,
906 P_DISPLAY_LEVEL in VARCHAR2,
907 P_DATA_SOURCE_ID in NUMBER,
908 P_DISPLAY_ENABLED_FLAG in VARCHAR2,
909 P_SEEDED_APPLICATION_ID in NUMBER,
910 P_DATA_TYPE in VARCHAR2,
911 P_COLUMN_NAME in VARCHAR2,
912 P_ITEM_NAME in VARCHAR2,
913 P_DISPLAY_PROMPT in VARCHAR2,
914 P_ITEM_DESCRIPTION in VARCHAR2,
915 P_FLEXFIELD_ITEM_FLAG in VARCHAR2,
916 P_AMOUNT_ITEM_FLAG IN VARCHAR2,
917 P_ASSIGNMENT_ENABLED_FLAG IN VARCHAR2,
918 P_DISPLAYED_MULTI_LEVEL_FLAG IN VARCHAR2,
919 P_TAX_ITEM_FLAG in VARCHAR2,
920 P_TOTALS_ENABLED_FLAG in VARCHAR2,
921 P_LINK_ENABLED_FLAG in VARCHAR2,
922 P_ITEM_TYPE in VARCHAR2
923 ) is
924 row_id varchar2(64);
925 item_id number := -1;
926 l_data_source_id number;
927 begin
928 l_data_source_id := p_data_source_id;
929 if (p_data_source_id = -1)
930 then
931 l_data_source_id := null;
932 end if;
933
934 AR_BPA_ITEMS_PKG.UPDATE_ROW (
935 X_AMOUNT_ITEM_FLAG => P_AMOUNT_ITEM_FLAG,
936 X_ASSIGNMENT_ENABLED_FLAG => P_ASSIGNMENT_ENABLED_FLAG,
937 X_DATA_SOURCE_ID => l_DATA_SOURCE_ID,
938 X_DISPLAY_ENABLED_FLAG => P_DISPLAY_ENABLED_FLAG,
939 X_DISPLAY_LEVEL => P_DISPLAY_LEVEL,
940 X_DISPLAY_PROMPT => P_DISPLAY_PROMPT,
941 X_ITEM_CODE => P_ITEM_CODE,
942 X_ITEM_DESCRIPTION => P_ITEM_DESCRIPTION,
943 X_ITEM_ID => P_ITEM_ID,
944 X_ITEM_IMAGE_FILENAME => null,
945 X_ITEM_MESSAGE_NAME => null,
946 X_ITEM_NAME => P_ITEM_NAME,
947 X_ITEM_SOURCE => 'P',
948 X_ITEM_TEXT_VALUE => null,
949 X_ITEM_TYPE => P_ITEM_TYPE,
950 X_URL_ID => null,
951 X_LAST_UPDATE_DATE => sysdate,
952 X_LAST_UPDATED_BY => 0,
953 X_LAST_UPDATE_LOGIN => 0,
954 X_DISPLAYED_MULTI_LEVEL_FLAG => P_DISPLAYED_MULTI_LEVEL_FLAG,
955 X_SEEDED_APPLICATION_ID => P_SEEDED_APPLICATION_ID,
956 X_TAX_ITEM_FLAG => P_TAX_ITEM_FLAG,
957 X_TOTALS_ENABLED_FLAG => P_TOTALS_ENABLED_FLAG,
958 X_LINK_ENABLED_FLAG => P_LINK_ENABLED_FLAG,
959 X_DATA_TYPE => P_DATA_TYPE,
960 X_COLUMN_NAME => P_COLUMN_NAME,
961 X_URLCFG_ENABLED_FLAG => null,
962 X_FLEXFIELD_ITEM_FLAG => P_FLEXFIELD_ITEM_FLAG);
963 exception
964 when NO_DATA_FOUND then
965 select ar_bpa_items_s.nextval
966 INTO item_id
967 from dual;
968 AR_BPA_ITEMS_PKG.INSERT_ROW (
969 X_ROWID => row_id,
970 X_AMOUNT_ITEM_FLAG => P_AMOUNT_ITEM_FLAG,
971 X_ASSIGNMENT_ENABLED_FLAG => P_ASSIGNMENT_ENABLED_FLAG,
972 X_DATA_SOURCE_ID => l_DATA_SOURCE_ID,
973 X_DISPLAY_ENABLED_FLAG => P_DISPLAY_ENABLED_FLAG,
974 X_DISPLAY_LEVEL => P_DISPLAY_LEVEL,
975 X_DISPLAY_PROMPT => P_DISPLAY_PROMPT,
976 X_ITEM_CODE => P_ITEM_CODE,
977 X_ITEM_DESCRIPTION => P_ITEM_DESCRIPTION,
978 X_ITEM_ID => item_id,
979 X_ITEM_IMAGE_FILENAME => null,
980 X_ITEM_MESSAGE_NAME => null,
981 X_ITEM_NAME => P_ITEM_NAME,
982 X_ITEM_SOURCE => 'P',
983 X_ITEM_TEXT_VALUE => null,
984 X_ITEM_TYPE => P_ITEM_TYPE,
985 X_URL_ID => null,
986 X_CREATION_DATE => sysdate,
987 X_CREATED_BY => 0,
988 X_LAST_UPDATE_DATE => sysdate,
989 X_LAST_UPDATED_BY => 0,
990 X_LAST_UPDATE_LOGIN => 0,
991 X_DISPLAYED_MULTI_LEVEL_FLAG => P_DISPLAYED_MULTI_LEVEL_FLAG,
992 X_SEEDED_APPLICATION_ID => P_SEEDED_APPLICATION_ID,
993 X_TAX_ITEM_FLAG => P_TAX_ITEM_FLAG,
994 X_TOTALS_ENABLED_FLAG => P_TOTALS_ENABLED_FLAG,
995 X_LINK_ENABLED_FLAG => P_LINK_ENABLED_FLAG,
996 X_DATA_TYPE => P_DATA_TYPE,
997 X_COLUMN_NAME => P_COLUMN_NAME,
998 X_URLCFG_ENABLED_FLAG => null,
999 X_FLEXFIELD_ITEM_FLAG => P_FLEXFIELD_ITEM_FLAG);
1000 end UPDATE_VIEW_ITEM;
1001
1002 procedure DELETE_VIEW_ITEM (
1003 P_ITEM_ID in NUMBER
1004 ) is
1005 begin
1006 AR_BPA_ITEMS_PKG.DELETE_ROW(
1007 X_ITEM_ID => P_ITEM_ID );
1008 end DELETE_VIEW_ITEM;
1009
1010 begin
1011
1012 --arp_global.init_global;
1013 --arp_standard.init_standard;
1014 pg_debug := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
1015
1016 end AR_BPA_UTILS_PKG;