APPS.OKL_SSC_WF SQL Statements

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

Line: 28

procedure raise_assets_update_event ( p_event_name   in varchar2 ,
                                      parent_line_id in varchar2,
                                      requestorId  in varchar2,
                                      new_site_id1 in varchar2,
                                      new_site_id2 in varchar2,
                                      old_site_id1 in varchar2,
                                      old_site_id2 in varchar2,
                                      trx_date     in date
                                      )
                                                   IS

x_return_status VARCHAR2(1);

Line: 81

end  raise_assets_update_event;

Line: 98

SELECT user_name from fnd_user
WHERE user_id = p_id;

Line: 103

SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = respKey
AND application_id = 540;

Line: 215

select asset_number from okl_txl_assets_b
where tas_id = p_tas_id
and tal_type = p_tal_type
;

Line: 294

    select decode(fnd_profile.value('AR_MASK_BANK_ACCOUNT_NUMBERS'),'F',rpad(substr(cc_number,1,4),length(cc_number),l_mask_string),'L',lpad(substr(cc_number,length(cc_number)-3),length(cc_number),l_mask_string),'N','N')
    into l_result
    from dual;

Line: 315

SELECT user_name from fnd_user
WHERE user_id = p_id;

Line: 319

SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = respKey
AND application_id = 540;

Line: 420

SELECT
DISTINCT ASX.ASSET_NUMBER,
INX.SERIAL_NUMBER
FROM   OKL_TXL_ITM_INSTS INX, OKL_TXL_ASSETS_B ASX
WHERE  INX.TAS_ID  = p_tas_id
AND INX.TAL_TYPE = p_tal_type
AND ASX.KLE_ID = INX.DNZ_CLE_ID
;

Line: 496

procedure update_serial_fnc (itemtype in varchar2,
                             itemkey in varchar2,
                             actid in number,
                             funcmode in varchar2,
                             resultout out nocopy varchar2 ) is

x_return_status varchar2(1);

Line: 523

update_serial_number(p_api_version    => 1.0,
                     p_init_msg_list     => OKC_API.G_FALSE,
                     p_tas_id           => l_tas_id,
                     x_return_status    => x_return_status,
                     x_msg_count        => x_msg_count,
                     x_msg_data         => l_msg_data);

Line: 567

WF_CORE.CONTEXT ('okl_ssc_wf', 'update_serial_fnc:'||l_msg_data, itemtype, itemkey,actid,funcmode);

Line: 571

end update_serial_fnc;

Line: 587

SELECT user_name from fnd_user
WHERE user_id = p_id;

Line: 591

SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = respKey
AND application_id = 540;

Line: 689

select
distinct
ASX.ASSET_NUMBER,
PSU1.DESCRIPTION        OLD_LOCATION,
PSU2.DESCRIPTION        NEW_LOCATION,
TRX.DATE_TRANS_OCCURRED EFFECTIVE_DATE
from OKL_TXL_ITM_INSTS INX,
OKX_PARTY_SITE_USES_V  PSU1,
OKX_PARTY_SITE_USES_V  PSU2,
OKL_TXL_ASSETS_B       ASX,
OKL_TRX_ASSETS         TRX
where
    INX.TAS_ID   = p_tas_id
AND INX.TAL_TYPE = p_tal_type
AND ASX.KLE_ID   = INX.DNZ_CLE_ID
AND PSU1.ID1 (+) = INX.OBJECT_ID1_OLD
AND PSU1.ID2 (+) = INX.OBJECT_ID2_OLD
AND PSU2.ID1     = INX.OBJECT_ID1_NEW
AND PSU2.ID2     = INX.OBJECT_ID2_NEW
AND TRX.ID       = INX.TAS_ID
;

Line: 738

   SELECT PSU.DESCRIPTION
     FROM OKX_PARTY_SITE_USES_V PSU
    WHERE PSU.ID1 = cp_id1
      AND PSU.ID2 = cp_id2;

