APPS.PAY_BEE_SS SQL Statements

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

Line: 218

                                        p_delete_flag     in varchar2 default 'N',
                                        p_assignment_id in varchar2
                                       ,p_login_person_id  in number
				                               ) as


  l_transaction_id             number default null;

Line: 291

  select count(*) into l_flag from hr_api_transaction_values where name = p_assignment_no||'@P_EMPLOYEE_NAME' and transaction_step_id = l_transaction_step_id;

Line: 341

  l_transaction_table(l_count).param_name := p_assignment_no||'@P_DELETE_FLAG_'||p_iterator;

Line: 342

  l_transaction_table(l_count).param_value := p_delete_flag;

Line: 382

                                        p_delete_flag     in varchar2 default 'N'
                                       ,p_login_person_id  in number
				                               ) as


  l_transaction_id             number default null;

Line: 411

UPDATE  hr_api_transaction_values
SET     varchar2_value ='Unprocessed'
WHERE   transaction_step_id in (select transaction_step_id  from hr_api_transaction_steps where transaction_id =l_transaction_id)
and name = 'P_BATCH_STATUS_FLAG';

Line: 459

select count(*) into l_flag from hr_api_transaction_values where name = 'P_BATCH_TYPE' and transaction_step_id = l_transaction_step_id;

Line: 508

  l_transaction_table(l_count).param_name := 'P_DELETE_FLAG_'||p_iterator;

Line: 509

  l_transaction_table(l_count).param_value := p_delete_flag;

Line: 608

select ELEMENT_TYPE_ID into l_element_type_id
from pay_element_types_f
where ELEMENT_NAME = p_element_name;

Line: 612

select INPUT_VALUE_ID into l_input_value_id
from pay_input_values_f
where ELEMENT_TYPE_ID = l_element_type_id
and NAME = p_input_value_name;

Line: 709

/* select ELEMENT_TYPE_ID into l_element_type_id
from pay_element_types_f
where ELEMENT_NAME = p_element_name;

Line: 713

select INPUT_VALUE_ID into l_input_value_id
from pay_input_values_f
where ELEMENT_TYPE_ID = l_element_type_id
and NAME = p_input_value_name; */

Line: 763

select transaction_id into l_transaction_id from hr_api_transaction_steps where transaction_step_id in (select TRANSACTION_STEP_ID from hr_api_transaction_values where name='P_BATCH_NAME' and VARCHAR2_VALUE=p_batch_name);

Line: 767

procedure update_delete_flag(p_transaction_id in number,p_name in varchar2) is
--pragma autonomous_transaction;

Line: 772

update hr_api_transaction_values set varchar2_value='Y' where name=p_name and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=p_transaction_id);

Line: 774

end update_delete_flag;

Line: 777

procedure update_batch_header_table(p_batch_name in varchar2,
                                    p_business_group_id in number) is

--pragma autonomous_transaction;

Line: 793

select pay_batch_headers_s.nextval  into l_batch_id from dual;

Line: 795

select transaction_step_id into l_transaction_step_id
from hr_api_transaction_values
where name = 'P_BATCH_NAME'
and varchar2_value = p_batch_name;

Line: 820

insert into pay_batch_headers(BATCH_ID,BUSINESS_GROUP_ID,BATCH_NAME,batch_status,ACTION_IF_EXISTS,PURGE_AFTER_TRANSFER,REJECT_IF_FUTURE_CHANGES,DATE_EFFECTIVE_CHANGES)
                       values(l_batch_id,p_business_group_id,p_batch_name,'U',l_action_if_exists,l_purge_after_transfer,l_reject_if_future_changes,l_date_effective_changes);

Line: 823

update hr_api_transaction_values set number_value=l_batch_id
where name='P_BATCH_ID'
and transaction_step_id=l_transaction_step_id;

Line: 828

end update_batch_header_table;

Line: 830

procedure update_batch_lines_table(p_batch_name in varchar2,
                                    p_business_group_id in number) is

--pragma autonomous_transaction;

Line: 867

select varchar2_value from hr_api_transaction_values
where name like '%P_ASSIGNMENT_NUMBER%'
and transaction_step_id=l_transaction_step_id;

Line: 872

 select  max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp1, VARCHAR2_VALUE, null)),
                max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp2, VARCHAR2_VALUE, null)),
                max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp3, DATE_VALUE, null)),
                max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp4, VARCHAR2_VALUE, null))
                from   hr_api_transaction_values
                where (name like l_temp5
                or name like l_temp6
                or name like l_temp7
                or name like l_temp8)
                AND    TRANSACTION_STEP_ID in (select TRANSACTION_STEP_ID from hr_api_transaction_steps where TRANSACTION_ID=l_transaction_id)
                group by regexp_replace(substr(name,instr(name,'@')+1), '[A-Z]','')
                order by  regexp_replace(substr(name,instr(name,'@')+1), '[A-Z]','');

