[Home] [Help]
PACKAGE BODY: APPS.INV_ENI_ITEMS_STAR_PKG
Source
1 Package Body INV_ENI_ITEMS_STAR_PKG AS
2 /* $Header: INVENICB.pls 120.4 2005/11/07 02:19:23 lparihar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ENI_ITEMS_STAR_PKG';
5
6
7 --**********************************************************************
8 -- Maintains STAR table when changes are detected on MTL_ITEM_CATEGORIES
9 --**********************************************************************
10
11 PROCEDURE Sync_Category_Assignments(
12 p_api_version IN NUMBER
13 ,p_init_msg_list IN VARCHAR2
14 ,p_inventory_item_id IN NUMBER
15 ,p_organization_id IN NUMBER
16 ,p_category_set_id IN NUMBER
17 ,p_old_category_id IN NUMBER
18 ,p_new_category_id IN NUMBER
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_msg_count OUT NOCOPY NUMBER
21 ,x_msg_data OUT NOCOPY VARCHAR2) IS
22 BEGIN
23
24 IF (INV_ITEM_UTIL.Object_Exists
25 (p_object_type => 'PACKAGE',
26 p_object_name => 'ENI_ITEMS_STAR_PKG') = 'Y')
27 THEN
28
29 EXECUTE IMMEDIATE
30 ' BEGIN '||
31 ' ENI_ITEMS_STAR_PKG.Sync_Category_Assignments( '||
32 ' p_api_version => :p_api_version '||
33 ' , p_init_msg_list => :p_init_msg_list '||
34 ' , p_inventory_item_id => :p_Inventory_Item_ID '||
35 ' , p_organization_id => :p_Organization_ID '||
36 ' , x_return_status => :x_return_status '||
37 ' , x_msg_count => :x_msg_count '||
38 ' , x_msg_data => :x_msg_data ); '||
39 ' END;'
40 USING IN p_api_version,
41 IN p_init_msg_list,
42 IN p_inventory_item_id,
43 IN p_organization_id,
44 OUT x_return_status,
45 OUT x_msg_count,
46 OUT x_msg_data;
47
48 END IF;
49
50 IF ((INV_ITEM_UTIL.Object_Exists
51 (p_object_type => 'PACKAGE',
52 p_object_name => 'ENI_UPD_ASSGN') = 'Y')
53 AND x_return_status ='S' )
54 THEN
55 EXECUTE IMMEDIATE
56 ' BEGIN '||
57 ' ENI_UPD_ASSGN.UPDATE_ASSGN_FLAG( '||
58 ' p_new_category_id => :p_new_category_id '||
59 ' , p_old_category_id => :p_old_category_id '||
60 ' , x_return_status => :x_return_status '||
61 ' , x_msg_count => :x_msg_count '||
62 ' , x_msg_data => :x_msg_data ); '||
63 ' END;'
64 USING IN p_new_category_id,
65 IN p_old_category_id,
66 OUT x_return_status,
67 OUT x_msg_count,
68 OUT x_msg_data;
69
70 END IF;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 x_return_status := 'U';
75 END;
76
77 PROCEDURE Update_ENI_Staging_Table(
78 p_mode_flag IN VARCHAR2
79 ,p_category_set_id IN NUMBER
80 ,p_category_id IN NUMBER
81 ,p_language_code IN VARCHAR2
82 ,x_return_status OUT NOCOPY VARCHAR2
83 ,x_msg_count OUT NOCOPY NUMBER
84 ,x_msg_data OUT NOCOPY VARCHAR2) IS
85 ----------------------------------------------------------------------
86 -- Start of comments
87 --
88 -- Procedure name : Update_ENI_Staging_Table
89 -- Type : Public
90 -- Pre-reqs : None
91 -- Requirement : Bug 3134279 (11.5.10 Requirement)
92 -- Functionality : To insert data into eni_denorm_hrchy_stg so that
93 -- DBI team can use the same for denormalization
94 -- Notes :
95 --
96 -- History :
97 -- 09-SEP-2003 Sridhar R Creation
98 --
99 -- END OF comments
100 ----------------------------------------------------------------------
101
102 l_object_type CONSTANT VARCHAR2(20) := 'CATEGORY_SET';
103 BEGIN
104 IF (INV_ITEM_UTIL.Object_Exists
105 (p_object_type => 'PACKAGE',
106 p_object_name => 'ENI_DENORM_HRCHY') = 'Y') THEN
107 -- the package exists
108 EXECUTE IMMEDIATE
109 ' BEGIN '||
110 ' ENI_DENORM_HRCHY.Insert_Into_Staging ( '||
111 ' p_object_type => :b_object_type '||
112 ' ,p_object_id => :b_caregory_set_id '||
113 ' ,p_child_id => :b_category_id '||
114 ' ,p_parent_id => NULL '||
115 ' ,p_mode_flag => :b_mode_flag '||
116 ' ,p_language_code => :b_language_code '||
117 ' ,x_return_status => :b_return_status '||
118 ' ,x_msg_count => :b_msg_count '||
119 ' ,x_msg_data => :b_msg_data ); '||
120 ' END;'
121 USING IN l_object_type,
122 IN p_category_set_id,
123 IN p_category_id,
124 IN p_mode_flag,
125 IN p_language_code,
126 OUT x_return_status,
127 OUT x_msg_count,
128 OUT x_msg_data;
129 IF (x_return_status <> 'S') THEN
130 RETURN;
131 END IF;
132 END IF; -- package exists
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 x_return_status := 'U';
137 -- Bug 4569555 RAISE;
138 END Update_ENI_Staging_Table;
139
140 PROCEDURE SYNC_STAR_ITEMS_FROM_IOI(
141 p_api_version IN NUMBER
142 ,p_init_msg_list IN VARCHAR2 := 'F'
143 ,p_set_process_id IN NUMBER
144 ,x_return_status OUT NOCOPY VARCHAR2
145 ,x_msg_count OUT NOCOPY NUMBER
146 ,x_msg_data OUT NOCOPY VARCHAR2) IS
147 BEGIN
148
149 IF (INV_ITEM_UTIL.Object_Exists
150 (p_object_type => 'PACKAGE',
151 p_object_name => 'ENI_ITEMS_STAR_PKG') = 'Y') THEN
152 -- the package exists
153 EXECUTE IMMEDIATE
154 ' BEGIN '||
155 ' ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI '||
156 ' ( '||
157 ' p_api_version => :p_api_version '||
158 ' , p_init_msg_list => :p_init_msg_list '||
159 ' , p_set_process_id => :p_set_process_id '||
160 ' , x_return_status => :x_return_status '||
161 ' , x_msg_count => :x_msg_count '||
162 ' , x_msg_data => :x_msg_data '||
163 ' ); '||
164 ' END;'
165 USING IN p_api_version,
166 IN p_init_msg_list,
167 IN p_set_process_id,
168 OUT x_return_status,
169 OUT x_msg_count,
170 OUT x_msg_data;
171
172 END IF;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 x_return_status := 'U';
177 -- Bug 4569555 RAISE;
178 END SYNC_STAR_ITEMS_FROM_IOI;
179
180 PROCEDURE Sync_Star_ItemCatg_From_COI(
181 p_api_version IN NUMBER
182 ,p_init_msg_list IN VARCHAR2 := 'F'
183 ,p_set_process_id IN NUMBER
184 ,x_return_status OUT NOCOPY VARCHAR2
185 ,x_msg_count OUT NOCOPY NUMBER
186 ,x_msg_data OUT NOCOPY VARCHAR2) IS
187 BEGIN
188
189 IF (INV_ITEM_UTIL.Object_Exists
190 (p_object_type => 'PACKAGE',
191 p_object_name => 'ENI_ITEMS_STAR_PKG') = 'Y') THEN
192 -- the package exists
193 EXECUTE IMMEDIATE
194 ' BEGIN '||
195 ' ENI_ITEMS_STAR_PKG.Sync_Star_ItemCatg_From_COI '||
196 ' ( '||
197 ' p_api_version => :p_api_version '||
198 ' , p_init_msg_list => :p_init_msg_list '||
199 ' , p_set_process_id => :p_set_process_id '||
200 ' , x_return_status => :x_return_status '||
201 ' , x_msg_count => :x_msg_count '||
202 ' , x_msg_data => :x_msg_data '||
203 ' ); '||
204 ' END;'
205 USING IN p_api_version,
206 IN p_init_msg_list,
207 IN p_set_process_id,
208 OUT x_return_status,
209 OUT x_msg_count,
210 OUT x_msg_data;
211
212 END IF;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 x_return_status := 'U';
217 -- Bug 4569555 RAISE;
218 END Sync_Star_ItemCatg_From_COI;
219
220
221 End INV_ENI_ITEMS_STAR_PKG;