Banking Starter Pack

Creates a user with the following details using API:

• Customer ID number — ID number registered in the banking database.
• Address — Home or work address of the user registered in the banking database.
• Division — Specifies the division where the user is registered.

//Input - Customer Identifier
SELECT a.PER_ID_NBR CUST_IDENTIFIER
,b.ADDRESS1 ADDR_LINE1
,b.ADDRESS2 ADDR_LINE2
,b.ADDRESS3 ADDR_LINE3
,b.ADDRESS4 ADDR_LINE4
,b.CITY CITY
,b.POSTAL POSTAL_ID
,b.cis_division DIVISION
FROM CI_PER_ID a
,CI_PER b
,c1_address_entity c
,CI_CIS_DIVISION d
WHERE a.PER_ID_NBR = :F1 –Customer Identifier
AND a.PER_ID = b.per_id
AND c.ENTITY_ID = b.per_id
AND b.cis_division = d.CIS_DIVISION

Creates a bank account with the following details using API:

• Account number — Account number of the user as stored in the banking database
• Account type — Specifies the account type of the account number.
• Account start date — Specifies when the account became active.
• Contract type — Type of the contract associated with the account number.
• Contract status — Describes the current status of the contract.
• Contract start date — Specifies when the contract became active.

//Input - Account Identifier

SELECT a.ACCT_NBR ACCT_IDENTIFIER
,a.ACCT_NBR_TYPE_CD ACCT_TYPE
,b.SETUP_DT ACCT_STRT_DT
,c.SA_TYPE_CD CONTRACT_TYPE
FROM CI_ACCT_NBR a
,CI_ACCT b
,CI_SA c
WHERE a.acct_id = b.acct_id
AND a.ACCT_NBR = :F1 – Account Identifier
AND b.acct_id = c.acct_id

Links a child’s account to their parent’s account.

//Input - Parent Customer Identifier

SELECT PARENT_PER_NBR
,CHILD_PER_NBR
,A.PER_REL_TYPE_CD
,TO_CHAR(A.START_DT, ‘DD-MON-YYYY’) START_DATE
FROM CI_PER_PER A
,(
SELECT PER_ID PARENT_PER_ID
,PER_ID_NBR PARENT_PER_NBR
FROM CI_PER_ID
)
,(
SELECT PER_ID CHILD_PER_ID
,PER_ID_NBR CHILD_PER_NBR
FROM CI_PER_ID
)
WHERE A.PER_ID1 = PARENT_PER_ID
AND PER_ID2 = CHILD_PER_ID
AND CHILD_PER_NBR = :F1 – Child Customer

Updates the details of a customer. The Person ID number value cannot be updated, but the following can be:

• Customer identifier
• Email ID
• Phone number
• Address line 1
• Address lne 2
• Address line 3
• Address line 4x
• City
• Postal ID
• Division

//Input - Person Identifier

SELECT a.PER_ID_NBR CUST_IDENTIFIER
,b.per_id CUST_ID
,b.ADDRESS1 ADDR_LINE1
,b.ADDRESS2 ADDR_LINE2
,b.ADDRESS3 ADDR_LINE3
,b.ADDRESS4 ADDR_LINE4
,b.CITY CITY
,b.POSTAL POSTAL_ID
,b.cis_division DIVISION
FROM CI_PER_ID a
,CI_PER b
,c1_address_entity c
,CI_CIS_DIVISION d
WHERE a.PER_ID_NBR = :F1 – Person Identifier
AND a.PER_ID = b.per_id
AND c.ENTITY_ID = b.per_id
AND b.cis_division = d.CIS_DIVISION
//Input - Parent Customer Identifier
SELECT a.PER_ID1 PARENT_ID
,a.PER_ID2 CHILD_ID
,(
SELECT per_id_nbr
FROM ci_per_id
WHERE a.per_id2 = per_id
) Child_Identifier
FROM ci_per_per a
,ci_per_id b
WHERE b.PER_ID_NBR = ‘ABCD01’ – Parent Customer Identifier
AND a.per_id1 = b.per_id;
//Input - Person Identifier
select B.PER_ID_NBR, c.DESCR Char_Type , PER_ID_NBR Char_Value
from CI_PER_ID b , CI_ID_TYPE_l c
where B.PER_ID IN( select Per_id from CI_PER_ID where PER_ID_NBR = ‘87009953’) – Person Identifier
and b.ID_TYPE_CD = c.ID_TYPE_CD;

