APPS.JTY_TERR_SPARES_PVT SQL Statements

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

Line: 45

    select 'Y'
    into l_migration_complete
    from jtf_terr_types_all
    where terr_type_id = -9
      and created_by = 9
      and rownum < 2;

Line: 80

      insert into jtf_terr_types_all
      ( TERR_TYPE_ID
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , CREATED_BY
      , CREATION_DATE
      , APPLICATION_SHORT_NAME
      , NAME
      , ENABLED_FLAG
      , DESCRIPTION
      , START_DATE_ACTIVE
      , ORG_ID
      , ORIG_SYSTEM_REFERENCE_ID)
      select -9
      , 9
      , sysdate
      , 9
      , sysdate
      , 'CSP'
      , 'Spares Management Return Routing'
      , 'Y'
      , l_description
      , sysdate
      , -3113
      , -1002
      from dual;

Line: 128

      insert into jtf_terr_type_usgs_all
      ( TERR_TYPE_USG_ID
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , CREATED_BY
      , CREATION_DATE
      , TERR_TYPE_ID
      , SOURCE_ID
      , ORG_ID
      )
      select jtf_terr_type_usgs_s.nextval
      , 9
      , sysdate
      , 9
      , sysdate
      , terr_type_id
      , ORIG_SYSTEM_REFERENCE_ID    -- Used this column to temp store source_id
      , org_id
      from jtf_terr_types_all
      where  ORIG_SYSTEM_REFERENCE_ID is not null
        and  terr_type_id = -9
        and  CREATED_BY = 9;

Line: 173

      insert into jtf_type_qtype_usgs_all
       ( TYPE_QTYPE_USG_ID
       , LAST_UPDATED_BY
       , LAST_UPDATE_DATE
       , CREATED_BY
       , CREATION_DATE
       , last_update_login
       , TERR_TYPE_ID
       , QUAL_TYPE_USG_ID
       , ORG_ID)
       select jtf_type_qtype_usgs_s.nextval
           , 9
           , sysdate
           , 9
           , sysdate
           , 9
           , jttu.terr_type_id
           , jqtu.qual_type_usg_id
           , jttu.org_id
       from jtf_terr_type_usgs_all jttu
          , jtf_qual_type_usgs_all jqtu
       where jqtu.source_id = jttu.source_id
       and  jttu.source_id = -1002
       and jttu.terr_type_id = -9
       and jqtu.qual_type_usg_id <> -1005  -- Exclude service account transaction type
       and jttu.created_by = 9;

Line: 221

       insert into jtf_terr_type_qual_all
          (TERR_TYPE_QUAL_ID
           , LAST_UPDATED_BY
           , LAST_UPDATE_DATE
           , CREATED_BY
           , CREATION_DATE
           , QUAL_USG_ID
           , TERR_TYPE_ID
           , ORG_ID)
       select jtf_terr_type_qual_s.nextval
         , 9
         , sysdate
         , 9
         , sysdate
         , jqu.QUAL_USG_ID
         , jttu.terr_type_id
         , jttu.org_id
       from jtf_terr_type_usgs_all jttu
          , jtf_qual_type_usgs_all jqtu
          , jtf_qual_usgs_all jqu
       where jttu.created_by = 9
       and jttu.terr_type_id = -9
       and jttu.source_id = -1002
       and jqu.QUAL_TYPE_USG_ID = jqtu.qual_type_usg_id
       and jqtu.source_id = jttu.source_id
       and jttu.org_id = jqu.org_id
       and jqu.hierarchy_type = 'GEOGRAPHY'
       and jttu.terr_type_id <> -1 ;-- For NA territories this table is not populated

Line: 411

  /* delete and insert all the attributes into the trans table as name - value pair */
  DELETE jty_terr_nvp_trans_gt;

