eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EDW_HR_PERSON_M_C SQL Statements

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

Line: 13


/* This procedure dynamically builds a sql statement to insert rows */
/* into the given supervisor hierarchy level table from the given   */
/* level collection view                                            */
/********************************************************************/
Procedure Do_Insert( p_tree_number  IN NUMBER,
                     p_from_level   IN NUMBER,
                     p_to_level     IN NUMBER,
                     p_from_date    IN DATE,
                     p_to_date      IN DATE)
IS

  l_sql_stmt    VARCHAR2(2000); -- Holds SQL Statement to be executed

Line: 32


  l_pk_value    VARCHAR2(60);  -- Pk value selected from collection view

Line: 33


  l_fk_value    VARCHAR2(60);  -- Fk value selected from collection view

Line: 46


  l_rows_inserted    NUMBER := 0;  -- Number of rows inserted

Line: 134


/* Not a push down - straight insert */
/*************************************/
  IF (p_from_level = p_to_level) THEN

    l_sql_stmt :=
'Insert Into ' || l_to_table || '(
 assignment_id,
 collection_status,
 creation_date,
 error_code,
 instance,
 last_update_date,
 name,
 name_display,
 operation_code,
 person_id,
 request_id,
 row_id,
 sprvsr_dp,
 ' || l_pk_column || ',
 ' || l_fk_column || ',
 user_attribute1,
 user_attribute2,
 user_attribute3,
 user_attribute4,
 user_attribute5)
select
 lvln.assignment_id,
 ''READY'',
 sysdate,
 null,
 lvln.INSTANCE,
 sysdate,
 '||l_standard_name||',
 '||l_standard_name_display||',
 to_char(null),
 lvln.person_id,
 to_number(null),
 to_char(null),
 lvln.sprvsr_dp,
 ' || l_pk_value || ',
 ' || l_fk_value || ',
 lvln.user_attribute1,
 lvln.user_attribute2,
 lvln.user_attribute3,
 lvln.user_attribute4,
 lvln.user_attribute5
from ' || l_from_view || ' lvln,
 per_all_people_f peo
where peo.person_id = lvln.person_id
 and SYSDATE between peo.effective_start_date and peo.effective_end_date
UNION ALL
select
 to_number(null),
 ''READY'',
 sysdate,
 null,
 null,
 sysdate,
 null,
 null,
 to_char(null),
 to_number(null),
 to_number(null),
 to_char(null),
 null,
 ''' || l_na_edw_pk || ''',
 ''' || l_na_edw_fk || ''',
 null,
 null,
 null,
 null,
 null
from dual';

Line: 213


    l_sql_stmt := 'Insert Into ' || l_to_table || '(
        assignment_id,
        collection_status,
        creation_date,
        error_code,
        instance,
        last_update_date,
        name,
        name_display,
        operation_code,
        person_id,
        request_id,
        row_id,
        sprvsr_dp,
        ' || l_pk_column || ',
        ' || l_fk_column || ',
        user_attribute1,
        user_attribute2,
        user_attribute3,
        user_attribute4,
        user_attribute5
        )
     select lvln.assignment_id,
        ''READY'',
        sysdate,
        to_char(null),   -- error code
        lvln.INSTANCE,
        sysdate,
        ' || l_push_down_name || ',
        ' || l_push_down_name_display || ',
        to_char(null),   -- operation_code
        lvln.person_id,
        to_number(null), -- request_id
        to_char(null),   -- row_id
        ' || l_push_down_name || ',
        ' || l_pk_value || ',
        ' || l_fk_value || ',
        lvln.user_attribute1,
        lvln.user_attribute2,
        lvln.user_attribute3,
        lvln.user_attribute4,
        lvln.user_attribute5
     from ' || l_from_view || ' lvln,
          per_all_people_f peo
     where lvln.NAME is not null
     and   peo.person_id = lvln.person_id
     and   SYSDATE between peo.effective_start_date and
                           peo.effective_end_date
';

