1 PACKAGE BODY PO_CHANGE_RESPONSE_PVT AS
2 /* $Header: POXCHREB.pls 120.10.12020000.2 2013/02/10 21:35:58 vegajula ship $*/
3
4 /*Initializing Private Procedures-Functions
5 */
6
7 /**
8 * Private Procedure: CreateSingleDistributionRecords
9 * Effects: Creates a distribution record in PO_CHANGE_REQUESTS for each
10 * shipment change where the shipment has a single distribution.
11 * Note: Normally, distribution records are created by the
12 * "Respond to Changes" page. However, for the single distribution case,
13 * we allow users to respond through a notification, so we need to create
14 * the distribution records here.
15 * Modifies: Creates distribution records in PO_CHANGE_REQUESTS for any
16 * changes to shipments with one distribution.
17 **/
18
19 PROCEDURE CreateSingleDistribution (
20 p_change_request_group_id IN NUMBER
21 ) IS
22
23 CURSOR single_dist_shipments_csr IS
24 SELECT change_request_id
25 FROM po_change_requests PCR
26 WHERE request_status = 'BUYER_APP'
27 AND change_request_group_id = p_change_request_group_id
28 AND request_level = 'SHIPMENT'
29 AND initiator = 'SUPPLIER'
30 AND 1 = (SELECT count(*)
31 FROM po_distributions POD
32 WHERE POD.line_location_id =
33 NVL(PCR.document_line_location_id, PCR.parent_line_location_id));
34
35 -- there is only one distribution for this shipment
36
37 l_change_request_id NUMBER;
38
39 BEGIN
40
41 OPEN single_dist_shipments_csr;
42 LOOP
43 FETCH single_dist_shipments_csr INTO l_change_request_id;
44 EXIT WHEN single_dist_shipments_csr%NOTFOUND;
45
46 -- Create a distribution CR record by copying most of the fields from
47 -- the shipment record.
48 INSERT INTO po_change_requests
49 ( Change_Request_Group_Id, Change_Request_Id, Initiator, Action_Type, Request_Reason,
50 Document_Type, Request_Level,
51 Request_Status, Document_Header_Id, Document_Num, Document_Revision_Num, Created_By, Creation_Date,
52 Last_Updated_By, Last_Update_Date, Last_Update_Login, Vendor_Id, Vendor_Site_Id, Vendor_Contact_Id,
53 Request_Expiration_Date, PO_Release_Id, Document_Line_Id, Document_Line_Number, Document_Line_Location_Id,
54 Document_Shipment_Number, Parent_Line_Location_Id, Document_distribution_id, Document_distribution_Number,
55 Old_Quantity, New_Quantity, Old_Promised_Date, New_Promised_Date,
56 Old_Supplier_Part_Number, New_Supplier_Part_Number, Old_Price, New_Price,
57 Old_Need_By_Date, New_Need_By_Date,
58 Old_Supplier_Reference_Number, New_Supplier_Reference_Number,
59 Requester_Id, Responded_by, Response_date, Response_Reason,
60 Old_Currency_Unit_Price, New_Currency_Unit_Price,
61 Recoverable_Tax, Nonrecoverable_Tax,
62 WF_Item_type, WF_Item_key, Parent_Change_Request_Id, Validation_Error,
63 Approval_Required_Flag, Old_Supplier_Order_Number, New_Supplier_Order_Number,
64 Old_Supplier_Order_Line_Number, New_Supplier_Order_Line_Number,Old_Amount,New_Amount) -- Added Amount for FPS Changes
65 SELECT
66 PCR.Change_Request_Group_Id,
67 PO_CHG_REQUEST_SEQ.nextval, -- CHANGE_REQUEST_ID
68 PCR.Initiator, PCR.Action_Type, PCR.Request_Reason,
69 PCR.Document_Type,
70 'DISTRIBUTION', -- REQUEST_LEVEL
71 PCR.Request_Status, PCR.Document_Header_Id, PCR.Document_Num,
72 PCR.Document_Revision_Num, PCR.Created_By, PCR.Creation_Date,
73 PCR.Last_Updated_By, PCR.Last_Update_Date, PCR.Last_Update_Login,
74 PCR.Vendor_Id, PCR.Vendor_Site_Id, PCR.Vendor_Contact_Id,
75 PCR.Request_Expiration_Date, PCR.PO_Release_Id, PCR.Document_Line_Id,
76 PCR.Document_Line_Number, PCR.Document_Line_Location_Id,
77 PCR.Document_Shipment_Number, PCR.Parent_Line_Location_Id,
78 Decode(PCR.Parent_Line_Location_Id, null, POD.po_distribution_id, null),
79 POD.distribution_num,
80 PCR.Old_Quantity, PCR.New_Quantity, PCR.Old_Promised_Date, PCR.New_Promised_Date,
81 PCR.Old_Supplier_Part_Number, PCR.New_Supplier_Part_Number,
82 PCR.Old_Price, PCR.New_Price,
83 PCR.Old_Need_By_Date, PCR.New_Need_By_Date,
84 PCR.Old_Supplier_Reference_Number, PCR.New_Supplier_Reference_Number,
85 PCR.Requester_Id, PCR.Responded_by, PCR.Response_date, PCR.Response_Reason,
86 PCR.Old_Currency_Unit_Price, PCR.New_Currency_Unit_Price,
87 PCR.Recoverable_Tax, PCR.Nonrecoverable_Tax,
88 PCR.WF_Item_type, PCR.WF_Item_key, PCR.Parent_Change_Request_Id,
89 PCR.Validation_Error, PCR.Approval_Required_Flag,
90 PCR.Old_Supplier_Order_Number, PCR.New_Supplier_Order_Number,
91 PCR.Old_Supplier_Order_Line_Number, PCR.New_Supplier_Order_Line_Number,PCR.Old_Amount,PCR.New_Amount
92 FROM po_change_requests PCR, po_distributions_all POD
93 WHERE PCR.change_request_id = l_change_request_id
94 AND POD.line_location_id =
95 NVL(PCR.document_line_location_id, PCR.parent_line_location_id);
96
97 END LOOP;
98 CLOSE single_dist_shipments_csr;
99
100 END CreateSingleDistribution;
101
102
103 /** CheckPartialAck
104 * ----------------
105 * Purpose:
106 * This procedure is used to determine whether the PO is in partial ack state.
107 * The PO is said to be NOT in partial ack state
108 * when either the PO does not require acknowledgement or all the shipments have been either acked or changed
109 * Usage:
110 * This procedure is called in following scenarios -
111 * 1. To determine that the supplier is acting on the PO and the buyer cannot currently act on it
112 * Return Value:
113 * This procedure returns p_partial_ack which has a value of 'Y' if the PO is in a partial ack state
114 * and 'N' if the PO is not in a partial ack state
115 */
116
117 PROCEDURE CheckPartialAck (p_api_version in number,
118 x_return_status out NOCOPY varchar2,
119 p_po_header_id in number,
120 p_po_release_id in number,
121 p_change_request_group_id in number,
122 x_partial_ack out NOCOPY varchar2) is
123
124 l_acceptance_required VARCHAR2(1);
125 l_authorization_status VARCHAR2(20);
126 l_change_requested_by VARCHAR2(20);
127 l_revision_num NUMBER;
128 l_change_shipment_count NUMBER;
129 l_acc_shipment_count NUMBER;
130 l_total_shipment_count NUMBER;
131 l_all_ack VARCHAR2(1);
132
133 BEGIN
134
135 -- determine if the PO needs acknowledgement, is 'in process' and the change_requested_by is 'SUPPLIER'
136
137 if (p_po_release_id is null) then
138
139 select acceptance_required_flag,
140 authorization_status,
141 change_requested_by,
142 revision_num
143 into l_acceptance_required,
144 l_authorization_status,
145 l_change_requested_by,
146 l_revision_num
147 from po_headers_all
148 where po_header_id = p_po_header_id;
149
150 else
151
152 select acceptance_required_flag,
153 authorization_status,
154 change_requested_by,
155 revision_num
156 into l_acceptance_required,
157 l_authorization_status,
158 l_change_requested_by,
159 l_revision_num
160 from po_releases_all
161 where po_release_id = p_po_release_id;
162
163 end if;
164
165 if ((l_acceptance_required = 'Y') AND (l_authorization_status = 'IN PROCESS')
166 AND (l_change_requested_by = 'SUPPLIER')) then
167
168 --bug 4107241
169 --buyer should not be able to ack PO's which are partially acknowledged
170 --this code block replaces commented code
171
172 l_all_ack := PO_ACKNOWLEDGE_PO_PVT.All_Shipments_Responded(1.0, FND_API.G_FALSE,
173 p_po_header_id,p_po_release_id,l_revision_num );
174
175 if(l_all_ack = FND_API.G_TRUE) then
176 x_partial_ack := 'N';
177 else
178 x_partial_ack := 'Y';
179 end if;
180
181 /*
182
183 -- select count of shipments of the latest revision from po_acceptances which have been acked
184
185 if (p_po_release_id is null) then
186
187 select count(*) into l_acc_shipment_count
188 from po_acceptances
189 where po_header_id = p_po_header_id
190 and po_line_location_id is not null
191 and revision_num = l_revision_num
192 and po_release_id is null;
193
194 else
195
196 select count(*) into l_acc_shipment_count
197 from po_acceptances
198 where po_release_id = p_po_release_id
199 and po_line_location_id is not null
200 and revision_num = l_revision_num;
201
202 end if;
203
204 -- select count of non-split shipments with same change request group id
205
206 if (p_po_release_id is null) then
207
208 select count(*) into l_change_shipment_count
209 from po_change_requests
210 where document_header_id = p_po_header_id
211 and po_release_id is null
212 and change_request_group_id = p_change_request_group_id
213 and request_level = 'SHIPMENT'
214 and parent_line_location_id is null;
215
216 else
217
218 select count(*) into l_change_shipment_count
219 from po_change_requests
220 where po_release_id = p_po_release_id
221 and change_request_group_id = p_change_request_group_id
222 and request_level = 'SHIPMENT'
223 and parent_line_location_id is null;
224
225 end if;
226
227 -- select total count of shipments to be acknowledged i.e.
228 -- all shipments which have not been cancelled or closed
229
230 if (p_po_release_id is null) then
231
232 select count(*) into l_total_shipment_count
233 from po_line_locations_all
234 where po_header_id = p_po_header_id
235 and po_release_id is null
236 and nvl(cancel_flag, 'N') <> 'Y'
237 and nvl(closed_code, 'OPEN') = 'OPEN'
238 and nvl(payment_type,'NULL')<>'ADVANCE'; --Bug 5132565
239
240 else
241
242 select count(*) into l_total_shipment_count
243 from po_line_locations_all
244 where po_header_id = p_po_header_id
245 and po_release_id = p_po_release_id
246 and nvl(cancel_flag, 'N') <> 'Y'
247 and nvl(closed_code, 'OPEN') = 'OPEN'
248 and nvl(payment_type,'NULL')<>'ADVANCE'; --Bug 5132565
249
250
251 end if;
252
253 -- now compare quantities to determine whether all required shipments have been acked or changed
254
255 if ((l_acc_shipment_count + l_change_shipment_count) < (l_total_shipment_count )) then
256 x_partial_ack := 'Y';
257 else
258 x_partial_ack := 'N';
259 end if;
260
261 */
262
263 else /* either PO does not require acceptance or supplier is not acting on the PO */
264
265 x_partial_ack := 'N';
266
267 end if;
268
269 EXCEPTION WHEN OTHERS THEN
270
271 x_partial_ack := 'N';
272
273 END CheckPartialAck;
274
275
276
277 /** CheckChangePending
278 * -------------------
279 * Purpose:
280 * This procedure is used to determine if there are any supplier initiated changes in pending status
281 * Usage:
282 * This procedure is called in following scenarios -
283 * 1. To determine if there is a pending change request from the supplier on a PO and
284 * if so activate the Repsonse button for that PO on the PO summary form
285 * 2. To determine after the buyer responds whether there are any more changes in pending status
286 * to which the buyer needs to respond before we call the PO validation and update procedures
287 * Return Value:
288 * This procedure returns p_change_pending with value 'Y' if there are some changes in pending state and
289 * with value 'N' if there are no changes in pending state
290 */
291
292 PROCEDURE CheckChangePending (p_api_version in number,
293 x_return_status out NOCOPY varchar2,
294 p_po_header_id in number,
295 p_po_release_id in number,
296 x_change_pending out NOCOPY varchar2) is
297
298 l_pending_count NUMBER;
299 l_revision_num NUMBER;
300
301 BEGIN
302
303 select revision_num
304 into l_revision_num
305 from po_headers_all
306 where po_header_id = p_po_header_id;
307
308 select count(*) into l_pending_count
309 from po_change_requests
310 where document_header_id = p_po_header_id
311 and document_revision_num = l_revision_num
312 and request_status = 'PENDING';
313
314 if (l_pending_count > 0) then
315 x_change_pending := 'Y';
316 else
317 x_change_pending := 'N';
318 end if;
319 EXCEPTION
320 WHEN OTHERS THEN
321 x_change_pending := 'N';
322 End CheckChangePending;
323
324
325 /** get_distribution_count
326 */
327
328 FUNCTION get_distribution_count(p_request_level IN VARCHAR,
329 p_document_line_location_Id IN NUMBER,
330 p_parent_line_location_id IN NUMBER) RETURN NUMBER is
331 l_distribution_count NUMBER;
332
333 BEGIN
334 l_distribution_count := 0;
335
336 if (p_request_level = 'SHIPMENT') then
337
338 if ((p_parent_line_location_id is null) OR (p_parent_line_location_id = 0)) then
339
340 select count(*) into l_distribution_count
341 from po_distributions_all where line_location_id = p_document_line_location_id;
342
343 else /* line is a split shipment */
344
345 select count(*) into l_distribution_count
346 from po_distributions_all where line_location_id = p_parent_line_location_id;
347
348 end if; /* split shipment test */
349
350 end if;
351
352 return l_distribution_count;
353
354 EXCEPTION WHEN OTHERS THEN
355
356 return l_distribution_count;
357
358 END get_distribution_count;
359
360
361
362 /** MoveChangeToPO
363 * ---------------
364 * Purpose:
365 * This procedure is used to perform validation of changes requested to PO and perform updates on the PO
366 * Usage:
367 * This procedure is called in the following scenario:
368 * 1. When the buyer completes responding to all the requested changes, this procedure is
369 * called by ProcessResponse procedure
370 * Return Value:
371 * This procedure returns return_code which has a value
372 * 0 if the validation was completely successful,
373 * 1 if the validation failed for some or all changes
374 * 2 if there was an unexpected error
375 */
376
377 PROCEDURE MoveChangeToPO (p_api_version in number,
378 x_return_status out NOCOPY varchar2,
379 p_po_header_id in number,
380 p_po_release_id in number,
381 p_change_request_group_id in number,
382 p_user_id in number,
383 x_return_code out NOCOPY NUMBER,
384 x_err_msg out NOCOPY VARCHAR2,
385 x_doc_check_rec_type out NOCOPY POS_ERR_TYPE,
386 p_launch_approvals_flag IN VARCHAR2,
387 p_mass_update_releases IN VARCHAR2 DEFAULT NULL, -- Bug 3373453
388 p_req_chg_initiator IN VARCHAR2 DEFAULT NULL --Bug 14549341
389 ) is
390
391 CURSOR changed_lines_cursor IS
392 SELECT document_line_id, new_price, new_supplier_part_number,
393 new_start_date, new_expiration_date, new_amount
394 FROM po_change_requests
395 where request_status = 'BUYER_APP'
396 AND document_header_id = p_po_header_id
397 AND change_request_group_id = p_change_request_group_id
398 AND request_level = 'LINE';
399
400 CURSOR changed_shipments_cursor IS
401 SELECT document_line_location_id,
402 new_quantity, new_promised_date, new_need_by_date, new_price,
403 parent_line_location_id,
404 Decode(parent_line_location_id, null, null, document_shipment_number),
405 new_amount,
406 new_progress_type, -- << Complex work changes for R12 >>
407 new_pay_description,
408 new_supplier_order_line_number
409 FROM po_change_requests
410 where request_status = 'BUYER_APP'
411 AND document_header_id = p_po_header_id
412 AND change_request_group_id = p_change_request_group_id
413 AND request_level = 'SHIPMENT';
414
415 CURSOR changed_distributions_cursor IS
416 SELECT PCR.document_distribution_id, PCR.new_quantity, Decode(PCR.parent_line_location_id, null, null, PCR.document_shipment_number),
417 POD.po_distribution_id parent_distribution_id, PCR.new_amount
418 FROM po_change_requests PCR,
419 po_distributions POD
420 where PCR.request_status = 'BUYER_APP'
421 AND PCR.document_header_id = p_po_header_id
422 AND PCR.change_request_group_id = p_change_request_group_id
423 AND PCR.request_level = 'DISTRIBUTION'
424 -- Identify the parent of a split distribution:
425 AND POD.line_location_id (+)= PCR.parent_line_location_id
426 AND POD.distribution_num (+)= PCR.document_distribution_number
427 AND nvl(PCR.new_quantity,-1)<>0
428 AND nvl(PCR.new_amount,-1)<>0;
429
430 l_po_line_id_tbl PO_TBL_NUMBER;
431 l_quantity_tbl PO_TBL_NUMBER;
432 l_price_tbl PO_TBL_NUMBER;
433 l_amount_tbl PO_TBL_NUMBER;
434 l_po_line_location_id_tbl PO_TBL_NUMBER;
435 l_parent_line_location_id_tbl PO_TBL_NUMBER;
436 l_promised_date_tbl PO_TBL_DATE;
437 l_need_by_date_tbl PO_TBL_DATE;
438 l_start_date_tbl PO_TBL_DATE;
439 l_expiration_date_tbl PO_TBL_DATE;
440 l_split_shipment_num_tbl PO_TBL_NUMBER;
441 l_po_distribution_id_tbl PO_TBL_NUMBER;
442 l_parent_distribution_id_tbl PO_TBL_NUMBER;
443 l_supplier_part_number_tbl PO_TBL_VARCHAR30;
444 l_err_msg_name_tbl po_tbl_varchar30;
445 l_err_msg_text_tbl po_tbl_varchar2000;
446 l_line_changes PO_LINES_REC_TYPE;
447 l_shipment_changes PO_SHIPMENTS_REC_TYPE;
448 l_distribution_changes PO_DISTRIBUTIONS_REC_TYPE;
449 l_changes PO_CHANGES_REC_TYPE;
450 /* << Complex work changes for R12 >>*/
451 l_new_progress_type_tbl PO_TBL_VARCHAR30;
452 l_new_pay_description_tbl PO_TBL_VARCHAR240;
453 l_new_supp_order_line_no PO_TBL_VARCHAR25;
454
455
456 x_pos_errors POS_ERR_TYPE;
457 l_api_errors PO_API_ERRORS_REC_TYPE;
458 l_s_org_id NUMBER;
459 l_return_status VARCHAR2(30);
460 l_progress varchar2(3) := '000';
461 l_launch_approvals_flag VARCHAR2(1);
462
463 l_document_type PO_DOCUMENT_TYPES_ALL_B.document_type_code%TYPE;
464 l_document_subtype PO_DOCUMENT_TYPES_ALL_B.document_subtype%TYPE;
465
466
467 BEGIN
468
469 l_progress := '001';
470
471 x_return_code := 0; /* 0 implies no error */
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473 x_err_msg := '';
474 x_doc_check_rec_type := null;
475
476 /*
477 if (p_launch_approvals_flag = 'N') then
478 l_launch_approvals_flag := FND_API.G_FALSE;
479
480 else
481 l_launch_approvals_flag := FND_API.G_TRUE;
482
483 end if;
484 */
485
486
487 if (p_po_release_id is not null) then
488
489 select org_id, release_type
490 into l_s_org_id, l_document_subtype
491 from po_releases_all
492 where po_release_id = p_po_release_id;
493
494 l_document_type := 'RELEASE';
495
496 else
497
498 select org_id, type_lookup_code
499 into l_s_org_id, l_document_subtype
500 from po_headers_all
501 where po_header_id = p_po_header_id;
502
503 IF (l_document_subtype IN ('BLANKET','CONTRACT')) THEN
504 l_document_type := 'PA';
505 ELSE
506 l_document_type := 'PO';
507 END IF;
508
509
510 end if;
511
512 PO_MOAC_UTILS_PVT.set_org_context(l_s_org_id) ; -- <R12 MOAC>
513
514 l_progress := '002';
515
516 -- For shipments with a single distribution create distribution records in PO_CHANGE_REQUESTS
517
518 CreateSingleDistribution(p_change_request_group_id);
519
520 l_progress := '003';
521
522 -- Fetch the line changes from the PO_CHANGE_REQUESTS table and construct the line changes object
523
524 OPEN changed_lines_cursor;
525 FETCH changed_lines_cursor BULK COLLECT INTO
526 l_po_line_id_tbl,
527 l_price_tbl,
528 l_supplier_part_number_tbl,
529 l_start_date_tbl,
530 l_expiration_date_tbl,
531 l_amount_tbl;
532 CLOSE changed_lines_cursor;
533
534 l_progress := '004';
535
536 l_line_changes := PO_LINES_REC_TYPE.create_object (
537 p_po_line_id => l_po_line_id_tbl,
538 p_unit_price => l_price_tbl,
539 p_vendor_product_num => l_supplier_part_number_tbl,
540 p_start_date => l_start_date_tbl,
541 p_expiration_date => l_expiration_date_tbl,
542 p_amount => l_amount_tbl
543 );
544
545 l_progress := '005';
546
547 -- Fetch the shipment changes from the PO_CHANGE_REQUESTS table and construct the shipment changes object
548
549 OPEN changed_shipments_cursor;
550 FETCH changed_shipments_cursor BULK COLLECT INTO
551 l_po_line_location_id_tbl,
552 l_quantity_tbl,
553 l_promised_date_tbl,
554 l_need_by_date_tbl,
555 l_price_tbl,
556 l_parent_line_location_id_tbl,
557 l_split_shipment_num_tbl,
558 l_amount_tbl,
559 l_new_progress_type_tbl,
560 l_new_pay_description_tbl, --<< Complex work changes for R12 >>
561 l_new_supp_order_line_no;
562
563 CLOSE changed_shipments_cursor;
564
565 l_progress := '006';
566
567 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
568 p_po_line_location_id => l_po_line_location_id_tbl,
569 p_quantity => l_quantity_tbl,
570 p_promised_date => l_promised_date_tbl,
571 p_price_override => l_price_tbl,
572 p_parent_line_location_id => l_parent_line_location_id_tbl,
573 p_split_shipment_num => l_split_shipment_num_tbl,
574 p_need_by_date => l_need_by_date_tbl,
575 p_amount => l_amount_tbl,
576 p_payment_type => l_new_progress_type_tbl, -- << Complex work changes for R12 >>
577 p_description => l_new_pay_description_tbl,
578 p_new_supp_order_line_no => l_new_supp_order_line_no
579 );
580
581 l_progress := '007';
582
583 -- Fetch the distribution changes from the PO_CHANGE_REQUESTS table and
584 -- construct the distribution changes object
585
586 OPEN changed_distributions_cursor;
587 FETCH changed_distributions_cursor BULK COLLECT INTO
588 l_po_distribution_id_tbl,
589 l_quantity_tbl,
590 l_split_shipment_num_tbl,
591 l_parent_distribution_id_tbl,
592 l_amount_tbl;
593 CLOSE changed_distributions_cursor;
594
595 l_progress := '008';
596
597 l_distribution_changes := PO_DISTRIBUTIONS_REC_TYPE.create_object (
598 p_po_distribution_id => l_po_distribution_id_tbl,
599 p_quantity_ordered => l_quantity_tbl,
600 p_parent_distribution_id => l_parent_distribution_id_tbl,
601 p_split_shipment_num => l_split_shipment_num_tbl,
602 p_amount_ordered => l_amount_tbl
603 );
604
605 l_progress := '009';
606
607 -- Construct the document-level changes object.
608 l_changes := PO_CHANGES_REC_TYPE.create_object (
609 p_po_header_id => p_po_header_id,
610 p_po_release_id => p_po_release_id,
611 p_line_changes => l_line_changes,
612 p_shipment_changes => l_shipment_changes,
613 p_distribution_changes => l_distribution_changes
614 );
615
616 l_progress := '010';
617
618 -- The document is currently in 'IN PROCESS' status. Set it to 'APPROVED'
619 -- status to allow the PO Change API to process it
620
621 IF (p_po_release_id IS NULL) THEN -- PO, PA
622 UPDATE po_headers_all
623 SET authorization_status = 'APPROVED'
624 WHERE po_header_id = p_po_header_id;
625 ELSE -- Release
626 UPDATE po_releases_all
627 SET authorization_status = 'APPROVED'
628 WHERE po_release_id = p_po_release_id;
629 END IF;
630
631 l_progress := '011';
632
633 -- Call the PO Change API to apply these changes to the document
634 PO_DOCUMENT_UPDATE_GRP.update_document (
635 p_api_version => 1.0,
636 p_init_msg_list => FND_API.G_TRUE,
637 x_return_status => l_return_status,
638 p_changes => l_changes,
639 p_run_submission_checks => FND_API.G_TRUE,
640 p_launch_approvals_flag => FND_API.G_FALSE,
641 p_buyer_id => NULL,
642 p_update_source => NULL, -- default
643 p_override_date => NULL,
644 x_api_errors => l_api_errors,
645 p_mass_update_releases => p_mass_update_releases,
646 p_req_chg_initiator => p_req_chg_initiator --Bug 14549341
647 );
648
649 l_progress := '012';
650
651 -- Construct a POS_ERR_TYPE object from the errors returned by the PO Change API.
652 IF ((l_return_status = FND_API.G_RET_STS_ERROR) OR (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
653 l_err_msg_name_tbl := po_tbl_varchar30();
654 l_err_msg_text_tbl := po_tbl_varchar2000();
655 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl, l_err_msg_text_tbl);
656
657 FOR i IN 1..l_api_errors.message_name.COUNT LOOP
658
659 if ((nvl(l_api_errors.message_name(i),'NULL') not in
660 ('PO_SUB_SHIP_NO_DIST','PO_SUB_REL_SHIP_NO_DIST',
661 'PO_SUB_QTY_MISMATCH_DIST','PO_SUB_QTY_MISMATCH_SHIPMENT'))
662 AND nvl(l_api_errors.message_type(i), 'E') <> 'W') then
663
664 x_pos_errors.message_name.extend;
665 x_pos_errors.message_name(i) := l_api_errors.message_name(i);
666 x_pos_errors.text_line.extend;
667 x_pos_errors.text_line(i) := l_api_errors.message_text(i);
668
669 end if;
670
671 END LOOP;
672
673 x_doc_check_rec_type := x_pos_errors;
674
675 END IF;
676
677 l_progress := '013';
678
679 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
680 x_return_code := 1;
681 x_return_status := FND_API.G_RET_STS_ERROR;
682 x_err_msg := l_progress||':'||FND_MSG_PUB.get(FND_MSG_PUB.G_LAST,'F');
683 return;
684 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
685 raise FND_API.G_EXC_UNEXPECTED_ERROR;
686 END IF; -- l_return_status
687
688 l_progress := '014';
689
690 -- update_document generates a new PO_LINE_LOCATION_ID for each of the
691 -- split shipments. Update the PO_CHANGE_REQUESTS table with these IDs
692
693 --FOR i IN shipment changes LOOP
694 FOR i IN 1..l_changes.shipment_changes.PO_LINE_LOCATION_ID.COUNT LOOP
695 IF (l_changes.shipment_changes.parent_line_location_id(i) IS NOT NULL) THEN
696 UPDATE po_change_requests
697 SET document_line_location_id =
698 l_changes.shipment_changes.po_line_location_id(i)
699 WHERE parent_line_location_id =
700 l_changes.shipment_changes.parent_line_location_id(i)
701 AND document_shipment_number =
702 l_changes.shipment_changes.split_shipment_num(i)
703 AND change_request_group_id = p_change_request_group_id;
704 END IF;
705 END LOOP;
706
707 l_progress := '015';
708 x_err_msg := x_err_msg || 'SUCCESS : l_progress = ' || l_progress;
709
710
711 if (p_launch_approvals_flag = 'Y') then
712
713 PO_DOCUMENT_UPDATE_GRP.launch_po_approval_wf (
714 p_api_version => 1.0,
715 p_init_msg_list => FND_API.G_TRUE,
716 x_return_status => l_return_status,
717 p_document_id => NVL(p_po_release_id, p_po_header_id),
718 p_document_type => l_document_type,
719 p_document_subtype => l_document_subtype,
720 p_preparer_id => NULL,
721 p_approval_background_flag => NULL,
722 p_mass_update_releases => p_mass_update_releases );
723
724 end if;
725
726
727 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
728 x_return_code := 1;
729 x_return_status := FND_API.G_RET_STS_ERROR;
730 x_err_msg := l_progress||':'||FND_MSG_PUB.get(FND_MSG_PUB.G_LAST,'F');
731 return;
732 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
733 raise FND_API.G_EXC_UNEXPECTED_ERROR;
734 END IF; -- l_return_status
735
736 l_progress := '016';
737
738 EXCEPTION
739
740 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
741
742 x_return_code := 2;
743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
744 x_err_msg := l_progress||':'||FND_MSG_PUB.get(FND_MSG_PUB.G_LAST,'F');
745
746 WHEN OTHERS THEN
747
748 x_return_code := 2;
749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
750 x_err_msg := l_progress||':' || ':' || sqlcode ||':'||sqlerrm(sqlcode);
751
752 END MoveChangeToPO;
753
754
755
756 /** update_change_response
757 * -----------------------
758 * Purpose:
759 * This procedure is used to Update Request Statuses in Change_Table when Buyer Submits his Response
760 * from Response Page
761 * Usage:
762 * This is called from PosChangeResponseVO updateResponse procedure in a loop for all accepted/rejected changes
763 * Return Value:
764 * This procedure does not have a return value
765 */
766
767 PROCEDURE update_change_response (p_request_status in VARCHAR2,
768 p_responded_by in NUMBER,
769 p_response_reason in VARCHAR2,
770 p_change_request_id in NUMBER,
771 p_request_level in VARCHAR2,
772 p_change_request_group_id in NUMBER,
773 p_line_location_id in NUMBER,
774 p_splitFlag in VARCHAR2,
775 p_cancel_backing_req in VARCHAR2) is
776
777 l_wf_item_key po_change_requests.wf_item_key%TYPE;
778 l_wf_item_type po_change_requests.wf_item_type%TYPE;
779
780 BEGIN
781
782 -- first update the line or shipment record in po_change_requests
783
784 if (p_request_status = 'REJECTED') then
785
786 update po_change_requests
787 set request_status = p_request_status,
788 -- change_active_flag = 'N', /* commented out due to bug 3574114 */
789 responded_by = p_responded_by,
790 response_date = sysdate,
791 last_updated_by = p_responded_by,
792 last_update_date = sysdate,
793 response_reason = p_response_reason
794 where change_request_id = p_change_request_id;
795
796 else
797
798 update po_change_requests
799 set request_status = p_request_status,
800 cancel_backing_req = p_cancel_backing_req,
801 responded_by = p_responded_by,
802 response_date = sysdate,
803 last_updated_by = p_responded_by,
804 last_update_date = sysdate,
805 response_reason = p_response_reason
806 where change_request_id = p_change_request_id;
807
808 end if;
809
810
811 begin
812
813 select wf_item_type, wf_item_key
814 into l_wf_item_type, l_wf_item_key
815 from po_change_requests
816 where change_request_id = p_change_request_id;
817
818 if (Wf_Item.Item_Exist(l_wf_item_type, l_wf_item_key)) then
819
820 wf_engine.abortprocess(l_wf_item_type, l_wf_item_key);
821
822 end if;
823
824 exception when others then
825 null;
826 end;
827
828 -- then perform the following step to update distribution records in Change Request table
829 -- if the the request_level for original record is 'SHIPMENT'
830
831 if (p_request_level = 'SHIPMENT') then
832
833 -- update the corresponding distribution records
834 if (p_splitFlag = 'N') then
835
836 update po_change_requests
837 set request_status = p_request_status,
838 responded_by = p_responded_by,
839 response_date = sysdate,
840 last_updated_by = p_responded_by,
841 last_update_date = sysdate,
842 response_reason = p_response_reason
843 where change_request_group_id = p_change_request_group_id and
844 request_level = 'DISTRIBUTION' and
845 request_status = 'PENDING' and
846 document_line_location_id = p_line_location_id;
847
848
849 -- also update approved_flag of corresponding shipment if change request is rejected
850
851 if (p_request_status = 'REJECTED') then
852
853 update po_line_locations_all
854 set approved_flag = 'Y'
855 where line_location_id = p_line_location_id;
856
857 end if;
858
859 else /* p_splitFlag is not 'N' */
860
861 update po_change_requests
862 set request_status = p_request_status,
863 responded_by = p_responded_by,
864 response_date = sysdate,
865 last_updated_by = p_responded_by,
866 last_update_date = sysdate,
867 response_reason = p_response_reason
868 where change_request_group_id = p_change_request_group_id and
869 request_level = 'DISTRIBUTION' and
870 request_status = 'PENDING' and
871 parent_line_location_id = p_line_location_id;
872
873 end if; /* splitFlag */
874
875 end if; /* request-level is 'SHIPMENT' */
876
877 END update_change_response;
878
879
880 /* roll_back_acceptance is used to roll back buyer acceptance responses
881 * in po_change_requests to 'PENDING' when a validation error is
882 * encountered during PO update validation and we decide to roll back the transaction
883 */
884
885 PROCEDURE roll_back_acceptance (p_change_request_group_id in NUMBER) is
886
887 BEGIN
888
889 update po_change_requests
890 set request_status = 'PENDING'
891 where change_request_group_id = p_change_request_group_id
892 and request_status in ('BUYER_APP', 'WAIT_MGR_APP');
893
894 exception when others then
895
896 null;
897
898 END;
899
900 END PO_CHANGE_RESPONSE_PVT;
901