Updates the details of an account. The Account number and Zone values cannot be updated, but the following can be:

• Effective date
• Char type
• Char value
• Bill cycle
• Currency

//Input - Account Identifier

SELECT a.ACCT_ID
,a.EFFDT
,b.DESCR CHAR_TYPE
,decode(trim(a.CHAR_VAL), NULL, a.ADHOC_CHAR_VAL, a.CHAR_VAL) Char_Value
,d.BILL_CYC_CD
,d.CURRENCY_CD
FROM ci_acct_char a
,ci_char_type_l b
,CI_ACCT_NBR c
,CI_ACCT d
WHERE a.acct_id = c.ACCT_ID
AND c.acct_nbr = :F1 – Account Identifier
AND a.CHAR_TYPE_CD = b.CHAR_TYPE_CD
AND c.ACCT_ID = d.ACCT_ID

Assigns a list of standard prices for items to a customer, including the following details:

• Price List ID — Identification number of the price list where prices of items are defined.
• Start date — Effective date for the price list for the customer.
• End date — Specifies when the price list is no longer for the customer.

//Input - Person Identifier
SELECT e.PER_ID
,a.PRICELIST_ID
,TO_CHAR(c.START_DT, ‘DD-MON-YYYY’) START_DT
,c.PRICE_STATUS_FLAG
,c.PRICEITEM_CD
,f.Value_amt
,l.descr
,g.TIERED_FLAG
FROM ci_pricelist a
,ci_priceLIST_asgn b
,ci_priceasgn C
,ci_party d
,CI_PER_ID e
,CI_PRICECOMP f
,CI_RC_MAP g
,CI_RC_MAP_L l
WHERE a.PRICELIST_ID = b.PRICELIST_ID
AND b.PRICELIST_ID = C.OWNER_ID
AND d.PARTY_UID = B.PARTY_UID
AND d.PARTY_TYPE_FLG = ‘PERS’
AND d.PARTY_ID = e.PER_ID
AND e.PER_ID_NBR = :F1 – Person Identifier
AND f.price_asgn_id = c.price_asgn_id
AND f.RC_MAP_ID = g.RC_MAP_ID
AND f.pricecomp_seqno = g.RC_SEQ
AND l.RC_MAP_ID = G.RC_MAP_ID
AND l.language_cd = ‘ENG’
ORDER BY f.PRICE_ASGN_ID
,f.PRICECOMP_SEQNO

Assigns a list of standard prices for items to an account, including the following details:

• Price List ID — Identification number of the price list where prices of items are defined.
• Start date — Effective date for the price list for the customer.
• End date — Specifies when the price list is no longer for the customer.

//Input - Account Identifier

SELECT e.ACCT_ID
,a.PRICELIST_ID
,TO_CHAR(c.START_DT, ‘DD-MON-YYYY’) START_DT
,c.PRICE_STATUS_FLAG
,c.PRICEITEM_CD
,f.Value_amt
,l.descr
,g.TIERED_FLAG
FROM ci_pricelist a
,ci_priceLIST_asgn b
,ci_priceasgn C
,ci_party d
,CI_ACCT_NBR e
,CI_PRICECOMP f
,CI_RC_MAP g
,CI_RC_MAP_L l
WHERE a.PRICELIST_ID = b.PRICELIST_ID
AND b.PRICELIST_ID = C.OWNER_ID
AND d.PARTY_UID = B.PARTY_UID
AND d.PARTY_TYPE_FLG = ‘ACCT’
AND d.PARTY_ID = e.ACCT_ID
AND e.ACCT_NBR = :F1 – Account Identifier
AND f.price_asgn_id = c.price_asgn_id
AND f.RC_MAP_ID = g.RC_MAP_ID
AND f.pricecomp_seqno = g.RC_SEQ
AND l.RC_MAP_ID = G.RC_MAP_ID
AND l.language_cd = ‘ENG’
ORDER BY f.PRICE_ASGN_ID
,f.PRICECOMP_SEQNO

