1 PACKAGE INV_COPY_ORGANIZATION_REPORT AUTHID CURRENT_USER AS
2 -- $Header: INVCORPS.pls 120.1.12000000.2 2007/02/26 11:28:05 myerrams ship $
3 --+===========================================================================+
4 --| Copyright (c) YYYY Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| FILENAME |
9 --| INVCORPS.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package will be used to generated the data required for Copy Orgn |
13 --| Report. |
14 --| |
15 --| HISTORY |
16 --| 07/21/2003 nkilleda Created. |
17 --| 04/26/2004 nesoni modified for bug 3550415. |
18 --| New function Receiving_Subinv_Exist added. |
19 --| 11/06/2004 shpandey Bug 3683490 Added New function clob_to_varchar |
20 --| added to convert clob |
21 --| field to varchar2 as to_char function(clob) is not |
22 --| supported for version 8i. |
23 --| 12/04/2005 shpandey Added a function Get_Err_Wip_Acc_Classes for |
24 --| supporting Wip Accounting Classes entity for R12 |
25 --| 21/02/2007 myerrams Bug5592181 Added New function Get_Err_StdOperations|
26 --| to Validate StandardOperations created for new org |
27 --| against those in model org. |
28 --+===========================================================================+
29 --=============================================================================
30 -- TYPE DECLARATIONS
31 --=============================================================================
32
33 --=============================================================================
34 -- Commented as these are not required anymore.
35 --=============================================================================
36 --TYPE Entity_Rec_Type IS RECORD
37 --( entity_type VARCHAR2(100)
38 --, table_name VARCHAR2(100)
39 --, field_name VARCHAR2(100)
40 --);
41 --TYPE Entity_Table IS TABLE OF Entity_Rec_Type
42 --INDEX BY BINARY_INTEGER;
43 --
44 --m_user_values Entity_Table;
45 --m_entities Entity_Table;
46 --m_entities_pk Entity_Table;
47 --m_entities_temp Entity_Table;
48 --
49 --TYPE PK_Array IS TABLE OF VARCHAR2(30);
50 --TYPE Primary_Key_Rec IS RECORD
51 --( entity_type VARCHAR2(100)
52 --, table_name VARCHAR2(100)
53 --, primary_key PK_Array
54 --);
55 --TYPE Primary_Key_Hash IS TABLE OF Primary_Key_Rec
56 --INDEX BY BINARY_INTEGER;
57 --
58 --m_primary_key_hash Primary_Key_Hash;
59 --=============================================================================
60
61 TYPE Token_Rec IS RECORD
62 ( token_name VARCHAR2(100)
63 , token_value VARCHAR2(100)
64 );
65 TYPE Token_Hash IS TABLE OF Token_Rec
66 INDEX BY BINARY_INTEGER;
67
68 TYPE Char_Array IS TABLE OF VARCHAR2(240);
69
70 TYPE Num_Array IS TABLE OF NUMBER;
71
72 TYPE Entity_Primary_Key IS REF CURSOR;
73
74 s_error_tokens Token_Hash;
75
76 --=============================================================================
77 -- CONSTANTS
78 --=============================================================================
79 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_COPY_ORGANIZATION_REPORT';
80
81 --=============================================================================
82 -- PUBLIC VARIABLES
83 --=============================================================================
84 g_variable NUMBER;
85
86 --=============================================================================
87 -- PROCEDURES AND FUNCTIONS
88 --=============================================================================
89
90 --=============================================================================
91 -- PROCEDURE NAME: Generate_Report_Data
92 -- PRE-CONDITIONS: None.
93 -- DESCRIPTION : Main method called from Before_Report trigger of the report.
94 -- Calls initialize(), Validate_Locs() and Validate_Orgs()
95 -- functions to generate report data.
96 -- PARAMETERS :
97 -- p_api_version REQUIRED. As per standards.
98 -- p_init_msg_list REQUIRED. As per standards.
99 -- p_commit REQUIRED. As per standards.
100 -- x_return_status REQUIRED. Value can be
101 -- FND_API.G_RET_STS_SUCCESS
102 -- FND_API.G_RET_STS_ERROR
103 -- FND_API.G_RET_STS_UNEXP_ERROR
104 -- x_msg_count REQUIRED. As per standards.
105 -- x_msg_data REQUIRED. As per standards.
106 -- p_group_code REQUIRED. Group code created for Copy Org request.
107 -- p_model_org_code REQUIRED. Model organization to copy from.
108 -- p_organization_code REQUIRED. New organization to be created.
109 -- p_copy_boms REQUIRED. Flag indicating to Copy BOMs.
110 -- p_copy_routings REQUIRED. Flag indicating to Copy Routings.
111 -- p_copy_items REQUIRED. Flag indicating to Copy Items.
112 -- p_copy_ship_net REQUIRED. Flag indicating to Copy Shipping Networks.
113 -- p_assgn_hier REQUIRED. Flag indicating to Copy Assign Hierarchies.
114 -- p_location_status REQUIRED. Flag indicating if loc was created by req.
115 --
116 -- EXCEPTIONS : None.
117 --
118 --=============================================================================
119 PROCEDURE Generate_Report_Data
120 ( p_api_version IN NUMBER
121 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
122 , p_commit IN VARCHAR2 := FND_API.G_FALSE
123 , x_return_status OUT NOCOPY VARCHAR2
124 , x_msg_count OUT NOCOPY NUMBER
125 , x_msg_data OUT NOCOPY VARCHAR2
126 , p_group_code IN VARCHAR2
127 , p_model_org_code IN VARCHAR2
128 , p_organization_code IN VARCHAR2
129 , p_copy_boms IN VARCHAR2
130 , p_copy_routings IN VARCHAR2
131 , p_copy_items IN VARCHAR2
132 , p_copy_ship_net IN VARCHAR2
133 , p_assgn_hier IN VARCHAR2
134 , p_location_status IN VARCHAR2
135 );
136
137 --=============================================================================
138 -- API NAME : initialize()
139 -- PRE-CONDITIONS: None.
140 -- DESCRIPTION : initialize the lists m_user_values with values
141 -- from Report Table.
142 -- PARAMETERS : None
143 -- EXCEPTIONS : None.
144 --
145 -- This procedure has been obsoleted as modification of attribute values
146 -- through UI is no longer supported in Copy Org.
147 --=============================================================================
148 --PROCEDURE initialize;
149
150 --=============================================================================
151 -- API NAME : Get_Unique_List
152 -- PRE-CONDITIONS: None.
153 -- DESCRIPTION : Remove duplicate entries from an array.
154 -- PARAMETERS :
155 -- p_array REQUIRED. Array containing duplicate records.
156 --
157 -- RETURNS : Array containing unique records ( Char_Array )
158 -- EXCEPTIONS : None.
159 --
160 --=============================================================================
161 FUNCTION Get_Unique_List ( p_array Char_Array ) RETURN Char_Array;
162
163 --=============================================================================
164 -- PROC NAME : Validate_Orgs
165 -- PRE-CONDITIONS: New Organization, Location, Parameters must be created.
166 -- DESCRIPTION : Validate the org and call the function to validate entities
167 -- for model org against new org.
168 -- PARAMETERS : None.
169 -- EXCEPTIONS : None.
170 --
171 --=============================================================================
172 PROCEDURE Validate_Orgs;
173
174 --=============================================================================
175 -- PROC NAME : Validate_Locs
176 -- PRE-CONDITIONS: New Organization, Location, Parameters must be created.
177 -- DESCRIPTION : Validate the location for model org against new org.
178 -- PARAMETERS : None.
179 -- EXCEPTIONS : None.
180 --
181 --=============================================================================
182 PROCEDURE Validate_Locs;
183
184 --=============================================================================
185 -- FUNCTION NAME : Validate_Entities
186 -- PRE-CONDITIONS: New Organization, Location, Parameters must be created.
187 -- DESCRIPTION : Validate all entities for model org against new org.
188 -- PARAMETERS : None.
189 -- EXCEPTIONS : None.
190 --
191 --=============================================================================
192 FUNCTION Validate_Entities RETURN VARCHAR2;
193
194 --=============================================================================
195 -- FUNCTION NAME : Get_Err_Subinv_Names
196 -- PRE-CONDITIONS: None.
197 -- DESCRIPTION : Validate subinventories created for new org against
198 -- those in model org.
199 -- PARAMETERS : None.
200 -- EXCEPTIONS : None.
201 --
202 --=============================================================================
203 FUNCTION Get_Err_Subinv_Names RETURN Char_Array;
204
205 --=============================================================================
206 -- FUNCTION NAME : Get_Err_Bom_Resources
207 -- PRE-CONDITIONS: None.
208 -- DESCRIPTION : Validate bom resources created for new org against
209 -- those in model org.
210 -- PARAMETERS : None.
211 -- EXCEPTIONS : None.
212 --
213 --=============================================================================
214 FUNCTION Get_Err_Bom_Resources RETURN Char_Array;
215
216 --=============================================================================
217 -- FUNCTION NAME : Get_Err_Bom_Departments
218 -- PRE-CONDITIONS: None.
219 -- DESCRIPTION : Validate bom departments created for new org against
220 -- those in model org.
221 -- PARAMETERS : None.
222 -- EXCEPTIONS : None.
223 --
224 --=============================================================================
225 FUNCTION Get_Err_Bom_Departments RETURN Char_Array;
226
227 --=============================================================================
228 -- FUNCTION NAME : Get_Err_Ship_Net
229 -- PRE-CONDITIONS: None.
230 -- DESCRIPTION : Validate shipping networks created for new org against
231 -- those in model org.
232 -- PARAMETERS : None.
233 -- EXCEPTIONS : None.
234 --
235 --=============================================================================
236 FUNCTION Get_Err_Ship_Net RETURN Char_Array;
237
238 --=============================================================================
239 -- FUNCTION NAME : Get_Err_Bom_Bom
240 -- PRE-CONDITIONS: None.
241 -- DESCRIPTION : Validate boms created for new org against
242 -- those in model org.
243 -- PARAMETERS : None.
244 -- EXCEPTIONS : None.
245 --
246 --=============================================================================
247 FUNCTION Get_Err_Bom_Bom RETURN Char_Array;
248
249 --=============================================================================
250 -- FUNCTION NAME : Get_Err_Org_Information
251 -- PRE-CONDITIONS: None.
252 -- DESCRIPTION : Validate org information created for new org against
253 -- those in model org.
254 -- PARAMETERS : None.
255 -- EXCEPTIONS : None.
256 --
257 --=============================================================================
258 FUNCTION Get_Err_Org_Information RETURN Char_Array;
259
260 --=============================================================================
261 -- FUNCTION NAME : Get_Err_Mtl_Items
262 -- PRE-CONDITIONS: None.
263 -- DESCRIPTION : Validate items created for new org against
264 -- those in model org.
265 -- PARAMETERS : None.
266 -- EXCEPTIONS : None.
267 --
268 --=============================================================================
269 FUNCTION Get_Err_Mtl_Items RETURN Char_Array;
270
271 --=============================================================================
272 -- FUNCTION NAME : Get_Err_Mtl_Items_Subinv
273 -- PRE-CONDITIONS: None.
274 -- DESCRIPTION : Validate item subinventories created for new org against
275 -- those in model org.
276 -- PARAMETERS : None.
277 -- EXCEPTIONS : None.
278 --
279 --=============================================================================
280 FUNCTION Get_Err_Mtl_Items_Subinv RETURN Char_Array;
281
282 --=============================================================================
283 -- FUNCTION NAME : Get_Err_Mtl_Item_Cat
284 -- PRE-CONDITIONS: None.
285 -- DESCRIPTION : Validate item categories created for new org against
286 -- those in model org.
287 -- PARAMETERS : None.
288 -- EXCEPTIONS : None.
289 --
290 --=============================================================================
291 FUNCTION Get_Err_Mtl_Item_Cat RETURN Char_Array;
292
293 --=============================================================================
294 -- FUNCTION NAME : Get_Err_Mtl_Item_Rev
298 -- PARAMETERS : None.
295 -- PRE-CONDITIONS: None.
296 -- DESCRIPTION : Validate item revisions created for new org against
297 -- those in model org.
299 -- EXCEPTIONS : None.
300 --
301 --=============================================================================
302 FUNCTION Get_Err_Mtl_Item_Rev RETURN Char_Array;
303
304 --=============================================================================
305 -- FUNCTION NAME : Get_Err_StdOperations
306 -- PRE-CONDITIONS: None.
307 -- DESCRIPTION : Validate StandardOperations created for new org against
308 -- those in model org.
309 -- PARAMETERS : None.
310 -- EXCEPTIONS : None.
311 --
312 --=============================================================================
313 FUNCTION Get_Err_StdOperations RETURN Char_Array;
314
315 --=============================================================================
316 -- FUNCTION NAME : Get_Err_Routings
317 -- PRE-CONDITIONS: None.
318 -- DESCRIPTION : Validate routings created for new org against
319 -- those in model org.
320 -- PARAMETERS : None.
321 -- EXCEPTIONS : None.
322 --
323 --=============================================================================
324 FUNCTION Get_Err_Routings RETURN Char_Array;
325
326 --=============================================================================
327 -- FUNCTION NAME : Get_Err_Mtl_Item_Locations
328 -- PRE-CONDITIONS: None.
329 -- DESCRIPTION : Validate Item Locations created for new org against
330 -- those in model org.
331 -- PARAMETERS : None.
332 -- EXCEPTIONS : None.
333 --
334 --=============================================================================
335 FUNCTION Get_Err_Mtl_Item_Locations RETURN Char_Array;
336
337 --=============================================================================
338 -- FUNCTION NAME : Get_Err_Bom_Dept_Res
339 -- PRE-CONDITIONS: None.
340 -- DESCRIPTION : Validate department resources created for new org against
341 -- those in model org.
342 -- PARAMETERS : None.
343 -- EXCEPTIONS : None.
344 --
345 --=============================================================================
346 FUNCTION Get_Err_Bom_Dept_Res RETURN Char_Array;
347
348 --=============================================================================
349 -- FUNCTION NAME : Get_Err_Cst_Res_Ovhds
350 -- PRE-CONDITIONS: None.
351 -- DESCRIPTION : Validate resource overheads created for new org against
352 -- those in model org.
353 -- PARAMETERS : None.
354 -- EXCEPTIONS : None.
355 --
356 --=============================================================================
357 FUNCTION Get_Err_Cst_Res_Ovhds RETURN Char_Array;
358
359 --=============================================================================
360 -- FUNCTION NAME : Get_Err_Cst_Res_Costs
361 -- PRE-CONDITIONS: None.
362 -- DESCRIPTION : Validate resource costs created for new org against
363 -- those in model org.
364 -- PARAMETERS : None.
365 -- EXCEPTIONS : None.
366 --
367 --=============================================================================
368 FUNCTION Get_Err_Cst_Res_Costs RETURN Char_Array;
369
370 --=============================================================================
371 -- FUNCTION NAME : Get_Err_Bom_Dept_Classes
372 -- PRE-CONDITIONS: None.
373 -- DESCRIPTION : Validate bom department classes created for new org against
374 -- those in model org.
375 -- PARAMETERS : None.
376 -- EXCEPTIONS : None.
377 --
378 --=============================================================================
379 FUNCTION Get_Err_Bom_Dept_Classes RETURN Char_Array;
380
381 --=============================================================================
382 -- FUNCTION NAME : Get_Err_Bom_Alt_Desig
383 -- PRE-CONDITIONS: None.
384 -- DESCRIPTION : Validate bom alternate designators created for new org
385 -- against those in model org.
386 -- PARAMETERS : None.
387 -- EXCEPTIONS : None.
388 --
389 --=============================================================================
390 FUNCTION Get_Err_Bom_Alt_Desig RETURN Char_Array;
391
392 --=============================================================================
393 -- FUNCTION NAME : Get_Err_Hierarchy
394 -- PRE-CONDITIONS: None.
395 -- DESCRIPTION : Validate hierarchies to which new org is assigned against
396 -- those for model org.
397 -- PARAMETERS : None
398 -- EXCEPTIONS : None.
399 --
400 --=============================================================================
401 FUNCTION Get_Err_Hierarchy RETURN Char_Array;
402
403 --=============================================================================
404 -- PROC NAME : Insert_Row
405 -- PRE-CONDITIONS: None.
406 -- DESCRIPTION : Get the translated message from FND MESSAGES table.
407 -- PARAMETERS :
408 -- p_location_code > Location Code
409 -- p_business_group_name > Business Group Name
410 -- p_status > Status for Location / Organization
411 -- p_error_msg > Error Message
412 -- p_rec_type > REQUIRED. Record Type
413 -- p_entity_type > Entity Type
414 -- p_copy_cnt > Act Count of Records Copied
415 -- p_modify_cnt > Act Count of Records Modified
416 -- p_exp_copy_cnt > Exp Count of Records to be Copied
417 -- p_exp_modify_cnt > Exp Count of Records to be Modified
418 -- p_entity_name > Entity Record that was not copied
419 -- p_entity_inconsistency > Error message ( Not Copied / Not Modified )
420 -- p_put_orgs > Flag to determine if org codes need to be
421 -- inserted for any row.
422 -- EXCEPTIONS : None.
423 --
424 --=============================================================================
425 PROCEDURE Insert_Row
426 ( p_location_code IN VARCHAR2
427 , p_business_group_name IN VARCHAR2
428 , p_status IN VARCHAR2
429 , p_error_msg IN VARCHAR2
430 , p_rec_type IN VARCHAR2
431 , p_entity_type IN VARCHAR2
432 , p_copy_cnt IN NUMBER
433 , p_modify_cnt IN NUMBER
434 , p_exp_copy_cnt IN NUMBER
435 , p_exp_modify_cnt IN NUMBER
436 , p_entity_name IN VARCHAR2
437 , p_entity_inconsistency IN VARCHAR2
438 , p_put_orgs IN BOOLEAN
439 );
440
441 --=============================================================================
442 -- FUNCTION NAME : Get_Organization_Id
443 -- PRE-CONDITIONS: None.
444 -- DESCRIPTION : Get the organization Id for this org code.
445 -- PARAMETERS :
446 -- p_org_code REQUIRED. Org Code to be converted to Id.
447 --
448 -- EXCEPTIONS : None.
449 --=============================================================================
450 FUNCTION Get_Organization_Id ( p_org_code IN VARCHAR2 ) RETURN NUMBER;
451
452 --=============================================================================
453 -- FUNCTION NAME : Is_Entity_Modified
454 -- PRE-CONDITIONS: None.
455 -- DESCRIPTION : Return True if there are any attributes to be modified
456 -- for this entity
457 -- PARAMETERS :
458 -- p_entity_type REQUIRED. Entity to be checked for modifications.
459 --
460 -- EXCEPTIONS : None.
461 --
462 -- This procedure has been obsoleted as modification of attribute values
463 -- through UI is no longer supported in Copy Org.
464 --=============================================================================
465 --FUNCTION Is_Entity_Modified ( p_entity_type IN VARCHAR2 ) RETURN BOOLEAN;
466
467 --=============================================================================
468 -- PROC NAME : Put_Report_Data
469 -- PRE-CONDITIONS: None.
470 -- DESCRIPTION : Insert records for Qualitative / Quantitative data
471 -- for all entities.
472 -- PARAMETERS : None
473 --
474 -- EXCEPTIONS : None.
475 --
476 --=============================================================================
477 PROCEDURE Put_Report_Data;
478
479 --=============================================================================
480 -- PROC NAME : Init_Vars
481 -- PRE-CONDITIONS: None.
482 -- DESCRIPTION : Initialize variables for processing entities.
483 -- PARAMETERS : p_entity_type. REQUIRED : Entity Type being validated.
484 -- EXCEPTIONS : None.
485 --
486 --=============================================================================
487 PROCEDURE Init_Vars ( p_entity_type IN VARCHAR2 );
488
489 --=============================================================================
490 -- FUNCTION NAME : Get_Fnd_Message
491 -- PRE-CONDITIONS: None.
492 -- DESCRIPTION : Get the translated message from FND MESSAGES table.
493 -- PARAMETERS :
494 -- p_msg_name REQUIRED. Name of the error message.
495 -- p_token_array REQUIRED. List of token name/values in error message.
496 --
497 -- EXCEPTIONS : None.
498 --
499 --=============================================================================
500 FUNCTION Get_Fnd_Message
501 ( p_msg_name IN VARCHAR2
502 , p_token_array IN Token_Hash
503 ) RETURN VARCHAR2;
504
505 --=============================================================================
506 -- FUNCTION NAME : Purge_Copy_Org_Report
507 -- PRE-CONDITIONS: None.
508 -- DESCRIPTION : Purge records from the Copy Org Report table for the
509 -- group code.
510 -- PARAMETERS : None.
511 -- EXCEPTIONS : None.
512 --
513 --=============================================================================
514 FUNCTION Purge_Copy_Org_Report RETURN BOOLEAN;
515
516 --=============================================================================
517 --Bug: 3550415. New function added to verify if Receiving Subinventories exist
518 -- corresponding to Model Org
519 --=============================================================================
520 -- FUNCTION NAME : Receiving_Subinv_Exist
521 -- PRE-CONDITIONS: None.
522 -- DESCRIPTION : Returns TRUE if Receiving Subinventories exist for the
523 -- model org else returns FALSE.
524 -- PARAMETERS :
525 -- p_organization_id REQUIRED. Organization Id.
526 --
530 FUNCTION Receiving_Subinv_Exist(p_organization_id IN NUMBER ) RETURN VARCHAR2;
527 -- EXCEPTIONS : None.
528 --
529 --=============================================================================
531 --=============================================================================
532 --Bug: 3683490. New function added to convert clob field to varchar2
533 -- as to_char() function is not supported for version 8i.
534 --=============================================================================
535 /*
536 ** -------------------------------------------------------------------------
537 ** Function: clob_to_varchar
538 ** Description: Takes in a CLOB database object and returns the
539 ** corresponding VARCHAR2 object
540 ** Input:
541 ** lobsrc
542 ** The CLOB to be converted into a VARCHAR2 string
543 **
544 ** Returns:
545 ** The VARCHAR2 string that was converted from the passed in CLOB
546 ** --------------------------------------------------------------------------
547 */
548
549 FUNCTION clob_to_varchar ( lobsrc IN CLOB ) return VARCHAR2;
550
551 --=============================================================================
552 -- FUNCTION NAME : Get_Err_Wip_Acc_Classes
553 -- PRE-CONDITIONS: None.
554 -- DESCRIPTION : Validate wip accounting classes created for new org against
555 -- those in model org.
556 -- PARAMETERS : None.
557 -- EXCEPTIONS : None.
558 -- shpandey, added the function below for R12 development.
559 --=============================================================================
560 FUNCTION Get_Err_Wip_Acc_Classes RETURN Char_Array;
561
562 END INV_COPY_ORGANIZATION_REPORT;
[Home] [Help]