[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* The following delete and update statements are commented out */
/* as a quick workaround to fix the time-consuming table handler issue */
/* Eventually we'll need to turn them into a separate fix_language procedure */
/* refer fnd bug# 3723612 for details and oks bug 4210278
/*
DELETE FROM OKS_K_LINES_TL T
WHERE NOT EXISTS (
SELECT NULL
FROM OKS_K_LINES_B B
WHERE B.ID =T.ID
);
UPDATE OKS_K_LINES_TL 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_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 OKS_K_LINES_TL SUBB, OKS_K_LINES_TL SUBT
WHERE SUBB.ID = SUBT.ID
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 /*+ append parallel (tt) */ INTO OKS_K_LINES_TL tt (
ID,
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 /*+ parallel(v) parallel(t) use_nl(t) */ v.* from
(select /*+ no_merge ordered parallel(b) */
B.ID,
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_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG IN ('I', 'B')
and B.LANGUAGE = USERENV('LANG')
) v,
OKS_K_LINES_TL t
WHERE t.ID(+) = v.ID
AND t.LANGUAGE(+) = v.LANGUAGE_CODE
and t.id is NULL;
SELECT
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,
LOCKED_PRICE_LIST_LINE_ID,
BREAK_UOM,
PRORATE,
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,
SFWT_FLAG,
INVOICE_TEXT,
IB_TRX_DETAILS,
STATUS_TEXT,
REACT_TIME_NAME,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
-- R12 Data Model Changes 4485150 Start
TRXN_EXTENSION_ID,
TAX_CLASSIFICATION_CODE,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE,
COVERAGE_ID,
STANDARD_COV_YN,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
ORIG_SYSTEM_SOURCE_CODE,
-- R12 Data Model Changes 4485150 End
--skuchima VRM ER
REVENUE_IMPACT_DATE,
COUNTER_VALUE_ID
FROM Oks_K_Lines_V
WHERE oks_k_lines_v.id = p_id;
l_klnv_rec.last_updated_by,
l_klnv_rec.last_update_date,
l_klnv_rec.last_update_login,
-- R12 Data Model Changes 4485150 Start
l_klnv_rec.trxn_extension_id,
l_klnv_rec.tax_classification_code,
l_klnv_rec.exempt_certificate_number,
l_klnv_rec.exempt_reason_code,
l_klnv_rec.coverage_id,
l_klnv_rec.standard_cov_yn,
l_klnv_rec.orig_system_id1,
l_klnv_rec.orig_system_reference1,
l_klnv_rec.orig_system_source_code,
-- R12 Data Model Changes 4485150 End
--skuchima VRM ER
l_klnv_rec.revenue_impact_date,
l_klnv_rec.counter_value_id
;
SELECT
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,
LOCKED_PRICE_LIST_LINE_ID,
BREAK_UOM,
PRORATE,
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,
-- R12 Data Model Changes 4485150 Start
TRXN_EXTENSION_ID,
TAX_CLASSIFICATION_CODE,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE,
COVERAGE_ID,
STANDARD_COV_YN,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
ORIG_SYSTEM_SOURCE_CODE ,
-- R12 Data Model Changes 4485150 End
--skuchima VRM ER
revenue_impact_date,
counter_value_id
FROM Oks_K_Lines_B
WHERE oks_k_lines_b.id = p_id;
l_kln_rec.last_updated_by,
l_kln_rec.last_update_date,
l_kln_rec.last_update_login,
-- R12 Data Model Changes 4485150 Start
l_kln_rec.trxn_extension_id,
l_kln_rec.tax_classification_code,
l_kln_rec.exempt_certificate_number,
l_kln_rec.exempt_reason_code,
l_kln_rec.coverage_id,
l_kln_rec.standard_cov_yn,
l_kln_rec.orig_system_id1,
l_kln_rec.orig_system_reference1,
l_kln_rec.orig_system_source_code,
-- R12 Data Model Changes 4485150 End
-- skuchima VRM ER
l_kln_rec.revenue_impact_date,
l_kln_rec.counter_value_id
;
SELECT
ID,
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
FROM Oks_K_Lines_Tl
WHERE oks_k_lines_tl.id = p_id
AND oks_k_lines_tl.language = p_language;
l_klt_rec.last_updated_by,
l_klt_rec.last_update_date,
l_klt_rec.last_update_login;
IF (l_klnv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
l_klnv_rec.last_updated_by := NULL;
IF (l_klnv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
l_klnv_rec.last_update_date := NULL;
IF (l_klnv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
l_klnv_rec.last_update_login := NULL;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
PROCEDURE insert_row(
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_kln_rec IN kln_rec_type,
x_kln_rec OUT NOCOPY kln_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
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,
locked_price_list_line_id,
break_uom,
prorate,
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,
-- R12 Data Model Changes 4485150 Start
trxn_extension_id,
tax_classification_code,
exempt_certificate_number,
exempt_reason_code,
coverage_id,
standard_cov_yn,
orig_system_id1,
orig_system_reference1,
orig_system_source_code ,
-- R12 Data Model Changes 4485150 End
-- skuchima VRM ER
Revenue_Impact_Date,
counter_value_id
)
VALUES (
l_kln_rec.id,
l_kln_rec.cle_id,
l_kln_rec.dnz_chr_id,
l_kln_rec.discount_list,
l_kln_rec.acct_rule_id,
l_kln_rec.payment_type,
l_kln_rec.cc_no,
l_kln_rec.cc_expiry_date,
l_kln_rec.cc_bank_acct_id,
l_kln_rec.cc_auth_code,
l_kln_rec.commitment_id,
l_kln_rec.locked_price_list_id,
l_kln_rec.usage_est_yn,
l_kln_rec.usage_est_method,
l_kln_rec.usage_est_start_date,
l_kln_rec.termn_method,
l_kln_rec.ubt_amount,
l_kln_rec.credit_amount,
l_kln_rec.suppressed_credit,
l_kln_rec.override_amount,
l_kln_rec.cust_po_number_req_yn,
l_kln_rec.cust_po_number,
l_kln_rec.grace_duration,
l_kln_rec.grace_period,
l_kln_rec.inv_print_flag,
l_kln_rec.price_uom,
l_kln_rec.tax_amount,
l_kln_rec.tax_inclusive_yn,
l_kln_rec.tax_status,
l_kln_rec.tax_code,
l_kln_rec.tax_exemption_id,
l_kln_rec.ib_trans_type,
l_kln_rec.ib_trans_date,
l_kln_rec.prod_price,
l_kln_rec.service_price,
l_kln_rec.clvl_list_price,
l_kln_rec.clvl_quantity,
l_kln_rec.clvl_extended_amt,
l_kln_rec.clvl_uom_code,
l_kln_rec.toplvl_operand_code,
l_kln_rec.toplvl_operand_val,
l_kln_rec.toplvl_quantity,
l_kln_rec.toplvl_uom_code,
l_kln_rec.toplvl_adj_price,
l_kln_rec.toplvl_price_qty,
l_kln_rec.averaging_interval,
l_kln_rec.settlement_interval,
l_kln_rec.minimum_quantity,
l_kln_rec.default_quantity,
l_kln_rec.amcv_flag,
l_kln_rec.fixed_quantity,
l_kln_rec.usage_duration,
l_kln_rec.usage_period,
l_kln_rec.level_yn,
l_kln_rec.usage_type,
l_kln_rec.uom_quantified,
l_kln_rec.base_reading,
l_kln_rec.billing_schedule_type,
l_kln_rec.full_credit,
l_kln_rec.locked_price_list_line_id,
l_kln_rec.break_uom,
l_kln_rec.prorate,
l_kln_rec.coverage_type,
l_kln_rec.exception_cov_id,
l_kln_rec.limit_uom_quantified,
l_kln_rec.discount_amount,
l_kln_rec.discount_percent,
l_kln_rec.offset_duration,
l_kln_rec.offset_period,
l_kln_rec.incident_severity_id,
l_kln_rec.pdf_id,
l_kln_rec.work_thru_yn,
l_kln_rec.react_active_yn,
l_kln_rec.transfer_option,
l_kln_rec.prod_upgrade_yn,
l_kln_rec.inheritance_type,
l_kln_rec.pm_program_id,
l_kln_rec.pm_conf_req_yn,
l_kln_rec.pm_sch_exists_yn,
l_kln_rec.allow_bt_discount,
l_kln_rec.apply_default_timezone,
l_kln_rec.sync_date_install,
l_kln_rec.object_version_number,
l_kln_rec.request_id,
l_kln_rec.created_by,
l_kln_rec.creation_date,
l_kln_rec.last_updated_by,
l_kln_rec.last_update_date,
l_kln_rec.last_update_login,
-- R12 Data Model Changes 4485150 Start
l_kln_rec.trxn_extension_id,
l_kln_rec.tax_classification_code,
l_kln_rec.exempt_certificate_number,
l_kln_rec.exempt_reason_code,
l_kln_rec.coverage_id,
l_kln_rec.standard_cov_yn,
l_kln_rec.orig_system_id1,
l_kln_rec.orig_system_reference1,
l_kln_rec.orig_system_source_code ,
-- R12 Data Model Changes 4485150 End
-- skuchima VRM ER
l_kln_rec.Revenue_Impact_Date ,
l_kln_rec.counter_value_id
);
END insert_row;
PROCEDURE insert_row(
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_klt_rec IN klt_rec_type,
x_klt_rec OUT NOCOPY klt_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
SELECT *
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
INSERT INTO OKS_K_LINES_TL(
id,
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)
VALUES (
l_klt_rec.id,
l_klt_rec.language,
l_klt_rec.source_lang,
l_klt_rec.sfwt_flag,
l_klt_rec.invoice_text,
l_klt_rec.ib_trx_details,
l_klt_rec.status_text,
l_klt_rec.react_time_name,
l_klt_rec.created_by,
l_klt_rec.creation_date,
l_klt_rec.last_updated_by,
l_klt_rec.last_update_date,
l_klt_rec.last_update_login);
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_klnv_rec IN klnv_rec_type,
x_klnv_rec OUT NOCOPY klnv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
l_klnv_rec.LAST_UPDATE_DATE := l_klnv_rec.CREATION_DATE;
l_klnv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_klnv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
insert_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_kln_rec,
lx_kln_rec
);
insert_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_klt_rec,
lx_klt_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_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) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
insert_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_error_rec.error_type,
x_msg_count => l_error_rec.msg_count,
x_msg_data => l_error_rec.msg_data,
p_klnv_rec => p_klnv_tbl(i),
x_klnv_rec => x_klnv_tbl(i));
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_klnv_tbl IN klnv_tbl_type,
x_klnv_tbl OUT NOCOPY klnv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
insert_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
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 => l_error_tbl);
END insert_row;
SELECT OBJECT_VERSION_NUMBER
FROM OKS_K_LINES_B
WHERE ID = p_kln_rec.id
AND OBJECT_VERSION_NUMBER = p_kln_rec.object_version_number
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT OBJECT_VERSION_NUMBER
FROM OKS_K_LINES_B
WHERE ID = p_kln_rec.id;
OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
SELECT *
FROM OKS_K_LINES_TL
WHERE ID = p_klt_rec.id
FOR UPDATE NOWAIT;
OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
PROCEDURE update_row(
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_kln_rec IN kln_rec_type,
x_kln_rec OUT NOCOPY kln_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
IF (x_kln_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_kln_rec.last_updated_by := l_kln_rec.last_updated_by;
IF (x_kln_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_kln_rec.last_update_date := l_kln_rec.last_update_date;
IF (x_kln_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_kln_rec.last_update_login := l_kln_rec.last_update_login;
UPDATE OKS_K_LINES_B
SET CLE_ID = l_def_kln_rec.cle_id,
DNZ_CHR_ID = l_def_kln_rec.dnz_chr_id,
DISCOUNT_LIST = l_def_kln_rec.discount_list,
ACCT_RULE_ID = l_def_kln_rec.acct_rule_id,
PAYMENT_TYPE = l_def_kln_rec.payment_type,
CC_NO = l_def_kln_rec.cc_no,
CC_EXPIRY_DATE = l_def_kln_rec.cc_expiry_date,
CC_BANK_ACCT_ID = l_def_kln_rec.cc_bank_acct_id,
CC_AUTH_CODE = l_def_kln_rec.cc_auth_code,
COMMITMENT_ID = l_def_kln_rec.commitment_id,
LOCKED_PRICE_LIST_ID = l_def_kln_rec.locked_price_list_id,
USAGE_EST_YN = l_def_kln_rec.usage_est_yn,
USAGE_EST_METHOD = l_def_kln_rec.usage_est_method,
USAGE_EST_START_DATE = l_def_kln_rec.usage_est_start_date,
TERMN_METHOD = l_def_kln_rec.termn_method,
UBT_AMOUNT = l_def_kln_rec.ubt_amount,
CREDIT_AMOUNT = l_def_kln_rec.credit_amount,
SUPPRESSED_CREDIT = l_def_kln_rec.suppressed_credit,
OVERRIDE_AMOUNT = l_def_kln_rec.override_amount,
CUST_PO_NUMBER_REQ_YN = l_def_kln_rec.cust_po_number_req_yn,
CUST_PO_NUMBER = l_def_kln_rec.cust_po_number,
GRACE_DURATION = l_def_kln_rec.grace_duration,
GRACE_PERIOD = l_def_kln_rec.grace_period,
INV_PRINT_FLAG = l_def_kln_rec.inv_print_flag,
PRICE_UOM = l_def_kln_rec.price_uom,
TAX_AMOUNT = l_def_kln_rec.tax_amount,
TAX_INCLUSIVE_YN = l_def_kln_rec.tax_inclusive_yn,
TAX_STATUS = l_def_kln_rec.tax_status,
TAX_CODE = l_def_kln_rec.tax_code,
TAX_EXEMPTION_ID = l_def_kln_rec.tax_exemption_id,
IB_TRANS_TYPE = l_def_kln_rec.ib_trans_type,
IB_TRANS_DATE = l_def_kln_rec.ib_trans_date,
PROD_PRICE = l_def_kln_rec.prod_price,
SERVICE_PRICE = l_def_kln_rec.service_price,
CLVL_LIST_PRICE = l_def_kln_rec.clvl_list_price,
CLVL_QUANTITY = l_def_kln_rec.clvl_quantity,
CLVL_EXTENDED_AMT = l_def_kln_rec.clvl_extended_amt,
CLVL_UOM_CODE = l_def_kln_rec.clvl_uom_code,
TOPLVL_OPERAND_CODE = l_def_kln_rec.toplvl_operand_code,
TOPLVL_OPERAND_VAL = l_def_kln_rec.toplvl_operand_val,
TOPLVL_QUANTITY = l_def_kln_rec.toplvl_quantity,
TOPLVL_UOM_CODE = l_def_kln_rec.toplvl_uom_code,
TOPLVL_ADJ_PRICE = l_def_kln_rec.toplvl_adj_price,
TOPLVL_PRICE_QTY = l_def_kln_rec.toplvl_price_qty,
AVERAGING_INTERVAL = l_def_kln_rec.averaging_interval,
SETTLEMENT_INTERVAL = l_def_kln_rec.settlement_interval,
MINIMUM_QUANTITY = l_def_kln_rec.minimum_quantity,
DEFAULT_QUANTITY = l_def_kln_rec.default_quantity,
AMCV_FLAG = l_def_kln_rec.amcv_flag,
FIXED_QUANTITY = l_def_kln_rec.fixed_quantity,
USAGE_DURATION = l_def_kln_rec.usage_duration,
USAGE_PERIOD = l_def_kln_rec.usage_period,
LEVEL_YN = l_def_kln_rec.level_yn,
USAGE_TYPE = l_def_kln_rec.usage_type,
UOM_QUANTIFIED = l_def_kln_rec.uom_quantified,
BASE_READING = l_def_kln_rec.base_reading,
BILLING_SCHEDULE_TYPE = l_def_kln_rec.billing_schedule_type,
FULL_CREDIT = l_def_kln_rec.full_credit,
LOCKED_PRICE_LIST_LINE_ID = l_def_kln_rec.locked_price_list_line_id,
BREAK_UOM = l_def_kln_rec.break_uom,
PRORATE = l_def_kln_rec.prorate,
COVERAGE_TYPE = l_def_kln_rec.coverage_type,
EXCEPTION_COV_ID = l_def_kln_rec.exception_cov_id,
LIMIT_UOM_QUANTIFIED = l_def_kln_rec.limit_uom_quantified,
DISCOUNT_AMOUNT = l_def_kln_rec.discount_amount,
DISCOUNT_PERCENT = l_def_kln_rec.discount_percent,
OFFSET_DURATION = l_def_kln_rec.offset_duration,
OFFSET_PERIOD = l_def_kln_rec.offset_period,
INCIDENT_SEVERITY_ID = l_def_kln_rec.incident_severity_id,
PDF_ID = l_def_kln_rec.pdf_id,
WORK_THRU_YN = l_def_kln_rec.work_thru_yn,
REACT_ACTIVE_YN = l_def_kln_rec.react_active_yn,
TRANSFER_OPTION = l_def_kln_rec.transfer_option,
PROD_UPGRADE_YN = l_def_kln_rec.prod_upgrade_yn,
INHERITANCE_TYPE = l_def_kln_rec.inheritance_type,
PM_PROGRAM_ID = l_def_kln_rec.pm_program_id,
PM_CONF_REQ_YN = l_def_kln_rec.pm_conf_req_yn,
PM_SCH_EXISTS_YN = l_def_kln_rec.pm_sch_exists_yn,
ALLOW_BT_DISCOUNT = l_def_kln_rec.allow_bt_discount,
APPLY_DEFAULT_TIMEZONE = l_def_kln_rec.apply_default_timezone,
SYNC_DATE_INSTALL = l_def_kln_rec.sync_date_install,
OBJECT_VERSION_NUMBER = l_def_kln_rec.object_version_number,
REQUEST_ID = l_def_kln_rec.request_id,
--CREATED_BY = l_def_kln_rec.created_by, /*Commented for bug 14499300*/
--CREATION_DATE = l_def_kln_rec.creation_date,
LAST_UPDATED_BY = l_def_kln_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_kln_rec.last_update_date,
LAST_UPDATE_LOGIN = l_def_kln_rec.last_update_login,
-- R12 Data Model Changes 4485150 Start
TRXN_EXTENSION_ID = l_def_kln_rec.trxn_extension_id,
TAX_CLASSIFICATION_CODE = l_def_kln_rec.tax_classification_code,
EXEMPT_CERTIFICATE_NUMBER = l_def_kln_rec.exempt_certificate_number,
EXEMPT_REASON_CODE = l_def_kln_rec.exempt_reason_code,
COVERAGE_ID = l_def_kln_rec.coverage_id,
STANDARD_COV_YN = l_def_kln_rec.standard_cov_yn,
ORIG_SYSTEM_ID1 = l_def_kln_rec.orig_system_id1,
ORIG_SYSTEM_REFERENCE1 = l_def_kln_rec.orig_system_reference1,
ORIG_SYSTEM_SOURCE_CODE = l_def_kln_rec.orig_system_source_code,
-- R12 Data Model Changes 4485150 End
--skuchima VRM ER
revenue_Impact_Date = l_def_kln_rec.revenue_Impact_Date,
counter_value_id = l_def_kln_rec.counter_value_id
WHERE ID = l_def_kln_rec.id;
END update_row;
PROCEDURE update_row(
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_klt_rec IN klt_rec_type,
x_klt_rec OUT NOCOPY klt_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
IF (x_klt_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_klt_rec.last_updated_by := l_klt_rec.last_updated_by;
IF (x_klt_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_klt_rec.last_update_date := l_klt_rec.last_update_date;
IF (x_klt_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_klt_rec.last_update_login := l_klt_rec.last_update_login;
UPDATE OKS_K_LINES_TL
SET INVOICE_TEXT = l_def_klt_rec.invoice_text,
IB_TRX_DETAILS = l_def_klt_rec.ib_trx_details,
STATUS_TEXT = l_def_klt_rec.status_text,
REACT_TIME_NAME = l_def_klt_rec.react_time_name,
--CREATED_BY = l_def_klt_rec.created_by, /*Commented for bug 14499300*/
--CREATION_DATE = l_def_klt_rec.creation_date,
LAST_UPDATED_BY = l_def_klt_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_klt_rec.last_update_date,
LAST_UPDATE_LOGIN = l_def_klt_rec.last_update_login
WHERE ID = l_def_klt_rec.id
--npalepu modified on 15-FEB-2007 for bug # 5691160
/* AND SOURCE_LANG = USERENV('LANG'); */
UPDATE OKS_K_LINES_TL
SET SFWT_FLAG = 'Y'
WHERE ID = l_def_klt_rec.id
AND SOURCE_LANG <> USERENV('LANG');
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_klnv_rec IN klnv_rec_type,
x_klnv_rec OUT NOCOPY klnv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
l_klnv_rec.LAST_UPDATE_DATE := SYSDATE;
l_klnv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_klnv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
IF (x_klnv_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_klnv_rec.last_updated_by := l_db_klnv_rec.last_updated_by;
IF (x_klnv_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_klnv_rec.last_update_date := l_db_klnv_rec.last_update_date;
IF (x_klnv_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_klnv_rec.last_update_login := l_db_klnv_rec.last_update_login;
update_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_kln_rec,
lx_kln_rec
);
update_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_klt_rec,
lx_klt_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_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) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
update_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_error_rec.error_type,
x_msg_count => l_error_rec.msg_count,
x_msg_data => l_error_rec.msg_data,
p_klnv_rec => p_klnv_tbl(i),
x_klnv_rec => x_klnv_tbl(i));
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_klnv_tbl IN klnv_tbl_type,
x_klnv_tbl OUT NOCOPY klnv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
update_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
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 => l_error_tbl);
END update_row;
PROCEDURE delete_row(
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_kln_rec IN kln_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
DELETE FROM OKS_K_LINES_B
WHERE ID = p_kln_rec.id;
END delete_row;
PROCEDURE delete_row(
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_klt_rec IN klt_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
DELETE FROM OKS_K_LINES_TL
WHERE ID = p_klt_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_klnv_rec IN klnv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
delete_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_klt_rec
);
delete_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_kln_rec
);
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_klnv_tbl IN klnv_tbl_type,
px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
delete_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_error_rec.error_type,
x_msg_count => l_error_rec.msg_count,
x_msg_data => l_error_rec.msg_data,
p_klnv_rec => p_klnv_tbl(i));
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_klnv_tbl IN klnv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
delete_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
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 => l_error_tbl);
END delete_row;