DBA Data[Home] [Help]

APPS.OKS_ENTITLEMENTS_WEB SQL Statements

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

Line: 210

SELECT  PSE.PARTY_SITE_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
     OKC_K_HEADERS_ALL_V hdr1,
     okc_k_items itm,
     HZ_PARTY_SITES PSE,
     HZ_LOCATIONS LCN
WHERE rle.jtot_object1_code ='OKX_PARTY'
      and rle.object1_id1   =to_number(p_party_id)
      and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
      AND RLE.CLE_ID IS NULL
      and rle.chr_id= hdr1.id
      and rle.dnz_chr_id= hdr1.id
      and itm.jtot_object1_code = 'OKX_PARTYSITE'
      and itm.dnz_chr_id = rle.chr_id
      and PSE.PARTY_SITE_ID = itm.object1_id1
      and LCN.LOCATION_ID = PSE.LOCATION_ID
      and LCN.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
      and PSE.PARTY_SITE_NAME=p_site_name;
Line: 231

SELECT B.INVENTORY_ITEM_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
     OKC_K_HEADERS_ALL_V hdr1,
     okc_k_items itm,
     MTL_SYSTEM_ITEMS_B_KFV B,
     MTL_SYSTEM_ITEMS_TL T
WHERE     rle.jtot_object1_code='OKX_PARTY'
      and rle.object1_id1   =to_number(p_party_id)
      and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
      AND RLE.CLE_ID IS NULL
      and rle.chr_id=hdr1.id
      and rle.dnz_chr_id=hdr1.id
      and itm.dnz_chr_id = rle.chr_id
      and itm.jtot_object1_code = 'OKX_COVITEM'
      and B.INVENTORY_ITEM_ID = itm.object1_id1
      and B.ORGANIZATION_ID = itm.object1_id2
      and B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
      and B.ORGANIZATION_ID = T.ORGANIZATION_ID
      and T.LANGUAGE = userenv('LANG')
      and T.DESCRIPTION=p_item_name;
Line: 254

SELECT B.SYSTEM_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
     OKC_K_HEADERS_ALL_V hdr1,
     okc_k_items itm,
     CS_SYSTEMS_ALL_B B,
     CS_SYSTEMS_ALL_TL T
WHERE rle.jtot_object1_code='OKX_PARTY'
      and rle.object1_id1   =to_number(p_party_id)
      and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
      AND RLE.CLE_ID IS NULL
      and rle.chr_id=hdr1.id
      and rle.dnz_chr_id=hdr1.id
      and itm.jtot_object1_code = 'OKX_COVSYST'
      and itm.dnz_chr_id = rle.chr_id
      and B.SYSTEM_ID = itm.object1_id1
      and B.SYSTEM_ID = T.SYSTEM_ID
      and T.LANGUAGE = userenv('LANG')
      and T.NAME=p_system_name;
Line: 275

SELECT CP.INSTANCE_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
     OKC_K_HEADERS_ALL_B hdr1,
     okc_k_items itm,
     CSI_ITEM_INSTANCES CP,
     MTL_SYSTEM_ITEMS_B_KFV BK
WHERE rle.jtot_object1_code = 'OKX_PARTY'
      and rle.object1_id1   = p_party_id
      and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
      AND RLE.CLE_ID IS NULL
      and rle.dnz_chr_id = hdr1.id
      and itm.dnz_chr_id = rle.chr_id
      and itm.jtot_object1_code = 'OKX_CUSTPROD'
      and CP.instance_id = itm.object1_id1
      and BK.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
      and BK.ORGANIZATION_ID   = CP.inv_master_organization_id
      and BK.DESCRIPTION=p_product_name;
Line: 533

      SELECT  DISTINCT
              PSE.PARTY_SITE_ID ID1,
              '#' ID2,
              PSE.PARTY_SITE_NAME NAME,
              SUBSTR(arp_addr_label_pkg.format_address(NULL,LCN.ADDRESS1,LCN.ADDRESS2,LCN.ADDRESS3,LCN.ADDRESS4,
		    LCN.CITY,LCN.COUNTY,LCN.STATE,LCN.PROVINCE,LCN.POSTAL_CODE,NULL,LCN.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),1,80) DESCRIPTION
      FROM OKC_K_PARTY_ROLES_B rle,
           OKC_K_HEADERS_ALL_V hdr1,
           okc_k_items itm,
           HZ_PARTY_SITES PSE,
           HZ_LOCATIONS LCN
     WHERE rle.jtot_object1_code = 'OKX_PARTY'
       and rle.object1_id1 = to_number(p_party_id)
       and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
       AND RLE.CLE_ID IS NULL
       and rle.chr_id= hdr1.id
       and rle.dnz_chr_id= hdr1.id
       and itm.jtot_object1_code = 'OKX_PARTYSITE'
       and itm.dnz_chr_id = rle.chr_id
       and PSE.PARTY_SITE_ID = itm.object1_id1
       and LCN.LOCATION_ID = PSE.LOCATION_ID
       and LCN.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
       and (PSE.PARTY_SITE_NAME like p_site_name or PSE.PARTY_SITE_NAME is NULL);
Line: 637

SELECT DISTINCT
       B.INVENTORY_ITEM_ID ID1,
       B.ORGANIZATION_ID ID2,
       T.DESCRIPTION NAME,
       B.CONCATENATED_SEGMENTS DESCRIPTION
FROM OKC_K_PARTY_ROLES_B rle,
     OKC_K_HEADERS_ALL_V hdr1,
     okc_k_items itm,
     MTL_SYSTEM_ITEMS_B_KFV B,
     MTL_SYSTEM_ITEMS_TL T
WHERE     rle.jtot_object1_code='OKX_PARTY'
      and rle.object1_id1   =to_number(p_party_id)
      and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
      AND RLE.CLE_ID IS NULL
      and rle.chr_id=hdr1.id
      and rle.dnz_chr_id=hdr1.id
      and itm.dnz_chr_id = rle.chr_id
      and itm.jtot_object1_code = 'OKX_COVITEM'
      and B.INVENTORY_ITEM_ID = itm.object1_id1
      and B.ORGANIZATION_ID = itm.object1_id2
      and B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
      and B.ORGANIZATION_ID = T.ORGANIZATION_ID
      and T.LANGUAGE = userenv('LANG')
      and T.DESCRIPTION like p_item_name;
Line: 744

SELECT DISTINCT
       B.SYSTEM_ID ID1,
       '#' ID2,
       T.NAME NAME,
       T.DESCRIPTION DESCRIPTION
FROM OKC_K_PARTY_ROLES_B rle,
     OKC_K_HEADERS_ALL_V hdr1,
     okc_k_items itm,
     CS_SYSTEMS_ALL_B B,
     CS_SYSTEMS_ALL_TL T
WHERE rle.jtot_object1_code='OKX_PARTY'
      and rle.object1_id1   =to_number(p_party_id)
      and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
      AND RLE.CLE_ID IS NULL
      and rle.chr_id=hdr1.id
      and rle.dnz_chr_id=hdr1.id
      and itm.jtot_object1_code = 'OKX_COVSYST'
      and itm.dnz_chr_id = rle.chr_id
      and B.SYSTEM_ID = itm.object1_id1
      and B.SYSTEM_ID = T.SYSTEM_ID
      and T.LANGUAGE = userenv('LANG')
      AND T.NAME like p_system_name;
Line: 849

SELECT DISTINCT
       CP.INSTANCE_ID ID1,
       '#' ID2,
       BK.DESCRIPTION NAME,
       BK.CONCATENATED_SEGMENTS || '-' || CP.instance_number DESCRIPTION
FROM OKC_K_PARTY_ROLES_B rle,
     OKC_K_HEADERS_ALL_B hdr1,
     OKC_K_ITEMS itm,
     CSI_ITEM_INSTANCES CP,
     MTL_SYSTEM_ITEMS_B_KFV BK
WHERE rle.jtot_object1_code = 'OKX_PARTY'
      and rle.object1_id1   =  p_party_id
      and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
      AND RLE.CLE_ID IS NULL
      and rle.dnz_chr_id = hdr1.id
      and itm.dnz_chr_id = rle.chr_id
      and itm.jtot_object1_code = 'OKX_CUSTPROD'
      and CP.instance_id = itm.object1_id1
      and BK.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
      and BK.ORGANIZATION_ID = CP.inv_master_organization_id
      AND BK.DESCRIPTION like p_product_name;
Line: 952

      SELECT party_name FROM hz_parties WHERE party_id=p_party_id;
Line: 955

      SELECT CODE, MEANING FROM OKC_SUBCLASSES_V WHERE CLS_CODE = 'SERVICE' ORDER BY MEANING;
Line: 958

      SELECT CODE, MEANING FROM OKC_STATUSES_V ORDER BY MEANING;
Line: 1062

      SELECT id FROM OKC_K_HEADERS_ALL_V
      WHERE contract_number=p_contract_number
        AND contract_number_modifier IS NULL;
Line: 1068

      SELECT id FROM OKC_K_HEADERS_ALL_V
      WHERE contract_number=p_contract_number
        AND contract_number_modifier=p_contract_modifier;
