[Home] [Help]
PACKAGE BODY: APPS.PO_CHG_REQUEST_PVT
Source
1 PACKAGE BODY PO_CHG_REQUEST_PVT AS
2 /* $Header: POXPCHGB.pls 120.38.12020000.2 2013/02/10 17:33:12 vegajula ship $ */
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
5
6 -- Read the profile option that enables/disables the debug log
7 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8
9
10
11 /**
12 * Private Function: getAckNotifId
13 * Requires: PO_HEADER_ID , PO_RELEASE_ID
14 * Modifies: None
15 * Effects: Checks if there is an open Notification for Acknowledgement
16 * from Core PO for the Supplier
17 * Returns:
18 * Notification Id
19 */
20
21 function getAckNotifId (
22 p_po_header_id in number,
23 p_po_release_id in number,
24 x_activity_name out nocopy varchar2) RETURN NUMBER IS
25
26 v_nid NUMBER;
27 l_po_item_type PO_HEADERS_ALL.WF_ITEM_TYPE%TYPE;
28 l_po_item_key PO_HEADERS_ALL.WF_ITEM_KEY%TYPE;
29 l_message_name varchar2(100);
30
31
32 BEGIN
33 if p_po_release_id is null then
34 begin
35 select a.notification_id,poh.wf_item_type,poh.wf_item_key, a.message_name
36 INTO v_nid,l_po_item_type,l_po_item_key, l_message_name
37 from wf_notifications a, po_headers_all poh,
38 wf_item_activity_statuses wa
39 where poh.po_header_id = p_po_header_id and
40 poh.wf_item_key = wa.item_key and
41 poh.wf_item_type = wa.item_type
42 and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
43 and a.status = 'OPEN'
44 and a.notification_id = wa.notification_id
45 and wa.activity_status = 'NOTIFIED';
46 exception
47 when no_data_found then v_nid := null;
48 end;
49 else
50 begin
51 select a.notification_id,por.wf_item_type,por.wf_item_key, a.message_name
52 INTO v_nid,l_po_item_type,l_po_item_key, l_message_name
53 from wf_notifications a, po_releases_all por,
54 wf_item_activity_statuses wa
55 where por.po_release_id = p_po_release_id and
56 por.wf_item_key = wa.item_key and
57 por.wf_item_type = wa.item_type
58 and a.message_name in ('PO_EMAIL_PO_WITH_RESPONSE', 'PO_EMAIL_PO_PDF_WITH_RESPONSE')
59 and a.status = 'OPEN'
60 and a.notification_id = wa.notification_id
61 and wa.activity_status = 'NOTIFIED';
62 exception
63 when no_data_found then v_nid := null;
64 x_activity_name := null;
65 end;
66 end if;
67 if (l_message_name = 'PO_EMAIL_PO_WITH_RESPONSE') then
68 x_activity_name := 'NOTIFY_WEB_SUPPLIER_RESP';
69 elsif (l_message_name = 'PO_EMAIL_PO_PDF_WITH_RESPONSE') then
70 x_activity_name := 'NOTIFY_WEB_SUPP_PDF';
71 end if;
72
73 return v_nid;
74 END;
75
76 /**
77 * Private Function: getSigNotifId
78 * Requires: P_ITEM_KEY
79 * Modifies: None
80 * Effects: Checks if there is an open Notification for Signature for
81 * the Supplier
82 * Returns:
83 * Notification Id
84 */
85
86 function getSigNotifId (
87 p_item_type in VARCHAR2,
88 p_item_key in VARCHAR2
89 ) RETURN NUMBER IS
90
91 v_nid NUMBER;
92
93 BEGIN
94 begin
95 select a.notification_id
96 INTO v_nid
97 from wf_notifications a, wf_item_activity_statuses wa
98 where wa.item_key = p_item_key and
99 wa.item_type = p_item_type and
100 a.message_name = 'PO_SUPPLIER_SIGNATURE' and
101 a.status = 'OPEN' and
102 a.notification_id = wa.notification_id;
103
104 exception
105 when no_data_found then v_nid := null;
106 end;
107 return v_nid;
108 END;
109
110
111 /**
112 * Private Function: getRequestGroupId
113 * Requires: PO_HEADER_ID , PO_RELEASE_ID , Document_Type
114 * Modifies: None
115 * Effects: Gets the Request Group Id for Supplier Change Requests
116 * Returns:
117 * RequestGroupId
118 */
119
120 function getRequestGroupId (
121 p_po_header_id in number,
122 p_po_release_id in number,
123 p_document_type in varchar2) RETURN NUMBER IS
124
125 v_req_grp_id number;
126
127 cursor c1(p_po_header_id in number,p_document_type in varchar2) is
128 select change_request_group_id
129 from po_change_requests
130 where document_header_id = p_po_header_id and
131 request_status in ('NEW', 'PENDING') and
132 request_level in ('HEADER', 'LINE', 'SHIPMENT') and
133 document_type = p_document_type;
134
135 cursor c2(p_po_release_id in number, p_document_type in varchar2) is
136 select change_request_group_id
137 from po_change_requests
138 where po_release_id = p_po_release_id and
139 request_status in ('NEW', 'PENDING') and
140 request_level in ('HEADER', 'LINE', 'SHIPMENT') and
141 document_type = p_document_type;
142
143 BEGIN
144
145 if p_po_release_id is null then
146 begin
147 open c1(p_po_header_id,p_document_type);
148 fetch c1 into v_req_grp_id;
149 close c1;
150 exception
151 when others then
152 v_req_grp_id := null;
153 end;
154 else
155 begin
156 open c2(p_po_release_id,p_document_type);
157 fetch c2 into v_req_grp_id;
158 close c2;
159 exception
160 when others then
161 v_req_grp_id := null;
162 end;
163 end if;
164 /* if v_req_grp_id is null then
165 select po_chg_request_seq.nextval
166 into v_req_grp_id
167 from dual;
168 end if; */
169 return v_req_grp_id;
170 END;
171
172 /**
173 * Private Function: startSupplierWF
174 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,CHANGE_REQUEST_GROUP_ID,
175 ACCEPTANCE_REQUIRED_FLAG
176 * Modifies:
177 * Effects: This procedure checks whether PO Requires Acceptance notifications
178 are active and close those notifications with proper result. This
179 procedure also initiates the PO Change Order workflow process to send
180 the notification to the buyer about the supplier acknowledgement.
181 * Returns:
182 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
183 * FND_API.G_RET_STS_ERROR if an error occurs
184 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
185 *
186 */
187
188 function startSupplierWF
189 (p_po_header_id IN number,
190 p_po_release_id IN number,
191 p_revision_num IN number,
192 p_chg_request_grp_id IN number,
193 p_ack_reqd IN varchar2
194 ) RETURN VARCHAR2 IS
195
196 startWf varchar2(10);
197 ifAckReqd PO_HEADERS_ALL.acceptance_required_flag%TYPE;
198 l_nid NUMBER;
199 l_po_item_type PO_HEADERS_ALL.WF_ITEM_TYPE%TYPE;
200 l_po_item_key PO_HEADERS_ALL.WF_ITEM_KEY%TYPE;
201 l_api_name CONSTANT VARCHAR2(30) := 'startSupplierWF';
202 x_return_status VARCHAR2(10);
203 l_activity_name VARCHAR2(100);
204 l_document_type varchar2(10);
205 l_chg_request_grp_id number;
206
207 /* Start changes for 7172390 */
208 -- Added l_accepted_flag variable to get the accepted flag of the document.
209 -- Added Notif_Ack_Status variable to get the status of the document.
210 l_accepted_flag VARCHAR2(20);
211 Notif_Ack_Status VARCHAR2(100);
212 /* End changes for 7172390 */
213
214 BEGIN
215 -- Assume worflow needs to be started for every change request
216 startWf := FND_API.G_TRUE;
217 -- initialize return status
218 x_return_status := FND_API.G_RET_STS_SUCCESS;
219
220 -- Also update approved flag on the po_line_locations to restrict receiving
221 -- call workflow if this is the final action
222
223 if p_po_release_id is null then
224 select wf_item_key,wf_item_type
225 into l_po_item_key,l_po_item_type
226 from po_headers_all
227 where po_header_id = p_po_header_id;
228 else
229 select wf_item_key,wf_item_type
230 into l_po_item_key,l_po_item_type
231 from po_releases_all
232 where po_release_id = p_po_release_id;
233 end if;
234
235 if(p_ack_reqd = 'Y') then
236 --Retrieve the Acknowledgement Notification sent thru core po
237 l_nid := getAckNotifId(p_po_header_id,p_po_release_id, l_activity_name);
238 -- Close the notification sent thru core po
239 startWf := po_acknowledge_po_grp.all_shipments_responded (
240 1.0,FND_API.G_FALSE,p_po_header_id , p_po_release_id, p_revision_num );
241 if l_nid is not null then
242 begin
243 /* Start changes for 7172390 */
244 if(startWf = FND_API.G_TRUE) THEN
245 BEGIN
246 /* l_accepted_flag and Notif_Ack_Status will have the following values respectively.
247 'Y' for Accepted staus.
248 'N' for Rejected status.
249 'A' for Acknowledged staus.
250 None of the above then Supplier Change Pending status.
251 */
252 if p_po_release_id is null then
253 SELECT ACCEPTED_FLAG
254 INTO l_accepted_flag
255 FROM po_acceptances
256 WHERE po_header_id = p_po_header_id
257 AND REVISION_NUM = p_revision_num
258 AND PO_LINE_LOCATION_ID IS NULL
259 AND ACCEPTING_PARTY='S';
260 ELSE
261 SELECT ACCEPTED_FLAG
262 INTO l_accepted_flag
263 FROM po_acceptances
264 WHERE po_release_id = p_po_release_id
265 AND REVISION_NUM = p_revision_num
266 AND PO_LINE_LOCATION_ID IS NULL
267 AND ACCEPTING_PARTY='S';
268 END IF;
269 EXCEPTION
270 WHEN No_Data_Found THEN
271 Notif_Ack_Status := FND_MESSAGE.GET_STRING('POS','POS_PO_SUP_CHANGE');
272 END;
273 IF l_accepted_flag = 'Y' THEN
274 Notif_Ack_Status := FND_MESSAGE.GET_STRING('POS','POS_ACCEPTED');
275 elsif l_accepted_flag = 'N' THEN
276 Notif_Ack_Status := FND_MESSAGE.GET_STRING('POS','POS_REJECTED');
277 elsif l_accepted_flag = 'A' THEN
278 Notif_Ack_Status := FND_MESSAGE.GET_STRING('POS','POS_PO_ACKNOWLEDGED');
279 ELSE
280 Notif_Ack_Status := FND_MESSAGE.GET_STRING('POS','POS_PO_SUP_CHANGE');
281 END IF;
282 ELSE
283 Notif_Ack_Status := FND_MESSAGE.GET_STRING('POS','POS_PO_PARTIALLY_ACKED');
284 END IF;
285 wf_engine.completeActivity(l_po_item_type,
286 l_po_item_key,
287 l_activity_name,
288 --'NOTIFY_WEB_SUPPLIER_RESP',
289 Notif_Ack_Status);
290 /* End changes for 7172390 */
291 exception
292 when others then
293 raise;
294 end;
295 end if;
296 else
297 startWf := FND_API.G_TRUE;
298 end if;
299
300 -- Call workflow
301 if(startWf = FND_API.G_TRUE) then
302 /* Handle the change request group id for cases when supplier completes the acknowledgement after
303 asking for a change initially bug 4872348 */
304 if(p_chg_request_grp_id is null) then
305 if (p_po_release_id is not null) then
306 l_document_type := 'RELEASE';
307 else
308 l_document_type := 'PO';
309 end if;
310
311 l_chg_request_grp_id := getRequestGroupId (p_po_header_id => p_po_header_id,
312 p_po_release_id => p_po_release_id,
313 p_document_type => l_document_type );
314 else
315 l_chg_request_grp_id := p_chg_request_grp_id;
316 end if; -- end of fix
317
318 po_sup_chg_request_wf_grp.StartSupplierChangeWF(
319 1.0,x_return_status,p_po_header_id ,p_po_release_id,
320 p_revision_num, l_chg_request_grp_id ,p_ack_reqd);
321 end if;
322
323 return x_return_status;
324
325 exception
326 WHEN OTHERS THEN
327 raise;
328 x_return_status := FND_API.g_ret_sts_unexp_error;
329 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
330 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
331 IF g_fnd_debug = 'Y' THEN
332 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
333 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
334 l_api_name || '.others_exception', sqlcode);
335 END IF;
336 END IF;
337 END IF;
338 return x_return_status;
339 end startSupplierWF;
340
341 /**
342 * Private Function: startSignatureWF
343 * Requires: PO_HEADER_ID , PO_RELEASE_ID ,
344 * Modifies: None
345 * Effects: Initiates the supplier Signature workflow
346 * Returns:
347 * x_return_status
348 */
349
350 function startSignatureWF (
351 p_item_type IN VARCHAR2,
352 p_item_key IN VARCHAR2,
353 p_po_header_id IN NUMBER,
354 p_revision_num IN NUMBER,
355 p_document_type IN VARCHAR2,
356 p_document_subtype IN VARCHAR2,
357 p_document_number IN VARCHAR2,
358 p_org_id IN NUMBER,
359 p_Agent_Id IN NUMBER,
360 p_supplier_user_id IN NUMBER
361 ) RETURN VARCHAR2 IS
362
363 l_nid NUMBER;
364 l_api_name CONSTANT VARCHAR2(30) := 'startSignatureWF';
365 x_return_status VARCHAR2(10);
366 l_supplier_username fnd_user.user_name%type;
367 n_varname Wf_Engine.NameTabTyp;
368 n_varval Wf_Engine.NumTabTyp;
369 t_varname Wf_Engine.NameTabTyp;
370 t_varval Wf_Engine.TextTabTyp;
371 l_supplier_displayname VARCHAR2(240);
372
373 BEGIN
374 -- initialize return status
375 x_return_status := FND_API.G_RET_STS_SUCCESS;
376 if (p_item_key is null ) then
377 x_return_status := FND_API.g_ret_sts_unexp_error;
378 return x_return_status;
379 else
380
381 wf_engine.createProcess ( ItemType => p_item_type,
382 ItemKey => p_item_key,
383 Process => 'DOCUMENT_SIGNATURE_PROCESS');
384
385 -- Get Supplier User Name
386
387 WF_DIRECTORY.GetUserName( 'FND_USR',
388 p_supplier_user_id,
389 l_supplier_username,
390 l_supplier_displayname);
391
392 -- Set Workflow Attributes
393 n_varname(1) := 'DOCUMENT_ID';
394 n_varval(1) := p_po_header_id;
395 n_varname(2) := 'SUPPLIER_USER_ID';
396 n_varval(2) := p_supplier_user_id;
397 n_varname(3) := 'PO_REVISION_NUM';
398 n_varval(3) := p_revision_num;
399 n_varname(4) := 'ORG_ID';
400 n_varval(4) := p_org_id;
401 n_varname(5) := 'BUYER_EMPLOYEE_ID';
402 n_varval(5) := p_agent_id;
403
404 t_varname(1) := 'DOCUMENT_TYPE';
405 t_varval(1) := p_document_type;
406 t_varname(2) := 'DOCUMENT_SUBTYPE';
407 t_varval(2) := p_document_subtype;
408 t_varname(3) := 'DOCUMENT_NUMBER';
409 t_varval(3) := p_document_number;
410 t_varname(4) := 'SUPPLIER_USER_NAME';
411 t_varval(4) := l_supplier_username;
412
413 Wf_Engine.SetItemAttrNumberArray(p_item_type, p_item_key,n_varname,n_varval);
414 Wf_Engine.SetItemAttrTextArray(p_item_type, p_item_key,t_varname,t_varval);
415
416 wf_engine.StartProcess(ItemType => p_item_type,
417 ItemKey => p_item_key);
418 -- DO explicit commit
419 commit;
420 return x_return_status;
421 end if;
422 exception
423 WHEN OTHERS THEN
424 raise;
425 x_return_status := FND_API.g_ret_sts_unexp_error;
426 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
427 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
428 IF g_fnd_debug = 'Y' THEN
429 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
430 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
431 l_api_name || '.others_exception', sqlcode);
432 END IF;
433 END IF;
434 END IF;
435 return x_return_status;
436 end startSignatureWF;
437
438
439 /**
440 * Private Function: startSignatureWF
441 * Requires: PO_HEADER_ID , PO_RELEASE_ID ,
442 * Modifies: None
443 * Effects: Initiates the supplier Signature workflow
444 * Returns:
445 * x_return_status
446 */
447
448 function startSignatureWF (
449 p_item_type IN VARCHAR2,
450 p_item_key IN VARCHAR2,
451 p_po_header_id IN NUMBER,
452 p_revision_num IN NUMBER,
453 p_document_type IN VARCHAR2,
454 p_document_subtype IN VARCHAR2,
455 p_document_number IN VARCHAR2,
456 p_org_id IN NUMBER,
457 p_Agent_Id IN NUMBER,
458 p_supplier_user_id IN NUMBER,
459 p_draft_id IN NUMBER
460 ) RETURN VARCHAR2 IS
461
462 l_nid NUMBER;
463 l_api_name CONSTANT VARCHAR2(30) := 'startSignatureWF';
464 x_return_status VARCHAR2(10);
465 l_supplier_username fnd_user.user_name%type;
466 n_varname Wf_Engine.NameTabTyp;
467 n_varval Wf_Engine.NumTabTyp;
468 t_varname Wf_Engine.NameTabTyp;
469 t_varval Wf_Engine.TextTabTyp;
470 l_supplier_displayname VARCHAR2(240);
471
472 BEGIN
473 -- initialize return status
474 x_return_status := FND_API.G_RET_STS_SUCCESS;
475 if (p_item_key is null ) then
476 x_return_status := FND_API.g_ret_sts_unexp_error;
477 return x_return_status;
478 else
479
480 wf_engine.createProcess ( ItemType => p_item_type,
481 ItemKey => p_item_key,
482 Process => 'DOCUMENT_SIGNATURE_PROCESS');
483
484 -- Get Supplier User Name
485
486 WF_DIRECTORY.GetUserName( 'FND_USR',
487 p_supplier_user_id,
488 l_supplier_username,
489 l_supplier_displayname);
490
491 -- Set Workflow Attributes
492 n_varname(1) := 'DOCUMENT_ID';
493 n_varval(1) := p_po_header_id;
494 n_varname(2) := 'SUPPLIER_USER_ID';
495 n_varval(2) := p_supplier_user_id;
496 n_varname(3) := 'PO_REVISION_NUM';
497 n_varval(3) := p_revision_num;
498 n_varname(4) := 'ORG_ID';
499 n_varval(4) := p_org_id;
500 n_varname(5) := 'BUYER_EMPLOYEE_ID';
501 n_varval(5) := p_agent_id;
502 n_varname(6) := 'DRAFT_ID';
503 n_varval(6) := p_draft_id;
504
505
506 t_varname(1) := 'DOCUMENT_TYPE';
507 t_varval(1) := p_document_type;
508 t_varname(2) := 'DOCUMENT_SUBTYPE';
509 t_varval(2) := p_document_subtype;
510 t_varname(3) := 'DOCUMENT_NUMBER';
511 t_varval(3) := p_document_number;
512 t_varname(4) := 'SUPPLIER_USER_NAME';
513 t_varval(4) := l_supplier_username;
514
515 Wf_Engine.SetItemAttrNumberArray(p_item_type, p_item_key,n_varname,n_varval);
516 Wf_Engine.SetItemAttrTextArray(p_item_type, p_item_key,t_varname,t_varval);
517
518 wf_engine.StartProcess(ItemType => p_item_type,
519 ItemKey => p_item_key);
520 -- DO explicit commit
521 commit;
522 return x_return_status;
523 end if;
524 exception
525 WHEN OTHERS THEN
526 raise;
527 x_return_status := FND_API.g_ret_sts_unexp_error;
528 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
529 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
530 IF g_fnd_debug = 'Y' THEN
531 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
532 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
533 l_api_name || '.others_exception', sqlcode);
534 END IF;
535 END IF;
536 END IF;
537 return x_return_status;
538 end startSignatureWF;
539
540 /**
541 * Public Procedure: save_request
542 * Requires: API message list has been initialized if p_init_msg_list is false.
543 * Modifies: API message list
544 * Effects: Saves Data to the Change Request Table
545 * Returns:
546 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
547 * FND_API.G_RET_STS_ERROR if an error occurs
548 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
549 */
550
551 procedure save_request(
552 p_api_version IN NUMBER,
553 p_Init_Msg_List IN VARCHAR2,
554 x_return_status OUT NOCOPY VARCHAR2,
555 p_po_header_id IN NUMBER,
556 p_po_release_id IN NUMBER,
557 p_revision_num IN NUMBER,
558 p_po_change_requests IN pos_chg_rec_tbl,
559 x_request_group_id OUT NOCOPY NUMBER,
560 p_chn_int_cont_num IN varchar2 default null,
561 p_chn_source IN varchar2 default null,
562 p_chn_requestor_username in varchar2 default null,
563 p_user_id IN number default null,
564 p_login_id IN number default null) IS
565
566 rec_cnt number;
567 p_chg_request_grp_id number;
568 x_return_code varchar2(40);
569 v_request_group_id number;
570 accp_flag char(1);
571 v_buyer_id number;
572 v_document_type po_change_requests.DOCUMENT_TYPE%TYPE;
573 l_user_id NUMBER := fnd_global.user_id;
574 l_login_id NUMBER := fnd_global.login_id;
575 l_api_name CONSTANT VARCHAR2(30) := 'save_request';
576 l_api_version_number CONSTANT NUMBER := 1.0;
577
578 BEGIN
579 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
580 -- initialize message list
581 FND_MSG_PUB.initialize;
582 END IF;
583
584 -- Standard call to check for call compatibility.
585
586 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
587 p_api_version,
588 l_api_name,
589 G_PKG_NAME)
590 THEN
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594 -- initialize return status
595 x_return_status := FND_API.G_RET_STS_SUCCESS;
596
597 if (p_user_id is not null) then
598 l_user_id := p_user_id;
599 l_login_id := p_login_id;
600 end if;
601
602 rec_cnt := p_po_change_requests.count();
603 -- get the ChangerequestGroupID only when changes are requested
604 IF( rec_cnt > 0) THEN
605 -- Get the document type of the first record to get the unique request group id
606 if (p_po_release_id is not null) then
607 v_document_type := 'RELEASE';
608 else
609 v_document_type := 'PO';
610 end if;
611
612 v_request_group_id := getRequestGroupId(p_po_header_id,p_po_release_id,v_document_type);
613 IF(v_request_group_id is null) THEN
614 select po_chg_request_seq.nextval
615 into v_request_group_id
616 from dual;
617 END IF;
618 x_request_group_id := v_request_group_id;
619 END IF;
620
621 FOR i in 1..rec_cnt LOOP
622 if(p_po_change_requests(i).action_type not in ('ACCEPT','REJECT')) then
623
624 insert into po_change_requests(
625 change_request_group_id, change_request_id,
626 initiator, action_type, request_reason,
627 request_level, request_status, document_type,
628 document_header_id, document_num,
629 document_revision_num, po_release_id,
630 created_by, creation_date,last_updated_by,last_update_date,
631 last_update_login,document_line_id, document_line_number,
632 document_line_location_id, document_shipment_number,
633 parent_line_location_id, document_distribution_id,
634 document_distribution_number,
635 old_quantity, new_quantity,
636 old_promised_date, new_promised_date,
637 old_supplier_part_number, new_supplier_part_number,
638 old_price, new_price, old_need_by_date, new_need_by_date,
639 old_supplier_reference_number, new_supplier_reference_number,
640 Approval_Required_Flag,Parent_Change_request_Id,
641 Requester_Id ,
642 OLD_SUPPLIER_ORDER_NUMBER , NEW_SUPPLIER_ORDER_NUMBER,
643 OLD_SUPPLIER_ORDER_LINE_NUMBER , NEW_SUPPLIER_ORDER_LINE_NUMBER,
644 change_active_flag, MSG_CONT_NUM, REQUEST_ORIGIN,ADDITIONAL_CHANGES,
645 OLD_START_DATE,NEW_START_DATE,OLD_EXPIRATION_DATE,NEW_EXPIRATION_DATE,
646 OLD_AMOUNT,NEW_AMOUNT,
647 SUPPLIER_DOC_REF, SUPPLIER_LINE_REF, SUPPLIER_SHIPMENT_REF, --added in FPJ for splits.
648 NEW_PROGRESS_TYPE,NEW_PAY_DESCRIPTION --<< Complex work changes for R12 >>
649
650 )
651 values (x_request_group_id,po_chg_request_seq.nextval,
652 p_po_change_requests(i).initiator,
653 p_po_change_requests(i).action_type,
654 p_po_change_requests(i).request_reason,
655 p_po_change_requests(i).request_level,
656 p_po_change_requests(i).request_status,
657 p_po_change_requests(i).document_type,
658 p_po_change_requests(i).document_header_id,
659 p_po_change_requests(i).document_num,
660 to_number(p_po_change_requests(i).document_revision_num),
661 p_po_change_requests(i).po_release_id,
662 l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663 p_po_change_requests(i).document_line_id,
664 p_po_change_requests(i).document_line_number,
665 p_po_change_requests(i).document_line_location_id,
666 p_po_change_requests(i).document_shipment_number,
667 p_po_change_requests(i).parent_line_location_id,
668 p_po_change_requests(i).document_distribution_id,
669 p_po_change_requests(i).document_distribution_number,
670 p_po_change_requests(i).old_quantity,
671 p_po_change_requests(i).new_quantity,
672 p_po_change_requests(i).old_promised_date,
673 p_po_change_requests(i).new_promised_date,
674 p_po_change_requests(i).old_supplier_part_number,
675 p_po_change_requests(i).new_supplier_part_number,
676 p_po_change_requests(i).old_price,
677 p_po_change_requests(i).new_price,
678 p_po_change_requests(i).old_need_by_date,
679 p_po_change_requests(i).new_need_by_date,
680 p_po_change_requests(i).old_supplier_reference_number,
681 p_po_change_requests(i).new_supplier_reference_number,
682 p_po_change_requests(i).Approval_Required_Flag,
683 p_po_change_requests(i).Parent_Change_request_Id,
684 p_po_change_requests(i).Requester_id,
685 p_po_change_requests(i).Old_Supplier_Order_Number,
686 p_po_change_requests(i).New_Supplier_Order_Number,
687 p_po_change_requests(i).Old_Supplier_Order_Line_Number,
688 p_po_change_requests(i).New_Supplier_Order_Line_Number,
689 decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
690 p_chn_int_cont_num,
691 p_chn_source,
692 p_po_change_requests(i).Additional_changes,
693 p_po_change_requests(i).old_start_date,
694 p_po_change_requests(i).new_start_date,
695 p_po_change_requests(i).old_expiration_date,
696 p_po_change_requests(i).new_expiration_date,
697 p_po_change_requests(i).old_amount,
698 p_po_change_requests(i).new_amount,
699 p_po_change_requests(i).SUPPLIER_DOC_REF,
700 p_po_change_requests(i).SUPPLIER_LINE_REF,
701 p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
702 p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
703 p_po_change_requests(i).NEW_PAY_DESCRIPTION
704
705 );
706 end if;
707 end loop;
708
709 EXCEPTION
710 WHEN FND_API.g_exc_error THEN
711 x_return_status := FND_API.g_ret_sts_error;
712 WHEN FND_API.g_exc_unexpected_error THEN
713 x_return_status := FND_API.g_ret_sts_unexp_error;
714 WHEN OTHERS THEN
715 x_return_status := FND_API.g_ret_sts_unexp_error;
716
717 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
718 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
719 IF g_fnd_debug = 'Y' THEN
720 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
721 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
722 l_api_name || '.others exception' ,sqlcode);
723 END IF;
724 END IF;
725 END IF;
726
727 END save_request;
728
729 /**
730 * Public Procedure: process_supplier_request
731 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,POS_CHG_REC_TBL
732 * Modifies:
733 * Effects: Processes the change Request and calls PO Doc Submission Check
734 * Returns:
735 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
736 * FND_API.G_RET_STS_ERROR if an error occurs
737 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
738 * POS_ERR_TYPE
739 */
740
741 procedure process_supplier_request (
742 p_po_header_id IN number,
743 p_po_release_id IN number,
744 p_revision_num IN number,
745 p_po_change_requests IN pos_chg_rec_tbl,
746 x_online_report_id OUT NOCOPY number,
747 x_pos_errors OUT NOCOPY POS_ERR_TYPE,
748 p_chn_int_cont_num IN varchar2 default null,
749 p_chn_source IN varchar2 default null,
750 p_chn_requestor_username in varchar2 default null,
751 p_user_id IN number default null,
752 p_login_id IN number default null,
753 p_last_upd_date IN date default null,
754 p_mpoc IN varchar2 default FND_API.G_FALSE) IS
755
756 x_error_code varchar2(40);
757 no_rec_found exception;
758 v_auth_status PO_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
759 x_progress varchar2(3) := '000';
760 l_api_version_number CONSTANT NUMBER := 1.0;
761 l_api_name CONSTANT VARCHAR2(30) := 'process_supplier_request';
762 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
763 l_msg_count NUMBER;
764 l_msg_data VARCHAR2(2000);
765 l_commit VARCHAR2(1) := FND_API.G_FALSE;
766 l_user_id NUMBER;
767 l_login_id NUMBER;
768 l_request_group_id NUMBER :=null;
769 x_return_status varchar2(20);
770 updatePoAttr boolean := false;
771 saveRequest boolean := false;
772 callWf boolean := false;
773 l_po_change_requests pos_chg_rec_tbl := NULL;
774 vAckTbl pos_ack_rec_tbl := pos_ack_rec_tbl();
775 ack_cnt number :=0;
776 callDocCheck boolean :=false;
777 accp_flag char(1);
778 v_buyer_id number;
779 x_accp_flag po_headers_all.acceptance_required_flag%type;
780 l_err_msg_name_tbl po_tbl_varchar30;
781 l_err_msg_text_tbl po_tbl_varchar2000;
782 l_last_upd_date po_headers_all.last_update_date%type;
783 l_count_asn NUMBER;
784 l_ret_sts varchar2(1);
785
786
787 CURSOR PO_CSR(p_po_header_id in number) IS
788 SELECT last_update_date
789 FROM PO_HEADERS_ALL
790 WHERE PO_HEADER_ID = p_po_header_id
791 FOR UPDATE of last_update_date NOWAIT;
792
793 poRec PO_CSR%ROWTYPE;
794
795 CURSOR REL_CSR(p_po_release_id in number) IS
796 SELECT last_update_date
797 FROM PO_RELEASES_ALL
798 WHERE PO_RELEASE_ID = p_po_release_id
799 FOR UPDATE of last_update_date NOWAIT;
800
801 relRec REL_CSR%ROWTYPE;
802
803 BEGIN
804 -- initialize return status
805 x_return_status := FND_API.G_RET_STS_SUCCESS;
806 x_progress := '001';
807
808
809 -- Lock the PO Header Row for update of Last Update Date
810 if (p_po_release_id is not null ) then
811 BEGIN
812 OPEN REL_CSR(p_po_release_id);
813 FETCH REL_CSR INTO relRec;
814 l_last_upd_date := relRec.last_update_date;
815 if (REL_CSR%NOTFOUND) then
816 CLOSE REL_CSR;
817 IF (g_fnd_debug = 'Y') THEN
818 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
819 FND_LOG.string(FND_LOG.level_error, g_module_prefix || 'process_supplier_request ', ' Record dosent exist for po_release_id = ' || p_po_release_id);
820 END IF;
821 END IF;
822 end if;
823 CLOSE REL_CSR;
824 EXCEPTION
825 WHEN OTHERS THEN
826 if (sqlcode = '-54') then
827 l_err_msg_name_tbl := po_tbl_varchar30();
828 l_err_msg_text_tbl := po_tbl_varchar2000();
829 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
830 x_pos_errors.message_name.extend;
831 x_pos_errors.text_line.extend;
832 x_pos_errors.message_name(1) := null;
833 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_LOCKED_PO_ROW');
834 return;
835 end if;
836 END;
837 else
838 BEGIN
839 OPEN PO_CSR(p_po_header_id);
840 FETCH PO_CSR INTO poRec;
841 l_last_upd_date := poRec.last_update_date;
842 if (PO_CSR%NOTFOUND) then
843 CLOSE PO_CSR;
844 IF (g_fnd_debug = 'Y') THEN
845 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
846 FND_LOG.string(FND_LOG.level_error, g_module_prefix || 'process_supplier_request', 'Record dosent exist for po_header_id = ' || p_po_header_id);
847 END IF;
848 END IF;
849 end if;
850 CLOSE PO_CSR;
851 EXCEPTION
852 WHEN OTHERS THEN
853 if (sqlcode = '-54') then
854 l_err_msg_name_tbl := po_tbl_varchar30();
855 l_err_msg_text_tbl := po_tbl_varchar2000();
856 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
857 x_pos_errors.message_name.extend;
858 x_pos_errors.text_line.extend;
859 x_pos_errors.message_name(1) := null;
860 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_LOCKED_PO_ROW');
861
862 return;
863 end if;
864 END;
865 end if;
866
867 -- Check if the same record is being update
868 -- Check against last_updated_date to make sure that
869 -- The record that was queried is being updated
870
871 if (p_last_upd_date <> l_last_upd_date) then
872 l_err_msg_name_tbl := po_tbl_varchar30();
873 l_err_msg_text_tbl := po_tbl_varchar2000();
874 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
875 x_pos_errors.message_name.extend;
876 x_pos_errors.text_line.extend;
877 x_pos_errors.message_name(1) := null;
878 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_MODIFIED_PO_ROW');
879 return;
880 end if;
881 -- Copy the request into a local var
882 l_po_change_requests := p_po_change_requests;
883
884 IF g_fnd_debug = 'Y' THEN
885 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
886 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
887 '.invoked', 'Type: ' ||
888 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
889 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
890 END IF;
891 END IF;
892
893 validate_shipment_cancel (
894 p_po_header_id,
895 p_po_release_id,
896 p_po_change_requests,
897 x_pos_errors,
898 l_ret_sts);
899 if(l_ret_sts = 'Y') then
900 return;
901 end if;
902
903 if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
904 l_po_change_requests(1).request_level='HEADER' ) then
905 if (l_po_change_requests.count > 1 ) then
906 x_return_status := FND_API.g_ret_sts_unexp_error;
907
908 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
909 IF g_fnd_debug = 'Y' THEN
910 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
911 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
912 l_api_name || fnd_message.get_string('PO', 'POS_MULT_HDR_CANCEL_REQ'), sqlcode);
913 END IF;
914 END IF;
915
916
917
918 l_err_msg_name_tbl := po_tbl_varchar30();
919 l_err_msg_text_tbl := po_tbl_varchar2000();
920 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
921
922
923
924 x_pos_errors.message_name.extend;
925 x_pos_errors.text_line.extend;
926 x_pos_errors.message_name(1) := null;
927 /*
928 POS_MULT_HDR_CANCEL_REQ = 'Multiple requests are made in context with Header level cancel.'
929 */
930
931 x_pos_errors.text_line(1) := fnd_message.get_string('PO', 'POS_MULT_HDR_CANCEL_REQ');
932
933 return;
934 end if;
935 save_cancel_request(
936 p_api_version => 1.0 ,
937 p_Init_Msg_List => FND_API.G_FALSE,
938 x_return_status => l_return_status,
939 p_po_header_id => p_po_header_id,
940 p_po_release_id => p_po_release_id,
941 p_revision_num => p_revision_num,
942 p_po_change_requests => l_po_change_requests,
943 x_request_group_id => l_request_group_id
944 );
945 x_online_report_id := 0;
946 if (l_return_status <> FND_API.g_ret_sts_success) then
947 l_err_msg_name_tbl := po_tbl_varchar30();
948 l_err_msg_text_tbl := po_tbl_varchar2000();
949 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
950
951
952 x_pos_errors.message_name.extend;
953 x_pos_errors.text_line.extend;
954 x_pos_errors.message_name(1) := null;
955 /*
956 POS_SAVE_CANCEL_REQ_ERR = 'Error while saving the cancel request: '
957 */
958
959 x_pos_errors.text_line(1) :=
960 fnd_message.get_string('PO', 'POS_SAVE_CANCEL_REQ_ERR') ||
961 FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F');
962
963
964 end if;
965
966 return;
967
968 end if;
969
970 if p_po_release_id is null then
971 select agent_id,nvl(acceptance_required_flag,'N')
972 into v_buyer_id,x_accp_flag
973 from po_headers_all
974 where po_header_id = p_po_header_id;
975 else
976 select agent_id,nvl(acceptance_required_flag,'N')
977 into v_buyer_id,x_accp_flag
978 from po_releases_all
979 where po_release_id = p_po_release_id;
980 end if;
981
982 FOR i in 1..l_po_change_requests.count()
983 LOOP
984 if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
985 l_po_change_requests(i).request_level='SHIPMENT' ) then
986 callWf := true;
987 --updatePoAttr := false;
988 --callDocCheck := false;
989
990 if (l_po_change_requests(i).action_type = 'ACCEPT') then
991 accp_flag := 'Y';
992 else
993 accp_flag := 'N';
994 end if;
995
996 -- Process The Acknowledgements
997
998 PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
999 1.0,FND_API.G_FALSE,x_return_status,
1000 l_po_change_requests(i).document_line_location_id,
1001 l_po_change_requests(i).document_header_id,
1002 l_po_change_requests(i).po_release_id,
1003 l_po_change_requests(i).document_revision_num,
1004 accp_flag,
1005 l_po_change_requests(i).request_reason,
1006 v_buyer_id, fnd_global.user_id);
1007
1008 elsif (l_po_change_requests(i).request_level='LINE' AND
1009 l_po_change_requests(i).action_type='MODIFICATION') then
1010 callWf := true;
1011 callDocCheck := true;
1012 updatePoAttr := true;
1013 saveRequest := true;
1014 elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
1015 l_po_change_requests(i).action_type='CANCELLATION') then
1016 -- Do not call doc sub check for shipment cancellation
1017 saveRequest := true;
1018 callWf := true;
1019 --callDocCheck := false;
1020 updatePoAttr := true;
1021
1022 elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
1023 l_po_change_requests(i).action_type='MODIFICATION') then
1024 -- If quantity,promised_date,price,Amount have not changed in the shipment level do not update po
1025 /*Bug 7112734 - Start
1026 During PO change process if Supplier Order Line number alone is changed then it will
1027 be treated as acceptance of the PO.
1028 */
1029 if (l_po_change_requests(i).new_quantity is null AND
1030 l_po_change_requests(i).new_promised_date is null AND
1031 l_po_change_requests(i).new_price is null AND
1032 l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
1033 --callDocCheck := false;
1034 --updatePoAttr := false;
1035 l_po_change_requests(i).request_status := 'ACCEPTED';
1036
1037 -- If PO requires acknowledgement, post shipment-level acceptance.
1038 IF (x_accp_flag = 'Y') THEN
1039
1040 callWf := true;
1041 -- Process The Acknowledgements
1042 PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
1043 1.0,FND_API.G_FALSE,x_return_status,
1044 l_po_change_requests(i).document_line_location_id,
1045 l_po_change_requests(i).document_header_id,
1046 l_po_change_requests(i).po_release_id,
1047 l_po_change_requests(i).document_revision_num,
1048 'Y',
1049 l_po_change_requests(i).request_reason,
1050 v_buyer_id, fnd_global.user_id);
1051
1052 END IF;
1053
1054 else
1055 -- if othere parameters are updated with so then update po
1056 callWf := true;
1057 callDocCheck := true;
1058 updatePoAttr := true;
1059 saveRequest := true;
1060 -- Bug 7112734 - End
1061 end if; -- if only so has changed
1062
1063 if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
1064 begin
1065 update po_line_locations_all
1066 set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
1067 where line_location_id = l_po_change_requests(i).document_line_location_id;
1068 exception
1069 WHEN OTHERS THEN
1070 x_return_status := FND_API.g_ret_sts_unexp_error;
1071 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1072 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1073 IF g_fnd_debug = 'Y' THEN
1074 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1075 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1076 l_api_name || '.others_exception', sqlcode);
1077 END IF;
1078 END IF;
1079 END IF;
1080 end; end if;
1081 elsif (l_po_change_requests(i).request_level='HEADER'
1082 AND l_po_change_requests(i).action_type='MODIFICATION') then
1083 saveRequest := true;
1084
1085 if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
1086
1087 if (p_po_release_id is null ) then
1088 -- Update the vendor_order_num for PO Headers no need of approval.
1089 update po_headers_all
1090 set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
1091 where po_header_id = p_po_header_id;
1092 else
1093 -- Update the vendor_order_num for PO Releases no need of approval.
1094 update po_releases_all
1095 set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
1096 where po_release_id = p_po_release_id;
1097 end if;
1098 end if;
1099
1100 -- Set startWf to false
1101 if (l_po_change_requests(i).Additional_changes is not null) then
1102 callWf := true;
1103 updatePoAttr := true;
1104 end if;
1105 --callDocCheck := false;
1106
1107 end if; -- end if accept reject
1108 END LOOP;
1109 if ((callDocCheck) AND l_po_change_requests.count() > 0 ) then
1110 IF g_fnd_debug = 'Y' THEN
1111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1112 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1113 '.invoked', ' Change Count : ' || TO_CHAR(l_po_change_requests.count()));
1114 END IF;
1115 END IF;
1116
1117 IS_ASN_EXIST( p_po_header_id,
1118 p_po_release_id,
1119 p_po_change_requests,
1120 x_pos_errors,
1121 l_ret_sts);
1122 IF( l_ret_sts = 'Y')
1123 THEN RETURN;
1124 END IF;
1125 validate_change_request (
1126 p_api_version => 1.0,
1127 p_init_msg_list => FND_API.G_FALSE,
1128 x_return_status => x_return_status,
1129 x_msg_data => l_msg_data,
1130 p_po_header_id => p_po_header_id,
1131 p_po_release_id => p_po_release_id,
1132 p_revision_num => p_revision_num,
1133 p_po_change_requests => l_po_change_requests,
1134 x_online_report_id => x_online_report_id,
1135 x_pos_errors => x_pos_errors);
1136
1137 end if;
1138
1139 if (l_po_change_requests.count() > 0 AND x_return_status = FND_API.G_RET_STS_SUCCESS) then
1140 IF g_fnd_debug = 'Y' THEN
1141 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1142 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1143 '.invoked', 'Type: ' ||
1144 ', Save Count : ' || TO_CHAR(l_po_change_requests.count()));
1145 END IF;
1146 END IF;
1147
1148
1149 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1150
1151 if (saveRequest) then
1152
1153 save_request(
1154 p_api_version => 1.0,
1155 p_init_msg_list => FND_API.G_FALSE,
1156 x_return_status => x_return_status,
1157 p_po_header_id => p_po_header_id,
1158 p_po_release_id => p_po_release_id,
1159 p_revision_num => p_revision_num,
1160 p_po_change_requests => l_po_change_requests,
1161 x_request_group_id => l_request_group_id,
1162 p_chn_int_cont_num => p_chn_int_cont_num,
1163 p_chn_source => p_chn_source,
1164 p_chn_requestor_username => p_chn_requestor_username,
1165 p_user_id => p_user_id,
1166 p_login_id => p_login_id);
1167 end if;
1168
1169 if(updatePoAttr) then
1170 IF g_fnd_debug = 'Y' THEN
1171 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1172 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1173 '.invoked', 'Update PO ' ||
1174 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
1175 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
1176 END IF;
1177 END IF;
1178 update_po_attributes(p_po_header_id,p_po_release_id,p_revision_num,
1179 l_request_group_id, x_return_status, p_chn_requestor_username,
1180 p_user_id,
1181 p_login_id);
1182 end if;
1183
1184
1185 /* Bug 3534807, mji
1186 Check if all shipments has been acknowledged, if yes post header
1187 acknowledgement record.
1188 */
1189 PO_ACKNOWLEDGE_PO_GRP.Set_Header_Acknowledgement (
1190 1.0,
1191 FND_API.G_FALSE,
1192 x_return_status,
1193 p_po_header_id,
1194 p_po_release_id );
1195
1196
1197 if (callWf) then
1198 IF g_fnd_debug = 'Y' THEN
1199 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1200 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1201 '.invoked', 'Call Workflow ' || ', request group id ' || to_char(l_request_group_id));
1202 END IF;
1203 END IF;
1204 --start multiple po change
1205 if (p_mpoc = FND_API.G_TRUE) then
1206 if(x_accp_flag = 'Y') then
1207 x_return_status := po_acknowledge_po_grp.all_shipments_responded (
1208 1.0,FND_API.G_FALSE,p_po_header_id , p_po_release_id, p_revision_num );
1209 else
1210 x_return_status := FND_API.G_TRUE;
1211 end if;
1212
1213 if(x_return_status = FND_API.G_TRUE) then
1214 IF g_fnd_debug = 'Y' THEN
1215 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1216 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1217 '.invoked', ' All shipments acked/changed ' );
1218 END IF;
1219 END IF;
1220 else
1221 l_err_msg_name_tbl := po_tbl_varchar30();
1222 l_err_msg_text_tbl := po_tbl_varchar2000();
1223 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
1224 x_pos_errors.message_name.extend;
1225 x_pos_errors.text_line.extend;
1226 x_pos_errors.message_name(1) := null;
1227 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_PO_ALL_NOT_RESPND');
1228 end if;
1229 end if;
1230 --end mupltiple po change change
1231
1232 x_return_status := startSupplierWF( p_po_header_id,p_po_release_id,
1233 p_revision_num, l_request_group_id, x_accp_flag);
1234 IF g_fnd_debug = 'Y' THEN
1235 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1236 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1237 '.invoked', 'Call Workflow ' || ', Return Status ' || x_return_status);
1238 END IF;
1239 END IF;
1240 end if;
1241
1242 end if; --if docCheck returns FND_API.G_RET_STS_SUCCESS
1243
1244 end if;
1245 -- Update the PO Headers/Releases even if the changes dosent require doc Check
1246 if (callDocCheck=false) then
1247 if (p_user_id is null or p_login_id is null) then
1248 l_user_id := fnd_global.user_id;
1249 l_login_id := fnd_global.login_id;
1250 else
1251 l_user_id := p_user_id;
1252 l_login_id := p_login_id;
1253 end if;
1254 -- Update the last update date if po dosent require to be updated
1255 if (p_po_release_id is not null) then
1256 update po_releases_all set
1257 last_update_date = sysdate,
1258 last_updated_by = l_user_id,
1259 last_update_login = l_login_id,
1260 request_id = fnd_global.conc_request_id,
1261 program_application_id = fnd_global.prog_appl_id,
1262 program_id = fnd_global.conc_program_id,
1263 program_update_date = sysdate
1264 where po_release_id = p_po_release_id;
1265 else
1266 update po_headers_all set
1267 last_update_date = sysdate,
1268 last_updated_by = l_user_id,
1269 last_update_login = l_login_id,
1270 request_id = fnd_global.conc_request_id,
1271 program_application_id = fnd_global.prog_appl_id,
1272 program_id = fnd_global.conc_program_id,
1273 program_update_date = sysdate
1274 where po_header_id = p_po_header_id;
1275 end if;
1276 end if; -- if call doc check is false
1277 EXCEPTION
1278 WHEN OTHERS THEN
1279 x_return_status := FND_API.g_ret_sts_unexp_error;
1280 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1281 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1282 IF g_fnd_debug = 'Y' THEN
1283 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1284 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1285 l_api_name || '.others_exception', sqlcode);
1286 END IF;
1287 END IF;
1288 END IF;
1289 l_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1290 p_encoded => 'F');
1291 END process_supplier_request;
1292 /**
1293 * Private Procedure: update_po_attributes
1294 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,REQUEST_GROUP_ID
1295 * Modifies:
1296 * Effects: Updates The PO_HEADERS_ALL, PO_RELEASES_ALL
1297 * Returns:
1298 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
1299 * FND_API.G_RET_STS_ERROR if an error occurs
1300 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1301 */
1302
1303 procedure update_po_attributes
1304 (p_po_header_id IN number,
1305 p_po_release_id IN number,
1306 p_revision_num IN number,
1307 p_chg_request_grp_id IN number,
1308 x_return_status OUT NOCOPY varchar2,
1309 p_chn_requestor_username in varchar2 default null,
1310 p_user_id IN number default null,
1311 p_login_id IN number default null) is
1312
1313 l_api_name CONSTANT VARCHAR2(30) := 'update_po_attributes';
1314 l_user_id number;
1315 l_login_id number;
1316
1317 BEGIN
1318
1319 x_return_status := FND_API.G_RET_STS_SUCCESS;
1320 if (p_user_id is null or p_login_id is null) then
1321 l_user_id := fnd_global.user_id;
1322 l_login_id := fnd_global.login_id;
1323 else
1324 l_user_id := p_user_id;
1325 l_login_id := p_login_id;
1326 end if;
1327
1328 if p_po_release_id is null then
1329 update po_headers_all set
1330 authorization_status = 'IN PROCESS',
1331 CHANGE_REQUESTED_BY = 'SUPPLIER',
1332 last_update_date = sysdate,
1333 last_updated_by = l_user_id,
1334 last_update_login = l_login_id,
1335 request_id = fnd_global.conc_request_id,
1336 program_application_id = fnd_global.prog_appl_id,
1337 program_id = fnd_global.conc_program_id,
1338 program_update_date = sysdate
1339 where po_header_id = p_po_header_id;
1340
1341 -- Update the approved_flag to R for all the shipments that has been changed
1342 -- do not update the flag for cancellation requests at shipments
1343 -- That was the earlier comment now we are going to chnage the
1344 -- Approved flag for cancellation records as asked by DBI team
1345 -- bug 4306375
1346 -- jai
1347
1348 update po_line_locations_all
1349 set approved_flag = 'R'
1350 where line_location_id in (select document_line_location_id
1351 from po_change_requests
1352 where request_level = 'SHIPMENT' and
1353 document_header_id = p_po_header_id and
1354 action_type in ('MODIFICATION','CANCELLATION') and
1355 initiator = 'SUPPLIER' and
1356 request_status ='PENDING') and
1357 approved_flag='Y';
1358
1359 -- Update all the shipments for which line price has been changed to prevent receiving
1360 -- do not update the line locations for cancellation request
1361 -- That was the earlier comment now we are going to chnage the
1362 -- Approved flag for cancellation records as asked by DBI team
1363 -- bug 4306375
1364 -- jai
1365
1366 update po_line_locations_all
1367 set approved_flag = 'R'
1368 where po_header_id in (select document_header_id
1369 from po_change_requests
1370 where request_level = 'HEADER' and
1371 document_header_id = p_po_header_id and
1372 action_type ='CANCELLATION' and
1373 initiator = 'SUPPLIER' and
1374 request_status ='PENDING') and
1375 approved_flag='Y';
1376
1377 update po_line_locations_all
1378 set approved_flag = 'R'
1379 where po_line_id in (select document_line_id
1380 from po_change_requests
1381 where request_level = 'LINE' and
1382 document_header_id = p_po_header_id and
1383 request_status = 'PENDING' and
1384 initiator = 'SUPPLIER' and
1385 action_type = 'MODIFICATION' and
1386 new_price is not null) and
1387 approved_flag='Y'
1388 and po_release_id is null; --This condition added for bug 8768745
1389 /* jai
1390 and
1391 line_location_id not in (select document_line_location_id
1392 from po_change_requests
1393 where request_level = 'SHIPMENT' and
1394 document_header_id = p_po_header_id and
1395 action_type = 'CANCELLATION' and
1396 initiator = 'SUPPLIER' and
1397 request_status ='PENDING') ;
1398 */
1399 else
1400 -- For Releases
1401 update po_releases_all set
1402 authorization_status = 'IN PROCESS',
1403 CHANGE_REQUESTED_BY = 'SUPPLIER',
1404 revised_date = sysdate,
1405 last_update_date = sysdate,
1406 last_updated_by = l_user_id,
1407 last_update_login = l_login_id,
1408 request_id = fnd_global.conc_request_id,
1409 program_application_id = fnd_global.prog_appl_id,
1410 program_id = fnd_global.conc_program_id,
1411 program_update_date = sysdate
1412 where po_release_id = p_po_release_id;
1413
1414 -- Now Update the approved_flag to R for all the shipments that has been
1415 -- changed to prevent receiving
1416 update po_line_locations_all
1417 set approved_flag = 'R'
1418 where line_location_id in (select document_line_location_id
1419 from po_change_requests
1420 where request_level = 'SHIPMENT' and
1421 po_release_id = p_po_release_id and
1422 action_type in ('MODIFICATION','CANCELLATION') and
1423 initiator = 'SUPPLIER' and
1424 request_status = 'PENDING') and
1425 approved_flag='Y';
1426
1427 --New DBI request
1428 update po_line_locations_all
1429 set approved_flag = 'R'
1430 where po_release_id in (select po_release_id
1431 from po_change_requests
1432 where request_level = 'HEADER' and
1433 po_release_id = p_po_release_id and
1434 action_type ='CANCELLATION' and
1435 initiator = 'SUPPLIER' and
1436 request_status ='PENDING') and
1437 approved_flag='Y';
1438 end if;
1439 EXCEPTION
1440 WHEN OTHERS THEN
1441 x_return_status := FND_API.g_ret_sts_unexp_error;
1442 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1443 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1444 IF g_fnd_debug = 'Y' THEN
1445 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1446 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1447 l_api_name || '.others_exception', sqlcode);
1448 END IF;
1449 END IF;
1450 END IF;
1451 END update_po_attributes;
1452
1453 /**
1454 * Public Procedure: validate_change_request
1455 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,POS_CHG_REC_TBL
1456 * Modifies:
1457 * Effects: Converts the Supplier Change Request To PO Change Request
1458 * Calls Doc Submission Check API
1459 * Also calls process_acknowledgements API to post Acknowledgements
1460 * Returns:
1461 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
1462 * FND_API.G_RET_STS_ERROR if an error occurs
1463 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1464 */
1465
1466 procedure validate_change_request (
1467 p_api_version IN NUMBER,
1468 p_init_msg_list IN VARCHAR2,
1469 x_return_status OUT NOCOPY VARCHAR2,
1470 x_msg_data OUT NOCOPY VARCHAR2,
1471 p_po_header_id IN number,
1472 p_po_release_id IN number,
1473 p_revision_num IN number,
1474 p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1475 x_online_report_id OUT NOCOPY number,
1476 x_pos_errors OUT NOCOPY pos_err_type,
1477 x_doc_check_error_msg OUT NOCOPY Doc_Check_Return_Type) is
1478
1479 x_error_code varchar2(40);
1480 rec_cnt number :=0;
1481 line_cnt number :=0;
1482 ship_cnt number :=0;
1483 dist_cnt number :=0;
1484 ack_cnt number :=0;
1485 p_document_id NUMBER;
1486 v_document_type PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1487 v_type_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1488 v_document_subtype PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1489 sub_check_failed exception;
1490
1491 poLineIdTbl po_tbl_number := po_tbl_number();
1492 unitPriceTbl po_tbl_number := po_tbl_number();
1493 -- <PO_CHANGE_API FPJ> VENDOR_PRODUCT_NUM should use varchar30, not varchar40:
1494 supItemTbl po_tbl_varchar30 := po_tbl_varchar30();
1495 startdateTbl po_tbl_date := po_tbl_date();
1496 expirationdateTbl po_tbl_date := po_tbl_date();
1497 amountTbl po_tbl_number := po_tbl_number();
1498 shipamountTbl po_tbl_number := po_tbl_number();
1499 poLineLocIdTbl po_tbl_number := po_tbl_number();
1500 parentLineLocIdTbl po_tbl_number := po_tbl_number();
1501 quantityTbl po_tbl_number := po_tbl_number();
1502 priceOverrideTbl po_tbl_number := po_tbl_number();
1503 shipmentNumTbl po_tbl_number := po_tbl_number();
1504 promisedDateTbl po_tbl_date := po_tbl_date();
1505
1506 distQtyTbl po_tbl_number := po_tbl_number();
1507 distIdTbl po_tbl_number := po_tbl_number();
1508 distAmtTbl po_tbl_number := po_tbl_number(); -- FPS
1509
1510 l_return_status varchar2(10);
1511 l_sub_check_status varchar2(10);
1512 l_online_report_id number;
1513 l_msg_data varchar2(2000);
1514
1515 --l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1516
1517 -- <PO_CHANGE_API FPJ START>
1518 -- Added a PO_ prefix to the names of the change object types:
1519 vLineChanges PO_LINES_REC_TYPE;
1520 vShipChanges PO_SHIPMENTS_REC_TYPE;
1521 vDistChanges PO_DISTRIBUTIONS_REC_TYPE;
1522 vRequestedChanges PO_CHANGES_REC_TYPE;
1523 -- <PO_CHANGE_API FPJ END>
1524
1525 vAckTbl pos_ack_rec_tbl := pos_ack_rec_tbl();
1526 x_progress varchar2(3) := '000';
1527 l_api_name CONSTANT VARCHAR2(30) := 'validate_change_request';
1528 l_api_version CONSTANT NUMBER := 1.0;
1529 x_sub_errors number;
1530 x_org_id number;
1531 sub_error_flag varchar2(1);
1532 x_cum_flag boolean := FALSE;
1533 x_price number := NULL;
1534 l_error_index number := 0;
1535 l_err_msg_name_tbl po_tbl_varchar30;
1536 l_err_msg_text_tbl po_tbl_varchar2000;
1537 l_total_qty number;
1538 l_ga_ship_qty number;
1539 l_ga_lineLocId number;
1540 l_ga_lineId number;
1541 l_qty_orig number;
1542 l_qty_split number;
1543 l_shipToOrg number;
1544 l_shipToLoc number;
1545 l_needByDate date;
1546 lLine number;
1547 changeOrig varchar2(1) := 'F';
1548 l_price_break_type VARCHAR2(1) := NULL;
1549 l_cumulative_flag BOOLEAN := false;
1550 l_initiator po_change_requests.initiator%type :='SUPPLIER';
1551 --<< Complex work changes for R12 >>
1552 progress_type_tbl PO_TBL_VARCHAR30:= PO_TBL_VARCHAR30();
1553 pay_description_tbl PO_TBL_VARCHAR240:= PO_TBL_VARCHAR240();
1554
1555 /* 9867085 */
1556 skip_line number := 0;
1557
1558 -- Bug 8818198. Modified cursor to pick lines, which are not cancelled.
1559 -- Bug 9060324. Modified cursor to pick only the open lines.
1560 cursor ga_line_csr(p_po_header_id in number) is
1561 select po_line_id
1562 from po_lines_archive_all pol
1563 where pol.po_header_id = p_po_header_id and
1564 pol.latest_external_flag='Y' and
1565 nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED') and
1566 nvl(pol.cancel_flag,'N') <> 'Y' and
1567 nvl(pol.MANUAL_PRICE_CHANGE_FLAG,'N') <> 'Y' AND /* 9867085 */
1568 pol.from_header_id in (
1569 select po_header_id
1570 from po_headers_all poh
1571 where poh.global_agreement_flag='Y'
1572 and poh.po_header_id=pol.from_header_id) and
1573 exists(select poll.line_location_id
1574 from po_line_locations_archive_all poll
1575 where poll.po_line_id = pol.po_line_id and
1576 nvl(poll.closed_code,'OPEN') not in('FINALLY CLOSED') and
1577 nvl(poll.cancel_flag,'N') <> 'Y' and
1578 poll.latest_external_flag='Y');
1579
1580 cursor ga_ship_csr(p_line_id in number) is
1581 select line_location_id,quantity
1582 from po_line_locations_archive_all
1583 where po_line_id = p_line_id and
1584 nvl(closed_code,'OPEN') not in('FINALLY CLOSED') and
1585 nvl(cancel_flag,'N') <> 'Y' and
1586 latest_external_flag='Y' ;
1587
1588 BEGIN
1589
1590 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
1591 l_api_name, g_pkg_name)
1592 THEN
1593 RAISE FND_API.g_exc_unexpected_error;
1594 END IF;
1595
1596 x_return_status := FND_API.g_ret_sts_success;
1597 IF g_fnd_debug = 'Y' THEN
1598 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1599 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1600 '.invoked', 'Type: ' ||
1601 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
1602 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
1603 END IF;
1604 END IF;
1605
1606 if (p_po_release_id is not null) then
1607 p_document_id := p_po_release_id;
1608 v_document_type := 'RELEASE';
1609 v_document_subtype := 'RELEASE';
1610 select org_id
1611 into x_org_id
1612 from po_releases_all
1613 where po_release_id= p_po_release_id;
1614 else
1615 p_document_id := p_po_header_id;
1616 select type_lookup_code , org_id
1617 into v_type_code , x_org_id
1618 from po_headers_all
1619 where po_header_id= p_po_header_id;
1620 if (v_type_code in ('STANDARD','PLANNED')) then
1621 v_document_type := 'PO';
1622 v_document_subtype := v_type_code;
1623 elsif (v_type_code in ('BLANKET','CONTRACT')) then
1624 v_document_type := 'PA';
1625 v_document_subtype := v_type_code;
1626 end if;
1627 end if;
1628
1629 -- Set the org context before calling core po api's
1630 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
1631
1632 rec_cnt := p_po_change_requests.count();
1633 -- Now check if the document references a global agreement
1634 -- or a blanket, then get the price accordingly
1635 if (rec_cnt > 0) then
1636 l_initiator:=p_po_change_requests(1).initiator;
1637 end if;
1638 if (rec_cnt > 0) then
1639 FOR i in 1..rec_cnt
1640 LOOP
1641 --Construct the table of line record
1642 if(p_po_change_requests(i).request_level = 'LINE') then
1643 poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1644 startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1645
1646 line_cnt := line_cnt + 1;
1647 poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1648 unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1649 supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1650 startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1651 expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1652 amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1653 end if; -- end if line
1654
1655 -- do not send cancellation request
1656 if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1657 (p_po_change_requests(i).action_type not in ('ACCEPT','REJECT','CANCEL'))) then
1658 ship_cnt := ship_cnt + 1;
1659 poLineLocIdTbl.extend; quantityTbl.extend;
1660 promisedDateTbl.extend;priceOverrideTbl.extend;
1661 parentLineLocIdTbl.extend;
1662 shipmentNumTbl.extend;
1663 shipamountTbl.extend;
1664 progress_type_tbl.extend;
1665 pay_description_tbl.extend;
1666 -- if release / standard po referencing a GA/ Quotation
1667 -- Call Get Price Break API.
1668
1669 --if ((p_po_change_requests(i).from_header_id is not null) or
1670 -- (p_po_change_requests(i).po_release_id is not null)) then
1671 if (p_po_change_requests(i).po_release_id is not null) then
1672
1673 SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1674 INTO l_price_break_type
1675 FROM po_lines_all
1676 WHERE po_line_id = p_po_change_requests(i).document_line_id;
1677
1678 IF (l_price_break_type = 'Y') THEN
1679 l_cumulative_flag := TRUE;
1680 ELSE
1681 l_cumulative_flag := FALSE;
1682 END IF;
1683
1684 if(p_po_change_requests(i).new_price is null) then
1685 x_price := po_sourcing2_sv.get_break_price(
1686 nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1687 p_po_change_requests(i).ship_to_organization_id,
1688 p_po_change_requests(i).ship_to_location_id,
1689 p_po_change_requests(i).document_line_id,
1690 l_cumulative_flag,
1691 nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1692 p_po_change_requests(i).document_line_location_id);
1693 p_po_change_requests(i).old_price := x_price;
1694 end if;
1695 end if; -- end if release
1696
1697 poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1698 parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1699 quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700 promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701 priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702 shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703 progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1704 pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1705 shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1706 end if; -- if shipment
1707
1708 if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1709 dist_cnt := dist_cnt + 1;
1710 distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1711 distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1712 distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1713 distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1714
1715 end if; -- if dist
1716
1717 --end if; -- end of rec count
1718 END LOOP;
1719
1720 -- Now check if the change request consists of any shipments that refers to a GA
1721 -- in that case sum up the quatities and call price break api to get new line price
1722 -- and post a line level change to Doc Check API
1723
1724 if (p_po_release_id is null ) then
1725 open ga_line_csr(p_po_header_id);
1726 loop
1727
1728 l_qty_orig := 0;
1729 l_qty_split := 0;
1730 l_total_qty := 0;
1731 x_price := 0;
1732
1733 fetch ga_line_csr into l_ga_lineId;
1734 exit when ga_line_csr%notfound;
1735
1736 /* 9867085 */
1737 SKIP_LINE := 0;
1738
1739 FOR I IN 1..REC_CNT LOOP
1740
1741 IF ( P_PO_CHANGE_REQUESTS(I).REQUEST_LEVEL = 'LINE' AND
1742 P_PO_CHANGE_REQUESTS(I).DOCUMENT_LINE_ID = L_GA_LINEID AND
1743 NVL(P_PO_CHANGE_REQUESTS(I).NEW_PRICE,-1) <> NVL(P_PO_CHANGE_REQUESTS(I).OLD_PRICE,-1) )
1744 THEN
1745 SKIP_LINE := 1;
1746
1747 END IF;
1748 end loop;
1749
1750 IF SKIP_LINE = 1 THEN
1751 SKIP_LINE := 0;
1752 --CONTINUE; Bug#12883760 Key word not supported in 10g
1753 ELSE
1754 /* 9867085 */
1755
1756 open ga_ship_csr(l_ga_lineId);
1757
1758 loop
1759
1760 fetch ga_ship_csr
1761 into l_ga_lineLocId,l_ga_ship_qty;
1762 exit when ga_ship_csr%notfound;
1763 changeOrig := 'F';
1764 FOR i in 1..rec_cnt LOOP
1765
1766 if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1767 p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1768
1769 if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1770 p_po_change_requests(i).new_quantity is not null and
1771 p_po_change_requests(i).parent_line_location_id is null ) then
1772
1773 l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1774 changeOrig := 'T';
1775 end if;
1776 -- Sum up all the split quantities
1777 if (p_po_change_requests(i).parent_line_location_id is not null and
1778 p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1779 p_po_change_requests(i).new_quantity is not null) then
1780
1781 l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1782
1783 end if;
1784 end if ; -- if shipment change
1785 END LOOP;
1786
1787 if (changeOrig = 'F') then
1788 -- if original shipment hasnt been changed
1789 l_qty_orig := l_qty_orig + l_ga_ship_qty;
1790 end if;
1791
1792 end loop;
1793 close ga_ship_csr;
1794
1795 l_total_qty := l_qty_orig + l_qty_split;
1796
1797 -- Get the price break for the total quantity for each line using the min shipment
1798 -- need by date and ship to org
1799 select ship_to_location_id, ship_to_organization_id,need_by_date
1800 into l_shipToLoc,l_shipToOrg,l_needBydate
1801 from po_line_locations_archive_all
1802 where shipment_num = (select min(shipment_num)
1803 from po_line_locations_archive_all
1804 where po_line_id = l_ga_lineId and
1805 nvl(closed_code,'OPEN') not in('FINALLY CLOSED') and
1806 nvl(cancel_flag,'N') <> 'Y' and
1807 latest_external_flag='Y' ) and
1808 latest_external_flag='Y' and
1809 po_line_id = l_ga_lineId ;
1810
1811 -- For Global Agreement refered Standard PO's x_cum_flag is always FALSE
1812 x_price := po_sourcing2_sv.get_break_price(
1813 l_total_qty, l_shipToOrg, l_shipToLoc, l_ga_lineId, x_cum_flag, l_needBydate, null);
1814
1815 -- Post a line level change with the price returned from price break api
1816
1817 lLine := poLineIdTbl.count;
1818 poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1819 startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1820
1821 line_cnt := line_cnt + 1;
1822 poLineIdTbl(lLine+1) := l_ga_lineId;
1823 unitPriceTbl(lLine+1) := x_price;
1824 supItemTbl(lLine+1) := null;
1825 startdateTbl(lLine+1) := null;
1826 expirationdateTbl(lLine+1) := null;
1827 amountTbl(lLine+1) := null;
1828
1829
1830 IF g_fnd_debug = 'Y' THEN
1831 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1832 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name
1833 , 'Calculating Price break for STD PO from GA: ' ||
1834 ', PO Line Id : ' || NVL(TO_CHAR(l_ga_lineId),'null') ||
1835 ', Total Quantity : ' || NVL(TO_CHAR(l_total_qty),'null') ||
1836 ', Price Break : ' || NVL(TO_CHAR(x_price),'null'));
1837 END IF;
1838 END IF;
1839 END IF;--SKIP_LINE = 1
1840 end loop;
1841 close ga_line_csr;
1842
1843 end if; -- if release id
1844 -- Construct Line Record Changes
1845
1846 -- <PO_CHANGE_API FPJ START>
1847 -- Added a PO_ prefix to the names of the change object types and
1848 -- modified their constructors.
1849 vLineChanges := PO_LINES_REC_TYPE.create_object (
1850 p_po_line_id => poLineIdTbl,
1851 p_unit_price => unitPriceTbl,
1852 p_vendor_product_num => supItemTbl,
1853 p_start_date => startdateTbl,
1854 p_expiration_date => expirationdateTbl,
1855 p_amount => amountTbl
1856 );
1857
1858 vShipChanges := PO_SHIPMENTS_REC_TYPE.create_object (
1859 p_po_line_location_id => poLineLocIdTbl,
1860 p_quantity => quantityTbl,
1861 p_promised_date => promisedDateTbl,
1862 p_price_override => priceOverrideTbl,
1863 p_parent_line_location_id => parentLineLocIdTbl,
1864 p_split_shipment_num => shipmentNumTbl,
1865 p_payment_type => progress_type_tbl,
1866 p_description => pay_description_tbl,
1867 p_amount => shipamountTbl
1868 );
1869
1870 vDistChanges := PO_DISTRIBUTIONS_REC_TYPE.create_object (
1871 p_po_distribution_id => distIdTbl,
1872 p_quantity_ordered => distQtyTbl,
1873 p_amount_ordered => distAmtTbl -- FPS
1874 );
1875
1876 vRequestedChanges := PO_CHANGES_REC_TYPE.create_object (
1877 p_po_header_id => p_po_header_id,
1878 p_po_release_id => p_po_release_id,
1879 p_line_changes => vLineChanges,
1880 p_shipment_changes => vShipChanges,
1881 p_distribution_changes => vDistChanges
1882 );
1883 -- <PO_CHANGE_API FPJ END>
1884
1885
1886
1887 PO_DOCUMENT_CHECKS_GRP.PO_SUBMISSION_CHECK(
1888 p_api_version => 1.0,
1889 p_action_requested => 'DOC_SUBMISSION_CHECK',
1890 p_document_type => v_document_type,
1891 p_document_subtype => v_document_subtype,
1892 p_document_id => p_document_id,
1893 p_org_id => x_org_id,
1894 p_requested_changes => vRequestedChanges,
1895 p_req_chg_initiator => l_initiator,
1896 x_return_status => l_return_status,
1897 x_sub_check_status => l_sub_check_status,
1898 x_msg_data => l_msg_data,
1899 x_online_report_id => x_online_report_id,
1900 x_doc_check_error_record => x_doc_check_error_msg);
1901
1902
1903
1904
1905 l_err_msg_name_tbl := po_tbl_varchar30();
1906 l_err_msg_text_tbl := po_tbl_varchar2000();
1907 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
1908
1909 if ( l_return_status = FND_API.G_RET_STS_SUCCESS AND
1910 l_sub_check_status = FND_API.G_RET_STS_ERROR) THEN
1911
1912 x_sub_errors := x_doc_check_error_msg.online_report_id.count;
1913 sub_error_flag := 'N';
1914 FOR i in 1..x_sub_errors loop
1915 if ((x_doc_check_error_msg.message_name(i) not in
1916 ('PO_SUB_PO_LINE_NE_SHIP_AMT','PO_SUB_PO_LINE_NE_SHIP_QTY',
1917 'PO_SUB_PO_SHIP_NE_DIST_AMT','PO_SUB_PO_SHIP_NE_DIST_QTY',
1918 'PO_SUB_REQ_LINE_NE_DIST_AMT','PO_SUB_REQ_LINE_NE_DIST_QTY',
1919 'PO_SUB_REL_SHIP_NE_DIST_AMT','PO_SUB_REL_SHIP_NE_DIST_QTY',
1920 'PO_SUB_SHIP_NO_DIST','PO_SUB_REL_SHIP_NO_DIST',
1921 'PO_SUB_PAY_ITEM_NE_LINE_AMT')) --Bug 5547289
1922 AND nvl(x_doc_check_error_msg.message_type(i), 'E') <> 'W') then
1923 sub_error_flag := 'Y';
1924 l_error_index := l_error_index + 1;
1925 x_pos_errors.message_name.extend;
1926 x_pos_errors.text_line.extend;
1927 x_pos_errors.message_name(l_error_index) := x_doc_check_error_msg.message_name(i);
1928 x_pos_errors.text_line(l_error_index) := x_doc_check_error_msg.text_line(i);
1929 end if;
1930 end loop;
1931 -- Some other errors were reported from submission check api
1932 if (sub_error_flag = 'Y') then
1933 raise sub_check_failed;
1934 else
1935 x_return_status := FND_API.G_RET_STS_SUCCESS;
1936 end if;
1937 elsif (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1938 --x_msg_data has stuff regarding doc sub check.
1939 l_error_index := l_error_index + 1;
1940 x_pos_errors.message_name.extend;
1941 x_pos_errors.text_line.extend;
1942 x_pos_errors.message_name(l_error_index) := null;
1943 x_pos_errors.text_line(l_error_index) := l_msg_data;
1944 raise sub_check_failed;
1945 -- If l_return_status and l_sub_check_status = FND_API.G_RET_STS_SUCCESS
1946 -- Then Continue no errors in doc check
1947 end if;
1948
1949 x_progress := '007';
1950 end if; --end rec cnt
1951
1952 EXCEPTION
1953 WHEN FND_API.g_exc_error THEN
1954 x_return_status := FND_API.g_ret_sts_error;
1955 WHEN FND_API.g_exc_unexpected_error THEN
1956 x_return_status := FND_API.g_ret_sts_unexp_error;
1957 WHEN sub_check_failed THEN
1958 x_return_status := FND_API.g_ret_sts_error;
1959 WHEN OTHERS THEN
1960 x_return_status := FND_API.g_ret_sts_unexp_error;
1961 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1962 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1963 IF g_fnd_debug = 'Y' THEN
1964 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1965 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1966 l_api_name || '.others_exception', sqlcode);
1967 END IF;
1968 END IF;
1969 END IF;
1970 l_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1971 p_encoded => 'F');
1972 END validate_change_request;
1973
1974 /* Overloaded Procedure to return only filtered Errors for supplier changes*/
1975
1976 procedure validate_change_request (
1977 p_api_version IN NUMBER,
1978 p_init_msg_list IN VARCHAR2,
1979 x_return_status OUT NOCOPY VARCHAR2,
1980 x_msg_data OUT NOCOPY VARCHAR2,
1981 p_po_header_id IN number,
1982 p_po_release_id IN number,
1983 p_revision_num IN number,
1984 p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1985 x_online_report_id OUT NOCOPY number,
1986 x_pos_errors OUT NOCOPY pos_err_type) is
1987
1988 l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1989 l_msg_data varchar2(2000) := NULL;
1990
1991 BEGIN
1992 --l_po_change_requests := p_po_change_requests;
1993
1994 validate_change_request (
1995 p_api_version => 1.0,
1996 p_init_msg_list => FND_API.G_FALSE,
1997 x_return_status => x_return_status,
1998 x_msg_data => l_msg_data,
1999 p_po_header_id => p_po_header_id,
2000 p_po_release_id => p_po_release_id,
2001 p_revision_num => p_revision_num,
2002 p_po_change_requests => p_po_change_requests,
2003 x_online_report_id => x_online_report_id,
2004 x_pos_errors => x_pos_errors,
2005 x_doc_check_error_msg => l_doc_check_error_msg);
2006
2007 END validate_change_request;
2008
2009 /**
2010 * Private Function: ifLineChangable
2011 * Requires: PO_LINE_ID
2012 * Modifies: None
2013 * Effects:
2014 * Determines id the Line Price can be changed based on
2015 * 1. ACCRUE_ON_RECEIPT_FLAG
2016 * 2. QUANTITY_BILLED
2017 * Returns:
2018 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
2019 * FND_API.G_RET_STS_ERROR if an error occurs
2020 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2021 */
2022
2023 function ifLineChangable( p_po_line_id IN number)
2024 return varchar2 is
2025
2026 v_qty_billed number;
2027 v_accr_rcpt_cnt number;
2028 v_qty_rcvd number;
2029 x_display_stat varchar2(1) :='Y';
2030 x_price_change po_lines_all.allow_price_override_flag%type;
2031 x_order_type po_line_types.order_type_lookup_code%type;
2032 l_return_status varchar2(3);
2033 l_retro_active_price_change varchar2(3);
2034 l_doc_type varchar2(25);
2035 x_po_header_id po_headers_all.po_header_id%type;
2036 l_is_complex_po varchar2(1):='N';
2037 l_is_actual_po varchar2(1):='Y';
2038
2039 BEGIN
2040
2041 /* not needed anymore. use the po api.
2042 begin
2043
2044 select nvl(allow_price_override_flag,'N')
2045 into x_price_change
2046 from po_lines_all
2047 where po_line_id =
2048 (select from_line_id
2049 from po_lines_all
2050 where po_line_id=p_po_line_id);
2051 exception
2052 when no_data_found then
2053 x_price_change :='Y';
2054 end;
2055 */
2056
2057 --See if the PO is of type BLANKET; if so, the price can be updatable at any time.
2058 select poh.type_lookup_code
2059 into l_doc_type
2060 from po_headers_all poh, po_lines_all pol
2061 where poh.po_header_id = pol.po_header_id
2062 and po_line_id = p_po_line_id;
2063
2064 if (l_doc_type = 'STANDARD') then
2065
2066 -- Check if the line type is amount based then do not allow change
2067 --PO API does not cover this.
2068 begin
2069 select polt.ORDER_TYPE_LOOKUP_CODE,PO_HEADER_ID
2070 into x_order_type,x_po_header_id
2071 from po_lines_all pol,po_line_types polt
2072 where pol.po_line_id = p_po_line_id and
2073 pol.line_type_id = polt.line_type_id;
2074 exception
2075 when no_data_found then
2076 x_price_change :='Y';
2077 end;
2078
2079 if (PO_COMPLEX_WORK_PVT.is_complex_work_po(x_po_header_id)) then
2080 l_is_complex_po:='Y';
2081 end if;
2082
2083 if (PO_COMPLEX_WORK_PVT.is_financing_po(x_po_header_id)) then
2084 l_is_actual_po:='N';
2085 end if;
2086
2087 if (((x_order_type = 'AMOUNT') and (l_is_complex_po='N')) or ((l_is_actual_po='Y') and (l_is_complex_po='Y'))) then
2088 x_display_stat := 'N';
2089 else
2090
2091 PO_DOCUMENT_CHECKS_GRP.check_std_po_price_updateable (
2092 p_api_version => 1.0,
2093 x_return_status =>l_return_status,
2094 p_po_line_id => p_po_line_id,
2095 p_from_price_break => 'N',
2096 p_add_reasons_to_msg_list => 'N',
2097 x_price_updateable => x_price_change,
2098 x_retroactive_price_change => l_retro_active_price_change);
2099
2100 if (x_price_change = PO_CORE_S.G_PARAMETER_NO) then
2101 x_display_stat := 'N';
2102 else
2103 x_display_stat := 'Y';
2104 end if;
2105
2106 end if;
2107
2108 else --end of if l_doc_type = 'STANDARD'
2109 x_display_stat := 'Y'; --For blankets, planned, GAs, price is always changeable.
2110 end if;
2111
2112
2113
2114 return x_display_stat;
2115
2116 EXCEPTION
2117 WHEN OTHERS THEN
2118 return('');
2119 END ifLineChangable;
2120
2121
2122 procedure validateCancelRequest(
2123 p_api_version IN NUMBER,
2124 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2125 x_return_status OUT NOCOPY VARCHAR2,
2126 p_po_header_id IN NUMBER,
2127 p_po_release_id IN NUMBER) IS
2128
2129 p_document_id NUMBER;
2130 v_document_type PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
2131 v_document_subtype PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
2132 v_type_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
2133 l_api_name CONSTANT VARCHAR2(30) := 'validateCancelRequest';
2134 l_api_version CONSTANT NUMBER := 1.0;
2135 x_org_id number;
2136 x_ship_count NUMBER := 0;
2137
2138
2139 BEGIN
2140 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2141 l_api_name, g_pkg_name)
2142 THEN
2143 RAISE FND_API.g_exc_unexpected_error;
2144 END IF;
2145
2146 x_return_status := FND_API.g_ret_sts_success;
2147
2148 -- Call this when logging is enabled
2149
2150 IF g_fnd_debug = 'Y' THEN
2151 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2152 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2153 '.invoked', 'Type: ' ||
2154 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
2155 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
2156 END IF;
2157 END IF;
2158 if (p_po_release_id is not null) then
2159
2160 p_document_id := p_po_release_id;
2161 v_document_type := 'RELEASE';
2162 v_document_subtype := 'RELEASE';
2163
2164
2165 select poh.type_lookup_code,por.org_id
2166 into v_type_code,x_org_id
2167 from po_headers_all poh,po_releases_all por
2168 where por.po_header_id = poh.po_header_id and
2169 por.po_release_id = p_po_release_id;
2170
2171 if (v_type_code = 'BLANKET') then
2172 v_document_type := 'RELEASE';
2173 v_document_subtype := 'BLANKET';
2174 elsif (v_type_code = 'PLANNED') then
2175 v_document_type := 'RELEASE';
2176 v_document_subtype := 'SCHEDULED';
2177 end if;
2178 else
2179 p_document_id := p_po_header_id;
2180 select type_lookup_code into v_type_code
2181 from po_headers_all
2182 where po_header_id= p_po_header_id;
2183 if (v_type_code in ('STANDARD','PLANNED')) then
2184 v_document_type := 'PO';
2185 v_document_subtype := v_type_code;
2186 elsif (v_type_code in ('BLANKET','CONTRACT')) then
2187 v_document_type := 'PA';
2188 v_document_subtype := v_type_code;
2189 end if;
2190
2191 select org_id
2192 into x_org_id
2193 from po_headers_all
2194 where po_header_id= p_po_header_id;
2195
2196 end if;
2197 -- Set the org context before calling the cancel api
2198
2199 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
2200
2201 PO_Document_Control_GRP.check_control_action(
2202 p_api_version => 1.0,
2203 p_init_msg_list => FND_API.G_TRUE,
2204 x_return_status => x_return_status,
2205 p_doc_type => v_document_type,
2206 p_doc_subtype => v_document_subtype,
2207 p_doc_id => p_po_header_id,
2208 p_doc_num => null,
2209 p_release_id => p_po_release_id,
2210 p_release_num => null,
2211 p_doc_line_id => null,
2212 p_doc_line_num => null,
2213 p_doc_line_loc_id => null,
2214 p_doc_shipment_num => null,
2215 p_action => 'CANCEL');
2216
2217
2218 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2219
2220 BEGIN
2221 if (p_po_release_id is not null) then
2222 SELECT count(*)
2223 INTO x_ship_count
2224 FROM PO_LINE_LOCATIONS_ALL POLL,PO_LINES_ALL POL
2225 WHERE POLL.po_release_id = p_po_release_id
2226 AND POLL.po_line_id = POL.po_line_id
2227 AND nvl(POLL.cancel_flag, 'N') = 'N'
2228 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2229 AND POLL.shipment_type in ('SCHEDULED', 'BLANKET')
2230 AND (nvl(POLL.quantity_received,0) >= nvl(POLL.quantity,0)
2231 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity,0)
2232 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity_received,0))
2233 AND POL.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE');
2234 else
2235 SELECT count(*)
2236 INTO x_ship_count
2237 FROM PO_LINE_LOCATIONS_ALL POLL,PO_LINES_ALL POL,PO_HEADERS_ALL POH
2238 WHERE
2239 POH.PO_HEADER_ID = p_po_header_id
2240 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
2241 AND POH.TYPE_LOOKUP_CODE ='STANDARD'
2242 AND POLL.po_line_id = POL.po_line_id
2243 AND nvl(POLL.cancel_flag, 'N') = 'N'
2244 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2245 AND (nvl(POLL.quantity_received,0) >= nvl(POLL.quantity,0)
2246 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity,0)
2247 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity_received,0))
2248 AND POL.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
2249 AND NVL(POLL.payment_type,' ') <> 'ADVANCE'; -- <Bug 5504546>
2250
2251 end if;
2252
2253 if (x_ship_count > 0) then
2254 x_return_status := FND_API.G_RET_STS_ERROR;
2255 else
2256 x_return_status := FND_API.G_RET_STS_SUCCESS;
2257 end if;
2258
2259 EXCEPTION
2260 WHEN FND_API.g_exc_error THEN
2261 x_return_status := FND_API.g_ret_sts_error;
2262 WHEN FND_API.g_exc_unexpected_error THEN
2263 x_return_status := FND_API.g_ret_sts_unexp_error;
2264 WHEN OTHERS THEN
2265 x_return_status := FND_API.g_ret_sts_unexp_error;
2266 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2267 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2268 IF g_fnd_debug = 'Y' THEN
2269 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2270 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2271 l_api_name || '.others_exception', sqlcode);
2272 END IF;
2273 END IF;
2274 END IF;
2275
2276 END;
2277 END IF;
2278
2279
2280 EXCEPTION
2281
2282 WHEN FND_API.g_exc_error THEN
2283 x_return_status := FND_API.g_ret_sts_error;
2284 WHEN FND_API.g_exc_unexpected_error THEN
2285 x_return_status := FND_API.g_ret_sts_unexp_error;
2286 WHEN OTHERS THEN
2287 x_return_status := FND_API.g_ret_sts_unexp_error;
2288 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2289 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2290 IF g_fnd_debug = 'Y' THEN
2291 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2292 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2293 l_api_name || '.others_exception', sqlcode);
2294 END IF;
2295 END IF;
2296 END IF;
2297
2298 END validateCancelRequest;
2299
2300 PROCEDURE getShipmentStatus(
2301 p_line_location_id IN NUMBER,
2302 p_po_header_id IN NUMBER,
2303 p_po_release_id IN NUMBER,
2304 p_revision_num IN NUMBER,
2305 x_msg_code OUT NOCOPY VARCHAR2,
2306 x_msg_display OUT NOCOPY VARCHAR2,
2307 x_note OUT NOCOPY CLOB) IS
2308
2309 x_ack_stat varchar2(40);
2310 x_accp_flag po_headers_all.acceptance_required_flag%type;
2311 x_revision number;
2312 x_cons_flag po_line_locations.consigned_flag%type;
2313
2314 BEGIN
2315 if p_line_location_id is not null then
2316 if (p_po_release_id is null) then
2317 select DECODE( nvl(pll.cancel_flag,'N'),
2318 'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'),
2319 'N',DECODE(NVL(pll.CONSIGNED_FLAG,'N'),
2320 'Y',DECODE(NVL(pll.CLOSED_CODE,'OPEN'),'CLOSED FOR INVOICE',
2321 DECODE(
2322 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2323 (1.0,FND_API.G_FALSE,pll.line_location_id,
2324 pll.po_header_id, p_po_release_id,p_revision_num),
2325 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2326 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2327 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2328 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2329 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2330 '',polc.displayed_field),
2331 polc.displayed_field
2332 ),
2333 'N',
2334 CASE
2335 when NVL(pll.CLOSED_CODE,'OPEN')='OPEN' OR NVL(pll.CLOSED_CODE,'OPEN')='CLOSED FOR RECEIVING'
2336 OR NVL(pll.CLOSED_CODE,'OPEN') = 'CLOSED FOR INVOICE'
2337 THEN
2338 DECODE(
2339 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2340 (1.0,FND_API.G_FALSE,pll.line_location_id,
2341 pll.po_header_id, p_po_release_id,p_revision_num),
2342 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2343 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2344 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2345 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2346 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2347 '',polc.displayed_field)
2348 ELSE polc.displayed_field
2349 END
2350 )
2351 ) ,
2352 nvl(pll.closed_code,'OPEN'),
2353 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2354 (1.0,FND_API.G_FALSE,pll.line_location_id,
2355 pll.po_header_id, p_po_release_id,p_revision_num),nvl(poh.acceptance_required_flag,'N'),
2356 poh.revision_num,nvl(pll.consigned_flag,'N')
2357 into x_msg_display,x_msg_code,x_ack_stat,x_accp_flag,x_revision,x_cons_flag
2358 from po_line_locations_all pll,
2359 po_headers_all poh,
2360 po_lookup_codes polc
2361 where
2362 polc.lookup_code = NVL(pll.closed_code, 'OPEN') and
2363 polc.lookup_type = 'DOCUMENT STATE' and
2364 poh.po_header_id = pll.po_header_id and
2365 pll.line_location_id = p_line_location_id ;
2366
2367 if ( x_ack_stat in ('REJECTED','ACCEPTED')) then
2368 begin
2369 select note into x_note
2370 from po_acceptances
2371 where po_line_location_id=p_line_location_id and
2372 revision_num = x_revision;
2373 exception
2374 when others then
2375 x_note := null;
2376 end;
2377 end if;
2378
2379 else
2380 select DECODE( nvl(pll.cancel_flag,'N'),
2381 'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'),
2382 'N',DECODE(NVL(pll.CONSIGNED_FLAG,'N'),
2383 'Y',DECODE(NVL(pll.CLOSED_CODE,'OPEN'),'CLOSED FOR INVOICE',
2384 DECODE(
2385 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2386 (1.0,FND_API.G_FALSE,pll.line_location_id,
2387 pll.po_header_id, p_po_release_id,p_revision_num),
2388 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2389 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2390 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2391 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2392 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2393 '',polc.displayed_field),
2394 polc.displayed_field
2395 ),
2396 'N',
2397 CASE
2398 when NVL(pll.CLOSED_CODE,'OPEN')='OPEN' OR NVL(pll.CLOSED_CODE,'OPEN')='CLOSED FOR RECEIVING'
2399 OR NVL(pll.CLOSED_CODE,'OPEN') = 'CLOSED FOR INVOICE'
2400 THEN
2401 DECODE(
2402 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2403 (1.0,FND_API.G_FALSE,pll.line_location_id,
2404 pll.po_header_id, p_po_release_id,p_revision_num),
2405 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2406 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2407 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2408 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2409 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2410 '',polc.displayed_field)
2411 ELSE polc.displayed_field
2412 END
2413 )
2414 ) ,
2415 nvl(pll.closed_code,'OPEN'),
2416 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2417 (1.0,FND_API.G_FALSE,pll.line_location_id,
2418 pll.po_header_id, p_po_release_id,p_revision_num),nvl(por.acceptance_required_flag,'N'),
2419 por.revision_num,nvl(pll.consigned_flag,'N')
2420 into x_msg_display,x_msg_code,x_ack_stat,x_accp_flag,x_revision,x_cons_flag
2421 from po_line_locations_all pll,
2422 po_releases_all por,
2423 po_lookup_codes polc
2424 where
2425 polc.lookup_code = NVL(pll.closed_code, 'OPEN') and
2426 polc.lookup_type = 'DOCUMENT STATE' and
2427 por.po_header_id = pll.po_header_id and
2428 por.po_release_id = p_po_release_id and
2429 pll.line_location_id = p_line_location_id ;
2430
2431 if ( x_ack_stat = 'REJECTED') then
2432 begin
2433 select note into x_note
2434 from po_acceptances
2435 where po_line_location_id=p_line_location_id and
2436 revision_num = x_revision;
2437 exception
2438 when others then
2439 x_note := null;
2440 end;
2441 end if;
2442
2443 end if;
2444
2445 --Bug 4107241: allow acknowledge any shipments not closed/finally closed.
2446 if (x_ack_stat = 'ACK_REQUIRED' and
2447 x_msg_code not in ('CLOSED', 'FINALLY CLOSED') ) then
2448 x_msg_code :='ACK REQUIRED';
2449 -- valid assumption for now, coz this values is reqd in UI
2450 -- to display ack actions in poplist , where only PO's with
2451 -- status OPEN ack is allowed
2452 elsif x_ack_stat = 'REJECTED' then
2453 x_msg_code := 'REJECTED';
2454 elsif (x_ack_stat = 'ACCEPTED' and x_accp_flag='Y') then
2455 x_msg_code := 'ACKSTAGE';
2456 elsif x_ack_stat = 'PENDING_CANCEL' then
2457 x_msg_code := 'PENDING_CANCEL';
2458
2459 end if;
2460 else
2461 -- This means a split shipment pass back PENDING
2462 select FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE')
2463 into x_msg_display
2464 from dual;
2465
2466 x_msg_code :='PENDING_CHANGE';
2467 end if;
2468
2469 END getShipmentStatus;
2470
2471 procedure save_cancel_request(
2472 p_api_version IN NUMBER ,
2473 p_Init_Msg_List IN VARCHAR2 ,
2474 x_return_status OUT NOCOPY VARCHAR2,
2475 p_po_header_id IN number,
2476 p_po_release_id IN number,
2477 p_revision_num IN number,
2478 p_po_change_requests IN pos_chg_rec_tbl,
2479 x_request_group_id OUT NOCOPY NUMBER) is
2480
2481 l_api_name CONSTANT VARCHAR2(30) := 'save_cancel_request';
2482 l_api_version_number CONSTANT NUMBER := 1.0;
2483 l_request_group_id NUMBER;
2484
2485 BEGIN
2486 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2487 -- initialize message list
2488 FND_MSG_PUB.initialize;
2489 END IF;
2490
2491 -- Standard call to check for call compatibility.
2492
2493 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2494 p_api_version,
2495 l_api_name,
2496 G_PKG_NAME)
2497 THEN
2498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2499 END IF;
2500
2501 -- initialize return status
2502 x_return_status := FND_API.G_RET_STS_SUCCESS;
2503 save_request(
2504 p_api_version => 1.0,
2505 p_init_msg_list => FND_API.G_FALSE,
2506 x_return_status => x_return_status,
2507 p_po_header_id => p_po_header_id,
2508 p_po_release_id => p_po_release_id,
2509 p_revision_num => p_revision_num,
2510 p_po_change_requests => p_po_change_requests,
2511 x_request_group_id => l_request_group_id);
2512
2513 -- Call Update PO Procedure to set PO in IN PROCESS
2514 update_po_attributes(p_po_header_id,
2515 p_po_release_id,
2516 p_revision_num,
2517 l_request_group_id,
2518 x_return_status);
2519
2520 -- Start the workflow for cancel request
2521 if (x_return_status = FND_API.G_RET_STS_SUCCESS ) then
2522 x_return_status := startSupplierWF(
2523 p_po_header_id,p_po_release_id,p_revision_num,
2524 l_request_group_id,'N');
2525 end if;
2526
2527 EXCEPTION
2528 WHEN FND_API.g_exc_error THEN
2529 x_return_status := FND_API.g_ret_sts_error;
2530 WHEN FND_API.g_exc_unexpected_error THEN
2531 x_return_status := FND_API.g_ret_sts_unexp_error;
2532 WHEN OTHERS THEN
2533 x_return_status := FND_API.g_ret_sts_unexp_error;
2534
2535 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2536 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2537 IF g_fnd_debug = 'Y' THEN
2538 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2539 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2540 l_api_name || '.others exception' ,sqlcode);
2541 END IF;
2542 END IF;
2543 END IF;
2544
2545
2546 END save_cancel_request;
2547
2548 /**
2549 * Private Function: getLineAttrs
2550 * Requires: PO_LINE_ID
2551 * Modifies: None
2552 * Effects:
2553 * Determines if there is a Global Agreement,Un Number, Haz Class
2554 * Returns:
2555 * x_ga_number, x_un_number, x_haz_class
2556 */
2557 procedure getLineAttrs(
2558 p_from_header_id IN NUMBER,
2559 p_un_number_id IN NUMBER,
2560 p_haz_class_id IN NUMBER,
2561 x_ga_number OUT NOCOPY VARCHAR2,
2562 x_un_number OUT NOCOPY VARCHAR2,
2563 x_haz_class_desc OUT NOCOPY VARCHAR2) is
2564
2565 BEGIN
2566
2567 BEGIN
2568 SELECT segment1
2569 INTO x_ga_number
2570 FROM po_headers_all
2571 WHERE po_header_id = p_from_header_id
2572 AND global_agreement_flag='Y';
2573 EXCEPTION
2574 when no_data_found then
2575 x_ga_number := null;
2576 END;
2577
2578 if p_un_number_id is not null then
2579
2580 BEGIN
2581 SELECT UN_NUMBER
2582 INTO x_un_number
2583 FROM PO_UN_NUMBERS_TL
2584 WHERE UN_NUMBER_ID = p_un_number_id
2585 AND LANGUAGE = USERENV('LANG');
2586 --AND SOURCE_LANG = USERENV('LANG'); Bug 3637026
2587 EXCEPTION
2588 when no_data_found then
2589 x_un_number := null;
2590 END;
2591 end if;
2592
2593 if p_haz_class_id is not null then
2594
2595 BEGIN
2596 SELECT DESCRIPTION
2597 INTO x_haz_class_desc
2598 FROM PO_HAZARD_CLASSES_TL
2599 WHERE HAZARD_CLASS_ID = p_haz_class_id
2600 AND LANGUAGE = USERENV('LANG');
2601 --AND SOURCE_LANG = USERENV('LANG'); Bug 3637026
2602 EXCEPTION
2603 when no_data_found then
2604 x_haz_class_desc := null;
2605 END;
2606 end if;
2607
2608 END getLineAttrs;
2609
2610 /**
2611 * Procedure: cancel_change_request
2612 * Requires: PO_LINE_ID or po_line_location_id
2613 * Modifies: None
2614 * Effects:
2615 * Determines if there is any change request pending approval for the buyer
2616 * and cancels the request subsequently
2617 *
2618 */
2619 PROCEDURE cancel_change_request
2620 (p_api_version IN NUMBER,
2621 p_init_msg_list IN VARCHAR2,
2622 x_return_status OUT NOCOPY VARCHAR2,
2623 p_po_header_id IN NUMBER,
2624 p_po_release_id IN NUMBER,
2625 p_po_line_id IN NUMBER,
2626 p_po_line_location_id IN NUMBER)
2627 IS
2628
2629 l_api_name CONSTANT VARCHAR2(30) := 'cancel_change_request';
2630 l_api_version CONSTANT NUMBER := 1.0;
2631 l_cancel_msg fnd_new_messages.message_text%type := fnd_message.get_string('POS','POS_AUTO_CANCEL_BY_BUYER');
2632 xGrpId number := 0;
2633 xRevNum number ;
2634 lGrpId number := 0;
2635 lRevNum number ;
2636 l_return_status varchar2(1);
2637 l_msg_out varchar2(2000);
2638 l_revision_num number;
2639
2640 cursor c1(p_po_header_id in number) is
2641 select change_request_group_id,DOCUMENT_REVISION_NUM
2642 from po_change_requests
2643 where document_header_id = p_po_header_id and
2644 document_type = 'PO' and
2645 change_active_flag= 'Y' and
2646 initiator = 'SUPPLIER' and
2647 request_status not in ('ACCEPTED', 'REJECTED');
2648
2649 cursor c2(p_po_release_id in number) is
2650 select change_request_group_id,DOCUMENT_REVISION_NUM
2651 from po_change_requests
2652 where po_release_id = p_po_release_id and
2653 document_type = 'RELEASE' and
2654 change_active_flag= 'Y' and
2655 initiator = 'SUPPLIER' and
2656 request_status not in ('ACCEPTED', 'REJECTED');
2657
2658 BEGIN
2659 -- Start standard API initialization
2660 IF FND_API.to_boolean(p_init_msg_list) THEN
2661 FND_MSG_PUB.initialize;
2662 END IF;
2663 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2664 l_api_name, g_pkg_name)
2665 THEN
2666 RAISE FND_API.g_exc_unexpected_error;
2667 END IF;
2668 x_return_status := FND_API.g_ret_sts_success;
2669 -- End standard API initialization
2670
2671 -- Get the change request group id for the change requests if any
2672 if (p_po_release_id is not null) then
2673 open c2(p_po_release_id);
2674 fetch c2 into xGrpId,xRevNum;
2675 close c2;
2676 else
2677 open c1(p_po_header_id);
2678 fetch c1 into xGrpId,xRevNum;
2679 close c1;
2680 end if;
2681
2682 if (xGrpId > 0) then
2683 l_cancel_msg := fnd_message.get_string('POS','POS_AUTO_CANCEL_BY_BUYER') ;
2684 if (p_po_release_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2685 begin
2686 update po_change_requests
2687 set request_status = 'REJECTED',change_active_flag = 'N',
2688 request_reason=l_cancel_msg
2689 where po_release_id = p_po_release_id and
2690 initiator = 'SUPPLIER' and
2691 request_status not in ('ACCEPTED','REJECTED') and
2692 action_type='MODIFICATION';
2693
2694 update po_change_requests
2695 set request_status = 'ACCEPTED',
2696 change_active_flag = 'N'
2697 where initiator = 'SUPPLIER' and
2698 request_status not in ('ACCEPTED','REJECTED') and
2699 action_type='CANCELLATION' and
2700 po_release_id = p_po_release_id;
2701 exception
2702 when no_data_found then
2703 null;
2704 end;
2705 end if;
2706
2707 if (p_po_header_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2708 begin
2709 update po_change_requests
2710 set request_status = 'REJECTED',
2711 change_active_flag = 'N',
2712 request_reason=l_cancel_msg
2713 where document_header_id = p_po_header_id and
2714 request_status not in ('ACCEPTED','REJECTED') and
2715 initiator = 'SUPPLIER' and
2716 action_type='MODIFICATION';
2717
2718 update po_change_requests
2719 set request_status = 'ACCEPTED',
2720 change_active_flag = 'N'
2721 where request_status not in ('ACCEPTED','REJECTED') and
2722 initiator = 'SUPPLIER' and
2723 action_type='CANCELLATION' and
2724 document_header_id = p_po_header_id ;
2725 exception
2726 when no_data_found then
2727 null;
2728 end;
2729 end if;
2730
2731 if (p_po_line_location_id is not null ) then
2732 begin
2733 update po_change_requests
2734 set request_status = 'REJECTED',change_active_flag = 'N',request_reason=l_cancel_msg
2735 where document_line_location_id = p_po_line_location_id and
2736 request_level = 'SHIPMENT' and
2737 request_status not in ('ACCEPTED','REJECTED') and
2738 initiator = 'SUPPLIER' and
2739 action_type='MODIFICATION';
2740
2741 update po_change_requests
2742 set request_status = 'ACCEPTED',change_active_flag = 'N'
2743 where document_line_location_id = p_po_line_location_id and
2744 request_level = 'SHIPMENT' and
2745 request_status not in ('ACCEPTED','REJECTED') and
2746 initiator = 'SUPPLIER' and
2747 action_type='CANCELLATION';
2748
2749 exception
2750 when no_data_found then
2751 null;
2752 end;
2753 end if;
2754
2755 if (p_po_line_id is not null and p_po_line_location_id is null ) then
2756 begin
2757 update po_change_requests
2758 set request_status='REJECTED',change_active_flag='N',request_reason=l_cancel_msg
2759 where document_line_id = p_po_line_id and
2760 request_status not in ('ACCEPTED','REJECTED') and
2761 initiator = 'SUPPLIER' and
2762 action_type='MODIFICATION';
2763
2764 update po_change_requests
2765 set request_status='ACCEPTED',change_active_flag='N'
2766 where document_line_id = p_po_line_id and
2767 request_status not in ('ACCEPTED','REJECTED') and
2768 initiator = 'SUPPLIER' and
2769 action_type='CANCELLATION';
2770
2771 exception
2772 when no_data_found then
2773 null;
2774 end;
2775 end if;
2776
2777 -- reset document status to approved if there are no more changes pending by supplier
2778
2779 if (p_po_release_id is not null) then
2780 open c2(p_po_release_id);
2781 fetch c2 into lGrpId,lRevNum;
2782 close c2;
2783 if (lGrpId is null) then
2784
2785 update po_releases_all set
2786 authorization_status = 'APPROVED',
2787 CHANGE_REQUESTED_BY = null,
2788 revised_date = sysdate,
2789 last_update_date = sysdate,
2790 last_updated_by = fnd_global.user_id,
2791 last_update_login = fnd_global.login_id,
2792 request_id = fnd_global.conc_request_id,
2793 program_application_id = fnd_global.prog_appl_id,
2794 program_id = fnd_global.conc_program_id,
2795 program_update_date = sysdate
2796 where po_release_id = p_po_release_id;
2797 -- Update all the change requests with current revision number
2798 else
2799 begin
2800 select revision_num
2801 into l_revision_num
2802 from po_releases_all
2803 where po_release_id = p_po_release_id;
2804 exception
2805 when others then
2806 raise;
2807 end;
2808 update po_change_requests
2809 set document_revision_num = l_revision_num
2810 where po_release_id = p_po_release_id and
2811 request_status not in ('ACCEPTED','REJECTED') and
2812 document_type = 'RELEASE' and
2813 change_active_flag= 'Y' and
2814 initiator = 'SUPPLIER' ;
2815 end if;
2816 else
2817 open c1(p_po_header_id);
2818 fetch c1 into lGrpId,lRevNum;
2819 close c1;
2820 if (lGrpId is null) then
2821
2822 update po_headers_all set
2823 authorization_status = 'APPROVED',
2824 CHANGE_REQUESTED_BY = null,
2825 last_update_date = sysdate,
2826 last_updated_by = fnd_global.user_id,
2827 last_update_login = fnd_global.login_id,
2828 request_id = fnd_global.conc_request_id,
2829 program_application_id = fnd_global.prog_appl_id,
2830 program_id = fnd_global.conc_program_id,
2831 program_update_date = sysdate
2832 where po_header_id = p_po_header_id;
2833 -- Update all the change requests with current revision number
2834 else
2835
2836 begin
2837 select revision_num
2838 into l_revision_num
2839 from po_headers_all
2840 where po_header_id = p_po_header_id;
2841 exception
2842 when others then
2843 raise;
2844 end;
2845 update po_change_requests
2846 set document_revision_num=l_revision_num
2847 where document_header_id = p_po_header_id and
2848 request_status not in ('ACCEPTED','REJECTED') and
2849 document_type = 'PO' and
2850 change_active_flag= 'Y' and
2851 initiator = 'SUPPLIER' ;
2852 end if;
2853 end if;
2854 -- Call process Response to send notification if there are no more changes
2855 -- in change request table
2856
2857 if (lGrpId is null) then
2858 po_sup_chg_request_wf_grp.Buyer_CancelDocWithChn(
2859 1.0,l_return_status,p_po_header_id,p_po_release_id,xRevNum,xGrpId,l_msg_out);
2860 if (l_return_status <> 'S') then
2861 IF (g_fnd_debug = 'Y') THEN
2862 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2863 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2864 l_api_name || '.call buyer cancel workflow', l_msg_out);
2865 END IF;
2866 END IF;
2867 end if;
2868 end if; -- if lGrpId is null
2869 end if; -- if xGrpId > 0
2870
2871
2872
2873 EXCEPTION
2874 WHEN FND_API.g_exc_error THEN
2875 x_return_status := FND_API.g_ret_sts_error;
2876 WHEN FND_API.g_exc_unexpected_error THEN
2877 x_return_status := FND_API.g_ret_sts_unexp_error;
2878 WHEN OTHERS THEN
2879 x_return_status := FND_API.g_ret_sts_unexp_error;
2880 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2881 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2882 IF (g_fnd_debug = 'Y') THEN
2883 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2884 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2885 l_api_name || '.others_exception', 'Exception');
2886 END IF;
2887 END IF;
2888 END IF;
2889 END cancel_change_request;
2890
2891 procedure process_supplier_signature (
2892 p_api_version IN NUMBER,
2893 p_Init_Msg_List IN VARCHAR2,
2894 x_return_status OUT NOCOPY VARCHAR2,
2895 x_notification_id OUT NOCOPY NUMBER,
2896 p_po_header_id IN number,
2897 p_revision_num IN number,
2898 p_document_subtype IN VARCHAR2,
2899 p_document_number IN VARCHAR2,
2900 p_org_id IN NUMBER,
2901 p_Agent_Id IN NUMBER,
2902 p_supplier_user_id IN number)
2903 IS
2904
2905 l_api_version_number CONSTANT NUMBER := 1.0;
2906 l_api_name CONSTANT VARCHAR2(30) := 'process_supplier_signature';
2907 l_item_key WF_ITEMS.item_key%TYPE := NULL;
2908 l_item_type WF_ITEMS.item_type%TYPE;
2909 x_result VARCHAR2(20);
2910 x_sup_user_id NUMBER;
2911 l_supplier_username fnd_user.user_name%type;
2912 sig_notif_notfound exception;
2913 l_document_type VARCHAR2(20);
2914 l_msg_count NUMBER;
2915 l_msg_data VARCHAR2(2000);
2916 l_ret_status VARCHAR2(20);
2917 BEGIN
2918
2919 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2920 -- initialize message list
2921 FND_MSG_PUB.initialize;
2922 END IF;
2923
2924 -- Standard call to check for call compatibility.
2925
2926 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2927 p_api_version,
2928 l_api_name,
2929 G_PKG_NAME)
2930 THEN
2931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2932 END IF;
2933
2934 -- initialize return status
2935 x_return_status := FND_API.G_RET_STS_SUCCESS;
2936 if (p_document_subtype in ('STANDARD','PLANNED')) then
2937 l_document_type := 'PO';
2938
2939 elsif (p_document_subtype in ('BLANKET','CONTRACT')) then
2940 l_document_type := 'PA';
2941 end if;
2942
2943 select wf_item_type
2944 into l_item_type
2945 from po_headers_all
2946 WHERE po_header_id = p_po_header_id;
2947
2948
2949 -- First Find the Item Key for this Document if it were ever generated
2950 BEGIN
2951 PO_SIGNATURE_GRP.Find_Item_Key(
2952 p_api_version => 1.0,
2953 p_init_msg_list => FND_API.G_FALSE,
2954 p_po_header_id => p_po_header_id,
2955 p_revision_num => p_revision_num ,
2956 p_document_type => l_document_type ,
2957 x_itemkey => l_item_key,
2958 x_result => x_result,
2959 x_return_status => l_ret_status,
2960 x_msg_count => l_msg_count,
2961 x_msg_data => l_msg_data );
2962 END;
2963
2964
2965
2966 -- To create Item key for the Document Signature Process
2967 IF (l_item_key is null) then
2968 BEGIN
2969
2970 PO_SIGNATURE_GRP.Get_Item_Key(
2971 p_api_version => 1.0,
2972 p_init_msg_list => FND_API.G_FALSE,
2973 p_po_header_id => p_po_header_id,
2974 p_revision_num => p_revision_num ,
2975 p_document_type => l_document_type ,
2976 x_itemkey => l_item_key,
2977 x_result => x_result,
2978 x_return_status => l_ret_status,
2979 x_msg_count => l_msg_count,
2980 x_msg_data => l_msg_data );
2981
2982 END;
2983
2984
2985 -- Start Signature Workflow and pass the Newly generated Item Key
2986 -- Create a Workflow Process
2987
2988 x_return_status := startSignatureWF (
2989 l_item_type,
2990 l_item_key ,
2991 p_po_header_id ,
2992 p_revision_num ,
2993 l_document_type,
2994 p_document_subtype,
2995 p_document_number,
2996 p_org_id ,
2997 p_Agent_Id ,
2998 p_supplier_user_id ) ;
2999
3000 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
3001 x_notification_id := getSigNotifId(l_item_type, l_item_key);
3002 if x_notification_id is null then
3003 RAISE sig_notif_notfound;
3004 end if;
3005 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3006 FND_LOG.string(FND_LOG.level_unexpected, l_api_name ||
3007 l_item_key || '.Notification :' || to_char(x_notification_id) ,sqlcode);
3008 END IF;
3009
3010 return;
3011 end if;
3012
3013
3014 ELSE
3015
3016 -- Find the Notification generated for the given Item Key
3017 -- Compare the User Id with the Workflow Invoker's User Id
3018 x_sup_user_id := wf_engine.GetItemAttrNumber (itemtype => l_item_type,
3019 itemkey => l_item_key,
3020 aname => 'SUPPLIER_USER_ID');
3021
3022 if (x_sup_user_id = p_supplier_user_id) then
3023 -- get the signature notification for the item key
3024 x_notification_id := getSigNotifId(l_item_type, l_item_key);
3025 if (x_notification_id is null) then
3026 RAISE sig_notif_notfound;
3027 end if;
3028 return;
3029 else
3030 -- If the Notification was not generated for the same user the
3031 -- Abort the previous process and start a new one
3032 BEGIN
3033 PO_SIGNATURE_GRP.Abort_Doc_Sign_Process(
3034 p_api_version => 1.0,
3035 p_init_msg_list => FND_API.G_FALSE,
3036 p_itemkey => l_item_key,
3037 x_result => x_result,
3038 x_return_status => l_ret_status,
3039 x_msg_count => l_msg_count,
3040 x_msg_data => l_msg_data );
3041
3042 END;
3043 -- Generate new Item Key
3044 BEGIN
3045
3046 PO_SIGNATURE_GRP.Get_Item_Key(
3047 p_api_version => 1.0,
3048 p_init_msg_list => FND_API.G_FALSE,
3049 p_po_header_id => p_po_header_id,
3050 p_revision_num => p_revision_num ,
3051 p_document_type => l_document_type ,
3052 x_itemkey => l_item_key,
3053 x_result => x_result,
3054 x_return_status => l_ret_status,
3055 x_msg_count => l_msg_count,
3056 x_msg_data => l_msg_data );
3057 END;
3058
3059 -- Start Signature Workflow and pass the Newly generated Item Key
3060 -- Create a Workflow Process
3061
3062 x_return_status := startSignatureWF (
3063 l_item_type,
3064 l_item_key ,
3065 p_po_header_id ,
3066 p_revision_num ,
3067 l_document_type,
3068 p_document_subtype,
3069 p_document_number,
3070 p_org_id ,
3071 p_Agent_Id ,
3072 p_supplier_user_id ) ;
3073
3074 if (x_return_status = 'S') then
3075 x_notification_id := getSigNotifId(l_item_type, l_item_key);
3076 if (x_notification_id is null) then
3077 RAISE sig_notif_notfound;
3078 end if;
3079
3080 return;
3081 end if;
3082
3083 end if;
3084 END IF;
3085
3086 EXCEPTION
3087 WHEN FND_API.g_exc_error THEN
3088 x_return_status := FND_API.g_ret_sts_error;
3089 WHEN FND_API.g_exc_unexpected_error THEN
3090 x_return_status := FND_API.g_ret_sts_unexp_error;
3091 WHEN sig_notif_notfound THEN
3092 IF g_fnd_debug = 'Y' THEN
3093 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3094 FND_LOG.string(FND_LOG.level_unexpected, l_api_name ||
3095 l_item_key || '.Notification not found exception' ,sqlcode);
3096 END IF;
3097 END IF;
3098 x_return_status := FND_API.g_ret_sts_error;
3099 WHEN OTHERS THEN
3100 x_return_status := FND_API.g_ret_sts_unexp_error;
3101 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3102 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3103 IF g_fnd_debug = 'Y' THEN
3104 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3105 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3106 l_api_name || '.others exception' ,sqlcode);
3107 END IF;
3108 END IF;
3109 END IF;
3110 END process_supplier_signature;
3111
3112
3113 procedure process_supplier_signature (
3114 p_api_version IN NUMBER,
3115 p_Init_Msg_List IN VARCHAR2,
3116 x_return_status OUT NOCOPY VARCHAR2,
3117 x_notification_id OUT NOCOPY NUMBER,
3118 p_po_header_id IN number,
3119 p_revision_num IN number,
3120 p_document_subtype IN VARCHAR2,
3121 p_document_number IN VARCHAR2,
3122 p_org_id IN NUMBER,
3123 p_Agent_Id IN NUMBER,
3124 p_supplier_user_id IN NUMBER,
3125 p_draft_id IN number)
3126 IS
3127
3128 l_api_version_number CONSTANT NUMBER := 1.0;
3129 l_api_name CONSTANT VARCHAR2(30) := 'process_supplier_signature';
3130 l_item_key WF_ITEMS.item_key%TYPE := NULL;
3131 l_item_type WF_ITEMS.item_type%TYPE;
3132 x_result VARCHAR2(20);
3133 x_sup_user_id NUMBER;
3134 l_supplier_username fnd_user.user_name%type;
3135 sig_notif_notfound exception;
3136 l_document_type VARCHAR2(20);
3137 l_msg_count NUMBER;
3138 l_msg_data VARCHAR2(2000);
3139 l_ret_status VARCHAR2(20);
3140 BEGIN
3141
3142 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
3143 -- initialize message list
3144 FND_MSG_PUB.initialize;
3145 END IF;
3146
3147 -- Standard call to check for call compatibility.
3148
3149 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3150 p_api_version,
3151 l_api_name,
3152 G_PKG_NAME)
3153 THEN
3154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3155 END IF;
3156
3157 -- initialize return status
3158 x_return_status := FND_API.G_RET_STS_SUCCESS;
3159 if (p_document_subtype in ('STANDARD','PLANNED')) then
3160 l_document_type := 'PO';
3161
3162 elsif (p_document_subtype in ('BLANKET','CONTRACT')) then
3163 l_document_type := 'PA';
3164 end if;
3165
3166 select wf_item_type
3167 into l_item_type
3168 from po_headers_merge_v
3169 WHERE po_header_id = p_po_header_id AND
3170 draft_id = p_draft_id;
3171
3172
3173 -- First Find the Item Key for this Document if it were ever generated
3174 BEGIN
3175 PO_SIGNATURE_GRP.Find_Item_Key(
3176 p_api_version => 1.0,
3177 p_init_msg_list => FND_API.G_FALSE,
3178 p_po_header_id => p_po_header_id,
3179 p_revision_num => p_revision_num ,
3180 p_document_type => l_document_type ,
3181 x_itemkey => l_item_key,
3182 x_result => x_result,
3183 x_return_status => l_ret_status,
3184 x_msg_count => l_msg_count,
3185 x_msg_data => l_msg_data );
3186 END;
3187
3188
3189
3190 -- To create Item key for the Document Signature Process
3191 IF (l_item_key is null) then
3192 BEGIN
3193
3194 PO_SIGNATURE_GRP.Get_Item_Key(
3195 p_api_version => 1.0,
3196 p_init_msg_list => FND_API.G_FALSE,
3197 p_po_header_id => p_po_header_id,
3198 p_revision_num => p_revision_num ,
3199 p_document_type => l_document_type ,
3200 x_itemkey => l_item_key,
3201 x_result => x_result,
3202 x_return_status => l_ret_status,
3203 x_msg_count => l_msg_count,
3204 x_msg_data => l_msg_data );
3205
3206 END;
3207
3208
3209 -- Start Signature Workflow and pass the Newly generated Item Key
3210 -- Create a Workflow Process
3211
3212 x_return_status := startSignatureWF (
3213 l_item_type,
3214 l_item_key ,
3215 p_po_header_id ,
3216 p_revision_num ,
3217 l_document_type,
3218 p_document_subtype,
3219 p_document_number,
3220 p_org_id ,
3221 p_Agent_Id ,
3222 p_supplier_user_id,
3223 p_draft_id ) ;
3224
3225 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
3226 x_notification_id := getSigNotifId(l_item_type, l_item_key);
3227 if x_notification_id is null then
3228 RAISE sig_notif_notfound;
3229 end if;
3230 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3231 FND_LOG.string(FND_LOG.level_unexpected, l_api_name ||
3232 l_item_key || '.Notification :' || to_char(x_notification_id) ,sqlcode);
3233 END IF;
3234
3235 return;
3236 end if;
3237
3238
3239 ELSE
3240
3241 -- Find the Notification generated for the given Item Key
3242 -- Compare the User Id with the Workflow Invoker's User Id
3243 x_sup_user_id := wf_engine.GetItemAttrNumber (itemtype => l_item_type,
3244 itemkey => l_item_key,
3245 aname => 'SUPPLIER_USER_ID');
3246
3247 if (x_sup_user_id = p_supplier_user_id) then
3248 -- get the signature notification for the item key
3249 x_notification_id := getSigNotifId(l_item_type, l_item_key);
3250 if (x_notification_id is null) then
3251 RAISE sig_notif_notfound;
3252 end if;
3253 return;
3254 else
3255 -- If the Notification was not generated for the same user the
3256 -- Abort the previous process and start a new one
3257 BEGIN
3258 PO_SIGNATURE_GRP.Abort_Doc_Sign_Process(
3259 p_api_version => 1.0,
3260 p_init_msg_list => FND_API.G_FALSE,
3261 p_itemkey => l_item_key,
3262 x_result => x_result,
3263 x_return_status => l_ret_status,
3264 x_msg_count => l_msg_count,
3265 x_msg_data => l_msg_data );
3266
3267 END;
3268 -- Generate new Item Key
3269 BEGIN
3270
3271 PO_SIGNATURE_GRP.Get_Item_Key(
3272 p_api_version => 1.0,
3273 p_init_msg_list => FND_API.G_FALSE,
3274 p_po_header_id => p_po_header_id,
3275 p_revision_num => p_revision_num ,
3276 p_document_type => l_document_type ,
3277 x_itemkey => l_item_key,
3278 x_result => x_result,
3279 x_return_status => l_ret_status,
3280 x_msg_count => l_msg_count,
3281 x_msg_data => l_msg_data );
3282 END;
3283
3284 -- Start Signature Workflow and pass the Newly generated Item Key
3285 -- Create a Workflow Process
3286
3287 x_return_status := startSignatureWF (
3288 l_item_type,
3289 l_item_key ,
3290 p_po_header_id ,
3291 p_revision_num ,
3292 l_document_type,
3293 p_document_subtype,
3294 p_document_number,
3295 p_org_id ,
3296 p_Agent_Id ,
3297 p_supplier_user_id,
3298 p_draft_id ) ;
3299
3300 if (x_return_status = 'S') then
3301 x_notification_id := getSigNotifId(l_item_type, l_item_key);
3302 if (x_notification_id is null) then
3303 RAISE sig_notif_notfound;
3304 end if;
3305
3306 return;
3307 end if;
3308
3309 end if;
3310 END IF;
3311
3312 EXCEPTION
3313 WHEN FND_API.g_exc_error THEN
3314 x_return_status := FND_API.g_ret_sts_error;
3315 WHEN FND_API.g_exc_unexpected_error THEN
3316 x_return_status := FND_API.g_ret_sts_unexp_error;
3317 WHEN sig_notif_notfound THEN
3318 IF g_fnd_debug = 'Y' THEN
3319 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3320 FND_LOG.string(FND_LOG.level_unexpected, l_api_name ||
3321 l_item_key || '.Notification not found exception' ,sqlcode);
3322 END IF;
3323 END IF;
3324 x_return_status := FND_API.g_ret_sts_error;
3325 WHEN OTHERS THEN
3326 x_return_status := FND_API.g_ret_sts_unexp_error;
3327 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3328 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3329 IF g_fnd_debug = 'Y' THEN
3330 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3331 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3332 l_api_name || '.others exception' ,sqlcode);
3333 END IF;
3334 END IF;
3335 END IF;
3336 END process_supplier_signature;
3337
3338 function create_pos_change_rec (
3339 p_Action_Type IN VARCHAR2, --(30),
3340 p_Initiator IN VARCHAR2, --(30),
3341 p_Document_Type IN VARCHAR2, --(30),
3342 p_Request_Level IN VARCHAR2, --(30),
3343 p_Request_Status IN VARCHAR2, --(30),
3344 p_Document_Header_Id IN NUMBER,
3345 p_Request_Reason IN VARCHAR2 default null, --(2000),
3346 p_PO_Release_Id IN NUMBER default null,
3347 p_Document_Num IN VARCHAR2 default null, --(20),
3348 p_Document_Revision_Num IN NUMBER default null,
3349 p_Document_Line_Id IN NUMBER default null,
3350 p_Document_Line_Number IN NUMBER default null,
3351 p_Document_Line_Location_Id IN NUMBER default null,
3352 p_Document_Shipment_Number IN NUMBER default null,
3353 p_Document_Distribution_id IN NUMBER default null,
3354 p_Document_Distribution_Number IN NUMBER default null,
3355 p_Parent_Line_Location_Id IN NUMBER default null,
3356 p_Old_Quantity IN NUMBER default null,
3357 p_New_Quantity IN NUMBER default null,
3358 p_Old_Promised_Date IN DATE default null,
3359 p_New_Promised_Date IN DATE default null,
3360 p_Old_Supplier_Part_Number IN VARCHAR2 default null, --(25),
3361 p_New_Supplier_Part_Number IN VARCHAR2 default null, --(25),
3362 p_Old_Price IN NUMBER default null,
3363 p_New_Price IN NUMBER default null,
3364 p_Old_Supplier_Reference_Num IN VARCHAR2 default null, --(30),
3365 p_New_Supplier_Reference_Num IN VARCHAR2 default null, --(30),
3366 p_From_Header_id IN NUMBER default null,
3367 p_Recoverable_Tax IN NUMBER default null,
3368 p_Non_recoverable_tax IN NUMBER default null,
3369 p_Ship_To_Location_id IN NUMBER default null,
3370 p_Ship_To_Organization_Id IN NUMBER default null,
3371 p_Old_Need_By_Date IN DATE default null,
3372 p_New_Need_By_Date IN DATE default null,
3373 p_Approval_Required_Flag IN VARCHAR2 default null, --(1),
3374 p_Parent_Change_request_Id IN NUMBER default null,
3375 p_Requester_id IN NUMBER default null,
3376 p_Old_Supplier_Order_Number IN VARCHAR2 default null, --(25),
3377 p_New_Supplier_Order_Number IN VARCHAR2 default null, --(25),
3378 p_Old_Supplier_Order_Line_Num IN VARCHAR2 default null, --(25),
3379 p_New_Supplier_Order_Line_Num IN VARCHAR2 default null , --(25),
3380 p_Additional_changes IN VARCHAR2 default null, --(2000),
3381 p_old_Start_date IN DATE default null,
3382 p_new_Start_date IN DATE default null,
3383 p_old_Expiration_date IN DATE default null,
3384 p_new_Expiration_date IN DATE default null,
3385 p_old_Amount IN NUMBER default null,
3386 p_new_Amount IN NUMBER default null,
3387 p_SUPPLIER_DOC_REF IN varchar2 default null, --(256),
3388 p_SUPPLIER_LINE_REF IN varchar2 default null, --(256),
3389 p_SUPPLIER_SHIPMENT_REF IN varchar2 default null, --(256)
3390 --<< Complex work changes for R12 >>
3391 p_NEW_PROGRESS_TYPE IN varchar2 default null,
3392 p_NEW_PAY_DESCRIPTION IN varchar2 default null
3393
3394 ) return pos_chg_rec
3395 is
3396
3397 begin
3398 return pos_chg_rec(
3399 Action_Type => p_Action_Type,
3400 Initiator => p_Initiator,
3401 Request_Reason => p_Request_Reason,
3402 Document_Type => p_Document_Type,
3403 Request_Level => p_Request_Level,
3404 Request_Status => p_Request_Status,
3405 Document_Header_Id => p_Document_Header_Id,
3406 PO_Release_Id => p_PO_Release_Id,
3407 Document_Num => p_Document_Num,
3408 Document_Revision_Num => p_Document_Revision_Num,
3409 Document_Line_Id => p_Document_Line_Id,
3410 Document_Line_Number => p_Document_Line_Number,
3411 Document_Line_Location_Id => p_Document_Line_Location_Id,
3412 Document_Shipment_Number => p_Document_Shipment_Number,
3413 Document_Distribution_id => p_Document_Distribution_id,
3414 Document_Distribution_Number => p_Document_Distribution_Number,
3415 Parent_Line_Location_Id => p_Parent_Line_Location_Id,
3416 Old_Quantity => p_Old_Quantity,
3417 New_Quantity => p_New_Quantity,
3418 Old_Promised_Date => p_Old_Promised_Date,
3419 New_Promised_Date => p_New_Promised_Date,
3420 Old_Supplier_Part_Number => p_Old_Supplier_Part_Number,
3421 New_Supplier_Part_Number => p_New_Supplier_Part_Number,
3422 Old_Price => p_Old_Price,
3423 New_Price => p_New_Price,
3424 Old_Supplier_Reference_Number => p_Old_Supplier_Reference_Num,
3425 New_Supplier_Reference_Number => p_New_Supplier_Reference_Num,
3426 From_Header_id => p_From_Header_id,
3427 Recoverable_Tax => p_Recoverable_Tax,
3428 Non_recoverable_tax => p_Non_recoverable_tax,
3429 Ship_To_Location_id => p_Ship_To_Location_id,
3430 Ship_To_Organization_Id => p_Ship_To_Organization_Id,
3431 Old_Need_By_Date => p_Old_Need_By_Date,
3432 New_Need_By_Date => p_New_Need_By_Date,
3433 Approval_Required_Flag => p_Approval_Required_Flag,
3434 Parent_Change_request_Id => p_Parent_Change_request_Id,
3435 Requester_id => p_Requester_id,
3436 Old_Supplier_Order_Number => p_Old_Supplier_Order_Number,
3437 New_Supplier_Order_Number => p_New_Supplier_Order_Number,
3438 Old_Supplier_Order_Line_Number => p_Old_Supplier_Order_Line_Num,
3439 New_Supplier_Order_Line_Number => p_New_Supplier_Order_Line_Num,
3440 Additional_changes => p_Additional_changes,
3441 old_Start_date => p_old_Start_date,
3442 new_Start_date => p_new_Start_date,
3443 old_Expiration_date => p_old_Expiration_date,
3444 new_Expiration_date => p_new_Expiration_date,
3445 old_Amount => p_old_Amount,
3446 new_Amount => p_new_Amount,
3447 SUPPLIER_DOC_REF => p_SUPPLIER_DOC_REF,
3448 SUPPLIER_LINE_REF => p_SUPPLIER_LINE_REF,
3449 SUPPLIER_SHIPMENT_REF => p_SUPPLIER_SHIPMENT_REF ,
3450 --<< Complex work changes for R12 >>
3451 NEW_PROGRESS_TYPE =>p_NEW_PROGRESS_TYPE,
3452 NEW_PAY_DESCRIPTION =>p_NEW_PAY_DESCRIPTION
3453
3454
3455 );
3456
3457 end;
3458
3459 /*
3460 * Function to get maximum shipment number for a given po_line_id
3461 */
3462 function getMaxShipmentNum (
3463 p_po_line_id IN NUMBER)
3464 return NUMBER IS
3465
3466 v_ship_num NUMBER;
3467 v_progress varchar2(3);
3468
3469 BEGIN
3470
3471 v_progress := '111';
3472
3473 select max(shipment_num)
3474 into v_ship_num
3475 from po_line_locations_All
3476 where po_line_id = p_po_line_id
3477 group by po_line_id;
3478
3479 RETURN v_ship_num;
3480
3481 EXCEPTION
3482 WHEN others THEN
3483 PO_MESSAGE_S.SQL_ERROR(
3484 'PO_CHG_REQUEST_PVT.getMaxShipmentNum',
3485 v_progress,
3486 sqlcode );
3487
3488 RETURN -1;
3489
3490 END;
3491
3492 function getLastUpdateDate (
3493 p_header_id IN NUMBER,
3494 p_release_id in NUMBER)
3495 return DATE IS
3496
3497 p_last_update_date DATE;
3498 v_progress varchar2(3);
3499
3500 BEGIN
3501
3502 v_progress := '113';
3503
3504 if (p_header_id is null) then
3505
3506 select last_update_Date
3507 into p_last_update_date
3508 from po_releases_All where
3509 po_release_id = p_release_id
3510 and rownum=1;
3511
3512 else
3513
3514 select last_update_Date
3515 into p_last_update_date
3516 from po_headers_All
3517 where po_header_id = p_header_id
3518 and rownum=1;
3519
3520 end if;
3521
3522 return p_last_update_Date;
3523
3524 EXCEPTION
3525 WHEN others THEN
3526 PO_MESSAGE_S.SQL_ERROR(
3527 'PO_CHG_REQUEST_PVT.getLastUpdateDate',
3528 v_progress,
3529 sqlcode );
3530
3531 return null;
3532
3533 END;
3534
3535 /*Added release_id as part of fix for bug 12903291 */
3536 procedure validate_shipment_cancel (
3537 p_po_header_id IN number,
3538 p_po_release_id IN number,
3539 p_po_change_requests IN pos_chg_rec_tbl,
3540 x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3541 x_ret_sts OUT NOCOPY varchar2
3542 ) IS
3543
3544 l_po_change_requests pos_chg_rec_tbl := NULL;
3545 l_count_asn NUMBER;
3546 l_err_msg_name_tbl po_tbl_varchar30;
3547 l_err_msg_text_tbl po_tbl_varchar2000;
3548 l_err_count NUMBER;
3549
3550 BEGIN
3551 l_err_count := 0;
3552 l_po_change_requests := p_po_change_requests ;
3553 l_err_msg_name_tbl := po_tbl_varchar30();
3554 l_err_msg_text_tbl := po_tbl_varchar2000();
3555 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3556 x_ret_sts := 'N';
3557 FOR j in 1..l_po_change_requests.count()
3558 LOOP
3559 if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3560 -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3561 IF(p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL) THEN
3562 select count(*)
3563 into l_count_asn
3564 from RCV_TRANSACTIONS_INTERFACE rti
3565 where rti.TRANSACTION_TYPE = 'SHIP' and
3566 rti.PROCESSING_STATUS_CODE = 'PENDING' and
3567 rti.quantity > 0 and
3568 rti.PO_HEADER_ID = p_po_header_id and
3569 rti.po_release_id (+) = p_po_release_id and
3570 (rti.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null);
3571 ELSE
3572 select count(*)
3573 into l_count_asn
3574 from RCV_TRANSACTIONS_INTERFACE rti
3575 where rti.TRANSACTION_TYPE = 'SHIP' and
3576 rti.PROCESSING_STATUS_CODE = 'PENDING' and
3577 rti.quantity > 0 and
3578 rti.PO_HEADER_ID = p_po_header_id AND
3579 (rti.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null);
3580 END IF;
3581
3582 IF(l_count_asn > 0) then
3583 l_err_count := l_err_count + 1;
3584 x_pos_errors.message_name.extend;
3585 x_pos_errors.text_line.extend;
3586 x_pos_errors.message_name(l_err_count) := null;
3587 if l_po_change_requests(j).document_line_location_id is not null then
3588 FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3589 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3590 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3591 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3592 else
3593 x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_UNPRC_TX');
3594 return;
3595 END IF;
3596 END IF;
3597
3598 IF(p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL) THEN
3599 SELECT count(*)
3600 into l_count_asn
3601 FROM RCV_SHIPMENT_LINES RSL
3602 WHERE RSL.po_header_id = p_po_header_id
3603 AND RSL.po_release_id (+) = p_po_release_id
3604 AND (RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null)
3605 AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
3606 AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
3607 AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
3608 ELSE
3609 SELECT count(*)
3610 into l_count_asn
3611 FROM RCV_SHIPMENT_LINES RSL
3612 WHERE RSL.po_header_id = p_po_header_id
3613 AND (RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null)
3614 AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
3615 AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
3616 AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
3617 END IF;
3618
3619 IF(l_count_asn > 0) then
3620 l_err_count := l_err_count + 1;
3621 x_pos_errors.message_name.extend;
3622 x_pos_errors.text_line.extend;
3623 x_pos_errors.message_name(l_err_count) := null;
3624 if l_po_change_requests(j).document_line_location_id is not null then
3625 FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3626 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3627 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3628 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3629 else
3630 x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_OPEN_ASN') ;
3631 return;
3632 END IF;
3633 END IF;
3634 END IF;
3635 END LOOP;
3636 if(l_err_count < 1) then
3637 validate_ship_inv_cancel (
3638 p_po_header_id,
3639 p_po_change_requests,
3640 x_pos_errors,
3641 x_ret_sts);
3642 else
3643 x_ret_sts := 'Y';
3644 end if;
3645
3646 END validate_shipment_cancel;
3647
3648 procedure validate_ship_inv_cancel (
3649 p_po_header_id IN number,
3650 p_po_change_requests IN pos_chg_rec_tbl,
3651 x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3652 x_ret_sts OUT NOCOPY varchar2
3653 ) IS
3654
3655 l_po_change_requests pos_chg_rec_tbl := NULL;
3656 l_count_asn NUMBER;
3657 l_err_msg_name_tbl po_tbl_varchar30;
3658 l_err_msg_text_tbl po_tbl_varchar2000;
3659 l_err_count NUMBER;
3660 l_quan_ordered NUMBER;
3661 l_quan_recd NUMBER;
3662 l_quan_billed NUMBER;
3663 BEGIN
3664 l_err_count := 0;
3665 l_po_change_requests := p_po_change_requests ;
3666 l_err_msg_name_tbl := po_tbl_varchar30();
3667 l_err_msg_text_tbl := po_tbl_varchar2000();
3668 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3669 FOR j in 1..l_po_change_requests.count()
3670 LOOP
3671 if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3672 l_po_change_requests(j).request_level='SHIPMENT' ) then
3673 l_quan_ordered := -1;
3674 l_quan_recd := -1;
3675 l_quan_billed := -1;
3676 begin
3677 SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3678 into l_quan_ordered, l_quan_billed, l_quan_recd
3679 FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3680 WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3681 AND POLL.po_line_id = POL.po_line_id
3682 AND nvl(POLL.cancel_flag, 'N') = 'N'
3683 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3684 AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3685 AND nvl(POLL.quantity_billed, 0) > nvl(POLL.quantity_received,0);
3686 EXCEPTION
3687 WHEN OTHERS THEN
3688 l_quan_ordered := -1;
3689 END;
3690
3691 if (l_quan_ordered > -1 ) then
3692 l_err_count := l_err_count + 1;
3693 x_pos_errors.message_name.extend;
3694 x_pos_errors.text_line.extend;
3695 x_pos_errors.message_name(l_err_count) := null;
3696 FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_RCV');
3697 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3698 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3699 fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3700 fnd_message.set_token('QTY_RCV', l_quan_recd) ;
3701 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3702 end if;
3703
3704 l_quan_ordered := -1;
3705 l_quan_recd := -1;
3706 l_quan_billed := -1;
3707 begin
3708 SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3709 into l_quan_ordered, l_quan_billed, l_quan_recd
3710 FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3711 WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3712 AND POLL.po_line_id = POL.po_line_id
3713 AND nvl(POLL.cancel_flag, 'N') = 'N'
3714 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3715 AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3716 AND nvl(POLL.quantity_billed, 0) > nvl(POLL.quantity,0);
3717 EXCEPTION
3718 WHEN OTHERS THEN
3719 l_quan_ordered := -1;
3720 END;
3721 if (l_quan_ordered > -1 ) then
3722 l_err_count := l_err_count + 1;
3723 x_pos_errors.message_name.extend;
3724 x_pos_errors.text_line.extend;
3725 x_pos_errors.message_name(l_err_count) := null;
3726 FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_ORD');
3727 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3728 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3729 fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3730 fnd_message.set_token('QTY_ORD', l_quan_ordered) ;
3731 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3732 end if;
3733 END IF;
3734 END LOOP;
3735 if(l_err_count < 1) then
3736 x_ret_sts := 'N';
3737 else
3738 x_ret_sts := 'Y';
3739 end if ;
3740
3741 END validate_ship_inv_cancel;
3742
3743 /*Added for bug#14155598*/
3744 PROCEDURE IS_ASN_EXIST(p_po_header_id IN NUMBER,
3745 p_po_release_id IN NUMBER,
3746 p_po_change_requests IN POS_CHG_REC_TBL,
3747 x_pos_errors OUT nocopy POS_ERR_TYPE,
3748 x_ret_sts OUT nocopy VARCHAR2)
3749 IS
3750 l_po_change_requests POS_CHG_REC_TBL := NULL;
3751 l_count_asn NUMBER;
3752 l_err_msg_name_tbl PO_TBL_VARCHAR30;
3753 l_err_msg_text_tbl PO_TBL_VARCHAR2000;
3754 l_err_count NUMBER;
3755 l_api_name VARCHAR2(100) := 'IS_ASN_EXIST';
3756 CURSOR ship_cur(
3757 p_po_line_id NUMBER) IS
3758 SELECT line_location_id,shipment_num
3759 FROM po_line_locations_all
3760 WHERE po_line_id = p_po_line_id;
3761 BEGIN
3762 l_err_count := 0;
3763 l_po_change_requests := p_po_change_requests;
3764 l_err_msg_name_tbl := Po_tbl_varchar30();
3765 l_err_msg_text_tbl := Po_tbl_varchar2000();
3766 x_pos_errors := Pos_err_type(l_err_msg_name_tbl, l_err_msg_text_tbl);
3767 x_ret_sts := 'N';
3768
3769 IF g_fnd_debug = 'Y' THEN
3770 IF ( fnd_log.g_current_runtime_level <= fnd_log.level_procedure ) THEN
3771 fnd_log.String(fnd_log.level_procedure, g_module_prefix|| l_api_name|| '.invoked', 'Type: '
3772 || 'entered procedure'
3773 ||l_api_name
3774 ||', Header ID: '
3775 ||Nvl(To_char(p_po_header_id), 'null')
3776 ||', Release ID: '
3777 ||Nvl(To_char(p_po_release_id), 'null'));
3778 END IF;
3779 END IF;
3780
3781 FOR j IN 1..l_po_change_requests.Count() LOOP
3782 IF( l_po_change_requests(j).request_level = 'SHIPMENT' AND l_po_change_requests(j).action_type = 'MODIFICATION' ) THEN
3783 IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3784 SELECT Count(*)
3785 INTO l_count_asn
3786 FROM rcv_transactions_interface rti
3787 WHERE rti.processing_status_code = 'PENDING'
3788 AND rti.quantity > 0
3789 AND rti.po_header_id = p_po_header_id
3790 AND rti.po_release_id (+) = p_po_release_id
3791 AND rti.po_line_location_id =l_po_change_requests(j).document_line_location_id;
3792 ELSE
3793 SELECT Count(*)
3794 INTO l_count_asn
3795 FROM rcv_transactions_interface rti
3796 WHERE rti.processing_status_code = 'PENDING'
3797 AND rti.quantity > 0
3798 AND rti.po_header_id = p_po_header_id
3799 AND rti.po_line_location_id =l_po_change_requests(j).document_line_location_id;
3800 END IF;
3801
3802 IF( l_count_asn > 0 ) THEN
3803 l_err_count := l_err_count + 1;
3804 x_pos_errors.message_name.extend;
3805 x_pos_errors.text_line.extend;
3806 x_pos_errors.Message_name(l_err_count) := NULL;
3807 IF l_po_change_requests(j).document_line_location_id IS NOT NULL
3808 THEN
3809 fnd_message.Set_name('POS', 'POS_CHG_PO_UNPRC_TRX');
3810 fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3811 fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3812 x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3813 END IF;
3814 END IF;
3815
3816 IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3817 SELECT Count(*)
3818 INTO l_count_asn
3819 FROM rcv_shipment_lines RSL
3820 WHERE RSL.po_header_id = p_po_header_id
3821 AND RSL.po_release_id (+) = p_po_release_id
3822 AND RSL.po_line_location_id =l_po_change_requests(j).document_line_location_id
3823 AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
3824 AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
3825 AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
3826 ELSE
3827 SELECT Count(*)
3828 INTO l_count_asn
3829 FROM rcv_shipment_lines RSL
3830 WHERE RSL.po_header_id = p_po_header_id
3831 AND RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id
3832 AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
3833 AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
3834 AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
3835 END IF;
3836
3837 IF( l_count_asn > 0 ) THEN
3838 l_err_count := l_err_count + 1;
3839 x_pos_errors.message_name.extend;
3840 x_pos_errors.text_line.extend;
3841 x_pos_errors.Message_name(l_err_count) := NULL;
3842 IF L_po_change_requests(j).document_line_location_id IS NOT NULL
3843 THEN
3844 fnd_message.Set_name('POS', 'POS_CHG_PO_OPEN_ASN');
3845 fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3846 fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3847 x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3848 END IF;
3849 END IF;
3850 END IF;
3851
3852 IF ( l_po_change_requests(j).request_level = 'LINE' AND l_po_change_requests(j).action_type = 'MODIFICATION' ) THEN
3853 FOR ship_rec IN ship_cur(l_po_change_requests(j).document_line_id)
3854 LOOP
3855 IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3856 SELECT Count(*)
3857 INTO l_count_asn
3858 FROM rcv_transactions_interface rti
3859 WHERE rti.processing_status_code = 'PENDING'
3860 AND rti.quantity > 0
3861 AND rti.po_header_id = p_po_header_id
3862 AND rti.po_release_id (+) = p_po_release_id
3863 AND rti.po_line_location_id = ship_rec.line_location_id;
3864 ELSE
3865 SELECT Count(*)
3866 INTO l_count_asn
3867 FROM rcv_transactions_interface rti
3868 WHERE rti.processing_status_code = 'PENDING'
3869 AND rti.quantity > 0
3870 AND rti.po_header_id = p_po_header_id
3871 AND rti.po_line_location_id = ship_rec.line_location_id;
3872 END IF;
3873
3874 IF( l_count_asn > 0 ) THEN
3875 l_err_count := l_err_count + 1;
3876 x_pos_errors.message_name.extend;
3877 x_pos_errors.text_line.extend;
3878 x_pos_errors.Message_name(l_err_count) := NULL;
3879
3880 IF l_po_change_requests(j).document_line_id IS NOT NULL
3881 THEN
3882 fnd_message.Set_name('POS', 'POS_CHG_LINE_UNPRC_TRX');
3883 fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3884 fnd_message.Set_token('SHIPMENT',ship_rec.shipment_num);
3885 x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3886 END IF;
3887 END IF;
3888
3889 IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3890 SELECT Count(*)
3891 INTO l_count_asn
3892 FROM rcv_shipment_lines RSL
3893 WHERE RSL.po_header_id = p_po_header_id
3894 AND RSL.po_release_id (+) = p_po_release_id
3895 AND RSL.po_line_location_id = ship_rec.line_location_id
3896 AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
3897 AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
3898 AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
3899 ELSE
3900 SELECT Count(*)
3901 INTO l_count_asn
3902 FROM rcv_shipment_lines RSL
3903 WHERE RSL.po_header_id = p_po_header_id
3904 AND RSL.po_line_location_id = ship_rec.line_location_id
3905 AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
3906 AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
3907 AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED' ;
3908 END IF;
3909
3910 IF( l_count_asn > 0 ) THEN
3911 l_err_count := l_err_count + 1;
3912 x_pos_errors.message_name.extend;
3913 x_pos_errors.text_line.extend;
3914 x_pos_errors.Message_name(l_err_count) := NULL;
3915 IF l_po_change_requests(j).document_line_id IS NOT NULL
3916 THEN
3917 fnd_message.Set_name('POS', 'POS_CHG_LINE_OPEN_ASN');
3918 fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3919 fnd_message.Set_token('SHIPMENT',ship_rec.shipment_num);
3920 x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3921 END IF;
3922 END IF;
3923 END LOOP;
3924 END IF;
3925 END LOOP;
3926
3927 IF( l_err_count < 1 ) THEN
3928 x_ret_sts := 'N';
3929 ELSE
3930 x_ret_sts := 'Y';
3931 END IF;
3932 EXCEPTION
3933 WHEN OTHERS THEN RAISE;
3934 x_ret_sts := fnd_api.g_ret_sts_unexp_error;
3935 IF fnd_msg_pub.Check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3936 THEN
3937 fnd_msg_pub.Add_exc_msg(g_pkg_name, l_api_name);
3938 IF g_fnd_debug = 'Y' THEN
3939 IF ( fnd_log.g_current_runtime_level <=fnd_log.level_unexpected ) THEN
3940 fnd_log.String(fnd_log.level_unexpected, g_module_prefix|| l_api_name||'.others_exception', SQLCODE);
3941 END IF;
3942 END IF;
3943 END IF;
3944 END IS_ASN_EXIST;
3945
3946 END PO_CHG_REQUEST_PVT;