|
SELECT C.BUSINESS_UNIT , B.ASSOC_EXP_BD , C.ACCOUNT , C.DEPTID , %subrec(CF9A_AK_SBR, C) , %subrec(CFCC2_AK_SBR, C) , C.BUDGET_PERIOD , D.LEDGER_GROUP , D.SEQ_NBR , D.CAP , D.PERCENTAGE , - SUM(A.POSTED_TOTAL_AMT) , A.LEDGER , E.LEDGER_TYPE_KK FROM PS_LEDGER_KK A , PS_KK_BUDGET_TYPE B , PS_KK_EX_XREF C , PS_KK_REV_XREF D , PS_LED_GRP_LED_TBL E , PS_SET_CNTRL_REC F , PS_SET_CNTRL_REC G , PS_BUS_UNIT_TBL_GL H WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.CURRENCY_CD = H.BASE_CURRENCY AND A.LEDGER = E.LEDGER AND E.SETID = F.SETID AND F.SETCNTRLVALUE = A.BUSINESS_UNIT AND F.RECNAME = 'LED_GRP_LED_TBL' AND E.LEDGER_GROUP = D.LEDGER_GROUP AND E.LEDGER_GROUP = B.LEDGER_GROUP AND E.LEDGER_TYPE_KK = D.REV_METHOD AND B.SETID1 = G.SETID AND A.BUDGET_PERIOD = D.BUDGET_PERIOD AND G.SETCNTRLVALUE = A.BUSINESS_UNIT AND G.RECNAME = 'KK_BD_TYPE_VW' AND B.EFFDT = ( SELECT MAX(H.EFFDT) FROM PS_KK_BUDGET_TYPE H WHERE H.SETID1 = B.SETID1 AND H.LEDGER_GROUP = B.LEDGER_GROUP AND H.EFFDT <= %CurrentDateIn AND H.EFF_STATUS = 'A') AND C.SEQ_NBR = D.SEQ_NBR AND B.ASSOC_EXP_BD = C.LEDGER_GROUP AND B.LEDGER_GROUP = D.LEDGER_GROUP AND (A.ACCOUNT = D.ACCOUNT OR A.ACCOUNT = ( SELECT I.ACCOUNT FROM PS_KK_BD_DFLT_ACCT I , PS_SET_CNTRL_REC J WHERE I.SETID1 = B.SETID1 AND I.LEDGER_GROUP = B.LEDGER_GROUP AND I.EFFDT = B.EFFDT AND I.SETID = J.SETID AND J.SETCNTRLVALUE = A.BUSINESS_UNIT AND J.RECNAME = 'KK_BD_TYPE_VW')) AND A.DEPTID = D.DEPTID AND %Join(COMMON_FIELDS, CF9A_AK_SBR A, CF9A_AK_SBR D) AND %Join(COMMON_FIELDS, CFCC2_AK_SBR A, CFCC2_AK_SBR D) GROUP BY C.BUSINESS_UNIT, B.ASSOC_EXP_BD, C.ACCOUNT, C.DEPTID, %subrec(CF9A_AK_SBR, C),%subrec(CFCC2_AK_SBR, C), C.BUDGET_PERIOD, D.LEDGER_GROUP, A.LEDGER, E.LEDGER_TYPE_KK, D.SEQ_NBR, D.CAP, D.PERCENTAGE
|
BUSINESS_UNIT