4 queries found for "Characteristic Type"

Get all from Characteristic Type

select * 
from CI_CHAR_TYPE

uploaded by Nuno Rodrigues

Get specific Characteristic Type

select * 
from CI_CHAR_TYPE
where 
CHAR_TYPE_CD = 'XXXX'

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

Retrieve Account Effective Characteristics

SELECT
C.CHAR_TYPE_CD,    
C.DESCR,
A.CHAR_VAL,
A.ADHOC_CHAR_VAL,
A.CHAR_VAL_FK1,
A.CHAR_VAL_FK2,
A.CHAR_VAL_FK3,
A.CHAR_VAL_FK4,
A.CHAR_VAL_FK5
FROM
    CI_ACCT_CHAR A, 
    CI_CHAR_TYPE B,
    CI_CHAR_TYPE_L C
WHERE
    A.ACCT_ID = 'INSERT_ACCOUNT_ID'
    -- Can include a specific type
    [ AND A.CHAR_TYPE_CD = 'INSERT_CHAR_TYPE']
    AND B.CHAR_TYPE_CD = A.CHAR_TYPE_CD
    AND B.CHAR_TYPE_CD = C.CHAR_TYPE_CD
    AND C.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
    AND A.EFFDT = (SELECT MAX(D.EFFDT) 
                          FROM CI_ACCT_CHAR D
                         WHERE D.ACCT_ID = A.ACCT_ID
                         AND D.CHAR_TYPE_CD = A.CHAR_TYPE_CD
                           AND D.EFFDT <= SYSDATE)
                          

uploaded by Nuno Rodrigues