APPS.OKS_EXTWAR_UTIL_PUB SQL Statements

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

Line: 83

Procedure Update_Hdr_Amount
 (
  p_api_version         IN   Number,
  p_init_msg_list       IN   Varchar2,
  p_chr_id              IN   Number,
  x_return_status       OUT  NOCOPY Varchar2,
  x_msg_count           OUT  NOCOPY Number,
  x_msg_data            OUT  NOCOPY Varchar2
 )
 IS

   l_return_status	Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;

Line: 95

   l_api_name            CONSTANT VARCHAR2(30) := 'Update_Hdr_Amount';

Line: 103

   Cursor l_line_csr Is Select Sum(Nvl(PRICE_NEGOTIATED,0))
                        From OKC_K_LINES_B
                        Where dnz_chr_id = p_chr_id And
                              lse_id in (7,8,9,10,11,35,25);

Line: 131

    	okc_contract_pub.update_contract_header
    	(
    		p_api_version						=> l_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_chrv_tbl							=> l_chrv_tbl_in,
    		x_chrv_tbl							=> l_chrv_tbl_out
      );

Line: 178

  END Update_Hdr_Amount;

Line: 221

    l_p_tavv_tbl(1).last_updated_by := NULL;

Line: 222

    l_p_tavv_tbl(1).last_update_date := NULL;

Line: 223

    l_p_tavv_tbl(1).last_update_login := NULL;

Line: 266

    SELECT fu.user_id
    FROM jtf_rs_resource_extns jrd,
         fnd_user fu
    WHERE jrd.resource_id=p_resource_id
    AND    fu.user_id = jrd.user_id;

Line: 273

  CURSOR l_party_name_csr Is select party_Name from hz_parties where party_id = p_party_id;

Line: 376

  select object1_id1
  from okc_k_party_roles_b
  where dnz_chr_id = p_contract_id
  and cle_id is null
  and RLE_CODE = 'CUSTOMER';

Line: 395

SELECT salesrep_id
From   jtf_rs_salesreps
Where  resource_id = p_resource_id and org_id = p_org_id;

Line: 410

select resource_name from jtf_rs_resource_extns_tl
where resource_id = p_resource_id
and   language = userenv('LANG');

Line: 425

select party_Name from hz_parties
where party_id = p_party_id;

Line: 458

SELECT object1_id1, cro_code, id
FROM OKC_CONTACTS
WHERE CRO_CODE = p_cro_code
AND dnz_chr_id = p_contract_header_id;

Line: 464

select id from okc_k_party_roles_b
where dnz_chr_id = p_contract_header_id
and cle_id is null
and RLE_CODE = 'VENDOR';

Line: 485

  DELETE_CONTACT (
                 x_return_status => l_return_status,
                 p_contact_id    => C_GET_CONTACT.id
                 );

Line: 532

PROCEDURE DELETE_CONTACT (
                           x_return_status                OUT NOCOPY VARCHAR2,
			   p_contact_id		IN NUMBER
			   ) IS
-- Contact Details
cursor contact_det is
select a.dnz_chr_id,a.object1_id1, a.object1_id2, a.jtot_object1_code
from   okc_contacts a
where  a.id = p_contact_id;

Line: 545

select id
from   okc_rule_groups_b
where  dnz_chr_id = p_chr_id
and    cle_id is null;

Line: 646

    okc_contract_party_pub.delete_contact (
    	p_api_version		=> l_api_version,
    	p_init_msg_list		=> l_init_msg_list,
    	x_return_status		=> l_return_status,
    	x_msg_count		=> l_msg_count,
    	x_msg_data		=> l_msg_data,
    	p_ctcv_tbl		=> l_ctcv_tbl_in
    );

Line: 655

      LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);

Line: 658

      LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);

Line: 662

END DELETE_CONTACT;

Line: 692

    l_ctcv_tbl_in(1).last_updated_by                 := OKC_API.G_MISS_NUM;

Line: 693

    l_ctcv_tbl_in(1).last_update_date                := SYSDATE;

Line: 694

    l_ctcv_tbl_in(1).last_update_login               := OKC_API.G_MISS_NUM;

Line: 708

      LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);

