Code:
CREATE OR REPLACE PROCEDURE thqvb_TEST(g_plazano VARCHAR2,g_oprdate VARCHAR2) IS
BEGIN
DECLARE
thq_oprdate date;
thq_plazano varchar2(3);
thq_reconcilecode varchar2(3);
thq_badgeno number(5);
thq_bagno number(4);
thq_jobno number(2);
thq_laneno varchar2(3);
thq_badgename varchar2(25);
thq_totdeclareamount number(10,2);
thq_tottranamount number (10,2);
thq_discrrconcile number (10,2);
thq_discradjustment number (10,2);
thq_adjustamount number (10,2);
thq_reclass number (10,2);
thq_totaltraffic number (10,2);
thq_receiptno number (12);
thq_recount number(5);
CURSOR C1 IS
select distinct HQRH_BADGENO ,HQBD_BADGENAME,
hqrd_reconcilecode,HQRH_TOTDECLAREAMOUNT ,
HQRH_TOTTRANAMOUNT,
Case sign(HQRH_DISCRRECONCILE) when 1 then HQRH_DISCRRECONCILE end as HQRH_DISCRRECONCILE ,
case sign(hqrh_discradjustment) when -1 then (hqrh_discradjustment * -1) end as hqrh_discradjustment,
case sign (hqrh_adjustamount) when 1 then hqrh_adjustamount end as hqrh_adjustamount,
hqrd_reconcileamount,hqrh_receiptno
FROM HQ_CASHRECONCILEHEADER,HQ_BADGE,HQ_cashreconciledetail
WHERE HQ_CASHRECONCILEHEADER.HQRH_BADGENO = HQ_BADGE.HQBD_BADGENO
AND HQ_BADGE.HQBD_BADGENO = HQ_cashreconciledetail.HQRD_BADGENO
AND HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = HQ_cashreconciledetail.HQRD_PLAZANO
AND HQ_CASHRECONCILEHEADER.HQRH_OPERATIONALDATE = HQ_cashreconciledetail.HQRD_OPERATIONALDATE
AND (HQ_CASHRECONCILEHEADER.HQRH_OPERATIONALDATE = TO_DATE(g_oprdate, 'DD/MM/YYYY'))
AND (HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = g_plazano) AND
hqrd_reconcilecode in ('H1','R1','W1','W2','M1','M2','M3','P2','P4','V1','V2');
CURSOR C2 IS
select distinct hqdh_badgeno,hqdh_bagno from hq_cashdeclaredheader;
CURSOR C3 IS
select distinct hqdh_operationaldate,hqjb_laneno,hqjb_jobno
from hq_cashdeclaredheader,hq_job
where hqdh_badgeno = hqjb_badgeno and
hqdh_declaredatetime = hqjb_declaredatetime
and hqdh_operationaldate = to_date(g_oprdate,'dd/mm/yyyy')
and hqdh_badgeno = thq_badgeno and hqdh_bagno = thq_bagno order by hqjb_jobno;
-- CURSOR C4 IS
--
-- select hqjd_operationaldate,hqjd_laneno,hqjd_jobno,sum(hqjd_tranclass1+hqjd_tranclass2+hqjd_tranclass3+hqjd_tranclass4+hqjd_tranclass5) as totaltraffic
-- from hq_jobpaymentdetails where hqjd_operationaldate = to_date(g_oprdate,'dd/mm/yyyy')
-- and hqjd_laneno = thq_laneno and hqjd_jobno = thq_jobno
-- and hqjd_paymenttype in ('CSH','SVR','EXM','AZC') group by hqjd_operationaldate,hqjd_laneno,hqjd_jobno order by hqjd_jobno;
BEGIN
DELETE T_HQ_CASHRECHEADER ;
COMMIT;
OPEN C1;
FETCH C1 INTO thq_badgeno, thq_badgename,thq_reconcilecode,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno;
LOOP
EXIT WHEN C1%NOTFOUND;
select count (*) into thq_recount from T_HQ_CASHRECHEADER
where thqcrh_plazano = g_plazano
and THQCRH_OPERATIONALDATE = g_oprdate;
if thq_recount=0 then
if thq_reconcilecode ='H1' then
INSERT INTO T_HQ_CASHRECHEADER (thqcrh_plazano,THQCRH_OPERATIONALDATE,thqcrh_badgeno,thqcrh_badgename,thqcrh_totdeclareamount,thqcrh_totgrossamount,thqcrh_excess,thqcrh_discradjustment,thqcrh_adjustamount,thqcrh_mtlh,thqcrh_receiptno)
VALUES (thq_plazano,thq_oprdate,thq_badgeno, thq_badgename,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno);
end if;
if thq_reconcilecode ='R1' then
INSERT INTO T_HQ_CASHRECHEADER (thqcrh_plazano,THQCRH_OPERATIONALDATE,thqcrh_badgeno,thqcrh_badgename,thqcrh_totdeclareamount,thqcrh_totgrossamount,thqcrh_excess,thqcrh_discradjustment,thqcrh_adjustamount,thqcrh_reclass,thqcrh_receiptno)
VALUES (thq_plazano,thq_oprdate,thq_badgeno, thq_badgename,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno);
end if;
if thq_reconcilecode in ('M1' ,'M2','M3') then
INSERT INTO T_HQ_CASHRECHEADER (thqcrh_plazano,THQCRH_OPERATIONALDATE,thqcrh_badgeno,thqcrh_badgename,thqcrh_totdeclareamount,thqcrh_totgrossamount,thqcrh_excess,thqcrh_discradjustment,thqcrh_adjustamount,thqcrh_mtlm,thqcrh_receiptno)
VALUES (thq_plazano,thq_oprdate,thq_badgeno, thq_badgename,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno);
end if;
if thq_reconcilecode in ('W1','W2') then
INSERT INTO T_HQ_CASHRECHEADER (thqcrh_plazano,THQCRH_OPERATIONALDATE,thqcrh_badgeno,thqcrh_badgename,thqcrh_totdeclareamount,thqcrh_totgrossamount,thqcrh_excess,thqcrh_discradjustment,thqcrh_adjustamount,thqcrh_wrongbutton,thqcrh_receiptno)
VALUES (thq_plazano,thq_oprdate,thq_badgeno, thq_badgename,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno);
end if;
if thq_reconcilecode IN ('V1','V2') then
INSERT INTO T_HQ_CASHRECHEADER (thqcrh_plazano,THQCRH_OPERATIONALDATE,thqcrh_badgeno,thqcrh_badgename,thqcrh_totdeclareamount,thqcrh_totgrossamount,thqcrh_excess,thqcrh_discradjustment,thqcrh_adjustamount,thqcrh_violv,thqcrh_receiptno)
VALUES (thq_plazano,thq_oprdate,thq_badgeno, thq_badgename,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno);
end if;
if thq_reconcilecode IN ('P1','P2') then
INSERT INTO T_HQ_CASHRECHEADER (thqcrh_plazano,THQCRH_OPERATIONALDATE,thqcrh_badgeno,thqcrh_badgename,thqcrh_totdeclareamount,thqcrh_totgrossamount,thqcrh_excess,thqcrh_discradjustment,thqcrh_adjustamount,thqcrh_violP,thqcrh_receiptno)
VALUES (thq_plazano,thq_oprdate,thq_badgeno, thq_badgename,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno);
end if;
END IF;
FETCH C1 INTO thq_badgeno, thq_badgename,thq_reconcilecode,thq_totdeclareamount,thq_tottranamount,thq_discrrconcile, thq_discradjustment,thq_adjustamount,thq_reclass,thq_receiptno;
END LOOP;
COMMIT;
CLOSE C1;
DELETE T_HQ_CASHRECHEADER;
COMMIT;
thq_recount :=0 ;
OPEN C2;
FETCH C2 INTO thq_badgeno, thq_bagno ;
LOOP
EXIT WHEN C2%NOTFOUND;
OPEN C3;
FETCH C3 INTO thq_oprdate, thq_laneno, thq_jobno ;
LOOP
EXIT WHEN C3%NOTFOUND;
-- OPEN C4;
--
-- FETCH C4 INTO thq_totaltraffic ;
--
-- LOOP
-- EXIT WHEN C4%NOTFOUND;
thq_recount :=1;
select SUM(hqjd_tranclass1+hqjd_tranclass2+hqjd_tranclass3+hqjd_tranclass4+hqjd_tranclass5)
into thq_totaltraffic from hq_jobpaymentdetails where hqjd_operationaldate = to_date(g_oprdate,'dd/mm/yyyy')
and hqjd_laneno = thq_laneno and hqjd_jobno = thq_jobno
and hqjd_paymenttype in ('CSH','SVR','EXM','AZC') group by hqjd_operationaldate,hqjd_laneno,hqjd_jobno
order by hqjd_jobno;
insert into T_HQ_CASHRECHEADER ( thq_oprdate,thq_laneno,thq_jobno,thq_totaltraffic)
VALUES (g_oprdate,thq_laneno,thq_jobno,thq_totaltraffic);
FETCH C3 INTO thq_oprdate, thq_laneno, thq_jobno ;
END LOOP;
CLOSE C3;
if thq_recount=0 then
insert into T_HQ_CASHRECHEADER ( thq_oprdate,thq_laneno,thq_jobno,thq_totaltraffic)
VALUES (g_oprdate,thq_laneno,thq_jobno,thq_totaltraffic);
end if;
thq_recount := 0;
FETCH C2 INTO thq_badgeno, thq_bagno;
END LOOP;
COMMIT;
CLOSE C2;
END;
END;
/
hai this is madhavi,
i wrote one stored procedure but when i execute that sp iam getting the above error.
i have no idea why its coming can u plz anyone give any suggestions.
its little bit urgent. cau plz anyone suggest me.
Thanks in advance.
Comment