LOC-BU with Status.sql

-- specified flexfields
SELECT DISTINCT 'MERGE' "METADATA"
	, 'Location' "Location"
	, TO_CHAR(PL.INTERNAL_LOCATION_CODE) "LocationCode"
	, FSS.SET_CODE "SetCode"
	, TO_CHAR(PLDF.EFFECTIVE_START_DATE, 'YYYY/MM/DD') "EffectiveStartDate"
	, TO_CHAR(PLDF.EFFECTIVE_END_DATE, 'YYYY/MM/DD') "EffectiveEndDate"
	, 'Global Data Elements' "FLEX:PER_LOCATIONS_DF"
	, PLDF.ATTRIBUTE2 "dbPerBetriesbrat(PER_LOCATIONS_DF=Global Data Elements)"
	, PLDF.ATTRIBUTE2 "dbPerBetriesbrat_Display(PER_LOCATIONS_DF=Global Data Elements)"
	, PLDF.ATTRIBUTE3 "dbPerBetriebsratName(PER_LOCATIONS_DF=Global Data Elements)"
	, HIKM.SOURCE_SYSTEM_OWNER "SourceSystemOwner"
	, HIKM.SOURCE_SYSTEM_ID "SourceSystemId"
	, nvl(CASE WHEN PLDF.ATTRIBUTE2 IS NOT NULL
		THEN (CASE WHEN PLDF.ATTRIBUTE2 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 PER_LOCATIONS PL
	, PER_LOCATION_DETAILS_F PLDF
	, FND_SETID_SETS FSS
	, HRC_INTEGRATION_KEY_MAP HIKM
WHERE 1 = 1
	AND PL.LOCATION_ID = PLDF.LOCATION_ID
	AND PL.SET_ID = FSS.SET_ID
	AND FSS.LANGUAGE = 'D'
	AND PL.LOCATION_ID = HIKM.SURROGATE_ID
	AND HIKM.OBJECT_NAME = 'Location'
ORDER BY TO_CHAR(PL.INTERNAL_LOCATION_CODE), TO_CHAR(PLDF.EFFECTIVE_START_DATE, 'YYYY/MM/DD'), TO_CHAR(PLDF.EFFECTIVE_END_DATE, 'YYYY/MM/DD')