DBA Data[Home] [Help]

PACKAGE BODY: APPS.POS_SCO_TOLERANCE_PVT

Source


1 PACKAGE BODY POS_SCO_TOLERANCE_PVT AS
2 /* $Header: POSPTOLB.pls 120.59.12020000.4 2013/02/09 13:16:30 hvutukur ship $ */
3 
4   G_PKG_NAME  CONSTANT    VARCHAR2(30) := 'POS_SCO_TOLERANCE_PVT';
5   G_FILE_NAME CONSTANT    VARCHAR2(30) := 'POSPTOLB.pls';
6   g_module_prefix CONSTANT VARCHAR2(100) := 'pos.plsql.' || 'POS_SCO_TOLERANCE_PVT'  || '.';
7    -- Read the profile option that enables/disables the debug log
8   g_fnd_debug VARCHAR2(1)   := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9   g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
10 
11 PROCEDURE LOG_MESSAGE( p_proc_name IN VARCHAR2,
12                        p_text      IN VARCHAR2,
13                        p_log_data  IN VARCHAR2)
14 
15 IS
16 BEGIN
17 
18   IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
19     FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
20                    'POS_SCO_TOLERANCE_PVT',
21                     p_proc_name || ': '
22                     || p_text || ': '
23                     || p_log_data);
24   END IF;
25 END LOG_MESSAGE;
26 
27 /* This procedure call the ip API PO_CO_TOLERANCES_GRP.GET_TOLERANCES and sets the Tolerance
28    Attributes and Routing Attributes.
29 */
30 PROCEDURE INITIALIZE_TOL_VALUES(      itemtype        IN  VARCHAR2,
31   	                              itemkey         IN  VARCHAR2,
32   	                              actid           IN  NUMBER,
33   	                              funcmode        IN  VARCHAR2,
34                                       resultout       OUT NOCOPY VARCHAR2)
35 IS
36 
37 CURSOR getDocType(p_change_request_grp_id_csr IN NUMBER) IS
38 SELECT DISTINCT document_type
39 FROM   po_change_requests
40 WHERE  change_request_group_id = p_change_request_grp_id_csr;
41 
42 CURSOR getDocSubType(p_po_header_id_csr IN NUMBER) IS
43 SELECT type_lookup_code
44 FROM   po_headers_all poha
45 WHERE  poha.po_header_id = p_po_header_id_csr;
46 
47 CURSOR getDocSubTypeRel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) IS
48 SELECT distinct(release_type)
49 FROM   po_releases_all pora
50 WHERE  pora.po_header_id = p_po_header_id_csr
51        AND pora.po_release_id = p_po_release_id_csr;
52 
53 CURSOR getOrgId(p_po_header_id_csr IN NUMBER) IS
54 SELECT org_id
55 FROM po_headers_all
56 WHERE po_header_id = p_po_header_id_csr;
57 
58 l_chg_req_grp_id po_change_requests.change_request_group_id%TYPE;
59 l_po_header_id   po_headers_all.po_header_id%TYPE;
60 l_po_release_id  po_releases_all.po_release_id%TYPE;
61 l_doc_type       po_change_requests.document_type%TYPE;
62 l_po_style       varchar2(10);
63 l_doc_subtype    varchar2(10);
64 l_promise_date_incr           NUMBER;
65 l_promise_date_decr           NUMBER;
66 l_unit_price_incr             NUMBER;
67 l_unit_price_decr             NUMBER;
68 l_shipment_qty_incr           NUMBER;
69 l_shipment_qty_decr           NUMBER;
70 l_pay_item_qty_incr           NUMBER;
71 l_pay_item_qty_decr           NUMBER;
72 l_doc_amount_incr_val         NUMBER;
73 l_doc_amount_decr_val         NUMBER;
74 l_doc_amount_incr_per         NUMBER;
75 l_doc_amount_decr_per         NUMBER;
76 l_line_amount_incr_per        NUMBER;
77 l_line_amount_decr_per        NUMBER;
78 l_line_amount_incr_val        NUMBER;
79 l_line_amount_decr_val        NUMBER;
80 l_ship_amount_incr_val        NUMBER;
81 l_ship_amount_decr_val        NUMBER;
82 l_ship_amount_incr_per        NUMBER;
83 l_ship_amount_decr_per        NUMBER;
84 l_pay_item_amount_incr_per    NUMBER;
85 l_pay_item_amount_decr_per    NUMBER;
86 l_pay_item_amount_incr_val    NUMBER;
87 l_pay_item_amount_decr_val    NUMBER;
88 l_prm_date_approval_flag      VARCHAR2(10);
89 l_ship_qty_approval_flag      VARCHAR2(10);
90 l_price_approval_flag         VARCHAR2(10);
91 l_complex_po_style            VARCHAR2(10);
92 l_org_id                      NUMBER;
93 x_tol_tab PO_CO_TOLERANCES_GRP.tolerances_tbl_type;
94 x_return_status varchar2(1);
95 x_msg_count NUMBER;
96 x_msg_data VARCHAR2(2000);
97 x_progress VARCHAR2(1000);
98 BEGIN
99 
100   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
101       FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
102                      g_module_prefix,
103                      'Enter Initialize Tol Proc'
104                      );
105   END IF;
106 
107     x_progress := 'INIT_TOL_VALUES:000';
108 
109     l_chg_req_grp_id :=   wf_engine.GetItemAttrNumber (  itemtype => itemtype,
110             			                         itemkey  => itemkey,
111          		                                 aname    => 'CHANGE_REQUEST_GROUP_ID');
112 
113     l_po_header_id   :=   wf_engine.GetItemAttrNumber (  itemtype => itemtype,
114       						         itemkey  => itemkey,
115       		                                         aname    => 'PO_HEADER_ID');
116 
117     l_po_release_id  :=   wf_engine.GetItemAttrNumber (  itemtype => itemtype,
118                                                          itemkey  => itemkey,
119                                                          aname    => 'PO_RELEASE_ID');
120 
121     x_progress := 'INIT_TOL_VALUES:001';
122 
123     -- Getting the Doc types and subtypes coz the flow is valid for Standard PO's and Blanket Releases
124 
125     OPEN  getDocType(l_chg_req_grp_id);
126     LOOP
127     FETCH getDocType
128     INTO  l_doc_type;
129     EXIT WHEN getDocType%NOTFOUND;
130     END LOOP;
131 
132     IF getDocType%ISOPEN THEN
133        CLOSE getDocType;
134     END IF;
135 
136    IF(l_doc_type = 'PO') THEN
137     OPEN getDocSubType(l_po_header_id);
138     LOOP
139     FETCH getDocSubType
140     INTO l_doc_subtype;
141     EXIT WHEN getDocSubType%NOTFOUND;
142     END LOOP;
143 
144     IF getDocSubType%ISOPEN THEN
145        CLOSE getDocSubType;
146     END IF;
147 
148    ELSIF(l_doc_type = 'RELEASE') THEN
149     OPEN getDocSubTypeRel(l_po_header_id,l_po_release_id);
150     LOOP
151     FETCH getDocSubTypeRel
152     INTO l_doc_subtype;
153     EXIT WHEN getDocSubTypeRel%NOTFOUND;
154     END LOOP;
155 
156     IF getDocSubTypeRel%ISOPEN THEN
157        CLOSE getDocSubTypeRel;
158     END IF;
159    END IF;
160 
161 
162 
163     -- get the org id and set the item attribute value
164     OPEN getOrgId(l_po_header_id);
165     LOOP
166     FETCH getOrgId
167     INTO l_org_id;
168     EXIT WHEN getOrgId%NOTFOUND;
169     END LOOP;
170 
171     IF getOrgId%ISOPEN THEN
172        CLOSE getOrgId;
173     END IF;
174 
175 
176     x_progress := 'INIT_TOL_VALUES:002';
177 
178 
179 
180     wf_engine.SetItemAttrText (itemtype => itemtype,
181              		       itemkey  => itemkey,
182           		       aname    => 'DOCUMENT_TYPE',
183       		               avalue   =>  l_doc_type);
184 
185     wf_engine.SetItemAttrText (itemtype => itemtype,
186                                itemkey  => itemkey,
187                                aname    => 'DOC_SUB_TYPE',
188                                avalue   =>  l_doc_subtype);
189 
190    log_message('INITIALIZE_TOL_VALUES','Operating Unit',l_org_id);
191 
192    x_progress := 'INIT_TOL_VALUES:003: Call get_tolerances';
193 
194 
195    PO_CO_TOLERANCES_GRP.GET_TOLERANCES (1.0,
196 			                FND_API.G_TRUE,
197 			                l_org_id,
198 			                PO_CO_TOLERANCES_GRP.G_SUPP_CHG_APP,
199 			                x_tol_tab,
200 			                x_return_status,
201 			                x_msg_count,
202                                         x_msg_data);
203 
204    IF x_return_status IS NOT NULL AND  x_return_status = FND_API.g_ret_sts_success THEN
205      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
206         FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
207                        g_module_prefix,
208                        x_progress
209                        || 'x_return_status=' || x_return_status);
210      END IF;
211 
212    ELSE
213      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
214        FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
215                        g_module_prefix,
216                        x_progress
217                        ||'x_return_status = ' || x_return_status
218                        ||'x_msg_count = ' || x_msg_count
219                        ||'x_msg_data = ' || x_msg_data);
220      END IF;
221    END IF;
222 
223 
224   x_progress := 'INIT_TOL_VALUES:004';
225 
226    -- loop through all the tolerances retrieved
227   FOR i in 1..x_tol_tab.count
228   LOOP
229    IF (x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PROMISED_DATE) THEN
230        l_promise_date_incr := x_tol_tab(i).max_increment;
231        l_promise_date_decr := x_tol_tab(i).max_decrement;
232 
233    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_UNIT_PRICE) THEN
234        l_unit_price_incr := x_tol_tab(i).max_increment;
235        l_unit_price_decr := x_tol_tab(i).max_decrement;
236 
237    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_QTY) THEN
238        l_shipment_qty_incr := x_tol_tab(i).max_increment;
239        l_shipment_qty_decr := x_tol_tab(i).max_decrement;
240 
241    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_QTY) THEN
242        l_pay_item_qty_incr := x_tol_tab(i).max_increment;
243        l_pay_item_qty_decr := x_tol_tab(i).max_decrement;
244 
245    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_DOCUMENT_AMOUNT_VALUE) THEN
246        l_doc_amount_incr_val := x_tol_tab(i).max_increment;
247        l_doc_amount_decr_val := x_tol_tab(i).max_decrement;
248 
249    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_DOCUMENT_AMOUNT_PERCENT) THEN
250        l_doc_amount_incr_per := x_tol_tab(i).max_increment;
251        l_doc_amount_decr_per := x_tol_tab(i).max_decrement;
252 
253    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_AMOUNT_PERCENT) THEN
254        l_line_amount_incr_per := x_tol_tab(i).max_increment;
255        l_line_amount_decr_per := x_tol_tab(i).max_decrement;
256 
257    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_LINE_AMOUNT_VALUE) THEN
258        l_line_amount_incr_val := x_tol_tab(i).max_increment;
259        l_line_amount_decr_val := x_tol_tab(i).max_decrement;
260 
261    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_AMOUNT_VALUE) THEN
262        l_ship_amount_incr_val := x_tol_tab(i).max_increment;
263        l_ship_amount_decr_val := x_tol_tab(i).max_decrement;
264 
265    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_AMOUNT_VALUE) THEN
266        l_pay_item_amount_incr_val := x_tol_tab(i).max_increment;
267        l_pay_item_amount_decr_val := x_tol_tab(i).max_decrement;
268 
269    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_AMOUNT_PERCENT) THEN
270        l_ship_amount_incr_per := x_tol_tab(i).max_increment;
271        l_ship_amount_decr_per := x_tol_tab(i).max_decrement;
272 
273    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PAY_ITEM_AMOUNT_PERCENT) THEN
274        l_pay_item_amount_incr_per := x_tol_tab(i).max_increment;
275        l_pay_item_amount_decr_per := x_tol_tab(i).max_decrement;
276 
277 
278    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PROMISED_DATE_APPROVAL_FLAG) THEN
279        l_prm_date_approval_flag := x_tol_tab(i).enabled_flag;
280 
281    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_SHIPMENT_QTY_APPROVAL_FLAG) THEN
282        l_ship_qty_approval_flag := x_tol_tab(i).enabled_flag;
283 
284    ELSIF(x_tol_tab(i).tolerance_name = PO_CO_TOLERANCES_GRP.G_PRICE_APPROVAL_FLAG) THEN
285        l_price_approval_flag := x_tol_tab(i).enabled_flag;
286 
287   END IF;
288   END LOOP;
289 
290 
291   x_progress := 'INIT_TOL_VALUES:005';
292 
293 
294     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
295          			     itemkey  => itemkey,
296       		                     aname    => 'PROMISE_DATE_INCR',
297       		                     avalue   => l_promise_date_incr);
298 
299     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
300           			     itemkey  => itemkey,
301       		                     aname    => 'PROMISE_DATE_DEC',
302       		                     avalue   => l_promise_date_decr);
303 
304     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
305          			     itemkey  => itemkey,
306       		                     aname    => 'UNIT_PRICE_INCR',
307       		                     avalue   => l_unit_price_incr);
308 
309     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
310          			     itemkey  => itemkey,
311       		                     aname    => 'UNIT_PRICE_DEC',
312       		                     avalue   => l_unit_price_decr);
313 
314     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
315          			     itemkey  => itemkey,
316       		                     aname    => 'DOC_AMOUNT_INCR_PER',
317       		                     avalue   => l_doc_amount_incr_per);
318 
319     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
320          			     itemkey  => itemkey,
321       		                     aname    => 'DOC_AMOUNT_DEC_PER',
322       		                     avalue   => l_doc_amount_decr_per);
323 
324     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
325          			     itemkey  => itemkey,
326       		                     aname    => 'DOC_AMOUNT_INCR_VAL',
327       		                     avalue   => l_doc_amount_incr_val);
328 
329     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
330          			     itemkey  => itemkey,
331       		                     aname    => 'DOC_AMOUNT_DEC_VAL',
332       		                     avalue   => l_doc_amount_decr_val);
333 
334     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
335          			     itemkey  => itemkey,
336       		                     aname    => 'LINE_AMOUNT_INCR_PER',
337       		                     avalue   => l_line_amount_incr_per);
338 
339     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
340          			     itemkey  => itemkey,
341       		                     aname    => 'LINE_AMOUNT_DEC_PER',
342       		                     avalue   => l_line_amount_decr_per);
343 
344     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
345          			     itemkey  => itemkey,
346       		                     aname    => 'LINE_AMOUNT_INCR_VAL',
347       		                     avalue   => l_line_amount_incr_val);
348 
349     wf_engine.SetItemAttrNumber (    itemtype => itemtype,
350          			     itemkey  => itemkey,
351       		                     aname    => 'LINE_AMOUNT_DEC_VAL',
352       		                     avalue   => l_line_amount_decr_val);
353 
354     wf_engine.SetItemAttrText  (itemtype => itemtype,
355                                 itemkey  => itemkey,
356                                 aname    => 'PROMISE_DATE_APP_FLAG',
357                                 avalue   => l_prm_date_approval_flag);
358 
359     wf_engine.SetItemAttrText  (itemtype => itemtype,
360                                 itemkey  => itemkey,
361                                 aname    => 'SHIP_QTY_APPROVAL_FLAG',
362                                 avalue   => l_ship_qty_approval_flag);
363 
364     wf_engine.SetItemAttrText  (itemtype => itemtype,
365                                 itemkey  => itemkey,
366                                 aname    => 'PRICE_APPROVAL_FLAG',
367                                 avalue   => l_price_approval_flag);
368 
369 
370     -- Get the PO Style ( COMPLEX or NORMAL and accordingly populating the Tolerance Attributes
371     l_po_style :=   wf_engine.GetItemAttrText  ( itemtype => itemtype,
372                                                  itemkey  => itemkey,
373                                                  aname    => 'PO_STYLE_TYPE');
374 
375     log_message('INITIALIZE_TOL_VALUES','PO Style Type',l_po_style);
376 
377 
378     IF (l_po_style ='COMPLEX') THEN
379 
380        wf_engine.SetItemAttrNumber (        itemtype => itemtype,
381              			            itemkey  => itemkey,
382           		                    aname    => 'PAY_QUANTITY_INCR',
383           		                    avalue   => l_pay_item_qty_incr);
384 
385         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
386              			             itemkey  => itemkey,
387           		                     aname    => 'PAY_QUANTITY_DEC',
388           		                     avalue   => l_pay_item_qty_decr);
389 
390         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
391              			             itemkey  => itemkey,
392           		                     aname    => 'PAY_AMOUNT_INCR_PER',
393           		                     avalue   => l_pay_item_amount_incr_per);
394 
395         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
396              			             itemkey  => itemkey,
397           		                     aname    => 'PAY_AMOUNT_DEC_PER',
398           		                     avalue   => l_pay_item_amount_decr_per);
399 
400         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
401              			             itemkey  => itemkey,
402           		                     aname    => 'PAY_AMOUNT_INCR_VAL',
403           		                     avalue   => l_pay_item_amount_incr_val);
404 
405         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
406              			             itemkey  => itemkey,
407           		                     aname    => 'PAY_AMOUNT_DEC_VAL',
408           		                     avalue   => l_pay_item_amount_decr_val);
409 
410        IF (PO_COMPLEX_WORK_PVT.is_financing_po(l_po_header_id)) THEN
411           l_complex_po_style := 'FINANCING';
412 
413           wf_engine.SetItemAttrText  (itemtype => itemtype,
414                                       itemkey  => itemkey,
415                                       aname    => 'COMPLEX_PO_STYLE',
416                                       avalue   => l_complex_po_style);
417        ELSE
418           l_complex_po_style := 'ACTUALS';
419 
420 	  wf_engine.SetItemAttrText  (itemtype => itemtype,
421                                       itemkey  => itemkey,
422                                       aname    => 'COMPLEX_PO_STYLE',
423                                       avalue   => l_complex_po_style);
424        END IF;
425 
426 
427 
428     ELSIF (l_po_style ='NORMAL') THEN
429 
430         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
431              			             itemkey  => itemkey,
432           		                     aname    => 'SHIP_QUANTITY_INCR',
433           		                     avalue   => l_shipment_qty_incr);
434 
435         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
436              			             itemkey  => itemkey,
437           		                     aname    => 'SHIP_QUANTITY_DEC',
438           		                     avalue   => l_shipment_qty_decr);
439 
440         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
441              			             itemkey  => itemkey,
442           		                     aname    => 'SHIP_AMOUNT_INCR_PER',
443           		                     avalue   => l_ship_amount_incr_per);
444 
445         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
446              			             itemkey  => itemkey,
447           		                     aname    => 'SHIP_AMOUNT_DEC_PER',
448           		                     avalue   => l_ship_amount_decr_per);
449 
450         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
451              			             itemkey  => itemkey,
452           		                     aname    => 'SHIP_AMOUNT_INCR_VAL',
453           		                     avalue   => l_ship_amount_incr_val);
454 
455         wf_engine.SetItemAttrNumber (        itemtype => itemtype,
456              			             itemkey  => itemkey,
457           		                     aname    => 'SHIP_AMOUNT_DEC_VAL',
458           		                     avalue   => l_ship_amount_decr_val);
459 
460 
461 
462 
463     END IF;
464 
465 EXCEPTION
466   WHEN OTHERS THEN
467        IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
468           FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
469                          g_module_prefix,
470                          x_progress || ':unexpected error' || Sqlerrm);
471         END IF;
472  wf_core.context('POSCHORD', 'INITIALIZE_TOL_VALUES', itemtype, itemkey, to_char(actid),funcmode);
473  raise;
474 
475 END INITIALIZE_TOL_VALUES;
476 
477 
478 /* This procedure checks whether Promise_Date_Change is within the tolerance or not
479    Returns 'Y' if within the tolerance
480    Returns 'N' if out of tolerance
481 */
482 
483 PROCEDURE PROMISE_DATE_WITHIN_TOL(       itemtype        IN VARCHAR2,
484  	                                 itemkey         IN VARCHAR2,
485  	                                 actid           IN NUMBER,
486  	                                 funcmode        IN VARCHAR2,
487                                          resultout       OUT NOCOPY VARCHAR2)
488 
489 IS
490 
491  -- Cursor to pick up old_promised_date and new_promised_date, handles the case when either of them is null
492   CURSOR c_promise_date_changes(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) IS
493          SELECT nvl(pcr.old_promised_date,pcr.old_need_by_date) old_promise_date,
494                 pcr.new_promised_date, pll.promised_date,pll.need_by_date
495          FROM   po_change_requests pcr,
496 	        po_line_locations_all pll
497          WHERE  pcr.document_header_id=p_po_header_id_csr
498 	        AND pcr.document_line_location_id = pll.line_location_id
499                 AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
500                 AND pcr.request_level = 'SHIPMENT'
501                 AND pcr.action_type = 'MODIFICATION'
502                 AND pcr.request_status = 'PENDING'
503                 AND pcr.initiator='SUPPLIER'
504                 AND ( (pcr.new_promised_date <> old_promised_date)  OR
505 	              (nvl(pcr.old_promised_date,nvl(pcr.old_need_by_date,pcr.new_promised_date - 1))<>pcr.new_promised_date)
506                     );
507 
508   l_po_header_id     po_headers_all.po_header_id%type;
509   l_change_group_id  po_change_requests.change_request_group_id%type;
510   l_prom_date_dec    NUMBER;
511   l_prom_date_incr   NUMBER;
512   l_old_promise_date po_change_requests.old_promised_date%type;
513   l_new_promise_date po_change_requests.new_promised_date%type;
514   l_promised_date    po_line_locations_all.promised_date%type;
515   l_need_by_date     po_line_locations_all.need_by_date%type;
516   x_progress         VARCHAR2(1000);
517   l_return_val       VARCHAR2(1):='Y';
518   l_po_style_type    VARCHAR2(10);
519   l_doc_type         VARCHAR2(10);
520 
521 BEGIN
522 
523   IF ( funcmode = 'RUN' ) THEN
524 
525             x_progress := 'PROMISE_DATE_WITHIN_TOL:000';
526 
527       	    l_change_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
528       						              itemkey  => itemkey,
529       		                                              aname    => 'CHANGE_REQUEST_GROUP_ID');
530 
531             l_po_header_id    := wf_engine.GetItemAttrNumber (itemtype => itemtype,
532       						              itemkey  => itemkey,
533       		                                              aname    => 'PO_HEADER_ID');
534 
535             l_po_style_type   := wf_engine.GetItemAttrText   (itemtype => itemtype,
536       						              itemkey  => itemkey,
537       		                                              aname    => 'PO_STYLE_TYPE');
538 
539       	    l_doc_type        := wf_engine.GetItemAttrText   (itemtype => itemtype,
540       						              itemkey  => itemkey,
541       		                                              aname    => 'DOCUMENT_TYPE');
542 
543       	    x_progress := 'PROMISE_DATE_WITHIN_TOL:001';
544 
545         IF (l_change_group_id IS NOT NULL) THEN
546 
547       	    -- check only for doc type SPO AND BPA Release (get the value from item attribute DOCUMENT_TYPE)
548       		-- DOC_SUB_TYPE is already checked in business rules check
549 
550           IF( (l_doc_type = 'PO') OR  (l_doc_type = 'RELEASE')) THEN
551       		  -- get the promise date tolerances in days
552       		      l_prom_date_dec   := wf_engine.GetItemAttrNumber (itemtype => itemtype,
553          			                                        itemkey  => itemkey,
554       		                                                        aname    => 'PROMISE_DATE_DEC');
555       	              l_prom_date_incr  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
556          			                                        itemkey  => itemkey,
557       		                                                        aname    => 'PROMISE_DATE_INCR');
558 
559       		  log_message('PROMISE_DATE_WITHIN_TOL','Promise Date Incr and decr Values',l_prom_date_incr || ', '|| l_prom_date_dec);
560 
561       	      x_progress := 'PROMISE_DATE_WITHIN_TOL:002';
562 
563               OPEN c_promise_date_changes(l_po_header_id,l_change_group_id) ;
564       		     LOOP
565       		     FETCH     c_promise_date_changes
566       		     INTO      l_old_promise_date,
567       		               l_new_promise_date,
568 			       l_promised_date,
569 			       l_need_by_date;
570       		     EXIT WHEN c_promise_date_changes%NOTFOUND;
571 
572       		     x_progress := 'PROMISE_DATE_WITHIN_TOL:003';
573       		      log_message('PROMISE_DATE_WITHIN_TOL','Old & New Promise Date',l_old_promise_date || ', '|| l_new_promise_date);
574 
575                         IF (l_promised_date is null AND l_need_by_date is null) THEN
576                            CLOSE c_promise_date_changes;
577                            l_return_val :='N' ;
578                         END IF;
579 
580 
581       		         EXIT WHEN (l_return_val = 'N');
582 
583       		         IF (NOT change_within_tol_date(l_old_promise_date, l_new_promise_date, l_prom_date_incr, l_prom_date_dec)) THEN
584       		         l_return_val := 'N';
585       		         END IF;
586 
587       		      x_progress := 'PROMISE_DATE_WITHIN_TOL:004';
588       		      END LOOP;
589 
590       		    IF c_promise_date_changes%ISOPEN THEN
591 	       CLOSE c_promise_date_changes;
592                     END IF;
593 
594       	    END IF;  -- DOC_TYPE check
595         END IF;  --l_change_group_id IS NOT NULL
596 
597       	-- set result value
598         resultout :=  wf_engine.eng_completed || ':' || l_return_val ;
599 
600         x_progress := 'PROMISE_DATE_WITHIN_TOL:005';
601 
602         log_message('PROMISE_DATE_WITHIN_TOL','Result',resultout);
603 
604   END IF; -- IF ( funcmode = 'RUN' )
605 
606 
607 EXCEPTION
608    WHEN OTHERS THEN
609      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610                FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
611                               g_module_prefix,
612                               x_progress || ':unexpected error' || Sqlerrm);
613      END IF;
614      wf_core.context('POSCHORD', 'PROMISE_DATE_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
615      raise;
616 END PROMISE_DATE_WITHIN_TOL;
617 
618 
619 /* This procedure checks whether Unit_Price_Change is within the tolerance or not
620    Returns 'Y' if within the tolerance
621    Returns 'N' if out of tolerance
622 */
623 PROCEDURE UNIT_PRICE_WITHIN_TOL(   itemtype        IN VARCHAR2,
624  	                           itemkey         IN VARCHAR2,
625  	                           actid           IN NUMBER,
626  	                           funcmode        IN VARCHAR2,
627                                    resultout       OUT NOCOPY VARCHAR2)
628 
629 IS
630 -- This cursor picks up Unit Price chnages for SPO at Line Level
631   CURSOR c_unit_price_changes ( p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER) IS
632 	   SELECT old_price,new_price
633 	   FROM   po_change_requests
634 	   WHERE  document_header_id=p_po_header_id_csr
635 	          AND CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
636 	          AND request_level = 'LINE'
637 	          AND new_price IS NOT NULL
638 	          AND action_type = 'MODIFICATION'
639 	          AND request_status = 'PENDING'
640 	          AND initiator='SUPPLIER';
641 
642 -- This  cursor picks up unit price changes for  BPA release at the shipment level
643 -- pcr.old_price is added to consider price breaks for release
644   CURSOR c_ship_unit_price_rel (p_po_release_id_csr IN NUMBER,p_po_header_id IN NUMBER,p_change_group_id_csr IN NUMBER) IS
645           SELECT  plla.price_override,nvl(pcr.new_price,pcr.old_price)
646 	  	   FROM   po_change_requests  pcr,
647 	  	          po_line_locations_all plla
648 	  	   WHERE  pcr.po_release_id= p_po_release_id_csr
649 	  	          AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
650 	  	          AND pcr.request_level = 'SHIPMENT'
651 	  	          AND pcr.new_price IS NOT NULL
652 	  	          AND pcr.action_type = 'MODIFICATION'
653 	  	          AND pcr.request_status = 'PENDING'
654 	  	          AND pcr.initiator='SUPPLIER'
655 			      AND pcr.document_line_location_id = plla.line_location_id;
656 
657 -- cursor to check for the COMPLEX WORK (Financing Case)
658 CURSOR c_line_unit_price_cw (p_po_release_id_csr IN NUMBER,p_po_header_id IN NUMBER,p_change_group_id_csr IN NUMBER) IS
659 	           SELECT pl.unit_price,pcr.new_price
660 	  	  	   FROM   po_change_requests  pcr,
661 	  	  	          po_lines_all pl
662 	  	  	   WHERE  pcr.document_header_id= p_po_header_id
663 	  	  	          AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
664 	  	  	          AND pcr.request_level = 'LINE'
665 	  	  	          AND pcr.new_price IS NOT NULL
666 	  	  	          AND pcr.action_type = 'MODIFICATION'
667 	  	  	          AND pcr.request_status = 'PENDING'
668 	  	  	          AND pcr.initiator='SUPPLIER'
669 			          AND pcr.document_line_id = pl.po_line_id;
670 
671 
672   -- cursor to check for the Complex Work( Actuals Case)
673 
674 
675   l_po_header_id            po_headers_all.po_header_id%TYPE;
676   l_po_release_id           po_releases_all.po_release_id%TYPE;
677   l_change_group_id         po_change_requests.change_request_group_id%type;
678   l_unitprice_lower_tol     number;
679   l_unitprice_upper_tol     number;
680   l_old_price               po_change_requests.old_price%type;
681   l_new_price		    po_change_requests.new_price%type;
682   x_progress                VARCHAR2(1000);
683   l_return_val              VARCHAR2(1):='Y';
684   l_po_style_type           VARCHAR2(10);
685   l_doc_type                VARCHAR2(10);
686   l_complex_po_style        VARCHAR2(10);
687 
688 BEGIN
689 
690   IF ( funcmode = 'RUN' ) THEN
691 
692      x_progress := 'UNIT_PRICE_WITHIN_TOL:000';
693 
694      l_change_group_id    :=  wf_engine.GetItemAttrNumber (itemtype => itemtype,
695 						           itemkey  => itemkey,
696 		                                           aname    => 'CHANGE_REQUEST_GROUP_ID');
697 
698      l_po_header_id       :=  wf_engine.GetItemAttrNumber (itemtype => itemtype,
699       						           itemkey  => itemkey,
700       		                                           aname    => 'PO_HEADER_ID');
701 
702      l_po_release_id      :=  wf_engine.GetItemAttrNumber (itemtype => itemtype,
703       						           itemkey  => itemkey,
704       		                                           aname    => 'PO_RELEASE_ID');
705 
706      l_po_style_type      :=  wf_engine.GetItemAttrText   (itemtype => itemtype,
707       						           itemkey  => itemkey,
708       		                                           aname    => 'PO_STYLE_TYPE');
709 
710      l_doc_type           :=  wf_engine.GetItemAttrText   (itemtype => itemtype,
711       						           itemkey  => itemkey,
712       		                                           aname    => 'DOCUMENT_TYPE');
713 
714      l_complex_po_style   :=   wf_engine.GetItemAttrText   (itemtype => itemtype,
715       						           itemkey  => itemkey,
716       		                                           aname    => 'COMPLEX_PO_STYLE');
717 
718      x_progress := 'UNIT_PRICE_WITHIN_TOL:001';
719  IF (l_change_group_id IS NOT NULL) THEN
720      -- check for the DOC types (applicable for  PO unit price( Line level)  and BPA release unit price(Shipment Level)
721 	 -- if other doc types return true and exit
722    IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
723 	  -- get the unit price percentage tolerances
724 	  l_unitprice_lower_tol     :=  wf_engine.GetItemAttrNumber (itemtype => itemtype,
725       						                     itemkey  => itemkey,
726       		                                                     aname    => 'UNIT_PRICE_DEC');
727 
728 	  l_unitprice_upper_tol     :=  wf_engine.GetItemAttrNumber (itemtype => itemtype,
729       						                     itemkey  => itemkey,
730       		                                                     aname    => 'UNIT_PRICE_INCR');
731 	  x_progress := 'UNIT_PRICE_WITHIN_TOL:002';
732       log_message('UNIT_PRICE_WITHIN_TOL','Unit Price Incr & Decr Values',l_unitprice_upper_tol || ', '|| l_unitprice_lower_tol);
733     IF(l_doc_type = 'PO' and l_po_style_type='NORMAL' ) THEN
734 	OPEN c_unit_price_changes(l_po_header_id,l_change_group_id);
735 	  LOOP
736 	  FETCH c_unit_price_changes
737           INTO  l_old_price,
738 	        l_new_price;
739 	  x_progress := 'UNIT_PRICE_WITHIN_TOL:003';
740 	  log_message('UNIT_PRICE_WITHIN_TOL','Old & New Price Values',l_old_price || ', '|| l_new_price);
741 	  EXIT WHEN c_unit_price_changes%NOTFOUND;
742           EXIT WHEN (l_return_val = 'N');
743 
744 	    IF (NOT change_within_tol(l_old_price, l_new_price, l_unitprice_upper_tol, l_unitprice_lower_tol,0,0)) THEN
745 	        l_return_val := 'N';
746 	    END IF;
747 	  x_progress := 'UNIT_PRICE_WITHIN_TOL:004';
748 	  END LOOP;
749 	CLOSE  c_unit_price_changes;
750     ELSIF(l_doc_type = 'RELEASE' and l_po_style_type='NORMAL') THEN
751 	OPEN c_ship_unit_price_rel(l_po_release_id,l_po_header_id,l_change_group_id);
752 	   LOOP
753 	   FETCH c_ship_unit_price_rel
754 	   INTO  l_old_price,
755 	   	 l_new_price;
756 	   x_progress := 'UNIT_PRICE_WITHIN_TOL:005';
757 	   log_message('UNIT_PRICE_WITHIN_TOL','Old & New Price Values',l_old_price || ', '|| l_new_price);
758 	   EXIT WHEN c_ship_unit_price_rel%NOTFOUND;
759 	   EXIT WHEN (l_return_val = 'N');
760 
761 	     IF (NOT change_within_tol(l_old_price, l_new_price, l_unitprice_upper_tol, l_unitprice_lower_tol,0,0)) THEN
762 	   	        l_return_val := 'N';
763 	     END IF;
764 	   x_progress := 'UNIT_PRICE_WITHIN_TOL:006';
765 	   END LOOP;
766 	 CLOSE  c_ship_unit_price_rel;
767      END IF;  -- PO Or RELEASE
768 
769 
770      IF(l_po_style_type='COMPLEX') THEN
771      	    IF(l_complex_po_style = 'FINANCING') THEN
772                OPEN c_line_unit_price_cw(l_po_release_id,l_po_header_id,l_change_group_id);
773      	   	  LOOP
774      	   	  FETCH c_line_unit_price_cw
775      	          INTO  l_old_price,
776      	   	        l_new_price;
777      	   	  x_progress := 'UNIT_PRICE_WITHIN_TOL:007';
778      	   	  log_message('UNIT_PRICE_WITHIN_TOL','Old & New Price Values',l_old_price || ', '|| l_new_price);
779      	   	  EXIT WHEN c_line_unit_price_cw%NOTFOUND;
780      	   	  EXIT WHEN (l_return_val = 'N');
781      	   	    IF (NOT change_within_tol(l_old_price, l_new_price, l_unitprice_upper_tol, l_unitprice_lower_tol,0,0)) THEN
782      	   	        l_return_val := 'N';
783      	   	    END IF;
784      	   	  x_progress := 'UNIT_PRICE_WITHIN_TOL:008';
785      	   	  END LOOP;
786      	   	CLOSE  c_line_unit_price_cw;
787              END IF;  -- financing
788       END IF; -- po_style_type='COMPLEX'
789 
790   END IF;  -- doc_type PO or RELEASE
791 
792  END IF;  -- change_group_id is not null
793 
794 		-- set result value
795 
796 	resultout := wf_engine.eng_completed|| ':' || l_return_val ;
797 	x_progress := 'UNIT_PRICE_WITHIN_TOL:009';
798         log_message('UNIT_PRICE_WITHIN_TOL','Result',resultout);
799 
800  END IF; -- IF ( funcmode = 'RUN' )
801 
802 EXCEPTION
803   WHEN OTHERS THEN
804 
805     IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
806               FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
807                              g_module_prefix,
808                              x_progress || ':unexpected error' || Sqlerrm);
809     END IF;
810 
811 
812   wf_core.context('POSCHORD', 'UNIT_PRICE_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
813 
814   raise;
815 
816 END UNIT_PRICE_WITHIN_TOL;
817 
818 
819 /* This procedure checks whether Shipment_Quantity_Change is within the tolerance or not
820    Returns 'Y' if within the tolerance
821    Returns 'N' if out of tolerance
822 */
823 
824 PROCEDURE SHIP_QUANTITY_WITHIN_TOL( itemtype        IN VARCHAR2,
825  	                            itemkey         IN VARCHAR2,
826  	                            actid           IN NUMBER,
827  	                            funcmode        IN VARCHAR2,
828                                     resultout       OUT NOCOPY VARCHAR2)
829 IS
830 -- This cursor picks up shipment quantity changes for SPO and BPA releases
831   CURSOR c_ship_qty_changes (p_change_group_id_csr IN NUMBER) IS
832 	 SELECT pcr.old_quantity,
833 	        pcr.new_quantity
834 	 FROM   po_change_requests pcr
835 	 WHERE  pcr.change_request_group_id=p_change_group_id_csr
836 	       AND pcr.new_quantity IS NOT NULL
837 	       AND pcr.action_type='MODIFICATION'
838 	       AND pcr.request_status= 'PENDING'
839 	       AND pcr.request_level= 'SHIPMENT'
840 	       AND pcr.initiator= 'SUPPLIER';
841 
842   l_old_ship_qty           po_change_requests.old_quantity%TYPE;
843   l_new_ship_qty           po_change_requests.new_quantity%TYPE;
844   l_return_val             VARCHAR2(1) :='Y';
845   l_ship_qty_max_incr_per  NUMBER;
846   l_shipq_ty_max_dec_per   NUMBER;
847   l_ship_qty_max_incr_val  NUMBER;
848   l_ship_qty_max_dec_val   NUMBER;
849   x_progress               VARCHAR2(1000);
850   l_po_header_id           po_headers_all.po_header_id%TYPE;
851   l_change_group_id        po_change_requests.change_request_group_id%type;
852   l_po_style_type          VARCHAR2(10);
853   l_doc_type               VARCHAR2(10);
854 
855 BEGIN
856 
857   IF ( funcmode = 'RUN' ) THEN
858 
859         x_progress := 'SHIP_QUANTITY_WITHIN_TOL:000';
860         l_po_header_id     := wf_engine.GetItemAttrNumber (itemtype => itemtype,
861 					                   itemkey  => itemkey,
862 	                                                   aname    => 'PO_HEADER_ID');
863 
864 	l_change_group_id  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
865 	 					           itemkey  => itemkey,
866 	                                                   aname    => 'CHANGE_REQUEST_GROUP_ID');
867 
868 	l_po_style_type    := wf_engine.GetItemAttrText   (itemtype => itemtype,
869       						           itemkey  => itemkey,
870       		                                           aname    => 'PO_STYLE_TYPE');
871 
872         l_doc_type         := wf_engine.GetItemAttrText   (itemtype => itemtype,
873       						           itemkey  => itemkey,
874       		                                           aname    => 'DOCUMENT_TYPE');
875 	 x_progress := 'SHIP_QUANTITY_WITHIN_TOL:001';
876       IF (l_change_group_id IS NOT NULL) THEN
877 	 -- get shipment quantity tolerances
878 	 -- check for the DOC types (applicable for  PO  and BPA release)
879 	  -- if other doc types return true and exit
880         IF (l_po_style_type='NORMAL') THEN
881 
882           l_ship_qty_max_incr_per :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
883       						                 itemkey  => itemkey,
884       		                                                 aname    => 'SHIP_QUANTITY_INCR');
885 
886           l_shipq_ty_max_dec_per  :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
887       						                 itemkey  => itemkey,
888       		                                                 aname    => 'SHIP_QUANTITY_DEC');
889         ELSIF (l_po_style_type='COMPLEX') THEN
890 
891           l_ship_qty_max_incr_per :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
892       						                 itemkey  => itemkey,
893       		                                                 aname    => 'PAY_QUANTITY_INCR');
894 
895           l_shipq_ty_max_dec_per  :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
896       						                 itemkey  => itemkey,
897       		                                                 aname    => 'PAY_QUANTITY_DEC');
898         END IF;
899           x_progress := 'SHIP_QUANTITY_WITHIN_TOL:002';
900           log_message('SHIP_QUANTITY_WITHIN_TOL','Ship Quantity Max Incr & dec Values', l_ship_qty_max_incr_per || ', ' || l_shipq_ty_max_dec_per);
901 	  OPEN c_ship_qty_changes (l_change_group_id);
902 
903 	    LOOP
904 	    FETCH c_ship_qty_changes
905 	    INTO  l_old_ship_qty,
906 	          l_new_ship_qty;
907             EXIT WHEN c_ship_qty_changes%NOTFOUND;
908             x_progress := 'SHIP_QUANTITY_WITHIN_TOL:003';
909 	    log_message('SHIP_QUANTITY_WITHIN_TOL','Old & New Ship Quantity values',l_old_ship_qty || ', ' ||l_new_ship_qty);
910 	    EXIT WHEN (l_return_val = 'N');
911 
912 	    IF (NOT change_within_tol(l_old_ship_qty, l_new_ship_qty, l_ship_qty_max_incr_per, l_shipq_ty_max_dec_per,0,0)) THEN
913 	       l_return_val := 'N';
914 	    END IF;
915 	    x_progress:= 'SHIP_QUANTITY_WITHIN_TOL:004';
916 	    END LOOP;
917 	  CLOSE  c_ship_qty_changes;
918       END IF;  -- change group Id is not null
919 	-- set result value
920 	resultout := wf_engine.eng_completed|| ':' || l_return_val ;
921 	x_progress := 'SHIP_QUANTITY_WITHIN_TOL:005';
922 	log_message('SHIP_QUANTITY_WITHIN_TOL','Result',resultout);
923 
924   END IF; -- IF ( funcmode = 'RUN' )
925 
926 EXCEPTION
927    WHEN OTHERS THEN
928    IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
929           FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
930                          g_module_prefix,
931                          x_progress || ':unexpected error' || Sqlerrm);
932    END IF;
933   wf_core.context('POSCHORD', 'SHIP_QUANTITY_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
934   raise;
935 END SHIP_QUANTITY_WITHIN_TOL;
936 
937 
938 /* This procedure checks whether Document Amount Change is within the tolerance or not
939    Any Line Price change or shipment quantity chnage that affects the amount is also
940    taken in to consideration
941    Returns 'Y' if within the tolerance
942    Returns 'N' if out of tolerance
943 */
944 
945 
946 PROCEDURE DOC_AMOUNT_WITHIN_TOL( itemtype        IN VARCHAR2,
947  	                         itemkey         IN VARCHAR2,
948  	                         actid           IN NUMBER,
949  	                         funcmode        IN VARCHAR2,
950                                  resultout       OUT NOCOPY VARCHAR2)
951 IS
952 
953 --  Picks up Old Amount for SPO
954 CURSOR c_old_doc_amt_changes(p_po_header_id_csr IN NUMBER)  IS
955 	 SELECT  sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pl.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
956          FROM 	 po_lines_all pl,
957                  po_line_locations_all pll
958          WHERE   pl.po_header_id = p_po_header_id_csr
959 	 	 AND pll.po_line_id = pl.po_line_id;
960 -- Picks up Old Amount For Releases
961 CURSOR c_old_doc_amt_changes_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER)  IS
962 	 SELECT  sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
963          FROM 	 po_lines_all pl,
964                  po_line_locations_all pll
965          WHERE   pll.po_release_id = p_po_release_id_csr
966                  AND pll.po_header_id = p_po_header_id_csr
967 	 	 AND pll.po_line_id = pl.po_line_id;
968 
969  -- Picks up Old Amount for Complex POs ( Actuals case )
970  CURSOR c_old_doc_amt_cw_actuals(p_po_header_id_csr IN NUMBER) IS
971         SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
972                                           (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
973                                              'AMOUNT',
974                    DECODE(pll.payment_type,  'LUMPSUM',
975                                               (pll.amount - NVL(pll.amount_cancelled,0)),
976                                               'MILESTONE',
977                                      	      (pll.amount - NVL(pll.amount_cancelled,0)),
978                                               'RATE',
979                                               (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override))))
980         FROM 	 po_lines_all pl,
981                  po_line_locations_all pll
982         WHERE    pl.po_header_id = p_po_header_id_csr
983 	         AND pll.po_line_id = pl.po_line_id;
984 
985 
986  -- Picks up Old Doc Amount for Complex Pos( Financing case)
987  CURSOR c_old_doc_amt_cw_financing(p_po_header_id_csr IN NUMBER) IS
988         SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
989 	                                    (pl.quantity*pl.unit_price),
990 					    'AMOUNT',
991 					    (pl.amount)))
992               FROM po_lines_all pl
993 	      WHERE pl.po_header_id = p_po_header_id_csr;
994 
995   l_po_header_id         po_change_requests.document_header_id%TYPE;
996   l_po_release_id        po_change_requests.po_release_id%TYPE;
997   l_change_group_id      po_change_requests.change_request_group_id%TYPE;
998   l_old_doc_amt          NUMBER;
999   l_total_new_doc_amt    NUMBER;
1000   l_new_doc_amt_rel      NUMBER;
1001   l_return_val           VARCHAR2(1) :='Y';
1002   l_doc_amt_max_incr_per NUMBER;
1003   l_doc_amt_max_dec_per  NUMBER;
1004   l_doc_amt_max_incr_val NUMBER;
1005   l_doc_amt_max_dec_val  NUMBER;
1006   x_progress             VARCHAR2(1000);
1007   l_po_style_type        VARCHAR2(10);
1008   l_doc_type             VARCHAR2(10);
1009   l_complex_po_style     VARCHAR2(10);
1010 
1011 BEGIN
1012 
1013   IF ( funcmode = 'RUN' ) THEN
1014 
1015        x_progress := 'DOC_AMOUNT_WITHIN_TOL:000';
1016        l_po_header_id      := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1017                                                            itemkey  => itemkey,
1018 	                                                   aname    => 'PO_HEADER_ID');
1019 
1020        l_change_group_id   := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1021 	 					           itemkey  => itemkey,
1022 	                                                   aname    => 'CHANGE_REQUEST_GROUP_ID');
1023 
1024        l_po_release_id     := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1025                                                            itemkey  => itemkey,
1026 	                                                   aname    => 'PO_RELEASE_ID');
1027 
1028        l_po_style_type     :=  wf_engine.GetItemAttrText (itemtype => itemtype,
1029       						          itemkey  => itemkey,
1030       		                                          aname    => 'PO_STYLE_TYPE');
1031 
1032        l_doc_type          := wf_engine.GetItemAttrText (itemtype => itemtype,
1033       						         itemkey  => itemkey,
1034       		                                         aname    => 'DOCUMENT_TYPE');
1035 
1036        l_complex_po_style  := wf_engine.GetItemAttrText (itemtype => itemtype,
1037       						         itemkey  => itemkey,
1038       		                                         aname    => 'COMPLEX_PO_STYLE');
1039 
1040        x_progress := 'DOC_AMOUNT_WITHIN_TOL:001';
1041 
1042   IF (l_change_group_id IS NOT NULL) THEN
1043 
1044 	    -- get po document total tolerances
1045 	    -- check for the DOC types (applicable for  PO Amount  and BPA  Release Amount)
1046 	    -- if other doc types return true and exit
1047       IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
1048 
1049 	    l_doc_amt_max_incr_per  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1050       						                    itemkey  => itemkey,
1051       		                                                    aname    => 'DOC_AMOUNT_INCR_PER');
1052             l_doc_amt_max_dec_per   := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1053       						                    itemkey  => itemkey,
1054       		                                                    aname    => 'DOC_AMOUNT_DEC_PER');
1055 
1056 	    x_progress := 'DOC_AMOUNT_WITHIN_TOL:002';
1057 	    log_message('DOC_AMOUNT_WITHIN_TOL','Doc Amount Inc & Dec percentage',l_doc_amt_max_incr_per || ', '|| l_doc_amt_max_dec_per);
1058 
1059 
1060             l_doc_amt_max_incr_val  :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
1061       						                   itemkey  => itemkey,
1062       		                                                   aname    => 'DOC_AMOUNT_INCR_VAL');
1063             l_doc_amt_max_dec_val   :=wf_engine.GetItemAttrNumber (itemtype => itemtype,
1064       						                   itemkey  => itemkey,
1065       		                                                   aname    => 'DOC_AMOUNT_DEC_VAL');
1066 
1067 	    x_progress := 'DOC_AMOUNT_WITHIN_TOL:003';
1068 	    log_message('DOC_AMOUNT_WITHIN_TOL','Doc Amount Inc & Dec Values',l_doc_amt_max_incr_val|| ', '||l_doc_amt_max_dec_val);
1069 
1070 
1071          IF(l_doc_type = 'PO') THEN
1072 
1073            IF(l_po_style_type = 'NORMAL') THEN
1074 	    OPEN c_old_doc_amt_changes(l_po_header_id);
1075 	       FETCH c_old_doc_amt_changes INTO  l_old_doc_amt;
1076                x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1077                log_message('DOC_AMOUNT_WITHIN_TOL','Old AMount ',l_old_doc_amt);
1078 	       l_total_new_doc_amt:= CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1079 	       x_progress := 'DOC_AMOUNT_WITHIN_TOL:008';
1080 	       log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_total_new_doc_amt);
1081 	    CLOSE c_old_doc_amt_changes;
1082 
1083 	   ELSIF(l_po_style_type = 'COMPLEX') THEN
1084 
1085 	     IF(l_complex_po_style = 'ACTUALS') THEN
1086 
1087 	       OPEN c_old_doc_amt_cw_actuals(l_po_header_id);
1088                FETCH c_old_doc_amt_cw_actuals INTO  l_old_doc_amt;
1089                x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1090                log_message('DOC_AMOUNT_WITHIN_TOL','Old AMount ',l_old_doc_amt);
1091 	       l_total_new_doc_amt:= CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1092 	       x_progress := 'DOC_AMOUNT_WITHIN_TOL:008';
1093 	       log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_total_new_doc_amt);
1094 	       CLOSE c_old_doc_amt_cw_actuals;
1095 
1096              ELSIF(l_complex_po_style = 'FINANCING') THEN
1097 
1098 	       OPEN c_old_doc_amt_cw_financing(l_po_header_id);
1099                FETCH c_old_doc_amt_cw_financing INTO  l_old_doc_amt;
1100                x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1101                log_message('DOC_AMOUNT_WITHIN_TOL','Old AMount ',l_old_doc_amt);
1102 	       l_total_new_doc_amt:= CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1103 	       x_progress := 'DOC_AMOUNT_WITHIN_TOL:008';
1104 	       log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_total_new_doc_amt);
1105 	       CLOSE c_old_doc_amt_cw_financing;
1106 
1107              END IF;
1108 
1109            END IF;
1110 	    IF (NOT change_within_tol(l_old_doc_amt, l_total_new_doc_amt, l_doc_amt_max_incr_per, l_doc_amt_max_dec_per, l_doc_amt_max_incr_val, l_doc_amt_max_dec_val)) THEN
1111 	        l_return_val := 'N';
1112 	    END IF;
1113 	    x_progress := 'DOC_AMOUNT_WITHIN_TOL:009';
1114 
1115 	 ELSIF(l_doc_type = 'RELEASE') THEN
1116 
1117 	    OPEN c_old_doc_amt_changes_rel(l_po_header_id,l_po_release_id);
1118 	      FETCH c_old_doc_amt_changes_rel INTO  l_old_doc_amt;
1119 	      x_progress := 'DOC_AMOUNT_WITHIN_TOL:010';
1120               log_message('DOC_AMOUNT_WITHIN_TOL','Old Amount',l_old_doc_amt);
1121 	    CLOSE c_old_doc_amt_changes_rel;
1122 
1123 	      l_new_doc_amt_rel :=  CALCULATE_NEW_DOC_AMOUNT(l_po_header_id,l_po_release_id,l_complex_po_style);
1124               x_progress := 'DOC_AMOUNT_WITHIN_TOL:011';
1125               log_message('DOC_AMOUNT_WITHIN_TOL','New Amount',l_new_doc_amt_rel);
1126 
1127 	      IF (NOT change_within_tol(l_old_doc_amt, l_new_doc_amt_rel, l_doc_amt_max_incr_per, l_doc_amt_max_dec_per, l_doc_amt_max_incr_val, l_doc_amt_max_dec_val)) THEN
1128 	        l_return_val := 'N';
1129 	      END IF;
1130 	 END IF; -- l_doc_type = PO , l_doc_type = RELEASE
1131 
1132 	       x_progress := 'DOC_AMOUNT_WITHIN_TOL:012';
1133      END IF; -- PO Or RELEASE
1134 
1135    END IF; -- change group id is not null
1136 
1137 	  -- set result value
1138 	  resultout := wf_engine.eng_completed|| ':' || l_return_val ;
1139 
1140 	  x_progress := 'DOC_AMOUNT_WITHIN_TOL:013';
1141 	  log_message('DOC_AMOUNT_WITHIN_TOL','Result',resultout);
1142 
1143  END IF; -- IF ( funcmode = 'RUN' )
1144 
1145 EXCEPTION
1146    WHEN OTHERS THEN
1147       IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148           FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1149                          g_module_prefix,
1150                          x_progress || ':unexpected error' || Sqlerrm);
1151       END IF;
1152  wf_core.context('POSCHORD', 'DOC_AMOUNT_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
1153  raise;
1154 END DOC_AMOUNT_WITHIN_TOL;
1155 
1156 
1157 FUNCTION CALCULATE_NEW_DOC_AMOUNT(  p_po_header_id IN NUMBER , p_po_release_id IN NUMBER, p_complex_po_style IN VARCHAR2)
1158 RETURN NUMBER
1159 IS
1160 -- Picks up new amount for SPO
1161 CURSOR c_new_doc_amt_changes(p_po_header_id_csr IN NUMBER) IS
1162 	 SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT',(nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
1163 	          (nvl(pcr.new_price,pl.unit_price) *
1164 	          (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1165 	          FROM    po_change_requests pcr, --line amount/price change
1166 	 	 	 po_change_requests pcr1, --shipment quantity change
1167 	 	 	 po_lines_all pl,
1168 	 	 	 po_line_locations_all pll
1169 	          WHERE   pl.po_header_id = p_po_header_id_csr
1170 	 	 	 AND pll.po_line_id = pl.po_line_id
1171 	                 AND pcr1.document_header_id (+) = p_po_header_id_csr
1172 	                 AND pcr1.document_line_location_id(+) = pll.line_location_id
1173 	 	         AND pcr1.action_type(+) = 'MODIFICATION'
1174 	 	         AND pcr1.request_status(+) = 'PENDING'
1175 	 	         AND pcr1.request_level (+) = 'SHIPMENT'
1176 	 	         AND pcr1.initiator(+) = 'SUPPLIER'
1177 	                 AND pcr.document_line_id(+) = pl.po_line_id
1178 	 	         AND pcr.action_type(+) = 'MODIFICATION'
1179 	 	         AND pcr.request_status(+) = 'PENDING'
1180 	 	         AND pcr.request_level (+) = 'LINE'
1181 	 	         AND pcr.initiator(+) = 'SUPPLIER'
1182 	 	UNION ALL
1183 	   -- for splitted shipments
1184 	  SELECT   nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
1185 	           (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
1186 	          FROM    po_change_requests pcr, --line amount/price change
1187 	 		 po_change_requests pcr2, --for split shipments
1188 	 	 	 po_lines_all pl,
1189 	 	 	 po_line_locations_all pll
1190 	          WHERE   pl.po_header_id = p_po_header_id_csr
1191 	 	 	 AND pll.po_line_id = pl.po_line_id
1192 	                 AND pcr2.document_header_id = p_po_header_id_csr
1193 	                 AND pcr2.parent_line_location_id = pll.line_location_id
1194 	 	         AND pcr2.action_type = 'MODIFICATION'
1195 	 	         AND pcr2.request_status = 'PENDING'
1196 	 	         AND pcr2.request_level  = 'SHIPMENT'
1197 	 	         AND pcr2.initiator = 'SUPPLIER'
1198 	                 AND pcr.document_line_id(+) = pl.po_line_id
1199 	 	         AND pcr.action_type(+) = 'MODIFICATION'
1200 	 	         AND pcr.request_status(+) = 'PENDING'
1201 	 	         AND pcr.request_level (+) = 'LINE'
1202 	                 AND pcr.initiator(+) = 'SUPPLIER';
1203 
1204   -- Picks up new amount for releases
1205   -- old_price included for price breaks
1206  CURSOR c_new_doc_amt_changes_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER ) IS
1207   SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)), (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) * (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1208   	FROM    po_change_requests pcr,
1209   		po_lines_all pl,
1210   		po_line_locations_all pll
1211   	WHERE  pll.po_header_id = p_po_header_id_csr
1212                AND pll.po_release_id = p_po_release_id_csr
1213   	       AND pll.po_line_id = pl.po_line_id
1214   	       AND pcr.po_release_id(+) = p_po_release_id_csr
1215   	       AND pcr.document_header_id(+) = p_po_header_id_csr
1216   	       --AND pcr.document_line_id = pl.po_line_id
1217   	       AND pcr.action_type(+) = 'MODIFICATION'
1218   	       AND pcr.request_status(+) = 'PENDING'
1219   	       AND pcr.request_level (+) = 'SHIPMENT'
1220   	       AND pcr.initiator(+) = 'SUPPLIER'
1221   	       AND pcr.document_line_location_id(+) = pll.line_location_id
1222   	 UNION ALL
1223   SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(pcr2.new_price,pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
1224   	FROM    po_change_requests pcr2, -- for splitted shipments
1225   		po_lines_all pl,
1226   		po_line_locations_all pll
1227   	WHERE  pll.po_header_id = p_po_header_id_csr
1228   	       AND pll.po_line_id = pl.po_line_id
1229   	       AND pcr2.po_release_id(+) = p_po_release_id_csr
1230   	       AND pcr2.document_header_id(+) = p_po_header_id_csr
1231   	       AND pcr2.document_line_id(+) = pl.po_line_id
1232   	       AND pcr2.action_type(+) = 'MODIFICATION'
1233   	       AND pcr2.request_status(+) = 'PENDING'
1234   	       AND pcr2.request_level (+) = 'SHIPMENT'
1235   	       AND pcr2.initiator(+) = 'SUPPLIER'
1236 	       AND pcr2.parent_line_location_id = pll.line_location_id;
1237 
1238 
1239  -- Picks Up New Amount for Complex Po's ( Actuals Case)
1240 CURSOR c_new_doc_amt_chg_cw_actuals(p_po_header_id_csr IN NUMBER) IS
1241  SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1242                                          (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1243                                         'AMOUNT',
1244                DECODE(pll.payment_type, 'LUMPSUM',
1245                                          (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1246                                         'MILESTONE',
1247                                          (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1248                                         'RATE',
1249                                          (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1250  FROM
1251     po_change_requests pcr, --shipment changes
1252     po_lines_all pl,
1253     po_line_locations_all pll
1254  WHERE
1255     pl.po_header_id = p_po_header_id_csr
1256     AND pll.po_line_id = pl.po_line_id
1257     AND pcr.document_header_id (+) = p_po_header_id_csr
1258     AND pcr.document_line_location_id(+) = pll.line_location_id
1259     AND pcr.action_type(+) = 'MODIFICATION'
1260     AND pcr.request_status(+) = 'PENDING'
1261     AND pcr.request_level (+) = 'SHIPMENT'
1262     AND pcr.initiator(+) = 'SUPPLIER'
1263 UNION ALL
1264  -- for split shipment changes
1265 SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1266                                          (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1267                                         'AMOUNT',
1268                DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
1269                                                                   (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1270                                                                   'MILESTONE',
1271                                                                   (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1272                                                                   'RATE',
1273                                                                   (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1274 FROM
1275     po_change_requests pcr, --shipment changes
1276     po_lines_all pl,
1277     po_line_locations_all pll
1278 WHERE
1279     pl.po_header_id = p_po_header_id_csr
1280     AND pll.po_line_id = pl.po_line_id
1281     AND pcr.document_header_id  = p_po_header_id_csr
1282     AND pcr.parent_line_location_id = pll.line_location_id
1283     AND pcr.action_type = 'MODIFICATION'
1284     AND pcr.request_status = 'PENDING'
1285     AND pcr.request_level  = 'SHIPMENT'
1286     AND pcr.initiator = 'SUPPLIER';
1287 
1288 
1289 -- Picks Up New Amount for Complex Po's ( Financing Case)
1290 CURSOR c_new_doc_amt_chg_cw_financing(p_po_header_id_csr IN NUMBER) IS
1291   SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
1292                                           'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
1293   FROM
1294       po_lines_all pl,
1295       po_change_requests pcr
1296   WHERE
1297       pl.po_header_id = p_po_header_id_csr
1298       AND pcr.document_header_id (+) = p_po_header_id_csr
1299       AND pcr.document_line_id(+) = pl.po_line_id
1300       AND pcr.action_type(+) = 'MODIFICATION'
1301       AND pcr.request_status(+) = 'PENDING'
1302       AND pcr.request_level (+) = 'LINE'
1303       AND pcr.initiator(+) = 'SUPPLIER';
1304 
1305 
1306 
1307   l_new_doc_amt          NUMBER;
1308   l_total_new_doc_amt    NUMBER := 0;
1309   x_progress             VARCHAR2(1000);
1310 
1311   BEGIN
1312 
1313     x_progress := 'CALCULATE_NEW_DOC_AMOUNT:000';
1314     IF(p_po_release_id is null) THEN
1315 
1316          x_progress := 'CALCULATE_NEW_DOC_AMOUNT:001';
1317       IF(p_complex_po_style = 'ACTUALS') THEN
1318        OPEN c_new_doc_amt_chg_cw_actuals(p_po_header_id);
1319 	 LOOP
1320 	 FETCH c_new_doc_amt_chg_cw_actuals INTO  l_new_doc_amt;
1321 	 EXIT WHEN c_new_doc_amt_chg_cw_actuals%NOTFOUND;
1322 	     l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1323 	 END LOOP;
1324          x_progress:= 'CALCULATE_NEW_DOC_AMOUNT:002';
1325          log_message('CALCULATE_NEW_DOC_AMOUNT','New AMount ',l_total_new_doc_amt);
1326        CLOSE   c_new_doc_amt_chg_cw_actuals;
1327      ELSIF(p_complex_po_style = 'FINANCING') THEN
1328         OPEN c_new_doc_amt_chg_cw_financing(p_po_header_id);
1329 	 LOOP
1330 	 FETCH c_new_doc_amt_chg_cw_financing INTO  l_new_doc_amt;
1331 	 EXIT WHEN c_new_doc_amt_chg_cw_financing%NOTFOUND;
1332 	     l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1333 	 END LOOP;
1334          x_progress:= 'CALCULATE_NEW_DOC_AMOUNT:002';
1335          log_message('CALCULATE_NEW_DOC_AMOUNT','New AMount ',l_total_new_doc_amt);
1336        CLOSE   c_new_doc_amt_chg_cw_financing;
1337      ELSE
1338         OPEN c_new_doc_amt_changes(p_po_header_id);
1339 	 LOOP
1340 	 FETCH c_new_doc_amt_changes INTO  l_new_doc_amt;
1341 	 EXIT WHEN c_new_doc_amt_changes%NOTFOUND;
1342 	     l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1343 	 END LOOP;
1344          x_progress:= 'CALCULATE_NEW_DOC_AMOUNT:002';
1345          log_message('CALCULATE_NEW_DOC_AMOUNT','New AMount ',l_total_new_doc_amt);
1346         CLOSE   c_new_doc_amt_changes;
1347       END IF;
1348 
1349     ELSIF( p_po_release_id is not null) THEN
1350          x_progress := 'CALCULATE_NEW_DOC_AMOUNT:003';
1351          OPEN c_new_doc_amt_changes_rel(p_po_header_id,p_po_release_id);
1352        	 LOOP
1353        	 FETCH c_new_doc_amt_changes_rel INTO  l_new_doc_amt;
1354        	 EXIT WHEN c_new_doc_amt_changes_rel%NOTFOUND;
1355        	     l_total_new_doc_amt := l_total_new_doc_amt + l_new_doc_amt;
1356        	 END LOOP;
1357          x_progress:= 'DOC_AMOUNT_WITHIN_TOL:004';
1358          log_message('DOC_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_doc_amt);
1359         CLOSE   c_new_doc_amt_changes_rel;
1360           x_progress := 'CALCULATE_NEW_DOC_AMOUNT:005';
1361      END IF;
1362 
1363    return   l_total_new_doc_amt;
1364 
1365   EXCEPTION
1366     WHEN OTHERS THEN
1367       IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1368                 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1369                                g_module_prefix,
1370                                x_progress || ':unexpected error' || Sqlerrm);
1371       END IF;
1372   raise;
1373 
1374 END CALCULATE_NEW_DOC_AMOUNT;
1375 
1376 /* This procedure checks whether Line Amount Change is within the tolerance or not
1377    Any Line Price change or shipment quantity chnage that affects the amount is also
1378    taken in to consideration
1379    Returns 'Y' if within the tolerance
1380    Returns 'N' if out of tolerance
1381 */
1382 
1383 
1384 PROCEDURE LINE_AMOUNT_WITHIN_TOL( itemtype        IN VARCHAR2,
1385  	                          itemkey         IN VARCHAR2,
1386  	                          actid           IN NUMBER,
1387  	                          funcmode        IN VARCHAR2,
1388                                   resultout       OUT NOCOPY VARCHAR2)
1389 IS
1390 -- Picks up Old Line Amount for SPO
1391   CURSOR c_line_amt_old(p_po_header_id_csr IN NUMBER) IS
1392          select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
1393          from po_lines_all pol,
1394               po_line_locations_all pll
1395          where pll.po_header_id = p_po_header_id_csr
1396          AND   pll.po_line_id = pol.po_line_id
1397          GROUP BY pll.po_line_id;
1398 
1399   CURSOR c_line_amt_old_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
1400        select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
1401        from po_lines_all pol,
1402             po_line_locations_all pll
1403        where pll.po_release_id =p_po_release_id_csr
1404        AND   pll.po_header_id = p_po_header_id_csr
1405        AND   pll.po_line_id = pol.po_line_id
1406        GROUP BY pll.po_line_id;
1407 
1408 
1409  -- Picks up Old Line Amount ( Financing Case)
1410   CURSOR c_line_amt_old_cw_financing(p_po_header_id_csr IN NUMBER) IS
1411         SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',pl.unit_price*pl.quantity,'AMOUNT',pl.amount)),pl.po_line_id
1412         FROM po_lines_all pl
1413 	WHERE pl.po_header_id =  p_po_header_id_csr
1414 	GROUP BY pl.po_line_id;
1415 
1416  -- Picks up Old Line Amount ( Actuals case)
1417    CURSOR c_line_amt_old_cw_actuals(p_po_header_id_csr IN NUMBER) IS
1418        SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
1419                                           (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
1420                                            'AMOUNT',
1421                   DECODE(pll.payment_type, 'LUMPSUM',
1422                                            (pll.amount - NVL(pll.amount_cancelled,0)),
1423                                            'MILESTONE',
1424                                      	   (pll.amount - NVL(pll.amount_cancelled,0)),
1425                                            'RATE',
1426                                            (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.po_line_id
1427         FROM 	 po_lines_all pl,
1428                  po_line_locations_all pll
1429         WHERE    pl.po_header_id = p_po_header_id_csr
1430 	         AND pll.po_line_id = pl.po_line_id
1431                  GROUP BY pll.po_line_id;
1432 
1433 
1434   l_old_lineamt           po_change_requests.old_amount%TYPE;
1435   l_temp_po_line_id       NUMBER;
1436   l_return_val            VARCHAR2(1) :='Y';
1437   l_line_amt_max_incr_per    NUMBER;
1438   l_line_amt_max_dec_per     NUMBER;
1439   l_line_amt_max_incr_val    NUMBER;
1440   l_line_amt_max_dec_val     NUMBER;
1441   l_temp_total_line_amt_new  NUMBER;
1442   x_progress              VARCHAR2(1000);
1443   l_po_header_id          po_change_requests.document_header_id%TYPE;
1444   l_po_release_id         NUMBER;
1445   l_po_style_type                 VARCHAR2(10);
1446   l_doc_type                VARCHAR2(10);
1447   l_complex_po_style        VARCHAR2(10);
1448 
1449 BEGIN
1450 
1451 	IF ( funcmode = 'RUN' ) THEN
1452 
1453 	 x_progress := 'LINE_AMOUNT_WITHIN_TOL:000';
1454 
1455 	 l_po_header_id     :=  wf_engine.GetItemAttrNumber (itemtype =>  itemtype,
1456 					                    itemkey  => itemkey,
1457 	                                                    aname    => 'PO_HEADER_ID');
1458 
1459 	 l_po_release_id    :=  wf_engine.GetItemAttrNumber (itemtype =>  itemtype,
1460 					                    itemkey  => itemkey,
1461 	                                                    aname    => 'PO_RELEASE_ID');
1462 
1463 	 l_po_style_type    :=  wf_engine.GetItemAttrText  (itemtype => itemtype,
1464       						            itemkey  => itemkey,
1465       		                                            aname    => 'PO_STYLE_TYPE');
1466 
1467          l_doc_type         :=  wf_engine.GetItemAttrText  (itemtype => itemtype,
1468       						            itemkey  => itemkey,
1469       		                                            aname    => 'DOCUMENT_TYPE');
1470 
1471          l_complex_po_style :=   wf_engine.GetItemAttrText (itemtype => itemtype,
1472       						            itemkey  => itemkey,
1473       		                                            aname    => 'COMPLEX_PO_STYLE');
1474 
1475 
1476 	 x_progress := 'LINE_AMOUNT_WITHIN_TOL:001';
1477 
1478     IF (l_po_header_id IS NOT NULL) THEN
1479 	    -- check for the DOC types (applicable for  PO LINE AMOUNT( Line level)
1480 	    -- if other doc types return true and exit
1481        IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
1482 	    -- get shipment quantity tolerances
1483 	    l_line_amt_max_incr_per  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1484       						                     itemkey  => itemkey,
1485       		                                                     aname    => 'LINE_AMOUNT_INCR_PER');
1486 
1487 	    l_line_amt_max_dec_per   := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1488       						                     itemkey  => itemkey,
1489       		                                                     aname    => 'LINE_AMOUNT_DEC_PER');
1490 
1491 	    x_progress := 'LINE_AMOUNT_WITHIN_TOL:002';
1492 	    log_message('LINE_AMOUNT_WITHIN_TOL','Line Amount Incr & Decr Tol percentage',l_line_amt_max_incr_per || ', '|| l_line_amt_max_dec_per);
1493 
1494 
1495 	    l_line_amt_max_incr_val   := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1496       						                      itemkey  => itemkey,
1497       		                                                      aname    => 'LINE_AMOUNT_INCR_VAL');
1498 
1499 	    l_line_amt_max_dec_val    := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1500       						                      itemkey  => itemkey,
1501       		                                                      aname    => 'LINE_AMOUNT_DEC_VAL');
1502 
1503 	    x_progress := 'LINE_AMOUNT_WITHIN_TOL:003';
1504 	    log_message('LINE_AMOUNT_WITHIN_TOL','Line Amount Incr & Decr Tol values ',l_line_amt_max_incr_val || ', '|| l_line_amt_max_dec_val);
1505 
1506 IF(l_doc_type = 'PO') THEN
1507 
1508 	  IF(l_po_style_type = 'NORMAL') THEN
1509 	    OPEN c_line_amt_old(l_po_header_id);
1510 
1511 	    LOOP
1512 	      FETCH c_line_amt_old
1513 	        INTO l_old_lineamt,l_temp_po_line_id;
1514               EXIT WHEN c_line_amt_old%NOTFOUND;
1515 
1516               x_progress := 'LINE_AMOUNT_WITHIN_TOL:004';
1517               log_message('LINE_AMOUNT_WITHIN_TOL','Line Id ',l_temp_po_line_id);
1518               log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount ',l_old_lineamt);
1519 
1520               EXIT WHEN (l_return_val = 'N');
1521 
1522 	      l_temp_total_line_amt_new := CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1523 
1524 	      x_progress := 'LINE_AMOUNT_WITHIN_TOL:005';
1525 	      log_message('LINE_AMOUNT_WITHIN_TOL','New line amount',l_temp_total_line_amt_new);
1526 
1527               IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per, l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1528 	        l_return_val := 'N';
1529 	      END IF;
1530 	      x_progress:= 'LINE_AMOUNT_WITHIN_TOL:006';
1531 	      log_message('LINE_AMOUNT_WITHIN_TOL','Result',l_return_val);
1532 	      END LOOP;
1533 	    CLOSE  c_line_amt_old;
1534           ELSIF(l_po_style_type = 'COMPLEX') THEN
1535 	    IF(l_complex_po_style = 'ACTUALS') THEN
1536 	       OPEN c_line_amt_old_cw_actuals(l_po_header_id);
1537 
1538 	       LOOP
1539 	        FETCH c_line_amt_old_cw_actuals
1540 	        INTO l_old_lineamt,l_temp_po_line_id;
1541                 EXIT WHEN c_line_amt_old_cw_actuals%NOTFOUND;
1542 
1543                 x_progress := 'LINE_AMOUNT_WITHIN_TOL:004';
1544                 log_message('LINE_AMOUNT_WITHIN_TOL','Line Id ',l_temp_po_line_id);
1545                 log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount ',l_old_lineamt);
1546 
1547                 EXIT WHEN (l_return_val = 'N');
1548 
1549 	        l_temp_total_line_amt_new := CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1550 
1551 	        x_progress := 'LINE_AMOUNT_WITHIN_TOL:005';
1552 	        log_message('LINE_AMOUNT_WITHIN_TOL','New line amount',l_temp_total_line_amt_new);
1553 
1554                 IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per, l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1555 	          l_return_val := 'N';
1556 	        END IF;
1557 	        x_progress:= 'LINE_AMOUNT_WITHIN_TOL:006';
1558 	        log_message('LINE_AMOUNT_WITHIN_TOL','Result',l_return_val);
1559 	        END LOOP;
1560 	        CLOSE  c_line_amt_old_cw_actuals;
1561            ELSIF(l_complex_po_style = 'FINANCING') THEN
1562 	       OPEN c_line_amt_old_cw_financing(l_po_header_id);
1563 
1564 	       LOOP
1565 	        FETCH c_line_amt_old_cw_financing
1566 	        INTO l_old_lineamt,l_temp_po_line_id;
1567                 EXIT WHEN c_line_amt_old_cw_financing%NOTFOUND;
1568 
1569                 x_progress := 'LINE_AMOUNT_WITHIN_TOL:004';
1570                 log_message('LINE_AMOUNT_WITHIN_TOL','Line Id ',l_temp_po_line_id);
1571                 log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount ',l_old_lineamt);
1572 
1573                 EXIT WHEN (l_return_val = 'N');
1574 
1575 	        l_temp_total_line_amt_new := CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1576 
1577 	        x_progress := 'LINE_AMOUNT_WITHIN_TOL:005';
1578 	        log_message('LINE_AMOUNT_WITHIN_TOL','New line amount',l_temp_total_line_amt_new);
1579 	        END LOOP;
1580                 CLOSE  c_line_amt_old_cw_financing;
1581 
1582                END IF; -- ACTUALS , FINANCING
1583            END IF; -- po_style_type = NORMAL
1584 
1585             IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per, l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1586 	          l_return_val := 'N';
1587 	        END IF;
1588 	        x_progress:= 'LINE_AMOUNT_WITHIN_TOL:006';
1589 	        log_message('LINE_AMOUNT_WITHIN_TOL','Result',l_return_val);
1590 
1591 
1592  ELSIF(l_doc_type = 'RELEASE') THEN
1593 
1594                   OPEN c_line_amt_old_rel(l_po_header_id,l_po_release_id);
1595 	             LOOP
1596 	                     FETCH c_line_amt_old_rel INTO  l_old_lineamt,l_temp_po_line_id;
1597                 	     EXIT WHEN c_line_amt_old_rel%NOTFOUND;
1598                 	     x_progress := 'LINE_AMOUNT_WITHIN_TOL:010';
1599                              log_message('LINE_AMOUNT_WITHIN_TOL','Old Line Amount',l_old_lineamt);
1600 
1601                   l_temp_total_line_amt_new :=  CALCULATE_NEW_LINE_AMOUNT(l_po_header_id,l_po_release_id,l_temp_po_line_id,l_complex_po_style);
1602                   x_progress := 'LINE_AMOUNT_WITHIN_TOL:011';
1603                   log_message('LINE_AMOUNT_WITHIN_TOL','New Line Amount',l_temp_total_line_amt_new);
1604 
1605 
1606                  IF (NOT change_within_tol(l_old_lineamt, l_temp_total_line_amt_new, l_line_amt_max_incr_per,l_line_amt_max_dec_per,l_line_amt_max_incr_val,l_line_amt_max_dec_val)) THEN
1607 	        l_return_val := 'N';
1608 	        END IF;
1609                    END LOOP;
1610                    CLOSE c_line_amt_old_rel;
1611 
1612 ELSE
1613 	     l_return_val := 'Y';
1614 
1615 END IF;
1616 
1617 END IF; -- l_doc_type PO Or RELEASE
1618 
1619  END IF; -- l_po_header_id is not null
1620 	  -- set result value
1621 	  resultout := wf_engine.eng_completed|| ':' || l_return_val ;
1622 	  x_progress := 'LINE_AMOUNT_WITHIN_TOL:007';
1623           log_message('LINE_AMOUNT_WITHIN_TOL','Final result',resultout);
1624   END IF; -- IF ( funcmode = 'RUN' )
1625 
1626 EXCEPTION
1627    WHEN OTHERS THEN
1628    IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629              FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1630                             g_module_prefix,
1631                             x_progress || ':unexpected error' || Sqlerrm);
1632    END IF;
1633    wf_core.context('POSCHORD', 'LINE_AMOUNT_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
1634    raise;
1635 END LINE_AMOUNT_WITHIN_TOL;
1636 
1637 
1638 FUNCTION  CALCULATE_NEW_LINE_AMOUNT( p_po_header_id IN NUMBER, p_po_release_id IN NUMBER, p_po_line_id IN NUMBER,p_complex_po_style IN VARCHAR2)
1639 RETURN NUMBER
1640 IS
1641 -- Picks up new line amount for SPO
1642 CURSOR c_line_amt_new(p_po_header_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1643   SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
1644            (nvl(pcr.new_price,pl.unit_price) *
1645            (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1646            FROM    po_change_requests pcr, --line amount/price change
1647   	 	 po_change_requests pcr1, --shipment quantity change
1648   	 	 po_lines_all pl,
1649   	 	 po_line_locations_all pll
1650            WHERE   pl.po_header_id = p_po_header_id_csr
1651                    AND pl.po_line_id = p_temp_po_line_id_csr
1652   	 	   AND pll.po_line_id = pl.po_line_id
1653                    AND pcr1.document_header_id (+) = p_po_header_id_csr
1654                    AND pcr1.document_line_location_id(+) = pll.line_location_id
1655   	           AND pcr1.action_type(+) = 'MODIFICATION'
1656   	           AND pcr1.request_status(+) = req_status
1657   	           AND pcr1.request_level (+) = 'SHIPMENT'
1658   	           AND pcr1.initiator(+) = req_initiator
1659                    AND pcr.document_line_id(+) = pl.po_line_id
1660   	           AND pcr.action_type(+) = 'MODIFICATION'
1661   	           AND pcr.request_status(+) = req_status
1662   	           AND pcr.request_level (+) = 'LINE'
1663   	           AND pcr.initiator(+) = req_initiator
1664   	UNION ALL
1665     -- for splitted shipments
1666    SELECT   nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
1667             (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
1668            FROM    po_change_requests pcr, --line amount/price change
1669   		   po_change_requests pcr2, --for split shipments
1670   	 	   po_lines_all pl,
1671   	 	   po_line_locations_all pll
1672            WHERE   pl.po_header_id = p_po_header_id_csr
1673                    AND pl.po_line_id = p_temp_po_line_id_csr
1674   	 	   AND pll.po_line_id = pl.po_line_id
1675                    AND pcr2.document_header_id = p_po_header_id_csr
1676                    AND pcr2.parent_line_location_id = pll.line_location_id
1677   	           AND pcr2.action_type = 'MODIFICATION'
1678   	           AND pcr2.request_status in req_status
1679   	           AND pcr2.request_level  = 'SHIPMENT'
1680   	           AND pcr2.initiator = req_initiator
1681                    AND pcr.document_line_id(+) = pl.po_line_id
1682   	           AND pcr.action_type(+) = 'MODIFICATION'
1683   	           AND pcr.request_status in req_status
1684   	           AND pcr.request_level (+) = 'LINE'
1685 	           AND pcr.initiator(+) = req_initiator;
1686 
1687 
1688 -- Picks up new line amount for releses
1689 -- old_price is included for price breaks
1690 CURSOR c_line_amt_new_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1691    SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
1692 			  (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
1693 			   (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
1694      	FROM    po_change_requests pcr,
1695      		po_lines_all pl,
1696      		po_line_locations_all pll
1697      	WHERE  pll.po_header_id = p_po_header_id_csr
1698      	       AND pll.po_line_id = p_temp_po_line_id_csr
1699      	       AND pll.po_line_id = pl.po_line_id
1700      	       AND pll.po_release_id = p_po_release_id_csr
1701 	       AND pcr.po_release_id (+) = p_po_release_id_csr
1702      	       AND pcr.document_header_id(+) = p_po_header_id_csr
1703      	       --AND pcr.document_line_id = pl.po_line_id
1704      	       AND pcr.action_type(+) = 'MODIFICATION'
1705      	       AND pcr.request_status (+) = req_status
1706      	       AND pcr.request_level (+) = 'SHIPMENT'
1707      	       AND pcr.initiator(+) = req_initiator
1708      	       AND pcr.document_line_location_id(+) = pll.line_location_id
1709      	 UNION ALL
1710      SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
1711      	FROM    po_change_requests pcr2, -- for splitted shipments
1712      		po_lines_all pl,
1713      		po_line_locations_all pll
1714      	WHERE  pll.po_header_id = p_po_header_id_csr
1715      	       AND pll.po_line_id = p_temp_po_line_id_csr
1716      	       AND pll.po_line_id = pl.po_line_id
1717      	       AND pcr2.po_release_id = p_po_release_id_csr
1718      	       AND pcr2.document_header_id(+) = p_po_header_id_csr
1719      	       AND pcr2.document_line_id(+) = pl.po_line_id
1720      	       AND pcr2.action_type(+) = 'MODIFICATION'
1721      	       AND pcr2.request_status in req_status
1722      	       AND pcr2.request_level (+) = 'SHIPMENT'
1723      	       AND pcr2.initiator(+) = req_initiator
1724 	       AND pcr2.parent_line_location_id = pll.line_location_id;
1725 
1726 
1727 -- Picks up New Line Amount for complex Po( Actuals Case)
1728  CURSOR c_line_amt_new_cw_actuals(p_po_header_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1729    select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1730                                          (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1731                                         'AMOUNT',
1732                   DECODE(pll.payment_type, 'LUMPSUM',
1733                                          (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1734                                         'MILESTONE',
1735                                          (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1736                                         'RATE',
1737                                          (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1738  FROM
1739     po_change_requests pcr, --shipment quantity changes
1740     po_lines_all pl,
1741     po_line_locations_all pll
1742  WHERE
1743     pl.po_header_id = p_po_header_id_csr
1744     AND pl.po_line_id = p_temp_po_line_id_csr
1745     AND pll.po_line_id = pl.po_line_id
1746     AND pcr.document_header_id (+) = p_po_header_id_csr
1747     AND pcr.document_line_location_id(+) = pll.line_location_id
1748     AND pcr.action_type(+) = 'MODIFICATION'
1749     AND pcr.request_status(+)=req_status
1750     AND pcr.request_level (+) = 'SHIPMENT'
1751     AND pcr.initiator(+) = req_initiator
1752  UNION ALL
1753  -- for split shipment changes
1754  select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
1755                                          (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
1756                                         'AMOUNT',
1757                 DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
1758                                                                   (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1759                                                                   'MILESTONE',
1760                                                                   (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
1761                                                                   'RATE',
1762                                                                   (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
1763  FROM
1764     po_change_requests pcr, --shipment quantity changes
1765     po_lines_all pl,
1766     po_line_locations_all pll
1767  WHERE
1768     pl.po_header_id = p_po_header_id_csr
1769     AND pl.po_line_id = p_temp_po_line_id_csr
1770     AND pll.po_line_id = pl.po_line_id
1771     AND pcr.document_header_id  = p_po_header_id_csr
1772     AND pcr.parent_line_location_id = pll.line_location_id
1773     AND pcr.action_type = 'MODIFICATION'
1774     AND pcr.request_status(+)=req_status
1775     AND pcr.request_level  = 'SHIPMENT'
1776     AND pcr.initiator = req_initiator;
1777 
1778 
1779 -- Picks up New Line Amount for Complex POs( Financing Case)
1780 CURSOR c_line_amt_new_cw_financing(p_po_header_id_csr IN NUMBER, p_temp_po_line_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
1781   SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
1782                                           'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
1783   FROM
1784       po_lines_all pl,
1785       po_change_requests pcr
1786   WHERE
1787       pl.po_header_id = p_po_header_id_csr
1788       AND pl.po_line_id = p_temp_po_line_id_csr
1789       AND pcr.document_header_id (+) = p_po_header_id_csr
1790       AND pcr.document_line_id(+) = pl.po_line_id
1791       AND pcr.action_type(+) = 'MODIFICATION'
1792       AND pcr.request_status(+)=req_status
1793       AND pcr.request_level (+) = 'LINE'
1794       AND pcr.initiator(+) = req_initiator;
1795 
1796 l_new_line_amt          NUMBER;
1797 l_total_new_line_amt    NUMBER := 0;
1798 x_progress             VARCHAR2(1000);
1799 req_status              po_change_requests.request_status%TYPE;
1800 req_initiator           po_change_requests.initiator%TYPE;
1801 
1802 BEGIN
1803 
1804   /* Code Changes for Bug - 11794109 Start */
1805   BEGIN
1806 
1807     IF(p_po_release_id is null) THEN
1808         select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
1809         where document_header_id=p_po_header_id AND document_type = 'PO'
1810               and change_active_flag='Y'
1811               and rownum=1;
1812     ELSE
1813         select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
1814         where document_header_id=p_po_header_id AND document_type = 'RELEASE'
1815               and po_release_id = p_po_release_id
1816               and change_active_flag='Y'
1817               and rownum=1;
1818     END IF;
1819 
1820   EXCEPTION
1821     WHEN NO_DATA_FOUND THEN
1822          return   l_total_new_line_amt;
1823   END;
1824   /* Code Changes for Bug - 11794109 End */
1825 
1826     x_progress := 'CALCULATE_NEW_LINE_AMOUNT:000';
1827     IF(p_po_release_id is null) THEN
1828      IF(p_complex_po_style = 'ACTUALS')  THEN
1829         OPEN c_line_amt_new_cw_actuals(p_po_header_id,p_po_line_id,req_status,req_initiator);
1830 	 LOOP
1831 	 FETCH c_line_amt_new_cw_actuals INTO  l_new_line_amt;
1832 	 EXIT WHEN c_line_amt_new_cw_actuals%NOTFOUND;
1833 	     l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1834 	 END LOOP;
1835          x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1836          log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1837         CLOSE   c_line_amt_new_cw_actuals;
1838      ELSIF(p_complex_po_style = 'FINANCING') THEN
1839         OPEN c_line_amt_new_cw_financing(p_po_header_id,p_po_line_id,req_status,req_initiator);
1840 	 LOOP
1841 	 FETCH c_line_amt_new_cw_financing INTO  l_new_line_amt;
1842 	 EXIT WHEN c_line_amt_new_cw_financing%NOTFOUND;
1843 	     l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1844 	 END LOOP;
1845          x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1846          log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1847         CLOSE   c_line_amt_new_cw_financing;
1848 
1849      ELSE
1850 
1851         OPEN c_line_amt_new(p_po_header_id,p_po_line_id,req_status,req_initiator);
1852 	 LOOP
1853 	 FETCH c_line_amt_new INTO  l_new_line_amt;
1854 	 EXIT WHEN c_line_amt_new%NOTFOUND;
1855 	     l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1856 	 END LOOP;
1857          x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1858          log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1859         CLOSE   c_line_amt_new;
1860      END IF;
1861     ELSIF( p_po_release_id is not null) THEN
1862 
1863        OPEN c_line_amt_new_rel(p_po_header_id,p_po_release_id,p_po_line_id,req_status,req_initiator);
1864        	 LOOP
1865        	 FETCH c_line_amt_new_rel INTO  l_new_line_amt;
1866        	 EXIT WHEN c_line_amt_new_rel%NOTFOUND;
1867        	     l_total_new_line_amt := l_total_new_line_amt + l_new_line_amt;
1868        	 END LOOP;
1869          x_progress:= 'LINE_AMOUNT_WITHIN_TOL:005';
1870          log_message('LINE_AMOUNT_WITHIN_TOL','New AMount ',l_total_new_line_amt);
1871         CLOSE   c_line_amt_new_rel;
1872     END IF;
1873 
1874    return   l_total_new_line_amt;
1875 
1876   EXCEPTION
1877     WHEN OTHERS THEN
1878       IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1879                 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1880                                g_module_prefix,
1881                                x_progress || ':unexpected error' || Sqlerrm);
1882       END IF;
1883   raise;
1884  END CALCULATE_NEW_LINE_AMOUNT;
1885 
1886 
1887 PROCEDURE SHIP_AMOUNT_WITHIN_TOL( itemtype        IN VARCHAR2,
1888  	                          itemkey         IN VARCHAR2,
1889  	                          actid           IN NUMBER,
1890  	                          funcmode        IN VARCHAR2,
1891                                   resultout       OUT NOCOPY VARCHAR2)
1892 IS
1893 
1894 -- Calculates the old shipment amount
1895  CURSOR c_old_ship_amt(p_po_header_id_csr IN NUMBER) IS
1896         select sum(decode(pol.matching_basis,'AMOUNT',(nvl(pll.amount,0) - nvl(pll.amount_cancelled,0)),(pol.unit_price *(pll.quantity-nvl(pll.quantity_cancelled,0))))),pll.line_location_id
1897 	from    po_line_locations_all pll,
1898 	        po_lines_all pol
1899 	where pll.po_header_id = p_po_header_id_csr
1900 	      AND pll.po_line_id = pol.po_line_id
1901 	      GROUP BY pll.line_location_id;
1902 
1903 -- Calculate Old Shipment amount for the BPA Release
1904  CURSOR c_old_ship_amt_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) IS
1905         SELECT  sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0))))),
1906                 pll.line_location_id
1907 	FROM 	po_lines_all pl,
1908 	        po_line_locations_all pll
1909 	WHERE   pll.po_release_id = p_po_release_id_csr
1910                 AND pll.po_header_id = p_po_header_id_csr
1911 		AND pll.po_line_id = pl.po_line_id
1912 		GROUP BY pll.line_location_id;
1913 
1914 -- Calculate Old Shipment amount for the Complex work POs ( Actuals And Financing Case)
1915  CURSOR c_old_ship_amt_cw(p_po_header_id_csr IN NUMBER) IS
1916         SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
1917                                           (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
1918                                             'AMOUNT',
1919                    DECODE(pll.payment_type, 'LUMPSUM',
1920                                             (pll.amount - NVL(pll.amount_cancelled,0)),
1921                                             'MILESTONE',
1922                                      	    (pll.amount - NVL(pll.amount_cancelled,0)),
1923                                             'RATE',
1924                                             (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.line_location_id
1925          FROM 	 po_lines_all pl,
1926                  po_line_locations_all pll
1927          WHERE   pl.po_header_id = p_po_header_id_csr
1928 	         AND pll.po_line_id = pl.po_line_id
1929                  GROUP BY pll.line_location_id;
1930 
1931 
1932 
1933 
1934 
1935 
1936   l_po_header_id          po_change_requests.document_header_id%TYPE;
1937   l_po_release_id         po_change_requests.po_release_id%TYPE;
1938   l_po_line_id            po_change_requests.document_line_id%TYPE;
1939   l_change_group_id       po_change_requests.change_request_group_id%type;
1940   l_matching_basis        po_lines_all.matching_basis%TYPE;
1941   l_old_shipamt           po_change_requests.old_amount%TYPE;
1942   l_new_shipamt           po_change_requests.new_amount%TYPE;
1943   l_old_ship_amt_rel      NUMBER;
1944   l_new_ship_amt_rel      NUMBER;
1945   l_old_ship_amt_cw       NUMBER;
1946   l_new_ship_amt_cw       NUMBER;
1947   l_return_val            VARCHAR2(1) :='Y';
1948   l_shipamt_max_incr_per  NUMBER;
1949   l_shipamt_max_dec_per   NUMBER;
1950   l_shipamt_max_incr_val  NUMBER;
1951   l_shipamt_max_dec_val   NUMBER;
1952   l_old_ship_amt          NUMBER;
1953   l_new_ship_amt          NUMBER := 0;
1954   l_new_ship_amt_no_change NUMBER := 0;
1955   l_line_location_id      NUMBER;
1956   l_po_style_type         VARCHAR2(10);
1957   l_doc_type              VARCHAR2(10);
1958   x_progress              VARCHAR2(1000);
1959 
1960 
1961   BEGIN
1962 
1963   IF ( funcmode = 'RUN' ) THEN
1964            x_progress := 'SHIP_AMOUNT_WITHIN_TOL:000';
1965 
1966 	   l_po_header_id       := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1967 					                        itemkey  => itemkey,
1968 	                                                        aname    => 'PO_HEADER_ID');
1969 
1970 	   l_po_release_id      := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1971 					                        itemkey  => itemkey,
1972 	                                                        aname    => 'PO_RELEASE_ID');
1973 
1974 	   l_change_group_id    := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1975 					                        itemkey  => itemkey,
1976 	                                                        aname    => 'CHANGE_REQUEST_GROUP_ID');
1977 
1978 	   l_po_style_type      := wf_engine.GetItemAttrText   (itemtype => itemtype,
1979       						                itemkey  => itemkey,
1980       		                                                aname    => 'PO_STYLE_TYPE');
1981 
1982            l_doc_type           := wf_engine.GetItemAttrText   (itemtype => itemtype,
1983       						                itemkey  => itemkey,
1984       		                                                aname    => 'DOCUMENT_TYPE');
1985       	   x_progress := 'SHIP_AMOUNT_WITHIN_TOL:001';
1986 
1987    IF (l_change_group_id IS NOT NULL) THEN
1988 	        -- check for the DOC types (applicable for  PO LINE AMOUNT( Line level)
1989 	        -- if other doc types return true and exit
1990 	IF(l_doc_type = 'PO' OR l_doc_type = 'RELEASE') THEN
1991 
1992 	   IF (l_po_style_type='NORMAL') THEN
1993 
1994  	 	-- get shipment quantity tolerances
1995 	  	l_shipamt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1996       						                       itemkey  => itemkey,
1997       		                                                       aname    => 'SHIP_AMOUNT_INCR_PER');
1998 
1999 	  	l_shipamt_max_dec_per  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2000       						                       itemkey  => itemkey,
2001       		                                                       aname    => 'SHIP_AMOUNT_DEC_PER');
2002 
2003 	  	x_progress := 'SHIP_AMOUNT_WITHIN_TOL:002';
2004 	  	log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr Percentage',l_shipamt_max_incr_per ||', '|| l_shipamt_max_dec_per);
2005 	        -- get shipment quantity tolerances
2006 	  	l_shipamt_max_incr_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2007       						                       itemkey  => itemkey,
2008       		                                                       aname    => 'SHIP_AMOUNT_INCR_VAL');
2009 
2010 	  	l_shipamt_max_dec_val  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2011       						                       itemkey  => itemkey,
2012       		                                                       aname    => 'SHIP_AMOUNT_DEC_VAL');
2013 
2014       		x_progress := 'SHIP_AMOUNT_WITHIN_TOL:003';
2015 	  	log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr value', l_shipamt_max_incr_val ||', '|| l_shipamt_max_dec_val);
2016 
2017 	   ELSIF (l_po_style_type='COMPLEX') THEN
2018 	        -- Complex Work PO Chack  -- COMPLEX WORK
2019 	        -- get shipment quantity tolerances
2020 		l_shipamt_max_incr_per := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2021 		      						       itemkey  => itemkey,
2022 		      		                                       aname    => 'PAY_AMOUNT_INCR_PER');
2023 
2024 		l_shipamt_max_dec_per  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2025 		      						       itemkey  => itemkey,
2026 		      		                                       aname    => 'PAY_AMOUNT_DEC_PER');
2027 
2028 		x_progress := 'SHIP_AMOUNT_WITHIN_TOL:004';
2029 	  	log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr Percentage',l_shipamt_max_incr_per ||', '|| l_shipamt_max_dec_per);
2030 	        -- get shipment quantity tolerances
2031 		l_shipamt_max_incr_val := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2032 		    				                       itemkey  => itemkey,
2033 		      		                                       aname    => 'PAY_AMOUNT_INCR_VAL');
2034 
2035                 l_shipamt_max_dec_val  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2036        						                       itemkey  => itemkey,
2037 		      		                                       aname    => 'PAY_AMOUNT_DEC_VAL');
2038 	        x_progress := 'SHIP_AMOUNT_WITHIN_TOL:005';
2039 	  	log_message('SHIP_AMOUNT_WITHIN_TOL','Ship amount Incr & decr value', l_shipamt_max_incr_val ||', '|| l_shipamt_max_dec_val);
2040 	   END IF;  --l_po_style_type='NORMAL'/'COMPLEX'
2041 
2042 
2043 
2044 	   IF(l_doc_type = 'PO' and l_po_style_type='NORMAL') THEN
2045 	       OPEN c_old_ship_amt(l_po_header_id);
2046 	             LOOP
2047 	      	     FETCH c_old_ship_amt
2048 	             INTO l_old_ship_amt,l_line_location_id;
2049 	             x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2050 	             log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2051 	             EXIT WHEN (l_return_val='N');
2052                      EXIT WHEN c_old_ship_amt%NOTFOUND;
2053 
2054 	             l_new_ship_amt := CALCULATE_NEW_SHIP_AMOUNT(l_po_header_id,l_po_release_id,l_line_location_id,'N',l_po_style_type,null);
2055 
2056 	             x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2057 	             IF (NOT change_within_tol(l_old_ship_amt, l_new_ship_amt, l_shipamt_max_incr_per, l_shipamt_max_dec_per,l_shipamt_max_incr_val   , l_shipamt_max_dec_val)) THEN
2058                          l_return_val := 'N';
2059                      END IF;
2060                      x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2061                      log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2062                      END LOOP;
2063 	        CLOSE  c_old_ship_amt;
2064 
2065 	        IF (l_return_val = 'Y') THEN
2066                     wf_engine.SetItemAttrText( itemtype  => itemtype,
2067                                                itemkey   => itemkey,
2068                                                aname     => 'NOTIF_USAGE',
2069                                                avalue     => 'BUYER_AUTO_FYI');
2070                 END IF;
2071            ELSIF ((l_doc_type = 'RELEASE') and (l_po_style_type='NORMAL')) THEN
2072                 OPEN c_old_ship_amt_rel(l_po_header_id,l_po_release_id);
2073 	      	     LOOP
2074 	      	     FETCH c_old_ship_amt_rel
2075 	      	     INTO l_old_ship_amt,l_line_location_id;
2076 	      	     x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2077 	      	     log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2078 	      	     EXIT WHEN (l_return_val='N');
2079 	             EXIT WHEN c_old_ship_amt_rel%NOTFOUND;
2080 
2081 	      	     l_new_ship_amt := CALCULATE_NEW_SHIP_AMOUNT(l_po_header_id,l_po_release_id,l_line_location_id,'N',l_po_style_type,null);
2082 
2083 	      	     x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2084 	             IF (NOT change_within_tol(l_old_ship_amt, l_new_ship_amt, l_shipamt_max_incr_per, l_shipamt_max_dec_per,l_shipamt_max_incr_val   , l_shipamt_max_dec_val)) THEN
2085 	                 l_return_val := 'N';
2086 	             END IF;
2087 	             x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2088 	             log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2089 	             END LOOP;
2090 	         CLOSE  c_old_ship_amt_rel;
2091 
2092               IF (l_return_val = 'Y') THEN
2093 	         wf_engine.SetItemAttrText( itemtype  => itemtype,
2094 	                                    itemkey   => itemkey,
2095 	                                    aname     => 'NOTIF_USAGE',
2096 	                                    avalue     => 'BUYER_AUTO_FYI');
2097               END IF;
2098          ELSIF (l_po_style_type='COMPLEX') THEN
2099 
2100 	       OPEN c_old_ship_amt_cw(l_po_header_id);
2101 	             LOOP
2102 	      	     FETCH c_old_ship_amt_cw
2103 	             INTO l_old_ship_amt,l_line_location_id;
2104 	             x_progress := 'SHIP_AMOUNT_WITHIN_TOL:006';
2105 	             log_message('SHIP_AMOUNT_WITHIN_TOL','Old Ship Amount',l_old_ship_amt);
2106 	             EXIT WHEN (l_return_val='N');
2107                      EXIT WHEN c_old_ship_amt_cw%NOTFOUND;
2108 
2109 	             l_new_ship_amt := CALCULATE_NEW_SHIP_AMOUNT(l_po_header_id,l_po_release_id,l_line_location_id,'N',l_po_style_type,null);
2110 
2111 	             x_progress := 'SHIP_AMOUNT_WITHIN_TOL:007';
2112 	             IF (NOT change_within_tol(l_old_ship_amt, l_new_ship_amt, l_shipamt_max_incr_per, l_shipamt_max_dec_per,l_shipamt_max_incr_val   , l_shipamt_max_dec_val)) THEN
2113                          l_return_val := 'N';
2114                      END IF;
2115                      x_progress := 'SHIP_AMOUNT_WITHIN_TOL:009';
2116                      log_message('SHIP_AMOUNT_WITHIN_TOL','Result',l_return_val);
2117                      END LOOP;
2118 	        CLOSE  c_old_ship_amt_cw;
2119 
2120 	        IF (l_return_val = 'Y') THEN
2121                     wf_engine.SetItemAttrText( itemtype  => itemtype,
2122                                                itemkey   => itemkey,
2123                                                aname     => 'NOTIF_USAGE',
2124                                                avalue     => 'BUYER_AUTO_FYI');
2125                 END IF;
2126 
2127          END IF; -- PO Or RELEASE or complex work POs
2128       END IF;  -- l_doc_type PO or RELEASE
2129         -- set result value
2130       resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2131    END IF; -- change_group_id is not null
2132         x_progress :=  'SHIP_AMOUNT_WITHIN_TOL:016';
2133         log_message('SHIP_AMOUNT_WITHIN_TOL','Result',resultout);
2134  END IF; -- IF ( funcmode = 'RUN' )
2135 EXCEPTION
2136   WHEN OTHERS THEN
2137   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2138             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2139                            g_module_prefix,
2140                            x_progress || ':unexpected error' || Sqlerrm);
2141   END IF;
2142   wf_core.context('POSCHORD', 'SHIP_AMOUNT_WITHIN_TOL', itemtype, itemkey, to_char(actid),funcmode);
2143   raise;
2144 END SHIP_AMOUNT_WITHIN_TOL;
2145 
2146 
2147 FUNCTION CALCULATE_NEW_SHIP_AMOUNT( p_po_header_id         IN NUMBER,
2148                                     p_po_release_id        IN NUMBER,
2149                                     p_line_location_id     IN NUMBER,
2150                                     p_split_flag           IN VARCHAR2,
2151 				    p_po_style_type        IN VARCHAR2,
2152                                     p_po_shipment_num      IN NUMBER)
2153 RETURN NUMBER
2154 IS
2155 
2156 --- picks up new shipment amount for SPO
2157 CURSOR c_new_shipment_amount (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
2158     SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
2159             (nvl(pcr.new_price,pl.unit_price) *
2160             (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2161             FROM    po_change_requests pcr, --line amount/price change
2162    	 	 po_change_requests pcr1, --shipment quantity change
2163    	 	 po_lines_all pl,
2164    	 	 po_line_locations_all pll
2165             WHERE   pl.po_header_id = p_po_header_id_csr
2166                  AND pll.line_location_id = p_line_location_id_csr
2167    	 	 AND pll.po_line_id = pl.po_line_id
2168                     AND pcr1.document_header_id (+) = p_po_header_id_csr
2169                     AND pcr1.document_line_location_id(+) = pll.line_location_id
2170    	         AND pcr1.action_type(+) = 'MODIFICATION'
2171    	         AND pcr1.request_status(+) = req_status
2172    	         AND pcr1.request_level (+) = 'SHIPMENT'
2173    	         AND pcr1.initiator(+) = req_initiator
2174                     AND pcr.document_line_id(+) = pl.po_line_id
2175    	         AND pcr.action_type(+) = 'MODIFICATION'
2176    	         AND pcr.request_status(+) = req_status
2177    	         AND pcr.request_level (+) = 'LINE'
2178    	         AND pcr.initiator(+) =  req_initiator;
2179 
2180 CURSOR c_new_shipment_amount_split (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR,p_po_shipment_num IN NUMBER) IS
2181      -- for splitted shipments
2182     SELECT   nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
2183              (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
2184             FROM    po_change_requests pcr, --line amount/price change
2185    		 po_change_requests pcr2, --for split shipments
2186    	 	 po_lines_all pl,
2187    	 	 po_line_locations_all pll
2188             WHERE   pl.po_header_id = p_po_header_id_csr
2189                  AND pll.line_location_id = p_line_location_id_csr
2190    	 	 AND pll.po_line_id = pl.po_line_id
2191                     AND pcr2.document_header_id = p_po_header_id_csr
2192                     AND pcr2.parent_line_location_id = pll.line_location_id
2193    	         AND pcr2.action_type = 'MODIFICATION'
2194    	         AND pcr2.request_status = req_status
2195    	         AND pcr2.request_level  = 'SHIPMENT'
2196    	         AND pcr2.initiator =  req_initiator
2197                  AND pcr2.document_shipment_number = p_po_shipment_num
2198                     AND pcr.document_line_id(+) = pl.po_line_id
2199    	         AND pcr.action_type(+) = 'MODIFICATION'
2200    	         AND pcr.request_status(+) =  req_status
2201    	         AND pcr.request_level (+) =  'LINE'
2202 	         AND pcr.initiator(+) =  req_initiator;
2203 
2204 --- picks up new shipment amount for releses
2205 -- old_price is included for price breaks
2206 CURSOR c_new_shipment_amount_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER, p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR) IS
2207    SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
2208 			  (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
2209 			   (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
2210     	FROM    po_change_requests pcr,
2211     		po_lines_all pl,
2212     		po_line_locations_all pll
2213     	WHERE  pll.po_header_id = p_po_header_id_csr
2214     	       AND pll.line_location_id = p_line_location_id_csr
2215 	       AND pll.po_release_id = p_po_release_id_csr
2216     	       AND pll.po_line_id = pl.po_line_id
2217     	      -- AND pcr.po_release_id = p_po_release_id_csr
2218     	       AND pcr.document_header_id(+) = p_po_header_id_csr
2219     	       AND pcr.document_line_id = pl.po_line_id
2220     	       AND pcr.action_type(+) = 'MODIFICATION'
2221     	       AND pcr.request_status(+) = req_status
2222     	       AND pcr.request_level (+) = 'SHIPMENT'
2223     	       AND pcr.initiator(+) = req_initiator
2224     	       AND pcr.document_line_location_id(+) = pll.line_location_id;
2225 
2226 CURSOR c_new_ship_amt_rel_split(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER, p_line_location_id_csr IN NUMBER,req_status IN VARCHAR,req_initiator IN VARCHAR,p_po_shipment_num IN NUMBER) IS
2227     SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
2228     	FROM    po_change_requests pcr2, -- for splitted shipments
2229     		po_lines_all pl,
2230     		po_line_locations_all pll
2231     	WHERE  pll.po_header_id = p_po_header_id_csr
2232     	       AND pll.line_location_id = p_line_location_id_csr
2233     	       AND pll.po_line_id = pl.po_line_id
2234     	       AND pcr2.po_release_id = p_po_release_id_csr
2235     	       AND pcr2.document_header_id(+) = p_po_header_id_csr
2236     	       AND pcr2.document_line_id(+) = pl.po_line_id
2237     	       AND pcr2.action_type(+) = 'MODIFICATION'
2238     	       AND pcr2.request_status(+)=  req_status
2239     	       AND pcr2.request_level (+) = 'SHIPMENT'
2240     	       AND pcr2.initiator(+) = req_initiator
2241     	       AND pcr2.parent_line_location_id = pll.line_location_id
2242                AND pcr2.document_shipment_number = p_po_shipment_num;
2243 
2244 -- Picks up New Shipment Amount for complex work Po's (Actuals And Financing Case)
2245 CURSOR c_new_shipment_amount_cw (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER) IS
2246     select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
2247                                          (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
2248                                         'AMOUNT',
2249                    DECODE(pll.payment_type, 'LUMPSUM',
2250                                          (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
2251                                         'MILESTONE',
2252                                          (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
2253                                         'RATE',
2254                                          (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2255     FROM
2256         po_change_requests pcr, --shipment quantity changes
2257         po_lines_all pl,
2258         po_line_locations_all pll
2259     WHERE
2260         pl.po_header_id = p_po_header_id_csr
2261         AND pll.line_location_id = p_line_location_id_csr
2262         AND pll.po_line_id = pl.po_line_id
2263         AND pcr.document_header_id (+) = p_po_header_id_csr
2264         AND pcr.document_line_location_id(+) = pll.line_location_id
2265         AND pcr.action_type(+) = 'MODIFICATION'
2266         AND pcr.request_status(+) = 'PENDING'
2267         AND pcr.request_level (+) = 'SHIPMENT'
2268         AND pcr.initiator(+) = 'SUPPLIER';
2269 
2270 -- Picks up New Shipment Amount for complex work Po's (Actuals And Financing Case) for split cases
2271 CURSOR c_new_shipment_amount_split_cw (p_po_header_id_csr IN NUMBER,p_line_location_id_csr IN NUMBER,p_po_shipment_num IN NUMBER) IS
2272     select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
2273                                             (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
2274                                             'AMOUNT',
2275                    DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
2276                                                                        (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
2277                                                                        'MILESTONE',
2278                                                                        (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
2279                                                                        'RATE',
2280                                                                        (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
2281     FROM
2282        po_change_requests pcr, --shipment quantity changes
2283        po_lines_all pl,
2284        po_line_locations_all pll
2285     WHERE
2286        pl.po_header_id = p_po_header_id_csr
2287        AND pll.line_location_id = p_line_location_id_csr
2288        AND pll.po_line_id = pl.po_line_id
2289        AND pcr.document_header_id  = p_po_header_id_csr
2290        AND pcr.parent_line_location_id = pll.line_location_id
2291        AND pcr.action_type = 'MODIFICATION'
2292        AND pcr.request_status = 'PENDING'
2293        AND pcr.request_level  = 'SHIPMENT'
2294        AND pcr.initiator = 'SUPPLIER'
2295        AND pcr.document_shipment_number = p_po_shipment_num;
2296 
2297 l_new_ship_amt          NUMBER;
2298 x_progress             VARCHAR2(1000);
2299 req_status              po_change_requests.request_status%TYPE;
2300 req_initiator           po_change_requests.initiator%TYPE;
2301 
2302 BEGIN
2303 
2304   /* Code Changes for Bug - 11794109 Start */
2305   BEGIN
2306 
2307     IF(p_po_release_id is null) THEN
2308         select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
2309         where document_header_id=p_po_header_id AND document_type = 'PO'
2310               and change_active_flag='Y'
2311               and rownum=1;
2312     ELSE
2313         select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
2314         where document_header_id=p_po_header_id AND document_type = 'RELEASE'
2315               and po_release_id = p_po_release_id
2316               and change_active_flag='Y'
2317               and rownum=1;
2318     END IF;
2319 
2320   EXCEPTION
2321     WHEN NO_DATA_FOUND THEN
2322          l_new_ship_amt := 0;
2323          return   l_new_ship_amt;
2324   END;
2325   /* Code Changes for Bug - 11794109 End */
2326 
2327 
2328    IF(p_po_release_id is null) THEN
2329      IF(p_split_flag = 'N') THEN
2330        IF(p_po_style_type = 'NORMAL') THEN
2331           OPEN c_new_shipment_amount(p_po_header_id,p_line_location_id,req_status,req_initiator);
2332 	   LOOP
2333 	   FETCH c_new_shipment_amount INTO  l_new_ship_amt;
2334 	   EXIT WHEN c_new_shipment_amount%NOTFOUND;
2335 	   END LOOP;
2336           CLOSE   c_new_shipment_amount;
2337        ELSIF(p_po_style_type = 'COMPLEX') THEN
2338           OPEN c_new_shipment_amount_cw(p_po_header_id,p_line_location_id);
2339 	   LOOP
2340 	   FETCH c_new_shipment_amount_cw INTO  l_new_ship_amt;
2341 	   EXIT WHEN c_new_shipment_amount_cw%NOTFOUND;
2342 	   END LOOP;
2343           CLOSE   c_new_shipment_amount_cw;
2344        END IF;
2345      ELSIF(p_split_flag = 'Y') THEN
2346        IF(p_po_style_type = 'NORMAL') THEN
2347          OPEN c_new_shipment_amount_split(p_po_header_id,p_line_location_id,req_status,req_initiator,p_po_shipment_num);
2348           LOOP
2349           FETCH c_new_shipment_amount_split INTO  l_new_ship_amt;
2350           EXIT WHEN c_new_shipment_amount_split%NOTFOUND;
2351           END LOOP;
2352          CLOSE   c_new_shipment_amount_split;
2353        ELSIF(p_po_style_type = 'COMPLEX') THEN
2354           OPEN c_new_shipment_amount_split_cw(p_po_header_id,p_line_location_id,p_po_shipment_num);
2355           LOOP
2356           FETCH c_new_shipment_amount_split_cw INTO  l_new_ship_amt;
2357           EXIT WHEN c_new_shipment_amount_split_cw%NOTFOUND;
2358           END LOOP;
2359          CLOSE   c_new_shipment_amount_split_cw;
2360        END IF;
2361      END IF;
2362    ELSIF( p_po_release_id is not null) THEN
2363      IF(p_split_flag = 'N') THEN
2364        OPEN c_new_shipment_amount_rel(p_po_header_id,p_po_release_id,p_line_location_id,req_status,req_initiator);
2365        	 LOOP
2366        	 FETCH c_new_shipment_amount_rel INTO  l_new_ship_amt;
2367        	 EXIT WHEN c_new_shipment_amount_rel%NOTFOUND;
2368        	 END LOOP;
2369        CLOSE   c_new_shipment_amount_rel;
2370      ELSIF (p_split_flag = 'Y') THEN
2371         OPEN c_new_ship_amt_rel_split(p_po_header_id,p_po_release_id,p_line_location_id,req_status,req_initiator,p_po_shipment_num);
2372          LOOP
2373          FETCH c_new_ship_amt_rel_split INTO  l_new_ship_amt;
2374          EXIT WHEN c_new_ship_amt_rel_split%NOTFOUND;
2375          END LOOP;
2376         CLOSE   c_new_ship_amt_rel_split;
2377      END IF;
2378     END IF;
2379 
2380    return   l_new_ship_amt;
2381 
2382   EXCEPTION
2383     WHEN OTHERS THEN
2384       IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2385                 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2386                                g_module_prefix,
2387                                x_progress || ':unexpected error' || Sqlerrm);
2388       END IF;
2389   raise;
2390 END CALCULATE_NEW_SHIP_AMOUNT;
2391 
2392 FUNCTION CHANGE_WITHIN_TOL( p_oldValue         IN NUMBER,
2393 	                    p_newValue         IN NUMBER,
2394 	                    p_maxIncrement_per IN NUMBER,
2395 	                    p_maxDecrement_per IN NUMBER,
2396 	                    p_maxIncrement_val IN NUMBER,
2397 	                    p_maxDecrement_val IN NUMBER)
2398 RETURN boolean
2399 
2400 IS
2401 
2402   l_changePercent NUMBER;
2403   l_changeValue   NUMBER;
2404   x_progress varchar2(1000);
2405 
2406 BEGIN
2407 
2408    x_progress := 'CHANGE_WITHIN_TOL:000';
2409    -- First Check whether Buyer has Set Tolerance Values or Not( If Not Set Then No Auto-Approval)
2410    /* bug 9884700 , Consider p_oldValue=0 condition also */
2411 IF((p_oldValue >= 0) AND (p_newValue > 0)) THEN
2412   IF((p_newValue > p_oldValue) AND (nvl(p_maxIncrement_per,0) = 0 AND nvl(p_maxIncrement_val,0) = 0)) THEN
2413    return FALSE;
2414   ELSIF((p_newValue < p_oldValue) AND (nvl(p_maxDecrement_per,0) = 0 AND nvl(p_maxDecrement_val,0) = 0)) THEN
2415    return FALSE;
2416   END IF;
2417 END IF;
2418   x_progress := 'CHANGE_WITHIN_TOL:001';
2419   IF (p_oldValue <> p_newValue) THEN
2420       IF (p_oldValue >= 0 AND p_newValue > 0) THEN
2421       --- Checking for the Value change
2422          l_changeValue := abs(p_oldValue - p_newValue);
2423 	 x_progress := 'CHANGE_WITHIN_TOL:002';
2424 	 -- value has increased
2425           IF (p_maxIncrement_val <> 0 AND p_oldValue < p_newValue) THEN
2426 	      IF(l_changeValue > p_maxIncrement_val) THEN
2427 		  return FALSE;
2428 	      END IF;
2429 	  END IF;
2430 	 -- value has decreased
2431           IF (p_maxDecrement_val <> 0 AND p_oldValue > p_newValue) THEN
2432 	      IF(l_changeValue > p_maxDecrement_val) THEN
2433 		  return FALSE;
2434 	      END IF;
2435 	  END IF;
2436        --- Checking for the percentage change
2437        /* bug 9884700 */
2438           if p_oldValue=0 then
2439 	  l_changePercent:=p_newValue*100;
2440           else
2441 	  l_changePercent := ((p_oldValue - p_newValue)/p_oldValue)*100;
2442 	  end if;
2443 	  x_progress := 'CHANGE_WITHIN_TOL:003';
2444 	  -- value has increased
2445 	  IF (p_maxIncrement_per <> 0 AND p_oldValue < p_newValue) THEN
2446 	      IF((abs(l_changePercent)) > p_maxIncrement_per) THEN
2447 	      return FALSE;
2448 	      END IF;
2449 	  END IF;
2450 	 -- value has decreased
2451           IF (p_maxDecrement_per <> 0 AND p_oldValue > p_newValue) THEN
2452 	      IF(l_changePercent > p_maxDecrement_per) THEN
2453 	         return FALSE;
2454 	      END IF;
2455 	  END IF;
2456        END IF;
2457  END IF;
2458 
2459  return TRUE;
2460 
2461 EXCEPTION
2462   WHEN OTHERS THEN
2463     IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2464               FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2465                              g_module_prefix,
2466                              x_progress || ':unexpected error' || Sqlerrm);
2467     END IF;
2468 raise;
2469 
2470 END CHANGE_WITHIN_TOL;
2471 ------------------------------------------------------------------------------
2472 FUNCTION CHANGE_WITHIN_TOL_DATE(p_oldValue     IN DATE,
2473 		                p_newValue     IN DATE,
2474                                 p_maxIncrement IN NUMBER,
2475 		                p_maxDecrement IN NUMBER)
2476 RETURN boolean
2477 
2478 IS
2479 x_progress varchar2(1000);
2480 BEGIN
2481 
2482  x_progress := 'CHANGE_WITHIN_TOL_DATE:000';
2483  -- First Check whether Buyer has Set Tolerance Values or Not( If Not Set Then No Auto-Approval)
2484  IF( (p_newValue > p_oldValue) AND (nvl(p_maxIncrement,0) = 0)) THEN
2485    return FALSE;
2486  ELSIF( (p_newValue < p_oldValue) AND(nvl(p_maxDecrement,0) = 0)) THEN
2487    return FALSE;
2488  END IF;
2489 
2490  x_progress := 'CHANGE_WITHIN_TOL_DATE:001';
2491 
2492   IF(p_oldValue <> p_newValue) THEN
2493 
2494 		     -- check for upper tol
2495 			 IF (p_maxIncrement <> 0 AND p_oldValue < p_newValue)THEN
2496 			    IF(p_newValue - p_maxIncrement > p_oldValue) THEN
2497 			      return FALSE;
2498 			    END IF;
2499 		        END IF;
2500 
2501 			 -- check for lower tol
2502 			 IF(p_maxDecrement <> 0 AND p_oldValue > p_newValue) THEN
2503 			    IF(p_newValue + p_maxDecrement < p_oldValue) THEN
2504 			      return FALSE;
2505 			    END IF;
2506 			 END IF;
2507   END IF;
2508 
2509   x_progress := 'CHANGE_WITHIN_TOL_DATE:002';
2510 
2511 		  return TRUE;
2512 EXCEPTION
2513  WHEN OTHERS THEN
2514   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2515               FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2516                              g_module_prefix,
2517                              x_progress || ':unexpected error' || Sqlerrm);
2518     END IF;
2519 raise;
2520 
2521 
2522 END CHANGE_WITHIN_TOL_DATE;
2523 
2524 PROCEDURE ROUTE_TO_REQUESTER( itemtype        IN VARCHAR2,
2525  	                      itemkey         IN VARCHAR2,
2526  	                      actid           IN NUMBER,
2527  	                      funcmode        IN VARCHAR2,
2528                               resultout       OUT NOCOPY VARCHAR2)
2529 
2530 IS
2531  l_return_val            VARCHAR2(1) ;
2532  l_po_header_id          po_headers_all.po_header_id%TYPE;
2533  l_change_group_id       po_change_requests.change_request_group_id%type;
2534  x_progress              VARCHAR2(1000);
2535  l_po_style_type         VARCHAR2(10);
2536  l_doc_type               VARCHAR2(10);
2537  l_doc_subtype            VARCHAR2(10);
2538  l_prmdate_app_flag       VARCHAR2(10);
2539  l_shi_qty_app_flag       VARCHAR2(10);
2540  l_unit_price_app_flag    VARCHAR2(10);
2541 
2542 BEGIN
2543   x_progress := 'ROUTE_TO_REQUESTER:000';
2544   l_po_header_id      := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2545   					              itemkey  => itemkey,
2546   	                                              aname    => 'PO_HEADER_ID');
2547 
2548   l_change_group_id   := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2549   					              itemkey  => itemkey,
2550   	                                              aname    => 'CHANGE_REQUEST_GROUP_ID');
2551 
2552   l_po_style_type     :=  wf_engine.GetItemAttrText  (itemtype => itemtype,
2553       						      itemkey  => itemkey,
2554       		                                      aname    => 'PO_STYLE_TYPE');
2555 
2556   l_doc_type          :=  wf_engine.GetItemAttrText  (itemtype => itemtype,
2557       					              itemkey  => itemkey,
2558       		                                      aname    => 'DOCUMENT_TYPE');
2559 
2560   l_doc_subtype       :=  wf_engine.GetItemAttrText  (itemtype => itemtype,
2561                                                       itemkey  => itemkey,
2562                                                       aname    => 'DOC_SUB_TYPE');
2563 
2564   l_prmdate_app_flag  :=  wf_engine.GetItemAttrText  (itemtype => itemtype,
2565                                                       itemkey  => itemkey,
2566                                                       aname    => 'PROMISE_DATE_APP_FLAG');
2567 
2568   l_shi_qty_app_flag  :=  wf_engine.GetItemAttrText  (itemtype => itemtype,
2569                                                       itemkey  => itemkey,
2570                                                       aname    => 'SHIP_QTY_APPROVAL_FLAG');
2571 
2572   l_unit_price_app_flag := wf_engine.GetItemAttrText (itemtype => itemtype,
2573                                                       itemkey  => itemkey,
2574                                                       aname    => 'PRICE_APPROVAL_FLAG');
2575 
2576   x_progress := 'ROUTE_TO_REQUESTER:001';
2577 
2578 IF((l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD') OR (l_doc_type = 'RELEASE' AND l_doc_subtype = 'BLANKET')) THEN
2579 
2580 
2581   IF ( ROUTETOREQUESTER ( l_po_header_id,l_change_group_id,l_doc_type,l_prmdate_app_flag,l_shi_qty_app_flag,l_unit_price_app_flag)=TRUE) THEN
2582      l_return_val:= 'Y' ;
2583      x_progress := 'ROUTE_TO_REQUESTER:002';
2584   ELSE
2585     -- send the notification to buyer
2586     wf_engine.SetItemAttrText(itemtype => itemtype,
2587                               itemkey => itemkey,
2588                               aname => 'NOTIF_USAGE',
2589                               avalue => 'BUYER');
2590      l_return_val:= 'N' ;
2591      x_progress := 'ROUTE_TO_REQUESTER:003';
2592    END IF;
2593 
2594 ELSE
2595      wf_engine.SetItemAttrText(itemtype => itemtype,
2596                               itemkey => itemkey,
2597                               aname => 'NOTIF_USAGE',
2598                               avalue => 'BUYER');
2599      l_return_val:= 'N' ;
2600      x_progress := 'ROUTE_TO_REQUESTER:004';
2601 END IF;
2602   resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2603   x_progress := 'ROUTE_TO_REQUESTER:005';
2604   log_message('ROUTE_TO_REQUESTER','Result',resultout);
2605 EXCEPTION
2606   WHEN OTHERS THEN
2607    IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2608              FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2609                             g_module_prefix,
2610                             x_progress || ':unexpected error' || Sqlerrm);
2611    END IF;
2612    wf_core.context('POSCHORD', 'ROUTE_TO_REQUESTER', itemtype, itemkey, to_char(actid),funcmode);
2613    raise;
2614 END ROUTE_TO_REQUESTER;
2615 
2616 
2617 FUNCTION ROUTETOREQUESTER (p_po_header_id IN NUMBER ,p_change_group_id IN NUMBER, p_doc_type IN VARCHAR2, p_prm_date_app_flag IN VARCHAR2, p_ship_qty_app_flag IN VARCHAR2, p_unit_price_app_flag IN VARCHAR2)
2618 return boolean is
2619 
2620 cursor c_promise_date_changed (p_po_header_id_csr_pd IN NUMBER,p_change_group_id_csr_pd IN NUMBER) is
2621        select count(1) from po_change_requests pcr
2622        where pcr.document_header_id=p_po_header_id_csr_pd
2623 	     AND pcr.change_request_group_id=p_change_group_id_csr_pd
2624 	     AND pcr.new_promised_date IS NOT NULL
2625 	     AND pcr.action_type='MODIFICATION'
2626 	     AND pcr.request_status= 'PENDING'
2627 	     AND pcr.request_level= 'SHIPMENT'
2628 	     AND pcr.initiator= 'SUPPLIER';
2629 
2630 
2631 
2632 cursor c_ship_qty_changed(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER)  is
2633        select count(1) from po_change_requests pcr
2634        where  pcr.document_header_id=p_po_header_id_csr
2635 	      AND pcr.change_request_group_id=p_change_group_id_csr
2636 	      AND pcr.new_quantity IS NOT NULL
2637 	      AND pcr.action_type='MODIFICATION'
2638 	      AND pcr.request_status= 'PENDING'
2639 	      AND pcr.request_level= 'SHIPMENT'
2640 	      AND pcr.initiator= 'SUPPLIER';
2641 
2642 
2643 cursor c_price_changed(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER)  is
2644 select count(1) from po_change_requests pcr
2645 where  pcr.document_header_id=p_po_header_id_csr
2646        AND pcr.change_request_group_id=p_change_group_id_csr
2647        AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
2648        AND pcr.action_type='MODIFICATION'
2649        AND pcr.request_status= 'PENDING'
2650        AND pcr.request_level IN ('LINE','SHIPMENT')
2651        AND pcr.initiator= 'SUPPLIER';
2652 
2653 cursor c_price_changed_rel(p_po_header_id_csr IN NUMBER,p_change_group_id_csr IN NUMBER)  is
2654 select count(1) from po_change_requests pcr
2655 where  pcr.document_header_id = p_po_header_id_csr
2656        AND pcr.change_request_group_id=p_change_group_id_csr
2657        AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
2658        AND pcr.action_type='MODIFICATION'
2659        AND pcr.request_status= 'PENDING'
2660        AND pcr.request_level= 'SHIPMENT'
2661        AND pcr.initiator= 'SUPPLIER';
2662 
2663 l_progress varchar2(50) := '000';
2664 l_promise_date_changed number;
2665 l_ship_qty_changed     number;
2666 l_price_changed        number;
2667 l_ret_val              varchar2(10) := 'N';
2668 l_ret_val_prom_date_ch              varchar2(10) := 'N';
2669 l_ret_val_ship_qty_ch              varchar2(10) := 'N';
2670 l_ret_val_line_price_ch              varchar2(10) := 'N';
2671 l_ret_val_ship_price_ch              varchar2(10) := 'N';
2672 l_api_name varchar2(50) := 'ROUTE_TO_REQUESTER';
2673 x_progress varchar2(1000);
2674 
2675 
2676 BEGIN
2677      x_progress := 'ROUTETOREQUESTER:000';
2678      -- Checking whether there is a promise data change
2679     log_message('ROUTETOREQUESTER','Checking for Promise Date Changes','.');
2680 
2681      OPEN c_promise_date_changed(p_po_header_id ,p_change_group_id ) ;
2682         FETCH c_promise_date_changed INTO l_promise_date_changed;
2683         log_message('ROUTETOREQUESTER','l_promise_date_changed',l_promise_date_changed);
2684 
2685         IF ( l_promise_date_changed >= 1) THEN
2686            CLOSE c_promise_date_changed;
2687            x_progress := 'ROUTETOREQUESTER:001';
2688            log_message('ROUTETOREQUESTER','Promise Date Changed','Yes');
2689             IF( p_prm_date_app_flag = 'Y') THEN
2690 	            log_message('ROUTETOREQUESTER','Promise Date Changed Retrun Value','Yes');
2691               l_ret_val_prom_date_ch := 'Y';
2692 	            ELSE
2693               log_message('ROUTETOREQUESTER','Promise Date Changed Retrun Value','No');
2694 	            l_ret_val_prom_date_ch := 'N';
2695 	          END IF;
2696         END IF;
2697 
2698      IF c_promise_date_changed%ISOPEN THEN
2699         CLOSE c_promise_date_changed;
2700      END IF;
2701 
2702      log_message('ROUTETOREQUESTER','Checking for Ship Qty Changes','.');
2703 
2704      -- Checking whether there is a Shipment quantity  change
2705 
2706      OPEN c_ship_qty_changed(p_po_header_id ,p_change_group_id );
2707         FETCH c_ship_qty_changed INTO l_ship_qty_changed;
2708         log_message('ROUTETOREQUESTER','l_ship_qty_changed',l_ship_qty_changed);
2709         IF (l_ship_qty_changed >= 1) THEN
2710            CLOSE c_ship_qty_changed;
2711            x_progress := 'ROUTETOREQUESTER:002';
2712            log_message('ROUTETOREQUESTER','Ship Qty changed','Yes');
2713            IF( p_ship_qty_app_flag = 'Y') THEN
2714               log_message('ROUTETOREQUESTER','Shipment Qty Retrun Value','Yes');
2715 	            l_ret_val_ship_qty_ch := 'Y';
2716 	         ELSE
2717               log_message('ROUTETOREQUESTER','Shipment Qty Retrun Value','No');
2718 	            l_ret_val_ship_qty_ch := 'N';
2719 	         END IF;
2720         END IF;
2721 
2722      IF c_ship_qty_changed%ISOPEN THEN
2723         CLOSE c_ship_qty_changed;
2724      END IF;
2725 
2726      -- Checking whether there is a Line price change
2727     log_message('ROUTETOREQUESTER','Checking for Line Price Changes','.');
2728 
2729 
2730     IF ( p_doc_type = 'PO') THEN
2731       OPEN c_price_changed(p_po_header_id ,p_change_group_id );
2732       FETCH c_price_changed INTO l_price_changed;
2733       log_message('ROUTETOREQUESTER','l_price_changed',l_price_changed);
2734         IF(l_price_changed >= 1) THEN
2735            CLOSE c_price_changed;
2736            x_progress := 'ROUTETOREQUESTER:003';
2737            log_message('ROUTETOREQUESTER','Line Price Changed','Yes');
2738            IF( p_unit_price_app_flag = 'Y') THEN
2739                log_message('ROUTETOREQUESTER','Line Price Return Value','Yes');
2740 	             l_ret_val_line_price_ch := 'Y';
2741 	         ELSE
2742                log_message('ROUTETOREQUESTER','Line Price Return Value','No');
2743 	             l_ret_val_line_price_ch := 'N';
2744 	        END IF;
2745        END IF;
2746 
2747       IF c_price_changed%ISOPEN THEN
2748           CLOSE c_price_changed;
2749       END IF;
2750    log_message('ROUTETOREQUESTER','Checking for Shipment Price Changes','.');
2751 
2752 
2753    ELSIF( p_doc_type = 'RELEASE') THEN
2754        OPEN c_price_changed_rel(p_po_header_id ,p_change_group_id );
2755        FETCH c_price_changed_rel INTO l_price_changed;
2756        log_message('ROUTETOREQUESTER','l_price_changed',l_price_changed);
2757           IF(l_price_changed >= 1) THEN
2758              CLOSE c_price_changed_rel;
2759              x_progress := 'ROUTETOREQUESTER:004';
2760              log_message('ROUTETOREQUESTER','Shipment Price Changed','Yes');
2761 	           IF(p_unit_price_app_flag = 'Y') THEN
2762                  log_message('ROUTETOREQUESTER','Shipment Price Return Value','Yes');
2763 	               l_ret_val_ship_price_ch := 'Y';
2764              ELSE
2765                  log_message('ROUTETOREQUESTER','Shipment Price Return Value','No');
2766 	               l_ret_val_ship_price_ch := 'N';
2767 	          END IF;
2768           END IF;
2769 
2770        IF c_price_changed_rel%ISOPEN THEN
2771            CLOSE c_price_changed_rel;
2772        END IF;
2773 
2774    END IF; -- END ELSIF
2775 
2776   x_progress := 'ROUTETOREQUESTER:005';
2777   IF(l_ret_val_line_price_ch = 'Y' OR l_ret_val_ship_qty_ch = 'Y' OR l_ret_val_prom_date_ch = 'Y' OR l_ret_val_ship_price_ch = 'Y')
2778   THEN
2779   l_ret_val := 'Y';
2780   ELSE
2781   l_ret_val := 'N';
2782   END IF;
2783   log_message('ROUTETOREQUESTER','l_ret_val - ',l_ret_val);
2784   log_message('ROUTETOREQUESTER','l_ret_val_line_price_ch - ',l_ret_val_line_price_ch);
2785   log_message('ROUTETOREQUESTER','l_ret_val_ship_qty_ch - ',l_ret_val_ship_qty_ch);
2786   log_message('ROUTETOREQUESTER','l_ret_val_prom_date_ch - ',l_ret_val_prom_date_ch);
2787 
2788   IF( l_ret_val = 'Y') THEN
2789     log_message('ROUTETOREQUESTER','Returning - ','True');
2790     return TRUE;
2791   ELSIF(l_ret_val = 'N') THEN
2792     log_message('ROUTETOREQUESTER','Returning - ','False');
2793     return FALSE;
2794  ELSE RETURN FALSE;
2795   END IF;
2796 
2797 EXCEPTION
2798   WHEN OTHERS THEN
2799    IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2800              FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2801                             g_module_prefix,
2802                             x_progress || ':unexpected error' || Sqlerrm);
2803    END IF;
2804  raise;
2805 END ROUTETOREQUESTER;
2806 
2807 
2808 
2809 
2810 PROCEDURE ROUTE_SCO_BIZ_RULES( itemtype        IN VARCHAR2,
2811  	                       itemkey         IN VARCHAR2,
2812  	                       actid           IN NUMBER,
2813  	                       funcmode        IN VARCHAR2,
2814                                resultout       OUT NOCOPY VARCHAR2)
2815 
2816 IS
2817 
2818 -- curosr to get the ReqHeaderId
2819 cursor c_getReqHdrId(p_po_header_id_csr IN NUMBER) is
2820         select distinct porh.requisition_header_id
2821         from   po_requisition_headers_all porh,
2822                po_requisition_lines_all porl,
2823                po_headers_all poh,
2824                po_line_locations_all poll
2825         where  porh.requisition_header_id = porl.requisition_header_id AND
2826                porl.line_location_id = poll.line_location_id  AND
2827                poh.po_header_id = poll.po_header_id AND
2828                poh.po_header_id = p_po_header_id_csr;
2829 
2830 cursor c_getReqHdrId_r(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
2831         select distinct porh.requisition_header_id
2832         from   po_requisition_headers_all porh,
2833                po_requisition_lines_all porl,
2834                po_headers_all poh,
2835                po_line_locations_all poll
2836         where  porh.requisition_header_id = porl.requisition_header_id AND
2837                porl.line_location_id = poll.line_location_id  AND
2838                poh.po_header_id = poll.po_header_id AND
2839                poh.po_header_id = p_po_header_id_csr AND
2840                poll.po_release_id = p_po_release_id_csr;
2841 
2842 l_po_header_id          po_headers_all.po_header_id%TYPE;
2843 l_po_release_id         po_releases_all.po_release_id%TYPE;
2844 l_doc_type              VARCHAR2(10);
2845 l_return_val            VARCHAR2(1);
2846 l_change_group_id       po_change_requests.change_request_group_id%TYPE;
2847 l_req_hdr_id            po_requisition_headers_all.requisition_header_id%TYPE;
2848 x_progress              VARCHAR2(1000);
2849 l_auto_app_flag         VARCHAR2(1);
2850 
2851 BEGIN
2852 
2853 x_progress := 'ROUTE_SCO_BIZ_RULES:000';
2854 
2855 l_po_header_id     := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2856 					                               itemkey  => itemkey,
2857 	                                               aname    => 'PO_HEADER_ID');
2858 
2859 l_po_release_id    := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2860   					           itemkey  => itemkey,
2861   	                                           aname    => 'PO_RELEASE_ID');
2862 
2863 l_doc_type         := wf_engine.GetItemAttrText   (itemtype => itemtype,
2864       					           itemkey  => itemkey,
2865       		                                   aname    => 'DOCUMENT_TYPE');
2866 
2867 l_change_group_id  := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2868                                                    itemkey  => itemkey,
2869                                                    aname    => 'CHANGE_REQUEST_GROUP_ID');
2870 
2871 l_auto_app_flag     :=  wf_engine.GetItemAttrText   (itemtype => itemtype,
2872       					            itemkey   => itemkey,
2873       		                                    aname     => 'AUTO_APP_BIZ_RULES_FLAG');
2874 
2875   x_progress := 'ROUTE_SCO_BIZ_RULES:001';
2876 
2877 IF(l_auto_app_flag = 'Y') THEN
2878 
2879   IF (ROUTE_SCO_BIZ_RULES_CHECK(l_po_header_id,l_po_release_id,l_doc_type,l_change_group_id)=FALSE)  THEN
2880       l_return_val:= 'N';
2881       x_progress := 'ROUTE_SCO_BIZ_RULES:002';
2882       -- Set the Notif Usage for the notification to be sent to Buyer
2883       wf_engine.SetItemAttrText   (itemtype => itemtype,
2884                                    itemkey => itemkey,
2885                                    aname => 'NOTIF_USAGE',
2886                                    avalue =>'BUYER');
2887   ELSE
2888       l_return_val:= 'Y';
2889       x_progress := 'ROUTE_SCO_BIZ_RULES:003';
2890 
2891        IF( PROMISEDATECHANGE(l_po_header_id,l_change_group_id) = FALSE) THEN    -- lock the corresponding req if there are price or quantity changes
2892 
2893         if l_po_release_id is not null then
2894           OPEN c_getReqHdrId_r(l_po_header_id,l_po_release_id);
2895           LOOP
2896           FETCH c_getReqHdrId_r INTO l_req_hdr_id;
2897           EXIT WHEN c_getReqHdrId_r%NOTFOUND;
2898           x_progress:= 'ROUTE_SCO_BIZ_RULES:004';
2899           log_message('ROUTE_SCO_BIZ_RULES','Locking the Req',l_req_hdr_id);
2900 
2901           update po_requisition_headers_all
2902           set change_pending_flag = 'Y'
2903           where requisition_header_id = l_req_hdr_id;
2904 
2905           END LOOP;
2906           CLOSE c_getReqHdrId_r;
2907 
2908         else
2909           OPEN c_getReqHdrId(l_po_header_id);
2910           LOOP
2911           FETCH c_getReqHdrId INTO l_req_hdr_id;
2912           EXIT WHEN c_getReqHdrId%NOTFOUND;
2913           x_progress:= 'ROUTE_SCO_BIZ_RULES:004a';
2914           log_message('ROUTE_SCO_BIZ_RULES','Locking the Req',l_req_hdr_id);
2915 
2916           update po_requisition_headers_all
2917           set change_pending_flag = 'Y'
2918           where requisition_header_id = l_req_hdr_id;
2919 
2920           END LOOP;
2921           CLOSE c_getReqHdrId;
2922         end if;
2923 
2924       END IF;
2925 
2926   END IF;
2927 
2928 ELSIF( l_auto_app_flag = 'N') THEN
2929   l_return_val:= 'N';
2930   -- Set the Notif Usage for the notification to be sent to Buyer
2931         wf_engine.SetItemAttrText(   itemtype => itemtype,
2932                                      itemkey => itemkey,
2933                                      aname => 'NOTIF_USAGE',
2934                                      avalue =>'BUYER');
2935 END IF;
2936 
2937 
2938  resultout := wf_engine.eng_completed|| ':' || l_return_val ;
2939 
2940  x_progress := 'ROUTE_SCO_BIZ_RULES:005';
2941  log_message('ROUTE_SCO_BIZ_RULES','Result',resultout);
2942 
2943 
2944 EXCEPTION
2945   WHEN OTHERS THEN
2946    IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2947             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
2948                            g_module_prefix,
2949                            x_progress || ':unexpected error' || Sqlerrm);
2950    END IF;
2951    wf_core.context('POSCHORD', 'ROUTE_SCO_BIZ_RULES', itemtype, itemkey, to_char(actid),funcmode);
2952    raise;
2953 END ROUTE_SCO_BIZ_RULES;
2954 
2955 
2956 PROCEDURE AUTO_APP_BIZ_RULES(itemtype        IN VARCHAR2,
2957    	                     itemkey         IN VARCHAR2,
2958    	                     actid           IN NUMBER,
2959    	                     funcmode        IN VARCHAR2,
2960                              resultout       OUT NOCOPY VARCHAR2)
2961 
2962 IS
2963 
2964 l_po_header_id          po_headers_all.po_header_id%TYPE;
2965 l_po_release_id         po_releases_all.po_release_id%TYPE;
2966 l_return_val            VARCHAR2(1);
2967 x_progress              VARCHAR2(1000);
2968 l_po_style_type         VARCHAR2(10);
2969 l_doc_type              VARCHAR2(10);
2970 l_doc_subtype           VARCHAR2(10);
2971 
2972 BEGIN
2973   x_progress := 'AUTO_APP_BIZ_RULES:000';
2974 
2975   l_po_header_id      :=  wf_engine.GetItemAttrNumber (itemtype => itemtype,
2976   					               itemkey  => itemkey,
2977   	                                               aname    => 'PO_HEADER_ID');
2978 
2979   l_po_release_id     := wf_engine.GetItemAttrNumber  (itemtype => itemtype,
2980   					               itemkey  => itemkey,
2981   	                                               aname    => 'PO_RELEASE_ID');
2982 
2983   l_po_style_type     := wf_engine.GetItemAttrText    (itemtype => itemtype,
2984       					               itemkey  => itemkey,
2985       		                                       aname    => 'PO_STYLE_TYPE');
2986 
2987   l_doc_type          := wf_engine.GetItemAttrText    (itemtype => itemtype,
2988       					               itemkey  => itemkey,
2989       		                                       aname    => 'DOCUMENT_TYPE');
2990 
2991   l_doc_subtype       := wf_engine.GetItemAttrText    (itemtype => itemtype,
2992                                                        itemkey  => itemkey,
2993                                                        aname    => 'DOC_SUB_TYPE');
2994 
2995   x_progress := 'AUTO_APP_BIZ_RULES:001';
2996 
2997 IF((l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD') OR (l_doc_type = 'RELEASE' AND l_doc_subtype = 'BLANKET')) THEN
2998     IF ( AUTO_APP_BIZ_RULES_CHECK(l_po_header_id,l_po_release_id,l_doc_type)=FALSE) THEN
2999 
3000          wf_engine.SetItemAttrText(     itemtype => itemtype,
3001                                         itemkey => itemkey,
3002                                         aname => 'NOTIF_USAGE',
3003                                         avalue => 'BUYER');
3004 
3005          wf_engine.SetItemAttrText(     itemtype => itemtype,
3006                                         itemkey => itemkey,
3007                                         aname => 'AUTO_APP_BIZ_RULES_FLAG',
3008                                         avalue => 'N');
3009 
3010          x_progress := 'AUTO_APP_BIZ_RULES:002';
3011 
3012          l_return_val:= 'N';
3013      ELSE
3014          wf_engine.SetItemAttrText(     itemtype => itemtype,
3015 	                                itemkey => itemkey,
3016 	                                aname => 'AUTO_APP_BIZ_RULES_FLAG',
3017                                         avalue => 'Y');
3018 
3019          l_return_val:= 'Y';
3020 
3021      END IF;
3022 ELSE
3023      l_return_val := 'N';
3024 END IF;
3025 
3026   resultout := wf_engine.eng_completed|| ':' || l_return_val ;
3027   x_progress := 'AUTO_APP_BIZ_RULES:003';
3028 
3029   log_message('AUTO_APP_BIZ_RULES','Result',resultout);
3030 
3031 EXCEPTION
3032  WHEN OTHERS THEN
3033   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3034             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3035                            g_module_prefix,
3036                            x_progress || ':unexpected error' || Sqlerrm);
3037   END IF;
3038   wf_core.context('POSCHORD', 'AUTO_APP_BIZ_RULES', itemtype, itemkey, to_char(actid),funcmode);
3039   raise;
3040 END AUTO_APP_BIZ_RULES;
3041 
3042 
3043 FUNCTION ROUTE_SCO_BIZ_RULES_CHECK (p_po_header_id IN NUMBER,p_po_release_id IN NUMBER,p_doc_type IN VARCHAR2, p_change_group_id IN NUMBER)
3044 RETURN BOOLEAN
3045 IS
3046 
3047  --  Cursor for Checking for the multipe backing reqs is mapped to the PO
3048 
3049  CURSOR c_reqs_count(p_po_header_id_csr IN NUMBER) is
3050   SELECT count(distinct porl.requisition_header_id)
3051   FROM   po_requisition_lines_all porl,  -- <Shared Proc FPJ>
3052          po_line_locations_all poll
3053   WHERE  poll.line_location_id = porl.line_location_id AND
3054          NVL(poll.cancel_flag, 'N') = 'N' AND
3055          NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
3056          poll.shipment_type IN('STANDARD', 'BLANKET')
3057          AND poll.po_header_id=p_po_header_id_csr;
3058 
3059   --  Cursor for Checking for the multipe backing reqs is mapped to the releases
3060 
3061  CURSOR c_reqs_count_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3062   SELECT count(distinct porl.requisition_header_id)
3063   FROM   po_requisition_lines_all porl,  -- <Shared Proc FPJ>
3064          po_line_locations_all poll
3065   WHERE  poll.line_location_id = porl.line_location_id AND
3066          NVL(poll.cancel_flag, 'N') = 'N' AND
3067          NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
3068          poll.shipment_type IN('STANDARD', 'BLANKET')
3069          AND poll.po_header_id=p_po_header_id_csr
3070          AND poll.po_release_id = p_po_release_id_csr;
3071 
3072 
3073  -- Cursor for checking all shipments should be mappped to one req.
3074 
3075  CURSOR c_req_map_ship(p_po_header_id_csr IN NUMBER) is
3076  SELECT count(1)
3077  FROM   po_line_locations_all plla
3078  WHERE  plla.po_header_id = p_po_header_id_csr
3079         AND plla.line_location_id NOT IN (SELECT plla2.line_location_id
3080                                           FROM   po_requisition_lines_all  porla,
3081                                                  po_line_locations_all plla2
3082                                           WHERE  plla2.po_header_id = p_po_header_id_csr
3083                                                  AND porla.line_location_id = plla2.line_location_id);
3084 
3085 
3086  -- Cursor for checking all shipments should be mappped to one req for the releases
3087   CURSOR c_req_map_ship_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) IS
3088   SELECT count(1)
3089   FROM   po_line_locations_all plla
3090   WHERE  plla.po_header_id = p_po_header_id_csr
3091          AND plla.po_release_id = p_po_release_id_csr
3092          AND plla.line_location_id NOT IN (   SELECT plla2.line_location_id
3093                                               FROM   po_requisition_lines_all  porla,
3094                                                      po_line_locations_all plla2
3095                                               WHERE  plla2.po_header_id = p_po_header_id_csr
3096                                               AND    plla2.po_release_id = p_po_release_id_csr
3097                                               AND    porla.line_location_id = plla2.line_location_id);
3098 
3099 
3100  -- Cursor for checking whether there is a price change for Catalog  item.
3101 
3102  cursor c_cat_price_change(p_po_header_id_csr IN NUMBER)  is
3103         select count(1)
3104         from   po_change_requests pcr,
3105 	       po_requisition_lines_all porl1,
3106 	       po_line_locations_all pll
3107         where  pcr.document_header_id = p_po_header_id_csr
3108                AND pcr.REQUEST_LEVEL = 'LINE'
3109                AND pcr.new_price is not NULL
3110                AND pcr.request_status = 'PENDING'
3111 	       AND porl1.line_location_id = pll.line_location_id
3112 	       AND pcr.document_line_id = pll.po_line_id
3113 	       AND porl1.item_id is not null;
3114 
3115 
3116 -- Curosr for getting the  Releases Price changes for Catlog request should go to the buyer
3117  cursor c_ship_price_change(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER)  is
3118         select count(1)
3119         from   po_change_requests pcr,
3120 	       po_requisition_lines_all porl1,
3121 	       po_line_locations_all pll
3122         where  pcr.document_header_id = p_po_header_id_csr
3123 	       AND pcr.po_release_id = p_po_release_id_csr
3124                AND pcr.REQUEST_LEVEL = 'SHIPMENT'
3125                AND nvl(pcr.new_price,pcr.new_amount) is not NULL   -- NEW_AMOUNT in case of FPS
3126                AND pcr.request_status = 'PENDING'
3127 	       AND pcr.document_line_location_id = porl1.line_location_id
3128 	       AND porl1.line_location_id = pll.line_location_id
3129 	       AND porl1.item_id is NOT NULL;
3130 
3131 -- Cursor for checking the FPS price changes for catalog requests
3132 cursor c_fps_price_change(p_po_header_id_csr IN NUMBER) IS
3133     select count(1)
3134         from   po_change_requests pcr,
3135 	       po_requisition_lines_all porl1,
3136 	       po_line_locations_all pll
3137 	where  pcr.document_header_id = p_po_header_id_csr
3138 	       AND pcr.REQUEST_LEVEL = 'SHIPMENT'
3139                AND pcr.new_amount is not NULL
3140                AND pcr.request_status = 'PENDING'
3141   	       AND pcr.document_line_location_id = porl1.line_location_id
3142 	       AND porl1.line_location_id = pll.line_location_id
3143 	       AND porl1.item_id IS NOT NULL;
3144 
3145 
3146 -- Check whether that Requisition is Locked or not
3147 
3148  cursor c_req_locks(p_po_header_id_csr IN NUMBER) is
3149         select count(1)
3150         from   po_requisition_headers_all porh,
3151                po_requisition_lines_all porl,
3152                po_headers_all poh,
3153                po_line_locations_all poll
3154         where  porh.requisition_header_id = porl.requisition_header_id AND
3155                porl.line_location_id = poll.line_location_id  AND
3156                poh.po_header_id = poll.po_header_id AND
3157                poh.po_header_id = p_po_header_id_csr AND
3158                porh.change_pENDing_flag = 'Y' ;
3159 
3160 
3161  -- Check whether that Requisition is Locked or not for the releases
3162 
3163  cursor c_req_locks_rel(p_po_header_id_csr IN NUMBER, p_po_release_id_csr IN NUMBER) is
3164         select count(1)
3165         from   po_requisition_headers_all porh,
3166                po_requisition_lines_all porl,
3167                po_headers_all poh,
3168                po_line_locations_all poll
3169         where  porh.requisition_header_id = porl.requisition_header_id AND
3170                porl.line_location_id = poll.line_location_id  AND
3171                poh.po_header_id = poll.po_header_id AND
3172                poh.po_header_id = p_po_header_id_csr AND
3173                poll.po_release_id = p_po_release_id_csr AND
3174                porh.change_pENDing_flag = 'Y' ;
3175 
3176   --Cursor to test whether changes are done on lines from multiple requesters
3177    CURSOR l_requestors_csr(c_grp_id_csr IN NUMBER)
3178   IS
3179   select UNIQUE(pda.deliver_to_person_id)
3180   from
3181   	po_change_requests pcr,
3182   	po_distributions_all pda
3183   where pcr.change_request_group_id = c_grp_id_csr
3184   AND pcr.request_level = 'LINE'
3185   AND pcr.document_line_id = pda.po_line_id
3186   and pda.deliver_to_person_id is not null
3187   union
3188   select UNIQUE(pda.deliver_to_person_id)
3189   from
3190   	po_change_requests pcr,
3191   	po_distributions_all pda
3192   where pcr.change_request_group_id = c_grp_id_csr
3193   AND pcr.request_level = 'SHIPMENT'
3194   AND pcr.document_line_location_id = pda.line_location_id
3195   and pda.deliver_to_person_id is not null;
3196 
3197 
3198 
3199  -- Check whether there is a quantity change from a shipment which is made up of two dIFferent req lines
3200 
3201  -- will be taken care of INIsProrateNeeded function
3202 
3203  l_backing_req_count  number;
3204  l_catp_change_count  number;
3205  l_fps_change_count   number;
3206  l_ship_change_count  number;
3207  l_req_lock_cnt       number;
3208  l_temp_line_loc_id   number;
3209  l_ship_map           number;
3210  l_api_name           varchar2(50) := 'ROUTE_SCO_BIZ_RULES_FUNC';
3211  x_progress           varchar2(1000);
3212 
3213  l_change_group_id NUMBER;
3214  l_requester_id NUMBER;
3215  l_count_req NUMBER;
3216 
3217  BEGIN
3218 
3219  x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:000';
3220 
3221  --Bug 11732340
3222 --Check for unique requestor
3223     l_count_req := 0;
3224     OPEN l_requestors_csr(p_change_group_id);
3225     LOOP
3226 	 FETCH l_requestors_csr INTO l_requester_id;
3227      EXIT WHEN l_requestors_csr%NOTFOUND;
3228      l_count_req := l_count_req + 1;
3229     END LOOP;
3230 
3231     close l_requestors_csr;
3232 
3233     IF(l_count_req <> 1)
3234     THEN
3235         RETURN FALSE;
3236     END IF;
3237 
3238 
3239 
3240 
3241  --- Po should have one backing req and all shipments should be mapped to the req line
3242  IF(p_doc_type = 'PO') THEN
3243            /* OPEN c_reqs_count(p_po_header_id);
3244               FETCH c_reqs_count INTO l_backing_req_count;
3245 
3246               x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:001';
3247               log_message('ROUTE_SCO_BIZ_RULES_CHECK','Backing Req Count',l_backing_req_count);
3248 
3249               IF ((l_backing_req_count > 1) or (l_backing_req_count = 0)) THEN
3250                   CLOSE c_reqs_count;
3251                   return FALSE;
3252               ELSIF (l_backing_req_count = 1) THEN
3253                   OPEN c_req_map_ship(p_po_header_id);
3254                   FETCH c_req_map_ship INTO l_ship_map;
3255                   x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:002';
3256                   log_message('ROUTE_SCO_BIZ_RULES_CHECK','Shipment Mapped',l_ship_map);
3257                   IF (l_ship_map >= 1) THEN
3258  		     CLOSE c_req_map_ship;
3259  	             return FALSE;
3260                   END IF;
3261  	          CLOSE c_req_map_ship;
3262               END IF;
3263 
3264               IF c_req_map_ship%ISOPEN THEN
3265                 CLOSE c_req_map_ship;
3266               END IF;
3267               IF c_reqs_count%ISOPEN THEN
3268                  CLOSE c_reqs_count;
3269               END IF;    */
3270 
3271   x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:003';
3272   ---- Price changes for NCR should go to the buyer
3273 
3274              OPEN c_cat_price_change(p_po_header_id);
3275              FETCH c_cat_price_change INTO l_catp_change_count;
3276              log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_catp_change_count);
3277              IF (l_catp_change_count >= 1)  THEN
3278                CLOSE c_cat_price_change;
3279                return FALSE;
3280              END IF;
3281 
3282              IF c_cat_price_change%ISOPEN THEN
3283                  CLOSE c_cat_price_change;
3284              END IF;
3285 
3286  ---- FPS Price Changes for NCR should go to the buyer
3287              OPEN c_fps_price_change(p_po_header_id);
3288              FETCH c_fps_price_change INTO l_fps_change_count;
3289              log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_fps_change_count);
3290              IF (l_fps_change_count >= 1)  THEN
3291                CLOSE c_fps_price_change;
3292                return FALSE;
3293              END IF;
3294 
3295              IF c_fps_price_change%ISOPEN THEN
3296                  CLOSE c_fps_price_change;
3297              END IF;
3298 
3299   x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:004';
3300   --- Check whether Requisition is locked or not if the SCO involves more than Promise Date change
3301 
3302              OPEN c_req_locks(p_po_header_id);
3303              FETCH c_req_locks INTO l_req_lock_cnt;
3304              log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Lock Count',l_req_lock_cnt);
3305 
3306              IF( PROMISEDATECHANGE(p_po_header_id,p_change_group_id) = FALSE) THEN
3307                 IF(l_req_lock_cnt >= 1) THEN
3308                    CLOSE c_req_locks;
3309                    log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Locking Biz Rule','Failed');
3310                    return FALSE;
3311                 END IF;
3312              END IF;
3313              IF c_req_locks%ISOPEN THEN
3314                  CLOSE c_req_locks;
3315              END IF;
3316 
3317   x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:005';
3318 
3319   return TRUE;
3320 
3321 
3322 ELSIF(p_doc_type = 'RELEASE') THEN
3323             --- Po should have one backing req and all shipments should be mapped to the req line
3324          /* x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:006';
3325             OPEN c_reqs_count_rel(p_po_header_id,p_po_release_id);
3326             FETCH c_reqs_count_rel INTO l_backing_req_count;
3327             x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:007';
3328             log_message('ROUTE_SCO_BIZ_RULES_CHECK','Backing Req Count',l_backing_req_count);
3329 
3330             IF ((l_backing_req_count > 1) or (l_backing_req_count = 0)) THEN
3331                 CLOSE c_reqs_count_rel;
3332                 return FALSE;
3333 
3334             ELSIF l_backing_req_count = 1 THEN
3335                 OPEN c_req_map_ship_rel(p_po_header_id,p_po_release_id);
3336                 FETCH c_req_map_ship_rel INTO l_ship_map;
3337                 x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:008';
3338                 log_message('ROUTE_SCO_BIZ_RULES_CHECK','Shipment Mapped',l_ship_map);
3339                 IF l_ship_map >= 1 THEN
3340  		   CLOSE c_req_map_ship_rel;
3341  	           return FALSE;
3342                 END IF;
3343  	        CLOSE c_req_map_ship_rel;
3344     	    END IF;
3345 
3346             IF c_req_map_ship_rel%ISOPEN THEN
3347                 CLOSE c_req_map_ship_rel;
3348             END IF;
3349             IF c_reqs_count_rel%ISOPEN THEN
3350                 CLOSE c_reqs_count_rel;
3351             END IF;          */
3352 
3353 --- Ship Price Changes for the NCR releases should go to the buyer
3354         OPEN c_ship_price_change(p_po_header_id,p_po_release_id);
3355              FETCH c_ship_price_change INTO l_ship_change_count;
3356              log_message('ROUTE_SCO_BIZ_RULES_CHECK','Cat Price Change Count',l_ship_change_count);
3357              IF (l_ship_change_count >= 1)  THEN
3358                CLOSE c_ship_price_change;
3359                return FALSE;
3360              END IF;
3361 
3362              IF c_ship_price_change%ISOPEN THEN
3363                  CLOSE c_ship_price_change;
3364              END IF;
3365 
3366 
3367 
3368  x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:009';
3369   --- Check whether Requisition is locked or not if the SCO involves more than Promise Date change
3370 
3371             OPEN c_req_locks_rel(p_po_header_id,p_po_release_id);
3372             FETCH c_req_locks_rel INTO l_req_lock_cnt;
3373             log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Lock Count',l_req_lock_cnt);
3374 
3375             IF( PROMISEDATECHANGE(p_po_header_id,p_change_group_id) = FALSE) THEN
3376                 IF(l_req_lock_cnt >= 1) THEN
3377                    CLOSE c_req_locks_rel;
3378                    log_message('ROUTE_SCO_BIZ_RULES_CHECK','Req Locking Biz Rule','Failed');
3379                    return FALSE;
3380                 END IF;
3381             END IF;
3382             IF c_req_locks_rel%ISOPEN THEN
3383                  CLOSE c_req_locks_rel;
3384             END IF;
3385 
3386   x_progress := 'ROUTE_SCO_BIZ_RULES_CHECK:010';
3387 
3388   return TRUE;
3389 END IF;  -- IF p_doc_type = PO or RELEASE
3390 RETURN TRUE;
3391 EXCEPTION
3392  WHEN OTHERS THEN
3393   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3394           FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3395                          g_module_prefix,
3396                          x_progress || ':unexpected error' || Sqlerrm);
3397   END IF;
3398   raise;
3399 END ROUTE_SCO_BIZ_RULES_CHECK;
3400 
3401 ------------------------------------------------------------------------------
3402 
3403 
3404 FUNCTION AUTO_APP_BIZ_RULES_CHECK (p_po_header_id IN NUMBER,p_po_release_id IN NUMBER,p_doc_type IN VARCHAR2)
3405 return boolean
3406 
3407 IS
3408 
3409 -- Split shipment check
3410 
3411   cursor c_split_ships(p_po_header_id_csr IN NUMBER) is
3412          select count(1)
3413          from   po_change_requests
3414          where  parent_line_location_id is not null
3415          AND    action_type = 'MODIFICATION'
3416          AND    document_header_id = p_po_header_id_csr
3417          AND    request_level = 'SHIPMENT'
3418          AND    request_status = 'PENDING';
3419 
3420 
3421 -- Split shipment check for releses
3422   cursor c_split_ships_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3423            select count(1)
3424            from   po_change_requests
3425            where  parent_line_location_id is not null
3426            AND    action_type = 'MODIFICATION'
3427            AND    document_header_id = p_po_header_id_csr
3428            AND    po_release_id      = p_po_release_id_csr
3429            AND    request_level = 'SHIPMENT'
3430            AND    request_status = 'PENDING';
3431 
3432 --   Cancellation request should go to buyer
3433 
3434   cursor c_cancel_requests(p_po_header_id_csr IN NUMBER) is
3435          SELECT count(1)
3436          from   po_change_requests
3437          where  action_type = 'CANCELLATION'
3438          AND    request_status = 'PENDING'
3439          AND    document_header_id = p_po_header_id_csr;
3440 
3441 
3442 --   Cancellation request should go to buyer for the releases
3443   cursor c_cancel_requests_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3444            SELECT count(1)
3445            from   po_change_requests
3446            where  action_type = 'CANCELLATION'
3447            AND    request_status = 'PENDING'
3448            AND    document_header_id = p_po_header_id_csr
3449            AND    po_release_id    =   p_po_release_id_csr;
3450 
3451 
3452 --Additional Change Request  the unstructured change request
3453 
3454   cursor c_add_changes(p_po_header_id_csr IN NUMBER) is
3455         select count(1)
3456         from   po_change_requests
3457         where  action_type = 'MODIFICATION'
3458         AND    ADDITIONAL_CHANGES is not NULL
3459         AND    request_status = 'PENDING'
3460         AND    document_header_id = p_po_header_id_csr;
3461 
3462 --Additional Change Request  the unstructured change request for the releases
3463   cursor c_add_changes_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3464         select count(1)
3465         from   po_change_requests
3466         where  action_type = 'MODIFICATION'
3467         AND    ADDITIONAL_CHANGES is not NULL
3468         AND    request_level = 'HEADER'
3469         AND    request_status = 'PENDING'
3470         AND    document_header_id = p_po_header_id_csr
3471         AND    po_release_id    =   p_po_release_id_csr;
3472 
3473 
3474 
3475 
3476 -- SCO created during Acknowledgement AND the supplier has rejected at least one shipment
3477 -- Cursor for checking whether sco is created during Acknowledgement or not.
3478   cursor c_sco_ack_ship(p_po_header_id_csr IN NUMBER) is
3479   SELECT acceptance_required_flag,revision_num
3480   FROM   po_headers_all
3481   WHERE  po_header_id = p_po_header_id_csr;
3482 
3483 -- Cursor for checking the acknowledgement status of the shipment
3484   cursor c_sco_ack_rej_ship(p_po_header_id_csr IN NUMBER, c_revision_num IN NUMBER) is
3485   SELECT count(1)
3486   FROM   po_acceptances pa,
3487          po_lines_archive_all pla,
3488          po_line_locations_archive_all plla
3489   WHERE  plla.po_header_id = p_po_header_id_csr
3490          AND pa.accepted_flag = 'N'
3491          AND plla.po_line_id = pla.po_line_id
3492          AND pa.po_line_location_id = plla.line_location_id
3493          AND pa.revision_num = c_revision_num
3494 	 AND plla.revision_num = (SELECT max(plla2.revision_num)
3495 	                          FROM   po_line_locations_archive_all plla2
3496 	                          WHERE  plla2.line_location_id = plla.line_location_id
3497                                   AND    plla.revision_num <= c_revision_num);
3498 
3499 
3500  -- SCO created during Acknowledgement AND the supplier has rejected at least one shipment for the releases
3501   -- Cursor for checking whether sco is created during Acknowledgement or not.
3502      cursor c_sco_ack_ship_rel(p_po_header_id_csr IN NUMBER,p_po_release_id_csr IN NUMBER) is
3503      SELECT acceptance_required_flag,revision_num
3504      FROM   po_releases_all
3505      WHERE  po_header_id = p_po_header_id_csr
3506      AND    po_release_id = p_po_release_id_csr;
3507 
3508     -- Cursor for checking the acknowledgement status of the shipment
3509   cursor c_sco_ack_rej_ship_rel(p_po_header_id_csr IN NUMBER, c_revision_num IN NUMBER, p_po_release_id_csr IN NUMBER ) is
3510   SELECT count(1)
3511   FROM   po_acceptances pa,
3512          po_lines_archive_all pla,
3513          po_line_locations_archive_all plla
3514   WHERE  plla.po_header_id = p_po_header_id_csr
3515          AND pa.po_release_id = p_po_release_id_csr
3516          AND pa.accepted_flag = 'N'
3517          AND plla.po_release_id = pa.po_release_id
3518          AND plla.po_line_id = pla.po_line_id
3519          AND pa.po_line_location_id = plla.line_location_id
3520          AND pa.revision_num = c_revision_num
3521 	 AND plla.revision_num = (SELECT max(plla2.revision_num)
3522 	                          FROM   po_line_locations_archive_all plla2
3523 	                          WHERE  plla2.line_location_id = plla.line_location_id
3524                                   AND    plla.revision_num <= c_revision_num);
3525 
3526 -- Cursor for checking whether the signature is required or not
3527  /*  5550515
3528   cursor c_sgn_req_flag(p_po_header_id_csr IN NUMBER) IS
3529   SELECT acceptance_required_flag
3530   FROM   po_headers_all
3531   WHERE  po_header_id = p_po_header_id_csr;
3532 */
3533  cursor c_sgn_req_flag_po(p_po_header_id_csr IN NUMBER,c_revision_num IN NUMBER ) IS
3534   SELECT count(1)
3535   FROM   po_acceptances
3536   WHERE  po_header_id = p_po_header_id_csr
3537   and revision_num=c_revision_num
3538   and signature_flag='Y';
3539 /*
3540 cursor c_sgn_req_flag_rel(p_po_release_id_csr IN NUMBER, c_revision_num IN NUMBER) IS
3541 SELECT count(1)
3542   FROM   po_acceptances
3543   WHERE  po_release_id =p_po_release_id_csr
3544   and revision_num=c_revision_num
3545   and signature_flag='Y';
3546 */
3547 -- Cursor for checking for a supplier Item Change
3548    CURSOR c_supp_item_chg(p_po_header_id_csr IN NUMBER) IS
3549    SELECT count(1)
3550    FROM   po_change_requests
3551    WHERE  action_type = 'MODIFICATION'
3552    AND    request_status = 'PENDING'
3553    AND    request_level = 'LINE'
3554    AND    new_supplier_part_number is not null
3555    AND    document_header_id = p_po_header_id_csr;
3556 
3557   l_split_shipment_request      number;
3558   l_cancel_requests             number;
3559   l_additional_change_requests  number;
3560   l_ack_reject_cnt              number;
3561   l_supp_item_chg_cnt           number;
3562   l_temp                        number;
3563   l_accpt_req_flag              po_headers_all.acceptance_required_flag%type;
3564   l_revision_num                number;
3565   l_signature_required          number :=0;
3566   l_ack_status_flag_count       number;
3567   l_api_name                    varchar2(50) := 'AUTO_APP_BIZ_RULES';
3568   x_progress                    varchar2(1000);
3569 BEGIN
3570 
3571 x_progress := 'AUTO_APP_BIZ_RULES_CHECK:000';
3572 
3573 IF(p_doc_type = 'PO') THEN
3574         -- Split shipment check
3575         /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3576        /*
3577         x_progress := 'AUTO_APP_BIZ_RULES_CHECK:001';
3578         OPEN c_split_ships(p_po_header_id);
3579         FETCH c_split_ships INTO l_split_shipment_request;
3580         log_message('AUTO_APP_BIZ_RULES_CHECK','Split Shipment Check',l_split_shipment_request);
3581         IF(l_split_shipment_request > 0) THEN
3582              CLOSE c_split_ships;
3583              return FALSE;
3584         END IF;
3585         IF c_split_ships%ISOPEN THEN
3586              CLOSE c_split_ships;
3587         END IF;
3588        */
3589       /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3590    x_progress := 'AUTO_APP_BIZ_RULES_CHECK:002';
3591         --   Cancellation request should go to buyer
3592         OPEN c_cancel_requests(p_po_header_id);
3593         FETCH c_cancel_requests INTO l_cancel_requests;
3594         log_message('AUTO_APP_BIZ_RULES_CHECK','Cancellation Request Check',l_cancel_requests);
3595         IF(l_cancel_requests>0) THEN
3596              CLOSE c_cancel_requests;
3597              return FALSE;
3598         END IF;
3599         IF c_cancel_requests%ISOPEN THEN
3600              CLOSE c_cancel_requests;
3601         END IF;
3602 
3603    x_progress := 'AUTO_APP_BIZ_RULES_CHECK:003';
3604         -- additional changes should go to the buyer
3605         OPEN c_add_changes(p_po_header_id);
3606         FETCH c_add_changes INTO l_additional_change_requests;
3607         log_message('AUTO_APP_BIZ_RULES_CHECK','Additional Change Request Check',l_additional_change_requests);
3608         IF(l_additional_change_requests > 0 ) THEN
3609              CLOSE c_add_changes;
3610              return FALSE;
3611         END IF;
3612         IF c_add_changes%ISOPEN THEN
3613              CLOSE c_add_changes;
3614         END IF;
3615 
3616    x_progress := 'AUTO_APP_BIZ_RULES_CHECK:004';
3617         --Reject IF the SCO is created during acknowledgment, AND the supplier has rejected
3618         -- at least one shipment, the SCO should be rOUTed to the buyer.
3619         OPEN c_sco_ack_ship(p_po_header_id);
3620         FETCH c_sco_ack_ship INTO l_accpt_req_flag,l_revision_num;
3621         IF(l_accpt_req_flag = 'Y') THEN
3622              OPEN c_sco_ack_rej_ship(p_po_header_id,l_revision_num);
3623              FETCH c_sco_ack_rej_ship INTO l_ack_status_flag_count;
3624              log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack',l_ack_status_flag_count);
3625              IF(l_ack_status_flag_count >= 1) THEN
3626                  CLOSE c_sco_ack_rej_ship;
3627                  CLOSE c_sco_ack_ship;
3628                  log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack Check','Failed');
3629                  return FALSE;
3630              END IF;
3631         END IF;
3632         IF c_sco_ack_ship%ISOPEN THEN
3633             CLOSE c_sco_ack_ship;
3634         END IF;
3635         IF c_sco_ack_rej_ship%ISOPEN THEN
3636             CLOSE c_sco_ack_rej_ship;
3637         END IF;
3638 
3639     x_progress := 'AUTO_APP_BIZ_RULES_CHECK:005';
3640     -- check whether the document requires signature or not
3641         OPEN c_sgn_req_flag_po(p_po_header_id,l_revision_num);
3642         FETCH c_sgn_req_flag_po INTO l_signature_required;
3643         log_message('AUTO_APP_BIZ_RULES_CHECK','Signature Required ',l_signature_required);
3644 
3645         IF(l_signature_required >=1) THEN
3646             CLOSE c_sgn_req_flag_po;
3647             return FALSE;
3648         END IF;
3649 
3650         IF c_sgn_req_flag_po%ISOPEN THEN
3651             CLOSE c_sgn_req_flag_po;
3652         END IF;
3653 
3654     -- check whether the supplier item change is requested or not
3655         OPEN c_supp_item_chg(p_po_header_id);
3656         FETCH c_supp_item_chg INTO l_supp_item_chg_cnt;
3657         log_message('AUTO_APP_BIZ_RULES_CHECK','Supplie Item Chnage ',l_supp_item_chg_cnt);
3658 
3659         IF(l_supp_item_chg_cnt > 0) THEN
3660             CLOSE c_supp_item_chg;
3661             return FALSE;
3662         END IF;
3663         IF c_supp_item_chg%ISOPEN THEN
3664             CLOSE c_supp_item_chg;
3665         END IF;
3666 
3667   return TRUE;
3668 
3669 ELSIF(p_doc_type = 'RELEASE') THEN
3670    /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3671    /*
3672     x_progress := 'AUTO_APP_BIZ_RULES_CHECK:006';
3673       OPEN c_split_ships_rel(p_po_header_id,p_po_release_id);
3674       FETCH c_split_ships_rel INTO l_split_shipment_request;
3675       log_message('AUTO_APP_BIZ_RULES_CHECK','Split Shipment Check',l_split_shipment_request);
3676       IF(l_split_shipment_request > 0) THEN
3677          CLOSE c_split_ships_rel;
3678          return FALSE;
3679       END IF;
3680       IF c_split_ships_rel%ISOPEN THEN
3681          CLOSE c_split_ships_rel;
3682       END IF;
3683    */
3684   /*As part of 7697043 when spiltment occures is always sending todo to buyer to avoid that commented this code*/
3685     x_progress := 'AUTO_APP_BIZ_RULES_CHECK:007';
3686     --   Cancellation request should go to buyer
3687       OPEN c_cancel_requests_rel(p_po_header_id,p_po_release_id);
3688       FETCH c_cancel_requests_rel INTO l_cancel_requests;
3689       log_message('AUTO_APP_BIZ_RULES_CHECK','Cancellation Request Check',l_cancel_requests);
3690       IF(l_cancel_requests>0) THEN
3691          CLOSE c_cancel_requests_rel;
3692          return FALSE;
3693       END IF;
3694       IF c_cancel_requests_rel%ISOPEN THEN
3695          CLOSE c_cancel_requests_rel;
3696       END IF;
3697 
3698     x_progress := 'AUTO_APP_BIZ_RULES_CHECK:008';
3699     -- Additional chnages requested should go to the buyer
3700        OPEN c_add_changes_rel(p_po_header_id,p_po_release_id);
3701        FETCH c_add_changes_rel INTO l_additional_change_requests;
3702        log_message('AUTO_APP_BIZ_RULES_CHECK','Additional Change Request Check',l_additional_change_requests);
3703        IF(l_additional_change_requests > 0 ) THEN
3704           CLOSE c_add_changes_rel;
3705           return FALSE;
3706        END IF;
3707        IF c_add_changes_rel%ISOPEN THEN
3708           CLOSE c_add_changes_rel;
3709        END IF;
3710 
3711      x_progress := 'AUTO_APP_BIZ_RULES_CHECK:009';
3712      --Reject IF the SCO is created during acknowledgment, AND the supplier has rejected
3713      -- at least one shipment, the SCO should be rOUTed to the buyer.
3714         OPEN c_sco_ack_ship_rel(p_po_header_id,p_po_release_id);
3715         FETCH c_sco_ack_ship_rel INTO l_accpt_req_flag,l_revision_num;
3716         IF(l_accpt_req_flag = 'Y') THEN
3717             OPEN c_sco_ack_rej_ship_rel(p_po_header_id,l_revision_num,p_po_release_id);
3718             FETCH c_sco_ack_rej_ship_rel INTO l_ack_status_flag_count;
3719             log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack',l_ack_status_flag_count);
3720             IF(l_ack_status_flag_count >= 1) THEN
3721                CLOSE c_sco_ack_rej_ship_rel;
3722                CLOSE c_sco_ack_ship_rel;
3723                log_message('AUTO_APP_BIZ_RULES_CHECK','Shipments rejected during ack Check','Failed');
3724                return FALSE;
3725              END IF;
3726         END IF;
3727 
3728         IF c_sco_ack_ship_rel%ISOPEN THEN
3729           CLOSE c_sco_ack_ship_rel;
3730         END IF;
3731         IF c_sco_ack_rej_ship_rel%ISOPEN THEN
3732           CLOSE c_sco_ack_rej_ship_rel;
3733         END IF;
3734 /*
3735 -- check whether the document requires signature or not
3736         OPEN c_sgn_req_flag_rel(p_po_release_id,l_revision_num);
3737         FETCH c_sgn_req_flag_rel INTO l_signature_required;
3738 
3739         log_message('AUTO_APP_BIZ_RULES_CHECK','Signature Required ',l_signature_required);
3740 
3741         IF(l_signature_required <1) THEN
3742             CLOSE c_sgn_req_flag_rel;
3743             return FALSE;
3744         END IF;
3745 
3746         IF c_sgn_req_flag_rel%ISOPEN THEN
3747             CLOSE c_sgn_req_flag_rel;
3748         END IF;
3749  */
3750 
3751      x_progress := 'AUTO_APP_BIZ_RULES_CHECK:010';
3752 return TRUE;
3753 
3754 END IF; -- If po_doc_type = PO or RELEASE
3755 
3756 EXCEPTION
3757    WHEN OTHERS THEN
3758      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3759                FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3760                               g_module_prefix,
3761                               x_progress || ':unexpected error' || Sqlerrm);
3762      END IF;
3763 raise;
3764 
3765 END AUTO_APP_BIZ_RULES_CHECK;
3766 
3767 ------------------------------------------------------------------------------
3768 PROCEDURE PROMISE_DATE_CHANGE(itemtype        IN VARCHAR2,
3769   	                      itemkey         IN VARCHAR2,
3770   	                      actid           IN NUMBER,
3771   	                      funcmode        IN VARCHAR2,
3772                               resultout       OUT NOCOPY VARCHAR2)
3773 IS
3774 
3775 l_po_header_id          po_headers_all.po_header_id%TYPE;
3776 x_progress              VARCHAR2(1000);
3777 l_change_group_id       po_change_requests.change_request_group_id%TYPE;
3778 
3779 BEGIN
3780 
3781 
3782 IF (funcmode = 'RUN') THEN
3783 
3784   x_progress := 'PROMISE_DATE_CHANGE:000';
3785 
3786   l_po_header_id      :=  wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3787                                                         itemkey  => itemkey,
3788                                                         aname    => 'PO_HEADER_ID');
3789 
3790   l_change_group_id   :=  wf_engine.GetItemAttrNumber (itemtype => itemtype,
3791   					               itemkey  => itemkey,
3792   	                                               aname    => 'CHANGE_REQUEST_GROUP_ID');
3793   -- IF only a promise date change return yes otherwise no
3794 
3795   IF PROMISEDATECHANGE(l_po_header_id,l_change_group_id)=TRUE THEN
3796     resultout := wf_engine.eng_completed || ':' || 'Y';
3797   ELSE
3798     resultout := wf_engine.eng_completed || ':' || 'N';
3799   END IF;
3800   x_progress:= 'PROMISE_DATE_CHANGE:001';
3801   log_message('PROMISE_DATE_CHANGE','Only promised date changed',resultout);
3802   return;
3803 END IF;
3804 
3805 EXCEPTION
3806  WHEN OTHERS THEN
3807 -- The line below records this function call INthe error
3808 -- system INthe case of an exception.
3809 
3810  IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3811            FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3812                           g_module_prefix,
3813                           x_progress || ':unexpected error' || Sqlerrm);
3814   END IF;
3815 
3816 wf_core.context('POSCHORD', 'PROMISE_DATE_CHANGE',itemtype, itemkey, to_char(actid),funcmode);
3817 
3818 raise;
3819 
3820 END PROMISE_DATE_CHANGE;
3821 
3822 
3823 FUNCTION PROMISEDATECHANGE(p_po_header_id IN NUMBER, p_change_group_id IN NUMBER)
3824 return boolean
3825 is
3826 
3827 CURSOR c_shipment_change(l_po_header_id IN NUMBER,l_change_group_id_csr IN NUMBER) IS
3828        select  OLD_QUANTITY,
3829                NEW_QUANTITY,
3830                OLD_PROMISED_DATE,
3831                NEW_PROMISED_DATE,
3832                OLD_PRICE,
3833                NEW_PRICE,
3834 	       OLD_AMOUNT,
3835 	       NEW_AMOUNT
3836        from    po_change_requests
3837        where   initiator='SUPPLIER'
3838 	       AND action_type='MODIFICATION'
3839 	       AND request_level='SHIPMENT'
3840                AND change_request_group_id = l_change_group_id_csr
3841                AND request_status IN('PENDING','BUYER_APP')
3842                AND document_header_id=l_po_header_id
3843                AND  ( (nvl(new_promised_date,sysdate) <> nvl(old_promised_date,sysdate-1)) OR
3844 	              (nvl(new_promised_date,old_promised_date-1)<>old_promised_date) OR
3845 	              (nvl(old_promised_date,new_promised_date-1)<>new_promised_date)
3846                     );
3847 
3848 CURSOR c_line_change(l_po_header_id IN NUMBER,l_change_group_id_csr IN NUMBER) IS
3849         select  count(1)
3850         from   po_change_requests
3851         where  initiator='SUPPLIER'
3852 	       AND action_type='MODIFICATION'
3853                AND change_request_group_id = l_change_group_id_csr
3854 	       AND request_level='LINE'
3855                AND request_status IN('PENDING','BUYER_APP')
3856                AND document_header_id=l_po_header_id;
3857 
3858 
3859 l_only_promised_date_change      boolean :=TRUE;
3860 l_old_quantity              	 po_change_requests.old_quantity%type;
3861 l_new_quantity 		    	 po_change_requests.new_quantity%type;
3862 l_old_promised_date	    	 po_change_requests.old_promised_date%type;
3863 l_new_promised_date 	    	 po_change_requests.new_promised_date%type;
3864 l_old_price 		    	 po_change_requests.old_price%type;
3865 l_new_price 		    	 po_change_requests.new_price%type;
3866 l_old_amount 		  	 po_change_requests.old_amount%type;
3867 l_new_amount 			 po_change_requests.new_amount%type;
3868 x_progress                       VARCHAR2(1000);
3869 l_line_changes_counter           number:=0;
3870 l_api_name                       varchar2(50) := 'PROMISEDATECHANGE';
3871 
3872 
3873 BEGIN
3874 
3875  x_progress:='PROMISEDATECHANGE:000';
3876 
3877   IF (c_line_change%ISOPEN) THEN
3878      CLOSE c_line_change;
3879   ELSE
3880       OPEN c_line_change(p_po_header_id,p_change_group_id);
3881   END IF;
3882   FETCH c_line_change INTO l_line_changes_counter ;
3883   x_progress:='PROMISEDATECHANGE:001';
3884   log_message('PROMISEDATECHANGE','Price Changes Line Level',l_line_changes_counter);
3885 
3886   IF l_line_changes_counter >0 THEN
3887      CLOSE c_line_change;
3888      return FALSE;
3889   END IF;
3890 
3891   IF (c_line_change%ISOPEN) THEN
3892   CLOSE c_line_change;
3893   END IF;
3894 
3895  x_progress:='PROMISEDATECHANGE:002';
3896 
3897   IF (c_shipment_change%ISOPEN) THEN
3898   CLOSE c_shipment_change;
3899   else
3900   OPEN c_shipment_change(p_po_header_id,p_change_group_id);
3901   END IF;
3902   LOOP
3903   FETCH c_shipment_change INTO
3904         l_old_quantity,
3905         l_new_quantity,
3906         l_old_promised_date,
3907         l_new_promised_date,
3908         l_old_price,
3909         l_new_price,
3910         l_old_amount,
3911         l_new_amount;
3912   x_progress:='PROMISEDATECHANGE:003';
3913   log_message('PROMISEDATECHANGE','Quantity Changes',l_old_quantity || ', '||l_new_quantity);
3914   log_message('PROMISEDATECHANGE','Promise date Changes',l_old_promised_date || ', '||l_new_promised_date);
3915   log_message('PROMISEDATECHANGE','Shipment Price Changes',l_old_promised_date || ', '||l_new_promised_date);
3916   -- IF only a promise date change return TRUE otherwise return FALSE
3917   EXIT WHEN c_shipment_change%NOTFOUND;
3918   EXIT WHEN (l_only_promised_date_change=FALSE);
3919 
3920     IF nvl(l_old_quantity,0)<>nvl(l_new_quantity,0) THEN
3921            l_only_promised_date_change:= FALSE;
3922     ELSIF nvl(l_old_price,0)<>nvl(l_new_price,nvl(l_old_price,0)) THEN
3923            l_only_promised_date_change:= FALSE;
3924     ELSIF nvl(l_old_amount,0)<>nvl(l_new_amount,0) THEN
3925            l_only_promised_date_change:= FALSE;
3926     ELSIF (l_new_promised_date is null AND l_old_promised_date is null) THEN
3927            l_only_promised_date_change:= FALSE;
3928     END IF;
3929   END LOOP;
3930   CLOSE c_shipment_change;
3931   x_progress:='PROMISEDATECHANGE:004';
3932 return l_only_promised_date_change;
3933 exception
3934  when others THEN
3935    IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3936           FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3937                          g_module_prefix,
3938                          x_progress || ':unexpected error' || Sqlerrm);
3939    END IF;
3940 raise;
3941 END PROMISEDATECHANGE;
3942 
3943 ------------------------------------------------------------------------------
3944 
3945 PROCEDURE INITIATE_RCO_FLOW (itemtype        IN VARCHAR2,
3946    	                     itemkey         IN VARCHAR2,
3947    	                     actid           IN NUMBER,
3948    	                     funcmode        IN VARCHAR2,
3949                              resultout       OUT NOCOPY VARCHAR2)
3950 IS
3951 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
3952 l_po_header_id    po_headers_all.po_header_id%type;
3953 l_po_release_id   po_releases_all.po_release_id%type;
3954 x_progress        VARCHAR2(1000);
3955 
3956 BEGIN
3957 x_progress := 'INITIATE_RCO_FLOW:000';
3958 l_po_header_id   :=  wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3959                                                    itemkey  => itemkey,
3960                                                    aname    => 'PO_HEADER_ID');
3961 l_po_release_id   :=  wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3962                                                    itemkey  => itemkey,
3963                                                    aname    => 'PO_RELEASE_ID');
3964 
3965 
3966 x_progress := 'INITIATE_RCO_FLOW:001';
3967 log_message('INITIATE_RCO_FLOW','Po Header Id',l_po_header_id);
3968 INITIATERCOFLOW (l_po_header_id,l_po_release_id,l_change_group_id);
3969 -- set the x_change_request_group_id number
3970 x_progress:= 'INITIATE_RCO_FLOW:002';
3971 log_message('INITIATE_RCO_FLOW','Change Req Group Id',l_change_group_id);
3972 
3973 wf_engine.SetItemAttrNumber( itemtype => itemtype,
3974                              itemkey  => itemkey,
3975                              aname    => 'REQ_CHANGE_REQUEST_GROUP_ID',
3976                              avalue    => l_change_group_id);
3977 
3978 resultout:=wf_engine.eng_completed;
3979 x_progress := 'INITIATE_RCO_FLOW:003';
3980 
3981 EXCEPTION
3982 
3983 when others THEN
3984 
3985   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3986             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
3987                            g_module_prefix,
3988                            x_progress || ':unexpected error' || Sqlerrm);
3989   END IF;
3990   wf_core.context('POSCHORD', 'INITIATE_RCO_FLOW', itemtype, itemkey, to_char(actid), funcmode);
3991   raise;
3992 END INITIATE_RCO_FLOW;
3993 
3994 
3995 PROCEDURE   INITIATERCOFLOW(p_po_header_id IN NUMBER, p_po_release_id IN NUMBER, x_change_group_id OUT NOCOPY NUMBER)
3996 IS
3997 
3998   --- this also should hANDle non sync values
3999 CURSOR  c_change_request(p_po_header_id_csr IN NUMBER) IS
4000 SELECT  pcr.change_request_group_id,
4001 	pcr.new_price new_price,
4002 	pcr.new_quantity,
4003 	pcr.new_start_date,
4004 	pcr.new_expiration_date,
4005 	pcr.new_amount,
4006 	pcr.request_level,
4007         --pcr.new_promised_date,
4008 	nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
4009         pcr.request_reason,
4010 	prla.requisition_line_id,
4011 	prla.requisition_header_id,
4012 	prla.line_location_id,
4013 	prda.distribution_id
4014 FROM    po_change_requests pcr,
4015         po_requisition_lines_all prla,
4016 	po_req_distributions_all prda,
4017 	po_line_locations_all plla
4018 WHERE   document_header_id= p_po_header_id_csr
4019         AND request_status='BUYER_APP'
4020         AND change_active_flag='Y'
4021         AND initiator='SUPPLIER'
4022       --AND pcr.document_line_location_id = prla.line_location_id(+)
4023         AND prda.requisition_line_id=prla.requisition_line_id
4024         AND pcr.request_level='SHIPMENT'
4025         AND plla.line_location_id = prla.line_location_id
4026 	AND plla.po_line_id=pcr.document_line_id
4027 	AND plla.po_header_id=pcr.document_header_id
4028 	AND (   pcr.new_price    is not null    --New change JAI
4029 	     OR pcr.new_quantity is not null
4030 	     OR pcr.new_start_date is not null
4031 	     OR pcr.new_expiration_date is not null
4032 	     OR pcr.new_amount is not null
4033 	    )
4034 UNION
4035 SELECT  pcr.change_request_group_id,
4036 	pcr.new_price,
4037 	null new_quantity,
4038 	null new_start_date ,
4039 	null new_expiration_date,
4040 	null new_amount,
4041 	pcr.request_level,
4042       --null new_promised_date,
4043 	null new_need_by_date,
4044         pcr.request_reason,
4045 	prla.requisition_line_id,
4046 	prla.requisition_header_id,
4047 	prla.line_location_id,
4048 	null distribution_id
4049 FROM    po_change_requests pcr,
4050 	po_line_locations_all plla,
4051 	po_requisition_lines_all prla
4052 WHERE   document_header_id=p_po_header_id_csr
4053         AND request_status='BUYER_APP'
4054         AND change_active_flag='Y'
4055         AND initiator='SUPPLIER'
4056 	AND pcr.request_level='LINE'
4057 	AND plla.line_location_id = prla.line_location_id
4058 	AND plla.po_line_id=pcr.document_line_id
4059 	AND plla.po_header_id=pcr.document_header_id;
4060 
4061 --for releases
4062 
4063 CURSOR  c_change_request_rel(p_po_release_id_csr IN NUMBER) IS
4064 SELECT  pcr.change_request_group_id,
4065 	pcr.new_price new_price,
4066 	pcr.new_quantity,
4067 	pcr.new_start_date,
4068 	pcr.new_expiration_date,
4069 	pcr.new_amount,
4070 	pcr.request_level,
4071         --pcr.new_promised_date,
4072 	nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
4073         pcr.request_reason,
4074 	prla.requisition_line_id,
4075 	prla.requisition_header_id,
4076 	prla.line_location_id,
4077 	prda.distribution_id
4078 FROM    po_change_requests pcr,
4079         po_requisition_lines_all prla,
4080 	po_req_distributions_all prda,
4081 	po_line_locations_all plla
4082 WHERE   pcr.po_release_id= p_po_release_id_csr
4083         AND request_status='BUYER_APP'
4084         AND change_active_flag='Y'
4085         AND initiator='SUPPLIER'
4086       --AND pcr.document_line_location_id = prla.line_location_id(+)
4087         AND prda.requisition_line_id=prla.requisition_line_id
4088         AND pcr.request_level='SHIPMENT'
4089         AND plla.line_location_id = prla.line_location_id
4090 	AND plla.po_line_id=pcr.document_line_id
4091 	AND plla.po_header_id=pcr.document_header_id
4092     AND plla.po_release_id =pcr.po_release_id
4093 	AND (   pcr.new_price    is not null    --New change JAI
4094          OR pcr.new_quantity is not null
4095 	     OR pcr.new_start_date is not null
4096 	     OR pcr.new_expiration_date is not null
4097 	     OR pcr.new_amount is not null
4098 	    )
4099 UNION
4100 SELECT  pcr.change_request_group_id,
4101 	pcr.new_price,
4102 	null new_quantity,
4103 	null new_start_date ,
4104 	null new_expiration_date,
4105 	null new_amount,
4106 	pcr.request_level,
4107       --null new_promised_date,
4108 	null new_need_by_date,
4109         pcr.request_reason,
4110 	prla.requisition_line_id,
4111 	prla.requisition_header_id,
4112 	prla.line_location_id,
4113 	null distribution_id
4114 FROM    po_change_requests pcr,
4115 	po_line_locations_all plla,
4116 	po_requisition_lines_all prla
4117 WHERE   pcr.po_release_id=p_po_release_id_csr
4118         AND request_status='BUYER_APP'
4119         AND change_active_flag='Y'
4120         AND initiator='SUPPLIER'
4121 	AND pcr.request_level='LINE'
4122 	AND plla.line_location_id = prla.line_location_id
4123 	AND plla.po_line_id=pcr.document_line_id
4124 	AND plla.po_header_id=pcr.document_header_id;
4125 
4126 
4127 
4128 --l_change_group_id         po_change_requests.change_request_group_id%type ;
4129 l_change_group_id         po_change_requests.change_request_group_id%type ;
4130 l_new_price               po_change_requests.new_price%type ;
4131 l_new_quantity            po_change_requests.new_quantity%type ;
4132 l_new_start_date          po_change_requests.new_start_date%type  ;
4133 l_new_expiration_date     po_change_requests.new_expiration_date%type ;
4134 l_new_amount              po_change_requests.new_amount%type ;
4135 l_request_level           po_change_requests.request_level%type ;
4136 l_new_promised_date       po_change_requests.new_promised_date%type ;
4137 l_new_need_by_date        po_change_requests.new_need_by_date%type ;
4138 l_request_reason          po_change_requests.request_reason%type ;
4139 l_requisition_line_id     po_requisition_lines_all.requisition_line_id%type ;
4140 l_requisition_header_id   po_requisition_lines_all.requisition_header_id%type ;
4141 l_line_location_id        po_requisition_lines_all.line_location_id%type ;
4142 l_req_distribution_id     po_req_distributions_all.distribution_id%type ;
4143 l_change_table            PO_REQ_CHANGE_TABLE;
4144 l_cancel_table            PO_REQ_CANCEL_TABLE:=null;
4145 l_rec_count number :=0;
4146 l_req_hdr_id number;
4147 l_api_version number := 1.0;
4148 l_api_name varchar2(100) := 'INITIATERCOFLOW';
4149 x_progress varchar2(1000);
4150 l_po_line_id  number;
4151 x_return_status VARCHAR2(10);
4152 x_retMsg VARCHAR2(2000):='';
4153 x_errTable PO_REQ_CHANGE_ERR_TABLE;
4154 x_errCode VARCHAR2(10);
4155 l_dummy_table_number    po_tbl_number := po_tbl_number();
4156 
4157 /* Bug 7422622 - Added the following variables to check whether AME
4158 the setup is done.  If yes then clear the approval list. - Start*/
4159 
4160 l_application_id     number :=201;
4161 l_ame_transaction_type po_document_types.ame_transaction_type%TYPE;
4162 
4163 /* Bug 7422622 -  End */
4164 
4165 BEGIN
4166 
4167     x_retMsg :='';
4168     x_progress  := 'INITIATERCOFLOW:000';
4169 
4170     l_change_table:=PO_REQ_CHANGE_TABLE(
4171                req_line_id   =>   po_tbl_number(),
4172     	       req_dist_id   =>   po_tbl_number(),
4173     	       price         =>   po_tbl_number(),
4174     	       quantity      =>   po_tbl_number(),
4175     	       need_by       =>   po_tbl_date(),
4176     	       start_date    =>   po_tbl_date(),
4177     	       END_date      =>   po_tbl_date(),
4178     	       amount        =>   po_tbl_number(),
4179     	       type          =>   po_tbl_varchar60(),
4180                change_reason =>   po_tbl_VARCHAR2000());
4181 
4182     if  p_po_release_id is not null then
4183 
4184      OPEN c_change_request_rel (p_po_release_id);
4185           l_rec_count :=1;
4186          loop
4187 
4188 
4189          FETCH c_change_request_rel INTO
4190                         l_change_group_id,
4191                         l_new_price ,
4192 	 		l_new_quantity,
4193 	 		l_new_start_date,
4194 	 		l_new_expiration_date,
4195 	 		l_new_amount,
4196 	 		l_request_level,
4197 	 		--l_new_promised_date,
4198 	 		l_new_need_by_date,
4199 	 	        l_request_reason,
4200 	 		l_requisition_line_id,
4201 	 		l_requisition_header_id,
4202 	 		l_line_location_id,
4203 	                l_req_distribution_id;
4204 
4205 
4206        EXIT WHEN c_change_request_rel%NOTFOUND;
4207    x_progress  := 'INITIATERCOFLOW:001';
4208 
4209 --Filling the table with data
4210 
4211      l_change_table.req_line_id.extend(1);
4212      l_change_table.req_line_id(l_rec_count):=l_requisition_line_id;
4213      l_change_table.req_dist_id.extend(1);
4214      l_change_table.req_dist_id(l_rec_count):=l_req_distribution_id;
4215      l_change_table.price.extend(1);
4216      l_change_table.price(l_rec_count):=l_new_price;
4217      l_change_table.quantity.extend(1);
4218      l_change_table.quantity(l_rec_count):=l_new_quantity;
4219      l_change_table.need_by.extend(1);
4220      l_change_table.start_date.extend(1);
4221      l_change_table.END_date.extend(1);
4222      l_change_table.amount.extend(1);
4223      l_change_table.amount(l_rec_count):=l_new_amount;
4224      l_change_table.type.extend(1);
4225      l_change_table.change_reason.extend(1);
4226      l_change_table.change_reason(l_rec_count):=l_request_reason;
4227 
4228      l_rec_count:=l_rec_count+1;
4229 
4230      END  loop;
4231 
4232      CLOSE c_change_request_rel;
4233 
4234      else
4235 
4236       OPEN c_change_request(p_po_header_id);
4237                l_rec_count :=1;
4238               loop
4239 
4240 
4241               FETCH c_change_request INTO
4242                              l_change_group_id,
4243                              l_new_price ,
4244      	 		l_new_quantity,
4245      	 		l_new_start_date,
4246      	 		l_new_expiration_date,
4247      	 		l_new_amount,
4248      	 		l_request_level,
4249      	 		--l_new_promised_date,
4250      	 		l_new_need_by_date,
4251      	 	        l_request_reason,
4252      	 		l_requisition_line_id,
4253      	 		l_requisition_header_id,
4254      	 		l_line_location_id,
4255      	                l_req_distribution_id;
4256 
4257 
4258             EXIT WHEN c_change_request%NOTFOUND;
4259         x_progress  := 'INITIATERCOFLOW:001';
4260 
4261      --Filling the table with data
4262 
4263           l_change_table.req_line_id.extend(1);
4264           l_change_table.req_line_id(l_rec_count):=l_requisition_line_id;
4265           l_change_table.req_dist_id.extend(1);
4266           l_change_table.req_dist_id(l_rec_count):=l_req_distribution_id;
4267           l_change_table.price.extend(1);
4268           l_change_table.price(l_rec_count):=l_new_price;
4269           l_change_table.quantity.extend(1);
4270           l_change_table.quantity(l_rec_count):=l_new_quantity;
4271           l_change_table.need_by.extend(1);
4272           l_change_table.start_date.extend(1);
4273           l_change_table.END_date.extend(1);
4274           l_change_table.amount.extend(1);
4275           l_change_table.amount(l_rec_count):=l_new_amount;
4276           l_change_table.type.extend(1);
4277           l_change_table.change_reason.extend(1);
4278           l_change_table.change_reason(l_rec_count):=l_request_reason;
4279 
4280           l_rec_count:=l_rec_count+1;
4281 
4282           END  loop;
4283 
4284      CLOSE c_change_request;
4285 
4286      end if;
4287 
4288     x_progress  := 'INITIATERCOFLOW:002 Call Save Req Change';
4289 
4290     PO_RCO_VALIDATION_PVT.Save_ReqChange( l_api_version,
4291     		                          x_return_status,
4292       		           	          l_requisition_header_id,
4293      	                                  l_change_table ,
4294      	                                  l_cancel_table ,
4295      			                  x_change_group_id,
4296      			      	          x_retMsg ,
4297       			                  x_errTable);
4298 
4299    IF x_return_status IS NOT NULL AND  x_return_status = FND_API.g_ret_sts_success THEN
4300      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4301         FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4302                        g_module_prefix,
4303                        x_progress
4304                        || 'x_return_status=' || x_return_status
4305                        || 'x_change_group_id = '|| x_change_group_id);
4306      END IF;
4307 
4308    ELSE
4309      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4310        FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4311                        g_module_prefix,
4312                        x_progress
4313                        ||'x_return_status = ' || x_return_status
4314                        ||'x_retMsg = ' || x_retMsg);
4315      END IF;
4316    END IF;
4317 
4318    x_progress  := 'INITIATERCOFLOW:003';
4319 
4320    /* Bug 7422622 -- Check whether AME is setup for Requistion flow. If
4321    the setup is done then clear the approval list.*/
4322 
4323    SELECT ame_transaction_type
4324    INTO   l_ame_transaction_type
4325    FROM   po_document_types
4326    WHERE  document_type_code = 'CHANGE_REQUEST' and
4327           document_subtype = 'REQUISITION';
4328 
4329    if(l_ame_transaction_type is not null) then
4330 
4331      IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4332        fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'POS_SCO_TOLERANCE_PVT.INITIATERCOFLOW.invoked','l_ame_transaction_type = ' ||l_ame_transaction_type);
4333        fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'POS_SCO_TOLERANCE_PVT.INITIATERCOFLOW.invoked','l_requisition_header_id = ' ||l_requisition_header_id);
4334        fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'POS_SCO_TOLERANCE_PVT.INITIATERCOFLOW.invoked','applicationId = ' ||l_application_id);
4335      END IF;
4336 
4337      ame_api2.clearAllApprovals( applicationIdIn   => l_application_id,
4338                                  transactionIdIn   => l_requisition_header_id,
4339                                  transactionTypeIn => l_ame_transaction_type
4340                                 );
4341    end if;
4342    /* Bug 7422622 -  End */
4343 --- Ip requirement
4344      update PO_CHANGE_REQUESTS
4345      set Parent_change_request_id = x_change_group_id
4346      where change_request_group_id= l_change_group_id;
4347 
4348  Exception
4349 
4350  when others THEN
4351   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4352             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4353                            g_module_prefix,
4354                            x_progress || ':unexpected error' || Sqlerrm);
4355   END IF;
4356 
4357  raise;
4358 
4359 
4360 END INITIATERCOFLOW;
4361 ------------------------------------------------------------------------------
4362 
4363 
4364 PROCEDURE START_RCO_WORKFLOW (itemtype        IN VARCHAR2,
4365    	                      itemkey         IN VARCHAR2,
4366    	                      actid           IN NUMBER,
4367    	                      funcmode        IN VARCHAR2,
4368                               resultout       OUT NOCOPY VARCHAR2) is
4369 
4370 l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
4371 x_progress        VARCHAR2(1000);
4372 x_apprv_status    VARCHAR2(1);
4373 
4374 BEGIN
4375 
4376   x_progress := 'START_RCO_WORKFLOW:000';
4377 
4378     l_change_group_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
4379                                             itemkey => itemkey,
4380                                             aname => 'REQ_CHANGE_REQUEST_GROUP_ID');
4381 
4382   x_progress := 'START_RCO_WORKFLOW:001';
4383 
4384   /* Call the ip API with change_request_group_id to set the approval_required_flag */
4385 
4386   PO_RCOTOLERANCE_GRP.SET_APPROVAL_REQUIRED_FLAG(l_change_group_id,x_apprv_status);
4387 
4388 
4389   STARTRCOWORKFLOW (l_change_group_id);
4390 
4391   resultout:=wf_engine.eng_completed;
4392 
4393   x_progress := 'START_RCO_WORKFLOW:002';
4394 
4395 
4396 exception
4397 when others THEN
4398 
4399   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4400             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4401                            g_module_prefix,
4402                            x_progress || ':unexpected error' || Sqlerrm);
4403   END IF;
4404 
4405  -- The line below records this function call INthe error
4406  -- system INthe case of an exception.
4407 wf_core.context('POSCHORD', 'START_RCO_WORKFLOW', itemtype, itemkey, to_char(actid), funcmode);
4408 
4409  raise;
4410 
4411 
4412 END START_RCO_WORKFLOW;
4413 ------------------------------------------------------------------------------
4414 
4415 PROCEDURE   STARTRCOWORKFLOW(p_change_request_group_id IN NUMBER) is
4416 
4417 
4418 
4419     l_api_version number := 1.0;
4420     l_api_name  varchar2(100) := 'STARTRCOWF';
4421     x_return_status VARCHAR2(10);
4422     x_change_request_group_id number;
4423     x_retMsg VARCHAR2(2000):='';
4424     x_errTable PO_REQ_CHANGE_ERR_TABLE;
4425     x_errCode VARCHAR2(10);
4426     x_progress varchar2(1000);
4427     l_dummy_table_number    po_tbl_number := po_tbl_number();
4428 
4429 
4430 BEGIN
4431 
4432     x_retMsg :='';
4433     x_progress  := 'STARTRCOWORKFLOW:000 Call Submit Req Change';
4434 
4435  --   get the x_change_request_group_id
4436 
4437     PO_RCO_VALIDATION_PVT.Submit_ReqChange (l_api_version ,
4438                                             x_return_status,
4439                                             p_change_request_group_id,--x_change_request_group_id,
4440                                             'N',-- p_fundscheck_flag IN VARCHAR2,
4441                                             'Please',
4442                                             'SUPPLIER',
4443                                              x_retMsg ,
4444                                              x_errCode ,
4445                                              x_errTable );
4446 
4447    IF x_return_status IS NOT NULL AND  x_return_status = FND_API.g_ret_sts_success THEN
4448      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4449         FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4450                        g_module_prefix,
4451                        x_progress
4452                        || ' x_return_status=' || x_return_status);
4453      END IF;
4454 
4455    ELSE
4456      IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4457        FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4458                        g_module_prefix,
4459                        x_progress
4460                        ||' x_return_status = ' || x_return_status
4461                        ||' x_retMsg = ' || x_retMsg
4462                        ||' x_errCode = ' || x_errCode);
4463      END IF;
4464    END IF;
4465 
4466    x_progress  := 'STARTRCOWORKFLOW:001';
4467 
4468 
4469 
4470 Exception
4471 
4472  when others THEN
4473   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4474             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4475                            g_module_prefix,
4476                            x_progress || ':unexpected error' || Sqlerrm);
4477   END IF;
4478 
4479 
4480   raise;
4481 
4482 
4483 END STARTRCOWORKFLOW;
4484 ------------------------------------------------------------------------------
4485 
4486 PROCEDURE MARK_SCO_FOR_REQ(itemtype        IN VARCHAR2,
4487 		           itemkey         IN VARCHAR2,
4488         		   actid           IN NUMBER,
4489 	            	   funcmode        IN VARCHAR2,
4490                            resultout       OUT NOCOPY VARCHAR2)
4491 IS
4492 
4493  CURSOR  l_planners_csr(c_po_header_id IN NUMBER)
4494  IS
4495   select UNIQUE(porh.PREPARER_ID)
4496   from   po_requisition_headers_all porh,
4497          po_requisition_lines_all porl,
4498          po_headers_all poh,
4499          po_line_locations_all poll
4500   where  porh.requisition_header_id = porl.requisition_header_id AND
4501          porl.line_location_id = poll.line_location_id  AND
4502          poh.po_header_id = poll.po_header_id AND
4503          poh.po_header_id = c_po_header_id;
4504 --Bug 5053593.
4505   CURSOR l_requestors_csr(c_grp_id_csr IN NUMBER)
4506   IS
4507   select pda.deliver_to_person_id
4508   from
4509   	po_change_requests pcr,
4510   	po_distributions_all pda
4511   where pcr.change_request_group_id = c_grp_id_csr
4512   AND pcr.request_level = 'LINE'
4513   AND pcr.document_line_id = pda.po_line_id
4514   union
4515   select pda.deliver_to_person_id
4516   from
4517   	po_change_requests pcr,
4518   	po_distributions_all pda
4519   where pcr.change_request_group_id = c_grp_id_csr
4520   AND pcr.request_level = 'SHIPMENT'
4521   AND pcr.document_line_location_id = pda.line_location_id;
4522 
4523   l_change_group_id PO_CHANGE_REQUESTS.CHANGE_REQUEST_GROUP_ID%type;
4524   x_progress        VARCHAR2(1000);
4525   l_planner_username fnd_user.user_name%type;
4526   l_planner_disp_name VARCHAR2(2000);
4527   l_requester_username fnd_user.user_name%type;
4528   l_requester_disp_name VARCHAR2(2000);
4529   l_requester_id number;
4530   l_planner_id 	number;
4531   l_po_header_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
4532   count_rec NUMBER;
4533 BEGIN
4534 
4535   x_progress :='MARK_SCO_FOR_REQ:000';
4536 
4537 --Bug 11732340
4538 --Change group id to get the requester
4539     l_change_group_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
4540                                             itemkey => itemkey,
4541                                             aname => 'CHANGE_REQUEST_GROUP_ID');
4542 
4543   /*
4544   OPEN l_planners_csr(l_change_group_id );
4545      */
4546     l_po_header_id :=wf_engine.GetItemAttrNumber(itemtype => itemtype,
4547                                                  itemkey => itemkey,
4548                                                  aname => 'PO_HEADER_ID');
4549 
4550      count_rec := 0;
4551      OPEN l_planners_csr(l_po_header_id);
4552      LOOP
4553      FETCH l_planners_csr INTO l_planner_id;
4554      EXIT WHEN l_planners_csr%NOTFOUND;
4555      count_rec := count_rec + 1;
4556 
4557      END LOOP;
4558 
4559      IF (count_rec <> 1 )--Means there are more than 1 planners for the req
4560      THEN
4561      l_planner_id := NULL;
4562      END IF;
4563 
4564      close l_planners_csr;
4565 
4566   IF(l_planner_id is not null)  THEN
4567 
4568     x_progress:= 'MARK_SCO_FOR_REQ:001';
4569 
4570 
4571      -- Set the notIFication to be sent to the Requester
4572      wf_directory.GetUserName( p_orig_system    => 'PER',
4573                                p_orig_system_id => l_planner_id,
4574                                p_name           => l_planner_username,
4575                                p_display_name   => l_planner_disp_name);
4576 
4577     x_progress:= 'MARK_SCO_FOR_REQ:002';
4578     log_message('MARK_SCO_FOR_REQ','Planner User Name ',l_planner_username);
4579 
4580        wf_engine.SetItemAttrText(itemtype => itemtype,
4581                                 itemkey => itemkey,
4582                                 aname => 'PREPARER_USERNAME',
4583                                 avalue => l_planner_username);
4584   ELSE
4585     OPEN l_requestors_csr(l_change_group_id);
4586      FETCH l_requestors_csr INTO l_requester_id;
4587     close l_requestors_csr;
4588      x_progress:= 'MARK_SCO_FOR_REQ:003';
4589 
4590 
4591      wf_directory.GetUserName( p_orig_system    => 'PER',
4592                                p_orig_system_id => l_requester_id,
4593                                p_name           => l_requester_username,
4594                                p_display_name   => l_requester_disp_name);
4595 
4596      x_progress:= 'MARK_SCO_FOR_REQ:004';
4597      log_message('MARK_SCO_FOR_REQ','Requester User Name ',l_requester_username);
4598 
4599      wf_engine.SetItemAttrText(itemtype => itemtype,
4600                                 itemkey => itemkey,
4601                                 aname => 'PREPARER_USERNAME',
4602                                 avalue => l_requester_username);
4603   END IF;
4604       wf_engine.SetItemAttrText(itemtype => itemtype,
4605 	                        itemkey => itemkey,
4606 	                        aname => 'NOTIF_USAGE',
4607 	                        avalue =>'REQ');
4608        update po_change_requests
4609         set request_status ='REQ_APP',
4610             responded_by = fnd_global.user_id,
4611             response_date = sysdate
4612         where change_request_group_id = l_change_group_id
4613               AND request_status = 'PENDING';
4614 
4615 
4616   resultout:=wf_engine.eng_completed;
4617 
4618   x_progress := 'MARK_SCO_FOR_REQ:005';
4619 
4620 
4621 
4622 exception
4623 
4624 when others THEN
4625 
4626   IF( g_fnd_debug = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4627             FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
4628                            g_module_prefix,
4629                            x_progress || ':unexpected error' || Sqlerrm);
4630   END IF;
4631    wf_core.context('POSCHORD', 'MARK_SCO_FOR_REQ', itemtype, itemkey, to_char(actid), funcmode);
4632 
4633   raise;
4634 
4635 END MARK_SCO_FOR_REQ;
4636 
4637 ------------------------------------------------------------------------------
4638 
4639 
4640 END POS_SCO_TOLERANCE_PVT;