1 PACKAGE BODY PO_CHG_REQUEST_GRP AS
2 /* $Header: POXGCHGB.pls 120.3.12010000.3 2008/10/20 15:30:54 vchiran ship $ */
3
4
5 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
6
7 -- Read the profile option that enables/disables the debug log
8 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9
10
11 /*
12 This PL/SQL table will store the supplier request. A new row will be added
13 for each call of the store_supplier_request.
14
15 g_po_change_table pos_chg_rec_tbl;
16 */
17 /*
18 g_int_cont_num value will store the internal control number of each request.
19 This will be set in the initialize call and reset on the windup call.
20 Each time the API is called, it will be matched for the integrity purposes.
21
22 g_int_cont_num varchar2(256);
23 */
24
25
26 /******************************* Private Procedures ***********************/
27
28 procedure getNewShipmentNumber (p_line_id IN number,
29 x_new_shipment_number OUT NOCOPY number) is
30 i number;
31 l_shipment_number number := null;
32 l_api_name CONSTANT VARCHAR2(30) := 'getNewShipmentNumber';
33 begin
34 select max(shipment_num) + 1
35 into l_shipment_number
36 from po_line_locations_archive_all
37 where po_line_id = p_line_id
38 and latest_external_flag = 'Y';
39
40
41
42 if (g_po_change_table.count() > 0) then
43 FOR i in 1..g_po_change_table.count()
44 LOOP
45 if (g_po_change_table(i).Document_Line_Id = p_line_id ) then
46 if (l_shipment_number <= g_po_change_table(i).Document_Shipment_Number) then
47 l_shipment_number := g_po_change_table(i).Document_Shipment_Number + 1;
48 end if;
49 end if;
50 end loop;
51 end if;
52 x_new_shipment_number := l_shipment_number;
53 exception when others then
54 --There can be only unforeseen system errors.
55 IF g_fnd_debug = 'Y' THEN
56 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
57 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
58 l_api_name || '.others_exception', sqlcode);
59 END IF;
60 END IF;
61 raise;
62 end;
63
64
65 procedure validateCancelRequest(
66 p_api_version IN NUMBER,
67 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
68 x_return_status OUT NOCOPY VARCHAR2,
69 p_po_header_id IN NUMBER,
70 p_po_release_id IN NUMBER,
71 p_po_line_location_id IN number default null) IS
72
73 p_document_id NUMBER;
74 v_document_type PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
75 v_document_subtype PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
76 v_type_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE;
77 l_api_name CONSTANT VARCHAR2(30) := 'validateCancelRequest';
78 l_api_version CONSTANT NUMBER := 1.0;
79 l_doc_line_id number; -- added for bug# 5639722
80 x_org_id number;
81
82 BEGIN
83 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
84 l_api_name, g_pkg_name)
85 THEN
86 RAISE FND_API.g_exc_unexpected_error;
87 END IF;
88
89 x_return_status := FND_API.g_ret_sts_success;
90
91 -- Call this when logging is enabled
92
93 IF g_fnd_debug = 'Y' THEN
94 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
95 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
96 '.invoked', 'Type: ' ||
97 ', Header ID: ' || NVL(TO_CHAR(p_po_header_id),'null') ||
98 ', Release ID: ' || NVL(TO_CHAR(p_po_release_id),'null'));
99 END IF;
100 END IF;
101 if (p_po_release_id is not null) then
102 p_document_id := p_po_release_id;
103 v_document_type := 'RELEASE';
104 v_document_subtype := 'BLANKET';
105
106 select org_id
107 into x_org_id
108 from po_releases_all
109 where po_release_id= p_po_release_id;
110
111 else
112 p_document_id := p_po_header_id;
113 select type_lookup_code into v_type_code
114 from po_headers_all
115 where po_header_id= p_po_header_id;
116 if (v_type_code in ('STANDARD','PLANNED')) then
117 v_document_type := 'PO';
118 v_document_subtype := v_type_code;
119 elsif (v_type_code in ('BLANKET','CONTRACT')) then
120 v_document_type := 'PA';
121 v_document_subtype := v_type_code;
122 end if;
123
124 select org_id
125 into x_org_id
126 from po_headers_all
127 where po_header_id= p_po_header_id;
128
129 end if;
130 -- Set the org context before calling the cancel api
131
132 PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; -- <R12 MOAC>
133
134 -- added for bug# 5639722
135 select plla.PO_LINE_ID into l_doc_line_id
136 from po_line_locations_all plla
137 where plla.line_location_id = p_po_line_location_id;
138
139 PO_Document_Control_GRP.check_control_action(
140 p_api_version => 1.0,
141 p_init_msg_list => FND_API.G_TRUE,
142 x_return_status => x_return_status,
143 p_doc_type => v_document_type,
144 p_doc_subtype => v_document_subtype,
145 p_doc_id => p_po_header_id,
146 p_doc_num => null,
147 p_release_id => p_po_release_id,
148 p_release_num => null,
149 p_doc_line_id => l_doc_line_id, -- bug# 5639722
150 p_doc_line_num => null,
151 p_doc_line_loc_id => p_po_line_location_id ,
152 p_doc_shipment_num => null,
153 p_action => 'CANCEL');
154
155 -- dbms_output.put_line ('The error is ' || x_return_status);
156
157
158 EXCEPTION
159
160 WHEN FND_API.g_exc_error THEN
161 x_return_status := FND_API.g_ret_sts_error;
162 WHEN FND_API.g_exc_unexpected_error THEN
163 x_return_status := FND_API.g_ret_sts_unexp_error;
164 WHEN OTHERS THEN
165 x_return_status := FND_API.g_ret_sts_unexp_error;
166 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
167 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
168 IF g_fnd_debug = 'Y' THEN
169 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
170 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
171 l_api_name || '.others_exception', sqlcode);
172 END IF;
173 END IF;
174 END IF;
175
176 END validateCancelRequest;
177
178
179
180 /********
181 Note this procedure will not check if there is a change pending already in
182 the change tables as if there is a change pending already it
183 would have been validated in the validate header.
184 ********/
185 -- Bug 7287009
186 /*Added vendor id and vendor site id in the signature of the procedure
187 Added l_org_id variable to capture org_id*/
188
189 procedure handle_header_level_requests
190 (p_po_number IN varchar2, --PO # of the PO being modified or
191 --the Blanket's PO #
192 p_release_number IN number, -- Release number if the PO Type
193 -- is release or null
194 p_po_type IN varchar2, -- RELEASE or STANDARD.
195 p_revision_num IN number, -- Revision number of the PO or the release
196 p_tp_id IN number, -- vendor_id
197 p_tp_site_id IN number, -- vendor_site_id
198 p_reason IN varchar2,
199 p_ack_type IN varchar2, -- 'ACCEPT' or 'REJECT' or 'MODIFICATION' or 'CANCELLATION'
200 p_so_order_number IN varchar2,
201 p_is_change IN OUT NOCOPY number,
202 x_error_id_out IN OUT NOCOPY number, -- The error id will be 2;
203 -- errors will go to the TP sysadmin
204 x_error_status_out IN OUT NOCOPY varchar2 -- Error message in this call
205 --concatenated with the old ones
206 )
207 is
208 l_release_id number;
209 l_po_header_id number;
210 l_max_rev number;
211 l_rec_cur_index number := 0;
212 l_ack_type varchar2 (30) := null;
213 l_old_so_order_number varchar2(25) := null;
214 l_new_so_order_number varchar2(25) := null;
215 l_req_status varchar2(30);
216 l_po_type varchar2(25);
217 l_can_cancel_status varchar2 (3) := null;
218 l_org_id number;
219
220 begin
221
222 if (p_ack_type <> 'CANCELLATION') then
223 l_release_id := null;
224 if (p_po_type = 'RELEASE') then
225 if (p_so_order_number is not null) then
226 /*
227 PO_CHG_API_REL_SO_CHG_INVLD = Sales Order Number change is not
228 supported for releases.
229 */
230 x_error_id_out := 2;
231 x_error_status_out := x_error_status_out ||
232 fnd_message.get_string('PO',
233 'PO_CHG_API_REL_SO_CHG_INVLD');
234 end if;
235 return; -- p_so_order_number is null, simply return as there no change
236 else
237 -- Bug 7287009 - Start
238 /*Used vendor id and vendor site id combination to fetch org_id.
239 Org_id is used in the second query's where clause to restrict
240 mutiple records fetch.*/
241
242 SELECT org_id
243 into l_org_id
244 FROM po_vendor_sites_all
245 WHERE vendor_id = p_tp_id
246 AND vendor_site_id = p_tp_site_id;
247
248 select po_header_id, VENDOR_ORDER_NUM
249 into l_po_header_id, l_old_so_order_number
250 from po_headers_all
251 where segment1 = p_po_number
252 AND org_id = l_org_id
253 AND TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
254 -- Bug 7287009 - End
255
256
257 end if; --if p_po_type = 'RELEASE'
258
259 --Ack Type always should be modification.
260
261 l_ack_type := 'MODIFICATION';
262
263
264 if (p_so_order_number is not null and
265 p_so_order_number <> l_old_so_order_number) then
266 p_is_change := 1;
267 l_new_so_order_number := p_so_order_number;
268 else --if there is no so_line_number change or if new is
269 --same as old so_line_number
270 p_is_change := 0; --we don't allow any other changes at the header.
271 l_new_so_order_number := null;
272 l_old_so_order_number := null;
273
274 end if;
275 else -- if (p_ack_type <> 'CANCELLATION')
276 l_ack_type := p_ack_type;
277
278 -- Bug 7287009 - Start
279 /*Used vendor id and vendor site id combination to fetch org_id.
280 Org_id is used in the second query's where clause to restrict
281 mutiple records fetch.*/
282
283 SELECT org_id
284 into l_org_id
285 FROM po_vendor_sites_all
286 WHERE vendor_id = p_tp_id
287 AND vendor_site_id = p_tp_site_id;
288
289 select po_header_id into l_po_header_id
290 from po_headers_all
291 where segment1 = p_po_number
292 AND org_id = l_org_id
293 AND TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
294 -- Bug 7287009 - End
295
296 if (upper(p_po_type) = 'RELEASE') then
297 select po_release_id into l_release_id
298 from po_releases_all
299 where po_header_id = l_po_header_id and release_num = p_release_number;
300 end if;
301
302 if (p_ack_type = 'CANCELLATION') then
303 validateCancelRequest(
304 p_api_version => 1.0,
305 p_init_msg_list => FND_API.G_FALSE,
306 x_return_status => l_can_cancel_status,
307 p_po_header_id => l_po_header_id,
308 p_po_release_id => l_release_id
309 );
310
311 if (l_can_cancel_status <> FND_API.G_RET_STS_SUCCESS) then
312 /*
313 PO_INVALID_CANCEL_REQ_REL = "The Release RELNUM for PO PONUMBER
317 fnd_message.set_token('RELNUM', p_release_number, false);
314 cannot be canelled due to error: ERRCODE. The error message is : ERRMSG"
315 */
316 fnd_message.set_name('PO', 'PO_INVALID_CANCEL_REQ_REL');
318 fnd_message.set_token('PONUMBER', p_po_number, false);
319 fnd_message.set_token('ERRCODE', l_can_cancel_status, false);
320 fnd_message.set_token('ERRMSG', FND_MSG_PUB.Get(p_msg_index => 1,p_encoded => 'F'));
321
322
323
324 x_error_status_out := x_error_status_out || fnd_message.get;
325
326 x_error_id_out := 2;
327 return;
328 end if;
329 end if;
330
331 end if;
332
333
334 /************** Store Request *********************/
335 if (x_error_id_out <> 0) then
336 return; --there was an error.
337 end if;
338
339 if (p_is_change = 0 and l_ack_type <> 'CANCELLATION') then
340 return; --There is no change or there is error.
341 end if;
342
343 select max(REVISION_NUM)
344 into l_max_rev
345 from po_headers_archive_all
346 where PO_HEADER_ID = l_po_header_id;
347
348 if (upper(p_po_type) = 'STANDARD') then
349 l_po_type := 'PO';
350 else
351 l_po_type := p_po_type;
352 end if;
353
354 l_req_status := 'PENDING'; -- 'PENDING' state, initially.
355 g_po_change_table.extend(1);
356 l_rec_cur_index := g_po_change_table.last;
357 /* Note N/U in the following comments stands for
358 not used in case of supplier change
359 N/A stands for not applicable in case of SHIPMENT level.
360 */
361
362
363 g_po_change_table(l_rec_cur_index) := PO_CHG_REQUEST_PVT.create_pos_change_rec(
364 p_Action_Type => l_ack_type,
365 p_Initiator => 'SUPPLIER',
366 p_Document_Type => l_po_type,
367 p_Request_Level => 'HEADER',
368 p_Request_Status => l_req_status,
369 p_Document_Header_Id => l_po_header_id,
370 p_Request_Reason => p_reason,
371 p_PO_Release_Id => l_release_id,
372 p_Document_Num => p_po_number,
373 p_Document_Revision_Num => l_max_rev,
374 p_Old_Supplier_Order_Number => l_old_so_order_number,
375 p_New_Supplier_Order_Number => l_new_so_order_number
376 );
377
378 exception
379 when others then
380 x_error_id_out := 2;
381 x_error_status_out := x_error_status_out || SQLERRM;
382
383
384 end handle_header_level_requests;
385
386
387 /****
388 Note: This procedure is called only if the change is at the line level.
389 ****/
390
391 -- Bug 7287009
392 /*Added vendor id and vendor site id in the signature of the procedure
393 Added l_org_id variable to capture org_id*/
394 procedure validate_line_change (
395 p_po_number IN varchar2, --PO # of the PO being modified or the Blanket's PO #
396 p_release_number IN number, -- Release number if the PO Type
397 -- is release or null
398 p_po_type IN varchar2, -- RELEASE or STANDARD.
399 p_revision_num IN number, -- Revision number of the PO or the release
400 p_tp_id IN number, -- vendor_id
401 p_tp_site_id IN number, -- vendor_site_id
402 p_line_num IN number, -- Line number being modified
403 p_quantity IN number, -- The new quantity (can be null)
404 p_quantity_uom IN varchar2, -- The UOM of the new quantity
405 p_price IN number, -- The new price value (can be null)
406 p_price_currency IN varchar2, -- The currency code of the new price
407 --(can be null)
408 p_price_uom IN varchar2, -- The UOM code of the new price (can be null)
409 p_supplier_part_num IN varchar2, --Can be null
410 p_reason IN varchar2,
411 p_is_change IN OUT NOCOPY number,
412 x_error_id_out IN OUT NOCOPY number,
413 x_error_status_out IN OUT NOCOPY varchar2
414 )
415 is
416 l_line_id number;
417 l_po_header_id number;
418 l_max_rev number;
419 l_rec_cur_index number := 0;
420 l_old_price_currency varchar2(10);
421 l_old_price number;
422 l_new_price number;
423 l_closed_code varchar2(30);
424 l_closed_flag varchar2(1);
425 l_cancel_flag varchar2(1);
426 l_old_supplier_part_num varchar2(25);
427 l_new_supplier_part_num varchar2(25);
428 l_req_status varchar2(30);
429 l_po_type varchar2(25);
430 l_stage varchar2(10) := 'BEGIN';
431 l_po_curr varchar2(10);
432 l_org_id number;
433 begin
434
435 --validate the the line number exists for the po specified and
436 -- get some more information about the line.
437 begin
438
439 -- Bug 7287009 - Start
440 /*Used vendor id and vendor site id combination to fetch org_id.
441 Org_id is used in the second query's where clause to restrict
442 mutiple records fetch.*/
443
444 SELECT org_id
445 into l_org_id
446 FROM po_vendor_sites_all
447 WHERE vendor_id = p_tp_id
451 PLA.PO_LINE_ID, PHA.PO_HEADER_ID,
448 AND vendor_site_id = p_tp_site_id;
449
450 select
452 FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY,
453 PLA.UNIT_PRICE UNIT_PRICE,
454 PLA.CLOSED_CODE,PLA.CLOSED_FLAG, PLA.CANCEL_FLAG, PLA.VENDOR_PRODUCT_NUM,
455 POCR.REQUEST_STATUS, PHA.CURRENCY_CODE
456 into l_line_id, l_po_header_id,
457 l_old_price_currency, l_old_price,
458 l_closed_code, l_closed_flag, l_cancel_flag, l_old_supplier_part_num,
459 l_req_status, l_po_curr
460
461 FROM
462 PO_LINES_ALL PLA,
463 FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
464 PO_HEADERS_ALL PHA,
465 PO_CHANGE_REQUESTS POCR
466
467 WHERE
468
469 FSPA.ORG_ID = PLA.ORG_ID AND
470 pha.po_header_id = PLA.PO_HEADER_ID and
471 pha.segment1 = p_po_number and
472 pha.org_id = l_org_id AND
473 pha.TYPE_LOOKUP_CODE = 'STANDARD' and
474 PLA.line_num = p_line_num and
475 PLA.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID (+) AND
476 PLA.PO_LINE_ID = POCR.DOCUMENT_LINE_ID (+) AND
477 POCR.REQUEST_LEVEL (+)= 'LINE' AND
478 POCR.CHANGE_ACTIVE_FLAG (+)= 'Y';
479
480 -- Bug 7287009 - End
481
482 exception
483 when no_data_found then
484 /*
485 PO_LINE_NUM_INVALID = 'The Line number LINENUM for
486 PO PONUM does not exists'.
487 */
488 fnd_message.set_name('PO', 'PO_LINE_NUM_INVALID');
489 fnd_message.set_token('LINENUM', p_line_num, false);
490 fnd_message.set_token('PONUM', p_po_number, false);
491
492 x_error_status_out := x_error_status_out || fnd_message.get;
493
494 x_error_id_out := 2;
495 return;
496
497 when others then
498 x_error_status_out := x_error_status_out ||
499 'Error at finding the line info.' || SQLERRM;
500 x_error_id_out := 2;
501 return;
502 end;
503
504
505 --Validate if there is a quantity change
506 if (p_quantity is not null) then
507 /*
508 PO_LN_QTY_INVALID = 'Changing the quantity at
509 the line level is invalid';
510 */
511 x_error_id_out := 2;
512 x_error_status_out := x_error_status_out ||
513 fnd_message.get_string('PO',
514 'PO_LN_QTY_INVALID');
515 return;
516 end if;
517
518 --Validate there is no change in the currency
519 if (p_price_currency is not null and
520 l_po_curr <> p_price_currency) then
521 /*
522 PO_CURRENCY_CHG_NOT_ALLOWED = 'Existing currency OLD_CUR is not allowed to change to NEW_CUR. Changing currency is not allowed.'
523 */
524 x_error_id_out := 2;
525 fnd_message.set_name('PO', 'PO_CURRENCY_CHG_NOT_ALLOWED');
526 fnd_message.set_token('OLD_CUR', l_po_curr, false);
527 fnd_message.set_token('NEW_CUR', p_price_currency, false);
528 x_error_status_out := x_error_status_out || fnd_message.get;
529 return;
530 end if;
531
532
533 --Validate price change request
534 --Price change is allowed at the line level only for standard po type
535 if (p_po_type = 'RELEASE' and p_price is not null) then
536 /*
537 PO_REL_PRICE_CHANGE_INVALID = 'At line level price cannot be
538 changed for PO Releases.'
539 */
540 x_error_id_out := 2;
541 x_error_status_out := x_error_status_out ||
542 fnd_message.get_string('PO',
543 'PO_REL_PRICE_CHANGE_INVALID');
544 return;
545
546 end if;
547
548 if (p_price is not null and p_price = 0) then
549 /*
550 PO_NEW_PRICE_ZERO_INVALID = 'Price cannot be changed to zero.'
551 */
552 x_error_id_out := 2;
553 x_error_status_out := x_error_status_out ||
554 fnd_message.get_string('PO',
555 'PO_NEW_PRICE_ZERO_INVALID');
556 return;
557
558 end if;
559
560 if (p_price is not null and p_price < 0) then
561 /*
562 PO_PRICE_LT_ZERO = 'The new quantity PRICE is less than
563 zero and it is not allowed.';
564 */
565 fnd_message.set_name('PO', 'PO_PRICE_LT_ZERO');
566 fnd_message.set_token('PRICE', p_price, false);
567 x_error_status_out := x_error_status_out || fnd_message.get;
568
569 x_error_id_out := 2;
570 return;
571 end if;
572
573 --validate the status of the po_line.
574 -- We cannot 'isLineChangable' proc as we need err msg.
575 if (l_cancel_flag is not null and l_cancel_flag = 'Y') then
576 /*
577 PO_CHN_API_LINE_CANCELED = 'The requested line LINENUM for PO
578 PONUM is already cancelled. No change
579 is allowed at this stage.'
580 */
581 x_error_id_out := 2;
582 fnd_message.set_name('PO', 'PO_CHN_API_LINE_CANCELED');
583 fnd_message.set_token('LINENUM', p_line_num, false);
584 fnd_message.set_token('PONUM', p_po_number, false);
585
586
590 end if;
587 x_error_status_out := x_error_status_out || fnd_message.get;
588 return;
589
591
592
593
594 if (l_closed_flag is not null and
595 (l_closed_code = 'CLOSED' or l_closed_code = 'FINALLY CLOSED' or
596 l_closed_code = 'CLOSED FOR RECEIVING' or
597 l_closed_code = 'CLOSED FOR INVOICING')) then
598 /*
599 PO_CHN_API_LINE_CLOSED_CODE = 'The requested line LINENUM for PO
600 PONUM is in the status CLOSEDCODE.
601 No change is allowed at this stage.'
602 */
603 x_error_id_out := 2;
604 fnd_message.set_name('PO', 'PO_CHN_API_LINE_CLOSED_CODE');
605 fnd_message.set_token('LINENUM', p_line_num, false);
606 fnd_message.set_token('PONUM', p_po_number, false);
607 fnd_message.set_token('CLOSEDCODE', l_closed_code, false);
608 end if;
609 if (l_closed_flag is not null and l_closed_flag = 'Y') then
610 /*
611 PO_CHN_API_LINE_CLOSED_FLAG = 'The requested line
612 LINENUM for PO PONUM is already Closed.
613 No change is allowed at this stage.'
614 */
615 x_error_id_out := 2;
616 fnd_message.set_name('PO', 'PO_CHN_API_LINE_CLOSED_FLAG');
617 fnd_message.set_token('LINENUM', p_line_num, false);
618 fnd_message.set_token('PONUM', p_po_number, false);
619
620 x_error_status_out := x_error_status_out || fnd_message.get;
621 return;
622
623 end if;
624
625 /* commnt: If it is RELEASE do not allow changes to supplier_item_number */
626 if (p_po_type = 'RELEASE' and p_supplier_part_num is not null) then
627 /*
628 PO_CHNAPI_RLS_INVALID_PARTNUM = 'The supplier part number
629 cannot be
630 changed to SUPPPARTNUM
631 for a release.';
632 */
633 fnd_message.set_name('PO', 'PO_CHNAPI_RLS_INVALID_PARTNUM');
634 fnd_message.set_token('SUPPPARTNUM', p_supplier_part_num, false);
635 x_error_status_out := x_error_status_out || fnd_message.get;
636
637 x_error_id_out := 2;
638 return;
639 end if;
640
641 if (p_supplier_part_num is not null and
642 l_old_supplier_part_num <> p_supplier_part_num) then
643 p_is_change := 1;
644 l_new_supplier_part_num := p_supplier_part_num;
645 else
646 l_new_supplier_part_num := null;
647 l_old_supplier_part_num := null;
648 end if;
649
650 if (p_price is not null and l_old_price <> p_price) then
651 p_is_change := 1;
652 l_new_price := p_price;
653 else
654 l_new_price := null;
655 l_old_price := null;
656 end if;
657
658 /* Check if there is a change request pending for that line*/
659 if (l_req_status = 'PENDING' or l_req_status = 'BUYER_APP' or
660 l_req_status = 'WAIT_MGR_APP') then
661 /*
662 PO_CHN_API_LINE_CHN_PEN = 'A change request for line LINENUM for
663 PO PONUM is pending. No change is
664 allowed at this stage.'
665 */
666 x_error_id_out := 2;
667 fnd_message.set_name('PO', 'PO_CHN_API_LINE_CHN_PEN');
668 fnd_message.set_token('LINENUM', p_line_num, false);
669 fnd_message.set_token('PONUM', p_po_number, false);
670
671 x_error_status_out := x_error_status_out || fnd_message.get;
672 return;
673
674 end if;
675
676
677 /************** Store Request *********************/
678
679 if (p_is_change = 0 or x_error_id_out <> 0) then
680 return; --There is no change or there is error.
681 end if;
682
683 /* Ignore the revision number sent as requested by CLN team. */
684 select max(REVISION_NUM)
685 into l_max_rev
686 from po_headers_archive_all
687 where PO_HEADER_ID = l_po_header_id;
688
689 if (upper(p_po_type) = 'STANDARD') then
690 l_po_type := 'PO';
691 else
692 l_po_type := p_po_type;
693 end if;
694
695 l_req_status := 'PENDING'; --'PENDING' state, initially.
696 l_stage := 'Store';
697 g_po_change_table.extend(1);
698 l_rec_cur_index := g_po_change_table.last;
699 /* Note N/U in the following comments stands for not
700 used in case of supplier change
701 N/A stands for not applicable in case of LINE level.
702 */
703
704 g_po_change_table(l_rec_cur_index) := PO_CHG_REQUEST_PVT.create_pos_change_rec(
705 p_Action_Type => 'MODIFICATION',
706 p_Initiator => 'SUPPLIER',
707 p_Request_Reason => p_reason,
708 p_Document_Type => l_po_type,
709 p_Request_Level => 'LINE',
710 p_Request_Status => l_req_status,
711 p_Document_Header_Id => l_po_header_id,
712 p_PO_Release_Id => null,
713 p_Document_Num => p_po_number,
717 p_Old_Supplier_Part_Number => l_old_supplier_part_num,
714 p_Document_Revision_Num => l_max_rev,
715 p_Document_Line_Id => l_line_id,
716 p_Document_Line_Number => p_line_num,
718 p_New_Supplier_Part_Number => l_new_supplier_part_num,
719 p_Old_Price => l_Old_Price,
720 p_New_Price => l_new_price
721 );
722
723
724 exception
725 when others then
726 x_error_id_out := 2;
727 x_error_status_out := x_error_status_out ||
728 'in validating the line :' || l_stage ||
729 ': ' || SQLERRM;
730
731
732 end validate_line_change;
733
734
735
736 /********
737 Note this procedure will not check if there is a change pending
738 already in the change tables as if there is a change pending
739 already it would have been validated in the validate header.
740 ********/
741
742 -- Bug 7287009
743 /*Added vendor id and vendor site id in the signature of the procedure
744 Added l_org_id variable to capture org_id*/
745 procedure validate_shipment_change (
746 p_po_number IN varchar2, --PO # of the PO being modified or the Blanket's PO #
747 p_release_number IN number, -- Release number if the PO Type is release or null
748 p_po_type IN varchar2, -- RELEASE or STANDARD.
749 p_revision_num IN number, -- Revision number of the PO or the release
750 p_tp_id IN number, -- vendor_id
751 p_tp_site_id IN number, -- vendor_site_id
752 p_line_num IN number, -- Line number being modified
753 p_shipment_num IN number, -- Shipment number (can be null if the change is at the line)
754 p_quantity IN number, -- The new quantity (can be null)
755 p_quantity_uom IN varchar2, -- The UOM of the new quantity
756 p_price IN number, -- The new price value (can be null)
757 p_price_currency IN varchar2, -- The currency code of the new price (can be null)
758 p_price_uom IN varchar2, -- The UOM code of the new price (can be null)
759 p_promised_date IN date, -- The new promised date (can be null)
760 p_reason IN varchar2,
761 p_ack_type IN varchar2, -- 'ACCEPT' or 'REJECT' or 'MODIFICATION'
762 p_so_line_number IN varchar2,
763 p_is_change IN OUT NOCOPY number,
764 x_error_id_out IN OUT NOCOPY number,
765 x_error_status_out IN OUT NOCOPY varchar2,
766 p_parent_shipment_number number default NULL,
767 p_SUPPLIER_DOC_REF varchar2 default NULL,
768 p_SUPPLIER_LINE_REF varchar2 default NULL,
769 p_SUPPLIER_SHIPMENT_REF varchar2 default NULL
770 )
771 is
772 l_old_quantity number;
773 l_new_quantity number;
774 l_old_promised_date date;
775 l_new_promised_date date;
776 l_old_price number;
777 l_new_price number;
778 l_old_uom varchar2 (10);
779 l_old_price_currency varchar2(10);
780 l_line_price_ovrride varchar2(2);
781 l_line_id number;
782 l_release_id number;
783 l_line_location_id number;
784 l_po_header_id number;
785 l_max_rev number;
786 l_rec_cur_index number := 0;
787 l_qt_recieved number := 0;
788 l_qt_billed number := 0;
789 l_closed_code varchar2 (30);
790 l_closed_flag varchar2(1);
791 l_ack_type varchar2 (30) := null;
792 l_old_so_line_number varchar2(25);
793 l_new_so_line_number varchar2(25);
794 l_req_status varchar2(30);
795 l_po_type varchar2(25);
796 l_shipment_number number;
797 l_parent_line_location_id number;
798 l_shipment_type varchar2(25);
799 l_drop_ship_flag varchar2(1);
800 l_return_status varchar2(1);
801 l_err_msg varchar2(2000);
802 l_po_curr varchar2(10);
803 l_org_id number;
804
805 begin
806
807 if (p_parent_shipment_number is null) then
808 l_shipment_number := p_shipment_num;
809 else
810 l_shipment_number := p_parent_shipment_number;
811 end if;
812
813 if (p_po_type = 'RELEASE') then
814 begin
815 -- Bug 7287009 - Start
816 /*Used vendor id and vendor site id combination to fetch org_id.
817 Org_id is used in the second query's where clause to restrict
818 mutiple records fetch.*/
819
820 SELECT org_id
821 into l_org_id
822 FROM po_vendor_sites_all
823 WHERE vendor_id = p_tp_id
824 AND vendor_site_id = p_tp_site_id;
825 select
826 PLLA.po_release_id, PLLA.LINE_LOCATION_ID,
827 PLLA.PO_LINE_ID, PHA.PO_HEADER_ID,
828 (PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) ORDERED_QUANTITY,
829 PLLA.QUANTITY_RECEIVED, PLLA.QUANTITY_BILLED,
830 --MUOMTL.UOM_CODE UOM,
831 FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY,
832 NVL(PLLA.PROMISED_DATE, PLLA.NEED_BY_DATE) PROMISED_DATE,
833 PLLA.PRICE_OVERRIDE PRICE_OVERRIDE,
834 PLLA.CLOSED_CODE,PLLA.CLOSED_FLAG, PLLA.SUPPLIER_ORDER_LINE_NUMBER,
835 POCR.REQUEST_STATUS, PLA.ALLOW_PRICE_OVERRIDE_FLAG,
836 SHIPMENT_TYPE, DROP_SHIP_FLAG, PHA.CURRENCY_CODE
837 into l_release_id, l_line_location_id, l_line_id, l_po_header_id,
838 l_old_quantity, l_qt_recieved, l_qt_billed,
839 --l_old_uom,
840 l_old_price_currency,
844 l_shipment_type, l_drop_ship_flag, l_po_curr
841 l_old_promised_date, l_old_price,
842 l_closed_code, l_closed_flag, l_old_so_line_number,
843 l_req_status, l_line_price_ovrride,
845 FROM
846 PO_LINE_LOCATIONS_ALL PLLA,
847 PO_LINES_ALL PLA,
848 FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
849 -- MTL_UNITS_OF_MEASURE_TL MUOMTL,
850 PO_RELEASES_ALL PRAA, PO_HEADERS_ALL PHA,
851 PO_CHANGE_REQUESTS POCR
852 WHERE
853 PLA.PO_Line_id= PLLA.PO_Line_ID and
854 (PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) > 0 AND
855 FSPA.ORG_ID = PLLA.ORG_ID AND
856 plla.po_release_id = praa.po_release_id and
857 pha.po_header_id = praa.po_header_id and
858 pha.segment1 = p_po_number and
859 pha.org_id = l_org_id AND
860 praa.release_num = p_release_number and
861 plla.shipment_num = l_shipment_number and
862 PLLA.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID (+) AND
863 PLLA.LINE_LOCATION_ID = POCR.DOCUMENT_LINE_LOCATION_ID (+) AND
864 POCR.REQUEST_LEVEL (+)= 'SHIPMENT' AND
865 POCR.CHANGE_ACTIVE_FLAG (+)= 'Y';
866 -- Bug 7287009 - End
867
868 exception
869 when no_data_found then
870 -- Check if the line number/shipment number valid.
871 /*
872 PO_RLS_SHIP_NUM_INVALID = 'The shipment number SHIPNUM for
873 the release number RELNUM of the
874 blanket PO PONUM does not exists'.
875 */
876 fnd_message.set_name('PO', 'PO_RLS_SHIP_NUM_INVALID');
877 fnd_message.set_token('SHIPNUM', l_shipment_number, false);
878 fnd_message.set_token('PONUM', p_po_number, false);
879 fnd_message.set_token('RELNUM', p_release_number, false);
880
881 x_error_status_out := x_error_status_out || fnd_message.get;
882
883 x_error_id_out := 2;
884 return;
885
886 when others then
887 x_error_status_out := x_error_status_out ||
888 'Error at finding the release old info.' ||
889 SQLERRM;
890 x_error_id_out := 2;
891 return;
892
893 end;
894
895
896 else -- (p_po_type = 'STANDARD')
897 begin
898 -- Bug 7287009 - Start
899 /*Used vendor id and vendor site id combination to fetch org_id.
900 Org_id is used in the second query's where clause to restrict
901 mutiple records fetch.*/
902
903 SELECT org_id
904 into l_org_id
905 FROM po_vendor_sites_all
906 WHERE vendor_id = p_tp_id
907 AND vendor_site_id = p_tp_site_id;
908
909 select
910 null, PLLA.LINE_LOCATION_ID, PLLA.PO_LINE_ID,
911 PHA.PO_HEADER_ID,
912 (PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) ORDERED_QUANTITY,
913 PLLA.QUANTITY_RECEIVED, PLLA.QUANTITY_BILLED,
914 FSPA.PAYMENT_CURRENCY_CODE BUYING_ORG_CURRENCY,
915 NVL(PLLA.PROMISED_DATE, PLLA.NEED_BY_DATE) PROMISED_DATE,
916 PLLA.PRICE_OVERRIDE PRICE_OVERRIDE,
917 PLLA.CLOSED_CODE,PLLA.CLOSED_FLAG,
918 PLLA.SUPPLIER_ORDER_LINE_NUMBER,
919 POCR.REQUEST_STATUS,
920 SHIPMENT_TYPE, DROP_SHIP_FLAG, PHA.CURRENCY_CODE
921 into l_release_id, l_line_location_id, l_line_id,
922 l_po_header_id, l_old_quantity, l_qt_recieved,
923 l_qt_billed, l_old_price_currency,
924 l_old_promised_date, l_old_price,
925 l_closed_code, l_closed_flag, l_old_so_line_number,
926 l_req_status,
927 l_shipment_type, l_drop_ship_flag, l_po_curr
928 FROM
929 PO_LINE_LOCATIONS_ALL PLLA,
930 PO_LINES_ALL PLA,
931 FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
932 PO_HEADERS_ALL PHA,
933 PO_CHANGE_REQUESTS POCR
934 WHERE
935 PLA.PO_Line_id= PLLA.PO_Line_ID and
936 (PLLA.QUANTITY - PLLA.QUANTITY_CANCELLED) > 0 AND
937 FSPA.ORG_ID = PLLA.ORG_ID AND
938 pha.po_header_id = PLLA.PO_HEADER_ID and
939 pha.segment1 = p_po_number and
940 pha.org_id = l_org_id AND
941 pha.TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ') and
942 plla.shipment_num = l_shipment_number and
943 PLA.line_num = p_line_num and
944 PLLA.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID (+) AND
945 PLLA.LINE_LOCATION_ID = POCR.DOCUMENT_LINE_LOCATION_ID (+) AND
946 POCR.REQUEST_LEVEL (+)= 'SHIPMENT' AND
947 POCR.CHANGE_ACTIVE_FLAG (+)= 'Y';
948 -- Bug 7287009 - End
949 exception
950 when no_data_found then
954 PO PONUM and the line number
951 -- Check if the line number/shipment number valid.
952 /*
953 PO_STN_SHIP_NUM_INVALID = 'The shipment number SHIPNUM for the
955 LINENUM does not exists'.
956 */
957 fnd_message.set_name('PO', 'PO_STN_SHIP_NUM_INVALID');
958 fnd_message.set_token('SHIPNUM', l_shipment_number, false);
959 fnd_message.set_token('PONUM', p_po_number, false);
960 fnd_message.set_token('LINENUM', p_line_num, false);
961
962 x_error_status_out := x_error_status_out || fnd_message.get;
963
964 x_error_id_out := 2;
965 return;
966
967 when others then
968 x_error_status_out := x_error_status_out ||
969 'Error at finding the standard old info.' ||
970 SQLERRM;
971 x_error_id_out := 2;
972 return;
973
974 end;
975 end if;
976
977
978
979 if (p_quantity is not null and p_quantity < 0) then
980 /*
981 PO_QTY_LT_ZERO = 'The new quantity QUANTITY is less
982 than zero and it is not allowed.';
983 */
984 fnd_message.set_name('PO', 'PO_QTY_LT_ZERO');
985 fnd_message.set_token('QUANTITY', p_quantity, false);
986
987 x_error_status_out := x_error_status_out || fnd_message.get;
988
989 x_error_id_out := 2;
990 return;
991 end if;
992
993 if (p_quantity is not null and
994 (p_quantity < l_qt_recieved or p_quantity < l_qt_billed)) then
995 /*
996 PO_QTY_LT_RECVD_OR_BILLED = 'The new quantity QUANTITY is less
997 than QTYRECIEVED or QTYBILLED and
998 it is not allowed.';
999 */
1000 fnd_message.set_name('PO', 'PO_QTY_LT_RECVD_OR_BILLED');
1001 fnd_message.set_token('QUANTITY', p_quantity, false);
1002 fnd_message.set_token('QTYRECIEVED', l_qt_recieved, false);
1003 fnd_message.set_token('QTYBILLED', l_qt_billed, false);
1004 x_error_status_out := x_error_status_out || fnd_message.get;
1005
1006 x_error_id_out := 2;
1007 return;
1008 end if;
1009
1010 /***
1011 If p_quantity is zero we need to treat this as cancel?
1012 no commitment fro cln.
1013 If the new quantity of a shipment is zero then change the
1014 action_type to CANCELLATION?. No not a good idea.-FPJ.
1015 Also at this point check if the request has any other changes,
1016 like date, price, etc. then reject.
1017 ***/
1018
1019 if (p_quantity is not null and p_quantity <> l_old_quantity
1020 and p_ack_type <> 'CANCELLATION') then -- added last condition for Bug# 5574841
1021 p_is_change := 1;
1022 l_new_quantity := p_quantity;
1023
1024 else -- if the quantities are same
1025 l_old_quantity := null;
1026 l_new_quantity := null;
1027
1028 end if; -- End of Quantity validations.
1029
1030 -- Check if the price and currency is being changed.
1031 --Validate there is no change in the currency
1032 if (p_price_currency is not null and
1033 l_po_curr <> p_price_currency) then
1034 /*
1035 PO_CURRENCY_CHG_NOT_ALLOWED = 'Existing currency OLD_CUR is not allowed to change to NEW_CUR. Changing currency is not allowed.'
1036 */
1037 x_error_id_out := 2;
1038 fnd_message.set_name('PO', 'PO_CURRENCY_CHG_NOT_ALLOWED');
1039 fnd_message.set_token('OLD_CUR', l_po_curr, false);
1040 fnd_message.set_token('NEW_CUR', p_price_currency, false);
1041 x_error_status_out := x_error_status_out || fnd_message.get;
1042 return;
1043 end if;
1044
1045 if (p_price is not null and p_price <> l_old_price) then
1046 p_is_change := 1;
1047
1048
1049 /*
1050 check if the po type is STANDARD, raise error.
1051 Price at the shipment level can be changed only for releases.
1052 */
1053 if (p_po_type <> 'RELEASE') then
1054 /*
1055 PO_STN_PRICE_CHANGE_INVALID = 'At shipment level price cannot
1056 be changed for STANDARD POs.'
1057 */
1058 x_error_id_out := 2;
1059 x_error_status_out := x_error_status_out ||
1060 fnd_message.get_string('PO', 'PO_STN_PRICE_CHANGE_INVALID');
1061 return;
1062
1063 end if;
1064
1065 if (p_po_type = 'RELEASE' and l_line_price_ovrride = 'N' and
1066 p_price is not null and p_price <> l_old_price
1067 ) then
1068 /*
1069 PO_RLS_PRICE_CHANGE_INVALID = 'At shipment level price cannot
1070 be changed for RELEASES POs as price
1071 override flag is set to no.'
1072 */
1073 x_error_id_out := 2;
1074 x_error_status_out := x_error_status_out ||
1075 fnd_message.get_string('PO', 'PO_RLS_PRICE_CHANGE_INVALID');
1076 return;
1077
1081 /*
1078 end if;
1079
1080 if (p_price is not null and p_price = 0) then
1082 PO_NEW_PRICE_ZERO_INVALID = 'Price cannot be changed to zero.'
1083 */
1084 x_error_id_out := 2;
1085 x_error_status_out := x_error_status_out ||
1086 fnd_message.get_string('PO', 'PO_NEW_PRICE_ZERO_INVALID');
1087 return;
1088
1089 end if;
1090
1091 if (p_price is not null and p_price < 0) then
1092 /*
1093 PO_PRICE_LT_ZERO = 'The new quantity PRICE is less than
1094 zero and it is not allowed.';
1095 */
1096 fnd_message.set_name('PO', 'PO_PRICE_LT_ZERO');
1097 fnd_message.set_token('PRICE', p_price, false);
1098 x_error_status_out := x_error_status_out || fnd_message.get;
1099
1100 x_error_id_out := 2;
1101 return;
1102 end if;
1103 l_new_price := p_price;
1104 else -- if the price is same
1105 l_old_price := null;
1106 l_new_price := null;
1107
1108 end if;
1109
1110 -- Check if the promised date is valid; Doc Submission program will
1111 -- validates this against need-by.
1112 if (p_promised_date is not null) then
1113 p_is_change := 1;
1114 if (p_promised_date < sysdate) then
1115 /*
1116 PO_NEW_PROM_DATE_IS_PAST = 'The requested date NEWPROMDATE is past;
1117 Promised Date should be a future date'
1118 */
1119 x_error_id_out := 2;
1120 fnd_message.set_name('PO', 'PO_NEW_PROM_DATE_IS_PAST');
1121 fnd_message.set_token('NEWPROMDATE',
1122 to_char(p_promised_date, 'MM/DD/YY HH:MI:SS AM'),
1123 false);
1124
1125 x_error_status_out := x_error_status_out || fnd_message.get;
1126 return;
1127
1128 end if;
1129 l_new_promised_date := p_promised_date;
1130 end if;
1131
1132 if (p_promised_date is null or p_promised_date = l_old_promised_date) then
1133 l_new_promised_date := null;
1134 l_old_promised_date := null;
1135 end if;
1136
1137 --Validate if the ack type is accept or reject do not allow changes
1138 if (p_ack_type is null) then
1139 l_ack_type := 'MODIFICATION';
1140 else
1141 l_ack_type := p_ack_type;
1142 end if;
1143
1144
1145 --Validate the shipment status if changes are allowed
1146 if (l_closed_code = 'CLOSED FOR INVOICE' or
1147 l_closed_code = 'CLOSED FOR RECEIVING') then
1148 /*
1149 PO_SHIPMENT_CLOSED_CODE = 'Shipment SHIPMENT for line LINENUM PO PONUM
1150 is closed for invoicing or receiving; Changes
1151 are not allowed at this stage.'
1152 */
1153 x_error_id_out := 2;
1154 fnd_message.set_name('PO', 'PO_SHIPMENT_CLOSED_CODE');
1155 fnd_message.set_token('SHIPMENT', l_shipment_number, false);
1156 fnd_message.set_token('LINENUM', p_line_num, false);
1157 fnd_message.set_token('PONUM', p_po_number, false);
1158 x_error_status_out := x_error_status_out || fnd_message.get;
1159
1160 return;
1161 end if;
1162
1163 if (l_closed_flag is not null and l_closed_flag = 'Y') then
1164 /*
1165 PO_SHIPMENT_CLOSED_FLAG = 'Shipment SHIPMENT for line LINENUM PO PONUM
1166 is closed; Changes are not allowed
1167 at this stage.'
1168 */
1169 x_error_id_out := 2;
1170 fnd_message.set_name('PO', 'PO_SHIPMENT_CLOSED_FLAG');
1171 fnd_message.set_token('SHIPMENT', l_shipment_number, false);
1172 fnd_message.set_token('LINENUM', p_line_num, false);
1173 fnd_message.set_token('PONUM', p_po_number, false);
1174 x_error_status_out := x_error_status_out || fnd_message.get;
1175 return;
1176 end if;
1177
1178 if (p_so_line_number is not null and
1179 p_so_line_number <> l_old_so_line_number) then
1180 p_is_change := 1;
1181 l_new_so_line_number := p_so_line_number;
1182 else
1183 l_new_so_line_number := null;
1184 l_old_so_line_number := null;
1185
1186 end if;
1187
1188 /* Check if there is a change request pending for that line*/
1189 if (l_req_status = 'PENDING' or l_req_status = 'BUYER_APP' or
1190 l_req_status = 'WAIT_MGR_APP') then
1191 /*
1192 PO_CHN_API_SHIP_CHN_PEN = 'A change request for shipment SHIPNUM line
1193 LINENUM for PO PONUM is pending. No change
1194 is allowed at this stage.'
1195 */
1196 x_error_id_out := 2;
1197 fnd_message.set_name('PO', 'PO_CHN_API_SHIP_CHN_PEN');
1198 fnd_message.set_token('SHIPNUM', l_shipment_number, false);
1199 fnd_message.set_token('LINENUM', p_line_num, false);
1200 fnd_message.set_token('PONUM', p_po_number, false);
1201
1202 x_error_status_out := x_error_status_out || fnd_message.get;
1203 return;
1204
1205 end if;
1206
1207 if ((l_ack_type = 'ACCEPT' or l_ack_type = 'REJECT')
1208 and p_is_change = 1) then
1209 /*
1210 PO_ACK_N_CHN_INVALID = 'A Shipment cannot be both acknowledged
1214 x_error_status_out := x_error_status_out ||
1211 and changed at the same time.'
1212 */
1213 x_error_id_out := 2;
1215 fnd_message.get_string('PO', 'PO_ACK_N_CHN_INVALID');
1216 return;
1217 elsif ((l_ack_type = 'ACCEPT' or l_ack_type = 'REJECT') and
1218 p_is_change = 0) then
1219 p_is_change := 2;
1220 end if;
1221
1222 if(p_parent_shipment_number is not null and
1223 (l_shipment_type = 'PRICE BREAK' or l_drop_ship_flag = 'Y')) then
1224 /*
1225 PO_INVALID_SHIPMENT_TYPE = 'A split request is not allowed for shipment SHIPNUM line
1226 LINENUM for PO PONUM as it is a drop ship or has price break.'
1227 */
1228 x_error_id_out := 2;
1229 fnd_message.set_name('PO', 'PO_INVALID_SHIPMENT_TYPE');
1230 fnd_message.set_token('SHIPNUM', l_shipment_number, false);
1231 fnd_message.set_token('LINENUM', p_line_num, false);
1232 fnd_message.set_token('PONUM', p_po_number, false);
1233
1234 x_error_status_out := x_error_status_out || fnd_message.get;
1235 return;
1236
1237 end if;
1238
1239 --Validate request for cancellation of a shipment
1240 if (l_ack_type = 'CANCELLATION' and p_is_change <> 0) then
1241 /*
1242 PO_CANCEL_N_CHANGE_ERR = 'Both change and cancel request for Shipment SHIPNUM, line LINENUM, PO PONUM is not allowed.'
1243 */
1244 x_error_id_out := 2;
1245 fnd_message.set_name('PO', 'PO_CANCEL_N_CHANGE_ERR ');
1246 fnd_message.set_token('SHIPNUM', l_shipment_number, false);
1247 fnd_message.set_token('LINENUM', p_line_num, false);
1248 fnd_message.set_token('PONUM', p_po_number, false);
1249
1250 x_error_status_out := x_error_status_out || fnd_message.get;
1251 return;
1252 end if;
1253
1254 if (l_ack_type = 'CANCELLATION') then
1255 validateCancelRequest( p_api_version => 1.0,
1256 p_init_msg_list => FND_API.G_FALSE,
1257 x_return_status => l_return_status,
1258 p_po_header_id => l_po_header_id,
1259 p_po_release_id => l_release_id
1260 , p_po_line_location_id => l_line_location_id
1261 );
1262
1263
1264 if (l_return_status <> FND_API.g_ret_sts_success) then
1265 /*
1266 PO_CHECK_CANCEL_ERR = 'There was an error ERRCODE in validating the cancel request for
1267 shipment SHIPNUM, line LINENUM, PO PONUM. The error message is: ERRMSG'
1268 */
1269
1270 x_error_id_out := 2;
1271 l_err_msg := FND_MSG_PUB.Get(p_msg_index => 1,p_encoded => 'F');
1272 fnd_message.set_name('PO', 'PO_CHECK_CANCEL_ERR');
1273 fnd_message.set_token('ERRCODE', l_return_status, false);
1274 fnd_message.set_token('SHIPNUM', l_shipment_number, false);
1275 fnd_message.set_token('LINENUM', p_line_num, false);
1276 fnd_message.set_token('PONUM', p_po_number, false);
1277 fnd_message.set_token('ERRMSG', l_err_msg, false);
1278
1279 x_error_status_out := x_error_status_out || fnd_message.get;
1280
1281 return;
1282
1283 end if;
1284 end if;
1285
1286
1287
1288 /*
1289 Note: 1. No need to check if line is canceled or closed, etc.
1290 If so, the shipments are also automatically canceled or closed.
1291 2. No need to check if there is a change pending here.
1292 If so, validate header would have raised the error.
1293 */
1294 /************** Store Request *********************/
1295 if ( x_error_id_out <> 0) then
1296 return;
1297 end if;
1298 if (p_is_change = 0 and l_ack_type <> 'CANCELLATION') then
1299 return; --There is no change or there is error.
1300 end if;
1301
1302 select max(REVISION_NUM)
1303 into l_max_rev
1304 from po_headers_archive_all
1305 where PO_HEADER_ID = l_po_header_id;
1306
1307 if (upper(p_po_type) = 'STANDARD') then
1308 l_po_type := 'PO';
1309 else
1310 l_po_type := p_po_type;
1311 end if;
1312
1313 l_req_status := 'PENDING'; -- 'PENDING' state, initially.
1314 g_po_change_table.extend(1);
1315 l_rec_cur_index := g_po_change_table.last;
1316 /* Note N/U in the following comments stands
1317 for not used in case of supplier change
1318 N/A stands for not applicable in case of SHIPMENT level.
1319 */
1320
1321 /* Added in FPJ for split
1322 If p_parent_shipment_number is not null, then we are splitting.
1323 so, l_document_line_location_id is the parent's line_location_id.
1324 In that case we will pass the
1325 l_document_line_location_id as l_parent_line_location_id.
1326 l_document_line_location_id as null,
1327 set the old values for qt, price, promised date to null
1328 get new value for l_document_shipment_number
1329 */
1330 if (p_parent_shipment_number is not null) then
1331 l_parent_line_location_id := l_line_location_id;
1332 l_line_location_id := null;
1333 --l_shipment_number := null;
1337 l_old_promised_date := null;
1334 getNewShipmentNumber (l_line_id, l_shipment_number);
1335 l_old_price := null;
1336 l_old_quantity := null;
1338 l_old_so_line_number := null;
1339 end if;
1340
1341
1342 g_po_change_table(l_rec_cur_index) := PO_CHG_REQUEST_PVT.create_pos_change_rec(
1343 p_Action_Type => l_ack_type,
1344 p_Initiator => 'SUPPLIER',
1345 p_Request_Reason => p_reason,
1346 p_Document_Type => l_po_type,
1347 p_Request_Level => 'SHIPMENT',
1348 p_Request_Status => l_req_status,
1349 p_Document_Header_Id => l_po_header_id,
1350 p_PO_Release_Id => l_release_id,
1351 p_Document_Num => p_po_number,
1352 p_Document_Revision_Num => l_max_rev,
1353 p_Document_Line_Id => l_line_id,
1354 p_Document_Line_Number => p_line_num,
1355 p_Document_Line_Location_Id => l_line_location_id,
1356 p_Document_Shipment_Number => l_shipment_number ,
1357 p_Parent_Line_Location_Id => l_parent_line_location_id,
1358 p_Old_Quantity => l_old_quantity,
1359 p_New_Quantity => l_new_quantity,
1360 p_Old_Promised_Date => l_old_promised_date,
1361 p_New_Promised_Date => l_new_promised_date,
1362 p_Old_Price => l_Old_Price,
1363 p_New_Price => l_new_price,
1364 p_Old_Supplier_Order_Line_Num => l_old_so_line_number,
1365 p_New_Supplier_Order_Line_Num => l_new_so_line_number,
1366 p_SUPPLIER_DOC_REF => p_SUPPLIER_DOC_REF,
1367 p_SUPPLIER_LINE_REF => p_SUPPLIER_LINE_REF,
1368 p_SUPPLIER_SHIPMENT_REF => p_SUPPLIER_SHIPMENT_REF
1369
1370 );
1371
1372
1373 exception
1374 when others then
1375 x_error_id_out := 2;
1376 x_error_status_out := x_error_status_out || SQLERRM;
1377
1378
1379 end validate_shipment_change;
1380
1381
1382 /*********************** PUBLIC APIs ****************************/
1383
1384 /*
1385 This procedure needs to be called first to initialize an inbound transaction.
1386 This will initialize some global variables. This should be called from the
1387 pre-process of the root node. No errors should occur here lest there is any
1388 weird error, processing should not continue, as it has not been
1389 initialized properly.
1390 */
1391 procedure initialize_chn_ack_inbound (
1392 p_requestor IN varchar2,
1393 p_int_cont_num IN varchar2, -- ECX's ICN.
1394 p_request_origin IN varchar2, -- '9iAS'
1395 p_tp_id IN number, -- vendor_id
1396 p_tp_site_id IN number, -- vendor_site_id
1397 x_error_id OUT NOCOPY number,
1398 x_error_status OUT NOCOPY VARCHAR2
1399
1400
1401 ) is
1402 l_user_id number;
1403 begin
1404 x_error_id := 0;
1405 x_error_status := '';
1406 g_int_cont_num := p_int_cont_num;
1407 g_requestor := p_requestor;
1408 g_request_origin := p_request_origin;
1409
1410 --ideally we should check if the requestor belongs to the tp_id and tp_site_id provided.
1411 begin
1412 select user_id
1413 into l_user_id
1414 from fnd_user
1415 where user_name = p_requestor; --ideally it should be 'XML_USER'
1416 exception when others then
1417 /*
1418 PO_CHN_XML_USER_INVALID = "The USERNAME is not a valid user
1419 in the system.
1420 Please consult your system administrator."
1421 */
1422 fnd_message.set_name('PO', 'PO_CHN_XML_USER_INVALID ');
1423 fnd_message.set_token('USERNAME', p_requestor, false);
1424 x_error_status := x_error_status || fnd_message.get;
1425 x_error_id := 2;
1426 return;
1427
1428 end;
1429
1430
1431 if (g_po_change_table is not null) then
1432 g_po_change_table.delete;
1433 g_po_change_table := null;
1434 end if;
1435 --reinitilize the global variable.
1436 g_po_change_table := pos_chg_rec_tbl();
1437 g_po_type := null;
1438 g_po_number := null;
1439 g_release_number := null;
1440 g_tp_id := null;
1441 g_tp_site_id := null;
1442
1443 exception
1444 when others then
1445 x_error_id := 2;
1446 x_error_status := x_error_status || SQLERRM;
1447
1448 end initialize_chn_ack_inbound;
1449
1450
1451
1452
1453 /*
1454 This API should be called from the in process of the header level.
1455 This will validate the header, if the PO #/Release mentioned belongs
1456 to the vendor and vendor site id,
1457 */
1458 procedure validate_header (
1459 p_requestor IN varchar2,
1460 p_int_cont_num IN varchar2,
1461 p_request_origin IN varchar2, -- XML/OTA/9iAS/OPEN
1462 p_request_type IN varchar2, -- 'CHANGE' or 'ACKNOWLEDGE' or CANCELLATION
1463 p_tp_id IN number, -- vendor_id
1464 p_tp_site_id IN number,
1465 p_po_number IN varchar2,
1466 p_release_number IN number,
1467 p_po_type IN varchar2,
1468 p_revision_num IN number,
1469 x_error_id_in IN number,
1470 x_error_status_in IN VARCHAR2,
1471 x_error_id_out OUT NOCOPY number,
1472 x_error_status_out OUT NOCOPY VARCHAR2 -- Error message
1473
1477 l_rel_status varchar2(25);
1474 ) is
1475 l_count number := 0;
1476 l_po_status varchar2(25);
1478 l_po_header_id number;
1479 l_release_id number := null;
1480 l_org_id number;
1481 --l_can_cancel_status varchar2 (2000) := null;
1482 begin
1483 x_error_id_out := x_error_id_in;
1484 x_error_status_out := x_error_status_in;
1485
1486 -- Bug 7287009 - Start
1487 /*Used vendor id and vendor site id combination to fetch org_id.
1488 Org_id is used in the second query's where clause to restrict
1489 mutiple records fetch.*/
1490
1491 SELECT org_id
1492 into l_org_id
1493 FROM po_vendor_sites_all
1494 WHERE vendor_id = p_tp_id
1495 AND vendor_site_id = p_tp_site_id;
1496
1497 -- Bug 7287009 - End
1498
1499
1500 --Check if the given release exists and belongs to the given TP
1501 if ('RELEASE' = upper(p_po_type) ) then
1502 select count(1) into l_count
1503 from po_releases_all pra, po_headers_all pha
1504 where pra.po_header_id = pha.po_header_id
1505 and pra.release_num = p_release_number
1506 and pha.segment1 = p_po_number
1507 and pha.org_id = l_org_id
1508 and pha.TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
1509
1510 if (l_count < 1) then
1511 /*
1512 PO_REL_NUM_NOT_FOUND = "The RELNUMBER with the blanket PONUMBER
1513 does not belong to the given supplier."
1514 */
1515 --Check: Lets not worry about providing the supplier name.
1516 fnd_message.set_name('PO', 'PO_REL_NUM_NOT_FOUND');
1517 fnd_message.set_token('PONUMBER', p_po_number, false);
1518 fnd_message.set_token('RELNUMBER', p_release_number , false);
1519 x_error_status_out := x_error_status_out || fnd_message.get;
1520 x_error_id_out := 2;
1521 return;
1522 end if;
1523
1524 else -- assume that it is a STANDARD PO
1525 --Check if the given PO exists and belongs to the given TP
1526 select count(1) into l_count
1527 from po_headers_all pha
1528 where pha.segment1 = p_po_number
1529 and pha.org_id = l_org_id
1530 AND pha.TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
1531
1532 if (l_count < 1) then
1533 /*
1534 PO_PO_NUM_NOT_FOUND = "The PO number PONUMBER does not belong to the given supplier."
1535 */
1536 fnd_message.set_name('PO', 'PO_PO_NUM_NOT_FOUND');
1537 fnd_message.set_token('PONUMBER', p_po_number, false);
1538 x_error_status_out := x_error_status_out || fnd_message.get;
1539 x_error_id_out := 2;
1540 return;
1541 end if;
1542 end if;
1543
1544 -- There is a corner case where the same segment1 can
1545 -- belong to more than one org. In that case return error.
1546 if (l_count > 1) then
1547 /*
1548 PO_PO_NUM_AMBIGUOUS = "The PO number PONUMBER is ambiguous.
1549 Please contact system admin."?
1550 */
1551 fnd_message.set_name('PO', 'PO_PO_NUM_AMBIGUOUS');
1552 fnd_message.set_token('PONUMBER', p_po_number, false);
1553 x_error_status_out := x_error_status_out || fnd_message.get;
1554 x_error_id_out := 2;
1555 return;
1556 end if;
1557
1558 --From here onwards PO is Valid
1559
1560 --Check to see if the given PO or Release is in the right status
1561 --to recieve change or acknowledge.
1562
1563 select authorization_status, po_header_id, last_update_date
1564 into l_po_status, l_po_header_id, g_last_upd_date
1565 from po_headers_all
1566 where segment1 = p_po_number
1567 and org_id = l_org_id
1568 and TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
1569
1570 --Common validation for STANDARD and BLANKET PO for both CHANGE and ACKNOWLEDGE
1571 if (l_po_status in ('CANCELLED', 'CLOSED', 'FINALLY CLOSED',
1572 'CLOSED FOR INVOICE', 'CLOSED FOR RECEIVING',
1573 'SUPPLIER_CHANGE_PENDING', 'FROZEN',
1574 'ON HOLD', 'PARTIALLY_ACKNOWLEDGED',
1575 'INTERNAL CHANGE', 'REJECTED',
1576 'IN PROCESS')) then
1577 --billOnlyFlag.equals("Y") ||^M
1578 --!(gaValid.equals("Y")) ||^M)
1579 /*
1580 PO_INVALID_STATUS_CODE = "The PO PONUMBER is currently in
1581 POSTATUSCODE. At this stage no
1582 modifictions to the PO is allowd."
1583 */
1584 fnd_message.set_name('PO', 'PO_INVALID_STATUS_CODE');
1585 fnd_message.set_token('PONUMBER', p_po_number, false);
1586 fnd_message.set_token('POSTATUSCODE', l_po_status, false);
1587
1588 x_error_status_out := x_error_status_out || fnd_message.get;
1589
1590 x_error_id_out := 2;
1591 return;
1592 end if;
1593
1594 if (upper(p_request_type) = 'CHANGE') then
1595 if (l_po_status = 'ACK_REQUIRED') then
1596 /*
1597 PO_STD_INVALID_STATUS_CODE = "The PO PONUMBER is currently in
1598 POSTATUSCODE. At this stage no
1599 modifictions to the PO is allowed."
1600 */
1601 fnd_message.set_name('PO', 'PO_STD_INVALID_STATUS_CODE');
1602 fnd_message.set_token('PONUMBER', p_po_number, false);
1603 fnd_message.set_token('POSTATUSCODE', l_po_status, false);
1604
1608 return;
1605 x_error_status_out := x_error_status_out || fnd_message.get;
1606
1607 x_error_id_out := 2;
1609 end if;
1610 elsif (upper(p_request_type) = 'ACKNOWLEDGE') then
1611 null; -- The common validations should be enough
1612
1613 elsif (upper(p_request_type) = 'CANCELLATION') then
1614 --This will be handled as header level changes.
1615 null;
1616 else
1617 /*
1618 PO_INVALID_REQUEST_TYPE = "The request type REQUESTTYPE is not valid.
1619 It should be CHANGE or ACKNOWLEDGE"
1620 */
1621 fnd_message.set_name('PO', 'PO_INVALID_REQUEST_TYPE');
1622 fnd_message.set_token('REQUESTTYPE', p_request_type, false);
1623 x_error_status_out := x_error_status_out || fnd_message.get;
1624 x_error_id_out := 2;
1625 return;
1626
1627 end if;
1628
1629 /*** Validate the releases ***/
1630 if ('RELEASE' = upper(p_po_type) ) then
1631
1632 select authorization_status, po_release_id, last_update_date
1633 into l_rel_status, l_release_id, g_last_upd_date
1634 from po_releases_all
1635 where po_header_id = l_po_header_id and release_num = p_release_number;
1636
1637 --Common validation for RELEASE for both CHANGE and ACKNOWLEDGE
1638 if (l_rel_status in ('CANCELLED', 'CLOSED', 'FINALLY CLOSED',
1639 'CLOSED FOR INVOICE', 'CLOSED FOR RECEIVING',
1640 'SUPPLIER_CHANGE_PENDING', 'FROZEN',
1641 'ON HOLD', 'PARTIALLY_ACKNOWLEDGED',
1642 'INTERNAL CHANGE', 'REJECTED',
1643 'IN PROCESS')) then
1644 --billOnlyFlag.equals("Y") ||^M
1645 --!(gaValid.equals("Y")) ||^M)
1646 /*
1647 PO_INVALID_STATUS_CODE_REL = "The Release RELNUM for PO PONUMBER
1648 is currently in RELSTATUSCODE.
1649 At this stage no modifictions to
1650 the PO is allowed."
1651 */
1652
1653 fnd_message.set_name('PO', 'PO_INVALID_STATUS_CODE_REL');
1654 fnd_message.set_token('RELNUM', p_release_number, false);
1655 fnd_message.set_token('PONUMBER', p_po_number, false);
1656 fnd_message.set_token('RELSTATUSCODE', l_rel_status, false);
1657
1658 x_error_status_out := x_error_status_out || fnd_message.get;
1659
1660 x_error_id_out := 2;
1661 return;
1662 end if;
1663
1664 if (upper(p_request_type) = 'CHANGE') then
1665 if (l_po_status = 'ACK_REQUIRED') then
1666 /*
1667 PO_INVALID_STATUS_CODE_REL = "The Release RELNUM for PO PONUMBER
1668 is currently in RELSTATUSCODE.
1669 At this stage no modifictions
1670 to the PO is allowed."
1671 */
1672
1673 fnd_message.set_name('PO', 'PO_INVALID_STATUS_CODE_REL');
1674 fnd_message.set_token('RELNUM', p_release_number, false);
1675 fnd_message.set_token('PONUMBER', p_po_number, false);
1676 fnd_message.set_token('RELSTATUSCODE', l_po_status, false);
1677
1678 x_error_status_out := x_error_status_out || fnd_message.get;
1679
1680 x_error_id_out := 2;
1681 return;
1682 end if;
1683 elsif (upper(p_request_type) = 'ACKNOWLEDGE') then
1684 null; -- The common validations for release should be enough
1685 elsif (upper(p_request_type) = 'CANCELLATION') then
1686 --this will be handled as a header level change.
1687 null;
1688
1689 else
1690 /*
1691 PO_INVALID_REQUEST_TYPE = "The request type REQUESTTYPE is not valid.
1692 It should be CHANGE or ACKNOWLEDGE"
1693 */
1694 fnd_message.set_name('PO', 'PO_INVALID_REQUEST_TYPE');
1695 fnd_message.set_token('REQUESTTYPE', p_request_type, false);
1696 x_error_status_out := x_error_status_out || fnd_message.get;
1697 x_error_id_out := 2;
1698 return;
1699
1700 end if;
1701
1702
1703 end if; -- if p_po_type = 'RELEASE'
1704
1705
1706 --update the global variables
1707 g_po_type := p_po_type;
1708 g_po_number := p_po_number;
1709 g_release_number := p_release_number;
1710 g_tp_id := p_tp_id;
1711 g_tp_site_id := p_tp_site_id;
1712
1713 /*** Handle Sales Order Change ***/
1714
1715
1716 exception
1717 when others then
1718 x_error_id_out := 2;
1719 x_error_status_out := x_error_status_out || SQLERRM;
1720
1721
1722 end;
1723
1724 /*
1725 This API should be called from the in process of the lines.
1726 This procedure needs to be called in the following scenarios:
1727 1. Modifications to a PO at the shipment level
1728 2. Modifications to a PO at the line level
1729 3. Acknowledgment at the shipment level
1730 4. Canceling at the shipment level
1731 Calls to this API will be stored in a pl/sql table and will
1732 not be processed immediately.
1733 Call process_supplier_request to process the request.
1734 */
1735 procedure store_supplier_request (
1736 p_requestor IN varchar2,
1737 p_int_cont_num IN varchar2,
1741 p_level IN varchar2,
1738 p_request_type IN varchar2,
1739 p_tp_id IN number,
1740 p_tp_site_id IN number,
1742 p_po_number IN varchar2,
1743 p_release_number IN number,
1744 p_po_type IN varchar2,
1745 p_revision_num IN number,
1746 p_line_num IN number,
1747 p_reason IN varchar2,
1748 p_shipment_num IN number,
1749 p_quantity IN number,
1750 p_quantity_uom IN varchar2,
1751 p_price IN number,
1752 p_price_currency IN varchar2,
1753 p_price_uom IN varchar2,
1754 p_promised_date IN date,
1755 p_supplier_part_num IN varchar2,
1756 p_so_number IN varchar2,
1757 p_so_line_number IN varchar2,
1758 p_ack_type IN varchar2,
1759 x_error_id_in IN number,
1760 x_error_status_in IN varchar2,
1761 x_error_id_out OUT NOCOPY number,
1762 x_error_status_out OUT NOCOPY varchar2,
1763 p_parent_shipment_number number default NULL,
1764 p_SUPPLIER_DOC_REF varchar2 default NULL,
1765 p_SUPPLIER_LINE_REF varchar2 default NULL,
1766 p_SUPPLIER_SHIPMENT_REF varchar2 default NULL
1767 ) is
1768 l_is_change number :=0;
1769 l_reason varchar2(2000);
1770 begin
1771 x_error_id_out := x_error_id_in;
1772 x_error_status_out := x_error_status_in;
1773
1774 -- validate parameters with the global variables.
1775
1776 if ( p_int_cont_num <> g_int_cont_num OR
1777 p_po_number <> g_po_number OR
1778 p_po_type <> g_po_type OR
1779 p_tp_id <> g_tp_id OR
1780 p_tp_site_id <> g_tp_site_id) then
1781 x_error_id_out := 2;
1782 x_error_status_out := x_error_status_out ||
1783 fnd_message.get_string('PO', 'PO_CHG_API_CALL_NOT_SYNC');
1784 return;
1785 end if;
1786
1787 /************** Validations ***********************/
1788 --Check the length of the reason;
1789 --it should not be more than 2000 bytes; if so truncate it.
1790 if (p_reason is null) then
1791
1792 if (p_level = 'SHIPMENT') then
1793 /*
1794 PO_CHG_API_SH_REASON_IS_NULL = Reason cannot be null for
1795 line LINENUM and shipment SHIPNUM
1796 */
1797 fnd_message.set_name('PO', 'PO_CHG_API_SH_REASON_IS_NULL');
1798 fnd_message.set_token('LINENUM', p_line_num, false);
1799 fnd_message.set_token('SHIPNUM', p_shipment_num, false);
1800 elsif (p_level = 'LINE') then
1801 /*
1802 PO_CHG_API_LN_REASON_IS_NULL = Reason cannot be null for
1803 line LINENUM
1804 */
1805 fnd_message.set_name('PO', 'PO_CHG_API_LN_REASON_IS_NULL');
1806 fnd_message.set_token('LINENUM', p_line_num, false);
1807 else --if p_level = HEADER
1808 /*
1809 PO_CHG_API_REASON_IS_NULL Reason cannot be null for PONUM
1810 */
1811 fnd_message.set_name('PO', 'PO_CHG_API_REASON_IS_NULL');
1812 fnd_message.set_token('PONUM', p_po_number, false);
1813 end if;
1814
1815 x_error_status_out := x_error_status_out || fnd_message.get;
1816 x_error_id_out := 2;
1817
1818
1819 return;
1820
1821 end if;
1822
1823
1824 if (p_reason is not null) then
1825 l_reason := substr(ltrim(rtrim(p_reason, ' , '), ' , '), 1, 2000);
1826 else
1827 l_reason := p_reason;
1828 end if;
1829
1830 -- For shipment level validations
1831 -- Bug 7287009 - Start
1832 /*Added vendor id and vendor site id in the parameter list*/
1833 if (p_level = 'SHIPMENT') then
1834 validate_shipment_change (p_po_number, p_release_number,
1835 p_po_type, p_revision_num,
1836 p_tp_id, p_tp_site_id,
1837 p_line_num, p_shipment_num, p_quantity,
1838 p_quantity_uom, p_price, p_price_currency,
1839 p_price_uom, p_promised_date, l_reason,
1840 p_ack_type, p_so_line_number, l_is_change,
1841 x_error_id_out, x_error_status_out,
1842 p_parent_shipment_number,
1843 p_SUPPLIER_DOC_REF, p_SUPPLIER_LINE_REF,
1844 p_SUPPLIER_SHIPMENT_REF
1845 );
1846 -- Bug 7287009 - End
1847 if (x_error_id_out <> 0) then
1848 return;
1849 end if;
1850 end if;
1851
1852 -- Bug 7287009 - Start
1853 /*Added vendor id and vendor site id in the parameter list*/
1854 if (p_level = 'LINE') then
1855 validate_line_change(p_po_number, p_release_number,
1856 p_po_type, p_revision_num,
1857 p_tp_id, p_tp_site_id,
1858 p_line_num, p_quantity,
1859 p_quantity_uom, p_price, p_price_currency, p_price_uom,
1860 p_supplier_part_num, l_reason,
1861 l_is_change, x_error_id_out, x_error_status_out
1862 );
1863 -- Bug 7287009 - End
1864
1865 if (x_error_id_out <> 0) then
1866 return;
1867 end if;
1868 end if;
1869
1870 -- Bug 7287009 - Start
1871 /*Added vendor id and vendor site id in the parameter list*/
1872
1873 if (p_level = 'HEADER') then
1874 handle_header_level_requests(p_po_number, p_release_number,
1878 l_is_change, x_error_id_out, x_error_status_out
1875 p_po_type, p_revision_num,
1876 p_tp_id, p_tp_site_id,
1877 p_reason, p_ack_type, p_so_number,
1879 );
1880 -- Bug 7287009 - End
1881
1882 end if;
1883
1884
1885
1886
1887 exception
1888 when others then
1889 x_error_id_out := 2;
1890 x_error_status_out := x_error_status_out || SQLERRM;
1891
1892 end store_supplier_request;
1893
1894 /*
1895 Call this procedure from the post-process before calling the wind-up API
1896 only if the error_id from the earlier calls is 0. This API should not
1897 be called if the request is for header level acknowledgment.
1898 This API will place the supplier request in the change request
1899 table and kicks-off the workflow for the approval of the change request.
1900 */
1901 procedure process_supplier_request (
1902 p_int_cont_num IN varchar2,
1903 x_error_id_in IN number,
1904 x_error_status_in IN varchar2,
1905 x_error_id_out OUT NOCOPY number,
1906 x_error_status_out OUT NOCOPY VARCHAR2
1907 ) is
1908 l_pos_errors POS_ERR_TYPE;
1909 l_po_header_id number;
1910 l_po_release_id number;
1911 l_revision_num number;
1912 l_online_report_id number := 0;
1913 i number;
1914 l_po_tbl_varchar2000 po_tbl_varchar2000 := null;
1915 l_err_count number := 0;
1916 l_user_id number;
1917
1918 begin
1919 x_error_id_out := x_error_id_in;
1920 x_error_status_out := x_error_status_in;
1921
1922 --dbms_output.put_line('x_error_status_out inside1 : ' || x_error_status_out);
1923
1924 --validate the global information
1925
1926 if ( p_int_cont_num <> g_int_cont_num ) then
1927 x_error_id_out := 2;
1928 x_error_status_out := x_error_status_out ||
1929 fnd_message.get_string('PO', 'PO_CHG_API_CALL_NOT_SYNC');
1930 return;
1931 end if;
1932
1933
1934 if ( g_po_change_table is null OR g_po_change_table.count = 0) then
1935 x_error_id_out := 0; --No change found.Do not error;but do not process.
1936 x_error_status_out := x_error_status_out ||
1937 fnd_message.get_string('PO', 'PO_CHG_API_NO_CHANGE_FOUND');
1938 return;
1939 end if;
1940
1941 --dbms_output.put_line('x_error_status_out inside2 : ' || x_error_status_out);
1942
1943 l_po_header_id := g_po_change_table(g_po_change_table.FIRST).Document_Header_Id;
1944 l_po_release_id := g_po_change_table(g_po_change_table.FIRST).PO_Release_Id;
1945 l_revision_num := g_po_change_table(g_po_change_table.FIRST).Document_Revision_Num;
1946 select user_id
1947 into l_user_id
1948 from fnd_user
1949 where user_name = g_requestor;
1950
1951 --dbms_output.put_line('x_error_status_out inside3 : ' || x_error_status_out);
1952 PO_CHG_REQUEST_PVT.process_supplier_request (
1953 p_po_header_id =>l_po_header_id,
1954 p_po_release_id => l_po_release_id,
1955 p_revision_num =>l_revision_num,
1956 p_po_change_requests => g_po_change_table,
1957 x_online_report_id => l_online_report_id,
1958 x_pos_errors => l_pos_errors,
1959 p_chn_int_cont_num => g_int_cont_num,
1960 p_chn_source => g_request_origin,
1961 p_chn_requestor_username => g_requestor,
1962 p_user_id => l_user_id,
1963 p_login_id => l_user_id,
1964 p_last_upd_date => g_last_upd_date) ;
1965
1966
1967
1968 /*
1969 FOR i IN l_pos_errors.FIRST..l_pos_errors.LAST LOOP
1970 x_error_id_out := 2;
1971 x_error_status_out := x_error_status_out ||
1972 l_pos_errors(i).text_line; --buffer overflow?
1973 end loop;
1974 */
1975
1976 if (l_pos_errors is not null) then
1977 l_po_tbl_varchar2000 := l_pos_errors.text_line;
1978
1979 --dbms_output.put_line('x_error_status_out inside5 : '
1980 --|| x_error_status_out);
1981 if (l_po_tbl_varchar2000 is not null ) then
1982 --dbms_output.put_line('x_error_status_out inside5^ : '
1983 --|| x_error_status_out);
1984 l_err_count := l_po_tbl_varchar2000.count;
1985 end if;
1986
1987 if (l_err_count > 0) then
1988 --dbms_output.put_line('x_error_status_out inside5^^ : '
1989 --|| x_error_status_out);
1990
1991 FOR i IN 1..l_err_count LOOP
1992 x_error_id_out := 2;
1993 --dbms_output.put_line('x_error_status_out inside6 : '
1994 --|| x_error_status_out);
1995
1996 x_error_status_out := x_error_status_out || l_po_tbl_varchar2000(i);
1997 --dbms_output.put_line('x_error_status_out inside7 : '
1998 --|| x_error_status_out);
1999 end loop;
2000 end if;
2001 end if;
2002 --dbms_output.put_line('x_error_status_out inside8 : '
2003 --|| x_error_status_out);
2004 exception
2005 when others then
2006 x_error_id_out := 2;
2007
2008
2009 begin
2010 --dbms_output.put_line('x_error_status_out inside9 : ' ||
2011 -- x_error_status_out);
2012 x_error_status_out := x_error_status_out || SQLERRM;
2013 --dbms_output.put_line('x_error_status_out inside10 : ' ||
2014 --x_error_status_out);
2015 exception --may errorout due to bufferoverflow.
2016 when others then
2017 return;
2018 end;
2019
2020
2021 end process_supplier_request;
2022
2023 /*
2024 This procedure needs to be called from Acknowledge PO inbound
2025 at in_process of the header, only when the PO is acknowledged
2026 at the header level. In case of shipment level acknowledgement
2027 this procedure should not be called. The acknowledge po request
2028 will be processed immediately and only once per transaction.
2032 p_requestor IN varchar2,
2029 So, no need for error_id_in and error_id_out etc.
2030 */
2031 procedure acknowledge_po(
2033 p_int_cont_num IN varchar2,
2034 p_request_type IN varchar2,
2035 p_tp_id IN number,
2036 p_tp_site_id IN number,
2037 p_po_number IN varchar2,
2038 p_release_number IN number,
2039 p_po_type IN varchar2,
2040 p_revision_num IN number,
2041 p_ack_code IN number, -- 0 for accept 2 reject
2042 p_ack_reason IN varchar2,
2043 x_error_id OUT NOCOPY number,
2044 x_error_status OUT NOCOPY VARCHAR2
2045
2046 ) is
2047 l_po_header_id VARCHAR2(30);
2048 l_po_release_id VARCHAR2(30) := null;
2049 l_po_buyer_id VARCHAR2(30);
2050 l_po_accept_reject VARCHAR2(30);
2051 l_po_acc_type_code VARCHAR2(30);
2052 l_po_ack_comments VARCHAR2(2000);
2053 l_user_id VARCHAR2(30);
2054 l_org_id number;
2055
2056 --Bug 6850595
2057 /*Added the follwoing variables.
2058 l_last_update_date is added to check the concurrency when the same PO is modified in different places.
2059 x_error is added to check if there are any concurrency issues. If x_error is true then it will raise
2060 concurrency exception.
2061 l_concurrency_exception is added to raise the exception in case if there are any concurrency issues. */
2062
2063 l_last_update_date po_headers_all.last_update_date%type;
2064 x_error VARCHAR2(30);
2065 l_concurrency_exception EXCEPTION;
2066
2067 begin
2068
2069 /*
2070 Note: The header is validated already at the validate_header stage.
2071 */
2072 -- Bug 7287009 - Start
2073 /*Used vendor id and vendor site id combination to fetch org_id.
2074 Org_id is used in the second query's where clause to restrict
2075 mutiple records fetch.*/
2076
2077 SELECT org_id
2078 into l_org_id
2079 FROM po_vendor_sites_all
2080 WHERE vendor_id = p_tp_id
2081 AND vendor_site_id = p_tp_site_id;
2082
2083 select to_char(po_header_id), to_char(agent_id), LAST_UPDATE_DATE
2084 into l_po_header_id, l_po_buyer_id, l_last_update_date
2085 from po_headers_all
2086 where segment1 = p_po_number
2087 and org_id = l_org_id
2088 AND TYPE_LOOKUP_CODE NOT IN ('QUOTATION','RFQ');
2089 -- Bug 7287009 - End
2090
2091 select to_char(user_id)
2092 into l_user_id
2093 from fnd_user
2094 where user_name = p_requestor;
2095
2096 if (p_po_type = 'RELEASE') then
2097 select to_char(po_release_id), to_char(agent_id), LAST_UPDATE_DATE
2098 into l_po_release_id, l_po_buyer_id, l_last_update_date
2099 from po_releases_all praa
2100 where po_header_id = l_po_header_id
2101 and RELEASE_NUM = p_release_number;
2102 end if;
2103
2104 if (p_ack_code = 0) then
2105 l_po_accept_reject := 'Y';
2106 elsif (p_ack_code = 2) then
2107 l_po_accept_reject := 'N';
2108 elsif (p_ack_code = 1) then
2109 /*
2110 PO_CHN_API_ACK_CODE_1 = 'Accept with changes is not supported
2111 through this API; Context: PONUM'
2112 */
2116
2113 x_error_id := 2;
2114 fnd_message.set_name('PO', 'PO_CHN_API_ACK_CODE_1');
2115 fnd_message.set_token('PONUM', p_po_number, false);
2117 x_error_status := x_error_status || fnd_message.get;
2118 return;
2119 else
2120 /*
2121 PO_CHN_API_ACK_CODE_INVALID = 'AckCode of ACKCODE is invalid.
2122 It shoule either 0 for ACCEPT
2123 or 2 for REJECT; Context: PONUM'
2124 */
2125 x_error_id := 2;
2126 fnd_message.set_name('PO', 'PO_CHN_API_ACK_CODE_INVALID');
2127 fnd_message.set_token('ACKCODE', p_po_number, false);
2128 fnd_message.set_token('PONUM', p_po_number, false);
2129
2130 x_error_status := x_error_status || fnd_message.get;
2131 return;
2132 end if;
2133
2134 --Bug 6850595
2135 /* l_last_update_date is passed as an input parameter to check the
2136 concurrency issue. x_error is an out variable, which captures errors
2137 if there are any exceptions. */
2138
2139 POS_ACK_PO.ACKNOWLEDGE_PO (
2140 l_po_header_id,
2141 l_po_release_id,
2142 l_po_buyer_id,
2143 l_po_accept_reject,
2144 l_po_acc_type_code, --Should be always null from FPI.
2145 p_ack_reason,
2146 l_user_id,
2147 l_last_update_date,
2148 x_error);
2149
2150 IF x_error = 'true' THEN
2151 RAISE l_concurrency_exception;
2152 END IF;
2153
2154 exception
2155 when others then
2156 x_error_id := 2;
2157 x_error_status := x_error_status || SQLERRM;
2158 end acknowledge_po;
2159
2160 /*
2161 Call this procedure at the post process stage as the last action.
2162 At this point the pl/sql table will be 'delete'd. Call this
2163 procedure even if there were errors in the earlier calls.
2164 */
2165 procedure windup_chn_ack_inbound (
2166 p_requestor IN varchar2,
2167 p_int_cont_num IN varchar2,
2168 p_request_origin IN varchar2,
2169 p_tp_id IN number,
2170 p_tp_site_id IN number,
2171 x_error_id_in IN number,
2172 x_error_status_in IN varchar2,
2173 x_error_id_out OUT NOCOPY number,
2174 x_error_status_out OUT NOCOPY VARCHAR2
2175 ) is
2176 begin
2177 /* We have no use of these global variables; so erase them;
2178 */
2179
2180 x_error_id_out := x_error_id_in;
2181 x_error_status_out := x_error_status_in;
2182
2183 g_int_cont_num := null;
2184
2185 if (g_po_change_table is not null) then
2186 g_po_change_table.delete;
2187 g_po_change_table := null;
2188 end if;
2189
2190 g_po_type := null;
2191 g_po_number := null;
2192 g_release_number := null;
2193 g_tp_id := null;
2194 g_tp_site_id := null;
2195
2196 exception
2197 when others then
2198 x_error_id_out := 2;
2199 x_error_status_out := x_error_status_out || SQLERRM;
2200 end windup_chn_ack_inbound;
2201
2202 end PO_CHG_REQUEST_GRP;
2203
2204
2205
[Home] [Help]