DBA Data[Home] [Help]

APPS.PNRX_MILESTONES SQL Statements

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

Line: 95

  V_LAST_UPDATE_DATE                                          DATE;
Line: 96

  V_LAST_UPDATED_BY                                           NUMBER;
Line: 97

  V_LAST_UPDATE_LOGIN                                         NUMBER;
Line: 126

  'SELECT
  distinct
  ten.location_id                                  LOCATION_ID,
  NVL(ten.occupancy_date, ten.estimated_occupancy_date)    OCCUPANCY_DATE,
  les.lease_id                                     LEASE_ID,
  les.lease_name                                   LEASE_NAME,
  les.lease_number                                 LEASE_NUMBER,
  les.lease_commencement_date                      LEASE_COMMENCEMENT_DATE,
  les.lease_termination_date                       LEASE_TERMINATION_DATE,
  les.lease_execution_date                         LEASE_EXECUTION_DATE,
  (TRUNC(les.lease_termination_date)- TRUNC(les.lease_commencement_date)+1) LEASE_TERM,
  fnd4.meaning                                     LEASE_CLASS,
  les.user_responsible                             LEASE_RESPONSIBLE_USER,
  fnd1.meaning                                     LEASE_STATUS,
  fnd6.meaning                                     LEASE_TYPE,
  ten.estimated_occupancy_date                     ESTIMATED_OCCUPANCY_DATE,
  ten.occupancy_date                               OCCUPANCY_DATE,
  ten.attribute_category                           ATTRIBUTE_CATEGORY,
  ten.attribute1                                   ATTRIBUTE1,
  ten.attribute2                                   ATTRIBUTE2,
  ten.attribute3                                   ATTRIBUTE3,
  ten.attribute4                                   ATTRIBUTE4,
  ten.attribute5                                   ATTRIBUTE5,
  ten.attribute6                                   ATTRIBUTE6,
  ten.attribute7                                   ATTRIBUTE7,
  ten.attribute8                                   ATTRIBUTE8,
  ten.attribute9                                   ATTRIBUTE9,
  ten.attribute10                                  ATTRIBUTE10,
  ten.attribute11                                  ATTRIBUTE11,
  ten.attribute12                                  ATTRIBUTE12,
  ten.attribute13                                  ATTRIBUTE13,
  ten.attribute14                                  ATTRIBUTE14,
  ten.attribute15                                  ATTRIBUTE15,
  les.last_update_date                             LAST_UPDATE_DATE,
  les.last_updated_by                              LAST_UPDATED_BY,
  les.last_update_login                            LAST_UPDATE_LOGIN,
  les.creation_date                                CREATION_DATE,
  les.created_by                                   CREATED_BY,
  fnd5.meaning                                     LOCATION_TYPE,
  NVL(NVL(loc.building, loc.FLOOR), loc.office)    LOCATION_NAME,
  loc.location_code                                LOCATION_CODE,
  fnd7.meaning                                     USAGE_TYPE,
  pa.address_line1||pa.address_line2||pa.address_line3||pa.address_line4||pa.county||pa.city||pa.state||pa.province||pa.zip_code||pa.country               ADDRESS,
  pa.county                                        COUNTY,
  pa.city                                          CITY,
  pa.state                                         STATE,
  pa.province                                      PROVINCE,
  pa.zip_code                                      ZIP_CODE,
  pa.country                                       COUNTRY,
  DECODE (location_type_lookup_code,'||''''||'OFFICE'||''''||',loc.rentable_area,
          '||''''||'FLOOR'||''''||',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id),
          '||''''||'PARCEL'||''''||',PNP_UTIL_FUNC.get_floor_rentable_area(loc.location_id),
          '||''''||'BUILDING'||''''||',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id),
          '||''''||'LAND'||''''||',PNP_UTIL_FUNC.get_building_rentable_area(loc.location_id),
                rentable_area)                     RENTABLE_AREA,
  DECODE (location_type_lookup_code,'||''''||'OFFICE'||''''||',loc.usable_area,
          '||''''||'FLOOR'||''''||',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id),
          '||''''||'PARCEL'||''''||',PNP_UTIL_FUNC.get_floor_usable_area(loc.location_id),
          '||''''||'BUILDING'||''''||',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id),
          '||''''||'LAND'||''''||',PNP_UTIL_FUNC.get_building_usable_area(loc.location_id),
                usable_area)                       USABLE_AREA,
  loc.gross_area                                   GROSS_AREA,
  fnd2.meaning                                     TENURE,
  mil.lease_milestone_id                           LEASE_MILESTONE_ID,
  fnd3.meaning                                     MILESTONE_TYPE,
  fnd.user_name                                    RESPONSIBLE_USER,
  mil.milestone_date                               ACTION_DATE,
  mil.milestone_date-mil.lead_days                 MILESTONE_DATE,
  mil.action_taken                                 ACTION_TAKEN
  FROM pn_leases_v                les,
       pn_tenancies               ten,
       pn_locations               loc,
       pn_lease_milestones        mil,
       fnd_user                   fnd,
       pn_addresses               pa,
       fnd_lookups                fnd1,
       fnd_lookups                fnd2,
       fnd_lookups                fnd3,
       fnd_lookups                fnd4,
       fnd_lookups                fnd5,
       fnd_lookups                fnd6,
       fnd_lookups                fnd7
  WHERE ten.lease_id      = les.lease_id
    AND ten.location_id   = loc.location_id
    AND ( (( loc.active_start_date BETWEEN  les.lease_commencement_date AND  les.lease_Termination_date) OR
    (loc.active_end_date BETWEEN  les.lease_commencement_date AND  les.lease_Termination_date)) OR ( loc.active_start_date < les.lease_commencement_date AND loc.active_end_date
     > les.lease_Termination_date))
    AND mil.lease_id      = les.lease_id
    AND mil.user_id       = fnd.user_id
    AND ten.primary_flag  ='||''''||'Y'||''''||'
    AND les.status = fnd1.lookup_code (+)
    AND loc.lease_or_owned = fnd2.lookup_code (+)
    AND fnd3.lookup_code   = mil.milestone_type_code
    AND fnd4.lookup_code (+)  = les.lease_class_code
    AND fnd5.lookup_code   = loc.location_type_lookup_code
    AND fnd6.lookup_code   = les.lease_type_code
    AND fnd7.lookup_code   = ten.tenancy_usage_lookup_code
    AND fnd1.lookup_type (+) = '||''''||'PN_LEASE_STATUS_TYPE'||''''||'
    AND fnd2.lookup_type (+) = '||''''||'PN_LEASED_OR_OWNED'||''''||'
    AND fnd3.lookup_type = '||''''||'PN_MILESTONES_TYPE'||''''||'
    AND fnd4.lookup_type (+) = '||''''||'PN_LEASE_CLASS'||''''||'
    AND fnd5.lookup_type = '||''''||'PN_LOCATION_TYPE'||''''||'
    AND fnd6.lookup_type = '||''''||'PN_LEASE_TYPE'||''''||'
    AND fnd7.lookup_type = '||''''||'PN_TENANCY_USAGE_TYPE'||''''||'
    AND pa.address_id(+)  = loc.address_id';
