DELETE FROM mrelhr.STAFF_LOAN_SCHEDULE
where (LOAN_ID, comp_code) in (select loan_id, comp_code from mrelhr.STAFF_LOAN_MAST where comp_code = 1 and staff_type = 'L')

DELETE from mrelhr.STAFF_LOAN_MAST where comp_code = 1 and staff_type = 'L'



DECLARE
    CURSOR C IS
    SELECT 
       LB_NO LBNO, LOAN_DT, 
       LOAN_AMT, DURATION NO_OF_INST, (LOAN_AMT/DURATION) INST_AMT, 
       BAL_BF, NULL REMARKS, CASE WHEN INST_AMT = 0 AND BAL_BF > 0 THEN 'Y' ELSE 'N' END STOP_FLAG, 
       NULL STOP_UPTO, 'AMOL' INSERTED_BY, SYSDATE INSERTED_ON, 
       'AMOL' MODIFIED_BY, SYSDATE MODIFIED_ON, 1 COMP_CODE, 
       NULL PAY_COMPONENT, 'P' LOAN_TYPE, INTEREST INTST_AMT, 
       'N' LOAN_COMPLETE, 'L' STAFF_TYPE,'N'  INT_FLAG,  LEAST(LOAN_AMT, (LOAN_AMT-(BAL_BF-INTEREST))) LOAN_RE_PAID, 
       GREATEST((INTEREST-BAL_BF), 0) INST_RE_PAID,
       NULL INST_AMT_FIRST, 
       CASE WHEN LOAN_AMT > LEAST(LOAN_AMT, (LOAN_AMT-(BAL_BF-INTEREST))) THEN CASE WHEN (LOAN_AMT/DURATION) <> CASE WHEN INST_AMT = 0 AND INST > 0 THEN INST ELSE INST_AMT END THEN CASE WHEN INST_AMT = 0 AND INST > 0 THEN INST ELSE INST_AMT END END END INST_AMT_MODI, 
       ROUND(INTEREST/ NVL(INTCTR, 1) ) INTST_INST_AMT, 
       INTCTR INST_CNT, NULL PAY_MODE, NULL CHQ_NO, 
       NULL CHQ_DATE
    FROM GJM.HJM_PFLOAN_010417
    WHERE nvl(bal_bf, 0) > 0;
 
    v_LOAN_ID     VARCHAR2(10);
    v_NO_OF_INSTL NUMBER(3);
    v_INST_NO     NUMBER(3);
BEGIN
    FOR R IN C LOOP
            SELECT '16-17'|| LPAD(NVL(MAX(TO_NUMBER(SUBSTR(LOAN_ID,6))),0)+1,5,'0')
            INTO v_LOAN_ID
            FROM MRELHR.STAFF_LOAN_MAST WHERE COMP_CODE = :COMP_CODE
            AND SUBSTR(LOAN_ID, 1, 5) = '16-17';

        INSERT INTO MRELHR.STAFF_LOAN_MAST (
           LOAN_ID, STAFF_CODE, LOAN_DT, 
           LOAN_AMT, NO_OF_INST, INST_AMT, 
           BAL_BF, REMARKS, STOP_FLAG, 
           STOP_UPTO, INSERTED_BY, INSERTED_ON, 
           MODIFIED_BY, MODIFIED_ON, COMP_CODE, 
           PAY_COMPONENT, LOAN_TYPE, INTST_AMT, 
           LOAN_COMPLETE, STAFF_TYPE,INST_AMT_FIRST, INST_AMT_MODI,
           INTST_INST_AMT,INST_CNT) 
        VALUES (v_LOAN_ID, R.LBNO,R.LOAN_DT,
            R.LOAN_AMT,R.NO_OF_INST,R.INST_AMT,
            R.BAL_BF,NULL,R.STOP_FLAG,
            NULL,'AMOL',SYSDATE,
            'AMOL',SYSDATE,:COMP_CODE,
            NULL,r.LOAN_TYPE,R.INTST_AMT,
            'N','L', 0,R.INST_AMT_MODI,
            R.INTST_INST_AMT,R.INST_CNT);
         v_INST_NO := 1; 
        
            INSERT INTO MRELHR.STAFF_LOAN_SCHEDULE (
               LOAN_ID, COMP_CODE, INST_NO, 
               INST_AMT, PAID_ON, STAFF_CODE, 
               INSERTED_BY, INSERTED_ON, MODIFIED_BY, 
               MODIFIED_ON, LOAN_TYPE, INST_TYPE) 
            VALUES (v_LOAN_ID,:COMP_CODE,v_INST_NO,
                R.LOAN_RE_PAID,'31-MAR-17',R.LBNO,
                'AMOL',SYSDATE,'AMOL',
                SYSDATE,r.LOAN_TYPE,'P');
                
           IF    R.INST_RE_PAID  > 0 THEN
            INSERT INTO MRELHR.STAFF_LOAN_SCHEDULE (
               LOAN_ID, COMP_CODE, INST_NO, 
               INST_AMT, PAID_ON, STAFF_CODE, 
               INSERTED_BY, INSERTED_ON, MODIFIED_BY, 
               MODIFIED_ON, LOAN_TYPE, INST_TYPE) 
            VALUES (v_LOAN_ID,:COMP_CODE,v_INST_NO+1,
                R.INST_RE_PAID,'31-MAR-17',R.LBNO,
                'AMOL',SYSDATE,'AMOL',
                SYSDATE,r.LOAN_TYPE,'I');
           
           END IF;
         
    END LOOP;
