[Home] [Help]
PACKAGE BODY: APPS.CSM_INV_LOC_ASS_EVENT_PKG
Source
1 PACKAGE BODY CSM_INV_LOC_ASS_EVENT_PKG
2 /* $Header: csmeilab.pls 120.1 2005/07/25 00:09:46 trajasek noship $*/
3 AS
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_INV_LOC_ASS';
16
17 g_table_name1 CONSTANT VARCHAR2(30) := 'CSP_INV_LOC_ASSIGNMENTS';
18 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_INV_LOC_ASS_ACC';
19 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_INV_LOC_ASS_ACC_S';
20 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
21 CSM_ACC_PKG.t_publication_item_list('CSM_INV_LOC_ASS');
22 g_pk1_name1 CONSTANT VARCHAR2(30) := 'CSP_INV_LOC_ASSIGNMENT_ID';
23
24 g_pub_item2 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INVENTORIES';
25 g_table_name2 CONSTANT VARCHAR2(30) := 'MTL_SEC_INVENTORIES';
26 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INV_ACC';
27 g_acc_sequence_name2 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INV_ACC_S';
28 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
29 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SEC_INVENTORIES');
30 g_pk1_name2 CONSTANT VARCHAR2(30) := 'SECONDARY_INVENTORY_NAME';
31 g_pk2_name2 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
32
33 g_pub_item3 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
34 g_table_name3 CONSTANT VARCHAR2(30) := 'MTL_ITEM_LOCATIONS';
35 g_acc_table_name3 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_ACC';
36 g_acc_sequence_name3 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_ACC_S';
37 g_publication_item_name3 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
38 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_ITEM_LOCATIONS');
39 g_pk1_name3 CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
40 g_pk2_name3 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
41
42 PROCEDURE INV_LOC_ASSIGNMENT_INS_INIT (p_csp_inv_loc_assignment_id IN NUMBER)
43 IS
44 l_sqlerrno VARCHAR2(20);
45 l_sqlerrmsg VARCHAR2(4000);
46 l_error_msg VARCHAR2(4000);
47 l_return_status VARCHAR2(2000);
48
49 CURSOR l_invLocAsgn_csr(p_csp_inv_loc_assg_id IN NUMBER)
50 IS
51 SELECT au.user_id AS user_id,
52 invloc.resource_id AS resource_id,
53 invLoc.organization_id AS organization_id,
54 invLoc.subinventory_code AS subinventory_code
55 FROM csp_inv_loc_assignments invLoc,
56 asg_user au
57 WHERE invloc.csp_inv_loc_assignment_id = p_csp_inv_loc_assg_id
58 AND au.resource_id = invloc.resource_id;
59
60 BEGIN
61 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASSIGNMENT_INS_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
62 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
63
64 FOR r_invLocAsgn_rec IN l_invLocAsgn_csr(p_csp_inv_loc_assignment_id) LOOP
65 -- download subinventory and locator info
66 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
67 p_user_id=>r_invLocAsgn_rec.user_id);
68
69 -- not being used as there we are not supporting it right now
70 -- download serial numbers for the subinv
71 CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
72 p_user_id => r_invLocAsgn_rec.user_id);
73
74 -- for inventory download....JTM Master conc program for INV needs to be run
75 END LOOP;
76
77 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASSIGNMENT_INS_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
78 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
79 EXCEPTION
80 WHEN OTHERS THEN
81 l_sqlerrno := to_char(SQLCODE);
82 l_sqlerrmsg := substr(SQLERRM, 1,2000);
83 l_error_msg := ' Exception in INV_LOC_ASSIGNMENT_INS_INIT for csp_inv_loc_assignment_id:'
84 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
85 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
86 RAISE;
87 END INV_LOC_ASSIGNMENT_INS_INIT;
88
89 PROCEDURE INV_LOC_ASS_ACC_I(p_csp_inv_loc_assignment_id IN NUMBER, p_user_id IN NUMBER)
90 IS
91 l_sqlerrno VARCHAR2(20);
92 l_sqlerrmsg VARCHAR2(4000);
93 l_error_msg VARCHAR2(4000);
94 l_return_status VARCHAR2(2000);
95 l_subinventory_code csp_inv_loc_assignments.subinventory_code%TYPE;
96 l_organization_id csp_inv_loc_assignments.organization_id%TYPE;
97 l_inv_location_id mtl_item_locations.inventory_location_id%TYPE;
98 l_locator_org_id mtl_item_locations.organization_id%TYPE;
99
100 -- get subinventory/organization to insert into csm_mtl_sec_inv
101 CURSOR l_inv_loc_ass_csr(p_inv_loc_ass_id IN NUMBER)
102 IS
103 SELECT subinventory_code,
104 organization_id
105 FROM csp_inv_loc_assignments
106 WHERE csp_inv_loc_assignment_id = p_inv_loc_ass_id;
107
108 -- get inventory_location_id/organization_id to insert into csm_mtl_item_locations
109 CURSOR l_inv_location_csr(p_subinv_code IN VARCHAR2, p_organization_id IN NUMBER)
110 IS
111 SELECT mil.inventory_location_id, mil.organization_id
112 FROM mtl_item_locations mil
113 WHERE mil.subinventory_code = p_subinv_code
114 AND mil.organization_id = p_organization_id;
115
116 BEGIN
117 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_ACC_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
118 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
119
120 OPEN l_inv_loc_ass_csr(p_csp_inv_loc_assignment_id);
121 FETCH l_inv_loc_ass_csr INTO l_subinventory_code, l_organization_id;
122 IF l_inv_loc_ass_csr%FOUND THEN
123 CSM_ACC_PKG.Insert_Acc
124 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
125 ,P_ACC_TABLE_NAME => g_acc_table_name1
126 ,P_SEQ_NAME => g_acc_sequence_name1
127 ,P_PK1_NAME => g_pk1_name1
128 ,P_PK1_NUM_VALUE => p_csp_inv_loc_assignment_id
129 ,P_USER_ID => p_user_id
130 );
131
132 -- insert into csm_mtl_sec_inventories_acc
133 CSM_ACC_PKG.Insert_Acc
134 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
135 ,P_ACC_TABLE_NAME => g_acc_table_name2
136 ,P_SEQ_NAME => g_acc_sequence_name2
137 ,P_PK1_NAME => g_pk1_name2
138 ,P_PK1_CHAR_VALUE => l_subinventory_code
139 ,P_PK2_NAME => g_pk2_name2
140 ,P_PK2_NUM_VALUE => l_organization_id
141 ,P_USER_ID => p_user_id
142 );
143 END IF;
144 CLOSE l_inv_loc_ass_csr;
145
146 OPEN l_inv_location_csr(l_subinventory_code, l_organization_id);
147 LOOP
148 FETCH l_inv_location_csr INTO l_inv_location_id, l_locator_org_id;
149 IF l_inv_location_csr%NOTFOUND THEN
150 EXIT;
151 END IF;
152 -- insert into csm_mtl_item_locations_acc
153 CSM_ACC_PKG.Insert_Acc
154 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
155 ,P_ACC_TABLE_NAME => g_acc_table_name3
156 ,P_SEQ_NAME => g_acc_sequence_name3
157 ,P_PK1_NAME => g_pk1_name3
158 ,P_PK1_NUM_VALUE => l_inv_location_id
159 ,P_PK2_NAME => g_pk2_name3
160 ,P_PK2_NUM_VALUE => l_locator_org_id
161 ,P_USER_ID => p_user_id
162 );
163
164 END LOOP;
165 CLOSE l_inv_location_csr;
166
167 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_ACC_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
168 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 l_sqlerrno := to_char(SQLCODE);
173 l_sqlerrmsg := substr(SQLERRM, 1,2000);
174 l_error_msg := ' Exception in INV_LOC_ASS_ACC_I for csp_inv_loc_assignment_id:'
175 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
176 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
177 RAISE;
178 END INV_LOC_ASS_ACC_I;
179
180 PROCEDURE INV_LOC_ASSIGNMENT_DEL_INIT(p_csp_inv_loc_assignment_id IN NUMBER)
181 IS
182 l_sqlerrno VARCHAR2(20);
183 l_sqlerrmsg VARCHAR2(4000);
184 l_error_msg VARCHAR2(4000);
185 l_return_status VARCHAR2(2000);
186
187 CURSOR l_cila_post_del_csr(p_csp_inv_loc_assignment_id IN NUMBER)
188 IS
189 SELECT cila.organization_id,
190 cila.subinventory_code,
191 cila.resource_id,
192 au.user_id
193 FROM csp_inv_loc_assignments cila,
194 asg_user au
195 WHERE cila.csp_inv_loc_assignment_id = p_csp_inv_loc_assignment_id
196 AND au.resource_id = cila.resource_id;
197
198 BEGIN
199 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASSIGNMENT_DEL_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
200 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
201
202 FOR r_cila_post_del_rec IN l_cila_post_del_csr(p_csp_inv_loc_assignment_id) LOOP
203 -- delete downloaded subinventory and locator info
204 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
205 p_user_id=>r_cila_post_del_rec.user_id);
206
207 -- not being used as there we are not supporting it right now
208 -- delete downloaded serial numbers for the subinv
209 CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
210 p_user_id => r_cila_post_del_rec.user_id);
211
212 -- for inventory data to be deleted....JTM Master conc program for INV needs to be run
213 END LOOP;
214
215 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASSIGNMENT_DEL_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
216 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
217 EXCEPTION
218 WHEN OTHERS THEN
219 l_sqlerrno := to_char(SQLCODE);
220 l_sqlerrmsg := substr(SQLERRM, 1,2000);
221 l_error_msg := ' Exception in INV_LOC_ASSIGNMENT_DEL_INIT for csp_inv_loc_assignment_id:'
222 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
223 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
224 RAISE;
225 END INV_LOC_ASSIGNMENT_DEL_INIT;
226
227 PROCEDURE INV_LOC_ASS_ACC_D(p_csp_inv_loc_assignment_id IN NUMBER, p_user_id IN NUMBER)
228 IS
229 l_sqlerrno VARCHAR2(20);
230 l_sqlerrmsg VARCHAR2(4000);
231 l_error_msg VARCHAR2(4000);
232 l_return_status VARCHAR2(2000);
233 l_subinventory_code csp_inv_loc_assignments.subinventory_code%TYPE;
234 l_organization_id csp_inv_loc_assignments.organization_id%TYPE;
235 l_inv_location_id mtl_item_locations.inventory_location_id%TYPE;
236 l_locator_org_id mtl_item_locations.organization_id%TYPE;
237
238 -- get subinventory/organization to delete from csm_mtl_sec_inv
239 CURSOR l_inv_loc_ass_csr(p_inv_loc_ass_id IN NUMBER)
240 IS
241 SELECT subinventory_code,
242 organization_id
243 FROM csp_inv_loc_assignments
244 WHERE csp_inv_loc_assignment_id = p_inv_loc_ass_id;
245
246 -- get inventory_location_id/organization_id to delete from csm_mtl_item_locations
247 CURSOR l_inv_location_csr(p_subinv_code IN VARCHAR2, p_organization_id IN NUMBER)
248 IS
249 SELECT mil.inventory_location_id, mil.organization_id
250 FROM mtl_item_locations mil
251 WHERE mil.subinventory_code = p_subinv_code
252 AND mil.organization_id = p_organization_id;
253
254 BEGIN
255 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_ACC_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
256 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
257
258 OPEN l_inv_loc_ass_csr(p_csp_inv_loc_assignment_id);
259 FETCH l_inv_loc_ass_csr INTO l_subinventory_code, l_organization_id;
260 IF l_inv_loc_ass_csr%FOUND THEN
261 -- delete from csm_inv_loc_ass_acc
262 CSM_ACC_PKG.Delete_Acc
263 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
264 ,P_ACC_TABLE_NAME => g_acc_table_name1
265 ,P_PK1_NAME => g_pk1_name1
266 ,P_PK1_NUM_VALUE => p_csp_inv_loc_assignment_id
267 ,P_USER_ID => p_user_id
268 );
269
270 -- delete from csm_mtl_sec_inventories_acc
271 CSM_ACC_PKG.Delete_Acc
272 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
273 ,P_ACC_TABLE_NAME => g_acc_table_name2
274 ,P_PK1_NAME => g_pk1_name2
275 ,P_PK1_CHAR_VALUE => l_subinventory_code
276 ,P_PK2_NAME => g_pk2_name2
277 ,P_PK2_NUM_VALUE => l_organization_id
278 ,P_USER_ID => p_user_id
279 );
280
281 END IF;
282 CLOSE l_inv_loc_ass_csr;
283
284 OPEN l_inv_location_csr(l_subinventory_code, l_organization_id);
285 LOOP
286 FETCH l_inv_location_csr INTO l_inv_location_id, l_locator_org_id;
287 IF l_inv_location_csr%NOTFOUND THEN
288 EXIT;
289 END IF;
290
291 -- delete from csm_mtl_item_locations_acc
292 CSM_ACC_PKG.Delete_Acc
293 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
294 ,P_ACC_TABLE_NAME => g_acc_table_name3
295 ,P_PK1_NAME => g_pk1_name3
296 ,P_PK1_NUM_VALUE => l_inv_location_id
297 ,P_PK2_NAME => g_pk2_name3
298 ,P_PK2_NUM_VALUE => l_locator_org_id
299 ,P_USER_ID => p_user_id
300 );
301
302 END LOOP;
303 CLOSE l_inv_location_csr;
304
305 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_ACC_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
306 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 l_sqlerrno := to_char(SQLCODE);
311 l_sqlerrmsg := substr(SQLERRM, 1,2000);
312 l_error_msg := ' Exception in INV_LOC_ASS_ACC_D for csp_inv_loc_assignment_id:'
313 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
314 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
315 RAISE;
316 END INV_LOC_ASS_ACC_D;
317
318 PROCEDURE INV_LOC_ASSIGNMENT_UPD_INIT(p_csp_inv_loc_assignment_id IN NUMBER)
319 IS
320 l_sqlerrno VARCHAR2(20);
321 l_sqlerrmsg VARCHAR2(4000);
322 l_error_msg VARCHAR2(4000);
323 l_return_status VARCHAR2(2000);
324
325 CURSOR l_invLocAsgn_csr(p_csp_inv_loc_assg_id IN NUMBER)
326 IS
327 SELECT acc.access_id,
328 acc.user_id
329 FROM csm_inv_loc_ass_acc acc,
330 csp_inv_loc_assignments cila
331 WHERE acc.csp_inv_loc_assignment_id = p_csp_inv_loc_assg_id
332 AND cila.csp_inv_loc_assignment_id = acc.csp_inv_loc_assignment_id;
333
334 l_invLocAsgn_rec l_invLocAsgn_csr%ROWTYPE;
335
336 BEGIN
337 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASSIGNMENT_UPD_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
338 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
339
340 OPEN l_invLocAsgn_csr(p_csp_inv_loc_assignment_id);
341 FETCH l_invLocAsgn_csr INTO l_invLocAsgn_rec;
342 IF l_invLocAsgn_csr%FOUND THEN
343 -- call the mark dirty for update
344 CSM_ACC_PKG.Update_Acc
345 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
346 ,P_ACC_TABLE_NAME => g_acc_table_name1
347 ,P_USER_ID => l_invLocAsgn_rec.user_id
348 ,P_ACCESS_ID => l_invLocAsgn_rec.access_id
349 );
350
351 END IF;
352 CLOSE l_invLocAsgn_csr;
353
354 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASSIGNMENT_UPD_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
355 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
356 EXCEPTION
357 WHEN OTHERS THEN
358 l_sqlerrno := to_char(SQLCODE);
359 l_sqlerrmsg := substr(SQLERRM, 1,2000);
360 l_error_msg := ' Exception in INV_LOC_ASSIGNMENT_UPD_INIT for csp_inv_loc_assignment_id:'
361 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
362 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT',FND_LOG.LEVEL_EXCEPTION);
363 RAISE;
364 END INV_LOC_ASSIGNMENT_UPD_INIT;
365
366 END CSM_INV_LOC_ASS_EVENT_PKG;