[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;
SELECT okc_template_wf_keys_s1.NEXTVAL WF_SEQUENCE,
tmpl.template_id,
tmpl.template_name,
tmpl.description,
tmpl.status_code,
tmpl.object_version_number,
tmpl.org_id,
tmpl.intent,
tmpl.working_copy_flag,
tmpl.print_template_id,
tmpl.contract_expert_enabled,
fnd_lang.description language_desc
FROM okc_terms_templates_all tmpl,
fnd_languages_vl fnd_lang
WHERE tmpl.template_id = cp_template_id
AND ( tmpl.object_version_number = cp_object_version_number
OR cp_object_version_number IS NULL)
AND tmpl.language = fnd_lang.language_code(+)
FOR UPDATE OF
tmpl.status_code,
tmpl.object_version_number,
tmpl.last_update_date,
tmpl.last_updated_by
NOWAIT;
PROCEDURE selector (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out nocopy varchar2 )
IS
l_user_id NUMBER;
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = 'OKC_TERMS_LIBRARY_ADMIN'
AND application_id = 510;
WF_CORE.context('OKC_TERMS_TMPL_APPROVAL_PVT','Selector',itemtype,itemkey,actid,funcmode);
END selector;
SELECT object_version_number
FROM OKC_TERMS_TEMPLATES_ALL
WHERE TEMPLATE_ID = cp_template_id;
Okc_Api.Set_Message(G_FND_APP,G_RECORD_DELETED);
Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
p_check_for_drafts : 'Y' or 'N' if Y checks for drafts and inserts them
in the OKC_TMPL_DRAFT_CLAUSES table
x_sequence_id : contains the sequence id for table OKC_QA_ERRORS_T
that contains the validation results, is null if
no qa errors or warnings are found.
Existing out param x_qa_return_status will change to
have the following statues
x_qa_return_status : S if the template was succesfully submitted
W if qa check resulted in warnings. Use x_sequence_id
to display the qa results.
E if qa check resulted in errors. Use x_sequence_id
to display the qa results
D if there are draft articles and the user should be
redirected to the new submit page. Use x_sequence_id
if not null, to display a warnings link on the
new submit page.
p_validation_level p_check_for_drafts
Search/View/Update : A Y
New Submit Page : A N
Validation Page : E N
*/
PROCEDURE start_approval (
p_api_version IN Number,
p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
p_commit IN Varchar2 default FND_API.G_FALSE,
p_template_id IN Number,
p_object_version_number IN Number default NULL,
x_return_status OUT NOCOPY Varchar2,
x_msg_data OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number,
x_qa_return_status OUT NOCOPY Varchar2,
p_validation_level IN VARCHAR2 DEFAULT 'A',
p_check_for_drafts IN VARCHAR2 DEFAULT 'N',
x_sequence_id OUT NOCOPY NUMBER
)
IS
l_api_version CONSTANT NUMBER := 2;
SELECT org_id
-- SELECT '!'
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
SELECT 1
FROM OKC_K_ARTICLES_B
WHERE document_id = p_template_id
AND document_type = 'TEMPLATE';
SELECT name
FROM hr_operating_units
WHERE organization_id = pc_org_id;
UPDATE okc_terms_templates_all
SET status_code = 'PENDING_APPROVAL',
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE template_id = p_template_id;
UPDATE OKC_TMPL_DRAFT_CLAUSES
SET WF_SEQ_ID = l_tmpl_rec.wf_sequence
WHERE template_id = p_template_id
AND nvl(selected_yn, 'N') = 'Y';
DELETE OKC_TMPL_DRAFT_CLAUSES
WHERE TEMPLATE_ID = p_template_id
AND nvl(selected_yn, 'N') = 'N';
SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
UPDATE Okc_Xprt_Question_Orders
SET runtime_available_flag = 'Y',
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE template_id= l_tmpl_id
AND question_rule_status = 'ACTIVE';
DELETE FROM okc_xprt_template_rules
WHERE NVL(deleted_flag,'N') = 'Y'
AND template_id = l_tmpl_id;
UPDATE okc_xprt_template_rules
SET published_flag = 'Y'
WHERE template_id= l_tmpl_id ;
UPDATE okc_terms_templates_all
SET status_code = 'APPROVED'
WHERE template_id = l_tmpl_id;
UPDATE okc_terms_templates_all
SET status_code = l_tmpl_status
WHERE template_id = l_tmpl_id;
UPDATE okc_terms_templates_all
--SET status_code = nvl(l_tmpl_status,decode(working_copy_flag,null,'DRAFT','REVISION'))
--SET status_code = decode(working_copy_flag,null,'DRAFT','REVISION')
SET status_code = 'REJECTED'
WHERE template_id = l_tmpl_id;
PROCEDURE select_approver (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out nocopy varchar2 )
IS
l_template_id NUMBER;
SELECT decode(tmpl.intent,'S',org.org_information2,org.org_information6) org_information
FROM hr_organization_information org,
okc_terms_templates_all tmpl
WHERE org.organization_id = tmpl.org_id
AND org.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
AND tmpl.template_id = cp_template_id;
END select_approver;
SELECT 1
FROM fnd_attached_documents
WHERE entity_name = 'OKC_TERMS_TEMPLATES'
AND pk1_value = cp_val1
AND pk2_value = cp_val2;
SELECT 1
FROM OKC_TERMS_TEMPLATES_ALL
WHERE template_id = cp_tmpl_id
AND print_template_id IS NOT NULL;
p_status The status that the articles should be updated to,
can be one of 3 values - 'PENDING_APPROVAL', 'APPROVED', 'REJECTED'.
Error is thrown if the status is something else.
p_validation_level meaningful only for p_status = PENDING_APPROVAL.
The pending approval blk api accepts a validation level parameter
to either do complete or no validation. Passed as it is to the
pending approval blk api.
x_validation_results If for any clauses fail the validation check the results
are returned in this table
*/
PROCEDURE change_clause_status (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_template_id IN NUMBER,
p_wf_seq_id IN NUMBER DEFAULT NULL,
p_status IN VARCHAR2,
p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
x_validation_results OUT NOCOPY OKC_ART_BLK_PVT.validation_tbl_type)
IS
l_api_version CONSTANT NUMBER := 1;
SELECT org_id from OKC_TERMS_TEMPLATES_ALL
WHERE template_id = cp_template_id;
SELECT article_version_id from OKC_TMPL_DRAFT_CLAUSES
WHERE template_id = cp_template_id
AND nvl(wf_seq_id, -99) = nvl(cp_wf_seq_id, -99) --[p_wf_seq_id can be null]
AND selected_yn = 'Y';
l_article_version_id_tbl.DELETE;
SELECT decode(org_information1, 'Y', 'ADOPTED','AVAILABLE'),
hr.organization_id,
decode(cp_tmpl_intent,'S',org_information3,org_information7) org_information
FROM hr_organization_units hr,
hr_organization_information hri
WHERE hri.organization_id = hr.organization_id
and org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and hr.organization_id <> cp_global_org_id;
SELECT name
FROM hr_all_organization_units
WHERE organization_id = cp_org_id;
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'OKC_ARTICLE_ADOPTION_TYPE'
AND lookup_code = cp_adoption_type;
SELECT user_name
FROM wf_user_roles
WHERE role_name ='SYSADMIN';
selector(itemtype => itemtype,
itemkey => itemkey,
actid => actid,
funcmode => 'SET_CTX',
resultout => resultout);
SELECT 1
FROM okc_article_versions oav,
okc_tmpl_draft_clauses otdc
WHERE otdc.template_id = cp_template_id
AND otdc.wf_seq_id = cp_wf_seq_id
AND otdc.article_version_id = oav.article_version_id
AND oav.global_yn = 'Y'
AND ROWNUM < 2;
SELECT Count(1)
INTO l_org_count
FROM hr_organization_units hr,
hr_organization_information hri
WHERE hri.organization_id = hr.organization_id
and org_information_context = 'OKC_TERMS_LIBRARY_DETAILS'
and hr.organization_id <> global_org_id;
PROCEDURE select_draft_clauses(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_template_id IN NUMBER)
IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'select_draft_clauses';
okc_debug.log('100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.select_draft_clauses, p_template_id' || p_template_id, 2);
G_PKG_NAME, '100: Entered OKC_TERMS_TMPL_APPROVAL_PVT.select_draft_clauses, p_template_id' || p_template_id );
SAVEPOINT select_draft_clauses_pvt;
UPDATE OKC_TMPL_DRAFT_CLAUSES
SET selected_yn = 'Y'
WHERE template_id = (
select template_id from okc_terms_templates_all
where template_id = p_template_id and status_code in ('DRAFT', 'REJECTED', 'REVISION'));
okc_debug.log('1000: Leaving select_draft_clauses', 2);
G_PKG_NAME, '1000: Leaving select_draft_clauses' );
okc_debug.log('800: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_ERROR Exception', 2);
G_PKG_NAME, '800: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_ERROR Exception' );
okc_debug.log('900: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '900: Leaving select_draft_clauses: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
okc_debug.log('1000: Leaving select_draft_clauses because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '1000: Leaving select_draft_clauses because of EXCEPTION: '||sqlerrm );
END select_draft_clauses;