[Home] [Help]
PACKAGE BODY: APPS.CLN_SYNCITEM_PKG
Source
1 PACKAGE BODY CLN_SYNCITEM_PKG AS
2 /* $Header: CLNSYITB.pls 120.4 2006/11/02 10:56:05 slattupa noship $ */
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4 g_party_id VARCHAR2(40);
5
6 -- Package
7 -- CLN_SYNCITEM_PKG
8 --
9 -- Purpose
10 -- Body of package CLN_SYNCITEM_PKG.
11 --
12 -- History
13 -- July-21-2003 Rahul Krishan Created
14
15
16 -- Name
17 -- GET_PARTY_ID
18 -- Purpose
19 -- This function returns the trading party id where the Payload needs to be sent
20 --
21 -- Arguments
22 --
23 -- Notes
24 -- No specific notes.
25 FUNCTION GET_PARTY_ID
26 RETURN NUMBER
27 IS
28 BEGIN
29 RETURN g_party_id;
30 END;
31
32
33 -- Name
34 -- GET_CUST_ACCT_ID
35 -- Purpose
36 -- This function returns the customer account id
37 --
38 -- Arguments
39 --
40 -- Notes
41 -- No specific notes.
42 FUNCTION GET_CUST_ACCT_ID
43 RETURN NUMBER
44 IS
45 l_cust_acct_id NUMBER;
46
47 BEGIN
48
49 IF (l_Debug_Level <= 2) THEN
50 cln_debug_pub.Add('----- Entering GET_CUST_ACCT_ID API ------- ',2);
51 END IF;
52
53 SELECT hca.cust_account_id cust_account_id
54 INTO l_cust_acct_id
55 FROM hz_cust_accounts hca
56 WHERE hca.party_id = CLN_SYNCITEM_PKG.GET_PARTY_ID();
57
58 IF (l_Debug_Level <= 2) THEN
59 cln_debug_pub.Add('Customer Account ID '||l_cust_acct_id,2);
60 cln_debug_pub.Add('----- Entering GET_CUST_ACCT_ID API ------- ',2);
61 END IF;
62
63 RETURN l_cust_acct_id;
64
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 IF (l_Debug_Level <= 1) THEN
68 cln_debug_pub.Add('Unable to find the customer details',1);
69 END IF;
70 END;
71
72
73 -- Name
74 -- SET_PARTY_ID
75 -- Purpose
76 -- This procedure is called from the 2A12 XGM and while the inprocessing mode
77 -- is carried out. This makes sure that the view cln_2a12_party_v gets value
78 -- This procedure sets the party id so as to maintain the
79 -- context from within the XGM.
80 --
81 -- Arguments
82 --
83 -- Notes
84 -- No specific notes.
85
86 PROCEDURE SET_PARTY_ID ( p_tp_party_id IN NUMBER)
87
88 IS
89 l_debug_level NUMBER;
90
91 BEGIN
92
93 IF (l_Debug_Level <= 2) THEN
94 cln_debug_pub.Add('----- Entering SET_PARTY_ID API ------- ',2);
95 END IF;
96
97 g_party_id := p_tp_party_id;
98
99 IF (l_Debug_Level <= 2) THEN
100 cln_debug_pub.Add('----- Party Id set as '||g_party_id,1);
101 cln_debug_pub.Add('----- Exting SET_PARTY_ID API ------- ',2);
102 END IF;
103
104 END;
105
106
107
108 -- Name
109 -- RAISE_SYNCITEM_EVENT
110 -- Purpose
111 -- This procedure is called from the 2A12 concurrent program.
112 -- This captures the user input and after processing raises an event for
113 -- for outbound processing.
114 -- Arguments
115 --
116 -- Notes
117 -- No specific notes.
118 PROCEDURE Raise_Syncitem_Event(
119 errbuf OUT NOCOPY VARCHAR2,
120 retcode OUT NOCOPY VARCHAR2,
121 p_tp_header_id IN NUMBER,
122 p_inventory_org_id IN NUMBER,
123 p_category_set_id IN NUMBER,
124 p_category_id IN NUMBER,
125 p_catalog_category_id IN NUMBER,
126 p_item_status IN VARCHAR2,
127 p_from_items IN VARCHAR2,
128 p_to_items IN VARCHAR2,
129 p_numitems_per_payload IN NUMBER)
130
131 IS
132
133 l_genwf_cln_parameter_list wf_parameter_list_t;
134 l_profile_value VARCHAR2(100);
135
136 l_date DATE;
137
138 l_error_code NUMBER;
139 l_event_key NUMBER;
140 l_tp_header_id NUMBER;
141 l_syncitem_seq NUMBER;
142 l_organization_id NUMBER;
143 l_view_party_id NUMBER;
144 --l_dummy_count NUMBER;
145
146
147 l_canonical_date VARCHAR2(100);
148 l_from_items VARCHAR2(100);
149 l_to_items VARCHAR2(100);
150
151 l_from_items_subset VARCHAR2(100);
152 l_to_items_subset VARCHAR2(100);
153
154 l_error_msg VARCHAR2(255);
155 l_msg_data VARCHAR2(255);
156 l_doc_number VARCHAR2(255);
157 l_xmlg_transaction_type VARCHAR2(255);
158 l_xmlg_transaction_subtype VARCHAR2(255);
159 l_xmlg_document_id VARCHAR2(255);
160 l_tr_partner_type VARCHAR2(255);
161 l_tr_partner_id VARCHAR2(255);
162 l_tr_partner_site VARCHAR2(255);
163 l_party_name VARCHAR2(255);
164 l_doc_dir VARCHAR2(255);
165 l_dummy_check VARCHAR2(2);
166 l_counter BINARY_INTEGER;
167 l_items_exist BOOLEAN;
168
169
170 -- cursor to hold the list of items to send
171 CURSOR c_ItemsToSend ( p_inventory_org_id NUMBER,
172 p_category_set_id NUMBER,
173 p_category_id NUMBER,
174 p_catalog_category_id NUMBER,
175 p_item_status VARCHAR2,
176 p_from_items VARCHAR2,
177 p_to_items VARCHAR2)
178 IS
179 SELECT concatenated_segments
180 FROM CLN_ITEMMST_ITEMHEADER_V
181 WHERE ORGANIZATION_ID= p_inventory_org_id
182 AND ( p_category_set_id IS NULL OR
183 p_category_set_id IN
184 ( SELECT mcsvc.category_set_id
185 FROM mtl_item_categories mic, mtl_category_set_valid_cats mcsvc
186 WHERE mcsvc.category_set_id = mic.category_set_id AND
187 mic.inventory_item_id = CLN_ITEMMST_ITEMHEADER_V.INVENTORY_ITEM_ID AND
188 mic.organization_id = p_inventory_org_id
189 )
190 )
191 AND ( p_category_id IS NULL OR
192 p_category_id IN
193 ( SELECT mcsvc.category_id
194 FROM mtl_item_categories mic, mtl_category_set_valid_cats mcsvc
195 WHERE mcsvc.category_id = mic.category_id AND
196 mic.inventory_item_id = CLN_ITEMMST_ITEMHEADER_V.INVENTORY_ITEM_ID AND
197 mic.organization_id = p_inventory_org_id
198 )
199 )
200 AND ( p_catalog_category_id IS NULL OR
201 p_catalog_category_id IN
202 ( SELECT micgk.item_catalog_group_id
203 FROM mtl_item_catalog_groups_kfv micgk
204 WHERE micgk.item_catalog_group_id = CLN_ITEMMST_ITEMHEADER_V.item_catalog_group_id
205 )
206 )
207 AND ( p_item_status IS NULL OR
208 INVENTORY_ITEM_STATUS_CODE = p_item_status)
209 AND (
210 CONCATENATED_SEGMENTS >= nvl(p_from_items,CONCATENATED_SEGMENTS)
211 AND
212 CONCATENATED_SEGMENTS <= nvl(p_to_items,CONCATENATED_SEGMENTS)
213 )
214 ORDER BY concatenated_segments;
215
216 BEGIN
217
218 IF (l_Debug_Level <= 2) THEN
219 cln_debug_pub.Add('----- Entering Raise_Syncitem_Event API ------- ',2);
220 END IF;
221
222
223 -- Initialize API return status to success
224 l_msg_data := 'Successfully called the CLN API to kick off sync items event';
225
226
227 -- Parameters received from the concurrrent program
228 IF (l_Debug_Level <= 1) THEN
229 cln_debug_pub.Add('== PARAMETERS RECEIVED FROM CONCURRENT PROGRAM== ',1);
230 cln_debug_pub.Add('Trading Partner Header ID - '||p_tp_header_id,1);
231 cln_debug_pub.Add('Inventory Org ID - '||p_inventory_org_id,1);
232 cln_debug_pub.Add('Category Set ID - '||p_category_set_id,1);
233 cln_debug_pub.Add('Category ID - '||p_category_id,1);
234 cln_debug_pub.Add('Catalog Category ID - '||p_catalog_category_id,1);
235 cln_debug_pub.Add('Item Status - '||p_item_status,1);
236 cln_debug_pub.Add('From Items [Concatenated Segment] - '||p_from_items,1);
237 cln_debug_pub.Add('To Items [Concatenated Segment] - '||p_to_items,1);
238 cln_debug_pub.Add('Number Of Items /Message - '||p_numitems_per_payload,1);
239 cln_debug_pub.Add('=================================================',1);
240 END IF;
241
242
243 -- Getting Trading Partner Details
244 IF (l_Debug_Level <= 1) THEN
245 cln_debug_pub.Add('Getting Trading Partner Details Using Tp_Header_Id',1);
246 END IF;
247
248 BEGIN
249
250 select eth.party_type, eth.party_id, eth.party_site_id
251 INTO l_tr_partner_type, l_tr_partner_id, l_tr_partner_site
252 from ecx_tp_headers eth
253 where eth.tp_header_id = p_tp_header_id;
254
255 -- this is reqd for setting the view cln_2a12_party_v
256 g_party_id := l_tr_partner_id;
257
258
259
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_FOUND');
263 l_msg_data := FND_MESSAGE.GET;
264 IF (l_Debug_Level <= 1) THEN
265 cln_debug_pub.Add('Unable to find the set up details for the trading partner',1);
266 END IF;
267
268 RAISE FND_API.G_EXC_ERROR;
269 WHEN TOO_MANY_ROWS THEN
270 FND_MESSAGE.SET_NAME('CLN','CLN_CH_TP_DETAILS_NOT_UNIQUE');
271 l_msg_data := FND_MESSAGE.GET;
272 IF (l_Debug_Level <= 1) THEN
273 cln_debug_pub.Add('More then one row found for the same trading partner set up',1);
274 END IF;
275
276 RAISE FND_API.G_EXC_ERROR;
277 END;
278
279 IF (l_Debug_Level <= 1) THEN
280 cln_debug_pub.Add('======== Trading Partner Details Found =========',1);
281 cln_debug_pub.Add('Trading Partner Type - '||l_tr_partner_type,1);
282 cln_debug_pub.Add('Trading Partner ID - '||l_tr_partner_id,1);
283 cln_debug_pub.Add('Trading Partner Site - '||l_tr_partner_site,1);
284 cln_debug_pub.Add('Trading Partner Name - '||l_party_name,1);
285 END IF;
286
287
288 -- Defaulting based on some business logic
289
290 l_from_items := p_from_items;
291 l_to_items := p_to_items;
292
293
294 BEGIN
295 IF (l_Debug_Level <= 1) THEN
296 cln_debug_pub.Add('Checking for the users choice ....',1);
297 END IF;
298
299
300 l_profile_value := fnd_profile.VALUE ('CLN_ITEM_SEND_CUST_XREF_ONLY');
301
302 IF (l_Debug_Level <= 1) THEN
303 cln_debug_pub.Add('profile value for - CLN_ITEM_SEND_CUST_XREF_ONLY -'||l_profile_value,1);
304 END IF;
305
306 -- Modified the query below due to performance hit. Bug #4946778
307
308 SELECT 'x' into l_dummy_check FROM dual
309 WHERE EXISTS (SELECT 'X' FROM mtl_system_items_b_kfv msib, --mtl_system_items_vl msib,
310 mtl_customer_item_xrefs mcix, -- mtl_item_revisions mir,
311 po_hazard_classes_tl phct,
312 MTL_CUSTOMER_ITEMS MCI,
313 HZ_PARTIES HZP,
314 MFG_LOOKUPS MFL ,
315 HZ_CUST_ACCOUNTS HZC,
316 AR_LOOKUPS ARL
317 WHERE mcix.customer_item_id = mci.customer_item_id AND
318 mcix.inventory_item_id(+)= msib.inventory_item_id AND
319 mcix.master_organization_id =msib.organization_id AND
320 mci.customer_id(+) = cln_syncitem_pkg.get_cust_acct_id () AND
321 -- msib.inventory_item_id = mir.inventory_item_id(+) AND
322 -- msib.organization_id = mir.organization_id(+) AND
323 MCI.CUSTOMER_CATEGORY_CODE = ARL.LOOKUP_CODE(+) AND
324 msib.service_item_flag = 'N' AND
325 msib.inventory_item_flag = 'Y' AND
326 msib.customer_order_enabled_flag = 'Y' AND
327 MCI.INACTIVE_FLAG = 'N' AND
328 HZC.PARTY_ID = HZP.PARTY_ID AND HZC.STATUS = 'A' AND
329 msib.hazard_class_id = phct.hazard_class_id(+) AND
330 MCI.CUSTOMER_ID = HZC.CUST_ACCOUNT_ID AND
331 MCI.ITEM_DEFINITION_LEVEL = MFL.LOOKUP_CODE AND
332 MFL.LOOKUP_TYPE = 'INV_ITEM_DEFINITION_LEVEL' AND
333 --mir.revision = (SELECT MAX (revision) FROM mtl_item_revisions WHERE inventory_item_id = mir.inventory_item_id AND
334 --organization_id = mir.organization_id) AND
335 phct.LANGUAGE(+) = USERENV ('lang') AND
336 ARL.ENABLED_FLAG(+) = 'Y' AND
337 ARL.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY' AND
338 TRUNC(SYSDATE) BETWEEN NVL(TRUNC((ARL.START_DATE_ACTIVE(+))),SYSDATE) AND
339 NVL(TRUNC((ARL.END_DATE_ACTIVE(+))), SYSDATE) AND
340 ( ( NVL (fnd_profile.VALUE ('CLN_ITEM_SEND_CUST_XREF_ONLY'),'N') = 'Y' AND mci.customer_item_number IS NOT NULL ) OR
341 NVL (fnd_profile.VALUE ('CLN_ITEM_SEND_CUST_XREF_ONLY'), 'N') ='N' ) AND
342 ( p_category_set_id IS NULL OR p_category_set_id IN
343 ( SELECT mcsvc.category_set_id
344 FROM mtl_item_categories mic,
345 mtl_category_set_valid_cats mcsvc
346 WHERE mcsvc.category_set_id = mic.category_set_id AND
347 mic.inventory_item_id = msib.INVENTORY_ITEM_ID AND
348 mic.organization_id = p_inventory_org_id )
349 ) AND
350 ( p_category_id IS NULL OR p_category_id IN
351 ( SELECT mic.category_id
352 FROM mtl_item_categories mic
353 WHERE mic.category_id = p_category_id AND
354 mic.inventory_item_id = msib.INVENTORY_ITEM_ID AND
355 mic.organization_id = p_inventory_org_id )
356 )AND
357 ( p_catalog_category_id IS NULL OR p_catalog_category_id IN
358 ( SELECT micgk.item_catalog_group_id
359 FROM mtl_item_catalog_groups_kfv micgk
360 WHERE micgk.item_catalog_group_id = msib.item_catalog_group_id)
361 )AND
362 ( p_item_status IS NULL OR msib.INVENTORY_ITEM_STATUS_CODE = p_item_status)AND
363 ( msib.CONCATENATED_SEGMENTS >= NVL(l_from_items,msib.CONCATENATED_SEGMENTS) AND
364 msib.CONCATENATED_SEGMENTS <= NVL(l_to_items,msib.CONCATENATED_SEGMENTS)));
365
366
367 EXCEPTION
368 WHEN NO_DATA_FOUND THEN
369 FND_MESSAGE.SET_NAME('CLN','CLN_CH_NO_ROW_SELECTED');
370 l_msg_data := FND_MESSAGE.GET;
371 IF (l_Debug_Level <= 1) THEN
372 cln_debug_pub.Add('No records found for the user input',1);
373 END IF;
374
375 RAISE FND_API.G_EXC_ERROR;
376 END;
377
378 IF (l_Debug_Level <= 1) THEN
379 cln_debug_pub.Add('User input seems valid.....',1);
380 END IF;
381
382 -- Get the document creation date as canonical date
383 SELECT sysdate into l_date from dual;
384 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
385
386 IF (l_Debug_Level <= 1) THEN
387 cln_debug_pub.Add('Canonical Date set as - '||l_canonical_date,1);
388 END IF;
389
390
391 -- Generic attribute list for generic outbound workflow
392 l_genwf_cln_parameter_list := wf_parameter_list_t();
393
394
395 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
396 p_value => l_tr_partner_id,
397 p_parameterlist => l_genwf_cln_parameter_list);
398
399 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
400 p_value => l_tr_partner_site,
401 p_parameterlist => l_genwf_cln_parameter_list);
402
403 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
404 p_value => l_tr_partner_type,
405 p_parameterlist => l_genwf_cln_parameter_list);
406
407 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
408 p_value => 'CLN',
409 p_parameterlist => l_genwf_cln_parameter_list);
410
411 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
412 p_value => 'SYNCITEMO',
413 p_parameterlist => l_genwf_cln_parameter_list);
414
415 wf_event.AddParameterToList(p_name => 'ECX_DELIVERY_CHECK_REQUIRED',
416 p_value => 'YES',
417 p_parameterlist => l_genwf_cln_parameter_list);
418
419 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER1',
420 p_value => p_inventory_org_id,
421 p_parameterlist => l_genwf_cln_parameter_list);
422
423 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER2',
424 p_value => p_category_set_id,
425 p_parameterlist => l_genwf_cln_parameter_list);
426
427 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER3',
428 p_value => p_category_id,
429 p_parameterlist => l_genwf_cln_parameter_list);
430
431 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER4',
432 p_value => p_catalog_category_id,
433 p_parameterlist => l_genwf_cln_parameter_list);
434
435 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER5',
436 p_value => p_item_status,
437 p_parameterlist => l_genwf_cln_parameter_list);
438
439 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER6',
440 p_value => l_from_items,
441 p_parameterlist => l_genwf_cln_parameter_list);
442
443 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER7',
444 p_value => l_to_items,
445 p_parameterlist => l_genwf_cln_parameter_list);
446
447 wf_event.AddParameterToList(p_name => 'ORG_ID',
448 p_value => p_inventory_org_id,
449 p_parameterlist => l_genwf_cln_parameter_list);
450
451 wf_event.AddParameterToList(p_name => 'DOCUMENT_CAREATION_DATE',
452 p_value => l_canonical_date,
453 p_parameterlist => l_genwf_cln_parameter_list);
454
455 wf_event.AddParameterToList(p_name => 'VALIDATION_REQUIRED_YN',
456 p_value => 'N',
457 p_parameterlist => l_genwf_cln_parameter_list);
458
459 wf_event.AddParameterToList(p_name => 'CH_MESSAGE_BEFORE_GENERATE_XML',
460 p_value => 'CLN_CH_COLLABORATION_CREATED',
461 p_parameterlist => l_genwf_cln_parameter_list);
462
463 wf_event.AddParameterToList(p_name => 'CH_MESSAGE_AFTER_XML_SENT',
464 p_value => 'CLN_SYNC_ITEM_XML_SENT',
465 p_parameterlist => l_genwf_cln_parameter_list);
466
467 wf_event.AddParameterToList(p_name => 'COLLABORATION_STATUS_SET',
468 p_value => 'Y',
469 p_parameterlist => l_genwf_cln_parameter_list);
470
471 wf_event.AddParameterToList(p_name => 'CH_MESSAGE_NO_TP_SETUP',
472 p_value => 'CLN_CH_TP_SETUP_NOTFOUND',
473 p_parameterlist => l_genwf_cln_parameter_list);
474
475
476
477 IF(p_numitems_per_payload IS NULL ) THEN
478 IF (l_Debug_Level <= 1) THEN
479 cln_debug_pub.Add('Workflow event- oracle.apps.cln.common.xml.out', 1);
480 END IF;
481
482
483 -- create unique key
484 SELECT CLN_SYNCITEM_S.nextval into l_syncitem_seq from dual;
485 l_event_key := to_char(p_tp_header_id) || '.' || to_char(l_syncitem_seq);
486
487 IF (l_Debug_Level <= 1) THEN
488 cln_debug_pub.Add('Event Key set as - '||l_event_key,1);
489 END IF;
490
491 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
492 p_value => l_event_key,
493 p_parameterlist => l_genwf_cln_parameter_list);
494
495 wf_event.AddParameterToList(p_name => 'XML_EVENT_KEY',
496 p_value => l_event_key,
497 p_parameterlist => l_genwf_cln_parameter_list);
498
499 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
500 p_value => l_event_key,
501 p_parameterlist => l_genwf_cln_parameter_list);
502
503 IF (l_Debug_Level <= 1) THEN
504 cln_debug_pub.Add('raising event as - oracle.apps.cln.common.xml.out',1);
505 END IF;
506
507 -- raise event for send show shipment document
508 wf_event.raise(p_event_name => 'oracle.apps.cln.common.xml.out',
509 p_event_key => l_event_key,
510 p_parameters => l_genwf_cln_parameter_list);
511
512
513
514 ELSIF (p_numitems_per_payload >= 1) THEN
515
516 -- open cursor for all the documents that will be sent
517 OPEN c_ItemsToSend (p_inventory_org_id,
518 p_category_set_id,
519 p_category_id,
520 p_catalog_category_id,
521 p_item_status,
522 l_from_items,
523 l_to_items );
524
525 LOOP -- begin of xml documents generation
526 l_counter := 1; -- reset counter
527
528 IF (l_Debug_Level <= 1) THEN
529 cln_debug_pub.Add('Counter set as - '||l_counter,1);
530 END IF;
531
532 -- extract first item
533 FETCH c_ItemsToSend INTO l_from_items;
534 EXIT WHEN c_ItemsToSend%NOTFOUND;
535
536 IF (l_Debug_Level <= 1) THEN
537 cln_debug_pub.Add('start item of the message found as - '||l_from_items,1);
538 END IF;
539
540 l_to_items := l_from_items; -- jst incase this is the last item
541
542 WHILE l_counter < p_numitems_per_payload LOOP
543 FETCH c_ItemsToSend INTO l_to_items; -- extract last item number
544 EXIT WHEN c_ItemsToSend%NOTFOUND; -- if we reached the end, then just send out what's left
545
546 IF (l_Debug_Level <= 1) THEN
547 cln_debug_pub.Add('intermediatory end item of the message found as - '||l_to_items,1);
548 END IF;
549
550 l_counter := l_counter + 1;
551
552 IF (l_Debug_Level <= 1) THEN
553 cln_debug_pub.Add('Counter value raised to - '||l_counter,1);
554 END IF;
555 END LOOP;
556
557 IF (l_Debug_Level <= 1) THEN
558 cln_debug_pub.Add('end item of the message found as - '||l_to_items,1);
559 END IF;
560
561 -- create unique key
562 SELECT CLN_SYNCITEM_S.nextval INTO l_syncitem_seq FROM dual;
563 l_event_key := to_char(p_tp_header_id) || '.' || to_char(l_syncitem_seq);
564
565
566 IF (l_Debug_Level <= 1) THEN
567 cln_debug_pub.Add('Event Key set as - '||l_event_key,1);
568 END IF;
569
570 -- setting the generic workflow parameters
571 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER6',
572 p_value => l_from_items,
573 p_parameterlist => l_genwf_cln_parameter_list);
574
575 wf_event.AddParameterToList(p_name => 'MAP_PARAMETER7',
576 p_value => l_to_items,
577 p_parameterlist => l_genwf_cln_parameter_list);
578
579 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
580 p_value => l_event_key,
581 p_parameterlist => l_genwf_cln_parameter_list);
582
583 wf_event.AddParameterToList(p_name => 'XML_EVENT_KEY',
584 p_value => l_event_key,
585 p_parameterlist => l_genwf_cln_parameter_list);
586
587 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
588 p_value => l_event_key,
589 p_parameterlist => l_genwf_cln_parameter_list);
590
591 IF (l_Debug_Level <= 1) THEN
592 cln_debug_pub.Add('raising event as - oracle.apps.cln.common.xml.out',1);
593 END IF;
594
595 -- raise event for send show shipment document
596 wf_event.raise(p_event_name => 'oracle.apps.cln.common.xml.out',
597 p_event_key => l_event_key,
598 p_parameters => l_genwf_cln_parameter_list);
599
600 IF (l_Debug_Level <= 1) THEN
601 cln_debug_pub.Add('....Event Raised...',1);
602 END IF;
603 END LOOP;
604
605 CLOSE c_ItemsToSend;
606 END IF;
607
608
609 retcode := 0;
610 errbuf := 'Successful';
611
612 -- check the error message
613 IF (l_Debug_Level <= 1) THEN
614 cln_debug_pub.Add(l_msg_data,1);
615 END IF;
616
617 IF (l_Debug_Level <= 2) THEN
618 cln_debug_pub.Add('------- Exiting Raise_Syncitem_Event API --------- ',2);
619 END IF;
620
621 -- Exception Handling
622 EXCEPTION
623
624 WHEN FND_API.G_EXC_ERROR THEN
625 retcode := 2 ;
626 errbuf := l_msg_data;
627 IF (l_Debug_Level <= 5) THEN
628 cln_debug_pub.Add(l_msg_data,4);
629 cln_debug_pub.Add('------- Exiting Raise_Syncitem_Event API with error --------- ',2);
630 END IF;
631
632
633 WHEN OTHERS THEN
634 l_error_code :=SQLCODE;
635 l_error_msg :=SQLERRM;
636 retcode :=2 ;
637 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
638 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
639 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
640 l_msg_data :='Unexpected Error -'||l_error_code||' : '||l_error_msg;
641 errbuf := l_msg_data;
642 IF (l_Debug_Level <= 5) THEN
643 cln_debug_pub.Add(l_msg_data,6);
644 cln_debug_pub.Add('------- Exiting Raise_Syncitem_Event API with an unexpected error --------- ',2);
645 END IF;
646
647
648 END Raise_Syncitem_Event;
649
650
651 -- Name
652 -- SEND_SYNCITEM_DELETE
653 -- Purpose
654 -- This procedure is called from the 2A12 Workflow.
655 -- This procedure checks for the Trading Partner setup. Also, sets the WF Item
656 -- attributes and raises the Sync Item event.
657 --
658 -- Arguments
659 --
660 -- Notes
661 -- No specific notes.
662
663 PROCEDURE Send_Syncitem_Delete(itemtype in varchar2,
664 itemkey in varchar2,
665 actid in number,
666 funcmode in varchar2,
667 resultout in out NOCOPY varchar2) IS
668 l_debug_level NUMBER;
669
670 x_progress VARCHAR2(100);
671 transaction_type varchar2(240);
672 transaction_subtype varchar2(240);
673 document_direction varchar2(240);
674 message_text varchar2(240);
675 party_id number;
676 party_site_id number;
677 party_type varchar2(30);
678 return_code pls_integer;
679 errmsg varchar2(2000);
680 result boolean;
681 l_error_code NUMBER;
682 l_error_msg VARCHAR2(1000);
683
684 -- parameters for create collaboration date
685 l_date DATE;
686 l_canonical_date VARCHAR2(100);
687
688 -- parameters for raising event
689 l_send_shsp_event VARCHAR2(100);
690 l_create_cln_event VARCHAR2(100);
691 l_event_key VARCHAR2(100);
692 l_syncitem_seq NUMBER;
693 l_send_syit_parameter_list wf_parameter_list_t;
694 l_create_cln_parameter_list wf_parameter_list_t;
695 l_organization_id NUMBER;
696 l_tp_header_id NUMBER;
697 p_inventory_item_id NUMBER;
698 p_org_id NUMBER;
699
700 -- cursor to hold the list of trading partners to send to
701 CURSOR c_TradingPartners IS
702 select eth.tp_header_id
703 from ecx_tp_headers eth, ecx_tp_details etd, ecx_ext_processes eep,
704 ecx_transactions et, hz_parties hp, hz_party_sites hps, hz_locations hl
705 where eth.tp_header_id = etd.tp_header_id
706 and etd.EXT_PROCESS_ID = eep.EXT_PROCESS_ID and eth.party_id = hp.party_id
707 and eth.party_site_id = hps.party_site_id and hps.location_id = hl.location_id
708 and eep.transaction_id = et.transaction_id and et.transaction_type = 'CLN'
709 and et.transaction_subtype = 'SYNCITEMDELO' and eep.direction = 'OUT';
710
711 BEGIN
712 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
713
714 x_progress := '000';
715 transaction_type := 'CLN';
716 transaction_subtype := 'SYNCITEMDELO';
717 document_direction := 'OUT';
718 message_text := 'CLN_SYIT_MESSAGE_SENT';
719 party_type := 'C';
720 result := FALSE;
721 l_send_shsp_event := 'oracle.apps.cln.event.syncitem';
722 l_create_cln_event := 'oracle.apps.cln.ch.collaboration.create';
723 l_send_syit_parameter_list := wf_parameter_list_t();
724 l_create_cln_parameter_list := wf_parameter_list_t();
725
726 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Entered Procedure';
727 if (l_debug_level <= 1) then
728 cln_debug_pub.Add('Failure point ' || x_progress, 1);
729 end if;
730
731 -- get organization ID
732 select FND_PROFILE.VALUE('ORG_ID')
733 into l_organization_id
734 from dual;
735
736 -- Retrieve Activity Attributes
737 p_inventory_item_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'INVENTORY_ITEM_ID');
738 p_org_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'ORGANIZATION_ID');
739 l_organization_id := p_org_id;
740
741 OPEN c_TradingPartners; -- open cursor
742
743 LOOP
744 -- get next trading partner
745 FETCH c_TradingPartners INTO l_tp_header_id;
746
747 -- if no trading partner, then finished.
748 EXIT WHEN c_TradingPartners%NOTFOUND;
749
750 -- get parameters for that particular trading partner
751 select eth.party_id, eth.party_site_id
752 into party_id, party_site_id
753 from ecx_tp_headers eth
754 where eth.tp_header_id = l_tp_header_id;
755
756 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Initialized procedure parameters';
757 if (l_debug_level <= 1) then
758 cln_debug_pub.Add('Failure point ' || x_progress, 1);
759 end if;
760
761 -- XML Setup Check
762 ecx_document.isDeliveryRequired(
763 transaction_type => transaction_type,
764 transaction_subtype => transaction_subtype,
765 party_id => party_id,
766 party_site_id => party_site_id,
767 resultout => result,
768 retcode => return_code,
769 errmsg => errmsg);
770
771 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : XML Setup Check Done';
772 if (l_debug_level <= 1) then
773 cln_debug_pub.Add('Failure point ' || x_progress, 1);
774 end if;
775
776 -- Decision on action depending on XML Setup Check
777 if NOT(result) then -- XML not setup
778
779 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : XML Setup does not exist';
780 if (l_debug_level <= 1) then
781 cln_debug_pub.Add('Failure point ' || x_progress, 1);
782 end if;
783 else
784 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : XML Setup exists';
785 if (l_debug_level <= 1) then
786 cln_debug_pub.Add('Failure point ' || x_progress, 1);
787 end if;
788
789 -- create unique key
790 SELECT CLN_SYNCITEM_S.nextval into l_syncitem_seq from dual;
791 l_event_key := to_char(l_tp_header_id) || '.' || to_char(l_syncitem_seq);
792
793 SELECT sysdate into l_date from dual;
794 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
795
796 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Unique key created';
797 if (l_debug_level <= 1) then
798 cln_debug_pub.Add('Failure point ' || x_progress, 1);
799 end if;
800
801 -- add parameters to list for create collaboration event
802 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
803 p_value => transaction_type,
804 p_parameterlist => l_create_cln_parameter_list);
805 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
806 p_value => transaction_subtype,
807 p_parameterlist => l_create_cln_parameter_list);
808 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
809 p_value => document_direction,
810 p_parameterlist => l_create_cln_parameter_list);
811 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
812 p_value => l_event_key,
813 p_parameterlist => l_create_cln_parameter_list);
814 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
815 p_value => party_id,
816 p_parameterlist => l_create_cln_parameter_list);
817 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
818 p_value => party_site_id,
819 p_parameterlist => l_create_cln_parameter_list);
820 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
821 p_value => party_type,
822 p_parameterlist => l_create_cln_parameter_list);
823 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
824 p_value => l_event_key,
825 p_parameterlist => l_create_cln_parameter_list);
826 wf_event.AddParameterToList(p_name => 'ORG_ID',
827 p_value => l_organization_id,
828 p_parameterlist => l_create_cln_parameter_list);
829 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
830 p_value => l_canonical_date,
831 p_parameterlist => l_create_cln_parameter_list);
832
833 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Create Event Parameters Setup';
834 if (l_debug_level <= 1) then
835 cln_debug_pub.Add('Failure point ' || x_progress, 1);
836 end if;
837
838 -- raise create collaboration event
839 wf_event.raise(p_event_name => l_create_cln_event,
840 p_event_key => l_event_key,
841 p_parameters => l_create_cln_parameter_list);
842
843 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Create Event Raised';
844 if (l_debug_level <= 1) then
845 cln_debug_pub.Add('Failure point ' || x_progress, 1);
846 end if;
847
848 -- add parameters to list for send sync item document
849 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
850 p_value => transaction_type,
851 p_parameterlist => l_send_syit_parameter_list);
852 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
853 p_value => transaction_subtype,
854 p_parameterlist => l_send_syit_parameter_list);
855 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
856 p_value => transaction_type,
857 p_parameterlist => l_send_syit_parameter_list);
858 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
859 p_value => transaction_subtype,
860 p_parameterlist => l_send_syit_parameter_list);
861 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
862 p_value => document_direction,
863 p_parameterlist => l_send_syit_parameter_list);
864 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
865 p_value => party_id,
866 p_parameterlist => l_send_syit_parameter_list);
867 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
868 p_value => party_site_id,
869 p_parameterlist => l_send_syit_parameter_list);
870 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
871 p_value => party_type,
872 p_parameterlist => l_send_syit_parameter_list);
873 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
874 p_value => party_id,
875 p_parameterlist => l_send_syit_parameter_list);
876 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
877 p_value => party_site_id,
878 p_parameterlist => l_send_syit_parameter_list);
879 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
880 p_value => party_type,
881 p_parameterlist => l_send_syit_parameter_list);
882 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
883 p_value => l_event_key,
884 p_parameterlist => l_send_syit_parameter_list);
885 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
886 p_value => l_event_key,
887 p_parameterlist => l_send_syit_parameter_list);
888 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
889 p_value => l_event_key,
890 p_parameterlist => l_send_syit_parameter_list);
891 wf_event.AddParameterToList(p_name => 'MESSAGE_TEXT',
892 p_value => message_text,
893 p_parameterlist => l_send_syit_parameter_list);
894 wf_event.AddParameterToList(p_name => 'ORG_ID',
895 p_value => l_organization_id,
896 p_parameterlist => l_send_syit_parameter_list);
897 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER1',
898 p_value => NULL,
899 p_parameterlist => l_send_syit_parameter_list);
900 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER2',
901 p_value => NULL,
902 p_parameterlist => l_send_syit_parameter_list);
903 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER3',
904 p_value => NULL,
905 p_parameterlist => l_send_syit_parameter_list);
906 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER4',
907 p_value => NULL,
908 p_parameterlist => l_send_syit_parameter_list);
909 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER5',
910 p_value => NULL,
911 p_parameterlist => l_send_syit_parameter_list);
912 wf_event.AddParameterToList(p_name => 'INVENTORY_ITEM_ID', -- may possibly need this
913 p_value => p_inventory_item_id,
914 p_parameterlist => l_send_syit_parameter_list);
915 wf_event.AddParameterToList(p_name => 'ORGANIZATION_ID',
916 p_value => p_org_id,
917 p_parameterlist => l_send_syit_parameter_list);
918 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
919 p_value => l_canonical_date,
920 p_parameterlist => l_send_syit_parameter_list);
921
922 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Initialize Send Document Parameters';
923 if (l_debug_level <= 1) then
924 cln_debug_pub.Add('Failure point ' || x_progress, 1);
925 end if;
926
927 -- raise event for send show shipment document
928 wf_event.raise(p_event_name => l_send_shsp_event,
929 p_event_key => l_event_key,
930 p_parameters => l_send_syit_parameter_list);
931
932 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Send Document Event Raised';
933 if (l_debug_level <= 1) then
934 cln_debug_pub.Add('Failure point ' || x_progress, 1);
935 end if;
936 end if;
937
938 END LOOP;
939
940 -- close cursor when done
941 CLOSE c_TradingPartners;
942
943 -- Reached Here. Successful execution.
944 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : Exiting Procedure';
945 if (l_debug_level <= 1) then
946 cln_debug_pub.Add('Failure point ' || x_progress, 1);
947 end if;
948
949 resultout := 'COMPLETE:T';
950 EXCEPTION
951 WHEN OTHERS THEN
952 l_error_code := SQLCODE;
953 l_error_msg := SQLERRM;
954 if (l_debug_level <= 1) then
955 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
956 end if;
957
958 x_progress := 'CLN_SYNCITEM_PKG.Send_Syncitem_Delete : ERROR';
959 if (l_debug_level <= 1) then
960 cln_debug_pub.Add('Failure point ' || x_progress, 1);
961 end if;
962 END Send_Syncitem_Delete;
963
964
965 -- Name
966 -- ARCHIVE_DELETED_ITEMS
967 -- Purpose
968 -- This procedure is called from the 2A12 Workflow.
969 -- This procedure archives the deleted items into 'cln_itemmst_deleted_items' table.
970 --
971 -- Arguments
972 --
973 -- Notes
974 -- No specific notes.
975
976 PROCEDURE Archive_Deleted_Items(itemtype in varchar2,
977 itemkey in varchar2,
978 actid in number,
979 funcmode in varchar2,
980 resultout in out NOCOPY varchar2) IS
981 l_debug_level NUMBER;
982
983 x_progress VARCHAR2(100);
984 l_error_code NUMBER;
985 l_error_msg VARCHAR2(1000);
986 p_inventory_item_id NUMBER:= Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'INVENTORY_ITEM_ID');
987 p_org_id NUMBER:= Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'ORGANIZATION_ID');
988 p_concatenated_segments VARCHAR2(50);
989 p_item_type VARCHAR2(30);
990 p_primary_uom_code VARCHAR2(3);
991 p_customer_item_number VARCHAR2(50);
992
993 --cursor to hold all the associated records of the deleted item.
994 CURSOR c_DeletedItems
995 IS
996 SELECT distinct msib.concatenated_segments, msib.item_type, msib.primary_uom_code, mci.customer_item_number
997 FROM mtl_system_items_b_kfv msib, mtl_system_items_tl msit, mtl_customer_item_xrefs mcix, mtl_customer_items mci,
998 mtl_item_revisions mir, mtl_item_catalog_groups_kfv micgk, po_hazard_classes_tl phct
999 WHERE msib.inventory_item_id = msit.inventory_item_id(+) and msib.inventory_item_id = mcix.inventory_item_id(+)
1000 and mcix.customer_item_id = mci.customer_item_id(+) and msib.inventory_item_id = mir.inventory_item_id(+)
1001 and msit.organization_id = msib.organization_id and mir.organization_id = msib.organization_id
1002 and msib.service_item_flag = 'N' and msib.inventory_item_flag = 'Y'
1003 and msib.item_catalog_group_id = micgk.item_catalog_group_id(+) and msib.hazard_class_id = phct.hazard_class_id(+)
1004 and msib.inventory_item_id = p_inventory_item_id and msib.organization_id = p_org_id;
1005
1006 BEGIN
1007
1008
1009 resultout := 'COMPLETE:T';
1010 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
1011 x_progress := 'CLN_SYNCITEM_PKG.Archive_Deleted_Items : Entered Procedure';
1012 if (l_debug_level <= 1) then
1013 cln_debug_pub.Add('Failure point ' || x_progress, 1);
1014 end if;
1015
1016
1017
1018 FOR del_rec in c_DeletedItems LOOP
1019
1020 --table to hold the records of the deleted item, index being (inventory_item_id, customer_item_number)
1021 INSERT INTO cln_itemmst_deleted_items
1022 (inventory_item_id, organization_id, concatenated_segments, item_type, primary_uom_code, customer_item_number)
1023 VALUES
1024 (p_inventory_item_id, p_org_id, del_rec.concatenated_segments, del_rec.item_type, del_rec.primary_uom_code, del_rec.customer_item_number);
1025
1026 END LOOP;
1027
1028
1029 -- Reached Here. Successful execution.
1030 x_progress := 'CLN_SYNCITEM_PKG.Archive_Deleted_Items : Exiting Procedure';
1031 if (l_debug_level <= 1) then
1032 cln_debug_pub.Add('Failure point ' || x_progress, 1);
1033 end if;
1034
1035 EXCEPTION
1036
1037 WHEN OTHERS THEN
1038 l_error_code := SQLCODE;
1039 l_error_msg := SQLERRM;
1040 if (l_debug_level <= 1) then
1041 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
1042 end if;
1043
1044 x_progress := 'CLN_SYNCITEM_PKG.Archive_Deleted_Items : ERROR';
1045 if (l_debug_level <= 1) then
1046 cln_debug_pub.Add('Failure point ' || x_progress, 1);
1047 end if;
1048 resultout := 'COMPLETE:F';
1049 END Archive_Deleted_Items;
1050
1051
1052 -- Name
1053 -- DELETE_ARCHIVED_ITEMS
1054 -- Purpose
1055 -- This procedure is called from the 2A12 Workflow.
1056 -- This procedure deletes the archived items from the 'cln_itemmst_deleted_items'.
1057 --
1058 -- Arguments
1059 --
1060 -- Notes
1061 -- Commented the code for fixing bug 3875383
1062
1063 PROCEDURE Delete_Archived_Items(itemtype in varchar2,
1064 itemkey in varchar2,
1065 actid in number,
1066 funcmode in varchar2,
1067 resultout in out NOCOPY varchar2) IS
1068 l_debug_level NUMBER;
1069
1070 x_progress VARCHAR2(100);
1071 l_error_code NUMBER;
1072 l_error_msg VARCHAR2(1000);
1073 p_inventory_item_id NUMBER;
1074 p_org_id NUMBER;
1075
1076 BEGIN
1077 /* Commented the code for deletion for fixing bug 3875383*/
1078 /******
1079 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
1080 x_progress := 'CLN_SYNCITEM_PKG.Delete_Archived_Items : Entered Procedure';
1081 if (l_debug_level <= 1) then
1082 cln_debug_pub.Add('Failure point ' || x_progress, 1);
1083 end if;
1084
1085 -- Retrieve Activity Attributes
1086 p_inventory_item_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'INVENTORY_ITEM_ID');
1087 p_org_id := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'ORGANIZATION_ID');
1088
1089 DELETE FROM cln_itemmst_deleted_items
1090 WHERE inventory_item_id = p_inventory_item_id AND organization_id = p_org_id;
1091
1092 -- Reached Here. Successful execution.
1093 x_progress := 'CLN_SYNCITEM_PKG.Delete_Archived_Items : Exiting Procedure';
1094 if (l_debug_level <= 1) then
1095 cln_debug_pub.Add('Failure point ' || x_progress, 1);
1096 end if;
1097 ******/
1098 resultout := 'COMPLETE:T';
1099 EXCEPTION
1100 WHEN OTHERS THEN
1101 l_error_code := SQLCODE;
1102 l_error_msg := SQLERRM;
1103 if (l_debug_level <= 1) then
1104 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
1105 end if;
1106
1107 x_progress := 'CLN_SYNCITEM_PKG.Delete_Archived_Items : ERROR';
1108 if (l_debug_level <= 1) then
1109 cln_debug_pub.Add('Failure point ' || x_progress, 1);
1110 end if;
1111 END Delete_Archived_Items;
1112
1113 END CLN_SYNCITEM_PKG;