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;