Line: 711

      LOG_MESSAGES('okc_contract_party_pub.delete_contact l_msg_data = ' || l_msg_data);

Line: 754

        SELECT user_name
        FROM fnd_user
        WHERE user_id = p_user_id ;

Line: 881

 cursor l_fnd_csr is select user_name from fnd_user where user_id = p_notify_id;

Line: 928

          SELECT code
          FROM okc_statuses_b
          WHERE ste_code = p_ste_code
          AND   default_yn = 'Y';

Line: 944

          SELECT ste_code
          FROM okc_statuses_b
          WHERE code = p_sts_code;

Line: 979

                SELECT a.salesrep_id
                From   jtf_rs_salesreps a,
                       okc_k_headers_b b
                Where b.id = p_chr_id
                  and a.resource_id = p_resource_id
                  and a.org_id = b.authoring_org_id;

Line: 1092

      SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
      ,hdr.contract_number_modifier
      ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
      ,Party2.id,hz.party_name, hzl.country, hzl.state
      ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
  FROM OKC_K_HEADERS_B hdr,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts    cont,
       hz_parties hz,
       hz_party_sites hzs,
       hz_locations hzl
  WHERE hdr.id = p_contract_hdr_id
  AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
  AND hdr.sts_code  = p_status_code
  AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
  AND hdr.template_yn   = 'N'
  AND party1.dnz_chr_id = hdr.id
  AND party1.cle_id is null
  AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
  AND hz.party_id    = party1.object1_id1
  AND hzs.party_id   =   hz.party_id
  AND hzs.identifying_address_flag ='Y'
  AND hzl.location_id   = hzs.location_id
  AND party2.dnz_chr_id = party1.dnz_chr_id
  AND party2.chr_id = party1.dnz_chr_id
  AND party2.cle_id is null
  AND party2.rle_code IN ('VENDOR','MERCHANT')
  AND cont.cpl_id (+)     = party2.id
  AND cont.object1_id1(+) = p_salesrep_id
  AND cont.cro_code(+)    = p_cro_code
  AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));

Line: 1127

        SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
             ,hdr.contract_number_modifier
             ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
             ,Party2.id,hz.party_name, hzl.country, hzl.state
             ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
        FROM OKC_K_HEADERS_B hdr,
             okc_k_party_roles_b party1,
             okc_k_party_roles_b party2,
             okc_contacts    cont,
             hz_parties hz,
             hz_party_sites hzs,
             hz_locations hzl
        WHERE hdr.id = p_contract_hdr_id
          AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
          AND hdr.sts_code  = p_status_code
          AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
          AND hdr.template_yn = 'N'
          AND party1.dnz_chr_id = hdr.id
          AND party1.cle_id is null
          AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
          AND hz.party_id = party1.object1_id1
          AND hzs.party_id   =   hz.party_id
          AND hzs.identifying_address_flag ='Y'
          AND hzl.location_id = hzs.location_id
          AND party2.dnz_chr_id = party1.dnz_chr_id
          AND party2.chr_id = party1.dnz_chr_id
          AND party2.cle_id is null
          AND party2.rle_code IN ('VENDOR','MERCHANT')
          AND cont.cpl_id (+) = party2.id
          AND cont.cro_code(+)    = p_cro_code
          AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));

Line: 1161

      SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
      ,hdr.contract_number_modifier
      ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
      ,Party2.id,hz.party_name, hzl.country, hzl.state
      ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
  FROM OKC_K_HEADERS_B hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts    cont,
       hz_parties hz,
       hz_party_sites hzs,
       hz_locations hzl
  WHERE hdr.id = p_contract_hdr_id
  AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
  AND stat.STE_CODE  IN ('ENTERED','ACTIVE','SIGNED','HOLD')
  AND hdr.sts_code  = stat.CODE
  AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
  AND hdr.template_yn = 'N'
  AND party1.dnz_chr_id = hdr.id
  AND party1.cle_id is null
  AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
  AND hz.party_id = party1.object1_id1
  AND hzs.party_id   =   hz.party_id
  AND hzs.identifying_address_flag ='Y'
  AND hzl.location_id = hzs.location_id
  AND party2.dnz_chr_id = party1.dnz_chr_id
  AND party2.chr_id = party1.dnz_chr_id
  AND party2.cle_id is null
  AND party2.rle_code IN ('VENDOR','MERCHANT')
  AND cont.cpl_id (+) = party2.id
  AND cont.object1_id1(+) = p_salesrep_id
  AND cont.cro_code(+)    = p_cro_code
  AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));

