[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO OKC_CLS_CLAUSE_BUFFER_T(
BUFFER_SEQ_ID,
CLAUSE_SEQUENCE,
FULLTEXT_YN,
PRESECRIBED_YN,
REQUIRED_YN,
CLAUSE_CLS_ID,
CLAUSE_REF_YN,
CLAUSE_PROVISION_YN,
CLAUSE_NUMBER,
REGULATION_ID,
REGULATION_NAME,
REGULATION_TITLE,
REGULATION_URL,
SECTION_ID,
SECTION_NAME,
SECTION_TITLE,
ALTERNATE_OR_DEVIATION,
VARIATION_NAME,
REVISION_ID,
REVISION_FILLINS_YN,
REVISION_EDITABLE_YN,
REVISION_TITLE,
CLAUSE_TEXT,
EFF_START_DATE,
EFF_END_DATE,
SERIAL_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
(SELECT OKC_CLS_CLAUSE_BUFFER_T_S.NEXTVAL,
CLAUSE_SEQUENCE,
decode(FULLTEXT_YN,'true','Y','false','N',null),
decode(PRESECRIBED_YN,'true','Y','false','N',null),
decode(REQUIRED_YN,'true','Y','false','N',null),
CLAUSE_CLS_ID,
decode(CLAUSE_REF_YN,'true','Y','false','N',null),
decode(CLAUSE_PROVISION_YN,'true','Y','false','N',null),
CLAUSE_NUMBER,
REGULATION_ID,
REGULATION_NAME,
REGULATION_TITLE,
REGULATION_URL,
SECTION_ID,
SECTION_NAME,
SECTION_TITLE,
ALTERNATE_OR_DEVIATION,
VARIATION_NAME,
REVISION_ID,
decode(REVISION_FILLINS_YN,'true','Y','false','N',null),
decode(REVISION_EDITABLE_YN,'true','Y','false','N',null),
REVISION_TITLE,
CLAUSE_TEXT,
to_date(EFF_START_DATE,'mm/dd/yyyy'),
to_date(EFF_END_DATE,'mm/dd/yyyy'),
ROWNUM,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
FROM xmltable('//ProcurementDocumentClause' passing xmltype(p_data)
COLUMNS
"CLAUSE_SEQUENCE" NUMBER PATH '@id',
"FULLTEXT_YN" VARCHAR2(10) PATH '@fullText',
"PRESECRIBED_YN" VARCHAR2(10) PATH '@prescribed',
"REQUIRED_YN" VARCHAR2(10) PATH '@required',
"CLAUSE_CLS_ID" NUMBER PATH 'Clause/@id',
"CLAUSE_REF_YN" VARCHAR2(10) PATH 'Clause/@referenceAllowed',
"CLAUSE_PROVISION_YN" VARCHAR2(10) PATH 'Clause/@provision',
"CLAUSE_NUMBER" VARCHAR2(50) PATH 'Clause/Number',
"REGULATION_ID" NUMBER PATH 'Clause/Regulation/@id',
"REGULATION_NAME" VARCHAR2(200) PATH 'Clause/Regulation/Name',
"REGULATION_TITLE" VARCHAR2(200) PATH 'Clause/Regulation/Title',
"REGULATION_URL" VARCHAR2(250) PATH 'Clause/Regulation/Url',
"SECTION_ID" NUMBER PATH 'Clause/Section/@id',
"SECTION_NAME" VARCHAR2(100) PATH 'Clause/Section/Name',
"SECTION_TITLE" VARCHAR2(100) PATH 'Clause/Section/Title',
"ALTERNATE_OR_DEVIATION" VARCHAR2(100) PATH 'Clause/ClauseVariation/AlternateOrDeviation',
"VARIATION_NAME" VARCHAR2(200) PATH 'Clause/ClauseVariation/VariationName',
"REVISION_ID" NUMBER PATH 'Clause/Revision/@id',
"REVISION_FILLINS_YN" VARCHAR2(10) PATH 'Clause/Revision/@hasFillins',
"REVISION_EDITABLE_YN" VARCHAR2(10) PATH 'Clause/Revision/@isEditable',
"REVISION_TITLE" VARCHAR2(200) PATH 'Clause/Revision/Title',
"CLAUSE_TEXT" CLOB PATH 'Clause/Revision/Body',
"EFF_START_DATE" varchar2(50) PATH 'Clause/Revision/EffectiveStartDate',
"EFF_END_DATE" varchar2(50) PATH 'Clause/Revision/EffectiveEndDate'
) as op);
DELETE FROM OKC_CLS_CLAUSE_BUFFER_T
WHERE token = p_token;
insert into OKC_CLS_CLAUSE_BUFFER_T(
BUFFER_SEQ_ID,
CLAUSE_SEQUENCE,
FULLTEXT_YN,
PRESECRIBED_YN,
REQUIRED_YN,
CLAUSE_CLS_ID,
CLAUSE_REF_YN,
CLAUSE_PROVISION_YN,
CLAUSE_NUMBER,
SECTION_ID,
SECTION_NAME,
SECTION_TITLE,
ALTERNATE_OR_DEVIATION,
VARIATION_NAME,
REVISION_ID,
REVISION_FILLINS_YN,
REVISION_EDITABLE_YN,
REVISION_TITLE,
TOKEN,
SERIAL_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
(SELECT OKC_CLS_CLAUSE_BUFFER_T_S.NEXTVAL,
ROWNUM,
NULL,
--CLAUSE_SEQUENCE,
--decode(FULLTEXT_YN,'true','Y','false','N',null),
decode(PRESECRIBED_YN,'true','Y','false','N',null),
decode(REQUIRED_YN,'true','Y','false','N',null),
CLAUSE_CLS_ID,
decode(CLAUSE_REF_YN,'true','Y','false','N',null),
decode(CLAUSE_PROVISION_YN,'true','Y','false','N',null),
CLAUSE_NUMBER,
SECTION_ID,
SECTION_NAME,
SECTION_TITLE,
ALTERNATE_OR_DEVIATION,
VARIATION_NAME,
REVISION_ID,
decode(REVISION_FILLINS_YN,'true','Y','false','N',null),
decode(REVISION_EDITABLE_YN,'true','Y','false','N',null),
REVISION_TITLE,
Nvl(p_token,''),
ROWNUM,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
FROM xmltable('//Clause' passing xmltype(p_data)
COLUMNS
-- "CLAUSE_SEQUENCE" NUMBER PATH '@id',
-- "FULLTEXT_YN" VARCHAR2(10) PATH '@fullText',
"PRESECRIBED_YN" VARCHAR2(10) PATH '@prescribed',
"REQUIRED_YN" VARCHAR2(10) PATH '@required',
"CLAUSE_CLS_ID" NUMBER PATH '@id',
"CLAUSE_REF_YN" VARCHAR2(10) PATH '@referenceAllowed',
"CLAUSE_PROVISION_YN" VARCHAR2(10) PATH '@provision',
"CLAUSE_NUMBER" VARCHAR2(50) PATH 'Number',
"SECTION_ID" NUMBER PATH 'Section/@id',
"SECTION_NAME" VARCHAR2(100) PATH 'Section',
"SECTION_TITLE" VARCHAR2(100) PATH 'Section',
"ALTERNATE_OR_DEVIATION" VARCHAR2(100) PATH 'ClauseVariation/AlternateOrDeviation',
"VARIATION_NAME" VARCHAR2(200) PATH 'ClauseVariation/VariationName',
"REVISION_ID" NUMBER PATH 'Revision/@id',
"REVISION_FILLINS_YN" VARCHAR2(10) PATH 'Revision/@hasFillins',
"REVISION_EDITABLE_YN" VARCHAR2(10) PATH 'Revision/@isEditable',
"REVISION_TITLE" VARCHAR2(200) PATH 'Revision/Title'
) as op);
SELECT DISTINCT SECTION_ID,
SECTION_NAME,
SECTION_TITLE
FROM OKC_CLS_CLAUSE_BUFFER_T
WHERE token = p_tok;
SELECT buf.buffer_seq_id buffer_seq_id,
art.ARTICLE_ID art_id,
ver.ARTICLE_VERSION_ID ver_id
FROM OKC_CLS_CLAUSE_BUFFER_T buf,
OKC_ARTICLES_ALL ART
,OKC_ARTICLE_VERSIONS ver
WHERE buf.token = param_token
AND art.article_number = buf.CLAUSE_NUMBER
AND art.ORIG_SYSTEM_REFERENCE_CODE = 'CLS'
AND art.ORIG_SYSTEM_REFERENCE_ID1 = buf.CLAUSE_CLS_ID
AND art.article_id = ver.article_id
AND ver.ORIG_SYSTEM_REFERENCE_CODE = 'CLS'
AND ver.ORIG_SYSTEM_REFERENCE_ID1 = buf.REVISION_ID
AND ver.ARTICLE_STATUS = 'APPROVED'
AND (art.ORG_ID = FND_GLOBAL.ORG_ID
OR (exists ( SELECT 1
FROM okc_article_ADOPTIONS ADP
WHERE adp.global_article_version_id = ver.article_version_id
AND adp.adoption_type = 'ADOPTED'
AND adp.local_org_id = FND_GLOBAL.ORG_ID
AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
)
)
);
SELECT buf.buffer_seq_id buffer_seq_id,
art.ARTICLE_ID art_id,
ver.ARTICLE_VERSION_ID ver_id
FROM OKC_CLS_CLAUSE_BUFFER_T buf,
OKC_ARTICLES_ALL ART
,OKC_ARTICLE_VERSIONS ver
WHERE buf.token = '96aebdf2d5525841ddcf5bb9097714e0'
AND art.article_number = buf.CLAUSE_NUMBER
AND art.article_id = ver.article_id
AND SYSDATE BETWEEN Nvl(ver.start_date,SYSDATE-1) AND Nvl(ver.end_date, SYSDATE+1)
AND ver.ARTICLE_STATUS = 'APPROVED'
AND (art.ORG_ID = FND_GLOBAL.ORG_ID
OR (exists ( SELECT 1
FROM okc_article_ADOPTIONS ADP
WHERE adp.global_article_version_id = ver.article_version_id
AND adp.adoption_type = 'ADOPTED'
AND adp.local_org_id = FND_GLOBAL.ORG_ID
AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
)
)
);
SELECT id
INTO l_section_id
FROM okc_sections_b
WHERE heading = l_xml_section_name
AND DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
AND ROWNUM = 1;
SELECT 'Y'
INTO l_section_present_in_lib
FROM FND_LOOKUPS
WHERE lookup_type = 'OKC_ARTICLE_SECTION'
AND lookup_code = l_xml_section_name
AND ROWNUM = 1;
UPDATE OKC_CLS_CLAUSE_BUFFER_T
SET ORIG_SECTION_ID = l_section_id,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE token = p_token
AND SECTION_ID = l_xml_section_id
AND SECTION_NAME = l_xml_section_name
AND SECTION_TITLE = l_xml_section_title;
DELETE FROM okc_k_art_variables
WHERE CAT_ID IN (SELECT id
FROM okc_k_articles_b
WHERE SOURCE_FLAG = 'C'
AND DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
);
DELETE FROM okc_k_articles_b
WHERE SOURCE_FLAG = 'C'
AND DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id ;
SELECT Max(DISPLAY_SEQUENCE)
INTO l_max_seq
FROM okc_k_articles_b
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id ;
SELECT Count(1)
INTO l_cls_clauses_in_ou_count
FROM okc_articles_all
WHERE ORIG_SYSTEM_REFERENCE_CODE = 'CLS'
AND ORG_ID = FND_GLOBAL.ORG_ID;
UPDATE OKC_CLS_CLAUSE_BUFFER_T buf
SET ORIG_ARTICLE_ID = l_art_ver_ids_tbl(i).art_id ,
ORIG_ARTICLE_VERSION_ID = l_art_ver_ids_tbl(i).ver_id,
CLAUSE_DISPLAY_SEQUENCE = (l_max_seq + (SERIAL_NUMBER * 10)),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE buffer_seq_id = l_art_ver_ids_tbl(i).seq_id ;
SELECT LISTAGG(CLAUSE_NUMBER,',') WITHIN GROUP (order BY CLAUSE_NUMBER) "err_values"
INTO l_error_values
FROM (
SELECT CASE WHEN art.ARTICLE_VERSION_ID <> buf.ORIG_ARTICLE_VERSION_ID THEN 'Y'
ELSE 'N'
END AS wrong_version_yn,
CLAUSE_NUMBER
FROM okc_k_articles_b art, OKC_CLS_CLAUSE_BUFFER_T buf
WHERE art.DOCUMENT_TYPE = p_doc_type
AND art.DOCUMENT_ID = p_doc_id
AND art.SAV_SAE_ID = buf.ORIG_ARTICLE_ID
)
WHERE wrong_version_yn = 'Y';
UPDATE OKC_CLS_CLAUSE_BUFFER_T buf
SET ORIG_ARTICLE_ID = l_art_ver_ids_tbl(i).art_id ,
ORIG_ARTICLE_VERSION_ID = l_art_ver_ids_tbl(i).ver_id,
CLAUSE_DISPLAY_SEQUENCE = (l_max_seq + (SERIAL_NUMBER * 10)),
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE buffer_seq_id = l_art_ver_ids_tbl(i).seq_id ;
SELECT Count(1)
INTO l_missing_clause_count
from OKC_CLS_CLAUSE_BUFFER_T
WHERE token = p_token
AND ORIG_ARTICLE_ID IS NULL;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Inserting the clauses.');
INSERT INTO okc_k_articles_b (
ID,
SAV_SAE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
FULLTEXT_YN,
LAST_UPDATE_LOGIN,
DOCUMENT_TYPE,
DOCUMENT_ID,
SOURCE_FLAG,
MANDATORY_YN,
SCN_ID,
DISPLAY_SEQUENCE,
ARTICLE_VERSION_ID,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_ARTICLE_ID)
(SELECT okc_k_articles_b_s.NEXTVAL,
ORIG_ARTICLE_ID,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
FULLTEXT_YN,
fnd_global.login_id,
p_doc_type,
p_doc_id,
'C',
REQUIRED_YN,
ORIG_SECTION_ID,
CLAUSE_DISPLAY_SEQUENCE,
ORIG_ARTICLE_VERSION_ID,
'CLS',
CLAUSE_CLS_ID,
ORIG_ARTICLE_ID
FROM OKC_CLS_CLAUSE_BUFFER_T buf
WHERE TOKEN = p_token
AND NOT EXISTS (SELECT 1
FROM okc_k_articles_b
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
AND SAV_SAE_ID = buf.ORIG_ARTICLE_ID
)
)
;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'okc.plsql.OKC_CLS_UTIL.'||l_api_name,'LOAD_CLAUSES_FOR_DOC: Inserting the Variables.');
INSERT INTO okc_k_art_variables (
CAT_ID,
VARIABLE_CODE,
VARIABLE_TYPE,
EXTERNAL_YN,
ATTRIBUTE_VALUE_SET_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
(SELECT art.id,
art_vbles.VARIABLE_CODE,
art_bus_vbles.VARIABLE_TYPE,
art_bus_vbles.EXTERNAL_YN,
art_bus_vbles.VALUE_SET_ID,
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
FROM okc_k_articles_b art,
OKC_ARTICLE_VARIABLES art_vbles,
OKC_BUS_VARIABLES_B art_bus_vbles
WHERE art.DOCUMENT_TYPE = p_doc_type
AND art.DOCUMENT_ID = p_doc_id
AND ART.SOURCE_FLAG = 'C'
AND art_vbles.ARTICLE_VERSION_ID = art.ARTICLE_VERSION_ID
AND art_bus_vbles.variable_code = art_vbles.VARIABLE_CODE
);
l_output_xml := '
INSERT INTO OKC_CLS_REST_CALLS_LOGS(
ID,
REQUEST_METHOD,
URL,
URI,
TOKEN,
CLAT,
CLAT_SECRET,
NONCE,
SIGNATURE,
TIME_STAMPED,
REQUEST_BODY,
AUTH_TYPE,
API_KEY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
) VALUES
(
l_req_seq_id,
p_request_method,
p_url,
p_uri,
p_token,
p_clat,
p_clat_secret,
p_nonce,
p_signature,
p_timestamp,
p_request_body,
p_auth_type,
p_api_key,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
UPDATE OKC_CLS_REST_CALLS_LOGS
SET RESPONSE_STATUS_CODE = p_response_status_code,
TRANSACTION_STATUS = l_transaction_status,
RESPONSE = p_response,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE ID = p_req_seq_id;