The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gpc_code, bundle_id, source_system_id, source_system_reference
FROM mtl_system_items_interface
WHERE bundle_id = l_b_Id ;
UPDATE mtl_system_items_interface
SET Item_catalog_group_id = l_icc_code
WHERE bundle_id = item_data.bundle_id
AND source_system_id = item_data.source_system_id
AND source_system_reference = item_data.source_system_reference;
IS SELECT gpc_code ,bundle_id, global_trade_item_number,source_system_reference
FROM mtl_system_items_interface
WHERE bundle_id = l_b_Id ;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE
SET CATEGORY_id = l_acc_code ,CATEGORY_SET_ID = l_acc_catalog
WHERE bundle_id = item_data.bundle_id
AND source_system_id = item_data.global_trade_item_number
AND source_system_reference = item_data.source_system_reference;
SELECT Value(xml_tab) bundle
FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/Bundles/Bundle'))) xml_tab;
SELECT Value(xml_tab) bundle
FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '//ItemBundle'))) xml_tab;
SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
INTO l_bundle_collection_id
FROM dual;
INSERT INTO MTL_ITEM_BULKLOAD_RECS (request_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
bundle_collection_id,
bundle_id,
source_system_id,
source_system_reference
) values
(-1,
sysdate,
sysdate,
1,
1,
l_bundle_collection_id,
l_bundle_id,
l_source_system_id,
l_source_system_ref
);
INSERT INTO MTL_ITEM_BULKLOAD_RECS (request_id,
creation_date,
last_update_date,
created_by,
last_updated_by,
bundle_collection_id,
bundle_id,
message_type,
message_code
) values
(-1,
sysdate,
sysdate,
1,
1,
l_bundle_collection_id,
l_bundle_id,
c_bls.bundle.extract('/Bundle/Message/@type').getStringVal(),
substr(c_bls.bundle.extract('/Bundle/Message/text()').getStringVal(), 1, 80));
IS SELECT bundle_id
FROM MTL_ITEM_BULKLOAD_RECS
WHERE bundle_collection_id = p_rcb_id;
SELECT bundle_id
FROM MTL_ITEM_BULKLOAD_RECS
WHERE bundle_collection_id = p_rcb_id
AND entity_type LIKE 'ITEM';
SELECT batch_id
FROM EGO_IMPORT_BATCHES_tl
WHERE name = l_batch_name
and language = USERENV('LANG');
SELECT batch_type
from ego_import_batches_b
WHERE batch_id = l_batch_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = l_batch_id
AND ENABLED_FOR_DATA_POOL = 'Y'
);
SELECT set_process_id
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE ISTI
SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
PROCESS_FLAG = C_INIT_PROCESS_FLAG,
--CONFIRM_STATUS = 'UN',
SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = ISTI.SET_PROCESS_ID)
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID)
AND ITEM_NUMBER IS NULL;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE ISTI
SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
PROCESS_FLAG = C_INIT_PROCESS_FLAG,
CONFIRM_STATUS = 'CN',
SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = ISTI.SET_PROCESS_ID)
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID)
AND ITEM_NUMBER IS NOT NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
PROCESS_STATUS = C_INIT_PROCESS_FLAG,
SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = EIUAI.DATA_SET_ID)
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
PROCESS_FLAG = C_INIT_PROCESS_FLAG,
SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = EIAI.BATCH_ID)
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
PROCESS_FLAG = C_INIT_PROCESS_FLAG,
SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = MICI.SET_PROCESS_ID)
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE BOM_BILL_OF_MTLS_INTERFACE BBOMI
SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
PROCESS_FLAG = 1
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
SET TRANSACTION_TYPE = C_TRANSACTION_SYNC,
PROCESS_FLAG = 1
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE EGO_INTERFACE_TL
SET PROCESS_STATUS = 1
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE EGO_UCCNET_EVENTS EUE
SET SOURCE_SYSTEM_ID = (SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = EUE.import_batch_id)
WHERE CLN_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE BOM_BILL_OF_MTLS_INTERFACE BBOMI
SET ORGANIZATION_ID = (SELECT ORGANIZATION_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE BUNDLE_ID = BBOMI.BUNDLE_ID
AND ROWNUM = 1),
ORGANIZATION_CODE = (SELECT mp.organization_code
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
MTL_PARAMETERS mp
WHERE mp.organization_id = msii.organization_id
AND msii.BUNDLE_ID = BBOMI.BUNDLE_ID
AND ROWNUM = 1)
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
SET ORGANIZATION_ID = (SELECT ORGANIZATION_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE BUNDLE_ID = BICI.BUNDLE_ID
AND ROWNUM = 1),
ORGANIZATION_CODE = (SELECT mp.organization_code
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
MTL_PARAMETERS mp
WHERE mp.organization_id = msii.organization_id
AND msii.BUNDLE_ID = BICI.BUNDLE_ID
AND ROWNUM = 1)
WHERE BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET pk2_value = (SELECT asa.VENDOR_SITE_ID
FROM ap_supplier_sites_all asa,
ap_supplier_sites_all asa2,
mtl_system_items_interface msii
WHERE asa.party_site_id = asa2.party_site_id
AND asa.vendor_id = EIUAI.pk1_value
AND asa2.vendor_site_id = EIUAI.pk2_value
AND asa.org_id = msii.organization_id
AND msii.BUNDLE_ID = EIUAI.BUNDLE_ID
AND ROWNUM =1)
WHERE data_level_name = 'ITEM_SUP_SITE'
AND pk2_value IS NOT NULL
AND BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET pk2_value = (SELECT asa.VENDOR_SITE_ID
FROM ap_supplier_sites_all asa,
ap_supplier_sites_all asa2,
mtl_system_items_interface msii
WHERE asa.party_site_id = asa2.party_site_id
AND asa.vendor_id = EIAI.pk1_value
AND asa2.vendor_site_id = EIAI.pk2_value
AND asa.org_id = msii.organization_id
AND msii.BUNDLE_ID = EIAI.BUNDLE_ID
AND ROWNUM =1)
WHERE data_level_name = 'ITEM_SUP_SITE'
AND pk2_value IS NOT NULL
AND BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET pk2_value = (SELECT asa.VENDOR_SITE_ID
FROM ap_supplier_sites_all asa,
ap_supplier_sites_all asa2,
mtl_system_items_interface msii
WHERE asa.party_site_id = asa2.party_site_id
AND asa.vendor_id = EIUAI.pk1_value
AND asa2.vendor_site_id = EIUAI.pk2_value
AND asa.org_id = msii.organization_id
AND msii.BUNDLE_ID = EIUAI.BUNDLE_ID
AND ROWNUM =1)
WHERE data_level_name = 'ITEM_SUP_SITE_ORG'
AND pk2_value IS NOT NULL
AND BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET pk2_value = (SELECT asa.VENDOR_SITE_ID
FROM ap_supplier_sites_all asa,
ap_supplier_sites_all asa2,
mtl_system_items_interface msii
WHERE asa.party_site_id = asa2.party_site_id
AND asa.vendor_id = EIAI.pk1_value
AND asa2.vendor_site_id = EIAI.pk2_value
AND asa.org_id = msii.organization_id
AND msii.BUNDLE_ID = EIAI.BUNDLE_ID
AND ROWNUM =1)
WHERE data_level_name = 'ITEM_SUP_SITE_ORG'
AND pk2_value IS NOT NULL
AND BUNDLE_ID IN
(SELECT BUNDLE_ID
FROM MTL_ITEM_BULKLOAD_RECS
WHERE BUNDLE_COLLECTION_ID = X_BUNDLE_COLLECTION_ID);
SELECT XMLELEMENT("BundleCollections",
XMLELEMENT("BundleCollection",
XMLELEMENT("BundleCollectionId", bundle_collection_id),
XMLELEMENT("Bundles",
XMLAGG(XMLELEMENT("Bundle",
XMLELEMENT("BundleId", bundle_id)))))) XML_DOC
INTO l_xml_doc
FROM MTL_ITEM_BULKLOAD_RECS
WHERE bundle_collection_id = p_bundle_collection_id
AND bundle_id > Nvl(p_prior_bundle_id, 0)
AND ROWNUM < p_max_elements
GROUP BY bundle_collection_id;
SELECT asa.VENDOR_ID, aas.VENDOR_NAME
INTO X_SUPPLIER_ID, X_SUPPLIER_NAME
FROM ap_suppliers aas,
ap_supplier_sites_all asa,
hz_party_sites hps
WHERE hps.GLOBAL_LOCATION_NUMBER = X_EXT_SUP_ID
AND hps.party_site_id = asa.party_site_id
AND aas.vendor_id = asa.vendor_id
AND rownum = 1;
SELECT asa.VENDOR_SITE_ID, asa.VENDOR_SITE_CODE
INTO X_SUPPLIER_ID, X_SUPPLIER_NAME
FROM ap_suppliers aas,
ap_supplier_sites_all asa,
hz_party_sites hps
WHERE hps.GLOBAL_LOCATION_NUMBER = X_EXT_SUP_ID
AND hps.party_site_id = asa.party_site_id
AND aas.vendor_id = asa.vendor_id
AND rownum = 1;
SELECT MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
INTO l_id
FROM dual;
SELECT Value(xml_tab) entry
FROM TABLE(XMLSEQUENCE(EXTRACT(p_collection_xml, '/TL/'||p_entity_name))) xml_tab;
INSERT INTO EGO_INTERFACE_TL (
set_process_id,
unique_id,
bundle_id,
table_name,
LANGUAGE,
column_name,
column_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
) VALUES(
p_batch_id,
p_unique_id,
p_bundle_id,
p_table_name,
trans_entry.ENTRY.extract('/'||p_entity_name||'/@languageID').getStringVal(),
p_column_name,
trans_entry.ENTRY.extract('/'||p_entity_name||'/text()').getStringVal(),
1,
SYSDATE,
1,
SYSDATE,
1
);
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER)
IS
-- Attribute Groups
CURSOR c_AttributeGrps(p_entity_xml XMLTYPE, p_entity_name VARCHAR2)
IS
SELECT Value(xml_tab) attributeGroups
FROM TABLE(XMLSEQUENCE(EXTRACT(p_entity_xml, '/'||p_entity_name||'/AttributeGroup'))) xml_tab;
SELECT Value(xml_tab) attributes
FROM TABLE(XMLSEQUENCE(EXTRACT(p_attrGrp_xml, '/AttributeGroup/Attribute'))) xml_tab;
INSERT INTO EGO_ITM_USR_ATTR_INTRFC(
transaction_id,
bundle_id,
source_system_id,
source_system_reference,
data_set_id,
row_identifier,
organization_code,
attr_group_type,
attr_group_int_name,
attr_int_name,
attr_value_str,
attr_value_num,
attr_value_date,
attr_value_uom,
data_level_name,
pk1_value,
pk2_value,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
p_transaction_id,
p_bundle_id,
p_source_system_id,
p_source_system_reference,
p_data_set_id,
l_row_identifier,
p_organization_code,
'EGO_ITEMMGMT_GROUP', -- bug:6525204 Passing EGO_ITEMMGMT_GROUP as UDA type always
l_attr_group_int_name,
l_attr_int_name,
l_attr_text_value,
l_attr_numeric_value,
l_date_value,
l_attr_quant_unit_code,
p_data_level_name,
p_pk1_value,
p_pk2_value,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_update_login
);
SELECT XMLELEMENT("TL", l_xml_Attrs.attributes.extract('/Attribute/ValueText'))
INTO l_xml_trans
FROM DUAL;
l_last_updated_by NUMBER;
l_last_update_date DATE;
l_last_update_login NUMBER;
EGO_ORC_DELETE_LINE EXCEPTION;
SELECT Value(xml_tab) bundles
FROM TABLE(XMLSEQUENCE(EXTRACT(p_collection_xml, '/XMLEntries/SyncItemPublication/ItemPublicationLine'))) xml_tab;
SELECT Value(xml_tab) items
FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/ItemPublicationLine/Item'))) xml_tab;
SELECT Value(xml_tab) classifications
FROM TABLE(XMLSEQUENCE(EXTRACT(p_items_xml, '/Item/ItemCatalog'))) xml_tab;
SELECT Value(xml_tab) suppliers
FROM TABLE(XMLSEQUENCE(EXTRACT(p_items_xml, '/Item/ItemSupplier'))) xml_tab;
SELECT Value(xml_tab) supplierLocations
FROM TABLE(XMLSEQUENCE(EXTRACT(p_suppliers_xml, '/ItemSupplier/ItemSupplierLocation'))) xml_tab;
SELECT Value(xml_tab) structures
FROM TABLE(XMLSEQUENCE(EXTRACT(p_bundles_xml, '/ItemPublicationLine/ItemStructure'))) xml_tab;
SELECT Value(xml_tab) components
FROM TABLE(XMLSEQUENCE(EXTRACT(p_structures_xml, '/ItemStructure/ComponentItem'))) xml_tab;
SELECT CONCATENATED_SEGMENTS
FROM MTL_CATEGORIES_KFV
WHERE SEGMENT2 = c_alt_cat_code
AND ROWNUM = 1;
l_last_updated_by := 0;
l_last_update_date := SYSDATE;
l_last_update_login := 0;
IF (l_xml_null_chk IS NOT NULL AND l_xml_null_chk.getStringVal() = 'PUBLICATION_DELETE') --bug:6500128
THEN
raise EGO_ORC_DELETE_LINE;
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE(
message_timestamp,
transaction_id,
top_item_flag,
bundle_id,
set_process_id,
source_system_id,
source_system_reference,
item_number,
organization_code,
serial_status_enabled,
lot_status_enabled,
service_item_flag,
--type_code
dual_uom_control,
primary_uom_code,
--storage_uom_code,
--shipping_uom_code,
--UOM_conversion_usage_code
secondary_uom_code,
description,
unit_volume,
volume_uom_code,
unit_weight,
weight_uom_code,
dimension_uom_code,
unit_length,
unit_width,
unit_height,
cycle_count_enabled_flag,
--lot_expiration_on_receipt,
lot_merge_enabled,
lot_split_enabled,
--reservation_allowed_flag,
--serialization_event_code,
shelf_life_days,
--revision_control_flag,
stock_enabled_flag,
auto_lot_alpha_prefix,
--auto_lot_suffix,
auto_serial_alpha_prefix,
--auto_serial_suffix,
--debit_gl_account_code,
asset_creation_code,
purchasing_enabled_flag,
receipt_required_flag,
must_use_approved_vendor_flag,
allow_substitute_receipts_flag,
allow_unordered_receipts_flag,
rfq_required_flag,
taxable_flag,
hazard_class_id,
tax_code,
--issue_uom_code,
--list_price_per_unit_amount,
list_price_per_unit,
under_shipment_tolerance,
over_shipment_tolerance,
--receipt_duration_tolerance,
--manufactured_item_indicator,
consigned_flag,
inventory_planning_code,
--reorder_max_inv_duration,
--reorder_min_inv_duration,
--reorder_max_inv_quantity,
--reorder_min_inv_quantity,
--reorder_quantity,
min_minmax_quantity,
max_minmax_quantity,
minimum_order_quantity,
shrinkage_rate,
bom_item_type,
config_model_type,
effectivity_control,
wip_supply_type,
eng_item_flag,
bom_enabled_flag,
costing_enabled_flag,
inventory_asset_flag,
std_lot_size,
back_orderable_flag,
returnable_flag,
--assemble_to_order_flag,
gpc_code,
trade_item_descriptor,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES(
l_message_date,
l_transaction_id,
NVL(l_xml_ItemEBO.items.extract('/Item/AttributeGroup[ID = "EGO_ORCH_INT"]/Attribute[ID ="TopItem"]/Value/text()').getStringVal(), 'N'),
l_bundle_id,
l_batch_id,
p_source_sys_id,
l_source_sys_reference,
l_xml_ItemEBO.items.extract('/Item/ItemIdentification/Identification/text()').getStringVal(),
l_org_code,
Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/SerialControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/LotControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/ServiceIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
--l_xml_ItemEBO.items.extract('/Item/ItemBase/TypeCode/text()').getStringVal(),
Decode(l_xml_ItemEBO.items.extract('/Item/ItemBase/DualUOMTrackingIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
-- UOM to be set during import
null,--l_xml_ItemEBO.items.extract('/Item/ItemBase/BaseUOMCode/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/ItemBase/StorageUOMCode/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/ItemBase/ShippingUOMCode/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/ItemBase/UOMConversionUsageCode/text()').getStringVal(),
null,--l_xml_ItemEBO.items.extract('/Item/ItemBase/SecondaryUOMCode/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/ItemBase/Description[position() = 1]/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitVolumeMeasure/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitVolumeMeasure/@unitCode').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitWeightMeasure/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/UnitWeightMeasure/@unitCode').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/LengthMeasure/@unitCode').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/LengthMeasure/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/WidthMeasure/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PhysicalCharacteristics/HeightMeasure/text()').getStringVal(),
Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/CycleCountEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
--Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotExpirationOnReceiptIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotMergeEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/LotSplitEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
--Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/ReservationAllowedIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
--l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/SerializationEventCode/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/ShelfLifeDuration/text()').getStringVal(),
--Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/RevisionControlIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/StockingAllowedIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialLotNumberPrefix/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialLotNumberSuffix/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialSerialNumberPrefix/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/InventoryConfiguration/InitialSerialNumberSuffix/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/DebitGLAccountCode/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AssetClassificationCode/text()').getStringVal(),
Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/PurchasableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/ReceiptRequiredIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UseApprovedSupplierIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AllowReceiptSubstitutionIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/AllowUnorderedReceiptIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/RFQRequiredIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/TaxableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/HazardClassificationCode/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/TaxCode/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/IssueUOMCode/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UnitListPrice/Amount/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/UnitListPrice/PerQuantity/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/OverReceiptTolerancePercent/UnderDuration/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/OverReceiptTolerancePercent/OverDuration/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PurchasingConfiguration/ReceiptDurationTolerance/text()').getStringVal(),
--Decode(l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ManufacturedItemIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
Decode(l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ConsignmentItemIndicator/text()').getStringVal(), 'true', 1, 'false', 0, null),
l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/InventoryPlanningCode/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MaximumInventoryDuration/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MinimumInventoryDuration/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MaximumReorderQuantity/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/MinimumReorderQuantity/text()').getStringVal(),
--l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ReorderSetup/ReorderQuantity/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinMaxSetup/MinimumInventoryQuantity/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinMaxSetup/MaximumInventoryQuantity/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/MinimumProductionOrderQuantity/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/PlanningConfiguration/ShrinkageRate/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/BOMItemTypeCode/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/ConfiguratorModelTypeCode/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/EffectivityControlCode/text()').getStringVal(),
l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/WIPSupplyTypeCode/text()').getStringVal(),
Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/EngineeringItemIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/AllowStructureIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/CostingEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/InventoryAssetIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
l_xml_ItemEBO.items.extract('/Item/ManufacturingConfiguration/StandardLotSizeQuantity/text()').getStringVal(),
Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/BackOrderEnabledIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/ReturnableIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
--Decode(l_xml_ItemEBO.items.extract('/Item/OrderManagementConfiguration/AssembleToOrderIndicator/text()').getStringVal(), 'true', 'Y', 'false', 'N', null),
l_xml_ItemEBO.items.extract('/Item/ItemCatalog[PrimaryIndicator = "true"]/CatalogReference/CatalogIdentification/Identification/ID[@schemeName = "GPC"]/text()').getStringVal(),
-- Expecting the trade_item_descriptor as the BaseUOM
l_xml_ItemEBO.items.extract('/Item/ItemBase/BaseUOMCode/text()').getStringVal(),
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
);
SELECT XMLELEMENT("TL", l_xml_ItemEBO.items.extract('/Item/ItemIdentification/Name'))
INTO l_xml_trans
FROM DUAL;
SELECT XMLELEMENT("TL", l_xml_ItemEBO.items.extract('/Item/ItemBase/Description'))
INTO l_xml_trans
FROM DUAL;
INSERT INTO EGO_UCCNET_EVENTS (
source_system_id,
source_system_reference,
message_id,
import_batch_id,
ext_complex_item_reference,
batch_id,
event_row_id,
event_type,
event_action,
gtin,
supplier_gln,
target_market,
cln_id,
disposition_code,
disposition_date,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
p_source_sys_id,
l_source_sys_reference,
l_message_id,
l_batch_id,
l_external_bundle_id, -- External Bundle Id
-1,
l_transaction_id, -- PDUTTA:IDentify seq
'PUBLICATION_INBOUND',
'NEW_ITEM', -- Action or NEW_ITEM
l_source_sys_reference,
'-1', -- Supplier GLN
'-1', -- Tgt Mgt
l_bundle_id,
EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_ACCEPTED_MESSAGE_TYPE,
sysdate,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
);
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE(
transaction_id,
category_set_name,
category_name,
source_system_id,
source_system_reference,
bundle_id,
set_process_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES (
l_transaction_id,
l_xml_Classification.classifications.extract('/ItemCatalog/CatalogReference/CatalogIdentification/Identification/ID/@schemeName').getStringVal(),
l_alt_cat_concat_seg,
p_source_sys_id,
l_source_sys_reference,
l_bundle_id,
l_batch_id,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
);
INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
transaction_id,
batch_id,
source_system_id,
source_system_reference,
bundle_id,
pk1_value,
supplier_name,
supplier_number,
--supplier_site_name,
data_level_name,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
transaction_type
) VALUES(
l_transaction_id,
l_batch_id,
p_source_sys_id,
l_source_sys_reference,
l_bundle_id,
l_supplier_id,
l_supplier_name,
l_supplier_id,
--l_supplier_site_name,
'ITEM_SUP',
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
'SYNC'
);
p_last_updated_by => l_last_updated_by,
p_last_update_date => l_last_update_date,
p_last_update_login => l_last_update_login);
INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
transaction_id,
batch_id,
source_system_id,
source_system_reference,
bundle_id,
pk1_value,
pk2_value,
supplier_name,
supplier_number,
supplier_site_name,
data_level_name,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
transaction_type
) VALUES(
l_transaction_id,
l_batch_id,
p_source_sys_id,
l_source_sys_reference,
l_bundle_id,
l_supplier_id,
l_supplier_site_id,
l_supplier_name,
l_supplier_site_id,
l_xml_SupplierSite.supplierLocations.extract('/ItemSupplierLocation/LocationReference/Name/text()').getStringVal(),
'ITEM_SUP_SITE',
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
'SYNC'
);
INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF(
transaction_id,
batch_id,
source_system_id,
source_system_reference,
bundle_id,
organization_code,
pk1_value,
pk2_value,
supplier_name,
supplier_number,
supplier_site_name,
data_level_name,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
transaction_type
) VALUES(
l_transaction_id,
l_batch_id,
p_source_sys_id,
l_source_sys_reference,
l_bundle_id,
l_org_code,
l_supplier_id,
l_supplier_site_id,
l_supplier_name,
l_supplier_site_id,
l_xml_SupplierSite.supplierLocations.extract('/ItemSupplierLocation/LocationReference/Name/text()').getStringVal(),
l_supplier_attr_level,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
'SYNC'
);
p_last_updated_by => l_last_updated_by,
p_last_update_date => l_last_update_date,
p_last_update_login => l_last_update_login);
p_last_updated_by => l_last_updated_by,
p_last_update_date => l_last_update_date,
p_last_update_login => l_last_update_login);
INSERT INTO BOM_BILL_OF_MTLS_INTERFACE(
transaction_id,
batch_id,
--source_system_id,
source_system_reference,
bundle_id,
alternate_bom_designator,
organization_code,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES(
NULL,--l_transaction_id,
l_batch_id,
--p_source_sys_id,
l_hdr_source_sys_reference,
l_bundle_id,
'PIM_PBOM_S',
l_org_code,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
);
INSERT INTO BOM_INVENTORY_COMPS_INTERFACE(
transaction_id,
batch_id,
--source_system_id,
parent_source_system_reference,
comp_source_system_reference,
bundle_id,
alternate_bom_designator,
organization_code,
component_quantity,
primary_unit_of_measure,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES(
NULL,--l_transaction_id,
l_batch_id,
--p_source_sys_id,
l_hdr_source_sys_reference,
l_xml_component.components.extract('/ComponentItem/ItemReference/ItemIdentification/GTIN/text()').getStringVal(),
l_bundle_id,
'PIM_PBOM_S',
l_org_code,
l_xml_component.components.extract('/ComponentItem/ComponentItemBase/Quantity/text()').getStringVal(),
l_xml_component.components.extract('/ComponentItem/ComponentItemBase/Quantity/@unitCode').getStringVal(),
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login
);
WHEN EGO_ORC_DELETE_LINE --Ignore Delete messages
THEN
NULL;