Line: 1186

      SELECT name
      FROM oks_k_lines_v srv,
            okc_k_lines_v cov
      WHERE srv.cle_id = p_line_id
      AND   srv.coverage_id = cov.id;
Line: 1253

      SELECT
        hdr.ID HeaderId,
        hdr.CONTRACT_NUMBER ContractNumber,
        hdr.CONTRACT_NUMBER_MODIFIER Modifier,
        ver.MAJOR_VERSION||'.'||MINOR_VERSION Version,
        hdr.COGNOMEN KnownAs,
        hdr.SHORT_DESCRIPTION ShortDescription,
        hdr.ESTIMATED_AMOUNT Amount,
        hdr.CURRENCY_CODE Currency_code,
        hdr.STS_CODE StatusCode,
        sts.MEANING Status,
        hdr.SCS_CODE CatCode,
        cat.MEANING Category,
        ord.ORDER_NUMBER OrderNumber,
        hdr.START_DATE ContStartDate,
        hdr.END_DATE  ContEndDate
      FROM
        OKC_K_REL_OBJS_V rel,
        OKX_ORDER_HEADERS_V ord,
        OKC_STATUSES_V  sts,
        OKC_SUBCLASSES_V cat,
        OKC_K_VERS_NUMBERS_V ver,
        OKC_K_HEADERS_ALL_V hdr
      WHERE
        hdr.ID = to_number(p_contract_id_arg)
        AND hdr.sts_code = sts.code
        AND SYSDATE BETWEEN sts.start_date AND nvl(sts.end_date,SYSDATE)
        AND hdr.scs_code = cat.code
        AND hdr.id = ver.chr_id
        AND hdr.id = rel.chr_id(+)
        AND rel.cle_id IS NULL
        AND rel.object1_id1 = ord.id1(+)
        and rel.jtot_object1_code(+) = 'OKX_ORDERHEAD';
Line: 1289

      /*SELECT fndLkups.meaning
      FROM   fnd_lookups fndLkups
      WHERE  fndLkups.lookup_code = p_period_unit
             and fndLkups.lookup_type = 'EGO_SRV_DURATION_PERIOD';*/
Line: 1294

      SELECT unit_of_measure_tl
      FROM mtl_units_of_measure_tl
      WHERE uom_code = p_period_unit
      AND language = userenv('LANG');
Line: 1302

 SELECT okxCountry.Name BillToCountry
 FROM   HZ_CUST_SITE_USES_ALL CS,
        HZ_PARTY_SITES PS,
        HZ_LOCATIONS L,
        HZ_CUST_ACCT_SITES_ALL CA,
        OKX_CUSTOMER_ACCOUNTS_V cus_b,
        OKC_K_HEADERS_ALL_B hdr,
        OKX_COUNTRIES_V okxCountry
 WHERE  hdr.ID=p_contract_id
        AND CS.SITE_USE_ID = hdr.BILL_TO_SITE_USE_ID
        AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
        AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
        AND PS.LOCATION_ID = L.LOCATION_ID
        AND CS.SITE_USE_CODE = 'BILL_TO'
        AND cus_b.id1 = CA.CUST_ACCOUNT_ID
        AND okxCountry.ID1=L.COUNTRY;
Line: 1322

     SELECT okxCountry.Name BillToCountry
     FROM   HZ_CUST_SITE_USES_ALL CS,
            HZ_PARTY_SITES PS,
            HZ_LOCATIONS L,
            HZ_CUST_ACCT_SITES_ALL CA,
            OKX_CUSTOMER_ACCOUNTS_V cus_b,
            OKC_K_HEADERS_ALL_B hdr,
            OKX_COUNTRIES_V okxCountry
      WHERE
        hdr.ID=p_contract_id
        AND CS.SITE_USE_ID = hdr.SHIP_TO_SITE_USE_ID
        AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
        AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
        AND PS.LOCATION_ID = L.LOCATION_ID
        AND CS.SITE_USE_CODE = 'SHIP_TO'
        AND cus_b.id1 = CA.CUST_ACCOUNT_ID
        AND okxCountry.ID1=L.COUNTRY;
Line: 1344

       SELECT
        hdr.ID ChrID,
        cus_b.NAME BillToCustomer,
        CS.LOCATION BillToSite,
        L.ADDRESS1||
          decode(L.ADDRESS2,NULL,NULL,' , '|| L.ADDRESS2)||
          decode(L.ADDRESS3,NULL,NULL,' , '||L.ADDRESS3)||
          decode(L.ADDRESS4,NULL,NULL,' , '||L.ADDRESS4)
        BillToAddress,
        L.CITY||
          decode(L.STATE,NULL,NULL,'  '||L.STATE)||
          decode(L.POSTAL_CODE,NULL,NULL,'  '||L.POSTAL_CODE)
        BillToCityStateZip
      FROM
        HZ_CUST_SITE_USES_ALL CS,
        HZ_PARTY_SITES PS,
        HZ_LOCATIONS L,
        HZ_CUST_ACCT_SITES_ALL CA,
        OKX_CUSTOMER_ACCOUNTS_V cus_b,
        OKC_K_HEADERS_ALL_V hdr
      WHERE hdr.ID= p_contract_id
        AND CS.SITE_USE_ID = hdr.BILL_TO_SITE_USE_ID
        AND CS.SITE_USE_CODE = 'BILL_TO'
        AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
        AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
        AND PS.LOCATION_ID = L.LOCATION_ID
        AND cus_b.id1 = CA.CUST_ACCOUNT_ID;
Line: 1376

      SELECT
        cus_s.NAME ShipToCustomer,
        CS.LOCATION ShipToSite,
        L.ADDRESS1||
          decode(L.ADDRESS2,NULL,NULL,' , '||L.ADDRESS2)||
          decode(L.ADDRESS3,NULL,NULL,' , '||L.ADDRESS3)||
          decode(L.ADDRESS4,NULL,NULL,' , '||L.ADDRESS4)
        ShipToAddress,
        L.CITY||
          decode(L.STATE,NULL,NULL,'  '||L.STATE)||
          decode(L.POSTAL_CODE,NULL,NULL,'  '||L.POSTAL_CODE)
        ShipToCityStateZip
      FROM
        OKX_CUSTOMER_ACCOUNTS_V cus_s,
        HZ_CUST_SITE_USES_ALL CS,
        HZ_PARTY_SITES PS,
        HZ_LOCATIONS L,
        HZ_CUST_ACCT_SITES_ALL CA,
        OKC_K_HEADERS_ALL_V hdr
      WHERE
        hdr.ID=p_contract_id
        AND CS.SITE_USE_ID = hdr.SHIP_TO_SITE_USE_ID
        AND CS.SITE_USE_CODE = 'SHIP_TO'
        AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
        AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
        AND PS.LOCATION_ID = L.LOCATION_ID
        AND cus_s.ID1 = CA.CUST_ACCOUNT_ID;
Line: 1406

         SELECT bil.PROFILE_NUMBER BillProfileNumber
         FROM   OKS_BILLING_PROFILES_V bil, OKS_K_HEADERS_B oksHdr
         WHERE  oksHdr.CHR_ID = p_contract_id AND bil.ID = oksHdr.BILLING_PROFILE_ID;
Line: 1412

      SELECT
        hdr.id chr_id,
        pty.party_number PartyNumber,
        rle.rle_code RleCode,
        pty.name name,
        fnd.meaning Role,
        pty.gsa_indicator_flag Gsa
      FROM
        FND_LOOKUPS fnd,
        OKX_PARTIES_V pty,
        OKC_K_PARTY_ROLES_B rle,
        OKC_K_HEADERS_ALL_B hdr
      WHERE
        hdr.id=p_contract_id
        and rle.chr_id=hdr.id
        and rle.dnz_chr_id=hdr.id
        and rle.jtot_object1_code='OKX_PARTY'
        AND pty.id1=rle.object1_id1
        AND pty.id2='#'
        AND fnd.lookup_type='OKC_ROLE'
        AND fnd.lookup_code=rle.rle_code
      UNION
      SELECT
        hdr.id chr_id,
        pty.party_number PartyNumber,
        rle.rle_code RleCode,
        pty.name name,
        fnd.meaning Role,
        NULL Gsa
      FROM
        FND_LOOKUPS fnd,
        OKX_PARTIES_V pty,
        OKC_K_PARTY_ROLES_B rle,
        OKC_K_HEADERS_ALL_B hdr
      WHERE
        hdr.id=p_contract_id
        AND rle.chr_id=hdr.id
        AND rle.dnz_chr_id=hdr.id
        AND rle.jtot_object1_code='OKX_VENDOR'
        AND pty.id1=rle.object1_id1
        AND pty.id2='#'
        AND fnd.lookup_type='OKC_ROLE'
        AND fnd.lookup_code=rle.rle_code
      UNION
      SELECT
        hdr.id chr_id,
        NULL PartyNumber,
        rle.rle_code RleCode,
        pty.name name,
        fnd.meaning Role,
        NULL Gsa
      FROM
        FND_LOOKUPS fnd,
        OKX_ORGANIZATION_DEFS_V pty,
        OKC_K_PARTY_ROLES_B rle,
        OKC_K_HEADERS_ALL_B hdr
      WHERE
        hdr.id=p_contract_id
        AND rle.chr_id=hdr.id
        AND rle.dnz_chr_id=hdr.id
        AND rle.jtot_object1_code='OKX_OPERUNIT'
        AND pty.id1=rle.object1_id1
        AND pty.id2='#'
        AND fnd.lookup_type='OKC_ROLE'
        AND fnd.lookup_code=rle.rle_code;
