[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select xmlElement("Attribute",xmlElement("Id",attribute_id),
xmlElement("Name",attribute_name),
xmlAgg(xmlelement("ValueText",xmlattributes(language as "languageID"),attribute_translatable_value)))
FROM EGO_ALL_ATTR_LANG_V
WHERE extension_id =p_ext_id AND ATTRIBUTE_TRANSLATABLE_VALUE IS NOT NULL
AND LANGUAGE = p_lang GROUP BY ATTRIBUTE_ID, ATTRIBUTE_NAME;
Select xmlElement("Attribute",xmlElement("Id",attribute_id),
xmlElement("Name",attribute_name),
xmlAgg(xmlelement("ValueText",xmlattributes(language as "languageID"),attribute_translatable_value)))
FROM EGO_ALL_ATTR_LANG_V
WHERE extension_id =p_ext_id AND ATTRIBUTE_TRANSLATABLE_VALUE IS NOT NULL
GROUP BY ATTRIBUTE_ID, ATTRIBUTE_NAME;
SELECT xmlConcat(xmlElement("Id",attributegroup_id),
xmlElement("Name", attribute_group_name),
xmlagg(xmlelement("Attribute",
xmlelement("Id",attribute_id),
xmlelement("Name",attribute_name),
xmlForest(attribute_char_value AS "Value",
attribute_number_value AS "ValueNumeric",
attribute_uom_value AS "ValueQuantity",
attribute_date_value AS "ValueDate",
attribute_datetime_value AS "ValueDateTime")
)))
FROM EGO_ALL_ATTR_BASE_V
WHERE extension_id = p_ext_id
GROUP BY attribute_group_name,attributegroup_id ;
Select xmlElement("AttributeGroup",x_doc, x_temp)
INTO x_doc
FROM DUAL;
SELECT
XMLELEMENT("ItemIdentification",
XMLELEMENT("Identification",
XMLELEMENT("ID",itemId),
XMLELEMENT("ContextID",XMLATTRIBUTES(orgIdStr AS "SchemeID"),orgId),
XMLELEMENT("Name",kfv.concatenated_segments)))
INTO x_doc
FROM mtl_system_items_vl msiv, org_organization_definitions orgdef,mtl_system_items_b_kfv kfv
WHERE msiv.inventory_item_id = kfv.inventory_item_id AND
msiv.organization_id = kfv.organization_id AND
msiv.organization_id = orgdef.organization_id AND
msiv.inventory_item_id = itemId AND
msiv.organization_id = orgId ;
SELECT
lc.element_number,lcphase.element_number
FROM mtl_system_items_vl itemvl,org_organization_definitions orgdef,
pa_ego_lifecycles_v lc,pa_ego_phases_v lcphase
WHERE itemvl.lifecycle_id = lc.proj_element_id AND
itemvl.current_phase_id = lcphase.proj_element_id AND
itemvl.organization_id = orgdef.organization_id AND
itemvl.inventory_item_id = itemId AND itemvl.organization_id = orgId;
x_query := 'SELECT XMLAGG(XMLCONCAT(XMLELEMENT("DESCRIPTION",XMLATTRIBUTES(MSIT.language AS "languageId"),
MSIT.description))) FROM MTL_SYSTEM_ITEMS_TL MSIT WHERE MSIT.INVENTORY_ITEM_ID = '|| itemId || 'AND MSIT.ORGANIZATION_ID = ' || orgId || '
AND MSIT.language = '|| '''' || p_language || '''' ;
SELECT XMLAGG(XMLCONCAT(XMLELEMENT("Description",XMLATTRIBUTES(MSIT.language AS "languageID"),
MSIT.description)))
INTO x_temp
FROM MTL_SYSTEM_ITEMS_TL msit
WHERE msit.INVENTORY_ITEM_ID = itemId
AND msit.ORGANIZATION_ID = orgId;
SELECT
XMLELEMENT("ItemBase",
XMLCONCAT(x_temp,
XMLELEMENT("LongDescription",itemvl.long_description),
XMLELEMENT("LifeCycle", lifecycle),
XMLELEMENT("LifeCyclePhase",lifecycle_phase),
XMLELEMENT("ApprovalStatus",itemvl.approval_status),
XMLELEMENT("Status",itemvl.inventory_item_status_code),
XMLELEMENT("TypeCode",itemvl.item_type),
XMLELEMENT("EngineeringItemFlag",itemvl.eng_item_flag),
XMLELEMENT("BaseUOMCode",itemvl.primary_uom_code),
XMLELEMENT("SecondaryUOMCode",itemvl.secondary_uom_code),
XMLELEMENT("CreationDate",itemvl.creation_date)))
INTO x_doc
FROM mtl_system_items_vl itemvl,org_organization_definitions orgdef
WHERE
itemvl.organization_id = orgdef.organization_id AND
itemvl.inventory_item_id = itemId AND itemvl.organization_id = orgId;
SELECT XMLCONCAT(x_temp,x_doc)
INTO x_doc
FROM DUAL;
SELECT XMLELEMENT("SyncItemPrimaryAttributeEBM",
XMLELEMENT("DataArea",
XMLELEMENT("SyncItemPrimaryAttribute",
XMLCONCAT(x_doc,
x_uda))))
INTO x_doc
FROM DUAL;
SELECT XMLCONCAT(
XMLAGG(XMLELEMENT("AttributeGroup",
XMLELEMENT("Id",catId),
XMLELEMENT("Name", FND.DESCRIPTIVE_FLEX_CONTEXT_CODE),
XMLAGG(XMLELEMENT("Attribute",
XMLELEMENT("Name",FND.END_USER_COLUMN_NAME),
XMLELEMENT("Value",DECODE(APPLICATION_COLUMN_NAME,'ATTRIBUTE1',CAT.ATTRIBUTE1,'ATTRIBUTE2',CAT.ATTRIBUTE2,
'ATTRIBUTE3',CAT.ATTRIBUTE3,'ATTRIBUTE4',CAT.ATTRIBUTE4,'ATTRIBUTE5',CAT.ATTRIBUTE5,
'ATTRIBUTE6',CAT.ATTRIBUTE6,'ATTRIBUTE7',CAT.ATTRIBUTE7,'ATTRIBUTE8',CAT.ATTRIBUTE8,
'ATTRIBUTE9',CAT.ATTRIBUTE9,'ATTRIBUTE10',CAT.ATTRIBUTE10,'ATTRIBUTE11',CAT.ATTRIBUTE11,
'ATTRIBUTE12',CAT.ATTRIBUTE12,'ATTRIBUTE13',CAT.ATTRIBUTE13,'ATTRIBUTE14',CAT.ATTRIBUTE14)))))))
INTO x_temp
FROM MTL_CATEGORIES_B_KFV CAT,
FND_DESCR_FLEX_COL_USAGE_VL FND
WHERE (FND.APPLICATION_ID=401)
AND (FND.DESCRIPTIVE_FLEXFIELD_NAME LIKE 'MTL_CATEGORIES')
AND FND.ENABLED_FLAG ='Y'
AND CAT.CATEGORY_ID =catId
GROUP BY FND.DESCRIPTIVE_FLEX_CONTEXT_CODE;
SELECT
XMLELEMENT("SyncClassificationSchemeEBM",
XMLELEMENT("DataArea",
XMLELEMENT("SyncClassificationScheme",
XMLELEMENT("ClassificationSchemeIdentification",
XMLELEMENT("Identification",
XMLELEMENT("ID",catId))),
XMLELEMENT("Classification",
XMLELEMENT("Code",CAT.CONCATENATED_SEGMENTS),x_temp))))
INTO x_doc
FROM MTL_CATEGORIES_B_KFV CAT
WHERE CAT.CATEGORY_ID =catId;
SELECT XMLELEMENT("CatalogIdentification",
XMLELEMENT("Identification",
XMLELEMENT("ID",cat_set_id),
XMLELEMENT("Name",CATEGORY_SET_NAME)))
INTO x_iden
FROM mtl_category_sets_vl
WHERE CATEGORY_SET_ID = cat_set_id;
x_query := 'SELECT XMLAGG(XMLELEMENT("Description",XMLATTRIBUTES(LANGUAGE AS "LanguageID"),DESCRIPTION))
FROM mtl_category_sets_tl
WHERE category_set_id = '|| cat_set_id || 'AND language = '|| '''' || p_language || '''' ;
SELECT XMLAGG(XMLELEMENT("Description",XMLATTRIBUTES(LANGUAGE AS "LanguageID"),DESCRIPTION))
INTO x_desc
FROM mtl_category_sets_tl
WHERE category_set_id =cat_set_id;
SELECT XMLELEMENT("CatalogBase",x_desc)
INTO x_desc
FROM DUAL;
SELECT
XMLELEMENT("ClassificationCode",
XMLATTRIBUTES(CAT.CONCATENATED_SEGMENTS AS "Name"),parentCategoryId)
INTO x_temp1
FROM MTL_CATEGORIES_B_KFV CAT
WHERE CAT.CATEGORY_ID = parentCategoryId;
SELECT XMLELEMENT("ChildClassificationCode",
XMLATTRIBUTES(CAT.CONCATENATED_SEGMENTS AS "Name"),categoryId)
INTO x_temp2
FROM MTL_CATEGORIES_B_KFV CAT
WHERE CAT.CATEGORY_ID = categoryId;
SELECT XMLELEMENT("CatalogClassification",
XMLELEMENT("CatalogClassificationStructure",
x_temp1,x_temp2))
INTO x_temp1
FROM DUAL;
select XMLCONCAT(x_iden,x_desc,x_temp1) into x_doc from dual;
SELECT XMLELEMENT("SyncCatalogEBM",
XMLELEMENT("DataArea",
XMLELEMENT("SyncCatalog",
x_cat)))
INTO x_doc
FROM DUAL;
SELECT data_level_id
FROM ego_data_level_vl
WHERE data_level_name = c_data_level_int_name;
Select XMLCONCAT(XMLELEMENT("SupplierPartyReference",
XMLELEMENT("PartyIdentification",
XMLELEMENT("Identification",
XMLELEMENT("ID", vendor_id),
XMLELEMENT("NAME", vendor_name)
)
)
),
XMLELEMENT("TimePeriod",
XMLELEMENT("StartDateTime",start_date_active),
XMLELEMENT("EndDateTime",end_date_active)
)
)
INTO x_doc
FROM ap_suppliers
WHERE vendor_id = p_supplier_id;
Select
XMLCONCAT(
XMLELEMENT("SupplierPartyReference",
XMLELEMENT("PartyIdentification",
XMLELEMENT("Identification",
XMLELEMENT("ID", vendor_id),
XMLELEMENT("NAME", vendor_name)
)
)
),
XMLELEMENT("TimePeriod",
XMLELEMENT("StartDateTime",start_date_active),
XMLELEMENT("EndDateTime",end_date_active)
),
(select XMLELEMENT("ItemSupplierLocation",
XMLELEMENT("LocationReference",
XMLELEMENT("LocationIdentification",
XMLELEMENT("Identification",
XMLELEMENT("ID", ss.vendor_site_id),
XMLELEMENT("NAME", ss.VENDOR_SITE_CODE)
)
)
)
)
FROM ap_supplier_sites_all ss
WHERE ss.vendor_site_id = p_supplier_site_id
))
INTO x_doc
FROM ap_suppliers
WHERE vendor_id = p_supplier_id;
SELECT xmlconcat(l_item_id_info, l_item_base_info)
INTO l_item_id_info
FROM dual;
SELECT XMLELEMENT("SyncItemSupplierEBM",
XMLELEMENT("DataArea",
XMLELEMENT("SyncItemSupplier",
l_item_id_info,
XMLELEMENT("ItemSupplier",
l_supplier_info,
l_uda_xml
)
)))
INTO x_doc
FROM DUAL;
SELECT xmlconcat(l_item_id_info, l_item_base_info)
INTO l_item_id_info
FROM dual;
SELECT XMLELEMENT("SyncItemSupplierEBM",
XMLELEMENT("DataArea",
XMLELEMENT("SyncItemSupplier",
l_item_id_info,
XMLELEMENT("ItemSupplier",
l_supplier_site_info,
l_uda_xml)
))
)
INTO x_doc
FROM DUAL;
SELECT assembly_item_id, organization_id INTO l_assly_item_id, l_assly_item_org_id
FROM bom_structures_b
WHERE bill_sequence_id = p_structure_id ;
SELECT component_item_id INTO l_comp_item_id
FROM bom_components_b
WHERE bill_sequence_id = p_structure_id
AND component_sequence_id = p_component_id;
SELECT xmlelement("SyncItemStructureEBM",
xmlelement("DataArea",xmlelement("SyncItemStructure",
XMLELEMENT("ItemStructureIdentification",
XMLELEMENT("Identification",
xmlforest(s.bill_sequence_id AS "ID",
nvl(s.alternate_bom_designator,'PRIMARY') AS "NAME")
)),
XMLELEMENT("ItemReference",l_assly_item_info_xml),
(SELECT XMLELEMENT("ComponentItem",
-- XMLELEMENT("COMPONENT_SEQUENCE_ID",c.COMPONENT_SEQUENCE_ID),
XMLELEMENT("ItemReference", l_comp_item_info_xml),
xmlelement("ComponentItemBase",
xmlelement("Quantity", c.component_quantity),
xmlelement("EffectiveTimePeriod",
xmlelement("EndDateTime", c.disable_date)
)
)
)
FROM bom_components_b c
where c.component_sequence_id = p_component_id
)
)))
INTO x_doc
FROM bom_structures_b s
WHERE s.bill_sequence_id = p_structure_id;
SELECT xmlelement("SyncItemStructureEBM",
xmlelement("DataArea",
xmlelement("SyncItemStructure",
XMLELEMENT("ItemStructureIdentification",
XMLELEMENT("Identification",
xmlforest(s.bill_sequence_id AS "ID", nvl(s.alternate_bom_designator,'PRIMARY') AS "NAME")
)
),
XMLELEMENT("ItemReference",l_assly_item_info_xml)
)
)
)
INTO x_doc
FROM bom_structures_b s
WHERE s.bill_sequence_id = p_structure_id;
SELECT xmlelement("SyncItemStructureEBM",
xmlelement("DataArea",xmlelement("SyncItemStructure",
XMLELEMENT("ItemStructureIdentification",
XMLELEMENT("Identification",
xmlforest(s.bill_sequence_id AS "ID",
nvl(s.alternate_bom_designator,'PRIMARY') AS "NAME")
)),
XMLELEMENT("ItemReference",l_assly_item_info_xml),
(SELECT XMLAGG(
XMLELEMENT("ComponentItem",
-- XMLELEMENT("COMPONENT_SEQUENCE_ID",c.COMPONENT_SEQUENCE_ID),
XMLELEMENT("ItemReference",getItemIdentification(c.component_item_id, l_assly_item_org_id)),
xmlelement("ComponentItemBase",
xmlelement("Quantity", c.component_quantity),
xmlelement("EffectiveTimePeriod",
xmlelement("EndDateTime", c.disable_date)
)
)
)
)
FROM bom_components_b c
where c.bill_sequence_id = s.common_bill_sequence_id
)
)))
INTO x_doc
FROM bom_structures_b s
WHERE s.bill_sequence_id = p_structure_id;
SELECT xmlconcat(l_item_id_info, l_item_base_info)
INTO l_item_id_info
FROM dual;
SELECT XMLELEMENT("ItemCatalog",
XMLELEMENT("CatalogReference",l_catalog_info))
INTO l_catalog_info
FROM dual;
SELECT XMLELEMENT("SyncItemCatalogEBM",
XMLELEMENT("DataArea",
XMLELEMENT("SyncItemCatalog",
XMLCONCAT(l_item_id_info, l_catalog_info)
)))
INTO x_doc
FROM dual;
SELECT EVENT_PAYLOAD
INTO p_event
FROM EGO_BUSINESS_EVENTS_TRACKING
WHERE SEQUENCE_ID = p_sequence_id;