CREATE OR REPLACE PROCEDURE etldbo.sp_cschedule
  (
    in_Workflow_RunID  IN NUMBER,
    in_IL_End_Date     IN VARCHAR2,
    in_User            IN VARCHAR2,
    p_sqlcode          OUT NUMBER,
    p_sqlerrm          OUT VARCHAR2,
    p_error            OUT varchar2
  )
IS
--Variable declaration
    o_SecurityType   NUMBER;
    o_SecurityID     NUMBER;
    o_CouponPeriod   NUMBER;
    o_CouponDate     DATE;
    o_PriorDate      DATE;
    o_ExdivDate      DATE;
    o_CouponAmount   NUMBER(28,12);
    o_Coupon         NUMBER(28,12);
    o_VCoupon        NUMBER := 0;
    o_PIKBond        NUMBER;
    o_ABond          NUMBER;
    o_VAmortization  NUMBER := 0;
    o_Workflow_RunID NUMBER;
    o_User           VARCHAR2(30 BYTE);
    v_SecurityID     NUMBER;
    v_CouponDate     DATE;
    v_PriorDate      DATE;
    v_CouponPeriod   NUMBER;
    v_CouponAmount   NUMBER(28,12);
    v_IL_End_Date    VARCHAR2(40) := in_IL_End_Date;
  in_SecurityType  NUMBER;
    in_SecurityID    NUMBER;
    in_CouponPeriod  NUMBER;
    in_CouponDate    DATE;
    in_PriorDate     DATE;
    in_ExdivDate     DATE;
    in_CouponAmount  securitydbo.security_master.coupon%TYPE;
    in_Coupon        securitydbo.security_master.coupon%TYPE;
    in_VCoupon       NUMBER;
    in_PIKBond       NUMBER;
    in_ABond         NUMBER;
    in_VAmortization NUMBER;

