[Home] [Help]
PACKAGE BODY: APPS.CLN_PO_SYNC_CAT_PKG
Source
1 PACKAGE BODY CLN_PO_SYNC_CAT_PKG AS
2 /* $Header: CLNPOCSB.pls 120.2 2006/04/03 08:28:29 smuthuav noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4 -- Package
5 -- CLN_PO_SYNC_CAT_PKG
6 --
7 -- Purpose
8 -- Package body for the package specification: CLN_PO_CATALOG_SYNC.
9 -- This package functions facilitate in Catalog sync operation
10 -- An inbound catalog will result in a Blanket purchase order
11 -- creation or updation
12 --
13 -- History
14 -- Jun-03-2003 Viswanthan Umapathy Created
15
16
17
18 -- Name
19 -- PROCESS_HEADER
20 -- Purpose
21 -- Creates a row in PO_HEADERS_INTERFACE and updates the collaboration
22 -- based on Catalog header details
23 -- Arguments
24 -- Catalog header details
25 -- Notes
26 -- No specific notes
27
28 PROCEDURE PROCESS_HEADER (
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 x_po_hdr_id OUT NOCOPY NUMBER,
32 x_operation OUT NOCOPY VARCHAR2,
33 p_app_ref_id IN VARCHAR2,
34 p_int_cont_num IN VARCHAR2,
35 p_ctg_sync_id IN VARCHAR2,
36 p_itf_hdr_id IN NUMBER,
37 p_batch_id IN NUMBER,
38 p_doc_type IN VARCHAR2,
39 p_tp_id IN NUMBER,
40 p_tp_site_id IN NUMBER,
41 p_ctg_name IN VARCHAR2,
42 p_eff_date IN DATE,
43 p_exp_date IN DATE,
44 p_currency IN NUMBER
45 )
46 IS
47 l_ctg_sync_id VARCHAR2(10);
48 l_ctg_name VARCHAR2(255);
49 l_return_status VARCHAR2(1000);
50 l_return_msg VARCHAR2(2000);
51 l_debug_mode VARCHAR2(300);
52 l_error_code NUMBER;
53 l_error_msg VARCHAR2(2000);
54 l_msg_text VARCHAR2(1000);
55 l_org_id NUMBER;
56 BEGIN
57 -- Sets the debug mode to be FILE
58 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
59
60 -- Initialize API return status to success
61 x_return_status := 'S';
62 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
63 x_msg_data := FND_MESSAGE.GET;
64
65 IF (l_Debug_Level <= 2) THEN
66 cln_debug_pub.Add('ENTERING PROCESS_HEADER', 2);
67 END IF;
68
69 IF (l_Debug_Level <= 1) THEN
70 cln_debug_pub.Add('With the following parameters:', 1);
71 cln_debug_pub.Add('p_app_ref_id:' || p_app_ref_id, 1);
72 cln_debug_pub.Add('p_int_cont_num:' || p_int_cont_num, 1);
73 cln_debug_pub.Add('p_ctg_sync_id:' || p_ctg_sync_id, 1);
74 cln_debug_pub.Add('p_itf_hdr_id:' || p_itf_hdr_id, 1);
75 cln_debug_pub.Add('p_batch_id:' || p_batch_id, 1);
76 cln_debug_pub.Add('p_doc_type:' || p_doc_type, 1);
77 cln_debug_pub.Add('p_tp_id:' || p_tp_id, 1);
78 cln_debug_pub.Add('p_tp_site_id:' || p_tp_site_id, 1);
79 cln_debug_pub.Add('p_ctg_name:' || p_ctg_name, 1);
80 cln_debug_pub.Add('p_eff_date:' || p_eff_date, 1);
81 cln_debug_pub.Add('p_exp_date:' || p_exp_date, 1);
82 cln_debug_pub.Add('p_currency:' || p_currency, 1);
83 END IF;
84
85 -- No need to create collaboration since XMLGateway Event handler will create
86 -- a collaboration if XMLGateway receives an inbound CLN document otherthan CBOD
87
88 -- Need to reomve the sysdate
89 -- l_ctg_name := substr(p_ctg_name, 1, instr(p_ctg_name, ':', 1, 3)-1);
90
91 l_ctg_name := p_ctg_name;
92 IF (l_Debug_Level <= 1) THEN
93 cln_debug_pub.Add('l_ctg_name:' || l_ctg_name, 1);
94 END IF;
95
96
97 -- If the sysnc id is anything other than A,R,U error out
98 l_ctg_sync_id := upper(p_ctg_sync_id);
99 IF (l_ctg_sync_id IS NULL) OR ( l_ctg_sync_id <> 'A'
100 AND l_ctg_sync_id <> 'U'
101 AND l_ctg_sync_id <> 'R') THEN
102 IF (l_Debug_Level <= 1) THEN
103 cln_debug_pub.Add('Invalid Transaction Code - ' || l_ctg_sync_id, 1);
104 END IF;
105
106 -- Invalid Transaction Code - "CODE"
107 FND_MESSAGE.SET_NAME('CLN','CLN_INVALID_TXN_CODE');
108 FND_MESSAGE.SET_TOKEN('CODE', l_ctg_sync_id);
109 x_msg_data := FND_MESSAGE.GET;
110 RAISE_UPDATE_COLLABORATION(
111 x_return_status => l_return_status,
112 x_msg_data => l_return_msg,
113 p_ref_id => p_app_ref_id,
114 p_doc_no => NULL,
115 p_part_doc_no => l_ctg_name,
116 p_msg_text => x_msg_data,
117 p_status_code => 1,
118 p_int_ctl_num => p_int_cont_num);
119 IF l_return_status <> 'S' THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 END IF;
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 IF (l_Debug_Level <= 1) THEN
124 cln_debug_pub.Add(x_msg_data, 1);
125 END IF;
126
127 IF (l_Debug_Level <= 2) THEN
128 cln_debug_pub.Add('EXITING PROCESS_HEADER', 2);
129 END IF;
130
131 RETURN;
132 END IF;
133
134 -- BUG 3155860 - MULTIPLE BPO FOR THE SAME VENDOR DOC NUMBER CAN ALSO BE TAKEN CARE
135 -- Canceled and Closed BPO are not taken into consideration.
136 BEGIN
137 x_operation := 'UPDATE';
138 SELECT po_header_id
139 INTO x_po_hdr_id
140 FROM PO_HEADERS_ALL
141 WHERE VENDOR_ORDER_NUM = l_ctg_name
142 AND vendor_id = p_tp_id -- Bug #5006663
143 AND NVL(CANCEL_FLAG, 'N') = 'N'
144 AND NVL(CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED');
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 x_operation := 'INSERT';
148 WHEN OTHERS THEN
149 IF (l_Debug_Level <= 5) THEN
150 cln_debug_pub.Add('Exception while trying to obtain the BPO number',5);
151 END IF;
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 l_error_code := SQLCODE;
154 l_error_msg := SQLERRM;
155 x_msg_data := l_error_code||' : '||l_error_msg;
156 IF (l_Debug_Level <= 5) THEN
157 cln_debug_pub.Add(x_msg_data, 5);
158 END IF;
159
160 x_msg_data := 'While trying to obtain BPO number'
161 || ' for the inbound sync catalog#'
162 || p_ctg_name
163 || ', the following error is encountered:'
164 || x_msg_data;
165 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
166 IF (l_Debug_Level <= 5) THEN
167 cln_debug_pub.Add('EXITING PROCESS_HEADER', 2);
168 END IF;
169 RETURN;
170 END;
171
172 -- Update the collaboration
173 FND_MESSAGE.SET_NAME('CLN','CLN_DOCUMENT_PROCESSED');
174 -- Document Processed
175 l_msg_text := FND_MESSAGE.GET;
176 IF (l_Debug_Level <= 1) THEN
177 cln_debug_pub.Add('l_msg_text:' || l_msg_text, 1);
178 END IF;
179
180
181 RAISE_UPDATE_COLLABORATION(
182 x_return_status => l_return_status,
183 x_msg_data => l_return_msg,
184 p_ref_id => p_app_ref_id,
185 p_doc_no => NULL,
186 p_part_doc_no => l_ctg_name,
187 p_msg_text => l_msg_text,
188 p_status_code => 0,
189 p_int_ctl_num => p_int_cont_num);
190 IF l_return_status <> 'S' THEN
191 IF (l_Debug_Level <= 1) THEN
192 cln_debug_pub.Add('RAISE_UPDATE_COLLABORATION CALL FAILED',1);
193 END IF;
194
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197
198 SELECT org_id
199 INTO l_org_id
200 FROM po_vendor_sites_all
201 WHERE vendor_site_id = p_tp_site_id;
202
203
204 IF (l_Debug_Level <= 1) THEN
205 cln_debug_pub.Add('x_operation:' || x_operation, 1);
206 END IF;
207
208 SAVEPOINT PO_UPDATE_TXN;
209
210 -- Insert in to PO_HEADERS_INTERFACE
211 IF x_operation = 'INSERT' THEN
212 -- Create a new BPO
213 -- While creating a new po, vendor document num
214 -- needs to be filled with catalog name
215 -- Need to insert a row with action as ORIGINAL
216
217 /* Bug : 3630042. In case of multiple messages due to
218 grouping factor, we never know the action is create.
219 We will populate it in the workflow using the procedure
220 SET_ACTION_CREATE_OR_UPDATE
221 */
222 INSERT INTO po_headers_interface(interface_header_id,
223 batch_id,
224 --action,
225 document_type_code,
226 vendor_id,
227 vendor_site_id,
228 effective_date,
229 expiration_date,
230 vendor_doc_num,
231 org_id,
232 amount_agreed
233 )
234 values(p_itf_hdr_id,
235 p_itf_hdr_id,
236 --'ORIGINAL',
237 'BLANKET',
238 p_tp_id,
239 p_tp_site_id,
240 p_eff_date,
241 p_exp_date,
242 l_ctg_name,
243 l_org_id,
244 0);
245 ELSE
246 -- Update an existing BPO
247 -- Need to insert a row in this case also
248 -- Action UPDATE
249 INSERT INTO po_headers_interface(interface_header_id,
250 batch_id,
251 action,
252 document_type_code,
253 vendor_id,
254 vendor_site_id,
255 effective_date,
256 expiration_date,
257 vendor_doc_num,
258 org_id,
259 amount_agreed
260 )
261 values(p_itf_hdr_id,
262 p_itf_hdr_id,
263 'UPDATE',
264 'BLANKET',
265 p_tp_id,
266 p_tp_site_id,
267 p_eff_date,
268 p_exp_date,
269 l_ctg_name,
270 l_org_id,
271 0);
272 END IF;
273 IF (l_Debug_Level <= 2) THEN
274 cln_debug_pub.Add('EXITING PROCESS_HEADER', 2);
275 END IF;
276
277 EXCEPTION
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279 ROLLBACK TO PO_UPDATE_TXN;
280 IF (l_Debug_Level <= 5) THEN
281 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
282 END IF;
283
284 -- Assaign global error info and set return values
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 x_msg_data := l_return_msg;
287 IF (l_Debug_Level <= 5) THEN
288 cln_debug_pub.Add(x_msg_data, 5);
289 END IF;
290
291 x_msg_data := 'While trying to process header details'
292 || ' for the inbound sync catalog#'
293 || p_ctg_name
294 || ', the following error is encountered:'
295 || x_msg_data;
296 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
297 IF (l_Debug_Level <= 5) THEN
298 cln_debug_pub.Add('EXITING PROCESS_HEADER', 2);
299 END IF;
300
301 WHEN OTHERS THEN
302 ROLLBACK TO PO_UPDATE_TXN;
303 IF (l_Debug_Level <= 5) THEN
304 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
305 END IF;
306
307 x_return_status := FND_API.G_RET_STS_ERROR;
308 l_error_code := SQLCODE;
309 l_error_msg := SQLERRM;
310 x_msg_data := l_error_code||' : '||l_error_msg;
311 IF (l_Debug_Level <= 5) THEN
312 cln_debug_pub.Add(x_msg_data, 5);
313 END IF;
314
315 x_msg_data := 'While trying to process header details'
316 || ' for the inbound sync catalog#'
317 || p_ctg_name
318 || ', the following error is encountered:'
319 || x_msg_data;
320 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
321 IF (l_Debug_Level <= 5) THEN
322 cln_debug_pub.Add('EXITING PROCESS_HEADER', 2);
323 END IF;
324
325 END PROCESS_HEADER;
326
327
328
329 -- Name
330 -- PROCESS_LINE
331 -- Purpose
332 -- Creates or updates a BPO Line
333 -- By creating a row in PO_LINES_INTERFACE
334 -- Updates the collaboration,
335 -- Based on Catalog line details
336 -- Arguments
337 -- Catalog line header details
338 -- Notes
339 -- No Specific Notes
340
341 PROCEDURE PROCESS_LINE(
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_data OUT NOCOPY VARCHAR2,
344 x_line_num OUT NOCOPY NUMBER,
345 p_operation IN VARCHAR2,
346 p_hdr_id IN NUMBER,
347 p_app_ref_id IN VARCHAR2,
348 p_int_cont_num IN VARCHAR2,
349 p_ctg_name IN VARCHAR2,
350 p_itf_hdr_id IN NUMBER,
351 p_itf_lin_id IN NUMBER,
352 p_vdr_part_num IN VARCHAR2,
353 p_item_desc IN VARCHAR2,
354 p_item IN VARCHAR2,
355 p_item_rev IN VARCHAR2,
356 p_category IN VARCHAR2,
357 p_uom IN VARCHAR2,
358 p_item_min_ord_quan IN VARCHAR2,
359 p_price IN NUMBER,
360 p_price_uom IN VARCHAR2,
361 p_price_currency IN VARCHAR2,
362 p_attribute1 IN VARCHAR2,
363 p_attribute2 IN VARCHAR2,
364 p_attribute3 IN VARCHAR2,
365 p_attribute4 IN VARCHAR2,
366 p_attribute5 IN VARCHAR2,
367 p_attribute6 IN VARCHAR2,
368 p_attribute7 IN VARCHAR2,
369 p_attribute8 IN VARCHAR2,
370 p_attribute9 IN VARCHAR2,
371 p_attribute10 IN VARCHAR2,
372 p_attribute11 IN VARCHAR2,
373 p_attribute12 IN VARCHAR2,
374 p_attribute13 IN VARCHAR2,
375 p_attribute14 IN VARCHAR2,
376 p_attribute15 IN VARCHAR2)
377 IS
378 l_return_status VARCHAR2(1000);
379 l_return_msg VARCHAR2(2000);
380 l_debug_mode VARCHAR2(300);
381 l_error_code NUMBER;
382 l_error_msg VARCHAR2(2000);
383 l_ctg_name VARCHAR2(50);
384 l_count NUMBER;
385 l_line_num NUMBER;
386 l_uom_code VARCHAR2(500);
387 BEGIN
388
389 -- Sets the debug mode to be FILE
390 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
391
392 -- Initialize API return status to success
393 x_return_status := 'S';
394 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
395 x_msg_data := FND_MESSAGE.GET;
396
397 IF (l_Debug_Level <= 2) THEN
398 cln_debug_pub.Add('ENTERING PROCESS_LINE', 2);
399 END IF;
400
401 IF (l_Debug_Level <= 1) THEN
402 cln_debug_pub.Add('With the following parameters:', 1);
403 cln_debug_pub.Add('p_operation:' || p_operation, 1);
404 cln_debug_pub.Add('p_hdr_id:' || p_hdr_id, 1);
405 cln_debug_pub.Add('p_app_ref_id:' || p_app_ref_id, 1);
406 cln_debug_pub.Add('p_int_cont_num:' || p_int_cont_num, 1);
407 cln_debug_pub.Add('p_ctg_name:' || p_ctg_name, 1);
408 cln_debug_pub.Add('p_itf_hdr_id:' || p_itf_hdr_id, 1);
409 cln_debug_pub.Add('p_itf_lin_id:' || p_itf_lin_id, 1);
410 cln_debug_pub.Add('p_vdr_part_num:' || p_vdr_part_num, 1);
411 cln_debug_pub.Add('p_item_desc:' || p_item_desc, 1);
412 cln_debug_pub.Add('p_item:' || p_item, 1);
413 cln_debug_pub.Add('p_item_rev:' || p_item_rev, 1);
414 cln_debug_pub.Add('p_category:' || p_category, 1);
415 cln_debug_pub.Add('p_uom:' || p_uom, 1);
416 cln_debug_pub.Add('p_item_min_ord_quan:' || p_item_min_ord_quan, 1);
417 cln_debug_pub.Add('p_price:' || p_price, 1);
418 cln_debug_pub.Add('p_price_uom:' || p_price_uom, 1);
419 cln_debug_pub.Add('p_price_currency:' || p_price_currency, 1);
420 END IF;
421
422
423 -- Need to reomve the sysdate
424 -- l_ctg_name := substr(p_ctg_name, 1, instr(p_ctg_name, ':', 1, 3)-1);
425 l_ctg_name := p_ctg_name;
426
427 /*
428 -- Whatever that comes in XML is itself is the uom code ?
429 SELECT UOM_CODE
430 INTO l_uom_code
431 FROM MTL_UNITS_OF_MEASURE_VL
432 WHERE UNIT_OF_MEASURE = p_uom;
433 */
434
435 l_uom_code := p_uom;
436 IF (l_Debug_Level <= 1) THEN
437 cln_debug_pub.Add('l_uom_code:' || l_uom_code, 1);
438 END IF;
439
440 -- Need to find if it a duplicate item
441 BEGIN
442 SELECT line_num
443 INTO l_line_num
444 FROM PO_LINES_INTERFACE
445 WHERE interface_header_id = p_itf_hdr_id
446 AND nvl(ITEM, '-1') = nvl(p_item, '-1')
447 AND nvl(UOM_CODE, '-1') = nvl(l_uom_code, '-1')
448 AND ROWNUM < 2; -- All the rows returned by this query have either the same line_num or no rows
449 EXCEPTION WHEN NO_DATA_FOUND THEN
450 -- No rows found. So go ahead and do the insertion
451 l_line_num := null;
452 END;
453
454 IF l_line_num IS NOT NULL THEN
455 IF (l_Debug_Level <= 1) THEN
456 cln_debug_pub.Add('Duplicate item:' || p_item, 1);
457 cln_debug_pub.Add('UOM - ' || l_uom_code, 1);
458 cln_debug_pub.Add('Line number found : ' || l_line_num, 1);
459 END IF;
460 x_line_num := l_line_num;
461
462 /****** Need not throw error as per bug 3430538
463 -- Duplicate Item in the Catalog: ITEM UOM - CODE
464 FND_MESSAGE.SET_NAME('CLN','CLN_DUPLICATE_ITEM');
465 FND_MESSAGE.SET_TOKEN('ITEM', p_item);
466 FND_MESSAGE.SET_TOKEN('CODE', l_uom_code);
467 x_msg_data := FND_MESSAGE.GET;
468 IF (l_Debug_Level <= 1) THEN
469 cln_debug_pub.Add('x_msg_data:' || x_msg_data, 1);
470 END IF;
471
472 RAISE_UPDATE_COLLABORATION(
473 x_return_status => l_return_status,
474 x_msg_data => l_return_msg,
475 p_ref_id => p_app_ref_id,
476 p_doc_no => NULL,
477 p_part_doc_no => NULL,
478 p_msg_text => x_msg_data,
479 p_status_code => 1,
480 p_int_ctl_num => p_int_cont_num);
481 IF l_return_status <> 'S' THEN
482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483 END IF;
484 x_return_status := 'DIE';
485 IF (l_Debug_Level <= 1) THEN
486 cln_debug_pub.Add(x_msg_data, 1);
487 END IF;
488 ********* End of commenting*/
489
490 IF (l_Debug_Level <= 2) THEN
491 cln_debug_pub.Add('EXITING PROCESS_ORDER_HEADER, WITHOUT CREATION LINE', 2);
492 END IF;
493
494 RETURN;
495
496 END IF;
497
498 SELECT cln_generic_s.nextval
499 INTO l_line_num
500 FROM DUAL;
501
502 IF (l_Debug_Level <= 1) THEN
503 cln_debug_pub.Add('l_line_num:' || l_line_num, 1);
504 END IF;
505
506 x_line_num := l_line_num;
507
508 INSERT INTO po_lines_interface(interface_header_id,
509 interface_line_id,
510 item,
511 ITEM_REVISION,
512 CATEGORY,
513 ITEM_DESCRIPTION,
514 MIN_ORDER_QUANTITY,
515 UOM_CODE,
516 line_num,
517 VENDOR_PRODUCT_NUM,
518 PRICE_BREAK_LOOKUP_CODE,
519 LINE_ATTRIBUTE1,
520 LINE_ATTRIBUTE2,
521 LINE_ATTRIBUTE3,
522 LINE_ATTRIBUTE4,
523 LINE_ATTRIBUTE5,
524 LINE_ATTRIBUTE6,
525 LINE_ATTRIBUTE7,
526 LINE_ATTRIBUTE8,
527 LINE_ATTRIBUTE9,
528 LINE_ATTRIBUTE10,
529 LINE_ATTRIBUTE11,
530 LINE_ATTRIBUTE12,
531 LINE_ATTRIBUTE13,
532 LINE_ATTRIBUTE14,
533 LINE_ATTRIBUTE15)
534 values(p_itf_hdr_id,
535 p_itf_lin_id,
536 p_item,
537 p_item_rev,
538 p_category,
539 p_item_desc,
540 p_item_min_ord_quan,
541 l_uom_code,
542 l_line_num,
543 p_vdr_part_num,
544 'NON CUMULATIVE',
545 p_attribute1,
546 p_attribute2,
547 p_attribute3,
548 p_attribute4,
549 p_attribute5,
550 p_attribute6,
551 p_attribute7,
552 p_attribute8,
553 p_attribute9,
554 p_attribute10,
555 p_attribute11,
556 p_attribute12,
557 p_attribute13,
558 p_attribute14,
559 p_attribute15);
560
561 IF (l_Debug_Level <= 1) THEN
562 cln_debug_pub.Add('Inserted a row into for the line', 1);
563 END IF;
564
565
566 /*
567 IF p_operation = 'INSERT' THEN
568 -- Create a new BPO Line
569 SELECT cln_generic_s.nextval
570 INTO l_line_num
571 FROM DUAL;
572 x_line_num := l_line_num;
573 INSERT INTO po_lines_interface(interface_header_id,
574 interface_line_id,
575 item,
576 ITEM_REVISION,
577 CATEGORY,
578 ITEM_DESCRIPTION,
579 MIN_ORDER_QUANTITY,
580 -- UOM_CODE, How to get uom code from uom ?
581 line_num,
582 VENDOR_PRODUCT_NUM,
583 LINE_ATTRIBUTE1,
584 LINE_ATTRIBUTE2,
585 LINE_ATTRIBUTE3,
586 LINE_ATTRIBUTE4,
587 LINE_ATTRIBUTE5,
588 LINE_ATTRIBUTE6,
589 LINE_ATTRIBUTE7,
590 LINE_ATTRIBUTE8,
591 LINE_ATTRIBUTE9,
592 LINE_ATTRIBUTE10,
593 LINE_ATTRIBUTE11,
594 LINE_ATTRIBUTE12,
595 LINE_ATTRIBUTE13,
596 LINE_ATTRIBUTE14,
597 LINE_ATTRIBUTE15)
598 values(p_itf_hdr_id,
599 p_itf_lin_id,
600 p_item,
601 p_item_rev,
602 p_category,
603 p_item_desc,
604 p_item_min_ord_quan,
605 l_line_num,
606 p_vdr_part_num,
607 p_attribute1,
608 p_attribute2,
609 p_attribute3,
610 p_attribute4,
611 p_attribute5,
612 p_attribute6,
613 p_attribute7,
614 p_attribute8,
615 p_attribute9,
616 p_attribute10,
617 p_attribute11,
618 p_attribute12,
619 p_attribute13,
620 p_attribute14,
621 p_attribute15);
622 ELSE
623 -- BPO Line already exist
624 -- Need to return the original line number
625 -- Should insert the line details with the existing line num ?
626 BEGIN
627 SELECT line_num
628 INTO x_line_num
629 FROM PO_LINES_ALL POL ,
630 MTL_SYSTEM_ITEMS_KFV MIS,
631 FINANCIALS_SYSTEM_PARAMS_ALL FSP
632 -- MTL_CATEGORIES_KFV MCT
633 WHERE POL.ITEM_ID = MIS.INVENTORY_ITEM_ID (+)
634 AND NVL(MIS.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID)
635 = FSP.INVENTORY_ORGANIZATION_ID
636 AND FSP.ORG_ID = POL.ORG_ID
637 AND POL.PO_HEADER_ID = p_hdr_id
638 -- (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE VENDOR_ORDER_NUM = l_ctg_name)
639 AND upper(MIS.CONCATENATED_SEGMENTS) = upper(p_item)
640 AND upper(POL.UNIT_MEAS_LOOKUP_CODE) = upper(p_uom);
641 -- NO need to compare category
642 -- AND MCT.CATEGORY_ID = POL.CATEGORY_ID
643 -- AND upper(MCT.CONCATENATED_SEGMENTS) = upper(p_category);
644
645 cln_debug_pub.Add('x_line_num:' || x_line_num, 1);
646 -- Insert the line if necessary ?
647
648 EXCEPTION
649 WHEN NO_DATA_FOUND THEN
650 -- Line does not exist, create a new line
651 cln_debug_pub.Add('Line number does not exist, creating a new row', 1);
652 SELECT cln_generic_s.nextval
653 INTO l_line_num
654 FROM DUAL;
655 x_line_num := l_line_num;
656 cln_debug_pub.Add('x_line_num:' || x_line_num, 1);
657 INSERT INTO po_lines_interface(interface_header_id,
658 interface_line_id,
659 item,
660 line_num)
661 values(p_itf_hdr_id,
662 p_itf_lin_id,
663 p_item,
664 l_line_num);
665 END;
666 END IF;
667 */
668
669 -- In the message we can put insertion or updation
670 RAISE_ADD_MESSAGE(
671 x_return_status => l_return_status,
672 x_msg_data => l_return_msg,
673 p_ictrl_no => p_int_cont_num,
674 p_ref1 => p_item,
675 p_ref2 => p_item_rev,
676 p_ref3 => p_uom,
677 p_ref4 => p_item_min_ord_quan,
678 p_ref5 => p_vdr_part_num,
679 p_dtl_msg => NULL);
680 IF l_return_status <> 'S' THEN
681 IF (l_Debug_Level <= 1) THEN
682 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
683 END IF;
684
685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686 END IF;
687
688 IF (l_Debug_Level <= 2) THEN
689 cln_debug_pub.Add('EXITING PROCESS_LINE', 2);
690 END IF;
691
692 EXCEPTION
693 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
694 ROLLBACK TO PO_UPDATE_TXN;
695 IF (l_Debug_Level <= 5) THEN
696 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
697 END IF;
698
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 x_msg_data := l_return_msg;
701 IF (l_Debug_Level <= 5) THEN
702 cln_debug_pub.Add(x_msg_data, 5);
703 END IF;
704
705 x_msg_data := 'While trying to process line details'
706 || ' for the inbound sync catalog#'
707 || p_ctg_name
708 || ', the following error is encountered:'
709 || x_msg_data;
710 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
711 IF (l_Debug_Level <= 2) THEN
712 cln_debug_pub.Add('EXITING PROCESS_LINE', 2);
713 END IF;
714
715 WHEN OTHERS THEN
716 ROLLBACK TO PO_UPDATE_TXN;
717 -- More descriptive line details ?
718 IF (l_Debug_Level <= 5) THEN
719 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
720 END IF;
721
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 l_error_code := SQLCODE;
724 l_error_msg := SQLERRM;
725 x_msg_data := l_error_code||' : '||l_error_msg;
726 IF (l_Debug_Level <= 5) THEN
727 cln_debug_pub.Add(x_msg_data, 5);
728 END IF;
729
730 x_msg_data := 'While trying to process line details'
731 || ' for the inbound sync catalog#'
732 || p_ctg_name
733 || ', the following error is encountered:'
734 || x_msg_data;
735 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
736 IF (l_Debug_Level <= 5) THEN
737 cln_debug_pub.Add('EXITING PROCESS_LINE', 2);
738 END IF;
739
740 END PROCESS_LINE;
741
742
743
744 -- Name
745 -- PROCESS_PRICE_BREAKS
746 -- Purpose
747 -- Creates a PRICE BREAK row in PO_LINES_INTERFACE
748 -- based on Catalog line details
749 -- Arguments
750 -- Catalog line details and price break details
751 -- Notes
752 -- No Specific Notes
753
754 -- BUG 3138217 - CURRENCY VALIDATION TO BE DONE ON THE BUY SIDE
755 -- Added parameter x_bpo_cur_updated IN OUT NOCOPY VARCHAR2
756
757 PROCEDURE PROCESS_PRICE_BREAKS(
758 x_return_status OUT NOCOPY VARCHAR2,
759 x_msg_data OUT NOCOPY VARCHAR2,
760 x_bpo_cur_updated IN OUT NOCOPY VARCHAR2,
761 p_app_ref_id IN VARCHAR2,
762 p_int_cont_num IN VARCHAR2,
763 p_ctg_name IN VARCHAR2,
764 p_itf_hdr_id IN NUMBER,
765 p_itf_lin_id IN NUMBER,
766 p_line_num IN NUMBER,
767 p_item IN VARCHAR2,
768 p_item_rev IN VARCHAR2,
769 p_eff_date IN DATE,
770 p_exp_date IN DATE,
771 p_quantity IN NUMBER,
772 p_price IN NUMBER,
773 p_price_uom IN VARCHAR2,
774 p_price_currency IN VARCHAR2)
775 IS
776 l_return_status VARCHAR2(1000);
777 l_return_msg VARCHAR2(2000);
778 l_debug_mode VARCHAR2(300);
779 l_error_code NUMBER;
780 l_error_msg VARCHAR2(2000);
781 l_ctg_name VARCHAR2(50);
782 l_line_ship_num NUMBER;
783 l_line_price NUMBER;
784 l_count NUMBER;
785 BEGIN
786
787 -- Sets the debug mode to be FILE
788 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
789
790 -- Initialize API return status to success
791 x_return_status := 'S';
792 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
793 x_msg_data := FND_MESSAGE.GET;
794
795 IF (l_Debug_Level <= 2) THEN
796 cln_debug_pub.Add('ENTERING PROCESS_PRICE_BREAKS', 2);
797 END IF;
798
799 IF (l_Debug_Level <= 1) THEN
800 cln_debug_pub.Add('With the following parameters:', 1);
801 cln_debug_pub.Add('p_app_ref_id:' || p_app_ref_id, 1);
802 cln_debug_pub.Add('p_int_cont_num:' || p_int_cont_num, 1);
803 cln_debug_pub.Add('p_ctg_name:' || p_ctg_name, 1);
804 cln_debug_pub.Add('p_itf_hdr_id:' || p_itf_hdr_id, 1);
805 cln_debug_pub.Add('p_itf_lin_id:' || p_itf_lin_id, 1);
806 cln_debug_pub.Add('p_line_num:' || p_line_num, 1);
807 cln_debug_pub.Add('p_item:' || p_item, 1);
808 cln_debug_pub.Add('p_item_rev:' || p_item_rev, 1);
809 cln_debug_pub.Add('p_eff_date:' || p_eff_date, 1);
810 cln_debug_pub.Add('p_exp_date:' || p_exp_date, 1);
811 cln_debug_pub.Add('p_quantity:' || p_quantity, 1);
812 cln_debug_pub.Add('p_price:' || p_price, 1);
813 cln_debug_pub.Add('p_price_uom:' || p_price_uom, 1);
814 cln_debug_pub.Add('p_price_currency:' || p_price_currency, 1);
815 cln_debug_pub.Add('x_bpo_cur_updated:' || x_bpo_cur_updated, 1);
816 END IF;
817
818
819 -- Need to reomve the sysdate
820 -- l_ctg_name := substr(p_ctg_name, 1, instr(p_ctg_name, ':', 1, 3)-1);
821 l_ctg_name := p_ctg_name;
822
823
824 -- BUG 3138217 - CURRENCY VALIDATION TO BE DONE ON THE BUY SIDE
825 -- Need to update PO interface header table with currency code.
826
827 IF x_bpo_cur_updated = 'NO' AND p_price_currency IS NOT NULL THEN
828
829 UPDATE po_headers_interface
830 SET currency_code = p_price_currency
831 WHERE interface_header_id = p_itf_hdr_id;
832
833 x_bpo_cur_updated := 'YES';
834
835 END IF;
836
837
838 -- As per the map timephase price break will come first
839 -- and then the volumephase price break
840
841 -- Is this the first timephase price break
842 SELECT count(*)
843 INTO l_count
844 FROM po_lines_interface
845 WHERE interface_header_id = p_itf_hdr_id
846 AND interface_line_id = interface_line_id
847 AND line_num = p_line_num;
848 IF (l_Debug_Level <= 1) THEN
849 cln_debug_pub.Add('l_count:' || l_count, 1);
850 END IF;
851
852
853 IF l_count = 1 THEN -- First timephase price break
854
855 -- Get the unit price at line level
856 SELECT unit_price
857 INTO l_line_price
858 FROM po_lines_interface
859 WHERE interface_header_id = p_itf_hdr_id
860 AND interface_line_id = interface_line_id
861 AND line_num = p_line_num;
862 IF (l_Debug_Level <= 1) THEN
863 cln_debug_pub.Add('l_line_price:' || l_line_price, 1);
864 END IF;
865
866
867 IF l_line_price IS NULL OR l_line_price = 0 THEN
868 -- Update the line price because price at line level is not available
869 -- it comes as the first timephase price break
870 UPDATE po_lines_interface
871 SET unit_price = p_price
872 WHERE interface_header_id = p_itf_hdr_id
873 AND interface_line_id = interface_line_id
874 AND line_num = p_line_num;
875 END IF;
876 END IF;
877
878 -- Create a new BPO Line Price Break
879 -- No need to check,if this exists
880 SELECT cln_generic_s.nextval
881 INTO l_line_ship_num
882 FROM DUAL;
883
884 INSERT INTO po_lines_interface(interface_header_id,
885 interface_line_id,
886 item,
887 ITEM_REVISION,
888 line_num,
889 shipment_num,
890 unit_price,
891 effective_date,
892 expiration_date,
893 quantity,
894 PRICE_BREAK_LOOKUP_CODE)
895 values(p_itf_hdr_id,
896 p_itf_lin_id,
897 p_item,
898 p_item_rev,
899 p_line_num,
900 l_line_ship_num,
901 p_price,
902 p_eff_date,
903 p_exp_date,
904 p_quantity,
905 'NON CUMULATIVE');
906
907 RAISE_ADD_MESSAGE(
908 x_return_status => l_return_status,
909 x_msg_data => l_return_msg,
910 p_ictrl_no => p_int_cont_num,
911 p_ref1 => p_itf_hdr_id,
912 p_ref2 => p_itf_lin_id,
913 p_ref3 => p_line_num,
914 p_ref4 => p_item,
915 p_ref5 => p_item_rev,
916 p_dtl_msg => NULL);
917 IF l_return_status <> 'S' THEN
918 IF (l_Debug_Level <= 1) THEN
919 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
920 END IF;
921
922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
923 END IF;
924
925
926 IF (l_Debug_Level <= 2) THEN
927 cln_debug_pub.Add('EXITING PROCESS_PRICE_BREAKS', 2);
928 END IF;
929
930 EXCEPTION
931 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
932 ROLLBACK TO PO_UPDATE_TXN;
933 IF (l_Debug_Level <= 5) THEN
934 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
935 END IF;
936
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
938 x_msg_data := l_return_msg;
939 IF (l_Debug_Level <= 5) THEN
940 cln_debug_pub.Add(x_msg_data, 5);
941 END IF;
942
943 x_msg_data := 'While trying to process line price break details'
944 || ' for the inbound sync catalog#'
945 || p_ctg_name
946 || ', the following error is encountered:'
947 || x_msg_data;
948 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
949 IF (l_Debug_Level <= 5) THEN
950 cln_debug_pub.Add('EXITING PROCESS_PRICE_BREAKS', 2);
951 END IF;
952
953 WHEN OTHERS THEN
954 ROLLBACK TO PO_UPDATE_TXN;
955 -- More descriptive line details ?
956 IF (l_Debug_Level <= 5) THEN
957 cln_debug_pub.Add('Rolledback PO_UPDATE_TXN transaction',5);
958 END IF;
959
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 l_error_code := SQLCODE;
962 l_error_msg := SQLERRM;
963 x_msg_data := l_error_code||' : '||l_error_msg;
964 IF (l_Debug_Level <= 5) THEN
965 cln_debug_pub.Add(x_msg_data, 5);
966 END IF;
967
968 x_msg_data := 'While trying to process line price break details'
969 || ' for the inbound sync catalog#'
970 || p_ctg_name
971 || ', the following error is encountered:'
972 || x_msg_data;
973 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(x_msg_data);
974 IF (l_Debug_Level <= 5) THEN
975 cln_debug_pub.Add('EXITING PROCESS_PRICE_BREAKS', 2);
976 END IF;
977
978 END PROCESS_PRICE_BREAKS;
979
980
981
982 -- Name
983 -- CALL_TAKE_ACTIONS
984 -- Purpose
985 -- Invokes Notification Processor TAKE_ACTIONS according to the parameter.
986 -- Arguments
987 -- Description - Error message if errored out else 'SUCCESS'
988 -- Sales Order Status
989 -- Order Line Closed - YES/NO
990 -- Notes
991 -- No specific notes.
992
993 PROCEDURE CALL_TAKE_ACTIONS(
994 p_itemtype IN VARCHAR2,
995 p_itemkey IN VARCHAR2,
996 p_actid IN NUMBER,
997 p_funcmode IN VARCHAR2,
998 x_resultout IN OUT NOCOPY VARCHAR2)
999 IS
1000 l_doc_status VARCHAR2(100);
1001 l_description VARCHAR2(1000);
1002 l_trp_id VARCHAR2(100);
1003 l_app_ref_id VARCHAR2(255);
1004 l_return_status VARCHAR2(1000);
1005 l_return_msg VARCHAR2(2000);
1006 l_error_code NUMBER;
1007 l_error_msg VARCHAR2(2000);
1008 l_msg_data VARCHAR2(1000);
1009 l_not_msg VARCHAR2(1000);
1010 l_debug_mode VARCHAR2(255);
1011 l_tp_id NUMBER;
1012 l_ret_status VARCHAR2(5);
1013 l_ctg_sync_id VARCHAR2(5);
1014 l_int_hdr_id NUMBER;
1015 l_count NUMBER;
1016 l_int_ctl_num NUMBER;
1017 BEGIN
1018
1019 -- Sets the debug mode to be FILE
1020 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
1021
1022 x_resultout:='Yes';
1023
1024 IF (l_Debug_Level <= 2) THEN
1025 cln_debug_pub.Add('ENTERING CALL_TAKE_ACTIONS API', 2);
1026 END IF;
1027
1028 IF (l_Debug_Level <= 1) THEN
1029 cln_debug_pub.Add('Parameters:', 1);
1030 END IF;
1031
1032
1033 -- Should be S for sucess
1034 l_ret_status := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
1035 IF (l_Debug_Level <= 1) THEN
1036 cln_debug_pub.Add('l_ret_status:' || l_ret_status, 1);
1037 END IF;
1038
1039 l_int_hdr_id := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER9', TRUE));
1040 IF (l_Debug_Level <= 1) THEN
1041 cln_debug_pub.Add('l_int_hdr_id:' || l_int_hdr_id, 1);
1042 END IF;
1043
1044 l_int_ctl_num := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER1', TRUE));
1045 IF (l_Debug_Level <= 1) THEN
1046 cln_debug_pub.Add('l_int_ctl_num:' || l_int_ctl_num, 1);
1047 END IF;
1048
1049 IF (l_ret_status = 'S') THEN
1050 l_doc_status := 'SUCCESS';
1051 -- Successfully processed product catalog
1052 FND_MESSAGE.SET_NAME('CLN','CLN_PO_CATALOG_SYNC_SUCCESS');
1053 l_description := FND_MESSAGE.GET;
1054 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER12', l_doc_status);
1055 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', l_description);
1056 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '00');
1057 ELSE
1058 l_doc_status := 'ERROR';
1059 l_msg_data := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER7', TRUE);
1060 IF (l_Debug_Level <= 1) THEN
1061 cln_debug_pub.Add('l_msg_data:' || l_msg_data, 1);
1062 END IF;
1063
1064 -- Error while processing product catalog
1065 FND_MESSAGE.SET_NAME('CLN','CLN_PO_CATALOG_SYNC_ERROR');
1066 FND_MESSAGE.SET_TOKEN('ERROR', l_msg_data);
1067 l_description := FND_MESSAGE.GET;
1068 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER12', l_doc_status);
1069 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', l_description);
1070 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER2', '99');
1071 END IF;
1072
1073 l_trp_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE);
1074 IF (l_Debug_Level <= 1) THEN
1075 cln_debug_pub.Add('l_tp_id:' || l_trp_id, 1);
1076 END IF;
1077
1078 l_app_ref_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
1079 IF (l_Debug_Level <= 1) THEN
1080 cln_debug_pub.Add('l_app_ref_id:' || l_app_ref_id, 1);
1081 END IF;
1082
1083 CLN_UTILS.GET_TRADING_PARTNER(l_trp_id, l_tp_id);
1084 IF (l_Debug_Level <= 1) THEN
1085 cln_debug_pub.Add('Trading Partner ID:' || l_tp_id, 1);
1086 END IF;
1087
1088 l_ctg_sync_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER8', TRUE);
1089 IF (l_Debug_Level <= 1) THEN
1090 cln_debug_pub.Add('l_ctg_sync_id:' || l_ctg_sync_id, 1);
1091 END IF;
1092
1093
1094 -- All situations and codes ?
1095 -- Error occured
1096 IF l_ret_status <> 'S' THEN
1097 -- Invalid Transaction Code
1098 IF (l_ctg_sync_id IS NULL) OR (l_ctg_sync_id <> 'A'
1099 AND l_ctg_sync_id <> 'U'
1100 AND l_ctg_sync_id <> 'R') THEN
1101 FND_MESSAGE.SET_NAME('CLN','CLN_INVALID_TXN_CODE');
1102 FND_MESSAGE.SET_TOKEN('CODE', l_ctg_sync_id);
1103 l_msg_data := FND_MESSAGE.GET;
1104 IF (l_Debug_Level <= 1) THEN
1105 cln_debug_pub.Add('Invalid Trnsaction Code',1);
1106 END IF;
1107
1108 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
1109 x_ret_code => l_return_status,
1110 x_ret_desc => l_return_msg,
1111 p_notification_code => 'PC_IN04',
1112 p_notification_desc => l_msg_data,
1113 p_status => 'ERROR',
1114 p_tp_id => to_char(l_tp_id),
1115 p_reference => l_app_ref_id,
1116 p_coll_point => 'APPS',
1117 p_int_con_no => NULL);
1118 IF l_return_status <> 'S' THEN
1119 IF (l_Debug_Level <= 1) THEN
1120 cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 1);
1121 END IF;
1122
1123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1124 END IF;
1125 RETURN;
1126 END IF;
1127
1128
1129 -- Duplicate Item Error
1130 IF l_ret_status = 'DIE' THEN
1131 IF (l_Debug_Level <= 1) THEN
1132 cln_debug_pub.Add('Duplicate Item Error',1);
1133 END IF;
1134
1135 FND_MESSAGE.SET_NAME('CLN','CLN_DUPLICATE_ITEM');
1136 l_msg_data := FND_MESSAGE.GET;
1137 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
1138 x_ret_code => l_return_status,
1139 x_ret_desc => l_return_msg,
1140 p_notification_code => 'PC_IN03',
1141 p_notification_desc => l_msg_data,
1142 p_status => 'SUCCESS',
1143 p_tp_id => to_char(l_tp_id),
1144 p_reference => l_app_ref_id,
1145 p_coll_point => 'APPS',
1146 p_int_con_no => NULL);
1147 IF l_return_status <> 'S' THEN
1148 IF (l_Debug_Level <= 1) THEN
1149 cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 1);
1150 END IF;
1151
1152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1153 END IF;
1154 RETURN;
1155 END IF;
1156
1157
1158 -- Error
1159 IF (l_Debug_Level <= 1) THEN
1160 cln_debug_pub.Add('Global Error' || l_description,1);
1161 END IF;
1162
1163 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
1164 x_ret_code => l_return_status,
1165 x_ret_desc => l_return_msg,
1166 p_notification_code => 'PC_IN02',
1167 p_notification_desc => l_description,
1168 p_status => 'ERROR',
1169 p_tp_id => to_char(l_tp_id),
1170 p_reference => l_app_ref_id,
1171 p_coll_point => 'APPS',
1172 p_int_con_no => NULL);
1173 IF l_return_status <> 'S' THEN
1174 IF (l_Debug_Level <= 1) THEN
1175 cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 1);
1176 END IF;
1177
1178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1179 END IF;
1180 RETURN;
1181 END IF;
1182
1183 -- Success
1184 FND_MESSAGE.SET_NAME('CLN','CLN_G_RET_MSG_SUCCESS');
1185 l_msg_data := FND_MESSAGE.GET;
1186 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS(
1187 x_ret_code => l_return_status,
1188 x_ret_desc => l_return_msg,
1189 p_notification_code => 'PC_IN01',
1190 p_notification_desc => l_msg_data,
1191 p_status => 'SUCCESS',
1192 p_tp_id => to_char(l_tp_id),
1193 p_reference => l_app_ref_id,
1194 p_coll_point => 'APPS',
1195 p_int_con_no => NULL);
1196 IF l_return_status <> 'S' THEN
1197 IF (l_Debug_Level <= 1) THEN
1198 cln_debug_pub.Add('CALL_TAKE_ACTIONS CALL FAILED', 1);
1199 END IF;
1200
1201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1202 END IF;
1203
1204 IF (l_Debug_Level <= 2) THEN
1205 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
1206 END IF;
1207
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 l_error_code := SQLCODE;
1211 l_error_msg := SQLERRM;
1212
1213 x_resultout := 'ERROR:' || l_error_code || ':' || l_error_msg;
1214 IF (l_Debug_Level <= 5) THEN
1215 cln_debug_pub.Add( l_error_code || ':' || l_error_msg, 5);
1216 END IF;
1217
1218 IF (l_Debug_Level <= 2) THEN
1219 cln_debug_pub.Add('EXITING CALL_TAKE_ACTIONS API', 2);
1220 END IF;
1221 END CALL_TAKE_ACTIONS;
1222
1223
1224 -- Name
1225 -- SET_ITEM_ATTRIBUTES
1226 -- Purpose
1227 -- Sets the workflow item attributes requires
1228 -- Arguments
1229 -- Notes
1230 -- No specific notes.
1231
1232 PROCEDURE SET_ITEM_ATTRIBUTES(
1233 p_itemtype IN VARCHAR2,
1234 p_itemkey IN VARCHAR2,
1235 p_actid IN NUMBER,
1236 p_funcmode IN VARCHAR2,
1237 x_resultout IN OUT NOCOPY VARCHAR2)
1238 IS
1239 l_error_code NUMBER;
1240 l_error_msg VARCHAR2(2000);
1241 l_msg_data VARCHAR2(1000);
1242 l_not_msg VARCHAR2(1000);
1243 l_debug_mode VARCHAR2(255);
1244 l_approval_status VARCHAR2(255);
1245 l_create_src_rules VARCHAR2(255);
1246 l_create_upd_items VARCHAR2(255);
1247 l_doc_types VARCHAR2(255);
1248 l_rel_gen_method VARCHAR2(255);
1249 l_def_buyer VARCHAR2(255);
1250 l_tp_header_id NUMBER;
1251 l_org_id NUMBER;
1252 BEGIN
1253
1254 -- Sets the debug mode to be FILE
1255 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
1256
1257 x_resultout:='Yes';
1258
1259 IF (l_Debug_Level <= 2) THEN
1260 cln_debug_pub.Add('ENTERING SET_ITEM_ATTRIBUTES API', 2);
1261 END IF;
1262
1263 IF (l_Debug_Level <= 1) THEN
1264 cln_debug_pub.Add('Parameters:', 1);
1265 END IF;
1266
1267
1268 -- Get Profile Option values
1269 l_approval_status := FND_PROFILE.VALUE('CLN_2A1_PO_APPROVAL_STATUS');
1270 IF (l_Debug_Level <= 1) THEN
1271 cln_debug_pub.Add('l_approval_status:' || l_approval_status, 1);
1272 END IF;
1273
1274 l_create_src_rules := FND_PROFILE.VALUE('CLN_2A1_PO_CREATE_SOURCING_RULES');
1275 IF (l_Debug_Level <= 1) THEN
1276 cln_debug_pub.Add('l_create_src_rules:' || l_create_src_rules, 1);
1277 END IF;
1278
1279 l_create_upd_items := FND_PROFILE.VALUE('CLN_2A1_PO_CREATE_UPDATE_ITEMS');
1280 IF (l_Debug_Level <= 1) THEN
1281 cln_debug_pub.Add('l_create_upd_items:' || l_create_upd_items, 1);
1282 END IF;
1283
1284 l_doc_types := FND_PROFILE.VALUE('CLN_2A1_PO_PDOI_DOCUMENT_TYPES');
1285 IF (l_Debug_Level <= 1) THEN
1286 cln_debug_pub.Add('l_doc_types:' || l_doc_types, 1);
1287 END IF;
1288
1289 l_rel_gen_method := FND_PROFILE.VALUE('CLN_2A1_PO_PDOI_REL_GEN_METHOD');
1290 IF (l_Debug_Level <= 1) THEN
1291 cln_debug_pub.Add('l_rel_gen_method:' || l_rel_gen_method, 1);
1292 END IF;
1293
1294 l_def_buyer := FND_PROFILE.VALUE('CLN_2A1_PO_PDOI_VALID_AGENTS');
1295 IF (l_Debug_Level <= 1) THEN
1296 cln_debug_pub.Add('l_def_buyer:' || l_def_buyer, 1);
1297 END IF;
1298
1299 l_tp_header_id := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE));
1300 IF (l_Debug_Level <= 1) THEN
1301 cln_debug_pub.Add('l_tp_header_id:' || l_tp_header_id, 1);
1302 END IF;
1303
1304 -- The following statement should not throw any exception
1305 SELECT org_id
1306 INTO l_org_id
1307 FROM ecx_tp_headers eth, po_vendor_sites_all povs
1308 WHERE eth.tp_header_id = l_tp_header_id
1309 and povs.vendor_site_id = eth.party_site_id;
1310
1311 IF (l_Debug_Level <= 1) THEN
1312 cln_debug_pub.Add('l_org_id:' || l_org_id, 1);
1313 END IF;
1314
1315 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ARG1', l_def_buyer);
1316 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ARG3', l_create_upd_items);
1317 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ARG4', l_create_src_rules);
1318 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ARG5', l_approval_status);
1319 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ARG6', l_rel_gen_method);
1320 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ORG_ID', l_org_id);
1321 -- Global Agreement ?
1322 -- wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ARG7', TRUE);
1323
1324 IF (l_Debug_Level <= 2) THEN
1325 cln_debug_pub.Add('EXITING SET_ITEM_ATTRIBUTES API', 2);
1326 END IF;
1327
1328 EXCEPTION
1329 WHEN OTHERS THEN
1330 l_error_code := SQLCODE;
1331 l_error_msg := SQLERRM;
1332
1333 x_resultout := 'ERROR:' || l_error_code || ':' || l_error_msg;
1334 IF (l_Debug_Level <= 5) THEN
1335 cln_debug_pub.Add( l_error_code || ':' || l_error_msg, 5);
1336 END IF;
1337
1338 IF (l_Debug_Level <= 5) THEN
1339 cln_debug_pub.Add('EXITING SET_ITEM_ATTRIBUTES API', 2);
1340 END IF;
1341
1342 END SET_ITEM_ATTRIBUTES;
1343
1344
1345 -- Name
1346 -- SET_ACTION_INTERNAL
1347 -- Purpose
1348 -- Sets the ACTION column of po_heasers_interface to either CREATE or UPDATE
1349 -- Arguments
1350 -- Notes
1351 -- No specific notes.
1352
1353 PROCEDURE SET_ACTION_INTERNAL(
1354 x_resultout IN OUT NOCOPY VARCHAR2,
1355 p_catalog_name IN VARCHAR2,
1356 p_batch_id IN NUMBER,
1357 p_vendor_id IN NUMBER)
1358 IS
1359 PRAGMA AUTONOMOUS_TRANSACTION;
1360 l_error_code NUMBER;
1361 l_error_msg VARCHAR2(2000);
1362 l_msg_data VARCHAR2(1000);
1363 l_not_msg VARCHAR2(1000);
1364 l_debug_mode VARCHAR2(255);
1365 l_po_header_id NUMBER;
1366 l_interface_hdr_rec_count NUMBER;
1367 BEGIN
1368
1369 IF (l_Debug_Level <= 2) THEN
1370 cln_debug_pub.Add('ENTERING SET_ACTION_INTERNAL API', 2);
1371 cln_debug_pub.Add('p_catalog_name:'||p_catalog_name, 2);
1372 cln_debug_pub.Add('p_batch_id:'||p_batch_id, 2);
1373 cln_debug_pub.Add('p_vendor_id:'||p_vendor_id, 2);
1374
1375 END IF;
1376 -- To lock the rows
1377 UPDATE po_headers_interface
1378 SET vendor_doc_num = p_catalog_name
1379 WHERE vendor_doc_num = p_catalog_name
1380 AND vendor_id = p_vendor_id
1381 AND ACTION is NULL;
1382
1383 IF (l_Debug_Level <= 1) THEN
1384 cln_debug_pub.Add('Locked the pending rows of interface tables of the same catalog', 1);
1385 END IF;
1386
1387 BEGIN
1388 SELECT po_header_id
1389 INTO l_po_header_id
1390 FROM po_headers_all
1391 WHERE vendor_order_num = p_catalog_name
1392 AND vendor_id = p_vendor_id;
1393 EXCEPTION
1394 WHEN NO_DATA_FOUND THEN
1395 l_po_header_id := NULL;
1396 IF (l_Debug_Level <= 1) THEN
1397 cln_debug_pub.Add('PO not found in po_headers_all', 1);
1398 END IF;
1399 END;
1400
1401 IF (l_Debug_Level <= 1) THEN
1402 cln_debug_pub.Add('l_po_header_id:' || l_po_header_id, 1);
1403 END IF;
1404
1405 IF (l_po_header_id > 0 ) THEN
1406 --There is an existing PO, set the action to UPDATE and retrun from this procedure
1407 UPDATE po_headers_interface
1408 SET action = 'UPDATE'
1409 WHERE batch_id = p_batch_id;
1410 x_resultout := 'Y';
1411 COMMIT;
1412 IF (l_Debug_Level <= 5) THEN
1413 cln_debug_pub.Add('EXITING SET_ACTION_INTERNAL API', 2);
1414 END IF;
1415 RETURN;
1416 END IF;
1417
1418 BEGIN
1419 l_interface_hdr_rec_count := 0;
1420 -- Check whether there is any row, which is in process, for the same catalog. If so, then Wait.
1421 SELECT count('x')
1422 INTO l_interface_hdr_rec_count
1423 FROM po_headers_interface
1424 WHERE vendor_doc_num = p_catalog_name
1425 AND vendor_id = p_vendor_id
1426 AND ACTION = 'ORIGINAL'
1427 AND nvl(process_code,'~') NOT IN ('ACCEPTED', 'REJECTED');
1428 EXCEPTION
1429 WHEN NO_DATA_FOUND THEN
1430 l_interface_hdr_rec_count := 0;
1431 IF (l_Debug_Level <= 1) THEN
1432 cln_debug_pub.Add('No catalogs found', 1);
1433 END IF;
1434 END;
1435
1436 IF (l_Debug_Level <= 1) THEN
1437 cln_debug_pub.Add('l_interface_hdr_rec_count:' || l_interface_hdr_rec_count, 1);
1438 END IF;
1439
1440 IF l_interface_hdr_rec_count > 0 THEN
1441 --There is already a row which is in process. So wait for some time
1442 x_resultout := 'N';
1443 ELSE
1444 BEGIN
1445 SELECT po_header_id
1446 INTO l_po_header_id
1447 FROM po_headers_all
1448 WHERE vendor_order_num = p_catalog_name
1449 AND vendor_id = p_vendor_id;
1450 EXCEPTION
1451 WHEN NO_DATA_FOUND THEN
1452 l_po_header_id := NULL;
1453 IF (l_Debug_Level <= 1) THEN
1454 cln_debug_pub.Add('PO not found in po_headers_all', 1);
1455 END IF;
1456 END;
1457
1458 IF (l_Debug_Level <= 1) THEN
1459 cln_debug_pub.Add('l_po_header_id:' || l_po_header_id, 1);
1460 END IF;
1461
1462 IF (l_po_header_id > 0 ) THEN
1463 --There is an existing PO, set the action to UPDATE and retrun from this procedure
1464 UPDATE po_headers_interface
1465 SET action = 'UPDATE'
1466 WHERE batch_id = p_batch_id;
1467 ELSE
1468 UPDATE po_headers_interface
1469 SET action = 'ORIGINAL'
1470 WHERE batch_id = p_batch_id;
1471 END IF;
1472 x_resultout := 'Y';
1473 END IF;
1474
1475 COMMIT;
1476 IF (l_Debug_Level <= 2) THEN
1477 cln_debug_pub.Add('EXITING SET_ACTION_INTERNAL API', 2);
1478 END IF;
1479
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 l_error_code := SQLCODE;
1483 l_error_msg := SQLERRM;
1484 x_resultout := 'ERROR:' || l_error_code || ':' || l_error_msg;
1485 IF (l_Debug_Level <= 5) THEN
1486 cln_debug_pub.Add( l_error_code || ':' || l_error_msg, 5);
1487 END IF;
1488 ROLLBACK;
1489 IF (l_Debug_Level <= 5) THEN
1490 cln_debug_pub.Add('EXITING SET_ACTION_INTERNAL API', 2);
1491 END IF;
1492 END SET_ACTION_INTERNAL;
1493
1494
1495 -- Name
1496 -- SET_ACTION_CREATE_OR_UPDATE
1497 -- Purpose
1498 -- Sets the ACTION column of po_heasers_interface to either CREATE or UPDATE
1499 -- Arguments
1500 -- Notes
1501 -- No specific notes.
1502
1503 PROCEDURE SET_ACTION_CREATE_OR_UPDATE(
1504 p_itemtype IN VARCHAR2,
1505 p_itemkey IN VARCHAR2,
1506 p_actid IN NUMBER,
1507 p_funcmode IN VARCHAR2,
1508 x_resultout IN OUT NOCOPY VARCHAR2)
1509 IS
1510 l_error_code NUMBER;
1511 l_error_msg VARCHAR2(2000);
1512 l_msg_data VARCHAR2(1000);
1513 l_not_msg VARCHAR2(1000);
1514 l_debug_mode VARCHAR2(255);
1515 l_batch_id NUMBER;
1516 l_po_header_id NUMBER;
1517 l_vendor_id NUMBER;
1518 l_tp_hdr_id NUMBER;
1519 l_catalog_name VARCHAR2(255);
1520 l_action VARCHAR2(255);
1521 l_operation VARCHAR2(255);
1522
1523 BEGIN
1524
1525 -- Sets the debug mode to be FILE
1526 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
1527
1528
1529 IF (l_Debug_Level <= 2) THEN
1530 cln_debug_pub.Add('ENTERING SET_ACTION_CREATE_OR_UPDATE API', 2);
1531 END IF;
1532
1533 -- Get the batch ID, which is being imported
1534 l_operation := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER3', TRUE);
1535 IF (l_Debug_Level <= 1) THEN
1536 cln_debug_pub.Add('l_operation:' || l_operation, 1);
1537 END IF;
1538
1539 IF (l_operation = 'UPDATE') THEN
1540 --If the operation is already update, then need not do anything
1541 IF (l_Debug_Level <= 1) THEN
1542 cln_debug_pub.Add('Operation is update. Nothing to do.', 1);
1543 END IF;
1544 IF (l_Debug_Level <= 2) THEN
1545 cln_debug_pub.Add('EXITING SET_ACTION_CREATE_OR_UPDATE API', 2);
1546 END IF;
1547 x_resultout:='Y';
1548 RETURN;
1549 END IF;
1550
1551 -- Get the batch ID, which is being imported
1552 l_batch_id := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER9', TRUE));
1553 IF (l_Debug_Level <= 1) THEN
1554 cln_debug_pub.Add('l_batch_id:' || l_batch_id, 1);
1555 END IF;
1556
1557 l_catalog_name :=wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', TRUE);
1558 IF (l_Debug_Level <= 1) THEN
1559 cln_debug_pub.Add('l_catalog_name:' || l_catalog_name, 1);
1560 END IF;
1561
1562 l_tp_hdr_id :=to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE));
1563 IF (l_Debug_Level <= 1) THEN
1564 cln_debug_pub.Add('l_tp_hdr_id:' || l_tp_hdr_id, 1);
1565 cln_debug_pub.Add('About to call CLN_UTILS.GET_TRADING_PARTNER', 1);
1566 END IF;
1567
1568 CLN_UTILS.GET_TRADING_PARTNER(l_tp_hdr_id, l_vendor_id);
1569
1570 IF (l_Debug_Level <= 1) THEN
1571 cln_debug_pub.Add('Out of CLN_UTILS.GET_TRADING_PARTNER', 1);
1572 cln_debug_pub.Add('Vendor ID:' || l_vendor_id, 1);
1573 END IF;
1574
1575 SET_ACTION_INTERNAL( x_resultout => x_resultout,
1576 p_catalog_name => l_catalog_name,
1577 p_batch_id => l_batch_id,
1578 p_vendor_id => l_vendor_id);
1579
1580 IF (l_Debug_Level <= 1) THEN
1581 cln_debug_pub.Add('Returned from SET_ACTION_INTERNAL', 1);
1582 END IF;
1583
1584 IF (l_Debug_Level <= 2) THEN
1585 cln_debug_pub.Add('EXITING SET_ACTION_CREATE_OR_UPDATE API', 2);
1586 END IF;
1587
1588 EXCEPTION
1589 WHEN OTHERS THEN
1590 l_error_code := SQLCODE;
1591 l_error_msg := SQLERRM;
1592 x_resultout := 'ERROR:' || l_error_code || ':' || l_error_msg;
1593 IF (l_Debug_Level <= 5) THEN
1594 cln_debug_pub.Add( l_error_code || ':' || l_error_msg, 5);
1595 END IF;
1596
1597 IF (l_Debug_Level <= 5) THEN
1598 cln_debug_pub.Add('EXITING SET_ACTION_CREATE_OR_UPDATE API', 2);
1599 END IF;
1600
1601 END SET_ACTION_CREATE_OR_UPDATE;
1602
1603
1604 -- Name
1605 -- IS_PROCESSING_ERROR
1606 -- Purpose
1607 -- Checks if any error has occured and returns the same
1608 -- Arguments
1609 -- Notes
1610 -- No specific notes.
1611
1612 PROCEDURE IS_PROCESSING_ERROR(
1613 p_itemtype IN VARCHAR2,
1614 p_itemkey IN VARCHAR2,
1615 p_actid IN NUMBER,
1616 p_funcmode IN VARCHAR2,
1617 x_resultout IN OUT NOCOPY VARCHAR2)
1618 IS
1619 l_ret_status VARCHAR2(100);
1620 l_error_code NUMBER;
1621 l_error_msg VARCHAR2(2000);
1622 l_msg_data VARCHAR2(1000);
1623 l_not_msg VARCHAR2(1000);
1624 l_debug_mode VARCHAR2(255);
1625 BEGIN
1626
1627 -- Sets the debug mode to be FILE
1628 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
1629
1630 x_resultout := 'COMPLETE:F';
1631
1632 IF (l_Debug_Level <= 2) THEN
1633 cln_debug_pub.Add('ENTERING IS_PROCESSING_ERROR API', 2);
1634 END IF;
1635
1636 IF (l_Debug_Level <= 1) THEN
1637 cln_debug_pub.Add('Parameters:', 1);
1638 END IF;
1639
1640 -- Should be S for sucess
1641 l_ret_status := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
1642 IF (l_Debug_Level <= 1) THEN
1643 cln_debug_pub.Add('l_ret_status:' || l_ret_status, 1);
1644 END IF;
1645
1646 IF l_ret_status <> 'S' THEN
1647 x_resultout := 'COMPLETE:T';
1648 END IF;
1649
1650 IF (l_Debug_Level <= 1) THEN
1651 cln_debug_pub.Add('x_resultout:' || x_resultout, 1);
1652 END IF;
1653
1654
1655 IF (l_Debug_Level <= 2) THEN
1656 cln_debug_pub.Add('EXITING IS_PROCESSING_ERROR API', 2);
1657 END IF;
1658
1659 EXCEPTION
1660 WHEN OTHERS THEN
1661 x_resultout := 'COMPLETE:T';
1662 l_error_code := SQLCODE;
1663 l_error_msg := SQLERRM;
1664 x_resultout := 'ERROR:' || l_error_code || ':' || l_error_msg;
1665 IF (l_Debug_Level <= 5) THEN
1666 cln_debug_pub.Add( l_error_code || ':' || l_error_msg, 5);
1667 END IF;
1668 IF (l_Debug_Level <= 5) THEN
1669 cln_debug_pub.Add('EXITING IS_PROCESSING_ERROR API', 2);
1670 END IF;
1671
1672 END IS_PROCESSING_ERROR;
1673
1674
1675 -- Name
1676 -- LOG_PO_OI_ERRORS
1677 -- Purpose
1678 -- Quries PO Open Interface error table and captures the errors
1679 -- in collaboration addmessages
1680 -- Arguments
1681 -- Interface Header ID available as a item attribute
1682 -- Notes
1683 -- No specific notes.
1684
1685 PROCEDURE LOG_PO_OI_ERRORS(
1686 p_itemtype IN VARCHAR2,
1687 p_itemkey IN VARCHAR2,
1688 p_actid IN NUMBER,
1689 p_funcmode IN VARCHAR2,
1690 x_resultout IN OUT NOCOPY VARCHAR2)
1691 IS
1692 l_return_status VARCHAR2(100);
1693 l_return_msg VARCHAR2(2000);
1694 l_app_ref_id VARCHAR2(255);
1695 l_error_code NUMBER;
1696 l_error_msg VARCHAR2(2000);
1697 l_msg_data VARCHAR2(1000);
1698 l_not_msg VARCHAR2(1000);
1699 l_debug_mode VARCHAR2(255);
1700 l_error_status VARCHAR2(255);
1701 l_int_hdr_id NUMBER;
1702 l_count NUMBER;
1703 l_int_ctl_num NUMBER;
1704 l_vendor_id NUMBER;
1705 l_tp_hdr_id NUMBER;
1706 l_catalog_name VARCHAR2(255);
1707 l_bpo_number VARCHAR2(255);
1708 -- Cursor to retrieve all the user defined actions
1709 CURSOR PO_OPI_ERRORS(p_int_hdr_id NUMBER) IS
1710 SELECT INTERFACE_LINE_ID, BATCH_ID,
1711 TABLE_NAME, COLUMN_NAME, ERROR_MESSAGE, ERROR_MESSAGE_NAME
1712 FROM PO_INTERFACE_ERRORS
1713 WHERE INTERFACE_HEADER_ID = p_int_hdr_id;
1714 BEGIN
1715
1716 -- Sets the debug mode to be FILE
1717 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
1718 x_resultout:='Yes';
1719
1720 IF (l_Debug_Level <= 2) THEN
1721 cln_debug_pub.Add('ENTERING LOG_PO_OI_ERRORS API', 2);
1722 END IF;
1723
1724 IF (l_Debug_Level <= 1) THEN
1725 cln_debug_pub.Add('Parameters:', 1);
1726 END IF;
1727
1728
1729 l_int_hdr_id := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER9', TRUE));
1730 IF (l_Debug_Level <= 1) THEN
1731 cln_debug_pub.Add('l_int_hdr_id:' || l_int_hdr_id, 1);
1732 END IF;
1733
1734
1735 l_app_ref_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
1736 IF (l_Debug_Level <= 1) THEN
1737 cln_debug_pub.Add('l_app_ref_id:' || l_app_ref_id, 1);
1738 END IF;
1739
1740
1741 l_int_ctl_num := to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER1', TRUE));
1742 IF (l_Debug_Level <= 1) THEN
1743 cln_debug_pub.Add('l_int_ctl_num:' || l_int_ctl_num, 1);
1744 END IF;
1745
1746 -- Does PO Open Interface errored out
1747 select count(*)
1748 into l_count
1749 from po_interface_errors
1750 where interface_header_id = l_int_hdr_id;
1751
1752 -- If errored out, open a cursor ? and get all the error rows in po_interface_errors
1753 -- and add them to collaboration message
1754 IF l_count = 0 THEN
1755
1756 --Bug : 3732150
1757 --Get BPO Number from po headers all
1758 IF (l_Debug_Level <= 1) THEN
1759 cln_debug_pub.Add('Trying to get BPO Number from PO Headers All', 1);
1760 END IF;
1761
1762 l_catalog_name := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER5', TRUE);
1763 IF (l_Debug_Level <= 1) THEN
1764 cln_debug_pub.Add('l_catalog_name:' || l_catalog_name, 1);
1765 END IF;
1766
1767 l_tp_hdr_id :=to_number(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE));
1768 IF (l_Debug_Level <= 1) THEN
1769 cln_debug_pub.Add('l_tp_hdr_id:' || l_tp_hdr_id, 1);
1770 cln_debug_pub.Add('About to call CLN_UTILS.GET_TRADING_PARTNER', 1);
1771 END IF;
1772
1773 CLN_UTILS.GET_TRADING_PARTNER(l_tp_hdr_id, l_vendor_id);
1774
1775 IF (l_Debug_Level <= 1) THEN
1776 cln_debug_pub.Add('Out of CLN_UTILS.GET_TRADING_PARTNER', 1);
1777 cln_debug_pub.Add('Vendor ID:' || l_vendor_id, 1);
1778 END IF;
1779
1780 -- Query for BPO number based on Catalog name
1781 -- BUG 3155860 - MULTIPLE BPO FOR THE SAME VENDOR DOC NUMBER CAN ALSO BE TAKEN CARE
1782 -- Canceled and Closed BPO are not taken into consideration
1783 BEGIN
1784 SELECT segment1
1785 INTO l_bpo_number
1786 FROM PO_HEADERS_ALL
1787 WHERE VENDOR_ORDER_NUM = l_catalog_name
1788 AND vendor_id = l_vendor_id -- Bug #5006663
1789 AND NVL(CANCEL_FLAG, 'N') = 'N'
1790 AND NVL(CLOSED_CODE, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED');
1791 EXCEPTION
1792 WHEN NO_DATA_FOUND THEN
1793 l_bpo_number := NULL;
1794 WHEN OTHERS THEN
1795 l_bpo_number := NULL;
1796 IF (l_Debug_Level <= 5) THEN
1797 cln_debug_pub.Add('Exception while trying to obtain the BPO number',5);
1798 END IF;
1799 l_error_code := SQLCODE;
1800 l_error_msg := SQLERRM;
1801 l_msg_data := l_error_code||' : '||l_error_msg;
1802 IF (l_Debug_Level <= 5) THEN
1803 cln_debug_pub.Add(l_msg_data, 5);
1804 END IF;
1805
1806 l_msg_data := 'While trying to obtain BPO number'
1807 || ' for the inbound sync catalog#'
1808 || l_catalog_name
1809 || ', the following error is encountered:'
1810 || l_msg_data;
1811 IF (l_Debug_Level <= 5) THEN
1812 cln_debug_pub.Add(l_msg_data, 5);
1813 END IF;
1814 END;
1815
1816 IF (l_Debug_Level <= 1) THEN
1817 cln_debug_pub.Add('l_bpo_number:' || l_bpo_number, 1);
1818 END IF;
1819
1820 FND_MESSAGE.SET_NAME('CLN','CLN_OPEN_IF_SUCCESS');-- Imported product catalog
1821 l_msg_data := FND_MESSAGE.GET;
1822 RAISE_UPDATE_COLLABORATION(
1823 x_return_status => l_return_status,
1824 x_msg_data => l_return_msg,
1825 p_ref_id => l_app_ref_id,
1826 p_doc_no => l_bpo_number,
1827 p_part_doc_no => NULL,
1828 p_msg_text => l_msg_data,
1829 p_status_code => 0,
1830 p_int_ctl_num => l_int_ctl_num);
1831 IF l_return_status <> 'S' THEN
1832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1833 END IF;
1834 ELSE
1835 FND_MESSAGE.SET_NAME('CLN','CLN_OPEN_IF_ERROR');
1836 l_msg_data := FND_MESSAGE.GET;
1837 l_error_status := FND_API.G_RET_STS_ERROR;
1838 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', l_error_status);
1839 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER7', l_msg_data);
1840 RAISE_UPDATE_COLLABORATION(
1841 x_return_status => l_return_status,
1842 x_msg_data => l_return_msg,
1843 p_ref_id => l_app_ref_id,
1844 p_doc_no => NULL,
1845 p_part_doc_no => NULL,
1846 p_msg_text => l_msg_data,
1847 p_status_code => 1,
1848 p_int_ctl_num => l_int_ctl_num);
1849 IF l_return_status <> 'S' THEN
1850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1851 END IF;
1852 FOR ERRORS IN PO_OPI_ERRORS(l_int_hdr_id) LOOP
1853 IF (l_Debug_Level <= 1) THEN
1854 cln_debug_pub.Add('Obtained cursor row for each error', 1);
1855 END IF;
1856
1857 RAISE_ADD_MESSAGE(
1858 x_return_status => l_return_status,
1859 x_msg_data => l_return_msg,
1860 p_ictrl_no => l_int_ctl_num,
1861 p_ref1 => l_int_hdr_id,
1862 p_ref2 => ERRORS.INTERFACE_LINE_ID,
1863 p_ref3 => ERRORS.BATCH_ID,
1864 p_ref4 => ERRORS.TABLE_NAME,
1865 p_ref5 => ERRORS.COLUMN_NAME,
1866 p_dtl_msg => ERRORS.ERROR_MESSAGE);
1867 IF l_return_status <> 'S' THEN
1868 IF (l_Debug_Level <= 1) THEN
1869 cln_debug_pub.Add('RAISE_ADD_MESSAGE CALL FAILED', 1);
1870 END IF;
1871
1872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1873 END IF;
1874 END LOOP;
1875 END IF;
1876
1877 IF (l_Debug_Level <= 2) THEN
1878 cln_debug_pub.Add('EXITING LOG_PO_OI_ERRORS API', 2);
1879 END IF;
1880
1881 EXCEPTION
1882 WHEN OTHERS THEN
1883 l_error_code := SQLCODE;
1884 l_error_msg := SQLERRM;
1885 x_resultout := 'ERROR:' || l_error_code || ':' || l_error_msg;
1886 IF (l_Debug_Level <= 5) THEN
1887 cln_debug_pub.Add( l_error_code || ':' || l_error_msg, 5);
1888 END IF;
1889 IF (l_Debug_Level <= 5) THEN
1890 cln_debug_pub.Add('EXITING LOG_PO_OI_ERRORS API', 2);
1891 END IF;
1892
1893 END LOG_PO_OI_ERRORS;
1894
1895
1896 -- Name
1897 -- GET_TRADING_PARTNER_DETAILS
1898 -- Purpose
1899 -- This procedure returns back the trading partner id
1900 -- and trading partner site id based the header id
1901 --
1902 -- Arguments
1903 -- Header ID
1904 -- Notes
1905 -- No specific notes.
1906
1907 PROCEDURE GET_TRADING_PARTNER_DETAILS(
1908 x_tp_id OUT NOCOPY NUMBER,
1909 x_tp_site_id OUT NOCOPY NUMBER,
1910 p_tp_header_id IN NUMBER)
1911 IS
1912 l_debug_mode VARCHAR2(255);
1913 l_tp_id NUMBER;
1914 l_tp_site_id NUMBER;
1915 BEGIN
1916 -- Sets the debug mode to be FILE
1917 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
1918 IF (l_Debug_Level <= 2) THEN
1919 cln_debug_pub.Add('ENTERING GET_TRADING_PARTNER_DETAILS', 2);
1920 END IF;
1921
1922
1923 IF (l_Debug_Level <= 1) THEN
1924 cln_debug_pub.Add('p_tp_header_id:' || p_tp_header_id, 1);
1925 END IF;
1926
1927
1928 SELECT PARTY_ID, PARTY_SITE_ID
1929 INTO l_tp_id, l_tp_site_id
1930 FROM ECX_TP_HEADERS
1931 WHERE TP_HEADER_ID = p_tp_header_id;
1932
1933 IF (l_Debug_Level <= 1) THEN
1934 cln_debug_pub.Add('l_tp_id:' || l_tp_id, 1);
1935 END IF;
1936
1937 IF (l_Debug_Level <= 1) THEN
1938 cln_debug_pub.Add('l_tp_site_id:' || l_tp_site_id, 1);
1939 END IF;
1940
1941
1942 x_tp_id := l_tp_id;
1943 x_tp_site_id := l_tp_site_id;
1944
1945 IF (l_Debug_Level <= 2) THEN
1946 cln_debug_pub.Add('GET_TRADING_PARTNER_DETAILS', 2);
1947 END IF;
1948
1949 END GET_TRADING_PARTNER_DETAILS;
1950
1951
1952 -- Name
1953 -- RAISE_UPDATE_COLLABORATION
1954 -- Purpose
1955 -- This procedure raises an event to update a collaboration.
1956 --
1957 -- Arguments
1958 --
1959 -- Notes
1960 -- No specific notes.
1961
1962 PROCEDURE RAISE_UPDATE_COLLABORATION(
1963 x_return_status OUT NOCOPY VARCHAR2,
1964 x_msg_data OUT NOCOPY VARCHAR2,
1965 p_ref_id IN VARCHAR2,
1966 p_doc_no IN VARCHAR2,
1967 p_part_doc_no IN VARCHAR2,
1968 p_msg_text IN VARCHAR2,
1969 p_status_code IN NUMBER,
1970 p_int_ctl_num IN VARCHAR2)
1971 IS
1972 l_cln_ch_parameters wf_parameter_list_t;
1973 l_event_key NUMBER;
1974 l_error_code NUMBER;
1975 l_error_msg VARCHAR2(2000);
1976 l_debug_mode VARCHAR2(255);
1977 l_doc_status VARCHAR2(255);
1978 BEGIN
1979 -- Sets the debug mode to be FILE
1980 --l_debug_mode := cln_debug_pub.Set_Debug_Mode('FILE');
1981 IF (l_Debug_Level <= 2) THEN
1982 cln_debug_pub.Add('ENTERING RAISE_UPDATE_COLLABORATION', 2);
1983 END IF;
1984
1985
1986 -- Initialize API return status to success
1987 x_return_status := FND_API.G_RET_STS_SUCCESS;
1988
1989 FND_MESSAGE.SET_NAME('CLN','CLN_CH_EVENT_RAISED');
1990 FND_MESSAGE.SET_TOKEN('EVENT','Update');
1991 x_msg_data := FND_MESSAGE.GET;
1992
1993 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
1994
1995 IF (l_Debug_Level <= 1) THEN
1996 cln_debug_pub.Add('With the following parameters', 1);
1997 cln_debug_pub.Add('p_ref_id' || p_ref_id, 1);
1998 cln_debug_pub.Add('p_doc_no:' || p_doc_no, 1);
1999 cln_debug_pub.Add('p_status_code:' || p_status_code, 1);
2000 cln_debug_pub.Add('p_msg_text:' || p_msg_text, 1);
2001 cln_debug_pub.Add('p_part_doc_no:' || p_part_doc_no, 1);
2002 cln_debug_pub.Add('p_int_ctl_num:' || p_int_ctl_num, 1);
2003 END IF;
2004
2005
2006 IF p_status_code = 0 THEN
2007 l_doc_status := 'SUCCESS';
2008 ELSE
2009 l_doc_status := 'ERROR';
2010 END IF;
2011
2012 IF (l_Debug_Level <= 1) THEN
2013 cln_debug_pub.Add('l_doc_status:' || l_doc_status, 1);
2014 END IF;
2015
2016
2017 l_cln_ch_parameters := wf_parameter_list_t();
2018
2019 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_int_ctl_num, l_cln_ch_parameters);
2020 WF_EVENT.AddParameterToList('REFERENCE_ID', p_ref_id, l_cln_ch_parameters);
2021
2022 WF_EVENT.AddParameterToList('DOCUMENT_NO', p_doc_no, l_cln_ch_parameters);
2023
2024 WF_EVENT.AddParameterToList('PARTNER_DOCUMENT_NO', p_part_doc_no, l_cln_ch_parameters);
2025 WF_EVENT.AddParameterToList('ORIGINATOR_REFERENCE', p_doc_no, l_cln_ch_parameters);
2026
2027 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', l_doc_status, l_cln_ch_parameters);
2028 WF_EVENT.AddParameterToList('MESSAGE_TEXT', p_msg_text, l_cln_ch_parameters);
2029
2030 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',
2031 l_event_key, NULL, l_cln_ch_parameters, NULL);
2032 IF (l_Debug_Level <= 1) THEN
2033 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update raised', 1);
2034 END IF;
2035
2036
2037 IF (l_Debug_Level <= 2) THEN
2038 cln_debug_pub.Add('EXITING RAISE_UPDATE_COLLABORATION', 2);
2039 END IF;
2040
2041 EXCEPTION
2042 WHEN OTHERS THEN
2043 l_error_code := SQLCODE;
2044 l_error_msg := SQLERRM;
2045 x_return_status := FND_API.G_RET_STS_ERROR;
2046 x_msg_data := l_error_code || ':' || l_error_msg;
2047 IF (l_Debug_Level <= 5) THEN
2048 cln_debug_pub.Add(x_msg_data, 4);
2049 cln_debug_pub.Add('EXITING RAISE_UPDATE_COLLABORATION', 2);
2050 END IF;
2051
2052 END RAISE_UPDATE_COLLABORATION;
2053
2054
2055 -- Name
2056 -- RAISE_ADD_MESSAGE
2057 -- Purpose
2058 -- This procedure raises an event to add messages into collaboration history
2059 --
2060 -- Arguments
2061 --
2062 -- Notes
2063 -- No specific notes.
2064
2065 PROCEDURE RAISE_ADD_MESSAGE(
2066 x_return_status OUT NOCOPY VARCHAR2,
2067 x_msg_data OUT NOCOPY VARCHAR2,
2068 p_ictrl_no IN NUMBER,
2069 p_ref1 IN VARCHAR2,
2070 p_ref2 IN VARCHAR2,
2071 p_ref3 IN VARCHAR2,
2072 p_ref4 IN VARCHAR2,
2073 p_ref5 IN VARCHAR2,
2074 p_dtl_msg IN VARCHAR2)
2075 IS
2076 l_cln_ch_parameters wf_parameter_list_t;
2077 l_event_key NUMBER;
2078 l_error_code NUMBER;
2079 l_error_msg VARCHAR2(2000);
2080 l_debug_mode VARCHAR2(255);
2081 l_dtl_coll_id NUMBER;
2082 l_msg_data VARCHAR2(2000);
2083 BEGIN
2084 -- Sets the debug mode to be FILE
2085 --l_debug_mode :=cln_debug_pub.Set_Debug_Mode('FILE');
2086 IF (l_Debug_Level <= 2) THEN
2087 cln_debug_pub.Add('ENTERING RAISE_ADD_MESSAGE', 2);
2088 END IF;
2089
2090 -- Parameters received
2091 IF (l_Debug_Level <= 1) THEN
2092 cln_debug_pub.Add('With the following parameters',1);
2093 cln_debug_pub.Add('p_ictrl_no - ' || p_ictrl_no,1);
2094 cln_debug_pub.Add('p_ref1 - ' || p_ref1,1);
2095 cln_debug_pub.Add('p_ref2 - ' || p_ref2,1);
2096 cln_debug_pub.Add('p_ref3 - ' || p_ref3,1);
2097 cln_debug_pub.Add('p_ref4 - ' || p_ref4,1);
2098 cln_debug_pub.Add('p_ref5 - ' || p_ref5,1);
2099 cln_debug_pub.Add('p_dtl_msg - ' || p_dtl_msg,1);
2100 END IF;
2101
2102
2103 -- Initialize API return status to success
2104 x_return_status := FND_API.G_RET_STS_SUCCESS;
2105 FND_MESSAGE.SET_NAME('CLN', 'CLN_G_RET_MSG_SUCCESS');
2106 x_msg_data := FND_MESSAGE.GET;
2107
2108
2109 SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
2110
2111 l_cln_ch_parameters := wf_parameter_list_t();
2112 WF_EVENT.AddParameterToList('COLLABORATION_DETAIL_ID', l_dtl_coll_id, l_cln_ch_parameters);
2113 WF_EVENT.AddParameterToList('REFERENCE_ID1', p_ref1, l_cln_ch_parameters);
2114 WF_EVENT.AddParameterToList('REFERENCE_ID2', p_ref2, l_cln_ch_parameters);
2115 WF_EVENT.AddParameterToList('REFERENCE_ID3', p_ref3, l_cln_ch_parameters);
2116 WF_EVENT.AddParameterToList('REFERENCE_ID4', p_ref4, l_cln_ch_parameters);
2117 WF_EVENT.AddParameterToList('REFERENCE_ID5', p_ref5, l_cln_ch_parameters);
2118 WF_EVENT.AddParameterToList('DETAIL_MESSAGE', p_dtl_msg, l_cln_ch_parameters);
2119 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER', p_ictrl_no, l_cln_ch_parameters);
2120 WF_EVENT.AddParameterToList('DOCUMENT_TYPE', 'SALES_ORDER', l_cln_ch_parameters);
2121 WF_EVENT.AddParameterToList('DOCUMENT_DIRECTION', 'IN', l_cln_ch_parameters);
2122 -- Not required since defaulted to APPS
2123 -- WF_EVENT.AddParameterToList('COLLABORATION_POINT', 'APPS', l_cln_ch_parameters);
2124
2125 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.addmessage',
2126 l_event_key, NULL, l_cln_ch_parameters, NULL);
2127 IF (l_Debug_Level <= 1) THEN
2128 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.addmessage', 1);
2129 END IF;
2130
2131
2132 IF (l_Debug_Level <= 2) THEN
2133 cln_debug_pub.Add('EXITING RAISE_ADD_MESSAGE', 2);
2134 END IF;
2135
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138 l_error_code := SQLCODE;
2139 l_error_msg := SQLERRM;
2140 x_return_status := FND_API.G_RET_STS_ERROR;
2141 x_msg_data := l_error_code || ':' || l_error_msg;
2142 IF (l_Debug_Level <= 5) THEN
2143 cln_debug_pub.Add(x_msg_data, 4);
2144 cln_debug_pub.Add('EXITING RAISE_ADD_MESSAGE', 2);
2145 END IF;
2146
2147 END RAISE_ADD_MESSAGE;
2148
2149 END CLN_PO_SYNC_CAT_PKG;