[Home] [Help]
PACKAGE BODY: APPS.PO_RCOTOLERANCE_PVT
Source
1 PACKAGE BODY po_rcotolerance_pvt AS
2 /* $Header: POXVRTWB.pls 120.4.12010000.5 2009/01/08 10:22:25 rojain ship $*/
3 -- Read the profile option that enables/disables the debug log
4
5 g_debug CONSTANT VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'), 'N');
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_RCOTOLERANCE_PVT';
8
9 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.'
10 || g_pkg_name
11 || '.';
12
13 TOL_INT_NEEDBY_IND CONSTANT NUMBER := 3;
14 /**
15 * Public FUNCTION get_new_line_quantity
16 * Requires:
17 * Requisition Header Id
18 * Requisition Line Id
19 * Change Request Group Id
20 * Modifies: None.
21 * Effects: Get revised line quantity from po_req_distributions and po_change_requests.
22 * Returns:
23 * Revised line quantity
24 * If there is an exception returns 0
25 */
26
27 FUNCTION get_new_line_quantity
28 (p_req_id IN NUMBER,
29 p_req_line_id IN NUMBER,
30 p_grp_id IN NUMBER)
31 RETURN NUMBER
32 IS
33 l_new_line_qty NUMBER := 0;
34 BEGIN
35 SELECT nvl(SUM(get_new_distribution_qty(prl.requisition_header_id, p_grp_id, prl.requisition_line_id,
36 prd.distribution_id)),
37 0)
38 INTO l_new_line_qty
39 FROM po_req_distributions prd,
40 po_requisition_lines prl
41 WHERE prl.requisition_header_id = p_req_id
42 AND prl.requisition_line_id = p_req_line_id
43 AND prl.requisition_line_id = prd.requisition_line_id
44 AND nvl(prl.cancel_flag, 'N') = 'N'
45 AND nvl(prl.modified_by_agent_flag, 'N') = 'N';
46
47 RETURN l_new_line_qty;
48 EXCEPTION
49 WHEN OTHERS THEN
50 RETURN 0;
51 END get_new_line_quantity;
52 /**
53 * Public FUNCTION get_new_distribution_qty
54 * Requires: Requisition Header Id
55 * Change Request Group Id
56 * Requisition Line Id
57 * Modifies: None.
58 * Effects: Return updated distribution qty from po_change_requests if any
59 * Else Return the req_line_quantity from po_req_distributions
60 * If line was cancelled return 0
61 * Returns:
62 * Revised distribution quantity
63 * If something fails returns 0
64 */
65
66 FUNCTION get_new_distribution_qty
67 (p_req_id IN NUMBER,
68 p_grp_id IN NUMBER,
69 p_req_line_id IN NUMBER,
70 p_req_dist_id IN NUMBER)
71 RETURN NUMBER
72 IS
73 l_new_qty NUMBER := 0;
74 l_qty_changed NUMBER := 0;
75 BEGIN
76 SELECT COUNT(* )
77 INTO l_qty_changed
78 FROM po_change_requests
79 WHERE document_distribution_id = p_req_dist_id
80 AND document_line_id = p_req_line_id
81 AND document_header_id = p_req_id
82 AND request_status = 'SYSTEMSAVE'
83 AND new_quantity IS NOT NULL ;
84
85 IF (l_qty_changed > 0) THEN
86 SELECT new_quantity
87 INTO l_new_qty
88 FROM po_change_requests
89 WHERE document_distribution_id = p_req_dist_id
90 AND document_line_id = p_req_line_id
91 AND document_header_id = p_req_id
92 AND request_status = 'SYSTEMSAVE';
93 ELSE
94 SELECT req_line_quantity
95 INTO l_new_qty
96 FROM po_req_distributions
97 WHERE distribution_id = p_req_dist_id;
98 END IF;
99
100 RETURN l_new_qty;
101 EXCEPTION
102 WHEN OTHERS THEN
103 RETURN 0;
104 END get_new_distribution_qty;
105 /**
106 * Public FUNCTION change_within_tol_date
107 * Requires: oldValue
108 * newValue
109 * max increment tolerance
110 * max decrement tolerance
111 * Modifies: None.
112 * Effects: Checks whether given old and new values are within given
113 * max increment and max decrement values or not.
114 * Returns:
115 * 'Y' or 'N'
116 */
117
118 FUNCTION change_within_tol_date
119 (p_oldvalue IN DATE,
120 p_newvalue IN DATE,
121 p_maxincrement IN NUMBER,
122 p_maxdecrement IN NUMBER)
123 RETURN VARCHAR2
124 IS
125 l_returnvalue VARCHAR2(1) := 'Y';
126 l_api_name VARCHAR2(30) := 'Change_Within_Tol_Date';
127 BEGIN
128 IF (g_debug = 'Y'
129 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
130 fnd_log.string(fnd_log.level_statement, g_module_prefix
131 || l_api_name, 'Parameters:'
132 ||' OldValue:'
133 || to_char(p_oldvalue, 'DD-MON-YYYY')
134 ||' NewValue:'
135 || to_char(p_newvalue, 'DD-MON-YYYY')
136 ||' MaxIncrement:'
137 || to_char(p_maxincrement)
138 ||' MaxDecrement:'
139 || to_char(p_maxdecrement));
140 END IF;
141
142 IF (p_oldvalue IS NOT NULL
143 AND p_newvalue IS NOT NULL
144 AND p_oldvalue <> p_newvalue) THEN
145 -- check for upper tol
146
147 IF (p_oldvalue < p_newvalue) THEN
148 IF (p_newvalue - p_maxincrement > p_oldvalue) THEN
149 l_returnvalue := 'N';
150 END IF;
151 END IF;
152 -- check for lower tol
153
154 IF (p_oldvalue > p_newvalue) THEN
155 IF (p_newvalue + p_maxdecrement < p_oldvalue) THEN
156 l_returnvalue := 'N';
157 END IF;
158 END IF;
159 END IF;
160
161 IF (g_debug = 'Y'
162 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
163 fnd_log.string(fnd_log.level_statement, g_module_prefix
164 || l_api_name,'Return Value:'
165 || l_returnvalue);
166 END IF;
167
168 RETURN l_returnvalue;
169 END change_within_tol_date;
170 /**
171 * Public FUNCTION change_within_tol_percent
172 * Requires: oldValue
173 * newValue
174 * max increment tolerance (%)
175 * max decrement tolerance (%)
176 * Modifies: None.
177 * Effects: Checks whether given old and new values are within given
178 * max increment and max decrement values or not.
179 * Returns:
180 * 'Y' or 'N'
181 */
182
183 FUNCTION change_within_tol_percent
184 (p_oldvalue IN NUMBER,
185 p_newvalue IN NUMBER,
186 p_maxincrement IN NUMBER,
187 p_maxdecrement IN NUMBER)
188 RETURN VARCHAR2
189 IS
190 l_changepercent NUMBER;
191 l_returnvalue VARCHAR2(1) := 'Y';
192 l_api_name VARCHAR2(30) := 'Change_Within_Tol_Percent';
193 BEGIN
194 IF (g_debug = 'Y'
195 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
196 fnd_log.string(fnd_log.level_statement, g_module_prefix
197 || l_api_name, 'Parameters:'
198 ||' OldValue:'
199 || to_char(p_oldvalue)
200 ||' NewValue:'
201 || to_char(p_newvalue)
202 ||' MaxIncrement:'
203 || to_char(p_maxincrement)
204 ||' MaxDecrement:'
205 || to_char(p_maxdecrement));
206 END IF;
207
208 IF (p_oldvalue IS NOT NULL
209 AND p_newvalue IS NOT NULL
210 AND p_oldvalue <> p_newvalue
211 AND p_oldvalue <> 0) THEN
212 l_changepercent := abs((p_oldvalue - p_newvalue) / p_oldvalue) * 100;
213
214 IF (g_debug = 'Y'
215 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
216 fnd_log.string(fnd_log.level_statement, g_module_prefix
217 || l_api_name, 'ChangePercent:'
218 || to_char(l_changepercent));
219 END IF;
220 -- value has increased
221
222 IF (p_oldvalue < p_newvalue) THEN
223 IF (l_changepercent > p_maxincrement) THEN
224 l_returnvalue := 'N';
225 END IF;
226 END IF;
227 -- value has decreased
228
229 IF (p_oldvalue > p_newvalue) THEN
230 IF (l_changepercent > p_maxdecrement) THEN
231 l_returnvalue := 'N';
232 END IF;
233 END IF;
234 END IF;
235
236 IF (g_debug = 'Y'
237 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
238 fnd_log.string(fnd_log.level_statement, g_module_prefix
239 || l_api_name,'Return Value:'
240 || l_returnvalue);
241 END IF;
242
243 RETURN l_returnvalue;
244 END change_within_tol_percent;
245 /**
246 * Public FUNCTION change_within_tol_amount
247 * Requires: oldValue
248 * newValue
249 * max increment tolerance (functional currency)
250 * max decrement tolerance (functional currency)
251 * Modifies: None.
252 * Effects: Checks whether given old and new values are within given
253 * max increment and max decrement values in amount or not.
254 * Returns:
255 * 'Y' or 'N'
256 */
257
258 FUNCTION change_within_tol_amount
259 (p_oldvalue IN NUMBER,
260 p_newvalue IN NUMBER,
261 p_maxincrement IN NUMBER,
262 p_maxdecrement IN NUMBER)
263 RETURN VARCHAR2
264 IS
265 l_change NUMBER;
266 l_returnvalue VARCHAR2(1) := 'Y';
267 l_api_name VARCHAR2(30) := 'Change_Within_Tol_Amount';
268 BEGIN
269 IF (g_debug = 'Y'
270 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
271 fnd_log.string(fnd_log.level_statement, g_module_prefix
272 || l_api_name, 'Parameters:'
273 ||' OldValue:'
274 || to_char(p_oldvalue)
275 ||' NewValue:'
276 || to_char(p_newvalue)
277 ||' MaxIncrement:'
278 || to_char(p_maxincrement)
279 ||' MaxDecrement:'
280 || to_char(p_maxdecrement));
281 END IF;
282
283 IF (p_oldvalue IS NOT NULL
284 AND p_newvalue IS NOT NULL
285 AND p_oldvalue <> p_newvalue) THEN
286 l_change := p_oldvalue - p_newvalue;
287 -- value has increased
288
289 IF (p_oldvalue < p_newvalue) THEN
290 IF (abs(l_change) > p_maxincrement) THEN
291 l_returnvalue := 'N';
292 END IF;
293 END IF;
294 -- value has decreased
295
296 IF (p_oldvalue > p_newvalue) THEN
297 IF (l_change > p_maxdecrement) THEN
298 l_returnvalue := 'N';
299 END IF;
300 END IF;
301 END IF;
302
303 IF (g_debug = 'Y'
304 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
305 fnd_log.string(fnd_log.level_statement, g_module_prefix
306 || l_api_name,'Return Value:'
307 || l_returnvalue);
308 END IF;
309
310 RETURN l_returnvalue;
311 END change_within_tol_amount;
312 /**
313 * Public FUNCTION changes_within_tol
314 * Requires: oldValue
315 * newValue
316 * max increment tolerance (%)
317 * max decrement tolerance (%)
318 * max increment tolerance (functional currency)
319 * max decrement tolerance (functional currency)
320 * Modifies: None.
321 * Effects: Checks whether given old and new values are within given
322 * max increment and max decrement values in percent and amount or not.
323 * Returns:
324 * 'Y' or 'N'
325 *
326 * The logic to call tolerance check API's (for % and USD) :
327 * - if both tolerances(% and USD) are zero, this should go to approval
328 * - if one has value and the other is zero, validate against the one
329 * with value, and ignore the zero one
330 * - if both values are set up, we should validate against both values,
331 * the change will not be auto-approved if it can't pass both values.
332 */
333
334 FUNCTION changes_within_tol
335 (p_oldvalue IN NUMBER,
336 p_newvalue IN NUMBER,
337 p_maxincrement IN NUMBER,
338 p_maxdecrement IN NUMBER,
339 p_maxincrementamount IN NUMBER,
340 p_maxdecrementamount IN NUMBER)
341 RETURN VARCHAR2
342 IS
343 l_returnvalue VARCHAR2(1) := 'Y';
344 l_api_name VARCHAR2(30) := 'Changes_Within_Tol';
345 l_call_percent_api BOOLEAN := TRUE;
346 l_call_amount_api BOOLEAN := TRUE;
347 BEGIN
348 IF (p_maxincrement = 0
349 AND p_maxdecrement = 0
350 AND p_maxincrementamount <> 0
351 AND p_maxdecrementamount <> 0) THEN
352 l_call_percent_api := FALSE;
353 END IF;
354
355 IF (p_maxincrement <> 0
356 AND p_maxdecrement <> 0
357 AND p_maxincrementamount = 0
358 AND p_maxdecrementamount = 0) THEN
359 l_call_amount_api := FALSE;
360 END IF;
361
362 IF (g_debug = 'Y'
363 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
364 IF (l_call_percent_api) THEN
365 fnd_log.string(fnd_log.level_statement, g_module_prefix
366 || l_api_name,'l_call_percent_api: Y');
367 ELSE
368 fnd_log.string(fnd_log.level_statement, g_module_prefix
369 || l_api_name,'l_call_percent_api: N');
370 END IF;
371
372 IF (l_call_amount_api) THEN
373 fnd_log.string(fnd_log.level_statement, g_module_prefix
374 || l_api_name,'l_call_amount_api: Y');
375 ELSE
376 fnd_log.string(fnd_log.level_statement, g_module_prefix
377 || l_api_name,'l_call_amount_api: N');
378 END IF;
379 END IF;
380
381 IF (l_call_percent_api) THEN
382 l_returnvalue := change_within_tol_percent(p_oldvalue, p_newvalue, p_maxincrement, p_maxdecrement);
383
384 IF (g_debug = 'Y'
385 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
386 fnd_log.string(fnd_log.level_statement, g_module_prefix
387 || l_api_name,'Change_Within_Tol_Percent Return Value:'
388 || l_returnvalue);
389 END IF;
390 END IF;
391
392 IF (l_returnvalue <> 'N'
393 AND l_call_amount_api) THEN
394 l_returnvalue := change_within_tol_amount(p_oldvalue, p_newvalue, p_maxincrementamount,
395 p_maxdecrementamount);
396 END IF;
397
398 IF (g_debug = 'Y'
399 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
400 fnd_log.string(fnd_log.level_statement, g_module_prefix
401 || l_api_name,'Return Value:'
402 || l_returnvalue);
403 END IF;
404
405 RETURN l_returnvalue;
406 END changes_within_tol;
407 /**
408 * Requires: organization id
409 * Modifies: None.
410 * Effects: Calls PO_CO_TOLERANCES_GRP.get_tolerances to return
411 * tolerance values for RCO REQ Aproval tolerances.
412 * Returns:
413 * a table of records that contains tolerance values
414 */
415
416 FUNCTION populate_internal_tolerances
417 (p_organization_id IN NUMBER)
418 RETURN po_co_tolerances_grp.tolerances_tbl_type
419 IS
420 l_tolerances_tbl po_co_tolerances_grp.tolerances_tbl_type;
421 l_return_status VARCHAR2(1);
422 l_msg_count NUMBER;
423 l_msg_data VARCHAR2(100);
424 l_api_name VARCHAR2(30) := 'Populate_INTERNAL_Tolerances';
425 BEGIN
426 IF (g_debug = 'Y'
427 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
428 fnd_log.string(fnd_log.level_statement, g_module_prefix
429 || l_api_name,'p_organization_id :'
430 || p_organization_id);
431 END IF;
432
433
434 po_co_tolerances_grp.get_tolerances(p_api_version => 1.0, p_init_msg_list => fnd_api.g_true,
435 p_organization_id => p_organization_id, p_change_order_type => po_co_tolerances_grp.g_rco_int_req_app,
436 x_tolerances_tbl => l_tolerances_tbl, x_return_status => l_return_status,
437 x_msg_count => l_msg_count, x_msg_data => l_msg_data);
438
439 IF (g_debug = 'Y'
440 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
441 fnd_log.string(fnd_log.level_statement, g_module_prefix
442 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API result:'
443 || l_return_status);
444 END IF;
445
446 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
447 IF (g_debug = 'Y'
448 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
449 fnd_log.string(fnd_log.level_statement, g_module_prefix
450 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API failed:'
451 || l_msg_count
452 ||' '
453 || l_msg_data);
454
455 END IF;
456 END IF;
457
458 RETURN l_tolerances_tbl;
459 END populate_internal_tolerances;
460 /**
461 * Requires: organization id
462 * Modifies: None.
463 * Effects: Calls PO_CO_TOLERANCES_GRP.get_tolerances to return
464 * tolerance values for RCO REQ Aproval tolerances.
465 * Returns:
466 * a table of records that contains tolerance values
467 */
468
469 FUNCTION populate_tolerances
470 (p_organization_id IN NUMBER)
471 RETURN po_co_tolerances_grp.tolerances_tbl_type
472 IS
473 l_tolerances_tbl po_co_tolerances_grp.tolerances_tbl_type;
474 l_return_status VARCHAR2(1);
475 l_msg_count NUMBER;
476 l_msg_data VARCHAR2(100);
477 l_api_name VARCHAR2(30) := 'Populate_Tolerances';
478 BEGIN
479 IF (g_debug = 'Y'
480 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
481 fnd_log.string(fnd_log.level_statement, g_module_prefix
482 || l_api_name,'p_organization_id :'
483 || p_organization_id);
484 END IF;
485
486 po_co_tolerances_grp.get_tolerances(p_api_version => 1.0, p_init_msg_list => fnd_api.g_true,
487 p_organization_id => p_organization_id, p_change_order_type => po_co_tolerances_grp.g_rco_req_app,
488 x_tolerances_tbl => l_tolerances_tbl, x_return_status => l_return_status,
489 x_msg_count => l_msg_count, x_msg_data => l_msg_data);
490
491 IF (g_debug = 'Y'
492 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
493 fnd_log.string(fnd_log.level_statement, g_module_prefix
494 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API result:'
495 || l_return_status);
496 END IF;
497
498 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
499 IF (g_debug = 'Y'
500 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
501 fnd_log.string(fnd_log.level_statement, g_module_prefix
502 || l_api_name,'PO_CO_Tolerances_Grp.get_tolerances API failed:'
503 || l_msg_count
504 ||' '
505 || l_msg_data);
506 END IF;
507 END IF;
508
509 RETURN l_tolerances_tbl;
510 END populate_tolerances;
511 /**
512 * Requires: requisition change request group id
513 * organization id
514 * Modifies: None.
515 * Effects: Calls PO_CO_TOLERANCES_GRP.get_tolerances to return
516 * tolerance values for RCO REQ Aproval tolerances.
517 * Returns:
518 * a table of records that contains tolerance values
519 */
520
521 FUNCTION changes_within_reqappr_tol_val
522 (p_reqgrp_id IN NUMBER,
523 p_reqheader_id IN NUMBER,
524 p_org_id NUMBER,
525 p_source_type_code IN VARCHAR2)
526 RETURN VARCHAR2
527 IS
528 l_return_val VARCHAR2(1) := 'Y';
529 -- PLSQL table of tolerance values
530 l_tolerances_tbl po_co_tolerances_grp.tolerances_tbl_type;
531 l_api_name VARCHAR2(30) := 'Changes_Within_ReqAppr_Tol_Val';
532 l_progress VARCHAR2(100) := '000';
533 BEGIN
534 l_progress := '001';
535
536 IF (g_debug = 'Y'
537 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
538 fnd_log.string(fnd_log.level_statement, g_module_prefix
539 || l_api_name, 'p_reqgrp_id:'
540 || p_reqgrp_id
541 ||' p_reqheader_id:'
542 || p_reqheader_id
543 ||' p_org_id:'
544 || p_org_id);
545 END IF;
546
547 IF (p_reqgrp_id IS NOT NULL ) THEN
548 -- get tolerance values
549
550 IF (nvl(p_source_type_code, '') = 'INVENTORY') THEN
551 l_tolerances_tbl := populate_internal_tolerances(p_org_id);
552 ELSE
553 l_tolerances_tbl := populate_tolerances(p_org_id);
554 END IF;
555
556
557 l_progress := '002';
558
559 IF (g_debug = 'Y'
560 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
561 fnd_log.string(fnd_log.level_statement, g_module_prefix
562 || l_api_name,'Before needbydate, startdate, enddate checks');
563 END IF;
564 -- handle need by date, start date, end date, price checks
565
566 IF (nvl(p_source_type_code, '') = 'INVENTORY') THEN
567 -- handle need by date only
568 IF (g_debug = 'Y'
569 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
570 fnd_log.string(fnd_log.level_statement, g_module_prefix
571 || l_api_name,'for internal req Before need by date checks');
572 END IF;
573 l_progress := '021';
574
575 BEGIN
576 SELECT 'N'
577 INTO l_return_val
578 FROM dual
579 WHERE EXISTS (SELECT 'N'
580 FROM po_change_requests
581 WHERE change_request_group_id = p_reqgrp_id
582 AND action_type = 'MODIFICATION'
583 AND request_status = 'SYSTEMSAVE'
584 AND request_level = 'LINE'
585 AND (change_within_tol_date(old_need_by_date, new_need_by_date, l_tolerances_tbl(TOL_INT_NEEDBY_IND).max_increment,
586 l_tolerances_tbl(TOL_INT_NEEDBY_IND).max_decrement) = 'N')
587 );
588 EXCEPTION
589 WHEN no_data_found THEN
590 l_return_val := 'Y';
591 END;
592
593 IF (g_debug = 'Y'
594 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
595 fnd_log.string(fnd_log.level_statement, g_module_prefix
596 || l_api_name,'return value(needbydate):'
597 || l_return_val);
598 END IF;
599 ELSE
600 BEGIN
601 SELECT 'N'
602 INTO l_return_val
603 FROM dual
604 WHERE EXISTS (SELECT 'N'
605 FROM po_change_requests
606 WHERE change_request_group_id = p_reqgrp_id
607 AND action_type = 'MODIFICATION'
608 AND request_status = 'SYSTEMSAVE'
609 AND request_level = 'LINE'
610 AND ((change_within_tol_date(old_need_by_date, new_need_by_date, l_tolerances_tbl(tol_needby_ind).max_increment,
611 l_tolerances_tbl(tol_needby_ind).max_decrement) = 'N')
612 OR (change_within_tol_date(old_start_date, new_start_date, l_tolerances_tbl(tol_startdate_ind).max_increment,
613 l_tolerances_tbl(tol_startdate_ind).max_decrement) = 'N')
614 OR (change_within_tol_date(old_expiration_date, new_expiration_date, l_tolerances_tbl(tol_enddate_ind).max_increment,
615 l_tolerances_tbl(tol_enddate_ind).max_decrement) = 'N')
616 OR (change_within_tol_percent(old_price, new_price, l_tolerances_tbl(tol_unitprice_ind).max_increment,
617 l_tolerances_tbl(tol_unitprice_ind).max_decrement) = 'N')));
618 EXCEPTION
619 WHEN no_data_found THEN
620 l_return_val := 'Y';
621 END;
622
623 IF (g_debug = 'Y'
624 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
625 fnd_log.string(fnd_log.level_statement, g_module_prefix
626 || l_api_name,'return value(needbydate):'
627 || l_return_val);
628 END IF;
629 END IF;
630 l_progress := '003';
631
632 IF (g_debug = 'Y'
633 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
634 fnd_log.string(fnd_log.level_statement, g_module_prefix
635 || l_api_name,'Before line quantity, line amount checks');
636 END IF;
637 IF (l_return_val <> 'N') THEN
638 -- check for line quantity, line amount tolerances
639 BEGIN
640
641 IF (nvl(p_source_type_code, '') = 'INVENTORY') THEN
642 IF (g_debug = 'Y'
643 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
644 fnd_log.string(fnd_log.level_statement, g_module_prefix
645 || l_api_name,'for internal req Before line quantity, line amount checks');
646 END IF;
647
648 SELECT 'N'
649 INTO l_return_val
650 FROM dual
651 WHERE EXISTS (SELECT 'N' FROM (
652 SELECT PRL.UNIT_PRICE AS UNIT_PRICE, PRL.QUANTITY AS QUANTITY,PCR.NEW_QUANTITY AS NEW_QUANTITY
653 FROM PO_CHANGE_REQUESTS PCR,
654 PO_REQUISITION_LINES_ALL PRL
655 WHERE PCR.CHANGE_REQUEST_GROUP_ID = p_reqgrp_id
656 AND PCR.ACTION_TYPE = 'MODIFICATION'
657 AND PCR.REQUEST_STATUS NOT IN ('ACCEPTED',
658 'REJECTED')
659 AND PCR.REQUEST_LEVEL ='LINE'
660 AND PCR.INITIATOR = 'REQUESTER'
661 AND PCR.DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID
662 AND PCR.NEW_quantity IS NOT NULL) b
663
664 WHERE (((CHANGES_WITHIN_TOL( b.UNIT_PRICE * b.QUANTITY,
665 NVL(b.NEW_QUANTITY,b.QUANTITY) * b.UNIT_PRICE,
666 l_tolerances_tbl(tol_lineamt_ind).max_increment,
667 l_tolerances_tbl(tol_lineamt_ind).max_decrement,
668 l_tolerances_tbl(tol_lineamt_amt_ind).max_increment,
669 l_tolerances_tbl(tol_lineamt_amt_ind).max_decrement))= 'N')
670 OR (CHANGE_WITHIN_TOL_PERCENT( b.QUANTITY, b.NEW_QUANTITY,
671 l_tolerances_tbl(tol_lineqty_ind).max_increment,
672 l_tolerances_tbl(tol_lineqty_ind).max_decrement) = 'N')));
673
674
675 ELSE
676
677 SELECT 'N'
678 INTO l_return_val
679 FROM dual
680 WHERE EXISTS (SELECT 'N'
681 FROM po_change_requests pcr,
682 po_change_requests pcr1,
683 po_requisition_lines_all prl,
684 po_req_distributions_all prd
685 WHERE prd.requisition_line_id = prl.requisition_line_id
686 AND pcr.change_request_group_id = p_reqgrp_id
687 AND pcr.action_type (+ ) = 'MODIFICATION'
688 AND pcr.request_status (+ ) NOT IN ('ACCEPTED',
689 'REJECTED')
690 AND pcr.request_level (+ ) = 'DISTRIBUTION'
691 AND pcr.initiator (+ ) = 'REQUESTER'
692 AND pcr.document_distribution_id (+ ) = prd.distribution_id
693 -- AND pcr.document_line_id = pcr1.document_line_id
694 AND pcr1.change_request_group_id (+ ) = p_reqgrp_id
695 AND pcr1.document_line_id (+ ) = prl.requisition_line_id
696 AND pcr1.action_type (+ ) = 'MODIFICATION'
697 AND pcr1.request_status (+ ) NOT IN ('ACCEPTED',
698 'REJECTED')
699 AND pcr1.request_level (+ ) = 'LINE'
700 AND pcr1.initiator (+ ) = 'REQUESTER'
701 AND pcr1.new_price (+ ) IS NOT NULL
702
703 GROUP BY pcr.document_line_id
704 HAVING ((changes_within_tol(SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,
705 prl.unit_price * prd.req_line_quantity)),
706 SUM(decode(prl.matching_basis, 'AMOUNT', nvl(pcr.new_amount, prd.req_line_amount),
707 nvl(pcr.new_quantity, prd.req_line_quantity) * nvl(pcr1.new_price, prl.unit_price))),
708 l_tolerances_tbl(tol_lineamt_ind).max_increment,
709 l_tolerances_tbl(tol_lineamt_ind).max_decrement,
710 l_tolerances_tbl(tol_lineamt_amt_ind).max_increment,
711 l_tolerances_tbl(tol_lineamt_amt_ind).max_decrement) = 'N')
712 OR (change_within_tol_percent(SUM(prd.req_line_quantity), SUM(nvl(pcr.new_quantity, prd.req_line_quantity)),
713 l_tolerances_tbl(tol_lineqty_ind).max_increment,
714 l_tolerances_tbl(tol_lineqty_ind).max_decrement) = 'N')));
715 END IF;
716 EXCEPTION
717 WHEN no_data_found THEN
718 l_return_val := 'Y';
719 END;
720 END IF;
721
722 IF (g_debug = 'Y'
723 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
724 fnd_log.string(fnd_log.level_statement, g_module_prefix
725 || l_api_name,'return value(line amount):'
726 || l_return_val);
727 END IF;
728
729 l_progress := '004';
730
731 IF (g_debug = 'Y'
732 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
733 fnd_log.string(fnd_log.level_statement, g_module_prefix
734 || l_api_name,'Before document total checks');
735 END IF;
736
737 IF (l_return_val <> 'N') THEN
738 BEGIN
739 -- check for document total tolerances (funccur and percent)
740 SELECT changes_within_tol(SUM(decode(prl.matching_basis, 'AMOUNT', prd.req_line_amount,
741 prl.unit_price * (prd.req_line_quantity))),
742 SUM(decode(prl.matching_basis, 'AMOUNT', nvl(pcr.new_amount, prd.req_line_amount),
743 nvl(pcr.new_quantity, prd.req_line_quantity) * nvl(pcr1.new_price, prl.unit_price))),
744 l_tolerances_tbl(tol_reqtotal_ind).max_increment,
745 l_tolerances_tbl(tol_reqtotal_ind).max_decrement,
746 l_tolerances_tbl(tol_reqtotal_amt_ind).max_increment,
747 l_tolerances_tbl(tol_reqtotal_amt_ind).max_decrement)
748 INTO l_return_val
749 FROM po_change_requests pcr,
750 po_change_requests pcr1,
751 po_requisition_lines_all prl,
752 po_req_distributions_all prd
753 WHERE prl.requisition_line_id = prd.requisition_line_id
754 AND pcr.change_request_group_id (+ ) = p_reqgrp_id
755 AND pcr.action_type (+ ) = 'MODIFICATION'
756 AND pcr.request_status (+ ) NOT IN ('ACCEPTED',
757 'REJECTED')
758 AND pcr.request_level (+ ) = 'DISTRIBUTION'
759 AND pcr.initiator (+ ) = 'REQUESTER'
760 AND pcr.document_distribution_id (+ ) = prd.distribution_id
761 -- AND pcr.document_line_id = pcr1.document_line_id
762 AND pcr1.change_request_group_id (+ ) = p_reqgrp_id
763 AND prl.requisition_header_id = p_reqheader_id
764 AND pcr1.document_line_id (+ ) = prl.requisition_line_id
765 AND pcr1.action_type (+ ) = 'MODIFICATION'
766 AND pcr1.request_status (+ ) NOT IN ('ACCEPTED',
767 'REJECTED')
768 AND pcr1.request_level (+ ) = 'LINE'
769 AND pcr1.initiator (+ ) = 'REQUESTER'
770 AND pcr1.new_price (+ ) IS NOT NULL ;
771 EXCEPTION
772 WHEN no_data_found THEN
773 l_return_val := 'Y';
774 END;
775 END IF;
776 END IF;
777
778 IF (g_debug = 'Y'
779 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
780 fnd_log.string(fnd_log.level_statement, g_module_prefix
781 || l_api_name,'Return Value:'
782 || l_return_val);
783 END IF;
784
785 RETURN l_return_val;
786 EXCEPTION
787 WHEN OTHERS THEN
788 IF (g_debug = 'Y'
789 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
790 fnd_log.string(fnd_log.level_statement, g_module_prefix
791 || l_api_name, 'Exception:'
792 || SQLERRM
793 ||' PROGRESS:'
794 || l_progress);
795 END IF;
796
797 RETURN 'N';
798 END changes_within_reqappr_tol_val;
799 /**
800 * Public PROCEDURE set_approval_required_flag
801 * Requires: Change Request Group Id
802 * Modifies: Updates po_change_request with the result of the
803 * changes_within_reqapproval_tol_values() API
804 * Returns:
805 * approval_required_flag:Y if user cannot auto approve
806 * :N if he/she can auto approve
807 */
808
809 PROCEDURE set_approval_required_flag
810 (p_chreqgrp_id IN NUMBER,
811 x_appr_status OUT NOCOPY VARCHAR2,
812 p_source_type_code IN VARCHAR2 DEFAULT NULL)
813 IS
814 l_skip_std_logic VARCHAR2(1) := 'N';
815 l_org_id NUMBER;
816 l_reqheader_id NUMBER;
817 l_api_name VARCHAR2(30) := 'Set_Approval_Required_Flag';
818 BEGIN
819 x_appr_status := 'Y';
820
821 IF (g_debug = 'Y'
822 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
823 fnd_log.string(fnd_log.level_statement, g_module_prefix
824 || l_api_name, 'p_chreqgrp_id:'
825 || p_chreqgrp_id);
826 END IF;
827
828 -- call custom procedure first
829
830 por_custom_pkg.custom_rco_reqappr_tol_check(p_chreqgrp_id, x_appr_status, l_skip_std_logic);
831
832 IF (g_debug = 'Y'
833 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
834 fnd_log.string(fnd_log.level_statement, g_module_prefix
835 || l_api_name,'After Calling POR_CUSTOM_PKG.CUSTOM_RCO_REQAPPR_TOL_CHECK '
836 ||' x_appr_status:'
837 || x_appr_status
838 ||' l_skip_std_logic:'
839 || l_skip_std_logic);
840 END IF;
841
842 IF (l_skip_std_logic = 'N'
843 AND x_appr_status <> 'N') THEN
844
845 SELECT org_id,
846 requisition_header_id
847 INTO l_org_id,
848 l_reqheader_id
849 FROM po_requisition_headers prh,
850 po_change_requests pcr
851 WHERE pcr.change_request_group_id = p_chreqgrp_id
852 AND pcr.document_header_id = prh.requisition_header_id
853 AND ROWNUM = 1;
854
855 IF (changes_within_reqappr_tol_val(p_chreqgrp_id, l_reqheader_id, l_org_id, p_source_type_code) = 'Y') THEN
856
857 x_appr_status := 'N';
858 END IF;
859 END IF;
860
861 IF (g_debug = 'Y'
862 AND fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
863 fnd_log.string(fnd_log.level_statement, g_module_prefix
864 || l_api_name, 'x_appr_status:'
865 || x_appr_status);
866 END IF;
867
868
869 UPDATE po_change_requests
870 SET approval_required_flag = x_appr_status
871 WHERE change_request_group_id = p_chreqgrp_id;
872 END set_approval_required_flag;
873 END po_rcotolerance_pvt;