The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out nocopy VARCHAR2,
X_DOCUMENT_TYPE in VARCHAR2,
X_CONTRACT_TERMS_FUNCTION in VARCHAR2,
X_ENABLE_DELIVERABLES_YN in VARCHAR2,
X_NOTIFICATION_HEADER_FUNCTION in VARCHAR2,
X_INTENT in VARCHAR2,
X_PROVISION_ALLOWED_YN in VARCHAR2,
X_DOCUMENT_TYPE_CLASS in VARCHAR2,
X_DOC_PREVIEW_FUNCTION in VARCHAR2,
X_DOC_VIEW_DETAILS_FUNCTION in VARCHAR2,
X_SHOW_EXT_VAR_FLAG in VARCHAR2,
X_ENABLE_AMEND_SUMMARY_YN in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_NAME in VARCHAR2,
X_DOC_VERSION_VIEW in VARCHAR2,
X_ENABLE_ATTACHMENTS_YN in VARCHAR2,
X_EXTERNAL_USERLIST_PROC in VARCHAR2,
X_SHOW_IN_LOV_YN in VARCHAR2,
X_SHOW_PREVIEW_BTN_YN in VARCHAR2,
X_TARGET_RESPONSE_DOC_TYPE in VARCHAR2,
X_SHOW_IN_WORKBENCH_FLAG in VARCHAR2,
X_DISABLE_AMEND_YN in VARCHAR2,
X_SHOW_AUTH_PARTY_FLAG in VARCHAR2,
X_ENABLE_ATTACHED_DOC_FLAG in VARCHAR2,
X_VARIABLE_RESOLUTION_AM in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from OKC_BUS_DOC_TYPES_B
where DOCUMENT_TYPE = X_DOCUMENT_TYPE
;
insert into OKC_BUS_DOC_TYPES_B (
CONTRACT_TERMS_FUNCTION,
ENABLE_DELIVERABLES_YN,
NOTIFICATION_HEADER_FUNCTION,
DOCUMENT_TYPE,
INTENT,
PROVISION_ALLOWED_YN,
DOCUMENT_TYPE_CLASS,
DOC_PREVIEW_FUNCTION,
DOC_VIEW_DETAILS_FUNCTION,
SHOW_EXT_VAR_FLAG,
ENABLE_AMEND_SUMMARY_YN,
DOC_VERSION_VIEW,
ENABLE_ATTACHMENTS_YN,
EXTERNAL_USERLIST_PROC,
SHOW_IN_LOV_YN,
SHOW_PREVIEW_BTN_YN ,
TARGET_RESPONSE_DOC_TYPE ,
SHOW_IN_WORKBENCH_FLAG ,
DISABLE_AMEND_YN ,
SHOW_AUTH_PARTY_FLAG,
ENABLE_ATTACHED_DOC_FLAG,
VARIABLE_RESOLUTION_AM,
APPLICATION_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_CONTRACT_TERMS_FUNCTION,
X_ENABLE_DELIVERABLES_YN,
X_NOTIFICATION_HEADER_FUNCTION,
X_DOCUMENT_TYPE,
X_INTENT,
X_PROVISION_ALLOWED_YN,
X_DOCUMENT_TYPE_CLASS,
X_DOC_PREVIEW_FUNCTION,
X_DOC_VIEW_DETAILS_FUNCTION,
X_SHOW_EXT_VAR_FLAG,
X_ENABLE_AMEND_SUMMARY_YN,
X_DOC_VERSION_VIEW,
X_ENABLE_ATTACHMENTS_YN,
X_EXTERNAL_USERLIST_PROC,
X_SHOW_IN_LOV_YN,
X_SHOW_PREVIEW_BTN_YN ,
X_TARGET_RESPONSE_DOC_TYPE ,
X_SHOW_IN_WORKBENCH_FLAG ,
X_DISABLE_AMEND_YN ,
X_SHOW_AUTH_PARTY_FLAG,
X_ENABLE_ATTACHED_DOC_FLAG,
X_VARIABLE_RESOLUTION_AM,
X_APPLICATION_ID,
X_OBJECT_VERSION_NUMBER,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into OKC_BUS_DOC_TYPES_TL (
LAST_UPDATE_LOGIN,
DOCUMENT_TYPE,
SOURCE_LANG,
NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LANGUAGE
) select
X_LAST_UPDATE_LOGIN,
X_DOCUMENT_TYPE,
userenv('LANG'),
X_NAME,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
L.LANGUAGE_CODE
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from OKC_BUS_DOC_TYPES_TL T
where T.DOCUMENT_TYPE = X_DOCUMENT_TYPE
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
cursor c is select
CONTRACT_TERMS_FUNCTION,
ENABLE_DELIVERABLES_YN,
NOTIFICATION_HEADER_FUNCTION,
INTENT,
PROVISION_ALLOWED_YN,
DOCUMENT_TYPE_CLASS,
DOC_PREVIEW_FUNCTION,
DOC_VIEW_DETAILS_FUNCTION,
SHOW_EXT_VAR_FLAG,
ENABLE_AMEND_SUMMARY_YN,
DOC_VERSION_VIEW,
ENABLE_ATTACHMENTS_YN,
EXTERNAL_USERLIST_PROC,
SHOW_IN_LOV_YN ,
SHOW_PREVIEW_BTN_YN,
TARGET_RESPONSE_DOC_TYPE,
SHOW_IN_WORKBENCH_FLAG,
DISABLE_AMEND_YN,
SHOW_AUTH_PARTY_FLAG,
ENABLE_ATTACHED_DOC_FLAG,
VARIABLE_RESOLUTION_AM,
APPLICATION_ID,
OBJECT_VERSION_NUMBER
from OKC_BUS_DOC_TYPES_B
where DOCUMENT_TYPE = X_DOCUMENT_TYPE
for update of DOCUMENT_TYPE nowait;
cursor c1 is select
NAME,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from OKC_BUS_DOC_TYPES_TL
where DOCUMENT_TYPE = X_DOCUMENT_TYPE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of DOCUMENT_TYPE nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_DOCUMENT_TYPE in VARCHAR2,
X_CONTRACT_TERMS_FUNCTION in VARCHAR2,
X_ENABLE_DELIVERABLES_YN in VARCHAR2,
X_NOTIFICATION_HEADER_FUNCTION in VARCHAR2,
X_INTENT in VARCHAR2,
X_PROVISION_ALLOWED_YN in VARCHAR2,
X_DOCUMENT_TYPE_CLASS in VARCHAR2,
X_DOC_PREVIEW_FUNCTION in VARCHAR2,
X_DOC_VIEW_DETAILS_FUNCTION in VARCHAR2,
X_SHOW_EXT_VAR_FLAG in VARCHAR2,
X_ENABLE_AMEND_SUMMARY_YN in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_NAME in VARCHAR2,
X_DOC_VERSION_VIEW in VARCHAR2,
X_ENABLE_ATTACHMENTS_YN in VARCHAR2,
X_EXTERNAL_USERLIST_PROC in VARCHAR2,
X_SHOW_IN_LOV_YN in VARCHAR2,
X_SHOW_PREVIEW_BTN_YN in VARCHAR2,
X_TARGET_RESPONSE_DOC_TYPE in VARCHAR2,
X_SHOW_IN_WORKBENCH_FLAG in VARCHAR2,
X_DISABLE_AMEND_YN in VARCHAR2,
X_SHOW_AUTH_PARTY_FLAG in VARCHAR2,
X_ENABLE_ATTACHED_DOC_FLAG in VARCHAR2,
X_VARIABLE_RESOLUTION_AM in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update OKC_BUS_DOC_TYPES_B set
CONTRACT_TERMS_FUNCTION = X_CONTRACT_TERMS_FUNCTION,
ENABLE_DELIVERABLES_YN = X_ENABLE_DELIVERABLES_YN,
NOTIFICATION_HEADER_FUNCTION = X_NOTIFICATION_HEADER_FUNCTION,
INTENT = X_INTENT,
PROVISION_ALLOWED_YN = X_PROVISION_ALLOWED_YN,
DOCUMENT_TYPE_CLASS = X_DOCUMENT_TYPE_CLASS,
DOC_PREVIEW_FUNCTION = X_DOC_PREVIEW_FUNCTION,
DOC_VIEW_DETAILS_FUNCTION = X_DOC_VIEW_DETAILS_FUNCTION,
SHOW_EXT_VAR_FLAG = X_SHOW_EXT_VAR_FLAG,
ENABLE_AMEND_SUMMARY_YN = X_ENABLE_AMEND_SUMMARY_YN,
DOC_VERSION_VIEW = X_DOC_VERSION_VIEW,
ENABLE_ATTACHMENTS_YN = X_ENABLE_ATTACHMENTS_YN,
EXTERNAL_USERLIST_PROC = X_EXTERNAL_USERLIST_PROC,
SHOW_IN_LOV_YN = X_SHOW_IN_LOV_YN,
SHOW_PREVIEW_BTN_YN = X_SHOW_PREVIEW_BTN_YN ,
TARGET_RESPONSE_DOC_TYPE = X_TARGET_RESPONSE_DOC_TYPE ,
SHOW_IN_WORKBENCH_FLAG = X_SHOW_IN_WORKBENCH_FLAG ,
SHOW_AUTH_PARTY_FLAG = X_SHOW_AUTH_PARTY_FLAG,
ENABLE_ATTACHED_DOC_FLAG = X_ENABLE_ATTACHED_DOC_FLAG,
VARIABLE_RESOLUTION_AM = X_VARIABLE_RESOLUTION_AM,
DISABLE_AMEND_YN = X_DISABLE_AMEND_YN ,
APPLICATION_ID = X_APPLICATION_ID,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where DOCUMENT_TYPE = X_DOCUMENT_TYPE;
update OKC_BUS_DOC_TYPES_TL set
NAME = X_NAME,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG= userenv('LANG')
where DOCUMENT_TYPE = X_DOCUMENT_TYPE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure DELETE_ROW (
X_DOCUMENT_TYPE in VARCHAR2
) is
begin
-- skekkar
-- delete from okc_doc_qa_lists
delete from okc_doc_qa_lists
where DOCUMENT_TYPE = X_DOCUMENT_TYPE;
delete from OKC_BUS_DOC_TYPES_TL
where DOCUMENT_TYPE = X_DOCUMENT_TYPE;
delete from OKC_BUS_DOC_TYPES_B
where DOCUMENT_TYPE = X_DOCUMENT_TYPE;
end DELETE_ROW;
delete from OKC_BUS_DOC_TYPES_TL T
where not exists
(select NULL
from OKC_BUS_DOC_TYPES_B B
where B.DOCUMENT_TYPE = T.DOCUMENT_TYPE
);
update OKC_BUS_DOC_TYPES_TL T set (
NAME
) = (select
B.NAME
from OKC_BUS_DOC_TYPES_TL B
where B.DOCUMENT_TYPE = T.DOCUMENT_TYPE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.DOCUMENT_TYPE,
T.LANGUAGE
) in (select
SUBT.DOCUMENT_TYPE,
SUBT.LANGUAGE
from OKC_BUS_DOC_TYPES_TL SUBB, OKC_BUS_DOC_TYPES_TL SUBT
where SUBB.DOCUMENT_TYPE = SUBT.DOCUMENT_TYPE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.NAME <> SUBT.NAME
));
insert into OKC_BUS_DOC_TYPES_TL (
LAST_UPDATE_LOGIN,
DOCUMENT_TYPE,
SOURCE_LANG,
NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LANGUAGE
) select
B.LAST_UPDATE_LOGIN,
B.DOCUMENT_TYPE,
B.SOURCE_LANG,
B.NAME,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
L.LANGUAGE_CODE
from OKC_BUS_DOC_TYPES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from OKC_BUS_DOC_TYPES_TL T
where T.DOCUMENT_TYPE = B.DOCUMENT_TYPE
and T.LANGUAGE = L.LANGUAGE_CODE);
insert into OKC_DOC_QA_LISTS(
QA_CODE,
DOCUMENT_TYPE,
SEVERITY_FLAG,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
ENABLE_QA_YN
)select
lkp.lookup_code,
p_document_type,
'W',
1,
1,
sysdate,
1,
sysdate,
'Y'
from fnd_lookups lkp
where lkp.lookup_type = 'OKC_TERM_QA_LIST'
and lkp.lookup_code NOT IN ( select qa_code from okc_doc_qa_lists where document_type = p_document_type );