[Home] [Help]
PACKAGE BODY: APPS.BOMPLDCI
Source
1 package body BOMPLDCI as
2 /* $Header: BOMLDCIB.pls 115.7 99/09/16 16:00:42 porting ship $ */
3
4 function get_validation_org ( opunit in number,
5 site_level_org_id in number)
6 return integer
7 is
8
9 lValue fnd_profile_option_values.profile_option_value%type;
10 loldvalue fnd_profile_option_values.profile_option_value%type;
11 lValidOrg number;
12
13
14 cursor validation_org is
15 select distinct POV.profile_option_value
16 from fnd_profile_options PO,
17 fnd_profile_option_values POV,
18 fnd_responsibility FR,
19 fnd_profile_options PO2,
20 fnd_profile_option_values POV2
21 where PO.profile_option_name = 'SO_ORGANIZATION_ID'
22 and POV.application_id = PO.application_id
23 and POV.profile_option_id = PO.profile_option_id
24 and POV.level_id = 10003
25 and FR.application_id = POV.level_value_application_id
26 and FR.responsibility_id = POV.level_value
27 and PO2.profile_option_name = 'ORG_ID'
28 and POV2.application_id = PO2.application_id
29 and POV2.profile_option_id = PO2.profile_option_id
30 and POV2.level_id = 10003
31 and POV2.profile_option_value = to_char(opunit)
32 and POV2.level_value_Application_id = 300
33 and FR.application_id = POV2.level_value_application_id
34 and FR.responsibility_id = POV2.level_value;
35
36 multiorg_error EXCEPTION;
37
38 begin
39
40 lOldValue := 0;
41 /*------------------------------------------------------+
42 Get the site level values for so_organization_id
43 for the OE responsibility of operating unit opunit.
44 Only one row should be returned. If no row is returned,
45 use the profile value returned by bmlcci (site_level_org_id)
46 +-------------------------------------------------------*/
47
48 open validation_org;
49
50 fetch validation_org into lvalue;
51 lOldValue := lValue;
52
53 while validation_org%found
54 loop
55 fetch validation_org into lvalue;
56 if validation_org%rowcount > 1 then
57 if lOldValue <> lvalue then
58 raise multiorg_error;
59 end if;
60 end if;
61 end loop;
62 if validation_org%rowcount = 0 then
63 lValidOrg := site_level_org_id;
64 else
65 lValidOrg := to_number(lOldvalue);
66 end if;
67 close validation_org;
68 return (lValidOrg);
69 exception
70 when others then
71 raise multiorg_error;
72 end get_validation_org;
73
74
75 function bmldite_load_item (
76 org_id in number,
77 ci_delimiter in out VARCHAR2,
78 l_item_type in VARCHAR2,
79 error_message out VARCHAR2,
80 message_name out VARCHAR2,
81 table_name out VARCHAR2)
82 return integer
83 is
84 multiorg_error EXCEPTION;
85 load_error EXCEPTION;
86 dupl_error EXCEPTION;
87 loop_other_error EXCEPTION;
88 del_error EXCEPTION;
89 segment_name_error EXCEPTION;
90 flex_error EXCEPTION;
91 seg_del VARCHAR2(1);
92 ci_del VARCHAR2(1);
93 dummy_name VARCHAR2(30);
94 dupl_total_stmt VARCHAR2(6000);
95 dupl_tmp_stmt VARCHAR2(50);
96 del_len NUMBER;
97 method NUMBER;
98 loop_ctr NUMBER;
99 hold_next_seq NUMBER;
100 org_buf NUMBER;
101 org_method NUMBER;
102 stmt_num NUMBER;
103 fnd_size NUMBER;
104 inv_id NUMBER;
105 o_id NUMBER;
106 rows_processed NUMBER;
107 dupl_cursor NUMBER;
108 CURSOR check_segment IS
109 SELECT distinct organization_id
110 FROM mtl_demand
111 WHERE config_group_id = USERENV('SESSIONID');
112 CURSOR fnd_check(l_org_id NUMBER) IS
113 select nvl(FV.MAXIMUM_SIZE,-99)
114 from BOM_PARAMETERS P,
115 FND_ID_FLEX_SEGMENTS FS,
116 FND_FLEX_VALUE_SETS FV
117 where P.ORGANIZATION_ID = l_org_id
118 and FS.ID_FLEX_CODE = 'MSTK'
119 and FS.ID_FLEX_NUM = 101
120 and FS.SEGMENT_NAME = P.CONFIG_SEGMENT_NAME
121 and FS.APPLICATION_ID = 401 /* INV */
122 and FS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID;
123 TYPE SIsegTYPE is TABLE of
124 mtl_system_items_interface.segment1%TYPE
125 index by BINARY_INTEGER;
126 seg SIsegTYPE;
127 ind BINARY_INTEGER;
128 BEGIN
129
130 /*
131 ** Check delimiter to ensure it is a length of one and that it
132 ** isn't the same as the item delimiter value.
133 */
134
135 /* ATOPUTIL.info('In BOMPLDCI'); */
136 stmt_num := 5;
137 select length(ci_delimiter) into del_len from dual;
138 if (del_len <> 1 ) then
139 raise del_error;
140 else
141 ci_del := substrb(ci_delimiter,1,1);
142 end if;
143
144 table_name := 'FND_ID_FLEX_STRUCTURES ';
145 stmt_num := 10;
146 select concatenated_segment_delimiter
147 into seg_del
148 from fnd_id_flex_structures
149 where application_id = 401
150 and id_flex_code = 'MSTK'
151 and id_flex_num = 101;
152
153 if (seg_del = ci_del) then
154 raise del_error;
155 end if;
156
157 if (ci_del = ' ') then
158 ci_del := '';
159 end if;
160
161 /*
162 ** Check to see if the config_segment_name exists in bom_parameters
163 ** for the given organizations
164 */
165 table_name := 'BOM_PARAMETERS';
166 stmt_num :=15;
167
168 IF org_id = -1 THEN
169 stmt_num := 16;
170 OPEN check_segment;
171 LOOP
172 stmt_num := 17;
173 FETCH check_segment INTO org_buf;
174 EXIT WHEN check_segment%NOTFOUND;
175 stmt_num := 18;
176 select config_segment_name into dummy_name
177 from bom_parameters
178 where organization_id = org_buf;
179 IF dummy_name is NULL THEN RAISE segment_name_error;
180 END IF;
181 OPEN fnd_check(org_buf);
182 FETCH fnd_check INTO fnd_size;
183 IF fnd_check%NOTFOUND THEN
184 raise flex_error;
185 ELSIF fnd_size = -99 THEN
186 raise flex_error;
187 END IF;
188
189 CLOSE fnd_check;
190
191 END LOOP;
192 CLOSE check_segment;
193 ELSE
194 stmt_num := 19;
195 select config_segment_name into dummy_name
196 from bom_parameters
197 where organization_id = org_id;
198 IF dummy_name is NULL THEN RAISE segment_name_error;
199 END IF;
200 OPEN fnd_check(org_id);
201 FETCH fnd_check INTO fnd_size;
202 IF fnd_check%NOTFOUND THEN
203 raise flex_error;
204 ELSIF fnd_size = -99 THEN
205 raise flex_error;
206 END IF;
207 CLOSE fnd_check;
208
209 END IF;
210
211
212 /*
213 ** Insert a row into the new mtl_system_items_interface table.
214 */
215
216 table_name := 'INSERT MTL_SYSTEM_ITEMS_IF ';
217 stmt_num := 20;
218 insert into MTL_SYSTEM_ITEMS_INTERFACE
219 (INVENTORY_ITEM_ID,
220 ORGANIZATION_ID,
221 LAST_UPDATE_DATE,
222 LAST_UPDATED_BY,
223 CREATION_DATE,
224 CREATED_BY,
225 LAST_UPDATE_LOGIN,
226 SEGMENT1,
227 SEGMENT2,
228 SEGMENT3,
229 SEGMENT4,
230 SEGMENT5,
231 SEGMENT6,
232 SEGMENT7,
233 SEGMENT8,
234 SEGMENT9,
235 SEGMENT10,
236 SEGMENT11,
237 SEGMENT12,
238 SEGMENT13,
239 SEGMENT14,
240 SEGMENT15,
241 SEGMENT16,
242 SEGMENT17,
243 SEGMENT18,
244 SEGMENT19,
245 SEGMENT20,
246 ATP_FLAG,
247 MRP_PLANNING_CODE,
248 REPETITIVE_PLANNING_FLAG,
249 SHIPPABLE_ITEM_FLAG,
250 CUSTOMER_ORDER_FLAG,
251 INTERNAL_ORDER_FLAG,
252 BUILD_IN_WIP_FLAG,
253 PICK_COMPONENTS_FLAG,
254 REPLENISH_TO_ORDER_FLAG,
255 BASE_ITEM_ID,
256 DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
257 BOM_ENABLED_FLAG,
258 BOM_ITEM_TYPE,
259 AUTO_CREATED_CONFIG_FLAG,
260 DEMAND_SOURCE_LINE,
261 DEMAND_SOURCE_TYPE,
262 DEMAND_SOURCE_HEADER_ID,
263 COPY_ITEM_ID,
264 SET_ID,
265 ITEM_CATALOG_GROUP_ID,
266 INVENTORY_ITEM_FLAG,
267 STOCK_ENABLED_FLAG,
268 MTL_TRANSACTIONS_ENABLED_FLAG,
269 SO_TRANSACTIONS_FLAG,
270 RESERVABLE_TYPE,
271 REVISION,
272 ITEM_TYPE,
273 CUSTOMER_ORDER_ENABLED_FLAG,
274 INTERNAL_ORDER_ENABLED_FLAG
275 )
276 select MTL_SYSTEM_ITEMS_S.NEXTVAL,
277 D.ORGANIZATION_ID,
278 SYSDATE, /* LAST_UPDATE_DATE */
279 1, /* LAST_UPDATED_BY */
280 SYSDATE, /* CREATION_DATE */
281 1, /* CREATED_BY */
282 get_validation_org(SL.ORG_ID, org_id), /* last_update_login = validation_org */
283 /* config_number_method_type:
284 1 = Append with next sequence
285 2 = Replace with next sequence
286 3 = Replace with order header num/line num/delivery */
287 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT1',
288 decode(P.CONFIG_NUMBER_METHOD_TYPE,
289 1,SUBSTRB(S.SEGMENT1,1,decode(GREATEST(FV.MAXIMUM_SIZE,
290 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
291 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
292 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
293 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
294 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
295 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
296 S.SEGMENT1),S.SEGMENT1),
297 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT2',
298 decode(P.CONFIG_NUMBER_METHOD_TYPE,
299 1,SUBSTRB(S.SEGMENT2,1,decode(GREATEST(FV.MAXIMUM_SIZE,
300 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
301 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
302 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
303 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
304 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
305 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
306 S.SEGMENT2),S.SEGMENT2),
307 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT3',
308 decode(P.CONFIG_NUMBER_METHOD_TYPE,
309 1,SUBSTRB(S.SEGMENT3,1,decode(GREATEST(FV.MAXIMUM_SIZE,
310 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
311 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
312 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
313 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
314 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
315 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
316 S.SEGMENT3),S.SEGMENT3),
317 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT4',
318 decode(P.CONFIG_NUMBER_METHOD_TYPE,
319 1,SUBSTRB(S.SEGMENT4,1,decode(GREATEST(FV.MAXIMUM_SIZE,
320 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
321 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
322 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
323 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
324 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
325 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
326 S.SEGMENT4),S.SEGMENT4),
327 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT5',
328 decode(P.CONFIG_NUMBER_METHOD_TYPE,
329 1,SUBSTRB(S.SEGMENT5,1,decode(GREATEST(FV.MAXIMUM_SIZE,
330 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
331 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
332 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
333 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
334 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
335 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
336 S.SEGMENT5),S.SEGMENT5),
337 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT6',
338 decode(P.CONFIG_NUMBER_METHOD_TYPE,
339 1,SUBSTRB(S.SEGMENT6,1,decode(GREATEST(FV.MAXIMUM_SIZE,
340 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
341 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
342 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
343 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
344 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
345 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
346 S.SEGMENT6),S.SEGMENT6),
347 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT7',
348 decode(P.CONFIG_NUMBER_METHOD_TYPE,
349 1,SUBSTRB(S.SEGMENT7,1,decode(GREATEST(FV.MAXIMUM_SIZE,
350 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
351 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
352 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
353 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
354 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
355 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
356 S.SEGMENT7),S.SEGMENT7),
357 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT8',
358 decode(P.CONFIG_NUMBER_METHOD_TYPE,
359 1,SUBSTRB(S.SEGMENT8,1,decode(GREATEST(FV.MAXIMUM_SIZE,
360 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
361 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
362 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
363 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
364 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
365 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
366 S.SEGMENT8),S.SEGMENT8),
367 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT9',
368 decode(P.CONFIG_NUMBER_METHOD_TYPE,
369 1,SUBSTRB(S.SEGMENT9,1,decode(GREATEST(FV.MAXIMUM_SIZE,
370 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
371 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
372 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
373 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
374 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
375 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
376 S.SEGMENT9),S.SEGMENT9),
377 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT10',
378 decode(P.CONFIG_NUMBER_METHOD_TYPE,
379 1,SUBSTRB(S.SEGMENT10,1,decode(GREATEST(FV.MAXIMUM_SIZE,
380 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
381 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
382 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
383 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
384 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
385 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
386 S.SEGMENT10),S.SEGMENT10),
387 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT11',
388 decode(P.CONFIG_NUMBER_METHOD_TYPE,
389 1,SUBSTRB(S.SEGMENT11,1,decode(GREATEST(FV.MAXIMUM_SIZE,
390 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
391 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
392 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
393 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
394 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
395 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
396 S.SEGMENT11),S.SEGMENT11),
397 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT12',
398 decode(P.CONFIG_NUMBER_METHOD_TYPE,
399 1,SUBSTRB(S.SEGMENT12,1,decode(GREATEST(FV.MAXIMUM_SIZE,
400 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
401 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
402 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
403 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
404 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
405 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
406 S.SEGMENT12),S.SEGMENT12),
407 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT13',
408 decode(P.CONFIG_NUMBER_METHOD_TYPE,
409 1,SUBSTRB(S.SEGMENT13,1,decode(GREATEST(FV.MAXIMUM_SIZE,
410 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
411 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
412 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
413 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
414 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
415 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
416 S.SEGMENT13),S.SEGMENT13),
417 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT14',
418 decode(P.CONFIG_NUMBER_METHOD_TYPE,
419 1,SUBSTRB(S.SEGMENT14,1,decode(GREATEST(FV.MAXIMUM_SIZE,
420 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
421 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
422 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
423 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
424 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
425 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
426 S.SEGMENT14),S.SEGMENT14),
427 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT15',
428 decode(P.CONFIG_NUMBER_METHOD_TYPE,
429 1,SUBSTRB(S.SEGMENT15,1,decode(GREATEST(FV.MAXIMUM_SIZE,
430 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
431 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
432 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
433 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
434 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
435 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
436 S.SEGMENT15),S.SEGMENT15),
437 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT16',
438 decode(P.CONFIG_NUMBER_METHOD_TYPE,
439 1,SUBSTRB(S.SEGMENT16,1,decode(GREATEST(FV.MAXIMUM_SIZE,
440 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
441 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
442 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
443 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
444 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
445 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
446 S.SEGMENT16),S.SEGMENT16),
447 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT17',
448 decode(P.CONFIG_NUMBER_METHOD_TYPE,
449 1,SUBSTRB(S.SEGMENT17,1,decode(GREATEST(FV.MAXIMUM_SIZE,
450 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
451 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
452 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
453 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
454 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
455 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
456 S.SEGMENT17),S.SEGMENT17),
457 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT18',
458 decode(P.CONFIG_NUMBER_METHOD_TYPE,
459 1,SUBSTRB(S.SEGMENT18,1,decode(GREATEST(FV.MAXIMUM_SIZE,
460 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
461 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
462 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
463 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
464 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
465 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
466 S.SEGMENT18),S.SEGMENT18),
467 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT19',
468 decode(P.CONFIG_NUMBER_METHOD_TYPE,
469 1,SUBSTRB(S.SEGMENT19,1,decode(GREATEST(FV.MAXIMUM_SIZE,
470 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
471 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
472 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
473 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1 || ci_del || D.USER_LINE_NUM,
474 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
475 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
476 S.SEGMENT19),S.SEGMENT19),
477 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT20',
478 decode(P.CONFIG_NUMBER_METHOD_TYPE,
479 1,SUBSTRB(S.SEGMENT20,1,decode(GREATEST(FV.MAXIMUM_SIZE,
480 40),40,FV.MAXIMUM_SIZE-1-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),39-length(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL))) ||ci_del||
481 TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
482 2, TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
483 3, DECODE(D.USER_DELIVERY, NULL, L.SEGMENT1|| ci_del || D.USER_LINE_NUM,
484 L.SEGMENT1|| ci_del || D.USER_LINE_NUM || ci_del || D.USER_DELIVERY),
485 4, BOMPCFGI.user_item_number(D.INVENTORY_ITEM_ID),
486 S.SEGMENT20),S.SEGMENT20),
487 'N', /* ATP_FLAG */
488 S.MRP_PLANNING_CODE, /* MRP_PLANNING_CODE */
489 S.REPETITIVE_PLANNING_FLAG, /* REPETITIVE_PLANNING_FLAG */
490 'Y', /* SHIPPABLE_ITEM_FLAG = YES */
491 'N', /* CUSTOMER_ORDER_FLAG = NO */
492 'N', /* INTERNAL_ORDER_FLAG = NO */
493 'Y', /* BUILD_IN_WIP_FLAG = YES */
494 'N', /* PICK_COMPONENTS_FLAG */
495 'Y', /* REPLENISH_TO_ORDER_FLAG = YES */
496 D.INVENTORY_ITEM_ID, /* BASE_ITEM_ID = Model's Item ID */
497 'Y', /* DEFAULT_INCLUDE_IN_ROLLUP_FLAG = YES */
498 'Y', /* BOM_ENABLED_FLAG = Yes */
499 4, /* BOM_ITEM_TYPE = Standard */
500 'Y', /* AUTO_CREATED_CONFIG_FLAG = YES */
501 D.DEMAND_SOURCE_LINE, /* DEMAND_SOURCE_LINE */
502 D.DEMAND_SOURCE_TYPE, /* DEMAND_SOURCE_TYPE */
503 D.DEMAND_SOURCE_HEADER_ID, /* DEMAND_SOURCE_HEADER_ID */
504 D.INVENTORY_ITEM_ID, /* COPY_ITEM_ID */
505 USERENV('SESSIONID'), /* SET_ID */
506 S.ITEM_CATALOG_GROUP_ID,
507 'Y',
508 'Y',
509 'Y', /* MTL_TRANSACTIONS_ENABLED_FLAG */
510 'Y', /* SO_TRANSACTIONS_FLAG */
511 1, /* RESERVABLE_TYPE */
512 MP.STARTING_REVISION, /* REVISION */
513 l_item_type,
514 'N',
515 'N'
516 from MTL_DEMAND D,
517 MTL_SYSTEM_ITEMS S,
518 BOM_PARAMETERS P,
519 MTL_SALES_ORDERS L,
520 SO_LINES_ALL SL,
521 FND_ID_FLEX_SEGMENTS FS,
522 FND_FLEX_VALUE_SETS FV,
523 MTL_PARAMETERS MP
524 where D.config_group_id = USERENV('SESSIONID')
525 and D.ORGANIZATION_ID = S.ORGANIZATION_ID
526 and D.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
527 and D.DUPLICATED_CONFIG_ITEM_ID is NULL
528 and D.DUPLICATED_CONFIG_DEMAND_ID is NULL
529 and P.ORGANIZATION_ID = D.ORGANIZATION_ID
530 and L.SALES_ORDER_ID = D.DEMAND_SOURCE_HEADER_ID
531 and SL.INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID
532 and SL.LINE_ID = D.DEMAND_SOURCE_LINE
533 and FS.ID_FLEX_CODE = 'MSTK'
534 and FS.ID_FLEX_NUM = 101
535 and FS.SEGMENT_NAME = P.CONFIG_SEGMENT_NAME
536 and FS.APPLICATION_ID = 401 /* INV */
537 and FS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
538 and MP.ORGANIZATION_ID = S.ORGANIZATION_ID;
539
540 rows_processed := SQL%ROWCOUNT;
541 /* ATOPUTIL.info('Rows Inserted into Intf:' || rows_processed); */
542
543 IF SQL%ROWCOUNT > 0 THEN
544 /* Determine if the resulting configuration name is duplicated on
545 the data base. */
546 loop_ctr := 0;
547 <<dupl_loop>>
548 LOOP
549 IF loop_ctr = 10 THEN RAISE dupl_error;
550 END IF;
551 loop_ctr := loop_ctr + 1;
552
553
554 for ind in 1..20 loop
555 seg(ind) := 'SEGMENT' || ind ;
556 /* ATOPUTIL.info(seg(ind)); */
557 end loop;
558
559 stmt_num := 21;
560 OPEN check_segment;
561 LOOP
562 stmt_num := 22;
563 /*
564 ** Get the config segment name corresponding to the org_buf
565 */
566 FETCH check_segment INTO org_buf;
567 IF check_segment%NOTFOUND THEN
568 EXIT dupl_loop;
569 END IF;
570
571 stmt_num := 23;
572 select application_column_name into dummy_name
573 from fnd_id_flex_segments fs, bom_parameters p
574 where p.organization_id = org_buf
575 and fs.id_flex_code = 'MSTK'
576 and fs.id_flex_num = 101
577 and fs.segment_name = p.config_segment_name
578 and fs.application_id = 401;
579 IF dummy_name is NULL THEN RAISE segment_name_error;
580 END IF;
581
582 /* ATOPUTIL.info('Org_id:' || org_buf);
583 ATOPUTIL.info('Config_segment:' || dummy_name); */
584 /*
585 ** Prepare cursor to select for duplicate item name check
586 */
587 <<dupl_per_org_loop>>
588 LOOP
589 /* ATOPUTIL.info('Starting dupl_check loop'); */
590 stmt_num := 24;
591
592 dupl_total_stmt := 'select I.inventory_item_id,I.organization_id from mtl_system_items_interface I where I.SET_ID = TO_CHAR(USERENV(''SESSIONID'')) and I.organization_id = :org_buf';
593
594 dupl_total_stmt := dupl_total_stmt || ' ' || 'and exists((select ''exists'' from mtl_system_items S2 where S2.inventory_item_id <> I.inventory_item_id';
595
596 ind := 0;
597 stmt_num := 25;
598 for ind in 1..20 loop
599
600 if (seg(ind) = dummy_name) then
601
602 select decode(ind,1,'and S2.segment1 = I.segment1',
603 2,'and S2.segment2 = I.segment2',
604 3,'and S2.segment3 = I.segment3',
605 4,'and S2.segment4 = I.segment4',
606 5,'and S2.segment5 = I.segment5',
607 6,'and S2.segment6 = I.segment6',
608 7,'and S2.segment7 = I.segment7',
609 8,'and S2.segment8 = I.segment8',
610 9,'and S2.segment9 = I.segment9',
611 10,'and S2.segment10 = I.segment10',
612 11,'and S2.segment11 = I.segment11',
613 12,'and S2.segment12 = I.segment12',
614 13,'and S2.segment13 = I.segment13',
615 14,'and S2.segment14 = I.segment14',
616 15,'and S2.segment15 = I.segment15',
617 16,'and S2.segment16 = I.segment16',
618 17,'and S2.segment17 = I.segment17',
619 18,'and S2.segment18 = I.segment18',
620 19,'and S2.segment19 = I.segment19',
621 20,'and S2.segment20 = I.segment20')
622 into dupl_tmp_stmt
623 from dual;
624 dupl_total_stmt := dupl_total_stmt || ' ' || dupl_tmp_stmt ;
625
626 else
627
628 select decode(ind,1,'and nvl(S2.segment1,'' '') = nvl(I.segment1,'' '')',
629 2,'and nvl(S2.segment2,'' '') = nvl(I.segment2,'' '')',
630 3,'and nvl(S2.segment3,'' '') = nvl(I.segment3,'' '')',
631 4,'and nvl(S2.segment4,'' '') = nvl(I.segment4,'' '')',
632 5,'and nvl(S2.segment5,'' '') = nvl(I.segment5,'' '')',
633 6,'and nvl(S2.segment6,'' '') = nvl(I.segment6,'' '')',
634 7,'and nvl(S2.segment7,'' '') = nvl(I.segment7,'' '')',
635 8,'and nvl(S2.segment8,'' '') = nvl(I.segment8,'' '')',
636 9,'and nvl(S2.segment9,'' '') = nvl(I.segment9,'' '')',
637 10,'and nvl(S2.segment10,'' '') = nvl(I.segment10,'' '')',
638 11,'and nvl(S2.segment11,'' '') = nvl(I.segment11,'' '')',
639 12,'and nvl(S2.segment12,'' '') = nvl(I.segment12,'' '')',
640 13,'and nvl(S2.segment13,'' '') = nvl(I.segment13,'' '')',
641 14,'and nvl(S2.segment14,'' '') = nvl(I.segment14,'' '')',
642 15,'and nvl(S2.segment15,'' '') = nvl(I.segment15,'' '')',
643 16,'and nvl(S2.segment16,'' '') = nvl(I.segment16,'' '')',
644 17,'and nvl(S2.segment17,'' '') = nvl(I.segment17,'' '')',
645 18,'and nvl(S2.segment18,'' '') = nvl(I.segment18,'' '')',
646 19,'and nvl(S2.segment19,'' '') = nvl(I.segment19,'' '')',
647 20,'and nvl(S2.segment20,'' '') = nvl(I.segment20,'' '')')
648 into dupl_tmp_stmt
649 from dual;
650 dupl_total_stmt := dupl_total_stmt || ' ' || dupl_tmp_stmt;
651
652 end if;
653 end loop;
654
655 stmt_num := 26;
656 dupl_total_stmt := dupl_total_stmt || ' '|| ') UNION (';
657
658 /*
659 ** Prepare cursor to select for duplicate item name check
660 */
661 stmt_num := 27;
662
663 dupl_total_stmt := dupl_total_stmt || 'select ''exists'' from mtl_system_items_interface I2 where I2.SET_ID = TO_CHAR(USERENV(''SESSIONID'')) and I2.inventory_item_id <> I.inventory_item_id';
664
665 ind := 0;
666 stmt_num := 28;
667 for ind in 1..20 loop
668
669 if (seg(ind) = dummy_name) then
670
671 select decode(ind,1,'and I2.segment1 = I.segment1',
672 2,'and I2.segment2 = I.segment2',
673 3,'and I2.segment3 = I.segment3',
674 4,'and I2.segment4 = I.segment4',
675 5,'and I2.segment5 = I.segment5',
676 6,'and I2.segment6 = I.segment6',
677 7,'and I2.segment7 = I.segment7',
678 8,'and I2.segment8 = I.segment8',
679 9,'and I2.segment9 = I.segment9',
680 10,'and I2.segment10 = I.segment10',
681 11,'and I2.segment11 = I.segment11',
682 12,'and I2.segment12 = I.segment12',
683 13,'and I2.segment13 = I.segment13',
684 14,'and I2.segment14 = I.segment14',
685 15,'and I2.segment15 = I.segment15',
686 16,'and I2.segment16 = I.segment16',
687 17,'and I2.segment17 = I.segment17',
688 18,'and I2.segment18 = I.segment18',
689 19,'and I2.segment19 = I.segment19',
690 20,'and I2.segment20 = I.segment20')
691 into dupl_tmp_stmt
692 from dual;
693 dupl_total_stmt := dupl_total_stmt || ' ' || dupl_tmp_stmt ;
694
695 else
696
697 select decode(ind,1,'and nvl(I2.segment1,'' '') = nvl(I.segment1,'' '')',
698 2,'and nvl(I2.segment2,'' '') = nvl(I.segment2,'' '')',
699 3,'and nvl(I2.segment3,'' '') = nvl(I.segment3,'' '')',
700 4,'and nvl(I2.segment4,'' '') = nvl(I.segment4,'' '')',
701 5,'and nvl(I2.segment5,'' '') = nvl(I.segment5,'' '')',
702 6,'and nvl(I2.segment6,'' '') = nvl(I.segment6,'' '')',
703 7,'and nvl(I2.segment7,'' '') = nvl(I.segment7,'' '')',
704 8,'and nvl(I2.segment8,'' '') = nvl(I.segment8,'' '')',
705 9,'and nvl(I2.segment9,'' '') = nvl(I.segment9,'' '')',
706 10,'and nvl(I2.segment10,'' '') = nvl(I.segment10,'' '')',
707 11,'and nvl(I2.segment11,'' '') = nvl(I.segment11,'' '')',
708 12,'and nvl(I2.segment12,'' '') = nvl(I.segment12,'' '')',
709 13,'and nvl(I2.segment13,'' '') = nvl(I.segment13,'' '')',
710 14,'and nvl(I2.segment14,'' '') = nvl(I.segment14,'' '')',
711 15,'and nvl(I2.segment15,'' '') = nvl(I.segment15,'' '')',
712 16,'and nvl(I2.segment16,'' '') = nvl(I.segment16,'' '')',
713 17,'and nvl(I2.segment17,'' '') = nvl(I.segment17,'' '')',
714 18,'and nvl(I2.segment18,'' '') = nvl(I.segment18,'' '')',
715 19,'and nvl(I2.segment19,'' '') = nvl(I.segment19,'' '')',
716 20,'and nvl(I2.segment20,'' '') = nvl(I.segment20,'' '')')
717 into dupl_tmp_stmt
718 from dual;
719 dupl_total_stmt := dupl_total_stmt || ' ' || dupl_tmp_stmt;
720
721 end if;
722 end loop;
723
724 stmt_num := 29;
725
726 dupl_total_stmt := dupl_total_stmt || '))';
727
728 -- dbms_output.put_line(dupl_total_stmt);
729 /* ATOPUTIL.info(dupl_total_stmt); */
730
731
732 stmt_num := 30;
733 dupl_cursor := dbms_sql.open_cursor;
734
735 stmt_num := 31;
736 dbms_sql.parse(dupl_cursor,dupl_total_stmt,dbms_sql.v7);
737
738 /*
739 ** Here we are defining the position of the select columns
740 */
741
742 stmt_num := 32;
743 dbms_sql.define_column(dupl_cursor,1,inv_id);
744
745 stmt_num := 33;
746 dbms_sql.define_column(dupl_cursor,2,o_id);
747
748 stmt_num := 34;
749 dbms_sql.bind_variable(dupl_cursor,'org_buf',org_buf);
750
751 /*
752 ** Get all the duplicated rows into the tables
753 ** We execute the sql statement
754 */
755
756 stmt_num :=35;
757 rows_processed := dbms_sql.execute(dupl_cursor);
758
759 stmt_num := 36;
760
761 if dbms_sql.fetch_rows(dupl_cursor) = 0 then
762 /* ATOPUTIL.info('Quitting fetch_rows loop'); */
763 EXIT dupl_per_org_loop;
764 else
765 stmt_num := 101;
766 dbms_sql.column_value(dupl_cursor,1,inv_id);
767 dbms_sql.column_value(dupl_cursor,2,o_id);
768 /* ATOPUTIL.info('Duplicate Item:' || inv_id ||', Org_id:'|| o_id ); */
769
770 select config_number_method_type into org_method
771 from bom_parameters
772 where organization_id = org_buf;
773
774 stmt_num := 102;
775 if org_method = 4 THEN RAISE dupl_error;
776 end if;
777
778 if dbms_sql.is_open(dupl_cursor) then
779 dbms_sql.close_cursor(dupl_cursor);
780 end if;
781
782 stmt_num := 37;
783 select TO_CHAR(MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL)
784 into hold_next_seq
785 from dual;
786
787 /* ATOPUTIL.info('About to update MSI_Intf'); */
788
789 table_name := 'UPDATE MTL_SYSTEM_ITEMS_IF ';
790 stmt_num := 40;
791 update MTL_SYSTEM_ITEMS_INTERFACE I
792 set (SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
793 SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,
794 SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,
795 SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20)
796 = (select
797 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT1',
798 decode(P.CONFIG_NUMBER_METHOD_TYPE,
799 1,SUBSTRB(S.SEGMENT1,1,decode(GREATEST(FV.MAXIMUM_SIZE,
800 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
801 TO_CHAR(hold_next_seq),
802 2, TO_CHAR(hold_next_seq),
803 3, DECODE(D.USER_DELIVERY, NULL,
804 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
805 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
806 TO_CHAR(hold_next_seq)), S.SEGMENT1),S.SEGMENT1),
807 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT2',
808 decode(P.CONFIG_NUMBER_METHOD_TYPE,
809 1,SUBSTRB(S.SEGMENT2,1,decode(GREATEST(FV.MAXIMUM_SIZE,
810 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
811 TO_CHAR(hold_next_seq),
812 2, TO_CHAR(hold_next_seq),
813 3, DECODE(D.USER_DELIVERY, NULL,
814 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
815 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
816 TO_CHAR(hold_next_seq)), S.SEGMENT2),S.SEGMENT2),
817 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT3',
818 decode(P.CONFIG_NUMBER_METHOD_TYPE,
819 1,SUBSTRB(S.SEGMENT3,1,decode(GREATEST(FV.MAXIMUM_SIZE,
820 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
821 TO_CHAR(hold_next_seq),
822 2, TO_CHAR(hold_next_seq),
823 3, DECODE(D.USER_DELIVERY, NULL,
824 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
825 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
826 TO_CHAR(hold_next_seq)), S.SEGMENT3),S.SEGMENT3),
827 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT4',
828 decode(P.CONFIG_NUMBER_METHOD_TYPE,
829 1,SUBSTRB(S.SEGMENT4,1,decode(GREATEST(FV.MAXIMUM_SIZE,
830 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
831 TO_CHAR(hold_next_seq),
832 2, TO_CHAR(hold_next_seq),
833 3, DECODE(D.USER_DELIVERY, NULL,
834 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
835 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
836 TO_CHAR(hold_next_seq)), S.SEGMENT4),S.SEGMENT4),
837 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT5',
838 decode(P.CONFIG_NUMBER_METHOD_TYPE,
839 1,SUBSTRB(S.SEGMENT5,1,decode(GREATEST(FV.MAXIMUM_SIZE,
840 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
841 TO_CHAR(hold_next_seq),
842 2, TO_CHAR(hold_next_seq),
843 3, DECODE(D.USER_DELIVERY, NULL,
844 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
845 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
846 TO_CHAR(hold_next_seq)), S.SEGMENT5),S.SEGMENT5),
847 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT6',
848 decode(P.CONFIG_NUMBER_METHOD_TYPE,
849 1,SUBSTRB(S.SEGMENT6,1,decode(GREATEST(FV.MAXIMUM_SIZE,
850 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
851 TO_CHAR(hold_next_seq),
852 2, TO_CHAR(hold_next_seq),
853 3, DECODE(D.USER_DELIVERY, NULL,
854 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
855 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
856 TO_CHAR(hold_next_seq)), S.SEGMENT6),S.SEGMENT6),
857 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT7',
858 decode(P.CONFIG_NUMBER_METHOD_TYPE,
859 1,SUBSTRB(S.SEGMENT7,1,decode(GREATEST(FV.MAXIMUM_SIZE,
860 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
861 TO_CHAR(hold_next_seq),
862 2, TO_CHAR(hold_next_seq),
863 3, DECODE(D.USER_DELIVERY, NULL,
864 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
865 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
866 TO_CHAR(hold_next_seq)), S.SEGMENT7),S.SEGMENT7),
867 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT8',
868 decode(P.CONFIG_NUMBER_METHOD_TYPE,
869 1,SUBSTRB(S.SEGMENT8,1,decode(GREATEST(FV.MAXIMUM_SIZE,
870 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
871 TO_CHAR(hold_next_seq),
872 2, TO_CHAR(hold_next_seq),
873 3, DECODE(D.USER_DELIVERY, NULL,
874 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
875 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
876 TO_CHAR(hold_next_seq)), S.SEGMENT8),S.SEGMENT8),
877 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT9',
878 decode(P.CONFIG_NUMBER_METHOD_TYPE,
879 1,SUBSTRB(S.SEGMENT9,1,decode(GREATEST(FV.MAXIMUM_SIZE,
880 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
881 TO_CHAR(hold_next_seq),
882 2, TO_CHAR(hold_next_seq),
883 3, DECODE(D.USER_DELIVERY, NULL,
884 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
885 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
886 TO_CHAR(hold_next_seq)), S.SEGMENT9),S.SEGMENT9),
887 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT10',
888 decode(P.CONFIG_NUMBER_METHOD_TYPE,
889 1,SUBSTRB(S.SEGMENT10,1,decode(GREATEST(FV.MAXIMUM_SIZE,
890 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
891 TO_CHAR(hold_next_seq),
892 2, TO_CHAR(hold_next_seq),
893 3, DECODE(D.USER_DELIVERY, NULL,
894 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
895 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
896 TO_CHAR(hold_next_seq)), S.SEGMENT10),S.SEGMENT10),
897 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT11',
898 decode(P.CONFIG_NUMBER_METHOD_TYPE,
899 1,SUBSTRB(S.SEGMENT11,1,decode(GREATEST(FV.MAXIMUM_SIZE,
900 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
901 TO_CHAR(hold_next_seq),
902 2, TO_CHAR(hold_next_seq),
903 3, DECODE(D.USER_DELIVERY, NULL,
904 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
905 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
906 TO_CHAR(hold_next_seq)), S.SEGMENT11),S.SEGMENT11),
907 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT12',
908 decode(P.CONFIG_NUMBER_METHOD_TYPE,
909 1,SUBSTRB(S.SEGMENT12,1,decode(GREATEST(FV.MAXIMUM_SIZE,
910 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
911 TO_CHAR(hold_next_seq),
912 2, TO_CHAR(hold_next_seq),
913 3, DECODE(D.USER_DELIVERY, NULL,
914 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
915 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
916 TO_CHAR(hold_next_seq)), S.SEGMENT12),S.SEGMENT12),
917 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT13',
918 decode(P.CONFIG_NUMBER_METHOD_TYPE,
919 1,SUBSTRB(S.SEGMENT13,1,decode(GREATEST(FV.MAXIMUM_SIZE,
920 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
921 TO_CHAR(hold_next_seq),
922 2, TO_CHAR(hold_next_seq),
923 3, DECODE(D.USER_DELIVERY, NULL,
924 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
925 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
926 TO_CHAR(hold_next_seq)), S.SEGMENT13),S.SEGMENT13),
927 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT14',
928 decode(P.CONFIG_NUMBER_METHOD_TYPE,
929 1,SUBSTRB(S.SEGMENT14,1,decode(GREATEST(FV.MAXIMUM_SIZE,
930 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
931 TO_CHAR(hold_next_seq),
932 2, TO_CHAR(hold_next_seq),
933 3, DECODE(D.USER_DELIVERY, NULL,
934 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
935 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
936 TO_CHAR(hold_next_seq)), S.SEGMENT14),S.SEGMENT14),
937 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT15',
938 decode(P.CONFIG_NUMBER_METHOD_TYPE,
939 1,SUBSTRB(S.SEGMENT15,1,decode(GREATEST(FV.MAXIMUM_SIZE,
940 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
941 TO_CHAR(hold_next_seq),
942 2, TO_CHAR(hold_next_seq),
943 3, DECODE(D.USER_DELIVERY, NULL,
944 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
945 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
946 TO_CHAR(hold_next_seq)), S.SEGMENT15),S.SEGMENT15),
947 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT16',
948 decode(P.CONFIG_NUMBER_METHOD_TYPE,
949 1,SUBSTRB(S.SEGMENT16,1,decode(GREATEST(FV.MAXIMUM_SIZE,
950 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
951 TO_CHAR(hold_next_seq),
952 2, TO_CHAR(hold_next_seq),
953 3, DECODE(D.USER_DELIVERY, NULL,
954 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
955 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
956 TO_CHAR(hold_next_seq)), S.SEGMENT16),S.SEGMENT16),
957 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT17',
958 decode(P.CONFIG_NUMBER_METHOD_TYPE,
959 1,SUBSTRB(S.SEGMENT17,1,decode(GREATEST(FV.MAXIMUM_SIZE,
960 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
961 TO_CHAR(hold_next_seq),
962 2, TO_CHAR(hold_next_seq),
963 3, DECODE(D.USER_DELIVERY, NULL,
964 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
965 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
966 TO_CHAR(hold_next_seq)), S.SEGMENT17),S.SEGMENT17),
967 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT18',
968 decode(P.CONFIG_NUMBER_METHOD_TYPE,
969 1,SUBSTRB(S.SEGMENT18,1,decode(GREATEST(FV.MAXIMUM_SIZE,
970 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
971 TO_CHAR(hold_next_seq),
972 2, TO_CHAR(hold_next_seq),
973 3, DECODE(D.USER_DELIVERY, NULL,
974 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
975 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
976 TO_CHAR(hold_next_seq)), S.SEGMENT18),S.SEGMENT18),
977 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT19',
978 decode(P.CONFIG_NUMBER_METHOD_TYPE,
979 1,SUBSTRB(S.SEGMENT19,1,decode(GREATEST(FV.MAXIMUM_SIZE,
980 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
981 TO_CHAR(hold_next_seq),
982 2, TO_CHAR(hold_next_seq),
983 3, DECODE(D.USER_DELIVERY, NULL,
984 L.SEGMENT1 || ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
985 L.SEGMENT1 || ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
986 TO_CHAR(hold_next_seq)), S.SEGMENT19),S.SEGMENT19),
987 decode(FS.APPLICATION_COLUMN_NAME, 'SEGMENT20',
988 decode(P.CONFIG_NUMBER_METHOD_TYPE,
989 1,SUBSTRB(S.SEGMENT20,1,decode(GREATEST(FV.MAXIMUM_SIZE,
990 40),40,FV.MAXIMUM_SIZE-1-length(hold_next_seq),39-length(hold_next_seq))) ||ci_del||
991 TO_CHAR(hold_next_seq),
992 2, TO_CHAR(hold_next_seq),
993 3, DECODE(D.USER_DELIVERY, NULL,
994 L.SEGMENT1|| ci_del || D.USER_LINE_NUM || ci_del || TO_CHAR(hold_next_seq),
995 L.SEGMENT1|| ci_del || D.USER_LINE_NUM ||ci_del|| D.USER_DELIVERY || ci_del ||
996 TO_CHAR(hold_next_seq)), S.SEGMENT20),S.SEGMENT20)
997 from MTL_SYSTEM_ITEMS_INTERFACE I2,
998 MTL_SYSTEM_ITEMS S,
999 BOM_PARAMETERS P,
1000 MTL_SALES_ORDERS L,
1001 MTL_DEMAND D,
1002 FND_ID_FLEX_SEGMENTS FS,
1003 FND_FLEX_VALUE_SETS FV
1004 where I2.ORGANIZATION_ID = S.ORGANIZATION_ID
1005 and I2.BASE_ITEM_ID = S.INVENTORY_ITEM_ID
1006 and P.ORGANIZATION_ID = I2.ORGANIZATION_ID
1007 and D.DEMAND_SOURCE_LINE = I2.DEMAND_SOURCE_LINE
1008 and D.ORGANIZATION_ID = I2.ORGANIZATION_ID
1009 and D.DEMAND_SOURCE_TYPE = I2.DEMAND_SOURCE_TYPE
1010 and D.DEMAND_SOURCE_HEADER_ID = I2.DEMAND_SOURCE_HEADER_ID
1011 and D.INVENTORY_ITEM_ID = I2.BASE_ITEM_ID
1012 and D.DEMAND_SOURCE_HEADER_ID = L.SALES_ORDER_ID
1013 and D.PRIMARY_UOM_QUANTITY <> 0
1014 and FS.ID_FLEX_CODE = 'MSTK'
1015 and FS.ID_FLEX_NUM = 101
1016 and FS.SEGMENT_NAME = P.CONFIG_SEGMENT_NAME
1017 and FS.APPLICATION_ID = 401 /* INV */
1018 and FV.FLEX_VALUE_SET_ID = FS.FLEX_VALUE_SET_ID
1019 and I2.ORGANIZATION_ID = I.ORGANIZATION_ID
1020 and I2.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
1021 and I2.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID'))))
1022 where I.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')))
1023 and I.inventory_item_id = inv_id
1024 and I.organization_id = o_id;
1025 end if;
1026 END LOOP;
1027 END LOOP;
1028
1029 END LOOP;
1030
1031 END IF;
1032
1033 stmt_num := 60;
1034 if dbms_sql.is_open(dupl_cursor) then
1035 dbms_sql.close_cursor(dupl_cursor);
1036 end if;
1037
1038 /*
1039 ** Insert cost records for config items
1040 ** The cost organization id is either the organization id
1041 ** or the master organization id
1042 */
1043
1044 /* Insert a row into the cst_item_costs_interface table */
1045
1046 table_name := 'CST_ITEM_COSTS_INTERFACE';
1047 stmt_num := 70;
1048 insert into CST_ITEM_COSTS_INTERFACE
1049 (INVENTORY_ITEM_ID,
1050 ORGANIZATION_ID,
1051 COST_TYPE_ID,
1052 LAST_UPDATE_DATE,
1053 LAST_UPDATED_BY,
1054 CREATION_DATE,
1055 CREATED_BY,
1056 LAST_UPDATE_LOGIN,
1057 INVENTORY_ASSET_FLAG,
1058 LOT_SIZE,
1059 BASED_ON_ROLLUP_FLAG,
1060 SHRINKAGE_RATE,
1061 DEFAULTED_FLAG,
1062 COST_UPDATE_ID,
1063 PL_MATERIAL,
1064 PL_MATERIAL_OVERHEAD,
1065 PL_RESOURCE,
1066 PL_OUTSIDE_PROCESSING,
1067 PL_OVERHEAD,
1068 TL_MATERIAL,
1069 TL_MATERIAL_OVERHEAD,
1070 TL_RESOURCE,
1071 TL_OUTSIDE_PROCESSING,
1072 TL_OVERHEAD,
1073 MATERIAL_COST,
1074 MATERIAL_OVERHEAD_COST,
1075 RESOURCE_COST,
1076 OUTSIDE_PROCESSING_COST ,
1077 OVERHEAD_COST,
1078 PL_ITEM_COST,
1079 TL_ITEM_COST,
1080 ITEM_COST,
1081 UNBURDENED_COST ,
1082 BURDEN_COST,
1083 ATTRIBUTE_CATEGORY,
1084 ATTRIBUTE1,
1085 ATTRIBUTE2,
1086 ATTRIBUTE3,
1087 ATTRIBUTE4,
1088 ATTRIBUTE5,
1089 ATTRIBUTE6,
1090 ATTRIBUTE7,
1091 ATTRIBUTE8,
1092 ATTRIBUTE9,
1093 ATTRIBUTE10,
1094 ATTRIBUTE11,
1095 ATTRIBUTE12,
1096 ATTRIBUTE13,
1097 ATTRIBUTE14,
1098 ATTRIBUTE15
1099 )
1100 select SI.INVENTORY_ITEM_ID, /* INVENTORY_ITEM_ID */
1101 M.COST_ORGANIZATION_ID,
1102 C.COST_TYPE_ID,
1103 SYSDATE, /* LAST_UPDATE_DATE */
1104 -1, /* LAST_UPDATED_BY */
1105 SYSDATE, /* CREATION_DATE */
1106 -1, /* CREATED_BY */
1107 -1, /* LAST_UPDATE_LOGIN */
1108 C.INVENTORY_ASSET_FLAG,
1109 C.LOT_SIZE,
1110 C.BASED_ON_ROLLUP_FLAG,
1111 C.SHRINKAGE_RATE,
1112 C.DEFAULTED_FLAG,
1113 NULL, /* COST_UPDATE_ID */
1114 C.PL_MATERIAL,
1115 C.PL_MATERIAL_OVERHEAD,
1116 C.PL_RESOURCE,
1117 C.PL_OUTSIDE_PROCESSING,
1118 C.PL_OVERHEAD,
1119 C.TL_MATERIAL,
1120 C.TL_MATERIAL_OVERHEAD,
1121 C.TL_RESOURCE,
1122 C.TL_OUTSIDE_PROCESSING,
1123 C.TL_OVERHEAD,
1124 C.MATERIAL_COST,
1125 C.MATERIAL_OVERHEAD_COST,
1126 C.RESOURCE_COST,
1127 C.OUTSIDE_PROCESSING_COST ,
1128 C.OVERHEAD_COST,
1129 C.PL_ITEM_COST,
1130 C.TL_ITEM_COST,
1131 C.ITEM_COST,
1132 C.UNBURDENED_COST ,
1133 C.BURDEN_COST,
1134 C.ATTRIBUTE_CATEGORY,
1135 C.ATTRIBUTE1,
1136 C.ATTRIBUTE2,
1137 C.ATTRIBUTE3,
1138 C.ATTRIBUTE4,
1139 C.ATTRIBUTE5,
1140 C.ATTRIBUTE6,
1141 C.ATTRIBUTE7,
1142 C.ATTRIBUTE8,
1143 C.ATTRIBUTE9,
1144 C.ATTRIBUTE10,
1145 C.ATTRIBUTE11,
1146 C.ATTRIBUTE12,
1147 C.ATTRIBUTE13,
1148 C.ATTRIBUTE14,
1149 C.ATTRIBUTE15
1150 from
1151 MTL_PARAMETERS M,
1152 CST_ITEM_COSTS C,
1153 MTL_SYSTEM_ITEMS_INTERFACE SI
1154 where M.ORGANIZATION_ID = SI.ORGANIZATION_ID+0
1155 and C.ORGANIZATION_ID = M.ORGANIZATION_ID
1156 and C.INVENTORY_ITEM_ID = SI.COPY_ITEM_ID
1157 and C.COST_TYPE_ID = M.primary_cost_method
1158 and SI.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')));
1159
1160 /* Insert rows into the cst_item_cst_dtls_interface table */
1161
1162 table_name := 'CST_ITEM_CST_DTLS_INTERFACE';
1163 stmt_num := 80;
1164 insert into CST_ITEM_CST_DTLS_INTERFACE
1165 (INVENTORY_ITEM_ID,
1166 COST_TYPE_ID,
1167 LAST_UPDATE_DATE,
1168 LAST_UPDATED_BY,
1169 CREATION_DATE,
1170 CREATED_BY,
1171 LAST_UPDATE_LOGIN,
1172 ORGANIZATION_ID,
1173 OPERATION_SEQUENCE_ID,
1174 OPERATION_SEQ_NUM,
1175 DEPARTMENT_ID,
1176 LEVEL_TYPE,
1177 ACTIVITY_ID,
1178 RESOURCE_SEQ_NUM,
1179 RESOURCE_ID,
1180 RESOURCE_RATE,
1181 ITEM_UNITS,
1182 ACTIVITY_UNITS,
1183 USAGE_RATE_OR_AMOUNT,
1184 BASIS_TYPE,
1185 BASIS_RESOURCE_ID,
1186 BASIS_FACTOR,
1187 NET_YIELD_OR_SHRINKAGE_FACTOR,
1188 ITEM_COST,
1189 COST_ELEMENT_ID,
1190 ROLLUP_SOURCE_TYPE,
1191 ACTIVITY_CONTEXT,
1192 ATTRIBUTE_CATEGORY,
1193 ATTRIBUTE1,
1194 ATTRIBUTE2,
1195 ATTRIBUTE3,
1196 ATTRIBUTE4,
1197 ATTRIBUTE5,
1198 ATTRIBUTE6,
1199 ATTRIBUTE7,
1200 ATTRIBUTE8,
1201 ATTRIBUTE9,
1202 ATTRIBUTE10,
1203 ATTRIBUTE11,
1204 ATTRIBUTE12,
1205 ATTRIBUTE13,
1206 ATTRIBUTE14,
1207 ATTRIBUTE15
1208 )
1209 select SI.INVENTORY_ITEM_ID, /* INVENTORY_ITEM_ID */
1210 C.COST_TYPE_ID,
1211 SYSDATE, /* LAST_UPDATE_DATE */
1212 -1, /* LAST_UPDATED_BY */
1213 SYSDATE, /* CREATION_DATE */
1214 -1, /* CREATED_BY */
1215 -1, /* LAST_UPDATE_LOGIN */
1216 M.COST_ORGANIZATION_ID,
1217 C.OPERATION_SEQUENCE_ID,
1218 C.OPERATION_SEQ_NUM,
1219 C.DEPARTMENT_ID,
1220 C.LEVEL_TYPE,
1221 C.ACTIVITY_ID,
1222 C.RESOURCE_SEQ_NUM,
1223 C.RESOURCE_ID,
1224 C.RESOURCE_RATE,
1225 C.ITEM_UNITS,
1226 C.ACTIVITY_UNITS,
1227 C.USAGE_RATE_OR_AMOUNT,
1228 C.BASIS_TYPE,
1229 C.BASIS_RESOURCE_ID,
1230 C.BASIS_FACTOR,
1231 C.NET_YIELD_OR_SHRINKAGE_FACTOR,
1232 C.ITEM_COST,
1233 C.COST_ELEMENT_ID,
1234 C.ROLLUP_SOURCE_TYPE,
1235 C.ACTIVITY_CONTEXT,
1236 C.ATTRIBUTE_CATEGORY,
1237 C.ATTRIBUTE1,
1238 C.ATTRIBUTE2,
1239 C.ATTRIBUTE3,
1240 C.ATTRIBUTE4,
1241 C.ATTRIBUTE5,
1242 C.ATTRIBUTE6,
1243 C.ATTRIBUTE7,
1244 C.ATTRIBUTE8,
1245 C.ATTRIBUTE9,
1246 C.ATTRIBUTE10,
1247 C.ATTRIBUTE11,
1248 C.ATTRIBUTE12,
1249 C.ATTRIBUTE13,
1250 C.ATTRIBUTE14,
1251 C.ATTRIBUTE15
1252 from
1253 MTL_PARAMETERS M,
1254 CST_ITEM_COST_DETAILS C,
1255 MTL_SYSTEM_ITEMS_INTERFACE SI
1256 where M.ORGANIZATION_ID = SI.ORGANIZATION_ID+0
1257 and C.ORGANIZATION_ID = M.ORGANIZATION_ID
1258 and C.INVENTORY_ITEM_ID = SI.COPY_ITEM_ID
1259 and C.COST_TYPE_ID = M.primary_cost_method
1260 and C.ROLLUP_SOURCE_TYPE = 1 /* User Defined */
1261 and SI.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')));
1262
1263 /* Insert rows into the mtl_desc_elem_val_interface table */
1264
1265 table_name := 'MTL_DESC_ELEM_VAL_INTERFACE';
1266 stmt_num := 90;
1267 insert into MTL_DESC_ELEM_VAL_INTERFACE
1268 (INVENTORY_ITEM_ID,
1269 ELEMENT_NAME,
1270 LAST_UPDATE_DATE,
1271 LAST_UPDATED_BY,
1272 CREATION_DATE,
1273 CREATED_BY,
1274 LAST_UPDATE_LOGIN,
1275 ELEMENT_VALUE,
1276 DEFAULT_ELEMENT_FLAG,
1277 ELEMENT_SEQUENCE
1278 )
1279 select SI.INVENTORY_ITEM_ID, /* INVENTORY_ITEM_ID */
1280 E.ELEMENT_NAME, /* ELEMENT_NAME */
1281 SYSDATE, /* LAST_UPDATE_DATE */
1282 1, /* LAST_UPDATED_BY */
1283 SYSDATE, /* CREATION_DATE */
1284 1, /* CREATED_BY */
1285 1, /* LAST_UPDATE_LOGIN */
1286 D.ELEMENT_VALUE, /* ELEMENT_VALUE */
1287 E.DEFAULT_ELEMENT_FLAG, /* DEFAULT_ELEMENT_FLAG */
1288 E.ELEMENT_SEQUENCE /* ELEMENT_SEQUENCE */
1289 from MTL_SYSTEM_ITEMS_INTERFACE SI,
1290 MTL_DESCR_ELEMENT_VALUES D,
1291 MTL_DESCRIPTIVE_ELEMENTS E
1292 where D.INVENTORY_ITEM_ID = SI.COPY_ITEM_ID
1293 and E.ITEM_CATALOG_GROUP_ID = SI.ITEM_CATALOG_GROUP_ID
1294 and E.ELEMENT_NAME = D.ELEMENT_NAME
1295 and SI.SET_ID = TO_CHAR(to_number(USERENV('SESSIONID')));
1296 return(1);
1297
1298 EXCEPTION
1299 WHEN NO_DATA_FOUND THEN
1300 return(1);
1301 WHEN flex_error THEN
1302 message_name := 'BOM_ATO_LINK_ERROR';
1303 error_message := 'BOM_ATO_CONFIG_SEGMENT_ERROR';
1304 return(0);
1305 WHEN segment_name_error THEN
1306 message_name := 'BOM_ATO_LINK_ERROR';
1307 error_message := 'BOM_ATO_CONFIG_SEGMENT_ERROR';
1308 return(0);
1309 WHEN multiorg_error THEN
1310 message_name := 'BOM_ATO_LINK_ERROR';
1311 error_message := 'BOMPLDCI: ' || to_char(stmt_num) || 'raised multi_org_error';
1312 return(0);
1313 WHEN del_error THEN
1314 message_name := 'BOM_ATO_DELIMITER_ERROR';
1315 error_message := 'BOMPLDCI:'||to_char(stmt_num)||':' || substrb(sqlerrm,1,130);
1316 return(0);
1317 WHEN dupl_error THEN
1318 message_name := 'BOM_ATO_DUPL_CONFIG_NAME';
1319 error_message := 'BOMPLDCI:' ||to_char(stmt_num)||':'|| substrb(sqlerrm,1,130);
1320 return(0);
1321 WHEN loop_other_error THEN
1322 message_name := 'BOM_ATO_LOAD_LOOP_ERROR';
1323 error_message := 'BOMPLDCI:' ||to_char(stmt_num)||':'|| substrb(sqlerrm,1,130);
1324 return(0);
1325 WHEN OTHERS THEN
1326 error_message := 'BOMPLDCI:' ||to_char(stmt_num)||':'|| substrb(sqlerrm,1,130);
1327 message_name := 'BOM_ATO_LOAD_ERROR';
1328 return(0);
1329
1330 end bmldite_load_item;
1331 end BOMPLDCI;
1332