ct_core_fin_am_assets_by_loc

Purpose of Query

In service assets by location - Query returns all in service assets by location. Prompts for business unit and location. The location prompt will accept the wildcard %.

Folder

AM

Two level union query.

A Union query combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The two queries must have the same number of columns and compatible data types to unite.

Tables

Top Level:

A CTW_ASSET_COST - Asset Cost Rpt

Join C. CTW_ASSET_LOCTN - Asset Location

Join E. CTW_LOCATION_FN - Financial Location

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 JOIN is an SQL command that is used to relate two or more data tables (Records in PeopleSoft) based on the use of related (key) fields from one table to the next. Once joined, data can be retrieved without repeating all of the data in every table.

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.

Union 1 Level:

D. CTW_ACST_NONCAP- Asset Cost Non CapTransactions

F.CTW_ASSET_LOCTN - Asset Location

G.CTW_LOCATION_FN - Financial Location

Prompts

BUSINESS_UNIT - Business Unit = - (Equal To)

LOCATION - Location like (%) – (Wildcard enabled)

Criteria

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

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

Subquery for exists C.ASSET_STATUS - Asset Status in list ('A','I','M')

Union 1 Level:

D.ASSET_STATUS - Asset Status in list ('A','I','M')

Note: Runtime prompts are included as criteria.

Fields

Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / A.ASSET_ID - Asset Identification / Char12 / Asset ID
2 / A.TAG_NUMBER - Tag Number / Char12 / Tag Number
3 / A.CT_ASSET_DESCR - Asset Description / Char30 / Asset Descr
4 / A.DEPTID - Department / Char10 / DeptID
5 / C.LOCATION - Location Code / Char10 / Location
6 / E.DESCR -Description / Char30 / Location Descr
7 / E.BUILDING - Building # / Char10 / Building
8 / E.FLOOR - Floor # / Char10 / Floor #
9 / E.JURISDICTION - Jurisdiction / Char5 / Jurisdictn
10 / E.SECTOR - Sector / Char10 / Sector
11 / A.IN_SERVICE_DT - In Service Date / Date / In Service
12 / A.COST - Total Cost / SNm25.3 / Sum / Sum Total Cost