[Home] [Help]
PACKAGE BODY: APPS.PO_RCO_VALIDATION_PVT
Source
1 PACKAGE BODY po_rco_validation_pvt AS
2 /* $Header: POXVRCVB.pls 120.15.12010000.12 2009/02/19 13:43:45 rojain ship $ */
3 --g_pkg_name CONSTANT VARCHAR2(30) := 'PO_RCO_VALIDATION_PVT';
4 -- Read the profile option that enables/disables the debug log
5 -- Logging global constants
6 d_package_base CONSTANT VARCHAR2(100) := po_log.get_package_base(g_pkg_name);
7
8 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
9
10 -- Debugging
11 g_debug_stmt CONSTANT BOOLEAN := po_debug.is_debug_stmt_on;
12 g_debug_unexp CONSTANT BOOLEAN := po_debug.is_debug_unexp_on;
13
14 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
15 g_fnd_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'), 'N');
16
17
18 -- Initializing Private Functions/Procedures
19 TYPE number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
20
21 PROCEDURE Insert_PriceBreakRows(p_chn_grp_id IN NUMBER);
22
23 PROCEDURE Insert_LineQuantityOrAmount(p_chn_grp_id IN NUMBER);
24
25 PROCEDURE Validate_Quantity(p_header_id IN NUMBER,
26 p_release_id IN NUMBER,
27 p_po_change_table IN pos_chg_rec_tbl,
28 p_errortable IN OUT NOCOPY po_req_change_err_table,
29 p_error_index IN OUT NOCOPY NUMBER,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_return_msg OUT NOCOPY VARCHAR2);
32
33 PROCEDURE Decode_poerror(p_header_id IN NUMBER,
34 p_release_id IN NUMBER,
35 p_err_po_msg IN VARCHAR2,
36 p_doc_check_rec_type IN doc_check_return_type,
37 p_po_error_index IN NUMBER,
38 p_errortable IN OUT NOCOPY po_req_change_err_table,
39 p_error_index IN OUT NOCOPY NUMBER,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_return_msg OUT NOCOPY VARCHAR2);
42
43 PROCEDURE insert_reqchange(p_change_table change_tbl_type,
44 p_chn_req_grp_id NUMBER);
45
46 FUNCTION calculate_newunitprice(p_req_line_id NUMBER, p_new_price NUMBER) RETURN NUMBER;
47
48 PROCEDURE validate_changes(p_req_hdr_id IN NUMBER,
49 p_req_change_table IN OUT NOCOPY change_tbl_type,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_retmsg OUT NOCOPY VARCHAR,
52 p_errortable IN OUT NOCOPY po_req_change_err_table);
53
54
55 PROCEDURE update_recordswithtax(p_chn_req_grp_id NUMBER);
56
57 PROCEDURE update_internalrecordswithtax(p_chn_req_grp_id NUMBER);
58
59 PROCEDURE copy_change(p_header_id NUMBER,
60 p_release_id NUMBER,
61 p_req_hdr_id NUMBER,
62 req_change_record_tbl IN OUT NOCOPY change_tbl_type,
63 req_index IN NUMBER,
64 po_index IN NUMBER,
65 po_change_record_tbl IN OUT NOCOPY pos_chg_rec_tbl);
66
67 PROCEDURE insert_pricebreakrows(p_chn_grp_id IN NUMBER)
68 IS
69 l_api_name VARCHAR2(50) := 'Insert_PriceBreakRows';
70 l_progress VARCHAR2(5) := '000';
71
72 -- added for retroactive pricing checks
73 l_retropricing VARCHAR2(20) := '';
74 l_quantity_received NUMBER;
75 l_accrue_on_receipt_flag po_line_locations_all.accrue_on_receipt_flag%TYPE;
76 l_quantity_billed NUMBER;
77 l_call_price_break BOOLEAN := TRUE;
78
79 l_req_line_id NUMBER;
80 l_old_date DATE;
81 l_new_date DATE;
82 l_old_qty NUMBER;
83 l_new_qty NUMBER;
84 l_new_po_qty NUMBER;
85 l_old_price NUMBER;
86 l_old_curr_price NUMBER;
87 l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;
88 l_req_user_id NUMBER;
89 l_document_header_id NUMBER;
90 l_document_num po_change_requests.document_num%TYPE;
91 l_document_revision_num NUMBER;
92 l_document_line_number NUMBER;
93 l_requester_id NUMBER;
94
95 l_source_doc_header_id NUMBER;
96 l_source_doc_line_num NUMBER;
97 l_deliver_to_loc_id NUMBER;
98 l_destination_org_id NUMBER;
99 l_req_currency_code po_requisition_lines_all.currency_code%TYPE;
100 l_req_rate_type po_requisition_lines_all.rate_type%TYPE;
101 l_org_id NUMBER;
102 l_creation_date DATE;
103 l_supplier_id NUMBER;
104 l_supplier_site_id NUMBER;
105 l_order_header_id NUMBER;
106 l_order_line_id NUMBER;
107 l_line_type_id NUMBER;
108 l_item_revision po_requisition_lines_all.item_revision%TYPE;
109 l_item_id NUMBER;
110 l_category_id NUMBER;
111 l_supplier_item_num po_requisition_lines_all.supplier_ref_number%TYPE;
112 l_in_price NUMBER;
113
114 -- output values
115 l_new_base_unit_price NUMBER;
116 l_new_price NUMBER;
117 l_new_curr_price NUMBER;
118 l_discount NUMBER;
119 l_currency_code po_requisition_lines_all.currency_code%TYPE;
120 l_rate_type po_requisition_lines_all.rate_type%TYPE;
121 l_rate_date DATE;
122 l_rate NUMBER;
123 l_price_break_id NUMBER;
124
125 CURSOR l_linepricebreak_csr(grp_id NUMBER) IS
126 SELECT
127 DISTINCT
128 pcr.document_header_id,
129 pcr.document_num,
130 pcr.document_revision_num,
131 pcr.document_line_id,
132 pcr.document_line_number,
133 pcr.requester_id
134 FROM po_change_requests pcr,
135 po_requisition_lines_all prla
136 WHERE pcr.change_request_group_id = grp_id
137 AND pcr.action_type = 'MODIFICATION'
138 AND prla.requisition_line_id = pcr.document_line_id
139 AND prla.blanket_po_header_id IS NOT NULL;
140
141 BEGIN
142 l_retropricing := fnd_profile.value('PO_ALLOW_RETROPRICING_OF_PO');
143
144 l_req_user_id := fnd_global.user_id;
145
146 OPEN l_linepricebreak_csr(p_chn_grp_id);
147 LOOP
148 FETCH l_linepricebreak_csr
149 INTO
150 l_document_header_id,
151 l_document_num,
152 l_document_revision_num,
153 l_req_line_id,
154 l_document_line_number,
155 l_requester_id;
156
157 EXIT WHEN l_linepricebreak_csr%notfound;
158 l_progress := '001';
159
160 SELECT
161 prla.need_by_date,
162 prla.unit_meas_lookup_code,
163 prla.unit_price,
164 prla.currency_unit_price,
165 prla.blanket_po_header_id,
166 prla.blanket_po_line_num,
167 prla.deliver_to_location_id,
168 prla.destination_organization_id,
169 prla.currency_code,
170 prla.rate_type,
171 prla.org_id,
172 prla.vendor_id,
173 prla.vendor_site_id,
174 prla.creation_date,
175 plla.po_header_id,
176 plla.po_line_id,
177 prla.line_type_id,
178 prla.item_revision,
179 prla.item_id,
180 prla.category_id,
181 prla.supplier_ref_number,
182 prla.unit_price,
183 nvl(plla.quantity_received, 0),
184 nvl(plla.accrue_on_receipt_flag, 'N'),
185 nvl(plla.quantity_billed, 0)
186 INTO
187 l_old_date,
188 l_req_uom,
189 l_old_price,
190 l_old_curr_price,
191 l_source_doc_header_id,
192 l_source_doc_line_num,
193 l_deliver_to_loc_id,
194 l_destination_org_id,
195 l_req_currency_code,
196 l_req_rate_type,
197 l_org_id,
198 l_supplier_id,
199 l_supplier_site_id,
200 l_creation_date,
201 l_order_header_id,
202 l_order_line_id,
203 l_line_type_id,
204 l_item_revision,
205 l_item_id,
206 l_category_id,
207 l_supplier_item_num,
208 l_in_price,
209 l_quantity_received,
210 l_accrue_on_receipt_flag,
211 l_quantity_billed
212 FROM
213 po_requisition_lines_all prla,
214 po_line_locations_all plla
215 WHERE prla.requisition_line_id = l_req_line_id
216 AND prla.line_location_id = plla.line_location_id;
217
218 BEGIN
219 SELECT new_need_by_date
220 INTO l_new_date
221 FROM po_change_requests
222 WHERE new_need_by_date IS NOT NULL
223 AND change_request_group_id = p_chn_grp_id
224 AND document_line_id = l_req_line_id;
225 EXCEPTION WHEN OTHERS THEN
226 l_new_date := l_old_date;
227 END;
228
229 l_progress := '002';
230
231 SELECT nvl(SUM(new_quantity), 0)
232 INTO l_new_qty
233 FROM po_change_requests
234 WHERE new_quantity IS NOT NULL
235 AND change_request_group_id = p_chn_grp_id
236 AND document_line_id = l_req_line_id
237 AND action_type = 'MODIFICATION'
238 AND request_level = 'DISTRIBUTION';
239
240 SELECT nvl(SUM(req_line_quantity), 0)
241 INTO l_old_qty
242 FROM po_req_distributions_all
243 WHERE requisition_line_id = l_req_line_id
244 AND distribution_id NOT IN(SELECT document_distribution_id
245 FROM po_change_requests
246 WHERE new_quantity IS NOT NULL
247 AND change_request_group_id = p_chn_grp_id
248 AND document_line_id = l_req_line_id
249 AND action_type = 'MODIFICATION'
250 AND request_level = 'DISTRIBUTION');
251
252 l_new_qty := l_new_qty + l_old_qty;
253
254 l_progress := '003';
255
256 IF (l_retropricing = 'ALL_RELEASES') THEN
257 l_call_price_break := TRUE;
258 ELSE
259 IF ((l_quantity_received > 0 AND
260 l_accrue_on_receipt_flag = 'Y') OR
261 (l_quantity_billed > 0)) THEN
262 l_call_price_break := FALSE;
263 END IF;
264 END IF;
265
266
267 IF (l_call_price_break) THEN
268
269 po_price_break_grp.get_price_break (
270 p_source_document_header_id => l_source_doc_header_id,
271 p_source_document_line_num => l_source_doc_line_num,
272 p_in_quantity => l_new_qty,
273 p_unit_of_measure => l_req_uom,
274 p_deliver_to_location_id => l_deliver_to_loc_id,
275 p_required_currency => l_req_currency_code,
276 p_required_rate_type => l_req_rate_type,
277 p_need_by_date => l_new_date,
278 p_destination_org_id => l_destination_org_id,
279 p_org_id => l_org_id,
280 p_supplier_id => l_supplier_id,
281 p_supplier_site_id => l_supplier_site_id,
282 p_creation_date => l_creation_date,
283 p_order_header_id => l_order_header_id,
284 p_order_line_id => l_order_line_id,
285 p_line_type_id => l_line_type_id,
286 p_item_revision => l_item_revision,
287 p_item_id => l_item_id,
288 p_category_id => l_category_id,
289 p_supplier_item_num => l_supplier_item_num,
290 p_in_price => l_in_price,
291 --Below is OUTPUT
292 x_base_unit_price => l_new_base_unit_price,
293 x_base_price => l_new_price,
294 x_currency_price => l_new_curr_price,
295 x_discount => l_discount,
296 x_currency_code => l_currency_code,
297 x_rate_type => l_rate_type,
298 x_rate_date => l_rate_date,
299 x_rate => l_rate,
300 x_price_break_id => l_price_break_id);
301
302 IF (g_fnd_debug = 'Y') THEN
303 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
304 fnd_log.string(fnd_log.level_statement, g_module_prefix ||
305 l_api_name, 'New Base Unit Price:' || to_char(l_new_base_unit_price) || ' New Price:' || to_char(l_new_price) || ' New Cur Unit Price:' || to_char(l_new_curr_price));
306 END IF;
307 END IF;
308
309 IF(l_new_price <> l_old_price) THEN
310 l_progress := '004';
311 IF(l_old_curr_price IS NULL) THEN
312 l_new_curr_price := NULL;
313 END IF;
314 INSERT INTO po_change_requests
315 (
316 change_request_group_id,
317 change_request_id,
318 initiator,
319 action_type,
320 request_level,
321 request_status,
322 document_type,
323 document_header_id,
324 document_num,
325 document_revision_num,
326 created_by,
327 creation_date,
328 document_line_id,
329 document_line_number,
330 old_price,
331 new_price,
332 old_currency_unit_price,
333 new_currency_unit_price,
334 last_updated_by,
335 last_update_date,
336 last_update_login,
337 requester_id,
338 change_active_flag)
339 VALUES
340 (
341 p_chn_grp_id,
342 po_chg_request_seq.nextval,
343 'REQUESTER',
344 'DERIVED',
345 'LINE',
346 'SYSTEMSAVE',
347 'REQ',
348 l_document_header_id,
349 l_document_num,
350 l_document_revision_num,
351 l_req_user_id,
352 SYSDATE,
353 l_req_line_id,
354 l_document_line_number,
355 l_old_price,
356 l_new_price,
357 l_old_curr_price,
358 l_new_curr_price,
359 l_req_user_id,
360 SYSDATE,
361 l_req_user_id,
362 l_requester_id,
363 'Y'
364 );
365
366 END IF;
367
368 END IF; -- if for l_call_price_break check
369
370 END LOOP;
371 CLOSE l_linepricebreak_csr;
372
373 EXCEPTION WHEN OTHERS THEN
374 IF g_fnd_debug = 'Y' THEN
375 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
376 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
377 l_api_name || '.others_exception', l_progress || ':' || SQLERRM);
378 END IF;
379 END IF;
380 RAISE;
381
382 END insert_pricebreakrows;
383
384
385 -- Inserts Derived Row for Line level Quantity or Amount changes
386
387 PROCEDURE insert_linequantityoramount(p_chn_grp_id IN NUMBER)
388 IS
389 l_api_name VARCHAR2(50) := 'Insert_LineQuantityOrAmount';
390 l_progress VARCHAR2(5) := '000';
391 l_line_id NUMBER;
392 l_line_num NUMBER;
393 l_id NUMBER;
394 l_old_quantity NUMBER;
395 l_new_quantity NUMBER;
396 l_req_user_id NUMBER;
397 l_req_header_id NUMBER;
398 l_req_num po_requisition_headers_all.segment1%TYPE;
399 l_requester_id NUMBER;
400 l_matching_basis po_requisition_lines_all.matching_basis%TYPE;
401 l_old_amount NUMBER;
402 l_new_amount NUMBER;
403 l_old_cur_amount NUMBER;
404 l_new_cur_amount NUMBER;
405
406 CURSOR l_line_csr(grp_id NUMBER) IS
407 SELECT DISTINCT
408 document_header_id,
409 document_num,
410 document_line_id,
411 document_line_number,
412 requester_id
413 FROM po_change_requests
414 WHERE action_type = 'MODIFICATION'
415 AND change_request_group_id = grp_id;
416
417 CURSOR l_line_qty_chn_csr(line_id NUMBER, grp_id NUMBER) IS
418 SELECT change_request_id
419 FROM po_change_requests
420 WHERE document_line_id = line_id
421 AND change_request_group_id = grp_id
422 AND new_quantity IS NOT NULL
423 AND request_level = 'DISTRIBUTION';
424
425 CURSOR l_line_amt_chn_csr(line_id NUMBER, grp_id NUMBER) IS
426 SELECT change_request_id
427 FROM po_change_requests
428 WHERE document_line_id = line_id
429 AND change_request_group_id = grp_id
430 AND new_amount IS NOT NULL
431 AND request_level = 'DISTRIBUTION';
432
433 BEGIN
434 l_req_user_id := fnd_global.user_id;
435
436 OPEN l_line_csr(p_chn_grp_id);
437 LOOP
438 FETCH l_line_csr INTO
439 l_req_header_id,
440 l_req_num,
441 l_line_id,
442 l_line_num,
443 l_requester_id;
444 EXIT WHEN l_line_csr%notfound;
445 l_progress := '001';
446
447 SELECT matching_basis, quantity, amount, currency_amount
448 INTO l_matching_basis, l_old_quantity, l_old_amount, l_old_cur_amount
449 FROM po_requisition_lines_all
450 WHERE requisition_line_id = l_line_id;
451
452 -- handle amount based lines
453 IF (l_matching_basis = 'AMOUNT') THEN
454 OPEN l_line_amt_chn_csr(l_line_id, p_chn_grp_id);
455 FETCH l_line_amt_chn_csr INTO l_id;
456 CLOSE l_line_amt_chn_csr;
457
458 l_progress := '002';
459
460 IF(l_id > 0) THEN
461
462 l_progress := '003';
463 SELECT SUM(amount)
464 INTO l_new_amount
465 FROM (
466 SELECT new_amount amount
467 FROM po_change_requests
468 WHERE change_request_group_id = p_chn_grp_id
469 AND document_line_id = l_line_id
470 AND new_amount IS NOT NULL
471 AND request_level = 'DISTRIBUTION'
472 UNION ALL
473 SELECT req_line_amount amount
474 FROM po_req_distributions_all
475 WHERE requisition_line_id = l_line_id
476 AND distribution_id NOT IN
477 (SELECT document_distribution_id
478 FROM po_change_requests
479 WHERE change_request_group_id = p_chn_grp_id
480 AND document_line_id = l_line_id
481 AND new_amount IS NOT NULL
482 AND request_level = 'DISTRIBUTION')
483 );
484
485 SELECT SUM(amount)
486 INTO l_new_cur_amount
487 FROM (
488 SELECT new_currency_amount amount
489 FROM po_change_requests
490 WHERE change_request_group_id = p_chn_grp_id
491 AND document_line_id = l_line_id
492 AND new_currency_amount IS NOT NULL
493 AND request_level = 'DISTRIBUTION'
494 UNION ALL
495 SELECT req_line_currency_amount amount
496 FROM po_req_distributions_all
497 WHERE requisition_line_id = l_line_id
498 AND distribution_id NOT IN
499 (SELECT document_distribution_id
500 FROM po_change_requests
501 WHERE change_request_group_id = p_chn_grp_id
502 AND document_line_id = l_line_id
503 AND new_currency_amount IS NOT NULL
504 AND request_level = 'DISTRIBUTION')
505 );
506
507 ELSE
508 l_new_amount := NULL;
509 l_new_cur_amount := NULL;
510 END IF;
511
512
513
514 ELSE -- handle quantity based lines
515 OPEN l_line_qty_chn_csr(l_line_id, p_chn_grp_id);
516 FETCH l_line_qty_chn_csr INTO l_id;
517 CLOSE l_line_qty_chn_csr;
518
519 l_progress := '002';
520
521 IF(l_id > 0) THEN
522
523 l_progress := '003';
524 SELECT SUM(quantity)
525 INTO l_new_quantity
526 FROM (
527 SELECT new_quantity quantity
528 FROM po_change_requests
529 WHERE change_request_group_id = p_chn_grp_id
530 AND document_line_id = l_line_id
531 AND new_quantity IS NOT NULL
532 AND request_level = 'DISTRIBUTION'
533 UNION ALL
534 SELECT req_line_quantity quantity
535 FROM po_req_distributions_all
536 WHERE requisition_line_id = l_line_id
537 AND distribution_id NOT IN
538 (SELECT document_distribution_id
539 FROM po_change_requests
540 WHERE change_request_group_id = p_chn_grp_id
541 AND document_line_id = l_line_id
542 AND new_quantity IS NOT NULL
543 AND request_level = 'DISTRIBUTION')
544 );
545 ELSE
546 l_new_quantity := NULL;
547 END IF;
548
549 END IF;
550
551 l_progress := '004';
552
553 INSERT INTO po_change_requests
554 (
555 change_request_group_id,
556 change_request_id,
557 initiator,
558 action_type,
559 request_level,
560 request_status,
561 document_type,
562 document_header_id,
563 document_num,
564 created_by,
565 creation_date,
566 document_line_id,
567 document_line_number,
568 old_quantity,
569 new_quantity,
570 old_amount,
571 new_amount,
572 old_currency_amount,
573 new_currency_amount,
574 last_updated_by,
575 last_update_date,
576 last_update_login,
577 requester_id,
578 change_active_flag)
579 VALUES
580 (
581 p_chn_grp_id,
582 po_chg_request_seq.nextval,
583 'REQUESTER',
584 'DERIVED',
585 'LINE',
586 'SYSTEMSAVE',
587 'REQ',
588 l_req_header_id,
589 l_req_num,
590 l_req_user_id,
591 SYSDATE,
592 l_line_id,
593 l_line_num,
594 l_old_quantity,
595 l_new_quantity,
596 l_old_amount,
597 l_new_amount,
598 l_old_cur_amount,
599 l_new_cur_amount,
600 l_req_user_id,
601 SYSDATE,
602 l_req_user_id,
603 l_requester_id,
604 'Y'
605 );
606
607 END LOOP;
608 CLOSE l_line_csr;
609 EXCEPTION WHEN OTHERS THEN
610 IF g_fnd_debug = 'Y' THEN
611 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
612 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
613 l_api_name || '.others_exception', l_progress || ':' || SQLERRM);
614 END IF;
615 END IF;
616 RAISE;
617
618 END insert_linequantityoramount;
619
620 PROCEDURE validate_quantity(p_header_id IN NUMBER,
621 p_release_id IN NUMBER,
622 p_po_change_table IN pos_chg_rec_tbl,
623 p_errortable IN OUT NOCOPY po_req_change_err_table,
624 p_error_index IN OUT NOCOPY NUMBER,
625 x_return_status OUT NOCOPY VARCHAR2,
626 x_return_msg OUT NOCOPY VARCHAR2)
627 IS
628 l_api_name VARCHAR2(50) := 'Validate_quantity';
629 l_err_req_line_id NUMBER;
630 l_err_req_dist_id NUMBER;
631 l_err_req_line_num NUMBER;
632 l_err_req_dist_num NUMBER;
633 l_err_po_msg VARCHAR2(2000);
634 l_err_billed_qty NUMBER;
635 l_err_new_qty NUMBER;
636 l_err_delivered_qty NUMBER;
637 l_qty_old_rec NUMBER;
638 l_qty_old_del NUMBER;
639 l_qty_old_bill NUMBER;
640 l_qty_new_rec NUMBER;
641 BEGIN
642 x_return_msg := 'VQ001';
643
644 FOR s IN 1..p_po_change_table.count
645 LOOP
646
647 IF(p_po_change_table(s).new_quantity IS NOT NULL) THEN
648
649 x_return_msg := 'VQ002';
650 /* SELECT
651 quantity_delivered,
652 quantity_billed
653 INTO
654 l_qty_old_del,
655 l_qty_old_bill
656 FROM po_distributions_all
657 WHERE po_distribution_id = p_po_change_table(s).document_distribution_id;
658 */
659 -- Code commented and new code added for bug 7138977
660 select
661 dist.quantity_delivered * Decode(line.ORDER_TYPE_LOOKUP_CODE,'AMOUNT', Nvl(dist.rate,1),1), dist.quantity_billed * Decode(line.ORDER_TYPE_LOOKUP_CODE,'AMOUNT', Nvl(dist.rate,1),1)
662 into
663 l_qty_old_del,
664 l_qty_old_bill
665 from po_distributions_all dist, po_lines_all line
666 where dist.po_line_id = line.po_line_id AND
667 dist.po_distribution_id = p_po_change_table(s).document_distribution_id;
668
669 IF (g_fnd_debug = 'Y') THEN
670 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
671 FND_LOG.string(FND_LOG.level_statement, g_module_prefix ||
672 l_api_name, 'quantity_delivered : '||l_qty_old_del||', quantity_billed : '||l_qty_old_bill||', new_qty : '||p_po_change_table(s).new_quantity);
673 END IF;
674 END IF;
675
676 IF(l_qty_old_del > p_po_change_table(s).new_quantity OR
677 l_qty_old_bill > p_po_change_table(s).new_quantity) THEN
678
679 x_return_msg := 'VQ003:' || p_release_id || '*' || p_po_change_table(s).document_line_location_id ||
680 '*' || p_po_change_table(s).document_distribution_id;
681 IF(p_release_id IS NULL) THEN
682 SELECT
683 prla.line_num,
684 prda.distribution_num,
685 prla.requisition_line_id,
686 prda.distribution_id
687 INTO
688 l_err_req_line_num,
689 l_err_req_dist_num,
690 l_err_req_line_id,
691 l_err_req_dist_id
692 FROM
693 po_lines_all pla,
694 po_line_locations_all plla,
695 po_distributions_all pda,
696 po_requisition_lines_all prla,
697 po_req_distributions_all prda
698 WHERE
699 pla.po_header_id = p_header_id
700 AND pla.po_line_id = p_po_change_table(s).document_line_id
701 AND plla.po_line_id = pla.po_line_id
702 AND plla.line_location_id = p_po_change_table(s).document_line_location_id
703 AND pda.line_location_id = plla.line_location_id
704 AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
705 AND pda.req_distribution_id = prda.distribution_id
706 AND prda.requisition_line_id = prla.requisition_line_id;
707
708 ELSE
709 SELECT
710 prla.line_num,
711 prda.distribution_num,
712 prla.requisition_line_id,
713 prda.distribution_id
714 INTO
715 l_err_req_line_num,
716 l_err_req_dist_num,
717 l_err_req_line_id,
718 l_err_req_dist_id
719 FROM
720 po_line_locations_all plla,
721 po_distributions_all pda,
722 po_requisition_lines_all prla,
723 po_req_distributions_all prda
724 WHERE
725 plla.po_release_id = p_release_id
726 AND plla.line_location_id = p_po_change_table(s).document_line_location_id
727 AND pda.line_location_id = plla.line_location_id
728 AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
729 AND pda.req_distribution_id = prda.distribution_id
730 AND prda.requisition_line_id = prla.requisition_line_id;
731
732 END IF;
733 x_return_msg := 'VQ0031';
734 p_errortable.msg_data.extend(1);
735 p_errortable.req_line_id.extend(1);
736 p_errortable.req_dist_id.extend(1);
737 p_errortable.msg_count.extend(1);
738 p_errortable.err_attribute.extend(1);
739
740 p_errortable.req_line_id(p_error_index) := l_err_req_line_id;
741 p_errortable.req_dist_id(p_error_index) := l_err_req_dist_id;
742 fnd_message.set_name('PO', 'PO_RCO_NEW_QTY_BELOW_BILL_DEL');
743 fnd_message.set_token('LINE_NUM', l_err_req_line_num);
744 fnd_message.set_token('DIST_NUM', l_err_req_dist_num);
745 p_errortable.msg_data(p_error_index) := fnd_message.get;
746 p_errortable.msg_count(p_error_index) := 1;
747 p_errortable.err_attribute(p_error_index) := 'QUANTITY';
748
749 p_error_index := p_error_index + 1;
750 END IF;
751 x_return_msg := 'VQ004';
752 SELECT plla.quantity_received
753 INTO l_qty_old_rec
754 FROM
755 po_line_locations_all plla,
756 po_distributions_all pda
757 WHERE plla.line_location_id = pda.line_location_id
758 AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id;
759
760 x_return_msg := 'VQ005';
761
762 SELECT SUM(plla.quantity_received) + p_po_change_table(s).new_quantity
763 INTO l_qty_new_rec
764 FROM
765 po_line_locations_all plla,
766 po_distributions_all pda1,
767 po_distributions_all pda2
768 WHERE plla.line_location_id = pda1.line_location_id
769 AND pda1.po_distribution_id <> p_po_change_table(s).document_distribution_id
770 AND pda1.line_location_id = pda2.line_location_id
771 AND pda2.po_distribution_id = p_po_change_table(s).document_distribution_id;
772
773
774
775
776 IF(l_qty_old_rec > l_qty_new_rec) THEN
777 x_return_msg := 'VQ006';
778 IF(p_release_id IS NULL) THEN
779 SELECT
780 prla.line_num,
781 prda.distribution_num,
782 prla.requisition_line_id,
783 prda.distribution_id
784 INTO
785 l_err_req_line_num,
786 l_err_req_dist_num,
787 l_err_req_line_id,
788 l_err_req_dist_id
789 FROM
790 po_lines_all pla,
791 po_line_locations_all plla,
792 po_distributions_all pda,
793 po_requisition_lines_all prla,
794 po_req_distributions_all prda
795 WHERE
796 pla.po_header_id = p_header_id
797 AND pla.po_line_id = p_po_change_table(s).document_line_id
798 AND plla.po_line_id = pla.po_line_id
799 AND plla.line_location_id = p_po_change_table(s).document_line_location_id
800 AND pda.line_location_id = plla.line_location_id
801 AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
802 AND pda.req_distribution_id = prda.distribution_id
803 AND prda.requisition_line_id = prla.requisition_line_id;
804
805 ELSE
806 SELECT
807 prla.line_num,
808 prda.distribution_num,
809 prla.requisition_line_id,
810 prda.distribution_id
811 INTO
812 l_err_req_line_num,
813 l_err_req_dist_num,
814 l_err_req_line_id,
815 l_err_req_dist_id
816 FROM
817 po_line_locations_all plla,
818 po_distributions_all pda,
819 po_requisition_lines_all prla,
820 po_req_distributions_all prda
821 WHERE
822 plla.po_release_id = p_release_id
823 AND plla.line_location_id = p_po_change_table(s).document_line_location_id
824 AND pda.line_location_id = plla.line_location_id
825 AND pda.po_distribution_id = p_po_change_table(s).document_distribution_id
826 AND pda.req_distribution_id = prda.distribution_id
827 AND prda.requisition_line_id = prla.requisition_line_id;
828
829 END IF;
830 p_errortable.msg_data.extend(1);
831 p_errortable.req_line_id.extend(1);
832 p_errortable.req_dist_id.extend(1);
833 p_errortable.msg_count.extend(1);
834 p_errortable.err_attribute.extend(1);
835
836 p_errortable.req_line_id(p_error_index) := l_err_req_line_id;
837 p_errortable.req_dist_id(p_error_index) := l_err_req_dist_id;
838 fnd_message.set_name('PO', 'PO_RCO_NEW_QTY_BELOW_REC');
839 fnd_message.set_token('LINE_NUM', l_err_req_line_num);
840 fnd_message.set_token('DIST_NUM', l_err_req_dist_num);
841 p_errortable.msg_data(p_error_index) := fnd_message.get;
842 p_errortable.msg_count(p_error_index) := 1;
843 p_errortable.err_attribute(p_error_index) := 'QUANTITY';
844
845 p_error_index := p_error_index + 1;
846
847 END IF;
848 END IF;
849 END LOOP;
850 EXCEPTION WHEN OTHERS THEN
851 IF g_fnd_debug = 'Y' THEN
852 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
853 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
854 l_api_name || '.others_exception', x_return_msg || ':' || SQLERRM);
855 END IF;
856 END IF;
857 RAISE;
858 END validate_quantity;
859
860
861 PROCEDURE decode_poerror(p_header_id IN NUMBER,
862 p_release_id IN NUMBER,
863 p_err_po_msg IN VARCHAR2,
864 p_doc_check_rec_type IN doc_check_return_type,
865 p_po_error_index IN NUMBER,
866 p_errortable IN OUT NOCOPY po_req_change_err_table,
867 p_error_index IN OUT NOCOPY NUMBER,
868 x_return_status OUT NOCOPY VARCHAR2,
869 x_return_msg OUT NOCOPY VARCHAR2)
870 IS
871 l_api_name VARCHAR2(50) := 'decode_POError';
872 l_progress VARCHAR2(5) := '000';
873 l_blanket_num po_headers_all.segment1%TYPE;
874 l_po_num po_headers_all.segment1%TYPE;
875 l_release_num NUMBER;
876 l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
877 l_doc_type VARCHAR2(2000);
878 l_doc_num po_headers_all.segment1%TYPE;
879 l_err_po_line_num NUMBER;
880 BEGIN
881 x_return_status := fnd_api.g_ret_sts_success;
882
883 l_err_po_line_num := p_doc_check_rec_type.line_num(p_po_error_index);
884
885 IF(p_release_id IS NULL) THEN
886 SELECT
887 segment1,
888 type_lookup_code
889 INTO
890 l_po_num,
891 l_type_lookup_code
892 FROM po_headers_all
893 WHERE po_header_id = p_header_id;
894
895 IF(l_type_lookup_code = 'STANDARD') THEN
896 l_doc_type := fnd_message.get_string('PO', 'PO_WF_NOTIF_STD_PO');
897 ELSIF(l_type_lookup_code = 'PLANNED') THEN
898 l_doc_type := fnd_message.get_string('PO', 'PO_WF_NOTIF_PLAN_PO');
899 ELSIF(l_type_lookup_code = 'BLANKET') THEN
900 l_doc_type := fnd_message.get_string('PO', 'PO_WF_NOTIF_BLANKET');
901 END IF;
902
903 l_doc_num := l_po_num;
904 ELSE
905 SELECT
906 pha.segment1,
907 pra.release_num,
908 pha.type_lookup_code
909 INTO
910 l_blanket_num,
911 l_release_num,
912 l_type_lookup_code
913 FROM po_headers_all pha,
914 po_releases_all pra
915 WHERE pra.po_release_id = p_release_id
916 AND pra.po_header_id = pha.po_header_id;
917
918 IF(l_type_lookup_code = 'BLANKET') THEN
919 l_doc_type := fnd_message.get_string('PO', 'PO_WF_NOTIF_BKT_REL');
920 ELSIF(l_type_lookup_code = 'PLANNED') THEN
921 l_doc_type := fnd_message.get_string('PO', 'PO_WF_NOTIF_SCH_REL');
922 END IF;
923
924 l_doc_num := l_blanket_num || '-' || l_release_num;
925 END IF;
926
927 l_progress := '001';
928
929 IF(p_err_po_msg = 'PO_SUB_REL_AMT_GRT_LIMIT_AMT') THEN
930 /*PO_RCO_REL_AMT_EXC_LIMIT:
931 Changes entered cause the amount being released plus the amount release to date to be
932 greater than the amount limit for Release BLANKET_NUM - RELEASE_NUM.*/
933
934
935 p_errortable.msg_data.extend(1);
936 p_errortable.req_line_id.extend(1);
937 p_errortable.req_dist_id.extend(1);
938 p_errortable.msg_count.extend(1);
939 p_errortable.err_attribute.extend(1);
940
941 fnd_message.set_name('PO', 'PO_RCO_REL_AMT_EXC_LIMIT');
942 fnd_message.set_token('BLANKET_NUM', l_blanket_num);
943 fnd_message.set_token('RELEASE_NUM', l_release_num);
944
945 p_errortable.msg_data(p_error_index) := fnd_message.get;
946 p_errortable.msg_count(p_error_index) := 1;
947 p_error_index := p_error_index + 1;
948
949 ELSIF(p_err_po_msg = 'PO_SUB_REL_AMT_LESS_MINREL_AMT') THEN
950 /*PO_RCO_REL_AMT_BELOW_MIN: Changes entered cause the amount being released
951 plus the amount release to date to be less than the Min Release Amount for
952 Release BLANKET_NUM - RELEASE_NUM*/
953
954
955 p_errortable.msg_data.extend(1);
956 p_errortable.req_line_id.extend(1);
957 p_errortable.req_dist_id.extend(1);
958 p_errortable.msg_count.extend(1);
959 p_errortable.err_attribute.extend(1);
960
961 fnd_message.set_name('PO', 'PO_RCO_REL_AMT_BELOW_MIN');
962 fnd_message.set_token('BLANKET_NUM', l_blanket_num);
963 fnd_message.set_token('RELEASE_NUM', l_release_num);
964
965 p_errortable.msg_data(p_error_index) := fnd_message.get;
966 p_errortable.msg_count(p_error_index) := 1;
967 p_error_index := p_error_index + 1;
968
969
970 ELSIF(p_err_po_msg = 'PO_SUB_REL_SHIPAMT_LESS_MINREL') THEN
971 /*PO_RCO_REL_LINE_BELOW_MIN: Changes entered cause release line total to be
972 less than the agreement limit for line LINE_NUM on Release BLANKET_NUM - RELEASE_NUM.*/
973
974
975 p_errortable.msg_data.extend(1);
976 p_errortable.req_line_id.extend(1);
977 p_errortable.req_dist_id.extend(1);
978 p_errortable.msg_count.extend(1);
979 p_errortable.err_attribute.extend(1);
980
981 fnd_message.set_name('PO', 'PO_RCO_REL_LINE_BELOW_MIN');
982 fnd_message.set_token('BLANKET_NUM', l_blanket_num);
983 fnd_message.set_token('RELEASE_NUM', l_release_num);
984 fnd_message.set_token('LINE_NUM', l_err_po_line_num);
985
986 p_errortable.msg_data(p_error_index) := fnd_message.get;
987 p_errortable.msg_count(p_error_index) := 1;
988 p_error_index := p_error_index + 1;
989
990
991 ELSIF(p_err_po_msg = 'PO_SUB_REL_RATE_NULL') THEN
992 /*PO_RCO_REL_NO_EXCH: No exchange rate conversion information is available for
993 Release BLANKET_NUM - RELEASE_NUM your selected currency.
994 Please contact your Purchasing department for assistance.*/
995
996
997 p_errortable.msg_data.extend(1);
998 p_errortable.req_line_id.extend(1);
999 p_errortable.req_dist_id.extend(1);
1000 p_errortable.msg_count.extend(1);
1001 p_errortable.err_attribute.extend(1);
1002
1003 fnd_message.set_name('PO', 'PO_RCO_REL_NO_EXCH');
1004 fnd_message.set_token('BLANKET_NUM', l_blanket_num);
1005 fnd_message.set_token('RELEASE_NUM', l_release_num);
1006
1007 p_errortable.msg_data(p_error_index) := fnd_message.get;
1008 p_errortable.msg_count(p_error_index) := 1;
1009 p_error_index := p_error_index + 1;
1010
1011
1012 ELSIF(p_err_po_msg = 'PO_SUB_DIST_RATE_NULL') THEN
1013 --PO_RCO_DIST_NO_EXCH: No exchange rate conversion information is available for DOC_TYPE DOC_NUM line LINE_NUM for your selected currency. Please contact your Purchasing department for assistance.
1014
1015
1016 p_errortable.msg_data.extend(1);
1017 p_errortable.req_line_id.extend(1);
1018 p_errortable.req_dist_id.extend(1);
1019 p_errortable.msg_count.extend(1);
1020 p_errortable.err_attribute.extend(1);
1021
1022 fnd_message.set_name('PO', 'PO_RCO_DIST_NO_EXCH');
1023 fnd_message.set_token('DOC_TYPE', l_doc_type);
1024 fnd_message.set_token('DOC_NUM', l_doc_num);
1025 fnd_message.set_token('LINE_NUM', l_err_po_line_num);
1026
1027
1028 p_errortable.msg_data(p_error_index) := fnd_message.get;
1029 p_errortable.msg_count(p_error_index) := 1;
1030 p_error_index := p_error_index + 1;
1031
1032
1033 ELSIF(p_err_po_msg = 'PO_SUB_STD_GA_LINE_LESS_MINREL') THEN
1034 /*PO_RCO_STD_LINE_BELOW_MIN: Changes entered cause the line total to be
1035 less than the minimum release amount for line LINE_NUM on Purchase Order PO_NUM.*/
1036
1037
1038 p_errortable.msg_data.extend(1);
1039 p_errortable.req_line_id.extend(1);
1040 p_errortable.req_dist_id.extend(1);
1041 p_errortable.msg_count.extend(1);
1042 p_errortable.err_attribute.extend(1);
1043
1044 fnd_message.set_name('PO', 'PO_RCO_STD_LINE_BELOW_MIN');
1045 fnd_message.set_token('PO_NUM', l_po_num);
1046 fnd_message.set_token('LINE_NUM', l_err_po_line_num);
1047
1048
1049 p_errortable.msg_data(p_error_index) := fnd_message.get;
1050 p_errortable.msg_count(p_error_index) := 1;
1051 p_error_index := p_error_index + 1;
1052
1053
1054 ELSIF(p_err_po_msg = 'PO_SUB_REQ_AMT_TOL_EXCEED') THEN
1055 /*PO_RCO_AMT_EXC_TOL_MIN: Changes entered cause the total amount to
1056 exceed the amount tolerance limit for DOC_TYPE DOC_NUM.*/
1057
1058
1059 p_errortable.msg_data.extend(1);
1060 p_errortable.req_line_id.extend(1);
1061 p_errortable.req_dist_id.extend(1);
1062 p_errortable.msg_count.extend(1);
1063 p_errortable.err_attribute.extend(1);
1064
1065 fnd_message.set_name('PO', 'PO_RCO_AMT_EXC_TOL_MIN');
1066 fnd_message.set_token('DOC_TYPE', l_doc_type);
1067 fnd_message.set_token('DOC_NUM', l_doc_num);
1068
1069
1070 p_errortable.msg_data(p_error_index) := fnd_message.get;
1071 p_errortable.msg_count(p_error_index) := 1;
1072 p_error_index := p_error_index + 1;
1073
1074
1075 ELSIF(p_err_po_msg = 'PO_SUB_REQ_PRICE_TOL_EXCEED') THEN
1076 /*PO_RCO_LINE_PRICE_ECX_TOL: Changes entered cause the line price to
1077 exceed the price tolerance limit for LINE_NUM on DOC_TYPE DOC_NUM.*/
1078
1079
1080 p_errortable.msg_data.extend(1);
1081 p_errortable.req_line_id.extend(1);
1082 p_errortable.req_dist_id.extend(1);
1083 p_errortable.msg_count.extend(1);
1084 p_errortable.err_attribute.extend(1);
1085
1086 fnd_message.set_name('PO', 'PO_RCO_LINE_PRICE_ECX_TOL');
1087 fnd_message.set_token('DOC_TYPE', l_doc_type);
1088 fnd_message.set_token('DOC_NUM', l_doc_num);
1089 fnd_message.set_token('LINE_NUM', l_err_po_line_num);
1090
1091
1092 p_errortable.msg_data(p_error_index) := fnd_message.get;
1093 p_errortable.msg_count(p_error_index) := 1;
1094 p_error_index := p_error_index + 1;
1095
1096
1097
1098 ELSIF(p_err_po_msg = 'PO_SUB_STD_AMT_GRT_GA_AMT_LMT') THEN
1099 /*PO_RCO_STD_AMT_EXC_LIMIT: Changes entered cause the amount being released
1100 plus the amount release to date to be
1101 greater than the amount limit for Purchase Order PO_NUM.*/
1102
1103
1104
1105 p_errortable.msg_data.extend(1);
1106 p_errortable.req_line_id.extend(1);
1107 p_errortable.req_dist_id.extend(1);
1108 p_errortable.msg_count.extend(1);
1109 p_errortable.err_attribute.extend(1);
1110
1111 fnd_message.set_name('PO', 'PO_RCO_STD_AMT_EXC_LIMIT');
1112 fnd_message.set_token('PO_NUM', l_po_num);
1113
1114 p_errortable.msg_data(p_error_index) := fnd_message.get;
1115 p_errortable.msg_count(p_error_index) := 1;
1116 p_error_index := p_error_index + 1;
1117
1118
1119 ELSIF(p_err_po_msg = 'PO_SUB_STD_GA_PRICE_MISMATCH') THEN
1120 /*PO_RCO_STD_PRICE_EXC_TOL: Changes entered cause the line price to
1121 exceed the price override tolerance limit for LINE_NUM on Purchase Order PO_NUM.*/
1122
1123 p_errortable.msg_data.extend(1);
1124 p_errortable.req_line_id.extend(1);
1125 p_errortable.req_dist_id.extend(1);
1126 p_errortable.msg_count.extend(1);
1127 p_errortable.err_attribute.extend(1);
1128
1129 fnd_message.set_name('PO', 'PO_RCO_STD_PRICE_EXC_TOL');
1130 fnd_message.set_token('PO_NUM', l_po_num);
1131 fnd_message.set_token('LINE_NUM', l_err_po_line_num);
1132
1133 p_errortable.msg_data(p_error_index) := fnd_message.get;
1134 p_errortable.msg_count(p_error_index) := 1;
1135 p_error_index := p_error_index + 1;
1136
1137 END IF;
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 IF g_fnd_debug = 'Y' THEN
1141 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
1142 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
1143 l_api_name || '.others_exception',
1144 p_header_id || '*' || p_release_id || '*' || p_err_po_msg || ':' || SQLERRM);
1145 END IF;
1146 END IF;
1147 x_return_status := fnd_api.g_ret_sts_unexp_error;
1148 x_return_msg := 'DPE_UNEXP:' || p_header_id || '*' || p_release_id || '*' || p_err_po_msg || '*' || l_progress || ':' || SQLERRM;
1149 END decode_poerror;
1150
1151 /*
1152 *Given a particular ReqLine_id, and a new transactional price, we will return
1153 *the new functionally currency price.
1154 */
1155 FUNCTION calculate_newunitprice(p_req_line_id NUMBER, p_new_price NUMBER) RETURN NUMBER
1156 IS
1157 l_transaction_currency po_requisition_lines_all.currency_code%TYPE;
1158 l_functional_currency gl_sets_of_books.currency_code%TYPE;
1159 l_rate_type po_requisition_lines_all.rate_type%TYPE;
1160 l_conversion_date DATE;
1161 l_rate NUMBER;
1162 l_unit_price NUMBER;
1163 l_denominator NUMBER;
1164 l_numerator NUMBER;
1165 l_set_of_books_id NUMBER;
1166 l_gl_rate NUMBER;
1167
1168 BEGIN
1169
1170 SELECT currency_code
1171 INTO l_transaction_currency
1172 FROM po_requisition_lines_all
1173 WHERE requisition_line_id = p_req_line_id ;
1174
1175 SELECT currency_code, fsp.set_of_books_id
1176 INTO l_functional_currency, l_set_of_books_id
1177 FROM
1178 gl_sets_of_books gsob,
1179 financials_system_parameters fsp
1180 WHERE fsp.set_of_books_id = gsob.set_of_books_id;
1181
1182 IF(l_transaction_currency <> l_functional_currency) THEN
1183 SELECT nvl(rate_type,' ')
1184 INTO l_rate_type
1185 FROM po_requisition_lines_all
1186 WHERE requisition_line_id = p_req_line_id;
1187
1188 IF(l_rate_type <> 'User') THEN
1189 SELECT rate_date
1190 INTO l_conversion_date
1191 FROM po_requisition_lines_all
1192 WHERE requisition_line_id = p_req_line_id;
1193
1194 gl_currency_api.get_triangulation_rate(l_set_of_books_id,
1195 l_transaction_currency,
1196 l_conversion_date,
1197 l_rate_type,
1198 l_denominator,
1199 l_numerator,
1200 l_gl_rate);
1201
1202
1203 l_unit_price :=
1204 (p_new_price / l_denominator) * l_numerator;
1205 ELSE
1206 SELECT rate
1207 INTO l_rate
1208 FROM po_requisition_lines_all
1209 WHERE requisition_line_id = p_req_line_id;
1210
1211 l_unit_price := p_new_price * l_rate;
1212 END IF;
1213 ELSE
1214 l_unit_price := NULL;
1215 END IF;
1216 RETURN l_unit_price;
1217 EXCEPTION WHEN OTHERS THEN
1218 RETURN NULL;
1219 END calculate_newunitprice;
1220
1221 PROCEDURE generate_po_change_table(p_po_change_table IN OUT NOCOPY pos_chg_rec_tbl,
1222 p_progress OUT NOCOPY VARCHAR) IS
1223 l_shipment_index number_tbl;
1224 l_last NUMBER := 1;
1225 i NUMBER := 1;
1226 l_po_change_table_count NUMBER;
1227 l_po_tbl_index NUMBER;
1228 l_ship_quantity NUMBER;
1229 l_ship_to_location_id NUMBER;
1230 l_ship_to_organization_id NUMBER;
1231 l_need_by_date DATE;
1232
1233 BEGIN
1234
1235 l_po_change_table_count := p_po_change_table.count + 1;
1236 p_progress := 'G1' || to_char(l_po_change_table_count);
1237 WHILE (i<l_po_change_table_count) LOOP
1238 p_progress := 'G1' || to_char(l_po_change_table_count) ||' '|| to_char(i);
1239 IF(p_po_change_table(i).request_level = 'SHIPMENT') THEN
1240 l_shipment_index(p_po_change_table(i).document_line_location_id) := i;
1241 END IF;
1242 i := i + 1;
1243 END LOOP;
1244 i := 1;
1245 l_po_tbl_index := l_po_change_table_count;
1246 p_progress := 'G2' || to_char(l_po_change_table_count);
1247 WHILE (i<l_po_change_table_count) LOOP
1248 p_progress := 'G2' || to_char(l_po_change_table_count) ||' '|| to_char(i);
1249 IF(p_po_change_table(i).request_level = 'DISTRIBUTION') THEN
1250 IF(l_shipment_index.exists(p_po_change_table(i).document_line_location_id)) THEN
1251 p_po_change_table(l_shipment_index(p_po_change_table(i).
1252 document_line_location_id)).new_quantity
1253 := nvl(p_po_change_table(l_shipment_index(p_po_change_table(i).
1254 document_line_location_id)).new_quantity,
1255 p_po_change_table(l_shipment_index(p_po_change_table(i).
1256 document_line_location_id)).old_quantity) +
1257 p_po_change_table(i).new_quantity - p_po_change_table(i).old_quantity;
1258 ELSE
1259 p_progress := 'G2' || to_char(l_po_change_table_count) ||' '|| to_char(i) ||' '|| to_char(p_po_change_table(i).document_line_location_id);
1260 p_po_change_table.extend(1);
1261 l_shipment_index(p_po_change_table(i).document_line_location_id) := l_po_tbl_index;
1262 p_progress := 'G2-1' || to_char(l_po_change_table_count) ||' '|| to_char(i) ||' '|| to_char(p_po_change_table(i).document_line_location_id);
1263
1264 SELECT quantity,
1265 ship_to_location_id,
1266 ship_to_organization_id,
1267 need_by_date
1268 INTO l_ship_quantity,
1269 l_ship_to_location_id,
1270 l_ship_to_organization_id,
1271 l_need_by_date
1272 FROM po_line_locations_all
1273 WHERE line_location_id = p_po_change_table(i).document_line_location_id;
1274
1275 p_progress := 'G2-2' || to_char(l_po_change_table_count) ||' '|| to_char(i) ||' '|| to_char(p_po_change_table(i).document_line_location_id);
1276 p_po_change_table(l_po_tbl_index) := po_chg_request_pvt.create_pos_change_rec(
1277 p_action_type => 'MODIFICATION',
1278 p_initiator => 'REQUESTER',
1279 p_document_type => p_po_change_table(i).document_type,
1280 p_request_level => 'SHIPMENT',
1281 p_request_status => 'PENDING',
1282 p_document_header_id => p_po_change_table(i).document_header_id,
1283 p_request_reason => 'aa',
1284 p_po_release_id => p_po_change_table(i).po_release_id,
1285 p_document_num => p_po_change_table(i).document_num,
1286 p_document_revision_num => p_po_change_table(i).document_revision_num,
1287 p_document_line_id => p_po_change_table(i).document_line_id,
1288 p_document_line_number => p_po_change_table(i).document_line_number,
1289 p_document_line_location_id => p_po_change_table(i).document_line_location_id,
1290 p_document_shipment_number => p_po_change_table(i).document_shipment_number,
1291 p_document_distribution_id => NULL,
1292 p_document_distribution_number => NULL,
1293 p_parent_line_location_id => NULL, --NUMBER,
1294 p_old_quantity => l_ship_quantity, --NUMBER,
1295 p_new_quantity => l_ship_quantity + p_po_change_table(i).new_quantity - p_po_change_table(i).old_quantity,
1296 p_old_promised_date => NULL, --DATE,
1297 p_new_promised_date => NULL, --DATE,
1298 p_old_supplier_part_number => NULL, --VARCHAR2(25),
1299 p_new_supplier_part_number => NULL, --VARCHAR2(25),
1300 p_old_price => NULL,
1301 p_new_price => NULL,
1302 p_old_supplier_reference_num => NULL, --VARCHAR2(30),
1303 p_new_supplier_reference_num => NULL,
1304 p_from_header_id => NULL, --NUMBER
1305 p_recoverable_tax => NULL, --NUMBER
1306 p_non_recoverable_tax => NULL, --NUMBER
1307 p_ship_to_location_id => l_ship_to_location_id,
1308 p_ship_to_organization_id => l_ship_to_organization_id ,
1309 p_old_need_by_date => l_need_by_date,
1310 p_new_need_by_date => NULL,
1311 p_approval_required_flag => NULL,
1312 p_parent_change_request_id => NULL,
1313 p_requester_id => NULL,
1314 p_old_supplier_order_number => NULL,
1315 p_new_supplier_order_number => NULL,
1316 p_old_supplier_order_line_num => NULL,
1317 p_new_supplier_order_line_num => NULL,
1318 p_additional_changes => NULL,
1319 p_old_start_date => NULL,
1320 p_new_start_date => NULL,
1321 p_old_expiration_date => NULL,
1322 p_new_expiration_date => NULL,
1323 p_old_amount => NULL,
1324 p_new_amount => NULL
1325 );
1326
1327 p_progress := 'G2-3' || to_char(l_po_change_table_count) ||' '|| to_char(i) ||' '|| to_char(p_po_change_table(i).document_line_location_id);
1328 l_po_tbl_index := l_po_tbl_index + 1;
1329 END IF;
1330 END IF;
1331 i := i + 1;
1332 END LOOP;
1333 EXCEPTION
1334 WHEN OTHERS THEN
1335 p_progress := p_progress || SQLERRM;
1336 RAISE;
1337 END;
1338
1339 /*
1340 * This API takes in a PLSQL table of requisition changes, transform it into PLSQL tables
1341 * of PO changes (groupd by po_header_id/po_release_id), and call ISP's validate_change_request
1342 * API to validate the changes
1343 */
1344 PROCEDURE validate_changes(p_req_hdr_id IN NUMBER,
1345 p_req_change_table IN OUT NOCOPY change_tbl_type,
1346 x_return_status OUT NOCOPY VARCHAR2,
1347 x_retmsg OUT NOCOPY VARCHAR,
1348 p_errortable IN OUT NOCOPY po_req_change_err_table)
1349 IS
1350 l_api_name VARCHAR2(50) := 'Validate_Changes';
1351 l_po_change_table pos_chg_rec_tbl;
1352 l_error_table error_tbl_type;
1353 l_main_loop_flag VARCHAR2(1) := fnd_api.g_true;
1354 l_get_cur_id_loop_flag VARCHAR2(1) := fnd_api.g_true;
1355 i NUMBER := 2;
1356 j NUMBER;
1357 k NUMBER;
1358 l_table_index NUMBER;
1359 l_po_header_id NUMBER;
1360 l_current_hdr_id NUMBER;
1361 l_current_rel_id NUMBER;
1362 l_hdr_id NUMBER;
1363 l_rel_id NUMBER;
1364 l_current_row_id NUMBER;
1365
1366 l_current_rev_num NUMBER;
1367 l_output_report_id NUMBER;
1368 l_found_clean VARCHAR2(1);
1369 l_doc_check_rec_type doc_check_return_type;
1370 l_online_report_id NUMBER;
1371 l_error_index NUMBER := 1;
1372
1373 l_return_status VARCHAR2(1);
1374 l_return_msg VARCHAR2(2000);
1375 l_err_po_msg VARCHAR2(2000);
1376
1377 l_pos_errors_tbl pos_err_type;
1378 l_decode_status VARCHAR2(1);
1379 l_decode_msg VARCHAR2(2000);
1380 l_val_qty_msg VARCHAR2(2000);
1381 l_val_qty_status VARCHAR2(1);
1382 l_err_count NUMBER;
1383 l_req_org_id NUMBER;
1384 BEGIN
1385 x_retmsg := 'VC000';
1386 x_return_status := fnd_api.g_ret_sts_success;
1387
1388 j := 1;
1389 l_main_loop_flag := fnd_api.g_true;
1390
1391 --Main Loop Starts
1392 --Main Loop continues so as long there exists records with dirty_flag = 'N'
1393 WHILE(l_main_loop_flag = fnd_api.g_true)
1394 LOOP
1395 x_retmsg := 'VC001';
1396 --Get First Clean Row => dirty_flag ='N'
1397 l_get_cur_id_loop_flag := fnd_api.g_true;
1398 l_found_clean := fnd_api.g_true;
1399 WHILE(l_get_cur_id_loop_flag = fnd_api.g_true) LOOP
1400 IF(j = p_req_change_table.count + 1) THEN /*Cannot find any Clean Row, Thus End Procedure*/
1401 l_main_loop_flag := fnd_api.g_false;
1402 l_get_cur_id_loop_flag := fnd_api.g_false;
1403 l_found_clean := fnd_api.g_false;
1404 ELSIF(p_req_change_table(j).dirty_flag = 'N') THEN
1405 l_current_row_id := j;
1406 j := j + 1;
1407 l_get_cur_id_loop_flag := fnd_api.g_false;
1408 ELSE
1409 j := j + 1;
1410 END IF;
1411 END LOOP;
1412
1413 x_retmsg := 'VC002';
1414
1415 --Obtained First Clean Row in l_current_row_id
1416
1417 IF(l_found_clean = fnd_api.g_true) THEN
1418
1419 --Get po_header_id/po_release_id for the current Clean Row
1420 SELECT
1421 plla.po_header_id,
1422 plla.po_release_id
1423 INTO
1424 l_current_hdr_id,
1425 l_current_rel_id
1426 FROM
1427 po_line_locations_all plla,
1428 po_requisition_lines_all prla
1429 WHERE prla.line_location_id = plla.line_location_id
1430 AND prla.requisition_line_id = p_req_change_table(l_current_row_id).document_line_id;
1431
1432 --Refresh l_po_change_table
1433 IF(l_po_change_table IS NOT NULL) THEN
1434 l_po_change_table.delete;
1435 END IF;
1436
1437 k := 2; /*index of l_po_change_table*/
1438 p_req_change_table(l_current_row_id).dirty_flag := 'Y';
1439
1440 l_po_change_table := pos_chg_rec_tbl();
1441 l_po_change_table.extend(1);
1442 copy_change(l_current_hdr_id, l_current_rel_id, p_req_hdr_id,
1443 p_req_change_table, l_current_row_id, 1, l_po_change_table);
1444
1445
1446 --Inner Loop Starts
1447 --Scan through remaining clean Rows in p_req_change_table, and extract those
1448 --clean rows, copy them over, and mark them as dirty..
1449 FOR i IN j .. p_req_change_table.count
1450 LOOP
1451
1452 IF (p_req_change_table(i).dirty_flag = 'N') THEN
1453
1454
1455
1456 --Get po_header_id/po_release_id of this clean row
1457 SELECT
1458 plla.po_header_id,
1459 plla.po_release_id
1460 INTO
1461 l_hdr_id,
1462 l_rel_id
1463 FROM
1464 po_line_locations_all plla,
1465 po_requisition_lines_all prla
1466 WHERE prla.line_location_id = plla.line_location_id
1467 AND prla.requisition_line_id = p_req_change_table(i).document_line_id;
1468
1469
1470
1471 --po_header_id matches
1472 IF(l_current_rel_id IS NULL AND l_rel_id IS NULL AND l_hdr_id = l_current_hdr_id) THEN
1473
1474
1475 l_po_change_table.extend(1);
1476 copy_change(l_current_hdr_id, l_current_rel_id, p_req_hdr_id,
1477 p_req_change_table, i, k, l_po_change_table);
1478
1479 p_req_change_table(i).dirty_flag := 'Y';
1480
1481 k := k + 1;
1482
1483 --po_release_id matches
1484 ELSIF(l_rel_id = l_current_rel_id) THEN
1485
1486
1487 l_po_change_table.extend(1);
1488 copy_change(l_current_hdr_id, l_current_rel_id, p_req_hdr_id,
1489 p_req_change_table, i, k, l_po_change_table);
1490
1491 p_req_change_table(i).dirty_flag := 'Y';
1492 k := k + 1;
1493
1494 END IF;
1495
1496 END IF;
1497
1498 END LOOP;
1499
1500 x_retmsg := 'VC003';
1501
1502
1503 -- Check new quantity against delivered/received/billed quantity
1504 validate_quantity(l_current_hdr_id,
1505 l_current_rel_id,
1506 l_po_change_table,
1507 p_errortable,
1508 l_error_index,
1509 l_val_qty_status,
1510 l_val_qty_msg);
1511
1512
1513
1514 x_retmsg := 'VC0031';
1515 generate_po_change_table(l_po_change_table, x_retmsg);
1516 x_retmsg := 'VC0031-1';
1517
1518 po_chg_request_pvt.validate_change_request(
1519 p_api_version => 1.0,
1520 p_init_msg_list => fnd_api.g_false,
1521 x_return_status => l_return_status,
1522 x_msg_data => l_return_msg,
1523 p_po_header_id => l_current_hdr_id,
1524 p_po_release_id => l_current_rel_id,
1525 p_revision_num => l_current_rev_num,
1526 p_po_change_requests => l_po_change_table,
1527 x_online_report_id => l_online_report_id,
1528 x_pos_errors => l_pos_errors_tbl,
1529 x_doc_check_error_msg => l_doc_check_rec_type);
1530 x_retmsg := 'VC0032';
1531
1532 -- BUG: 3590131
1533 -- validate_change_request API set's org id context
1534 -- to the PO document's org, we need to set it back
1535 SELECT org_id
1536 INTO l_req_org_id
1537 FROM po_requisition_headers_all
1538 WHERE requisition_header_id = p_req_hdr_id;
1539
1540 -- set org context back to req's org
1541 po_moac_utils_pvt.set_org_context(l_req_org_id) ; -- <R12 MOAC>
1542
1543 IF(l_return_status = fnd_api.g_ret_sts_error) THEN
1544 x_retmsg := 'VC0d31:' || l_return_msg;
1545 l_err_count := l_doc_check_rec_type.online_report_id.count;
1546 IF(l_err_count > 0) THEN
1547 FOR y IN 1..l_err_count
1548 LOOP
1549 l_err_po_msg := l_doc_check_rec_type.message_name(y);
1550 decode_poerror(l_current_hdr_id,
1551 l_current_rel_id,
1552 l_err_po_msg,
1553 l_doc_check_rec_type,
1554 y,
1555 p_errortable,
1556 l_error_index,
1557 l_decode_status,
1558 l_decode_msg);
1559 IF(l_decode_status <> fnd_api.g_ret_sts_success) THEN
1560 x_retmsg := 'VC0d41:' || l_decode_msg;
1561 x_return_status := fnd_api.g_ret_sts_error;
1562 RETURN;
1563 END IF;
1564 END LOOP;
1565 END IF;
1566 ELSIF(l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1567 x_retmsg := 'VC0f31:' || l_return_msg;
1568 x_return_status := fnd_api.g_ret_sts_unexp_error;
1569 RETURN;
1570 END IF;
1571
1572 END IF;
1573
1574 END LOOP;
1575
1576 x_retmsg := 'VC004';
1577
1578 IF(p_errortable.req_line_id.count>0) THEN
1579
1580 x_return_status := fnd_api.g_ret_sts_error;
1581 ELSE
1582
1583 x_return_status := fnd_api.g_ret_sts_success;
1584 END IF;
1585
1586
1587 EXCEPTION WHEN OTHERS THEN
1588 x_return_status := fnd_api.g_ret_sts_unexp_error;
1589 x_retmsg := 'VC_Exp:' || x_retmsg || ':' || SQLERRM;
1590 IF g_fnd_debug = 'Y' THEN
1591 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
1592 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
1593 l_api_name || '.others_exception', x_retmsg);
1594 END IF;
1595 END IF;
1596 END validate_changes;
1597
1598 /*
1599 *Simple API to insert change records into PO_CHANGE_REQUESTS table
1600 */
1601 PROCEDURE insert_reqchange(p_change_table change_tbl_type,
1602 p_chn_req_grp_id NUMBER)
1603 IS
1604 l_api_name VARCHAR2(50) := 'Insert_ReqChange';
1605 l_req_user_id NUMBER;
1606
1607 BEGIN
1608
1609 l_req_user_id := fnd_global.user_id;
1610 FOR i IN 1..p_change_table.count
1611 LOOP
1612
1613 INSERT INTO po_change_requests
1614 (
1615 change_request_group_id,
1616 change_request_id,
1617 initiator,
1618 action_type,
1619 request_reason,
1620 request_level,
1621 request_status,
1622 document_type,
1623 document_header_id,
1624 document_num,
1625 document_revision_num,
1626 created_by,
1627 creation_date,
1628 document_line_id,
1629 document_line_number,
1630 document_distribution_id,
1631 document_distribution_number,
1632 old_quantity,
1633 new_quantity,
1634 old_price,
1635 new_price,
1636 old_need_by_date,
1637 new_need_by_date,
1638 old_currency_unit_price,
1639 new_currency_unit_price,
1640 last_updated_by,
1641 last_update_date,
1642 last_update_login,
1643 requester_id,
1644 change_active_flag,
1645 ref_po_header_id,
1646 ref_po_num,
1647 ref_po_release_id,
1648 ref_po_rel_num,
1649 old_start_date,
1650 new_start_date,
1651 old_expiration_date,
1652 new_expiration_date,
1653 old_amount,
1654 new_amount,
1655 old_currency_amount,
1656 new_currency_amount
1657 )
1658 VALUES
1659 (
1660 p_chn_req_grp_id,
1661 po_chg_request_seq.nextval,
1662 'REQUESTER',
1663 p_change_table(i).action_type,
1664 p_change_table(i).request_reason,
1665 p_change_table(i).request_level,
1666 p_change_table(i).request_status,
1667 'REQ',
1668 p_change_table(i).document_header_id,
1669 p_change_table(i).document_num,
1670 p_change_table(i).document_revision_num,
1671 l_req_user_id,
1672 SYSDATE,
1673 p_change_table(i).document_line_id,
1674 p_change_table(i).document_line_number,
1675 p_change_table(i).document_distribution_id,
1676 p_change_table(i).document_distribution_number,
1677 p_change_table(i).old_quantity,
1678 p_change_table(i).new_quantity,
1679 p_change_table(i).old_price,
1680 p_change_table(i).new_price,
1681 p_change_table(i).old_date,
1682 p_change_table(i).new_date,
1683 p_change_table(i).old_currency_unit_price,
1684 p_change_table(i).new_currency_unit_price,
1685 l_req_user_id,
1686 SYSDATE,
1687 l_req_user_id,
1688 p_change_table(i).requester_id,
1689 'Y',
1690 p_change_table(i).referenced_po_header_id,
1691 p_change_table(i).referenced_po_document_num,
1692 p_change_table(i).referenced_release_id,
1693 p_change_table(i).referenced_release_num,
1694 p_change_table(i).old_start_date,
1695 p_change_table(i).new_start_date,
1696 p_change_table(i).old_end_date,
1697 p_change_table(i).new_end_date,
1698 p_change_table(i).old_budget_amount,
1699 p_change_table(i).new_budget_amount,
1700 p_change_table(i).old_currency_budget_amount,
1701 p_change_table(i).new_currency_budget_amount
1702 );
1703
1704 END LOOP;
1705
1706
1707 EXCEPTION WHEN OTHERS THEN
1708
1709 IF g_fnd_debug = 'Y' THEN
1710 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
1711 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
1712 l_api_name || '.others_exception', SQLERRM);
1713 END IF;
1714 END IF;
1715 RAISE;
1716 END insert_reqchange;
1717
1718 /*
1719 *Calculate recoverable tax and non-recoverable tax for a req dist.
1720 */
1721 PROCEDURE calculate_disttax(p_api_version IN NUMBER,
1722 x_return_status OUT NOCOPY VARCHAR2,
1723 p_dist_id NUMBER,
1724 p_price NUMBER,
1725 p_quantity NUMBER,
1726 p_dist_amount NUMBER,
1727 p_rec_tax OUT NOCOPY NUMBER,
1728 p_nonrec_tax OUT NOCOPY NUMBER)
1729 IS
1730 l_api_name VARCHAR2(50) := 'Calculate_DistTax';
1731 l_dist_total NUMBER;
1732 l_new_total NUMBER;
1733 l_rec_tax NUMBER;
1734 l_nonrec_tax NUMBER;
1735 BEGIN
1736 x_return_status := fnd_api.g_ret_sts_success;
1737
1738 SELECT
1739 decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount, prl.unit_price * prd.req_line_quantity),
1740 decode(prl.matching_basis, 'AMOUNT', p_dist_amount, nvl(p_price, prl.unit_price) * nvl(p_quantity, prd.req_line_quantity)),
1741 prd.recoverable_tax,
1742 prd.nonrecoverable_tax
1743 INTO
1744 l_dist_total,
1745 l_new_total,
1746 l_rec_tax,
1747 l_nonrec_tax
1748 FROM
1749 po_requisition_lines_all prl,
1750 po_req_distributions_all prd
1751 WHERE prd.distribution_id = p_dist_id
1752 AND prd.requisition_line_id = prl.requisition_line_id;
1753
1754 -- Calcualte new tax only if existing total tax amount is
1755 -- greater than zero.
1756 IF((nvl(l_rec_tax, 0) + nvl(l_nonrec_tax, 0) ) > 0) THEN
1757 p_rec_tax := (l_rec_tax / l_dist_total) * l_new_total;
1758
1759 p_nonrec_tax :=
1760 (l_nonrec_tax / l_dist_total) *
1761 l_new_total;
1762 END IF;
1763 EXCEPTION WHEN OTHERS THEN
1764 x_return_status := fnd_api.g_ret_sts_unexp_error;
1765 IF g_fnd_debug = 'Y' THEN
1766 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
1767 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
1768 l_api_name || '.others_exception', SQLERRM);
1769 END IF;
1770 END IF;
1771 END calculate_disttax;
1772
1773 /*------------------------------------------------------+
1774 *Given a change group ID, update all distribution records
1775 *with tax information, if needed
1776 +-------------------------------------------------------*/
1777 PROCEDURE update_recordswithtax(p_chn_req_grp_id NUMBER)
1778 IS
1779 l_api_name VARCHAR2(50) := 'Update_RecordsWithTax';
1780 l_progress VARCHAR2(3) := '000';
1781 l_id NUMBER;
1782 l_line_id NUMBER;
1783 l_dist_id NUMBER;
1784 l_new_quantity NUMBER;
1785 l_quantity NUMBER;
1786 l_price NUMBER;
1787 l_rec_tax NUMBER;
1788 l_nonrec_tax NUMBER;
1789 l_cal_disttax_status VARCHAR2(1);
1790 l_dist_rec_tax NUMBER;
1791 l_dist_nonrec_tax NUMBER;
1792 l_temp_id NUMBER;
1793
1794 CURSOR l_dist_with_chn_csr(grp_id NUMBER, line_id NUMBER) IS
1795 SELECT
1796 change_request_id,
1797 new_quantity,
1798 document_distribution_id
1799 FROM po_change_requests
1800 WHERE change_request_group_id = grp_id
1801 AND document_line_id = line_id
1802 AND request_level = 'DISTRIBUTION';
1803
1804 CURSOR l_line_id_with_qty_chn_csr(grp_id NUMBER) IS
1805 SELECT DISTINCT document_line_id
1806 FROM po_change_requests
1807 WHERE change_request_group_id = grp_id
1808 AND new_quantity IS NOT NULL;
1809
1810 CURSOR l_line_id_with_price_chn_csr(grp_id NUMBER) IS
1811 SELECT
1812 change_request_id,
1813 document_line_id,
1814 new_price
1815 FROM po_change_requests
1816 WHERE change_request_group_id = grp_id
1817 AND new_price IS NOT NULL;
1818
1819 CURSOR l_dist_id_csr(line_id NUMBER) IS
1820 SELECT
1821 distribution_id,
1822 req_line_quantity
1823 FROM po_req_distributions_all
1824 WHERE requisition_line_id = l_line_id;
1825
1826
1827 CURSOR l_dist_exist_chn_csr(dist_id NUMBER, grp_id NUMBER) IS
1828 SELECT
1829 change_request_id,
1830 recoverable_tax,
1831 nonrecoverable_tax
1832 FROM po_change_requests
1833 WHERE change_request_group_id = p_chn_req_grp_id
1834 AND document_distribution_id = l_dist_id;
1835
1836
1837 BEGIN
1838
1839 --OUTER LOOP: loops through req distribution records with quantity change, and get the req line ID.
1840 --Objective is to populate tax attributes for distribution records (with quantity change)
1841 OPEN l_line_id_with_qty_chn_csr(p_chn_req_grp_id);
1842 LOOP
1843 FETCH l_line_id_with_qty_chn_csr INTO
1844 l_line_id;
1845 EXIT WHEN l_line_id_with_qty_chn_csr%notfound;
1846
1847 BEGIN
1848 SELECT nvl(new_currency_unit_price, new_price)
1849 INTO l_price
1850 FROM po_change_requests
1851 WHERE change_request_group_id = p_chn_req_grp_id
1852 AND document_line_id = l_line_id
1853 AND request_level = 'LINE'
1854 AND new_price IS NOT NULL;
1855 EXCEPTION WHEN OTHERS THEN
1856 SELECT nvl(currency_unit_price, unit_price)
1857 INTO l_price
1858 FROM po_requisition_lines_all
1859 WHERE requisition_line_id = l_line_id;
1860 END;
1861 l_progress := '001';
1862 --INNER LOOP: After getting the most recent price, update child distribution records with tax information.
1863 OPEN l_dist_with_chn_csr(p_chn_req_grp_id, l_line_id);
1864 LOOP
1865 FETCH l_dist_with_chn_csr INTO l_id, l_new_quantity, l_dist_id ;
1866 EXIT WHEN l_dist_with_chn_csr %notfound;
1867
1868 IF(l_new_quantity IS NOT NULL) THEN
1869 calculate_disttax(1.0, l_cal_disttax_status, l_dist_id, l_price, l_new_quantity, NULL, l_rec_tax, l_nonrec_tax);
1870 ELSE
1871 SELECT req_line_quantity
1872 INTO l_quantity
1873 FROM po_req_distributions_all
1874 WHERE distribution_id = l_dist_id;
1875 calculate_disttax(1.0, l_cal_disttax_status, l_dist_id, l_price, l_quantity, NULL, l_rec_tax, l_nonrec_tax);
1876 END IF;
1877 UPDATE po_change_requests
1878 SET recoverable_tax = l_rec_tax,
1879 nonrecoverable_tax = l_nonrec_tax
1880 WHERE change_request_id = l_id;
1881
1882
1883 END LOOP;
1884 CLOSE l_dist_with_chn_csr;
1885 END LOOP;
1886 CLOSE l_line_id_with_qty_chn_csr;
1887
1888 l_dist_id := NULL;
1889 l_price := NULL;
1890 l_quantity := NULL;
1891
1892 l_progress := '002';
1893 --2nd OUTER LOOP: update recoverable and non recoverable tax attributes of Line Records (with Price Change)
1894 OPEN l_line_id_with_price_chn_csr(p_chn_req_grp_id);
1895 LOOP
1896 FETCH l_line_id_with_price_chn_csr INTO l_id, l_line_id, l_price;
1897 EXIT WHEN l_line_id_with_price_chn_csr%notfound;
1898
1899 l_rec_tax := 0;
1900 l_nonrec_tax := 0;
1901
1902 OPEN l_dist_id_csr(l_line_id);
1903 LOOP
1904 FETCH l_dist_id_csr INTO
1905 l_dist_id,
1906 l_quantity;
1907 EXIT WHEN l_dist_id_csr%notfound;
1908
1909 OPEN l_dist_exist_chn_csr(l_dist_id, p_chn_req_grp_id);
1910 FETCH l_dist_exist_chn_csr INTO
1911 l_temp_id,
1912 l_dist_rec_tax,
1913 l_dist_nonrec_tax;
1914 CLOSE l_dist_exist_chn_csr;
1915
1916 IF(l_temp_id IS NOT NULL) THEN -- Distribution exist in po_change_requests table
1917 l_rec_tax := l_rec_tax + l_dist_rec_tax;
1918 l_nonrec_tax := l_nonrec_tax + l_dist_nonrec_tax;
1919 ELSE -- Distribution does NOT exist in change table, thus need to calculate
1920 calculate_disttax(1.0, l_cal_disttax_status, l_dist_id, l_price, l_quantity, NULL, l_dist_rec_tax, l_dist_nonrec_tax);
1921 l_rec_tax := l_rec_tax + l_dist_rec_tax;
1922 l_nonrec_tax := l_nonrec_tax + l_dist_nonrec_tax;
1923 END IF;
1924
1925 END LOOP;
1926 CLOSE l_dist_id_csr;
1927
1928 l_progress := '003';
1929 UPDATE po_change_requests
1930 SET recoverable_tax = l_rec_tax,
1931 nonrecoverable_tax = l_nonrec_tax
1932 WHERE change_request_id = l_id;
1933 END LOOP;
1934 CLOSE l_line_id_with_price_chn_csr;
1935
1936 EXCEPTION WHEN OTHERS THEN
1937 IF g_fnd_debug = 'Y' THEN
1938 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
1939 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
1940 l_api_name || '.others_exception',
1941 p_chn_req_grp_id || '*' || l_progress || ':' || SQLERRM);
1942 END IF;
1943 END IF;
1944 RAISE;
1945 END update_recordswithtax;
1946
1947 /*------------------------------------------------------+
1948 *Given a change group ID, update all line records
1949 *with tax information, if qunatity is changed needed
1950 +-------------------------------------------------------*/
1951 PROCEDURE update_internalrecordswithtax(p_chn_req_grp_id NUMBER)
1952 IS
1953 l_id NUMBER;
1954 l_line_id NUMBER;
1955 l_dist_id NUMBER;
1956 l_new_quantity NUMBER;
1957 l_quantity NUMBER;
1958 l_price NUMBER;
1959 l_rec_tax NUMBER;
1960 l_nonrec_tax NUMBER;
1961 l_cal_disttax_status VARCHAR2(1);
1962 l_dist_rec_tax NUMBER;
1963 l_dist_nonrec_tax NUMBER;
1964 l_temp_id NUMBER;
1965
1966 CURSOR l_line_id_with_qty_chn_csr(grp_id NUMBER) IS
1967 SELECT DISTINCT document_line_id
1968 FROM po_change_requests
1969 WHERE change_request_group_id = grp_id
1970 AND new_quantity IS NOT NULL;
1971
1972
1973 CURSOR l_dist_id_csr(line_id NUMBER) IS
1974 SELECT
1975 distribution_id,
1976 req_line_quantity
1977 FROM po_req_distributions_all
1978 WHERE requisition_line_id = l_line_id;
1979
1980
1981 CURSOR l_dist_exist_chn_csr(dist_id NUMBER, grp_id NUMBER) IS
1982 SELECT
1983 change_request_id,
1984 recoverable_tax,
1985 nonrecoverable_tax
1986 FROM po_change_requests
1987 WHERE change_request_group_id = p_chn_req_grp_id
1988 AND document_distribution_id = l_dist_id;
1989
1990 CURSOR l_dist_with_chn_csr(grp_id NUMBER, line_id NUMBER) IS
1991 SELECT
1992 change_request_id,
1993 new_quantity,
1994 document_distribution_id
1995 FROM po_change_requests
1996 WHERE change_request_group_id = grp_id
1997 AND document_line_id = line_id
1998 AND request_level = 'LINE';
1999
2000
2001 l_api_name CONSTANT VARCHAR(30) := 'Update_InternalRecordsWithTax';
2002 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
2003 l_progress VARCHAR2(3) := '000';
2004
2005 BEGIN
2006
2007 IF g_debug_stmt THEN
2008 po_debug.debug_begin(l_log_head);
2009 po_debug.debug_var(l_log_head, l_progress, 'p_chn_req_grp_id', p_chn_req_grp_id);
2010 END IF;
2011
2012 --OUTER LOOP: loops through req distribution records with quantity change, and get the req line ID.
2013 --Objective is to populate tax attributes for distribution records (with quantity change)
2014 OPEN l_line_id_with_qty_chn_csr(p_chn_req_grp_id);
2015 LOOP
2016 FETCH l_line_id_with_qty_chn_csr INTO
2017 l_line_id;
2018 EXIT WHEN l_line_id_with_qty_chn_csr%notfound;
2019
2020 BEGIN
2021 SELECT nvl(currency_unit_price, unit_price)
2022 INTO l_price
2023 FROM po_requisition_lines_all
2024 WHERE requisition_line_id = l_line_id;
2025 EXCEPTION WHEN OTHERS THEN
2026 l_price := NULL;
2027 END;
2028 l_progress := '001';
2029
2030 IF g_debug_stmt THEN
2031 po_debug.debug_var(l_log_head, l_progress, 'l_line_id', l_line_id);
2032 po_debug.debug_var(l_log_head, l_progress, 'l_price', l_price);
2033 END IF;
2034
2035
2036 --INNER LOOP: After getting the most recent price, update child distribution records with tax information.
2037 OPEN l_dist_with_chn_csr(p_chn_req_grp_id, l_line_id);
2038 LOOP
2039 FETCH l_dist_with_chn_csr INTO l_id, l_new_quantity, l_dist_id ;
2040 EXIT WHEN l_dist_with_chn_csr %notfound;
2041
2042 calculate_disttax(1.0, l_cal_disttax_status, l_dist_id, l_price, l_new_quantity, NULL, l_rec_tax, l_nonrec_tax);
2043
2044 l_progress := '002';
2045
2046 IF g_debug_stmt THEN
2047 po_debug.debug_var(l_log_head, l_progress, 'l_rec_tax', l_rec_tax);
2048 po_debug.debug_var(l_log_head, l_progress, 'l_nonrec_tax', l_nonrec_tax);
2049 po_debug.debug_stmt(l_log_head, l_progress,'Updating taxes in po_change_request table');
2050 END IF;
2051
2052
2053 UPDATE po_change_requests
2054 SET recoverable_tax = l_rec_tax,
2055 nonrecoverable_tax = l_nonrec_tax
2056 WHERE change_request_id = l_id;
2057
2058
2059 END LOOP;
2060 CLOSE l_dist_with_chn_csr;
2061 END LOOP;
2062 CLOSE l_line_id_with_qty_chn_csr;
2063
2064 EXCEPTION WHEN OTHERS THEN
2065 IF g_fnd_debug = 'Y' THEN
2066 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
2067 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
2068 l_api_name || '.others_exception',
2069 p_chn_req_grp_id || '*' || l_progress || ':' || SQLERRM);
2070 END IF;
2071 END IF;
2072 RAISE;
2073 END update_internalrecordswithtax;
2074 /*--------------------------------------------------------------------
2075 *Copy a requisition change record into a PO change record,
2076 *meanwhile updating the req change record with more information
2077 -----------------------------------------------------------------*/
2078 PROCEDURE copy_change(p_header_id NUMBER,
2079 p_release_id NUMBER,
2080 p_req_hdr_id NUMBER,
2081 req_change_record_tbl IN OUT NOCOPY change_tbl_type,
2082 req_index IN NUMBER,
2083 po_index IN NUMBER,
2084 po_change_record_tbl IN OUT NOCOPY pos_chg_rec_tbl)
2085 IS
2086 l_api_name VARCHAR2(50) := 'Copy_Change';
2087 l_progress VARCHAR2(3) := '000';
2088 l_po_doc_type VARCHAR2(30);
2089 l_po_num po_headers_all.segment1%TYPE;
2090 l_po_revision_num NUMBER;
2091 l_po_line_id NUMBER;
2092 l_po_line_number NUMBER;
2093 l_po_line_location_id NUMBER;
2094 l_po_shipment_number NUMBER;
2095 l_po_distribution_id NUMBER;
2096 l_po_distribution_number NUMBER;
2097 l_recoverable_tax NUMBER;
2098 l_non_recoverable_tax NUMBER;
2099 l_po_request_level po_change_requests.request_level%TYPE;
2100 l_req_request_level po_change_requests.request_level%TYPE;
2101 l_req_header_id NUMBER;
2102 l_req_num po_requisition_headers_all.segment1%TYPE;
2103 l_req_line_number NUMBER;
2104 l_req_dist_number NUMBER;
2105 l_new_functional_price NUMBER;
2106 l_old_curr_unit_price NUMBER;
2107 l_old_po_price NUMBER;
2108 l_old_req_price NUMBER;
2109 l_old_po_quantity NUMBER;
2110 l_old_req_quantity NUMBER;
2111 l_old_po_date DATE;
2112 l_old_req_date DATE;
2113 l_new_po_price NUMBER;
2114 l_new_po_quantity NUMBER;
2115 l_price_temp NUMBER;
2116 l_preparer_id NUMBER;
2117 l_po_ship_to_loc_id NUMBER;
2118 l_po_ship_to_org_id NUMBER;
2119 l_item_id NUMBER;
2120 l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;
2121 l_po_uom po_line_locations_all.unit_meas_lookup_code%TYPE;
2122 l_po_to_req_rate NUMBER;
2123 l_release_num NUMBER;
2124 l_old_start_date DATE;
2125 l_old_end_date DATE;
2126 l_old_amount NUMBER;
2127 l_old_curr_amount NUMBER;
2128 l_old_po_amount NUMBER;
2129 l_new_start_date DATE;
2130 l_new_end_date DATE;
2131 l_new_amount NUMBER;
2132 l_new_curr_amount NUMBER;
2133 l_new_po_amount NUMBER;
2134 l_new_functional_amount NUMBER;
2135 l_amount_temp NUMBER;
2136
2137 BEGIN
2138
2139
2140 IF(p_release_id IS NULL) THEN
2141 IF(req_change_record_tbl(req_index).document_distribution_id IS NOT NULL) THEN
2142
2143 --Standard PO Distribution Change
2144 l_po_request_level := 'DISTRIBUTION';
2145 l_req_request_level := 'DISTRIBUTION';
2146 l_progress := '001';
2147 SELECT
2148 'PO',
2149 pha.segment1,
2150 pha.revision_num,
2151 pla.po_line_id,
2152 pla.line_num,
2153 plla.line_location_id,
2154 plla.shipment_num,
2155 pda.po_distribution_id,
2156 pda.distribution_num,
2157 prha.segment1,
2158 prla.line_num,
2159 prda.distribution_num,
2160 prda.req_line_quantity,
2161 pda.quantity_ordered,
2162 prha.preparer_id,
2163 plla.ship_to_location_id,
2164 plla.ship_to_organization_id,
2165 prla.unit_meas_lookup_code,
2166 nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
2167 prla.item_id,
2168 pha.rate,
2169 prla.unit_price,
2170 prla.need_by_date,
2171 prla.assignment_start_date,
2172 prla.assignment_end_date,
2173 prda.req_line_amount,
2174 prda.req_line_currency_amount,
2175 pda.amount_ordered
2176 INTO
2177 l_po_doc_type,
2178 l_po_num,
2179 l_po_revision_num,
2180 l_po_line_id,
2181 l_po_line_number,
2182 l_po_line_location_id,
2183 l_po_shipment_number,
2184 l_po_distribution_id,
2185 l_po_distribution_number,
2186 l_req_num,
2187 l_req_line_number,
2188 l_req_dist_number,
2189 l_old_req_quantity,
2190 l_old_po_quantity,
2191 l_preparer_id,
2192 l_po_ship_to_loc_id,
2193 l_po_ship_to_org_id,
2194 l_req_uom,
2195 l_po_uom,
2196 l_item_id,
2197 l_po_to_req_rate,
2198 l_old_req_price,
2199 l_old_req_date,
2200 l_old_start_date,
2201 l_old_end_date,
2202 l_old_amount,
2203 l_old_curr_amount,
2204 l_old_po_amount
2205 FROM
2206 po_headers_all pha,
2207 po_lines_all pla,
2208 po_line_locations_all plla,
2209 po_req_distributions_all prda,
2210 po_requisition_lines_all prla,
2211 po_requisition_headers_all prha,
2212 po_distributions_all pda
2213 WHERE
2214 prda.distribution_id = req_change_record_tbl(req_index).document_distribution_id
2215 AND prda.requisition_line_id = prla.requisition_line_id
2216 AND prla.line_location_id = plla.line_location_id
2217 AND plla.po_line_id = pla.po_line_id
2218 AND pla.po_header_id = pha.po_header_id
2219 AND prha.requisition_header_id = prla.requisition_header_id
2220 AND pda.req_distribution_id = prda.distribution_id
2221 AND pda.line_location_id = prla.line_location_id;
2222
2223 ELSE
2224
2225 l_req_request_level := 'LINE';
2226
2227 l_progress := '002';
2228 SELECT
2229 'PO',
2230 pha.segment1,
2231 pha.revision_num,
2232 pla.po_line_id,
2233 pla.line_num,
2234 plla.line_location_id,
2235 plla.shipment_num,
2236 prha.segment1,
2237 prla.line_num,
2238 pla.unit_price,
2239 prla.currency_unit_price,
2240 prla.unit_price,
2241 prla.need_by_date,
2242 plla.need_by_date,
2243 prha.preparer_id,
2244 plla.ship_to_location_id,
2245 plla.ship_to_organization_id,
2246 prla.unit_meas_lookup_code,
2247 nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
2248 prla.item_id,
2249 pha.rate,
2250 prla.quantity,
2251 plla.quantity,
2252 prla.assignment_start_date,
2253 prla.assignment_end_date,
2254 prla.amount,
2255 prla.currency_amount,
2256 plla.amount
2257 INTO
2258 l_po_doc_type,
2259 l_po_num,
2260 l_po_revision_num,
2261 l_po_line_id,
2262 l_po_line_number,
2263 l_po_line_location_id,
2264 l_po_shipment_number,
2265 l_req_num,
2266 l_req_line_number,
2267 l_old_po_price,
2268 l_old_curr_unit_price,
2269 l_old_req_price,
2270 l_old_req_date,
2271 l_old_po_date,
2272 l_preparer_id,
2273 l_po_ship_to_loc_id,
2274 l_po_ship_to_org_id,
2275 l_req_uom,
2276 l_po_uom,
2277 l_item_id,
2278 l_po_to_req_rate,
2279 l_old_req_quantity,
2280 l_old_po_quantity,
2281 l_old_start_date,
2282 l_old_end_date,
2283 l_old_amount,
2284 l_old_curr_amount,
2285 l_old_po_amount
2286 FROM
2287 po_headers_all pha,
2288 po_lines_all pla,
2289 po_line_locations_all plla,
2290 po_requisition_lines_all prla,
2291 po_requisition_headers_all prha
2292 WHERE
2293 prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
2294 AND prla.line_location_id = plla.line_location_id
2295 AND plla.po_line_id = pla.po_line_id
2296 AND pla.po_header_id = pha.po_header_id
2297 AND prha.requisition_header_id = prla.requisition_header_id;
2298 IF(req_change_record_tbl(req_index).new_price IS NOT NULL) THEN
2299 l_po_request_level := 'LINE';
2300 l_po_line_location_id := NULL;
2301 l_po_shipment_number := NULL;
2302 l_po_ship_to_loc_id := NULL;
2303 l_po_ship_to_org_id := NULL;
2304 l_old_po_quantity := NULL;
2305 ELSE
2306 l_po_request_level := 'SHIPMENT';
2307 END IF;
2308
2309 END IF;
2310 ELSE
2311 IF(req_change_record_tbl(req_index).document_distribution_id IS NOT NULL) THEN
2312
2313 --Standard PO Distribution Change
2314 l_po_request_level := 'DISTRIBUTION';
2315 l_req_request_level := 'DISTRIBUTION';
2316 l_progress := '003';
2317 SELECT
2318 'PO',
2319 pha.segment1,
2320 pra.revision_num,
2321 plla.po_line_id,
2322 plla.line_location_id,
2323 plla.shipment_num,
2324 pda.po_distribution_id,
2325 pda.distribution_num,
2326 prha.segment1,
2327 prla.line_num,
2328 prda.distribution_num,
2329 prda.req_line_quantity,
2330 pda.quantity_ordered,
2331 prha.preparer_id,
2332 plla.ship_to_location_id,
2333 plla.ship_to_organization_id,
2334 prla.unit_meas_lookup_code,
2335 nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
2336 prla.item_id,
2337 pha.rate,
2338 prla.unit_price,
2339 prla.need_by_date,
2340 pra.release_num,
2341 prla.assignment_start_date,
2342 prla.assignment_end_date,
2343 prda.req_line_amount,
2344 prda.req_line_currency_amount,
2345 pda.amount_ordered
2346 INTO
2347 l_po_doc_type,
2348 l_po_num,
2349 l_po_revision_num,
2350 l_po_line_id,
2351 l_po_line_location_id,
2352 l_po_shipment_number,
2353 l_po_distribution_id,
2354 l_po_distribution_number,
2355 l_req_num,
2356 l_req_line_number,
2357 l_req_dist_number,
2358 l_old_req_quantity,
2359 l_old_po_quantity,
2360 l_preparer_id,
2361 l_po_ship_to_loc_id,
2362 l_po_ship_to_org_id,
2363 l_req_uom,
2364 l_po_uom,
2365 l_item_id,
2366 l_po_to_req_rate,
2367 l_old_req_price,
2368 l_old_req_date,
2369 l_release_num,
2370 l_old_start_date,
2371 l_old_end_date,
2372 l_old_amount,
2373 l_old_curr_amount,
2374 l_old_po_amount
2375 FROM
2376 po_headers_all pha,
2377 po_releases_all pra,
2378 po_lines_all pla,
2379 po_line_locations_all plla,
2380 po_distributions_all pda,
2381 po_req_distributions_all prda,
2382 po_requisition_lines_all prla,
2383 po_requisition_headers_all prha
2384 WHERE
2385 prda.distribution_id = req_change_record_tbl(req_index).document_distribution_id
2386 AND prda.distribution_id = pda.req_distribution_id
2387 AND pda.line_location_id = plla.line_location_id
2388 AND plla.po_release_id = pra.po_release_id
2389 AND pra.po_header_id = pha.po_header_id
2390 AND prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
2391 AND prha.requisition_header_id = prla.requisition_header_id
2392 AND prla.line_location_id = pda.line_location_id
2393 AND pla.po_line_id = plla.po_line_id;
2394
2395 ELSE
2396
2397 l_req_request_level := 'LINE';
2398 l_po_request_level := 'SHIPMENT';
2399 l_progress := '004';
2400 SELECT
2401 'PO',
2402 pha.segment1,
2403 pra.revision_num,
2404 plla.po_line_id,
2405 plla.line_location_id,
2406 plla.shipment_num,
2407 prha.segment1,
2408 prla.line_num,
2409 plla.price_override,
2410 prla.currency_unit_price,
2411 prla.unit_price,
2412 prla.need_by_date,
2413 plla.need_by_date,
2414 prha.preparer_id,
2415 plla.ship_to_location_id,
2416 plla.ship_to_organization_id,
2417 prla.unit_meas_lookup_code,
2418 nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
2419 prla.item_id,
2420 pha.rate,
2421 prla.quantity,
2422 plla.quantity,
2423 pra.release_num,
2424 prla.assignment_start_date,
2425 prla.assignment_end_date,
2426 prla.amount,
2427 prla.currency_amount,
2428 plla.amount
2429 INTO
2430 l_po_doc_type,
2431 l_po_num,
2432 l_po_revision_num,
2433 l_po_line_id,
2434 l_po_line_location_id,
2435 l_po_shipment_number,
2436 l_req_num,
2437 l_req_line_number,
2438 l_old_po_price,
2439 l_old_curr_unit_price,
2440 l_old_req_price,
2441 l_old_req_date,
2442 l_old_po_date,
2443 l_preparer_id,
2444 l_po_ship_to_loc_id,
2445 l_po_ship_to_org_id,
2446 l_req_uom,
2447 l_po_uom,
2448 l_item_id,
2449 l_po_to_req_rate,
2450 l_old_req_quantity,
2451 l_old_po_quantity,
2452 l_release_num,
2453 l_old_start_date,
2454 l_old_end_date,
2455 l_old_amount,
2456 l_old_curr_amount,
2457 l_old_po_amount
2458 FROM
2459 po_headers_all pha,
2460 po_releases_all pra,
2461 po_lines_all pla,
2462 po_line_locations_all plla,
2463 po_requisition_lines_all prla,
2464 po_requisition_headers_all prha
2465 WHERE
2466 prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
2467 AND prla.line_location_id = plla.line_location_id
2468 AND plla.po_release_id = pra.po_release_id
2469 AND pra.po_header_id = pha.po_header_id
2470 AND prha.requisition_header_id = prla.requisition_header_id
2471 AND pla.po_line_id = plla.po_line_id;
2472 END IF;
2473 END IF;
2474
2475 -- Calculate New PO Quantity based on UOM Conversion
2476 IF(l_req_uom <> l_po_uom) THEN
2477 po_uom_s.uom_convert(
2478 from_quantity => req_change_record_tbl(req_index).new_quantity,
2479 from_uom => l_req_uom,
2480 item_id => l_item_id,
2481 to_uom => l_po_uom,
2482 to_quantity => l_new_po_quantity);
2483 ELSE
2484 l_new_po_quantity := req_change_record_tbl(req_index).new_quantity;
2485 END IF;
2486
2487 -- Calculate New PO Price based on Currency Conversion
2488 IF(req_change_record_tbl(req_index).new_price IS NOT NULL) THEN
2489
2490 l_new_functional_price := calculate_newunitprice(req_change_record_tbl(req_index).document_line_id,
2491 req_change_record_tbl(req_index).new_price);
2492 IF(l_new_functional_price IS NOT NULL) THEN
2493 l_price_temp := req_change_record_tbl(req_index).new_price;
2494 req_change_record_tbl(req_index).new_price := l_new_functional_price;
2495 req_change_record_tbl(req_index).new_currency_unit_price := l_price_temp;
2496 req_change_record_tbl(req_index).old_currency_unit_price := l_old_curr_unit_price;
2497
2498 IF(l_po_to_req_rate IS NULL) THEN
2499 l_new_po_price := l_new_functional_price;
2500 ELSE
2501 l_new_po_price := l_new_functional_price / l_po_to_req_rate;
2502 END IF;
2503 ELSE
2504 IF(l_po_to_req_rate IS NULL) THEN
2505 l_new_po_price := req_change_record_tbl(req_index).new_price;
2506 ELSE
2507 l_new_po_price := req_change_record_tbl(req_index).new_price / l_po_to_req_rate;
2508 END IF;
2509 END IF;
2510 END IF;
2511
2512 l_new_start_date := req_change_record_tbl(req_index).new_start_date;
2513 l_new_end_date := req_change_record_tbl(req_index).new_end_date;
2514
2515 -- Calculate New PO AMOUNT based on Currency Conversion
2516 IF(req_change_record_tbl(req_index).new_budget_amount IS NOT NULL) THEN
2517
2518 l_new_functional_amount := calculate_newunitprice(req_change_record_tbl(req_index).document_line_id, req_change_record_tbl(req_index).new_budget_amount);
2519 IF(l_new_functional_amount IS NOT NULL) THEN
2520 l_amount_temp := req_change_record_tbl(req_index).new_budget_amount;
2521 req_change_record_tbl(req_index).new_budget_amount := l_new_functional_amount;
2522 req_change_record_tbl(req_index).new_currency_budget_amount := l_amount_temp;
2523 req_change_record_tbl(req_index).old_currency_budget_amount := l_old_curr_amount;
2524
2525 IF(l_po_to_req_rate IS NULL) THEN
2526 l_new_po_amount := l_new_functional_amount;
2527 ELSE
2528 l_new_po_amount := l_new_functional_amount / l_po_to_req_rate;
2529 END IF;
2530 ELSE
2531 IF(l_po_to_req_rate IS NULL) THEN
2532 l_new_po_amount := req_change_record_tbl(req_index).new_budget_amount;
2533 ELSE
2534 l_new_po_amount := req_change_record_tbl(req_index).new_budget_amount / l_po_to_req_rate;
2535 END IF;
2536 END IF;
2537 END IF;
2538
2539
2540 po_change_record_tbl(po_index) := po_chg_request_pvt.create_pos_change_rec(
2541 p_action_type => 'MODIFICATION',
2542 p_initiator => 'REQUESTER',
2543 p_document_type => l_po_doc_type,
2544 p_request_level => l_po_request_level,
2545 p_request_status => 'SYSTEMSAVE',
2546 p_document_header_id => p_header_id,
2547 p_request_reason => req_change_record_tbl(req_index).request_reason,
2548 p_po_release_id => p_release_id,
2549 p_document_num => l_po_num,
2550 p_document_revision_num => l_po_revision_num,
2551 p_document_line_id => l_po_line_id,
2552 p_document_line_number => l_po_line_number,
2553 p_document_line_location_id => l_po_line_location_id,
2554 p_document_shipment_number => l_po_shipment_number,
2555 p_document_distribution_id => l_po_distribution_id,
2556 p_document_distribution_number => l_po_distribution_number,
2557 p_parent_line_location_id => NULL,
2558 p_old_quantity => l_old_po_quantity, --OLD_QUANTITY
2559 p_new_quantity => l_new_po_quantity, -- NEW_QUANTITY **
2560 p_old_promised_date => NULL, -- OLD_PROMISED_DATE
2561 p_new_promised_date => NULL, --NEW_PROMISED_DATE
2562 p_old_supplier_part_number => NULL, --OLD_SUPPLIER_PART_NUMBER
2563 p_new_supplier_part_number => NULL, --NEW_SUPPLIER_PART_NUMBER
2564 p_old_price => l_old_po_price, --OLD_PRICE
2565 p_new_price => l_new_po_price, -- NEW_PRICE **
2566 p_old_supplier_reference_num => NULL, --OLD_SUPPLIER_REFERENCE_NUMBER
2567 p_new_supplier_reference_num => NULL, --NEW_SUPPLIER_REFERENCE_NUMBER
2568 p_from_header_id => NULL, --FROM_HEADER_ID
2569 p_recoverable_tax => NULL, --RECOVERABLE_TAX
2570 p_non_recoverable_tax => NULL, --NON_RECOVERABLE_TAX
2571 p_ship_to_location_id => l_po_ship_to_loc_id,-- SHIP_TO_LOCATION_ID
2572 p_ship_to_organization_id => l_po_ship_to_org_id,-- SHIP_TO_ORGANIZATION_ID
2573 p_old_need_by_date => l_old_po_date, --OLD_NEED_BY_DATE
2574 p_new_need_by_date => req_change_record_tbl(req_index).new_date, --NEW_NEED_BY_DATE
2575 p_approval_required_flag => NULL,
2576 p_parent_change_request_id => NULL,
2577 p_requester_id => NULL,
2578 p_old_supplier_order_number => NULL,
2579 p_new_supplier_order_number => NULL,
2580 p_old_supplier_order_line_num => NULL,
2581 p_new_supplier_order_line_num => NULL,
2582 p_additional_changes => NULL, -- additional_change
2583 p_old_start_date => l_old_start_date,
2584 p_new_start_date => l_new_start_date,
2585 p_old_expiration_date => l_old_end_date,
2586 p_new_expiration_date => l_new_end_date,
2587 p_old_amount => l_old_po_amount,
2588 p_new_amount => l_new_po_amount);
2589
2590
2591 req_change_record_tbl(req_index).action_type := 'MODIFICATION';
2592
2593 req_change_record_tbl(req_index).initiator := 'REQUESTER';
2594 req_change_record_tbl(req_index).request_level := l_req_request_level;
2595 req_change_record_tbl(req_index).request_status := 'SYSTEMSAVE';
2596 req_change_record_tbl(req_index).document_header_id := p_req_hdr_id;
2597 req_change_record_tbl(req_index).document_num := l_req_num;
2598 req_change_record_tbl(req_index).document_revision_num := l_po_revision_num;
2599
2600 req_change_record_tbl(req_index).document_line_number := l_req_line_number;
2601 req_change_record_tbl(req_index).document_distribution_number := l_req_dist_number;
2602
2603 req_change_record_tbl(req_index).old_quantity := l_old_req_quantity;
2604 req_change_record_tbl(req_index).old_price := l_old_req_price;
2605 req_change_record_tbl(req_index).old_date := l_old_req_date;
2606 req_change_record_tbl(req_index).requester_id := l_preparer_id;
2607
2608 req_change_record_tbl(req_index).referenced_po_header_id := p_header_id;
2609 req_change_record_tbl(req_index).referenced_po_document_num := l_po_num;
2610 req_change_record_tbl(req_index).referenced_release_id := p_release_id;
2611 req_change_record_tbl(req_index).referenced_release_num := l_release_num;
2612
2613 req_change_record_tbl(req_index).old_start_date := l_old_start_date;
2614 req_change_record_tbl(req_index).old_end_date := l_old_end_date;
2615 req_change_record_tbl(req_index).old_budget_amount := l_old_amount;
2616 req_change_record_tbl(req_index).old_currency_budget_amount := l_old_curr_amount;
2617
2618 --Exception Handling will be taken care of in parent procedures
2619 EXCEPTION WHEN OTHERS THEN
2620 IF g_fnd_debug = 'Y' THEN
2621 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
2622 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
2623 l_api_name || '.others_exception',
2624 l_progress || ':' || SQLERRM);
2625 END IF;
2626 END IF;
2627 RAISE;
2628 END copy_change;
2629
2630 /*--------------------------------------------------------------
2631 * IS_ON_COMPLEX_WORK_ORDER: For a particular Requisition dwishipment,
2632 * this API checks if the shipment is linked to a complex work order
2633 ----------------------------------------------------------------*/
2634 PROCEDURE is_on_complex_work_order(p_line_loc_id IN NUMBER,
2635 x_return_status OUT NOCOPY VARCHAR2)
2636 IS
2637 l_header_id po_line_locations_all.po_header_id%TYPE;
2638 l_api_name VARCHAR2(100) := 'is_on_complex_work_order()';
2639 BEGIN
2640
2641 x_return_status := 'N';
2642
2643 IF(p_line_loc_id IS NOT NULL) THEN
2644 SELECT po_header_id INTO l_header_id
2645 FROM po_line_locations_all
2646 WHERE line_location_id = p_line_loc_id;
2647
2648 --Call the PO API to check whether the passed PO is complex work order.
2649 IF (po_complex_work_pvt.is_complex_work_po(l_header_id)) THEN
2650 x_return_status := 'Y';
2651 END IF;
2652 END IF;
2653
2654 EXCEPTION
2655 WHEN OTHERS THEN
2656 x_return_status := fnd_api.g_ret_sts_unexp_error;
2657 fnd_msg_pub.add_exc_msg(g_module_prefix, l_api_name);
2658 END is_on_complex_work_order;
2659
2660
2661 /*-----------------------------------------------------------------------
2662 * IS_REQ_LINE_CANCELLABLE: checks if a requisition line can be cancelled.
2663 * It is called from 2 places
2664 * 1. Called from api IS_REQ_LINE_CHANGEABLE, with p_origin = 'Y'
2665 * 2. Called from the UI directly, with p_origin set to default (null)
2666 ------------------------------------------------------------------------*/
2667 PROCEDURE is_req_line_cancellable(p_api_version IN NUMBER,
2668 x_return_status OUT NOCOPY VARCHAR2,
2669 p_req_line_id IN NUMBER,
2670 p_origin IN VARCHAR2)
2671 IS
2672 l_api_name VARCHAR2(50) := 'IS_REQ_LINE_CANCELLABLE';
2673 l_progress VARCHAR2(3) := '000';
2674 l_auction_display_number NUMBER;
2675 l_auction_line_number NUMBER;
2676 l_reqs_in_pool_flag VARCHAR2(1);
2677 l_source_type_code po_requisition_lines_all.source_type_code%TYPE;
2678 l_return_status VARCHAR2(1);
2679 l_po_header_id NUMBER;
2680 l_po_release_id NUMBER;
2681 l_po_line_id NUMBER;
2682 l_po_line_loc_id NUMBER;
2683 l_po_doc_type VARCHAR2(30);
2684 l_po_doc_subtype VARCHAR2(30);
2685 l_count NUMBER;
2686 l_req_change_pending_flag VARCHAR2(1);
2687 l_agent_id NUMBER;
2688 l_modified_by_agent VARCHAR2(1);
2689 l_req_org_id NUMBER;
2690 l_po_org_id NUMBER;
2691 l_quantity NUMBER;
2692 l_received_quantity NUMBER;
2693 l_billed_quantity NUMBER;
2694 l_amount NUMBER;
2695 l_received_amount NUMBER;
2696 l_billed_amount NUMBER;
2697 l_receipt_required_flag po_line_locations.receipt_required_flag%TYPE;
2698 l_rcv_transaction_exist NUMBER := 0;
2699 l_asn_exist NUMBER := 0;
2700 l_not_delivered NUMBER := 0;
2701 l_dist_not_valid NUMBER := 0;
2702 l_is_on_complex_work_po VARCHAR2(1);
2703 l_transferred_to_oe_flag varchar(1) := null;
2704 l_cancelled varchar(1) :=null;
2705 is_so_cancel varchar2(1):='N';
2706 l_sts varchar2(3);
2707 BEGIN
2708
2709 x_return_status := fnd_api.g_ret_sts_success;
2710 /*
2711 * If called directly from the UI, we will need to make some extra validations,
2712 * which are also included in IS_REQ_LINE_CHANGEABLE.
2713 */
2714 IF (p_origin IS NULL) THEN
2715
2716 SELECT
2717 prla.source_type_code,
2718 prla.auction_display_number,
2719 prla.auction_line_number,
2720 prla.reqs_in_pool_flag,
2721 prla.line_location_id,
2722 prha.change_pending_flag,
2723 nvl(prla.modified_by_agent_flag, 'N'),
2724 prha.transferred_to_oe_flag,
2725 nvl(prla.cancel_flag,'N')
2726 INTO
2727 l_source_type_code,
2728 l_auction_display_number,
2729 l_auction_line_number,
2730 l_reqs_in_pool_flag,
2731 l_po_line_loc_id,
2732 l_req_change_pending_flag,
2733 l_modified_by_agent,
2734 l_transferred_to_oe_flag,
2735 l_cancelled
2736 FROM
2737 po_requisition_lines_all prla,
2738 po_requisition_headers_all prha
2739 WHERE
2740 prla.requisition_line_id = p_req_line_id AND
2741 prla.requisition_header_id = prha.requisition_header_id;
2742
2743
2744 IF (l_cancelled = 'Y' ) THEN
2745
2746 IF (g_fnd_debug = 'Y') THEN
2747 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
2748 fnd_log.string(fnd_log.level_statement,
2749 g_module_prefix || l_api_name,
2750 'Req Line ID:' || p_req_line_id || ' ' ||
2751 'is Already cancelled');
2752 END IF;
2753 END IF;
2754 x_return_status := fnd_api.g_ret_sts_error;
2755 RETURN;
2756 END IF;
2757
2758
2759 IF (l_source_type_code = 'INVENTORY') THEN
2760 IF ( nvl(l_transferred_to_oe_flag,'N') = 'Y' ) THEN
2761
2762 is_SO_line_cancellable(p_api_version =>1.0,
2763 x_return_status =>l_sts,
2764 p_req_line_id =>p_req_line_id,
2765 p_req_header_id =>null,
2766 x_cancellable =>is_so_cancel);
2767
2768 IF (g_fnd_debug = 'Y') THEN
2769 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
2770 fnd_log.string(fnd_log.level_statement,
2771 g_module_prefix || l_api_name,
2772 'Req Line ID:' || p_req_line_id || ' ' ||
2773 'transferred to so ='||is_so_cancel||' status'||l_sts);
2774 END IF;
2775 END IF;
2776 IF( l_sts <> fnd_api.g_ret_sts_success OR ( l_sts = fnd_api.g_ret_sts_success AND is_so_cancel = 'N') )THEN
2777 x_return_status := fnd_api.g_ret_sts_error;
2778 END IF;
2779 Else
2780 is_internal_line_cancellable(1.0, x_return_status, p_req_line_id);
2781 END IF;
2782 END IF;
2783
2784 ELSE
2785
2786 SELECT
2787 prla.line_location_id,
2788 prha.change_pending_flag,
2789 nvl(prla.modified_by_agent_flag, 'N')
2790 INTO
2791 l_po_line_loc_id,
2792 l_req_change_pending_flag,
2793 l_modified_by_agent
2794 FROM
2795 po_requisition_lines_all prla,
2796 po_requisition_headers_all prha
2797 WHERE
2798 prla.requisition_line_id = p_req_line_id AND
2799 prla.requisition_header_id = prha.requisition_header_id;
2800 END IF;
2801
2802 -- If the line is linked to a complex work PO, is not cancellable.
2803 IF(l_po_line_loc_id IS NOT NULL) THEN
2804 is_on_complex_work_order(l_po_line_loc_id, l_is_on_complex_work_po);
2805 IF(l_is_on_complex_work_po = 'Y') THEN
2806 x_return_status := fnd_api.g_ret_sts_error;
2807 END IF;
2808 END IF;
2809
2810 -- Line is placed on PO. Check if the req is in change pending status
2811 IF (l_req_change_pending_flag = 'Y' AND l_po_line_loc_id IS NOT NULL) THEN
2812 x_return_status := fnd_api.g_ret_sts_error;
2813 END IF;
2814
2815 IF (l_modified_by_agent = 'Y') THEN
2816 x_return_status := fnd_api.g_ret_sts_error;
2817 END IF;
2818
2819 l_progress := '001';
2820
2821 /*
2822 * Final Validation to check if a line can be cancelled will be done by
2823 * calling PO Cancel API to check if the corresponding shipment can be cancelled
2824 */
2825 IF (x_return_status = fnd_api.g_ret_sts_success AND l_po_line_loc_id IS NOT NULL) THEN
2826 IF (p_origin IS NULL) THEN
2827 SELECT COUNT(DISTINCT nvl(prda.requisition_line_id, - 1))
2828 INTO l_count
2829 FROM
2830 po_req_distributions_all prda,
2831 po_distributions_all pda,
2832 po_requisition_lines_all prla
2833 WHERE pda.line_location_id = prla.line_location_id
2834 AND prla.requisition_line_id = p_req_line_id
2835 AND pda.req_distribution_id = prda.distribution_id(+ );
2836
2837 IF (l_count > 1) THEN
2838 x_return_status := fnd_api.g_ret_sts_error;
2839 RETURN;
2840 END IF;
2841 END IF;
2842
2843 l_progress := '002';
2844
2845 SELECT
2846 pha.po_header_id,
2847 pla.po_line_id,
2848 plla.po_release_id,
2849 plla.line_location_id,
2850 pha.agent_id,
2851 prla.quantity - nvl(prla.quantity_cancelled, 0),
2852 plla.quantity_received,
2853 plla.quantity_billed,
2854 prla.amount,
2855 plla.amount_received,
2856 plla.amount_billed,
2857 plla.receipt_required_flag
2858 INTO
2859 l_po_header_id,
2860 l_po_line_id,
2861 l_po_release_id,
2862 l_po_line_loc_id,
2863 l_agent_id,
2864 l_quantity,
2865 l_received_quantity,
2866 l_billed_quantity,
2867 l_amount,
2868 l_received_amount,
2869 l_billed_amount,
2870 l_receipt_required_flag
2871 FROM
2872 po_headers_all pha,
2873 po_lines_all pla,
2874 po_line_locations_all plla,
2875 po_requisition_lines_all prla
2876 WHERE
2877 prla.requisition_line_id = p_req_line_id
2878 AND prla.line_location_id = plla.line_location_id
2879 AND plla.po_line_id = pla.po_line_id
2880 AND pla.po_header_id = pha.po_header_id;
2881
2882 l_progress := '003';
2883
2884 -- Bug : 3578699. If req line is fully received, user cannot cancel
2885 IF (l_received_quantity >= l_quantity OR
2886 l_received_amount >= l_amount) THEN
2887
2888 IF (g_fnd_debug = 'Y') THEN
2889 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
2890 fnd_log.string(fnd_log.level_statement,
2891 g_module_prefix || l_api_name,
2892 'Req Line ID:' || p_req_line_id || ' ' ||
2893 'Shipment Fully Received');
2894 END IF;
2895 END IF;
2896
2897 x_return_status := fnd_api.g_ret_sts_error;
2898 RETURN;
2899 END IF;
2900
2901 l_progress := '004';
2902
2903 -- Fully Billed Check (BUG: 3658317)
2904 IF (l_billed_quantity > l_quantity OR
2905 l_billed_amount > l_amount) THEN
2906
2907 IF (g_fnd_debug = 'Y') THEN
2908 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
2909 fnd_log.string(fnd_log.level_statement,
2910 g_module_prefix || l_api_name,
2911 'Req Line ID:' || p_req_line_id || ' ' ||
2912 'Shipment Fully Billed');
2913 END IF;
2914 END IF;
2915
2916 x_return_status := fnd_api.g_ret_sts_error;
2917 RETURN;
2918 END IF;
2919
2920 l_progress := '005';
2921
2922 -- Over Billed Check (If Match Approval Level is set to 3-way or 4-way only, this check is done)
2923 IF ((l_quantity >= l_billed_quantity AND
2924 nvl(l_receipt_required_flag, 'Y') <> 'N' AND
2925 l_billed_quantity > l_received_quantity) OR
2926 (l_amount >= l_billed_amount AND
2927 nvl(l_receipt_required_flag, 'Y') <> 'N' AND
2928 l_billed_amount > l_received_amount)) THEN
2929
2930 IF (g_fnd_debug = 'Y') THEN
2931 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
2932 fnd_log.string(fnd_log.level_statement,
2933 g_module_prefix || l_api_name,
2934 'Req Line ID:' || p_req_line_id || ' ' ||
2935 'Shipment Over Billed');
2936 END IF;
2937 END IF;
2938
2939 x_return_status := fnd_api.g_ret_sts_error;
2940 RETURN;
2941 END IF;
2942
2943 l_progress := '006';
2944
2945 -- Check for any Receiving Transaction exists
2946 BEGIN
2947 SELECT 1
2948 INTO l_rcv_transaction_exist
2949 FROM rcv_transactions_interface
2950 WHERE
2951 processing_status_code = 'PENDING' AND
2952 po_line_location_id = l_po_line_loc_id;
2953
2954 IF (l_rcv_transaction_exist = 1) THEN
2955
2956 IF (g_fnd_debug = 'Y') THEN
2957 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
2958 fnd_log.string(fnd_log.level_statement,
2959 g_module_prefix || l_api_name,
2960 'Req Line ID:' || p_req_line_id || ' ' ||
2961 'RCV transaction exists for Shipment');
2962 END IF;
2963 END IF;
2964
2965 x_return_status := fnd_api.g_ret_sts_error;
2966 RETURN;
2967 END IF;
2968
2969 EXCEPTION
2970 WHEN no_data_found THEN
2971 NULL;
2972 END;
2973
2974 l_progress := '007';
2975
2976 -- Check for ASN not being fully received
2977 BEGIN
2978 SELECT 1
2979 INTO l_asn_exist
2980 FROM rcv_shipment_lines
2981 WHERE
2982 po_line_location_id = l_po_line_loc_id AND
2983 nvl(quantity_shipped, 0) > nvl(quantity_received, 0) AND
2984 nvl(asn_line_flag, 'N') = 'Y' AND
2985 nvl(shipment_line_status_code, 'EXPECTED') <> 'CANCELLED';
2986
2987 IF (l_asn_exist = 1) THEN
2988
2989 IF (g_fnd_debug = 'Y') THEN
2990 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
2991 fnd_log.string(fnd_log.level_statement,
2992 g_module_prefix || l_api_name,
2993 'Req Line ID:' || p_req_line_id || ' ' ||
2994 'ASN not Fully Received');
2995 END IF;
2996 END IF;
2997
2998 x_return_status := fnd_api.g_ret_sts_error;
2999 RETURN;
3000 END IF;
3001
3002 EXCEPTION
3003 WHEN no_data_found THEN
3004 NULL;
3005 END;
3006
3007 l_progress := '008';
3008
3009 -- Check if shipment is received but not delivered
3010 BEGIN
3011
3012 SELECT 1
3013 INTO l_not_delivered
3014 FROM po_line_locations_all plla
3015 WHERE
3016 plla.line_location_id = l_po_line_loc_id AND
3017 ((nvl(plla.quantity_received, 0) >
3018 (SELECT SUM(nvl(pod.quantity_delivered, 0))
3019 FROM po_distributions pod
3020 WHERE pod.line_location_id = plla.line_location_id)) OR
3021 (nvl(plla.amount_received, 0) >
3022 (SELECT SUM(nvl(pod.amount_delivered, 0))
3023 FROM po_distributions pod
3024 WHERE pod.line_location_id = plla.line_location_id)));
3025
3026 IF (l_not_delivered = 1) THEN
3027
3028 IF (g_fnd_debug = 'Y') THEN
3029 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
3030 fnd_log.string(fnd_log.level_statement,
3031 g_module_prefix || l_api_name,
3032 'Req Line ID:' || p_req_line_id || ' ' ||
3033 'Shipment Received but not Delivered');
3034 END IF;
3035 END IF;
3036
3037 x_return_status := fnd_api.g_ret_sts_error;
3038 RETURN;
3039 END IF;
3040
3041 EXCEPTION
3042 WHEN no_data_found THEN
3043 NULL;
3044 END;
3045
3046 l_progress := '009';
3047
3048 -- PO distribution checks
3049 BEGIN
3050
3051 SELECT 1
3052 INTO l_dist_not_valid
3053 FROM
3054 po_line_locations_all poll,
3055 po_distributions_all pod,
3056 gl_code_combinations gcc
3057 WHERE
3058 poll.line_location_id = l_po_line_loc_id AND
3059 pod.line_location_id = poll.line_location_id AND
3060 gcc.code_combination_id = pod.code_combination_id AND
3061 ((trunc(SYSDATE) NOT BETWEEN
3062 nvl(gcc.start_date_active, trunc(SYSDATE) - 1) AND
3063 nvl(gcc.end_date_active, trunc(SYSDATE) + 1)
3064 ) OR
3065 pod.quantity_billed > pod.quantity_ordered OR -- fully billed
3066 pod.quantity_delivered > pod.quantity_ordered OR -- over delivered
3067 (pod.quantity_ordered >= pod.quantity_billed AND -- over billed
3068 nvl(poll.receipt_required_flag, 'Y') <> 'N' AND
3069 pod.quantity_billed > pod.quantity_delivered) OR
3070 pod.amount_billed > pod.amount_ordered OR
3071 pod.amount_delivered > pod.amount_ordered OR
3072 (pod.amount_ordered >= pod.amount_billed AND
3073 nvl(poll.receipt_required_flag, 'Y') <> 'N' AND
3074 pod.amount_billed > pod.amount_delivered));
3075
3076 IF (l_dist_not_valid = 1) THEN
3077
3078 IF (g_fnd_debug = 'Y') THEN
3079 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
3080 fnd_log.string(fnd_log.level_statement,
3081 g_module_prefix || l_api_name,
3082 'Req Line ID:' || p_req_line_id || ' ' ||
3083 'Distribution Checks Failed');
3084 END IF;
3085 END IF;
3086
3087 x_return_status := fnd_api.g_ret_sts_error;
3088 RETURN;
3089 END IF;
3090
3091 EXCEPTION
3092 WHEN no_data_found THEN
3093 NULL;
3094 END;
3095
3096 l_progress := '010';
3097
3098 IF (l_po_release_id IS NULL) THEN
3099 l_po_doc_type := 'PO';
3100 l_po_doc_subtype := 'STANDARD';
3101
3102 -- get org id of the PO
3103 SELECT org_id
3104 INTO l_po_org_id
3105 FROM po_headers_all
3106 WHERE po_header_id = l_po_header_id;
3107 ELSE
3108 l_po_doc_type := 'RELEASE';
3109 l_po_doc_subtype := 'BLANKET';
3110 l_po_line_id := NULL;
3111
3112 -- select org id of the release
3113 SELECT agent_id, org_id
3114 INTO l_agent_id, l_po_org_id
3115 FROM po_releases_all
3116 WHERE po_release_id = l_po_release_id;
3117 END IF;
3118
3119 l_progress := '011';
3120
3121 -- save current org id
3122 l_req_org_id := mo_global.get_current_org_id();
3123
3124 -- Set the org context before calling the cancel api
3125 po_moac_utils_pvt.set_org_context(l_po_org_id) ; -- <R12 MOAC>
3126
3127 l_progress := '012';
3128
3129 po_document_control_grp.check_control_action(
3130 p_api_version => 1.0,
3131 p_init_msg_list => fnd_api.g_true,
3132 x_return_status => l_return_status,
3133 p_doc_type => l_po_doc_type,
3134 p_doc_subtype => l_po_doc_subtype,
3135 p_doc_id => l_po_header_id,
3136 p_doc_num => NULL,
3137 p_release_id => l_po_release_id,
3138 p_release_num => NULL,
3139 p_doc_line_id => l_po_line_id,
3140 p_doc_line_num => NULL,
3141 p_doc_line_loc_id => l_po_line_loc_id,
3142 p_doc_shipment_num => NULL,
3143 p_action => 'CANCEL');
3144
3145 l_progress := '013';
3146
3147 -- set org context to the original value
3148 po_moac_utils_pvt.set_org_context(l_req_org_id) ; -- <R12 MOAC>
3149
3150 IF (g_fnd_debug = 'Y') THEN
3151 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
3152 fnd_log.string(fnd_log.level_statement,
3153 g_module_prefix || l_api_name,
3154 'PO_Document_Control_GRP.check_control_action result:' || l_return_status);
3155 END IF;
3156 END IF;
3157
3158 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
3159 x_return_status := fnd_api.g_ret_sts_success;
3160 ELSE
3161 x_return_status := fnd_api.g_ret_sts_error;
3162 END IF;
3163
3164 END IF;
3165 EXCEPTION WHEN OTHERS THEN
3166 x_return_status := fnd_api.g_ret_sts_unexp_error;
3167 IF (g_fnd_debug = 'Y') THEN
3168 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
3169 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
3170 l_api_name || '.others_exception',
3171 p_req_line_id || '*' || l_progress || ':' || SQLERRM);
3172 END IF;
3173 END IF;
3174 END is_req_line_cancellable;
3175
3176 /*--------------------------------------------------------------
3177 * IS_REQ_LINE_CHANGEABLE: For a particular Requisition line,
3178 * this API checks if the line be changed/cancelled, or if price,
3179 * date, and quantity associated with that the line can be changed.
3180 * Called Directly from UI, per req line.
3181 ----------------------------------------------------------------*/
3182 PROCEDURE is_req_line_changeable(p_api_version IN NUMBER,
3183 x_return_status OUT NOCOPY VARCHAR2,
3184 p_req_line_id IN NUMBER,
3185 p_price_changeable_flag OUT NOCOPY VARCHAR2,
3186 p_date_changeable_flag OUT NOCOPY VARCHAR2,
3187 p_qty_changeable_flag OUT NOCOPY VARCHAR2,
3188 p_start_date_changeable_flag OUT NOCOPY VARCHAR2,
3189 p_end_date_changeable_flag OUT NOCOPY VARCHAR2,
3190 p_amount_changeable_flag OUT NOCOPY VARCHAR2,
3191 p_cancellable_flag OUT NOCOPY VARCHAR2)
3192 IS
3193 l_api_name VARCHAR2(50) := 'IS_REQ_LINE_CHANGEABLE';
3194 l_progress VARCHAR2(100) := '000';
3195 l_line_changeable_flag VARCHAR2(1);
3196 l_line_loc_id NUMBER;
3197 l_source_type_code po_requisition_lines_all.source_type_code%TYPE;
3198 l_auction_display_number po_requisition_lines_all.auction_display_number%TYPE;
3199 l_auction_line_number NUMBER;
3200 l_reqs_in_pool_flag po_requisition_lines_all.reqs_in_pool_flag%TYPE;
3201 l_authorization_status po_headers_all.authorization_status%TYPE;
3202 l_frozen_flag VARCHAR2(1);
3203 l_closed_code po_headers_all.closed_code%TYPE;
3204 l_cancel_flag po_headers_all.cancel_flag%TYPE;
3205 l_catalog_type po_requisition_lines_all.catalog_type%TYPE;
3206 l_allow_price_override_flag po_lines_all.allow_price_override_flag%TYPE;
3207 l_accrue_on_receipt_flag po_line_locations_all.accrue_on_receipt_flag%TYPE;
3208 l_qty_received NUMBER;
3209 l_qty_billed NUMBER;
3210 l_count NUMBER;
3211 l_release_id NUMBER;
3212 l_return_status VARCHAR2(1);
3213 l_line_type po_line_types.purchase_basis%TYPE;
3214 l_global_agreement_flag po_headers_all.global_agreement_flag%TYPE;
3215 l_order_type_lookup_code po_requisition_lines_all.order_type_lookup_code%TYPE ;
3216 l_po_header_id po_headers_all.po_header_id%TYPE;
3217 l_template_id po_requisition_lines_all.noncat_template_id%TYPE;
3218 l_price_editable_flag por_noncat_templates_all_b.price_editable_flag%TYPE;
3219 l_amount_editable_flag por_noncat_templates_all_b.amount_editable_flag%TYPE;
3220 -- added for retroactive pricing checks
3221 l_retropricing VARCHAR2(20) := '';
3222 l_amount_based_service_line BOOLEAN := FALSE;
3223 l_destination_type_code po_requisition_lines_all.destination_type_code%TYPE;
3224 l_is_on_complex_work_po VARCHAR2(1);
3225 l_transferred_to_oe_flag varchar2(1);
3226 BEGIN
3227
3228
3229 x_return_status := fnd_api.g_ret_sts_success;
3230 l_line_changeable_flag := 'Y';
3231 p_price_changeable_flag := 'Y';
3232 p_date_changeable_flag := 'Y';
3233 p_qty_changeable_flag := 'Y';
3234 p_start_date_changeable_flag := 'Y';
3235 p_end_date_changeable_flag := 'Y';
3236 p_amount_changeable_flag := 'Y';
3237 p_cancellable_flag := 'Y';
3238
3239 l_progress := '000';
3240
3241 l_retropricing := fnd_profile.value('PO_ALLOW_RETROPRICING_OF_PO');
3242
3243 SELECT
3244 prla.purchase_basis,
3245 prla.line_location_id,
3246 prla.source_type_code,
3247 prla.auction_display_number,
3248 prla.auction_line_number,
3249 prla.reqs_in_pool_flag,
3250 nvl(prla.catalog_type,' '),
3251 prla.noncat_template_id,
3252 prla.destination_type_code,
3253 prha.transferred_to_oe_flag
3254 INTO
3255 l_line_type,
3256 l_line_loc_id,
3257 l_source_type_code,
3258 l_auction_display_number,
3259 l_auction_line_number,
3260 l_reqs_in_pool_flag,
3261 l_catalog_type,
3262 l_template_id,
3263 l_destination_type_code,
3264 l_transferred_to_oe_flag
3265 FROM
3266 po_requisition_lines_all prla,
3267 po_requisition_headers_all prha
3268 WHERE
3269 prla.requisition_line_id = p_req_line_id AND
3270 prla.requisition_header_id = prha.requisition_header_id;
3271
3272 -- for non catalog items with templates amount and price
3273 -- update depends on template definition
3274 IF (l_template_id IS NOT NULL) THEN
3275 SELECT price_editable_flag, amount_editable_flag
3276 INTO l_price_editable_flag, l_amount_editable_flag
3277 FROM por_noncat_templates_all_b
3278 WHERE template_id = l_template_id;
3279
3280 IF (l_price_editable_flag = 'N') THEN
3281 p_price_changeable_flag := 'N';
3282 END IF;
3283
3284 IF (l_amount_editable_flag = 'N') THEN
3285 p_amount_changeable_flag := 'N';
3286 END IF;
3287
3288 END IF;
3289
3290 -- If Req is linked to complex work.. RCO can not be done.
3291 is_on_complex_work_order(l_line_loc_id, l_is_on_complex_work_po);
3292
3293 IF(l_destination_type_code = 'EXPENSE' AND l_is_on_complex_work_po = 'Y') THEN
3294 l_line_changeable_flag := 'N';
3295 p_price_changeable_flag := 'N';
3296 p_date_changeable_flag := 'N';
3297 p_qty_changeable_flag := 'N';
3298 p_start_date_changeable_flag := 'N';
3299 p_end_date_changeable_flag := 'N';
3300 p_amount_changeable_flag := 'N';
3301 p_cancellable_flag := 'N';
3302 END IF;
3303
3304 IF(l_line_changeable_flag = 'Y') THEN
3305 IF (l_line_type = 'GOODS') THEN
3306 IF (l_template_id IS NULL) THEN
3307 p_amount_changeable_flag := 'N';
3308 END IF;
3309 p_start_date_changeable_flag := 'N';
3310 p_end_date_changeable_flag := 'N';
3311
3312 ELSIF(l_line_type = 'TEMP LABOR' OR l_line_type = 'SERVICES') THEN
3313
3314 p_qty_changeable_flag := 'N';
3315 p_price_changeable_flag := 'N';
3316 p_date_changeable_flag := 'N';
3317 p_start_date_changeable_flag := 'Y';
3318 p_end_date_changeable_flag := 'Y';
3319
3320 SELECT order_type_lookup_code
3321 INTO l_order_type_lookup_code
3322 FROM po_requisition_lines_all
3323 WHERE requisition_line_id = p_req_line_id;
3324
3325 IF (l_line_type = 'SERVICES') THEN
3326 p_date_changeable_flag := 'Y';
3327 p_start_date_changeable_flag := 'N';
3328 p_end_date_changeable_flag := 'N';
3329
3330 -- checking order type lookup code for amount based service lines
3331 IF (l_order_type_lookup_code <> 'FIXED PRICE') THEN
3332 p_qty_changeable_flag := 'Y';
3333 p_amount_changeable_flag := 'N';
3334 l_amount_based_service_line := TRUE;
3335 END IF;
3336
3337 END IF;
3338
3339 IF(l_line_type = 'TEMP LABOR' AND l_order_type_lookup_code = 'RATE') THEN
3340 -- for rate-based temp labor line, the budget amount can be modified
3341 p_amount_changeable_flag := 'Y';
3342
3343 ELSIF (NOT l_amount_based_service_line) THEN
3344 -- for fixed price temp labor line and fixed price services line,
3345 -- if there is a backing GBPA,
3346 -- if the price override allowed flag is Y, the labor amount can be changed
3347 -- if the price override allowed flag is N, the labor amount can not be changed.
3348 -- if there is not a backing GBPA, the labor anount can be changed
3349
3350 SELECT pha.global_agreement_flag, pha.po_header_id
3351 INTO l_global_agreement_flag, l_po_header_id
3352 FROM po_headers_all pha,
3353 po_requisition_lines_all prla
3354 WHERE pha.po_header_id (+ ) = prla.blanket_po_header_id
3355 AND prla.requisition_line_id = p_req_line_id;
3356
3357 IF(l_global_agreement_flag = 'Y') THEN
3358 BEGIN
3359 SELECT pla.allow_price_override_flag
3360 INTO l_allow_price_override_flag
3361 FROM po_requisition_lines_all prl,
3362 po_headers_all pha,
3363 po_lines_all pla
3364 WHERE pha.po_header_id = l_po_header_id
3365 AND pla.po_header_id = pha.po_header_id
3366 AND prl.blanket_po_line_num = pla.line_num
3367 AND prl.requisition_line_id = p_req_line_id;
3368
3369 -- in GCPA case, there is no po line associated with po header
3370 -- above query will result in no_data_found exception. catch GCPA case in exception block
3371
3372 IF(l_allow_price_override_flag = 'Y') THEN
3373 p_amount_changeable_flag := 'Y';
3374 ELSE
3375 p_amount_changeable_flag := 'N';
3376 END IF;
3377
3378 -- in GCPA case, user is allowed to change amount
3379
3380 EXCEPTION
3381 WHEN no_data_found THEN
3382 p_amount_changeable_flag := 'Y';
3383 END;
3384
3385
3386 ELSE
3387 p_amount_changeable_flag := 'Y';
3388 END IF;
3389 END IF;
3390
3391 END IF;
3392
3393 /*
3394 **Validation #1,#2,#3,#7 in DLD
3395 */
3396
3397
3398 l_progress := '001';
3399
3400 --In the following validation,
3401 --#1-#6 are shared by different type of requisition lines: GOODS, TEMP LABOR, SERVICES
3402 --#7-#11 are only for GOODS
3403
3404
3405 --Is there an internal order associated with line?(Validation #2 in DLD)
3406 IF (l_source_type_code = 'INVENTORY') THEN
3407 l_line_changeable_flag := 'N';
3408
3409 IF ( nvl(l_transferred_to_oe_flag,'N') = 'Y' ) THEN
3410 l_return_status := FND_API.G_RET_STS_ERROR;
3411 Else
3412 is_internal_line_cancellable(1.0, l_return_status, p_req_line_id);
3413 END IF;
3414
3415 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3416 p_cancellable_flag := 'N';
3417 END IF;
3418
3419 --Is there any PO associated with line?(Validation #1 in DLD)
3420 ELSIF(l_line_loc_id IS NULL) THEN
3421 l_line_changeable_flag := 'N';
3422 --Price change is allowed ONLY on non-catalog items(Validation #7 in DLD)
3423 ELSIF(l_line_type = 'GOODS' AND l_catalog_type <> 'NONCATALOG')THEN
3424 p_price_changeable_flag := 'N';
3425 END IF;
3426 END IF;-- end of l_line_changeable_flag='Y' check
3427
3428 l_progress := '002';
3429
3430 /*
3431 **Validation #4,#5,#8,#10,#11 in DLD
3432 */
3433 IF(l_line_changeable_flag = 'Y') THEN
3434
3435 SELECT po_release_id INTO l_release_id
3436 FROM po_line_locations_all
3437 WHERE line_location_id = l_line_loc_id;
3438
3439 IF(l_release_id IS NULL) THEN
3440
3441
3442 SELECT
3443 nvl(pha.authorization_status,' '),
3444 nvl(plla.closed_code, 'OPEN'),
3445 nvl(plla.cancel_flag, 'N'),
3446 pla.allow_price_override_flag,
3447 plla.accrue_on_receipt_flag,
3448 plla.quantity_received,
3449 plla.quantity_billed,
3450 nvl(pha.frozen_flag, 'N')
3451 INTO
3452 l_authorization_status ,
3453 l_closed_code,
3454 l_cancel_flag,
3455 l_allow_price_override_flag,
3456 l_accrue_on_receipt_flag,
3457 l_qty_received,
3458 l_qty_billed,
3459 l_frozen_flag
3460 FROM
3461 po_headers_all pha,
3462 po_lines_all pla,
3463 po_line_locations_all plla,
3464 po_requisition_lines_all prla
3465 WHERE pha.po_header_id = plla.po_header_id
3466 AND pla.po_line_id = plla.po_line_id
3467 AND plla.line_location_id = prla.line_location_id
3468 AND prla.requisition_line_id = p_req_line_id;
3469
3470 ELSE
3471
3472 SELECT
3473 nvl(pra.authorization_status,' '),
3474 nvl(plla.closed_code, 'OPEN'),
3475 nvl(plla.cancel_flag, 'N'),
3476 pla.allow_price_override_flag,
3477 plla.accrue_on_receipt_flag,
3478 plla.quantity_received,
3479 plla.quantity_billed,
3480 nvl(pra.frozen_flag,' ')
3481 INTO
3482 l_authorization_status ,
3483 l_closed_code,
3484 l_cancel_flag,
3485 l_allow_price_override_flag,
3486 l_accrue_on_receipt_flag,
3487 l_qty_received,
3488 l_qty_billed,
3489 l_frozen_flag
3490 FROM
3491 po_lines_all pla,
3492 po_line_locations_all plla,
3493 po_requisition_lines_all prla,
3494 po_releases_all pra
3495 WHERE pla.po_line_id = plla.po_line_id
3496 AND plla.line_location_id = prla.line_location_id
3497 AND prla.requisition_line_id = p_req_line_id
3498 AND pra.po_release_id = plla.po_release_id;
3499
3500
3501 END IF;
3502 l_progress := '003';
3503 --Check authorization status, closed code, cancel flag (Validation #4 and #5 in DLD)
3504 IF(l_frozen_flag = 'Y' OR l_authorization_status NOT IN('APPROVED') OR l_closed_code IN ('CLOSED','FINALLY CLOSED') OR l_cancel_flag <> 'N') THEN
3505 l_line_changeable_flag := 'N';
3506 p_cancellable_flag := 'N';
3507
3508 ELSIF (l_closed_code IN ('CLOSED FOR INVOICE', 'CLOSED FOR RECEIVING') AND l_retropricing <> 'ALL_RELEASES') THEN
3509 p_price_changeable_flag := 'N';
3510
3511 --Check for price override flag (Validation #8)
3512 ELSIF(l_line_type = 'GOODS' AND l_allow_price_override_flag = 'N') THEN
3513 p_price_changeable_flag := 'N';
3514
3515 --Check accrue on receipt flag (Validation #10)
3516 ELSIF(l_line_type = 'GOODS' AND l_accrue_on_receipt_flag = 'Y' AND
3517 l_retropricing <> 'ALL_RELEASES') THEN
3518 p_price_changeable_flag := 'N';
3519
3520 --If PO line has been partially received or invoiced (Validation #11)
3521 ELSIF(l_line_type = 'GOODS' AND l_retropricing <> 'ALL_RELEASES' AND
3522 (l_qty_received > 0 OR l_qty_billed >0)) THEN
3523 p_price_changeable_flag := 'N';
3524 END IF;
3525
3526
3527
3528 /*
3529 * Valiation #6: Checking if p_req_line_id corresponds to PO Shipment PS1, and PS1 has multiple PO distributions, which correspond to
3530 * multiple req distributions of multiple req lines or does not correspond to any req distributions:
3531 * In this case, no change, no cancel
3532 */
3533
3534 IF(l_line_changeable_flag = 'Y') THEN
3535 l_progress := '004';
3536 SELECT COUNT(DISTINCT nvl(prda.requisition_line_id, - 1))
3537 INTO l_count
3538 FROM
3539 po_req_distributions_all prda,
3540 po_distributions_all pda,
3541 po_requisition_lines_all prla
3542 WHERE pda.line_location_id = prla.line_location_id
3543 AND prla.requisition_line_id = p_req_line_id
3544 AND pda.req_distribution_id = prda.distribution_id(+ );
3545
3546 IF(l_count > 1) THEN
3547 l_line_changeable_flag := 'N';
3548 p_cancellable_flag := 'N';
3549 END IF;
3550 END IF;
3551
3552 IF(l_line_changeable_flag = 'N') THEN
3553 p_price_changeable_flag := 'N';
3554 p_date_changeable_flag := 'N';
3555 p_qty_changeable_flag := 'N';
3556 p_start_date_changeable_flag := 'N';
3557 p_end_date_changeable_flag := 'N';
3558 p_amount_changeable_flag := 'N';
3559 END IF;
3560
3561 /*
3562 * Extending Validation #6, if a req line correspond to the line of a Standard PO, its price can only be changed if
3563 * the req line is associated with a PO Shipment, which is the only shipment of its parent Line.
3564 */
3565 IF(l_release_id IS NULL AND p_price_changeable_flag = 'Y') THEN
3566 l_progress := '005';
3567 SELECT COUNT(1)
3568 INTO l_count
3569 FROM
3570 po_requisition_lines_all prla,
3571 po_line_locations_all plla,
3572 po_line_locations_all plla2
3573 WHERE plla.line_location_id = prla.line_location_id
3574 AND prla.requisition_line_id = p_req_line_id
3575 AND plla2.po_line_id = plla.po_line_id;
3576
3577 IF(l_count > 1) THEN
3578 p_price_changeable_flag := 'N';
3579
3580 END IF;
3581
3582 END IF;
3583
3584
3585 END IF;
3586
3587
3588 l_progress := '007';
3589
3590 /*
3591 * At this point, if cancel_flag = 'Y', we will need to call another API which further checks if the line
3592 * can be cancelled.
3593 */
3594 IF(p_cancellable_flag = 'Y') THEN
3595 is_req_line_cancellable(1.0, l_return_status, p_req_line_id, 'Y');
3596 IF(l_return_status = fnd_api.g_ret_sts_error) THEN
3597 p_cancellable_flag := 'N';
3598 END IF;
3599 END IF;
3600
3601 EXCEPTION WHEN OTHERS THEN
3602 IF g_fnd_debug = 'Y' THEN
3603 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
3604 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
3605 l_api_name || '.others_exception',
3606 p_req_line_id || '*' || l_progress || ':' || SQLERRM);
3607 END IF;
3608 END IF;
3609 x_return_status := fnd_api.g_ret_sts_unexp_error;
3610 END is_req_line_changeable;
3611
3612 /*-------------------------------------------------------------------------------------------------
3613 *This API is called directly from the UI. It will have PLSQL tables as input, which contain change/cancel requests
3614 *1. Validate the requests
3615 *2. If ALL valid, same them into PO_CHANGE_REQUESTS table
3616 *x_return_status = FND_API.G_RET_STS_SUCCESS => Everything is Valid, and records are saved into change table
3617 * FND_API.G_RET_STS_ERROR => Caught Errors, thus no records are saved into change table
3618 * FND_API.G_RET_STS_UNEXP_ERROR => Unexpected Errors Occur in the API
3619 *x_retMsg will indicate details/location of errors.
3620 ---------------------------------------------------------------------------------------------------*/
3621 PROCEDURE save_reqchange(p_api_version IN NUMBER,
3622 x_return_status OUT NOCOPY VARCHAR2,
3623 p_req_hdr_id IN NUMBER,
3624 p_change_table IN po_req_change_table,
3625 p_cancel_table IN po_req_cancel_table,
3626 p_change_request_group_id OUT NOCOPY NUMBER,
3627 x_retmsg OUT NOCOPY VARCHAR2,
3628 x_errtable OUT NOCOPY po_req_change_err_table)
3629 IS
3630 l_api_name VARCHAR2(50) := 'Save_ReqChange';
3631 l_req_change_table change_tbl_type;
3632 l_dummy NUMBER;
3633 y NUMBER := 1;
3634 l_change_result VARCHAR2(1) := fnd_api.g_ret_sts_success;
3635 l_cancel_result VARCHAR2(1);
3636 l_err_line_id_tbl po_tbl_number;
3637 l_err_line_num_tbl po_tbl_number;
3638 l_err_dist_id_tbl po_tbl_number;
3639 l_err_dist_num_tbl po_tbl_number;
3640 l_err_error_attr_tbl po_tbl_varchar30;
3641 l_err_msg_count_tbl po_tbl_number;
3642 l_err_msg_data_tbl po_tbl_varchar2000;
3643 l_irc_status VARCHAR2(1);
3644 l_irc_err_msg VARCHAR2(2000);
3645 l_catch_exception EXCEPTION;
3646 l_req_dist_id NUMBER;
3647 l_lineqty_status VARCHAR2(1);
3648 l_lineqty_msg VARCHAR2(2000);
3649
3650
3651 BEGIN
3652
3653 DELETE FROM po_change_requests
3654 WHERE document_header_id = p_req_hdr_id
3655 AND request_status = 'SYSTEMSAVE'
3656 AND initiator = 'REQUESTER';
3657
3658
3659
3660 x_retmsg := 'SRCH000';
3661
3662 IF(p_change_table IS NOT NULL) THEN
3663 --Input Change Table is p_change_table, which is a table of objects. The following "for" loop
3664 --Copy the data from p_change_table to l_req_change_table, which is a table of record
3665 FOR x IN 1..p_change_table.req_line_id.count
3666 LOOP
3667 /* Start Date, End Date and Amount changes
3668 Check if there is any change in start date or end date or amount
3669 Set DOCUMENT_DISTRIBUTION_ID only when there is any change in amount
3670 Handled Amount and Need By Date Combination for Fixed Price Lines*/
3671 IF((p_change_table.need_by(x) IS NOT NULL AND p_change_table.amount(x) IS NOT NULL)
3672 OR p_change_table.start_date(x) IS NOT NULL
3673 OR p_change_table.end_date(x) IS NOT NULL
3674 OR p_change_table.amount(x) IS NOT NULL) THEN
3675 IF(p_change_table.start_date(x) IS NOT NULL) THEN
3676 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3677 l_req_change_table(y).new_start_date := p_change_table.start_date(x);
3678 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3679 y := y + 1;
3680 END IF;
3681 IF(p_change_table.end_date(x) IS NOT NULL) THEN
3682 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3683 l_req_change_table(y).new_end_date := p_change_table.end_date(x);
3684 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3685 y := y + 1;
3686 END IF;
3687 IF(p_change_table.amount(x) IS NOT NULL) THEN
3688 IF(p_change_table.req_dist_id(x) IS NULL) THEN
3689 BEGIN
3690 SELECT distribution_id
3691 INTO l_req_dist_id
3692 FROM po_req_distributions_all
3693 WHERE requisition_line_id = p_change_table.req_line_id(x);
3694 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3695 l_req_change_table(y).document_distribution_id := l_req_dist_id;
3696 l_req_change_table(y).new_budget_amount := p_change_table.amount(x);
3697 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3698 y := y + 1;
3699 EXCEPTION
3700 WHEN OTHERS THEN
3701 NULL;
3702 END;
3703 ELSE
3704 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3705 l_req_change_table(y).document_distribution_id := p_change_table.req_dist_id(x);
3706 l_req_change_table(y).new_budget_amount := p_change_table.amount(x);
3707 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3708 y := y + 1;
3709 END IF;
3710 IF (p_change_table.need_by(x) IS NOT NULL) THEN
3711 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3712 l_req_change_table(y).new_date := p_change_table.need_by(x);
3713 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3714 y := y + 1;
3715 END IF;
3716 END IF;
3717 /* Price, Need By, Quantity changes
3718 Check if there is any change in price or need_by or quantity
3719 Set DOCUMENT_DISTRIBUTION_ID only when there is any change in quantity */
3720 ELSIF(p_change_table.price(x) IS NOT NULL
3721 OR p_change_table.need_by(x) IS NOT NULL
3722 OR p_change_table.quantity(x) IS NOT NULL) THEN
3723 IF(p_change_table.need_by(x) IS NOT NULL) THEN
3724 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3725 l_req_change_table(y).new_date := p_change_table.need_by(x);
3726 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3727 y := y + 1;
3728 END IF;
3729 IF(p_change_table.price(x) IS NOT NULL) THEN
3730 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3731 l_req_change_table(y).new_price := p_change_table.price(x);
3732 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3733 y := y + 1;
3734 END IF;
3735 IF(p_change_table.quantity(x) IS NOT NULL) THEN
3736 IF(p_change_table.req_dist_id(x) IS NULL) THEN
3737 BEGIN
3738 SELECT distribution_id
3739 INTO l_req_dist_id
3740 FROM po_req_distributions_all
3741 WHERE requisition_line_id = p_change_table.req_line_id(x);
3742 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3743 l_req_change_table(y).document_distribution_id := l_req_dist_id;
3744 l_req_change_table(y).new_quantity := p_change_table.quantity(x);
3745 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3746 y := y + 1;
3747 EXCEPTION
3748 WHEN OTHERS THEN
3749 NULL;
3750 END;
3751 ELSE
3752 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
3753 l_req_change_table(y).document_distribution_id := p_change_table.req_dist_id(x);
3754 l_req_change_table(y).new_quantity := p_change_table.quantity(x);
3755 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
3756 y := y + 1;
3757 END IF;
3758 END IF;
3759 END IF;
3760
3761 END LOOP;
3762
3763
3764
3765 --Validate the Change Requests, by passing in l_req_change_table, a table of records
3766 --Initialize the Error Table
3767 l_err_line_id_tbl := po_tbl_number();
3768 l_err_line_num_tbl := po_tbl_number();
3769 l_err_dist_id_tbl := po_tbl_number();
3770 l_err_dist_num_tbl := po_tbl_number();
3771 l_err_error_attr_tbl := po_tbl_varchar30();
3772 l_err_msg_count_tbl := po_tbl_number();
3773 l_err_msg_data_tbl := po_tbl_varchar2000();
3774 x_errtable := po_req_change_err_table(
3775 l_err_line_id_tbl,
3776 l_err_line_num_tbl,
3777 l_err_dist_id_tbl,
3778 l_err_dist_num_tbl,
3779 l_err_error_attr_tbl,
3780 l_err_msg_count_tbl,
3781 l_err_msg_data_tbl);
3782
3783 validate_changes(p_req_hdr_id, l_req_change_table, l_change_result, x_retmsg, x_errtable);
3784 END IF;
3785
3786 --If ALL changes are valid, we will insert change records, and insert cancel records(if any)
3787 IF(l_change_result = fnd_api.g_ret_sts_success) THEN
3788 x_retmsg := 'SRCH004';
3789 SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
3790
3791 insert_reqchange(l_req_change_table, p_change_request_group_id);
3792 x_retmsg := 'SRCH005';
3793
3794 update_recordswithtax(p_change_request_group_id);
3795 x_retmsg := 'SRCH006';
3796
3797 insert_linequantityoramount(p_change_request_group_id);
3798
3799 x_retmsg := 'SRCH0061';
3800
3801 insert_pricebreakrows(p_change_request_group_id);
3802
3803 x_retmsg := 'SRCH0062';
3804
3805 --Process Cancellation Requests
3806 l_cancel_result := fnd_api.g_ret_sts_success;
3807
3808 IF(p_cancel_table IS NOT NULL) THEN
3809 save_reqcancel(1.0, l_cancel_result, p_req_hdr_id, p_cancel_table, l_dummy, x_retmsg, p_change_request_group_id);
3810 END IF;
3811
3812 x_return_status := l_cancel_result;
3813 ELSIF(l_change_result = fnd_api.g_ret_sts_error) THEN
3814 x_return_status := fnd_api.g_ret_sts_error;
3815 x_retmsg := 'SRCH007';
3816 ELSE
3817 x_return_status := fnd_api.g_ret_sts_error;
3818
3819 END IF;
3820
3821 EXCEPTION
3822 WHEN OTHERS THEN
3823 x_retmsg := 'SRCHUNEXP:' || x_retmsg || ':' || SQLERRM;
3824 x_return_status := fnd_api.g_ret_sts_unexp_error;
3825 IF g_fnd_debug = 'Y' THEN
3826 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
3827 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
3828 l_api_name || '.others_exception', x_retmsg);
3829 END IF;
3830 END IF;
3831 END save_reqchange;
3832
3833 /*--------------------------------------------------------------
3834 **Save_ReqCancel: takes in a PLSQL table as input, containing
3835 **cancellation request. No Validation is done here. This API
3836 **simply insert records into PO_CHANGE_REQUESTS table
3837 ---------------------------------------------------------------*/
3838 PROCEDURE save_reqcancel(p_api_version IN NUMBER,
3839 x_return_status OUT NOCOPY VARCHAR2,
3840 p_req_hdr_id IN NUMBER,
3841 p_cancel_table IN po_req_cancel_table,
3842 p_change_request_group_id OUT NOCOPY NUMBER,
3843 x_retmsg OUT NOCOPY VARCHAR2,
3844 p_grp_id IN NUMBER)
3845 IS
3846 l_api_name VARCHAR2(50) := 'Save_ReqCancel';
3847 l_chn_req_id NUMBER;
3848 l_req_num po_requisition_headers_all.segment1%TYPE;
3849 l_req_line_num NUMBER;
3850 l_req_user_id NUMBER;
3851 l_line_loc_id NUMBER;
3852 l_po_header_id NUMBER;
3853 l_po_release_id NUMBER;
3854 l_po_revision_num NUMBER;
3855 l_preparer_id NUMBER;
3856 l_req_price NUMBER;
3857 l_req_currency_price NUMBER;
3858 l_req_quantity NUMBER;
3859 l_req_date DATE;
3860 l_po_num po_change_requests.ref_po_num%TYPE;
3861 l_po_release_num NUMBER;
3862 BEGIN
3863 x_retmsg := '000';
3864 l_req_user_id := fnd_global.user_id;
3865
3866
3867 IF(p_grp_id IS NULL) THEN
3868 SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
3869 DELETE FROM po_change_requests
3870 WHERE document_header_id = p_req_hdr_id
3871 AND initiator = 'REQUESTER'
3872 AND request_status = 'SYSTEMSAVE';
3873 ELSE
3874 p_change_request_group_id := p_grp_id;
3875 END IF;
3876 x_retmsg := '001';
3877 FOR i IN 1..p_cancel_table.req_line_id.count
3878 LOOP
3879
3880 SELECT po_chg_request_seq.nextval INTO l_chn_req_id FROM dual;
3881 SELECT
3882 prha.segment1,
3883 prla.line_num,
3884 prla.line_location_id,
3885 prha.preparer_id,
3886 prla.unit_price,
3887 prla.quantity,
3888 prla.need_by_date,
3889 prla.currency_unit_price
3890 INTO
3891 l_req_num,
3892 l_req_line_num,
3893 l_line_loc_id,
3894 l_preparer_id,
3895 l_req_price,
3896 l_req_quantity,
3897 l_req_date,
3898 l_req_currency_price
3899 FROM
3900 po_requisition_headers_all prha,
3901 po_requisition_lines_all prla
3902 WHERE prla.requisition_line_id = p_cancel_table.req_line_id(i)
3903 AND prla.requisition_header_id = prha.requisition_header_id;
3904
3905 IF(l_line_loc_id IS NOT NULL) THEN
3906 SELECT
3907 po_release_id,
3908 po_header_id
3909 INTO
3910 l_po_release_id,
3911 l_po_header_id
3912 FROM po_line_locations_all
3913 WHERE line_location_id = l_line_loc_id;
3914 IF(l_po_release_id IS NULL) THEN
3915 SELECT revision_num, segment1 INTO
3916 l_po_revision_num, l_po_num
3917 FROM po_headers_all
3918 WHERE po_header_id = l_po_header_id;
3919
3920 -- bug 5191164.
3921 -- Need to null out l_po_release_num for PO records
3922 l_po_release_num := NULL;
3923
3924 ELSE
3925 -- get po_number of the source document for RELEASE
3926 SELECT segment1 INTO l_po_num
3927 FROM po_headers_all
3928 WHERE po_header_id = l_po_header_id;
3929
3930 SELECT revision_num, release_num
3931 INTO l_po_revision_num, l_po_release_num
3932 FROM po_releases_all
3933 WHERE po_release_id = l_po_release_id;
3934 END IF;
3935 END IF;
3936 x_retmsg := '002';
3937 INSERT INTO po_change_requests
3938 (
3939 change_request_group_id,
3940 change_request_id,
3941 initiator,
3942 action_type,
3943 request_reason,
3944 request_level,
3945 request_status,
3946 document_type,
3947 document_header_id,
3948 document_num,
3949 document_revision_num,
3950 created_by,
3951 creation_date,
3952 document_line_id,
3953 document_line_number,
3954 last_updated_by,
3955 last_update_date,
3956 last_update_login,
3957 requester_id,
3958 change_active_flag,
3959 old_price,
3960 old_quantity,
3961 old_need_by_date,
3962 old_currency_unit_price,
3963 ref_po_header_id,
3964 ref_po_num,
3965 ref_po_release_id,
3966 ref_po_rel_num )
3967 VALUES
3968 (
3969 p_change_request_group_id,
3970 l_chn_req_id,
3971 'REQUESTER',
3972 'CANCELLATION',
3973 p_cancel_table.change_reason(i),
3974 'LINE',
3975 'SYSTEMSAVE',
3976 'REQ',
3977 p_req_hdr_id,
3978 l_req_num,
3979 l_po_revision_num,
3980 l_req_user_id,
3981 SYSDATE,
3982 p_cancel_table.req_line_id(i),
3983 l_req_line_num,
3984 l_req_user_id,
3985 SYSDATE,
3986 l_req_user_id,
3987 l_preparer_id,
3988 'Y',
3989 l_req_price,
3990 l_req_quantity,
3991 l_req_date,
3992 l_req_currency_price,
3993 l_po_header_id,
3994 l_po_num,
3995 l_po_release_id,
3996 l_po_release_num
3997 );
3998
3999
4000 END LOOP;
4001
4002
4003 x_return_status := fnd_api.g_ret_sts_success;
4004 EXCEPTION WHEN OTHERS THEN
4005 x_return_status := fnd_api.g_ret_sts_unexp_error;
4006 IF g_fnd_debug = 'Y' THEN
4007 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
4008 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
4009 l_api_name || '.others_exception', x_retmsg || ':' || SQLERRM);
4010 END IF;
4011 END IF;
4012 END save_reqcancel;
4013
4014 /*-----------------------------------------------------------------------------------------------------
4015 * At the Final Stage of Requester Creating Change request, SUBMIT_REQCHANGE will be executed to complete
4016 * the transaction. This API takes care of funds Check, and does a final round of validation against
4017 * all change/cancel requests before kicking off the Workflow.
4018 ----------------------------------------------------------------------------------------------------*/
4019 PROCEDURE submit_reqchange (
4020 p_api_version IN NUMBER,
4021 x_return_status OUT NOCOPY VARCHAR2,
4022 p_group_id IN NUMBER,
4023 p_fundscheck_flag IN VARCHAR2,
4024 p_note_to_approver IN VARCHAR2,
4025 p_initiator IN VARCHAR2,
4026 x_retmsg OUT NOCOPY VARCHAR2,
4027 x_errcode OUT NOCOPY VARCHAR2,
4028 x_errtable OUT NOCOPY po_req_change_err_table)
4029 IS
4030 l_api_name VARCHAR2(50) := 'Submit_ReqChange';
4031 i NUMBER := 1;
4032 l_cancelerrorsize NUMBER;
4033 l_flag_one VARCHAR2(1);
4034 l_flag_two VARCHAR2(1);
4035 --l_FC_Tbl po_fcin_type;
4036 l_req_dist_id NUMBER;
4037 l_req_line_id NUMBER;
4038 l_req_hdr_id NUMBER;
4039 l_req_num po_requisition_headers_all.segment1%TYPE;
4040 l_po_num po_headers_all.segment1%TYPE;
4041 l_header_id NUMBER;
4042 l_release_id NUMBER;
4043 l_line_id NUMBER;
4044 l_shipment_id NUMBER;
4045 l_distribution_id NUMBER;
4046 l_budget_account_id NUMBER;
4047 l_gl_date DATE;
4048 l_vendor_id NUMBER;
4049 l_old_price NUMBER;
4050 l_old_quantity NUMBER;
4051 l_old_tax NUMBER;
4052 l_ship_to_org_id NUMBER;
4053 l_ship_to_loc_id NUMBER;
4054 l_price_changed_flag VARCHAR2(1) := fnd_api.g_false;
4055 l_qty_changed_flag VARCHAR2(1) := fnd_api.g_false;
4056 l_change_exist VARCHAR2(1);
4057 l_cancel_exist VARCHAR2(1);
4058 l_new_price NUMBER;
4059 l_new_quantity NUMBER;
4060 l_new_po_quantity NUMBER;
4061 l_rec_tax NUMBER;
4062 l_nonrec_tax NUMBER;
4063 l_new_tax NUMBER;
4064 l_entered_dr NUMBER;
4065 l_entered_cr NUMBER;
4066 l_org_id NUMBER;
4067 l_fc_out_tbl po_fcout_type;
4068 l_fc_result_code VARCHAR2(1);
4069 l_fc_result_status VARCHAR2(1);
4070 l_fc_msg_count NUMBER;
4071 l_fc_msg_data VARCHAR2(2000);
4072 l_fc_req_line_id NUMBER;
4073 l_fc_req_line_num NUMBER;
4074 l_fc_req_distr_id NUMBER;
4075 l_fc_req_distr_num NUMBER;
4076 l_req_change_table change_tbl_type;
4077 l_new_date DATE;
4078 l_new_need_by_date DATE;
4079 l_old_need_by_date DATE;
4080 l_old_amount NUMBER;
4081 l_new_amount NUMBER;
4082 l_price_break VARCHAR2(1);
4083
4084 l_request_reason po_change_requests.request_reason%TYPE;
4085 l_cancel_errtable po_req_change_err_table;
4086 l_cal_disttax_status VARCHAR2(1);
4087 l_item_id NUMBER;
4088 l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;
4089 l_po_uom po_line_locations_all.unit_meas_lookup_code%TYPE;
4090 l_po_to_req_rate NUMBER;
4091
4092 l_po_return_code VARCHAR2(100) := '';
4093 l_err_line_id_tbl po_tbl_number;
4094 l_err_line_num_tbl po_tbl_number;
4095 l_err_dist_id_tbl po_tbl_number;
4096 l_err_dist_num_tbl po_tbl_number;
4097 l_err_error_attr_tbl po_tbl_varchar30;
4098 l_err_msg_count_tbl po_tbl_number;
4099 l_err_msg_data_tbl po_tbl_varchar2000;
4100 l_wf_status VARCHAR2(1);
4101 l_distribution_id_tbl po_tbl_number;
4102
4103 CURSOR l_changes_csr(grp_id NUMBER) IS
4104 SELECT
4105 document_header_id,
4106 document_line_id,
4107 document_distribution_id,
4108 new_quantity,
4109 new_price,
4110 new_need_by_date,
4111 request_reason
4112 FROM po_change_requests
4113 WHERE change_request_group_id = grp_id
4114 AND action_type = 'MODIFICATION';
4115
4116 CURSOR l_cancel_csr(grp_id NUMBER) IS
4117 SELECT
4118 document_header_id,
4119 document_line_id,
4120 request_reason
4121 FROM po_change_requests
4122 WHERE change_request_group_id = grp_id
4123 AND action_type = 'CANCELLATION';
4124
4125
4126 CURSOR l_dist_qty_price_chn_csr(grp_id NUMBER) IS
4127 SELECT
4128 document_line_id line_id,
4129 document_distribution_id dist_id,
4130 document_header_id hdr_id,
4131 document_num req_num
4132 FROM
4133 po_change_requests
4134 WHERE
4135 change_request_group_id = grp_id AND
4136 (new_quantity IS NOT NULL OR new_amount IS NOT NULL) AND
4137 action_type = 'MODIFICATION'
4138 UNION
4139 SELECT
4140 prda.requisition_line_id line_id,
4141 prda.distribution_id dist_id,
4142 prla.requisition_header_id hdr_id,
4143 prha.segment1 req_num
4144 FROM
4145 po_req_distributions_all prda,
4146 po_requisition_lines_all prla,
4147 po_change_requests pcr,
4148 po_requisition_headers_all prha
4149 WHERE
4150 prha.requisition_header_id = prla.requisition_header_id AND
4151 prla.requisition_line_id = prda.requisition_line_id AND
4152 pcr.document_line_id = prla.requisition_line_id AND
4153 pcr.change_request_group_id = grp_id AND
4154 pcr.action_type = 'MODIFICATION' AND (pcr.new_price IS NOT NULL OR
4155 pcr.new_need_by_date IS NOT NULL);
4156
4157 -- list of standard po distributions effected with the req changes
4158 CURSOR l_changed_po_dists_csr(grp_id NUMBER) IS
4159 SELECT -- any quantity or amount change
4160 pda.po_distribution_id
4161 FROM
4162 po_change_requests pcr,
4163 po_req_distributions_all prda,
4164 po_distributions_all pda,
4165 po_headers_all pha
4166 WHERE
4167 pcr.change_request_group_id = grp_id AND
4168 (pcr.new_quantity IS NOT NULL OR pcr.new_amount IS NOT NULL) AND
4169 pcr.action_type = 'MODIFICATION' AND
4170 pcr.document_distribution_id = prda.distribution_id AND
4171 prda.distribution_id = pda.req_distribution_id AND
4172 pda.po_header_id = pha.po_header_id AND
4173 pha.type_lookup_code = 'STANDARD'
4174 UNION
4175 SELECT -- select distributions that are effected with any line change
4176 pda.po_distribution_id
4177 FROM
4178 po_change_requests pcr,
4179 po_requisition_lines_all prla,
4180 po_req_distributions_all prda,
4181 po_distributions_all pda,
4182 po_headers_all pha
4183 WHERE
4184 pcr.change_request_group_id = grp_id AND
4185 pcr.action_type = 'MODIFICATION' AND
4186 (pcr.new_price IS NOT NULL OR pcr.new_need_by_date IS NOT NULL) AND
4187 pcr.document_line_id = prla.requisition_line_id AND
4188 prla.requisition_line_id = prda.requisition_line_id AND
4189 prda.distribution_id = pda.req_distribution_id AND
4190 pda.po_header_id = pha.po_header_id AND
4191 pha.type_lookup_code = 'STANDARD';
4192
4193 -- list of release distributions effected with the req changes
4194 CURSOR l_changed_rel_dists_csr(grp_id NUMBER) IS
4195 SELECT -- any quantity or amount change
4196 pda.po_distribution_id
4197 FROM
4198 po_change_requests pcr,
4199 po_req_distributions_all prda,
4200 po_distributions_all pda,
4201 po_requisition_lines_all prla,
4202 po_line_locations_all plla
4203 WHERE
4204 pcr.change_request_group_id = grp_id AND
4205 (pcr.new_quantity IS NOT NULL OR pcr.new_amount IS NOT NULL) AND
4206 pcr.action_type = 'MODIFICATION' AND
4207 pcr.document_distribution_id = prda.distribution_id AND
4208 prda.distribution_id = pda.req_distribution_id AND
4209 prla.requisition_line_id = prda.requisition_line_id AND
4210 prla.line_location_id = plla.line_location_id AND
4211 plla.po_release_id IS NOT NULL
4212 UNION -- select distributions that are effected with any line change
4213 SELECT
4214 pda.po_distribution_id
4215 FROM
4216 po_change_requests pcr,
4217 po_requisition_lines_all prla,
4218 po_req_distributions_all prda,
4219 po_distributions_all pda,
4220 po_line_locations_all plla
4221 WHERE
4222 pcr.change_request_group_id = grp_id AND
4223 pcr.action_type = 'MODIFICATION' AND
4224 (pcr.new_price IS NOT NULL OR pcr.new_need_by_date IS NOT NULL) AND
4225 pcr.document_line_id = prla.requisition_line_id AND
4226 prla.requisition_line_id = prda.requisition_line_id AND
4227 prda.distribution_id = pda.req_distribution_id AND
4228 prla.line_location_id = plla.line_location_id AND
4229 plla.po_release_id IS NOT NULL;
4230
4231
4232 BEGIN
4233 x_retmsg := 'SMRCH000';
4234 x_return_status := fnd_api.g_ret_sts_success;
4235
4236 --Check if Funds Check is needed
4237 SELECT
4238 nvl(fsp.req_encumbrance_flag, 'N'),
4239 nvl(fsp.purch_encumbrance_flag, 'N')
4240 INTO
4241 l_flag_one,
4242 l_flag_two
4243 FROM financials_system_parameters fsp;
4244
4245 --Check if change request exist
4246 l_change_exist := 'N';
4247 OPEN l_changes_csr(p_group_id);
4248 FETCH l_changes_csr
4249 INTO l_req_hdr_id, l_req_line_id, l_req_dist_id, l_new_quantity, l_new_price, l_new_date, l_request_reason;
4250 IF(l_req_hdr_id IS NOT NULL) THEN
4251 l_change_exist := 'Y';
4252 END IF;
4253 CLOSE l_changes_csr;
4254
4255 --Check if cancel request exist
4256 l_cancel_exist := 'N';
4257 OPEN l_cancel_csr(p_group_id);
4258 FETCH l_cancel_csr
4259 INTO l_req_hdr_id, l_req_line_id, l_request_reason;
4260 IF(l_req_hdr_id IS NOT NULL) THEN
4261 l_cancel_exist := 'Y';
4262 END IF;
4263 CLOSE l_cancel_csr;
4264
4265
4266 x_retmsg := 'SMRCH001';
4267
4268 --Funds Check Starts
4269 IF (l_change_exist = 'Y' AND p_fundscheck_flag = 'Y' AND (l_flag_one <> 'N' OR l_flag_two <> 'N')) THEN
4270
4271 x_retmsg := 'SMRCH002';
4272 --Check if any records require funds check.
4273 OPEN l_dist_qty_price_chn_csr(p_group_id);
4274 FETCH l_dist_qty_price_chn_csr INTO
4275 l_req_line_id,
4276 l_req_dist_id,
4277 l_req_hdr_id,
4278 l_req_num;
4279 CLOSE l_dist_qty_price_chn_csr;
4280
4281 IF (l_req_num IS NOT NULL) THEN
4282
4283 -- initialize distributions list table
4284 l_distribution_id_tbl := po_tbl_number();
4285
4286 -- insert NEW/OLD records of standard po distributions into PO_ENCUMBRANCE_GT
4287 OPEN l_changed_po_dists_csr(p_group_id);
4288
4289 FETCH l_changed_po_dists_csr BULK COLLECT
4290 INTO l_distribution_id_tbl;
4291
4292 CLOSE l_changed_po_dists_csr;
4293
4294 po_document_funds_grp.populate_encumbrance_gt(
4295 p_api_version => 1.0,
4296 x_return_status => x_return_status,
4297 p_doc_type => po_document_funds_grp.g_doc_type_po,
4298 p_doc_level => po_document_funds_grp.g_doc_level_distribution,
4299 p_doc_level_id_tbl => l_distribution_id_tbl,
4300 p_make_old_copies_flag => po_document_funds_grp.g_parameter_yes,
4301 p_make_new_copies_flag => po_document_funds_grp.g_parameter_yes,
4302 p_check_only_flag => po_document_funds_grp.g_parameter_yes);
4303
4304 -- error handling after calling populate_encumbrance_gt
4305 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
4306 x_retmsg := 'After calling populate_encumbrance_gt';
4307 x_errcode := 'FC_FAIL';
4308 RETURN;
4309 END IF;
4310
4311 -- insert NEW/OLD records of release distributions into PO_ENCUMBRANCE_GT
4312
4313 -- re-initialize distributions list table
4314 l_distribution_id_tbl.delete;
4315
4316 -- insert standard po distributions into PO_ENCUMBRANCE_GT
4317 OPEN l_changed_rel_dists_csr(p_group_id);
4318
4319 FETCH l_changed_rel_dists_csr BULK COLLECT
4320 INTO l_distribution_id_tbl;
4321
4322 CLOSE l_changed_rel_dists_csr;
4323
4324 po_document_funds_grp.populate_encumbrance_gt(
4325 p_api_version => 1.0,
4326 x_return_status => x_return_status,
4327 p_doc_type => po_document_funds_grp.g_doc_type_release,
4328 p_doc_level => po_document_funds_grp.g_doc_level_distribution,
4329 p_doc_level_id_tbl => l_distribution_id_tbl,
4330 p_make_old_copies_flag => po_document_funds_grp.g_parameter_yes,
4331 p_make_new_copies_flag => po_document_funds_grp.g_parameter_yes,
4332 p_check_only_flag => po_document_funds_grp.g_parameter_yes);
4333
4334 -- error handling after calling populate_encumbrance_gt
4335 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
4336 x_retmsg := 'After calling populate_encumbrance_gt (release)';
4337 x_errcode := 'FC_FAIL';
4338 RETURN;
4339 END IF;
4340
4341 -- Update NEW record in PO_ENCUMBRANCE_GT with the new
4342 -- values
4343
4344
4345 x_retmsg := 'SMRCH003';
4346 /*
4347 *Looping through the distribution records which requires fundscheck, and populating the fundscheck
4348 *input table with the appropriate data.
4349 */
4350 OPEN l_dist_qty_price_chn_csr(p_group_id);
4351
4352 LOOP
4353 FETCH l_dist_qty_price_chn_csr INTO
4354 l_req_line_id,
4355 l_req_dist_id,
4356 l_req_hdr_id,
4357 l_req_num;
4358
4359 EXIT WHEN l_dist_qty_price_chn_csr%notfound;
4360 x_retmsg := 'SMRCH0031:' || l_req_line_id || '*' || l_req_dist_id || '*' || l_req_hdr_id || '*' || l_req_num;
4361
4362 SELECT
4363 plla.line_location_id,
4364 pda.po_distribution_id,
4365 plla.po_line_id,
4366 nvl(plla.price_override, pla.unit_price),
4367 pda.quantity_ordered,
4368 pda.amount_ordered,
4369 prla.item_id,
4370 prla.unit_meas_lookup_code,
4371 nvl(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code),
4372 pha.rate,
4373 plla.need_by_date,
4374 plla.ship_to_organization_id,
4375 plla.ship_to_location_id
4376 INTO
4377 l_shipment_id,
4378 l_distribution_id,
4379 l_line_id,
4380 l_old_price,
4381 l_old_quantity,
4382 l_old_amount,
4383 l_item_id,
4384 l_req_uom,
4385 l_po_uom,
4386 l_po_to_req_rate,
4387 l_old_need_by_date,
4388 l_ship_to_org_id,
4389 l_ship_to_loc_id
4390 FROM
4391 po_req_distributions_all prda,
4392 po_requisition_lines_all prla,
4393 po_line_locations_all plla,
4394 po_distributions_all pda,
4395 po_lines_all pla,
4396 po_headers_all pha
4397 WHERE
4398 prda.distribution_id = l_req_dist_id
4399 AND prda.requisition_line_id = prla.requisition_line_id
4400 AND pda.req_distribution_id = prda.distribution_id
4401 AND pda.line_location_id = prla.line_location_id
4402 AND plla.line_location_id = prla.line_location_id
4403 AND plla.po_header_id = plla.po_header_id
4404 AND plla.po_line_id = pla.po_line_id
4405 AND pla.po_header_id = pha.po_header_id;
4406
4407 -- Obtain new amount (for service lines)
4408 BEGIN
4409
4410 SELECT new_amount
4411 INTO l_new_amount
4412 FROM po_change_requests
4413 WHERE
4414 change_request_group_id = p_group_id AND
4415 document_distribution_id = l_req_dist_id AND
4416 new_amount IS NOT NULL;
4417
4418 EXCEPTION WHEN no_data_found THEN
4419 l_new_amount := l_old_amount;
4420 END;
4421
4422 --Obtain most recent quantity
4423 l_price_break := 'N';
4424 BEGIN
4425 SELECT new_quantity
4426 INTO l_new_quantity
4427 FROM po_change_requests
4428 WHERE change_request_group_id = p_group_id
4429 AND document_distribution_id = l_req_dist_id
4430 AND new_quantity IS NOT NULL;
4431
4432 l_price_break := 'Y';
4433
4434 IF(l_req_uom <> l_po_uom) THEN
4435 po_uom_s.uom_convert(
4436 from_quantity => l_new_quantity,
4437 from_uom => l_req_uom,
4438 item_id => l_item_id,
4439 to_uom => l_po_uom,
4440 to_quantity => l_new_po_quantity);
4441
4442 l_new_quantity := l_new_po_quantity;
4443 END IF;
4444
4445 EXCEPTION WHEN no_data_found THEN
4446 l_new_quantity := l_old_quantity;
4447 END;
4448
4449 --Obtain most recent price
4450 BEGIN
4451 SELECT new_price
4452 INTO l_new_price
4453 FROM po_change_requests
4454 WHERE change_request_group_id = p_group_id
4455 AND document_line_id = l_req_line_id
4456 AND new_price IS NOT NULL;
4457
4458 IF(l_po_to_req_rate IS NOT NULL) THEN
4459 l_new_price := l_new_price / l_po_to_req_rate;
4460 END IF;
4461
4462 EXCEPTION WHEN no_data_found THEN
4463 BEGIN
4464 SELECT new_need_by_date
4465 INTO l_new_need_by_date
4466 FROM po_change_requests
4467 WHERE change_request_group_id = p_group_id
4468 AND document_line_id = l_req_line_id
4469 AND new_need_by_date IS NOT NULL;
4470
4471 l_price_break := 'Y';
4472 EXCEPTION WHEN no_data_found THEN
4473 l_new_need_by_date := l_old_need_by_date;
4474 END;
4475
4476 IF(l_price_break = 'Y') THEN
4477 l_new_price := po_sourcing2_sv.get_break_price(
4478 x_order_quantity => l_new_quantity,
4479 x_ship_to_org => l_ship_to_org_id,
4480 x_ship_to_loc => l_ship_to_loc_id,
4481 x_po_line_id => l_line_id,
4482 x_cum_flag => FALSE,
4483 p_need_by_date => l_new_need_by_date,
4484 x_line_location_id => l_shipment_id);
4485 ELSE
4486 l_new_price := l_old_price;
4487 END IF;
4488
4489 END;
4490
4491 --Calculate new tax
4492 calculate_disttax(1.0, l_cal_disttax_status, l_req_dist_id, l_new_price, l_new_quantity, NULL,
4493 l_rec_tax, l_nonrec_tax);
4494 l_new_tax := l_nonrec_tax;
4495
4496
4497 -- update new values in PO_ENCUMBRANCE_GT
4498 UPDATE po_encumbrance_gt
4499 SET
4500 amount_ordered = l_new_amount,
4501 quantity_ordered = l_new_quantity,
4502 price = l_new_price,
4503 nonrecoverable_tax = l_new_tax
4504 WHERE
4505 distribution_id = l_distribution_id AND
4506 adjustment_status = po_document_funds_grp.g_adjustment_status_new;
4507
4508 END LOOP;
4509 CLOSE l_dist_qty_price_chn_csr;
4510
4511
4512 x_retmsg := 'SMRCH0032';
4513 --Execute PO Funds Check API
4514
4515 po_document_funds_grp.check_adjust(
4516 p_api_version => 1.0,
4517 x_return_status => l_fc_result_status,
4518 p_doc_type => po_document_funds_grp.g_doc_type_mixed_po_release,
4519 p_doc_subtype => NULL,
4520 p_override_funds => po_document_funds_grp.g_parameter_use_profile,
4521 p_use_gl_date => po_document_funds_grp.g_parameter_yes,
4522 p_override_date => SYSDATE,
4523 p_report_successes => po_document_funds_grp.g_parameter_no,
4524 x_po_return_code => l_po_return_code,
4525 x_detailed_results => l_fc_out_tbl);
4526
4527 x_retmsg := 'SMRCH004';
4528
4529 IF (g_fnd_debug = 'Y') THEN
4530 IF (fnd_log.g_current_runtime_level <= fnd_log.level_statement) THEN
4531 fnd_log.string(fnd_log.level_statement,
4532 g_module_prefix || l_api_name,
4533 'FUNDS CHECK:' || l_fc_result_status ||' PO RETURN CODE:' || l_po_return_code);
4534 END IF;
4535 END IF;
4536
4537 IF (l_fc_result_status = fnd_api.g_ret_sts_unexp_error) THEN
4538 x_errcode := 'FC_ERROR';
4539 x_return_status := fnd_api.g_ret_sts_error;
4540 RETURN;
4541
4542 ELSE
4543
4544 IF (l_po_return_code = po_document_funds_grp.g_return_success) THEN
4545 x_return_status := fnd_api.g_ret_sts_success;
4546
4547 ELSE -- there can be warning/error message for other cases
4548
4549 x_errcode := 'FC_FAIL';
4550 x_return_status := fnd_api.g_ret_sts_error;
4551
4552 -- populate x_errTable (output PLSQL table) with the corresponding
4553 -- funds check error messages.
4554
4555 l_err_line_id_tbl := po_tbl_number();
4556 l_err_line_num_tbl := po_tbl_number();
4557 l_err_dist_id_tbl := po_tbl_number();
4558 l_err_dist_num_tbl := po_tbl_number();
4559 l_err_error_attr_tbl := po_tbl_varchar30();
4560 l_err_msg_count_tbl := po_tbl_number();
4561 l_err_msg_data_tbl := po_tbl_varchar2000();
4562
4563 x_errtable := po_req_change_err_table(
4564 l_err_line_id_tbl,
4565 l_err_line_num_tbl,
4566 l_err_dist_id_tbl,
4567 l_err_dist_num_tbl,
4568 l_err_error_attr_tbl,
4569 l_err_msg_count_tbl,
4570 l_err_msg_data_tbl);
4571
4572
4573 x_errtable.req_line_id.extend(l_fc_out_tbl.row_index.count);
4574 x_errtable.req_line_num.extend(l_fc_out_tbl.row_index.count);
4575 x_errtable.req_dist_id.extend(l_fc_out_tbl.row_index.count);
4576 x_errtable.req_dist_num.extend(l_fc_out_tbl.row_index.count);
4577 x_errtable.msg_count.extend(l_fc_out_tbl.row_index.count);
4578 x_errtable.msg_data.extend(l_fc_out_tbl.row_index.count);
4579 FOR x IN 1..l_fc_out_tbl.row_index.count LOOP
4580
4581 SELECT
4582 prda.distribution_id,
4583 prda.distribution_num,
4584 prda.requisition_line_id,
4585 prla.line_num
4586 INTO
4587 l_fc_req_distr_id,
4588 l_fc_req_distr_num,
4589 l_fc_req_line_id,
4590 l_fc_req_line_num
4591 FROM
4592 po_requisition_lines_all prla,
4593 po_req_distributions_all prda,
4594 po_distributions_all pda
4595 WHERE
4596 pda.po_distribution_id = l_fc_out_tbl.distribution_id(x)
4597 AND pda.req_distribution_id = prda.distribution_id
4598 AND prla.requisition_line_id = prda.requisition_line_id;
4599
4600 x_errtable.req_line_id(x) := l_fc_req_line_id;
4601 x_errtable.req_line_num(x) := l_fc_req_line_num;
4602 x_errtable.req_dist_id(x) := l_fc_req_distr_id;
4603 x_errtable.req_dist_num(x) := l_fc_req_distr_num;
4604 x_errtable.msg_data(x) := l_fc_out_tbl.error_msg(x);
4605
4606 END LOOP;
4607 RETURN;
4608 END IF;
4609 END IF;
4610 END IF;
4611
4612 END IF;
4613 --Funds Check Ends
4614
4615
4616 i := 1;
4617 OPEN l_changes_csr(p_group_id);
4618 LOOP
4619 FETCH l_changes_csr
4620 INTO
4621 l_req_hdr_id,
4622 l_req_line_id,
4623 l_req_dist_id,
4624 l_new_quantity,
4625 l_new_price,
4626 l_new_date,
4627 l_request_reason;
4628 EXIT WHEN l_changes_csr%notfound;
4629 l_req_change_table(i).document_line_id := l_req_line_id;
4630 l_req_change_table(i).document_distribution_id := l_req_dist_id;
4631 l_req_change_table(i).new_price := l_new_price;
4632 l_req_change_table(i).new_quantity := l_new_quantity;
4633 l_req_change_table(i).new_date := l_new_date;
4634 l_req_change_table(i).request_reason := l_request_reason;
4635
4636 i := i + 1;
4637 END LOOP;
4638 CLOSE l_changes_csr;
4639 x_retmsg := 'SMRCH006';
4640 l_err_line_id_tbl := po_tbl_number();
4641 l_err_line_num_tbl := po_tbl_number();
4642 l_err_dist_id_tbl := po_tbl_number();
4643 l_err_dist_num_tbl := po_tbl_number();
4644 l_err_error_attr_tbl := po_tbl_varchar30();
4645 l_err_msg_count_tbl := po_tbl_number();
4646 l_err_msg_data_tbl := po_tbl_varchar2000();
4647
4648 x_errtable := po_req_change_err_table(
4649 l_err_line_id_tbl,
4650 l_err_line_num_tbl,
4651 l_err_dist_id_tbl,
4652 l_err_dist_num_tbl,
4653 l_err_error_attr_tbl,
4654 l_err_msg_count_tbl,
4655 l_err_msg_data_tbl);
4656
4657 --Final Round of Validations Against Changes
4658 IF(l_change_exist = 'Y') THEN
4659 validate_changes(l_req_hdr_id, l_req_change_table, x_return_status, x_retmsg, x_errtable);
4660 END IF;
4661 x_retmsg := 'SMRCH007';
4662 --Submit Cancel Requests
4663 IF(l_cancel_exist = 'Y') THEN
4664 submit_reqcancel(1.0, x_return_status, p_group_id, x_retmsg, l_cancel_errtable, 'Y');
4665 END IF;
4666
4667 x_retmsg := 'SMRCH008';
4668
4669
4670
4671
4672 i := x_errtable.req_line_id.count + 1;
4673 l_cancelerrorsize := l_cancel_errtable.req_line_id.count;
4674 IF(l_cancelerrorsize > 0) THEN
4675 x_errtable.req_line_id.extend(l_cancelerrorsize);
4676 x_errtable.msg_count.extend(l_cancelerrorsize);
4677 x_errtable.msg_data.extend(l_cancelerrorsize);
4678
4679
4680 FOR k IN 1..l_cancelerrorsize
4681 LOOP
4682 x_errtable.req_line_id(i) := l_cancel_errtable.req_line_id(k);
4683 x_errtable.msg_count(i) := 1;
4684 x_errtable.msg_data(i) :='CANNOT CANCEL';
4685 i := i + 1;
4686 END LOOP;
4687 END IF;
4688
4689 /*
4690 * If all requests are valid, update status to "NEW" and kick off workflow
4691 */
4692 IF(x_errtable.req_line_id.count = 0) THEN
4693 UPDATE po_change_requests
4694 SET request_status = 'NEW'
4695 WHERE change_request_group_id = p_group_id
4696 AND request_status = 'SYSTEMSAVE';
4697
4698 --Kick Off Workflow
4699 x_retmsg := 'SMRCH009';
4700 po_reqchangerequestwf_pvt.submit_req_change(
4701 p_api_version => 1.0,
4702 p_commit => fnd_api.g_false,
4703
4704 p_req_header_id => l_req_hdr_id,
4705 p_note_to_approver => p_note_to_approver,
4706 p_initiator => p_initiator,
4707 x_return_status => l_wf_status);
4708 ELSE
4709 x_errcode := 'VC_FAIL';
4710 x_return_status := fnd_api.g_ret_sts_error;
4711 END IF;
4712 EXCEPTION WHEN OTHERS THEN
4713 x_return_status := fnd_api.g_ret_sts_unexp_error;
4714 x_retmsg := x_retmsg || ':' || SQLERRM;
4715 IF g_fnd_debug = 'Y' THEN
4716 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
4717 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
4718 l_api_name || '.others_exception', x_retmsg );
4719 END IF;
4720 END IF;
4721 END submit_reqchange;
4722
4723 /*
4724 **Submit_ReqCancel: Final procedure call for transaction which involves requester
4725 **cancelling a req line.
4726 **This API could be called from the Cancel Flow UI directly, or from Submit_ReqChange API.
4727 */
4728 PROCEDURE submit_reqcancel (
4729 p_api_version IN NUMBER,
4730 x_return_status OUT NOCOPY VARCHAR2,
4731 p_group_id IN NUMBER,
4732 x_retmsg OUT NOCOPY VARCHAR2,
4733 p_errtable OUT NOCOPY po_req_change_err_table,
4734 p_origin IN VARCHAR2)
4735 IS
4736 l_api_name VARCHAR2(50) := 'Submit_ReqCancel';
4737 l_line_id NUMBER;
4738 l_result VARCHAR2(1);
4739 i NUMBER := 1;
4740 l_canerr_line_id_tbl po_tbl_number;
4741 l_canerr_line_num_tbl po_tbl_number;
4742 l_canerr_dist_id_tbl po_tbl_number;
4743 l_canerr_dist_num_tbl po_tbl_number;
4744 l_canerr_error_attr_tbl po_tbl_varchar30;
4745 l_canerr_msg_count_tbl po_tbl_number;
4746 l_canerr_msg_data_tbl po_tbl_varchar2000;
4747 l_line_location_id NUMBER;
4748 l_chn_req_id NUMBER;
4749 l_req_hdr_id NUMBER;
4750 l_wf_status VARCHAR2(1);
4751 l_workflow_needed VARCHAR2(1) := 'N';
4752 CURSOR l_cancels_csr(grp_id NUMBER) IS
4753 SELECT
4754 pcr.document_header_id,
4755 pcr.document_line_id,
4756 prla.line_location_id,
4757 pcr.change_request_id
4758 FROM
4759 po_change_requests pcr,
4760 po_requisition_lines_all prla
4761 WHERE pcr.action_type = 'CANCELLATION'
4762 AND pcr.change_request_group_id = grp_id
4763 AND pcr.document_line_id = prla.requisition_line_id;
4764 BEGIN
4765
4766
4767 l_canerr_line_id_tbl := po_tbl_number();
4768 l_canerr_line_num_tbl := po_tbl_number();
4769 l_canerr_dist_id_tbl := po_tbl_number();
4770 l_canerr_dist_num_tbl := po_tbl_number();
4771 l_canerr_error_attr_tbl := po_tbl_varchar30();
4772 l_canerr_msg_count_tbl := po_tbl_number();
4773 l_canerr_msg_data_tbl := po_tbl_varchar2000();
4774
4775 p_errtable := po_req_change_err_table(
4776 l_canerr_line_id_tbl ,
4777 l_canerr_line_num_tbl,
4778 l_canerr_dist_id_tbl ,
4779 l_canerr_dist_num_tbl,
4780 l_canerr_error_attr_tbl,
4781 l_canerr_msg_count_tbl ,
4782 l_canerr_msg_data_tbl );
4783
4784 --Calling PO Cancel API to check if the corresponding PO Shipment Can be cancelled.
4785 OPEN l_cancels_csr(p_group_id);
4786 LOOP
4787 FETCH l_cancels_csr INTO
4788 l_req_hdr_id, l_line_id, l_line_location_id, l_chn_req_id;
4789 EXIT WHEN l_cancels_csr%notfound;
4790 IF(l_line_location_id IS NULL) THEN
4791 UPDATE po_change_requests
4792 SET request_status = 'ACCEPTED'
4793 WHERE change_request_id = l_chn_req_id;
4794 ELSE
4795 l_workflow_needed := 'Y';
4796 is_req_line_cancellable(1.0, x_return_status, l_line_id, l_result);
4797 IF(l_result = 'N') THEN
4798 p_errtable.req_line_id.extend(1);
4799 p_errtable.req_line_id(i) := l_line_id;
4800 i := i + 1;
4801 END IF;
4802 END IF;
4803 END LOOP;
4804
4805 CLOSE l_cancels_csr;
4806
4807 x_return_status := fnd_api.g_ret_sts_success;
4808
4809 --If all requests are valid, update status to "NEW", and kick off Workflow
4810
4811 IF(p_errtable.req_line_id.count = 0) THEN
4812 UPDATE po_change_requests
4813 SET request_status = 'NEW'
4814 WHERE change_request_group_id = p_group_id
4815 AND request_status = 'SYSTEMSAVE';
4816
4817 IF (p_origin IS NULL AND l_workflow_needed = 'Y') THEN
4818
4819 po_reqchangerequestwf_pvt.submit_req_change(
4820 p_api_version => 1.0,
4821 p_commit => fnd_api.g_false,
4822 x_return_status => l_wf_status,
4823 p_req_header_id => l_req_hdr_id,
4824 p_note_to_approver => NULL,
4825 p_initiator => 'REQUESTER');
4826 END IF;
4827 ELSE
4828 x_return_status := fnd_api.g_ret_sts_error;
4829 END IF;
4830
4831 EXCEPTION WHEN OTHERS THEN
4832 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4833 IF g_fnd_debug = 'Y' THEN
4834 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
4835 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
4836 l_api_name || '.others_exception', SQLERRM);
4837 END IF;
4838 END IF;
4839 END submit_reqcancel;
4840
4841 /**
4842 * This procedure returns whether an internal line can be cancelled or not
4843 * If the corresponding internal order is cancelled, it returns 'S'.
4844 * It also checks if the line is in oe interface table.
4845 **/
4846 PROCEDURE IS_INTERNAL_LINE_CANCELLABLE(p_api_version IN NUMBER,
4847 x_return_status OUT NOCOPY VARCHAR2,
4848 p_req_line_id IN NUMBER)
4849 IS
4850 l_req_line_id NUMBER:= 0;
4851 l_req_header_id NUMBER := 0;
4852 l_api_name varchar2(50):= 'Is_Internal_Line_Cancellable';
4853 BEGIN
4854 BEGIN
4855
4856 x_return_status := FND_API.G_RET_STS_SUCCESS;
4857
4858 SELECT
4859 prl.requisition_line_id,
4860 prh.requisition_header_id
4861 INTO
4862 l_req_line_id,
4863 l_req_header_id
4864 FROM po_requisition_lines prl,
4865 po_requisition_headers_all prh -- <R12 MOAC>
4866 WHERE prl.requisition_line_id = p_req_line_id AND
4867 prh.requisition_header_id = prl.requisition_header_id AND
4868 (NOT EXISTS
4869 (SELECT 'so line is not cancelled'
4870 FROM
4871 po_requisition_lines PORL,
4872 po_requisition_headers_all PORH, -- <R12 MOAC>
4873 po_system_parameters POSP
4874 WHERE
4875 PORL.requisition_line_id = p_req_line_id AND
4876 PORL.requisition_header_id = PORH.requisition_header_id AND
4877 (OE_ORDER_IMPORT_INTEROP_PUB.Get_Open_Qty(posp.order_source_id, porh.requisition_header_id, porl.requisition_line_id))>0)
4878 AND NOT EXISTS
4879 (SELECT 'line in interface table'
4880 FROM
4881 oe_headers_iface_all SOHI,
4882 po_system_parameters POSP
4883 WHERE
4884 SOHI.orig_sys_document_ref = to_char(PRH.requisition_header_id)
4885 AND SOHI.order_source_id = POSP.order_source_id));
4886
4887 EXCEPTION
4888
4889 WHEN NO_DATA_FOUND THEN
4890 x_return_status := FND_API.G_RET_STS_ERROR;
4891 RETURN;
4892
4893 WHEN OTHERS THEN
4894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4895 IF (g_fnd_debug = 'Y') THEN
4896 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
4897 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
4898 l_api_name || '.others_exception',
4899 p_req_line_id||':'||sqlerrm);
4900 END IF;
4901 END IF;
4902 RETURN;
4903 END;
4904
4905 /* Bug : 4639448
4906 ** Call to check whether the SO shipments are still in process..*/
4907 BEGIN
4908 IF po_req_lines_sv.val_oe_shipment_in_proc(l_req_header_id,
4909 p_req_line_id) = FALSE
4910 THEN
4911 x_return_status := FND_API.G_RET_STS_ERROR;
4912 RETURN;
4913 END IF;
4914 EXCEPTION
4915 WHEN NO_DATA_FOUND THEN
4916 x_return_status := FND_API.G_RET_STS_ERROR;
4917 RETURN;
4918 END;
4919
4920 /* Bug : 4639448
4921 ** Call po_req_lines_sv.val_reqs_qty_received to verify if internal
4922 ** requisition lines which are sourced from inventory, have been received or not.*/
4923 BEGIN
4924 IF po_req_lines_sv.val_reqs_qty_received (l_req_header_id,
4925 p_req_line_id ) = FALSE
4926 THEN
4927 x_return_status := FND_API.G_RET_STS_ERROR;
4928 RETURN;
4929 END IF;
4930 EXCEPTION
4931 WHEN NO_DATA_FOUND THEN
4932 x_return_status := FND_API.G_RET_STS_ERROR;
4933 RETURN;
4934 END;
4935
4936 END IS_INTERNAL_LINE_CANCELLABLE;
4937
4938 PROCEDURE is_SO_line_cancellable(p_api_version IN NUMBER,
4939 x_return_status OUT NOCOPY VARCHAR2,
4940 p_req_line_id IN NUMBER,
4941 p_req_header_id IN NUMBER,
4942 x_cancellable OUT NOCOPY VARCHAR2 )
4943 IS
4944 l_req_line_id NUMBER := 0;
4945 l_req_header_id NUMBER := 0;
4946 l_api_name VARCHAR2(50) := 'is_SO_line_cancellable';
4947 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
4948 l_progress VARCHAR2(3) := '000';
4949 x_update_allowed BOOLEAN := FALSE;
4950 x_cancel_allowed BOOLEAN := FALSE;
4951 X_msg_count number;
4952 X_msg_data varchar2(3000);
4953 l_orgid number;
4954 l_api_version CONSTANT NUMBER := 1.0;
4955 BEGIN
4956 x_return_status := fnd_api.g_ret_sts_success;
4957
4958 IF g_debug_stmt THEN
4959 po_debug.debug_begin(l_log_head);
4960 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
4961 po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
4962 po_debug.debug_var(l_log_head, l_progress, 'p_req_header_id', p_req_header_id);
4963 END IF;
4964
4965 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
4966 p_api_version,
4967 l_api_name ,
4968 G_PKG_NAME )
4969 THEN
4970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4971 END IF;
4972
4973 IF (p_req_line_id is null and p_req_header_id is null ) then
4974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4975 END if;
4976
4977 IF (p_req_line_id is null and p_req_header_id is not null ) then
4978 l_progress := '001';
4979
4980
4981 l_orgid := PO_ReqChangeRequestWF_PVT.get_sales_order_org(p_req_hdr_id => p_req_header_id);
4982
4983 IF l_orgid is NOT NULL THEN
4984 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
4985 END IF;
4986
4987 l_progress := '002';
4988 -- OM_API.is_req_line_cancellable(l_req_header_id,l_req_line_id,x_return_status);
4989 -- OM API OM_API provided is
4990 OE_Internal_Requisition_Pvt.Is_IReq_Changable
4991 ( P_API_Version => 1.0
4992 , P_internal_req_line_id =>p_req_line_id
4993 , P_internal_req_header_id =>p_req_header_id
4994 , X_Update_Allowed =>x_update_allowed
4995 , X_Cancel_Allowed =>x_cancel_allowed
4996 , X_msg_count =>X_msg_count
4997 , X_msg_data =>X_msg_data
4998 , X_return_status =>x_return_status
4999 );
5000
5001
5002 l_orgid := PO_ReqChangeRequestWF_PVT.get_requisition_org( p_req_hdr_id => p_req_header_id);
5003
5004 IF l_orgid is NOT NULL THEN
5005 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
5006 END IF;
5007
5008 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
5009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5010 ElsE
5011 x_cancellable := por_util_pkg.bool_to_varchar(X_Cancel_Allowed);
5012 END IF;
5013
5014 else
5015
5016
5017 SELECT
5018 prl.requisition_line_id,
5019 prh.requisition_header_id
5020 INTO
5021 l_req_line_id,
5022 l_req_header_id
5023 FROM po_requisition_lines prl,
5024 po_requisition_headers_all prh -- <R12 MOAC>
5025 WHERE prl.requisition_line_id = p_req_line_id AND
5026 prh.requisition_header_id = prl.requisition_header_id ;
5027
5028 l_progress := '001';
5029 l_orgid := PO_ReqChangeRequestWF_PVT.get_sales_order_org(p_req_hdr_id => l_req_header_id);
5030
5031 IF l_orgid is NOT NULL THEN
5032 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
5033 END IF;
5034
5035 l_progress := '002';
5036 -- OM_API.is_req_line_cancellable(l_req_header_id,l_req_line_id,x_return_status);
5037 -- OM API OM_API provided is
5038 OE_Internal_Requisition_Pvt.Is_IReq_Changable
5039 ( P_API_Version => 1.0
5040 , P_internal_req_line_id =>l_req_line_id
5041 , P_internal_req_header_id =>l_req_header_id
5042 , X_Update_Allowed =>x_update_allowed
5043 , X_Cancel_Allowed =>x_cancel_allowed
5044 , X_msg_count =>X_msg_count
5045 , X_msg_data =>X_msg_data
5046 , X_return_status =>x_return_status
5047 );
5048
5049
5050 l_orgid := PO_ReqChangeRequestWF_PVT.get_requisition_org( p_req_hdr_id => p_req_header_id);
5051
5052 IF l_orgid is NOT NULL THEN
5053 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
5054 END IF;
5055
5056 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
5057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5058 ElsE
5059 x_cancellable := por_util_pkg.bool_to_varchar(X_Cancel_Allowed);
5060 END IF;
5061 end if;
5062
5063 IF g_debug_stmt THEN
5064 po_debug.debug_var(l_log_head, l_progress, 'x_cancellable', x_cancellable);
5065 po_debug.debug_end(l_log_head);
5066 end if;
5067 EXCEPTION
5068 WHEN no_data_found THEN
5069 x_return_status := fnd_api.g_ret_sts_error;
5070 RETURN;
5071
5072 WHEN OTHERS THEN
5073 x_return_status := fnd_api.g_ret_sts_unexp_error;
5074 IF (g_fnd_debug = 'Y') THEN
5075 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
5076 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
5077 l_api_name || '.others_exception',
5078 p_req_line_id || ':' || SQLERRM);
5079 END IF;
5080 END IF;
5081 RETURN;
5082
5083 END is_SO_line_cancellable;
5084
5085 /**
5086 * This procedure returns whether an internal line can be updated
5087 * with quantity and need by date or not
5088 **/
5089 PROCEDURE is_internal_line_changeable(p_api_version IN NUMBER
5090 , X_Update_Allowed OUT NOCOPY VARCHAR2
5091 , X_Cancel_Allowed OUT NOCOPY VARCHAR2
5092 , x_return_status OUT NOCOPY VARCHAR2
5093 , p_req_line_id IN NUMBER)
5094 IS
5095 l_req_line_id NUMBER := 0;
5096 l_req_header_id NUMBER := 0;
5097 l_api_name VARCHAR2(50) := 'Is_Internal_Line_Changeable';
5098 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
5099 l_progress VARCHAR2(3) := '000';
5100 X_msg_count number;
5101 X_msg_data varchar2(3000);
5102 l_Update_Allowed boolean :=FALSE;
5103 l_Cancel_Allowed boolean :=FALSE;
5104 l_api_version CONSTANT NUMBER := 1.0;
5105 l_orgid number;
5106 BEGIN
5107
5108 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
5109 p_api_version,
5110 l_api_name ,
5111 G_PKG_NAME )
5112 THEN
5113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5114 END IF;
5115
5116 x_return_status := fnd_api.g_ret_sts_success;
5117 l_update_allowed := FALSE;
5118 l_cancel_allowed := FALSE;
5119
5120 IF g_debug_stmt THEN
5121 po_debug.debug_begin(l_log_head);
5122 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
5123 po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
5124 END IF;
5125
5126 SELECT
5127 prl.requisition_line_id,
5128 prl.requisition_header_id
5129 INTO
5130 l_req_line_id,
5131 l_req_header_id
5132 FROM po_requisition_lines_all prl
5133 WHERE prl.requisition_line_id = p_req_line_id;
5134
5135 l_progress := '001';
5136
5137 l_orgid := PO_ReqChangeRequestWF_PVT.get_sales_order_org(p_req_hdr_id => l_req_header_id);
5138
5139 IF l_orgid is NOT NULL THEN
5140 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
5141 END IF;
5142
5143
5144 -- OM_API.is_req_line_changeable(l_req_header_id,l_req_line_id,x_return_status);
5145 -- OM API OM_API provided is
5146 OE_Internal_Requisition_Pvt.Is_IReq_Changable
5147 ( P_API_Version => 1.0
5148 , P_internal_req_line_id =>l_req_line_id
5149 , P_internal_req_header_id =>l_req_header_id
5150 , X_Update_Allowed =>l_update_allowed
5151 , X_Cancel_Allowed =>l_cancel_allowed
5152 , X_msg_count =>X_msg_count
5153 , X_msg_data =>X_msg_data
5154 , X_return_status =>x_return_status
5155 );
5156
5157
5158 l_orgid := PO_ReqChangeRequestWF_PVT.get_requisition_org( p_req_hdr_id => l_req_header_id);
5159
5160 IF l_orgid is NOT NULL THEN
5161 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
5162 END IF;
5163
5164 x_update_allowed := POR_UTIL_PKG.bool_to_varchar(l_update_allowed);
5165 x_cancel_allowed := POR_UTIL_PKG.bool_to_varchar(l_cancel_allowed);
5166
5167 IF g_debug_stmt THEN
5168 po_debug.debug_var(l_log_head, l_progress, 'x_update_allowed', x_update_allowed);
5169 po_debug.debug_var(l_log_head, l_progress, 'x_cancel_allowed', x_cancel_allowed);
5170 po_debug.debug_end(l_log_head);
5171
5172 END IF;
5173 EXCEPTION
5174
5175 WHEN no_data_found THEN
5176 x_return_status := fnd_api.g_ret_sts_error;
5177 RETURN;
5178
5179 WHEN OTHERS THEN
5180 x_return_status := fnd_api.g_ret_sts_unexp_error;
5181 IF (g_fnd_debug = 'Y') THEN
5182 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
5183 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
5184 l_api_name || '.others_exception',
5185 p_req_line_id || ':' || SQLERRM);
5186 END IF;
5187 END IF;
5188 RETURN;
5189
5190 END is_internal_line_changeable;
5191
5192 /*--------------------------------------------------------------
5193 **Save_IReqCancel: takes in a PLSQL table as input, containing
5194 **cancellation request. No Validation is done here. This API
5195 **simply insert records into PO_CHANGE_REQUESTS table
5196 ---------------------------------------------------------------*/
5197
5198
5199 PROCEDURE save_ireqcancel(p_api_version IN NUMBER,
5200 x_return_status OUT NOCOPY VARCHAR2,
5201 p_req_hdr_id IN NUMBER,
5202 p_cancel_table IN po_req_cancel_table,
5203 p_change_request_group_id OUT NOCOPY NUMBER,
5204 l_progress OUT NOCOPY VARCHAR2,
5205 p_grp_id IN NUMBER)
5206 IS
5207 l_api_name VARCHAR2(50) := 'Save_IReqCancel';
5208 l_chn_req_id NUMBER;
5209 l_req_num po_requisition_headers_all.segment1%TYPE;
5210 l_req_line_num NUMBER;
5211 l_req_user_id NUMBER;
5212 l_line_loc_id NUMBER;
5213 l_po_header_id NUMBER;
5214 l_po_release_id NUMBER;
5215 l_po_revision_num NUMBER;
5216 l_preparer_id NUMBER;
5217 l_req_price NUMBER;
5218 l_req_currency_price NUMBER;
5219 l_req_quantity NUMBER;
5220 l_req_date DATE;
5221 l_po_num po_change_requests.ref_po_num%TYPE;
5222 l_po_release_num NUMBER;
5223 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
5224
5225 BEGIN
5226 l_progress := '000';
5227 l_req_user_id := fnd_global.user_id;
5228 IF g_debug_stmt THEN
5229 po_debug.debug_var(l_log_head, l_progress, 'p_api_version=', p_api_version);
5230 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id=', p_req_hdr_id);
5231 po_debug.debug_var(l_log_head, l_progress, 'p_grp_id=', p_grp_id);
5232 END IF;
5233
5234 IF(p_grp_id IS NULL) THEN
5235 SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
5236 DELETE FROM po_change_requests
5237 WHERE document_header_id = p_req_hdr_id
5238 AND initiator = 'REQUESTER'
5239 AND request_status = 'SYSTEMSAVE';
5240
5241 IF g_debug_stmt THEN
5242 po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
5243 po_debug.debug_var(l_log_head, l_progress, 'p_change_request_group_id=', p_change_request_group_id);
5244 END IF;
5245
5246 ELSE
5247 p_change_request_group_id := p_grp_id;
5248 END IF;
5249 l_progress := '001';
5250
5251 IF g_debug_stmt THEN
5252 po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
5253 po_debug.debug_var(l_log_head, l_progress, 'p_cancel_table.req_line_id.count=', p_cancel_table.req_line_id.count);
5254 END IF;
5255
5256
5257
5258 FOR i IN 1..p_cancel_table.req_line_id.count
5259 LOOP
5260
5261 SELECT po_chg_request_seq.nextval INTO l_chn_req_id FROM dual;
5262 SELECT
5263 prha.segment1,
5264 prla.line_num,
5265 prha.preparer_id,
5266 prla.unit_price,
5267 prla.quantity,
5268 prla.need_by_date
5269 INTO
5270 l_req_num,
5271 l_req_line_num,
5272 l_preparer_id,
5273 l_req_price,
5274 l_req_quantity,
5275 l_req_date
5276 FROM
5277 po_requisition_headers_all prha,
5278 po_requisition_lines_all prla
5279 WHERE prla.requisition_line_id = p_cancel_table.req_line_id(i)
5280 AND prla.requisition_header_id = prha.requisition_header_id;
5281
5282 /* if(l_line_loc_id is not null) then
5283 select
5284 po_release_id,
5285 po_header_id
5286 into
5287 l_po_release_id,
5288 l_po_header_id
5289 from po_line_locations_all
5290 where line_location_id = l_line_loc_id;
5291 if(l_po_release_id is null) then
5292 select revision_num,segment1 into
5293 l_po_revision_num, l_po_num
5294 from po_headers_all
5295 where po_header_id = l_po_header_id;
5296
5297 -- bug 5191164.
5298 -- Need to null out l_po_release_num for PO records
5299 l_po_release_num := null;
5300
5301 else
5302 -- get po_number of the source document for RELEASE
5303 select segment1 into l_po_num
5304 from po_headers_all
5305 where po_header_id = l_po_header_id;
5306
5307 select revision_num, release_num
5308 into l_po_revision_num, l_po_release_num
5309 from po_releases_all
5310 where po_release_id = l_po_release_id;
5311 end if;
5312 end if;
5313 */
5314 l_progress := '002';
5315 INSERT INTO po_change_requests
5316 (
5317 change_request_group_id,
5318 change_request_id,
5319 initiator,
5320 action_type,
5321 request_reason,
5322 request_level,
5323 request_status,
5324 document_type,
5325 document_header_id,
5326 document_num,
5327 created_by,
5328 creation_date,
5329 document_line_id,
5330 document_line_number,
5331 last_updated_by,
5332 last_update_date,
5333 last_update_login,
5334 requester_id,
5335 change_active_flag,
5336 old_price,
5337 old_quantity,
5338 old_need_by_date
5339 )
5340 VALUES
5341 (
5342 p_change_request_group_id,
5343 l_chn_req_id,
5344 'REQUESTER',
5345 'CANCELLATION',
5346 p_cancel_table.change_reason(i),
5347 'LINE',
5348 'SYSTEMSAVE',
5349 'REQ',
5350 p_req_hdr_id,
5351 l_req_num,
5352 l_req_user_id,
5353 SYSDATE,
5354 p_cancel_table.req_line_id(i),
5355 l_req_line_num,
5356 l_req_user_id,
5357 SYSDATE,
5358 l_req_user_id,
5359 l_preparer_id,
5360 'Y',
5361 l_req_price,
5362 l_req_quantity,
5363 l_req_date
5364 );
5365
5366 IF g_debug_stmt THEN
5367 po_debug.debug_var(l_log_head, l_progress,'NO of records inderted in po_change_requests =', SQL%rowcount);
5368 po_debug.debug_var(l_log_head, l_progress, 'p_change_request_group_id=', p_change_request_group_id);
5369 po_debug.debug_var(l_log_head, l_progress,'l_chn_req_id =', l_chn_req_id );
5370 po_debug.debug_stmt(l_log_head, l_progress,'INITIATOR =REQUESTER' );
5371 po_debug.debug_stmt(l_log_head, l_progress, 'ACTION_TYPE=CANCELLATION' );
5372 po_debug.debug_var(l_log_head, l_progress,'p_cancel_table.change_reason(i) =', p_cancel_table.change_reason(i) );
5373 po_debug.debug_stmt(l_log_head, l_progress, 'REQUEST_LEVEL=LINE' );
5374 po_debug.debug_stmt(l_log_head, l_progress, 'REQUEST_STATUS=SYSTEMSAVE' );
5375 po_debug.debug_stmt(l_log_head, l_progress, 'DOCUMENT_TYPE=REQ' );
5376 po_debug.debug_var(l_log_head, l_progress,'p_req_hdr_id =', p_req_hdr_id );
5377 po_debug.debug_var(l_log_head, l_progress,'l_req_num =', l_req_num );
5378 po_debug.debug_var(l_log_head, l_progress,'l_req_user_id =', l_req_user_id );
5379 po_debug.debug_var(l_log_head, l_progress,'p_cancel_table.req_line_id(i) =', p_cancel_table.req_line_id(i) );
5380 po_debug.debug_var(l_log_head, l_progress,'l_req_line_num =', l_req_line_num );
5381 po_debug.debug_var(l_log_head, l_progress,'l_req_user_id =', l_req_user_id );
5382 po_debug.debug_var(l_log_head, l_progress,'l_req_user_id =', l_req_user_id );
5383 po_debug.debug_var(l_log_head, l_progress,'l_preparer_id =', l_preparer_id );
5384 po_debug.debug_stmt(l_log_head, l_progress, 'CHANGE_ACTIVE_FLAG=Y' );
5385 po_debug.debug_var(l_log_head, l_progress,'l_req_price =', l_req_price );
5386 po_debug.debug_var(l_log_head, l_progress,'l_req_quantity =', l_req_quantity );
5387 po_debug.debug_var(l_log_head, l_progress, 'l_req_date=', l_req_date);
5388 END IF;
5389
5390 END LOOP;
5391
5392
5393 x_return_status := fnd_api.g_ret_sts_success;
5394 EXCEPTION WHEN OTHERS THEN
5395 x_return_status := fnd_api.g_ret_sts_unexp_error;
5396 IF g_fnd_debug = 'Y' THEN
5397 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
5398 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
5399 l_api_name || '.others_exception', l_progress || ':' || SQLERRM);
5400 END IF;
5401 END IF;
5402 END save_ireqcancel;
5403
5404 /*-------------------------------------------------------------------------------------------------
5405 *This API is called directly from the UI. It will have PLSQL tables as input, which contain change/cancel requests
5406 *1. Validate the requests
5407 *2. If ALL valid, same them into PO_CHANGE_REQUESTS table
5408 *x_return_status = FND_API.G_RET_STS_SUCCESS => Everything is Valid, and records are saved into change table
5409 * FND_API.G_RET_STS_ERROR => Caught Errors, thus no records are saved into change table
5410 * FND_API.G_RET_STS_UNEXP_ERROR => Unexpected Errors Occur in the API
5411 *x_retMsg will indicate details/location of errors.
5412 ---------------------------------------------------------------------------------------------------*/
5413 PROCEDURE save_ireqchange(p_api_version IN NUMBER,
5414 x_return_status OUT NOCOPY VARCHAR2,
5415 p_req_hdr_id IN NUMBER,
5416 p_change_table IN po_req_change_table,
5417 p_cancel_table IN po_req_cancel_table,
5418 p_change_request_group_id OUT NOCOPY NUMBER,
5419 x_retmsg OUT NOCOPY VARCHAR2,
5420 x_errtable OUT NOCOPY po_req_change_err_table)
5421 IS
5422 l_api_name VARCHAR2(50) := 'Save_IReqChange';
5423 l_req_change_table change_tbl_type;
5424 l_dummy NUMBER;
5425 y NUMBER := 1;
5426 l_change_result VARCHAR2(1) := fnd_api.g_ret_sts_success;
5427 l_cancel_result VARCHAR2(1);
5428 l_err_line_id_tbl po_tbl_number;
5429 l_err_line_num_tbl po_tbl_number;
5430 l_err_dist_id_tbl po_tbl_number;
5431 l_err_dist_num_tbl po_tbl_number;
5432 l_err_error_attr_tbl po_tbl_varchar30;
5433 l_err_msg_count_tbl po_tbl_number;
5434 l_err_msg_data_tbl po_tbl_varchar2000;
5435 l_irc_status VARCHAR2(1);
5436 l_irc_err_msg VARCHAR2(2000);
5437 l_catch_exception EXCEPTION;
5438 l_req_dist_id NUMBER;
5439 l_lineqty_status VARCHAR2(1);
5440 l_lineqty_msg VARCHAR2(2000);
5441 l_req_num NUMBER;
5442 l_req_dist_number NUMBER;
5443 l_old_req_date DATE;
5444 l_old_req_quantity NUMBER;
5445 l_old_amount NUMBER;
5446 l_preparer_id NUMBER;
5447 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
5448 l_progress VARCHAR2(3) := '000';
5449
5450
5451 BEGIN
5452
5453 IF g_debug_stmt THEN
5454 po_debug.debug_begin(l_log_head);
5455 po_debug.debug_var(l_log_head, l_progress, 'p_api_version', p_api_version);
5456 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
5457 END IF;
5458
5459
5460 DELETE FROM po_change_requests
5461 WHERE document_header_id = p_req_hdr_id
5462 AND request_status = 'SYSTEMSAVE'
5463 AND initiator = 'REQUESTER';
5464
5465 IF g_debug_stmt THEN
5466 po_debug.debug_var(l_log_head, l_progress,'NO of rows deleted from PO_CHANGE_REQUESTS', SQL%rowcount);
5467 END IF;
5468
5469
5470
5471 x_retmsg := 'SRCH000';
5472
5473 IF(p_change_table IS NOT NULL) THEN
5474 --Input Change Table is p_change_table, which is a table of objects. The following "for" loop
5475 --Copy the data from p_change_table to l_req_change_table, which is a table of record
5476 IF g_debug_stmt THEN
5477 po_debug.debug_var(l_log_head, l_progress,'p_change_table is not null and count=', p_change_table.req_line_id.count);
5478 END IF;
5479
5480
5481
5482 FOR x IN 1..p_change_table.req_line_id.count
5483 LOOP
5484 IF(p_change_table.need_by(x) IS NOT NULL
5485 -- OR p_change_table.amount(x) is not null
5486 OR p_change_table.quantity(x) IS NOT NULL) THEN
5487
5488 IF g_debug_stmt THEN
5489 po_debug.debug_stmt(l_log_head, l_progress,'In p_change_table need by date or quantity is changed');
5490 END IF;
5491
5492
5493 BEGIN
5494 SELECT
5495 prha.segment1,
5496 prda.distribution_id,
5497 prla.need_by_date,
5498 prla.quantity,
5499 prla.amount,
5500 prha.preparer_id
5501 INTO
5502 l_req_num,
5503 l_req_dist_number,
5504 l_old_req_date,
5505 l_old_req_quantity,
5506 l_old_amount,
5507 l_preparer_id
5508 FROM
5509 po_requisition_lines_all prla,
5510 po_requisition_headers_all prha,
5511 po_req_distributions_all prda
5512 WHERE
5513 prha.requisition_header_id = p_req_hdr_id
5514 AND prha.requisition_header_id = prla.requisition_header_id
5515 AND prla.requisition_line_id = p_change_table.req_line_id(x)
5516 AND prda.requisition_line_id = prla.requisition_line_id;
5517 EXCEPTION
5518 WHEN OTHERS THEN
5519 NULL;
5520 END;
5521
5522
5523
5524 l_req_change_table(y).action_type := 'MODIFICATION';
5525 l_req_change_table(y).request_level := 'LINE';
5526 l_req_change_table(y).request_status := 'SYSTEMSAVE';
5527 l_req_change_table(y).initiator := 'REQUESTER';
5528 l_req_change_table(y).document_header_id := p_req_hdr_id;
5529 l_req_change_table(y).document_num := l_req_num;
5530 l_req_change_table(y).document_line_id := p_change_table.req_line_id(x);
5531 l_req_change_table(y).document_distribution_id := l_req_dist_number;
5532 l_req_change_table(y).old_quantity := l_old_req_quantity;
5533 l_req_change_table(y).old_date := l_old_req_date;
5534 l_req_change_table(y).requester_id := l_preparer_id;
5535 l_req_change_table(y).old_budget_amount := l_old_amount;
5536
5537 IF g_debug_stmt THEN
5538 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).ACTION_TYPE', l_req_change_table(y).action_type);
5539 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).REQUEST_LEVEL', l_req_change_table(y).request_level);
5540 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).request_status', l_req_change_table(y).request_status);
5541 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).INITIATOR', l_req_change_table(y).initiator);
5542 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).DOCUMENT_HEADER_ID', l_req_change_table(y).document_header_id);
5543 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).DOCUMENT_NUM', l_req_change_table(y).document_num);
5544 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).DOCUMENT_LINE_ID', l_req_change_table(y).document_line_id);
5545 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).DOCUMENT_DISTRIBUTION_ID', l_req_change_table(y).document_distribution_id);
5546 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).OLD_QUANTITY', l_req_change_table(y).old_quantity);
5547 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).old_date', l_req_change_table(y).old_date);
5548 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).REQUESTER_ID', l_req_change_table(y).requester_id);
5549 po_debug.debug_var(l_log_head, l_progress, 'l_req_change_table(y).OLD_BUDGET_AMOUNT', l_req_change_table(y).old_budget_amount);
5550
5551 END IF;
5552
5553 /*
5554 if(p_change_table.amount(x) is not null) then
5555
5556 l_req_change_table(y).NEW_BUDGET_AMOUNT := p_change_table.amount(x);
5557 l_req_change_table(y).REQUEST_REASON := p_change_table.change_reason(x);
5558 y:=y+1;
5559 END IF; */
5560
5561 IF (p_change_table.need_by(x) IS NOT NULL) THEN
5562
5563 l_req_change_table(y).new_date := p_change_table.need_by(x);
5564 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
5565 -- y:=y+1;
5566 END IF;
5567 IF(p_change_table.quantity(x) IS NOT NULL) THEN
5568
5569 l_req_change_table(y).new_quantity := p_change_table.quantity(x);
5570 l_req_change_table(y).request_reason := p_change_table.change_reason(x);
5571 -- y:=y+1;
5572 END IF;
5573 y := y + 1;
5574 END IF;
5575
5576 END LOOP;
5577
5578
5579
5580 --Validate the Change Requests, by passing in l_req_change_table, a table of records
5581 --Initialize the Error Table
5582 l_err_line_id_tbl := po_tbl_number();
5583 l_err_line_num_tbl := po_tbl_number();
5584 l_err_dist_id_tbl := po_tbl_number();
5585 l_err_dist_num_tbl := po_tbl_number();
5586 l_err_error_attr_tbl := po_tbl_varchar30();
5587 l_err_msg_count_tbl := po_tbl_number();
5588 l_err_msg_data_tbl := po_tbl_varchar2000();
5589 x_errtable := po_req_change_err_table(
5590 l_err_line_id_tbl,
5591 l_err_line_num_tbl,
5592 l_err_dist_id_tbl,
5593 l_err_dist_num_tbl,
5594 l_err_error_attr_tbl,
5595 l_err_msg_count_tbl,
5596 l_err_msg_data_tbl);
5597
5598 -- Validate_Changes(p_req_hdr_id,l_req_change_table,l_change_result,x_retMsg,x_errTable);
5599 -- these validations are done online
5600 END IF;
5601
5602 --If ALL changes are valid, we will insert change records, and insert cancel records(if any)
5603 --if(l_change_result = FND_API.G_RET_STS_SUCCESS) then
5604 x_retmsg := 'SRCH004';
5605 SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
5606
5607
5608
5609 IF g_debug_stmt THEN
5610 po_debug.debug_var(l_log_head, l_progress, 'p_change_request_group_id=', p_change_request_group_id);
5611 END IF;
5612
5613 insert_reqchange(l_req_change_table, p_change_request_group_id);
5614 x_retmsg := 'SRCH005';
5615
5616 update_internalrecordswithtax(p_change_request_group_id);
5617 x_retmsg := 'SRCH006';
5618
5619
5620
5621 ---Insert_LineQuantityOrAmount(p_change_request_group_id); not inserting derived record in po_change_request
5622 l_change_result := fnd_api.g_ret_sts_success;
5623 x_retmsg := 'SRCH0061';
5624
5625 -- Insert_PriceBreakRows(p_change_request_group_id);
5626
5627 x_retmsg := 'SRCH0062';
5628
5629 --Process Cancellation Requests
5630 l_cancel_result := fnd_api.g_ret_sts_success;
5631
5632 IF(p_cancel_table IS NOT NULL) THEN
5633 save_ireqcancel(1.0, l_cancel_result, p_req_hdr_id, p_cancel_table, l_dummy, x_retmsg, p_change_request_group_id);
5634 END IF;
5635
5636 --x_return_status := l_cancel_result;
5637 x_return_status := fnd_api.g_ret_sts_success;
5638
5639 IF(l_change_result = fnd_api.g_ret_sts_error) THEN
5640 x_return_status := fnd_api.g_ret_sts_error;
5641 x_retmsg := 'SRCH007';
5642 ELSE
5643 x_return_status := fnd_api.g_ret_sts_success;
5644
5645 END IF;
5646
5647 EXCEPTION
5648 WHEN OTHERS THEN
5649 x_retmsg := 'SRCHUNEXP:' || x_retmsg || ':' || SQLERRM;
5650 x_return_status := fnd_api.g_ret_sts_unexp_error;
5651
5652
5653
5654 IF g_fnd_debug = 'Y' THEN
5655 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
5656 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
5657 l_api_name || '.others_exception', x_retmsg);
5658 END IF;
5659 END IF;
5660 END save_ireqchange;
5661
5662
5663
5664 /*
5665 **Submit_IReqCancel: Final procedure call for transaction which involves requester
5666 **cancelling a req line.
5667 **This API could be called from the Cancel Flow UI directly, or from Submit_ReqChange API.
5668 */
5669 PROCEDURE submit_ireqcancel (
5670 p_api_version IN NUMBER,
5671 x_return_status OUT NOCOPY VARCHAR2,
5672 p_group_id IN NUMBER,
5673 x_retmsg OUT NOCOPY VARCHAR2,
5674 p_errtable OUT NOCOPY po_req_change_err_table,
5675 p_origin IN VARCHAR2)
5676 IS
5677 l_api_name VARCHAR2(50) := 'Submit_IReqCancel';
5678 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
5679 l_line_id NUMBER;
5680 l_result VARCHAR2(1);
5681 l_progress VARCHAR2(3):='000';
5682 i NUMBER := 1;
5683 l_canerr_line_id_tbl po_tbl_number;
5684 l_canerr_line_num_tbl po_tbl_number;
5685 l_canerr_dist_id_tbl po_tbl_number;
5686 l_canerr_dist_num_tbl po_tbl_number;
5687 l_canerr_error_attr_tbl po_tbl_varchar30;
5688 l_canerr_msg_count_tbl po_tbl_number;
5689 l_canerr_msg_data_tbl po_tbl_varchar2000;
5690 l_line_location_id NUMBER;
5691 l_chn_req_id NUMBER;
5692 l_req_hdr_id NUMBER;
5693 l_wf_status VARCHAR2(1);
5694 l_workflow_needed VARCHAR2(1) := 'Y';
5695 CURSOR l_cancels_csr(grp_id NUMBER) IS
5696 SELECT
5697 pcr.document_header_id,
5698 pcr.document_line_id,
5699 prla.line_location_id,
5700 pcr.change_request_id
5701 FROM
5702 po_change_requests pcr,
5703 po_requisition_lines_all prla
5704 WHERE pcr.action_type = 'CANCELLATION'
5705 AND pcr.change_request_group_id = grp_id
5706 AND pcr.document_line_id = prla.requisition_line_id;
5707 BEGIN
5708 IF g_debug_stmt THEN
5709 po_debug.debug_var(l_log_head, l_progress, 'p_group_id', p_group_id);
5710 END IF;
5711
5712
5713 l_canerr_line_id_tbl := po_tbl_number();
5714 l_canerr_line_num_tbl := po_tbl_number();
5715 l_canerr_dist_id_tbl := po_tbl_number();
5716 l_canerr_dist_num_tbl := po_tbl_number();
5717 l_canerr_error_attr_tbl := po_tbl_varchar30();
5718 l_canerr_msg_count_tbl := po_tbl_number();
5719 l_canerr_msg_data_tbl := po_tbl_varchar2000();
5720
5721 p_errtable := po_req_change_err_table(
5722 l_canerr_line_id_tbl ,
5723 l_canerr_line_num_tbl,
5724 l_canerr_dist_id_tbl ,
5725 l_canerr_dist_num_tbl,
5726 l_canerr_error_attr_tbl,
5727 l_canerr_msg_count_tbl ,
5728 l_canerr_msg_data_tbl );
5729
5730 --Calling PO Cancel API to check if the corresponding PO Shipment Can be cancelled.
5731 OPEN l_cancels_csr(p_group_id);
5732 LOOP
5733 FETCH l_cancels_csr INTO
5734 l_req_hdr_id, l_line_id, l_line_location_id, l_chn_req_id;
5735 EXIT WHEN l_cancels_csr%notfound;
5736 /* if(l_line_location_id is null) then
5737 update po_change_requests
5738 set request_status = 'ACCEPTED'
5739 where change_request_id = l_chn_req_id;
5740 else */--since only lines on so is cancellable so wf is always needed
5741 l_workflow_needed := 'Y';
5742
5743 -- end if;
5744 END LOOP;
5745
5746 CLOSE l_cancels_csr;
5747
5748 x_return_status := fnd_api.g_ret_sts_success;
5749
5750 --If all requests are valid, update status to "NEW", and kick off Workflow
5751
5752 IF(p_errtable.req_line_id.count = 0) THEN
5753
5754
5755
5756
5757 UPDATE po_change_requests
5758 SET request_status = 'NEW'
5759 WHERE change_request_group_id = p_group_id
5760 AND request_status = 'SYSTEMSAVE';
5761
5762
5763
5764
5765 IF (p_origin IS NULL AND l_workflow_needed = 'Y') THEN
5766
5767 po_reqchangerequestwf_pvt.submit_internal_req_change(
5768 p_api_version => 1.0,
5769 p_commit => fnd_api.g_false,
5770 x_return_status => l_wf_status,
5771 p_req_header_id => l_req_hdr_id,
5772 p_note_to_approver => NULL,
5773 p_initiator => 'REQUESTER');
5774 END IF;
5775
5776
5777
5778
5779 ELSE
5780 x_return_status := fnd_api.g_ret_sts_error;
5781 END IF;
5782
5783 EXCEPTION WHEN OTHERS THEN
5784 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5785 IF g_fnd_debug = 'Y' THEN
5786 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
5787 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
5788 l_api_name || '.others_exception', SQLERRM);
5789 END IF;
5790 END IF;
5791 END submit_ireqcancel;
5792
5793
5794 PROCEDURE get_preparer_name(
5795 p_req_hdr_id IN NUMBER
5796 , x_preparer_name OUT NOCOPY VARCHAR2
5797 , x_return_status OUT NOCOPY VARCHAR2
5798 )
5799 IS
5800 l_preparer_id NUMBER;
5801 x_preparer_display_name varchar2(360);
5802 BEGIN
5803
5804 SELECT preparer_id into l_preparer_id
5805 FROM po_requisition_headers_all
5806 WHERE requisition_header_id = p_req_hdr_id;
5807
5808 WF_DIRECTORY.GetUserName( 'PER',
5809 l_preparer_id,
5810 x_preparer_name,
5811 x_preparer_display_name);
5812
5813
5814 x_return_status := fnd_api.g_ret_sts_success;
5815 END get_preparer_name;
5816
5817
5818
5819 PROCEDURE update_reqcancel_from_so(
5820 p_req_hdr_id IN NUMBER
5821 , p_req_line_id IN NUMBER
5822 , x_return_status OUT NOCOPY VARCHAR2
5823 )
5824 IS
5825
5826 CURSOR req_lines_cursor(x_requisition_header_id NUMBER) IS
5827 SELECT requisition_line_id
5828 FROM po_requisition_lines_all
5829 WHERE requisition_header_id = x_requisition_header_id
5830 AND SOURCE_TYPE_CODE = 'INVENTORY';
5831
5832
5833 l_req_line_id NUMBER;
5834 l_bool_ret_sts BOOLEAN;
5835 l_return_status VARCHAR2(10);
5836 l_open_quantity number;
5837 l_quantity_delivered NUMBER;
5838 l_quantity NUMBER;
5839 l_delta_quantity number;
5840 l_log_head CONSTANT VARCHAR2(100) := c_log_head || 'update_reqcancel_from_so';
5841 l_progress varchar2(3):='000';
5842 l_count number:=0;
5843 BEGIN
5844
5845 -- Bug 8235698: Check whether there is a pending change on this
5846 -- requisition line , if there exists one then
5847 -- return Error to the SO (OM) API.
5848
5849 BEGIN
5850
5851 SELECT COUNT(*) INTO l_count FROM po_change_requests
5852 WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
5853 AND DOCUMENT_TYPE= 'REQ'
5854 AND REQUEST_LEVEL= 'LINE'
5855 AND DOCUMENT_LINE_ID=p_req_line_id;
5856
5857 EXCEPTION
5858 WHEN no_data_found THEN
5859 l_count:=0;
5860 END;
5861
5862 IF (l_count <> 0 ) THEN
5863
5864 x_return_status := FND_API.G_RET_STS_ERROR;
5865 IF g_debug_stmt THEN
5866 po_debug.debug_begin(l_log_head);
5867 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
5868 po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
5869 po_debug.debug_var(l_log_head, l_progress, 'No of po_change_requests on req line', l_count);
5870 END IF;
5871
5872 return;
5873 END IF;
5874 -- have save point
5875 SAVEPOINT update_reqcancel_from_so_sp;
5876 /*
5877 * ALGORITHM : For each req line or perticular line
5878 Step 1: Retrive the open receiving quantity INTO l_open_quantity
5879 Step 2 : Retrive the REQ LINE quantity INTO l_quantity
5880 */
5881
5882 l_return_status := fnd_api.g_ret_sts_success;
5883 l_progress:='001';
5884
5885 IF g_debug_stmt THEN
5886 po_debug.debug_begin(l_log_head);
5887 po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
5888 END IF;
5889
5890 IF (p_req_hdr_id IS NOT NULL AND p_req_line_id IS NULL) THEN
5891 l_progress:='002';
5892
5893 OPEN req_lines_cursor (p_req_hdr_id) ;
5894 LOOP
5895 FETCH req_lines_cursor INTO l_req_line_id;
5896 EXIT WHEN req_lines_cursor%notfound;
5897 BEGIN
5898
5899 SELECT nvl(sum(w.shipped_quantity),0)
5900 INTO l_open_quantity
5901 FROM oe_order_lines_all oel
5902 ,oe_order_headers_all oeh
5903 ,wsh_delivery_details w
5904 ,po_requisition_lines_all pol
5905 WHERE
5906 oel.header_id = oeh.header_id
5907 AND oel.line_id = w.source_line_id
5908 AND w.source_code = 'OE'
5909 AND w.released_status = 'C'
5910 AND oel.source_document_line_id=pol.requisition_line_id
5911 AND oel.source_document_id=pol.requisition_header_id
5912 AND oeh.source_document_type_id =10
5913 AND pol.REQUISITION_LINE_ID=l_req_line_id;
5914 EXCEPTION
5915 WHEN NO_DATA_FOUND THEN
5916 l_open_quantity := 0;
5917 WHEN OTHERS THEN
5918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5919 END;
5920 BEGIN
5921 SELECT QUANTITY, nvl(QUANTITY_DELIVERED,0)
5922 INTO l_quantity, l_quantity_delivered
5923 FROM po_requisition_lines_all
5924 WHERE REQUISITION_LINE_ID=l_req_line_id;
5925 EXCEPTION
5926 WHEN OTHERS THEN
5927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5928 END;
5929 l_progress:='003';
5930
5931 IF g_debug_stmt THEN
5932 po_debug.debug_var(l_log_head, l_progress, 'l_quantity', l_quantity);
5933 po_debug.debug_var(l_log_head, l_progress, 'l_quantity_delivered', l_quantity_delivered);
5934 po_debug.debug_var(l_log_head, l_progress, 'l_open_quantity', l_open_quantity);
5935 END IF;
5936 IF (l_open_quantity IS NOT NULL AND l_quantity IS NOT NULL ) THEN
5937 IF (l_quantity - l_open_quantity = 0) THEN
5938 x_return_status:= FND_API.G_RET_STS_ERROR;
5939 RETURN;
5940 ELSE
5941 IF (l_quantity - l_open_quantity > 0) THEN
5942 -- THIS CAN BE EITHER CANCELLATION OR UPDATE
5943 IF (l_quantity_delivered < l_open_quantity) THEN
5944 -- UPDATE THE REQ LINE WITH DELTA QUANTITY AS
5945 l_delta_quantity := l_open_quantity-l_quantity ;
5946 IF g_debug_stmt THEN
5947 po_debug.debug_var(l_log_head, l_progress, 'l_delta_quantity', l_delta_quantity);
5948 END IF;
5949 l_progress:='004';
5950
5951 po_reqchangerequestwf_pvt.update_reqline_quan_changes(
5952 p_req_line_id => p_req_line_id,
5953 p_delta_quantity=> l_delta_quantity,
5954 x_return_status =>l_return_status);
5955
5956 else
5957 po_reqchangerequestwf_pvt.req_line_CANCEL(
5958 p_req_line_id => l_req_line_id,
5959 x_return_status =>l_return_status);
5960 END IF;
5961 END IF;
5962 END IF;
5963 END IF;
5964
5965
5966
5967
5968
5969 END LOOP;
5970 CLOSE req_lines_cursor;
5971
5972 ELSE IF (p_req_line_id IS NOT NULL ) THEN
5973 BEGIN
5974 SELECT nvl(sum(w.shipped_quantity),0)
5975 INTO l_open_quantity
5976 FROM oe_order_lines_all oel
5977 ,oe_order_headers_all oeh
5978 ,wsh_delivery_details w
5979 ,po_requisition_lines_all pol
5980 WHERE
5981 oel.header_id = oeh.header_id
5982 AND oel.line_id = w.source_line_id
5983 AND w.source_code = 'OE'
5984 AND w.released_status = 'C'
5985 AND oel.source_document_line_id=pol.requisition_line_id
5986 AND oel.source_document_id=pol.requisition_header_id
5987 AND oeh.source_document_type_id =10
5988 AND pol.REQUISITION_LINE_ID=p_req_line_id;
5989 EXCEPTION
5990 WHEN NO_DATA_FOUND THEN
5991 l_open_quantity := 0;
5992 WHEN OTHERS THEN
5993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5994 END;
5995 BEGIN
5996 SELECT QUANTITY, nvl(QUANTITY_DELIVERED,0)
5997 INTO l_quantity, l_quantity_delivered
5998 FROM po_requisition_lines_all
5999 WHERE REQUISITION_LINE_ID=p_req_line_id;
6000 EXCEPTION
6001 WHEN OTHERS THEN
6002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6003 END;
6004
6005 IF g_debug_stmt THEN
6006 po_debug.debug_var(l_log_head, l_progress, 'l_quantity', l_quantity);
6007 po_debug.debug_var(l_log_head, l_progress, 'l_quantity_delivered', l_quantity_delivered);
6008 po_debug.debug_var(l_log_head, l_progress, 'l_open_quantity', l_open_quantity);
6009 END IF;
6010
6011 IF (l_open_quantity IS NOT NULL AND l_quantity IS NOT NULL ) THEN
6012 IF (l_quantity - l_open_quantity = 0) THEN
6013 x_return_status:= FND_API.G_RET_STS_ERROR;
6014 IF g_debug_stmt THEN
6015 po_debug.debug_var(l_log_head, l_progress, 'x_return_status', x_return_status);
6016 END IF;
6017
6018 RETURN;
6019 ELSE
6020 IF (l_quantity - l_open_quantity > 0) THEN
6021 -- THIS CAN BE EITHER CANCELLATION OR UPDATE
6022 IF (l_quantity_delivered < l_open_quantity) THEN
6023 -- UPDATE THE REQ LINE WITH DELTA QUANTITY AS
6024 l_delta_quantity := l_open_quantity-l_quantity ;
6025 IF g_debug_stmt THEN
6026 po_debug.debug_var(l_log_head, l_progress, 'l_delta_quantity', l_delta_quantity);
6027 END IF;
6028 po_reqchangerequestwf_pvt.update_reqline_quan_changes(
6029 p_req_line_id => p_req_line_id,
6030 p_delta_quantity=> l_delta_quantity,
6031 x_return_status =>l_return_status);
6032
6033 else
6034 po_reqchangerequestwf_pvt.req_line_CANCEL(
6035 p_req_line_id => p_req_line_id,
6036 x_return_status =>l_return_status);
6037 END IF;
6038 END IF;
6039 END IF;
6040 END IF;
6041
6042 END IF;
6043 END IF;
6044
6045 IF g_debug_stmt THEN
6046 po_debug.debug_var(l_log_head, l_progress, 'l_return_status', l_return_status);
6047 END IF;
6048
6049 x_return_status := fnd_api.g_ret_sts_success;
6050
6051 EXCEPTION
6052 WHEN OTHERS THEN
6053 x_return_status:= FND_API.G_RET_STS_ERROR;
6054 ROLLBACK TO update_reqcancel_from_so_sp;
6055 END update_reqcancel_from_so;
6056
6057
6058
6059 PROCEDURE update_reqchange_from_so(
6060 p_req_line_id IN NUMBER
6061 , p_delta_quantity IN NUMBER
6062 , p_new_need_by_date IN DATE
6063 , x_return_status OUT NOCOPY VARCHAR2
6064 )
6065 IS
6066
6067 l_bool_ret_sts BOOLEAN;
6068 l_mtl_supply_quantity NUMBER;
6069 l_return_status varchar2(10);
6070 l_sync_need_by varchar2(3);
6071 l_count number:=0;
6072
6073 BEGIN
6074 -- Check whether there is a pending change on this
6075 -- requisition line , if there exists one then
6076 -- return Error to the SO (OM) API.
6077
6078 BEGIN
6079 SELECT count(*) INTO l_count
6080 FROM PO_change_requests
6081 WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
6082 AND DOCUMENT_TYPE= 'REQ'
6083 AND REQUEST_LEVEL= 'LINE'
6084 AND DOCUMENT_LINE_ID=p_req_line_id;
6085
6086 EXCEPTION
6087 WHEN no_data_found THEN
6088 l_count :=0;
6089 END;
6090
6091 IF (l_count <> 0 ) THEN
6092 x_return_status := FND_API.G_RET_STS_ERROR;
6093 return;
6094 END IF;
6095
6096 -- have save point
6097 SAVEPOINT update_reqchange_from_so_s;
6098 l_return_status := fnd_api.g_ret_sts_success;
6099
6100 --algo
6101 -- for the given req line the given attribute needs to be chnaged
6102 -- for quantity .. input is the delta qunatity = new qunatity-old quantity
6103 -- hence the new line quantity shall be existing qunatity+delta quantity
6104 -- this is applicable to both mtl_supply and po_requisition_lines_all
6105
6106 -- for need by date.. input is new need by date so update this value to
6107 -- both the tables
6108 IF p_req_line_id IS NOT NULL AND p_delta_quantity IS NOT NULL THEN
6109
6110 BEGIN
6111 po_reqchangerequestwf_pvt.update_reqline_quan_changes(
6112 p_req_line_id => p_req_line_id,
6113 p_delta_quantity=> p_delta_quantity,
6114 x_return_status =>l_return_status);
6115
6116 EXCEPTION
6117 WHEN OTHERS THEN
6118 ROLLBACK TO update_reqchange_from_so_s;
6119 END;
6120
6121
6122 END IF;
6123
6124 IF (p_req_line_id IS NOT NULL AND p_new_need_by_date IS NOT NULL ) THEN
6125 BEGIN
6126 -- read the profile POR: Sync up Need by date on IR with OM
6127 -- if yes then update the req table
6128 -- else by pass this and return success
6129 l_sync_need_by := nvl(fnd_profile.value('POR_SYNC_NEEDBYDATE_OM'), 'NO');
6130
6131 IF ( l_sync_need_by = 'YES' ) THEN
6132
6133 po_reqchangerequestwf_pvt.update_req_line_date_changes(p_req_line_id=>p_req_line_id,
6134 p_need_by_date=> p_new_need_by_date,
6135 x_return_status =>l_return_status);
6136
6137 END IF;
6138 EXCEPTION
6139 WHEN OTHERS THEN
6140 ROLLBACK TO update_reqchange_from_so_s;
6141 END;
6142
6143 END IF;
6144 x_return_status := fnd_api.g_ret_sts_success;
6145
6146 END update_reqchange_from_so;
6147
6148
6149 /*-----------------------------------------------------------------------------------------------------
6150 * At the Final Stage of Requester Creating Change request, SUBMIT_IREQCHANGE will be executed to complete
6151 * the transaction. This API takes care of funds Check, and does a final round of validation against
6152 * all change/cancel requests before kicking off the Workflow.
6153 ----------------------------------------------------------------------------------------------------*/
6154 PROCEDURE submit_ireqchange (
6155 p_api_version IN NUMBER,
6156 x_return_status OUT NOCOPY VARCHAR2,
6157 p_group_id IN NUMBER,
6158 p_fundscheck_flag IN VARCHAR2,
6159 p_note_to_approver IN VARCHAR2,
6160 p_initiator IN VARCHAR2,
6161 x_retmsg OUT NOCOPY VARCHAR2,
6162 x_errcode OUT NOCOPY VARCHAR2,
6163 x_errtable OUT NOCOPY po_req_change_err_table)
6164 IS
6165 l_api_name VARCHAR2(50) := 'Submit_IReqChange';
6166 l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
6167
6168 i NUMBER := 1;
6169 l_cancelerrorsize NUMBER;
6170 l_flag_one VARCHAR2(1);
6171 l_flag_two VARCHAR2(1);
6172
6173 l_req_dist_id NUMBER;
6174 l_req_line_id NUMBER;
6175 l_req_hdr_id NUMBER;
6176 l_req_num po_requisition_headers_all.segment1%TYPE;
6177 l_budget_account_id NUMBER;
6178 l_gl_date DATE;
6179 l_old_quantity NUMBER;
6180 l_old_tax NUMBER;
6181 l_qty_changed_flag VARCHAR2(1) := fnd_api.g_false;
6182 l_change_exist VARCHAR2(1);
6183 l_cancel_exist VARCHAR2(1);
6184 l_new_quantity NUMBER;
6185 l_new_so_quantity NUMBER;
6186 l_rec_tax NUMBER;
6187 l_nonrec_tax NUMBER;
6188 l_new_tax NUMBER;
6189 l_entered_dr NUMBER;
6190 l_entered_cr NUMBER;
6191 l_org_id NUMBER;
6192 l_fc_out_tbl po_fcout_type;
6193 l_fc_result_code VARCHAR2(1);
6194 l_fc_result_status VARCHAR2(1);
6195 l_fc_msg_count NUMBER;
6196 l_fc_msg_data VARCHAR2(2000);
6197 l_fc_req_line_id NUMBER;
6198 l_fc_req_line_num NUMBER;
6199 l_fc_req_distr_id NUMBER;
6200 l_fc_req_distr_num NUMBER;
6201 l_req_change_table change_tbl_type;
6202 l_new_date DATE;
6203 l_new_need_by_date DATE;
6204 l_old_need_by_date DATE;
6205 l_old_amount NUMBER;
6206 l_new_amount NUMBER;
6207 l_new_price NUMBER;
6208 l_distribution_id NUMBER;
6209 l_request_reason po_change_requests.request_reason%TYPE;
6210 l_cancel_errtable po_req_change_err_table;
6211 l_cal_disttax_status VARCHAR2(1);
6212 l_item_id NUMBER;
6213 l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;
6214 l_po_uom po_line_locations_all.unit_meas_lookup_code%TYPE;
6215 l_po_to_req_rate NUMBER;
6216
6217 l_po_return_code VARCHAR2(100) := '';
6218 l_err_line_id_tbl po_tbl_number;
6219 l_err_line_num_tbl po_tbl_number;
6220 l_err_dist_id_tbl po_tbl_number;
6221 l_err_dist_num_tbl po_tbl_number;
6222 l_err_error_attr_tbl po_tbl_varchar30;
6223 l_err_msg_count_tbl po_tbl_number;
6224 l_err_msg_data_tbl po_tbl_varchar2000;
6225 l_wf_status VARCHAR2(1);
6226 l_distribution_id_tbl po_tbl_number;
6227
6228 CURSOR l_changes_csr(grp_id NUMBER) IS
6229 SELECT
6230 document_header_id,
6231 document_line_id,
6232 document_distribution_id,
6233 new_quantity,
6234 new_need_by_date,
6235 request_reason
6236 FROM po_change_requests
6237 WHERE change_request_group_id = grp_id
6238 AND action_type = 'MODIFICATION';
6239
6240 CURSOR l_cancel_csr(grp_id NUMBER) IS
6241 SELECT
6242 document_header_id,
6243 document_line_id,
6244 request_reason
6245 FROM po_change_requests
6246 WHERE change_request_group_id = grp_id
6247 AND action_type = 'CANCELLATION';
6248
6249
6250 CURSOR l_dist_qty_price_chn_csr(grp_id NUMBER) IS
6251 SELECT
6252 document_line_id line_id,
6253 document_distribution_id dist_id,
6254 document_header_id hdr_id,
6255 document_num req_num
6256 FROM
6257 po_change_requests
6258 WHERE
6259 change_request_group_id = grp_id AND
6260 new_quantity IS NOT NULL AND
6261 action_type = 'MODIFICATION'
6262 ;/* UNION
6263 SELECT
6264 prda.requisition_line_id line_id,
6265 prda.distribution_id dist_id,
6266 prla.requisition_header_id hdr_id,
6267 prha.segment1 req_num
6268 FROM
6269 po_req_distributions_all prda,
6270 po_requisition_lines_all prla,
6271 po_change_requests pcr,
6272 po_requisition_headers_all prha
6273 WHERE
6274 prha.requisition_header_id = prla.requisition_header_id AND
6275 prla.requisition_line_id = prda.requisition_line_id AND
6276 pcr.document_line_id = prla.requisition_line_id AND
6277 pcr.change_request_group_id = grp_id AND
6278 pcr.action_type = 'MODIFICATION' AND
6279 pcr.new_need_by_date IS NOT NULL;*/
6280
6281 -- list of req distributions effected with the req changes
6282 CURSOR l_changed_req_dists_csr(grp_id NUMBER) IS
6283 SELECT -- any quantity change
6284 pcr.document_distribution_id
6285 FROM
6286 po_change_requests pcr
6287 WHERE
6288 pcr.change_request_group_id = grp_id AND
6289 pcr.new_quantity IS NOT NULL AND
6290 pcr.action_type = 'MODIFICATION';
6291
6292 /* AND
6293 pcr.document_distribution_id = prda.distribution_id*/
6294 /* CURSOR l_changed_req_dists_csr(grp_id NUMBER) IS
6295 SELECT -- any quantity or amount change
6296 prda.distribution_id
6297 FROM
6298 po_change_requests pcr,
6299 po_req_distributions_all prda
6300 WHERE
6301 pcr.change_request_group_id = grp_id AND
6302 pcr.new_quantity IS NOT NULL AND
6303 pcr.action_type = 'MODIFICATION' AND
6304 pcr.document_distribution_id = prda.distribution_id
6305 ; UNION
6306 SELECT -- select distributions that are effected with any line change
6307 prda.distribution_id
6308 FROM
6309 po_change_requests pcr,
6310 po_requisition_lines_all prla,
6311 po_req_distributions_all prda
6312 WHERE
6313 pcr.change_request_group_id = grp_id AND
6314 pcr.action_type = 'MODIFICATION' AND
6315 pcr.new_need_by_date IS NOT NULL AND
6316 pcr.document_line_id = prla.requisition_line_id AND
6317 prla.requisition_line_id = prda.requisition_line_id ;*/
6318
6319 -- list of release distributions effected with the req changes
6320 CURSOR l_dist_tax_csr(grp_id NUMBER) IS
6321 SELECT -- any quantity change
6322 prla.requisition_line_id,
6323 prda.distribution_id,
6324 prla.requisition_header_id,
6325 prla.unit_price,
6326 nvl(pcr.new_quantity, pcr.old_quantity)
6327 FROM
6328 po_change_requests pcr,
6329 po_req_distributions_all prda,
6330 po_requisition_lines_all prla
6331 WHERE
6332 pcr.change_request_group_id = grp_id AND
6333 pcr.new_quantity IS NOT NULL AND
6334 -- (pcr.new_quantity IS NOT NULL OR pcr.new_need_by_date IS NOT NULL) AND
6335 pcr.action_type = 'MODIFICATION' AND
6336 pcr.document_distribution_id = prda.distribution_id AND
6337 prla.requisition_line_id = prda.requisition_line_id AND
6338 pcr.document_line_id = prla.requisition_line_id ;
6339
6340 BEGIN
6341 x_return_status := fnd_api.g_ret_sts_error;
6342 x_retmsg := 'SMRCH000';
6343 x_return_status := fnd_api.g_ret_sts_success;
6344
6345 --Check if Funds Check is needed
6346 SELECT
6347 nvl(fsp.req_encumbrance_flag, 'N')
6348 INTO
6349 l_flag_one
6350 FROM financials_system_parameters fsp;
6351
6352 --Check if change request exist
6353 l_change_exist := 'N';
6354 OPEN l_changes_csr(p_group_id);
6355
6356 FETCH l_changes_csr
6357 INTO l_req_hdr_id, l_req_line_id, l_req_dist_id, l_new_quantity,
6358 l_new_date, l_request_reason;
6359 IF(l_req_hdr_id IS NOT NULL) THEN
6360 l_change_exist := 'Y';
6361 END IF;
6362 CLOSE l_changes_csr;
6363
6364 IF g_debug_stmt THEN
6365 po_debug.debug_var(l_log_head, x_retmsg, 'l_flag_one=', l_flag_one);
6366 po_debug.debug_var(l_log_head, x_retmsg, 'l_change_exist=', l_change_exist);
6367 po_debug.debug_var(l_log_head, x_retmsg, 'p_group_ID=', p_group_id);
6368 po_debug.debug_var(l_log_head, x_retmsg, 'l_cancel_exist=', l_cancel_exist);
6369 po_debug.debug_var(l_log_head, x_retmsg, 'l_req_hdr_id=', l_req_hdr_id);
6370 po_debug.debug_var(l_log_head, x_retmsg, 'l_req_line_id=', l_req_line_id);
6371 po_debug.debug_var(l_log_head, x_retmsg, 'l_request_reason=', l_request_reason);
6372 END IF;
6373
6374 --Check if cancel request exist
6375 l_cancel_exist := 'N';
6376 l_req_hdr_id := NULL;l_req_line_id := NULL;l_request_reason := NULL;
6377
6378 OPEN l_cancel_csr(p_group_id);
6379 FETCH l_cancel_csr
6380 INTO l_req_hdr_id, l_req_line_id, l_request_reason;
6381
6382 IF(l_req_hdr_id IS NOT NULL) THEN
6383 l_cancel_exist := 'Y';
6384 END IF;
6385
6386 CLOSE l_cancel_csr;
6387
6388 x_retmsg := 'SMRCH001';
6389
6390 IF g_debug_stmt THEN
6391 po_debug.debug_var(l_log_head, x_retmsg, 'l_flag_one=', l_flag_one);
6392 po_debug.debug_var(l_log_head, x_retmsg, 'l_change_exist=', l_change_exist);
6393 po_debug.debug_var(l_log_head, x_retmsg, 'p_group_ID=', p_group_id);
6394 po_debug.debug_var(l_log_head, x_retmsg, 'l_cancel_exist=', l_cancel_exist);
6395 po_debug.debug_var(l_log_head, x_retmsg, 'l_req_hdr_id=', l_req_hdr_id);
6396 po_debug.debug_var(l_log_head, x_retmsg, 'l_req_line_id=', l_req_line_id);
6397 po_debug.debug_var(l_log_head, x_retmsg, 'l_request_reason=', l_request_reason);
6398 END IF ;
6399
6400 --Funds Check Starts
6401 IF (l_change_exist = 'Y' AND p_fundscheck_flag = 'Y' AND l_flag_one <> 'N' ) THEN
6402
6403 x_retmsg := 'SMRCH002';
6404 IF g_debug_stmt THEN
6405 po_debug.debug_stmt(l_log_head, x_retmsg,'change exists with funds check');
6406 END IF;
6407
6408 --Check if any records require funds check.
6409 OPEN l_dist_qty_price_chn_csr(p_group_id);
6410 FETCH l_dist_qty_price_chn_csr INTO
6411 l_req_line_id,
6412 l_req_dist_id,
6413 l_req_hdr_id,
6414 l_req_num;
6415 CLOSE l_dist_qty_price_chn_csr;
6416
6417 IF (l_req_num IS NOT NULL) THEN
6418
6419 -- initialize distributions list table
6420 l_distribution_id_tbl := po_tbl_number();
6421
6422 -- insert NEW/OLD records of standard po distributions into PO_ENCUMBRANCE_GT
6423 OPEN l_changed_req_dists_csr(p_group_id);
6424
6425 FETCH l_changed_req_dists_csr BULK COLLECT
6426 INTO l_distribution_id_tbl;
6427
6428 CLOSE l_changed_req_dists_csr;
6429
6430 po_document_funds_grp.populate_encumbrance_gt(
6431 p_api_version => 1.0,
6432 x_return_status => x_return_status,
6433 p_doc_type => po_document_funds_grp.g_doc_type_requisition, --call with req type at dist level
6434 p_doc_level => po_document_funds_grp.g_doc_level_distribution,
6435 p_doc_level_id_tbl => l_distribution_id_tbl,
6436 p_make_old_copies_flag => po_document_funds_grp.g_parameter_yes,
6437 p_make_new_copies_flag => po_document_funds_grp.g_parameter_yes,
6438 p_check_only_flag => po_document_funds_grp.g_parameter_yes);
6439
6440 -- error handling after calling populate_encumbrance_gt
6441 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
6442 x_retmsg := 'After calling populate_encumbrance_gt';
6443 x_errcode := 'FC_FAIL';
6444 IF g_debug_stmt THEN
6445 po_debug.debug_stmt(l_log_head, x_retmsg,'error exists with funds check');
6446 END IF;
6447
6448 RETURN;
6449 END IF;
6450
6451 -- re-initialize distributions list table
6452 l_distribution_id_tbl.delete;
6453
6454 -- Update NEW record in PO_ENCUMBRANCE_GT with the new
6455 -- values
6456 x_retmsg := 'SMRCH003';
6457
6458 /*
6459 *Looping through the distribution records which requires fundscheck, and populating the fundscheck
6460 *input table with the appropriate data.
6461 */
6462
6463 /**/
6464 --need to get newest price quantity and amount
6465
6466
6467
6468 OPEN l_dist_tax_csr(p_group_id);
6469
6470 LOOP
6471 FETCH l_dist_tax_csr INTO
6472 l_req_line_id,
6473 l_req_dist_id,
6474 l_req_hdr_id,
6475 l_new_price,
6476 l_new_quantity;
6477 EXIT WHEN l_dist_tax_csr%notfound;
6478 x_retmsg := 'SMRCH0031:' || l_req_line_id || '*' || l_req_dist_id || '*' || l_req_hdr_id || '*' || l_req_num;
6479
6480 IF g_debug_stmt THEN
6481 po_debug.debug_var(l_log_head, x_retmsg,'l_req_line_id ', l_req_line_id );
6482 po_debug.debug_var(l_log_head, x_retmsg,'l_req_dist_id ', l_req_dist_id );
6483 po_debug.debug_var(l_log_head, x_retmsg,'l_req_hdr_id ', l_req_hdr_id );
6484 po_debug.debug_var(l_log_head, x_retmsg,'l_new_price ', l_new_price );
6485 po_debug.debug_var(l_log_head, x_retmsg, 'l_new_quantity', l_new_quantity);
6486 END IF;
6487
6488 calculate_disttax(1.0, l_cal_disttax_status, l_req_dist_id, l_new_price, l_new_quantity, NULL,
6489 l_rec_tax, l_nonrec_tax);
6490 l_new_tax := l_nonrec_tax;
6491 l_new_amount := l_new_price*l_new_quantity;
6492 IF g_debug_stmt THEN
6493 po_debug.debug_var(l_log_head, x_retmsg, 'l_rec_tax=', l_rec_tax);
6494 po_debug.debug_var(l_log_head, x_retmsg, 'l_nonrec_tax=', l_nonrec_tax);
6495 END IF;
6496
6497 -- update new values in PO_ENCUMBRANCE_GT
6498 UPDATE po_encumbrance_gt
6499 SET
6500 amount_ordered = l_new_amount,
6501 quantity_ordered = l_new_quantity,
6502 price = l_new_price,
6503 nonrecoverable_tax = l_new_tax
6504 WHERE
6505 distribution_id = l_distribution_id AND
6506 adjustment_status = po_document_funds_grp.g_adjustment_status_new;
6507
6508 END LOOP;
6509 CLOSE l_dist_tax_csr;
6510
6511
6512 x_retmsg := 'SMRCH0032';
6513 --Execute PO Funds Check API
6514
6515 po_document_funds_grp.check_adjust(
6516 p_api_version => 1.0,
6517 x_return_status => l_fc_result_status,
6518 p_doc_type => po_document_funds_grp.g_doc_type_REQUISITION,
6519 p_doc_subtype => NULL,
6520 p_override_funds => po_document_funds_grp.g_parameter_USE_PROFILE,
6521 p_use_gl_date => po_document_funds_grp.g_parameter_YES,
6522 p_override_date => sysdate,
6523 p_report_successes => po_document_funds_grp.g_parameter_NO,
6524 x_po_return_code => l_po_return_code,
6525 x_detailed_results => l_fc_out_tbl);
6526
6527 x_retMsg := 'SMRCH004';
6528
6529 IF (g_fnd_debug = 'Y') THEN
6530 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
6531 FND_LOG.string(FND_LOG.level_statement,
6532 g_module_prefix || l_api_name,
6533 'FUNDS CHECK:' || l_fc_result_status ||' PO RETURN CODE:' || l_po_return_code);
6534 END IF;
6535 END IF;
6536
6537 IF (l_fc_result_status = fnd_api.g_ret_sts_unexp_error) THEN
6538 x_errcode := 'FC_ERROR';
6539 x_return_status := fnd_api.g_ret_sts_error;
6540 RETURN;
6541
6542 ELSE
6543 IF g_debug_stmt THEN
6544 po_debug.debug_STmt(l_log_head, x_retmsg, 'after check adjust of funds check');
6545 END IF;
6546
6547
6548 IF (l_po_return_code = po_document_funds_grp.g_return_success) THEN
6549 x_return_status := fnd_api.g_ret_sts_success;
6550
6551 ELSE -- there can be warning/error message for other cases
6552
6553 x_errcode := 'FC_FAIL';
6554 x_return_status := fnd_api.g_ret_sts_error;
6555
6556 -- populate x_errTable (output PLSQL table) with the corresponding
6557 -- funds check error messages.
6558
6559 l_err_line_id_tbl := po_tbl_number();
6560 l_err_line_num_tbl := po_tbl_number();
6561 l_err_dist_id_tbl := po_tbl_number();
6562 l_err_dist_num_tbl := po_tbl_number();
6563 l_err_error_attr_tbl := po_tbl_varchar30();
6564 l_err_msg_count_tbl := po_tbl_number();
6565 l_err_msg_data_tbl := po_tbl_varchar2000();
6566
6567 x_errtable := po_req_change_err_table(
6568 l_err_line_id_tbl,
6569 l_err_line_num_tbl,
6570 l_err_dist_id_tbl,
6571 l_err_dist_num_tbl,
6572 l_err_error_attr_tbl,
6573 l_err_msg_count_tbl,
6574 l_err_msg_data_tbl);
6575
6576
6577 x_errtable.req_line_id.extend(l_fc_out_tbl.row_index.count);
6578 x_errtable.req_line_num.extend(l_fc_out_tbl.row_index.count);
6579 x_errtable.req_dist_id.extend(l_fc_out_tbl.row_index.count);
6580 x_errtable.req_dist_num.extend(l_fc_out_tbl.row_index.count);
6581 x_errtable.msg_count.extend(l_fc_out_tbl.row_index.count);
6582 x_errtable.msg_data.extend(l_fc_out_tbl.row_index.count);
6583 FOR x IN 1..l_fc_out_tbl.row_index.count LOOP
6584
6585 SELECT
6586 prda.distribution_id,
6587 prda.distribution_num,
6588 prda.requisition_line_id,
6589 prla.line_num
6590 INTO
6591 l_fc_req_distr_id,
6592 l_fc_req_distr_num,
6593 l_fc_req_line_id,
6594 l_fc_req_line_num
6595 FROM
6596 po_requisition_lines_all prla,
6597 po_req_distributions_all prda,
6598 po_distributions_all pda
6599 WHERE
6600 pda.po_distribution_id = l_fc_out_tbl.distribution_id(x)
6601 AND pda.req_distribution_id = prda.distribution_id
6602 AND prla.requisition_line_id = prda.requisition_line_id;
6603
6604 x_errtable.req_line_id(x) := l_fc_req_line_id;
6605 x_errtable.req_line_num(x) := l_fc_req_line_num;
6606 x_errtable.req_dist_id(x) := l_fc_req_distr_id;
6607 x_errtable.req_dist_num(x) := l_fc_req_distr_num;
6608 x_errtable.msg_data(x) := l_fc_out_tbl.error_msg(x);
6609
6610 END LOOP;
6611 RETURN;
6612 END IF;
6613 END IF;
6614 END IF;
6615
6616 END IF;
6617 --Funds Check Ends
6618
6619
6620 i := 1;
6621 OPEN l_changes_csr(p_group_id);
6622 LOOP
6623 FETCH l_changes_csr
6624 INTO
6625 l_req_hdr_id,
6626 l_req_line_id,
6627 l_req_dist_id,
6628 l_new_quantity,
6629 --l_new_price,
6630 l_new_date,
6631 l_request_reason;
6632 EXIT WHEN l_changes_csr%notfound;
6633 l_req_change_table(i).document_line_id := l_req_line_id;
6634 l_req_change_table(i).document_distribution_id := l_req_dist_id;
6635 l_req_change_table(i).new_price := l_new_price;
6636 l_req_change_table(i).new_quantity := l_new_quantity;
6637 l_req_change_table(i).new_date := l_new_date;
6638 l_req_change_table(i).request_reason := l_request_reason;
6639
6640 i := i + 1;
6641 END LOOP;
6642 CLOSE l_changes_csr;
6643 x_retmsg := 'SMRCH006';
6644 l_err_line_id_tbl := po_tbl_number();
6645 l_err_line_num_tbl := po_tbl_number();
6646 l_err_dist_id_tbl := po_tbl_number();
6647 l_err_dist_num_tbl := po_tbl_number();
6648 l_err_error_attr_tbl := po_tbl_varchar30();
6649 l_err_msg_count_tbl := po_tbl_number();
6650 l_err_msg_data_tbl := po_tbl_varchar2000();
6651
6652 x_errTable := po_req_change_err_table(
6653 l_err_line_id_tbl,
6654 l_err_line_num_tbl,
6655 l_err_dist_id_tbl,
6656 l_err_dist_num_tbl,
6657 l_err_error_attr_tbl,
6658 l_err_msg_count_tbl,
6659 l_err_msg_data_tbl);
6660
6661 --Final Round of Validations Against Changes
6662 -- if(l_change_exist = 'Y') then
6663 -- Validate_Changes(l_req_hdr_id,l_req_change_table,x_return_status,x_retMsg,x_errTable);
6664 --call OM API for this validation this is not needed
6665 -- end if;
6666 x_retmsg := 'SMRCH007';
6667 --Submit Cancel Requests
6668 IF g_debug_stmt THEN
6669 po_debug.debug_stmt(l_log_head, x_retmsg, 'submitting changes...');
6670 END IF;
6671
6672 IF(l_cancel_exist = 'Y') THEN
6673 submit_ireqcancel(1.0, x_return_status, p_group_id, x_retmsg, x_errTable, 'Y');
6674 END IF;
6675
6676 x_retmsg := 'SMRCH008';
6677 /* i:=x_errTable.req_line_id.count+1;
6678 l_CancelErrorSize:=l_cancel_ErrTable.req_line_id.count;
6679
6680 if(l_CancelErrorSize > 0) then
6681 x_errTable.req_line_id.extend(l_CancelErrorSize);
6682 x_errTable.msg_count.extend(l_CancelErrorSize);
6683 x_errTable.msg_data.extend(l_CancelErrorSize);
6684
6685 for k in 1..l_CancelErrorSize
6686 loop
6687 x_errTable.req_line_id(i):=l_cancel_ErrTable.req_line_id(k);
6688 x_errTable.msg_count(i):=1;
6689 x_errTable.msg_data(i):='CANNOT CANCEL';
6690 i:=i+1;
6691 end loop;
6692 end if;
6693 /*
6694 * If all requests are valid, update status to "NEW" and kick off workflow
6695 */
6696
6697
6698
6699 IF(x_errtable.req_line_id.count = 0) THEN
6700
6701 IF g_debug_stmt THEN
6702 po_debug.debug_stmt(l_log_head, x_retmsg, 'all change requests are valid, updating status to "NEW" and kick off workflow');
6703 END IF;
6704
6705
6706 UPDATE po_change_requests
6707 SET request_status = 'NEW'
6708 WHERE change_request_group_id = p_group_id
6709 AND request_status = 'SYSTEMSAVE';
6710
6711
6712 --Kick Off Workflow
6713 x_retmsg := 'SMRCH009';
6714
6715 po_reqchangerequestwf_pvt.submit_internal_req_change(
6716 p_api_version => 1.0,
6717 p_commit => fnd_api.g_false,
6718 p_req_header_id => l_req_hdr_id,
6719 p_note_to_approver => p_note_to_approver,
6720 p_initiator => p_initiator,
6721 x_return_status => l_wf_status);
6722 ELSE
6723 x_errcode := 'VC_FAIL';
6724 x_return_status := fnd_api.g_ret_sts_error;
6725 END IF;
6726 EXCEPTION WHEN OTHERS THEN
6727 x_return_status := fnd_api.g_ret_sts_unexp_error;
6728 x_retmsg := x_retmsg || ':' || SQLERRM;
6729 IF g_fnd_debug = 'Y' THEN
6730 IF (fnd_log.g_current_runtime_level <= fnd_log.level_unexpected) THEN
6731 fnd_log.string(fnd_log.level_unexpected, g_module_prefix ||
6732 l_api_name || '.others_exception', x_retmsg );
6733
6734 END IF;
6735 END IF;
6736 END SUBMIT_IREQCHANGE;
6737
6738 /* This is called from the UI to check whether the new values are valid
6739 */
6740 procedure validate_internal_req_changes(
6741 p_req_line_id IN NUMBER
6742 , p_req_header_id IN NUMBER
6743 , p_need_by_date IN DATE DEFAULT NULL
6744 , p_old_quantity IN NUMBER DEFAULT 0
6745 , p_new_quantity IN NUMBER DEFAULT 0
6746 , X_return_status OUT NOCOPY VARCHAR2
6747 )
6748 IS
6749
6750 L_msg_data VARCHAR2(2000);
6751 L_msg_count NUMBER;
6752 l_delta_quantity number;
6753 l_log_head CONSTANT VARCHAR2(100) := c_log_head ||'validate_internal_req_changes';
6754 l_progress varchar2(3) := '000';
6755
6756 /*Procedure Call_Process_Order_for_IReq -- Specification definition
6757 ( P_API_Version IN NUMBER
6758 , P_internal_req_line_id IN PO_Requisition_Lines_All.Requisition_Line_id%TYPE
6759 , P_internal_req_header_id IN PO_Requisition_Headers_All.Requisition_Header_id%TYPE
6760 , P_Mode IN VARCHAR2
6761 , P_Cancel_ISO IN BOOLEAN DEFAULT FALSE
6762 , P_Cancel_ISO_lines IN BOOLEAN DEFAULT FALSE
6763 , P_New_Request_Date IN DATE DEFAULT NULL
6764 , P_Delta_Ordered_Qty IN NUMBER DEFAULT 0
6765 , X_msg_count OUT NOCOPY NUMBER
6766 , X_msg_data OUT NOCOPY VARCHAR2
6767 , X_return_status OUT NOCOPY VARCHAR2
6768 );*/
6769
6770 l_orgid number;
6771 l_open_quantity number;
6772 BEGIN
6773
6774 /* Only for the qunatity changes check for the the new ordered quantity is less
6775 than received+open receiving
6776 quantity then the throw error */
6777
6778 IF( p_new_quantity <> 0 ) THEN
6779
6780 BEGIN
6781 SELECT nvl(sum(w.shipped_quantity),0)
6782 INTO l_open_quantity
6783 FROM oe_order_lines_all oel
6784 ,oe_order_headers_all oeh
6785 ,wsh_delivery_details w
6786 ,po_requisition_lines_all pol
6787 WHERE
6788 oel.header_id = oeh.header_id
6789 AND oel.line_id = w.source_line_id
6790 AND w.source_code = 'OE'
6791 AND w.released_status = 'C'
6792 AND oel.source_document_line_id=pol.requisition_line_id
6793 AND oel.source_document_id=pol.requisition_header_id
6794 AND oeh.source_document_type_id =10
6795 AND pol.REQUISITION_LINE_ID=p_req_line_id;
6796 EXCEPTION
6797 WHEN NO_DATA_FOUND THEN
6798 l_open_quantity := 0;
6799 WHEN OTHERS THEN
6800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6801 END;
6802
6803 IF ( p_new_quantity < l_open_quantity ) THEN
6804 RAISE FND_API.G_EXC_ERROR;
6805 END IF;
6806 END IF;
6807 l_delta_quantity := p_new_quantity-p_old_quantity;
6808
6809 IF g_debug_stmt THEN
6810 po_debug.debug_begin(l_log_head);
6811 po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
6812 po_debug.debug_var(l_log_head, l_progress, 'p_req_header_id', p_req_header_id);
6813 po_debug.debug_var(l_log_head, l_progress, 'p_need_by_date', p_need_by_date);
6814 po_debug.debug_var(l_log_head, l_progress, 'p_old_quantity', p_old_quantity);
6815 po_debug.debug_var(l_log_head, l_progress, 'p_new_quantity', p_new_quantity);
6816 po_debug.debug_var(l_log_head, l_progress, 'l_delta_quantity', l_delta_quantity);
6817 END IF;
6818
6819 l_progress :='001';
6820
6821
6822 l_orgid := PO_ReqChangeRequestWF_PVT.get_sales_order_org(p_req_hdr_id => p_req_header_id);
6823
6824 IF l_orgid is NOT NULL THEN
6825 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
6826 END IF;
6827
6828 IF g_debug_stmt THEN
6829 po_debug.debug_stmt(l_log_head, l_progress, 'Calling OM API Validating the changes');
6830 END IF;
6831
6832 OE_Internal_Requisition_Pvt.Call_Process_Order_for_IReq
6833 ( P_API_Version => 1.0
6834 , P_internal_req_line_id => p_req_line_id
6835 , P_internal_req_header_id => p_req_header_id
6836 , P_Mode => 'V' --SIMPLY VALIDATING
6837 , P_New_Request_Date => p_need_by_date
6838 , P_Delta_Ordered_Qty => l_delta_quantity
6839 , X_msg_count => L_msg_count
6840 , X_msg_data => L_msg_data
6841 , X_return_status => X_return_status
6842 );
6843
6844 IF g_debug_stmt THEN
6845 po_debug.debug_stmt(l_log_head, l_progress, 'returning from OM API Validating the changes');
6846 END IF;
6847
6848 l_orgid := PO_ReqChangeRequestWF_PVT.get_requisition_org( p_req_hdr_id => p_req_header_id);
6849
6850 IF l_orgid is NOT NULL THEN
6851 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
6852 END IF;
6853
6854
6855 IF g_debug_stmt THEN
6856 po_debug.debug_stmt(l_log_head, l_progress, 'returning from OM API Validating the changes');
6857 po_debug.debug_var(l_log_head, l_progress, 'l_orgid', l_orgid);
6858 po_debug.debug_var(l_log_head, l_progress, 'L_msg_data', L_msg_data);
6859 po_debug.debug_var(l_log_head, l_progress, 'L_msg_count', L_msg_count);
6860 po_debug.debug_var(l_log_head, l_progress, 'X_return_status', X_return_status);
6861 po_debug.debug_end(l_log_head);
6862
6863 END IF;
6864 exception
6865 when others then
6866 X_return_status:=FND_API.G_RET_STS_ERROR;
6867 IF g_debug_stmt THEN
6868 po_debug.debug_stmt(l_log_head, l_progress, ' Validating the changes in exception'|| sqlerrm);
6869 po_debug.debug_var(l_log_head, l_progress, 'l_orgid', l_orgid);
6870 po_debug.debug_var(l_log_head, l_progress, 'L_msg_data', L_msg_data);
6871 po_debug.debug_var(l_log_head, l_progress, 'L_msg_count', L_msg_count);
6872 po_debug.debug_var(l_log_head, l_progress, 'X_return_status', X_return_status);
6873 po_debug.debug_end(l_log_head);
6874 END IF;
6875 END validate_internal_req_changes;
6876
6877 END po_rco_validation_pvt;