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.OKL_LEASEAPP_TEMPLATE_PVT SQL Statements

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

Line: 37


       SELECT 'x'
       FROM   Okl_Leaseapp_Templates LATV,
              OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
       WHERE  LATV.ID                             = LAVV.LEASEAPP_TEMPLATE_ID
       AND    LATV.CREDIT_REVIEW_PURPOSE          = p_latv_rec.CREDIT_REVIEW_PURPOSE
       AND    LATV.CUST_CREDIT_CLASSIFICATION     = p_latv_rec.CUST_CREDIT_CLASSIFICATION
-- Bug 5149659 udhenuko : Start - Uncommenting industry values check
--Commented the industry values for checking uniqueness due to issue with data model
--industry values are stired at header and it will be same always accross versions
--for a given template
       AND    NVL(LATV.INDUSTRY_CODE, OKL_API.G_MISS_CHAR)         = NVL(p_latv_rec.INDUSTRY_CODE, OKL_API.G_MISS_CHAR)
       AND    NVL(LATV.INDUSTRY_CLASS, OKL_API.G_MISS_CHAR)        = NVL(p_latv_rec.INDUSTRY_CLASS, OKL_API.G_MISS_CHAR)
-- Bug 5149659 udhenuko : End
       AND    NVL(LAVV.CONTRACT_TEMPLATE_ID, OKL_API.G_MISS_NUM) = NVL(p_lavv_rec.CONTRACT_TEMPLATE_ID, OKL_API.G_MISS_NUM)
       AND    NVL(LAVV.CHECKLIST_ID, OKL_API.G_MISS_NUM)         = NVL(p_lavv_rec.CHECKLIST_ID, OKL_API.G_MISS_NUM)
       AND    LAVV.VERSION_STATUS                 = 'ACTIVE'
       AND    LAVV.ID                            <> nvl(p_lavv_rec.id,-99999);

Line: 116


    SELECT LATV.ID LAT_HDR_ID
         , LATV.OBJECT_VERSION_NUMBER HDR_OBJ_VER_NO
         , LAVV.OBJECT_VERSION_NUMBER VER_OBJ_VER_NO
    FROM   OKL_LEASEAPP_TEMPLATES        LATV,
           OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
    WHERE  LATV.ID  = LAVV.LEASEAPP_TEMPLATE_ID
    AND    LAVV.ID  = cp_lav_id;

Line: 126


    SELECT 'X'
      FROM OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
     WHERE LAVV.LEASEAPP_TEMPLATE_ID = cp_lat_id
       AND LAVV.VERSION_STATUS       <> G_STATUS_ACTIVE;

Line: 168


    OKL_LAV_PVT.update_row(p_api_version                => p_api_version
                            ,p_init_msg_list              => p_init_msg_list
                            ,x_return_status              => l_return_status
                            ,x_msg_count                  => x_msg_count
                            ,x_msg_data                   => x_msg_data
                            ,p_lavv_rec                   => l_lavv_rec
                            ,x_lavv_rec                   => lx_lavv_rec);

Line: 188


      OKL_LAT_PVT.update_row(p_api_version              => p_api_version
                            ,p_init_msg_list            => p_init_msg_list
                            ,x_return_status            => l_return_status
                            ,x_msg_count                => x_msg_count
                            ,x_msg_data                 => x_msg_data
                            ,p_latv_rec                 => l_latv_rec
                            ,x_latv_rec                 => lx_latv_rec);

Line: 349


      okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAT_PVT.INSERT_ROW'
      ,'begin debug OKLSLATB.pls call insert_row');

Line: 353


    OKL_LAT_PVT.insert_row(p_api_version            => p_api_version
                          ,p_init_msg_list          => p_init_msg_list
                          ,x_return_status          => x_return_status
                          ,x_msg_count              => x_msg_count
                          ,x_msg_data               => x_msg_data
                          ,p_latv_rec               => l_latv_rec
                          ,x_latv_rec               => lx_latv_rec);

Line: 361


      okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAT_PVT.INSERT_ROW'
      ,'end debug OKLSLATB.pls call insert_row');

Line: 366


       okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAT_PVT.INSERT_ROW',
       ' l_latv_name ' || l_latv_rec.NAME ||
           ' expiring lease application template with ret status ' || x_return_status ||
           ' x_msg_data ' || x_msg_data);

Line: 381


       okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
       ,'begin debug OKLSLAVB.pls call insert_row');

