[Home] [Help]
PACKAGE BODY: APPS.CLN_PO_CHANGE_ORDER
Source
1 PACKAGE BODY CLN_PO_CHANGE_ORDER AS
2 /* $Header: CLNPOCOB.pls 115.4 2004/04/20 15:24:51 kkram noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4 TYPE t_line_num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 l_line_num_tab t_line_num_tab;
6
7 -- Package
8 -- CLN_PO_CHANGE_ORDER
9 --
10 -- Purpose
11 -- Specification of package body: CLN_PO_CHANGE_ORDER.
12 -- This package functions facilitate in updating the Purchase order
13 --
14 -- History
15 -- Aug-06-2002 Viswanthan Umapathy Created
16
17
18 -- Name
19 -- IS_ALREADY_PROCESSED_LINE
20 -- Purpose
21 -- Checks whether a line is already processed or not
22 -- Arguments
23 -- PO Line Num
24
25
26 FUNCTION IS_ALREADY_PROCESSED_LINE(
27 p_line_num IN VARCHAR2)
28 RETURN BOOLEAN
29 IS
30 i binary_integer;
31 BEGIN
32 IF (l_Debug_Level <= 2) THEN
33 cln_debug_pub.Add('ENTERING PROCESS_ORDER_HEADER,p_requestor:' || p_line_num, 2);
34 END IF;
35
36 i := l_line_num_tab.first();
37 while i is not null loop
38 IF (l_Debug_Level <= 1) THEN
39 cln_debug_pub.Add('next element:' || l_line_num_tab(i), 1);
40 END IF;
41 IF (l_line_num_tab(i) = p_line_num ) THEN
42 IF (l_Debug_Level <= 2) THEN
43 cln_debug_pub.Add('EXITING PROCESS_ORDER_HEADER:Line is duplicate', 1);
44 END IF;
45 RETURN TRUE;
46 END IF;
47 i := l_line_num_tab.next(i);
48 end loop;
49 l_line_num_tab(l_line_num_tab.count()+1) := p_line_num;
50 IF (l_Debug_Level <= 2) THEN
51 cln_debug_pub.Add('EXITING PROCESS_ORDER_HEADER:Line is not duplicate', 1);
52 END IF;
53 RETURN FALSE;
54 END;
55
56
57
58 -- Name
59 -- PROCESS_ORDER_HEADER
60 -- Purpose
61 -- Validates PO Header details and updates the collaboration based on PO Header Details
62 -- Arguments
63 -- PO Header details
64 -- Notes
65 -- No specific notes
66 --
67
68
69 PROCEDURE PROCESS_ORDER_HEADER (
70 p_requestor IN VARCHAR2,
71 p_int_cont_num IN VARCHAR2,
72 p_app_ref_id IN VARCHAR2,
73 p_request_origin IN VARCHAR2,
74 p_request_type IN VARCHAR2,
75 p_tp_id IN NUMBER,
76 p_tp_site_id IN NUMBER,
77 p_po_number IN VARCHAR2,
78 p_so_number IN VARCHAR2,
79 p_release_number IN NUMBER,
80 p_po_type IN VARCHAR2,
81 p_revision_num IN NUMBER,
82 x_error_id_in IN NUMBER,
83 x_error_status_in IN VARCHAR2,
84 x_error_id_out OUT NOCOPY NUMBER,
85 x_error_status_out OUT NOCOPY VARCHAR2
86 )
87 IS
88 l_po_type varchar2(50);
89 l_return_status VARCHAR2(1000);
90 l_return_msg VARCHAR2(2000);
91 l_debug_mode VARCHAR2(300);
92 l_error_code NUMBER;
93 l_error_msg VARCHAR2(2000);
94 l_msg_text VARCHAR2(1000);
95 l_error_id NUMBER;
96 l_error_status VARCHAR2(1000);
97 BEGIN
98 -- Sets the debug mode to be FILE
99 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
100
101 -- Initialize API return status to success
102 x_error_id_out := 0;
103 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
104 x_error_status_out := FND_MESSAGE.GET;
105
106 IF (l_Debug_Level <= 2) THEN
107 cln_debug_pub.Add('ENTERING PROCESS_ORDER_HEADER', 2);
108 END IF;
109
110 IF (l_Debug_Level <= 1) THEN
111 cln_debug_pub.Add('With the following parameters:', 1);
112 cln_debug_pub.Add('p_requestor:' || p_requestor, 1);
113 cln_debug_pub.Add('p_int_cont_num:' || p_int_cont_num, 1);
114 cln_debug_pub.Add('p_app_ref_id:' || p_app_ref_id, 1);
115 cln_debug_pub.Add('p_request_origin:' || p_request_origin, 1);
116 cln_debug_pub.Add('p_request_type:' || p_request_type, 1);
117 cln_debug_pub.Add('p_tp_id:' || p_tp_id, 1);
118 cln_debug_pub.Add('p_tp_site_id:' || p_tp_site_id, 1);
119 cln_debug_pub.Add('p_po_number:' || p_po_number, 1);
120 cln_debug_pub.Add('p_so_number:' || p_so_number, 1);
121 cln_debug_pub.Add('p_release_number:' || p_release_number, 1);
122 cln_debug_pub.Add('p_revision_num:' || p_revision_num, 1);
123 cln_debug_pub.Add('x_error_id_in:' || x_error_id_in, 1);
124 cln_debug_pub.Add('x_error_status_in:' || x_error_status_in, 1);
125 cln_debug_pub.Add('x_error_id_out:' || x_error_id_out, 1);
126 cln_debug_pub.Add('x_error_status_out:' || x_error_status_out, 1);
127
128 cln_debug_pub.Add('G_ERROR_ID:' || G_ERROR_ID, 1);
129 cln_debug_pub.Add('G_ERROR_MESSAGE:' || G_ERROR_MESSAGE, 1);
130 END IF;
131
132
133 -- Intialize call may be failed, hence consider the error id
134 G_ERROR_ID := x_error_id_in;
135 G_ERROR_MESSAGE := x_error_status_in;
136
137 l_line_num_tab.delete;-- Initialize array of PO lines
138
139 SAVEPOINT PO_UPDATE_TXN;
140
141 IF (l_Debug_Level <= 1) THEN
142 cln_debug_pub.Add('G_ERROR_ID:' || G_ERROR_ID, 1);
143 cln_debug_pub.Add('G_ERROR_MESSAGE:' || G_ERROR_MESSAGE, 1);
144 END IF;
145
146
147 -- If and only if none has failed until now
148 IF G_ERROR_ID IS NULL OR G_ERROR_ID = 0 THEN
149 -- Document Processed
150 FND_MESSAGE.SET_NAME('CLN','CLN_DOCUMENT_PROCESSED');
151 l_msg_text := FND_MESSAGE.GET;
152 IF (l_Debug_Level <= 1) THEN
153 cln_debug_pub.Add('l_msg_text:' || l_msg_text, 1);
154 END IF;
155
156 RAISE_UPDATE_COLLABORATION(
157 x_return_status => l_return_status,
158 x_msg_data => l_return_msg,
159 p_ref_id => p_app_ref_id,
160 p_doc_no => p_po_number,
161 p_part_doc_no => p_so_number,
162 p_msg_text => l_msg_text,
163 p_status_code => 0,
164 p_int_ctl_num => p_int_cont_num);
165 IF l_return_status <> 'S' THEN
166 IF (l_Debug_Level <= 1) THEN
167 cln_debug_pub.Add('RAISE_UPDATE_COLLABORATION CALL FAILED',1);
168 END IF;
169
170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171 END IF;
172
173 -- Identify PO Type based on release number
174 l_po_type := 'STANDARD';
175 IF (p_release_number IS NOT NULL AND p_release_number > 0) THEN
176 l_po_type := 'RELEASE';
177 END IF;
178 IF (l_Debug_Level <= 1) THEN
179 cln_debug_pub.Add('l_po_type:' || l_po_type,1);
180 END IF;
181
182
183 PO_CHG_REQUEST_GRP.validate_header (
184 p_requestor => p_requestor,
185 p_int_cont_num => p_int_cont_num,
186 p_request_origin => p_request_origin,
187 p_request_type => 'CHANGE',
188 p_tp_id => p_tp_id,
189 p_tp_site_id => p_tp_site_id,
190 p_po_number => p_po_number,
191 p_release_number => p_release_number,
192 p_po_type => l_po_type,
193 -- Should not pass revision number
194 p_revision_num => NULL,
195 x_error_id_in => l_error_id,
196 x_error_status_in => l_error_status,
197 x_error_id_out => l_error_id,
198 x_error_status_out => l_error_status);
199 IF (l_Debug_Level <= 1) THEN
200 cln_debug_pub.Add('l_error_id:' || l_error_id,1);
201 cln_debug_pub.Add('l_error_status:' || l_error_status,1);
202 END IF;
203
204
205 -- If validate header errored out
206 IF l_error_id IS NOT NULL AND l_error_id <> 0 THEN
207 -- Assaign global error info and set return values
208 G_ERROR_ID := l_error_id;
209 G_ERROR_MESSAGE := l_error_status;
210 x_error_id_out := l_error_id;
211 x_error_status_out:= l_error_status;
212 IF (l_Debug_Level <= 1) THEN
213 cln_debug_pub.Add('G_ERROR_ID:' || G_ERROR_ID, 1);
214 cln_debug_pub.Add('G_ERROR_MESSAGE:' || G_ERROR_MESSAGE, 1);
215 cln_debug_pub.Add('validate_header call failed',1);
216 END IF;
217
218 ROLLBACK TO PO_UPDATE_TXN;
219 IF (l_Debug_Level <= 1) THEN
220 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction');
221 END IF;
222
223
224 RAISE_UPDATE_COLLABORATION(
225 x_return_status => l_return_status,
226 x_msg_data => l_return_msg,
227 p_ref_id => p_app_ref_id,
228 p_doc_no => p_po_number,
229 p_part_doc_no => p_so_number,
230 p_msg_text => l_error_status,
234 IF (l_Debug_Level <= 1) THEN
231 p_status_code => 1,
232 p_int_ctl_num => p_int_cont_num);
233 IF l_return_status <> 'S' THEN
235 cln_debug_pub.Add('RAISE_UPDATE_COLLABORATION CALL FAILED',1);
236 END IF;
237
238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
239 END IF;
240
241 RAISE_ADD_MESSAGE(
242 x_return_status => l_return_status,
243 x_msg_data => l_return_msg,
244 p_ictrl_no => p_int_cont_num,
245 p_ref1 => to_char(p_release_number),
246 p_ref2 => p_so_number,
247 p_ref3 => NULL,
248 p_ref4 => NULL,
249 p_ref5 => NULL,
250 p_dtl_msg => NULL);
251 IF l_return_status <> 'S' THEN
252 IF (l_Debug_Level <= 1) THEN
253 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
254 END IF;
255
256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
257 END IF;
258
259 IF (l_Debug_Level <= 1) THEN
260 cln_debug_pub.Add(x_error_status_out, 1);
261 END IF;
262
263 IF (l_Debug_Level <= 2) THEN
264 cln_debug_pub.Add('EXITING PROCESS_ORDER_HEADER', 2);
265 END IF;
266
267 RETURN;
268 END IF; -- IF Error ID is not zero
269 ELSE -- If Global Error ID is not zero
270 -- Set return values
271 x_error_id_out := G_ERROR_ID;
272 x_error_status_out := G_ERROR_MESSAGE;
273 END IF; -- if G_ERROR_ID is not zero
274 IF (l_Debug_Level <= 2) THEN
275 cln_debug_pub.Add('EXITING PROCESS_ORDER_HEADER', 2);
276 END IF;
277
278
279 EXCEPTION
280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
281 ROLLBACK TO PO_UPDATE_TXN;
282 IF (l_Debug_Level <= 5) THEN
283 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
284 END IF;
285
286 -- Assaign global error info and set return values
287 x_error_id_out := -1;
288 G_ERROR_ID := -1;
289 x_error_status_out := l_return_msg;
290 G_ERROR_MESSAGE := l_return_msg;
291 IF (l_Debug_Level <= 5) THEN
292 cln_debug_pub.Add(x_error_status_out, 3);
293 END IF;
294
295 x_error_status_out := 'While trying to process order header'
296 || ' for the inbound change sales order#'
297 || p_so_number
298 || ', purchase order#'
299 || p_po_number
300 || ', Revision Number '
301 || p_revision_num
302 || ', Release Number'
303 || p_release_number
304 || ', the following error is encountered:'
305 || x_error_status_out;
306 IF (l_Debug_Level <= 5) THEN
307 cln_debug_pub.Add('EXITING PROCESS_ORDER_HEADER', 2);
308 END IF;
309
310 WHEN OTHERS THEN
311 ROLLBACK TO PO_UPDATE_TXN;
312 IF (l_Debug_Level <= 5) THEN
313 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
314 END IF;
315
316 l_error_code := SQLCODE;
317 l_error_msg := SQLERRM;
318 x_error_id_out := -2;
319 G_ERROR_ID := -2;
320 x_error_status_out := l_error_code||' : '||l_error_msg;
321 G_ERROR_MESSAGE := x_error_status_out;
322 IF (l_Debug_Level <= 5) THEN
323 cln_debug_pub.Add(x_error_status_out, 3);
324 END IF;
325
326 x_error_status_out := 'While trying to process order header'
327 || ' for the inbound change sales order#'
328 || p_so_number
329 || ', purchase order#'
330 || p_po_number
331 || ', Revision Number '
332 || p_revision_num
333 || ', Release Number'
334 || p_release_number
335 || ', the following error is encountered:'
336 || x_error_status_out;
337 IF (l_Debug_Level <= 5) THEN
338 cln_debug_pub.Add('EXITING PROCESS_ORDER_HEADER', 2);
339 END IF;
340
341 END PROCESS_ORDER_HEADER;
342
343
344
345 -- Name
346 -- PROCESS_ORDER_LINE
347 -- Purpose
348 -- Processes the order line details by updating the PO thru 'Change PO' APIs
349 -- and collaboration history. Line price gets modified
350 -- Arguments
351 -- PO and SO Line details
352 -- Notes
353 -- No Specific Notes
354
355 PROCEDURE PROCESS_ORDER_LINE(
359 p_po_id IN VARCHAR2,
356 x_error_id OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2,
358 p_requstor IN VARCHAR2,
360 p_po_rel_num IN NUMBER,
361 p_po_rev_num IN NUMBER,
362 p_po_line_num IN NUMBER,
363 p_po_price IN NUMBER,
364 p_po_price_currency IN VARCHAR2,
365 p_po_price_uom IN VARCHAR2,
366 p_supplier_part_number IN VARCHAR2,
367 p_so_num IN VARCHAR2,
368 p_so_line_num IN NUMBER,
369 p_so_line_status IN VARCHAR2,
370 p_reason IN VARCHAR2,
371 p_app_ref_id IN VARCHAR2,
372 p_tp_id IN VARCHAR2,
373 p_tp_site_id IN VARCHAR2,
374 p_int_ctl_num IN VARCHAR2,
375 -- Supplier Line Reference added for new Change_PO API to
376 -- support split lines and cancellation at header and schedule level.
377 p_supp_doc_ref IN VARCHAR2 DEFAULT NULL,
378 p_supp_line_ref IN VARCHAR2 DEFAULT NULL)
379 IS
380 l_return_status VARCHAR2(1000);
381 l_return_msg VARCHAR2(2000);
382 l_debug_mode VARCHAR2(300);
383 l_error_code NUMBER;
384 l_error_msg VARCHAR2(2000);
385 l_po_type VARCHAR2(50);
386 l_error_id NUMBER;
387 l_error_status VARCHAR2(1000);
388 l_ack_type VARCHAR2(50);
389 BEGIN
390
391 l_po_type := 'STANDARD';
392 IF (p_po_rel_num IS NOT NULL AND p_po_rel_num > 0) THEN
393 l_po_type := 'RELEASE';
394 END IF;
395
396 -- Sets the debug mode to be FILE
397 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
398
399 -- Initialize API return status to success
400 l_error_id := 0;
401 x_error_id := 0;
402
403 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
404 x_msg_data := FND_MESSAGE.GET;
405
406 IF (l_Debug_Level <= 2) THEN
407 cln_debug_pub.Add('ENTERING PROCESS_ORDER_LINE', 2);
408 END IF;
409
410 IF (l_Debug_Level <= 1) THEN
411 cln_debug_pub.Add('With the following parameters:', 1);
412 cln_debug_pub.Add('p_requstor:' || p_requstor, 1);
413 cln_debug_pub.Add('p_po_id:' || p_po_id, 1);
414 cln_debug_pub.Add('p_po_rel_num:' || p_po_rel_num, 1);
415 cln_debug_pub.Add('p_po_rev_num:' || p_po_rev_num, 1);
416 cln_debug_pub.Add('p_po_line_num:' || p_po_line_num, 1);
417 cln_debug_pub.Add('p_po_price:' || p_po_price, 1);
418 cln_debug_pub.Add('p_po_price_currency:' || p_po_price_currency, 1);
419 cln_debug_pub.Add('p_po_price_uom:' || p_po_price_uom, 1);
420 cln_debug_pub.Add('p_supplier_part_number:' || p_supplier_part_number, 1);
421 cln_debug_pub.Add('p_so_num:' || p_so_num, 1);
422 cln_debug_pub.Add('p_so_line_num:' || p_so_line_num, 1);
423 cln_debug_pub.Add('p_so_line_status:' || p_so_line_status, 1);
424 cln_debug_pub.Add('p_reason:' || p_reason, 1);
425 cln_debug_pub.Add('p_app_ref_id:' || p_app_ref_id, 1);
426 cln_debug_pub.Add('p_tp_id:' || p_tp_id, 1);
427 cln_debug_pub.Add('p_tp_site_id:' || p_tp_site_id, 1);
428 cln_debug_pub.Add('p_int_ctl_num:' || p_int_ctl_num, 1);
429 cln_debug_pub.Add('p_supp_doc_ref:' || p_supp_doc_ref, 1);
430 cln_debug_pub.Add('p_supp_line_ref:' || p_supp_line_ref, 1);
431
432 cln_debug_pub.Add('G_ERROR_ID:' || G_ERROR_ID, 1);
433 cln_debug_pub.Add('G_ERROR_MESSAGE:' || G_ERROR_MESSAGE, 1);
434 END IF;
435
436
437 -- If and only if none has failed until now
438 IF G_ERROR_ID IS NULL OR G_ERROR_ID = 0 THEN
439 IF l_po_type = 'RELEASE' THEN
440 -- Nothing to do since the changes happens only at shipment level
441 -- Collaboration history too is not updated
442 IF (l_Debug_Level <= 1) THEN
443 cln_debug_pub.Add('Since this a RELEASE, Nothing to do', 1);
444 END IF;
445
446 RETURN;
447 ELSIF IS_ALREADY_PROCESSED_LINE(p_po_line_num) THEN
448 -- Nothing to do since the changes happens only once per each po line
449 -- Collaboration history too is not updated
450 IF (l_Debug_Level <= 1) THEN
451 cln_debug_pub.Add('Since this an already processed line, Nothing to do', 1);
452 END IF;
453 RETURN;
454 ELSE
455 IF p_reason = 'Cancelled' THEN
456 l_ack_type := 'CANCELLED';
457 ELSE
458 l_ack_type := 'MODIFICATION';
459 END IF;
460
461 PO_CHG_REQUEST_GRP.store_supplier_request (
462 p_requestor => p_requstor,
463 p_int_cont_num => p_int_ctl_num,
464 -- Always change irrespective of, if at all there is any change
465 p_request_type => 'CHANGE',
466 p_tp_id => p_tp_id,
467 p_tp_site_id => p_tp_site_id,
468 p_level => 'LINE',
469 p_po_number => p_po_id,
473 p_line_num => p_po_line_num,
470 p_release_number => p_po_rel_num,
471 p_po_type => 'STANDARD',
472 p_revision_num => NULL,
474 p_reason => p_reason,
475 p_shipment_num => NULL,
476 p_quantity => NULL,
477 p_quantity_uom => NULL,
478 p_price => p_po_price,
479 p_price_currency => p_po_price_currency,
480 p_price_uom => p_po_price_uom,
481 p_promised_date => NULL,
482 p_supplier_part_num => p_supplier_part_number,
483 p_so_number => p_so_num,
484 p_so_line_number => p_so_line_num,
485 p_ack_type => l_ack_type,
486 x_error_id_in => l_error_id,
487 x_error_status_in => l_error_status,
488 x_error_id_out => l_error_id,
489 x_error_status_out => l_error_status,
490 -- Supplier Line Reference added for new Change_PO API to
491 -- support split lines and cancellation at header and schedule level.
492 p_parent_shipment_number => NULL,
493 p_supplier_doc_ref => p_supp_doc_ref,
494 p_supplier_line_ref => p_supp_line_ref,
495 p_supplier_shipment_ref => NULL);
496 IF (l_Debug_Level <= 1) THEN
497 cln_debug_pub.Add('store_supplier_request');
498 cln_debug_pub.Add('l_error_id:' || l_error_id);
499 cln_debug_pub.Add('l_error_status:' || l_error_status);
500 END IF;
501
502 END IF; -- if itz a standard po
503
504
505 IF l_error_id IS NULL OR l_error_id = 0 THEN
506 RAISE_ADD_MESSAGE(
507 x_return_status => l_return_status,
508 x_msg_data => l_return_msg,
509 p_ictrl_no => p_int_ctl_num,
510 p_ref1 => p_po_line_num,
511 p_ref2 => p_so_line_num,
512 p_ref3 => p_po_price,
513 p_ref4 => p_po_price_currency,
514 p_ref5 => p_po_price_uom,
515 p_dtl_msg => p_reason);
516 IF l_return_status <> 'S' THEN
517 IF (l_Debug_Level <= 1) THEN
518 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
519 END IF;
520
521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
522 END IF;
523 END IF;
524
525 IF l_error_id IS NOT NULL AND l_error_id <> 0 THEN
526 G_ERROR_ID := l_error_id;
527 G_ERROR_MESSAGE := l_error_status;
528 x_error_id := l_error_id;
529 x_msg_data := l_error_status;
530 IF (l_Debug_Level <= 1) THEN
531 cln_debug_pub.Add('PO API Call failed', 1);
532 END IF;
533
534 ROLLBACK TO PO_UPDATE_TXN;
535 IF (l_Debug_Level <= 1) THEN
536 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',1);
537 END IF;
538
539 RAISE_UPDATE_COLLABORATION(
540 x_return_status => l_return_status,
541 x_msg_data => l_return_msg,
542 p_ref_id => p_app_ref_id,
543 p_doc_no => p_po_id,
544 p_part_doc_no => p_so_num,
545 p_msg_text => l_error_status,
546 p_status_code => 1,
547 p_int_ctl_num => p_int_ctl_num);
548 IF l_return_status <> 'S' THEN
549 IF (l_Debug_Level <= 1) THEN
550 cln_debug_pub.Add('RAISE_UPDATE_COLLABORATION CALL FAILED',1);
551 END IF;
552
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END IF;
555 RAISE_ADD_MESSAGE(
556 x_return_status => l_return_status,
557 x_msg_data => l_return_msg,
558 p_ictrl_no => p_int_ctl_num,
559 p_ref1 => p_po_line_num,
560 p_ref2 => p_so_line_num,
561 p_ref3 => p_po_price,
562 p_ref4 => p_po_price_currency,
563 p_ref5 => p_po_price_uom,
564 p_dtl_msg => p_reason);
565 IF l_return_status <> 'S' THEN
566 IF (l_Debug_Level <= 1) THEN
567 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
568 END IF;
569
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572 IF (l_Debug_Level <= 1) THEN
573 cln_debug_pub.Add(x_msg_data, 1);
574 END IF;
575
576 IF (l_Debug_Level <= 2) THEN
577 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE', 2);
578 END IF;
579
580 RETURN;
581 END IF; -- If Error ID is not zero
582 ELSE -- If Global Error ID is not zero
583 x_error_id := G_ERROR_ID;
584 x_msg_data := G_ERROR_MESSAGE;
585 END IF;
589
586 IF (l_Debug_Level <= 2) THEN
587 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE', 2);
588 END IF;
590 EXCEPTION
591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
592 ROLLBACK TO PO_UPDATE_TXN;
593 IF (l_Debug_Level <= 5) THEN
594 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
595 END IF;
596
597 x_error_id := -1;
598 G_ERROR_ID := -1;
599 x_msg_data := l_return_msg;
600 G_ERROR_MESSAGE := l_return_msg;
601 IF (l_Debug_Level <= 5) THEN
602 cln_debug_pub.Add(x_msg_data, 5);
603 END IF;
604
605 x_msg_data := 'While trying to process order line'
606 || ' for the inbound change sales order#'
607 || p_so_num
608 || ', purchase order#'
609 || p_po_id
610 || ', Revision Number '
611 || p_po_rel_num
612 || ', Release Number'
613 || p_po_rev_num
614 || ', PO Line Number'
615 || p_po_line_num
616 || ', the following error is encountered:'
617 || x_msg_data;
618 IF (l_Debug_Level <= 5) THEN
619 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE', 2);
620 END IF;
621
622 WHEN OTHERS THEN
623 ROLLBACK TO PO_UPDATE_TXN;
624 IF (l_Debug_Level <= 5) THEN
625 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
626 END IF;
627
628 l_error_code := SQLCODE;
629 l_error_msg := SQLERRM;
630 x_error_id := -2;
631 G_ERROR_ID := -2;
632 x_msg_data := l_error_code||' : '||l_error_msg;
633 G_ERROR_MESSAGE := x_msg_data;
634 IF (l_Debug_Level <= 5) THEN
635 cln_debug_pub.Add(x_msg_data, 5);
636 END IF;
637
638 x_msg_data := 'While trying to process order line'
639 || ' for the inbound change sales order#'
640 || p_so_num
641 || ', purchase order#'
642 || p_po_id
643 || ', Revision Number '
644 || p_po_rel_num
645 || ', Release Number'
646 || p_po_rev_num
647 || ', PO Line Number'
648 || p_po_line_num
649 || ', the following error is encountered:'
650 || x_msg_data;
651 IF (l_Debug_Level <= 5) THEN
652 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE', 2);
653 END IF;
654
655 END PROCESS_ORDER_LINE;
656
657
658
659 -- Name
660 -- PROCESS_ORDER_LINE_SHIPMENT
661 -- Purpose
662 -- Processes the order line shipment by updating the PO thru 'Change PO' APIs
663 -- and collaboration history
664 -- Shipment Quantity and Promised date get modified
665 -- If it is a RELEASE PO, Line price also gets modified
666 -- Arguments
667 -- PO and SO Line details
668 -- Notes
669 -- No Specific Notes
670
671 PROCEDURE PROCESS_ORDER_LINE_SHIPMENT(
672 x_error_id OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2,
674 p_requstor IN VARCHAR2,
675 p_po_id IN VARCHAR2,
676 p_po_rel_num IN NUMBER,
677 p_po_rev_num IN NUMBER,
678 p_po_line_num IN NUMBER,
682 p_po_price IN NUMBER,
679 p_po_ship_num IN NUMBER,
680 p_po_quantity IN NUMBER,
681 p_po_quantity_uom IN VARCHAR2,
683 p_po_price_currency IN VARCHAR2,
684 p_po_price_uom IN VARCHAR2,
685 p_po_promised_date IN DATE,
686 p_supplier_part_number IN VARCHAR2,
687 p_so_num IN VARCHAR2,
688 p_so_line_num IN NUMBER,
689 p_so_line_status IN VARCHAR2,
690 p_reason IN VARCHAR2,
691 p_app_ref_id IN VARCHAR2,
692 p_tp_id IN VARCHAR2,
693 p_tp_site_id IN VARCHAR2,
694 p_int_ctl_num IN VARCHAR2,
695 -- Supplier Line Reference added for new Change_PO API to
696 -- support split lines and cancellation at header and schedule level.
697 p_supp_doc_ref IN VARCHAR2 DEFAULT NULL,
698 p_supp_line_ref IN VARCHAR2 DEFAULT NULL,
699 p_supplier_shipment_ref IN VARCHAR2 DEFAULT NULL,
700 p_parent_shipment_number IN VARCHAR2 DEFAULT NULL)
701 IS
702 l_return_status VARCHAR2(1000);
703 l_return_msg VARCHAR2(2000);
704 l_debug_mode VARCHAR2(300);
705 l_error_code NUMBER;
706 l_error_msg VARCHAR2(2000);
707 l_po_type VARCHAR2(50);
708 l_error_id NUMBER;
709 l_error_status VARCHAR2(1000);
710 l_ack_type VARCHAR2(50);
711 BEGIN
712
713 -- Sets the debug mode to be FILE
714 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
715
716 -- Initialize API return status to success
717 l_error_id := 0;
718 x_error_id := 0;
719
720 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
721 x_msg_data := FND_MESSAGE.GET;
722
723 IF (l_Debug_Level <= 2) THEN
724 cln_debug_pub.Add('ENTERING PROCESS_ORDER_LINE_SHIPMENT', 2);
725 END IF;
726
727 IF (l_Debug_Level <= 1) THEN
728 cln_debug_pub.Add('With the following parameters:', 1);
729 cln_debug_pub.Add('p_requstor:' || p_requstor, 1);
730 cln_debug_pub.Add('p_po_id:' || p_po_id, 1);
731 cln_debug_pub.Add('p_po_rel_num:' || p_po_rel_num, 1);
732 cln_debug_pub.Add('p_po_rev_num:' || p_po_rev_num, 1);
733 cln_debug_pub.Add('p_po_line_num:' || p_po_line_num, 1);
734 cln_debug_pub.Add('p_po_ship_num:' || p_po_ship_num, 1);
735 cln_debug_pub.Add('p_po_quantity:' || p_po_quantity, 1);
736 cln_debug_pub.Add('p_po_quantity_uom:' || p_po_quantity_uom, 1);
737 cln_debug_pub.Add('p_po_price:' || p_po_price, 1);
738 cln_debug_pub.Add('p_po_price_currency:' || p_po_price_currency, 1);
739 cln_debug_pub.Add('p_po_price_uom:' || p_po_price_uom, 1);
743 cln_debug_pub.Add('p_so_line_num:' || p_so_line_num, 1);
740 cln_debug_pub.Add('p_po_promised_date:' || p_po_promised_date, 1);
741 cln_debug_pub.Add('p_supplier_part_number:' || p_supplier_part_number, 1);
742 cln_debug_pub.Add('p_so_num:' || p_so_num, 1);
744 cln_debug_pub.Add('p_so_line_status:' || p_so_line_status, 1);
745 cln_debug_pub.Add('p_reason:' || p_reason, 1);
746 cln_debug_pub.Add('p_app_ref_id:' || p_app_ref_id, 1);
747 cln_debug_pub.Add('p_tp_id:' || p_tp_id, 1);
748 cln_debug_pub.Add('p_tp_site_id:' || p_tp_site_id, 1);
749 cln_debug_pub.Add('p_int_ctl_num:' || p_int_ctl_num, 1);
750 cln_debug_pub.Add('p_supp_doc_ref:' || p_supp_doc_ref, 1);
751 cln_debug_pub.Add('p_supp_line_ref:' || p_supp_line_ref, 1);
752 cln_debug_pub.Add('p_supplier_shipment_ref:' || p_supplier_shipment_ref, 1);
753 cln_debug_pub.Add('p_parent_shipment_number:' || p_parent_shipment_number, 1);
754 END IF;
755
756
757 l_po_type := 'STANDARD';
758 IF (p_po_rel_num IS NOT NULL AND p_po_rel_num > 0) THEN
759 l_po_type := 'RELEASE';
760 END IF;
761
762 IF (l_Debug_Level <= 1) THEN
763 cln_debug_pub.Add('l_po_type:' || l_po_type, 1);
764
765 cln_debug_pub.Add('G_ERROR_ID:' || G_ERROR_ID, 1);
766 cln_debug_pub.Add('G_ERROR_MESSAGE:' || G_ERROR_MESSAGE, 1);
767 END IF;
768
769 IF p_reason = 'Cancelled' THEN
770 l_ack_type := 'CANCELLED';
771 ELSE
772 l_ack_type := 'MODIFICATION';
773 END IF;
774
775 -- If and only if none has failed until now
776 IF G_ERROR_ID IS NULL OR G_ERROR_ID = 0 THEN
777 IF l_po_type = 'RELEASE' THEN
778 -- Price also gets updated
779 PO_CHG_REQUEST_GRP.store_supplier_request (
780 p_requestor => p_requstor,
781 p_int_cont_num => p_int_ctl_num,
782 -- Always change irrespective of, if at all there is any change
783 p_request_type => 'CHANGE',
784 p_tp_id => p_tp_id,
785 p_tp_site_id => p_tp_site_id,
786 p_level => 'SHIPMENT',
787 p_po_number => p_po_id,
788 p_release_number => p_po_rel_num,
789 p_po_type => 'RELEASE',
790 -- Should not pass revision nmumber, it keeps changing
791 p_revision_num => NULL,
792 p_line_num => p_po_line_num,
793 p_reason => p_reason,
794 p_shipment_num => p_po_ship_num,
795 p_quantity => p_po_quantity,
796 p_quantity_uom => p_po_quantity_uom,
797 p_price => p_po_price,
798 p_price_currency => p_po_price_currency,
799 p_price_uom => p_po_price_uom,
800 p_promised_date => p_po_promised_date,
801 p_supplier_part_num => p_supplier_part_number,
802 p_so_number => p_so_num,
803 p_so_line_number => p_so_line_num,
804 p_ack_type => l_ack_type,
805 x_error_id_in => l_error_id,
806 x_error_status_in => l_error_status,
807 x_error_id_out => l_error_id,
808 x_error_status_out => l_error_status,
809 -- Supplier Line Reference added for new Change_PO API to
810 -- support split lines and cancellation at header and schedule level.
811 p_parent_shipment_number => p_parent_shipment_number,
812 p_supplier_doc_ref => p_supp_doc_ref,
813 p_supplier_line_ref => p_supp_line_ref,
814 p_supplier_shipment_ref => p_supplier_shipment_ref);
815 IF (l_Debug_Level <= 1) THEN
816 cln_debug_pub.Add('store_supplier_request');
817 cln_debug_pub.Add('l_error_id:' || l_error_id);
818 cln_debug_pub.Add('l_error_status:' || l_error_status);
819 END IF;
820
821 ELSE
822 PO_CHG_REQUEST_GRP.store_supplier_request (
823 p_requestor => p_requstor,
824 p_int_cont_num => p_int_ctl_num,
825 -- Always change irrespective of, if at all there is any change
826 p_request_type => 'CHANGE',
827 p_tp_id => p_tp_id,
828 p_tp_site_id => p_tp_site_id,
829 p_level => 'SHIPMENT',
830 p_po_number => p_po_id,
831 p_release_number => p_po_rel_num,
832 p_po_type => 'STANDARD',
833 p_revision_num => NULL,
834 p_line_num => p_po_line_num,
835 p_reason => p_reason,
836 p_shipment_num => p_po_ship_num,
837 p_quantity => p_po_quantity,
838 p_quantity_uom => p_po_quantity_uom,
839 p_price => NULL,
840 p_price_currency => NULL,
841 p_price_uom => NULL,
842 p_promised_date => p_po_promised_date,
843 p_supplier_part_num => p_supplier_part_number,
844 p_so_number => p_so_num,
845 p_so_line_number => p_so_line_num,
849 x_error_id_out => l_error_id,
846 p_ack_type => l_ack_type,
847 x_error_id_in => l_error_id,
848 x_error_status_in => l_error_status,
850 x_error_status_out => l_error_status,
851 -- Supplier Line Reference added for new Change_PO API to
852 -- support split lines and cancellation at header and schedule level.
853 p_parent_shipment_number => p_parent_shipment_number,
854 p_supplier_doc_ref => p_supp_doc_ref,
855 p_supplier_line_ref => p_supp_line_ref,
856 p_supplier_shipment_ref => p_supplier_shipment_ref);
857 IF (l_Debug_Level <= 1) THEN
858 cln_debug_pub.Add('store_supplier_request');
859 cln_debug_pub.Add('l_error_id:' || l_error_id);
860 cln_debug_pub.Add('l_error_status:' || l_error_status);
861 END IF;
862
863 END IF; -- if itz a standard po
864
865
866 IF l_error_id IS NULL OR l_error_id = 0 THEN
867 RAISE_ADD_MESSAGE(
868 x_return_status => l_return_status,
869 x_msg_data => l_return_msg,
870 p_ictrl_no => p_int_ctl_num,
871 p_ref1 => p_po_line_num,
872 p_ref2 => p_so_line_num,
873 p_ref3 => p_po_ship_num,
874 p_ref4 => p_po_quantity,
875 p_ref5 => p_po_promised_date,
876 p_dtl_msg => p_reason);
877 IF l_return_status <> 'S' THEN
878 IF (l_Debug_Level <= 1) THEN
879 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
880 END IF;
881
882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
883 END IF;
884 END IF;
885
886 IF l_error_id IS NOT NULL AND l_error_id <> 0 THEN
887 G_ERROR_ID := l_error_id;
888 G_ERROR_MESSAGE := l_error_status;
889 x_error_id := l_error_id;
890 x_msg_data := l_error_status;
891 IF (l_Debug_Level <= 1) THEN
892 cln_debug_pub.Add('PO API Call failed', 1);
893 END IF;
894
895 ROLLBACK TO PO_UPDATE_TXN;
896 IF (l_Debug_Level <= 1) THEN
897 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',1);
898 END IF;
899
900 RAISE_UPDATE_COLLABORATION(
901 x_return_status => l_return_status,
902 x_msg_data => l_return_msg,
903 p_ref_id => p_app_ref_id,
904 p_doc_no => p_po_id,
905 p_part_doc_no => p_so_num,
906 p_msg_text => l_error_status,
907 p_status_code => 1,
908 p_int_ctl_num => p_int_ctl_num);
909 IF l_return_status <> 'S' THEN
910 IF (l_Debug_Level <= 1) THEN
911 cln_debug_pub.Add('RAISE_UPDATE_COLLABORATION CALL FAILED',1);
912 END IF;
913
914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
915 END IF;
916 RAISE_ADD_MESSAGE(
917 x_return_status => l_return_status,
918 x_msg_data => l_return_msg,
919 p_ictrl_no => p_int_ctl_num,
920 p_ref1 => p_po_line_num,
921 p_ref2 => p_so_line_num,
922 p_ref3 => p_po_ship_num,
923 p_ref4 => p_po_quantity,
924 p_ref5 => p_po_promised_date,
925 p_dtl_msg => p_reason);
926 IF l_return_status <> 'S' THEN
927 IF (l_Debug_Level <= 1) THEN
928 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
929 END IF;
930
931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
932 END IF;
933 IF (l_Debug_Level <= 1) THEN
934 cln_debug_pub.Add(x_msg_data, 1);
935 END IF;
936
937 IF (l_Debug_Level <= 2) THEN
938 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE_SHIPMENT', 2);
939 END IF;
940
941 RETURN;
942 END IF; -- If Error ID is not zero
943 ELSE -- If Global Error ID is not zero
944 x_error_id := G_ERROR_ID;
945 x_msg_data := G_ERROR_MESSAGE;
946 END IF;
947 IF (l_Debug_Level <= 2) THEN
948 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE_SHIPMENT', 2);
949 END IF;
950
951 EXCEPTION
952 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
953 ROLLBACK TO PO_UPDATE_TXN;
954 IF (l_Debug_Level <= 5) THEN
955 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
956 END IF;
957
958 x_error_id := -1;
959 G_ERROR_ID := -1;
960 x_msg_data := l_return_msg;
961 G_ERROR_MESSAGE := l_return_msg;
962 IF (l_Debug_Level <= 5) THEN
963 cln_debug_pub.Add(x_msg_data, 5);
964 END IF;
965
969 || ', purchase order#'
966 x_msg_data := 'While trying to process order line shipment'
967 || ' for the inbound change sales order#'
968 || p_so_num
970 || p_po_id
971 || ', Revision Number '
972 || p_po_rel_num
973 || ', Release Number'
974 || p_po_rev_num
975 || ', PO Line Number'
976 || p_po_line_num
977 || ', PO Line Shipment Number'
978 || p_po_ship_num
979 || ', the following error is encountered:'
980 || x_msg_data;
981 IF (l_Debug_Level <= 5) THEN
982 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE_SHIPMENT', 2);
983 END IF;
984
985 WHEN OTHERS THEN
986 ROLLBACK TO PO_UPDATE_TXN;
987 IF (l_Debug_Level <= 5) THEN
988 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
989 END IF;
990
991 l_error_code := SQLCODE;
992 l_error_msg := SQLERRM;
993 x_error_id := -2;
994 G_ERROR_ID := -2;
995 x_msg_data := l_error_code||' : '||l_error_msg;
996 G_ERROR_MESSAGE := x_msg_data;
997 IF (l_Debug_Level <= 5) THEN
998 cln_debug_pub.Add(x_msg_data, 5);
999 END IF;
1000
1001 x_msg_data := 'While trying to process order line shipment'
1002 || ' for the inbound change sales order#'
1003 || p_so_num
1004 || ', purchase order#'
1005 || p_po_id
1006 || ', Revision Number '
1007 || p_po_rel_num
1008 || ', Release Number'
1009 || p_po_rev_num
1010 || ', PO Line Number'
1011 || p_po_line_num
1012 || ', PO Line Shipment Number'
1013 || p_po_ship_num
1014 || ', the following error is encountered:'
1015 || x_msg_data;
1016 IF (l_Debug_Level <= 5) THEN
1017 cln_debug_pub.Add('EXITING PROCESS_ORDER_LINE_SHIPMENT', 2);
1018 END IF;
1019
1020 END PROCESS_ORDER_LINE_SHIPMENT;
1021
1022
1023
1024 -- Name
1025 -- LOAD_CHANGES
1026 -- Purpose
1027 -- Call Process Supplier Request of Update_PO API to
1028 -- load all changes in to interface tables
1029 -- Arguments
1030 -- Internal Control Number
1031 -- Notes
1032 -- No Specific Notes
1033
1034 PROCEDURE LOAD_CHANGES(
1035 x_error_id OUT NOCOPY NUMBER,
1036 x_msg_data OUT NOCOPY VARCHAR2,
1037 p_app_ref_id IN VARCHAR2,
1038 p_po_id IN VARCHAR2,
1039 p_so_num IN VARCHAR2,
1040 p_int_ctl_num IN VARCHAR2)
1041 IS
1042 l_return_status VARCHAR2(1000);
1043 l_return_msg VARCHAR2(2000);
1044 l_debug_mode VARCHAR2(300);
1045 l_error_code NUMBER;
1046 l_error_msg VARCHAR2(2000);
1047 l_po_type VARCHAR2(50);
1048 l_error_id NUMBER;
1049 l_error_status VARCHAR2(1000);
1050 BEGIN
1051 -- Sets the debug mode to be FILE
1052 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
1053
1054 -- Initialize API return status to success
1055 l_error_id := 0;
1056 x_error_id := 0;
1057
1058 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
1059 x_msg_data := FND_MESSAGE.GET;
1060
1061 IF (l_Debug_Level <= 2) THEN
1062 cln_debug_pub.Add('ENTERING LOAD_CHANGES', 2);
1063 END IF;
1064
1065 IF (l_Debug_Level <= 1) THEN
1066 cln_debug_pub.Add('With the following parameters:', 1);
1067 cln_debug_pub.Add('p_app_ref_id:' || p_app_ref_id, 1);
1068 cln_debug_pub.Add('p_po_id:' || p_po_id, 1);
1069 cln_debug_pub.Add('p_so_num:' || p_so_num, 1);
1070 cln_debug_pub.Add('p_int_ctl_num:' || p_int_ctl_num, 1);
1071
1072 cln_debug_pub.Add('G_ERROR_ID:' || G_ERROR_ID, 1);
1073 cln_debug_pub.Add('G_ERROR_MESSAGE:' || G_ERROR_MESSAGE, 1);
1074 END IF;
1075
1076
1077 -- If and only if none has failed until now
1078 IF G_ERROR_ID IS NULL OR G_ERROR_ID = 0 THEN
1079 PO_CHG_REQUEST_GRP.process_supplier_request (
1080 p_int_cont_num => p_int_ctl_num,
1081 x_error_id_in => l_error_id,
1082 x_error_status_in => l_error_status,
1083 x_error_id_out => l_error_id,
1084 x_error_status_out => l_error_status);
1085 IF (l_Debug_Level <= 1) THEN
1086 cln_debug_pub.Add('process_supplier_request', 1);
1087 cln_debug_pub.Add('l_error_id:' || l_error_id,1);
1088 cln_debug_pub.Add('l_error_status:' || l_error_status,1);
1092 G_ERROR_ID := l_error_id;
1089 END IF;
1090
1091 IF l_error_id IS NOT NULL AND l_error_id <> 0 THEN
1093 G_ERROR_MESSAGE := l_error_status;
1094 x_error_id := l_error_id;
1095 x_msg_data := l_error_status;
1096 IF (l_Debug_Level <= 1) THEN
1097 cln_debug_pub.Add('PO API Call failed', 1);
1098 END IF;
1099
1100 ROLLBACK TO PO_UPDATE_TXN;
1101 IF (l_Debug_Level <= 1) THEN
1102 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',1);
1103 END IF;
1104
1105 RAISE_UPDATE_COLLABORATION(
1106 x_return_status => l_return_status,
1107 x_msg_data => l_return_msg,
1108 p_ref_id => p_app_ref_id,
1109 p_doc_no => p_po_id,
1110 p_part_doc_no => p_so_num,
1111 p_msg_text => l_error_status,
1112 p_status_code => 1,
1113 p_int_ctl_num => p_int_ctl_num);
1114 IF l_return_status <> 'S' THEN
1115 IF (l_Debug_Level <= 1) THEN
1116 cln_debug_pub.Add('RAISE_UPDATE_COLLABORATION CALL FAILED',1);
1117 END IF;
1118
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 END IF;
1121 IF (l_Debug_Level <= 1) THEN
1122 cln_debug_pub.Add(x_msg_data, 1);
1123 END IF;
1124
1125 IF (l_Debug_Level <= 2) THEN
1126 cln_debug_pub.Add('EXITING LOAD_CHANGES', 2);
1127 END IF;
1128
1129 RETURN;
1130 END IF; -- If Error ID is not zero
1131 ELSE -- If Global Error ID is not zero
1132 x_error_id := G_ERROR_ID;
1133 x_msg_data := G_ERROR_MESSAGE;
1134 END IF;
1135 IF (l_Debug_Level <= 2) THEN
1136 cln_debug_pub.Add('EXITING LOAD_CHANGES', 2);
1137 END IF;
1138
1139 EXCEPTION
1140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1141 ROLLBACK TO PO_UPDATE_TXN;
1142 IF (l_Debug_Level <= 5) THEN
1143 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
1144 END IF;
1145
1146 x_error_id := -1;
1147 G_ERROR_ID := -1;
1148 x_msg_data := l_return_msg;
1149 G_ERROR_MESSAGE := l_return_msg;
1150 IF (l_Debug_Level <= 5) THEN
1151 cln_debug_pub.Add(x_msg_data, 5);
1152 END IF;
1153
1154 x_msg_data := 'While trying load changes in to interface tables'
1155 || ' for the inbound change sales order#'
1156 || p_so_num
1157 || ', purchase order#'
1158 || p_po_id
1159 || ', the following error is encountered:'
1160 || x_msg_data;
1161 IF (l_Debug_Level <= 5) THEN
1162 cln_debug_pub.Add('EXITING LOAD_CHANGES', 2);
1163 END IF;
1164
1165 WHEN OTHERS THEN
1166 ROLLBACK TO PO_UPDATE_TXN;
1167 IF (l_Debug_Level <= 5) THEN
1168 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
1169 END IF;
1170
1171 l_error_code := SQLCODE;
1172 l_error_msg := SQLERRM;
1173 x_error_id := -2;
1174 G_ERROR_ID := -2;
1175 x_msg_data := l_error_code||' : '||l_error_msg;
1176 G_ERROR_MESSAGE := x_msg_data;
1177 IF (l_Debug_Level <= 5) THEN
1178 cln_debug_pub.Add(x_msg_data, 5);
1179 END IF;
1180
1181 x_msg_data := 'While trying to load changes in to interface tables'
1182 || ' for the inbound change sales order#'
1183 || p_so_num
1184 || ', purchase order#'
1185 || p_po_id
1186 || ', the following error is encountered:'
1187 || x_msg_data;
1188 IF (l_Debug_Level <= 5) THEN
1189 cln_debug_pub.Add('EXITING LOAD_CHANGES', 2);
1190 END IF;
1191
1192 END LOAD_CHANGES;
1193
1194
1195
1196
1197 -- Name
1198 -- GET_TRADING_PARTNER_DETAILS
1199 -- Purpose
1200 -- This procedure returns back the trading partner id
1201 -- and trading partner site id based the header id
1202 --
1203 -- Arguments
1204 -- Header ID
1205 -- Notes
1206 -- No specific notes.
1207
1208 PROCEDURE GET_TRADING_PARTNER_DETAILS(
1209 x_tp_id OUT NOCOPY NUMBER,
1210 x_tp_site_id OUT NOCOPY NUMBER,
1211 p_tp_header_id IN NUMBER)
1212 IS
1213 l_debug_mode VARCHAR2(255);
1214 l_tp_id NUMBER;
1215 l_tp_site_id NUMBER;
1216 BEGIN
1217 -- Sets the debug mode to be FILE
1218 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
1219 IF (l_Debug_Level <= 2) THEN
1220 cln_debug_pub.Add('ENTERING GET_TRADING_PARTNER_DETAILS', 2);
1221 END IF;
1222
1223
1224 IF (l_Debug_Level <= 1) THEN
1228
1225 cln_debug_pub.Add('p_tp_header_id:' || p_tp_header_id, 1);
1226 END IF;
1227
1229 SELECT PARTY_ID, PARTY_SITE_ID
1230 INTO l_tp_id, l_tp_site_id
1231 FROM ECX_TP_HEADERS
1232 WHERE TP_HEADER_ID = p_tp_header_id;
1233
1234 IF (l_Debug_Level <= 1) THEN
1235 cln_debug_pub.Add('l_tp_id:' || l_tp_id, 1);
1236 cln_debug_pub.Add('l_tp_site_id:' || l_tp_site_id, 1);
1237 END IF;
1238
1239
1240 x_tp_id := l_tp_id;
1241 x_tp_site_id := l_tp_site_id;
1242
1243 IF (l_Debug_Level <= 2) THEN
1244 cln_debug_pub.Add('GET_TRADING_PARTNER_DETAILS', 2);
1245 END IF;
1246
1247 END GET_TRADING_PARTNER_DETAILS;
1248
1249
1250
1251
1252 -- Name
1253 -- RAISE_UPDATE_EVENT
1254 -- Purpose
1255 -- This procedure raises an event to update a collaboration.
1256 --
1257 -- Arguments
1258 --
1259 -- Notes
1260 -- No specific notes.
1261
1262 PROCEDURE RAISE_UPDATE_COLLABORATION(
1263 x_return_status OUT NOCOPY VARCHAR2,
1264 x_msg_data OUT NOCOPY VARCHAR2,
1265 p_ref_id IN VARCHAR2,
1266 p_doc_no IN VARCHAR2,
1267 p_part_doc_no IN VARCHAR2,
1268 p_msg_text IN VARCHAR2,
1269 p_status_code IN NUMBER,
1270 p_int_ctl_num IN VARCHAR2)
1271 IS
1272 l_cln_ch_parameters wf_parameter_list_t;
1273 l_event_key NUMBER;
1274 l_error_code NUMBER;
1275 l_error_msg VARCHAR2(2000);
1276 l_debug_mode VARCHAR2(255);
1277 l_doc_status VARCHAR2(255);
1278 BEGIN
1279 -- Sets the debug mode to be FILE
1280 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
1281 IF (l_Debug_Level <= 2) THEN
1282 cln_debug_pub.Add('ENTERING RAISE_UPDATE_COLLABORATION', 2);
1283 END IF;
1284
1285
1286 -- Initialize API return status to success
1287 x_return_status := FND_API.G_RET_STS_SUCCESS;
1288
1289 FND_MESSAGE.SET_NAME('CLN','CLN_CH_EVENT_RAISED');
1290 FND_MESSAGE.SET_TOKEN('EVENT','Update');
1291 x_msg_data := FND_MESSAGE.GET;
1292
1293 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
1294
1295 IF (l_Debug_Level <= 1) THEN
1296 cln_debug_pub.Add('With the following parameters', 1);
1297 cln_debug_pub.Add('p_ref_id' || p_ref_id, 1);
1298 cln_debug_pub.Add('p_doc_no:' || p_doc_no, 1);
1299 cln_debug_pub.Add('p_status_code:' || p_status_code, 1);
1300 cln_debug_pub.Add('p_msg_text:' || p_msg_text, 1);
1301 cln_debug_pub.Add('p_part_doc_no:' || p_part_doc_no, 1);
1302 cln_debug_pub.Add('p_int_ctl_num:' || p_int_ctl_num, 1);
1303 END IF;
1304
1305
1306 IF p_status_code = 0 THEN
1307 l_doc_status := 'SUCCESS';
1308 ELSE
1309 l_doc_status := 'ERROR';
1310 END IF;
1311
1312 IF (l_Debug_Level <= 1) THEN
1313 cln_debug_pub.Add('l_doc_status:' || l_doc_status, 1);
1314 END IF;
1315
1316
1317 l_cln_ch_parameters := wf_parameter_list_t();
1318
1319 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_int_ctl_num, l_cln_ch_parameters);
1320 WF_EVENT.AddParameterToList('REFERENCE_ID', p_ref_id, l_cln_ch_parameters);
1321
1322 WF_EVENT.AddParameterToList('DOCUMENT_NO', p_doc_no, l_cln_ch_parameters);
1323 WF_EVENT.AddParameterToList('PARTNER_DOCUMENT_NO', p_part_doc_no, l_cln_ch_parameters);
1324 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', p_doc_no, l_cln_ch_parameters);
1325
1326 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
1327 WF_EVENT.AddParameterToList('MESSAGE_TEXT', p_msg_text, l_cln_ch_parameters);
1328
1329 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',
1330 l_event_key, NULL, l_cln_ch_parameters, NULL);
1331 IF (l_Debug_Level <= 1) THEN
1332 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update raised', 1);
1333 END IF;
1334
1335
1336 IF (l_Debug_Level <= 2) THEN
1337 cln_debug_pub.Add('EXITING RAISE_UPDATE_COLLABORATION', 2);
1338 END IF;
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 l_error_code := SQLCODE;
1343 l_error_msg := SQLERRM;
1344 x_return_status := FND_API.G_RET_STS_ERROR;
1345 x_msg_data := l_error_code || ':' || l_error_msg;
1346 IF (l_Debug_Level <= 5) THEN
1347 cln_debug_pub.Add(x_msg_data, 4);
1348 cln_debug_pub.Add('EXITING RAISE_UPDATE_COLLABORATION', 2);
1349 END IF;
1350
1351 END RAISE_UPDATE_COLLABORATION;
1352
1353
1354
1355
1356
1357 -- Name
1358 -- RAISE_ADD_MSG_EVENT
1359 -- Purpose
1360 -- This procedure raises an event to add messages into collaboration history
1361 --
1362 -- Arguments
1363 --
1364 -- Notes
1365 -- No specific notes.
1366
1367 PROCEDURE RAISE_ADD_MESSAGE(
1368 x_return_status OUT NOCOPY VARCHAR2,
1369 x_msg_data OUT NOCOPY VARCHAR2,
1370 p_ictrl_no IN NUMBER,
1371 p_ref1 IN VARCHAR2,
1372 p_ref2 IN VARCHAR2,
1373 p_ref3 IN VARCHAR2,
1374 p_ref4 IN VARCHAR2,
1375 p_ref5 IN VARCHAR2,
1379 l_event_key NUMBER;
1376 p_dtl_msg IN VARCHAR2)
1377 IS
1378 l_cln_ch_parameters wf_parameter_list_t;
1380 l_error_code NUMBER;
1381 l_error_msg VARCHAR2(2000);
1382 l_debug_mode VARCHAR2(255);
1383 l_dtl_coll_id NUMBER;
1384 l_msg_data VARCHAR2(2000);
1385 BEGIN
1386 -- Sets the debug mode to be FILE
1387 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
1388 IF (l_Debug_Level <= 2) THEN
1389 cln_debug_pub.Add('ENTERING RAISE_ADD_MESSAGE', 2);
1390 END IF;
1391
1392 -- Parameters received
1393 IF (l_Debug_Level <= 1) THEN
1394 cln_debug_pub.Add('With the following parameters',1);
1395 cln_debug_pub.Add('p_ictrl_no - ' || p_ictrl_no,1);
1396 cln_debug_pub.Add('p_ref1 - ' || p_ref1,1);
1397 cln_debug_pub.Add('p_ref2 - ' || p_ref2,1);
1398 cln_debug_pub.Add('p_ref3 - ' || p_ref3,1);
1399 cln_debug_pub.Add('p_ref4 - ' || p_ref4,1);
1400 cln_debug_pub.Add('p_ref5 - ' || p_ref5,1);
1401 cln_debug_pub.Add('p_dtl_msg - ' || p_dtl_msg,1);
1402 END IF;
1403
1404
1405 -- Initialize API return status to success
1406 x_return_status := FND_API.G_RET_STS_SUCCESS;
1407 FND_MESSAGE.SET_NAME('CLN', 'CLN_G_RET_MSG_SUCCESS');
1408 x_msg_data := FND_MESSAGE.GET;
1409
1410
1411 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
1412
1413 l_cln_ch_parameters := wf_parameter_list_t();
1414 WF_EVENT.AddParameterToList('COLLABORATION_DETAIL_ID', l_dtl_coll_id, l_cln_ch_parameters);
1415 WF_EVENT.AddParameterToList('REFERENCE_ID1', p_ref1, l_cln_ch_parameters);
1416 WF_EVENT.AddParameterToList('REFERENCE_ID2', p_ref2, l_cln_ch_parameters);
1417 WF_EVENT.AddParameterToList('REFERENCE_ID3', p_ref3, l_cln_ch_parameters);
1418 WF_EVENT.AddParameterToList('REFERENCE_ID4', p_ref4, l_cln_ch_parameters);
1419 WF_EVENT.AddParameterToList('REFERENCE_ID5', p_ref5, l_cln_ch_parameters);
1420 WF_EVENT.AddParameterToList('DETAIL_MESSAGE', p_dtl_msg, l_cln_ch_parameters);
1421 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_ictrl_no, l_cln_ch_parameters);
1422 WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SALES_ORDER', l_cln_ch_parameters);
1423 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
1424 -- Not required since defaulted to APPS
1425 -- WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
1426
1427 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',
1428 l_event_key, NULL, l_cln_ch_parameters, NULL);
1429 IF (l_Debug_Level <= 1) THEN
1430 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
1431 END IF;
1432
1433
1434 IF (l_Debug_Level <= 2) THEN
1435 cln_debug_pub.Add('EXITING RAISE_ADD_MESSAGE', 2);
1436 END IF;
1437
1438 EXCEPTION
1439 WHEN OTHERS THEN
1440 l_error_code := SQLCODE;
1441 l_error_msg := SQLERRM;
1442 x_return_status := FND_API.G_RET_STS_ERROR;
1443 x_msg_data := l_error_code || ':' || l_error_msg;
1444 IF (l_Debug_Level <= 5) THEN
1445 cln_debug_pub.Add(x_msg_data, 4);
1446 cln_debug_pub.Add('EXITING RAISE_ADD_MESSAGE', 2);
1447 END IF;
1448
1449 END RAISE_ADD_MESSAGE;
1450
1451
1452 -- Name
1453 -- CALL_TAKE_ACTIONS
1454 -- Purpose
1455 -- Invokes Notification Processor TAKE_ACTIONS according to the parameter.
1456 -- Arguments
1457 -- Description - Error message if errored out else 'SUCCESS'
1458 -- Sales Order Status
1459 -- Order Line Closed - YES/NO
1460 -- Notes
1461 -- No specific notes.
1462
1463 PROCEDURE CALL_TAKE_ACTIONS(
1464 p_itemtype IN VARCHAR2,
1465 p_itemkey IN VARCHAR2,
1466 p_actid IN NUMBER,
1467 p_funcmode IN VARCHAR2,
1468 x_resultout IN OUT NOCOPY VARCHAR2)
1469 IS
1470 l_doc_status VARCHAR2(100);
1471 l_description VARCHAR2(1000);
1472 l_trp_id VARCHAR2(100);
1473 l_app_ref_id VARCHAR2(255);
1474 l_return_status VARCHAR2(1000);
1475 l_return_msg VARCHAR2(2000);
1476 l_error_code NUMBER;
1477 l_error_msg VARCHAR2(2000);
1478 l_msg_data VARCHAR2(1000);
1479 l_not_msg VARCHAR2(1000);
1480 l_debug_mode VARCHAR2(255);
1481 l_tp_id NUMBER;
1482 l_error_id NUMBER;
1483 BEGIN
1484
1485 -- Sets the debug mode to be FILE
1486 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
1487
1488 x_resultout:='Yes';
1489
1490 IF (l_Debug_Level <= 2) THEN
1491 cln_debug_pub.Add('ENTERING CALL_TAKE_ACTIONS API', 2);
1492 END IF;
1493
1494 IF (l_Debug_Level <= 1) THEN
1495 cln_debug_pub.Add('Parameters:', 1);
1496 END IF;
1497
1498
1502 cln_debug_pub.Add('l_error_id:' || l_error_id, 1);
1499 -- l_error_id := TO_NUMBER(wf_engine.GetItemAttrText(itemtype, itemkey, actid, 'PARAMETER6'));
1500 l_error_id := wf_engine.GetItemAttrNumber(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
1501 IF (l_Debug_Level <= 1) THEN
1503 END IF;
1504
1505
1506 IF (l_error_id = 0) THEN
1507 l_doc_status := 'SUCCESS';
1508 -- Successfully processed change sales order
1509 FND_MESSAGE.SET_NAME('CLN','CLN_PO_CHANGE_SUCCESS');
1510 l_description := FND_MESSAGE.GET;
1511 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', l_doc_status);
1512 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', l_description);
1513 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '00');
1514 ELSE
1515 l_doc_status := 'ERROR';
1516 l_msg_data := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER7', TRUE);
1517 IF (l_Debug_Level <= 1) THEN
1518 cln_debug_pub.Add('l_msg_data:' || l_msg_data, 1);
1519 END IF;
1520
1521 -- Error while processing change sales order
1522 FND_MESSAGE.SET_NAME('CLN','CLN_PO_CHANGE_ERROR');
1523 FND_MESSAGE.SET_TOKEN('ERROR', l_msg_data);
1524 l_description := FND_MESSAGE.GET;
1525 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', l_doc_status);
1526 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', l_description);
1527 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '99');
1528 END IF;
1529
1530 l_trp_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE);
1531 IF (l_Debug_Level <= 1) THEN
1532 cln_debug_pub.Add('l_tp_id:' || l_trp_id, 1);
1533 END IF;
1534
1535 l_app_ref_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
1536 IF (l_Debug_Level <= 1) THEN
1537 cln_debug_pub.Add('l_app_ref_id:' || l_app_ref_id, 1);
1538 END IF;
1539
1540 CLN_UTILS.GET_TRADING_PARTNER(l_trp_id, l_tp_id);
1541 IF (l_Debug_Level <= 1) THEN
1542 cln_debug_pub.Add('Trading Partner ID:' || l_tp_id, 1);
1543 END IF;
1544
1545
1546 -- Error occured
1547 IF l_error_id <> 0 THEN
1548 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
1549 x_ret_code => l_return_status,
1550 x_ret_desc => l_return_msg,
1551 p_notification_code => 'CSO_IN02',
1552 p_notification_desc => l_description,
1553 p_status => 'ERROR',
1554 p_tp_id => to_char(l_tp_id),
1555 p_reference => l_app_ref_id,
1556 p_coll_point => 'APPS',
1557 p_int_con_no => NULL);
1558 IF l_return_status <> 'S' THEN
1559 IF (l_Debug_Level <= 1) THEN
1560 cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 1);
1561 END IF;
1562
1563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1564 END IF;
1565 RETURN;
1566 END IF;
1567
1568 -- Success
1569 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
1570 l_msg_data := FND_MESSAGE.GET;
1571 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
1572 x_ret_code => l_return_status,
1573 x_ret_desc => l_return_msg,
1574 p_notification_code => 'CSO_IN01',
1575 p_notification_desc => l_description,
1576 p_status => 'SUCCESS',
1577 p_tp_id => to_char(l_tp_id),
1578 p_reference => l_app_ref_id,
1579 p_coll_point => 'APPS',
1580 p_int_con_no => NULL);
1581 IF l_return_status <> 'S' THEN
1582 IF (l_Debug_Level <= 1) THEN
1583 cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 1);
1584 END IF;
1585
1586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1587 END IF;
1588
1589 IF (l_Debug_Level <= 2) THEN
1590 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
1591 END IF;
1592
1593 EXCEPTION
1594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1595 IF (l_Debug_Level <= 5) THEN
1596 cln_debug_pub.Add(l_return_msg, 6);
1597 END IF;
1598
1599 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_return_msg);
1600 IF (l_Debug_Level <= 5) THEN
1601 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
1602 END IF;
1603
1604 WHEN OTHERS THEN
1605 l_error_code := SQLCODE;
1606 l_error_msg := SQLERRM;
1607 l_not_msg := l_error_code || ':' || l_error_msg;
1608 IF (l_Debug_Level <= 2) THEN
1609 cln_debug_pub.Add(l_not_msg, 6);
1610 END IF;
1611
1612 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_not_msg);
1613 IF (l_Debug_Level <= 5) THEN
1614 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
1615 END IF;
1616
1617 END CALL_TAKE_ACTIONS;
1618
1619 END CLN_PO_CHANGE_ORDER;