Line: 886

 select input_value_id from pay_input_values_f
 where element_type_id=l_element_type_id
  order by display_sequence;

Line: 892

select transaction_id into l_transaction_id
from hr_api_transaction_steps
where transaction_step_id = (select transaction_step_id
from hr_api_transaction_values
where name = 'P_BATCH_NAME'
and varchar2_value = p_batch_name);

Line: 899

select distinct transaction_step_id into l_transaction_step_id
from hr_api_transaction_values
where name like '%P_EMPLOYEE_NAME%'
and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps  where transaction_id=l_transaction_id);

Line: 904

select batch_id into l_batch_id from pay_batch_headers
where batch_name = p_batch_name;

Line: 930

            select element_type_id into l_element_type_id from pay_element_types_f
            where element_name = l_element_name
            and business_group_id=p_business_group_id;

Line: 941

  /*          select distinct transaction_step_id into l_transaction_step_id_1
            from hr_api_transaction_values
            where name like l_temp9
            and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps  where transaction_id=l_transaction_id);   */

Line: 987

           insert into pay_batch_lines (BATCH_LINE_ID,
                             COST_ALLOCATION_KEYFLEX_ID,
                             ELEMENT_TYPE_ID,
                             ASSIGNMENT_ID,
                             BATCH_ID,
                             BATCH_LINE_STATUS,
                             ASSIGNMENT_NUMBER,
                             BATCH_SEQUENCE,
                             EFFECTIVE_DATE,
                             ELEMENT_NAME,
                             ENTRY_TYPE,
                             VALUE_1,
                             VALUE_2,
                             VALUE_3,
                             VALUE_4,
                             VALUE_5,
                             VALUE_6,
                             VALUE_7,
                             VALUE_8,
                             VALUE_9,
                             VALUE_10,
                             VALUE_11,
                             VALUE_12,
                             VALUE_13,
                             VALUE_14,
                             VALUE_15,
                             OBJECT_VERSION_NUMBER)
                       values(pay_batch_lines_s.nextval,
                              l_costing_flex_id,
                              l_element_type_id,
                              l_assignment_id,
                              l_batch_id,
                              'U',
                              l_assignment_number,
                              l_batch_sequence,
                              l_effective_date,
                              l_element_name,
                              'E',
                              input_values(1),
                              input_values(2),
															input_values(3),
															input_values(4),
															input_values(5),
															input_values(6),
															input_values(7),
															input_values(8),
															input_values(9),
															input_values(10),
															input_values(11),
															input_values(12),
															input_values(13),
															input_values(14),
															input_values(15),
															1);

Line: 1053

end update_batch_lines_table;

Line: 1076

select batch_line_id from pay_batch_lines where batch_id=l_batch_id;

Line: 1079

SELECT  source_type_meaning
      , line_text
      , message_level
      , assignment_number
      , element_name
      , batch_id
      , source_type
      , source_id
FROM    pay_paywsqee_messages
where batch_id = l_batch_id;

Line: 1095

select batch_id into l_batch_id from pay_batch_headers
where batch_name = p_batch_name;

Line: 1105

select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;

Line: 1118

select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;

Line: 1145

UPDATE  hr_api_transaction_values
SET     varchar2_value ='Valid'
WHERE   transaction_step_id in (select transaction_step_id  from hr_api_transaction_steps where transaction_id =l_transaction_id)
and name = 'P_BATCH_STATUS_FLAG';

Line: 1150

UPDATE  hr_api_transaction_values
SET     varchar2_value ='Validation Failed'
WHERE   transaction_step_id in (select transaction_step_id  from hr_api_transaction_steps where transaction_id =l_transaction_id)
and name = 'P_BATCH_STATUS_FLAG';

Line: 1167

delete from pay_batch_lines where batch_line_id=l_batch_line_id;

Line: 1175

 SELECT  batch_status into l_batch_status
                         FROM    pay_batch_headers
                         WHERE   batch_name =p_batch_name;

Line: 1192

delete from pay_batch_headers where batch_id=l_batch_id;

Line: 1209

UPDATE  hr_api_transaction_values
SET     varchar2_value ='Processing'
WHERE   transaction_step_id in (select transaction_step_id  from hr_api_transaction_steps where transaction_id =l_transaction_id)
and name = 'P_BATCH_STATUS_FLAG';

Line: 1231

procedure update_element_lines_table(p_batch_name in varchar2,
                                    p_business_group_id in number
                                   ) is

--pragma autonomous_transaction;

Line: 1274

