CREATE OR REPLACE VIEW PEND_ANX_IV_POST_VIEW AS ((SELECT CHCODE DOCCODE,CHDATE DOCDATE,CHTIME DOCTIME,NULL CHK_ANXIV,a.unitcode,a.partycode, NULL CHALLANNO,'A' O_BY,HOLD_ANIV FROM CHALLAN57F4 A WHERE EXISTS (SELECT /*+ USE INDEX*/ CHCODE FROM CHALLAN57F4ITEMS B WHERE CHCODE = A.CHCODE AND EXISTS (SELECT /*+USE INDEX*/ ITEMCODE FROM ITEMMST WHERE ITEMCODE = B.ITEMCODE AND EXREG IS NOT NULL AND UNITCODE = A.UNITCODE)) AND NOT EXISTS (SELECT CHCODE FROM JOB_POSTING WHERE CHCODE = A.CHCODE)) UNION (SELECT MRNCODE,MRNDATE ,MRNTIME,CHK_ANXIV,d.unitcode,d.partycode, CHALLANNO,'B' O_BY,HOLD_ANIV FROM MATERIALRECEIPTNOTE D WHERE TRANS_TYPE IN ('Return of Job Work (O)','Rejection of Job Work(O)') AND EXE_NON = 'Y' AND REFMRNCODE IS NULL AND MRNTYPE != 'SUBSIDARY' AND EXISTS (SELECT /*+ USE INDEX*/ MRNCODE FROM MRNITEMS E WHERE MRNCODE = D.MRNCODE AND EXISTS (SELECT /*+ USE INDEX*/ITEMCODE FROM ITEMMST WHERE ITEMCODE = E.ITEMCODE AND EXREG IS NOT NULL AND UNITCODE = D.UNITCODE)) AND NOT EXISTS (select mrncode from JOB_POSTING where MRNCODE = D.MRNCODE))) ********************** CREATE OR REPLACE VIEW PEND_ANX_V_POST_VIEW AS (SELECT MRNCODE,MRNDATE,MRNTIME,CHK_ANXV,PARTYCODE,UNITCODE,CHALLANNO,'A' O_BY,HOLD_ANV FROM MATERIALRECEIPTNOTE A WHERE TRANS_TYPE = 'Job Work (I)' AND STOREFORWARDDATE IS NOT NULL AND STOREFORWARDTIME IS NOT NULL AND EXE_NON = 'Y' AND REFMRNCODE IS NULL AND MRNTYPE != 'SUBSIDARY' AND REFMRNCODE IS NULL AND EXISTS (SELECT /*+ USE INDEX*/ MRNCODE FROM MRNITEMS B WHERE MRNCODE = A.MRNCODE AND EXISTS (SELECT /*+ USE INDEX*/ITEMCODE FROM ITEMMST WHERE ITEMCODE= B.ITEMCODE AND EXREG IS NOT NULL AND UNITCODE = A.UNITCODE)) AND NOT EXISTS (SELECT mrncode from JOB_POSTING_INWARD where MRNCODE = A.MRNCODE) UNION SELECT SBCODE MRNCODE, SBDATE MRNDATE,TIMEDISPATCH MRNTIME,NULL CHK_ANXV,PARTYCODE, UNITCODE,NULL CHALLANNO,'B' O_BY,HOLD_ANV FROM SUBSIDARYCHALLAN A WHERE EXISTS (SELECT /*+ USE INDEX*/SBCODE FROM SCITEMS SC WHERE SBCODE = A.SBCODE AND EXISTS (SELECT /*+ USE INDEX*/ITEMCODE FROM ITEMMST IM WHERE IM.ITEMCODE=SC.ITEMCODE AND EXREG IS NOT NULL)) AND NOT EXISTS (SELECT DRCODE FROM JOB_POSTING_INWARD WHERE DRCODE = A.SBCODE) )