[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.12020000.2 2012/07/13 08:20:14 swuppala ship $ */
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 --Start: bug 12416965
30 /*EXECUTE IMMEDIATE
31 ' BEGIN '||
32 ' ENI_ITEMS_STAR_PKG.Sync_Category_Assignments( '||
33 ' p_api_version => :p_api_version '||
34 ' , p_init_msg_list => :p_init_msg_list '||
35 ' , p_inventory_item_id => :p_Inventory_Item_ID '||
36 ' , p_organization_id => :p_Organization_ID '||
37 ' , x_return_status => :x_return_status '||
38 ' , x_msg_count => :x_msg_count '||
39 ' , x_msg_data => :x_msg_data ); '||
40 ' END;'
41 USING IN p_api_version,
42 IN p_init_msg_list,
43 IN p_inventory_item_id,
44 IN p_organization_id,
45 OUT x_return_status,
46 OUT x_msg_count,
47 OUT x_msg_data; */
48
49 ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
50 p_api_version => p_api_version
51 , p_init_msg_list => p_init_msg_list
52 , p_inventory_item_id => p_inventory_item_id
53 , p_organization_id => p_organization_id
54 , x_return_status => x_return_status
55 , x_msg_count => x_msg_count
56 , x_msg_data => x_msg_data );
57
58 --End: Bug 12416965
59
60 END IF;
61
62 IF ((INV_ITEM_UTIL.Object_Exists
63 (p_object_type => 'PACKAGE',
64 p_object_name => 'ENI_UPD_ASSGN') = 'Y')
65 AND x_return_status ='S' )
66 THEN
67 --Start: bug 12416965
68 /* EXECUTE IMMEDIATE
69 ' BEGIN '||
70 ' ENI_UPD_ASSGN.UPDATE_ASSGN_FLAG( '||
71 ' p_new_category_id => :p_new_category_id '||
72 ' , p_old_category_id => :p_old_category_id '||
73 ' , x_return_status => :x_return_status '||
74 ' , x_msg_count => :x_msg_count '||
75 ' , x_msg_data => :x_msg_data ); '||
76 ' END;'
77 USING IN p_new_category_id,
78 IN p_old_category_id,
79 OUT x_return_status,
80 OUT x_msg_count,
81 OUT x_msg_data; */
82
83 ENI_UPD_ASSGN.UPDATE_ASSGN_FLAG(
84 p_new_category_id => p_new_category_id
85 , p_old_category_id => p_old_category_id
86 , x_return_status => x_return_status
87 , x_msg_count => x_msg_count
88 , x_msg_data => x_msg_data );
89
90 --End: bug 12416965
91
92 END IF;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 x_return_status := 'U';
97 END;
98
99 PROCEDURE Update_ENI_Staging_Table(
100 p_mode_flag IN VARCHAR2
101 ,p_category_set_id IN NUMBER
102 ,p_category_id IN NUMBER
103 ,p_language_code IN VARCHAR2
104 ,x_return_status OUT NOCOPY VARCHAR2
105 ,x_msg_count OUT NOCOPY NUMBER
106 ,x_msg_data OUT NOCOPY VARCHAR2) IS
107 ----------------------------------------------------------------------
108 -- Start of comments
109 --
110 -- Procedure name : Update_ENI_Staging_Table
111 -- Type : Public
112 -- Pre-reqs : None
113 -- Requirement : Bug 3134279 (11.5.10 Requirement)
114 -- Functionality : To insert data into eni_denorm_hrchy_stg so that
115 -- DBI team can use the same for denormalization
116 -- Notes :
117 --
118 -- History :
119 -- 09-SEP-2003 Sridhar R Creation
120 --
121 -- END OF comments
122 ----------------------------------------------------------------------
123
124 l_object_type CONSTANT VARCHAR2(20) := 'CATEGORY_SET';
125 BEGIN
126 IF (INV_ITEM_UTIL.Object_Exists
127 (p_object_type => 'PACKAGE',
128 p_object_name => 'ENI_DENORM_HRCHY') = 'Y') THEN
129 -- the package exists
130 EXECUTE IMMEDIATE
131 ' BEGIN '||
132 ' ENI_DENORM_HRCHY.Insert_Into_Staging ( '||
133 ' p_object_type => :b_object_type '||
134 ' ,p_object_id => :b_caregory_set_id '||
135 ' ,p_child_id => :b_category_id '||
136 ' ,p_parent_id => NULL '||
137 ' ,p_mode_flag => :b_mode_flag '||
138 ' ,p_language_code => :b_language_code '||
139 ' ,x_return_status => :b_return_status '||
140 ' ,x_msg_count => :b_msg_count '||
141 ' ,x_msg_data => :b_msg_data ); '||
142 ' END;'
143 USING IN l_object_type,
144 IN p_category_set_id,
145 IN p_category_id,
146 IN p_mode_flag,
147 IN p_language_code,
148 OUT x_return_status,
149 OUT x_msg_count,
150 OUT x_msg_data;
151 IF (x_return_status <> 'S') THEN
152 RETURN;
153 END IF;
154 END IF; -- package exists
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 x_return_status := 'U';
159 -- Bug 4569555 RAISE;
160 END Update_ENI_Staging_Table;
161
162 PROCEDURE SYNC_STAR_ITEMS_FROM_IOI(
163 p_api_version IN NUMBER
164 ,p_init_msg_list IN VARCHAR2 := 'F'
165 ,p_set_process_id IN NUMBER
166 ,x_return_status OUT NOCOPY VARCHAR2
167 ,x_msg_count OUT NOCOPY NUMBER
168 ,x_msg_data OUT NOCOPY VARCHAR2) IS
169 BEGIN
170
171 IF (INV_ITEM_UTIL.Object_Exists
172 (p_object_type => 'PACKAGE',
173 p_object_name => 'ENI_ITEMS_STAR_PKG') = 'Y') THEN
174 -- the package exists
175 EXECUTE IMMEDIATE
176 ' BEGIN '||
177 ' ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI '||
178 ' ( '||
179 ' p_api_version => :p_api_version '||
180 ' , p_init_msg_list => :p_init_msg_list '||
181 ' , p_set_process_id => :p_set_process_id '||
182 ' , x_return_status => :x_return_status '||
183 ' , x_msg_count => :x_msg_count '||
184 ' , x_msg_data => :x_msg_data '||
185 ' ); '||
186 ' END;'
187 USING IN p_api_version,
188 IN p_init_msg_list,
189 IN p_set_process_id,
190 OUT x_return_status,
191 OUT x_msg_count,
192 OUT x_msg_data;
193
194 END IF;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 x_return_status := 'U';
199 -- Bug 4569555 RAISE;
200 END SYNC_STAR_ITEMS_FROM_IOI;
201
202 PROCEDURE Sync_Star_ItemCatg_From_COI(
203 p_api_version IN NUMBER
204 ,p_init_msg_list IN VARCHAR2 := 'F'
205 ,p_set_process_id IN NUMBER
206 ,x_return_status OUT NOCOPY VARCHAR2
207 ,x_msg_count OUT NOCOPY NUMBER
208 ,x_msg_data OUT NOCOPY VARCHAR2) IS
209 BEGIN
210
211 IF (INV_ITEM_UTIL.Object_Exists
212 (p_object_type => 'PACKAGE',
213 p_object_name => 'ENI_ITEMS_STAR_PKG') = 'Y') THEN
214 -- the package exists
215 EXECUTE IMMEDIATE
216 ' BEGIN '||
217 ' ENI_ITEMS_STAR_PKG.Sync_Star_ItemCatg_From_COI '||
218 ' ( '||
219 ' p_api_version => :p_api_version '||
220 ' , p_init_msg_list => :p_init_msg_list '||
221 ' , p_set_process_id => :p_set_process_id '||
222 ' , x_return_status => :x_return_status '||
223 ' , x_msg_count => :x_msg_count '||
224 ' , x_msg_data => :x_msg_data '||
225 ' ); '||
226 ' END;'
227 USING IN p_api_version,
228 IN p_init_msg_list,
229 IN p_set_process_id,
230 OUT x_return_status,
231 OUT x_msg_count,
232 OUT x_msg_data;
233
234 END IF;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 x_return_status := 'U';
239 -- Bug 4569555 RAISE;
240 END Sync_Star_ItemCatg_From_COI;
241
242
243 End INV_ENI_ITEMS_STAR_PKG;