APPS.PAY_AU_PAYSUM_ALLOWANCE SQL Statements

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

Line: 29

select balance_dimension_id
from pay_balance_dimensions
where  dimension_name = '_ASG_LE_YTD'
     and  legislation_code = 'AU'
;

Line: 37

select  distinct pbt.balance_name
     ,      pdb.defined_balance_id
     ,      pbt.balance_type_id
     from   pay_element_types_f         pet
     ,      pay_balance_types           pbt
     ,      pay_defined_balances        pdb
     where pet.business_group_id = p_business_group_id
     and   pet.element_information_category = 'AU_EARNINGS'
     and   pet.element_information1        = 'Y'
     and   pet.element_information2        = pbt.balance_type_id
     and   (pbt.business_group_id is not null or pbt.legislation_code is null)
     and   pbt.balance_type_id             = pdb.balance_type_id(+)
     and   pdb.balance_dimension_id(+) = c_balance_dimension_id
     and   not exists (
                                   select null
                                   from pay_balance_attributes pba,
                                             pay_bal_attribute_definitions pbad
                                   where pba.defined_balance_id = pdb.defined_balance_id
                                        and pbad.attribute_name = 'AU_EOY_ALLOWANCE'
                                        and pbad.attribute_id = pba.attribute_id
                                    )
     order by 1
     ;

Line: 63

select pbt.balance_name
       ,count(pdb.defined_balance_id) dim_count
from
          pay_defined_balances        pdb
     ,    pay_balance_dimensions  pbd
     ,    pay_balance_types              pbt
where pdb.balance_dimension_id(+) = pbd.balance_dimension_id
and pbd.dimension_name in ( '_ASG_LE_YTD', '_ASG_LE_RUN')
and pbd.legislation_code = 'AU'
and pdb.balance_type_id(+) = c_balance_type_id
and pbt.balance_type_id = c_balance_type_id
group by pbt.balance_name
order by 1
;

Line: 82

select  pbt.balance_name, pbt.balance_type_id
  from  PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
            ,pay_balance_attributes pba
            ,pay_defined_balances        pdb
            ,pay_balance_types           pbt
            ,pay_balance_dimensions pbd
 where  pbad.attribute_name = 'AU_EOY_ALLOWANCE'
     and   pba.attribute_id = pbad.attribute_id
     and   pba.defined_balance_id = pdb.defined_balance_id
     and   pdb.balance_type_id = pbt.balance_type_id
     and   pdb.business_group_id = c_business_group_id
     and   pbd.balance_dimension_id = pdb.balance_dimension_id
     and   pbd.dimension_name = '_ASG_LE_YTD'
     and   pbd.legislation_code = 'AU'
     order by 1
     ;

Line: 123

 t_allowance_balance.delete;

Line: 124

 t_chk_bal.delete;

Line: 126

select name into l_business_group_name
from per_business_groups
where business_group_id = p_business_group_id;

Line: 130

select decode(p_mode, 'V', 'Validate', 'Update') into l_mode from dual;

Line: 292

        select attribute_id
        from    PAY_BAL_ATTRIBUTE_DEFINITIONS
        where attribute_name = 'AU_EOY_ALLOWANCE' ;

Line: 326

            ELSE  -- update mode
                      PAY_BALANCE_ATTRIBUTE_API.create_balance_attribute
                            (p_validate                        => l_validate
                            ,p_attribute_id                   => l_attribute_id
                            ,p_defined_balance_id   => p_allowance_balance(i).defined_balance_id
                            ,p_business_group_id   => p_business_group_id
                            ,p_balance_attribute_id  => l_balance_attribute_id
                            );

Line: 382

select pdb.defined_balance_id
from pay_balance_dimensions pbd, pay_defined_balances pdb
where pbd.dimension_name = '_LE_YTD'
and pbd.legislation_code = 'AU'
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pdb.balance_type_id = c_balance_type_id
;

Line: 391

select pdb.defined_balance_id
from pay_balance_dimensions pbd, pay_defined_balances pdb
where pbd.dimension_name = '_LE_RUN'
and pbd.legislation_code = 'AU'
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pdb.balance_type_id = c_balance_type_id
;

Line: 460

    ELSE  -- update mode

        IF (p_t_allowance_balance.count > 0) THEN

            FOR i in p_t_allowance_balance.first .. p_t_allowance_balance.last LOOP

                open get_dimension_le_ytd (p_t_allowance_balance(i).balance_type_id);

Line: 562

select pbt.balance_name, pbd.dimension_name, pdb.defined_balance_id, pdb.save_run_balance
from pay_balance_types pbt
          ,pay_defined_balances pdb
          ,pay_balance_dimensions pbd
where pbt.balance_type_id = pdb.balance_type_id
and pbt. balance_type_id = c_balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name in ('_ASG_LE_RUN','_LE_RUN')
and nvl(pdb.save_run_balance, 'N') <> 'Y'
order by 1, 2
;

Line: 609

    ELSE  -- update mode

        IF (p_t_allowance_balance.count > 0) THEN

            FOR i in p_t_allowance_balance.first .. p_t_allowance_balance.last LOOP

                FOR rec_run_balance in get_run_balance (p_t_allowance_balance(i).balance_type_id) LOOP
                        FND_FILE.PUT_LINE(FND_FILE.LOG,p_t_allowance_balance(i).balance_name||' with '||rec_run_balance.dimension_name||'('||rec_run_balance.defined_balance_id||') : '||rec_run_balance.save_run_balance);

Line: 619

                        UPDATE  pay_defined_balances
                        SET  save_run_balance = 'Y'
                        WHERE  defined_balance_id = rec_run_balance.defined_balance_id;

Line: 642

                        UPDATE  pay_defined_balances
                        SET  save_run_balance = 'Y'
                        WHERE  defined_balance_id = rec_run_balance.defined_balance_id;

Line: 684

select
         pad.address_id
         ,pad.person_id
         ,pad.date_from
         ,pad.address_line1
         ,pad.address_line2
         ,pad.address_line3
         ,pad.town_or_city
         ,pad.region_1
         ,pad.postal_code
         ,pad.country
from per_addresses pad
        ,per_business_groups pbg
where pad.business_group_id = p_business_group_id
and pad.business_group_id = pbg.business_group_id
and pbg.legislation_code = 'AU'
and pad.style = 'AU_GLB'
and pad.primary_flag = 'Y'
order by person_id, date_from
;

Line: 707

select distinct full_name, employee_number
from per_all_people_f
where person_id = c_person_id
and c_date between effective_start_date and effective_end_date;

Line: 728

select name into l_business_group_name
from per_business_groups
where business_group_id = p_business_group_id;

Line: 732

select decode(p_mode, 'V', 'Validate', 'Update') into l_mode from dual;

Line: 804

  ELSIF p_mode <> 'V' and l_check <> 0 THEN /* the case that Update mode is selected when invalid data exist */
     raise e_bad_address;

Line: 816

IF p_mode <> 'V' and l_check = 0 THEN /* Update mode */

  l_count := 0;

Line: 821

    UPDATE per_addresses
    SET style = 'AU', address_line2 = rec_addr.address_line2||' '||rec_addr.address_line3, address_line3 = null
    WHERE address_id = rec_addr.address_id;

Line: 826

             FND_FILE.PUT_LINE(FND_FILE.LOG,rec_person.full_name ||'('||rec_person.employee_number||') - address style is updated to Australia effective from '||rec_addr.date_from);

Line: 835

  FND_FILE.PUT_LINE(FND_FILE.LOG,l_count||' Australia (International) address styles are updated to Australia address style');