Line: 1198

      SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
      ,hdr.contract_number_modifier
      ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
      ,Party2.id,hz.party_name, hzl.country, hzl.state
      ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
  FROM OKC_K_HEADERS_B hdr,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts    cont,
       hz_parties hz,
       hz_party_sites hzs,
       hz_locations hzl
  WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
  AND hdr.sts_code  =  p_status_code
  AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
  AND hdr.template_yn = 'N'
  AND party1.dnz_chr_id = hdr.id
  AND party1.cle_id is null
  AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
  AND hz.party_id = party1.object1_id1
  AND hzs.party_id   =   hz.party_id
  AND hzs.identifying_address_flag ='Y'
  AND hzl.location_id   = hzs.location_id
  AND party2.dnz_chr_id = party1.dnz_chr_id
  AND party2.chr_id     = party1.dnz_chr_id
  AND party2.cle_id is null
  AND party2.rle_code IN ('VENDOR','MERCHANT')
  AND cont.cpl_id (+)     = party2.id
  AND cont.object1_id1(+) = p_salesrep_id
  AND cont.cro_code(+)    = p_cro_code
  AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));

Line: 1232

      SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
      ,hdr.contract_number_modifier
      ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
      ,Party2.id,hz.party_name, hzl.country, hzl.state
      ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
  FROM OKC_K_HEADERS_B hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts    cont,
       hz_parties hz,
       hz_party_sites hzs,
       hz_locations hzl
  WHERE hdr.id = p_contract_hdr_id
  AND hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
  AND stat.STE_CODE  IN ('ENTERED','ACTIVE','SIGNED','HOLD')
  AND hdr.sts_code  = stat.CODE
  AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
  AND hdr.template_yn = 'N'
  AND party1.dnz_chr_id = hdr.id
  AND party1.cle_id is null
  AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
  AND hz.party_id = party1.object1_id1
  AND hzs.party_id   =   hz.party_id
  AND hzs.identifying_address_flag ='Y'
  AND hzl.location_id = hzs.location_id
  AND party2.dnz_chr_id = party1.dnz_chr_id
  AND party2.chr_id = party1.dnz_chr_id
  AND party2.cle_id is null
  AND party2.rle_code IN ('VENDOR','MERCHANT')
  AND cont.cpl_id (+)  = party2.id
  AND cont.cro_code(+) = p_cro_code
  AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE)) ;

Line: 1268

     SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
      ,hdr.contract_number_modifier
      ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
      ,Party2.id,hz.party_name, hzl.country, hzl.state
      ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
  FROM OKC_K_HEADERS_B hdr,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts    cont,
       hz_parties hz,
       hz_party_sites hzs,
       hz_locations hzl
  WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
  AND hdr.sts_code  = p_status_code
  AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
  AND hdr.template_yn   = 'N'
  AND party1.dnz_chr_id = hdr.id
  AND party1.cle_id is null
  AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
  AND hz.party_id  = party1.object1_id1
  AND hzs.party_id = hz.party_id
  AND hzs.identifying_address_flag ='Y'
  AND hzl.location_id = hzs.location_id
  AND party2.dnz_chr_id = party1.dnz_chr_id
  AND party2.chr_id     = party1.dnz_chr_id
  AND party2.cle_id is null
  AND party2.rle_code IN ('VENDOR','MERCHANT')
  AND cont.cpl_id (+)  = party2.id
  AND cont.cro_code(+) = p_cro_code
  AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));

