TH5R_PJ_300129

SELECT
PJ.PROJECT_NO
,MP.PARTY_NAME CUSTOMER_NAME
,QTN.WORK_ORDER_NO WO_NO
,QTN.SUBJECT
,COALESCE(QTN.TOTAL_PRE_TAX_HOME_AMT,0) QTN_AMT
,ME1.EMPLOYEE_NAME SITE_SUPERVISOR_NAME
,ME2.PARTY_CONTACT_NAME CONTACT_PERSON_NAME
,SL1.SITE_LOC_DESC
,QTN.QUOTATION_DATE
,QTN.VALIDITY_DUE_DATE
,CASE WHEN QTN.VALIDITY_DUE_DATE>= CURRENT_DATE THEN 'and not expired' ELSE 'and expired' END AS STATUS_2
,H.DRAFT_BILL_NO
,H.DRAFT_BILL_DATE
,H.INTERNAL_REMARKS
,QPHS.PHASE_DESC
,COALESCE(QPHS.PRE_TAX_EXTENDED_AMT,0) QPHS_AMT
,D.WORK_ORDER_NO GRN_NO
,INV.INVOICE_DATE
,COALESCE(D.TOTAL_HOME_AMT,0) GRN_AMT
,COALESCE(D.IS_CANCELLED,'N') IS_CANCELLED
FROM PJ_DRAFT_BILL_OST_HDR H
LEFT JOIN PJ_DRAFT_BILL_OST_DET D ON D.DRAFT_BILL_NO = H.DRAFT_BILL_NO
LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
LEFT JOIN MT_PARTY MP ON MP.PARTY_CODE = PJ.CUSTOMER_CODE
LEFT JOIN (
SELECT QUOTATION_NO, MAX(REVISION_NO) REVISION_NO
FROM PJ_QTN_HST_HDR
GROUP BY QUOTATION_NO
) QTN_MAX_REV ON QTN_MAX_REV.QUOTATION_NO = H.QUOTATION_NO
LEFT JOIN PJ_QTN_HST_HDR QTN ON QTN.QUOTATION_NO = H.QUOTATION_NO AND QTN.REVISION_NO = QTN_MAX_REV.REVISION_NO
LEFT JOIN PJ_QTN_HST_PHS QPHS ON QPHS.QUOTATION_NO = QTN.QUOTATION_NO AND QPHS.REVISION_NO = QTN.REVISION_NO AND QPHS.PHASE_NO = D.PHASE_NO
LEFT JOIN MT_EMPLOYEE ME1 ON ME1.EMPLOYEE_CODE = QTN.SITE_SUPERVISOR
LEFT JOIN PJ_AR_INV_HST_HDR INV ON INV.INVOICE_NO = D.INVOICE_NO
LEFT JOIN MT_SITE_LOC SL1 ON SL1.SITE_LOC_CODE = H.SITE_LOC_CODE
LEFT JOIN MT_PARTY_CONTACT ME2 ON ME2.party_code = QTN.customer_code AND me2.PARTY_CONTACT_CODE = qtn.QUOTATION_CONTACT_CODE
WHERE 1=1 $P!{__RPT__FILTER__PARAM}
AND QTN.WORK_ORDER_NO IS NOT NULL
AND (H.STATUS IS NULL OR H.STATUS <> 'X')
AND (($P{PROJECT_NO_PASSED}=3 AND (PJ.PROJECT_NO BETWEEN $P{PROJECT_NO_FROM} AND $P{PROJECT_NO_TO}))
OR ($P{PROJECT_NO_PASSED}=2 AND (PJ.PROJECT_NO <= $P{PROJECT_NO_TO}))
OR ($P{PROJECT_NO_PASSED}=1 AND (PJ.PROJECT_NO >= $P{PROJECT_NO_FROM}))
OR ($P{PROJECT_NO_PASSED}=0))
AND (($P{PROJECT_STATUS} = 'H' AND PJ.STATUS = 'H')
        OR ($P{PROJECT_STATUS} = 'O' AND PJ.STATUS = 'O')
        OR ($P{PROJECT_STATUS} <> 'O' AND $P{PROJECT_STATUS} <> 'H'))
ORDER BY PJ.PROJECT_NO, QTN.WORK_ORDER_NO,H.QUOTATION_NO, H.DRAFT_BILL_NO,QPHS.PHASE_NO