END; 



DECLARE
    CURSOR C IS
    SELECT 
       LB_NO LBNO, LOAN_DT, 
       LOAN_AMT, DURATION NO_OF_INST, INST_AMT, 
       BAL_BF, NULL REMARKS,  'N'  STOP_FLAG, 
       NULL STOP_UPTO, 'AMOL' INSERTED_BY, SYSDATE INSERTED_ON, 
       'AMOL' MODIFIED_BY, SYSDATE MODIFIED_ON, 1 COMP_CODE, 
       NULL PAY_COMPONENT, 'S' LOAN_TYPE, 0 INTST_AMT, 
       'N' LOAN_COMPLETE, 'L' STAFF_TYPE,'N'  INT_FLAG, LOAN_AMT-BAL_BF LOAN_RE_PAID,
       NULL INST_AMT_FIRST, NULL INST_AMT_MODI, 0 INTST_INST_AMT, 
       0 INST_CNT, NULL PAY_MODE, NULL CHQ_NO, 
       NULL CHQ_DATE
    FROM GJM.HJM_SPLOAN_010417
    WHERE NVL(BAL_BF, 0) > 0;
    v_LOAN_ID     VARCHAR2(10);
    v_NO_OF_INSTL NUMBER(3);
    v_INST_NO     NUMBER(3);
BEGIN
    FOR R IN C LOOP
            SELECT '16-17'|| LPAD(NVL(MAX(TO_NUMBER(SUBSTR(LOAN_ID,6))),0)+1,5,'0')
            INTO v_LOAN_ID
            FROM MRELHR.STAFF_LOAN_MAST WHERE COMP_CODE = :COMP_CODE
            AND SUBSTR(LOAN_ID, 1, 5) = '16-17';

        INSERT INTO MRELHR.STAFF_LOAN_MAST (
           LOAN_ID, STAFF_CODE, LOAN_DT, 
           LOAN_AMT, NO_OF_INST, INST_AMT, 
           BAL_BF, REMARKS, STOP_FLAG, 
           STOP_UPTO, INSERTED_BY, INSERTED_ON, 
           MODIFIED_BY, MODIFIED_ON, COMP_CODE, 
           PAY_COMPONENT, LOAN_TYPE, INTST_AMT, 
           LOAN_COMPLETE, STAFF_TYPE,INST_AMT_FIRST, INST_AMT_MODI,
           INTST_INST_AMT,INST_CNT) 
        VALUES (v_LOAN_ID, R.LBNO,R.LOAN_DT,
            R.LOAN_AMT,R.NO_OF_INST,R.INST_AMT,
            R.BAL_BF,NULL,R.STOP_FLAG,
            NULL,'AMOL',SYSDATE,
            'AMOL',SYSDATE,:COMP_CODE,
            NULL,r.LOAN_TYPE,R.INTST_AMT,
            'N','L', 0,0,
            R.INTST_INST_AMT,R.INST_CNT);
         v_INST_NO := 1; 

            INSERT INTO MRELHR.STAFF_LOAN_SCHEDULE (
               LOAN_ID, COMP_CODE, INST_NO, 
               INST_AMT, PAID_ON, STAFF_CODE, 
               INSERTED_BY, INSERTED_ON, MODIFIED_BY, 
               MODIFIED_ON, LOAN_TYPE, INST_TYPE) 
            VALUES (v_LOAN_ID,:COMP_CODE,v_INST_NO,
                R.LOAN_RE_PAID,'31-MAR-17',R.LBNO,
                'AMOL',SYSDATE,'AMOL',
                SYSDATE,r.LOAN_TYPE,'P');
         
    END LOOP;
END;