Line: 385


    OKL_LAV_PVT.insert_row(p_api_version             => p_api_version
                          ,p_init_msg_list           => p_init_msg_list
                          ,x_return_status           => x_return_status
                          ,x_msg_count               => x_msg_count
                          ,x_msg_data                => x_msg_data
                          ,p_lavv_rec                => l_lavv_rec
                          ,x_lavv_rec                => lx_lavv_rec);

Line: 393


      okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
      ,'end debug OKLSLAVB.pls call insert_row');

Line: 398


       okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAV_PVT.INSERT_ROW',
       'l_lavv_rec.version_number '||to_char(l_lavv_rec.version_number)||
           'l_lavv_rec.version_status '||l_lavv_rec.version_status||
       ' expiring lease application template  with ret status '||x_return_status||' x_msg_data '||x_msg_data);

Line: 457


  PROCEDURE update_leaseapp_template(p_api_version        IN NUMBER,
                                     p_init_msg_list      IN VARCHAR2,
                                     x_return_status      OUT NOCOPY VARCHAR2,
                                     x_msg_count          OUT NOCOPY NUMBER,
                                     x_msg_data           OUT NOCOPY VARCHAR2,
                                     p_latv_rec           IN  latv_rec_type,
                                     x_latv_rec           OUT NOCOPY latv_rec_type,
                                     p_lavv_rec           IN lavv_rec_type,
                                     x_lavv_rec           OUT NOCOPY lavv_rec_type,
                                     p_ident_flag         IN VARCHAR2)IS
    -- Variables Declarations
    l_api_version CONSTANT NUMBER       DEFAULT 1.0;

Line: 469


    l_api_name    CONSTANT VARCHAR2(30) DEFAULT 'UPDATE_LEASEAPP_TEMPLATE';

Line: 489


    SELECT MIN(LAVV.valid_from) valid_from
    FROM   Okl_Leaseapp_Templates LATV,
           okl_leaseapp_templ_versions_v LAVV
    WHERE  LATV.ID  = LAVV.LEASEAPP_TEMPLATE_ID
    AND    LATV.ID  = p_lat_id
    GROUP BY LATV.ID;

Line: 499


    SELECT MAX(START_DATE) max_start_date
    FROM
      (
         SELECT chr.START_DATE START_DATE
        FROM   okc_k_headers_b chr,
               okl_vp_associations vpa,
               Okl_Leaseapp_Templates lat,
               okl_leaseapp_templ_versions_b lav
        WHERE  chr.scs_code = 'PROGRAM'
          AND  chr.sts_code = 'ACTIVE'
          AND  chr.id = vpa.chr_id
          AND  vpa.ASSOC_OBJECT_TYPE_CODE = 'LA_TEMPLATE'
          AND  vpa.ASSOC_OBJECT_ID = lat.ID
          AND  vpa.ASSOC_OBJECT_VERSION  = lav.VERSION_NUMBER
          AND  LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
          AND  lat.ID          =   p_lat_id
        UNION
        SELECT laa.VALID_FROM START_DATE
        FROM   OKL_LEASE_APPLICATIONS_B laa,
               okl_leaseapp_templates lat,
               okl_leaseapp_templ_versions_v lav
        WHERE  lat.id = lav.leaseapp_template_id
        AND    laa.LEASEAPP_TEMPLATE_ID = lav.ID
        AND    laa.APPLICATION_STATUS IN('CONV-CL','CONV-K','CR-APPROVED','CR-SUBMITTED',
                                         'PR-ACCEPTED','PR-APPROVED','PR-SUBMITTED')
        AND    laa.VALID_FROM >= lav.VALID_FROM
        AND    lat.ID          =   p_lat_id
       ) MY_START_DATE;

Line: 644


      OKL_LAT_PVT.update_row(p_api_version              => p_api_version
                            ,p_init_msg_list            => p_init_msg_list
                            ,x_return_status            => x_return_status
                            ,x_msg_count                => x_msg_count
                            ,x_msg_data                 => x_msg_data
                            ,p_latv_rec                 => l_latv_rec
                            ,x_latv_rec                 => lx_latv_rec);

Line: 657


         okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAT_PVT.UPDATE_ROW',
         'l_latv_name '||l_latv_rec.NAME
         ||'lease application template with ret status '||x_return_status||' x_msg_data '||x_msg_data);

Line: 675


      OKL_LAV_PVT.update_row(p_api_version                => p_api_version
                            ,p_init_msg_list              => p_init_msg_list
                            ,x_return_status              => x_return_status
                            ,x_msg_count                  => x_msg_count
                            ,x_msg_data                   => x_msg_data
                            ,p_lavv_rec                   => l_lavv_rec
                            ,x_lavv_rec                   => lx_lavv_rec);