Assigns a price list that overrides any existing price list assigned to the customer.

//Input - Customer Identifier

SELECT d.PER_ID_NBR
,b.START_DT
,b.END_DT
,b.PRICE_STATUS_FLAG
,c.DESCR
FROM ci_party a
,ci_priceasgn b
,ci_priceitem_l c
,ci_per_id d
WHERE a.party_uid = b.OWNER_ID
AND a.PARTY_TYPE_FLG = ‘PERS’
AND D.PER_ID_NBR = ‘55445566’ – Customer Identifier
AND D.PER_ID = a.PARTY_ID
AND b.PRICEITEM_CD = c.PRICEITEM_CD;

Assigns a price list that overrides any existing price list assigned to an account.

//Input - Account Identifier

SELECT d.ACCT_NBR
,b.START_DT
,b.END_DT
,b.PRICE_STATUS_FLAG
,c.DESCR
FROM ci_party a
,ci_priceasgn b
,ci_priceitem_l c
,CI_ACCT_NBR d
WHERE a.party_uid = b.OWNER_ID
AND a.PARTY_TYPE_FLG = ‘ACCT’
AND D.ACCT_NBR = ‘RIAINDIA_MS01’ – Customer Identifier
AND D.ACCT_ID = a.PARTY_ID
AND b.PRICEITEM_CD = c.PRICEITEM_CD;

Generates a bill using the account name. For batch testing, it runs the following batches in order:

• BILLOPEN (Pending Bill Generation) — Generates blank bill in pending status for accounts in a given division and bill cycle for a given cut-off date.
• BSGENREG (Bill Segment Generation) — Generates bill segments, including all billable charges, of accounts that have blank bills in an open bill cycle, input bill cycle, or division for a given cutoff date.
• POSTPROC (Bill Completion) — Creates post processing bill segment that has been generated by the previous batches.

//Input - Account Identifier ,Account Num Type
SELECT c.acct_nbr ACCT_IDENTIFIER
,c.acct_nbr_type_cd
,b.BILLABLE_CHG_ID
,TRIM(b.PRICEITEM_CD) PRICE_ITEM_CD
,CALC_AMT BSEG_AMT
,a.SVC_QTY TXN_AMT
,DECODE((
SELECT value_amt
FROM ci_pricecomp e
WHERE e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 10
), NULL, ‘NA’, (
SELECT ‘0’ || value_amt
FROM ci_pricecomp e
WHERE e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 10
)) Rate1
,DECODE((
SELECT LOWER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 10
), NULL, ‘NA’, (
SELECT LOWER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 10
)) TIER1_LOWER_LIMIT
,DECODE((
SELECT UPPER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 10
), NULL, ‘NA’, (
SELECT UPPER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 10
)) TIER1_UPPER_LIMIT
,DECODE((
SELECT value_amt
FROM ci_pricecomp e
WHERE e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 20
), NULL, ‘NA’, (
SELECT ‘0’ || value_amt
FROM ci_pricecomp e
WHERE e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 20
)) Rate2
,DECODE((
SELECT LOWER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 20
), NULL, ‘NA’, (
SELECT LOWER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 20
)) TIER2_LOWER_LIMIT
,DECODE((
SELECT UPPER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 20
), NULL, ‘NA’, (
SELECT UPPER_LIMIT
FROM ci_pricecomp_tier pt
,ci_pricecomp e
WHERE pt.PRICECOMP_ID = e.PRICECOMP_ID
AND e.PRICE_ASGN_ID = b.PRICE_ASGN_ID
AND PRICECOMP_SEQNO = 20
)) TIER2_UPPER_LIMIT
FROM ci_bchg_sq a
,ci_bill_chg b
,ci_acct_nbr c
,ci_sa d
,ci_bseg_calc f
WHERE a.BILLABLE_CHG_ID = b.BILLABLE_CHG_ID
AND c.acct_nbr = :F1 – Account Identifier
AND c.acct_nbr_type_cd = :F2 – Account Num Type
AND c.acct_id = d.acct_id
AND d.sa_id = b.sa_id
AND f.BILLABLE_CHG_ID = b.BILLABLE_CHG_ID;

