[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT result_code
FROM OKL_TAX_ATTR_DEFINITIONS
WHERE result_type_code = 'TBC_CODE'
AND nvl(TRY_ID, -99999) = nvl(p_tbcv_rec.TRY_ID,-99999)
AND nvl(STY_ID, -99999) = nvl(p_tbcv_rec.STY_ID,-99999)
AND nvl(BOOK_CLASS_CODE,'XXXXX') = nvl(p_tbcv_rec.BOOK_CLASS_CODE,'XXXXX')
AND nvl(TAX_COUNTRY_CODE,'XXXXX') = nvl(p_tbcv_rec.TAX_COUNTRY_CODE,'XXXXX')
AND nvl(TAX_ATTRIBUTE_DEF_ID, -99999) <> nvl(cp_tax_attribute_def_id,-99999)
AND nvl(EXPIRE_FLAG,'N')<> 'Y' ;
SELECT result_code
FROM OKL_TAX_ATTR_DEFINITIONS
WHERE result_type_code = 'PC_CODE'
AND nvl(PURCHASE_OPTION_CODE,'XXXXX') = nvl(p_tbcv_rec.PURCHASE_OPTION_CODE,'XXXXX')
AND nvl(STY_ID, -99999) = nvl(p_tbcv_rec.STY_ID,-99999)
AND nvl(INT_DISCLOSED_CODE,'N') = nvl(p_tbcv_rec.INT_DISCLOSED_CODE,'N')
AND nvl(TITLE_TRNSFR_CODE,'N') = nvl(p_tbcv_rec.TITLE_TRNSFR_CODE,'N')
AND nvl(SALE_LEASE_BACK_CODE,'N') = nvl(p_tbcv_rec.SALE_LEASE_BACK_CODE,'N')
AND nvl(LEASE_PURCHASED_CODE,'N') = nvl(p_tbcv_rec.LEASE_PURCHASED_CODE,'N')
AND nvl(TAX_COUNTRY_CODE,'XXXXX') = nvl(p_tbcv_rec.TAX_COUNTRY_CODE,'XXXXX')
AND nvl(TAX_ATTRIBUTE_DEF_ID, -99999) <> nvl(cp_tax_attribute_def_id,-99999)
AND nvl(EXPIRE_FLAG,'N')<> 'Y' ;
SELECT result_code
FROM OKL_TAX_ATTR_DEFINITIONS
WHERE result_type_code = 'UFC_CODE'
AND nvl(PURCHASE_OPTION_CODE,'XXXXX') = nvl(p_tbcv_rec.PURCHASE_OPTION_CODE,'XXXXX')
AND nvl(PDT_ID, -99999) = nvl(p_tbcv_rec.PDT_ID,-99999)
AND nvl(STY_ID, -99999) = nvl(p_tbcv_rec.STY_ID,-99999)
AND nvl(TRY_ID, -99999) = nvl(p_tbcv_rec.TRY_ID,-99999)
AND nvl(LEASE_PURCHASED_CODE,'N') = nvl(p_tbcv_rec.LEASE_PURCHASED_CODE,'N')
AND nvl(EQUIP_USAGE_CODE,'XXXXX') = nvl(p_tbcv_rec.EQUIP_USAGE_CODE,'XXXXX')
AND nvl(VENDOR_SITE_ID,-99999) = nvl(p_tbcv_rec.VENDOR_SITE_ID,-99999)
AND nvl(INT_DISCLOSED_CODE,'N') = nvl(p_tbcv_rec.INT_DISCLOSED_CODE,'N')
AND nvl(TITLE_TRNSFR_CODE,'N') = nvl(p_tbcv_rec.TITLE_TRNSFR_CODE,'N')
AND nvl(SALE_LEASE_BACK_CODE,'N') = nvl(p_tbcv_rec.SALE_LEASE_BACK_CODE,'N')
AND nvl(TAX_COUNTRY_CODE,'XXXXX') = nvl(p_tbcv_rec.TAX_COUNTRY_CODE,'XXXXX')
AND nvl(TERM_QUOTE_TYPE_CODE,'XXXXX') = nvl(p_tbcv_rec.TERM_QUOTE_TYPE_CODE,'XXXXX')
AND nvl(TERM_QUOTE_REASON_CODE,'XXXXX') = nvl(p_tbcv_rec.TERM_QUOTE_REASON_CODE,'XXXXX')
AND nvl(EXPIRE_FLAG,'N')<> 'Y'
AND nvl(TAX_ATTRIBUTE_DEF_ID, -99999) <> nvl(cp_tax_attribute_def_id,-99999)
AND ( ( -- This condition will allow cases where DB FROm and To are NULL and also Screen FROM and TO are null
--(AGE_OF_EQUIP_FROM IS NOT NULL OR AGE_OF_EQUIP_TO IS NOT NULL OR p_tbcv_rec.AGE_OF_EQUIP_FROM IS NOT NULL OR p_tbcv_rec.AGE_OF_EQUIP_TO IS NOT NULL )
-- AND
-- this condition will prevent exact matches (including cases where some values are null)
(nvl(AGE_OF_EQUIP_FROM,-99999) = nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,-99999) AND
nvl(AGE_OF_EQUIP_TO, -99999) = nvl(p_tbcv_rec.AGE_OF_EQUIP_TO,-99999)
)
)
OR -- age of equipment from can not be null for comparison purposes (when TO is not null),
-- as we can assume it is 0, if null
-- so this condition takes care of scenarios where both Froms and both Tos have a value
-- OR any of the FROMs are null and both Tos have a value
(--nvl(AGE_OF_EQUIP_FROM,0) IS NOT NULL AND nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) IS NOT NULL AND
AGE_OF_EQUIP_TO IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NOT NULL AND
( (nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) < nvl(AGE_OF_EQUIP_FROM,0) AND p_tbcv_rec.AGE_OF_EQUIP_TO >= nvl(AGE_OF_EQUIP_FROM,0))
OR
(nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) >= nvl(AGE_OF_EQUIP_FROM,0) AND nvl(p_tbcv_rec.AGE_OF_EQUIP_FROM,0) <= AGE_OF_EQUIP_TO) --AND p_tbcv_rec.AGE_OF_EQUIP_TO > AGE_OF_EQUIP_TO)
)
)
OR
( AGE_OF_EQUIP_TO IS NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NULL AND
-- In this case Both the FROMs can not be null together or have the same value, as it will get captured in condition 1
-- here, either DB FROM is Null and Screen FROM is not null --> This combination is ok
-- OR DB FROM is not null and Screen FROM is null --> this combinatio is ok
-- OR both FROMs have a value(differenr value) --> restrict this combination
AGE_OF_EQUIP_FROM IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_FROM IS NOT NULL -- The 2 FROMs can not have same value at this point
)
OR
( AGE_OF_EQUIP_TO IS NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NOT NULL AND -- TO in DB is Null,TO on screen is not null
-- In this case following scenarios are possible
-- DB FROM is Null (DB To is also NUll) FROM on the screen can be considered to be be >=0 (0 if null), since TO on screen is not null - OK
-- DB FROM >=0, SCREEN TO < DB FROM - ok
-- DB FROM >=0, SCREEN TO >= DB FROM - restrict this condition
AGE_OF_EQUIP_FROM >= 0 AND p_tbcv_rec.AGE_OF_EQUIP_TO >= AGE_OF_EQUIP_FROM
)
OR
( AGE_OF_EQUIP_TO IS NOT NULL AND p_tbcv_rec.AGE_OF_EQUIP_TO IS NULL AND
-- In this case following scenarios are possible
-- DB FROM can be considered to be >=0 (0 if null), since DB TO is not null, so there is a fixed age range defined in DB
-- SCREEN FROM is null (TO is always NULL) - OK
-- screen from >=0, SCREEN FROM > DB TO - ok
-- screen from >=0, screen from <= db to - RESTRICT THIS CONDITION
p_tbcv_rec.AGE_OF_EQUIP_FROM >=0 AND p_tbcv_rec.AGE_OF_EQUIP_FROM <= AGE_OF_EQUIP_TO
)
) ;
SELECT classification_name
FROM zx_fc_business_categories_v
WHERE classification_code = p_lookup_code;
SELECT classification_name
FROM zx_fc_product_categories_v
WHERE classification_code = p_lookup_code;
SELECT classification_name
FROM zx_fc_user_defined_v
WHERE classification_code = p_lookup_code;
PROCEDURE insert_tbc_definition(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_tbcv_rec IN tbcv_rec_type,
x_tbcv_rec OUT NOCOPY tbcv_rec_type) IS
l_api_version NUMBER ;
SAVEPOINT tbc_insert ;
okl_tbc_pvt.insert_row(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,lp_tbcv_rec
,lx_tbcv_rec);
ROLLBACK TO tbc_insert;
ROLLBACK TO tbc_insert;
ROLLBACK TO tbc_insert;
FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','insert_tbc_definition');
END insert_tbc_definition;
PROCEDURE insert_tbc_definition(
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_tbcv_tbl IN tbcv_tbl_type,
x_tbcv_tbl OUT NOCOPY tbcv_tbl_type) IS
l_api_version NUMBER;
SAVEPOINT tbc_insert;
okl_tbc_pvt.insert_row(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,lp_tbcv_tbl(i)
,lx_tbcv_rec);
ROLLBACK TO tbc_insert;
ROLLBACK TO tbc_insert;
ROLLBACK TO tbc_insert;
FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','insert_tbc_definition');
END insert_tbc_definition;
PROCEDURE update_tbc_definition(
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_tbcv_rec IN tbcv_rec_type
,x_tbcv_rec OUT NOCOPY tbcv_rec_type) IS
l_api_version NUMBER ;
SAVEPOINT tbc_update;
okl_tbc_pvt.update_row(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,lp_tbcv_rec
,lx_tbcv_rec);
ROLLBACK TO tbc_update;
ROLLBACK TO tbc_update;
ROLLBACK TO tbc_update;
FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','update_tbc_definition');
END update_tbc_definition;
PROCEDURE update_tbc_definition(
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_tbcv_tbl IN tbcv_tbl_type,
x_tbcv_tbl OUT NOCOPY tbcv_tbl_type) IS
l_api_version NUMBER;
SAVEPOINT tbc_update;
okl_tbc_pvt.update_row(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,lp_tbcv_tbl
,lx_tbcv_tbl);
ROLLBACK TO tbc_update;
ROLLBACK TO tbc_update;
ROLLBACK TO tbc_update;
FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','update_tbc_definition');
END update_tbc_definition;
PROCEDURE delete_tbc_definition(
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_tbcv_rec IN tbcv_rec_type) IS
i NUMBER :=0;
SAVEPOINT tbc_delete;
okl_tbc_pvt.delete_row(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,lp_tbcv_rec);
ROLLBACK TO tbc_delete;
ROLLBACK TO tbc_delete;
ROLLBACK TO tbc_delete;
FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','delete_tbc_definition');
END delete_tbc_definition;
PROCEDURE delete_tbc_definition(
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_tbcv_tbl IN tbcv_tbl_type) IS
i NUMBER := 0;
SAVEPOINT tbc_delete;
okl_tbc_pvt.delete_row(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,lp_tbcv_tbl);
ROLLBACK TO tbc_delete;
ROLLBACK TO tbc_delete;
ROLLBACK TO tbc_delete;
FND_MSG_PUB.ADD_EXC_MSG('OKL_TBC_DEFINITIONS_PUB','delete_tbc_definition');
END delete_tbc_definition;