Objective:
Post doing the conversion in Fusion HCM, it is important to reconcile the match between the conversion records and loaded records. In order to match the records, you have to build a BIP report to get the details from the system.
Note: Source System Owner may differ with your conversion. Please align the correct source system owner in your query.
Query :
SELECT
TO_CHAR(loc.EFFECTIVE_START_DATE,’YYYY/MM/DD’) EFFECTIVE_START_DATE, TO_CHAR(loc.EFFECTIVE_END_DATE,’YYYY/MM/DD’) EFFECTIVE_END_DATE,
–asm.determinant_value business_unit,
lot.location_code,
lot.location_name,
lot.description,
lot.ac_location_code,
–addr.style,
addr.address_line_1,
addr.address_line_2,
addr.address_line_3,
addr.address_line_4,
addr.building,
addr.floor_number,
addr.region_1,
addr.region_2,
addr.region_3,
addr.town_or_city,
addr.derived_locale,
(select TERRITORY_SHORT_NAME from fnd_territories_vl
where TERRITORY_CODE =addr.country)country,
addr.postal_code,
addr.long_postal_code,
loc.”SHIP_TO_SITE_FLAG”,loc.”SHIP_TO_LOCATION_ID”,loc.”RECEIVING_SITE_FLAG”,loc.”BILL_TO_SITE_FLAG”,
loc.”OFFICE_SITE_FLAG”
FROM per_location_details_f loc ,
per_location_details_f_tl lot,
per_addresses_f addr,
hrc_integration_key_map hrc
WHERE 1=1
AND loc.location_details_id = lot.location_details_id
AND loc.effective_start_date = lot.effective_start_date
AND loc.effective_end_date = lot.effective_end_date
AND lot.language = userenv(‘LANG’)
and loc.main_address_id= addr.address_id
AND loc.effective_start_date = addr.effective_start_date
AND loc.effective_end_date = addr.effective_end_date
AND hrc.object_name =’Location’
AND hrc.source_system_owner =’VISION‘
AND hrc.surrogate_id=loc.location_id
AND trunc(sysdate) BETWEEN loc.effective_start_date and loc.effective_end_date
