DBA Data[Home] [Help]

APPS.HR_EFC_INFO SQL Statements

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

Line: 14

g_update_step_10 CONSTANT NUMBER := 15;
Line: 15

g_update_step_20 CONSTANT NUMBER := 2;
Line: 16

g_update_step_30 CONSTANT NUMBER := 7;
Line: 17

g_update_step_40 CONSTANT NUMBER := 0;
Line: 18

g_update_step_50 CONSTANT NUMBER := 1;
Line: 37

    SELECT ppt.payment_type_name
         , ppt.territory_code
         , ppt.category
      FROM pay_payment_types ppt
     WHERE ppt.payment_type_id = c_payment_type_id;
Line: 53

    SELECT act.business_group_id
      FROM hr_efc_actions act
     WHERE act.efc_action_status = 'P'
       AND act.efc_action_type = 'C';
Line: 83

  SELECT pap.parameter_value
    FROM pay_action_parameters pap
   WHERE pap.parameter_name = g_name;
Line: 111

  SELECT pbg.currency_code
    FROM per_business_groups pbg
   WHERE pbg.business_group_id = c_bg;
Line: 375

                                ,p_step           IN varchar2 default 'C_UPDATE'
                                ) IS
--
-- Cursor to find no. of workers
  CURSOR csr_check_workers(c_action_id IN number
                          ,c_component_name IN varchar2) IS
    SELECT epc.total_workers
      FROM hr_efc_process_components epc
     WHERE epc.efc_action_id = c_action_id
       AND epc.process_component_name = c_component_name;
Line: 391

    SELECT 'Y'
      FROM hr_efc_process_components epc
         , hr_efc_workers efw
     WHERE epc.efc_action_id = c_action_id
       AND epc.step = c_step
       AND epc.sub_step < c_sub_step
       AND efw.efc_process_component_id = epc.efc_process_component_id
       AND efw.worker_process_status = 'P';
Line: 405

   SELECT count(*)
     FROM hr_efc_process_components epc
        , hr_efc_workers efw
    WHERE epc.efc_action_id = c_action_id
      AND epc.step = c_step
      AND epc.sub_step = c_sub_step
      AND efw.efc_process_component_id = epc.efc_process_component_id
      AND efw.worker_process_status = 'C';
Line: 422

  IF ((p_step <> 'C_UPDATE') and (p_step <> 'C_RECAL')) THEN
     -- Incorrect parameter
     hr_utility.set_message(800,'PER_52703_EFC_INVALID_STEP');
Line: 448

  IF (p_step = 'C_UPDATE') THEN

     -- Work out if we have expected number of rows
     IF p_sub_step = '20' THEN
        l_expected := g_update_step_10 * p_total_workers;
Line: 454

        l_expected := g_update_step_20 * p_total_workers;
Line: 456

        l_expected := g_update_step_30 * p_total_workers;
Line: 458

        l_expected := g_update_step_40 * p_total_workers;
Line: 514

    SELECT act.efc_action_id
         , act.business_group_id
      FROM hr_efc_actions act
     WHERE act.efc_action_status = 'P'
       AND act.efc_action_type = 'C';
Line: 551

PROCEDURE insert_line(p_line VARCHAR2
                     ,p_line_num NUMBER default null) IS

  l_line_num number;
Line: 565

  INSERT INTO hr_api_user_hook_reports
    (session_id,
     line,
     text)
  VALUES
    (userenv('SESSIONID'),
     l_line_num,
     p_line);
Line: 574

END insert_line;
Line: 594

  insert_line(l_line, p_line);
Line: 611

    SELECT pbg.name
      FROM per_business_groups pbg
     WHERE pbg.business_group_id = p_bg;
Line: 625

  insert_line(l_line,l_line_num);
Line: 629

  insert_line(l_line,l_line_num);
Line: 633

  insert_line(l_line, l_line_num);
Line: 640

  insert_line(l_line, l_line_num);
Line: 644

  insert_line(l_line, l_line_num);
Line: 742

    SELECT ppt.payment_type_id
         , ppt.category
         , ppt.currency_code
      FROM pay_payment_types ppt
     WHERE ppt.payment_type_name = c_payment_type
       AND ppt.territory_code = c_territory_code;
Line: 750

    SELECT ppt.payment_type_id
         , ppt.category
         , ppt.currency_code
      FROM pay_payment_types ppt
     WHERE ppt.payment_type_name = c_payment_type
       AND ppt.territory_code IS NULL;
Line: 960

PROCEDURE insert_or_select_comp_row
  (p_action_id                IN     number
  ,p_process_component_name   IN     varchar2
  ,p_table_name               IN     varchar2
  ,p_total_workers            IN     number
  ,p_worker_id                IN     number
  ,p_step                     IN     varchar2
  ,p_sub_step                 IN     number
  ,p_process_component_id        OUT NOCOPY number) IS
