Posts

Showing posts from December, 2025

The Transformer Architecture

The Transformer Architecture: This is the "brain" behind GenAI. You must know the Attention Mechanism, which allows the model to focus on the most relevant words in a sentence regardless of how far apart they are. 2. The OCI Generative AI Service Oracle provides a fully managed service that lets you integrate LLMs into your apps. For the exam, remember these three providers/models OCI supports: Meta: Llama models (great for chat and general tasks). Cohere: Command, Embed, and Rerank models (excellent for business tasks and RAG). Mistral: High-performance open-source models. 3. Key OCI GenAI Features (High Probability for Exam) Oracle differentiates itself through security and performance. Memorize these: Dedicated AI Clusters: Instead of sharing hardware with other companies, OCI gives you dedicated GPU resources. This ensures consistent performance and ensures your data never leaves your environment to train public models. Fine-Tuning: The process of taking a pre-tra...

Full query

SELECT rules.*, -- BBAction_c DECODE(Rule_Number, 1, 'MAV_DIR_BB_ACTION_1000', 2, 'MAV_DIR_BB_ACTION_1003', 3, 'MAV_DIR_BB_ACTION_1001', 4, 'MAV_DIR_BB_ACTION_1003', 5, 'MAV_DIR_BB_ACTION_1003', 6, 'MAV_DIR_BB_ACTION_1003', 7, 'MAV_DIR_BB_ACTION_1003', 8, 'MAV_DIR_BB_ACTION_1003', 9, 'MAV_DIR_BB_ACTION_1003', 10, 'MAV_DIR_BB_ACTION_1003', 99, 'MAV_DIR_BB_ACTION_1002', 11, 'MAV_DIR_BB_ACTION_1002', NULL ) AS "BBAction_c", -- BBReasonCode_c DECODE(Rule_Number, 1, 'MVA_DIR_BB_REASON_1018', 2, 'MVA_DIR_BB_REASON_1001', 3, 'MVA_DIR_BB_REASON_1018', 4, 'MVA_DIR_BB_REASON_1001', 5, 'MVA_DIR_BB_REASON_1001', 6, 'MVA_DIR_BB_REASON_1001', 7, 'MVA_DIR...

Section 17-Done

GROUP BY BG.PAYRUN_ID, BG.PAYSHEET_ID, BG.PAY_PERIOD_ID, BG.PAYRUN_NAME, BG.PAY_DATE, BG.CREDITED_PARTICIPANT_ID, BG.ORG_ID, BG.PAY_GROUP_NAME, BG.LATEST_HOME_CURRENCY, BG.PAYSHEET_STATUS, BG.conversionrate, BG.finalPayout, BG.finalPayout_paysheet, BG.PAY_AMT_PAY_CURR_USD, PN.person_number, PN.PARTICIPANT_NAME, LPC.PLAN_COMPONENT_ID, LPC.PLAN_COMPONENT_name, LPC.INCENTIVE_TYPE, LPC.ROLE_NAME, PAY_CAL.PAYSHEET_CUTOFF_DT, PAY_CAL.MPA_CUTOFF_START_DT, PAY_CAL.MPA_CUTOFF_END_DT, PAY_CAL.PAYROLL_SUBMISSION_END_CST, parDetails.COUNTRY_CODE, parDetails.ANALYST_PARTY_ID, parDetails.PD_HR_TI, DUAL_SNAP.HRStatus_c, DUAL_SNAP.SalesRP_c, DUAL_...

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.LAT...

Section 15-Done

-- BB as % of HR TI TO_CHAR( CASE WHEN NVL(parDetails.PD_HR_TI, 0) = 0 THEN 0 ELSE ( TO_NUMBER( CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR ELSE 0 END ), 0 ), 'FM9999999990.0000' ) END ...

Section 14-Done

-- PTD Earnings (Period-to-Date) CASE WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN PTD_AMT.PTD_EARNINGS_LC ELSE BG.finalPayout END AS PTDEarnings_c, -- PTD Earnings in USD CASE WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN TO_CHAR(PTD_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000') ELSE TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000') END AS PTDEarningsUSD_c, -- Period Multi-Currency Flag NVL(MC_FLAGS.MULTI_CURR_YN, 'N') AS PeriodMultiCurrencyFlag_c, -- Current Earnings Distribution (CED) = PTD - BB TO_CHAR( TO_NUMBER( CASE WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN PTD_AMT.PTD_EARNINGS_LC ELSE BG.finalPayout ...

Section 13-Done

-- Beginning Balance (BB) fields CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR ELSE 0 END ), 0 ), 'FM9999999990.0000' ) END AS BeginningBalance_c, -- BB in USD CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_USD ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR * ...

