[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_STS_UPDATE_TRX_MISSING VARCHAR2(200) := 'OKL_LLA_STS_UPDATE_TRX_MISSING';
SELECT
cim.ID,
cim.OBJECT_VERSION_NUMBER,
cim.CLE_ID,
cim.CHR_ID,
cim.CLE_ID_FOR,
cim.DNZ_CHR_ID,
cim.OBJECT1_ID1,
cim.OBJECT1_ID2,
cim.JTOT_OBJECT1_CODE,
cim.UOM_CODE,
cim.EXCEPTION_YN,
cim.NUMBER_OF_ITEMS,
cim.UPG_ORIG_SYSTEM_REF,
cim.UPG_ORIG_SYSTEM_REF_ID,
cim.PRICED_ITEM_YN,
cim.CREATED_BY,
cim.CREATION_DATE,
cim.LAST_UPDATED_BY,
cim.LAST_UPDATE_DATE,
cim.LAST_UPDATE_LOGIN
FROM Okc_K_Items_V cim
where cle_id = p_cle_id;
l_cimv_rec.LAST_UPDATED_BY,
l_cimv_rec.LAST_UPDATE_DATE,
l_cimv_rec.LAST_UPDATE_LOGIN;
SELECT ID,
OBJECT_VERSION_NUMBER,
TAS_ID,
TAL_ID,
KLE_ID,
TAL_TYPE,
LINE_NUMBER,
INSTANCE_NUMBER_IB,
OBJECT_ID1_NEW,
OBJECT_ID2_NEW,
JTOT_OBJECT_CODE_NEW,
OBJECT_ID1_OLD,
OBJECT_ID2_OLD,
JTOT_OBJECT_CODE_OLD,
INVENTORY_ORG_ID,
SERIAL_NUMBER,
MFG_SERIAL_NUMBER_YN,
INVENTORY_ITEM_ID,
INV_MASTER_ORG_ID,
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 OKL_TXL_ITM_INSTS iti
WHERE iti.kle_id = p_kle_id
and iti.tal_type = p_trx_type
and exists (select '1' from OKL_TRX_ASSETS
where OKL_TRX_ASSETS.TAS_TYPE = p_trx_type
and OKL_TRX_ASSETS.TSU_CODE = G_TSU_CODE_ENTERED
and OKL_TRX_ASSETS.ID = iti.tas_id);
l_iipv_rec.LAST_UPDATED_BY,
l_iipv_rec.LAST_UPDATE_DATE,
l_iipv_rec.LAST_UPDATE_LOGIN;
PROCEDURE update_trx_status(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tas_id IN NUMBER,
p_tsu_code IN VARCHAR2) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'update_trx_status';
SELECT tsu_code
FROM OKL_TRX_ASSETS
WHERE id = p_tas_id;
p_msg_name => G_STS_UPDATE_TRX_MISSING,
p_token1 => G_TAS_ID_TOKEN,
p_token1_value => p_tas_id
);
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
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_thpv_rec => l_thpv_rec,
x_thpv_rec => l_thpv_rec_out);
END update_trx_status;
SELECT P.PARTY_ID
INTO l_party_id
FROM HZ_PARTIES P, OKC_K_PARTY_ROLES_B OKPRV
WHERE OKPRV.chr_id = p_chrv_id
AND OKPRV.rle_code = 'LESSEE'
AND OKPRV.jtot_object1_code = 'OKX_PARTY'
AND p.PARTY_ID = OKPRV.object1_id1
AND p.party_type in ('PERSON', 'ORGANIZATION');
select transaction_type_id
from CSI_TXN_TYPES
where source_transaction_type = p_transaction_type;
SELECT MP.master_organization_id
FROM MTL_PARAMETERS MP,
OKC_K_HEADERS_B CHR
WHERE MP.organization_id = CHR.inv_organization_id
AND CHR.id = p_chr_id;
SELECT cim.number_of_items,
cim.object1_id1,
cim.object1_id2
FROM OKC_K_ITEMS CIM,
OKC_K_LINES_B MDL,
OKC_LINE_STYLES_B MDL_LSE,
OKC_K_LINES_B INST
WHERE CIM.CLE_ID = MDL.ID
AND MDL.CLE_ID = INST.CLE_ID
AND MDL.LSE_ID = MDL_LSE.ID
AND MDL_LSE.LTY_CODE = G_MODEL_LINE_LTY_CODE
AND INST.ID = p_inst_line_id;
SELECT nvl(comms_nl_trackable_flag,'N'),
segment1,
description,
--Bug#2845959
primary_uom_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id;
SELECT to_number(rulv.object1_id1)
FROM OKC_RULES_V rulv
WHERE rulv.rule_information_category = G_CUST_ACCT_RULE
AND rulv.dnz_chr_id = p_chrv_id
AND exists (select '1'
from OKC_RULE_GROUPS_V rgpv
where rgpv.chr_id = p_chrv_id
and rgpv.rgd_code = G_CUST_ACCT_RULE_GROUP
and rgpv.id = rulv.rgp_id);
SELECT chrb.cust_acct_id
FROM OKC_K_HEADERS_B chrb
WHERE chrb.id = p_chrv_id;
SELECT location_id,
party_site_id
FROM OKX_PARTY_SITE_USES_V
WHERE id1 = p_site_use_id1
AND id2 = p_site_use_id2;
okl_okc_migration_pvt.update_contract_item
(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_cimv_rec => l_cimv_rec,
x_cimv_rec => l_cimv_rec_out);
update_trx_status(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_tas_id => l_iipv_rec.tas_id,
p_tsu_code => G_TSU_CODE_PROCESSED);
SELECT cim.number_of_items,
cim.object1_id1,
cim.object1_id2
FROM OKC_K_ITEMS CIM,
OKC_K_LINES_B MDL,
OKC_K_LINES_B INST
WHERE CIM.CLE_ID = MDL.ID
AND MDL.CLE_ID = INST.CLE_ID
AND MDL.LSE_ID = G_MODEL_LINE_LTY_ID
AND INST.ID = p_inst_line_id;
SELECT nvl(comms_nl_trackable_flag,'N'),
segment1,
description,
--Bug#2845959
primary_uom_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id;
SELECT HPS.location_id,
HPS.party_site_id
FROM HZ_PARTY_SITE_USES HPSU, HZ_PARTY_SITES HPS
WHERE HPS.party_site_id = HPSU.party_site_id
AND HPSU.party_site_use_id = p_site_use_id1;
SELECT INSTANCE_ID
FROM CSI_ITEM_INSTANCES CSI
WHERE SERIAL_NUMBER = p_serial_number
AND INVENTORY_ITEM_ID = p_inv_item_id
AND INV_MASTER_ORGANIZATION_ID = p_inv_mstr_org_id
AND INSTANCE_STATUS_ID IN (SELECT INSTANCE_STATUS_ID
FROM CSI_INSTANCE_STATUSES
WHERE TERMINATED_FLAG = 'N')
AND NVL(ACTIVE_END_DATE,(p_khr_start_date+1)) > p_khr_start_date
AND ROWNUM = 1
AND NOT EXISTS
(SELECT CLE.DNZ_CHR_ID
FROM OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B CLS,
OKC_K_ITEMS CIM,
OKX_INSTALL_ITEMS_V CIX,
OKL_K_HEADERS KHR
WHERE CLE.LSE_ID = CLS.ID
AND CLE.DNZ_CHR_ID = KHR.ID
AND CLS.LTY_CODE = 'INST_ITEM'
AND CLE.ID = CIM.CLE_ID
AND CIM.OBJECT1_ID1 = CIX.ID1
AND CIM.OBJECT1_ID2 = CIX.ID2
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CIX.INSTANCE_ID = CSI.INSTANCE_ID);
SELECT '!'
FROM okc_k_headers_b CHR
WHERE chr.id = p_contract_id
AND EXISTS (SELECT '1'
FROM okc_line_styles_b lse,
okc_k_lines_b cle
WHERE cle.sts_code = 'APPROVED'
AND lse.id = cle.lse_id
AND lse.lty_code = 'USAGE'
AND cle.dnz_chr_id = chr.id);
okl_okc_migration_pvt.update_contract_item
(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_cimv_rec => l_cimv_rec,
x_cimv_rec => l_cimv_rec_out);
update_trx_status(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_tas_id => l_iipv_rec.tas_id,
p_tsu_code => G_TSU_CODE_PROCESSED);
SELECT SCS_CODE,
STS_CODE,
INV_ORGANIZATION_ID,
START_DATE -- rkuttiya added for bug # 6795295
From OKC_K_HEADERS_B
WHERE ID = P_CHRV_ID;
SELECT cle.id,
cle.cle_id
from okc_k_lines_b cle,
okc_statuses_b sts -- 4698117
where cle.lse_id = G_IB_LINE_LTY_ID
and cle.dnz_chr_id = p_chrv_id
and cle.sts_code = sts.code
and sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED');
SELECT MP.master_organization_id
FROM MTL_PARAMETERS MP,
OKC_K_HEADERS_B CHR
WHERE MP.organization_id = CHR.inv_organization_id
AND CHR.id = p_chr_id;
SELECT chrb.cust_acct_id
FROM OKC_K_HEADERS_B chrb
WHERE chrb.id = p_chrv_id;
l_ib_cle_id_tbl.delete;
l_inst_cle_id_tbl.delete;
SELECT cle.id,
cle.cle_id,
cle.dnz_chr_id
from okc_k_lines_b cle,
okc_line_styles_b lse,
okc_k_lines_b inst_cle,
okc_line_styles_b inst_cle_lse
where lse.id = cle.lse_id
and lse.lty_code = p_lty_code
and cle.cle_id = inst_cle.id
and cle.dnz_chr_id = inst_cle.dnz_chr_id
and inst_cle_lse.id = inst_cle.lse_id
and inst_cle_lse.lty_code = 'FREE_FORM2'
and inst_cle.cle_id = p_fin_ast_cle_id
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug#2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED'))
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED'))
AND not exists (select '1'
from OKC_STATUSES_B sts2
Where sts2.code = inst_cle.sts_code
--Bug#2522268
--And sts2.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED'));
SELECT khr.deal_type,
chr.id,
chr.sts_code,
chr.orig_system_id1,
khr.pdt_id,
chr.start_date,
cplb.object1_id1,
chr.cust_acct_id,
chr.scs_code
FROM OKC_K_PARTY_ROLES_B cplb,
OKC_RULES_B rul,
OKL_K_HEADERS khr,
OKC_K_HEADERS_B chr
WHERE cplb.chr_id = chr.id
AND cplb.dnz_chr_id = chr.id
AND cplb.rle_code = 'LESSEE'
AND rul.dnz_chr_id = chr.id
AND rul.rule_information_category = 'LARLES'
AND rul.dnz_chr_id = khr.id --added as part of performance tuning by dkagrawa
AND khr.id = chr.id
AND chr.id = p_rel_chr_id
AND nvl(rul.Rule_information1,'N') = 'Y';
Select cim_ib.object1_id1,
iti.object_id1_new,
trx.id tas_id,
cleb_ib.id cleb_ib_id
From okc_k_items cim_ib,
okc_k_lines_b cleb_ib,
okc_line_styles_b lseb_ib,
okc_statuses_b stsb,
okl_txl_itm_insts iti,
okl_trx_assets trx,
okl_trx_types_tl ttyt
where iti.kle_id = cleb_ib.id
and iti.tas_id = trx.id
and trx.tas_type = 'CRL'
and trx.tsu_code = 'ENTERED'
and trx.try_id = ttyt.id
and ttyt.language = userenv('LANG')
and ttyt.name = 'Internal Asset Creation'
and cim_ib.cle_id = cleb_ib.id
and cim_ib.dnz_chr_id = cleb_ib.dnz_chr_id
and cleb_ib.dnz_chr_id = p_rel_chr_id
and lseb_ib.id = cleb_ib.lse_id
and lseb_ib.lty_code = 'INST_ITEM'
and cleb_ib.sts_code = stsb.code
and stsb.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
Select *
from csi_item_instances
where instance_id = p_instance_id;
Select hps.location_id,
hpsu.party_site_id
from hz_party_sites hps,
hz_party_site_uses hpsu
where hps.party_site_id = hpsu.party_site_id
and hpsu.party_site_use_id = p_site_use_id;
select *
from csi_i_parties
where instance_id = p_instance_id
and relationship_type_code = 'OWNER'
and active_end_date is null;
l_update_required varchar2(1) default 'N';
l_update_required := 'N';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
l_update_required := 'Y';
If l_update_required = 'Y' then
okl_context.set_okc_org_context(p_chr_id => p_rel_chr_id);
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
,p_ext_attrib_values_tbl => l_extend_attrib_values_tbl
,p_party_tbl => l_party_tbl
,p_account_tbl => l_party_account_tbl
,p_pricing_attrib_tbl => l_pricing_attribs_tbl
,p_org_assignments_tbl => l_organization_units_tbl
,p_asset_assignment_tbl => l_instance_Asset_tbl
,p_txn_rec => l_transaction_rec
,x_instance_id_lst => l_id_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
l_extend_attrib_values_tbl.delete;
l_party_tbl.delete;
l_party_account_tbl.delete;
l_pricing_attribs_tbl.delete;
l_organization_units_tbl.delete;
l_instance_Asset_tbl.delete;
l_instance_header_tbl.delete;
update_trx_status(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_tas_id => l_ib_line_rec.tas_id,
p_tsu_code => G_TSU_CODE_PROCESSED);
SELECT mtl.serial_number_control_code
FROM mtl_system_items mtl,
okc_k_headers_b chrb
WHERE mtl.inventory_item_id = p_inv_item_id
AND mtl.organization_id = chrb.inv_organization_id
--BUG# 3489089
AND chrb.id = p_chr_id;
SELECT mtl.serial_number_control_code
FROM mtl_system_items mtl,
okc_k_headers_b chrb,
okc_k_lines_b cleb
WHERE mtl.inventory_item_id = p_inv_item_id
AND mtl.organization_id = chrb.inv_organization_id
AND chrb.id = cleb.dnz_chr_id
AND cleb.id = p_cle_id;
SELECT ib_cle.id,
ib_cle.cle_id
from okc_k_lines_b ib_cle,
okc_k_lines_b inst_cle,
okc_statuses_b inst_sts
where ib_cle.lse_id = G_IB_LINE_LTY_ID
and ib_cle.dnz_chr_id = p_chrv_id
AND inst_sts.code = ib_cle.sts_code
AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
AND ib_cle.cle_id = inst_cle.id
and inst_cle.dnz_chr_id = p_chrv_id
and inst_cle.cle_id = p_fin_ast_cle_id;
SELECT cim.object1_id1
from okc_k_items cim
where cim.cle_id = p_cle_id;
select chr.ORIG_SYSTEM_ID1 orig_chr_id,
cle.orig_system_id1 orig_fin_ast_cle_id
from okc_k_headers_b chr,
okc_k_lines_b cle
where chr.id = p_chr_id
and cle.id = p_fin_ast_cle_id
and cle.chr_id = p_chr_id
and cle.dnz_chr_id = p_chr_id;
SELECT orig_ib_cle.id orig_ib_cle_id,
orig_ib_cle.cle_id orig_inst_cle_id,
orig_ib_cle.orig_system_id1 rbk_ib_cle_id
FROM okc_k_lines_b orig_ib_cle,
okc_k_lines_b orig_inst_cle,
okc_k_lines_b rbk_inst_cle,
okc_statuses_b inst_sts
WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
AND orig_ib_cle.cle_id = orig_inst_cle.id
AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
AND rbk_inst_cle.id = orig_inst_cle.orig_system_id1
AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
AND inst_sts.code = orig_ib_cle.sts_code
AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
SELECT orig_ib_cle.id ib_cle_id,
orig_ib_cle.cle_id inst_cle_id,
orig_ib_cim.object1_id1 instance_id
FROM okc_k_items orig_ib_cim,
okc_k_lines_b orig_ib_cle,
okc_k_lines_b orig_inst_cle,
okc_statuses_b inst_sts
WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
AND orig_ib_cle.cle_id = orig_inst_cle.id
AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
AND orig_ib_cim.cle_id = orig_ib_cle.id
AND orig_ib_cim.dnz_chr_id = p_orig_chr_id
AND orig_ib_cim.object1_id1 IS NOT NULL
AND inst_sts.code = orig_ib_cle.sts_code
AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
AND NOT EXISTS (
SELECT 1
FROM okc_k_lines_b rbk_inst_cle,
okc_statuses_b rbk_inst_sts
WHERE rbk_inst_cle.orig_system_id1 = orig_inst_cle.id
AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
AND rbk_inst_sts.code = rbk_inst_cle.sts_code
AND rbk_inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED'));
CURSOR srl_num_to_update_csr(p_orig_fin_ast_cle_id IN NUMBER,
p_rbk_fin_ast_cle_id IN NUMBER,
p_orig_chr_id IN NUMBER,
p_rbk_chr_id IN NUMBER ) IS
SELECT orig_ib_cle.id orig_ib_cle_id,
orig_ib_cle.cle_id orig_inst_cle_id,
rbk_ib_cle.id rbk_ib_cle_id,
rbk_ib_cle.cle_id rbk_inst_cle_id,
TO_NUMBER(ib_cim.object1_id1) instance_id,
ib_cim.id orig_ib_cim_id
FROM okc_k_lines_b orig_ib_cle,
okc_k_lines_b orig_inst_cle,
okc_k_lines_b rbk_inst_cle,
okc_k_lines_b rbk_ib_cle,
okc_statuses_b inst_sts,
okc_k_items ib_cim
WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
AND orig_ib_cle.cle_id = orig_inst_cle.id
AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
AND rbk_inst_cle.orig_system_id1 = orig_inst_cle.id
AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
AND rbk_ib_cle.cle_id = rbk_inst_cle.id
AND rbk_ib_cle.dnz_chr_id = p_rbk_chr_id
AND rbk_ib_cle.lse_id = G_IB_LINE_LTY_ID
AND inst_sts.code = orig_ib_cle.sts_code
AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
AND ib_cim.cle_id = orig_ib_cle.id
AND ib_cim.dnz_chr_id = p_orig_chr_id;
SELECT csi_item.serial_number
FROM csi_item_instances csi_item
WHERE csi_item.instance_id = p_instance_id;
SELECT serial_number
FROM okl_txl_itm_insts iti,
okl_trx_assets tas
WHERE iti.dnz_cle_id = P_rbk_fin_ast_cle_id
AND iti.kle_id = p_rbk_ib_cle_id
AND iti.tal_type = G_TRX_LINE_TYPE_REBOOK
AND tas.tas_type = G_TRX_LINE_TYPE_REBOOK
AND tas.tsu_code = G_TSU_CODE_ENTERED
AND tas.id = iti.tas_id;
SELECT csi_item.inventory_item_id
FROM csi_item_instances csi_item,
okc_k_items ib_cim,
okc_k_lines_b ib_cle,
okc_k_lines_b inst_cle,
okc_statuses_b inst_sts
WHERE ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = p_chr_id
AND inst_cle.cle_id = p_fin_ast_cle_id
AND inst_cle.lse_id = G_INST_LINE_LTY_ID
AND inst_cle.dnz_chr_id = p_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.lse_id = G_IB_LINE_LTY_ID
AND ib_cle.dnz_chr_id = p_chr_id
AND csi_item.instance_id = TO_NUMBER(ib_cim.object1_id1)
AND inst_sts.code = ib_cle.sts_code
AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
SELECT cim.number_of_items,
cim.object1_id1,
cim.object1_id2
FROM OKC_K_ITEMS CIM,
OKC_K_LINES_B MDL,
OKC_LINE_STYLES_B MDL_LSE
WHERE CIM.CLE_ID = MDL.ID
AND MDL.CLE_ID = p_fin_ast_cle_id
AND MDL.LSE_ID = MDL_LSE.ID
AND MDL_LSE.LTY_CODE = G_MODEL_LINE_LTY_CODE;
FOR srl_num_to_update_rec IN srl_num_to_update_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
p_orig_chr_id => l_orig_k_id,
p_rbk_chr_id => p_rbk_chr_id) LOOP
--
-- Expire IB instance with old inventory item
--
l_instance_query_rec := l_instance_query_temp_rec;
l_instance_query_rec.instance_id := srl_num_to_update_rec.instance_id;
p_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
p_transaction_type => G_IB_BKNG_TXN_TYPE,
x_trx_rec => l_transaction_rec);
l_cim_rec.id := srl_num_to_update_rec.orig_ib_cim_id;
okl_okc_migration_pvt.update_contract_item(
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_cimv_rec =>l_cim_rec,
x_cimv_rec =>x_cim_rec);
p_inst_cle_id => srl_num_to_update_rec.orig_inst_cle_id,
p_ib_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
x_party_tbl => l_party_tbl,
x_party_account => l_party_account,
x_inv_mstr_org_id => l_inv_mstr_org_id,
x_model_line_qty => l_model_line_qty,
x_primary_uom_code => l_primary_uom_code,
x_inv_org_id => l_orig_inv_org_id);
p_inst_cle_id => srl_num_to_update_rec.orig_inst_cle_id,
p_ib_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
p_party_tbl => l_party_tbl,
p_party_account => l_party_account,
p_inv_mstr_org_id => l_inv_mstr_org_id,
p_model_line_qty => l_model_line_qty,
p_uom_code => l_primary_uom_code,
p_trx_type => 'CRB',
p_inv_org_id => l_orig_inv_org_id,
p_rbk_ib_cle_id => srl_num_to_update_rec.rbk_ib_cle_id,
x_cimv_rec => l_cimv_rec);
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
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 => x_clev_rec,
x_klev_rec => x_klev_rec
);
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
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 => x_clev_rec,
x_klev_rec => x_klev_rec
);
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_upd_instance_rec
,p_ext_attrib_values_tbl => l_upd_ext_attrib_values_tbl
,p_party_tbl => l_upd_party_tbl
,p_account_tbl => l_upd_account_tbl
,p_pricing_attrib_tbl => l_upd_pricing_attrib_tbl
,p_org_assignments_tbl => l_upd_org_assignments_tbl
,p_asset_assignment_tbl => l_upd_asset_assignment_tbl
,p_txn_rec => l_upd_txn_rec
,x_instance_id_lst => l_upd_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
FOR srl_num_to_update_rec IN srl_num_to_update_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
p_orig_chr_id => l_orig_k_id,
p_rbk_chr_id => p_rbk_chr_id) LOOP
l_srl_num_old_rec := NULL;
OPEN srl_num_old_csr(p_instance_id => srl_num_to_update_rec.instance_id);
p_rbk_ib_cle_id => srl_num_to_update_rec.rbk_ib_cle_id);
l_instance_query_rec.instance_id := srl_num_to_update_rec.instance_id;
p_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
p_transaction_type => G_IB_BKNG_TXN_TYPE,
x_trx_rec => l_upd_txn_rec);
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_upd_instance_rec
,p_ext_attrib_values_tbl => l_upd_ext_attrib_values_tbl
,p_party_tbl => l_upd_party_tbl
,p_account_tbl => l_upd_account_tbl
,p_pricing_attrib_tbl => l_upd_pricing_attrib_tbl
,p_org_assignments_tbl => l_upd_org_assignments_tbl
,p_asset_assignment_tbl => l_upd_asset_assignment_tbl
,p_txn_rec => l_upd_txn_rec
,x_instance_id_lst => l_upd_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);