DECLARE
    CURSOR C IS
    SELECT 
       LB_NO LBNO, LOAN_DT, 
       LOAN_AMT, DURATION NO_OF_INST, INST_AMT, 
       BAL_BF, NULL REMARKS,  'N'  STOP_FLAG, 
       NULL STOP_UPTO, 'AMOL' INSERTED_BY, SYSDATE INSERTED_ON, 
       'AMOL' MODIFIED_BY, SYSDATE MODIFIED_ON, 1 COMP_CODE, 
       NULL PAY_COMPONENT, 'C' LOAN_TYPE, 0 INTST_AMT, 
       'N' LOAN_COMPLETE, 'L' STAFF_TYPE,'N'  INT_FLAG, LOAN_AMT-BAL_BF LOAN_RE_PAID,
       NULL INST_AMT_FIRST, NULL INST_AMT_MODI, 0 INTST_INST_AMT, 
       0 INST_CNT, NULL PAY_MODE, NULL CHQ_NO, 
       NULL CHQ_DATE
    FROM GJM.HJM_CPLOAN_010417
    WHERE NVL(BAL_BF, 0) > 0;
    v_LOAN_ID     VARCHAR2(10);
    v_NO_OF_INSTL NUMBER(3);
    v_INST_NO     NUMBER(3);
BEGIN
    FOR R IN C LOOP
            SELECT '16-17'|| LPAD(NVL(MAX(TO_NUMBER(SUBSTR(LOAN_ID,6))),0)+1,5,'0')
            INTO v_LOAN_ID
            FROM MRELHR.STAFF_LOAN_MAST WHERE COMP_CODE = :COMP_CODE
            AND SUBSTR(LOAN_ID, 1, 5) = '16-17';

        INSERT INTO MRELHR.STAFF_LOAN_MAST (
           LOAN_ID, STAFF_CODE, LOAN_DT, 
           LOAN_AMT, NO_OF_INST, INST_AMT, 
           BAL_BF, REMARKS, STOP_FLAG, 
           STOP_UPTO, INSERTED_BY, INSERTED_ON, 
           MODIFIED_BY, MODIFIED_ON, COMP_CODE, 
           PAY_COMPONENT, LOAN_TYPE, INTST_AMT, 
           LOAN_COMPLETE, STAFF_TYPE,INST_AMT_FIRST, INST_AMT_MODI,
           INTST_INST_AMT,INST_CNT) 
        VALUES (v_LOAN_ID, R.LBNO,R.LOAN_DT,
            R.LOAN_AMT,R.NO_OF_INST,R.INST_AMT,
            R.BAL_BF,NULL,R.STOP_FLAG,
            NULL,'AMOL',SYSDATE,
            'AMOL',SYSDATE,:COMP_CODE,
            NULL,r.LOAN_TYPE,R.INTST_AMT,
            'N','L', 0,0,
            R.INTST_INST_AMT,R.INST_CNT);
         v_INST_NO := 1; 

            INSERT INTO MRELHR.STAFF_LOAN_SCHEDULE (
               LOAN_ID, COMP_CODE, INST_NO, 
               INST_AMT, PAID_ON, STAFF_CODE, 
               INSERTED_BY, INSERTED_ON, MODIFIED_BY, 
               MODIFIED_ON, LOAN_TYPE, INST_TYPE) 
            VALUES (v_LOAN_ID,:COMP_CODE,v_INST_NO,
                R.LOAN_RE_PAID,'31-MAR-17',R.LBNO,
                'AMOL',SYSDATE,'AMOL',
                SYSDATE,r.LOAN_TYPE,'P');
         
    END LOOP;
END;


SELECT  A.COMP_CODE, SUM(DECODE(B.DR_CR, 'D', B.AMOUNT, -1*B.AMOUNT)) AMT, SUM(DECODE(B.DR_CR, 'C', B.AMOUNT, 0)) CR
FROM    ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B
WHERE   A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE
AND     A.STATUS <> 'C' 
AND     A.VCH_DATE BETWEEN '01-APR-15' AND '30-JUN-15'
AND     A.AUTO_TRANS = 'J'
AND     B.ACC_CODE IN ('0000000000042', '0000000000503', '0000000000294')
GROUP BY A.COMP_CODE



SELECT     A.MR_DATE, B.MR_NO, B.ACCEPTED_JUTE_CODE JUTE_CODE, B.ACTUAL_QTY
FROM       JUTEOFFICE.JUTEOFFICE_MR_MAST A, JUTEOFFICE.JUTEOFFICE_MR_DTLS B
WHERE     A.MR_NO = B.MR_NO AND A.COMP_CODE = B.COMP_CODE
AND         A.COMP_CODE = 2
AND         B.GODOWN_CODE IS NOT NULL
AND         A.MR_DATE =  '18-AUG-16' 

