eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.EGO_BOM_BULKLOAD_PVT_PKG

Source


1 PACKAGE BODY EGO_BOM_BULKLOAD_PVT_PKG AS
2 /* $Header: BOMBBLPB.pls 115.12 2004/04/30 10:43:22 hgelli noship $ */
3 
4 -- =================================================================
5 -- Global variables used in the package.
6 -- =================================================================
7 
8   G_USER_ID         NUMBER  :=  -1;
9   G_LOGIN_ID        NUMBER  :=  -1;
10   G_PROG_APPID      NUMBER  :=  -1;
11   G_PROG_ID         NUMBER  :=  -1;
12   G_REQUEST_ID      NUMBER  :=  -1;
13   G_DEBUG           NUMBER  :=   1;
14 
15   G_STATUS_SUCCESS    CONSTANT VARCHAR2(1)    := 'S';
16   G_STATUS_ERROR      CONSTANT VARCHAR2(1)    := 'E';
17 
18   --This is the UI language.
19   G_LANGUAGE_CODE          VARCHAR2(3);
20   G_CONCREQ_VALID_FLAG     BOOLEAN := FALSE;
21 
22   G_ERROR_TABLE_NAME      VARCHAR2(99) := 'BOM_BULKLOAD_INTF';
23   G_ERROR_ENTITY_CODE     VARCHAR2(99) := 'EGO_ITEM';
24   G_ERROR_FILE_NAME       VARCHAR2(99);
25   G_BO_IDENTIFIER         VARCHAR2(99) := 'EGO_ITEM';
26 
27 
28 PROCEDURE open_debug_session IS
29 
30   CURSOR c_get_utl_file_dir IS
31      SELECT VALUE
32       FROM V$PARAMETER
33      WHERE NAME = 'utl_file_dir';
34 
35   --local variables
36   l_log_output_dir       VARCHAR2(200);
37   l_log_return_status    VARCHAR2(99);
38   l_errbuff              VARCHAR2(999);
39 BEGIN
40 
41   OPEN c_get_utl_file_dir;
42   FETCH c_get_utl_file_dir INTO l_log_output_dir;
43   --developer_debug('UTL_FILE_DIR : '||l_log_output_dir);
44   IF c_get_utl_file_dir%FOUND THEN
45     ------------------------------------------------------
46     -- Trim to get only the first directory in the list --
47     ------------------------------------------------------
48     IF INSTR(l_log_output_dir,',') <> 0 THEN
49       l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
50     END IF;
51 
52     G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'_'||TO_CHAR(SYSDATE, 'DDMONYYYY_HH24MISS')||'.err';
53 
54     Error_Handler.Open_Debug_Session(
55       p_debug_filename   => G_ERROR_FILE_NAME
56      ,p_output_dir       => l_log_output_dir
57      ,x_return_status    => l_log_return_status
58      ,x_error_mesg       => l_errbuff
59      );
60 
61     FND_FILE.put_line(FND_FILE.LOG, ' Log file location --> '||l_log_output_dir||'/'||G_ERROR_FILE_NAME ||' created with status '|| l_log_return_status);
62 
63     IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
64        FND_FILE.put_line(FND_FILE.LOG, 'Unable to open error log file. Error => '||l_errbuff);
65     END IF;
66 
67   END IF;--IF c_get_utl_file_dir%FOUND THEN
68 
69 END open_debug_session;
70 
71 FUNCTION Bill_Sequence( p_assembly_item_id IN NUMBER
72            , p_alternate_bom_designator IN VARCHAR2
73            , p_organization_id  IN NUMBER
74       )
75   RETURN NUMBER  IS
76   l_id       NUMBER;
77 BEGIN
78 
79   SELECT Bill_Sequence_Id
80     INTO l_id
81    FROM Bom_Bill_Of_Materials
82   WHERE Assembly_Item_Id = p_assembly_item_id
83     AND NVL(Alternate_Bom_Designator, 'NONE') =
84              DECODE(p_alternate_bom_designator,NULL,'NONE',p_alternate_bom_designator)
85       AND Organization_Id = p_organization_id;
86 
87   RETURN l_id;
88 
89   EXCEPTION
90   WHEN OTHERS THEN
91     RETURN NULL;
92 END Bill_Sequence;
93 
94 /********************************************************************
95 * Function      : Organization
96 * Returns       : NUMBER
97 * Purpose       : Will convert the value of organization_code to
98 *     organization_id using MTL_PARAMETERS.
99 *                 If the conversion fails then the function will return
100 *     a NULL otherwise will return the org_id.
101 *     For an unexpected error function will return a
102 *     missing value.
103 *********************************************************************/
104 FUNCTION ORGANIZATION
105    ( p_organization IN VARCHAR2) RETURN NUMBER
106 IS
107   l_id                          NUMBER;
108 BEGIN
109   SELECT  Organization_Id
110   INTO    l_id
111   FROM    Mtl_Parameters
112   WHERE   Organization_Code = p_organization;
113 
114   RETURN l_id;
115 
116   EXCEPTION
117     WHEN NO_DATA_FOUND THEN
118       RETURN NULL;
119     WHEN OTHERS THEN
120       RETURN FND_API.G_MISS_NUM;
121 END ORGANIZATION;
122 
123 
124 /*******************************************************************
125 * Function  : Component_Item
126 * Parameters IN : Component Item Name
127 *     Organization ID
128 * Parameters OUT: Error Message
129 * Returns : Component_Item_Id
130 * Purpose : Function will convert the component item name to its
131 *     corresponsind ID and return the value.
132 *     If the component is invalid, then a NULL is returned.
133 *********************************************************************/
134 FUNCTION Component_Item( p_organization_id   IN NUMBER
135       , p_component_item_num IN VARCHAR2)
136 RETURN NUMBER
137 IS
138   l_id        NUMBER;
139   ret_code    NUMBER;
140   l_err_text  VARCHAR2(2000);
141 BEGIN
142   ret_code := INVPUOPI.Mtl_Pr_Parse_Flex_Name(
143                 Org_Id => p_organization_id,
144                 Flex_Code => 'MSTK',
145                 Flex_Name => p_component_item_num,
146                 Flex_Id => l_id,
147                 Set_Id => -1,
148                 Err_Text => l_err_text);
149 
150   IF (ret_code <> 0) THEN
151     RETURN NULL;
152   END IF;
153 
154   RETURN l_id;
155 
156 END Component_Item;
157 
158 
159 ------------------------------------------------------------------------------------
160 PROCEDURE Structure_Intf_Proc_Complete
161   (
162     p_resultfmt_usage_id     IN    NUMBER
163   , x_errbuff                OUT   NOCOPY VARCHAR2
164   , x_retcode                OUT   NOCOPY VARCHAR2
165     ) IS
166 
167 BEGIN
168 
169 -- Update process flag in Ego Bulkload interface table
170   UPDATE EGO_BULKLOAD_INTF EBI
171    SET  EBI.PROCESS_STATUS =
172     (
173       SELECT BMI.PROCESS_FLAG
174       FROM   BOM_BILL_OF_MTLS_INTERFACE BMI
175       WHERE  BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
176     )
177   WHERE EXISTS
178     (
179       SELECT 'X'
180       FROM   BOM_BILL_OF_MTLS_INTERFACE BMI
181       WHERE  BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
182     )
183     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
184 
185 
186   UPDATE EGO_BULKLOAD_INTF EBI
187    SET  EBI.PROCESS_STATUS =
188     (
189       SELECT BICI.PROCESS_FLAG
190       FROM   BOM_INVENTORY_COMPS_INTERFACE BICI
191       WHERE  BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
192     )
193   WHERE EXISTS
194     (
195       SELECT 'X'
196       FROM   BOM_INVENTORY_COMPS_INTERFACE BICI
197       WHERE  BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
198     )
199     AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
200 
201 
202 -- Commiting after the process flag is updated.
203    COMMIT;
204 
205   --Error_Handler.Write_Debug('EBI: Updated the Process_Status to Indicate Succssful/Unsucessful completion.');
206   x_retcode := G_STATUS_SUCCESS;
207 
208 EXCEPTION
209   WHEN OTHERS THEN
210     x_retcode := G_STATUS_ERROR;
211     x_errbuff := SUBSTRB(SQLERRM, 1,240);
212     RAISE;
213 END Structure_Intf_Proc_Complete;
214 
215 PROCEDURE PROCESS_BOM_INTERFACE_LINES
216                (
217                  p_resultfmt_usage_id    IN         NUMBER,
218                  p_user_id               IN         NUMBER,
219                  p_conc_request_id       IN         NUMBER,
220                  p_language_code         IN         VARCHAR2,
221                  x_errbuff               IN OUT NOCOPY VARCHAR2,
222                  x_retcode               IN OUT NOCOPY VARCHAR2
223                 )
224 IS
225 
226 --Type Declarations
227   TYPE VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(256)
228    INDEX BY BINARY_INTEGER;
229 
230 
231 --BOM RECORDS....
232   l_return_status       VARCHAR2(1);
233   l_msg_count           NUMBER := 0;
234   l_unexp_error         VARCHAR2(1000);
235   tempVar               NUMBER;
236   m                     NUMBER := 0; --counter for revised components
237   l_commonitem          VARCHAR2(81);
238   error_message         VARCHAR2(2000) := '';
239 
240 
241   l_return_code         NUMBER;
242   l_err_text            VARCHAR2(2000);
243   l_err_return_code     INTEGER;
244 
245   --API return parameters
246   l_retcode             VARCHAR2(10);
247   l_errbuff             VARCHAR2(2000);
248 
249 --Dynamic Cursor Parameters
250   l_dyn_sql             VARCHAR2(10000);
251   l_dyn_sql_select      VARCHAR2(10000);
252   l_dyn_sql_insert      VARCHAR2(10000);
253   l_dyn_sql_cursor      VARCHAR2(10000);
254   l_msii_set_process_id NUMBER;
255   l_cursor_select       INTEGER;
256   l_cursor_execute      INTEGER;
257   l_temp                NUMBER(10) := 1;
258 
259 --Column Mapping Tables;
260   l_prod_col_name_tbl   VARCHAR_TBL_TYPE;
261   l_intf_col_name_tbl   VARCHAR_TBL_TYPE;
262   i                     NUMBER := 0;
263   j                     NUMBER := 0;
264   k                     NUMBER := 0;
265 
266 --BOM Interface Table Mappings
267   l_bom_col_name        VARCHAR_TBL_TYPE;
268   l_bom_tbl_name        VARCHAR_TBL_TYPE;
269   l_bom_col_type        VARCHAR_TBL_TYPE;
270 
271 --Column Mappings
272   l_prod_col_name       VARCHAR2(256);
273   l_intf_col_name       VARCHAR2(256);
274   l_parent_column       VARCHAR2(256);
275   l_item_col_name       VARCHAR2(256);
276   l_org_id_column       VARCHAR2(256);
277   l_altbom_column       VARCHAR2(256);
278   l_comp_seq_col_name   VARCHAR2(256);
279 
280 --Txn Types
281 
282   G_TXN_CREATE          VARCHAR2(10) := 'CREATE';
283   G_TXN_ADD             VARCHAR2(10) := 'ADD';
284   G_TXN_UPDATE          VARCHAR2(10) := 'UPDATE';
285   G_TXN_DELETE          VARCHAR2(10) := 'DELETE';
286   G_TXN_SYNC            VARCHAR2(10) := 'SYNC';
287 
288 -- COLUMN NAMES
289   G_ITEM_NAME           VARCHAR2(30) := 'ITEM_NUMBER';
290   G_ORG_CODE            VARCHAR2(30) := 'ORGANIZATION_CODE';
291   G_ALT_BOM             VARCHAR2(30) := 'ALTERNATE_BOM_DESIGNATOR';
292   G_PARENT_NAME         VARCHAR2(30) := 'PARENT_NAME';
293   G_QUANTITY            VARCHAR2(30) := 'QUANTITY';
294   G_COMPONENT_SEQ_ID    VARCHAR2(30) := 'COMPONENT_SEQUENCE_ID';
295 
296 
297 -- Bom Interface column names
298   G_EFFECTIVITY_DATE    VARCHAR2(30) := 'EFFECTIVITY_DATE';
299   G_OPERATION_SEQ_NUM   VARCHAR2(30) := 'OPERATION_SEQ_NUM';
300   G_FROM_END_ITEM_UNIT_NUMBER   VARCHAR2(30) := 'FROM_END_ITEM_UNIT_NUMBER';
301 
302 --Column Values
303   L_ITEM_NAME           VARCHAR2(240) ;
304   L_ORGANIZATION_CODE   VARCHAR2(3) ;
305   L_STRUCTURE_NAME      VARCHAR2(10) ;
306   L_PARENT_NAME         VARCHAR2(240) ;
307   L_QUANTITY            NUMBER := 2;
308   L_TRANSACTION_ID      NUMBER;
309 
310 -- Interface COLUMN NAMES
311   G_INTF_STRUCT_NAME    VARCHAR2(30) := 'C_FIX_COLUMN3';
312   G_INTF_ORG_CODE       VARCHAR2(30) := 'C_INTF_ATTR1';
313   G_INTF_COMP_SEQ_ID    VARCHAR2(30) := 'N_INTF_ATTR1';
314 
315 -- Temparory Variables
316   l_Org_Id              NUMBER;
317   l_Inv_Item_Id         NUMBER;
318   l_Bill_Seq_Id         NUMBER;
319   l_str                 VARCHAR2(1000);
320 
321 -- Constant Values
322   G_DEL_GROUP_NAME    VARCHAR2(10)  := 'B_BLK_INTF';
323   G_DEL_GROUP_DESC    VARCHAR2(240) := 'Delete Group for EGO BOM Bulkload Structures';
324 
325   l_bom_header_columns_tbl   DBMS_SQL.VARCHAR2_TABLE;
326 
327 
328 --DEBUG FLAG
329   l_debug       VARCHAR2(10);
330   --
331   --  Get the Header Data from the Parents.
332   --
333 
334 
335   --
336   -- To get the BOM Attribute columns in the Result Format.
337   --
338 
339   CURSOR C_BOM_ATTRIBUTE_COLUMNS (c_Resultfmt_Usage_Id  IN  NUMBER) IS
340     SELECT
341       erf.Attribute_Code,
342       erf.Intf_Column_Name,
343       bcc.Bom_Intf_Column_Name ,
344       bcc.Bom_Intf_Table_Name,
345       bcc.Operation_Type
346     FROM
347       Ego_Results_Fmt_Usages erf,
348       Bom_Component_Columns bcc
349     WHERE
350       Region_Code = 'BOM_RESULT_DUMMY_REGION'
351     AND
352       Region_Application_Id = 702
353     AND
354       Customization_Application_Id = 431
355     AND
356       Resultfmt_Usage_Id = c_Resultfmt_Usage_Id
357     AND
358       bcc.Attribute_Code = erf.Attribute_Code
359     AND
360       (   bcc.Parent_Entity IS NULL
361       OR  BCC.Attribute_Code = 'ITEM_NUMBER'
362       OR  BCC.Attribute_Code = 'PARENT_NAME')
363     AND
364       erf.Attribute_Code NOT LIKE '%$$%';
365 
366   -- Cursor to create a row with 'Primary' (Null) alternate for structure headers
367   -- This is to automate the creation of primary boms for PLM purpose
368   CURSOR C_BOM_BILL_PRIMARY(C_REQUEST_ID IN NUMBER) IS
369     SELECT
370       ASSEMBLY_ITEM_ID,
371       ORGANIZATION_ID,
372       ASSEMBLY_TYPE,
373       PROCESS_FLAG,
374       ORGANIZATION_CODE,
375       COMMON_ORG_CODE,
376       ITEM_NUMBER,
377       IMPLEMENTATION_DATE
378     FROM
379       BOM_BILL_OF_MTLS_INTERFACE
380     WHERE
381       PROCESS_FLAG = 1
382     AND
383       REQUEST_ID = C_REQUEST_ID;
384 
385 
386 BEGIN
387     l_debug := fnd_profile.value('MRP_DEBUG');
388 
389     IF (NVL(fnd_profile.value('CONC_REQUEST_ID'), 0) <> 0) THEN
390       G_CONCREQ_VALID_FLAG  := TRUE;
391     END IF;
392 
393     IF (G_CONCREQ_VALID_FLAG ) THEN
394       FND_FILE.put_line(FND_FILE.LOG, ' ******** New Log ******** ');
395     END IF;
396 
397    -- the values are chosen from the FND_GLOBALS
398     G_USER_ID    := FND_GLOBAL.user_id         ;
399     G_LOGIN_ID   := FND_GLOBAL.login_id        ;
400     G_PROG_APPID := FND_GLOBAL.prog_appl_id    ;
401     G_PROG_ID    := FND_GLOBAL.conc_program_id ;
402     G_REQUEST_ID := FND_GLOBAL.conc_request_id ;
403     G_LANGUAGE_CODE := p_Language_Code;
404 
405 
406 
407     Error_Handler.initialize();
408     Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
409     --Log errors into file
410     --Error_Handler.Set_Debug('Y');
411     --Opens Error_Handler debug session
412     --Open_Debug_Session;
413 
414     --After Open_Debug_Session, can log using Error_Handler.Write_Debug()
415     --Replace this with FND_FILE.put_line() if needed.
416     --Error_Handler.Write_Debug('G_USER_ID : '||TO_CHAR(G_USER_ID));
417     --Error_Handler.Write_Debug('G_PROG_ID : '||TO_CHAR(G_PROG_ID));
418     --Error_Handler.Write_Debug('G_REQUEST_ID : '||TO_CHAR(G_REQUEST_ID));
419     --Error_Handler.Write_Debug('P_RESULT_FMT_USAGE_ID : '||TO_CHAR(p_Resultfmt_Usage_Id));
420 
421 
422 --  Delete all the earlier uploads from the same spreadsheet.
423    /*
424     DELETE FROM  EGO_BULKLOAD_INTF
425     WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
426     AND PROCESS_STATUS <> 1;
427 */
428 
429     --Error_Handler.Write_Debug('About to populate the EBI with Trans IDs');
430     --Populate the Transaction IDs for current result fmt usage ID
431      --New Transaction ID. It will be replaced by old Transaction ID Seq.
432      --SET  transaction_id = MSII_TRANSACTION_ID_S.NEXTVAL
433     UPDATE EGO_BULKLOAD_INTF
434      SET  Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
435     WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND PROCESS_STATUS = 1 ;
436 
437     IF l_debug = 'Y' THEN
438       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering First Loop ');
439     END IF;
440 
441 
442 --  Get the Mapped Columns to a Table
443     i := 1;
444     FOR C_BOM_ATTRIBUTE_COLUMNS_REC IN C_BOM_ATTRIBUTE_COLUMNS
445      (
446        p_resultfmt_usage_id
447       )
448     LOOP
449       l_prod_col_name_tbl(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE;
450       l_intf_col_name_tbl(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
451       l_bom_col_name(i)      := C_BOM_ATTRIBUTE_COLUMNS_REC.BOM_INTF_COLUMN_NAME;
452       l_bom_tbl_name(i)      := C_BOM_ATTRIBUTE_COLUMNS_REC.BOM_INTF_TABLE_NAME;
453       l_bom_col_type(i)      := C_BOM_ATTRIBUTE_COLUMNS_REC.OPERATION_TYPE;
454 
455       IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_PARENT_NAME) THEN
456         l_parent_column := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
457       END IF;
458       IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_ITEM_NAME) THEN
459         l_item_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
460       END IF;
461       i := i+1;
462     END LOOP;
463 
464     IF l_debug = 'Y' THEN
465       FND_FILE.PUT_LINE( FND_FILE.LOG,'Exiting First Loop ');
466     END IF;
467 
468 
469 -- Process The Rows for BOM BO Header For Create and Update.
470     l_dyn_sql_insert := '';
471     l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (REQUEST_ID, Transaction_Type ';
472     l_dyn_sql_insert := l_dyn_sql_insert || ', Transaction_Id, Process_Flag, Item_Number ';
473     l_dyn_sql_insert := l_dyn_sql_insert || ', Organization_Code, Alternate_Bom_Designator) ';
474 
475     l_dyn_sql_select := '';
476     l_dyn_sql_select := l_dyn_sql_select || ' SELECT REQUEST_ID, Transaction_Type, Transaction_Id, 1, ' || l_item_col_name;
477     l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_ORG_CODE;
478     l_dyn_sql_select := l_dyn_sql_select || ', DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || ')';
479     l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';
480     l_dyn_sql_select := l_dyn_sql_select || ' AND  PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NULL ';
481 
482     l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
483 
484     IF l_debug = 'Y' THEN
485       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering First SQL 1' || l_dyn_sql);
486     END IF;
487 
488 
489     EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
490 -- Header Data Ready
491 
492 -- Process Components for UPDATE
493     l_dyn_sql_insert := '';
494     l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( REQUEST_ID, Transaction_Type, Transaction_Id, Process_Flag, ';
495     l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';
496     l_dyn_sql_select := '';
497     l_dyn_sql_select := l_dyn_sql_select || 'SELECT REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
498     l_dyn_sql_select := l_dyn_sql_select || 'DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || '), ';
499     l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';
500 
501     FOR i IN 1..l_prod_col_name_tbl.COUNT LOOP
502       IF (l_bom_col_name(i) IS NOT NULL) THEN
503       -- For Effectivity_Date, Operation_Seq_Num, and From_End_Unit_Number changes
504       -- we need to update the New_Effectivity_Date, New_Operation_Seq_Num,
505       -- and New_From_End_Unit_Number
506 
507 
508         IF (l_bom_col_name(i) = G_EFFECTIVITY_DATE) THEN
509           l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_EFFECTIVITY_DATE,';
510           -- If the new effectivity date is same as the effectivity date in the database
511           -- then insert null other wise insert new value into the interface table.
512           l_str := 'DECODE(( SELECT To_Char(Effectivity_Date,''DD-MON-YYYY HH24:MI:SS'') FROM Bom_inventory_Components WHERE Component_Sequence_Id = ' || G_INTF_COMP_SEQ_ID || ')';
513           l_str := l_str || ',' || l_intf_col_name_tbl(i) ||',TO_DATE(NULL, ''DD-MON-YYYY HH24:MI:SS'')';
514           l_str := l_str || ',TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS'')),';
515           l_dyn_sql_select := l_dyn_sql_select || l_str;
516         ELSE
517             IF (l_bom_col_name(i) = G_OPERATION_SEQ_NUM) THEN
518               l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_OPERATION_SEQ_NUM,';
519             ELSIF (l_bom_col_name(i) = G_FROM_END_ITEM_UNIT_NUMBER) THEN
520               l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_FROM_END_ITEM_UNIT_NUMBER,';
521             ELSE
522               l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
523             END IF;
524          -- As date values are coming as character values convert them as dates.
525             IF ((l_bom_col_type(i) IS NOT NULL) AND (l_bom_col_type(i) = 'DATETIME')) THEN
526               l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
527             ELSE
528               l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
529             END IF;
530         END IF;
531 
532       END IF;
533     END LOOP;
534 
535     l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
536     l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
537     l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
538     l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
539     l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';
540     l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NOT NULL ';
541     l_dyn_sql_select := l_dyn_sql_select || ' AND Transaction_Type = ''' || G_TXN_UPDATE || ''' ';
542 
543     l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
544 
545     IF l_debug = 'Y' THEN
546       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 2' || l_dyn_sql);
547     END IF;
548 
549     EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
550 -- End of Process Components for UPDATE
551       IF l_debug = 'Y' THEN
552         FND_FILE.PUT_LINE( FND_FILE.LOG,'Executed Succesfully 2');
553       END IF;
554 
555 
556 -- Process Components for CREATE/ADD
557   -- Create Structure Header record if that is not available.
558   -- Comment this block if it is not required.
559     l_dyn_sql_cursor := '';
560     l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT Distinct Transaction_Id, ' || l_parent_column ;
561     l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_ORG_CODE;
562     l_dyn_sql_cursor := l_dyn_sql_cursor || ', DECODE(' || G_INTF_STRUCT_NAME || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || ')';
563     l_dyn_sql_cursor := l_dyn_sql_cursor || ' FROM EGO_BULKLOAD_INTF WHERE Process_Status = 1';
564     l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND Resultfmt_Usage_Id = :RESULTFMT_USAGE_ID ';
565     l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND ' || l_parent_column || ' IS NOT NULL ';
566     l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND (Transaction_Type = ''' || G_TXN_CREATE || ''' OR  Transaction_Type = ''' || G_TXN_ADD || ''' ';
567     l_dyn_sql_cursor := l_dyn_sql_cursor || ' OR Transaction_Type = ''' || G_TXN_SYNC || ''' )';
568 
569     IF l_debug = 'Y' THEN
570       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 2.1' || l_dyn_sql_cursor);
571     END IF;
572 
573     l_cursor_select := Dbms_Sql.Open_Cursor;
574     Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);
575     Dbms_Sql.Define_Column(l_cursor_select, 1, L_TRANSACTION_ID);
576     Dbms_Sql.Define_Column(l_cursor_select, 2, L_PARENT_NAME, 5000);
577     Dbms_Sql.Define_Column(l_cursor_select, 3, L_ORGANIZATION_CODE, 5000);
578     Dbms_Sql.Define_Column(l_cursor_select, 4, L_STRUCTURE_NAME, 5000);
579 
580     Dbms_Sql.Bind_Variable(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
581 
582     IF l_debug = 'Y' THEN
583       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 2.2' || p_resultfmt_usage_id);
584     END IF;
585 
586 
587     l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);
588 
589     IF l_debug = 'Y' THEN
590       FND_FILE.PUT_LINE( FND_FILE.LOG,'SUCCESS');
591     END IF;
592     i := 1;
593     LOOP
594       IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
595         Dbms_Sql.Column_Value(l_cursor_select,1,L_TRANSACTION_ID);
596         Dbms_Sql.Column_Value(l_cursor_select,2,L_PARENT_NAME);
597         Dbms_Sql.Column_Value(l_cursor_select,3,L_ORGANIZATION_CODE);
598         Dbms_Sql.Column_Value(l_cursor_select,4,L_STRUCTURE_NAME);
599 
600         l_Org_Id := ORGANIZATION(L_ORGANIZATION_CODE);
601         l_Inv_Item_Id := Component_Item(l_Org_Id,L_PARENT_NAME);
602         l_Bill_Seq_Id := Bill_Sequence(l_Inv_Item_Id,L_STRUCTURE_NAME,l_Org_Id);
603         IF(l_Bill_Seq_Id IS NULL) THEN
604         INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
605             REQUEST_ID,
606             TRANSACTION_TYPE,
607             TRANSACTION_ID,
608             PROCESS_FLAG,
609             ITEM_NUMBER,
610             ORGANIZATION_CODE,
611             ALTERNATE_BOM_DESIGNATOR           )
612         VALUES                                 (
613             G_REQUEST_ID,
614             G_TXN_CREATE,
615             L_TRANSACTION_ID,
616             1,
617             L_PARENT_NAME,
618             L_ORGANIZATION_CODE,
619             L_STRUCTURE_NAME                   );
620         END IF;
621         i := i+ 1;
622       ELSE
623         EXIT;
624       END IF;
625     END LOOP;
626 
627     IF l_debug = 'Y' THEN
628       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 3' || l_dyn_sql);
629     END IF;
630 
631 
632     Dbms_Sql.Close_Cursor(l_cursor_select);
633   -- End of Creating Structure Header record if that is not available.
634 
635     l_dyn_sql_insert := '';
636     l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( REQUEST_ID, Transaction_Type, Transaction_Id, Process_Flag, ';
637     l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';
638     l_dyn_sql_select := '';
639     l_dyn_sql_select := l_dyn_sql_select || 'SELECT REQUEST_ID, ''' || G_TXN_CREATE || ''', Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
640     l_dyn_sql_select := l_dyn_sql_select || 'DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || '), ';
641     l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';
642 
643     FOR i IN 1..l_prod_col_name_tbl.COUNT LOOP
644       IF (l_bom_col_name(i) IS NOT NULL) THEN
645         l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
646 
647      -- As date values are coming as character values convert them as dates.
648         IF ((l_bom_col_type(i) IS NOT NULL) AND (l_bom_col_type(i) = 'DATETIME')) THEN
649           l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
650         ELSE
651           l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
652         END IF;
653 
654       END IF;
655     END LOOP;
656 
657     l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
658     l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
659     l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
660     l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
661     l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';
662     l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NOT NULL ';
663 --    l_dyn_sql_select := l_dyn_sql_select || ' AND (Transaction_Type = ''' || G_TXN_CREATE || ''' OR  Transaction_Type = ''' || G_TXN_ADD || ''') ';
664 
665     l_dyn_sql_select := l_dyn_sql_select || ' AND (Transaction_Type = ''' || G_TXN_CREATE || ''' OR  Transaction_Type = ''' || G_TXN_ADD || ''' ';
666     l_dyn_sql_select := l_dyn_sql_select || ' OR Transaction_Type = ''' || G_TXN_SYNC || ''' )';
667 
668 
669     l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
670 
671     IF l_debug = 'Y' THEN
672       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 4' || l_dyn_sql);
673     END IF;
674 
675     EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
676 -- End of Process Components for CREATE/ADD
677 
678       IF l_debug = 'Y' THEN
679         FND_FILE.PUT_LINE( FND_FILE.LOG,'Executed Succesfully 4');
680       END IF;
681 
682 -- Start of process components for Delete
683     l_dyn_sql_insert := '';
684     l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( REQUEST_ID, Transaction_Type, Transaction_Id, Process_Flag, ';
685     l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';
686     l_dyn_sql_insert := l_dyn_sql_insert || 'DELETE_GROUP_NAME, DG_DESCRIPTION, ';
687     l_dyn_sql_select := '';
688     l_dyn_sql_select := l_dyn_sql_select || 'SELECT REQUEST_ID, Transaction_Type, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
689     l_dyn_sql_select := l_dyn_sql_select || 'DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || '), ';
690     l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';
691     l_dyn_sql_select := l_dyn_sql_select || '''' || G_DEL_GROUP_NAME || ''', ''' || G_DEL_GROUP_DESC || ''', ' ;
692 
693     FOR i IN 1..l_prod_col_name_tbl.COUNT LOOP
694       IF (l_bom_col_name(i) IS NOT NULL) THEN
695         l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
696 
697      -- As date values are coming as character values convert them as dates.
698         IF ((l_bom_col_type(i) IS NOT NULL) AND (l_bom_col_type(i) = 'DATETIME')) THEN
699           l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
700         ELSE
701           l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
702         END IF;
703 
704       END IF;
705     END LOOP;
706 
707     l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
708     l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
709     l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
710     l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
711     l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';
712     l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NOT NULL ';
713     l_dyn_sql_select := l_dyn_sql_select || ' AND Transaction_Type = ''' || G_TXN_DELETE || ''' ';
714 
715     l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
716 
717     IF l_debug = 'Y' THEN
718       FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 5' || l_dyn_sql);
719     END IF;
720 
721     EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
722 -- End of process components for Delete
723 
724     IF l_debug = 'Y' THEN
725       FND_FILE.PUT_LINE( FND_FILE.LOG,'Before UPdate of AssemblyType');
726     END IF;
727 
728 -- Updateing the assembly_type to 2 for BOM_header
729     UPDATE BOM_BILL_OF_MTLS_INTERFACE set assembly_type = 2
730     where assembly_type is null;
731 
732     IF l_debug = 'Y' THEN
733       FND_FILE.PUT_LINE( FND_FILE.LOG,'After UPdate of AssemblyType');
734     END IF;
735 
736 -- iNSERT ROWS FOR PRIMARY ALTERNATE FOR creating primary bom if it doesn't exist
737 -- Also set the txn id for all those rows
738     FOR C_BOM_BILL_PRIMARY_REC IN C_BOM_BILL_PRIMARY
739      (
740        G_REQUEST_ID
741       )
742     LOOP
743       INSERT INTO BOM_BILL_OF_MTLS_INTERFACE
744       (
745         ASSEMBLY_ITEM_ID,
746         ORGANIZATION_ID,
747         ASSEMBLY_TYPE,
748         PROCESS_FLAG,
749         ORGANIZATION_CODE,
750         COMMON_ORG_CODE,
751         ITEM_NUMBER,
752         IMPLEMENTATION_DATE,
753         ALTERNATE_BOM_DESIGNATOR,
754         TRANSACTION_TYPE,
755         REQUEST_ID)
756       VALUES
757       (
758         C_BOM_BILL_PRIMARY_REC.ASSEMBLY_ITEM_ID,
759         C_BOM_BILL_PRIMARY_REC.ORGANIZATION_ID,
760         C_BOM_BILL_PRIMARY_REC.ASSEMBLY_TYPE,
761         C_BOM_BILL_PRIMARY_REC.PROCESS_FLAG,
762         C_BOM_BILL_PRIMARY_REC.ORGANIZATION_CODE,
763         C_BOM_BILL_PRIMARY_REC.COMMON_ORG_CODE,
764         C_BOM_BILL_PRIMARY_REC.ITEM_NUMBER,
765         C_BOM_BILL_PRIMARY_REC.IMPLEMENTATION_DATE,
766         NULL,
767         G_TXN_SYNC,
768         G_REQUEST_ID);
769     END LOOP;
770 
771 
772     UPDATE BOM_BILL_OF_MTLS_INTERFACE
773      SET  Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
774     WHERE  REQUEST_ID = G_REQUEST_ID AND PROCESS_FLAG = 1
775     and Transaction_Id is null;
776 
777 
778 -- Call the BOM API TO PROCESS INTERFACE TABLES
779    l_err_return_code := bom_open_interface_api.import_bom
780       ( org_id    => 207 --Dummy value, all_org below carries precedence
781       , all_org   => 1
782       , err_text  => l_err_text
783       );
784 
785 
786     IF l_debug = 'Y' THEN
787       FND_FILE.PUT_LINE( FND_FILE.LOG,'Done Processing');
788     END IF;
789 
790     --Error_Handler.Write_Debug('Structure Import : UPDATE : l_err_text = ' || l_err_text);
791 
792 -- Call completion procedure
793     Structure_Intf_Proc_Complete
794     (
795       p_resultfmt_usage_id  => p_resultfmt_usage_id
796       ,x_errbuff             => l_errbuff
797       ,x_retcode             => l_retcode
798     );
799 
800     IF l_debug = 'Y' THEN
801       FND_FILE.PUT_LINE( FND_FILE.LOG,'Completed Processing');
802     END IF;
803 
804     --Error_Handler.Write_Debug('Updated the Process Status to Indicate Successful/Unsucessful component/structure Import Completion');
805 
806   EXCEPTION
807     WHEN OTHERS THEN
808     l_err_text := SQLERRM;
809       --Error_Handler.Write_Debug('WHEN OTHERS Exception.');
810       --Error_Handler.Write_Debug('error code : '|| TO_CHAR(SQLCODE));
811       --Error_Handler.Write_Debug('error text : '|| SQLERRM);
812       x_errbuff := 'Error : '||TO_CHAR(SQLCODE)||'---'||SQLERRM;
813       x_retcode := Error_Handler.G_STATUS_ERROR;
814       IF l_debug = 'Y' THEN
815         FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering Exception Message ' || x_errbuff);
816         FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering Exception Code' || x_retcode);
817       END IF;
818       Error_Handler.Close_Debug_Session;
819 
820 END PROCESS_BOM_INTERFACE_LINES;
821 
822 END EGO_BOM_BULKLOAD_PVT_PKG;