10 queries found for "Lookup Field Value Language"

Get User Group Access Modes on a specific Application Service

Select DESCR 
From CI_LOOKUP_VAL_L, SC_ACCESS_CNTL 
Where SC_ACCESS_CNTL.ACCESS_MODE = CI_LOOKUP_VAL_L.FIELD_VALUE
And CI_LOOKUP_VAL_L.FIELD_NAME = 'ACCESS_MODE'
And CI_LOOKUP_VAL_L.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
And SC_ACCESS_CNTL.APP_SVC_ID = 'INSERT_APPLICATION_SERVICE_ID'
And SC_ACCESS_CNTL.USR_GRP_ID = 'INSERT_USER_GROUP_ID'

uploaded by Nuno Rodrigues

Get Bill Segments for Service Agreement

SELECT
  BS.START_DT,
  BS.END_DT,
  BS.BSEG_STAT_FLG,
  BS.BSEG_ID,
  BS.BILL_ID,
  BS.SA_ID,
  NM.ENTITY_NAME
, SA.ACCT_ID,
  LK.DESCR
FROM
  CI_PER_NAME NM,
   CI_ACCT_PER AC,
   CI_SA SA,
   CI_BSEG BS,
   CI_LOOKUP_VAL_L  LK
WHERE
  BS.SA_ID = 'INSERT_SA_ID'
   AND SA.SA_ID = BS.SA_ID
   AND AC.ACCT_ID = SA.ACCT_ID
   AND AC.MAIN_CUST_SW = 'Y'
   AND NM.PER_ID = AC.PER_ID
   AND NM.PRIM_NAME_SW = 'Y'
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CODE_MOSTLY_ENG'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
ORDER BY BS.START_DT DESC

uploaded by Nuno Rodrigues

Get Bill Segments for Person

SELECT
    BS.START_DT,
    BS.END_DT,
    BS.BSEG_STAT_FLG,
    BS.BSEG_ID,
    BS.BILL_ID,
    BS.SA_ID,
    NM.ENTITY_NAME,  
 SA.ACCT_ID,
LK.DESCR
 FROM
    CI_PER_NAME NM,
     CI_ACCT_PER AC,
     CI_SA SA,
     CI_BSEG BS,
    CI_LOOKUP_VAL_L LK
 WHERE
    NM.ENTITY_NAME_UPR LIKE UPPER('INSERT_PERSON_NAME')
     AND AC.PER_ID = NM.PER_ID
     AND SA.ACCT_ID = AC.ACCT_ID
     AND BS.SA_ID = SA.SA_ID
     AND BS.BILL_ID <> ' '
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
ORDER BY 
   NM.ENTITY_NAME_UPR
    , BS.START_DT DESC
    , BS.SA_ID

uploaded by Nuno Rodrigues

Get Bill Segments for Account

SELECT
    BS.START_DT,
    BS.END_DT,
    BS.BSEG_STAT_FLG,
    BS.BSEG_ID,
    BS.BILL_ID,
    BS.SA_ID,
    NM.ENTITY_NAME,
    LK.DESCR
 FROM
    CI_PER_NAME NM,
     CI_ACCT_PER AC,
     CI_SA SA,
     CI_BSEG BS,
    CI_LOOKUP_VAL_L  LK
 WHERE
    SA.ACCT_ID = 'INSERT_ACCOUNT_ID'
     AND AC.ACCT_ID = SA.ACCT_ID
     AND AC.MAIN_CUST_SW = 'Y'
     AND NM.PER_ID = AC.PER_ID
     AND NM.PRIM_NAME_SW = 'Y'
     AND BS.SA_ID = SA.SA_ID
     AND BS.BILL_ID <> ' '
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
ORDER BY 
    BS.START_DT DESC
    , BS.SA_ID

uploaded by Nuno Rodrigues

Get Bills for Customer

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  NM.ENTITY_NAME,
  BI.DUE_DT,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
   CI_ACCT_PER AP,
   CI_PER_NAME NM,
   CI_LOOKUP_VAL_L LK
WHERE
  NM.ENTITY_NAME_UPR LIKE UPPER('INSERT_CUSTOMER_NAME')
   AND BI.ACCT_ID = AP.ACCT_ID
   AND AP.PER_ID = NM.PER_ID
   AND LK.FIELD_NAME = 'BILL_STAT_FLG'
   AND LK.LANGUAGE_CD = :LANGUAGE
   AND LK.FIELD_VALUE = BI.BILL_STAT_FLG
   ORDER BY NM.ENTITY_NAME_UPR ASC
  , BI.ACCT_ID ASC
  ,  BI.BILL_DT DESC
  , BI.CRE_DTTM DESC

