CC&B Financial Concept and queries.

CC&B Financial Concept and queries.

Today I am going to teach how to retrieve financial information from CC&B. We focus on financial Transactions related to billing, Adjustment (Debit Note, Credit Note), Payments, Outstanding and different distributions for all these segments.

In CI_FT table we have all financial transactions which are segregated by FT_TYPE_FLG. 

Following is the description of each FT_TYPE_FLG code.

BS = 'Bill Segment'

BX = 'Bill Cancelation'

PS = 'Payment Segment'

PX = 'Payment Cancelation'

AD = 'Adjustment Segment'

AX = 'Adjustment Cancelation'

There is also a Switch name FREEZE_SW. If this switch is 'Y' Means transaction is Freeze (Posted) and Un-Freeze (Un-posted) otherwise.

ACCOUNTING_DT shows the financial Transaction Date and CUR_AMT shows the transaction amount. Now let's talk about the relation of CI_FT with other tables.

Relationship with Billing

We have a billing table CI_BILL and a child table which contains different segment of a bill with the name of CI_BSEG. The relation between CI_BILL and CI_BSEG is one to many which means you may have one or more segments against each bill. Following is the query to understand logical relationship between Bill and Bill Segment table.

SELECT *

FROM CI_BILL CB

INNER JOIN CI_BSEG CBS

ON (CB.BILL_ID = CBS.BILL_ID)

WHERE CB.BILL_ID = 000004144285 --Enter your bill ID here.

Relation between CI_BILL and CI_FT is one to many and it's logically related with CI_BILL.BILL_ID and CI_FT.PARENT_ID. Kindly see the query to understand it more.

SELECT *

FROM CI_BILL CB

INNER JOIN CI_FT FT

ON (FT.PARENT_ID = CB.BILL_ID)

WHERE CB.BILL_ID = 000004144285; --Enter your bill ID here;

Relation between CI_BSEG and CI_FT is one to one and it's logically related with CI_BSEG.BSEG_ID and CI_FT.SIBLING_ID. Kindly see the query to understand it more.

SELECT *

FROM CI_BSEG CBS

INNER JOIN CI_FT FT

ON (FT.SIBLING_ID = CBS.BSEG_ID)

WHERE CBS.BILL_ID = 000004144285; --Enter your bill ID here.;

We have one more level of Billing in which we can find the distribution of Bill Segment. Distribution means how Bill is distributed to different type of charges e.g. Consumer Energy Cost, Sales Tax etc.

CI_BSEG_CALC_LN table have those distributions. The relation with CI_BSEG_CALC_LN and CI_BSEG is one to many. Kindly see the query to understand it more.

SELECT *

FROM CI_BSEG CBS

INNER JOIN CI_BSEG_CALC_LN CBCL

ON (CBCL.BSEG_ID = CBS.BSEG_ID)

WHERE CBS.BILL_ID = 000004144285; --Enter your bill ID here;

Adjustments

To retrieve Adjustment information from Financial Transaction table is pretty easy. Only have to match FT_TYPE_FLG (AD, AX) which is already defined. Following query can be used.

SELECT *

FROM CI_FT

WHERE FT_TYPE_FLG IN ('AD','AX');

We have an adjustment table which contains all the information related to adjustments. All adjustments are segregated by adjustment type. Following is the query to understand logical relationship between FT and adjustment tables.

SELECT *

FROM CI_ADJ CJ, CI_FT FT

WHERE CJ.SA_ID = FT.SA_ID

AND CJ.ADJ_ID = FT.SIBLING_ID

AND CJ.ADJ_TYPE_CD = FT.PARENT_ID;

If adjustment amount is greater than zero, it means Debit note and vice versa.

Payments

Retrieval of payment related transaction from financial transaction table can be made by FT_TYPE_FLG (PS, PX) which is already defined above. Following query can be used.

SELECT *

FROM CI_FT

WHERE FT_TYPE_FLG IN ('PS','PX');

Payment table contains all payment information. Payment amount cannot be allocated to any further distributions like tax and other components. Following query might be helpful to retrieve payments.

SELECT *

FROM CI_FT FT, CI_PAY CP

WHERE FT.FT_TYPE_FLG IN ('PS','PX')

AND CP.PAY_ID = FT.PARENT_ID;

Outstanding Amount

SELECT SUM(CUR_AMT) , SA_ID

FROM CI_FT 

GROUP BY SA_ID;

Easiest way to create relation between To-Do and Account, Bill, Adjustment, FA, Case and etc. in CC&B

The relation between To-Do and other entities are usually created in CC&B with Drill Keys now the question arises what are the drill keys. Drill keys are used to navigate from To-Do to source entity.  Following is the query to retrive source information from To-Do's with using drill keys .
For Bill
SELECT *
FROM CI_TD_ENTRY TE
INNER JOIN CI_TD_DRLKEY CTD
ON(TE.TD_ENTRY_ID = CTD.TD_ENTRY_ID)
INNER JOIN  CI_BILL CB
ON (CTD.KEY_VALUE = CB.BILL_ID);

For Account

SELECT *
FROM CI_TD_ENTRY TE
INNER JOIN CI_TD_DRLKEY CTD
ON(TE.TD_ENTRY_ID = CTD.TD_ENTRY_ID)
INNER JOIN  CI_ACCT CA
ON (CTD.KEY_VALUE = CA.ACCT_ID);

For Case

SELECT *
FROM CI_TD_ENTRY TE
INNER JOIN CI_TD_DRLKEY CTD
ON(TE.TD_ENTRY_ID = CTD.TD_ENTRY_ID)
INNER JOIN  CI_CASE CC
ON (CTD.KEY_VALUE = CC.CASE_ID);

To find the drill key information go through the CI_TD_DRLKEY_TY table.

Query to find customer name from account ID in CCB

This query help you to retrieve customer name from his account id

SELECT CPN.ENTITY_NAME
FROM CI_ACCT CA
INNER JOIN CI_ACCT_PER CAP
ON (CAP.ACCT_ID = CA.ACCT_ID
AND CA.ACCT_ID = '0002000000' -- Put your account id here
AND TRIM(CAP.ACCT_REL_TYPE_CD) = 'MAIN')-- only to retrieve main customers there may be one or many child
INNER JOIN CI_PER CP
ON (CP.PER_ID = CAP.PER_ID)
INNER JOIN CI_PER_NAME CPN
ON (CPN.PER_ID = CP.PER_ID
AND TRIM(CPN.NAME_TYPE_FLG) = 'PRIM');--only to retrieve primary name may or may not there are many

Query to retrieve account from meter id in CCB

SELECT AC.ACCT_ID
FROM CI_MTR MTR
INNER JOIN CI_MTR_CONFIG CNF
ON (MTR.MTR_ID = CNF.MTR_ID
AND MTR.MTR_ID = 0000084706)-- put your mtr id here
INNER JOIN CI_SP_MTR_HIST HIST
ON (CNF.MTR_CONFIG_ID = HIST.MTR_CONFIG_ID
AND TRIM(HIST.REMOVAL_MR_ID) IS NULL) --When you have to retrieve the meter which is installed on sp this field will be null
INNER JOIN CI_SP SP
ON(HIST.SP_ID = SP.SP_ID)
INNER JOIN CI_SA_SP SASP
ON (SP.SP_ID = SASP.SP_ID)
INNER JOIN CI_SA SA
ON(SASP.SA_ID = SA.SA_ID
AND SA.SA_STATUS_FLG = '20')--for retrieval of active SA's
INNER JOIN CI_ACCT AC
ON(SA.ACCT_ID = AC.ACCT_ID);