1 package ben_ext_thread as
2 /* $Header: benxthrd.pkh 120.4.12010000.2 2008/08/05 15:01:13 ubhat 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
55
56 */
57 --
58 g_package varchar2(80) := 'ben_ext_thread';
59 --
60 Type g_num_list is table of number
61 Index by binary_integer;
62 --
63 type l_number_type is varray(200) of number;
64 --
65 g_processes_rec g_num_list;
66 --
67 g_max_errors_allowed number;
68 -- RChase Create a global for extract definition id to be used by formula calls
69 g_ext_dfn_id ben_ext_dfn.ext_dfn_id%type;
70 g_ext_rslt_id ben_ext_rslt.ext_rslt_id%type;
71 --
72 g_ext_group_elmt1 ben_ext_fld.short_name%type ;
73 g_ext_group_elmt2 ben_ext_fld.short_name%type ;
74
75 --
76 g_num_processes number := 0;
77 --
78 g_err_name varchar2(250);
79 --
80 g_err_num number := 0;
81 --
82 g_job_failure_error exception;
83 --
84 g_ht_error exception;
85 --
86 g_ext_strt_dt date;
87 --
88 g_ext_end_dt date;
89 --
90 g_effective_start_date date ;
91 g_effective_end_date date ;
92 g_actual_start_date date ;
93 g_actual_end_date date ;
94 --
95 g_err_cnt number := 0;
96 --
97 g_per_cnt number := 0;
98 --
99 g_rec_cnt number := 0;
100 --
101 g_dtl_cnt number := 0;
102 --
103 g_hdr_cnt number := 0;
104 --
105 g_trl_cnt number := 0;
106 --
107 g_subhdr_cnt number := 0;
108 --
109 g_subtrl_cnt number := 0;
110
111 g_subhdr_chg_log varchar2(15) ;
112
113
114 g_chg_ext_from_ben varchar2(1) ;
115 g_chg_ext_from_pay varchar2(1) ;
116
117
118 procedure do_multithread
119 (errbuf out nocopy varchar2
120 ,retcode out nocopy number
121 ,p_benefit_action_id in number
122 ,p_ext_dfn_id in number
123 ,p_thread_id in number
124 ,p_effective_date in varchar2
125 ,p_business_group_id In number
126 ,p_data_typ_cd in varchar2
127 ,p_ext_typ_cd in varchar2
128 ,p_ext_crit_prfl_id in number
129 ,p_ext_rslt_id in number
130 ,p_ext_file_id in number
131 ,p_ext_strt_dt in varchar2
132 ,p_ext_end_dt in varchar2
133 ,p_prmy_sort_cd in varchar2
134 ,p_scnd_sort_cd in varchar2
135 ,p_output_name in varchar2
136 ,p_apnd_rqst_id_flag in varchar2
137 ,p_request_id in number
138 ,p_use_eff_dt_for_chgs_flag in varchar2
139 ,p_master_process_flag in varchar2
140 ,p_eff_start_date in varchar2
141 ,p_eff_end_date in varchar2
142 ,p_act_start_date in varchar2
143 ,p_act_end_date in varchar2
144 ,p_penserv_mode in varchar2 DEFAULT 'N' -- 6995291 vkodedal
145 );
146 --
147 /* Start of Changes for WWBUG: 1931774 */
148 /* Added declaration for previously private procedure */
149
150 Procedure process_ext_ht_recs(p_ext_rslt_id in number,
151 p_ext_file_id in number,
152 p_ext_typ_cd in varchar2,
153 p_rcd_typ_cd in varchar2,
154 p_business_group_id in number,
155 p_effective_date in date,
156 p_group_val_01 in varchar2 default null,
157 p_group_val_02 in varchar2 default null,
158 p_request_id in number default null,
159 p_ext_crit_prfl_id in number default null,
160 p_ext_per_bg_id in number default null
161 );
162
163 /* End of Changes for WWBUG: 1931774 */
164 --
165 /* PLEASE NOTICE: be sure that if you add any parameters to
166 do_multithread, you also must update benxmgr3.sql */
167 --
168 procedure process
169 (errbuf out nocopy varchar2
170 ,retcode out nocopy varchar2
171 ,p_benefit_action_id in number
172 ,p_ext_dfn_id in number
173 ,p_effective_date in varchar2
174 ,p_business_group_id in number
175 --
176 ,p_output_type in varchar2 default null
177 ,p_out_dummy in varchar2 default null
178 ,p_xdo_template_id in number default null
179 ,p_eff_start_date in varchar2 default null
180 ,p_eff_end_date in varchar2 default null
181 ,p_act_start_date in varchar2 default null
182 ,p_act_end_date in varchar2 default null
183 --
184 ,p_ext_rslt_id in number default null
185 ,p_subhdr_chg_log in varchar2 default null
186 ,p_penserv_date in date default null -- 6895935, 6801389 vkodedal
187 ,p_penserv_mode in VARCHAR2 DEFAULT 'N' -- 6995291 vkodedal
188 );
189 --
190 /* PLEASE NOTICE: be sure that if you add any parameters to
191 process, you also must update benxmgr2.sql */
192 --
193 --Old proc. Not used
194 --
195 /*procedure restart(errbuf out nocopy varchar2
196 ,retcode out nocopy varchar2
197 ,p_benefit_action_id in number
198 ,p_ext_rslt_id in number default null );
199 */
200
201 --
202 --new proc
203 --
204 procedure restart(errbuf out nocopy varchar2
205 ,retcode out nocopy varchar2
206 ,p_ext_dfn_id in number
207 ,p_concurrent_request_id in number );
208
209 Procedure load_extract(
210 errbuf out nocopy varchar2
211 ,retcode out nocopy number
212 ,p_mode in varchar2
213 ,p_seeded in varchar2 default 'N'
214 ,p_loader_file in varchar2 default null
215 ,p_file_name in varchar2
216 ,p_view_name in varchar2 default null
217 ,p_extract_file_id in number default null
218 ,p_business_group_id in number
219 ,p_validate in varchar2 default 'N'
220 ,p_allow_override in varchar2 default 'N'
221 ) ;
222
223 End ben_ext_thread;
[Home] [Help]