[Home] [Help]
PACKAGE BODY: APPS.PO_RCO_VALIDATION_GRP
Source
1 PACKAGE BODY po_rco_validation_grp AS
2 /* $Header: POXGRCVB.pls 120.16.12020000.3 2013/02/10 23:18:14 vegajula ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_RCO_VALIDATION_GRP';
5 -- Read the profile option that enables/disables the debug log
6 -- Logging global constants
7 d_package_base CONSTANT VARCHAR2(100) := po_log.get_package_base(g_pkg_name);
8
9 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
10
11 -- Debugging
12 g_debug_stmt CONSTANT BOOLEAN := po_debug.is_debug_stmt_on;
13 g_debug_unexp CONSTANT BOOLEAN := po_debug.is_debug_unexp_on;
14
15 FUNCTION is_req_cancellable(p_req_hdr_id IN NUMBER) RETURN VARCHAR2
16 IS
17 CURSOR l_line_csr(hdr_id NUMBER) IS
18 SELECT requisition_line_id,
19 line_location_id
20 FROM po_requisition_lines_all
21 WHERE requisition_header_id = hdr_id;
22
23 l_line_id NUMBER;
24 l_line_location_id NUMBER;
25 l_line_with_po NUMBER := 0;
26 l_return_status VARCHAR2(1);
27 l_c VARCHAR2(1);
28 l_api_name VARCHAR2(50) := 'Is_Req_Cancellable';
29 l_oe_flag VARCHAR2(1);
30
31 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
32 l_progress VARCHAR2(3) := '000';
33
34
35
36 BEGIN
37
38 IF g_debug_stmt THEN
39 po_debug.debug_begin(l_log_head);
40 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
41 END IF;
42
43
44 OPEN l_line_csr(p_req_hdr_id);
45 LOOP
46 FETCH l_line_csr
47 INTO l_line_id,
48 l_line_location_id;
49
50 EXIT WHEN l_line_csr%notfound;
51
52 IF (l_line_location_id IS NOT NULL) THEN
53 l_line_with_po := l_line_with_po + 1;
54 END IF; --Bug6747949
55 l_c := is_reqline_cancellable(l_line_id);
56
57 IF g_debug_stmt THEN
58 po_debug.debug_var(l_log_head, l_progress, 'l_line_id', l_line_id);
59 po_debug.debug_var(l_log_head, l_progress, 'Is_ReqLine_Cancellable', l_c);
60 END IF;
61
62 IF(l_c = 'Y') THEN
63 RETURN 'Y';
64 END IF;
65 END LOOP;
66 CLOSE l_line_csr;
67
68 IF g_debug_stmt THEN
69 po_debug.debug_var(l_log_head, l_progress, 'returning not cancellable', l_line_with_po);
70 END IF;
71
72 return 'N';
73
74 EXCEPTION WHEN OTHERS THEN
75 IF g_debug_stmt THEN
76 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
77 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
78 END IF;
79 END IF;
80
81 RETURN 'N';
82 END is_req_cancellable;
83
84 FUNCTION is_req_changeable(p_req_hdr_id IN NUMBER) RETURN VARCHAR2
85 IS
86
87 CURSOR l_line_csr(hdr_id NUMBER) IS
88 SELECT requisition_line_id,
89 line_location_id
90 FROM po_requisition_lines_all
91 WHERE requisition_header_id = hdr_id;
92
93 l_line_id NUMBER;
94 l_line_location_id NUMBER;
95 l_line_with_po NUMBER := 0;
96 l_return_status VARCHAR2(1);
97 l_p VARCHAR2(1);
98 l_d VARCHAR2(1);
99 l_q VARCHAR2(1);
100 l_start_date VARCHAR2(1);
101 l_end_date VARCHAR2(1);
102 l_amount VARCHAR2(1);
103 l_c VARCHAR2(1);
104 l_api_name VARCHAR2(50) := 'Is_Req_Changeable';
105
106 BEGIN
107
108 OPEN l_line_csr(p_req_hdr_id);
109 LOOP
110 FETCH l_line_csr
111 INTO l_line_id,
112 l_line_location_id;
113 EXIT WHEN l_line_csr%notfound;
114
115 IF (l_line_location_id IS NOT NULL) THEN
116 l_line_with_po := l_line_with_po + 1;
117
118 is_req_line_changeable(
119 1.0,
120 l_return_status,
121 l_line_id,
122 l_p,
123 l_d,
124 l_q,
125 l_start_date,
126 l_end_date,
127 l_amount,
128 l_c);
129
130 IF(l_p = 'Y' OR l_d = 'Y' OR l_q = 'Y' OR l_c = 'Y' OR
131 l_start_date = 'Y' OR l_end_date = 'Y' OR l_amount = 'Y') THEN
132 RETURN 'Y';
133 END IF;
134 END IF;
135 END LOOP;
136 CLOSE l_line_csr;
137
138 -- if no line has po associated, the req is changeable
139 IF (l_line_with_po = 0) THEN
140 RETURN 'Y';
141 ELSE
142 RETURN 'N';
143 END IF;
144
145 EXCEPTION WHEN OTHERS THEN
146 IF g_debug_stmt THEN
147 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
148 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
149 END IF;
150
151 END IF;
152
153 RETURN 'N';
154 END is_req_changeable;
155
156 FUNCTION is_reqline_cancellable(p_req_line_id IN NUMBER) RETURN VARCHAR2
157 IS
158 l_return_status VARCHAR2(1);
159 BEGIN
160 po_rco_validation_pvt.is_req_line_cancellable(
161 1.0,
162 l_return_status,
163 p_req_line_id,
164 NULL);
165 IF(l_return_status = fnd_api.g_ret_sts_success) THEN
166 RETURN 'Y';
167 ELSE
168 RETURN 'N';
169 END IF;
170 END is_reqline_cancellable;
171
172
173 PROCEDURE calculate_disttax(p_api_version IN NUMBER,
174 x_return_status OUT NOCOPY VARCHAR2,
175 p_dist_id NUMBER,
176 p_price NUMBER,
177 p_quantity NUMBER,
178 p_dist_amount NUMBER,
179 p_rec_tax OUT NOCOPY NUMBER,
180 p_nonrec_tax OUT NOCOPY NUMBER)
181 IS
182 BEGIN
183 po_rco_validation_pvt.calculate_disttax(
184 p_api_version,
185 x_return_status,
186 p_dist_id,
187 p_price,
188 p_quantity,
189 p_dist_amount,
190 p_rec_tax,
191 p_nonrec_tax);
192 END calculate_disttax;
193
194
195 PROCEDURE is_req_line_cancellable(p_api_version IN NUMBER,
196 x_return_status OUT NOCOPY VARCHAR2,
197 p_req_line_id IN NUMBER,
198 p_flag IN VARCHAR2 DEFAULT NULL)
199 IS
200
201 BEGIN
202 po_rco_validation_pvt.is_req_line_cancellable(
203 p_api_version,
204 x_return_status,
205 p_req_line_id,
206 p_flag);
207
208
209 END is_req_line_cancellable;
210
211 PROCEDURE is_req_line_changeable(p_api_version IN NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2,
213 p_req_line_id IN NUMBER,
214 p_price_flag OUT NOCOPY VARCHAR2,
215 p_date_flag OUT NOCOPY VARCHAR2,
216 p_qty_flag OUT NOCOPY VARCHAR2,
217 p_start_date_flag OUT NOCOPY VARCHAR2,
218 p_end_date_flag OUT NOCOPY VARCHAR2,
219 p_amount_flag OUT NOCOPY VARCHAR2,
220 p_cancel_flag OUT NOCOPY VARCHAR2)
221 IS
222 BEGIN
223 po_rco_validation_pvt.is_req_line_changeable(
224 p_api_version,
225 x_return_status,
226 p_req_line_id,
227 p_price_flag,
228 p_date_flag,
229 p_qty_flag,
230 p_start_date_flag,
231 p_end_date_flag,
232 p_amount_flag,
233 p_cancel_flag);
234
235 END is_req_line_changeable;
236
237 PROCEDURE save_ireqchange(p_api_version IN NUMBER,
238 x_return_status OUT NOCOPY VARCHAR2,
239 p_req_hdr_id IN NUMBER,
240 p_change_table IN po_req_change_table,
241 p_cancel_table IN po_req_cancel_table,
242 p_change_request_group_id OUT NOCOPY NUMBER,
243 x_retmsg OUT NOCOPY VARCHAR2,
244 x_errtable OUT NOCOPY po_req_change_err_table)
245 IS
246
247
248 l_api_name CONSTANT VARCHAR(30) := 'Save_IReqChange';
249 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
250 l_progress VARCHAR2(3) := '000';
251
252
253 BEGIN
254
255
256 IF g_debug_stmt THEN
257 po_debug.debug_begin(l_log_head);
258 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
259 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
260 END IF;
261
262 po_rco_validation_pvt.save_ireqchange(
263 p_api_version,
264 x_return_status,
265 p_req_hdr_id,
266 p_change_table,
267 p_cancel_table,
268 p_change_request_group_id,
269 x_retmsg,
270 x_errtable);
271
272
273
274
275 IF g_debug_stmt THEN po_debug.debug_end(l_log_head); END IF;
276
277 END save_ireqchange;
278
279 PROCEDURE save_reqchange(p_api_version IN NUMBER,
280 x_return_status OUT NOCOPY VARCHAR2,
281 p_req_hdr_id IN NUMBER,
282 p_change_table IN po_req_change_table,
283 p_cancel_table IN po_req_cancel_table,
284 p_change_request_group_id OUT NOCOPY NUMBER,
285 x_retmsg OUT NOCOPY VARCHAR2,
286 x_errtable OUT NOCOPY po_req_change_err_table)
287 IS
288 BEGIN
289 po_rco_validation_pvt.save_reqchange(
290 p_api_version,
291 x_return_status,
292 p_req_hdr_id,
293 p_change_table,
294 p_cancel_table,
295 p_change_request_group_id,
296 x_retmsg,
297 x_errtable);
298 END save_reqchange;
299
300 PROCEDURE save_reqcancel(p_api_version IN NUMBER,
301 x_return_status OUT NOCOPY VARCHAR2,
302 p_req_hdr_id IN NUMBER,
303 p_cancel_table IN po_req_cancel_table,
304 p_change_request_group_id OUT NOCOPY NUMBER,
305 x_retmsg OUT NOCOPY VARCHAR2,
306 p_grp_id IN NUMBER DEFAULT NULL)
307 IS
308 BEGIN
309 po_rco_validation_pvt.save_reqcancel(
310 p_api_version,
311 x_return_status,
312 p_req_hdr_id,
313 p_cancel_table,
314 p_change_request_group_id,
315 x_retmsg,
316 p_grp_id);
317 END save_reqcancel;
318
319
320 PROCEDURE submit_reqchange(p_api_version IN NUMBER,
324 p_note_to_approver IN VARCHAR2,
321 x_return_status OUT NOCOPY VARCHAR2,
322 p_group_id IN NUMBER,
323 p_fundscheck_flag IN VARCHAR2,
325 p_initiator IN VARCHAR2,
326 x_retmsg OUT NOCOPY VARCHAR2,
327 x_errcode OUT NOCOPY VARCHAR2,
328 x_errtable OUT NOCOPY po_req_change_err_table )
329 IS
330 BEGIN
331 po_rco_validation_pvt.submit_reqchange(p_api_version,
332 x_return_status,
333 p_group_id,
334 p_fundscheck_flag,
335 p_note_to_approver,
336 p_initiator,
337 x_retmsg,
338 x_errcode,
339 x_errtable );
340 END submit_reqchange;
341
342 PROCEDURE submit_reqcancel (
343 p_api_version IN NUMBER,
344 x_return_status OUT NOCOPY VARCHAR2,
345 p_group_id IN NUMBER,
346 x_retmsg OUT NOCOPY VARCHAR2,
347 p_errtable OUT NOCOPY po_req_change_err_table,
348 p_flag IN VARCHAR2 DEFAULT NULL)
349 IS
350 BEGIN
351 po_rco_validation_pvt.submit_reqcancel (
352 p_api_version ,
353 x_return_status ,
354 p_group_id ,
355 x_retmsg ,
356 p_errtable ,
357 p_flag );
358 END submit_reqcancel;
359
360
361
362 PROCEDURE save_ireqcancel(p_api_version IN NUMBER,
363 x_return_status OUT NOCOPY VARCHAR2,
364 p_req_hdr_id IN NUMBER,
365 p_cancel_table IN po_req_cancel_table,
366 p_change_request_group_id OUT NOCOPY NUMBER,
367 x_retmsg OUT NOCOPY VARCHAR2,
368 p_grp_id IN NUMBER DEFAULT NULL)
369 IS
370
371 l_api_name CONSTANT VARCHAR(30) := 'Save_IReqCancel';
372 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
373 l_progress VARCHAR2(3) := '000';
374
375 BEGIN
376
377 IF g_debug_stmt THEN
378 po_debug.debug_begin(l_log_head);
379 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
380 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
381 END IF;
382
383
384 po_rco_validation_pvt.save_ireqcancel(
385 p_api_version,
386 x_return_status,
387 p_req_hdr_id,
388 p_cancel_table,
389 p_change_request_group_id,
390 x_retmsg,
391 p_grp_id);
392 IF g_debug_stmt THEN
393 po_debug.debug_end(l_log_head);
394 END IF;
395
396 END save_ireqcancel;
397
398
399 PROCEDURE submit_ireqchange(p_api_version IN NUMBER,
400 x_return_status OUT NOCOPY VARCHAR2,
401 p_group_id IN NUMBER,
402 p_fundscheck_flag IN VARCHAR2,
403 p_note_to_approver IN VARCHAR2,
404 p_initiator IN VARCHAR2,
405 x_retmsg OUT NOCOPY VARCHAR2,
406 x_errcode OUT NOCOPY VARCHAR2,
407 x_errtable OUT NOCOPY po_req_change_err_table )
408 IS
409 l_api_name CONSTANT VARCHAR(30) := 'Submit_IReqChange';
410 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
411 l_progress VARCHAR2(3) := '000';
412
413 BEGIN
414
415 IF g_debug_stmt THEN
416 po_debug.debug_begin(l_log_head);
417 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
418 po_debug.debug_var(l_log_head, l_progress, 'p_group_ID', p_group_id);
419 po_debug.debug_var(l_log_head, l_progress, 'p_fundscheck_flag', p_fundscheck_flag);
420 po_debug.debug_var(l_log_head, l_progress, 'p_note_to_approver', p_note_to_approver);
421 END IF;
422
423 po_rco_validation_pvt.submit_ireqchange(p_api_version,
424 x_return_status,
425 p_group_id,
426 p_fundscheck_flag,
427 p_note_to_approver,
428 p_initiator,
429 x_retmsg,
430 x_errcode,
431 x_errtable );
432
433 IF g_debug_stmt THEN
434 po_debug.debug_end(l_log_head);
435 END IF;
436
437
438 END submit_ireqchange;
439
440 PROCEDURE submit_ireqcancel (
441 p_api_version IN NUMBER,
442 x_return_status OUT NOCOPY VARCHAR2,
443 p_group_id IN NUMBER,
444 x_retmsg OUT NOCOPY VARCHAR2,
445 p_errtable OUT NOCOPY po_req_change_err_table,
446 p_flag IN VARCHAR2 DEFAULT NULL)
447 IS
448 l_api_name CONSTANT VARCHAR(30) := 'Submit_IReqChange';
449 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
450 l_progress VARCHAR2(3) := '000';
451
452 BEGIN
453
454 IF g_debug_stmt THEN
455 po_debug.debug_begin(l_log_head);
456 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
457 --PO_DEBUG.debug_var(l_log_head,l_progress,'p_req_hdr_id',p_req_hdr_id);
458 po_debug.debug_var(l_log_head, l_progress, 'p_group_ID', p_group_id);
459 po_debug.debug_var(l_log_head, l_progress, 'p_flag', p_flag);
460 END IF;
461
462
463 po_rco_validation_pvt.submit_ireqcancel (
464 p_api_version ,
465 x_return_status ,
466 p_group_id ,
467 x_retmsg ,
468 p_errtable ,
469 p_flag );
470
471 IF g_debug_stmt THEN
472 po_debug.debug_end(l_log_head);
473 END IF;
474 END submit_ireqcancel;
475
476
477
478 PROCEDURE is_on_complex_work_order(p_line_loc_id IN NUMBER,
479 x_return_status OUT NOCOPY VARCHAR2)
480 IS
481 BEGIN
482 po_rco_validation_pvt.is_on_complex_work_order(p_line_loc_id,
483 x_return_status);
484 END is_on_complex_work_order;
485
486 PROCEDURE is_complex_work_order(p_header_id IN NUMBER,
487 x_return_status OUT NOCOPY VARCHAR2)
488 IS
489 BEGIN
490 po_rco_validation_pvt.is_complex_work_order(p_header_id,
491 x_return_status);
492 END is_complex_work_order;
493
494 FUNCTION IS_ANY_LINE_WITHDRAWABLE(p_req_hdr_id in number) RETURN VARCHAR2
495 IS
496 l_line_to_withdraw_count NUMBER := 0;
497 l_api_name varchar2(50) := 'AllLINES_ChangableORCancelled';
498
499 BEGIN
500
501 SELECT Count(*)
502 INTO l_line_to_withdraw_count
503 FROM po_requisition_lines_all
504 WHERE REQUISITION_HEADER_ID = p_req_hdr_id
505 AND LINE_LOCATION_ID IS NULL
506 AND nvl(CANCEL_FLAG,'N') = 'N'
507 AND nvl(CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED');
508
509
510 IF ( l_line_to_withdraw_count > 0) THEN
511 return 'Y';
512 ELSE
513 return 'N';
514 END IF;
515
516 exception when others then
517 IF g_debug_unexp THEN
518 po_debug.debug_var(l_api_name, '001', 'sqlerrm=', sqlerrm);
519 END IF;
520 RETURN 'Y';
521
522 END IS_ANY_LINE_WITHDRAWABLE;
523
524
525
526 PROCEDURE get_preparer_name(
527 p_api_version IN NUMBER
528 , p_req_hdr_id IN NUMBER
529 , x_return_status OUT NOCOPY VARCHAR2
530 , x_preparer_name OUT NOCOPY VARCHAR2
531 )
532 IS
533
534 l_api_name CONSTANT VARCHAR2(30) := 'get_preparer_name';
535 l_api_version CONSTANT NUMBER := 1.0;
536 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
537 l_progress VARCHAR2(3) := '000';
538
539 BEGIN
540
541 IF g_debug_stmt THEN
542 po_debug.debug_begin(l_log_head);
543 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
544 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
545 END IF;
546
547 l_progress := '010';
548
549 -- Standard Start of API savepoint
550 SAVEPOINT get_preparer_name_sp;
551
552 l_progress := '020';
553
554 -- Standard call to check for call compatibility
555
556 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
557
558 RAISE fnd_api.g_exc_unexpected_error;
559 END IF;
560
561 l_progress := '030';
562
563
564 -- Initialize API return status to success
565
566 x_return_status := fnd_api.g_ret_sts_success;
567
568 l_progress := '050';
569
570 IF g_debug_stmt THEN
574 l_progress := '060';
571 po_debug.debug_stmt(l_log_head, l_progress,'Calling Private Procedure get_preparer_name');
572 END IF;
573
575
576
577 po_rco_validation_pvt.get_preparer_name(p_req_hdr_id => p_req_hdr_id
578 , x_preparer_name => x_preparer_name
579 , x_return_status => x_return_status);
580
581
582 IF g_debug_stmt THEN
583 po_debug.debug_stmt(l_log_head, l_progress,'After Private Procedure get_preparer_name');
584 END IF;
585
586 EXCEPTION
587
588 WHEN fnd_api.g_exc_error THEN
589 ROLLBACK TO get_preparer_name_sp;
590 x_return_status := fnd_api.g_ret_sts_error;
591
592 WHEN fnd_api.g_exc_unexpected_error THEN
593 ROLLBACK TO get_preparer_name_sp;
594 x_return_status := fnd_api.g_ret_sts_unexp_error;
595 WHEN OTHERS THEN
596 ROLLBACK TO get_preparer_name_sp;
597 x_return_status := fnd_api.g_ret_sts_unexp_error;
598 IF g_debug_stmt THEN
599 po_debug.debug_var(l_log_head, l_progress,'EXCEPTION: Location and SQL CODE is ', SQLCODE);
600 END IF;
601 END get_preparer_name;
602
603 /**
604 * Bug: 14227140 deprecated with overridden method.
605 * version change from 2.0 to 3.0
606 */
607 PROCEDURE update_reqcancel_from_so(
608 p_api_version IN NUMBER
609 , p_req_line_id_tbl IN Dbms_Sql.number_table
610 , p_req_can_qty_tbl IN Dbms_Sql.number_table
611 , p_req_can_all IN Boolean
612 , x_return_status OUT NOCOPY VARCHAR2
613 )
614 IS
615
616 l_api_name CONSTANT VARCHAR2(30) := 'update_ReqCancel_from_SO';
617 l_api_version CONSTANT NUMBER := 3.0;
618 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
619 l_progress VARCHAR2(3) := '000';
620 p_req_hdr_id NUMBER :=0;
621 p_qty NUMBER;
622 BEGIN
623
624 IF g_debug_stmt THEN
625 po_debug.debug_begin(l_log_head);
626 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
627 END IF;
628
629 l_progress := '010';
630
631 -- Standard call to check for call compatibility
632
633 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
634 RAISE fnd_api.g_exc_unexpected_error;
635 END IF;
636
637 END update_reqcancel_from_so;
638
639
640 /**
641 * Bug: 14227140 deprecated with overridden method.
642 * version change from 2.0 to 3.0
643 */
644 PROCEDURE update_reqcancel_from_so(
645 p_api_version IN NUMBER
646 , x_return_status OUT NOCOPY VARCHAR2
647 , p_req_line_id_tbl IN Dbms_Sql.number_table --this needs to be table of number
648 )
649 IS
650
651 l_api_name CONSTANT VARCHAR2(30) := 'update_ReqCancel_from_SO';
652 l_api_version CONSTANT NUMBER := 3.0;
653 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
654 l_progress VARCHAR2(3) := '000';
655 p_req_hdr_id NUMBER :=0;
656
657 BEGIN
658
659 IF g_debug_stmt THEN
660 po_debug.debug_begin(l_log_head);
661 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
662 END IF;
663
664 l_progress := '010';
665
666 -- Standard Start of API savepoint
667 SAVEPOINT update_reqcancel_from_so_sp;
668
669 l_progress := '020';
670
671 -- Standard call to check for call compatibility
672
673 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
674 RAISE fnd_api.g_exc_unexpected_error;
675 END IF;
676
677 l_progress := '030';
678
679
680
681 IF g_debug_stmt THEN
682 po_debug.debug_stmt(l_log_head, l_progress,'After Private Procedure update_ReqCancel_from_SO');
683 END IF;
684
685 EXCEPTION
686
687 WHEN fnd_api.g_exc_error THEN
688 ROLLBACK TO update_reqcancel_from_so_sp;
689 x_return_status := fnd_api.g_ret_sts_error;
690
691 WHEN fnd_api.g_exc_unexpected_error THEN
692 ROLLBACK TO update_reqcancel_from_so_sp;
693 x_return_status := fnd_api.g_ret_sts_unexp_error;
694 WHEN OTHERS THEN
695 ROLLBACK TO update_reqcancel_from_so_sp;
696 x_return_status := fnd_api.g_ret_sts_unexp_error;
697 IF g_debug_stmt THEN
698 po_debug.debug_var(l_log_head, l_progress,'EXCEPTION: Location and SQL CODE is ', SQLCODE);
699 END IF;
700 END update_reqcancel_from_so;
701
702
703 /**
704 * Bug: 14227140 deprecated with overridden method.
705 * version change from 1.0 to 2.0
706 */
707
708 PROCEDURE update_reqchange_from_so(
709 p_api_version IN NUMBER
710 , x_return_status OUT NOCOPY VARCHAR2
711 , p_req_line_id IN NUMBER
712 , p_delta_quantity IN NUMBER
713 , p_new_need_by_date IN DATE
714 )
715 IS
716
717 l_api_name CONSTANT VARCHAR2(30) := 'update_ReqChange_from_SO';
718 l_api_version CONSTANT NUMBER := 2.0;
719 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
720 l_progress VARCHAR2(3) := '000';
721
722 BEGIN
723
724 IF g_debug_stmt THEN
725 po_debug.debug_begin(l_log_head);
726 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
727 /* PO_DEBUG.debug_var(l_log_head,l_progress,'p_commit',p_commit);
728 PO_DEBUG.debug_var(l_log_head,l_progress,'p_init_msg_list',p_init_msg_list);
729 PO_DEBUG.debug_var(l_log_head,l_progress,'p_validation_level',p_validation_level);
730 */ po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
731 po_debug.debug_var(l_log_head, l_progress, 'p_delta_quantity', p_delta_quantity);
732 po_debug.debug_var(l_log_head, l_progress, 'p_new_need_by_date', p_new_need_by_date);
733 END IF;
734
735 l_progress := '010';
736
737 -- Standard call to check for call compatibility
738
739 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
740
741 RAISE fnd_api.g_exc_unexpected_error;
742 END IF;
743
744 END update_reqchange_from_so;
745
746
747
748
749
750 -- 14227140 changes starts
751 /**
752 * Procedure to update the cancel qty in req line from SO
753 * This method is called when a SO initiated partial
754 * cancellation of Qty (Primary or Secondary) or cancellation of line.
755 *
756 * @param p_api_version of the procedure api
757 * @param p_req_line_id_tbl number table of canceled req lines
758 * @param p_req_can_prim_qty_tbl number table of canceled Prim Qty of req lines
759 * @param p_req_can_sec_qty_tbl number table of canceled Secondary Qty of req lines
760 * @param p_req_can_all boolean to hole weather req line cancelation flag
761 * @param x_return_status returns the tstatus of the api.
762 */
763 PROCEDURE update_reqcancel_from_so(
764 p_api_version IN NUMBER
765 , p_req_line_id_tbl IN Dbms_Sql.number_table
766 , p_req_can_prim_qty_tbl IN Dbms_Sql.number_table
767 , p_req_can_sec_qty_tbl IN Dbms_Sql.number_table
768 , p_req_can_all IN Boolean
769 , x_return_status OUT NOCOPY VARCHAR2
770 )
771 IS
772
773 l_api_name CONSTANT VARCHAR2(30) := 'update_reqcancel_from_so';
774 l_api_version CONSTANT NUMBER := 4.0;
775 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
776 l_progress VARCHAR2(3) := '000';
777 p_req_hdr_id NUMBER :=0;
778 p_prim_qty NUMBER;
779 p_sec_qty NUMBER;
780 BEGIN
781
782 IF g_debug_stmt THEN
783 po_debug.debug_begin(l_log_head);
784 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
785 END IF;
786
787 l_progress := '010';
788
789 -- Standard Start of API savepoint
790 SAVEPOINT update_reqcancel_from_so_sp;
791
792 l_progress := '020';
793
794 -- Standard call to check for call compatibility
795
796 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
797 RAISE fnd_api.g_exc_unexpected_error;
798 END IF;
799
800 l_progress := '030';
801
802 IF (p_req_line_id_tbl IS NULL OR p_req_line_id_tbl.Count=0 ) THEN
803 IF g_debug_stmt THEN
804 po_debug.debug_stmt(l_log_head, l_progress,'Procedure update_ReqCancel_from_SO called with no req line id from OM');
805 END IF;
806 RAISE fnd_api.g_exc_unexpected_error;
807 END IF;
808
809 -- Initialize API return status to success
810 x_return_status := fnd_api.g_ret_sts_success;
811
812 l_progress := '050';
813
814 IF g_debug_stmt THEN
815 po_debug.debug_stmt(l_log_head, l_progress,'Calling Private Procedure update_ReqCancel_from_SO table count ='|| p_req_line_id_tbl.Count);
816 END IF;
817
818 l_progress := '060';
819
820 FOR i in 1.. p_req_line_id_tbl.COUNT LOOP
821
822 IF g_debug_stmt THEN
826 END IF;
823 po_debug.debug_var(l_log_head, l_progress,'p_req_line_id',p_req_line_id_tbl(i));
824 po_debug.debug_var(l_log_head, l_progress,'p_req_can_all',p_req_can_all);
825
827
828 if p_req_can_all then
829 p_prim_qty := 0;
830 p_sec_qty := 0;
831 else
832 p_prim_qty := p_req_can_prim_qty_tbl(i) ;
833 p_sec_qty := p_req_can_sec_qty_tbl(i) ;
834 end if;
835 IF g_debug_stmt THEN
836 po_debug.debug_var(l_log_head, l_progress,'p_req_line_id',p_req_line_id_tbl(i));
837 po_debug.debug_var(l_log_head, l_progress,'p_prim_qty',p_prim_qty);
838 po_debug.debug_var(l_log_head, l_progress,'p_sec_qty',p_sec_qty);
839 po_debug.debug_var(l_log_head, l_progress,'p_req_can_all',p_req_can_all);
840
841 END IF;
842
843
844
845 po_rco_validation_pvt.update_reqcancel_from_so(p_req_line_id => p_req_line_id_tbl(i)
846 , p_req_cancel_prim_qty => p_prim_qty
847 , p_req_cancel_sec_qty => p_sec_qty
848 , p_req_cancel_all => p_req_can_all
849 ,x_return_status => x_return_status);
850
851 END LOOP;
852
853
854 IF g_debug_stmt THEN
855 po_debug.debug_stmt(l_log_head, l_progress,'After Private Procedure update_ReqCancel_from_SO');
856 END IF;
857
858 EXCEPTION
859
860 WHEN fnd_api.g_exc_error THEN
861 ROLLBACK TO update_reqcancel_from_so_sp;
862 x_return_status := fnd_api.g_ret_sts_error;
863
864 WHEN fnd_api.g_exc_unexpected_error THEN
865 ROLLBACK TO update_reqcancel_from_so_sp;
866 x_return_status := fnd_api.g_ret_sts_unexp_error;
867 WHEN OTHERS THEN
868 ROLLBACK TO update_reqcancel_from_so_sp;
869 x_return_status := fnd_api.g_ret_sts_unexp_error;
870 IF g_debug_stmt THEN
871 po_debug.debug_var(l_log_head, l_progress,'EXCEPTION: Location and SQL CODE is ', SQLCODE);
872 END IF;
873 END update_reqcancel_from_so;
874
875
876
877
878
879 /**
880 * Procedure to update the Qty changes on req line from SO changes
881 * This method is called when a SO initiated change in Qty (Primary or Secondary).
882 *
883 * @param p_api_version of the procedure api
884 * @param x_return_status returns the tstatus of the api.
885
886 * @param p_req_line_id number holds the req line number
887 * @param p_delta_quantity_prim number changed Prim Qty of SO
888 * @param p_delta_quantity_sec number changed Secondary Qty of SO
889 * @param p_new_need_by_date date need by date of SO.
890 */
891
892 PROCEDURE update_reqchange_from_so(
893 p_api_version IN NUMBER
894 , x_return_status OUT NOCOPY VARCHAR2
895 , p_req_line_id IN NUMBER
896 , p_delta_quantity_prim IN NUMBER
897 , p_delta_quantity_sec IN NUMBER
898 , p_new_need_by_date IN DATE
899 )
900 IS
901
902 l_api_name CONSTANT VARCHAR2(30) := 'update_reqchange_from_so';
903 l_api_version CONSTANT NUMBER := 3.0;
904 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
905 l_progress VARCHAR2(3) := '000';
906
907 BEGIN
908
909 IF g_debug_stmt THEN
910 po_debug.debug_begin(l_log_head);
911 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
912 po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
913 po_debug.debug_var(l_log_head, l_progress, 'p_delta_quantity_prim', p_delta_quantity_prim);
914 po_debug.debug_var(l_log_head, l_progress, 'p_delta_quantity_sec', p_delta_quantity_sec);
915 po_debug.debug_var(l_log_head, l_progress, 'p_new_need_by_date', p_new_need_by_date);
916 END IF;
917
918 l_progress := '010';
919
920 -- Standard Start of API savepoint
921
922 SAVEPOINT update_reqchange_from_so_sp;
923
924 l_progress := '020';
925
926 -- Standard call to check for call compatibility
927
928 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
929
930 RAISE fnd_api.g_exc_unexpected_error;
931 END IF;
932
933 l_progress := '040';
934
935 -- Initialize API return status to success
936
937 x_return_status := fnd_api.g_ret_sts_success;
938
939 l_progress := '050';
940
941 IF g_debug_stmt THEN
942 po_debug.debug_stmt(l_log_head, l_progress,'Calling Private Procedure update_reqchange_from_so');
943 END IF;
944
945 l_progress := '060';
946
947
948 IF (p_req_line_id IS NOT NULL ) AND
949 (p_delta_quantity_prim IS NOT NULL OR p_delta_quantity_sec IS NOT NULL OR p_new_need_by_date IS NOT NULL ) THEN
950
951 po_rco_validation_pvt.update_reqchange_from_so(
952 p_req_line_id => p_req_line_id
953 , p_delta_quantity_prim => p_delta_quantity_prim
954 , p_delta_quantity_sec => p_delta_quantity_sec
955 , p_new_need_by_date => p_new_need_by_date
956 , x_return_status => x_return_status);
957
958 ELSE
959 RAISE fnd_api.g_exc_unexpected_error;
960 END IF;
961
962
963 IF g_debug_stmt THEN
964 po_debug.debug_stmt(l_log_head, l_progress,'After Private Procedure update_ReqChange_from_SO');
965 END IF;
966
967 EXCEPTION
968
969 WHEN fnd_api.g_exc_error THEN
970 ROLLBACK TO update_reqchange_from_so_sp;
971 x_return_status := fnd_api.g_ret_sts_error;
972
973 WHEN fnd_api.g_exc_unexpected_error THEN
974 ROLLBACK TO update_reqchange_from_so_sp;
975 x_return_status := fnd_api.g_ret_sts_unexp_error;
976 WHEN OTHERS THEN
977 ROLLBACK TO update_reqchange_from_so_sp;
978 x_return_status := fnd_api.g_ret_sts_unexp_error;
979 IF g_debug_stmt THEN
980 po_debug.debug_var(l_log_head, l_progress,'EXCEPTION: Location and SQL CODE is ', SQLCODE);
981 END IF;
982
983
984 END update_reqchange_from_so;
985
986 -- 14227140 changes ends
987
988
989 END po_rco_validation_grp;