17 queries found for "Financial Transaction"

Financial Transactions on Bill created by Offcyle Generator

SELECT FT.FT_ID,
       FT.SA_ID,
       FT.ACCOUNTING_DT,
       FT.CUR_AMT,
       FT.TOT_AMT,
       FT.FT_TYPE_FLG,
       LK2.DESCR SHOW_ON_BILL_DESCR,
       FT.CURRENCY_CD,
       LK1.DESCR FT_TYPE_DESCR
FROM CI_FT FT,
     CI_LOOKUP LK1,
     CI_LOOKUP LK2,
     CI_BILL BI
WHERE BI.OFFCYC_BGEN_ID = 'INSERT_OFFCYC_BGEN_ID'
AND FT.BILL_ID   = BI.BILL_ID
AND LK1.FIELD_NAME = 'FT_TYPE_FLG'
AND LK1.FIELD_VALUE = FT.FT_TYPE_FLG
AND LK1.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
AND LK2.FIELD_NAME = 'F1_YESNO_FLG'
AND LK2.FIELD_VALUE = FT.SHOW_ON_BILL_SW
AND LK2.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
ORDER BY FT.ACCOUNTING_DT, FT.SA_ID, FT.FT_ID

uploaded by Nuno Rodrigues

Financial Transactions on Bill

SELECT FT.FT_ID,
       FT.SA_ID,
       FT.ACCOUNTING_DT,
       FT.CUR_AMT,
       FT.TOT_AMT,
       FT.FT_TYPE_FLG,
       LK2.DESCR SHOW_ON_BILL_DESCR,
       FT.CURRENCY_CD,
       LK1.DESCR FT_TYPE_DESCR
FROM CI_FT FT,
     CI_LOOKUP LK1,
     CI_LOOKUP LK2,
     CI_BILL BI
WHERE BI.BILL_ID = 'INSERT_BILL_ID'
AND FT.BILL_ID   = BI.BILL_ID
AND LK1.FIELD_NAME = 'FT_TYPE_FLG'
AND LK1.FIELD_VALUE = FT.FT_TYPE_FLG
AND LK1.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
AND LK2.FIELD_NAME = 'F1_YESNO_FLG'
AND LK2.FIELD_VALUE = FT.SHOW_ON_BILL_SW
AND LK2.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
ORDER BY FT.ACCOUNTING_DT, FT.SA_ID, FT.FT_ID

uploaded by Nuno Rodrigues

Service Agreement Annual Bill Amount

SELECT SUM(A.CUR_AMT) AS CUR_AMT
          , SUM(A.TOT_AMT) AS TOT_AMT           
          ,to_char(B.START_DT, 'YYYY') as Year
          , COUNT(A.FT_ID) AS NUM_FINANCIAL_TRANSACTIONS  
   FROM CI_FT A          
          , CI_BSEG B 
 WHERE A.SA_ID = 'INSERT_SA_ID'
     AND A.FT_TYPE_FLG = 'BS'   
     AND A.SIBLING_ID = B.BSEG_ID   
     AND NOT EXISTS (SELECT 'X' FROM CI_FT C                    
                                WHERE C.SIBLING_ID = A.SIBLING_ID                     
                                    AND C.FT_TYPE_FLG = 'BX')
GROUP BY to_char(B.START_DT, 'YYYY')

uploaded by Nuno Rodrigues

Get Bill's Bill Segment Amount

SELECT
  SVCTYPEL.SVC_TYPE_CD, 
  SVCTYPEL.DESCR, 
  SUM(FT.CUR_AMT) AS CUR_AMT_SUM
FROM
  CI_SVC_TYPE SVCTYPE, 
  CI_SVC_TYPE_L SVCTYPEL, 
  CI_SA_TYPE SATY, 
  CI_SA SA, 
  CI_FT FT
WHERE 
  FT.BILL_ID = 'INSERT_BILL_ID'
  AND FT.PARENT_ID = FT.BILL_ID
  AND FT.FT_TYPE_FLG IN ('BS','BX')
  AND FT.FREEZE_SW = 'Y'
  AND FT.SHOW_ON_BILL_SW = 'Y'
  AND FT.SA_ID = SA.SA_ID
  AND SA.CIS_DIVISION = SATY.CIS_DIVISION
  AND SA.SA_TYPE_CD = SATY.SA_TYPE_CD
  AND SATY.SVC_TYPE_CD = SVCTYPE.SVC_TYPE_CD
  AND SVCTYPE.SVC_TYPE_CD = SVCTYPEL.SVC_TYPE_CD
  AND SVCTYPEL.LANGUAGE_CD = 'INSERT_LANGUAGE_CD_MOSTLY_ENG'
