[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT APPLICATION_COLUMN_NAME as DATABASE_COLUMN, FORM_LEFT_PROMPT as DISPLAY_VALUE
FROM FND_DESCR_FLEX_COL_USAGE_TL
WHERE DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type
AND APPLICATION_ID = 431
AND LANGUAGE = USERENV('LANG');
,p_ignore_delete IN VARCHAR2 DEFAULT 'N'
,x_singe_row_attrs_rec OUT NOCOPY EGO_ITEM_PUB.UCCNET_ATTRS_SINGL_ROW_REC_TYP
,x_multi_row_attrs_tbl OUT NOCOPY EGO_ITEM_PUB.UCCNET_ATTRS_MULTI_ROW_TBL_TYP
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR c_intf_single_row_attrs(c_inventory_item_id IN NUMBER, c_organization_id IN NUMBER) IS
SELECT
MAX(DECODE(ATTR_GROUP_INT_NAME, 'Date_Information', DECODE(ATTR_INT_NAME, 'Start_Availability_Date_Time', NVL(ATTR_VALUE_DATE, G_MISS_DATE), null), null)) AS START_AVAILABILITY_DATE_TIME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Date_Information', DECODE(ATTR_INT_NAME, 'Consumer_Avail_Date_Time', NVL(ATTR_VALUE_DATE, G_MISS_DATE), null), null)) AS CONSUMER_AVAIL_DATE_TIME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Date_Information', DECODE(ATTR_INT_NAME, 'End_Availability_Date_Time', NVL(ATTR_VALUE_DATE, G_MISS_DATE), null), null)) AS END_AVAILABILITY_DATE_TIME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Date_Information', DECODE(ATTR_INT_NAME, 'Effective_Date', NVL(ATTR_VALUE_DATE, G_MISS_DATE), null), null)) AS EFFECTIVE_DATE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_Identification', DECODE(ATTR_INT_NAME, 'ISBN_Number', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS ISBN_NUMBER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_Identification', DECODE(ATTR_INT_NAME, 'ISSN_Number', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS ISSN_NUMBER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_MARKING', DECODE(ATTR_INT_NAME, 'IS_INGREDIENT_IRRADIATED', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_INGREDIENT_IRRADIATED
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_MARKING', DECODE(ATTR_INT_NAME, 'IS_TRADE_ITEM_GENETICALLY_MOD', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_GENETICALLY_MOD
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_MARKING', DECODE(ATTR_INT_NAME, 'IS_TRADE_ITEM_IRRADIATED', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_IRRADIATED
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_MARKING', DECODE(ATTR_INT_NAME, 'IS_RAW_MATERIAL_IRRADIATED', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_RAW_MATERIAL_IRRADIATED
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_Measurements', DECODE(ATTR_INT_NAME, 'DEGREE_OF_ORIGINAL_WORT', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DEGREE_OF_ORIGINAL_WORT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_Measurements', DECODE(ATTR_INT_NAME, 'FAT_PERCENT_IN_DRY_MATTER', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS FAT_PERCENT_IN_DRY_MATTER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'FMCG_Measurements', DECODE(ATTR_INT_NAME, 'PERCENT_OF_ALCOHOL_BY_VOL', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS PERCENT_OF_ALCOHOL_BY_VOL
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Gtin_Unit_Indicator', DECODE(ATTR_INT_NAME, 'Is_Trade_Item_A_Consumer_Unit', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_A_CONSUMER_UNIT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Gtin_Unit_Indicator', DECODE(ATTR_INT_NAME, 'Is_Trade_Item_A_Base_Unit', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_A_BASE_UNIT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Gtin_Unit_Indicator', DECODE(ATTR_INT_NAME, 'Is_Trade_Item_Info_Private', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_INFO_PRIVATE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Gtin_Unit_Indicator', DECODE(ATTR_INT_NAME, 'Is_Trade_Item_A_Variable_Unit', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_A_VARIABLE_UNIT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Handling_Information', DECODE(ATTR_INT_NAME, 'Stacking_Weight_Maximum', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS STACKING_WEIGHT_MAXIMUM
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Handling_Information', DECODE(ATTR_INT_NAME, 'Stacking_Factor', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS STACKING_FACTOR
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Material_Safety_Data', DECODE(ATTR_INT_NAME, 'Material_Safety_Data_Sheet_No', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS MATERIAL_SAFETY_DATA_SHEET_NO
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Order_Information', DECODE(ATTR_INT_NAME, 'Ordering_Lead_Time', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS ORDERING_LEAD_TIME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Order_Information', DECODE(ATTR_INT_NAME, 'Order_Quantity_Min', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS ORDER_QUANTITY_MIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Order_Information', DECODE(ATTR_INT_NAME, 'Order_Sizing_Factor', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS ORDER_SIZING_FACTOR
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Order_Information', DECODE(ATTR_INT_NAME, 'Order_Quantity_Max', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS ORDER_QUANTITY_MAX
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Order_Information', DECODE(ATTR_INT_NAME, 'Order_Quantity_Multiple', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS ORDER_QUANTITY_MULTIPLE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Packaging_Marking', DECODE(ATTR_INT_NAME, 'Is_Pack_Marked_With_Exp_Date', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_PACK_MARKED_WITH_EXP_DATE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Packaging_Marking', DECODE(ATTR_INT_NAME, 'Is_Package_Marked_As_Rec', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_PACKAGE_MARKED_AS_REC
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Packaging_Marking', DECODE(ATTR_INT_NAME, 'Is_Package_Marked_Ret', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_PACKAGE_MARKED_RET
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Packaging_Marking', DECODE(ATTR_INT_NAME, 'Is_Pack_Marked_With_Ingred', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_PACK_MARKED_WITH_INGRED
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Packaging_Marking', DECODE(ATTR_INT_NAME, 'Is_Pack_Marked_With_Green_Dot', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_PACK_MARKED_WITH_GREEN_DOT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Price_Date_Information', DECODE(ATTR_INT_NAME, 'Effective_End_Date', NVL(ATTR_VALUE_DATE, G_MISS_DATE), null), null)) AS EFFECTIVE_END_DATE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Price_Date_Information', DECODE(ATTR_INT_NAME, 'Suggested_Retail_Price', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS SUGGESTED_RETAIL_PRICE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Price_Date_Information', DECODE(ATTR_INT_NAME, 'Catalog_Price', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS CATALOG_PRICE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Price_Date_Information', DECODE(ATTR_INT_NAME, 'Effective_Start_Date', NVL(ATTR_VALUE_DATE, G_MISS_DATE), null), null)) AS EFFECTIVE_START_DATE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Price_Information', DECODE(ATTR_INT_NAME, 'Retail_Price_On_Trade_Item', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS RETAIL_PRICE_ON_TRADE_ITEM
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Del_To_Dist_Cntr_Temp_Min', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS DEL_TO_DIST_CNTR_TEMP_MIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uccnet_Storage_Temp_Min', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS STORAGE_HANDLING_TEMP_MIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uom_Storage_Handling_Temp_Max', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_STORAGE_HANDLING_TEMP_MAX
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uccnet_Storage_Temp_Max', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS STORAGE_HANDLING_TEMP_MAX
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uom_Storage_Handling_Temp_Min', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_STORAGE_HANDLING_TEMP_MIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uom_Delivery_To_Mrkt_Temp_Max', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_DELIVERY_TO_MRKT_TEMP_MAX
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Del_To_Dist_Cntr_Temp_Max', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS DEL_TO_DIST_CNTR_TEMP_MAX
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Delivery_To_Mrkt_Temp_Min', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS DELIVERY_TO_MRKT_TEMP_MIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uom_Delivery_To_Mrkt_Temp_Min', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_DELIVERY_TO_MRKT_TEMP_MIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Delivery_To_Mrkt_Temp_Max', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS DELIVERY_TO_MRKT_TEMP_MAX
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uom_Del_To_Dist_Cntr_Temp_Max', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_DEL_TO_DIST_CNTR_TEMP_MAX
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Temperature_Information', DECODE(ATTR_INT_NAME, 'Uom_Del_To_Dist_Cntr_Temp_Min', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_DEL_TO_DIST_CNTR_TEMP_MIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Brand_Name', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS BRAND_NAME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Invoice_Name', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS INVOICE_NAME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Sub_Brand', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS SUB_BRAND
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Eanucc_Code', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS EANUCC_CODE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'EANUCC_Type', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS EANUCC_TYPE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'DESCRIPTION_SHORT', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DESCRIPTION_SHORT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Trade_Item_Coupon', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS TRADE_ITEM_COUPON
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Trade_Item_Form_Description', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS TRADE_ITEM_FORM_DESCRIPTION
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Functional_Name', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS FUNCTIONAL_NAME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Is_Barcode_Symbology_Derivable', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_BARCODE_SYMBOLOGY_DERIVABLE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Retail_Brand_Owner_Gln', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS BRAND_OWNER_GLN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Retail_Brand_Owner_Name', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS BRAND_OWNER_NAME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Hierarchy', DECODE(ATTR_INT_NAME, 'Quantity_Of_Comp_Lay_Item', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS QUANTITY_OF_COMP_LAY_ITEM
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Hierarchy', DECODE(ATTR_INT_NAME, 'Quantity_Of_Inner_Pack', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS QUANTITY_OF_INNER_PACK
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Hierarchy', DECODE(ATTR_INT_NAME, 'Quantity_Of_Item_Inner_Pack', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS QUANTITY_OF_ITEM_INNER_PACK
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Hierarchy', DECODE(ATTR_INT_NAME, 'Quanity_Of_Item_In_Layer', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS QUANITY_OF_ITEM_IN_LAYER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Marking', DECODE(ATTR_INT_NAME, 'Has_Batch_Number', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS HAS_BATCH_NUMBER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Marking', DECODE(ATTR_INT_NAME, 'Is_Trade_Item_Marked_Rec_Flag', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_MAR_REC_FLAG
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Marking', DECODE(ATTR_INT_NAME, 'Is_Non_Sold_Trade_Ret_Flag', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_NON_SOLD_TRADE_RET_FLAG
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Net_Content', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS NET_CONTENT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Gross_Weight', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS GROSS_WEIGHT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Uom_Net_Content', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_NET_CONTENT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Diameter', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS DIAMETER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Ingredient_Strength', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS INGREDIENT_STRENGTH
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Generic_Ingredient_Strgth', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS GENERIC_INGREDIENT_STRGTH
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Generic_Ingredient', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS GENERIC_INGREDIENT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Peg_Vertical', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS PEG_VERTICAL
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Peg_Horizontal', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS PEG_HORIZONTAL
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Drained_Weight', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS DRAINED_WEIGHT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Measurements', DECODE(ATTR_INT_NAME, 'Is_Net_Content_Dec_Flag', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_NET_CONTENT_DEC_FLAG
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'DEPT_OF_TRNSPRT_DANG_GOODS_NUM', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DEPT_OF_TRNSPRT_DANG_GOODS_NUM
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'MODEL_NUMBER', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS MODEL_NUMBER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'IS_TRADE_ITEM_RECALLED', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_TRADE_ITEM_RECALLED
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'TRADE_ITEM_FINISH_DESCRIPTION', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS TRADE_ITEM_FINISH_DESCRIPTION
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'WARRANTY_DESCRIPTION', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS WARRANTY_DESCRIPTION
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'SECURITY_TAG_LOCATION', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS SECURITY_TAG_LOCATION
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'PIECES_PER_TRADE_ITEM', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS PIECES_PER_TRADE_ITEM
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'NESTING_INCREMENT', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS NESTING_INCREMENT
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'URL_FOR_WARRANTY', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS URL_FOR_WARRANTY
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'IS_OUT_OF_BOX_PROVIDED', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS IS_OUT_OF_BOX_PROVIDED
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Hardlines', DECODE(ATTR_INT_NAME, 'RETURN_GOODS_POLICY', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS RETURN_GOODS_POLICY
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Uccnet_Size_Description', DECODE(ATTR_INT_NAME, 'Size_Description', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DESCRIPTIVE_SIZE
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = p_target_proc_status
AND INVENTORY_ITEM_ID = c_inventory_item_id
AND ORGANIZATION_ID = c_organization_id;
SELECT
ROW_IDENTIFIER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Bar_Code', DECODE(ATTR_INT_NAME, 'Bar_Code_Type', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS BAR_CODE_TYPE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Country_Of_Origin', DECODE(ATTR_INT_NAME, 'Country_OF_Origin', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS COUNTRY_OF_ORIGIN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Delivery_Method_Indicator', DECODE(ATTR_INT_NAME, 'Delivery_Method_Indicator', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DELIVERY_METHOD_INDICATOR
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Gtin_Color_Description', DECODE(ATTR_INT_NAME, 'Color_Code_Value', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS COLOR_CODE_VALUE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Gtin_Color_Description', DECODE(ATTR_INT_NAME, 'Color_Code_List_Agency', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS COLOR_CODE_LIST_AGENCY
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Handling_Information', DECODE(ATTR_INT_NAME, 'Handling_Instructions_Code', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS HANDLING_INSTRUCTIONS_CODE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Class_Of_Dangerous_Code', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS CLASS_OF_DANGEROUS_CODE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Dangerous_Goods_Margin_Number', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DANGEROUS_GOODS_MARGIN_NUMBER
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Dangerous_Goods_Hazardous_Code', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DANGEROUS_GOODS_HAZARDOUS_CODE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Dangerous_Goods_Reg_Code', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DANGEROUS_GOODS_REG_CODE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'United_Nations_Dang_Goods_No', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS UNITED_NATIONS_DANG_GOODS_NO
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Uom_Flash_Point_Temp', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS UOM_FLASH_POINT_TEMP
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Flash_Point_Temp', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS FLASH_POINT_TEMP
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Dangerous_Goods_Technical_Name', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DANGEROUS_GOODS_TECHNICAL_NAME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Dangerous_Goods_Shipping_Name', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DANGEROUS_GOODS_SHIPPING_NAME
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Hazardous_Information', DECODE(ATTR_INT_NAME, 'Dangerous_Goods_Pack_Group', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS DANGEROUS_GOODS_PACK_GROUP
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Manufacturing_Info', DECODE(ATTR_INT_NAME, 'Manufacturer_Gln', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS MANUFACTURER_GLN
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Manufacturing_Info', DECODE(ATTR_INT_NAME, 'Name_Of_Manufacturer', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS MANUFACTURER_ID
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Size_Description', DECODE(ATTR_INT_NAME, 'SIZE_CODE_LIST_AGENCY', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS SIZE_CODE_LIST_AGENCY
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'Size_Description', DECODE(ATTR_INT_NAME, 'SIZE_CODE_VALUE', NVL(ATTR_VALUE_STR, G_MISS_CHAR), null), null)) AS SIZE_CODE_VALUE
,MAX(DECODE(ATTR_GROUP_INT_NAME, 'TRADE_ITEM_HARMN_SYS_IDENT', DECODE(ATTR_INT_NAME, 'HARMONIZED_TARIFF_SYS_ID_CODE', NVL(ATTR_VALUE_NUM, G_MISS_NUM), null), null)) AS HARMONIZED_TARIFF_SYS_ID_CODE
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS'
AND DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = p_target_proc_status
AND INVENTORY_ITEM_ID = c_inventory_item_id
AND ORGANIZATION_ID = c_organization_id
AND ((UPPER(TRANSACTION_TYPE) <> 'DELETE' AND p_ignore_delete = 'Y') OR (NVL(p_ignore_delete, 'N') = 'N'))
GROUP BY ROW_IDENTIFIER;
SELECT a.APPLICATION_COLUMN_NAME, u.UOM_CODE
FROM EGO_FND_DF_COL_USGS_EXT a, MTL_UNITS_OF_MEASURE_TL u
WHERE a.UOM_CLASS = u.UOM_CLASS(+)
AND u.BASE_UOM_FLAG(+) = 'Y'
AND u.LANGUAGE(+) = USERENV('LANG')
AND a.APPLICATION_ID = 431
AND a.DESCRIPTIVE_FLEXFIELD_NAME IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND a.APPLICATION_COLUMN_NAME IN (
'GROSS_WEIGHT'
,'PEG_VERTICAL'
,'PEG_HORIZONTAL'
,'DRAINED_WEIGHT'
,'DIAMETER'
,'ORDERING_LEAD_TIME'
,'GENERIC_INGREDIENT_STRGTH'
,'STACKING_WEIGHT_MAXIMUM'
,'PIECES_PER_TRADE_ITEM'
,'NESTING_INCREMENT'
);
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ITEM_CATALOG_GROUP_ID
,SOURCE_SYSTEM_ID
,SOURCE_SYSTEM_REFERENCE
,MAX(TRANSACTION_ID) AS TRANSACTION_ID
,MAX(CREATED_BY) AS CREATED_BY
,MAX(CREATION_DATE) AS CREATION_DATE
,MAX(LAST_UPDATED_BY) AS LAST_UPDATED_BY
,MAX(LAST_UPDATE_DATE) AS LAST_UPDATE_DATE
,MAX(LAST_UPDATE_LOGIN) AS LAST_UPDATE_LOGIN
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE (( ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS') )
OR
( ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND ATTR_GROUP_INT_NAME LIKE 'EGOINT#_GDSN%' ESCAPE '#' )
)
AND DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 2
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID, ITEM_CATALOG_GROUP_ID, SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE;
,p_ignore_delete => 'Y'
,x_singe_row_attrs_rec => l_single_row_attrs
,x_multi_row_attrs_tbl => l_multi_row_attrs
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 3
WHERE DATA_SET_ID = p_data_set_id
AND (( ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS') )
OR
( ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND ATTR_GROUP_INT_NAME LIKE 'EGOINT#_GDSN%' ESCAPE '#' )
)
AND INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = i.ORGANIZATION_ID
AND PROCESS_STATUS = 2;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 3
WHERE DATA_SET_ID = p_data_set_id
AND (( ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS') )
OR
( ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND ATTR_GROUP_INT_NAME LIKE 'EGOINT#_GDSN%' ESCAPE '#' )
)
AND INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = i.ORGANIZATION_ID
AND PROCESS_STATUS = 2;
SELECT
msib.TRADE_ITEM_DESCRIPTOR
,gtn.BRAND_OWNER_GLN
,gtn.BRAND_OWNER_NAME
,gtn.EANUCC_CODE
,gtn.EANUCC_TYPE
,gtn.CATALOG_PRICE
,gtn.EFFECTIVE_START_DATE
,gtn.EFFECTIVE_END_DATE
,gtn.SUGGESTED_RETAIL_PRICE
,gtn.START_AVAILABILITY_DATE_TIME
,gtn.END_AVAILABILITY_DATE_TIME
,gtn.ORDER_QUANTITY_MAX
,gtn.ORDER_QUANTITY_MIN
-- non-edtable at non-leaf
,gtn.ISSN_NUMBER
,gtn.ISBN_NUMBER
,gtn.PERCENT_OF_ALCOHOL_BY_VOL
,gtn.FAT_PERCENT_IN_DRY_MATTER
,gtn.GENERIC_INGREDIENT_STRGTH
,gtn.INGREDIENT_STRENGTH
,gtn.DEL_TO_DIST_CNTR_TEMP_MIN
,gtn.UOM_DEL_TO_DIST_CNTR_TEMP_MIN
,gtn.DEL_TO_DIST_CNTR_TEMP_MAX
,gtn.UOM_DEL_TO_DIST_CNTR_TEMP_MAX
,gtn.DELIVERY_TO_MRKT_TEMP_MIN
,gtn.UOM_DELIVERY_TO_MRKT_TEMP_MIN
,gtn.DELIVERY_TO_MRKT_TEMP_MAX
,gtn.UOM_DELIVERY_TO_MRKT_TEMP_MAX
,gtn.STORAGE_HANDLING_TEMP_MIN
,gtn.UOM_STORAGE_HANDLING_TEMP_MIN
,gtn.STORAGE_HANDLING_TEMP_MAX
,gtn.UOM_STORAGE_HANDLING_TEMP_MAX
,gtn.IS_PACK_MARKED_WITH_GREEN_DOT
,gtn.IS_PACK_MARKED_WITH_INGRED
,gtn.IS_INGREDIENT_IRRADIATED
,gtn.IS_RAW_MATERIAL_IRRADIATED
,gtn.IS_TRADE_ITEM_GENETICALLY_MOD
,gtn.IS_TRADE_ITEM_IRRADIATED
,gtn.SUB_BRAND
,gtn.TRADE_ITEM_COUPON
,gtn.TRADE_ITEM_FORM_DESCRIPTION
,gtn.HAS_BATCH_NUMBER
,gtn.IS_NON_SOLD_TRADE_RET_FLAG
,gtn.IS_TRADE_ITEM_MAR_REC_FLAG
,gtn.IS_PACK_MARKED_WITH_EXP_DATE
,gtn.FUNCTIONAL_NAME
,gtn.DIAMETER
,gtn.DRAINED_WEIGHT
,gtn.PEG_HORIZONTAL
,gtn.PEG_VERTICAL
,gtn.GENERIC_INGREDIENT
,gtn.UOM_NET_CONTENT
FROM EGO_ITEM_GTN_ATTRS_VL gtn, MTL_SYSTEM_ITEMS_B msib
WHERE gtn.INVENTORY_ITEM_ID(+) = msib.INVENTORY_ITEM_ID
AND gtn.ORGANIZATION_ID(+) = msib.ORGANIZATION_ID
AND msib.INVENTORY_ITEM_ID = p_inventory_item_id
AND msib.ORGANIZATION_ID = p_organization_id;
SELECT
COLOR_CODE_LIST_AGENCY
,COLOR_CODE_VALUE
,MANUFACTURER_GLN
,MANUFACTURER_ID
,SIZE_CODE_LIST_AGENCY
,SIZE_CODE_VALUE
,CLASS_OF_DANGEROUS_CODE
,DANGEROUS_GOODS_MARGIN_NUMBER
,DANGEROUS_GOODS_HAZARDOUS_CODE
,DANGEROUS_GOODS_PACK_GROUP
,DANGEROUS_GOODS_REG_CODE
,DANGEROUS_GOODS_SHIPPING_NAME
,UNITED_NATIONS_DANG_GOODS_NO
,FLASH_POINT_TEMP
,DANGEROUS_GOODS_TECHNICAL_NAME
,COUNTRY_OF_ORIGIN
,HANDLING_INSTRUCTIONS_CODE
FROM EGO_ITM_GTN_MUL_ATTRS_VL
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT GDSN_OUTBOUND_ENABLED_FLAG INTO l_gdsn_outbound_flag
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT GDSN_OUTBOUND_ENABLED_FLAG INTO l_gdsn_outbound_flag
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE SET_PROCESS_ID = G_DATA_SET_ID
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id
AND ROWNUM = 1;
SELECT TRADE_ITEM_DESCRIPTOR INTO l_trade_item_desc
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE SET_PROCESS_ID = G_DATA_SET_ID
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id
AND ROWNUM = 1;
IF p_multi_row_attrs_tbl(l_index).TRANSACTION_TYPE <> 'DELETE' THEN
IF NVL(p_multi_row_attrs_tbl(l_index).SIZE_CODE_VALUE, G_MISS_CHAR) <> G_MISS_CHAR THEN
l_attr1_disp := Get_Attribute_Display_Name('EGO_ITEM_GTIN_MULTI_ATTRS', 'SIZE_CODE_VALUE');
END IF; -- if i.transaction_type <> 'DELETE'
SELECT MSIK.INVENTORY_ITEM_ID, CONCATENATED_SEGMENTS INTO l_item_id, l_item_number
FROM MTL_SYSTEM_ITEMS_KFV MSIK, EGO_ITEM_GTN_ATTRS_B EGA
WHERE EGA.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND EGA.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND EGA.EANUCC_CODE = l_eanucc_code
AND EGA.EANUCC_TYPE = l_eanucc_type
AND ROWNUM = 1;
SELECT INVENTORY_ITEM_ID, ITEM_NUMBER INTO l_item_id, l_item_number
FROM
(
SELECT
INVENTORY_ITEM_ID,
ITEM_NUMBER,
MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'Eanucc_Code', ATTR_VALUE_STR, null), null)) AS EANUCC_CODE,
MAX(DECODE(ATTR_GROUP_INT_NAME, 'Trade_Item_Description', DECODE(ATTR_INT_NAME, 'EANUCC_Type', ATTR_VALUE_STR, null), null)) AS EANUCC_TYPE
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_ATTRS'
AND DATA_SET_ID = G_DATA_SET_ID
AND PROCESS_STATUS = 2
AND INVENTORY_ITEM_ID <> p_inventory_item_id
GROUP BY INVENTORY_ITEM_ID, ITEM_NUMBER
)
WHERE EANUCC_CODE = l_eanucc_code
AND EANUCC_TYPE = l_eanucc_type
AND ROWNUM = 1;
SELECT
DANGEROUS_GOODS_MARGIN_NUMBER,
DANGEROUS_GOODS_HAZARDOUS_CODE,
DANGEROUS_GOODS_PACK_GROUP,
DANGEROUS_GOODS_REG_CODE,
DANGEROUS_GOODS_SHIPPING_NAME,
UNITED_NATIONS_DANG_GOODS_NO,
FLASH_POINT_TEMP,
UOM_FLASH_POINT_TEMP,
DANGEROUS_GOODS_TECHNICAL_NAME
INTO
l_dngr_goods_margin_number,
l_dngr_goods_hazardous_code,
l_dngr_goods_pack_group,
l_dngr_goods_reg_code,
l_dngr_goods_shipping_name,
l_united_nations_dang_goods_no,
l_flash_point_temp,
l_uom_flash_point_temp,
l_dngr_goods_technical_name
FROM EGO_ITM_GTN_MUL_ATTRS_VL eigmav, EGO_ATTR_GROUPS_V eagv
WHERE eigmav.INVENTORY_ITEM_ID = p_inventory_item_id
AND eigmav.ORGANIZATION_ID = p_organization_id
AND eigmav.ATTR_GROUP_ID = eagv.ATTR_GROUP_ID
AND eagv.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS'
AND eagv.APPLICATION_ID = EGO_APPL_ID
AND eagv.ATTR_GROUP_NAME = 'Hazardous_Information'
AND eigmav.CLASS_OF_DANGEROUS_CODE = p_multi_row_attrs_tbl(l_index).CLASS_OF_DANGEROUS_CODE;
SELECT MANUFACTURER_ID INTO l_manufacturer_id
FROM EGO_ITM_GTN_MUL_ATTRS_VL eigmav, EGO_ATTR_GROUPS_V eagv
WHERE eigmav.INVENTORY_ITEM_ID = p_inventory_item_id
AND eigmav.ORGANIZATION_ID = p_organization_id
AND eigmav.ATTR_GROUP_ID = eagv.ATTR_GROUP_ID
AND eagv.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS'
AND eagv.APPLICATION_ID = EGO_APPL_ID
AND eagv.ATTR_GROUP_NAME = 'Manufacturing_Info'
AND eigmav.MANUFACTURER_GLN = p_multi_row_attrs_tbl(l_index).MANUFACTURER_GLN;
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ITEM_CATALOG_GROUP_ID
,MAX(TRANSACTION_ID) AS TRANSACTION_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 2
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID, ITEM_CATALOG_GROUP_ID;
SELECT
eiuai.INVENTORY_ITEM_ID
,eiuai.ORGANIZATION_ID
,eav.ATTR_ID
,eiuai.ATTR_INT_NAME
,eiuai.ATTR_VALUE_STR
,eiuai.ATTR_VALUE_NUM
,eiuai.ATTR_VALUE_DATE
FROM EGO_ITM_USR_ATTR_INTRFC eiuai, EGO_ATTRS_V eav
WHERE eiuai.ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND eiuai.ATTR_GROUP_TYPE = eav.ATTR_GROUP_TYPE
AND eiuai.ATTR_GROUP_INT_NAME = eav.ATTR_GROUP_NAME
AND eiuai.ATTR_INT_NAME = eav.ATTR_NAME
AND eav.APPLICATION_ID = EGO_APPL_ID
AND eiuai.DATA_SET_ID = p_data_set_id
AND eiuai.PROCESS_STATUS = 2
AND eiuai.INVENTORY_ITEM_ID = c_inventory_item_id
AND eiuai.ORGANIZATION_ID = c_organization_id;
SELECT
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,ATTR_GROUP_INT_NAME
,MAX(TRANSACTION_ID) AS TRANSACTION_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND ATTR_GROUP_INT_NAME LIKE 'EGOINT#_GDSN%' ESCAPE '#'
AND DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 2
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID, ATTR_GROUP_INT_NAME;
SELECT
eiuai.INVENTORY_ITEM_ID
,eiuai.ORGANIZATION_ID
,eiuai.ATTR_INT_NAME
FROM EGO_ITM_USR_ATTR_INTRFC eiuai
WHERE eiuai.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND eiuai.ATTR_GROUP_INT_NAME = c_attr_group_name
AND eiuai.DATA_SET_ID = p_data_set_id
AND eiuai.PROCESS_STATUS = 2
AND eiuai.INVENTORY_ITEM_ID = c_inventory_item_id
AND eiuai.ORGANIZATION_ID = c_organization_id;
Debug_Msg('Calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES');
EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES(
i.INVENTORY_ITEM_ID,
i.ORGANIZATION_ID,
l_attribute_names,
FND_API.G_FALSE,
l_return_status,
l_msg_count,
l_msg_data);
Debug_Msg('Finished Calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES with status='||l_return_status);
, p_transaction_type => 'UPDATE'
, x_error_message => l_msg_data
);
l_attribute_names.DELETE;
Debug_Msg('Calling EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES');
EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES(
i.INVENTORY_ITEM_ID,
i.ORGANIZATION_ID,
l_attribute_names,
i.ATTR_GROUP_INT_NAME,
FND_API.G_FALSE,
l_return_status,
l_msg_count,
l_msg_data);
Debug_Msg('Finished Calling EGO_GTIN_PVT.PROCESS_EXTN_ATTRIBUTE_UPDATES with status='||l_return_status);
IF p_extension_id IS NOT NULL AND NVL(p_transaction_type, 'X') <> 'DELETE' THEN
l_mode := 'UPDATE';
ELSIF p_transaction_type = 'DELETE' THEN
l_mode := 'DELETE';
** This procedure creates/updates the UCCnet attributes for an item
*/
PROCEDURE Process_UCCnet_Attrs_For_Item (
p_api_version IN NUMBER
,p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_single_row_attrs_rec IN EGO_ITEM_PUB.UCCnet_Attrs_Singl_Row_Rec_Typ
,p_multi_row_attrs_table IN EGO_ITEM_PUB.UCCnet_Attrs_Multi_Row_Tbl_Typ
,p_check_policy IN VARCHAR2 DEFAULT FND_API.G_TRUE
,p_entity_id IN NUMBER DEFAULT NULL
,p_entity_index IN NUMBER DEFAULT NULL
,p_entity_code IN VARCHAR2 DEFAULT NULL
,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR c_uom_code IS
SELECT a.APPLICATION_COLUMN_NAME, u.UOM_CODE
FROM EGO_FND_DF_COL_USGS_EXT a, MTL_UNITS_OF_MEASURE_TL u
WHERE a.UOM_CLASS = u.UOM_CLASS(+)
AND u.BASE_UOM_FLAG(+) = 'Y'
AND u.LANGUAGE(+) = USERENV('LANG')
AND a.APPLICATION_ID = 431
AND a.DESCRIPTIVE_FLEXFIELD_NAME IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND a.APPLICATION_COLUMN_NAME IN (
'GROSS_WEIGHT'
,'PEG_VERTICAL'
,'PEG_HORIZONTAL'
,'DRAINED_WEIGHT'
,'DIAMETER'
,'ORDERING_LEAD_TIME'
,'GENERIC_INGREDIENT_STRGTH'
,'STACKING_WEIGHT_MAXIMUM'
,'PIECES_PER_TRADE_ITEM'
,'NESTING_INCREMENT'
,'DEL_TO_DIST_CNTR_TEMP_MIN'
,'DEL_TO_DIST_CNTR_TEMP_MAX'
,'DELIVERY_TO_MRKT_TEMP_MIN'
,'DELIVERY_TO_MRKT_TEMP_MAX'
,'STORAGE_HANDLING_TEMP_MIN'
,'STORAGE_HANDLING_TEMP_MAX'
,'FLASH_POINT_TEMP'
);
SELECT ATTR_GROUP_TYPE, ATTR_GROUP_NAME, ATTR_NAME, DATABASE_COLUMN
FROM EGO_ATTRS_V
WHERE ATTR_GROUP_TYPE IN ('EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND APPLICATION_ID = EGO_APPL_ID;
SELECT ITEM_CATALOG_GROUP_ID INTO l_category_id
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT TRADE_ITEM_DESCRIPTOR INTO l_trade_item_desc
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
l_attributes_data_table.DELETE;
Debug_Msg('Calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES');
EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES(
p_inventory_item_id,
p_organization_id,
l_attribute_names,
FND_API.G_FALSE,
l_return_status,
l_msg_count,
l_msg_data);
Debug_Msg('Finished Calling EGO_GTIN_PVT.PROCESS_ATTRIBUTE_UPDATES with status='||l_return_status);