DECLARE
CURSOR C IS
SELECT  A.TRANS_DATE, B.MILLRECEIPT_NO, B.JUTE_CODE--, B.JUTE_QTY
FROM    JUTEOFFICE.JUTEOFFICE_RAWJUTE_ISSUE_MAST A, JUTEOFFICE.JUTEOFFICE_RAWJUTE_ISSUE_DTLS B
WHERE A.TRANS_ID = B.TRANS_ID AND A.COMP_CODE = B.COMP_CODE
AND NVL(B.CAN_FLAG, 'N') = 'N'
AND A.COMP_CODE = 2
--AND A.TRANS_TYPE = 'R'
AND A.TRANS_DATE >=  '01-APR-16' 
--AND        B.TRANS_ID NOT IN (SELECT TRANS_ID FROM JUTEOFFICE.JUTEOFFICE_RAWJUTE_STOCK_DTLS WHERE CAN_FLAG = 'Y')
MINUS
SELECT  A.TRANS_DATE, B.MILLRECEIPT_NO, B.JUTE_CODE--, B.JUTE_QTY
FROM    JUTEOFFICE.JUTEOFFICE_RAWJUTE_STOCK_MAST A, JUTEOFFICE.JUTEOFFICE_RAWJUTE_STOCK_DTLS B
WHERE A.TRANS_ID = B.TRANS_ID AND A.COMP_CODE = B.COMP_CODE
AND NVL(B.CAN_FLAG, 'N') = 'N'
AND A.COMP_CODE = 2
AND A.TRANS_TYPE = 'I'
AND A.TRANS_DATE >=  '01-APR-16' ;

BEGIN
FOR R IN C LOOP
    UPDATE JUTEOFFICE.JUTEOFFICE_RAWJUTE_ISSUE_DTLS A
    SET A.MODIFIED_ON = A.MODIFIED_ON+1
    WHERE TRANS_DATE = R.TRANS_DATE AND A.MILLRECEIPT_NO = R.MILLRECEIPT_NO AND A.JUTE_CODE = R.JUTE_CODE;
END LOOP;    

END;


------------------
select * from STORES.VU_STORES_CWI where comp_code = 100

select distinct sr_no from(
select sr_no, item_code
from stores.stores_sr_dtls
where comp_code = 100
and ACCAT = 'H')

select sr, count(1) from(
select vch_no, vch_date, vch_amount, substr(narration, instr(narration, 'NO')+3) sr
from accounts.accounts_voucher_mast
where auto_trans = 'T' and comp_code = 100
and vch_type = 'F' and status <> 'C'
order by 4
)
group by sr
having count(1) > 1
--------------------

24308100
GANESH DA-9432866054
GANESH DA-9433226826

SELECT B.MR_NO, A.MR_AMT, B.BILL_AMT, A.MR_AMT-B.BILL_AMT DIFF
FROM(
    SELECT VCH_NO, TRIM(SUBSTR(VCH_NO, 1, 5)||LPAD(SUBSTR(NARRATION, INSTR(NARRATION, '.')+2, (INSTR(NARRATION, 'DATED')-4)- INSTR(NARRATION, '.')+2), 6, 0)) MR_NO, VCH_AMOUNT MR_AMT
    FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST
    WHERE COMP_CODE = :COMP
    AND VCH_DATE BETWEEN :stdt and :endt
    AND STATUS <> 'C'
    AND AUTO_TRANS = 'J'
    AND VCH_TYPE = 'F'
    ORDER BY 1
    ) A,
    (
    SELECT VCH_NO, TRIM(SUBSTR(VCH_NO, 1, 5)||LPAD(SUBSTR(NARRATION, INSTR(NARRATION, 'No.')+4, (INSTR(NARRATION, 'UNDER')-9)- INSTR(NARRATION, 'No.')+4), 5, 0)) MR_NO, VCH_AMOUNT BILL_AMT
    FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST
    WHERE COMP_CODE = :COMP
    AND VCH_DATE BETWEEN :stdt and :endt
    AND STATUS <> 'C'
    AND AUTO_TRANS = 'J'
    AND VCH_TYPE = 'D'
    --AND ACC_CODE = '0000000000332'
    AND NARRATION LIKE ('BEING THE ENTRY TAKEN TO ACCOUNT PURCHASE OF RAW JUTE FROM%')
    ORDER BY 1
    ) B
WHERE   A.MR_NO = B.MR_NO(+)   
AND     NVL(A.MR_AMT, 0) <> NVL(B.BILL_AMT, 0)
AND     NVL(B.BILL_AMT, 0) > 0


