[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* the procedure is located at the global level and not at the insert row level as the same procedure
* can later be used for capturing other DML logic.
*/
-------------------------------------------------------------------------------
-- PROCEDURE raise_business_event
-------------------------------------------------------------------------------
-- Start of comments
--
-- Procedure Name : raise_business_event
-- Description : This procedure is a wrapper that raises a business event
-- : when ever asset serial numbers are created or deleted.
-- Business Rules :
-- Parameters : p_chr_id,p_asset_id, p_ser_num,p_event_name along with other api params
-- Version : 1.0
-- History : 30-AUG-2004 SJALASUT created
-- End of comments
PROCEDURE raise_business_event(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_chr_id IN okc_k_headers_b.id%TYPE,
p_asset_id IN okc_k_lines_b.id%TYPE,
p_ser_num IN okl_txl_itm_insts.serial_number%TYPE,
p_event_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_parameter_list wf_parameter_list_t;
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
OKL_ITI_PVT.insert_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_iivv_rec,
x_iivv_rec);
OKL_ITI_PVT.insert_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_iivv_tbl,
x_iivv_tbl);
PROCEDURE update_txl_itm_insts(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_iivv_rec IN iivv_rec_type,
x_iivv_rec OUT NOCOPY iivv_rec_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TXL_ITM_INSTS';
SELECT cle.dnz_chr_id
FROM okc_k_lines_b cle
WHERE cle.id = p_kle_id;
SELECT serial_number, dnz_cle_id
FROM okl_txl_itm_insts
WHERE id = p_id;
OKL_ITI_PVT.update_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_iivv_rec,
x_iivv_rec);
END update_txl_itm_insts;
PROCEDURE update_txl_itm_insts(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_iivv_tbl IN iivv_tbl_type,
x_iivv_tbl OUT NOCOPY iivv_tbl_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TXL_ITM_INSTS';
OKL_ITI_PVT.update_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_iivv_tbl,
x_iivv_tbl);
END update_txl_itm_insts;
PROCEDURE delete_txl_itm_insts(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_iivv_rec IN iivv_rec_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TXL_ITM_INSTS';
SELECT cle.dnz_chr_id
FROM okc_k_lines_b cle
WHERE cle.id = p_kle_id;
SELECT lines.dnz_chr_id, items.dnz_cle_id, items.serial_number
FROM okc_k_lines_b lines, okl_txl_itm_insts items
WHERE items.id = p_inst_id
AND lines.id = items.dnz_cle_id;
OKL_ITI_PVT.delete_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_iivv_rec);
END delete_txl_itm_insts;
PROCEDURE delete_txl_itm_insts(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_iivv_tbl IN iivv_tbl_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TXL_ITM_INSTS';
OKL_ITI_PVT.delete_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_iivv_tbl);
END delete_txl_itm_insts;
SELECT 'Y'
FROM okc_k_headers_b chr,
okc_statuses_b sts
WHERE chr.id = p_chr_id
AND sts.code = chr.sts_code
AND sts.ste_code IN ('SIGNED', 'ENTERED');
SELECT line.id asset_id
FROM okc_k_lines_b line,
okc_line_styles_b style,
okc_statuses_b sts
WHERE line.lse_id = style.id
AND style.lty_code = 'FREE_FORM1'
AND line.dnz_chr_id = p_chr_id
AND line.id = nvl(p_line_id,line.id)
AND sts.code = line.sts_code
AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED','CANCELLED');
SELECT mtl.serial_number_control_code,
mtl.description item_desc
FROM okc_k_lines_b line,
okc_line_styles_b style,
okc_k_items kitem,
mtl_system_items mtl
WHERE line.lse_id = style.id
AND style.lty_code = 'ITEM'
AND line.id = kitem.cle_id
AND kitem.jtot_object1_code = 'OKX_SYSITEM'
AND kitem.object1_id1 = mtl.inventory_item_id
AND kitem.object1_id2 = TO_CHAR(mtl.organization_id)
AND line.cle_id = p_line_id;
SELECT inst.id inst_id
FROM okc_k_lines_b ff2,
okc_k_lines_b inst,
okc_line_styles_b ff2style,
okc_line_styles_b inststyle,
okl_txl_itm_insts txl
WHERE ff2.lse_id = ff2style.id
AND ff2style.lty_code = 'FREE_FORM2'
AND ff2.id = inst.cle_id
AND inst.lse_id = inststyle.id
AND inststyle.lty_code = 'INST_ITEM'
AND txl.kle_id = inst.id
AND ff2.cle_id = p_asset_id;
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,
DNZ_CLE_ID,
instance_id,
selected_for_split_flag,
asd_id
FROM OKL_TXL_ITM_INSTS_V iti
WHERE iti.kle_id = p_id;
l_itiv_rec.LAST_UPDATED_BY,
l_itiv_rec.LAST_UPDATE_DATE,
l_itiv_rec.LAST_UPDATE_LOGIN,
l_itiv_rec.DNZ_CLE_ID,
l_itiv_rec.instance_id,
l_itiv_rec.selected_for_split_flag,
l_itiv_rec.asd_id;
okl_txl_itm_insts_pvt.update_txl_itm_insts(
p_api_version => 1.0,
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_iivv_rec => l_iivv_rec,
x_iivv_rec => x_iivv_rec
);