ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 0E902D4003326DE6E0636DFB09052884Y select /*+ push_pred(uom) */ null action_, null status_, null message_, null request_id_, null modified_columns_, to_date(null) timestamp_, :p_upload_mode upload_mode_, -- mucv.uom_class, mucv.description class_description, mucv.disable_date class_inactive_date, --uom.base_uom_code, -- decode(muomv.base_uom_flag,'Y','Y',null) base_uom_flag, muomv.unit_of_measure_tl unit_of_measure, muomv.uom_code, muomv.description uom_description, muomv.disable_date uom_inactive_date, -- mucv.attribute_category class_attrib_category, mucv.attribute1 class_attribute1, mucv.attribute2 class_attribute2, mucv.attribute3 class_attribute3, mucv.attribute4 class_attribute4, mucv.attribute5 class_attribute5, mucv.attribute6 class_attribute6, mucv.attribute7 class_attribute7, mucv.attribute8 class_attribute8, mucv.attribute9 class_attribute9, mucv.attribute10 class_attribute10, mucv.attribute11 class_attribute11, mucv.attribute12 class_attribute12, mucv.attribute13 class_attribute13, mucv.attribute14 class_attribute14, mucv.attribute15 class_attribute15, -- muomv.attribute_category uom_attrib_category, muomv.attribute1 uom_attribute1, muomv.attribute2 uom_attribute2, muomv.attribute3 uom_attribute3, muomv.attribute4 uom_attribute4, muomv.attribute5 uom_attribute5, muomv.attribute6 uom_attribute6, muomv.attribute7 uom_attribute7, muomv.attribute8 uom_attribute8, muomv.attribute9 uom_attribute9, muomv.attribute10 uom_attribute10, muomv.attribute11 uom_attribute11, muomv.attribute12 uom_attribute12, muomv.attribute13 uom_attribute13, muomv.attribute14 uom_attribute14, muomv.attribute15 uom_attribute15, -- mucv.last_update_date class_lud, mucv.last_updated_by class_luby, muomv.last_update_date uom_lud, muomv.last_updated_by uom_luby, to_number(null) upload_row from mtl_uom_classes_vl mucv, mtl_units_of_measure_vl muomv where muomv.uom_class=mucv.uom_class and 1=1 Convert result SQL from the legacy lexical style to the standard Error/Success/Order By style; flatten the uom_qry WITH block into the main query; add the hidden upload_row column; order the result by upload_row (the order rows were uploaded). U :R122_API_Exists = 'Y' A xxen_inv_upload.upload_uoms US INV Unit Of Measure Upload INV Unit Of Measure Upload ====================== Note: This upload can only be used in R12.2 or later. This upload can be used to create and update Unit of Measure Classes and Units of Measures This upload is only supported in release R12.2 or later. The Oracle API is not available in earlier releases. Enginatics Upload 1=1 1 -40 :p_r122_api_exists Char select 'Y' from dba_objects where object_type = 'PACKAGE' and object_name = 'INV_UOM_API_PUB' US R122 API Exists 2 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 3 30 1=1 uom.uom_class = :p_uom_class LOV custom Y select mucv.uom_class_tl value, mucv.uom_class description from mtl_uom_classes_vl mucv order by value US Unit of Measure Class 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 TIMESTAMP_ Date date Y 7 UPLOAD_MODE_ Char varchar2 2000 :$flex$.Upload_Mode Y 8 UOM_CLASS Char varchar2 10 Y Y 9 CLASS_DESCRIPTION Char varchar2 50 10 CLASS_INACTIVE_DATE Date date 11 BASE_UOM_FLAG LOV custom varchar2 1 Y select fl.lookup_code value, null description from fnd_lookups fl where fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' When creating a new UOM Class, the first row in the template for that class must contain the BASE Unit of Measure with the Base UOM Flag set to Y Y 12 UNIT_OF_MEASURE Char varchar2 25 Y Y 13 UOM_CODE Char varchar2 3 Y Y 14 UOM_DESCRIPTION Char varchar2 50 15 UOM_INACTIVE_DATE Date date 16 CLASS_ATTRIB_CATEGORY Char varchar2 30 17 CLASS_ATTRIBUTE1 Char varchar2 150 18 CLASS_ATTRIBUTE2 Char varchar2 150 19 CLASS_ATTRIBUTE3 Char varchar2 150 20 CLASS_ATTRIBUTE4 Char varchar2 150 21 CLASS_ATTRIBUTE5 Char varchar2 150 22 CLASS_ATTRIBUTE6 Char varchar2 150 23 CLASS_ATTRIBUTE7 Char varchar2 150 24 CLASS_ATTRIBUTE8 Char varchar2 150 25 CLASS_ATTRIBUTE9 Char varchar2 150 26 CLASS_ATTRIBUTE10 Char varchar2 150 27 CLASS_ATTRIBUTE11 Char varchar2 150 28 CLASS_ATTRIBUTE12 Char varchar2 150 29 CLASS_ATTRIBUTE13 Char varchar2 150 30 CLASS_ATTRIBUTE14 Char varchar2 150 31 CLASS_ATTRIBUTE15 Char varchar2 150 32 UOM_ATTRIB_CATEGORY Char varchar2 30 33 UOM_ATTRIBUTE1 Char varchar2 150 34 UOM_ATTRIBUTE2 Char varchar2 150 35 UOM_ATTRIBUTE3 Char varchar2 150 36 UOM_ATTRIBUTE4 Char varchar2 150 37 UOM_ATTRIBUTE5 Char varchar2 150 38 UOM_ATTRIBUTE6 Char varchar2 150 39 UOM_ATTRIBUTE7 Char varchar2 150 40 UOM_ATTRIBUTE8 Char varchar2 150 41 UOM_ATTRIBUTE9 Char varchar2 150 42 UOM_ATTRIBUTE10 Char varchar2 150 43 UOM_ATTRIBUTE11 Char varchar2 150 44 UOM_ATTRIBUTE12 Char varchar2 150 45 UOM_ATTRIBUTE13 Char varchar2 150 46 UOM_ATTRIBUTE14 Char varchar2 150 47 UOM_ATTRIBUTE15 Char varchar2 150 48 CLASS_LUD Date date 49 CLASS_LUBY Number number 50 UOM_LUD Date date 51 UOM_LUBY Number number 52 UPLOAD_ROW Number number Y U 2 status_ out Char STATUS_CODE_ U 3 message_ out Char MESSAGE_ U 4 request_id_ out Number REQUEST_ID_ U 5 timestamp_ out Date TIMESTAMP_ U 6 upload_mode_ in Char UPLOAD_MODE_ U 7 p_uom_class in Char UOM_CLASS U 8 p_class_description in Char CLASS_DESCRIPTION U 9 p_class_disable_date in Date CLASS_INACTIVE_DATE U 10 p_base_uom_flag in Char BASE_UOM_FLAG U 11 p_unit_of_measure in Char UNIT_OF_MEASURE U 12 p_uom_code in Char UOM_CODE U 13 p_uom_description in Char UOM_DESCRIPTION U 14 p_uom_disable_date in Date UOM_INACTIVE_DATE U 15 p_class_attrib_category in Char CLASS_ATTRIB_CATEGORY U 16 p_class_attribute1 in Char CLASS_ATTRIBUTE1 U 17 p_class_attribute2 in Char CLASS_ATTRIBUTE2 U 18 p_class_attribute3 in Char CLASS_ATTRIBUTE3 U 19 p_class_attribute4 in Char CLASS_ATTRIBUTE4 U 20 p_class_attribute5 in Char CLASS_ATTRIBUTE5 U 21 p_class_attribute6 in Char CLASS_ATTRIBUTE6 U 22 p_class_attribute7 in Char CLASS_ATTRIBUTE7 U 23 p_class_attribute8 in Char CLASS_ATTRIBUTE8 U 24 p_class_attribute9 in Char CLASS_ATTRIBUTE9 U 25 p_class_attribute10 in Char CLASS_ATTRIBUTE10 U 26 p_class_attribute11 in Char CLASS_ATTRIBUTE11 U 27 p_class_attribute12 in Char CLASS_ATTRIBUTE12 U 28 p_class_attribute13 in Char CLASS_ATTRIBUTE13 U 29 p_class_attribute14 in Char CLASS_ATTRIBUTE14 U 30 p_class_attribute15 in Char CLASS_ATTRIBUTE15 U 31 p_uom_attrib_category in Char UOM_ATTRIB_CATEGORY U 32 p_uom_attribute1 in Char UOM_ATTRIBUTE1 U 33 p_uom_attribute2 in Char UOM_ATTRIBUTE2 U 34 p_uom_attribute3 in Char UOM_ATTRIBUTE3 U 35 p_uom_attribute4 in Char UOM_ATTRIBUTE4 U 36 p_uom_attribute5 in Char UOM_ATTRIBUTE5 U 37 p_uom_attribute6 in Char UOM_ATTRIBUTE6 U 38 p_uom_attribute7 in Char UOM_ATTRIBUTE7 U 39 p_uom_attribute8 in Char UOM_ATTRIBUTE8 U 40 p_uom_attribute9 in Char UOM_ATTRIBUTE9 U 41 p_uom_attribute10 in Char UOM_ATTRIBUTE10 U 42 p_uom_attribute11 in Char UOM_ATTRIBUTE11 U 43 p_uom_attribute12 in Char UOM_ATTRIBUTE12 U 44 p_uom_attribute13 in Char UOM_ATTRIBUTE13 U 45 p_uom_attribute14 in Char UOM_ATTRIBUTE14 U 46 p_uom_attribute15 in Char UOM_ATTRIBUTE15 Error SQL Order By upload_row, uom_class, decode(base_uom_flag,'Y',1,2), unit_of_measure Success SQL select /*+ ordered push_pred(uomc) */ case when xm.status_code_ = xxen_upload.status_success and uom.uom_luby is not null then xxen_upload.action_meaning(xxen_upload.action_update) else xm.action_ end action_, case when xm.status_code_ = xxen_upload.status_success and uom.uom_luby is not null then xm.status_ else xxen_upload.status_meaning(xxen_upload.status_error) end status_, case when xm.status_code_ = xxen_upload.status_success and uom.uom_luby is not null then xm.message_ else nvl(xm.message_,'The API failed to ' || xm.action_ || ' the UOM') end message_, xm.request_id_, xm.modified_columns_, xm.timestamp_, xm.upload_mode_, -- nvl2(uom.uom_luby,uom.uom_class,xm.uom_class) uom_class, nvl2(uom.uom_luby,uom.class_description,xm.class_description) class_description, nvl2(uom.uom_luby,uom.class_inactive_date,xm.class_inactive_date) class_inactive_date, --nvl2(uom.uom_luby,uom.base_uom_code,xm.base_uom_code) base_uom_code, -- nvl2(uom.uom_luby,uom.base_uom_flag,xm.base_uom_flag) base_uom_flag, nvl2(uom.uom_luby,uom.unit_of_measure,xm.unit_of_measure) unit_of_measure, nvl2(uom.uom_luby,uom.uom_code,xm.uom_code) uom_code, nvl2(uom.uom_luby,uom.uom_description,xm.uom_description) uom_description, nvl2(uom.uom_luby,uom.uom_inactive_date,xm.uom_inactive_date) uom_inactive_date, -- nvl2(uom.uom_luby,uom.class_attrib_category,xm.class_attrib_category) class_attrib_category, nvl2(uom.uom_luby,uom.class_attribute1,xm.class_attribute1) class_attribute1, nvl2(uom.uom_luby,uom.class_attribute2,xm.class_attribute2) class_attribute2, nvl2(uom.uom_luby,uom.class_attribute3,xm.class_attribute3) class_attribute3, nvl2(uom.uom_luby,uom.class_attribute4,xm.class_attribute4) class_attribute4, nvl2(uom.uom_luby,uom.class_attribute5,xm.class_attribute5) class_attribute5, nvl2(uom.uom_luby,uom.class_attribute6,xm.class_attribute6) class_attribute6, nvl2(uom.uom_luby,uom.class_attribute7,xm.class_attribute7) class_attribute7, nvl2(uom.uom_luby,uom.class_attribute8,xm.class_attribute8) class_attribute8, nvl2(uom.uom_luby,uom.class_attribute9,xm.class_attribute9) class_attribute9, nvl2(uom.uom_luby,uom.class_attribute10,xm.class_attribute10) class_attribute10, nvl2(uom.uom_luby,uom.class_attribute11,xm.class_attribute11) class_attribute11, nvl2(uom.uom_luby,uom.class_attribute12,xm.class_attribute12) class_attribute12, nvl2(uom.uom_luby,uom.class_attribute13,xm.class_attribute13) class_attribute13, nvl2(uom.uom_luby,uom.class_attribute14,xm.class_attribute14) class_attribute14, nvl2(uom.uom_luby,uom.class_attribute15,xm.class_attribute15) class_attribute15, -- nvl2(uom.uom_luby,uom.uom_attrib_category,xm.uom_attrib_category) uom_attrib_category, nvl2(uom.uom_luby,uom.uom_attribute1,xm.uom_attribute1) uom_attribute1, nvl2(uom.uom_luby,uom.uom_attribute2,xm.uom_attribute2) uom_attribute2, nvl2(uom.uom_luby,uom.uom_attribute3,xm.uom_attribute3) uom_attribute3, nvl2(uom.uom_luby,uom.uom_attribute4,xm.uom_attribute4) uom_attribute4, nvl2(uom.uom_luby,uom.uom_attribute5,xm.uom_attribute5) uom_attribute5, nvl2(uom.uom_luby,uom.uom_attribute6,xm.uom_attribute6) uom_attribute6, nvl2(uom.uom_luby,uom.uom_attribute7,xm.uom_attribute7) uom_attribute7, nvl2(uom.uom_luby,uom.uom_attribute8,xm.uom_attribute8) uom_attribute8, nvl2(uom.uom_luby,uom.uom_attribute9,xm.uom_attribute9) uom_attribute9, nvl2(uom.uom_luby,uom.uom_attribute10,xm.uom_attribute10) uom_attribute10, nvl2(uom.uom_luby,uom.uom_attribute11,xm.uom_attribute11) uom_attribute11, nvl2(uom.uom_luby,uom.uom_attribute12,xm.uom_attribute12) uom_attribute12, nvl2(uom.uom_luby,uom.uom_attribute13,xm.uom_attribute13) uom_attribute13, nvl2(uom.uom_luby,uom.uom_attribute14,xm.uom_attribute14) uom_attribute14, nvl2(uom.uom_luby,uom.uom_attribute15,xm.uom_attribute15) uom_attribute15, -- nvl2(uom.uom_luby,uom.class_lud,xm.class_lud) class_lud, nvl2(uom.uom_luby,uom.class_luby,xm.class_luby) class_luby, nvl2(uom.uom_luby,uom.uom_lud,xm.uom_lud) uom_lud, nvl2(uom.uom_luby,uom.uom_luby,xm.uom_luby) uom_luby, xm.upload_row from xxen_inv_unit_of_measur_8520_u xm, ( select mucv.uom_class, mucv.description class_description, mucv.disable_date class_inactive_date, (select muomv.uom_code from mtl_units_of_measure_vl muomv where muomv.uom_class = mucv.uom_class and muomv.base_uom_flag = 'Y' ) base_uom_code, -- decode(muomv.base_uom_flag,'Y','Y',null) base_uom_flag, muomv.unit_of_measure_tl unit_of_measure, muomv.uom_code, muomv.description uom_description, muomv.disable_date uom_inactive_date, -- mucv.attribute_category class_attrib_category, mucv.attribute1 class_attribute1, mucv.attribute2 class_attribute2, mucv.attribute3 class_attribute3, mucv.attribute4 class_attribute4, mucv.attribute5 class_attribute5, mucv.attribute6 class_attribute6, mucv.attribute7 class_attribute7, mucv.attribute8 class_attribute8, mucv.attribute9 class_attribute9, mucv.attribute10 class_attribute10, mucv.attribute11 class_attribute11, mucv.attribute12 class_attribute12, mucv.attribute13 class_attribute13, mucv.attribute14 class_attribute14, mucv.attribute15 class_attribute15, -- muomv.attribute_category uom_attrib_category, muomv.attribute1 uom_attribute1, muomv.attribute2 uom_attribute2, muomv.attribute3 uom_attribute3, muomv.attribute4 uom_attribute4, muomv.attribute5 uom_attribute5, muomv.attribute6 uom_attribute6, muomv.attribute7 uom_attribute7, muomv.attribute8 uom_attribute8, muomv.attribute9 uom_attribute9, muomv.attribute10 uom_attribute10, muomv.attribute11 uom_attribute11, muomv.attribute12 uom_attribute12, muomv.attribute13 uom_attribute13, muomv.attribute14 uom_attribute14, muomv.attribute15 uom_attribute15, -- mucv.last_update_date class_lud, mucv.last_updated_by class_luby, muomv.last_update_date uom_lud, muomv.last_updated_by uom_luby from mtl_uom_classes_vl mucv, mtl_units_of_measure_vl muomv where muomv.uom_class = mucv.uom_class ) uom where decode(xm.status_code_,xxen_upload.status_success,xm.uom_class,null) = uom.uom_class (+) and decode(xm.status_code_,xxen_upload.status_success,xm.uom_code,null) = uom.uom_code (+) and uom.uom_lud (+) >= xm.timestamp_ and uom.uom_luby (+) = fnd_global.user_id and xm.status_code_=xxen_upload.status_success