1 PACKAGE BODY po_interface_s2 AS
2 /* $Header: POXBWP2B.pls 120.5.12020000.2 2013/02/10 17:37:55 vegajula ship $*/
3
4 c_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_INTERFACE_S2.';
5
6
7 -- <GC FPJ START>
8
9 PROCEDURE get_distinct_src_id
10 ( p_po_header_id IN NUMBER,
11 p_src_doc_type IN VARCHAR2,
12 p_global_flag IN VARCHAR2,
13 x_src_doc_id OUT NOCOPY NUMBER);
14
15 -- <GC FPJ END>
16
17 /* ============================================================================
18 NAME: GET_SOURCE_INFO
19 DESC: Get quote info
20 ARGS: x_requisition_line_id IN number,
21 x_vendor_id IN number,
22 x_quote_header_id IN OUT number,
23 x_quote_line_id OUT number
24 ALGR:
25 History: 09-25-03 mbhargav Changed the signature to not take vendor_site_code
26 ==========================================================================*/
27 PROCEDURE get_source_info(x_requisition_line_id IN number,
28 x_vendor_id IN number,
29 x_currency IN varchar2,
30 x_source_header_id IN OUT NOCOPY number,
31 x_source_line_id OUT NOCOPY number,
32 p_vendor_site_id IN NUMBER, -- <GC FPJ>
33 p_purchasing_org_id IN NUMBER, -- <GC FPJ>
34 x_src_document_type OUT NOCOPY VARCHAR2 -- <GC FPJ>
35 ) IS
36
37 x_source_line_num number := null;
38 x_progress varchar2(3) ;
39
40 l_doc_type_code PO_REQUISITION_LINES_ALL.document_type_code%TYPE;
41 BEGIN
42
43 x_progress:='000';
44
45 BEGIN
46 SELECT blanket_po_header_id,
47 blanket_po_line_num,
48 document_type_code -- <GC FPJ>
49 INTO x_source_header_id,
50 x_source_line_num,
51 l_doc_type_code -- <GC FPJ>
52 FROM po_requisition_lines
53 WHERE requisition_line_id = x_requisition_line_id;
54
55 EXCEPTION
56 WHEN NO_DATA_FOUND THEN
57 x_source_header_id := null;
58 x_source_line_id := null;
59 return;
60 END;
61
62
63 /*
64 ** Get po_line_id for the source line if the vendor info/currency on the PO and
65 ** the source doc match
66 */
67
68 x_progress:='001';
69
70
71 BEGIN
72
73 -- <GC FPJ START>
74 IF (l_doc_type_code = 'CONTRACT') THEN
75
76 -- Return contract referenece (global or local) of the req if the ref
77 -- is consistent with the document the req line is going to add to.
78
79 -- SQL What: Determine whether the contract reference on the req
80 -- line can be carried over to PO line
81 -- SQL Why: Autocreate should not create a line with incorrect
82 -- contract reference
83
84 SELECT PH.po_header_id,
85 PH.type_lookup_code
86 INTO x_source_header_id,
87 x_src_document_type
88 FROM po_headers_all PH
89 WHERE PH.po_header_id = x_source_header_id
90 AND PH.type_lookup_code = 'CONTRACT'
91 AND PH.vendor_id = x_vendor_id
92 AND ((PH.global_agreement_flag = 'Y'
93 AND PH.currency_code = x_currency
94 AND (ph.vendor_site_id = p_vendor_site_id --<Shared Proc FPJ>
95 OR EXISTS (SELECT 1
96 FROM po_ga_org_assignments PGOA
97 WHERE PGOA.po_header_id = PH.po_header_id
98 AND PGOA.enabled_flag = 'Y'
99 AND PGOA.vendor_site_id = decode(Nvl(PH.Enable_All_Sites,'N'),'N',p_vendor_site_id, PGOA.vendor_site_id ))))
100 OR
101 (NVL(PH.global_agreement_flag, 'N') = 'N')
102 AND NVL(PH.org_id, -1) = NVL(p_purchasing_org_id, -1));
103 ELSE
104 -- <GC FPJ END>
105
106 SELECT pl.po_line_id,
107 ph.type_lookup_code -- <GC FPJ>
108 INTO x_source_line_id,
109 x_src_document_type -- <GC FPJ>
110 FROM po_headers_all ph,
111 po_lines_all pl
112 WHERE ph.po_header_id = pl.po_header_id
113 AND ph.vendor_id = x_vendor_id
114 AND ph.currency_code = x_currency --<Bug 3613912>
115 AND ((ph.type_lookup_code = 'BLANKET'
116 and nvl(ph.global_agreement_flag,'N') = 'Y'
117 --<Shared Proc FPJ START>
118 --Need to ensure the site is header site or one of the
119 --purchasing site on Global Agreement
120 AND (ph.vendor_site_id = p_vendor_site_id
121 OR EXISTS (SELECT 1
122 FROM po_ga_org_assignments PGOA
123 WHERE PGOA.po_header_id = x_source_header_id
124 AND PGOA.enabled_flag = 'Y'
125 AND PGOA.vendor_site_id = p_vendor_site_id)))
126 OR (ph.type_lookup_code = 'QUOTATION'
127 AND ph.vendor_site_id = p_vendor_site_id))
128 --<Shared Proc FPJ END>
129 AND ph.po_header_id = x_source_header_id
130 AND pl.line_num = x_source_line_num;
131 END IF;
132 EXCEPTION
133 WHEN NO_DATA_FOUND THEN
134
135 x_source_header_id := null;
136 x_source_line_id := null;
137 return;
138 END;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 po_message_s.sql_error('GET SOURCE INFO',x_progress,sqlcode);
143 raise;
144 END get_source_info;
145
146 PROCEDURE get_doc_header_info (p_add_to_doc_id IN NUMBER,
147 p_add_to_type OUT NOCOPY VARCHAR2,
148 p_add_to_vendor_id OUT NOCOPY NUMBER,
149 p_add_to_vendor_site_id OUT NOCOPY NUMBER,
150 p_add_to_currency_code OUT NOCOPY VARCHAR2,
151 p_add_to_terms_id OUT NOCOPY NUMBER,
152 p_add_to_ship_via_lookup_code OUT NOCOPY VARCHAR2,
153 p_add_to_fob_lookup_code OUT NOCOPY VARCHAR2,
154 p_add_to_freight_lookup_code OUT NOCOPY VARCHAR2,
155 x_add_to_shipping_control OUT NOCOPY VARCHAR2 -- <INBOUND LOGISTICS FPJ>
156 ) IS
157
158
159 BEGIN
160
161 Select type_lookup_code,
162 vendor_id,
163 vendor_site_id,
164 currency_code,
165 terms_id,
166 ship_via_lookup_code,
167 fob_lookup_code,
168 freight_terms_lookup_code,
169 shipping_control -- <INBOUND LOGISTICS FPJ>
170 into p_add_to_type,
171 p_add_to_vendor_id ,
172 p_add_to_vendor_site_id ,
173 p_add_to_currency_code ,
174 p_add_to_terms_id ,
175 p_add_to_ship_via_lookup_code ,
176 p_add_to_fob_lookup_code ,
177 p_add_to_freight_lookup_code,
178 x_add_to_shipping_control -- <INBOUND LOGISTICS FPJ>
179 From po_headers_all
180 Where po_header_id = p_add_to_doc_id;
181
182 EXCEPTION
183 when others then
184 p_add_to_type := null;
185 p_add_to_vendor_id := null;
186 p_add_to_vendor_site_id := null;
187 p_add_to_currency_code := null;
188 p_add_to_terms_id := null;
189 p_add_to_ship_via_lookup_code := null;
190 p_add_to_fob_lookup_code := null;
191 p_add_to_freight_lookup_code := null;
192 x_add_to_shipping_control := NULL; -- <INBOUND LOGISTICS FPJ>
193 END get_doc_header_info;
194
195
196 -----------------------------------------------------------------<SERVICES FPJ>
197 -------------------------------------------------------------------------------
198 --Start of Comments
199 --Name: is_req_in_pool
200 --Pre-reqs:
201 -- None.
202 --Modifies:
203 -- None.
204 --Locks:
205 -- None.
206 --Function:
207 -- Determines if a Requisition line is currently in the Requisition Pool.
208 --Parameters:
209 --IN:
210 --p_req_line_id
211 -- Unique ID of the Requisition Line
212 --Returns:
213 -- TRUE if the Requisition line is in the Requisition Pool. FALSE otherwise.
214 --Testing:
215 -- None.
216 --End of Comments
217 -------------------------------------------------------------------------------
218 -------------------------------------------------------------------------------
219 FUNCTION is_req_in_pool
220 (
221 p_req_line_id IN NUMBER
222 )
223 RETURN BOOLEAN
224 IS
225 l_reqs_in_pool_flag PO_REQUISITION_LINES_ALL.reqs_in_pool_flag%TYPE;
226 -- <REQINPOOL> variable deletion
227
228 BEGIN
229
230 -- <REQINPOOL START>
231 SELECT reqs_in_pool_flag
232 INTO l_reqs_in_pool_flag
233 FROM po_requisition_lines_all
234 WHERE requisition_line_id = p_req_line_id;
235
236 IF (l_reqs_in_pool_flag = 'Y') THEN
237 return (TRUE);
238 ELSE
239 return (FALSE);
240 END IF;
241 -- <REQINPOOL END>
242
243 EXCEPTION
244
245 WHEN OTHERS THEN
246 PO_MESSAGE_S.sql_error ( 'PO_INTERFACE_S2.is_in_req_pool', '000', SQLCODE );
247 RAISE;
248
249 END is_req_in_pool;
250
251
252 PROCEDURE update_terms(p_new_po_id IN number) IS
253
254
255 l_source_doc_id number;
256
257 l_terms_id po_headers.terms_id%TYPE;
258 l_fob_lookup_code po_headers.fob_lookup_code%TYPE;
259 l_freight_lookup_code po_headers.freight_terms_lookup_code%TYPE;
260 l_supplier_note po_headers.note_to_vendor%TYPE;
261 l_receiver_note po_headers.note_to_receiver%TYPE;
262
263 l_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE; -- <2748409>
264 l_pay_on_code PO_HEADERS_ALL.pay_on_code%TYPE; -- <2748409>
265 l_bill_to_location_id PO_HEADERS_ALL.bill_to_location_id%TYPE; -- <2748409>
266 l_ship_to_location_id PO_HEADERS_ALL.ship_to_location_id%TYPE; -- <2748409>
267
268 -- <GC FPJ START>
269 l_ga_count NUMBER;
270 l_quotation_count NUMBER;
271 l_lc_count NUMBER;
272 l_gc_count NUMBER;
273 -- <GC FPJ END>
274
275 l_shipping_control PO_HEADERS_ALL.shipping_control%TYPE; -- <INBOUND LOGISTICS FPJ>
276 l_return_status VARCHAR2(1);
277
278 l_api_name CONSTANT VARCHAR2(30) := 'update_terms ';
279
280 BEGIN
281 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
282 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || '.begin','update terms');
283 END IF;
284 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
285 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'new PO ' , to_char(p_new_po_id));
286 END IF;
287
288
289 -- <GC FPJ START>
290
291 -- SQL What: Get the number of distinct source documents for each source
292 -- documents type referenced on this standard PO
293 -- SQL Why: Based on the terms default hierarchy, we want to default the
294 -- terms and conditions from the source document if there is only
295 -- one distinct document of that document type.
296
297 /* Bug# 5873206, The condition for checking Quotation
298 was coded incorrectly. Due to this we were not considering
299 Terms and conditions when Source document was a Quotation.
300 Commented the NULL and added it in the else of decode. */
301
302 SELECT COUNT(DISTINCT (DECODE(POSRC.type_lookup_code,
303 'BLANKET',
304 POSRC.po_header_id,
305 NULL))),
306 COUNT(DISTINCT (DECODE(POSRC.type_lookup_code,
307 'QUOTATION',
308 --NULL, --Bug# 5873206,
309 POSRC.po_header_id,
310 NULL))),
311 COUNT(DISTINCT (DECODE(POC.global_agreement_flag,
312 'Y',
313 NULL,
314 POC.po_header_id))),
315 COUNT(DISTINCT (DECODE(POC.global_agreement_flag,
316 'Y',
317 POC.po_header_id,
318 NULL)))
319 INTO l_ga_count,
320 l_quotation_count,
321 l_lc_count,
322 l_gc_count
323 FROM po_lines POL,
324 po_headers_all POSRC,
325 po_headers_all POC
326 WHERE POL.po_header_id = p_new_po_id
327 AND POL.from_header_id = POSRC.po_header_id (+)
328 AND POL.contract_id = POC.po_header_id (+);
329 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
330 FND_LOG.string( FND_LOG.LEVEL_STATEMENT,
331 c_log_head || l_api_name || '.after_get_cnt',
335 END IF;
332 ' ga cnt = ' || l_ga_count || ' quot cnt = ' ||
333 l_quotation_count || ' lc cnt = ' || l_lc_count ||
334 ' gc cnt = ' || l_gc_count);
336
337 -- Rules of defaulting terms and conditions:
338 -- The source document hieararchy for terms and conditions defaulting is:
339 -- Global Agreement, Quotation, Local Contract, Global Contract.
340 -- 1) For a src document type, if there is only one distinct documents
341 -- referenced on the standard PO lines, default terms and conditions
342 -- from that src document
343 -- 2) If there are more than one distinct documents on that type being
344 -- referenced on the standard PO, do not default terms and conditions
345 -- 3) If there is no document of this type being referenced on the standard
346 -- PO, get the next document type and repeat the same checking.
347
348 IF (l_ga_count > 0) THEN
349 IF (l_ga_count = 1) THEN
350
351 -- default terms from GA
352 get_distinct_src_id
353 ( p_po_header_id => p_new_po_id,
354 p_src_doc_type => 'BLANKET',
355 p_global_flag => 'Y',
356 x_src_doc_id => l_source_doc_id
357 );
358 END IF;
359 ELSIF (l_quotation_count > 0) THEN
360 IF (l_quotation_count = 1) THEN
361
362 -- default terms from Quotation
363 get_distinct_src_id
364 ( p_po_header_id => p_new_po_id,
365 p_src_doc_type => 'QUOTATION',
366 p_global_flag => 'N',
367 x_src_doc_id => l_source_doc_id
368 );
369 END IF;
370 ELSIF (l_lc_count > 0) THEN
371 IF (l_lc_count = 1) THEN
372
373 -- default terms from local contract
374 get_distinct_src_id
375 ( p_po_header_id => p_new_po_id,
376 p_src_doc_type => 'CONTRACT',
377 p_global_flag => 'N',
378 x_src_doc_id => l_source_doc_id
379 );
380 END IF;
381 ELSIF (l_gc_count > 0) THEN
382 IF (l_gc_count = 1) THEN
383
384 -- default terms from global contract
385 get_distinct_src_id
386 ( p_po_header_id => p_new_po_id,
387 p_src_doc_type => 'CONTRACT',
388 p_global_flag => 'Y',
389 x_src_doc_id => l_source_doc_id
390 );
391 END IF;
392 END IF;
393
394 -- The original method of getting l_source_doc_id is not used
395 -- anymore. The code has been removed
396
397 -- <GC FPJ END>
398
399 IF (l_source_doc_id IS NOT NULL) THEN -- bug2930830
400 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
401 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'source ' , to_char(l_source_doc_id));
402 END IF;
403
404 Select terms_id,
405 fob_lookup_code,
406 freight_terms_lookup_code,
407 note_to_vendor,
408 note_to_receiver,
409 ship_via_lookup_code, -- <2748409>
410 pay_on_code, -- <2748409>
411 bill_to_location_id, -- <2748409>
412 ship_to_location_id, -- <2748409>
413 shipping_control -- <INBOUND LOGISTICS FPJ>
414 into l_terms_id ,
415 l_fob_lookup_code ,
416 l_freight_lookup_code ,
417 l_supplier_note,
418 l_receiver_note,
419 l_ship_via_lookup_code, -- <2748409>
420 l_pay_on_code, -- <2748409>
421 l_bill_to_location_id, -- <2748409>
422 l_ship_to_location_id, -- <2748409>
423 l_shipping_control -- <INBOUND LOGISTICS FPJ>
424 From po_headers_all
425 Where po_header_id = l_source_doc_id;
426 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
427 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'After SELECTing Terms and Conditions.' , 0);
428 END IF;
429
430 -- Bug 3807992: Added nvl to the terms to retain existing values if the
431 -- source doc does not have terms
432 update po_headers_all
433 set terms_id = nvl(l_terms_id ,terms_id),
434 fob_lookup_code = nvl(l_fob_lookup_code,fob_lookup_code),
435 freight_terms_lookup_code = nvl(l_freight_lookup_code,freight_terms_lookup_code),
436 note_to_vendor = l_supplier_note,
437 note_to_receiver = l_receiver_note,
438 shipping_control = nvl(l_shipping_control,shipping_control) -- <INBOUND LOGISTICS FPJ>
439 where po_header_id = p_new_po_id;
440 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
441 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'After UPDATEing Global Terms and Conditions.' , 0);
442 END IF;
443
444 -- <2748409 START>: If Req references a Global Agreement in the
445 -- Owning Org, update PO w/ Local Terms and Conditions from the GA.
446 --
447
448 -- <GC PFJ>
449 -- Update local terms as well if source doc is a local contract
450
451 IF ( PO_GA_PVT.is_owning_org(l_source_doc_id) OR
452 PO_GA_PVT.is_local_document(l_source_doc_id, 'CONTRACT') OR
453 (l_quotation_count = 1 and l_ship_to_location_id is not null)) --Bug 14553745
454 THEN
455
456 UPDATE po_headers_all
457 SET ship_via_lookup_code = nvl(l_ship_via_lookup_code ,ship_via_lookup_code),
461 WHERE po_header_id = p_new_po_id;
458 pay_on_code = nvl(l_pay_on_code ,pay_on_code ),
459 bill_to_location_id = l_bill_to_location_id ,
460 ship_to_location_id = l_ship_to_location_id
462
463 END IF;
464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
465 --
466 -- <2748409 END>
467
468 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name || 'After UPDATEing Local Terms and Conditions.' , 0);
469 END IF;
470
471 end if;
472
473 -- < 11i10+ - R12 Contracts ER Start>
474 -- Auto Apply the contract terms if the PO does not have any source
475 -- documents on any of the lines. We already have variables above
476 -- which have the src doc count. we will use these to check
477 -- Bug 4618614: GSCC error to check the log level
478 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
479 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||
480 'Auto Apply Contract terms if applicable.' , 0);
481 END IF;
482
483 IF l_ga_count = 0 AND
484 l_quotation_count = 0 AND
485 l_lc_count = 0 AND
486 l_gc_count = 0
487 THEN
488 PO_CONTERMS_UTL_GRP.Auto_Apply_ConTerms (
489 p_document_id => p_new_po_id,
490 p_template_id => NULL,
491 x_return_status => l_return_status);
492 END IF;
493
494 -- Bug 4618614: GSCC error to check the log level
495 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
496 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||
497 'Return Status: '||l_return_status , 0);
498 END IF;
499 -- < 11i10+ - R12 Contracts ER End>
500
501 exception
502 when others then
503 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
504 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,c_log_head || l_api_name ||'.EXCEPTION', 'update terms: Inside exception :'|| '000' ||sqlcode);
505 END IF;
506 END update_terms;
507
508 -- <GC FPJ START>
509
510 /**=========================================================================
511 * Procedure: get_distinct_src_id <GC FPJ>
512 * Effects: Get the distinct source document on the standard PO, given
513 * the the source document type.
514 * Requires: The proceudre assumes that there is only one distinct reference
515 * document of the type specified within the whole standard PO.
516 * Parameters: p_po_header_id - header id of the standard PO
517 * p_src_doc_type - src document type being searched for
518 * p_global_flag - whether the src document is global
519 * x_src_doc_id - return value (header id of the src document)
520 ==========================================================================**/
521
522 PROCEDURE get_distinct_src_id
523 ( p_po_header_id IN NUMBER,
524 p_src_doc_type IN VARCHAR2,
525 p_global_flag IN VARCHAR2,
526 x_src_doc_id OUT NOCOPY NUMBER) IS
527
528 l_src_doc_id PO_HEADERS_ALL.po_header_id%TYPE := NULL;
529 l_global_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
530
531 BEGIN
532 IF (p_src_doc_type IN ('BLANKET', 'QUOTATION')) THEN
533 SELECT POH.po_header_id
534 INTO x_src_doc_id
535 FROM po_lines POL,
536 po_headers_all POH
537 WHERE POL.po_header_id = p_po_header_id
538 AND POH.po_header_id = POL.from_header_id
539 AND POH.type_lookup_code = p_src_doc_type
540 AND NVL(POH.global_agreement_flag, 'N') = p_global_flag
541 AND ROWNUM = 1;
542
543 ELSIF (p_src_doc_type = 'CONTRACT') THEN
544
545 SELECT POH.po_header_id
546 INTO x_src_doc_id
547 FROM po_lines POL,
548 po_headers_all POH
549 WHERE POL.po_header_id = p_po_header_id
550 AND POH.po_header_id = POL.contract_id
551 AND NVL(POH.global_agreement_flag, 'N') = p_global_flag
552 AND ROWNUM = 1;
553 END IF;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 x_src_doc_id := NULL;
558 END get_distinct_src_id;
559
560 -- <GC FPJ END>
561
562 END po_interface_s2;
[Home] [Help]