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;