Line: 744

  SELECT NAME
    FROM OKC_K_LINES_V
   WHERE ID = cp_kle_id;

Line: 755

 SELECT user_id
 FROM   FND_USER
 WHERE  User_Name = FND_GLOBAL.user_name;

Line: 896

procedure update_location_fnc (itemtype in varchar2,
                                    itemkey in varchar2,
                                    actid in number,
                                    funcmode in varchar2,
                                    resultout out nocopy varchar2 ) is

l_tas_id number;

Line: 926

    SELECT
           PARTY_SITE_ID,
           LOCATION_ID
    FROM   okx_party_site_uses_v
    WHERE  ID1  = p_id1 AND ID2  = p_id2;

Line: 937

select chr.org_id
from   okc_k_headers_all_b chr,
       okc_k_lines_b cle
where  chr.id = cle.dnz_chr_id
  and  cle.cle_id = p_kle_id
  and  rownum = 1;

Line: 983

    OKL_BLK_AST_UPD_PUB.update_location(
               p_api_version    => 1.0,
               p_init_msg_list  => OKL_API.G_FALSE,
               p_loc_rec        => l_loc_rec,
               x_return_status  => x_return_status,
               x_msg_count      => x_msg_count,
               x_msg_data       => l_msg_data);

Line: 994

    update_location(p_api_version   => 1.0,
                p_init_msg_list     => OKC_API.G_FALSE,
                     p_tas_id           => l_tas_id,
                     x_return_status    => x_return_status,
                     x_msg_count        => x_msg_count,
                     x_msg_data         => l_msg_data);

Line: 1020

      OKL_BLK_AST_UPD_PVT.process_update_location(
         p_api_version                    => 1.0,
         p_init_msg_list                  => 'T',
         p_kle_id                         => l_asset_id,
         x_return_status                  => x_return_status,
         x_msg_count                      => x_msg_count,
         x_msg_data                       => l_msg_data);

Line: 1069

WF_CORE.CONTEXT ('OKL_SSC_WF', 'update_location_fnc:'||l_msg_data, itemtype, itemkey,actid,funcmode);

Line: 1074

end update_location_fnc;

Line: 1096

            select transaction_type_id
            from   CS_TRANSACTION_TYPES_V
            where  Name = p_transaction_type;

Line: 1150

 PROCEDURE update_serial_number(
                            p_api_version                    IN  NUMBER,
                            p_init_msg_list                  IN  VARCHAR2,
                            p_tas_id                         IN  NUMBER,
                            x_return_status                  OUT NOCOPY VARCHAR2,
                            x_msg_count                      OUT NOCOPY NUMBER,
                            x_msg_data                       OUT NOCOPY VARCHAR2)
  AS

    CURSOR okl_itiv_csr (p_tas_id     IN NUMBER) IS
    SELECT
           KLE_ID,
           SERIAL_NUMBER,
           instance_number_ib,
           INVENTORY_ITEM_ID
    FROM   OKL_TXL_ITM_INSTS
    WHERE  TAS_ID  = p_tas_id;

Line: 1169

    SELECT
           KIT.OBJECT1_ID1
    FROM   OKC_K_ITEMS KIT
    WHERE  KIT.CLE_ID = (SELECT KLB.ID FROM OKC_K_LINES_B KLB WHERE
                         KLB.ID = p_kle_id);

Line: 1219

      select object_version_number into l_object_version_number from
      csi_item_instances
      where instance_id = l_instance_rec_type.INSTANCE_ID;

