1 Package Body pqh_synchronize_local_tct as
2 /* $Header: pqtatlcp.pkb 115.1 2003/05/22 21:37:42 srajakum noship $ */
3 --
4 --
5 Procedure copy_global_attr_to_local(errbuf out nocopy varchar2
6 , retcode out nocopy number
7 , p_short_name in varchar2) is
8 --
9 cursor c1 (p_transaction_category_id number) is
10 select *
11 from pqh_txn_category_attributes
12 where transaction_category_id = p_transaction_category_id;
13 cursor c2 (p_short_name varchar2)is
14 select transaction_category_id,business_group_id
15 from pqh_transaction_categories
16 where short_name = p_short_name
17 and business_group_id is not null;
18 l_ovn number;
19 l_txn_cat_attr_id number;
20 l_glb_txn_cat_id number;
21 l_check varchar2(30);
22 --
23 l_lcl_txn_cat_attr_id pqh_txn_category_attributes.txn_category_attribute_id%type;
24 l_lcl_txn_cat_attr_ovn pqh_txn_category_attributes.object_version_number%type;
25 --
26 begin
27 -- idea of pulling short_name is that same script can be reused for
28 -- other transaction categories, if required.
29 -- pull the global txn cat for the short name, as attributes are
30 -- attached with global transaction category by default.
31 --
32 l_glb_txn_cat_id := pqh_workflow.get_txn_cat(p_short_name);
33 --
34 for i in c1(l_glb_txn_cat_id) loop
35 -- pull all the txn_category_attributes for the global txn cat
36 for j in c2(p_short_name) loop
37 -- pull all txn_cats irrespective of the Business group for the short name
38 begin
39 select txn_category_attribute_id,
40 object_version_number
41 into l_lcl_txn_cat_attr_id, l_lcl_txn_cat_attr_ovn
42 from pqh_txn_category_attributes
43 where transaction_category_id = j.transaction_category_id
44 and attribute_id = i.attribute_id;
45 --
46 -- If the attribute exists for the local transaction category, update its value set.
47 --
48 if i.value_set_id IS NOT NULL then
49 --
50 pqh_txn_cat_attributes_api.update_TXN_CAT_ATTRIBUTE(
51 p_validate => false
52 ,p_txn_category_attribute_id => l_lcl_txn_cat_attr_id
53 ,p_value_set_id => i.value_set_id
54 ,p_value_style_cd => i.value_style_cd
55 ,p_object_version_number => l_lcl_txn_cat_attr_ovn
56 ,p_effective_date => trunc(sysdate)
57 );
58 End if;
59 --
60 exception
61 when no_data_found then
62 -- attribute doesnot exist, call the insert api.
63 pqh_txn_cat_attributes_api.create_TXN_CAT_ATTRIBUTE
64 (
65 p_validate => false
66 ,p_txn_category_attribute_id => l_txn_cat_attr_id
67 ,p_attribute_id => i.attribute_id
68 ,p_transaction_category_id => j.transaction_category_id
69 ,p_value_set_id => i.value_set_id
70 ,p_object_version_number => l_ovn
71 ,p_transaction_table_route_id => i.transaction_table_route_id
72 ,p_form_column_name => i.form_column_name
73 ,p_identifier_flag => i.identifier_flag
74 ,p_list_identifying_flag => null
75 ,p_member_identifying_flag => null
76 ,p_refresh_flag => i.refresh_flag
77 ,p_select_flag => i.select_flag
78 ,p_value_style_cd => i.value_style_cd
79 ,p_effective_date => trunc(sysdate)
80 );
81 end;
82 end loop;
83 end loop;
84 EXCEPTION
85 When Others Then
86 errbuf := SQLERRM;
87 fnd_file.put_line(fnd_file.log, errbuf);
88 RAISE;
89 end copy_global_attr_to_local;
90 --
91 --
92 END pqh_synchronize_local_tct;
[Home] [Help]