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_ORGANIZATION_M_C SQL Statements

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

Line: 10


/* This procedure dynamically builds a sql statement to insert rows */
/* into the given org 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: 29


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

Line: 30


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

Line: 37


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

Line: 104


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

    l_sql_stmt := 'Insert Into ' || l_to_table || '(
      BUSINESS_GROUP,
      CREATION_DATE,
      INSTANCE,
      LAST_UPDATE_DATE,
      NAME,
      ORGANIZATION_ID,
      ' || l_pk_column || ',
      ' || l_fk_column || ',
      PRIMARY_ORG_DP,
      OPERATION_CODE,
      COLLECTION_STATUS,
      USER_ATTRIBUTE1,
      USER_ATTRIBUTE2,
      USER_ATTRIBUTE3,
      USER_ATTRIBUTE4,
      USER_ATTRIBUTE5)
    select BUSINESS_GROUP,
      sysdate,
      INSTANCE,
      sysdate,
      NAME,
      ORGANIZATION_ID,
      ' || l_pk_value || ',
      ' || l_fk_value || ',
      PRIMARY_ORG_DP,
      NULL, -- OPERATION_CODE
      ''READY'',
      NULL,
      NULL,
      NULL,
      NULL,
      NULL
     from ' || l_from_view || '
     where last_update_date between :date_from and :date_to';

Line: 148


    l_sql_stmt := 'Insert Into ' || l_to_table || '(
      BUSINESS_GROUP,
      CREATION_DATE,
      INSTANCE,
      LAST_UPDATE_DATE,
      NAME,
      ORGANIZATION_ID,
      ' || l_pk_column || ',
      ' || l_fk_column || ',
      PRIMARY_ORG_DP,
      OPERATION_CODE,
      COLLECTION_STATUS,
      USER_ATTRIBUTE1,
      USER_ATTRIBUTE2,
      USER_ATTRIBUTE3,
      USER_ATTRIBUTE4,
      USER_ATTRIBUTE5)
    select BUSINESS_GROUP,
      sysdate,
      INSTANCE,
      sysdate,
      ''' || l_push_lookup || ''' || ''('' || NAME || '')'',
      ORGANIZATION_ID,
      ' || l_pk_value || ',
      ' || l_fk_value || ',
      ''' || l_push_lookup || ''' || ''('' || NAME || '')'',
      NULL, -- OPERATION_CODE
      ''READY'',
      NULL,
      NULL,
      NULL,
      NULL,
      NULL
     from ' || l_from_view || '
     where last_update_date between :date_from and :date_to
     and NAME is not null';

Line: 194


  l_rows_inserted := sql%rowcount;

Line: 195


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

Line: 201


End Do_Insert;

Line: 214


 l_rows_inserted        Number := 0;

Line: 239


   Insert Into EDW_ORGA_ORG_LSTG@EDW_APPS_TO_WH(
     BUSINESS_GROUP,
     ORGANIZATION_DP,
     ORGANIZATION_PK,
     ROW_ID,
     DATE_FROM,
     DATE_TO,
     INSTANCE,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     NAME,
     OPERATING_UNIT_FK,
     OPERATING_UNIT_FK_KEY,
     ORG_CODE,
     ORG_INT_EXT_FLAG,
     ORG_PRIM_CST_MTHD,
     ORG_TYPE,
     LEVEL_NAME,
     PERSON_MANAGER_ID,
     PERSON_MANAGER_FK,
     PERSON_MANAGER_FK_KEY,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     REQUEST_ID,
     OPERATION_CODE,
     ERROR_CODE,
     COLLECTION_STATUS,
/* New changes by HRI */
     ORGANIZATION_ID,
     ORG_CAT1,
     ORG_CAT10,
     ORG_CAT11,
     ORG_CAT12,
     ORG_CAT13,
     ORG_CAT14,
     ORG_CAT15,
     ORG_CAT2,
     ORG_CAT3,
     ORG_CAT4,
     ORG_CAT5,
     ORG_CAT6,
     ORG_CAT7,
     ORG_CAT8,
     ORG_CAT9,
     ORG_TREE1_LVL1_FK)
   select
     BUSINESS_GROUP,
     ORGANIZATION_DP,
     ORGANIZATION_PK,
     NULL,		--ROW_ID,
     DATE_FROM,
     DATE_TO,
     INSTANCE,			--bis_edw_instance.get_code,
     sysdate,
     sysdate,			--CREATION_DATE,
     NAME,
     nvl(OPERATING_UNIT_FK, 'NA_EDW'),
     NULL,			--OPERATING_UNIT_FK_KEY
     ORG_CODE,
     ORG_INT_EXT_FLAG,
     ORG_PRIM_CST_MTHD,
     ORG_TYPE,
     LEVEL_NAME,
     PERSON_MANAGER_ID,
     PERSON_MANAGER_FK,
     PERSON_MANAGER_FK_KEY,
     NULL, --USER_ATTRIBUTE1,
     NULL, --USER_ATTRIBUTE2,
     NULL, --USER_ATTRIBUTE3,
     NULL, --USER_ATTRIBUTE4,
     NULL, --USER_ATTRIBUTE5,
     NULL,			--REQUEST_ID,
     NULL, 			--OPERATION_CODE
     NULL,			--ERROR_CODE
     'READY',
     ORGANIZATION_ID,
     ORG_CAT1,
     ORG_CAT10,
     ORG_CAT11,
     ORG_CAT12,
     ORG_CAT13,
     ORG_CAT14,
     ORG_CAT15,
     ORG_CAT2,
     ORG_CAT3,
     ORG_CAT4,
     ORG_CAT5,
     ORG_CAT6,
     ORG_CAT7,
     ORG_CAT8,
     ORG_CAT9,
     NVL(ORG_TREE1_LVL1_FK, 'NA_EDW')
   from EDW_ORGA_ORG_LCV@APPS_TO_APPS
   where last_update_date between l_push_date_range1 and l_push_date_range2
   or (last_update_date is null);

