5236_p

SELECT PJHDR.PROJECT_NO,
             COALESCE(PJHDR.SUBJECT,'') SUBJECT,
             PJHDR.CUSTOMER_CODE,
             COALESCE(P.PARTY_NAME,'') CUSTOMER_NAME,
             TH.TRANSACTION_DATE,
             TD.ORIGIN_FROM_MODULE,
             TD.ORIGIN_FROM_TRANSACTION_TYPE,
             TD.ORIGIN_FROM_VCH_NO,
             TH.MODULE_CODE CUR_MODULE_CODE,
             TH.TRANSACTION_TYPE_CODE CUR_TRANSACTION_TYPE_CODE,
             TH.VOUCHER_NO CUR_VCH_NO,
             SUM(TD.ACTUAL_COST_IN_HOME_CCY) AMT,
             ALLOC_VCH.PARTY_NAME,
             'ACUAL_COST' AS TP,
             TD.SCH_SEQ_NO
      FROM PJ_EST_OST_HDR PJHDR
        LEFT JOIN MT_PARTY P ON PJHDR.CUSTOMER_CODE = P.PARTY_CODE
        JOIN PJ_COMMITTED_COST_TRACK_TRN_HDR TH ON PJHDR.PROJECT_NO = TH.PROJECT_NO
        JOIN PJ_COMMITTED_COST_TRACK_TRN_DET TD  ON TD.PROJECT_NO = TH.PROJECT_NO   AND TD.TRN_SEQ_NO = TH.TRN_SEQ_NO AND TD.NO_OF_ALLOC = TH.NO_OF_ALLOC

        LEFT JOIN PJ_SUB_CON_HDR SUBHDR ON TH.VOUCHER_NO = SUBHDR.SUB_CON_CONTRACT_NO AND SUBHDR.STATUS IN ('T','H')
        LEFT JOIN TRANSACTION_ALLOCATED_TO_PROJECT_GENERAL_INFO ALLOC_VCH ON TH.VOUCHER_NO = ALLOC_VCH.SOURCE_VOUCHER_NO AND TH.TRN_SEQ_NO = ALLOC_VCH.TRN_SEQ_NO
      WHERE ((3 = 3 AND (PJHDR.PROJECT_NO BETWEEN '2020001' AND '2020001'))
        OR (3 = 2 AND (PJHDR.PROJECT_NO <= '2020001'))
        OR (3 = 1 AND (PJHDR.PROJECT_NO >= '2020001')) OR (3 = 0))
      AND   (TH.FINANCIAL_YEAR*100 + TH.FINANCIAL_PERIOD) <= (2020*100 + 12)
      AND SUBHDR.SUB_CON_CONTRACT_NO IS NULL
      GROUP BY PJHDR.PROJECT_NO,
              PJHDR.SUBJECT,
              PJHDR.CUSTOMER_CODE,
              P.PARTY_NAME,
              TH.TRANSACTION_DATE,
              TD.ORIGIN_FROM_MODULE,
              TD.ORIGIN_FROM_TRANSACTION_TYPE,
              TD.ORIGIN_FROM_VCH_NO,
              TH.MODULE_CODE,
              TH.TRANSACTION_TYPE_CODE,
              TH.VOUCHER_NO,
              ALLOC_VCH.PARTY_NAME,
             TD.SCH_SEQ_NO