1 Package hr_transaction_ss AUTHID CURRENT_USER as
2 /* $Header: hrtrnwrs.pkh 120.1.12010000.2 2010/03/14 18:51:23 ckondapi ship $ */
3 -- Global variables
4 g_date_format varchar2(10) := 'RRRR/MM/DD';
5 -- ---------------------------------------------------------------------------
6 -- Following are the Bulk Fetch Type Declaration
7 -- ---------------------------------------------------------------------------
8 TYPE NumberTblType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
9 TYPE VarChar30TblType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
10 TYPE VarChar2000TblType IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
11 TYPE DateTblType IS TABLE OF DATE INDEX BY BINARY_INTEGER;
12
13 TYPE transaction_data IS RECORD
14 (NAME VarChar30TblType
15 ,VARCHAR2_VALUE VarChar2000TblType
16 ,NUMBER_VALUE NumberTblType
17 ,DATE_VALUE DateTblType);
18 g_oracle_db_version CONSTANT NUMBER := hr_general2.get_oracle_db_version;
19 -- ---------------------------------------------------------------------------
20 -- Following two variables are used by save_transaction_step procedure.
21 -- ---------------------------------------------------------------------------
22
23 TYPE transaction_row IS RECORD
24 (param_name VARCHAR2(200)
25 ,param_value LONG
26 ,param_original_value LONG --ns
27 ,param_data_type VARCHAR2(200));
28
29 TYPE transaction_table IS TABLE OF transaction_row INDEX BY BINARY_INTEGER;
30 -- ----------------------------------------------------------------------------
31 -- get workflow attribute p_effective_date.
32 -- ----------------------------------------------------------------------------
33
34 FUNCTION get_wf_effective_date
35 (p_transaction_step_id in number)
36 RETURN varchar2;
37
38 -- ----------------------------------------------------------------------------
39 -- |---------------------------< get_transaction_id >-------------------------|
40 -- ----------------------------------------------------------------------------
41 -- {Start Of Comments}
42 --
43 -- Description:
44 -- This function returns the TRANSACTION_ID for the specified item type
45 -- and key exist. If the TRANSACTION_ID does not exist a NULL value is
46 -- returned.
47 --
48 -- Pre-Requisities:
49 -- None.
50 --
51 -- In Parameters:
52 -- p_item_type -> The internal name for the item type.
53 -- p_item_key -> A string that represents a primary key generated by the
54 -- application for the item type. The string uniquely
55 -- identifies the item within an item type.
56 --
57 -- Post Success:
58 -- If the TRANSACTION_ID exists for the item type and key then the value
59 -- is returned.
60 -- If the TRANSACTION_ID does not exist then a NULL value is returned.
61 --
62 -- Post Failure:
63 -- None.
64 --
65 -- Developer Implementation Notes:
66 -- None
67 --
68 -- Access Status:
69 -- Internal Development Use Only.
70 --
71 -- {End Of Comments}
72 --
73 -------------------------------------------------------------------------------
74 -- Validate the transaction.
75 -- result returns 'Y' - pass validation
76 -- result returns 'N' - has errors.
77 -------------------------------------------------------------------------------
78 procedure validate_transaction
79 (p_item_type in varchar2
80 ,p_item_key in varchar2
81 ,p_effective_date in varchar2 default null
82 ,p_update_object_version in varchar2 default 'N'
83 ,p_result out nocopy varchar2);
84 -------------------------------------------------------------------------------
85 -- ns 11/06/2003: Bug 3223682: Overloaded version
86 -- Validate the transaction.
87 -- additional parameter p_ignore_warnings
88 -------------------------------------------------------------------------------
89 procedure validate_transaction
90 (p_item_type in varchar2
91 ,p_item_key in varchar2
92 ,p_effective_date in varchar2 default null
93 ,p_update_object_version in varchar2 default 'N'
94 ,p_ignore_warnings in varchar2 default 'N'
95 ,p_result out nocopy varchar2);
96 -------------------------------------------------------------------------------
97 -- set workflow item attribute 'SAVE_FOR_LATER'
98 -- 'S' -- Save for Later
99 -- 'C' -- Returned for Correction
100 -- 'Y' -- Submitted for Approval
101 -- 'W' -- this is the default value. It is for the system crash or system time
102 -- out.
103 -- 'D' -- this is for the deleted suspended process.
104 -- ----------------------------------------------------------------------------
105 procedure set_initial_save_for_later
106 (itemtype in varchar2,
107 itemkey in varchar2,
108 actid in number,
109 funmode in varchar2,
110 result out nocopy varchar2 );
111
112 procedure set_delete_save_for_later
113 (itemtype in varchar2,
114 itemkey in varchar2,
115 actid in number,
116 funmode in varchar2,
117 result out nocopy varchar2 );
118
119 procedure set_save_for_later_status
120 (p_item_type in varchar2
121 ,p_item_key in varchar2
122 ,p_status in varchar2
123 ,p_transaction_id in number default null);
124
125 procedure set_save_for_later
126 (itemtype in varchar2,
127 itemkey in varchar2,
128 actid in number,
129 funmode in varchar2,
130 result out nocopy varchar2 );
131
132 procedure set_return_for_correction
133 (itemtype in varchar2,
134 itemkey in varchar2,
135 actid in number,
136 funmode in varchar2,
137 result out nocopy varchar2 );
138
139 procedure set_submit_for_approval
140 (itemtype in varchar2,
141 itemkey in varchar2,
142 actid in number,
143 funmode in varchar2,
144 result out nocopy varchar2 );
145
146 -- ----------------------------------------------------------------------------
147 function get_transaction_id
148 (p_item_type in varchar2
149 ,p_item_key in varchar2) return number;
150 -- ----------------------------------------------------------------------------
151 -- |----------------------------< start_transaction >-------------------------|
152 -- ----------------------------------------------------------------------------
153 -- {Start Of Comments}
154 --
155 -- Description:
156 -- This procedure initiates a new transaction. When a new transaction is
157 -- initiated the following item attributes are created if they don't already
158 -- exist: TRANSACTION_ID, CURRENT_PERSON_ID, TRANSACTION_PRIVILEGE
159 --
160 -- The TRANSACTION_ID stores the transaction identifier.
161 -- The CURRENT_PERSON_ID stores the person identifier as the workflow
162 -- progresses.
163 -- The TRANSACTION_PRIVILEGE determines if the transaction is PUBLIC or
164 -- PRIVATE.
165 --
166 -- A transaction is created by calling the
167 -- hr_transaction_api.create_transaction business process API.
168 --
169 -- Pre-Requisities:
170 -- Executed from Oracle Workflow.
171 --
172 -- In Parameters:
173 -- All the following IN parameteres are set by Oracle Workflow:
174 -- itemtype -> The internal name for the item type.
175 -- itemkey -> A string that represents a primary key generated by
176 -- the application for the item type. The string uniquely
177 -- identifies the item within an item type.
178 -- actid -> The ID number of the activity that this procedure is
179 -- called from.
180 -- funmode -> The mode of the function activity. Either 'RUN' or
181 -- 'CANCEL'.
182 --
183 -- Post Success:
184 -- A new transaction is created with the following item attributes set:
185 -- TRANSACTION_ID, CURRENT_PERSON_ID, TRANSACTION_PRIVILEGE
186 -- A workflow result of SUCCESS is set
187 --
188 -- Post Failure:
189 -- This procedure can fail under the following circumstances:
190 -- 1) If a TRANSACTION_ID item attribute already exists with a value for
191 -- the item type and key. You cannot start a new transaction if the
192 -- item type and key already have a transaction in progress.
193 -- 2) Validating the login
194 --
195 -- Developer Implementation Notes:
196 -- None
197 --
198 -- Access Status:
199 -- Internal Development Use Only.
200 --
201 -- {End Of Comments}
202 -- ----------------------------------------------------------------------------
203 procedure start_transaction
204 (itemtype in varchar2
205 ,itemkey in varchar2
206 ,actid in number
207 ,funmode in varchar2
208 ,p_login_person_id in number
209 ,p_product_code in varchar2 default null
210 ,p_url in varchar2 default null
211 ,p_status in varchar2 default null
212 ,p_section_display_name in varchar2 default null
213 ,p_function_id in number default null
214 ,p_transaction_ref_table in varchar2 default 'HR_API_TRANSACTIONS'
215 ,p_transaction_ref_id in number default null
216 ,p_transaction_type in varchar2 default null
217 ,p_assignment_id in number default null
218 ,p_api_addtnl_info in varchar2 default null
219 ,p_selected_person_id in number default null
220 ,p_transaction_effective_date in date default null
221 ,p_process_name in varchar2 default null
222 ,p_plan_id in number default null
223 ,p_rptg_grp_id in number default null
224 ,p_effective_date_option in varchar2 default null
225 ,result out nocopy varchar2
226 );
227 -- ----------------------------------------------------------------------------
228 -- |----------------------------< rollback_transaction >----------------------|
229 -- ----------------------------------------------------------------------------
230 -- {Start Of Comments}
231 --
232 -- Description:
233 -- This procedure deletes the transaction values, steps and transaction for
234 -- the specified item type and key.
235 --
236 -- Pre-Requisities:
237 -- Executed from Oracle Workflow.
238 --
239 -- In Parameters:
240 -- All the following IN parameteres are set by Oracle Workflow:
241 -- itemtype -> The internal name for the item type.
242 -- itemkey -> A string that represents a primary key generated by
243 -- the application for the item type. The string uniquely
244 -- identifies the item within an item type.
245 -- actid -> The ID number of the activity that this procedure is
246 -- called from.
247 -- funmode -> The mode of the function activity. Either 'RUN' or
248 -- 'CANCEL'.
249 --
250 -- Post Success:
251 -- The transaction values, steps and transaction will be deleted from
252 -- the transaction schema for the given item type and key.
253 -- A workflow result of SUCCESS is set
254 --
255 -- Post Failure:
256 -- If the deletion of the transaction raises an exception this is treated
257 -- as a system error and raised as such.
258 --
259 -- Developer Implementation Notes:
260 -- None
261 --
262 -- Access Status:
263 -- Internal Development Use Only.
264 --
265 -- {End Of Comments}
266 -- ----------------------------------------------------------------------------
267 procedure rollback_transaction
268 (itemtype in varchar2
269 ,itemkey in varchar2
270 ,actid in number
271 ,funmode in varchar2
272 ,result out nocopy varchar2);
273 -- ----------------------------------------------------------------------------
274 -- |-------------------------< process_web_api_call >-------------------------|
275 -- ----------------------------------------------------------------------------
276 -- {Start Of Comments}
277 --
278 -- Description:
279 -- This procedure will for the specified transaction step and web form API
280 -- name execute the API using dynamic PL/SQL.
281 --
282 -- Pre-Requisities:
283 -- The transaction step and API code must exist.
284 --
285 -- In Parameters:
286 -- p_transaction_step_id -> The transaction step identifier.
287 -- p_api_name -> The API name to be called (e.g.
288 -- hr_emp_marital_web.process_api).
289 -- p_validate -> If set to TRUE all the work
290 -- performed by the API will be
291 -- rolled back. If set to FALSE,
292 -- the work is not rolled back.
293 --
294 -- Post Success:
295 -- The API would be dynamically built, parsed and executed.
296 --
297 -- Post Failure:
298 -- The exception is raised.
299 --
300 -- Developer Implementation Notes:
301 -- None
302 --
303 -- Access Status:
304 -- Internal Development Use Only.
305 --
306 -- {End Of Comments}
307 -- ----------------------------------------------------------------------------
308 procedure process_web_api_call
309 (p_transaction_step_id in number
310 ,p_api_name in varchar2
311 ,p_extra_parameter_name in varchar2 default null
312 ,p_extra_parameter_value in varchar2 default null
313 ,p_validate in boolean default false);
314 -- ----------------------------------------------------------------------------
315 -- |----------------------------< commit_transaction >------------------------|
316 -- ----------------------------------------------------------------------------
317 -- {Start Of Comments}
318 --
319 -- Description:
320 -- This procedure will process each transaction step for the transaction.
321 -- If ALL the transaction step process successfully then the transaction
322 -- is committed to the HR Schema.
323 -- If any application errors or warnings are raised then the errors are
324 -- reported (warnings are only reported once). If a system error occurrs
325 -- the system error is raised. If either an application, warning or system
326 -- error has been reported, the work performed by the transaction is
327 -- rolled back (the transaction itself stays intact).
328 --
329 -- Pre-Requisities:
330 -- Executed from Oracle Workflow.
331 --
332 -- In Parameters:
333 -- All the following IN parameteres are set by Oracle Workflow:
334 -- itemtype -> The internal name for the item type.
335 -- itemkey -> A string that represents a primary key generated by
336 -- the application for the item type. The string uniquely
337 -- identifies the item within an item type.
338 -- actid -> The ID number of the activity that this procedure is
339 -- called from.
340 -- funmode -> The mode of the function activity. Either 'RUN' or
341 -- 'CANCEL'.
342 --
343 -- Post Success:
344 -- The work performed by the transaction is committed to the Schema and
345 -- a result of SUCCESS is returned.
346 --
347 -- Post Failure:
348 -- If an application or warning error is raised then the errors are
349 -- displayed as a HTML form.
350 -- If a system error is raised, the workflow activity is placed in
351 -- ERROR.
352 --
353 -- Developer Implementation Notes:
354 -- None
355 --
356 -- Access Status:
357 -- Internal Development Use Only.
358 --
359 -- {End Of Comments}
360 -- ----------------------------------------------------------------------------
361 procedure commit_transaction
362 (itemtype in varchar2
363 ,itemkey in varchar2
364 ,actid in number
365 ,funmode in varchar2
366 ,result out nocopy varchar2);
367 -- ----------------------------------------------------------------------------
368 -- |---------------------< commit_approval_transaction >----------------------|
369 -- ----------------------------------------------------------------------------
370 -- {Start Of Comments}
371 --
372 -- Description:
373 -- This procedure will process each transaction step for the transaction.
374 -- If ALL the transaction step process successfully then the transaction
375 -- is committed to the HR Schema.
376 -- If any application errors are raised then a result of
377 -- APPLICATION_ERROR is returned. If a warning is raised it is ignored.
378 -- At no point is any HTML is generated.
379 -- If a system error occurrs the system error is raised. If either an
380 -- application or system error has been reported, the work
381 -- performed by the transaction is rolled back (the transaction itself
382 -- stays intact).
383 --
384 -- Pre-Requisities:
385 -- Executed from Oracle Workflow.
386 --
387 -- In Parameters:
388 -- All the following IN parameteres are set by Oracle Workflow:
389 -- itemtype -> The internal name for the item type.
390 -- itemkey -> A string that represents a primary key generated by
391 -- the application for the item type. The string uniquely
392 -- identifies the item within an item type.
393 -- actid -> The ID number of the activity that this procedure is
394 -- called from.
395 -- funmode -> The mode of the function activity. Either 'RUN' or
396 -- 'CANCEL'.
397 --
398 -- Post Success:
399 -- The work performed by the transaction is committed to the Schema and
400 -- a result of SUCCESS is returned.
401 --
402 -- Post Failure:
403 -- If an application error is raised then the APPLICATION_ERROR result
404 -- is returned.
405 -- If a system error is raised, the workflow activity is placed in
406 -- ERROR.
407 --
408 -- Developer Implementation Notes:
409 -- None
410 --
411 -- Access Status:
412 -- Internal Development Use Only.
413 --
414 -- {End Of Comments}
415 -- ----------------------------------------------------------------------------
416 procedure commit_approval_transaction
417 (itemtype in varchar2
418 ,itemkey in varchar2
419 ,actid in number
420 ,funmode in varchar2
421 ,result out nocopy varchar2);
422 -- ----------------------------------------------------------------------------
423 -- |-----------------------------< retry_transaction >------------------------|
424 -- ----------------------------------------------------------------------------
425 -- {Start Of Comments}
426 --
427 -- Description:
428 -- This procedure will retry an transaction which had previously failed
429 -- due to errors or warnings being processed.
430 -- If ALL the transaction step process successfully then the transaction
431 -- is committed to the HR Schema. All warnings are ignored.
432 -- If any application errors are raised then the errors are
433 -- reported (warnings are ignored). If a system error occurrs
434 -- the system error is raised. If either an application or system
435 -- error has been reported, the work performed by the transaction is
436 -- rolled back (the transaction itself stays intact).
437 --
438 -- Pre-Requisities:
439 --
440 -- In Parameters:
441 -- p_item_type -> The internal name for the item type.
442 -- p_item_key -> A string that represents a primary key generated by
443 -- the application for the item type. The string uniquely
444 -- identifies the item within an item type.
445 -- p_actid -> The ID number of the activity that this procedure is
446 -- called from.
447 --
448 -- Post Success:
449 -- The work performed by the transaction is committed to the Schema and
450 -- the workflow activity is completed with a result of SUCCESS.
451 --
452 -- Post Failure:
453 -- If an application error is raised then the errors are displayed
454 -- as a HTML form.
455 -- If a system error is raised, the workflow activity is placed in
456 -- ERROR.
457 --
458 -- Developer Implementation Notes:
459 -- None
460 --
461 -- Access Status:
462 -- Internal Development Use Only.
463 --
464 -- {End Of Comments}
465 -- ----------------------------------------------------------------------------
466 procedure retry_transaction
467 (p_item_type in varchar2
468 ,p_item_key in varchar2
469 ,p_actid in number);
470 --
471 /*------------------------------------------------------------------------------
472 |
473 | Name : save_transaction_step
474 |
475 | Purpose :
476 |
477 | Saves the records into Transaction Tables.
478 |
479 | In Parameters :
480 | p_item_type ->
481 | The internal name for the item type.
482 | p_item_key ->
483 | A string that represents a primary key generated by
484 | the application for the item type. The string uniquely
485 | identifies the item within an item type.
486 | p_actid ->
487 | The ID number of the activity that this procedure is called
488 | from.
489 | p_transaction_Step_id =>
490 | Transaction Step ID
491 | p_api_name ->
492 | Fully qualified procedure name which will update the HRMS
493 | table.(eg hr_addresses_web.process_api). This procedure will
494 | be called by the approval process.
495 | p_api_display_name ->
496 | ?
497 | p_transaction_data ->
498 | A record structure which contains the data to be saved to the
499 | transaction table.
500 +-----------------------------------------------------------------------------*/
501 PROCEDURE save_transaction_step
502 (p_item_type IN VARCHAR2
503 ,p_item_key IN VARCHAR2
504 ,p_actid IN NUMBER
505 ,p_login_person_id IN NUMBER
506 ,p_transaction_step_id IN OUT NOCOPY NUMBER
507 ,p_api_name IN VARCHAR2 default null
508 ,p_api_display_name IN VARCHAR2 DEFAULT NULL
509 ,p_transaction_data IN TRANSACTION_TABLE
510 ,p_product_code in varchar2 default null
511 ,p_url in varchar2 default null
512 ,p_status in varchar2 default null
513 ,p_section_display_name in varchar2 default null
514 ,p_function_id in number default null
515 ,p_transaction_ref_table in varchar2 default null
516 ,p_transaction_ref_id in number default null
517 ,p_transaction_type in varchar2 default null
518 ,p_assignment_id in number default null
519 ,p_api_addtnl_info in varchar2 default null
520 ,p_selected_person_id in number default null
521 ,p_transaction_effective_date in date default null
522 ,p_process_name in varchar2 default null
523 ,p_plan_id in number default null
524 ,p_rptg_grp_id in number default null
525 ,p_effective_date_option in varchar2 default null
526 ) ;
527
528 /*
529 ||=======================================================================
530 || FUNCTION : get_activity_trans_step_id
531 || DESCRIPTION : This will return the transaction step id for a given
532 || activity name and from possible 'active' transaction
533 || steps.
534 ||=======================================================================
535 */
536 FUNCTION get_activity_trans_step_id
537 (p_activity_name IN
538 wf_item_activity_statuses_v.activity_name%TYPE
539 ,p_trans_step_id_tbl IN hr_util_web.g_varchar2_tab_type)
540 RETURN hr_api_transaction_steps.transaction_step_id%TYPE ;
541 /*
542 ||======================================================================
543 || FUNCTION : check_txn_step_exists
544 || DESCRIPTION : This will return TRUE or FALSE depending on whehter
545 || data exists for an activity in the txn table
546
547 || In Parameters :
548 || p_item_type ->
549 || The internal name for the item type.
550 || p_item_key ->
551 || A string that represents a primary key generated by
552 || the application for the item type. The string uniquely
553 || identifies the item within an item type.
554 || p_actid ->
555 || The ID number of the activity that this procedure is called
556 || from.
557 ||=====================================================================
558 */
559
560 FUNCTION check_txn_step_exists (
561 p_item_type IN wf_items.item_type%TYPE,
562 p_item_key IN wf_items.item_key%TYPE,
563 p_actid IN NUMBER )
564 RETURN BOOLEAN ;
565
566
567 /*
568 ||======================================================================
569 || PROCEDURE : delete_txn_step
570 || DESCRIPTION : Delete a transaction step for a given activity
571 ||
572
573 || In Parameters :
574 || p_item_type ->
575 || The internal name for the item type.
576 || p_item_key ->
577 || A string that represents a primary key generated by
578 || the application for the item type. The string uniquely
579 || identifies the item within an item type.
580 || p_actid ->
581 || The ID number of the activity that this procedure is called
582 || from.
583 || p_activity_name ->
584 || Name of the activity for which steps need to be deleted
585 ||=====================================================================
586 */
587
588 PROCEDURE delete_trn_step_by_act_name(
589 p_item_type IN varchar2,
590 p_item_key IN varchar2,
591 p_actid IN varchar2,
592 P_activity_name IN VARCHAR2,
593 p_login_person_id in varchar2 );
594
595 PROCEDURE delete_transaction_steps(
596 p_item_type IN varchar2,
597 p_item_key IN varchar2,
598 p_actid IN varchar2 default null,
599 p_login_person_id IN varchar2);
600
601 PROCEDURE delete_transaction_step(
602 p_transaction_step_id IN varchar2,
603 p_object_version_number IN varchar2 default null,
604 p_login_person_id IN varchar2);
605
606 function get_transaction_step_ovn(
607 p_transaction_step_id in number)
608 return number;
609
610 procedure set_transaction_value
611 (p_transaction_step_id in varchar2
612 ,p_login_person_id in varchar2
613 ,p_datatype in varchar2
614 ,p_name in varchar2
615 ,p_value in varchar2);
616
617 procedure create_transaction_step
618 (p_item_type in varchar2
619 ,p_item_key in varchar2
620 ,p_actid in varchar2
621 ,p_login_person_id in varchar2
622 ,p_api_name in varchar2
623 ,p_transaction_step_id out nocopy varchar2
624 ,p_object_version_number out nocopy varchar2);
625
626 --
627 -- --------------------<get_review_regions>----------------------------- --
628 -- Procedure to get the review region item names, step ids and activity ids of the
629 -- update regions involved in a current transaction.
630 -- This procedure return one string which will in turn gets parsed by the
631 -- jdbc code that is calling this.
632 -- ---------------------------------------------------------------------- --
633 --
634 procedure get_review_regions
635 (p_item_key IN VARCHAR2
636 ,p_item_Type IN VARCHAR2
637 ,p_review_regions OUT nocopy VARCHAR2
638 ,p_status OUT nocopy VARCHAR2);
639 --
640
641 procedure get_transaction_data
642 (p_transaction_step_id IN VARCHAR2
643 ,p_bulk_fetch_limit IN NUMBER DEFAULT 200
644 ,p_transaction_data OUT nocopy transaction_data);
645 --
646
647 procedure set_transaction_approved
648 (itemtype in varchar2,
649 itemkey in varchar2,
650 actid in number,
651 funmode in varchar2,
652 result out nocopy varchar2 );
653 --
654 end hr_transaction_ss;
[Home] [Help]