GROUP BY
  SVCTYPEL.SVC_TYPE_CD, 
  SVCTYPEL.DESCR
  

uploaded by Nuno Rodrigues

Retrieve Match Events for Adjustment

SELECT DISTINCT ME.MATCH_EVT_ID,
                ME.MEVT_STATUS_FLG,
                ME.ARCHIVE_SW
FROM   CI_FT FT,
       CI_MATCH_EVT ME
WHERE  FT.SIBLING_ID = 'INSERT_ADJUSTMENT_ID'
       AND ( FT.FT_TYPE_FLG = 'AD'
              OR FT.FT_TYPE_FLG = 'AX' )
       AND ME.MATCH_EVT_ID = FT.MATCH_EVT_ID

uploaded by Nuno Rodrigues

Retrieve Match Events for Bill

SELECT DISTINCT ME.MATCH_EVT_ID,
                ME.MEVT_STATUS_FLG,
                ME.ARCHIVE_SW
FROM   CI_BSEG BS,
       CI_FT FT,
       CI_MATCH_EVT ME
WHERE  FT.SIBLING_ID = BS.BSEG_ID
       AND BS.BILL_ID = 'INSERT_BILL_ID'
       AND ( FT.FT_TYPE_FLG = 'BS'
              OR FT.FT_TYPE_FLG = 'BX' )
       AND ME.MATCH_EVT_ID = FT.MATCH_EVT_ID

uploaded by Nuno Rodrigues

Retrieve Match Events for Bill Segment

SELECT DISTINCT ME.MATCH_EVT_ID,
                ME.MEVT_STATUS_FLG,
                ME.ARCHIVE_SW
FROM   CI_FT FT,
       CI_MATCH_EVT ME
WHERE  FT.SIBLING_ID = 'INSERT_BILL_SEGMENT_ID'
       AND ( FT.FT_TYPE_FLG = 'BS'
              OR FT.FT_TYPE_FLG = 'BX' )
       AND ME.MATCH_EVT_ID = FT.MATCH_EVT_ID

uploaded by Nuno Rodrigues

Retrieve FTs for Match Event

SELECT FT.FT_ID
FROM   CI_FT FT
WHERE  FT.MATCH_EVT_ID = 'INSERT_MATCH_EVENT_ID'

uploaded by Nuno Rodrigues

Retrieve Open Item Payment FT without Match Event

SELECT FT.FT_ID
FROM   CI_PAY P,
       CI_FT FT
WHERE  FT.PARENT_ID = P.PAY_ID
       AND P.PAY_EVENT_ID = 'INSERT_PAYMENT_EVENT_ID'
       AND MATCH_EVT_ID = ' '

uploaded by Nuno Rodrigues

Retrieve Open Item FT without Match Event

SELECT FT_ID
FROM CI_FT
WHERE 
SIBLING_ID = 'INSERT_BSEG_PAY_OR_ADJ_ID'
AND MATCH_EVT_ID = ' '

uploaded by Nuno Rodrigues

Retrieve Match Events for Payment Event

SELECT DISTINCT ME.MATCH_EVT_ID,
                ME.MEVT_STATUS_FLG,
                ME.ARCHIVE_SW
FROM   CI_PAY P,
       CI_FT FT,
       CI_MATCH_EVT ME
WHERE  FT.PARENT_ID = P.PAY_ID
       AND P.PAY_EVENT_ID = 'INSERT_PAY_EVENT_ID'
       AND ( FT.FT_TYPE_FLG = 'PS'
              OR FT.FT_TYPE_FLG = 'PX' )
       AND FT.MATCH_EVT_ID = ME.MATCH_EVT_ID

uploaded by Nuno Rodrigues

Retrieve Active 'Service Agreements' without Payments

select  
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'UNIT' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1)  "UNIT",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'REGION' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1) "REGION",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'AREA' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1) "AREA",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'ZONE' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM = 1) "ZONE",
(SELECT DESCR FROM CI_CHAR_VAL_L CHARL,CI_PREM_CHAR PREMCHARA WHERE PREMCHARA.CHAR_TYPE_CD = CHARL.CHAR_TYPE_CD 
AND PREMCHARA.CHAR_VAL = CHARL.CHAR_VAL AND CHARL.CHAR_TYPE_CD = 'SUB-ZONE' AND PREMCHARA.PREM_ID  = a.char_PREM_ID AND ROWNUM =1) "SUB-ZONE",
        a.acct_id,
        (select upper(pn.entity_name) from ci_acct_per ap,ci_per_name pn where ap.per_id = pn.per_id and ap.acct_id = a.acct_id and pn.PRIM_NAME_SW = 'Y' and rownum =1) Entity_name,
        (select (TRIM (PM.ADDRESS1)||' '||TRIM (PM.ADDRESS2)||' '||TRIM (PM.ADDRESS3)||' '||TRIM (PM.ADDRESS4)) from ci_prem pm where
        pm.prem_id = a.char_prem_id and rownum =1) address,
        a.SA_ID,
        a.SA_TYPE_CD,
        a.START_DT,
        a.SA_STATUS_FLG,
        a.CHAR_PREM_ID,
        a.TOT_TO_BILL_AMT,
        au.USER_ID SA_CREATED_BY
