1 PACKAGE BODY PO_AUTOCREATE_UTIL_PVT AS
2 /* $Header: POXVACUB.pls 120.1.12020000.4 2013/05/20 11:18:22 akyanama ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_AUTOCREATE_UTIL_PVT';
5 G_MODULE_PREFIX CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7
8 /**************** PRIVATE PROCEDURES ****************/
9
10 -----------------------------------------------------------------------
11 --Start of Comments
12 --Name: add_to_builder_reqs_gt
13 --Pre-reqs: None
14 --Modifies: PO_SESSION_GT
15 --Locks:
16 -- None
17 --Function:
18 -- Insert rows to PO_SESSION_GT. This is done to track builder reqs on
19 -- the server side
20 --Parameters:
21 --IN:
22 --p_key
23 -- key in PO_SESSION_GT
24 --p_req_list
25 -- table containing requisition_line_id
26 --IN OUT:
27 --OUT:
28 --Returns:
29 --Notes:
30 --Testing:
31 --End of Comments
32 -----------------------------------------------------------------------
33 PROCEDURE add_to_builder_reqs_gt
34 ( p_key IN NUMBER,
35 p_req_list IN PO_TBL_NUMBER
36 ) IS
37
38 BEGIN
39
40 FORALL i IN 1..p_req_list.COUNT
41 INSERT INTO PO_SESSION_GT
42 ( key,
43 index_num1
44 )
45 VALUES
46 ( p_key,
47 p_req_list(i)
48 );
49 END add_to_builder_reqs_gt;
50
51
52
53 /**************** PUBLIC PROCEDURES ****************/
54
55 -----------------------------------------------------------------------
56 --Start of Comments
57 --Name: add_to_builder_reqs_gt
58 --Pre-reqs: None
59 --Modifies: PO_SESSION_GT
60 --Locks:
61 -- None
62 --Function:
63 -- Synchronize PO_SESSION_GT with builder reqs. This procedure
64 -- first cleans up the gt table and then insert into the table with the
65 -- rows passed in
66 --Parameters:
67 --IN:
68 --p_key
69 -- key in PO_SESSION_GT
70 --p_req_list
71 -- table containing requisition_line_id
72 --IN OUT:
73 --OUT:
74 --Returns:
75 --Notes:
76 --Testing:
77 --End of Comments
78 -----------------------------------------------------------------------
79 PROCEDURE synchronize_builder_reqs
80 ( p_key IN NUMBER,
81 p_req_list IN PO_TBL_NUMBER
82 ) IS
83
84 l_api_name CONSTANT VARCHAR2(30) := 'synchronize_builder_reqs';
85 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
86 G_MODULE_PREFIX || l_api_name || '.';
87 l_progress VARCHAR2(3);
88
89 BEGIN
90
91 l_progress := '000';
92
93 IF (g_debug_stmt) THEN
94 PO_DEBUG.debug_begin
95 ( p_log_head => l_module
96 );
97
98 PO_DEBUG.debug_var
99 ( p_log_head => l_module,
100 p_progress => l_progress,
101 p_name => 'p_key',
102 p_value => p_key
103 );
104
105 PO_DEBUG.debug_var
106 ( p_log_head => l_module,
107 p_progress => l_progress,
108 p_name => 'p_req_list.COUNT',
109 p_value => p_req_list.COUNT
110 );
111 END IF;
112
113 clear_builder_reqs_gt
114 ( p_key => p_key
115 );
116
117 add_to_builder_reqs_gt
118 ( p_key => p_key,
119 p_req_list => p_req_list
120 );
121
122 IF (g_debug_stmt) THEN
123 PO_DEBUG.debug_end
124 ( p_log_head => l_module
125 );
126 END IF;
127 END synchronize_builder_reqs;
128
129
130 -------------------------------------------------------------------------------
131 -------------------------------------------------------------------------------
132 --Start of Comments
133 --Name: add_req_lines_gt
134 --Pre-reqs:
135 -- None
136 --Modifies:
137 -- po_session_gt
138 --Locks:
139 -- None
140 --Function:
141 -- Adds the input table of numbers to the PO_SESSION_GT table and returns
142 -- the GT key for which the values were added.
143 --Parameters:
144 --IN:
145 --p_req_line_id_tbl
146 -- PO_TBL_NUMBER of Requisition Line IDs to add to the GT table
147 --Returns:
148 -- NUMBER representing the key value for which the input values were added
149 --Notes:
150 -- None
151 --Testing:
152 -- None
153 --End of Comments
154 -------------------------------------------------------------------------------
155 -------------------------------------------------------------------------------
156 FUNCTION add_req_lines_gt
157 (
158 p_req_line_id_tbl IN PO_TBL_NUMBER
159 )
160 RETURN NUMBER
161 IS
162 l_key NUMBER;
163
164 BEGIN
165
166 l_key := PO_CORE_S.get_session_gt_nextval;
167
168 FORALL i IN 1..p_req_line_id_tbl.COUNT
169
170 INSERT INTO po_session_gt
171 ( key
172 , index_num1
173 ) VALUES
174 ( l_key
175 , p_req_line_id_tbl(i)
176 );
177
178 return (l_key);
179
180 END add_req_lines_gt;
181
182 -----------------------------------------------------------------------
183 --Start of Comments
184 --Name: clear_builder_reqs_gt
185 --Pre-reqs: None
186 --Modifies: PO_SESSION_GT
187 --Locks:
188 -- None
189 --Function:
190 -- Remove all builder reqs from PO_SESSION_GT table
191 --Parameters:
192 --IN:
193 --p_key
194 -- key in PO_SESSION_GT
195 --IN OUT:
196 --OUT:
197 --Returns:
198 --Notes:
199 --Testing:
200 --End of Comments
201 -----------------------------------------------------------------------
202 PROCEDURE clear_builder_reqs_gt
203 (
204 p_key IN NUMBER
205 )
206 IS
207 BEGIN
208
209 DELETE FROM PO_SESSION_GT
210 WHERE key = p_key;
211
212 END clear_builder_reqs_gt;
213
214 -------------------------------------------------------------------------------
215 -------------------------------------------------------------------------------
216 --Start of Comments
217 --Name: get_and_lock_req_lines_in_pool
218 --Pre-reqs:
219 -- None
220 --Modifies:
221 -- None
222 --Locks:
223 -- po_requisition_lines_all
224 --Function:
225 -- Retrieves and locks all Requisition Lines that are specified in the input
226 -- nested table and are in the Req Pool.
227 --Parameters:
228 --IN:
229 --p_req_line_id_tbl
230 -- PO_TBL_NUMBER of Requisition Line IDs to add to the GT table
231 --p_lock_records
232 -- 'Y' or 'N' indicating whether to lock the records that are being retrieved
233 --OUT:
234 --x_req_line_id_in_pool_tbl
235 -- PO_TBL_NUMBER of Req Line IDs which exist in the input nested table
236 -- as well as the Req Pool
237 --x_records_locked
238 -- BOOLEAN indicating whether the records were successfully locked
239 --Notes:
240 -- None
241 --Testing:
242 -- None
243 --End of Comments
244 -------------------------------------------------------------------------------
245 -------------------------------------------------------------------------------
246 PROCEDURE get_and_lock_req_lines_in_pool
247 (
248 p_req_line_id_tbl IN PO_TBL_NUMBER
249 , p_lock_records IN VARCHAR2
250 , x_req_line_id_in_pool_tbl OUT NOCOPY PO_TBL_NUMBER
251 , x_records_locked OUT NOCOPY VARCHAR2
252 )
253 IS
254 l_key NUMBER;
255
256 BEGIN
257
258 x_records_locked := 'N';
259
260 -- Initialize GT Table ----------------------------------------------------
261
262 l_key := PO_AUTOCREATE_UTIL_PVT.add_req_lines_gt(p_req_line_id_tbl);
263
264
265 -- Query and Lock ---------------------------------------------------------
266
267 IF ( p_lock_records = 'Y' ) THEN
268
269 SELECT pool.requisition_line_id
270 BULK COLLECT INTO x_req_line_id_in_pool_tbl
271 FROM po_req_lines_in_pool_sec_v pool
272 WHERE pool.requisition_line_id IN ( SELECT selected.index_num1
273 FROM po_session_gt selected
274 WHERE selected.key = l_key
275 )
276 FOR UPDATE NOWAIT;
277
278 x_records_locked := 'Y';
279
280 -- Query Only -------------------------------------------------------------
281
282 ELSE
283
284 SELECT pool.requisition_line_id
285 BULK COLLECT INTO x_req_line_id_in_pool_tbl
286 FROM po_req_lines_in_pool_sec_v pool
287 WHERE pool.requisition_line_id IN ( SELECT selected.index_num1
288 FROM po_session_gt selected
289 WHERE selected.key = l_key
290 );
291
292 END IF;
293
294 -- Clean Up GT Table ------------------------------------------------------
295
296 -- Delete Requisition Lines which we previously added to the GT table.
297
298 clear_builder_reqs_gt(l_key);
299
300 EXCEPTION
301
302 WHEN OTHERS THEN
303
304 IF ( SQLCODE = -54 ) -- unable to lock records
305 THEN
306 x_records_locked := 'N';
307 ELSE
308 raise;
309 END IF;
310
314 -------------------------------------------------------------------------------
311 END get_and_lock_req_lines_in_pool;
312
313 -------------------------------------------------------------------------------
315 --Start of Comments
316 -- Project : Options Enhancement
317 --Name: crosslinked_option_exist
318 --Function:
319 -- check if the clin has any option which is crosslinked [i.e. having base line from some other clin slin structure]
320 --Parameters:
321 --IN:
322 --p_req_line_id line id of the clin
323 --End of Comments
324 -------------------------------------------------------------------------------
325 -------------------------------------------------------------------------------
326 FUNCTION crosslinked_option_exist
327 ( p_req_line_id IN NUMBER
328 ) RETURN VARCHAR2 IS
329
330 d_api_name CONSTANT VARCHAR2(30) := 'has_CrossLinked_Options';
331 d_module CONSTANT VARCHAR2(2000) := G_PKG_NAME || d_api_name || '.';
332 d_position NUMBER;
333
334 l_crosslinked_option_exist VARCHAR2(1) := FND_API.G_FALSE;
335
336
337 BEGIN
338 d_position := 0;
339 IF (PO_LOG.d_proc) THEN
340 PO_LOG.proc_begin(d_module);
341 END IF;
342
343 SELECT FND_API.G_TRUE
344 INTO l_crosslinked_option_exist
345 FROM dual
346 WHERE EXISTS(
347 SELECT 'Cross Linked Child Exists'
348 FROM po_requisition_lines_all base_line,
349 po_requisition_lines_all child_line
350 WHERE base_line.requisition_line_id = Nvl(child_line.clm_base_line_num,-1)
351 AND Nvl(base_line.group_line_id,-1) <> Nvl(child_line.group_line_id,-1)
352 AND child_line.group_line_id =p_req_line_id
353 );
354
355
356 IF (PO_LOG.d_proc) THEN
357 PO_LOG.proc_end(d_module, 'l_crosslinked_option_exist', l_crosslinked_option_exist);
358 END IF;
359
360 RETURN l_crosslinked_option_exist;
361
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 l_crosslinked_option_exist:= FND_API.G_FALSE;
365 IF (PO_LOG.d_proc) THEN
366 PO_LOG.proc_end(d_module, 'NO_DATA_FOUND: l_crosslinked_option_exist', l_crosslinked_option_exist);
367 END IF;
368 RETURN l_crosslinked_option_exist;
369
370 END crosslinked_option_exist;
371
372 -------------------------------------------------------------------------------
373 --Start of Comments
374 -- Project : PAR
375 --Name: is_par_option_line_editable
376 --Function:
377 -- check if the Option line is selectable for the Autocreate or not.
378 --Parameters:
379 --IN:
380 --p_req_line_id line id of the clin
381 --End of Comments
382 -------------------------------------------------------------------------------
383 -------------------------------------------------------------------------------
384 FUNCTION is_par_option_line_editable
385 ( p_req_line_id IN NUMBER
386 ) RETURN VARCHAR2 IS
387
388 d_api_name CONSTANT VARCHAR2(30) := 'par_option_line_editable';
389 d_module CONSTANT VARCHAR2(2000) := G_PKG_NAME || d_api_name || '.';
390 d_position NUMBER;
391
392 l_is_par_option_line_editable VARCHAR2(1) := FND_API.G_FALSE;
393
394
395 BEGIN
396 d_position := 0;
397 IF (PO_LOG.d_proc) THEN
398 PO_LOG.proc_begin(d_module);
399 END IF;
400
401 Select FND_API.G_TRUE
402 INTO l_is_par_option_line_editable
403 from dual
404 where EXISTS
405 ( select 1 from po_requisition_lines_all prla where requisition_line_id = p_req_line_id
406 and par_draft_id is not null
407 AND
408 ((clm_base_line_num IS NULL AND group_line_id IS NULL ) OR
409 ( clm_base_line_num IS NULL AND
410 EXISTS (select 1 from po_lines_draft_all plda where po_line_id = prla.clm_base_line_num and plda.draft_id = prla.par_draft_id
411 and plda.change_status = 'UPDATE'))));
412
413
414 IF (PO_LOG.d_proc) THEN
415 PO_LOG.proc_end(d_module, 'l_is_par_option_line_editable', l_is_par_option_line_editable);
416 END IF;
417
418 RETURN l_is_par_option_line_editable;
419
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 l_is_par_option_line_editable:= FND_API.G_FALSE;
423 IF (PO_LOG.d_proc) THEN
424 PO_LOG.proc_end(d_module, 'NO_DATA_FOUND: l_is_par_option_line_editable', l_is_par_option_line_editable);
425 END IF;
426 RETURN l_is_par_option_line_editable;
427
428 END is_par_option_line_editable;
429
430
431 -------------------------------------------------------------------------------
432 -------------------------------------------------------------------------------
433 --Start of Comments
434 --Project : Options Enhancement
435 --Name: is_crosslinked_child
436 --Function:
437 -- check if the current line belongs to a clin-slin structure whose any of the option lines has the base as the input
438 -- parent clin line.
439 --Parameters:
440 --IN:
441 --p_req_line_id line id of the clin
442 --c_req_line_id requisition line for the requisition to which this clin p_req_line_id belongs to
443 --End of Comments
444 -------------------------------------------------------------------------------
445 -------------------------------------------------------------------------------
446 FUNCTION is_crosslinked_child
447 ( c_req_line_id IN NUMBER,
448 p_req_line_id IN NUMBER
449
450 ) RETURN VARCHAR2 IS
451
452 d_api_name CONSTANT VARCHAR2(30) := 'is_crosslinked_child';
456
453 d_module CONSTANT VARCHAR2(2000) := G_PKG_NAME || d_api_name || '.';
454
455 l_crosslinked_child VARCHAR2(1) := FND_API.G_FALSE;
457 /* Editted these two sequels under the bug 16798807
458 During selection of dependent lines in Demand work bench.
459 For PAR requisitions, Lines with Clm_base_line_num or group_line_id
460 as 0 are not to be deemed as dependent, as this signifies that
461 it is a group line in the award and its parent is not available in
462 dwb. this value can be around multiple clin - slin structures
463 so they should not be considered for grouping.
464 */
465
466 CURSOR C_lines(c_req_line_id po_requisition_lines_all.requisition_line_id%TYPE) IS
467 SELECT Nvl(( CASE
468 WHEN ( (clm_base_line_num = 0) AND par_line_id IS NOT NULL )THEN NULL
469 ELSE clm_base_line_num
470 END ), -1) clm_base_line_num
471 FROM po_requisition_lines_all
472 WHERE requisition_line_id IN (c_req_line_id,(SELECT group_line_id
473 FROM po_requisition_lines_all
474 WHERE requisition_line_id = c_req_line_id))
475 OR group_line_id IN (c_req_line_id,(SELECT group_line_id
476 FROM po_requisition_lines_all
477 WHERE requisition_line_id = c_req_line_id
478 AND ( par_line_id IS NULL OR
479 (par_line_id IS NOT NULL AND group_line_id <> 0) )));
480
481
482 CURSOR P_lines(c_req_line_id po_requisition_lines_all.requisition_line_id%TYPE) IS
483 SELECT requisition_line_id
484 FROM po_requisition_lines_all
485 WHERE requisition_line_id IN (c_req_line_id,(SELECT group_line_id
486 FROM po_requisition_lines_all
487 WHERE requisition_line_id = c_req_line_id))
488 OR group_line_id IN (c_req_line_id,(SELECT group_line_id
489 FROM po_requisition_lines_all
490 WHERE requisition_line_id = c_req_line_id
491 AND ( par_line_id IS NULL OR
492 (par_line_id IS NOT NULL AND group_line_id <> 0) )));
493
494
495
496
497 BEGIN
498
499 IF(to_NUMBER(c_req_line_id) = To_Number(p_req_line_id)) THEN
500 RETURN l_crosslinked_child;
501 END IF;
502
503 IF (PO_LOG.d_proc) THEN
504 PO_LOG.proc_begin(d_module);
505 END IF;
506
507 FOR cur_req_lines IN C_lines(c_req_line_id) LOOP
508 EXIT WHEN C_lines%NOTFOUND;
509
510 IF C_lines%ISOPEN THEN
511 FOR base_req_lines IN P_lines(p_req_line_id) LOOP
512 EXIT WHEN P_lines%NOTFOUND;
513
514 IF cur_req_lines.clm_base_line_num = base_req_lines.requisition_line_id THEN
515 l_crosslinked_child := FND_API.G_TRUE;
516 EXIT;
517 END IF;
518
519 END LOOP;
520 END IF;
521
522 END LOOP;
523
524
525 IF (PO_LOG.d_proc) THEN
526 PO_LOG.proc_end(d_module, 'l_crosslinked_child', l_crosslinked_child);
527 END IF;
528
529 RETURN l_crosslinked_child;
530
531 EXCEPTION
532 WHEN NO_DATA_FOUND THEN
533 l_crosslinked_child:= FND_API.G_FALSE;
534
535 IF (PO_LOG.d_proc) THEN
536 PO_LOG.proc_end(d_module, 'NO_DATA_FOUND: l_crosslinked_child', l_crosslinked_child);
537 END IF;
538
539 RETURN l_crosslinked_child;
540
541 END is_crosslinked_child;
542
543 END PO_AUTOCREATE_UTIL_PVT;
[Home] [Help]