5833

SELECT H.VOUCHER_NO,
             H.VOUCHER_DATE,
             H.CUSTOMER_CODE,
             H.CURRENCY_CODE,
             H.CUSTOMER_NAME,
             H.PROJECT_NO,
            H.SUBJECT,
             H.DIRECTOR_CODE,
             H.TEAM_LEADER_CODE,
             H.DIRECTOR_NAME,
             H.TEAM_LEADER_NAME,
             H.SERVICE_CATEGORY_CODE,
             H.SERVICE_CATEGORY_DESC,
             H.SALES_TAX_CODE,
             CASE WHEN LINE_ITEM_COUNT = 0 THEN H.TOTAL_AFTER_TAX_AMT ELSE H.TOTAL_AFTER_TAX_AMT/ LINE_ITEM_COUNT END AS TOTAL_AFTER_TAX_AMT,
             CASE WHEN LINE_ITEM_COUNT = 0 THEN H.TOTAL_AFTER_TAX_HOME_AMT ELSE H.TOTAL_AFTER_TAX_HOME_AMT/ LINE_ITEM_COUNT END AS TOTAL_AFTER_TAX_HOME_AMT,
             CASE WHEN LINE_ITEM_COUNT = 0 THEN H.TOTAL_SALES_TAX_HOME_AMT ELSE H.TOTAL_SALES_TAX_HOME_AMT/ LINE_ITEM_COUNT END AS TOTAL_SALES_TAX_HOME_AMT,
             CASE WHEN LINE_ITEM_COUNT = 0 THEN H.TOTAL_PRE_TAX_HOME_AMT ELSE H.TOTAL_PRE_TAX_HOME_AMT/ LINE_ITEM_COUNT END AS TOTAL_PRE_TAX_HOME_AMT,
             H.PRE_TAX_EXTENDED_HOME_AMT ,
             H.PROJECT_FEE,
             H.VOUCHER_TYPE
