1 PACKAGE BODY PO_CHANGE_RESPONSE_PVT AS
2 /* $Header: POXCHREB.pls 120.9.12010000.2 2008/08/02 10:34:30 rramasam 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 ) is
389
390 CURSOR changed_lines_cursor IS
391 SELECT document_line_id, new_price, new_supplier_part_number,
392 new_start_date, new_expiration_date, new_amount
393 FROM po_change_requests
394 where request_status = 'BUYER_APP'
395 AND document_header_id = p_po_header_id
396 AND change_request_group_id = p_change_request_group_id
397 AND request_level = 'LINE';
398
399 CURSOR changed_shipments_cursor IS
400 SELECT document_line_location_id,
401 new_quantity, new_promised_date, new_need_by_date, new_price,
402 parent_line_location_id,
403 Decode(parent_line_location_id, null, null, document_shipment_number),
404 new_amount,
405 new_progress_type, -- << Complex work changes for R12 >>
406 new_pay_description,
407 new_supplier_order_line_number
408 FROM po_change_requests
409 where request_status = 'BUYER_APP'
410 AND document_header_id = p_po_header_id
411 AND change_request_group_id = p_change_request_group_id
412 AND request_level = 'SHIPMENT';
413
414 CURSOR changed_distributions_cursor IS
415 SELECT PCR.document_distribution_id, PCR.new_quantity, Decode(PCR.parent_line_location_id, null, null, PCR.document_shipment_number),
416 POD.po_distribution_id parent_distribution_id, PCR.new_amount
417 FROM po_change_requests PCR,
418 po_distributions POD
419 where PCR.request_status = 'BUYER_APP'
420 AND PCR.document_header_id = p_po_header_id
421 AND PCR.change_request_group_id = p_change_request_group_id
422 AND PCR.request_level = 'DISTRIBUTION'
423 -- Identify the parent of a split distribution:
424 AND POD.line_location_id (+)= PCR.parent_line_location_id
425 AND POD.distribution_num (+)= PCR.document_distribution_number
426 AND nvl(PCR.new_quantity,-1)<>0
427 AND nvl(PCR.new_amount,-1)<>0;
428
429 l_po_line_id_tbl PO_TBL_NUMBER;
430 l_quantity_tbl PO_TBL_NUMBER;
431 l_price_tbl PO_TBL_NUMBER;
432 l_amount_tbl PO_TBL_NUMBER;
433 l_po_line_location_id_tbl PO_TBL_NUMBER;
434 l_parent_line_location_id_tbl PO_TBL_NUMBER;
435 l_promised_date_tbl PO_TBL_DATE;
436 l_need_by_date_tbl PO_TBL_DATE;
437 l_start_date_tbl PO_TBL_DATE;
438 l_expiration_date_tbl PO_TBL_DATE;
439 l_split_shipment_num_tbl PO_TBL_NUMBER;
440 l_po_distribution_id_tbl PO_TBL_NUMBER;
441 l_parent_distribution_id_tbl PO_TBL_NUMBER;
442 l_supplier_part_number_tbl PO_TBL_VARCHAR30;
443 l_err_msg_name_tbl po_tbl_varchar30;
444 l_err_msg_text_tbl po_tbl_varchar2000;
445 l_line_changes PO_LINES_REC_TYPE;
446 l_shipment_changes PO_SHIPMENTS_REC_TYPE;
447 l_distribution_changes PO_DISTRIBUTIONS_REC_TYPE;
448 l_changes PO_CHANGES_REC_TYPE;
449 /* << Complex work changes for R12 >>*/
450 l_new_progress_type_tbl PO_TBL_VARCHAR30;
451 l_new_pay_description_tbl PO_TBL_VARCHAR240;
452 l_new_supp_order_line_no PO_TBL_VARCHAR25;
453
454
455 x_pos_errors POS_ERR_TYPE;
456 l_api_errors PO_API_ERRORS_REC_TYPE;
457 l_s_org_id NUMBER;
458 l_return_status VARCHAR2(30);
459 l_progress varchar2(3) := '000';
460 l_launch_approvals_flag VARCHAR2(1);
461
462 l_document_type PO_DOCUMENT_TYPES_ALL_B.document_type_code%TYPE;
463 l_document_subtype PO_DOCUMENT_TYPES_ALL_B.document_subtype%TYPE;
464
465
466 BEGIN
467
468 l_progress := '001';
469
470 x_return_code := 0; /* 0 implies no error */
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472 x_err_msg := '';
473 x_doc_check_rec_type := null;
474
475 /*
476 if (p_launch_approvals_flag = 'N') then
477 l_launch_approvals_flag := FND_API.G_FALSE;
478
479 else
480 l_launch_approvals_flag := FND_API.G_TRUE;
481
482 end if;
483 */
484
485
486 if (p_po_release_id is not null) then
487
488 select org_id, release_type
489 into l_s_org_id, l_document_subtype
490 from po_releases_all
491 where po_release_id = p_po_release_id;
492
493 l_document_type := 'RELEASE';
494
495 else
496
497 select org_id, type_lookup_code
498 into l_s_org_id, l_document_subtype
499 from po_headers_all
500 where po_header_id = p_po_header_id;
501
502 IF (l_document_subtype IN ('BLANKET','CONTRACT')) THEN
503 l_document_type := 'PA';
504 ELSE
505 l_document_type := 'PO';
506 END IF;
507
508
509 end if;
510
511 PO_MOAC_UTILS_PVT.set_org_context(l_s_org_id) ; -- <R12 MOAC>
512
513 l_progress := '002';
514
515 -- For shipments with a single distribution create distribution records in PO_CHANGE_REQUESTS
516
517 CreateSingleDistribution(p_change_request_group_id);
518
519 l_progress := '003';
520
521 -- Fetch the line changes from the PO_CHANGE_REQUESTS table and construct the line changes object
522
523 OPEN changed_lines_cursor;
524 FETCH changed_lines_cursor BULK COLLECT INTO
525 l_po_line_id_tbl,
526 l_price_tbl,
527 l_supplier_part_number_tbl,
528 l_start_date_tbl,
529 l_expiration_date_tbl,
530 l_amount_tbl;
531 CLOSE changed_lines_cursor;
532
533 l_progress := '004';
534
535 l_line_changes := PO_LINES_REC_TYPE.create_object (
536 p_po_line_id => l_po_line_id_tbl,
537 p_unit_price => l_price_tbl,
538 p_vendor_product_num => l_supplier_part_number_tbl,
539 p_start_date => l_start_date_tbl,
540 p_expiration_date => l_expiration_date_tbl,
541 p_amount => l_amount_tbl
542 );
543
544 l_progress := '005';
545
546 -- Fetch the shipment changes from the PO_CHANGE_REQUESTS table and construct the shipment changes object
547
548 OPEN changed_shipments_cursor;
549 FETCH changed_shipments_cursor BULK COLLECT INTO
550 l_po_line_location_id_tbl,
551 l_quantity_tbl,
552 l_promised_date_tbl,
553 l_need_by_date_tbl,
554 l_price_tbl,
555 l_parent_line_location_id_tbl,
556 l_split_shipment_num_tbl,
557 l_amount_tbl,
558 l_new_progress_type_tbl,
559 l_new_pay_description_tbl, --<< Complex work changes for R12 >>
560 l_new_supp_order_line_no;
561
562 CLOSE changed_shipments_cursor;
563
564 l_progress := '006';
565
566 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
567 p_po_line_location_id => l_po_line_location_id_tbl,
568 p_quantity => l_quantity_tbl,
569 p_promised_date => l_promised_date_tbl,
570 p_price_override => l_price_tbl,
571 p_parent_line_location_id => l_parent_line_location_id_tbl,
572 p_split_shipment_num => l_split_shipment_num_tbl,
573 p_need_by_date => l_need_by_date_tbl,
574 p_amount => l_amount_tbl,
575 p_payment_type => l_new_progress_type_tbl, -- << Complex work changes for R12 >>
576 p_description => l_new_pay_description_tbl,
577 p_new_supp_order_line_no => l_new_supp_order_line_no
578 );
579
580 l_progress := '007';
581
582 -- Fetch the distribution changes from the PO_CHANGE_REQUESTS table and
583 -- construct the distribution changes object
584
585 OPEN changed_distributions_cursor;
586 FETCH changed_distributions_cursor BULK COLLECT INTO
587 l_po_distribution_id_tbl,
588 l_quantity_tbl,
589 l_split_shipment_num_tbl,
590 l_parent_distribution_id_tbl,
591 l_amount_tbl;
592 CLOSE changed_distributions_cursor;
593
594 l_progress := '008';
595
596 l_distribution_changes := PO_DISTRIBUTIONS_REC_TYPE.create_object (
597 p_po_distribution_id => l_po_distribution_id_tbl,
598 p_quantity_ordered => l_quantity_tbl,
599 p_parent_distribution_id => l_parent_distribution_id_tbl,
600 p_split_shipment_num => l_split_shipment_num_tbl,
601 p_amount_ordered => l_amount_tbl
602 );
603
604 l_progress := '009';
605
606 -- Construct the document-level changes object.
607 l_changes := PO_CHANGES_REC_TYPE.create_object (
608 p_po_header_id => p_po_header_id,
609 p_po_release_id => p_po_release_id,
610 p_line_changes => l_line_changes,
611 p_shipment_changes => l_shipment_changes,
612 p_distribution_changes => l_distribution_changes
613 );
614
615 l_progress := '010';
616
617 -- The document is currently in 'IN PROCESS' status. Set it to 'APPROVED'
618 -- status to allow the PO Change API to process it
619
620 IF (p_po_release_id IS NULL) THEN -- PO, PA
621 UPDATE po_headers_all
622 SET authorization_status = 'APPROVED'
623 WHERE po_header_id = p_po_header_id;
624 ELSE -- Release
625 UPDATE po_releases_all
626 SET authorization_status = 'APPROVED'
627 WHERE po_release_id = p_po_release_id;
628 END IF;
629
630 l_progress := '011';
631
632 -- Call the PO Change API to apply these changes to the document
633 PO_DOCUMENT_UPDATE_GRP.update_document (
634 p_api_version => 1.0,
635 p_init_msg_list => FND_API.G_TRUE,
636 x_return_status => l_return_status,
637 p_changes => l_changes,
638 p_run_submission_checks => FND_API.G_TRUE,
639 p_launch_approvals_flag => FND_API.G_FALSE,
640 p_buyer_id => NULL,
641 p_update_source => NULL, -- default
642 p_override_date => NULL,
643 x_api_errors => l_api_errors,
644 p_mass_update_releases => p_mass_update_releases
645 );
646
647 l_progress := '012';
648
649 -- Construct a POS_ERR_TYPE object from the errors returned by the PO Change API.
650 IF ((l_return_status = FND_API.G_RET_STS_ERROR) OR (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
651 l_err_msg_name_tbl := po_tbl_varchar30();
652 l_err_msg_text_tbl := po_tbl_varchar2000();
653 x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl, l_err_msg_text_tbl);
654
655 FOR i IN 1..l_api_errors.message_name.COUNT LOOP
656
657 if ((nvl(l_api_errors.message_name(i),'NULL') not in
658 ('PO_SUB_SHIP_NO_DIST','PO_SUB_REL_SHIP_NO_DIST',
659 'PO_SUB_QTY_MISMATCH_DIST','PO_SUB_QTY_MISMATCH_SHIPMENT'))
660 AND nvl(l_api_errors.message_type(i), 'E') <> 'W') then
661
662 x_pos_errors.message_name.extend;
663 x_pos_errors.message_name(i) := l_api_errors.message_name(i);
664 x_pos_errors.text_line.extend;
665 x_pos_errors.text_line(i) := l_api_errors.message_text(i);
666
667 end if;
668
669 END LOOP;
670
671 x_doc_check_rec_type := x_pos_errors;
672
673 END IF;
674
675 l_progress := '013';
676
677 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
678 x_return_code := 1;
679 x_return_status := FND_API.G_RET_STS_ERROR;
680 x_err_msg := l_progress||':'||FND_MSG_PUB.get(FND_MSG_PUB.G_LAST,'F');
681 return;
682 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
683 raise FND_API.G_EXC_UNEXPECTED_ERROR;
684 END IF; -- l_return_status
685
686 l_progress := '014';
687
688 -- update_document generates a new PO_LINE_LOCATION_ID for each of the
689 -- split shipments. Update the PO_CHANGE_REQUESTS table with these IDs
690
691 --FOR i IN shipment changes LOOP
692 FOR i IN 1..l_changes.shipment_changes.PO_LINE_LOCATION_ID.COUNT LOOP
693 IF (l_changes.shipment_changes.parent_line_location_id(i) IS NOT NULL) THEN
694 UPDATE po_change_requests
695 SET document_line_location_id =
696 l_changes.shipment_changes.po_line_location_id(i)
697 WHERE parent_line_location_id =
698 l_changes.shipment_changes.parent_line_location_id(i)
699 AND document_shipment_number =
700 l_changes.shipment_changes.split_shipment_num(i)
701 AND change_request_group_id = p_change_request_group_id;
702 END IF;
703 END LOOP;
704
705 l_progress := '015';
706 x_err_msg := x_err_msg || 'SUCCESS : l_progress = ' || l_progress;
707
708
709 if (p_launch_approvals_flag = 'Y') then
710
711 PO_DOCUMENT_UPDATE_GRP.launch_po_approval_wf (
712 p_api_version => 1.0,
713 p_init_msg_list => FND_API.G_TRUE,
714 x_return_status => l_return_status,
715 p_document_id => NVL(p_po_release_id, p_po_header_id),
716 p_document_type => l_document_type,
717 p_document_subtype => l_document_subtype,
718 p_preparer_id => NULL,
719 p_approval_background_flag => NULL,
720 p_mass_update_releases => p_mass_update_releases );
721
722 end if;
723
724
725 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
726 x_return_code := 1;
727 x_return_status := FND_API.G_RET_STS_ERROR;
728 x_err_msg := l_progress||':'||FND_MSG_PUB.get(FND_MSG_PUB.G_LAST,'F');
729 return;
730 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
731 raise FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF; -- l_return_status
733
734 l_progress := '016';
735
736 EXCEPTION
737
738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
739
740 x_return_code := 2;
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
742 x_err_msg := l_progress||':'||FND_MSG_PUB.get(FND_MSG_PUB.G_LAST,'F');
743
744 WHEN OTHERS THEN
745
746 x_return_code := 2;
747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
748 x_err_msg := l_progress||':' || ':' || sqlcode ||':'||sqlerrm(sqlcode);
749
750 END MoveChangeToPO;
751
752
753
754 /** update_change_response
755 * -----------------------
756 * Purpose:
757 * This procedure is used to Update Request Statuses in Change_Table when Buyer Submits his Response
758 * from Response Page
759 * Usage:
760 * This is called from PosChangeResponseVO updateResponse procedure in a loop for all accepted/rejected changes
761 * Return Value:
762 * This procedure does not have a return value
763 */
764
765 PROCEDURE update_change_response (p_request_status in VARCHAR2,
766 p_responded_by in NUMBER,
767 p_response_reason in VARCHAR2,
768 p_change_request_id in NUMBER,
769 p_request_level in VARCHAR2,
770 p_change_request_group_id in NUMBER,
771 p_line_location_id in NUMBER,
772 p_splitFlag in VARCHAR2,
773 p_cancel_backing_req in VARCHAR2) is
774
775 l_wf_item_key po_change_requests.wf_item_key%TYPE;
776 l_wf_item_type po_change_requests.wf_item_type%TYPE;
777
778 BEGIN
779
780 -- first update the line or shipment record in po_change_requests
781
782 if (p_request_status = 'REJECTED') then
783
784 update po_change_requests
785 set request_status = p_request_status,
786 -- change_active_flag = 'N', /* commented out due to bug 3574114 */
787 responded_by = p_responded_by,
788 response_date = sysdate,
789 last_updated_by = p_responded_by,
790 last_update_date = sysdate,
791 response_reason = p_response_reason
792 where change_request_id = p_change_request_id;
793
794 else
795
796 update po_change_requests
797 set request_status = p_request_status,
798 cancel_backing_req = p_cancel_backing_req,
799 responded_by = p_responded_by,
800 response_date = sysdate,
801 last_updated_by = p_responded_by,
802 last_update_date = sysdate,
803 response_reason = p_response_reason
804 where change_request_id = p_change_request_id;
805
806 end if;
807
808
809 begin
810
811 select wf_item_type, wf_item_key
812 into l_wf_item_type, l_wf_item_key
813 from po_change_requests
814 where change_request_id = p_change_request_id;
815
816 if (Wf_Item.Item_Exist(l_wf_item_type, l_wf_item_key)) then
817
818 wf_engine.abortprocess(l_wf_item_type, l_wf_item_key);
819
820 end if;
821
822 exception when others then
823 null;
824 end;
825
826 -- then perform the following step to update distribution records in Change Request table
827 -- if the the request_level for original record is 'SHIPMENT'
828
829 if (p_request_level = 'SHIPMENT') then
830
831 -- update the corresponding distribution records
832 if (p_splitFlag = 'N') then
833
834 update po_change_requests
835 set request_status = p_request_status,
836 responded_by = p_responded_by,
837 response_date = sysdate,
838 last_updated_by = p_responded_by,
839 last_update_date = sysdate,
840 response_reason = p_response_reason
841 where change_request_group_id = p_change_request_group_id and
842 request_level = 'DISTRIBUTION' and
843 request_status = 'PENDING' and
844 document_line_location_id = p_line_location_id;
845
846
847 -- also update approved_flag of corresponding shipment if change request is rejected
848
849 if (p_request_status = 'REJECTED') then
850
851 update po_line_locations_all
852 set approved_flag = 'Y'
853 where line_location_id = p_line_location_id;
854
855 end if;
856
857 else /* p_splitFlag is not 'N' */
858
859 update po_change_requests
860 set request_status = p_request_status,
861 responded_by = p_responded_by,
862 response_date = sysdate,
863 last_updated_by = p_responded_by,
864 last_update_date = sysdate,
865 response_reason = p_response_reason
866 where change_request_group_id = p_change_request_group_id and
867 request_level = 'DISTRIBUTION' and
868 request_status = 'PENDING' and
869 parent_line_location_id = p_line_location_id;
870
871 end if; /* splitFlag */
872
873 end if; /* request-level is 'SHIPMENT' */
874
875 END update_change_response;
876
877
878 /* roll_back_acceptance is used to roll back buyer acceptance responses
879 * in po_change_requests to 'PENDING' when a validation error is
880 * encountered during PO update validation and we decide to roll back the transaction
881 */
882
883 PROCEDURE roll_back_acceptance (p_change_request_group_id in NUMBER) is
884
885 BEGIN
886
887 update po_change_requests
888 set request_status = 'PENDING'
889 where change_request_group_id = p_change_request_group_id
890 and request_status in ('BUYER_APP', 'WAIT_MGR_APP');
891
892 exception when others then
893
894 null;
895
896 END;
897
898 END PO_CHANGE_RESPONSE_PVT;
899
[Home] [Help]