[Home] [Help]
PACKAGE BODY: APPS.EGO_PUBLICATION_PKG
Source
1 PACKAGE BODY EGO_PUBLICATION_PKG AS
2 /* $Header: EGOPBLCB.pls 120.14 2007/10/11 12:54:32 bramnan noship $ */
3
4 PROCEDURE getUDAAttributes
5 (
6 extension_id IN NUMBER,
7 p_language IN VARCHAR2,
8 x_doc OUT NOCOPY xmltype,
9 x_error_message OUT NOCOPY VARCHAR2
10 )
11 IS
12
13 -- language_clause varchar2(2000);
14 ext_id NUMBER;
15 x_query varchar2(4000);
16 no_extId_exception EXCEPTION;
17 x_temp xmltype;
18
19 CURSOR C1(p_ext_id NUMBER,p_lang VARCHAR2) IS
20 Select xmlElement("Attribute",xmlElement("Id",attribute_id),
21 xmlElement("Name",attribute_name),
22 xmlAgg(xmlelement("ValueText",xmlattributes(language as "languageID"),attribute_translatable_value)))
23 FROM EGO_ALL_ATTR_LANG_V
24 WHERE extension_id =p_ext_id AND ATTRIBUTE_TRANSLATABLE_VALUE IS NOT NULL
25 AND LANGUAGE = p_lang GROUP BY ATTRIBUTE_ID, ATTRIBUTE_NAME;
26
27
28 CURSOR C2(p_ext_id NUMBER) IS
29 Select xmlElement("Attribute",xmlElement("Id",attribute_id),
30 xmlElement("Name",attribute_name),
31 xmlAgg(xmlelement("ValueText",xmlattributes(language as "languageID"),attribute_translatable_value)))
32 FROM EGO_ALL_ATTR_LANG_V
33 WHERE extension_id =p_ext_id AND ATTRIBUTE_TRANSLATABLE_VALUE IS NOT NULL
34 GROUP BY ATTRIBUTE_ID, ATTRIBUTE_NAME;
35
36 CURSOR C3(p_ext_id NUMBER) IS
37 SELECT xmlConcat(xmlElement("Id",attributegroup_id),
38 xmlElement("Name", attribute_group_name),
39 xmlagg(xmlelement("Attribute",
40 xmlelement("Id",attribute_id),
41 xmlelement("Name",attribute_name),
42 xmlForest(attribute_char_value AS "Value",
43 attribute_number_value AS "ValueNumeric",
44 attribute_uom_value AS "ValueQuantity",
45 attribute_date_value AS "ValueDate",
46 attribute_datetime_value AS "ValueDateTime")
47 )))
48 FROM EGO_ALL_ATTR_BASE_V
49 WHERE extension_id = p_ext_id
50 GROUP BY attribute_group_name,attributegroup_id ;
51
52 ---------------- SAMPLE XML ----------------------------------------------
53 ---------------- <AttributeGroup> --------------------------------------
54 ---------------- <ID>1234</ID> -------------------------------------------
55 ---------------- <NAME>INTERNAL_NAME_AG1</NAME> --------------------------
56 ---------------- Numeric Attribute ---------------------------------------
57 ---------------- <ATTRIBUTE> ---------------------------------------------
58 ---------------- <ID>54544</ID> ------------------------------------------
59 ---------------- <NAME>Attr1</NAME> --------------------------------------
60 ---------------- <VALUENUMERIC>123</VALUENUMERIC> ------------------------
61 ---------------- <VALUEQUANTITY>UOMVALUE </VALUEQUANTITY> ----------------
62 ---------------- </ATTRIBUTE> --------------------------------------------
63 ---------------- <ATTRIBUTE> --------------------------------------------
64 ---------------- <ID>54545</ID> ------------------------------------------
65 ---------------- <NAME>Attr2</NAME> --------------------------------------
66 ---------------- <VALUE>San Francisco</VALUE> ----------------------------
67 ---------------- </ATTRIBUTE> -------------------------------------------
68 ---------------- Date Time Attribute ------------------------------------
69 ---------------- <ATTRIBUTE> --------------------------------------------
70 ---------------- <ID>54546</ID> ------------------------------------------
71 ---------------- <NAME>Attr3</NAME> --------------------------------------
72 --------------- <VALUEDATETIME>10-03-07:22:14:06</VALUEDATETIME> ---------
73 --------------- </ATTRIBUTE> ---------------------------------------------
74 --------------- Date Attribute -------------------------------------------
75 --------------- <ATTRIBUTE> ----------------------------------------------
76 ---------------- <ID>54547</ID> ------------------------------------------
77 --------------- <NAME>Attr3</NAME> --------------------------------------
78 --------------- <VALUEDATE>10-03-07</VALUEDATE> -------------------------
79 --------------- </ATTRIBUTE> --------------------------------------------
80 --------------- Translatable Attributes ---------------------------------
81 --------------- <ATTRIBUTE> ---------------------------------------------
82 ---------------- <ID>54548</ID> ------------------------------------------
83 --------------- <NAME>Attr4</NAME> --------------------------------------
84 --------------- <VALUETEXT LANGUAGEID = "US">LANGVALUE1</VALUETEXT> ------
85 --------------- <VALUETEXT LANGUAGEID = "KR">LANGVALUE1</VALUETEXT> ------
86 --------------- </ATTRIBUTE> ---------------------------------------------
87 --------------- </ATTRIBUTE_GROUP> ---------------------------------------
88
89
90
91 BEGIN
92
93
94 --------- Assign extension_id from input parameteres -------------------
95
96 IF extension_id IS NULL THEN
97
98 x_error_message := 'Extension_id cannot be null';
99 RAISE no_extId_exception;
100 END IF;
101
102 ext_id := extension_id;
103
104 --------- Query Lang View for Translatable Attributes -------------------------------------------------
105
106
107 IF (p_language is NOT NULL) THEN
108
109 OPEN C1(ext_id,p_language);
110 FETCH C1 INTO x_temp;
111 CLOSE C1;
112
113 ---------- Generate the Translatable Attributes for all Languages ------------------------------
114
115 ELSE
116
117 OPEN C2(ext_id);
118 FETCH C2 INTO x_temp;
119 CLOSE C2;
120
121 END IF;
122
123
124 ------------ Generate Non - Translatable Attributes ------------------------------------
125 OPEN C3(ext_id);
126 FETCH C3 INTO x_doc;
127 CLOSE C3;
128
129 ------------- Concatenate Translatable and Non- Translatable Attributes to Generate Attribute Group Element
130
131 Select xmlElement("AttributeGroup",x_doc, x_temp)
132 INTO x_doc
133 FROM DUAL;
134
135
136 EXCEPTION
137 WHEN no_data_found THEN
138 x_error_message := 'unexpected_error';
139 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
140 RETURN;
141 WHEN no_extId_exception THEN
142 x_error_message := 'USER ERROR: Extension Id cannot be Null';
143 RETURN;
144 WHEN others THEN
145 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
146 RETURN;
147
148 END getUDAAttributes;
149
150
151 PROCEDURE getItemIdentification
152 (
153 inventory_item_id IN NUMBER,
154 organization_id IN NUMBER,
155 x_doc OUT NOCOPY XMLTYPE,
156 x_error_message OUT NOCOPY VARCHAR2
157 )
158
159 IS
160 ------ Sample xml ----------------------------------------------------
161 ------ <ItemIdentification> ------------------------------------------
162 ------ <Identification> ----------------------------------------------
163 ------ <ID>155</ID> --------------------------------------------------
164 ------ <ContextID schemeID = ORGID>204 </ContextID> --------------------
165 ------ <Name>Abcd</Name> ---------------------------------------------
166 ------ </Identification> ---------------------------------------------
167 ------- </ItemIdentification> ----------------------------------------
168
169
170
171 itemId number;
172 orgId number;
173 orgIdStr varchar2(2000);
174 x_temp xmltype;
175 x_query varchar2(2000);
176 no_pk_exception EXCEPTION;
177
178 BEGIN
179
180 IF ((inventory_item_id IS NULL) OR (organization_id is NULL)) THEN
181
182 RAISE no_pk_exception;
183
184 END IF;
185
186 itemId := inventory_item_id;
187 orgId := organization_id;
188 orgIdStr := 'ORGID';
189
190
191 ---------- Generate IDENTIFICATION ELEMENT -------------------------------
192
193 SELECT
194 XMLELEMENT("ItemIdentification",
195 XMLELEMENT("Identification",
196 XMLELEMENT("ID",itemId),
197 XMLELEMENT("ContextID",XMLATTRIBUTES(orgIdStr AS "SchemeID"),orgId),
198 XMLELEMENT("Name",kfv.concatenated_segments)))
199 INTO x_doc
200 FROM mtl_system_items_vl msiv, org_organization_definitions orgdef,mtl_system_items_b_kfv kfv
201 WHERE msiv.inventory_item_id = kfv.inventory_item_id AND
202 msiv.organization_id = kfv.organization_id AND
203 msiv.organization_id = orgdef.organization_id AND
204 msiv.inventory_item_id = itemId AND
205 msiv.organization_id = orgId ;
206
207 EXCEPTION
208 WHEN no_data_found THEN
209 x_error_message := 'unexpected_error';
210 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
211 RETURN;
212 WHEN no_PK_exception THEN
213 x_error_message := 'USER ERROR: Inventory Item Id and Organization Id cannot be Null';
214 RETURN;
215 WHEN others THEN
216 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
217 RETURN;
218
219 END getItemIdentification;
220
221 PROCEDURE getItemBase
222 (
223 inventory_item_id IN NUMBER,
224 organization_id IN NUMBER,
225 p_language IN VARCHAR2,
226 x_doc OUT NOCOPY XMLTYPE,
227 x_error_message OUT NOCOPY VARCHAR2
228 )
229 IS
230
231 itemId NUMBER;
232 orgId NUMBER;
233 x_temp xmltype;
234 no_pk_exception EXCEPTION;
235 lifecycle varchar2(2000);
236 lifecycle_phase varchar2(2000);
237 -- language_clause varchar2(2000);
238 x_query varchar2(2000);
239
240 ------ Sample XML ----------------------------------------------
241 ------ <ItemBase> ---------------------------------------------
242 ------ <Description LanguageID = KR>Itm Descp</Description>---
243 ------ <Description LanguageID = US>Itm Descp</Description>---
244 ------ <LONGDESCRIPTION>Abcd is an item </LONGDESCRIPTION> -----
245 ------ <LIFECYCLE>LC1</ LIFECYCLE> -----------------------------
246 ------ <LIFECYCLE_PHASE>LC Phase 1</ LIFECYCLE_PHASE> ----------
247 ------ <APPROVAL_STATUS>Approved</APPROVAL_STATUS> -------------
248 ------ <Status>Approved</Status> ------------------------------
249 ------ <TypeCode>Engineering</TypeCode> ------------
250 ------ <EngineeringItemIndicator>Yes</EngineeringItemIndicator> ------
251 ------ <BaseUOMCode>Kgs</BaseUOMCode> --------------------------
252 ------ <SecondaryUOMCode>Lbs</SecondaryUOMCode> ----------------------
253 ------ <CREATION_DATE>01-DEC-2005</CREATION_DATE> --------------
254 ------ </ItemBase> --------------------------------------------
255
256
257 CURSOR C1 IS
258 SELECT
259 lc.element_number,lcphase.element_number
260 FROM mtl_system_items_vl itemvl,org_organization_definitions orgdef,
261 pa_ego_lifecycles_v lc,pa_ego_phases_v lcphase
262 WHERE itemvl.lifecycle_id = lc.proj_element_id AND
263 itemvl.current_phase_id = lcphase.proj_element_id AND
264 itemvl.organization_id = orgdef.organization_id AND
265 itemvl.inventory_item_id = itemId AND itemvl.organization_id = orgId;
266
267
268 BEGIN
269
270 IF ((inventory_item_id IS NULL) OR (organization_id is NULL)) THEN
271
272 RAISE no_pk_exception;
273
274 END IF;
275
276 itemId := inventory_item_id;
277 orgId := organization_id;
278
279
280 OPEN C1;
281 FETCH C1 INTO lifecycle,lifecycle_phase;
282 CLOSE C1;
283
284 --------- Commented as langArray has been changed to Varchar to avoid wrapper objects in bpel ------------------------------------
285 /*
286 language_clause := ' (';
287
288 IF ((langArray IS NOT NULL) AND (langArray.count>0)) THEN
289 FOR icount in langArray.first .. langArray.last LOOP
290 language_clause := language_clause || '''' || langArray(icount) || ''',';
291 END LOOP;
292 language_clause := substr(language_clause,0, length(language_clause)-1);
293 language_clause := language_clause || ' ) ';
294 */
295
296 -------- Generate Description Elements with p_language where clause ---------------------------------
297
298 IF (p_language IS NOT NULL) THEN
299
300 x_query := 'SELECT XMLAGG(XMLCONCAT(XMLELEMENT("DESCRIPTION",XMLATTRIBUTES(MSIT.language AS "languageId"),
301 MSIT.description))) FROM MTL_SYSTEM_ITEMS_TL MSIT WHERE MSIT.INVENTORY_ITEM_ID = '|| itemId || 'AND MSIT.ORGANIZATION_ID = ' || orgId || '
302 AND MSIT.language = '|| '''' || p_language || '''' ;
303
304 Execute Immediate x_query
305 INTO x_temp;
306
307
308 ELSE
309 --- Generate Description Elements without Language clause--------
310 SELECT XMLAGG(XMLCONCAT(XMLELEMENT("Description",XMLATTRIBUTES(MSIT.language AS "languageID"),
311 MSIT.description)))
312 INTO x_temp
313 FROM MTL_SYSTEM_ITEMS_TL msit
314 WHERE msit.INVENTORY_ITEM_ID = itemId
315 AND msit.ORGANIZATION_ID = orgId;
316 END IF;
317
318 ------ Generate Primary Attributes of Item -------------
319
320 SELECT
321 XMLELEMENT("ItemBase",
322 XMLCONCAT(x_temp,
323 XMLELEMENT("LongDescription",itemvl.long_description),
324 XMLELEMENT("LifeCycle", lifecycle),
325 XMLELEMENT("LifeCyclePhase",lifecycle_phase),
326 XMLELEMENT("ApprovalStatus",itemvl.approval_status),
327 XMLELEMENT("Status",itemvl.inventory_item_status_code),
328 XMLELEMENT("TypeCode",itemvl.item_type),
329 XMLELEMENT("EngineeringItemFlag",itemvl.eng_item_flag),
330 XMLELEMENT("BaseUOMCode",itemvl.primary_uom_code),
331 XMLELEMENT("SecondaryUOMCode",itemvl.secondary_uom_code),
332 XMLELEMENT("CreationDate",itemvl.creation_date)))
333 INTO x_doc
334 FROM mtl_system_items_vl itemvl,org_organization_definitions orgdef
335 WHERE
336 itemvl.organization_id = orgdef.organization_id AND
337 itemvl.inventory_item_id = itemId AND itemvl.organization_id = orgId;
338
339
340 EXCEPTION
341 WHEN no_data_found THEN
342 x_error_message := 'unexpected_error';
343 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
344 RETURN;
345 WHEN no_PK_exception THEN
346 x_error_message := 'USER ERROR: Inventory Item Id and Organization Id cannot be Null';
347 RETURN;
348 WHEN others THEN
349 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
350 RETURN;
351
352 END getItemBase;
353
354 PROCEDURE getItemAttributes
355 (
356 inventory_item_id IN NUMBER,
357 organization_id IN NUMBER,
358 extension_id IN NUMBER,
359 p_language IN VARCHAR2,
360 x_doc OUT NOCOPY xmltype,
361 x_error_message OUT NOCOPY VARCHAR2
362 )
363 IS
364
365 x_temp xmltype;
366 itemId number;
367 orgId number;
368 x_uda xmltype;
369 no_pk_exception EXCEPTION;
370
371
372
373 --------- Sample XML -------------------------------------------------
374 --------- <SyncItemPrimaryAttributeEBM> ------------------------------------------------
375 --------- <DataArea> -----------------------------------------------------
376 --------- <SyncItemPrimaryAttribute> -----------------------------------------------------
377 --------- ITEM IDENTIFICATION ----------------------------------------
378 --------- ITEM BASE --------------------------------------------------
379 --------- AttributeGroup - UDA ----------------------------------------
380 --------- </SyncItemPrimaryAttribute> -----------------------------------------------------
381 --------- </DataArea> -----------------------------------------------------
382 -------- </SyncItemPrimaryAttributeEBM> ------------------------------------------------
383
384
385 BEGIN
386
387 IF ((inventory_item_id IS NULL) OR (organization_id is NULL)) THEN
388
389 RAISE no_pk_exception;
390
391 END IF;
392
393 itemId := inventory_item_id;
394 orgId := organization_id;
395
396 ------- Call getItemIdentification to capture ITEM_IDENTIFICATIION -----------------
397
398 getItemIdentification(
399 inventory_item_id =>itemId,
400 organization_id =>orgId,
401 x_doc =>x_temp,
402 x_error_message =>x_error_message);
403
404 ------ Call getItemBase to capture ITEM_BASE --------------------------------
405
406 getItemBase(
407 inventory_item_id =>itemId,
408 organization_id =>orgId,
409 p_language => p_language,
410 x_doc =>x_doc,
411 x_error_message =>x_error_message);
412
413 ------ Concatenate ITEM_IDENTIFICATION AND ITEM_BASE ------------------------
414
415 SELECT XMLCONCAT(x_temp,x_doc)
416 INTO x_doc
417 FROM DUAL;
418
419 ------ Call getUDAAttributes to capture UDA XML if itemAttrGroup is not null-
420
421 If extension_id IS NOT NULL THEN
422
423 getUDAAttributes(extension_id =>extension_id,
424 p_language => p_language,
425 x_doc =>x_uda,
426 x_error_message =>x_error_message);
427
428
429 END IF;
430
431 ------- Generate the SYNC_ITEM XML ------------------------------------------
432
433 SELECT XMLELEMENT("SyncItemPrimaryAttributeEBM",
434 XMLELEMENT("DataArea",
435 XMLELEMENT("SyncItemPrimaryAttribute",
436 XMLCONCAT(x_doc,
437 x_uda))))
438 INTO x_doc
439 FROM DUAL;
440
441
442
443 EXCEPTION
444 WHEN no_data_found THEN
445 x_error_message := 'unexpected_error';
446 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
447 RETURN;
448 WHEN no_PK_exception THEN
449 x_error_message := 'USER ERROR: Inventory Item Id and Organization Id cannot be Null';
450 RETURN;
451 WHEN others THEN
452 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
453 RETURN;
454
455
456 END getItemAttributes;
457
458
459
460 PROCEDURE getCategoryAttributes
461 (
462 category_id IN NUMBER,
463 getFlexAttributesFlag IN CHAR,
464 x_doc OUT NOCOPY XMLTYPE,
465 x_error_message OUT NOCOPY VARCHAR2
466 )
467
468 IS
469
470 ----------------- SAMPLE XML ----------------------------------------
471 ----------------- <SyncClassificationSchemeEBM> ---------------------
472 ----------------- <DataArea> ----------------------------------------
473 ----------------- <SyncClassificationScheme> -----------------------
474 ----------------- <ClassificationSchemeIdentification> --------------
475 ----------------- <Identification> ----------------------------------
476 -----------------<Id></Id> ------------------------------------------
477 -----------------</Identification> ----------------------------------
478 -----------------</ClassificationSchemeIdentification>---------------
479 -----------------<Classification>------------------------------------
480 ----------------- <Code>MISC.MISC</Code> ----------------------------
481 ------------------ <AttributeGroup> ---------------------------------
482 ----------------- <Id></Id> -----------------------------------------
483 ----------------- <Name languageId="String"></Name> ----------------
484 ----------------- <Attribute> --------------------------------------
485 ----------------- <Name languageId="String"></Name> ----------------
486 ----------------- <ValueText langaugeId="String"></ValueText>-------
487 ----------------- </Attribute> -------------------------------------
488 ----------------- </AttributeGroup>----------------------------------
489 ----------------- </Classification>---------------------------------
490 ----------------- </SyncClassificationScheme>------------------------
491 ----------------- </DataArea> ---------------------------------------
492 ----------------- </SyncClassificationSchemeEBM> --------------------
493
494 catId number;
495 x_prim xmltype;
496 x_desc xmltype;
497 x_temp xmltype;
498 -- language_clause varchar2(2000);
499 x_query varchar2(2000);
500 no_catId_exception EXCEPTION;
501
502 -- Remove Attribute Groups Elemenet from DFF ------------------------------
503 BEGIN
504
505 IF category_id is NULL THEN
506
507 RAISE no_catId_exception;
508
509 END IF;
510
511 catId := category_id;
512
513 -------- Generate SYNCCLASSIFICATIONSCHEME ELEMENT ---------------------------
514
515 -------- Generate AttributeGroup Element IF flag is true -----------
516
517 IF (getFlexAttributesFlag = 'Y') THEN
518
519 SELECT XMLCONCAT(
520 XMLAGG(XMLELEMENT("AttributeGroup",
521 XMLELEMENT("Id",catId),
522 XMLELEMENT("Name", FND.DESCRIPTIVE_FLEX_CONTEXT_CODE),
523 XMLAGG(XMLELEMENT("Attribute",
524 XMLELEMENT("Name",FND.END_USER_COLUMN_NAME),
525 XMLELEMENT("Value",DECODE(APPLICATION_COLUMN_NAME,'ATTRIBUTE1',CAT.ATTRIBUTE1,'ATTRIBUTE2',CAT.ATTRIBUTE2,
526 'ATTRIBUTE3',CAT.ATTRIBUTE3,'ATTRIBUTE4',CAT.ATTRIBUTE4,'ATTRIBUTE5',CAT.ATTRIBUTE5,
527 'ATTRIBUTE6',CAT.ATTRIBUTE6,'ATTRIBUTE7',CAT.ATTRIBUTE7,'ATTRIBUTE8',CAT.ATTRIBUTE8,
528 'ATTRIBUTE9',CAT.ATTRIBUTE9,'ATTRIBUTE10',CAT.ATTRIBUTE10,'ATTRIBUTE11',CAT.ATTRIBUTE11,
529 'ATTRIBUTE12',CAT.ATTRIBUTE12,'ATTRIBUTE13',CAT.ATTRIBUTE13,'ATTRIBUTE14',CAT.ATTRIBUTE14)))))))
530 INTO x_temp
531 FROM MTL_CATEGORIES_B_KFV CAT,
532 FND_DESCR_FLEX_COL_USAGE_VL FND
533 WHERE (FND.APPLICATION_ID=401)
534 AND (FND.DESCRIPTIVE_FLEXFIELD_NAME LIKE 'MTL_CATEGORIES')
535 AND FND.ENABLED_FLAG ='Y'
536 AND CAT.CATEGORY_ID =catId
537 GROUP BY FND.DESCRIPTIVE_FLEX_CONTEXT_CODE;
538 END IF;
539
540 ----------- Generate SyncClassificationSchemeEBM ELEMENT ------------------
541
542 SELECT
543 XMLELEMENT("SyncClassificationSchemeEBM",
544 XMLELEMENT("DataArea",
545 XMLELEMENT("SyncClassificationScheme",
546 XMLELEMENT("ClassificationSchemeIdentification",
547 XMLELEMENT("Identification",
548 XMLELEMENT("ID",catId))),
549 XMLELEMENT("Classification",
550 XMLELEMENT("Code",CAT.CONCATENATED_SEGMENTS),x_temp))))
551 INTO x_doc
552 FROM MTL_CATEGORIES_B_KFV CAT
553 WHERE CAT.CATEGORY_ID =catId;
554
555
556 EXCEPTION
557 WHEN no_data_found THEN
558 x_error_message := 'unexpected_error';
559 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
560 RETURN;
561 WHEN no_catId_exception THEN
562 x_error_message := 'USER ERROR: Category Id cannot be Null';
563 RETURN;
564 WHEN others THEN
565 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
566 RETURN;
567
568
569 END getCategoryAttributes;
570
571 PROCEDURE getCatalogAttributesInternal
572 (
573 catalogId IN NUMBER,
574 parentCategoryId IN NUMBER,
575 categoryId IN NUMBER,
576 p_language IN VARCHAR2,
577 x_doc OUT NOCOPY xmltype,
578 x_error_message OUT NOCOPY VARCHAR2
579 )
580 IS
581
582 cat_set_id number;
583 x_iden xmltype;
584 x_desc xmltype;
585 x_temp1 xmltype;
586 x_temp2 xmltype;
587 x_temp2_child xmltype;
588 x_query varchar2(2000);
589 -- language_clause varchar2(2000);
590 no_calg_id_exception EXCEPTION;
591
592 BEGIN
593
594 IF catalogId is NULL THEN
595
596 RAISE no_calg_id_exception;
597 END IF;
598
599
600
601 cat_set_id := catalogId;
602
603
604 ------ Generate CATALOGIDENTIFICATION ELEMENT ----------------------------
605
606 SELECT XMLELEMENT("CatalogIdentification",
607 XMLELEMENT("Identification",
608 XMLELEMENT("ID",cat_set_id),
609 XMLELEMENT("Name",CATEGORY_SET_NAME)))
610 INTO x_iden
611 FROM mtl_category_sets_vl
612 WHERE CATEGORY_SET_ID = cat_set_id;
613
614 ------ Generate DESCRIPTION ELEMENT ----------------------------------------
615
616 --------- Commented as langArray has been changed to Varchar to avoid wrapper objects in bpel -----------------
617
618
619 /*language_clause := ' (';
620
621 IF ((langArray IS NOT NULL) AND (langArray.count>0)) THEN
622 FOR icount in langArray.first .. langArray.last LOOP
623 language_clause := language_clause || '''' || langArray(icount) || ''',';
624 END LOOP;
625 language_clause := substr(language_clause,0, length(language_clause)-1);
626 language_clause := language_clause || ' ) ';
627 */
628 ------ Based on p_language, construct the DESCRIPTION ELEMENT --------------
629
630 IF (p_language IS NOT NULL) THEN
631
632 x_query := 'SELECT XMLAGG(XMLELEMENT("Description",XMLATTRIBUTES(LANGUAGE AS "LanguageID"),DESCRIPTION))
633 FROM mtl_category_sets_tl
634 WHERE category_set_id = '|| cat_set_id || 'AND language = '|| '''' || p_language || '''' ;
635
636 Execute Immediate x_query
637 INTO x_desc;
638
639 ELSE
640
641 SELECT XMLAGG(XMLELEMENT("Description",XMLATTRIBUTES(LANGUAGE AS "LanguageID"),DESCRIPTION))
642 INTO x_desc
643 FROM mtl_category_sets_tl
644 WHERE category_set_id =cat_set_id;
645 END IF;
646
647 ----- Generate CATALOGBASE ELEMENT -------------------------------------
648
649 SELECT XMLELEMENT("CatalogBase",x_desc)
650 INTO x_desc
651 FROM DUAL;
652
653
654 ------ Generate ClassificationCode Element if parentCategory Id is not NULL ----
655
656 IF (parentCategoryId is not Null) THEN
657
658 SELECT
659 XMLELEMENT("ClassificationCode",
660 XMLATTRIBUTES(CAT.CONCATENATED_SEGMENTS AS "Name"),parentCategoryId)
661 INTO x_temp1
662 FROM MTL_CATEGORIES_B_KFV CAT
663 WHERE CAT.CATEGORY_ID = parentCategoryId;
664 END IF;
665
666 ----- Generate ChildClassificationCode Element if categoryId is not NULL -----
667
668 IF (categoryId is not NUll) THEN
669
670 SELECT XMLELEMENT("ChildClassificationCode",
671 XMLATTRIBUTES(CAT.CONCATENATED_SEGMENTS AS "Name"),categoryId)
672 INTO x_temp2
673 FROM MTL_CATEGORIES_B_KFV CAT
674 WHERE CAT.CATEGORY_ID = categoryId;
675 END IF;
676
677 ------------- Generate CatalogClassification ELEMENT -----------------
678
679 SELECT XMLELEMENT("CatalogClassification",
680 XMLELEMENT("CatalogClassificationStructure",
681 x_temp1,x_temp2))
682 INTO x_temp1
683 FROM DUAL;
684
685 select XMLCONCAT(x_iden,x_desc,x_temp1) into x_doc from dual;
686
687 EXCEPTION
688 WHEN no_data_found THEN
689 x_error_message := 'unexpected_error';
690 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
691 RETURN;
692 WHEN no_calg_id_exception THEN
693 x_error_message := 'USER ERROR: Catalog Id cannot be Null';
694 RETURN;
695 WHEN others THEN
696 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
697 RETURN;
698
699 END getCatalogAttributesInternal;
700
701 PROCEDURE getCatalogAttributes
702 (
703 catalogId IN NUMBER,
704 parentCategoryId IN NUMBER,
705 categoryId IN NUMBER,
706 p_language IN VARCHAR2,
707 x_doc OUT NOCOPY xmltype,
708 x_error_message OUT NOCOPY VARCHAR2
709 )
710 -- Display Empty Description Tag
711 IS
712
713 ------------- SAMPLE XML ---------------------------------------------
714 ------------- <SyncCatalogEBM> ---------------------------------------
715 ------------- <DataArea> ---------------------------------------------
716 ------------- <SyncCatalog> ------------------------------------------
717 ------------- <CatalogIdentification> --------------------------------
718 ------------- <Identification> ---------------------------------------
719 ------------- <ID>200</ID> -------------------------------------------
720 ------------- <Name>catalog1</NAME> ----------------------------------
721 ------------- </Identification> --------------------------------------
722 ------------- </CatalogIdentification> -------------------------------
723 ------------- <CatalogBase> ------------------------------------------
724 ------------- <Description languageID=US>Catalog1 Descp</Description>-
725 ------------- </CatalogBase> -----------------------------------------
726 ------------- <CatalogClassification> --------------------------------
727 ------------- <CatalogClassificationStructure> -----------------------
728 ------------- <ClassificationCode Name=""></ClassificationCode> ------
729 ------------- <ChildClassificationCode Name=""></ChildClassificationCode>---
730 ------------- </CatalogClassificationStructure>-----------------------
731 ------------- </CatalogClassification> -------------------------------
732 ------------- </SyncCatalog ------------------------------------------
733 ------------- </DataArea> ---------------------------------------------
734 ------------- </SyncCatalogEBM> --------------------------------------
735
736 cat_set_id number;
737 x_iden xmltype;
738 x_desc xmltype;
739 x_temp1 xmltype;
740 x_temp2 xmltype;
741 x_temp2_child xmltype;
742 x_cat xmltype;
743 x_query varchar2(2000);
744 -- language_clause varchar2(2000);
745 no_calg_id_exception EXCEPTION;
746
747
748 BEGIN
749 getCatalogAttributesInternal(
750 catalogId,
751 parentCategoryId,
752 categoryId,
753 p_language,
754 x_cat,
755 x_error_message
756 );
757
758 -------------- Generate SyncCatalogEBM Element -------------------------
759 SELECT XMLELEMENT("SyncCatalogEBM",
760 XMLELEMENT("DataArea",
761 XMLELEMENT("SyncCatalog",
762 x_cat)))
763 INTO x_doc
764 FROM DUAL;
765
766
767 EXCEPTION
768 WHEN no_data_found THEN
769 x_error_message := 'unexpected_error';
770 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
771 RETURN;
772 WHEN no_calg_id_exception THEN
773 x_error_message := 'USER ERROR: Catalog Id cannot be Null';
774 RETURN;
775 WHEN others THEN
776 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
777 RETURN;
778
779 END getCatalogAttributes;
780
781
782
783 ---------------------------------------
784 -- getDataLevelId
785
786 -- input
787 -- p_data_level_internal_name
788 -- can take one of these values
789 -- G_ITM_DATA_LVL CONSTANT VARCHAR2(20) := 'ITEM_LEVEL';
790 -- G_ITM_ORG_DATA_LVL CONSTANT VARCHAR2(20) := 'ITEM_ORG';
791 -- G_ITM_SUP_DATA_LVL CONSTANT VARCHAR2(20) := 'ITEM_SUP';
792 -- G_ITM_SUP_SITE_DATA_LVL CONSTANT VARCHAR2(20) := 'ITEM_SUP_SITE';
793 -- G_ITM_SUP_SITE_ORG_DATA_LVL CONSTANT VARCHAR2(20) := 'ITEM_SUP_SITE_ORG';
794
795 -- output
796 -- data_level_id of the Internal name
797 -- 0 (zero) if the Internal Name did not match any
798 ---------------------------------------
799
800
801 FUNCTION getDataLevelId (p_data_level_internal_name IN VARCHAR2)
802 RETURN NUMBER IS
803 x_data_level_id NUMBER := 0;
804
805 CURSOR data_level_cursor (c_data_level_int_name VARCHAR2) IS
806 SELECT data_level_id
807 FROM ego_data_level_vl
808 WHERE data_level_name = c_data_level_int_name;
809
810 BEGIN
811 -- TBD : Should we add null check for incomin param ?
812 OPEN data_level_cursor(p_data_level_internal_name);
813 FETCH data_level_cursor INTO x_data_level_id;
814 CLOSE data_level_cursor;
815 RETURN x_data_level_id;
816 END getDataLevelId;
817
818
819 ---------------------------------------
820 -- getSupplierAttributes
821 ---------------------------------------
822 -- sample output will be
823
824 -- <SupplierPartyReference>
825 -- <PartyIdentification>
826 -- <Identification>
827 -- <ID></ID>
828 -- <Name></Name>
829 -- </Identification>
830 -- </PartyIdentification>
831 -- </SupplierPartyReference>
832 -- <TimePeriod>
833 -- <StartDateTime></StartDateTime>
834 -- <EndDateTime></EndDateTime>
835 -- </TimePeriod>
836
837 ---------------------------------------
838 PROCEDURE getSupplierAttributes
839 (
840 p_api_version IN NUMBER,
841 p_supplier_id IN NUMBER,
842 p_language IN VARCHAR2, -- If none is passed all languages are returned back
843 x_doc OUT NOCOPY XMLTYPE,
844 x_error_message OUT NOCOPY VARCHAR2
845 )
846 IS
847 BEGIN
848 -- TBD : add debug
849
850 -- query Supplier for ID
851 -- TBD : Ask Gopal what is the output format expected ?
852 -- Is it the one mentioned above ?
853 Select XMLCONCAT(XMLELEMENT("SupplierPartyReference",
854 XMLELEMENT("PartyIdentification",
855 XMLELEMENT("Identification",
856 XMLELEMENT("ID", vendor_id),
857 XMLELEMENT("NAME", vendor_name)
858 )
859 )
860 ),
861 XMLELEMENT("TimePeriod",
862 XMLELEMENT("StartDateTime",start_date_active),
863 XMLELEMENT("EndDateTime",end_date_active)
864 )
865 )
866 INTO x_doc
867 FROM ap_suppliers
868 WHERE vendor_id = p_supplier_id;
869
870 EXCEPTION
871 WHEN no_data_found THEN
872 x_error_message := 'No Data found';
873 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
874 RETURN;
875 WHEN others THEN
876 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
877 RETURN;
878
879 END getSupplierAttributes;
880
881 ---------------------------------------
882 -- Get Supplier Site Attributes
883 ---------------------------------------
884 ---------------------------------------
885 -- sample output will be
886
887 -- <SupplierPartyReference>
888 -- <PartyIdentification>
889 -- <Identification>
890 -- <ID></ID>
891 -- <Name></Name>
892 -- </Identification>
893 -- </PartyIdentification>
894 -- </SupplierPartyReference>
895 -- <TimePeriod>
896 -- <StartDateTime></StartDateTime>
897 -- <EndDateTime></EndDateTime>
898 -- </TimePeriod>
899 -- <AttributeGroup></AttributeGroup>
900 -- <ItemSupplierLocation>
901 -- <LocationReference>
902 -- <LocationIdentification>
903 --
904 -- </LocationIdentification>
905 -- </LocationReference>
906 -- </ItemSupplierLocation>
907
908 ---------------------------------------
909 PROCEDURE getSupplierSiteAttributes(
910 p_api_version IN NUMBER,
911 p_supplier_id IN NUMBER,
912 p_supplier_site_id IN NUMBER,
913 p_language IN VARCHAR2, -- If none is passed all languages are returned back
914 x_doc OUT NOCOPY XMLTYPE,
915 x_error_message OUT NOCOPY VARCHAR2
916 )
917 IS
918 BEGIN
919 -- TBD : Add Debug here
920 Select
921 XMLCONCAT(
922 XMLELEMENT("SupplierPartyReference",
923 XMLELEMENT("PartyIdentification",
924 XMLELEMENT("Identification",
925 XMLELEMENT("ID", vendor_id),
926 XMLELEMENT("NAME", vendor_name)
927 )
928 )
929 ),
930 XMLELEMENT("TimePeriod",
931 XMLELEMENT("StartDateTime",start_date_active),
932 XMLELEMENT("EndDateTime",end_date_active)
933 ),
934 (select XMLELEMENT("ItemSupplierLocation",
935 XMLELEMENT("LocationReference",
936 XMLELEMENT("LocationIdentification",
937 XMLELEMENT("Identification",
938 XMLELEMENT("ID", ss.vendor_site_id),
939 XMLELEMENT("NAME", ss.VENDOR_SITE_CODE)
940 )
941 )
942 )
943 )
944 FROM ap_supplier_sites_all ss
945 WHERE ss.vendor_site_id = p_supplier_site_id
946 ))
947 INTO x_doc
948 FROM ap_suppliers
949 WHERE vendor_id = p_supplier_id;
950
951 EXCEPTION
952 WHEN no_data_found THEN
953 x_error_message := 'No Data found';
954 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
955 RETURN;
956 WHEN others THEN
957 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
958 RETURN;
959 END getSupplierSiteAttributes;
960
961 ---------------------------------------
962 -- Get Item Supplier Attributes
963
964 -- Output
965 -- <SyncItemSupplierEBM>
966 -- <DataArea>
967 -- <SyncItemSupplier>
968 -- ---ITEM IDENTIFICATION---
969 -- --- ITEM BASE ---
970 -- <ItemSupplier>
971 -- <SupplierPartyReference>
972 -- <PartyIdentification>
973 -- <Identification>
974 -- <ID></ID>
975 -- <Name></Name>
976 -- </Identification>
977 -- </PartyIdentification>
978 -- </SupplierPartyReference>
979 -- <TimePeriod>
980 -- <StartDateTime></StartDateTime>
981 -- <EndDateTime></EndDateTime>
982 -- </TimePeriod>
983 -- ---UDA XML---
984 -- </ItemSupplier>
985 -- </SyncItemSupplier>
986 -- </DataArea>
987 -- </SyncItemSupplierEBM>
988 ---------------------------------------
989 PROCEDURE getItemSupplierAttributes
990 (
991 p_api_version IN NUMBER,
992 p_inventory_item_id IN NUMBER, -- Item Identifier1
993 p_organization_id IN NUMBER, -- Item Identifier2
994 p_supplierId IN NUMBER, -- Supplier Identifier
995 p_extension_id IN NUMBER, -- pk for identifying the row in ext values table
996 p_language IN VARCHAR2,
997 x_doc OUT NOCOPY XMLTYPE,
998 x_error_message OUT NOCOPY VARCHAR2
999 )
1000 IS
1001 l_data_level_id NUMBER;
1002 l_item_base_info XMLTYPE;
1003 l_item_id_info XMLTYPE;
1004 l_supplier_info XMLTYPE;
1005 l_uda_xml XMLTYPE;
1006
1007 BEGIN
1008 -- 1. get_data_level_id for the data_level_internal_name
1009 l_data_level_id := getDataLevelId(G_ITM_SUP_DATA_LVL);
1010
1011 -- 2. get item id and base xml
1012 -- TBD : remove PIMDH_PUBLISH_PKGNEW
1013 getItemIdentification(
1014 p_inventory_item_id,
1015 p_organization_id,
1016 l_item_id_info,
1017 x_error_message
1018 );
1019
1020 -- TBD : remove PIMDH_PUBLISH_PKGNEW
1021 getItemBase(
1022 p_inventory_item_id,
1023 p_organization_id,
1024 p_language,
1025 l_item_base_info,
1026 x_error_message
1027 );
1028
1029 SELECT xmlconcat(l_item_id_info, l_item_base_info)
1030 INTO l_item_id_info
1031 FROM dual;
1032
1033 -- 3. get Supplier info
1034 getSupplierAttributes
1035 (
1036 p_api_version => 1.0,
1037 p_supplier_id => p_supplierId,
1038 p_language => p_language,
1039 x_doc => l_supplier_info,
1040 x_error_message => x_error_message
1041 );
1042
1043 -- 3. get the attributes for this intersection
1044 getUDAAttributes
1045 (
1046 extension_id => p_extension_id,
1047 p_language => p_language,
1048 x_doc => l_uda_xml,
1049 x_error_message => x_error_message
1050 );
1051
1052 SELECT XMLELEMENT("SyncItemSupplierEBM",
1053 XMLELEMENT("DataArea",
1054 XMLELEMENT("SyncItemSupplier",
1055 l_item_id_info,
1056 XMLELEMENT("ItemSupplier",
1057 l_supplier_info,
1058 l_uda_xml
1059 )
1060 )))
1061 INTO x_doc
1062 FROM DUAL;
1063
1064 EXCEPTION
1065 WHEN no_data_found THEN
1066 x_error_message := 'No Data found';
1067 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
1068 RETURN;
1069 WHEN others THEN
1070 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
1071 RETURN;
1072 END getItemSupplierAttributes;
1073 ---------------------------------------
1074
1075 -- sample output will be
1076
1077 -- <SyncItemSupplierEBM>
1078 -- <DataArea>
1079 -- <SyncItemSupplier>
1080 -- ---ITEM IDENTIFICATION---
1081 -- --- ITEM BASE ---
1082 -- <ItemSupplier>
1083 -- <SupplierPartyReference>
1084 -- <PartyIdentification>
1085 -- <Identification>
1086 -- <ID>200</ID>
1087 -- <Name></Name>
1088 -- </Identification>
1089 -- <PartyIdentification>
1090 -- </SupplierPartyReference>
1091 -- <TimePeriod>
1092 -- <StartDateTime></StartDateTime>
1093 -- <EndDateTime></EndDateTime>
1094 -- </TimePeriod>
1095 -- <AttributeGroup></AttributeGroup>
1096 -- <ItemSupplierLocation>
1097 -- <LocationReference>
1098 -- <LocationIdentification>
1099 -- </LocationIdentification>
1100 -- </LocationReference>
1101 -- </ItemSupplierLocation>
1102 -- ---UDA XML---
1103 -- </ItemSupplier>
1104 -- </SyncItemSupplier>
1105 -- </DataArea>
1106 -- </SyncItemSupplierEBM>
1107 ---------------------------------------
1108 PROCEDURE getItemSupplierSiteAttributes
1109 (
1110 p_api_version IN NUMBER,
1111 p_inventory_item_id IN NUMBER,
1112 p_organization_id IN NUMBER,
1113 p_supplierId IN NUMBER,
1114 p_site_id IN NUMBER,
1115 p_extension_id IN NUMBER,
1116 p_language IN VARCHAR2, -- If none is passed all languages are returned back
1117 x_doc OUT NOCOPY xmltype,
1118 x_error_message OUT NOCOPY varchar2
1119 )
1120 IS
1121 l_data_level_id NUMBER;
1122 l_item_base_info XMLTYPE;
1123 l_item_id_info XMLTYPE;
1124 l_supplier_site_info XMLTYPE;
1125 l_uda_xml XMLTYPE;
1126
1127 BEGIN
1128 -- 1. get_data_level_id for the data_level_internal_name
1129 l_data_level_id := getDataLevelId(G_ITM_SUP_SITE_DATA_LVL);
1130
1131 -- 2. get item id and base xml
1132 -- TBD : remove PIMDH_PUBLISH_PKGNEW
1133 getItemIdentification(
1134 p_inventory_item_id,
1135 p_organization_id,
1136 l_item_id_info,
1137 x_error_message
1138 );
1139
1140 -- TBD : remove PIMDH_PUBLISH_PKGNEW
1141 getItemBase(
1142 p_inventory_item_id,
1143 p_organization_id,
1144 p_language,
1145 l_item_base_info,
1146 x_error_message
1147 );
1148
1149 SELECT xmlconcat(l_item_id_info, l_item_base_info)
1150 INTO l_item_id_info
1151 FROM dual;
1152
1153 -- 3. get Supplier info
1154 getSupplierSiteAttributes
1155 (
1156 p_api_version => 1.0,
1157 p_supplier_id => p_supplierId,
1158 p_supplier_site_id => p_site_id,
1159 p_language => p_language,
1160 x_doc => l_supplier_site_info,
1161 x_error_message => x_error_message
1162 );
1163
1164 -- 3. get the attributes for this intersection
1165 getUDAAttributes
1166 (
1167 extension_id => p_extension_id,
1168 p_language => p_language,
1169 x_doc => l_uda_xml,
1170 x_error_message => x_error_message
1171 );
1172
1173 SELECT XMLELEMENT("SyncItemSupplierEBM",
1174 XMLELEMENT("DataArea",
1175 XMLELEMENT("SyncItemSupplier",
1176 l_item_id_info,
1177 XMLELEMENT("ItemSupplier",
1178 l_supplier_site_info,
1179 l_uda_xml)
1180 ))
1181 )
1182 INTO x_doc
1183 FROM DUAL;
1184
1185 EXCEPTION
1186 WHEN no_data_found THEN
1187 x_error_message := 'No Data found';
1188 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
1189 RETURN;
1190 WHEN others THEN
1191 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
1192 RETURN;
1193 END getItemSupplierSiteAttributes;
1194
1195 ---------------------------------------
1196 FUNCTION getItemIdentification
1197 (
1198 p_inventory_item_id IN NUMBER,
1199 p_organization_id IN NUMBER
1200 )
1201 RETURN XMLTYPE
1202 IS
1203 l_item_info_xml XMLTYPE;
1204 l_error_message VARCHAR2(4000);
1205 BEGIN
1206 getItemIdentification
1207 (
1208 inventory_item_id => p_inventory_item_id,
1209 organization_id => p_organization_id,
1210 x_doc => l_item_info_xml,
1211 x_error_message => l_error_message
1212 );
1213
1214 if (l_error_message IS NULL) THEN
1215 return l_item_info_xml;
1216 else
1217 return null;
1218 end if;
1219 END getItemIdentification;
1220 ------------------------------------------------------
1221 -- getStructureAttributes
1222
1223 -- input
1224 -- p_structure_id - bill_sequence_id for the structure
1225 -- p_component_id - component_sequence_id for the Component
1226 -- output
1227 -- the output structure definition will contain COMPONENT_ITEM in it only
1228 -- if p_component_id is specified then
1229
1230 -- output sample
1231 -- <SyncItemStructure>
1232 -- <ItemStructureIdentification>
1233 -- <Identification>
1234 -- <ID>200</ID>
1235 -- <Name>PBOM</Name>
1236 -- </Identification>
1237 -- </ItemStructureIdentification>
1238 -- ---ITEM IDENTIFICATION---
1239 -- <ComponentItem>
1240 -- ---ITEM IDENTIFICATION---
1241 -- <Quantity>10</QUANTITY>
1242 -- </ComponentItem>
1243 -- <ComponentItem>
1244 -- ---ITEM IDENTIFICATION---
1245 -- <QUANTITY>10</QUANTITY>
1246 -- </ComponentItem>
1247 -- <ComponentItem>
1248 -- ---ITEM IDENTIFICATION---
1249 -- <QUANTITY>10</QUANTITY>
1250 -- </ComponentItem>
1251 -- </SyncItemStructure>
1252 ------------------------------------------------------
1253
1254 PROCEDURE getStructureAttributes
1255 (
1256 p_api_version IN NUMBER,
1257 p_structure_id IN NUMBER,
1258 p_component_id IN NUMBER,
1259 p_language IN VARCHAR2, -- If none is passed all languages are returned back
1260 p_get_first_level_comps IN VARCHAR2,
1261 x_doc OUT NOCOPY XMLTYPE,
1262 x_error_message OUT NOCOPY VARCHAR2
1263 )
1264 IS
1265 l_assly_item_id NUMBER;
1266 l_assly_item_org_id NUMBER;
1267 l_comp_item_id NUMBER;
1268
1269 l_assly_item_info_xml XMLTYPE;
1270 l_comp_item_info_xml XMLTYPE;
1271
1272 BEGIN
1273 -- TBD : debug :print input vars here
1274
1275 SELECT assembly_item_id, organization_id INTO l_assly_item_id, l_assly_item_org_id
1276 FROM bom_structures_b
1277 WHERE bill_sequence_id = p_structure_id ;
1278
1279 IF (p_component_id is not null) THEN
1280 SELECT component_item_id INTO l_comp_item_id
1281 FROM bom_components_b
1282 WHERE bill_sequence_id = p_structure_id
1283 AND component_sequence_id = p_component_id;
1284 END IF;
1285
1286 getItemIdentification(
1287 l_assly_item_id,
1288 l_assly_item_org_id,
1289 l_assly_item_info_xml,
1290 x_error_message
1291 );
1292
1293 IF (p_component_id is not null) THEN
1294 getItemIdentification(
1295 l_comp_item_id,
1296 l_assly_item_org_id,
1297 l_comp_item_info_xml,
1298 x_error_message
1299 );
1300 END IF;
1301
1302 IF (p_component_id is not null) THEN
1303 SELECT xmlelement("SyncItemStructureEBM",
1304 xmlelement("DataArea",xmlelement("SyncItemStructure",
1305 XMLELEMENT("ItemStructureIdentification",
1306 XMLELEMENT("Identification",
1307 xmlforest(s.bill_sequence_id AS "ID",
1308 nvl(s.alternate_bom_designator,'PRIMARY') AS "NAME")
1309 )),
1310 XMLELEMENT("ItemReference",l_assly_item_info_xml),
1311 (SELECT XMLELEMENT("ComponentItem",
1312 -- XMLELEMENT("COMPONENT_SEQUENCE_ID",c.COMPONENT_SEQUENCE_ID),
1313 XMLELEMENT("ItemReference", l_comp_item_info_xml),
1314 xmlelement("ComponentItemBase",
1315 xmlelement("Quantity", c.component_quantity),
1316 xmlelement("EffectiveTimePeriod",
1317 xmlelement("EndDateTime", c.disable_date)
1318 )
1319 )
1320 )
1321 FROM bom_components_b c
1322 where c.component_sequence_id = p_component_id
1323 )
1324 )))
1325 INTO x_doc
1326 FROM bom_structures_b s
1327 WHERE s.bill_sequence_id = p_structure_id;
1328 ELSE
1329 BEGIN
1330 IF ((p_get_first_level_comps IS NULL) OR (p_get_first_level_comps = 'N')) THEN
1331 BEGIN
1332 SELECT xmlelement("SyncItemStructureEBM",
1333 xmlelement("DataArea",
1334 xmlelement("SyncItemStructure",
1335 XMLELEMENT("ItemStructureIdentification",
1336 XMLELEMENT("Identification",
1337 xmlforest(s.bill_sequence_id AS "ID", nvl(s.alternate_bom_designator,'PRIMARY') AS "NAME")
1338 )
1339 ),
1340 XMLELEMENT("ItemReference",l_assly_item_info_xml)
1341 )
1342 )
1343 )
1344 INTO x_doc
1345 FROM bom_structures_b s
1346 WHERE s.bill_sequence_id = p_structure_id;
1347 END;
1348 ELSIF (p_get_first_level_comps = 'Y') THEN
1349 BEGIN
1350 SELECT xmlelement("SyncItemStructureEBM",
1351 xmlelement("DataArea",xmlelement("SyncItemStructure",
1352 XMLELEMENT("ItemStructureIdentification",
1353 XMLELEMENT("Identification",
1354 xmlforest(s.bill_sequence_id AS "ID",
1355 nvl(s.alternate_bom_designator,'PRIMARY') AS "NAME")
1356 )),
1357 XMLELEMENT("ItemReference",l_assly_item_info_xml),
1358 (SELECT XMLAGG(
1359 XMLELEMENT("ComponentItem",
1360 -- XMLELEMENT("COMPONENT_SEQUENCE_ID",c.COMPONENT_SEQUENCE_ID),
1361 XMLELEMENT("ItemReference",getItemIdentification(c.component_item_id, l_assly_item_org_id)),
1362 xmlelement("ComponentItemBase",
1363 xmlelement("Quantity", c.component_quantity),
1364 xmlelement("EffectiveTimePeriod",
1365 xmlelement("EndDateTime", c.disable_date)
1366 )
1367 )
1368 )
1369 )
1370 FROM bom_components_b c
1371 where c.bill_sequence_id = s.common_bill_sequence_id
1372 )
1373 )))
1374 INTO x_doc
1375 FROM bom_structures_b s
1376 WHERE s.bill_sequence_id = p_structure_id;
1377 END;
1378 END IF;
1379 END;
1380 END IF;
1381
1382 EXCEPTION
1383 WHEN no_data_found THEN
1384 x_error_message := 'No Data found';
1385 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
1386 RETURN;
1387 WHEN others THEN
1388 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
1389 RETURN;
1390 END getStructureAttributes;
1391
1392
1393 --------------------------------------------------
1394 -- getItemCategoryAttributes
1395
1396 -- sample output
1397 -- <SYNC_ITEM_CATALOG>
1398 -- ---ITEM IDENTIFICATION---
1399 -- ---ITEM BASE ---
1400 -- <ITEM_CATALOG>
1401 -- <CATALOG_IDENTIFICATION>
1402 -- <PRIMARY_IDENTIFICATION>
1403 -- <ID>200</ID>
1404 -- <NAME>Catalog1</NAME>
1405 -- <PRIMARY_IDENTIFICATION>
1406 -- <DESCRIPTION>Catalog1 Descp</DESCRIPTION>
1407 -- </CATALOG_IDENTIFICATION >
1408 -- <CATALOG_CLASSIFICATION>
1409 -- <ID>400</ID>
1410 -- <CODE>MISC.MISC</CODE>
1411 -- <CATALOG_CLASSIFICATION>
1412 -- <ITEM_CATALOG>
1413 -- <SYNC_ITEM_CATALOG>
1414 --------------------------------------------------
1415
1416 PROCEDURE getItemCategoryAttributes
1417 (
1418 p_api_version IN NUMBER,
1419 p_inventory_item_id IN NUMBER,
1420 p_organization_id IN NUMBER,
1421 p_catalog_id IN NUMBER,
1422 p_category_id IN NUMBER,
1423 p_language IN VARCHAR2, -- If none is passed all languages are returned back
1424 x_doc OUT NOCOPY xmltype,
1425 x_error_message OUT NOCOPY varchar2
1426 )
1427 IS
1428 l_item_id_info XMLTYPE;
1429 l_item_base_info XMLTYPE;
1430 l_catalog_info XMLTYPE;
1431 l_category_info XMLTYPE;
1432 BEGIN
1433 -- 1. fetch item id and base clauses
1434
1435 getItemIdentification(
1436 p_inventory_item_id,
1437 p_organization_id,
1438 l_item_id_info,
1439 x_error_message
1440 );
1441
1442 getItemBase(
1443 p_inventory_item_id,
1444 p_organization_id,
1445 p_language,
1446 l_item_base_info,
1447 x_error_message
1448 );
1449
1450 SELECT xmlconcat(l_item_id_info, l_item_base_info)
1451 INTO l_item_id_info
1452 FROM dual;
1453
1454 -- 2. fetch the catalog informations for the given item from mtl_item_categories
1455 -- categories from this can be across multiple catalogs
1456 -- group all of the categories unders its own catalog
1457 ---
1458
1459 getCatalogAttributesInternal
1460 (
1461 p_catalog_id,
1462 null,
1463 p_category_id,
1464 p_language,
1465 l_catalog_info,
1466 x_error_message
1467 );
1468 ---
1469
1470
1471 IF l_catalog_info IS NOT NULL THEN
1472 SELECT XMLELEMENT("ItemCatalog",
1473 XMLELEMENT("CatalogReference",l_catalog_info))
1474 INTO l_catalog_info
1475 FROM dual;
1476 END IF;
1477
1478 IF (l_item_id_info IS NOT NULL)
1479 OR (l_catalog_info IS NOT NULL) THEN
1480 SELECT XMLELEMENT("SyncItemCatalogEBM",
1481 XMLELEMENT("DataArea",
1482 XMLELEMENT("SyncItemCatalog",
1483 XMLCONCAT(l_item_id_info, l_catalog_info)
1484 )))
1485 INTO x_doc
1486 FROM dual;
1487 END IF;
1488
1489 EXCEPTION
1490 WHEN OTHERS then
1491 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm; -- TBD : Attend to this
1492 return;
1493
1494 END getItemCategoryAttributes;
1495
1496 PROCEDURE getEventPayload
1497 (
1498 p_sequence_id IN NUMBER,
1499 p_event OUT NOCOPY WF_EVENT_T,
1500 x_error_message OUT NOCOPY VARCHAR2
1501 )
1502
1503 IS
1504
1505 BEGIN
1506
1507
1508 ----------- Retrieve Event Payload based on Sequence_Id from Ego_business_events_tracking ---------
1509
1510 SELECT EVENT_PAYLOAD
1511 INTO p_event
1512 FROM EGO_BUSINESS_EVENTS_TRACKING
1513 WHERE SEQUENCE_ID = p_sequence_id;
1514
1515 EXCEPTION
1516 WHEN no_data_found THEN
1517 x_error_message := 'No Data found';
1518 x_error_message := x_error_message || ':' || SQLCODE || ':' || sqlerrm;
1519 RETURN;
1520 WHEN others THEN
1521 x_error_message := 'errormessage' || ':' || SQLCODE || ':' || sqlerrm;
1522 RETURN;
1523
1524 END getEventPayload;
1525
1526 END EGO_PUBLICATION_PKG;