[Home] [Help]
PACKAGE BODY: APPS.PO_CLM_UPG_PVT
Source
1 PACKAGE BODY PO_CLM_UPG_PVT AS
2 /* $Header: PO_CLM_UPG_PVT.plb 120.8.12020000.3 2013/02/10 20:56:11 vegajula ship $ */
3 -- Start of comments
4 -- API name : merge_tbl_varchar240
5 -- Pre-reqs : None.
6 -- Modifiers : none
7 -- Function : this api returns the UNION ALL of the input tables.
8 -- Parameters :
9 -- IN :l_entity_pk_tbl IN PO_TBL_VARCHAR240
10 -- l_temp_entity_pk_tbl IN PO_TBL_VARCHAR240
11 -- OUT :
12 -- IN OUT :
13 FUNCTION merge_tbl_varchar240(l_entity_pk_tbl IN PO_TBL_VARCHAR240,
14 l_temp_entity_pk_tbl IN PO_TBL_VARCHAR240)
15 RETURN PO_TBL_VARCHAR240
16 IS
17 l_entity_pk1_tbl PO_TBL_VARCHAR240;
18 BEGIN
19 l_entity_pk1_tbl := PO_TBL_VARCHAR240();
20 SELECT *
21 BULK COLLECT INTO l_entity_pk1_tbl
22 FROM (SELECT * FROM TABLE(l_entity_pk_tbl)
23 UNION ALL
24 SELECT * FROM TABLE(l_temp_entity_pk_tbl)
25 );
26 RETURN l_entity_pk1_tbl;
27 END merge_tbl_varchar240;
28
29 -- Start of comments
30 -- API name : merge_tbl_varchar30
31 -- Pre-reqs : None.
32 -- Modifiers : none
33 -- Function : this api returns the UNION ALL of the input tables.
34 -- Parameters :
35 -- IN :l_entity_pk_tbl IN PO_TBL_VARCHAR30
36 -- l_temp_entity_pk_tbl IN PO_TBL_VARCHAR30
37 -- OUT :
38 -- IN OUT :
39 FUNCTION merge_tbl_varchar30(l_entity_pk_tbl IN PO_TBL_VARCHAR30,
40 l_temp_entity_pk_tbl IN PO_TBL_VARCHAR30)
41 RETURN PO_TBL_VARCHAR30
42 IS
43 l_entity_pk1_tbl PO_TBL_VARCHAR30;
44 BEGIN
45 l_entity_pk1_tbl := PO_TBL_VARCHAR30();
46 SELECT *
47 BULK COLLECT INTO l_entity_pk1_tbl
48 FROM (SELECT * FROM TABLE(l_entity_pk_tbl)
49 UNION ALL
50 SELECT * FROM TABLE(l_temp_entity_pk_tbl)
51 );
52 RETURN l_entity_pk1_tbl;
53 END merge_tbl_varchar30;
54
55 PROCEDURE enable_conc_mod_for_styleId( p_style_id IN NUMBER);
56 /* Bug 13938456 -Procedure to create attachment level locks*/
57 PROCEDURE lock_attachments(
58 p_entity_name_tbl PO_TBL_VARCHAR30,
59 p_draft_id NUMBER ,
60 p_pk1_tbl PO_TBL_VARCHAR240 ,
61 p_pk2_tbl PO_TBL_VARCHAR240 ,
62 p_pk3_tbl PO_TBL_VARCHAR240 ,
63 p_pk4_tbl PO_TBL_VARCHAR240
64 );
65
66
67 -- Start of comments
68 -- API name : enable_conc_mod
69 -- Pre-reqs : None.
70 -- Function :For the old data, i.e. Modifications created before this
71 -- Concurrent Modifications patch is applied,
72 -- the locks and change data will be missing.
73 -- This procedure calls another proedure enable_conc_mod_for_styleId
74 -- to create locks for all the entities modified by the open Modifications.
75 -- Parameters :
76 -- IN :p_style_id IN NUMBER
77 -- OUT :
78 -- IN-OUT :
79 -- End of comments
80 PROCEDURE enable_conc_mod( p_style_id IN NUMBER)
81 IS
82 l_style_id_tbl PO_TBL_NUMBER;
83 BEGIN
84 l_style_id_tbl := PO_TBL_NUMBER();
85 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod:p_style_id: '||p_style_id);
86
87 -- If style_id is null then locks are created for ALL style_id's having
88 -- enable_conc_mod_for_styleId='modification' and CONC_MODS_ENABLED_FLAG='N'
89
90 IF p_style_id IS NULL
91 THEN
92 SELECT style_id
93 BULK COLLECT
94 INTO l_style_id_tbl
95 FROM PO_DOC_STYLE_HEADERS
96 WHERE CHANGE_PROCESS_TYPE = 'MODIFICATION'
97 AND NVL(CONC_MODS_ENABLED_FLAG,'N') = 'N' ;
98 ELSE
99 SELECT p_style_id
100 BULK COLLECT
101 INTO l_style_id_tbl
102 FROM dual
103 WHERE EXISTS ( SELECT 'conc_mod_enabled_flag is N '
104 FROM PO_DOC_STYLE_HEADERS
105 WHERE NVL(CONC_MODS_ENABLED_FLAG,'N') = 'N'
106 AND CHANGE_PROCESS_TYPE = 'MODIFICATION');
107
108 FOR i in 1.. l_style_id_tbl.COUNT LOOP
109 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod:l_style_id_tbl('||i||')'||l_style_id_tbl(i));
110 END LOOP;
111 END IF;
112
113 FOR i IN 1.. l_style_id_tbl.COUNT LOOP
114 enable_conc_mod_for_styleId(l_style_id_tbl(i));
115 END LOOP;
116
117 END enable_conc_mod;
118
119
120 -- Start of comments
121 -- API name : enable_conc_mod_for_styleId
122 -- Pre-reqs : None.
123 -- Function : This Procedure creates locks for all the entities modified by the open Modifications for
124 -- the given style_id.
125 -- Also,For records existing in all draft tables corresponding to open Modifications,
126 -- create rows in po_entity_locks table with highest level of locks
127 -- partial lock for header and addresses,
128 -- and full locks for lines, shipments, distributions and attachments.
129 -- Parameters :
130 -- IN :p_style_id IN NUMBER
131 -- OUT :
132 -- IN OUT :
133 -- End of comments
134 PROCEDURE enable_conc_mod_for_styleId( p_style_id IN NUMBER)
135 IS
136 l_draft_id_tbl PO_TBL_NUMBER;
137 l_entity_pk1_tbl PO_TBL_VARCHAR240;
138 l_entity_name_tbl PO_TBL_VARCHAR30;
139 l_entity_pk2_tbl PO_TBL_VARCHAR240;
140 l_entity_pk3_tbl PO_TBL_VARCHAR240;
141 l_entity_pk4_tbl PO_TBL_VARCHAR240;
142 l_temp_entity_pk1_tbl PO_TBL_VARCHAR240;
143 l_temp_entity_name_tbl PO_TBL_VARCHAR30;
144 l_temp_entity_pk2_tbl PO_TBL_VARCHAR240;
145 l_temp_entity_pk3_tbl PO_TBL_VARCHAR240;
146 l_temp_entity_pk4_tbl PO_TBL_VARCHAR240;
147 x_return_status VARCHAR2(10);
148 x_results PO_VALIDATION_RESULTS_TYPE;
149
150 BEGIN
151 l_draft_id_tbl := po_tbl_number();
152 l_entity_pk1_tbl := PO_TBL_VARCHAR240();
153 l_entity_name_tbl := PO_TBL_VARCHAR30();
154 l_entity_pk2_tbl :=PO_TBL_VARCHAR240();
155 l_entity_pk3_tbl :=PO_TBL_VARCHAR240();
156 l_entity_pk4_tbl :=PO_TBL_VARCHAR240();
157 l_temp_entity_pk1_tbl :=PO_TBL_VARCHAR240();
158 l_temp_entity_name_tbl :=PO_TBL_VARCHAR30();
159 l_temp_entity_pk2_tbl :=PO_TBL_VARCHAR240();
160 l_temp_entity_pk3_tbl :=PO_TBL_VARCHAR240();
161 l_temp_entity_pk4_tbl :=PO_TBL_VARCHAR240();
162 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:p_style_id: '||p_style_id);
163 --commit or rollback to savepoint and exit
164 SAVEPOINT update_sp;
165 -- collect draft_id for all open modifications having the given style_id
166
167 SELECT pod.draft_id
168 BULK COLLECT
169 INTO l_draft_id_tbl
170 FROM po_drafts pod,
171 po_headers_all poh
172 WHERE pod.document_id = poh.po_header_id
173 AND poh.style_id = p_style_id
174 AND pod.draft_type= 'MOD'
175 AND pod.status IN ('DRAFT','REJECTED','IN PROCESS',
176 'PRE-APPROVED','SUPPLIER SIGN');
177
178
179 FOR i in 1.. l_draft_id_tbl.COUNT LOOP
180 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:l_draft_id_tbl('||i||')'||l_draft_id_tbl(i));
181 END LOOP;
182
183 FOR i IN 1 .. l_draft_id_tbl.COUNT LOOP
184
185 SELECT poha.po_header_id ,
186 PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY ,
187 null,
188 null,
189 null
190 BULK COLLECT
191 INTO l_entity_pk1_tbl
192 , l_entity_name_tbl
193 , l_entity_pk2_tbl
194 , l_entity_pk3_tbl
195 , l_entity_pk4_tbl
196 FROM po_headers_draft_all poha
197 WHERE poha.draft_id = l_draft_id_tbl(i);
198
199
200 SELECT pola.po_line_id,
201 PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY ,
202 null,
203 null,
204 null
205 BULK COLLECT
206 INTO l_temp_entity_pk1_tbl
207 , l_temp_entity_name_tbl
208 , l_temp_entity_pk2_tbl
209 , l_temp_entity_pk3_tbl
210 , l_temp_entity_pk4_tbl
211 FROM po_lines_draft_all pola
212 WHERE pola.draft_id= l_draft_id_tbl(i);
213
214 -- update tables
215 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update Lines Start');
216
217 l_entity_pk1_tbl:=merge_tbl_varchar240(l_entity_pk1_tbl,l_temp_entity_pk1_tbl);
218 l_entity_name_tbl := merge_tbl_varchar30(l_entity_name_tbl,l_temp_entity_name_tbl);
219 l_entity_pk2_tbl:=merge_tbl_varchar240(l_entity_pk2_tbl,l_temp_entity_pk2_tbl);
220 l_entity_pk3_tbl:=merge_tbl_varchar240(l_entity_pk3_tbl,l_temp_entity_pk3_tbl);
221 l_entity_pk4_tbl:=merge_tbl_varchar240(l_entity_pk4_tbl,l_temp_entity_pk4_tbl);
222
223 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update Lines END');
224 --end
225
226 SELECT polla.line_location_id ,
227 PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ENTITY ,
228 null,
229 null,
230 null
231 BULK COLLECT
232 INTO l_temp_entity_pk1_tbl
233 , l_temp_entity_name_tbl
234 , l_temp_entity_pk2_tbl
235 , l_temp_entity_pk3_tbl
236 , l_temp_entity_pk4_tbl
237 FROM PO_LINE_LOCATIONS_DRAFT_ALL polla
238 WHERE polla.draft_id= l_draft_id_tbl(i);
239
240 -- update tables
241 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update SHIPMENT Start');
242
243 l_entity_pk1_tbl:=merge_tbl_varchar240(l_entity_pk1_tbl,l_temp_entity_pk1_tbl);
244 l_entity_name_tbl := merge_tbl_varchar30(l_entity_name_tbl,l_temp_entity_name_tbl);
245 l_entity_pk2_tbl:=merge_tbl_varchar240(l_entity_pk2_tbl,l_temp_entity_pk2_tbl);
246 l_entity_pk3_tbl:=merge_tbl_varchar240(l_entity_pk3_tbl,l_temp_entity_pk3_tbl);
247 l_entity_pk4_tbl:=merge_tbl_varchar240(l_entity_pk4_tbl,l_temp_entity_pk4_tbl);
248
249 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update SHIPMENT End');
250 --end
251
252 SELECT pod.po_distribution_id,
253 PO_DRAFTS_PVT.G_LOCK_DIST_ENTITY ,
254 null,
255 null,
256 null
257 BULK COLLECT
258 INTO l_temp_entity_pk1_tbl
259 , l_temp_entity_name_tbl
260 , l_temp_entity_pk2_tbl
261 , l_temp_entity_pk3_tbl
262 , l_temp_entity_pk4_tbl
263 FROM PO_DISTRIBUTIONS_DRAFT_ALL pod
264 WHERE pod.draft_id= l_draft_id_tbl(i);
265
266 -- update tables
267 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update DISTRIBUTION Start');
268
269 l_entity_pk1_tbl:=merge_tbl_varchar240(l_entity_pk1_tbl,l_temp_entity_pk1_tbl);
270 l_entity_name_tbl := merge_tbl_varchar30(l_entity_name_tbl,l_temp_entity_name_tbl);
271 l_entity_pk2_tbl:=merge_tbl_varchar240(l_entity_pk2_tbl,l_temp_entity_pk2_tbl);
272 l_entity_pk3_tbl:=merge_tbl_varchar240(l_entity_pk3_tbl,l_temp_entity_pk3_tbl);
273 l_entity_pk4_tbl:=merge_tbl_varchar240(l_entity_pk4_tbl,l_temp_entity_pk4_tbl);
274
275 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update DISTRIBUTION End');
276 --end
277
278 SELECT phbd.po_header_id ,
279 PO_DRAFTS_PVT.G_LOCK_ADDRESS_ENTITY ,
280 phbd.c_ext_attr39,
281 null,
282 null
283 BULK COLLECT
284 INTO l_temp_entity_pk1_tbl
285 , l_temp_entity_name_tbl
286 , l_temp_entity_pk2_tbl
287 , l_temp_entity_pk3_tbl
288 , l_temp_entity_pk4_tbl
289 FROM po_headers_all_ext_b phbd
290 WHERE phbd.c_ext_attr40 ='PO_UDA_ADDRESS_TYPES'
291 -- multi-row attribute groups
292 AND EXISTS (SELECT 'Multi row attr grp'
293 FROM ego_fnd_dsc_flx_ctx_ext ag
294 WHERE phbd.attr_group_id = ag.attr_group_id
295 AND ag.multi_row = 'Y')
296 AND phbd.draft_id= l_draft_id_tbl(i);
297
298 -- update tables
299 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update UDA-ADDRESS Start');
304 l_entity_pk4_tbl:=merge_tbl_varchar240(l_entity_pk4_tbl,l_temp_entity_pk4_tbl);
300 l_entity_pk1_tbl:=merge_tbl_varchar240(l_entity_pk1_tbl,l_temp_entity_pk1_tbl);
301 l_entity_name_tbl := merge_tbl_varchar30(l_entity_name_tbl,l_temp_entity_name_tbl);
302 l_entity_pk2_tbl:=merge_tbl_varchar240(l_entity_pk2_tbl,l_temp_entity_pk2_tbl);
303 l_entity_pk3_tbl:=merge_tbl_varchar240(l_entity_pk3_tbl,l_temp_entity_pk3_tbl);
305 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Update UDA-ADDRESS End');
306 --end
307
308 FOR i in 1.. l_entity_pk1_tbl.COUNT LOOP
309 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:l_entity_pk1_tbl('||i||')'||l_entity_pk1_tbl(i));
310 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:l_entity_name_tbl('||i||')'||l_entity_name_tbl(i));
311 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:l_entity_pk2_tbl('||i||')'||l_entity_pk2_tbl(i));
312 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:l_entity_pk3_tbl('||i||')'||l_entity_pk3_tbl(i));
313 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:l_entity_pk4_tbl('||i||')'||l_entity_pk4_tbl(i));
314 END LOOP;
315
316 --calling PO_DRAFTS_PVT.lock_entities
317 PO_DRAFTS_PVT.lock_entities(
318 p_entity_name_tbl => l_entity_name_tbl,
319 p_draft_id => l_draft_id_tbl(i) ,
320 p_pk1_tbl => l_entity_pk1_tbl ,
321 p_pk2_tbl => l_entity_pk2_tbl ,
322 p_pk3_tbl => l_entity_pk3_tbl ,
323 p_pk4_tbl => l_entity_pk3_tbl ,
324 x_return_status => x_return_status,
325 x_results => x_results
326 );
327
328 -- To create attachment level locks
329 lock_attachments(
330 p_entity_name_tbl => l_entity_name_tbl,
331 p_draft_id => l_draft_id_tbl(i) ,
332 p_pk1_tbl => l_entity_pk1_tbl ,
333 p_pk2_tbl => l_entity_pk2_tbl ,
334 p_pk3_tbl => l_entity_pk3_tbl ,
335 p_pk4_tbl => l_entity_pk3_tbl
336 );
337
338
339 --update PO_HEADERS_DRAFT_ALL.OLD_AMOUNT_LIMIT
340 -- PO_LINES_DRAFT_ALL.OLD_AMOUNT
341 -- PO_LINE_LOCATIONS_DRAFT_ALL.OLD_AMOUNT
342 -- PO_DISTRIBUTIONS_DRAFT_ALL.OLD_AMOUNT_ORDERED
343 -- PO_DRAFTS.AGENT_ID
344
345 UPDATE PO_HEADERS_DRAFT_ALL phd
346 SET phd.OLD_AMOUNT_LIMIT = ( SELECT phb.AMOUNT_LIMIT
347 FROM PO_HEADERS_ALL phb
348 WHERE phb.po_header_id=phd.po_header_id)
349 WHERE phd.draft_id=l_draft_id_tbl(i);
350 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Number of Rows Updated PO_HEADERS_DRAFT_ALL'||SQL%ROWCOUNT);
351
352 UPDATE PO_LINES_DRAFT_ALL phd
353 SET phd.OLD_AMOUNT = ( SELECT phb.AMOUNT
354 FROM PO_LINES_ALL phb
355 WHERE phb.po_line_id= phd.po_line_id)
356 WHERE phd.draft_id=l_draft_id_tbl(i);
357 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Number of Rows Updated PO_LINES_DRAFT_ALL'||SQL%ROWCOUNT);
358
359 UPDATE PO_LINE_LOCATIONS_DRAFT_ALL phd
360 SET phd.OLD_AMOUNT = ( SELECT phb.AMOUNT
361 FROM PO_LINE_LOCATIONS_ALL phb
362 WHERE phb.line_location_id= phd.line_location_id)
363 WHERE phd.draft_id=l_draft_id_tbl(i);
364 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Number of Rows Updated PO_LINE_LOCATIONS_DRAFT_ALL'||SQL%ROWCOUNT);
365
366 UPDATE PO_DISTRIBUTIONS_DRAFT_ALL phd
367 SET phd.OLD_AMOUNT_ORDERED = ( SELECT phb.AMOUNT_ORDERED
368 FROM PO_DISTRIBUTIONS_ALL phb
369 WHERE phb.po_distribution_id= phd.po_distribution_id)
370 WHERE phd.draft_id=l_draft_id_tbl(i);
371 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Number of Rows Updated PO_DISTRIBUTIONS_DRAFT_ALL'||SQL%ROWCOUNT);
372
373 UPDATE PO_DRAFTS phd
374 SET phd.AGENT_ID = ( SELECT phb.AGENT_ID
375 FROM PO_HEADERS_ALL phb
376 WHERE phb.po_header_id=phd.document_id)
377 WHERE phd.draft_id=l_draft_id_tbl(i);
378 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Number of Rows Updated PO_DRAFTS'||SQL%ROWCOUNT);
379
380 IF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
381 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId: FAILED for draft_id:'||l_draft_id_tbl(i)
382 ||' for style_id:'||p_style_id);
383 FND_FILE.put_line(FND_FILE.output,'Failed to upgrade Modifications for the document style : '||p_style_id);
384 FND_FILE.put_line(FND_FILE.output,'Please check the log file for more details.');
385 ROLLBACK TO UPDATE_SP;
386 RETURN ;
387 ELSE
388 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId: SUCCESSFUL for draft_id:'||l_draft_id_tbl(i)
389 ||' for style_id:'||p_style_id);
390 COMMIT;
391 END IF;
392
393 END LOOP;
394
395 --set PO_DOC_STYLE_HEADERS.CONC_MODS_ENABLED_FLAG to Y
396 UPDATE PO_DOC_STYLE_HEADERS
397 SET CONC_MODS_ENABLED_FLAG='Y'
398 WHERE style_id=p_style_id;
399 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId:Number of Rows Updated PO_DOC_STYLE_HEADERS'||SQL%ROWCOUNT);
400 FND_FILE.put_line(FND_FILE.log,'enable_conc_mod_for_styleId: SUCCESFUL for STYLE_ID:'||p_style_id);
401 FND_FILE.put_line(FND_FILE.output,'Successfully upgraded Modifications for the document style x'||p_style_id);
402 COMMIT;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 FND_FILE.put_line(FND_FILE.log,('An error was encountered Errorcode- '||SQLCODE||' -Errormessage- '||SQLERRM));
407 FND_FILE.put_line(FND_FILE.output,('An exception Errorcode- '||SQLCODE||' occured check the logs for further details'));
408 END enable_conc_mod_for_styleId;
409
410
411 -------------------------------------------------------------------------------
412 --Bug 13938456 : Concurrent Mod Attachments
413 --Name: lock_attachments
414 --Procedure to create attachment level locks
415 --End of Comments
416 -------------------------------------------------------------------------------
417 PROCEDURE lock_attachments(
418 p_entity_name_tbl PO_TBL_VARCHAR30,
419 p_draft_id NUMBER ,
420 p_pk1_tbl PO_TBL_VARCHAR240 ,
421 p_pk2_tbl PO_TBL_VARCHAR240 ,
422 p_pk3_tbl PO_TBL_VARCHAR240 ,
423 p_pk4_tbl PO_TBL_VARCHAR240
424 ) AS
425
426 --Cursor to fetch the attachment level details for the mod at different entity levels
427
428 CURSOR fetch_attachment_ids(p_entity_id NUMBER, p_draft_id NUMBER, p_entity_name VARCHAR2) IS
429 SELECT attached_document_id
430 FROM fnd_attached_documents
431 WHERE pk1_value = To_Char(p_entity_id)||'-'||To_Char(p_draft_id)
432 AND entity_name = p_entity_name;
433
434 l_attachment_lock_type VARCHAR2(1) := 'P';
435
436 BEGIN
437
438 FND_FILE.put_line(FND_FILE.log,'creating attachment level locks for draft id '||p_draft_id);
439
440 --Iterate through the loop to check if any attachments exist
441 FOR itr IN 1..p_entity_name_tbl.Count() LOOP
442
443 -- For header attachments
444 CASE p_entity_name_tbl(itr)
445
446 WHEN 'PO_HEADER' THEN
447
448 --fetch header attachments
449 FOR l_rec IN fetch_attachment_ids(p_pk1_tbl(itr),p_draft_id,'PO_HEADERS') LOOP
450 -- make an entry in po_entity_locks table
451 INSERT INTO po_entity_locks(
452 po_entity_lock_id,
453 entity_name,
454 entity_pk1,
455 entity_pk2,
456 lock_by_draft_id,
457 lock_type,
458 last_update_date,
459 last_updated_by,
460 creation_date,
461 created_by
462 )
463 VALUES(
464 po_entity_locks_s.NEXTVAL,
465 PO_DRAFTS_PVT.G_LOCK_HEADER_ATTACH_ENTITY,
466 p_pk1_tbl(itr),
467 l_rec.attached_document_id,
468 p_draft_id,
469 l_attachment_lock_type,
470 SYSDATE,
471 FND_GLOBAL.user_id,
472 SYSDATE,
473 FND_GLOBAL.user_id
474 );
475 END LOOP;
476
477 WHEN 'PO_LINE' THEN
478
479 --fetch line attachments
480 FOR l_rec IN fetch_attachment_ids(p_pk1_tbl(itr),p_draft_id,'PO_LINES') LOOP
481 -- make an entry in po_entity_locks table
482 INSERT INTO po_entity_locks(
483 po_entity_lock_id,
484 entity_name,
485 entity_pk1,
486 entity_pk2,
487 lock_by_draft_id,
488 lock_type,
489 last_update_date,
490 last_updated_by,
491 creation_date,
492 created_by
493 )
494 VALUES(
495 po_entity_locks_s.NEXTVAL,
496 PO_DRAFTS_PVT.G_LOCK_LINE_ATTACH_ENTITY,
497 p_pk1_tbl(itr),
498 l_rec.attached_document_id,
499 p_draft_id,
500 l_attachment_lock_type,
501 SYSDATE,
502 FND_GLOBAL.user_id,
503 SYSDATE,
504 FND_GLOBAL.user_id
505 );
506
507 END LOOP;
508
509 WHEN 'PO_LINE_LOCATION' THEN
510
511 --Fetch line location level attachments
512 FOR l_rec IN fetch_attachment_ids(p_pk1_tbl(itr),p_draft_id,'PO_SHIPMENTS') LOOP
513
514 -- make an entry in po_entity_locks table
515 INSERT INTO po_entity_locks(
516 po_entity_lock_id,
517 entity_name,
518 entity_pk1,
519 entity_pk2,
520 lock_by_draft_id,
521 lock_type,
522 last_update_date,
523 last_updated_by,
524 creation_date,
525 created_by
526 )
527 VALUES(
528 po_entity_locks_s.NEXTVAL,
529 PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ATTACH_ENTITY,
530 p_pk1_tbl(itr),
531 l_rec.attached_document_id,
532 p_draft_id,
533 l_attachment_lock_type,
534 SYSDATE,
535 FND_GLOBAL.user_id,
536 SYSDATE,
537 FND_GLOBAL.user_id
538 );
539
540 END LOOP;
541 END CASE ;
542 END LOOP;
543
544 Exception
545 -- Other entity names can be ignored
546 when CASE_NOT_FOUND then NULL;
547
548 END lock_attachments;
549 END PO_CLM_UPG_PVT;