GPFR_DSN_DUCS

(SQL View)
 Index Back

DSN DUCS Data

DSN DUCS Data

SELECT C.PIN_NM , D.PIN_NM , COALESCE(( SELECT X.PIN_NM FROM PS_GP_PIN X WHERE X.PIN_NUM = D.PIN_USER_FLD1_NUM), ' ') AS "USER FIELD 1" , COALESCE(( SELECT X.SOVR_VAL_CHAR FROM PS_GP_ELM_DFN_SOVR X WHERE D.PIN_NUM = X.PIN_NUM AND X.PIN_SOVR_NUM = D.PIN_USER_FLD1_NUM AND X.BGN_DT = ( SELECT MAX(X1.BGN_DT) FROM PS_GP_ELM_DFN_SOVR X1 WHERE X1.PIN_NUM = X.PIN_NUM AND X1.PIN_SOVR_NUM = X.PIN_SOVR_NUM AND X1.BGN_DT <= %CurrentDateIn)), ' ') "VALUE FIELD 1" , COALESCE(( SELECT X.PIN_NM FROM PS_GP_PIN X WHERE X.PIN_NUM = D.PIN_USER_FLD2_NUM), ' ') "USER FIELD 2" , COALESCE(( SELECT X.SOVR_VAL_CHAR FROM PS_GP_ELM_DFN_SOVR X WHERE D.PIN_NUM = X.PIN_NUM AND X.PIN_SOVR_NUM = D.PIN_USER_FLD2_NUM AND X.BGN_DT = ( SELECT MAX(X1.BGN_DT) FROM PS_GP_ELM_DFN_SOVR X1 WHERE X1.PIN_NUM = X.PIN_NUM AND X1.PIN_SOVR_NUM = X.PIN_SOVR_NUM AND X1.BGN_DT <= %CurrentDateIn)), ' ') "VALUE FIELD 2" , COALESCE(( SELECT X.PIN_NM FROM PS_GP_PIN X WHERE X.PIN_NUM = D.PIN_USER_FLD3_NUM), ' ') "USER FIELD 3" ,COALESCE(( SELECT X.SOVR_VAL_CHAR FROM PS_GP_ELM_DFN_SOVR X WHERE D.PIN_NUM = X.PIN_NUM AND X.PIN_SOVR_NUM = D.PIN_USER_FLD3_NUM AND X.BGN_DT = ( SELECT MAX(X1.BGN_DT) FROM PS_GP_ELM_DFN_SOVR X1 WHERE X1.PIN_NUM = X.PIN_NUM AND X1.PIN_SOVR_NUM = X.PIN_SOVR_NUM AND X1.BGN_DT <= %CurrentDateIn)), ' ') "VALUE FIELD 3" , E.GP_ELN_ATTR1 "TARGET 1" , E.GP_ELN_ATTR2 "TARGET 2" , E.GP_ELN_ATTR3 "ATTRIBUTE" , (CASE WHEN E.GP_ELN_ATTR4 = 1 THEN 'Add' ELSE CASE WHEN E.GP_ELN_ATTR4 = -1 THEN 'Sub' ELSE ' ' END END ) "UNIT" , (CASE WHEN E.GP_ELN_ATTR5 = 1 THEN 'Add' ELSE CASE WHEN E.GP_ELN_ATTR5 = -1 THEN 'Sub' ELSE ' ' END END ) "BASE" , (CASE WHEN E.GP_ELN_ATTR6 = 1 THEN 'Add' ELSE CASE WHEN E.GP_ELN_ATTR6 = -1 THEN 'Sub' ELSE ' ' END END ) "UNIT + BASE" , (CASE WHEN E.GP_ELN_ATTR7 = 1 THEN 'Add' ELSE CASE WHEN E.GP_ELN_ATTR7 = -1 THEN 'Sub' ELSE ' ' END END ) "RATE" , (CASE WHEN E.GP_ELN_ATTR8 = 1 THEN 'Add' ELSE CASE WHEN E.GP_ELN_ATTR8 = -1 THEN 'Sub' ELSE ' ' END END ) "PERCENT" , (CASE WHEN E.GP_ELN_ATTR9 = 1 THEN 'Add' ELSE CASE WHEN E.GP_ELN_ATTR9 = -1 THEN 'Sub' ELSE ' ' END END ) "AMOUNT" , E.GP_ELN_ATTR10 FROM PS_GP_ELEM_GRP A, PS_GP_ELEM_GRP_MBR B, PS_GP_PIN C, PS_GP_PIN D, PS_GP_ELN_PIN_ATTR E WHERE 1 = 1 AND A.PIN_NUM = C.PIN_NUM AND A.PIN_NUM = B.PIN_NUM AND B.PIN_ELEM_NUM = D.PIN_NUM AND C.PIN_NM = 'S21.G00.23' AND A.EFFDT = B.EFFDT AND B.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_GP_ELEM_GRP X WHERE A.PIN_NUM = X.PIN_NUM) AND E.COUNTRY = 'FRA' AND E.GP_ELN_SET = 'DSN' AND A.PIN_NUM = E.PIN_ELEM_GRP_NUM AND E.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_GP_ELN_PIN_ATTR X WHERE X.GP_ELN_SET = E.GP_ELN_SET) AND B.PIN_ELEM_NUM = E.PIN_NUM

  • Related Language Record: GPFR_DSNDUC_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 GPFR_ELEMENT_GROUP Character(30) VARCHAR2(30) NOT NULL Element Group
    2 GPFR_ELEMT_GRP_MBR Character(30) VARCHAR2(30) NOT NULL Member
    3 PIN_CUSTOM1 Character(20) VARCHAR2(20) NOT NULL Customer FIelds
    4 SOVR_VAL_CHAR_1 Character(25) VARCHAR2(25) NOT NULL SOVR Character Value used in Bulk Positive Input
    5 PIN_CUSTOM2 Character(20) VARCHAR2(20) NOT NULL PIN_CUSTOM2
    6 SOVR_VAL_CHAR_2 Character(25) VARCHAR2(25) NOT NULL SOVR Character Value used in Bulk Positive Input
    7 PIN_CUSTOM3 Character(20) VARCHAR2(20) NOT NULL PIN_CUSTOM3 fields
    8 SOVR_VAL_CHAR_3 Character(25) VARCHAR2(25) NOT NULL SOVR Character Value used in Bulk Positive Input
    9 GP_ELN_ATTR1 Character(20) VARCHAR2(20) NOT NULL ELN Attribute
    10 GP_ELN_ATTR2 Character(20) VARCHAR2(20) NOT NULL ELN Attribute
    11 GP_ELN_ATTR3 Character(20) VARCHAR2(20) NOT NULL ELN Attribute
    12 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    13 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    14 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    15 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    16 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    17 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    18 GP_ELN_ATTR10 Character(20) VARCHAR2(20) NOT NULL ELN Attribute