1 PACKAGE Bom_Import_Pub AS
2 /* $Header: BOMSIMPS.pls 120.19 2006/09/20 19:02:10 dikrishn noship $ */
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 * Procedure: Populate_Struct_Interface_Rows
133 * Purpose : This method will populate the Structure and Component Interface
134 * tables from EGO_BULKLOAD_INTF Table. This API will be invoked
135 * by EGO-WEBADI program. API queries the display format metadata
136 * and populates the interface columns accordingly. The table
137 * EGO_BULKLOAD_INTF stores the data uploaded by XL WEB-ADI interface.
138
139
140
141 * Parameters:
142 * p_batch_id IN
143 * p_result_format_usageId IN
144 * x_error_msg OUT
145 * x_return_code OUT
146 **************************************************************************/
147 /*#
148 * This method will populate the structure and component Interface tables
149 * from EGO_BULKLOAD_INTF Table. This table holds the data loaded by
150 * XL (WEB-ADI) Interface by users.
151 * @param p_batch_id batch identifier
152 * @param p_resultfmt_usage_id result format usage id
153 * @param p_user_id User Id
154 * @param p_conc_request_id Concurrent Request Id
155 * @param p_language_code Language Code
156 * @param p_start_upload - This could have values 'T' - yes, 'F' - no, 'J' - from JCP
157 * @param x_errbuff OUT Error Message.
158 * @param x_retcode Return code holding return status
159 * @rep:scope public
160 * @rep:lifecycle active
161 * @rep:displayname Perform Attribute Rollup on a BOM/Product Structure
162 */
163 PROCEDURE Process_Structure_Data
164 ( p_batch_id IN NUMBER ,
165 p_resultfmt_usage_id IN NUMBER,
166 p_user_id IN NUMBER,
167 p_conc_request_id IN NUMBER,
168 p_language_code IN VARCHAR2,
169 p_start_upload IN VARCHAR2,
170 x_errbuff IN OUT NOCOPY VARCHAR2,
171 x_retcode IN OUT NOCOPY VARCHAR2
172 );
173
174
175 /*#
176 * This procedure will update the Bom Structure and Components
177 * Interface tables with the cross reference data obtained from
178 * Mtl_Cross_References.This API will update the Cross Referenced data
179 * for record in a batch which have matching entries in Mtl_Cross_References
180 * tabele.
181 * @param p_batch_id Batch Identifier for the batch being Imported
182 * @param x_Mesg_Token_Tbl Error handler Message Token
183 * @param x_Return_Status Return code holding return status
184 * @rep:scope private
185 * @rep:lifecycle active
186 * @rep:displayname Resolve Cross References
187 */
188
189 PROCEDURE RESOLVE_XREFS_FOR_BATCH
190 (
191 p_batch_id IN NUMBER
192 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
193 , x_Return_Status IN OUT NOCOPY VARCHAR2
194 );
195
196 /*#
197 * This procedure will update the Bom Structure and Components
198 * Interface tables with the PDH matched data fro Ego_Item_Matches table
199 * This API will will update the Matched PDH Data
200 * for record in a batch which have matching entries
201 * @param p_batch_id Batch Identifier for the batch being Imported
202 * @param p_source_system_id Source System Identifier for the Batch
203 * @param x_Mesg_Token_Tbl Error handler Message Token
204 * @param x_Return_Status Return code holding return status
205 * @rep:scope private
206 * @rep:lifecycle active
207 * @rep:displayname Update Match Data
208 */
209 PROCEDURE UPDATE_MATCH_DATA
210 (
211 p_batch_id IN NUMBER
212 , p_source_system_id IN NUMBER
213 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
214 , x_Return_Status IN OUT NOCOPY VARCHAR2
215 );
216
217
218 /*#
219 * This procedure will Update the Bom Structure and Components Interface
220 * tables with Bill Sequence Id,Component Sequence Id and Transaction Type
221 * Info based on the matched or cross referenced data that the record in the
222 * Interface table will have.If the Header interface table record has a valid
223 * Structure Header information in PDH then the Bill Squence Id for that existing
224 * Structure will be populated in the Header Interface table record.The transaction
225 * type will be changed to UPDATE only if the user entered value is 'SYNC'.If the
226 * target bill doesnt exist then the transaction type will be updated to CREATE.
227
228
229 * Also for all the component records for each Header this API will update the
230 * Component Sequence Id,Bill Sequence Id and the Transaction Type if the Component
231
232 * specified by data in the interface table record exist in the target Structure.
233 * If the Target Structure has any component that doesnt match with any of the
234 * source Components then that will be entered into the Interface table with a
235 * transaction type of Delete.
236 * @param p_batch_id Batch Identifier for the batch being Imported
237 * @param x_Mesg_Token_Tbl Error handler Message Token
238 * @param x_Return_Status Return code holding return status
239 * @rep:scope private
240 * @rep:lifecycle active
241 * @rep:displayname Update Bill Info
242 */
243 PROCEDURE UPDATE_BILL_INFO
244 (
245 p_batch_id IN NUMBER
246 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
247 , x_Return_Status IN OUT NOCOPY VARCHAR2
248 );
249
250 /**
251 * This procedure is used by the EGO team to notify that
252 * matching of all the uploaded records is over and
253 * further processing can be continued.
254 * @param p_batch_id Batch Identifier
255 * @param p_init_msg_list Message List Initializer Flag
256 * @param x_return_status Return Status of the API
257 * @param x_Error_Mesg Error Message
258 * @param p_debug Debug Flag
259 * @param p_output_dir Output Directory
260 * @param p_debug_filename Debug File Name
261 * @rep:scope private
262 * @rep:lifecycle active
263 * @rep:displayname Matching Complete
264 */
265 PROCEDURE Matching_Complete
266 (
267 p_batch_id IN NUMBER
268 , p_init_msg_list IN VARCHAR2
272 , p_output_dir IN VARCHAR2
269 , x_return_status IN OUT NOCOPY VARCHAR2
270 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
271 , p_debug IN VARCHAR2
273 , p_debug_filename IN VARCHAR2
274 );
275
276 /**
277 * This procedure is used by the EGO team to notify that
278 * matching of all the uploaded records is over and
279 * further processing can be continued.
280 * @param p_batch_id Batch Identifier
281 * @param x_return_status Return Status of the API
282 * @param x_Error_Mesg Error Message
283 * @rep:scope public
284 * @rep:lifecycle active
285 * @rep:displayname Matching Complete
286 */
287 PROCEDURE Matching_Complete
288 (
289 p_batch_id IN NUMBER
290 , x_return_status IN OUT NOCOPY VARCHAR2
291 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
292 );
293
294 /**
295 * This is the Function for getting the attribute difference.
296 * This returns the User Attribute Values for both Source System
297 * item and Pdh Item.This function will retrieve the values for
298 * both Component Base and Component Extended Attributes.
299 * If any of source item or pdh item is null it returns null as
300 * attribute values for that item.
301 * @param p_batch_id Batch Identifier for the Batch Imported
302 * @param p_ss_record_id Source System Reference for the Source System Item/Component
303 * @param p_comp_seq_id Component Sequence Id for the Pdh Component if any.
304 * @param p_str_type_id Structure Type Id from the Batch Options.
305 * @param p_effec_date Effectivity Date for the component
306 * @param p_op_seq_num Operation Sequence Number for the Component
307 * @param p_item_id Component Item Id
308 * @param p_org_id Organization Id
309 * @rep:scope private
310 * @rep:lifecycle active
311 * @rep:displayname Get Component Attributes Difference
312 */
313
314 FUNCTION BOM_GET_COMP_ATTR_DATA
315 (
316 p_batch_id NUMBER,
317 p_ss_record_id VARCHAR2,
318 p_comp_seq_id NUMBER,
319 p_str_type_id NUMBER,
320 p_effec_date DATE,
321 p_op_seq_num NUMBER,
322 p_item_id NUMBER,
323 p_org_id NUMBER,
324 p_intf_uniq_id NUMBER
325 ) RETURN Bom_Attr_Diff_Table_Type;
326
327
328 FUNCTION get_ref_desgs
329 (
330 p_batch_id IN NUMBER
331 , p_comp_rec_id IN VARCHAR2
332 , p_comp_seq_id IN NUMBER
333 , p_mode IN NUMBER
334 , p_effec_date IN DATE
335 , p_op_seq_num IN NUMBER
336 , p_item_id IN NUMBER
337 , p_org_id IN NUMBER
338 )RETURN VARCHAR2;
339
340
341 /**
342 * This procedure does the Value updation for User Attributes.
343 * Based on the process_status of the component this API will
344 * update the corresponding User Attribute Rows with the component
345 * sequence ids and Bill Sequence Ids and set process_flag depending on that
346 * of Component.After setting the values it calls the Ext API for User
347 * Attribute Bulk Load.
348 * @param p_batch_id Batch Identifier for the Batch Imported
349 * @param p_transaction_id Transaction Id for the Component
350 * @param p_comp_seq_id Component Sequence Id of the Component
351 * @param p_bill_seq_id Bill Sequence Id for the Header
352 * @param p_call_Ext_Api Flag to check whether to call the Ext API
353 * @param x_Mesg_Token_Tbl Error Handlers Message Token Table
354 * @param x_Return_Status Return Status after processing
355 * @rep:scope private
356 * @rep:lifecycle active
357 * @rep:displayname Update User Attribute Data
358
359 */
360 PROCEDURE Update_User_Attr_Data
361 (
362 p_batch_id IN NUMBER
363 , p_transaction_id IN NUMBER
364 , p_comp_seq_id IN NUMBER
365 , p_bill_seq_id IN NUMBER
366 , p_call_Ext_Api IN VARCHAR2
367 , p_parent_id IN NUMBER
368 , p_org_id IN NUMBER
369 , x_Return_Status IN OUT NOCOPY VARCHAR2
370 , x_Error_Text IN OUT NOCOPY VARCHAR2
371 );
372
373 /**
374 * This procedure is the starting point for the existing open interface
375 * tables being used to create batches.
376 * Users will call this API once the data load for a batch is done in the
377 * bom interface tables.
378 * @param p_batch_id Batch Identifier for the batch being Imported
379 * @param x_error_message Error Message
380 * @param x_return_code Return code holding return status
381 * @rep:scope public
382 * @rep:lifecycle active
383 * @rep:displayname Data Upload Complete
384 */
385
386 PROCEDURE DATA_UPLOAD_COMPLETE
387 (
388 p_batch_id IN NUMBER
389 , x_error_message OUT NOCOPY VARCHAR2
390 , x_return_code OUT NOCOPY VARCHAR2
391
392 );
393
394 /**
395 *This procedure is the starting point for the existing open interface
399 * @param p_init_msg_list Init Message List Flag
396 *tables being used to create batches.Users will call this API once the data load for a batch is
397 *done in the bom interface tables.
398 * @param p_batch_id Batch Identifier for the batch being Imported
400 * @param x_error_message Error Message
401 * @param x_return_code Return code holding return status
402 * @param p_output_dir Out Put directory for logging
403 * @param p_debug_filename Debug File name
404 * @rep:scope public
405 * @rep:lifecycle active
406 * @rep:displayname Data Upload Complete
407 */
408 PROCEDURE DATA_UPLOAD_COMPLETE
409 (
410 p_batch_id IN NUMBER
411 , p_init_msg_list IN VARCHAR2
412 , x_return_status IN OUT NOCOPY VARCHAR2
413 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
414 , p_debug IN VARCHAR2
415 , p_output_dir IN VARCHAR2
416 , p_debug_filename IN VARCHAR2
417 );
418
419
420
421 /**
422 * This procedure is called for importing the structure entities for a batch
423 * This API will import data from interface tables and update the
424 * production tables. This API will launch BOM Java Concurrent Program.
425 * @param p_batch_id Batch Identifier for the batch being Imported
426 * @param x_error_message Error Message
427 * @param x_return_code Return code holding return status
428 * @rep:scope private
429 * @rep:lifecycle active
430 * @rep:displayname Import Structure Data
431 */
432
433
434 PROCEDURE IMPORT_STRUCTURE_DATA
435 (
436 p_batch_id IN NUMBER
437 , p_items_import_complete IN VARCHAR2
438 , p_callFromJCP IN VARCHAR2
439 , p_request_id IN NUMBER
440 , x_error_message OUT NOCOPY VARCHAR2
441 , x_return_code OUT NOCOPY VARCHAR2
442 );
443
444
445
446 /**
447 * This procedure is called for pre-processing the rows prior to
448 * calling the BOM Java Concurrent Program.
449 * This API will set the row status to a process_flag value of 5
450 * based on batch options for change management to process
451 * The pre-process will resolve all the source system to id
452 * conversion for batch records.
453 * @param p_batch_id Batch Identifier for the batch being Imported
454 * @param x_error_message Error Message
455 * @param x_return_code Return code holding return status
456 * @rep:scope private
457 * @rep:lifecycle active
458 * @rep:displayname Pre-Process Import Rows
459
460 */
461
462 PROCEDURE PRE_PROCESS_IMPORT_ROWS
463 (
464 p_batch_id IN NUMBER
465 , p_items_import_complete IN VARCHAR2
466 , x_error_message OUT NOCOPY VARCHAR2
467 , x_return_code OUT NOCOPY VARCHAR2
468 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
469 );
470
471 /**
472 * This procedure propagates the confirmation status to various structure
473
474 * entities based on item interface tables confirmation status.
475 * The match_status could be confirmed, unconfirmed or excluded
476 * The transaction boundary for a structure is considered to be
477 * structure, first level of its components and the child entities for
478 * that level of components. Unconfirmed rows for any component result
479 * in not processing the whole structure. However exclusion of a component
480 * will result in processing
481 * @param p_batch_id Batch Identifier for the batch being Imported
482 * @param x_error_message Error Message
483 * @param x_return_code Return code holding return status
484 * @rep:scope private
485 * @rep:lifecycle active
486 * @rep:displayname Pre-Process Import Rows
487
488 */
489
490 PROCEDURE PROPAGATE_CONFIRMATION_STATUS
491 (
492 p_batch_id IN NUMBER
493 , x_error_message OUT NOCOPY VARCHAR2
494 , x_return_code OUT NOCOPY VARCHAR2
495 );
496
497 /**
498 * Concurrent Program Replacement for BMCOIN
499 * @param p_batch_id Batch Identifier for the batch being Imported
500 * @param x_error_message Error Message
501 * @param x_return_code Return Code Success or Error
502 * @p_organization_id Organization Id for which the import program will run
503 * @param p_all_organization All Organizations Option
504 * @param p_import_routings Import Routings Option
505 * @param p_import_bills Import Bills Option
506 * @param p_delete_rows Delete the Processed Rows from Interface Tables Option
507 * @param p_batch_id Batch Identifier for processing a set of records
508 * @rep:lifecycle active
509 * @rep:displayname Pre-Process Import Rows
510 */
511 PROCEDURE Import_Interface_Rows
512 (
513 x_err_buffer OUT NOCOPY VARCHAR2,
514 x_return_code OUT NOCOPY VARCHAR2,
515 p_organization_id IN NUMBER,
516 p_all_organization IN VARCHAR2,
517 p_import_routings IN VARCHAR2,
518 p_import_bills IN VARCHAR2,
519 p_delete_rows IN VARCHAR2,
520 p_batch_id IN NUMBER
521 );
522
523 /**
524 * This is the procedure for updating the Bill with item names
525 * for a Pdh Batch Import.If it is a Pdh Batch Import this
526 * API will be called and this API will do the id to val
527 * conversion if needed.This will also populate the
528 * source_system_reference with the Item Names or Component
529 * names.This is for the Structure Import UI to show the
530 * details of the batch even for a Pdh Batch Import which will
531 * not have any source_system_reference.
532
533 * @param p_batch_id Batch Identifier for the Pdh Batch
534 * @rep:scope private
538 PROCEDURE Update_Bill_Val_Id
535 * @rep:lifecycle active
536 * @rep:displayname Update Bill for Pdh Import
537 */
539 (
540 p_batch_id IN NUMBER
541 , x_return_status IN OUT NOCOPY VARCHAR2
542 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
543 );
544
545 /*
546 * The following functions are used for getting the
547 * values of FND_API.G_MISS_NUM,FND_API.G_MISS_CHAR and
548 * FND_API.G_MISS_DATE in the SQL query used in
549 * Bom Attributes Diff VO Query
550 */
551 FUNCTION get_G_MISS_NUM RETURN NUMBER;
552
553 FUNCTION get_G_MISS_CHAR RETURN VARCHAR;
554
555
556 FUNCTION get_G_MISS_DATE RETURN DATE;
557
558 /**
559 * This is the procedure for updating the BOM interface
560 * tables with the newly confirmed pdh item ids.This API will
561 * be called by EGO when the user confirms any source item in a
562 * batch and when they propagate the confirmed pdh item ids to
563 * subsequent tables.
564 * Do We Need This For BOM if the newly confirmed source item
565 * is a header item then we need to update Header Interface table with
566 * the bill sequence id of the structure of the newly confirmed pdh item
567 * We also need to update Bom Components Interface table with this
568 * new bill sequence id for all the components of the confirmed source item.
569
570 * If the newly confirmed source item is a component then we need to check whe
571 ther
572
573 * the corresponding pdh item exist as a component in the target bill,if not t
574 hen add it
575
576 * as a new component to the target pdh strcuture.
577 * @param p_batch_id Batch Identifier for the Imported Batch
578 * @param p_ssRef_varray Source System Refence Array for all the source items
579 confirmed
580
581 * @param p_item_id_varray Item Id array containing the Ids of the confirmed P
582 DH items
583
584
585 * @param x_error_message Error Message
586 * @param x_return_code Return code holding return status
587 * @rep:scope private
588 * @rep:lifecycle active
589 * @rep:displayname Update Confirmed Items
590 */
591
592 PROCEDURE Update_Confirmed_Items
593 (
594 p_batch_id IN NUMBER
595 , p_ssRef_varray IN VARCHAR2_VARRAY
596 , x_Error_Message IN OUT NOCOPY VARCHAR2
597 , x_Return_Status IN OUT NOCOPY VARCHAR2
598 );
599
600
601 /****************** Local Procedures Section Ends ******************/
602 /*
603 * The Method that willl be invoked by JCP
604 */
605
606 PROCEDURE Process_Structure_Data
607 (
608 p_batch_id IN NUMBER
609 );
610
611 /*
612 * The Method will return the Primay display name is the internal
613 * name is *NULL* - used in the UI
614 * @param p_struct_Internal_Name Structure Internal Name
615 * @param Returns Structure Name
616 * @rep:scope private
617 * @rep:lifecycle active
618 * @rep:displayname Get Primary Structure name
619 */
620 FUNCTION Get_Primary_StructureName
621 (p_struct_Internal_Name IN VARCHAR2)
622 RETURN VARCHAR2;
623
624 PROCEDURE Check_Change_Options
625 (
626 p_batch_id IN NUMBER,
627 x_error_code IN OUT NOCOPY VARCHAR2,
628 x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
629 );
630
631 PROCEDURE PROCESS_ALL_COMPS_BATCH
632 (
633 p_batch_id IN NUMBER
634 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
635 , x_Return_Status IN OUT NOCOPY VARCHAR2
636 );
637
638 /*
639 * The Method will return the BATCHID sequence to be used for OI Process
640 * @param Returns Batch ID
641 * @rep:scope public
642 * @rep:lifecycle active
643 * @rep:displayname Get Batch ID
644 */
645 FUNCTION Get_BatchId
646 RETURN NUMBER;
647
648 /*
649 * This API will delete all the records from all the
650 * BOM interface tables for the given batch id.
651 * @param p_batch_id Batch Id for which data is to be deleted
652 * @param x_error_mesg Error Message
653 * @param x_ret_code Return Code
654 * @rep:scope private
655 * @rep:lifecycle active
656 * @rep:displayname Delete Interface Records
657 */
658
659 PROCEDURE Delete_Interface_Records
660 (
661 p_batch_id IN NUMBER
662 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
663 , x_Ret_Code IN OUT NOCOPY VARCHAR2
664 );
665
666 /**
667 * Procedure to merge the duplicate records starting with the
668 * components and then propagating to the child entities.
669 * @param p_batch_id Batch Id for which data is to be deleted
670 * @param x_Ret_Status Return Code
671 * @param x_Error_Mesg Error Message
672 * @rep:scope private
673 * @rep:lifecycle active
674 * @rep:displayname Merge Duplicate Rows
675 */
676 PROCEDURE Merge_Duplicate_Rows
677 (
678 p_batch_id IN NUMBER,
679 x_Ret_Status IN OUT NOCOPY VARCHAR2,
680 x_Error_Mesg IN OUT NOCOPY VARCHAR2
681 );
682 /*
683 * Procedure to merge Reference Designators
684 * @param p_batch_id Batch Id for which data is to be deleted
685 * @p_comp_seq_id Component Sequence Id
686 * @p_comp_name Component Name
687 * @p_comp_ref Component Source System Reference
688 * @p_effec_date Effectivity Date for the component
689 * @p_op_seq Operation Sequence Number for the component
690 * @p_new_effec_date New Effectivity Date for a changed component
691 * @p_new_op_seq New Operation Sequence Number
692 * @p_from_unit From end item unit number
696 * @x_Ret_Status Return Status
693 * @p_from_item_id From end item revision id
694 * @p_parent_name Parent Name
695 * @p_parent_ref Parent Source System Reference
697 * @x_Error_Mesg Error Message
698 * @rep:scope private
699 * @rep:lifecycle active
700 * @rep:displayname Merge Reference Designators
701 */
702
703 PROCEDURE Merge_Ref_Desgs
704 (
705 p_batch_id IN NUMBER,
706 p_comp_seq_id IN NUMBER,
707 p_comp_name IN VARCHAR2,
708 p_comp_ref IN VARCHAR2,
709 p_effec_date IN DATE,
710 p_op_seq IN NUMBER,
711 p_new_effec_date IN DATE,
712 p_new_op_seq IN NUMBER,
713 p_from_unit IN VARCHAR2,
714 p_from_item_id IN NUMBER,
715 p_parent_name IN VARCHAR2,
716 p_parent_ref IN VARCHAR2,
717 x_Ret_Status IN OUT NOCOPY VARCHAR2,
718 x_Error_Mesg IN OUT NOCOPY VARCHAR2
719 );
720
721 /*
722 * Procedure to merge user attributes duplicate rows
723 * @p_batch_id Batch Id
724 * @p_comp_seq Component Sequence Id
725 * @p_comp_name Component Name
726 * @p_comp_ref Component Source System Reference
727 * @p_txn_id Transaction Id
728 * @x_Ret_Status Return Status
729 * @x_Error_Mesg Error Message
730 * @rep:scope private
731 * @rep:lifecycle active
732 * @rep:displayname Merge Component Attributes
733 */
734 PROCEDURE Merge_User_Attrs
735 (
736 p_batch_id IN NUMBER,
737 p_comp_seq IN NUMBER,
738 p_comp_name IN VARCHAR2,
739 p_comp_ref IN VARCHAR2,
740 p_txn_id IN NUMBER,
741 p_par_name IN VARCHAR2,
742 p_par_ref IN VARCHAR2,
743 p_org_id IN NUMBER,
744 p_org_code IN VARCHAR2,
745 x_Ret_Status IN OUT NOCOPY VARCHAR2,
746 x_Error_Mesg IN OUT NOCOPY VARCHAR2
747 );
748 /*
749 * Procedure to update the interface records
750 * with process_flag = 5 when change options are
751 * set in the batch or there are change policy for the
752 * structure
753 * @param p_batch_id Batch Identifier
754 * @rep:scope private
755 * @rep:lifecycle active
756 * @rep:displayname Porcess CM Options
757 */
758 PROCEDURE Process_CM_Options(p_batch_id IN NUMBER);
759
760 PROCEDURE Get_Item_Security_Predicate
761 (
762 p_object_name IN VARCHAR2,
763 p_party_id IN VARCHAR2,
764 p_privilege_name IN VARCHAR2,
765 p_table_alias IN VARCHAR2,
766 x_security_predicate OUT NOCOPY VARCHAR2
767 );
768 FUNCTION Get_Item_Matches
769 (
770 p_batch_id IN NUMBER,
771 p_ss_ref IN VARCHAR2
772 )
773 RETURN VARCHAR2;
774
775 END Bom_Import_Pub; -- Package spec
[Home] [Help]