Line: 1301

      SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
      ,hdr.contract_number_modifier
      ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
      ,Party2.id,hz.party_name, hzl.country, hzl.state
      ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
  FROM OKC_K_HEADERS_B hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts    cont,
       hz_parties hz,
       hz_party_sites hzs,
       hz_locations hzl
  WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
  AND stat.STE_CODE  IN ('ENTERED','ACTIVE','SIGNED','HOLD')
  AND hdr.sts_code  = stat.CODE
  AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
  AND hdr.template_yn = 'N'
  AND party1.dnz_chr_id = hdr.id
  AND party1.cle_id is null
  AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
  AND hz.party_id = party1.object1_id1
  AND hzs.party_id   =   hz.party_id
  AND hzs.identifying_address_flag ='Y'
  AND hzl.location_id   = hzs.location_id
  AND party2.dnz_chr_id = party1.dnz_chr_id
  AND party2.chr_id     = party1.dnz_chr_id
  AND party2.cle_id is null
  AND party2.rle_code IN ('VENDOR','MERCHANT')
  AND cont.cpl_id      = party2.id
  AND cont.object1_id1 = p_salesrep_id
  AND cont.end_date is  null
  AND cont.cro_code    = p_cro_code
  AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE));

Line: 1338

      SELECT /*+ PARALLEL(hdr) */  hdr.id,hdr.contract_number
      ,hdr.contract_number_modifier
      ,hdr.authoring_org_id,hdr.inv_organization_id, party1.object1_id1
      ,Party2.id,hz.party_name, hzl.country, hzl.state
      ,cont.id,cont.object1_id1,cont.start_date,hdr.sts_code
  FROM OKC_K_HEADERS_B hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts    cont,
       hz_parties hz,
       hz_party_sites hzs,
       hz_locations hzl
  WHERE hdr.authoring_org_id=nvl(p_org_id,hdr.authoring_org_id)
  AND stat.STE_CODE  IN ('ENTERED','ACTIVE','SIGNED','HOLD')
  AND hdr.sts_code  = stat.CODE
  AND hdr.scs_code  IN ('SERVICE','WARRANTY','SUBSCRIPTION')
  AND hdr.template_yn = 'N'
  AND party1.dnz_chr_id = hdr.id
  AND party1.cle_id is null
  AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
  AND hz.party_id = party1.object1_id1
  AND hzs.party_id   =   hz.party_id
  AND hzs.identifying_address_flag ='Y'
  AND hzl.location_id   = hzs.location_id
  AND party2.dnz_chr_id = party1.dnz_chr_id
  AND party2.chr_id     = party1.dnz_chr_id
  AND party2.cle_id is null
  AND party2.rle_code IN ('VENDOR','MERCHANT')
  AND cont.cpl_id (+) = party2.id
  AND cont.cro_code(+)= p_cro_code
  AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE));

Line: 1376

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.id = p_contract_hdr_id
   AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code = p_status_code
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id  = party1.dnz_chr_id
   AND party2.chr_id      = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id      = party2.id
   AND cont.object1_id1 = p_salesrep_id
   AND cont.cro_code    = p_cro_code
   AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE))
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1421

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.id = p_contract_hdr_id
   AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code  = p_status_code
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id = party1.dnz_chr_id
   AND party2.chr_id = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id(+)   = party2.id
   AND cont.cro_code(+) = p_cro_code
   AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1465

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.id = p_contract_hdr_id
   AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code = stat.code
   AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id = party1.dnz_chr_id
   AND party2.chr_id = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id      = party2.id
   AND cont.object1_id1 = p_salesrep_id
   AND cont.cro_code    = p_cro_code
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1512

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code = p_status_code
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id = party1.dnz_chr_id
   AND party2.chr_id = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id      = party2.id
   AND cont.object1_id1 = p_salesrep_id
   AND cont.cro_code    = p_cro_code
   AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE))
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1555

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.id = p_contract_hdr_id
   AND hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code = stat.code
   AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id = party1.dnz_chr_id
   AND party2.chr_id     = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id(+) = party2.id
   AND cont.cro_code(+) = p_cro_code
   AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1601

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code = p_status_code
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id = party1.dnz_chr_id
   AND party2.chr_id = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id(+)  = party2.id
   AND cont.cro_code(+) = p_cro_code
   AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1643

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code = stat.code
   AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id = party1.dnz_chr_id
   AND party2.chr_id = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id      = party2.id
   AND cont.object1_id1 = p_salesrep_id
   AND cont.cro_code    = p_cro_code
   AND (TRUNC(NVL(cont.end_date,SYSDATE)) >= TRUNC(SYSDATE))
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1688

