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

Popular posts from this blog

Section 15-Done

Section 13-Done