1 PACKAGE BODY PA_TASK_APPROVAL_PKG AS
2 /* $Header: PATSKPKB.pls 120.3.12010000.2 2009/08/19 12:46:13 anuragar noship $ */
3
4 p_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE log_message (p_log_msg IN VARCHAR2, debug_level IN NUMBER) IS
7 BEGIN
8 IF P_DEBUG_MODE = 'Y' THEN
9 pa_debug.write('log_message: ' || 'PA PWP Notification: ', 'log: ' || p_log_msg, debug_level);
10 END IF;
11 END log_message;
12
13 -- To verify the task is a child task or not
14 FUNCTION Is_Child_Task (p_project_id IN NUMBER
15 ,p_proj_element IN NUMBER
16 ,p_parent_struc_ver IN NUMBER
17 ,p_msg_count OUT NOCOPY NUMBER
18 ,p_msg_data OUT NOCOPY VARCHAR2
19 ,p_return_status OUT NOCOPY VARCHAR2
20 ) RETURN BOOLEAN IS
21
22 -- Cursor to identify if the given task has a parent task or not.
23 CURSOR C1 IS
24 SELECT 'Y'
25 FROM PA_PROJ_ELEMENT_VERSIONS
26 WHERE proj_element_id = p_proj_element
27 AND parent_structure_version_id = p_parent_struc_ver
28 AND financial_task_flag = 'Y'
29 AND EXISTS (SELECT 1
30 FROM PA_OBJECT_RELATIONSHIPS
31 WHERE object_type_from = 'PA_TASKS'
32 AND object_id_to1 = element_version_id
33 AND object_type_to = 'PA_TASKS'
34 AND relationship_type = 'S');
35
36 l_is_child_task VARCHAR2(1) :='N';
37 BEGIN
38 log_message('Inside PA_TASK_APPROVAL_PKG.Is_Child_Task',3);
39 p_return_status := 'S';
40 OPEN C1;
41 FETCH C1 INTO l_is_child_task;
42 CLOSE C1;
43 --b6694902_debug.debug('is_child_task '||l_is_child_task);
44 log_message('Result of Is_Child_Task '||l_is_child_task,3);
45
46 IF l_is_child_task = 'Y' THEN
47 log_message('Task '||p_proj_element||' has a parent task',3);
48 RETURN TRUE;
49 ELSE
50 log_message('Task '||p_proj_element||' is a top/root task',3);
51 RETURN FALSE;
52 END IF;
53 EXCEPTION
54 WHEN OTHERS THEN
55 p_return_status := 'E';
56 END Is_Child_Task;
57
58 -- This procedure is to return true/false based on the parent_task's approval status
59 -- of a given task. If Parent task is not approved, we cannot raise notification for
60 -- the given task.
61
62 FUNCTION Is_Parent_Task_Approved
63 (p_project_id IN NUMBER
64 ,p_parent_task_id IN NUMBER
65 ,p_task_id IN NUMBER
66 ,p_parent_struc_ver IN NUMBER
67 ,p_msg_count OUT NOCOPY NUMBER
68 ,p_msg_data OUT NOCOPY VARCHAR2
69 ,p_return_status OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
70
71 -- Cursor is to identify if the p_parent_task_id exists in PA_TASKs or not.
72 CURSOR C1 IS
73 SELECT 'Y'
74 FROM PA_TASKS
75 WHERE project_id = p_project_id
76 AND task_id = p_parent_task_id
77 AND EXISTS (
78 SELECT 1
79 FROM PA_PROJ_ELEMENTS
80 WHERE proj_element_id = p_parent_task_id
81 AND link_task_flag = 'N');
82
83 -- Cursor to find out if p_parent_task_id is root/top task
84 CURSOR C2 IS
85 SELECT 'Y'
86 FROM PA_PROJ_ELEMENT_VERSIONS
87 WHERE proj_element_id = p_parent_task_id
88 AND parent_structure_version_id = p_parent_struc_ver
89 AND financial_task_flag = 'Y'
90 AND EXISTS (SELECT 1
91 FROM PA_OBJECT_RELATIONSHIPS
92 WHERE object_type_from = 'PA_STRUCTURES'
93 AND object_id_to1 = element_version_id
94 AND object_type_to = 'PA_TASKS'
95 AND relationship_type = 'S');
96
97 l_is_parent_tsk_aprvd VARCHAR2(1) := 'N';
98 BEGIN
99 log_message('Inside PA_TASK_APPROVAL_PKG.Is_Parent_Task_Approved',3);
100 p_return_status := 'S';
101 OPEN C1;
102 FETCH C1 INTO l_is_parent_tsk_aprvd;
103 CLOSE C1;
104
105 IF l_is_parent_tsk_aprvd = 'N' THEN
106 -- If the task and parent task are one and the same, which means-> task is a root/top task.
107 -- In this case we need to check cursor C2. If cursor c2 returns a record, we return
108 -- that parent task is approved.
109 IF p_task_id = p_parent_task_id THEN
110 OPEN C2;
111 FETCH C2 INTO l_is_parent_tsk_aprvd;
112 CLOSE C2;
113 END IF;
114 END IF;
115
116 --b6694902_debug.debug('is_parent_task_aprv '||l_is_parent_tsk_aprvd);
117 log_message('Result of Is_Parent_Task_Approved '||l_is_parent_tsk_aprvd,3);
118
119 IF l_is_parent_tsk_aprvd = 'Y' THEN
120 RETURN TRUE;
121 ELSE
122 RETURN FALSE;
123 END IF;
124 EXCEPTION
125 WHEN OTHERS THEN
126 p_return_status := 'E';
127 END Is_Parent_Task_Approved;
128
129 PROCEDURE Submit_Task
130 (p_project_id IN NUMBER
131 ,p_task_id IN NUMBER
132 ,p_ref_task_id IN NUMBER
133 ,p_parent_struc_ver IN NUMBER
134 ,p_approver_user_id IN NUMBER
135 ,p_ci_id IN NUMBER
136 ,p_msg_count OUT NOCOPY NUMBER
137 ,p_msg_data OUT NOCOPY VARCHAR2
138 ,p_return_status OUT NOCOPY VARCHAR2) IS
139
140 x_err_stack VARCHAR2(2000);
141 x_err_stage VARCHAR2(100);
142 x_err_code NUMBER;
143 BEGIN
144 log_message('Inside PA_TASK_APPROVAL_PKG.Submit_Task',3);
145 p_return_status := 'S';
146
147 --b6694902_debug.debug('Before calling Start_Task_Aprv_Wf ');
148 --b6694902_debug.debug('Before calling Start_Task_Aprv_Wf for task '||p_task_id);
149 --b6694902_debug.debug('Before calling Start_Task_Aprv_Wf for task '||p_parent_struc_ver);
150
151 log_message('Before calling Start_Task_Aprv_Wf for task',3);
152 log_message('Task Id ['||p_task_id||'], '||
153 'Parent Task Structure version id ['||p_parent_struc_ver||'], '||
154 'Approver User Id ['||p_approver_user_id||']'
155 ,3);
156
157 update pa_proj_elements
158 set task_approver_id = p_approver_user_id
159 where proj_element_id = p_task_id;
160
161 PA_TASK_WORKFLOW_PKG.Start_Task_Aprv_Wf (
162 'PATASKWF'
163 ,'PA_TASK_APPROVAL_WF'
164 ,p_project_id
165 ,p_task_id
166 ,p_parent_struc_ver
167 ,p_approver_user_id
168 ,p_ci_id
169 ,x_err_stack
170 ,x_err_stage
171 ,x_err_code
172 );
173 --b6694902_debug.debug('x_err_code '||x_err_code);
174 IF x_err_code > 0 THEN
175 p_return_status :='E';
176 END IF;
177 commit;
178 END Submit_Task;
179
180 -- This procedure is being called from Change Order workflow
181 -- to see if all the used tasks are approved or not.
182 PROCEDURE Check_UsedTask_Status
183 (p_ci_id IN NUMBER
184 ,p_msg_count OUT NOCOPY NUMBER
185 ,p_msg_data OUT NOCOPY VARCHAR2
186 ,p_return_status OUT NOCOPY VARCHAR2) IS
187
188 CURSOR C1 IS
189 Select count(distinct task_id) from
190 pa_resource_assignments pra where
191 budget_version_id in (
192 select budget_version_id from pa_budget_versions where ci_id = p_ci_id )
193 and exists (select 1
194 from pa_proj_elements ppe,
195 pa_proj_element_versions ppev,
196 pa_object_relationships por
197 where ppe.proj_element_id = pra.task_id
198 and ppe.project_id = pra.project_id
199 and ppe.link_task_flag = 'Y'
200 and ppe.type_id = 1
201 and ppev.proj_element_id = ppe.proj_element_id
202 and por.object_id_to1 = ppev.element_version_id
203 and por.object_type_to = 'PA_TASKS'
204 and por.relationship_type = 'S'
205 and ppev.financial_task_flag = 'Y')
206 and not exists (select 1 from pa_tasks where task_id = pra.task_id and project_id = pra.project_id);
207
208 l_unapproved_task_cnt NUMBER;
209 BEGIN
210 log_message('Inside PA_TASK_APPROVAL_PKG.Check_UsedTask_Status',3);
211 p_return_status := 'S';
212 OPEN C1;
213 FETCH C1 INTO l_unapproved_task_cnt;
214 CLOSE C1;
215
216 IF l_unapproved_task_cnt > 0 THEN
217 log_message('There are unapproved tasks for this change document.',3);
218 p_return_status := 'E';
219 END IF;
220 END;
221
222 -- This procedure is to put the Change Order status to 'CI_SUBMITTED'.
223 -- These are eligible to be picked up by the Task Workflow process.
224 PROCEDURE Mark_CO_Status(p_ci_id IN NUMBER
225 ,p_msg_count OUT NOCOPY NUMBER
226 ,p_msg_data OUT NOCOPY VARCHAR2
227 ,p_return_status OUT NOCOPY VARCHAR2) IS
228 BEGIN
229 log_message('Inside PA_TASK_APPROVAL_PKG.Mark_CO_Status',3);
230 p_return_status := 'S';
231 UPDATE pa_control_items SET status_code = 'CI_SUBMITTED' WHERE ci_id = p_ci_id;
232 EXCEPTION
233 WHEN OTHERS THEN
234 log_message('Inside WHEBN OTHERS exception of PA_TASK_APPROVAL_PKG.Mark_CO_Status',3);
235 p_return_status := 'E';
236 p_msg_data := SQLERRM;
237 END;
238
239 END PA_TASK_APPROVAL_PKG;
[Home] [Help]