[Home] [Help]
PACKAGE BODY: APPS.PO_LOCKS
Source
1 PACKAGE BODY PO_LOCKS AS
2 -- $Header: POXLOCKB.pls 120.2 2011/05/30 05:46:03 lswamina ship $
3
4
5
6 -----------------------------------------------------------------------------
7 -- Declare private package variables.
8 -----------------------------------------------------------------------------
9
10 -- Debugging
11
12 g_pkg_name CONSTANT
13 VARCHAR2(30)
14 := 'PO_LOCKS'
15 ;
16 g_log_head CONSTANT
17 VARCHAR2(50)
18 := 'po.plsql.' || g_pkg_name || '.'
19 ;
20
21 g_debug_stmt
22 BOOLEAN
23 ;
24 g_debug_unexp
25 BOOLEAN
26 ;
27
28
29
30
31 -----------------------------------------------------------------------------
32 -- Define procedures.
33 -----------------------------------------------------------------------------
34
35
36
37
38 -------------------------------------------------------------------------------
39 --Start of Comments
40 --Name: lock_headers
41 --Pre-reqs:
42 -- None.
43 --Modifies:
44 -- None.
45 --Locks:
46 -- PO_HEADERS_ALL
47 -- PO_RELEASES_ALL
48 -- PO_REQUISITION_HEADERS_ALL
49 --Function:
50 -- Locks the document headers of the given ids.
51 --Parameters:
52 --IN:
53 --p_doc_type
54 -- Document type. Use the g_doc_type_<> variables, where <> is:
55 -- REQUISITION
56 -- PA
57 -- PO
58 -- RELEASE
59 --p_doc_level
60 -- The type of ids that are being passed. Use g_doc_level_<>
61 -- HEADER
62 -- LINE
63 -- SHIPMENT
64 -- DISTRIBUTION
65 --p_doc_level_id_tbl
66 -- Ids of the doc level type of which to lock the header of the document.
67 --Testing:
68 --
69 --End of Comments
70 -------------------------------------------------------------------------------
71 PROCEDURE lock_headers(
72 p_doc_type IN VARCHAR2
73 , p_doc_level IN VARCHAR2
74 , p_doc_level_id_tbl IN po_tbl_number
75 , p_calling_mode IN VARCHAR2 DEFAULT NULL
76 )
77 IS
78
79 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'LOCK_HEADERS';
80 l_progress VARCHAR2(3) := '000';
81
82 l_doc_id_tbl po_tbl_number;
83 l_doc_id_key NUMBER;
84
85 ----------------------------------------------------------------
86 -- PO_SESSION_GT column mapping
87 --
88 -- num1 doc id
89 ----------------------------------------------------------------
90
91 CURSOR l_lock_req_csr(p_doc_id_key NUMBER) IS
92 SELECT NULL
93 FROM
94 PO_REQUISITION_HEADERS_ALL PRH
95 , PO_SESSION_GT IDS
96 WHERE PRH.requisition_header_id = IDS.num1
97 AND IDS.key = p_doc_id_key
98 FOR UPDATE OF PRH.requisition_header_id
99 NOWAIT
100 ;
101
102 CURSOR l_lock_release_csr(p_doc_id_key NUMBER) IS
103 SELECT NULL
104 FROM
105 PO_RELEASES_ALL POR
106 , PO_SESSION_GT IDS
107 WHERE POR.po_release_id = IDS.num1
108 AND IDS.key = p_doc_id_key
109 FOR UPDATE OF POR.po_release_id
110 NOWAIT
111 ;
112
113 CURSOR l_lock_po_csr(p_doc_id_key NUMBER) IS
114 SELECT NULL
115 FROM
116 PO_HEADERS_ALL POH
117 , PO_SESSION_GT IDS
118 WHERE POH.po_header_id = IDS.num1
119 AND IDS.key = p_doc_id_key
120 FOR UPDATE OF POH.po_header_id
121 NOWAIT
122 ;
123
124 /*Bug8512125 - Defined a new set of cursors to lock records when the calling mode is RCV
125 This will wait indefinitely till it aquires lock*/
126
127 CURSOR l_rcv_lock_req_csr(p_doc_id_key NUMBER) IS
128 SELECT NULL
129 FROM
130 PO_REQUISITION_HEADERS_ALL PRH
131 , PO_SESSION_GT IDS
132 WHERE PRH.requisition_header_id = IDS.num1
133 AND IDS.key = p_doc_id_key
134 FOR UPDATE
135 ;
136
137 CURSOR l_rcv_lock_release_csr(p_doc_id_key NUMBER) IS
138 SELECT NULL
139 FROM
140 PO_RELEASES_ALL POR
141 , PO_SESSION_GT IDS
142 WHERE POR.po_release_id = IDS.num1
143 AND IDS.key = p_doc_id_key
144 FOR UPDATE
145 ;
146
147 CURSOR l_rcv_lock_po_csr(p_doc_id_key NUMBER) IS
148 SELECT NULL
149 FROM
150 PO_HEADERS_ALL POH
151 , PO_SESSION_GT IDS
152 WHERE POH.po_header_id = IDS.num1
153 AND IDS.key = p_doc_id_key
154 FOR UPDATE
155 ;
156
157
158 BEGIN
159
160 IF g_debug_stmt THEN
161 PO_DEBUG.debug_begin(l_log_head);
162 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
163 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
164 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
165 PO_DEBUG.debug_var(l_log_head,l_progress,'p_calling_mode', p_calling_mode);
166 END IF;
167
168 l_progress := '010';
169
170 -- Get the header ids for the given doc level ids.
171
172 PO_CORE_S.get_document_ids(
173 p_doc_type => p_doc_type
174 , p_doc_level => p_doc_level
175 , p_doc_level_id_tbl => p_doc_level_id_tbl
176 , x_doc_id_tbl => l_doc_id_tbl
177 );
178
179 l_progress := '020';
180
181 -- Put the header ids into the scratchpad so that the cursors work.
182
183 SELECT PO_SESSION_GT_S.nextval
184 INTO l_doc_id_key
185 FROM DUAL
186 ;
187
188 l_progress := '030';
189
190 FORALL i IN 1 .. l_doc_id_tbl.COUNT
191 INSERT INTO PO_SESSION_GT ( key, num1 )
192 VALUES ( l_doc_id_key, l_doc_id_tbl(i) )
193 ;
194
195 l_progress := '040';
196
197 -- Cursors lock the rows on OPEN, and we don't need to SELECT anything.
198 /*Bug8512125 If the calling mode is RCV we run a new set of cursors defined*/
199
200 IF (p_calling_mode = 'RCV') THEN
201
202 IF (p_doc_type = PO_CORE_S.g_doc_type_REQUISITION) THEN
203
204 l_progress := '050';
205 IF g_debug_stmt THEN
206 PO_DEBUG.debug_stmt(l_log_head,l_progress,'requisition');
207 END IF;
208
209 OPEN l_rcv_lock_req_csr(p_doc_id_key => l_doc_id_key);
210 CLOSE l_rcv_lock_req_csr;
211
212 l_progress := '060';
213
214 ELSIF (p_doc_type = PO_CORE_S.g_doc_type_RELEASE) THEN
215
216 l_progress := '070';
217 IF g_debug_stmt THEN
218 PO_DEBUG.debug_stmt(l_log_head,l_progress,'release');
219 END IF;
220
221 OPEN l_rcv_lock_release_csr(p_doc_id_key => l_doc_id_key);
222 CLOSE l_rcv_lock_release_csr;
223
224 l_progress := '080';
225
226 ELSIF (p_doc_type IN (PO_CORE_S.g_doc_type_PO, PO_CORE_S.g_doc_type_PA)) THEN
227
228 l_progress := '090';
229 IF g_debug_stmt THEN
230 PO_DEBUG.debug_stmt(l_log_head,l_progress,'po, pa');
231 END IF;
232
233 OPEN l_rcv_lock_po_csr(p_doc_id_key => l_doc_id_key);
234 CLOSE l_rcv_lock_po_csr;
235
236 l_progress := '100';
237
238 ELSE
239
240 l_progress := '190';
241
242 RAISE PO_CORE_S.g_INVALID_CALL_EXC;
243
244 END IF;
245
246 ELSE /* If calling mode is not RCV*/
247
248 IF (p_doc_type = PO_CORE_S.g_doc_type_REQUISITION) THEN
249
250 l_progress := '200';
251 IF g_debug_stmt THEN
252 PO_DEBUG.debug_stmt(l_log_head,l_progress,'requisition');
253 END IF;
254
255 OPEN l_lock_req_csr(p_doc_id_key => l_doc_id_key);
256 CLOSE l_lock_req_csr;
257
258 l_progress := '210';
259
260 ELSIF (p_doc_type = PO_CORE_S.g_doc_type_RELEASE) THEN
261
262 l_progress := '220';
263 IF g_debug_stmt THEN
264 PO_DEBUG.debug_stmt(l_log_head,l_progress,'release');
265 END IF;
266
267 OPEN l_lock_release_csr(p_doc_id_key => l_doc_id_key);
268 CLOSE l_lock_release_csr;
269
270 l_progress := '230';
271
272 ELSIF (p_doc_type IN (PO_CORE_S.g_doc_type_PO, PO_CORE_S.g_doc_type_PA)) THEN
273
274 l_progress := '240';
275 IF g_debug_stmt THEN
276 PO_DEBUG.debug_stmt(l_log_head,l_progress,'po, pa');
277 END IF;
278
279 OPEN l_lock_po_csr(p_doc_id_key => l_doc_id_key);
280 CLOSE l_lock_po_csr;
281
282 l_progress := '250';
283
284 ELSE
285
286 l_progress := '300';
287
288 RAISE PO_CORE_S.g_INVALID_CALL_EXC;
289
290 END IF;
291
292 END IF;
293
294
295 l_progress := '900';
296
297 IF g_debug_stmt THEN
298 PO_DEBUG.debug_end(l_log_head);
299 END IF;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 IF g_debug_unexp THEN
304 PO_DEBUG.debug_exc(l_log_head,l_progress);
305 END IF;
306 RAISE;
307
308 END lock_headers;
309
310
311
312
313 -------------------------------------------------------------------------------
314 --Start of Comments
315 --Name: lock_distributions
316 --Pre-reqs:
317 -- None.
318 --Modifies:
319 -- None.
320 --Locks:
321 -- PO_DISTRIBUTIONS_ALL
322 -- PO_REQ_DISTRIBUTIONS_ALL
323 --Function:
324 -- Locks the distributions below the given ids.
325 --Parameters:
326 --IN:
327 --p_doc_type
328 -- Document type. Use the g_doc_type_<> variables, where <> is:
329 -- REQUISITION
330 -- PA
331 -- PO
332 -- RELEASE
333 --p_doc_level
334 -- The type of ids that are being passed. Use g_doc_level_<>
335 -- HEADER
336 -- LINE
337 -- SHIPMENT
338 -- DISTRIBUTION
339 --p_doc_level_id_tbl
340 -- Ids of the doc level type of which to lock the header of the document.
341 --Testing:
342 --
343 --End of Comments
344 -------------------------------------------------------------------------------
345 PROCEDURE lock_distributions(
346 p_doc_type IN VARCHAR2
347 , p_doc_level IN VARCHAR2
348 , p_doc_level_id_tbl IN po_tbl_number
349 )
350 IS
351
352 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'LOCK_DISTRIBUTIONS';
353 l_progress VARCHAR2(3) := '000';
354
355 l_dist_id_tbl po_tbl_number;
356 l_dist_id_key NUMBER;
357
358 ----------------------------------------------------------------
359 -- PO_SESSION_GT column mapping
360 --
361 -- num1 distribution id
362 ----------------------------------------------------------------
363
364 CURSOR l_lock_req_csr(p_dist_id_key NUMBER) IS
365 SELECT NULL
366 FROM
367 PO_REQ_DISTRIBUTIONS_ALL PRD
368 , PO_SESSION_GT DIST_IDS
369 WHERE PRD.distribution_id = DIST_IDS.num1
370 AND DIST_IDS.key = p_dist_id_key
371 FOR UPDATE OF PRD.distribution_id
372 NOWAIT
373 ;
374
375 CURSOR l_lock_nonreq_csr(p_dist_id_key NUMBER) IS
376 SELECT NULL
377 FROM
378 PO_DISTRIBUTIONS_ALL POD
379 , PO_SESSION_GT DIST_IDS
380 WHERE POD.po_distribution_id = DIST_IDS.num1
381 AND DIST_IDS.key = p_dist_id_key
382 FOR UPDATE OF POD.po_distribution_id
383 NOWAIT
384 ;
385
386 BEGIN
387
388 IF g_debug_stmt THEN
389 PO_DEBUG.debug_begin(l_log_head);
390 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_type', p_doc_type);
391 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level', p_doc_level);
392 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_level_id_tbl', p_doc_level_id_tbl);
393 END IF;
394
395 l_progress := '010';
396
397 -- Get the distribution ids for the given doc level ids.
398
399 PO_CORE_S.get_distribution_ids(
400 p_doc_type => p_doc_type
401 , p_doc_level => p_doc_level
402 , p_doc_level_id_tbl => p_doc_level_id_tbl
403 , x_distribution_id_tbl => l_dist_id_tbl
404 );
405
406 l_progress := '015';
407
408 -- Put the distribution ids in the scratchpad, so that the
409 -- cursors will work (PL/SQL locking limitations).
410
411 SELECT PO_SESSION_GT_S.nextval
412 INTO l_dist_id_key
413 FROM DUAL
414 ;
415
416 l_progress := '020';
417
418 FORALL i IN 1 .. l_dist_id_tbl.COUNT
419 INSERT INTO PO_SESSION_GT ( key, num1 )
420 VALUES ( l_dist_id_key, l_dist_id_tbl(i) )
421 ;
422
423 l_progress := '030';
424
425 -- Cursors lock the rows on OPEN, and we don't need to SELECT into anything.
426
427 IF (p_doc_type = PO_CORE_S.g_doc_type_REQUISITION) THEN
428
429 l_progress := '040';
430 IF g_debug_stmt THEN
431 PO_DEBUG.debug_stmt(l_log_head,l_progress,'requisition');
432 END IF;
433
434 OPEN l_lock_req_csr(p_dist_id_key => l_dist_id_key);
435 CLOSE l_lock_req_csr;
436
437 l_progress := '050';
438
439 ELSIF (p_doc_type IN ( PO_CORE_S.g_doc_type_PO
440 , PO_CORE_S.g_doc_type_PA
441 , PO_CORE_S.g_doc_type_RELEASE ))
442 THEN
443
444 l_progress := '060';
445 IF g_debug_stmt THEN
446 PO_DEBUG.debug_stmt(l_log_head,l_progress,'not requisition');
447 END IF;
448
449 OPEN l_lock_nonreq_csr(p_dist_id_key => l_dist_id_key);
450 CLOSE l_lock_nonreq_csr;
451
452 l_progress := '070';
453
454 ELSE
455
456 l_progress := '090';
457
458 RAISE PO_CORE_S.g_INVALID_CALL_EXC;
459
460 END IF;
461
462 l_progress := '900';
463
464 IF g_debug_stmt THEN
465 PO_DEBUG.debug_end(l_log_head);
466 END IF;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 IF g_debug_unexp THEN
471 PO_DEBUG.debug_exc(l_log_head,l_progress);
472 END IF;
473 RAISE;
474
475 END lock_distributions;
476
477 -------------------------------------------------------------------------------
478 --Start of Comments
479 --Name: lock_sourcing_rules
480 --Pre-reqs:
481 -- None.
482 --Modifies:
483 -- None.
484 --Locks:
485 -- MRP_SOURCING_RULES
486 --Function:
487 -- Locks the sourcing rules for a given sourcing_rule_id.
488 --Parameters:
489 --IN:
490 --p_sourcing_rule_id
491 --Sourcing rule id
492 --
493 --End of Comments
494 -------------------------------------------------------------------------------
495 PROCEDURE lock_sourcing_rules(
496 p_sourcing_rule_id IN NUMBER
497 )
498 IS
499
500 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'LOCK_SOURCING_RULES';
501 l_progress VARCHAR2(3) := '000';
502
503 CURSOR l_lock_sourcing_rule_csr(p_sourcing_rule_id NUMBER) IS
504 SELECT *
505 FROM MRP_SOURCING_RULES
506 FOR UPDATE OF sourcing_rule_id
507 NOWAIT
508 ;
509
510 BEGIN
511
512 IF g_debug_stmt THEN
513 PO_DEBUG.debug_begin(l_log_head);
514 PO_DEBUG.debug_var(l_log_head,l_progress,'c', p_sourcing_rule_id);
515 END IF;
516
517 l_progress := '010';
518
519
520 OPEN l_lock_sourcing_rule_csr(p_sourcing_rule_id => p_sourcing_rule_id);
521 CLOSE l_lock_sourcing_rule_csr;
522
523 l_progress := '020';
524
525
526 IF g_debug_stmt THEN
527 PO_DEBUG.debug_end(l_log_head);
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 IF g_debug_unexp THEN
533 PO_DEBUG.debug_exc(l_log_head,l_progress);
534 END IF;
535 RAISE;
536
537 END lock_sourcing_rules;
538
539
540 -----------------------------------------------------------------------------
541 -- Initialize package variables.
542 -----------------------------------------------------------------------------
543
544 BEGIN
545
546 g_debug_stmt := PO_DEBUG.is_debug_stmt_on;
547 g_debug_unexp := PO_DEBUG.is_debug_unexp_on;
548
549
550 END PO_LOCKS;