Line: 338


   l_rows_inserted := nvl(sql%rowcount,0);

Line: 341


   edw_log.put_line('Inserted ' || to_char(l_rows_inserted) ||
         ' rows into the EDW_ORGA_ORG_LSTG staging table');

Line: 347


					l_rows_inserted;

Line: 377


 l_rows_inserted        Number := 0;

Line: 403


   Insert Into EDW_ORGA_OPER_UNIT_LSTG@EDW_APPS_TO_WH(
     BUSINESS_GROUP,
     DATE_FROM,
     DATE_TO,
     INSTANCE,
     INT_EXT_FLAG,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     LEGAL_ENTITY_FK,
     LEGAL_ENTITY_FK_KEY,
     NAME,
     OPERATING_UNIT_DP,
     OPERATING_UNIT_PK,
     ROW_ID,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     LEVEL_NAME,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     REQUEST_ID,
     OPERATION_CODE,
     ERROR_CODE,
     COLLECTION_STATUS,
/* New change by HRI */
     OPERATING_UNIT_ID)
   select
     BUSINESS_GROUP,
     DATE_FROM,
     DATE_TO,
     INSTANCE,			--bis_edw_instance.get_code,
     INT_EXT_FLAG,
     sysdate,
     sysdate,			--CREATION_DATE
     nvl(LEGAL_ENTITY_FK, 'NA_EDW'),
     NULL,			--LEGAL_ENTITY_FK_KEY,
     NAME,
     OPERATING_UNIT_DP,
     OPERATING_UNIT_PK,
     null,  ---rowid
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     LEVEL_NAME,
     NULL, --USER_ATTRIBUTE1,
     NULL, --USER_ATTRIBUTE2,
     NULL, --USER_ATTRIBUTE3,
     NULL, --USER_ATTRIBUTE4,
     NULL, --USER_ATTRIBUTE5,
     NULL,			--REQUEST_ID,
     NULL, 			--OPERATION_CODE
     NULL,			--ERROR_CODE,
     'READY',
     OPERATING_UNIT_ID
   from EDW_ORGA_OPER_UNIT_LCV@apps_to_apps
   where last_update_date between g_push_date_range1 and g_push_date_range2
   or (last_update_date is null);

Line: 464


   l_rows_inserted := nvl(sql%rowcount,0);

Line: 467


   edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
         ' rows into the EDW_ORGA_OPER_UNIT_LSTG staging table');