Line: 1236

      csi_item_instance_pub.update_item_instance(p_api_version           =>  p_api_version,
                                                 p_commit                =>  fnd_api.g_false,
                                                 p_init_msg_list         =>  p_init_msg_list,
                                                 p_validation_level      =>  fnd_api.g_valid_level_full,
                                                 p_instance_rec          =>  l_instance_rec_type,
                                                 p_ext_attrib_values_tbl =>  l_ext_attrib_values_tbl,
                                                 p_party_tbl             =>  l_party_tbl,
                                                 p_account_tbl           =>  l_account_tbl,
                                                 p_pricing_attrib_tbl    =>  l_pricing_attrib_tbl,
                                                 p_org_assignments_tbl   =>  l_org_assignments_tbl,
                                                 p_asset_assignment_tbl  =>  l_asset_assignment_tbl,
                                                 p_txn_rec               =>  l_txn_rec,
                                                 x_instance_id_lst       =>  l_instance_id_lst,
                                                 x_return_status         =>  x_return_status,
                                                 x_msg_count             =>  x_msg_count,
                                                 x_msg_data              =>  x_msg_data);

Line: 1274

      FND_MSG_PUB.ADD_EXC_MSG('OKL_SSC_ASST_LOC_SERNUM_PUB','Update_Serial_Number');

Line: 1278

  END update_serial_number;

Line: 1280

 PROCEDURE update_location(
                            p_api_version                    IN  NUMBER,
                            p_init_msg_list                  IN  VARCHAR2,
                            p_tas_id                         IN  NUMBER,
                            x_return_status                  OUT NOCOPY VARCHAR2,
                            x_msg_count                      OUT NOCOPY NUMBER,
                            x_msg_data                       OUT NOCOPY VARCHAR2)
  AS

    CURSOR okl_itiv_csr (p_tas_id     IN NUMBER) IS
    SELECT
           KLE_ID,
           OBJECT_ID1_NEW,
           OBJECT_ID2_NEW,
           instance_number_ib,
           INVENTORY_ITEM_ID
    FROM   OKL_TXL_ITM_INSTS
    WHERE  TAS_ID  = p_tas_id;

Line: 1300

    SELECT
           PARTY_SITE_ID,
           LOCATION_ID
    FROM   okx_party_site_uses_v
    WHERE  ID1  = p_id1 AND ID2  = p_id2;

Line: 1307

    SELECT
           KIT.OBJECT1_ID1
    FROM   OKC_K_ITEMS KIT
    WHERE  KIT.CLE_ID = (SELECT KLB.ID FROM OKC_K_LINES_B KLB WHERE
                         KLB.ID = p_kle_id);

Line: 1368

      select object_version_number into l_object_version_number from
      csi_item_instances
      where instance_id = l_instance_rec_type.INSTANCE_ID;

Line: 1385

      csi_item_instance_pub.update_item_instance(p_api_version           =>  p_api_version,
                                                 p_commit                =>  fnd_api.g_false,
                                                 p_init_msg_list         =>  p_init_msg_list,
                                                 p_validation_level      =>  fnd_api.g_valid_level_full,
                                                 p_instance_rec          =>  l_instance_rec_type,
                                                 p_ext_attrib_values_tbl =>  l_ext_attrib_values_tbl,
                                                 p_party_tbl             =>  l_party_tbl,
                                                 p_account_tbl           =>  l_account_tbl,
                                                 p_pricing_attrib_tbl    =>  l_pricing_attrib_tbl,
                                                 p_org_assignments_tbl   =>  l_org_assignments_tbl,
                                                 p_asset_assignment_tbl  =>  l_asset_assignment_tbl,
                                                 p_txn_rec               =>  l_txn_rec,
                                                 x_instance_id_lst       =>  l_instance_id_lst,
                                                 x_return_status         =>  x_return_status,
                                                 x_msg_count             =>  x_msg_count,
                                                 x_msg_data              =>  x_msg_data);

Line: 1424

      FND_MSG_PUB.ADD_EXC_MSG('OKL_SSC_ASST_LOC_SERNUM_PUB','Update_Location');

Line: 1428

  END update_location;

Line: 1436

    SELECT
            ID,
            KLE_ID,
            DNZ_KHR_ID,
            ASSET_NUMBER,
            DESCRIPTION
     FROM   OKL_TXL_ASSETS_V
     WHERE  nvl(OKL_TXL_ASSETS_V.TAS_ID,-9999)     = p_tas_id;

