APPS.PN_EXP_TO_CAD_ITF SQL Statements

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

Line: 19

    p_last_update_from                      VARCHAR2   ,
    p_last_update_to                        VARCHAR2   ,
    p_as_of_date                            VARCHAR2 DEFAULT NULL
      )

  IS
     l_as_of_date DATE := pnp_util_func.get_as_of_date(fnd_date.canonical_to_date(p_as_of_date));

Line: 36

          p_last_update_from => p_last_update_from   ,
          p_last_update_to   => p_last_update_to     ,
          p_as_of_date       => l_as_of_date
        );

Line: 56

          p_last_update_from   ,
          p_last_update_to     ,
          p_as_of_date
        );

Line: 89

    p_last_update_from      IN VARCHAR2   ,
    p_last_update_to        IN VARCHAR2   ,
    p_as_of_date            IN VARCHAR2
  )


  IS

    l_last_update_from        DATE := fnd_date.canonical_to_date(NVL(p_last_update_from,'0001/01/01:00:00:00'));

Line: 98

    l_last_update_to          DATE := fnd_date.canonical_to_date(NVL(p_last_update_to,'4712/12/31:00:00:00'));

Line: 105

    insert_update             VARCHAR2(6);

Line: 106

    FAIL_ON_UPDATE            EXCEPTION;

Line: 120

    v_last_update_date                      PN_LOCATIONS.LAST_UPDATE_DATE%TYPE;

Line: 144

    v_last_update_login                     PN_LOCATIONS.LAST_UPDATE_LOGIN%TYPE;

Line: 145

    v_last_updated_by                       PN_LOCATIONS.LAST_UPDATED_BY%TYPE;

Line: 207

      'SELECT
                       LOCATION_ID,
                       LOCATION_CODE,
                       LOCATION_TYPE_LOOKUP_CODE,
                       SPACE_TYPE_LOOKUP_CODE,
                       PARENT_LOCATION_ID,
                       LEASE_OR_OWNED,
                       BUILDING,
                       FLOOR,
                       OFFICE,
                       ADDRESS_LINE1,
                       ADDRESS_LINE2,
                       ADDRESS_LINE3,
                       ADDRESS_LINE4,
                       COUNTY,
                       CITY,
                       STATE,
                       PROVINCE,
                       ZIP_CODE,
                       COUNTRY,
                       ADDRESS_STYLE,
                       MAX_CAPACITY,
                       OPTIMUM_CAPACITY,
                       RENTABLE_AREA,
                       USABLE_AREA,
                       ALLOCATE_COST_CENTER_CODE,
                       UOM_CODE,
                       L.ATTRIBUTE_CATEGORY,
                       L.ATTRIBUTE1,
                       L.ATTRIBUTE2,
                       L.ATTRIBUTE3,
                       L.ATTRIBUTE4,
                       L.ATTRIBUTE5,
                       L.ATTRIBUTE6,
                       L.ATTRIBUTE7,
                       L.ATTRIBUTE8,
                       L.ATTRIBUTE9,
                       L.ATTRIBUTE10,
                       L.ATTRIBUTE11,
                       L.ATTRIBUTE12,
                       L.ATTRIBUTE13,
                       L.ATTRIBUTE14,
                       L.ATTRIBUTE15,
                       A.ATTRIBUTE_CATEGORY,
                       A.ATTRIBUTE1,
                       A.ATTRIBUTE2,
                       A.ATTRIBUTE3,
                       A.ATTRIBUTE4,
                       A.ATTRIBUTE5,
                       A.ATTRIBUTE6,
                       A.ATTRIBUTE7,
                       A.ATTRIBUTE8,
                       A.ATTRIBUTE9,
                       A.ATTRIBUTE10,
                       A.ATTRIBUTE11,
                       A.ATTRIBUTE12,
                       A.ATTRIBUTE13,
                       A.ATTRIBUTE14,
                       A.ATTRIBUTE15,
                       L.SOURCE,
                       L.GROSS_AREA,
                       L.ASSIGNABLE_AREA,
                       L.CLASS,
                       L.STATUS_TYPE,
                       L.SUITE,
                       L.COMMON_AREA,
                       L.COMMON_AREA_FLAG,
                       L.FUNCTION_TYPE_LOOKUP_CODE, ---BUG#2198182
                       L.ACTIVE_START_DATE,
                       L.ACTIVE_END_DATE,
                       L.STANDARD_TYPE_LOOKUP_CODE ---BUG#5359173
                     FROM
                       PN_LOCATIONS L,
                       PN_ADDRESSES_ALL A
                     WHERE
                       L.ADDRESS_ID = A.ADDRESS_ID (+)
                       AND L.LAST_UPDATE_DATE >= TRUNC(:date_from )
                       AND  L.LAST_UPDATE_DATE  <= TRUNC(:date_to) ';