SELECT A.INV_NO, A.COMP_CODE, B.BILL_DATE, A.IMP_EXP, B.IMP_EXP IM_EXP, B.BILL_AMT, B.BILL_AMT-B.IMP_EXP DIFF
FROM(
    SELECT  INV_NO, COMP_CODE, SUM(IMP_EXP) IMP_EXP
    FROM    JUTEOFFICE.JUTE_CNFBILL_MAST_SINGLE_MR
    GROUP BY INV_NO, COMP_CODE
    ) A,
    (
    SELECT  A.BILL_DATE, A.INV_NO, A.COMP_CODE, A.IMP_EXP, A.BILL_AMT, (SELECT COUNT(DISTINCT BILL_TYPE) FROM JUTEOFFICE.JUTEOFFICE_CNFBILL_MAST WHERE INV_NO = A.INV_NO AND COMP_CODE = A.COMP_CODE AND NVL(BILL_AMT, 0) > 0) CNT
    FROM    JUTEOFFICE.JUTEOFFICE_CNFBILL_MAST_SINGLE A
    ) B
WHERE A.INV_NO = B.INV_NO AND A.COMP_CODE = B.COMP_CODE
--AND B.COMP_CODE = :COMP_
AND   B.CNT = 3


SELECT  A.MR_NO, A.COMP_CODE, A.BILL_DT, A.DIFF, B.ACCEPTED_JUTE_CODE, JUTEOFFICE.MR_FINAL_RATE(A.MR_NO,A.COMP_CODE, B.SLNO) AMT
FROM(
    SELECT  A.INV_NO, A.COMP_CODE, A.MR_NO, A.IMP_EXP, ROUND(A.BILL_EXP*A.IMP_EXP/A.TOT_IMP_EXP) BILL_AMT, ROUND(A.BILL_EXP*A.IMP_EXP/A.TOT_IMP_EXP)-A.IMP_EXP DIFF,
            TO_DATE((SELECT BILL_DATE FROM JUTEOFFICE.JUTEOFFICE_CNFBILL_MAST_SINGLE WHERE INV_NO = A.INV_NO AND COMP_CODE = A.COMP_CODE)) BILL_DT
    FROM(
        SELECT  A.INV_NO, A.COMP_CODE, A.MR_NO, A.IMP_EXP, 
                (SELECT IMP_EXP FROM JUTEOFFICE.JUTEOFFICE_CNFBILL_MAST_SINGLE WHERE INV_NO = A.INV_NO AND COMP_CODE = A.COMP_CODE) TOT_IMP_EXP,
                (SELECT BILL_AMT FROM JUTEOFFICE.JUTEOFFICE_CNFBILL_MAST_SINGLE WHERE INV_NO = A.INV_NO AND COMP_CODE = A.COMP_CODE) BILL_EXP
        FROM    JUTEOFFICE.JUTE_CNFBILL_MAST_SINGLE_MR A
        ) A
    ) A, JUTEOFFICE.JUTEOFFICE_MR_DTLS B
WHERE   A.MR_NO = B.MR_NO AND A.COMP_CODE = B.COMP_CODE
AND     B.GODOWN_CODE IS NOT NULL 
ORDER BY 2, 1 


-----------adv adjusted not posted in ac-------
SELECT  *
from    stores.STORES_PAY_ADVISE_MAST A
where A.PAY_TYPE = 'A'
 AND A.PAID_STATUS = 'N'
 AND NVL(A.SANC_TOT_AMT, 0) > 0
 and exists (select PAY_ADVISE_NO from stores.STORES_PAY_ADVISE_DTLS WHERE PAY_ADVISE_NO = A.PAY_ADVISE_NO AND COMP_CODE = A.COMP_CODE)


----------------------

