[Home] [Help]
PACKAGE BODY: APPS.PO_LINE_TYPES_SV
Source
4
1 PACKAGE BODY PO_LINE_TYPES_SV as
2 /* $Header: POXSTLTB.pls 120.1.12010000.3 2009/01/28 10:37:36 mugoel ship $ */
3 /*========================== PO_LINE_TYPES_SV ===========================*/
5 /*===========================================================================
6
7 FUNCTION NAME: val_line_type()
8
9 ===========================================================================*/
10 FUNCTION val_line_type(X_line_type_id IN NUMBER) return BOOLEAN IS
11
12 X_progress varchar2(3) := NULL;
13 X_line_type_id_v number := NULL;
14
15 BEGIN
16
17 X_progress := '010';
18
19 /* Check if the given Line Type is active */
20
21 SELECT line_type_id
22 INTO X_line_type_id_v
23 FROM po_line_types
24 WHERE sysdate < nvl(inactive_date, sysdate + 1)
25 AND line_type_id = X_line_type_id;
26
27 return (TRUE);
28
29 EXCEPTION
30
31 when no_data_found then
35 raise;
32 return (FALSE);
33 when others then
34 po_message_s.sql_error('val_line_type',X_progress,sqlcode);
36
37 END val_line_type;
38
39 /*===========================================================================
40
41 PROCEDURE NAME: test_get_line_type_def()
42
43 ===========================================================================*/
44
45 PROCEDURE test_get_line_type_def
46 (X_Line_Type_Id IN NUMBER) IS
47
48 X_Order_Type_Lookup_Code VARCHAR2(25) := '';
49 X_Category_Id NUMBER := '';
50 X_Unit_Meas_Lookup_Code VARCHAR2(25) := '';
51 X_Unit_Price NUMBER := '';
52 X_Outside_Operations_Flag VARCHAR2(1) := '';
53 X_Receiving_Flag VARCHAR2(1) := '';
54 X_Receive_close_tolerance NUMBER := '';
55
56 BEGIN
57
58 -- dbms_output.put_line('before call');
59 -- Bug: 1189629 Added receive close tolerance to the list of parameters
60 po_line_types_sv.get_line_type_def(X_Line_Type_Id,
61 X_Order_Type_Lookup_Code,
62 X_Category_Id,
63 X_Unit_Meas_Lookup_Code,
64 X_Unit_Price,
65 X_Outside_Operations_Flag,
66 X_Receiving_Flag,
67 X_Receive_close_tolerance);
68
69 -- dbms_output.put_line('Order Type value is = '||X_Order_Type_Lookup_Code);
70 -- dbms_output.put_line('Category Id value is = '||X_Category_Id);
71 -- dbms_output.put_line('Unit Measure value is = '||X_Unit_Meas_Lookup_Code);
72 -- dbms_output.put_line('Unit Price is = '||X_Unit_Price);
73 -- dbms_output.put_line('Outside Op value is = '||X_Outside_Operations_Flag);
74 -- dbms_output.put_line('Receiving Flag value is = '||X_Receiving_Flag);
75
76 END test_get_line_type_def;
77
78
79 -----------------------------------------------------------------<SERVICES FPJ>
80 -------------------------------------------------------------------------------
81 --Start of Comments
82 --Name: get_line_type_def
83 --Pre-reqs:
84 -- None.
85 --Modifies:
86 -- None.
87 --Locks:
88 -- None.
89 --Function: Retrieves all attributes associated with a given line type.
90 --
91 --Parameters:
92 --IN:
93 --p_line_type_id - Unique ID of Line Type
94 --
95 --OUT:
96 --x_order_type_lookup_code
97 --x_purchase_basis
98 --x_matching_basis
99 --x_category_id
100 --x_unit_meas_lookup_code
101 --x_unit_price
102 --x_outside_operations_flag
103 --x_receiving_flag
104 --x_receive_close_tolerance
105 --
106 --Notes:
107 -- None.
108 --Testing:
109 -- None.
110 --End of Comments
111 -------------------------------------------------------------------------------
112 -------------------------------------------------------------------------------
113 PROCEDURE get_line_type_def
114 ( p_line_type_id IN NUMBER
115 , x_order_type_lookup_code OUT NOCOPY VARCHAR2
116 , x_purchase_basis OUT NOCOPY VARCHAR2
117 , x_matching_basis OUT NOCOPY VARCHAR2
118 , x_category_id OUT NOCOPY NUMBER
119 , x_unit_meas_lookup_code OUT NOCOPY VARCHAR2
120 , x_unit_price OUT NOCOPY NUMBER
121 , x_outside_operations_flag OUT NOCOPY VARCHAR2
122 , x_receiving_flag OUT NOCOPY VARCHAR2
123 , x_receive_close_tolerance OUT NOCOPY NUMBER
124 )
125 IS
126
127 x_progress VARCHAR2(3) := '';
128 invalid_id EXCEPTION;
129
130 -- Bug: 1189629 selected receive close tolerance also in the cursor
131 CURSOR C is
132 SELECT lt.order_type_lookup_code,
133 lt.purchase_basis, -- <SERVICES FPJ>
134 lt.matching_basis, -- <SERVICES FPJ>
135 lt.category_id,
136 lt.unit_of_measure,
137 lt.unit_price,
138 nvl(lt.outside_operation_flag,'N'),
139 lt.receiving_flag,
140 lt.receive_close_tolerance
141 FROM po_line_types_b lt -- <SERVICES FPJ>
142 WHERE lt.line_type_id = p_line_type_id; -- <SERVICES FPJ>
143
144 BEGIN
145
146 -- dbms_output.put_line('Before open cursor');
147
148 IF (p_line_type_id IS NOT NULL) THEN -- <SERVICES FPJ>
149
150 x_progress := '010';
151 OPEN C;
152 x_progress := '020';
153
154 FETCH C into X_Order_Type_Lookup_Code,
155 x_purchase_basis, -- <SERVICES FPJ>
156 x_matching_basis, -- <SERVICES FPJ>
157 X_Category_Id,
158 X_Unit_Meas_Lookup_Code,
159 X_Unit_Price,
160 X_Outside_Operations_Flag,
161 X_Receiving_Flag,
162 X_Receive_close_tolerance;
163 CLOSE C;
164
165 -- dbms_output.put_line('Order Type value is = '||X_Order_Type_Lookup_Code);
166 -- dbms_output.put_line('Category Id value is = '||X_Category_Id);
167 -- dbms_output.put_line('Unit Measure value is = '||X_Unit_Meas_Lookup_Code);
168 -- dbms_output.put_line('Unit Price is = '||X_Unit_Price);
169 -- dbms_output.put_line('Outside Op value is = '||X_Outside_Operations_Flag);
170 -- dbms_output.put_line('Receiving Flag value is = '||X_Receiving_Flag);
171
172 else
173 x_progress := '030';
174 raise invalid_id;
175
176 end if;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 -- dbms_output.put_line('In exception');
184 END get_line_type_def;
181 po_message_s.sql_error('get_line_type_def', x_progress, sqlcode);
182 raise;
183
185
186
187 -----------------------------------------------------------------<SERVICES FPJ>
188 -------------------------------------------------------------------------------
189 --Start of Comments
190 --Name: get_line_type_def
191 --Pre-reqs:
192 -- None.
193 --Modifies:
194 -- None.
195 --Locks:
196 -- None.
197 --Function: Retrieves all attributes associated with a given line type.
198 --
199 --Parameters:
200 --IN:
201 --p_line_type_id - Unique ID of Line Type
202 --
203 --OUT:
204 --x_order_type_lookup_code
205 --x_purchase_basis
206 --x_category_id
207 --x_unit_meas_lookup_code
208 --x_unit_price
209 --x_outside_operations_flag
210 --x_receiving_flag
211 --x_receive_close_tolerance
212 --
213 --Notes:
214 -- None.
215 --Testing:
216 -- None.
217 --End of Comments
218 -------------------------------------------------------------------------------
219 -------------------------------------------------------------------------------
220 PROCEDURE get_line_type_def
221 ( p_line_type_id IN NUMBER
222 , x_order_type_lookup_code OUT NOCOPY VARCHAR2
223 , x_purchase_basis OUT NOCOPY VARCHAR2
224 , x_category_id OUT NOCOPY NUMBER
225 , x_unit_meas_lookup_code OUT NOCOPY VARCHAR2
226 , x_unit_price OUT NOCOPY NUMBER
227 , x_outside_operations_flag OUT NOCOPY VARCHAR2
228 , x_receiving_flag OUT NOCOPY VARCHAR2
229 , x_receive_close_tolerance OUT NOCOPY NUMBER
230 )
231 IS
232 l_matching_basis PO_LINE_TYPES_B.matching_basis%TYPE;
233
234 BEGIN
235
236 PO_LINE_TYPES_SV.get_line_type_def
237 ( p_line_type_id => p_line_type_id
238 , x_order_type_lookup_code => x_order_type_lookup_code
239 , x_purchase_basis => x_purchase_basis
240 , x_matching_basis => l_matching_basis
241 , x_category_id => x_category_id
242 , x_unit_meas_lookup_code => x_unit_meas_lookup_code
243 , x_unit_price => x_unit_price
244 , x_outside_operations_flag => x_outside_operations_flag
245 , x_receiving_flag => x_receiving_flag
246 , x_receive_close_tolerance => x_receive_close_tolerance
247 );
248
249 END;
250
251 -----------------------------------------------------------------<SERVICES FPJ>
252 -------------------------------------------------------------------------------
253 --Start of Comments
254 --Name: get_line_type_def
255 --Pre-reqs:
256 -- None.
257 --Modifies:
258 -- None.
259 --Locks:
260 -- None.
261 --Function: Retrieves attributes associated with a given line type.
262 --
263 --Parameters:
264 --IN:
265 --p_line_type_id - Unique ID of Line Type
266 --
267 --OUT:
268 --x_order_type_lookup_code
269 --x_purchase_basis
270 --x_matching_basis
271 --x_outside_operations_flag
272 --
273 --Notes:
274 -- None.
275 --Testing:
276 -- None.
277 --End of Comments
278 -------------------------------------------------------------------------------
279 -------------------------------------------------------------------------------
280 PROCEDURE get_line_type_def
281 ( p_line_type_id IN NUMBER
285 , x_outside_operation_flag OUT NOCOPY VARCHAR2
282 , x_order_type_lookup_code OUT NOCOPY VARCHAR2
283 , x_purchase_basis OUT NOCOPY VARCHAR2
284 , x_matching_basis OUT NOCOPY VARCHAR2
286 )
287 IS
288 l_category_id PO_LINE_TYPES_B.category_id%TYPE;
289 l_unit_meas_lookup_code PO_LINE_TYPES_B.unit_of_measure%TYPE;
290 l_unit_price PO_LINE_TYPES_B.unit_price%TYPE;
291 l_receiving_flag PO_LINE_TYPES_B.receiving_flag%TYPE;
292 l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
293
294 BEGIN
295
296 PO_LINE_TYPES_SV.get_line_type_def
297 ( p_line_type_id => p_line_type_id
298 , x_order_type_lookup_code => x_order_type_lookup_code
299 , x_purchase_basis => x_purchase_basis
300 , x_matching_basis => x_matching_basis
301 , x_category_id => l_category_id
302 , x_unit_meas_lookup_code => l_unit_meas_lookup_code
303 , x_unit_price => l_unit_price
304 , x_outside_operations_flag => x_outside_operation_flag
305 , x_receiving_flag => l_receiving_flag
306 , x_receive_close_tolerance => l_receive_close_tolerance
307 );
308
309 END get_line_type_def;
310
311
312 /*===========================================================================
313
314 PROCEDURE NAME: get_line_type_def()
315
316 ===========================================================================*/
317
318 PROCEDURE get_line_type_def
319 (X_Line_Type_Id IN NUMBER,
320 X_Order_Type_Lookup_Code IN OUT NOCOPY VARCHAR2,
321 X_Category_Id IN OUT NOCOPY NUMBER,
322 X_Unit_Meas_Lookup_Code IN OUT NOCOPY VARCHAR2,
323 X_Unit_Price IN OUT NOCOPY NUMBER,
324 X_Outside_Operations_Flag IN OUT NOCOPY VARCHAR2,
325 X_Receiving_Flag IN OUT NOCOPY VARCHAR2,
326 X_Receive_close_tolerance IN OUT NOCOPY NUMBER)
327 IS
328 l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; -- <SERVICES FPJ>
329
330 BEGIN
331
332 -- <SERVICES FPJ START> Call to overloaded 'get_line_type_def' procedure.
333 --
334 PO_LINE_TYPES_SV.get_line_type_def
335 ( p_line_type_id => X_Line_Type_Id
336 , x_order_type_lookup_code => X_Order_Type_Lookup_Code
337 , x_purchase_basis => l_purchase_basis
338 , x_category_id => X_Category_Id
339 , x_unit_meas_lookup_code => X_Unit_Meas_Lookup_Code
340 , x_unit_price => X_Unit_Price
341 , x_outside_operations_flag => X_Outside_Operations_Flag
342 , x_receiving_flag => X_Receiving_Flag
343 , x_receive_close_tolerance => X_Receive_close_tolerance
344 );
345 -- <SERVICES FPJ END>
346
347 END get_line_type_def;
348
349
350
351 /*===========================================================================
352
353 FUNCTION NAME: get_line_type
354
355 ===========================================================================*/
356
357 FUNCTION get_line_type (x_line_type_id NUMBER)
358 RETURN VARCHAR2 is
359 x_progress VARCHAR2(3) := NULL;
360 x_line_type VARCHAR2(25);
361 begin
362 x_progress := 10;
363
364 SELECT polt.line_type
365 INTO x_line_type
366 FROM po_line_types polt
367 WHERE polt.line_type_id = x_line_type_id
368 AND nvl(polt.inactive_date,sysdate + 1) > sysdate;
369
370 return(x_line_type);
371
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 return('');
375
376 WHEN OTHERS THEN
377 po_message_s.sql_error('get_line_type', x_progress, sqlcode);
378 raise;
379
380 end get_line_type;
381
382
383 /*===========================================================================
384
385 FUNCTION NAME: outside_processing_items_exist
386
387 DESCRIPTION: Global Agreements (FP-I): Takes a po_header_id and
388 returns TRUE if that header contains any lines with
389 Outside Processing items. FALSE otherwise.
390
391 ===========================================================================*/
392 FUNCTION outside_processing_items_exist
393 (
394 p_po_header_id NUMBER
395 )
396 RETURN BOOLEAN
397 IS
398 CURSOR l_item_csr IS
399 SELECT item_id, org_id
400 FROM po_lines_all
401 WHERE po_header_id = p_po_header_id;
402
403 l_item_id PO_LINES_ALL.item_id%TYPE;
404 l_org_id PO_LINES_ALL.org_id%TYPE;
405
406 BEGIN
407
408 OPEN l_item_csr;
409 LOOP --------------------------------------------------
410
411 FETCH l_item_csr INTO l_item_id, l_org_id;
412
413 EXIT WHEN l_item_csr%NOTFOUND;
414
415 IF ( is_outside_processing_item( l_item_id, l_org_id ) ) THEN
416 return (TRUE);
417 END IF;
418
419 END LOOP; ---------------------------------------------
420 CLOSE l_item_csr;
421
422 return (false); -- no outside processing items were found
423
424 EXCEPTION
425
426 WHEN OTHERS THEN
427 PO_MESSAGE_S.sql_error('outside_processing_items_exist','000',sqlcode);
428 RAISE;
429
430 END outside_processing_items_exist;
431
432
433 /*===========================================================================
434
435 FUNCTION NAME: is_outside_processing_item
436
437 DESCRIPTION: Global Agreements (FP-I): Takes an item_id and
438 returns TRUE if it is an Outside Processing item.
439 FALSE otherwise.
440
441 ===========================================================================*/
442 FUNCTION is_outside_processing_item
443 (
444 p_item_id NUMBER
445 , p_org_id NUMBER
446 )
447 RETURN BOOLEAN
448 IS
449 l_outside_operation_flag MTL_SYSTEM_ITEMS.outside_operation_flag%TYPE;
450
451 BEGIN
452
453 SELECT outside_operation_flag
454 INTO l_outside_operation_flag
455 FROM mtl_system_items
456 WHERE inventory_item_id = p_item_id
457 AND organization_id = p_org_id;
458
459 IF ( l_outside_operation_flag = 'Y' ) THEN
460 return (TRUE);
461 ELSE
462 return (FALSE);
463 END IF;
464
465 EXCEPTION
466
467 WHEN OTHERS THEN
468 return (FALSE);
469 END;
470
471
472 /*===========================================================================
473
474 FUNCTION: is_outside_processing
475
476 DESCRIPTION: Takes a line_type_id and returns TRUE if that line
477 type is Outside Processing, FALSE otherwise.
478
479 ===========================================================================*/
480
481 FUNCTION is_outside_processing
482 (
483 p_line_type_id NUMBER
484 )
485 RETURN BOOLEAN
486 IS
487 x_outside_operation_flag VARCHAR2(1) := 'N';
488
489 BEGIN
490
491 SELECT outside_operation_flag
492 INTO x_outside_operation_flag
493 FROM po_line_types
494 WHERE line_type_id = p_line_type_id;
495
496 IF ( x_outside_operation_flag = 'Y' )
497 THEN
498 return (TRUE);
499 ELSE
500 return (FALSE);
501 END IF;
502
503 EXCEPTION
504
505 WHEN OTHERS THEN
506 PO_MESSAGE_S.sql_error('outside_operation_flag','000',sqlcode);
507 RAISE;
508
509 END is_outside_processing;
510
511
512 -----------------------------------------------------------------<SERVICES FPJ>
513 -------------------------------------------------------------------------------
514 --Start of Comments
515 --Name: transactions_exist
516 --Pre-reqs:
517 -- None.
518 --Modifies:
519 -- None.
520 --Locks:
521 -- None.
522 --Function:
523 -- Determine if any transactions exist with the given Line Type.
524 --Parameters:
525 --IN:
526 --p_line_type_id
527 -- Unique ID of Line Type
528 --Returns:
529 -- TRUE if any transactions (in any status) exist with the given Line Type.
530 -- FALSE otherwise.
531 --Notes:
532 -- None.
533 --Testing:
534 -- None.
535 --End of Comments
536 -------------------------------------------------------------------------------
537 -------------------------------------------------------------------------------
538 FUNCTION transactions_exist
539 (
540 p_line_type_id NUMBER
541 )
542 RETURN VARCHAR2
543 IS
544 l_sourcing_negs_exist_flag VARCHAR2(1);
545 l_error_code VARCHAR2(100);
546 l_error_message VARCHAR2(250);
547
548 l_transactions_exist VARCHAR2(30);
549
550 BEGIN
551
552 -- <Bug 8203958>
553 -- Handle NO_DATA_FOUND, and set l_transactions_exist to NULL.
554 BEGIN
555 --Bug# 7395515 START
556 --Remove all the cursor's with exists clauses.
557 --SQL ID : 28308519, 28308573, 28308549
558 SELECT 'Lines Exist'
559 into l_transactions_exist
560 FROM dual
561 where EXISTS (SELECT 'Req Lines Exist'
562 From po_requisition_lines_all PRL
563 WHERE PRL.line_type_id = p_line_type_id)
564
565 OR EXISTS (SELECT 'PO Lines Exist'
566 FROM po_lines_all POL
567 WHERE POL.line_type_id = p_line_type_id)
568
569 OR EXISTS (SELECT 'Archived PO Lines Exist'
570 FROM po_lines_archive_all POAL
571 WHERE POAL.line_type_id = p_line_type_id ) ;
572
573 EXCEPTION
574 WHEN NO_DATA_FOUND THEN
575 l_transactions_exist := NULL;
576 END;
577
578 If (l_transactions_exist is not null) then
579 return 'Y';
580 END IF;
581
582
583 PON_SOURCING_API_GRP.val_neg_exists_for_line_type
584 ( p_line_type_id => p_line_type_id
585 , x_result => l_sourcing_negs_exist_flag
586 , x_error_code => l_error_code
587 , x_error_message => l_error_message
588 );
589 IF l_sourcing_negs_exist_flag = 'Y' THEN
590 return 'Y';
591 ELSE
592 return 'N';
593 END IF;
594
595 END transactions_exist;
596
597
598 END po_line_types_sv;