1 PACKAGE BODY PO_RELEASES_SV4 as
2 /* $Header: POXPOR4B.pls 120.1 2006/03/23 04:50:22 amony noship $ */
3
4
5 /*===========================================================================
6
7 PROCEDURE NAME: release_post_query
8
9 ===========================================================================*/
10 PROCEDURE release_post_query
11 (
12 X_release_id IN NUMBER,
13 X_rel_total OUT NOCOPY NUMBER,
14 X_encumbered_flag OUT NOCOPY VARCHAR2,
15 X_release_type IN VARCHAR2,
16 X_po_header_id IN NUMBER,
17 X_rel_total_2 OUT NOCOPY NUMBER,
18 X_ship_via_lookup_code OUT NOCOPY VARCHAR2,
19 X_ship_num OUT NOCOPY NUMBER,
20 p_ship_to_org_id IN NUMBER, --< Bug 3378554 Start >
21 p_po_authorization_status IN VARCHAR2,
22 p_freight_terms_lookup_code IN VARCHAR2,
23 p_fob_lookup_code IN VARCHAR2,
24 p_pay_on_code IN VARCHAR2,
25 x_ship_to_org_code OUT NOCOPY VARCHAR2,
26 x_agreement_status OUT NOCOPY VARCHAR2,
27 x_freight_terms OUT NOCOPY VARCHAR2,
28 x_fob OUT NOCOPY VARCHAR2,
29 x_pay_on_dsp OUT NOCOPY VARCHAR2 --< Bug 3378554 End >
30 )
31 IS
32 X_progress varchar2(3) := '';
33
34 BEGIN
35
36 X_progress := '010';
37
38 X_rel_total := po_core_s.get_total('R', X_release_id,FALSE);
39
40 /* Bug#2567391 : Replaced the following call which gets the release header
41 total with the above call to handle the rounding problem in running
42 total implementation. Commenting the following call
43 X_rel_total :=
44 po_line_locations_pkg_s3.select_summary(X_release_id); */
45
46
47 --<Encumbrance FPJ>
48 PO_CORE_S.should_display_reserved(
49 p_doc_type => PO_CORE_S.g_doc_type_RELEASE
50 , p_doc_level => PO_CORE_S.g_doc_level_HEADER
51 , p_doc_level_id => x_release_id
52 , x_display_reserved_flag => x_encumbered_flag
53 );
54
55
56 IF (X_release_type = 'BLANKET') THEN
57 X_rel_total_2 := po_core_s.get_total('B', X_po_header_id);
58 ELSE
59 X_rel_total_2 := po_core_s.get_total('P', X_po_header_id);
60 END IF;
61
62 SELECT ship_via_lookup_code
63 INTO X_ship_via_lookup_code
64 FROM po_headers
65 WHERE po_header_id = X_po_header_id;
66
67 X_ship_num := po_line_locations_pkg_s3.get_max_shipment_num(NULL,
68 X_release_id,
69 X_release_type);
70
71 --< Bug 3378554 Start >
72 IF (p_ship_to_org_id IS NOT NULL) THEN
73 BEGIN
74 --SQL What: Get org code of p_ship_to_org_id if it is in current SOB
75 --SQL Why: Used for defaulting ship to org
76 SELECT mp.organization_code
77 INTO x_ship_to_org_code
78 FROM financials_system_parameters fsp,
79 hr_organization_information hoi,
80 mtl_parameters mp
81 WHERE mp.organization_id = p_ship_to_org_id
82 AND mp.organization_id = hoi.organization_id
83 AND hoi.org_information_context = 'Accounting Information'
84 AND hoi.org_information1 = TO_CHAR(fsp.set_of_books_id);
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 -- If not found, then do nothing
88 NULL;
89 END;
90 END IF; --< if p_ship_to_org_id not null >
91
92 PO_CORE_S.get_displayed_value
93 (x_lookup_type => 'AUTHORIZATION STATUS',
94 x_lookup_code => NVL(p_po_authorization_status,'INCOMPLETE'),
95 x_disp_value => x_agreement_status);
96
97 IF (p_freight_terms_lookup_code IS NOT NULL) THEN
98 PO_CORE_S.get_displayed_value
99 (x_lookup_type => 'FREIGHT TERMS',
100 x_lookup_code => p_freight_terms_lookup_code,
101 x_disp_value => x_freight_terms);
102 END IF;
103
104 IF (p_fob_lookup_code IS NOT NULL) THEN
105 PO_CORE_S.get_displayed_value
106 (x_lookup_type => 'FOB',
107 x_lookup_code => p_fob_lookup_code,
108 x_disp_value => x_fob);
109 END IF;
110
111 IF (p_pay_on_code IS NOT NULL) THEN
112 PO_CORE_S.get_displayed_value
113 (x_lookup_type => 'PAY ON CODE',
114 x_lookup_code => p_pay_on_code,
115 x_disp_value => x_pay_on_dsp);
116 END IF;
117 --< Bug 3378554 End >
118
119 EXCEPTION
120 when NO_DATA_FOUND then null;
121 when others then
122 --dbms_output.put_line('In exception');
123 po_message_s.sql_error('release_post_query', X_progress, sqlcode);
124 raise;
125
126 END release_post_query;
127
128
129
130 /*===========================================================================
131
132 PROCEDURE NAME: get_release_num
133
134 ===========================================================================*/
135 PROCEDURE get_release_num
136 (X_po_header_id IN NUMBER,
137 X_release_num IN OUT NOCOPY NUMBER) IS
138
139 X_progress varchar2(3) := '';
140
141 CURSOR C is
142 SELECT to_number(max(POR.release_num) + 1)
143 FROM po_releases POR
144 WHERE POR.po_header_id = X_po_header_id;
145
146 BEGIN
147
148 --dbms_output.put_line('Before open cursor');
149
150 if (X_po_header_id is not null) then
151 X_progress := '010';
152 OPEN C;
153 X_progress := '020';
154
155 FETCH C into X_release_num;
156
157 CLOSE C;
158
159 --
160 -- If there is not a release number then this is the
161 -- first release to be created and the release number
162 -- should be defaulted to 1.
163 --
164 if (X_release_num is null) then
165 X_release_num := 1;
166 end if;
167
168 --dbms_output.put_line('Release Num'||X_release_num);
169
170 else
171 X_progress := '030';
172 po_message_s.sql_error('get_release_num', X_progress, sqlcode);
173
174 end if;
175
176 EXCEPTION
177 when others then
178 --dbms_output.put_line('In exception');
179 po_message_s.sql_error('get_release_num', X_progress, sqlcode);
180 raise;
181 END get_release_num;
182
183 /*===========================================================================
184
185 PROCEDURE NAME: get_po_release_id()
186
187 ===========================================================================*/
188
189 PROCEDURE get_po_release_id
190 (X_po_release_id_record IN OUT NOCOPY rcv_shipment_line_sv.release_id_record_type) is
191
192 v_count number;
193
194 BEGIN
195
196 /* If shipment_num, po_line_id and release_num are provided
197 then we can find the exact po_line_location_record */
198
199 IF X_po_release_id_record.shipment_num IS NOT NULL AND
200 X_po_release_id_record.release_num IS NOT NULL THEN
201
202 begin
203
204 select pll.po_release_id, pll.line_location_id
205 into X_po_release_id_record.po_release_id ,
206 X_po_release_id_record.po_line_location_id
207 from po_line_locations pll, po_releases pr where
208 pll.po_line_id = nvl(X_po_release_id_record.po_line_id,pll.po_line_id) and --1830177
209 pll.po_release_id = pr.po_release_id and
210 pr.po_header_id = X_po_release_id_record.po_header_id and
211 pr.release_num = X_po_release_id_record.release_num and
212 pll.shipment_num = X_po_release_id_record.shipment_num;
213
214 exception
215
216 when no_data_found then
217
218 -- Bug 4881909 : Returning proper error_message in case of exception.
219
220 begin
221
222 select 1
223 into v_count
224 from po_releases
225 where po_header_id = X_po_release_id_record.po_header_id
226 and release_num = X_po_release_id_record.release_num;
227
228 exception
229
230 when no_data_found then
231
232 X_po_release_id_record.error_record.error_status := 'F';
233 X_po_release_id_record.error_record.error_message := 'RCV_ROI_INVALID_RELEASE_NUM';
234
235 end;
236
237 if (X_po_release_id_record.error_record.error_status <> 'F' ) then
238
239 X_po_release_id_record.error_record.error_status := 'F';
240 X_po_release_id_record.error_record.error_message := 'RCV_ROI_INVALID_REL_SHIP_NUM';
241
242 end if;
243 end;
244
245 END IF;
246
247 /* If shipment_num is null and po_line_id and release_num are
248 provided then
249 we can find the po_release_id
250 we can FIND the po_line_location_id if there is only one record for this release_num */
251
252 IF X_po_release_id_record.shipment_num IS NULL AND
253 X_po_release_id_record.release_num IS NOT NULL THEN
254
255 select count(*) into v_count
256 from po_line_locations pll, po_releases pr where
257 pll.po_line_id = nvl(X_po_release_id_record.po_line_id,pll.po_line_id) and --1830177
258 pll.po_release_id = pr.po_release_id and
259 pr.po_header_id = X_po_release_id_record.po_header_id and
260 pr.release_num = X_po_release_id_record.release_num;
261
262 IF v_count = 1 THEN
263
264 select pll.po_release_id, pll.line_location_id, pll.shipment_num
265 into X_po_release_id_record.po_release_id ,
266 X_po_release_id_record.po_line_location_id,
267 X_po_release_id_record.shipment_num
268 from po_line_locations pll, po_releases pr where
269 pll.po_line_id = nvl(X_po_release_id_record.po_line_id,pll.po_line_id) and --1830177
270 pll.po_release_id = pr.po_release_id and
271 pr.po_header_id = X_po_release_id_record.po_header_id and
272 pr.release_num = X_po_release_id_record.release_num;
273
274 ELSIF v_count > 1 then
275
276 select distinct pll.po_release_id
277 into X_po_release_id_record.po_release_id
278 from po_line_locations pll, po_releases pr where
279 pll.po_line_id = nvl(X_po_release_id_record.po_line_id,pll.po_line_id) and--1830177
280 pll.po_release_id = pr.po_release_id and
281 pr.po_header_id = X_po_release_id_record.po_header_id and
282 pr.release_num = X_po_release_id_record.release_num;
283
284 /* Bug# 2677526 */
285 x_po_release_id_record.po_line_location_id := NULL;
286
287 ELSIF v_count = 0 then
288
289 x_po_release_id_record.po_line_location_id := NULL;
290 x_po_release_id_record.po_release_id := NULL;
291 x_po_release_id_record.error_record.error_status := 'F';
292 x_po_release_id_record.error_record.error_message := 'RCV_ITEM_PO_REL_ID';
293
294 END IF;
295
296 END IF;
297
298 /* If po_line_id, shipment_num are not null and release_num is null then
299 we can find the po_line_location_id and po_release_id if there is only
300 one record for this po_line_id + shipment_num combination (ie no multiple releases) */
301
302 /* Bug 1830177. The following if statements should include the condition
303 * po_line_id not null
304 */
305 IF (X_po_release_id_record.po_line_id is not null) THEN
306
307 IF X_po_release_id_record.shipment_num IS NOT NULL AND
308 X_po_release_id_record.release_num IS NULL THEN
309
310 select count(*) into v_count
311 from po_line_locations pll
312 where
313 pll.po_line_id = X_po_release_id_record.po_line_id and
314 pll.shipment_num = X_po_release_id_record.shipment_num;
315
316 IF v_count = 1 THEN
317
318 select pll.po_release_id, pll.line_location_id
319 into X_po_release_id_record.po_release_id ,
320 X_po_release_id_record.po_line_location_id
321 from po_line_locations pll
322 where
323 pll.po_line_id = X_po_release_id_record.po_line_id and
324 pll.shipment_num = X_po_release_id_record.shipment_num;
325
326 ELSE
327
328 x_po_release_id_record.po_line_location_id := NULL;
329 x_po_release_id_record.po_release_id := NULL;
330 x_po_release_id_record.error_record.error_status := 'F';
331
332 -- Bug 4881909 : Returning proper error_message in case of exception.
333 x_po_release_id_record.error_record.error_message := 'RCV_ROI_INVALID_PO_SHIP_NUM';
334
335 END IF;
336
337 END IF;
338
339 /* If po_line_id is not null and release_num, shipment_num is null then
340 we can find the po_line_location_id, po_release_id, shipment_num if there is only
341 one record for this po_line_id (ie no multiple shipments/releases) */
342
343 IF X_po_release_id_record.shipment_num IS NULL AND
344 X_po_release_id_record.release_num IS NULL THEN
345
346 select count(*) into v_count
347 from po_line_locations pll
348 where
349 pll.po_line_id = X_po_release_id_record.po_line_id
350 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
351 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
352 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
353 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED');
354
355 IF v_count = 1 THEN
356
357 select pll.po_release_id, pll.line_location_id, pll.shipment_num
358 into X_po_release_id_record.po_release_id ,
359 X_po_release_id_record.po_line_location_id,
360 X_po_release_id_record.shipment_num
361 from po_line_locations pll
362 where
363 pll.po_line_id = X_po_release_id_record.po_line_id
364 and NVL(pll.APPROVED_FLAG,'N') = 'Y' -- bug 610238 should include the same clause as above
365 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
366 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
367 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED');
368
369 ELSE
370
371 x_po_release_id_record.po_line_location_id := NULL;
372 x_po_release_id_record.po_release_id := NULL;
373 x_po_release_id_record.error_record.error_status := 'S';
374 x_po_release_id_record.error_record.error_message := NULL;
375
376 END IF;
377
378 END IF;
379 END IF;
380
381 exception
382 when others then
383 x_po_release_id_record.error_record.error_status := 'U';
384
385 END get_po_release_id;
386
387 /*===========================================================================
388
389 PROCEDURE NAME: val_release_date
390
391 ===========================================================================*/
392
393 PROCEDURE val_release_date
394 (X_po_header_id IN NUMBER,
395 X_release_date IN DATE,
396 X_valid_release_date_flag IN OUT NOCOPY VARCHAR2) IS
397
398 X_progress varchar2(3) := '';
399
400
401 -- <Cursor modified Action Date TZ FPJ>
402 CURSOR C is
403 SELECT 'Y'
404 FROM PO_HEADERS POH
405 WHERE POH.po_header_id = X_po_header_id
406 AND TRUNC(X_release_date) BETWEEN
407 TRUNC(nvl(POH.start_date, X_release_date))
408 AND
409 TRUNC(nvl(POH.end_date, X_release_date));
410
411 BEGIN
412
413 --dbms_output.put_line('Before open cursor');
414
415 if (X_po_header_id is not null) then
416
417 OPEN C;
418 X_progress := '020';
419
420 FETCH C into X_valid_release_date_flag;
421
422 CLOSE C;
423
424 else
425 X_progress := '030';
426 po_message_s.sql_error('val_release_date', X_progress, sqlcode);
427
428 end if;
429
430 EXCEPTION
431 when others then
432 --dbms_output.put_line('In exception');
433 po_message_s.sql_error('val_release_date', X_progress, sqlcode);
434 raise;
435 END val_release_date;
436
437 /*===========================================================================
438
439 FUNCTION NAME: val_doc_num_unique
440
441 ===========================================================================*/
442 FUNCTION val_doc_num_unique
443 (X_po_header_id IN NUMBER,
444 X_release_num IN NUMBER,
445 X_rowid IN VARCHAR2)
446 RETURN BOOLEAN IS
447
448 X_progress VARCHAR2(3) := '';
449 X_release_num_unqiue VARCHAR2(1) := 'Y';
450
451 /*
452 ** Check to see if the release number for the
453 ** po header id exists in the database.
454 */
455 CURSOR C is
456 SELECT 'N'
457 FROM PO_RELEASES POR
458 WHERE POR.po_header_id = X_po_header_id
459 AND POR.release_num = X_release_num
460 AND (X_rowid is null OR
461 X_rowid <> POR.rowid);
462
463 BEGIN
464
465 --dbms_output.put_line('Before open cursor');
466
467 IF (X_po_header_id is not null AND X_release_num is not null) THEN
468
469 OPEN C;
470 X_progress := '020';
471
472 FETCH C into X_release_num_unqiue;
473
474 CLOSE C;
475
476 /*
477 ** If the release number does exist, then the release
478 ** number is not unique and we should return false
479 */
480 IF (X_release_num_unqiue = 'Y') THEN
481 return(TRUE);
482 ELSE
483 return(FALSE);
484 END IF;
485
486 ELSE
487 X_progress := '030';
488 po_message_s.sql_error('val_doc_num_unique', X_progress, sqlcode);
489
490 END if;
491
492
493 EXCEPTION
494 when others then
495 --dbms_output.put_line('In exception');
496 po_message_s.sql_error('val_doc_num_unique', X_progress, sqlcode);
497 raise;
498 END val_doc_num_unique;
499
500
501 /*===========================================================================
502
503 FUNCTION NAME: val_approval_status
504
505
506 ===========================================================================*/
507 FUNCTION val_approval_status
508 (X_po_release_id IN NUMBER,
509 X_release_num IN NUMBER,
510 X_agent_id IN NUMBER,
511 X_release_date IN DATE,
512 X_acceptance_required_flag IN VARCHAR2,
513 X_acceptance_due_date IN VARCHAR2,
514 p_shipping_control IN VARCHAR2
515 -- <INBOUND LOGISTICS FPJ>
516 ) RETURN BOOLEAN IS
517
518 X_progress VARCHAR2(3) := '';
519 X_approval_status_changed VARCHAR2(1) := 'N';
520
521 /*
522 ** Check to see if the if any of the follosing release header values
523 ** have changed.
524 */
525 CURSOR C is
526 SELECT 'Y'
527 FROM PO_RELEASES POR
528 WHERE POR.po_release_id = X_po_release_id
529 AND ( POR.release_num <> X_release_num
530 OR POR.agent_id <> X_agent_id
531 OR POR.release_date <> X_release_date
532 OR ((POR.acceptance_required_flag <> X_acceptance_required_flag)
533 OR (POR.acceptance_required_flag IS NULL
534 AND
535 X_acceptance_required_flag IS NOT NULL)
536 OR (POR.acceptance_required_flag IS NOT NULL
537 AND
538 X_acceptance_required_flag IS NULL))
539 OR ((POR.acceptance_due_date <> X_acceptance_due_date)
540 OR (POR.acceptance_due_date IS NULL
541 AND
542 X_acceptance_due_date IS NOT NULL)
543 OR (POR.acceptance_due_date IS NOT NULL
544 AND
545 X_acceptance_due_date IS NULL))
546 -- <INBOUND LOGISTICS FPJ START>
547 OR ((POR.shipping_control <> p_shipping_control)
548 OR (POR.shipping_control IS NULL
549 AND
550 p_shipping_control IS NOT NULL)
551 OR (POR.shipping_control IS NOT NULL
552 AND
553 p_shipping_control IS NULL))
554 -- <INBOUND LOGISTICS FPJ END>
555 );
556
557 BEGIN
558
559 --dbms_output.put_line('Before open cursor');
560
561 IF (X_po_release_id is not null) THEN
562
563 OPEN C;
564 X_progress := '020';
565
566 FETCH C into X_approval_status_changed;
567
568 CLOSE C;
569
570 END IF;
571
572 /*
573 ** If the approval status changed flag is Y, one of
574 ** the values on the release header has changed since it
575 ** was last saved to the database.
576 */
577 IF (X_approval_status_changed = 'Y') THEN
578 --dbms_output.put_line('status changed = Y');
579 return(FALSE);
580 ELSE
581 --dbms_output.put_line('status changed = N');
582 return(TRUE);
583 END IF;
584
585 EXCEPTION
586 WHEN OTHERS THEN
587 --dbms_output.put_line('In exception');
588 po_message_s.sql_error('val_approval_status', X_progress, sqlcode);
589 raise;
590 END val_approval_status;
591
592
593 END PO_RELEASES_SV4;
[Home] [Help]