|
SELECT a.business_unit , a.voucher_id , a.invoice_id , a.invoice_dt , a.vendor_id , a.gross_amt , a.txn_currency_cd FROM PS_VOUCHER a , ps_set_cntrl_rec b , ps_bus_unit_opt_ap c WHERE a.appr_status = 'P' AND a.voucher_style <> 'TMPL' AND a.vchr_apprvl_flg = 'W' AND a.close_status <> 'C' AND a.process_man_close <> 'Y' AND a.entry_status = 'P' AND A.MATCH_STATUS_VCHR IN ('N', 'M', 'O') AND B.SETCNTRLVALUE = A.BUSINESS_UNIT AND B.RECNAME = 'BUS_UNIT_OPT_AP' AND B.REC_GROUP_ID = 'AP_01' AND B.SETID = C.SETID AND ((C.REQ_VALID_KKDT = 'N') OR (C.REQ_VALID_KKDT = 'Y' AND a.doc_tol_hdr_status = 'V' AND a.budget_hdr_status = 'V' AND a.budget_misc_status = 'V')) AND c.effdt = ( SELECT MAX(cc.effdt) FROM ps_bus_unit_opt_ap cc WHERE cc.setid = c.setid AND cc.effdt <= a.invoice_dt) AND c.eff_status = 'A' AND NOT EXISTS ( SELECT 'X' FROM PS_VCHR_AF_XREF XREF WHERE XREF.BUSINESS_UNIT = a.BUSINESS_UNIT AND XREF.VOUCHER_ID = a.VOUCHER_ID AND XREF.EOAWTHREAD_STATUS = 'P') AND ((a.vchr_src IN ('AMLS', 'ARCR', 'BILL', 'CLBK', 'CNTR', 'CONS', 'EDI', 'FRE', 'MTCH', 'NERS', 'ONL', 'PAYR', 'PBIL', 'PERS', 'PPAY', 'PROC', 'QUCK', 'RBCR', 'RETL', 'RPOV', 'RTV', 'SAD', 'SPRO', 'TRPM', 'XLS','PRQ')) OR (A.vchr_src IN ('XML','DCM','SSI','EXL') AND EXISTS ( SELECT 'X' FROM PS_EM_BUY_REG_TBL D WHERE D.BUSINESS_UNIT = a.BUSINESS_UNIT AND D.EM_USE_AP_APPROVAL = 'Y')) OR (A.VCHR_SRC IN ('XML','DCM') AND NOT EXISTS ( SELECT 'X' FROM PS_EM_BUY_REG_TBL H , PS_EM_AGREEMENT g , PS_SET_CNTRL_REC i WHERE i.setcntrlvalue = a.business_unit AND i.rec_group_id = 'EM_01' AND i.recname = 'EM_AGREEMENT' AND i.setid = g.SETID AND H.BUSINESS_UNIT = a.BUSINESS_UNIT AND a.vendor_setid = g.vendor_setid AND a.vendor_id = g.vendor_id AND a.vndr_loc = g.vndr_loc))) UNION SELECT AA.BUSINESS_UNIT , AA.VOUCHER_ID , AA.INVOICE_ID , AA.INVOICE_DT , AA.VENDOR_ID , AA.GROSS_AMT , AA.TXN_CURRENCY_CD FROM PS_VOUCHER AA WHERE AA.ENTRY_STATUS = 'P' AND AA.APPR_STATUS <> 'A' AND AA.APPR_CHECK_FLG = 'N' AND AA.VCHR_APPRVL_FLG = 'S' AND AA.MATCH_STATUS_VCHR IN ('M', 'N') AND ((AA.vchr_src IN ('AMLS', 'ARCR', 'BILL', 'CLBK', 'CNTR', 'CONS', 'EDI', 'FRE', 'MTCH', 'NERS', 'ONL', 'PAYR', 'PBIL', 'PERS', 'PPAY', 'PROC', 'QUCK', 'RBCR', 'RETL', 'RPOV', 'RTV', 'SAD', 'SPRO', 'TRPM', 'XLS','PRQ')) OR ((AA.vchr_src IN ('XML','DCM','SSI','EXL') AND EXISTS ( SELECT 'X' FROM PS_EM_BUY_REG_TBL DD WHERE DD.BUSINESS_UNIT = AA.BUSINESS_UNIT AND DD.EM_USE_AP_APPROVAL = 'Y')) OR (AA.VCHR_SRC IN ('XML','DCM') AND NOT EXISTS ( SELECT 'X' FROM PS_EM_BUY_REG_TBL HH , PS_EM_AGREEMENT gg , PS_SET_CNTRL_REC ii WHERE ii.setcntrlvalue = AA.business_unit AND ii.rec_group_id = 'EM_01' AND ii.recname = 'EM_AGREEMENT' AND ii.setid = gg.SETID AND HH.BUSINESS_UNIT = AA.BUSINESS_UNIT AND AA.vendor_setid = gg.vendor_setid AND AA.vendor_id = gg.vendor_id AND AA.vndr_loc = gg.vndr_loc))))
|
BUSINESS_UNIT