3 queries found for "Lookup"

Financial Transactions on Bill created by Offcyle Generator

SELECT FT.FT_ID,
       FT.SA_ID,
       FT.ACCOUNTING_DT,
       FT.CUR_AMT,
       FT.TOT_AMT,
       FT.FT_TYPE_FLG,
       LK2.DESCR SHOW_ON_BILL_DESCR,
       FT.CURRENCY_CD,
       LK1.DESCR FT_TYPE_DESCR
FROM CI_FT FT,
     CI_LOOKUP LK1,
     CI_LOOKUP LK2,
     CI_BILL BI
WHERE BI.OFFCYC_BGEN_ID = 'INSERT_OFFCYC_BGEN_ID'
AND FT.BILL_ID   = BI.BILL_ID
AND LK1.FIELD_NAME = 'FT_TYPE_FLG'
AND LK1.FIELD_VALUE = FT.FT_TYPE_FLG
AND LK1.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
AND LK2.FIELD_NAME = 'F1_YESNO_FLG'
AND LK2.FIELD_VALUE = FT.SHOW_ON_BILL_SW
AND LK2.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
ORDER BY FT.ACCOUNTING_DT, FT.SA_ID, FT.FT_ID

uploaded by Nuno Rodrigues

Financial Transactions on Bill

SELECT FT.FT_ID,
       FT.SA_ID,
       FT.ACCOUNTING_DT,
       FT.CUR_AMT,
       FT.TOT_AMT,
       FT.FT_TYPE_FLG,
       LK2.DESCR SHOW_ON_BILL_DESCR,
       FT.CURRENCY_CD,
       LK1.DESCR FT_TYPE_DESCR
FROM CI_FT FT,
     CI_LOOKUP LK1,
     CI_LOOKUP LK2,
     CI_BILL BI
WHERE BI.BILL_ID = 'INSERT_BILL_ID'
AND FT.BILL_ID   = BI.BILL_ID
AND LK1.FIELD_NAME = 'FT_TYPE_FLG'
AND LK1.FIELD_VALUE = FT.FT_TYPE_FLG
AND LK1.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
AND LK2.FIELD_NAME = 'F1_YESNO_FLG'
AND LK2.FIELD_VALUE = FT.SHOW_ON_BILL_SW
AND LK2.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
ORDER BY FT.ACCOUNTING_DT, FT.SA_ID, FT.FT_ID

uploaded by Nuno Rodrigues

Run details of batches for a particular day.

