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