1 package ben_ext_thread AUTHID CURRENT_USER as
2 /* $Header: benxthrd.pkh 120.5.12020000.3 2012/07/03 13:03:31 amnaraya ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Benefit Extract Thread
13 Purpose
14 This package is used to multithread benefit extract process
15 History
16 Date Who Version What?
17 10/15/98 Pdas 115.0 Created.
18 11/06/98 Yrathman 115.1 Added globals for header/trailer
19 11/16/98 Pdas 115.2 Added more globals
20 11/23/98 Pdas 115.3 Added more globals
21 01/22/99 Pdas 115.4 Changed concurrent program date parameters
22 to varchar2.
23 09 Mar 99 G Perry 115.5 IS to AS.
24 08 Apr 99 I Sen 115.6 Added use_eff_dt_for_chgs_flag
25 12 May 99 T Hayden 115.7 Major Fixes.
26 30 May 00 G Perry 115.8 Tuning Fixes.
27 29 Nov 00 R Chase 115.9 Bug 1521958.Added global to hold
28 extract definition id to be used
29 by formula calls.
30 29 Nov 01 dschwart/ 115.10 Bug#1931774: added declration of
31 bburns process_ext_ht_recs to make public
32 20 jan 02 tjesumic 115.11 restart process added , rslt_id added in process
33 11 mar 02 tjesumic 115.12 UTF changes added
34 05 may 02 tjesumic 115.13 p_ext_crit_prfl_id ,p_rquest_id added as paramter
35 to process_ext_ht_recs
36 27 dec 02 lakrish 115.17 NOCOPY changes
37 19-Jan-03 tjesumic 115.18 New procedire load_extract added to import and export the
38 extract definition
39 26-May-04 mmudigon 115.19 Bug 3672376. Parameters changed
40 for Restart procedure
41 22-Mar-05 tjesumic 115.20 CWB (CW) , and subheader coded changes
42 new extract type for 'CW' and new header and trialer procedure
43 for subheader and new criteria for both added
44 30-Mar-05 tjesumic 115.21 new param p_subhdr_chg_log added for nfc extract to get postion
45 suheader from history table for GHR
46 31-MAr-05 tjesumic 115.22 GHR changes
47 15-Apr-05 tjesumic 115.23 Global/Cross bg changes added
48 08-Jun-05 tjesumic 115.24/25 pennserver enhancement for new parameter, outpput type
49 effective, actual date and pauroll change events
50 28-APR-06 hgattu 115.26 new parameter p_out_dummy is added tp process procedure(5131931)
51 12-Feb-06 tjesumic 115.27 allow overide param added for uploading file
52 required file benextse.lct 115.40 , benextse,pkh/pkb 115.24/73
53 30-Apr-08 vkodedal 115.28 Changes required for penserver - performance fix--6895935,6801389,6995291
54 16-Dec-11 velvanop 115.175 Bug#13509481: Added person selection rule parameter for the Extract process
55
56
57 */
58 --
59 g_package varchar2(80) := 'ben_ext_thread';
60 --
61 Type g_num_list is table of number
62 Index by binary_integer;
63 --
64 type l_number_type is varray(200) of number;
65 --
66 g_processes_rec g_num_list;
67 --
68 g_max_errors_allowed number;
69 -- RChase Create a global for extract definition id to be used by formula calls
70 g_ext_dfn_id ben_ext_dfn.ext_dfn_id%type;
71 g_ext_rslt_id ben_ext_rslt.ext_rslt_id%type;
72 --
73 g_ext_group_elmt1 ben_ext_fld.short_name%type ;
74 g_ext_group_elmt2 ben_ext_fld.short_name%type ;
75
76 --
77 g_num_processes number := 0;
78 --
79 g_err_name varchar2(250);
80 --
81 g_err_num number := 0;
82 --
83 g_job_failure_error exception;
84 --
85 g_ht_error exception;
86 --
87 g_ext_strt_dt date;
88 --
89 g_ext_end_dt date;
90 --
91 g_effective_start_date date ;
92 g_effective_end_date date ;
93 g_actual_start_date date ;
94 g_actual_end_date date ;
95 --
96 g_err_cnt number := 0;
97 --
98 g_per_cnt number := 0;
99 --
100 g_rec_cnt number := 0;
101 --
102 g_dtl_cnt number := 0;
103 --
104 g_hdr_cnt number := 0;
105 --
106 g_trl_cnt number := 0;
107 --
108 g_subhdr_cnt number := 0;
109 --
110 g_subtrl_cnt number := 0;
111
112 g_subhdr_chg_log varchar2(15) ;
113
114
115 g_chg_ext_from_ben varchar2(1) ;
116 g_chg_ext_from_pay varchar2(1) ;
117
118
119 procedure do_multithread
120 (errbuf out nocopy varchar2
121 ,retcode out nocopy number
122 ,p_benefit_action_id in number
123 ,p_ext_dfn_id in number
124 ,p_thread_id in number
125 ,p_effective_date in varchar2
126 ,p_business_group_id In number
127 ,p_data_typ_cd in varchar2
128 ,p_ext_typ_cd in varchar2
129 ,p_ext_crit_prfl_id in number
130 ,p_ext_rslt_id in number
131 ,p_ext_file_id in number
132 ,p_ext_strt_dt in varchar2
133 ,p_ext_end_dt in varchar2
134 ,p_prmy_sort_cd in varchar2
135 ,p_scnd_sort_cd in varchar2
136 ,p_output_name in varchar2
137 ,p_apnd_rqst_id_flag in varchar2
138 ,p_request_id in number
139 ,p_use_eff_dt_for_chgs_flag in varchar2
140 ,p_master_process_flag in varchar2
141 ,p_eff_start_date in varchar2
142 ,p_eff_end_date in varchar2
143 ,p_act_start_date in varchar2
144 ,p_act_end_date in varchar2
145 ,p_penserv_mode in varchar2 DEFAULT 'N' -- 6995291 vkodedal
146 );
147 --
148 /* Start of Changes for WWBUG: 1931774 */
149 /* Added declaration for previously private procedure */
150
151 Procedure process_ext_ht_recs(p_ext_rslt_id in number,
152 p_ext_file_id in number,
153 p_ext_typ_cd in varchar2,
154 p_rcd_typ_cd in varchar2,
155 p_business_group_id in number,
156 p_effective_date in date,
157 p_group_val_01 in varchar2 default null,
158 p_group_val_02 in varchar2 default null,
159 p_request_id in number default null,
160 p_ext_crit_prfl_id in number default null,
161 p_ext_per_bg_id in number default null
162 );
163
164 /* End of Changes for WWBUG: 1931774 */
165 --
166 /* PLEASE NOTICE: be sure that if you add any parameters to
167 do_multithread, you also must update benxmgr3.sql */
168 --
169 procedure process
170 (errbuf out nocopy varchar2
171 ,retcode out nocopy varchar2
172 ,p_benefit_action_id in number
173 ,p_ext_dfn_id in number
174 ,p_effective_date in varchar2
175 ,p_business_group_id in number
176 --
177 ,p_output_type in varchar2 default null
178 ,p_out_dummy in varchar2 default null
179 ,p_xdo_template_id in number default null
180 ,p_eff_start_date in varchar2 default null
181 ,p_eff_end_date in varchar2 default null
182 ,p_act_start_date in varchar2 default null
183 ,p_act_end_date in varchar2 default null
184 --
185 ,p_ext_rslt_id in number default null
186 ,p_subhdr_chg_log in varchar2 default null
187 ,p_penserv_date in date default null -- 6895935, 6801389 vkodedal
188 ,p_penserv_mode in VARCHAR2 DEFAULT 'N' -- 6995291 vkodedal
189 ,p_person_selection_rl in number default null
190 );
191 --
192 /* PLEASE NOTICE: be sure that if you add any parameters to
193 process, you also must update benxmgr2.sql */
194 --
195 --Old proc. Not used
196 --
197 /*procedure restart(errbuf out nocopy varchar2
198 ,retcode out nocopy varchar2
199 ,p_benefit_action_id in number
200 ,p_ext_rslt_id in number default null );
201 */
202
203 --
204 --new proc
205 --
206 procedure restart(errbuf out nocopy varchar2
207 ,retcode out nocopy varchar2
208 ,p_ext_dfn_id in number
209 ,p_concurrent_request_id in number );
210
211 Procedure load_extract(
212 errbuf out nocopy varchar2
213 ,retcode out nocopy number
214 ,p_mode in varchar2
215 ,p_seeded in varchar2 default 'N'
216 ,p_loader_file in varchar2 default null
217 ,p_file_name in varchar2
218 ,p_view_name in varchar2 default null
219 ,p_extract_file_id in number default null
220 ,p_business_group_id in number
221 ,p_validate in varchar2 default 'N'
222 ,p_allow_override in varchar2 default 'N'
223 ) ;
224
225 End ben_ext_thread;
[Home] [Help]