--Cursor
  CURSOR c_src IS
      select           
          SecurityType,
          SecurityID,  
          CouponPeriod,
          CouponDate,
          PriorDate,  
          ExdivDate,  
          DateCheck,
          CouponAmount,
          Coupon,
          PIKBond,      
          ABond,
          VCoupon,
          VAmortization
    from (      
         select           
          SecurityType,
          SecurityID,  
          CouponPeriod,
          CouponDate,
          PriorDate,  
          ExdivDate,  
          DateCheck,
          CouponAmount,
          Coupon,
          PIKBond,      
          ABond,
          VCoupon,
          VAmortization,
          COUNT(1) OVER(PARTITION BY SecurityID,CouponDate)AS dupcnt 
        from(   
          select  CScheduleSecurityType   AS SecurityType,
              BondDataSecurityID      AS SecurityID,  
              CscheduleCouponPeriod   AS CouponPeriod,
              CScheduleCouponDate     AS CouponDate,
              CSchedulePriorDate      AS PriorDate,  
              CScheduleExDivDate      AS ExdivDate,
              CASE 
                   WHEN ( to_date(to_char(CScheduleCouponDate,'MM/DD/YYYY'),'MM/DD/YYYY')>=to_date(to_char(CScheduleExDivDate,'MM/DD/YYYY'),'MM/DD/YYYY')) THEN 0
                   WHEN ( to_date(to_char(CSchedulePriorDate,'MM/DD/YYYY'),'MM/DD/YYYY')<to_date(to_char(CScheduleExDivDate,'MM/DD/YYYY'),'MM/DD/YYYY'))THEN 0
                   WHEN ( to_date(to_char(CScheduleExDivDate,'MM/DD/YYYY'),'MM/DD/YYYY')>to_date(to_char(CSchedulePriorDate,'MM/DD/YYYY'),'MM/DD/YYYY')) THEN 0
                   ELSE 1
              end as DateCheck,
              CscheduleCouponAmt      AS CouponAmount,
              CScheduleCoupon         AS Coupon,
              MAX(CSchedulePIKBond)   AS PIKBond,      
              MAX(CscheduleABond)     AS ABond,
              CScheduleVCoupon        AS VCoupon,
              MAX(CscheduleVAmortization)  AS VAmortization
          FROM ( 
            select distinct 
            case when sd.user_group_char10= 'Y' then '3' when trim(upper(sd.alt_security_type))='INFLATION BOND' then '2' else '1' end CScheduleSecurityType, 
            trim(x.xref_security_id) BondDataSecurityID, 
            case 
             when nvl(trim(s.default_flag),'N') = 'Y' then 0
             when s.coupon_freq_code is null then null
             when s.coupon_freq_code not in ('MAT','1_D','7_D','28_D','91_D','182_D','1_M','3_M','6_M','12_M') then null
             when s.coupon_freq_code = '1_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 1 then 0
             when s.coupon_freq_code = '3_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 3 then 0
             when s.coupon_freq_code = '6_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 6 then 0
             when s.coupon_freq_code = '12_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 12 then 0
             when s.coupon_freq_code = '1_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 1 then 0
             when s.coupon_freq_code = '7_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 7 then 0
             when s.coupon_freq_code = '28_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 28 then 0
             when s.coupon_freq_code = '91_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 91 then 0
             when s.coupon_freq_code = '182_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 182 then 0
             when s.coupon_freq_code = 'MAT' then 0 
             when pcd.coupon_begin_date = f.dated_date then 1
             when pcd.coupon_end_date = s.mat_date then 2
             else 3
          end                   CscheduleCouponPeriod,  
            pcd.coupon_end_date                                   CScheduleCouponDate, 
            pcd.coupon_begin_date                                 CSchedulePriorDate, 
            pcd.coupon_end_date - nvl(f.ex_days,0)                CScheduleExDivDate,
          case 
             when nvl(trim(s.default_flag),'N') = 'Y' then 0
             when s.coupon_freq_code is null then null
             when s.coupon_freq_code not in ('MAT','1_D','7_D','28_D','91_D','182_D','1_M','3_M','6_M','12_M') then null
             when s.coupon_freq_code = '1_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 1 then s.coupon / 12
             when s.coupon_freq_code = '3_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 3 then s.coupon / 4
             when s.coupon_freq_code = '6_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 6 then s.coupon / 2
             when s.coupon_freq_code = '12_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 12 then s.coupon
             when s.coupon_freq_code = '1_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 1 then s.coupon / 365
             when s.coupon_freq_code = '7_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 7 then s.coupon / 52
             when s.coupon_freq_code = '28_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 28 then s.coupon / 12
             when s.coupon_freq_code = '91_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 91 then s.coupon / 4
             when s.coupon_freq_code = '182_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 182 then s.coupon / 2
             when s.coupon_freq_code = 'MAT' then 0  ---this may change
             else round((s.coupon/((pcd.coupon_end_date - add_months(pcd.coupon_end_date,-12)))) * ((pcd.coupon_end_date - pcd.coupon_begin_date)+1),8)
          end                   CscheduleCouponAmt,  
            decode(nvl(Trim(s.default_flag),'N'),'Y',0, s.coupon) CScheduleCoupon, 
          case 
             when s.process_sec_type = 'DBIBPK' then 1
             when fd.calc_typ = '1224' then 1
             else 0
          end                       CSchedulePIKBond,
            decode(s2.factor,null,decode(nvl(trim(fd.sinkable),'N'),'Y',1,0),1)   CscheduleABond, 
            decode(trim(S.coupon_type_code),'F',0,decode(s2.factor,null,0,1))     CscheduleVAmortization,
            0 as CScheduleVCoupon
        from rulesdbo.entity e 
          inner join holdingdbo.position p on e.entity_id = p.entity_id
          inner join holdingdbo.position_detail d on p.position_id = d.position_id
          inner join holdingdbo.position_cost_detail pcd on p.position_id = pcd.position_id and d.position_detail_id = pcd.position_detail_id
          inner join pace_masterdbo.interfaces i on p.src_intfc_inst = i.instance
          inner join securitydbo.security_master s on d.security_alias = s.security_alias
          inner join securitydbo.security_master_detail sd on d.security_alias = sd.security_alias
          inner join securitydbo.fixed_income f on d.security_alias = f.security_alias
          inner join securitydbo.fixed_income_detail fd on d.security_alias = fd.security_alias
          left join securitydbo.xreference x on d.security_alias = x.security_alias 
          left join (select bc.business_date, week_end_flag
                 from pace_masterdbo.business_calendar bc
                 inner join pace_masterdbo.interfaces i on bc.src_intfc_inst = i.instance and i.short_desc = 'G_BNYMGLBL') bc on p.effective_date = bc.business_date
          left join (select distinct b.business_date effective_date, s.security_alias, sum((s.schedule_amount)) Factor
                 from pace_masterdbo.business_calendar b
                 inner join pace_masterdbo.interfaces i on b.src_intfc_inst = i.instance and i.short_desc = 'G_BNYMGLBL'
                 inner join securitydbo.schedule s on b.business_date >= s.effective_date
                 where s.schedule_sub_type = 'SINK'
                   and s.src_intfc_inst = 4
                   group by b.business_date, s.security_alias) s2 on d.security_alias = s2.security_alias and p.effective_date = s2.effective_date
        where i.short_desc = 'P2P-CORE'
            and p.effective_date between (to_date(v_IL_End_Date,'YYYYMMDD')-3) and TO_DATE(v_IL_End_Date,'YYYYMMDD')
            and e.user_float5 = 0
            and d.share_par_value <> 0
            and sd.user_group_desc3 = '5'
            and e.user_field3 = 'CASH'
            and (x.xref_type = 'BPAS_SECID' or x.xref_type = 'OLD_BPAS_SECID')    
        GROUP BY  x.xref_security_id, 
              f.ex_days,
              pcd.coupon_begin_date,
              pcd.coupon_end_date, 
              s.default_flag, 
              s.coupon_type_code,
              add_months(pcd.coupon_end_date,-12),
              months_between(pcd.coupon_begin_date,pcd.coupon_end_date),
              decode(s2.factor,null,1,1-s2.factor),
              decode(pcd.tips_index_ratio,0,1,pcd.tips_index_ratio), 
              decode(nvl(trim(s.default_flag),'N'),'Y',0, s.coupon),
              decode(s2.factor,null,decode(nvl(trim(fd.sinkable),'N'),'Y',1,0),1),
              decode(trim(S.coupon_type_code),'F',0,decode(s2.factor,null,0,1)), 
            case 
             when s.process_sec_type = 'DBIBPK' then 1
             when fd.calc_typ = '1224' then 1
             else 0
          end,
              case when sd.user_group_char10 = 'Y' then '3'  when trim(upper(sd.alt_security_type)) = 'INFLATION BOND' then '2' else '1'  end,
          case 
             when nvl(trim(s.default_flag),'N') = 'Y' then 0
             when s.coupon_freq_code is null then null
             when s.coupon_freq_code not in ('MAT','1_D','7_D','28_D','91_D','182_D','1_M','3_M','6_M','12_M') then null
             when s.coupon_freq_code = '1_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 1 then s.coupon / 12
             when s.coupon_freq_code = '3_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 3 then s.coupon / 4
             when s.coupon_freq_code = '6_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 6 then s.coupon / 2
             when s.coupon_freq_code = '12_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 12 then s.coupon
             when s.coupon_freq_code = '1_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 1 then s.coupon / 365
             when s.coupon_freq_code = '7_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 7 then s.coupon / 52
             when s.coupon_freq_code = '28_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 28 then s.coupon / 12
             when s.coupon_freq_code = '91_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 91 then s.coupon / 4
             when s.coupon_freq_code = '182_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 182 then s.coupon / 2
             when s.coupon_freq_code = 'MAT' then 0  ---this may change
             else round((s.coupon/((pcd.coupon_end_date - add_months(pcd.coupon_end_date,-12)))) * ((pcd.coupon_end_date - pcd.coupon_begin_date)+1),8)
          end,
          case 
             when nvl(trim(s.default_flag),'N') = 'Y' then 0
             when s.coupon_freq_code is null then null
             when s.coupon_freq_code not in ('MAT','1_D','7_D','28_D','91_D','182_D','1_M','3_M','6_M','12_M') then null
             when s.coupon_freq_code = '1_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 1 then 0
             when s.coupon_freq_code = '3_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 3 then 0
             when s.coupon_freq_code = '6_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 6 then 0
             when s.coupon_freq_code = '12_M' and months_between(pcd.coupon_end_date,pcd.coupon_begin_date) = 12 then 0
             when s.coupon_freq_code = '1_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 1 then 0
             when s.coupon_freq_code = '7_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 7 then 0
             when s.coupon_freq_code = '28_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 28 then 0
             when s.coupon_freq_code = '91_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 91 then 0
             when s.coupon_freq_code = '182_D' and (pcd.coupon_end_date-pcd.coupon_begin_date) = 182 then 0
             when s.coupon_freq_code = 'MAT' then 0 
             when pcd.coupon_begin_date = f.dated_date then 1
             when pcd.coupon_end_date = s.mat_date then 2
             else 3
           end 
        order by trim(x.xref_security_id)desc
        )group by 
              CScheduleSecurityType ,
              BondDataSecurityID    ,  
              CscheduleCouponPeriod ,
              CScheduleCouponDate   ,
              CSchedulePriorDate    ,  
              CScheduleExDivDate    ,  
              CscheduleCouponAmt    ,
              CScheduleCoupon       ,
              CScheduleVCoupon        
        ) where SecurityID IS NOT NULL AND CouponDate IS NOT NULL AND PriorDate  IS NOT NULL AND ExdivDate IS NOT NULL AND CouponAmount IS NOT NULL AND Coupon IS NOT null
        and DateCheck = 0) where DupCnt = 1;

  CURSOR c_dps (p_SecurityID number , p_CouponDate date)IS
      SELECT  DISTINCT
          TRIM(x.xref_security_id)                                              AS SecurityID,
          pcd.coupon_end_date                                                   AS CouponDate,
          pcd.coupon_begin_date                                                 AS PriorDate,
          f.last_income_date                                                    AS last_income_date,
          f.ex_days                                                             AS ex_days,
          s.mat_date                                                            AS mat_date,
          TRIM(s.coupon_freq_code)                                              AS coupon_freq_code,
          decode(s2.factor,NULL,decode(nvl(trim(fd.sinkable),'N'),'Y',1,0),1)   AS ABond,
          decode(trim(s.coupon_type_code),'F',0,decode(s2.factor,NULL,0,1))     AS VAmortization
         FROM rulesdbo.entity e
          INNER JOIN holdingdbo.position p                  ON e.entity_id = p.entity_id
          INNER JOIN holdingdbo.position_detail d           ON p.position_id = d.position_id
          INNER JOIN holdingdbo.position_cost_detail pcd    ON p.position_id = pcd.position_id AND d.position_detail_id = pcd.position_detail_id
          INNER JOIN pace_masterdbo.interfaces i            ON p.src_intfc_inst = i.instance
          INNER JOIN securitydbo.security_master s          ON d.security_alias = s.security_alias
          INNER JOIN securitydbo.security_master_detail sd  ON d.security_alias = sd.security_alias
          INNER JOIN securitydbo.fixed_income f             ON d.security_alias = f.security_alias
          INNER JOIN securitydbo.fixed_income_detail fd     ON d.security_alias = fd.security_alias
          LEFT JOIN securitydbo.xreference x                ON d.security_alias = x.security_alias
          LEFT JOIN (SELECT DISTINCT b.business_date effective_date, s.security_alias, SUM((s.schedule_amount)) Factor
                  FROM pace_masterdbo.business_calendar b
                    INNER JOIN pace_masterdbo.interfaces i  ON b.src_intfc_inst = i.instance AND i.short_desc = 'G_BNYMGLBL'
                    INNER JOIN securitydbo.schedule s       ON b.business_date >= s.effective_date
                WHERE s.schedule_sub_type = 'SINK'
                  AND s.src_intfc_inst = 4
                GROUP BY b.business_date, s.security_alias) s2 ON d.security_alias = s2.security_alias AND pcd.coupon_end_date = s2.effective_date
        WHERE i.short_desc        = 'P2P-CORE'
        AND  p.effective_date between (to_date(v_IL_End_Date,'YYYYMMDD')-3) and TO_DATE(v_IL_End_Date,'YYYYMMDD')
        AND e.user_float5       = 0
        AND d.share_par_value   <> 0
        AND sd.user_group_desc3 = '5'
        AND e.user_field3       = 'CASH'
        AND (x.xref_type        = 'BPAS_SECID' OR x.xref_type = 'OLD_BPAS_SECID')
        AND x.xref_security_id  = p_SecurityID
        AND pcd.coupon_end_date = p_CouponDate
        AND pcd.coupon_end_date IS NOT NULL;

