HELLO,
I WANT PASS CUSRSOR OUTPUR AS PARAMETER ( I.E TABLENAME ).
PL/SQL CODE:=
SET SERVEROUTPUT ON;
DECLARE
D VARCHAR2(50);
CURSOR D1 IS SELECT DISTINCT FNAME,PERIOD FROM ACTUAL_SALARY WHERE PERIOD='2014-2015';
BEGIN
DELETE FROM SAL_SUM;
COMMIT;
FOR I IN D1 LOOP
D:=I.FNAME;
INSERT INTO SAL_SUM
SELECT D MONTH,I.PERIOD PERIOD,SUM(EMP) EMP,SUM(BASIC) BASIC ,SUM(HRA) HRA,SUM(CONV) CONV,SUM(EDU) EDU,SUM(NEWS) NEWS,SUM(KIT) KIT,SUM(DA) DA,SUM(PP) PP,SUM(VEHICLE_ALLOW) VEHICLE_ALLOW,SUM(SERVICE_ALLOWANCE) SERVICE_ALLOW,SUM(OTHER_ALLOW) OTHER_ALLOW,SUM(SPINCT) SPINCT,SUM(GROSS) GROSS ,SUM(LIC) LIC,SUM(PF) PF,SUM(ESIC) ESIC,SUM(PT) PT,SUM(INCOME_TAX) INCOME_TAX,SUM(SOCIETY_SHARE) SOCIETY_SHARE,SUM(E_FEE) E_FESS,SUM(ADVANCE) ADVANCE,SUM(LOAN ) LOAN,SUM(INTEREST) INTEREST,SUM(TOTAL_DED) TOTAL_DED,SUM(NET_SALARY) NET_SALARY,PREP_MONTH,PREP_YEAR FROM (SELECT ALL CASE WHEN SETTLE<>'Y' THEN COUNT( DISTINCT EMPCODE) ELSE 0 END "EMP",SUM(A.BASIC) BASIC, SUM(( NVL(A.HRA,0)+NVL(A.SPECIALHRA,0) )) HRA, SUM(A.CONVEYANCE) CONV, SUM(A.EDU_ALLOW) EDU,SUM(A.NEWS_ALLOW) NEWS, SUM(A.KIT_ALLOW) KIT, SUM(A.DA) DA,SUM(A.PERSONALPAY) PP,SUM(( NVL(A.VEHICLE_ALLOW,0) + NVL(A.OTHER_ALLOW1,0) )) VEHICLE_ALLOW,SUM(NVL(A.SPECIAL_HILL_ALLOW,0))SERVICE_ALLOWANCE,SUM((NVL(A.OTHER_ALLOW2,0)+NVL(A.OTHER_ALLOW4,0)
+(NVL(A.DAMAN_ALLOW,0)-NVL(A.REC_DAMAN_ALLOW,0)))) OTHER_ALLOW, SUM(( NVL(A.OTHER_ALLOW3,0) +NVL(A.SPINCT,0))) SPINCT, SUM(A.GROSS_SAL) GROSS, SUM(A.LIC_PREMIUM)LIC, SUM(A.PF)PF, SUM(A.ESIC)ESIC, SUM(A.PT)PT,SUM(A.INCOME_TAX)INCOME_TAX,SUM((A.SOCIETY_SHARE)) SOCIETY_SHARE,SUM(NVL(A.E_FEE,0))E_FEE,SUM((NVL(A.ADVANCE1,0)+NVL(A.ADVANCE2,0))) ADVANCE, SUM( A.MEC_LOAN)LOAN, SUM(A.INTEREST)INTEREST, SUM(A.TOTAL_DED)TOTAL_DED,SUM(A.NET_SALARY)NET_SALARY,A.PREP_MONTH,A.PREP_YEAR FROM D A GROUP BY A.PREP_MONTH,A.PREP_YEAR ,SETTLE) GROUP BY PREP_MONTH,PREP_YEAR;
DBMS_OUTPUT.PUT_LINE(I.FNAME||' - '||I.PERIOD);
COMMIT;
END LOOP;
END;