--
  l_lockhandle  varchar2(128);
Line: 978

    SELECT epc.efc_process_component_id
      FROM hr_efc_process_components epc
     WHERE epc.efc_action_id = c_action_id
       AND epc.process_component_name = c_process_component_name;
Line: 1014

     INSERT INTO hr_efc_process_components
       (efc_process_component_id
       ,efc_action_id
       ,process_component_name
       ,table_name
       ,total_workers
       ,step
       ,sub_step
       ,last_update_date
       ,last_updated_by
       ,last_update_login
       ,created_by
       ,creation_date)
     VALUES
       (hr_efc_process_components_s.nextval
       ,p_action_id
       ,p_process_component_name
       ,p_table_name
       ,p_total_workers
       ,p_step
       ,p_sub_step
       ,sysdate
       ,-1
       ,-1
       ,-1
       ,sysdate)
     RETURNING efc_process_component_id INTO p_process_component_id;
Line: 1048

END insert_or_select_comp_row;
Line: 1053

PROCEDURE insert_or_select_worker_row
  (p_efc_worker_id              OUT NOCOPY number
  ,p_status                  IN OUT NOCOPY varchar2
  ,p_process_component_id    IN     number
  ,p_process_component_name  IN     varchar2
  ,p_action_id               IN     number
  ,p_worker_number           IN     number
  ,p_pk1                     IN OUT NOCOPY number
  ,p_pk2                     IN OUT NOCOPY varchar2
  ,p_pk3                     IN OUT NOCOPY varchar2
  ,p_pk4                     IN OUT NOCOPY varchar2
  ,p_pk5                     IN OUT NOCOPY varchar2
  ) IS
--
-- Cursor to check restart
CURSOR csr_restart(c_action_id IN number
                  ,c_worker_id IN number
                  ,c_component IN varchar2) IS
  SELECT ewo.efc_worker_id
       , ewo.worker_process_status
       , ewo.pk1
       , ewo.pk2
       , ewo.pk3
       , ewo.pk4
       , ewo.pk5
    FROM hr_efc_process_components epc
       , hr_efc_workers ewo
   WHERE epc.efc_action_id = c_action_id
     AND epc.process_component_name = c_component
     AND epc.efc_process_component_id = ewo.efc_process_component_id
     AND ewo.worker_number = c_worker_id;
Line: 1087

  SELECT p.spid
    FROM v$session s
       , v$process p
   WHERE s.audsid = userenv('SESSIONID')
     AND p.addr = s.paddr;
Line: 1116

     INSERT INTO hr_efc_workers
       (efc_worker_id
       ,efc_process_component_id
       ,efc_action_id
       ,worker_number
       ,worker_process_status
       ,pk1
       ,pk2
       ,pk3
       ,pk4
       ,pk5
       ,spid
       ,last_update_date
       ,last_updated_by
       ,last_update_login
       ,created_by
       ,creation_date
       )
     VALUES
       (hr_efc_workers_s.nextval
       ,p_process_component_id
       ,p_action_id
       ,p_worker_number
       ,p_status
       ,l_pk1
       ,p_pk2
       ,p_pk3
       ,p_pk4
       ,p_pk5
       ,l_spid
       ,sysdate
       ,-1
       ,-1
       ,-1
       ,sysdate
       )
     RETURNING efc_worker_id INTO p_efc_worker_id;
Line: 1171

           UPDATE hr_efc_workers
              SET spid = l_spid
            WHERE efc_worker_id = l_restart.efc_worker_id;
Line: 1189

END insert_or_select_worker_row;
Line: 1207

    SELECT ewa.efc_worker_audit_id
         , ewa.number_of_rows
      FROM hr_efc_worker_audits ewa
     WHERE ewa.efc_worker_id = c_worker_id
       AND ewa.column_name = c_column_name
       AND ewa.currency_code = c_currency;
Line: 1216

    SELECT hr_efc_worker_audits_s.nextval
      FROM dual;
Line: 1250

          INSERT INTO hr_efc_worker_audits
           (efc_worker_audit_id
           ,efc_worker_id
           ,column_name
           ,currency_code
           ,number_of_rows
           ,last_update_date
           ,last_updated_by
           ,last_update_login
           ,created_by
           ,creation_date
           )
          VALUES
           (hr_efc_worker_audits_s.nextval
           ,p_worker_id
           ,p_column_name
           ,p_last_curr
           ,p_count
           ,sysdate
           ,-1
           ,-1
           ,-1
           ,sysdate
           );