Line: 454

  dbms_sql.define_column (l_cursor, 32,V_LAST_UPDATE_DATE);
Line: 455

  dbms_sql.define_column (l_cursor, 33,V_LAST_UPDATED_BY);
Line: 456

  dbms_sql.define_column (l_cursor, 34,V_LAST_UPDATE_LOGIN);
Line: 519

        dbms_sql.column_value (l_cursor, 32,V_LAST_UPDATE_DATE);
Line: 520

        dbms_sql.column_value (l_cursor, 33,V_LAST_UPDATED_BY);
Line: 521

        dbms_sql.column_value (l_cursor, 34,V_LAST_UPDATE_LOGIN);
Line: 548

        PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_les: insert(+)');
Line: 550

        INSERT INTO pn_milestones_itf
        (LEASE_MILESTONE_ID                         ,
        MILESTONE_TYPE_CODE                         ,
        RESPONSIBLE_USER                            ,
        ACTION_DATE                                 ,
        NOTIFICATION_DATE                           ,
        ACTION_TAKEN                                ,
        LEASE_ID                                    ,
        LEASE_NAME                                  ,
        LEASE_NUMBER                                ,
        LEASE_COMMENCEMENT_DATE                     ,
        LEASE_TERMINATION_DATE                      ,
        LEASE_EXECUTION_DATE                        ,
        LEASE_TERM                                  ,
        LEASE_CLASS                                 ,
        LEASE_RESPONSIBLE_USER                      ,
        LEASE_STATUS                                ,
        LEASE_TYPE                                  ,
        ESTIMATED_OCCUPANCY_DATE                    ,
        ACTUAL_OCCUPANCY_DATE                       ,
        LOCATION_ID                                 ,
        LOCATION_TYPE                               ,
        LOCATION_NAME                               ,
        LOCATION_CODE                               ,
        SPACE_TYPE                                  ,
        REGION                                      ,
        PROPERTY_NAME                               ,
        BUILDING_OR_LAND_NAME                       ,
        FLOOR_OR_PARCEL_NAME                        ,
        OFFICE_OR_SECTION_NAME                      ,
        ADDRESS                                     ,
        COUNTY                                      ,
        CITY                                        ,
        STATE                                       ,
        PROVINCE                                    ,
        ZIP_CODE                                    ,
        COUNTRY                                     ,
        GROSS_AREA                                  ,
        RENTABLE_AREA                               ,
        USABLE_AREA                                 ,
        TENURE                                      ,
        TEN_ATTRIBUTE_CATEGORY                      ,
        TEN_ATTRIBUTE1                              ,
        TEN_ATTRIBUTE2                              ,
        TEN_ATTRIBUTE3                              ,
        TEN_ATTRIBUTE4                              ,
        TEN_ATTRIBUTE5                              ,
        TEN_ATTRIBUTE6                              ,
        TEN_ATTRIBUTE7                              ,
        TEN_ATTRIBUTE8                              ,
        TEN_ATTRIBUTE9                              ,
        TEN_ATTRIBUTE10                             ,
        TEN_ATTRIBUTE11                             ,
        TEN_ATTRIBUTE12                             ,
        TEN_ATTRIBUTE13                             ,
        TEN_ATTRIBUTE14                             ,
        TEN_ATTRIBUTE15                             ,
        LAST_UPDATE_DATE                            ,
        LAST_UPDATED_BY                             ,
        LAST_UPDATE_LOGIN                           ,
        CREATION_DATE                               ,
        CREATED_BY                                  ,
        REQUEST_ID                                  )
        VALUES
        (V_LEASE_MILESTONE_ID                       ,
        V_MILESTONE_TYPE                            ,
        V_RESPONSIBLE_USER                          ,
        V_ACTION_DATE                               ,
        V_MILESTONE_DATE                            ,
        V_ACTION_TAKEN                              ,
        V_LEASE_ID                                  ,
        V_LEASE_NAME                                ,
        V_LEASE_NUMBER                              ,
        V_LEASE_COM_DATE                            ,
        V_LEASE_TERM_DATE                           ,
        V_LEASE_EXE_DATE                            ,
        V_LEASE_TERM                                ,
        V_LEASE_CLASS                               ,
        V_LEASE_RESP_USER                           ,
        V_LEASE_STATUS                              ,
        V_LEASE_TYPE                                ,
        V_ESTIMATED_OCCUPANCY_DATE                  ,
        V_ACTUAL_OCCUPANCY_DATE                     ,
        V_LOCATION_ID                               ,
        V_LOCATION_TYPE                             ,
        V_LOCATION_NAME                             ,
        V_LOCATION_CODE                             ,
        V_SPACE_TYPE                                ,
        v_code_data.REGION_NAME                     ,
        v_code_data.PROPERTY_NAME                   ,
        v_code_data.BUILDING                        ,
        v_code_data.FLOOR                           ,
        v_code_data.OFFICE                          ,
        V_ADDRESS                                   ,
        V_COUNTY                                    ,
        V_CITY                                      ,
        V_STATE                                     ,
        V_PROVINCE                                  ,
        V_ZIP_CODE                                  ,
        V_COUNTRY                                   ,
        V_GROSS_AREA                                ,
        V_RENTABLE_AREA                             ,
        V_USABLE_AREA                               ,
        V_TENURE                                    ,
        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_LAST_UPDATE_DATE                          ,
        V_LAST_UPDATED_BY                           ,
        V_LAST_UPDATE_LOGIN                         ,
        V_CREATION_DATE                             ,
        V_CREATED_BY                                ,
        l_request_id                                );
Line: 677

        PNP_DEBUG_PKG.put_log_msg('pn_roll_rent_les: insert(-)');