1 PACKAGE Bom_Import_Pub AUTHID CURRENT_USER AS
2 /* $Header: BOMSIMPS.pls 120.23.12020000.2 2012/07/13 01:51:12 mshirkol ship $ */
3 /*# This package provides APIs for importing structure and its related entities
4 * through open interface or through web-adi interface. The supported entities
5 * are structure header, components, substitute components, reference
6 * designators and component operations. Each import batch is identified by
7 * a batch identifier.
8 * The APIs in this package supports external items and structure import
9 * for product data hub support along with support for creation of structures
10 * for existing PDH Items. It also includes Change control on data for
11 * objects that have change policies defined at object level or at batch
12 * level (for information about Change functionality, refer to the
13 * Change Management documentation).
14
15 *
16 * ------------------
17 * -- Object Types --
18 * ------------------
19 *
20 * Each of the following data types is defined as an Oracle object type
21 * or recodrd type that exists independently in the database. These these
22 * types were created for use by this package.
23 *
24 * --------------------------
25 * BATCH_OPTIONS - used to store batch options in a session
26 * --------------------------
27 *<code><pre>
28
29 * Type batch_options as object
30 * SOURCE_SYSTEM_ID EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE
31 * , BATCH_TYPE EGO_IMPORT_BATCHES_B.BATCH_TYPE%TYPE
32 * , ASSIGNEE EGO_IMPORT_BATCHES_B.ASSIGNEE%TYPE
33 * , BATCH_STATUS EGO_IMPORT_BATCHES_B.BATCH_STATUS%TYPE
34 * , MATCH_ON_DATA_LOAD EGO_IMPORT_OPTION_SETS.MATCH_ON_DATA_LOAD%TYPE
35 * , IMPORT_ON_DATA_LOAD EGO_IMPORT_OPTION_SETS.IMPORT_ON_DATA_LOAD%TYPE
36 * , IMPORT_XREF_ONLY EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE
37 * , STRUCTURE_TYPE_ID EGO_IMPORT_OPTION_SETS.STRUCTURE_TYPE_ID%TYPE
38 * , STRUCTURE_NAME EGO_IMPORT_OPTION_SETS.STRUCTURE_NAME%TYPE
39 * , STRUCTURE_EFFECTIVITY_TYPE EGO_IMPORT_OPTION_SETS.STRUCTURE_EFFECTIVITY_TYPE%TYPE
40 * , EFFECTIVITY_DATE EGO_IMPORT_OPTION_SETS.EFFECTIVITY_DATE%TYPE
41 * , FROM_END_ITEM_UNIT_NUMBER EGO_IMPORT_OPTION_SETS.FROM_END_ITEM_UNIT_NUMBER%TYPE
42 * , STRUCTURE_CONTENT EGO_IMPORT_OPTION_SETS.STRUCTURE_CONTENT%TYPE
43 * , CHANGE_NOTICE EGO_IMPORT_OPTION_SETS.CHANGE_NOTICE%TYPE
44 * , CHANGE_ORDER_CREATION EGO_IMPORT_OPTION_SETS.CHANGE_ORDER_CREATION%TYPE
45 * , PDH_BATCH varchar2(1)
46 *</pre></code>
47 * ----------------
48 * Parameters
49 * ----------------
50 * <pre>
51 * Object_Name -- BATCH_OPTIONS
52 * SOURCE_SYSTEM_ID -- Source System Id for the batch
53 * BATCH_TYPE -- Batch Type, Item Batch or Structure Batch
54 * ASSIGNEE -- Assignee for the batch
55 * BATCH_STATUS -- Status like pending, completed etc.
56 * MATCH_ON_DATA_LOAD -- Run match on data load (Y/N)
57 * IMPORT_ON_DATA_LOAD -- Run import on Data Load (Y/N)
58 * IMPORT_XREF_ONLY -- Import only Croosreferenced Items
59 * STRUCTURE_TYPE_ID -- Structure Type ID
60 * STRUCTURE_NAME -- Alternate BOM Designator/Structure Name
61 * STRUCTURE_EFFECTIVITY_TYPE -- Effectivity Control
62 * EFFECTIVITY_DATE -- Effectivity Date for Date Effectivity Structures
63 * FROM_END_ITEM_UNIT_NUMBER -- From End Item Unit Number for Unit Effectivity
64 * STRUCTURE_CONTENT -- Complete Structure (Y/N)
65 * CHANGE_NOTICE -- Change Notice
66 * CHANGE_ORDER_CREATION -- N - New, E - Existing , I - None/Ignore Change
67 * PDH_BATCH -- Y - yes, N - NO
68 </pre>
69 * @rep:scope public
70 * @rep:product BOM
71 * @rep:displayname Structure Import
72 * @rep:compatibility S
73 * @rep:category BUSINESS_ENTITY BOM_BILL_OF_MATERIAL
74 */
75 /***************************************************************************
76 --
77 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
78 -- All rights reserved.
79 --
80 -- FILENAME
81 --
82 -- BOMSIMPS.pls
83 --
84 -- DESCRIPTION
85 --
86 -- Spec of package Bom_Import_Pub
87 --
88 -- NOTES
89 --
90 -- HISTORY
91 --
92 -- 04-May-2005 Sreejith Nelloliyil Initial Creation
93 -- 05-May-2005 Dinu Krishnan Created the APIs
94 -- 1.RESOLVE_XREFS_FOR_BATCH
95 -- 2.Update Match Data
96 -- 3.Update Bill Info
97 -- 4.Check Component Exist
98 ***************************************************************************/
99
100
101 TYPE BATCH_OPTIONS IS RECORD
102 ( SOURCE_SYSTEM_ID EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE
103 , BATCH_TYPE EGO_IMPORT_BATCHES_B.BATCH_TYPE%TYPE
104 , ASSIGNEE EGO_IMPORT_BATCHES_B.ASSIGNEE%TYPE
105 , BATCH_STATUS EGO_IMPORT_BATCHES_B.BATCH_STATUS%TYPE
106 , MATCH_ON_DATA_LOAD EGO_IMPORT_OPTION_SETS.MATCH_ON_DATA_LOAD%TYPE
107 , IMPORT_ON_DATA_LOAD EGO_IMPORT_OPTION_SETS.IMPORT_ON_DATA_LOAD%TYPE
108 , IMPORT_XREF_ONLY EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE
109 , STRUCTURE_TYPE_ID EGO_IMPORT_OPTION_SETS.STRUCTURE_TYPE_ID%TYPE
110 , STRUCTURE_NAME EGO_IMPORT_OPTION_SETS.STRUCTURE_NAME%TYPE
111 , STRUCTURE_EFFECTIVITY_TYPE EGO_IMPORT_OPTION_SETS.STRUCTURE_EFFECTIVITY_TYPE%TYPE
112 , EFFECTIVITY_DATE EGO_IMPORT_OPTION_SETS.EFFECTIVITY_DATE%TYPE
113 , FROM_END_ITEM_UNIT_NUMBER EGO_IMPORT_OPTION_SETS.FROM_END_ITEM_UNIT_NUMBER%TYPE
114 , STRUCTURE_CONTENT EGO_IMPORT_OPTION_SETS.STRUCTURE_CONTENT%TYPE
115 , CHANGE_NOTICE EGO_IMPORT_OPTION_SETS.CHANGE_NOTICE%TYPE
116 , CHANGE_ORDER_CREATION EGO_IMPORT_OPTION_SETS.CHANGE_ORDER_CREATION%TYPE
117 , PDH_BATCH varchar2(1)
118 , ADD_ALL_TO_CHANGE_FLAG EGO_IMPORT_OPTION_SETS.ADD_ALL_TO_CHANGE_FLAG%TYPE
119 );
120
121 --TYPE VARCHAR2_VARRAY is VARRAY(3) OF VARCHAR2(25); we have an xdf for this
122
123
124 --
125 -- Global Constants
126 --
127 --G_PDH_SRCSYS_ID NUMBER := 20202;
128 G_PDH_SRCSYS_ID NUMBER := EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID; --Bom_Common_Definitions.Get_Pdh_Srcsys_Code;
129 --EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID;
130 G_APP_SHORT_NAME VARCHAR2(3) := 'BOM';
131
132 -- Bug 13873323 start
133 G_UTL_FILE_DIR VARCHAR2(2000);
134 G_HAS_UTL_FILE_DIR VARCHAR2(1) := 'N';
135 -- Bug 13873323 end
136
137 -- PIM for Telco Validations
138 -- Telco Library validation is commented as it was
139 -- decided not to provide/support validations for this attributes.
140 -- G_COM_VALDN_FAIL CONSTANT NUMBER := 3.65;
141
142 /************************************************************************
143 * Procedure: Populate_Struct_Interface_Rows
144 * Purpose : This method will populate the Structure and Component Interface
145 * tables from EGO_BULKLOAD_INTF Table. This API will be invoked
146 * by EGO-WEBADI program. API queries the display format metadata
147 * and populates the interface columns accordingly. The table
148 * EGO_BULKLOAD_INTF stores the data uploaded by XL WEB-ADI interface.
149
150
151
152 * Parameters:
153 * p_batch_id IN
154 * p_result_format_usageId IN
155 * x_error_msg OUT
156 * x_return_code OUT
157 **************************************************************************/
158 /*#
159 * This method will populate the structure and component Interface tables
160 * from EGO_BULKLOAD_INTF Table. This table holds the data loaded by
161 * XL (WEB-ADI) Interface by users.
162 * @param p_batch_id batch identifier
163 * @param p_resultfmt_usage_id result format usage id
164 * @param p_user_id User Id
165 * @param p_conc_request_id Concurrent Request Id
166 * @param p_language_code Language Code
167 * @param p_start_upload - This could have values 'T' - yes, 'F' - no, 'J' - from JCP
168 * @param x_errbuff OUT Error Message.
169 * @param x_retcode Return code holding return status
170 * @rep:scope public
171 * @rep:lifecycle active
172 * @rep:displayname Perform Attribute Rollup on a BOM/Product Structure
173 */
174 PROCEDURE Process_Structure_Data
175 ( p_batch_id IN NUMBER ,
176 p_resultfmt_usage_id IN NUMBER,
177 p_user_id IN NUMBER,
178 p_conc_request_id IN NUMBER,
179 p_language_code IN VARCHAR2,
180 p_start_upload IN VARCHAR2,
181 x_errbuff IN OUT NOCOPY VARCHAR2,
182 x_retcode IN OUT NOCOPY VARCHAR2
183 );
184
185
186 /*#
187 * This procedure will update the Bom Structure and Components
188 * Interface tables with the cross reference data obtained from
189 * Mtl_Cross_References.This API will update the Cross Referenced data
190 * for record in a batch which have matching entries in Mtl_Cross_References
191 * tabele.
192 * @param p_batch_id Batch Identifier for the batch being Imported
193 * @param x_Mesg_Token_Tbl Error handler Message Token
194 * @param x_Return_Status Return code holding return status
195 * @rep:scope private
196 * @rep:lifecycle active
197 * @rep:displayname Resolve Cross References
198 */
199
200 PROCEDURE RESOLVE_XREFS_FOR_BATCH
201 (
202 p_batch_id IN NUMBER
203 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
204 , x_Return_Status IN OUT NOCOPY VARCHAR2
205 );
206
207 /*#
208 * This procedure will update the Bom Structure and Components
209 * Interface tables with the PDH matched data fro Ego_Item_Matches table
210 * This API will will update the Matched PDH Data
211 * for record in a batch which have matching entries
212 * @param p_batch_id Batch Identifier for the batch being Imported
213 * @param p_source_system_id Source System Identifier for the Batch
214 * @param x_Mesg_Token_Tbl Error handler Message Token
215 * @param x_Return_Status Return code holding return status
216 * @rep:scope private
217 * @rep:lifecycle active
218 * @rep:displayname Update Match Data
219 */
220 PROCEDURE UPDATE_MATCH_DATA
221 (
222 p_batch_id IN NUMBER
223 , p_source_system_id IN NUMBER
224 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
225 , x_Return_Status IN OUT NOCOPY VARCHAR2
226 );
227
228
229 /*#
230 * This procedure will Update the Bom Structure and Components Interface
231 * tables with Bill Sequence Id,Component Sequence Id and Transaction Type
232 * Info based on the matched or cross referenced data that the record in the
233 * Interface table will have.If the Header interface table record has a valid
234 * Structure Header information in PDH then the Bill Squence Id for that existing
235 * Structure will be populated in the Header Interface table record.The transaction
236 * type will be changed to UPDATE only if the user entered value is 'SYNC'.If the
237 * target bill doesnt exist then the transaction type will be updated to CREATE.
238
239
240 * Also for all the component records for each Header this API will update the
241 * Component Sequence Id,Bill Sequence Id and the Transaction Type if the Component
242
243 * specified by data in the interface table record exist in the target Structure.
244 * If the Target Structure has any component that doesnt match with any of the
245 * source Components then that will be entered into the Interface table with a
246 * transaction type of Delete.
247 * @param p_batch_id Batch Identifier for the batch being Imported
248 * @param x_Mesg_Token_Tbl Error handler Message Token
249 * @param x_Return_Status Return code holding return status
250 * @rep:scope private
251 * @rep:lifecycle active
252 * @rep:displayname Update Bill Info
253 */
254 PROCEDURE UPDATE_BILL_INFO
255 (
256 p_batch_id IN NUMBER
257 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
258 , x_Return_Status IN OUT NOCOPY VARCHAR2
259 );
260
261 /**
262 * This procedure is used by the EGO team to notify that
263 * matching of all the uploaded records is over and
264 * further processing can be continued.
265 * @param p_batch_id Batch Identifier
266 * @param p_init_msg_list Message List Initializer Flag
267 * @param x_return_status Return Status of the API
268 * @param x_Error_Mesg Error Message
269 * @param p_debug Debug Flag
270 * @param p_output_dir Output Directory
271 * @param p_debug_filename Debug File Name
272 * @rep:scope private
273 * @rep:lifecycle active
274 * @rep:displayname Matching Complete
275 */
276 PROCEDURE Matching_Complete
277 (
278 p_batch_id IN NUMBER
279 , p_init_msg_list IN VARCHAR2
280 , x_return_status IN OUT NOCOPY VARCHAR2
281 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
282 , p_debug IN VARCHAR2
283 , p_output_dir IN VARCHAR2
284 , p_debug_filename IN VARCHAR2
285 );
286
287 /**
288 * This procedure is used by the EGO team to notify that
289 * matching of all the uploaded records is over and
290 * further processing can be continued.
291 * @param p_batch_id Batch Identifier
292 * @param x_return_status Return Status of the API
293 * @param x_Error_Mesg Error Message
294 * @rep:scope public
295 * @rep:lifecycle active
296 * @rep:displayname Matching Complete
297 */
298 PROCEDURE Matching_Complete
299 (
300 p_batch_id IN NUMBER
301 , x_return_status IN OUT NOCOPY VARCHAR2
302 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
303 );
304
305 /**
306 * This is the Function for getting the attribute difference.
307 * This returns the User Attribute Values for both Source System
308 * item and Pdh Item.This function will retrieve the values for
309 * both Component Base and Component Extended Attributes.
310 * If any of source item or pdh item is null it returns null as
311 * attribute values for that item.
312 * @param p_batch_id Batch Identifier for the Batch Imported
313 * @param p_ss_record_id Source System Reference for the Source System Item/Component
314 * @param p_comp_seq_id Component Sequence Id for the Pdh Component if any.
315 * @param p_str_type_id Structure Type Id from the Batch Options.
316 * @param p_effec_date Effectivity Date for the component
317 * @param p_op_seq_num Operation Sequence Number for the Component
318 * @param p_item_id Component Item Id
319 * @param p_org_id Organization Id
320 * @rep:scope private
321 * @rep:lifecycle active
322 * @rep:displayname Get Component Attributes Difference
323 */
324
325 FUNCTION BOM_GET_COMP_ATTR_DATA
326 (
327 p_batch_id NUMBER,
328 p_ss_record_id VARCHAR2,
329 p_comp_seq_id NUMBER,
330 p_str_type_id NUMBER,
331 p_effec_date DATE,
332 p_op_seq_num NUMBER,
333 p_item_id NUMBER,
334 p_org_id NUMBER,
335 p_intf_uniq_id NUMBER
336 ) RETURN Bom_Attr_Diff_Table_Type;
337
338
339 FUNCTION get_ref_desgs
340 (
341 p_batch_id IN NUMBER
342 , p_comp_rec_id IN VARCHAR2
346 , p_op_seq_num IN NUMBER
343 , p_comp_seq_id IN NUMBER
344 , p_mode IN NUMBER
345 , p_effec_date IN DATE
347 , p_item_id IN NUMBER
348 , p_org_id IN NUMBER
349 )RETURN VARCHAR2;
350
351
352 /**
353 * This procedure does the Value updation for User Attributes.
354 * Based on the process_status of the component this API will
355 * update the corresponding User Attribute Rows with the component
356 * sequence ids and Bill Sequence Ids and set process_flag depending on that
357 * of Component.After setting the values it calls the Ext API for User
358 * Attribute Bulk Load.
359 * @param p_batch_id Batch Identifier for the Batch Imported
360 * @param p_transaction_id Transaction Id for the Component
361 * @param p_comp_seq_id Component Sequence Id of the Component
362 * @param p_bill_seq_id Bill Sequence Id for the Header
363 * @param p_call_Ext_Api Flag to check whether to call the Ext API
364 * @param x_Mesg_Token_Tbl Error Handlers Message Token Table
365 * @param x_Return_Status Return Status after processing
366 * @rep:scope private
367 * @rep:lifecycle active
368 * @rep:displayname Update User Attribute Data
369
370 */
371 PROCEDURE Update_User_Attr_Data
372 (
373 p_batch_id IN NUMBER
374 , p_transaction_id IN NUMBER
375 , p_comp_seq_id IN NUMBER
376 , p_bill_seq_id IN NUMBER
377 , p_call_Ext_Api IN VARCHAR2
378 , p_parent_id IN NUMBER
379 , p_org_id IN NUMBER
380 , x_Return_Status IN OUT NOCOPY VARCHAR2
381 , x_Error_Text IN OUT NOCOPY VARCHAR2
382 );
383
384 /**
385 * This procedure is the starting point for the existing open interface
386 * tables being used to create batches.
387 * Users will call this API once the data load for a batch is done in the
388 * bom interface tables.
389 * @param p_batch_id Batch Identifier for the batch being Imported
390 * @param x_error_message Error Message
391 * @param x_return_code Return code holding return status
392 * @rep:scope public
393 * @rep:lifecycle active
394 * @rep:displayname Data Upload Complete
395 */
396
397 PROCEDURE DATA_UPLOAD_COMPLETE
398 (
399 p_batch_id IN NUMBER
400 , x_error_message OUT NOCOPY VARCHAR2
401 , x_return_code OUT NOCOPY VARCHAR2
402
403 );
404
405 /**
406 *This procedure is the starting point for the existing open interface
407 *tables being used to create batches.Users will call this API once the data load for a batch is
408 *done in the bom interface tables.
409 * @param p_batch_id Batch Identifier for the batch being Imported
410 * @param p_init_msg_list Init Message List Flag
411 * @param x_error_message Error Message
412 * @param x_return_code Return code holding return status
413 * @param p_output_dir Out Put directory for logging
414 * @param p_debug_filename Debug File name
415 * @rep:scope public
416 * @rep:lifecycle active
417 * @rep:displayname Data Upload Complete
418 */
419 PROCEDURE DATA_UPLOAD_COMPLETE
420 (
421 p_batch_id IN NUMBER
422 , p_init_msg_list IN VARCHAR2
423 , x_return_status IN OUT NOCOPY VARCHAR2
424 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
425 , p_debug IN VARCHAR2
426 , p_output_dir IN VARCHAR2
427 , p_debug_filename IN VARCHAR2
428 );
429
430
431
432 /**
433 * This procedure is called for importing the structure entities for a batch
434 * This API will import data from interface tables and update the
435 * production tables. This API will launch BOM Java Concurrent Program.
436 * @param p_batch_id Batch Identifier for the batch being Imported
437 * @param x_error_message Error Message
438 * @param x_return_code Return code holding return status
439 * @rep:scope private
440 * @rep:lifecycle active
441 * @rep:displayname Import Structure Data
442 */
443
444
445 PROCEDURE IMPORT_STRUCTURE_DATA
446 (
447 p_batch_id IN NUMBER
448 , p_items_import_complete IN VARCHAR2
449 , p_callFromJCP IN VARCHAR2
450 , p_request_id IN NUMBER
451 , x_error_message OUT NOCOPY VARCHAR2
452 , x_return_code OUT NOCOPY VARCHAR2
453 );
454
455
456
457 /**
458 * This procedure is called for pre-processing the rows prior to
459 * calling the BOM Java Concurrent Program.
460 * This API will set the row status to a process_flag value of 5
461 * based on batch options for change management to process
462 * The pre-process will resolve all the source system to id
463 * conversion for batch records.
464 * @param p_batch_id Batch Identifier for the batch being Imported
465 * @param x_error_message Error Message
466 * @param x_return_code Return code holding return status
467 * @rep:scope private
468 * @rep:lifecycle active
469 * @rep:displayname Pre-Process Import Rows
470
471 */
472
473 PROCEDURE PRE_PROCESS_IMPORT_ROWS
474 (
475 p_batch_id IN NUMBER
476 , p_items_import_complete IN VARCHAR2
477 , x_error_message OUT NOCOPY VARCHAR2
478 , x_return_code OUT NOCOPY VARCHAR2
479 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
480 );
481
482 /**
483 * This procedure propagates the confirmation status to various structure
484
485 * entities based on item interface tables confirmation status.
486 * The match_status could be confirmed, unconfirmed or excluded
487 * The transaction boundary for a structure is considered to be
488 * structure, first level of its components and the child entities for
492 * @param p_batch_id Batch Identifier for the batch being Imported
489 * that level of components. Unconfirmed rows for any component result
490 * in not processing the whole structure. However exclusion of a component
491 * will result in processing
493 * @param x_error_message Error Message
494 * @param x_return_code Return code holding return status
495 * @rep:scope private
496 * @rep:lifecycle active
497 * @rep:displayname Pre-Process Import Rows
498
499 */
500
501 PROCEDURE PROPAGATE_CONFIRMATION_STATUS
502 (
503 p_batch_id IN NUMBER
504 , x_error_message OUT NOCOPY VARCHAR2
505 , x_return_code OUT NOCOPY VARCHAR2
506 );
507
508 /**
509 * Concurrent Program Replacement for BMCOIN
510 * @param p_batch_id Batch Identifier for the batch being Imported
511 * @param x_error_message Error Message
512 * @param x_return_code Return Code Success or Error
513 * @p_organization_id Organization Id for which the import program will run
514 * @param p_all_organization All Organizations Option
515 * @param p_import_routings Import Routings Option
516 * @param p_import_bills Import Bills Option
517 * @param p_delete_rows Delete the Processed Rows from Interface Tables Option
518 * @param p_batch_id Batch Identifier for processing a set of records
519 * @rep:lifecycle active
520 * @rep:displayname Pre-Process Import Rows
521 */
522 PROCEDURE Import_Interface_Rows
523 (
524 x_err_buffer OUT NOCOPY VARCHAR2,
525 x_return_code OUT NOCOPY VARCHAR2,
526 p_organization_id IN NUMBER,
527 p_all_organization IN VARCHAR2,
528 p_import_routings IN VARCHAR2,
529 p_import_bills IN VARCHAR2,
530 p_delete_rows IN VARCHAR2,
531 p_batch_id IN NUMBER
532 );
533
534 /**
535 * This is the procedure for updating the Bill with item names
536 * for a Pdh Batch Import.If it is a Pdh Batch Import this
537 * API will be called and this API will do the id to val
538 * conversion if needed.This will also populate the
539 * source_system_reference with the Item Names or Component
540 * names.This is for the Structure Import UI to show the
541 * details of the batch even for a Pdh Batch Import which will
542 * not have any source_system_reference.
543
544 * @param p_batch_id Batch Identifier for the Pdh Batch
545 * @rep:scope private
546 * @rep:lifecycle active
547 * @rep:displayname Update Bill for Pdh Import
548 */
549 PROCEDURE Update_Bill_Val_Id
550 (
551 p_batch_id IN NUMBER
552 , x_return_status IN OUT NOCOPY VARCHAR2
553 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
554 );
555
556 /*
557 * The following functions are used for getting the
558 * values of FND_API.G_MISS_NUM,FND_API.G_MISS_CHAR and
559 * FND_API.G_MISS_DATE in the SQL query used in
560 * Bom Attributes Diff VO Query
561 */
562 FUNCTION get_G_MISS_NUM RETURN NUMBER;
563
564 FUNCTION get_G_MISS_CHAR RETURN VARCHAR;
565
566
567 FUNCTION get_G_MISS_DATE RETURN DATE;
568
569 /**
570 * This is the procedure for updating the BOM interface
571 * tables with the newly confirmed pdh item ids.This API will
572 * be called by EGO when the user confirms any source item in a
573 * batch and when they propagate the confirmed pdh item ids to
574 * subsequent tables.
575 * Do We Need This For BOM if the newly confirmed source item
576 * is a header item then we need to update Header Interface table with
577 * the bill sequence id of the structure of the newly confirmed pdh item
578 * We also need to update Bom Components Interface table with this
579 * new bill sequence id for all the components of the confirmed source item.
580
581 * If the newly confirmed source item is a component then we need to check whe
582 ther
583
584 * the corresponding pdh item exist as a component in the target bill,if not t
585 hen add it
586
587 * as a new component to the target pdh strcuture.
588 * @param p_batch_id Batch Identifier for the Imported Batch
589 * @param p_ssRef_varray Source System Refence Array for all the source items
590 confirmed
591
592 * @param p_item_id_varray Item Id array containing the Ids of the confirmed P
593 DH items
594
595
596 * @param x_error_message Error Message
597 * @param x_return_code Return code holding return status
598 * @rep:scope private
599 * @rep:lifecycle active
600 * @rep:displayname Update Confirmed Items
601 */
602
603 PROCEDURE Update_Confirmed_Items
604 (
605 p_batch_id IN NUMBER
606 , p_ssRef_varray IN VARCHAR2_VARRAY
607 , x_Error_Message IN OUT NOCOPY VARCHAR2
608 , x_Return_Status IN OUT NOCOPY VARCHAR2
609 );
610
611
612 /****************** Local Procedures Section Ends ******************/
613 /*
614 * The Method that willl be invoked by JCP
615 */
616
617 PROCEDURE Process_Structure_Data
618 (
619 p_batch_id IN NUMBER
620 );
621
622 /*
623 * The Method will return the Primay display name is the internal
624 * name is *NULL* - used in the UI
625 * @param p_struct_Internal_Name Structure Internal Name
626 * @param Returns Structure Name
627 * @rep:scope private
628 * @rep:lifecycle active
629 * @rep:displayname Get Primary Structure name
630 */
631 FUNCTION Get_Primary_StructureName
632 (p_struct_Internal_Name IN VARCHAR2)
633 RETURN VARCHAR2;
634
635 PROCEDURE Check_Change_Options
636 (
637 p_batch_id IN NUMBER,
641
638 x_error_code IN OUT NOCOPY VARCHAR2,
639 x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
640 );
642 PROCEDURE PROCESS_ALL_COMPS_BATCH
643 (
644 p_batch_id IN NUMBER
645 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
646 , x_Return_Status IN OUT NOCOPY VARCHAR2
647 );
648
649 /*
650 * The Method will return the BATCHID sequence to be used for OI Process
651 * @param Returns Batch ID
652 * @rep:scope public
653 * @rep:lifecycle active
654 * @rep:displayname Get Batch ID
655 */
656 FUNCTION Get_BatchId
657 RETURN NUMBER;
658
659 /*
660 * This API will delete all the records from all the
661 * BOM interface tables for the given batch id.
662 * @param p_batch_id Batch Id for which data is to be deleted
663 * @param x_error_mesg Error Message
664 * @param x_ret_code Return Code
665 * @rep:scope private
666 * @rep:lifecycle active
667 * @rep:displayname Delete Interface Records
668 */
669
670 PROCEDURE Delete_Interface_Records
671 (
672 p_batch_id IN NUMBER
673 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
674 , x_Ret_Code IN OUT NOCOPY VARCHAR2
675 );
676
677 /**
678 * Procedure to merge the duplicate records starting with the
679 * components and then propagating to the child entities.
680 * @param p_batch_id Batch Id for which data is to be deleted
681 * @param x_Ret_Status Return Code
682 * @param x_Error_Mesg Error Message
683 * @rep:scope private
684 * @rep:lifecycle active
685 * @rep:displayname Merge Duplicate Rows
686 */
687 PROCEDURE Merge_Duplicate_Rows
688 (
689 p_batch_id IN NUMBER,
690 x_Ret_Status IN OUT NOCOPY VARCHAR2,
691 x_Error_Mesg IN OUT NOCOPY VARCHAR2
692 );
693 /*
694 * Procedure to merge Reference Designators
695 * @param p_batch_id Batch Id for which data is to be deleted
696 * @p_comp_seq_id Component Sequence Id
697 * @p_comp_name Component Name
698 * @p_comp_ref Component Source System Reference
699 * @p_effec_date Effectivity Date for the component
700 * @p_op_seq Operation Sequence Number for the component
701 * @p_new_effec_date New Effectivity Date for a changed component
702 * @p_new_op_seq New Operation Sequence Number
703 * @p_from_unit From end item unit number
704 * @p_from_item_id From end item revision id
705 * @p_parent_name Parent Name
706 * @p_parent_ref Parent Source System Reference
707 * @x_Ret_Status Return Status
708 * @x_Error_Mesg Error Message
709 * @rep:scope private
710 * @rep:lifecycle active
711 * @rep:displayname Merge Reference Designators
712 */
713
714 PROCEDURE Merge_Ref_Desgs
715 (
716 p_batch_id IN NUMBER,
717 p_comp_seq_id IN NUMBER,
718 p_comp_name IN VARCHAR2,
719 p_comp_ref IN VARCHAR2,
720 p_effec_date IN DATE,
721 p_op_seq IN NUMBER,
722 p_new_effec_date IN DATE,
723 p_new_op_seq IN NUMBER,
724 p_from_unit IN VARCHAR2,
725 p_from_item_id IN NUMBER,
726 p_parent_name IN VARCHAR2,
727 p_parent_ref IN VARCHAR2,
728 x_Ret_Status IN OUT NOCOPY VARCHAR2,
729 x_Error_Mesg IN OUT NOCOPY VARCHAR2
730 );
731
732 /*
733 * Procedure to merge user attributes duplicate rows
734 * @p_batch_id Batch Id
735 * @p_comp_seq Component Sequence Id
736 * @p_comp_name Component Name
737 * @p_comp_ref Component Source System Reference
738 * @p_txn_id Transaction Id
739 * @x_Ret_Status Return Status
740 * @x_Error_Mesg Error Message
741 * @rep:scope private
742 * @rep:lifecycle active
743 * @rep:displayname Merge Component Attributes
744 */
745 PROCEDURE Merge_User_Attrs
746 (
747 p_batch_id IN NUMBER,
748 p_comp_seq IN NUMBER,
749 p_comp_name IN VARCHAR2,
750 p_comp_ref IN VARCHAR2,
751 p_txn_id IN NUMBER,
752 p_par_name IN VARCHAR2,
753 p_par_ref IN VARCHAR2,
754 p_org_id IN NUMBER,
755 p_org_code IN VARCHAR2,
756 x_Ret_Status IN OUT NOCOPY VARCHAR2,
757 x_Error_Mesg IN OUT NOCOPY VARCHAR2
758 );
759 /*
760 * Procedure to update the interface records
761 * with process_flag = 5 when change options are
762 * set in the batch or there are change policy for the
763 * structure
764 * @param p_batch_id Batch Identifier
765 * @rep:scope private
766 * @rep:lifecycle active
767 * @rep:displayname Porcess CM Options
768 */
769 PROCEDURE Process_CM_Options(p_batch_id IN NUMBER);
770
771 PROCEDURE Get_Item_Security_Predicate
772 (
773 p_object_name IN VARCHAR2,
774 p_party_id IN VARCHAR2,
775 p_privilege_name IN VARCHAR2,
776 p_table_alias IN VARCHAR2,
777 x_security_predicate OUT NOCOPY VARCHAR2
778 );
779 FUNCTION Get_Item_Matches
780 (
781 p_batch_id IN NUMBER,
782 p_ss_ref IN VARCHAR2
783 )
784 RETURN VARCHAR2;
785
786 END Bom_Import_Pub; -- Package spec
[Home] [Help]