[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
G_LOCK_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_LOCK_RECORD_DELETED;
SELECT document_type_class
FROM okc_bus_doc_types_b
WHERE document_type = doc_type;
SELECT draft_id
FROM po_headers_draft_all
WHERE po_header_id = c_po_header_id;
EXECUTE IMMEDIATE 'select PO_UDA_PUB.get_single_attr_value(p_entity_code => :1,
pk1_value => :2,
pk2_value => :3,
p_attr_grp_int_name => :4,
p_attr_int_name => :5,
p_mode => :6) from dual'
INTO l_variable_value
USING l_entity_name,l_pk1_value,l_pk2_value,l_attr_grp,l_attr,p_uda_mode;
EXECUTE IMMEDIATE 'select PO_UDA_PUB.get_address_attr_value(p_template_id => NULL,
p_entity_code => :2,
pk1_value => :3,
pk2_value => :4,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
p_attr_grp_id => NULL,
p_attr_grp_int_name => :9,
p_attr_id => NULL,
p_attr_int_name => :11,
p_address_type => :12) from dual'
INTO l_variable_value
USING l_entity_name,l_pk1_value,l_pk2_value,p_clm_ref2,p_clm_ref3,p_clm_ref4;
query_str := 'select '||p_clm_ref3||' from PO_HEADERS_ALL where po_header_id = '||p_doc_id;
SELECT var.variable_code variable_code, --Removed USER$ to resolve Rule firing for UDV with Procedures
var.clm_ref1,var.clm_ref2,var.clm_ref3,var.clm_ref4,var.clm_ref5, var.clm_source, vart.variable_name
FROM okc_bus_variables_b var, okc_bus_variables_tl vart
WHERE var.clm_source IS NOT NULL
AND var.variable_code = vart.variable_code
AND vart.language = 'US'
AND var.variable_source = 'M'
AND var.variable_code IN
(SELECT distinct rcon.object_code variable_code -- LHS of Condition from Template rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_template_usages tuse,
okc_xprt_template_rules trule
WHERE tuse.document_id = p_doc_id
AND tuse.document_type = p_doc_type
AND tuse.template_id = trule.template_id
AND trule.rule_id = rhdr.rule_id
AND rhdr.rule_id = rcon.rule_id
AND rcon.object_type = 'VARIABLE'
AND rhdr.status_code = 'ACTIVE'
-- AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
GROUP BY rcon.object_code
UNION
SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Template rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon,
okc_template_usages tuse,
okc_xprt_template_rules trule
WHERE tuse.document_id = p_doc_id
AND tuse.document_type = p_doc_type
AND tuse.template_id = trule.template_id
AND trule.rule_id = rhdr.rule_id
AND rhdr.rule_id = rcon.rule_id
AND rcon.object_value_type = 'VARIABLE'
AND rhdr.status_code = 'ACTIVE'
-- AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
GROUP BY rcon.object_value_code
UNION
SELECT distinct rcon.object_code variable_code -- LHS of Condition from Global Rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.org_id = p_org_id
AND rhdr.intent = p_intent
AND rhdr.org_wide_flag = 'Y'
AND rcon.object_type = 'VARIABLE'
AND rhdr.status_code = 'ACTIVE'
-- AND SUBSTR(rcon.object_code,1,3) <> 'OKC'
GROUP BY rcon.object_code
UNION
SELECT distinct rcon.object_value_code variable_code -- RHS of Condition from Global Rule
FROM okc_xprt_rule_hdrs_all rhdr,
okc_xprt_rule_conditions rcon
WHERE rhdr.rule_id = rcon.rule_id
AND rhdr.org_id = p_org_id
AND rhdr.intent = p_intent
AND rhdr.org_wide_flag = 'Y'
AND rcon.object_value_type = 'VARIABLE'
AND rhdr.status_code = 'ACTIVE'
-- AND SUBSTR(rcon.object_value_code,1,3) <> 'OKC'
GROUP BY rcon.object_value_code);
SELECT distinct procedure_name procedure_name
FROM okc_xprt_deviations_t
WHERE run_id = p_sequence_id;
SELECT distinct variable_code variable_code
FROM okc_xprt_deviations_t
WHERE run_id = p_sequence_id
AND procedure_name = p_procedure_name;
variableCode_tbl.DELETE;
l_udf_var_value_tbl.DELETE(i);
SELECT VB.variable_code,
KA.id,
KA.article_version_id,
VBT.variable_name,
VB.clm_source,
VB.clm_ref1,
VB.clm_ref2,
VB.clm_ref3,
VB.clm_ref4,
VB.clm_ref5
FROM okc_k_articles_b KA,
okc_k_art_variables KV,
okc_bus_variables_b VB,
okc_bus_variables_tl VBT
WHERE VB.variable_code = KV.variable_code
and VB.variable_code = VBT.variable_code
and VBT.language = 'US'
AND KA.id = KV.cat_id
AND VB.clm_source is not null
AND KA.document_type = p_document_type
AND KA.document_id = p_document_id
ORDER BY VB.variable_code;
DELETE FROM OKC_TERMS_CLM_UDV_T;
/* Insert data into the temp table */
IF l_variable_value IS NOT NULL THEN
INSERT INTO OKC_TERMS_CLM_UDV_T
(
VARIABLE_CODE,
VARIABLE_VALUE,
DOC_TYPE,
DOC_ID,
ARTICLE_VERSION_ID,
CAT_ID
)
VALUES
(
csr_get_clm_udv_rec.variable_code, -- VARIABLE_CODE
l_variable_value, -- VARIABLE_VALUE
p_document_type, -- DOCUMENT_TYPE
p_document_id, -- DOCUMENT_ID
csr_get_clm_udv_rec.article_version_id, -- ARTICLE_VERSION_ID
csr_get_clm_udv_rec.id -- CAT_ID
);
SELECT kvar.variable_value_id FROM okc_k_art_variables kvar,okc_k_articles_b kart
WHERE kart.id = kvar.cat_id
AND kart.document_id = p_doc_id
AND kvar.variable_value_id IN
(SELECT avs.variable_value_id FROM okc_art_var_sections avs
WHERE avs.article_id = p_article_id
AND avs.article_version_id = p_article_version_id
)
AND kvar.variable_value_id IS NOT NULL;
SELECT bv.variable_type FROM okc_bus_variables_b bv
WHERE bv.variable_code = p_variable_code;
SELECT variable_value_id,variable_value,variable_code
FROM okc_art_var_sections
WHERE article_id = p_article_id
AND article_version_id = p_article_version_id;
SELECT variable_code
FROM okc_article_versions
WHERE article_version_id = p_article_version_id;
SELECT avs.scn_CODE FROM okc_art_var_sections avs
WHERE avs.variable_value = p_variable_value
AND avs.article_id = p_article_id
AND avs.article_version_id = p_article_version_id;
SELECT art.ORG_ID,art.ARTICLE_INTENT,kart.document_type
FROM okc_articles_all art,okc_k_articles_b kart
WHERE art.ARTICLE_ID = kart.sav_sae_id
AND kart.document_id = p_doc_id
AND ROWNUM=1;
SELECT variable_value
FROM okc_art_var_sections
WHERE variable_value_id = p_var_value_id
AND article_id = p_article_id
AND article_version_id = p_article_version_id;
SELECT variable_name
FROM okc_bus_variables_tl
WHERE variable_code = p_var_code;
SELECT clm_document_format
FROM po_headers_all
WHERE po_header_id = p_doc_id;
SELECT document_format
FROM pon_auction_headers_all
WHERE auction_header_id = p_doc_id;
EXECUTE IMMEDIATE 'SELECT document_format
FROM pon_auction_headers_all
WHERE auction_header_id = :1'
INTO l_var_value
USING p_doc_id;
EXECUTE IMMEDIATE 'SELECT clm_document_format
FROM po_headers_all
WHERE po_header_id = :1'
INTO l_var_value
USING p_doc_id;
SELECT scn_code, Count(scn_code)
FROM okc_sections_b
WHERE document_type = p_document_type
AND document_id = p_document_id
GROUP BY scn_code
HAVING (Count(scn_code) >1 );
SELECT id
FROM okc_sections_b
WHERE document_type = p_document_type
AND document_id = p_document_id
AND scn_code = p_scn_code
ORDER BY id;
UPDATE okc_k_articles_b
SET scn_id = l_dup_scn_id_tbl(1),
display_sequence = display_sequence + ((SELECT Max(display_sequence) FROM okc_k_articles_b
WHERE document_type = p_document_type AND document_id = p_document_id
AND scn_id = l_dup_scn_id_tbl(1)))
WHERE document_type = p_document_type
AND document_id = p_document_id
AND scn_id IN (l_remaining_scn_ids);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'700: Update done on okc_k_articles_b table ');
/*When all the articles are updated with the first section_id, delete the remaining sections from okc_sections_b table*/
l_del_stmt := 'DELETE FROM okc_sections_b WHERE id IN (' || l_remaining_scn_ids || ')';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'800: Delete done on okc_sections_b table ');
SELECT sav_sae_id, scn_id
FROM okc_k_articles_b
WHERE document_type = p_document_type
AND document_id = p_document_id
GROUP BY sav_sae_id, scn_id
HAVING (Count(sav_sae_id) >1 );
SELECT id
FROM okc_k_articles_b
where document_type = p_document_type
AND document_id = p_document_id
AND sav_sae_id = p_article_id
AND scn_id = p_scn_id
ORDER BY id asc;
l_del_stmt := 'DELETE FROM okc_k_articles_b WHERE id IN (' || l_dup_articles || ')';
PROCEDURE insert_usages_row( p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
p_template_id IN NUMBER,
p_doc_numbering_scheme IN NUMBER,
p_document_number IN VARCHAR2,
p_article_effective_date IN DATE,
p_config_header_id IN NUMBER,
p_config_revision_number IN NUMBER,
p_valid_config_yn IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2 ,
p_orig_system_reference_id1 IN NUMBER,
p_orig_system_reference_id2 IN NUMBER,
p_lock_terms_flag IN VARCHAR2,
p_locked_by_user_id IN NUMBER,
p_primary_template IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_usages_row';
l_last_updated_by OKC_TEMPLATE_USAGES.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_TEMPLATE_USAGES.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_TEMPLATE_USAGES.LAST_UPDATE_DATE%TYPE;
SELECT 'Y' FROM okc_mlp_template_usages
WHERE document_type = p_document_type
AND document_id = p_document_id
AND template_id = p_template_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered insert_usages_row ');
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
INSERT INTO okc_mlp_template_usages(
DOCUMENT_TYPE,
DOCUMENT_ID,
TEMPLATE_ID,
DOC_NUMBERING_SCHEME,
DOCUMENT_NUMBER,
ARTICLE_EFFECTIVE_DATE,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER,
VALID_CONFIG_YN,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
lock_terms_flag,
locked_by_user_id,
primary_template )
VALUES (
p_document_type,
p_document_id,
p_template_id,
p_doc_numbering_scheme,
p_document_number,
p_article_effective_date,
p_config_header_id,
p_config_revision_number,
p_valid_config_yn,
p_orig_system_reference_code,
p_orig_system_reference_id1,
p_orig_system_reference_id2,
l_object_version_number,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_login,
l_last_update_date,
p_lock_terms_flag,
p_locked_by_user_id,
p_primary_template
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: Leaving Insert_Row');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'400: Leaving Insert_Row:OTHERS Exception');
END insert_usages_row;
SELECT object_version_number
FROM OKC_MLP_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id
AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
FOR UPDATE OF object_version_number NOWAIT;
SELECT object_version_number
FROM OKC_MLP_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = cp_document_type AND DOCUMENT_ID = cp_document_id;
Okc_Api.Set_Message(G_FND_APP,G_LOCK_RECORD_DELETED,
'ENTITYNAME','OKC_MLP_TEMPLATE_USAGES',
'PKEY',p_document_type||':'||p_document_id,
'OVN',p_object_version_number
);
Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
PROCEDURE Delete_Usages_Row(
x_return_status OUT NOCOPY VARCHAR2,
p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
p_object_version_number IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Usages_Row';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Delete_Usages_Row');
DELETE FROM OKC_MLP_TEMPLATE_USAGES WHERE DOCUMENT_TYPE = p_document_type AND DOCUMENT_ID = p_document_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving Delete_Row');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Delete_Usages_Row:FND_API.G_EXC_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Delete_Usages_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving Delete_Usages_Row because of EXCEPTION: '||sqlerrm);
END Delete_Usages_Row;
SELECT 'Y' FROM dual WHERE p_template_id IN (
SELECT template_id FROM okc_template_usages
WHERE document_type = p_document_type AND document_id = p_document_id
UNION ALL
SELECT template_id FROM okc_mlp_template_usages
WHERE document_type = p_document_type AND document_id = p_document_id);
SELECT TEMPLATE_ID,
DOC_NUMBERING_SCHEME,
DOCUMENT_NUMBER,
ARTICLE_EFFECTIVE_DATE,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER,
VALID_CONFIG_YN,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
LOCK_TERMS_FLAG,
LOCKED_BY_USER_ID,
PRIMARY_TEMPLATE
FROM okc_mlp_template_usages
WHERE document_type = p_source_doc_type
AND document_id = p_source_doc_id;
INSERT INTO okc_mlp_template_usages(
DOCUMENT_TYPE,
DOCUMENT_ID,
TEMPLATE_ID,
DOC_NUMBERING_SCHEME,
DOCUMENT_NUMBER,
ARTICLE_EFFECTIVE_DATE,
CONFIG_HEADER_ID,
CONFIG_REVISION_NUMBER,
VALID_CONFIG_YN,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
lock_terms_flag,
locked_by_user_id,
primary_template )
VALUES (
p_target_doc_type,
p_target_doc_id,
TemplateIdTbl(i),
DocNumSchemeTbl(i),
DocumentNumberTbl(i),
ArticleEffectiveDateTbl(i),
ConfigHeaderIdTbl(i),
ConfigRevisionNumberTbl(i),
ValidConfigYNTbl(i),
OrigSystemRefCodeTbl(i),
OrigSystemRefId1Tbl(i),
OrigSystemRefId2Tbl(i),
1,
Fnd_Global.User_Id,
sysdate,
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
SYSDATE,
LockTermsTbl(i),
LockedByUserIdTbl(i),
PrimaryTemplateTbl(i)
);