1 PACKAGE OKS_WF_K_PROCESS_PVT AS
2 /* $Header: OKSVKWFS.pls 120.11.12010000.2 2008/10/22 12:52:07 ssreekum ship $ */
3
4 TYPE WF_ATTR_DETAILS IS RECORD
5 (
6 CONTRACT_ID NUMBER,
7 CONTRACT_NUMBER VARCHAR2(120),
8 CONTRACT_MODIFIER VARCHAR2(120),
9 PROCESS_TYPE VARCHAR2(30), -- Online, Manual or Evergreen
10 -- Valid values are Manual(M), Automatic(A), Yes(Y), No or Not required(N)
11 IRR_FLAG VARCHAR2(5),
12 NEGOTIATION_STATUS VARCHAR2(30),
13 ITEM_KEY WF_ITEMS.ITEM_KEY%TYPE
14 );
15
16 TYPE WF_ATTR_DETAILS_TBL IS TABLE OF WF_ATTR_DETAILS INDEX BY BINARY_INTEGER;
17
18 TYPE email_attr_rec IS RECORD
19 (
20 CONTRACT_ID NUMBER,
21 ITEM_KEY WF_ITEMS.ITEM_KEY%TYPE,
22 EMAIL_TYPE VARCHAR2(10),
23 TO_EMAIL VARCHAR2(4000),
24 SENDER_EMAIL VARCHAR2(4000),
25 EMAIL_SUBJECT VARCHAR2(4000),
26 IH_SUBJECT VARCHAR2(4000),
27 IH_MESSAGE VARCHAR2(4000),
28 EMAIL_BODY_ID NUMBER,
29 ATTACHMENT_ID NUMBER,
30 ATTACHMENT_NAME VARCHAR2(150),
31 CONTRACT_STATUS VARCHAR2(30)
32 );
33
34 TYPE notif_attr_rec IS RECORD
35 (
36 CONTRACT_ID NUMBER,
37 ITEM_KEY WF_ITEMS.ITEM_KEY%TYPE,
38 PERFORMER VARCHAR2(100),
39 NTF_TYPE VARCHAR2(30),
40 NTF_SUBJECT VARCHAR2(2000),
41 MESSAGE1 VARCHAR2(2000),
42 MESSAGE2 VARCHAR2(2000),
43 MESSAGE3 VARCHAR2(2000),
44 MESSAGE4 VARCHAR2(2000),
45 MESSAGE5 VARCHAR2(2000),
46 MESSAGE6 VARCHAR2(2000),
47 MESSAGE7 VARCHAR2(2000),
48 MESSAGE8 VARCHAR2(2000),
49 MESSAGE9 VARCHAR2(2000),
50 MESSAGE10 VARCHAR2(2000),
51 SUBJECT VARCHAR2(4000),
52 ACCEPT_DECLINE_BY VARCHAR2(4000),
53 REQ_ASSIST_ROLE VARCHAR2(100),
54 MSGS_FROM_STACK_YN VARCHAR2(1)
55 );
56
57 PROCEDURE is_online_k_yn
58 (
59 p_api_version IN NUMBER,
60 p_init_msg_list IN VARCHAR2,
61 p_contract_id IN NUMBER,
62 p_item_key IN VARCHAR2,
63 x_online_yn OUT NOCOPY VARCHAR2,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER,
66 x_msg_data OUT NOCOPY VARCHAR2
67 );
68
69 PROCEDURE complete_activity
70 (
71 p_api_version IN NUMBER,
72 p_init_msg_list IN VARCHAR2,
73 p_contract_id IN NUMBER,
74 p_item_key IN VARCHAR2,
75 p_resultout IN VARCHAR2,
76 p_process_status IN VARCHAR2,
77 p_activity_name IN VARCHAR2,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER
81 );
82
83 PROCEDURE customer_accept_quote
84 (
85
86 p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2,
88 p_commit IN VARCHAR2 DEFAULT 'F',
89 p_contract_id IN NUMBER,
90 p_item_key IN VARCHAR2,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_data OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER
94 );
95
96 PROCEDURE customer_decline_quote
97 (
98
99 p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2,
101 p_commit IN VARCHAR2 DEFAULT 'F',
102 p_contract_id IN NUMBER,
103 p_item_key IN VARCHAR2,
104 p_reason_code IN VARCHAR2,
105 p_comments IN VARCHAR2,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_data OUT NOCOPY VARCHAR2,
108 x_msg_count OUT NOCOPY NUMBER
109 );
110
111 PROCEDURE customer_request_assistance
112 (
113
114 p_api_version IN NUMBER,
115 p_init_msg_list IN VARCHAR2,
116 p_commit IN VARCHAR2 DEFAULT 'F',
117 p_contract_id IN NUMBER,
118 p_item_key IN VARCHAR2,
119 p_to_email IN VARCHAR2,
120 p_cc_email IN VARCHAR2,
121 p_subject IN VARCHAR2,
122 p_message IN VARCHAR2,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER
126 );
127
128 PROCEDURE set_notification_attr
129 (
130 itemtype IN VARCHAR2,
131 itemkey IN VARCHAR2,
132 actid IN NUMBER,
133 funcmode IN VARCHAR2,
134 resultout OUT NOCOPY VARCHAR2
135 );
136
137 PROCEDURE set_notification_attr
138 (
139 p_api_version IN NUMBER,
140 p_init_msg_list IN VARCHAR2,
141 p_contract_id IN NUMBER,
142 p_performer IN VARCHAR2,
143 p_notif_type IN VARCHAR2,
144 p_notif_subject IN VARCHAR2,
145 p_message1 IN VARCHAR2,
146 p_message2 IN VARCHAR2,
147 p_message3 IN VARCHAR2,
148 p_message4 IN VARCHAR2,
149 p_message5 IN VARCHAR2,
150 p_message6 IN VARCHAR2,
151 p_message7 IN VARCHAR2,
152 p_message8 IN VARCHAR2,
153 p_message9 IN VARCHAR2,
154 p_message10 IN VARCHAR2,
155 p_subject IN VARCHAR2,
156 p_msgs_from_stack_yn IN VARCHAR2,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY VARCHAR2,
159 x_msg_data OUT NOCOPY VARCHAR2
160 );
161
162 PROCEDURE launch_k_process_wf
163 (
164 p_api_version IN NUMBER,
165 p_init_msg_list IN VARCHAR2,
166 p_commit IN VARCHAR2 DEFAULT 'F',
167 p_wf_attributes IN OKS_WF_K_PROCESS_PVT.WF_ATTR_DETAILS,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_count OUT NOCOPY NUMBER,
170 x_msg_data OUT NOCOPY VARCHAR2
171 );
172
173 PROCEDURE submit_for_approval
174 (
175 p_api_version IN NUMBER,
176 p_init_msg_list IN VARCHAR2,
177 p_commit IN VARCHAR2 DEFAULT 'F',
178 p_contract_id IN NUMBER,
179 p_item_key IN VARCHAR2,
180 p_validate_yn IN VARCHAR2,
181 p_qa_required_yn IN VARCHAR2,
182 x_negotiation_status OUT NOCOPY VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2
186 );
187
188 PROCEDURE cancel_contract
189 (
190
191 p_api_version IN NUMBER,
192 p_init_msg_list IN VARCHAR2,
193 p_commit IN VARCHAR2 DEFAULT 'F',
194 p_contract_id IN NUMBER,
195 p_item_key IN VARCHAR2,
196 p_cancellation_reason IN VARCHAR2,
197 p_cancellation_date IN DATE,
198 p_cancel_source IN VARCHAR2,
199 p_comments IN VARCHAR2,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_data OUT NOCOPY VARCHAR2,
202 x_msg_count OUT NOCOPY NUMBER
203 );
204
205 PROCEDURE assign_new_qto_contact
206 (
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2,
209 p_commit IN VARCHAR2 DEFAULT 'F',
210 p_contract_id IN NUMBER,
211 p_item_key IN VARCHAR2,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_data OUT NOCOPY VARCHAR2,
214 x_msg_count OUT NOCOPY NUMBER
215 );
216
217 PROCEDURE clean_wf
218 (
219 p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2,
221 p_commit IN VARCHAR2 DEFAULT 'F',
222 p_contract_id IN NUMBER,
223 p_item_key IN VARCHAR2,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_data OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER
227 );
228
229 PROCEDURE publish_to_customer
230 (
231 p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2,
233 p_commit IN VARCHAR2 DEFAULT 'F',
234 p_contract_id IN NUMBER,
235 p_item_key IN VARCHAR2,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2
239 );
240
241 PROCEDURE initialize
242 (
243 itemtype IN VARCHAR2,
244 itemkey IN VARCHAR2,
245 actid IN NUMBER,
246 funcmode IN VARCHAR2,
247 resultout OUT NOCOPY VARCHAR2
248 );
249
250 PROCEDURE get_old_wf_status
251 (
252 itemtype IN VARCHAR2,
253 itemkey IN VARCHAR2,
254 actid IN NUMBER,
255 funcmode IN VARCHAR2,
256 resultout OUT NOCOPY VARCHAR2
257 );
258
259 procedure email_mute
260 (
261 itemtype IN VARCHAR2,
262 itemkey IN VARCHAR2,
263 actid IN number,
264 funcmode IN VARCHAR2,
265 resultout OUT NOCOPY VARCHAR2
266 );
267
268 PROCEDURE get_process_type
269 (
270 itemtype IN VARCHAR2,
271 itemkey IN VARCHAR2,
272 actid IN NUMBER,
273 funcmode IN VARCHAR2,
274 resultout OUT NOCOPY VARCHAR2
275 );
276
277 PROCEDURE salesrep_action
278 (
279 itemtype IN VARCHAR2,
280 itemkey IN VARCHAR2,
281 actid IN NUMBER,
282 funcmode IN VARCHAR2,
283 resultout OUT NOCOPY VARCHAR2
284 );
285
286 PROCEDURE check_qa
287 (
288 itemtype IN VARCHAR2,
289 itemkey IN VARCHAR2,
290 actid IN NUMBER,
291 funcmode IN VARCHAR2,
292 resultout OUT NOCOPY VARCHAR2
293 );
294
295 PROCEDURE process_negotiation_status
296 (
297 itemtype IN VARCHAR2,
298 itemkey IN VARCHAR2,
299 actid IN NUMBER,
300 funcmode IN VARCHAR2,
301 resultout OUT NOCOPY VARCHAR2
302 );
303
304 PROCEDURE update_negotiation_status
305 (
306 p_api_version IN NUMBER,
307 p_init_msg_list IN VARCHAR2,
308 p_commit IN VARCHAR2 DEFAULT 'F',
309 p_chr_id IN NUMBER,
310 p_negotiation_status IN VARCHAR2,
311 x_return_status OUT NOCOPY VARCHAR2,
312 x_msg_count OUT NOCOPY NUMBER,
313 x_msg_data OUT NOCOPY VARCHAR2
314 );
315
316 PROCEDURE is_approval_required
317 (
318 itemtype IN VARCHAR2,
319 itemkey IN VARCHAR2,
320 actid IN NUMBER,
321 funcmode IN VARCHAR2,
322 resultout OUT NOCOPY VARCHAR2
323 );
324
325 PROCEDURE get_approval_flag
326 (
327 itemtype IN VARCHAR2,
328 itemkey IN VARCHAR2,
329 actid IN NUMBER,
330 funcmode IN VARCHAR2,
331 resultout OUT NOCOPY VARCHAR2
332 );
333
334 PROCEDURE is_submit_for_approval_allowed
335 (
336 p_api_version IN NUMBER,
337 p_init_msg_list IN VARCHAR2,
338 p_contract_id IN NUMBER,
339 p_item_key IN VARCHAR2,
340 x_activity_name OUT NOCOPY VARCHAR2,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER,
343 x_msg_data OUT NOCOPY VARCHAR2
344 );
345
346 PROCEDURE customer_action
347 (
348 itemtype IN VARCHAR2,
349 itemkey IN VARCHAR2,
350 actid IN NUMBER,
351 funcmode IN VARCHAR2,
352 resultout OUT NOCOPY VARCHAR2
353 );
354
355 PROCEDURE launch_approval_wf
356 (
357 itemtype IN VARCHAR2,
358 itemkey IN VARCHAR2,
359 actid IN NUMBER,
360 funcmode IN VARCHAR2,
361 resultout OUT NOCOPY VARCHAR2
362 );
363
364 PROCEDURE accept_quote
365 (
366
367 p_api_version IN NUMBER,
368 p_init_msg_list IN VARCHAR2,
369 p_commit IN VARCHAR2 DEFAULT 'F',
370 p_contract_id IN NUMBER,
371 p_item_key IN VARCHAR2,
372 p_accept_confirm_yn IN VARCHAR2,
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_msg_data OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER
376 );
377
378 PROCEDURE activate_contract
379 (
380 itemtype IN VARCHAR2,
381 itemkey IN VARCHAR2,
382 actid IN NUMBER,
383 funcmode IN VARCHAR2,
384 resultout OUT NOCOPY VARCHAR2
385 );
386
387 PROCEDURE set_email_attr
388 (
389 itemtype IN VARCHAR2,
390 itemkey IN VARCHAR2,
391 actid IN NUMBER,
392 funcmode IN VARCHAR2,
393 resultout OUT NOCOPY VARCHAR2
394 );
395
396 /*
397 This procesure is a concurrent program, that launches wf for all
398 ENTERED status contracts, that do not have a workflow associated with them
399 and have not been submitted for approval
400 */
401 PROCEDURE launch_wf_conc_prog
402 (
403 ERRBUF OUT NOCOPY VARCHAR2,
404 RETCODE OUT NOCOPY NUMBER
405 );
406
407 /* Bulk API for launching wf for ENTERED status Service Contracts
408 This procedure launches the workflow for a Service Contract. From R12 onwards every
409 Service Contract when created has a workflow associated with it, that routes the
410 contract till it is activated.
411
412 Parameters
413 p_wf_attributes_tbl : table of records containg the details of the workflow to be
414 launched
415 p_update_item_key : Y|N indicating if oks_k_headers_b and oks_k_headers_bh are to be
416 updated with the passed item keys
417
418 Rules for input record fiels
419 1. Contract_id must be passed, if not passed the record is ignored
420 2. Contract number and modifier must be passed, they are set as item attributes for the
421 workflow
422 3. Process_type and irr_flag are optional, they are stamped as workflow item
423 attributes. Defaulted as - procees_type = NSR and irr_flag = Y
424 4. Negotiation_status is optional, if NULL or PREDRAFT, it is defaulted as DRAFT. It is
425 stamped as workflow item attribute.
426 5. Item_key is optional, if not passed it is defaulted as
427 contract_id || to_char(sysdate, 'YYYYMMDDHH24MISS').
428
429 */
430 PROCEDURE launch_k_process_wf_blk
431 (
432 p_api_version IN NUMBER DEFAULT 1.0,
433 p_init_msg_list IN VARCHAR2 DEFAULT 'F',
434 p_commit IN VARCHAR2 DEFAULT 'F',
435 p_wf_attributes_tbl IN WF_ATTR_DETAILS_TBL,
436 p_update_item_key IN VARCHAR2 DEFAULT 'Y',
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2
440 );
441
442 END OKS_WF_K_PROCESS_PVT;