child query capped to 4 decimal point
SELECT
-- Parent Record Number (with INCENTIVE_TYPE from plan component)
"Parent Record Number" AS "ParentRecordNumber",
-- Multi-Currency Record Name (Child)
"Record Name" AS "RecordName",
-- Multi-Currency Record Number (Child)
"Record Number" AS "RecordNumber",
-- BB in Multi currency (only when BB data exists)
CASE
WHEN BB_HOME_CURRENCY_CODE IS NOT NULL
THEN TO_CHAR(ROUND("MC Earnings", 4), 'FM9999999990.0000')
ELSE NULL
END AS "BBInMultiCurrency_c",
-- BB Home currency
BB_HOME_CURRENCY_CODE AS "BBHomeCurrency_c",
-- BB in Home currency (only when BB data exists)
CASE
WHEN "BB PTD Earning LC" IS NOT NULL
THEN TO_CHAR(ROUND("BB PTD Earning LC", 4), 'FM9999999990.0000')
ELSE NULL
END AS "BBInHomeCurrency_c",
-- BB in Latest Home Currency
CASE
WHEN "BB PTD Earning (Latest HC)" IS NOT NULL
THEN TO_CHAR(ROUND(TO_NUMBER("BB PTD Earning (Latest HC)"), 4), 'FM9999999990.0000')
ELSE NULL
END AS "BBInLatestHomeCurrency_c",
-- BB Latest Home currency
CASE
WHEN BB_HOME_CURRENCY_CODE IS NOT NULL
THEN latest_currency_code
ELSE NULL
END AS "BBLatestHomeCurrency_c",
-- BB Conversion factor(HC to LHC) - COMBINED RATE
CASE
WHEN "BB Conversion Rate (HC_to_Latest_HC)" IS NOT NULL
THEN TO_CHAR(ROUND(TO_NUMBER("BB Conversion Rate (HC_to_Latest_HC)"), 4), 'FM9999999990.0000')
ELSE NULL
END AS "BBConversionFactorHCToLHC_c",
-- PTD in Multicurrency (only when PTD data exists)
CASE
WHEN PTD_HOME_CURRENCY_CODE IS NOT NULL
THEN TO_CHAR(ROUND("MC Earnings", 4), 'FM9999999990.0000')
ELSE NULL
END AS "PTDInMulticurrency_c",
-- PTD Earnings Home currency
PTD_HOME_CURRENCY_CODE AS "PTDEarningsHomeCurrency_c",
-- PTD Earnings in Home currency (only when PTD data exists)
CASE
WHEN "PTD Earning LC" IS NOT NULL
THEN TO_CHAR(ROUND("PTD Earning LC", 4), 'FM9999999990.0000')
ELSE NULL
END AS "PTDEarningsInHomeCurrency_c",
-- PTD Earnings in Latest Home Currency
CASE
WHEN "PTD Earning (Latest HC)" IS NOT NULL
THEN TO_CHAR(ROUND(TO_NUMBER("PTD Earning (Latest HC)"), 4), 'FM9999999990.0000')
ELSE NULL
END AS "PTDEarningsInLatestHomeCurrency_c",
-- PTD Earnings Latest Home currency
CASE
WHEN PTD_HOME_CURRENCY_CODE IS NOT NULL
THEN latest_currency_code
ELSE NULL
END AS "PTDEarningsLatestHomeCurrency_c",
-- PTD Earnings Conversion factor(HC to LHC) -COMBINED RATE
CASE
WHEN "Conversion Rate (HC_to_Latest_HC)" IS NOT NULL
THEN TO_CHAR(ROUND(TO_NUMBER("Conversion Rate (HC_to_Latest_HC)"), 4), 'FM9999999990.0000')
ELSE NULL
END AS "PTDEarningsConversionFactorHCToLHC_c",
-- Additional columns for reference
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
MC_SCENARIO_TYPE AS MultiCurrencyScenario_c
Comments
Post a Comment