[Home] [Help]
PACKAGE BODY: APPS.CSM_CSP_REQ_LINES_EVENT_PKG
Source
1 PACKAGE BODY CSM_CSP_REQ_LINES_EVENT_PKG
2 /* $Header: csmerlb.pls 120.5.12020000.2 2013/04/09 10:58:37 saradhak ship $*/
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_table_name1 CONSTANT VARCHAR2(30) := 'CSP_REQUIREMENT_LINES';
16 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_REQ_LINES_ACC';
17 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_REQ_LINES_ACC_S';
18 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSM_REQ_LINES');
20 g_pk1_name1 CONSTANT VARCHAR2(30) := 'REQUIREMENT_LINE_ID';
21
22 PROCEDURE CSP_REQ_LINES_MDIRTY_I(p_requirement_line_id IN NUMBER, p_user_id IN NUMBER)
23 IS
24 l_sqlerrno VARCHAR2(20);
25 l_sqlerrmsg VARCHAR2(4000);
26 l_error_msg VARCHAR2(4000);
27 l_return_status VARCHAR2(2000);
28
29 BEGIN
30 CSM_UTIL_PKG.LOG('Entering CSP_REQ_LINES_MDIRTY_I for requirement_line_id: ' || p_requirement_line_id,
31 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
32
33 CSM_ACC_PKG.Insert_Acc
34 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
35 ,P_ACC_TABLE_NAME => g_acc_table_name1
36 ,P_SEQ_NAME => g_acc_sequence_name1
37 ,P_PK1_NAME => g_pk1_name1
38 ,P_PK1_NUM_VALUE => p_requirement_line_id
39 ,P_USER_ID => p_user_id
40 );
41
42 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_LINES_MDIRTY_I for requirement_line_id: ' || p_requirement_line_id,
43 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
44 EXCEPTION
45 WHEN OTHERS THEN
46 l_sqlerrno := to_char(SQLCODE);
47 l_sqlerrmsg := substr(SQLERRM, 1,2000);
48 l_error_msg := ' Exception in CSP_REQ_LINES_MDIRTY_I for requirement_line_id:'
49 || to_char(p_requirement_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
50 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
51 RAISE;
52 END CSP_REQ_LINES_MDIRTY_I;
53
54 PROCEDURE CSP_REQ_LINES_MDIRTY_D(p_requirement_line_id IN NUMBER, p_user_id IN NUMBER)
55 IS
56 l_sqlerrno VARCHAR2(20);
57 l_sqlerrmsg VARCHAR2(4000);
58 l_error_msg VARCHAR2(4000);
59 l_return_status VARCHAR2(2000);
60
61
62 BEGIN
63 CSM_UTIL_PKG.LOG('Entering CSP_REQ_LINES_MDIRTY_D for requirement_line_id: ' || p_requirement_line_id,
64 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
65
66 CSM_ACC_PKG.Delete_Acc
67 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
68 ,P_ACC_TABLE_NAME => g_acc_table_name1
69 ,P_PK1_NAME => g_pk1_name1
70 ,P_PK1_NUM_VALUE => p_requirement_line_id
71 ,P_USER_ID => p_user_id
72 );
73
74 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_LINES_MDIRTY_D for requirement_line_id: ' || p_requirement_line_id,
75 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
76 EXCEPTION
77 WHEN OTHERS THEN
78 l_sqlerrno := to_char(SQLCODE);
79 l_sqlerrmsg := substr(SQLERRM, 1,2000);
80 l_error_msg := ' Exception in CSP_REQ_LINES_MDIRTY_D for requirement_line_id:'
81 || to_char(p_requirement_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
82 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
83 RAISE;
84 END CSP_REQ_LINES_MDIRTY_D;
85
86 PROCEDURE CSP_REQ_LINES_MDIRTY_U(p_requirement_line_id IN NUMBER, p_user_id IN NUMBER)
87 IS
88 l_sqlerrno VARCHAR2(20);
89 l_sqlerrmsg VARCHAR2(4000);
90 l_error_msg VARCHAR2(4000);
91 l_return_status VARCHAR2(2000);
92 l_access_id NUMBER;
93
94 BEGIN
95 CSM_UTIL_PKG.LOG('Entering CSP_REQ_LINES_MDIRTY_U for requirement_line_id: ' || p_requirement_line_id,
96 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
97
98 l_access_id := CSM_ACC_PKG.Get_Acc_Id
99 ( P_ACC_TABLE_NAME => g_acc_table_name1
100 ,P_PK1_NAME => g_pk1_name1
101 ,P_PK1_NUM_VALUE => p_requirement_line_id
102 ,P_USER_ID => p_user_id
103 );
104
105 IF l_access_id <> -1 THEN
106 CSM_ACC_PKG.Update_Acc
107 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
108 ,P_ACC_TABLE_NAME => g_acc_table_name1
109 ,P_ACCESS_ID => l_access_id
110 ,P_USER_ID => p_user_id
111 );
112 END IF;
113
114 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_LINES_MDIRTY_U for requirement_line_id: ' || p_requirement_line_id,
115 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
116 EXCEPTION
117 WHEN OTHERS THEN
118 l_sqlerrno := to_char(SQLCODE);
119 l_sqlerrmsg := substr(SQLERRM, 1,2000);
120 l_error_msg := ' Exception in CSP_REQ_LINES_MDIRTY_U for requirement_line_id:'
121 || to_char(p_requirement_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
122 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_U',FND_LOG.LEVEL_EXCEPTION);
123 RAISE;
124 END CSP_REQ_LINES_MDIRTY_U;
125
126
127 PROCEDURE CONC_ORDER_UPDATE(p_status OUT NOCOPY VARCHAR2,
128 p_message OUT NOCOPY VARCHAR2)
129 IS
130
131 /*** get the last run date of the concurent program ***/
132 CURSOR c_LastRundate
133 IS
134 SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
135 FROM JTM_CON_REQUEST_DATA
136 WHERE package_name = 'CSM_CSP_REQ_LINES_EVENT_PKG'
137 AND procedure_name = 'CONC_ORDER_UPDATE';
138
139 r_LastRundate c_LastRundate%ROWTYPE;
140
141 /*CURSOR c_order_info (b_last_run_date DATE)
142 IS
143 SELECT acc.user_id
144 , acc.access_id
145 FROM csm_req_lines_acc acc
146 , CSP_REQ_LINE_DETAILS crld
147 , OE_ORDER_LINES_ALL ol
148 WHERE acc.requirement_line_id = crld.requirement_line_id
149 AND crld.source_id = ol.line_id
150 AND crld.SOURCE_TYPE='IO'
151 AND ol.LAST_UPDATE_DATE >= b_last_run_date */
152
153 CURSOR c_order_info (b_last_run_date DATE) IS
154 SELECT ol.line_id,acc.user_id,acc.requirement_line_id
155 FROM oe_order_lines_all ol,csm_req_lines_acc ACC, CSP_REQ_LINE_DETAILS crld
156 WHERE acc.requirement_line_id= crld.requirement_line_id and crld.source_type='IO' and ol.line_id=crld.source_id
157 AND ol.last_update_date > b_last_run_date
158 UNION ALL
159 SELECT ol.line_id,acc.user_id ,acc.requirement_line_id
160 FROM oe_order_lines_all ol,csm_req_lines_acc ACC, CSP_REQ_LINE_DETAILS crld
161 WHERE acc.requirement_line_id= crld.requirement_line_id and crld.source_type='IO' and ol.line_id=crld.source_id
162 AND ol.last_update_date < b_last_run_date
163 and ol.flow_status_code in ('AWAITING_SHIPPING' ,'PRODUCTION_COMPLETE')
164 AND EXISTS(SELECT 1 FROM wsh_delivery_details WHERE source_line_id=ol.line_id AND source_code = 'OE' AND LAST_UPDATE_DATE > b_last_run_date);
165
166
167 CURSOR c_res_info (b_last_run_date DATE)
168 IS
169 SELECT acc.user_id
170 , res.reservation_id
171 FROM csm_req_lines_acc acc
172 , CSP_REQ_LINE_DETAILS crld
173 , MTL_RESERVATIONS res
174 WHERE acc.requirement_line_id = crld.requirement_line_id
175 AND crld.source_id = res.RESERVATION_ID
176 AND crld.SOURCE_TYPE='RES'
177 AND res.LAST_UPDATE_DATE >= b_last_run_date ;
178
179 l_tab_rqmt_line_id ASG_DOWNLOAD.ACCESS_LIST;
180 l_tab_oe_line_id ASG_DOWNLOAD.ACCESS_LIST;
181 l_tab_res_id ASG_DOWNLOAD.ACCESS_LIST;
182 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
183 g_debug_level NUMBER;
184 l_dummy BOOLEAN;
185 l_current_run_date DATE;
186
187 CURSOR c_hdr_id(b_line_id NUMBER) IS
188 SELECT HEADER_ID FROM OE_ORDER_LINES_ALL WHERE LINE_ID=b_line_id;
189
190 l_hrd_id NUMBER;
191 BEGIN
192 CSM_UTIL_PKG.LOG('Entering CONC_ORDER_UPDATE','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);
193
194 l_current_run_date := sysdate;
195
196 /*** First retrieve last run date of the conccurent program ***/
197 OPEN c_LastRundate;
198 FETCH c_LastRundate INTO r_LastRundate;
199 CLOSE c_LastRundate;
200
201 l_tab_oe_line_id.DELETE;
202 l_tab_user_id.DELETE;
203 l_tab_rqmt_line_id.DELETE;
204
205 OPEN c_order_info(r_LastRundate.LAST_RUN_DATE);
206 FETCH c_order_info BULK COLLECT INTO l_tab_oe_line_id,l_tab_user_id,l_tab_rqmt_line_id;
207 CLOSE c_order_info;
208
209 CSM_UTIL_PKG.LOG('Updating Order Lines','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);
210
211 FOR i IN 1..l_tab_oe_line_id.COUNT
212 LOOP
213 -- mark dirty the record
214 l_dummy := asg_download.mark_dirty(
215 p_pub_item => 'CSM_ORDER_LINES'
216 , p_accessid => l_tab_oe_line_id(i)
217 , p_userid => l_tab_user_id(i)
218 , p_dml => 'U'
219 , p_timestamp => l_current_run_date
220 );
221
222 OPEN c_hdr_id(l_tab_oe_line_id(i));
223 FETCH c_hdr_id INTO l_hrd_id;
224 CLOSE c_hdr_id;
225 l_dummy := asg_download.mark_dirty(
226 p_pub_item => 'CSM_ORDER_HEADERS'
227 , p_accessid => l_hrd_id
228 , p_userid => l_tab_user_id(i)
229 , p_dml => 'U'
230 , p_timestamp => l_current_run_date
231 );
232
233 --Notify User of update on Order placed
234 IF(NOT CSM_UTIL_PKG.is_html5_user(l_tab_user_id(i)) AND NOT CSM_UTIL_PKG.is_new_mmu_user(CSM_UTIL_PKG.get_user_name(l_tab_user_id(i)))) THEN
235 CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT('CSM_REQ_LINES',to_char(l_tab_rqmt_line_id(i)),'UPDATE');
236 END IF;
237 END LOOP;
238
239
240 l_tab_res_id.DELETE;
241 l_tab_user_id.DELETE;
242
243 OPEN c_res_info(r_LastRundate.LAST_RUN_DATE);
244 FETCH c_res_info BULK COLLECT INTO l_tab_user_id, l_tab_res_id;
245 CLOSE c_res_info;
246
247 CSM_UTIL_PKG.LOG('Updating Reservation Lines','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);
248
249 FOR i IN 1..l_tab_res_id.COUNT
250 LOOP
251 -- mark dirty the record
252 l_dummy := asg_download.mark_dirty(
253 p_pub_item => 'CSM_MTL_RESERVATIONS'
254 , p_accessid => l_tab_res_id(i)
255 , p_userid => l_tab_user_id(i)
256 , p_dml => 'U'
257 , p_timestamp => l_current_run_date
258 );
259
260 END LOOP;
261
262 UPDATE JTM_CON_REQUEST_DATA
263 SET LAST_RUN_DATE = l_current_run_date
264 WHERE package_name = 'CSM_CSP_REQ_LINES_EVENT_PKG'
265 AND procedure_name = 'CONC_ORDER_UPDATE';
266
267 COMMIT;
268
269 CSM_UTIL_PKG.LOG('Leaving CONC_ORDER_UPDATE','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);
270
271 p_status := 'FINE';
272 p_message := 'CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE Executed successfully';
273
274 RETURN;
275 EXCEPTION WHEN OTHERS THEN
276 p_status := 'ERROR';
277 p_message := 'Error in CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE: ' || substr(SQLERRM, 1, 2000);
278 /*** hook failed -> log error ***/
279 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
280 jtm_message_log_pkg.Log_Msg
281 ( 1
282 , g_table_name1
283 , 'Caught exception in CONC_ORDER_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
284 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
285 END IF;
286 fnd_msg_pub.Add_Exc_Msg('CSM_CSP_REQ_LINES_EVENT_PKG','CONC_ORDER_UPDATE',sqlerrm);
287 END CONC_ORDER_UPDATE;
288
289 PROCEDURE doMarkDirty(p_pi IN VARCHAR2,p_a IN NUMBER,p_u IN NUMBER,p_d IN VARCHAR2)
290 IS
291 l_dummy BOOLEAN;
292 BEGIN
293 l_dummy := asg_download.mark_dirty(
294 p_pub_item => p_pi
295 , p_accessid => p_a
296 , p_userid => p_u
297 , p_dml => p_d
298 , p_timestamp => sysdate
299 );
300 END doMarkDirty;
301
302 PROCEDURE REQ_LINE_DTL_IUD(p_rld IN NUMBER, p_DML_t IN VARCHAR2)
303 IS
304 CURSOR c_get_dtl IS
305 SELECT ACCESS_ID,USER_ID,SOURCE_ID FROM CSM_REQ_LINE_DETAILS_ACC WHERE REQ_LINE_DETAIL_ID=p_rld;
306
307 CURSOR c_hdr_id(b_line_id NUMBER) IS
308 SELECT HEADER_ID FROM OE_ORDER_LINES_ALL WHERE LINE_ID=b_line_id;
309
310 l_sqlerrno VARCHAR2(20);
311 l_sqlerrmsg VARCHAR2(4000);
312 l_dummy boolean;
313 l_src VARCHAR2(30);
314 l_src_id NUMBER;
315 l_req_line_detail_id csp_req_line_details.req_line_detail_id%TYPE :=p_rld;
316 l_req_line_id csp_requirement_lines.requirement_line_id%TYPE;
317 l_found BOOLEAN:=false;
318 l_access_id NUMBER;
319 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
320 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
321 l_tab_item_id ASG_DOWNLOAD.ACCESS_LIST;
322 l_tab_org_id ASG_DOWNLOAD.ACCESS_LIST;
323 l_tab_old_src_id ASG_DOWNLOAD.ACCESS_LIST;
324 l_upd_dml_t VARCHAR2(10):=p_DML_t;
325 l_header_id NUMBER;
326 BEGIN
327 CSM_UTIL_PKG.LOG('Entering REQ_LINE_DTL_IUD for req_line_dtl_id:' || p_rld||' with DML:'||p_DML_t,
328 'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD',FND_LOG.LEVEL_PROCEDURE);
329
330 SELECT REQUIREMENT_LINE_ID,SOURCE_TYPE,SOURCE_ID INTO l_req_line_id,l_src,l_src_id FROM CSP_REQ_LINE_DETAILS WHERE req_line_detail_id=l_req_line_detail_id;
331
332 CSM_UTIL_PKG.LOG('Processing req_line_id:'||l_req_line_id||fnd_global.local_chr(10)||'req line detail_id:'||l_req_line_detail_id
333 ||fnd_global.local_chr(10)||'src type:'||l_src
334 ||fnd_global.local_chr(10)||'src id:'||l_src_id, 'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD', FND_LOG.LEVEL_PROCEDURE );
335
336 OPEN c_get_dtl;
337 FETCH c_get_dtl BULK COLLECT INTO l_tab_access_id,l_tab_user_id,l_tab_old_src_id;
338 CLOSE c_get_dtl;
339
340 IF (l_tab_access_id.COUNT=0) AND p_dml_t <> 'D' THEN
341 INSERT INTO CSM_REQ_LINE_DETAILS_ACC(ACCESS_ID,REQ_LINE_DETAIL_ID,USER_ID,ITEM_ID,ORG_ID,SOURCE_ID,CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY)
342 select CSM_REQ_LINES_ACC_S.nextval,req_line_detail_id,user_id,b.inventory_item_id,d.destination_organization_id,b.source_id,sysdate,sysdate,1,1
343 from (select b.req_line_detail_id,a.user_id,c.inventory_item_id,b.requirement_line_id,b.source_id
344 from csm_req_lines_acc a, csp_req_line_details b ,
345 oe_order_lines_all c ,asg_user au
346 where a.requirement_line_id=b.requirement_line_id
347 and b.source_type= 'IO' and b.source_id=c.line_id
348 and b.req_line_detail_id=l_req_line_detail_id
349 and au.user_id=a.user_id
350 UNION ALL
351 select b.req_line_detail_id,a.user_id,c.inventory_item_id,b.requirement_line_id,b.source_id
352 from csm_req_lines_acc a, csp_req_line_details b , mtl_reservations c,asg_user au
353 where a.requirement_line_id=b.requirement_line_id
354 and b.source_type= 'RES' and b.source_id=c.reservation_id
355 and b.req_line_detail_id=l_req_line_detail_id
356 and au.user_id=a.user_id ) b,
357 csp_requirement_headers d ,
358 csp_requirement_lines e
359 where e.requirement_line_id=b.requirement_line_id
360 and d.requirement_header_id=e.requirement_header_id
361 and not exists(select 1 from CSM_REQ_LINE_DETAILS_ACC acc
362 where acc.req_line_detail_id=b.req_line_detail_id
363 and acc.user_id=b.user_id);
364
365 IF (sql%rowcount=0) THEN
366 CSM_UTIL_PKG.LOG('Leaving-1 REQ_LINE_DTL_IUD for req_line_dtl_id: ' || p_rld, 'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD',FND_LOG.LEVEL_PROCEDURE);
367 RETURN;
368 END IF;
369
370 SELECT ACCESS_ID,USER_ID,ITEM_ID,ORG_ID BULK COLLECT INTO l_tab_access_id,l_tab_user_id,l_tab_item_id,l_tab_org_id
371 FROM CSM_REQ_LINE_DETAILS_ACC WHERE req_line_detail_id=l_req_line_detail_id;
372
373 l_dummy := asg_download.mark_dirty(
374 P_PUB_ITEM => 'CSM_REQ_LINE_DETAILS'
375 , p_accessList => l_tab_access_id
376 , p_userid_list => l_tab_user_id
377 , p_dml_type => 'I'
378 , P_TIMESTAMP => sysdate
379 );
380
381 l_found:=true;
382 END IF;
383
384 IF (l_tab_access_id.COUNT=0) THEN
385 CSM_UTIL_PKG.LOG('Leaving-2 REQ_LINE_DTL_IUD for req_line_dtl_id: ' || p_rld, 'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD',FND_LOG.LEVEL_PROCEDURE);
386 RETURN;
387 END IF;
388
389 FOR I IN 1..l_tab_access_id.COUNT
390 LOOP
391 IF NOT l_found THEN
392 doMarkDirty('CSM_REQ_LINE_DETAILS',l_tab_access_id(I),l_tab_user_id(I),p_DML_t);
393 END IF;
394
395 IF l_src_id IS NOT NULL THEN
396 IF p_DML_t='U' AND l_tab_old_src_id(I) <> l_src_id THEN --on receive, reservation gets deleted and new one gets created with RLD updated to new one
397 --on Addr change , RLD is updated with new IO line/header(Bug 16339885)
398
399 CSM_UTIL_PKG.LOG('Update Acc src and Mark Delete for old '||l_src||':'||l_tab_old_src_id(I)||' for user_id'||l_tab_user_id(I),
400 'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD',FND_LOG.LEVEL_PROCEDURE);
401
402 IF l_src='RES' THEN
403 doMarkDirty('CSM_MTL_RESERVATIONS',l_tab_old_src_id(I),l_tab_user_id(I),'D');
404 ELSIF l_src='IO' THEN
405 doMarkDirty('CSM_ORDER_LINES',l_tab_old_src_id(I),l_tab_user_id(I),'D');
406
407 OPEN c_hdr_id(l_tab_old_src_id(I));
408 FETCH c_hdr_id INTO l_header_id;
409 CLOSE c_hdr_id;
410
411 doMarkDirty('CSM_ORDER_HEADERS',l_header_id,l_tab_user_id(I),'D');
412 END IF;
413
414 l_upd_dml_t:='I';
415
416 UPDATE CSM_REQ_LINE_DETAILS_ACC SET SOURCE_ID=l_src_id
417 WHERE req_line_detail_id=l_req_line_detail_id AND USER_ID=l_tab_user_id(I);
418 END IF;
419
420 IF l_src='RES' THEN
421
422 doMarkDirty('CSM_MTL_RESERVATIONS',l_src_id,l_tab_user_id(I),l_upd_dml_t);
423
424 ELSIF l_src='IO' THEN
425
426 doMarkDirty('CSM_ORDER_LINES',l_src_id,l_tab_user_id(I),l_upd_dml_t);
427
428 OPEN c_hdr_id(l_src_id);
429 FETCH c_hdr_id INTO l_header_id;
430 CLOSE c_hdr_id;
431
432 doMarkDirty('CSM_ORDER_HEADERS',l_header_id,l_tab_user_id(I),l_upd_dml_t);
433
434 END IF;
435 END IF;
436
437 END LOOP;
438
439 IF p_DML_t='D' THEN
440 FORALL I IN 1.. l_tab_access_id.COUNT
441 DELETE FROM CSM_REQ_LINE_DETAILS_ACC WHERE ACCESS_ID=l_tab_access_id(I);
442 END IF;
443
444 IF p_DML_t<>'D' AND l_found THEN
445 FOR I IN 1..l_tab_item_id.COUNT --delete will be handled by mtl sys item refresh conc prog
446 LOOP
447 BEGIN
448 select 1 INTO l_src_id from csm_mtl_system_items_acc acc
449 where acc.inventory_item_id=l_tab_item_id(I)
450 and acc.organization_id=l_tab_org_id(I)
451 and acc.user_id=l_tab_user_id(I);
452 l_found:=true;
453 EXCEPTION
454 WHEN OTHERS THEN
455 l_found:=false;
456 END;
457
458 If NOT l_found THEN
459 select csm_mtl_system_items_acc_s.NEXTVAL INTO l_access_id from dual;
460
461 INSERT INTO csm_mtl_system_items_acc(access_id , user_id, inventory_item_id, organization_id, counter,
462 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
463 VALUES (l_access_id, l_tab_user_id(I),l_tab_item_id(I), l_tab_org_id(I), 1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
464
465 doMarkDirty('CSM_MTL_SYSTEM_ITEMS',l_access_id,l_tab_user_id(I),'I');
466
467 END IF;
468
469 END LOOP;
470 END IF;
471 CSM_UTIL_PKG.LOG('Leaving REQ_LINE_DTL_IUD for req_line_dtl_id: ' || p_rld,
472 'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD',FND_LOG.LEVEL_PROCEDURE);
473 EXCEPTION
474 WHEN OTHERS THEN
475 l_sqlerrno := to_char(SQLCODE);
476 l_sqlerrmsg := substr(SQLERRM, 1,2000);
477 CSM_UTIL_PKG.LOG(' Exception in REQ_LINE_DTL_IUD for req_line_dtl_id:'
478 || to_char(p_rld) || ':' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD',FND_LOG.LEVEL_EXCEPTION);
479 END REQ_LINE_DTL_IUD;
480
481 END CSM_CSP_REQ_LINES_EVENT_PKG;