OPRIDBUSINESS_UNITPROJECT_IDACTIVITY_IDBUSINESS_UNIT_CACONTRACT_NUMCONTRACT_LINE_NUMPC_CA_MARGN_VW3(SQL View) |
Index Back |
|---|---|
Contract Profit MarginView by PC BU, Project, Activity, CA BU, Contract Num and Line Num. |
| SELECT B.OPRID , A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.BUSINESS_UNIT_CA , A.CONTRACT_NUM , A.CONTRACT_LINE_NUM , A.CUST_ID , A.NAME1 , SUM(A.RESOURCE_AMOUNT) , SUM(A.BUDGET_AMOUNT) , SUM(A.PC_ANLTC_ACT_COST) , SUM(A.PC_ANLTC_ACT_REV) , SUM(A.PC_ANLTC_ACT_REV - A.PC_ANLTC_ACT_COST) , CASE WHEN SUM(A.PC_ANLTC_ACT_REV) <> 0 THEN %Round(%DecMult(%DecDiv(SUM(A.PC_ANLTC_ACT_REV - A.PC_ANLTC_ACT_COST), SUM(A.PC_ANLTC_ACT_REV)), 100), 2) ELSE 0 END , SUM(A.BUDGET_AMOUNT - A.PC_ANLTC_ACT_COST) , A.CURRENCY_CD , RPT.PC_GROUP_NAME , RPT.DISPLAY_ORDER FROM PS_PROJ_RES_SUM A , PS_PC_OPRID_PRJACT B , PS_PC_RPT_GROUPS RPT WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND RPT.FEATURE_CD = 'PC_CA_MARG' AND A.CONTRACT_NUM <> ' ' AND A.RESOURCE_AMOUNT <> 0 GROUP BY B.OPRID, A.BUSINESS_UNIT, A.PROJECT_ID, A.ACTIVITY_ID, A.BUSINESS_UNIT_CA, A.CONTRACT_NUM, A.CONTRACT_LINE_NUM, A.CUST_ID, A.NAME1, A.CURRENCY_CD, RPT.PC_GROUP_NAME, RPT.DISPLAY_ORDER, RPT.FROM_RANGE, RPT.TO_RANGE HAVING (SUM(A.PC_ANLTC_ACT_REV) <> 0 AND %Round(%DecMult(%DecDiv(SUM(A.PC_ANLTC_ACT_REV - A.PC_ANLTC_ACT_COST), SUM(A.PC_ANLTC_ACT_REV)), 100), 2) BETWEEN RPT.FROM_RANGE AND RPT.TO_RANGE) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 3 | PROJECT_ID |
Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 4 | ACTIVITY_ID |
Character(15) | VARCHAR2(15) NOT NULL | Activity ID |
| 5 | BUSINESS_UNIT_CA |
Character(5) | VARCHAR2(5) NOT NULL | Contracts Business Unit |
| 6 | CONTRACT_NUM |
Character(25) | VARCHAR2(25) NOT NULL | Contract |
| 7 | CONTRACT_LINE_NUM |
Number(3,0) | SMALLINT NOT NULL | Contract Line Num |
| 8 | CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations. |
| 9 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
| 10 | RESOURCE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Transaction Amount |
| 11 | BUDGET_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | BUDGET_AMT |
| 12 | PC_ANLTC_ACT_COST | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Profitability Actual Cost |
| 13 | PC_ANLTC_ACT_REV | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Profitability Actual Revenue |
| 14 | MARGIN | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Margin |
| 15 | PROFIT_MARGIN | Signed Number(27,2) | DECIMAL(25,2) NOT NULL | Profit Margin Percent |
| 16 | VAR_AMOUNT_01 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account. |
| 17 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 18 | PC_GROUP_NAME | Character(30) | VARCHAR2(30) NOT NULL | Analytics Grouping Name |
| 19 | DISPLAY_ORDER | Number(3,0) | SMALLINT NOT NULL | Display order for ordering items in a list - web style |