CT_CORE_FN_AM_CAT_PRFILE_AUDIT

Purpose of Query

Category/Profile Mismatch - Query provides list of all in-service assets where the category and profile do not match.

Folder

AM

Tables

A.CTW_ASSET_COST - Asset Cost Rpt

SubQuery:

C.CTW_ASSET_COST - Asset Cost Rpt

MAX(DTTM_STAMP - Date/Time Stamp)

SubQuery:

B CTW_ASSET_RPR - Asset Repair Rpt

B.ASSET_ID exists

B.ASSET_STATUS - Asset Status in list ('I','M','A') – (In Service, Suspended, Received (Not in Service))

Note: A subquery is a query whose results are used by another query. The main query uses the subquery’s result set as a comparison value for a selection criterion.

Prompts

BUSINESS_UNIT - Business Unit= - (Equal To)

Sample Results:

Criteria

A.TRANS_TYPE - Action in list ('ADD','ADJ','REI','RET','TRF','RCT') – (Asset Addition, Asset Cost Adjustment, Asset Reinstatement, Asset Retirement, Asset Transfer, Asset Recategorization)

A.TRANS_IN_OUT - Transaction In/Out not equal to O

(A.CATEGORY - Asset Category equal to EQUIP

and

A.PROFILE_ID - Asset Profile ID not in list ('1AIRCRAFT','1BOAT','1BOOKS/MAP','1EQUIPMENT','1LIVESTOCK','1RAILCAR','1VEHICLES','ISEQUIP1','ISEQUIP10','ISEQUIP12','ISEQUIP15','ISEQUIP3','ISEQUIP4','ISEQUIP5','ISEQUIP66','ISEQUIP84','ISVEHICLES'))

OR

(A.CATEGORY - Asset Category equal to CNTRL

and

A.PROFILE_ID not equal to 1CNTRLITEM)

OR

(A.CATEGORY - Asset Category equal to ART

and

A.PROFILE_ID not equal to 1FINEART)

OR

(A.CATEGORY - Asset Category equal to BUILD

and

A.PROFILE_ID not in list ('1BUILDINGS','ISBUILD10','ISBUILD20','ISBUILD30'))

OR

(A.CATEGORY - Asset Category equal to CONST

and

A.PROFILE_ID not equal to 1CONSTRUCT)

OR

(A.CATEGORY - Asset Category equal to DCEQP

and

A.PROFILE_ID not in list ('ISDCEQP10','ISDCEQP15','ISDCEQP5'))

OR

(A.CATEGORY - Asset Category equal to DPEQP

and

A.PROFILE_ID not in list ('ISDPEQP1','ISDPEQP3','ISDPEQP4','ISDPEQP5'))

OR

(A.CATEGORY - Asset Category equal to DSOFT

and

A.PROFILE_ID not in list ('ISDSOFT1','ISDSOFT3','ISDSOFT5'))

OR

(A.CATEGORY - Asset Category equal to IMPRO

and

A.PROFILE_ID not equal to 1SITEIMPRO)

OR

(A.CATEGORY - Asset Category equal to INFRA

and

A.PROFILE_ID not in list ('1AIRLAND20','1RAIL/BRIG','1ROAD/BRIG','ISAIRLND15','ISAIRLND30','ISGLYCOL','ISSEWERSYS','ISWATERSYS'))

OR

(A.CATEGORY - Asset Category equal to LAND

and

A.PROFILE_ID not in list ('1LAND','ISLAND'))

OR

(A.CATEGORY - Asset Category equal to LAND

and

A.PROFILE_ID not in list ('1LAND','ISLAND'))

OR

(A.CATEGORY - Asset Category equal to LBUIL

and

A.PROFILE_ID not equal to 1LEASEDBUI)

OR

(A.CATEGORY - Asset Category equal to LCNTR

and

A.PROFILE_ID not equal to 1LCNTRL)

OR

(A.CATEGORY - Asset Category equal to LEQUI

and

A.PROFILE_ID not equal to 1LEASEDEQ )

OR

(A.CATEGORY - Asset Category equal to LIMPR

and

A.PROFILE_ID not equal to 1LEASEPROP)

OR

(A.CATEGORY - Asset Category equal to MDEQP

and

A.PROFILE_ID not equal to ISMDEQP3)

OR

(A.CATEGORY - Asset Category equal to OFEQP

and

A.PROFILE_ID not in list ('ISOFEQP1','ISOFEQP3','ISOFEQP5'))

OR

(A.CATEGORY - Asset Category equal to PDEQP

and

A.PROFILE_ID not equal to ISPDEQP1)

OR

(A.CATEGORY - Asset Category equal to POEQP

and

A.PROFILE_ID not equal to ISPOEQP1)

OR

(A.CATEGORY - Asset Category equal to PSOFT

and

A.PROFILE_ID not equal to ISPSOFT1)

OR

(A.CATEGORY - Asset Category equal to SOFT

and

A.PROFILE_ID not equal to 1SOFTWARE)

SUBQUERY-Equal to

A.DTTM_STAMP - Date/Time Stamp equal to Max(C.DTTM_STAMP - Date/Time Stamp)

SUBQUERY – (Exists)

B.ASSET_STATUS - Asset Status in list ('I','M','A') – (In Service, Suspended, Received (Not in Service))

Note: Runtime prompts are included as criteria.

Fields

Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / A.BUSINESS_UNIT - Business Unit / Char5 / Unit
2 / A.ASSET_ID - Asset Identification / Char12 / Asset ID
3 / A.TAG_NUMBER - Tag Number / Char12 / Tag Number
4 / A.CT_ASSET_DESCR - Asset Description / Char30 / Asset Descr
5 / A.COST - Total Cost / SNm25.3 / Sum / Sum TotalCost
6 / A.LOCATION - Location Code / Char10 / Max / Max Location
7 / A.CT_LOCATION_DESCR - Location Description / Char30 / Max / Max Location Descr
8 / A.PROFILE_ID - Asset Profile ID / Char10 / Profile ID
Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / B.ASSET_ID - Asset Identification / Char12 / Asset ID