Line: 1480

      select lnStyl.Name LineType
      from okc_line_styles_v lnStyl
      where lnStyl.id = p_lse_id;
Line: 1487

      SELECT
        ln.DNZ_CHR_ID ChrId,
        ln.id LineID,
        ln.Start_Date lineStartDate,
        ln.LINE_NUMBER lineNumber,
        ln.End_Date lineEndDate,
        ln.Exception_YN Exemption,
        sys.concatenated_segments LineName,
        sys.description LineDescription,
        ln.lse_id LseID,
        itm.number_of_items Quantity
      FROM
        MTL_SYSTEM_ITEMS_KFV sys,
        okc_k_items itm,
        okc_k_lines_v ln
      WHERE
        ln.DNZ_CHR_ID=p_contract_id
        AND ln.lse_id IN (1,12,14,19)
        AND itm.cle_id=ln.id
        AND itm.JTOT_OBJECT1_CODE IN ('OKX_WARRANTY', 'OKX_SERVICE', 'OKX_USAGE') -- #4915688
        AND sys.inventory_item_id=itm.object1_id1
        AND sys.ORGANIZATION_ID=itm.object1_id2
       --  AND (sys.service_item_flag='Y' OR sys.usage_item_flag='Y') #4915688
        order by to_number(ln.line_number) ;
Line: 1514

      SELECT
        cus.name AccountName,
        cus.description AccountDesc,
        cus.party_id Account
      FROM
        okx_customer_accounts_v cus,
        okc_k_lines_v ln
      WHERE
        ln.DNZ_CHR_ID = p_contract_id
        AND ln.id=p_line_id
        AND ln.lse_id IN (1,12,14,19)
        AND cus.id1=ln.CUST_ACCT_ID;
Line: 1529

      select okxCountry.Name CountryName
      from OKX_COUNTRIES_V okxCountry
      where okxCountry.ID1=p_country_code;
Line: 1537

      SELECT
        CS.SITE_USE_CODE AddressType,
        CS.LOCATION SiteName,
        L.ADDRESS1 Address1,
        L.ADDRESS2 Address2,
        L.ADDRESS3 Address3,
        L.ADDRESS4 Address4,
        L.CITY || ' ' ||  L.COUNTY City,
        L.STATE State,
        L.POSTAL_CODE ZipCode,
        L.COUNTRY Country
      FROM
          HZ_CUST_SITE_USES_ALL CS,
        HZ_PARTY_SITES PS,
        HZ_LOCATIONS L,
        HZ_CUST_ACCT_SITES_ALL CA,
        OKC_K_LINES_B okcLn
      WHERE okcLn.id = p_line_id
        AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
        AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
        AND PS.LOCATION_ID = L.LOCATION_ID
        AND (CS.SITE_USE_ID = okcLn.BILL_TO_SITE_USE_ID
        OR  CS.SITE_USE_ID = okcLn.SHIP_TO_SITE_USE_ID);
Line: 1833

      SELECT
             C.LAST_NAME name
      FROM
             JTF_RS_RESOURCE_EXTNS RSC ,
             PO_VENDOR_SITES_ALL S ,
             PO_VENDOR_CONTACTS C
      WHERE
             RSC.CATEGORY = 'SUPPLIER_CONTACT'
             AND C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
             AND S.VENDOR_SITE_ID = C.VENDOR_SITE_ID
             AND S.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
             AND RSC.RESOURCE_ID = to_number(p_object1_id1)
             AND '#' = p_object1_id2
      UNION ALL
      SELECT
             EMP.FULL_NAME name
             FROM JTF_RS_RESOURCE_EXTNS RSC ,
             FND_USER U ,
             OKX_PER_ALL_PEOPLE_V EMP
      WHERE
             RSC.CATEGORY = 'EMPLOYEE'
             AND EMP.PERSON_ID = RSC.SOURCE_ID
             AND U.USER_ID = RSC.USER_ID
             AND RSC.RESOURCE_ID = to_number(p_object1_id1)
             AND '#' = p_object1_id2
      UNION ALL
      SELECT
             PARTY.PARTY_NAME name
      FROM
             JTF_RS_RESOURCE_EXTNS RSC ,
             FND_USER U ,
             HZ_PARTIES PARTY
      WHERE
             RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
             AND PARTY.PARTY_ID = RSC.SOURCE_ID
             AND U.USER_ID = RSC.USER_ID
             AND RSC.RESOURCE_ID = to_number(p_object1_id1)
             AND '#' = p_object1_id2
      UNION ALL
      SELECT /*+ ordered */
             TL.RESOURCE_NAME name
      FROM
             JTF_RS_RESOURCE_EXTNS RSC
             ,JTF_RS_SALESREPS SRP
             ,JTF_RS_RESOURCE_EXTNS_TL TL  -- Bug Fix #5442182
             ,FND_USER U

      WHERE
             RSC.CATEGORY = 'OTHER'
             AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
             AND U.USER_ID = RSC.USER_ID
             AND SRP.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
             AND TL.RESOURCE_ID = SRP.RESOURCE_ID  -- Bug Fix #5442182
             AND TL.LANGUAGE = USERENV('LANG')     -- Bug Fix #5442182
             AND TL.CATEGORY = RSC.CATEGORY
             AND RSC.RESOURCE_ID = to_number(p_object1_id1)
             AND '#' = p_object1_id2;
Line: 1893

      Select name From OKX_PARTY_CONTACTS_V Where id1 = to_number(p_object1_id1) and id2 = p_object1_id2;
Line: 1896

      SELECT TL.RESOURCE_NAME
      FROM JTF_RS_SALESREPS S
          ,JTF_RS_RESOURCE_EXTNS_TL TL   -- Bug Fix #5442182
      WHERE S.salesrep_id = TO_NUMBER(p_object1_id1)
      AND   S.org_id = p_org_id
      AND   S.RESOURCE_ID = TL.RESOURCE_ID
      AND   TL.LANGUAGE = USERENV('LANG');
Line: 1985

      Select EMAIL_ADDRESS Email
      From   OKS_CONTACT_POINTS_V
      Where
        OWNER_TABLE_ID = to_number(p_owner_table_id)
        and (CONTACT_POINT_TYPE = 'EMAIL' or CONTACT_POINT_TYPE = 'Email');
Line: 1993

      Select
        rol.DNZ_CHR_ID ChrID,
        rol.RLE_CODE RleCode,
        con.JTOT_OBJECT1_CODE OwnerTableID,
        con.ROLE Role,
        con.START_DATE StartDate,
        con.END_DATE EndDate,
        con.object1_id1 ContactID,
        con.object1_id2 ID2
      from
        OKC_CONTACTS_V con,
        OKC_K_PARTY_ROLES_B rol
      where
        rol.dnz_chr_id = to_number(p_contract_id_arg)
        and rol.RLE_CODE = p_party_rle_code_arg
        and rol.CLE_ID is NULL
        and rol.JTOT_OBJECT1_CODE in ('OKX_PARTY','OKX_VENDOR','OKX_OPERUNIT')
        and con.CPL_ID = rol.ID;
Line: 2013

            SELECT org_id
            FROM   OKC_K_HEADERS_ALL_B
            WHERE  id = contract_id;
Line: 2018

            SELECT email_address
            FROM JTF_RS_SALESREPS
            WHERE salesrep_id = p_contact_id
            AND   org_id = p_org_id;
Line: 2024

            SELECT email_address
            FROM OKX_RESOURCES_V
            WHERE id1 = p_contact_id;
Line: 2029

            SELECT email_address
            FROM JTF_RS_GROUPS_B
            WHERE group_id = p_contact_id;
Line: 2145

      Select
        OWNER_TABLE_ID Id,
        decode(
          CONTACT_POINT_TYPE,
          'EMAIL',
          'Email',
          'PHONE',
          'Phone',
          'FAX',
          'Fax',
          CONTACT_POINT_TYPE
        ) ContactType,
        EMAIL_ADDRESS Email,
        PHONE_LINE_TYPE PhoneLineType,
        PHONE_COUNTRY_CODE CountryCode,
        PHONE_AREA_CODE  AreaCode,
        PHONE_NUMBER pNumber,
        PHONE_EXTENSION Extension
      From
        OKS_CONTACT_POINTS_V
      Where  OWNER_TABLE_ID = to_number(p_contact_id_arg);
