|
SELECT SHEET_ID , LINE_NBR , 'NO_RECEIPT' FROM PS_EX_SHEET_LINE WHERE RECEIPT_REQ_EX = 'Y' AND NO_RECEIPT_FLG = 'Y' UNION SELECT SHEET_ID , LINE_NBR , 'TAX_IMPLICATIONS' FROM PS_EX_SHEET_LINE WHERE TAX_IMPLICATIONS = 'Y' UNION SELECT SHEET_ID , LINE_NBR , 'OUT_OF_POLICY' FROM PS_EX_SHEET_LINE WHERE OUT_OF_POLICY = 'Y' UNION SELECT SHEET_ID , LINE_NBR , 'DUPLICATES_EXIST' FROM PS_EX_SHEET_LINE WHERE DUPLICATES_EXIST = 'Y' UNION SELECT O.SHEET_ID , O.LINE_NBR , 'VAT_RECEIPT' FROM PS_VAT_ENT_BU_GL L , PS_VAT_ENT_RGSTRN M , PS_EX_SHEET_LINE O , PS_EX_SHEET_HDR P WHERE L.VAT_ENTITY = M.VAT_ENTITY AND O.SHEET_ID = P.SHEET_ID AND O.VAT_RECEIPT = 'Y' AND L.BUSINESS_UNIT = P.BUSINESS_UNIT_GL UNION SELECT O.SHEET_ID , O.LINE_NBR , 'VAT_RECEIPT' FROM PS_BUS_UNIT_TBL_EX N , PS_EX_SHEET_LINE O , PS_EX_SHEET_HDR P WHERE N.VAT_RCRD_FRGN_FLG = 'Y' AND O.SHEET_ID = P.SHEET_ID AND O.VAT_RECEIPT = 'Y' AND N.BUSINESS_UNIT_GL = P.BUSINESS_UNIT_GL UNION SELECT A.SHEET_ID , A.LINE_NBR , 'PREFERRED_MERCHANT_NOT_USED' FROM PS_EX_SHEET_LINE A , PS_EX_TYPES_TBL B , PS_EX_SHEET_HDR C WHERE A.EXPENSE_TYPE = B.EXPENSE_TYPE AND B.PREF_MRCHNT_FLG = 'Y' AND C.SETID = B.SETID AND A.PREF_MRCH_NOT_USED = 'Y' AND A.SHEET_ID = C.SHEET_ID AND B.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.RECNAME = 'EX_TYPES_TBL' AND D.SETCNTRLVALUE = C.BUSINESS_UNIT_GL AND D.REC_GROUP_ID = ( SELECT S.REC_GROUP_ID FROM PS_REC_GROUP_REC S WHERE S.RECNAME = 'EX_TYPES_TBL')) AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_EX_TYPES_TBL B1 WHERE B.SETID = B1.SETID AND B.EFF_STATUS = B1.EFF_STATUS AND B.EXPENSE_TYPE = B1.EXPENSE_TYPE AND B1.EFFDT <= A.TRANS_DT) UNION SELECT SHEET_ID , LINE_NBR , 'OLDER_TRANSACTION' FROM PS_EX_SHEET_LINE WHERE OLDER_TRANSACTION = 'Y' UNION SELECT A.SHEET_ID , A.LINE_NBR , 'PERSONAL_EXPENSE' FROM PS_EX_SHEET_LINE A , PS_EX_SHEET_HDR B , PS_EX_EXP_MTHD_TBL C WHERE A.SHEET_ID = B.SHEET_ID AND A.EXPEND_MTHD = C.EXPEND_MTHD AND C.EXPEND_MTHD_EDIT = 'VND' AND A.PERSONAL_EXPENSE = 'Y' AND C.SETID = ( SELECT T.SETID FROM PS_SET_CNTRL_REC T WHERE T.RECNAME = 'EX_EXP_MTHD_TBL' AND T.SETCNTRLVALUE = B.BUSINESS_UNIT_GL AND T.REC_GROUP_ID = ( SELECT U.REC_GROUP_ID FROM PS_REC_GROUP_REC U WHERE U.RECNAME = 'EX_EXP_MTHD_TBL')) AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_EX_EXP_MTHD_TBL C_ED WHERE C.SETID = C_ED.SETID AND C.EXPEND_MTHD = C_ED.EXPEND_MTHD AND C_ED.EFFDT <= A.TRANS_DT) UNION SELECT E.SHEET_ID , E.LINE_NBR , (CASE WHEN A.CC_FEED_FLG = 'Y' AND E.CC_FEED_FLG = 'Y' THEN 'CREDIT_CARD_FEED' END) FROM PS_EX_REFINE_TMPL A , PS_EX_SHEET_LINE E , PS_EX_SHEET_HDR F WHERE A.APPR_TRANS_TYPE = 'ER' AND A.REFINEMENT_TMPL_ID = ( SELECT B.REFINEMENT_TMPL_ID FROM PS_EX_PROF_ATTR B WHERE B.SETID = A.SETID AND B.APPR_TRANS_TYPE = 'ER' AND B.APPROVER_PROFILE = ( SELECT C.APPROVER_PROFILE FROM PS_EX_PROF_APPRVR C WHERE C.APPRVR_TYPE = ( SELECT D.APPRVR_TYPE FROM PS_EX_APPRVR_TYPE D WHERE D.APPROVER_CATEGORY = 'HRS' AND D.SETID = A.SETID) AND C.SETID = A.SETID)) AND E.SHEET_ID = F.SHEET_ID AND A.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_REC G WHERE G.RECNAME = 'EX_REFINE_TMPL' AND G.SETCNTRLVALUE = F.BUSINESS_UNIT_GL AND G.REC_GROUP_ID = ( SELECT S.REC_GROUP_ID FROM PS_REC_GROUP_REC S WHERE S.RECNAME = 'EX_REFINE_TMPL')) UNION SELECT E.SHEET_ID , E.LINE_NBR , (CASE WHEN E.VAT_AMT <> 0 AND A.VAT_ENTRIES = 'Y' THEN 'VAT_ENTRIES' END) FROM PS_EX_REFINE_TMPL A , PS_EX_SHEET_LINE E , PS_EX_SHEET_HDR F WHERE A.APPR_TRANS_TYPE = 'ER' AND A.REFINEMENT_TMPL_ID = ( SELECT B.REFINEMENT_TMPL_ID FROM PS_EX_PROF_ATTR B WHERE B.SETID = A.SETID AND B.APPR_TRANS_TYPE = 'ER' AND B.APPROVER_PROFILE = ( SELECT C.APPROVER_PROFILE FROM PS_EX_PROF_APPRVR C WHERE C.APPRVR_TYPE = ( SELECT D.APPRVR_TYPE FROM PS_EX_APPRVR_TYPE D WHERE D.APPROVER_CATEGORY = 'HRS' AND D.SETID = A.SETID) AND C.SETID = A.SETID)) AND E.SHEET_ID = F.SHEET_ID AND A.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_REC G WHERE G.RECNAME = 'EX_REFINE_TMPL' AND G.SETCNTRLVALUE = F.BUSINESS_UNIT_GL AND G.REC_GROUP_ID = ( SELECT S.REC_GROUP_ID FROM PS_REC_GROUP_REC S WHERE S.RECNAME = 'EX_REFINE_TMPL'))
|
SHEET_ID