SELECT A.VCH_NO NEW_VCH_NO, A.COMP_CODE, A.VCH_DATE, A.VCH_TYPE, A.VCH_AMOUNT, A.CHQ_NO, A.MODIFIED_BY, A.MODIFIED_ON, B.VCH_NO OLD_VCH_NO
FROM(
    SELECT  A.VCH_NO, A.COMP_CODE, A.VCH_DATE, A.VCH_TYPE, A.VCH_AMOUNT, A.NARRATION, MODIFIED_BY, TRIM(MODIFIED_ON) MODIFIED_ON, 
            TRIM(SUBSTR(A.NARRATION, 22, INSTR(A.NARRATION, 'AS GRATUITY') - 22)) LB_NAME,
            SUBSTR(A.NARRATION, INSTR(A.NARRATION, 'BY CHQ NO')+9, 7) CHQ_NO
    FROM    ACCOUNTS.ACCOUNTS_VOUCHER_MAST A
    WHERE   A.STATUS <> 'C'
    AND     A.VCH_DATE < '01-APR-15'
    AND     A.AUTO_TRANS = 'G'
    AND     MODIFIED_ON >= '01-APR-15'
    ) A,
    (
    SELECT  A.VCH_NO, A.COMP_CODE, A.VCH_DATE, A.VCH_TYPE, A.VCH_AMOUNT, A.NARRATION, CAN_BY MODIFIED_BY, TRIM(CAN_DATE) MODIFIED_ON, 
            TRIM(SUBSTR(A.NARRATION, 22, INSTR(A.NARRATION, 'AS GRATUITY') - 22)) LB_NAME,
            SUBSTR(A.NARRATION, INSTR(A.NARRATION, 'BY CHQ NO')+9, 7) CHQ_NO
    FROM    ACCOUNTS.ACCOUNTS_VOUCHER_MAST A
    WHERE   A.STATUS = 'C'
    AND     A.VCH_DATE < '01-APR-15'
    AND     A.AUTO_TRANS = 'G'
    ) B
WHERE A.COMP_CODE = B.COMP_CODE AND A.VCH_DATE = B.VCH_DATE
AND A.VCH_TYPE = B.VCH_TYPE AND A.VCH_AMOUNT = B.VCH_AMOUNT 
AND A.CHQ_NO = B.CHQ_NO AND A.MODIFIED_BY = B.MODIFIED_BY AND A.MODIFIED_ON = B.MODIFIED_ON


**********************************************************



DECLARE
    CURSOR C IS
    SELECT Q.COMP_CODE,Q.MR_NO,Q.MR_DATE,Q.VENDOR_CODE,Q.VENDOR_NAME,Q.PARTY_BILL_NO,Q.TOTAL_AMT,Q.CHQ_NO,Q.PAID_DATE,Q.DAYS,Q.INTT, Q.INSERTED_BY
    FROM
    (SELECT P.COMP_CODE,P.MR_NO,P.MR_DATE,P.VENDOR_CODE,P.VENDOR_NAME,P.PARTY_BILL_NO,P.TOTAL_AMT,P.CHQ_NO,TO_DATE(P.PAID_DATE,'DD/MM/RRRR') PAID_DATE,P.INSERTED_BY, 
    ((TO_DATE(P.PAID_DATE,'DD/MM/RRRR')-P.MR_DATE)-P.V_GRACE) DAYS,
    ROUND(((P.TOTAL_AMT*P.INTT_RATE)/100)*(((TO_DATE(P.PAID_DATE,'DD/MM/RRRR')-P.MR_DATE)-P.V_GRACE)/365),0) INTT
    FROM
    (SELECT A.COMP_CODE,A.MR_NO,A.MR_DATE,B.VENDOR_CODE,X.INSERTED_BY,
    (SELECT VENDOR_NAME FROM MREL.MREL_RAW_JUTE_VENDOR_MAST
     WHERE VENDOR_CODE=B.VENDOR_CODE) VENDOR_NAME,
    B.PARTY_BILL_NO,B.TOTAL_AMT,X.CHQ_NO,
    (CASE WHEN Y.ADV_ADJUST = 'Y' THEN (SELECT TO_CHAR(PAY_ADVISE_DATE, 'DD/MM/RRRR') 
    FROM JUTEOFFICE.JUTEOFFICE_PAY_ADVANCE_MAST WHERE COMP_CODE = NVL(Y.COMP_CODE_ADV, Y.COMP_CODE) AND PAY_ADVISE_NO =  Y.ADVANCE_NO) ELSE TO_CHAR(X.PAY_ADVISE_DATE, 'DD/MM/RRRR') END) PAID_DATE,
    (SELECT NVL(GRACE_PERIOD,0) FROM JUTEOFFICE.JUTEOFFICE_PAYMENT_TERMS
                WHERE TRIM(PAYMENT_TERMS)=(SELECT TRIM(PAY_TYPE) FROM JUTEOFFICE.JUTEOFFICE_PO_MAST
                WHERE COMP_CODE=A.COMP_CODE AND PO_NO=(SELECT PO_NO FROM JUTEOFFICE.JUTEOFFICE_MR_MAST
                WHERE MR_NO=A.MR_NO AND COMP_CODE=A.COMP_CODE))) V_GRACE,
                (SELECT NVL(INTT_RATE,0) FROM JUTEOFFICE.JUTE_MR_INTT_RATE
            WHERE WEF=(SELECT MAX(WEF) FROM JUTEOFFICE.JUTE_MR_INTT_RATE WHERE WEF<=A.MR_DATE)) INTT_RATE
    FROM JUTEOFFICE.JUTEOFFICE_MR_MAST A,JUTEOFFICE.JUTEOFFICE_PARTY_BILL_MAST B,JUTEOFFICE.JUTEOFFICE_PAY_ADVISE_MAST X, 
    JUTEOFFICE.JUTEOFFICE_PAY_ADVISE_DTLS Y
    WHERE A.MR_NO=B.MR_NO AND A.COMP_CODE=B.COMP_CODE
    AND X.PAY_ADVISE_NO = Y.PAY_ADVISE_NO AND X.COMP_CODE = Y.COMP_CODE 
    AND Y.BILL_NO =B.BILL_NO AND Y.COMP_CODE =A.COMP_CODE
    AND B.VENDOR_CODE NOT IN('646','666','685','690')
    --AND A.MR_NO=MRNO
    AND X.PAY_ADVISE_DATE >= '01-SEP-16'
    AND A.COMP_CODE=:COMPCODE
    AND A.COMP_CODE IN(SELECT COMP_CODE FROM MREL.MREL_COMPANY_MAST
    WHERE GROUP_CODE=(SELECT DISTINCT GROUP_CODE FROM MREL.MREL_COMPANY_MAST WHERE COMP_CODE=:COMPCODE))
    AND NOT EXISTS (SELECT MR_NO FROM JUTEOFFICE.JUTEOFFICE_INTT_BILL_MAST WHERE MR_NO = A.MR_NO AND COMP_CODE = A.COMP_CODE)
    ) P
    WHERE P.V_GRACE>0
    ) Q;