Line: 1479

   SELECT 'Y'
     FROM wf_event_subscriptions a,
          wf_events b
    WHERE a.event_filter_guid = b.guid
      AND a.status = 'ENABLED'
      AND b.name   = p_event_name
      AND rownum   = 1;

Line: 1528

SELECT OKLSSC_WFITEMKEY_S.nextval from dual;

Line: 1588

SELECT party_id from hz_parties
WHERE party_number = provider_name;

Line: 1592

SELECT site_id from OKL_INS_PARTYSITES_V
WHERE party_id = p_party_id;

Line: 1675

SELECT responsibility_id
INTO l_respId
FROM fnd_responsibility
WHERE responsibility_key = l_resp_key
AND application_id = 540;

Line: 1762

SELECT responsibility_id
INTO l_respId
FROM fnd_responsibility
WHERE responsibility_key = l_resp_key
AND application_id = 540;

Line: 1795

SELECT contract_number
INTO l_knum
FROM okc_k_headers_v
WHERE id = l_chrid;

Line: 1821

OKL_INS_POLICIES_PUB.insert_ins_policies(1.0,
            FND_API.G_FALSE,
                  lx_return_status,
                        lx_msg_count,
                        lx_msg_data,
                  l_policy_rec,
            lx_policy_rec);

Line: 1881

SELECT contract_number from okc_k_headers_b
WHERE id = p_id;

Line: 1885

SELECT user_name from fnd_user
WHERE user_id = p_id;

Line: 1916

SELECT responsibility_id
INTO l_respId
FROM fnd_responsibility
WHERE responsibility_key = l_resp_key
AND application_id = 540;

Line: 1990

SELECT CONTRACT_NUMBER ,org_id
FROM OKC_K_HEADERS_all_b --modified by rajnisku for getting org_id
WHERE id = l_chr_id;

Line: 1995

SELECT user_name from fnd_user
WHERE user_id = l_user_id;

Line: 1999

SELECT name
FROM okl_invoice_formats_v
WHERE id = l_format_id;

Line: 2004

SELECT rule_information1
FROM okc_rules_b
WHERE rule_information_category = 'LAINVD'
AND dnz_chr_id = l_chr_id;

Line: 2010

SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_key
AND application_id = 540;

Line: 2108

SELECT rl.id,
       rl.rgp_id
FROM   okc_rule_groups_v rg,
       okc_rules_v rl
WHERE rl.rgp_id = rg.id
AND   rl.dnz_chr_id = rg.dnz_chr_id
AND   rg.chr_id  = rl.dnz_chr_id
AND   rg.cle_id is null
AND   rg.rgd_code ='LABILL'
AND   rl.rule_information_category = 'LAINVD'
AND   rg.dnz_chr_id = p_chr_id;

Line: 2120

select org_id from okc_k_headers_all_b
where id=p_contract_id;

Line: 2166

  l_rule_tbl(l_counter).LAST_UPDATED_BY           := OKC_API.G_MISS_NUM;

Line: 2167

  l_rule_tbl(l_counter).LAST_UPDATE_DATE          := OKC_API.G_MISS_DATE;

Line: 2168

  l_rule_tbl(l_counter).LAST_UPDATE_LOGIN         := OKC_API.G_MISS_NUM;

Line: 2208

select org_id from okc_k_headers_all_b
where id=p_contract_id; --added by rajnisku for retrieving orginfo

Line: 2214

SELECT contract.contract_number contract_number,
       site.name bill_to_site,
       site.description bill_to_address
FROM   okx_cust_site_uses_v site ,
       okc_k_headers_all_b contract
WHERE  contract.id = p_contract_id
  AND  site.id1 = contract.bill_to_site_use_id; */

Line: 2224

  SELECT contract.contract_number contract_number,
         cs.location bill_to_site,
         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) bill_to_address
  FROM  hz_cust_site_uses_all cs,
        hz_cust_acct_sites_all ca,
        hz_party_sites ps,
        hz_locations l,
        okc_k_headers_all_b contract
  WHERE cs.cust_acct_site_id = ca.cust_acct_site_id
  AND   ca.party_site_id = ps.party_site_id
  AND   ps.location_id = l.location_id
  AND   l.content_source_type = 'USER_ENTERED'
  AND   cs.site_use_id = contract.bill_to_site_use_id
  AND   contract.id = p_contract_id;