Line: 1277

         UPDATE hr_efc_worker_audits
            SET number_of_rows = l_rows + p_count
          WHERE efc_worker_audit_id = l_audit_id;
Line: 1303

    SELECT ewa.efc_worker_audit_id
         , ewa.number_of_rows
      FROM hr_efc_worker_audits ewa
     WHERE ewa.efc_worker_id = c_worker_id
       AND ewa.column_name = c_column_name
       AND ewa.currency_code = c_currency;
Line: 1324

        INSERT INTO hr_efc_worker_audits
         (efc_worker_audit_id
         ,efc_worker_id
         ,column_name
         ,currency_code
         ,number_of_rows
         ,last_update_date
         ,last_updated_by
         ,last_update_login
         ,created_by
         ,creation_date
         )
        VALUES
         (hr_efc_worker_audits_s.nextval
         ,p_efc_worker_id
         ,p_col_name
         ,p_last_curr
         ,p_count
         ,sysdate
         ,-1
         ,-1
         ,-1
         ,sysdate
         );
Line: 1351

       UPDATE hr_efc_worker_audits
          SET number_of_rows = l_rows + p_count
        WHERE efc_worker_audit_id = l_audit_id;
Line: 1365

PROCEDURE update_worker_row(p_efc_worker_id IN number
                           ,p_pk1           IN number
                           ,p_pk2           IN varchar2
                           ,p_pk3           IN varchar2
                           ,p_pk4           IN varchar2
                           ,p_pk5           IN varchar2
                           ) IS
BEGIN
  --
  -- Update the worker row
  UPDATE hr_efc_workers
  SET pk1 = p_pk1
    , pk2 = p_pk2
    , pk3 = p_pk3
    , pk4 = p_pk4
    , pk5 = p_pk5
  WHERE efc_worker_id = p_efc_worker_id;
Line: 1383

END update_worker_row;
Line: 1397

  UPDATE hr_efc_workers
    SET pk1 = p_pk1
      , pk2 = p_pk2
      , pk3 = p_pk3
      , pk4 = p_pk4
      , pk5 = p_pk5
      , worker_process_status = 'C'
  WHERE efc_worker_id = p_efc_worker_id;
Line: 1435

PROCEDURE delete_action_history IS
--
l_del_tab_sql varchar2(2000) :=
'BEGIN
  LOOP
    DELETE FROM  efc
    WHERE efc.efc_action_id = 
      AND ROWNUM < ;


Line: 1450



  SELECT act.matching_efc_action_id
    FROM hr_efc_actions act
   WHERE act.efc_action_type = 'D'
     AND act.efc_action_status = 'P';



Line: 1457



 SELECT distinct tab.table_name
   FROM all_tables tab
      , all_tab_columns col
      , user_synonyms syn
  WHERE ((tab.table_name like '%_EFC'
    AND tab.table_name <> 'PAY_BALANCE_TYPES_EFC'
    AND tab.table_name <> 'PAY_ORG_PAYMENT_METHODS_F_EFC'
    AND hr_general.hrms_object(tab.table_name) = 'TRUE')
     OR tab.table_name = 'HR_EFC_ROUNDING_ERRORS')
    AND col.table_name = tab.table_name
    AND col.column_name = 'EFC_ACTION_ID'
    AND tab.table_name = syn.synonym_name
    AND tab.owner = syn.table_owner
    AND col.owner = tab.owner;



Line: 1486



     hr_utility.set_message(800,'PER_52718_EFC_NO_DELETE_ACTION');



Line: 1511



END delete_action_history;



Line: 1516



PROCEDURE insert_rounding_row
  (p_action_id                IN     number
  ,p_source_id                IN     number
  ,p_source_table             IN     varchar2
  ,p_source_column            IN     varchar2
  ,p_rounding_amount          IN     number) IS
--
  --
BEGIN
  --
  --
   INSERT INTO hr_efc_rounding_errors
       (efc_rounding_error_id
       ,efc_action_id
       ,source_id
       ,source_table
       ,source_column
       ,rounding_amount
       ,last_update_date
       ,last_updated_by
       ,last_update_login
       ,created_by
       ,creation_date)
     VALUES
       (hr_efc_rounding_errors_s.nextval
       ,p_action_id
       ,p_source_id
       ,p_source_table
       ,p_source_column
       ,p_rounding_amount
       ,sysdate
       ,-1
       ,-1
       ,-1
       ,sysdate);



Line: 1556



END insert_rounding_row;



Line: 1575



  SELECT tab.column_name,
         tab.data_type,
         tab.data_length
    FROM all_tab_columns tab
    ,    user_synonyms syn
   WHERE tab.table_name = c_name
   AND   tab.table_name = syn.synonym_name
   AND   tab.owner = syn.table_owner;