1 PACKAGE hxt_batch_process AUTHID CURRENT_USER AS
2 /* $Header: hxtbat.pkh 120.7.12020000.2 2012/07/18 10:48:25 asrajago ship $ */
3
4 g_user_id fnd_user.user_id%TYPE := FND_GLOBAL.User_Id; -- SPR C163 by BC
5
6
7 -- Bug 8888777
8 -- Added global variables for IV processing.
9 g_IV_upgrade VARCHAR2(30);
10 g_IV_format VARCHAR2(50);
11 g_XIV_TABLE HXT_OTC_RETRIEVAL_INTERFACE.IV_TABLE;
12
13 -- Bug 12993528
14 -- New types to store IV details in cache.
15
16 -- Bug 14273944
17 -- Added display_sequence column
18 TYPE IV_SEQ IS RECORD
19 (
20 input_value_id NUMBER,
21 name VARCHAR2(50),
22 lookup_type VARCHAR2(100),
23 uom VARCHAR2(50),
24 otl_sequence NUMBER,
25 display_sequence NUMBER
26 );
27
28 TYPE IV_SEQ_TABLE IS TABLE OF IV_SEQ;
29
30 TYPE LIST_IV_SEQ_TABLE IS TABLE OF IV_SEQ_TABLE INDEX BY VARCHAR2(50);
31
32 g_list_iv_seq_table LIST_IV_SEQ_TABLE;
33
34
35 --
36 /********Bug: 4620315 **********/
37
38 TYPE merge_batches_type_rec IS RECORD (batch_id pay_batch_headers.batch_id%TYPE,
39 tc_id hxt_timecards_f.id%TYPE,
40 valid_tc_retcode number,
41 tc_rowid varchar2(500),
42 for_person_id hxt_timecards_f.for_person_id%TYPE,
43 time_period_id hxt_timecards_f.time_period_id%TYPE,
44 auto_gen_flag hxt_timecards_f.auto_gen_flag%TYPE,
45 approv_person_id hxt_timecards_f.approv_person_id%TYPE,
46 approved_timestamp hxt_timecards_f.approved_timestamp%TYPE,
47 created_by hxt_timecards_f.created_by%TYPE,
48 creation_date hxt_timecards_f.creation_date%TYPE,
49 last_updated_by hxt_timecards_f.last_updated_by%TYPE,
50 last_update_date hxt_timecards_f.last_update_date%TYPE,
51 last_update_login hxt_timecards_f.last_update_login%TYPE,
52 payroll_id hxt_timecards_f.payroll_id%TYPE,
53 status hxt_timecards_f.status%TYPE,
54 effective_start_date hxt_timecards_f.effective_start_date%TYPE,
55 effective_end_date hxt_timecards_f.effective_end_date%TYPE,
56 object_version_number hxt_timecards_f.object_version_number%TYPE
57 ); /*** To record the validated timecards details ***/
58
59 TYPE merge_batches_type_table IS TABLE OF merge_batches_type_rec
60 INDEX BY BINARY_INTEGER;
61
62 TYPE del_empty_batches_type_rec IS RECORD (batch_id pay_batch_headers.batch_id%TYPE,
63 batch_ovn pay_batch_headers.object_version_number%TYPE
64 ); /*** To record the empty batches detail ***/
65
66 TYPE del_empty_batches_type_table IS TABLE OF del_empty_batches_type_rec
67 INDEX BY BINARY_INTEGER;
68
69 FUNCTION merge_batches
70 RETURN fnd_profile_option_values.profile_option_value%TYPE;
71
72 PROCEDURE merge_batches (p_merge_batch_name VARCHAR2,
73 p_merge_batches MERGE_BATCHES_TYPE_TABLE,
74 p_del_empty_batches DEL_EMPTY_BATCHES_TYPE_TABLE,
75 p_bus_group_id NUMBER,
76 p_mode VARCHAR2
77 );
78
79 /********Bug: 4620315 **********/
80
81 PROCEDURE Main_Process (
82 errbuf OUT NOCOPY VARCHAR2,
83 retcode OUT NOCOPY NUMBER,
84 p_payroll_id IN NUMBER,
85 p_date_earned IN VARCHAR2,
86 p_time_period_id IN NUMBER DEFAULT NULL,
87 p_from_batch_num IN NUMBER DEFAULT NULL,
88 p_to_batch_num IN NUMBER DEFAULT NULL,
89 p_ref_num IN VARCHAR2 DEFAULT NULL,
90 p_process_mode IN VARCHAR2,
91 p_bus_group_id IN NUMBER,
92 p_merge_flag IN VARCHAR2 DEFAULT '0',
93 p_merge_batch_name IN VARCHAR2 DEFAULT NULL,
94 p_merge_batch_specified IN VARCHAR2 DEFAULT null);
95 --
96 FUNCTION get_lookup_code (p_meaning IN VARCHAR2,
97 p_date_active IN DATE)
98 RETURN VARCHAR2;
99 --
100 PROCEDURE sum_to_mix (p_batch_id IN NUMBER,
101 p_time_period_id IN NUMBER,
102 p_sum_retcode IN OUT NOCOPY NUMBER);
103 --
104 PROCEDURE Transfer_To_Payroll( p_batch_id IN NUMBER
105 , p_payroll_id IN VARCHAR2
106 , p_batch_status IN VARCHAR2
107 , p_ref_num IN VARCHAR2
108 , p_process_mode IN VARCHAR2
109 , p_pay_retcode IN OUT NOCOPY NUMBER);
110
111
112 PROCEDURE Set_Batch_Status(p_date_earned DATE,
113 p_batch_id IN NUMBER, p_status IN VARCHAR2);
114
115 PROCEDURE rollback_paymix(p_batch_id IN NUMBER, p_time_period_id IN NUMBER,
116 p_rollback_retcode OUT NOCOPY NUMBER);
117
118 PROCEDURE Insert_Pay_Batch_Errors( p_batch_id IN NUMBER,
119 p_error_level IN VARCHAR2,
120 p_exception_details IN VARCHAR2,
121 p_return_code OUT NOCOPY NUMBER);
122 PROCEDURE Del_Prior_Errors( p_batch_id NUMBER );
123 PROCEDURE CALL_GEN_ERROR2 ( p_batch_id IN NUMBER
124 , p_tim_id IN NUMBER
125 , p_hrw_id IN NUMBER
126 , p_time_period_id IN NUMBER
127 , p_error_msg IN VARCHAR2
128 , p_loc IN VARCHAR2
129 , p_sql_err IN VARCHAR2
130 , p_TYPE IN VARCHAR2);
131
132 PROCEDURE dtl_to_bee ( p_values_rec IN HXT_BATCH_VALUES_V%ROWTYPE ,
133 p_sum_retcode IN OUT NOCOPY NUMBER,
134 p_batch_sequence IN NUMBER ) ;
135
136
137 -- Bug 8888777
138 -- Added new function to pick up BEE_IV_UPGRADE status.
139 FUNCTION get_upgrade_status(p_batch_id IN NUMBER)
140 RETURN VARCHAR2;
141
142 -- Bug 9494444
143 -- Added new procedure to facilitate snapping retrieval info
144 -- for Dashboard
145 PROCEDURE snap_retrieval_details(p_batch_id IN NUMBER);
146
147 -- Bug 12993528
148 -- New procedure to get sequence of IVs.
149 procedure GET_INPUT_VALUE_DETAILS (
150 --
151 -- Returns the input value details for the element selected by an LOV
152 --
153 p_element_type_id number,
154 p_effective_date date,
155 p_input_value_id1 in out nocopy number,
156 p_input_value_id2 in out nocopy number,
157 p_input_value_id3 in out nocopy number,
158 p_input_value_id4 in out nocopy number,
159 p_input_value_id5 in out nocopy number,
160 p_input_value_id6 in out nocopy number,
161 p_input_value_id7 in out nocopy number,
162 p_input_value_id8 in out nocopy number,
163 p_input_value_id9 in out nocopy number,
164 p_input_value_id10 in out nocopy number,
165 p_input_value_id11 in out nocopy number,
166 p_input_value_id12 in out nocopy number,
167 p_input_value_id13 in out nocopy number,
168 p_input_value_id14 in out nocopy number,
169 p_input_value_id15 in out nocopy number,
170 p_name1 in out nocopy varchar2,
171 p_name2 in out nocopy varchar2,
172 p_name3 in out nocopy varchar2,
173 p_name4 in out nocopy varchar2,
174 p_name5 in out nocopy varchar2,
175 p_name6 in out nocopy varchar2,
176 p_name7 in out nocopy varchar2,
177 p_name8 in out nocopy varchar2,
178 p_name9 in out nocopy varchar2,
179 p_name10 in out nocopy varchar2,
180 p_name11 in out nocopy varchar2,
181 p_name12 in out nocopy varchar2,
182 p_name13 in out nocopy varchar2,
183 p_name14 in out nocopy varchar2,
184 p_name15 in out nocopy varchar2,
185 p_lookup_type1 in out nocopy varchar2,
186 p_lookup_type2 in out nocopy varchar2,
187 p_lookup_type3 in out nocopy varchar2,
188 p_lookup_type4 in out nocopy varchar2,
189 p_lookup_type5 in out nocopy varchar2,
190 p_lookup_type6 in out nocopy varchar2,
191 p_lookup_type7 in out nocopy varchar2,
192 p_lookup_type8 in out nocopy varchar2,
193 p_lookup_type9 in out nocopy varchar2,
194 p_lookup_type10 in out nocopy varchar2,
195 p_lookup_type11 in out nocopy varchar2,
196 p_lookup_type12 in out nocopy varchar2,
197 p_lookup_type13 in out nocopy varchar2,
198 p_lookup_type14 in out nocopy varchar2,
199 p_lookup_type15 in out nocopy varchar2,
200 p_otl_sequence1 in out nocopy number,
201 p_otl_sequence2 in out nocopy number,
202 p_otl_sequence3 in out nocopy number,
203 p_otl_sequence4 in out nocopy number,
204 p_otl_sequence5 in out nocopy number,
205 p_otl_sequence6 in out nocopy number,
206 p_otl_sequence7 in out nocopy number,
207 p_otl_sequence8 in out nocopy number,
208 p_otl_sequence9 in out nocopy number,
209 p_otl_sequence10 in out nocopy number,
210 p_otl_sequence11 in out nocopy number,
211 p_otl_sequence12 in out nocopy number,
212 p_otl_sequence13 in out nocopy number,
213 p_otl_sequence14 in out nocopy number,
214 p_otl_sequence15 in out nocopy number
215 );
216
217
218
219
220 END hxt_batch_process;