[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM OKL_LEASE_QUOTES_TL T
WHERE NOT EXISTS (SELECT NULL FROM OKL_LEASE_QUOTES_B B WHERE B.ID =T.ID);
UPDATE OKL_LEASE_QUOTES_TL T
SET (SHORT_DESCRIPTION,
DESCRIPTION,
COMMENTS) =
(SELECT
B.SHORT_DESCRIPTION,
B.DESCRIPTION,
B.COMMENTS
FROM
OKL_LEASE_QUOTES_TL B
WHERE
B.ID = T.ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (T.ID, T.LANGUAGE) IN (SELECT
SUBT.ID,
SUBT.LANGUAGE
FROM
OKL_LEASE_QUOTES_TL SUBB,
OKL_LEASE_QUOTES_TL SUBT
WHERE
SUBB.ID = SUBT.ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
OR (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
OR (SUBB.COMMENTS <> SUBT.COMMENTS)
OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
)
);
INSERT INTO OKL_LEASE_QUOTES_TL (
ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SHORT_DESCRIPTION,
DESCRIPTION,
COMMENTS)
SELECT
B.ID,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.SFWT_FLAG,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.SHORT_DESCRIPTION,
B.DESCRIPTION,
B.COMMENTS
FROM OKL_LEASE_QUOTES_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (
SELECT NULL
FROM OKL_LEASE_QUOTES_TL T
WHERE T.ID = B.ID
AND T.LANGUAGE = L.LANGUAGE_CODE
);
SELECT
id
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,reference_number
,status
,parent_object_code
,parent_object_id
,valid_from
,valid_to
,customer_bookclass
,customer_taxowner
,expected_start_date
,expected_funding_date
,expected_delivery_date
,pricing_method
,term
,product_id
,end_of_term_option_id
,structured_pricing
,line_level_pricing
,rate_template_id
,rate_card_id
,lease_rate_factor
,target_rate_type
,target_rate
,target_amount
,target_frequency
,target_arrears_yn
,target_periods
,iir
,booking_yield
,pirr
,airr
,sub_iir
,sub_booking_yield
,sub_pirr
,sub_airr
,usage_category
,usage_industry_class
,usage_industry_code
,usage_amount
,usage_location_id
,property_tax_applicable
,property_tax_billing_type
,upfront_tax_treatment
,upfront_tax_stream_type
,transfer_of_title
,age_of_equipment
,purchase_of_lease
,sale_and_lease_back
,interest_disclosed
,primary_quote
,legal_entity_id
-- Bug 5908845. eBTax Enhancement Project
,line_intended_use
-- End Bug 5908845. eBTax Enhancement Project
,short_description
,description
,comments
INTO
l_lsqv_rec.id
,l_lsqv_rec.object_version_number
,l_lsqv_rec.attribute_category
,l_lsqv_rec.attribute1
,l_lsqv_rec.attribute2
,l_lsqv_rec.attribute3
,l_lsqv_rec.attribute4
,l_lsqv_rec.attribute5
,l_lsqv_rec.attribute6
,l_lsqv_rec.attribute7
,l_lsqv_rec.attribute8
,l_lsqv_rec.attribute9
,l_lsqv_rec.attribute10
,l_lsqv_rec.attribute11
,l_lsqv_rec.attribute12
,l_lsqv_rec.attribute13
,l_lsqv_rec.attribute14
,l_lsqv_rec.attribute15
,l_lsqv_rec.reference_number
,l_lsqv_rec.status
,l_lsqv_rec.parent_object_code
,l_lsqv_rec.parent_object_id
,l_lsqv_rec.valid_from
,l_lsqv_rec.valid_to
,l_lsqv_rec.customer_bookclass
,l_lsqv_rec.customer_taxowner
,l_lsqv_rec.expected_start_date
,l_lsqv_rec.expected_funding_date
,l_lsqv_rec.expected_delivery_date
,l_lsqv_rec.pricing_method
,l_lsqv_rec.term
,l_lsqv_rec.product_id
,l_lsqv_rec.end_of_term_option_id
,l_lsqv_rec.structured_pricing
,l_lsqv_rec.line_level_pricing
,l_lsqv_rec.rate_template_id
,l_lsqv_rec.rate_card_id
,l_lsqv_rec.lease_rate_factor
,l_lsqv_rec.target_rate_type
,l_lsqv_rec.target_rate
,l_lsqv_rec.target_amount
,l_lsqv_rec.target_frequency
,l_lsqv_rec.target_arrears_yn
,l_lsqv_rec.target_periods
,l_lsqv_rec.iir
,l_lsqv_rec.booking_yield
,l_lsqv_rec.pirr
,l_lsqv_rec.airr
,l_lsqv_rec.sub_iir
,l_lsqv_rec.sub_booking_yield
,l_lsqv_rec.sub_pirr
,l_lsqv_rec.sub_airr
,l_lsqv_rec.usage_category
,l_lsqv_rec.usage_industry_class
,l_lsqv_rec.usage_industry_code
,l_lsqv_rec.usage_amount
,l_lsqv_rec.usage_location_id
,l_lsqv_rec.property_tax_applicable
,l_lsqv_rec.property_tax_billing_type
,l_lsqv_rec.upfront_tax_treatment
,l_lsqv_rec.upfront_tax_stream_type
,l_lsqv_rec.transfer_of_title
,l_lsqv_rec.age_of_equipment
,l_lsqv_rec.purchase_of_lease
,l_lsqv_rec.sale_and_lease_back
,l_lsqv_rec.interest_disclosed
,l_lsqv_rec.primary_quote
,l_lsqv_rec.legal_entity_id
-- Bug 5908845. eBTax Enhancement Project
,l_lsqv_rec.line_intended_use
-- End Bug 5908845. eBTax Enhancement Project
,l_lsqv_rec.short_description
,l_lsqv_rec.description
,l_lsqv_rec.comments
FROM okl_lease_quotes_v
WHERE id = p_id;
PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_lsq_rec IN lsq_rec_type) IS
l_prog_name VARCHAR2(61);
l_prog_name := G_PKG_NAME||'.insert_row (B)';
INSERT INTO okl_lease_quotes_b (
id
,object_version_number
,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
,reference_number
,status
,parent_object_code
,parent_object_id
,valid_from
,valid_to
,customer_bookclass
,customer_taxowner
,expected_start_date
,expected_funding_date
,expected_delivery_date
,pricing_method
,term
,product_id
,end_of_term_option_id
,structured_pricing
,line_level_pricing
,rate_template_id
,rate_card_id
,lease_rate_factor
,target_rate_type
,target_rate
,target_amount
,target_frequency
,target_arrears_yn
,target_periods
,iir
,booking_yield
,pirr
,airr
,sub_iir
,sub_booking_yield
,sub_pirr
,sub_airr
,usage_category
,usage_industry_class
,usage_industry_code
,usage_amount
,usage_location_id
,property_tax_applicable
,property_tax_billing_type
,upfront_tax_treatment
,upfront_tax_stream_type
,transfer_of_title
,age_of_equipment
,purchase_of_lease
,sale_and_lease_back
,interest_disclosed
,primary_quote
,legal_entity_id
--Bug 5908845. eBTax Enhancement Project
,line_intended_use
-- End Bug 5908845. eBTax Enhancement Project
)
VALUES
(
p_lsq_rec.id
,p_lsq_rec.object_version_number
,p_lsq_rec.attribute_category
,p_lsq_rec.attribute1
,p_lsq_rec.attribute2
,p_lsq_rec.attribute3
,p_lsq_rec.attribute4
,p_lsq_rec.attribute5
,p_lsq_rec.attribute6
,p_lsq_rec.attribute7
,p_lsq_rec.attribute8
,p_lsq_rec.attribute9
,p_lsq_rec.attribute10
,p_lsq_rec.attribute11
,p_lsq_rec.attribute12
,p_lsq_rec.attribute13
,p_lsq_rec.attribute14
,p_lsq_rec.attribute15
,G_USER_ID
,SYSDATE
,G_USER_ID
,SYSDATE
,G_LOGIN_ID
,p_lsq_rec.reference_number
,p_lsq_rec.status
,p_lsq_rec.parent_object_code
,p_lsq_rec.parent_object_id
,p_lsq_rec.valid_from
,p_lsq_rec.valid_to
,p_lsq_rec.customer_bookclass
,p_lsq_rec.customer_taxowner
,p_lsq_rec.expected_start_date
,p_lsq_rec.expected_funding_date
,p_lsq_rec.expected_delivery_date
,p_lsq_rec.pricing_method
,p_lsq_rec.term
,p_lsq_rec.product_id
,p_lsq_rec.end_of_term_option_id
,p_lsq_rec.structured_pricing
,p_lsq_rec.line_level_pricing
,p_lsq_rec.rate_template_id
,p_lsq_rec.rate_card_id
,p_lsq_rec.lease_rate_factor
,p_lsq_rec.target_rate_type
,p_lsq_rec.target_rate
,p_lsq_rec.target_amount
,p_lsq_rec.target_frequency
,p_lsq_rec.target_arrears_yn
,p_lsq_rec.target_periods
,p_lsq_rec.iir
,p_lsq_rec.booking_yield
,p_lsq_rec.pirr
,p_lsq_rec.airr
,p_lsq_rec.sub_iir
,p_lsq_rec.sub_booking_yield
,p_lsq_rec.sub_pirr
,p_lsq_rec.sub_airr
,p_lsq_rec.usage_category
,p_lsq_rec.usage_industry_class
,p_lsq_rec.usage_industry_code
,p_lsq_rec.usage_amount
,p_lsq_rec.usage_location_id
,p_lsq_rec.property_tax_applicable
,p_lsq_rec.property_tax_billing_type
,p_lsq_rec.upfront_tax_treatment
,p_lsq_rec.upfront_tax_stream_type
,p_lsq_rec.transfer_of_title
,p_lsq_rec.age_of_equipment
,p_lsq_rec.purchase_of_lease
,p_lsq_rec.sale_and_lease_back
,p_lsq_rec.interest_disclosed
,p_lsq_rec.primary_quote
,p_lsq_rec.legal_entity_id
-- Bug 5908845. eBTax Enhancement Project
,p_lsq_rec.line_intended_use
-- End Bug 5908845. eBTax Enhancement Project
);
END insert_row;
PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_lsqtl_rec IN lsqtl_rec_type) IS
CURSOR get_languages IS
SELECT language_code
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
l_prog_name := G_PKG_NAME||'.insert_row (TL)';
INSERT INTO OKL_LEASE_QUOTES_TL (
id
,language
,source_lang
,sfwt_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,short_description
,description
,comments)
VALUES (
p_lsqtl_rec.id
,l_lang_rec.language_code
,USERENV('LANG')
,l_sfwt_flag
,G_USER_ID
,SYSDATE
,G_USER_ID
,SYSDATE
,G_LOGIN_ID
,p_lsqtl_rec.short_description
,p_lsqtl_rec.description
,p_lsqtl_rec.comments);
END insert_row;
PROCEDURE insert_row (
x_return_status OUT NOCOPY VARCHAR2,
p_lsqv_rec IN lsqv_rec_type,
x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.insert_row (V)';
SELECT okl_lsq_seq.nextval INTO l_lsqv_rec.ID FROM DUAL;
insert_row (x_return_status => l_return_status, p_lsq_rec => l_lsq_rec);
insert_row (x_return_status => l_return_status, p_lsqtl_rec => l_lsqtl_rec);
END insert_row;
PROCEDURE insert_row(
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_lsqv_rec IN lsqv_rec_type,
x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.insert_row (REC)';
insert_row (x_return_status => l_return_status,
p_lsqv_rec => p_lsqv_rec,
x_lsqv_rec => x_lsqv_rec);
END insert_row;
PROCEDURE insert_row(
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_lsqv_tbl IN lsqv_tbl_type,
x_lsqv_tbl OUT NOCOPY lsqv_tbl_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
insert_row (x_return_status => l_return_status,
p_lsqv_rec => p_lsqv_tbl(i),
x_lsqv_rec => x_lsqv_tbl(i));
END insert_row;
SELECT OBJECT_VERSION_NUMBER
FROM OKL_LEASE_QUOTES_B
WHERE ID = p_lsq_rec.id
AND OBJECT_VERSION_NUMBER = p_lsq_rec.object_version_number
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT OBJECT_VERSION_NUMBER
FROM OKL_LEASE_QUOTES_B
WHERE ID = p_lsq_rec.id;
PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_lsq_rec IN lsq_rec_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.update_row (B)';
UPDATE okl_lease_quotes_b
SET
object_version_number = p_lsq_rec.object_version_number+1
,attribute_category = p_lsq_rec.attribute_category
,attribute1 = p_lsq_rec.attribute1
,attribute2 = p_lsq_rec.attribute2
,attribute3 = p_lsq_rec.attribute3
,attribute4 = p_lsq_rec.attribute4
,attribute5 = p_lsq_rec.attribute5
,attribute6 = p_lsq_rec.attribute6
,attribute7 = p_lsq_rec.attribute7
,attribute8 = p_lsq_rec.attribute8
,attribute9 = p_lsq_rec.attribute9
,attribute10 = p_lsq_rec.attribute10
,attribute11 = p_lsq_rec.attribute11
,attribute12 = p_lsq_rec.attribute12
,attribute13 = p_lsq_rec.attribute13
,attribute14 = p_lsq_rec.attribute14
,attribute15 = p_lsq_rec.attribute15
,reference_number = p_lsq_rec.reference_number
,status = p_lsq_rec.status
,parent_object_code = p_lsq_rec.parent_object_code
,parent_object_id = p_lsq_rec.parent_object_id
,valid_from = p_lsq_rec.valid_from
,valid_to = p_lsq_rec.valid_to
,customer_bookclass = p_lsq_rec.customer_bookclass
,customer_taxowner = p_lsq_rec.customer_taxowner
,expected_start_date = p_lsq_rec.expected_start_date
,expected_funding_date = p_lsq_rec.expected_funding_date
,expected_delivery_date = p_lsq_rec.expected_delivery_date
,pricing_method = p_lsq_rec.pricing_method
,term = p_lsq_rec.term
,product_id = p_lsq_rec.product_id
,end_of_term_option_id = p_lsq_rec.end_of_term_option_id
,structured_pricing = p_lsq_rec.structured_pricing
,line_level_pricing = p_lsq_rec.line_level_pricing
,rate_template_id = p_lsq_rec.rate_template_id
,rate_card_id = p_lsq_rec.rate_card_id
,lease_rate_factor = p_lsq_rec.lease_rate_factor
,target_rate_type = p_lsq_rec.target_rate_type
,target_rate = p_lsq_rec.target_rate
,target_amount = p_lsq_rec.target_amount
,target_frequency = p_lsq_rec.target_frequency
,target_arrears_yn = p_lsq_rec.target_arrears_yn
,target_periods = p_lsq_rec.target_periods
,iir = p_lsq_rec.iir
,booking_yield = p_lsq_rec.booking_yield
,pirr = p_lsq_rec.pirr
,airr = p_lsq_rec.airr
,sub_iir = p_lsq_rec.sub_iir
,sub_booking_yield = p_lsq_rec.sub_booking_yield
,sub_pirr = p_lsq_rec.sub_pirr
,sub_airr = p_lsq_rec.sub_airr
,usage_category = p_lsq_rec.usage_category
,usage_industry_class = p_lsq_rec.usage_industry_class
,usage_industry_code = p_lsq_rec.usage_industry_code
,usage_amount = p_lsq_rec.usage_amount
,usage_location_id = p_lsq_rec.usage_location_id
,property_tax_applicable = p_lsq_rec.property_tax_applicable
,property_tax_billing_type = p_lsq_rec.property_tax_billing_type
,upfront_tax_treatment = p_lsq_rec.upfront_tax_treatment
,upfront_tax_stream_type = p_lsq_rec.upfront_tax_stream_type
,transfer_of_title = p_lsq_rec.transfer_of_title
,age_of_equipment = p_lsq_rec.age_of_equipment
,purchase_of_lease = p_lsq_rec.purchase_of_lease
,sale_and_lease_back = p_lsq_rec.sale_and_lease_back
,interest_disclosed = p_lsq_rec.interest_disclosed
,primary_quote = p_lsq_rec.primary_quote
,legal_entity_id = p_lsq_rec.legal_entity_id
-- Bug 5908845. eBTax Enhancement Project
,line_intended_use = p_lsq_rec.line_intended_use
-- End Bug 5908845. eBTax Enhancement Project
WHERE id = p_lsq_rec.id;
END update_row;
PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_lsqtl_rec IN lsqtl_rec_type) IS
l_prog_name VARCHAR2(61);
l_prog_name := G_PKG_NAME||'.update_row (TL)';
UPDATE OKL_LEASE_QUOTES_TL
SET
source_lang = USERENV('LANG')
,sfwt_flag = 'Y'
,last_updated_by = G_USER_ID
,last_update_date = SYSDATE
,last_update_login = G_LOGIN_ID
,short_description = p_lsqtl_rec.short_description
,description = p_lsqtl_rec.description
,comments = p_lsqtl_rec.comments
WHERE ID = p_lsqtl_rec.id;
UPDATE OKL_LEASE_QUOTES_TL
SET SFWT_FLAG = 'N'
WHERE ID = p_lsqtl_rec.id
AND SOURCE_LANG = LANGUAGE;
END update_row;
PROCEDURE update_row (
x_return_status OUT NOCOPY VARCHAR2,
p_lsqv_rec IN lsqv_rec_type,
x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
l_prog_name VARCHAR2(61);
l_prog_name := G_PKG_NAME||'.update_row (V)';
l_return_status := validate_attributes (l_lsqv_rec,'UPDATE'); --Bug 7596781
update_row (x_return_status => l_return_status, p_lsq_rec => l_lsq_rec);
update_row (x_return_status => l_return_status, p_lsqtl_rec => l_lsqtl_rec);
END update_row;
PROCEDURE update_row(
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_lsqv_rec IN lsqv_rec_type,
x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.update_row (REC)';
update_row (x_return_status => l_return_status,
p_lsqv_rec => p_lsqv_rec,
x_lsqv_rec => x_lsqv_rec);
END update_row;
PROCEDURE update_row(
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_lsqv_tbl IN lsqv_tbl_type,
x_lsqv_tbl OUT NOCOPY lsqv_tbl_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.update_row (TBL)';
update_row (x_return_status => l_return_status,
p_lsqv_rec => p_lsqv_tbl(i),
x_lsqv_rec => x_lsqv_tbl(i));
END update_row;
PROCEDURE delete_row(
x_return_status OUT NOCOPY VARCHAR2,
p_id IN NUMBER) IS
l_prog_name VARCHAR2(61);
l_prog_name := G_PKG_NAME||'.delete_row (V)';
DELETE FROM OKL_LEASE_QUOTES_B WHERE id = p_id;
DELETE FROM OKL_LEASE_QUOTES_TL WHERE id = p_id;
END delete_row;
PROCEDURE delete_row(
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_lsqv_rec IN lsqv_rec_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.delete_row (REC)';
delete_row (x_return_status => l_return_status,
p_id => p_lsqv_rec.id);
END delete_row;
PROCEDURE delete_row(
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_lsqv_tbl IN lsqv_tbl_type) IS
l_return_status VARCHAR2(1);
l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
delete_row (x_return_status => l_return_status,
p_id => p_lsqv_tbl(i).id);
END delete_row;