SQL for Oracle Utilities Applications

Get payment

SELECT 
    PE.PAY_DT,
    PY.PAY_AMT,
    PY.PAY_ID,
    PE.PAY_EVENT_ID
FROM
    CI_PAY PY
WHERE 
    PY.ACCT_ID = :1
    AND PY.PAY_STATUS_FLG = :2
    AND PY.PAY_EVENT_ID = PE.PAY_EVENT_ID
ORDER BY
    PE.PAY_DT DESC
--
-- :1 – Account Id
-- :2 – Payment Status = 50 (Frozen)

Ft to bseg calc lines

SELECT sa.ACCT_ID, ft.ACCOUNTING_DT, ft.SA_ID, ft.FT_ID,ft.PARENT_ID,ft.SIBLING_ID,CUR_AMT
,bline.CALC_AMT,  bline.DST_ID, bline.BILL_SQ
, bline.DESCR_ON_BILL, BSEG.SA_ID ,bline.APP_IN_SUMM_SW
	FROM ods.CI_FT ft
	INNER JOIN ods.CI_BSEG bseg ON ft.SA_ID=BSEG.SA_ID AND ft.PARENT_ID = BSeg.BILL_ID
	INNER JOIN ods.CI_BSEG_CALC_LN bLine ON bseg.BSEG_ID = bline.BSEG_ID AND bline.DST_ID!=' '
	INNER JOIN ods.CI_SA sa ON sa.SA_ID = ft.SA_ID
	WHERE  ft.PARENT_ID = 'your bill id'

Total Generated Charges for a Bill

SELECT sum(cur_amt),
  bill_id
FROM cisadm.ci_ft
WHERE redundant_sw= 'N'
AND freeze_sw     = 'Y'
AND parent_id     = 'INSERT_BILL_ID'
GROUP BY bill_id;

Retrieve Account's Last Bill Details

SELECT bil1.acct_id,
  bil1.due_dt last_bill_due_dt,
  bil1.bill_id last_bill_id,
  bil1.BILL_DT LAst_bill_dt,
  ACCT.CUST_CL_CD
FROM ci_bill bil1,
  ci_acct acct
WHERE bil1.acct_id = 'INSERT_ACCOUNT_ID'
AND bil1.bill_dt =
  (SELECT MAX(BILL_DT)
  FROM ci_bill bil2
  WHERE bil2.acct_id = bil1.acct_id
  )
AND bil1.acct_id = acct.acct_id;

Retrieve To Do Counts

SELECT  /*+ PARALLEL(4) */
        TD.TD_TYPE_CD,
        TD.TD_ENTRY_ID,
        MSL.MESSAGE_TEXT,
        TD.ENTRY_STATUS_FLG,
        TD.BATCH_CD,
        LANG.DESCR,
        TD.MESSAGE_NBR,
        TD.MESSAGE_CAT_NBR
FROM
        CISADM.CI_TD_ENTRY TD,
        CISADM.CI_TD_TYPE_L LANG,
        CISADM.CI_MSG_L MSL
        WHERE TD.TD_TYPE_CD = LANG.TD_TYPE_CD
        AND MSL.MESSAGE_CAT_NBR = TD.MESSAGE_CAT_NBR
        AND MSL.MESSAGE_NBR = TD.MESSAGE_NBR
        AND TD.ROLE_ID = 'ITADMIN'
        AND TD.ENTRY_STATUS_FLG <> 'C'
ORDER BY
        MSL.MESSAGE_TEXT;