BEGIN
    FOR R IN C LOOP
        JUTEOFFICE.JUTEOFFICE_INTT_BILL(R.mr_no,R.comp_code,R.inserted_by);
    END LOOP;
    COMMIT;
END;

******************************************************************



SELECT A.VCH_NO, A.VCH_TYPE, A.COMP_CODE, A.VCH_DATE, A.DAY_SERIAL, SUM(DECODE(B.DR_CR, 'D', B.AMOUNT)) DR_AMT, SUM(DECODE(B.DR_CR, 'C', B.AMOUNT)) CR_AMT
FROM   ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B
WHERE  A.FIN_YEAR = B.FIN_YEAR
AND    A.VCH_NO = B.VCH_NO
AND    A.VCH_TYPE = B.VCH_TYPE
AND    A.COMP_CODE = B.COMP_CODE
AND    A.STATUS <> 'C'
HAVING NVL(SUM(DECODE(B.DR_CR, 'D', B.AMOUNT)), 0) <> NVL(SUM(DECODE(B.DR_CR, 'C', B.AMOUNT)), 0)
GROUP BY A.VCH_NO, A.VCH_TYPE, A.COMP_CODE, A.VCH_DATE, A.DAY_SERIAL


SELECT A.VCH_NO, A.VCH_TYPE, A.COMP_CODE, A.VCH_DATE, A.DAY_SERIAL, SUM(DECODE(B.DR_CR, 'D', B.ADJ_AMOUNT)) DR_AMT, SUM(DECODE(B.DR_CR, 'C', B.ADJ_AMOUNT)) CR_AMT
FROM   ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B
WHERE  A.FIN_YEAR = B.FIN_YEAR
AND    A.VCH_NO = B.VCH_NO
AND    A.VCH_TYPE = B.VCH_TYPE
AND    A.COMP_CODE = B.COMP_CODE
AND    A.STATUS <> 'C'
HAVING NVL(SUM(DECODE(B.DR_CR, 'D', B.ADJ_AMOUNT)), 0) <> NVL(SUM(DECODE(B.DR_CR, 'C', B.ADJ_AMOUNT)), 0)
GROUP BY A.VCH_NO, A.VCH_TYPE, A.COMP_CODE, A.VCH_DATE, A.DAY_SERIAL
ORDER BY A.COMP_CODE


DELETE FROM ACCOUNTS.ACCOUNTS_BILL_DTLS
WHERE (VCH_NO, COMP_CODE, ACC_CODE) IN 
(
SELECT A.VCH_NO, A.COMP_CODE, B.ACC_CODE
FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B
WHERE A.VCH_NO = B.VCH_NO
AND   A.COMP_CODE = B.COMP_CODE
AND   A.STATUS <> 'C'
MINUS
SELECT A.VCH_NO, A.COMP_CODE, B.ACC_CODE
FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B
WHERE A.VCH_NO = B.VCH_NO
AND   A.COMP_CODE = B.COMP_CODE
AND   A.STATUS <> 'C'
)


