1 PACKAGE EGO_TA_BULKLOAD_PVT AUTHID CURRENT_USER AS
2 /* $Header: EGOVTABS.pls 120.0.12010000.3 2010/04/26 13:31:19 ccsingh noship $ */
3 -- This is the package which will get called from Concurent program
4 -- In this we have API Import_TA_Intf which will do :
5 -- 1.BULK_VALIDATION and set the process flags in interface table accordingly.
6 -- Here we have to do all the validation which we can do at table level to
7 -- restrict the records IN PL-SQL TABLE.
8 -- 2.Load the relevant records in PL-SQL Table.
9 -- 3.Call to main API PROCESS_TA(PL-SQL table).
10
11 ------------------------------------------------------------------------------------
12 -- Declaration of associated array for TA Interface Table --
13 ------------------------------------------------------------------------------------
14 SUBTYPE TA_Intf_Tbl is ego_metadata_pub.TA_Intf_Tbl;
15
16 ---------------------------------------------------------------
17 -- Global Variables and Constants --
18 ---------------------------------------------------------------
19 G_BO_IDENTIFIER CONSTANT VARCHAR2(30) := 'ICC';
20 G_ENTITY_IDENTIFIER CONSTANT VARCHAR2(30) := 'ICC_TA';
21 G_TABLE_NAME CONSTANT VARCHAR2(30) := 'EGO_TRANS_ATTRS_VERS_INTF';
22 G_TOKEN_TBL Error_Handler.Token_Tbl_Type;
23
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_TA_BULKLOAD_PVT';
25 G_APP_NAME CONSTANT VARCHAR2(3) := 'EGO';
26
27
28 ---------------------------------------------------------------
29 -- Transaction Type. --
30 ---------------------------------------------------------------
31
32 G_CREATE CONSTANT VARCHAR2(10) := 'CREATE';
33 G_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
34 G_DELETE CONSTANT VARCHAR2(10) := 'DELETE';
35 G_SYNC CONSTANT VARCHAR2(10) := 'SYNC';
36
37 ---------------------------------------------------------------
38 -- API Return statuses. --
39 ---------------------------------------------------------------
40
41 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
42 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
43 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
44 G_MISS_CHAR CONSTANT VARCHAR2(1) := FND_API.G_MISS_CHAR;
45 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
46
47 ---------------------------------------------------------------
48 -- WHO Columns --
49 ---------------------------------------------------------------
50
51 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
52 G_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
53 G_APPLICATION_ID NUMBER;
54 G_PROG_APPL_ID CONSTANT NUMBER := FND_GLOBAL.PROG_APPL_ID;
55 G_PROGRAM_ID CONSTANT NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
56 G_REQUEST_ID CONSTANT NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
57
58 ---------------------------------------------------------------
59 -- Data Types for Attribute--
60 ---------------------------------------------------------------
61
62 G_CHAR_DATA_TYPE CONSTANT VARCHAR2(1) := 'C';
63 G_NUMBER_DATA_TYPE CONSTANT VARCHAR2(1) := 'N';
64 G_DATE_DATA_TYPE CONSTANT VARCHAR2(1) := 'X';
65 G_DATE_TIME_DATA_TYPE CONSTANT VARCHAR2(1) := 'Y';
66
67 ---------------------------------------------------------------
68 -- Process Status flags--
69 ---------------------------------------------------------------
70 G_PROCESS_RECORD CONSTANT NUMBER := 1;
71 G_ERROR_RECORD CONSTANT NUMBER := 3;
72 G_SUCCESS_RECORD CONSTANT NUMBER := 7;
73
74 G_EGO_MD_API CONSTANT NUMBER := 1;
75 G_EGO_MD_INTF CONSTANT NUMBER := 2;
76 G_FLOW_TYPE NUMBER := G_EGO_MD_API;
77
78 ---------------------------------------------------------------
79 -- ERROR Handling--
80 ---------------------------------------------------------------
81 G_MESSAGE_NAME VARCHAR2(100);
82 G_MESSAGE_TEXT VARCHAR2(2000);
83
84
85 -- ============================================================================
86 -- Name : Import_TA_intf
87 -- Description : This is the main API which will do above tasks and make a call
88 -- to EGO_TRANS_ATTR_PVT.Process_TA.
89 -- Parameters :
90 -- Parameters:
91 -- IN :
92 -- p_api_version IN NUMBER
93 -- Active API version number
94 --
95 -- p_set_process_id IN NUMBER
96 -- Batch Id to be processed
97 --
98 -- p_item_catalog_group_id IN NUMBER
99 -- ICC id for which the import has to process.
100 --
101 -- p_icc_version_number_intf IN NUMBER
102 -- ICC version number which user is providing in interface table
103 --
104 -- p_icc_version_number_act IN NUMBER
105 -- After resolving sync what is the actual version number in targer sys
106 --
107 -- OUT :
108 -- x_return_status OUT NOCOPY VARCHAR2
109 -- Used to get status of a procedure,Successful or not
110 --
111 -- x_return_msg OUT NOCOPY VARCHAR2
112 -- Error Message to be return.
113
114 -- ============================================================================
115
116 PROCEDURE Import_TA_Intf(
117 p_api_version IN NUMBER,
118 p_set_process_id IN NUMBER,
119 p_item_catalog_group_id IN NUMBER,
120 p_icc_version_number_intf IN NUMBER,
121 p_icc_version_number_act IN NUMBER,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_return_msg OUT NOCOPY VARCHAR2);
124
125 -- ============================================================================
126 -- Name : Initialize
127 -- Description : This will intialize values in interface table Transaction_id
128 -- and set transaction_name to UPPER.
129 -- Addtion to that set G_APPLICATION_ID also
130 --
131 -- Parameters :
132 -- IN :
133 -- p_set_process_id IN Number
134 -- Batch Id to be processed
135 --
136 -- OUT :
137 -- x_return_status OUT NOCOPY VARCHAR2
138 -- Used to get status of a procedure, whether it executed
139 -- Successfully or not.
140
141
142 -- ============================================================================
143
144 PROCEDURE Initialize(
145 p_set_process_id IN NUMBER,
146 x_return_status OUT NOCOPY VARCHAR2);
147
148 -- ============================================================================
149 -- Name : Bulk_Validate_Trans_Attrs
150 -- Description : This will Validate common things at table level to restrict
151 -- the no of records from loading in pl-sql table
152 -- Parameters :
153 -- IN :
154 -- p_set_process_id IN Number
155 -- Batch Id to be processed
156
157 -- ============================================================================
158
159 PROCEDURE Bulk_Validate_Trans_Attrs (
160 p_set_process_id IN NUMBER);
161
162 -- ============================================================================
163 -- Name : Bulk_Validate_Trans_Attrs_ICC
164 -- Description : This validate things specific to ICC ID and ICC version
165 -- only.
166 -- Parameters :
167 -- IN :
168 -- p_set_process_id IN Number
169 -- Batch Id to be processed
170 --
171 -- p_item_catalog_group_id IN NUMBER
172 -- ICC id for which the import has to process.
173 --
174 -- p_item_catalog_group_name IN VARCHAR2
175 -- ICC name for value to ID conversion.
176 --
177 -- ============================================================================
178
179 PROCEDURE Bulk_Validate_Trans_Attrs_ICC (
180 p_set_process_id IN NUMBER,
181 p_item_catalog_group_id IN NUMBER,
182 p_item_catalog_group_name IN VARCHAR2);
183
184
185
186 -- ============================================================================
187 -- Name : Value_to_Id
188 -- Description : This will convert value to id at table level.
189 --
190 -- Parameters :
191 -- IN :
192 -- p_set_process_id IN Number
193 -- Batch Id to be processed
194 -- ============================================================================
195 PROCEDURE Value_to_Id(
196 p_set_process_id IN NUMBER);
197
198
199 -- ============================================================================
200 -- Name : Load_Trans_Attrs_recs
201 -- Description : This procedure will be used to load the PL-SQL records
202 --
203 -- Parameters :
204 -- IN :
205 -- p_set_process_id IN Number
206 -- Batch Id to be processed
207 --
208 -- x_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl
209 -- inft type table after loading the records.
210 --
211 -- p_item_catalog_group_id IN NUMBER
212 -- ICC id for which the import has to process.
213 --
214 -- p_icc_version_number_intf IN NUMBER
215 -- ICC version number which user is providing in interface table
216 --
217 -- p_icc_version_number_act IN NUMBER
218 -- After resolving sync what is the actual version number in targer sys
219 --
220 -- OUT :
221 -- x_return_status OUT NOCOPY VARCHAR2
222 -- Used to get status of a procedure, whether it executed
223 -- Successfully or not.
224 --
225 --
226 -- x_return_msg OUT NOCOPY VARCHAR2
227 --
228 -- ============================================================================
229
230 PROCEDURE Load_Trans_Attrs_recs(
231 p_set_process_id IN NUMBER,
232 p_item_catalog_group_id IN NUMBER,
233 p_icc_version_number_intf IN NUMBER,
234 p_icc_version_number_act IN NUMBER,
235 x_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_return_msg OUT NOCOPY VARCHAR2) ;
238
239 -- ============================================================================
240 -- Name : Convert_intf_rec_to_api_rec
241 -- Description : This procedure will be used to convet interface table type
242 -- to production table type for calling create/upd/del/rel api's
243 -- Parameters :
244 -- IN : p_ta_intf_tbl IN TA_Intf_Tbl
245 -- Inteface table pl-sql record which needs to be
246 -- converted to prod rec type to call create/update/del API's.
247 --
248 -- OUT :
249 -- x_ego_ta_tbl OUT EGO_TRAN_ATTR_tbl
250 -- Original production type pl-sql recrod
251 -- ============================================================================
252
253
254 PROCEDURE Convert_intf_rec_to_api_rec (
255 p_ta_intf_tbl IN TA_Intf_Tbl ,
256 x_ego_ta_tbl OUT NOCOPY EGO_TRAN_ATTR_TBL) ;
257
258
259 -- =================================================================================
260 -- Name : Process_Trans_Attrs
261 -- Description : This is the main API which will call transact_TA for final transaciton.
262 -- This will get called from public api as well.
263 --
264 -- Parameters:
265 -- IN :
266 -- p_api_version IN NUMBER
267 -- Active API version number
268 --
269 -- p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl
270 -- Table instance having record of the type TA_Intf_Tbl
271 --
272 -- p_item_catalog_group_id IN NUMBER
273 -- ICC id for which the import has to process.
274 --
275 -- p_icc_version_number_intf IN NUMBER
276 -- ICC version number which user is providing in interface table
277 --
278 -- p_icc_version_number_act IN NUMBER
279 -- After resolving sync what is the actual version number in targer sys
280 --
281 --
282 --
283 --
284 -- OUT :
285 -- x_return_status OUT NOCOPY VARCHAR2
286 -- Used to get status of a procedure, whether it executed
287 -- Successfully or not.
288 --
289 -- x_return_msg OUT NOCOPY VARCHAR2
290 --
291 -- =================================================================================
292
293 PROCEDURE Process_Trans_Attrs (
294 p_api_version IN NUMBER,
295 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
296 p_item_catalog_group_id IN NUMBER,
297 p_icc_version_number_intf IN NUMBER,
298 p_icc_version_number_act IN NUMBER,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_return_msg OUT NOCOPY VARCHAR2) ;
301
302
303 -- =================================================================================
304 -- Name : Construct_Trans_Attrs
305 -- Description : This is same as value to id conversion with Initialize. This
306 -- is for those who are coming with public API
307 --
308 -- Parameters:
309 -- IN :
310 -- p_api_version IN NUMBER
311 -- Active API version number
312 --
313 -- p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl
314 -- Table instance having record of the type TA_Intf_Tbl
315
316 --
317 --
318 --
319 -- OUT :
320 -- x_return_status OUT NOCOPY VARCHAR2
321 -- Used to get status of a procedure, whether it executed
322 -- Successfully or not.
323
324 -- x_return_msg OUT NOCOPY VARCHAR2
325 --
326 -- =================================================================================
327
328 PROCEDURE Construct_Trans_Attrs(
329 p_api_version IN NUMBER,
330 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_return_msg OUT NOCOPY VARCHAR2) ;
333
334 -- =================================================================================
335 -- Name : Validate_Trans_Attrs
336 -- Description : This has same validation which we are doing in bulk validation.
337 -- Again this is for those who are coming with public API
338 --
339 -- Parameters:
340 -- IN :
341 -- p_api_version IN NUMBER
342 -- Active API version number
343 --
344 -- p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl
345 -- Table instance having record of the type TA_Intf_Tbl
346
347 --
348 --
349 --
350 -- OUT :
351 -- x_return_status OUT NOCOPY VARCHAR2
352 -- Used to get status of a procedure, whether it executed
353 -- Successfully or not.
354 --
355 -- x_return_msg OUT NOCOPY VARCHAR2
356 --
357 -- =================================================================================
358
359 PROCEDURE Validate_Trans_Attrs(
360 p_api_version IN NUMBER,
361 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
362 x_return_status OUT NOCOPY VARCHAR2,
363 x_return_msg OUT NOCOPY VARCHAR2);
364
365 -- =================================================================================
366 -- Name : Transact_Trans_Attrs
367 -- Description : This will handle the main transactions, Create, Update Delete.
368 --
369 -- Parameters:
370 -- IN :
371 -- p_api_version IN NUMBER
372 -- Active API version number
373 --
374 -- p_ta_intf_rec IN OUT NOCOPY ego_trans_attrs_vers_intf%ROWTYPE
375 -- Table instance having record of the type TA_Intf_Tbl
376 --
377 --
378 --
379 --
380 -- OUT :
381 -- x_return_status OUT NOCOPY VARCHAR2
382 -- Used to get status of a procedure, whether it executed
383 -- Successfully or not.
384 --
385 --
386 -- x_return_msg OUT NOCOPY VARCHAR2
387 --
388 -- =================================================================================
389
390 PROCEDURE Transact_Trans_Attrs(
391 p_api_version IN NUMBER,
392 p_ta_intf_rec IN OUT NOCOPY ego_trans_attrs_vers_intf%ROWTYPE,
393 x_return_status OUT NOCOPY VARCHAR2,
394 x_return_msg OUT NOCOPY VARCHAR2);
395
396 -- ============================================================================
397 -- Name : Update_Intf_Trans_Attrs
398 -- Description : This is responsible for updating the status as error or success,
399 -- after transact_ta
400 -- Parameters :
401 -- IN : p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl
402 -- Inteface table pl-sql record which will update the status of
403 -- records after transact_ta.
404 --
405 --
406 -- OUT :
407 -- x_return_status OUT VARCHAR2
408 -- Successfully or not.
409 --
410 -- x_return_msg OUT VARCHAR2
411 -- return message for status.
412 --
413 -- ============================================================================
414
415 PROCEDURE Update_Intf_Trans_Attrs(
416 p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
417 x_return_status OUT NOCOPY VARCHAR2,
418 x_return_msg OUT NOCOPY VARCHAR2);
419
420 -- ============================================================================
421 -- Name : Update_Intf_Err_Recs_Trans_Attrs
422 -- Description : This is responsible for updating the status as error or success,
423 -- after transact_ta
424 -- Parameters :
425 -- IN : p_set_process_id IN NUMBER
426 -- Batch Id.
427 --
428 -- p_item_catalog_group_id IN NUMBER
429 -- ICC id
430 --
431 -- p_icc_version_number_intf IN NUMBER
432 -- Interface table version number
433 --
434 --
435 -- OUT :
436 -- x_return_status OUT NOOCOPY VARCHAR2
437 -- Successfully or not.
438 --
439 -- x_return_msg OUT NOCOPY VARCHAR2
440 -- return message for status.
441 --
442 -- ============================================================================
443
444
445 PROCEDURE Update_Intf_Err_Trans_Attrs(
446 p_set_process_id IN NUMBER,
447 p_item_catalog_group_id IN NUMBER,
448 p_icc_version_number_intf IN NUMBER,
449 x_return_status OUT NOCOPY VARCHAR2,
450 x_return_msg OUT NOCOPY VARCHAR2);
451
452 -- ============================================================================
453 -- Name : Delete_Processed_Trans_Attrs
454 -- Description : This will be called by main API of concurrent program for deleting,
455 -- processed recrods
456 -- Parameters :
457 -- IN : p_set_process_id IN NUMBER
458 -- Batch Id
459 --
460 -- OUT :
461 -- x_return_status OUT NOOCOPY VARCHAR2
462 -- Successfully or not.
463 --
464 -- x_return_msg OUT NOOCOPY VARCHAR2
465 -- message for return status.
466 --
467 -- ============================================================================
468
469
470 PROCEDURE Delete_Processed_Trans_Attrs(
471 p_set_process_id IN NUMBER,
472 x_return_status OUT NOCOPY VARCHAR2,
473 x_return_msg OUT NOCOPY VARCHAR2
474 ) ;
475
476 -- ============================================================================
477 -- Name : Check_TA_IS_INVALID
478 -- Description : This is the same Function written in EGO_TRANSACTION_ATTR_PVT
479 -- but there they are only handling Draft versions and here
480 -- we are creating released TA.
481 -- Parameters :
482 -- IN : p_item_cat_group_id IN NUMBER
483 -- ICC Id
484 --
485 -- p_icc_version_number IN NUMBER
486 -- ICC version number
487 --
488 -- p_attr_id IN NUMBER
489 -- Attribute Id
490 --
491 -- p_attr_name IN VARCHAR2
492 -- Attribute Internal Name
493 --
494 -- p_attr_disp_name IN VARCHAR2
495 -- Attribute Display Name
496 --
497 -- p_attr_sequence IN NUMBER
498 -- Attribute Sequence
499
500 -- ============================================================================
501
502
503 FUNCTION Check_TA_IS_INVALID (
504 p_item_cat_group_id IN NUMBER,
505 p_icc_version_number IN NUMBER,
506 p_attr_id IN NUMBER,
507 p_attr_name IN VARCHAR2 DEFAULT NULL ,
508 p_attr_disp_name IN VARCHAR2 DEFAULT NULL ,
509 p_attr_sequence IN NUMBER DEFAULT NULL
510 )
511 RETURN BOOLEAN;
512
513 END EGO_TA_BULKLOAD_PVT ;
[Home] [Help]