Line: 272


  l_rows_inserted := sql%rowcount;

Line: 278


  edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
  ' rows into the ' || l_to_table || ' staging table');

Line: 284


End Do_Insert;

Line: 288


/* New supervisor hierarchy inserted by HRI                                     */
/********************************************************************************/

Procedure Push_Tree( p_from_date         IN DATE,
                     p_to_date           IN DATE,
                     p_tree              IN NUMBER )
IS
BEGIN

/****************************************************/
/* Collect levels in the following order            */
/*  From view level 1  to table level 1             */
/*  From view level 2  to table level 1             */
/*   - - -          - - -       - - -               */
/*  From view level 15 to table level 1             */
/*  From view level 2  to table level 2             */
/*  From view level 3  to table level 2             */
/*   - - -          - - -       - - -               */
/*  From view level 15 to table level 2             */
/*   - - -          - - -       - - -               */
/*  From view level 14 to table level 14            */
/*  From view level 15 to table level 14            */
/*  From view level 15 to table level 15            */
/****************************************************/

  FOR v_push_to_level IN 1..g_number_of_levels LOOP

    edw_log.put_line('Starting Push_EDW_HR_PERM_SPSR_' || v_push_to_level || '_LSTG');

Line: 320


        Do_Insert( p_tree_number  => p_tree,
                   p_from_level   => v_push_from_view,
                   p_to_level     => v_push_to_level,
                   p_from_date    => p_from_date,
                   p_to_date      => p_to_date );

Line: 340


 l_rows_inserted            Number:=0;

Line: 347


 select supv_person_id
 from hri_supv_hrchy_summary;

Line: 388


/* 10 Levels inserted                            */
/*************************************************/

   edw_log.put_line( 'About to call Supervisor Hierarchy summary table population routine' );

Line: 431


   edw_log.put_line('Total rows inserted : '||g_row_count);

Line: 455


    l_rows_inserted NUMBER :=0;

Line: 460


   Insert Into
    EDW_HR_PERM_ASSIGN_LSTG@EDW_APPS_TO_WH(
    ASSIGNMENT_PK,
    BUSINESS_GROUP,
    CREATION_DATE,
    END_DATE,
    INSTANCE,
    LAST_UPDATE_DATE,
    NAME,
    NAME_DISPLAY,
    START_DATE,
    USER_ATTRIBUTE1,
    USER_ATTRIBUTE2,
    USER_ATTRIBUTE3,
    USER_ATTRIBUTE4,
    USER_ATTRIBUTE5,
    NATIONAL_IDENTIFIER,
    PERSON_DP,
    PERSON_ID,
    PERSON_NUM,
    PLANNER_CODE,
    PLANNER_FLAG,
    PREVIOUS_LAST_NAME,
    REGION_OF_BIRTH,
    REHIRE_RCMMNDTN,
    RESUME_EXISTS,
    RESUME_UPDATED_DATE,
    SALESREP_ID,
    SALES_REP_FLAG,
    STUDENT_STATUS,
    SYS_GEN_FLAG,
    TITLE,
    TOWN_OF_BIRTH,
    ALL_FK,
    SPRVSR_LVL1_FK,
    BUYER_FLAG,
    COUNTRY_OF_BIRTH,
    CRRSPNDNC_LANGUAGE,
    DATE_EMP_DATA_VRFD,
    DATE_OF_BIRTH,
    DISABILITY_FLAG,
    EFFECTIVE_END_DATE,
    EFFECTIVE_START_DATE,
    EMAIL_ADDRESS,
    FAST_PATH_EMPLOYEE,
    FIRST_NAME,
    FTE_CAPACITY,
    FULL_NAME,
    GENDER,
    GLOBAL_PERSON_ID,
    INTERNAL_LOCATION,
    KNOWN_AS,
    LAST_NAME,
    MAILSTOP,
    MARITAL_STATUS,
    MIDDLE_NAMES,
    NAME_PREFIX,
    NAME_SUFFIX,
    NATIONALITY,
    OPERATION_CODE,
    COLLECTION_STATUS,
/* New for 115.1 */
    EMPLOYEE_FLAG,
    APPLICANT_FLAG)
   select plcv.ASSIGNMENT_PK,