Line: 319

            (l_cursor,'date_from',l_last_update_from );

Line: 322

            (l_cursor,'date_to',l_last_update_to );

Line: 493

        SELECT  1
        INTO    exists_in_itf
        FROM    PN_LOCATIONS_ITF
        WHERE   location_id         =  v_location_id
        AND     active_start_date   =  v_active_start_date
        AND     active_END_date     =  v_active_end_date;

Line: 519

      INSERT INTO PN_LOCATIONS_ITF (
        BATCH_NAME,
        ENTRY_TYPE,
        LOCATION_ID,
        LOCATION_CODE,
        LOCATION_TYPE_LOOKUP_CODE,
        SPACE_TYPE_LOOKUP_CODE,
        LAST_UPDATE_DATE,
        CREATION_DATE,
        PARENT_LOCATION_ID,
        LEASE_OR_OWNED,
        BUILDING,
        FLOOR,
        OFFICE,
        ADDRESS_LINE1,
        ADDRESS_LINE2,
        ADDRESS_LINE3,
        ADDRESS_LINE4,
        COUNTY,
        CITY,
        STATE,
        PROVINCE,
        ZIP_CODE,
        COUNTRY,
        ADDRESS_STYLE,
        MAX_CAPACITY,
        OPTIMUM_CAPACITY,
        RENTABLE_AREA,
        USABLE_AREA,
        ALLOCATE_COST_CENTER_CODE,
        UOM_CODE,
        LAST_UPDATE_LOGIN,
        LAST_UPDATED_BY,
        CREATED_BY,
        ATTRIBUTE_CATEGORY,
        ATTRIBUTE1,
        ATTRIBUTE2,
        ATTRIBUTE3,
        ATTRIBUTE4,
        ATTRIBUTE5,
        ATTRIBUTE6,
        ATTRIBUTE7,
        ATTRIBUTE8,
        ATTRIBUTE9,
        ATTRIBUTE10,
        ATTRIBUTE11,
        ATTRIBUTE12,
        ATTRIBUTE13,
        ATTRIBUTE14,
        ATTRIBUTE15,
        ADDR_ATTRIBUTE_CATEGORY,
        ADDR_ATTRIBUTE1,
        ADDR_ATTRIBUTE2,
        ADDR_ATTRIBUTE3,
        ADDR_ATTRIBUTE4,
        ADDR_ATTRIBUTE5,
        ADDR_ATTRIBUTE6,
        ADDR_ATTRIBUTE7,
        ADDR_ATTRIBUTE8,
        ADDR_ATTRIBUTE9,
        ADDR_ATTRIBUTE10,
        ADDR_ATTRIBUTE11,
        ADDR_ATTRIBUTE12,
        ADDR_ATTRIBUTE13,
        ADDR_ATTRIBUTE14,
        ADDR_ATTRIBUTE15,
        SOURCE,
        GROSS_AREA,
        ASSIGNABLE_AREA,
        CLASS,
        STATUS_TYPE,
        SUITE,
        COMMON_AREA,
        COMMON_AREA_FLAG,
        FUNCTION_TYPE_LOOKUP_CODE,
        STANDARD_TYPE_LOOKUP_CODE, ---BUG#5359173
        ACTIVE_START_DATE,
        ACTIVE_END_DATE
      )
      VALUES (
        p_BATCH_NAME,
        decode(v_SOURCE, NULL, 'A', 'U'),
        v_LOCATION_ID,
        v_LOCATION_CODE,
        v_LOCATION_TYPE_LOOKUP_CODE,
        v_SPACE_TYPE_LOOKUP_CODE,
        SYSDATE,
        SYSDATE,
        v_PARENT_LOCATION_ID,
        v_LEASE_OR_OWNED,
        v_BUILDING,
        v_FLOOR,
        v_OFFICE,
        v_ADDRESS_LINE1,
        v_ADDRESS_LINE2,
        v_ADDRESS_LINE3,
        v_ADDRESS_LINE4,
        v_COUNTY,
        v_CITY,
        v_STATE,
        v_PROVINCE,
        v_ZIP_CODE,
        v_COUNTRY,
        v_ADDRESS_STYLE,
        to_NUMBER(v_MAX_CAPACITY),
        to_NUMBER(v_OPTIMUM_CAPACITY),
        to_NUMBER(v_RENTABLE_AREA),
        to_NUMBER(v_USABLE_AREA),
        v_ALLOCATE_COST_CENTER_CODE,
        v_UOM_CODE,
        fnd_profile.value('CONC_LOGIN_ID'),
        fnd_profile.value('USER_ID'),
        fnd_profile.value('USER_ID'),
        v_ATTRIBUTE_CATEGORY,
        v_ATTRIBUTE1,
        v_ATTRIBUTE2,
        v_ATTRIBUTE3,
        v_ATTRIBUTE4,
        v_ATTRIBUTE5,
        v_ATTRIBUTE6,
        v_ATTRIBUTE7,
        v_ATTRIBUTE8,
        v_ATTRIBUTE9,
        v_ATTRIBUTE10,
        v_ATTRIBUTE11,
        v_ATTRIBUTE12,
        v_ATTRIBUTE13,
        v_ATTRIBUTE14,
        v_ATTRIBUTE15,
        v_ADDR_ATTRIBUTE_CATEGORY,
        v_ADDR_ATTRIBUTE1,
        v_ADDR_ATTRIBUTE2,
        v_ADDR_ATTRIBUTE3,
        v_ADDR_ATTRIBUTE4,
        v_ADDR_ATTRIBUTE5,
        v_ADDR_ATTRIBUTE6,
        v_ADDR_ATTRIBUTE7,
        v_ADDR_ATTRIBUTE8,
        v_ADDR_ATTRIBUTE9,
        v_ADDR_ATTRIBUTE10,
        v_ADDR_ATTRIBUTE11,
        v_ADDR_ATTRIBUTE12,
        v_ADDR_ATTRIBUTE13,
        v_ADDR_ATTRIBUTE14,
        v_ADDR_ATTRIBUTE15,
        NVL(v_source,'PN'),
        v_GROSS_AREA,
        v_ASSIGNABLE_AREA,
        v_CLASS,
        v_STATUS_TYPE,
        v_SUITE,
        v_COMMON_AREA,
        v_COMMON_AREA_FLAG,
        v_function_type_lookup_code,
        v_standard_type_lookup_code,    ---BUG#5359173
        v_active_start_date,
        v_active_end_date
      );

