[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from okc_k_lines_b cleb
where id = p_cle_id
and lse_id = 33;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT h.currency_code
FROM okc_k_headers_b h,
okc_k_lines_b l
WHERE h.id = l.dnz_chr_id
AND l.id = p_line_id;
SELECT txd.id,
txd.split_percent,
txl.kle_id,
txd.target_kle_id,
txd.quantity,
txl.current_units
FROM okl_txd_assets_b txd,
okl_txl_Assets_b txl
WHERE txl.tal_type = 'ALI'
AND txd.tal_id = txl.id
AND txl.id = p_trxline_id
--Bug# 6898798 start
ORDER BY nvl(txd.split_percent,-1);
SELECT cle.cle_id , sts.ste_code
FROM okc_k_lines_b cle , OKC_STATUSES_B sts
WHERE id = p_fa_line_id
and cle.sts_code = sts.code;
select cleb.id,
cleb.price_unit
from okc_k_lines_b cleb,
okc_line_styles_b lseb
where cleb.cle_id = p_cle_id
and cleb.dnz_chr_id = p_chr_id
and cleb.lse_id = lseb.id
and lseb.lty_code = p_lty_code;
SELECT crl.id
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl.object1_id1
AND stty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = 'LALEVL'
AND crl.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crg.dnz_chr_id = p_chrId
and cle_id = p_cle_id;
SELECT crg.cle_id,
crl.id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION4,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION10,
crl.RULE_INFORMATION11
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl.object1_id1
AND stty.stream_type_purpose = pmnt_strm_purpose
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId;
SELECT
crl2.object1_id1,
crl2.object1_id2,
crl2.rule_information2,
NVL(crl2.rule_information3,0) rule_information3,
crl2.rule_information4,
crl2.rule_information5,
crl2.rule_information6,
crl2.rule_information7,
crl2.rule_information8,
crl2.rule_information10
FROM OKC_RULES_B crl1, OKC_RULES_B crl2
WHERE crl1.id = crl2.object2_id1
AND crl1.id = p_id
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = chrId
AND crl2.dnz_chr_id = chrId
ORDER BY crl2.rule_information2 ASC;
SELECT
crl2.object1_id1,
crl2.object1_id2,
crl2.rule_information2,
NVL(crl2.rule_information3,0) rule_information3,
crl2.rule_information4,
crl2.rule_information5,
crl2.rule_information6,
crl2.rule_information7,
crl2.rule_information8,
crl2.rule_information10
FROM OKC_RULES_B crl1, OKC_RULES_B crl2
WHERE crl1.id = crl2.object2_id1
AND crl1.id = p_id
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = chrId
AND crl2.dnz_chr_id = chrId
ORDER BY crl2.rule_information2 ASC;
select CAPITALIZE_DOWN_PAYMENT_YN ,
CAPITAL_REDUCTION
from okl_k_lines
where id=p_cle_id ;
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_tbl => l_clev_price_tbl,
p_klev_tbl => l_klev_price_tbl,
x_clev_tbl => lx_clev_price_tbl,
x_klev_tbl => lx_klev_price_tbl);
okl_contract_status_pub.update_contract_status(
p_api_version => l_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_khr_status => 'PASSED',
p_chr_id => p_chr_id);
SELECT tas.id
FROM OKL_TXL_ASSETS_B txl, OKL_TRX_ASSETS tas
WHERE txl.tal_type= 'ALI'
AND txl.dnz_khr_id = p_chr_id
AND txl.tas_id = tas.id
AND tas.tas_type = 'ALI'
AND tas.tsu_code = 'ENTERED';
SELECT A.MULTI_GAAP_YN,
B.REPORTING_PDT_ID
FROM OKL_K_HEADERS A,
OKL_PRODUCTS B
WHERE A.ID = p_chr_id
AND A.PDT_ID = B.ID;
okl_contract_status_pub.update_contract_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_khr_status => 'BOOKED',
p_chr_id => p_chr_id);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
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_khr_id => p_chr_id ,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_SUBMIT_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE);
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_trxv_rec,
x_thpv_rec => lx_trxv_rec);
SELECT pdt.id product_id
,pdt.name product_name
,khr.sts_code contract_status
,khr.start_date start_date
,khr.currency_code currency_code
,khr.authoring_org_id authoring_org_id
,khr.currency_conversion_rate currency_conversion_rate
,khr.currency_conversion_type currency_conversion_type
,khr.currency_conversion_date currency_conversion_date
,khr.scs_code
FROM okl_products_v pdt
,okl_k_headers_full_v khr
WHERE khr.id = p_contract_id
AND khr.pdt_id = pdt.id;
SELECT description
FROM OKL_PRODUCTS_V
WHERE id = p_pdt_id;
select description,
lookup_code
from fnd_lookup_values
where language = 'US'
and lookup_type = lkp_type
and meaning = mng;
l_amount_tbl.delete;
Okl_Trx_Contracts_Pub.update_trx_contracts
(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_tcnv_rec => l_tcnv_rec
,p_tclv_tbl => l_tclv_tbl
,x_tcnv_rec => x_tcnv_rec
,x_tclv_tbl => x_tclv_tbl );
SELECT id,
RMR_ID,
ART1_CODE,
RELOCATE_ASSET_YN,
VOLUNTARY_YN,
COMMMERCIALLY_REAS_SALE_YN,
ORG_ID,
FLOOR_PRICE,
NEW_ITEM_PRICE,
NEW_ITEM_NUMBER,
ASSET_RELOCATED_YN,
REPURCHASE_AGMT_YN,
CURRENCY_CODE,
CURRENCY_CONVERSION_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE,
--Bug# 6336455
LEGAL_ENTITY_ID
FROM OKL_ASSET_RETURNS_b
WHERE kle_id = p_kle_id
AND ars_code = 'SCHEDULED';
select 'N' from dual
where exists (
SELECT 1
FROM OKL_ASSET_RETURNS_V
WHERE kle_id = p_kle_id
AND ars_code <> 'CANCELLED'
);
select date_terminated
from okc_k_lines_b
where id=p_kle_id;
SELECT cle.id id
FROM okc_k_lines_b cle
CONNECT BY PRIOR cle.id = cle.cle_id
START WITH cle.id = p_cle_id;
OKL_ASSET_RETURNS_PUB.insert_asset_returns(
p_api_version => p_api_version,
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_artv_rec => lp_artv_rec,
x_artv_rec => lx_artv_rec);
OKL_CONTRACT_PUB.update_contract_line(
p_api_version => l_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_ASSET_RETURNS_PUB.update_asset_returns(
p_api_version => p_api_version,
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_artv_rec => lp_upd_artv_rec,
x_artv_rec => lx_artv_rec);
SELECT o.asset_id, o.asset_number, o.corporate_book, a.cost, o.depreciation_category, a.original_cost, o.current_units,
o.dnz_chr_id ,a.book_type_code, b.book_class, a.prorate_convention_code
FROM okx_asset_lines_v o, fa_books a, fa_book_controls b
WHERE o.parent_line_id = p_kle_id
AND o.asset_id = a.asset_id
AND a.book_type_code = b.book_type_code
AND a.date_ineffective IS NULL
AND a.transaction_header_id_out IS NULL
ORDER BY book_class;
SELECT h.tsu_code, h.tas_type, h.date_trans_occurred, l.dnz_asset_id,
l.asset_number, l.kle_id ,l.DNZ_KHR_ID
FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l
WHERE h.id = l.tas_id
AND h.date_trans_occurred <= cp_trx_date
AND h.tas_type in ('AMT','AUD','AUS')
AND l.asset_number = cp_asset_number;
SELECT 'Y' date_valid,
TO_CHAR(chrb.start_date,pdateformat) con_start_date,
TO_CHAR(chrb.end_date,pdateformat) con_end_date,
chrb.sts_code sts_code
FROM okc_k_headers_b chrb
WHERE ptrxdate BETWEEN TRUNC(chrb.start_date) AND TRUNC(chrb.end_date)
AND chrb.id = pchrid;
SELECT 'Y' date_valid,
TO_CHAR(cleb.start_date,pdateformat) line_start_date,
TO_CHAR(cleb.end_date,pdateformat) line_end_date,
cleb.sts_code sts_code
FROM okc_k_lines_b cleb
WHERE ptrxdate BETWEEN TRUNC(cleb.start_date) AND TRUNC(cleb.end_date)
AND cleb.id = pcleid;
SELECT TO_CHAR(cleb.start_date,pdateformat) line_start_date,
TO_CHAR(cleb.end_date,pdateformat) line_end_date,
cleb.sts_code sts_code,
trunc(cleb.start_date) cle_start_date,
TRUNC(cleb.end_date) cle_end_date
FROM okc_k_lines_b cleb
WHERE cleb.id = pcleid;
SELECT 'Y' date_valid,
TO_CHAR(cleb.start_date,pdateformat) line_start_date,
TO_CHAR(cleb.end_date,pdateformat) line_end_date,
cleb.sts_code sts_code
FROM okc_k_lines_b cleb
WHERE cleb.id = pcleid;*/
SELECT
ID1,
ID2,
NAME,
DESCRIPTION,
ITEM_DESCRIPTION,
COMMENTS,
CHR_ID,
DNZ_CHR_ID,
LTY_CODE,
LSE_TYPE,
LSE_PARENT_ID,
PARENT_LINE_ID,
LINE_NUMBER,
DATE_TERMINATED,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
STATUS,
ASSET_ID,
QUANTITY,
UNIT_OF_MEASURE_CODE,
ASSET_NUMBER,
CORPORATE_BOOK,
LIFE_IN_MONTHS,
ORIGINAL_COST,
COST,
ADJUSTED_COST,
TAG_NUMBER,
CURRENT_UNITS,
SERIAL_NUMBER,
REVAL_CEILING,
NEW_USED,
IN_SERVICE_DATE,
MANUFACTURER_NAME,
MODEL_NUMBER,
ASSET_TYPE,
SALVAGE_VALUE,
PERCENT_SALVAGE_VALUE,
DEPRECIATION_CATEGORY,
DEPRN_START_DATE,
DEPRN_METHOD_CODE,
RATE_ADJUSTMENT_FACTOR,
BASIC_RATE,
ADJUSTED_RATE,
RECOVERABLE_COST,
ORG_ID,
SET_OF_BOOKS_ID,
PROPERTY_TYPE_CODE,
PROPERTY_1245_1250_CODE,
IN_USE_FLAG,
OWNED_LEASED,
INVENTORIAL,
LINE_STATUS
FROM OKX_ASSET_LINES_V
WHERE parent_line_id = p_cle_id;
SELECT ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
TAS_ID,
ILO_ID,
ILO_ID_OLD,
IAY_ID,
IAY_ID_NEW,
KLE_ID,
DNZ_KHR_ID,
LINE_NUMBER,
ORG_ID,
TAL_TYPE,
ASSET_NUMBER,
DESCRIPTION,
FA_LOCATION_ID,
ORIGINAL_COST,
CURRENT_UNITS,
MANUFACTURER_NAME,
YEAR_MANUFACTURED,
SUPPLIER_ID,
USED_ASSET_YN,
TAG_NUMBER,
MODEL_NUMBER,
CORPORATE_BOOK,
DATE_PURCHASED,
DATE_DELIVERY,
IN_SERVICE_DATE,
LIFE_IN_MONTHS,
DEPRECIATION_ID,
DEPRECIATION_COST,
DEPRN_METHOD,
DEPRN_RATE,
SALVAGE_VALUE,
PERCENT_SALVAGE_VALUE,
--Bug# 2981308
ASSET_KEY_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,
DEPRECIATE_YN,
HOLD_PERIOD_DAYS,
OLD_SALVAGE_VALUE,
NEW_RESIDUAL_VALUE,
OLD_RESIDUAL_VALUE,
UNITS_RETIRED,
COST_RETIRED,
SALE_PROCEEDS,
REMOVAL_COST,
DNZ_ASSET_ID,
DATE_DUE,
REP_ASSET_ID,
LKE_ASSET_ID,
MATCH_AMOUNT,
SPLIT_INTO_SINGLES_FLAG,
SPLIT_INTO_UNITS,
--Bug #2723498 : 11.5.9 - Multi currency compliance
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE
-- Multi-Currency Change
FROM Okl_Txl_Assets_V
WHERE okl_txl_assets_v.kle_id = p_kle_id
AND EXISTS (SELECT NULL
FROM okl_trx_Assets trx,
okl_trx_types_tl ttyp
WHERE trx.id = okl_txl_assets_v.tas_id
AND trx.tsu_code = 'ENTERED'
AND trx.try_id = ttyp.id
AND ttyp.name = 'Split Asset'
AND ttyp.LANGUAGE = 'US');
l_txlv_rec.LAST_UPDATED_BY,
l_txlv_rec.LAST_UPDATE_DATE,
l_txlv_rec.LAST_UPDATE_LOGIN,
l_txlv_rec.DEPRECIATE_YN,
l_txlv_rec.HOLD_PERIOD_DAYS,
l_txlv_rec.OLD_SALVAGE_VALUE,
l_txlv_rec.NEW_RESIDUAL_VALUE,
l_txlv_rec.OLD_RESIDUAL_VALUE,
l_txlv_rec.UNITS_RETIRED,
l_txlv_rec.COST_RETIRED,
l_txlv_rec.SALE_PROCEEDS,
l_txlv_rec.REMOVAL_COST,
l_txlv_rec.DNZ_ASSET_ID,
l_txlv_rec.DATE_DUE,
l_txlv_rec.REP_ASSET_ID,
l_txlv_rec.LKE_ASSET_ID,
l_txlv_rec.MATCH_AMOUNT,
l_txlv_rec.SPLIT_INTO_SINGLES_FLAG,
l_txlv_rec.SPLIT_INTO_UNITS,
-- Multi-Currency Change
l_txlv_rec.CURRENCY_CODE,
l_txlv_rec.CURRENCY_CONVERSION_TYPE,
l_txlv_rec.CURRENCY_CONVERSION_RATE,
l_txlv_rec.CURRENCY_CONVERSION_DATE
;
SELECT
ID,
OBJECT_VERSION_NUMBER,
TAL_ID,
TARGET_KLE_ID,
LINE_DETAIL_NUMBER,
ASSET_NUMBER,
DESCRIPTION,
QUANTITY,
COST,
TAX_BOOK,
LIFE_IN_MONTHS_TAX,
DEPRN_METHOD_TAX,
DEPRN_RATE_TAX,
SALVAGE_VALUE,
SPLIT_PERCENT,
INVENTORY_ITEM_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,
--Bug #2723498 : 11.5.9 - Multi currency compliance
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE
FROM Okl_Txd_Assets_v txdv
WHERE txdv.tal_id = p_tal_id
ORDER BY NVL(target_kle_id,-1)
--Bug# 3502142
, NVL(split_percent,-1);
l_txdv_rec.LAST_UPDATED_BY,
l_txdv_rec.LAST_UPDATE_DATE,
l_txdv_rec.LAST_UPDATE_LOGIN,
-- Multi-Currency Change
l_txdv_rec.CURRENCY_CODE,
l_txdv_rec.CURRENCY_CONVERSION_TYPE,
l_txdv_rec.CURRENCY_CONVERSION_RATE,
l_txdv_rec.CURRENCY_CONVERSION_DATE;
SELECT 'Y'
FROM okx_assets_v okx
WHERE okx.asset_number = p_asset_number;
SELECT 'Y'
FROM okl_k_lines_full_v kle,
okc_line_styles_b lse
WHERE kle.name = p_asset_number
AND kle.lse_id = lse.id
--and lse.lty_code = 'FIXED_ASSET';
SELECT 'Y'
FROM okl_txd_assets_b txd
WHERE NVL(txd.asset_number,'-999999999999999') = p_asset_number
AND EXISTS (SELECT NULL
FROM okl_trx_Assets trx,
okl_trx_types_tl ttyp,
okl_txl_assets_b txl
WHERE trx.id = txl.tas_id
AND trx.try_id = ttyp.id
AND ttyp.name = 'Split Asset'
AND ttyp.LANGUAGE = 'US'
AND txl.id = txd.tal_id);
SELECT 'Y'
FROM okx_assets_v okx
WHERE okx.asset_number = p_asset_number;
SELECT 'Y'
FROM okl_k_lines_full_v kle,
okc_line_styles_b lse
WHERE kle.name = p_asset_number
AND kle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET';
SELECT 'Y'
FROM okl_txd_assets_b txd,
okl_txl_assets_b txl,
okl_trx_types_tl ttyp,
okl_trx_assets trx
WHERE NVL(txd.asset_number,'-999999999999999') = p_asset_number
AND txd.tal_id = txl.id
AND txl.tas_id = trx.id
AND trx.try_id = ttyp.id
AND ttyp.name = 'Split Asset'
AND ttyp.LANGUAGE = 'US'
AND NVL(txd.target_kle_id,-99) <> txl.kle_id
AND trx.tsu_code = 'ENTERED'
AND txd.id <> p_txdv_id;
SELECT 'Y'
FROM okl_txl_assets_b txl
WHERE txl.asset_number = p_asset_number
AND txl.tal_type IN ('ALI','CRB'); --only transactions apart from split which create a new line
SELECT id
FROM OKL_TRX_TYPES_TL
WHERE UPPER(name) = UPPER(p_try_name)
AND LANGUAGE = 'US';
SELECT 'Y'
FROM OKC_K_LINES_B cle
WHERE cle.id = p_cle_id
AND EXISTS (SELECT '1'
FROM OKC_LINE_STYLES_B lse
WHERE lse.id = cle.lse_id
AND lse.lty_code = G_FIN_AST_LTY_CODE
AND lse.lse_type = G_TOP_LINE_STYLE)
AND EXISTS (SELECT '1'
FROM OKC_SUBCLASS_TOP_LINE stl,
OKC_K_HEADERS_B CHR
WHERE stl.lse_id = cle.lse_id
AND stl.scs_code = CHR.scs_code
AND CHR.id = cle.chr_id);
SELECT location_id
FROM okx_ast_dst_hst_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND status = 'A'
AND NVL(start_date_active,SYSDATE) <= SYSDATE
AND NVL(end_date_active,SYSDATE+1) > SYSDATE
AND transaction_header_id_out IS NULL
AND retirement_id IS NULL
AND ROWNUM < 2;
PROCEDURE Update_trx_header(
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_trxv_rec IN trxv_rec_type,
x_trxv_rec OUT NOCOPY trxv_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRX_HEADER';
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 => p_trxv_rec,
x_thpv_rec => x_trxv_rec);
END Update_trx_header;
SELECT 'OKL'||TO_CHAR(okl_fan_seq.NEXTVAL)
INTO l_txdv_rec.asset_number FROM dual;
SELECT mtl.serial_number_control_code,
mtl.inventory_item_id,
mtl.organization_id
FROM mtl_system_items mtl,
okc_k_items model_cim,
okc_k_lines_b model_cle,
okc_line_styles_b model_lse
WHERE model_cim.object1_id2 = TO_CHAR(mtl.organization_id)
AND model_cim.object1_id1 = mtl.inventory_item_id
AND model_cim.jtot_object1_code = 'OKX_SYSITEM'
AND model_cim.cle_id = model_cle.id
AND model_cim.dnz_chr_id = model_cle.dnz_chr_id
AND model_cle.cle_id = fin_ast_id
AND model_cle.lse_id = model_lse.id
AND model_lse.lty_code = 'ITEM';
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 serial_number
FROM csi_item_instances
WHERE serial_number = Srl_Number;
SELECT TO_CHAR(OKL_IBN_SEQ.NEXTVAL)
FROM dual;
SELECT mp.master_organization_id,
chrb.inv_organization_id,
txdb.inventory_item_id
FROM mtl_parameters mp,
okc_k_headers_b chrb,
okc_k_lines_b cleb,
okl_txl_Assets_b txlb,
okl_txd_assets_b txdb
WHERE mp.organization_id = chrb.inv_organization_id
AND chrb.id = cleb.dnz_chr_id
AND cleb.id = txlb.kle_id
AND txlb.id = p_tal_id
AND txdb.id = p_asd_id;
SELECT txlb.kle_id fa_cle_id,
ib_cle.id ib_cle_id,
txlb.tas_id,
csi.location_id, --hz_locations
csi.install_location_id,
--Bug# 3569441
csi.install_location_type_code, --hz_party_sites, hz_locations
csi.instance_number
FROM csi_item_instances csi,
csi_instance_statuses csi_inst_sts,
okc_k_items ib_cim,
okc_k_lines_b ib_cle,
okc_line_styles_b ib_lse,
okc_k_lines_b inst_cle,
okc_line_styles_b inst_lse,
okc_statuses_b inst_sts,
okc_k_lines_b fa_cle,
okc_line_styles_b fa_lse,
okl_txl_assets_b txlb
WHERE csi.instance_id = TO_NUMBER(ib_cim.object1_id1)
AND csi_inst_sts.instance_status_id = csi.instance_status_id
AND NVL(csi_inst_sts.terminated_flag,'N') = 'N'
AND ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = ib_cle.dnz_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.dnz_chr_id = inst_cle.dnz_chr_id
AND ib_cle.lse_id = ib_lse.id
AND ib_lse.lty_code = 'INST_ITEM'
AND inst_cle.cle_id = fa_cle.cle_id
AND inst_cle.dnz_chr_id = fa_cle.dnz_chr_id
AND inst_cle.lse_id = inst_lse.id
AND inst_lse.lty_code = 'FREE_FORM2'
AND inst_sts.code = inst_cle.sts_code
AND INST_STS.STE_CODE NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
AND fa_cle.id = txlb.kle_id
AND fa_cle.lse_id = fa_lse.id
AND fa_lse.lty_code = 'FIXED_ASSET'
AND txlb.id = p_tal_id;
SELECT TO_CHAR(Party_site_use_id) party_site_use_id
FROM hz_party_site_uses
WHERE party_site_id = pty_site_id
AND site_use_type = 'INSTALL_AT';
SELECT TO_CHAR(psu.party_site_use_id) party_site_use_id
FROM hz_party_site_uses psu,
hz_party_sites ps
WHERE psu.party_site_id = ps.party_site_id
AND psu.site_use_type = 'INSTALL_AT'
AND ps.location_id = loc_id;
SELECT SUBSTR(arp_addr_label_pkg.format_address(NULL,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,NULL,hl.country,NULL, NULL,NULL,NULL,NULL,NULL,NULL,'n','n',80,1,1),1,80)
FROM hz_locations hl,
hz_party_sites ps
WHERE hl.location_id = ps.location_id
AND ps.party_site_id = pty_site_id;
SELECT SUBSTR(arp_addr_label_pkg.format_address(NULL,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,NULL,hl.country,NULL, NULL,NULL,NULL,NULL,NULL,NULL,'n','n',80,1,1),1,80)
FROM hz_locations hl
WHERE hl.location_id = loc_id;
select tal_id
from okl_txd_assets_b
where id = p_asd_id;
l_itiv_rec.selected_for_split_flag := 'Y';
SELECT name
FROM okc_k_lines_tl
WHERE id = p_fin_ast_id;
SELECT
COUNT(1)
FROM csi_item_instances csi,
okc_k_items ib_cim,
okc_k_lines_b ib_cle,
okc_line_styles_b ib_lse,
okc_k_lines_b inst_cle,
okc_line_styles_b inst_lse,
okc_statuses_b inst_sts
WHERE csi.instance_id = TO_NUMBER(ib_cim.object1_id1)
AND ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = ib_cle.dnz_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.dnz_chr_id = inst_cle.dnz_chr_id
AND ib_cle.lse_id = ib_lse.id
AND ib_lse.lty_code = 'INST_ITEM'
AND inst_cle.cle_id = fin_ast_id
AND inst_cle.lse_id = inst_lse.id
AND inst_lse.lty_code = 'FREE_FORM2'
AND inst_sts.code = inst_cle.sts_code
--Bug# 5946411: ER
--AND INST_STS.STE_CODE NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
SELECT txl.current_units
FROM okl_txl_assets_b txl
WHERE id = p_tal_id;
SELECT ib_cle.id ib_cle_id,
csi.instance_id,
csi.serial_number,
csi.instance_number,
csi.inv_organization_id,
csi.inventory_item_id,
csi.inv_master_organization_id,
csi.unit_of_measure,
csi.quantity,
csi.instance_status_id,
csi.location_id, --hz_locations
csi.install_location_id,
--BUG# 3569441
csi.install_location_type_code --hz_party_sites OR hz_loactions
FROM csi_item_instances csi,
okc_k_items ib_cim,
okc_k_lines_b ib_cle,
okc_line_styles_b ib_lse,
okc_k_lines_b inst_cle,
okc_line_styles_b inst_lse,
okc_statuses_b inst_sts
WHERE csi.instance_id = TO_NUMBER(ib_cim.object1_id1)
AND ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = ib_cle.dnz_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.dnz_chr_id = inst_cle.dnz_chr_id
AND ib_cle.lse_id = ib_lse.id
AND ib_lse.lty_code = 'INST_ITEM'
AND inst_cle.cle_id = fin_ast_id
AND inst_cle.lse_id = inst_lse.id
AND inst_lse.lty_code = 'FREE_FORM2'
AND inst_sts.code = inst_cle.sts_code
--Bug# 5946411: ER
--AND INST_STS.STE_CODE NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
SELECT ib_cle.id ib_cle_id,
csi.instance_id,
csi.serial_number,
csi.instance_number,
csi.inv_organization_id,
csi.inventory_item_id,
csi.inv_master_organization_id,
csi.unit_of_measure,
csi.quantity,
csi.instance_status_id,
csi.location_id, --hz_locations
csi.install_location_id,
--BUG# 3569441
csi.install_location_type_code --hz_party_sites OR hz_locations
FROM csi_item_instances csi,
okc_k_items ib_cim,
okc_k_lines_b ib_cle,
okc_line_styles_b ib_lse,
okc_k_lines_b inst_cle,
okc_line_styles_b inst_lse,
okc_statuses_b inst_sts
WHERE csi.instance_id = TO_NUMBER(ib_cim.object1_id1)
AND ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = ib_cle.dnz_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.dnz_chr_id = inst_cle.dnz_chr_id
AND ib_cle.lse_id = ib_lse.id
AND ib_cle.id = p_ib_cle_id
AND ib_lse.lty_code = 'INST_ITEM'
AND inst_cle.cle_id = fin_ast_id
AND inst_cle.lse_id = inst_lse.id
AND inst_lse.lty_code = 'FREE_FORM2'
AND inst_sts.code = inst_cle.sts_code
--Bug# 5946411: ER
--AND INST_STS.STE_CODE NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
SELECT TO_CHAR(Party_site_use_id) party_site_use_id
FROM hz_party_site_uses
WHERE party_site_id = pty_site_id
AND site_use_type = 'INSTALL_AT';
SELECT TO_CHAR(psu.party_site_use_id) party_site_use_id
FROM hz_party_site_uses psu,
hz_party_sites ps
WHERE psu.party_site_id = ps.party_site_id
AND psu.site_use_type = 'INSTALL_AT'
AND ps.location_id = loc_id;
SELECT SUBSTR(arp_addr_label_pkg.format_address(NULL,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,NULL,hl.country,NULL, NULL,NULL,NULL,NULL,NULL,NULL,'n','n',80,1,1),1,80)
FROM hz_locations hl,
hz_party_sites ps
WHERE hl.location_id = ps.location_id
AND ps.party_site_id = pty_site_id;
SELECT SUBSTR(arp_addr_label_pkg.format_address(NULL,hl.address1,hl.address2,hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,NULL,hl.country,NULL, NULL,NULL,NULL,NULL,NULL,NULL,'n','n',80,1,1),1,80)
FROM hz_locations hl
WHERE hl.location_id = loc_id;
SELECT name
FROM okc_k_lines_tl
WHERE id = p_fin_ast_id;
l_iipv_tbl(i).selected_for_split_flag := 'Y';
l_iipv_tbl(i).selected_for_split_flag := 'N';
l_iipv_tbl.DELETE;
SELECT khr.deal_type
FROM okl_k_headers khr,
okc_k_headers_b chrb,
okc_k_lines_b cleb
WHERE khr.id = chrb.id
AND chrb.id = cleb.dnz_chr_id
AND cleb.id = PCleId;
SELECT txl.id, --tal_id
txl.SPLIT_INTO_SINGLES_FLAG, --p_split_into_individuals_yn
txl.SPLIT_INTO_UNITS, --p_split_into_units
--Bug# 3156924
tas.DATE_TRANS_OCCURRED,
tas.ID
FROM OKL_TXL_ASSETS_B txl,
OKL_TRX_ASSETS tas
WHERE txl.kle_id = p_cle_id
AND txl.tas_id = tas.id
AND txl.tal_type = 'ALI'
AND tas.tas_type = 'ALI'
AND tas.tsu_code = 'ENTERED'
AND EXISTS (SELECT NULL
FROM
okl_trx_types_tl ttyp
WHERE tas.try_id = ttyp.id
AND ttyp.name = 'Split Asset'
AND ttyp.LANGUAGE = 'US');
SELECT '!'
FROM dual
WHERE EXISTS
(SELECT 1
FROM okl_txl_itm_insts
WHERE tal_id = p_tal_id
AND tal_type = 'ALI');
SELECT id
FROM okl_txl_itm_insts
WHERE tal_id = p_tal_id
AND tal_type = 'ALI';
SELECT khrv.currency_code,
khrv.currency_conversion_type,
khrv.currency_conversion_rate,
khrv.currency_conversion_date,
khrv.start_date
FROM okl_k_headers_full_v khrv,
okc_k_lines_b cle
WHERE khrv.id = cle.dnz_chr_id
AND cle.id = PCleId;
SELECT
cle_fa.id kle_id,
clet.name NAME,
clet.item_description DESCRIPTION,
cle.chr_id CHR_ID,
cle.dnz_chr_id DNZ_CHR_ID,
cle.id PARENT_LINE_ID,
cle.start_date START_DATE_ACTIVE,
cle.end_date END_DATE_ACTIVE,
cim_fa.number_of_items CURRENT_UNITS,
clet.name ASSET_NUMBER,
kle.OEC ORIGINAL_COST,
kle.OEC COST,
cle.sts_code LINE_STATUS
FROM okc_k_items cim_fa,
okc_k_lines_b cle_fa,
okc_line_styles_b lse_fa,
okl_k_lines kle,
okc_k_lines_tl clet,
okc_k_lines_b cle
WHERE cim_fa.cle_id = cle_fa.id
AND cle_fa.cle_id = cle.id
AND cle_fa.dnz_chr_id = cle.dnz_chr_id
AND cle_fa.lse_id = lse_fa.id
AND lse_fa.lty_code = 'FIXED_ASSET'
AND kle.id = cle.id
AND clet.id = cle.id
AND clet.LANGUAGE = USERENV('LANG')
AND cle.id = PCleId;
SELECT '!'
FROM dual
WHERE EXISTS
(
SELECT '1'
FROM
okc_k_headers_b oks_chrb,
okc_line_styles_b oks_cov_pd_lse,
okc_k_lines_b oks_cov_pd_cleb,
okc_k_rel_objs krel,
okc_line_styles_b lnk_srv_lse,
okc_statuses_b lnk_srv_sts,
okc_k_lines_b lnk_srv_cleb,
okc_k_items lnk_srv_cim
WHERE oks_chrb.scs_code = 'SERVICE'
AND oks_chrb.id = oks_cov_pd_cleb.dnz_chr_id
AND oks_cov_pd_cleb.lse_id = oks_cov_pd_lse.id
AND oks_cov_pd_lse.lty_code = 'COVER_PROD'
AND '#' = krel.object1_id2
AND oks_cov_pd_cleb.id = krel.object1_id1
AND krel.rty_code = 'OKLSRV'
AND krel.chr_id = lnk_srv_cleb.dnz_chr_id
AND krel.cle_id = lnk_srv_cleb.id
AND lnk_srv_cleb.lse_id = lnk_srv_lse.id
AND lnk_srv_lse.lty_code = 'LINK_SERV_ASSET'
AND lnk_srv_cleb.sts_code = lnk_srv_sts.code
AND lnk_srv_sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
AND lnk_srv_cleb.dnz_chr_id = lnk_srv_cim.dnz_chr_id
AND lnk_srv_cleb.id = lnk_srv_cim.cle_id
AND lnk_srv_cim.jtot_object1_code = 'OKX_COVASST'
AND lnk_srv_cim.object1_id2 = '#'
AND lnk_srv_cim.object1_id1 = TO_CHAR(p_cle_id)
);
SELECT fab.asset_key_ccid
FROM fa_additions_b fab
WHERE fab.asset_id = p_asset_id;
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
SELECT '!'
FROM OKC_RULES_B rul
WHERE rul.dnz_chr_id = p_chr_id
AND rul.rule_information_category = 'LARLES'
AND NVL(rule_information1,'N') = 'Y';
SELECT
QVE.DESCRIPTION
FROM
OKL_PDT_QUALITYS PQY
, OKL_PQY_VALUES QVE
WHERE
QVE.PQY_ID = PQY.ID
AND PQY.NAME = cp_name
AND QVE.VALUE = cp_value;
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_ctr_id1;
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_trxv_rec,
x_thpv_rec => x_trxv_rec);
OKL_TXL_ASSETS_PUB.update_txl_asset_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_tlpv_rec => l_txlv_rec,
x_tlpv_rec => x_txlv_rec);
OKL_TXD_ASSETS_PUB.delete_txd_asset_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_adpv_tbl => l_txdv_tbl);
okl_txl_itm_insts_pub.delete_txl_itm_insts(
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_iipv_tbl => l_iipv_tbl);
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
PROCEDURE Update_Split_Transaction(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_cle_id IN NUMBER,
p_txdv_tbl IN txdv_tbl_type,
x_txdv_tbl OUT NOCOPY txdv_tbl_type) IS
l_return_status VARCHAR2(1) DEFAULT OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SPLIT_TRX';
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.cle_id = p_cle_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET'
--Bug# 2761799 : Do not check for active status on sysdate
--AND trunc(nvl(start_date,sysdate)) <= trunc(sysdate)
--AND trunc(nvl(end_date,sysdate+1)) > trunc(sysdate)
--Bug# 5946411: ER :commented following
-- AND cle.sts_code = 'BOOKED';
OKL_TXD_ASSETS_PUB.update_txd_asset_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_adpv_rec => l_txdv_rec,
x_adpv_rec => lx_txdv_rec);
END Update_Split_Transaction;
SELECT
ID,
OBJECT_VERSION_NUMBER,
CLE_ID,
CHR_ID,
CLE_ID_FOR,
DNZ_CHR_ID,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
UOM_CODE,
EXCEPTION_YN,
NUMBER_OF_ITEMS,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
PRICED_ITEM_YN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okc_K_Items_V
WHERE okc_k_items_v.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,
KLE_ID,
STY_ID,
PRC_CODE,
FCG_CODE,
NTY_CODE,
ESTIMATED_OEC,
LAO_AMOUNT,
TITLE_DATE,
FEE_CHARGE,
LRS_PERCENT,
INITIAL_DIRECT_COST,
PERCENT_STAKE,
PERCENT,
EVERGREEN_PERCENT,
AMOUNT_STAKE,
OCCUPANCY,
COVERAGE,
RESIDUAL_PERCENTAGE,
DATE_LAST_INSPECTION,
DATE_SOLD,
LRV_AMOUNT,
CAPITAL_REDUCTION,
DATE_NEXT_INSPECTION_DUE,
DATE_RESIDUAL_LAST_REVIEW,
DATE_LAST_REAMORTISATION,
VENDOR_ADVANCE_PAID,
WEIGHTED_AVERAGE_LIFE,
TRADEIN_AMOUNT,
BOND_EQUIVALENT_YIELD,
TERMINATION_PURCHASE_AMOUNT,
REFINANCE_AMOUNT,
YEAR_BUILT,
DELIVERED_DATE,
CREDIT_TENANT_YN,
DATE_LAST_CLEANUP,
YEAR_OF_MANUFACTURE,
COVERAGE_RATIO,
REMARKETED_AMOUNT,
GROSS_SQUARE_FOOTAGE,
PRESCRIBED_ASSET_YN,
DATE_REMARKETED,
NET_RENTABLE,
REMARKET_MARGIN,
DATE_LETTER_ACCEPTANCE,
REPURCHASED_AMOUNT,
DATE_COMMITMENT_EXPIRATION,
DATE_REPURCHASED,
DATE_APPRAISAL,
RESIDUAL_VALUE,
APPRAISAL_VALUE,
SECURED_DEAL_YN,
GAIN_LOSS,
FLOOR_AMOUNT,
RE_LEASE_YN,
PREVIOUS_CONTRACT,
TRACKED_RESIDUAL,
DATE_TITLE_RECEIVED,
AMOUNT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
STY_ID_FOR,
CLG_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DATE_FUNDING,
DATE_FUNDING_REQUIRED,
DATE_ACCEPTED,
DATE_DELIVERY_EXPECTED,
OEC,
CAPITAL_AMOUNT,
RESIDUAL_GRNTY_AMOUNT,
RESIDUAL_CODE,
RVI_PREMIUM,
CREDIT_NATURE,
CAPITALIZED_INTEREST,
CAPITAL_REDUCTION_PERCENT,
DATE_PAY_INVESTOR_START,
PAY_INVESTOR_FREQUENCY,
PAY_INVESTOR_EVENT,
PAY_INVESTOR_REMITTANCE_DAYS,
--Bug# 2998115
FEE_TYPE,
--Bug# 3143522 : Subsidies
SUBSIDY_ID,
--SUBSIDIZED_OEC,
--SUBSIDIZED_CAP_AMOUNT,
PRE_TAX_YIELD,
AFTER_TAX_YIELD,
IMPLICIT_INTEREST_RATE,
IMPLICIT_NON_IDC_INTEREST_RATE,
PRE_TAX_IRR,
AFTER_TAX_IRR,
SUBSIDY_OVERRIDE_AMOUNT,
--quote
SUB_PRE_TAX_YIELD,
SUB_AFTER_TAX_YIELD,
SUB_IMPL_INTEREST_RATE,
SUB_IMPL_NON_IDC_INT_RATE,
SUB_PRE_TAX_IRR,
SUB_AFTER_TAX_IRR,
--Bug# 2994971
ITEM_INSURANCE_CATEGORY,
--Bug# 3973640 : 11.5.10+ schema changes
QTE_ID,
FUNDING_DATE,
STREAM_TYPE_SUBCLASS
-- Bug#4508050 - smadhava - Added - Start
, FEE_PURPOSE_CODE
, DATE_FUNDING_EXPECTED
, DATE_DELIVERY_EXPECTED
, MANUFACTURER_NAME
, MODEL_NUMBER
, DOWN_PAYMENT_RECEIVER_CODE
, CAPITALIZE_DOWN_PAYMENT_YN
-- Bug#4508050 - smadhava - Added - End
--Bug# 4631549
,Expected_asset_cost
FROM OKL_K_LINES_V
WHERE OKL_K_LINES_V.id = p_id;
l_klev_rec.LAST_UPDATED_BY,
l_klev_rec.LAST_UPDATE_DATE,
l_klev_rec.LAST_UPDATE_LOGIN,
l_klev_rec.DATE_FUNDING,
l_klev_rec.DATE_FUNDING_REQUIRED,
l_klev_rec.DATE_ACCEPTED,
l_klev_rec.DATE_DELIVERY_EXPECTED,
l_klev_rec.OEC,
l_klev_rec.CAPITAL_AMOUNT,
l_klev_rec.RESIDUAL_GRNTY_AMOUNT,
l_klev_rec.RESIDUAL_CODE,
l_klev_rec.RVI_PREMIUM,
l_klev_rec.CREDIT_NATURE,
l_klev_rec.CAPITALIZED_INTEREST,
l_klev_rec.CAPITAL_REDUCTION_PERCENT,
l_klev_rec.DATE_PAY_INVESTOR_START,
l_klev_rec.PAY_INVESTOR_FREQUENCY,
l_klev_rec.PAY_INVESTOR_EVENT,
l_klev_rec.PAY_INVESTOR_REMITTANCE_DAYS,
--Bug# 2998115:
l_klev_rec.FEE_TYPE,
--Bug#3143522 : Subsidies
l_klev_rec.SUBSIDY_ID,
--l_klev_rec.SUBSIDIZED_OEC,
--l_klev_rec.SUBSIDIZED_CAP_AMOUNT,
l_klev_rec.PRE_TAX_YIELD,
l_klev_rec.AFTER_TAX_YIELD,
l_klev_rec.IMPLICIT_INTEREST_RATE,
l_klev_rec.IMPLICIT_NON_IDC_INTEREST_RATE,
l_klev_rec.PRE_TAX_IRR,
l_klev_rec.AFTER_TAX_IRR,
l_klev_rec.SUBSIDY_OVERRIDE_AMOUNT,
--quote
l_klev_rec.SUB_PRE_TAX_YIELD,
l_klev_rec.SUB_AFTER_TAX_YIELD,
l_klev_rec.SUB_IMPL_INTEREST_RATE,
l_klev_rec.SUB_IMPL_NON_IDC_INT_RATE,
l_klev_rec.SUB_PRE_TAX_IRR,
l_klev_rec.SUB_AFTER_TAX_IRR,
--Bug# 2994971 :
l_klev_rec.ITEM_INSURANCE_CATEGORY,
--Bug# 3973640 : 11.5.10+ schema changes
l_klev_rec.QTE_ID,
l_klev_rec.FUNDING_DATE,
l_klev_rec.STREAM_TYPE_SUBCLASS
-- Bug#4508050 - smadhava - Added - Start
, l_klev_rec.FEE_PURPOSE_CODE
, l_klev_rec.DATE_FUNDING_EXPECTED
, l_klev_rec.DATE_DELIVERY_EXPECTED
, l_klev_rec.MANUFACTURER_NAME
, l_klev_rec.MODEL_NUMBER
, l_klev_rec.DOWN_PAYMENT_RECEIVER_CODE
, l_klev_rec.CAPITALIZE_DOWN_PAYMENT_YN
-- Bug#4508050 - smadhava - Added - End
--Bug# 4631549
,l_klev_rec.Expected_Asset_Cost
;
SELECT
ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID,
CLE_ID,
LSE_ID,
LINE_NUMBER,
STS_CODE,
DISPLAY_SEQUENCE,
TRN_CODE,
DNZ_CHR_ID,
COMMENTS,
ITEM_DESCRIPTION,
OKE_BOE_DESCRIPTION,
HIDDEN_IND,
PRICE_UNIT,
PRICE_UNIT_PERCENT,
PRICE_NEGOTIATED,
PRICE_NEGOTIATED_RENEWED,
PRICE_LEVEL_IND,
INVOICE_LINE_LEVEL_IND,
DPAS_RATING,
BLOCK23TEXT,
EXCEPTION_YN,
TEMPLATE_USED,
DATE_TERMINATED,
NAME,
START_DATE,
END_DATE,
DATE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
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,
PRICE_TYPE,
CURRENCY_CODE,
CURRENCY_CODE_RENEWED,
LAST_UPDATE_LOGIN
FROM Okc_K_Lines_V
WHERE okc_k_lines_v.id = p_id;
l_clev_rec.LAST_UPDATED_BY,
l_clev_rec.LAST_UPDATE_DATE,
l_clev_rec.PRICE_TYPE,
l_clev_rec.CURRENCY_CODE,
l_clev_rec.CURRENCY_CODE_RENEWED,
l_clev_rec.LAST_UPDATE_LOGIN;
SELECT txd.split_percent,
txl.kle_id,
txd.target_kle_id,
cle.cle_id cle_id,
target_cle.cle_id target_cle_id,
txd.quantity,
txl.current_units
FROM okl_txd_assets_b txd,
okl_txl_Assets_b txl,
okc_k_lines_b cle,
okc_k_lines_b target_cle
WHERE txl.tal_type = 'ALI'
AND txd.tal_id = txl.id
AND txl.id = p_trxline_id
AND txl.kle_id <> txd.target_kle_id
AND cle.id = txl.kle_id
AND target_cle.id = txd.target_kle_id
--Bug# 3502142
ORDER BY NVL(txd.split_percent,-1);
SELECT txd.split_percent,
txl.kle_id,
txd.target_kle_id,
cle.cle_id cle_id,
target_cle.cle_id target_cle_id,
txd.quantity,
txl.current_units
FROM okl_txd_assets_b txd,
okl_txl_Assets_b txl,
okc_k_lines_b cle,
okc_k_lines_b target_cle
WHERE txl.tal_type = 'ALI'
AND txd.tal_id = txl.id
AND txl.id = p_trxline_id
AND txl.kle_id = txd.target_kle_id
AND cle.id = txl.kle_id
AND target_cle.id = txd.target_kle_id;
SELECT lnk_cle.id,
lnk_target_cle.id,
--Bug 3502142
lnk_target_cle.cle_id
FROM okc_k_lines_b lnk_cle,
okc_k_items lnk_cim,
okc_line_styles_b lnk_lse,
okc_k_lines_b lnk_target_cle,
okc_k_items lnk_target_cim,
okc_line_styles_b lnk_target_lse
WHERE lnk_cim.object1_id1 = TO_CHAR(p_cle_id)
AND lnk_cim.object1_id2 = '#'
AND lnk_cim.jtot_object1_code = 'OKX_COVASST'
AND lnk_cle.id = lnk_cim.cle_id
AND lnk_cle.dnz_chr_id = lnk_cim.dnz_chr_id
AND lnk_cle.lse_id = lnk_lse.id
AND lnk_lse.lty_code IN ('LINK_FEE_ASSET','LINK_SERV_ASSET','LINK_USAGE_ASSET')
AND lnk_target_cim.object1_id1 = TO_CHAR(p_target_cle_id)
AND lnk_target_cim.object1_id2 = '#'
AND lnk_target_cim.jtot_object1_code = 'OKX_COVASST'
AND lnk_target_cle.id = lnk_target_cim.cle_id
AND lnk_target_cle.dnz_chr_id = lnk_target_cim.dnz_chr_id
AND lnk_target_cle.lse_id = lnk_target_lse.id
AND lnk_target_lse.lty_code IN ('LINK_FEE_ASSET','LINK_SERV_ASSET','LINK_USAGE_ASSET')
AND lnk_cle.cle_id = lnk_target_cle.cle_id
AND lnk_cle.id <> lnk_target_cle.id
AND lnk_cle.dnz_chr_id = lnk_target_cle.dnz_chr_id;
SELECT lnk_cle.id,
lnk_target_cle.id,
--Bug 3502142
lnk_target_cle.cle_id
FROM okc_k_lines_b lnk_cle,
okc_k_items lnk_cim,
okc_line_styles_b lnk_lse,
okc_k_lines_b lnk_target_cle,
okc_k_items lnk_target_cim,
okc_line_styles_b lnk_target_lse
WHERE lnk_cim.object1_id1 = TO_CHAR(p_cle_id)
AND lnk_cim.object1_id2 = '#'
AND lnk_cim.jtot_object1_code = 'OKX_COVASST'
AND lnk_cle.id = lnk_cim.cle_id
AND lnk_cle.dnz_chr_id = lnk_cim.dnz_chr_id
AND lnk_cle.lse_id = lnk_lse.id
AND lnk_lse.lty_code IN ('LINK_FEE_ASSET','LINK_SERV_ASSET','LINK_USAGE_ASSET')
AND lnk_target_cim.object1_id1 = TO_CHAR(p_target_cle_id)
AND lnk_target_cim.object1_id2 = '#'
AND lnk_target_cim.jtot_object1_code = 'OKX_COVASST'
AND lnk_target_cle.id = lnk_target_cim.cle_id
AND lnk_target_cle.dnz_chr_id = lnk_target_cim.dnz_chr_id
AND lnk_target_cle.lse_id = lnk_target_lse.id
AND lnk_target_lse.lty_code IN ('LINK_FEE_ASSET','LINK_SERV_ASSET','LINK_USAGE_ASSET')
AND lnk_cle.cle_id = lnk_target_cle.cle_id
AND lnk_cle.id = lnk_target_cle.id
AND lnk_cle.dnz_chr_id = lnk_target_cle.dnz_chr_id;
SELECT str.Id,
str.transaction_number,
str.sgn_code SGN_CODE,
str.khr_id,
str.sty_id,
str.say_code,
str.active_yn,
str.kle_id,
--Bug# 3502142
str.purpose_code,
str.comments,
str.date_current,
-- Bug# 4775555
sty.stream_type_purpose,
--Bug# 6344223
str.link_hist_stream_id
FROM okl_streams str,
okl_strm_type_b sty
WHERE
str.kle_id = kleId
AND str.say_code = status
AND str.sty_id = sty.id;
SELECT ele.id,
ele.DATE_BILLED,
ele.STREAM_ELEMENT_DATE,
ele.AMOUNT,
ele.ACCRUED_YN,
ele.comments,
str.transaction_number,
str.sgn_code SGN_CODE,
ele.stm_id STM_ID,
ele.se_line_number SE_LINE_NUMBER
FROM okl_strm_elements ele,
okl_streams str
WHERE ele.stm_id = str.id
AND str.id = strId
AND str.kle_id = kleId
AND str.sty_id = styId
AND UPPER(str.say_code) = 'CURR'
AND UPPER(str.active_yn) = activeYn
AND NVL(str.purpose_code,'ORIG') = purposeCode
ORDER BY 3;
SELECT okl_sif_seq.NEXTVAL
FROM dual;
SELECT currency_code
FROM okc_k_lines_b
WHERE id = p_kle_id;
SELECT txd.id,
txd.split_percent,
txl.kle_id,
txd.target_kle_id,
cle.cle_id cle_id,
target_cle.cle_id target_cle_id,
txd.quantity,
txl.current_units
FROM okl_txd_assets_b txd,
okl_txl_Assets_b txl,
okc_k_lines_b cle,
okc_k_lines_b target_cle
WHERE txl.tal_type = 'ALI'
AND txd.tal_id = txl.id
AND txl.id = p_trxline_id
AND txl.kle_id <> txd.target_kle_id
AND cle.id = txl.kle_id
AND target_cle.id = txd.target_kle_id;
Okl_Streams_Pub.update_streams(
p_api_version => l_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_stmv_rec => l_stmv_rec_hist
,x_stmv_rec => x_stmv_rec);
Okl_Streams_Pub.update_streams(
p_api_version => l_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_stmv_rec => l_stmv_rec_hist
,p_selv_tbl => l_selv_tbl_hist
,x_stmv_rec => x_stmv_rec
,x_selv_tbl => x_selv_tbl);
l_selv_tbl.DELETE(1, l_selv_tbl.LAST);
l_selv_tbl_hist.DELETE(1, l_selv_tbl.LAST);
SELECT msi.asset_category_id
FROM mtl_system_items msi
WHERE msi.organization_id = p_inv_org_id
AND msi.inventory_item_id = p_inv_item_id;
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
);
SELECT LEVEL,
id,
chr_id,
cle_id,
dnz_chr_id,
lse_id
FROM okc_k_lines_b
CONNECT BY PRIOR id = cle_id
START WITH id = p_cle_id;
SELECT lty_code
FROM okc_line_styles_b
WHERE id = p_lse_id;
SELECT rul.id sll_id,
rul.jtot_object1_code,
rul.object1_id1,
rul.object1_id2,
rul.jtot_object2_code,
rul.object2_id2,
--nvl(rul.Rule_information6,'0') amount_sll,
rul.Rule_information6 amount_sll,
rul.object2_id1 slh_id,
rul.rule_information1,
rul.rule_information2,
rul.rule_information3,
rul.rule_information4,
rul.rule_information5,
rul.rule_information7,
rul.rule_information8 amount_stub,
rul.rule_information9,
rul.rule_information10,
rul.rule_information11,
rul.rule_information12,
rul.rule_information13,
rul.rule_information14,
rul.rule_information15,
rul_slh.jtot_object1_code strm_type_source,
rul_slh.object1_id1 strm_type_id1,
rul_slh.object1_id2 strm_type_id2,
cleb.currency_code
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okc_rules_b rul_slh,
okc_k_lines_b cleb
--Bug# : 3124577 - 11.5.10 : Rule Migration
WHERE rul.rule_information_category = 'LASLL'
--where rul.rule_information_category = 'SLL'
AND rul.rgp_id = rgp.id
AND rgp.rgd_code = 'LALEVL'
and rgp.cle_id = cleb.id
and cleb.id = p_cle_id
AND rul_slh.id = rul.object2_id1
AND rul_slh.rgp_id = rgp.id
AND rul_slh.rule_information_category = 'LASLH'
ORDER BY rul_slh.object1_id1;
SELECT rul.id slh_id,
rul.jtot_object1_code strm_type_source,
rul.object1_id1 strm_type_id1,
rul.object1_id2 strm_type_id2
FROM okc_rules_b rul
WHERE rul.id = p_rul_id
--Bug# - 3124577: 11.5.10 :---Rule Migration
AND rul.rule_information_category = 'LASLH';
SELECT cle.cle_id srv_fee_line_id,
cle.id lnk_line_id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse,
OKC_STATUSES_B sts,
OKC_K_ITEMS cim,
OKC_K_LINES_B fin_asst_line
WHERE cle.lse_id = lse.id
AND lse.lty_code IN ('LINK_FEE_ASSET','LINK_SERV_ASSET','LINK_USAGE_ASSET')
AND cle.dnz_chr_id = fin_asst_line.dnz_chr_id
AND sts.code = cle.sts_code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
AND cim.cle_id = cle.id
AND cim.dnz_chr_id = cle.dnz_chr_id
AND cim.object1_id1 = TO_CHAR(p_cle_id)
AND cim.object1_id2 = '#'
AND cim.jtot_object1_code = 'OKX_COVASST'
AND cim.dnz_chr_id = fin_asst_line.dnz_chr_id
AND fin_asst_line.id = p_cle_id;
L_UPDATED_SLL_AMOUNT NUMBER;
l_updated_stub_amount NUMBER;
SELECT
kle_sub.subsidy_id subsidy_id
,cleb_sub.id subsidy_cle_id
,clet_sub.name name
,clet_sub.item_description description
,kle_sub.amount amount
,kle_sub.subsidy_override_amount subsidy_override_amount
,cleb_sub.dnz_chr_id dnz_chr_id
,cleb_asst.id asset_cle_id
,cplb.id cpl_id
,pov.vendor_id vendor_id
,pov.vendor_name vendor_name
,clet_asst.name asset_number
,cleb_sub.orig_system_id1 parent_sub_cle_id
FROM
po_vendors pov,
okc_k_party_roles_b cplb,
okl_k_lines kle_sub,
okc_k_lines_tl clet_sub,
okc_k_lines_b cleb_sub,
okc_line_styles_b lseb_sub,
okc_k_lines_tl clet_asst,
okc_k_lines_b cleb_asst
WHERE
pov.vendor_id = cplb.object1_id1
AND cplb.object1_id2 = '#'
AND cplb.jtot_object1_code = 'OKX_VENDOR'
AND cplb.dnz_chr_id = cleb_sub.dnz_chr_id
AND cplb.cle_id = cleb_sub.id
AND cplb.chr_id IS NULL
AND cplb.rle_code = 'OKL_VENDOR'
AND kle_sub.id = cleb_sub.id
AND clet_sub.id = cleb_sub.id
AND clet_sub.LANGUAGE = USERENV('LANG')
AND cleb_sub.cle_id = cleb_asst.id
AND cleb_sub.dnz_chr_id = cleb_asst.dnz_chr_id
AND cleb_sub.sts_code <> 'ABANDONED'
AND lseb_sub.id = cleb_sub.lse_id
AND lseb_sub.lty_code = 'SUBSIDY'
AND clet_asst.id = cleb_asst.id
AND clet_asst.LANGUAGE =USERENV('LANG')
AND cleb_asst.id = p_asset_cle_id;
SELECT
sid.ID
,sid.OBJECT_VERSION_NUMBER
,sid.CLE_ID
,sid.FA_CLE_ID
,sid.INVOICE_NUMBER
,sid.DATE_INVOICED
,sid.DATE_DUE
,sid.SHIPPING_ADDRESS_ID1
,sid.SHIPPING_ADDRESS_ID2
,sid.SHIPPING_ADDRESS_CODE
,sid.ATTRIBUTE_CATEGORY
,sid.ATTRIBUTE1
,sid.ATTRIBUTE2
,sid.ATTRIBUTE3
,sid.ATTRIBUTE4
,sid.ATTRIBUTE5
,sid.ATTRIBUTE6
,sid.ATTRIBUTE7
,sid.ATTRIBUTE8
,sid.ATTRIBUTE9
,sid.ATTRIBUTE10
,sid.ATTRIBUTE11
,sid.ATTRIBUTE12
,sid.ATTRIBUTE13
,sid.ATTRIBUTE14
,sid.ATTRIBUTE15
,sid.CREATED_BY
,sid.CREATION_DATE
,sid.LAST_UPDATED_BY
,sid.LAST_UPDATE_DATE
,sid.LAST_UPDATE_LOGIN
,cleb_fa.id fixed_asset_cle_id
FROM okl_supp_invoice_dtls sid,
okc_k_lines_b cleb_fa,
okc_line_styles_b lseb_fa,
okc_k_lines_b cleb
WHERE sid.cle_id = cleb.orig_system_id1
AND cleb_fa.cle_id = cleb.cle_id
AND cleb_fa.dnz_chr_id = cleb.dnz_chr_id
AND lseb_fa.id = cleb_fa.lse_id
AND lseb_fa.lty_code = 'FIXED_ASSET'
AND cleb.id = p_cle_id;
SELECT id,
quantity,
split_percent
FROM okl_txd_assets_b txd
WHERE txd.tal_id = p_tal_id
AND NVL(txd.target_kle_id,-1) <> p_cle_id;
SELECT cle.id
FROM OKC_K_LINES_B cle
WHERE cle.dnz_chr_id = p_chr_id
AND cle.orig_system_id1 = p_cle_id;
SELECT cle.id
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.cle_id = p_cle_id
AND cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET';
SELECT cleb.sts_code sts_code
FROM okc_k_lines_b cleb
WHERE cleb.id = pcleid;
OKL_TXD_ASSETS_PUB.update_txd_asset_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_adpv_rec => l_txdv_rec,
x_adpv_rec => l_txdv_rec_out);
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 => lupd_cimv_rec,
x_cimv_rec => l_cimv_rec_out);
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 => l_clev_rec_out,
x_klev_rec => l_klev_rec_out);
l_updated_sll_amount := NULL;
l_updated_stub_amount := NULL;
l_updated_sll_amount := TO_NUMBER(l_sll_rec.amount_sll)*l_split_factor;
p_amount => l_updated_sll_amount,
p_currency_code => l_sll_rec.currency_code,
p_round_option => 'STM',
x_rounded_amount => l_rounded_amount
);
l_updated_sll_amount := l_rounded_amount;
l_updated_stub_amount := TO_NUMBER(l_sll_rec.amount_stub)*l_split_factor;
p_amount => l_updated_stub_amount,
p_currency_code => l_sll_rec.currency_code,
p_round_option => 'STM',
x_rounded_amount => l_rounded_amount
);
l_updated_stub_amount := l_rounded_amount;
IF NVL(l_updated_sll_amount,OKL_API.G_MISS_NUM) <> OKL_API.G_MISS_NUM THEN
l_rulv_rec.rule_information6 := l_updated_sll_amount;
ELSIF NVL(l_updated_sll_amount,OKL_API.G_MISS_NUM) = OKL_API.G_MISS_NUM THEN
l_rulv_rec.rule_information6 := NULL;
IF NVL(l_updated_stub_amount,OKL_API.G_MISS_NUM) <> OKL_API.G_MISS_NUM THEN
l_rulv_rec.rule_information8 := l_updated_stub_amount;
ELSIF NVL(l_updated_stub_amount,OKL_API.G_MISS_NUM) = OKL_API.G_MISS_NUM THEN
l_rulv_rec.rule_information8 := NULL;
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 => l_rulv_rec,
x_rulv_rec => l_rulv_rec_out);
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 => l_rulv_rec,
x_rulv_rec => l_rulv_rec_out);
l_lnk_clev_rec.LAST_UPDATED_BY := OKL_API.G_MISS_NUM;
l_lnk_clev_rec.LAST_UPDATE_DATE := OKL_API.G_MISS_DATE;
l_lnk_clev_rec.LAST_UPDATE_LOGIN := OKL_API.G_MISS_NUM;
l_klev_rec.LAST_UPDATED_BY := OKL_API.G_MISS_NUM;
l_klev_rec.LAST_UPDATE_DATE := OKL_API.G_MISS_DATE;
l_klev_rec.LAST_UPDATE_LOGIN := OKL_API.G_MISS_NUM;
l_lnk_cimv_rec.LAST_UPDATED_BY := OKL_API.G_MISS_NUM;
l_lnk_cimv_rec.LAST_UPDATE_DATE := OKL_API.G_MISS_DATE;
l_lnk_cimv_rec.LAST_UPDATE_LOGIN := OKL_API.G_MISS_NUM;
l_updated_sll_amount := NULL;
l_updated_stub_amount := NULL;
l_updated_sll_amount := TO_NUMBER(l_sll_rec.amount_sll)*l_split_factor;
p_amount => l_updated_sll_amount,
p_currency_code => l_sll_rec.currency_code,
p_round_option => 'STM',
x_rounded_amount => l_rounded_amount
);
l_updated_sll_amount := l_rounded_amount;
l_sll_rulv_rec.rule_information6 := l_updated_sll_amount;
l_updated_stub_amount := TO_NUMBER(l_sll_rec.amount_stub)*l_split_factor;
p_amount => l_updated_stub_amount,
p_currency_code => l_sll_rec.currency_code,
p_round_option => 'STM',
x_rounded_amount => l_rounded_amount
);
l_updated_stub_amount := l_rounded_amount;
l_sll_rulv_rec.rule_information8 := l_updated_stub_amount;
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_lnk_clev_old_rec,
p_klev_rec => l_lnk_klev_old_rec,
x_clev_rec => lx_lnk_clev_old_rec,
x_klev_rec => lx_lnk_klev_old_rec);
l_lnk_clev_rec.LAST_UPDATED_BY := OKL_API.G_MISS_NUM;
l_lnk_clev_rec.LAST_UPDATE_DATE := OKL_API.G_MISS_DATE;
l_lnk_clev_rec.LAST_UPDATE_LOGIN := OKL_API.G_MISS_NUM;
l_klev_rec.LAST_UPDATED_BY := OKL_API.G_MISS_NUM;
l_klev_rec.LAST_UPDATE_DATE := OKL_API.G_MISS_DATE;
l_klev_rec.LAST_UPDATE_LOGIN := OKL_API.G_MISS_NUM;
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_lnk_cimv_old_rec,
x_cimv_rec => lx_lnk_cimv_old_rec);
l_lnk_cimv_rec.LAST_UPDATED_BY := OKL_API.G_MISS_NUM;
l_lnk_cimv_rec.LAST_UPDATE_DATE := OKL_API.G_MISS_DATE;
l_lnk_cimv_rec.LAST_UPDATE_LOGIN := OKL_API.G_MISS_NUM;
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 => l_rulv_rec,
x_rulv_rec => l_rulv_rec_out);
l_updated_sll_amount := TO_NUMBER(l_sll_rec.amount_sll) - l_split_pymt_sum;
p_amount => l_updated_sll_amount,
p_currency_code => l_sll_rec.currency_code,
p_round_option => 'STM',
x_rounded_amount => l_rounded_amount
);
l_updated_sll_amount := l_rounded_amount;
l_sll_rulv_rec.rule_information6 := l_updated_sll_amount;
l_updated_stub_amount := TO_NUMBER(l_sll_rec.amount_stub) - l_split_pymt_sum;
p_amount => l_updated_stub_amount,
p_currency_code => l_sll_rec.currency_code,
p_round_option => 'STM',
x_rounded_amount => l_rounded_amount
);
l_updated_stub_amount := l_rounded_amount;
l_sll_rulv_rec.rule_information8 := l_updated_stub_amount;
SELECT LEVEL,
id,
chr_id,
cle_id,
dnz_chr_id,
lse_id
FROM okc_k_lines_b
CONNECT BY PRIOR id = cle_id
START WITH id = p_cle_id;
SELECT lnk_cleb.id lnk_cle_id
FROM okc_k_lines_b lnk_cleb,
okc_line_styles_b lnk_lseb,
okc_statuses_b lnk_stsb,
okc_k_items lnk_cim
WHERE lnk_cleb.id = lnk_cim.cle_id
AND lnk_cleb.dnz_chr_id = lnk_cim.dnz_chr_id
AND lnk_cleb.lse_id = lnk_lseb.id
AND lnk_lseb.lty_code IN
('LINK_FEE_ASSET','LINK_SERV_ASSET','LINK_USAGE_ASSET')
AND lnk_cleb.sts_code = lnk_stsb.code
AND lnk_stsb.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
AND lnk_cim.object1_id1 = TO_CHAR(p_cle_id)
AND lnk_cim.object1_id2 = '#'
AND lnk_cim.jtot_object1_code = 'OKX_COVASST';
okl_okc_migration_pvt.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,
x_clev_rec => lx_clev_rec);
okl_okc_migration_pvt.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_lnk_clev_rec,
x_clev_rec => lx_lnk_clev_rec);
SELECT book_class
FROM okx_asst_bk_controls_v
WHERE book_type_code = p_book_type_code;
select tas.date_trans_occurred
FROM OKL_TRX_ASSETS tas,
OKL_TXL_ASSETS_B tal,
OKL_TXD_ASSETS_B txl
WHERE tas.id = tal.tas_id
AND tal.id = txl.tal_id
AND txl.tax_book = p_tax_book
AND tas.tsu_code = 'PROCESSED'
AND tas.tas_type in ('AMT')
And tal.kle_id = p_asset_id;
SELECT
tas.date_trans_occurred
FROM OKL_TRX_ASSETS tas,
OKL_TXL_ASSETS_B tal,
OKL_TXD_ASSETS_B txd
WHERE tas.id = tal.tas_id
AND tal.corporate_book = P_corp_book
AND tas.tsu_code = 'PROCESSED'
AND tas.tas_type in ('AMT')
And tal.kle_id = p_asset_id
AND tal.id = txd.tal_id(+)
AND TAX_BOOK IS NULL ;
select tas.date_trans_occurred
FROM OKL_TRX_ASSETS tas,
OKL_TXL_ASSETS_B tal,
OKL_TXD_ASSETS_B txl
WHERE tas.id = tal.tas_id
AND tal.id = txl.tal_id
AND txl.tax_book = p_tax_book
AND tas.tsu_code = 'PROCESSED'
AND tas.tas_type in ('AUS')
And tal.kle_id = p_asset_id;
SELECT
tas.date_trans_occurred
FROM OKL_TRX_ASSETS tas,
OKL_TXL_ASSETS_B tal,
OKL_TXD_ASSETS_B txd
WHERE tas.id = tal.tas_id
AND tal.corporate_book = P_corp_book
AND tas.tsu_code = 'PROCESSED'
AND tas.tas_type in ('AUS')
And tal.kle_id = p_asset_id
AND tal.id = txd.tal_id(+)
AND TAX_BOOK IS NULL ;
SELECT asset_key_ccid
FROM fa_additions
WHERE asset_id = p_asset_id;
SELECT ytd_deprn - (ytd_deprn -(ytd_deprn*p_split_factor)),
deprn_reserve - (deprn_reserve -(deprn_reserve*p_split_factor)),
prior_fy_expense - (prior_fy_expense-(prior_fy_expense*p_split_factor)),
bonus_ytd_deprn - (bonus_ytd_deprn-(bonus_ytd_deprn*p_split_factor)),
bonus_deprn_reserve - (bonus_deprn_reserve-(bonus_deprn_reserve*p_split_factor))
FROM okx_ast_dprtns_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND deprn_run_date = (SELECT MAX(deprn_run_date)
FROM okx_ast_dprtns_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code);
SELECT --p_txdv_rec.quantity,
--p_ast_line_rec.current_units,
p_units,
assigned_to,
code_combination_id,
location_id,
p_units
--p_txdv_rec.quantity
--p_ast_line_rec.current_units
FROM okx_ast_dst_hst_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND transaction_header_id_out IS NULL
AND retirement_id IS NULL;
SELECT ALLOWED_DEPRN_LIMIT,
ALLOWED_DEPRN_LIMIT_AMOUNT
,DEPRN_LIMIT_TYPE
,DEPRECIATE_FLAG
--Bug# 6152614
,PRORATE_CONVENTION_CODE
,PRORATE_DATE
FROM FA_books
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND transaction_header_id_out IS NULL;
SELECT DECODE(bkc.book_class,'CORPORATE',NULL,'TAX',ast.asset_id)
FROM okx_assets_v ast,
okx_asst_bk_controls_v bkc
WHERE ast.asset_number = p_asset_number
AND ast.corporate_book = bkc.mass_copy_source_book
AND bkc.book_type_code = p_book_type_code;
SELECT cleb.sts_code sts_code,
cleb.dnz_chr_id chr_id,
khr.PDT_ID,
chr.START_DATE
FROM okc_k_lines_b cleb,
okl_k_headers khr,
OKC_K_HEADERS_B chr
WHERE cleb.id = pcleid
and khr.id = cleb.dnz_chr_id
and chr.id = khr.id;
SELECT book_class
FROM okx_asst_bk_controls_v
WHERE book_type_code = p_book_type_code;
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
SELECT units_assigned,
distribution_id
FROM OKX_AST_DST_HST_V
WHERE asset_id = p_ast_line_rec.asset_id
AND book_type_code = p_ast_line_rec.corporate_book
AND transaction_header_id_out IS NULL
AND retirement_id IS NULL;
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
l_trans_rec.who_info.last_update_login := FND_GLOBAL.LOGIN_ID;
l_asset_dist_tbl.DELETE;
SELECT units_assigned,
distribution_id
FROM OKX_AST_DST_HST_V
WHERE asset_id = p_ast_line_rec.asset_id
AND book_type_code = p_ast_line_rec.corporate_book
AND transaction_header_id_out IS NULL
AND retirement_id IS NULL;
SELECT book_class
FROM okx_asst_bk_controls_v
WHERE book_type_code = p_book_type_code;
SELECT retirement_prorate_convention
FROM fa_category_book_defaults
WHERE book_type_code = p_book_type_code
AND category_id = p_category_id
AND p_dpis BETWEEN start_dpis AND NVL(end_dpis,p_dpis);
fnd_profile.get('LOGIN_ID', l_trans_rec.who_info.last_update_login);
fnd_profile.get('USER_ID', l_trans_rec.who_info.last_updated_by);
IF (l_trans_rec.who_info.last_updated_by IS NULL) THEN
l_trans_rec.who_info.last_updated_by := -1;
IF (l_trans_rec.who_info.last_update_login IS NULL) THEN
l_trans_rec.who_info.last_update_login := -1;
l_trans_rec.who_info.last_update_date := SYSDATE;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by;
SELECT units_assigned,
distribution_id
FROM OKX_AST_DST_HST_V
WHERE asset_id = p_ast_line_rec.asset_id
AND book_type_code = p_ast_line_rec.corporate_book
AND transaction_header_id_out IS NULL
AND retirement_id IS NULL;
SELECT book_class
FROM okx_asst_bk_controls_v
WHERE book_type_code = p_book_type_code;
SELECT -- -1 * (ytd_deprn -(ytd_deprn*p_split_factor)),
-- -1 * (deprn_reserve -(deprn_reserve*p_split_factor)),
-1 * (prior_fy_expense-(prior_fy_expense*p_split_factor)),
-1 * (bonus_ytd_deprn-(bonus_ytd_deprn*p_split_factor)),
-1 * (bonus_deprn_reserve-(bonus_deprn_reserve*p_split_factor))
FROM okx_ast_dprtns_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND deprn_run_date = (SELECT MAX(deprn_run_date)
FROM okx_ast_dprtns_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code);
SELECT -- -1 * ytd_deprn,
-- -1 * deprn_reserve,
-1 * prior_fy_expense,
-1 * bonus_ytd_deprn,
-1 * bonus_deprn_reserve
FROM okx_ast_dprtns_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND deprn_run_date = (SELECT MAX(deprn_run_date)
FROM okx_ast_dprtns_v
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code);
SELECT cleb.sts_code sts_code,cleb.dnz_chr_id chr_id
FROM okc_k_lines_b cleb
WHERE cleb.id = pcleid;
SELECT cleb.sts_code sts_code,
cleb.dnz_chr_id chr_id,
khr.PDT_ID,
chr.START_DATE
FROM okc_k_lines_b cleb,
okl_k_headers khr,
OKC_K_HEADERS_B chr
WHERE cleb.id = pcleid
and khr.id = cleb.dnz_chr_id
and chr.id = khr.id;
select max(th.amortization_start_date) amortization_start_date
from fa_transaction_headers th,
fa_books inbk,
fa_books outbk
where inbk.asset_id = p_asset_id
and inbk.book_type_code = p_book_type_code
and outbk.asset_id(+) = p_asset_id
and outbk.book_type_code(+) = p_book_type_code
and inbk.transaction_header_id_in = th.transaction_header_id
and outbk.transaction_header_id_out(+) = th.transaction_header_id
and th.asset_id = p_asset_id
and th.book_type_code = p_book_type_code
and th.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN',
'TRANSFER', 'TRANSFER IN/VOID',
'RECLASS', 'UNIT ADJUSTMENT',
'REINSTATEMENT');
l_trans_rec_ua.who_info.last_updated_by := FND_GLOBAL.USER_ID;
l_trans_rec_ua.who_info.last_update_login := FND_GLOBAL.LOGIN_ID;
l_asset_dist_tbl.DELETE;
SELECT transaction_type_id
FROM CSI_TXN_TYPES
WHERE source_transaction_type = p_transaction_type;
PROCEDURE delete_instance_lines(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_inst_cle_id IN NUMBER,
p_ib_cle_id IN NUMBER) IS
l_return_status VARCHAR2(1) DEFAULT OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INSTANCE_LINES';
OKC_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_ib_clev_rec,
x_clev_rec => lx_ib_clev_rec);
OKC_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_line_id => l_ib_cle_id);
OKL_KLE_PVT.delete_row(
p_api_version => l_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_klev_rec => l_ib_klev_rec);
OKC_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_inst_clev_rec,
x_clev_rec => lx_inst_clev_rec);
OKC_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_line_id => l_inst_cle_id);
OKL_KLE_PVT.delete_row(
p_api_version => l_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_klev_rec => l_ib_klev_rec);
END delete_instance_lines;
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 instance_status_id
FROM CSI_INSTANCE_STATUSES
WHERE NVL(TERMINATED_FLAG,'N') = 'Y'
AND name = p_status_name;
SELECT '!'
FROM OKL_TXL_ITM_INSTS iti
WHERE iti.instance_id = PInstanceId
AND iti.tal_id = PTalId
AND NVL(iti.selected_for_split_flag,'N') = 'Y'
AND iti.tal_type = 'ALI';
SELECT inst_cle.id inst_cle_id,
ib_cle.id ib_cle_id
FROM
okc_k_items ib_cim,
okc_k_lines_b ib_cle,
okc_line_styles_b ib_lse,
okc_k_lines_b inst_cle,
okc_line_styles_b inst_lse,
okc_k_lines_b fa_cle,
okc_line_styles_b fa_lse,
okl_txl_Assets_b tal
WHERE ib_cim.object1_id1 = TO_CHAR(PInstanceId)
AND ib_cim.object1_id2 = '#'
AND ib_cim.jtot_object1_code = 'OKX_IB_ITEM'
AND ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = ib_cle.dnz_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.dnz_chr_id = inst_cle.dnz_chr_id
AND ib_cle.lse_id = ib_lse.id
AND ib_lse.lty_code = 'INST_ITEM'
AND inst_cle.cle_id = fa_cle.cle_id
AND inst_cle.dnz_chr_id = fa_cle.dnz_chr_id
AND inst_cle.lse_id = inst_lse.id
AND inst_lse.lty_code = 'FREE_FORM2'
AND fa_cle.id = tal.kle_id
AND fa_cle.dnz_chr_id = PChrId
AND fa_cle.lse_id = fa_lse.id
AND fa_lse.lty_code = 'FIXED_ASSET'
AND tal.id = PTalId;
SELECT inst_cle.id inst_cle_id,
ib_cim.id ib_cim_id
FROM
okc_k_items ib_cim,
okc_k_lines_b ib_cle,
okc_line_styles_b ib_lse,
okc_k_lines_b inst_cle,
okc_line_styles_b inst_lse,
okc_k_lines_b fa_cle,
okc_line_styles_b fa_lse
WHERE
ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = ib_cle.dnz_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.dnz_chr_id = inst_cle.dnz_chr_id
AND ib_cle.lse_id = ib_lse.id
AND ib_lse.lty_code = 'INST_ITEM'
AND inst_cle.cle_id = fa_cle.cle_id
AND inst_cle.dnz_chr_id = fa_cle.dnz_chr_id
AND inst_cle.lse_id = inst_lse.id
AND inst_lse.lty_code = 'FREE_FORM1'
AND fa_cle.id = PTarget_kle_id
AND fa_cle.dnz_chr_id = PChrId
AND fa_cle.lse_id = fa_lse.id
AND fa_lse.lty_code = 'FIXED_ASSET';
SELECT serial_number,
id
FROM okl_txl_itm_insts
WHERE asd_id = p_asd_id
AND NVL(selected_for_split_flag,'N') = 'Y';
l_iipv_rec.selected_for_split_flag := 'P';
okl_txl_itm_insts_pub.update_txl_itm_insts
(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_iipv_rec => l_iipv_rec,
x_iipv_rec => lx_iipv_rec);
NULL; -- no need to update quantity - item instance may be serailized
delete_instance_lines(
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_inst_cle_id => l_inst_cle_id,
p_ib_cle_id => l_ib_cle_id);
END IF; -- If item instance is selected for serial split
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
);
SELECT cim_model.object1_id1,
cim_model.object1_id2,
cle_fa.dnz_chr_id,
cle_fa.cle_id
FROM okc_k_items cim_model,
okc_k_lines_b cle_model,
okc_line_styles_b lse_model,
okc_k_lines_b cle_fa
WHERE cim_model.dnz_chr_id = cle_model.dnz_chr_id
AND cim_model.cle_id = cle_model.id
AND cle_model.lse_id = lse_model.id
AND lse_model.lty_code = 'ITEM'
AND cle_model.cle_id = cle_fa.cle_id
AND cle_model.dnz_chr_id = cle_fa.dnz_chr_id
AND cle_fa.id = p_fa_line_id;
SELECT cle_ib.id,
cle_inst.id
FROM okc_k_lines_b cle_ib,
okc_line_styles_b lse_ib,
okc_k_lines_b cle_inst,
okc_line_styles_b lse_inst
WHERE cle_ib.lse_id = lse_ib.id
AND lse_ib.lty_code = 'INST_ITEM'
AND cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
AND cle_ib.cle_id = cle_inst.id
AND cle_inst.lse_id = lse_inst.id
AND lse_inst.lty_code = 'FREE_FORM2'
AND cle_inst.cle_id = p_fin_ast_id;
SELECT cle_ib.id,
cle_inst.id
FROM 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 cle_fa,
okl_txd_assets_b txdb
WHERE cle_ib.lse_id = lse_ib.id
AND lse_ib.lty_code = 'INST_ITEM'
AND cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
AND cle_ib.cle_id = cle_inst.id
AND cle_inst.lse_id = lse_inst.id
AND lse_inst.lty_code = 'FREE_FORM2'
AND cle_inst.cle_id = cle_fa.cle_id
AND cle_fa.id = txdb.target_kle_id
AND txdb.id = p_txd_id;
SELECT cle_fa.cle_id
FROM okc_k_lines_b cle_fa,
okl_txd_assets_b txdb
WHERE cle_fa.id = txdb.target_kle_id
AND txdb.id = p_txd_id;
SELECT id
FROM okc_k_lines_b cle_new_ib
WHERE cle_new_ib.cle_id = p_new_inst_id;
SELECT cleb.sts_code sts_code
FROM okc_k_lines_b cleb
WHERE cleb.id = pcleid;
delete_instance_lines(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_inst_cle_id => l_tgt_inst_cle_id,
p_ib_cle_id => l_tgt_ib_cle_id);
OKL_OKC_MIGRATION_PVT.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_restricted_update => OKC_API.G_FALSE,
p_clev_tbl => l_clev_tbl,
x_clev_tbl => lx_clev_tbl);
SELECT cleb.cle_id
FROM okc_k_lines_b cleb
WHERE cleb.id = p_cle_id;
SELECT txdb.target_kle_id
FROM okl_txd_assets_b txdb
WHERE txdb.tal_id = p_tal_id;
SELECT cleb.id
FROM okc_k_lines_b cleb,
okc_line_styles_b lseb
WHERE lseb.id = cleb.lse_id
AND lseb.lty_code = 'FREE_FORM2'
AND cleb.sts_code <> 'ABANDONED'
AND cleb.cle_id = p_cle_id;
OKL_OKC_MIGRATION_PVT.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,
x_clev_rec => lx_clev_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
);
SELECT cle_id,
dnz_chr_id,
--Bug# 6373605 start
sts_code
--Bug# 6373605 end
FROM okc_k_lines_b
WHERE id = p_fa_line_id;
SELECT id,
cle_id
FROM okc_k_items cim
WHERE EXISTS
(SELECT '1'
FROM okl_txd_assets_b txd
WHERE txd.target_kle_id = cim.cle_id
AND txd.id = p_id
);
SELECT cim.object1_id1,
cim.object1_id2,
cim.id,
cim.cle_id,
cim.dnz_chr_id
FROM OKC_K_ITEMS cim,
OKC_K_LINES_B inst_item,
OKC_LINE_STYLES_B inst_item_lse,
OKC_K_LINES_B f_frm2,
OKC_LINE_STYLES_B f_frm2_lse,
OKC_K_LINES_B fa
WHERE cim.cle_id = inst_item.id
AND cim.dnz_chr_id = inst_item.dnz_chr_id
AND inst_item.cle_id = f_frm2.id
AND inst_item.lse_id = inst_item_lse.id
AND inst_item_lse.lty_code = 'INST_ITEM'
AND f_frm2.cle_id = fa.cle_id
AND f_frm2.lse_id = f_frm2_lse.id
AND f_frm2_lse.lty_code = 'FREE_FORM2'
AND fa.id = p_fa_line_id;
SELECT 'Y' -- 'Y' if the current period of the asset is period of addition.
FROM dual
WHERE NOT EXISTS
(SELECT 'x'
FROM fa_deprn_summary
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND deprn_amount <> 0
AND deprn_source_code = 'DEPRN'
);
SELECT
id1,
id2,
name,
description,
book_type_code,
book_class,
asset_id,
asset_number,
serial_number,
salvage_value,
percent_salvage_value,
life_in_months,
acquisition_date,
original_cost,
cost,
adjusted_cost,
tag_number,
current_units,
reval_ceiling,
new_used,
manufacturer_name,
model_number,
asset_type,
depreciation_category,
deprn_start_date,
deprn_method_code,
rate_adjustment_factor,
basic_rate,
adjusted_rate,
start_date_active,
end_date_active,
status,
primary_uom_code,
recoverable_cost,
org_id,
set_of_books_id
FROM OKX_AST_BKS_V
WHERE TRUNC(NVL(start_date_active,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(end_date_active,SYSDATE+1)) >TRUNC(SYSDATE)
AND book_class IN ('CORPORATE','TAX')
AND asset_id = p_asset_id
ORDER BY book_class,asset_id;
SELECT f_frm2.id instance_id,
inst_item.id ib_line_id
FROM OKC_K_ITEMS cim,
OKC_K_LINES_B inst_item,
OKC_LINE_STYLES_B inst_item_lse,
OKC_K_LINES_B f_frm2,
OKC_LINE_STYLES_B f_frm2_lse,
OKC_K_LINES_B fa,
OKL_TXD_ASSETS_B asd
WHERE cim.cle_id = inst_item.id
AND cim.dnz_chr_id = inst_item.dnz_chr_id
AND inst_item.cle_id = f_frm2.id
AND inst_item.lse_id = inst_item_lse.id
AND inst_item_lse.lty_code = 'INST_ITEM'
AND f_frm2.cle_id = fa.cle_id
AND f_frm2.lse_id = f_frm2_lse.id
AND f_frm2_lse.lty_code = 'FREE_FORM2'
AND fa.id = asd.target_kle_id
AND asd.id = p_asd_id
AND EXISTS (SELECT NULL
FROM OKC_K_ITEMS cim_p,
OKC_K_LINES_B inst_item_p,
OKC_LINE_STYLES_B inst_item_lse_p,
OKC_K_LINES_B f_frm2_p,
OKC_LINE_STYLES_B f_frm2_lse_p,
OKC_K_LINES_B fa_p,
OKL_TXL_ASSETS_B tal
WHERE cim_p.object1_id1 = cim.object1_id1
AND cim_p.object1_id2 = cim.object1_id2
AND cim_p.jtot_object1_code = cim.jtot_object1_code
AND cim_p.dnz_chr_id = cim.dnz_chr_id
AND cim_p.cle_id = inst_item_p.id
AND cim_p.dnz_chr_id = inst_item_p.dnz_chr_id
AND inst_item_p.cle_id = f_frm2_p.id
AND inst_item_p.lse_id = inst_item_lse_p.id
AND inst_item_lse_p.lty_code = 'INST_ITEM'
AND f_frm2_p.cle_id = fa_p.cle_id
AND f_frm2_p.lse_id = f_frm2_lse_p.id
AND f_frm2_lse_p.lty_code = 'FREE_FORM2'
AND fa_p.id = tal.kle_id
AND tal.id = asd.tal_id
);
SELECT '!'
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM okl_trx_quotes_b h,
okl_txl_quote_lines_b l,
okl_trx_contracts t,
okl_k_headers k
WHERE h.id = l.qte_id
AND h.id = t.qte_id
AND h.khr_id = k.id
AND (( k.deal_type LIKE 'LEASE%' AND h.qtp_code IN
('TER_PURCHASE','TER_RECOURSE','TER_ROLL_PURCHASE','TER_MAN_PURCHASE'))
OR
(k.deal_type LIKE 'LOAN%' )
)
AND l.qlt_code = 'AMCFIA'
AND l.quote_quantity < l.asset_quantity
AND l.kle_id = pcleid
AND t.tcn_type = 'ALT'
--rkuttiya added for 12.1.1 Multi GAAP
AND t.representation_type = 'PRIMARY'
--
--Bug# 6043327 : R12B SLA impact
--AND t.tsu_code = 'WORKING'
AND t.tmt_status_code = 'WORKING'
);
SELECT DATE_TRANS_OCCURRED,
TRANS_NUMBER,
--Bug# 6373605 start
TRY_ID
--Bug# 6373605 end
FROM OKL_TRX_ASSETS
WHERE id = ptrxid;
select chrb.orig_system_source_code
from okc_k_headers_b chrb
where chrb.id = p_chr_id;
select kle.expected_asset_cost
from okl_k_lines kle
where kle.id = p_cle_id;
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_exp_cost_rec,
p_klev_rec => l_klev_exp_cost_rec,
x_clev_rec => lx_clev_exp_cost_rec,
x_klev_rec => lx_klev_exp_cost_rec
);
okl_tal_pvt.update_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_talv_rec => l_talv_date_rec,
x_talv_rec => lx_talv_date_rec);
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);
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);
delete_instance_lines(
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_inst_cle_id => l_dup_inst_cle_id,
p_ib_cle_id => l_dup_ib_cle_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_trxv_rec,
x_thpv_rec => lx_trxv_rec);
SELECT cle.id,
cle.dnz_chr_id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.cle_id = p_cle_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET'
--Bug Fix# 2744213 - 2761799
-- should not check for effectivity on sysdate
-- AND trunc(nvl(start_date,sysdate)) <= trunc(sysdate)
-- AND trunc(nvl(end_date,sysdate+1)) > trunc(sysdate)
--Bug# 5946411: ER
--commented the status as it should consider all kind of contract
-- AND cle.sts_code = 'BOOKED';
SELECT COUNT(iti.serial_number)
FROM okl_txl_itm_insts iti
WHERE iti.tal_id = PTalid
AND tal_type = 'ALI'
AND NVL(selected_for_split_flag,'N') = 'Y';
SELECT COUNT(iti.serial_number)
FROM okl_txl_itm_insts iti
WHERE iti.tal_id = PTalId
AND iti.asd_id = PTxdId
AND tal_type = 'ALI'
AND NVL(selected_for_split_flag,'N') = 'Y';
SELECT name
FROM okc_k_lines_tl
WHERE id = p_fin_ast_id;
SELECT mtl.serial_number_control_code
FROM mtl_system_items mtl,
okc_k_headers_b CHR,
okc_k_lines_b cle
WHERE mtl.inventory_item_id = PInvItmId
AND mtl.organization_id = CHR.INV_ORGANIZATION_ID
AND CHR.id = cle.chr_id
AND cle.id = P_fin_ast_id;
SELECT st.ste_code
FROM OKC_K_HEADERS_V chr,
okc_statuses_b st
WHERE chr.id = p_chr_id
and st.code = chr.sts_code;
SELECT cleb.dnz_chr_id,
tasb.date_trans_occurred
FROM okl_trx_assets tasb
,okl_txl_assets_b txlb
,okc_k_lines_b cleb
,okc_line_styles_b lseb
WHERE txlb.tas_id = tasb.id
AND cleb.id = txlb.kle_id
AND cleb.cle_id = p_cle_id
AND cleb.lse_id = lseb.id
AND lseb.lty_Code = 'FIXED_ASSET'
AND tasb.tsu_code = 'ENTERED';
SELECT cle.id fixedasst_line
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.cle_id = p_finasst_line
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET';
SELECT tal.id tal_id,
tas.id tas_id
FROM OKL_TRX_ASSETS TAS,
OKL_TXL_ASSETS_B TAL
WHERE tas.id = tal.tas_id
AND tas.tsu_code = 'ENTERED'
AND tas.tas_type = 'ALI'
AND tal.tal_type = 'ALI'
AND tal.kle_id = p_fixedasst_line;
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_tasv_rec,
x_thpv_rec => lx_tasv_rec);
SELECT cle.id,
cle.dnz_chr_id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.cle_id = p_cle_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET';
SELECT COUNT(iti.serial_number)
FROM okl_txl_itm_insts iti
WHERE iti.tal_id = PTalid
AND tal_type = 'ALI'
AND NVL(selected_for_split_flag,'N') = 'Y';
SELECT name
FROM okc_k_lines_tl
WHERE id = p_fin_ast_id;