The following lines contain the word 'select', 'insert', 'update' or 'delete':
, P_Updated_By IN NUMBER
, P_Update_Date IN DATE
, P_Login_ID IN NUMBER
, X_Chg_Log_ID IN OUT NOCOPY NUMBER
, X_Approve_Date IN OUT NOCOPY DATE
, X_Implement_Date IN OUT NOCOPY DATE
) IS
CURSOR sts IS
SELECT wf_item_type
, wf_process
FROM oke_chg_statuses_b
WHERE chg_status_code = P_New_Status_Code;
SELECT k.k_number_disp
, k.k_type_code
, kt.k_type_name
, k.authoring_org_id
FROM oke_k_headers_v k
, oke_k_types_vl kt
WHERE k_header_id = P_K_Header_ID
AND kt.k_type_code = k.k_type_code;
SELECT u.user_name
FROM per_all_people_f p
, fnd_user u
WHERE person_id = P_Requested_By
AND u.employee_id = p.person_id;
SELECT chg_status_type_code
FROM oke_chg_statuses_b
WHERE chg_status_code = C_Status_Code;
SELECT oke_chg_logs_s.nextval
INTO X_Chg_Log_ID
FROM dual;
, aname => 'LAST_UPDATED_BY'
, avalue => P_Updated_by );
INSERT INTO oke_chg_logs
( chg_log_id
, chg_request_id
, chg_status_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, wf_item_type
, wf_process
, wf_item_key )
VALUES
( X_Chg_Log_ID
, P_Chg_Request_ID
, P_New_Status_Code
, P_Update_Date
, P_Updated_By
, P_Update_Date
, P_Updated_By
, P_Login_ID
, l_wf_item_type
, l_wf_process
, l_wf_item_key
);
X_Approve_Date := P_Update_Date;
X_Implement_Date := P_Update_Date;
SELECT WF_Item_Type
, WF_Item_Key
FROM OKE_Chg_Logs
WHERE Chg_Log_ID = P_Chg_Log_ID;
PROCEDURE Update_Process
( P_Chg_Log_ID IN NUMBER
, P_Mode IN VARCHAR2
) IS
CURSOR wf IS
SELECT wf_item_type
, wf_item_key
FROM oke_chg_logs
WHERE chg_log_id = P_Chg_Log_ID;
END Update_Process;
SELECT CRQ2.Chg_Request_Num
, CS.Chg_Status_Type_Code
FROM oke_chg_requests CRQ1
, oke_chg_requests CRQ2
, oke_chg_statuses_b CS
WHERE CRQ1.Chg_Request_ID = X_Chg_Request_ID
AND CRQ2.K_Header_ID = CRQ1.K_Header_ID
AND CRQ2.Chg_Request_ID <> CRQ1.Chg_Request_ID
AND CS.Chg_Status_Code = CRQ2.Chg_Status_Code
AND ( CS.Chg_Status_Type_Code = 'IN PROGRESS'
OR ( CS.Chg_Status_Type_Code NOT IN ( 'COMPLETED'
, 'CANCELED' )
AND CRQ2.Effective_Date < CRQ1.Effective_Date
)
)
ORDER BY DECODE(CS.Chg_Status_Type_Code, 'IN PROGRESS' , 1 , 2);
SELECT COUNT(CRQ2.Chg_Request_Num) ChgReq_Count
FROM oke_chg_requests CRQ1
, oke_chg_requests CRQ2
, oke_chg_statuses_b CS
WHERE CRQ1.Chg_Request_ID = X_Chg_Request_ID
AND CRQ2.K_Header_ID = CRQ1.K_Header_ID
AND CRQ2.Chg_Request_ID <> CRQ1.Chg_Request_ID
AND CS.Chg_Status_Code = CRQ2.Chg_Status_Code
AND CS.Chg_Status_Type_Code IN ( 'IN PROGRESS' , 'COMPLETED' )
AND CRQ2.Effective_Date > CRQ1.Effective_Date;
SELECT CR.Chg_Request_Num
, CS.Chg_Status_Name
, H.Version_Reason_Code
FROM oke_k_vers_numbers_h H
, oke_chg_requests CR
, oke_chg_statuses_tl CS
, ( SELECT K_Header_ID
, Chg_Request_ID
, max(Major_Version) Last_Version
FROM oke_k_vers_numbers_h
GROUP BY K_Header_ID , Chg_Request_ID ) V
WHERE V.K_Header_ID = X_K_Header_ID
AND H.K_Header_ID = V.K_Header_ID
AND H.Major_Version = V.Last_Version
AND ( X_Major_Version IS NULL
OR H.Major_Version <= X_Major_Version )
AND H.Version_Reason_Code <> 'CHGREQ_REVERT'
AND CR.Chg_Request_ID = H.Chg_Request_ID
AND CS.Chg_Status_Code = CR.Chg_Status_Code
AND CS.Language = userenv('LANG')
ORDER BY H.Major_Version DESC;