SELECT /*+ PARALLEL(HDR) */
       hdr.ID,
       hdr.contract_number,
       hdr.contract_number_modifier,
       hdr.authoring_org_id,
       hdr.inv_organization_id,
       party1.object1_id1,
       party2.ID,
       hz.party_name,
       c.country,
       c.region_2 state,
       cont.ID,
       cont.object1_id1,
       cont.start_date,
       hdr.sts_code
  FROM okc_k_headers_b hdr,
       okc_statuses_b stat,
       okc_k_party_roles_b party1,
       okc_k_party_roles_b party2,
       okc_contacts cont,
       hz_parties hz,
       hr_all_organization_units b,
       hr_locations_all c
 WHERE hdr.authoring_org_id = NVL( p_org_id, hdr.authoring_org_id )
   AND hdr.sts_code = stat.code
   AND stat.ste_code IN ('ENTERED','ACTIVE','SIGNED','HOLD')
   AND hdr.scs_code IN( 'SERVICE', 'WARRANTY', 'SUBSCRIPTION' )
   AND hdr.template_yn = 'N'
   AND party1.dnz_chr_id = hdr.ID
   AND party1.cle_id IS NULL
   AND party1.rle_code IN ('CUSTOMER','SUBSCRIBER')
   AND party1.object1_id1 = hz.party_id
   AND party2.dnz_chr_id = party1.dnz_chr_id
   AND party2.chr_id = party1.dnz_chr_id
   AND party2.cle_id IS NULL
   AND party2.rle_code IN ('VENDOR','MERCHANT')
   AND cont.cpl_id(+) = party2.id
   AND cont.cro_code(+) = p_cro_code
   AND (TRUNC(NVL(cont.end_date(+),SYSDATE)) >= TRUNC(SYSDATE))
   AND party2.object1_id1 = b.organization_id
   AND b.location_id = c.location_id;

Line: 1734

         SELECT fu.user_id
         FROM   jtf_rs_resource_extns jrd,fnd_user fu
         WHERE  jrd.resource_id=p_resource_id
         AND    fu.user_id = jrd.user_id;

Line: 1740

         SELECT chr_id,resource_id,user_id,
                salesrep_id,org_id
         FROM oks_jtf_res_temp;

Line: 1744

  CURSOR update_contact_resource IS
         SELECT contact_id,authoring_org_id
         FROM oks_k_res_temp
         WHERE status  IN
                (SELECT code
                 FROM okc_statuses_v
                 WHERE ste_code IN('ACTIVE','SIGNED','HOLD'))
         AND contact_id IS NOT NULL;

Line: 1753

 CURSOR delete_contact_resource IS
         SELECT contact_id,authoring_org_id
         FROM oks_k_res_temp
         WHERE status  IN
               (SELECT code
                FROM okc_statuses_v
                WHERE ste_code = 'ENTERED')
         AND contact_id IS NOT NULL;

Line: 1763

         SELECT id,contract_number,contract_number_modifier
         FROM oks_k_res_temp
         WHERE  status  IN
                (SELECT code
                FROM okc_statuses_v
                WHERE ste_code IN('ACTIVE','SIGNED','HOLD'))
         AND contact_start_date >= trunc(sysdate) ;

Line: 1772

         SELECT id,contract_number, contract_number_modifier,party_name
         FROM   OKS_K_RES_TEMP
         WHERE  id not in (SELECT chr_id FROM OKS_JTF_RES_TEMP);

Line: 1777

         SELECT contract_number, contract_number_modifier
         FROM  okc_k_headers_b
         WHERE id = p_chr_id;

Line: 1798

           SELECT contract_number
           FROM   okc_k_headers_b
           WHERE  id = p_chr_id;

Line: 1813

      SELECT id
      FROM okc_k_party_roles_b
      WHERE dnz_chr_id = p_chr_id
      AND rle_code  IN ('VENDOR','MERCHANT')
      AND cle_id is null;

Line: 1831

         SELECT authoring_org_id
         FROM   okc_k_headers_b
         WHERE  id = p_chr_id;

Line: 2182

     l_gen_return_Rec.trans_object_id.delete;

Line: 2183

     l_gen_return_Rec.resource_id.delete;