FROM (SELECT H.INVOICE_NO VOUCHER_NO,
             H.INVOICE_DATE VOUCHER_DATE,
             H.BILLING_PARTY_CODE CUSTOMER_CODE,
             H.CURRENCY_CODE,
             MP.PARTY_NAME CUSTOMER_NAME,
             COALESCE(H.PROJECT_NO, PI.PROJECT_NO) PROJECT_NO,
             CASE
                WHEN COALESCE(PH.SUBJECT, '') = '' OR H.REMARKS IS NULL OR CAST(H.REMARKS AS VARCHAR(2000)) = '' THEN COALESCE(PH.SUBJECT, '') || COALESCE(CAST(H.REMARKS AS VARCHAR(2000)), '')
                ELSE COALESCE(PH.SUBJECT, '') || ' - ' || COALESCE(CAST(H.REMARKS AS VARCHAR(2000)), '')
                END AS SUBJECT,
             DTL.DIRECTOR_CODE,
             DTL.TEAM_LEADER_CODE,
             ME.EMPLOYEE_NAME DIRECTOR_NAME,
             ME2.EMPLOYEE_NAME TEAM_LEADER_NAME,
             COALESCE(MBC.GL_ACC_CODE,'~~~~~OTHERS') AS SERVICE_CATEGORY_CODE,
             COALESCE(MBC.GL_ACC_DESC,'Others') AS SERVICE_CATEGORY_DESC,
             COALESCE(PI.SALES_TAX_CODE, H.SALES_TAX_CODE ) SALES_TAX_CODE,
             COALESCE(PI.TOTAL_AFTER_TAX_AFT_RET, H.TOTAL_AFTER_TAX_AMT) TOTAL_AFTER_TAX_AMT,
             COALESCE(PI.TOTAL_AFTER_TAX_HOME_AFT_RET, H.TOTAL_AFTER_TAX_HOME_AMT) TOTAL_AFTER_TAX_HOME_AMT,
             COALESCE(PI.TOTAL_SALES_TAX_HOME_AFT_RET, H.TOTAL_SALES_TAX_HOME_AMT) TOTAL_SALES_TAX_HOME_AMT,
             COALESCE(PI.TOTAL_PRE_TAX_HOME_AFT_RET, H.TOTAL_PRE_TAX_HOME_AMT) TOTAL_PRE_TAX_HOME_AMT,
             D.PRE_TAX_EXTENDED_HOME_AMT,
             PI.TOTAL_PRE_TAX_HOME_AFT_RET PROJECT_FEE,
             1 VOUCHER_TYPE,
             COALESCE((SELECT COUNT (*) FROM AR_INV_HST_DET DET WHERE H.INVOICE_NO = DET.INVOICE_NO), 1) LINE_ITEM_COUNT
      FROM AR_INV_HST_HDR H
        LEFT JOIN AR_INV_HST_DET D ON H.INVOICE_NO = D.INVOICE_NO
        LEFT JOIN PJ_AR_INV_HST_HDR PI ON H.INVOICE_NO = PI.INVOICE_NO
        LEFT OUTER JOIN MT_SALES_TAX ST ON D.SALES_TAX_CODE = ST.SALES_TAX_CODE
        LEFT OUTER JOIN MT_PARTY MP ON H.BILLING_PARTY_CODE = MP.PARTY_CODE
        LEFT OUTER JOIN PJ_EST_OST_HDR PH ON COALESCE(H.PROJECT_NO, PI.PROJECT_NO) = PH.PROJECT_NO
        LEFT OUTER JOIN PJ_EST_OST_DIR_TEAM_LEAD DTL
                     ON (PH.PROJECT_NO = DTL.PROJECT_NO
                    AND H.INVOICE_DATE >= DTL.EFFECTIVE_DATE_FROM
                    AND (H.INVOICE_DATE <= DTL.EFFECTIVE_DATE_TO
                     OR DTL.EFFECTIVE_DATE_TO IS NULL))
        LEFT OUTER JOIN MT_EMPLOYEE ME ON ME.EMPLOYEE_CODE = DTL.DIRECTOR_CODE
        LEFT OUTER JOIN MT_EMPLOYEE ME2 ON ME2.EMPLOYEE_CODE = DTL.TEAM_LEADER_CODE
        LEFT OUTER JOIN MT_SERVICE MS ON D.SERVICE_CODE = MS.SERVICE_CODE
        LEFT OUTER JOIN MT_INVENTORY MI ON D.INVENTORY_CODE = MI.INVENTORY_CODE
        LEFT OUTER JOIN MT_CHART_OF_ACC MBC ON MBC.GL_ACC_CODE = MS.SALES_GLAN
      WHERE (H.YEAR_POSTED_TO*100 + H.PERIOD_POSTED_TO) BETWEEN ($P{FINANCIAL_YEAR_FROM}*100 + $P{FINANCIAL_PERIOD_FROM}) AND ($P{FINANCIAL_YEAR_TO}*100 + $P{FINANCIAL_PERIOD_TO})
      UNION ALL
      SELECT H.CREDIT_NOTE_NO VOUCHER_NO,
             H.CREDIT_NOTE_DATE VOUCHER_DATE,
             H.BILLING_PARTY_CODE CUSTOMER_CODE,
             H.CURRENCY_CODE,
             MP.PARTY_NAME CUSTOMER_NAME,
             COALESCE(PC.PROJECT_NO, H.PROJECT_NO) PROJECT_NO,
             CASE
                WHEN COALESCE(PH.SUBJECT, '') = '' OR H.REMARKS IS NULL OR CAST(H.REMARKS AS VARCHAR(2000)) = '' THEN COALESCE(PH.SUBJECT, '') || COALESCE(CAST(H.REMARKS AS VARCHAR(2000)), '')
                ELSE COALESCE(PH.SUBJECT, '') || ' - ' || COALESCE(CAST(H.REMARKS AS VARCHAR(2000)), '')
                END AS SUBJECT,
             DTL.DIRECTOR_CODE,
             DTL.TEAM_LEADER_CODE,
             ME.EMPLOYEE_NAME DIRECTOR_NAME,
             ME2.EMPLOYEE_NAME TEAM_LEADER_NAME,
             COALESCE(MBC.GL_ACC_CODE,'~~~~~OTHERS') AS SERVICE_CATEGORY_CODE,
             COALESCE(MBC.GL_ACC_DESC,'Others') AS SERVICE_CATEGORY_DESC,
             COALESCE(H.SALES_TAX_CODE, PC.SALES_TAX_CODE ) SALES_TAX_CODE,
             COALESCE(PC.TOTAL_AFTER_TAX_AFT_RET, H.TOTAL_AFTER_TAX_AMT) TOTAL_AFTER_TAX_AMT,
             COALESCE(PC.TOTAL_AFTER_TAX_HOME_AFT_RET, H.TOTAL_AFTER_TAX_HOME_AMT) TOTAL_AFTER_TAX_HOME_AMT,
             COALESCE(PC.TOTAL_SALES_TAX_HOME_AFT_RET, H.TOTAL_SALES_TAX_HOME_AMT) TOTAL_SALES_TAX_HOME_AMT,
             COALESCE(PC.TOTAL_PRE_TAX_HOME_AFT_RET, H.TOTAL_PRE_TAX_HOME_AMT) TOTAL_PRE_TAX_HOME_AMT,
             D.PRE_TAX_EXTENDED_HOME_AMT,
             PC.TOTAL_PRE_TAX_HOME_AFT_RET PROJECT_FEE,
             2 VOUCHER_TYPE,
             COALESCE((SELECT COUNT (*) FROM AR_CRN_HST_DET DET WHERE H.CREDIT_NOTE_NO = DET.CREDIT_NOTE_NO),1) LINE_ITEM_COUNT
      FROM AR_CRN_HST_HDR H
        LEFT JOIN AR_CRN_HST_DET D ON H.CREDIT_NOTE_NO = D.CREDIT_NOTE_NO
        LEFT JOIN PJ_AR_CRN_HST_HDR PC ON H.CREDIT_NOTE_NO = PC.CREDIT_NOTE_NO
        LEFT OUTER JOIN MT_SALES_TAX ST ON D.SALES_TAX_CODE = ST.SALES_TAX_CODE
        LEFT OUTER JOIN MT_PARTY MP ON H.BILLING_PARTY_CODE = MP.PARTY_CODE
        LEFT OUTER JOIN PJ_EST_OST_HDR PH ON COALESCE(H.PROJECT_NO, PC.PROJECT_NO) = PH.PROJECT_NO
        LEFT OUTER JOIN PJ_EST_OST_DIR_TEAM_LEAD DTL
                     ON (PH.PROJECT_NO = DTL.PROJECT_NO
                    AND H.CREDIT_NOTE_DATE >= DTL.EFFECTIVE_DATE_FROM
                    AND (H.CREDIT_NOTE_DATE <= DTL.EFFECTIVE_DATE_TO
                     OR DTL.EFFECTIVE_DATE_TO IS NULL))
        LEFT OUTER JOIN MT_EMPLOYEE ME ON ME.EMPLOYEE_CODE = DTL.DIRECTOR_CODE
        LEFT OUTER JOIN MT_EMPLOYEE ME2 ON ME2.EMPLOYEE_CODE = DTL.TEAM_LEADER_CODE
        LEFT OUTER JOIN MT_SERVICE MS ON D.SERVICE_CODE = MS.SERVICE_CODE
        LEFT OUTER JOIN MT_INVENTORY MI ON D.INVENTORY_CODE = MI.INVENTORY_CODE
        LEFT OUTER JOIN MT_CHART_OF_ACC MBC ON MBC.GL_ACC_CODE = MS.SALES_GLAN
      WHERE (H.YEAR_POSTED_TO*100 + H.PERIOD_POSTED_TO) BETWEEN ($P{FINANCIAL_YEAR_FROM}*100 + $P{FINANCIAL_PERIOD_FROM}) AND ($P{FINANCIAL_YEAR_TO}*100 + $P{FINANCIAL_PERIOD_TO})) H
WHERE 1 = 1 $P!{__RPT__FILTER__PARAM}