Line: 489


 Insert Into EDW_ORGA_OPER_UNIT_LSTG@EDW_APPS_TO_WH(
     BUSINESS_GROUP,
     DATE_FROM,
     DATE_TO,
     INSTANCE,
     INT_EXT_FLAG,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     LEGAL_ENTITY_FK,
     LEGAL_ENTITY_FK_KEY,
     NAME,
     OPERATING_UNIT_DP,
     OPERATING_UNIT_PK,
     ROW_ID,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     LEVEL_NAME,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     REQUEST_ID,
     OPERATION_CODE,
     ERROR_CODE,
     COLLECTION_STATUS,
/* New change by HRI */
     OPERATING_UNIT_ID)
   select
     null,   -- BUSINESS_GROUP,
     DATE_FROM,
     DATE_TO,
     INSTANCE,			--bis_edw_instance.get_code,
     INT_EXT_FLAG,
     sysdate,
     sysdate,			--CREATION_DATE
     BUSINESS_GROUP_PK ||'-'||'BGRP',
     NULL,			--LEGAL_ENTITY_FK_KEY,
     l_tmp_str1 || ' (' || l_tmp_str2 || ' (' || NAME || '))',  --for: NAME
     l_tmp_str1 || ' (' || l_tmp_str2 || ' (' || NAME || '))',  --for: OPERATING_UNIT_DP
     BUSINESS_GROUP_PK ||'-'||'BGRP',
     null,   --rowid
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     'BGRP',
     NULL, --USER_ATTRIBUTE1,
     NULL, --USER_ATTRIBUTE2,
     NULL, --USER_ATTRIBUTE3,
     NULL, --USER_ATTRIBUTE4,
     NULL, --USER_ATTRIBUTE5,
     NULL,			--REQUEST_ID,
     NULL, 			--OPERATION_CODE
     NULL,			--ERROR_CODE,
     'READY',
     BUSINESS_GROUP_ID
   from EDW_ORGA_BUSINESS_GRP_LCV@apps_to_apps
   where last_update_date between g_push_date_range1 and g_push_date_range2
   or (last_update_date is null);

Line: 550


   l_rows_inserted := nvl(sql%rowcount,0);

Line: 553


   edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
         ' rows into the EDW_ORGA_BUSINESS_GROUP_LSTG staging table');

Line: 587


 l_rows_inserted        Number := 0;

Line: 612


   Insert Into EDW_ORGA_LEG_ENTITY_LSTG@EDW_APPS_TO_WH(
     BUSINESS_GROUP_FK,
     BUSINESS_GROUP_FK_KEY,
     DATE_FROM,
     DATE_TO,
     INSTANCE,
     INT_EXT_FLAG,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     LEGAL_ENTITY_DP,
     LEGAL_ENTITY_PK,
     ROW_ID,
     NAME,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     SET_OF_BOOKS,
     LEVEL_NAME,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     REQUEST_ID,
     OPERATION_CODE,
     ERROR_CODE,
     COLLECTION_STATUS,
/* New change by HRI */
     LEGAL_ENTITY_ID)
   select
     nvl(BUSINESS_GROUP_FK, 'NA_EDW'),
     NULL,			--BUSINESS_GROUP_FK,
     DATE_FROM,
     DATE_TO,
     INSTANCE,			--bis_edw_instance.get_code,
     INT_EXT_FLAG,
     sysdate,
     sysdate,			--CREATION_DATE,
     LEGAL_ENTITY_DP,
     LEGAL_ENTITY_PK,
     NULL,			--ROW_ID,
     NAME,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     SET_OF_BOOKS,
     LEVEL_NAME,
     NULL, --USER_ATTRIBUTE1,
     NULL, --USER_ATTRIBUTE2,
     NULL, --USER_ATTRIBUTE3,
     NULL, --USER_ATTRIBUTE4,
     NULL, --USER_ATTRIBUTE5,
     NULL,			--REQUEST_ID,
     NULL, 			--OPERATION_CODE
     NULL,			--ERROR_CODE,
     'READY',
     LEGAL_ENTITY_ID
   from EDW_ORGA_LEG_ENTITY_LCV@apps_to_apps
   where last_update_date between g_push_date_range1 and g_push_date_range2
   or (last_update_date is null);

Line: 673


   l_rows_inserted := nvl(sql%rowcount,0);

Line: 676


   edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
         ' rows into the EDW_ORGA_LEG_ENTITY_LSTG staging table');