Line: 2184

     l_gen_bulk_rec.trans_object_id.delete;

Line: 2185

     l_gen_bulk_rec.trans_detail_object_id.delete;

Line: 2186

     l_gen_bulk_rec.SQUAL_CHAR01.delete;

Line: 2187

     l_gen_bulk_rec.SQUAL_CHAR04.delete;

Line: 2188

     l_gen_bulk_rec.SQUAL_CHAR07.delete;

Line: 2189

     l_gen_bulk_rec.SQUAL_NUM01.delete;

Line: 2218

             INSERT INTO OKS_K_RES_TEMP (id,contract_number,contract_number_modifier,status,
                                         authoring_org_id,inv_organization_id,party_id,cpl_id,
                                         party_name,country_code,state_code,contact_id,salesrep_id,
                                         contact_start_date,contact_end_date,contract_start_date,contract_end_date)
             values (lb_id(i)
                    ,lb_contract_number(i)
                    ,lb_contract_number_modifier(i)
                    ,lb_status(i)
                    ,lb_authoring_org_id(i)
                    ,lb_inv_organization_id(i)
                    ,lb_party_id(i)
                    ,lb_cpl_id(i)
                    ,lb_party_name(i)
                    ,lb_country_code(i)
                    ,lb_state_code(i)
                    ,lb_contact_id(i)
                    ,lb_salesrep_id(i)
                    ,lb_contact_start_date(i)
                    ,null
                    ,null
                    ,null
                    );

Line: 2245

                 'INSERTED INTO oks_reassign_resource_TMP : Successful'
               || 'Nmber of Recs passedto JTF: '
               || l_gen_bulk_rec.trans_object_id.count
               || 'JTF_TERR_ASSIGN_PUB.get_winners start :'
               || to_char(sysdate,'HH:MI:SS'));

Line: 2284

        l_winning_tbl.delete;

Line: 2323

                                   ' Contract selected to insert into JTF_RES_TEMP : ' || l_winning_tbl(idx4).chr_id);

Line: 2326

                        INSERT INTO OKS_JTF_RES_TEMP (chr_id,resource_id,user_id,salesrep_id
                                                      ,org_id,inv_organization_id,contract_start_date,contract_end_date)
                        values(l_winning_tbl(idx4).chr_id
                              ,l_winning_tbl(idx4).resource_id
                              ,l_winning_tbl(idx4).user_id
                              ,l_temp_salesrep
                              ,l_temp_org_id
                              ,null
                              ,null
                              ,null);

Line: 2348

                        DELETE FROM oks_k_res_temp
                        WHERE  id = l_winning_tbl(idx4).chr_id;

Line: 2370

          DELETE  FROM oks_k_res_temp a
                  WHERE exists
                       ( SELECT null
                         FROM oks_k_res_temp b,
                              oks_jtf_res_temp c
                         WHERE b.id = a.id
                           AND b.salesrep_id = c.salesrep_id
                           AND c.chr_id = a.id
            );

Line: 2393

              DELETE FROM oks_k_res_temp
              WHERE id =  contract_noresource_rec.id;

Line: 2425

         DELETE FROM oks_k_res_temp a
                WHERE exists
                      ( SELECT null
                        FROM oks_k_res_temp b
                        WHERE b.id = a.id
                        AND b.contact_start_date >= trunc(sysdate)
                        AND b.status IN (SELECT code
                         		 FROM okc_statuses_v
                                         WHERE ste_code IN('ACTIVE','SIGNED','HOLD'))
                      );

Line: 2437

          DELETE FROM oks_jtf_res_temp a
                 WHERE not exists
                      ( SELECT null
                        FROM oks_k_res_temp b
                        WHERE a.chr_id = b.id
                      );

Line: 2475

                      l_ctcv_tbl_in(idx1).last_updated_by  := OKC_API.G_MISS_NUM;

Line: 2476

                      l_ctcv_tbl_in(idx1).last_update_date := SYSDATE;

Line: 2477

                      l_ctcv_tbl_in(idx1).last_update_login := OKC_API.G_MISS_NUM;

Line: 2493

                            'contract selected to create new contact : ' || l_contract_number
                            || ' ' || 'ID ' || lj_chr_id(i2) );

