|
SELECT LN.CONTRACT_NUM , LN.BUSINESS_UNIT_GL , LN.JOURNAL_ID , LN.JOURNAL_DATE , SUM(LN.AMOUNT)*(-1) , LN.CURRENCY_CD , LN.BUSINESS_UNIT_PC , LN.PROJECT_ID , ( SELECT PRJ.DESCR FROM PS_PROJECT PRJ WHERE PRJ.BUSINESS_UNIT = LN.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID = LN.PROJECT_ID), SUM(LN.AMOUNT_BSE)*(-1), LN.BASE_CURRENCY FROM PS_CA_ACCTG_LINE LN, PS_JRNL_HEADER HDR WHERE HDR.BUSINESS_UNIT = LN.BUSINESS_UNIT_GL AND HDR.JOURNAL_ID = LN.JOURNAL_ID AND HDR.JOURNAL_DATE = LN.JOURNAL_DATE AND HDR.UNPOST_SEQ = ( SELECT MAX(HDR1.UNPOST_SEQ) FROM PS_JRNL_HEADER HDR1 WHERE HDR.BUSINESS_UNIT = HDR1.BUSINESS_UNIT AND HDR.JOURNAL_ID = HDR1.JOURNAL_ID AND HDR.JOURNAL_DATE = HDR1.JOURNAL_DATE) AND LN.DST_USE = 'REV' GROUP BY LN.CONTRACT_NUM, LN.BUSINESS_UNIT_GL, LN.JOURNAL_ID, LN.JOURNAL_DATE, LN.CURRENCY_CD, LN.BUSINESS_UNIT_PC, LN.PROJECT_ID, LN.BASE_CURRENCY UNION SELECT LINE.CONTRACT_NUM , LINE.BUSINESS_UNIT_GL , LINE.JOURNAL_ID , LINE.JOURNAL_DATE , SUM(LINE.AMOUNT)*(-1) , LINE.CURRENCY_CD , LINE.BUSINESS_UNIT_PC , LINE.PROJECT_ID , ( SELECT PROJ.DESCR FROM PS_PROJECT PROJ WHERE PROJ.BUSINESS_UNIT = LINE.BUSINESS_UNIT_PC AND PROJ.PROJECT_ID = LINE.PROJECT_ID), %DecMult(%DecDiv(SUM(LINE.AMOUNT_BSE)*(-1), RT.RATE_DIV), RT.RATE_MULT), CA.CURRENCY_CD FROM PS_CA_CONTR_HDR CA, PS_CA_ACCTG_LN_PC LINE, PS_JRNL_HEADER HDR2, PS_RT_DFLT_VW RT WHERE CA.CONTRACT_NUM = LINE.CONTRACT_NUM AND HDR2.BUSINESS_UNIT = LINE.BUSINESS_UNIT_GL AND HDR2.JOURNAL_ID = LINE.JOURNAL_ID AND HDR2.JOURNAL_DATE = LINE.JOURNAL_DATE AND HDR2.UNPOST_SEQ = ( SELECT MAX(HDR3.UNPOST_SEQ) FROM PS_JRNL_HEADER HDR3 WHERE HDR2.BUSINESS_UNIT = HDR3.BUSINESS_UNIT AND HDR2.JOURNAL_ID = HDR3.JOURNAL_ID AND HDR2.JOURNAL_DATE = HDR3.JOURNAL_DATE) AND LINE.DST_USE = 'REV' AND RT.RT_TYPE = CA.RT_TYPE AND LINE.CURRENCY_CD = RT.FROM_CUR AND CA.CURRENCY_CD = RT.TO_CUR AND RT.EFFDT = ( SELECT MAX(RT_ED.EFFDT) FROM PS_RT_DFLT_VW RT_ED WHERE RT.FROM_CUR = RT_ED.FROM_CUR AND RT.TO_CUR = RT_ED.TO_CUR AND RT.RT_TYPE = RT_ED.RT_TYPE AND RT_ED.EFFDT <= %CurrentDateIn) GROUP BY LINE.CONTRACT_NUM, LINE.BUSINESS_UNIT_GL, LINE.JOURNAL_ID, LINE.JOURNAL_DATE, LINE.CURRENCY_CD, LINE.BUSINESS_UNIT_PC, LINE.PROJECT_ID, RT.RATE_DIV, RT.RATE_MULT, CA.CURRENCY_CD UNION SELECT HDR.CONTRACT_NUM , HDR.BUSINESS_UNIT , HDR.INVOICE , HDR.INVOICE_DT , SUM(HDR.INVOICE_AMOUNT) , HDR.BI_CURRENCY_CD , LN.BUSINESS_UNIT_PC , LN.PROJECT_ID , ( SELECT PROJ.DESCR FROM PS_PROJECT PROJ WHERE PROJ.BUSINESS_UNIT = LN.BUSINESS_UNIT_PC AND PROJ.PROJECT_ID = LN.PROJECT_ID), %DecMult(%DecDiv(SUM(HDR.INVOICE_AMOUNT), RT.RATE_DIV), RT.RATE_MULT), CA.CURRENCY_CD FROM PS_BI_HDR HDR, PS_BI_LINE LN, PS_CA_CONTR_HDR CA, PS_CA_DETAIL DTL, PS_RT_DFLT_VW RT WHERE HDR.BUSINESS_UNIT = LN.BUSINESS_UNIT AND HDR.INVOICE = LN.INVOICE AND HDR.CONTRACT_NUM = LN.CONTRACT_NUM AND HDR.CONTRACT_NUM = CA.CONTRACT_NUM AND CA.CONTRACT_NUM = DTL.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = LN.CONTRACT_LINE_NUM AND RT.RT_TYPE = CA.RT_TYPE AND HDR.BI_CURRENCY_CD = RT.FROM_CUR AND CA.CURRENCY_CD = RT.TO_CUR AND RT.EFFDT = ( SELECT MAX(RT_ED.EFFDT) FROM PS_RT_DFLT_VW RT_ED WHERE RT.FROM_CUR = RT_ED.FROM_CUR AND RT.TO_CUR = RT_ED.TO_CUR AND RT.RT_TYPE = RT_ED.RT_TYPE AND RT_ED.EFFDT <= %CurrentDateIn) AND HDR.BILL_STATUS NOT IN ('TMP', 'TMR') AND DTL.REV_RECOG_METHOD = '5' GROUP BY HDR.CONTRACT_NUM, HDR.BUSINESS_UNIT, HDR.INVOICE, HDR.INVOICE_DT, HDR.BI_CURRENCY_CD, LN.BUSINESS_UNIT_PC, LN.PROJECT_ID, RT.RATE_DIV, RT.RATE_MULT, CA.CURRENCY_CD
|