[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO CS_UPG_ERRORS(Orig_System_Reference,
Orig_System_Reference_Id,
Orig_System_Ref_Id_Upper,
Upgrade_DateTime,
Error_Message)
VALUES (P_Original_System_Reference,
P_Original_System_Reference_Id,
P_Original_System_Ref_Id_Upper,
P_DateTime,
P_Error_Message);
PROCEDURE Insert_Time_code_units IS
l_api_version CONSTANT NUMBER := 1.0;
SELECT 'y' FROM okc_time_code_units_v
WHERE uom_code = p_uom_code
AND tce_code = p_tce_code;
l_tcuv_rec_in.last_updated_by := -1;
l_tcuv_rec_in.last_update_date := SYSDATE;
l_tcuv_rec_in.last_update_login := -1;
Log_Errors('INSERT_TIME_CODE_UNITS',
NULL,
NULL,
SYSDATE,
l_Error_Message);
Log_Errors('INSERT_TIME_CODE_UNITS',
NULL,
NULL,
SYSDATE,
l_Error_Message);
Log_Errors('INSERT_TIME_CODE_UNITS',
NULL,
NULL,
SYSDATE,
l_Error_Message);
Log_Errors('INSERT_TIME_CODE_UNITS',
NULL,
NULL,
SYSDATE,
l_Error_Message);
Log_Errors('INSERT_TIME_CODE_UNITS',
NULL,
NULL,
SYSDATE,
l_Error_Message);
Log_Errors('INSERT_TIME_CODE_UNITS',
NULL,
NULL,
SYSDATE,
l_Error_Message);
RAISE_APPLICATION_ERROR(-20000,'Error While running Insert_Time_Code_Units for: '||l_error_message);
END Insert_Time_code_units;
SELECT count(*)
INTO l_busproc_cnt
FROM cs_business_processes
WHERE UPPER(name) = UPPER('SERVICE_CONTRACTS_UPGRADE_BP');
SELECT max(BUSINESS_PROCESS_ID)+1
INTO l_busproc_id
FROM cs_business_processes;
INSERT into cs_business_processes(
BUSINESS_PROCESS_ID ,
ORDER_TYPE_ID ,
NAME ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
DESCRIPTION ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
SERVICE_REQUEST_FLAG ,
DEPOT_REPAIR_FLAG ,
FIELD_SERVICE_FLAG ,
CONTRACTS_FLAG ,
STAND_ALONE_FLAG ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CONTEXT )
values
(l_busproc_id ,
NULL ,
'SERVICE_CONTRACTS_UPGRADE_BP',
SYSDATE ,
0, --LAST_UPDATED_BY ??
SYSDATE ,
-1 , --CREATED_BY ??
0, --LAST_UPDATE_LOGIN ??
'Business Process for Upgrade',
To_Date('01/01/1900','DD/MM/YYYY'),
NULL , --END_DATE_ACTIVE ??
'Y' ,
'Y' ,
'Y' ,
'Y' ,
NULL, -- STAND_ALONE_FLAG ??
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL);
l_txnb_rec_in.last_update_date := sysdate;
l_txnb_rec_in.last_updated_by := 0;
l_txnb_rec_in.last_update_login :=0;
CS_TXNBTYPE_PVT.INSERT_ROW(
p_api_version =>l_api_version,
p_init_msg_list =>l_init_msg_list,
p_validation_level =>l_validation_level,
p_commit =>l_commit,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_txnbtype_rec =>l_txnb_rec_in,
x_txn_billing_type_id =>l_txn_billing_type_id,
x_object_version_number =>l_object_version_number);
SELECT business_process_id
FROM CS_BUSINESS_PROCESSES
WHERE NAME = 'SERVICE_CONTRACTS_UPGRADE_BP';
CS_BUS_PROCESS_TXNS_PKG.INSERT_ROW(
X_ROWID => L_ROWID,
X_BUSINESS_PROCESS_ID => L_BUSINESS_PROCESS_ID,
X_TRANSACTION_TYPE_ID => L_TRANSACTION_TYPE_ID,
X_START_DATE_ACTIVE => L_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => L_END_DATE_ACTIVE,
X_ATTRIBUTE1 => L_ATTRIBUTE1,
X_ATTRIBUTE2 => L_ATTRIBUTE2,
X_ATTRIBUTE3 => L_ATTRIBUTE3,
X_ATTRIBUTE4 => L_ATTRIBUTE4,
X_ATTRIBUTE5 => L_ATTRIBUTE5,
X_ATTRIBUTE6 => L_ATTRIBUTE6,
X_ATTRIBUTE7 => L_ATTRIBUTE7,
X_ATTRIBUTE8 => L_ATTRIBUTE8,
X_ATTRIBUTE9 => L_ATTRIBUTE9,
X_ATTRIBUTE10 => L_ATTRIBUTE10,
X_ATTRIBUTE11 => L_ATTRIBUTE11,
X_ATTRIBUTE12 => L_ATTRIBUTE12,
X_ATTRIBUTE13 => L_ATTRIBUTE13,
X_ATTRIBUTE14 => L_ATTRIBUTE14,
X_ATTRIBUTE15 => L_ATTRIBUTE15,
X_CONTEXT => L_CONTEXT,
X_MODE => L_MODE);
SELECT count(*) from CS_TRANSACTION_TYPES
WHERE NAME='SERVICE_CONTRACTS_UPGRADE_TXN';
SELECT max(TRANSACTION_TYPE_ID)+1
FROM CS_TRANSACTION_TYPES;
l_LAST_UPDATE_DATE date ;
l_LAST_UPDATED_BY number ;
l_LAST_UPDATE_LOGIN number ;
l_LAST_UPDATE_DATE := sysdate;
l_LAST_UPDATED_BY :=1;
l_LAST_UPDATE_LOGIN :=0;
CS_TRANSACTION_TYPES_PKG.INSERT_ROW (
X_ROWID =>L_ROWID ,
X_TRANSACTION_TYPE_ID => l_TXNTYPE_ID,
X_SEEDED_FLAG => L_SEEDED_FLAG ,
X_REVISION_FLAG => L_REVISION_FLAG ,
X_END_DATE_ACTIVE => L_END_DATE_ACTIVE ,
X_START_DATE_ACTIVE => L_START_DATE_ACTIVE ,
X_ATTRIBUTE1 =>l_ATTRIBUTE1,
X_ATTRIBUTE2 =>l_ATTRIBUTE2,
X_ATTRIBUTE3 =>l_ATTRIBUTE3,
X_ATTRIBUTE4 =>l_ATTRIBUTE4,
X_ATTRIBUTE5 =>l_ATTRIBUTE5,
X_ATTRIBUTE6 =>l_ATTRIBUTE6,
X_ATTRIBUTE7 =>l_ATTRIBUTE7,
X_ATTRIBUTE8 =>l_ATTRIBUTE8,
X_ATTRIBUTE9 =>l_ATTRIBUTE9,
X_ATTRIBUTE10 =>l_ATTRIBUTE10,
X_CONTEXT => l_CONTEXT,
X_INSTALLED_CP_STATUS_ID => l_INSTALLED_CP_STATUS_ID,
X_ATTRIBUTE11 =>l_ATTRIBUTE11,
X_ATTRIBUTE12 =>l_ATTRIBUTE12,
X_ATTRIBUTE13 =>l_ATTRIBUTE13,
X_ATTRIBUTE14 =>l_ATTRIBUTE14,
X_ATTRIBUTE15 =>l_ATTRIBUTE15,
X_INSTALLED_STATUS_CODE => l_INSTALLED_STATUS_CODE,
X_INSTALLED_CP_RETURN_REQUIRED =>l_INSTALLED_CP_RETURN_REQUIRED,
X_NO_CHARGE_FLAG =>l_NO_CHARGE_FLAG ,
X_DEPOT_REPAIR_FLAG =>l_DEPOT_REPAIR_FLAG,
X_NEW_CP_STATUS_ID =>l_NEW_CP_STATUS_ID,
X_NEW_CP_STATUS_CODE =>L_NEW_CP_STATUS_CODE,
X_TRANSFER_SERVICE =>L_TRANSFER_SERVICE ,
X_NEW_CP_RETURN_REQUIRED =>L_NEW_CP_RETURN_REQUIRED,
X_NAME =>L_NAME ,
X_DESCRIPTION =>L_DESCRIPTION ,
X_CREATION_DATE =>L_CREATION_DATE,
X_CREATED_BY =>L_CREATED_BY ,
X_LAST_UPDATE_DATE =>L_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY =>L_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN =>L_LAST_UPDATE_LOGIN ,
X_MOVE_COUNTERS_FLAG =>L_MOVE_COUNTERS_FLAG,
X_OBJECT_VERSION_NUMBER=>L_OBJECT_VERSION_NUMBER);
select 'Y' from cs_cp_services_all
where rownum < 2;
Insert_Time_code_units;
PROCEDURE Update_status IS
Cursor Cur_hdr_status IS
select old_stat.name old_status
from okc_k_headers_v kh ,
cs_contracts_all csc,
cs_contract_statuses old_stat
where csc.contract_status_id = old_stat.contract_status_id
and csc.contract_id = kh.id
and upper(kh.sts_code) <> upper(old_stat.name)
and exists (select b.code from okc_statuses_v b
where upper(old_stat.name) = upper(b.code));
select old_stat.name old_status
from okc_k_lines_v kl ,
cs_cp_services_all ccs,
cs_contract_statuses old_stat
where ccs.contract_line_status_id = old_stat.contract_status_id
and ccs.cp_service_id = kl.id
and upper(kl.sts_code) <> upper(old_stat.name)
and exists (select b.code from okc_statuses_v b
where upper(old_stat.name) = upper(b.code));
UPDATE OKC_K_HEADERS_B
SET STS_CODE = UPPER(status_rec.old_status)
WHERE id in (select cc.contract_id
FROM cs_contracts_all cc,
cs_contract_statuses stat
WHERE cc.contract_status_id = stat.contract_status_id
AND stat.name = status_rec.old_status);
UPDATE OKC_K_LINES_B
SET STS_CODE = UPPER(status_rec.old_status)
WHERE id in (select cs.cp_service_id
FROM cs_cp_services_all cs,
cs_contract_statuses stat
WHERE cs.contract_line_status_id = stat.contract_status_id
AND stat.name = status_rec.old_status);
END Update_status;
PROCEDURE Update_time_zone IS
CURSOR Cur_tz IS
SELECT distinct dnz_chr_id
FROM CS_COVERAGE_TXN_GROUPS ctxg,
OKC_K_LINES_B cle
WHERE cle.upg_Orig_System_Ref='CS_COVERAGE_TXN_GROUPS'
AND cle.upg_Orig_System_Ref_Id = ctxg.Coverage_txn_Group_id
AND ctxg.Time_Zone_Id IS NULL;
UPDATE OKS_COVERAGE_TIMEZONES
SET TIMEZONE_ID = l_cov_timezone_id --tz_rec.timezone_id
WHERE timezone_id = nvl(FND_PROFILE.value('CS_UPG_CONTRACTS_TIMEZONE'),47)
AND dnz_chr_id IN --= tz_rec.chr_id
( SELECT distinct dnz_chr_id
FROM CS_COVERAGE_TXN_GROUPS ctxg,
OKC_K_LINES_B cle
WHERE cle.upg_Orig_System_Ref='CS_COVERAGE_TXN_GROUPS'
AND cle.upg_Orig_System_Ref_Id = ctxg.Coverage_txn_Group_id
AND ctxg.Time_Zone_Id IS NULL);
END Update_time_zone;
select oracle_username
from fnd_oracle_userid
where read_only_flag = 'U';
Select index_name
From all_indexes
Where index_name like 'OKC_K_LINES_B_UPG'
and owner = c_owner;
Select index_name
From all_indexes
Where index_name like 'COV_SCH_ID_N10'
and owner = c_owner;