3.1 worker

SELECT AD.INVOICE_NO, AD.PK_NO_DET
,PY.VOUCHER_DATE VOUCHER_DATE
,PY.DEDUCTION_VOUCHER_NO DEDUCTION_VOUCHER_NO
,DS.EMPLOYEE_CODE AS EMPLOYEE_CODE
, ME.EMPLOYEE_NAME AS EMPLOYEE_NAME
,COALESCE(AD.ALLOC_HOME_AMT,0) ALLOC_HOME_AMT
FROM PY_DEDUCTION_SERVICE DS
JOIN PJ_MAINCONAPIN_ALLOC_DET AD ON DS.MAINCONAPIN_INVOICE_NO = AD.INVOICE_NO AND DS.MAINCONAPIN_PK_NO_ALLOC = AD.PK_NO_ALLOC AND DS.MAINCONAPIN_PK_NO_ALLOC_DET = AD.PK_NO_ALLOC_DET
JOIN PJ_MAINCONAPIN_ALLOC A ON A.INVOICE_NO = AD.INVOICE_NO AND A.PK_NO_ALLOC = AD.PK_NO_ALLOC
JOIN PY_DEDUCTION PY ON PY.DEDUCTION_VOUCHER_NO = DS.DEDUCTION_VOUCHER_NO
LEFT JOIN MT_EMPLOYEE ME ON ME.EMPLOYEE_CODE = DS.EMPLOYEE_CODE
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE A.STATUS = 'H'
AND AD.ALLOC_TYPE = 'E'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
AND AD.INVOICE_NO = $P{INVOICE_NO} AND AD.PK_NO_DET = $P{PK_NO_DET}
AND $P{FROM_WHERE} = 'MAINCONAPIN'

UNION ALL

SELECT AD.CREDIT_NOTE_NO INVOICE_NO, AD.PK_NO_DET
,PY.VOUCHER_DATE VOUCHER_DATE
,PY.ADDITIONAL_PAYMENT_VCH_NO DEDUCTION_VOUCHER_NO
,DS.EMPLOYEE_CODE AS EMPLOYEE_CODE
, ME.EMPLOYEE_NAME AS EMPLOYEE_NAME
,COALESCE(AD.ALLOC_HOME_AMT,0) ALLOC_HOME_AMT
FROM PY_ADD_PMT_EMPLOYEE_SERVICE DS
JOIN PJ_MAINCONAPcn_ALLOC_DET AD ON DS.MAINCONAPCN_CREDIT_NOTE_NO = AD.CREDIT_NOTE_NO AND DS.MAINCONAPCN_PK_NO_ALLOC = AD.PK_NO_ALLOC AND DS.MAINCONAPCN_PK_NO_ALLOC_DET = AD.PK_NO_ALLOC_DET
JOIN PJ_MAINCONAPcn_ALLOC A ON A.CREDIT_NOTE_NO = AD.CREDIT_NOTE_NO AND A.PK_NO_ALLOC = AD.PK_NO_ALLOC
JOIN PY_ADDITIONAL_PAYMENT PY ON PY.ADDITIONAL_PAYMENT_VCH_NO = DS.ADDITIONAL_PAYMENT_VCH_NO
LEFT JOIN MT_EMPLOYEE ME ON ME.EMPLOYEE_CODE = DS.EMPLOYEE_CODE
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE A.STATUS = 'H'
AND AD.ALLOC_TYPE = 'E'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
AND AD.CREDIT_NOTE_NO = $P{INVOICE_NO} AND AD.PK_NO_DET = $P{PK_NO_DET}
AND $P{FROM_WHERE} = 'MAINCONAPCN'