1 package body BOMPCHDU as
2 /* $Header: BOMCHDUB.pls 115.3 99/07/16 05:11:38 porting shi $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : BOMCHDUB.pls |
10 | DESCRIPTION : |
11 | This file creates packaged functions that check for matching |
12 | configurations. |
13 | |
14 | BOMPCHDU.is_base_demand_row -- Checks whether a given row in |
15 | mtl_demand has already been found to be a duplicate. |
16 | |
17 | BOMPCHDU.bomfchdu_check_dupl_config -- Checks through each |
18 | row in mtl_demand which has been marked to be processed. |
19 | Depending on profile settings, it may call a custom function |
20 | for pre-existing configurations or use the matching function |
21 | from match and reserve. Then, it does an in batch match on |
22 | the configurations. |
23 | |
24 | BOMPCHDU.existing_dupl_match -- for a given demand_id, it |
25 | searches BOM ATO Configurations for a matching configuration. |
26 | |
27 | BOMPCHDU.check_dupl_batch_match -- for a given demand_id, it |
28 | checks the other rows to be processed whether any has an |
29 | identical configuration. If any of the other rows are |
30 | identical, their dupl_config_demand_id or dupl_config_item_id |
31 | is updated accordingly. |
32 | |
33 | |
34 | HISTORY : |
35 | 06/13/93 Chung Wei Lee Initial version |
36 | 08/16/93 Chung Wei Lee Added more comments |
37 | 08/23/93 Chung Wei Lee Added codes to check dup new config |
38 | 11/08/93 Randy Roupp Added sql_stmt_num logic |
39 | 11/09/93 Randy Roupp Changed is_base_demand_row function |
40 | 01/14/94 Nagaraj Handle the case if d1.primary_uom_ |
41 | quantity is zero |
42 | 02/21/94 Manish Modi Moved bomfcdec_ch_du_ext_config to |
43 | BOMPCEDC. |
44 | 11/01/95 Edward Lee Re-wrote package to use a matching |
45 | function similar to the one in |
46 | BOMPMCFG which drives off of so_lines|
47 | Also added a check for existing |
48 | configurations in BOM ATO Configs. |
49 =============================================================================*/
50
51 function is_base_demand_row(
52 input_demand_id in number,
53 error_message out VARCHAR2, /* 70 bytes to hold returned msg */
54 message_name out VARCHAR2, /* 30 bytes to hold returned name*/
55 table_name out VARCHAR2 /* 30 bytes to hold returned tbl */
56 )
57 return integer
58 is
59 l_demand_count number;
60 l_dup_demand_id number;
61 l_dup_item_id number;
62 sql_stmt_num number;
63 begin
64 /*
65 ** Check whether the passed demand row is a duplicated demand row
66 ** for other demand row/rows already
67 */
68 sql_stmt_num := 30;
69 select duplicated_config_demand_id, duplicated_config_item_id into
70 l_dup_demand_id, l_dup_item_id
71 from mtl_demand
72 where demand_id = input_demand_id
73 and config_group_id = USERENV('SESSIONID')
74 and config_status = 20
75 and rownum = 1; /* demand_id is a duplicated_config_demand_id
76 for an active demand row */
77 if (l_dup_demand_id is null and l_dup_item_id is null) then
78 return(1); /* need to continue duplicate check */
79 else
80 return(0); /* no need continuing duplicate check */
81 end if;
82 exception
83 when NO_DATA_FOUND THEN
84 return(0);
85 when OTHERS THEN
86 error_message := 'ibdr:' || to_char(sql_stmt_num) || ':' ||
87 substrb(sqlerrm,1,150);
88 message_name := 'BOM_ATO_PROCESS_ERROR';
89 table_name := 'MTL_DEMAND';
90
91 return(2); /* Error condition */
92
93 end;
94
95
96 function bomfchdu_check_dupl_config (
97 error_message out VARCHAR2,
98 message_name out VARCHAR2,
99 table_name out VARCHAR2,
100 nobatch in number default 0
101 )
102 return integer
103 is
104 sql_stmt_num number;
105 /*
106 ** Declare cursor for fetching demand lines to be checked
107 */
108 CURSOR cc IS
109 select demand_id, demand_source_line
110 from mtl_demand D,
111 mtl_sales_orders S
112 where D.config_group_id = USERENV('SESSIONID')
113 and D.duplicated_config_item_id is NULL
114 and D.demand_source_header_id = S.sales_order_id
115 order by S.segment1 desc,D.user_line_num desc;
116
117 l_demand_id number;
118 l_demand_source_line number;
119 l_config_item_id number;
120 status number;
121 CK_EXT_ERROR exception;
122 CK_NEW_ERROR exception;
123 CK_MR_ERROR exception;
124 match_profile VARCHAR2(100);
125 message VARCHAR2(100);
126
127 begin
128 sql_stmt_num := 5;
129
130 /*
131 select nvl(substr(profile_option_value,1,30),'0') into match_profile
132 from fnd_profile_option_values val,fnd_profile_options op
133 where op.profile_option_name = 'BOM:CHECK_DUPL_CONFIG'
134 and val.level_id = 10001
135 and val.profile_option_id = op.profile_option_id;
136 */
137
138 match_profile:=FND_PROFILE.Value('BOM:CHECK_DUPL_CONFIG');
139
140 message_name := 'Just about to open cursor.';
141 sql_stmt_num :=6;
142 open cc;
143 /*
144 ** Loop through all the processing demand records
145 ** Check for an existing matching configuration
146 ** Check for matching configuration demand
147 */
148 loop
149 sql_stmt_num :=7;
150 fetch cc into l_demand_id,l_demand_source_line;
151 exit when (cc%notfound);
152 /*
153 ** Search whether this row has already been found
154 ** to be a duplicate
155 */
156 sql_stmt_num :=8;
157 status := BOMPCHDU.is_base_demand_row(
158 l_demand_id,
159 error_message,
160 message_name,
161 table_name);
162 /* Proceed only if dupl_config_item_id and */
163 /* dupl_config_demand_id still null. */
164 if (status = 1) then
165 sql_stmt_num :=9;
166 if (match_profile = '1') then
167 /*
168 ** Keep old hook to check for for existing
169 ** Configurations in Item/BOM/Rtg tables
170 ** '1' = CHECK_DUPL_CONFIG is YES, so use customer hook
171 */
172
173 status := BOMPCEDC.bomfcdec_ch_du_ext_config(
174 l_demand_id,
175 l_config_item_id,
176 error_message,
177 message_name,
178 table_name);
179 if (status = 1) then
180 if (l_config_item_id is not NULL) then
181 sql_stmt_num := 10;
182 update mtl_demand
183 set duplicated_config_item_id= l_config_item_id
184 where demand_id = l_demand_id;
185 end if; /* end of if config_item is not NULL */
186 else
187 raise CK_NEW_ERROR; /* if status not 1 */
188 end if; /* end of if status = 1 */
189 else /* if match_profile is NOT 'YES' */
190 if (match_profile = '3') then
191
192 /* Match_profile = '3' means
193 ** Match and Reserve, so use new
194 ** existing_dupl_match to check for existing
195 ** configuration in BAC */
196
197 status := BOMPCHDU.existing_dupl_match(
198 l_demand_id,
199 l_config_item_id,
200 error_message,
201 message_name,
202 table_name);
203 if (status <> 1) then
204 raise CK_MR_ERROR; /* if status not 1 */
205 end if; /* status = 1 */
206
207 end if; /* match_profile = MATCH and RESERVE = 1 */
208
209 end if; /* on the local_profile_check */
210 /*
211 ** Now, we check the other orders in this
212 ** batch, and set their fuplicate_config_id
213 ** if they match.
214 */
215 sql_stmt_num :=21;
216 if (nobatch=0) then
217 status := BOMPCHDU.check_dupl_batch_match(
218 l_demand_id,
219 l_config_item_id,
220 l_demand_source_line,
221 error_message,
222 message_name,
223 table_name);
224 end if;
225 else
226 status:=1; /* if not base demand row, reset */
227 end if; /* on the base_demand_row */
228 end loop;
229 close cc;
230
231 return(1);
232 exception
233 when CK_EXT_ERROR then
234 return (status);
235 when CK_MR_ERROR then
236 return (status);
237 when CK_NEW_ERROR then
238 return (status);
239 when OTHERS then
240 status := sql_stmt_num; /*sqlcode; */
241 error_message := 'bcdc:' || to_char(sql_stmt_num) || ':' ||
242 substrb(sqlerrm,1,150);
243 table_name := 'MTL_DEMAND';
244 return (status);
245
246 end;
247
248
249
250 function existing_dupl_match (
251 input_demand_id in number,
252 dupl_item_id out number,
253 error_message out VARCHAR2,
254 message_name out VARCHAR2,
255 table_name out VARCHAR2
256 )
257 return integer
258 is
259 stmt_num number;
260 cfm_value number;
261 match_results number;
262 my_match number;
263 NO_MODEL_FOUND EXCEPTION;
264 begin
265 /*
266 ** This function searches
267 ** for an existing configuration that meets the requirements
268 ** of orders being processed in this run of Create Configuration
269 ** cfm_routing_flag indicates the type of routing used to create
270 ** existing configurations. Matching configurations must have
271 ** same values of cfm_routing_flag
272 */
273
274 select NVL(cfm_routing_flag,0) into cfm_value
275 from mtl_demand md,
276 bom_operational_routings bor
277 where md.inventory_item_id = bor.assembly_item_id(+)
278 and md.organization_id = bor.organization_id(+)
279 and bor.alternate_routing_designator(+) is NULL
280 and md.demand_id = input_demand_id;
281
282
283
284 stmt_num :=60;
285 update mtl_demand m
286 set m.duplicated_config_item_id = (
287 select BAC1.config_item_id
288 from BOM_ATO_CONFIGURATIONS BAC1, /* the duplicate */
289 so_lines_all solp, /* Parent of ATO Model if any */
290 so_lines_all sol1, /* processing */
291 mtl_system_items msi,
292 bom_operational_routings bor,
293 bom_parameters bp
294 where BAC1.base_model_id = sol1.inventory_item_id
295 and BAC1.organization_id = sol1.warehouse_id
296 and BAC1.component_item_id = sol1.inventory_item_id
297 and bp.organization_id = BAC1.organization_id
298 and NVL(BAC1.cfm_routing_flag,0) = cfm_value
299 and solp.line_id = nvl(sol1.link_to_line_id,sol1.line_id)
300 and msi.organization_id = BAC1.organization_id
301 and msi.inventory_item_id = BAC1.config_item_id
302 and msi.inventory_item_status_code <> bp.bom_delete_status_code
303 and not exists (select 'X'
304 from so_lines_all sol5 /* current */
305 where sol5.ato_line_id = sol1.line_id
306 and sol5.ordered_quantity > nvl(sol5.cancelled_quantity,0)
307 and sol5.inventory_item_id not in
308 (select BAC2.component_item_id
309 from BOM_ATO_CONFIGURATIONS BAC2 /* duplicates */
310 where BAC2.config_item_id = BAC1.config_item_id
311 and BAC2.component_item_id <> BAC1.component_item_id
312 and BAC2.component_item_id = sol5.inventory_item_id
313 and BAC2.component_code =
314 decode(sol1.link_to_line_id,NULL,sol5.component_code,
315 substrb(sol5.component_code,
316 lengthb(solp.component_code)+2))
317 and BAC2.COMPONENT_QUANTITY =
318 ((sol5.ordered_quantity-nvl(sol5.cancelled_quantity,0))/
319 (sol1.ordered_quantity-nvl(sol1.cancelled_quantity,0)))
320 )
321 )
322 and exists(select 'X'
326 having count(*) = (select count (*)
323 from BOM_ATO_CONFIGURATIONS BAC3 /* duplicates */
324 where BAC3.config_item_id = BAC1.config_item_id
325 and BAC3.component_item_id <> BAC1.component_item_id
327 from so_lines_all sol7 /* processing */
328 where sol7.ato_line_id = sol1.line_id
329 and sol7.ordered_quantity>nvl(sol7.cancelled_quantity,0)
330 )
331 )
332 and sol1.line_id = m.demand_source_line
333 and rownum = 1
334 )
335 where m.demand_id = input_demand_id
336 and m.config_group_id = USERENV('SESSIONID')
337 and m.demand_type = 1
338 and m.duplicated_config_item_id is NULL;
339
340
341
342 select duplicated_config_item_id into match_results
343 from mtl_demand
344 where config_group_id = USERENV('SESSIONID')
345 and demand_id = input_demand_id;
346 /* **** is this REALLY necessary? just updated it*** */
347
348 if (match_results is not null) then
349 stmt_num := 70;
350 update bom_ato_configurations
351 set last_referenced_date = SYSDATE
352 where config_item_id = match_results;
353
354 end if;
355
356 dupl_item_id := match_results;
357 return (1);
358
359 exception
360 when NO_MODEL_FOUND THEN
361 error_message := 'BOMPCHDU' || to_char(stmt_num) || ':' ||
362 substrb(sqlerrm,1,150);
363 message_name := 'BOM_ATO_PROCESS_ERROR';
364 table_name := 'MTL_DEMAND';
365 return (0);
366
367 when NO_DATA_FOUND THEN
368
369 return(0);
370 when OTHERS THEN
371
372 error_message := 'BOMPCHDU' || to_char(stmt_num) || ':' ||
373 substrb(sqlerrm,1,150);
374 message_name := 'BOM_ATO_PROCESS_ERROR';
375 table_name := 'SO_LINES_ALL';
376 return(0);
377 end;
378
379
380 function check_dupl_batch_match (
381 input_demand_id in number,
382 dupl_item_id number,
383 copy_line_id in number,
384 error_message out VARCHAR2, /* 70 bytes to hold returned msg */
385 message_name out VARCHAR2, /* 30 bytes to hold returned name */
386 table_name out VARCHAR2 /* 30 bytes to hold returned tbl */
387 )
388 return integer
389 is
390 stmt_num number;
391 dupl_demand_id number;
392 temp_item_id number;
393 NO_MODEL_FOUND EXCEPTION;
394 begin
395 temp_item_id := dupl_item_id;
396 if (dupl_item_id is null) then
397 temp_item_id := NULL;
398 dupl_demand_id := input_demand_id;
399 end if;
400
401 temp_item_id := dupl_item_id;
402 stmt_num :=100;
403
404 update mtl_demand m
405 set m.duplicated_config_item_id = temp_item_id,
406 m.duplicated_config_demand_id = dupl_demand_id
407 where m.config_group_id = USERENV('SESSIONID')
408 and m.demand_id in (
409 select m1.demand_id
410 from so_lines_all soldp, /* parent of duplicate */
411 so_lines_all solp, /* parent of other lines */
412 so_lines_all sol1, /* processing other lines */
413 so_lines_all sold, /* current -- duplicate */
414 mtl_demand m1
415 where sol1.line_id=m1.demand_source_line
416 and m1.config_group_id = USERENV('SESSIONID')
417 and m1.demand_id <> input_demand_id
418 and sold.line_id = copy_line_id
419 and sold.inventory_item_id = sol1.inventory_item_id +0
420 and sold.warehouse_id = sol1.warehouse_id
421 and solp.line_id = nvl(sol1.link_to_line_id,sol1.line_id)
422 and soldp.line_id = nvl(sold.link_to_line_id,sold.line_id)
423 and m1.config_status = 20
424 and m1.duplicated_config_item_id is null
425 and m1.duplicated_config_demand_id is null
426 and exists(select 'X'
427 from so_lines_all sold3 /* duplicates */
428 where sold3.ato_line_id = sold.line_id
429 and sold3.ordered_quantity>nvl(sold3.cancelled_quantity,0)
430 having count(*) = (select count (*)
431 from so_lines_all sol7 /* processing */
432 where sol7.ato_line_id = sol1.line_id
433 and sol7.ordered_quantity>nvl(sol7.cancelled_quantity,0)
434 )
435 )
436 and not exists (select 'X'
437 from so_lines_all sol5 /* current */
438 where sol5.ato_line_id = sol1.line_id
439 and sol5.ordered_quantity > nvl(sol5.cancelled_quantity,0)
440 and sol5.inventory_item_id not in
441 (select sold2.inventory_item_id
442 from so_lines_all sold2 /* duplicates */
443 where sold2.ato_line_id = sold.line_id
444 and sold2.component_code = sol5.component_code
445 and decode(sold.link_to_line_id,NULL,sold2.component_code,
446 substrb(sold2.component_code,
447 lengthb(soldp.component_code)+2)) =
448 decode(sol1.link_to_line_id,NULL,sol5.component_code,
449 substrb(sol5.component_code,
450 lengthb(solp.component_code)+2))
451 and
452 ((sold2.ordered_quantity-nvl(sold2.cancelled_quantity,0))/(sold.ordered_quantity-nvl(sold.cancelled_quantity,0))) =
453 ((sol5.ordered_quantity-nvl(sol5.cancelled_quantity,0))/
454 (sol1.ordered_quantity-nvl(sol1.cancelled_quantity,0)))
455
456 )
457 )
458 );
459 return (1);
460
461 exception
462 when NO_MODEL_FOUND THEN
463
464 error_message := 'BOMPCHDU:' || to_char(stmt_num) || ':' ||
465 substrb(sqlerrm,1,150);
466 message_name := 'BOM_ATO_PROCESS_ERROR';
470 when NO_DATA_FOUND THEN
467 table_name := 'MTL_DEMAND';
468 return (0);
469
471
472 return(0);
473 when OTHERS THEN
474
475 error_message := 'BOMPCHDU' || to_char(stmt_num) || ':' ||
476 substrb(sqlerrm,1,150);
477 message_name := 'BOM_ATO_PROCESS_ERROR';
478 table_name := 'SO_LINES_ALL';
479 return(0);
480 end;
481
482
483
484 end BOMPCHDU;
[Home] [Help]