1 PACKAGE BODY PO_HEADERS_SV2 as
2 /* $Header: POXPOH2B.pls 120.0.12000000.2 2007/10/11 14:03:11 ppadilam ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: val_approval_status()
7
8 ===========================================================================*/
9
10 FUNCTION val_approval_status(X_po_header_id IN NUMBER,
11 X_agent_id IN NUMBER,
12 X_vendor_site_id IN NUMBER,
13 X_vendor_contact_id IN NUMBER,
14 X_confirming_order_flag IN VARCHAR2,
15 X_ship_to_location_id IN NUMBER,
16 X_bill_to_location_id IN NUMBER,
17 X_terms_id IN NUMBER,
18 X_ship_via_lookup_code IN VARCHAR2,
19 X_fob_lookup_code IN VARCHAR2,
20 X_freight_terms_lookup_code IN VARCHAR2,
21 X_note_to_vendor IN VARCHAR2,
22 X_acceptance_required_flag IN VARCHAR2,
23 X_acceptance_due_date IN DATE,
24 X_blanket_total_amount IN NUMBER,
25 X_start_date IN DATE,
26 X_end_date IN DATE,
27 X_amount_limit IN NUMBER
28 ,p_kterms_art_upd_date IN DATE --<CONTERMS FPJ>
29 ,p_kterms_deliv_upd_date IN DATE --<CONTERMS FPJ>
30 ,p_shipping_control IN VARCHAR2 -- <INBOUND LOGISTICS FPJ>
31 )
32 return boolean is
33 X_unapprove_doc boolean;
34
35 X_progress VARCHAR2(3) := NULL;
36 X_change_status varchar2(1) := 'N';
37
38 BEGIN
39 /* Check if there has been any change in the fields that
40 ** affect the approval status. If so , return TRUE to
41 ** indicate that the approval status has to be changed */
42
43 SELECT 'Y'
44 INTO X_change_status
45 FROM PO_HEADERS POH
46 WHERE (poh.po_header_id = X_po_header_id)
47 AND ( (poh.agent_id <> X_agent_id)
48 OR
49 (poh.agent_id is NULL AND
50 X_agent_id is NOT NULL)
51 OR (poh.agent_id is NOT NULL AND
52 X_agent_id is NULL)
53 OR (poh.vendor_site_id <> X_vendor_site_id)
54 OR (poh.vendor_site_id is NULL AND
55 X_vendor_site_id is NOT NULL)
56 OR (poh.vendor_site_id is NOT NULL AND
57 X_vendor_site_id is NULL)
58 OR (poh.vendor_contact_id <> X_vendor_contact_id )
59 OR (poh.vendor_contact_id is NULL AND
60 X_vendor_contact_id is NOT NULL)
61 OR (poh.vendor_contact_id is NOT NULL AND
62 X_vendor_contact_id is NULL)
63 OR (poh.confirming_order_flag <> X_confirming_order_flag)
64 OR (poh.confirming_order_flag is NULL AND
65 X_confirming_order_flag is NOT NULL)
66 OR (poh.confirming_order_flag is NOT NULL AND
67 X_confirming_order_flag is NULL)
68 OR (poh.ship_to_location_id <> X_ship_to_location_id)
69 OR (poh.ship_to_location_id is NULL AND
70 X_ship_to_location_id is NOT NULL)
71 OR (poh.ship_to_location_id is NOT NULL AND
72 X_ship_to_location_id is NULL)
73 OR (poh.bill_to_location_id <> X_bill_to_location_id)
74 OR (poh.bill_to_location_id is NULL AND
75 X_bill_to_location_id is NOT NULL)
76 OR (poh.bill_to_location_id is NOT NULL AND
77 X_bill_to_location_id is NULL)
78 OR (poh.terms_id <> X_terms_id )
79 OR (poh.terms_id is NULL AND
80 X_terms_id is NOT NULL)
81 OR (poh.terms_id is NOT NULL AND
82 X_terms_id is NULL)
83 OR ( poh.ship_via_lookup_code <> X_ship_via_lookup_code)
84 OR ( poh.ship_via_lookup_code is NOT NULL AND
85 X_ship_via_lookup_code is NULL)
86 OR ( poh.ship_via_lookup_code is NULL AND
87 X_ship_via_lookup_code is NOT NULL)
88 OR (poh.fob_lookup_code <> X_fob_lookup_code )
89 OR (poh.fob_lookup_code is NULL AND
90 X_fob_lookup_code is NOT NULL)
91 OR (poh.fob_lookup_code is NOT NULL AND
92 X_fob_lookup_code is NULL)
93 OR (poh.freight_terms_lookup_code <> X_freight_terms_lookup_code )
94 OR (poh.freight_terms_lookup_code is NULL AND
95 X_freight_terms_lookup_code is NOT NULL)
96 OR (poh.freight_terms_lookup_code is NOT NULL AND
97 X_freight_terms_lookup_code is NULL)
98 OR (poh.note_to_vendor <> X_note_to_vendor )
99 OR (poh.note_to_vendor is NULL AND
100 X_note_to_vendor is NOT NULL)
101 OR (poh.note_to_vendor is NOT NULL AND
102 X_note_to_vendor is NULL)
103 OR (poh.acceptance_required_flag <> X_acceptance_required_flag)
104 OR (poh.acceptance_required_flag is NULL AND
105 X_acceptance_required_flag is NOT NULL)
106 OR (poh.acceptance_required_flag is NOT NULL AND
107 X_acceptance_required_flag is NULL)
108 OR (poh.acceptance_due_date <> X_acceptance_due_date )
109 OR (poh.acceptance_due_date is NULL AND
110 X_acceptance_due_date is NOT NULL)
111 OR (poh.acceptance_due_date is NOT NULL AND
112 X_acceptance_due_date is NULL)
113 OR (poh.blanket_total_amount <> X_blanket_total_amount)
114 OR (poh.blanket_total_amount is NULL AND
115 X_blanket_total_amount is NOT NULL)
116 OR (poh.blanket_total_amount is NOT NULL AND
117 X_blanket_total_amount is NULL)
118 OR (poh.start_date <> X_start_date)
119 OR (poh.start_date is NULL AND
120 X_start_date is NOT NULL)
121 OR (poh.start_date is NOT NULL AND
122 X_start_date is NULL)
123 OR (poh.end_date <> X_end_date )
124 OR (poh.end_date is NULL AND
125 X_end_date is NOT NULL)
126 OR (poh.end_date is NOT NULL AND
127 X_end_date is NULL)
128 OR (poh.amount_limit <> X_amount_limit)
129 OR (poh.amount_limit is NULL AND
130 X_amount_limit is NOT NULL)
131 OR (poh.amount_limit is NOT NULL AND
132 X_amount_limit is NULL)
133
134 --<CONTERMS FPJ START>
135 OR (poh.conterms_articles_upd_date <> p_kterms_art_upd_date)
136 OR (poh.conterms_articles_upd_date is NULL AND
137 p_kterms_art_upd_date is NOT NULL)
138 OR (poh.conterms_articles_upd_date is NOT NULL AND
139 p_kterms_art_upd_date is NULL)
140
141 OR (poh.conterms_deliv_upd_date <> p_kterms_deliv_upd_date)
142 OR (poh.conterms_deliv_upd_date is NULL AND
143 p_kterms_deliv_upd_date is NOT NULL)
144 OR (poh.conterms_deliv_upd_date is NOT NULL AND
145 p_kterms_deliv_upd_date is NULL)
146
147 --<CONTERMS FPJ END>
148
149 -- <INBOUND LOGISTICS FPJ START>
150 OR (POH.shipping_control <> p_shipping_control)
151 OR (POH.shipping_control IS NULL AND
152 p_shipping_control IS NOT NULL)
153 OR ( POH.shipping_control IS NOT NULL AND
154 p_shipping_control IS NULL)
155 -- <INBOUND LOGISTICS FPJ END>
156 );
157
158
159 if X_change_status = 'Y' then
160 X_unapprove_doc := TRUE;
161 else
162 X_unapprove_doc := FALSE;
163 end if;
164
165 return(X_unapprove_doc);
166
167
168 EXCEPTION
169 when no_data_found then
170 --dbms_output.put_line('No data found');
171 X_unapprove_doc := FALSE;
172 return(X_unapprove_doc);
173 when others then
174 po_message_s.sql_error('val_approval_status', x_progress, sqlcode);
175 raise;
176
177 END val_approval_status;
178
179
180 /*===========================================================================
181
182 PROCEDURE NAME: update_children()
183
184 ===========================================================================*/
185 /*
186 PROCEDURE update_children() IS
187
188 x_progress VARCHAR2(3) := NULL;
189
190 BEGIN
191
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 po_message_s.sql_error('update_children', x_progress, sqlcode);
196 RAISE;
197
198 END update_children; */
199
200
201
202 /*===========================================================================
203
204 FUNCTION NAME: val_release_date()
205
206 ===========================================================================*/
207
208 FUNCTION val_release_date(X_po_header_id IN number,
209 X_start_date IN date,
210 X_end_date IN date,
211 X_type_lookup_code IN varchar2,
212 X_menu_path IN varchar2)
213 return boolean IS
214 X_success boolean := TRUE;
215
216 X_progress VARCHAR2(3) := '';
217
218
219 BEGIN
220
221 if ((X_type_lookup_code = 'PLANNED') or
222 (X_type_lookup_code = 'BLANKET')) then
223
224 /* X_progress := '010';
225 X_success := val_start_date(X_po_header_id,X_start_date); */
226
227 /* Check that there is no release already created against the PO
228 ** after the specified end date. */
229
230 if X_success then
231 X_progress := '020';
232 X_success := val_end_date( X_po_header_id,X_end_date);
233 end if;
234
235 if (not X_success) then
236 return(X_success);
237 end if;
238
239 end if;
240
241 /* Bug 509797 ecso 6/26/97
242 Move this validation to client side
243
244 if (X_menu_path = 'PA') then
245 X_progress := '030';
246 X_success := po_notif_controls_sv.val_date_notif(X_po_header_id,X_end_date);
247 end if;
248 */
249 return(X_success);
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 po_message_s.sql_error('val_release_date', X_progress, sqlcode);
254 RAISE;
255
256 END val_release_date;
257
258 /*===========================================================================
259
260 FUNCTION NAME: val_start_date()
261
262 ===========================================================================*/
263
264 FUNCTION val_start_date(X_po_header_id IN number,
265 X_start_date IN date )
266 return boolean is
267 X_valid_date boolean;
268
269 X_progress varchar2(3) := '000';
270
271 cursor c1 is
272 SELECT 'release exist prior to the effective date'
273 FROM po_releases
274 WHERE release_date < X_start_date
275 AND po_header_id = X_po_header_id;
276 Recinfo c1%rowtype;
277
278 BEGIN
279
280 X_progress := '010';
281 open c1;
282 X_progress := '020';
283 fetch c1 into recinfo;
284
285 X_progress := '030';
286
287 if (c1%notfound) then
288 close c1;
289 X_valid_date := TRUE;
290 return(X_valid_date);
291 end if;
292
293 X_progress := '040';
294
295 X_valid_date := FALSE;
296 /* po_message_s.app_error('PO_PO_START_DATE_LT_REL_DATE'); bug # 6363130 */
297 return(X_valid_date);
298
299 EXCEPTION
300
301 when others then
302 po_message_s.sql_error('val_start_date',X_progress,sqlcode);
303 raise;
304
305 END val_start_date;
306
307
308 /*===========================================================================
309
310 FUNCTION NAME: val_end_date()
311
312 ===========================================================================*/
313
314 FUNCTION val_end_date(X_po_header_id IN number,
315 X_end_date IN date )
316 return boolean is
317 X_valid_date boolean ;
318
319 X_progress VARCHAR2(3) := '';
320
321 cursor c1 is
322 SELECT 'release exist after the expiration date'
323 FROM po_releases
324 -- <Action Date TZ FPJ: trunc added>
325 WHERE TRUNC(release_date) > X_end_date
326 AND po_header_id = X_po_header_id;
327
328 Recinfo c1%rowtype;
329
330 BEGIN
331 X_progress := '010';
332 open c1;
333 X_progress := '020';
334
335
336 /* Check that there is no release against this PO after the date
337 specified */
338
339 fetch c1 into recinfo;
340
341 X_progress := '030';
342
343 if (c1%notfound) then
344 close c1;
345 X_valid_date := TRUE;
346 return(X_valid_date);
347 end if;
348
349 X_progress := '040';
350
351 X_valid_date := FALSE;
352 po_message_s.app_error('PO_PO_END_DATE_GT_RELEASE_DATE');
353 return(X_valid_date);
354
355 EXCEPTION
356 when others then
357 po_message_s.sql_error('val_end_date', x_progress, sqlcode);
358 raise;
359
360 END val_end_date;
361
362 /*==========================================================================
363
364 PROCEDURE NAME : update_req_link()
365
366 ===========================================================================*/
367
368 PROCEDURE update_req_link(X_po_header_id IN number) is
369
370 X_progress varchar2(3) := '';
371
372 BEGIN
373 X_progress := '010';
374
375 /* Update po_requisition_lines to remove the link to
376 the shipment if you are deleting a standard or planned PO */
377
378 -- <REQINPOOL>: added update of reqs_in_pool_flag and of
379 -- WHO columns.
380
381 UPDATE po_requisition_lines_all
382 SET line_location_id = NULL
383 , reqs_in_pool_flag = 'Y'
384 , last_update_date = SYSDATE
388 FROM po_line_locations
385 , last_updated_by = FND_GLOBAL.USER_ID
386 , last_update_login = FND_GLOBAL.LOGIN_ID
387 WHERE line_location_id in (SELECT line_location_id
389 WHERE po_header_id = X_po_header_id);
390
391 EXCEPTION
392
393 /* It is not mandatory that every PO has been created from a Req.
394 So, if there is no data found, it is NOT an error */
395
396 when no_data_found then
397 null;
398
399
400 when others then
401 po_message_s.sql_error('update_req_link', X_progress, sqlcode);
402 raise;
403
404 END update_req_link;
405
406 /*===========================================================================
407
408 PROCEDURE NAME: get_po_details()
409
410 ===========================================================================*/
411
412 PROCEDURE get_po_details(X_po_header_id IN NUMBER ,
413 X_type_lookup_code IN OUT NOCOPY VARCHAR2,
414 X_revision_num IN OUT NOCOPY NUMBER ,
415 X_currency_code IN OUT NOCOPY VARCHAR2,
416 X_supplier_id IN OUT NOCOPY NUMBER ,
417 X_supplier_site_id IN OUT NOCOPY NUMBER ,
418 X_ship_to_location_id IN OUT NOCOPY NUMBER ) IS
419 CURSOR C is
420 SELECT POH.currency_code,
421 POH.type_lookup_code,
422 POH.revision_num,
423 POH.vendor_id,
424 POH.vendor_site_id,
425 POH.ship_to_location_id
426 FROM PO_HEADERS POH
427 WHERE POH.po_header_id = X_po_header_id;
428
429
430 X_progress VARCHAR2(3) := '';
431
432 BEGIN
433 --dbms_output.put_line('Before open cursor');
434
435 if (X_po_header_id is not null) then
436 X_progress := '010';
437 OPEN C;
438 X_progress := '020';
439
440 /* Get the following info for a given PO */
441
442 FETCH C into X_currency_code,
443 X_type_lookup_code,
444 X_revision_num,
445 X_supplier_id,
446 X_supplier_site_id,
447 X_ship_to_location_id;
448
449 CLOSE C;
450
451 --dbms_output.put_line('Currency Code'||X_currency_code);
452 --dbms_output.put_line('Type Lookup Code'||X_type_lookup_code);
453 --dbms_output.put_line('Revision'||X_revision_num);
454 --dbms_output.put_line('Supplier'||X_supplier_id);
455 --dbms_output.put_line('Supplier_site'||X_supplier_site_id);
456 --dbms_output.put_line('Ship_to_location'||X_ship_to_location_id);
457
458 else
459 X_progress := '030';
460 po_message_s.sql_error('no header id', X_progress, sqlcode);
461
462 end if;
463
464
465 EXCEPTION
466 when others then
467 po_message_s.sql_error('get_po_details', x_progress, sqlcode);
468 raise;
469
470 END get_po_details;
471
472 /*===========================================================================
473
474 PROCEDURE NAME: get_segment1_details()
475
476 ===========================================================================*/
477 /*
478 PROCEDURE get_segment1_details() IS
479
480 x_progress VARCHAR2(3) := NULL;
481
482 BEGIN
483
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 po_message_s.sql_error('get_segment1_details', x_progress, sqlcode);
488 RAISE;
489
490 END get_segment1_details; */
491
492
493 procedure test_start_date ( X_po_header_id IN number, X_start_date IN date) is
494 valid_date boolean ;
495 begin
496 valid_date := po_headers_sv2.val_start_date(X_po_header_id, X_start_date);
497 /*
498 if valid_date then
499 --dbms_output.put_line('VALID');
500 else
501 --dbms_output.put_line('INVALID');
502 end if;
503 */
504 end test_start_date;
505
506 procedure test_get_po_encumbered (X_po_header_id IN number) is
507 X_encumb boolean;
508
509 begin
510 X_encumb := po_headers_sv1.get_po_encumbered(X_po_header_id);
511 /*
512 if X_encumb then
513 --dbms_output.put_line('ENCUMBERED');
514 else
515 dbms_output.put_line('NOT ENCUMBERED');
516 end if;
517 */
518
519 end test_get_po_encumbered;
520 /*===========================================================================
521
522 PROCEDURE NAME: get_document_status()
523
524 ===========================================================================*/
525
526
527
528 procedure get_document_status(X_lookup_code IN varchar2,
529 X_document_type IN varchar2,
530 X_document_status IN OUT NOCOPY varchar2) is
531
532 X_progress varchar2(3) := '';
533
534 begin
535 IF X_document_type NOT IN ('RFQ', 'QUOTATION') THEN
536 X_progress := '010';
537
538 select polc.displayed_field
539 into X_document_status
540 from po_lookup_codes polc
544 ELSE
541 where polc.lookup_type = 'AUTHORIZATION STATUS'
542 and polc.lookup_code = X_lookup_code ;
543
545
546 X_progress :='020';
547
548 select polc.displayed_field
549 into X_document_status
550 from po_lookup_codes polc
551 where lookup_type = 'RFQ/QUOTE STATUS'
552 and lookup_code = X_lookup_code;
553
554 END IF;
555
556 exception
557 when others then
558 po_message_s.sql_error('get_document_status', X_progress, sqlcode);
559 raise;
560
561 end get_document_status;
562
563
564 -- <GC FPJ START>
565
566 /*===========================================================================
567
568 PROCEDURE NAME: val_contract_eff_date <GC FPJ>
569
570 ===========================================================================*/
571
572 PROCEDURE val_contract_eff_date
573 ( p_po_header_id IN NUMBER,
574 p_start_date IN DATE,
575 p_end_date IN DATE,
576 x_result OUT NOCOPY VARCHAR2
577 ) IS
578
579 l_invalid VARCHAR2(1);
580
581 BEGIN
582
583 IF (p_po_header_id IS NULL
584 OR
585 (p_start_date IS NULL AND p_end_date IS NULL)
586 ) THEN
587
588 -- if contract is not created or no eff date is specified, there
589 -- is no need to check
590
591 x_result := FND_API.G_TRUE;
592 ELSE
593
594 -- SQL What: Return lines that reference the contract, but creation
595 -- date of their headers are not within effective dates
596 -- of the contract
597 -- SQL Why: Need to determine if there is any line violating
598 -- the effective date rule of the contract
599
600 SELECT 'Y'
601 INTO l_invalid
602 FROM po_headers_all POH,
603 po_lines_all POL
604 WHERE POL.contract_id = p_po_header_id
605 AND POL.po_header_id = POH.po_header_id
606 AND TRUNC(POL.creation_date) NOT BETWEEN
607 NVL(TRUNC(p_start_date), POL.creation_date - 1)
608 AND NVL(TRUNC(p_end_date), POL.creation_date + 1)
609 AND POH.approved_date IS NOT NULL
610 AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
611 AND NVL(POH.cancel_flag, 'N') <> 'Y';
612
613 -- return a row violating the effective dates
614 x_result := FND_API.G_FALSE;
615 END IF;
616
617 EXCEPTION
618 WHEN NO_DATA_FOUND THEN
619 x_result := FND_API.G_TRUE; -- all line created within eff date
620 WHEN TOO_MANY_ROWS THEN
621 x_result := FND_API.G_FALSE; -- mult line created outside eff dates
622 WHEN OTHERS THEN
623 x_result := FND_API.G_FALSE;
624 RAISE;
625 END val_contract_eff_date;
626
627 -- <GC FPJ END>
628
629 END PO_HEADERS_SV2;
[Home] [Help]