Line: 678

        Insert_Update := 'Insert';

Line: 687

        UPDATE  PN_LOCATIONS_ITF SET
        BATCH_NAME = p_batch_name,
        ENTRY_TYPE = 'U',
        LOCATION_ID = v_location_id,
        LOCATION_CODE = v_LOCATION_CODE,
        LOCATION_TYPE_LOOKUP_CODE = v_location_type_lookup_code,
        SPACE_TYPE_LOOKUP_CODE = v_location_type_lookup_code,
        LAST_UPDATE_DATE = SYSDATE,
        CREATION_DATE = SYSDATE,
        PARENT_LOCATION_ID = v_parent_location_id,
        LEASE_OR_OWNED = v_lease_or_owned,
        BUILDING = v_building,
        FLOOR = v_floor,
        OFFICE = v_office,
        ADDRESS_LINE1 = v_address_line1,
        ADDRESS_LINE2 = v_address_line2,
        ADDRESS_LINE3 = v_address_line3,
        ADDRESS_LINE4 = v_address_line4,
        COUNTY = v_county,
        CITY = v_city,
        STATE = v_state,
        PROVINCE = v_province,
        ZIP_CODE = v_zip_code,
        COUNTRY = v_country,
        ADDRESS_STYLE = v_address_style,
        MAX_CAPACITY = v_max_capacity,
        OPTIMUM_CAPACITY = v_optimum_capacity,
        RENTABLE_AREA = v_rentable_area,
        USABLE_AREA = v_usable_area,
        ALLOCATE_COST_CENTER_CODE = v_allocate_cost_center_code,
        UOM_CODE = v_uom_code,
        LAST_UPDATE_LOGIN = fnd_profile.value('CONC_LOGIN_ID'),
        LAST_UPDATED_BY = fnd_profile.value('USER_ID'),
        CREATED_BY = fnd_profile.value('USER_ID'),
        ATTRIBUTE_CATEGORY = v_attribute_category,
        ATTRIBUTE1 = v_attribute1,
        ATTRIBUTE2 = v_attribute2,
        ATTRIBUTE3 = v_attribute3,
        ATTRIBUTE4 = v_attribute4,
        ATTRIBUTE5 = v_attribute5,
        ATTRIBUTE6 = v_attribute6,
        ATTRIBUTE7 = v_attribute7,
        ATTRIBUTE8 = v_attribute8,
        ATTRIBUTE9 = v_attribute9,
        ATTRIBUTE10 = v_attribute10,
        ATTRIBUTE11 = v_attribute11,
        ATTRIBUTE12 = v_attribute12,
        ATTRIBUTE13 = v_attribute13,
        ATTRIBUTE14 = v_attribute14,
        ATTRIBUTE15 = v_attribute15,
        ADDR_ATTRIBUTE_CATEGORY = v_addr_attribute_category,
        ADDR_ATTRIBUTE1 = v_addr_attribute1,
        ADDR_ATTRIBUTE2 = v_addr_attribute2,
        ADDR_ATTRIBUTE3 = v_addr_attribute3,
        ADDR_ATTRIBUTE4 = v_addr_attribute4,
        ADDR_ATTRIBUTE5 = v_addr_attribute5,
        ADDR_ATTRIBUTE6 = v_addr_attribute6,
        ADDR_ATTRIBUTE7 = v_addr_attribute7,
        ADDR_ATTRIBUTE8 = v_addr_attribute8,
        ADDR_ATTRIBUTE9 = v_addr_attribute9,
        ADDR_ATTRIBUTE10 = v_addr_attribute10,
        ADDR_ATTRIBUTE11 = v_addr_attribute11,
        ADDR_ATTRIBUTE12 = v_addr_attribute12,
        ADDR_ATTRIBUTE13 = v_addr_attribute13,
        ADDR_ATTRIBUTE14 = v_addr_attribute14,
        ADDR_ATTRIBUTE15 = v_addr_attribute15,
        -- SOURCE = NVL(v_Source, 'PN'),
        GROSS_AREA = v_gross_area,
        ASSIGNABLE_AREA = v_assignable_area,
        CLASS = v_class,
        STATUS_TYPE = v_status_type,
        SUITE = v_suite,
        COMMON_AREA = v_common_area,
        COMMON_AREA_FLAG = v_common_area_flag,
        FUNCTION_TYPE_LOOKUP_CODE = v_function_type_lookup_code,   --BUG#2198182
        STANDARD_TYPE_LOOKUP_CODE = v_standard_type_lookup_code  ---BUG#5359173
        WHERE LOCATION_ID = v_location_id
        AND   active_start_date = v_active_start_date
        AND   active_end_date   = v_active_end_date ;