select varchar2_value from hr_api_transaction_values
where name like '%P_ASSIGNMENT_NUMBER%'
and transaction_step_id=l_transaction_step_id;

Line: 1280

 select  max(decode(substr(name,1,15),'P_ELEMENT_NAME_', VARCHAR2_VALUE, null)),
                max(decode(substr(name,1,15),'P_ELEMENT_TYPE_', VARCHAR2_VALUE, null)),
                max(decode(substr(name,1,17),'P_EFFECTIVE_DATE_', DATE_VALUE, null)),
                max(decode(substr(name,1,13),'P_COSTING_ID_', VARCHAR2_VALUE, null)),
                max(decode(substr(name,1,13),'P_ELEMENT_ID_', VARCHAR2_VALUE, null)),
                max(decode(substr(name,1,20),'P_ASSIGNMENT_NUMBER_', VARCHAR2_VALUE, null))
                from   hr_api_transaction_values
                where (name like 'P_ELEMENT_NAME_%'
                or name like 'P_ELEMENT_TYPE_%'
                or name like 'P_EFFECTIVE_DATE_%'
                or name like 'P_COSTING_ID_%'
                or name like 'P_ELEMENT_ID_%'
                or name like 'P_ASSIGNMENT_NUMBER_%')
                AND    TRANSACTION_STEP_ID in (select TRANSACTION_STEP_ID from hr_api_transaction_steps where TRANSACTION_ID=l_transaction_id)
                group by regexp_replace(name, '[A-Z]','')
                order by  regexp_replace(name, '[A-Z]','');

Line: 1297

 select input_value_id from pay_input_values_f
 where element_type_id=l_element_type_id
  order by display_sequence;

Line: 1303

select transaction_id into l_transaction_id
from hr_api_transaction_steps
where transaction_step_id = (select transaction_step_id
from hr_api_transaction_values
where name = 'P_BATCH_NAME'
and varchar2_value = p_batch_name);

Line: 1311

select number_value into l_business_group_id
from hr_api_transaction_values
where name = 'P_BUSINESS_GROUP_ID'
and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);

Line: 1316

select distinct transaction_step_id into l_transaction_step_id
from hr_api_transaction_values
where name like 'P_EMPLOYEE_NAME%'
and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps  where transaction_id=l_transaction_id);

Line: 1321

delete from hr_api_transaction_values where transaction_step_id in (select transaction_step_id from hr_api_transaction_steps  where transaction_id=l_transaction_id)
and name like 'P_EFFECTIVE_DATE_OPTION';

Line: 1327

select batch_id into l_batch_id from pay_batch_headers
where batch_name = p_batch_name;

Line: 1340

SELECT  assignment_id into l_assignment_id
FROM    per_all_assignments_f paf
WHERE   SYSDATE
        BETWEEN paf.effective_start_date
        AND     paf.effective_end_date
and paf.business_group_id=l_business_group_id
and paf.assignment_number=l_assignment_number;

Line: 1353

             select distinct transaction_step_id into l_transaction_step_id_1
            from hr_api_transaction_values
            where name like '%@%'
            and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps  where transaction_id=l_transaction_id);

Line: 1407

 insert into pay_batch_lines (BATCH_LINE_ID,
                             COST_ALLOCATION_KEYFLEX_ID,
                             ELEMENT_TYPE_ID,
                             ASSIGNMENT_ID,
                             BATCH_ID,
                             BATCH_LINE_STATUS,
                             ASSIGNMENT_NUMBER,
                             BATCH_SEQUENCE,
                             EFFECTIVE_DATE,
                             ELEMENT_NAME,
                             ENTRY_TYPE,
                             VALUE_1,
                             VALUE_2,
                             VALUE_3,
                             VALUE_4,
                             VALUE_5,
                             VALUE_6,
                             VALUE_7,
                             VALUE_8,
                             VALUE_9,
                             VALUE_10,
                             VALUE_11,
                             VALUE_12,
                             VALUE_13,
                             VALUE_14,
                             VALUE_15,
                             OBJECT_VERSION_NUMBER)
                       values(pay_batch_lines_s.nextval,
                              l_costing_flex_id,
                              l_element_type_id,
                              l_assignment_id,
                              l_batch_id,
                              'U',
                              l_assignment_number,
                              l_batch_sequence,
                              l_effective_date,
                              l_element_name,
                              'E',
                              input_values(1),
                              input_values(2),
															input_values(3),
															input_values(4),
															input_values(5),
															input_values(6),
															input_values(7),
															input_values(8),
															input_values(9),
															input_values(10),
															input_values(11),
															input_values(12),
															input_values(13),
															input_values(14),
															input_values(15),
															1);

Line: 1479

end update_element_lines_table;

Line: 1533