begin
  FOR r_src IN c_src 
  LOOP
    -- Variable assignment
      p_sqlcode       := 0;
      p_sqlerrm       := NULL;
      o_Workflow_RunID:= in_Workflow_RunID;
      o_User          := in_User;
      v_SecurityID    := r_src.SecurityID;
      v_CouponDate    := r_src.CouponDate;
      v_PriorDate     := r_src.PriorDate;
      v_CouponPeriod  := r_src.CouponPeriod;
      v_CouponAmount  := r_src.CouponAmount;
      in_SecurityType := r_src.SecurityType;
      in_SecurityID   := r_src.SecurityID;
      in_coupon       := r_src.coupon;
      in_PIKBond      := r_src.PIKBond;
      in_VCoupon      := r_src.VCoupon;
--Insert the first incoming records before looping
            INSERT INTO ETLDBO.STG_CSCHEDULE
            VALUES
            (
              r_src.SecurityType,
              r_src.SecurityID,
              r_src.CouponPeriod,
              r_src.CouponDate,
              r_src.PriorDate,
              r_src.ExdivDate,
              r_src.Coupon,
              r_src.CouponAmount,
              r_src.PIKBond,
              r_src.ABond,
              r_src.VCoupon,
              r_src.VAmortization,
              NULL,
              NULL,
              in_Workflow_RunID,
              SYSDATE,
              SYSDATE,
              in_User,
              SYSDATE,
              in_User
            );
