The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_WF_EVT_KHR_UPDATED CONSTANT VARCHAR2(41) := 'oracle.apps.okl.la.lease_contract.updated';
select a.attribute_label_long
from ak_region_items ri, ak_regions r, ak_attributes_vl a
where ri.region_code = r.region_code
and ri.region_application_id = r.region_application_id
and ri.attribute_code = a.attribute_code
and ri.attribute_application_id = a.attribute_application_id
and ri.region_code = p_ak_region
and ri.attribute_code = p_ak_attribute
--end modified abhsaxen for performance SQLID 20562566
;
SELECT
ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID_RESPONSE,
CHR_ID_AWARD,
INV_ORGANIZATION_ID,
STS_CODE,
QCL_ID,
SCS_CODE,
CONTRACT_NUMBER,
CURRENCY_CODE,
CONTRACT_NUMBER_MODIFIER,
ARCHIVED_YN,
DELETED_YN,
CUST_PO_NUMBER_REQ_YN,
PRE_PAY_REQ_YN,
CUST_PO_NUMBER,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
DPAS_RATING,
COGNOMEN,
TEMPLATE_YN,
TEMPLATE_USED,
DATE_APPROVED,
DATETIME_CANCELLED,
AUTO_RENEW_DAYS,
DATE_ISSUED,
DATETIME_RESPONDED,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
RFP_TYPE,
CHR_TYPE,
KEEP_ON_MAIL_LIST,
SET_ASIDE_REASON,
SET_ASIDE_PERCENT,
RESPONSE_COPIES_REQ,
DATE_CLOSE_PROJECTED,
DATETIME_PROPOSED,
DATE_SIGNED,
DATE_TERMINATED,
DATE_RENEWED,
TRN_CODE,
START_DATE,
END_DATE,
AUTHORING_ORG_ID,
BUY_OR_SELL,
ISSUE_OR_RECEIVE,
ESTIMATED_AMOUNT,
ESTIMATED_AMOUNT_RENEWED,
CURRENCY_CODE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
APPLICATION_ID,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
PROGRAM_ID,
REQUEST_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
PRICE_LIST_ID,
PRICING_DATE,
SIGN_BY_DATE,
TOTAL_LINE_LIST_PRICE,
USER_ESTIMATED_AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okc_K_Headers_V
WHERE okc_k_headers_v.id = p_id;
l_chrv_rec.DELETED_YN,
l_chrv_rec.CUST_PO_NUMBER_REQ_YN,
l_chrv_rec.PRE_PAY_REQ_YN,
l_chrv_rec.CUST_PO_NUMBER,
l_chrv_rec.SHORT_DESCRIPTION,
l_chrv_rec.COMMENTS,
l_chrv_rec.DESCRIPTION,
l_chrv_rec.DPAS_RATING,
l_chrv_rec.COGNOMEN,
l_chrv_rec.TEMPLATE_YN,
l_chrv_rec.TEMPLATE_USED,
l_chrv_rec.DATE_APPROVED,
l_chrv_rec.DATETIME_CANCELLED,
l_chrv_rec.AUTO_RENEW_DAYS,
l_chrv_rec.DATE_ISSUED,
l_chrv_rec.DATETIME_RESPONDED,
l_chrv_rec.NON_RESPONSE_REASON,
l_chrv_rec.NON_RESPONSE_EXPLAIN,
l_chrv_rec.RFP_TYPE,
l_chrv_rec.CHR_TYPE,
l_chrv_rec.KEEP_ON_MAIL_LIST,
l_chrv_rec.SET_ASIDE_REASON,
l_chrv_rec.SET_ASIDE_PERCENT,
l_chrv_rec.RESPONSE_COPIES_REQ,
l_chrv_rec.DATE_CLOSE_PROJECTED,
l_chrv_rec.DATETIME_PROPOSED,
l_chrv_rec.DATE_SIGNED,
l_chrv_rec.DATE_TERMINATED,
l_chrv_rec.DATE_RENEWED,
l_chrv_rec.TRN_CODE,
l_chrv_rec.START_DATE,
l_chrv_rec.END_DATE,
l_chrv_rec.AUTHORING_ORG_ID,
l_chrv_rec.BUY_OR_SELL,
l_chrv_rec.ISSUE_OR_RECEIVE,
l_chrv_rec.ESTIMATED_AMOUNT,
l_chrv_rec.ESTIMATED_AMOUNT_RENEWED,
l_chrv_rec.CURRENCY_CODE_RENEWED,
l_chrv_rec.UPG_ORIG_SYSTEM_REF,
l_chrv_rec.UPG_ORIG_SYSTEM_REF_ID,
l_chrv_rec.APPLICATION_ID,
l_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
l_chrv_rec.ORIG_SYSTEM_ID1,
l_chrv_rec.ORIG_SYSTEM_REFERENCE1,
l_chrv_rec.program_id,
l_chrv_rec.request_id,
l_chrv_rec.program_update_date,
l_chrv_rec.program_application_id,
l_chrv_rec.price_list_id,
l_chrv_rec.pricing_date,
l_chrv_rec.sign_by_date,
l_chrv_rec.total_line_list_price,
l_chrv_rec.USER_ESTIMATED_AMOUNT,
l_chrv_rec.ATTRIBUTE_CATEGORY,
l_chrv_rec.ATTRIBUTE1,
l_chrv_rec.ATTRIBUTE2,
l_chrv_rec.ATTRIBUTE3,
l_chrv_rec.ATTRIBUTE4,
l_chrv_rec.ATTRIBUTE5,
l_chrv_rec.ATTRIBUTE6,
l_chrv_rec.ATTRIBUTE7,
l_chrv_rec.ATTRIBUTE8,
l_chrv_rec.ATTRIBUTE9,
l_chrv_rec.ATTRIBUTE10,
l_chrv_rec.ATTRIBUTE11,
l_chrv_rec.ATTRIBUTE12,
l_chrv_rec.ATTRIBUTE13,
l_chrv_rec.ATTRIBUTE14,
l_chrv_rec.ATTRIBUTE15,
l_chrv_rec.CREATED_BY,
l_chrv_rec.CREATION_DATE,
l_chrv_rec.LAST_UPDATED_BY,
l_chrv_rec.LAST_UPDATE_DATE,
l_chrv_rec.LAST_UPDATE_LOGIN;
SELECT
ID,
OBJECT_VERSION_NUMBER,
ISG_ID,
KHR_ID,
PDT_ID,
AMD_CODE,
DATE_FIRST_ACTIVITY,
GENERATE_ACCRUAL_YN,
GENERATE_ACCRUAL_OVERRIDE_YN,
DATE_REFINANCED,
CREDIT_ACT_YN,
TERM_DURATION,
CONVERTED_ACCOUNT_YN,
DATE_CONVERSION_EFFECTIVE,
SYNDICATABLE_YN,
SALESTYPE_YN,
DATE_DEAL_TRANSFERRED,
DATETIME_PROPOSAL_EFFECTIVE,
DATETIME_PROPOSAL_INEFFECTIVE,
DATE_PROPOSAL_ACCEPTED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PRE_TAX_YIELD,
AFTER_TAX_YIELD,
IMPLICIT_INTEREST_RATE,
IMPLICIT_NON_IDC_INTEREST_RATE,
TARGET_PRE_TAX_YIELD,
TARGET_AFTER_TAX_YIELD,
TARGET_IMPLICIT_INTEREST_RATE,
TARGET_IMPLICIT_NONIDC_INTRATE,
DATE_LAST_INTERIM_INTEREST_CAL,
DEAL_TYPE,
PRE_TAX_IRR,
AFTER_TAX_IRR,
EXPECTED_DELIVERY_DATE,
ACCEPTED_DATE,
PREFUNDING_ELIGIBLE_YN,
REVOLVING_CREDIT_YN,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
ASSIGNABLE_YN
FROM OKL_K_HEADERS_V
WHERE OKL_K_HEADERS_V.id = p_id;
l_khrv_rec.LAST_UPDATED_BY,
l_khrv_rec.LAST_UPDATE_DATE,
l_khrv_rec.LAST_UPDATE_LOGIN,
l_khrv_rec.PRE_TAX_YIELD,
l_khrv_rec.AFTER_TAX_YIELD,
l_khrv_rec.IMPLICIT_INTEREST_RATE,
l_khrv_rec.IMPLICIT_NON_IDC_INTEREST_RATE,
l_khrv_rec.TARGET_PRE_TAX_YIELD,
l_khrv_rec.TARGET_AFTER_TAX_YIELD,
l_khrv_rec.TARGET_IMPLICIT_INTEREST_RATE,
l_khrv_rec.TARGET_IMPLICIT_NONIDC_INTRATE,
l_khrv_rec.DATE_LAST_INTERIM_INTEREST_CAL,
l_khrv_rec.DEAL_TYPE,
l_khrv_rec.PRE_TAX_IRR,
l_khrv_rec.AFTER_TAX_IRR,
l_khrv_rec.EXPECTED_DELIVERY_DATE,
l_khrv_rec.ACCEPTED_DATE,
l_khrv_rec.PREFUNDING_ELIGIBLE_YN,
l_khrv_rec.REVOLVING_CREDIT_YN,
l_khrv_rec.CURRENCY_CONVERSION_RATE,
l_khrv_rec.CURRENCY_CONVERSION_DATE,
l_khrv_rec.CURRENCY_CONVERSION_TYPE,
l_khrv_rec.ASSIGNABLE_YN
;
--SELECT ADD_MONTHS(start_date,term_duration)-1
SELECT chr.start_date, khr.term_duration
FROM okl_k_headers khr,
okc_k_headers_b chr
WHERE khr.id = chr.id
AND chr.id = l_chr_id;
Select scs_code
From okc_k_headers_b
where id = p_source_chr_id;
OKL_CONTRACT_PUB.update_contract_header(
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_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
Select scs_code
From okc_k_headers_b
where id = p_source_chr_id;
SELECT
ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
OBJECT1_ID1,
OBJECT2_ID1,
OBJECT3_ID1,
OBJECT1_ID2,
OBJECT2_ID2,
OBJECT3_ID2,
JTOT_OBJECT1_CODE,
JTOT_OBJECT2_CODE,
JTOT_OBJECT3_CODE,
DNZ_CHR_ID,
RGP_ID,
PRIORITY,
STD_TEMPLATE_YN,
COMMENTS,
WARN_YN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
--TEXT,
RULE_INFORMATION_CATEGORY,
RULE_INFORMATION1,
RULE_INFORMATION2,
RULE_INFORMATION3,
RULE_INFORMATION4,
RULE_INFORMATION5,
RULE_INFORMATION6,
RULE_INFORMATION7,
RULE_INFORMATION8,
RULE_INFORMATION9,
RULE_INFORMATION10,
RULE_INFORMATION11,
RULE_INFORMATION12,
RULE_INFORMATION13,
RULE_INFORMATION14,
RULE_INFORMATION15,
TEMPLATE_YN,
ans_set_jtot_object_code,
ans_set_jtot_object_id1,
ans_set_jtot_object_id2,
DISPLAY_SEQUENCE
FROM Okc_Rules_V
WHERE okc_rules_v.rgp_id = p_rgp_id
AND okc_rules_v.object2_id1 = p_slh_id
AND RULE_INFORMATION_CATEGORY = DECODE(p_rdf_code,NULL,RULE_INFORMATION_CATEGORY,p_rdf_code);
l_rulv_rec.LAST_UPDATED_BY,
l_rulv_rec.LAST_UPDATE_DATE,
l_rulv_rec.LAST_UPDATE_LOGIN,
--l_rulv_rec.TEXT,
l_rulv_rec.RULE_INFORMATION_CATEGORY,
l_rulv_rec.RULE_INFORMATION1,
l_rulv_rec.RULE_INFORMATION2,
l_rulv_rec.RULE_INFORMATION3,
l_rulv_rec.RULE_INFORMATION4,
l_rulv_rec.RULE_INFORMATION5,
l_rulv_rec.RULE_INFORMATION6,
l_rulv_rec.RULE_INFORMATION7,
l_rulv_rec.RULE_INFORMATION8,
l_rulv_rec.RULE_INFORMATION9,
l_rulv_rec.RULE_INFORMATION10,
l_rulv_rec.RULE_INFORMATION11,
l_rulv_rec.RULE_INFORMATION12,
l_rulv_rec.RULE_INFORMATION13,
l_rulv_rec.RULE_INFORMATION14,
l_rulv_rec.RULE_INFORMATION15,
l_rulv_rec.TEMPLATE_YN,
l_rulv_rec.ans_set_jtot_object_code,
l_rulv_rec.ans_set_jtot_object_id1,
l_rulv_rec.ans_set_jtot_object_id2,
l_rulv_rec.DISPLAY_SEQUENCE ;
SELECT orig_system_id1
FROM okc_k_lines_v
WHERE id = p_cle_id;
SELECT id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND id = p_rgp_id
AND rgd_code = 'LALEVL';
SELECT id
FROM okc_rules_v
WHERE rgp_id = p_rgp_id
AND rule_information_category IN ('LASLH','LASLL');
Okl_Rule_Pub.delete_rule(
p_api_version => 1.0,
p_init_msg_list => Okc_Api.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_tbl => l_rulv_tbl
);
SELECT id,
cle_id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id
AND rgd_code = 'LALEVL';
SELECT id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND rgd_code = 'LALEVL'
AND (cle_id = p_cle_id
OR ( cle_id IS NULL
AND
p_cle_id IS NULL));
SELECT orig_system_id1
FROM OKC_K_LINES_V
WHERE id = p_cle_id;
SELECT id,
cle_id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL -- Bug 2376998
AND rgd_code = 'LALEVL';
SELECT id
FROM okc_rule_groups_v
WHERE dnz_chr_id = p_chr_id
AND rgd_code = 'LALEVL'
AND (cle_id = p_cle_id
OR ( cle_id IS NULL
AND
p_cle_id IS NULL));
SELECT orig_system_id1
FROM OKC_K_LINES_V
WHERE id = p_cle_id;
SELECT id,currency_code,currency_conversion_type,currency_conversion_date,currency_conversion_rate, cust_acct_id, khr_id
FROM okl_k_headers_full_v
WHERE scs_code = 'QUOTE'
AND id = p_id;
SELECT rgp.id
FROM okc_rule_groups_b rgp, okc_subclass_rg_defs rg_defs
WHERE rgp.dnz_chr_id = p_source_chr_id
AND rgp.rgd_code = rg_defs.rgd_code
AND rg_defs.scs_code = 'LEASE'
AND rgp.cle_id IS NULL
AND rgp.rgd_code NOT IN ('LATOWN','CURRENCY');
SELECT gvr.chr_id_referred
FROM okc_governances gvr,
okc_k_headers_b CHR,
okc_k_headers_b mla
WHERE gvr.dnz_chr_id = p_source_chr_id
AND gvr.chr_id = p_source_chr_id
AND gvr.cle_id IS NULL
AND CHR.id = gvr.dnz_chr_id
AND mla.id = gvr.chr_id_referred
AND mla.scs_code = 'MASTER_LEASE';
SELECT id
FROM okc_k_articles_b
WHERE dnz_chr_id = p_source_chr_id
AND cle_id IS NULL;
SELECT cpl.id
FROM okc_k_party_roles_b cpl, okc_subclass_roles ROLES
WHERE dnz_chr_id = p_source_chr_id
AND cpl.rle_code = ROLES.rle_code
AND ROLES.scs_code = 'LEASE'
AND cpl.cle_id IS NULL;
SELECT cle.id
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okc_subclass_top_line stl
WHERE chr_id = p_source_chr_id
AND cle.lse_id= lse.id
AND stl.scs_code = 'LEASE'
AND lse.id = stl.lse_id
AND lse.lse_type = 'TLS'
AND lse.lse_parent_id IS NULL;
SELECT cle.id
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okc_subclass_top_line stl
WHERE chr_id = p_dest_chr_id
AND cle.lse_id= lse.id
AND stl.scs_code = 'LEASE'
AND lse.id = stl.lse_id
AND lse.lse_type = 'TLS'
AND lse.lse_parent_id IS NULL;
lp_chrv_rec.deleted_yn := 'N';
SELECT access_level
FROM OKC_ROLE_SOURCES
WHERE rle_code = p_rle_code
AND buy_or_sell = 'S';
lp_chrv_rec.deleted_yn := 'N';
SELECT COUNT(1)
FROM okc_k_party_roles_B
WHERE dnz_chr_id = p_chr_id
AND chr_id = p_chr_id
AND rle_code = G_RLE_CODE
AND object1_id1 = p_customer_id1
AND object1_id2 = p_customer_id2
--end modified abhsaxen for performance SQLID 20562707
;
SELECT access_level
FROM OKC_ROLE_SOURCES
WHERE rle_code = p_rle_code
AND buy_or_sell = 'S';
lp_chrv_rec.deleted_yn := 'N';
SELECT COUNT(1)
FROM okc_k_party_roles_B
WHERE dnz_chr_id = p_chr_id
AND chr_id = p_chr_id
AND rle_code = G_RLE_CODE
AND object1_id1 = p_customer_id1
AND object1_id2 = p_customer_id2
--end modified abhsaxen for performance SQLID 20562694
;
SELECT access_level
FROM OKC_ROLE_SOURCES
WHERE rle_code = p_rle_code
AND buy_or_sell = 'S';
SELECT id, object_version_number, sfwt_flag,
cpl_id, chr_id, cle_id,
rle_code, dnz_chr_id, object1_id1,
object1_id2, jtot_object1_code, cognomen,
code, facility, minority_group_lookup_code,
small_business_flag, women_owned_flag, alias,
attribute_category, attribute1, attribute2,
attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
NULL cust_acct_id, NULL bill_to_site_use_id
FROM okc_k_party_roles_v cplv
WHERE cplv.rle_code = G_LEASE_VENDOR
AND cplv.chr_id = p_source_id; -- vendor program id
lp_chrv_rec.deleted_yn := 'N';
PROCEDURE delete_rules(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_old_khr_id IN NUMBER, -- prev vp id
p_source_id IN NUMBER, -- vp id
p_dest_id IN NUMBER, -- k id
p_org_id IN NUMBER,
p_organization_id IN NUMBER) IS
l_api_name VARCHAR2(30) := 'DELETE_RULES';
SELECT id
FROM okl_k_headers_full_v
WHERE scs_code = 'PROGRAM'
AND sts_code IN ('ACTIVE','PASSED')
AND khr_id = p_vp_id
AND NVL(template_yn,'N') = 'Y'; */
SELECT CHRB.ID ID
FROM OKC_K_HEADERS_ALL_B CHRB,
OKL_K_HEADERS KHR
WHERE CHRB.SCS_CODE = 'PROGRAM'
AND CHRB.ID = KHR.ID
AND CHRB.STS_CODE IN ('ACTIVE', 'PASSED')
AND CHRB.TEMPLATE_YN = 'Y'
AND KHR.KHR_ID = p_vp_id;
SELECT rglease.id, rglease.rgd_code
FROM okc_rule_groups_v rglease
WHERE dnz_chr_id = p_dest_id
AND chr_id = p_dest_id
AND EXISTS(
SELECT '1'
FROM okc_rule_groups_v rg,
okc_k_headers_v hdr
WHERE rg.chr_id = p_vp_tmpl_id
AND hdr.id = rg.chr_id
AND hdr.id = rg.dnz_chr_id
AND rg.cle_id IS NULL
AND rg.rgd_code = rglease.rgd_code
);
OKL_RULE_PUB.delete_rule_group(
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_rgpv_rec => lp_rgpv_rec
);
SELECT id
FROM okl_k_headers_full_v
WHERE scs_code = 'PROGRAM'
AND sts_code IN ('ACTIVE','PASSED')
AND khr_id = p_vp_id
AND NVL(template_yn,'N') = 'Y'; */
SELECT CHRB.ID ID
FROM OKC_K_HEADERS_ALL_B CHRB,
OKL_K_HEADERS KHR
WHERE CHRB.SCS_CODE = 'PROGRAM'
AND CHRB.ID = KHR.ID
AND CHRB.STS_CODE IN ('ACTIVE', 'PASSED')
AND CHRB.TEMPLATE_YN = 'Y'
AND KHR.KHR_ID = p_vp_id;
SELECT rg.id
FROM okc_rule_groups_v rg,
okc_k_headers_v hdr
WHERE rg.chr_id = p_vp_tmpl_id
AND hdr.id = rg.chr_id
AND hdr.id = rg.dnz_chr_id
AND rg.cle_id IS NULL
AND EXISTS
(SELECT '1'
FROM okc_subclass_rg_defs rgdfs
WHERE rgdfs.rgd_code = rg.rgd_code
AND rgdfs.scs_code = 'LEASE')
AND NOT EXISTS
(SELECT '1'
FROM okc_rule_groups_v rglease
WHERE dnz_chr_id =p_dest_id
AND chr_id = p_dest_id
AND rglease.rgd_code = rg.rgd_code);
SELECT fus.application_column_name
FROM FND_DESCR_FLEX_COLUMN_USAGES fus,
fnd_flex_value_sets ffv,
okc_rg_def_rules defrul,
fnd_lookup_values flup,
okc_rule_defs_v rulup
WHERE fus.DESCRIPTIVE_FLEX_CONTEXT_CODE = defrul.rdf_code
AND fus.application_id = 540
AND ffv.flex_value_set_id = fus.flex_value_set_id
AND flup.lookup_code = defrul.rgd_code
AND flup.lookup_type = 'OKC_RULE_GROUP_DEF'
AND rulup.rule_code = defrul.rdf_code
AND defrul.rdf_code = p_rule_code
AND defrul.rgd_code = p_rule_group_code
AND flex_value_set_name = 'OKC_AMOUNT'
--end modified abhsaxen for performance SQLID 20562750
;
select rgp.rgd_code,rul.rule_information_category,rul.id
from okc_rule_groups_b rgp, okc_rules_b rul
where rgp.id = rul.rgp_id
and rul.dnz_chr_id = p_dest_id
and rgp.id = p_rgp_id
and exists (
select 1
from FND_DESCR_FLEX_COLUMN_USAGES fus,
fnd_flex_value_sets ffv,
okc_rg_def_rules defrul,
fnd_lookup_values flup,
okc_rule_defs_b rulup
where fus.descriptive_flex_context_code = defrul.rdf_code
and fus.application_id = 540
and ffv.flex_value_set_id(+) = fus.flex_value_set_id
and flup.lookup_code = defrul.rgd_code
and flup.lookup_type = 'OKC_RULE_GROUP_DEF'
and rulup.rule_code = defrul.rdf_code
and flex_value_set_name = 'OKC_AMOUNT'
and defrul.rdf_code = rul.rule_information_category -- rule
and defrul.rgd_code = rgp.rgd_code -- rule group
);
SELECT currency_code, start_date
FROM okc_k_headers_b
WHERE id = p_dest_id;
SELECT RULE_INFORMATION1,RULE_INFORMATION2,RULE_INFORMATION3,RULE_INFORMATION4,RULE_INFORMATION5,
RULE_INFORMATION6,RULE_INFORMATION7,RULE_INFORMATION8,RULE_INFORMATION9,RULE_INFORMATION10,
RULE_INFORMATION11,RULE_INFORMATION12,RULE_INFORMATION13,RULE_INFORMATION14,RULE_INFORMATION15
FROM okc_rules_v
WHERE id = p_rule_id
AND dnz_chr_id = p_dest_id;
SELECT rgrdfs.id
FROM okc_k_headers_b CHR,
okc_subclass_roles sre,
okc_role_sources rse,
okc_subclass_rg_defs rgdfs,
okc_rg_role_defs rgrdfs
WHERE CHR.id = p_dest_id
AND sre.scs_code = CHR.scs_code
AND sre.rle_code = rse.rle_code
AND rse.rle_code = G_LEASE_VENDOR
AND rse.buy_or_sell = CHR.buy_or_sell
AND rgdfs.scs_code = CHR.scs_code
AND rgdfs.rgd_code = G_VENDOR_BILL_RGD_CODE
AND rgrdfs.srd_id = rgdfs.id
AND rgrdfs.sre_id = sre.id;
SELECT rgpv.id
FROM okc_rule_groups_v rgpv
WHERE rgpv.rgd_code = G_VENDOR_BILL_RGD_CODE
AND rgpv.dnz_chr_id = p_source_id;
select cplv.id
from okc_k_party_roles_b cplv
where cplv.rle_code = g_lease_vendor
and cplv.chr_id = p_source_id
and cplv.dnz_chr_id = cplv.chr_id
and not exists (select 1
from okc_k_party_roles_b s_cpl
where s_cpl.chr_id = p_dest_id
and s_cpl.dnz_chr_id = s_cpl.chr_id
and s_cpl.rle_code = g_lease_vendor
and cplv.object1_id1 = s_cpl.object1_id1
);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_rulv_rec,
x_rulv_rec => lx_rulv_rec);
delete_rules(
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_old_khr_id => p_old_khr_id,
p_source_id => p_source_id,
p_dest_id => p_dest_id,
p_org_id => okl_context.get_okc_org_id,
p_organization_id => okl_context.get_okc_organization_id
);
SELECT template_yn, chr_type
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT template_yn, chr_type
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT template_yn, chr_type
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT CHR.id
FROM okc_k_headers_b CHR
, okc_k_party_roles_b prl
WHERE prl.dnz_chr_id = CHR.id
AND prl.chr_id = CHR.id
AND CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND CHR.template_yn = p_temp_yn
AND prl.rle_code = G_RLE_CODE
AND prl.object1_id1 = p_object1_id1
AND prl.object1_id2 = p_object1_id2
AND prl.jtot_object1_code = p_customer_code
AND CHR.contract_number = p_source_contract_number;
SELECT template_yn, chr_type
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT CHR.id
FROM okc_k_headers_b CHR
, okc_k_party_roles_b prl
WHERE prl.dnz_chr_id = CHR.id
AND prl.chr_id = CHR.id
AND CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND CHR.template_yn = p_temp_yn
AND prl.rle_code = G_RLE_CODE
AND prl.object1_id1 = p_object1_id1
AND prl.object1_id2 = p_object1_id2
AND prl.jtot_object1_code = p_customer_code
AND CHR.contract_number = p_source_contract_number;
SELECT CHR.id
FROM okc_k_headers_b CHR
WHERE CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND CHR.template_yn = p_temp_yn
AND CHR.contract_number = p_source_contract_number;
OKL_CONTRACT_PUB.update_contract_header(
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_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
SELECT template_yn, chr_type
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_source_chr_id;
SELECT CHR.id
FROM okc_k_headers_b CHR
, okc_k_party_roles_b prl
WHERE prl.dnz_chr_id = CHR.id
AND prl.chr_id = CHR.id
AND CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND NVL(chr.template_yn,'N') = p_temp_yn
AND prl.rle_code = G_RLE_CODE
AND prl.object1_id1 = p_object1_id1
AND prl.object1_id2 = p_object1_id2
AND prl.jtot_object1_code = p_customer_code
AND CHR.contract_number = p_source_contract_number;
SELECT CHR.id
FROM okl_k_headers_full_v CHR
, okc_k_party_roles_b prl
WHERE prl.dnz_chr_id = CHR.id
AND prl.chr_id = CHR.id
AND CHR.scs_code = 'LEASE'
AND CHR.chr_type = 'CYA'
AND NVL(chr.template_yn,'N') = 'Y'
AND prl.rle_code = G_RLE_CODE
AND prl.object1_id1 = p_object1_id1
AND prl.object1_id2 = p_object1_id2
AND prl.jtot_object1_code = p_customer_code
AND NVL(CHR.TEMPLATE_TYPE_CODE,'XXX') = 'LEASEAPP'
AND CHR.contract_number = p_source_contract_number;
SELECT CHR.id
FROM okc_k_headers_b CHR,
okl_k_headers khr
, okc_k_party_roles_b prl
WHERE chr.id = khr.id
AND prl.dnz_chr_id = CHR.id
AND prl.chr_id = CHR.id
AND CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND NVL(CHR.template_yn,'N') = p_temp_yn
AND prl.rle_code = G_RLE_CODE
AND prl.object1_id1 = p_object1_id1
AND prl.object1_id2 = p_object1_id2
AND prl.jtot_object1_code = p_customer_code
AND CHR.contract_number = p_source_contract_number
AND exists( select 1 from okl_vp_associations vpaso
where vpaso.chr_id = l_prog_id);
SELECT CHR.id
FROM okc_k_headers_b CHR,
okl_k_headers khr
, okc_k_party_roles_b prl
WHERE chr.id = khr.id
AND prl.dnz_chr_id = CHR.id
AND prl.chr_id = CHR.id
AND CHR.scs_code = 'LEASE'
AND CHR.chr_type = 'CYA'
AND NVL(CHR.template_yn,'N') = 'Y'
AND prl.rle_code = G_RLE_CODE
AND prl.object1_id1 = p_object1_id1
AND prl.object1_id2 = p_object1_id2
AND prl.jtot_object1_code = p_customer_code
AND CHR.contract_number = p_source_contract_number
AND NVL(KHR.TEMPLATE_TYPE_CODE,'XXX') = 'LEASEAPP'
AND khr_id = l_prog_id;
SELECT CHR.id
FROM okc_k_headers_b CHR,
okl_k_headers khr
WHERE chr.id = khr.id
AND CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND NVL(CHR.template_yn,'N') = p_temp_yn
AND CHR.contract_number = p_source_contract_number
AND exists( select 1 from okl_vp_associations vpaso
where vpaso.chr_id = l_prog_id);
SELECT CHR.id
FROM okc_k_headers_b CHR,
okl_k_headers khr
WHERE chr.id = khr.id
AND CHR.scs_code = 'LEASE'
AND CHR.chr_type = 'CYA'
AND NVL(CHR.template_yn,'N') = 'Y'
AND CHR.contract_number = p_source_contract_number
AND NVL(KHR.TEMPLATE_TYPE_CODE,'XXX') = 'LEASEAPP'
AND khr_id = l_prog_id;
SELECT CHR.id
FROM okc_k_headers_b CHR
WHERE CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND CHR.template_yn = p_temp_yn
AND CHR.contract_number = p_source_contract_number;
SELECT CHR.id
FROM okl_k_headers_full_v CHR
WHERE CHR.scs_code = 'LEASE'
AND CHR.chr_type = 'CYA'
AND nvl(CHR.template_yn,'N') = 'Y'
AND NVL(CHR.TEMPLATE_TYPE_CODE,'XXX') = 'LEASEAPP'
AND CHR.contract_number = p_source_contract_number;
SELECT chr.id
FROM okl_k_headers_full_v chr
WHERE chr.scs_code = 'PROGRAM'
AND nvl(chr.template_yn, 'N') = 'N'
AND chr.sts_code = 'ACTIVE'
AND chr.authoring_org_id = p_org_id
AND NVL(chr.start_date,p_effective_from) <= p_effective_from
AND NVL(chr.end_date,p_effective_from) >= p_effective_from
AND chr.contract_number = p_program_name;
SELECT CHR.id
FROM okl_k_headers_full_v CHR
WHERE CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND nvl(chr.template_yn,'N') = 'Y'
AND NVL(chr.template_type_code,'XXX') = OKL_TEMP_TYPE_PROGRAM
AND CHR.contract_number = p_source_contract_number;
AND exists( select 1
from okl_vp_associations vpaso
where vpaso.chr_id = p_prog_id
and vpaso.assoc_object_type_code = 'LC_TEMPLATE'
and NVL(vpaso.start_date,p_effective_from) <= p_effective_from
and NVL(vpaso.end_date,p_effective_from) >= p_effective_from);
SELECT CHR.id
FROM okl_k_headers_full_v CHR
WHERE CHR.scs_code = p_scs_code
AND CHR.chr_type = 'CYA'
AND nvl(chr.template_yn,'N') = 'Y'
AND NVL(chr.template_type_code,'XXX') = OKL_TEMP_TYPE_PROGRAM
AND CHR.contract_number = p_source_contract_number;
select
lsq.id quote_id
from
okl_lease_quotes_b lsq
,okl_lease_quotes_tl lsqt
,okl_lease_opportunities_b lop
where lop.org_id = p_auth_org_id
and lop.inv_org_id = p_inv_org_id
and lsq.parent_object_code = 'LEASEOPP'
and lop.id = lsq.parent_object_id
and lsq.id = lsqt.id
and lsqt.language = userenv('LANG')
and lsq.status = 'ACCEPTED'
and lsq.reference_number = p_quote_number
and not exists (select 1
from okl_lease_apps_all_b
where lease_opportunity_id = lop.id
and application_status <> 'WITHDRAWN')
and not exists (select 1
from okc_k_headers_all_b
where orig_system_source_code = 'OKL_QUOTE'
and orig_system_id1 = lsq.id
and sts_code <> 'ABANDONED');
select
lap.id leaseapp_id
from
okl_lease_applications_b lap
,okl_lease_applications_tl lapt
where
lap.org_id = p_auth_org_id
and lap.inv_org_id = p_inv_org_id
and lap.id = lapt.id
and lapt.language = userenv('LANG')
and lap.application_status = 'CR-APPROVED'
and lap.reference_number = p_leaseapp_number
and not exists (select 1
from okc_k_headers_all_b
where orig_system_source_code = 'OKL_LEASE_APP'
and orig_system_id1 = lap.id
and sts_code <> 'ABANDONED')
and not exists (select 1
from okl_lease_apps_all_b
where parent_leaseapp_id = lap.id
and application_status not in ('WITHDRAWN' , 'CR-REJECTED'))
;
OKL_CONTRACT_PUB.update_contract_header(
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_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
OKL_CONTRACT_PUB.update_contract_header(
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_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
SELECT COUNT(1)
FROM okx_customer_accounts_v ca, okx_parties_v P
WHERE P.id1 = ca.party_id
AND ca.description = p_cust_acc_id1
AND P.name = p_name;
SELECT id
FROM OKL_PRODUCTS_V
WHERE name = p_product_name;
PROCEDURE update_release_contract(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER,
p_contract_number IN VARCHAR2,
p_chr_description IN VARCHAR2,
p_cust_id IN NUMBER,
p_customer_name IN VARCHAR2,
p_customer_id1 IN VARCHAR2,
p_customer_acc_name IN VARCHAR2,
p_customer_acct_id1 IN VARCHAR2,
p_product_name IN VARCHAR2,
p_mla_id IN NUMBER,
p_mla_no IN VARCHAR2,
p_gvr_id_mla IN NUMBER,
p_cl_id IN NUMBER,
p_cl_no IN VARCHAR2,
p_gvr_id_cl IN NUMBER,
p_deal_type IN VARCHAR2,
p_program_no IN VARCHAR2,
p_program_id IN NUMBER,
p_program_yn IN VARCHAR2
) AS
l_api_name VARCHAR2(30) := 'update_release_contract';
select OKX_PARTY.ID1, OKX_PARTY.ID2
from OKX_PARTIES_V OKX_PARTY
where OKX_PARTY.name = p_name
and okx_party.id1 = p_customer_id1;
select ca.id1
from okx_customer_accounts_v ca,
okx_parties_v p
where p.id1 = ca.party_id
and ca.description = p_cust_acc_desc
and p.name = p_name;
SELECT id
FROM OKL_PRODUCTS_V
WHERE name = p_product_name;
SELECT rbr_code
FROM okl_trx_contracts
WHERE khr_id_new = p_chr_id
--rkuttiya added for 12.1.1 multigaap project
AND representation_type = 'PRIMARY';
select id
from OKL_k_headers_full_V
where contract_number = p_mla_no
and scs_code = 'MASTER_LEASE'
and STS_CODE = 'ACTIVE'
and TEMPLATE_YN = 'N'
and BUY_OR_SELL = 'S';
select cl.id
from okl_k_hdr_crdtln_uv cl
where cl.contract_number = p_cl_no
and exists ( select 1
from okc_k_headers_b chr
where chr.currency_code = cl.currency_code
and cl.end_date >= chr.start_date
and cl.cust_name = p_customer_name
and cl.cust_acc_number = p_customer_acc_name);
select id
from OKL_k_headers_full_V prg_hdr
where contract_number = p_program_no
and scs_code = 'PROGRAM'
and nvl(TEMPLATE_YN, 'N') = 'N'
and sts_code = 'ACTIVE'
and exists (select 1
from okc_k_headers_b
where id = p_chr_id
and authoring_org_id = prg_hdr.authoring_org_id);
SELECT khr_id
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
SELECT authoring_org_id, inv_organization_id
FROM okl_k_headers_full_V
WHERE id = p_chr_id;
OKL_CONTRACT_PUB.update_contract_header(
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_restricted_update => 'F',
p_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_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 => lp_lessee_cplv_rec,
x_cplv_rec => lx_lessee_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 => lp_lessee_cplv_rec,
x_cplv_rec => lx_lessee_cplv_rec,
p_kplv_rec => lp_kplv_rec,
x_kplv_rec => lx_kplv_rec);
OKL_OKC_MIGRATION_PVT.update_governance(
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_gvev_rec => lp_mla_gvev_rec,
x_gvev_rec => lx_mla_gvev_rec);
OKL_OKC_MIGRATION_PVT.delete_governance(
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_gvev_rec => lp_mla_gvev_rec);
OKL_OKC_MIGRATION_PVT.update_governance(
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_gvev_rec => lp_cl_gvev_rec,
x_gvev_rec => lx_cl_gvev_rec);
OKL_OKC_MIGRATION_PVT.delete_governance(
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_gvev_rec => lp_cl_gvev_rec);
OKL_CONTRACT_PUB.update_contract_header(
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_restricted_update => 'F',
p_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
PROCEDURE update_deal(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_durv_rec IN deal_rec_type,
x_durv_rec OUT NOCOPY deal_rec_type
) AS
l_api_name VARCHAR2(30) := 'update_deal';
SELECT qcl.id
FROM OKC_QA_CHECK_LISTS_TL qcl,
OKC_QA_CHECK_LISTS_B qclv
WHERE qclv.Id = qcl.id
AND UPPER(qcl.name) = 'OKL LA QA CHECK LIST'
AND qcl.LANGUAGE = USERENV('LANG');
SELECT ADD_MONTHS(p_durv_rec.chr_start_date,p_durv_rec.khr_term_duration)-1
FROM dual;
SELECT vers.major_version||'.'||vers.minor_version
FROM okc_k_vers_numbers_v vers
WHERE vers.chr_id = p_durv_rec.chr_id;
SELECT khr_id
FROM okl_k_headers_full_v
WHERE id = p_durv_rec.chr_id;
SELECT 'Y'
FROM okc_k_headers_b CHR
WHERE EXISTS (SELECT 1
FROM okc_line_styles_b lse,
okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.dnz_chr_id = CHR.id
AND cle.lse_id = lse.id
AND cle.id = kle.id
-- START: cklee/mvasudev,10/14/2005,bug#4300891 - (okl.g Bug#4307723)
AND cle.sts_code <> 'ABANDONED'
-- END: cklee/mvasudev,10/14/2005,bug#4300891 - (okl.g Bug#4307723)
AND lse.lty_code = 'FREE_FORM1')
AND CHR.id = p_durv_rec.chr_id;
SELECT rul.rule_information1
FROM okc_rule_groups_b rgp,
okc_rules_b rul
WHERE rgp.id = rul.rgp_id
AND rgp.rgd_code = 'LARLES'
AND rul.rule_information_category = 'LARLES'
AND rgp.dnz_chr_id = p_durv_rec.chr_id
AND rgp.chr_id = p_durv_rec.chr_id
AND rul.dnz_chr_id = p_durv_rec.chr_id;
SELECT cle.id
FROM okc_line_styles_b lse,
okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.dnz_chr_id = p_durv_rec.chr_id
AND cle.lse_id = lse.id
AND cle.id = kle.id
AND lse.lty_code = 'FREE_FORM1';
SELECT rul.rule_information1
FROM okc_rules_b rul,
okc_rule_groups_b rgp
WHERE rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LACPLN'
AND rgp.RGD_CODE = 'LACPLN'
AND rul.dnz_chr_id = rgp.dnz_chr_id
AND rgp.dnz_chr_id = p_durv_rec.chr_id
AND rgp.chr_id = p_durv_rec.chr_id;
select chr.orig_system_source_code
from okc_k_headers_b chr
where chr.id = p_durv_rec.chr_id;
SELECT chrb.template_yn,
khr.template_type_code
FROM okc_k_headers_b chrb,
okl_k_headers khr
WHERE chrb.id = khr.id
AND chrb.id = p_chr_id;
SELECT qcl.id
FROM OKC_QA_CHECK_LISTS_TL qcl,
OKC_QA_CHECK_LISTS_B qclv
WHERE qclv.Id = qcl.id
AND UPPER(qcl.name) = 'OKL KT for PA QA Checklist'
AND qcl.LANGUAGE = 'US';
SELECT qcl.id
FROM OKC_QA_CHECK_LISTS_TL qcl,
OKC_QA_CHECK_LISTS_B qclv
WHERE qclv.Id = qcl.id
AND UPPER(qcl.name) = 'OKL KT FOR LA QA CHECKLIST'
AND qcl.LANGUAGE = 'US';
p_event_name => G_WF_EVT_KHR_UPDATED,
p_parameters => l_parameter_list);
update_release_contract(
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_chr_id => p_durv_rec.chr_id,
p_contract_number => p_durv_rec.chr_contract_number,
p_chr_description => p_durv_rec.chr_description,
p_cust_id => p_durv_rec.cust_id,
p_customer_name => p_durv_rec.cust_name,
p_customer_id1 => p_durv_rec.cust_object1_id1,
p_customer_acc_name => p_durv_rec.customer_account,
p_customer_acct_id1 => p_durv_rec.chr_cust_acct_id,
p_product_name => p_durv_rec.product_name,
p_mla_id => p_durv_rec.mla_gvr_chr_id_referred,
p_mla_no => p_durv_rec.mla_contract_number,
p_gvr_id_mla => p_durv_rec.mla_gvr_id,
p_cl_id => p_durv_rec.cl_gvr_chr_id_referred,
p_cl_no => p_durv_rec.cl_contract_number,
p_gvr_id_cl => p_durv_rec.cl_gvr_id,
p_deal_type => p_durv_rec.khr_deal_type,
p_program_no => p_durv_rec.program_contract_number,
p_program_id => p_durv_rec.khr_khr_id,
p_program_yn => p_durv_rec.khr_generate_accrual_yn
);
OKL_CONTRACT_PUB.update_contract_header(
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_restricted_update => 'F',
p_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_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 => lp_lessee_cplv_rec,
x_cplv_rec => lx_lessee_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 => lp_lessee_cplv_rec,
x_cplv_rec => lx_lessee_cplv_rec,
p_kplv_rec => lp_kplv_rec,
x_kplv_rec => lx_kplv_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 => lp_lessee_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 => lp_lessee_cplv_rec,
p_kplv_rec => lp_kplv_rec);
OKL_OKC_MIGRATION_PVT.update_contact(
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_ctcv_rec => lp_ctcv_rec,
x_ctcv_rec => lx_ctcv_rec);
OKL_OKC_MIGRATION_PVT.delete_contact(
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_ctcv_rec => lp_ctcv_rec);
OKL_OKC_MIGRATION_PVT.update_governance(
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_gvev_rec => lp_mla_gvev_rec,
x_gvev_rec => lx_mla_gvev_rec);
OKL_OKC_MIGRATION_PVT.delete_governance(
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_gvev_rec => lp_mla_gvev_rec);
OKL_OKC_MIGRATION_PVT.update_governance(
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_gvev_rec => lp_cl_gvev_rec,
x_gvev_rec => lx_cl_gvev_rec);
OKL_OKC_MIGRATION_PVT.delete_governance(
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_gvev_rec => lp_cl_gvev_rec);
OKL_RULE_PUB.update_rule_group(
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_rgpv_rec => lp_larles_rgpv_rec,
x_rgpv_rec => lx_larles_rgpv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_larles_rulv_rec,
x_rulv_rec => lx_larles_rulv_rec);
OKL_RULE_PUB.update_rule_group(
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_rgpv_rec => lp_larebl_rgpv_rec,
x_rgpv_rec => lx_larebl_rgpv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_larebl_rulv_rec,
x_rulv_rec => lx_larebl_rulv_rec);
OKL_RULE_PUB.delete_rule(
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_rulv_rec => lp_larebl_rulv_rec);
OKL_RULE_PUB.update_rule_group(
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_rgpv_rec => lp_lanntf_rgpv_rec,
x_rgpv_rec => lx_lanntf_rgpv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_lanntf_rulv_rec,
x_rulv_rec => lx_lanntf_rulv_rec);
OKL_RULE_PUB.update_rule_group(
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_rgpv_rec => lp_lacpln_rgpv_rec,
x_rgpv_rec => lx_lacpln_rgpv_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 => lp_clev_rec,
p_klev_rec => lp_klev_rec,
p_edit_mode => 'N',
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_lacpln_rulv_rec,
x_rulv_rec => lx_lacpln_rulv_rec);
OKL_RULE_PUB.update_rule_group(
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_rgpv_rec => lp_lapact_rgpv_rec,
x_rgpv_rec => lx_lapact_rgpv_rec);
OKL_RULE_PUB.update_rule(
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_rulv_rec => lp_lapact_rulv_rec,
x_rulv_rec => lx_lapact_rulv_rec);
SELECT id,
contract_number,
description,
sts_code,
start_date,
end_date,
term_duration,
cust_po_number,
inv_organization_id,
authoring_org_id,
generate_accrual_yn,
syndicatable_yn,
prefunding_eligible_yn,
revolving_credit_yn,
converted_account_yn,
credit_act_yn,
template_yn,
date_signed,
date_deal_transferred,
accepted_date,
expected_delivery_date,
amd_code,
deal_type,
-- orig_system_reference1,
-- orig_system_source_code,
-- orig_system_id1,
currency_code,
pdt_id,
khr_id
FROM okl_k_headers_full_v
WHERE id = p_durv_rec.chr_id;
SELECT vers.major_version||'.'||vers.minor_version
FROM okc_k_vers_numbers_v vers
WHERE vers.chr_id = p_durv_rec.chr_id;
SELECT mla.contract_number, gvr.chr_id_referred, gvr.id
FROM okl_k_headers_full_v mla, okc_governances_v gvr
WHERE mla.id = gvr.chr_id_referred
AND mla.scs_code = p_scs_code -- mla or cl
AND gvr.chr_id = p_durv_rec.chr_id
AND gvr.dnz_chr_id = p_durv_rec.chr_id;
SELECT rgp.id, rul.id, rul.rule_information1
FROM okc_rule_groups_v rgp, okc_rules_v rul
WHERE rgp.id = rul.rgp_id
AND rgp.rgd_code = p_rgd_code
AND rul.rule_information_category = p_rule_information_category
AND rul.dnz_chr_id = p_durv_rec.chr_id
AND rgp.dnz_chr_id = p_durv_rec.chr_id
AND rgp.chr_id = p_durv_rec.chr_id;
SELECT cpl.id,cpl.object1_id1,cpl.object1_id2,cpl.jtot_object1_code
FROM okc_k_party_roles_B cpl
WHERE cpl.rle_code = p_rle_code
AND cpl.chr_id = p_durv_rec.chr_id
AND cpl.dnz_chr_id = p_durv_rec.chr_id
--end modified abhsaxen for performance SQLID 20563248
;
SELECT rgp.id, rul.id, rul.object1_id1,rul.object1_id2,rul.jtot_object1_code
FROM okc_rule_groups_v rgp, okc_rules_v rul
WHERE rgp.id = rul.rgp_id
AND rgp.rgd_code = 'LACAN'
AND rul.rule_information_category = 'CAN'
AND rul.dnz_chr_id = p_durv_rec.chr_id
AND rgp.dnz_chr_id = p_durv_rec.chr_id
AND rgp.chr_id = p_durv_rec.chr_id;
SELECT cust_site.description
FROM okx_cust_site_uses_v cust_site
WHERE cust_site.party_id = p_cust_id1
AND cust_site.site_use_code = 'LEGAL';
SELECT id,name,description
FROM okl_products_v
WHERE id = product_id;
SELECT currency_code, name
FROM fnd_currencies_vl
WHERE currency_code = p_currency_code;
SELECT id,contract_number
FROM okl_k_headers_full_v
WHERE id = program_id;
select ctc.id,ctc.object1_id1,ctc.object1_id2,ctc.jtot_object1_code
from okc_k_party_roles_b cpl, okc_contacts ctc
where cpl.id = ctc.cpl_id
and cpl.rle_code = 'LESSOR'
and ctc.cro_code = 'SALESPERSON'
and cpl.chr_id = p_durv_rec.chr_id
and cpl.dnz_chr_id = p_durv_rec.chr_id
and ctc.dnz_chr_id = p_durv_rec.chr_id
--end modified abhsaxen for performance SQLID 20563327
;
SELECT CHR.sts_code
FROM OKC_STATUSES_B sts,okc_k_headers_v CHR
WHERE STS.CODE = CHR.STS_CODE
AND CHR.id = p_durv_rec.chr_id;
PROCEDURE update_party(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_kpl_rec IN party_rec_type,
x_kpl_rec OUT NOCOPY party_rec_type
) AS
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'update_party';
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 => lp_cplv_rec,
x_cplv_rec => lx_cplv_rec,
p_kplv_rec => lp_kplv_rec,
x_kplv_rec => lx_kplv_rec);