POS-BU with Status.sql

SELECT DISTINCT 'MERGE' "METADATA"
	, 'Position' "Position"
	, HAPF.POSITION_CODE "PositionCode"
	, HOUFT.NAME "BusinessUnitName"
	, TO_CHAR(HAPF.EFFECTIVE_START_DATE, 'YYYY/MM/DD') "EffectiveStartDate"
	, TO_CHAR(HAPF.EFFECTIVE_END_DATE, 'YYYY/MM/DD') "EffectiveEndDate"
	, HIKM.SOURCE_SYSTEM_OWNER "SourceSystemOwner"
	, HIKM.SOURCE_SYSTEM_ID "SourceSystemId"
	, HAPF.BARGAINING_UNIT_CD "BargainingUnitCd"
	, nvl(CASE WHEN HAPF.BARGAINING_UNIT_CD IS NOT NULL
		THEN (CASE WHEN HAPF.BARGAINING_UNIT_CD NOT IN (SELECT DISTINCT FLVB.LOOKUP_CODE
								FROM FND_LOOKUP_VALUES_B FLVB
								WHERE 1 = 1
									AND FLVB.LOOKUP_TYPE = 'BARGAINING_UNIT_CODE') THEN 'INVALID' ELSE 'VALID' END)
		ELSE ''
		END, 'NULL') "BargainingUnitStatus"
FROM HR_ALL_POSITIONS_F HAPF
	, HR_ORGANIZATION_UNITS_F_TL HOUFT
	, HRC_INTEGRATION_KEY_MAP HIKM
WHERE 1 = 1
	AND HAPF.BUSINESS_UNIT_ID = HOUFT.ORGANIZATION_ID
	AND HAPF.POSITION_ID = HIKM.SURROGATE_ID
	AND HOUFT.LANGUAGE = HOUFT.SOURCE_LANG
	AND HIKM.OBJECT_NAME = 'Position'
ORDER BY HAPF.POSITION_CODE, TO_CHAR(HAPF.EFFECTIVE_START_DATE, 'YYYY/MM/DD')