Home » SQL & PL/SQL » SQL & PL/SQL » Stored Proc not inserting all records
Stored Proc not inserting all records [message #644715] |
Sun, 15 November 2015 23:30 |
|
kr1347
Messages: 16 Registered: September 2014
|
Junior Member |
|
|
Hi Below is the stored procedure i have written .The Cursor c_src query has about 437 records .. I wanted for each record in cursor c_src it has to loop through cursor r_dsp and insert into table
But it is actually inserting only one record
Please help me out
Code is here
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;
[EDITED by LF: applied [spoiler] tags]
[Updated on: Tue, 17 November 2015 01:27] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
Re: Stored Proc not inserting all records [message #644757 is a reply to message #644755] |
Mon, 16 November 2015 15:08 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
kr1347 wrote on Mon, 16 November 2015 18:08I am new to this stored proc ..trying to fix it
The first step is in Michel's first point: get rid of (remove) the exception clause (stated there, entirely). It is just hiding the errors which are happening. After removing it from the procedure body, what will be the result of its running?
|
|
|
Goto Forum:
Current Time: Wed Apr 17 22:34:31 CDT 2024
|