Line: 768

           RAISE FAIL_ON_UPDATE;

Line: 771

        Insert_Update := 'Update';

Line: 799

        ', '                     || Insert_Update               ||
        ', Location Code: '      || v_Location_Code             ||
        ', Location Id: '        || v_Location_Id
       );

Line: 860

    p_last_update_from      IN              VARCHAR2   ,
    p_last_update_to        IN              VARCHAR2   ,
    p_as_of_date            IN              VARCHAR2
  )

  IS

    l_last_update_from        DATE := NVL(fnd_date.canonical_to_date(p_last_update_from),
                                          fnd_date.canonical_to_date('0001/01/01:00:00:00'));

Line: 869

    l_last_update_to          DATE := NVL(fnd_date.canonical_to_date(p_last_update_to),
                                          fnd_date.canonical_to_date('4712/12/31:00:00:00'));

Line: 874

    Insert_Update                           VARCHAR2(6);

Line: 875

    FAIL_ON_UPDATE                          EXCEPTION;

Line: 888

    v_LAST_UPDATE_DATE                      DATE;

Line: 889

    v_LAST_UPDATE_LOGIN                     NUMBER;

Line: 892

    v_LAST_UPDATED_BY                       NUMBER;

Line: 938

    'SELECT
                       SP.EMP_SPACE_ASSIGN_ID,
                       SP.LOCATION_ID,
                       SP.PERSON_ID,
                       SP.COST_CENTER_CODE,
                       SP.ALLOCATED_AREA,
                       SP.ATTRIBUTE_CATEGORY,
                       SP.ATTRIBUTE1,
                       SP.ATTRIBUTE2,
                       SP.ATTRIBUTE3,
                       SP.ATTRIBUTE4,
                       SP.ATTRIBUTE5,
                       SP.ATTRIBUTE6,
                       SP.ATTRIBUTE7,
                       SP.ATTRIBUTE8,
                       SP.ATTRIBUTE9,
                       SP.ATTRIBUTE10,
                       SP.ATTRIBUTE11,
                       SP.ATTRIBUTE12,
                       SP.ATTRIBUTE13,
                       SP.ATTRIBUTE14,
                       SP.ATTRIBUTE15,
                       LO.LOCATION_TYPE_LOOKUP_CODE,
                       LO.LOCATION_CODE,
                       SP.SOURCE,
                       SP.EMP_ASSIGN_START_DATE,
                       SP.EMP_ASSIGN_END_DATE,
                       SP.UTILIZED_AREA,
                       SP.PROJECT_ID,
                       SP.TASK_ID
                     FROM
                       PN_LOCATIONS     LO,
                       PN_SPACE_ASSIGN_EMP_ALL  SP
                     WHERE
                      LO.location_id  =  SP.location_id
                     AND TRUNC(:as_of_date) between
                          SP.EMP_ASSIGN_START_DATE
                     AND NVL(SP.EMP_ASSIGN_END_DATE,TRUNC(:v_date))
                     AND SP.LAST_UPDATE_DATE >= TRUNC(:date_from )
                     AND  SP.LAST_UPDATE_DATE  <= TRUNC(:date_to)';

