APPS.PY_ZA_BAL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 55

    select value,
           assignment_action_id
    from   pay_assignment_latest_balances
    where  assignment_id = c_assignment_id
    and    defined_balance_id = c_defined_bal_id;

Line: 90

   select action_sequence from
          pay_assignment_actions
   where assignment_action_id = c_assignment_action_id;

Line: 121

        SELECT value
        from pay_assignment_latest_balances
        Where assignment_action_id = c_asg_action_id
        and   defined_balance_id = c_defined_balance_id;

Line: 158

    SELECT assignment_id
    FROM pay_assignment_actions paa,
         pay_payroll_actions    ppa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  ppa.payroll_action_id = paa.payroll_action_id
    AND  ppa.action_type        in ('R', 'Q', 'I', 'V', 'B');

Line: 194

    SELECT
         to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
    FROM pay_assignment_actions paa,
         pay_payroll_actions    ppa
    WHERE
         paa.assignment_id = c_assignment_id
    AND  ppa.payroll_action_id = paa.payroll_action_id
    AND  ppa.effective_date <= c_effective_date
    AND  ppa.action_type        in ('R', 'Q', 'I', 'V', 'B'); */

Line: 207

SELECT   /*+ ORDERED
                USE_NL(PAA PPA)
                INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
                INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
            TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||
                      paa.assignment_action_id),16))
   FROM     pay_assignment_actions paa,
            pay_payroll_actions ppa
   WHERE    paa.assignment_id = c_assignment_id
   AND      ppa.payroll_action_id = paa.payroll_action_id
   AND      ppa.effective_date <= c_effective_date
   AND      ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');

Line: 253

    SELECT expired_value
    FROM pay_assignment_latest_balances
    WHERE expired_assignment_action_id = c_assignment_action_id
    AND assignment_id = c_assignment_id
    AND defined_balance_id = c_defined_balance_id;

Line: 289

   select pdb.defined_balance_id from
        pay_defined_balances pdb,
        pay_balance_dimensions pbd
   where pdb.balance_dimension_id = pbd.balance_dimension_id
   and pbd.database_item_suffix =  c_db_item_suffix
   and pdb.balance_type_id = c_balance_type_id;

Line: 322

   SELECT    ppa.effective_date
   FROM      pay_payroll_actions ppa,
             pay_assignment_actions paa
   WHERE     paa.payroll_action_id = ppa.payroll_action_id
   AND       paa.assignment_action_id = p_assignment_action_id;

Line: 362

    SELECT ptp.prd_information1, ppa.payroll_id
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 372

    SELECT max(ptp.end_date)
    FROM per_time_periods ptp
    WHERE
         ptp.prd_information1 = c_tax_year
    AND  ptp.payroll_id = c_payroll_id;

Line: 425

                SELECT
                        effective_start_date
                INTO
                        l_period_from_date
                FROM
                        ff_globals_f
                WHERE   global_name = l_global_name
                AND     business_group_id = p_business_group_id
                AND     p_user_date BETWEEN effective_start_date AND effective_end_date;

Line: 451

        select nvl(min(P.effective_start_date)
                  ,to_date('31-12-4712','dd-mm-yyyy'))
                into l_event_from_date
                   from per_people_f p,  /* should this be all ? */
                        per_assignments_f ass
                   where p.per_information2 = 'Y'
                   and ASS.person_id = P.person_id
                   and P.effective_start_date <= p_end_date
                   and p.effective_end_date >=   p_start_date
                   and p_end_date between
                                ass.effective_start_date and ass.effective_end_date
                   and ass.assignment_id = p_assignment_id ;

Line: 496

        SELECT value, assignment_id
        from  pay_assignment_latest_balances
        Where assignment_action_id = c_asg_action_id
        and   defined_balance_id = c_defined_balance_id;

Line: 502

        SELECT
                BAL_ASSACT.assignment_id,
                BAL_ASSACT.action_sequence,
                BACT.effective_date,
                PTP.regular_payment_date,
                BACT.business_group_id
        FROM
                pay_assignment_actions  BAL_ASSACT,
                pay_payroll_actions             BACT,
                per_time_periods                        PTP
        WHERE
                BAL_ASSACT.assignment_action_id = p_assignment_action_id
        AND     PTP.time_period_id = BACT.time_period_id
        AND     BACT.payroll_action_id = BAL_ASSACT.payroll_action_id;

Line: 518

        SELECT
                DB.balance_type_id,
                DIM.dimension_name,
                DIM.database_item_suffix ,
                DIM.legislation_code
        FROM
                pay_defined_balances    DB,
                pay_balance_dimensions  DIM
        WHERE   DB.defined_balance_id = p_defined_balance_id
        AND     DIM.balance_dimension_id = DB.balance_dimension_id;