Line: 2218

      Select
        ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,L.ADDRESS1,L.ADDRESS2,L.ADDRESS3, L.ADDRESS4,L.CITY
        ,L.COUNTY,L.STATE,L.PROVINCE,L.POSTAL_CODE,NULL,L.COUNTRY,NULL, NULL,NULL,NULL,NULL,NULL,NULL
        ,'N','N',300,1,1) Address
      From
        OKC_K_LINES_B okcLn,
        OKC_K_HEADERS_ALL_B hdr,
        HZ_CUST_SITE_USES_ALL CS,
        HZ_PARTY_SITES PS,
        HZ_LOCATIONS L,
        HZ_CUST_ACCT_SITES_ALL CA,
        OKX_CUSTOMER_ACCOUNTS_V cus
      Where
        okcLn.id = p_party_roles_cle_id
        AND hdr.id = okcLn.dnz_chr_id
        and cus.ID1 = okcLn.CUST_ACCT_ID
        and cus.ID2 = '#'
        and CA.CUST_ACCOUNT_ID = cus.PARTY_ID
        AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
        AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
        AND PS.LOCATION_ID = L.LOCATION_ID
        AND CS.SITE_USE_CODE = 'BILL_TO'
        AND CS.ORG_ID = hdr.org_id
        AND rownum < 2;
Line: 2269

      select name
      from OKX_PARTIES_V
      where id1 = to_number(p_itm_obj1_arg) and id2 = p_itm_obj2_arg;
Line: 2277

      select name
      from OKX_CUSTOMER_ACCOUNTS_V
      where id1 = to_number(p_itm_obj1_arg) and id2 = p_itm_obj2_arg;
Line: 2285

       select MTL.concatenated_segments
       from MTL_SYSTEM_ITEMS_KFV MTL,
           CSI_ITEM_INSTANCES CSI
       where csi.instance_id =  p_itm_obj1_arg
       and mtl.inventory_item_id = csi.inventory_item_id
       and mtl.organization_id =csi.inv_master_organization_id;
Line: 2298

      select concatenated_segments
      from MTL_SYSTEM_ITEMS_KFV
      where
        inventory_item_id = to_number(p_itm_obj1_arg)
        and organization_id = to_number(p_itm_obj2_arg)
        and serviceable_product_flag='Y';
Line: 2309

      select name
      from  CS_SYSTEMS_ALL_TL
      where system_id = to_number(p_itm_obj1_arg)
      and   language = userenv('lang');
Line: 2319

      select party_site_number||'-'||name
      from OKX_PARTY_SITES_V
      where
        id1 = to_number(p_itm_obj1_arg)
        and id2 = p_itm_obj2_arg;
Line: 2415

      Select
        Fnd.Meaning RenewalType
      From
        OKC_K_LINES_B okcLn,
        FND_LOOKUPS Fnd
      Where
        okcLn.ID = to_number(p_line_id_arg)
        And Fnd.Lookup_Code = okcLn.LINE_RENEWAL_TYPE_CODE
        And Fnd.Lookup_Type = 'OKC_LINE_RENEWAL_TYPE';
Line: 2427

      Select  nvl(sum(line.PRICE_NEGOTIATED),0) LineAmount, line.currency_code Denomination
      From OKC_K_LINES_B line
      Where line.CLE_ID =  to_number(p_line_id_arg)
      Group By line.currency_code;
Line: 2435

      /* Select
        oksLnTL.INVOICE_TEXT InvoiceText,
        oksLnB.INV_PRINT_FLAG InvoicePrintFlg
      From
        OKS_K_LINES_TL oksLnTL, OKS_K_LINES_B oksLnB
      Where
        oksLnB.Cle_Id = to_number(p_line_id_arg)
        And oksLnTL.ID = oksLnB.ID; */
Line: 2445

      Select
        oksLnTL.INVOICE_TEXT InvoiceText
       ,fnd.MEANING InvoicePrintFlg
      From
        OKS_K_LINES_TL oksLnTL
       ,OKS_K_LINES_B oksLnB
       ,FND_LOOKUPS fnd
      Where oksLnB.Cle_Id = to_number(p_line_id_arg)
        And oksLnTL.ID = oksLnB.ID
        And language = userenv('LANG')
        And fnd.LOOKUP_TYPE = 'OKS_Y_N'
        And fnd.LOOKUP_CODE =  DECODE(oksLnB.INV_PRINT_FLAG,'N','N','Y') ;
Line: 2461

        Select oksLn.tax_classification_code TaxCode
               ,lok.meaning TaxStatus
               ,oksLn.EXEMPT_CERTIFICATE_NUMBER TaxExcemptCertificate
               ,oksLn.EXEMPT_REASON_CODE TaxExcemptCode
        From   OKS_K_LINES_B oksLn,
               FND_LOOKUPS  lok
        Where  oksLn.Cle_Id = to_number(p_line_id_arg)
        and    lok.lookup_type      =  'ZX_EXEMPTION_CONTROL'
        and    lok.lookup_code      =  oksLn.tax_status;
Line: 2471

     /* Select
        oksLn.TAX_STATUS TaxStatusCode,
        lok.NAME  TaxStatus,
        to_char(oksLn.TAX_EXEMPTION_ID) TaxExcemptCode,
        tcd.NAME TaxCode
      From
        OKX_TAX_CODES_V tcd,
        OKX_LOOKUPS_V lok,
        OKS_K_LINES_B oksLn
      Where
        oksLn.Cle_Id = to_number(p_line_id_arg)
        and lok.Lookup_Code = oksLn.TAX_STATUS
        and lok.Lookup_type = 'TAX_CONTROL_FLAG'
        and oksLn.TAX_CODE = tcd.Id1(+); */
Line: 2488

      Select
        ln.ID CoverageLineID,
        ln.NAME CoverageName,
        ln.ITEM_DESCRIPTION CoverageDescription,
        ln.START_DATE CoverageStartDate,
        ln.END_DATE CoverageEndDate,
        decode(ln.lse_id,15,'Y','N') Warranty_YN
      From
        OKC_K_LINES_V ln
      Where
        ln.CLE_ID = to_number(p_line_id_arg)
        and ln.LSE_ID in (2,15,20);
Line: 2504

      select
        oks.coverage_ID CoverageLineID,
        cov.NAME CoverageName,
        cov.ITEM_DESCRIPTION CoverageDescription,
        ln.START_DATE CoverageStartDate,
        ln.END_DATE CoverageEndDate,
        decode(cov.lse_id,15,'Y','N') Warranty_YN
      From
        OKC_K_LINES_b ln,
        OKS_K_LINES_b oks,
        OKC_K_LINES_V cov
      Where
        ln.ID = to_number(p_line_id_arg)
        and ln.id = oks.cle_id
        and cov.id = oks.coverage_id
        and cov.LSE_ID in (2,15,20);
Line: 2523

      Select cvt.MEANING CoverageType
      From
        OKS_COV_TYPES_V cvt,
        OKS_K_LINES_B   oksLn
      Where
        oksLn.CLE_ID = p_coverage_id
        and cvt.CODE = oksLn.COVERAGE_TYPE;
Line: 2533

      Select
        ln_c.ID CoverageID,
        to_char(oksLn.EXCEPTION_COV_ID) ExcCoverageLineID,
        ln.NAME ExcCoverageName,
        ln.item_description Description,
        ln.START_DATE StartDate,
        ln.END_DATE EndDate,
        decode(ln.lse_id,15,'Y','N') Warranty_YN
      From
        OKC_K_LINES_V ln,
        OKC_K_LINES_B ln_c,
        OKS_K_LINES_B oksLn
      Where
        ln_c.CLE_ID = to_number(p_line_id_arg) and
        ln.LSE_ID in (2,15,20)
        and oksLn.CLE_ID = ln_c.ID
        and ln.ID = oksLn.EXCEPTION_COV_ID;
Line: 2554

     SELECT
        oksrv.Coverage_ID CoverageID,
        to_char(okscov.EXCEPTION_COV_ID) ExcCoverageLineID,
        expcov.NAME ExcCoverageName,
        expcov.item_description Description,
        expcov.START_DATE StartDate,
        expcov.END_DATE EndDate,
        decode(expcov.lse_id,15,'Y','N') Warranty_YN
      from
           okc_k_lines_b srv,
           oks_k_lines_b oksrv,
           oks_k_lines_b okscov,
           okc_k_lines_v expcov
     Where srv.id = to_number(p_line_id_arg)
     AND srv.id = oksrv.cle_id
     AND oksrv.coverage_id = okscov.cle_id
     AND okscov.EXCEPTION_COV_ID = expcov.id;
Line: 2575

     Select
        cvt.MEANING CoverageType
      From
        OKS_COV_TYPES_V cvt,
        OKS_K_LINES_B oksLn
      Where
        oksLn.CLE_ID = p_excep_coverage_type_id
        and cvt.CODE = oksLn.COVERAGE_TYPE;
Line: 2587

      SELECT unit_of_measure_tl
      FROM mtl_units_of_measure_tl
      WHERE uom_code = p_period_unit
      AND language = userenv('LANG');
Line: 2592

      /*SELECT fndLkups.meaning
      FROM   fnd_lookups fndLkups
      WHERE  fndLkups.lookup_code = p_period_unit
             and fndLkups.lookup_type = 'EGO_SRV_DURATION_PERIOD';*/
