[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM OKS_K_LINES_TLH T
WHERE NOT EXISTS (
SELECT NULL
FROM OKS_K_LINES_BH B
WHERE B.ID =T.ID
AND B.MAJOR_VERSION =T.MAJOR_VERSION
);
UPDATE OKS_K_LINES_TLH T SET(
INVOICE_TEXT,
IB_TRX_DETAILS,
STATUS_TEXT,
REACT_TIME_NAME) = (SELECT
B.INVOICE_TEXT,
B.IB_TRX_DETAILS,
B.STATUS_TEXT,
B.REACT_TIME_NAME
FROM OKS_K_LINES_TLH B
WHERE B.ID = T.ID
AND B.MAJOR_VERSION = T.MAJOR_VERSION
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE ( T.ID,
T.MAJOR_VERSION,
T.LANGUAGE)
IN (SELECT
SUBT.ID,
SUBT.MAJOR_VERSION,
SUBT.LANGUAGE
FROM OKS_K_LINES_TLH SUBB, OKS_K_LINES_TLH SUBT
WHERE SUBB.ID = SUBT.ID
AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.INVOICE_TEXT <> SUBT.INVOICE_TEXT
OR SUBB.IB_TRX_DETAILS <> SUBT.IB_TRX_DETAILS
OR SUBB.STATUS_TEXT <> SUBT.STATUS_TEXT
OR SUBB.REACT_TIME_NAME <> SUBT.REACT_TIME_NAME
OR (SUBB.INVOICE_TEXT IS NULL AND SUBT.INVOICE_TEXT IS NOT NULL)
OR (SUBB.IB_TRX_DETAILS IS NULL AND SUBT.IB_TRX_DETAILS IS NOT NULL)
OR (SUBB.STATUS_TEXT IS NULL AND SUBT.STATUS_TEXT IS NOT NULL)
OR (SUBB.REACT_TIME_NAME IS NULL AND SUBT.REACT_TIME_NAME IS NOT NULL)
));
INSERT INTO OKS_K_LINES_TLH (
ID,
MAJOR_VERSION,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
INVOICE_TEXT,
IB_TRX_DETAILS,
STATUS_TEXT,
REACT_TIME_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT
B.ID,
B.MAJOR_VERSION,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.SFWT_FLAG,
B.INVOICE_TEXT,
B.IB_TRX_DETAILS,
B.STATUS_TEXT,
B.REACT_TIME_NAME,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN
FROM OKS_K_LINES_TLH B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (
SELECT NULL
FROM OKS_K_LINES_TLH T
WHERE T.ID = B.ID
AND T.MAJOR_VERSION = B.MAJOR_VERSION
AND T.LANGUAGE = L.LANGUAGE_CODE
);
oks_kln_pvt.insert_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_klnv_rec => p_klnv_rec,
x_klnv_rec => x_klnv_rec
);
oks_kln_pvt.insert_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_klnv_tbl => p_klnv_tbl,
x_klnv_tbl => x_klnv_tbl,
px_error_tbl => px_error_tbl
);
oks_kln_pvt.insert_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_klnv_tbl => p_klnv_tbl,
x_klnv_tbl => x_klnv_tbl
);
PROCEDURE update_line(
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_klnv_rec IN klnv_rec_type,
x_klnv_rec OUT NOCOPY klnv_rec_type,
p_validate_yn IN VARCHAR2) IS
BEGIN
x_return_status := G_RET_STS_SUCCESS;
oks_kln_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_klnv_rec => p_klnv_rec,
x_klnv_rec => x_klnv_rec
);
END update_line;
PROCEDURE update_line(
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_klnv_tbl IN klnv_tbl_type,
x_klnv_tbl OUT NOCOPY klnv_tbl_type,
px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE,
p_validate_yn IN VARCHAR2) IS
BEGIN
x_return_status := G_RET_STS_SUCCESS;
oks_kln_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_klnv_tbl => p_klnv_tbl,
x_klnv_tbl => x_klnv_tbl,
px_error_tbl => px_error_tbl
);
END update_line;
PROCEDURE update_line(
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_klnv_tbl IN klnv_tbl_type,
x_klnv_tbl OUT NOCOPY klnv_tbl_type,
p_validate_yn IN VARCHAR2) IS
BEGIN
x_return_status := G_RET_STS_SUCCESS;
oks_kln_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_klnv_tbl => p_klnv_tbl,
x_klnv_tbl => x_klnv_tbl
);
END update_line;
PROCEDURE delete_line(
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_klnv_rec IN klnv_rec_type) IS
BEGIN
oks_kln_pvt.delete_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_klnv_rec => p_klnv_rec
);
END delete_line;
PROCEDURE delete_line(
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_klnv_tbl IN klnv_tbl_type,
px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
BEGIN
oks_kln_pvt.delete_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_klnv_tbl => p_klnv_tbl,
px_error_tbl => px_error_tbl
);
END delete_line;
PROCEDURE delete_line(
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_klnv_tbl IN klnv_tbl_type) IS
BEGIN
oks_kln_pvt.delete_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_klnv_tbl => p_klnv_tbl
);
END delete_line;
PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,
p_klnv_tbl IN klnv_tbl_type) IS
l_tabsize NUMBER := p_klnv_tbl.COUNT;
in_last_updated_by OKC_DATATYPES.Number15TabTyp;
in_last_update_date OKC_DATATYPES.DateTabTyp;
in_last_update_login OKC_DATATYPES.Number15TabTyp;
in_last_updated_by(j) := p_klnv_tbl(i).last_updated_by;
in_last_update_date(j) := p_klnv_tbl(i).last_update_date;
in_last_update_login(j) := p_klnv_tbl(i).last_update_login;
INSERT
INTO OKS_K_LINES_B
(
id,
cle_id,
dnz_chr_id,
discount_list,
acct_rule_id,
payment_type,
cc_no,
cc_expiry_date,
cc_bank_acct_id,
cc_auth_code,
commitment_id,
locked_price_list_id,
usage_est_yn,
usage_est_method,
usage_est_start_date,
termn_method,
ubt_amount,
credit_amount,
suppressed_credit,
override_amount,
cust_po_number_req_yn,
cust_po_number,
grace_duration,
grace_period,
inv_print_flag,
price_uom,
tax_amount,
tax_inclusive_yn,
tax_status,
tax_code,
tax_exemption_id,
ib_trans_type,
ib_trans_date,
prod_price,
service_price,
clvl_list_price,
clvl_quantity,
clvl_extended_amt,
clvl_uom_code,
toplvl_operand_code,
toplvl_operand_val,
toplvl_quantity,
toplvl_uom_code,
toplvl_adj_price,
toplvl_price_qty,
averaging_interval,
settlement_interval,
minimum_quantity,
default_quantity,
amcv_flag,
fixed_quantity,
usage_duration,
usage_period,
level_yn,
usage_type,
uom_quantified,
base_reading,
billing_schedule_type,
full_credit,
coverage_type,
exception_cov_id,
limit_uom_quantified,
discount_amount,
discount_percent,
offset_duration,
offset_period,
incident_severity_id,
pdf_id,
work_thru_yn,
react_active_yn,
transfer_option,
prod_upgrade_yn,
inheritance_type,
pm_program_id,
pm_conf_req_yn,
pm_sch_exists_yn,
allow_bt_discount,
apply_default_timezone,
sync_date_install,
object_version_number,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
---skuchima VRM ER
revenue_Impact_Date,
counter_value_id
) VALUES (
in_id(i),
in_cle_id(i),
in_dnz_chr_id(i),
in_discount_list(i),
in_acct_rule_id(i),
in_payment_type(i),
in_cc_no(i),
in_cc_expiry_date(i),
in_cc_bank_acct_id(i),
in_cc_auth_code(i),
in_commitment_id(i),
in_locked_price_list_id(i),
in_usage_est_yn(i),
in_usage_est_method(i),
in_usage_est_start_date(i),
in_termn_method(i),
in_ubt_amount(i),
in_credit_amount(i),
in_suppressed_credit(i),
in_override_amount(i),
in_cust_po_number_req_yn(i),
in_cust_po_number(i),
in_grace_duration(i),
in_grace_period(i),
in_inv_print_flag(i),
in_price_uom(i),
in_tax_amount(i),
in_tax_inclusive_yn(i),
in_tax_status(i),
in_tax_code(i),
in_tax_exemption_id(i),
in_ib_trans_type(i),
in_ib_trans_date(i),
in_prod_price(i),
in_service_price(i),
in_clvl_list_price(i),
in_clvl_quantity(i),
in_clvl_extended_amt(i),
in_clvl_uom_code(i),
in_toplvl_operand_code(i),
in_toplvl_operand_val(i),
in_toplvl_quantity(i),
in_toplvl_uom_code(i),
in_toplvl_adj_price(i),
in_toplvl_price_qty(i),
in_averaging_interval(i),
in_settlement_interval(i),
in_minimum_quantity(i),
in_default_quantity(i),
in_amcv_flag(i),
in_fixed_quantity(i),
in_usage_duration(i),
in_usage_period(i),
in_level_yn(i),
in_usage_type(i),
in_uom_quantified(i),
in_base_reading(i),
in_billing_schedule_type(i),
in_full_credit(i),
in_coverage_type(i),
in_exception_cov_id(i),
in_limit_uom_quantified(i),
in_discount_amount(i),
in_discount_percent(i),
in_offset_duration(i),
in_offset_period(i),
in_incident_severity_id(i),
in_pdf_id(i),
in_work_thru_yn(i),
in_react_active_yn(i),
in_transfer_option(i),
in_prod_upgrade_yn(i),
in_inheritance_type(i),
in_pm_program_id(i),
in_pm_conf_req_yn(i),
in_pm_sch_exists_yn(i),
in_allow_bt_discount(i),
in_apply_default_timezone(i),
in_sync_date_install(i),
in_object_version_number(i),
in_request_id(i),
in_created_by(i),
in_creation_date(i),
in_last_updated_by(i),
in_last_update_date(i),
in_last_update_login(i),
---skuchima VRM ER
in_revenue_Impact_Date(i),
in_counter_value_id(i)
);
INSERT INTO OKS_K_LINES_TL(
id,
language,
source_lang,
sfwt_flag,
invoice_text,
ib_trx_details,
status_text,
react_time_name,
security_group_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
in_id(i),
OKC_UTIL.g_language_code(lang_i),
in_source_lang(i),
in_sfwt_flag(i),
in_invoice_text(i),
in_ib_trx_details(i),
in_status_text(i),
in_react_time_name(i),
in_security_group_id(i),
in_created_by(i),
in_creation_date(i),
in_last_updated_by(i),
in_last_update_date(i),
in_last_update_login(i)
);
END INSERT_ROW_UPG;
in_last_updated_by OKC_DATATYPES.Number15TabTyp;
in_last_update_date OKC_DATATYPES.DateTabTyp;
in_last_update_login OKC_DATATYPES.Number15TabTyp;
in_last_updated_by(j) := p_klnhv_tbl(i).last_updated_by;
in_last_update_date(j) := p_klnhv_tbl(i).last_update_date;
in_last_update_login(j) := p_klnhv_tbl(i).last_update_login;
Insert Into oks_k_lines_bh
(
id ,
major_version,
cle_id ,
dnz_chr_id ,
discount_list ,
acct_rule_id ,
payment_type ,
cc_no ,
cc_expiry_date ,
cc_bank_acct_id ,
cc_auth_code ,
commitment_id ,
locked_price_list_id ,
usage_est_yn ,
usage_est_method ,
usage_est_start_date ,
termn_method ,
ubt_amount ,
credit_amount ,
suppressed_credit ,
override_amount ,
cust_po_number_req_yn,
cust_po_number,
grace_duration ,
grace_period ,
inv_print_flag ,
price_uom ,
tax_amount ,
tax_inclusive_yn ,
tax_status ,
tax_code ,
tax_exemption_id ,
ib_trans_type ,
ib_trans_date ,
prod_price ,
service_price ,
clvl_list_price ,
clvl_quantity ,
clvl_extended_amt ,
clvl_uom_code ,
toplvl_operand_code ,
toplvl_operand_val ,
toplvl_quantity ,
toplvl_uom_code ,
toplvl_adj_price ,
toplvl_price_qty ,
averaging_interval ,
settlement_interval ,
minimum_quantity ,
default_quantity ,
amcv_flag ,
fixed_quantity ,
usage_duration ,
usage_period ,
level_yn ,
usage_type ,
uom_quantified ,
base_reading ,
billing_schedule_type ,
full_credit ,
coverage_type ,
exception_cov_id ,
limit_uom_quantified ,
discount_amount ,
discount_percent ,
offset_duration ,
offset_period ,
incident_severity_id ,
pdf_id ,
work_thru_yn ,
react_active_yn ,
transfer_option ,
prod_upgrade_yn ,
inheritance_type ,
pm_program_id ,
pm_conf_req_yn ,
pm_sch_exists_yn ,
allow_bt_discount ,
apply_default_timezone ,
sync_date_install,
object_version_number ,
security_group_id ,
request_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
--skuchima VRM ER
revenue_Impact_Date,
counter_value_id
)
Values (
in_id(i),
in_major_version(i),
in_cle_id(i),
in_dnz_chr_id(i),
in_discount_list(i),
in_acct_rule_id(i),
in_payment_type(i),
in_cc_no(i),
in_cc_expiry_date(i),
in_cc_bank_acct_id(i),
in_cc_auth_code(i),
in_commitment_id(i),
in_locked_price_list_id(i),
in_usage_est_yn(i),
in_usage_est_method(i),
in_usage_est_start_date(i),
in_termn_method(i),
in_ubt_amount(i),
in_credit_amount(i),
in_suppressed_credit(i),
in_override_amount(i),
in_cust_po_number_req_yn(i),
in_cust_po_number(i),
in_grace_duration(i),
in_grace_period(i),
in_inv_print_flag(i),
in_price_uom(i),
in_tax_amount(i),
in_tax_inclusive_yn(i),
in_tax_status(i),
in_tax_code(i),
in_tax_exemption_id(i),
in_ib_trans_type(i),
in_ib_trans_date(i),
in_prod_price(i),
in_service_price(i),
in_clvl_list_price(i),
in_clvl_quantity(i),
in_clvl_extended_amt(i),
in_clvl_uom_code(i),
in_toplvl_operand_code(i),
in_toplvl_operand_val(i),
in_toplvl_quantity(i),
in_toplvl_uom_code(i),
in_toplvl_adj_price(i),
in_toplvl_price_qty(i),
in_averaging_interval(i),
in_settlement_interval(i),
in_minimum_quantity(i),
in_default_quantity(i),
in_amcv_flag(i),
in_fixed_quantity(i),
in_usage_duration(i),
in_usage_period(i),
in_level_yn(i),
in_usage_type(i),
in_uom_quantified(i),
in_base_reading(i),
in_billing_schedule_type(i),
in_full_credit(i),
in_coverage_type(i),
in_exception_cov_id(i),
in_limit_uom_quantified(i),
in_discount_amount(i),
in_discount_percent(i),
in_offset_duration(i),
in_offset_period(i),
in_incident_severity_id(i),
in_pdf_id(i),
in_work_thru_yn(i),
in_react_active_yn(i),
in_transfer_option(i),
in_prod_upgrade_yn(i),
in_inheritance_type(i),
in_pm_program_id(i),
in_pm_conf_req_yn(i),
in_pm_sch_exists_yn(i),
in_allow_bt_discount(i),
in_apply_default_timezone(i),
in_sync_date_install(i),
in_object_version_number(i),
in_security_group_id(i),
in_request_id(i),
in_created_by(i),
in_creation_date(i),
in_last_updated_by(i),
in_last_update_date(i),
in_last_update_login(i),
--skuchima VRM ER
in_revenue_Impact_Date(i),
in_counter_value_id(i)
);
Insert Into oks_k_lines_tlh
(
id ,
major_version,
language ,
source_lang ,
sfwt_flag ,
invoice_text ,
ib_trx_details ,
status_text ,
react_time_name ,
security_group_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
) Values (
in_id (i),
in_major_version(i),
OKC_UTIL.g_language_code(lang_i),
in_source_lang (i),
in_sfwt_flag (i),
in_invoice_text (i),
in_ib_trx_details (i),
in_status_text (i),
in_react_time_name (i),
in_security_group_id (i),
in_created_by (i),
in_creation_date (i),
in_last_updated_by (i),
in_last_update_date (i),
in_last_update_login(i)
);