SELECT X.BATCH_CD,
       X.BATCH_NBR,
       COUNT(DISTINCT X.BATCH_THREAD_NBR) THREADS,
       X.STATUS,
       SUM(X.PROC_CNT) PROC_CNT,
       SUM(X.ERR_CNT) ERR_CNT,
       STARTTIME.DATETIME START_TM,
       ENDTIME.DATETIME END_TM,
       ROUND(( TO_DATE (ENDTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS') - TO_DATE (STARTTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS')) * 1440,2) DURATION_MIN,
       ENDTIME.TIME_CD
  FROM 
  (
  SELECT BR.BATCH_CD, BR.BATCH_NBR, BI.BATCH_THREAD_NBR, SUBSTR(L.DESCR,0,1) STATUS, BI.REC_PROC_CNT PROC_CNT, BI.REC_ERR_CNT ERR_CNT
    FROM CISADM.CI_BATCH_RUN BR,
         CISADM.CI_BATCH_THD BT,
         CISADM.CI_BATCH_INST BI,
         CISADM.CI_LOOKUP L,
         CISADM.CI_MSG_LOG ML,
         CISADM.CI_MSG_LOGPARM MLP 
   WHERE BR.BATCH_BUS_DT = to_date('01-07-15','MM-DD-YY') --Search By This Date
     AND BT.BATCH_CD = BR.BATCH_CD
     AND BT.BATCH_NBR = BR.BATCH_NBR
     AND BI.BATCH_CD = BR.BATCH_CD
     AND BI.BATCH_NBR = BT.BATCH_NBR
     AND L.FIELD_NAME = 'RUN_STATUS'
     AND L.FIELD_VALUE = BR.RUN_STATUS
     AND BI.SCHEDULER_ID = ML.SCHEDULER_ID
     AND ML.MESSAGE_NBR IN (11913, 12114, 12115, 90001, 90003, 90004)
     AND BI.SCHEDULER_ID = MLP.SCHEDULER_ID 
     AND MLP.MESSAGE_SEQ = ML.MESSAGE_SEQ 
     AND MLP.MESSAGE_PARM LIKE '2___-__-__-__.__.__' 
  GROUP BY BR.BATCH_CD, BR.BATCH_NBR, BI.BATCH_THREAD_NBR, L.DESCR, BI.REC_PROC_CNT, BI.REC_ERR_CNT 
  ORDER BY BR.BATCH_CD
  ) X,
  (
  SELECT I.BATCH_CD, I.BATCH_NBR, MIN(to_date(M.MESSAGE_PARM,'YYYY-MM-DD-HH24.MI.SS')) DATETIME, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown') TIME_CD 
    FROM CISADM.CI_BATCH_INST I, 
         CISADM.CI_MSG_LOGPARM M, 
         CISADM.CI_MSG_LOG L, 
         CISADM.CI_BATCH_RUN R 
   WHERE I.SCHEDULER_ID = M.SCHEDULER_ID 
     AND I.SCHEDULER_ID = L.SCHEDULER_ID 
     AND I.BATCH_CD = R.BATCH_CD 
     AND I.BATCH_NBR = R.BATCH_NBR 
     AND M.MESSAGE_SEQ = L.MESSAGE_SEQ 
     AND L.MESSAGE_NBR IN (11913, 90001) 
     AND M.MESSAGE_PARM LIKE '2___-__-__-__.__.__' 
   GROUP BY I.BATCH_CD, I.BATCH_NBR, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown')
  )STARTTIME,
  (
  SELECT I.BATCH_CD, I.BATCH_NBR, MAX(to_date(M.MESSAGE_PARM,'YYYY-MM-DD-HH24.MI.SS')) DATETIME, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown') TIME_CD 
    FROM CISADM.CI_BATCH_INST I, 
         CISADM.CI_MSG_LOGPARM M, 
         CISADM.CI_MSG_LOG L, 
         CISADM.CI_BATCH_RUN R 
   WHERE I.SCHEDULER_ID = M.SCHEDULER_ID 
     AND I.SCHEDULER_ID = L.SCHEDULER_ID 
     AND I.BATCH_CD = R.BATCH_CD 
     AND I.BATCH_NBR = R.BATCH_NBR 
     AND M.MESSAGE_SEQ = L.MESSAGE_SEQ 
     AND L.MESSAGE_NBR IN (12114, 12115, 90003, 90004) 
     AND M.MESSAGE_PARM LIKE '2___-__-__-__.__.__' 
   GROUP BY I.BATCH_CD, I.BATCH_NBR, DECODE (L.MESSAGE_NBR, 11913,'Start' ,12114,'Ended Normally' ,12115,'Ended Abnormally', 90001,'Start' ,90003,'Ended Normally' ,90004,'Ended Abnormally', 'Unknown')
  )ENDTIME
WHERE STARTTIME.BATCH_CD = X.BATCH_CD
  AND STARTTIME.BATCH_NBR = X.BATCH_NBR
  AND ENDTIME.BATCH_CD = X.BATCH_CD
  AND ENDTIME.BATCH_NBR = X.BATCH_NBR
GROUP BY X.BATCH_CD, 
  X.BATCH_NBR, 
  X.STATUS, 
  ROUND ( ( TO_DATE (ENDTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS') - TO_DATE (STARTTIME.DATETIME, 'YYYY-MM-DD-HH24.MI.SS')) * 1440,2), 
  ENDTIME.TIME_CD, 
  STARTTIME.DATETIME, 
  ENDTIME.DATETIME
ORDER BY X.BATCH_CD
;

uploaded by Vinayak Gadgil