Line: 2599

      /* Select
        ln.ID  CoveredLevelId,
        ln.LSE_ID lseID,
        lnp.LINE_NUMBER||'.'||ln.LINE_NUMBER LineNumber,
        ln.START_DATE  StartDate,
        ln.END_DATE EndDate,
        ln.DATE_TERMINATED Terminated,
        decode (
          itm.JTOT_OBJECT1_CODE,
          'OKX_CUSTPROD',
          'Covered Product',
          'OKX_COVITEM',
          'Covered Item',
          'OKX_PARTYSITE',
          'Covered Site',
          'OKX_COVSYST',
          'Covered System',
          'OKX_CUSTACCT',
          'Covered Customer',
          'OKX_PARTY',
          'Covered Party',
          itm.JTOT_OBJECT1_CODE
        ) Coverage,
        itm.object1_id1 ObjId1,
        itm.object1_id2 ObjId2,
        itm.JTOT_OBJECT1_CODE JtotObj
      From
        okc_k_items itm,
        okc_k_lines_b ln,
        okc_k_lines_b lnp
      where
        lnp.ID = to_number(p_line_id_arg)
        and ln.cle_id = lnp.ID
        and ln.lse_id in (7,8,9,10,11,18,25,35)
        and itm.cle_id = ln.id; */
Line: 2637

        Select
        ln.ID  CoveredLevelId,
        ln.LSE_ID lseID,
        lnp.LINE_NUMBER||'.'||ln.LINE_NUMBER LineNumber,
        ln.START_DATE  StartDate,
        ln.END_DATE EndDate,
        ln.DATE_TERMINATED Terminated,
        style.name Coverage,
        itm.object1_id1 ObjId1,
        itm.object1_id2 ObjId2,
        itm.JTOT_OBJECT1_CODE JtotObj
      From
        okc_k_items itm,
        okc_k_lines_b ln,
        okc_k_lines_b lnp,
        okc_line_styles_tl style
      where
        lnp.ID = to_number(p_line_id_arg)
        and ln.cle_id = lnp.ID
        and ln.lse_id in (7,8,9,10,11,18,25,35)
        and itm.cle_id = ln.id
        and style.id = ln.lse_id
        and style.language = userenv('LANG');
Line: 2663

      Select
        Fnd.Meaning RenewalType
      From
        OKC_K_LINES_B okcLn,
        FND_LOOKUPS Fnd
      Where
        okcLn.Id = p_covered_level_id
        AND Fnd.Lookup_Code = okcLn.LINE_RENEWAL_TYPE_CODE
        And Fnd.Lookup_Type = 'OKC_LINE_RENEWAL_TYPE';
Line: 2676

      /* Select
        rol.CLE_ID ContractLineID,
        con.role Role,
        con.START_DATE StartDate,
        con.END_DATE EndDate,
        OKC_UTIL.GET_NAME_FROM_JTFV(con.jtot_object1_code,con.object1_id1,con.object1_id2) ContactName
      from
        OKC_CONTACTS_V con,
        OKC_K_PARTY_ROLES_B rol,
        OKC_K_LINES_B ln
      where
        ln.ID = to_number(p_line_id_arg)
        and rol.cle_id = ln.ID
        and rol.dnz_chr_id  = ln.dnz_chr_id
        and con.CPL_ID = rol.ID; */
Line: 2693

        SELECT    FNDCONT.MEANING Role,
                  HZP.PARTY_NAME ContactName,
                  CONT.START_DATE StartDate,
                  CONT.END_DATE EndDate,
                  PR.CLE_ID ContractLineID
        FROM   OKC_K_LINES_B LINE,
               OKC_K_PARTY_ROLES_B PR,
               FND_LOOKUPS FNDCONT,
               OKC_CONTACTS CONT,
               HZ_PARTIES HZP,
               HZ_RELATIONSHIPS HZR,
               HZ_CUST_ACCOUNT_ROLES ACCROLE

        WHERE LINE.ID =   to_number(p_line_id_arg)
        AND PR.CLE_ID =  LINE.ID
        AND PR.DNZ_CHR_ID = LINE.DNZ_CHR_ID
        AND PR.RLE_CODE IN ('CUSTOMER','THIRD_PARTY','SUBSCRIBER')
        AND CONT.CRO_CODE = FNDCONT.LOOKUP_CODE
        AND CONT.JTOT_OBJECT1_CODE IN ('OKX_CONTADMN','OKX_CONTBILL','OKX_CONTSHIP','OKX_CONTTECH')
        AND FNDCONT.LOOKUP_TYPE = 'OKC_CONTACT_ROLE'
        AND PR.ID = CONT.CPL_ID
        AND TO_NUMBER(CONT.OBJECT1_ID1) = ACCROLE.CUST_ACCOUNT_ROLE_ID
        AND ACCROLE.PARTY_ID = HZR.PARTY_ID
        AND ACCROLE.ROLE_TYPE = 'CONTACT'
        AND HZR.RELATIONSHIP_CODE IN ('CONTACT_OF','EMPLOYEE_OF')
        AND HZR.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
        AND HZR.SUBJECT_ID = HZP.PARTY_ID  ;
Line: 2931

      Select
        fnd.MEANING WarrantyInheritance
      From
        FND_LOOKUPS fnd,
        OKS_K_LINEs_B oksLn
      Where
        oksLn.CLE_ID = to_number(p_coverage_ID_arg)
        and fnd.LOOKUP_TYPE = 'OKSWHETYPE'
        and fnd.LOOKUP_CODE = oksLn.INHERITANCE_TYPE;
Line: 2943

      Select
        oksLn.PROD_UPGRADE_YN FreeUpgrade,
        Fnd.MEANING TransferAllowed
      From
        OKS_K_LINES_B oksLn,
        FND_LOOKUPS   Fnd
      Where
        oksLn.CLE_ID =  to_number(p_coverage_ID_arg)
        AND Fnd.lookup_code = oksLn.TRANSFER_OPTION
        AND Fnd.lookup_type='OKS_TRANSFER_OPTIONS';
Line: 2956

      Select
        ln.ID BusProcessID,
        bus.NAME Name
      From
        OKC_K_ITEMS itm,
        OKX_BUS_PROCESSES_V bus,
        OKC_K_LINES_V ln
      Where
        ln.CLE_ID = to_number(p_coverage_ID_arg)
        and ln.LSE_ID in (3,16 ,21)
        and ln.ID = itm.CLE_ID
        and itm.JTOT_OBJECT1_CODE = 'OKX_BUSIPROC'
        and bus.ID1 = itm.OBJECT1_ID1
        and bus.ID2 = itm.OBJECT1_ID2;
Line: 2973

        Select
          oksLn.OFFSET_DURATION OffSetDuration,
          Fnd.Meaning OffsetPeriod
        From
          OKS_K_LINES_B oksLn,
          FND_LOOKUPS Fnd
        Where
         --  oksLn.dnz_chr_id  = to_number(p_contract_ID_arg)
           oksLn.CLE_ID = business_proc_id
          and Fnd.lookup_type = 'EGO_SRV_DURATION_PERIOD'
          and Fnd.lookup_code = oksLn.OFFSET_PERIOD;
Line: 2987

        Select
          prl.NAME PriceList
        From
          OKX_LIST_HEADERS_V prl,
          OKC_K_LINES_v okCLn
        Where
          okcLn.ID = business_proc_id
          and prl.ID1 = okcLn.Price_list_id;
Line: 2998

        Select
          dis.NAME Discount
        From
          OKX_LIST_HEADERS_V dis,
          OKS_K_LINES_B oksLn
        Where
          oksLn.CLE_ID = business_proc_id
          and dis.ID1 = oksLn.DISCOUNT_LIST;
Line: 3136

      Select
        tmz.NAME TimeZone
      From
        OKS_COVERAGE_TIMEZONES cvr,
        OKX_TIMEZONES_V tmz
      Where
        cvr.CLE_ID = to_number(p_bus_proc_ID_arg)
        and tmz.TIMEZONE_ID = cvr.TIMEZONE_ID
       -- and rownum < 2;
Line: 3149

      select decode(length(CovTImes.start_hour),1,'0' ||CovTImes.start_hour,CovTImes.start_hour) ||':'||
             decode(length(CovTImes.start_minute),1,CovTImes.start_minute || '0',CovTImes.start_minute) StartTime,
             decode(length(CovTImes.end_hour),1,'0' ||CovTImes.end_hour,CovTImes.end_hour) ||':'||
             decode(length(CovTImes.end_minute),1,CovTImes.end_minute || '0',CovTImes.end_minute) EndTime,
             CovTImes.Sunday_YN Sun,
             CovTImes.Monday_YN Mon,
             CovTImes.Tuesday_YN Tue,
             CovTImes.Wednesday_YN Wed,
             CovTImes.Thursday_YN Thr,
             CovTImes.Friday_YN Fri,
             CovTImes.Saturday_YN Sat
      from   oks_coverage_timeZones CovTimeZones,
             okx_timezones_v OkxTimeZones,
             oks_coverage_times CovTimes
      where  CovTimeZones.timezone_id = OkxTimeZones.timezone_id
             and CovTimeZones.cle_id = to_number(p_bus_proc_ID_arg)
             and CovTImes.COV_TZE_LINE_ID=CovTimeZones.id
             and CovTimeZones.default_yn = 'Y';
