1 PACKAGE JTF_EC_WORKFLOW_PKG AUTHID CURRENT_USER as
2 /* $Header: jtfecwfs.pls 120.4.12020000.2 2012/07/25 14:55:59 aditysin ship $ */
3 /*#
4 * This is the private interface to the JTF Escalation Management.
5 * This Interface is used for handling workflow notifications.
6 *
7 * @rep:scope private
8 * @rep:product JTF
9 * @rep:lifecycle active
10 * @rep:displayname Escalation Management
11 * @rep:compatibility S
12 * @rep:category BUSINESS_ENTITY JTA_ESCALATION
13 */
14
15 -- Start of comments
16 -- API name : JTF_EC_WORKFLOW_PKG
17 -- Type : Private.
18 -- Function : Private package used from JTFEC workflow item - sends -- notifications for Reactive Escalation module.
19 -- Pre-reqs : None.
20 -- Parameters :
21 -- name direction type required?
22 -- ---- --------- ---- ---------
23 -- p_api_version IN NUMBER required
24 -- p_init_msg_list IN VARCHAR2 optional DEFAULT fnd_api.g_false
25 -- p_commit IN VARCHAR2 optional DEFAULT fnd_api.g_false
26 -- x_return_status OUT VARCHAR2 required
27 -- x_msg_count OUT NUMBER required
31 -- p_owner_changed IN VARCHAR2 optional value 'Y' indicates that the owner is changed
28 -- x_msg_data OUT VARCHAR2 required
29 -- p_task_id IN NUMBER required
30 -- p_doc_created IN VARCHAR2 optional value 'Y' indicates that the document is created
32 -- p_level_changed IN VARCHAR2 optional value 'Y' indicates that the level is changed
33 -- p_status_changed IN VARCHAR2 optional value 'Y' indicates that the status is changed
34 -- p_target_date_changed IN VARCHAR2 optional value 'Y' indicates that the target_date is changed
35 -- p_old_owner_id IN NUMBER optional, required with OWNER_CHANGED event
36 -- p_old_level IN VARCHAR2 optional, required with LEVEL_CHANGED event
37 -- p_old_status_id IN NUMBER optional, required with STATUS_CHANGED event
38 -- p_old_target_date IN DATE optional, required with TARGET_DATE_CHANGED event
39 -- p_wf_process_name IN VARCHAR2 optional -- DEFAULT 'ESC_NOTIF_PROCESS'
40 -- p_wf_item_type_name IN VARCHAR2
41 -- DEFAULT 'JTFEC'
42 -- x_wf_process_id OUT NUMBER required
43 --
44 -- Version : Current version 1.0
45 --
46 -- Notes :
47 --------------------------------------------------------------------------------
48 -- Currently we support the following events:
49 -- =============
50 -- OWNER_CHANGED
51 -- LEVEL_CHANGED
52 -- ESC_DOC_CREATED
53 -- STATUS_CHANGED
54 -- TARGET_DATE_CHANGED
55 --
56 ---------------------------------------------------------------------------------
57 --
58 -- End of comments
59
60 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_EC_WORKFLOW_PKG';
61 jtf_resc_item_type CONSTANT VARCHAR2(8) := 'JTFEC';
62 jtf_resc_main_process CONSTANT VARCHAR2(30) := 'ESC_NOTIF_PROCESS';
63 g_notif_not_sent VARCHAR2(2000) := NULL;
64
65 --Record type added for ER 7032664
66 TYPE esc_rec_type IS RECORD(
67 task_id NUMBER,
68 doc_created VARCHAR2(1),
69 owner_changed VARCHAR2(1),
70 owner_type_changed VARCHAR2(1),
71 level_changed VARCHAR2(1),
72 status_changed VARCHAR2(1),
73 target_date_changed VARCHAR2(1),
74 old_owner_id NUMBER,
75 old_owner_type_code VARCHAR2(30),
76 old_level VARCHAR2(30),
77 old_status_id NUMBER,
78 old_target_date DATE
79 );
80
81
82 TYPE nlist_rec_type is RECORD (
83 name wf_users.name%TYPE := FND_API.G_MISS_CHAR,
84 display_name wf_users.display_name%TYPE := FND_API.G_MISS_CHAR,
85 email_address wf_users.email_address%TYPE := FND_API.G_MISS_CHAR);
86
87
88 TYPE task_details_rec_type is RECORD(
89 task_name jtf_tasks_vl.task_name%TYPE,
90 task_number jtf_tasks_vl.task_number%TYPE,
91 description jtf_tasks_vl.description%TYPE,
92 owner_code jtf_tasks_vl.owner_type_code%TYPE,
93 owner_id jtf_tasks_vl.owner_id%TYPE,
94 escalation_level jtf_tasks_vl.escalation_level%TYPE,
95 task_status_id jtf_tasks_vl.task_status_id%TYPE,
96 target_date jtf_tasks_vl.planned_end_date%TYPE,
97 date_opened jtf_tasks_vl.creation_date%TYPE,
98 date_changed jtf_tasks_vl.last_update_date%TYPE,
99 update_id jtf_tasks_vl.last_updated_by%TYPE,
100 create_id jtf_tasks_vl.created_by%TYPE);
101
102
103 TYPE nlist_tbl_type is TABLE of nlist_rec_type
104 INDEX BY BINARY_INTEGER;
105
106 G_Miss_NotifList nlist_tbl_type;
107 G_Miss_Nlist_Rec nlist_rec_type;
108
109 NotifList nlist_tbl_type;
110
111
112 /*#
113 * Starts the workflow for the resources related to the Escalation
114 *
115 * @param p_api_version the standard API version number
116 * @param p_init_msg_list the standard API flag allows API callers to request
117 * that the API does the initialization of the message list on their behalf.
118 * By default, the message list will not be initialized.
119 * @param p_commit the standard API flag is used by API callers to ask
120 * the API to commit on their behalf after performing its function
121 * By default, the commit will not be performed.
122 * @param x_return_status the parameter that returns the result of all the operations performed.
123 * by the API and must have one of the following values:
124 * <LI><Code>FND_API.G_RET_STS_SUCCESS</Code>
125 * <LI><Code>FND_API.G_RET_STS_ERROR</Code>
126 * <LI><Code>FND_API.G_RET_STS_UNEXP_ERROR</Code>
127 * @param x_msg_data the parameter that returns the FND Message in encoded format.
128 * @param x_msg_count the parameter that returns the number of messages in the FND message list.
129 * @param p_task_id the escalation id
130 * @param p_doc_created the document created flag
131 * @param p_owner_changed the owner changed flag
132 * @param p_owner_type_changed the owner type changed flag
133 * @param p_level_changed the level changed flag
134 * @param p_status_changed the status changed flag
135 * @param p_target_date_changed the target date changed flag
136 * @param p_old_owner_id the old owner id
137 * @param p_old_owner_type_code the old owner type code
138 * @param p_old_level the old level
139 * @param p_old_status_id the old status id
140 * @param p_old_target_date the old target date
141 * @param p_wf_process_name the name of the workflow process
142 * @param p_wf_item_type_name the name of the workflow item type
143 * @param x_notif_not_sent the parameter that return the flag on notifications sent
144 * @param x_wf_process_id the parameter that returns the workflow process id
145 * @rep:scope private
146 * @rep:lifecycle active
147 * @rep:displayname Start Workflow Notifications
148 * @rep:compatibility S
149 */
150 PROCEDURE Start_Resc_Workflow(
151 p_api_version IN NUMBER,
155 x_msg_count OUT NOCOPY NUMBER,
152 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
153 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
154 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_data OUT NOCOPY VARCHAR2,
157 p_task_id IN NUMBER,
158 p_doc_created IN VARCHAR2 := FND_API.G_MISS_CHAR,
159 p_owner_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
160 p_owner_type_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
161 p_level_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
162 p_status_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
163 p_target_date_changed IN VARCHAR2 := FND_API.G_MISS_CHAR,
164 p_old_owner_id IN NUMBER := FND_API.G_MISS_NUM,
165 p_old_owner_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
166 p_old_level IN VARCHAR2 := FND_API.G_MISS_CHAR,
167 p_old_status_id IN NUMBER := FND_API.G_MISS_NUM,
168 p_old_target_date IN DATE := FND_API.G_MISS_DATE,
169 p_wf_process_name IN VARCHAR2 DEFAULT 'ESC_NOTIF_PROCESS',
170 p_wf_item_type_name IN VARCHAR2 DEFAULT 'JTFEC',
171 x_notif_not_sent OUT NOCOPY VARCHAR2,
172 x_wf_process_id OUT NOCOPY NUMBER
173
174 );
175
176 /*#
177 * Checks for the event based on the function mode
178 *
179 * @param itemtype the type of the workflow item
180 * @param itemkey the key of the workflow item
181 * @param actid the activity id
182 * @param funcmode the mode of activity - run / complete / cancel
183 * @param resultout the parameter the returns the status of the event
184 * @rep:scope private
185 * @rep:lifecycle active
186 * @rep:displayname Check Event
187 * @rep:compatibility S
188 */
189 PROCEDURE Check_Event(
190 itemtype IN VARCHAR2,
191 itemkey IN VARCHAR2,
192 actid IN NUMBER,
193 funcmode IN VARCHAR2,
194 resultout OUT NOCOPY VARCHAR2
195 );
196
197 /*#
198 * Sets the Notification Messages
199 *
200 * @param itemtype the type of the workflow item
201 * @param itemkey the key of the workflow item
202 * @param actid the activity id
203 * @param funcmode the mode of activity
204 * @param resultout the parameter the returns the status of the event
205 * @rep:scope private
206 * @rep:lifecycle active
207 * @rep:displayname Set Notification Message
208 * @rep:compatibility S
209 */
210 PROCEDURE Set_Notif_Message(
211 itemtype IN VARCHAR2,
212 itemkey IN VARCHAR2,
213 actid IN NUMBER,
214 funcmode IN VARCHAR2,
215 resultout OUT NOCOPY VARCHAR2
216 );
217
218 /*#
219 * Sets the Notification Performer
220 *
221 * @param itemtype the type of the workflow item
222 * @param itemkey the key of the workflow item
223 * @param actid the activity id
224 * @param funcmode the mode of activity
225 * @param resultout the parameter the returns the status of the event
226 * @rep:scope private
227 * @rep:lifecycle active
228 * @rep:displayname Set Notification Performer
229 * @rep:compatibility S
230 */
231 PROCEDURE Set_Notif_Performer(
232 itemtype IN VARCHAR2,
233 itemkey IN VARCHAR2,
234 actid IN NUMBER,
235 funcmode IN VARCHAR2,
236 resultout OUT NOCOPY VARCHAR2
237 );
238
239 /*#
240 * Gets the document details
241 *
242 * @param p_task_id the escalation id
243 * @param x_doc_type the parameter that returns type of reference document
244 * @param x_doc_number the parameter that returns number of the reference document
245 * @param x_doc_owner_name the parameter that returns owner name of the reference document
246 * @param x_doc_details_t the parameter that returns the document details in text format
247 * @param x_doc_details_h the paramter that returns the document details in html format
248 * @param x_return_status the parameter that returns the result of all the operations performed.
249 * by the API and must have one of the following values:
250 * <LI><Code>FND_API.G_RET_STS_SUCCESS</Code>
251 * <LI><Code>FND_API.G_RET_STS_ERROR</Code>
252 * <LI><Code>FND_API.G_RET_STS_UNEXP_ERROR</Code>
253 * @rep:scope private
254 * @rep:lifecycle active
255 * @rep:displayname Get Document Details
256 * @rep:compatibility S
257 */
258 PROCEDURE get_doc_details(
259 p_task_id IN VARCHAR2,
260 x_doc_type OUT NOCOPY VARCHAR2,
261 x_doc_number OUT NOCOPY VARCHAR2,
262 x_doc_owner_name OUT NOCOPY VARCHAR2,
263 x_doc_details_t OUT NOCOPY VARCHAR2,
264 x_doc_details_h OUT NOCOPY VARCHAR2,
265 x_return_status OUT NOCOPY VARCHAR2);
266
267 --Start of code for ER 7032664
268
269 Procedure Raise_Esc_Create_Event(P_TASK_ID IN NUMBER);
270
271 Procedure Raise_Esc_Update_Event(
272 task_id IN NUMBER,
273 doc_created IN VARCHAR2,
274 owner_changed IN VARCHAR2,
275 owner_type_changed IN VARCHAR2,
276 level_changed IN VARCHAR2,
277 status_changed IN VARCHAR2,
278 target_date_changed IN VARCHAR2,
279 old_owner_id IN NUMBER,
280 old_owner_type_code IN VARCHAR2,
281 old_level IN VARCHAR2,
282 old_status_id IN NUMBER,
283 old_target_date IN DATE
284 );
285
286 Procedure Raise_Esc_Update_Event(P_ESC_REC IN JTF_EC_WORKFLOW_PKG.esc_rec_type);
287
288 FUNCTION create_esc_notif_subs (
289 p_subscription_guid IN RAW,
290 p_event IN OUT NOCOPY wf_event_t
291 )
292 RETURN VARCHAR2;
293
294 FUNCTION update_esc_notif_subs (
295 p_subscription_guid IN RAW,
296 p_event IN OUT NOCOPY wf_event_t
297 )
298 RETURN VARCHAR2;
299
300 --End of code for ER 7032664
301
302 END JTF_EC_WORKFLOW_PKG;