procedure update_transaction_id(p_item_key in varchar2,p_batch_name in varchar2,p_login_person_id in number) is
l_old_item_key varchar2(4000);

Line: 1550

select transaction_id into l_old_transaction_id
from hr_api_transaction_steps
where transaction_step_id = (select transaction_step_id
from hr_api_transaction_values
where name = 'P_BATCH_NAME'
and varchar2_value = p_batch_name);

Line: 1587

select varchar2_value into l_old_item_key
from hr_api_transaction_values
where name = 'P_ITEM_KEY'
and transaction_step_id = (select transaction_step_id
from hr_api_transaction_values
where name = 'P_BATCH_NAME'
and varchar2_value = p_batch_name);

Line: 1597

update hr_api_transaction_values set varchar2_value=p_item_key where
 name = 'P_ITEM_KEY'
and transaction_step_id = (select transaction_step_id
from hr_api_transaction_values
where name = 'P_BATCH_NAME'
and varchar2_value = p_batch_name);

Line: 1604

update hr_api_transaction_steps set transaction_id=l_transaction_id,item_key=p_item_key
where item_type='HRSSA'
and item_key=l_old_item_key
and transaction_id = l_old_transaction_id;

Line: 1610

update PQH_SS_TRANS_STATE_HISTORY set transaction_history_id=l_transaction_id
where transaction_history_id=l_old_transaction_id;

Line: 1613

update PQH_SS_APPROVAL_HISTORY set transaction_history_id=l_transaction_id
where transaction_history_id=l_old_transaction_id;

Line: 1618

end update_transaction_id;

Line: 1620

procedure delete_assignment_information(p_batch_name in varchar2,p_assignment_no in varchar2) is

l_transaction_id number;

Line: 1631

  select count(*) into l_flag
 from hr_api_transaction_values
 where name = p_assignment_no||'@P_ASSIGNMENT_NUMBER'
 and varchar2_value =p_assignment_no
and transaction_step_id in(select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );

Line: 1642

select transaction_step_id into l_transaction_step_id from hr_api_transaction_values
where name = p_assignment_no||'@P_ASSIGNMENT_NUMBER'
 and varchar2_value =p_assignment_no
and transaction_step_id in(select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );

Line: 1649

delete from hr_api_transaction_values
where name like l_temp
and transaction_step_id =l_transaction_step_id;

Line: 1657

end delete_assignment_information;

Line: 1679

select transaction_id into l_transaction_id
from hr_api_transaction_steps
where transaction_step_id=p_transaction_step_id;

Line: 1685

select varchar2_value into l_batch_type
from hr_api_transaction_values
where name = 'P_BATCH_TYPE'
and transaction_step_id = p_transaction_step_id;

Line: 1692

select varchar2_value into l_batch_name
from hr_api_transaction_values
where name = 'P_BATCH_NAME'
and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);

Line: 1697

select number_value into l_business_group_id
from hr_api_transaction_values
where name = 'P_BUSINESS_GROUP_ID'
and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);

Line: 1702

select status into l_status_wf
from hr_api_transactions
where transaction_id=l_transaction_id;

Line: 1707

select created_by into l_user_id
from hr_api_transactions
where transaction_id=l_transaction_id;

Line: 1710

select responsibility_id into l_resp_id from fnd_user_resp_groups_direct where user_id=l_user_id and rownum=1;

Line: 1720

update_batch_header_table(p_batch_name =>l_batch_name,
                                    p_business_group_id =>l_business_group_id);

Line: 1723

update_batch_lines_table(p_batch_name=>l_batch_name,
                                    p_business_group_id =>l_business_group_id);

Line: 1728

update_batch_header_table(p_batch_name =>l_batch_name,
                                    p_business_group_id =>l_business_group_id);

Line: 1731

update_element_lines_table(p_batch_name=>l_batch_name,
                                    p_business_group_id =>l_business_group_id);

Line: 1738

select batch_id into l_batch_id
from pay_batch_headers
where batch_name=l_batch_name;

Line: 1778

select batch_id into l_batch_id
from pay_batch_headers
where batch_name=p_batch_name;

Line: 1790

select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;

Line: 1799

select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;

Line: 1824

procedure delete_batch_on_cancel(p_batch_name in varchar2) is
l_transaction_id number;

Line: 1831

select count(*) into l_flag
from hr_api_transaction_values
where name = 'P_BATCH_TYPE'
and transaction_step_id in(select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );

Line: 1838

delete from hr_api_transaction_values
where transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );

Line: 1842

delete from hr_api_transaction_steps
where transaction_id=l_transaction_id;

Line: 1846

delete from hr_api_transactions
where transaction_id=l_transaction_id;

Line: 1853

end delete_batch_on_cancel;