SELECT		JH_DT AS JOB_OPEN_DT,
		JH_ACTUAL_END_DT AS JOB_CLOSED_DT,
		JH_JOB_NO AS JOB_NO, 
		JH_CUST_NAME AS CUSTOMER,
		JH_DESC_1 AS JOB_DESC,
		JH_SM_CODE AS CR,
		JH_FLEX_01 AS TR,
		JH_FLEX_02 AS VESSEL,
		JH_FLEX_08 AS CC,
		JH_FLEX_17 AS DEPT,
		JIH_NO AS INVOICE_NO,
		JIH_CR_DT AS INVOICE_DT,
		SUM(JII.JII_SALE_LC_VAL) AS INVOICE_VAL,
		SUM(JED_ACT_MAT_VAL) AS MATERIAL,
		SUM(JED_ACT_LAB_VAL) AS LABOR
FROM		(
			SELECT		JH.JH_DT JOB_OPEN_DT,
					JH.JH_ACTUAL_END_DT JOB_CLOSED_DT,
					JH.JH_JOB_NO JOB_NO, 
					JH.JH_CUST_NAME CUSTOMER,
					JH.JH_DESC_1 JOB_DESC,
					JH.JH_SM_CODE CR,
					JH.JH_FLEX_01 TR,
					JH.JH_FLEX_02 VESSEL,
					JH.JH_FLEX_08 CC,
					JH.JH_FLEX_17 DEPT,
					JIH.JIH_NO INVOICE_NO,
					JIH.JIH_CR_DT INVOICE_DT,
					SUM(JII.JII_SALE_LC_VAL) AS INVOICE_VAL,
					NULL AS MATERIAL,
					NULL AS LABOR
			FROM		OT_JOB_HEAD AS JH
			INNER JOIN	OT_JOB_INVOICE_DETAIL AS JII ON JII.JII_CHARGE_CODE = JH.JH_JOB_NO
			INNER JOIN	OT_JOB_INVOICE_HEAD AS JIH ON JIH.JIH_SYS_ID = JII.JII_JIH_SYS_ID
			WHERE		JH.JH_CUST_NAME <> 'GOLTENS DUBAI INTERNAL JOBS'
					AND JH.JH_ACTUAL_END_DT IS NOT NULL
					AND EXISTS	(
								select	1
								from	FT_CUR_TRANS_DETAIL
								where	(td_tran_code = 'JIN' OR td_doc_ref like '%JIN%')
									AND td_anly_code_1 = JH.JH_JOB_NO

								union all

								select	1
								from	FT_prv_TRANS_DETAIL
								where	(td_tran_code='JIN' OR td_doc_ref like '%JIN%')
									AND td_anly_code_1 = JH.JH_JOB_NO


								union all

								select	1
								from	FT_UNPOSTED_TRANS_DETAIL
								where	td_tran_code = 'JIN'
									AND td_anly_code_1 = JH_JOB_NO
							)

			UNION all

			SELECT		JH.JH_DT JOB_OPEN_DT,
					JH.JH_ACTUAL_END_DT JOB_CLOSED_DT,
					JH.JH_JOB_NO JOB_NO, 
					JH.JH_CUST_NAME CUSTOMER,
					JH.JH_DESC_1 JOB_DESC,
					JH.JH_SM_CODE CR,
					JH.JH_FLEX_01 TR,
					JH.JH_FLEX_02 VESSEL,
					JH.JH_FLEX_08 CC,
					JH.JH_FLEX_17 DEPT,
					JIH.JIH_NO INVOICE_NO,
					JIH.JIH_CR_DT INVOICE_DT,
					NULL AS INVOICE_VAL,
					JED_ACT_MAT_VAL AS MATERIAL,
					JED_ACT_LAB_VAL AS LABOR
			FROM		OT_JOB_HEAD AS JH
			INNER JOIN	OT_JOB_INVOICE_HEAD AS JIH ON JIH.JIH_CHARGE_CODE = JH.JH_JOB_NO
			INNER JOIN	OS_JOB_EST_DETAIL_GLT AS JED ON JED.JED_JOB_NO = JH.JH_JOB_NO
			WHERE		JH.JH_CUST_NAME <> 'GOLTENS DUBAI INTERNAL JOBS'
					AND JH.JH_ACTUAL_END_DT IS NOT NULL
					AND EXISTS	(
								select	1
								from	FT_CUR_TRANS_DETAIL
								where	(td_tran_code = 'JIN' OR td_doc_ref like '%JIN%')
									AND td_anly_code_1 = JH.JH_JOB_NO

								union all

								select	1
								from	FT_prv_TRANS_DETAIL
								where	(td_tran_code = 'JIN' OR td_doc_ref like '%JIN%')
									AND td_anly_code_1 = JH.JH_JOB_NO

								union all

								select	1
								from	FT_UNPOSTED_TRANS_DETAIL
								where	td_tran_code = 'JIN'
									AND td_anly_code_1 = JH_JOB_NO
							)
		) AS d
group by	JH_DT,
		JH_ACTUAL_END_DT,
		JH_JOB_NO,
		JH_CUST_NAME,
		JH_DESC_1,
		JH_SM_CODE,
		JH_FLEX_01,
		JH_FLEX_02,
		JH_FLEX_08,
		JH_FLEX_17,
		JIH_NO,
		JIH_CR_DT