Line: 1018

            (l_cursor,'date_from',l_last_update_from );

Line: 1021

            (l_cursor,'date_to',l_last_update_to );

Line: 1112

        SELECT  1
        INTO    exists_in_itf
        FROM    PN_EMP_SPACE_ASSIGN_ITF
        WHERE   emp_space_assign_id         =  V_EMP_SPACE_ASSIGN_ID;

Line: 1130

      pnp_debug_pkg.log(' bfore insert ');

Line: 1132

      INSERT INTO PN_EMP_SPACE_ASSIGN_ITF (
        BATCH_NAME,
        ENTRY_TYPE,
        EMP_SPACE_ASSIGN_ID,
        LOCATION_ID,
        employee_id,
        COST_CENTER_CODE,
        ALLOCATED_AREA,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        ATTRIBUTE_CATEGORY,
        ATTRIBUTE1,
        ATTRIBUTE2,
        ATTRIBUTE3,
        ATTRIBUTE4,
        ATTRIBUTE5,
        ATTRIBUTE6,
        ATTRIBUTE7,
        ATTRIBUTE8,
        ATTRIBUTE9,
        ATTRIBUTE10,
        ATTRIBUTE11,
        ATTRIBUTE12,
        ATTRIBUTE13,
        ATTRIBUTE14,
        ATTRIBUTE15,
        SOURCE,
        EMP_ASSIGN_START_DATE,
        EMP_ASSIGN_END_DATE,
        UTILIZED_AREA,
        PROJECT_ID,
        TASK_ID
      )
      VALUES (
        p_BATCH_NAME,
        decode(v_SOURCE, NULL, 'A', 'U'),
        v_EMP_SPACE_ASSIGN_ID,
        v_LOCATION_ID,
        v_person_id,
        v_COST_CENTER_CODE,
        v_ALLOCATED_AREA,
        SYSDATE,
        fnd_profile.value('CONC_LOGIN_ID'),
        fnd_profile.value('USER_ID'),
        SYSDATE,
        fnd_profile.value('USER_ID'),
        v_ATTRIBUTE_CATEGORY,
        v_ATTRIBUTE1,
        v_ATTRIBUTE2,
        v_ATTRIBUTE3,
        v_ATTRIBUTE4,
        v_ATTRIBUTE5,
        v_ATTRIBUTE6,
        v_ATTRIBUTE7,
        v_ATTRIBUTE8,
        v_ATTRIBUTE9,
        v_ATTRIBUTE10,
        v_ATTRIBUTE11,
        v_ATTRIBUTE12,
        v_ATTRIBUTE13,
        v_ATTRIBUTE14,
        v_ATTRIBUTE15,
        NVL(v_source,'PN'),
        v_EMP_ASSIGN_START_DATE,
        v_EMP_ASSIGN_END_DATE,
        v_UTILIZED_AREA,
        v_PROJECT_ID,
        v_TASK_ID
      );