Line: 2265

select name bill_to_site, description bill_to_address
from okx_cust_site_uses_v
where id1= p_billing_site_id; */

Line: 2270

SELECT CS.LOCATION BILL_TO_SITE,
       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) BILL_TO_ADDRESS
FROM HZ_CUST_SITE_USES_ALL CS,
     HZ_CUST_ACCT_SITES_ALL CA,
     HZ_PARTY_SITES  PS,
     HZ_LOCATIONS L
WHERE CS.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND L.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND CS.site_use_id = p_billing_site_id;

Line: 2287

SELECT user_name from fnd_user
WHERE user_id = l_user_id;

Line: 2335

SELECT responsibility_id
INTO      l_respId
FROM    fnd_responsibility
WHERE  responsibility_key = l_resp_key -- This example is for 'Lease Center Agent' responsibility
AND       application_id = 540;

Line: 2443

     okl_contract_pub.update_contract_header(
            p_api_version      => '1.0'
            ,p_init_msg_list    => 'T'
            ,x_return_status   => l_return_status
            ,x_msg_count       => l_msg_count
            ,x_msg_data        => l_msg_data
            ,p_restricted_update => OKL_API.G_FALSE
            ,p_chrv_rec => l_chrv_rec
            ,p_khrv_rec => l_khrv_rec
            ,x_chrv_rec => x_chrv_rec
            ,x_khrv_rec => x_khrv_rec);

Line: 2519

SELECT OKLSSC_WFITEMKEY_S.nextval  key from dual;

Line: 2548

SELECT OKLSSC_WFITEMKEY_S.nextval  key from dual;

Line: 2551

select authoring_org_id
from okc_k_headers_all_b
where id = p_contract_id;

Line: 2590

SELECT contract_number from okc_k_headers_b
WHERE id = p_chr_id;

Line: 2594

SELECT policy_number, iss_code, cancellation_date, khr_id
from OKL_INS_POLICIES_B
WHERE  id = p_pol_id;

Line: 2599

SELECT user_name from fnd_user
WHERE user_id = p_requestor_id;

Line: 2603

SELECT meaning from fnd_lookups
WHERE lookup_type = p_type
 AND  lookup_code = p_code;

Line: 2608

SELECT cancellation_comment
FROM OKL_INS_POLICIES_TL
WHERE id = p_pol_id;

Line: 2660

SELECT responsibility_id
into   l_resp_id
FROM   fnd_responsibility
WHERE  responsibility_key = l_resp_key
AND    application_id = 540;

Line: 2737

   SELECT iss_code
   FROM OKL_INS_POLICIES_B
   WHERE  id = p_polid;

Line: 2742

   SELECT OKLSSC_WFITEMKEY_S.nextval
   FROM  dual;

Line: 2772

      OKL_INSURANCE_POLICIES_PUB.delete_policy(
                                 p_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_ipyv_rec => l_ipyv_rec,
                                 x_ipyv_rec => lx_ipyv_rec);

Line: 2852

 SELECT responsibility_id
 FROM fnd_responsibility
 WHERE responsibility_key = respKey
 AND application_id = 540;

Line: 2859

SELECT IPYB.POLICY_NUMBER , ICMB.CLAIM_NUMBER
 FROM OKL_INS_POLICIES_B IPYB,  OKL_INS_CLAIMS_B ICMB
 WHERE IPYB.ID = ICMB.IPY_ID
 AND ICMB.ID = claim_id;

Line: 2969

  select OKLSSC_WFITEMKEY_S.nextval INTO l_seq FROM DUAL ;

Line: 2983

   l_parameter_list.DELETE;

Line: 3053