from    ci_sa a ,CM_AUDIT_SA au
WHERE A.sa_type_cd IN ( Insert SA Type Codes) 
and     sa_status_flg in ('insert SA status flag') 
and     not exists (select 'x' from ci_ft where sa_id=a.sa_id and ft_type_flg in ('PS','PX'))
and
        (select to_char(c.START_DTTM,'dd/mm/yyyy')
from    ci_sa b,ci_sa_sp c
where   b.sa_id = c.sa_id and B.ACCT_ID = A.ACCT_ID
and     STOP_MR_ID = ' ' AND STOP_DTTM IS NULL
and     sa_type_cd like 'U-%' and sa_status_flg in ('20') and rownum = 1
) IS NOT NULL
and au.PK_VALUE1 = a.sa_id
and au.AUDIT_ACTION_FLG = 'A'
and au.audit_fld_name = 'START_DT'

uploaded by Muhammad Junaid

Dollars billed for particular bill cycle and business date.

select sum(cur_amt) from cisadm.ci_ft a where a.ft_type_flg = 'BS'
and parent_id in (select bill_id from cisadm.ci_bill where bill_cyc_cd = 'MDXX'   and bill_dt = 'DD-MMM-YYYY');	

uploaded by Vinayak Gadgil

Retrieve Current balance of the account

select sum(f.CUR_AMT)
from cisadm.CI_ACCT a, cisadm.ci_sa s, cisadm.CI_FT f
where a.ACCT_ID=s.ACCT_ID
and f.SA_ID=s.SA_ID
and a.ACCT_ID = 'INSERT ACCT ID'
group by s.ACCT_ID;

uploaded by Tushar Mali

DELETE PENDING BILLS

prompt "Start of delete bills treatment"
SET SERVEROUTPUT ON;


DECLARE
VBILL_ID            VARCHAR2(12);
BILL_COUNT          NUMBER:=0;

CURSOR C1 is select distinct bs.bill_id from  ci_bseg bs, ci_sa sa, ci_bill bill
where bs.sa_id=sa.sa_id
 and sa.sa_type_cd in ('xxxx','yyyyy','zzzzzz')
and bill.bill_id=bs.bill_id and bill.bill_stat_flg='P'
 and bs.bill_cyc_cd = '   ' and bs.bseg_stat_flg in ('10','20','30','40');



BEGIN

OPEN C1;
  LOOP
  FETCH c1 INTO VBILL_ID;
  EXIT WHEN c1%NOTFOUND;
  
BEGIN 

BILL_COUNT := BILL_COUNT+1;
delete from  ci_bill_sa where bill_id in (select bill_id from ci_bill where bill_id = VBILL_ID);
delete from  ci_bill_excp where bill_id in (select bill_id from ci_bill where bill_id = VBILL_ID);
delete from  ci_bill_routing where bill_id in (select bill_id from ci_bill where bill_id = VBILL_ID);

delete from  ci_bseg_sq where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_read where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_msg where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);

delete from  ci_bseg_k where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_item where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_excp where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_cl_char where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_calc_ln where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg_calc where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);
delete from  ci_bseg where bseg_id in (select bseg_id from ci_bseg where bill_id = VBILL_ID);


delete from  ci_ft_gl  where ft_id in ( select ft_id from ci_ft where parent_id = VBILL_ID);
delete from  ci_ft_k  where ft_id in ( select ft_id from ci_ft where parent_id = VBILL_ID);
delete from  ci_ft  where ft_id in ( select ft_id from ci_ft where parent_id = VBILL_ID);

delete from  ci_bill where bill_id = VBILL_ID;
delete from  ci_bill_char where bill_id = VBILL_ID;
delete from  ci_bill_k where bill_id = VBILL_ID;

Exception 
When others 
then dbms_output.put_line('Error, Bill Id: '||VBILL_ID||', '||sqlerrm);
    
END;

END LOOP;  

close c1;
DBMS_OUTPUT.PUT_LINE('Number of Bills Processed ' || BILL_COUNT); 
END;
/



--commit;

prompt "End of Treatment"

exit;

uploaded by Alamuri Prasad Rao

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;

uploaded by Ninad Deshpande

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'

uploaded by Kemal Onyurt