Section 3- done
-- Materialize conversion rates once
CONVERSION_RATES_USD AS (
SELECT
CR.FROM_CURRENCY,
CR.TO_CURRENCY,
CR.CONVERSION_START_DATE,
CR.CONVERSION_END_DATE,
CR.CONVERSION_RATE
FROM CN_CONVERSION_RATES CR
INNER JOIN CN_CONVERSION_TYPES_TL CT
ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID
WHERE CT.CONVERSION_TYPE_ID = '-1001'
AND CT.LANGUAGE = 'US'
AND CR.TO_CURRENCY = 'USD'
),
-- Materialize person numbers once - FILTERED
PERSON_NUMBERS AS (
SELECT DISTINCT
cspl.participant_id,
papf.person_number,
cspl.PARTICIPANT_NAME
FROM CN_SRP_PARTICIPANTS_ALL cspl
INNER JOIN hz_person_profiles hpp
ON cspl.party_id = hpp.party_id
INNER JOIN PER_ALL_PEOPLE_F papf
ON papf.person_id = hpp.hr_person_id
WHERE cspl.participant_id = NVL(:participant_id, cspl.participant_id)
),
-- Materialize participant details once - FILTERED
PARTICIPANT_DETAILS_CACHE AS (
SELECT
a.analyst_id,
b.PARTICIPANT_ID,
b.PARTICIPANT_DETAIL_ID,
b.ORG_ID,
b.START_DATE,
b.END_DATE,
b.COUNTRY_CODE,
b.ATTRIBUTE_CHAR40,
b.ATTRIBUTE_NUMBER1,
b.CURRENCY_CODE,
b.ATTRIBUTE_CHAR2,
b.ATTRIBUTE_CHAR17
FROM CN_SRP_PARTICIPANTS_ALL a
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL b
ON a.participant_id = b.participant_id
WHERE b.PARTICIPANT_ID = NVL(:participant_id, b.PARTICIPANT_ID)
),
Comments
Post a Comment