Line: 688


         okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAV_PVT.UPDATE_ROW',
         'l_lavv_rec.version_number '||to_char(l_lavv_rec.version_number) ||'l_lavv_rec.version_status '
         ||l_lavv_rec.version_status
         ||'lease application template  with ret status '||x_return_status||'x_msg_data '||x_msg_data);

Line: 743


  END update_leaseapp_template;

Line: 792


     /*  SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
        FROM   okl_leaseapp_templates lat,
               okl_leaseapp_templ_versions_v lav
        WHERE  lat.id = lav.leaseapp_template_id
        AND    lat.id = p_lat_id
        group by lav.valid_from,lav.valid_to; */

Line: 799


       SELECT LAV.VERSION_NUMBER
	    , LAV.VALID_FROM
	    , LAV.VALID_TO
       FROM OKL_LEASEAPP_TEMPLATES LAT
          , OKL_LEASEAPP_TEMPL_VERSIONS_B LAV
       WHERE LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
         AND LAT.ID = p_lat_id
         AND TO_NUMBER(LAV.VERSION_NUMBER) = (SELECT MAX(TO_NUMBER(LAV1.VERSION_NUMBER))
                                              FROM OKL_LEASEAPP_TEMPL_VERSIONS_B LAV1
                                              WHERE LAV1.LEASEAPP_TEMPLATE_ID = LAT.ID);

Line: 923


        okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.update_leaseapp_template'
        ,'end debug OKLRLATB.pls call update_leaseapp_template');

Line: 934


       update_leaseapp_template(p_api_version => p_api_version
                          ,p_init_msg_list    => p_init_msg_list
                          ,x_return_status    => x_return_status
                          ,x_msg_count        => x_msg_count
                          ,x_msg_data         => x_msg_data
                          ,p_latv_rec         => l_latv_rec_old
                          ,x_latv_rec         => x_latv_rec_old
                          ,p_lavv_rec         => l_lavv_rec_old
                          ,x_lavv_rec         => x_lavv_rec_old
                          ,p_ident_flag       => 'V');

Line: 945


         okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.update_leaseapp_template'
         ,'end debug OKLRLATB.pls call update_leaseapp_template');

Line: 950


          okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.update_leaseapp_template',
          'l_latv_name '||l_latv_rec_old.NAME ||
          'lease application template  with ret status '||x_return_status||' x_msg_data '||x_msg_data);

Line: 967


         okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
         ,'begin debug OKLSLAVB.pls call insert_row');

Line: 988


       OKL_LAV_PVT.insert_row(p_api_version                        => p_api_version
                          ,p_init_msg_list                      => p_init_msg_list
                          ,x_return_status                      => x_return_status
                          ,x_msg_count                          => x_msg_count
                          ,x_msg_data                           => x_msg_data
                          ,p_lavv_rec                           => l_lavv_rec_new
                          ,x_lavv_rec                           => x_lavv_rec_new);

Line: 996


         okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
         ,'end debug OKLSLAVB.pls call insert_row');

Line: 1001


          okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAT_PVT.INSERT_ROW',
                  ' l_latv_name '||l_latv_rec_old.NAME ||
          ' expiring lease application template  with ret status '||x_return_status||' x_msg_data '||x_msg_data);

Line: 1064


      SELECT MEANING
      FROM  FND_LOOKUPS FND
      WHERE FND.LOOKUP_TYPE = p_lookup_type
      AND   FND.LOOKUP_CODE = p_lookup_code;

Line: 1172


        SELECT CHECKLIST_NUMBER TEMPLATE_NUMBER
        FROM   OKL_CHECKLISTS CHK,
               OKL_LEASEAPP_TEMPLATES LATV,
               OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
        WHERE  LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
        AND    LAVV.CHECKLIST_ID = CHK.ID
        AND    ( (LAVV.VALID_FROM NOT BETWEEN NVL(CHK.START_DATE,LAVV.VALID_FROM)
                  AND NVL(CHK.END_DATE,LAVV.VALID_FROM))
                OR CHK.STATUS_CODE <> 'ACTIVE'
                OR CHK.CHECKLIST_PURPOSE_CODE NOT IN ('CHECKLIST_TEMPLATE', 'CHECKLIST_TEMPLATE_GROUP')
                OR CHK.ORG_ID <> LATV.ORG_ID)
        AND    LAVV.ID = p_lavv_id;