Line: 3170

      select IncidentSeverities.Name SeverityName,
             ServiceLines.react_time_name Severity,
             ServiceLines.work_thru_yn WorkThrough,
             ServiceLines.react_active_yn Active,
             ActTimes.uom_code UOM,
             NVL2 (ActTimes.sun_duration, (floor(ActTimes.sun_duration/60))||':'||decode(LENGTH(mod (ActTimes.sun_duration, 60)), 1, '0'||(mod (ActTimes.sun_duration, 60)), (mod (ActTimes.sun_duration, 60))), null ) Sunday,
             NVL2 (ActTimes.mon_duration, (floor(ActTimes.mon_duration/60))||':'||decode(LENGTH(mod (ActTimes.mon_duration, 60)), 1, '0'||(mod (ActTimes.mon_duration, 60)), (mod (ActTimes.mon_duration, 60))), null ) Monday,
             NVL2 (ActTimes.tue_duration, (floor(ActTimes.tue_duration/60))||':'||decode(LENGTH(mod (ActTimes.tue_duration, 60)), 1, '0'||(mod (ActTimes.tue_duration, 60)), (mod (ActTimes.tue_duration, 60))), null ) Tuesday,
             NVL2 (ActTimes.wed_duration, (floor(ActTimes.wed_duration/60))||':'||decode(LENGTH(mod (ActTimes.wed_duration, 60)), 1, '0'||(mod (ActTimes.wed_duration, 60)), (mod (ActTimes.wed_duration, 60))), null ) Wednesday,
             NVL2 (ActTimes.thu_duration, (floor(ActTimes.thu_duration/60))||':'||decode(LENGTH(mod (ActTimes.thu_duration, 60)), 1, '0'||(mod (ActTimes.thu_duration, 60)), (mod (ActTimes.thu_duration, 60))), null ) Thursday,
             NVL2 (ActTimes.fri_duration, (floor(ActTimes.fri_duration/60))||':'||decode(LENGTH(mod (ActTimes.fri_duration, 60)), 1, '0'||(mod (ActTimes.fri_duration, 60)), (mod (ActTimes.fri_duration, 60))), null ) Friday,
             NVL2 (ActTimes.sat_duration, (floor(ActTimes.sat_duration/60))||':'||decode(LENGTH(mod (ActTimes.sat_duration, 60)), 1, '0'||(mod (ActTimes.sat_duration, 60)), (mod (ActTimes.sat_duration, 60))), null ) Saturday
      from   oks_action_time_types ActTimeTypes,
             oks_action_times ActTimes,
             okc_k_lines_v CoreLines,
             oks_k_lines_v ServiceLines,
             OKX_INCIDENT_SEVERITS_V IncidentSeverities
      where  ActTimeTypes.cle_id = CoreLines.id
             and ActTimeTypes.action_type_code='RCN'
             and ActTimeTypes.cle_id = ActTimes.cle_id
             and ActTimeTypes.id = ActTimes.cov_action_type_id
             and ServiceLines.cle_id = CoreLines.id
             and IncidentSeverities.id1 = ServiceLines.incident_severity_id
             and CoreLines.cle_id = to_number(p_bus_proc_ID_arg);
Line: 3197

      select IncidentSeverities.Name SeverityName,
             ServiceLines.react_time_name Severity,
             ServiceLines.work_thru_yn WorkThrough,
             ServiceLines.react_active_yn Active,
             ActTimes.uom_code UOM,
             NVL2 (ActTimes.sun_duration, (floor(ActTimes.sun_duration/60))||':'||decode(LENGTH(mod (ActTimes.sun_duration, 60)), 1, '0'||(mod (ActTimes.sun_duration, 60)), (mod (ActTimes.sun_duration, 60))), null ) Sunday,
             NVL2 (ActTimes.mon_duration, (floor(ActTimes.mon_duration/60))||':'||decode(LENGTH(mod (ActTimes.mon_duration, 60)), 1, '0'||(mod (ActTimes.mon_duration, 60)), (mod (ActTimes.mon_duration, 60))), null ) Monday,
             NVL2 (ActTimes.tue_duration, (floor(ActTimes.tue_duration/60))||':'||decode(LENGTH(mod (ActTimes.tue_duration, 60)), 1, '0'||(mod (ActTimes.tue_duration, 60)), (mod (ActTimes.tue_duration, 60))), null ) Tuesday,
             NVL2 (ActTimes.wed_duration, (floor(ActTimes.wed_duration/60))||':'||decode(LENGTH(mod (ActTimes.wed_duration, 60)), 1, '0'||(mod (ActTimes.wed_duration, 60)), (mod (ActTimes.wed_duration, 60))), null ) Wednesday,
             NVL2 (ActTimes.thu_duration, (floor(ActTimes.thu_duration/60))||':'||decode(LENGTH(mod (ActTimes.thu_duration, 60)), 1, '0'||(mod (ActTimes.thu_duration, 60)), (mod (ActTimes.thu_duration, 60))), null ) Thursday,
             NVL2 (ActTimes.fri_duration, (floor(ActTimes.fri_duration/60))||':'||decode(LENGTH(mod (ActTimes.fri_duration, 60)), 1, '0'||(mod (ActTimes.fri_duration, 60)), (mod (ActTimes.fri_duration, 60))), null ) Friday,
             NVL2 (ActTimes.sat_duration, (floor(ActTimes.sat_duration/60))||':'||decode(LENGTH(mod (ActTimes.sat_duration, 60)), 1, '0'||(mod (ActTimes.sat_duration, 60)), (mod (ActTimes.sat_duration, 60))), null ) Saturday
      from   oks_action_time_types ActTimeTypes,
             oks_action_times ActTimes,
             okc_k_lines_v CoreLines,
             oks_k_lines_v ServiceLines,
             OKX_INCIDENT_SEVERITS_V IncidentSeverities
      where  ActTimeTypes.cle_id = CoreLines.id
             and ActTimeTypes.action_type_code='RSN'
             and ActTimeTypes.cle_id = ActTimes.cle_id
             and ActTimeTypes.id = ActTimes.cov_action_type_id
             and ServiceLines.cle_id = CoreLines.id
             and IncidentSeverities.id1 = ServiceLines.incident_severity_id
             and CoreLines.cle_id =  to_number(p_bus_proc_ID_arg);
Line: 3224

        select
          pty.cle_id businessprocessid,
          pty.rle_code rlecode,
          con.role resourcetype,
          C.LAST_NAME name2
        from
          okc_contacts_v con,
          okc_k_party_roles_b pty ,
          JTF_RS_RESOURCE_EXTNS RSC ,
          PO_VENDOR_SITES_ALL S ,
          PO_VENDOR_CONTACTS C
        where
          pty.cle_id = to_number(p_bus_proc_ID_arg)
          and pty.dnz_chr_id = to_number(p_contract_ID_arg)
          and con.cpl_id = pty.id
          and con.jtot_object1_code ='okx_resource'
          AND con.object1_id1 = RSC.RESOURCE_ID
          AND con.object1_id2 = '#'
          AND RSC.CATEGORY = 'SUPPLIER_CONTACT'
          AND C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
          AND S.VENDOR_SITE_ID = C.VENDOR_SITE_ID
          AND S.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
        union all
        select
          pty.cle_id businessprocessid,
          pty.rle_code rlecode,
          con.role resourcetype,
          EMP.FULL_NAME name2
        from
          okc_contacts_v con,
          okc_k_party_roles_b pty ,
          JTF_RS_RESOURCE_EXTNS RSC ,
          FND_USER U ,
          OKX_PER_ALL_PEOPLE_V EMP
        where
          pty.cle_id = to_number(p_bus_proc_ID_arg)
          and pty.dnz_chr_id = to_number(p_contract_ID_arg)
          and con.cpl_id = pty.id
          and con.jtot_object1_code ='okx_resource'
          and RSC.CATEGORY = 'EMPLOYEE'
          AND EMP.PERSON_ID = RSC.SOURCE_ID
          AND U.USER_ID = RSC.USER_ID
          AND con.object1_id1 = RSC.RESOURCE_ID
          AND con.object1_id2 = '#'
        union all
        select
          pty.cle_id businessprocessid,
          pty.rle_code rlecode,
          con.role resourcetype,
          PARTY.PARTY_NAME name2
        from
          okc_contacts_v con,
          okc_k_party_roles_b pty ,
          JTF_RS_RESOURCE_EXTNS RSC ,
          FND_USER U ,
          HZ_PARTIES PARTY
        where
          pty.cle_id = to_number(p_bus_proc_ID_arg)
          and pty.dnz_chr_id = to_number(p_contract_ID_arg)
          and con.cpl_id = pty.id
          and con.jtot_object1_code ='okx_resource'
          AND RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
          AND PARTY.PARTY_ID = RSC.SOURCE_ID
          AND U.USER_ID = RSC.USER_ID
          AND con.object1_id1 = RSC.RESOURCE_ID
          AND con.object1_id2 = '#'
        union all
        select
          pty.cle_id businessprocessid,
          pty.rle_code rlecode,
          con.role resourcetype,
          SRP.NAME name2
        from
          okc_contacts_v con,
          okc_k_party_roles_b pty ,
          JTF_RS_RESOURCE_EXTNS RSC ,
          FND_USER U ,
          JTF_RS_SALESREPS SRP
        where
          pty.cle_id = to_number(p_bus_proc_ID_arg)
          and pty.dnz_chr_id = to_number(p_contract_ID_arg)
          and con.cpl_id = pty.id
          and con.jtot_object1_code ='okx_resource'
          and RSC.CATEGORY = 'OTHER'
          AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
          AND U.USER_ID = RSC.USER_ID
          AND SRP.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
          AND con.object1_id1 = RSC.RESOURCE_ID
          AND con.object1_id2 = '#'; */