plcv.BUSINESS_GROUP,
plcv.CREATION_DATE,
plcv.END_DATE,
plcv.INSTANCE,
plcv.LAST_UPDATE_DATE,
/***************************************************/
/* The Name string is reformatted here and the     */
/* name attribute from the LCV is ignored.  This   */
/* is to facilitate easy changing of how this      */
/* string is constructed.                          */
/***************************************************/
DECODE(plcv.person_id,NULL,plcv.name,
  DECODE(plcv.known_as||plcv.first_name,NULL,'',
       NVL(plcv.known_as,plcv.first_name)||' ')
        ||plcv.last_name || '('||plcv.person_num||')'
      )                                         NAME,
DECODE(plcv.person_id,NULL,plcv.name,
  DECODE(plcv.known_as||plcv.first_name,NULL,'',
       NVL(plcv.known_as,plcv.first_name)||' ')
        ||plcv.last_name
         )                                      NAME_DISPLAY,
plcv.START_DATE,
plcv.USER_ATTRIBUTE1,
plcv.USER_ATTRIBUTE2,
plcv.USER_ATTRIBUTE3,
plcv.USER_ATTRIBUTE4,
plcv.USER_ATTRIBUTE5,
plcv.NATIONAL_IDENTIFIER,
plcv.PERSON_DP,
plcv.PERSON_ID,
plcv.PERSON_NUM,
plcv.PLANNER_CODE,
plcv.PLANNER_FLAG,
plcv.PREVIOUS_LAST_NAME,
plcv.REGION_OF_BIRTH,
plcv.REHIRE_RCMMNDTN,
plcv.RESUME_EXISTS,
plcv.RESUME_UPDATED_DATE,
plcv.SALESREP_ID,
plcv.SALES_REP_FLAG,
plcv.STUDENT_STATUS,
plcv.SYS_GEN_FLAG,
plcv.TITLE,
plcv.TOWN_OF_BIRTH,
NVL(plcv.ALL_FK, 'NA_EDW'),
plcv.SPRVSR_LVL1_FK,
plcv.BUYER_FLAG,
plcv.COUNTRY_OF_BIRTH,
plcv.CRRSPNDNC_LANGUAGE,
plcv.DATE_EMP_DATA_VRFD,
plcv.DATE_OF_BIRTH,
plcv.DISABILITY_FLAG,
plcv.EFFECTIVE_END_DATE,
plcv.EFFECTIVE_START_DATE,
plcv.EMAIL_ADDRESS,
plcv.FAST_PATH_EMPLOYEE,
plcv.FIRST_NAME,
plcv.FTE_CAPACITY,
plcv.FULL_NAME,
plcv.GENDER,
plcv.GLOBAL_PERSON_ID,
plcv.INTERNAL_LOCATION,
plcv.KNOWN_AS,
plcv.LAST_NAME,
plcv.MAILSTOP,
plcv.MARITAL_STATUS,
plcv.MIDDLE_NAMES,
plcv.NAME_PREFIX,
plcv.NAME_SUFFIX,
plcv.NATIONALITY,
    NULL, -- OPERATION_CODE
    'READY',
plcv.EMPLOYEE_FLAG,
plcv.APPLICANT_FLAG
   from EDW_HR_PERM_ASSIGN_LCV@APPS_TO_APPS plcv
   where plcv.last_update_date between l_date1 and l_date2;

Line: 602


   l_rows_inserted := sql%rowcount;

Line: 603


   EDW_HR_PERSON_M_C.g_row_count := EDW_HR_PERSON_M_C.g_row_count + l_rows_inserted ;

Line: 604


   edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
' rows into the EDW_HR_PERM_ASSIGN_LSTG staging table');

Line: 617


SELECT instance_code INTO g_instance_code
FROM edw_local_instance;