Line: 636

      select pdb.balance_type_id,
             pbd.dimension_name
      from   pay_balance_dimensions pbd,
             pay_defined_balances   pdb
      where  pdb.defined_balance_id = c_defined_balance_id
      and    pdb.balance_dimension_id = pbd.balance_dimension_id;

Line: 715

      select pdb.balance_type_id,
             pbd.dimension_name
      from   pay_balance_dimensions pbd,
             pay_defined_balances   pdb
      where  pdb.defined_balance_id = c_defined_balance_id
      and    pdb.balance_dimension_id = pbd.balance_dimension_id;

Line: 848

    SELECT ptp.end_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 997

    SELECT ptp.end_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1064

    SELECT ptp.end_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1374

    SELECT ptp.end_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1439

    SELECT ptp.end_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1588

    SELECT ptp.pay_advice_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1656

    SELECT ptp.pay_advice_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1666

    SELECT ptp.pay_advice_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1778

    SELECT ptp.prd_information2, ppa.payroll_id
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 1788

    SELECT ptp.prd_information2
    FROM per_time_periods ptp
    WHERE
         ptp.start_date <= c_effective_date
    AND  ptp.end_date   >= c_effective_date
    AND  ptp.payroll_id = c_payroll_id;

Line: 1796

    SELECT ptp.prd_information2
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 2301

    SELECT ptp.end_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 2366

    SELECT ptp.end_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 2514

    SELECT ptp.pay_advice_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 2582

    SELECT ptp.pay_advice_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 2592

    SELECT ptp.pay_advice_date
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 2744

    SELECT to_number(ptp.prd_information3)
    FROM per_time_periods ptp,
         pay_payroll_actions ppa,
         pay_assignment_actions paa
    WHERE
         paa.assignment_action_id = c_assignment_action_id
    AND  paa.payroll_action_id = ppa.payroll_action_id
    AND  ppa.time_period_id = ptp.time_period_id;

Line: 2814

                select to_number(ptp.prd_information3)
                from per_time_periods ptp,
                         pay_payroll_actions ppa,
                         pay_assignment_actions paa
                where
                         paa.assignment_action_id = c_assignment_action
                and      ppa.payroll_action_id    = paa.payroll_action_id
                and      ptp.time_period_id               = ppa.time_period_id;

Line: 2824

                select to_number(ptp.prd_information3)
                from per_time_periods ptp,
                         pay_payroll_actions ppa,
                         pay_assignment_actions paa
                where
                         paa.assignment_action_id = c_assignment_action
                and      ppa.payroll_action_id    = paa.payroll_action_id
                and      ptp.time_period_id               = ppa.time_period_id;

Line: 2936

        SELECT
                NVL(SUM(TARGET.result_value * FEED.scale),0)
        INTO
                l_balance
        FROM
                pay_balance_feeds_f             FEED
                ,pay_run_result_values          TARGET
                ,pay_run_results                RR
                ,pay_payroll_actions            PACT
                ,per_time_periods               PPTP
                ,pay_assignment_actions         ASSACT
        WHERE
                        FEED.balance_type_id = P_BALANCE_TYPE_ID
        AND     FEED.input_value_id = TARGET.input_value_id
        AND     TARGET.run_result_id = RR.run_result_id
        AND     RR.assignment_action_id = ASSACT.assignment_action_id
        AND     ASSACT.payroll_action_id = PACT.payroll_action_id
        AND     PACT.effective_date BETWEEN FEED.effective_start_date AND FEED.effective_end_date
        AND     RR.status IN ('P','PA')
        AND     PACT.time_period_id = PPTP.time_period_id
        AND     PPTP.regular_payment_date >= P_PERIOD_FROM_DATE
        AND     PACT.effective_date >= P_EVENT_FROM_DATE
        AND     PACT.effective_date <= P_TO_DATE
        AND     ASSACT.action_sequence <= NVL(P_ACTION_SEQUENCE,ASSACT.action_sequence)
        AND     ASSACT.assignment_id = P_ASSIGNMENT_ID;

Line: 2993

                SELECT
                        pay_balance_dimensions_s.NEXTVAL
                INTO
                        l_bal_next
                FROM DUAL;

Line: 3009

                INSERT INTO FF_ROUTES
                (
                        route_id,
                        route_name,
                        user_defined_flag,
                        description,
                        text
                )
                VALUES
                (
                        ff_routes_s.NEXTVAL,
                        'ROUTE_NAME_' || ff_routes_s.CURRVAL ,
                        'N',
                        'User balance dimension for '||
                         UPPER(RPAD(p_suffix,30,' ')) || 'USER-REG ASG '||
                         p_start_dd_mm || ' RESET'|| TO_CHAR(p_frequency,'00'),
                        l_route_text
                );

Line: 3034

                INSERT INTO ff_route_context_usages
                (
                        route_id,
                        context_id,
                        sequence_no
                )
                SELECT
                        ff_routes_s.CURRVAL,
                        context_id,
                        1
                FROM
                        ff_contexts
                WHERE
                        context_name = 'ASSIGNMENT_ACTION_ID';