Line: 3317

         SELECT  PartyRoles.cle_id businessprocessid
                 ,PartyRoles.rle_code rlecode
                 ,RoleLookup.meaning resourcetype
                 ,DECODE (RSC.CATEGORY
                 ,'SUPPLIER_CONTACT', DECODE(substr(vendor_contact.PERSON_FIRST_NAME,1,15),
                     NULL, substr(vendor_contact.PERSON_LAST_NAME,1,15),
                     substr(vendor_contact.PERSON_LAST_NAME,1,15)||', '||substr(vendor_contact.PERSON_FIRST_NAME,1,15))
                 ,'EMPLOYEE', employee.full_name
                 ,'PARTNER', hz_party.party_name
                 ,'PARTY', hz_party.party_name
                 ) name2

          FROM  OKC_K_LINES_B okcline,
               JTF_RS_RESOURCE_EXTNS RSC,
               OKC_CONTACTS Contacts,
               FND_LOOKUPS RoleLookup,
               OKC_K_PARTY_ROLES_B PartyRoles,
               AP_SUPPLIER_CONTACTS ap_supp_contact,
               HZ_PARTIES vendor_contact,
               PER_ALL_PEOPLE_F employee,
               HZ_PARTIES hz_party
          WHERE
               okcline.ID = to_number(p_bus_proc_ID_arg)
               AND PartyRoles.cle_id = okcline.ID
               AND  PartyRoles.dnz_chr_id = okcline.dnz_chr_id
               AND Contacts.cpl_id = PartyRoles.id
               AND Contacts.OBJECT1_ID1 = RSC.RESOURCE_ID
               AND Contacts.JTOT_OBJECT1_CODE ='OKX_RESOURCE'
               AND RoleLookup.lookup_type = 'OKC_CONTACT_ROLE'
               AND Contacts.CRO_CODE = RoleLookup.lookup_code
               AND RSC.SOURCE_ID = ap_supp_contact.vendor_contact_id (+)
               AND ap_supp_contact.PER_PARTY_ID = vendor_contact.party_id (+)
               AND RSC.SOURCE_ID = employee.person_id (+)
               AND trunc(sysdate)between employee.effective_start_date (+) AND employee.effective_end_date (+)
               AND RSC.SOURCE_ID = hz_party.PARTY_ID (+)
               UNION ALL
                 SELECT PartyRoles.cle_id businessprocessid
                ,PartyRoles.rle_code rlecode
                ,RoleLookup.meaning resourcetype
                ,resource_group.group_name name2
          FROM OKC_K_LINES_B okcline,
              JTF_RS_GROUPS_TL resource_group,
              OKC_CONTACTS Contacts,
              FND_LOOKUPS RoleLookup,
              OKC_K_PARTY_ROLES_B PartyRoles

          WHERE  okcline.ID = to_number(p_bus_proc_ID_arg)
              AND PartyRoles.cle_id = okcline.ID
              AND  PartyRoles.dnz_chr_id = okcline.dnz_chr_id
              AND Contacts.cpl_id = PartyRoles.id
              AND Contacts.OBJECT1_ID1 = resource_group.group_id
              AND Contacts.JTOT_OBJECT1_CODE ='OKS_RSCGROUP'
              AND resource_group.language = USERENV ('LANG')
              AND RoleLookup.lookup_type = 'OKC_CONTACT_ROLE'
              AND Contacts.CRO_CODE = RoleLookup.lookup_code;
Line: 3375

        Select
          ln.CLE_ID BusinessProceeID,
          ln.ID BillTypeID,
          bil.NAME,
          trn.NAME||'-'||csl.meaning BillType,
          csl.MEANING,
          to_char(oksLn.DISCOUNT_AMOUNT) MaxAmount,
          to_char(oksLn.DISCOUNT_PERCENT) Per_Covered
        From
          CS_LOOKUPS csl,
          OKX_TRANSACTION_TYPES_V trn,
          OKX_TXN_BILLING_TYPES_V bil,
          OKC_K_ITEMS itm,
          OKC_K_LINES_B ln,
          OKS_K_LINES_B oksLn
        Where
          ln.CLE_ID = to_number(p_bus_proc_ID_arg)
          AND ln.LSE_ID in (5,23,59) -- Fix #4238239
          and itm.CLE_ID = ln.ID
          and itm.jtot_object1_code = 'OKX_BILLTYPE'
          and bil.ID1 = itm.OBJECT1_ID1
          and bil.ID2 = itm.OBJECT1_ID2
          and bil.TRANSACTION_TYPE_ID = trn.TRANSACTION_TYPE_ID
          and csl.LOOKUP_CODE = bil.BILLING_TYPE
          and csl.LOOKUP_TYPE   = 'MTL_SERVICE_BILLABLE_FLAG'
          and oksLn.Cle_Id = ln.ID;
Line: 3404

        SELECT  fnd.meaning,
                mtl.UNIT_OF_MEASURE,
                bsh.FLAT_RATE,
                bsh.PERCENT_OVER_LIST_PRICE

        FROM   oks_billrate_schedules  bsh ,
               okc_k_lines_b lines,
               mtl_units_of_measure_tl mtl,
               fnd_lookups fnd
        WHERE  bsh.cle_id = lines.id
               and lines.lse_id in (6,24,60)
               and lines.cle_id = p_bill_type_id
               and mtl.UOM_CODE(+) =  bsh.UOM
               and mtl.language(+) = userenv('LANG')
               and fnd.lookup_type(+) = 'OKS_BILLING_RATE'
               and fnd.lookup_code(+) = bsh.bill_rate_code
               and bsh.holiday_yn = 'N';
Line: 3644

    /*  Select oksLn.INVOICE_TEXT InvoiceText,
             oksLn.INV_PRINT_FLAG InvoicePrintFlg
      From   OKS_K_LINES_V oksLn
      Where  oksLn.Cle_Id = to_number(p_line_id_arg); */
Line: 3649

      Select
        oksLnTL.INVOICE_TEXT InvoiceText
       ,fnd.MEANING InvoicePrintFlg
      From
        OKS_K_LINES_TL oksLnTL
       ,OKS_K_LINES_B oksLnB
       ,FND_LOOKUPS fnd
      Where oksLnB.Cle_Id = to_number(p_line_id_arg)
        And oksLnTL.ID = oksLnB.ID
        And language = userenv('LANG')
        And fnd.LOOKUP_TYPE = 'OKS_Y_N'
        And fnd.LOOKUP_CODE =  DECODE(oksLnB.INV_PRINT_FLAG,'N','N','Y') ;
Line: 3664

      Select nvl(sum(line.PRICE_NEGOTIATED),0) LineAmount
      From OKC_K_LINES_B line
      Where line.CLE_ID =  to_number(p_line_id_arg);
Line: 3670

     Select hdr.org_id
     FROM   okc_k_lines_b ln,
            okc_k_headers_All_b hdr
     WHERE  ln.id  = to_number(p_line_id)
     AND    hdr.id = ln.dnz_chr_id;
Line: 3679

        Select lok.meaning TaxStatus
               ,oksLn.tax_classification_code TaxCode
        From   OKS_K_LINES_B oksLn,
               FND_LOOKUPS  lok
        Where  oksLn.Cle_Id = to_number(p_line_id_arg)
        and    lok.lookup_type      =  'ZX_EXEMPTION_CONTROL'
        and    lok.lookup_code      =  oksLn.tax_status;
Line: 3687

     /* Select
        lok.NAME  TaxStatus,
        tcd.NAME TaxCode
      From
        OKX_TAX_CODES_V tcd,
        OKX_LOOKUPS_V lok,
        OKS_K_LINES_B oksLn
      Where
        oksLn.Cle_Id = to_number(p_line_id_arg)
        and lok.Lookup_Code = oksLn.TAX_STATUS
        and lok.Lookup_type = 'TAX_CONTROL_FLAG'
        and oksLn.TAX_CODE = tcd.Id1(+); */
