The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress || l_error_msg);
PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
'l_forward_to_username: ' || l_forward_to_username);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT full_name
INTO l_disp_name
FROM PER_ALL_PEOPLE_F --
WHERE person_id = l_id
AND trunc(sysdate) BETWEEN effective_start_date
AND effective_end_date;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
Select ORIG_SYSTEM_ID
from WF_USERS
where name = username;
SELECT HR.PERSON_ID
FROM FND_USER FND, PO_WORKFORCE_CURRENT_X HR --
WHERE FND.USER_NAME = username
AND FND.EMPLOYEE_ID = HR.PERSON_ID
AND ROWNUM = 1;
Select default_approval_path_id into l_approval_path_id
FROM po_document_types podt
WHERE podt.document_type_code = l_document_type_code
AND podt.document_subtype = l_document_subtype;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT FORWARDING_MODE_CODE into l_forward_mode
from po_document_types
where document_subtype = l_document_subtype
and document_type_code = l_document_type_code;
SELECT NVL(use_positions_flag, 'N') into l_use_positions_flag
from financials_system_parameters;
** check to select the supervisor if the assignment type E.
** Also modified the SQLs slightly to ensure that no more inline
** SQLs to improve performance.
** Also added CWK assignment type
*/
/*Bug 8331565:
Introduced view PER_PERSON_TYPES_V
to get if the next approver is Contingent worker
*/
CURSOR c1 (p_empid NUMBER, p_business_group_id NUMBER) IS
SELECT pafe.supervisor_id
FROM Per_All_Assignments_f pafe, --
Per_All_People_f ppfs, --
Per_All_Assignments_f pafs, --
per_person_types_v ppts,
per_person_type_usages_f pptu
WHERE pafe.business_group_id = p_business_group_id
AND pafe.person_id = p_empid
AND Trunc(SYSDATE) BETWEEN pafe.Effective_Start_Date
AND pafe.Effective_End_Date
AND pafe.Primary_Flag = 'Y'
AND pafe.Assignment_Type IN ('E','C')
AND ppfs.Person_Id = pafe.Supervisor_Id
AND Trunc(SYSDATE) BETWEEN ppfs.Effective_Start_Date
AND ppfs.Effective_End_Date
AND pafs.Person_Id = ppfs.Person_Id
AND Trunc(SYSDATE) BETWEEN pafs.Effective_Start_Date
AND pafs.Effective_End_Date
AND pafs.Primary_Flag = 'Y'
AND pafs.Assignment_Type IN ('E','C')
AND pptu.Person_Id = ppfs.Person_Id
AND ppts.person_type_id = pptu.person_type_id
AND ppts.System_Person_Type IN ('EMP','EMP_APL','CWK'); --
** check to select the supervisor if the assignment type E.
** Also modified the SQLs slightly to ensure that no more inline
** SQLs to improve performance.
** Also added CWK assignment type
*/
/*Bug 8331565:
Introduced view PER_PERSON_TYPES_V
to get if the next approver is Contingent worker
*/
CURSOR C2 ( p_empid NUMBER ) IS
SELECT DISTINCT Pafe.supervisor_id
FROM Per_All_Assignments_f pafe, --
Per_All_People_f ppfs, --
Per_All_Assignments_f pafs, --
per_person_types_v ppts,
per_person_type_usages_f pptu
WHERE pafe.person_id = p_empid
AND Trunc(SYSDATE) BETWEEN pafe.Effective_Start_Date
AND pafe.Effective_End_Date
AND pafe.Primary_Flag = 'Y'
AND pafe.Assignment_Type IN ('E','C')
AND ppfs.Person_Id = pafe.Supervisor_Id
AND Trunc(SYSDATE) BETWEEN ppfs.Effective_Start_Date
AND ppfs.Effective_End_Date
AND Pafs.Person_Id = ppfs.Person_Id
AND Trunc(SYSDATE) BETWEEN pafs.Effective_Start_Date
AND pafs.Effective_End_Date
AND pafs.Primary_Flag = 'Y'
AND pafs.Assignment_Type IN ('E','C')
AND pptu.Person_Id = ppfs.Person_Id
AND ppts.person_type_id = pptu.person_type_id
AND ppts.System_Person_Type IN ('EMP','EMP_APL','CWK'); --
SELECT business_group_id
INTO l_business_group_id
FROM FINANCIALS_SYSTEM_PARAMETERS;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
SELECT POEH.superior_id, poeh.superior_level, HREC.full_name
FROM PO_EMPLOYEES_CURRENT_X HREC, --
PO_EMPLOYEE_HIERARCHIES POEH
WHERE POEH.position_structure_id = p_approval_path_id
AND POEH.employee_id = p_empid
AND HREC.employee_id = POEH.superior_id
AND POEH.superior_level > 0
UNION ALL
SELECT poeh.superior_id, poeh.superior_level, cwk.full_name
FROM PER_ALL_PEOPLE_F CWK,
PO_EMPLOYEE_HIERARCHIES POEH,
PER_ALL_ASSIGNMENTS_F A,
PER_PERIODS_OF_SERVICE PS
WHERE poeh.position_structure_id = p_approval_path_id
AND poeh.employee_id = p_empid
AND cwk.person_id = poeh.superior_id
AND poeh.superior_level > 0
AND nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y'
AND A.PERSON_ID = CWK.PERSON_ID
AND A.PERSON_ID = PS.PERSON_ID
AND A.ASSIGNMENT_TYPE='E'
AND CWK.EMPLOYEE_NUMBER IS NOT NULL
AND A.PERIOD_OF_SERVICE_ID = PS.PERIOD_OF_SERVICE_ID
AND A.PRIMARY_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN CWK.EFFECTIVE_START_DATE AND CWK.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND ( PS.ACTUAL_TERMINATION_DATE>= TRUNC(sysdate) OR PS.ACTUAL_TERMINATION_DATE IS NULL )
UNION ALL
SELECT
POEH.SUPERIOR_ID , POEH.SUPERIOR_LEVEL, CWK.FULL_NAME
FROM PER_ALL_PEOPLE_F CWK,
PO_EMPLOYEE_HIERARCHIES POEH,
PER_ALL_ASSIGNMENTS_F A,
PER_PERIODS_OF_PLACEMENT PP
WHERE poeh.position_structure_id = p_approval_path_id
AND poeh.employee_id = p_empid
AND cwk.person_id = poeh.superior_id
AND poeh.superior_level > 0
AND nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y'
AND A.PERSON_ID = CWK.PERSON_ID
AND A.PERSON_ID = PP.PERSON_ID
AND A.ASSIGNMENT_TYPE='C'
AND CWK.NPW_NUMBER IS NOT NULL
AND A.PERIOD_OF_PLACEMENT_DATE_START = PP.DATE_START
AND A.PRIMARY_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN CWK.EFFECTIVE_START_DATE AND CWK.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND ( PP.ACTUAL_TERMINATION_DATE>= TRUNC(sysdate) OR PP.ACTUAL_TERMINATION_DATE IS NULL )
ORDER BY superior_level, full_name;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
select NVL(can_preparer_approve_flag,'N')
from po_document_types
where document_type_code = p_document_type_code
and document_subtype = p_document_subtype;