SELECT object_version_number, currency_code from okc_k_headers_b
WHERE id = p_id;

Line: 3057

SELECT user_name from fnd_user
WHERE user_id = p_id;

Line: 3074

OKL_CS_LEASE_RENEWAL_PUB.update_lrnw_request(1.0,
                                          OKL_API.G_FALSE,
                                          x_return_status,
                                          x_msg_count,
                                          x_msg_data,
                                          p_trqv_rec,
                                          x_trqv_rec);

Line: 3092

OKL_CONTRACT_PUB.update_contract_header(1.0,
                                        FND_API.G_FALSE,
                                        x_return_status,
                                        x_msg_count,
                                        x_msg_data,
                                        null,
                                        chrvrec1,
                                        chrvrec2,
                                        xchrvrec1,
                                        xchrvrec2);

Line: 3150

SELECT object_version_number, currency_code from okc_k_headers_b
WHERE id = p_id;

Line: 3154

SELECT user_name from fnd_user
WHERE user_id = p_id;

Line: 3171

OKL_CS_LEASE_RENEWAL_PUB.update_lrnw_request(1.0,
                                          OKL_API.G_FALSE,
                                          x_return_status,
                                          x_msg_count,
                                          x_msg_data,
                                          p_trqv_rec,
                                          x_trqv_rec);

Line: 3189

OKL_CONTRACT_PUB.update_contract_header(1.0,
                                        FND_API.G_FALSE,
                                        x_return_status,
                                        x_msg_count,
                                        x_msg_data,
                                        null,
                                        chrvrec1,
                                        chrvrec2,
                                        xchrvrec1,
                                        xchrvrec2);

Line: 3280

	SELECT ccard_remittance_id rm_id
	FROM okl_system_params_all
	WHERE org_id = lorg_id ;

Line: 3286

	Select bill_to_site_use_id site_use_id
	from ra_customer_trx
	where customer_Trx_id = l_inv_id;

Line: 3299

	  select cust_account_id INTO l_cust_id
	  from HZ_CUST_ACCOUNTS
	  where account_number = p_custid;--Smoduga removed char conversion

Line: 3412

SELECT pay_from_customer cust_account_id
FROM ar_cash_receipts_all
WHERE  cash_receipt_id = p_payment_id;

Line: 3417

SELECT account_name
FROM hz_cust_accounts
WHERE  cust_account_id = p_cust_id;

Line: 3422

SELECT user_name from fnd_user
WHERE user_id = p_requestor_id;

Line: 3462

SELECT responsibility_id
into   l_resp_id
FROM   fnd_responsibility
WHERE  responsibility_key = l_payee_role
AND    application_id = 540;

Line: 3539

 select id from okc_k_party_roles_b
  where dnz_chr_id = p_khr_id
  AND chr_id = dnz_chr_id
  and rle_code='LESSEE';

Line: 3545

  SELECT email_address from fnd_user
  WHERE  user_id = p_user_id;

Line: 3644

    SELECT OKLSSC_WFITEMKEY_S.nextval into l_seq
    FROM  dual;

Line: 3696

SELECT contract_number from okc_k_headers_b
WHERE id = p_khr_id;

Line: 3700

SELECT quote_number, quote_type_description,
       quote_reason_description, comments, khr_id
from okl_am_quotes_uv
WHERE  id = p_qte_id;

Line: 3706

SELECT user_name from fnd_user
WHERE user_id = p_requestor_id;

Line: 3750

SELECT responsibility_id into   l_resp_id
FROM   fnd_responsibility
WHERE  responsibility_key = l_resp_key
AND    application_id = 540;

Line: 3861

procedure raise_ser_num_update_event ( p_event_name  in varchar2 ,
                                      requestId   in varchar2,
                                      requestorId in varchar2,
                                      requestType in varchar2
                                      )
                                                   IS

x_return_status VARCHAR2(1);

Line: 3875

SELECT OKLSSC_WFITEMKEY_S.nextval  key from dual;

Line: 3906

end  raise_ser_num_update_event;