Line: 1205

        Insert_Update := 'Insert';

Line: 1207

        PNP_DEBUG_PKG.log('Inserted Row ' || v_Counter);

Line: 1216

      UPDATE PN_EMP_SPACE_ASSIGN_ITF SET
        BATCH_NAME = p_batch_name,
        ENTRY_TYPE = 'U',
        EMP_SPACE_ASSIGN_ID = v_emp_space_assign_id,
        LOCATION_ID = v_location_id,
        employee_id = v_person_id,
        COST_CENTER_CODE = v_cost_center_code,
        ALLOCATED_AREA = v_allocated_area,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = fnd_profile.value('CONC_LOGIN_ID'),
        CREATED_BY = fnd_profile.value('USER_ID'),
        CREATION_DATE = SYSDATE,
        LAST_UPDATED_BY = fnd_profile.value('USER_ID'),
        ATTRIBUTE_CATEGORY = v_attribute_category,
        ATTRIBUTE1 = v_attribute1,
        ATTRIBUTE2 = v_attribute2,
        ATTRIBUTE3 = v_attribute3,
        ATTRIBUTE4 = v_attribute4,
        ATTRIBUTE5 = v_attribute5,
        ATTRIBUTE6 = v_attribute6,
        ATTRIBUTE7 = v_attribute7,
        ATTRIBUTE8 = v_attribute8,
        ATTRIBUTE9 = v_attribute9,
        ATTRIBUTE10 = v_attribute10,
        ATTRIBUTE11 = v_attribute11,
        ATTRIBUTE12 = v_attribute12,
        ATTRIBUTE13 = v_attribute13,
        ATTRIBUTE14 = v_attribute14,
        ATTRIBUTE15 = v_attribute15,
        EMP_ASSIGN_START_DATE = v_EMP_ASSIGN_START_DATE,
        EMP_ASSIGN_END_DATE = v_EMP_ASSIGN_END_DATE,
        UTILIZED_AREA = v_UTILIZED_AREA,
        PROJECT_ID = v_PROJECT_ID,
        TASK_ID=v_TASK_ID
        WHERE EMP_SPACE_ASSIGN_ID = v_emp_space_assign_id;

Line: 1253

          RAISE FAIL_ON_UPDATE;

Line: 1256

        Insert_Update := 'Update';

Line: 1258

        PNP_DEBUG_PKG.log('Updated Row ' || v_Counter);

Line: 1283

        ', '                     || Insert_Update               ||
        ', Emp Space Assign Id: '|| v_emp_space_assign_id       ||
        ', Location Id: '        || v_Location_Id               ||
        ', Location Code: '      || v_Location_Code
       );