uploaded by Nuno Rodrigues

Get Bills for Account

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  NM.ENTITY_NAME,
  BI.DUE_DT,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
   CI_ACCT_PER AP,
   CI_PER_NAME NM,
   CI_LOOKUP_VAL_L LK
WHERE
  BI.ACCT_ID = 'INSERT_ACCOUNT_ID'
   AND BI.ACCT_ID = AP.ACCT_ID
   AND AP.MAIN_CUST_SW = 'Y'
   AND NM.NAME_TYPE_FLG = 'PRIM'
   AND AP.PER_ID = NM.PER_ID
  AND LK.FIELD_NAME = 'BILL_STAT_FLG'
  AND LK.LANGUAGE_CD = :LANGUAGE
  AND LK.FIELD_VALUE = BI.BILL_STAT_FLG   
ORDER BY BI.ACCT_ID ASC
  , BI.BILL_DT DESC
  , BI.CRE_DTTM DESC
  

uploaded by Nuno Rodrigues

Get Bill with specific Characteristic value

SELECT
  BI.ACCT_ID,
  BI.BILL_STAT_FLG,
  BI.BILL_DT,
  BI.BILL_ID,
  BI.CRE_DTTM,
  BI.DUE_DT,  
  NM.ENTITY_NAME,
  LK.DESCR AS BILL_STAT_DESCR
FROM
  CI_BILL BI,
  CI_ACCT_PER AP,
  CI_PER_NAME NM,
  CI_BILL_CHAR BIC,
  CI_CHAR_TYPE CHTY,
   CI_LOOKUP_VAL_L LK
WHERE
  BI.ACCT_ID = AP.ACCT_ID
  AND AP.MAIN_CUST_SW = 'Y'
  AND NM.NAME_TYPE_FLG =   'PRIM'
  AND AP.PER_ID = NM.PER_ID
  AND BI.BILL_ID = BIC.BILL_ID
  AND TRIM(BIC.CHAR_TYPE_CD) =  'INSERT_CHAR_TYPE_CD'
  AND TRIM(BIC.SRCH_CHAR_VAL) LIKE UPPER('INSERT_SEARCH_VALUE')
  AND CHTY.CHAR_TYPE_CD = BIC.CHAR_TYPE_CD
   AND LK.FIELD_NAME = 'BILL_STAT_FLG'
  AND LK.LANGUAGE_CD = :LANGUAGE
  AND LK.FIELD_VALUE = BI.BILL_STAT_FLG   
ORDER BY BI.ACCT_ID ASC
  , BI.BILL_DT DESC
  , BI.CRE_DTTM DESC  
  

uploaded by Nuno Rodrigues

Get Bill Segments for Bill

SELECT
    BS.START_DT,
    BS.END_DT,
    BS.BSEG_STAT_FLG,
    BS.BSEG_ID,
    BS.BILL_ID,
    BS.SA_ID,
    NM.ENTITY_NAME
   ,SA.ACCT_ID,
    LK.DESCR
 FROM
    CI_PER_NAME NM,
     CI_ACCT_PER AC,
     CI_SA SA,
     CI_BSEG BS,
    CI_LOOKUP_VAL_L  LK
 WHERE
    BS.BILL_ID = 'INSERT_BILL_ID'
     AND SA.SA_ID = BS.SA_ID
     AND AC.ACCT_ID = SA.ACCT_ID
     AND AC.MAIN_CUST_SW = 'Y'
     AND NM.PER_ID = AC.PER_ID
     AND NM.PRIM_NAME_SW = 'Y'
   AND LK.LANGUAGE_CD = 'INSERT_LANGUAGE_CD'
   AND LK.FIELD_NAME = 'BSEG_STAT_FLG'          
   AND LK.FIELD_VALUE = BS.BSEG_STAT_FLG
 ORDER BY 
    BS.START_DT DESC
    , BS.SA_ID

uploaded by Nuno Rodrigues

Customer Modification Schemas

SELECT F1S.SCHEMA_NAME,
  LVL.DESCR,
  F1S.SCHEMA_TYPE_FLG,
  F1S.SCHEMA_DEFN
FROM F1_SCHEMA F1S,
  CI_LOOKUP_VAL_L LVL
WHERE F1S.OWNER_FLG='CM'
AND LVL.FIELD_NAME ='SCHEMA_TYPE_FLG'
AND LVL.FIELD_VALUE=F1S.SCHEMA_TYPE_FLG