Line: 413

  INSERT INTO jty_terr_nvp_trans_gt (
     attribute_name
    ,num_value
    ,char_value
    ,date_value )
  ( SELECT 'COUNTRY'                  attribute_name
          ,null                       num_value
          ,p_TerrServReq_Rec.COUNTRY  char_value
          ,null                       date_value
    FROM  DUAL
    UNION ALL
    SELECT 'CITY'                  attribute_name
          ,null                    num_value
          ,p_TerrServReq_Rec.CITY  char_value
          ,null                    date_value
    FROM  DUAL
    UNION ALL
    SELECT 'POSTAL_CODE'                  attribute_name
          ,null                           num_value
          ,p_TerrServReq_Rec.POSTAL_CODE  char_value
          ,null                           date_value
    FROM  DUAL
    UNION ALL
    SELECT 'STATE'                  attribute_name
          ,null                     num_value
          ,p_TerrServReq_Rec.STATE  char_value
          ,null                     date_value
    FROM  DUAL
    UNION ALL
    SELECT 'AREA_CODE'                  attribute_name
          ,null                         num_value
          ,p_TerrServReq_Rec.AREA_CODE  char_value
          ,null                         date_value
    FROM  DUAL
    UNION ALL
    SELECT 'COUNTY'                  attribute_name
          ,null                      num_value
          ,p_TerrServReq_Rec.COUNTY  char_value
          ,null                      date_value
    FROM  DUAL
    UNION ALL
    SELECT 'COMP_NAME_RANGE'                  attribute_name
          ,null                               num_value
          ,p_TerrServReq_Rec.COMP_NAME_RANGE  char_value
          ,null                               date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PROVINCE'                  attribute_name
          ,null                        num_value
          ,p_TerrServReq_Rec.PROVINCE  char_value
          ,null                        date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PROBLEM_CODE'                  attribute_name
          ,null                            num_value
          ,p_TerrServReq_Rec.PROBLEM_CODE  char_value
          ,null                            date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SR_CREATION_CHANNEL'                  attribute_name
          ,null                                   num_value
          ,p_TerrServReq_Rec.SR_CREATION_CHANNEL  char_value
          ,null                                   date_value
    FROM  DUAL
    UNION ALL
    SELECT 'VIP_CUSTOMER'                  attribute_name
          ,null                            num_value
          ,p_TerrServReq_Rec.squal_char11  char_value
          ,null                            date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SR_PROBLEM_CODE'               attribute_name
          ,null                            num_value
          ,p_TerrServReq_Rec.squal_char12  char_value
          ,null                            date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SR_CUST_CNTCT_PREF'            attribute_name
          ,null                            num_value
          ,p_TerrServReq_Rec.squal_char13  char_value
          ,null                            date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SR_SRVC_CNTCT_CVG'             attribute_name
          ,null                            num_value
          ,p_TerrServReq_Rec.squal_char21  char_value
          ,null                            date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SR_LANGUAGE'                   attribute_name
          ,null                            num_value
          ,p_TerrServReq_Rec.squal_char20  char_value
          ,null                            date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PARTY_ID'                  attribute_name
          ,p_TerrServReq_Rec.PARTY_ID  num_value
          ,null                        char_value
          ,null                        date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PARTY_SITE_ID'                  attribute_name
          ,p_TerrServReq_Rec.PARTY_SITE_ID  num_value
          ,null                             char_value
          ,null                             date_value
    FROM  DUAL
    UNION ALL
    SELECT 'NUM_OF_EMPLOYEES'                  attribute_name
          ,p_TerrServReq_Rec.NUM_OF_EMPLOYEES  num_value
          ,null                                char_value
          ,null                                date_value
    FROM  DUAL
    UNION ALL
    SELECT 'INCIDENT_TYPE_ID'                  attribute_name
          ,p_TerrServReq_Rec.INCIDENT_TYPE_ID  num_value
          ,null                                char_value
          ,null                                date_value
    FROM  DUAL
    UNION ALL
    SELECT 'INCIDENT_SEVERITY_ID'                  attribute_name
          ,p_TerrServReq_Rec.INCIDENT_SEVERITY_ID  num_value
          ,null                                    char_value
          ,null                                    date_value
    FROM  DUAL
    UNION ALL
    SELECT 'INCIDENT_URGENCY_ID'                  attribute_name
          ,p_TerrServReq_Rec.INCIDENT_URGENCY_ID  num_value
          ,null                                   char_value
          ,null                                   date_value
    FROM  DUAL
    UNION ALL
    SELECT 'INCIDENT_STATUS_ID'                  attribute_name
          ,p_TerrServReq_Rec.INCIDENT_STATUS_ID  num_value
          ,null                                  char_value
          ,null                                  date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PLATFORM_ID'                  attribute_name
          ,p_TerrServReq_Rec.PLATFORM_ID  num_value
          ,null                           char_value
          ,null                           date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SUPPORT_SITE_ID'                  attribute_name
          ,p_TerrServReq_Rec.SUPPORT_SITE_ID  num_value
          ,null                               char_value
          ,null                               date_value
    FROM  DUAL
    UNION ALL
    SELECT 'CUSTOMER_SITE_ID'                  attribute_name
          ,p_TerrServReq_Rec.CUSTOMER_SITE_ID  num_value
          ,null                                char_value
          ,null                                date_value
    FROM  DUAL
    UNION ALL
    SELECT 'INVENTORY_ITEM_ID'                  attribute_name
          ,p_TerrServReq_Rec.INVENTORY_ITEM_ID  num_value
          ,null                                 char_value
          ,null                                 date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SRP_INVENTORY_ITEM_ID'        attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM12  num_value
          ,null                           char_value
          ,null                           date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SRP_ORG_ID'                  attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM13 num_value
          ,null                          char_value
          ,null                          date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SPC_CATEGORY_ID'                  attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM14      num_value
          ,null                               char_value
          ,null                               date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PCS_INVENTORY_ITEM_ID'        attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM15  num_value
          ,null                           char_value
          ,null                           date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PCS_ORG_ID'                  attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM16 num_value
          ,null                          char_value
          ,null                          date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PCS_COMPONENT_ID'            attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM23 num_value
          ,null                          char_value
          ,null                          date_value
    FROM  DUAL
    UNION ALL
    SELECT 'PCS_SUBCOMPONENT_ID'          attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM24  num_value
          ,null                           char_value
          ,null                           date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SR_GROUP_OWNER_ID'               attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM17  num_value
          ,null                           char_value
          ,null                           date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SSI_INVENTORY_ITEM_ID'        attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM18  num_value
          ,null                           char_value
          ,null                           date_value
    FROM  DUAL
    UNION ALL
    SELECT 'SSI_ORG_ID'                  attribute_name
          ,p_TerrServReq_Rec.SQUAL_NUM19 num_value
          ,null                          char_value
          ,null                          date_value
    FROM  DUAL
    UNION ALL
    SELECT 'DAY_OF_WEEK'                    attribute_name
          ,null                             num_value
          --,p_TerrServReq_Rec.DAY_OF_WEEK   char_value
          , DECODE(p_TerrServReq_Rec.DAY_OF_WEEK,FND_API.G_MISS_CHAR,null,
                   p_TerrServReq_Rec.DAY_OF_WEEK)   char_value
          ,null                             date_value
    FROM  DUAL
    UNION ALL
    SELECT 'TIME_OF_DAY'                  attribute_name
          , null                            num_value
          --, p_TerrServReq_Rec.TIME_OF_DAY   char_value
          , DECODE(p_TerrServReq_Rec.TIME_OF_DAY,FND_API.G_MISS_CHAR,null,
                   p_TerrServReq_Rec.TIME_OF_DAY)   char_value
          ,null                             date_value
    FROM  DUAL

  );