Line: 1187


        SELECT OKH.CONTRACT_NUMBER TEMPLATE_NUMBER
        FROM   OKC_K_HEADERS_B OKH,
               OKL_LEASEAPP_TEMPLATES LATV,
               OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV,
               OKL_K_HEADERS KHR,
               OKC_STATUSES_V STS
        WHERE  LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
        AND    LAVV.CONTRACT_TEMPLATE_ID = OKH.ID
        AND    OKH.ID = KHR.ID
        AND    OKH.STS_CODE = STS.CODE
        --Bug#6850094 : Include contract template with any status
        /*
        AND    (TEMPLATE_YN <> 'Y'
                OR STS.STE_CODE <> 'ACTIVE'
                OR NVL(KHR.TEMPLATE_TYPE_CODE, 'X') <> 'LEASEAPP'
                OR OKH.AUTHORING_ORG_ID <> LATV.ORG_ID)
        */
        AND    (OKH.TEMPLATE_YN <> 'Y'
                OR (NVL(KHR.TEMPLATE_TYPE_CODE,'X') ='LEASEAPP' AND STS.STE_CODE <> 'ACTIVE')
                OR NVL(KHR.TEMPLATE_TYPE_CODE, 'X') NOT IN ('LEASEAPP','CONTRACT')
                OR OKH.AUTHORING_ORG_ID <> LATV.ORG_ID)
        --Bug#6850094:End
        AND    LAVV.ID = p_lavv_id;

Line: 1314


       update_leaseapp_template(p_api_version      => p_api_version
                              ,p_init_msg_list    => p_init_msg_list
                              ,x_return_status    => x_return_status
                              ,x_msg_count        => x_msg_count
                              ,x_msg_data         => x_msg_data
                              ,p_latv_rec         => l_latv_rec
                              ,x_latv_rec         => lx_latv_rec
                              ,p_lavv_rec         => l_lavv_rec
                              ,x_lavv_rec         => lx_lavv_rec
                              ,p_ident_flag       => 'A');

Line: 1394


    SELECT MAX(START_DATE) max_start_date
    FROM
      (
        SELECT chr.START_DATE START_DATE
        FROM   okc_k_headers_b chr,
               okl_vp_associations vpa,
               Okl_Leaseapp_Templates lat,
               okl_leaseapp_templ_versions_b lav
        WHERE  chr.scs_code = 'PROGRAM'
          AND  chr.sts_code = 'ACTIVE'
          AND  chr.id = vpa.chr_id
          AND  vpa.ASSOC_OBJECT_TYPE_CODE = 'LA_TEMPLATE'
          AND  vpa.ASSOC_OBJECT_ID = lat.ID
          AND  vpa.ASSOC_OBJECT_VERSION  = lav.VERSION_NUMBER
          AND  LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
          AND  lat.ID          =   p_lat_id
        UNION
        SELECT laa.VALID_FROM START_DATE
        FROM   OKL_LEASE_APPLICATIONS_B laa,
               okl_leaseapp_templates lat,
               okl_leaseapp_templ_versions_v lav
        WHERE  lat.id = lav.leaseapp_template_id
        AND    laa.LEASEAPP_TEMPLATE_ID = lav.ID
        AND    laa.APPLICATION_STATUS IN('CONV-CL','CONV-K','CR-APPROVED','CR-SUBMITTED',
                                         'PR-ACCEPTED','PR-APPROVED','PR-SUBMITTED')
        AND    trunc(laa.VALID_FROM) >= trunc(lav.VALID_FROM)
        AND    lat.ID          =   p_lat_id
       ) MY_START_DATE;

Line: 1424


     /*  SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
        FROM   okl_leaseapp_templates lat,
               okl_leaseapp_templ_versions_v lav
        WHERE  lat.id = lav.leaseapp_template_id
        AND    lat.id = p_lat_id
        group by lav.valid_from,lav.valid_to;*/

Line: 1431


        SELECT LAV.VERSION_NUMBER
	     , LAV.VALID_FROM
	     , LAV.VALID_TO
	FROM OKL_LEASEAPP_TEMPLATES LAT
	   , OKL_LEASEAPP_TEMPL_VERSIONS_B LAV
	WHERE LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
	  AND LAT.ID = p_lat_id
	  AND TO_NUMBER(LAV.VERSION_NUMBER) = (SELECT MAX(TO_NUMBER(LAV1.VERSION_NUMBER))
	                                       FROM OKL_LEASEAPP_TEMPL_VERSIONS_B LAV1
                                               WHERE LAV1.LEASEAPP_TEMPLATE_ID = LAT.ID);