Section 12-Done

SELECT -- Build unique record identifier TO_CHAR( BG.PAY_PERIOD_ID || '-' || SUBSTR( BG.PAYRUN_NAME, 1 + INSTR(BG.PAYRUN_NAME, '_', -1, 2), (INSTR(BG.PAYRUN_NAME, '_', -1, 1) - INSTR(BG.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || PN.person_number || '-' || BG.PAYSHEET_ID ) AS RecordId_c, -- Basic identifiers BG.PAYRUN_ID AS PayRunId_c, BG.PAYSHEET_ID AS PaysheetId_c, BG.PAY_PERIOD_ID AS PeriodId_c, PN.person_number AS PersonNumber_c, PN.PARTICIPANT_NAME AS ParticipantName_c, BG.CREDITED_PARTICIPANT_ID AS ParticipantId_c, -- Dates TO_CHAR(BG.PAY_DATE, 'YYYY-MM-DD') AS PayDate_c, TO_CHAR(PAY_CAL.PAYSH...

Section 11-Done

-- NEW CTE: Dual Snapshot for HR Status and Sales Rep PARTICIPANT_DUAL_SNAPSHOT AS ( SELECT BG.PAYRUN_ID, BG.CREDITED_PARTICIPANT_ID, BG.ORG_ID, BG.PAY_DATE, PAY_CAL.PAYSHEET_CUTOFF_DT, -- Snapshot at PAY_DATE PD_PAY.ATTRIBUTE_CHAR2 AS HR_Status_PayDt, PD_PAY.ATTRIBUTE_CHAR17 AS SalesRep_PayDt, PD_PAY.COUNTRY_CODE AS Employment_Country_PayDt, -- Snapshot at PAYSHEET_CUTOFF_DT PD_CUTOFF.ATTRIBUTE_CHAR2 AS HR_Status_CutoffDt, PD_CUTOFF.ATTRIBUTE_CHAR17 AS SalesRep_CutoffDt, PD_CUTOFF.COUNTRY_CODE AS Employment_Country_CutoffDt, -- Apply NVL logic here (prioritize cutoff date) NVL(PD_CUTOFF.ATTRIBUTE_CHAR2, PD_PAY.ATTRIBUTE_CHAR2) AS HRStatus_c, NVL(PD_CUTOFF.ATTRIBU...

Section 10- DOne

-- PTD USD PTD_EARNINGS_USD AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PTD_EARNINGS_USD, MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY FROM CN_TP_PARTICIPANT_PAY_ALL P INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = P.PAYRUN_ID AND PB.ORG_ID = P.ORG_ID INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID AND P.ORG_ID = PD.ORG_ID LEFT JOIN CONVERSIO...

Section 9- DOne

--BB -- Step 1: Convert BB to USD (grouped by HOME_CURRENCY_CODE across all old periods) BB_AMT_USD AS ( SELECT P.PAYRUN_ID, P.PAYEE_PARTICIPANT_ID AS CREDITED_PARTICIPANT_ID, P.PAYMENT_BATCH_PERIOD AS PAY_PERIOD_ID, LISTAGG(DISTINCT P.HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS HOME_CURRENCY_CODE, SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR, SUM( P.PAY_AMT_PAY_CURR * CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE NVL(CR_USD.CONVERSION_RATE, 1) END ) AS BB_AMOUNT_USD, MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY, MAX(P.PAY_DATE) AS PAY_DATE, LISTAGG(DISTINCT P.PAY_PERIOD_ID, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS OLD_PAY_PERI...

Section 8

-- Materialize latest plan component - FILTERED LATEST_PLAN_COMPONENT AS ( SELECT PARTICIPANT_ID, PAYRUN_NAME, ORG_ID, PAYRUN_ID, PERIOD_ID, PLAN_COMPONENT_ID, PLAN_COMPONENT_name, INCENTIVE_TYPE, ROLE_NAME, PAY_GROUP_NAME, START_DATE, END_DATE FROM ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PB.PAYRUN_NAME, PART_PAY.ORG_ID, PB.PAYRUN_ID, PB.PAY_PERIOD_ID AS PERIOD_ID, PART_PAY.PLAN_COMPONENT_ID, PC.INCENTIVE_TYPE, PC.Plan_component_name, roles.ROLE_NAME, PG.PAY_GROUP_NAME, TRUNC(SRP_CP.START_DATE) AS START...