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)