--Commit
        COMMIT;
--LOOP for each records
  FOR r_dps IN c_dps(r_src.securityid , r_src.coupondate)
    LOOP
     WHILE (v_CouponDate < r_dps.mat_date AND v_CouponDate < to_date(v_IL_End_Date,'YYYYMMDD') + 11 )
        LOOP
          IF v_CouponDate =  r_dps.last_income_date THEN
        --assign the values
            o_SecurityType  := in_SecurityType;
            o_SecurityID    := in_SecurityID;
            o_CouponDate    := r_dps.mat_date;
            o_PriorDate     := v_CouponDate;
            o_ExdivDate     := o_CouponDate - NVL((r_dps.ex_days),0);
            --CouponPeriod
            CASE
                WHEN r_dps.coupon_freq_code IS NULL THEN o_CouponPeriod:= NULL;
                WHEN r_dps.coupon_freq_code NOT IN ('1_D','7_D','28_D','91_D','182_D','1_M','3_M','6_M','12_M') THEN o_CouponPeriod:= NULL;
                WHEN r_dps.coupon_freq_code = '1_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 1  THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '3_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 3  THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '6_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 6  THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '12_M'  AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 12 THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '1_D'   AND (o_CouponDate-o_PriorDate) = 1   THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '7_D'   AND (o_CouponDate-o_PriorDate) = 7   THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '28_D'  AND (o_CouponDate-o_PriorDate) = 28  THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '91_D'  AND (o_CouponDate-o_PriorDate) = 91  THEN o_CouponPeriod:= 0;
                WHEN r_dps.coupon_freq_code = '182_D' AND (o_CouponDate-o_PriorDate) = 182 THEN o_CouponPeriod:= 0;
                ELSE o_CouponPeriod:= 2;
            END CASE;
            --CouponAmount
           IF (o_CouponPeriod = 0 AND v_CouponPeriod = 0) THEN o_CouponAmount := v_CouponAmount;
               ELSE
            CASE
              WHEN r_dps.coupon_freq_code IS NULL THEN o_CouponAmount := NULL;
              WHEN r_dps.coupon_freq_code NOT IN ('1_D','7_D','28_D','91_D','182_D','1_M','3_M','6_M','12_M') THEN o_CouponAmount:=NULL;
              WHEN r_dps.coupon_freq_code = '1_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 1   THEN o_CouponAmount:= in_coupon/12;
              WHEN r_dps.coupon_freq_code = '3_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 3   THEN o_CouponAmount := in_coupon /4;
              WHEN r_dps.coupon_freq_code = '6_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 6  THEN o_CouponAmount := in_coupon /2;
              WHEN r_dps.coupon_freq_code = '12_M'  AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 12 THEN o_CouponAmount := in_coupon;
              WHEN r_dps.coupon_freq_code = '1_D'   AND (o_CouponDate-o_PriorDate) = 1   THEN o_CouponAmount := in_coupon /365;
              WHEN r_dps.coupon_freq_code = '7_D'   AND (o_CouponDate-o_PriorDate) = 7   THEN o_CouponAmount := in_coupon /52;
              WHEN r_dps.coupon_freq_code = '28_D'  AND (o_CouponDate-o_PriorDate) = 28  THEN o_CouponAmount := in_coupon /12;
              WHEN r_dps.coupon_freq_code = '91_D'  AND (o_CouponDate-o_PriorDate) = 91  THEN o_CouponAmount := in_coupon /4;
              WHEN r_dps.coupon_freq_code = '182_D' AND (o_CouponDate-o_PriorDate) = 182 THEN o_CouponAmount := in_coupon /2;
              ELSE o_CouponAmount := ROUND((in_coupon/((o_CouponDate - ADD_MONTHS(o_CouponDate,-12)))) * ((o_CouponDate - o_PriorDate)+1),8);
            END CASE;
            END IF;
            o_Coupon        := in_coupon;
            o_PIKBond       := in_PIKBond;
            o_ABond         := r_dps.ABond;
            o_VCoupon       := in_Coupon;
            o_VAmortization := r_dps.VAmortization;

            --Insert
            INSERT INTO ETLDBO.STG_CSCHEDULE
            VALUES
            (
              o_SecurityType,
              o_SecurityID,
              o_CouponPeriod,
              o_CouponDate,
              o_PriorDate,
              o_ExdivDate,
              o_Coupon,
              o_CouponAmount,
              o_PIKBond,
              o_ABond,
              o_VCoupon,
              o_VAmortization,
              NULL,
              NULL,
              o_Workflow_RunID,
              SYSDATE,
              SYSDATE,
              o_User,
              SYSDATE,
              o_User
            );
           --Commit
             COMMIT;
           ELSE
