[Home] [Help]
PACKAGE BODY: APPS.CSM_INV_LOC_ASS_EVENT_PKG
Source
4 --
1 PACKAGE BODY CSM_INV_LOC_ASS_EVENT_PKG
2 /* $Header: csmeilab.pls 120.3 2008/06/12 12:52:09 trajasek ship $*/
3 AS
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 AND au.USER_ID = au.owner_id;
60
61 BEGIN
62 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASSIGNMENT_INS_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
63 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
64
68 p_user_id=>r_invLocAsgn_rec.user_id);
65 FOR r_invLocAsgn_rec IN l_invLocAsgn_csr(p_csp_inv_loc_assignment_id) LOOP
66 -- download subinventory and locator info
67 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
69
70 -- not being used as there we are not supporting it right now
71 -- download serial numbers for the subinv
72 CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_I(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
73 p_user_id => r_invLocAsgn_rec.user_id);
74
75 -- for inventory download....JTM Master conc program for INV needs to be run
76 END LOOP;
77
78 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASSIGNMENT_INS_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
79 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
80 EXCEPTION
81 WHEN OTHERS THEN
82 l_sqlerrno := to_char(SQLCODE);
83 l_sqlerrmsg := substr(SQLERRM, 1,2000);
84 l_error_msg := ' Exception in INV_LOC_ASSIGNMENT_INS_INIT for csp_inv_loc_assignment_id:'
85 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
86 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
87 RAISE;
88 END INV_LOC_ASSIGNMENT_INS_INIT;
89
90 PROCEDURE INV_LOC_ASS_ACC_I(p_csp_inv_loc_assignment_id IN NUMBER, p_user_id IN NUMBER)
91 IS
92 l_sqlerrno VARCHAR2(20);
93 l_sqlerrmsg VARCHAR2(4000);
94 l_error_msg VARCHAR2(4000);
95 l_return_status VARCHAR2(2000);
96 l_subinventory_code csp_inv_loc_assignments.subinventory_code%TYPE;
97 l_organization_id csp_inv_loc_assignments.organization_id%TYPE;
98 l_inv_location_id mtl_item_locations.inventory_location_id%TYPE;
99 l_locator_org_id mtl_item_locations.organization_id%TYPE;
100
101 -- get subinventory/organization to insert into csm_mtl_sec_inv
102 CURSOR l_inv_loc_ass_csr(p_inv_loc_ass_id IN NUMBER)
103 IS
104 SELECT subinventory_code,
105 organization_id
106 FROM csp_inv_loc_assignments
107 WHERE csp_inv_loc_assignment_id = p_inv_loc_ass_id;
108
109 -- get inventory_location_id/organization_id to insert into csm_mtl_item_locations
110 CURSOR l_inv_location_csr(p_subinv_code IN VARCHAR2, p_organization_id IN NUMBER)
111 IS
112 SELECT mil.inventory_location_id, mil.organization_id
113 FROM mtl_item_locations mil
114 WHERE mil.subinventory_code = p_subinv_code
115 AND mil.organization_id = p_organization_id;
116
117 BEGIN
118 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_ACC_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
119 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
120
121 OPEN l_inv_loc_ass_csr(p_csp_inv_loc_assignment_id);
122 FETCH l_inv_loc_ass_csr INTO l_subinventory_code, l_organization_id;
123 IF l_inv_loc_ass_csr%FOUND THEN
124 CSM_ACC_PKG.Insert_Acc
125 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
126 ,P_ACC_TABLE_NAME => g_acc_table_name1
127 ,P_SEQ_NAME => g_acc_sequence_name1
128 ,P_PK1_NAME => g_pk1_name1
129 ,P_PK1_NUM_VALUE => p_csp_inv_loc_assignment_id
130 ,P_USER_ID => p_user_id
131 );
132
133 -- insert into csm_mtl_sec_inventories_acc
134 CSM_ACC_PKG.Insert_Acc
135 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
139 ,P_PK1_CHAR_VALUE => l_subinventory_code
136 ,P_ACC_TABLE_NAME => g_acc_table_name2
137 ,P_SEQ_NAME => g_acc_sequence_name2
138 ,P_PK1_NAME => g_pk1_name2
140 ,P_PK2_NAME => g_pk2_name2
141 ,P_PK2_NUM_VALUE => l_organization_id
142 ,P_USER_ID => p_user_id
143 );
144 END IF;
145 CLOSE l_inv_loc_ass_csr;
146
147 OPEN l_inv_location_csr(l_subinventory_code, l_organization_id);
148 LOOP
149 FETCH l_inv_location_csr INTO l_inv_location_id, l_locator_org_id;
150 IF l_inv_location_csr%NOTFOUND THEN
151 EXIT;
152 END IF;
153 -- insert into csm_mtl_item_locations_acc
154 CSM_ACC_PKG.Insert_Acc
155 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
156 ,P_ACC_TABLE_NAME => g_acc_table_name3
157 ,P_SEQ_NAME => g_acc_sequence_name3
158 ,P_PK1_NAME => g_pk1_name3
159 ,P_PK1_NUM_VALUE => l_inv_location_id
160 ,P_PK2_NAME => g_pk2_name3
161 ,P_PK2_NUM_VALUE => l_locator_org_id
162 ,P_USER_ID => p_user_id
163 );
164
165 END LOOP;
166 CLOSE l_inv_location_csr;
167
168 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_ACC_I for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
169 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 l_sqlerrno := to_char(SQLCODE);
174 l_sqlerrmsg := substr(SQLERRM, 1,2000);
175 l_error_msg := ' Exception in INV_LOC_ASS_ACC_I for csp_inv_loc_assignment_id:'
176 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
177 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
178 RAISE;
179 END INV_LOC_ASS_ACC_I;
180
181 PROCEDURE INV_LOC_ASSIGNMENT_DEL_INIT(p_csp_inv_loc_assignment_id IN NUMBER)
182 IS
183 l_sqlerrno VARCHAR2(20);
184 l_sqlerrmsg VARCHAR2(4000);
185 l_error_msg VARCHAR2(4000);
186 l_return_status VARCHAR2(2000);
187
188 CURSOR l_cila_post_del_csr(p_csp_inv_loc_assignment_id IN NUMBER)
189 IS
190 SELECT cila.organization_id,
191 cila.subinventory_code,
192 cila.resource_id,
193 au.user_id
194 FROM csp_inv_loc_assignments cila,
195 asg_user au
199
196 WHERE cila.csp_inv_loc_assignment_id = p_csp_inv_loc_assignment_id
197 AND au.resource_id = cila.resource_id
198 AND au.USER_ID = au.owner_id;
200 BEGIN
201 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASSIGNMENT_DEL_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
202 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
203
204 FOR r_cila_post_del_rec IN l_cila_post_del_csr(p_csp_inv_loc_assignment_id) LOOP
205 -- delete downloaded subinventory and locator info
206 CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
207 p_user_id=>r_cila_post_del_rec.user_id);
208
209 -- not being used as there we are not supporting it right now
210 -- delete downloaded serial numbers for the subinv
211 CSM_SERIAL_NUMBERS_EVENT_PKG.INV_LOC_ASS_MSN_MAKE_DIRTY_D(p_csp_inv_loc_assignment_id=>p_csp_inv_loc_assignment_id,
212 p_user_id => r_cila_post_del_rec.user_id);
213
214 -- for inventory data to be deleted....JTM Master conc program for INV needs to be run
215 END LOOP;
216
217 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASSIGNMENT_DEL_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
218 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
219 EXCEPTION
220 WHEN OTHERS THEN
221 l_sqlerrno := to_char(SQLCODE);
222 l_sqlerrmsg := substr(SQLERRM, 1,2000);
223 l_error_msg := ' Exception in INV_LOC_ASSIGNMENT_DEL_INIT for csp_inv_loc_assignment_id:'
224 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
225 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
226 RAISE;
227 END INV_LOC_ASSIGNMENT_DEL_INIT;
228
229 PROCEDURE INV_LOC_ASS_ACC_D(p_csp_inv_loc_assignment_id IN NUMBER, p_user_id IN NUMBER)
230 IS
231 l_sqlerrno VARCHAR2(20);
232 l_sqlerrmsg VARCHAR2(4000);
233 l_error_msg VARCHAR2(4000);
234 l_return_status VARCHAR2(2000);
235 l_subinventory_code csp_inv_loc_assignments.subinventory_code%TYPE;
236 l_organization_id csp_inv_loc_assignments.organization_id%TYPE;
237 l_inv_location_id mtl_item_locations.inventory_location_id%TYPE;
238 l_locator_org_id mtl_item_locations.organization_id%TYPE;
239
240 -- get subinventory/organization to delete from csm_mtl_sec_inv
241 CURSOR l_inv_loc_ass_csr(p_inv_loc_ass_id IN NUMBER)
242 IS
243 SELECT subinventory_code,
244 organization_id
245 FROM csp_inv_loc_assignments
246 WHERE csp_inv_loc_assignment_id = p_inv_loc_ass_id;
247
248 -- get inventory_location_id/organization_id to delete from csm_mtl_item_locations
249 CURSOR l_inv_location_csr(p_subinv_code IN VARCHAR2, p_organization_id IN NUMBER)
250 IS
251 SELECT mil.inventory_location_id, mil.organization_id
252 FROM mtl_item_locations mil
253 WHERE mil.subinventory_code = p_subinv_code
254 AND mil.organization_id = p_organization_id;
255
256 BEGIN
257 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASS_ACC_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
258 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
259
260 OPEN l_inv_loc_ass_csr(p_csp_inv_loc_assignment_id);
261 FETCH l_inv_loc_ass_csr INTO l_subinventory_code, l_organization_id;
262 IF l_inv_loc_ass_csr%FOUND THEN
263 -- delete from csm_inv_loc_ass_acc
264 CSM_ACC_PKG.Delete_Acc
265 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
266 ,P_ACC_TABLE_NAME => g_acc_table_name1
267 ,P_PK1_NAME => g_pk1_name1
268 ,P_PK1_NUM_VALUE => p_csp_inv_loc_assignment_id
269 ,P_USER_ID => p_user_id
270 );
271
272 -- delete from csm_mtl_sec_inventories_acc
273 CSM_ACC_PKG.Delete_Acc
274 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
275 ,P_ACC_TABLE_NAME => g_acc_table_name2
276 ,P_PK1_NAME => g_pk1_name2
277 ,P_PK1_CHAR_VALUE => l_subinventory_code
278 ,P_PK2_NAME => g_pk2_name2
279 ,P_PK2_NUM_VALUE => l_organization_id
280 ,P_USER_ID => p_user_id
281 );
282
283 END IF;
284 CLOSE l_inv_loc_ass_csr;
285
286 OPEN l_inv_location_csr(l_subinventory_code, l_organization_id);
287 LOOP
288 FETCH l_inv_location_csr INTO l_inv_location_id, l_locator_org_id;
289 IF l_inv_location_csr%NOTFOUND THEN
290 EXIT;
291 END IF;
292
293 -- delete from csm_mtl_item_locations_acc
294 CSM_ACC_PKG.Delete_Acc
295 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
296 ,P_ACC_TABLE_NAME => g_acc_table_name3
297 ,P_PK1_NAME => g_pk1_name3
298 ,P_PK1_NUM_VALUE => l_inv_location_id
299 ,P_PK2_NAME => g_pk2_name3
300 ,P_PK2_NUM_VALUE => l_locator_org_id
301 ,P_USER_ID => p_user_id
302 );
303
304 END LOOP;
305 CLOSE l_inv_location_csr;
306
307 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASS_ACC_D for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
308 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
309
310 EXCEPTION
311 WHEN OTHERS THEN
312 l_sqlerrno := to_char(SQLCODE);
313 l_sqlerrmsg := substr(SQLERRM, 1,2000);
314 l_error_msg := ' Exception in INV_LOC_ASS_ACC_D for csp_inv_loc_assignment_id:'
315 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
316 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
317 RAISE;
318 END INV_LOC_ASS_ACC_D;
319
320 PROCEDURE INV_LOC_ASSIGNMENT_UPD_INIT(p_csp_inv_loc_assignment_id IN NUMBER)
321 IS
322 l_sqlerrno VARCHAR2(20);
323 l_sqlerrmsg VARCHAR2(4000);
324 l_error_msg VARCHAR2(4000);
325 l_return_status VARCHAR2(2000);
326
327 CURSOR l_invLocAsgn_csr(p_csp_inv_loc_assg_id IN NUMBER)
328 IS
329 SELECT acc.access_id,
330 acc.user_id
331 FROM csm_inv_loc_ass_acc acc,
332 csp_inv_loc_assignments cila,
333 ASG_USER au
334 WHERE acc.csp_inv_loc_assignment_id = p_csp_inv_loc_assg_id
335 AND cila.csp_inv_loc_assignment_id = acc.csp_inv_loc_assignment_id
336 AND au.USER_ID = acc.USER_ID
337 AND au.USER_ID = au.OWNER_ID;
338
339 l_invLocAsgn_rec l_invLocAsgn_csr%ROWTYPE;
340
341 BEGIN
342 CSM_UTIL_PKG.LOG('Entering INV_LOC_ASSIGNMENT_UPD_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
343 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
344
345 OPEN l_invLocAsgn_csr(p_csp_inv_loc_assignment_id);
346 FETCH l_invLocAsgn_csr INTO l_invLocAsgn_rec;
347 IF l_invLocAsgn_csr%FOUND THEN
348 -- call the mark dirty for update
349 CSM_ACC_PKG.Update_Acc
350 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
351 ,P_ACC_TABLE_NAME => g_acc_table_name1
352 ,P_USER_ID => l_invLocAsgn_rec.user_id
353 ,P_ACCESS_ID => l_invLocAsgn_rec.access_id
354 );
355
356 END IF;
357 CLOSE l_invLocAsgn_csr;
358
359 CSM_UTIL_PKG.LOG('Leaving INV_LOC_ASSIGNMENT_UPD_INIT for csp_inv_loc_assignment_id: ' || p_csp_inv_loc_assignment_id,
360 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
361 EXCEPTION
362 WHEN OTHERS THEN
363 l_sqlerrno := to_char(SQLCODE);
364 l_sqlerrmsg := substr(SQLERRM, 1,2000);
365 l_error_msg := ' Exception in INV_LOC_ASSIGNMENT_UPD_INIT for csp_inv_loc_assignment_id:'
366 || to_char(p_csp_inv_loc_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
367 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_INV_LOC_ASS_EVENT_PKG.INV_LOC_ASSIGNMENT_UPD_INIT',FND_LOG.LEVEL_EXCEPTION);
368 RAISE;
369 END INV_LOC_ASSIGNMENT_UPD_INIT;
370
371 END CSM_INV_LOC_ASS_EVENT_PKG;