Line: 3702

      Select
        decode(to_char(oksLn.AVERAGING_INTERVAL),Null,'N','Y') AveragingAllowed,
        to_char(oksLn.AVERAGING_INTERVAL) AveragingInterval,
        decode(oksLn.SETTLEMENT_INTERVAL,1,'Y','N') SettlementAgainstActualUsage,
        decode(
          oksLn.USAGE_TYPE,
          'FRT',
          'Fixed Per Period',
          'VRT',
          'Actual Per Period',
          'QTY',
          'Actual By Quantity',
          'NPR',
          'Negotiated Price',
          oksLn.USAGE_TYPE
        )UsageType
      From
        OKX_UNITS_OF_MEASURE_V uom,
        OKS_K_LINES_B oksLn
      Where
        oksLn.CLE_ID = to_number(p_line_id_arg)
        and uom.UOM_CODE(+) = oksLn.USAGE_PERIOD;
Line: 3727

      SELECT sub_line.line_number          LineNumber,
             okcLnV.cognomen               LineRef,
             oksLnV.invoice_text           InvoiceText,
             rul.usage_period              Period,
             to_char(rul.minimum_quantity) Rate_Minimum,
             to_char(rul.default_quantity) Rate_Default,
             rul.amcv_flag                 AMCV_YN,
             to_char(rul.fixed_quantity)   Rate_Fixed,
             to_char(rul.usage_duration)   NoOf_TUOM_per,
             rul.level_yn                  Level_YN,
             mtl.Unit_of_measure           UOM,
             to_char(rul.base_reading)     NetReading,
             '#'                           Reading,
             sub_line.price_negotiated     Price,
             okcItms.object1_id1           ItemObject1_Id1
       FROM  OKC_K_LINES_B           sub_line,
             OKS_K_LINES_B           rul,
             OKC_K_LINES_V           okcLnV,
             OKS_K_LINES_V           oksLnV,
             OKC_K_ITEMS             okcItms,
             MTL_UNITS_OF_MEASURE_TL mtl
       WHERE sub_line.cle_id = to_number(p_line_id_arg)
             AND rul.cle_id = sub_line.id
             AND okcLnV.cle_id = sub_line.cle_id
             AND oksLnV.cle_id = sub_line.id
             AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
             AND okcItms.cle_id = sub_line.id
             AND mtl.uom_code = okcItms.uom_code
             AND mtl.language = USERENV('LANG')
             AND not exists (select 1 from okc_k_rel_objs rel
                             WHERE rel.cle_id = sub_line.id );
Line: 3762

      /* Select
        sys.NAME  Name,
        cgrp.SOURCE_OBJECT_CODE||';'||cp.CURRENT_SERIAL_NUMBER||';'||cp.REFERENCE_NUMBER SourceDetails,
Line: 3779

      Select
        sys.NAME Name,
        cgrp.SOURCE_OBJECT_CODE||';'||hdr.CONTRACT_NUMBER||';'||hdr.CONTRACT_NUMBER_MODIFIER SourceDetails,
Line: 3800

     Select
       sys.concatenated_segments  Name,
       cca.SOURCE_OBJECT_CODE||';'||cp.SERIAL_NUMBER||';'||cp.INSTANCE_NUMBER SourceDetails,
Line: 3819

     Select
       sys.concatenated_segments  Name,
       cca.SOURCE_OBJECT_CODE||';'||hdr.CONTRACT_NUMBER||';'||hdr.CONTRACT_NUMBER_MODIFIER SourceDetails,
Line: 3978

      /* SELECT
             CG.NAME || '.' || CT.NAME name ,
             CT.TYPE type ,
             IT.NAME sourcedetails ,
             CII.SERIAL_NUMBER serialnumber ,
             CII.INSTANCE_NUMBER referencenumber,
             CT.UOM_CODE uom ,
             CT.NET_READING netreading ,
             CT.VALUE_TIMESTAMP timestamp
        FROM
             OKX_COUNTER_GROUPS_V CG ,
             OKX_COUNTERS_V CT ,
             CSI_ITEM_INSTANCES CII ,
             OKX_SYSTEM_ITEMS_V IT
       WHERE
             CG.COUNTER_GROUP_ID = CT.COUNTER_GROUP_ID
             AND CT.USAGE_ITEM_ID IS NOT NULL
             AND CG.SOURCE_OBJECT_ID = CII.INSTANCE_ID
             AND CG.SOURCE_OBJECT_CODE = 'CP'
             AND IT.ID1 = CII.INVENTORY_ITEM_ID
             AND IT.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID')
             AND CT.COUNTER_ID = to_number(p_covered_prod_ID_arg)
       UNION
       SELECT
             CG.NAME || '.' || CT.NAME name ,
             CT.TYPE type ,
             KL.NAME|| '-' ||KH.CONTRACT_NUMBER sourcedetails ,
             NULL serialnumber ,
             NULL referencenumber ,
             CT.UOM_CODE uom ,
             CT.NET_READING netreading ,
             CT.VALUE_TIMESTAMP timestamp
       FROM
             OKX_COUNTER_GROUPS_V CG ,
             OKX_COUNTERS_V CT ,
             OKC_K_LINES_B KL ,
             OKC_K_HEADERS_ALL_B KH
       WHERE
             CG.COUNTER_GROUP_ID = CT.COUNTER_GROUP_ID
             AND CT.USAGE_ITEM_ID IS NOT NULL
             AND CG.SOURCE_OBJECT_ID = KL.ID
             AND CG.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
             AND KH.ID = KL.DNZ_CHR_ID
             AND CT.COUNTER_ID = to_number(p_covered_prod_ID_arg); */
Line: 4024

      SELECT CCG.NAME || '.' || CCT.NAME name ,
             lkup.meaning type ,
             sys.concatenated_segments sourcedetails ,
             CII.SERIAL_NUMBER serialnumber ,
             CII.INSTANCE_NUMBER referencenumber,
             CCB.UOM_CODE uom ,
             CV.NET_READING netreading ,
             CV.VALUE_TIMESTAMP timestamp
        FROM   MTL_SYSTEM_ITEMS_B_KFV sys,
               CSI_ITEM_INSTANCES CII,
               CS_CSI_COUNTER_GROUPS CCG,
               csi_counters_b ccb,
               csi_counters_tl cct,
               csi_counter_associations cca,
               CSI_COUNTER_READINGS CV,
               csi_lookups lkup
     Where  ccb.COUNTER_ID = to_number(p_covered_prod_ID_arg)
       and  sys.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
       and  sys.ORGANIZATION_ID =  CII.INV_MASTER_ORGANIZATION_ID
       AND  ccg.template_flag = 'N'
       AND ccg.counter_group_id = ccb.group_id
       AND ccb.counter_id = cca.counter_id
       and cca.SOURCE_OBJECT_CODE = 'CP'
       and cca.source_object_id = cII.instance_id
       and CV.COUNTER_ID (+) = CCB.COUNTER_ID
       AND CV.COUNTER_VALUE_ID (+) = CSI_COUNTER_READINGS_PVT.get_latest_reading(CCB.COUNTER_ID)
       AND ccb.counter_id = cct.counter_id
       AND cct.language = USERENV('LANG')
       AND lkup.lookup_type = 'CSI_COUNTER_TYPE'
       AND ccb.counter_type = lkup.lookup_code
       UNION
       SELECT
             CCG.NAME || '.' || CCT.NAME name ,
             lkup.meaning type ,
             SYS.CONCATENATED_SEGMENTS || '-' ||KH.CONTRACT_NUMBER || KH.CONTRACT_NUMBER_MODIFIER sourcedetails ,
             NULL serialnumber ,
             NULL referencenumber,
             CCB.UOM_CODE uom ,
             CV.NET_READING netreading ,
             CV.VALUE_TIMESTAMP timestamp
       FROM  MTL_SYSTEM_ITEMS_B_KFV sys,
             CS_CSI_COUNTER_GROUPS CCG,
             csi_counters_b ccb,
             csi_counters_tl cct,
             csi_counter_associations cca,
             CSI_COUNTER_READINGS CV,
             csi_lookups lkup,
             OKC_K_ITEMS KI,
             OKC_K_LINES_B KL ,
             OKC_K_HEADERS_ALL_B KH
       WHERE sys.INVENTORY_ITEM_ID = KI.OBJECT1_ID1
       and  sys.ORGANIZATION_ID = KI.OBJECT1_ID2
       AND  ccg.template_flag = 'N'
       AND ccg.counter_group_id = ccb.group_id
       AND ccb.counter_id = cca.counter_id
       and cca.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
       and cca.source_object_id = KL.id
       and CV.COUNTER_ID (+) = CCB.COUNTER_ID
       AND CV.COUNTER_VALUE_ID (+) = CSI_COUNTER_READINGS_PVT.get_latest_reading(CCB.COUNTER_ID)
       AND ccb.counter_id = cct.counter_id
       AND cct.language = USERENV('LANG')
       AND lkup.lookup_type = 'CSI_COUNTER_TYPE'
       AND ccb.counter_type = lkup.lookup_code
       AND KH.ID = KL.DNZ_CHR_ID
       AND KL.ID = KI.CLE_ID
       AND KI.JTOT_OBJECT1_CODE IN('OKX_SERVICE','OKX_WARRANTY','OKX_USAGE')
       AND CCB.COUNTER_ID = to_number(p_covered_prod_ID_arg);