[Home] [Help]
PACKAGE BODY: APPS.PO_CHG_REQUEST_PVT
Source
1 PACKAGE BODY PO_CHG_REQUEST_PVT AS
2 /* $Header: POXPCHGB.pls 120.28.12010000.2 2008/08/04 08:31:04 rramasam 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 * Public Procedure: save_request
439 * Requires: API message list has been initialized if p_init_msg_list is false.
440 * Modifies: API message list
441 * Effects: Saves Data to the Change Request Table
442 * Returns:
443 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
444 * FND_API.G_RET_STS_ERROR if an error occurs
445 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
446 */
447
448 procedure save_request(
449 p_api_version IN NUMBER,
450 p_Init_Msg_List IN VARCHAR2,
451 x_return_status OUT NOCOPY VARCHAR2,
452 p_po_header_id IN NUMBER,
453 p_po_release_id IN NUMBER,
454 p_revision_num IN NUMBER,
455 p_po_change_requests IN pos_chg_rec_tbl,
456 x_request_group_id OUT NOCOPY NUMBER,
457 p_chn_int_cont_num IN varchar2 default null,
458 p_chn_source IN varchar2 default null,
459 p_chn_requestor_username in varchar2 default null,
460 p_user_id IN number default null,
461 p_login_id IN number default null) IS
462
463 rec_cnt number;
464 p_chg_request_grp_id number;
465 x_return_code varchar2(40);
466 v_request_group_id number;
467 accp_flag char(1);
468 v_buyer_id number;
469 v_document_type po_change_requests.DOCUMENT_TYPE%TYPE;
470 l_user_id NUMBER := fnd_global.user_id;
471 l_login_id NUMBER := fnd_global.login_id;
472 l_api_name CONSTANT VARCHAR2(30) := 'save_request';
473 l_api_version_number CONSTANT NUMBER := 1.0;
474
475 BEGIN
476 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
477 -- initialize message list
478 FND_MSG_PUB.initialize;
479 END IF;
480
481 -- Standard call to check for call compatibility.
482
483 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
484 p_api_version,
485 l_api_name,
486 G_PKG_NAME)
487 THEN
488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
489 END IF;
490
491 -- initialize return status
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493
494 if (p_user_id is not null) then
495 l_user_id := p_user_id;
496 l_login_id := p_login_id;
497 end if;
498
499 rec_cnt := p_po_change_requests.count();
500 -- get the ChangerequestGroupID only when changes are requested
501 IF( rec_cnt > 0) THEN
502 -- Get the document type of the first record to get the unique request group id
503 if (p_po_release_id is not null) then
504 v_document_type := 'RELEASE';
505 else
506 v_document_type := 'PO';
507 end if;
508
509 v_request_group_id := getRequestGroupId(p_po_header_id,p_po_release_id,v_document_type);
510 IF(v_request_group_id is null) THEN
511 select po_chg_request_seq.nextval
512 into v_request_group_id
513 from dual;
514 END IF;
515 x_request_group_id := v_request_group_id;
516 END IF;
517
518 FOR i in 1..rec_cnt LOOP
519 if(p_po_change_requests(i).action_type not in ('ACCEPT','REJECT')) then
520
521 insert into po_change_requests(
522 change_request_group_id, change_request_id,
523 initiator, action_type, request_reason,
524 request_level, request_status, document_type,
525 document_header_id, document_num,
526 document_revision_num, po_release_id,
527 created_by, creation_date,last_updated_by,last_update_date,
528 last_update_login,document_line_id, document_line_number,
529 document_line_location_id, document_shipment_number,
530 parent_line_location_id, document_distribution_id,
531 document_distribution_number,
532 old_quantity, new_quantity,
533 old_promised_date, new_promised_date,
534 old_supplier_part_number, new_supplier_part_number,
535 old_price, new_price, old_need_by_date, new_need_by_date,
536 old_supplier_reference_number, new_supplier_reference_number,
537 Approval_Required_Flag,Parent_Change_request_Id,
538 Requester_Id ,
539 OLD_SUPPLIER_ORDER_NUMBER , NEW_SUPPLIER_ORDER_NUMBER,
540 OLD_SUPPLIER_ORDER_LINE_NUMBER , NEW_SUPPLIER_ORDER_LINE_NUMBER,
541 change_active_flag, MSG_CONT_NUM, REQUEST_ORIGIN,ADDITIONAL_CHANGES,
542 OLD_START_DATE,NEW_START_DATE,OLD_EXPIRATION_DATE,NEW_EXPIRATION_DATE,
543 OLD_AMOUNT,NEW_AMOUNT,
544 SUPPLIER_DOC_REF, SUPPLIER_LINE_REF, SUPPLIER_SHIPMENT_REF, --added in FPJ for splits.
545 NEW_PROGRESS_TYPE,NEW_PAY_DESCRIPTION --<< Complex work changes for R12 >>
546
547 )
548 values (x_request_group_id,po_chg_request_seq.nextval,
549 p_po_change_requests(i).initiator,
550 p_po_change_requests(i).action_type,
551 p_po_change_requests(i).request_reason,
552 p_po_change_requests(i).request_level,
553 p_po_change_requests(i).request_status,
554 p_po_change_requests(i).document_type,
555 p_po_change_requests(i).document_header_id,
556 p_po_change_requests(i).document_num,
557 to_number(p_po_change_requests(i).document_revision_num),
558 p_po_change_requests(i).po_release_id,
559 l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560 p_po_change_requests(i).document_line_id,
561 p_po_change_requests(i).document_line_number,
562 p_po_change_requests(i).document_line_location_id,
563 p_po_change_requests(i).document_shipment_number,
564 p_po_change_requests(i).parent_line_location_id,
565 p_po_change_requests(i).document_distribution_id,
566 p_po_change_requests(i).document_distribution_number,
567 p_po_change_requests(i).old_quantity,
568 p_po_change_requests(i).new_quantity,
569 p_po_change_requests(i).old_promised_date,
570 p_po_change_requests(i).new_promised_date,
571 p_po_change_requests(i).old_supplier_part_number,
572 p_po_change_requests(i).new_supplier_part_number,
573 p_po_change_requests(i).old_price,
574 p_po_change_requests(i).new_price,
575 p_po_change_requests(i).old_need_by_date,
576 p_po_change_requests(i).new_need_by_date,
577 p_po_change_requests(i).old_supplier_reference_number,
578 p_po_change_requests(i).new_supplier_reference_number,
579 p_po_change_requests(i).Approval_Required_Flag,
580 p_po_change_requests(i).Parent_Change_request_Id,
581 p_po_change_requests(i).Requester_id,
582 p_po_change_requests(i).Old_Supplier_Order_Number,
583 p_po_change_requests(i).New_Supplier_Order_Number,
584 p_po_change_requests(i).Old_Supplier_Order_Line_Number,
585 p_po_change_requests(i).New_Supplier_Order_Line_Number,
586 decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
587 p_chn_int_cont_num,
588 p_chn_source,
589 p_po_change_requests(i).Additional_changes,
590 p_po_change_requests(i).old_start_date,
591 p_po_change_requests(i).new_start_date,
592 p_po_change_requests(i).old_expiration_date,
593 p_po_change_requests(i).new_expiration_date,
594 p_po_change_requests(i).old_amount,
595 p_po_change_requests(i).new_amount,
596 p_po_change_requests(i).SUPPLIER_DOC_REF,
597 p_po_change_requests(i).SUPPLIER_LINE_REF,
598 p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
599 p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
600 p_po_change_requests(i).NEW_PAY_DESCRIPTION
601
602 );
603 end if;
604 end loop;
605
606 EXCEPTION
607 WHEN FND_API.g_exc_error THEN
608 x_return_status := FND_API.g_ret_sts_error;
609 WHEN FND_API.g_exc_unexpected_error THEN
610 x_return_status := FND_API.g_ret_sts_unexp_error;
611 WHEN OTHERS THEN
612 x_return_status := FND_API.g_ret_sts_unexp_error;
613
614 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
615 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
616 IF g_fnd_debug = 'Y' THEN
617 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
618 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
619 l_api_name || '.others exception' ,sqlcode);
620 END IF;
621 END IF;
622 END IF;
623
624 END save_request;
625
626 /**
627 * Public Procedure: process_supplier_request
628 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,POS_CHG_REC_TBL
629 * Modifies:
630 * Effects: Processes the change Request and calls PO Doc Submission Check
631 * Returns:
632 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
633 * FND_API.G_RET_STS_ERROR if an error occurs
634 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
635 * POS_ERR_TYPE
636 */
637
638 procedure process_supplier_request (
639 p_po_header_id IN number,
640 p_po_release_id IN number,
641 p_revision_num IN number,
642 p_po_change_requests IN pos_chg_rec_tbl,
643 x_online_report_id OUT NOCOPY number,
644 x_pos_errors OUT NOCOPY POS_ERR_TYPE,
645 p_chn_int_cont_num IN varchar2 default null,
646 p_chn_source IN varchar2 default null,
647 p_chn_requestor_username in varchar2 default null,
648 p_user_id IN number default null,
649 p_login_id IN number default null,
650 p_last_upd_date IN date default null,
651 p_mpoc IN varchar2 default FND_API.G_FALSE) IS
652
653 x_error_code varchar2(40);
654 no_rec_found exception;
655 v_auth_status PO_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
656 x_progress varchar2(3) := '000';
657 l_api_version_number CONSTANT NUMBER := 1.0;
658 l_api_name CONSTANT VARCHAR2(30) := 'process_supplier_request';
659 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
660 l_msg_count NUMBER;
661 l_msg_data VARCHAR2(2000);
662 l_commit VARCHAR2(1) := FND_API.G_FALSE;
663 l_user_id NUMBER;
664 l_login_id NUMBER;
665 l_request_group_id NUMBER :=null;
666 x_return_status varchar2(20);
667 updatePoAttr boolean := false;
668 saveRequest boolean := false;
669 callWf boolean := false;
670 l_po_change_requests pos_chg_rec_tbl := NULL;
671 vAckTbl pos_ack_rec_tbl := pos_ack_rec_tbl();
672 ack_cnt number :=0;
673 callDocCheck boolean :=false;
674 accp_flag char(1);
675 v_buyer_id number;
676 x_accp_flag po_headers_all.acceptance_required_flag%type;
677 l_err_msg_name_tbl po_tbl_varchar30;
678 l_err_msg_text_tbl po_tbl_varchar2000;
679 l_last_upd_date po_headers_all.last_update_date%type;
680 l_count_asn NUMBER;
681 l_ret_sts varchar2(1);
682
683
684 CURSOR PO_CSR(p_po_header_id in number) IS
685 SELECT last_update_date
686 FROM PO_HEADERS_ALL
687 WHERE PO_HEADER_ID = p_po_header_id
688 FOR UPDATE of last_update_date NOWAIT;
689
690 poRec PO_CSR%ROWTYPE;
691
692 CURSOR REL_CSR(p_po_release_id in number) IS
693 SELECT last_update_date
694 FROM PO_RELEASES_ALL
695 WHERE PO_RELEASE_ID = p_po_release_id
696 FOR UPDATE of last_update_date NOWAIT;
697
698 relRec REL_CSR%ROWTYPE;
699
700 BEGIN
701 -- initialize return status
702 x_return_status := FND_API.G_RET_STS_SUCCESS;
703 x_progress := '001';
704
705
706 -- Lock the PO Header Row for update of Last Update Date
707 if (p_po_release_id is not null ) then
708 BEGIN
709 OPEN REL_CSR(p_po_release_id);
710 FETCH REL_CSR INTO relRec;
711 l_last_upd_date := relRec.last_update_date;
712 if (REL_CSR%NOTFOUND) then
713 CLOSE REL_CSR;
714 IF (g_fnd_debug = 'Y') THEN
715 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
716 FND_LOG.string(FND_LOG.level_error, g_module_prefix || 'process_supplier_request ', ' Record dosent exist for po_release_id = ' || p_po_release_id);
717 END IF;
718 END IF;
719 end if;
720 CLOSE REL_CSR;
721 EXCEPTION
722 WHEN OTHERS THEN
723 if (sqlcode = '-54') then
724 l_err_msg_name_tbl := po_tbl_varchar30();
725 l_err_msg_text_tbl := po_tbl_varchar2000();
726 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
727 x_pos_errors.message_name.extend;
728 x_pos_errors.text_line.extend;
729 x_pos_errors.message_name(1) := null;
730 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_LOCKED_PO_ROW');
731 return;
732 end if;
733 END;
734 else
735 BEGIN
736 OPEN PO_CSR(p_po_header_id);
737 FETCH PO_CSR INTO poRec;
738 l_last_upd_date := poRec.last_update_date;
739 if (PO_CSR%NOTFOUND) then
740 CLOSE PO_CSR;
741 IF (g_fnd_debug = 'Y') THEN
742 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
743 FND_LOG.string(FND_LOG.level_error, g_module_prefix || 'process_supplier_request', 'Record dosent exist for po_header_id = ' || p_po_header_id);
744 END IF;
745 END IF;
746 end if;
747 CLOSE PO_CSR;
748 EXCEPTION
749 WHEN OTHERS THEN
750 if (sqlcode = '-54') then
751 l_err_msg_name_tbl := po_tbl_varchar30();
752 l_err_msg_text_tbl := po_tbl_varchar2000();
753 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
754 x_pos_errors.message_name.extend;
755 x_pos_errors.text_line.extend;
756 x_pos_errors.message_name(1) := null;
757 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_LOCKED_PO_ROW');
758
759 return;
760 end if;
761 END;
762 end if;
763
764 -- Check if the same record is being update
765 -- Check against last_updated_date to make sure that
766 -- The record that was queried is being updated
767
768 if (p_last_upd_date <> l_last_upd_date) then
769 l_err_msg_name_tbl := po_tbl_varchar30();
770 l_err_msg_text_tbl := po_tbl_varchar2000();
771 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
772 x_pos_errors.message_name.extend;
773 x_pos_errors.text_line.extend;
774 x_pos_errors.message_name(1) := null;
775 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_MODIFIED_PO_ROW');
776 return;
777 end if;
778 -- Copy the request into a local var
779 l_po_change_requests := p_po_change_requests;
780
781 IF g_fnd_debug = 'Y' THEN
782 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
783 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
784 '.invoked', 'Type: ' ||
785 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
786 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
787 END IF;
788 END IF;
789
790 validate_shipment_cancel (
791 p_po_header_id,
792 p_po_change_requests,
793 x_pos_errors,
794 l_ret_sts);
795 if(l_ret_sts = 'Y') then
796 return;
797 end if;
798
799 if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
800 l_po_change_requests(1).request_level='HEADER' ) then
801 if (l_po_change_requests.count > 1 ) then
802 x_return_status := FND_API.g_ret_sts_unexp_error;
803
804 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
805 IF g_fnd_debug = 'Y' THEN
806 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
807 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
808 l_api_name || fnd_message.get_string('PO', 'POS_MULT_HDR_CANCEL_REQ'), sqlcode);
809 END IF;
810 END IF;
811
812
813
814 l_err_msg_name_tbl := po_tbl_varchar30();
815 l_err_msg_text_tbl := po_tbl_varchar2000();
816 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
817
818
819
820 x_pos_errors.message_name.extend;
821 x_pos_errors.text_line.extend;
822 x_pos_errors.message_name(1) := null;
823 /*
824 POS_MULT_HDR_CANCEL_REQ = 'Multiple requests are made in context with Header level cancel.'
825 */
826
827 x_pos_errors.text_line(1) := fnd_message.get_string('PO', 'POS_MULT_HDR_CANCEL_REQ');
828
829 return;
830 end if;
831 save_cancel_request(
832 p_api_version => 1.0 ,
833 p_Init_Msg_List => FND_API.G_FALSE,
834 x_return_status => l_return_status,
835 p_po_header_id => p_po_header_id,
836 p_po_release_id => p_po_release_id,
837 p_revision_num => p_revision_num,
838 p_po_change_requests => l_po_change_requests,
839 x_request_group_id => l_request_group_id
840 );
841 x_online_report_id := 0;
842 if (l_return_status <> FND_API.g_ret_sts_success) then
843 l_err_msg_name_tbl := po_tbl_varchar30();
844 l_err_msg_text_tbl := po_tbl_varchar2000();
845 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
846
847
848 x_pos_errors.message_name.extend;
849 x_pos_errors.text_line.extend;
850 x_pos_errors.message_name(1) := null;
851 /*
852 POS_SAVE_CANCEL_REQ_ERR = 'Error while saving the cancel request: '
853 */
854
855 x_pos_errors.text_line(1) :=
856 fnd_message.get_string('PO', 'POS_SAVE_CANCEL_REQ_ERR') ||
857 FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F');
858
859
860 end if;
861
862 return;
863
864 end if;
865
866 if p_po_release_id is null then
867 select agent_id,nvl(acceptance_required_flag,'N')
868 into v_buyer_id,x_accp_flag
869 from po_headers_all
870 where po_header_id = p_po_header_id;
871 else
872 select agent_id,nvl(acceptance_required_flag,'N')
873 into v_buyer_id,x_accp_flag
874 from po_releases_all
875 where po_release_id = p_po_release_id;
876 end if;
877
878 FOR i in 1..l_po_change_requests.count()
879 LOOP
880 if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
881 l_po_change_requests(i).request_level='SHIPMENT' ) then
882 callWf := true;
883 --updatePoAttr := false;
884 --callDocCheck := false;
885
886 if (l_po_change_requests(i).action_type = 'ACCEPT') then
887 accp_flag := 'Y';
888 else
889 accp_flag := 'N';
890 end if;
891
892 -- Process The Acknowledgements
893
894 PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
895 1.0,FND_API.G_FALSE,x_return_status,
896 l_po_change_requests(i).document_line_location_id,
897 l_po_change_requests(i).document_header_id,
898 l_po_change_requests(i).po_release_id,
899 l_po_change_requests(i).document_revision_num,
900 accp_flag,
901 l_po_change_requests(i).request_reason,
902 v_buyer_id, fnd_global.user_id);
903
904 elsif (l_po_change_requests(i).request_level='LINE' AND
905 l_po_change_requests(i).action_type='MODIFICATION') then
906 callWf := true;
907 callDocCheck := true;
908 updatePoAttr := true;
909 saveRequest := true;
910 elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
911 l_po_change_requests(i).action_type='CANCELLATION') then
912 -- Do not call doc sub check for shipment cancellation
913 saveRequest := true;
914 callWf := true;
915 --callDocCheck := false;
916 updatePoAttr := true;
917
918 elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
919 l_po_change_requests(i).action_type='MODIFICATION') then
920 -- If quantity,promised_date,price,Amount have not changed in the shipment level do not update po
921 /*Bug 7112734 - Start
922 During PO change process if Supplier Order Line number alone is changed then it will
923 be treated as acceptance of the PO.
924 */
925 if (l_po_change_requests(i).new_quantity is null AND
926 l_po_change_requests(i).new_promised_date is null AND
927 l_po_change_requests(i).new_price is null AND
928 l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
929 --callDocCheck := false;
930 --updatePoAttr := false;
931 l_po_change_requests(i).request_status := 'ACCEPTED';
932
933 -- If PO requires acknowledgement, post shipment-level acceptance.
934 IF (x_accp_flag = 'Y') THEN
935
936 callWf := true;
937 -- Process The Acknowledgements
938 PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
939 1.0,FND_API.G_FALSE,x_return_status,
940 l_po_change_requests(i).document_line_location_id,
941 l_po_change_requests(i).document_header_id,
942 l_po_change_requests(i).po_release_id,
943 l_po_change_requests(i).document_revision_num,
944 'Y',
945 l_po_change_requests(i).request_reason,
946 v_buyer_id, fnd_global.user_id);
947
948 END IF;
949
950 else
951 -- if othere parameters are updated with so then update po
952 callWf := true;
953 callDocCheck := true;
954 updatePoAttr := true;
955 saveRequest := true;
956 -- Bug 7112734 - End
957 end if; -- if only so has changed
958
959 if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
960 begin
961 update po_line_locations_all
962 set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
963 where line_location_id = l_po_change_requests(i).document_line_location_id;
964 exception
965 WHEN OTHERS THEN
966 x_return_status := FND_API.g_ret_sts_unexp_error;
967 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
968 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
969 IF g_fnd_debug = 'Y' THEN
970 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
971 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
972 l_api_name || '.others_exception', sqlcode);
973 END IF;
974 END IF;
975 END IF;
976 end; end if;
977 elsif (l_po_change_requests(i).request_level='HEADER'
978 AND l_po_change_requests(i).action_type='MODIFICATION') then
979 saveRequest := true;
980
981 if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
982
983 if (p_po_release_id is null ) then
984 -- Update the vendor_order_num for PO Headers no need of approval.
985 update po_headers_all
986 set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
987 where po_header_id = p_po_header_id;
988 else
989 -- Update the vendor_order_num for PO Releases no need of approval.
990 update po_releases_all
991 set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
992 where po_release_id = p_po_release_id;
993 end if;
994 end if;
995
996 -- Set startWf to false
997 if (l_po_change_requests(i).Additional_changes is not null) then
998 callWf := true;
999 updatePoAttr := true;
1000 end if;
1001 --callDocCheck := false;
1002
1003 end if; -- end if accept reject
1004 END LOOP;
1005 if ((callDocCheck) AND l_po_change_requests.count() > 0 ) then
1006 IF g_fnd_debug = 'Y' THEN
1007 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1008 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1009 '.invoked', ' Change Count : ' || TO_CHAR(l_po_change_requests.count()));
1010 END IF;
1011 END IF;
1012 validate_change_request (
1013 p_api_version => 1.0,
1014 p_init_msg_list => FND_API.G_FALSE,
1015 x_return_status => x_return_status,
1016 x_msg_data => l_msg_data,
1017 p_po_header_id => p_po_header_id,
1018 p_po_release_id => p_po_release_id,
1019 p_revision_num => p_revision_num,
1020 p_po_change_requests => l_po_change_requests,
1021 x_online_report_id => x_online_report_id,
1022 x_pos_errors => x_pos_errors);
1023
1024 end if;
1025
1026 if (l_po_change_requests.count() > 0 AND x_return_status = FND_API.G_RET_STS_SUCCESS) then
1027 IF g_fnd_debug = 'Y' THEN
1028 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1029 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1030 '.invoked', 'Type: ' ||
1031 ', Save Count : ' || TO_CHAR(l_po_change_requests.count()));
1032 END IF;
1033 END IF;
1034
1035
1036 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1037
1038 if (saveRequest) then
1039
1040 save_request(
1041 p_api_version => 1.0,
1042 p_init_msg_list => FND_API.G_FALSE,
1043 x_return_status => x_return_status,
1044 p_po_header_id => p_po_header_id,
1045 p_po_release_id => p_po_release_id,
1046 p_revision_num => p_revision_num,
1047 p_po_change_requests => l_po_change_requests,
1048 x_request_group_id => l_request_group_id,
1049 p_chn_int_cont_num => p_chn_int_cont_num,
1050 p_chn_source => p_chn_source,
1051 p_chn_requestor_username => p_chn_requestor_username,
1052 p_user_id => p_user_id,
1053 p_login_id => p_login_id);
1054 end if;
1055
1056 if(updatePoAttr) then
1057 IF g_fnd_debug = 'Y' THEN
1058 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1059 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1060 '.invoked', 'Update PO ' ||
1061 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
1062 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
1063 END IF;
1064 END IF;
1065 update_po_attributes(p_po_header_id,p_po_release_id,p_revision_num,
1066 l_request_group_id, x_return_status, p_chn_requestor_username,
1067 p_user_id,
1068 p_login_id);
1069 end if;
1070
1071
1072 /* Bug 3534807, mji
1073 Check if all shipments has been acknowledged, if yes post header
1074 acknowledgement record.
1075 */
1076 PO_ACKNOWLEDGE_PO_GRP.Set_Header_Acknowledgement (
1077 1.0,
1078 FND_API.G_FALSE,
1079 x_return_status,
1080 p_po_header_id,
1081 p_po_release_id );
1082
1083
1084 if (callWf) then
1085 IF g_fnd_debug = 'Y' THEN
1086 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1087 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1088 '.invoked', 'Call Workflow ' || ', request group id ' || to_char(l_request_group_id));
1089 END IF;
1090 END IF;
1091 --start multiple po change
1092 if (p_mpoc = FND_API.G_TRUE) then
1093 if(x_accp_flag = 'Y') then
1094 x_return_status := po_acknowledge_po_grp.all_shipments_responded (
1095 1.0,FND_API.G_FALSE,p_po_header_id , p_po_release_id, p_revision_num );
1096 else
1097 x_return_status := FND_API.G_TRUE;
1098 end if;
1099
1100 if(x_return_status = FND_API.G_TRUE) then
1101 IF g_fnd_debug = 'Y' THEN
1102 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1103 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1104 '.invoked', ' All shipments acked/changed ' );
1105 END IF;
1106 END IF;
1107 else
1108 l_err_msg_name_tbl := po_tbl_varchar30();
1109 l_err_msg_text_tbl := po_tbl_varchar2000();
1110 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
1111 x_pos_errors.message_name.extend;
1112 x_pos_errors.text_line.extend;
1113 x_pos_errors.message_name(1) := null;
1114 x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_PO_ALL_NOT_RESPND');
1115 end if;
1116 end if;
1117 --end mupltiple po change change
1118
1119 x_return_status := startSupplierWF( p_po_header_id,p_po_release_id,
1120 p_revision_num, l_request_group_id, x_accp_flag);
1121 IF g_fnd_debug = 'Y' THEN
1122 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1123 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1124 '.invoked', 'Call Workflow ' || ', Return Status ' || x_return_status);
1125 END IF;
1126 END IF;
1127 end if;
1128
1129 end if; --if docCheck returns FND_API.G_RET_STS_SUCCESS
1130
1131 end if;
1132 -- Update the PO Headers/Releases even if the changes dosent require doc Check
1133 if (callDocCheck=false) then
1134 if (p_user_id is null or p_login_id is null) then
1135 l_user_id := fnd_global.user_id;
1136 l_login_id := fnd_global.login_id;
1137 else
1138 l_user_id := p_user_id;
1139 l_login_id := p_login_id;
1140 end if;
1141 -- Update the last update date if po dosent require to be updated
1142 if (p_po_release_id is not null) then
1143 update po_releases_all set
1144 last_update_date = sysdate,
1145 last_updated_by = l_user_id,
1146 last_update_login = l_login_id,
1147 request_id = fnd_global.conc_request_id,
1148 program_application_id = fnd_global.prog_appl_id,
1149 program_id = fnd_global.conc_program_id,
1150 program_update_date = sysdate
1151 where po_release_id = p_po_release_id;
1152 else
1153 update po_headers_all set
1154 last_update_date = sysdate,
1155 last_updated_by = l_user_id,
1156 last_update_login = l_login_id,
1157 request_id = fnd_global.conc_request_id,
1158 program_application_id = fnd_global.prog_appl_id,
1159 program_id = fnd_global.conc_program_id,
1160 program_update_date = sysdate
1161 where po_header_id = p_po_header_id;
1162 end if;
1163 end if; -- if call doc check is false
1164 EXCEPTION
1165 WHEN OTHERS THEN
1166 x_return_status := FND_API.g_ret_sts_unexp_error;
1167 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1168 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1169 IF g_fnd_debug = 'Y' THEN
1170 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1171 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1172 l_api_name || '.others_exception', sqlcode);
1173 END IF;
1174 END IF;
1175 END IF;
1176 l_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1177 p_encoded => 'F');
1178 END process_supplier_request;
1179 /**
1180 * Private Procedure: update_po_attributes
1181 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,REQUEST_GROUP_ID
1182 * Modifies:
1183 * Effects: Updates The PO_HEADERS_ALL, PO_RELEASES_ALL
1184 * Returns:
1185 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
1186 * FND_API.G_RET_STS_ERROR if an error occurs
1187 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1188 */
1189
1190 procedure update_po_attributes
1191 (p_po_header_id IN number,
1192 p_po_release_id IN number,
1193 p_revision_num IN number,
1194 p_chg_request_grp_id IN number,
1195 x_return_status OUT NOCOPY varchar2,
1196 p_chn_requestor_username in varchar2 default null,
1197 p_user_id IN number default null,
1198 p_login_id IN number default null) is
1199
1200 l_api_name CONSTANT VARCHAR2(30) := 'update_po_attributes';
1201 l_user_id number;
1202 l_login_id number;
1203
1204 BEGIN
1205
1206 x_return_status := FND_API.G_RET_STS_SUCCESS;
1207 if (p_user_id is null or p_login_id is null) then
1208 l_user_id := fnd_global.user_id;
1209 l_login_id := fnd_global.login_id;
1210 else
1211 l_user_id := p_user_id;
1212 l_login_id := p_login_id;
1213 end if;
1214
1215 if p_po_release_id is null then
1216 update po_headers_all set
1217 authorization_status = 'IN PROCESS',
1218 CHANGE_REQUESTED_BY = 'SUPPLIER',
1219 last_update_date = sysdate,
1220 last_updated_by = l_user_id,
1221 last_update_login = l_login_id,
1222 request_id = fnd_global.conc_request_id,
1223 program_application_id = fnd_global.prog_appl_id,
1224 program_id = fnd_global.conc_program_id,
1225 program_update_date = sysdate
1226 where po_header_id = p_po_header_id;
1227
1228 -- Update the approved_flag to R for all the shipments that has been changed
1229 -- do not update the flag for cancellation requests at shipments
1230 -- That was the earlier comment now we are going to chnage the
1231 -- Approved flag for cancellation records as asked by DBI team
1232 -- bug 4306375
1233 -- jai
1234
1235 update po_line_locations_all
1236 set approved_flag = 'R'
1237 where line_location_id in (select document_line_location_id
1238 from po_change_requests
1239 where request_level = 'SHIPMENT' and
1240 document_header_id = p_po_header_id and
1241 action_type in ('MODIFICATION','CANCELLATION') and
1242 initiator = 'SUPPLIER' and
1243 request_status ='PENDING') and
1244 approved_flag='Y';
1245
1246 -- Update all the shipments for which line price has been changed to prevent receiving
1247 -- do not update the line locations for cancellation request
1248 -- That was the earlier comment now we are going to chnage the
1249 -- Approved flag for cancellation records as asked by DBI team
1250 -- bug 4306375
1251 -- jai
1252
1253 update po_line_locations_all
1254 set approved_flag = 'R'
1255 where po_header_id in (select document_header_id
1256 from po_change_requests
1257 where request_level = 'HEADER' and
1258 document_header_id = p_po_header_id and
1259 action_type ='CANCELLATION' and
1260 initiator = 'SUPPLIER' and
1261 request_status ='PENDING') and
1262 approved_flag='Y';
1263
1264 update po_line_locations_all
1265 set approved_flag = 'R'
1266 where po_line_id in (select document_line_id
1267 from po_change_requests
1268 where request_level = 'LINE' and
1269 document_header_id = p_po_header_id and
1270 request_status = 'PENDING' and
1271 initiator = 'SUPPLIER' and
1272 action_type = 'MODIFICATION' and
1273 new_price is not null) and
1274 approved_flag='Y' ;
1275 /* jai
1276 and
1277 line_location_id not in (select document_line_location_id
1278 from po_change_requests
1279 where request_level = 'SHIPMENT' and
1280 document_header_id = p_po_header_id and
1281 action_type = 'CANCELLATION' and
1282 initiator = 'SUPPLIER' and
1283 request_status ='PENDING') ;
1284 */
1285 else
1286 -- For Releases
1287 update po_releases_all set
1288 authorization_status = 'IN PROCESS',
1289 CHANGE_REQUESTED_BY = 'SUPPLIER',
1290 revised_date = sysdate,
1291 last_update_date = sysdate,
1292 last_updated_by = l_user_id,
1293 last_update_login = l_login_id,
1294 request_id = fnd_global.conc_request_id,
1295 program_application_id = fnd_global.prog_appl_id,
1296 program_id = fnd_global.conc_program_id,
1297 program_update_date = sysdate
1298 where po_release_id = p_po_release_id;
1299
1300 -- Now Update the approved_flag to R for all the shipments that has been
1301 -- changed to prevent receiving
1302 update po_line_locations_all
1303 set approved_flag = 'R'
1304 where line_location_id in (select document_line_location_id
1305 from po_change_requests
1306 where request_level = 'SHIPMENT' and
1307 po_release_id = p_po_release_id and
1308 action_type in ('MODIFICATION','CANCELLATION') and
1309 initiator = 'SUPPLIER' and
1310 request_status = 'PENDING') and
1311 approved_flag='Y';
1312
1313 --New DBI request
1314 update po_line_locations_all
1315 set approved_flag = 'R'
1316 where po_release_id in (select po_release_id
1317 from po_change_requests
1318 where request_level = 'HEADER' and
1319 po_release_id = p_po_release_id and
1320 action_type ='CANCELLATION' and
1321 initiator = 'SUPPLIER' and
1322 request_status ='PENDING') and
1323 approved_flag='Y';
1324 end if;
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 x_return_status := FND_API.g_ret_sts_unexp_error;
1328 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1329 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1330 IF g_fnd_debug = 'Y' THEN
1331 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1332 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1333 l_api_name || '.others_exception', sqlcode);
1334 END IF;
1335 END IF;
1336 END IF;
1337 END update_po_attributes;
1338
1339 /**
1340 * Public Procedure: validate_change_request
1341 * Requires: PO_HEADER_ID,PO_RELEASE_ID,REVISION_NUM,POS_CHG_REC_TBL
1342 * Modifies:
1343 * Effects: Converts the Supplier Change Request To PO Change Request
1344 * Calls Doc Submission Check API
1345 * Also calls process_acknowledgements API to post Acknowledgements
1346 * Returns:
1347 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
1348 * FND_API.G_RET_STS_ERROR if an error occurs
1349 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1350 */
1351
1352 procedure validate_change_request (
1353 p_api_version IN NUMBER,
1354 p_init_msg_list IN VARCHAR2,
1355 x_return_status OUT NOCOPY VARCHAR2,
1356 x_msg_data OUT NOCOPY VARCHAR2,
1357 p_po_header_id IN number,
1358 p_po_release_id IN number,
1359 p_revision_num IN number,
1360 p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1361 x_online_report_id OUT NOCOPY number,
1362 x_pos_errors OUT NOCOPY pos_err_type,
1363 x_doc_check_error_msg OUT NOCOPY Doc_Check_Return_Type) is
1364
1365 x_error_code varchar2(40);
1366 rec_cnt number :=0;
1367 line_cnt number :=0;
1368 ship_cnt number :=0;
1369 dist_cnt number :=0;
1370 ack_cnt number :=0;
1371 p_document_id NUMBER;
1372 v_document_type PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1373 v_type_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1374 v_document_subtype PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1375 sub_check_failed exception;
1376
1377 poLineIdTbl po_tbl_number := po_tbl_number();
1378 unitPriceTbl po_tbl_number := po_tbl_number();
1379 -- <PO_CHANGE_API FPJ> VENDOR_PRODUCT_NUM should use varchar30, not varchar40:
1380 supItemTbl po_tbl_varchar30 := po_tbl_varchar30();
1381 startdateTbl po_tbl_date := po_tbl_date();
1382 expirationdateTbl po_tbl_date := po_tbl_date();
1383 amountTbl po_tbl_number := po_tbl_number();
1384 shipamountTbl po_tbl_number := po_tbl_number();
1385 poLineLocIdTbl po_tbl_number := po_tbl_number();
1386 parentLineLocIdTbl po_tbl_number := po_tbl_number();
1387 quantityTbl po_tbl_number := po_tbl_number();
1388 priceOverrideTbl po_tbl_number := po_tbl_number();
1389 shipmentNumTbl po_tbl_number := po_tbl_number();
1390 promisedDateTbl po_tbl_date := po_tbl_date();
1391
1392 distQtyTbl po_tbl_number := po_tbl_number();
1393 distIdTbl po_tbl_number := po_tbl_number();
1394 distAmtTbl po_tbl_number := po_tbl_number(); -- FPS
1395
1396 l_return_status varchar2(10);
1397 l_sub_check_status varchar2(10);
1398 l_online_report_id number;
1399 l_msg_data varchar2(2000);
1400
1401 --l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1402
1403 -- <PO_CHANGE_API FPJ START>
1404 -- Added a PO_ prefix to the names of the change object types:
1405 vLineChanges PO_LINES_REC_TYPE;
1406 vShipChanges PO_SHIPMENTS_REC_TYPE;
1407 vDistChanges PO_DISTRIBUTIONS_REC_TYPE;
1408 vRequestedChanges PO_CHANGES_REC_TYPE;
1409 -- <PO_CHANGE_API FPJ END>
1410
1411 vAckTbl pos_ack_rec_tbl := pos_ack_rec_tbl();
1412 x_progress varchar2(3) := '000';
1413 l_api_name CONSTANT VARCHAR2(30) := 'validate_change_request';
1414 l_api_version CONSTANT NUMBER := 1.0;
1415 x_sub_errors number;
1416 x_org_id number;
1417 sub_error_flag varchar2(1);
1418 x_cum_flag boolean := FALSE;
1419 x_price number := NULL;
1420 l_error_index number := 0;
1421 l_err_msg_name_tbl po_tbl_varchar30;
1422 l_err_msg_text_tbl po_tbl_varchar2000;
1423 l_total_qty number;
1424 l_ga_ship_qty number;
1425 l_ga_lineLocId number;
1426 l_ga_lineId number;
1427 l_qty_orig number;
1428 l_qty_split number;
1429 l_shipToOrg number;
1430 l_shipToLoc number;
1431 l_needByDate date;
1432 lLine number;
1433 changeOrig varchar2(1) := 'F';
1434 l_price_break_type VARCHAR2(1) := NULL;
1435 l_cumulative_flag BOOLEAN := false;
1436 l_initiator po_change_requests.initiator%type :='SUPPLIER';
1437 --<< Complex work changes for R12 >>
1438 progress_type_tbl PO_TBL_VARCHAR30:= PO_TBL_VARCHAR30();
1439 pay_description_tbl PO_TBL_VARCHAR240:= PO_TBL_VARCHAR240();
1440
1441
1442 cursor ga_line_csr(p_po_header_id in number) is
1443 select po_line_id
1444 from po_lines_archive_all pol
1445 where pol.po_header_id = p_po_header_id and
1446 pol.latest_external_flag='Y' and
1447 pol.from_header_id in (
1448 select po_header_id
1449 from po_headers_all poh
1450 where poh.global_agreement_flag='Y'
1451 and poh.po_header_id=pol.from_header_id) ;
1452
1453 cursor ga_ship_csr(p_line_id in number) is
1454 select line_location_id,quantity
1455 from po_line_locations_archive_all
1456 where po_line_id = p_line_id and
1457 nvl(closed_code,'OPEN') not in('FINALLY CLOSED') and
1458 nvl(cancel_flag,'N') <> 'Y' and
1459 latest_external_flag='Y' ;
1460
1461 BEGIN
1462
1463 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
1464 l_api_name, g_pkg_name)
1465 THEN
1466 RAISE FND_API.g_exc_unexpected_error;
1467 END IF;
1468
1469 x_return_status := FND_API.g_ret_sts_success;
1470 IF g_fnd_debug = 'Y' THEN
1471 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1472 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1473 '.invoked', 'Type: ' ||
1474 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
1475 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
1476 END IF;
1477 END IF;
1478
1479 if (p_po_release_id is not null) then
1480 p_document_id := p_po_release_id;
1481 v_document_type := 'RELEASE';
1482 v_document_subtype := 'RELEASE';
1483 select org_id
1484 into x_org_id
1485 from po_releases_all
1486 where po_release_id= p_po_release_id;
1487 else
1488 p_document_id := p_po_header_id;
1489 select type_lookup_code , org_id
1490 into v_type_code , x_org_id
1491 from po_headers_all
1492 where po_header_id= p_po_header_id;
1493 if (v_type_code in ('STANDARD','PLANNED')) then
1494 v_document_type := 'PO';
1495 v_document_subtype := v_type_code;
1496 elsif (v_type_code in ('BLANKET','CONTRACT')) then
1497 v_document_type := 'PA';
1498 v_document_subtype := v_type_code;
1499 end if;
1500 end if;
1501
1502 -- Set the org context before calling core po api's
1503 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
1504
1505 rec_cnt := p_po_change_requests.count();
1506 -- Now check if the document references a global agreement
1507 -- or a blanket, then get the price accordingly
1508 if (rec_cnt > 0) then
1509 l_initiator:=p_po_change_requests(1).initiator;
1510 end if;
1511 if (rec_cnt > 0) then
1512 FOR i in 1..rec_cnt
1513 LOOP
1514 --Construct the table of line record
1515 if(p_po_change_requests(i).request_level = 'LINE') then
1516 poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1517 startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1518
1519 line_cnt := line_cnt + 1;
1520 poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1521 unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1522 supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1523 startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1524 expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1525 amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1526 end if; -- end if line
1527
1528 -- do not send cancellation request
1529 if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1530 (p_po_change_requests(i).action_type not in ('ACCEPT','REJECT','CANCEL'))) then
1531 ship_cnt := ship_cnt + 1;
1532 poLineLocIdTbl.extend; quantityTbl.extend;
1533 promisedDateTbl.extend;priceOverrideTbl.extend;
1534 parentLineLocIdTbl.extend;
1535 shipmentNumTbl.extend;
1536 shipamountTbl.extend;
1537 progress_type_tbl.extend;
1538 pay_description_tbl.extend;
1539 -- if release / standard po referencing a GA/ Quotation
1540 -- Call Get Price Break API.
1541
1542 --if ((p_po_change_requests(i).from_header_id is not null) or
1543 -- (p_po_change_requests(i).po_release_id is not null)) then
1544 if (p_po_change_requests(i).po_release_id is not null) then
1545
1546 SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1547 INTO l_price_break_type
1548 FROM po_lines_all
1549 WHERE po_line_id = p_po_change_requests(i).document_line_id;
1550
1551 IF (l_price_break_type = 'Y') THEN
1552 l_cumulative_flag := TRUE;
1553 ELSE
1554 l_cumulative_flag := FALSE;
1555 END IF;
1556
1557 if(p_po_change_requests(i).new_price is null) then
1558 x_price := po_sourcing2_sv.get_break_price(
1559 nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1560 p_po_change_requests(i).ship_to_organization_id,
1561 p_po_change_requests(i).ship_to_location_id,
1562 p_po_change_requests(i).document_line_id,
1563 l_cumulative_flag,
1564 nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1565 p_po_change_requests(i).document_line_location_id);
1566 p_po_change_requests(i).old_price := x_price;
1567 end if;
1568 end if; -- end if release
1569
1570 poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1571 parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1572 quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573 promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574 priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575 shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576 progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1577 pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1578 shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1579 end if; -- if shipment
1580
1581 if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1582 dist_cnt := dist_cnt + 1;
1583 distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1584 distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1585 distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1586 distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1587
1588 end if; -- if dist
1589
1590 --end if; -- end of rec count
1591 END LOOP;
1592
1593 -- Now check if the change request consists of any shipments that refers to a GA
1594 -- in that case sum up the quatities and call price break api to get new line price
1595 -- and post a line level change to Doc Check API
1596
1597 if (p_po_release_id is null ) then
1598 open ga_line_csr(p_po_header_id);
1599 loop
1600
1601 l_qty_orig := 0;
1602 l_qty_split := 0;
1603 l_total_qty := 0;
1604 x_price := 0;
1605
1606 fetch ga_line_csr into l_ga_lineId;
1607 exit when ga_line_csr%notfound;
1608
1609 open ga_ship_csr(l_ga_lineId);
1610
1611 loop
1612
1613 fetch ga_ship_csr
1614 into l_ga_lineLocId,l_ga_ship_qty;
1615 exit when ga_ship_csr%notfound;
1616 changeOrig := 'F';
1617 FOR i in 1..rec_cnt LOOP
1618
1619 if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1620 p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1621
1622 if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1623 p_po_change_requests(i).new_quantity is not null and
1624 p_po_change_requests(i).parent_line_location_id is null ) then
1625
1626 l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1627 changeOrig := 'T';
1628 end if;
1629 -- Sum up all the split quantities
1630 if (p_po_change_requests(i).parent_line_location_id is not null and
1631 p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1632 p_po_change_requests(i).new_quantity is not null) then
1633
1634 l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1635
1636 end if;
1637 end if ; -- if shipment change
1638 END LOOP;
1639
1640 if (changeOrig = 'F') then
1641 -- if original shipment hasnt been changed
1642 l_qty_orig := l_qty_orig + l_ga_ship_qty;
1643 end if;
1644
1645 end loop;
1646 close ga_ship_csr;
1647
1648 l_total_qty := l_qty_orig + l_qty_split;
1649
1650 -- Get the price break for the total quantity for each line using the min shipment
1651 -- need by date and ship to org
1652 select ship_to_location_id, ship_to_organization_id,need_by_date
1653 into l_shipToLoc,l_shipToOrg,l_needBydate
1654 from po_line_locations_archive_all
1655 where shipment_num = (select min(shipment_num)
1656 from po_line_locations_archive_all
1657 where po_line_id = l_ga_lineId and
1658 nvl(closed_code,'OPEN') not in('FINALLY CLOSED') and
1659 nvl(cancel_flag,'N') <> 'Y' and
1660 latest_external_flag='Y' ) and
1661 latest_external_flag='Y' and
1662 po_line_id = l_ga_lineId ;
1663
1664 -- For Global Agreement refered Standard PO's x_cum_flag is always FALSE
1665 x_price := po_sourcing2_sv.get_break_price(
1666 l_total_qty, l_shipToOrg, l_shipToLoc, l_ga_lineId, x_cum_flag, l_needBydate, null);
1667
1668 -- Post a line level change with the price returned from price break api
1669
1670 lLine := poLineIdTbl.count;
1671 poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1672 startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1673
1674 line_cnt := line_cnt + 1;
1675 poLineIdTbl(lLine+1) := l_ga_lineId;
1676 unitPriceTbl(lLine+1) := x_price;
1677 supItemTbl(lLine+1) := null;
1678 startdateTbl(lLine+1) := null;
1679 expirationdateTbl(lLine+1) := null;
1680 amountTbl(lLine+1) := null;
1681
1682
1683 IF g_fnd_debug = 'Y' THEN
1684 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1685 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name
1686 , 'Calculating Price break for STD PO from GA: ' ||
1687 ', PO Line Id : ' || NVL(TO_CHAR(l_ga_lineId),'null') ||
1688 ', Total Quantity : ' || NVL(TO_CHAR(l_total_qty),'null') ||
1689 ', Price Break : ' || NVL(TO_CHAR(x_price),'null'));
1690 END IF;
1691 END IF;
1692 end loop;
1693 close ga_line_csr;
1694
1695 end if; -- if release id
1696 -- Construct Line Record Changes
1697
1698 -- <PO_CHANGE_API FPJ START>
1699 -- Added a PO_ prefix to the names of the change object types and
1700 -- modified their constructors.
1701 vLineChanges := PO_LINES_REC_TYPE.create_object (
1702 p_po_line_id => poLineIdTbl,
1703 p_unit_price => unitPriceTbl,
1704 p_vendor_product_num => supItemTbl,
1705 p_start_date => startdateTbl,
1706 p_expiration_date => expirationdateTbl,
1707 p_amount => amountTbl
1708 );
1709
1710 vShipChanges := PO_SHIPMENTS_REC_TYPE.create_object (
1711 p_po_line_location_id => poLineLocIdTbl,
1712 p_quantity => quantityTbl,
1713 p_promised_date => promisedDateTbl,
1714 p_price_override => priceOverrideTbl,
1715 p_parent_line_location_id => parentLineLocIdTbl,
1716 p_split_shipment_num => shipmentNumTbl,
1717 p_payment_type => progress_type_tbl,
1718 p_description => pay_description_tbl,
1719 p_amount => shipamountTbl
1720 );
1721
1722 vDistChanges := PO_DISTRIBUTIONS_REC_TYPE.create_object (
1723 p_po_distribution_id => distIdTbl,
1724 p_quantity_ordered => distQtyTbl,
1725 p_amount_ordered => distAmtTbl -- FPS
1726 );
1727
1728 vRequestedChanges := PO_CHANGES_REC_TYPE.create_object (
1729 p_po_header_id => p_po_header_id,
1730 p_po_release_id => p_po_release_id,
1731 p_line_changes => vLineChanges,
1732 p_shipment_changes => vShipChanges,
1733 p_distribution_changes => vDistChanges
1734 );
1735 -- <PO_CHANGE_API FPJ END>
1736
1737
1738
1739 PO_DOCUMENT_CHECKS_GRP.PO_SUBMISSION_CHECK(
1740 p_api_version => 1.0,
1741 p_action_requested => 'DOC_SUBMISSION_CHECK',
1742 p_document_type => v_document_type,
1743 p_document_subtype => v_document_subtype,
1744 p_document_id => p_document_id,
1745 p_org_id => x_org_id,
1746 p_requested_changes => vRequestedChanges,
1747 p_req_chg_initiator => l_initiator,
1748 x_return_status => l_return_status,
1749 x_sub_check_status => l_sub_check_status,
1750 x_msg_data => l_msg_data,
1751 x_online_report_id => x_online_report_id,
1752 x_doc_check_error_record => x_doc_check_error_msg);
1753
1754
1755
1756
1757 l_err_msg_name_tbl := po_tbl_varchar30();
1758 l_err_msg_text_tbl := po_tbl_varchar2000();
1759 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
1760
1761 if ( l_return_status = FND_API.G_RET_STS_SUCCESS AND
1762 l_sub_check_status = FND_API.G_RET_STS_ERROR) THEN
1763
1764 x_sub_errors := x_doc_check_error_msg.online_report_id.count;
1765 FOR i in 1..x_sub_errors loop
1766 if ((x_doc_check_error_msg.message_name(i) not in
1767 ('PO_SUB_PO_LINE_NE_SHIP_AMT','PO_SUB_PO_LINE_NE_SHIP_QTY',
1768 'PO_SUB_PO_SHIP_NE_DIST_AMT','PO_SUB_PO_SHIP_NE_DIST_QTY',
1769 'PO_SUB_REQ_LINE_NE_DIST_AMT','PO_SUB_REQ_LINE_NE_DIST_QTY',
1770 'PO_SUB_REL_SHIP_NE_DIST_AMT','PO_SUB_REL_SHIP_NE_DIST_QTY',
1771 'PO_SUB_SHIP_NO_DIST','PO_SUB_REL_SHIP_NO_DIST',
1772 'PO_SUB_PAY_ITEM_NE_LINE_AMT')) --Bug 5547289
1773 AND nvl(x_doc_check_error_msg.message_type(i), 'E') <> 'W') then
1774 sub_error_flag := 'Y';
1775 l_error_index := l_error_index + 1;
1776 x_pos_errors.message_name.extend;
1777 x_pos_errors.text_line.extend;
1778 x_pos_errors.message_name(l_error_index) := x_doc_check_error_msg.message_name(i);
1779 x_pos_errors.text_line(l_error_index) := x_doc_check_error_msg.text_line(i);
1780 else
1781 sub_error_flag := 'N';
1782 end if;
1783 end loop;
1784 -- Some other errors were reported from submission check api
1785 if (sub_error_flag = 'Y') then
1786 raise sub_check_failed;
1787 else
1788 x_return_status := FND_API.G_RET_STS_SUCCESS;
1789 end if;
1790 elsif (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1791 --x_msg_data has stuff regarding doc sub check.
1792 l_error_index := l_error_index + 1;
1793 x_pos_errors.message_name.extend;
1794 x_pos_errors.text_line.extend;
1795 x_pos_errors.message_name(l_error_index) := null;
1796 x_pos_errors.text_line(l_error_index) := l_msg_data;
1797 raise sub_check_failed;
1798 -- If l_return_status and l_sub_check_status = FND_API.G_RET_STS_SUCCESS
1799 -- Then Continue no errors in doc check
1800 end if;
1801
1802 x_progress := '007';
1803 end if; --end rec cnt
1804
1805 EXCEPTION
1806 WHEN FND_API.g_exc_error THEN
1807 x_return_status := FND_API.g_ret_sts_error;
1808 WHEN FND_API.g_exc_unexpected_error THEN
1809 x_return_status := FND_API.g_ret_sts_unexp_error;
1810 WHEN sub_check_failed THEN
1811 x_return_status := FND_API.g_ret_sts_error;
1812 WHEN OTHERS THEN
1813 x_return_status := FND_API.g_ret_sts_unexp_error;
1814 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1815 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1816 IF g_fnd_debug = 'Y' THEN
1817 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1818 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1819 l_api_name || '.others_exception', sqlcode);
1820 END IF;
1821 END IF;
1822 END IF;
1823 l_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1824 p_encoded => 'F');
1825 END validate_change_request;
1826
1827 /* Overloaded Procedure to return only filtered Errors for supplier changes*/
1828
1829 procedure validate_change_request (
1830 p_api_version IN NUMBER,
1831 p_init_msg_list IN VARCHAR2,
1832 x_return_status OUT NOCOPY VARCHAR2,
1833 x_msg_data OUT NOCOPY VARCHAR2,
1834 p_po_header_id IN number,
1835 p_po_release_id IN number,
1836 p_revision_num IN number,
1837 p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1838 x_online_report_id OUT NOCOPY number,
1839 x_pos_errors OUT NOCOPY pos_err_type) is
1840
1841 l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1842 l_msg_data varchar2(2000) := NULL;
1843
1844 BEGIN
1845 --l_po_change_requests := p_po_change_requests;
1846
1847 validate_change_request (
1848 p_api_version => 1.0,
1849 p_init_msg_list => FND_API.G_FALSE,
1850 x_return_status => x_return_status,
1851 x_msg_data => l_msg_data,
1852 p_po_header_id => p_po_header_id,
1853 p_po_release_id => p_po_release_id,
1854 p_revision_num => p_revision_num,
1855 p_po_change_requests => p_po_change_requests,
1856 x_online_report_id => x_online_report_id,
1857 x_pos_errors => x_pos_errors,
1858 x_doc_check_error_msg => l_doc_check_error_msg);
1859
1860 END validate_change_request;
1861
1862 /**
1863 * Private Function: ifLineChangable
1864 * Requires: PO_LINE_ID
1865 * Modifies: None
1866 * Effects:
1867 * Determines id the Line Price can be changed based on
1868 * 1. ACCRUE_ON_RECEIPT_FLAG
1869 * 2. QUANTITY_BILLED
1870 * Returns:
1871 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
1872 * FND_API.G_RET_STS_ERROR if an error occurs
1873 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1874 */
1875
1876 function ifLineChangable( p_po_line_id IN number)
1877 return varchar2 is
1878
1879 v_qty_billed number;
1880 v_accr_rcpt_cnt number;
1881 v_qty_rcvd number;
1882 x_display_stat varchar2(1) :='Y';
1883 x_price_change po_lines_all.allow_price_override_flag%type;
1884 x_order_type po_line_types.order_type_lookup_code%type;
1885 l_return_status varchar2(3);
1886 l_retro_active_price_change varchar2(3);
1887 l_doc_type varchar2(25);
1888 x_po_header_id po_headers_all.po_header_id%type;
1889 l_is_complex_po varchar2(1):='N';
1890 l_is_actual_po varchar2(1):='Y';
1891
1892 BEGIN
1893
1894 /* not needed anymore. use the po api.
1895 begin
1896
1897 select nvl(allow_price_override_flag,'N')
1898 into x_price_change
1899 from po_lines_all
1900 where po_line_id =
1901 (select from_line_id
1902 from po_lines_all
1903 where po_line_id=p_po_line_id);
1904 exception
1905 when no_data_found then
1906 x_price_change :='Y';
1907 end;
1908 */
1909
1910 --See if the PO is of type BLANKET; if so, the price can be updatable at any time.
1911 select poh.type_lookup_code
1912 into l_doc_type
1913 from po_headers_all poh, po_lines_all pol
1914 where poh.po_header_id = pol.po_header_id
1915 and po_line_id = p_po_line_id;
1916
1917 if (l_doc_type = 'STANDARD') then
1918
1919 -- Check if the line type is amount based then do not allow change
1920 --PO API does not cover this.
1921 begin
1922 select polt.ORDER_TYPE_LOOKUP_CODE,PO_HEADER_ID
1923 into x_order_type,x_po_header_id
1924 from po_lines_all pol,po_line_types polt
1925 where pol.po_line_id = p_po_line_id and
1926 pol.line_type_id = polt.line_type_id;
1927 exception
1928 when no_data_found then
1929 x_price_change :='Y';
1930 end;
1931
1932 if (PO_COMPLEX_WORK_PVT.is_complex_work_po(x_po_header_id)) then
1933 l_is_complex_po:='Y';
1934 end if;
1935
1936 if (PO_COMPLEX_WORK_PVT.is_financing_po(x_po_header_id)) then
1937 l_is_actual_po:='N';
1938 end if;
1939
1940 if (((x_order_type = 'AMOUNT') and (l_is_complex_po='N')) or ((l_is_actual_po='Y') and (l_is_complex_po='Y'))) then
1941 x_display_stat := 'N';
1942 else
1943
1944 PO_DOCUMENT_CHECKS_GRP.check_std_po_price_updateable (
1945 p_api_version => 1.0,
1946 x_return_status =>l_return_status,
1947 p_po_line_id => p_po_line_id,
1948 p_from_price_break => 'N',
1949 p_add_reasons_to_msg_list => 'N',
1950 x_price_updateable => x_price_change,
1951 x_retroactive_price_change => l_retro_active_price_change);
1952
1953 if (x_price_change = PO_CORE_S.G_PARAMETER_NO) then
1954 x_display_stat := 'N';
1955 else
1956 x_display_stat := 'Y';
1957 end if;
1958
1959 end if;
1960
1961 else --end of if l_doc_type = 'STANDARD'
1962 x_display_stat := 'Y'; --For blankets, planned, GAs, price is always changeable.
1963 end if;
1964
1965
1966
1967 return x_display_stat;
1968
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 return('');
1972 END ifLineChangable;
1973
1974
1975 procedure validateCancelRequest(
1976 p_api_version IN NUMBER,
1977 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1978 x_return_status OUT NOCOPY VARCHAR2,
1979 p_po_header_id IN NUMBER,
1980 p_po_release_id IN NUMBER) IS
1981
1982 p_document_id NUMBER;
1983 v_document_type PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1984 v_document_subtype PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1985 v_type_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
1986 l_api_name CONSTANT VARCHAR2(30) := 'validateCancelRequest';
1987 l_api_version CONSTANT NUMBER := 1.0;
1988 x_org_id number;
1989 x_ship_count NUMBER := 0;
1990
1991
1992 BEGIN
1993 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
1994 l_api_name, g_pkg_name)
1995 THEN
1996 RAISE FND_API.g_exc_unexpected_error;
1997 END IF;
1998
1999 x_return_status := FND_API.g_ret_sts_success;
2000
2001 -- Call this when logging is enabled
2002
2003 IF g_fnd_debug = 'Y' THEN
2004 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2005 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2006 '.invoked', 'Type: ' ||
2007 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
2008 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
2009 END IF;
2010 END IF;
2011 if (p_po_release_id is not null) then
2012
2013 p_document_id := p_po_release_id;
2014 v_document_type := 'RELEASE';
2015 v_document_subtype := 'RELEASE';
2016
2017
2018 select poh.type_lookup_code,por.org_id
2019 into v_type_code,x_org_id
2020 from po_headers_all poh,po_releases_all por
2021 where por.po_header_id = poh.po_header_id and
2022 por.po_release_id = p_po_release_id;
2023
2024 if (v_type_code = 'BLANKET') then
2025 v_document_type := 'RELEASE';
2026 v_document_subtype := 'BLANKET';
2027 elsif (v_type_code = 'PLANNED') then
2028 v_document_type := 'RELEASE';
2029 v_document_subtype := 'SCHEDULED';
2030 end if;
2031 else
2032 p_document_id := p_po_header_id;
2033 select type_lookup_code into v_type_code
2034 from po_headers_all
2035 where po_header_id= p_po_header_id;
2036 if (v_type_code in ('STANDARD','PLANNED')) then
2037 v_document_type := 'PO';
2038 v_document_subtype := v_type_code;
2039 elsif (v_type_code in ('BLANKET','CONTRACT')) then
2040 v_document_type := 'PA';
2041 v_document_subtype := v_type_code;
2042 end if;
2043
2044 select org_id
2045 into x_org_id
2046 from po_headers_all
2047 where po_header_id= p_po_header_id;
2048
2049 end if;
2050 -- Set the org context before calling the cancel api
2051
2052 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
2053
2054 PO_Document_Control_GRP.check_control_action(
2055 p_api_version => 1.0,
2056 p_init_msg_list => FND_API.G_TRUE,
2057 x_return_status => x_return_status,
2058 p_doc_type => v_document_type,
2059 p_doc_subtype => v_document_subtype,
2060 p_doc_id => p_po_header_id,
2061 p_doc_num => null,
2062 p_release_id => p_po_release_id,
2063 p_release_num => null,
2064 p_doc_line_id => null,
2065 p_doc_line_num => null,
2066 p_doc_line_loc_id => null,
2067 p_doc_shipment_num => null,
2068 p_action => 'CANCEL');
2069
2070
2071 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2072
2073 BEGIN
2074 if (p_po_release_id is not null) then
2075 SELECT count(*)
2076 INTO x_ship_count
2077 FROM PO_LINE_LOCATIONS_ALL POLL,PO_LINES_ALL POL
2078 WHERE POLL.po_release_id = p_po_release_id
2079 AND POLL.po_line_id = POL.po_line_id
2080 AND nvl(POLL.cancel_flag, 'N') = 'N'
2081 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2082 AND POLL.shipment_type in ('SCHEDULED', 'BLANKET')
2083 AND (nvl(POLL.quantity_received,0) >= nvl(POLL.quantity,0)
2084 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity,0)
2085 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity_received,0))
2086 AND POL.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE');
2087 else
2088 SELECT count(*)
2089 INTO x_ship_count
2090 FROM PO_LINE_LOCATIONS_ALL POLL,PO_LINES_ALL POL,PO_HEADERS_ALL POH
2091 WHERE
2092 POH.PO_HEADER_ID = p_po_header_id
2093 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
2094 AND POH.TYPE_LOOKUP_CODE ='STANDARD'
2095 AND POLL.po_line_id = POL.po_line_id
2096 AND nvl(POLL.cancel_flag, 'N') = 'N'
2097 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2098 AND (nvl(POLL.quantity_received,0) >= nvl(POLL.quantity,0)
2099 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity,0)
2100 OR nvl(POLL.quantity_billed,0) > nvl(POLL.quantity_received,0))
2101 AND POL.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
2102 AND NVL(POLL.payment_type,' ') <> 'ADVANCE'; -- <Bug 5504546>
2103
2104 end if;
2105
2106 if (x_ship_count > 0) then
2107 x_return_status := FND_API.G_RET_STS_ERROR;
2108 else
2109 x_return_status := FND_API.G_RET_STS_SUCCESS;
2110 end if;
2111
2112 EXCEPTION
2113 WHEN FND_API.g_exc_error THEN
2114 x_return_status := FND_API.g_ret_sts_error;
2115 WHEN FND_API.g_exc_unexpected_error THEN
2116 x_return_status := FND_API.g_ret_sts_unexp_error;
2117 WHEN OTHERS THEN
2118 x_return_status := FND_API.g_ret_sts_unexp_error;
2119 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2120 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2121 IF g_fnd_debug = 'Y' THEN
2122 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2123 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2124 l_api_name || '.others_exception', sqlcode);
2125 END IF;
2126 END IF;
2127 END IF;
2128
2129 END;
2130 END IF;
2131
2132
2133 EXCEPTION
2134
2135 WHEN FND_API.g_exc_error THEN
2136 x_return_status := FND_API.g_ret_sts_error;
2137 WHEN FND_API.g_exc_unexpected_error THEN
2138 x_return_status := FND_API.g_ret_sts_unexp_error;
2139 WHEN OTHERS THEN
2140 x_return_status := FND_API.g_ret_sts_unexp_error;
2141 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2142 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2143 IF g_fnd_debug = 'Y' THEN
2144 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2145 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2146 l_api_name || '.others_exception', sqlcode);
2147 END IF;
2148 END IF;
2149 END IF;
2150
2151 END validateCancelRequest;
2152
2153 PROCEDURE getShipmentStatus(
2154 p_line_location_id IN NUMBER,
2155 p_po_header_id IN NUMBER,
2156 p_po_release_id IN NUMBER,
2157 p_revision_num IN NUMBER,
2158 x_msg_code OUT NOCOPY VARCHAR2,
2159 x_msg_display OUT NOCOPY VARCHAR2,
2160 x_note OUT NOCOPY LONG) IS
2161
2162 x_ack_stat varchar2(40);
2163 x_accp_flag po_headers_all.acceptance_required_flag%type;
2164 x_revision number;
2165 x_cons_flag po_line_locations.consigned_flag%type;
2166
2167 BEGIN
2168 if p_line_location_id is not null then
2169 if (p_po_release_id is null) then
2170 select DECODE( nvl(pll.cancel_flag,'N'),
2171 'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'),
2172 'N',DECODE(NVL(pll.CONSIGNED_FLAG,'N'),
2173 'Y',DECODE(NVL(pll.CLOSED_CODE,'OPEN'),'CLOSED FOR INVOICE',
2174 DECODE(
2175 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2176 (1.0,FND_API.G_FALSE,pll.line_location_id,
2177 pll.po_header_id, p_po_release_id,p_revision_num),
2178 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2179 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2180 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2181 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2182 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2183 '',polc.displayed_field),
2184 polc.displayed_field
2185 ),
2186 'N',
2187 CASE
2188 when NVL(pll.CLOSED_CODE,'OPEN')='OPEN' OR NVL(pll.CLOSED_CODE,'OPEN')='CLOSED FOR RECEIVING'
2189 OR NVL(pll.CLOSED_CODE,'OPEN') = 'CLOSED FOR INVOICE'
2190 THEN
2191 DECODE(
2192 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2193 (1.0,FND_API.G_FALSE,pll.line_location_id,
2194 pll.po_header_id, p_po_release_id,p_revision_num),
2195 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2196 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2197 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2198 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2199 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2200 '',polc.displayed_field)
2201 ELSE polc.displayed_field
2202 END
2203 )
2204 ) ,
2205 nvl(pll.closed_code,'OPEN'),
2206 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2207 (1.0,FND_API.G_FALSE,pll.line_location_id,
2208 pll.po_header_id, p_po_release_id,p_revision_num),nvl(poh.acceptance_required_flag,'N'),
2209 poh.revision_num,nvl(pll.consigned_flag,'N')
2210 into x_msg_display,x_msg_code,x_ack_stat,x_accp_flag,x_revision,x_cons_flag
2211 from po_line_locations_all pll,
2212 po_headers_all poh,
2213 po_lookup_codes polc
2214 where
2215 polc.lookup_code = NVL(pll.closed_code, 'OPEN') and
2216 polc.lookup_type = 'DOCUMENT STATE' and
2217 poh.po_header_id = pll.po_header_id and
2218 pll.line_location_id = p_line_location_id ;
2219
2220 if ( x_ack_stat in ('REJECTED','ACCEPTED')) then
2221 begin
2222 select note into x_note
2223 from po_acceptances
2224 where po_line_location_id=p_line_location_id and
2225 revision_num = x_revision;
2226 exception
2227 when others then
2228 x_note := null;
2229 end;
2230 end if;
2231
2232 else
2233 select DECODE( nvl(pll.cancel_flag,'N'),
2234 'Y',fnd_message.get_string('POS','POS_PO_CANCELLED'),
2235 'N',DECODE(NVL(pll.CONSIGNED_FLAG,'N'),
2236 'Y',DECODE(NVL(pll.CLOSED_CODE,'OPEN'),'CLOSED FOR INVOICE',
2237 DECODE(
2238 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2239 (1.0,FND_API.G_FALSE,pll.line_location_id,
2240 pll.po_header_id, p_po_release_id,p_revision_num),
2241 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2242 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2243 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2244 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2245 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2246 '',polc.displayed_field),
2247 polc.displayed_field
2248 ),
2249 'N',
2250 CASE
2251 when NVL(pll.CLOSED_CODE,'OPEN')='OPEN' OR NVL(pll.CLOSED_CODE,'OPEN')='CLOSED FOR RECEIVING'
2252 OR NVL(pll.CLOSED_CODE,'OPEN') = 'CLOSED FOR INVOICE'
2253 THEN
2254 DECODE(
2255 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2256 (1.0,FND_API.G_FALSE,pll.line_location_id,
2257 pll.po_header_id, p_po_release_id,p_revision_num),
2258 'PENDING_CHANGE',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE'),
2259 'ACK_REQUIRED',FND_MESSAGE.GET_STRING('POS','POS_ACCP_REQUIRED'),
2260 'PENDING_CANCEL',FND_MESSAGE.GET_STRING('POS','POS_PENDING_CANCEL'),
2261 'ACCEPTED',FND_MESSAGE.GET_STRING('POS','POS_PO_ACCEPTED'),
2262 'REJECTED',FND_MESSAGE.GET_STRING('POS','POS_PO_REJECTED'),
2263 '',polc.displayed_field)
2264 ELSE polc.displayed_field
2265 END
2266 )
2267 ) ,
2268 nvl(pll.closed_code,'OPEN'),
2269 PO_ACKNOWLEDGE_PO_GRP.GET_SHIPMENT_ACK_CHANGE_STATUS
2270 (1.0,FND_API.G_FALSE,pll.line_location_id,
2271 pll.po_header_id, p_po_release_id,p_revision_num),nvl(por.acceptance_required_flag,'N'),
2272 por.revision_num,nvl(pll.consigned_flag,'N')
2273 into x_msg_display,x_msg_code,x_ack_stat,x_accp_flag,x_revision,x_cons_flag
2274 from po_line_locations_all pll,
2275 po_releases_all por,
2276 po_lookup_codes polc
2277 where
2278 polc.lookup_code = NVL(pll.closed_code, 'OPEN') and
2279 polc.lookup_type = 'DOCUMENT STATE' and
2280 por.po_header_id = pll.po_header_id and
2281 por.po_release_id = p_po_release_id and
2282 pll.line_location_id = p_line_location_id ;
2283
2284 if ( x_ack_stat = 'REJECTED') then
2285 begin
2286 select note into x_note
2287 from po_acceptances
2288 where po_line_location_id=p_line_location_id and
2289 revision_num = x_revision;
2290 exception
2291 when others then
2292 x_note := null;
2293 end;
2294 end if;
2295
2296 end if;
2297
2298 --Bug 4107241: allow acknowledge any shipments not closed/finally closed.
2299 if (x_ack_stat = 'ACK_REQUIRED' and
2300 x_msg_code not in ('CLOSED', 'FINALLY CLOSED') ) then
2301 x_msg_code :='ACK REQUIRED';
2302 -- valid assumption for now, coz this values is reqd in UI
2303 -- to display ack actions in poplist , where only PO's with
2304 -- status OPEN ack is allowed
2305 elsif x_ack_stat = 'REJECTED' then
2306 x_msg_code := 'REJECTED';
2307 elsif (x_ack_stat = 'ACCEPTED' and x_accp_flag='Y') then
2308 x_msg_code := 'ACKSTAGE';
2309 elsif x_ack_stat = 'PENDING_CANCEL' then
2310 x_msg_code := 'PENDING_CANCEL';
2311
2312 end if;
2313 else
2314 -- This means a split shipment pass back PENDING
2315 select FND_MESSAGE.GET_STRING('POS','POS_PENDING_CHANGE')
2316 into x_msg_display
2317 from dual;
2318
2319 x_msg_code :='PENDING_CHANGE';
2320 end if;
2321
2322 END getShipmentStatus;
2323
2324 procedure save_cancel_request(
2325 p_api_version IN NUMBER ,
2326 p_Init_Msg_List IN VARCHAR2 ,
2327 x_return_status OUT NOCOPY VARCHAR2,
2328 p_po_header_id IN number,
2329 p_po_release_id IN number,
2330 p_revision_num IN number,
2331 p_po_change_requests IN pos_chg_rec_tbl,
2332 x_request_group_id OUT NOCOPY NUMBER) is
2333
2334 l_api_name CONSTANT VARCHAR2(30) := 'save_cancel_request';
2335 l_api_version_number CONSTANT NUMBER := 1.0;
2336 l_request_group_id NUMBER;
2337
2338 BEGIN
2339 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2340 -- initialize message list
2341 FND_MSG_PUB.initialize;
2342 END IF;
2343
2344 -- Standard call to check for call compatibility.
2345
2346 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2347 p_api_version,
2348 l_api_name,
2349 G_PKG_NAME)
2350 THEN
2351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2352 END IF;
2353
2354 -- initialize return status
2355 x_return_status := FND_API.G_RET_STS_SUCCESS;
2356 save_request(
2357 p_api_version => 1.0,
2358 p_init_msg_list => FND_API.G_FALSE,
2359 x_return_status => x_return_status,
2360 p_po_header_id => p_po_header_id,
2361 p_po_release_id => p_po_release_id,
2362 p_revision_num => p_revision_num,
2363 p_po_change_requests => p_po_change_requests,
2364 x_request_group_id => l_request_group_id);
2365
2366 -- Call Update PO Procedure to set PO in IN PROCESS
2367 update_po_attributes(p_po_header_id,
2368 p_po_release_id,
2369 p_revision_num,
2370 l_request_group_id,
2371 x_return_status);
2372
2373 -- Start the workflow for cancel request
2374 if (x_return_status = FND_API.G_RET_STS_SUCCESS ) then
2375 x_return_status := startSupplierWF(
2376 p_po_header_id,p_po_release_id,p_revision_num,
2377 l_request_group_id,'N');
2378 end if;
2379
2380 EXCEPTION
2381 WHEN FND_API.g_exc_error THEN
2382 x_return_status := FND_API.g_ret_sts_error;
2383 WHEN FND_API.g_exc_unexpected_error THEN
2384 x_return_status := FND_API.g_ret_sts_unexp_error;
2385 WHEN OTHERS THEN
2386 x_return_status := FND_API.g_ret_sts_unexp_error;
2387
2388 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2389 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2390 IF g_fnd_debug = 'Y' THEN
2391 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2392 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2393 l_api_name || '.others exception' ,sqlcode);
2394 END IF;
2395 END IF;
2396 END IF;
2397
2398
2399 END save_cancel_request;
2400
2401 /**
2402 * Private Function: getLineAttrs
2403 * Requires: PO_LINE_ID
2404 * Modifies: None
2405 * Effects:
2406 * Determines if there is a Global Agreement,Un Number, Haz Class
2407 * Returns:
2408 * x_ga_number, x_un_number, x_haz_class
2409 */
2410 procedure getLineAttrs(
2411 p_from_header_id IN NUMBER,
2412 p_un_number_id IN NUMBER,
2413 p_haz_class_id IN NUMBER,
2414 x_ga_number OUT NOCOPY VARCHAR2,
2415 x_un_number OUT NOCOPY VARCHAR2,
2416 x_haz_class_desc OUT NOCOPY VARCHAR2) is
2417
2418 BEGIN
2419
2420 BEGIN
2421 SELECT segment1
2422 INTO x_ga_number
2423 FROM po_headers_all
2424 WHERE po_header_id = p_from_header_id
2425 AND global_agreement_flag='Y';
2426 EXCEPTION
2427 when no_data_found then
2428 x_ga_number := null;
2429 END;
2430
2431 if p_un_number_id is not null then
2432
2433 BEGIN
2434 SELECT UN_NUMBER
2435 INTO x_un_number
2436 FROM PO_UN_NUMBERS_TL
2437 WHERE UN_NUMBER_ID = p_un_number_id
2438 AND LANGUAGE = USERENV('LANG');
2439 --AND SOURCE_LANG = USERENV('LANG'); Bug 3637026
2440 EXCEPTION
2441 when no_data_found then
2442 x_un_number := null;
2443 END;
2444 end if;
2445
2446 if p_haz_class_id is not null then
2447
2448 BEGIN
2449 SELECT DESCRIPTION
2450 INTO x_haz_class_desc
2451 FROM PO_HAZARD_CLASSES_TL
2452 WHERE HAZARD_CLASS_ID = p_haz_class_id
2453 AND LANGUAGE = USERENV('LANG');
2454 --AND SOURCE_LANG = USERENV('LANG'); Bug 3637026
2455 EXCEPTION
2456 when no_data_found then
2457 x_haz_class_desc := null;
2458 END;
2459 end if;
2460
2461 END getLineAttrs;
2462
2463 /**
2464 * Procedure: cancel_change_request
2465 * Requires: PO_LINE_ID or po_line_location_id
2466 * Modifies: None
2467 * Effects:
2468 * Determines if there is any change request pending approval for the buyer
2469 * and cancels the request subsequently
2470 *
2471 */
2472 PROCEDURE cancel_change_request
2473 (p_api_version IN NUMBER,
2474 p_init_msg_list IN VARCHAR2,
2475 x_return_status OUT NOCOPY VARCHAR2,
2476 p_po_header_id IN NUMBER,
2477 p_po_release_id IN NUMBER,
2478 p_po_line_id IN NUMBER,
2479 p_po_line_location_id IN NUMBER)
2480 IS
2481
2482 l_api_name CONSTANT VARCHAR2(30) := 'cancel_change_request';
2483 l_api_version CONSTANT NUMBER := 1.0;
2484 l_cancel_msg fnd_new_messages.message_text%type := fnd_message.get_string('POS','POS_AUTO_CANCEL_BY_BUYER');
2485 xGrpId number := 0;
2486 xRevNum number ;
2487 lGrpId number := 0;
2488 lRevNum number ;
2489 l_return_status varchar2(1);
2490 l_msg_out varchar2(2000);
2491 l_revision_num number;
2492
2493 cursor c1(p_po_header_id in number) is
2494 select change_request_group_id,DOCUMENT_REVISION_NUM
2495 from po_change_requests
2496 where document_header_id = p_po_header_id and
2497 document_type = 'PO' and
2498 change_active_flag= 'Y' and
2499 initiator = 'SUPPLIER' and
2500 request_status not in ('ACCEPTED', 'REJECTED');
2501
2502 cursor c2(p_po_release_id in number) is
2503 select change_request_group_id,DOCUMENT_REVISION_NUM
2504 from po_change_requests
2505 where po_release_id = p_po_release_id and
2506 document_type = 'RELEASE' and
2507 change_active_flag= 'Y' and
2508 initiator = 'SUPPLIER' and
2509 request_status not in ('ACCEPTED', 'REJECTED');
2510
2511 BEGIN
2512 -- Start standard API initialization
2513 IF FND_API.to_boolean(p_init_msg_list) THEN
2514 FND_MSG_PUB.initialize;
2515 END IF;
2516 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2517 l_api_name, g_pkg_name)
2518 THEN
2519 RAISE FND_API.g_exc_unexpected_error;
2520 END IF;
2521 x_return_status := FND_API.g_ret_sts_success;
2522 -- End standard API initialization
2523
2524 -- Get the change request group id for the change requests if any
2525 if (p_po_release_id is not null) then
2526 open c2(p_po_release_id);
2527 fetch c2 into xGrpId,xRevNum;
2528 close c2;
2529 else
2530 open c1(p_po_header_id);
2531 fetch c1 into xGrpId,xRevNum;
2532 close c1;
2533 end if;
2534
2535 if (xGrpId > 0) then
2536 l_cancel_msg := fnd_message.get_string('POS','POS_AUTO_CANCEL_BY_BUYER') ;
2537 if (p_po_release_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2538 begin
2539 update po_change_requests
2540 set request_status = 'REJECTED',change_active_flag = 'N',
2541 request_reason=l_cancel_msg
2542 where po_release_id = p_po_release_id and
2543 initiator = 'SUPPLIER' and
2544 request_status not in ('ACCEPTED','REJECTED') and
2545 action_type='MODIFICATION';
2546
2547 update po_change_requests
2548 set request_status = 'ACCEPTED',
2549 change_active_flag = 'N'
2550 where initiator = 'SUPPLIER' and
2551 request_status not in ('ACCEPTED','REJECTED') and
2552 action_type='CANCELLATION' and
2553 po_release_id = p_po_release_id;
2554 exception
2555 when no_data_found then
2556 null;
2557 end;
2558 end if;
2559
2560 if (p_po_header_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2561 begin
2562 update po_change_requests
2563 set request_status = 'REJECTED',
2564 change_active_flag = 'N',
2565 request_reason=l_cancel_msg
2566 where document_header_id = p_po_header_id and
2567 request_status not in ('ACCEPTED','REJECTED') and
2568 initiator = 'SUPPLIER' and
2569 action_type='MODIFICATION';
2570
2571 update po_change_requests
2572 set request_status = 'ACCEPTED',
2573 change_active_flag = 'N'
2574 where request_status not in ('ACCEPTED','REJECTED') and
2575 initiator = 'SUPPLIER' and
2576 action_type='CANCELLATION' and
2577 document_header_id = p_po_header_id ;
2578 exception
2579 when no_data_found then
2580 null;
2581 end;
2582 end if;
2583
2584 if (p_po_line_location_id is not null ) then
2585 begin
2586 update po_change_requests
2587 set request_status = 'REJECTED',change_active_flag = 'N',request_reason=l_cancel_msg
2588 where document_line_location_id = p_po_line_location_id and
2589 request_level = 'SHIPMENT' and
2590 request_status not in ('ACCEPTED','REJECTED') and
2591 initiator = 'SUPPLIER' and
2592 action_type='MODIFICATION';
2593
2594 update po_change_requests
2595 set request_status = 'ACCEPTED',change_active_flag = 'N'
2596 where document_line_location_id = p_po_line_location_id and
2597 request_level = 'SHIPMENT' and
2598 request_status not in ('ACCEPTED','REJECTED') and
2599 initiator = 'SUPPLIER' and
2600 action_type='CANCELLATION';
2601
2602 exception
2603 when no_data_found then
2604 null;
2605 end;
2606 end if;
2607
2608 if (p_po_line_id is not null and p_po_line_location_id is null ) then
2609 begin
2610 update po_change_requests
2611 set request_status='REJECTED',change_active_flag='N',request_reason=l_cancel_msg
2612 where document_line_id = p_po_line_id and
2613 request_status not in ('ACCEPTED','REJECTED') and
2614 initiator = 'SUPPLIER' and
2615 action_type='MODIFICATION';
2616
2617 update po_change_requests
2618 set request_status='ACCEPTED',change_active_flag='N'
2619 where document_line_id = p_po_line_id and
2620 request_status not in ('ACCEPTED','REJECTED') and
2621 initiator = 'SUPPLIER' and
2622 action_type='CANCELLATION';
2623
2624 exception
2625 when no_data_found then
2626 null;
2627 end;
2628 end if;
2629
2630 -- reset document status to approved if there are no more changes pending by supplier
2631
2632 if (p_po_release_id is not null) then
2633 open c2(p_po_release_id);
2634 fetch c2 into lGrpId,lRevNum;
2635 close c2;
2636 if (lGrpId is null) then
2637
2638 update po_releases_all set
2639 authorization_status = 'APPROVED',
2640 CHANGE_REQUESTED_BY = null,
2641 revised_date = sysdate,
2642 last_update_date = sysdate,
2643 last_updated_by = fnd_global.user_id,
2644 last_update_login = fnd_global.login_id,
2645 request_id = fnd_global.conc_request_id,
2646 program_application_id = fnd_global.prog_appl_id,
2647 program_id = fnd_global.conc_program_id,
2648 program_update_date = sysdate
2649 where po_release_id = p_po_release_id;
2650 -- Update all the change requests with current revision number
2651 else
2652 begin
2653 select revision_num
2654 into l_revision_num
2655 from po_releases_all
2656 where po_release_id = p_po_release_id;
2657 exception
2658 when others then
2659 raise;
2660 end;
2661 update po_change_requests
2662 set document_revision_num = l_revision_num
2663 where po_release_id = p_po_release_id and
2664 request_status not in ('ACCEPTED','REJECTED') and
2665 document_type = 'RELEASE' and
2666 change_active_flag= 'Y' and
2667 initiator = 'SUPPLIER' ;
2668 end if;
2669 else
2670 open c1(p_po_header_id);
2671 fetch c1 into lGrpId,lRevNum;
2672 close c1;
2673 if (lGrpId is null) then
2674
2675 update po_headers_all set
2676 authorization_status = 'APPROVED',
2677 CHANGE_REQUESTED_BY = null,
2678 last_update_date = sysdate,
2679 last_updated_by = fnd_global.user_id,
2680 last_update_login = fnd_global.login_id,
2681 request_id = fnd_global.conc_request_id,
2682 program_application_id = fnd_global.prog_appl_id,
2683 program_id = fnd_global.conc_program_id,
2684 program_update_date = sysdate
2685 where po_header_id = p_po_header_id;
2686 -- Update all the change requests with current revision number
2687 else
2688
2689 begin
2690 select revision_num
2691 into l_revision_num
2692 from po_headers_all
2693 where po_header_id = p_po_header_id;
2694 exception
2695 when others then
2696 raise;
2697 end;
2698 update po_change_requests
2699 set document_revision_num=l_revision_num
2700 where document_header_id = p_po_header_id and
2701 request_status not in ('ACCEPTED','REJECTED') and
2702 document_type = 'PO' and
2703 change_active_flag= 'Y' and
2704 initiator = 'SUPPLIER' ;
2705 end if;
2706 end if;
2707 -- Call process Response to send notification if there are no more changes
2708 -- in change request table
2709
2710 if (lGrpId is null) then
2711 po_sup_chg_request_wf_grp.Buyer_CancelDocWithChn(
2712 1.0,l_return_status,p_po_header_id,p_po_release_id,xRevNum,xGrpId,l_msg_out);
2713 if (l_return_status <> 'S') then
2714 IF (g_fnd_debug = 'Y') THEN
2715 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2716 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2717 l_api_name || '.call buyer cancel workflow', l_msg_out);
2718 END IF;
2719 END IF;
2720 end if;
2721 end if; -- if lGrpId is null
2722 end if; -- if xGrpId > 0
2723
2724
2725
2726 EXCEPTION
2727 WHEN FND_API.g_exc_error THEN
2728 x_return_status := FND_API.g_ret_sts_error;
2729 WHEN FND_API.g_exc_unexpected_error THEN
2730 x_return_status := FND_API.g_ret_sts_unexp_error;
2731 WHEN OTHERS THEN
2732 x_return_status := FND_API.g_ret_sts_unexp_error;
2733 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2734 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2735 IF (g_fnd_debug = 'Y') THEN
2736 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2737 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2738 l_api_name || '.others_exception', 'Exception');
2739 END IF;
2740 END IF;
2741 END IF;
2742 END cancel_change_request;
2743
2744 procedure process_supplier_signature (
2745 p_api_version IN NUMBER,
2746 p_Init_Msg_List IN VARCHAR2,
2747 x_return_status OUT NOCOPY VARCHAR2,
2748 x_notification_id OUT NOCOPY NUMBER,
2749 p_po_header_id IN number,
2750 p_revision_num IN number,
2751 p_document_subtype IN VARCHAR2,
2752 p_document_number IN VARCHAR2,
2753 p_org_id IN NUMBER,
2754 p_Agent_Id IN NUMBER,
2755 p_supplier_user_id IN number)
2756 IS
2757
2758 l_api_version_number CONSTANT NUMBER := 1.0;
2759 l_api_name CONSTANT VARCHAR2(30) := 'process_supplier_signature';
2760 l_item_key WF_ITEMS.item_key%TYPE := NULL;
2761 l_item_type WF_ITEMS.item_type%TYPE;
2762 x_result VARCHAR2(20);
2763 x_sup_user_id NUMBER;
2764 l_supplier_username fnd_user.user_name%type;
2765 sig_notif_notfound exception;
2766 l_document_type VARCHAR2(20);
2767 l_msg_count NUMBER;
2768 l_msg_data VARCHAR2(2000);
2769 l_ret_status VARCHAR2(20);
2770 BEGIN
2771
2772 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2773 -- initialize message list
2774 FND_MSG_PUB.initialize;
2775 END IF;
2776
2777 -- Standard call to check for call compatibility.
2778
2779 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2780 p_api_version,
2781 l_api_name,
2782 G_PKG_NAME)
2783 THEN
2784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2785 END IF;
2786
2787 -- initialize return status
2788 x_return_status := FND_API.G_RET_STS_SUCCESS;
2789 if (p_document_subtype in ('STANDARD','PLANNED')) then
2790 l_document_type := 'PO';
2791
2792 elsif (p_document_subtype in ('BLANKET','CONTRACT')) then
2793 l_document_type := 'PA';
2794 end if;
2795
2796 select wf_item_type
2797 into l_item_type
2798 from po_headers_all
2799 WHERE po_header_id = p_po_header_id;
2800
2801
2802 -- First Find the Item Key for this Document if it were ever generated
2803 BEGIN
2804 PO_SIGNATURE_GRP.Find_Item_Key(
2805 p_api_version => 1.0,
2806 p_init_msg_list => FND_API.G_FALSE,
2807 p_po_header_id => p_po_header_id,
2808 p_revision_num => p_revision_num ,
2809 p_document_type => l_document_type ,
2810 x_itemkey => l_item_key,
2811 x_result => x_result,
2812 x_return_status => l_ret_status,
2813 x_msg_count => l_msg_count,
2814 x_msg_data => l_msg_data );
2815 END;
2816
2817
2818
2819 -- To create Item key for the Document Signature Process
2820 IF (l_item_key is null) then
2821 BEGIN
2822
2823 PO_SIGNATURE_GRP.Get_Item_Key(
2824 p_api_version => 1.0,
2825 p_init_msg_list => FND_API.G_FALSE,
2826 p_po_header_id => p_po_header_id,
2827 p_revision_num => p_revision_num ,
2828 p_document_type => l_document_type ,
2829 x_itemkey => l_item_key,
2830 x_result => x_result,
2831 x_return_status => l_ret_status,
2832 x_msg_count => l_msg_count,
2833 x_msg_data => l_msg_data );
2834
2835 END;
2836
2837
2838 -- Start Signature Workflow and pass the Newly generated Item Key
2839 -- Create a Workflow Process
2840
2841 x_return_status := startSignatureWF (
2842 l_item_type,
2843 l_item_key ,
2844 p_po_header_id ,
2845 p_revision_num ,
2846 l_document_type,
2847 p_document_subtype,
2848 p_document_number,
2849 p_org_id ,
2850 p_Agent_Id ,
2851 p_supplier_user_id ) ;
2852
2853 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2854 x_notification_id := getSigNotifId(l_item_type, l_item_key);
2855 if x_notification_id is null then
2856 RAISE sig_notif_notfound;
2857 end if;
2858 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2859 FND_LOG.string(FND_LOG.level_unexpected, l_api_name ||
2860 l_item_key || '.Notification :' || to_char(x_notification_id) ,sqlcode);
2861 END IF;
2862
2863 return;
2864 end if;
2865
2866
2867 ELSE
2868
2869 -- Find the Notification generated for the given Item Key
2870 -- Compare the User Id with the Workflow Invoker's User Id
2871 x_sup_user_id := wf_engine.GetItemAttrNumber (itemtype => l_item_type,
2872 itemkey => l_item_key,
2873 aname => 'SUPPLIER_USER_ID');
2874
2875 if (x_sup_user_id = p_supplier_user_id) then
2876 -- get the signature notification for the item key
2877 x_notification_id := getSigNotifId(l_item_type, l_item_key);
2878 if (x_notification_id is null) then
2879 RAISE sig_notif_notfound;
2880 end if;
2881 return;
2882 else
2883 -- If the Notification was not generated for the same user the
2884 -- Abort the previous process and start a new one
2885 BEGIN
2886 PO_SIGNATURE_GRP.Abort_Doc_Sign_Process(
2887 p_api_version => 1.0,
2888 p_init_msg_list => FND_API.G_FALSE,
2889 p_itemkey => l_item_key,
2890 x_result => x_result,
2891 x_return_status => l_ret_status,
2892 x_msg_count => l_msg_count,
2893 x_msg_data => l_msg_data );
2894
2895 END;
2896 -- Generate new Item Key
2897 BEGIN
2898
2899 PO_SIGNATURE_GRP.Get_Item_Key(
2900 p_api_version => 1.0,
2901 p_init_msg_list => FND_API.G_FALSE,
2902 p_po_header_id => p_po_header_id,
2903 p_revision_num => p_revision_num ,
2904 p_document_type => l_document_type ,
2905 x_itemkey => l_item_key,
2906 x_result => x_result,
2907 x_return_status => l_ret_status,
2908 x_msg_count => l_msg_count,
2909 x_msg_data => l_msg_data );
2910 END;
2911
2912 -- Start Signature Workflow and pass the Newly generated Item Key
2913 -- Create a Workflow Process
2914
2915 x_return_status := startSignatureWF (
2916 l_item_type,
2917 l_item_key ,
2918 p_po_header_id ,
2919 p_revision_num ,
2920 l_document_type,
2921 p_document_subtype,
2922 p_document_number,
2923 p_org_id ,
2924 p_Agent_Id ,
2925 p_supplier_user_id ) ;
2926
2927 if (x_return_status = 'S') then
2928 x_notification_id := getSigNotifId(l_item_type, l_item_key);
2929 if (x_notification_id is null) then
2930 RAISE sig_notif_notfound;
2931 end if;
2932
2933 return;
2934 end if;
2935
2936 end if;
2937 END IF;
2938
2939 EXCEPTION
2940 WHEN FND_API.g_exc_error THEN
2941 x_return_status := FND_API.g_ret_sts_error;
2942 WHEN FND_API.g_exc_unexpected_error THEN
2943 x_return_status := FND_API.g_ret_sts_unexp_error;
2944 WHEN sig_notif_notfound THEN
2945 IF g_fnd_debug = 'Y' THEN
2946 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2947 FND_LOG.string(FND_LOG.level_unexpected, l_api_name ||
2948 l_item_key || '.Notification not found exception' ,sqlcode);
2949 END IF;
2950 END IF;
2951 x_return_status := FND_API.g_ret_sts_error;
2952 WHEN OTHERS THEN
2953 x_return_status := FND_API.g_ret_sts_unexp_error;
2954 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2955 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2956 IF g_fnd_debug = 'Y' THEN
2957 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2958 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2959 l_api_name || '.others exception' ,sqlcode);
2960 END IF;
2961 END IF;
2962 END IF;
2963 END process_supplier_signature;
2964
2965 function create_pos_change_rec (
2966 p_Action_Type IN VARCHAR2, --(30),
2967 p_Initiator IN VARCHAR2, --(30),
2968 p_Document_Type IN VARCHAR2, --(30),
2969 p_Request_Level IN VARCHAR2, --(30),
2970 p_Request_Status IN VARCHAR2, --(30),
2971 p_Document_Header_Id IN NUMBER,
2972 p_Request_Reason IN VARCHAR2 default null, --(2000),
2973 p_PO_Release_Id IN NUMBER default null,
2974 p_Document_Num IN VARCHAR2 default null, --(20),
2975 p_Document_Revision_Num IN NUMBER default null,
2976 p_Document_Line_Id IN NUMBER default null,
2977 p_Document_Line_Number IN NUMBER default null,
2978 p_Document_Line_Location_Id IN NUMBER default null,
2979 p_Document_Shipment_Number IN NUMBER default null,
2980 p_Document_Distribution_id IN NUMBER default null,
2981 p_Document_Distribution_Number IN NUMBER default null,
2982 p_Parent_Line_Location_Id IN NUMBER default null,
2983 p_Old_Quantity IN NUMBER default null,
2984 p_New_Quantity IN NUMBER default null,
2985 p_Old_Promised_Date IN DATE default null,
2986 p_New_Promised_Date IN DATE default null,
2987 p_Old_Supplier_Part_Number IN VARCHAR2 default null, --(25),
2988 p_New_Supplier_Part_Number IN VARCHAR2 default null, --(25),
2989 p_Old_Price IN NUMBER default null,
2990 p_New_Price IN NUMBER default null,
2991 p_Old_Supplier_Reference_Num IN VARCHAR2 default null, --(30),
2992 p_New_Supplier_Reference_Num IN VARCHAR2 default null, --(30),
2993 p_From_Header_id IN NUMBER default null,
2994 p_Recoverable_Tax IN NUMBER default null,
2995 p_Non_recoverable_tax IN NUMBER default null,
2996 p_Ship_To_Location_id IN NUMBER default null,
2997 p_Ship_To_Organization_Id IN NUMBER default null,
2998 p_Old_Need_By_Date IN DATE default null,
2999 p_New_Need_By_Date IN DATE default null,
3000 p_Approval_Required_Flag IN VARCHAR2 default null, --(1),
3001 p_Parent_Change_request_Id IN NUMBER default null,
3002 p_Requester_id IN NUMBER default null,
3003 p_Old_Supplier_Order_Number IN VARCHAR2 default null, --(25),
3004 p_New_Supplier_Order_Number IN VARCHAR2 default null, --(25),
3005 p_Old_Supplier_Order_Line_Num IN VARCHAR2 default null, --(25),
3006 p_New_Supplier_Order_Line_Num IN VARCHAR2 default null , --(25),
3007 p_Additional_changes IN VARCHAR2 default null, --(2000),
3008 p_old_Start_date IN DATE default null,
3009 p_new_Start_date IN DATE default null,
3010 p_old_Expiration_date IN DATE default null,
3011 p_new_Expiration_date IN DATE default null,
3012 p_old_Amount IN NUMBER default null,
3013 p_new_Amount IN NUMBER default null,
3014 p_SUPPLIER_DOC_REF IN varchar2 default null, --(256),
3015 p_SUPPLIER_LINE_REF IN varchar2 default null, --(256),
3016 p_SUPPLIER_SHIPMENT_REF IN varchar2 default null, --(256)
3017 --<< Complex work changes for R12 >>
3018 p_NEW_PROGRESS_TYPE IN varchar2 default null,
3019 p_NEW_PAY_DESCRIPTION IN varchar2 default null
3020
3021 ) return pos_chg_rec
3022 is
3023
3024 begin
3025 return pos_chg_rec(
3026 Action_Type => p_Action_Type,
3027 Initiator => p_Initiator,
3028 Request_Reason => p_Request_Reason,
3029 Document_Type => p_Document_Type,
3030 Request_Level => p_Request_Level,
3031 Request_Status => p_Request_Status,
3032 Document_Header_Id => p_Document_Header_Id,
3033 PO_Release_Id => p_PO_Release_Id,
3034 Document_Num => p_Document_Num,
3035 Document_Revision_Num => p_Document_Revision_Num,
3036 Document_Line_Id => p_Document_Line_Id,
3037 Document_Line_Number => p_Document_Line_Number,
3038 Document_Line_Location_Id => p_Document_Line_Location_Id,
3039 Document_Shipment_Number => p_Document_Shipment_Number,
3040 Document_Distribution_id => p_Document_Distribution_id,
3041 Document_Distribution_Number => p_Document_Distribution_Number,
3042 Parent_Line_Location_Id => p_Parent_Line_Location_Id,
3043 Old_Quantity => p_Old_Quantity,
3044 New_Quantity => p_New_Quantity,
3045 Old_Promised_Date => p_Old_Promised_Date,
3046 New_Promised_Date => p_New_Promised_Date,
3047 Old_Supplier_Part_Number => p_Old_Supplier_Part_Number,
3048 New_Supplier_Part_Number => p_New_Supplier_Part_Number,
3049 Old_Price => p_Old_Price,
3050 New_Price => p_New_Price,
3051 Old_Supplier_Reference_Number => p_Old_Supplier_Reference_Num,
3052 New_Supplier_Reference_Number => p_New_Supplier_Reference_Num,
3053 From_Header_id => p_From_Header_id,
3054 Recoverable_Tax => p_Recoverable_Tax,
3055 Non_recoverable_tax => p_Non_recoverable_tax,
3056 Ship_To_Location_id => p_Ship_To_Location_id,
3057 Ship_To_Organization_Id => p_Ship_To_Organization_Id,
3058 Old_Need_By_Date => p_Old_Need_By_Date,
3059 New_Need_By_Date => p_New_Need_By_Date,
3060 Approval_Required_Flag => p_Approval_Required_Flag,
3061 Parent_Change_request_Id => p_Parent_Change_request_Id,
3062 Requester_id => p_Requester_id,
3063 Old_Supplier_Order_Number => p_Old_Supplier_Order_Number,
3064 New_Supplier_Order_Number => p_New_Supplier_Order_Number,
3065 Old_Supplier_Order_Line_Number => p_Old_Supplier_Order_Line_Num,
3066 New_Supplier_Order_Line_Number => p_New_Supplier_Order_Line_Num,
3067 Additional_changes => p_Additional_changes,
3068 old_Start_date => p_old_Start_date,
3069 new_Start_date => p_new_Start_date,
3070 old_Expiration_date => p_old_Expiration_date,
3071 new_Expiration_date => p_new_Expiration_date,
3072 old_Amount => p_old_Amount,
3073 new_Amount => p_new_Amount,
3074 SUPPLIER_DOC_REF => p_SUPPLIER_DOC_REF,
3075 SUPPLIER_LINE_REF => p_SUPPLIER_LINE_REF,
3076 SUPPLIER_SHIPMENT_REF => p_SUPPLIER_SHIPMENT_REF ,
3077 --<< Complex work changes for R12 >>
3078 NEW_PROGRESS_TYPE =>p_NEW_PROGRESS_TYPE,
3079 NEW_PAY_DESCRIPTION =>p_NEW_PAY_DESCRIPTION
3080
3081
3082 );
3083
3084 end;
3085
3086 /*
3087 * Function to get maximum shipment number for a given po_line_id
3088 */
3089 function getMaxShipmentNum (
3090 p_po_line_id IN NUMBER)
3091 return NUMBER IS
3092
3093 v_ship_num NUMBER;
3094 v_progress varchar2(3);
3095
3096 BEGIN
3097
3098 v_progress := '111';
3099
3100 select max(shipment_num)
3101 into v_ship_num
3102 from po_line_locations_All
3103 where po_line_id = p_po_line_id
3104 group by po_line_id;
3105
3106 RETURN v_ship_num;
3107
3108 EXCEPTION
3109 WHEN others THEN
3110 PO_MESSAGE_S.SQL_ERROR(
3111 'PO_CHG_REQUEST_PVT.getMaxShipmentNum',
3112 v_progress,
3113 sqlcode );
3114
3115 RETURN -1;
3116
3117 END;
3118
3119 function getLastUpdateDate (
3120 p_header_id IN NUMBER,
3121 p_release_id in NUMBER)
3122 return DATE IS
3123
3124 p_last_update_date DATE;
3125 v_progress varchar2(3);
3126
3127 BEGIN
3128
3129 v_progress := '113';
3130
3131 if (p_header_id is null) then
3132
3133 select last_update_Date
3134 into p_last_update_date
3135 from po_releases_All where
3136 po_release_id = p_release_id
3137 and rownum=1;
3138
3139 else
3140
3141 select last_update_Date
3142 into p_last_update_date
3143 from po_headers_All
3144 where po_header_id = p_header_id
3145 and rownum=1;
3146
3147 end if;
3148
3149 return p_last_update_Date;
3150
3151 EXCEPTION
3152 WHEN others THEN
3153 PO_MESSAGE_S.SQL_ERROR(
3154 'PO_CHG_REQUEST_PVT.getLastUpdateDate',
3155 v_progress,
3156 sqlcode );
3157
3158 return null;
3159
3160 END;
3161
3162 procedure validate_shipment_cancel (
3163 p_po_header_id IN number,
3164 p_po_change_requests IN pos_chg_rec_tbl,
3165 x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3166 x_ret_sts OUT NOCOPY varchar2
3167 ) IS
3168
3169 l_po_change_requests pos_chg_rec_tbl := NULL;
3170 l_count_asn NUMBER;
3171 l_err_msg_name_tbl po_tbl_varchar30;
3172 l_err_msg_text_tbl po_tbl_varchar2000;
3173 l_err_count NUMBER;
3174
3175 BEGIN
3176 l_err_count := 0;
3177 l_po_change_requests := p_po_change_requests ;
3178 l_err_msg_name_tbl := po_tbl_varchar30();
3179 l_err_msg_text_tbl := po_tbl_varchar2000();
3180 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3181 x_ret_sts := 'N';
3182 FOR j in 1..l_po_change_requests.count()
3183 LOOP
3184 if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3185 -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3186 select count(*)
3187 into l_count_asn
3188 from RCV_TRANSACTIONS_INTERFACE rti
3189 where rti.TRANSACTION_TYPE = 'SHIP' and
3190 rti.PROCESSING_STATUS_CODE = 'PENDING' and
3191 rti.quantity > 0 and
3192 rti.PO_HEADER_ID = p_po_header_id and
3193 (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);
3194 IF(l_count_asn > 0) then
3195 l_err_count := l_err_count + 1;
3196 x_pos_errors.message_name.extend;
3197 x_pos_errors.text_line.extend;
3198 x_pos_errors.message_name(l_err_count) := null;
3199 if l_po_change_requests(j).document_line_location_id is not null then
3200 FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3201 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3202 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3203 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3204 else
3205 x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_UNPRC_TX');
3206 return;
3207 END IF;
3208 END IF;
3209
3210 SELECT count(*)
3211 into l_count_asn
3212 FROM RCV_SHIPMENT_LINES RSL
3213 WHERE RSL.po_header_id = p_po_header_id
3214 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)
3215 AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
3216 AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
3217 AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
3218 IF(l_count_asn > 0) then
3219 l_err_count := l_err_count + 1;
3220 x_pos_errors.message_name.extend;
3221 x_pos_errors.text_line.extend;
3222 x_pos_errors.message_name(l_err_count) := null;
3223 if l_po_change_requests(j).document_line_location_id is not null then
3224 FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3225 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3226 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3227 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3228 else
3229 x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_OPEN_ASN') ;
3230 return;
3231 END IF;
3232 END IF;
3233 END IF;
3234 END LOOP;
3235 if(l_err_count < 1) then
3236 validate_ship_inv_cancel (
3237 p_po_header_id,
3238 p_po_change_requests,
3239 x_pos_errors,
3240 x_ret_sts);
3241 else
3242 x_ret_sts := 'Y';
3243 end if;
3244
3245 END validate_shipment_cancel;
3246
3247 procedure validate_ship_inv_cancel (
3248 p_po_header_id IN number,
3249 p_po_change_requests IN pos_chg_rec_tbl,
3250 x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3251 x_ret_sts OUT NOCOPY varchar2
3252 ) IS
3253
3254 l_po_change_requests pos_chg_rec_tbl := NULL;
3255 l_count_asn NUMBER;
3256 l_err_msg_name_tbl po_tbl_varchar30;
3257 l_err_msg_text_tbl po_tbl_varchar2000;
3258 l_err_count NUMBER;
3259 l_quan_ordered NUMBER;
3260 l_quan_recd NUMBER;
3261 l_quan_billed NUMBER;
3262 BEGIN
3263 l_err_count := 0;
3264 l_po_change_requests := p_po_change_requests ;
3265 l_err_msg_name_tbl := po_tbl_varchar30();
3266 l_err_msg_text_tbl := po_tbl_varchar2000();
3267 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3268 FOR j in 1..l_po_change_requests.count()
3269 LOOP
3270 if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3271 l_po_change_requests(j).request_level='SHIPMENT' ) then
3272 l_quan_ordered := -1;
3273 l_quan_recd := -1;
3274 l_quan_billed := -1;
3275 begin
3276 SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3277 into l_quan_ordered, l_quan_billed, l_quan_recd
3278 FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3279 WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3280 AND POLL.po_line_id = POL.po_line_id
3281 AND nvl(POLL.cancel_flag, 'N') = 'N'
3282 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3283 AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3284 AND nvl(POLL.quantity_billed, 0) > nvl(POLL.quantity_received,0);
3285 EXCEPTION
3286 WHEN OTHERS THEN
3287 l_quan_ordered := -1;
3288 END;
3289
3290 if (l_quan_ordered > -1 ) then
3291 l_err_count := l_err_count + 1;
3292 x_pos_errors.message_name.extend;
3293 x_pos_errors.text_line.extend;
3294 x_pos_errors.message_name(l_err_count) := null;
3295 FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_RCV');
3296 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3297 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3298 fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3299 fnd_message.set_token('QTY_RCV', l_quan_recd) ;
3300 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3301 end if;
3302
3303 l_quan_ordered := -1;
3304 l_quan_recd := -1;
3305 l_quan_billed := -1;
3306 begin
3307 SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3308 into l_quan_ordered, l_quan_billed, l_quan_recd
3309 FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3310 WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3311 AND POLL.po_line_id = POL.po_line_id
3312 AND nvl(POLL.cancel_flag, 'N') = 'N'
3313 AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3314 AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3315 AND nvl(POLL.quantity_billed, 0) > nvl(POLL.quantity,0);
3316 EXCEPTION
3317 WHEN OTHERS THEN
3318 l_quan_ordered := -1;
3319 END;
3320 if (l_quan_ordered > -1 ) then
3321 l_err_count := l_err_count + 1;
3322 x_pos_errors.message_name.extend;
3323 x_pos_errors.text_line.extend;
3324 x_pos_errors.message_name(l_err_count) := null;
3325 FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_ORD');
3326 fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3327 fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3328 fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3329 fnd_message.set_token('QTY_ORD', l_quan_ordered) ;
3330 x_pos_errors.text_line(l_err_count) := fnd_message.get;
3331 end if;
3332 END IF;
3333 END LOOP;
3334 if(l_err_count < 1) then
3335 x_ret_sts := 'N';
3336 else
3337 x_ret_sts := 'Y';
3338 end if ;
3339
3340 END validate_ship_inv_cancel;
3341
3342 END PO_CHG_REQUEST_PVT;