[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_SV4
Source
1 PACKAGE BODY PO_HEADERS_SV4 as
2 /* $Header: POXPOH4B.pls 120.1 2008/02/25 09:22:51 adevadul ship $*/
3
4 /*=============================================================================
5
6 FUNCTION: is_quotation <GA FPI>
7
8 DESCRIPTION: Returns TRUE if the po_header_id is of type_lookup_code
9 'QUOTATION'. FALSE, otherwise.
10
11 =============================================================================*/
12 FUNCTION is_quotation
13 (
14 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
15 )
16 RETURN BOOLEAN
17 IS
18 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
19
20 BEGIN
21
22 SELECT type_lookup_code
23 INTO l_type_lookup_code
24 FROM po_headers_all
25 WHERE po_header_id = p_po_header_id;
26
27 IF ( l_type_lookup_code = 'QUOTATION' ) THEN
28 return (TRUE);
29 ELSE
30 return (FALSE);
31 END IF;
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 return (FALSE);
36
37 END is_quotation;
38
39
40 /*===========================================================================
41
45
42 PROCEDURE NAME: get_doc_type_lookup_code()
43
44 ===========================================================================*/
46 PROCEDURE get_doc_type_lookup_code (X_doc_type_code IN VARCHAR2,
47 X_doc_subtype IN VARCHAR2,
48 X_def_doc_type_name IN OUT NOCOPY VARCHAR2,
49 X_def_type_lookup_code IN OUT NOCOPY VARCHAR2) IS
50
51 x_progress VARCHAR2(3) := NULL;
52
53 BEGIN
54
55 x_progress := '010';
56
57 SELECT type_name, document_subtype
58 INTO X_def_doc_type_name, X_def_type_lookup_code
59 FROM po_document_types
60 WHERE document_type_code = X_doc_type_code and
61 document_subtype = X_doc_subtype;
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 x_def_doc_type_name := '';
66 x_def_type_lookup_code := '';
67 WHEN OTHERS THEN
68 po_message_s.sql_error('get_doc_type_lookup_code', x_progress, sqlcode);
69 RAISE;
70
71 END get_doc_type_lookup_code;
72
73
74 /*===========================================================================
75
76 PROCEDURE NAME: get_lookup_code_dsp()
77
78 ===========================================================================*/
79
80
81 procedure get_lookup_code_dsp (X_lookup_type IN VARCHAR2,
82 X_lookup_code IN VARCHAR2,
83 X_lookup_code_dsp IN OUT NOCOPY VARCHAR2) is
84
85 X_progress varchar2(3) := '';
86
87 begin
88 X_progress := '010';
89
90 select polc.displayed_field
91 into X_lookup_code_dsp
92 from po_lookup_codes polc
93 where polc.lookup_type = X_lookup_type
94 and polc.lookup_code = X_lookup_code;
95
96 exception
97 WHEN NO_DATA_FOUND THEN
98 X_lookup_code_dsp := '';
99 WHEN OTHERS THEN
100 po_message_s.sql_error('get_lookup_code_dsp', X_progress, sqlcode);
101 raise;
102
103 end get_lookup_code_dsp;
104
105
106 /*=============================================================================
107
108 PROCEDURE: get_type_name <GA FPI>
109
110 DESCRIPTION: Gets the displayed type name for a particular po_header_id.
111
112 =============================================================================*/
113 FUNCTION get_type_name
114 (
115 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
116 )
117 RETURN PO_DOCUMENT_TYPES_ALL.type_name%TYPE
118 IS
119 x_type_name PO_DOCUMENT_TYPES_ALL.type_name%TYPE;
120
121 l_org_id PO_HEADERS_ALL.org_id%TYPE;
122 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
123 l_quote_type_lookup_code PO_HEADERS_ALL.quote_type_lookup_code%TYPE;
124
125 l_document_type_code PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
126 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
127
128 BEGIN
129
130 SELECT type_lookup_code,
131 quote_type_lookup_code,
132 org_id
133 INTO l_type_lookup_code,
134 l_quote_type_lookup_code,
135 l_org_id
136 FROM po_headers_all
137 WHERE po_header_id = p_po_header_id;
138
139 -- based on PO_HEADERS_ALL.type_lookup_code & .quote_type_lookup_code,
140 -- sets PO_DOCUMENT_TYPES_ALL.document_type_code & .document_subtype
141 --
142 IF ( l_type_lookup_code = 'BLANKET' ) THEN -- Blanket Agreement
143 l_document_type_code := 'PA';
144 l_document_subtype := l_type_lookup_code;
145 ELSIF ( l_type_lookup_code = 'QUOTATION' ) THEN -- Quotation
146 l_document_type_code := l_type_lookup_code;
147 l_document_subtype := l_quote_type_lookup_code;
148 ELSIF ( l_type_lookup_code = 'PLANNED' ) THEN -- Planned PO
149 l_document_type_code := 'PO';
150 l_document_subtype := l_type_lookup_code;
151 ELSIF ( l_type_lookup_code = 'CONTRACT' ) THEN -- Contract Agreement
152 l_document_type_code := 'PA';
153 l_document_subtype := l_type_lookup_code;
154 ELSIF ( l_type_lookup_code = 'RFQ' ) THEN -- RFQ
155 l_document_type_code := l_type_lookup_code;
156 l_document_subtype := l_quote_type_lookup_code;
157 ELSIF ( l_type_lookup_code = 'STANDARD' ) THEN -- Standard PO
158 l_document_type_code := 'PO';
159 l_document_subtype := l_type_lookup_code;
160 ELSE
161 return (NULL);
162 END IF;
163
164 SELECT type_name
165 INTO x_type_name
166 FROM po_document_types_all
167 WHERE document_type_code = l_document_type_code
168 AND document_subtype = l_document_subtype
169 AND org_id = l_org_id;
170
171 return (x_type_name);
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 return (NULL);
176
177 END get_type_name;
178
179
180 /*=============================================================================
181
182 FUNCTION: get_doc_num <GA FPI>
183
184 DESCRIPTION: Gets document number (segment1) for a po_header_id.
185
186 =============================================================================*/
187 FUNCTION get_doc_num
188 (
189 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
190 )
194
191 RETURN PO_HEADERS_ALL.segment1%TYPE
192 IS
193 x_segment1 PO_HEADERS_ALL.segment1%TYPE;
195 BEGIN
196
197 SELECT segment1
198 INTO x_segment1
199 FROM po_headers_all
200 WHERE po_header_id = p_po_header_id;
201
202 return (x_segment1);
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 return (NULL);
207
208 END get_doc_num;
209
210
211 /*=============================================================================
212
213 FUNCTION: get_vendor_quote_num <GA FPI>
214
215 DESCRIPTION: Gets quote_vendor_quote_num for a particular po_header_id.
216
217 =============================================================================*/
218 FUNCTION get_vendor_quote_num
219 (
220 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
221 )
222 RETURN PO_HEADERS_ALL.quote_vendor_quote_number%TYPE
223 IS
224 x_vendor_quote_num PO_HEADERS_ALL.quote_vendor_quote_number%TYPE;
225
226 BEGIN
227
228 SELECT quote_vendor_quote_number
229 INTO x_vendor_quote_num
230 FROM po_headers_all
231 WHERE po_header_id = p_po_header_id;
232
233 return (x_vendor_quote_num);
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 return (NULL);
238
239 END get_vendor_quote_num;
240
241
242 /*===========================================================================
243
244 FUNCTION NAME: val_vendor_has_contracts()
245
246 ===========================================================================*/
247
248 function val_vendor_has_contracts(X_vendor_id IN number)
249 return varchar2 is
250 X_vendor_has_contracts varchar2(5) := 'FALSE';
251 X_contract_count number := 0;
252 X_Progress varchar2(3) := '';
253
254 begin
255 X_progress := '010';
256
257 -- bug 411933
258 -- Replace Select count by Where Exists to improve performance
259
260 select 1
261 into X_contract_count
262 from sys.dual
263 where exists
264 (SELECT 'Vendor has contract'
265 from po_headers ph
266 where ph.vendor_id = X_vendor_id
267 and ph.type_lookup_code = 'CONTRACT'
268 and nvl(ph.cancel_flag,'N') = 'N'
269 and sysdate between nvl(ph.start_date, sysdate - 1)
270 and nvl(ph.end_date, sysdate + 1));
271
272 if X_contract_count > 0 then
273 X_vendor_has_contracts := 'TRUE';
274 else
275 X_vendor_has_contracts := 'FALSE';
276 end if;
277 return(X_vendor_has_contracts);
278
279 exception
280 when no_data_found then
281 X_vendor_has_contracts := 'FALSE';
282 return(X_vendor_has_contracts);
283 when others then
284 X_vendor_has_contracts := 'FALSE';
285 return(X_vendor_has_contracts);
286 end val_vendor_has_contracts;
287
288 /*===========================================================================
289
290 PROCEDURE NAME: get_preparer_approve_flag()
291
292 ===========================================================================*/
293
294
295 procedure get_preparer_approve_flag
296 (X_document_type IN VARCHAR2,
297 X_document_subtype IN VARCHAR2,
298 X_can_preparer_approve_flag IN OUT NOCOPY VARCHAR2) is
299
300 X_progress varchar2(3) := '';
301
302 begin
303 X_progress := '010';
304
305 SELECT nvl(can_preparer_approve_flag,'N')
306 INTO X_can_preparer_approve_flag
307 FROM po_document_types
308 WHERE document_type_code = X_document_type
309 AND document_subtype = X_document_subtype;
310
311 exception
312 WHEN NO_DATA_FOUND THEN
313 X_can_preparer_approve_flag := '';
314 WHEN OTHERS THEN
315 po_message_s.sql_error(' get_preparer_approve_flag', X_progress, sqlcode);
316 raise;
317
318 end get_preparer_approve_flag;
319
320
321 --=============================================================================
322 -- Function : cumulative_lines_exist -- <2706225>
323 -- Type : Private
324 --
325 -- Pre-reqs : -
326 -- Modifies : -
327 -- Description : Determines if the document has any lines which have the
328 -- 'Cumulative Pricing' set.
329 --
330 -- Parameters : p_po_header_id - document ID
331 --
332 -- Returns : TRUE if the document has 'Cumulative Pricing' lines.
333 -- FALSE otherwise.
334 -- Exceptions : -
335 --=============================================================================
336 FUNCTION cumulative_lines_exist
337 (
338 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
339 )
340 RETURN BOOLEAN
341 IS
342 l_price_break_lookup_code PO_LINES_ALL.price_break_lookup_code%TYPE;
343
344 CURSOR l_cumulative_csr IS SELECT price_break_lookup_code
345 FROM po_lines_all
346 WHERE po_header_id = p_po_header_id;
347 BEGIN
348
349 OPEN l_cumulative_csr;
350 LOOP
351
352 FETCH l_cumulative_csr INTO l_price_break_lookup_code;
353 EXIT WHEN l_cumulative_csr%NOTFOUND;
354
355 IF ( l_price_break_lookup_code = 'CUMULATIVE' )
356 THEN
357 return (TRUE); -- line is Cumulative Pricing, return TRUE
358 END IF;
359
360 END LOOP;
361 CLOSE l_cumulative_csr;
362
363 return (FALSE); -- if loop finishes, no cumulative lines found
364
365 END cumulative_lines_exist;
366
367
368 /*=============================================================================
369
370 FUNCTION: references_exist <GA FPI>
371
372 DESCRIPTION: Returns TRUE if there exist any references for any line in
373 the input po_header_id. FALSE, otherwise.
374
375 =============================================================================*/
376 /*FUNCTION references_exist
377 (
378 p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE
379 )
380 RETURN BOOLEAN
381 IS
382 CURSOR l_reference_csr IS
383 SELECT from_header_id, from_line_id
384 FROM po_lines_all
385 WHERE po_header_id = p_po_header_id;
386
387 l_from_header_id PO_LINES_ALL.from_header_id%TYPE;
388 l_from_line_id PO_LINES_ALL.from_line_id%TYPE;
389
390 BEGIN
391
392 OPEN l_reference_csr;
393 LOOP
394
395 FETCH l_reference_csr INTO l_from_header_id, l_from_line_id;
396 EXIT WHEN l_reference_csr%NOTFOUND;
397
398 IF ( ( l_from_header_id IS NOT NULL )
399 AND ( l_from_line_id IS NOT NULL ) )
400 THEN
401 return (TRUE); -- reference found, return TRUE
402 END IF;
403
404 END LOOP;
405 CLOSE l_reference_csr;
406
407 return (FALSE); -- if loop finishes, no references were found
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 PO_MESSAGE_S.sql_error('references_exist','000',sqlcode);
412 RAISE;
413 END references_exist;
414 */
415
416 -- <GC FPJ START>
417
418 /**=========================================================================
419 * Procedure: is_contract_valid <GC FPJ>
420 * Effects: Check whether a contract is valid to be referenced by new PO line,
421 * i.e., the contract is:
422 * - Not cancelled
423 * - open
424 * - approved
425 * - not frozen
426 * - not expired
427 * Requires: none
428 * Modifies: none
429 * Returns: x_result: FND_API.G_TRUE if valid
430 * FND_API.G_FALSE otherwise
431 ==========================================================================**/
432 PROCEDURE is_contract_valid
433 ( p_po_header_id IN NUMBER,
434 x_result OUT NOCOPY VARCHAR2
435 ) IS
436
437 l_is_valid VARCHAR2(1) := 'N';
438
439 BEGIN
440
441 SELECT 'Y'
442 INTO l_is_valid
443 FROM po_headers_all POH
444 WHERE POH.po_header_id = p_po_header_id
445 AND POH.type_lookup_code = 'CONTRACT'
446 AND NVL(POH.cancel_flag, 'N') = 'N'
447 AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
448 AND (( NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y' --<R12 GCPA ER>
449 and poh.approved_date is not null )
450 OR POH.authorization_status = 'APPROVED' )
451 AND NVL(POH.frozen_flag, 'N') = 'N'
452 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(POH.start_date), SYSDATE - 1)
453 AND NVL(TRUNC(POH.end_date), SYSDATE + 1);
454
455 IF (l_is_valid = 'Y') THEN
456 x_result := FND_API.G_TRUE;
457 ELSE
458 x_result := FND_API.G_FALSE;
459 END IF;
460
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 x_result := FND_API.G_FALSE;
464 END is_contract_valid;
465
466 -- <GC FPJ END>
467
468 END PO_HEADERS_SV4;