[Home] [Help]
PACKAGE BODY: APPS.EGO_ORCHESTRATION_UTIL_PUB
Source
1 package body ego_orchestration_util_pub as
2 /* $Header: EGOORCHB.pls 120.21 2007/12/04 14:00:15 bbpatel noship $ */
3
4
5 FUNCTION GET_DATE_FROM_XML(l_xml_date IN XMLTYPE)
6 RETURN DATE -- bug:6507903 Changed return type from varchar2 to date
7 IS
8 BEGIN
9 IF l_xml_date IS NULL
10 THEN
11 RETURN NULL;
12 ELSE
13 RETURN To_Date( REGEXP_REPLACE(l_xml_date.getStringVal(), 'T', ' '), 'YYYY-MM-DD HH24:MI:SS');
14 END IF;
15 END GET_DATE_FROM_XML;
16
17 -- -----------------------------------------------------------------------------
18 -- API Name: Set_ICC_For_Rec_Bundle
19 -- This API will set Primary Catalog Category for Record bundle.
20 -- Input = Record Bundle Id
21 -- output = 1. Status ERROR or SUCCESS
22 -- 2. GPC Code list for Advanced GPC to ICC mapping has not done.
23 -- Signatures of Catalog mangements APIs are mentioned below.
24 -- -----------------------------------------------------------------------------
25
26
27 PROCEDURE Set_ICC_For_Rec_Bundle
28 ( p_rb_id IN NUMBER ,
29 x_Status OUT NOCOPY VARCHAR2,
30 x_Gpc_list OUT NOCOPY VARCHAR2
31 )IS
32 CURSOR Get_All_Items(l_b_Id NUMBER) IS
33 SELECT gpc_code, bundle_id, source_system_id, source_system_reference
34 FROM mtl_system_items_interface
35 WHERE bundle_id = l_b_Id ;
36 l_icc_code NUMBER;
37
38 BEGIN
39 x_Gpc_list := NULL;
40 x_Status := 'SUCCESS';
41 FOR item_data IN Get_All_Items(l_b_Id => p_rb_id)
42 LOOP
43 EGO_CATG_MAP_UTIL_PKG.Get_Item_Catalog_Ctgr_Mapping(
44 P_GPC_ID => item_data.gpc_code --gpc_code
45 ,X_ICC_CATEGORY_ID => l_icc_code); -- Icc code
46 --l_icc_code := 0;
47 IF l_icc_code IS NOT NULL
48 THEN
49 UPDATE mtl_system_items_interface
50 SET Item_catalog_group_id = l_icc_code
51 WHERE bundle_id = item_data.bundle_id
52 AND source_system_id = item_data.source_system_id
53 AND source_system_reference = item_data.source_system_reference;
54 ELSE
55 x_Status := 'ERROR';
56 x_Gpc_list := x_Gpc_list ||',' || item_data.gpc_code;
57 END IF;
58 END LOOP;
59 IF ( x_Status <> 'ERROR' )
60 THEN
61 x_Status := 'SUCCESS';
62 END IF;
63 COMMIT;
64 END;
65
66 -- -----------------------------------------------------------------------------
67 -- API Name: Set_ACC_For_Rec_Bundle
68 -- This API will set Alternate Catalog Category for Record bundle.
69 -- Input = Record Bundle Id
70 -- output = 1. Status ERROR or SUCCESS
71 -- 2. GPC Code list for Advanced GPC to ACC mapping has not done.
72 -- -----------------------------------------------------------------------------
73
74 PROCEDURE Set_ACC_For_Rec_Bundle
75 ( p_rb_id IN NUMBER ,
76 x_Status OUT NOCOPY VARCHAR2,
77 x_Gpc_list OUT NOCOPY VARCHAR2
78 )IS
79 CURSOR Get_All_Items(l_b_Id NUMBER)
80 IS SELECT gpc_code ,bundle_id, global_trade_item_number,source_system_reference
81 FROM mtl_system_items_interface
82 WHERE bundle_id = l_b_Id ;
83
84 l_acc_code NUMBER;
85 l_acc_catalog NUMBER;
86
87 BEGIN
88 x_Gpc_list := NULL;
89 FOR item_data IN Get_All_Items(l_b_Id => p_rb_id)
90 LOOP
91 EGO_CATG_MAP_UTIL_PKG.Get_Alt_Catalog_Ctgr_Mapping
92 ( P_GPC_ID => item_data.gpc_code --gpc code
93 ,X_ACC_CATEGORY_ID => l_acc_code -- acc code
94 ,X_ACC_CATALOG_ID => l_acc_catalog ); --acc catalog
95 --l_acc_code := 0;
96 --l_acc_catalog := 0;
97 IF l_acc_code IS NOT NULL
98 THEN
99 UPDATE MTL_ITEM_CATEGORIES_INTERFACE
100 SET CATEGORY_id = l_acc_code ,CATEGORY_SET_ID = l_acc_catalog
101 WHERE bundle_id = item_data.bundle_id
102 AND source_system_id = item_data.global_trade_item_number
103 AND source_system_reference = item_data.source_system_reference;
104 COMMIT;
105 ELSE
106 x_Status := 'ERROR';
107 x_Gpc_list := x_Gpc_list ||',' || item_data.gpc_code;
108 END IF;
109 END LOOP;
110 IF ( x_Status <> 'ERROR' )
111 THEN
112 x_Status := 'SUCCESS';
113 END IF;
114 END;
115
116 PROCEDURE ADD_BUNDLES_TO_COL (x_bundle_collection_id IN NUMBER,
117 p_bundles_clob IN CLOB,
118 x_new_bundle_col_id OUT NOCOPY NUMBER,
119 p_commit IN VARCHAR2 DEFAULT 'Y',
120 p_entity_name IN VARCHAR2 DEFAULT 'BUNDLE'
121 )
122 IS
123 CURSOR c_bundles(p_bundles_xml XMLTYPE)
124 IS
125 SELECT Value(xml_tab) bundle
126 FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/Bundles/Bundle'))) xml_tab;
127
128 CURSOR c_bundle_items(p_bundles_xml XMLTYPE)
129 IS
130 SELECT Value(xml_tab) bundle
131 FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '//ItemBundle'))) xml_tab;
132
133 l_bundle_collection_id NUMBER;
134 l_bundle_id NUMBER;
135 p_bundles_xml XMLTYPE;
136 p_cln_bundles_clob CLOB;
137 l_source_system_id NUMBER;
138 l_source_system_ref VARCHAR2(255);
139
140 BEGIN
141 l_bundle_collection_id := x_bundle_collection_id;
142 IF (l_bundle_collection_id = NULL OR
143 l_bundle_collection_id = 0 OR
144 l_bundle_collection_id = -1)
145 THEN
146 SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
147 INTO l_bundle_collection_id
148 FROM dual;
149 END IF;
150
151
152 x_new_bundle_col_id := l_bundle_collection_id;
153 p_cln_bundles_clob := REPLACE(p_bundles_clob, 'ns1:');
154 p_bundles_xml := XMLTYPE.createXML(xmlData => p_cln_bundles_clob);
155 IF p_entity_name = 'ITEM'
156 THEN
157 IF p_bundles_xml.extract('//ItemBundle') IS NOT NULL
158 THEN
159 FOR c_bls IN c_bundle_items(p_bundles_xml)
160 LOOP
161 l_bundle_id := c_bls.bundle.extract('/ItemBundle/BundleId/text()').getNumberVal();
162 l_source_system_id := c_bls.bundle.extract('/ItemBundle/ItemSourceSystemId/text()').getNumberVal();
163 l_source_system_ref := c_bls.bundle.extract('/ItemBundle/ItemSourceSystemReference/text()').getStringVal();
164
165 INSERT INTO MTL_ITEM_BULKLOAD_RECS (request_id,
166 creation_date,
167 last_update_date,
168 created_by,
169 last_updated_by,
170 bundle_collection_id,
171 bundle_id,
172 source_system_id,
173 source_system_reference
174 ) values
175 (-1,
176 sysdate,
177 sysdate,
178 1,
179 1,
180 l_bundle_collection_id,
181 l_bundle_id,
182 l_source_system_id,
183 l_source_system_ref
184 );
185 END LOOP;
186 END IF;
187
188 ELSE
189 FOR c_bls IN c_bundles(p_bundles_xml)
190 LOOP
191 l_bundle_id := c_bls.bundle.extract('/Bundle/BundleId/text()').getNumberVal();
192 INSERT INTO MTL_ITEM_BULKLOAD_RECS (request_id,
193 creation_date,
194 last_update_date,
195 created_by,
196 last_updated_by,
197 bundle_collection_id,
198 bundle_id,
199 message_type,
200 message_code
201 ) values
202 (-1,
203 sysdate,
204 sysdate,
205 1,
206 1,
207 l_bundle_collection_id,
208 l_bundle_id,
209 c_bls.bundle.extract('/Bundle/Message/@type').getStringVal(),
210 substr(c_bls.bundle.extract('/Bundle/Message/text()').getStringVal(), 1, 80));
211 END LOOP;
212 END IF;
213 IF p_commit = 'Y'
214 THEN
215 COMMIT;
216 END IF;
217 END ADD_BUNDLES_TO_COL;
218 -- -----------------------------------------------------------------------------
219 -- API Name: Set_ICC_For_Rec_Collection
220 -- This API will set Primary Catalog Category for bundle collection.
221 -- Input = Bundle Collection Id
222 -- output = 1. Bundle Collection Id With ICC (this will contain all bundles with ICC )
223 -- 2. Bundle Collection Id With ICC (this will contain all bundles without ICC )
224 --
225 -- -----------------------------------------------------------------------------
226
227 PROCEDURE Set_ICC_For_Rec_Collection
228 ( p_rc_id IN NUMBER ,
229 x_BundleWithICC OUT NOCOPY NUMBER,
230 x_BundleWithoutICC OUT NOCOPY NUMBER
231 )IS
232
233 CURSOR getBundles(p_rcb_id NUMBER )
234 IS SELECT bundle_id
235 FROM MTL_ITEM_BULKLOAD_RECS
236 WHERE bundle_collection_id = p_rcb_id;
237
238 l_gpc_list VARCHAR2(1000);
239 l_status VARCHAR2(1000);
240 l_FlagE VARCHAR2(10);
241 l_FlagS VARCHAR2(10);
242 l_bundle_W clob;
243 l_bundle_WO clob;
244
245 l_buffer varchar2(1000);
246
247 l_BundlesStartTag varchar2(100);
248 l_BundleStartTag varchar2(100);
249 l_BundlesEndTag varchar2(100);
250 l_BundleEngTag varchar2(100);
251
252 BEGIN
253
254 l_BundlesStartTag := '<Bundles>' ;
255 l_BundleStartTag := '<Bundle><BundleId>';
256 l_BundlesEndTag := '</Bundles>';
257 l_BundleEngTag := '</BundleId></Bundle>';
258 l_FlagE := 'N';
259 l_FlagS := 'N';
260 l_bundle_W := to_clob(' ');
261 l_bundle_WO := to_clob(' ');
262 dbms_lob.writeappend(l_bundle_W, length(l_BundlesStartTag), l_BundlesStartTag);
263 dbms_lob.writeappend(l_bundle_WO, length(l_BundlesStartTag), l_BundlesStartTag);
264 FOR record_bundle IN getBundles(p_rcb_id => p_rc_id)
265 LOOP
266 l_buffer := l_BundleStartTag || record_bundle.bundle_id || l_BundleEngTag ;
267 Set_ICC_For_Rec_Bundle(
268 p_rb_id => record_bundle.bundle_id --record bundle id code
269 ,x_Status => l_status -- status
270 ,x_Gpc_list => l_gpc_list ); --gpc list
271
272 IF (l_status = 'SUCCESS') THEN
273 l_FlagS := 'Y';
274 dbms_lob.writeappend(l_bundle_W, length(l_buffer), l_buffer);
275 ELSE
276 l_FlagE := 'Y';
277 dbms_lob.writeappend(l_bundle_WO, length(l_buffer), l_buffer);
278 END IF;
279 END LOOP ;
280 dbms_lob.writeappend(l_bundle_W, length(l_BundlesEndTag), l_BundlesEndTag);
281 dbms_lob.writeappend(l_bundle_WO, length(l_BundlesEndTag), l_BundlesEndTag);
282 IF( l_FlagS = 'Y') THEN
283 ADD_BUNDLES_TO_COL(
284 x_bundle_collection_id => -1 --record bundle id code
285 ,p_bundles_clob => l_bundle_W -- status
286 ,x_new_bundle_col_id => x_BundleWithICC ); --gpc list
287 ELSE
288 x_BundleWithICC := 0;
289 END IF;
290 IF( l_FlagE = 'Y') THEN
291 ADD_BUNDLES_TO_COL(
292 x_bundle_collection_id => -1 --record bundle id code
293 ,p_bundles_clob => l_bundle_WO -- status
294 ,x_new_bundle_col_id => x_BundleWithoutICC ); --gpc list
295 ELSE
296 x_BundleWithoutICC := 0;
297 END IF ;
298 END ;
299
300 -- -----------------------------------------------------------------------------
301 -- API Name: Set_ACC_For_Rec_Collection
302 -- This API will set Alternate Catalog Category for bundle collection.
303 -- Input = Bundle Collection Id
304 -- output = 1. Bundle Collection Id With ACC (this will contain all bundles with ACC )
305 -- 2. Bundle Collection Id With ACC (this will contain all bundles without ACC )
306 --
307 -- -----------------------------------------------------------------------------
308
309
310 PROCEDURE Set_ACC_For_Rec_Collection
311 ( p_rc_id IN NUMBER ,
312 x_BundleWithACC OUT NOCOPY NUMBER,
313 x_BundleWithoutACC OUT NOCOPY NUMBER
314 )IS
315 CURSOR getBundles(p_rcb_id NUMBER ) IS
316 SELECT bundle_id
317 FROM MTL_ITEM_BULKLOAD_RECS
318 WHERE bundle_collection_id = p_rcb_id
319 AND entity_type LIKE 'ITEM';
320
321 l_gpc_list VARCHAR2(1000);
322 l_status VARCHAR2(1000);
323
324 l_bundle_W clob;
325 l_bundle_WO clob;
326 l_FlagE VARCHAR2(10);
327 l_FlagS VARCHAR2(10);
328
329 l_buffer varchar2(1000);
330
331 l_BundlesStartTag varchar2(100);
332 l_BundleStartTag varchar2(100);
333 l_BundlesEndTag varchar2(100);
334 l_BundleEngTag varchar2(100);
335
336 BEGIN
337
338 l_BundlesStartTag := '<Bundles>' ;
339 l_BundleStartTag := '<Bundle><BundleId>';
340 l_BundlesEndTag := '</Bundles>';
341 l_BundleEngTag := '</BundleId></Bundle>';
342 l_bundle_W := to_clob(' ');
343 l_bundle_WO :=to_clob(' ');
344 dbms_lob.writeappend(l_bundle_W, length(l_BundlesStartTag), l_BundlesStartTag);
345 dbms_lob.writeappend(l_bundle_WO, length(l_BundlesStartTag), l_BundlesStartTag);
346 l_FlagE := 'N';
347 l_FlagS := 'N';
348
349 FOR record_bundle IN getBundles(p_rcb_id => p_rc_id)
350 LOOP
351 l_buffer := l_BundleStartTag || record_bundle.bundle_id || l_BundleEngTag ;
352 Set_ACC_For_Rec_Bundle(
353 p_rb_id => record_bundle.bundle_id --record bundle id code
354 ,x_status => l_status -- status
355 ,x_gpc_list => l_gpc_list ); --gpc list
356
357 IF (l_status = 'SUCCESS') THEN
358 dbms_lob.writeappend(l_bundle_W, length(l_buffer), l_buffer);
359 l_FlagS := 'Y';
360 ELSE
361 dbms_lob.writeappend(l_bundle_WO, length(l_buffer), l_buffer);
362 l_FlagE := 'Y';
363 END IF;
364 END LOOP ;
365 dbms_lob.writeappend(l_bundle_W, length(l_BundlesEndTag), l_BundlesEndTag);
366 dbms_lob.writeappend(l_bundle_WO, length(l_BundlesEndTag), l_BundlesEndTag);
367 IF( l_FlagS = 'Y') THEN
368 ADD_BUNDLES_TO_COL(
369 x_bundle_collection_id => -1 --record bundle id code
370 ,p_bundles_clob => l_bundle_W -- status
371 ,x_new_bundle_col_id => x_BundleWithACC ); --gpc list
372 ELSE
373 x_BundleWithACC := 0;
374 END IF;
375
376 IF( l_FlagE = 'Y') THEN
377 ADD_BUNDLES_TO_COL(
378 x_bundle_collection_id => -1 --record bundle id code
379 ,p_bundles_clob => l_bundle_WO -- status
380 ,x_new_bundle_col_id => x_BundleWithoutACC ); --gpc list
381 ELSE
382 x_BundleWithoutACC := 0;
383 END IF;
384
385 END ;
386
387 -- -----------------------------------------------------------------------------
388 -- API Name: validate_batch
389 -- This API will validate batch name passed and if it failed then will return defaul batch ID.
390 -- Input = 1. batch_name
391 -- 2. default_batch_name
392 -- output = 1. batch id
393 -- 2. error message
394 --
395 -- -----------------------------------------------------------------------------
396
397
398
399
400 PROCEDURE validate_batch
401 ( p_batch_name IN VARCHAR2 ,
402 p_default_batch_name IN VARCHAR2 ,
403 x_batch_id OUT NOCOPY NUMBER,
404 x_error_msg OUT NOCOPY VARCHAR2
405 ) IS
406
407 CURSOR Get_BatchId (l_batch_name VARCHAR2 ) IS
408 SELECT batch_id
409 FROM EGO_IMPORT_BATCHES_tl
410 WHERE name = l_batch_name
411 and language = USERENV('LANG');
412
413 CURSOR get_batch_type(l_batch_id NUMBER) IS
414 SELECT batch_type
415 from ego_import_batches_b
416 WHERE batch_id = l_batch_id;
417
418 CURSOR c_is_enabled_for_gdsn(l_batch_id NUMBER)
419 IS
420 SELECT 1
421 FROM DUAL
422 WHERE EXISTS (SELECT NULL
423 FROM EGO_IMPORT_OPTION_SETS
424 WHERE BATCH_ID = l_batch_id
425 AND ENABLED_FOR_DATA_POOL = 'Y'
426 );
427
428 -- Batch_id NUMBER;
429 l_Batch_satus VARCHAR2(100);
430 l_Batch_type VARCHAR2(100);
431 l_gdsn_batch BOOLEAN;
432 l_batch_id NUMBER;
433
434 BEGIN
435 x_error_msg := 'SUCCESS';
436 x_batch_id := 0;
437 l_batch_id := 0;
438 l_gdsn_batch := FALSE;
439
440 -- Find the Batch
441 OPEN Get_BatchId (l_batch_name => p_batch_name);
442 FETCH Get_BatchId INTO l_batch_id;
443 CLOSE Get_BatchId;
444 IF( l_batch_id = 0 )
445 THEN
446 x_error_msg := 'EGO_ORCH_INVALID_BATCH_NAME';
447 END IF ;
448
449 -- Check Batch Status
450 IF x_error_msg = 'SUCCESS'
451 THEN
452 l_Batch_satus := EGO_IMPORT_PVT.GET_BATCH_STATUS(l_batch_id );
453 IF ( l_Batch_satus <> 'A')
454 THEN
455 x_error_msg := 'EGO_ORCH_BATCH_INACTIVE';
456 END IF;
457 END IF;
458
459 IF x_error_msg = 'SUCCESS'
460 THEN
461 -- Validate Batch Type
462 OPEN get_batch_type (l_batch_id => l_batch_id);
463 FETCH get_batch_type INTO l_Batch_type;
464 CLOSE get_batch_type;
465 IF ( l_Batch_type = 'EGO_ITEM')
466 THEN
467 x_error_msg := 'EGO_ORCH_BATCH_ITEM_TYPE';
468 END IF;
469 END IF;
470
471 IF x_error_msg = 'SUCCESS'
472 THEN
473 FOR c_gdsn_batch_test in c_is_enabled_for_gdsn (l_batch_id => l_batch_id)
474 LOOP
475 l_gdsn_batch := TRUE;
476 END LOOP;
477 IF l_gdsn_batch <> TRUE
478 THEN
479 x_error_msg := 'EGO_ORCH_BATCH_NOT_GDSN';
480 END IF;
481 END IF;
482
483 IF x_error_msg <> 'SUCCESS'
484 THEN
485 OPEN Get_BatchId (l_batch_name => p_default_batch_name);
486 FETCH Get_BatchId INTO l_batch_id;
487 CLOSE Get_BatchId;
488
489 IF l_batch_id IS NULL
490 THEN
491 x_Batch_id := 0;
492 END IF;
493 ELSE
494 x_Batch_id := l_batch_id;
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS
499 THEN
500 x_error_msg := 'EGO_ORCH_BATCH_UEX_ERROR';
501 IF get_batch_type%ISOPEN
502 THEN
503 CLOSE get_batch_type;
504 END IF;
505 IF Get_BatchId%ISOPEN
506 THEN
507 CLOSE Get_BatchId;
508 END IF;
509 END;
510
511
512
513
514 FUNCTION PRE_PRE_PROCESS_BATCHES ( X_BUNDLE_COLLECTION_ID IN NUMBER,
515 X_COMMIT IN VARCHAR2
516 )
517 RETURN NUMBER
518 IS
519 l_collection_id NUMBER;
520 l_batch_id NUMBER;
521
522 CURSOR bundles IS
523 SELECT set_process_id
524 FROM MTL_SYSTEM_ITEMS_INTERFACE
525 WHERE BUNDLE_ID IN
526 (SELECT BUNDLE_ID
527 FROM MTL_ITEM_BULKLOAD_RECS
528 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
529
530 BEGIN
531 l_collection_id := X_BUNDLE_COLLECTION_ID;
532
533 --------------------------------------------------------------------------------------
534 -- PUTTING THE PARAMS FOR NEW ROWS
535 --------------------------------------------------------------------------------------
536
537 -- Updating items rows
538 UPDATE MTL_SYSTEM_ITEMS_INTERFACE ISTI
539 SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
540 PROCESS_FLAG = C_INIT_PROCESS_FLAG,
541 --CONFIRM_STATUS = 'UN',
542 SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
543 FROM EGO_IMPORT_BATCHES_B
544 WHERE BATCH_ID = ISTI.SET_PROCESS_ID)
545 WHERE BUNDLE_ID IN
546 (SELECT BUNDLE_ID
547 FROM MTL_ITEM_BULKLOAD_RECS
548 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID)
549 AND ITEM_NUMBER IS NULL;
550
551 UPDATE MTL_SYSTEM_ITEMS_INTERFACE ISTI
552 SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
553 PROCESS_FLAG = C_INIT_PROCESS_FLAG,
554 CONFIRM_STATUS = 'CN',
555 SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
556 FROM EGO_IMPORT_BATCHES_B
557 WHERE BATCH_ID = ISTI.SET_PROCESS_ID)
558 WHERE BUNDLE_ID IN
559 (SELECT BUNDLE_ID
560 FROM MTL_ITEM_BULKLOAD_RECS
561 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID)
562 AND ITEM_NUMBER IS NOT NULL;
563
564 -- Updating items user attribute rows
565 UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
566 SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
567 PROCESS_STATUS = C_INIT_PROCESS_FLAG,
568 SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
569 FROM EGO_IMPORT_BATCHES_B
570 WHERE BATCH_ID = EIUAI.DATA_SET_ID)
571 WHERE BUNDLE_ID IN
572 (SELECT BUNDLE_ID
573 FROM MTL_ITEM_BULKLOAD_RECS
574 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
575
576 -- Updating items association rows
577 UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
578 SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
579 PROCESS_FLAG = C_INIT_PROCESS_FLAG,
580 SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
581 FROM EGO_IMPORT_BATCHES_B
582 WHERE BATCH_ID = EIAI.BATCH_ID)
583 WHERE BUNDLE_ID IN
584 (SELECT BUNDLE_ID
585 FROM MTL_ITEM_BULKLOAD_RECS
586 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
587
588 -- Updating items caetgories rows
589 UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
590 SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
591 PROCESS_FLAG = C_INIT_PROCESS_FLAG,
592 SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
593 FROM EGO_IMPORT_BATCHES_B
594 WHERE BATCH_ID = MICI.SET_PROCESS_ID)
595 WHERE BUNDLE_ID IN
596 (SELECT BUNDLE_ID
597 FROM MTL_ITEM_BULKLOAD_RECS
598 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
599
600 -- Updating bill header rows
601 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBOMI
602 SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
603 PROCESS_FLAG = 1
604 WHERE BUNDLE_ID IN
605 (SELECT BUNDLE_ID
606 FROM MTL_ITEM_BULKLOAD_RECS
607 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
608
609 -- Updating bill components rows
610 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
611 SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
612 PROCESS_FLAG = 1
613 WHERE BUNDLE_ID IN
614 (SELECT BUNDLE_ID
615 FROM MTL_ITEM_BULKLOAD_RECS
616 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
617
618 -- Updating translatable rows
619 UPDATE EGO_INTERFACE_TL
620 SET PROCESS_STATUS = 1
621 WHERE BUNDLE_ID IN
622 (SELECT BUNDLE_ID
623 FROM MTL_ITEM_BULKLOAD_RECS
624 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
625
626
627 UPDATE EGO_UCCNET_EVENTS EUE
628 SET SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
629 FROM EGO_IMPORT_BATCHES_B
630 WHERE BATCH_ID = EUE.import_batch_id)
631 WHERE CLN_ID IN
632 (SELECT BUNDLE_ID
633 FROM MTL_ITEM_BULKLOAD_RECS
634 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
635
636
637 FOR batches IN bundles
638 LOOP
639 EGO_IMPORT_PVT.Resolve_SSXref_on_Data_load( p_data_set_id => batches.set_process_id);
640 END LOOP;
641
642 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBOMI
643 SET ORGANIZATION_ID = (SELECT ORGANIZATION_ID
644 FROM MTL_SYSTEM_ITEMS_INTERFACE
645 WHERE BUNDLE_ID = BBOMI.BUNDLE_ID
646 AND ROWNUM = 1),
647 ORGANIZATION_CODE = (SELECT mp.organization_code
648 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
649 MTL_PARAMETERS mp
650 WHERE mp.organization_id = msii.organization_id
651 AND msii.BUNDLE_ID = BBOMI.BUNDLE_ID
652 AND ROWNUM = 1)
653 WHERE BUNDLE_ID IN
654 (SELECT BUNDLE_ID
655 FROM MTL_ITEM_BULKLOAD_RECS
656 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
657
658 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
659 SET ORGANIZATION_ID = (SELECT ORGANIZATION_ID
660 FROM MTL_SYSTEM_ITEMS_INTERFACE
661 WHERE BUNDLE_ID = BICI.BUNDLE_ID
662 AND ROWNUM = 1),
663 ORGANIZATION_CODE = (SELECT mp.organization_code
664 FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
665 MTL_PARAMETERS mp
666 WHERE mp.organization_id = msii.organization_id
667 AND msii.BUNDLE_ID = BICI.BUNDLE_ID
668 AND ROWNUM = 1)
669 WHERE BUNDLE_ID IN
670 (SELECT BUNDLE_ID
671 FROM MTL_ITEM_BULKLOAD_RECS
672 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
673
674 UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
675 SET pk2_value = (SELECT asa.VENDOR_SITE_ID
676 FROM ap_supplier_sites_all asa,
677 ap_supplier_sites_all asa2,
678 mtl_system_items_interface msii
679 WHERE asa.party_site_id = asa2.party_site_id
680 AND asa.vendor_id = EIUAI.pk1_value
681 AND asa2.vendor_site_id = EIUAI.pk2_value
682 AND asa.org_id = msii.organization_id
683 AND msii.BUNDLE_ID = EIUAI.BUNDLE_ID
684 AND ROWNUM =1)
685 WHERE data_level_name = 'ITEM_SUP_SITE'
686 AND pk2_value IS NOT NULL
687 AND BUNDLE_ID IN
688 (SELECT BUNDLE_ID
689 FROM MTL_ITEM_BULKLOAD_RECS
690 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
691
692 UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
693 SET pk2_value = (SELECT asa.VENDOR_SITE_ID
694 FROM ap_supplier_sites_all asa,
695 ap_supplier_sites_all asa2,
696 mtl_system_items_interface msii
697 WHERE asa.party_site_id = asa2.party_site_id
698 AND asa.vendor_id = EIAI.pk1_value
699 AND asa2.vendor_site_id = EIAI.pk2_value
700 AND asa.org_id = msii.organization_id
701 AND msii.BUNDLE_ID = EIAI.BUNDLE_ID
702 AND ROWNUM =1)
703 WHERE data_level_name = 'ITEM_SUP_SITE'
704 AND pk2_value IS NOT NULL
705 AND BUNDLE_ID IN
706 (SELECT BUNDLE_ID
707 FROM MTL_ITEM_BULKLOAD_RECS
708 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
709
710 UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
711 SET pk2_value = (SELECT asa.VENDOR_SITE_ID
712 FROM ap_supplier_sites_all asa,
713 ap_supplier_sites_all asa2,
714 mtl_system_items_interface msii
715 WHERE asa.party_site_id = asa2.party_site_id
716 AND asa.vendor_id = EIUAI.pk1_value
717 AND asa2.vendor_site_id = EIUAI.pk2_value
718 AND asa.org_id = msii.organization_id
719 AND msii.BUNDLE_ID = EIUAI.BUNDLE_ID
720 AND ROWNUM =1)
721 WHERE data_level_name = 'ITEM_SUP_SITE_ORG'
722 AND pk2_value IS NOT NULL
723 AND BUNDLE_ID IN
724 (SELECT BUNDLE_ID
725 FROM MTL_ITEM_BULKLOAD_RECS
726 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
727
728 UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
729 SET pk2_value = (SELECT asa.VENDOR_SITE_ID
730 FROM ap_supplier_sites_all asa,
731 ap_supplier_sites_all asa2,
732 mtl_system_items_interface msii
733 WHERE asa.party_site_id = asa2.party_site_id
734 AND asa.vendor_id = EIAI.pk1_value
735 AND asa2.vendor_site_id = EIAI.pk2_value
736 AND asa.org_id = msii.organization_id
737 AND msii.BUNDLE_ID = EIAI.BUNDLE_ID
738 AND ROWNUM =1)
739 WHERE data_level_name = 'ITEM_SUP_SITE_ORG'
740 AND pk2_value IS NOT NULL
741 AND BUNDLE_ID IN
742 (SELECT BUNDLE_ID
743 FROM MTL_ITEM_BULKLOAD_RECS
744 WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
745
746 RETURN l_collection_id;
747 EXCEPTION
748 WHEN OTHERS
749 THEN
750 RETURN NULL;
751 END PRE_PRE_PROCESS_BATCHES;
752
753
754 FUNCTION GET_BUNDLES_FROM_COL ( p_bundle_collection_id IN NUMBER,
755 p_prior_bundle_id IN NUMBER,
756 p_max_elements IN NUMBER)
757 RETURN XMLTYPE
758 IS
759 l_xml_doc XMLTYPE;
760 BEGIN
761 l_xml_doc := NULL;
762 SELECT XMLELEMENT("BundleCollections",
763 XMLELEMENT("BundleCollection",
764 XMLELEMENT("BundleCollectionId", bundle_collection_id),
765 XMLELEMENT("Bundles",
766 XMLAGG(XMLELEMENT("Bundle",
767 XMLELEMENT("BundleId", bundle_id)))))) XML_DOC
768 INTO l_xml_doc
769 FROM MTL_ITEM_BULKLOAD_RECS
770 WHERE bundle_collection_id = p_bundle_collection_id
771 AND bundle_id > Nvl(p_prior_bundle_id, 0)
772 AND ROWNUM < p_max_elements
773 GROUP BY bundle_collection_id;
774
775 RETURN l_xml_doc;
776
777 EXCEPTION
778 WHEN NO_DATA_FOUND
779 THEN
780 return l_xml_doc;
781 END GET_BUNDLES_FROM_COL;
782
783 PROCEDURE GET_SUPPLIER_INFO ( X_EXT_SUP_ID IN VARCHAR2,
784 X_EXT_SUP_TYPE IN VARCHAR2,
785 X_SUP_LEVEL IN VARCHAR2,
786 X_SUPPLIER_ID OUT NOCOPY NUMBER,
787 X_SUPPLIER_NAME OUT NOCOPY VARCHAR2
788 )
789 IS
790 BEGIN
791 X_SUPPLIER_ID := NULL;
792 X_SUPPLIER_NAME := NULL;
793
794 IF X_EXT_SUP_TYPE = 'GLN' AND X_SUP_LEVEL = 'SUPPLIER'
795 THEN
796 SELECT asa.VENDOR_ID, aas.VENDOR_NAME
797 INTO X_SUPPLIER_ID, X_SUPPLIER_NAME
798 FROM ap_suppliers aas,
799 ap_supplier_sites_all asa,
800 hz_party_sites hps
801 WHERE hps.GLOBAL_LOCATION_NUMBER = X_EXT_SUP_ID
802 AND hps.party_site_id = asa.party_site_id
803 AND aas.vendor_id = asa.vendor_id
804 AND rownum = 1;
805
806 ELSIF X_EXT_SUP_TYPE = 'GLN'
807 THEN
808 SELECT asa.VENDOR_SITE_ID, asa.VENDOR_SITE_CODE
809 INTO X_SUPPLIER_ID, X_SUPPLIER_NAME
810 FROM ap_suppliers aas,
811 ap_supplier_sites_all asa,
812 hz_party_sites hps
813 WHERE hps.GLOBAL_LOCATION_NUMBER = X_EXT_SUP_ID
814 AND hps.party_site_id = asa.party_site_id
815 AND aas.vendor_id = asa.vendor_id
816 AND rownum = 1;
817
818 END IF;
819
820 EXCEPTION
821 WHEN OTHERS
822 THEN
823 NULL;
824 END GET_SUPPLIER_INFO;
825
826
827 FUNCTION GET_NEXT_ID RETURN NUMBER
828 IS
829 l_id NUMBER;
830 BEGIN
831 SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
832 INTO l_id
833 FROM dual;
834
835 RETURN l_id;
836 END;
837
838 PROCEDURE PROCESS_TL_ROWS(p_table_name IN VARCHAR2,
839 p_batch_id IN NUMBER,
840 p_unique_id IN NUMBER,
841 p_bundle_id IN NUMBER,
842 p_xml_data IN XMLTYPE,
843 p_entity_name IN VARCHAR2,
844 p_column_name IN VARCHAR2
845 )
846 IS
847 CURSOR c_entries(p_collection_xml XMLTYPE)
848 IS
849 SELECT Value(xml_tab) entry
850 FROM TABLE(XMLSEQUENCE(EXTRACT(p_collection_xml, '/TL/'||p_entity_name))) xml_tab;
851
852 BEGIN
853 FOR trans_entry IN c_entries(p_xml_data)
854 LOOP
855 INSERT INTO EGO_INTERFACE_TL (
856 set_process_id,
857 unique_id,
858 bundle_id,
859 table_name,
860 LANGUAGE,
861 column_name,
862 column_value,
863 created_by,
864 creation_date,
865 last_updated_by,
866 last_update_date,
867 last_update_login
868 ) VALUES(
869 p_batch_id,
870 p_unique_id,
871 p_bundle_id,
872 p_table_name,
873 trans_entry.ENTRY.extract('/'||p_entity_name||'/@languageID').getStringVal(),
874 p_column_name,
875 trans_entry.ENTRY.extract('/'||p_entity_name||'/text()').getStringVal(),
876 1,
877 SYSDATE,
878 1,
879 SYSDATE,
880 1
881 );
882 END LOOP;
883 END;
884
885
886 PROCEDURE SAVE_ATTR_DATA( p_xml IN XMLTYPE,
887 p_entity_name IN VARCHAR2,
888 p_transaction_id IN NUMBER,
889 p_bundle_id IN NUMBER,
890 p_source_system_id IN NUMBER,
891 p_source_system_reference IN VARCHAR2,
892 p_organization_code IN VARCHAR2,
893 p_data_set_id IN NUMBER,
894 p_data_level_name IN VARCHAR2,
895 p_pk1_value IN NUMBER,
896 p_pk2_value IN NUMBER,
897 p_created_by IN NUMBER,
898 p_creation_date IN DATE,
899 p_last_updated_by IN NUMBER,
900 p_last_update_date IN DATE,
901 p_last_update_login IN NUMBER)
902 IS
903
904 -- Attribute Groups
905 CURSOR c_AttributeGrps(p_entity_xml XMLTYPE, p_entity_name VARCHAR2)
906 IS
907 SELECT Value(xml_tab) attributeGroups
908 FROM TABLE(XMLSEQUENCE(EXTRACT(p_entity_xml, '/'||p_entity_name||'/AttributeGroup'))) xml_tab;
909
910 -- Attributes
911 CURSOR c_Attributes(p_attrGrp_xml XMLTYPE)
912 IS
913 SELECT Value(xml_tab) attributes
914 FROM TABLE(XMLSEQUENCE(EXTRACT(p_attrGrp_xml, '/AttributeGroup/Attribute'))) xml_tab;
915
916 l_row_identifier NUMBER;
917 l_attr_group_int_name VARCHAR2(255);
918 l_attr_int_name VARCHAR2(255);
919 l_attr_text_value VARCHAR2(255);
920 l_attr_numeric_value EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_NUM%TYPE;
921 l_attr_quant_unit_code EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_UOM%TYPE;
922 l_is_trans BOOLEAN;
923 l_xml_trans XMLTYPE;
924
925 l_xml_attr_grp_int_nm XMLTYPE;
926 l_xml_attr_int_nm XMLTYPE;
927 l_xml_attr_val XMLTYPE;
928 l_date_value EGO_ITM_USR_ATTR_INTRFC.attr_value_date%TYPE;
929
930 BEGIN
931 FOR l_xml_AttrGrps IN c_AttributeGrps(p_xml, p_entity_name)
932 LOOP
933 l_xml_attr_grp_int_nm := l_xml_AttrGrps.attributeGroups.extract('/AttributeGroup/ID/text()');
934 IF l_xml_attr_grp_int_nm IS NOT NULL AND (p_entity_name <> 'Item' OR l_xml_attr_grp_int_nm.getStringVal() <> 'EGO_ORCH_INT')
935 THEN
936 l_attr_group_int_name := l_xml_attr_grp_int_nm.getStringVal();
937 l_row_identifier := GET_NEXT_ID();
938 FOR l_xml_Attrs IN c_Attributes(l_xml_AttrGrps.attributeGroups)
939 LOOP
940 l_is_trans := FALSE;
941
942 l_attr_int_name := NULL;
943 l_xml_attr_int_nm := l_xml_Attrs.attributes.extract('/Attribute/ID/text()');
944 IF l_xml_attr_int_nm IS NOT NULL
945 THEN
946 l_attr_int_name := l_xml_attr_int_nm.getStringVal();
947 END IF;
948
949 l_xml_attr_val := l_xml_Attrs.attributes.extract('/Attribute/Value/text()');
950 l_attr_text_value := null;
951 IF l_xml_attr_val IS NULL
952 THEN
953 --l_attr_text_value := l_xml_Attrs.attributes.extract('/Attribute/ValueText[position() =1]/text()').getStringVal();
954 l_xml_attr_val := l_xml_Attrs.attributes.extract('/Attribute/ValueText[position() =1]/text()');
955 --IF l_attr_text_value IS NOT NULL
956 IF l_xml_attr_val IS NOT NULL
957 THEN
958 l_is_trans := TRUE;
959 END IF;
960 END IF;
961
962 IF l_xml_attr_val IS NOT NULL
963 THEN
964 l_attr_text_value := l_xml_attr_val.getStringVal();
965 END IF;
966
967 l_date_value := get_date_from_xml(l_xml_Attrs.attributes.extract('/Attribute/ValueDateTime/text()'));
968
969 -- bug:6504632 For numeric values, quantity attributes coming with UOM code, consider ValueQuantity tag
970 -- otherwise consider ValueNumeric tag
971 l_attr_numeric_value := NULL;
972 l_attr_quant_unit_code := NULL;
973 l_xml_attr_val := l_xml_Attrs.attributes.extract('/Attribute/ValueNumeric/text()');
974
975 IF l_xml_attr_val IS NOT NULL
976 THEN
977 l_attr_numeric_value := l_xml_attr_val.getNumberVal();
978 l_attr_quant_unit_code := NULL;
979 END IF;
980
981 l_xml_attr_val := l_xml_Attrs.attributes.extract('/Attribute/ValueQuantity/text()');
982
983 IF l_xml_attr_val IS NOT NULL
984 THEN
985 l_attr_numeric_value := l_xml_attr_val.getNumberVal();
986 l_attr_quant_unit_code := l_xml_Attrs.attributes.extract('/Attribute/ValueQuantity/@unitCode').getStringVal();
987 END IF;
988
989 INSERT INTO EGO_ITM_USR_ATTR_INTRFC(
990 transaction_id,
991 bundle_id,
992 source_system_id,
993 source_system_reference,
994 data_set_id,
995 row_identifier,
996 organization_code,
997
998 attr_group_type,
999 attr_group_int_name,
1000 attr_int_name,
1001 attr_value_str,
1002 attr_value_num,
1003 attr_value_date,
1004 attr_value_uom,
1005 data_level_name,
1006 pk1_value,
1007 pk2_value,
1008
1009 CREATED_BY,
1010 CREATION_DATE,
1011 LAST_UPDATED_BY,
1012 LAST_UPDATE_DATE,
1013 LAST_UPDATE_LOGIN
1014 ) VALUES (
1015 p_transaction_id,
1016 p_bundle_id,
1017 p_source_system_id,
1018 p_source_system_reference,
1019 p_data_set_id,
1020 l_row_identifier,
1021 p_organization_code,
1022
1023 'EGO_ITEMMGMT_GROUP', -- bug:6525204 Passing EGO_ITEMMGMT_GROUP as UDA type always
1024 l_attr_group_int_name,
1025 l_attr_int_name,
1026 l_attr_text_value,
1027 l_attr_numeric_value,
1028 l_date_value,
1029 l_attr_quant_unit_code,
1030 p_data_level_name,
1031 p_pk1_value,
1032 p_pk2_value,
1033
1034 p_created_by,
1035 p_creation_date,
1036 p_last_updated_by,
1037 p_last_update_date,
1038 p_last_update_login
1039 );
1040
1041 IF l_is_trans = TRUE
1042 THEN
1043 SELECT XMLELEMENT("TL", l_xml_Attrs.attributes.extract('/Attribute/ValueText'))
1044 INTO l_xml_trans
1045 FROM DUAL;
1046
1047 IF l_xml_trans IS NOT NULL
1048 THEN
1049 PROCESS_TL_ROWS(p_table_name => 'EGO_ITM_USR_ATTR_INTRFC',
1050 p_batch_id => p_data_set_id,
1051 p_unique_id => l_row_identifier,
1052 p_bundle_id => p_bundle_id,
1053 p_xml_data => l_xml_trans,
1054 p_entity_name => 'ValueText',
1055 p_column_name => l_attr_int_name);
1056 END IF;
1057 END IF;
1058 END LOOP; -- Loop over Attributes
1059 END IF;
1060 END LOOP; -- Loop over Attribute Groups
1061 END SAVE_ATTR_DATA;
1062
1063 PROCEDURE SAVE_DATA ( p_xml_clob IN CLOB,
1064 p_commit IN VARCHAR2,
1065 p_source_sys_id IN NUMBER,
1066 p_default_batch IN VARCHAR2,
1067 x_new_bundle_col_id OUT NOCOPY NUMBER,
1068 x_err_bundle_col_id OUT NOCOPY NUMBER)
1069 IS
1070 l_rt_trimmed_xml CLOB;
1071 l_trimmed_xml CLOB;
1072 l_bundles_xml CLOB;
1073 l_xml_data XMLTYPE;
1074
1075 l_BundlesStartTag varchar2(25) := '<Bundles>';
1076 l_BundleStartTag varchar2(25) := '<Bundle>';
1077 l_BundlesEndTag varchar2(25) := '</Bundles>';
1078 l_BundleEndTag varchar2(25) := '</Bundle>';
1079 l_BundleIdStartTag varchar2(25) := '<BundleId>';
1080 l_BundleIdEndTag varchar2(25) := '</BundleId>';
1081
1082 l_MessageTag VARCHAR2(200);
1083
1084 l_xml_trans XMLTYPE;
1085 x_return_type VARCHAR2(80);
1086 x_return_msg VARCHAR2(80);
1087 l_return_msg VARCHAR2(80);
1088
1089 l_bundle_id NUMBER;
1090 l_batch_id NUMBER;
1091 l_batch_name VARCHAR2(255);
1092 l_org_code VARCHAR2(80);
1093
1094 l_message_id VARCHAR2(80);
1095 l_message_date DATE;
1096 l_ext_complex_item_reference VARCHAR2(255);
1097 l_transaction_id NUMBER;
1098 l_source_sys_reference VARCHAR2(255);
1099 l_error_msg VARCHAR2(2000);
1100 l_supplier_id NUMBER;
1101 l_supplier_site_id NUMBER;
1102 l_supplier_name VARCHAR2(255);
1103 l_row_identifier NUMBER;
1104 l_hdr_source_sys_reference VARCHAR2(255);
1105 l_xml_null_chk XMLTYPE;
1106 l_xml_batch_name XMLTYPE;
1107 l_external_bundle_id VARCHAR2(255);
1108
1109 l_bundles_clob CLOB;
1110 l_err_bundles_clob CLOB;
1111
1112 l_err_bundle BOOLEAN;
1113 l_reg_bundle BOOLEAN;
1114 l_item_id VARCHAR2(80);
1115
1116 l_created_by NUMBER;
1117 l_creation_date DATE;
1118 l_last_updated_by NUMBER;
1119 l_last_update_date DATE;
1120 l_last_update_login NUMBER;
1121
1122 l_supplier_attr_level VARCHAR2(30);
1123
1124 l_alt_cat_concat_seg MTL_CATEGORIES_KFV.CONCATENATED_SEGMENTS%TYPE;
1125
1126 EGO_ORC_HDR_SEC_NOT_FOUND EXCEPTION;
1127 EGO_ORC_NO_BATCH EXCEPTION;
1128 EGO_MSG_ERROR EXCEPTION;
1129 EGO_ORC_NO_GTIN EXCEPTION;
1130 EGO_ORC_NO_GLN EXCEPTION;
1131 EGO_ORC_NO_STRUCTURE EXCEPTION;
1132 EGO_ORC_XML_ERROR EXCEPTION;
1133 EGO_ORC_INVALID_GLN EXCEPTION;
1134 EGO_ORC_DELETE_LINE EXCEPTION;
1135
1136 -- Bundles
1137 CURSOR c_bundles(p_collection_xml XMLTYPE)
1138 IS
1139 SELECT Value(xml_tab) bundles
1140 FROM TABLE(XMLSEQUENCE(EXTRACT(p_collection_xml, '/XMLEntries/SyncItemPublication/ItemPublicationLine'))) xml_tab;
1141
1142 -- Items
1143 CURSOR c_items(p_bundles_xml XMLTYPE)
1144 IS
1145 SELECT Value(xml_tab) items
1146 FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/ItemPublicationLine/Item'))) xml_tab;
1147
1148 -- Classification
1149 CURSOR c_classifications(p_items_xml XMLTYPE)
1150 IS
1151 SELECT Value(xml_tab) classifications
1152 FROM TABLE(XMLSEQUENCE(EXTRACT(p_items_xml, '/Item/ItemCatalog'))) xml_tab;
1153
1154 -- Suppliers
1155 CURSOR c_suppliers(p_items_xml XMLTYPE)
1156 IS
1157 SELECT Value(xml_tab) suppliers
1158 FROM TABLE(XMLSEQUENCE(EXTRACT(p_items_xml, '/Item/ItemSupplier'))) xml_tab;
1159
1160 -- Suppliers
1161 CURSOR c_supplierLocations(p_suppliers_xml XMLTYPE)
1162 IS
1163 SELECT Value(xml_tab) supplierLocations
1164 FROM TABLE(XMLSEQUENCE(EXTRACT(p_suppliers_xml, '/ItemSupplier/ItemSupplierLocation'))) xml_tab;
1165
1166 -- Structures
1167 CURSOR c_structure(p_bundles_xml XMLTYPE)
1168 IS
1169 SELECT Value(xml_tab) structures
1170 FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/ItemPublicationLine/ItemStructure'))) xml_tab;
1171
1172 -- Components
1173 CURSOR c_component(p_structures_xml XMLTYPE)
1174 IS
1175 SELECT Value(xml_tab) components
1176 FROM TABLE(XMLSEQUENCE(EXTRACT(p_structures_xml, '/ItemStructure/ComponentItem'))) xml_tab;
1177
1178 -- Alternate Category concatenated segments
1179 CURSOR c_alt_cat_concat_seg(c_alt_cat_code VARCHAR2)
1180 IS
1181 SELECT CONCATENATED_SEGMENTS
1182 FROM MTL_CATEGORIES_KFV
1183 WHERE SEGMENT2 = c_alt_cat_code
1184 AND ROWNUM = 1;
1185
1186 BEGIN
1187 x_return_type := 'S';
1188 -- Step 1: Prepare incoming data by trimming all prefixes
1189 l_trimmed_xml := REGEXP_REPLACE(p_xml_clob, 'xmlns="[^"]*"', '');
1190
1191 l_rt_trimmed_xml := REGEXP_REPLACE(l_trimmed_xml, '<\/[^:>=" ]*:', '</');
1192 l_trimmed_xml := REGEXP_REPLACE(l_rt_trimmed_xml, '<[^:>=" ]*:', '<');
1193
1194 l_created_by := 0;
1195 l_creation_date := SYSDATE;
1196 l_last_updated_by := 0;
1197 l_last_update_date := SYSDATE;
1198 l_last_update_login := 0;
1199
1200 l_err_bundle := FALSE;
1201 l_reg_bundle := FALSE;
1202
1203 l_error_msg := NULL;
1204
1205 x_new_bundle_col_id := NULL;
1206 x_err_bundle_col_id := NULL;
1207
1208 IF l_trimmed_xml IS NOT NULL
1209 THEN
1210 BEGIN
1211 l_xml_data := XMLTYPE(l_trimmed_xml);
1212 EXCEPTION
1213 WHEN OTHERS
1214 THEN
1215 RAISE EGO_ORC_XML_ERROR;
1216 END;
1217
1218 l_xml_null_chk := l_xml_data.extract('/XMLEntries/SyncItemPublication/ItemPublicationIdentification/AlternateIdentification/ID[@schemeName="1SYNC-MessageId"]/text()');
1219 IF l_xml_null_chk IS NULL
1220 THEN
1221 RAISE EGO_ORC_HDR_SEC_NOT_FOUND;
1222 END IF;
1223
1224 -- Step 1: Get Header Info
1225 l_message_id := l_xml_null_chk.getStringVal();
1226 l_message_date := get_date_from_xml(l_xml_data.extract('/XMLEntries/SyncItemPublication/Status/EffectiveDateTime/text()'));
1227
1228 -- Step 2: Get Bundle Info
1229 FOR l_xml_CplxItem IN c_bundles(l_xml_data)
1230 LOOP
1231 BEGIN
1232 l_external_bundle_id := '-1';
1233
1234 l_xml_null_chk := l_xml_CplxItem.bundles.extract('/ItemPublicationLine/ItemPublicationLineBase/ProcessingCode[@listAgencyName="1SYNC"]/text()');
1235 IF (l_xml_null_chk IS NOT NULL AND l_xml_null_chk.getStringVal() = 'PUBLICATION_DELETE') --bug:6500128
1236 THEN
1237 raise EGO_ORC_DELETE_LINE;
1238 END IF;
1239
1240 l_xml_null_chk := l_xml_CplxItem.bundles.extract('/ItemPublicationLine/ItemPublicationLineIdentification/AlternateIdentification/ID[@schemeName="1SYNC-DocumentId"]/text()');
1241 IF l_xml_null_chk IS NOT NULL
1242 THEN
1243 l_external_bundle_id := substr(l_xml_null_chk.getStringVal(), 1, 255);
1244 END IF;
1245
1246 l_org_code := NULL;
1247 l_supplier_attr_level := 'ITEM_SUP_SITE';
1248 l_xml_null_chk := l_xml_CplxItem.bundles.extract('/ItemPublicationLine/ItemPublicationLineIdentification/AlternateIdentification/ContextID[@schemeName="OrganizationCode"]/text()');
1249 IF l_xml_null_chk IS NOT NULL
1250 THEN
1251 l_org_code := substr(l_xml_null_chk.getStringVal(), 1, 80);
1252 l_supplier_attr_level := 'ITEM_SUP_SITE_ORG';
1253 END IF;
1254
1255 l_xml_batch_name := l_xml_CplxItem.bundles.extract('/ItemPublicationLine/Item/AttributeGroup[ID = "EGO_ORCH_INT"][Attribute[ID = "TopItem"]/Value = "Y"]/Attribute[ID = "BatchName"]/Value/text()');
1256
1257 l_batch_name := NULL;
1258 l_MessageTag := NULL;
1259 l_batch_id := 0;
1260 l_error_msg := NULL;
1261
1262 IF l_xml_batch_name IS NOT NULL
1263 THEN
1264 l_batch_name := l_xml_batch_name.getStringVal();
1265 ELSE
1266 l_batch_name := p_default_batch;
1267 END IF;
1268
1269 validate_batch(p_batch_name => l_batch_name ,
1270 p_default_batch_name => p_default_batch ,
1271 x_batch_id => l_batch_id,
1272 x_error_msg => l_error_msg);
1273
1274 l_bundle_id := GET_NEXT_ID();
1275
1276 -- Step 3: Iterate over Items
1277 FOR l_xml_ItemEBO IN c_items(l_xml_CplxItem.bundles)
1278 LOOP
1279 l_xml_null_chk := l_xml_ItemEBO.items.extract('/Item/ItemIdentification/GTIN/text()');
1280 IF l_xml_null_chk IS NULL
1281 THEN
1282 RAISE EGO_ORC_NO_GTIN;
1283 END IF;
1284
1285 l_source_sys_reference := l_xml_null_chk.getStringVal();
1286 l_transaction_id := GET_NEXT_ID();
1287
1288 -- Step 4: Insert Data into MTL_SYSTEM_ITEMS_INTERFACE
1289 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE(
1290 message_timestamp,
1291 transaction_id,
1292 top_item_flag,
1293 bundle_id,
1294 set_process_id,
1295 source_system_id,
1296 source_system_reference,
1297
1298 item_number,
1299 organization_code,
1300
1301 serial_status_enabled,
1302 lot_status_enabled,
1303 service_item_flag,
1304 --type_code
1305 dual_uom_control,
1306 primary_uom_code,
1307 --storage_uom_code,
1308 --shipping_uom_code,
1309 --UOM_conversion_usage_code
1310 secondary_uom_code,
1311 description,
1312
1313
1314 unit_volume,
1315 volume_uom_code,
1316 unit_weight,
1317 weight_uom_code,
1318 dimension_uom_code,
1319 unit_length,
1320 unit_width,
1321 unit_height,
1322
1323 cycle_count_enabled_flag,
1324 --lot_expiration_on_receipt,
1325 lot_merge_enabled,
1326 lot_split_enabled,
1327 --reservation_allowed_flag,
1328 --serialization_event_code,
1329 shelf_life_days,
1330 --revision_control_flag,
1331 stock_enabled_flag,
1332 auto_lot_alpha_prefix,
1333 --auto_lot_suffix,
1334 auto_serial_alpha_prefix,
1335 --auto_serial_suffix,
1336
1337 --debit_gl_account_code,
1338 asset_creation_code,
1339 purchasing_enabled_flag,
1340 receipt_required_flag,
1341 must_use_approved_vendor_flag,
1342 allow_substitute_receipts_flag,
1343 allow_unordered_receipts_flag,
1344 rfq_required_flag,
1345 taxable_flag,
1346 hazard_class_id,
1347 tax_code,
1348 --issue_uom_code,
1349 --list_price_per_unit_amount,
1350 list_price_per_unit,
1351 under_shipment_tolerance,
1352 over_shipment_tolerance,
1353 --receipt_duration_tolerance,
1354
1355 --manufactured_item_indicator,
1356 consigned_flag,
1357 inventory_planning_code,
1358 --reorder_max_inv_duration,
1359 --reorder_min_inv_duration,
1360 --reorder_max_inv_quantity,
1361 --reorder_min_inv_quantity,
1362 --reorder_quantity,
1363 min_minmax_quantity,
1364 max_minmax_quantity,
1365 minimum_order_quantity,
1366 shrinkage_rate,
1367
1368 bom_item_type,
1369 config_model_type,
1370 effectivity_control,
1371 wip_supply_type,
1372 eng_item_flag,
1373 bom_enabled_flag,
1374 costing_enabled_flag,
1375 inventory_asset_flag,
1376 std_lot_size,
1377 back_orderable_flag,
1378 returnable_flag,
1379 --assemble_to_order_flag,
1380
1381 gpc_code,
1382 trade_item_descriptor,
1383
1384 CREATED_BY,
1385 CREATION_DATE,
1386 LAST_UPDATED_BY,
1387 LAST_UPDATE_DATE,
1388 LAST_UPDATE_LOGIN
1389 ) VALUES(
1390 l_message_date,
1391 l_transaction_id,
1392 NVL(l_xml_ItemEBO.items.extract('/Item/AttributeGroup[ID = "EGO_ORCH_INT"]/Attribute[ID ="TopItem"]/Value/text()').getStringVal(), 'N'),
1393 l_bundle_id,
1394 l_batch_id,
1395 p_source_sys_id,
1396 l_source_sys_reference,
1397
1398 l_xml_ItemEBO.items.extract('/Item/ItemIdentification/Identification/text()').getStringVal(),
1399 l_org_code,
1400
1401 Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/SerialControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1402 Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/LotControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1403 Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/ServiceIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1404 --l_xml_ItemEBO.items.extract('/Item/ItemBase/TypeCode/text()').getStringVal(),
1405 Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/DualUOMTrackingIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
1406 -- UOM to be set during import
1407 null,--l_xml_ItemEBO.items.extract('/Item/ItemBase/BaseUOMCode/text()').getStringVal(),
1408 --l_xml_ItemEBO.items.extract('/Item/ItemBase/StorageUOMCode/text()').getStringVal(),
1409 --l_xml_ItemEBO.items.extract('/Item/ItemBase/ShippingUOMCode/text()').getStringVal(),
1410 --l_xml_ItemEBO.items.extract('/Item/ItemBase/UOMConversionUsageCode/text()').getStringVal(),
1411 null,--l_xml_ItemEBO.items.extract('/Item/ItemBase/SecondaryUOMCode/text()').getStringVal(),
1412 l_xml_ItemEBO.items.extract('/Item/ItemBase/Description[position() = 1]/text()').getStringVal(),
1413
1414 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitVolumeMeasure/text()').getStringVal(),
1415 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitVolumeMeasure/@unitCode').getStringVal(),
1416 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitWeightMeasure/text()').getStringVal(),
1417 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitWeightMeasure/@unitCode').getStringVal(),
1418 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/LengthMeasure/@unitCode').getStringVal(),
1419 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/LengthMeasure/text()').getStringVal(),
1420 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/WidthMeasure/text()').getStringVal(),
1421 l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/HeightMeasure/text()').getStringVal(),
1422
1423 Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/CycleCountEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1424 --Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotExpirationOnReceiptIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1425 Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotMergeEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1426 Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotSplitEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1427 --Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/ReservationAllowedIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1428 --l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/SerializationEventCode/text()').getStringVal(),
1429 l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/ShelfLifeDuration/text()').getStringVal(),
1430 --Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/RevisionControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1431 Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/StockingAllowedIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1432 l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialLotNumberPrefix/text()').getStringVal(),
1433 --l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialLotNumberSuffix/text()').getStringVal(),
1434 l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialSerialNumberPrefix/text()').getStringVal(),
1435 --l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialSerialNumberSuffix/text()').getStringVal(),
1436
1437 --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/DebitGLAccountCode/text()').getStringVal(),
1438 l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AssetClassificationCode/text()').getStringVal(),
1439 Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/PurchasableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1440 Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/ReceiptRequiredIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1441 Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UseApprovedSupplierIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1442 Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AllowReceiptSubstitutionIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1443 Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AllowUnorderedReceiptIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1444 Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/RFQRequiredIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1445 Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/TaxableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1446 l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/HazardClassificationCode/text()').getStringVal(),
1447 l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/TaxCode/text()').getStringVal(),
1448 --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/IssueUOMCode/text()').getStringVal(),
1449 --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UnitListPrice/Amount/text()').getStringVal(),
1450 l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UnitListPrice/PerQuantity/text()').getStringVal(),
1451 l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/OverReceiptTolerancePercent/UnderDuration/text()').getStringVal(),
1452 l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/OverReceiptTolerancePercent/OverDuration/text()').getStringVal(),
1453 --l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/ReceiptDurationTolerance/text()').getStringVal(),
1454
1455 --Decode(l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ManufacturedItemIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
1456 Decode(l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ConsignmentItemIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
1457 l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/InventoryPlanningCode/text()').getStringVal(),
1458 --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MaximumInventoryDuration/text()').getStringVal(),
1459 --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MinimumInventoryDuration/text()').getStringVal(),
1460 --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MaximumReorderQuantity/text()').getStringVal(),
1461 --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MinimumReorderQuantity/text()').getStringVal(),
1462 --l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/ReorderQuantity/text()').getStringVal(),
1463 l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinMaxSetup/MinimumInventoryQuantity/text()').getStringVal(),
1464 l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinMaxSetup/MaximumInventoryQuantity/text()').getStringVal(),
1465 l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinimumProductionOrderQuantity/text()').getStringVal(),
1466 l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ShrinkageRate/text()').getStringVal(),
1467
1468 l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/BOMItemTypeCode/text()').getStringVal(),
1469 l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/ConfiguratorModelTypeCode/text()').getStringVal(),
1470 l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/EffectivityControlCode/text()').getStringVal(),
1471 l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/WIPSupplyTypeCode/text()').getStringVal(),
1472 Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/EngineeringItemIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1473 Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/AllowStructureIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1474 Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/CostingEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1475 Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/InventoryAssetIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1476 l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/StandardLotSizeQuantity/text()').getStringVal(),
1477
1478 Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/BackOrderEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1479 Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/ReturnableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1480 --Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/AssembleToOrderIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
1481
1482 l_xml_ItemEBO.items.extract('/Item/ItemCatalog[PrimaryIndicator = "true"]/CatalogReference/CatalogIdentification/Identification/ID[@schemeName = "GPC"]/text()').getStringVal(),
1483 -- Expecting the trade_item_descriptor as the BaseUOM
1484 l_xml_ItemEBO.items.extract('/Item/ItemBase/BaseUOMCode/text()').getStringVal(),
1485
1486 l_created_by,
1487 l_creation_date,
1488 l_last_updated_by,
1489 l_last_update_date,
1490 l_last_update_login
1491 );
1492
1493 -- Step 5: Insert Data for translatable entries for MTL_SYSTEM_ITEMS_INTERFACE
1494
1495 SELECT XMLELEMENT("TL", l_xml_ItemEBO.items.extract('/Item/ItemIdentification/Name'))
1496 INTO l_xml_trans
1497 FROM DUAL;
1498
1499 IF l_xml_trans IS NOT NULL
1500 THEN
1501 PROCESS_TL_ROWS(p_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE',
1502 p_batch_id => l_batch_id,
1503 p_unique_id => l_transaction_id,
1504 p_bundle_id => l_bundle_id,
1505 p_xml_data => l_xml_trans,
1506 p_entity_name => 'Name',
1507 p_column_name => 'ItemNumber');
1508 END IF;
1509
1510 SELECT XMLELEMENT("TL", l_xml_ItemEBO.items.extract('/Item/ItemBase/Description'))
1511 INTO l_xml_trans
1512 FROM DUAL;
1513
1514 IF l_xml_trans IS NOT NULL
1515 THEN
1516 PROCESS_TL_ROWS(p_table_name => 'MTL_SYSTEM_ITEMS_INTERFACE',
1517 p_batch_id => l_batch_id,
1518 p_unique_id => l_transaction_id,
1519 p_bundle_id => l_bundle_id,
1520 p_xml_data => l_xml_trans,
1521 p_entity_name => 'Description',
1522 p_column_name => 'Description');
1523 END IF;
1524
1525 INSERT INTO EGO_UCCNET_EVENTS (
1526 source_system_id,
1527 source_system_reference,
1528 message_id,
1529 import_batch_id,
1530 ext_complex_item_reference,
1531 batch_id,
1532 event_row_id,
1533 event_type,
1534 event_action,
1535 gtin,
1536 supplier_gln,
1537 target_market,
1538 cln_id,
1539 disposition_code,
1540 disposition_date,
1541
1542 CREATED_BY,
1543 CREATION_DATE,
1544 LAST_UPDATED_BY,
1545 LAST_UPDATE_DATE,
1546 LAST_UPDATE_LOGIN
1547
1548 ) VALUES (
1549 p_source_sys_id,
1550 l_source_sys_reference,
1551 l_message_id,
1552 l_batch_id,
1553 l_external_bundle_id, -- External Bundle Id
1554 -1,
1555 l_transaction_id, -- PDUTTA:IDentify seq
1556 'PUBLICATION_INBOUND',
1557 'NEW_ITEM', -- Action or NEW_ITEM
1558 l_source_sys_reference,
1559 '-1', -- Supplier GLN
1560 '-1', -- Tgt Mgt
1561 l_bundle_id,
1562 EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_ACCEPTED_MESSAGE_TYPE,
1563 sysdate,
1564
1565 l_created_by,
1566 l_creation_date,
1567 l_last_updated_by,
1568 l_last_update_date,
1569 l_last_update_login
1570
1571 );
1572
1573 -- Step 6: Insert Data for classifications/categories
1574 FOR l_xml_Classification IN c_classifications(l_xml_ItemEBO.items)
1575 LOOP
1576 --bug:6485109 Insert concatenated segments category name
1577 FOR l_alt_cat_conc_seg_rec IN c_alt_cat_concat_seg ( l_xml_Classification.classifications.extract('/ItemCatalog/CatalogReference/CatalogIdentification/Identification/ID/text()').getStringVal() )
1578 LOOP
1579 l_alt_cat_concat_seg := l_alt_cat_conc_seg_rec.concatenated_segments;
1580 END LOOP;
1581
1582 INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE(
1583 transaction_id,
1584 category_set_name,
1585 category_name,
1586 source_system_id,
1587 source_system_reference,
1588 bundle_id,
1589 set_process_id,
1590
1591 CREATED_BY,
1592 CREATION_DATE,
1593 LAST_UPDATED_BY,
1594 LAST_UPDATE_DATE,
1595 LAST_UPDATE_LOGIN
1596 ) VALUES (
1597 l_transaction_id,
1598 l_xml_Classification.classifications.extract('/ItemCatalog/CatalogReference/CatalogIdentification/Identification/ID/@schemeName').getStringVal(),
1599 l_alt_cat_concat_seg,
1600 p_source_sys_id,
1601 l_source_sys_reference,
1602 l_bundle_id,
1603 l_batch_id,
1604
1605 l_created_by,
1606 l_creation_date,
1607 l_last_updated_by,
1608 l_last_update_date,
1609 l_last_update_login
1610 );
1611 END LOOP; -- Loop over Classifications
1612
1613 -- Step 7: Insert Data for Suppliers
1614 FOR l_xml_Supplier IN c_suppliers(l_xml_ItemEBO.items)
1615 LOOP
1616 BEGIN
1617 l_xml_null_chk := l_xml_Supplier.suppliers.extract('/ItemSupplier/SupplierPartyReference/PartyIdentification/AlternateIdentification/ID[@schemeName = "GLN"]/text()');
1618 IF l_xml_null_chk IS NULL
1619 THEN
1620 RAISE EGO_ORC_NO_GLN;
1621 END IF;
1622
1623
1624 GET_SUPPLIER_INFO ( X_EXT_SUP_ID => l_xml_null_chk.getStringVal(),
1625 X_EXT_SUP_TYPE => 'GLN',
1626 X_SUP_LEVEL => 'SUPPLIER',
1627 X_SUPPLIER_ID => l_supplier_id,
1628 X_SUPPLIER_NAME => l_supplier_name
1629 );
1630
1631 IF l_supplier_id IS NULL
1632 THEN
1633 RAISE EGO_ORC_INVALID_GLN;
1634 ELSE
1635 INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
1636 transaction_id,
1637 batch_id,
1638 source_system_id,
1639 source_system_reference,
1640 bundle_id,
1641
1642 pk1_value,
1643 supplier_name,
1644 supplier_number,
1645 --supplier_site_name,
1646 data_level_name,
1647
1648 CREATED_BY,
1649 CREATION_DATE,
1650 LAST_UPDATED_BY,
1651 LAST_UPDATE_DATE,
1652 LAST_UPDATE_LOGIN,
1653 transaction_type
1654 ) VALUES(
1655 l_transaction_id,
1656 l_batch_id,
1657 p_source_sys_id,
1658 l_source_sys_reference,
1659 l_bundle_id,
1660
1661 l_supplier_id,
1662 l_supplier_name,
1663 l_supplier_id,
1664 --l_supplier_site_name,
1665 'ITEM_SUP',
1666
1667 l_created_by,
1668 l_creation_date,
1669 l_last_updated_by,
1670 l_last_update_date,
1671 l_last_update_login,
1672 'SYNC'
1673 );
1674 -- Step 8: Insert Data for Supplier Attributes
1675 SAVE_ATTR_DATA( p_xml => l_xml_Supplier.suppliers,
1676 p_entity_name => 'ItemSupplier',
1677 p_transaction_id => l_transaction_id,
1678 p_bundle_id => l_bundle_id,
1679 p_source_system_id => p_source_sys_id,
1680 p_source_system_reference => l_source_sys_reference,
1681 p_organization_code => l_org_code,
1682 p_data_set_id => l_batch_id,
1683 p_data_level_name => 'ITEM_SUP',
1684 p_pk1_value => l_supplier_id,
1685 p_pk2_value => NULL,
1686 p_created_by => l_created_by,
1687 p_creation_date => l_creation_date,
1688 p_last_updated_by => l_last_updated_by,
1689 p_last_update_date => l_last_update_date,
1690 p_last_update_login => l_last_update_login);
1691
1692 -- Step 9: Insert Data for Supplier Site Attributes
1693 FOR l_xml_SupplierSite IN c_supplierLocations(l_xml_Supplier.suppliers)
1694 LOOP
1695 l_xml_null_chk := l_xml_SupplierSite.supplierLocations.extract('/ItemSupplierLocation/LocationReference/LocationIdentification/AlternateIdentification/ID[@schemeName = "GLN"]/text()');
1696 IF l_xml_null_chk IS NULL
1697 THEN
1698 RAISE EGO_ORC_NO_GLN;
1699 END IF;
1700 GET_SUPPLIER_INFO ( X_EXT_SUP_ID => l_xml_null_chk.getStringVal(),
1701 X_EXT_SUP_TYPE => 'GLN',
1702 X_SUP_LEVEL => 'SITE',
1703 X_SUPPLIER_ID => l_supplier_site_id,
1704 X_SUPPLIER_NAME => l_supplier_name
1705 );
1706 IF l_supplier_id IS NULL
1707 THEN
1708 RAISE EGO_ORC_INVALID_GLN;
1709 ELSE
1710
1711 -- bug:6485109 Insert a row for item supplier site for item supplier site org
1712 IF ( l_supplier_attr_level = 'ITEM_SUP_SITE_ORG')
1713 THEN
1714 INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
1715 transaction_id,
1716 batch_id,
1717 source_system_id,
1718 source_system_reference,
1719 bundle_id,
1720
1721 pk1_value,
1722 pk2_value,
1723 supplier_name,
1724 supplier_number,
1725 supplier_site_name,
1726 data_level_name,
1727
1728 CREATED_BY,
1729 CREATION_DATE,
1730 LAST_UPDATED_BY,
1731 LAST_UPDATE_DATE,
1732 LAST_UPDATE_LOGIN,
1733 transaction_type
1734 ) VALUES(
1735 l_transaction_id,
1736 l_batch_id,
1737 p_source_sys_id,
1738 l_source_sys_reference,
1739 l_bundle_id,
1740
1741 l_supplier_id,
1742 l_supplier_site_id,
1743 l_supplier_name,
1744 l_supplier_site_id,
1745 l_xml_SupplierSite.supplierLocations.extract('/ItemSupplierLocation/LocationReference/Name/text()').getStringVal(),
1746 'ITEM_SUP_SITE',
1747
1748 l_created_by,
1749 l_creation_date,
1750 l_last_updated_by,
1751 l_last_update_date,
1752 l_last_update_login,
1753 'SYNC'
1754 );
1755
1756 END IF;
1757
1758 INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
1759 transaction_id,
1760 batch_id,
1761 source_system_id,
1762 source_system_reference,
1763 bundle_id,
1764 organization_code,
1765
1766 pk1_value,
1767 pk2_value,
1768 supplier_name,
1769 supplier_number,
1770 supplier_site_name,
1771 data_level_name,
1772
1773 CREATED_BY,
1774 CREATION_DATE,
1775 LAST_UPDATED_BY,
1776 LAST_UPDATE_DATE,
1777 LAST_UPDATE_LOGIN,
1778 transaction_type
1779 ) VALUES(
1780 l_transaction_id,
1781 l_batch_id,
1782 p_source_sys_id,
1783 l_source_sys_reference,
1784 l_bundle_id,
1785 l_org_code,
1786
1787 l_supplier_id,
1788 l_supplier_site_id,
1789 l_supplier_name,
1790 l_supplier_site_id,
1791 l_xml_SupplierSite.supplierLocations.extract('/ItemSupplierLocation/LocationReference/Name/text()').getStringVal(),
1792 l_supplier_attr_level,
1793
1794 l_created_by,
1795 l_creation_date,
1796 l_last_updated_by,
1797 l_last_update_date,
1798 l_last_update_login,
1799 'SYNC'
1800 );
1801 -- Step 10: Insert Data for Supplier Site Attributes
1802 SAVE_ATTR_DATA( p_xml => l_xml_SupplierSite.supplierLocations,
1803 p_entity_name => 'ItemSupplierLocation',
1804 p_transaction_id => l_transaction_id,
1805 p_bundle_id => l_bundle_id,
1806 p_source_system_id => p_source_sys_id,
1807 p_source_system_reference => l_source_sys_reference,
1808 p_organization_code => l_org_code,
1809 p_data_set_id => l_batch_id,
1810 p_data_level_name => l_supplier_attr_level,
1811 p_pk1_value => l_supplier_id,
1812 p_pk2_value => l_supplier_site_id,
1813 p_created_by => l_created_by,
1814 p_creation_date => l_creation_date,
1815 p_last_updated_by => l_last_updated_by,
1816 p_last_update_date => l_last_update_date,
1817 p_last_update_login => l_last_update_login);
1818 END IF;
1819 END LOOP; -- Loop over Supplier Sites
1820 END IF;
1821 EXCEPTION
1822 WHEN EGO_ORC_NO_GLN
1823 THEN
1824 l_MessageTag := l_MessageTag || 'EGO_ORC_NO_GLN ';
1825 WHEN EGO_ORC_INVALID_GLN
1826 THEN
1827 l_MessageTag := l_MessageTag || 'EGO_ORC_INVALID_GLN';
1828 END;
1829 END LOOP; -- Loop over Suppliers
1830 -- Step 11: Insert Data for Item Attributes
1831 SAVE_ATTR_DATA( p_xml => l_xml_ItemEBO.items,
1832 p_entity_name => 'Item',
1833 p_transaction_id => l_transaction_id,
1834 p_bundle_id => l_bundle_id,
1835 p_source_system_id => p_source_sys_id,
1836 p_source_system_reference => l_source_sys_reference,
1837 p_organization_code => l_org_code,
1838 p_data_set_id => l_batch_id,
1839 p_data_level_name => 'ITEM_LEVEL',
1840 p_pk1_value => null,
1841 p_pk2_value => null,
1842 p_created_by => l_created_by,
1843 p_creation_date => l_creation_date,
1844 p_last_updated_by => l_last_updated_by,
1845 p_last_update_date => l_last_update_date,
1846 p_last_update_login => l_last_update_login);
1847 END LOOP; -- Loop over Items
1848
1849 FOR l_xml_structure IN c_structure(l_xml_CplxItem.bundles)
1850 LOOP
1851 BEGIN
1852 l_xml_null_chk := l_xml_structure.structures.extract('/ItemStructure/ItemReference/ItemIdentification/GTIN/text()');
1853 IF l_xml_null_chk IS NULL
1854 THEN
1855 RAISE EGO_ORC_NO_STRUCTURE;
1856 END IF;
1857 l_hdr_source_sys_reference := l_xml_null_chk.getStringVal();
1858
1859 INSERT INTO BOM_BILL_OF_MTLS_INTERFACE(
1860 transaction_id,
1861 batch_id,
1862 --source_system_id,
1863 source_system_reference,
1864 bundle_id,
1865 alternate_bom_designator,
1866 organization_code,
1867
1868 CREATED_BY,
1869 CREATION_DATE,
1870 LAST_UPDATED_BY,
1871 LAST_UPDATE_DATE,
1872 LAST_UPDATE_LOGIN
1873 ) VALUES(
1874 NULL,--l_transaction_id,
1875 l_batch_id,
1876 --p_source_sys_id,
1877 l_hdr_source_sys_reference,
1878 l_bundle_id,
1879 'PIM_PBOM_S',
1880 l_org_code,
1881
1882 l_created_by,
1883 l_creation_date,
1884 l_last_updated_by,
1885 l_last_update_date,
1886 l_last_update_login
1887 );
1888
1889 FOR l_xml_component IN c_component(l_xml_structure.structures)
1890 LOOP
1891 INSERT INTO BOM_INVENTORY_COMPS_INTERFACE(
1892 transaction_id,
1893 batch_id,
1894 --source_system_id,
1895 parent_source_system_reference,
1896 comp_source_system_reference,
1897 bundle_id,
1898 alternate_bom_designator,
1899 organization_code,
1900 component_quantity,
1901 primary_unit_of_measure,
1902
1903 CREATED_BY,
1904 CREATION_DATE,
1905 LAST_UPDATED_BY,
1906 LAST_UPDATE_DATE,
1907 LAST_UPDATE_LOGIN
1908 ) VALUES(
1909 NULL,--l_transaction_id,
1910 l_batch_id,
1911 --p_source_sys_id,
1912 l_hdr_source_sys_reference,
1913 l_xml_component.components.extract('/ComponentItem/ItemReference/ItemIdentification/GTIN/text()').getStringVal(),
1914 l_bundle_id,
1915 'PIM_PBOM_S',
1916 l_org_code,
1917 l_xml_component.components.extract('/ComponentItem/ComponentItemBase/Quantity/text()').getStringVal(),
1918 l_xml_component.components.extract('/ComponentItem/ComponentItemBase/Quantity/@unitCode').getStringVal(),
1919
1920 l_created_by,
1921 l_creation_date,
1922 l_last_updated_by,
1923 l_last_update_date,
1924 l_last_update_login
1925 );
1926
1927 END LOOP; -- Loop over Components
1928 EXCEPTION
1929 WHEN EGO_ORC_NO_STRUCTURE
1930 THEN
1931 l_MessageTag := l_MessageTag || 'EGO_ORC_NO_STRUCTURE ';
1932 END;
1933 END LOOP; -- Loop over ItemStructures
1934
1935 IF l_xml_batch_name IS NULL
1936 THEN
1937 RAISE EGO_ORC_NO_BATCH;
1938 END IF;
1939
1940 IF l_error_msg <> 'SUCCESS' AND l_batch_id = 0
1941 THEN
1942 RAISE EGO_MSG_ERROR;
1943 END IF;
1944
1945
1946 IF l_reg_bundle = FALSE
1947 THEN
1948 l_reg_bundle := TRUE;
1949 l_bundles_clob := l_BundlesStartTag;
1950 END IF;
1951
1952 dbms_lob.writeappend(l_bundles_clob, length(l_BundleStartTag), l_BundleStartTag);
1953 dbms_lob.writeappend(l_bundles_clob, length(l_BundleIdStartTag), l_BundleIdStartTag);
1954 dbms_lob.writeappend(l_bundles_clob, length(l_bundle_id), l_bundle_id);
1955 dbms_lob.writeappend(l_bundles_clob, length(l_BundleIdEndTag), l_BundleIdEndTag);
1956 IF l_MessageTag IS NOT NULL
1957 THEN
1958 l_MessageTag := '<Message type="Warning">'||l_MessageTag||'</Message>';
1959 dbms_lob.writeappend(l_bundles_clob, length(l_MessageTag), l_MessageTag);
1960 END IF;
1961 dbms_lob.writeappend(l_bundles_clob, length(l_BundleEndTag), l_BundleEndTag);
1962
1963 EXCEPTION
1964 WHEN EGO_ORC_DELETE_LINE --Ignore Delete messages
1965 THEN
1966 NULL;
1967
1968 WHEN EGO_ORC_NO_BATCH
1969 THEN
1970 IF l_err_bundle = FALSE
1971 THEN
1972 l_err_bundle := TRUE;
1973 l_err_bundles_clob := l_BundlesStartTag;
1974 END IF;
1975 l_MessageTag := '<Message type="Error">EGO_ORC_NO_BATCH</Message>';
1976
1977 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleStartTag), l_BundleStartTag);
1978 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdStartTag), l_BundleIdStartTag);
1979 dbms_lob.writeappend(l_err_bundles_clob, length(l_bundle_id), l_bundle_id);
1980 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdEndTag), l_BundleIdEndTag);
1981 dbms_lob.writeappend(l_err_bundles_clob, length(l_MessageTag), l_MessageTag);
1982 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleEndTag), l_BundleEndTag);
1983 WHEN EGO_ORC_NO_GTIN
1984 THEN
1985 IF l_err_bundle = FALSE
1986 THEN
1987 l_err_bundle := TRUE;
1988 l_err_bundles_clob := l_BundlesStartTag;
1989 END IF;
1990 l_MessageTag := '<Message type="Error">EGO_ORC_NO_GTIN</Message>';
1991
1992 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleStartTag), l_BundleStartTag);
1993 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdStartTag), l_BundleIdStartTag);
1994 dbms_lob.writeappend(l_err_bundles_clob, length(l_bundle_id), l_bundle_id);
1995 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdEndTag), l_BundleIdEndTag);
1996 dbms_lob.writeappend(l_err_bundles_clob, length(l_MessageTag), l_MessageTag);
1997 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleEndTag), l_BundleEndTag);
1998
1999 WHEN EGO_MSG_ERROR
2000 THEN
2001 IF l_err_bundle = FALSE
2002 THEN
2003 l_err_bundle := TRUE;
2004 l_err_bundles_clob := l_BundlesStartTag;
2005 END IF;
2006 l_MessageTag := '<Message type="Error">'||l_error_msg||'</Message>';
2007
2008 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleStartTag), l_BundleStartTag);
2009 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdStartTag), l_BundleIdStartTag);
2010 dbms_lob.writeappend(l_err_bundles_clob, length(l_bundle_id), l_bundle_id);
2011 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdEndTag), l_BundleIdEndTag);
2012 dbms_lob.writeappend(l_err_bundles_clob, length(l_MessageTag), l_MessageTag);
2013 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleEndTag), l_BundleEndTag);
2014 END;
2015 END LOOP; -- Loop over Bundles
2016
2017 IF l_reg_bundle = TRUE
2018 THEN
2019 dbms_lob.writeappend(l_bundles_clob, length(l_BundlesEndTag), l_BundlesEndTag);
2020 ADD_BUNDLES_TO_COL (x_bundle_collection_id => -1,
2021 p_bundles_clob => l_bundles_clob,
2022 x_new_bundle_col_id => x_new_bundle_col_id,
2023 p_commit => p_commit
2024 );
2025 END IF;
2026
2027 IF l_err_bundle = TRUE
2028 THEN
2029 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundlesEndTag), l_BundlesEndTag);
2030 ADD_BUNDLES_TO_COL (x_bundle_collection_id => -1,
2031 p_bundles_clob => l_err_bundles_clob,
2032 x_new_bundle_col_id => x_err_bundle_col_id,
2033 p_commit => p_commit
2034 );
2035 END IF;
2036
2037
2038 END IF; -- Input XML Check
2039 EXCEPTION
2040 WHEN EGO_ORC_HDR_SEC_NOT_FOUND
2041 THEN
2042 l_err_bundles_clob := l_BundlesStartTag;
2043 l_MessageTag := '<Message>EGO_ORC_HDR_SEC_NOT_FOUND</Message>';
2044 l_bundle_id := GET_NEXT_ID();
2045 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleStartTag), l_BundleStartTag);
2046 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdStartTag), l_BundleIdStartTag);
2047 dbms_lob.writeappend(l_err_bundles_clob, length(l_bundle_id), l_bundle_id);
2048 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdEndTag), l_BundleIdEndTag);
2049 dbms_lob.writeappend(l_err_bundles_clob, length(l_MessageTag), l_MessageTag);
2050 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleEndTag), l_BundleEndTag);
2051 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundlesEndTag), l_BundlesEndTag);
2052 ADD_BUNDLES_TO_COL (x_bundle_collection_id => -1,
2053 p_bundles_clob => l_err_bundles_clob,
2054 x_new_bundle_col_id => x_err_bundle_col_id,
2055 p_commit => p_commit
2056 );
2057
2058 WHEN EGO_ORC_XML_ERROR
2059 THEN
2060 l_err_bundles_clob := l_BundlesStartTag;
2061 l_MessageTag := '<Message>EGO_ORC_XML_ERROR</Message>';
2062 l_bundle_id := GET_NEXT_ID();
2063 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleStartTag), l_BundleStartTag);
2064 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdStartTag), l_BundleIdStartTag);
2065 dbms_lob.writeappend(l_err_bundles_clob, length(l_bundle_id), l_bundle_id);
2066 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleIdEndTag), l_BundleIdEndTag);
2067 dbms_lob.writeappend(l_err_bundles_clob, length(l_MessageTag), l_MessageTag);
2068 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundleEndTag), l_BundleEndTag);
2069 dbms_lob.writeappend(l_err_bundles_clob, length(l_BundlesEndTag), l_BundlesEndTag);
2070 ADD_BUNDLES_TO_COL (x_bundle_collection_id => -1,
2071 p_bundles_clob => l_err_bundles_clob,
2072 x_new_bundle_col_id => x_err_bundle_col_id,
2073 p_commit => p_commit
2074 );
2075
2076
2077 END SAVE_DATA;
2078
2079 END EGO_ORCHESTRATION_UTIL_PUB;
2080
2081