AM_ED_PEND_COST(SQL View) |
Index Back |
|---|---|
AM Pending Costs in PCEndeca ETL view to get pending costs in PC not yet interfaced to AM |
| SELECT A.BUSINESS_UNIT_AM , A.ASSET_ID , A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.RESOURCE_TYPE , A.RESOURCE_CATEGORY , A.RESOURCE_SUB_CAT , A.ASST_REVIEW_STATUS , A.TRANS_DT , A.ACCOUNTING_DT , A.DESCR , A.INTFC_TYPE , A.LOAD_STATUS , A.INTFC_ID , A.INTFC_LINE_NUM , I.PCCF_PASSTHRU_SW , A.RESOURCE_AMOUNT FROM PS_PROJ_AM A , PS_INSTALLATION_AM I WHERE A.ASST_REVIEW_STATUS <> 'R' AND NOT EXISTS ( SELECT 'X' FROM PS_ASSET_ACQ_DET B , PS_COST C WHERE B.PROJECT_ID = A.PROJECT_ID AND B.BUSINESS_UNIT_PC = C.BUSINESS_UNIT_PC AND B.ACTIVITY_ID = C.ACTIVITY_ID AND B.RESOURCE_TYPE = C.RESOURCE_TYPE AND B.RESOURCE_CATEGORY = C.RESOURCE_CATEGORY AND B.RESOURCE_SUB_CAT = C.RESOURCE_SUB_CAT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.ASSET_ID = C.ASSET_ID AND C.DTTM_STAMP = A.DTTM_STAMP AND ( (I.PCCF_PASSTHRU_SW = 'N') OR (I.PCCF_PASSTHRU_SW = 'A' AND B.BUSINESS_UNIT_PC = A.BUSINESS_UNIT AND B.ACTIVITY_ID = A.ACTIVITY_ID) OR ( I.PCCF_PASSTHRU_SW = 'Y' AND B.BUSINESS_UNIT_PC = A.BUSINESS_UNIT AND B.ACTIVITY_ID = A.ACTIVITY_ID AND B.RESOURCE_TYPE = A.RESOURCE_TYPE AND B.RESOURCE_CATEGORY = A.RESOURCE_CATEGORY AND B.RESOURCE_SUB_CAT = A.RESOURCE_SUB_CAT)) AND B.BUSINESS_UNIT = A.BUSINESS_UNIT_AM AND B.ASSET_ID = A.ASSET_ID AND B.SYSTEM_SOURCE = 'BPC') |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 3 | BUSINESS_UNIT_PC | Character(5) | VARCHAR2(5) NOT NULL | PC Business Unit |
| 4 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 5 | ACTIVITY_ID | Character(15) | VARCHAR2(15) NOT NULL | Activity ID |
| 6 | RESOURCE_TYPE | Character(5) | VARCHAR2(5) NOT NULL | Source Type |
| 7 | RESOURCE_CATEGORY | Character(5) | VARCHAR2(5) NOT NULL | Category |
| 8 | RESOURCE_SUB_CAT | Character(5) | VARCHAR2(5) NOT NULL | Subcategory |
| 9 | ASST_REVIEW_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Asset review status
A=Approved D=Distributed E=Reverse H=Hold N=New R=Reject V=Reversed |
| 10 | TRANS_DT | Date(10) | DATE | Transaction Date |
| 11 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |
| 12 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 13 | INTFC_TYPE | Character(3) | VARCHAR2(3) NOT NULL | Trans Load Type |
| 14 | LOAD_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
/* -> JMZ. PSOFT. 11-OCT-2002 */
New Status were added : HLD and INP for Asset Management.
/* <- JMZ. PSOFT. 11-OCT-2002 */
CON=Consolidated DON=Loaded ERR=Errored EXC=Excluded HLD=On Hold INP=In Process NEW=Pending PGI=PO Grp Incomplete REP=Replaced UNI=Unitized |
| 15 | INTFC_ID | Number(8,0) | INTEGER NOT NULL | Interface ID |
| 16 | INTFC_LINE_NUM | Number(8,0) | INTEGER NOT NULL | Interface Line Number |
| 17 | PCCF_PASSTHRU_SW | Character(1) | VARCHAR2(1) NOT NULL |
Project Chartfield Detail
A=Project ID through Activity ID N=Project ID Only Y=Retain All |
| 18 | RESOURCE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Transaction Amount |