SELECT A.LBNO, A.LB_NAME, A.NEW_CAT, A.JOIN_DATE, A.DEPT_CODE, B.TOT, A.INSERTED_ON
FROM(
    SELECT LBNO, LB_NAME, NEW_CAT, JOIN_DATE, DEPT_CODE, INSERTED_ON
    FROM MRELHR.HR_EMP_MAST M
    WHERE COMP_CODE = 1
    AND ACTUAL_ONROLL = 'Y'
    --AND NEW_CAT = 'X'
    AND LBNO IN (SELECT DISTINCT LBNO FROM MRELHR.HR_ATTND_DTLS WHERE COMP_CODE = M.COMP_CODE AND ADATE BETWEEN '16-MAY-17' AND '31-MAY-17')
    ) A,
    (
    SELECT A.LBNO,
           B.BASIC,
           C.DA,
           D.INC,
           E.ADH,
           F.WBI,
           ROUND (NVL (B.BASIC, 0)+ NVL (C.DA, 0)+ NVL (D.INC, 0)+ NVL (E.ADH, 0)+ NVL (F.WBI, 0),2) TOT
    FROM   (  SELECT   LBNO
                FROM   MRELHR.HR_EMP_PAY
               WHERE   COMP_CODE = :COMP
            GROUP BY   LBNO) A,
           (SELECT   LBNO, CUR_VALUE * 8 BASIC
              FROM   MRELHR.HR_EMP_PAY
             WHERE   COMP_CODE = :COMP AND COMPONENT_CODE = 7)
           B,
           (SELECT   LBNO, CUR_VALUE / 26 DA
              FROM   MRELHR.HR_EMP_PAY
             WHERE   COMP_CODE = :COMP AND COMPONENT_CODE = 12)
           C,
           (SELECT   LBNO, CUR_VALUE / 26 INC
              FROM   MRELHR.HR_EMP_PAY
             WHERE   COMP_CODE = :COMP AND COMPONENT_CODE = 41)
           D,
           (SELECT   LBNO, CUR_VALUE / 26 ADH
              FROM   MRELHR.HR_EMP_PAY
             WHERE   COMP_CODE = :COMP AND COMPONENT_CODE = 23)
           E,
           (SELECT   LBNO, CUR_VALUE / 26 WBI
              FROM   MRELHR.HR_EMP_PAY
             WHERE   COMP_CODE = :COMP AND COMPONENT_CODE = 42)
           F
    WHERE       A.LBNO = B.LBNO(+)
           AND A.LBNO = C.LBNO(+)
           AND A.LBNO = D.LBNO(+)
           AND A.LBNO = E.LBNO(+)
           AND A.LBNO = F.LBNO(+)
    ) B
WHERE A.LBNO = B.LBNO(+)



SELECT A.MR_NO, A.MR_AMOUNT, B.BILL_AMOUNT
FROM(
    SELECT  A.FIN_YEAR||LPAD(SUBSTR(A.NARRATION, INSTR(A.NARRATION, 'MR NO.')+7, (INSTR(A.NARRATION, 'DATED')-INSTR(A.NARRATION, 'MR NO.')-8)), 5, 0) MR_NO,
    A.VCH_AMOUNT MR_AMOUNT
    FROM    ACCOUNTS.ACCOUNTS_VOUCHER_MAST A
    WHERE   A.VCH_DATE BETWEEN '01-APR-16' AND '31-MAR-17'
    AND     A.COMP_CODE = :COMP_CODE
    AND     A.VCH_TYPE IN ('F')
    AND     A.AUTO_TRANS    = 'J'
    AND     A.STATUS <> 'C'
    AND     A.NARRATION LIKE 'BEING THE ENTRY TAKEN TO ACCOUNT PURCHASE OF RAW JUTE%'
    ) A,
    (
    SELECT  B.ADJ_NO MR_NO, A.VCH_NO, A.NARRATION, B.ADJ_AMOUNT BILL_AMOUNT
    FROM    ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B
    WHERE   A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE
    AND     A.VCH_DATE BETWEEN '01-APR-16' AND '31-MAR-17'
    AND     A.COMP_CODE = :COMP_CODE
    AND     A.VCH_TYPE IN ('D')
    AND     A.AUTO_TRANS    = 'J'
    AND     A.STATUS <> 'C'
    AND     A.NARRATION LIKE 'BEING THE ENTRY TAKEN TO ACCOUNT PURCHASE OF RAW JUTE FROM%'
    AND     B.ACC_CODE = '0000000000332'
    ) B
WHERE A.MR_NO = B.MR_NO(+)  
AND NVL(A.MR_AMOUNT,0)<>NVL(B.BILL_AMOUNT, 0)