[Home] [Help]
PACKAGE BODY: APPS.BOM_BULKLOAD_PVT_PKG
Source
4 -- =================================================================
1 PACKAGE BODY BOM_BULKLOAD_PVT_PKG AS
2 /* $Header: BOMBBLPB.pls 120.40.12020000.2 2012/07/13 01:44:13 mshirkol ship $ */
3
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 --sREEJITH
16 G_INTF_SRCSYS_COMPONENT VARCHAR2(15) := 'C_FIX_COLUMN12';
17 G_INTF_SRCSYS_DESCRIPTION VARCHAR2(15) := 'C_FIX_COLUMN13';
18 G_INTF_SRCSYS_PARENT VARCHAR2(15) := 'C_FIX_COLUMN14';
19
20 G_STATUS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
21 G_STATUS_ERROR CONSTANT VARCHAR2(1) := 'E';
22
23 --This is the UI language.
24 G_LANGUAGE_CODE VARCHAR2(3);
25 G_CONCREQ_VALID_FLAG BOOLEAN := FALSE;
26
27 G_ERROR_TABLE_NAME VARCHAR2(99) := 'BOM_BULKLOAD_INTF';
28 G_ERROR_ENTITY_CODE VARCHAR2(99) := 'EGO_ITEM';
29 G_ERROR_FILE_NAME VARCHAR2(99);
30 G_BO_IDENTIFIER VARCHAR2(99) := 'EGO_ITEM';
31 G_BOM_APPLICATION_ID NUMBER(3) := 702;
32 G_COMP_ATTR_GROUP_TYPE VARCHAR2(30) := 'BOM_COMPONENTMGMT_GROUP';
33
34 ---------------------------------------------------------------
35 -- Interface line processing statuses. --
39 G_INTF_STATUS_ERROR CONSTANT NUMBER := 3;
36 ---------------------------------------------------------------
37 G_INTF_STATUS_TOBE_PROCESS CONSTANT NUMBER := 1;
38 G_INTF_STATUS_SUCCESS CONSTANT NUMBER := 7;
40
41 ---------------------------------------------------------------
42 -- The process status to be set to the interface table is --
43 -- 1 by default. But for non PDH batch, set status to 0 --
44 ---------------------------------------------------------------
45
46 G_PROCESS_STATUS NUMBER := 1;
47
48 ----------------------------------------------------------------------------
49 -- The Date Format is chosen to be as close as possible to Timestamp format,
50 -- except that we support dates before zero A.D. (the "S" in the year part).
51 ----------------------------------------------------------------------------
52 G_DATE_FORMAT CONSTANT VARCHAR2(30) := 'SYYYY-MM-DD HH24:MI:SS';
53
54 -----------------------------------------------------------------------
55 -- These are the Constants to generate a New Line Character. --
56 -----------------------------------------------------------------------
57 G_CARRIAGE_RETURN VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(13);
58 G_LINE_FEED VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
59 G_NEWLINE VARCHAR2(2) := G_LINE_FEED;
60
61 -----------------------------------------------------------------------
62 -- TYPE Defenitions for RDs
63 -----------------------------------------------------------------------
64 TYPE RD_VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
65 G_MISS_RD_VARCHAR_TBL RD_VARCHAR_TBL_TYPE;
66
67 /* Function that checks if there are any rows to be processed in the
68 * EGO_BULKLOAD_INTF prior to running all the heavy dbms_sqls.
69 * This will give a performance boost if the users are loading data
70 * through the same batch_id
71 */
72 FUNCTION Interface_Rows_Exist
73 (p_resultfmt_usage_id IN NUMBER)
74 return BOOLEAN
75 IS
76 l_unprocessed_rowcount number := 0;
77 l_rows_exists BOOLEAN := FALSE;
78 begin
79 SELECT
80 COUNT(RESULTFMT_USAGE_ID) into l_unprocessed_rowcount
81 FROM
82 EGO_BULKLOAD_INTF E
83 where
84 E.RESULTFMT_USAGE_ID = p_resultfmt_usage_id
85 and E.PROCESS_STATUS = 1;
86 IF (l_unprocessed_rowcount > 0) then
87 l_rows_exists := TRUE;
88 END IF;
89 RETURN l_rows_exists;
90 end;
91
92 -----------------------------------------------------------------
93 -- Write Debug statements to Log using Error Handler procedure --
94 -----------------------------------------------------------------
95 PROCEDURE Write_Debug (p_msg IN VARCHAR2) IS
96 l_debug VARCHAR2(10);
97 BEGIN
98 l_debug := fnd_profile.value('MRP_DEBUG');
99
100 -- NOTE: No need to check for profile now, as Error_Handler checks
101 -- for Error_Handler.Get_Debug = 'Y' before writing to Debug Log.
102 IF l_debug = 'Y' THEN
103 FND_FILE.PUT_LINE( FND_FILE.LOG, '['||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'] '|| p_msg);
104 END IF;
105 END;
106
107 PROCEDURE open_debug_session IS
108
109 CURSOR c_get_utl_file_dir IS
110 SELECT VALUE
111 FROM V$PARAMETER
112 WHERE NAME = 'utl_file_dir';
113
114 --local variables
115 l_log_output_dir VARCHAR2(200);
116 l_log_return_status VARCHAR2(99);
117 l_errbuff VARCHAR2(999);
118 BEGIN
119 -- Bug 13873323 start
120 IF G_HAS_UTL_FILE_DIR = 'N'
121 THEN
122 OPEN c_get_utl_file_dir;
123 FETCH c_get_utl_file_dir INTO l_log_output_dir;
124 IF c_get_utl_file_dir%FOUND THEN
125 ------------------------------------------------------
126 -- Trim to get only the first directory in the list --
127 ------------------------------------------------------
128 IF INSTR(l_log_output_dir,',') <> 0 THEN
129 l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
130 END IF;
131 END IF;--IF c_get_utl_file_dir%FOUND THEN
132 -- Bug 13873323 start
133 G_HAS_UTL_FILE_DIR := 'Y';
134 G_UTL_FILE_DIR := l_log_output_dir;
135 -- Bug 13873323 start
136 ELSE
137 l_log_output_dir := G_UTL_FILE_DIR;
138 END IF; -- G_HAS_UTL_FILE_DIR
139
140 -- Bug 13873323 start
141 IF G_HAS_UTL_FILE_DIR = 'Y'
142 THEN
143 G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'_'||TO_CHAR(SYSDATE, 'DDMONYYYY_HH24MISS')||'.err';
144
145 Error_Handler.Open_Debug_Session(
146 p_debug_filename => G_ERROR_FILE_NAME
147 ,p_output_dir => l_log_output_dir
148 ,x_return_status => l_log_return_status
149 ,x_error_mesg => l_errbuff
150 );
151
152 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);
153
154 IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
155 FND_FILE.put_line(FND_FILE.LOG, 'Unable to open error log file. Error => '||l_errbuff);
156 END IF;
157
158 END IF;--IF c_get_utl_file_dir%FOUND THEN
159
160 END open_debug_session;
161
165 )
162 FUNCTION Bill_Sequence( p_assembly_item_id IN NUMBER
163 , p_alternate_bom_designator IN VARCHAR2
164 , p_organization_id IN NUMBER
166 RETURN NUMBER IS
167 l_id NUMBER;
168 BEGIN
169
170 SELECT Bill_Sequence_Id
171 INTO l_id
172 FROM Bom_Bill_Of_Materials
173 WHERE Assembly_Item_Id = p_assembly_item_id
174 AND NVL(Alternate_Bom_Designator, 'NONE') =
175 DECODE(p_alternate_bom_designator,NULL,'NONE',p_alternate_bom_designator)
176 AND Organization_Id = p_organization_id;
177
178 RETURN l_id;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 RETURN NULL;
183 END Bill_Sequence;
184
185 /********************************************************************
186 * Function : Organization
187 * Returns : NUMBER
188 * Purpose : Will convert the value of organization_code to
189 * organization_id using MTL_PARAMETERS.
190 * If the conversion fails then the function will return
191 * a NULL otherwise will return the org_id.
192 * For an unexpected error function will return a
193 * missing value.
194 *********************************************************************/
195 FUNCTION GET_ORGANIZATION_ID
196 ( p_organization IN VARCHAR2) RETURN NUMBER
197 IS
198 l_id NUMBER;
199 BEGIN
200 SELECT Organization_Id
201 INTO l_id
202 FROM Mtl_Parameters
203 WHERE Organization_Code = p_organization;
204
205 RETURN l_id;
206
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 RETURN NULL;
210 WHEN OTHERS THEN
211 RETURN FND_API.G_MISS_NUM;
212 END GET_ORGANIZATION_ID;
213
214
215 /*******************************************************************
216 * Function : Component_Item
217 * Parameters IN : Component Item Name
218 * Organization ID
222 * corresponsind ID and return the value.
219 * Parameters OUT: Error Message
220 * Returns : Component_Item_Id
221 * Purpose : Function will convert the component item name to its
223 * If the component is invalid, then a NULL is returned.
224 *********************************************************************/
225 FUNCTION Component_Item( p_organization_id IN NUMBER
226 , p_component_item_num IN VARCHAR2)
227 RETURN NUMBER
228 IS
229 l_id NUMBER;
230 ret_code NUMBER;
231 l_err_text VARCHAR2(2000);
232 BEGIN
233 ret_code := INVPUOPI.Mtl_Pr_Parse_Flex_Name(
234 Org_Id => p_organization_id,
235 Flex_Code => 'MSTK',
236 Flex_Name => p_component_item_num,
237 Flex_Id => l_id,
238 Set_Id => -1,
239 Err_Text => l_err_text);
240
241 IF (ret_code <> 0) THEN
242 RETURN NULL;
243 END IF;
244
245 RETURN l_id;
246
247 END Component_Item;
248
249
250 ------------------------------------------------------------------------------------
251 PROCEDURE Structure_Intf_Proc_Complete
252 (
253 p_resultfmt_usage_id IN NUMBER
254 , x_errbuff OUT NOCOPY VARCHAR2
255 , x_retcode OUT NOCOPY VARCHAR2
256 ) IS
257
258 BEGIN
259
260 -- Update process flag in Ego Bulkload interface table
261 UPDATE EGO_BULKLOAD_INTF EBI
262 SET EBI.PROCESS_STATUS =
263 (
264 SELECT BMI.PROCESS_FLAG
265 FROM BOM_BILL_OF_MTLS_INTERFACE BMI
266 WHERE BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
267 )
268 WHERE EXISTS
269 (
270 SELECT 'X'
271 FROM BOM_BILL_OF_MTLS_INTERFACE BMI
272 WHERE BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
273 )
274 AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
275
276
277 UPDATE EGO_BULKLOAD_INTF EBI
278 SET EBI.PROCESS_STATUS =
279 (
280 SELECT BICI.PROCESS_FLAG
281 FROM BOM_INVENTORY_COMPS_INTERFACE BICI
282 WHERE BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
283 )
284 WHERE EXISTS
285 (
286 SELECT 'X'
287 FROM BOM_INVENTORY_COMPS_INTERFACE BICI
288 WHERE BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
289 )
290 AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;
291
292
293 -- Commiting after the process flag is updated.
294 COMMIT;
295
296 --Error_Handler.Write_Debug('EBI: Updated the Process_Status to Indicate Succssful/Unsucessful completion.');
297 x_retcode := G_STATUS_SUCCESS;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 x_retcode := G_STATUS_ERROR;
302 x_errbuff := SUBSTRB(SQLERRM, 1,240);
303 RAISE;
304 END Structure_Intf_Proc_Complete;
305
306 -------------------------------------------------------------------
307 -- Checks whether the given string is number or not --
308 -------------------------------------------------------------------
309 FUNCTION RD_isNumber(p_str_num varchar2) RETURN BOOLEAN IS
310 l_num NUMBER;
311 l_ret_code BOOLEAN;
312 BEGIN
313 l_ret_code := TRUE;
314 BEGIN
315 l_num := To_Number(p_str_num);
316 EXCEPTION
317 WHEN Invalid_Number THEN
318 l_ret_code := FALSE;
319 WHEN Value_Error THEN
320 l_ret_code := FALSE;
321 WHEN OTHERS THEN
322 l_ret_code := FALSE;
323 END;
324 RETURN l_ret_code;
325 END;
326
327 -------------------------------------------------------------------
328 -- Split the string and return as ARRAY of Reference Designators --
329 -------------------------------------------------------------------
330 PROCEDURE splitIntoArray (p_comp_ref_desig IN VARCHAR2,
331 x_rd_tbl IN OUT NOCOPY RD_VARCHAR_TBL_TYPE)
332 IS
333 l_rds_tbl RD_VARCHAR_TBL_TYPE;
334 l_next_sep NUMBER;
335 l_prev_sep NUMBER;
336 l_sep_count NUMBER;
337 BEGIN
338 Write_Debug('Inside splitIntoArray');
339 l_sep_count := 1;
340 l_prev_sep := 1;
341 Write_debug('p_comp_ref_desig:---->' || p_comp_ref_desig || '<---');
342 IF (p_comp_ref_desig IS NOT NULL) THEN
343 l_next_sep := INSTR(p_comp_ref_desig,',', 1, l_sep_count);
344 IF l_next_sep = 0 THEN
345 l_rds_tbl (l_sep_count) := Trim(p_comp_ref_desig);
346 Write_debug(' RD No:' || l_sep_count ||':-->' || p_comp_ref_desig);
347 ELSE
348 WHILE l_next_sep > 0 LOOP
349 l_rds_tbl (l_sep_count) := Trim(SUBSTR(p_comp_ref_desig,l_prev_sep,(l_next_Sep - l_prev_sep)));
350 Write_debug(' RD No:' || l_sep_count ||':-->' || SUBSTR(p_comp_ref_desig,l_prev_sep,(l_next_Sep - l_prev_sep)));
351 l_sep_count := l_sep_count + 1;
352 l_prev_sep := l_next_sep + 1;
353 l_next_sep := INSTR(p_comp_ref_desig,',', 1, l_sep_count);
354 END LOOP;
355 IF (l_sep_count > 1 )THEN
356 l_rds_tbl (l_sep_count) := Trim(SUBSTR(p_comp_ref_desig,l_prev_sep));
357 Write_debug(' RD No:' || l_sep_count ||':-->' || SUBSTR(p_comp_ref_desig,l_prev_sep));
358 END IF;
359 END IF;
360 END IF;
361 x_rd_tbl := l_rds_tbl;
362 END splitIntoArray;
363
364 -------------------------------------------------------------------
365 -- Process Reference Designators --
366 -------------------------------------------------------------------
367 PROCEDURE getListOfRefDesigs (p_comp_ref_desig IN VARCHAR2,
371 l_ref_desig_tbl RD_VARCHAR_TBL_TYPE;
368 x_comp_ref_desig_tbl IN OUT NOCOPY RD_VARCHAR_TBL_TYPE)
369 IS
370 l_rd_tbl RD_VARCHAR_TBL_TYPE;
372 l_all_rd_count NUMBER;
373 I NUMBER;
374 -- Variables to support processing RDs with '-'
375 l_rd_str VARCHAR2(30);
376 l_rd_str_from VARCHAR2(30);
377 l_rd_str_to VARCHAR2(30);
378 l_prefix1 VARCHAR2(30);
379 l_suffix1 VARCHAR2(30);
380 l_prefix2 VARCHAR2(30);
381 l_suffix2 VARCHAR2(30);
382 l_ref_desig VARCHAR2(30);
383
384 l_rd_sep_pos NUMBER;
385 l_pre_num_start1 NUMBER;
386 l_pre_num_end1 NUMBER;
387 l_pre_num_start2 NUMBER;
388 l_pre_num_end2 NUMBER;
389 l_rd_num_from NUMBER;
390 l_rd_num_to NUMBER;
391 l_temp_num NUMBER;
392 l_num_len NUMBER;
393
394 BEGIN
395 Write_Debug('Inside getListOfRefDesigs');
396 --l_ref_desig_tbl := x_comp_ref_desig_tbl;
397 l_all_rd_count := 0;
398 splitIntoArray(p_comp_ref_desig => p_comp_ref_desig, x_rd_tbl => l_rd_tbl);
399
400 FOR I IN 1..l_rd_tbl.COUNT LOOP
401 --l_all_rd_count := l_all_rd_count + 1;
402 --l_ref_desig_tbl(l_all_rd_count) := l_rd_tbl(I);
403 -----------------
404 l_rd_str := l_rd_tbl(I);
405 l_rd_sep_pos := InStr(l_rd_str,'-');
406 Write_Debug('Position:--->' || l_rd_sep_pos || ': Length:--->' || Length(l_rd_str));
407 IF( l_rd_sep_pos > 0 AND Length(l_rd_str) > l_rd_sep_pos) THEN
408 l_rd_str_from := SubStr(l_rd_str,0,(l_rd_sep_pos-1));
409 l_rd_str_to := SubStr(l_rd_str,(l_rd_sep_pos+1));
410 Write_Debug('From:-->' || l_rd_str_from);
411 Write_Debug('TO :-->' || l_rd_str_to);
412 l_temp_num := Length(l_rd_str_from);
413 -- Get the prefix from the first part
414 FOR I IN 1..l_temp_num LOOP
415 IF(RD_isNumber(SubStr(l_rd_str_from,I,1))) THEN
416 Write_Debug('Number start position:' || I);
417 l_pre_num_start1 := I;
418 EXIT;
419 END IF;
420 END LOOP;
421 l_prefix1 := SubStr(l_rd_str_from,0,(l_pre_num_start1-1));
422 l_pre_num_end1 := 0;
423 Write_Debug('Prefix1:-->' || l_prefix1);
424 -- Get the suffix from the first part
425 FOR I IN l_pre_num_start1..l_temp_num LOOP
426 IF(NOT RD_isNumber(SubStr(l_rd_str_from,I,1))) THEN
427 Write_Debug('Suffix1 start position:' || I);
428 l_pre_num_end1 := I;
429 EXIT;
430 END IF;
431 END LOOP;
432 IF(l_pre_num_end1 <> 0) THEN
433 l_suffix1 := SubStr(l_rd_str_from,l_pre_num_end1);
434 Write_Debug('Suffix1:-->' || l_suffix1);
435 l_rd_num_from := To_Number(SubStr(l_rd_str_from,l_pre_num_start1,(l_pre_num_end1-l_pre_num_start1)));
436 ELSE
437 Write_Debug('Suffix1 IS NULL');
438 l_rd_num_from := To_Number(SubStr(l_rd_str_from,l_pre_num_start1));
439 END IF;
440 Write_Debug('Ref Desigs From:-->' || l_rd_num_from);
441 -- Get the prefix from the second part
442 l_temp_num := Length(l_rd_str_to);
443 FOR I IN 1..l_temp_num LOOP
444 IF(RD_isNumber(SubStr(l_rd_str_to,I,1))) THEN
445 Write_Debug('Number start position:' || I);
446 l_pre_num_start2 := I;
447 EXIT;
448 END IF;
449 END LOOP;
450 l_prefix2 := SubStr(l_rd_str_to,0,(l_pre_num_start2-1));
451 l_pre_num_end2 := 0;
452 Write_Debug('Prefix2:-->' || l_prefix2);
453 -- Get the suffix from the second part
454 FOR I IN l_pre_num_start2..l_temp_num LOOP
455 IF(NOT RD_isNumber(SubStr(l_rd_str_to,I,1))) THEN
456 Write_Debug('Suffix2 start position:' || I);
457 l_pre_num_end2 := I;
458 EXIT;
459 END IF;
460 END LOOP;
461 IF(l_pre_num_end2 <> 0) THEN
462 l_suffix2 := SubStr(l_rd_str_to,l_pre_num_end2);
463 Write_Debug('Suffix2:-->' || l_suffix2);
464 l_rd_num_to := To_Number(SubStr(l_rd_str_to,l_pre_num_start2,(l_pre_num_end2-l_pre_num_start2)));
465 ELSE
466 Write_Debug('Suffix2 IS NULL');
467 l_rd_num_to := To_Number(SubStr(l_rd_str_to,l_pre_num_start2));
468 END IF;
469 Write_Debug('Ref Desigs to:-->' || l_rd_num_to);
470 -- Get the from and to values to generate the list RDs
471 IF l_rd_num_from > l_rd_num_to THEN
472 l_rd_num_from := l_rd_num_from + l_rd_num_to;
473 l_rd_num_to := l_rd_num_from - l_rd_num_to;
474 l_rd_num_from := l_rd_num_from - l_rd_num_to;
475 END IF;
476 Write_Debug('Ref Desigs From:-->' || l_rd_num_from);
477 Write_Debug('Ref Desigs to:-->' || l_rd_num_to);
478 l_num_len := l_pre_num_end1 - l_pre_num_start1;
479 IF l_suffix1 IS NULL THEN
480 l_num_len := Length(l_rd_str_from) - l_pre_num_start1 + 1;
481 END IF;
482 Write_Debug('Ref Desigs Num Char Length:-->' || l_num_len);
483 IF(NOT (l_prefix1 <> l_prefix2 OR (l_suffix1 IS NOT NULL AND l_suffix1 <> l_suffix2) OR Length(l_rd_str_from) <> Length(l_rd_str_to))) THEN
484 -- Generate the list
485 FOR I IN l_rd_num_from..l_rd_num_to LOOP
486 l_ref_desig := l_prefix1 || LPad(I,l_num_len,'0') || l_suffix1;
487 l_all_rd_count := l_all_rd_count + 1;
488 l_ref_desig_tbl(l_all_rd_count) := l_ref_desig;
489 Write_Debug(' Reference Designator:' || I || '--->' || l_ref_desig);
493 l_all_rd_count := l_all_rd_count + 1;
490 END LOOP;
491 END IF;
492 ELSE
494 l_ref_desig_tbl(l_all_rd_count) := l_rd_str;
495 END IF;
496 END LOOP;
497 x_comp_ref_desig_tbl := l_ref_desig_tbl;
498 END getListOfRefDesigs;
499
500
501 PROCEDURE PROCESS_BOM_INTERFACE_LINES
502 (
503 p_batch_id IN NUMBER,
504 p_resultfmt_usage_id IN NUMBER,
505 p_user_id IN NUMBER,
506 p_conc_request_id IN NUMBER,
507 p_language_code IN VARCHAR2,
508 p_is_pdh_batch IN VARCHAR2,
509 x_errbuff IN OUT NOCOPY VARCHAR2,
510 x_retcode IN OUT NOCOPY VARCHAR2
511 )
512 IS
513
514 --Type Declarations
515 TYPE VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(256)
516 INDEX BY BINARY_INTEGER;
517
518 --Sreejith
519
520
521 --BOM RECORDS....
522 l_return_status VARCHAR2(1);
523 l_msg_count NUMBER := 0;
524 l_unexp_error VARCHAR2(1000);
525 tempVar NUMBER;
526 m NUMBER := 0; --counter for revised components
527 l_commonitem VARCHAR2(81);
528 error_message VARCHAR2(2000) := '';
529
530
531 l_return_code NUMBER;
532 l_err_text VARCHAR2(2000);
533 l_err_return_code INTEGER;
534
535 --API return parameters
536 l_retcode VARCHAR2(10);
537 l_errbuff VARCHAR2(2000);
538
539 --Dynamic Cursor Parameters
540 l_dyn_sql VARCHAR2(10000);
541 l_dyn_sql_select VARCHAR2(10000);
542 l_dyn_sql_insert VARCHAR2(10000);
543 l_dyn_sql_cursor VARCHAR2(10000);
544 l_msii_set_process_id NUMBER;
545 l_cursor_select INTEGER;
546 l_cursor_execute INTEGER;
547 l_temp NUMBER(10) := 1;
548
549 --Column Mapping Tables;
550 l_prod_col_name_tbl VARCHAR_TBL_TYPE;
551 l_intf_col_name_tbl VARCHAR_TBL_TYPE;
552 i NUMBER := 0;
553 j NUMBER := 0;
554 k NUMBER := 0;
555
556 --BOM Interface Table Mappings
557 l_bom_col_name VARCHAR_TBL_TYPE;
558 l_bom_tbl_name VARCHAR_TBL_TYPE;
559 l_bom_col_type VARCHAR_TBL_TYPE;
560 l_lookup_type VARCHAR_TBL_TYPE;
561
562 --Column Mappings
563 l_prod_col_name VARCHAR2(256);
564 l_intf_col_name VARCHAR2(256);
565 l_parent_column VARCHAR2(256);
566 l_item_col_name VARCHAR2(256);
567 l_org_id_column VARCHAR2(256);
568 l_altbom_column VARCHAR2(256);
569 l_comp_seq_col_name VARCHAR2(256);
570 l_eff_date_col_name VARCHAR2(256);
571 l_dis_date_col_name VARCHAR2(256);
572 l_oper_seq_col_name VARCHAR2(256);
573 l_from_unit_col_name VARCHAR2(256);
574 l_item_seq_col_name VARCHAR2(256);
575 l_imp_date_col_name VARCHAR2(256);
576
577 --Txn Types
578
579 G_TXN_CREATE VARCHAR2(10) := 'CREATE';
580 G_TXN_ADD VARCHAR2(10) := 'ADD';
581 G_TXN_UPDATE VARCHAR2(10) := 'UPDATE';
582 G_TXN_DELETE VARCHAR2(10) := 'DELETE';
583 G_TXN_SYNC VARCHAR2(10) := 'SYNC';
584 G_TXN_NO_OP VARCHAR2(10) := 'NO_OP';
585
586 -- COLUMN NAMES
587 G_ITEM_NAME VARCHAR2(30) := 'ITEM_NUMBER';
588 G_ORG_CODE VARCHAR2(30) := 'ORGANIZATION_CODE';
589 G_ALT_BOM VARCHAR2(30) := 'ALTERNATE_BOM_DESIGNATOR';
590 G_PARENT_NAME VARCHAR2(30) := 'PARENT_NAME';
591 G_QUANTITY VARCHAR2(30) := 'QUANTITY';
592 G_COMPONENT_SEQ_ID VARCHAR2(30) := 'COMPONENT_SEQUENCE_ID';
593 --Added by Hari to support import format
594 G_OP_SEQ_NUMBER VARCHAR2(30) := 'OperationSeqNum';
595 G_EFFECTIVY_DATE VARCHAR2(30) := 'EffectivityDate';
596 G_DISABLE_DATE VARCHAR2(30) := 'DisableDate';
597 G_FROM_UNIT_EFFECTIVE VARCHAR2(30) := 'FromEndItemUnitNumber';
598 G_ITEM_SEQUENCE VARCHAR2(30) := 'ItemNum';
599 G_IMPL_DATE VARCHAR2(30) := 'ImplementationDate';
600
601
602 -- Bom Interface column names
603 G_EFFECTIVITY_DATE VARCHAR2(30) := 'EFFECTIVITY_DATE';
604 G_OPERATION_SEQ_NUM VARCHAR2(30) := 'OPERATION_SEQ_NUM';
605 G_FROM_END_ITEM_UNIT_NUMBER VARCHAR2(30) := 'FROM_END_ITEM_UNIT_NUMBER';
606
607 --Sreejith
608 L_SRCSYS_PARENT VARCHAR2(240);
609
610 --Column Values
611 L_ITEM_NAME VARCHAR2(240) ;
612 L_ORGANIZATION_CODE VARCHAR2(3) ;
613 L_STRUCTURE_NAME VARCHAR2(10) ;
614 L_PARENT_NAME VARCHAR2(240) ;
615 L_QUANTITY NUMBER := 2;
616 L_TRANSACTION_ID NUMBER;
617 L_STR_TYPE_NAME VARCHAR2(80) ;
618 L_EFFEC_CONTROL VARCHAR2(80) ;
619 L_IS_PREF_MEANING VARCHAR2(80) ;
620 L_ASSTYPE_MEANING VARCHAR2(80) ;
621 L_PARENT_REVISION VARCHAR2(80) ; -- Bug No:5182523
622
623 -- Interface COLUMN NAMES
624 G_INTF_STRUCT_NAME VARCHAR2(30) := 'C_FIX_COLUMN3';
625 G_INTF_STR_TYPE_NAME VARCHAR2(30) := 'C_FIX_COLUMN5';
626 G_INTF_EFFEC_CONTROL VARCHAR2(30) := 'C_FIX_COLUMN7';
627 G_INTF_IS_PREFERRED VARCHAR2(30) := 'C_FIX_COLUMN8';
628 G_INTF_ORG_CODE VARCHAR2(30) := 'C_INTF_ATTR1';
629 G_INTF_COMP_SEQ_ID VARCHAR2(30) := 'N_INTF_ATTR1';
630 G_INTF_ASSEMBLY_TYPE VARCHAR2(30) := NULL;
631 G_INTF_PARENT_REVISION VARCHAR2(30) := NULL; -- Bug No:5182523
632 G_INTF_REVISION VARCHAR2(30) := NULL; -- Bug No:5182523
633 --Added by Hari to support import format
634 G_BILL_SEQUENCE_ID VARCHAR2(30) := 'INSTANCE_PK4_VALUE';
638
635 G_COMP_SEQUENCE_ID VARCHAR2(30) := 'INSTANCE_PK5_VALUE';
636 G_ASSEMBLY_ITEM_ID VARCHAR2(30) := 'C_INTF_ATTR226'; -- Ego people already used the cloumns from C_INTF_ATTR230
637 G_INTF_REF_DESIG VARCHAR2(30);
639 -- Temparory Variables
640 l_Org_Id NUMBER;
641 l_Inv_Item_Id NUMBER;
642 l_Bill_Seq_Id NUMBER;
643 l_str VARCHAR2(3000);
644 l_JCP_Id NUMBER;
645 l_eff_ctrl VARCHAR2(240);
646 l_is_preferred VARCHAR2(240);
647 l_assemblytype NUMBER;
648 l_str_type_id NUMBER;
649
650 -- Error Handler variables
651 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
652 l_Token_Tbl Error_Handler.Token_Tbl_Type;
653
654 -- Constant Values
655 G_DEL_GROUP_NAME VARCHAR2(10) := 'B_BLK_INTF';
656 G_DEL_GROUP_DESC VARCHAR2(240) := 'Delete Group for EGO BOM Bulkload Structures';
657
658 l_bom_header_columns_tbl DBMS_SQL.VARCHAR2_TABLE;
659
660 -- Variables for RDs
661 L_SRCSYS_ITEM VARCHAR2(240);
662 L_COMP_REF_DESIG VARCHAR2(3000); --Changed size from 240 to 3000 support larger size of reference designators
663 L_TRNSACTION_TYPE VARCHAR2(30);
664 --
665 l_comp_ref_desig_tbl RD_VARCHAR_TBL_TYPE;
666
667 -- Update sql variable for Multi Row
668 l_upd_sql VARCHAR2(3000);
669
670
671 --DEBUG FLAG
672 l_debug VARCHAR2(10);
673 --
674 -- Get the Header Data from the Parents.
675 --
676
677
678 --
679 -- To get the BOM Attribute columns in the Result Format.
680 --
681
682 CURSOR C_BOM_ATTRIBUTE_COLUMNS (c_Resultfmt_Usage_Id IN NUMBER) IS
683 SELECT
684 erf.Attribute_Code,
685 erf.Intf_Column_Name,
686 bcc.Bom_Intf_Column_Name ,
687 bcc.Bom_Intf_Table_Name,
688 bcc.Operation_Type,
689 bcc.Lookup_Type
690 FROM
691 Ego_Results_Fmt_Usages erf,
692 Bom_Component_Columns bcc
693 WHERE
694 (Region_Code = 'BOM_RESULT_DUMMY_REGION'
695 OR -- Fix for import Region
696 Region_Code = 'BOM_IMPORT_DUMMY_REGION' )
697 AND
698 Region_Application_Id = 702
699 AND
700 Customization_Application_Id = 431
701 AND
702 Resultfmt_Usage_Id = c_Resultfmt_Usage_Id
703 AND
704 bcc.Attribute_Code = erf.Attribute_Code
705 AND
706 bcc.OBJECT_TYPE = 'BOM_COMPONENTS'
707 AND
708 bcc.BOM_INTF_TABLE_NAME = 'BOM_INVENTORY_COMPS_INTERFACE'
709 AND
710 ( bcc.Parent_Entity IS NULL
711 OR BCC.Attribute_Code = 'ITEM_NUMBER'
712 OR BCC.Attribute_Code = 'PARENT_NAME')
713 AND
714 erf.Attribute_Code NOT LIKE '%$$%';
715
716 -- Cursor to create a row with 'Primary' (Null) alternate for structure headers
717 -- This is to automate the creation of primary boms for PLM purpose
718 CURSOR C_BOM_BILL_PRIMARY(C_REQUEST_ID IN NUMBER) IS
719 SELECT
720 ASSEMBLY_ITEM_ID,
721 ORGANIZATION_ID,
722 ASSEMBLY_TYPE,
723 PROCESS_FLAG,
724 ORGANIZATION_CODE,
725 COMMON_ORG_CODE,
726 ITEM_NUMBER,
727 IMPLEMENTATION_DATE
728 FROM
729 BOM_BILL_OF_MTLS_INTERFACE BMI
730 WHERE
731 PROCESS_FLAG = 1
732 AND
733 REQUEST_ID = C_REQUEST_ID
734 AND
735 BMI.Alternate_Bom_Designator IS NOT NULL
736 AND NOT EXISTS
737 (SELECT NULL FROM Bom_structures_b bsb
738 WHERE bsb.Assembly_Item_id IN (SELECT Inventory_item_id FROM mtl_system_items_vl WHERE concatenated_segments = bmi.Item_number)
739 AND bsb.Organization_id = (SELECT ORGANIZATION_ID FROM mtl_parameters WHERE ORGANIZATION_code = bmi.ORGANIZATION_code)
740 AND bsb.Alternate_Bom_Designator IS NULL
741 );
742
743
744 BEGIN
745 --Initializations
746 l_debug := fnd_profile.value('MRP_DEBUG');
747 IF (NVL(fnd_profile.value('CONC_REQUEST_ID'), 0) <> 0) THEN
748 G_CONCREQ_VALID_FLAG := TRUE;
749 END IF;
750 G_CONCREQ_VALID_FLAG := TRUE;
751
752 IF (G_CONCREQ_VALID_FLAG ) THEN
753 FND_FILE.put_line(FND_FILE.LOG, ' ******** New Log ******** ');
754 END IF;
755
756 -- the values are chosen from the FND_GLOBALS
757 G_USER_ID := FND_GLOBAL.user_id ;
758 G_LOGIN_ID := FND_GLOBAL.login_id ;
759 G_PROG_APPID := FND_GLOBAL.prog_appl_id ;
760 G_PROG_ID := FND_GLOBAL.conc_program_id ;
761 G_REQUEST_ID := FND_GLOBAL.conc_request_id ;
762 G_LANGUAGE_CODE := p_Language_Code;
763
764 l_oper_seq_col_name := null;
765 l_eff_date_col_name := null;
766 l_dis_date_col_name := null;
767 l_item_seq_col_name := null;
768 l_imp_date_col_name := null;
769
770 Error_Handler.initialize();
771 Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
772 -- Delete all the earlier uploads from the same spreadsheet.
773 /*
774 DELETE FROM EGO_BULKLOAD_INTF
775 WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
776 AND PROCESS_STATUS <> 1;
777 */
778
779
780 /* Return if no rows to process
781 * Should we be updating the requestIds?? Test
782 */
783 if (NOT Interface_Rows_Exist(p_resultfmt_usage_id)) then
784 FND_FILE.PUT_LINE( FND_FILE.LOG,'No Rows to Process for Data Separation' );
785 return;
786 end if;
787
788 -- Getting the Intf_column names from the ego_results_fmt_usages for the attributes
789 -- of OrgCode and CompSeqId. Previously we hard-coding them
790 IF l_debug = 'Y' THEN
794 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_PARENT_REVISION before :--->' || G_INTF_PARENT_REVISION);
791 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_ORG_CODE before :--->' || G_INTF_ORG_CODE);
792 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_COMP_SEQ_ID before :--->' || G_INTF_COMP_SEQ_ID);
793 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_ASSEMBLY_TYPE before :--->' || G_INTF_ASSEMBLY_TYPE);
795 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_REVISION before :--->' || G_INTF_REVISION);
796 END IF;
797
798 Select INTF_COLUMN_NAME into G_INTF_ORG_CODE FROM Ego_Results_Fmt_Usages erf
799 WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'ORGANIZATION_CODE';
800
801 BEGIN -- In imort formats may not be having the attribute ComponentSequenceId
802 Select INTF_COLUMN_NAME into G_INTF_COMP_SEQ_ID FROM Ego_Results_Fmt_Usages erf
803 WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'COMPONENT_SEQUENCE_ID';
804 EXCEPTION
805 WHEN NO_DATA_FOUND THEN
806 G_INTF_COMP_SEQ_ID := 'NULL';
807 END;
808
809 BEGIN
810 Select INTF_COLUMN_NAME into G_INTF_ASSEMBLY_TYPE FROM Ego_Results_Fmt_Usages erf
811 WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'SUB_ASSEMBLY_TYPE';
812 EXCEPTION
813 WHEN NO_DATA_FOUND THEN
814 G_INTF_ASSEMBLY_TYPE := 'NULL';
815 END;
816
817 BEGIN
818 Select INTF_COLUMN_NAME into G_INTF_PARENT_REVISION FROM Ego_Results_Fmt_Usages erf
819 WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'PARENT_REVISION_CODE';
820 EXCEPTION
821 WHEN NO_DATA_FOUND THEN
822 G_INTF_PARENT_REVISION := 'NULL';
823 END;
824
825 BEGIN
826 Select INTF_COLUMN_NAME into G_INTF_REVISION FROM Ego_Results_Fmt_Usages erf
827 WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'REVISION';
828 EXCEPTION
829 WHEN NO_DATA_FOUND THEN
830 G_INTF_REVISION := 'NULL';
831 END;
832
833 IF l_debug = 'Y' THEN
834 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_ORG_CODE after :--->' || G_INTF_ORG_CODE);
835 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_COMP_SEQ_ID after :--->' || G_INTF_COMP_SEQ_ID);
836 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_ASSEMBLY_TYPE after :--->' || G_INTF_ASSEMBLY_TYPE);
837 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_PARENT_REVISION after :--->' || G_INTF_PARENT_REVISION);
838 FND_FILE.PUT_LINE( FND_FILE.LOG,'G_INTF_REVISION after :--->' || G_INTF_REVISION);
839 END IF;
840
841 --bug 13829520 begin
842 IF (G_INTF_COMP_SEQ_ID IS NOT NULL AND G_INTF_COMP_SEQ_ID <> 'NULL') THEN
843 l_dyn_sql := '';
844 l_dyn_sql := l_dyn_sql || ' UPDATE EGO_BULKLOAD_INTF SET '||G_INTF_COMP_SEQ_ID||' = NULL ';
845 l_dyn_sql := l_dyn_sql || ' WHERE Resultfmt_Usage_Id = :RESULTFMT_USAGE_ID ';
846 l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
847 l_dyn_sql := l_dyn_sql || ' AND UPPER(TRANSACTION_TYPE) IN (''ADD'',''CREATE'') ';
848
849 EXECUTE IMMEDIATE l_dyn_sql USING p_resultfmt_usage_id;
850 END IF;
851 --bug 13829520 end
852
853 BEGIN -- In imort formats may not be having the attribute COMPONENT_REFERENCE_DESIGNATOR
854 Select INTF_COLUMN_NAME into G_INTF_REF_DESIG FROM Ego_Results_Fmt_Usages erf
855 WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND ATTRIBUTE_CODE = 'COMPONENT_REFERENCE_DESIGNATOR';
856 EXCEPTION
857 WHEN NO_DATA_FOUND THEN
858 G_INTF_REF_DESIG := 'NULL';
859 END;
860 Write_Debug('G_INTF_REF_DESIG after :--->' || G_INTF_REF_DESIG);
861 --Populate the Transaction IDs for current result fmt usage ID
862 --New Transaction ID. It will be replaced by old Transaction ID Seq.
863 --SET transaction_id = MSII_TRANSACTION_ID_S.NEXTVAL
864 UPDATE EGO_BULKLOAD_INTF
865 SET Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
866 WHERE Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND PROCESS_STATUS = 1 ;
867
868 IF l_debug = 'Y' THEN
869 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering First Loop ');
870 END IF;
871
872 --Sreejith
873
874 -- Get the Mapped Columns to a Table
875 i := 1;
876 FOR C_BOM_ATTRIBUTE_COLUMNS_REC IN C_BOM_ATTRIBUTE_COLUMNS
877 (
878 p_resultfmt_usage_id
879 )
880 LOOP
881 l_prod_col_name_tbl(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE;
882 l_intf_col_name_tbl(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
883 l_bom_col_name(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.BOM_INTF_COLUMN_NAME;
884 l_bom_tbl_name(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.BOM_INTF_TABLE_NAME;
885 l_bom_col_type(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.OPERATION_TYPE;
886 l_lookup_type(i) := C_BOM_ATTRIBUTE_COLUMNS_REC.LOOKUP_TYPE;
887
888 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_PARENT_NAME) THEN
889 l_parent_column := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
890 END IF;
891 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_ITEM_NAME) THEN
892 l_item_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
893 END IF;
894 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_EFFECTIVY_DATE) THEN
895 l_eff_date_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
896 END IF;
897 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_DISABLE_DATE) THEN
898 l_dis_date_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
899 END IF;
900 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_OP_SEQ_NUMBER) THEN
901 l_oper_seq_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
902 END IF;
903 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_FROM_UNIT_EFFECTIVE) THEN
907 l_item_seq_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
904 l_from_unit_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
905 END IF;
906 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_ITEM_SEQUENCE) THEN
908 END IF;
909 IF (C_BOM_ATTRIBUTE_COLUMNS_REC.ATTRIBUTE_CODE = G_IMPL_DATE) THEN
910 l_imp_date_col_name := C_BOM_ATTRIBUTE_COLUMNS_REC.INTF_COLUMN_NAME;
911 END IF;
912 i := i+1;
913 END LOOP;
914
915 IF l_debug = 'Y' THEN
916 FND_FILE.PUT_LINE( FND_FILE.LOG,'Exiting First Loop ');
917 END IF;
918
919 -- Added by hgelli for supporting import formats
920 -----------------------------------------------------
921 -- Update Instance PK2 Value with ORG ID.
922 -----------------------------------------------------
923 l_dyn_sql := '';
924 l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
925 l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK2_VALUE = ';
926 l_dyn_sql := l_dyn_sql || ' ( ';
927 l_dyn_sql := l_dyn_sql || ' SELECT ORGANIZATION_ID ';
928 l_dyn_sql := l_dyn_sql || ' FROM MTL_PARAMETERS ';
929 l_dyn_sql := l_dyn_sql || ' WHERE ORGANIZATION_CODE =EBI.'|| G_INTF_ORG_CODE;
930 l_dyn_sql := l_dyn_sql || ' ) ';
931 l_dyn_sql := l_dyn_sql || 'WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
932 l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
933
934 Write_Debug('Org Id Conversion-->' || l_dyn_sql);
935
936 EXECUTE IMMEDIATE l_dyn_sql USING p_Resultfmt_Usage_Id;
937
938 -----------------------------------------------------
939 -- Update Instance PK1 Value with Component Item ID
940 -----------------------------------------------------
941 l_dyn_sql := '';
942 l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
943 l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK1_VALUE = ';
944 l_dyn_sql := l_dyn_sql || ' ( ';
945 l_dyn_sql := l_dyn_sql || ' SELECT inventory_item_id ';
946 l_dyn_sql := l_dyn_sql || ' FROM mtl_system_items_vl mvll ';
947 l_dyn_sql := l_dyn_sql || ' WHERE mvll.concatenated_segments = EBI.'|| l_item_col_name;
948 l_dyn_sql := l_dyn_sql || ' AND mvll.organization_id = EBI.INSTANCE_PK2_VALUE';
949 l_dyn_sql := l_dyn_sql || ' ) ';
950 l_dyn_sql := l_dyn_sql || 'WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
951 l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
952
953 Write_Debug('Component Item Id Conversion-->' || l_dyn_sql);
954
955 EXECUTE IMMEDIATE l_dyn_sql USING p_Resultfmt_Usage_Id;
956
957 -----------------------------------------------------
958 -- Populate Assembly Item ID
959 -----------------------------------------------------
960 l_dyn_sql := '';
961 l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
962 l_dyn_sql := l_dyn_sql || ' SET ' || G_ASSEMBLY_ITEM_ID || ' = ';
963 l_dyn_sql := l_dyn_sql || ' ( ';
964 l_dyn_sql := l_dyn_sql || ' SELECT inventory_item_id ';
968 l_dyn_sql := l_dyn_sql || ' ) ';
965 l_dyn_sql := l_dyn_sql || ' FROM mtl_system_items_vl mvll ';
966 l_dyn_sql := l_dyn_sql || ' WHERE mvll.concatenated_segments = EBI.'|| l_parent_column;
967 l_dyn_sql := l_dyn_sql || ' AND mvll.organization_id = EBI.INSTANCE_PK2_VALUE';
969 l_dyn_sql := l_dyn_sql || 'WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
970 l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
971
972 Write_Debug('Assembly Item Id Conversion-->' || l_dyn_sql);
973
974 EXECUTE IMMEDIATE l_dyn_sql USING p_Resultfmt_Usage_Id;
975
976 -----------------------------------------------------
977 -- Populate BillSequenceId to Instance PK4
978 -----------------------------------------------------
979 l_dyn_sql := '';
980 l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
981 l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK4_VALUE = ';
982 l_dyn_sql := l_dyn_sql || ' ( ';
983 l_dyn_sql := l_dyn_sql || ' SELECT bill_sequence_id ';
984 l_dyn_sql := l_dyn_sql || ' FROM bom_structures_b bsb, mtl_system_items_vl mvll ';
985 l_dyn_sql := l_dyn_sql || ' WHERE mvll.concatenated_segments = EBI.'|| l_parent_column;
986 l_dyn_sql := l_dyn_sql || ' AND mvll.organization_id = EBI.INSTANCE_PK2_VALUE';
990 l_dyn_sql := l_dyn_sql || ' ) ';
987 l_dyn_sql := l_dyn_sql || ' AND bsb.assembly_item_id = mvll.inventory_item_id';
988 l_dyn_sql := l_dyn_sql || ' AND bsb.organization_id = mvll.organization_id';
989 l_dyn_sql := l_dyn_sql || ' AND NVL(bsb.alternate_bom_designator,Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY'')) = EBI.' || G_INTF_STRUCT_NAME ;
991 l_dyn_sql := l_dyn_sql || 'WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
992 l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
993
994 Write_Debug('Bill Sequence Id Conversion-->' || l_dyn_sql);
995
996 EXECUTE IMMEDIATE l_dyn_sql USING p_Resultfmt_Usage_Id;
997
998 --------------------------------------------------------------
999 -- Resolve user time zone conversions
1000 fnd_date_tz.init_timezones_for_fnd_date;
1001 IF (l_eff_date_col_name IS NOT NULL OR l_dis_date_col_name IS NOT NULL OR l_imp_date_col_name IS NOT NULL) THEN
1002 l_dyn_sql := '';
1003 l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI SET';
1004 IF l_eff_date_col_name IS NOT NULL THEN
1005 l_dyn_sql := l_dyn_sql || ' EBI.'|| l_eff_date_col_name;
1006 l_dyn_sql := l_dyn_sql || ' = decode(EBI.'|| l_eff_date_col_name || ', NULL, NULL,';
1007 l_dyn_sql := l_dyn_sql || ' to_char(BOM_BULKLOAD_PVT_PKG.displayDT_to_date(EBI.' || l_eff_date_col_name;
1008 l_dyn_sql := l_dyn_sql || ' ,2),''DD-MON-YYYY HH24:MI:SS'')) ' ; -- Changed for bug 11735632
1009 END IF;
1010 IF l_dis_date_col_name IS NOT NULL THEN
1011 IF l_eff_date_col_name IS NOT NULL THEN
1012 l_dyn_sql := l_dyn_sql || ' , ' ;
1013 END IF;
1014 l_dyn_sql := l_dyn_sql || ' EBI.'|| l_dis_date_col_name;
1015 l_dyn_sql := l_dyn_sql || ' = decode(EBI.'|| l_dis_date_col_name || ', NULL, NULL,';
1016 l_dyn_sql := l_dyn_sql || ' to_char(BOM_BULKLOAD_PVT_PKG.displayDT_to_date(EBI.' || l_dis_date_col_name;
1017 l_dyn_sql := l_dyn_sql || ' ,2),''DD-MON-YYYY HH24:MI:SS'')) ' ; -- Changed for bug 11735632
1018 END IF;
1019 IF l_imp_date_col_name IS NOT NULL THEN
1020 IF (l_eff_date_col_name IS NOT NULL OR l_dis_date_col_name IS NOT NULL) THEN
1021 l_dyn_sql := l_dyn_sql || ' , ' ;
1022 END IF;
1023 l_dyn_sql := l_dyn_sql || ' EBI.'|| l_imp_date_col_name;
1024 l_dyn_sql := l_dyn_sql || ' = decode(EBI.'|| l_imp_date_col_name || ', NULL, NULL,';
1025 l_dyn_sql := l_dyn_sql || ' to_char(BOM_BULKLOAD_PVT_PKG.displayDT_to_date(EBI.' || l_imp_date_col_name;
1026 l_dyn_sql := l_dyn_sql || ' ,2),''DD-MON-YYYY HH24:MI:SS'')) ' ;
1027 END IF;
1028
1029 l_dyn_sql := l_dyn_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1030 l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
1031 Write_Debug(' TIMEZONE conversion stuff ' || l_dyn_sql);
1032
1033 IF l_debug = 'Y' THEN
1034 FND_FILE.PUT_LINE( FND_FILE.LOG,' TIMEZONE conversion stuff ' || l_dyn_sql);
1035 END IF;
1036
1037 EXECUTE IMMEDIATE l_dyn_sql USING p_Resultfmt_Usage_Id;
1038 END IF;
1039 --------------------------------------------------------------
1040
1041 -----------------------------------------------------
1042 -- Populate ComponentSequenceId to Instance PK5
1043 -----------------------------------------------------
1044 IF l_eff_date_col_name IS NOT NULL AND l_oper_seq_col_name IS NOT NULL AND l_oper_seq_col_name <> '' AND l_eff_date_col_name <> '' THEN
1045 l_dyn_sql := '';
1046 l_dyn_sql := 'UPDATE EGO_BULKLOAD_INTF EBI';
1047 l_dyn_sql := l_dyn_sql || ' SET INSTANCE_PK5_VALUE = ';
1048 l_dyn_sql := l_dyn_sql || ' ( ';
1049 l_dyn_sql := l_dyn_sql || ' SELECT COMPONENT_SEQUENCE_ID ';
1050 l_dyn_sql := l_dyn_sql || ' FROM bom_components_b BCB ';
1051 l_dyn_sql := l_dyn_sql || ' WHERE BCB.bill_sequence_id = EBI.INSTANCE_PK4_VALUE';
1052 l_dyn_sql := l_dyn_sql || ' AND BCB.component_item_id = EBI.INSTANCE_PK1_VALUE';
1053 l_dyn_sql := l_dyn_sql || ' AND BCB.operation_seq_num = EBI.'|| l_oper_seq_col_name;
1054 l_dyn_sql := l_dyn_sql || ' AND BCB.effectivity_date = EBI.'|| l_eff_date_col_name;
1055 l_dyn_sql := l_dyn_sql || ' ) ';
1056 l_dyn_sql := l_dyn_sql || 'WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
1057 l_dyn_sql := l_dyn_sql || ' AND PROCESS_STATUS = 1 ';
1058
1059 Write_Debug('Component Sequence Id Conversion-->' || l_dyn_sql);
1060
1061 EXECUTE IMMEDIATE l_dyn_sql USING p_Resultfmt_Usage_Id;
1062 END IF;
1063 -- end Added by hgelli for supporting import formats
1064
1065 -- Process The Rows for BOM BO Header For Create and Update.
1066 l_dyn_sql_insert := '';
1067 l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (BATCH_ID';
1068 l_dyn_sql_insert := l_dyn_sql_insert || ' , SOURCE_SYSTEM_REFERENCE ';
1069 l_dyn_sql_insert := l_dyn_sql_insert || ' , SOURCE_SYSTEM_REFERENCE_DESC ';
1070 l_dyn_sql_insert := l_dyn_sql_insert || ' , REQUEST_ID, Transaction_Type ';
1071 l_dyn_sql_insert := l_dyn_sql_insert || ' , Transaction_Id, Process_Flag, Item_Number ';
1072 l_dyn_sql_insert := l_dyn_sql_insert || ' , Organization_Code, Alternate_Bom_Designator, Structure_Type_Name, Effectivity_Control, Is_Preferred, assembly_type, Revision) ';
1073
1074 l_dyn_sql_select := '';
1075 l_dyn_sql_select := l_dyn_sql_select || ' SELECT ' || P_BATCH_ID || ', C_FIX_COLUMN12, C_FIX_COLUMN13 ';
1079 || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1076 l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, Transaction_Type, Transaction_Id, 1, ' || l_item_col_name;
1077 l_dyn_sql_select := l_dyn_sql_select || ' , ' || G_INTF_ORG_CODE;
1078 l_dyn_sql_select := l_dyn_sql_select || ' , DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
1080 ||' :1 ),'|| G_INTF_STRUCT_NAME|| ')'
1081 || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
1082 || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1083 ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')' || ')';
1084 l_dyn_sql_select := l_dyn_sql_select || ' , ' || 'DECODE(' || G_INTF_STR_TYPE_NAME
1085 ||',null,(SELECT BSTV.structure_type_name from bom_structure_types_vl BSTV,ego_import_option_sets EIOS WHERE EIOS.batch_id = '
1086 || ' :3 AND BSTV.structure_type_id = EIOS.structure_type_id ),' || G_INTF_STR_TYPE_NAME || ' )';
1087 l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Fnd_Lookup_Values WHERE Lookup_Type = ''BOM_EFFECTIVITY_CONTROL'' AND LANGUAGE=USERENV(''LANG'') AND Meaning=' || G_INTF_EFFEC_CONTROL || ')';
1088 l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Fnd_Lookup_Values WHERE Lookup_Type = ''EGO_YES_NO'' AND LANGUAGE=USERENV(''LANG'') AND Meaning = ' || G_INTF_IS_PREFERRED || ')';
1089 l_dyn_sql_select := l_dyn_sql_select || ' , (SELECT Lookup_Code FROM Mfg_Lookups WHERE Lookup_Type = ''BOM_ASSEMBLY_TYPE'' AND Meaning=' || G_INTF_ASSEMBLY_TYPE || ')';
1090 l_dyn_sql_select := l_dyn_sql_select || ' , ' || G_INTF_REVISION || ' ';
1091 l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF WHERE RESULTFMT_USAGE_ID = :4 ';
1092 --Sreejith
1093 if (p_is_pdh_batch = 'Y') then
1094 l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NULL ';
1095 else
1096 l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || G_INTF_SRCSYS_PARENT || ' IS NULL ';
1097 end if;
1098
1099 l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
1100
1101 IF l_debug = 'Y' THEN
1102 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering First SQL 1-->' || l_dyn_sql);
1103 END IF;
1104
1105 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id,p_batch_id,p_batch_id,p_resultfmt_usage_id;
1106
1107 -- Header Data Ready
1108
1109 -- Process Components for CREATE/ADD
1110 -- Create Structure Header record if that is not available.
1111 -- Comment this block if it is not required.
1112 l_dyn_sql_cursor := '';
1113 l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT Distinct ' ;
1114 l_dyn_sql_cursor := l_dyn_sql_cursor || G_INTF_SRCSYS_PARENT || ' , ' || l_parent_column ;
1115 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_ORG_CODE;
1116 l_dyn_sql_cursor := l_dyn_sql_cursor || ', DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
1117 || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1118 || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
1119 || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1120 ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')'
1121 || ')';
1122 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || 'DECODE(' || G_INTF_STR_TYPE_NAME
1123 ||',null,(SELECT BSTV.structure_type_name from bom_structure_types_vl BSTV,ego_import_option_sets EIOS WHERE EIOS.batch_id = '
1124 || ' :3 AND BSTV.structure_type_id = EIOS.structure_type_id ),' || G_INTF_STR_TYPE_NAME || ' )';
1125 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_EFFEC_CONTROL;
1126 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_IS_PREFERRED;
1127 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_ASSEMBLY_TYPE;
1128 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_PARENT_REVISION;
1129 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , Transaction_Id ';
1130 l_dyn_sql_cursor := l_dyn_sql_cursor || ' FROM EGO_BULKLOAD_INTF WHERE Process_Status = 1';
1131 l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND Resultfmt_Usage_Id = :4 ';
1132 if (p_is_pdh_batch = 'Y') then
1133 l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND ' || l_parent_column || ' IS NOT NULL ';
1134 else
1135 l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND ' || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
1136 end if;
1140 IF G_INTF_ASSEMBLY_TYPE IS NOT NULL THEN
1137 l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_CREATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_ADD || ''' ';
1138 l_dyn_sql_cursor := l_dyn_sql_cursor || ' OR UPPER(Transaction_Type) = ''' || G_TXN_SYNC || ''' )';
1139
1141 l_dyn_sql_cursor := l_dyn_sql_cursor || ' ORDER BY ' || G_INTF_ASSEMBLY_TYPE || ' ';
1142 END IF;
1143
1144 IF l_debug = 'Y' THEN
1145 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 2.1-->' || l_dyn_sql_cursor);
1146 END IF;
1147
1148
1149 l_cursor_select := Dbms_Sql.Open_Cursor;
1150 Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);
1151 Dbms_Sql.Define_Column(l_cursor_select, 1, L_SRCSYS_PARENT,3000);
1152 Dbms_Sql.Define_Column(l_cursor_select, 2, L_PARENT_NAME, 3000);
1153 Dbms_Sql.Define_Column(l_cursor_select, 3, L_ORGANIZATION_CODE, 10);
1154 Dbms_Sql.Define_Column(l_cursor_select, 4, L_STRUCTURE_NAME, 240);
1155 Dbms_Sql.Define_Column(l_cursor_select, 5, L_STR_TYPE_NAME, 240);
1156 Dbms_Sql.Define_Column(l_cursor_select, 6, L_EFFEC_CONTROL, 240);
1157 Dbms_Sql.Define_Column(l_cursor_select, 7, L_IS_PREF_MEANING, 80);
1158 Dbms_Sql.Define_Column(l_cursor_select, 8, L_ASSTYPE_MEANING, 80);
1159 Dbms_Sql.Define_Column(l_cursor_select, 9, L_PARENT_REVISION, 80);
1160 Dbms_Sql.Define_Column(l_cursor_select, 10, L_TRANSACTION_ID);
1161
1162 Dbms_Sql.Bind_Variable(l_cursor_select,':1', p_batch_id);
1163 Dbms_Sql.Bind_Variable(l_cursor_select,':2', p_batch_id);
1164 Dbms_Sql.Bind_Variable(l_cursor_select,':3', p_batch_id);
1165 Dbms_Sql.Bind_Variable(l_cursor_select,':4', p_resultfmt_usage_id);
1166
1167 IF l_debug = 'Y' THEN
1168 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 2.2-->' || p_resultfmt_usage_id);
1169 END IF;
1170
1171 l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);
1172
1173 IF l_debug = 'Y' THEN
1177 LOOP
1174 FND_FILE.PUT_LINE( FND_FILE.LOG,'SUCCESS');
1175 END IF;
1176 i := 1;
1178 IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
1179 Dbms_Sql.Column_Value(l_cursor_select,1,L_SRCSYS_PARENT);
1180 Dbms_Sql.Column_Value(l_cursor_select,2,L_PARENT_NAME);
1181 Dbms_Sql.Column_Value(l_cursor_select,3,L_ORGANIZATION_CODE);
1182 Dbms_Sql.Column_Value(l_cursor_select,4,L_STRUCTURE_NAME);
1183 Dbms_Sql.Column_Value(l_cursor_select,5,L_STR_TYPE_NAME);
1184 Dbms_Sql.Column_Value(l_cursor_select,6,L_EFFEC_CONTROL);
1185 Dbms_Sql.Column_Value(l_cursor_select,7,L_IS_PREF_MEANING);
1186 Dbms_Sql.Column_Value(l_cursor_select,8,L_ASSTYPE_MEANING);
1187 Dbms_Sql.Column_Value(l_cursor_select,9,L_PARENT_REVISION);
1188 Dbms_Sql.Column_Value(l_cursor_select,10,L_TRANSACTION_ID);
1189
1190 IF L_EFFEC_CONTROL IS NOT NULL
1191 THEN
1192 SELECT
1193 Lookup_Code
1194 INTO
1195 l_eff_ctrl
1196 FROM
1197 Fnd_Lookup_Values
1198 WHERE
1199 Lookup_Type = 'BOM_EFFECTIVITY_CONTROL' AND LANGUAGE=USERENV('LANG') AND Meaning=L_EFFEC_CONTROL;
1200 ELSE
1201 l_eff_ctrl := null;
1202 END IF;
1203 IF L_IS_PREF_MEANING IS NOT NULL
1204 THEN
1205 SELECT
1206 Lookup_Code
1207 INTO
1208 l_is_preferred
1209 FROM
1210 Fnd_Lookup_Values
1211 WHERE
1212 Lookup_Type = 'EGO_YES_NO' AND LANGUAGE=USERENV('LANG') AND Meaning=L_IS_PREF_MEANING;
1213 ELSE
1214 l_is_preferred := null;
1215 END IF;
1216
1217 IF L_ASSTYPE_MEANING IS NOT NULL
1218 THEN
1219 SELECT
1220 Lookup_Code
1221 INTO
1222 l_assemblytype
1223 FROM
1224 Mfg_Lookups
1225 WHERE
1229 END IF;
1226 Lookup_Type = 'BOM_ASSEMBLY_TYPE' AND Meaning=L_ASSTYPE_MEANING;
1227 ELSE
1228 l_assemblytype := 2;
1230
1231 --bug 4777796 adding the structure type id also in the Get_Bill_Sequence; removed logic
1232
1233 l_Org_Id := Get_Organization_Id(L_ORGANIZATION_CODE);
1234 l_Inv_Item_Id := Component_Item(l_Org_Id,L_PARENT_NAME);
1235 l_Bill_Seq_Id := Bill_Sequence(l_Inv_Item_Id,L_STRUCTURE_NAME,l_Org_Id);
1236
1237 IF(l_Bill_Seq_Id IS NULL) THEN
1238 INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
1239 BATCH_ID,
1240 REQUEST_ID,
1241 TRANSACTION_TYPE,
1242 TRANSACTION_ID,
1243 PROCESS_FLAG,
1244 ITEM_NUMBER,
1245 ORGANIZATION_CODE,
1246 ALTERNATE_BOM_DESIGNATOR,
1247 STRUCTURE_TYPE_NAME,
1248 EFFECTIVITY_CONTROL,
1249 IS_PREFERRED ,
1250 assembly_type,
1251 REVISION,
1252 SOURCE_SYSTEM_REFERENCE )
1253 select
1254 p_batch_id,
1255 G_REQUEST_ID,
1256 G_TXN_SYNC,
1257 L_TRANSACTION_ID,
1258 1,
1259 L_PARENT_NAME,
1260 L_ORGANIZATION_CODE,
1261 L_STRUCTURE_NAME,
1262 L_STR_TYPE_NAME,
1263 l_eff_ctrl,
1264 l_is_preferred,
1265 l_assemblytype,
1266 L_PARENT_REVISION,
1267 L_SRCSYS_PARENT from dual
1268 where not exists
1269 (select 'X' from bom_bill_of_mtls_interface
1270 where
1271 batch_id = p_batch_id
1272 and request_id = g_request_id
1273 and process_flag = 1
1274 and ( (ITEM_NUMBER IS NOT NULL AND ITEM_NUMBER = L_PARENT_NAME) OR (SOURCE_SYSTEM_REFERENCE IS NOT NULL AND SOURCE_SYSTEM_REFERENCE=L_SRCSYS_PARENT))
1275 and ORGANIZATION_CODE = L_ORGANIZATION_CODE
1276 and nvl(ALTERNATE_BOM_DESIGNATOR,'000') = nvl(L_STRUCTURE_NAME,'000')
1277 and nvl(STRUCTURE_TYPE_NAME,'000') = nvl(L_STR_TYPE_NAME,'000')
1278 and nvl(EFFECTIVITY_CONTROL,'000') = nvl(l_eff_ctrl,'000') );
1279
1280 ELSE
1281 INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
1282 BATCH_ID,
1283 REQUEST_ID,
1284 TRANSACTION_TYPE,
1285 TRANSACTION_ID,
1286 PROCESS_FLAG,
1287 ITEM_NUMBER,
1288 ORGANIZATION_CODE,
1289 ALTERNATE_BOM_DESIGNATOR,
1290 STRUCTURE_TYPE_NAME,
1291 EFFECTIVITY_CONTROL,
1292 IS_PREFERRED ,
1293 assembly_type,
1294 REVISION,
1295 SOURCE_SYSTEM_REFERENCE )
1296 select
1297 p_batch_id,
1298 G_REQUEST_ID,
1299 G_TXN_NO_OP,
1300 L_TRANSACTION_ID,
1301 1,
1302 L_PARENT_NAME,
1303 L_ORGANIZATION_CODE,
1304 L_STRUCTURE_NAME,
1305 L_STR_TYPE_NAME,
1306 l_eff_ctrl,
1307 l_is_preferred,
1308 l_assemblytype,
1309 L_PARENT_REVISION,
1310 L_SRCSYS_PARENT from dual
1311 where not exists
1312 (select 'X' from bom_bill_of_mtls_interface
1313 where
1314 batch_id = p_batch_id
1315 and request_id = g_request_id
1316 and process_flag = 1
1317 and ( (ITEM_NUMBER IS NOT NULL AND ITEM_NUMBER = L_PARENT_NAME) OR (SOURCE_SYSTEM_REFERENCE IS NOT NULL AND SOURCE_SYSTEM_REFERENCE=L_SRCSYS_PARENT))
1318 and ORGANIZATION_CODE = L_ORGANIZATION_CODE
1319 and nvl(ALTERNATE_BOM_DESIGNATOR,'000') = nvl(L_STRUCTURE_NAME,'000')
1320 and nvl(STRUCTURE_TYPE_NAME,'000') = nvl(L_STR_TYPE_NAME,'000')
1321 and nvl(EFFECTIVITY_CONTROL,'000') = nvl(l_eff_ctrl,'000') );
1322 END IF;
1323 i := i+ 1;
1324 ELSE
1325 EXIT;
1326 END IF;
1327 END LOOP;
1328
1329 /* Header complete */
1330 --poplulating to see see errors
1331 -- Process Components for UPDATE
1332 l_dyn_sql_insert := '';
1333 l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
1334 l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
1335 l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
1336 l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
1337 l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, PARENT_REVISION_CODE, ';
1338 l_dyn_sql_select := '';
1339 l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
1340 l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
1341 l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
1342 l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
1343 || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1344 || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
1348
1345 || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1346 ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
1347 l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ' || G_INTF_PARENT_REVISION || ', ';
1349 FOR i IN 1..l_prod_col_name_tbl.COUNT LOOP
1350 IF (l_bom_col_name(i) IS NOT NULL) THEN
1351 -- For Effectivity_Date, Operation_Seq_Num, and From_End_Unit_Number changes
1352 -- we need to update the New_Effectivity_Date, New_Operation_Seq_Num,
1353 -- and New_From_End_Unit_Number
1354
1355
1356 IF (l_bom_col_name(i) = G_EFFECTIVITY_DATE) THEN
1357 l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_EFFECTIVITY_DATE,';
1358 -- If the new effectivity date is same as the effectivity date in the database
1359 -- then insert null other wise insert new value into the interface table.
1360 --Bug 12323522
1361 --Note: The above algorithm resulted int Sysdate for Effectivity_Date if the new effectivigy date is same as the effectivity date in DB.
1362 --So modify the logic as follows
1363 -- Bug 12323522 Commented out start
1364 --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 || ')';
1365 --l_str := l_str || ',' || l_intf_col_name_tbl(i) ||',TO_DATE(NULL, ''DD-MON-YYYY HH24:MI:SS'')';
1366 --l_str := l_str || ',TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS'')),';
1367 -- Bug 12323522 Commented out end
1368 --Bug 12323522 Start
1369 l_str := 'NVL2( ' || l_intf_col_name_tbl(i) ;
1370 l_str := l_str || ', TO_DATE(' || l_intf_col_name_tbl(i) || ', ''DD-MON-YYYY HH24:MI:SS'')';
1371 l_str := l_str || ', ( SELECT Effectivity_Date FROM Bom_inventory_Components WHERE Component_Sequence_Id = ' || G_INTF_COMP_SEQ_ID || ')),';
1372 --Bug 12323522 End
1373 l_dyn_sql_select := l_dyn_sql_select || l_str;
1374 ELSE
1375 IF (l_bom_col_name(i) = G_OPERATION_SEQ_NUM) THEN
1376 l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_OPERATION_SEQ_NUM,';
1377 ELSIF (l_bom_col_name(i) = G_FROM_END_ITEM_UNIT_NUMBER) THEN
1378 l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_FROM_END_ITEM_UNIT_NUMBER,';
1379 ELSE
1380 l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
1381 END IF;
1382 -- As date values are coming as character values convert them as dates.
1383 IF ((l_bom_col_type(i) IS NOT NULL) AND (l_bom_col_type(i) = 'DATETIME')) THEN
1384 l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
1385 ELSIF (l_lookup_type(i) IS NOT NULL) THEN
1386 l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
1387 l_str := l_str || ' LOOKUP_TYPE = ''' || l_lookup_type(i) ||''' AND LANGUAGE = USERENV(''LANG'') AND MEANING=' || l_intf_col_name_tbl(i) ||' ),';
1388 l_dyn_sql_select := l_dyn_sql_select || l_str;
1389 ELSE
1390 l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
1391 END IF;
1392 END IF;
1393
1394 END IF;
1395 END LOOP;
1396
1397 l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
1398 l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
1399 l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
1400 l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
1401 l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
1402 l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
1403
1404 if (p_is_pdh_batch = 'Y') then
1405 l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
1406 else
1407 l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
1408 end if;
1409 -- Sreejith
1410
1411 l_dyn_sql_select := l_dyn_sql_select || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_UPDATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_SYNC || ''') ';
1412 -- l_dyn_sql_select := l_dyn_sql_select || ' AND Transaction_Type = ''' || G_TXN_UPDATE || ''' ';
1413
1414 l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
1415
1416 IF l_debug = 'Y' THEN
1417 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 2 -->' || l_dyn_sql);
1418 END IF;
1419
1420
1421 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id,p_batch_id,p_resultfmt_usage_id;
1422 -- End of Process Components for UPDATE
1423 IF l_debug = 'Y' THEN
1424 FND_FILE.PUT_LINE( FND_FILE.LOG,'Executed Succesfully 2');
1425 END IF;
1426
1427 IF l_debug = 'Y' THEN
1428 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 3-->' || l_dyn_sql);
1429 END IF;
1430
1431
1432 Dbms_Sql.Close_Cursor(l_cursor_select);
1433 -- End of Creating Structure Header record if that is not available.
1434
1435 l_dyn_sql_insert := '';
1436 l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
1437 l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
1438 l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
1439 l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
1440 l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, PARENT_REVISION_CODE, ';
1441 l_dyn_sql_select := '';
1447 || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
1442 l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
1443 l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
1444 l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, ''' || G_TXN_CREATE || ''' , Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
1445 l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
1446 || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1448 || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1449 ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
1450 l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ' || G_INTF_PARENT_REVISION || ', ';
1451
1452 FOR i IN 1..l_prod_col_name_tbl.COUNT LOOP
1453 IF (l_bom_col_name(i) IS NOT NULL) THEN
1454 l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
1455
1456 -- As date values are coming as character values convert them as dates.
1457 IF ((l_bom_col_type(i) IS NOT NULL) AND (l_bom_col_type(i) = 'DATETIME')) THEN
1458 l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
1459 ELSIF (l_lookup_type(i) IS NOT NULL) THEN
1460 l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
1461 l_str := l_str || ' LOOKUP_TYPE = ''' || l_lookup_type(i) ||''' AND LANGUAGE = USERENV(''LANG'') AND MEANING=' || l_intf_col_name_tbl(i) ||' ),';
1462 l_dyn_sql_select := l_dyn_sql_select || l_str;
1463 ELSE
1464 l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
1465 END IF;
1466
1467 --Bug 12323522 start
1468 --Put NEW_EFFECTIVITY_DATE in addition to EFFECTIVITY_DATE
1469 IF (l_bom_col_name(i) = G_EFFECTIVITY_DATE ) THEN
1470 l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_EFFECTIVITY_DATE,';
1471 l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
1472 END IF;
1473 --Bug 12323522 end
1474
1475 END IF;
1476 END LOOP;
1477
1478 l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
1479 l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
1480 l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
1481 l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
1482 l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
1483 l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
1484 -- Sreejith
1485 if (p_is_pdh_batch = 'Y') then
1486 l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
1487 else
1488 l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
1489 end if;
1490 -- Sreejith
1491
1492 l_dyn_sql_select := l_dyn_sql_select || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_CREATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_ADD || ''') ';
1493
1494 -- l_dyn_sql_select := l_dyn_sql_select || ' AND (Transaction_Type = ''' || G_TXN_CREATE || ''' OR Transaction_Type = ''' || G_TXN_ADD || ''' ';
1495 -- l_dyn_sql_select := l_dyn_sql_select || ' OR Transaction_Type = ''' || G_TXN_SYNC || ''' )';
1496
1497
1498 l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
1499
1500 IF l_debug = 'Y' THEN
1501 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 4-->' || l_dyn_sql);
1502 END IF;
1503
1507 IF l_debug = 'Y' THEN
1504 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id,p_batch_id,p_resultfmt_usage_id;
1505 -- End of Process Components for CREATE/ADD
1506
1508 FND_FILE.PUT_LINE( FND_FILE.LOG,'Executed Succesfully 4');
1509 END IF;
1510
1511 -- Start of process components for Delete
1512 l_dyn_sql_insert := '';
1513 l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( BATCH_ID, ';
1514 l_dyn_sql_insert := l_dyn_sql_insert || ' COMP_SOURCE_SYSTEM_REFERENCE, COMP_SOURCE_SYSTEM_REFER_DESC,';
1515 l_dyn_sql_insert := l_dyn_sql_insert || ' PARENT_SOURCE_SYSTEM_REFERENCE, REQUEST_ID, Transaction_Type,';
1516 l_dyn_sql_insert := l_dyn_sql_insert || ' Transaction_Id, Process_Flag, ';
1517 l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';
1518 l_dyn_sql_insert := l_dyn_sql_insert || 'DELETE_GROUP_NAME, DG_DESCRIPTION, ';
1519 l_dyn_sql_select := '';
1520 l_dyn_sql_select := l_dyn_sql_select || 'SELECT ' || P_BATCH_ID || ' , ' || G_INTF_SRCSYS_COMPONENT ;
1521 l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_SRCSYS_DESCRIPTION || ' , ' || G_INTF_SRCSYS_PARENT ;
1522 l_dyn_sql_select := l_dyn_sql_select || ' , REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';
1523 l_dyn_sql_select := l_dyn_sql_select || ' DECODE(' || 'DECODE(' || G_INTF_STRUCT_NAME
1524 || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1525 || ' :1 ),'|| G_INTF_STRUCT_NAME|| ')' || ', Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,'
1526 || 'DECODE(' || G_INTF_STRUCT_NAME || ',null,(select decode(structure_name,BOM_GLOBALS.GET_PRIMARY_UI,null,structure_name) from ego_import_option_sets where batch_id = '
1527 ||' :2 ),'|| G_INTF_STRUCT_NAME|| ')) ,';
1528 l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';
1529 l_dyn_sql_select := l_dyn_sql_select || '''' || G_DEL_GROUP_NAME || ''', ''' || G_DEL_GROUP_DESC || ''', ' ;
1530
1531 FOR i IN 1..l_prod_col_name_tbl.COUNT LOOP
1532 IF (l_bom_col_name(i) IS NOT NULL) THEN
1533 l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';
1534
1535 -- As date values are coming as character values convert them as dates.
1536 IF ((l_bom_col_type(i) IS NOT NULL) AND (l_bom_col_type(i) = 'DATETIME')) THEN
1537 l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';
1538 ELSIF (l_lookup_type(i) IS NOT NULL) THEN
1539 l_str := '(SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES WHERE ';
1540 l_str := l_str || ' LOOKUP_TYPE = ''' || l_lookup_type(i) ||''' AND LANGUAGE = USERENV(''LANG'') AND MEANING=' || l_intf_col_name_tbl(i) ||' ),';
1541 l_dyn_sql_select := l_dyn_sql_select || l_str;
1542 ELSE
1543 l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';
1544 END IF;
1545
1546 END IF;
1547 END LOOP;
1548
1549 l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);
1553 l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :3 ';
1550 l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);
1551 l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';
1552 l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';
1554 l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ';
1555 -- Sreejith
1556 if (p_is_pdh_batch = 'Y') then
1557 l_dyn_sql_select := l_dyn_sql_select || l_parent_column || ' IS NOT NULL ';
1558 else
1559 l_dyn_sql_select := l_dyn_sql_select || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ';
1560 end if;
1561 -- Sreejith
1562
1563 l_dyn_sql_select := l_dyn_sql_select || ' AND UPPER(Transaction_Type) = ''' || G_TXN_DELETE || ''' ';
1564
1565 l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;
1566
1567 IF l_debug = 'Y' THEN
1568 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering SQL 5-->' || l_dyn_sql);
1569 END IF;
1570
1571 EXECUTE IMMEDIATE l_dyn_sql USING p_batch_id,p_batch_id,p_resultfmt_usage_id;
1572 -- End of process components for Delete
1573
1574 IF l_debug = 'Y' THEN
1575 FND_FILE.PUT_LINE( FND_FILE.LOG,'Before UPdate of AssemblyType');
1576 END IF;
1577
1578 -- Updateing the assembly_type to 2 for BOM_header
1579 UPDATE BOM_BILL_OF_MTLS_INTERFACE SET assembly_type = 2
1580 WHERE assembly_type IS NULL
1581 AND batch_id = p_batch_id;
1582
1583 IF l_debug = 'Y' THEN
1584 FND_FILE.PUT_LINE( FND_FILE.LOG,'After UPdate of AssemblyType');
1585 END IF;
1586
1590 FOR C_BOM_BILL_PRIMARY_REC IN C_BOM_BILL_PRIMARY
1587 /* Commenting the primary creation, as now we can create eng alternates with out primary.
1588 -- iNSERT ROWS FOR PRIMARY ALTERNATE FOR creating primary bom if it doesn't exist
1589 -- Also set the txn id for all those rows
1591 (
1592 G_REQUEST_ID
1593 )
1594 LOOP
1595 INSERT INTO BOM_BILL_OF_MTLS_INTERFACE
1596 (
1597 ASSEMBLY_ITEM_ID,
1598 ORGANIZATION_ID,
1599 ASSEMBLY_TYPE,
1600 PROCESS_FLAG,
1601 ORGANIZATION_CODE,
1602 COMMON_ORG_CODE,
1603 ITEM_NUMBER,
1604 IMPLEMENTATION_DATE,
1605 ALTERNATE_BOM_DESIGNATOR,
1606 TRANSACTION_TYPE,
1607 REQUEST_ID)
1608 VALUES
1609 (
1610 C_BOM_BILL_PRIMARY_REC.ASSEMBLY_ITEM_ID,
1611 C_BOM_BILL_PRIMARY_REC.ORGANIZATION_ID,
1612 C_BOM_BILL_PRIMARY_REC.ASSEMBLY_TYPE,
1613 C_BOM_BILL_PRIMARY_REC.PROCESS_FLAG,
1614 C_BOM_BILL_PRIMARY_REC.ORGANIZATION_CODE,
1615 C_BOM_BILL_PRIMARY_REC.COMMON_ORG_CODE,
1616 C_BOM_BILL_PRIMARY_REC.ITEM_NUMBER,
1617 C_BOM_BILL_PRIMARY_REC.IMPLEMENTATION_DATE,
1618 NULL,
1619 G_TXN_CREATE,
1620 G_REQUEST_ID);
1621 END LOOP;
1622 */
1623
1624 UPDATE BOM_BILL_OF_MTLS_INTERFACE
1625 SET Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
1626 WHERE REQUEST_ID = G_REQUEST_ID AND PROCESS_FLAG = 1
1627 AND Transaction_Id IS NULL;
1628
1629
1630 --Setting the transaction_id in the ego_bulkload_intf to be the same for multi row comp attr
1631
1632 IF l_eff_date_col_name IS NOT NULL AND l_oper_seq_col_name IS NOT NULL AND l_item_seq_col_name IS NOT NULL THEN
1633
1634 l_upd_sql := ' UPDATE EGO_BULKLOAD_INTF EBI1 ' ||
1635 ' SET EBI1.transaction_id = ( SELECT EBI2.transaction_id ' ||
1636 ' FROM EGO_BULKLOAD_INTF EBI2 ' ||
1637 ' WHERE EBI2.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI2.process_status = 1 ' ||
1638 ' AND EBI2.' || l_item_seq_col_name || ' IS NOT NULL' ;
1639 IF p_is_pdh_batch = 'Y' THEN
1640 l_upd_sql := l_upd_sql || ' AND EBI2.' || l_parent_column || ' = EBI1.' || l_parent_column ||
1641 ' AND EBI2.' || l_parent_column || ' IS NOT NULL ' ||
1642 ' AND EBI2.' || l_item_col_name || ' IS NOT NULL ' ||
1643 ' AND EBI2.' || l_item_col_name || ' = EBI1.' || l_item_col_name ;
1644 ELSE
1645 l_upd_sql := l_upd_sql || ' AND EBI2.' || G_INTF_SRCSYS_PARENT || ' = EBI1.' || G_INTF_SRCSYS_PARENT ||
1646 ' AND EBI2.' || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ' ||
1647 ' AND EBI2.' || G_INTF_SRCSYS_COMPONENT || ' IS NOT NULL ' ||
1648 ' AND EBI2.' || G_INTF_SRCSYS_COMPONENT || ' = EBI1.' || G_INTF_SRCSYS_COMPONENT ;
1649 END IF;
1650
1651 l_upd_sql := l_upd_sql || ') WHERE EBI1.resultfmt_usage_id = :RESULTFMT_USAGE_ID AND EBI1.process_status = 1 ' ||
1652 ' AND EBI1.' || l_item_seq_col_name || ' IS NULL ' ;
1653 IF p_is_pdh_batch = 'Y' THEN
1654 l_upd_sql := l_upd_sql || ' AND EBI1.' || l_parent_column || ' IS NOT NULL ' ||
1655 ' AND EBI1.' || l_parent_column || ' = (SELECT EBI3.' || l_parent_column ||
1656 ' FROM EGO_BULKLOAD_INTF EBI3 ' ||
1657 ' WHERE EBI3.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI3.process_status = 1 ' ||
1658 ' AND EBI3.' || l_item_seq_col_name || ' IS NOT NULL' ||
1659 ' AND EBI3.' || l_parent_column || ' = EBI1.' || l_parent_column ||
1660 ' AND EBI3.' || l_item_col_name || ' = EBI1.' || l_item_col_name ||
1661 ' ) ' ||
1662 ' AND EBI1.' || l_item_col_name || ' IS NOT NULL ' ||
1663 ' AND EBI1.' || l_item_col_name || ' = (SELECT EBI4.' || l_item_col_name ||
1664 ' FROM EGO_BULKLOAD_INTF EBI4 ' ||
1665 ' WHERE EBI4.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI4.process_status = 1 ' ||
1666 ' AND EBI4.' || l_item_seq_col_name || ' IS NOT NULL' ||
1667 ' AND EBI4.' || l_item_col_name || ' = EBI1.' || l_item_col_name ||
1668 ' AND EBI4.' || l_parent_column || ' = EBI1.' || l_parent_column ||
1669 ' ) ';
1670 ELSE
1671 l_upd_sql := l_upd_sql || ' AND EBI1.' || G_INTF_SRCSYS_PARENT || ' IS NOT NULL ' ||
1672 ' AND EBI1.' || G_INTF_SRCSYS_PARENT || ' = (SELECT EBI3.' || G_INTF_SRCSYS_PARENT ||
1673 ' FROM EGO_BULKLOAD_INTF EBI3 ' ||
1674 ' WHERE EBI3.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI3.process_status = 1 ' ||
1678 ' ) ' ||
1675 ' AND EBI3.' || l_item_seq_col_name || ' IS NOT NULL' ||
1676 ' AND EBI3.' || G_INTF_SRCSYS_PARENT || ' = EBI1.' || G_INTF_SRCSYS_PARENT ||
1677 ' AND EBI3.' || G_INTF_SRCSYS_COMPONENT || ' = EBI1.' || G_INTF_SRCSYS_COMPONENT ||
1679 ' AND EBI1.' || G_INTF_SRCSYS_COMPONENT || ' IS NOT NULL ' ||
1680 ' AND EBI1.' || G_INTF_SRCSYS_COMPONENT || ' = (SELECT EBI4.' || G_INTF_SRCSYS_COMPONENT ||
1681 ' FROM EGO_BULKLOAD_INTF EBI4 ' ||
1682 ' WHERE EBI4.resultfmt_usage_id = EBI1.resultfmt_usage_id AND EBI4.process_status = 1 ' ||
1683 ' AND EBI4.' || l_item_seq_col_name || ' IS NOT NULL' ||
1684 ' AND EBI4.' || G_INTF_SRCSYS_COMPONENT || ' = EBI1.' || G_INTF_SRCSYS_COMPONENT ||
1685 ' AND EBI4.' || G_INTF_SRCSYS_PARENT || ' = EBI1.' || G_INTF_SRCSYS_PARENT ||
1686 ' ) ';
1687 END IF;
1688
1689
1690 IF l_debug = 'Y' THEN
1691 FND_FILE.PUT_LINE( FND_FILE.LOG,'item_cole_name--' || l_item_col_name);
1692 FND_FILE.PUT_LINE( FND_FILE.LOG,'Update Sql for ego bulkload for Multi Row-->' || l_upd_sql);
1693 END IF;
1694
1695 EXECUTE IMMEDIATE l_upd_sql USING p_resultfmt_usage_id;
1696
1697 END IF;
1698
1699
1700 -- Call the load_comp_usr_attr_interface to load component user attributes
1701 load_comp_usr_attr_interface(
1702 p_resultfmt_usage_id => p_resultfmt_usage_id,
1703 p_data_set_id => p_batch_id,
1704 x_errbuff => l_errbuff,
1705 x_retcode => l_retcode
1706 );
1707
1708 -- Delete the duplicate component rows, which are populated bcos of component user attributes (multi row)
1709 -- This will be done by checking the value of EffectivityDate, OperationSequence, Item Sequence.
1710 IF l_eff_date_col_name IS NOT NULL AND l_oper_seq_col_name IS NOT NULL AND l_item_seq_col_name IS NOT NULL THEN
1711 DELETE BOM_INVENTORY_COMPS_INTERFACE
1712 WHERE PROCESS_FLAG = 1
1713 AND batch_id = p_batch_id
1714 AND (OPERATION_SEQ_NUM IS NOT NULL OR NEW_OPERATION_SEQ_NUM IS NOT NULL)
1715 AND (EFFECTIVITY_DATE IS NOT NULL OR NEW_EFFECTIVITY_DATE IS NOT NULL)
1716 AND ITEM_NUM IS NULL;
1717 END IF;
1718
1719 -- Process Reference Designators
1720 IF (G_INTF_REF_DESIG IS NOT NULL AND G_INTF_REF_DESIG <>'NULL') THEN
1721 l_dyn_sql_cursor := '';
1722 l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT ' || l_item_col_name || ',' || G_INTF_SRCSYS_COMPONENT ;
1723 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || l_parent_column || ',' || G_INTF_SRCSYS_PARENT ;
1724 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_ORG_CODE;
1725 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 || ')';
1726 l_dyn_sql_cursor := l_dyn_sql_cursor || ' , ' || G_INTF_REF_DESIG || ' , Transaction_Id , Transaction_Type ' ;
1727 l_dyn_sql_cursor := l_dyn_sql_cursor || ' FROM EGO_BULKLOAD_INTF WHERE Process_Status = 1';
1728 l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND Resultfmt_Usage_Id = :RESULTFMT_USAGE_ID ';
1729 l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND ' || l_parent_column || ' IS NOT NULL ';
1730 l_dyn_sql_cursor := l_dyn_sql_cursor || ' AND ( UPPER(Transaction_Type) = ''' || G_TXN_CREATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_ADD || ''' ';
1731 l_dyn_sql_cursor := l_dyn_sql_cursor || ' OR UPPER(Transaction_Type) = ''' || G_TXN_UPDATE || ''' OR UPPER(Transaction_Type) = ''' || G_TXN_SYNC || ''' )';
1732
1733 Write_Debug('Entering SQL R.1' || l_dyn_sql_cursor);
1734
1735 l_cursor_select := Dbms_Sql.Open_Cursor;
1736 Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);
1737 Dbms_Sql.Define_Column(l_cursor_select, 1, L_ITEM_NAME, 3000);
1738 Dbms_Sql.Define_Column(l_cursor_select, 2, L_SRCSYS_ITEM, 3000);
1739 Dbms_Sql.Define_Column(l_cursor_select, 3, L_PARENT_NAME, 3000);
1740 Dbms_Sql.Define_Column(l_cursor_select, 4, L_SRCSYS_PARENT,3000);
1741 Dbms_Sql.Define_Column(l_cursor_select, 5, L_ORGANIZATION_CODE, 10);
1742 Dbms_Sql.Define_Column(l_cursor_select, 6, L_STRUCTURE_NAME, 240);
1743 Dbms_Sql.Define_Column(l_cursor_select, 7, L_COMP_REF_DESIG, 3000);
1744 Dbms_Sql.Define_Column(l_cursor_select, 8, L_TRANSACTION_ID);
1745 Dbms_Sql.Define_Column(l_cursor_select, 9, L_TRNSACTION_TYPE, 30);
1746
1747 Dbms_Sql.Bind_Variable(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
1748
1749 Write_Debug('Entering SQL R.2' || p_resultfmt_usage_id);
1750
1751 l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);
1752
1753 Write_Debug('Success -- RegDesig Cursor execution');
1754
1755 LOOP
1756 IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
1760 Dbms_Sql.Column_Value(l_cursor_select,4,L_SRCSYS_PARENT);
1757 Dbms_Sql.Column_Value(l_cursor_select,1,L_ITEM_NAME);
1758 Dbms_Sql.Column_Value(l_cursor_select,2,L_SRCSYS_ITEM);
1759 Dbms_Sql.Column_Value(l_cursor_select,3,L_PARENT_NAME);
1761 Dbms_Sql.Column_Value(l_cursor_select,5,L_ORGANIZATION_CODE);
1762 Dbms_Sql.Column_Value(l_cursor_select,6,L_STRUCTURE_NAME);
1763 Dbms_Sql.Column_Value(l_cursor_select,7,L_COMP_REF_DESIG);
1764 Dbms_Sql.Column_Value(l_cursor_select,8,L_TRANSACTION_ID);
1765 Dbms_Sql.Column_Value(l_cursor_select,9,L_TRNSACTION_TYPE);
1766
1767
1768 getListOfRefDesigs(L_COMP_REF_DESIG, l_comp_ref_desig_tbl);
1769 FOR I IN 1..l_comp_ref_desig_tbl.COUNT LOOP
1770 INSERT INTO BOM_REF_DESGS_INTERFACE (
1771 BATCH_ID,
1772 REQUEST_ID,
1773 TRANSACTION_TYPE,
1774 TRANSACTION_ID,
1775 PROCESS_FLAG,
1776 ASSEMBLY_ITEM_NUMBER,
1777 ORGANIZATION_CODE,
1778 ALTERNATE_BOM_DESIGNATOR,
1779 COMPONENT_ITEM_NUMBER,
1780 COMP_SOURCE_SYSTEM_REFERENCE,
1781 PARENT_SOURCE_SYSTEM_REFERENCE,
1782 COMPONENT_REFERENCE_DESIGNATOR)
1783 VALUES (
1784 p_batch_id,
1785 G_REQUEST_ID,
1786 Decode(L_TRNSACTION_TYPE,G_TXN_SYNC,G_TXN_SYNC,G_TXN_UPDATE,G_TXN_SYNC,G_TXN_CREATE,G_TXN_CREATE,G_TXN_ADD,G_TXN_CREATE,L_TRNSACTION_TYPE),
1787 L_TRANSACTION_ID,
1788 1,
1789 L_PARENT_NAME,
1790 L_ORGANIZATION_CODE,
1794 L_SRCSYS_PARENT,
1791 L_STRUCTURE_NAME,
1792 L_ITEM_NAME,
1793 L_SRCSYS_ITEM,
1795 l_comp_ref_desig_tbl(I));
1796 END LOOP;
1797 ELSE
1798 EXIT;
1799 END IF;
1800 END LOOP;
1801
1802 Dbms_Sql.Close_Cursor(l_cursor_select);
1803 END IF;
1804
1805 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1806 SET(effectivity_date, operation_seq_num, from_end_item_unit_number, component_sequence_id, assembly_item_revision_code)
1807 = (SELECT Decode(effectivity_date,NULL,new_effectivity_date,effectivity_date),
1808 Decode(operation_seq_num,NULL,new_operation_seq_num,operation_seq_num),
1809 Decode(from_end_item_unit_number,NULL,new_from_end_item_unit_number,from_end_item_unit_number),
1810 component_sequence_id, parent_revision_code
1811 FROM BOM_INVENTORY_COMPS_INTERFACE BIC1
1812 WHERE BIC1.TRANSACTION_ID = BRDI.TRANSACTION_ID )
1813 WHERE
1814 BRDI.batch_id = p_batch_id
1815 AND EXISTS( Select 'X' FROM BOM_INVENTORY_COMPS_INTERFACE BIC12
1816 WHERE BIC12.TRANSACTION_ID = BRDI.TRANSACTION_ID);
1817 -- Process Reference Designators Complete
1818
1819
1820 -- Call the BOM API TO PROCESS INTERFACE TABLES
1821 /* l_err_return_code := bom_open_interface_api.import_bom
1822 ( org_id => 207 --Dummy value, all_org below carries precedence
1823 , all_org => 1
1824 , err_text => l_err_text
1825 );
1826 */
1827
1828 -- Updating the Bulkload interface rows with success.
1829 UPDATE EGO_BULKLOAD_INTF EBI
1830 SET EBI.PROCESS_STATUS = 7
1834 /* l_JCP_Id := Fnd_Request.Submit_Request(
1831 WHERE EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id AND EBI.PROCESS_STATUS = 1;
1832
1833 -- Call to launch the Java Concurrent Program
1835 application => 'BOM',
1836 program => 'BOMJCP',
1837 sub_request => FALSE,
1838 argument1 => G_REQUEST_ID);
1839
1840
1841 -- committing the changes to interface rows to reflect the changes in BOMJCP.
1842 COMMIT; */
1843 x_retcode := G_STATUS_SUCCESS;
1844
1845 IF l_debug = 'Y' THEN
1846 FND_FILE.PUT_LINE( FND_FILE.LOG,'Done Processing');
1847 END IF;
1848
1849 --Error_Handler.Write_Debug('Structure Import : UPDATE : l_err_text = ' || l_err_text);
1850
1851 -- Call completion procedure
1852 /* Structure_Intf_Proc_Complete
1853 (
1854 p_resultfmt_usage_id => p_resultfmt_usage_id
1855 ,x_errbuff => l_errbuff
1856 ,x_retcode => l_retcode
1857 );
1858 */
1859 IF l_debug = 'Y' THEN
1860 FND_FILE.PUT_LINE( FND_FILE.LOG,'Completed Processing');
1861 END IF;
1862
1863 --Error_Handler.Write_Debug('Updated the Process Status to Indicate Successful/Unsucessful component/structure Import Completion');
1864
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 l_err_text := SQLERRM;
1868 x_errbuff := 'Error : '||TO_CHAR(SQLCODE)||'---'||SQLERRM;
1869 x_retcode := Error_Handler.G_STATUS_ERROR;
1870 IF l_debug = 'Y' THEN
1871 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering Exception Message ' || x_errbuff);
1872 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entering Exception Code' || x_retcode);
1873 END IF;
1874 Error_Handler.Close_Debug_Session;
1875
1876 END PROCESS_BOM_INTERFACE_LINES;
1877
1878 FUNCTION displayDT_to_date(charDT VARCHAR2,
1879 calendar_aware NUMBER DEFAULT FND_DATE.calendar_aware_default) RETURN DATE IS
1880 dateval date;
1881 tz_code varchar2(50);
1882
1883 icx_date_format varchar2(200);
1884 output_mask varchar2(200);
1885 BEGIN
1886 icx_date_format := fnd_profile.value('ICX_DATE_FORMAT_MASK');
1887 if instr(icx_date_format, '|') > 0 then
1888 icx_date_format := substr(icx_date_format, 1, instr(icx_date_format, '|'));
1889 end if;
1890
1891 output_mask := icx_date_format || ' HH24:MI:SS';
1892
1893 -- Only non-Gregorian calendar.
1894 if (calendar_aware = FND_DATE.calendar_aware AND FND_DATE.is_non_gregorian) then
1895 dateval := to_date(charDT, output_mask, 'NLS_CALENDAR='''||FND_DATE.user_calendar||'''');
1896 else
1897 dateval := to_date(charDT, output_mask);
1898 end if;
1899
1900 if fnd_date.timezones_enabled then
1901 tz_code := fnd_date.client_timezone_code;
1902
1903 if tz_code <> fnd_date.server_timezone_code and tz_code <> 'FND_NO_CONVERT' then
1904 dateval := fnd_date.adjust_datetime(dateval, tz_code, fnd_date.server_timezone_code);
1905 end if;
1906 end if;
1907
1908 return dateval;
1909 END displayDT_to_date;
1910
1911
1912 PROCEDURE Check_DeReference_Structure
1913 (
1914 p_request_id IN NUMBER
1915 , p_batch_id IN NUMBER
1916 , p_assembly_item_id IN NUMBER
1917 , p_organization_id IN NUMBER
1918 , p_alternate_bom_designator IN VARCHAR2
1919 , x_errbuff OUT NOCOPY VARCHAR2
1920 , x_retcode OUT NOCOPY VARCHAR2
1921 ) IS
1922
1923 l_Component_Count NUMBER;
1924 l_Bill_Sequence_id NUMBER;
1925 l_Common_Bill_Sequence_Id NUMBER;
1926 l_Source_Bill_Sequence_Id NUMBER;
1927
1928 --Txn Types
1929
1930 G_TXN_CREATE VARCHAR2(10) := 'CREATE';
1931 G_TXN_ADD VARCHAR2(10) := 'ADD';
1932 G_TXN_UPDATE VARCHAR2(10) := 'UPDATE';
1933 G_TXN_DELETE VARCHAR2(10) := 'DELETE';
1934 G_TXN_SYNC VARCHAR2(10) := 'SYNC';
1935
1936 BEGIN
1937
1938 SELECT Bill_Sequence_id, Common_Bill_Sequence_id, Source_Bill_Sequence_id
1939 INTO l_Bill_Sequence_id, l_Common_Bill_Sequence_Id, l_Source_Bill_Sequence_Id
1940 FROM BOM_STRUCTURES_B
1941 WHERE assembly_item_id = p_assembly_item_id
1942 AND organization_id = p_organization_id
1943 AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
1944
1945 IF (l_Bill_Sequence_id <> l_Common_Bill_Sequence_Id) THEN
1946
1947 l_Component_Count := 0;
1948
1949 SELECT COUNT(COMPONENT_ITEM_ID) INTO l_Component_Count
1950 FROM BOM_INVENTORY_COMPS_INTERFACE
1951 WHERE process_flag = 1
1952 AND UPPER(transaction_type) = G_TXN_UPDATE
1953 AND Request_Id = p_request_id
1954 AND (p_batch_id IS NULL OR batch_id = p_batch_id)
1955 AND assembly_item_id = p_assembly_item_id
1956 AND organization_id = p_organization_id
1960 -- Dereference the Bill Header
1957 AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
1958
1959 IF (l_Component_Count > 0) THEN
1961 BOMPCMBM.Dereference_Header( p_bill_sequence_id => l_Bill_Sequence_id);
1962 -- Dereference the components and others
1963 BOMPCMBM.Replicate_Components( p_src_bill_sequence_id => l_Common_Bill_Sequence_Id, p_dest_bill_sequence_id => l_Bill_Sequence_id);
1964 -- Update the components with new component sequence id
1965 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
1966 SET COMPONENT_SEQUENCE_ID
1967 = (SELECT COMPONENT_SEQUENCE_ID
1968 FROM BOM_INVENTORY_COMPONENTS BIC, bom_structures_b bsb
1969 WHERE BIC.bill_sequence_id = bsb.bill_Sequence_id
1970 AND bsb.assembly_item_id = p_assembly_item_id
1971 AND bsb.organization_id = p_organization_id
1972 AND NVL(bsb.alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR)
1973 AND BIC.common_component_sequence_id = BICI.component_sequence_id)
1974 WHERE process_flag = 1
1975 AND UPPER(transaction_type) = G_TXN_UPDATE
1976 AND Request_Id = p_request_id
1977 AND (p_batch_id IS NULL OR batch_id = p_batch_id)
1978 AND assembly_item_id = p_assembly_item_id
1979 AND organization_id = p_organization_id
1980 AND NVL(alternate_bom_designator , Fnd_Api.G_MISS_CHAR) = NVL(p_alternate_bom_designator, Fnd_Api.G_MISS_CHAR);
1981 END IF;
1982 END IF;
1983
1984 --Error_Handler.Write_Debug('EBI: Updated the Process_Status to Indicate Succssful/Unsucessful completion.');
1985 x_retcode := G_STATUS_SUCCESS;
1986
1987 EXCEPTION
1988 WHEN NO_DATA_FOUND THEN
1989 x_retcode := G_STATUS_SUCCESS;
1990 WHEN OTHERS THEN
1991 x_retcode := G_STATUS_ERROR;
1992 x_errbuff := SUBSTRB(SQLERRM, 1,240);
1993 RAISE;
1994 END Check_DeReference_Structure;
1995
1996 -- Data seperation logic for component user attributes.
1997 PROCEDURE load_comp_usr_attr_interface
1998 (
1999 p_resultfmt_usage_id IN NUMBER,
2000 p_data_set_id IN NUMBER,
2001 x_errbuff OUT NOCOPY VARCHAR2,
2002 x_retcode OUT NOCOPY VARCHAR2
2003 ) IS
2004
2005
2006 ------------------------------------------------------------------------------
2007 -- To retrieve Attribute group codes, for given Result Format Usage ID.
2008 ------------------------------------------------------------------------------
2009 CURSOR c_user_attr_group_codes (c_resultfmt_usage_id IN NUMBER) IS
2010 SELECT DISTINCT To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) attr_group_id
2011 FROM ego_results_fmt_usages
2012 WHERE resultfmt_usage_id = c_resultfmt_usage_id
2013 AND attribute_code LIKE '%$$%'
2014 AND To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN --attr_group_id
2015 ------------------------------------------------------------------------------
2016 -- Fixed in 11.5.10. Ensuring only the Item User-Defined Attrs are processed.
2017 ------------------------------------------------------------------------------
2018 (
2019 SELECT attr_group_id
2020 FROM ego_attr_groups_v
2021 WHERE attr_group_type = G_COMP_ATTR_GROUP_TYPE
2022 AND application_id = G_BOM_APPLICATION_ID
2023 );
2024
2025
2026 ------------------------------------------------------------------------------
2027 -- To get the Attribute Group and Attribute Internal Names.
2028 -- NOTE: Joined extra attributes ATTR_GROUP_TYPE and APPLICATION_ID
2029 -- To hit the index.
2030 ------------------------------------------------------------------------------
2031 CURSOR c_attr_grp_n_attr_int_names(p_attr_id IN NUMBER) IS
2032 SELECT attr_group_name, attr_name
2033 FROM ego_attrs_v
2034 WHERE attr_id = p_attr_id
2035 AND attr_group_type = G_COMP_ATTR_GROUP_TYPE
2036 AND application_id = G_BOM_APPLICATION_ID;
2037
2038
2039 --------------------------------------------------------------------------------
2040 -- Defn includes a subset of EGO_USER_ATTRS_DATA_PVT.LOCAL_USER_ATTR_DATA_REC
2041 -- plus few User-Defined Attr Table related fields.
2042 --------------------------------------------------------------------------------
2043 TYPE L_USER_ATTR_REC_TYPE IS RECORD
2044 (
2045 DATA_SET_ID NUMBER(15)
2046 ,TRANSACTION_ID NUMBER(15)
2047 ,COMPONENT_SEQUENCE_ID NUMBER(15)
2048 ,BILL_SEQUENCE_ID NUMBER(15)
2049 ,ORGANIZATION_ID NUMBER(15)
2050 ,COMPONENT_ITEM_NUMBER VARCHAR2(1000)
2051 ,ORGANIZATION_CODE VARCHAR2(10)
2052 ,ROW_IDENTIFIER NUMBER(15)
2053 ,ATTR_GROUP_NAME VARCHAR2(30)
2054 ,ATTR_NAME VARCHAR2(30)
2055 ,ATTR_DATATYPE_CODE VARCHAR2(1) --Valid Vals: C / N / D
2059 ,INTF_COLUMN_NAME VARCHAR2(30)
2056 ,ATTR_VALUE_STR VARCHAR2(1000)
2057 ,ATTR_VALUE_NUM NUMBER
2058 ,ATTR_VALUE_DATE DATE
2060 ,SOURCE_SYSTEM_ID NUMBER
2061 ,SOURCE_SYSTEM_REFERENCE VARCHAR2(255)
2062 ,PARENT_SOURCE_SYSTEM_REFERENCE VARCHAR2(255)
2063 ,ASSEMBLY_ITEM_NUMBER VARCHAR2(255)
2064 );
2065
2066 ---------------------------------------------------------------------
2067 -- Type Declarations
2068 ---------------------------------------------------------------------
2069 TYPE L_USER_ATTR_TBL_TYPE IS TABLE OF L_USER_ATTR_REC_TYPE
2070 INDEX BY BINARY_INTEGER;
2071
2072 TYPE VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(256)
2073 INDEX BY BINARY_INTEGER;
2074
2075 -------------------------
2076 -- local variables --
2077 -------------------------
2078 l_str_type_id NUMBER;
2079 l_prod_col_name_tbl VARCHAR_TBL_TYPE;
2080 l_intf_col_name_tbl VARCHAR_TBL_TYPE;
2081
2082 l_attr_id_table DBMS_SQL.NUMBER_TABLE;
2083 l_intf_col_name_table DBMS_SQL.VARCHAR2_TABLE;
2084
2085 l_usr_attr_data_tbl L_USER_ATTR_TBL_TYPE;
2086
2087 l_bill_sequence_id_char VARCHAR(30);
2088 l_component_sequence_id_char VARCHAR(30);
2089 l_source_system_id NUMBER;
2090 l_source_system_ref VARCHAR2(255);
2094 l_assembly_item_num VARCHAR2(255);
2091 l_comp_item_num VARCHAR2(255);
2092 l_org_code VARCHAR2(25);
2093 l_par_reference VARCHAR2(255);
2095
2096 l_count NUMBER(5);
2097 l_data_type_code VARCHAR2(2);
2098 l_transaction_id NUMBER(15);
2099 l_msii_set_process_id NUMBER;
2100
2101 l_attr_group_int_name EGO_ATTRS_V.ATTR_GROUP_NAME%TYPE;
2102 l_attr_int_name EGO_ATTRS_V.ATTR_NAME%TYPE;
2103
2104 ---------------------------------------------------------
2105 -- Example Data Types to be used in Bind Variable.
2106 ---------------------------------------------------------
2107 l_varchar_example VARCHAR2(10000);
2108 l_number_example NUMBER;
2109 l_date_example DATE;
2110
2111 --------------------------------------------------------------------
2112 -- Actual Data to store corresponding data type value.
2113 -- NOTE: for fixing Bug# 3808455, changed the size of l_varchar_data
2114 -- to 10,000 chars. This is because, if there are 1000 Single
2115 -- Quotes in the String Attr Value, then the Escaped value
2116 -- becomes of Size 2000. So, for all better reasons, changing
2117 -- to a huge size.
2118 --------------------------------------------------------------------
2119 l_varchar_data VARCHAR2(10000);
2120 l_number_data NUMBER;
2121 l_date_data DATE;
2122
2123 ---------------------------------------------------------
2124 -- DBMS_SQL Open Cursor integers.
2125 ---------------------------------------------------------
2126 l_cursor_select INTEGER;
2127 l_cursor_execute INTEGER;
2128 l_cursor_attr_id_val INTEGER;
2129
2130 ---------------------------------------------------------
2131 -- Used for indexes.
2132 ---------------------------------------------------------
2133 l_temp NUMBER(10) := 1;
2134 l_actual_userattr_indx NUMBER(15);
2135 l_indx NUMBER(15);
2136 l_rows_per_attr_grp_indx NUMBER(15);
2137 l_save_indx NUMBER(15);
2138 l_attr_grp_has_data BOOLEAN;
2139
2140 l_attr_group_data_level VARCHAR2(30);
2141
2142 ---------------------------------------------------------
2143 -- Long Dynamic SQL Strings
2144 ---------------------------------------------------------
2145 l_dyn_sql VARCHAR2(10000);
2146 l_dyn_attr_id_val_sql VARCHAR2(10000);
2147
2148 ---------------------------------------------------------
2149 -- To Number the Attribute Group Data Rows Uniquely.
2150 ---------------------------------------------------------
2151 L_ATTR_GRP_ROW_IDENT NUMBER(5);
2152
2153 ---------------------------------------------------------
2154 -- Token tables to log errors, through Error_Handler
2155 ---------------------------------------------------------
2156 l_token_tbl_two Error_Handler.Token_Tbl_Type;
2157 l_token_tbl_one Error_Handler.Token_Tbl_Type;
2158
2159 l_comp_usr_attr_flag NUMBER(10) := 0;
2160
2161 BEGIN
2162 write_debug(' SRIDHAR');
2166 L_ATTR_GRP_ROW_IDENT := 0;
2163 ---------------------------------------------------------
2164 -- Initializing the Row Identifier.
2165 ---------------------------------------------------------
2167
2168 IF p_data_set_id IS NULL THEN
2169 SELECT mtl_system_items_intf_sets_s.NEXTVAL
2170 INTO l_msii_set_process_id
2171 FROM dual;
2172 ELSE
2173 l_msii_set_process_id := p_data_set_id;
2174 END IF;
2175
2176 --------------------------------------------------------------------
2177 -- Loop to process per Attribute Group of User-Defined Attributes.
2178 --------------------------------------------------------------------
2179 FOR c_attr_grp_rec IN c_user_attr_group_codes
2180 (
2181 p_resultfmt_usage_id
2182 )
2183 LOOP
2184
2185 --------------------------------------------------------------------
2186 -- Fetch Organization ID, Item Number in Temp PLSQL tables.
2187 --------------------------------------------------------------------
2188 l_dyn_sql := '';
2189 l_dyn_sql := ' SELECT To_Number(SUBSTR(attribute_code, INSTR(attribute_code, ''$$'')+2)) attr_id, intf_column_name ';
2190 l_dyn_sql := l_dyn_sql || ' FROM ego_results_fmt_usages ';
2191 l_dyn_sql := l_dyn_sql || ' WHERE resultfmt_usage_id = :RESULTFMT_USAGE_ID';
2192 l_dyn_sql := l_dyn_sql || ' AND attribute_code LIKE :ATTRIBUTE_CODE ';
2193 -- P4T Bug 8371175 start
2194 l_dyn_sql := l_dyn_sql || ' AND attribute_code NOT LIKE :ATTRIBUTE_CODE_O ';
2195 -- P4T Bug 8371175 end
2196
2197 Write_Debug(l_dyn_sql);
2198
2199 l_cursor_select := DBMS_SQL.OPEN_CURSOR;
2200 DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
2201 DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
2202 DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
2203 DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
2204 DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
2205 -- P4T Bug 8371175 start
2206 DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE_O', c_attr_grp_rec.attr_group_id||'$$%_O');
2207 -- P4T Bug 8371175 end
2208
2209 l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
2210
2211
2212 /*
2213 --------------------------------------------------------------------
2214 -- Added for BugFix 4114928 : We need to check for the data level --
2215 -- of the atr group and populate the REVISION_ID only if the AG --
2216 -- revision level. --
2217 --------------------------------------------------------------------
2218 SELECT DATA_LEVEL_INT_NAME INTO l_attr_group_data_level
2219 FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
2220 WHERE ATTR_GROUP_TYPE in (G_IUD_ATTR_GROUP_TYPE, G_GTN_SNG_ATTR_GROUP_TYPE, G_GTN_MUL_ATTR_GROUP_TYPE)
2221 AND ATTR_GROUP_ID = c_attr_grp_rec.attr_group_id
2222 AND OBJECT_NAME = G_EGO_ITEM_OBJ_NAME
2223 AND ROWNUM = 1;-- The AG cannot have associations at Item level and Revision Level for different Catalogs.
2224 */
2225
2226
2227 Write_Debug('About to start the Loop to fetch Rows');
2228 l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
2229 DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
2230 DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
2231 Write_Debug('Retrieved rows => '||To_char(l_count));
2232 DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
2233
2239 l_dyn_attr_id_val_sql := ' SELECT ';
2234 --------------------------------------------------------------------
2235 -- New DBMS_SQL Cursor for Select Attr Values.
2236 --------------------------------------------------------------------
2237 l_cursor_attr_id_val := DBMS_SQL.OPEN_CURSOR;
2238 l_dyn_attr_id_val_sql := '';
2240 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' TRANSACTION_ID , ';
2241 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK4_VALUE , ';
2242 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK5_VALUE , ';
2243 --------------------------------------------------------------------
2244 -- R12
2245 -- Adding the source system id and source system reference columns
2246 --------------------------------------------------------------------
2247 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' TO_NUMBER(C_FIX_COLUMN11) , ';
2248 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' C_FIX_COLUMN12 , C_INTF_ATTR4, C_INTF_ATTR2 ,C_FIX_COLUMN14 , C_INTF_ATTR5 ,';
2249
2250 --------------------------------------------------------------------
2251 -- Loop to Update the Inventory Item IDs.
2252 --------------------------------------------------------------------
2253 FOR i IN 1..l_attr_id_table.COUNT LOOP
2254 Write_Debug('Attr ID : '||To_char(l_attr_id_table(i)));
2255 Write_Debug('Intf Col Name : '||l_intf_col_name_table(i));
2256 IF (i <> l_attr_id_table.COUNT) THEN
2257 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) || ', ';
2258 ELSE
2259 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) ;
2260 END IF;
2261 END LOOP; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
2262
2263 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' FROM EGO_BULKLOAD_INTF ' ;
2264 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
2265 l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' AND PROCESS_STATUS = :PROCESS_STATUS ';
2266
2267 Write_Debug(l_dyn_attr_id_val_sql);
2268
2269 DBMS_SQL.PARSE(l_cursor_attr_id_val, l_dyn_attr_id_val_sql, DBMS_SQL.NATIVE);
2270 --------------------------------------------------------------------
2271 --Setting Data Type for Trasaction ID
2272 --------------------------------------------------------------------
2273 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 1, l_number_example);
2274
2275 --------------------------------------------------------------------
2276 --Setting Data Type for INSTANCE_PK4_VALUE (Bill Sequence Id)
2277 --------------------------------------------------------------------
2278 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 2, l_varchar_example, 1000);
2279
2280 --------------------------------------------------------------------
2281 --Setting Data Type for INSTANCE_PK2_VALUE (Component Sequence Id)
2282 --------------------------------------------------------------------
2283 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 3, l_varchar_example, 1000);
2284
2285 --Setting Data Type for Source System Id
2286 --------------------------------------------------------------------
2287 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 4, l_number_example);
2288
2289 --------------------------------------------------------------------
2290 --Setting Data Type for Source System Reference
2291 --------------------------------------------------------------------
2292 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 5, l_varchar_example, 1000);
2293
2294 --------------------------------------------------------------------
2295 --Setting Data Type for Component Item Number
2296 --------------------------------------------------------------------
2297 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 6, l_varchar_example, 1000);
2298
2299 --------------------------------------------------------------------
2300 --Setting Data Type for Organziation Code
2301 --------------------------------------------------------------------
2302 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 7, l_varchar_example, 1000);
2303
2304 --------------------------------------------------------------------
2305 --Setting Data Type for Parent Source System Reference
2306 --------------------------------------------------------------------
2307 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 8, l_varchar_example, 1000);
2308
2309 --------------------------------------------------------------------
2310 --Setting Data Type for Assembly Item Number
2311 --------------------------------------------------------------------
2312 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 9, l_varchar_example, 1000);
2313
2314 --------------------------------------------------------------------
2315 -- Loop to Bind the Data Types for the SELECT Columns.
2316 --------------------------------------------------------------------
2317 FOR i IN 1..l_attr_id_table.COUNT LOOP
2318
2319 ------------------------------------------------------------------------
2320 -- Since TRANSACTION_ID, INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE,
2321 -- INSTANCE_PK3_VALUE are added to the SELECT before the User-Defined
2322 -- Attrs, we need to adjust the index as follows.
2323 ------------------------------------------------------------------------
2324 l_actual_userattr_indx := i + 9;
2325
2326 l_data_type_code := SUBSTR (l_intf_col_name_table(i), 1, 1);
2327
2328 ------------------------------------------------------------------------
2329 -- Based on the Data Type of the attribute, define the column
2330 ------------------------------------------------------------------------
2331 IF (l_data_type_code = 'C') THEN
2332 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 1000);
2333 ELSIF (l_data_type_code = 'N') THEN
2337 END IF; --IF (l_data_type_code = 'C') THEN
2334 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);
2335 ELSE --IF (l_data_type_code = 'D') THEN
2336 DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_example);
2338
2339 END LOOP; --FOR i IN 1..l_attr_id_table.COUNT LOOP
2340
2341 DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);
2342
2343 write_debug('Binding the PROCESS_STATUS = '||G_INTF_STATUS_TOBE_PROCESS);
2344 DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':PROCESS_STATUS',G_INTF_STATUS_TOBE_PROCESS);
2345
2346 ------------------------------------------------------------------------
2347 -- Execute to get the Item User-Defined Attr values.
2348 ------------------------------------------------------------------------
2349 l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_attr_id_val);
2350
2351 l_rows_per_attr_grp_indx := 0;
2352
2353 ------------------------------------------------------------------------
2354 -- Loop for each row found in EBI
2355 ------------------------------------------------------------------------
2356 LOOP --LOOP FOR CURSOR_ATTR_ID_VAL
2357
2358 IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
2359
2360 ------------------------------------------------------------------------
2361 --Increment Row Identifier per (Attribute Group + Row) Combination.
2362 ------------------------------------------------------------------------
2363 L_ATTR_GRP_ROW_IDENT := L_ATTR_GRP_ROW_IDENT + 1;
2364
2365 Write_Debug('ROW_FOUND : '||L_ATTR_GRP_ROW_IDENT);
2366
2367 ------------------------------------------------------------------------
2368 -- First column is Transaction ID.
2369 ------------------------------------------------------------------------
2370 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
2371 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_bill_sequence_id_char);
2372 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_component_sequence_id_char);
2373 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_source_system_id);
2374 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_source_system_ref);
2375 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 6, l_comp_item_num);
2376 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 7, l_org_code);
2377 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 8, l_par_reference);
2378 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 9, l_assembly_item_num);
2379
2380 ------------------------------------------------------------------------
2381 -- Loop to Bind the Data Types for the SELECT Columns.
2382 ------------------------------------------------------------------------
2383 FOR i IN 1..l_attr_id_table.COUNT LOOP
2384
2385 OPEN c_attr_grp_n_attr_int_names(l_attr_id_table(i));
2386 FETCH c_attr_grp_n_attr_int_names INTO
2387 l_attr_group_int_name, l_attr_int_name;
2388
2389 Write_Debug(i||'=>'||l_attr_group_int_name||':'||l_attr_int_name);
2390
2391 l_attr_grp_has_data := FALSE;
2392
2393 ------------------------------------------------------------------------
2394 -- If one more Attribute found for the Attribute Group.
2395 ------------------------------------------------------------------------
2396 IF c_attr_grp_n_attr_int_names%FOUND THEN
2397 l_rows_per_attr_grp_indx := l_rows_per_attr_grp_indx + 1;
2398 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).DATA_SET_ID := l_msii_set_process_id;
2399 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).TRANSACTION_ID := l_transaction_id;
2400 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).BILL_SEQUENCE_ID := FND_NUMBER.CANONICAL_TO_NUMBER(l_bill_sequence_id_char);
2401 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).COMPONENT_SEQUENCE_ID := FND_NUMBER.CANONICAL_TO_NUMBER(l_component_sequence_id_char);
2402 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ROW_IDENTIFIER := L_ATTR_GRP_ROW_IDENT;
2403 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE := SUBSTR (l_intf_col_name_table(i), 1, 1);
2404 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_GROUP_NAME := l_attr_group_int_name;
2405 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_NAME := l_attr_int_name;
2406
2407 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).SOURCE_SYSTEM_ID := l_source_system_id;
2408 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).SOURCE_SYSTEM_REFERENCE := l_source_system_ref;
2409 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).COMPONENT_ITEM_NUMBER := l_comp_item_num;
2410 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ORGANIZATION_CODE := l_org_code;
2411 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PARENT_SOURCE_SYSTEM_REFERENCE := l_par_reference;
2412 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ASSEMBLY_ITEM_NUMBER := l_assembly_item_num;
2413
2414
2415
2416 ------------------------------------------------------------------------
2417 -- Since TRANSACTION_ID, INSTANCE_PK4_VALUE, INSTANCE_PK5_VALUE,
2418 -- are added to the SELECT before User-Defined
2419 -- Attrs, we need to adjust the index as follows.
2420 ------------------------------------------------------------------------
2421 l_actual_userattr_indx := i + 9;
2422
2423 Write_Debug('BEGIN: To Retrieve Attr Value at Position :'||l_actual_userattr_indx);
2424
2425 ------------------------------------------------------------------------
2426 -- Depending upon the Data Type, populate corresponding field in the
2427 -- User-Defined Attribute Data record.
2428 ------------------------------------------------------------------------
2429 IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'C') THEN
2433 ELSIF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'N') THEN
2430 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_data);
2431 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR := l_varchar_data;
2432 Write_Debug('String Value =>'||l_varchar_data);
2434 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_data);
2435 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_NUM := l_number_data;
2436 Write_Debug('Number Value =>'||l_number_data);
2437 ELSE --IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'D') THEN
2438 DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_data);
2439 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_DATE := l_date_data;
2440 Write_Debug('Date Value =>'||l_date_data);
2441 END IF; --end: IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'C') THEN
2442
2443 Write_Debug('END: Retrieved Attr Value');
2444
2445 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).INTF_COLUMN_NAME := l_intf_col_name_table(i);
2446
2447
2448 ------------------------------------------------------------------------
2449 -- Bug: 3025778 Modified If statment.
2450 -- Donot populate NULL Attribute value in the User-Defined Attrs
2451 -- Interface table.
2452 ------------------------------------------------------------------------
2453 IF ((l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR IS NULL) AND
2454 (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_NUM IS NULL) AND
2455 (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_DATE IS NULL)
2456 ) THEN
2457 ------------------------------------------------------------------------
2458 -- If all attribute values are NULL value, then delete
2462 l_rows_per_attr_grp_indx := l_rows_per_attr_grp_indx - 1;
2459 -- the row from PLSQL table.
2460 ------------------------------------------------------------------------
2461 l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);
2463
2464 Write_Debug('Due to NULL Att data, resetting back the PLSQL table index to : '||l_rows_per_attr_grp_indx);
2465
2466 END IF; --end: IF ((l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR...
2467
2468 END IF; --end: IF c_attr_grp_n_attr_int_names%FOUND THEN
2469
2470 CLOSE c_attr_grp_n_attr_int_names;
2471
2472 END LOOP; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
2473
2474 ELSE --end: IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
2475
2476 Write_Debug('Nothing Found (or) Done.');
2477 EXIT;
2478
2479 END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
2480
2481 END LOOP; --END: LOOP FOR CURSOR_ATTR_ID_VAL
2482
2483 l_attr_id_table.DELETE;
2484 l_intf_col_name_table.DELETE;
2485
2486
2487 DBMS_SQL.CLOSE_CURSOR(l_cursor_attr_id_val);
2488
2489 -------------------------------------------------------------------
2490 -- Loop for all the rows to be inserted per Attribute Group.
2491 -------------------------------------------------------------------
2492 FOR i IN 1..l_rows_per_attr_grp_indx LOOP
2493
2494
2495 -------------------------------------------------------------------------
2496 -- Fix for Bug# 3808455. To avoid the following error:
2497 -- ORA-01401: inserted value too large for column
2498 -- [This is done because ATTR_DISP_VALUE size is 1000 Chars]
2499 -------------------------------------------------------------------------
2500 IF ( LENGTH(l_usr_attr_data_tbl(i).ATTR_VALUE_STR) > 1000 ) THEN
2501 l_token_tbl_one(1).token_name := 'VALUE';
2502 l_token_tbl_one(1).token_value := l_usr_attr_data_tbl(i).ATTR_VALUE_STR;
2503
2504 Error_Handler.Add_Error_Message
2505 ( p_message_name => 'EGO_STR_ATTR_LEN_GT1000_ERR'
2506 , p_application_id => 'EGO'
2507 , p_message_text => NULL
2508 , p_token_tbl => l_token_tbl_one
2509 , p_message_type => 'E'
2510 , p_row_identifier => l_usr_attr_data_tbl(i).TRANSACTION_ID
2511 , p_table_name => G_ERROR_TABLE_NAME
2512 , p_entity_id => NULL
2513 , p_entity_index => NULL
2514 , p_entity_code => G_ERROR_ENTITY_CODE
2515 );
2516
2517 ---------------------------------------------------------------------------
2521 ---------------------------------------------------------------------------
2518 -- Put multiple ELSIF <<condition>> here, to report Errors with the Data.
2519 -- Finally, ELSE condition below means that Data is ~Error Free~ and ready
2520 -- to be Inserted.
2522
2523 ELSE --IF ( LENGTH(l_usr_attr_data_tbl(i)..
2524
2525
2526 ------------------------------------------------------------------------
2527 -- Populate l_varchar_data, to later populate in ATTR_DISP_VALUE
2528 ------------------------------------------------------------------------
2529 l_varchar_data := NULL;
2530 IF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'C') THEN
2531
2532 l_varchar_data := l_usr_attr_data_tbl(i).ATTR_VALUE_STR;
2533
2534 ELSIF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'N') THEN
2535
2536 IF (l_usr_attr_data_tbl(i).ATTR_VALUE_NUM IS NOT NULL) THEN
2537 l_varchar_data := To_char(l_usr_attr_data_tbl(i).ATTR_VALUE_NUM);
2538 END IF;
2539
2540 ELSE --IF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'D') THEN
2541
2542 IF (l_usr_attr_data_tbl(i).ATTR_VALUE_DATE IS NOT NULL) THEN
2543 l_varchar_data := To_Char(l_usr_attr_data_tbl(i).ATTR_VALUE_DATE , G_DATE_FORMAT);
2544 END IF;
2545
2546 END IF; --end: IF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'C') THEN
2547 --dikrishn Dont we need the structure type id to be inserted into
2548 -- bom_cmp_usr_attr_interface ?Bom JCP required batch_id so inserting that
2549 -- also
2550 SELECT structure_type_id
2551 INTO l_str_type_id
2552 FROM ego_import_option_sets
2553 where batch_id = p_data_set_id;
2554
2555 ----------------------------------------------------------------------
2556 -- 1)
2557 -- The User-Defined Attrs BO has some validation changes, which
2558 -- mandates users to pass in the display value, so that BO does the
2559 -- conversion to internal value. To support that change, I need to
2560 -- populate ATTR_DISP_VALUE instead of internal columns :
2561 -- ATTR_VALUE_STR, ATTR_VALUE_DATE, ATTR_VALUE_NUM.
2562 -- Above change to populate ATTR_DISP_VALUE was advised by DAARENA
2563 -- (Dylan Arena)
2564 --
2565 -- 2)
2566 -- TRANSACTION_TYPE, PROCESS_STATUS need *not* be populated as they
2567 -- are defaulted by the User Attrs PLSQL Program
2568 ----------------------------------------------------------------------
2569 --R12
2570 --DPHILIP: we will need to populate the process_status to 1 or 0 here.. we cant let the user attrs pl/sql handle this
2571
2572 -- Fixed bug 9918265
2573 SELECT COUNT(*) INTO l_comp_usr_attr_flag
2574 FROM BOM_CMP_USR_ATTR_INTERFACE
2575 WHERE DATA_SET_ID = l_usr_attr_data_tbl(i).DATA_SET_ID
2576 AND ROW_IDENTIFIER = l_usr_attr_data_tbl(i).ROW_IDENTIFIER
2577 AND ATTR_GROUP_INT_NAME = l_usr_attr_data_tbl(i).ATTR_GROUP_NAME
2578 AND ATTR_INT_NAME = l_usr_attr_data_tbl(i).ATTR_NAME
2579 --Bug 13580598
2580 AND ( ( ITEM_NUMBER = l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER
2581 AND ASSEMBLY_ITEM_NUMBER = l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER
2582 AND ORGANIZATION_CODE = l_usr_attr_data_tbl(i).ORGANIZATION_CODE )
2583 );
2584
2585 Write_Debug('l_comp_usr_attr_flag [' || l_comp_usr_attr_flag || ']' );
2586
2587 IF l_comp_usr_attr_flag = 0 THEN
2588 INSERT INTO BOM_CMP_USR_ATTR_INTERFACE
2589 (
2590 DATA_SET_ID ,
2591 TRANSACTION_ID ,
2592 BILL_SEQUENCE_ID ,
2593 COMPONENT_SEQUENCE_ID,
2594 ROW_IDENTIFIER ,
2595 ATTR_GROUP_INT_NAME ,
2596 ATTR_INT_NAME ,
2597 ATTR_DISP_VALUE ,
2598 PROCESS_STATUS ,
2599 SOURCE_SYSTEM_ID ,
2600 COMP_SOURCE_SYSTEM_REFERENCE,
2604 ITEM_NUMBER,
2601 BATCH_ID,
2602 STRUCTURE_TYPE_ID,
2603 TRANSACTION_TYPE,
2605 ORGANIZATION_CODE,
2606 ORGANIZATION_ID,
2607 ASSEMBLY_ITEM_NUMBER,
2608 PARENT_SOURCE_SYSTEM_REFERENCE,
2609 DATA_LEVEL_ID -- Added for PIMTELCO Bug-7645265
2610 )
2611 VALUES
2612 (
2613 l_usr_attr_data_tbl(i).DATA_SET_ID,
2614 l_usr_attr_data_tbl(i).TRANSACTION_ID,
2615 l_usr_attr_data_tbl(i).BILL_SEQUENCE_ID,
2619 l_usr_attr_data_tbl(i).ATTR_NAME,
2616 l_usr_attr_data_tbl(i).COMPONENT_SEQUENCE_ID,
2617 l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
2618 l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
2620 l_varchar_data,
2621 0,-- G_PROCESS_STATUS,
2622 l_usr_attr_data_tbl(i).SOURCE_SYSTEM_ID,
2623 l_usr_attr_data_tbl(i).SOURCE_SYSTEM_REFERENCE,
2624 p_data_set_id,
2625 l_str_type_id,
2626 'SYNC',
2627 l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER,
2628 l_usr_attr_data_tbl(i).ORGANIZATION_CODE,
2629 (SELECT ORGANIZATION_ID FROM mtl_parameters where organization_code = l_usr_attr_data_tbl(i).ORGANIZATION_CODE),
2630 l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER,
2631 l_usr_attr_data_tbl(i).PARENT_SOURCE_SYSTEM_REFERENCE,
2632 70201 -- Added for PIMTELCO Bug-7645265 Hardcoded Value for COMPONENTS_LEVEL data level
2633 );
2634 ELSE
2635 UPDATE BOM_CMP_USR_ATTR_INTERFACE
2636 SET TRANSACTION_ID = l_usr_attr_data_tbl(i).TRANSACTION_ID,
2637 BILL_SEQUENCE_ID = l_usr_attr_data_tbl(i).BILL_SEQUENCE_ID,
2638 COMPONENT_SEQUENCE_ID = l_usr_attr_data_tbl(i).COMPONENT_SEQUENCE_ID,
2639 ATTR_DISP_VALUE = l_varchar_data,
2640 PROCESS_STATUS = 0,
2641 SOURCE_SYSTEM_ID = l_usr_attr_data_tbl(i).SOURCE_SYSTEM_ID,
2642 COMP_SOURCE_SYSTEM_REFERENCE = l_usr_attr_data_tbl(i).SOURCE_SYSTEM_REFERENCE,
2643 BATCH_ID = p_data_set_id,
2644 STRUCTURE_TYPE_ID = l_str_type_id,
2645 TRANSACTION_TYPE = 'SYNC',
2646 ITEM_NUMBER = l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER,
2647 ORGANIZATION_CODE = l_usr_attr_data_tbl(i).ORGANIZATION_CODE,
2648 ORGANIZATION_ID = (SELECT ORGANIZATION_ID FROM mtl_parameters where organization_code = l_usr_attr_data_tbl(i).ORGANIZATION_CODE),
2649 ASSEMBLY_ITEM_NUMBER = l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER,
2650 PARENT_SOURCE_SYSTEM_REFERENCE = l_usr_attr_data_tbl(i).PARENT_SOURCE_SYSTEM_REFERENCE,
2651 DATA_LEVEL_ID = 70201,
2652 --pnagasur:Added for Bug 13085172
2653 ATTR_VALUE_STR =l_usr_attr_data_tbl(i).ATTR_VALUE_STR,
2654 ATTR_VALUE_NUM =l_usr_attr_data_tbl(i).ATTR_VALUE_NUM ,
2655 ATTR_VALUE_DATE =l_usr_attr_data_tbl(i).ATTR_VALUE_DATE
2656 WHERE DATA_SET_ID = l_usr_attr_data_tbl(i).DATA_SET_ID
2657 AND ROW_IDENTIFIER = l_usr_attr_data_tbl(i).ROW_IDENTIFIER
2658 AND ATTR_GROUP_INT_NAME = l_usr_attr_data_tbl(i).ATTR_GROUP_NAME
2659 AND ATTR_INT_NAME = l_usr_attr_data_tbl(i).ATTR_NAME
2660 --Bug 13580598
2661 AND ( ( ITEM_NUMBER = l_usr_attr_data_tbl(i).COMPONENT_ITEM_NUMBER
2662 AND ASSEMBLY_ITEM_NUMBER = l_usr_attr_data_tbl(i).ASSEMBLY_ITEM_NUMBER
2663 AND ORGANIZATION_CODE = l_usr_attr_data_tbl(i).ORGANIZATION_CODE )
2664 );
2665 END IF;
2666
2667 l_comp_usr_attr_flag := 0;
2668
2669 Write_Debug('DataSetID ['||l_usr_attr_data_tbl(i).DATA_SET_ID||'] '||G_NEWLINE||
2670 'TransactionID ['||l_usr_attr_data_tbl(i).TRANSACTION_ID||'] '||G_NEWLINE||
2671 'BillSequenceId ['||l_usr_attr_data_tbl(i).BILL_SEQUENCE_ID||'] '||G_NEWLINE||
2672 'CompSequenceId ['||l_usr_attr_data_tbl(i).COMPONENT_SEQUENCE_ID||'] '||G_NEWLINE||
2673 'RowIdentifier ['||l_usr_attr_data_tbl(i).ROW_IDENTIFIER||'] '||G_NEWLINE||
2674 'AttrGroupName ['||l_usr_attr_data_tbl(i).ATTR_GROUP_NAME||'] '||G_NEWLINE||
2675 'AttrName ['||l_usr_attr_data_tbl(i).ATTR_NAME||'] '||G_NEWLINE||
2676 'ATTR_VALUE_STR ['||l_usr_attr_data_tbl(i).ATTR_VALUE_STR ||']'||G_NEWLINE||
2677 ': Populated ATTR_DISP_VALUE of DataType['||l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE||'] => '||l_varchar_data);
2678
2679 END IF; --end: IF ( LENGTH(l_usr_attr_data_tbl(i)..
2680
2681 END LOOP; --FOR i IN 1..l_usr_attr_data_tbl.COUNT LOOP
2682
2683 Write_Debug('EIAI: Populated the Component User-Defined Attr Values for Attribute Group : '||l_attr_group_int_name);
2684
2685 END LOOP; --FOR c_attr_grp_rec IN c_user_attr_group_codes
2686
2687 Write_Debug('EIAI: DONE Populating the Component User-Defined Attr Values');
2688
2689 EXCEPTION
2690 WHEN OTHERS THEN
2691 x_retcode := G_STATUS_ERROR;
2692 x_errbuff := SUBSTRB(SQLERRM, 1,240);
2693 Write_Debug('load_comp_usr_attr_interface : EXCEPTION HAPPENED => '||x_errbuff);
2694 RAISE;
2695
2696 END load_comp_usr_attr_interface;
2697
2698 END BOM_BULKLOAD_PVT_PKG;