Runs the following batches in order:

• C1-GLASGN — Assigns the GL account to a financial transaction
• GLS — Stages the GL download
• GLDL — Extracts the GL download

SELECT BSEG_ID
,EXP_MSG
,MESSAGE_PARM1
,MESSAGE_PARM2
,MESSAGE_PARM3
,MESSAGE_PARM4
,MESSAGE_PARM5
,CRE_DTTM
FROM CI_BSEG_EXCP;

Creates a payment for the already generated bill.

For UI:

SELECT ACCT_ID
	,DECODE(PAY_STATUS_FLG, '50', 'COMPLETED', 'NOT COMPLETED')
	,PAY_AMT
	,MATCH_VAL BILL_iD
	,ILM_DT
FROM ci_pay;
SELECT PAY_ID
,EXP_MSG
,MESSAGE_PARM1
,MESSAGE_PARM2
,MESSAGE_PARM3
,MESSAGE_PARM4
,MESSAGE_PARM5
,CRE_DTTM
FROM CI_PAY_EXCP;

For API:

//Input - Account Identifier, Bill ID
SELECT D.ACCT_NBR ACCT_IDENTIFIER
,A.ORIG_BILL_AMT BILL_AMOUNT
,SUM(C.PAY_AMT) TOTAL_PAID_AMOUNT
,B.DESCR PAYMENT_STATUS
FROM C1_BILL_DTLS_VW A
,CI_LOOKUP_VAL_L B
,CI_PAY C
,CI_ACCT_NBR D
WHERE A.ACCT_ID = C.ACCT_ID
AND C.ACCT_ID = D.ACCT_ID
AND D.ACCT_NBR = :F1 – Account Identifier
AND A.BILL_ID = :F2 – Bill ID
AND B.FIELD_NAME = ‘PAY_STATUS_FLG’
AND B.FIELD_VALUE = C.PAY_STATUS_FLG
GROUP BY A.ORIG_BILL_AMT
,D.ACCT_NBR
,B.DESCR

//Input - Account Identifier, Payment Event ID
SELECT C.PAY_AMT PAYMENT_AMT
,B.DESCR PAYMENT_STATUS
FROM CI_ACCT_NBR A
,CI_LOOKUP_VAL_L B
,CI_PAY C
WHERE A.ACCT_ID = C.ACCT_ID
AND B.FIELD_NAME = ‘PAY_STATUS_FLG’
AND B.FIELD_VALUE = C.PAY_STATUS_FLG
AND A.ACCT_NBR = :F1 – Account Identifier
AND C.PAY_EVENT_ID = :F2 – Payment Event ID

Runs the following batches in order:

• C1-GLASGN — Assigns the GL account to a financial transaction
• GLS — Stages the GL download
• GLDL — Extracts the GL download

Input - Account Identifier
SELECT COUNT(1) RECORDSCOUNT
FROM CI_FT A
,CI_BILL B
,CI_FT_GL C
,CI_ACCT_NBR D
WHERE A.FT_TYPE_FLG = ‘BS’
AND A.PARENT_ID = B.BILL_ID
AND C.FT_ID = A.FT_ID
AND C.GL_ACCT = ’ '
AND B.ACCT_ID = D.ACCT_ID
AND D.ACCT_NBR = :F1 – Account Identifier