The following lines contain the word 'select', 'insert', 'update' or 'delete':
update oks_k_headers_b
set RMNDR_SUPPRESS_FLAG = p_suppress_yn,
object_version_number = object_version_number+1,
/* Added Bug# 7717268 */
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where chr_id = p_chr_id;
UPDATE okc_k_headers_all_b okcb
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE okcb.id = p_chr_id;
x_return_status := OKC_CVM_PVT.update_minor_version(p_chr_id => p_chr_id);
'1111:Reminder Suppress Flag Update Failed - l_sql_err:'||l_sql_err);
IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, olt.template_name)
FROM OKS_REPORT_TEMPLATES ort ,OKS_LAYOUT_TEMPLATES_V olt
WHERE ort.report_id=olt.template_id
AND ort.id=p_attachment_template_id;*/
IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, xtvl.template_name)
FROM oks_report_templates ort ,XDO_TEMPLATES_VL XTVL
WHERE XTVL.APPLICATION_ID = 515 AND
XTVL.TEMPLATE_TYPE_CODE = 'RTF' AND
SYSDATE BETWEEN XTVL.START_DATE AND
NVL(XTVL.END_DATE,SYSDATE) AND NVL(XTVL.DEPENDENCY_FLAG,'P') = 'P' AND
ort.report_id=xtvl.template_id AND
ort.id=p_attachment_template_id;
Update OKS_K_HEADERS_B
Set PROCESS_REQUEST_ID = l_request_id
Where CHR_ID = p_chr_id;
UPDATE OKS_K_HEADERS_B
SET PROCESS_REQUEST_ID = l_request_id
WHERE chr_id = l_chr_id_tbl(i);
SELECT 'X'
FROM okc_k_headers_all_b okck,
okc_statuses_b sts
WHERE okck.sts_code = sts.code
AND sts.ste_code = 'ENTERED'
AND okck.id = p_chr_id;
SELECT 'X'
FROM okc_k_headers_all_b okck,
okc_statuses_b sts
WHERE okck.sts_code = sts.code
AND sts.ste_code = 'ENTERED'
AND okck.id = p_chr_id
AND NOT EXISTS
(SELECT 1
FROM WF_ITEMS WF,
OKC_PROCESS_DEFS_B KPDF
WHERE WF.item_key = okck.contract_number || okck.contract_number_modifier
AND WF.end_date IS NULL
AND WF.item_type = KPDF.wf_name
AND KPDF.pdf_type = 'WPS');
This method will insert the email details
into OKS_EMAIL_DETAILS table and
will return email_id as the output parameter value.-Bug#4911901
*/
PROCEDURE STORE_EMAIL_DTLS
(
p_from_address IN VARCHAR2,
p_to_address IN VARCHAR2,
p_cc_address IN VARCHAR2,
p_reply_to_address IN VARCHAR2,
p_message_template_id IN NUMBER,
p_attachment_template_id IN NUMBER,
p_email_subject IN VARCHAR2,
p_email_body IN VARCHAR2,
p_email_contract_status IN VARCHAR2,
x_email_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)AS
l_api_name CONSTANT VARCHAR2(30) := 'STORE_EMAIL_DTLS';
INSERT INTO oks_email_details
(
email_id,
from_address,
to_address,
cc_address,
reply_to_address,
message_template_id,
attachment_template_id,
email_subject,
email_body,
email_contract_status,
CREATED_BY,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
oks_email_details_s1.nextval,
p_from_address,
p_to_address,
p_cc_address,
p_reply_to_address,
p_message_template_id,
p_attachment_template_id,
p_email_subject,
TO_CLOB(p_email_body),
p_email_contract_status,
FND_GLOBAL.USER_ID, -- CREATED_BY
FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY
SYSDATE, -- CREATION_DATE
SYSDATE, -- LAST_UPDATE_DATE
FND_GLOBAL.LOGIN_ID --LAST_UPDATE_LOGIN
)
RETURNING email_id
INTO l_email_id;
'200: After inserting into table:');
SELECT email_body
INTO x_email_body
FROM OKS_EMAIL_DETAILS
WHERE email_id = p_email_id;
This API will delete email details from OKS_EMAIL_DETAILS table.-Bug#4911901
*/
PROCEDURE DEL_EMAIL_DTLS
(
p_email_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)AS
l_api_name CONSTANT VARCHAR2(30) := 'DEL_EMAIL_DTLS';
DELETE FROM OKS_EMAIL_DETAILS WHERE email_id = p_email_id;
'402: Rows are deleted successfully from OKS_EMAIL_DETAILS table');
IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, olt.template_name)
FROM OKS_REPORT_TEMPLATES ort ,OKS_LAYOUT_TEMPLATES_V olt
WHERE ort.report_id=olt.template_id
AND ort.id=p_attachment_template_id;*/
IS SELECT ort.report_id,ort.template_set_type, nvl(ort.attachment_name, xtvl.template_name)
FROM oks_report_templates ort ,XDO_TEMPLATES_VL XTVL
WHERE XTVL.APPLICATION_ID = 515 AND
XTVL.TEMPLATE_TYPE_CODE = 'RTF' AND
SYSDATE BETWEEN XTVL.START_DATE AND
NVL(XTVL.END_DATE,SYSDATE) AND NVL(XTVL.DEPENDENCY_FLAG,'P') = 'P' AND
ort.report_id=xtvl.template_id AND
ort.id=p_attachment_template_id;
Update OKS_K_HEADERS_B
Set PROCESS_REQUEST_ID = l_request_id
Where CHR_ID = p_chr_id;
PROCEDURE update_single_contracts (
p_chr_id IN OKC_K_HEADERS_ALL_B.ID%TYPE,
p_status_code IN OKC_K_HEADERS_ALL_B.STS_CODE%TYPE,
p_reason_code IN OKC_K_HEADERS_ALL_B.TRN_CODE%TYPE,
p_comments IN VARCHAR2,
p_due_date IN OKS_K_HEADERS_B.FOLLOW_UP_DATE%TYPE,
p_action IN OKS_K_HEADERS_B.FOLLOW_UP_ACTION%TYPE,
p_est_percent IN OKS_K_HEADERS_B.EST_REV_PERCENT%TYPE,
p_est_date IN OKS_K_HEADERS_B.EST_REV_DATE%TYPE,
p_contract_notes IN JTF_NOTES_TL.NOTES%TYPE,
p_renewal_notes IN OKS_K_HEADERS_B.RENEWAL_COMMENT%TYPE,
x_succ_err_contract OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER)
AS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'update_single_contracts';
l_minor_version_updated VARCHAR2(1) := 'F';
Select sts_code,
contract_number||decode(contract_number_modifier, NULL,'','-'||contract_number_modifier) contract_number
from okc_k_headers_all_b
Where id = c_chr_id;
SELECT follow_up_date,
follow_up_action,
est_rev_percent,
est_rev_date
FROM oks_k_headers_b
WHERE chr_id = p_chr_id;
'211:Calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS API'
);
OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_init_msg_list => FND_API.G_TRUE,
p_id => p_chr_id,
p_new_sts_code => p_status_code,
p_canc_reason_code => p_reason_code,
p_old_sts_code => l_old_status_code,
p_comments => p_comments,
p_term_cancel_source => 'MANUAL',
p_date_cancelled => sysdate,
p_validate_status => 'Y');
'212:x_return_status after calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS: ' ||p_chr_id||': '||x_return_status);
'213:x_msg_data after calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS for ' ||p_chr_id||': '||x_msg_data);
'214:x_msg_count after calling OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS for ' ||p_chr_id||': '||x_msg_count);
--OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS API already updated minor version
l_minor_version_updated := 'T';
END IF;--end Update Contract Status
--if contract status got updated then update the jtf notes otherwise return error status
if(p_contract_notes IS NOT NULL) then
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
THEN
fnd_log.STRING (fnd_log.level_statement,
g_module ||
l_api_name,
'215:Calling JTF_NOTES_PUB.CREATE_NOTE API');
p_last_update_date => sysdate,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.LOGIN_ID,
p_note_type => 'OKS_ADMIN');
END IF;--end Update jtf Notes
fnd_message.set_name('OKS','OKS_FORECAST_UPDATE_FAILED');
Update oks_k_headers_b
set follow_up_date = p_due_date,
follow_up_action = p_action,
est_rev_percent = p_est_percent,
est_rev_date = p_est_date,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where chr_id = p_chr_id;
Update okc_k_headers_all_b okcb
set last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where okcb.id = p_chr_id;
'221a:Forecast and Follow Up update failed - l_sql_err:'||l_sql_err);
'221:Inside Update Follow Up and Forecast part - x_return_status:'||x_return_status);
end if;--end Update Follow Up Action and Date
UPDATE oks_k_headers_b
SET renewal_comment = p_renewal_notes,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE chr_id = p_chr_id;
UPDATE okc_k_headers_all_b okcb
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE okcb.id = p_chr_id;
END IF; --End: Update Renewal Notes - kkolukul
--Update minor version
if (l_minor_version_updated = 'F') THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
THEN
fnd_log.STRING (fnd_log.level_statement,
g_module ||
l_api_name,
'224:Now Updating minor_version in okc_k_vers_numbers');
/* Update okc_k_vers_numbers
Set minor_version = minor_version + 1
Where chr_id = p_chr_id;
'225a:Follow Up update failed - l_sql_err:'||l_sql_err);
x_return_status := OKC_CVM_PVT.update_minor_version(p_chr_id => p_chr_id);
'225a:Follow Up update failed - l_sql_err:'||l_sql_err);
'225:Inside Update minor version part - x_return_status:'||x_return_status);
end if;--end update minor version
END update_single_contracts;