1 PACKAGE OKS_WF_K_PROCESS_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKSVKWFS.pls 120.11.12010000.3 2009/05/14 09:41:48 cgopinee 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 /*cgopinee bugfix for 8361496*/
278 PROCEDURE get_curr_conv_date_validity
279 (
280 itemtype IN VARCHAR2,
281 itemkey IN VARCHAR2,
282 actid IN NUMBER,
283 funcmode IN VARCHAR2,
284 resultout OUT NOCOPY VARCHAR2
285 );
286 PROCEDURE salesrep_action
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 check_qa
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 process_negotiation_status
305 (
306 itemtype IN VARCHAR2,
307 itemkey IN VARCHAR2,
308 actid IN NUMBER,
309 funcmode IN VARCHAR2,
310 resultout OUT NOCOPY VARCHAR2
311 );
312
313 PROCEDURE update_negotiation_status
314 (
315 p_api_version IN NUMBER,
316 p_init_msg_list IN VARCHAR2,
317 p_commit IN VARCHAR2 DEFAULT 'F',
318 p_chr_id IN NUMBER,
319 p_negotiation_status IN VARCHAR2,
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_msg_count OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2
323 );
324
325 PROCEDURE is_approval_required
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 get_approval_flag
335 (
336 itemtype IN VARCHAR2,
337 itemkey IN VARCHAR2,
338 actid IN NUMBER,
339 funcmode IN VARCHAR2,
340 resultout OUT NOCOPY VARCHAR2
341 );
342
343 PROCEDURE is_submit_for_approval_allowed
344 (
345 p_api_version IN NUMBER,
346 p_init_msg_list IN VARCHAR2,
347 p_contract_id IN NUMBER,
348 p_item_key IN VARCHAR2,
349 x_activity_name OUT NOCOPY VARCHAR2,
350 x_return_status OUT NOCOPY VARCHAR2,
351 x_msg_count OUT NOCOPY NUMBER,
352 x_msg_data OUT NOCOPY VARCHAR2
353 );
354
355 PROCEDURE customer_action
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 launch_approval_wf
365 (
366 itemtype IN VARCHAR2,
367 itemkey IN VARCHAR2,
368 actid IN NUMBER,
369 funcmode IN VARCHAR2,
370 resultout OUT NOCOPY VARCHAR2
371 );
372
373 PROCEDURE accept_quote
374 (
375
376 p_api_version IN NUMBER,
377 p_init_msg_list IN VARCHAR2,
378 p_commit IN VARCHAR2 DEFAULT 'F',
379 p_contract_id IN NUMBER,
380 p_item_key IN VARCHAR2,
381 p_accept_confirm_yn IN VARCHAR2,
382 x_return_status OUT NOCOPY VARCHAR2,
383 x_msg_data OUT NOCOPY VARCHAR2,
384 x_msg_count OUT NOCOPY NUMBER
385 );
386
387 PROCEDURE activate_contract
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 PROCEDURE set_email_attr
397 (
398 itemtype IN VARCHAR2,
399 itemkey IN VARCHAR2,
400 actid IN NUMBER,
401 funcmode IN VARCHAR2,
402 resultout OUT NOCOPY VARCHAR2
403 );
404
405 /*
406 This procesure is a concurrent program, that launches wf for all
407 ENTERED status contracts, that do not have a workflow associated with them
408 and have not been submitted for approval
409 */
410 PROCEDURE launch_wf_conc_prog
411 (
412 ERRBUF OUT NOCOPY VARCHAR2,
413 RETCODE OUT NOCOPY NUMBER
414 );
415
416 /* Bulk API for launching wf for ENTERED status Service Contracts
417 This procedure launches the workflow for a Service Contract. From R12 onwards every
418 Service Contract when created has a workflow associated with it, that routes the
419 contract till it is activated.
420
421 Parameters
422 p_wf_attributes_tbl : table of records containg the details of the workflow to be
423 launched
424 p_update_item_key : Y|N indicating if oks_k_headers_b and oks_k_headers_bh are to be
425 updated with the passed item keys
426
427 Rules for input record fiels
428 1. Contract_id must be passed, if not passed the record is ignored
429 2. Contract number and modifier must be passed, they are set as item attributes for the
430 workflow
431 3. Process_type and irr_flag are optional, they are stamped as workflow item
432 attributes. Defaulted as - procees_type = NSR and irr_flag = Y
433 4. Negotiation_status is optional, if NULL or PREDRAFT, it is defaulted as DRAFT. It is
434 stamped as workflow item attribute.
435 5. Item_key is optional, if not passed it is defaulted as
436 contract_id || to_char(sysdate, 'YYYYMMDDHH24MISS').
437
438 */
439 PROCEDURE launch_k_process_wf_blk
440 (
441 p_api_version IN NUMBER DEFAULT 1.0,
442 p_init_msg_list IN VARCHAR2 DEFAULT 'F',
443 p_commit IN VARCHAR2 DEFAULT 'F',
444 p_wf_attributes_tbl IN WF_ATTR_DETAILS_TBL,
445 p_update_item_key IN VARCHAR2 DEFAULT 'Y',
446 x_return_status OUT NOCOPY VARCHAR2,
447 x_msg_count OUT NOCOPY NUMBER,
448 x_msg_data OUT NOCOPY VARCHAR2
449 );
450
451 END OKS_WF_K_PROCESS_PVT;