[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_PARTY_UPDATE_INVALID CONSTANT Varchar2(200) := 'OKL_SUB_RBK_PARTY_UPDATE';
SELECT
SUBSIDY_ID
,SUBSIDY_CLE_ID
,NAME
,DESCRIPTION
,AMOUNT
,SUBSIDY_OVERRIDE_AMOUNT
,DNZ_CHR_ID
,ASSET_CLE_ID
,CPL_ID
,VENDOR_ID
,VENDOR_NAME
FROM okl_asset_subsidy_uv asb
WHERE asb.subsidy_cle_id = p_id;
Select 'Y'
From okc_k_headers_b chr
where chr.orig_system_source_code = 'OKL_REBOOK'
and chr.id = p_chr_id;
select subb.stream_type_id,
subb.effective_from_date,
subb.effective_to_date,
subb.expire_after_days,
subb.maximum_term,
subb.name,
subt.short_description
from
okl_subsidies_tl subt,
okl_subsidies_b subb
where subt.id = subb.id
and subt.language = userenv('LANG')
and subb.id = p_sub_id;
select cleb.start_date,
cleb.end_date,
cleb.sts_code,
cleb.currency_code
from okc_k_lines_b cleb
where cleb.id = p_asset_cle_id;
select vendor_id
from po_vendors pov
where vendor_name = ltrim(rtrim(p_vend_name,' '),' ');
select id
from okl_subsidies_b subb
where name = ltrim(rtrim(p_subsidy_name,' '),' ');
select subb.stream_type_id,
subb.effective_from_date,
subb.effective_to_date,
subb.expire_after_days,
subb.maximum_term,
subb.name,
subt.short_description
from
okl_subsidies_tl subt,
okl_subsidies_b subb
where subt.id = subb.id
and subt.language = userenv('LANG')
and subb.id = p_sub_id;
select cleb.start_date,
cleb.end_date,
cleb.sts_code,
cleb.currency_code
from okc_k_lines_b cleb
where cleb.id = p_asset_cle_id;
Select lseb.id
from okc_line_styles_b lseb,
okc_line_styles_b top_lseb,
okc_subclass_top_line scs_lse,
okc_k_headers_b chrb
where lseb.lty_code = G_SUBLINE_LTY_CODE
and lseb.lse_parent_id = top_lseb.id
and top_lseb.lty_code = 'FREE_FORM1'
and lseb.lse_parent_id = scs_lse.lse_id
and scs_lse.scs_code = chrb.scs_code
and chrb.id = p_chr_id;
select nvl(max(cleb.display_sequence),0)+5
from okc_k_lines_b cleb
where cleb.cle_id = p_asset_cle_id
and cleb.dnz_chr_id = p_chr_id
and cleb.lse_id = p_lse_id;
select vendor_id
from po_vendors pov
where vendor_name = ltrim(rtrim(p_vend_name,' '),' ');
select id
from okl_subsidies_b subb
where name = ltrim(rtrim(p_subsidy_name,' '),' ');
SELECT DATE_TRANSACTION_OCCURRED
FROM okl_trx_contracts ktrx
WHERE ktrx.KHR_ID_NEW = rbk_chr_id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP Project
AND ktrx.representation_type = 'PRIMARY';
select clet.name
from okc_k_lines_tl clet
where clet.id = p_cle_id
and clet.language = userenv('LANG');
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_sub_clev_rec,
p_klev_rec => l_sub_klev_rec,
x_clev_rec => lx_sub_clev_rec,
x_klev_rec => lx_sub_klev_rec
);
Select 'Y'
from okl_subsidies_b sub,
okc_k_headers_b chrb,
okc_k_lines_b cleb
where sub.id = p_subsidy_id
and chrb.id = cleb.chr_id
and chrb.id = cleb.dnz_chr_id
and cleb.id = p_asset_cle_id
--check for authoring org id
and chrb.authoring_org_id = sub.org_id
--check for currency code
and chrb.currency_code = sub.currency_code;
Select 'Y'
from okl_subsidies_b sub,
okc_k_headers_b chrb,
okc_k_lines_b cleb
where sub.id = p_subsidy_id
and chrb.id = cleb.chr_id
and chrb.id = cleb.dnz_chr_id
and cleb.id = p_asset_cle_id
and decode(chrb.orig_system_source_code,
'OKL_RELEASE','Y',
sub.APPLICABLE_TO_RELEASE_YN) = sub.APPLICABLE_TO_RELEASE_YN;
Select 'Y'
from okl_subsidies_b sub,
okc_rules_b rulb,
okc_k_lines_b cleb
where sub.id = p_subsidy_id
and rulb.dnz_chr_id = cleb.chr_id
and rulb.rule_information_category = 'LARLES'
and cleb.id = p_asset_cle_id
and decode(ltrim(rtrim(rulb.RULE_INFORMATION1,' '),' '),
'Y','Y',
sub.APPLICABLE_TO_RELEASE_YN) = sub.APPLICABLE_TO_RELEASE_YN
union
-- to take care of S and O where release yes-no flag is not applicable
Select 'Y'
from okl_subsidies_b sub,
okc_k_lines_b cleb
where sub.id = p_subsidy_id
and cleb.id = p_asset_cle_id
and not exists (select 1
from okc_rules_b rulb
where rulb.dnz_chr_id = cleb.chr_id
and rulb.rule_information_category = 'LARLES');
Select 'Y'
from okl_subsidies_b sub,
okc_k_lines_b cleb,
okl_k_lines kle_fin
where sub.id = p_subsidy_id
and kle_fin.id = cleb.id
and cleb.id = p_asset_cle_id
and decode(NVL(kle_fin.re_lease_yn,'N'),
'Y','Y',
sub.applicable_to_release_yn) = sub.applicable_to_release_yn;
Select 'Y'
from okl_subsidies_b sub,
okc_k_lines_b cleb
where sub.id = p_subsidy_id
and cleb.id = p_asset_cle_id
-- start: okl.h cklee
-- and cleb.start_date between sub.effective_from_date
-- and nvl(sub.effective_to_date,cleb.start_date);
select 'Y'
from okl_subsidies_b sub
where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.subsidy_id = sub.id);
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.organization_id is not null
and suc.subsidy_id = sub.id);
Select 'Y'
From
--inv item and org
okc_k_lines_b cleb,
okc_k_lines_b cle_model,
okc_line_styles_b lse_model,
okc_k_items cim_model,
okl_subsidy_criteria suc
where cim_model.cle_id = cle_model.id
And cim_model.dnz_chr_id = cleb.dnz_chr_id
And cle_model.cle_id = cleb.id
And cle_model.dnz_chr_id = cleb.dnz_chr_id
And cle_model.lse_id = lse_model.id
And lse_model.lty_code = 'ITEM'
And cleb.id = p_asset_cle_id
And (to_char(suc.organization_id) = cim_model.object1_id2
And to_char(nvl(suc.inventory_item_id,cim_model.object1_id1)) = cim_model.object1_id1
)
And suc.subsidy_id = p_subsidy_id
And suc.organization_id is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.credit_classification_code is not null
-- start: okl.h cklee
-- And suc.id = sub.id);
select 'Y'
from okc_k_headers_b chrb,
hz_cust_accounts cust,
okc_k_lines_b cleb,
okl_subsidy_criteria suc
where chrb.id = cleb.chr_id
And cleb.dnz_chr_id = chrb.id
And cleb.id = p_asset_cle_id
-- start: okl.h cklee
-- And chrb.cust_acct_id = to_char(cust.cust_account_id)
And chrb.cust_acct_id = cust.cust_account_id
-- end: okl.h cklee
And suc.subsidy_id = p_subsidy_id
And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
And SUC.CREDIT_CLASSIFICATION_CODE is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
--cklee 03/16/2004
-- where suc.SALES_TERRITORY_CODE is not null
where suc.SALES_TERRITORY_ID is not null
And suc.subsidy_id = sub.id);
select 'Y'
from hz_locations loc,
hz_party_sites hzps,
hz_party_site_uses hzpsu,
okl_txl_itm_insts iti,
--csi_item_instances csii,
--okc_k_items cim_ib,
okc_k_lines_b cle_ib,
okc_line_styles_b lse_ib,
okc_k_lines_b cle_inst,
okc_line_styles_b lse_inst,
OKC_K_LINES_B cleb,
okl_subsidy_criteria suc
Where cle_inst.cle_id = cleb.id
And cle_inst.dnz_chr_id = cleb.dnz_chr_id
And cle_inst.lse_id = lse_inst.id
And lse_inst.lty_code = 'FREE_FORM2'--'FREE_FORM1' cklee 21-Jan-04 bug#3375789
And cle_ib.cle_id = cle_inst.id
And cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
And lse_ib.id = cle_ib.lse_id
And lse_ib.lty_code = 'INST_ITEM'
And iti.kle_id = cle_ib.id
And hzpsu.party_site_use_id = to_number(iti.object_id1_new)
And hzps.party_site_id = hzpsu.party_site_id
And loc.location_id = hzps.location_id
--And cim_ib.cle_id = cle_ib.id
--And cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
--And cim_ib.object1_id1 = csii.instance_id
--And loc.location_id = csii.location_id
And SUC.SUBSIDY_ID = p_subsidy_id
And SUC.SALES_TERRITORY_CODE = loc.country
And SUC.SALES_TERRITORY_CODE is not null
And cleb.id = p_asset_cle_id; -- 'FREE_FORM1'
select 'Y'
from RA_SALESREP_TERRITORIES rst,
OKC_CONTACTS cro,
OKC_K_PARTY_ROLES_B cplb,
OKC_K_LINES_B cleb,
okl_subsidy_criteria suc
Where
rst.salesrep_id = cro.object1_id1
And cro.object1_id2 = '#'
And cro.jtot_object1_code = 'OKX_SALEPERS'
And cro.cro_code = 'SALESPERSON'
And cro.cpl_id = cplb.id
And cro.dnz_chr_id = cplb.dnz_chr_id
And cplb.chr_id = cleb.dnz_chr_id
And cplb.dnz_chr_id = cleb.dnz_chr_id
And cplb.rle_code = 'LESSOR'
And SUC.SUBSIDY_ID = p_subsidy_id
And SUC.SALES_TERRITORY_ID = rst.territory_id
-- And SUC.SALES_TERRITORY_CODE is not null
And cleb.id = p_asset_cle_id;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.PRODUCT_ID is not null
And suc.subsidy_id = sub.id);
select 'Y'
from okl_k_headers khr,
okc_k_lines_b cleb,
okl_subsidy_criteria suc
Where khr.id = cleb.chr_id
And SUC.subsidy_id = p_subsidy_id
And SUC.product_id = khr.pdt_id
And SUC.product_id is not null
And cleb.id = p_asset_cle_id;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.INDUSTRY_CODE is not null
And suc.INDUSTRY_CODE_TYPE is not null
And suc.subsidy_id = sub.id);
select 'Y'
from hz_parties hp,
hz_cust_accounts_all hca,
okc_k_lines_b cleb,
okc_k_headers_b chrb,
okl_subsidy_criteria suc
where hp.party_id = hca.party_id
And hca.CUST_ACCOUNT_ID = chrb.cust_acct_id
And chrb.id = cleb.chr_id
And SUC.subsidy_id = p_subsidy_id
And SUC.industry_code = hp.sic_code
And SUC.industry_code_type = hp.sic_code_type
And SUC.industry_code is not null
And SUC.industry_code_type is not null
And cleb.id = p_asset_cle_id;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
-- Start : Bug 6050165 : prasjain
-- and TRUNC(nvl(sub.EFFECTIVE_TO_DATE,sysdate) + nvl(sub.EXPIRE_AFTER_DAYS,0)) >= TRUNC(sysdate);
Select 'Y'
from okc_k_headers_b chrb,
okc_k_lines_b cleb,
okl_trx_contracts ktrx
where chrb.id = cleb.chr_id
and chrb.id = cleb.dnz_chr_id
and chrb.orig_system_source_code = 'OKL_REBOOK'
and cleb.id = p_asset_cle_id
and ktrx.khr_id_new = chrb.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1. Multi GAAP Project
AND ktrx.representation_type = 'PRIMARY';
Select 'Y'
from okc_k_headers_b chrb,
okc_k_lines_b cleb,
okl_trx_contracts ktrx
where chrb.id = cleb.chr_id
and chrb.id = cleb.dnz_chr_id
and cleb.id = p_asset_cle_id
and ktrx.KHR_ID = chrb.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 MUlti GAAP Project
AND ktrx.representation_type = 'PRIMARY'
--
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chrb.id
AND rbk_khr.STATUS <> 'PROCESSED');
SELECT 'Y'
FROM OKL_TXL_ASSETS_B TXL,
OKL_TRX_ASSETS TRX,
OKC_K_LINES_B KLE_FIN,
OKC_K_LINES_B KLE_FIX,
OKC_LINE_STYLES_B LTY_FIN,
OKC_LINE_STYLES_B LTY_FIX
WHERE TXL.TAL_TYPE = 'ALI' -- identifies split transaction
AND TRX.TSU_CODE = 'ENTERED' -- split transaction in progress
AND TXL.TAS_ID = TRX.ID
AND KLE_FIN.LSE_ID = LTY_FIN.ID
AND LTY_FIN.LTY_CODE = 'FREE_FORM1'
AND KLE_FIN.ID = KLE_FIX.CLE_ID
AND KLE_FIX.LSE_ID = LTY_FIX.ID
AND LTY_FIX.LTY_CODE = 'FIXED_ASSET'
AND TXL.KLE_ID = KLE_FIX.ID
AND ( KLE_FIN.ID = p_asset_cle_id -- original asset during split
OR KLE_FIN.ID = (SELECT ORIG_SYSTEM_ID1
FROM OKC_K_LINES_B CLE_TMP
WHERE CLE_TMP.ID = p_asset_cle_id) -- new asset generated during split
);
Select 'Y'
from okl_subsidies_b sub,
okc_k_headers_b chrb
where sub.id = p_subsidy_id
and chrb.id = p_chr_id
--check for authoring org id
and chrb.authoring_org_id = sub.org_id
--check for currency code
and chrb.currency_code = sub.currency_code;
Select 'Y'
from okl_subsidies_b sub
where sub.id = p_subsidy_id
and p_start_date between sub.effective_from_date
and nvl(sub.effective_to_date,p_start_date);
select 'Y'
from okl_subsidies_b sub
where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.subsidy_id = sub.id);
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.organization_id is not null
and suc.subsidy_id = sub.id);
Select 'Y'
From
--inv item and org
okl_subsidy_criteria suc
where (suc.organization_id = p_inv_org_id
And nvl(suc.inventory_item_id,p_inv_item_id) = p_inv_item_id
)
And suc.subsidy_id = p_subsidy_id
And suc.organization_id is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.credit_classification_code is not null
-- start: okl.h cklee
-- And suc.id = sub.id);
select 'Y'
from okc_k_headers_all_b chrb,
hz_cust_accounts cust,
okl_subsidy_criteria suc
where chrb.id = p_chr_id
And chrb.cust_acct_id = cust.cust_account_id
And suc.subsidy_id = p_subsidy_id
And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
And SUC.CREDIT_CLASSIFICATION_CODE is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.SALES_TERRITORY_CODE is not null
And suc.subsidy_id = sub.id);
select 'Y'
from hz_locations loc,
hz_party_sites hzps,
hz_party_site_uses hzpsu,
okl_subsidy_criteria suc
Where hzpsu.party_site_use_id = p_install_site_use_id
And hzps.party_site_id = hzpsu.party_site_id
And loc.location_id = hzps.location_id
And SUC.SUBSIDY_ID = p_subsidy_id
And SUC.SALES_TERRITORY_CODE = loc.country
And SUC.SALES_TERRITORY_CODE is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.PRODUCT_ID is not null
And suc.subsidy_id = sub.id);
select 'Y'
from okl_k_headers khr,
okl_subsidy_criteria suc
Where khr.id = p_chr_id
And SUC.subsidy_id = p_subsidy_id
And SUC.product_id = khr.pdt_id
And SUC.product_id is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.INDUSTRY_CODE is not null
And suc.INDUSTRY_CODE_TYPE is not null
And suc.subsidy_id = sub.id);
select 'Y'
from hz_parties hp,
hz_cust_accounts_all hca,
okc_k_headers_b chrb,
okl_subsidy_criteria suc
where hp.party_id = hca.party_id
And hca.CUST_ACCOUNT_ID = chrb.cust_acct_id
And chrb.id = p_chr_id
And SUC.subsidy_id = p_subsidy_id
And SUC.industry_code = hp.sic_code
And SUC.industry_code_type = hp.sic_code_type
And SUC.industry_code is not null
And SUC.industry_code_type is not null;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
Select 'Y'
from okl_subsidies_b sub,
okc_k_headers_b chrb
where sub.id = p_subsidy_id
and chrb.id = p_chr_id
--check for authoring org id
and chrb.authoring_org_id = sub.org_id
--check for currency code
and chrb.currency_code = sub.currency_code;
Select 'Y'
from okl_subsidies_b sub
where sub.id = p_subsidy_id
--check for authoring org id
and sub.org_id = p_authoring_org_id
--check for currency code
and sub.currency_code = p_currency_code;
Select 'Y'
from okl_subsidies_b sub
where sub.id = p_subsidy_id
-- start: okl.h cklee
-- and p_start_date between sub.effective_from_date
-- and nvl(sub.effective_to_date,p_start_date);
select 'Y'
from okl_subsidies_b sub
where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.subsidy_id = sub.id);
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.organization_id is not null
and suc.subsidy_id = sub.id);
Select 'Y'
From
--inv item and org
okl_subsidy_criteria suc
where (suc.organization_id = p_inv_org_id
And nvl(suc.inventory_item_id,p_inv_item_id) = p_inv_item_id
)
And suc.subsidy_id = p_subsidy_id
And suc.organization_id is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.credit_classification_code is not null
-- start: okl.h cklee
-- And suc.id = sub.id);
select 'Y'
from okc_k_headers_b chrb,
hz_cust_accounts cust,
okl_subsidy_criteria suc
where chrb.id = p_chr_id
And chrb.cust_acct_id = to_char(cust.cust_account_id)
And suc.subsidy_id = p_subsidy_id
And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
And SUC.CREDIT_CLASSIFICATION_CODE is not null;
select 'Y'
from hz_cust_accounts cust,
okl_subsidy_criteria suc
where cust.cust_account_id = p_cust_account_id
And suc.subsidy_id = p_subsidy_id
And SUC.CREDIT_CLASSIFICATION_CODE = cust.CREDIT_CLASSIFICATION_CODE
And SUC.CREDIT_CLASSIFICATION_CODE is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.SALES_TERRITORY_ID is not null
And suc.subsidy_id = sub.id);
select 'Y'
from hz_locations loc,
hz_party_sites hzps,
hz_party_site_uses hzpsu,
okl_subsidy_criteria suc
Where hzpsu.party_site_use_id = p_install_site_use_id
And hzps.party_site_id = hzpsu.party_site_id
And loc.location_id = hzps.location_id
And SUC.SUBSIDY_ID = p_subsidy_id
And SUC.SALES_TERRITORY_CODE = loc.country
And SUC.SALES_TERRITORY_CODE is not null;
select 'Y'
from RA_SALESREP_TERRITORIES rst,
okl_subsidy_criteria suc
Where rst.salesrep_id = p_sales_rep_id
And SUC.SUBSIDY_ID = p_subsidy_id
And SUC.SALES_TERRITORY_ID = rst.territory_id;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.PRODUCT_ID is not null
And suc.subsidy_id = sub.id);
select 'Y'
from okl_k_headers khr,
okl_subsidy_criteria suc
Where khr.id = p_chr_id
And SUC.subsidy_id = p_subsidy_id
And SUC.product_id = khr.pdt_id
And SUC.product_id is not null;
select 'Y'
from okl_subsidy_criteria suc
where SUC.subsidy_id = p_subsidy_id
And SUC.product_id = p_pdt_id
And SUC.product_id is not null;
select 'Y'
from okl_subsidies_b sub
Where sub.id = p_subsidy_id
and exists (select 1
from okl_subsidy_criteria suc
where suc.INDUSTRY_CODE is not null
And suc.INDUSTRY_CODE_TYPE is not null
And suc.subsidy_id = sub.id);
select 'Y'
from ra_customers rac,
okc_k_headers_b chrb,
okl_subsidy_criteria suc
where rac.customer_id = chrb.cust_acct_id
And chrb.id = p_chr_id
And SUC.subsidy_id = p_subsidy_id
And SUC.industry_code = rac.sic_code
And SUC.industry_code_type = rac.sic_code_type
And SUC.industry_code is not null
And SUC.industry_code_type is not null;
select 'Y'
from hz_parties hp,
hz_cust_accounts_all hca,
okl_subsidy_criteria suc
where hp.party_id = hca.party_id
And hca.CUST_ACCOUNT_ID = p_cust_account_id
And SUC.subsidy_id = p_subsidy_id
And SUC.industry_code = hp.sic_code
And SUC.industry_code_type = hp.sic_code_type
And SUC.industry_code is not null
And SUC.industry_code_type is not null;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT line.start_date, hdr.currency_code
FROM okc_k_headers_b hdr,
okc_k_lines_b line
WHERE line.id = p_asset_cle_id
AND line.dnz_chr_id = hdr.id;
SELECT line.name
FROM okc_k_lines_v line
WHERE line.id = p_asset_cle_id;
SELECT sub.name
FROM okl_subsidies_b sub
WHERE sub.id = p_subsidy_id;
SELECT sub.subsidy_pool_name
FROM okl_subsidy_pools_v sub
WHERE sub.id = p_subsidy_pool_id;
SELECT sub.subsidy_pool_id,pool.currency_code
FROM okl_subsidies_b sub
,okl_subsidy_pools_b pool
WHERE sub.id = p_subsidy_id
AND sub.subsidy_pool_id = pool.id;
SELECT decision_status_code
,effective_from_date
,effective_to_date
FROM okl_subsidy_pools_b
WHERE id = p_subsidy_pool_id;
SELECT effective_from_date
,effective_to_date
FROM okl_subsidy_pools_b
WHERE id = p_subsidy_pool_id;
SELECT currency_code
,currency_conversion_type
FROM okl_subsidy_pools_b
WHERE id = p_subsidy_pool_id;
SELECT NVL(total_budgets,0) total_budget_amount
,NVL(total_subsidy_amount,0) total_subsidy_amount
FROM okl_subsidy_pools_b
WHERE id = p_subsidy_pool_id;
select SUM(decode(kle_sub.SUBSIDY_OVERRIDE_AMOUNT,
null, nvl(kle_sub.AMOUNT,0),
kle_sub.SUBSIDY_OVERRIDE_AMOUNT))
from okc_k_lines_b cleb_sub,
okc_line_styles_b lseb_sub,
okl_k_lines kle_sub,
okl_subsidies_b sub
where kle_sub.id = cleb_sub.id And
cleb_sub.lse_id = lseb_sub.id And
sub.id = kle_sub.subsidy_id And
sub.subsidy_pool_id = p_subsidy_pool_id And
lseb_sub.lty_code = 'SUBSIDY' And
cleb_sub.sts_code <> 'ABANDONED' And
cleb_sub.dnz_chr_id = (select dnz_chr_id
from okc_k_lines_b cleb_sub1
where cleb_sub1.id = p_asset_id);
SELECT start_date, currency_code
FROM okc_k_lines_b
WHERE id = p_asset_id;
SELECT nvl(total_budgets,0) total_budgets
,nvl(total_subsidy_amount,0) total_subsidy_amount
, subsidy_pool_name
FROM okl_subsidy_pools_b
WHERE id = p_subsidy_pool_id;
select sub.name
from okl_subsidies_b sub
where sub.id = p_subsidy_id;
SELECT nvl(total_budgets,0) total_budgets
,nvl(total_subsidy_amount,0) total_subsidy_amount
, subsidy_pool_name
FROM okl_subsidy_pools_b
WHERE id = p_subsidy_pool_id;
select sub.name
from okl_subsidies_b sub
where sub.id = p_subsidy_id;
select 'Y'
from okl_subsidies_b subb
where subb.id = p_subsidy_id;
select 'Y'
from okc_k_lines_b cleb,
okc_line_styles_b lseb
where cleb.id = p_subsidy_cle_id
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
and cleb.sts_code <> 'ABANDONED';
select 'Y'
from okc_k_headers_b chrb
where chrb.id = p_dnz_chr_id;
select 'Y'
from okc_k_lines_b cleb
where cleb.id = p_asset_cle_id;
select 'Y'
from po_vendors pov
where pov.vendor_id = p_vendor_id;
select 'Y'
from okc_k_lines_b cleb
where cleb.id = p_asset_cle_id
and cleb.dnz_chr_id = p_chr_id;
select 'Y'
from okc_k_lines_b cleb
where cleb.id = p_subsidy_cle_id
and cleb.cle_id = p_asset_cle_id;
select 'Y'
from okc_k_party_roles_b cplb
where cplb.id = p_cpl_id
and cplb.cle_id = p_subsidy_cle_id
and cplb.rle_code = 'OKL_VENDOR';
select 'Y'
from okc_k_party_roles_b cplb
where cplb.chr_id = p_chr_id
and cplb.dnz_chr_id = p_chr_id
and cplb.rle_code = 'OKL_VENDOR'
and cplb.object1_id1 = to_char(p_vendor_id)
and cplb.object1_id2 = '#'
and cplb.jtot_object1_code = 'OKC_VENDOR';
select 'Y',
clet.name subsidy_name,
clet_asst.name asset_number
from okl_k_lines kle,
okc_k_lines_tl clet,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_lines_tl clet_asst
where kle.id = cleb.id
and kle.subsidy_id = p_subsidy_id
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.cle_id = clet_asst.id
and clet_asst.id = p_Asset_cle_id
and clet_asst.language = userenv('LANG')
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
and cleb.sts_code <> 'ABANDOANED'
and cleb.id <> nvl(p_subsidy_cle_id,-999);
Select 'Y',
subb.name
from okl_subsidies_b subb
where subb.id = p_subsidy_id
and nvl(subb.exclusive_yn,'N') = 'Y'
and exists (select '1'
from okc_k_lines_b sub_cleb,
okc_line_styles_b sub_lseb
where sub_cleb.cle_id = p_asset_cle_id
and sub_cleb.sts_code <> 'ABANDONED'
and sub_cleb.id <> nvl(p_subsidy_cle_id,-999)
and sub_lseb.id = sub_cleb.lse_id
and sub_lseb.lty_code = 'SUBSIDY'
);
select 'Y'
from okc_k_lines_b cleb
where cleb.id = p_subsidy_cle_id
and cleb.cle_id = p_asset_cle_id;
select 'Y'
from okc_k_party_roles_b cplb
where cplb.id = p_cpl_id
and cplb.cle_id = p_subsidy_cle_id
and cplb.rle_code = 'OKL_VENDOR';
select 'Y',
clet.name subsidy_name,
clet_asst.name asset_number
from okl_k_lines kle,
okc_k_lines_tl clet,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_lines_tl clet_asst
where kle.id = cleb.id
and kle.subsidy_id = p_subsidy_id
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.cle_id = clet_asst.id
and clet_asst.id = p_Asset_cle_id
and clet_asst.language = userenv('LANG')
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
and cleb.sts_code <> 'ABANDOANED'
and cleb.id <> nvl(p_subsidy_cle_id,-999)
-- cklee 03/15/2004
group by clet.name, clet_asst.name
having count(1) > 1;
Select 'Y',
subb.name
from okl_subsidies_b subb
where subb.id = p_subsidy_id
and nvl(subb.exclusive_yn,'N') = 'Y'
and exists (select '1'
from okc_k_lines_b sub_cleb,
okc_line_styles_b sub_lseb
where sub_cleb.cle_id = p_asset_cle_id
and sub_cleb.sts_code <> 'ABANDONED'
and sub_cleb.id <> nvl(p_subsidy_cle_id,-999)
and sub_lseb.id = sub_cleb.lse_id
and sub_lseb.lty_code = 'SUBSIDY'
);
Select 'Y',
subb.name
from okl_subsidies_b subb,
okc_k_lines_b sub_cleb,
okc_line_styles_b sub_lseb,
okl_k_lines sub_kleb
where sub_cleb.cle_id = p_asset_cle_id
and sub_cleb.sts_code <> 'ABANDONED'
and sub_lseb.id = sub_cleb.lse_id
and sub_lseb.lty_code = 'SUBSIDY'
and sub_kleb.id = sub_cleb.id
and subb.id = sub_kleb.subsidy_id
and subb.exclusive_yn = 'Y';
select count(1)
from okl_subsidies_b subb,
okc_k_lines_b sub_cleb,
okc_line_styles_b sub_lseb,
okl_k_lines sub_kleb
where sub_cleb.cle_id = p_asset_cle_id
and sub_cleb.sts_code <> 'ABANDONED'
and sub_lseb.id = sub_cleb.lse_id
and sub_lseb.lty_code = 'SUBSIDY'
and sub_kleb.id = sub_cleb.id
and subb.id = sub_kleb.subsidy_id;
select clet.name
from okc_k_lines_tl clet
where clet.id = p_cle_id
and clet.language = userenv('LANG');
select subb.name
from okl_subsidies_b subb
where id = p_subsidy_id;
select pov.vendor_name
from po_vendors pov
where vendor_id = p_vendor_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => l_asb_rec.asset_cle_id);
PROCEDURE update_asset_subsidy(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_asb_rec IN asb_rec_type,
x_asb_rec OUT NOCOPY asb_rec_type) is
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'UPDATE_ASSET_SUBSIDY';
select pov.vendor_name
from po_vendors pov
where vendor_id = p_vendor_id;
select ppyd.id
from okl_party_payment_dtls ppyd
where ppyd.cpl_id = p_cpl_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => p_asb_rec.subsidy_cle_id);
p_msg_name => G_PARTY_UPDATE_INVALID,
p_token1 => G_SUBSIDY_TOKEN,
p_token1_value => l_db_asb_rec.name
);
OKL_PYD_PVT.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ppydv_rec => l_ppydv_rec);
OKL_CONTRACT_PUB.update_contract_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
okl_okc_migration_pvt.update_k_party_role(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => l_cplv_rec,
x_cplv_rec => lx_cplv_rec);
okl_k_party_roles_pvt.update_k_party_role(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => l_cplv_rec,
x_cplv_rec => lx_cplv_rec,
p_kplv_rec => l_kplv_rec,
x_kplv_rec => lx_kplv_rec
);
End update_asset_subsidy;
PROCEDURE update_asset_subsidy(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_asb_tbl IN asb_tbl_type,
x_asb_tbl OUT NOCOPY asb_tbl_type) is
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'UPDATE_ASSET_SUBSIDY';
update_asset_subsidy(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_asb_rec => l_asb_tbl(i),
x_asb_rec => x_asb_tbl(i));
End update_asset_subsidy;
PROCEDURE delete_asset_subsidy(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_asb_rec IN asb_rec_type) is
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'DELETE_ASSET_SUBSIDY';
select ppyd.id
from okl_party_payment_dtls ppyd
where ppyd.cpl_id = p_cpl_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => l_db_asb_rec.subsidy_cle_id);
OKL_PYD_PVT.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ppydv_rec => l_ppydv_rec);
OKL_CONTRACT_PUB.delete_contract_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec);
OKL_OKC_MIGRATION_PVT.delete_k_party_role(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => l_cplv_rec);
OKL_K_PARTY_ROLES_PVT.delete_k_party_role(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => l_cplv_rec,
p_kplv_rec => l_kplv_rec);
End delete_asset_subsidy;
PROCEDURE delete_asset_subsidy(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_asb_tbl IN asb_tbl_type) is
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'DELETE_ASSET_SUBSIDY';
delete_asset_subsidy(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_asb_rec => l_asb_tbl(i));
End delete_asset_subsidy;