[Home] [Help]
PACKAGE BODY: APPS.CSL_MTL_ONHAND_QTY_ACC_PKG
Source
1 PACKAGE BODY CSL_MTL_ONHAND_QTY_ACC_PKG AS
2 /* $Header: cslo1acb.pls 120.0 2005/08/30 01:38:06 utekumal noship $ */
3
4
5 /*** Globals for notifications ***/
6 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_MTL_ONHAND_QTY_ACC';
7 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
8 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_MTL_ONHAND_QTY');
9 g_table_name CONSTANT VARCHAR2(30) := 'MTL_ONHAND_QUANTITIES';
10 g_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
11 g_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
12 g_pk3_name CONSTANT VARCHAR2(30) := 'SUBINVENTORY_CODE';
13 g_debug_level NUMBER;
14
15 PROCEDURE REFRESH_ONHAND_QTY
16 IS
17
18 PRAGMA AUTONOMOUS_TRANSACTION;
19
20 TYPE sub_codeTab IS TABLE OF mtl_onhand_quantities.subinventory_code%TYPE INDEX BY BINARY_INTEGER;
21 TYPE inv_idTab IS TABLE OF mtl_onhand_quantities.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
22 TYPE org_idTab IS TABLE OF mtl_onhand_quantities.organization_id%TYPE INDEX BY BINARY_INTEGER;
23 TYPE revisionTab IS TABLE OF mtl_onhand_quantities.revision%TYPE INDEX BY BINARY_INTEGER;
24 TYPE locatorTab IS TABLE OF mtl_onhand_quantities.locator_id%TYPE INDEX BY BINARY_INTEGER;
25 TYPE tran_qtyTab IS TABLE OF mtl_onhand_quantities.transaction_quantity%TYPE INDEX BY BINARY_INTEGER;
26 TYPE lot_numTab IS TABLE OF mtl_onhand_quantities.lot_number%TYPE INDEX BY BINARY_INTEGER;
27 sub_code sub_codeTab;
28 inv_id inv_idTab;
29 org_id org_idTab;
30 rvision revisionTab;
31 loc_id locatorTab;
32 qty tran_qtyTab;
33 lot_num lot_numTab;
34
35 l_current_run_date DATE;
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 => null
44 , v_object_name => g_table_name
45 , v_message => 'Entering REFRESH_ONHAND_QTY'
46 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
47 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
48 END IF;
49
50 l_current_run_date := SYSDATE;
51
52 UPDATE JTM_CON_REQUEST_DATA SET LAST_RUN_DATE = l_current_run_date
53 WHERE PRODUCT_CODE = 'CSL'
54 AND PACKAGE_NAME = 'CSL_MTL_ONHAND_QTY_ACC_PKG'
55 AND PROCEDURE_NAME = 'REFRESH_ONHAND_QTY';
56
57 /*** First UPDATE existing MV records that changed ***/
58 /*** Fetch all records in a Bulk recordset ***/
59 SELECT ohq.subinventory_code
60 , ohq.inventory_item_id
61 , ohq.organization_id
62 , ohq.revision
63 , ohq.locator_id
64 , ohq.lot_number
65 , SUM(ohq.transaction_quantity)
66 BULK COLLECT INTO sub_code, inv_id, org_id,rvision, loc_id,lot_num, qty
67 FROM mtl_onhand_quantities ohq
68 WHERE (ohq.subinventory_code, ohq.organization_id) IN
69 ( SELECT secinv.secondary_inventory_name
70 , secinv.organization_id
71 FROM jtm_csp_sec_inv_acc secacc
72 , csp_sec_inventories secinv
73 WHERE secacc.secondary_inventory_id = secinv.secondary_inventory_id
74 AND condition_type = 'G'
75 )
76 GROUP BY ohq.subinventory_code,
77 ohq.inventory_item_id,
78 ohq.organization_id,
79 ohq.revision,
80 ohq.locator_id,
81 ohq.lot_number
82 HAVING SUM(ohq.transaction_quantity) <>
83 (
84 SELECT tot_txn_quantity
85 FROM csl_mtl_onhand_qty_mv ohqmv
86 WHERE ((ohqmv.LOT_NUMBER IS NULL AND ohq.LOT_NUMBER IS NULL) OR (ohqmv.LOT_NUMBER = ohq.LOT_NUMBER))
87 AND ((ohqmv.LOCATOR_ID IS NULL AND ohq.LOCATOR_ID IS NULL) OR (ohqmv.LOCATOR_ID = ohq.LOCATOR_ID))
88 AND ((ohqmv.REVISION IS NULL AND ohq.REVISION IS NULL) OR (ohqmv.REVISION = ohq.REVISION))
89 AND ohqmv.organization_id = ohq.organization_id
90 AND ohqmv.inventory_item_id = ohq.inventory_item_id
91 AND ohqmv.subinventory_code = ohq.subinventory_code
92 );
93
94 /*** Process all records from Bulk recordset ***/
95 IF (sub_code.COUNT > 0) THEN
96 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
97 jtm_message_log_pkg.Log_Msg
98 ( v_object_id => null
99 , v_object_name => g_table_name
100 , v_message => 'Number of updated records for REFRESH_ONHAND_QTY : '
101 || sub_code.COUNT
102 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
103 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
104 END IF;
105 FORALL i IN sub_code.FIRST..sub_code.LAST
106 UPDATE CSL_MTL_ONHAND_QTY_MV SET LAST_UPDATE_DATE = l_current_run_date, TOT_TXN_QUANTITY = qty(i)
107 WHERE subinventory_code = sub_code(i)
108 AND inventory_item_id = inv_id(i)
109 AND organization_id = org_id(i)
110 AND (LOT_NUMBER IS NULL OR LOT_NUMBER = lot_num(i))
111 AND (LOCATOR_ID IS NULL OR LOCATOR_ID = loc_id(i))
112 AND (REVISION IS NULL OR revision = rvision(i));
113 END IF;
114
115 /*** INSERT all newly created records for existing mobile users ***/
116 INSERT INTO CSL_MTL_ONHAND_QTY_MV (subinventory_code
117 , inventory_item_id, organization_id, revision, locator_id
118 , lot_number, last_update_date, tot_txn_quantity) (
119 SELECT ohq.subinventory_code, ohq.inventory_item_id
120 , ohq.organization_id, ohq.revision, ohq.locator_id
121 , ohq.lot_number, l_current_run_date
122 , SUM(ohq.transaction_quantity) tot_txn_quantity
123 FROM mtl_onhand_quantities ohq
124 WHERE (subinventory_code, organization_id) IN
125 ( SELECT csi.secondary_inventory_name
126 , csi.organization_id
127 FROM jtm_csp_sec_inv_acc secacc
128 , csp_sec_inventories csi
129 WHERE csi.SECONDARY_INVENTORY_ID = secacc.SECONDARY_INVENTORY_ID
130 AND csi.CONDITION_TYPE = 'G'
131 )
132 AND NOT EXISTS ( SELECT NULL
133 FROM CSL_MTL_ONHAND_QTY_MV ohqmv
134 WHERE ((ohqmv.LOT_NUMBER IS NULL AND ohq.LOT_NUMBER IS NULL) OR (ohqmv.LOT_NUMBER = ohq.LOT_NUMBER))
135 AND ((ohqmv.LOCATOR_ID IS NULL AND ohq.LOCATOR_ID IS NULL) OR (ohqmv.LOCATOR_ID = ohq.LOCATOR_ID))
136 AND ((ohqmv.REVISION IS NULL AND ohq.REVISION IS NULL) OR (ohqmv.REVISION = ohq.REVISION))
137 AND ohqmv.ORGANIZATION_ID = ohq.ORGANIZATION_ID
138 AND ohqmv.INVENTORY_ITEM_ID = ohq.INVENTORY_ITEM_ID
139 AND ohqmv.SUBINVENTORY_CODE = ohq.SUBINVENTORY_CODE
140 )
141 GROUP BY ohq.subinventory_code
142 , ohq.inventory_item_id
143 , ohq.organization_id
144 , ohq.revision
145 , ohq.locator_id
146 , ohq.lot_number);
147
148 /*** DELETE all records for inventories that are no longer replicated ***/
149 DELETE FROM CSL_MTL_ONHAND_QTY_MV
150 WHERE (subinventory_code, organization_id) not in (
151 SELECT csi.secondary_inventory_name
152 , csi.organization_id
153 FROM jtm_csp_sec_inv_acc secacc
154 , csp_sec_inventories csi
155 WHERE secacc.secondary_inventory_id = csi.secondary_inventory_id
156 AND csi.condition_type = 'G'
157 );
158
159 /*** DELETE all records that are no longer present in inventory ohq table ***/
160 DELETE CSL_MTL_ONHAND_QTY_MV oqv
161 WHERE (subinventory_code
162 , inventory_item_id, organization_id, revision, locator_id
163 , lot_number) NOT IN (
164 SELECT subinventory_code, inventory_item_id
165 , organization_id, revision, locator_id
166 , lot_number
167 FROM mtl_onhand_quantities
168 WHERE (subinventory_code, organization_id) IN (
169 SELECT csi.secondary_inventory_name
170 , csi.organization_id
171 FROM jtm_csp_sec_inv_acc secacc
172 , csp_sec_inventories csi
173 WHERE csi.SECONDARY_INVENTORY_ID = secacc.SECONDARY_INVENTORY_ID
174 AND csi.CONDITION_TYPE = 'G'
175 )
176 );
177
178 COMMIT;
179
180 /*** Processed MV table, now push changes to ACC table ***/
181 PROCESS_ACC(l_current_run_date);
182
183 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
184 jtm_message_log_pkg.Log_Msg
185 ( v_object_id => 1
186 , v_object_name => g_table_name
187 , v_message => 'Leaving REFRESH_ONHAND_QTY'
188 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
189 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
190 END IF;
191 EXCEPTION WHEN OTHERS THEN
192 /*** hook failed -> log error ***/
193 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
194 jtm_message_log_pkg.Log_Msg
195 ( v_object_id => 1
196 , v_object_name => g_table_name
197 , v_message => 'Caught exception in REFRESH_ONHAND_QTY hook:' || fnd_global.local_chr(10) || sqlerrm
198 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
199 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
200 END IF;
201 ROLLBACK;
202 fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','REFRESH_ONHAND_QTY',sqlerrm);
203
204 END REFRESH_ONHAND_QTY;
205
206 PROCEDURE PROCESS_ACC( l_current_run_date IN DATE)
207 IS
208
209 CURSOR c_secinv_resources IS (
210 SELECT DISTINCT resource_id
211 FROM JTM_CSP_INV_LOC_ASS_ACC);
212
213 r_secinv_resources c_secinv_resources%ROWTYPE;
214
215 CURSOR c_trackable_flag(b_inventory_item_id IN NUMBER
216 ,b_organization_id IN NUMBER
217 ) IS
218 SELECT COMMS_NL_TRACKABLE_FLAG
219 FROM MTL_SYSTEM_ITEMS_B
220 WHERE INVENTORY_ITEM_ID = b_inventory_item_id
221 AND ORGANIZATION_ID = b_organization_id;
222
223 r_trackable_flag c_trackable_flag%ROWTYPE;
224
225 TYPE access_idTab IS TABLE OF csl_mtl_onhand_qty_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
226 TYPE inv_idTab IS TABLE OF csl_mtl_onhand_qty_acc.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
227 TYPE org_idTab IS TABLE OF csl_mtl_onhand_qty_acc.organization_id%TYPE INDEX BY BINARY_INTEGER;
228 TYPE sub_codeTab IS TABLE OF csl_mtl_onhand_qty_acc.subinventory_code%TYPE INDEX BY BINARY_INTEGER;
229 TYPE rvsionTab IS TABLE OF csl_mtl_onhand_qty_acc.revision%TYPE INDEX BY BINARY_INTEGER;
230 TYPE loc_idTab IS TABLE OF csl_mtl_onhand_qty_acc.locator_id%TYPE INDEX BY BINARY_INTEGER;
231 TYPE lot_numTab IS TABLE OF csl_mtl_onhand_qty_acc.lot_number%TYPE INDEX BY BINARY_INTEGER;
232 TYPE res_idTab IS TABLE OF csl_mtl_onhand_qty_acc.resource_id%TYPE INDEX BY BINARY_INTEGER;
233 TYPE track_flagTab IS TABLE OF mtl_system_items_b.comms_nl_trackable_flag%TYPE INDEX BY BINARY_INTEGER;
234 acc_id access_idTab;
235 inv_id inv_idTab;
236 org_id org_idTab;
237 sub_code sub_codeTab;
238 rvsion rvsionTab;
239 loc_id loc_idTab;
240 lot_num lot_numTab;
241 res_id res_idTab;
242 track_flag track_flagTab;
243
244 l_dummy BOOLEAN;
245
246 BEGIN
247 /*** get debug level ***/
248 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
249
250 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
251 jtm_message_log_pkg.Log_Msg
252 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
253 , v_object_name => g_table_name
254 , v_message => 'Entering PROCESS_ACC'
255 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
256 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
257 END IF;
258
259 /*** Loop through resources to which a subinventory is assigned ***/
260 FOR r_secinv_resources IN c_secinv_resources LOOP
261 /*** First retrieve access_id of updated records and push them ***/
262
263 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
264 jtm_message_log_pkg.Log_Msg
265 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
266 , v_object_name => g_table_name
267 , v_message => 'Checking updates and inserts for resource_id = ' || r_secinv_resources.resource_id
268 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
269 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
270 END IF;
271
272 /*** Push updated record to client ***/
273 SELECT ohqacc.access_id
274 BULK COLLECT INTO acc_id
275 FROM csl_mtl_onhand_qty_mv ohqmv,
276 csl_mtl_onhand_qty_acc ohqacc
277 WHERE ((ohqmv.lot_number IS NULL AND ohqacc.lot_number IS NULL) OR (ohqmv.lot_number = ohqacc.lot_number))
278 AND ((ohqmv.locator_id IS NULL AND ohqacc.locator_id IS NULL) OR (ohqmv.locator_id = ohqacc.locator_id))
279 AND ((ohqmv.revision IS NULL AND ohqacc.revision IS NULL) OR (ohqmv.revision = ohqacc.revision))
280 AND ohqmv.organization_id = ohqacc.organization_id
281 AND ohqmv.inventory_item_id = ohqacc.inventory_item_id
282 AND ohqmv.subinventory_code = ohqacc.subinventory_code
283 AND ohqacc.resource_id = r_secinv_resources.resource_id
284 AND ohqmv.last_update_date = l_current_run_date;
285
286 IF (acc_id.COUNT > 0) THEN
287 /*** push to oLite using asg_download ***/
288
289 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
290 jtm_message_log_pkg.Log_Msg
291 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
292 , v_object_name => g_table_name
293 , v_message => 'Pushing ' || acc_id.COUNT || ' updated record(s)'
294 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
295 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
296 END IF;
297
298 FOR i IN acc_id.FIRST..acc_id.LAST LOOP
299 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
300 jtm_message_log_pkg.Log_Msg
301 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
302 , v_object_name => g_table_name
303 , v_message => 'Pushing record with access_id = ' || acc_id(i)
304 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
305 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
306 END IF;
307
308 l_dummy := asg_download.markdirty(
309 P_PUB_ITEM => g_publication_item_name(1)
310 , P_ACCESSID => acc_id(i)
311 , P_RESOURCEID => r_secinv_resources.resource_id
312 , P_DML => 'U'
313 , P_TIMESTAMP => SYSDATE
314 );
315 END LOOP;
316 END IF;
317
318 /*** Push inserted records to client ***/
319 acc_id.DELETE;
320 inv_id.DELETE;
321 org_id.DELETE;
322 sub_code.DELETE;
323 rvsion.DELETE;
324 loc_id.DELETE;
325 lot_num.DELETE;
326 acc_id.DELETE;
327 track_flag.DELETE;
328 SELECT ohqmv.INVENTORY_ITEM_ID
329 , ohqmv.ORGANIZATION_ID
330 , ohqmv.SUBINVENTORY_CODE
331 , ohqmv.REVISION,ohqmv.LOCATOR_ID
332 , ohqmv.LOT_NUMBER
333 , JTM_ACC_TABLE_S.NEXTVAL ACCESS_ID
334 , msi.COMMS_NL_TRACKABLE_FLAG
335 BULK COLLECT INTO inv_id, org_id, sub_code, rvsion, loc_id,lot_num, acc_id,track_flag
336 FROM csl_mtl_onhand_qty_mv ohqmv
337 , mtl_system_items msi
338 WHERE msi.INVENTORY_ITEM_ID = ohqmv.INVENTORY_ITEM_ID
339 AND msi.ORGANIZATION_ID = ohqmv.ORGANIZATION_ID
340 AND (ohqmv.subinventory_code, ohqmv.organization_id) IN
341 ( SELECT ila.subinventory_code, ila.organization_id
342 FROM csp_inv_loc_assignments ila
343 , jtm_csp_inv_loc_ass_acc ilaacc
344 WHERE ilaacc.resource_id = r_secinv_resources.resource_id
345 AND ilaacc.csp_inv_loc_assignment_id = ila.csp_inv_loc_assignment_id
346 AND SYSDATE BETWEEN NVL(ila.effective_date_start, SYSDATE)
347 AND NVL(ila.effective_date_end, SYSDATE)
348 )
349 AND NOT EXISTS (
350 SELECT NULL
351 FROM csl_mtl_onhand_qty_acc ohqacc
352 WHERE ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
353 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
354 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
355 AND ohqacc.organization_id = ohqmv.organization_id
359 );
356 AND ohqacc.inventory_item_id = ohqmv.inventory_item_id
357 AND ohqacc.subinventory_code = ohqmv.subinventory_code
358 AND ohqacc.resource_id = r_secinv_resources.resource_id
360
361 IF (acc_id.COUNT > 0) THEN
362 /*** push to oLite using asg_download ***/
363 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
364 jtm_message_log_pkg.Log_Msg
365 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
366 , v_object_name => g_table_name
367 , v_message => 'Pushing ' || acc_id.COUNT || ' inserted record(s)'
368 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
369 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
370 END IF;
371
372 FORALL i IN acc_id.FIRST..acc_id.LAST
373 INSERT INTO CSL_MTL_ONHAND_QTY_ACC (RESOURCE_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,
374 SUBINVENTORY_CODE,REVISION,LOCATOR_ID,LOT_NUMBER,ACCESS_ID,COUNTER,LAST_UPDATE_DATE,LAST_UPDATED_BY,
375 CREATION_DATE,CREATED_BY) VALUES (r_secinv_resources.resource_id,inv_id(i), org_id(i), sub_code(i),
376 rvsion(i), loc_id(i), lot_num(i), acc_id(i),1,SYSDATE,1,SYSDATE,1);
377
378 FOR i IN acc_id.FIRST..acc_id.LAST LOOP
379
380 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
381 jtm_message_log_pkg.Log_Msg
382 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
383 , v_object_name => g_table_name
384 , v_message => 'Pushing record with access_id = ' || acc_id(i)
385 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
386 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
387 END IF;
388
389 l_dummy := asg_download.markdirty(
390 P_PUB_ITEM => g_publication_item_name(1)
391 , P_ACCESSID => acc_id(i)
392 , P_RESOURCEID => r_secinv_resources.resource_id
393 , P_DML => 'I'
394 , P_TIMESTAMP => SYSDATE
395 );
396
397 IF NVL(track_flag(i),'N') <> 'Y' THEN
398 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
399 jtm_message_log_pkg.Log_Msg
400 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
401 , v_object_name => g_table_name
402 , v_message => 'Calling CSL_MTL_SYSTEM_ITEMS_ACC_PKG.PRE_INSERT_CHILD'
403 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
404 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
405 END IF;
406 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.PRE_INSERT_CHILD(inv_id(i),org_id(i),r_secinv_resources.resource_id);
407 END IF;
408
409 END LOOP;
410 END IF;
411
412 /*** Push deleted records to client ***/
413 acc_id.DELETE;
414 track_flag.DELETE;
415 inv_id.DELETE;
416 org_id.DELETE;
417 sub_code.DELETE;
418 SELECT ohqacc.ACCESS_ID
419 , ohqacc.inventory_item_id
420 , ohqacc.organization_id
421 , ohqacc.subinventory_code
422 , msi.COMMS_NL_TRACKABLE_FLAG
423 BULK COLLECT INTO acc_id,inv_id, org_id, sub_code, track_flag
424 FROM csl_mtl_onhand_qty_acc ohqacc
425 , mtl_system_items msi
426 WHERE msi.INVENTORY_ITEM_ID = ohqacc.INVENTORY_ITEM_ID
427 AND msi.ORGANIZATION_ID = ohqacc.ORGANIZATION_ID
428 AND ohqacc.resource_id = r_secinv_resources.resource_id
429 AND NOT EXISTS (
430 SELECT null
431 FROM csl_mtl_onhand_qty_mv ohqmv
432 WHERE ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
433 AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
434 AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
435 AND ohqacc.organization_id = ohqmv.organization_id
436 AND ohqacc.inventory_item_id = ohqmv.inventory_item_id
437 AND ohqacc.subinventory_code = ohqmv.subinventory_code
438 );
439
440 IF (acc_id.COUNT > 0) THEN
441 /*** push to oLite using asg_download ***/
442 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
443 jtm_message_log_pkg.Log_Msg
444 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
445 , v_object_name => g_table_name
446 , v_message => 'Pushing ' || acc_id.COUNT || ' deleted record(s)'
447 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
448 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
449 END IF;
450
451 FOR i IN acc_id.FIRST..acc_id.LAST LOOP
452
453 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
454 jtm_message_log_pkg.Log_Msg
455 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
456 , v_object_name => g_table_name
457 , v_message => 'Pushing record with access_id = ' || acc_id(i)
458 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
459 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
460 END IF;
461
462 l_dummy := asg_download.markdirty(
463 P_PUB_ITEM => g_publication_item_name(1)
464 , P_ACCESSID => acc_id(i)
465 , P_RESOURCEID => r_secinv_resources.resource_id
466 , P_DML => 'D'
470 IF NVL(track_flag(i),'N') <> 'Y' THEN
467 , P_TIMESTAMP => SYSDATE
468 );
469
471 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
472 jtm_message_log_pkg.Log_Msg
473 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
474 , v_object_name => g_table_name
475 , v_message => 'Calling CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_CHILD'
476 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
477 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
478 END IF;
479
480 CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_CHILD( inv_id(i)
481 , org_id(i)
482 , r_secinv_resources.resource_id);
483 END IF;
484 END LOOP;
485
486 FORALL i IN acc_id.FIRST..acc_id.LAST
487 DELETE CSL_MTL_ONHAND_QTY_ACC
488 WHERE ACCESS_ID = acc_id(i);
489 END IF;
490 COMMIT;
491 END LOOP;
492
493 /** Call out to item instances program to insert the OHQ instances **/
494 CSL_CSI_ITEM_INSTANCES_ACC_PKG.CONC_ITEM_INSTANCES(l_current_run_date);
495
496 COMMIT;
497
498 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
499 jtm_message_log_pkg.Log_Msg
500 ( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
501 , v_object_name => g_table_name
502 , v_message => 'Leaving PROCESS_ACC'
503 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
504 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
505 END IF;
506 EXCEPTION WHEN OTHERS THEN
507 /*** hook failed -> log error ***/
508 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
509 jtm_message_log_pkg.Log_Msg
510 ( v_object_id => 1
511 , v_object_name => g_table_name
512 , v_message => 'Caught exception in PROCESS_ACC hook:' || fnd_global.local_chr(10) || sqlerrm
513 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
514 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
515 END IF;
516 ROLLBACK;
517 fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','PROCESS_ACC',sqlerrm);
518 END PROCESS_ACC;
519
520 /*Delete all records for non-existing user ( e.g user was deleted )*/
521 PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
522 , x_return_status OUT NOCOPY VARCHAR2 )
523 IS
524 l_tab_access_id dbms_sql.Number_Table;
525 l_dummy BOOLEAN;
526 BEGIN
527 /*** get debug level ***/
528 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
529 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
530 jtm_message_log_pkg.Log_Msg
531 ( p_resource_id
532 , g_table_name
533 , 'Entering DELETE_ALL_ACC_RECORDS'
534 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
535 , v_module => 'csl_mtl_onhand_qty_acc_pkg'
536 );
537 END IF;
538
539 /* DELETE CSL_MTL_ONHAND_QTY_ACC
540 WHERE RESOURCE_ID = p_resource_id
541 RETURNING ACCESS_ID BULK COLLECT INTO l_tab_access_id;*/
542
543 SELECT ACCESS_ID
544 BULK COLLECT INTO l_tab_access_id
545 FROM CSL_MTL_ONHAND_QTY_ACC
546 WHERE RESOURCE_ID = p_resource_id;
547
548 IF l_tab_access_id.COUNT > 0 THEN
549 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
550 jtm_message_log_pkg.Log_Msg
551 ( p_resource_id
552 , g_table_name
553 , 'Pulling '||l_tab_access_id.COUNT||' records from Oracle Lite for resource '||p_resource_id
554 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
555 , v_module => 'csl_mtl_onhand_qty_acc_pkg'
556 );
557 END IF;
558
559 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST LOOP
560 /*** 1 or more acc rows retrieved -> push to resource ***/
561 l_dummy := asg_download.markdirty(
562 P_PUB_ITEM => g_publication_item_name(1)
563 , P_ACCESSID => l_tab_access_id(i)
564 , P_RESOURCEID => p_resource_id
565 , P_DML => 'D'
566 , P_TIMESTAMP => SYSDATE
567 );
568 END LOOP;
569
570 FORALL i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
571 DELETE CSL_MTL_ONHAND_QTY_ACC
572 WHERE ACCESS_ID = l_tab_access_id(i);
573 END IF;
574
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576
577 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
578 jtm_message_log_pkg.Log_Msg
579 ( p_resource_id
580 , g_table_name
581 , 'Leaving DELETE_ALL_ACC_RECORDS'
582 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
583 , v_module => 'csl_mtl_onhand_qty_acc_pkg'
584 );
585 END IF;
586 EXCEPTION WHEN OTHERS THEN
587 /*** hook failed -> log error ***/
588 -- x_return_status := FND_API.G_RET_STS_ERROR;
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
591 jtm_message_log_pkg.Log_Msg
592 ( v_object_id => 1
593 , v_object_name => g_table_name
594 , v_message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
595 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
596 , v_module => 'csl_mtl_onhand_qty_acc_pkg');
597 END IF;
598 fnd_msg_pub.Add_Exc_Msg('CSL_MTL_ONHAND_QTY_ACC_PKG','PROCESS_ACC',sqlerrm);
599 END DELETE_ALL_ACC_RECORDS;
600
601
602
603 END CSL_MTL_ONHAND_QTY_ACC_PKG;