[Home] [Help]
PACKAGE BODY: APPS.CSL_CSP_SEC_INV_ACC_PKG
Source
1 PACKAGE BODY CSL_CSP_SEC_INV_ACC_PKG AS
2 /* $Header: cslciacb.pls 120.0 2005/05/24 17:43:20 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_CSP_SEC_INV_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSP_SEC_INVENTORIES');
8 g_table_name CONSTANT VARCHAR2(30) := 'CSP_SEC_INVENTORIES';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'SECONDARY_INVENTORY_ID';
10 g_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
11 g_debug_level NUMBER; -- debug level
12
13 FUNCTION Insert_CSP_Sec_Inventory
14 (
15 p_resource_id IN NUMBER,
16 p_subinventory_code IN VARCHAR2,
17 p_organization_id IN NUMBER
18 )
19 RETURN BOOLEAN
20 IS
21
22 CURSOR c_csp_sec_inventories( b_subinventory_code VARCHAR2
23 , b_organization_id NUMBER
24 ) IS
25 SELECT csi.secondary_inventory_id
26 FROM csp_sec_inventories csi
27 WHERE (csi.SECONDARY_INVENTORY_NAME = b_subinventory_code
28 AND csi.ORGANIZATION_ID = b_organization_id)
29 OR ( CONDITION_TYPE = 'B'
30 -- Bug 3724123
31 AND csi.ORGANIZATION_ID = JTM_HOOK_UTIL_PKG.Get_Profile_Value('CS_INV_VALIDATION_ORG',0));
32
33 r_csp_sec_inventories c_csp_sec_inventories%ROWTYPE;
34
35 l_return_value BOOLEAN := FALSE;
36
37 BEGIN
38 /*** get debug level ***/
39 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
40
41 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
42 jtm_message_log_pkg.Log_Msg
43 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
44 , v_object_name => g_table_name
45 , v_message => 'Entering Insert_CSP_Sec_Inventory'
46 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
47 END IF;
48
49 /*** Retreive record assigned by Hook ***/
50 OPEN c_csp_sec_inventories( p_subinventory_code, p_organization_id);
51 FETCH c_csp_sec_inventories INTO r_csp_sec_inventories;
52 IF c_csp_sec_inventories%NOTFOUND THEN
53 /*** could not find assignment record -> exit ***/
54 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
55 jtm_message_log_pkg.Log_Msg
56 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
57 , v_object_name => g_table_name
58 , v_message => 'Insert_CSP_Sec_Inventory could not find :' || p_subinventory_code ||
59 ' , ' || p_organization_id || ' for resource id ' || p_resource_id
60 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
61
62 RETURN l_return_value;
63 END IF;
64
65 CLOSE c_csp_sec_inventories;
66 ELSE
67 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
68 jtm_message_log_pkg.Log_Msg
69 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
70 , v_object_name => g_table_name
71 , v_message => 'Inserting ACC record :' || p_subinventory_code || ' , ' || p_organization_id ||
72 ' for resource id ' || p_resource_id
73 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
74 END IF;
75
76 /*** Loop over all available records and put them in the acc table ***/
77 WHILE c_csp_sec_inventories%FOUND LOOP
78 /*** Call common package to insert record into ACC table ***/
79 JTM_HOOK_UTIL_PKG.Insert_Acc
80 ( p_publication_item_names => g_publication_item_name
81 ,p_acc_table_name => g_acc_table_name
82 ,p_pk1_name => g_pk1_name
83 ,p_pk1_num_value => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
84 ,p_resource_id => p_resource_id
85 );
86 CSL_MTL_SEC_INV_ACC_PKG.Insert_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
87 CSL_MTL_ITEM_LOCATIONS_ACC_PKG.Insert_Item_Locs_By_Subinv
88 ( p_subinventory_code => p_subinventory_code
89 , p_organization_id => p_organization_id
90 , p_resource_id => p_resource_id
91 );
92
93 FETCH c_csp_sec_inventories INTO r_csp_sec_inventories;
94 END LOOP;
95 l_return_value := TRUE;
96 END IF;
97
98 RETURN l_return_value;
99
100 END Insert_CSP_Sec_Inventory;
101
102 PROCEDURE Update_CSP_Sec_Inventory
103 (
104 p_resource_id IN NUMBER,
105 p_subinventory_code IN VARCHAR2,
106 p_organization_id IN NUMBER
107 )
108 IS
109 CURSOR c_csp_sec_inventories( b_subinventory_code VARCHAR2
110 , b_organization_id NUMBER
111 ) IS
112 SELECT csi.secondary_inventory_id
113 FROM csp_sec_inventories csi
114 WHERE (csi.SECONDARY_INVENTORY_NAME = b_subinventory_code
115 AND csi.ORGANIZATION_ID = b_organization_id)
116 OR ( CONDITION_TYPE = 'B'
117 -- Bug 3724123
118 AND csi.ORGANIZATION_ID = JTM_HOOK_UTIL_PKG.Get_Profile_Value('CS_INV_VALIDATION_ORG',0));
119
120 r_csp_sec_inventories c_csp_sec_inventories%ROWTYPE;
121
122 l_acc_id NUMBER;
123
124 BEGIN
125 /*** get debug level ***/
126 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
127
128 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
129 jtm_message_log_pkg.Log_Msg
130 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
131 , v_object_name => g_table_name
132 , v_message => 'Entering Update_CSP_Sec_Inventory'
133 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
134 END IF;
135
136 /*** Retreive record assigned by Hook ***/
137 OPEN c_csp_sec_inventories( p_subinventory_code, p_organization_id);
138 FETCH c_csp_sec_inventories INTO r_csp_sec_inventories;
139 IF c_csp_sec_inventories%NOTFOUND THEN
140 /*** could not find assignment record -> exit ***/
141 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
142 jtm_message_log_pkg.Log_Msg
143 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
144 , v_object_name => g_table_name
145 , v_message => 'Update_CSP_Sec_Inventory could not find :' || p_subinventory_code || ' , '
146 || p_organization_id || ' for resource id ' || p_resource_id
147 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
148 END IF;
149
150 CLOSE c_csp_sec_inventories;
151 ELSE
152 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
153 jtm_message_log_pkg.Log_Msg
154 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
155 , v_object_name => g_table_name
156 , v_message => 'Update ACC record :' || p_subinventory_code || ' , ' || p_organization_id
157 || ' for resource id ' || p_resource_id
158 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
159 END IF;
160
161 /*** Loop over all available records and put them in the acc table ***/
162 WHILE c_csp_sec_inventories%FOUND LOOP
163 /*** Call common package to insert record into ACC table ***/
164 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
165 ( P_ACC_TABLE_NAME => g_acc_table_name
166 ,P_PK1_NAME => g_pk1_name
167 ,P_PK1_NUM_VALUE => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
168 ,P_RESOURCE_ID => p_resource_id);
169
170
171 IF l_acc_id = -1 THEN
172 /*** Record is not yet in ACC tables. Insert has to be done ***/
173 JTM_HOOK_UTIL_PKG.Insert_Acc
174 ( p_publication_item_names => g_publication_item_name
175 ,p_acc_table_name => g_acc_table_name
176 ,p_pk1_name => g_pk1_name
177 ,p_pk1_num_value => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
178 ,p_resource_id => p_resource_id
179 );
180
181 CSL_MTL_SEC_INV_ACC_PKG.Insert_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
182 CSL_MTL_ITEM_LOCATIONS_ACC_PKG.Insert_Item_Locs_By_Subinv
183 ( p_subinventory_code => p_subinventory_code
184 , p_organization_id => p_organization_id
185 , p_resource_id => p_resource_id
186 );
187
188 ELSE
189 /*** Record is already in ACC. Only an update is required for re-sending ***/
190 JTM_HOOK_UTIL_PKG.Update_Acc
191 ( p_publication_item_names => g_publication_item_name
192 ,p_acc_table_name => g_acc_table_name
193 ,p_resource_id => p_resource_id
194 ,p_access_id => l_acc_id
195 );
196
197 CSL_MTL_SEC_INV_ACC_PKG.Update_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
198 END IF;
199
200
201 FETCH c_csp_sec_inventories INTO r_csp_sec_inventories;
202 END LOOP;
203 /*** Succesfull looped through recordset ***/
204
205 END IF;
206
207 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
208 jtm_message_log_pkg.Log_Msg
209 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
210 , v_object_name => g_table_name
211 , v_message => 'Leaving Update_CSP_Sec_Inventory'
212 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
213 END IF;
214
215
216 END Update_CSP_Sec_Inventory;
217
218 FUNCTION Delete_CSP_Sec_Inventory
219 (
220 p_resource_id IN NUMBER,
221 p_subinventory_code IN VARCHAR2,
222 p_organization_id IN NUMBER
223 )
224 RETURN BOOLEAN
225 IS
226
227 CURSOR c_csp_sec_inventories( b_subinventory_code VARCHAR2
228 , b_organization_id NUMBER
229 ) IS
230 SELECT csi.secondary_inventory_id
231 FROM csp_sec_inventories csi
232 WHERE (csi.SECONDARY_INVENTORY_NAME = b_subinventory_code
233 AND csi.ORGANIZATION_ID = b_organization_id)
234 OR ( CONDITION_TYPE = 'B'
235 -- Bug 3724123
236 AND csi.ORGANIZATION_ID = JTM_HOOK_UTIL_PKG.Get_Profile_Value('CS_INV_VALIDATION_ORG',0));
237
238 r_csp_sec_inventories c_csp_sec_inventories%ROWTYPE;
239
240 l_return_value BOOLEAN := FALSE;
241
242 BEGIN
243 /*** get debug level ***/
244 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
245
246 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
247 jtm_message_log_pkg.Log_Msg
248 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
249 , v_object_name => g_table_name
250 , v_message => 'Entering Delete_CSP_Sec_Inventory'
251 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
252 END IF;
253
254 /*** Retreive record assigned by Hook ***/
255 OPEN c_csp_sec_inventories( p_subinventory_code, p_organization_id);
256 FETCH c_csp_sec_inventories INTO r_csp_sec_inventories;
257 IF c_csp_sec_inventories%NOTFOUND THEN
258 /*** could not find assignment record -> exit ***/
259 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
260 jtm_message_log_pkg.Log_Msg
261 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
262 , v_object_name => g_table_name
263 , v_message => 'Delete_CSP_Sec_Inventory could not find :' || p_subinventory_code || ' , '
264 || p_organization_id || ' for resource id ' || p_resource_id
265 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
266 END IF;
267
268 CLOSE c_csp_sec_inventories;
269 RETURN l_return_value;
270 ELSE
271 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
272 jtm_message_log_pkg.Log_Msg
273 ( v_object_id => p_subinventory_code || ' , ' || p_organization_id
274 , v_object_name => g_table_name
275 , v_message => 'Deleting ACC record :' || p_subinventory_code || ' , ' || p_organization_id
276 || ' for resource id ' || p_resource_id
277 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
278 END IF;
279
280 /*** Loop over all available records and put them in the acc table ***/
281 WHILE c_csp_sec_inventories%FOUND LOOP
282 /*** Call common package to insert record into ACC table ***/
283 JTM_HOOK_UTIL_PKG.Delete_Acc
284 ( p_publication_item_names => g_publication_item_name
285 ,p_acc_table_name => g_acc_table_name
286 ,p_pk1_name => g_pk1_name
287 ,p_pk1_num_value => r_csp_sec_inventories.SECONDARY_INVENTORY_ID
288 ,p_resource_id => p_resource_id
289 );
290
291 CSL_MTL_SEC_INV_ACC_PKG.Delete_MTL_Sec_Inventory(p_resource_id, p_subinventory_code, p_organization_id);
292 CSL_MTL_ITEM_LOCATIONS_ACC_PKG.Delete_Item_Locs_By_Subinv
293 ( p_subinventory_code => p_subinventory_code
294 , p_organization_id => p_organization_id
295 , p_resource_id => p_resource_id
296 );
297
298
299 FETCH c_csp_sec_inventories INTO r_csp_sec_inventories;
300 END LOOP;
301 /*** Succesfull looped through recordset ***/
302 l_return_value := TRUE;
303 END IF;
304
305 RETURN l_return_value;
306
307 END Delete_CSP_Sec_Inventory;
308
309 END CSL_CSP_SEC_INV_ACC_PKG;