uploaded by Venkata Sai Bhargav Kakunuri

Retrieve all User Groups Information including Application Services [Can be used in CCB, MDM, ODM]

alter session set current_schema=CISADM;
SELECT
   TRIM(T1.USR_GRP_ID),
   TRIM(T1.USG_GRP_DESCR),
   TRIM(T2.APP_SVC_ID),
   TRIM(T3.APP_SVC_DESCR),
   T3.APP_SVC_EXP_DT,
   TRIM(T2.ACCESS_MODE),
   TRIM(T2.ACC_MODE_DESCR),
   T2.OWNER_FLG,
   T3.SC_TYPE_CD,
   TRIM(T3.SC_TYPE_DESCR),
   T3.AUTH_LEVEL_NBR,
   TRIM(T3.AUTH_LVL_DESCR)
FROM
   (
      SELECT
         USG.USR_GRP_ID,
         USGL.DESCR AS USG_GRP_DESCR 
      FROM
         CISADM.SC_USER_GROUP USG,
         CISADM.SC_USER_GROUP_L USGL 
      WHERE
         USG.USR_GRP_ID = USGL.USR_GRP_ID 
   )
   T1,
   (
      SELECT
         B.USR_GRP_ID,
         B.APP_SVC_ID,
         B.ACCESS_MODE,
         A.DESCR AS ACC_MODE_DESCR,
         B.OWNER_FLG 
      FROM
         CISADM.CI_LOOKUP_VAL_L A,
         CISADM.SC_ACCESS_CNTL B 
      WHERE
         B.ACCESS_MODE = A.FIELD_VALUE 
         AND A.FIELD_NAME = 'ACCESS_MODE' 
      ORDER BY
         B.USR_GRP_ID,
         B.APP_SVC_ID 
   )
   T2,
   (
      SELECT
         J1.USR_GRP_ID,
         J1.APP_SVC_ID,
         J1.APP_SVC_DESCR,
         J1.APP_SVC_EXP_DT,
         J2.SC_TYPE_CD,
         J2.SC_TYPE_DESCR,
         J2.AUTH_LEVEL_NBR,
         J2.AUTH_LVL_DESCR 
      FROM
         (
            SELECT
               USGP.USR_GRP_ID,
               USGP.APP_SVC_ID,
               ASL.DESCR AS APP_SVC_DESCR,
               USGP.EXPIRATION_DT AS APP_SVC_EXP_DT 
            FROM
               CISADM.SC_USR_GRP_PROF USGP,
               CISADM.SC_APP_SERVICE_L ASL 
            WHERE
               USGP.APP_SVC_ID = ASL.APP_SVC_ID 
         )
         J1 
         LEFT JOIN
            (
               SELECT
                  UGSC.USR_GRP_ID,
                  UGSC.APP_SVC_ID,
                  SCL.SC_TYPE_CD,
                  SCL.DESCR AS SC_TYPE_DESCR,
                  AL.AUTH_LEVEL_NBR,
                  AL.DESCR AS AUTH_LVL_DESCR 
               FROM
                  CISADM.CI_USR_GRP_SC UGSC,
                  CISADM.CI_SC_TYPE_L SCL,
                  CISADM.CI_SC_AUTH_LVL_L AL 
               WHERE
                  UGSC.SC_TYPE_CD = SCL.SC_TYPE_CD 
                  AND UGSC.SC_TYPE_CD = AL.SC_TYPE_CD 
                  AND SCL.SC_TYPE_CD = AL.SC_TYPE_CD 
                  AND UGSC.AUTH_LEVEL_NBR = AL.AUTH_LEVEL_NBR 
            )
            J2 
            ON J1.USR_GRP_ID = J2.USR_GRP_ID 
            AND J1.APP_SVC_ID = J2.APP_SVC_ID 
   )
   T3
WHERE
   T1.USR_GRP_ID = T2.USR_GRP_ID 
   AND T1.USR_GRP_ID = T3.USR_GRP_ID
   AND T2.USR_GRP_ID = T3.USR_GRP_ID
   AND T2.APP_SVC_ID = T3.APP_SVC_ID 
   AND T1.USR_GRP_ID IN 
   (
      'ALL_SERVICES'
   )
ORDER BY
   T1.USR_GRP_ID,
   T2.APP_SVC_ID,
   T2.ACCESS_MODE,
   T3.SC_TYPE_CD,
   T3.AUTH_LEVEL_NBR;

uploaded by Jessie Dean U. Tomas