1 package PAY_USER_COLUMN_INSTA_MATRIX as
2 /* $Header: pydputil.pkh 120.1 2005/06/14 02:23 mkataria noship $ */
3
4
5 /*------------------------------------------------------------------------*/
6 /*----------------------- < Utility data structures >---------------------*/
7 /*------------------------------------------------------------------------*/
8 type user_column_key_rec
9 is record (
10 qualifier varchar2(30)
11 ,user_column_key varchar2(240)
12 ,user_column_value pay_user_column_instances_f.value%type
13 );
14
15 type userkeys is table of user_column_key_rec index by binary_integer;
16
17
18 /*------------------------------------------------------------------------
19 --------------------<Create Data Pump Batch Lines>----------------------
20 ------------------------------------------------------------------------
21 NAME
22 CREATE_DATA_PUMP_BATCH_LINES
23 DESCRIPTION
24 Interface procedure for User Column Instance Matrix spreadsheet.
25 NOTES
26 Calls the main data pump package hrdpp_create_user_column_insta once
27 for each column value passed.
28 For example if there are 'N' columns and 'M' rows for the passed
29 User Table, this procedure will be called 'M' number of times through
30 WebADI (each time with 'N' column values). Each time the procedure is
31 called, it will call hrdpp_create_user_column_insta.insert_batch_lines
32 'N' number of times.
33 PARAMETERS
34 p_batch_id : Batch Id under which the batch lines
35 will be created. Mandatory.
36 p_data_pump_batch_line_id : Keeping it for future use.
37 p_data_pump_business_grp_name : Business group name. Mandatory.
38 p_effective_date : Effective date of the column instance.
39 Mandatory.
40 p_row_low_range_or_name : Row name. Mandatory.
41 p_user_table_name : User Table name. Mandatory.
42 p_row_high_range : Row High Range, in case the table is
43 range type. Optional.
44 p_value1..p_value25 : Values corresponding to user columns.
45 May be null.
46 ------------------------------------------------------------------------
47 ------------------------------------------------------------------------
48 ------------------------------------------------------------------------*/
49
50 procedure create_data_pump_batch_lines
51 (
52 p_batch_id in number
53 ,p_data_pump_batch_line_id in number default null
54 ,p_data_pump_business_grp_name in varchar2
55 ,p_effective_date in date
56 ,p_row_low_range_or_name in varchar2
57 ,p_user_table_name in varchar2
58 ,p_row_high_range in varchar2 default null
59 ,p_value1 in varchar2 default null
60 ,p_value2 in varchar2 default null
61 ,p_value3 in varchar2 default null
62 ,p_value4 in varchar2 default null
63 ,p_value5 in varchar2 default null
64 ,p_value6 in varchar2 default null
65 ,p_value7 in varchar2 default null
66 ,p_value8 in varchar2 default null
67 ,p_value9 in varchar2 default null
68 ,p_value10 in varchar2 default null
69 ,p_value11 in varchar2 default null
70 ,p_value12 in varchar2 default null
71 ,p_value13 in varchar2 default null
72 ,p_value14 in varchar2 default null
73 ,p_value15 in varchar2 default null
74 ,p_value16 in varchar2 default null
75 ,p_value17 in varchar2 default null
76 ,p_value18 in varchar2 default null
77 ,p_value19 in varchar2 default null
78 ,p_value20 in varchar2 default null
79 ,p_value21 in varchar2 default null
80 ,p_value22 in varchar2 default null
81 ,p_value23 in varchar2 default null
82 ,p_value24 in varchar2 default null
83 ,p_value25 in varchar2 default null
84 );
85
86
87 /*------------------------------------------------------------------------
88 -------------------------<Create Batch Header>--------------------------
89 ------------------------------------------------------------------------
90 NAME
91 CREATE_BATCH_HEADER
92 DESCRIPTION
93 Interface procedure for Create Batch Header spreadsheet.
94 NOTES
95 Creates Data Pump Batch header.
96 PARAMETERS
97 p_batch_id : Name of the batch. Mandatory.
98 p_business_group_name : Business group name. Mandatory.
99 p_reference : Batch reference. Optional
100 p_batch_id : Out parameter.
101 RETURNS
102 p_batch_id : Batch Id of the created batch
103 ------------------------------------------------------------------------
104 ------------------------------------------------------------------------
105 ------------------------------------------------------------------------*/
106
107 procedure create_batch_header
108 (
109 p_batch_name in varchar2,
110 p_business_group_name in varchar2 default null,
111 p_reference in varchar2 default null,
112 p_batch_id out nocopy number
113 );
114
115 /*------------------------------------------------------------------------
116 -----------------------< Batch Overall Status>--------------------------
117 ------------------------------------------------------------------------
118 NAME
119 BATCH_OVERALL_STATUS
120 DESCRIPTION
121 Returns overall status of the batch.
122 NOTES
123 Determines the overall status of the batch.
124 PARAMETERS
125 p_batch_id : Data Pump Batch Id. Mandatory.
126 RETURNS
127 Overall status of the batch.
128 ------------------------------------------------------------------------
129 ------------------------------------------------------------------------
130 ------------------------------------------------------------------------*/
131 function batch_overall_status (p_batch_id number) return varchar2;
132
133
134
135 /*------------------------------------------------------------------------
136 ---------------------------<Get Link Value>-----------------------------
137 ------------------------------------------------------------------------
138 NAME
139 GET_LINK_VALUE
140 DESCRIPTION
141 Interface function to return link_value for entities belonging to
142 one batch.
143 NOTES
144 Generates link values.
145 PARAMETERS
146 p_batch_line_id : Batch Line Id. Optional.
147 p_business_group_name : Business group name. Mandatory.
148 p_user_row_user_key : User Row User Key.May be null.
149 Mandatory.
150 p_user_table_user_key : User Table User Key. May be null.
151 Mandatory.
152 RETURNS
153 link value : Link value
154 ------------------------------------------------------------------------
155 ------------------------------------------------------------------------
156 ------------------------------------------------------------------------*/
157 function get_link_value
158 (
159 p_batch_line_id number
160 ,p_business_group_name varchar2
161 ,p_user_row_user_key varchar2
162 ,p_user_table_user_key varchar2
163 ) return number;
164
165
166
167 /*------------------------------------------------------------------------
168 ---------------------------<Insert User Key>----------------------------
169 ------------------------------------------------------------------------
170 NAME
171 GET_LINK_VALUE
172 DESCRIPTION
173 Overloaded procedure to insert user key for a user column. This
174 procedure will be invoked when we are creating column instance batch
175 line for the column of a table which exists in the live tables.
176 NOTES
177 Inserts User Column User Key in HR_PUMP_BATCH_LINE_USER_KEYS table.
178 PARAMETERS
179 p_business_group : Business group name. Mandatory.
180 p_user_column_name : User column name. Mandatory.
181 p_user_table_name : User table name. Mandatory
182
183 ------------------------------------------------------------------------
184 ------------------------------------------------------------------------
185 ------------------------------------------------------------------------*/
186 procedure insert_user_key
187 (
188 p_business_group varchar2
189 ,p_user_column_name varchar2
190 ,p_user_table_name varchar2
191 );
192
193
194 /*------------------------------------------------------------------------
195 ---------------------------<Insert User Key>----------------------------
196 ------------------------------------------------------------------------
197 NAME
198 GET_LINK_VALUE
199 DESCRIPTION
200 Overloaded procedure to insert user key for a user row. This
201 procedure will be invoked when we are creating column instance batch
202 line for the row of a table which exists in the live tables.
203 NOTES
204 Inserts User Row User Key in HR_PUMP_BATCH_LINE_USER_KEYS table.
205 PARAMETERS
206 p_business_group : Business group name. Mandatory.
207 p_user_row_name : User row name. Mandatory.
208 p_user_table_name : User table name. Mandatory
209 p_effective_date : Effective date.
210 ------------------------------------------------------------------------
211 ------------------------------------------------------------------------
212 ------------------------------------------------------------------------*/
213 procedure insert_user_key
214 (
215 p_business_group varchar2
216 ,p_user_row_name varchar2
217 ,p_user_table_name varchar2
218 ,p_effective_date date
219 );
220
221
222 /*------------------------------------------------------------------------
223 ---------------------------<Insert User Key>----------------------------
224 ------------------------------------------------------------------------
225 NAME
226 GET_LINK_VALUE
227 DESCRIPTION
228 Overloaded procedure to insert user key for a user table. This
229 procedure will be invoked when we are creating column/row batch
230 line for a table which exists in the live table.
231 NOTES
232 Inserts User Table User Key in HR_PUMP_BATCH_LINE_USER_KEYS table.
233 PARAMETERS
234 p_business_group : Business group name. Mandatory.
235 p_user_table_name : User table name. Mandatory
236
237 ------------------------------------------------------------------------
238 ------------------------------------------------------------------------
239 ------------------------------------------------------------------------*/
240 procedure insert_user_key
241 (
242 p_business_group varchar2
243 ,p_user_table_name varchar2
244 );
245
246
247 /*-----------------------------------------------------------------------
248 ------------------------<Get Matrix Row Values>-------------------------
249 ------------------------------------------------------------------------
250 NAME
251 GET_MATRIX_ROW_VALUES
252 DESCRIPTION
253 Function to return all the values corresponding to a row from
254 pay_user_column_instances_f and hrdpv_create_user_column_insta to
255 display in matrix spreadsheet.
256 NOTES
257 This function is invoked at the time of download/upload of user
258 column instances through matrix spreadsheet.
259 PARAMETERS
260 p_batch_id number : Batch Id. Mandatory.
261 p_user_table_name : User table name. Mandatory.
262 p_row_low_range_or_name : User row name. Mandatory
263 p_business_group_id : Business group id. Mandatory.
264 RETURNS
265 returns all the column instances correponding to a row in the order
266 the columns appear in matrix spreadsheet, enclosed between '$'
267 characters.
268 ------------------------------------------------------------------------
269 ------------------------------------------------------------------------
270 ------------------------------------------------------------------------*/
271
272 function get_matrix_row_values
273 (
274 p_batch_id number
275 ,p_user_table_name varchar2
276 ,p_row_low_range_or_name varchar2
277 ,p_business_group_id number
278 )return varchar2;
279
280
281 end pay_user_column_insta_matrix;
282