Line: 2514

         OPEN  update_contact_resource;

Line: 2516

           FETCH update_contact_resource BULK COLLECT INTO
               lr_cpl_id,lr_upd_cpl_org_id limit 1000;

Line: 2523

                            'Contacts selected for update ' || lr_cpl_id(i3) );

Line: 2531

           IF  update_contact_resource%ISOPEN THEN
               EXIT WHEN update_contact_resource%NOTFOUND;

Line: 2535

         IF update_contact_resource%ISOPEN THEN
            CLOSE update_contact_resource;

Line: 2544

         OPEN  delete_contact_resource;

Line: 2546

           FETCH delete_contact_resource BULK COLLECT INTO
               lr_del_cpl_id,lr_del_cpl_org_id limit 1000;

Line: 2553

                            'Contacts selected for delete ' || lr_del_cpl_id(i4));

Line: 2559

           IF    delete_contact_resource%ISOPEN THEN
                  EXIT WHEN delete_contact_resource%NOTFOUND;

Line: 2564

         IF delete_contact_resource%ISOPEN THEN
            CLOSE delete_contact_resource;

Line: 2570

                            'Completed populating PL/SQL tables for create, update and delete');

Line: 2575

                            ' Number of records selected for update ' || l_ctcv_tbl_in_upd.count || ' ' ||
                            ' Number of records selected for delete ' || l_ctcv_tbl_in_del.count || ' ' ||
                            ' Number of records selected for create ' || l_ctcv_tbl_in.count) ;

Line: 2588

               okc_contract_party_pub.update_contact ( p_api_version   => l_api_version,
                                                       p_init_msg_list => l_init_msg_list,
                                                       x_return_status => l_return_status,
                                                       x_msg_count     => l_msg_count,
                                                       x_msg_data      => l_msg_data,
                                                       p_ctcv_rec      => l_ctcv_tbl_in_upd(idx5),
                                                       x_ctcv_rec      => l_ctcv_rec_out_upd );

Line: 2603

                            'Exception in update contact ');

Line: 2605

               fnd_file.put_line(FND_FILE.LOG,'Exception in update contact');

Line: 2617

               okc_contract_party_pub.delete_contact ( p_api_version   => l_api_version,
                                                       p_init_msg_list => l_init_msg_list,
                                                       x_return_status => l_return_status,
                                                       x_msg_count     => l_msg_count,
                                                       x_msg_data      => l_msg_data,
                                                       p_ctcv_rec      => l_ctcv_tbl_in_del(idx6));

Line: 2632

                            'Exception in delete contact ');

Line: 2634

               fnd_file.put_line(FND_FILE.LOG,'Exception in delete contact');

Line: 2656

                     OKC_CVM_PVT.update_contract_version(p_api_version    => l_api_version,
	  	                                         p_init_msg_list  => l_init_msg_list,
 	                                                 x_return_status  => l_return_status,
	                                                 x_msg_count      => l_msg_count,
 	                                                 x_msg_data       => l_msg_data,
  	                                                 p_cvmv_rec       => l_cvmv_rec,
                                                         x_cvmv_rec       => l_cvmv_out_rec);

Line: 2668

                                       ||' Update contract version return status '
                                       || l_return_status );

Line: 2672

                     l_chrv_rec.last_update_date := sysdate;

Line: 2673

                     OKC_CONTRACT_PUB.update_contract_header(p_api_version  => l_api_version,
				                                         p_init_msg_list => OKC_API.G_TRUE,
                                                             x_return_status => l_return_status,
	                                                        x_msg_count => l_msg_count,
	                                                        x_msg_data  => l_msg_data,
	                                                        p_restricted_update => OKC_API.G_TRUE,
	                                                        p_chrv_rec => l_chrv_rec,
	                                                        x_chrv_rec => l_chrv_out_rec);

Line: 2684

                                         'Update contract header return status '
                                       || l_return_status );

Line: 2726

     l_ctcv_tbl_in.delete;

Line: 2727

     l_ctcv_tbl_in_del.delete;

Line: 2728

     l_ctcv_tbl_in_upd.delete;

Line: 2730

     DELETE FROM oks_k_res_temp;

Line: 2731

     DELETE FROM oks_jtf_res_temp;