--Create next (non-final) coupon
            o_SecurityType  := in_SecurityType;
            o_SecurityID    := in_SecurityID;
            --CouponDate
            CASE
              WHEN r_dps.coupon_freq_code IS NULL    THEN o_CouponDate:= NULL;
              WHEN r_dps.coupon_freq_code  = '1_M'   THEN o_CouponDate:= ADD_MONTHS(v_CouponDate,1);
              WHEN r_dps.coupon_freq_code  = '3_M'   THEN o_CouponDate:= ADD_MONTHS(v_CouponDate,3);
              WHEN r_dps.coupon_freq_code  = '6_M'   THEN o_CouponDate:= ADD_MONTHS(v_CouponDate,6);
              WHEN r_dps.coupon_freq_code  = '12_M'  THEN o_CouponDate:= ADD_MONTHS(v_CouponDate,12);
              WHEN r_dps.coupon_freq_code  = '1_D'   THEN o_CouponDate:= v_CouponDate + 1;
              WHEN r_dps.coupon_freq_code  = '7_D'   THEN o_CouponDate:= v_CouponDate + 7;
              WHEN r_dps.coupon_freq_code  = '28_D'  THEN o_CouponDate:= v_CouponDate + 28;
              WHEN r_dps.coupon_freq_code  = '91_D'  THEN o_CouponDate:= v_CouponDate + 91;
              WHEN r_dps.coupon_freq_code  = '182_D' THEN o_CouponDate:= v_CouponDate + 182;
              ELSE o_CouponDate:= NULL;
            END CASE;
            --PriorDate
            o_PriorDate    := v_CouponDate ;
            o_ExdivDate    := o_CouponDate - NVL(r_dps.ex_days,0);
            o_CouponPeriod := 0;
            --CouponAmount
             IF (o_CouponPeriod =0 AND v_CouponPeriod =0) THEN o_CouponAmount := v_CouponAmount;
               ELSE
            CASE
                WHEN r_dps.coupon_freq_code  IS NULL THEN o_CouponAmount := NULL;
                WHEN r_dps.coupon_freq_code  NOT IN ('1_D','7_D','28_D','91_D','182_D','1_M','3_M','6_M','12_M')  THEN o_CouponAmount := NULL;
                WHEN r_dps.coupon_freq_code  = '1_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 1           THEN o_CouponAmount := in_coupon/12;
                WHEN r_dps.coupon_freq_code  = '3_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 3           THEN o_CouponAmount := in_coupon/4;
                WHEN r_dps.coupon_freq_code  = '6_M'   AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 6           THEN o_CouponAmount := in_coupon/2;
                WHEN r_dps.coupon_freq_code  = '12_M'  AND MONTHS_BETWEEN(o_CouponDate,o_PriorDate) = 12          THEN o_CouponAmount := in_coupon;
                WHEN r_dps.coupon_freq_code  = '1_D'   AND (o_CouponDate-o_PriorDate) = 1                         THEN o_CouponAmount := in_coupon/365;
                WHEN r_dps.coupon_freq_code  = '7_D'   AND (o_CouponDate-o_PriorDate) = 7                         THEN o_CouponAmount := in_coupon/52;
                WHEN r_dps.coupon_freq_code  = '28_D'  AND (o_CouponDate-o_PriorDate) = 28                         THEN o_CouponAmount := in_coupon/12;
                WHEN r_dps.coupon_freq_code  = '91_D'  AND (o_CouponDate-o_PriorDate) = 91                         THEN o_CouponAmount := in_coupon/4;
                WHEN r_dps.coupon_freq_code  = '182_D' AND (o_CouponDate-o_PriorDate) = 182                       THEN o_CouponAmount := in_coupon/2;
                ELSE o_CouponAmount := ROUND((in_coupon/((o_CouponDate - ADD_MONTHS(o_CouponDate,-12)))) * ((o_CouponDate - o_PriorDate)+1),8);
            END CASE;
            END IF;
            o_Coupon        := in_coupon;
            o_PIKBond       := in_PIKBond;
            o_ABond         := r_dps.ABond;
            o_VCoupon       := in_VCoupon;
            o_VAmortization := r_dps.VAmortization;

    --Insert
            INSERT INTO ETLDBO.STG_CSCHEDULE
            VALUES
            (
              o_SecurityType,
              o_SecurityID,
              o_CouponPeriod,
              o_CouponDate,
              o_PriorDate,
              o_ExdivDate,
              o_Coupon,
              o_CouponAmount,
              o_PIKBond,
              o_ABond,
              o_VCoupon,
              o_VAmortization,
              NULL,
              NULL,
              o_Workflow_RunID,
              SYSDATE,
              SYSDATE,
              o_User,
              SYSDATE,
              o_User
            );
    --Commit
            COMMIT;
          END IF;
          v_CouponDate   :=  o_CouponDate;
          v_PriorDate    :=  o_PriorDate;
          v_CouponPeriod :=  o_CouponPeriod;
          v_CouponAmount :=  o_CouponAmount;
      END LOOP;
  end loop;
END LOOP;

    p_sqlcode := 0;
    p_sqlerrm := NULL;
    p_error   := NULL;

EXCEPTION
   WHEN OTHERS THEN
      p_sqlcode := SQLCODE;
      p_sqlerrm := SQLERRM;
      p_error   := o_SecurityID||o_CouponDate;
END sp_cschedule;