ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode 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 US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US EA11F501A1B2C3D4E5F60718293A4B5C select x.*, rownum upload_row from ( select null action_, null status_, null message_, null modified_columns_, efs.set_name, efs.description set_description, efs.effective_end_date set_effective_end_date, 'SET' row_type, cast(null as varchar2(240)) inventory_item, cast(null as varchar2(10)) failure_code_required, cast(null as date) association_effective_end_date, cast(null as varchar2(80)) failure_code, cast(null as varchar2(80)) cause_code, cast(null as varchar2(80)) resolution_code, cast(null as date) combination_effective_end_date, cast(null as varchar2(10)) delete_record from eam_failure_sets efs where 2=2 union all select null action_, null status_, null message_, null modified_columns_, efs.set_name, cast(null as varchar2(240)) set_description, cast(null as date) set_effective_end_date, 'ASSOCIATION' row_type, msibk.concatenated_segments inventory_item, decode(efsa.failure_code_required,'Y',xxen_util.meaning('Y','YES_NO',0)) failure_code_required, efsa.effective_end_date association_effective_end_date, cast(null as varchar2(80)) failure_code, cast(null as varchar2(80)) cause_code, cast(null as varchar2(80)) resolution_code, cast(null as date) combination_effective_end_date, cast(null as varchar2(10)) delete_record from eam_failure_sets efs, eam_failure_set_associations efsa, mtl_system_items_b_kfv msibk, mtl_parameters mp where efs.set_id=efsa.set_id and efsa.inventory_item_id=msibk.inventory_item_id and msibk.organization_id=mp.organization_id and mp.master_organization_id=mp.organization_id and 2=2 union all select null action_, null status_, null message_, null modified_columns_, efs.set_name, cast(null as varchar2(240)) set_description, cast(null as date) set_effective_end_date, 'COMBINATION' row_type, cast(null as varchar2(240)) inventory_item, cast(null as varchar2(10)) failure_code_required, cast(null as date) association_effective_end_date, efc.failure_code, efc.cause_code, efc.resolution_code, efc.effective_end_date combination_effective_end_date, cast(null as varchar2(10)) delete_record from eam_failure_sets efs, eam_failure_combinations efc where efs.set_id=efc.set_id and 2=2 ) x Initial EAM Failure Sets Upload - Sets, Associations and Combinations Y U A xxen_eam_upload.upload_failure_sets xxen_eam_upload.validate_failure_set_row( :$column$.row_type, :$column$.inventory_item, :$column$.failure_code, :$column$.cause_code, :$column$.resolution_code ) US EAM Failure Sets Upload EAM Failure Sets Upload ======================= Creates and updates Oracle EAM (Enterprise Asset Management) Failure Sets, along with their Asset-Group Item Associations and Failure/Cause/Resolution Combinations. One Set Name can own many child rows (Associations and Combinations). A SET row defines the header explicitly. For Create rows, the upload also creates the missing failure set header automatically from the first ASSOCIATION or COMBINATION row for that Set Name. Row Type -------- - SET: Creates or updates the failure set header (set_name, description, effective_end_date). - ASSOCIATION: Links the set to an asset-group / rebuildable item. The item must have EAM_ITEM_TYPE in (1,3) and exist in the master item organization. On Create, this row can also create the failure set header if it does not exist yet. - COMBINATION: Adds a valid Failure / Cause / Resolution code triple to the set. The individual codes must already exist in EAM Failure Codes setup (use the EAM Failure Codes Upload). On Create, this row can also create the failure set header if it does not exist yet. Upload Mode =========== - Create: opens an empty template. Enter a SET row per new failure set, or enter the first ASSOCIATION / COMBINATION row directly; the upload will create the missing header before processing the child row. - Create, Update: downloads existing sets / associations / combinations for review or update. Delete ====== - SET rows cannot be deleted directly (Oracle API does not ship Delete_FailureSet). End-date the set instead by setting Set Effective End Date. - ASSOCIATION and COMBINATION rows can be deleted by setting Delete to Yes. Dates ===== Blanking Effective End Date on an update reactivates the ended record. Enginatics Upload 1=1 2=2 1 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 2 20 2=2 efs.set_name=:p_set_name LOV custom Y select efs.set_name value, efs.description from eam_failure_sets efs order by efs.set_name US Set Name 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 SET_NAME Char varchar2 Name of the failure set (unique). Repeat across child rows that belong to the same set. Y 6 SET_DESCRIPTION Char varchar2 Description of the failure set. Processed on SET rows and on Create child rows when the failure set header does not exist yet. 7 SET_EFFECTIVE_END_DATE Date date Effective end date of the failure set. Processed on SET rows and on Create child rows when the failure set header does not exist yet. Blank = active. Set to sysdate to end-date the set (equivalent to delete, since Oracle does not provide Delete_FailureSet). 8 ROW_TYPE LOV custom varchar2 Y select 'SET' value, 'Failure Set Header' description from dual union all select 'ASSOCIATION' value, 'Asset Group Item Association' description from dual union all select 'COMBINATION' value, 'Failure / Cause / Resolution Combination' description from dual Row type: SET = failure set header; ASSOCIATION = set to asset-group item link; COMBINATION = failure/cause/resolution triple within a set. Y 9 INVENTORY_ITEM LOV custom varchar2 Y Y select msibk.inventory_item_id id, msibk.concatenated_segments value, msibk.description from mtl_system_items_b_kfv msibk, mtl_parameters mp where msibk.organization_id=mp.organization_id and mp.master_organization_id=mp.organization_id and msibk.eam_item_type in (1,3) order by msibk.concatenated_segments select msibk.inventory_item_id id, msibk.concatenated_segments value from mtl_system_items_b_kfv msibk, mtl_parameters mp where msibk.organization_id=mp.organization_id and mp.master_organization_id=mp.organization_id and msibk.eam_item_type in (1,3) Asset Group (EAM_ITEM_TYPE=1) or Rebuildable (EAM_ITEM_TYPE=3) item from the master item organization. Required on ASSOCIATION rows. 10 FAILURE_CODE_REQUIRED LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Yes: work order completions against the associated asset-group item must record a failure code. Blank = not required. Only used on ASSOCIATION rows. 11 ASSOCIATION_EFFECTIVE_END_DATE Date date Effective end date of the association. Blank = active. Only used on ASSOCIATION rows. 12 FAILURE_CODE LOV custom varchar2 Y select efc.failure_code value, efc.description from eam_failure_codes efc where nvl(efc.effective_end_date,sysdate+1)>trunc(sysdate) order by efc.failure_code Failure code. Must already exist in EAM Failure Codes setup. Required on COMBINATION rows. 13 CAUSE_CODE LOV custom varchar2 Y select ecc.cause_code value, ecc.description from eam_cause_codes ecc where nvl(ecc.effective_end_date,sysdate+1)>trunc(sysdate) order by ecc.cause_code Cause code. Must already exist in EAM Cause Codes setup. Required on COMBINATION rows. 14 RESOLUTION_CODE LOV custom varchar2 Y select erc.resolution_code value, erc.description from eam_resolution_codes erc where nvl(erc.effective_end_date,sysdate+1)>trunc(sysdate) order by erc.resolution_code Resolution code. Must already exist in EAM Resolution Codes setup. Required on COMBINATION rows. 15 COMBINATION_EFFECTIVE_END_DATE Date date Effective end date of the combination. Blank = active. Only used on COMBINATION rows. 16 DELETE_RECORD LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Set to Yes to delete the record. Only valid on ASSOCIATION and COMBINATION rows. SET rows cannot be deleted - end-date the set instead by setting Set Effective End Date. 17 UPLOAD_ROW Char number Y U 1 action_ in Char ACTION_CODE_ U 2 status_ out Char STATUS_CODE_ U 3 message_ out Char MESSAGE_ U 4 p_set_name in Char SET_NAME U 5 p_set_description in Char SET_DESCRIPTION U 6 p_set_effective_end_date in Date SET_EFFECTIVE_END_DATE U 7 p_row_type in Char ROW_TYPE U 8 p_inventory_item_id in Number INVENTORY_ITEM U 9 p_failure_code_required in Char FAILURE_CODE_REQUIRED U 10 p_assoc_effective_end_date in Date ASSOCIATION_EFFECTIVE_END_DATE U 11 p_failure_code in Char FAILURE_CODE U 12 p_cause_code in Char CAUSE_CODE U 13 p_resolution_code in Char RESOLUTION_CODE U 14 p_combo_effective_end_date in Date COMBINATION_EFFECTIVE_END_DATE U 15 p_delete_record in Char DELETE_RECORD Error SQL Order By set_name, decode(row_type,'SET',1,'ASSOCIATION',2,'COMBINATION',3,4), inventory_item, failure_code, cause_code, resolution_code Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.set_name, xu.set_description, xu.set_effective_end_date, xu.row_type, xu.inventory_item, xu.failure_code_required, xu.association_effective_end_date, xu.failure_code, xu.cause_code, xu.resolution_code, xu.combination_effective_end_date, xu.delete_record, xu.upload_row from xxen_eam_failure_sets_u_0610_u xu where xu.status_code_=xxen_upload.status_success Excel validation :$column$.cause_code CAUSE_CODE Excel validation :$column$.failure_code FAILURE_CODE Excel validation :$column$.inventory_item INVENTORY_ITEM Excel validation :$column$.resolution_code RESOLUTION_CODE Excel validation :$column$.row_type ROW_TYPE