Oracle Cloud HCM - Custom SQL

WITH SAWITH0 AS (
  select
    
    /*+ inline */
    T5889157.C442017697 as c3,
    T5889157.C435128591 as c4,
    T5889157.C262853249 as c5,
    T5889157.C475696698 as c6,
    T5889157.C171798894 as c7,
    T5889157.C350832392 as c8,
    T5889157.C180745024 as c9,
    T5889157.C171898426 as c10,
    T5889157.C43504333 as c11,
    T5889157.C3583004 as c12,
    T5889157.C458818009 as c13
  from
    (
      SELECT
        V184978139.USER_CATEGORY_NAME AS C442017697,
        V148402464.DIMENSION_NAME AS C435128591,
        V187766949.BALANCE_NAME AS C262853249,
        V294690684.NAME AS C475696698,
        V501267603.END_DATE AS C171798894,
        V294690684.EFFECTIVE_END_DATE AS C350832392,
        V294690684.EFFECTIVE_START_DATE AS C180745024,
        V294690684.ORG_UNIT_CLASSIFICATION_ID AS C171898426,
        V148402464.DIMENSION_USAGE_ID AS C43504333,
        V460866823.BALANCE_VALUE AS C3583004,
        V216021127.PERSON_NUMBER AS C458818009,
        V524217779.ASSIGNMENT_ID AS C342861816,
        V524217779.EFFECTIVE_START_DATE AS C427273052,
        V524217779.EFFECTIVE_END_DATE AS C338379411,
        V524217779.EFFECTIVE_LATEST_CHANGE AS C340606624,
        V524217779.EFFECTIVE_SEQUENCE AS C234863912,
        V27313565.DEFINED_BALANCE_ID AS C65422935,
        V460866823.EFFECTIVE_DATE AS C24022705,
        V501267603.TIME_PERIOD_ID AS PKA_TimePeriodId0,
        V216021127.PERSON_ID415 AS PKA_PersonDetailsPEOPersonId0,
        V216021127.EFFECTIVE_START_DATE424 AS PKA_PersonDetailsPEOEffective0,
        V216021127.EFFECTIVE_END_DATE433 AS PKA_PersonDetailsPEOEffective1,
        V294690684.UNIT_TLEO_ORGANIZATION_ID AS PKA_OrganizationUnitTranslati0,
        V294690684.UNIT_TLEO_EFFECTIVE_START_DATE AS PKA_OrganizationUnitTranslati1,
        V294690684.UNIT_TLEO_EFFECTIVE_END_DATE AS PKA_OrganizationUnitTranslati2,
        V294690684.LANGUAGE AS PKA_OrganizationUnitTranslati3,
        V187766949.BALANCE_TYPE_ID AS PKA_BalanceTypesPEOBalanceTyp0,
        V184978139.BALANCE_CATEGORY_ID AS PKA_BalanceCategoriesPEOBalan0
      FROM
        (
          SELECT
            *
          FROM
            (
              select
                PayrollBalancesPEO.action_sequence,
                PayrollBalancesPEO.area1,
                PayrollBalancesPEO.area2,
                PayrollBalancesPEO.area3,
                PayrollBalancesPEO.area4,
                PayrollBalancesPEO.balance_date,
                PayrollBalancesPEO.balance_value,
                PayrollBalancesPEO.calc_breakdown_id,
                PayrollBalancesPEO.context_value1,
                PayrollBalancesPEO.context_value2,
                PayrollBalancesPEO.context_value3,
                PayrollBalancesPEO.context_value4,
                PayrollBalancesPEO.context_value5,
                PayrollBalancesPEO.context_value6,
                PayrollBalancesPEO.defined_balance_id,
                PayrollBalancesPEO.effective_date effective_date,
                PayrollBalancesPEO.element_entry_id,
                PayrollBalancesPEO.enterprise_id,
                PayrollBalancesPEO.legal_employer_id,
                PayrollBalancesPEO.payroll_action_id payroll_action_id2,
                PayrollBalancesPEO.payroll_assignment_id,
                PayrollBalancesPEO.payroll_id payroll_id2,
                PayrollBalancesPEO.payroll_rel_action_id,
                PayrollBalancesPEO.payroll_relationship_id payroll_relationship_id2,
                PayrollBalancesPEO.payroll_term_id,
                PayrollBalancesPEO.run_balance_id,
                PayrollBalancesPEO.tax_unit_id,
                PayrollBalancesPEO.third_party_payee_id,
                PayrollBalancesPEO.time_definition_id,
                PayrollRelationshipPEO.payroll_relationship_id payroll_relationship_id,
                PayrollRelationshipPEO.object_version_number,
                PayrollRelationshipPEO.person_id,
                PayrollActionPEO.payroll_id payroll_id,
                PayrollActionPEO.payroll_action_id payroll_action_Id,
                PayrollActionPEO.effective_date effective_date2,
                PayrollActionPEO.date_earned,
                PayrollActionPEO.PAY_REQUEST_ID,
                PayrollActionPEO.CONSOLIDATION_SET_ID,
                PayrollActionPEO.EARN_TIME_PERIOD_ID,
                PayrollActionPEO.DEDN_TIME_PERIOD_ID,
                PayrollRelationshipActionPEO.RUN_TYPE_ID,
                PayrollRelationshipPEO.PAYROLL_STAT_UNIT_ID,
                PayrollRelationshipPEO.LEGISLATIVE_DATA_GROUP_ID,
                NVL(
                  PRG.assignment_id, AssignmentPEO.assignment_id
                ) assignment_id,
                PayrollActionPEO.effective_date anchor_date,
                AssignmentPEO.EFFECTIVE_LATEST_CHANGE,
                PLDG.LEGISLATION_CODE
              from
                PAY_RUN_BALANCES PayrollBalancesPEO,
                PAY_PAY_RELATIONSHIPS_DN PayrollRelationshipPEO,
                PAY_PAYROLL_REL_ACTIONS PayrollRelationshipActionPEO,
                PAY_PAYROLL_ACTIONS PayrollActionPEO,
                PER_ALL_ASSIGNMENTS_F AssignmentPEO,
                PAY_REL_GROUPS_DN PRG,
                per_legislative_data_groups_vl PLDG
              where
                PayrollBalancesPEO.payroll_rel_action_id = PayrollRelationshipActionPEO.payroll_rel_action_id
                and PayrollRelationshipActionPEO.payroll_action_id = PayrollActionPEO.payroll_action_id
                and PayrollBalancesPEO.payroll_relationship_id = PayrollRelationshipPEO.payroll_relationship_id (+)
                and PayrollRelationshipPEO.person_id = AssignmentPEO.person_id
                and PayrollBalancesPEO.effective_date = PayrollActionPEO.effective_date
                and PayrollActionPEO.action_type IN ('R', 'B', 'V', 'Q', 'I')
                and PayrollActionPEO.effective_date between AssignmentPEO.effective_start_date
                and AssignmentPEO.effective_end_date
                and AssignmentPEO.primary_flag = 'Y'
                and PayrollRelationshipPEO.LEGISLATIVE_DATA_GROUP_ID = PLDG.LEGISLATIVE_DATA_GROUP_ID
                and AssignmentPEO.WORK_TERMS_ASSIGNMENT_ID is not null
                AND (
                  AssignmentPEO.assignment_status_type <> 'INACTIVE'
                  or (
                    AssignmentPEO.assignment_status_type = 'INACTIVE'
                    and not exists (
                      select
                        null
                      from
                        per_all_assignments_f aaa
                      where
                        aaa.person_id = AssignmentPEO.person_id
                        and aaa.WORK_TERMS_ASSIGNMENT_ID is not null
                        and aaa.PRIMARY_FLAG = 'Y'
                        and aaa.assignment_id != AssignmentPEO.assignment_id
                        and PayrollActionPEO.EFFECTIVE_DATE BETWEEN aaa.effective_start_Date
                        AND aaa.effective_end_date
                        and (
                          aaa.assignment_status_type <> 'INACTIVE'
                          or (
                            aaa.assignment_status_type = 'INACTIVE'
                            and AssignmentPEO.ASSIGNMENT_ID < aaa.ASSIGNMENT_ID
                          )
                        )
                    )
                  )
                )
                and PRG.relationship_group_id (+)= PayrollBalancesPEO.payroll_assignment_id
                and PayrollRelationshipActionPEO.RETRO_COMPONENT_ID is null
            ) QRSLT
          WHERE
            (
              (1 = 1)
            )
            AND (
              (1 = 1)
            )
        ) V460866823,
        (
          SELECT
            DimensionUsagesPEO.DIMENSION_USAGE_ID,
            DimensionUsagesPEO.DIMENSION_NAME,
            DimensionUsagesPEO.LEGISLATION_CODE,
            DefinedBalancesPEO.DEFINED_BALANCE_ID
          FROM
            PAY_DIMENSION_USAGES_VL DimensionUsagesPEO,
            PAY_DEFINED_BALANCES DefinedBalancesPEO
          WHERE
            DimensionUsagesPEO.BALANCE_DIMENSION_ID = DefinedBalancesPEO.BALANCE_DIMENSION_ID
        ) V148402464,
        (
          SELECT
            TimePeriodPEO.TIME_PERIOD_ID,
            TimePeriodPEO.END_DATE
          FROM
            PAY_TIME_PERIODS TimePeriodPEO
          WHERE
            (1 = 1)
        ) V501267603,
        (
          SELECT
            AssignmentPEO.ASSIGNMENT_ID,
            AssignmentPEO.EFFECTIVE_START_DATE,
            AssignmentPEO.EFFECTIVE_END_DATE,
            AssignmentPEO.EFFECTIVE_LATEST_CHANGE,
            AssignmentPEO.EFFECTIVE_SEQUENCE,
            AssignmentPEO.LEGAL_ENTITY_ID,
            AssignmentPEO.PERSON_ID
          FROM
            PER_ALL_ASSIGNMENTS_M AssignmentPEO
        ) V524217779,
        (
          SELECT
            
            /*+ NO_PUSH_PRED */
            PersonPEO.PERSON_ID AS PERSON_ID271,
            PersonDetailsPEO.PERSON_ID AS PERSON_ID415,
            PersonDetailsPEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE424,
            PersonDetailsPEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE433,
            PersonDetailsPEO.PERSON_NUMBER,
            AssignmentPEO.ASSIGNMENT_ID AS ASSIGNMENT_ID776,
            AssignmentPEO.PERSON_ID AS PERSON_ID1245
          FROM
            PER_PERSONS PersonPEO,
            PER_ALL_PEOPLE_F PersonDetailsPEO,
            PER_ALL_ASSIGNMENTS_M AssignmentPEO
          WHERE
            (
              PersonPEO.PERSON_ID = PersonDetailsPEO.PERSON_ID
              AND PersonPEO.PERSON_ID = AssignmentPEO.PERSON_ID
              AND (
                TRUNC(SYSDATE) BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE
                AND PersonDetailsPEO.EFFECTIVE_END_DATE
              )
              AND (
                TRUNC(SYSDATE) BETWEEN AssignmentPEO.EFFECTIVE_START_DATE
                AND AssignmentPEO.EFFECTIVE_END_DATE
              )
            )
            AND (
              (
                (
                  AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y'
                )
              )
              AND (
                (
                  (
                    (
                      AssignmentPEO.ASSIGNMENT_TYPE = 'E'
                    )
                  )
                  OR (
                    (
                      AssignmentPEO.ASSIGNMENT_TYPE = 'C'
                    )
                  )
                  OR (
                    (
                      AssignmentPEO.ASSIGNMENT_TYPE = 'N'
                    )
                  )
                  OR (
                    (
                      AssignmentPEO.ASSIGNMENT_TYPE = 'P'
                    )
                  )
                )
              )
            )
        ) V216021127,
        (
          SELECT
            OrgUnitClassificationPEO.ORG_UNIT_CLASSIFICATION_ID,
            OrgUnitClassificationPEO.EFFECTIVE_START_DATE,
            OrgUnitClassificationPEO.EFFECTIVE_END_DATE,
            OrganizationUnitPEO.ORGANIZATION_ID AS UNIT_EO_ORGANIZATION_ID,
            OrganizationUnitPEO.EFFECTIVE_START_DATE AS UNIT_EO_EFFECTIVE_START_DATE,
            OrganizationUnitPEO.EFFECTIVE_END_DATE AS UNIT_EO_EFFECTIVE_END_DATE,
            OrganizationUnitTranslationPEO.ORGANIZATION_ID AS UNIT_TLEO_ORGANIZATION_ID,
            OrganizationUnitTranslationPEO.EFFECTIVE_START_DATE AS UNIT_TLEO_EFFECTIVE_START_DATE,
            OrganizationUnitTranslationPEO.EFFECTIVE_END_DATE AS UNIT_TLEO_EFFECTIVE_END_DATE,
            OrganizationUnitTranslationPEO.LANGUAGE,
            OrganizationUnitTranslationPEO.NAME
          FROM
            HR_ALL_ORGANIZATION_UNITS_F OrganizationUnitPEO,
            HR_ORG_UNIT_CLASSIFICATIONS_F OrgUnitClassificationPEO,
            HR_ORGANIZATION_UNITS_F_TL OrganizationUnitTranslationPEO
          WHERE
            (
              OrganizationUnitPEO.ORGANIZATION_ID = OrgUnitClassificationPEO.ORGANIZATION_ID(+)
              AND OrganizationUnitPEO.ORGANIZATION_ID = OrganizationUnitTranslationPEO.ORGANIZATION_ID(+)
              AND OrganizationUnitPEO.EFFECTIVE_START_DATE = OrganizationUnitTranslationPEO.EFFECTIVE_START_DATE(+)
              AND OrganizationUnitPEO.EFFECTIVE_END_DATE = OrganizationUnitTranslationPEO.EFFECTIVE_END_DATE(+)
              AND (
                USERENV('LANG')
              ) = OrganizationUnitTranslationPEO.LANGUAGE(+)
              AND (
                TRUNC(SYSDATE) BETWEEN OrganizationUnitPEO.EFFECTIVE_START_DATE
                AND OrganizationUnitPEO.EFFECTIVE_END_DATE
              )
              AND (
                TRUNC(SYSDATE) BETWEEN OrgUnitClassificationPEO.EFFECTIVE_START_DATE(+)
                AND OrgUnitClassificationPEO.EFFECTIVE_END_DATE(+)
              )
              AND (
                TRUNC(SYSDATE) BETWEEN OrganizationUnitTranslationPEO.EFFECTIVE_START_DATE(+)
                AND OrganizationUnitTranslationPEO.EFFECTIVE_END_DATE(+)
              )
            )
            AND (
              (
                (
                  OrgUnitClassificationPEO.CLASSIFICATION_CODE = 'HCM_LEMP'
                )
              )
            )
        ) V294690684,
        (
          SELECT
            DefinedBalancesPEO.BALANCE_TYPE_ID,
            DefinedBalancesPEO.DEFINED_BALANCE_ID
          FROM
            PAY_DEFINED_BALANCES DefinedBalancesPEO
        ) V27313565,
        (
          SELECT
            BalanceTypesPEO.BALANCE_CATEGORY_ID,
            BalanceTypesPEO.BALANCE_NAME,
            BalanceTypesPEO.BALANCE_TYPE_ID
          FROM
            PAY_BALANCE_TYPES_VL BalanceTypesPEO
        ) V187766949,
        (
          SELECT
            BalanceCategoriesPEO.BALANCE_CATEGORY_ID,
            BalanceCategoriesPEO.USER_CATEGORY_NAME
          FROM
            PAY_BALANCE_CATEGORIES_VL BalanceCategoriesPEO
        ) V184978139
      WHERE
        (
          V460866823.DEFINED_BALANCE_ID = V148402464.DEFINED_BALANCE_ID
          AND V460866823.LEGISLATION_CODE = V148402464.LEGISLATION_CODE
          AND V460866823.EARN_TIME_PERIOD_ID = V501267603.TIME_PERIOD_ID
          AND V460866823.PERSON_ID = V524217779.PERSON_ID
          AND V460866823.ASSIGNMENT_ID = V524217779.ASSIGNMENT_ID
          AND V460866823.EFFECTIVE_LATEST_CHANGE = V524217779.EFFECTIVE_LATEST_CHANGE
          AND V524217779.PERSON_ID = V216021127.PERSON_ID1245(+)
          AND V524217779.ASSIGNMENT_ID = V216021127.ASSIGNMENT_ID776(+)
          AND V524217779.LEGAL_ENTITY_ID = V294690684.UNIT_EO_ORGANIZATION_ID(+)
          AND V460866823.DEFINED_BALANCE_ID = V27313565.DEFINED_BALANCE_ID
          AND V27313565.BALANCE_TYPE_ID = V187766949.BALANCE_TYPE_ID
          AND V187766949.BALANCE_CATEGORY_ID = V184978139.BALANCE_CATEGORY_ID
        )
        AND (
          (
            (
              (
                V460866823.EFFECTIVE_DATE >= V524217779.EFFECTIVE_START_DATE
              )
            )
            AND (
              (
                V460866823.EFFECTIVE_DATE <= V524217779.EFFECTIVE_END_DATE
              )
            )
          )
          AND V501267603.END_DATE = '2024-04-06'
          AND V184978139.USER_CATEGORY_NAME = 'Involuntary Deductions'
          AND V187766949.BALANCE_NAME = 'Child Support'
          AND V148402464.DIMENSION_NAME = 'Relationship NoCB Tax Unit Run'
        )
    ) T5889157
),
SAWITH1 AS (
  select
    
    /*+ inline */
    D1.c12 as c1,
    D1.c13 as c2,
    D1.c3 as c3,
    D1.c4 as c4,
    D1.c5 as c5,
    D1.c6 as c6,
    D1.c7 as c7,
    D1.c8 as c8,
    D1.c9 as c9,
    D1.c10 as c10,
    D1.c11 as c11
  from
    SAWITH0 D1
)
select count(c2) ee_count
      ,sum(c1) amount
  from SAWITH1