Section 16-Done
FROM BG
-- **NEW JOIN: Add PARTICIPANT_DUAL_SNAPSHOT**
LEFT JOIN PARTICIPANT_DUAL_SNAPSHOT DUAL_SNAP
ON DUAL_SNAP.PAYRUN_ID = BG.PAYRUN_ID
AND DUAL_SNAP.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND DUAL_SNAP.ORG_ID = BG.ORG_ID
-- Person Numbers
LEFT JOIN PERSON_NUMBERS PN
ON PN.participant_id = BG.CREDITED_PARTICIPANT_ID
-- Latest Plan Component
LEFT JOIN LATEST_PLAN_COMPONENT LPC
ON LPC.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND LPC.ORG_ID = BG.ORG_ID
AND LPC.PERIOD_ID = BG.PAY_PERIOD_ID
AND LPC.PAYRUN_ID = BG.PAYRUN_ID
-- Calendar
LEFT JOIN PAY_CAL
ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(BG.PAYRUN_NAME)
AND PAY_CAL.PAYGROUP_NAME = UPPER(BG.PAY_GROUP_NAME)
AND PAY_CAL.PERIOD_ID = BG.PAY_PERIOD_ID
AND PAY_CAL.CURRENCY_CODE = BG.LATEST_HOME_CURRENCY
AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(LPC.INCENTIVE_TYPE))
-- Participant Details (for date range)
LEFT JOIN PARTICIPANT_DETAIL_DATERANGE parDetails
ON TRUNC(BG.PAY_DATE) BETWEEN parDetails.PD_START_DATE
AND NVL(parDetails.PD_END_DATE, DATE '9999-12-31')
AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID
-- Multi-Currency Flags
LEFT JOIN MULTI_CURRENCY_FLAGS MC_FLAGS
ON MC_FLAGS.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND MC_FLAGS.PAYRUN_ID = BG.PAYRUN_ID
AND MC_FLAGS.PERIOD_ID = BG.PAY_PERIOD_ID
-- Beginning Balance Amount (Multi-Currency)
LEFT JOIN BB_AMT
ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID
AND BB_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
-- PTD Earnings Amount (Multi-Currency)
LEFT JOIN PTD_EARNINGS_AMT PTD_AMT
ON PTD_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PTD_AMT.PAYRUN_ID = BG.PAYRUN_ID
AND PTD_AMT.ORG_ID = BG.ORG_ID
AND PTD_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
-- Prorated Monthly TI
LEFT JOIN PM_TI
ON PM_TI.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PM_TI.ORG_ID = BG.ORG_ID
AND PM_TI.PERIOD_ID = BG.PAY_PERIOD_ID
-- Base Payments (for single currency BB calculation)
LEFT JOIN BASE_PAYMENTS BP
ON BP.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND BP.PAYRUN_ID = BG.PAYRUN_ID
AND BP.ORG_ID = BG.ORG_ID
-- Conversion Rates (for single currency BB to USD)
LEFT JOIN CONVERSION_RATES_USD CR_USD
ON BP.HOME_CURRENCY_CODE != 'USD'
AND CR_USD.FROM_CURRENCY = BP.HOME_CURRENCY_CODE
AND TRUNC(BP.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE
AND CR_USD.CONVERSION_END_DATE
Comments
Post a Comment