Line: 3055

                INSERT INTO ff_route_parameters
                (
                        route_parameter_id,
                        route_id,
                        sequence_no,
                        parameter_name,
                        data_type
                )
                VALUES
                (
                        ff_route_parameters_s.NEXTVAL,
                        ff_routes_s.CURRVAL,
                        1,
                        'BALANCE TYPE ID',
                        'N'
                );

Line: 3124

                INSERT INTO pay_balance_dimensions
                (
                        balance_dimension_id,
                        business_group_id,
                        legislation_code,
                        route_id,
                        database_item_suffix,
                        dimension_name,
                        dimension_type,
                        description,
                        feed_checking_code,
                        legislation_subgroup,
                        payments_flag,
                        expiry_checking_code,
                        expiry_checking_level,
                        feed_checking_type
                )
                VALUES
                (
                        pay_balance_dimensions_s.CURRVAL,
                        p_business_group_id,
                        NULL,
                        ff_routes_s.CURRVAL,
                        p_suffix,
                        l_dim_name,
                        l_dim_type,
                        'User dimension defined by Request Id ' || l_req_id,
                        NULL,
                        NULL,
                        'N',
                        'hr_gbbal.check_expiry',
                        'P',
                        NULL
                );

Line: 3206

           SELECT ptp.end_date
           INTO   l_end_date
           FROM   per_time_periods ptp,
                  pay_payroll_actions ppa,
                  pay_assignment_actions paa
           WHERE  paa.assignment_action_id = c_assignment_action_id
           AND    paa.payroll_action_id = ppa.payroll_action_id
           AND    ppa.time_period_id = ptp.time_period_id;

Line: 3228

           SELECT ptp.pay_advice_date
           INTO   l_end_date
           FROM   per_time_periods ptp,
                  pay_payroll_actions ppa,
                  pay_assignment_actions paa
           WHERE  paa.assignment_action_id = c_assignment_action_id
           AND    paa.payroll_action_id = ppa.payroll_action_id
           AND    ppa.time_period_id = ptp.time_period_id;

Line: 3250

         SELECT  to_number(ptp.prd_information3)
         INTO    l_calendar_year
         FROM    per_time_periods ptp,
                 pay_payroll_actions ppa,
                 pay_assignment_actions paa
         WHERE   paa.assignment_action_id = c_assignment_action_id
         AND     paa.payroll_action_id = ppa.payroll_action_id
         AND     ppa.time_period_id = ptp.time_period_id;

Line: 3277

         SELECT ptp.prd_information2, ppa.payroll_id
         FROM   per_time_periods ptp,
                pay_payroll_actions ppa,
                pay_assignment_actions paa
         WHERE  paa.assignment_action_id = c_assignment_action_id
         AND    paa.payroll_action_id = ppa.payroll_action_id
         AND    ppa.time_period_id = ptp.time_period_id;

Line: 3286

         SELECT  ptp.prd_information2
         FROM    per_time_periods ptp
         WHERE   ptp.start_date <= c_effective_date
         AND     ptp.end_date   >= c_effective_date
         AND     ptp.payroll_id = c_payroll_id;

Line: 3335

         SELECT /*+ ORDERED
                USE_NL(PAA PPA)
                INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
                INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
			TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||
                      paa.assignment_action_id),16))
         FROM     pay_assignment_actions paa,
                  pay_payroll_actions ppa
         WHERE    paa.assignment_id = c_assignment_id
         AND      ppa.payroll_action_id = paa.payroll_action_id
         AND      ppa.effective_date <= c_effective_date
         AND      ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');

Line: 3351

         SELECT  pdb.defined_balance_id
         FROM    pay_defined_balances pdb,
                 pay_balance_dimensions pbd
         WHERE   pdb.balance_dimension_id = pbd.balance_dimension_id
         AND     pbd.database_item_suffix = c_db_item_suffix
         AND     pdb.balance_type_id = c_balance_type_id;

Line: 3457

         SELECT /*+ ORDERED
                USE_NL(PAA PPA)
                INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
                INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
			TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||
                      paa.assignment_action_id),16))
         FROM     pay_assignment_actions paa,
                  pay_payroll_actions ppa
         WHERE    paa.assignment_id = c_assignment_id
         AND      ppa.payroll_action_id = paa.payroll_action_id
         AND      ppa.effective_date <= c_effective_date
         AND      ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');

Line: 3518

         SELECT  pdb.defined_balance_id
         FROM    pay_defined_balances pdb,
                 pay_balance_dimensions pbd
         WHERE   pdb.balance_dimension_id = pbd.balance_dimension_id
         AND     pbd.database_item_suffix = c_db_item_suffix
         AND     pdb.balance_type_id = c_balance_type_id;