1 package BEN_EXT_UTIL as
2 /* $Header: benxutil.pkh 120.3.12010000.2 2008/08/05 15:02:07 ubhat ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Extract Utility.
12 Purpose:
13 This is used for utility style processes for the Benefits Extract System.
14 History:
15 Date Who Version What?
16 ---- --- ------- -----
17 24 Oct 98 Ty Hayden 115.0 Created.
18 25 Oct 98 Ty Hayden 115.1 Added request_id as input.
19 04 Feb 99 Pulak Das 115.2 Added procedure
20 get_rec_nam_num,
21 get_rec_statistics,
22 get_per_statistics,
23 get_err_warn_statitics.
24 08 Feb 99 Pulak Das 115.3 Added function
25 get_value (from benxsttl.pkh)
26 15 Feb 99 Ty Hayden 115.4 Added ff function
27 get_extract_value
28 09 Mar 99 G Perry 115.5 IS to AS.
29 13 May 99 I Sen 115.6 Added cal_ext_date function
30 (earlier in benxthrd)
31 16 Jun 99 I Sen 115.7 Added foreign key ref ext_rslt_id
32 03 Sep 99 Ty Hayden 115.8 Added procedure get_chg_dates.
33 13 Sep 99 Ty Hayden 115.9 Added get_cm_dates.
34 15 Nov 99 Ty Hayden 115.10 Added get_ext_dates.
35 30 Dec 99 Ty Hayden 115.11 Remove get_extract_value.
36 22 dec 02 tjesumic 115.12 dbdrv added
37 24-Dec-02 bmanyam 115.13 NOCOPY Changes
38 15-Dec-04 tjesumic 115.14 pl_pl_id added to calc_ext_dates
39 01-Nov-06 tjesumic 115.15 entries_affected procedire moved from pqp to ben
40 30-Apr-08 vkodedal 115.16 entries_affected - added one parameter for penserver
41 */
42 -----------------------------------------------------------------------------------
43 --
44 g_package varchar2(33) := 'ben_ext_util'; -- Global package name
45 --
46 Type g_rec_nam_num_rec_typ is record
47 (name ben_ext_rcd.name%type
48 ,num number);
49 --
50 Type g_rec_nam_num_tab_typ is table
51 of g_rec_nam_num_rec_typ
52 Index by binary_integer;
53 --
54 g_err_name varchar2(50);
55 g_job_failure exception;
56 --
57 PROCEDURE write_err
58 (p_err_num in number default null,
59 p_err_name in varchar2 default null,
60 p_typ_cd in varchar2 default null,
61 p_person_id in number default null,
62 p_request_id in number default null,
63 p_business_group_id in number default null,
64 p_ext_rslt_id in number default null);
65 --
66 -- This procedure will return a data structure containing the name and number
67 -- of all extracted records corresponding to a ext_rslt_id or request_id or both.
68 -- If no records are found then one record with value null, 0 will be returned.
69 -- If both ext_rslt_id and request_id are passed and if they do not correspond
70 -- to each other then one record with value null, 0 will be returned.
71 --
72 Procedure get_rec_nam_num
73 (p_ext_rslt_id in number default null
74 ,p_request_id in number default null
75 ,p_rec_tab out nocopy g_rec_nam_num_tab_typ
76 );
77 --
78 -- This procedure will return total header records, total detail records,
79 -- total trailer records corresponding to a ext_rslt_id or request_id or both.
80 -- If both ext_rslt_id and request_id are passed and if they do not correspond
81 -- to each other then 0, 0, 0 will be returned.
82 --
83 procedure get_rec_statistics
84 (p_ext_rslt_id in number default null
85 ,p_request_id in number default null
86 ,p_header_rec out nocopy number
87 ,p_detail_rec out nocopy number
88 ,p_trailer_rec out nocopy number
89 );
90 --
91 -- This procedure will return total people extracted, total people not
92 -- extracted due to error corresponding to a ext_rslt_id or request_id or both.
93 -- If both ext_rslt_id and request_id are passed and if they do not correspond
94 -- to each other then 0, 0, 0 will be returned.
95 --
96 procedure get_per_statistics
97 (p_ext_rslt_id in number default null
98 ,p_request_id in number default null
99 ,p_per_xtrctd out nocopy number
100 ,p_per_not_xtrctd out nocopy number
101 );
102 --
103 -- This procedure will return total job failures, total errors,
104 -- total warnings corresponding to a ext_rslt_id or request_id or both.
105 -- If both ext_rslt_id and request_id are passed and if they do not correspond
106 -- to each other then 0, 0, 0 will be returned.
107 --
108 procedure get_err_warn_statistics
109 (p_ext_rslt_id in number default null
110 ,p_request_id in number default null
111 ,p_job_failure out nocopy number
112 ,p_error out nocopy number
113 ,p_warning out nocopy number
114 );
115 --
116 procedure get_statistics_text
117 (p_ext_rslt_id in number default null
118 ,p_request_id in number default null
119 ,p_text out nocopy varchar2
120 );
121 --
122 Function get_value(p_ext_rcd_id number,
123 p_ext_rslt_dtl_id number,
124 p_seq_num number)
125 RETURN varchar2;
126 --
127 procedure get_chg_dates
128 (p_ext_dfn_id in number,
129 p_effective_date in date,
130 p_chg_actl_strt_dt out nocopy date,
131 p_chg_actl_end_dt out nocopy date,
132 p_chg_eff_strt_dt out nocopy date,
133 p_chg_eff_end_dt out nocopy date);
134 --
135 procedure get_cm_dates
136 (p_ext_dfn_id in number,
137 p_effective_date in date,
138 p_to_be_sent_strt_dt out nocopy date,
139 p_to_be_sent_end_dt out nocopy date);
140 --
141 procedure get_ext_dates
142 (p_ext_dfn_id in number,
143 p_data_typ_cd in varchar2,
144 p_effective_date in date,
145 p_person_ext_dt out nocopy date,
146 p_benefits_ext_dt out nocopy date
147 );
148 --
149 -- This function is for converting relative date to absolute date
150 --
151 Function calc_ext_date
152 (p_ext_date_cd in varchar2,
153 p_abs_date in date,
154 p_ext_dfn_id in number,
155 p_pl_id in number default null
156 ) return date;
157 --
158 PROCEDURE entries_affected
159 (p_assignment_id IN NUMBER DEFAULT NULL
160 ,p_event_group_id IN NUMBER DEFAULT NULL
161 ,p_mode IN VARCHAR2 DEFAULT NULL
162 ,p_start_date IN DATE DEFAULT hr_api.g_sot
163 ,p_end_date IN DATE DEFAULT hr_api.g_eot
164 ,p_business_group_id IN NUMBER
165 ,p_detailed_output OUT NOCOPY pay_interpreter_pkg.t_detailed_output_table_type
166 ,p_process_mode IN VARCHAR2 DEFAULT 'ENTRY_CREATION_DATE'
167 ,p_penserv_mode IN VARCHAR2 DEFAULT 'N' --vkodedal changes for penserver - 30-apr-2008
168 );
169
170 END; -- Package spec
[Home] [Help]