Line: 695


   Insert Into EDW_ORGA_LEG_ENTITY_LSTG@EDW_APPS_TO_WH(
     BUSINESS_GROUP_FK,
     BUSINESS_GROUP_FK_KEY,
     DATE_FROM,
     DATE_TO,
     INSTANCE,
     INT_EXT_FLAG,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     LEGAL_ENTITY_DP,
     LEGAL_ENTITY_PK,
     ROW_ID,
     NAME,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     SET_OF_BOOKS,
     LEVEL_NAME,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     REQUEST_ID,
     OPERATION_CODE,
     ERROR_CODE,
     COLLECTION_STATUS,
/* New change by HRI */
     LEGAL_ENTITY_ID)
   select
     BUSINESS_GROUP_PK,
     NULL,			--BUSINESS_GROUP_FK,
     DATE_FROM,
     DATE_TO,
     INSTANCE,			--bis_edw_instance.get_code,
     INT_EXT_FLAG,
     sysdate,
     sysdate,			--CREATION_DATE,
     l_tmp_str || ' (' || NAME || ')',  --for: BUSINESS_GROUP_DP
     BUSINESS_GROUP_PK ||'-'||'BGRP',
     NULL,			--ROW_ID,
     l_tmp_str || ' (' || NAME || ')',  --for: NAME,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     null, ---SET_OF_BOOKS,
     'BGRP',
     NULL, --USER_ATTRIBUTE1,
     NULL, --USER_ATTRIBUTE2,
     NULL, --USER_ATTRIBUTE3,
     NULL, --USER_ATTRIBUTE4,
     NULL, --USER_ATTRIBUTE5,
     NULL,			--REQUEST_ID,
     NULL, 			--OPERATION_CODE
     NULL,			--ERROR_CODE,
     'READY',
     BUSINESS_GROUP_ID
   from EDW_ORGA_BUSINESS_GRP_LCV@apps_to_apps
   where last_update_date between g_push_date_range1 and g_push_date_range2
   or (last_update_date is null);

Line: 756


   l_rows_inserted := nvl(sql%rowcount,0);

Line: 759


   edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
         ' rows into the EDW_ORGA_BUSINESS_GRP_LSTG staging table');

Line: 793


 l_rows_inserted        Number := 0;

Line: 817


   Insert Into EDW_ORGA_BUSINESS_GRP_LSTG@EDW_APPS_TO_WH(
     ALL_FK,
     ALL_FK_KEY,
     BUSINESS_GROUP_DP,
     BUSINESS_GROUP_PK,
     ROW_ID,
     DATE_FROM,
     DATE_TO,
     INSTANCE,
     INT_EXT_FLAG,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     NAME,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     REQUEST_ID,
     OPERATION_CODE,
     ERROR_CODE,
     COLLECTION_STATUS,
/* New change by HRI */
     BUSINESS_GROUP_ID,
     COST_ALLOCATION,
     LEGISLATION)
   select
     nvl(ALL_FK, 'NA_EDW'),
     NULL,		--ALL_FK_KEY
     BUSINESS_GROUP_DP,
     BUSINESS_GROUP_PK,
     NULL,		--ROW_ID,
     DATE_FROM,
     DATE_TO,
     INSTANCE,			--bis_edw_instance.get_code,
     INT_EXT_FLAG,
     sysdate,
     sysdate,			--CREATION_DATE,
     NAME,
     ORG_CODE,
     ORG_TYPE,
     PRIMARY_CST_MTHD,
     NULL, --USER_ATTRIBUTE1,
     NULL, --USER_ATTRIBUTE2,
     NULL, --USER_ATTRIBUTE3,
     NULL, --USER_ATTRIBUTE4,
     NULL, --USER_ATTRIBUTE5,
     NULL,			--REQUEST_ID,
     NULL, 			--OPERATION_CODE
     NULL,			--ERROR_ID,
     'READY',
     BUSINESS_GROUP_ID,
     COST_ALLOCATION_FLEXFIELD,
     LEGISLATION
   from EDW_ORGA_BUSINESS_GRP_LCV@apps_to_apps
   where last_update_date between g_push_date_range1 and g_push_date_range2
   or (last_update_date is null);

Line: 878


   l_rows_inserted := nvl(sql%rowcount,0);

Line: 881


   edw_log.put_line( 'Inserted ' || to_char(l_rows_inserted) ||
         ' rows into the EDW_ORGA_BUSINESS_GRP_LSTG staging table');

Line: 906


/* New Levels inserted by HRI */
/******************************/

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

  FOR v_push_to_level IN 1..g_number_of_levels LOOP

    edw_log.put_line('Starting Push_EDW_ORGA_TREE' || p_tree || '_LVL' ||
                     v_push_to_level || '_LSTG');

Line: 923


        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: 1032


/* 8 Levels inserted                             */
/*************************************************/

   edw_log.put_line( 'About to call Org Tree 1 routine' );