1 PACKAGE PA_TASK_WORKFLOW_PKG AUTHID CURRENT_USER as
2 /* $Header: PATSKWFS.pls 120.3 2009/07/21 14:11:32 anuragar noship $ */
3
4
5 TYPE g_deltask_rec IS RECORD (
6 project_id NUMBER(15),
7 task_id NUMBER(15),
8 elem_ver_id NUMBER(15),
9 rec_ver_num NUMBER(15),
10 parent_struc_ver NUMBER(15)
11 );
12
13 TYPE g_taskrec_tbl IS TABLE OF g_deltask_rec INDEX BY BINARY_INTEGER;
14
15 g_del_taskrec g_taskrec_tbl;
16
17 -- Cursor to fetch the project information.
18 CURSOR c_proj_info(p_project_id NUMBER) IS
19 SELECT project_id project_id
20 ,segment1 project_number
21 ,name project_name
22 ,start_date start_date
23 ,completion_date end_date
24 ,project_type project_type
25 ,carrying_out_organization_id organization_id
26 ,project_status_code project_status
27 FROM PA_PROJECTS_ALL
28 WHERE project_id = p_project_id;
29
30 -- Cursor to fetch the task information.
31 CURSOR c_task_info(p_project_id NUMBER, p_task_id NUMBER) IS
32 SELECT ppa.project_id project_id,
36 ppe.element_number task_number,
33 ppa.segment1 project_number,
34 ppa.name project_name,
35 ppe.proj_element_id task_id,
37 ppe.name task_name,
38 ppe.task_approver_id task_app_chg_id,
39 ppe.task_status,
40 ppe.created_by,
41 ppe.manager_person_id,
42 ppe.carrying_out_organization_id organization,
43 ppev.wbs_level task_wbs_level,
44 ppev.parent_structure_version_id,
45 ppevs.scheduled_start_date,
46 ppevs.scheduled_finish_date scheduled_end_date,
47 ppe1.element_number parent_task_number,
48 ppe1.proj_element_id parent_task_id,
49 ppe1.name parent_task_name,
50 ppev1.wbs_level parent_task_wbs_level
51 FROM PA_PROJ_ELEMENTS PPE,
52 PA_PROJ_ELEMENT_VERSIONS ppev,
53 PA_PROJ_ELEM_VER_SCHEDULE ppevs,
54 PA_PROJ_ELEMENTS PPE1,
55 PA_PROJ_ELEMENT_VERSIONS ppev1,
56 PA_OBJECT_RELATIONSHIPS por,
57 PA_PROJECTS_ALL ppa
58 WHERE ppa.project_id = p_project_id
59 AND ppe.project_id = p_project_id
60 AND ppe.link_task_flag = 'Y'
61 AND ppe.type_id = 1
62 AND ppev.proj_element_id = ppe.proj_element_id
63 AND por.object_id_to1 = ppev.element_version_id
64 AND ppevs.element_version_id(+) = ppev.element_version_id
65 AND ppev1.project_id = p_project_id
66 AND ((ppev1.element_version_id = por.object_id_from1)
67 OR (ppev1.parent_structure_version_id =por.object_id_from1
68 and por.RELATIONSHIP_SUBTYPE='STRUCTURE_TO_TASK'
69 and ppe1.proj_element_id = ppe.proj_element_id))
70 AND ppev1.parent_structure_version_id = ppev.parent_structure_version_id
71 AND ppe1.proj_element_id= ppev1.proj_element_id
72 AND por.object_type_to = 'PA_TASKS'
73 AND por.relationship_type = 'S'
74 AND ppev.financial_task_flag = 'Y'
75 AND ppev1.financial_task_flag = 'Y'
76 AND ppev.source_object_id = ppev1.source_object_id
77 AND ppe.proj_element_id = p_task_id;
78
79 CURSOR c_user_info(p_user_id NUMBER) IS
80 SELECT f.user_id user_id
81 ,f.user_name user_name
82 ,e.first_name||' '||e.last_name full_name
83 FROM FND_USER f
84 ,PA_EMPLOYEES e
85 WHERE f.user_id = p_user_id
86 AND f.employee_id = e.person_id;
87
88 /*---------------------------------------------------------------------------------------------------------
89 -- This is the main procedure that invokes the Task workflow. This is being called from Task Approval
90 -- Workflow package.
91 -- Input parameters
92 -- Parameters Type Required Description
93 -- p_item_type VARCHAR2 YES Workflow Item Type.
94 -- p_process VARCHAR2 YES Name of the process in workflow
95 -- to run.
96 -- p_project_id NUMBER YES Identification of the project
97 -- p_task_id NUMBER YES Task Identifier
98 -- p_parent_struc_ver NUMBER YES Parent task structure version id
99 -- p_approver_user_id NUMBER YES Approver user Id
100 -- p_ci_id NUMBER YES Change document Id
101 -- Out parameters
102 -- Parameters Type Required Description
103 -- x_error_stage VARCHAR2 YES To identify which flow of the code
104 -- caused the error.
105 -- x_err_code NUMBER YES To identify which part of the code
106 -- has been errored out.
107 -- x_error_stack VARCHAR2 YES Holds the error message code
108 ----------------------------------------------------------------------------------------------------------*/
109 PROCEDURE Start_Task_Aprv_Wf (p_item_type IN VARCHAR2
110 ,p_process IN VARCHAR2
111 ,p_project_id IN NUMBER
112 ,p_task_id IN NUMBER
113 ,p_parent_struc_ver IN NUMBER
114 ,p_approver_user_id IN NUMBER
115 ,p_ci_id IN NUMBER
116 ,x_err_stack IN OUT NOCOPY VARCHAR2
117 ,x_err_stage IN OUT NOCOPY VARCHAR2
118 ,x_err_code OUT NOCOPY NUMBER);
119
120
121 /*---------------------------------------------------------------------------------------------------------
122 -- This procedure is being invoked from Task workflow. This is to identify whether the task submitted for
123 -- approval is a child task of another task or is a main task itself. Based on this, workflow decides on
124 -- which method to invoke.
125 -- Input parameters
126 -- Parameters Type Required Description
127 -- itemtype VARCHAR2 YES Workflow Item type
128 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
129 -- actid NUMBER YES Action Id
130 -- funcmode VARCHAR2 YES Function Mode
131 -- Out parameters
132 -- Parameters Type Required Description
133 -- resultout VARCHAR2 YES Result of the particular function
134 ----------------------------------------------------------------------------------------------------------*/
135
139 ,funcmode IN VARCHAR2
136 PROCEDURE Is_Child_Task(itemtype IN VARCHAR2
137 ,itemkey IN VARCHAR2
138 ,actid IN NUMBER
140 ,resultout OUT NOCOPY VARCHAR2);
141
142 /*---------------------------------------------------------------------------------------------------------
143 -- This procedure is being invoked from Task workflow. This is to identify whether parent task of
144 -- the task submitted for approval is already approved or not. Based on this, workflow decides
145 -- whether to Raise notification for task approval or to update task status as 'Pending' for its parent
146 -- task approval.
147 -- Input parameters
148 -- Parameters Type Required Description
149 -- itemtype VARCHAR2 YES Workflow Item type
150 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
151 -- actid NUMBER YES Action Id
152 -- funcmode VARCHAR2 YES Function Mode
153 -- Out parameters
154 -- Parameters Type Required Description
155 -- resultout VARCHAR2 YES Result of the particular function
156 ----------------------------------------------------------------------------------------------------------*/
157
158 PROCEDURE Is_Parent_Task_Approved
159 (itemtype IN VARCHAR2
160 ,itemkey IN VARCHAR2
161 ,actid IN NUMBER
162 ,funcmode IN VARCHAR2
163 ,resultout OUT NOCOPY VARCHAR2);
164
165 PROCEDURE Append_Varchar_To_Clob(p_varchar IN VARCHAR2
166 ,p_clob IN OUT NOCOPY CLOB);
167
168 PROCEDURE Show_Task_Notify_Preview (document_id IN VARCHAR2
169 ,display_type IN VARCHAR2
170 ,document IN OUT NOCOPY CLOB
171 ,document_type IN OUT NOCOPY VARCHAR2);
172
173 -- This procedure is to generate the workflow notification dynamically
174 PROCEDURE Generate_Task_Aprv_Notify
175 (p_item_type IN VARCHAR2
176 ,p_item_key IN VARCHAR2
177 ,p_project_id IN NUMBER
178 ,p_org_id IN NUMBER
179 ,p_task_id IN NUMBER
180 ,p_parent_struc_ver IN NUMBER
181 ,p_ci_id IN NUMBER
182 ,p_cd_yn IN VARCHAR2 := 'Y'
183 ,x_content_id OUT NOCOPY NUMBER);
184
185 /*---------------------------------------------------------------------------------------------------------
186 -- This method is invoked from Task workflow based on the action choosen from the task approval notification.
187 -- This procedure in turn calls PA_TASKS_MAINT_PUB.CREATE_TASK to create entries into the pa_tasks table
188 -- Input parameters
189 -- Parameters Type Required Description
190 -- itemtype VARCHAR2 YES Workflow Item type
191 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
192 -- actid NUMBER YES Action Id
193 -- funcmode VARCHAR2 YES Function Mode
194 -- Out parameters
195 -- Parameters Type Required Description
196 -- resultout VARCHAR2 YES Result of the particular function
197 ----------------------------------------------------------------------------------------------------------*/
198 PROCEDURE Post_Task (itemtype IN VARCHAR2
199 ,itemkey IN VARCHAR2
200 ,actid IN NUMBER
201 ,funcmode IN VARCHAR2
202 ,resultout OUT NOCOPY VARCHAR2);
203
204 /*---------------------------------------------------------------------------------------------------------
205 -- This method is invoked from Task workflow if the parent task of the submitted task is not yet approved.
206 -- This procedure marks task as 'Pending' for its parent task approval in pa_proj_elements.
207 -- Input parameters
208 -- Parameters Type Required Description
209 -- itemtype VARCHAR2 YES Workflow Item type
210 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
211 -- actid NUMBER YES Action Id
212 -- funcmode VARCHAR2 YES Function Mode
213 -- Out parameters
214 -- Parameters Type Required Description
215 -- resultout VARCHAR2 YES Result of the particular function
216 ----------------------------------------------------------------------------------------------------------*/
217 PROCEDURE Update_Task_Status(itemtype IN VARCHAR2
218 ,itemkey IN VARCHAR2
219 ,actid IN NUMBER
220 ,funcmode IN VARCHAR2
221 ,resultout OUT NOCOPY VARCHAR2);
222
223 /*---------------------------------------------------------------------------------------------------------
224 -- This method is invoked from Task workflow based on the action choosen from the task approval notification.
225 -- We call PA_TASK_PUB1.Delete_Task_Version to delete the submitted task and its child tasks (if exists).
226 -- Input parameters
227 -- Parameters Type Required Description
228 -- itemtype VARCHAR2 YES Workflow Item type
229 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
230 -- actid NUMBER YES Action Id
231 -- funcmode VARCHAR2 YES Function Mode
232 -- Out parameters
233 -- Parameters Type Required Description
234 -- resultout VARCHAR2 YES Result of the particular function
235 ----------------------------------------------------------------------------------------------------------*/
236
237 PROCEDURE Delete_Task(itemtype IN VARCHAR2
238 ,itemkey IN VARCHAR2
239 ,actid IN NUMBER
240 ,funcmode IN VARCHAR2
241 ,resultout OUT NOCOPY VARCHAR2);
242
243 FUNCTION show_error(p_error_stack IN VARCHAR2,
244 p_error_stage IN VARCHAR2,
245 p_error_message IN VARCHAR2,
246 p_arg1 IN VARCHAR2 DEFAULT null,
247 p_arg2 IN VARCHAR2 DEFAULT null) RETURN VARCHAR2;
248
249
250 /*---------------------------------------------------------------------------------------------------------
251 -- This method is invoked from Change order workflow to verify the task used is already approved or not.
252 -- Input parameters
253 -- Parameters Type Required Description
254 -- itemtype VARCHAR2 YES Workflow Item type
255 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
256 -- actid NUMBER YES Action Id
257 -- funcmode VARCHAR2 YES Function Mode
258 -- Out parameters
259 -- Parameters Type Required Description
260 -- resultout VARCHAR2 YES Result of the particular function
261 ----------------------------------------------------------------------------------------------------------*/
262 PROCEDURE Verify_Task_Status
263 (itemtype IN VARCHAR2
264 ,itemkey IN VARCHAR2
265 ,actid IN NUMBER
266 ,funcmode IN VARCHAR2
267 ,resultout OUT NOCOPY VARCHAR2);
268
269 /*---------------------------------------------------------------------------------------------------------
270 -- This method is invoked from Change order workflow to mark change order status to PENDING, in case
271 -- if the task used in this Change Order is not yet approved.
272 -- Input parameters
273 -- Parameters Type Required Description
274 -- itemtype VARCHAR2 YES Workflow Item type
275 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
276 -- actid NUMBER YES Action Id
277 -- funcmode VARCHAR2 YES Function Mode
278 -- Out parameters
279 -- Parameters Type Required Description
280 -- resultout VARCHAR2 YES Result of the particular function
281 ----------------------------------------------------------------------------------------------------------*/
282 PROCEDURE Mark_CO_Status
283 (itemtype IN VARCHAR2
284 ,itemkey IN VARCHAR2
285 ,actid IN NUMBER
286 ,funcmode IN VARCHAR2
287 ,resultout OUT NOCOPY VARCHAR2);
288
289 /*---------------------------------------------------------------------------------------------------------
290 -- This method is being invoked from Tasks workflow to verify the if the submitted task is last task in
291 -- the hierarchy of the task for approval, and if so, raise notification for any change document which uses
292 -- this task.
293 -- Input parameters
294 -- Parameters Type Required Description
295 -- itemtype VARCHAR2 YES Workflow Item type
296 -- itemkey VARCHAR2 YES Item Key -> Unique identifier of the run
297 -- actid NUMBER YES Action Id
298 -- funcmode VARCHAR2 YES Function Mode
299 -- Out parameters
300 -- Parameters Type Required Description
301 -- resultout VARCHAR2 YES Result of the particular function
302 ----------------------------------------------------------------------------------------------------------*/
303 PROCEDURE Is_Last_Task(itemtype IN VARCHAR2
304 ,itemkey IN VARCHAR2
305 ,actid IN NUMBER
306 ,funcmode IN VARCHAR2
307 ,resultout OUT NOCOPY VARCHAR2);
308
309 END PA_TASK_WORKFLOW_PKG;
[Home] [Help]