[Home] [Help]
PACKAGE BODY: APPS.BOMPMCFG
Source
1 package body BOMPMCFG as
2 /* $Header: BOMMCFGB.pls 115.5 99/07/26 17:43:30 porting ship $ */
3 function atodupl_check (
4 model_line_id number,
5 sch_sesion_id number,
6 sch_grp_id number,
7 error_message out VARCHAR2,
8 message_name out VARCHAR2
9 )
10 return integer
11 is
12 configuration_item_id number;
13 model_detail_id number;
14 org_id number;
15 stmt_num number;
16 uom_code varchar2(3);
17 order_type varchar2(30);
18 order_number number;
19 line_qty number;
20 status number;
21 dupl_count number;
22 NO_MODEL_FOUND EXCEPTION;
23 INSERT_ERROR EXCEPTION;
24 CURSOR dupl_rows IS
25 select BAC1.config_item_id,BAC1.organization_id,sol1.unit_code,
26 (sol1.ordered_quantity-nvl(sol1.cancelled_quantity,0)),
27 soh.order_number,sot.name,sod.line_detail_id
28 from BOM_ATO_CONFIGURATIONS BAC1, /* the duplicate */
29 so_lines_all solp, /* Parent of the Model of processing if any */
30 so_lines_all sol1, /* processing */
31 so_headers_all soh, /* to get the header info */
32 so_order_types_all sot,
33 so_line_details sod,
34 mtl_system_items msi,
35 bom_parameters bp
36 where BAC1.base_model_id = sol1.inventory_item_id
37 and soh.header_id = sol1.header_id
38 and sot.order_type_id = soh.order_type_id
39 and sod.line_id = sol1.line_id
40 and solp.line_id = nvl(sol1.link_to_line_id,sol1.line_id)
41 and BAC1.organization_id = sod.warehouse_id
42 and BAC1.component_item_id = sol1.inventory_item_id
43 and bp.organization_id = BAC1.organization_id
44 and msi.organization_id = BAC1.organization_id
45 and msi.inventory_item_id = BAC1.config_item_id
46 and msi.inventory_item_status_code <> bp.bom_delete_status_code
47 and not exists (select 'Extra options in Order'
48 from so_lines_all sol5 /* current */
49 where sol5.ato_line_id = sol1.line_id
50 and sol5.ordered_quantity > nvl(sol5.cancelled_quantity,0)
51 and sol5.inventory_item_id not in
52 (select BAC2.component_item_id
53 from BOM_ATO_CONFIGURATIONS BAC2 /* duplicates */
54 where BAC2.config_item_id = BAC1.config_item_id
55 and BAC2.component_item_id <> BAC1.component_item_id
56 and BAC2.component_item_id = sol5.inventory_item_id
57 and BAC2.component_code =
58 decode(sol1.link_to_line_id,NULL,sol5.component_code,
59 substrb(sol5.component_code,
60 lengthb(solp.component_code)+2))
61 and BAC2.COMPONENT_QUANTITY =
62 ((sol5.ordered_quantity-nvl(sol5.cancelled_quantity,0))/
63 (sol1.ordered_quantity-nvl(sol1.cancelled_quantity,0)))
64 )
65 )
66 and not exists(select 'X'
67 from BOM_ATO_CONFIGURATIONS BAC3 /* duplicates */
68 where BAC3.config_item_id = BAC1.config_item_id
69 and BAC3.component_item_id <> BAC1.component_item_id
70 having count(*) <> (select count (*)
71 from so_lines_all sol7 /* processing */
72 where sol7.ato_line_id = sol1.line_id
73 and sol7.ordered_quantity>nvl(sol7.cancelled_quantity,0)
74 )
75 )
76 and sol1.line_id = model_line_id;
77 begin
78 dupl_count := 0;
79 stmt_num := 10;
80 open dupl_rows;
81 LOOP
82 stmt_num := 20;
83 fetch dupl_rows into configuration_item_id,org_id,uom_code,line_qty,
84 order_number,order_type,model_detail_id;
85 exit when (dupl_rows%notfound);
86 dupl_count := dupl_count + 1;
87
88 stmt_num := 30;
89 update bom_ato_configurations
90 set last_referenced_date = SYSDATE
91 where config_item_id = configuration_item_id;
92
93 status := BOMPMCFG.insert_mtl_dem_interface(configuration_item_id,org_id,
94 sch_sesion_id,sch_grp_id,model_line_id,model_detail_id,
95 uom_code,line_qty,order_number,order_type,
96 error_message,message_name
97 );
98
99 if status <> 1 THEN
100 RAISE INSERT_ERROR;
101 end if;
102
103 END LOOP;
104 if dupl_count = 0 THEN
105 raise NO_MODEL_FOUND;
106 end if;
107
108 return (1);
109 exception
110 when NO_MODEL_FOUND THEN
111 configuration_item_id := NULL;
112 error_message := 'BOMPMCFG:' || to_char(stmt_num) || ':' ||
113 substrb(sqlerrm,1,150);
114 message_name := 'BOM_ATO_PROCESS_ERROR';
115 return (100);
116
117 when INSERT_ERROR THEN
118 error_message := 'BOMPMCFG:insert_mtl_demand_interface';
119 return (200);
120
121 when NO_DATA_FOUND THEN
122 configuration_item_id := NULL;
123 return(1);
124 when OTHERS THEN
125
126 error_message := 'BOMPMCFG' || to_char(stmt_num) || ':' ||
127 substrb(sqlerrm,1,150);
128 message_name := 'BOM_ATO_PROCESS_ERROR';
129 return(300);
130 end;
131
132 function insert_mtl_dem_interface(
133 configuration_item_id in number, /* Item ID of Configuration */
134 org_id in number, /* Org id of the config item */
135 sch_session_id in number, /* Session id for insert */
136 sch_grp_id in number, /* Schedule group id */
137 model_line_id in number, /* Model line id */
138 model_detail_id in number, /* Model line detail id */
139 uom_code in varchar2,
140 line_qty in number,
141 order_number in number,
142 order_type in varchar2,
143 error_message out VARCHAR2, /* 70 bytes to hold returned msg */
144 message_name out VARCHAR2 /* 30 bytes to hold returned name */
145 )
146 return integer
147 is
148 stmt_num number;
149 begin
150 stmt_num := 100;
151 insert into mtl_demand_interface(
152 SCHEDULE_GROUP_ID
153 , SESSION_ID
154 , LOCK_FLAG
155 , ATP_CHECK
156 , CHECK_ATR
157 , DETAIL_RESERVE_FLAG
158 , C_COLUMN2
159 , C_COLUMN3
160 , VALIDATE_ROWS
161 , ACTION_CODE
162 , TRANSACTION_MODE
163 , PROCESS_FLAG
164 , LAST_UPDATE_DATE
165 , CREATION_DATE
166 , LAST_UPDATED_BY
167 , CREATED_BY
168 , LAST_UPDATE_LOGIN
169 , LINE_ITEM_UOM
170 , LINE_ITEM_QUANTITY
171 , ORGANIZATION_ID
172 , INVENTORY_ITEM_ID
173 , DEMAND_SOURCE_TYPE
174 , DEMAND_SOURCE_LINE
175 , DEMAND_SOURCE_DELIVERY
176 , REQUIREMENT_DATE
177 , DEMAND_HEADER_SEGMENT1
178 , DEMAND_HEADER_SEGMENT2
179 , DEMAND_HEADER_SEGMENT3
180 )
181 values(
182 sch_grp_id, /* Sch group_id */
183 sch_session_id, /* Session id */
184 2, /* Lock flag */
185 2, /* ATP check */
186 1, /* Check ATR */
187 2, /* Detail Reserve Flag */
188 'Y', /*C column 2 */
189 'Y', /* C column 3 */
190 2, /* Validate rows */
191 610, /* Action code */
192 1, /* Transaction Mode */
193 1, /* Process Flag */
194 SYSDATE, /* Last_update_date */
195 SYSDATE, /* creation date */
196 1, /* last updated by */
197 1, /* Created by */
198 1, /* Last update login */
199 uom_code, /* Line item UOM */
200 line_qty, /* Line item quantity */
201 org_id, /* Organization id */
202 configuration_item_id, /* Inventory item id */
203 2, /* Demand Source Type */
204 /* Demand src line */ to_char(model_line_id),
205 /*demand src delivery*/ to_char(model_detail_id),
206 SYSDATE, /* Requirement Date */
207 order_number,
208 order_type,
209 'ORDER ENTRY'
210 );
211
212 return(1);
213 exception
214 when OTHERS THEN
215 error_message := 'BOMPMCFG' || to_char(stmt_num) || ':' ||
216 substrb(sqlerrm,1,150);
217 message_name := 'BOM_ATO_PROCESS_ERROR';
218 return(400);
219 end;
220
221
222 function can_configurations(
223 prg_appid in number,
224 prg_id in number,
225 req_id in number,
226 user_id in number,
227 login_id in number,
228 error_message out varchar2,
229 message_name out varchar2,
230 table_name out varchar2
231 )
232 return integer
233 is
234 stmt_num number;
235 begin
236 stmt_num := 500;
237 insert into BOM_ATO_CONFIGURATIONS(
238 organization_id,
239 base_model_id,
240 config_item_id,
241 component_item_id,
242 component_quantity,
243 component_code,
244 last_referenced_date,
245 creation_date,
246 created_by,
247 last_update_login,
248 last_updated_by,
249 last_update_date,
250 request_id,
251 program_application_id,
252 program_id,
253 program_update_date,
254 cfm_routing_flag)
255 select
256 m.organization_id,
257 m.base_item_id,
258 m.inventory_item_id,
259 s2.inventory_item_id,
260 (s2.ordered_quantity-nvl(s2.cancelled_quantity,0))/
261 (s1.ordered_quantity-nvl(s1.cancelled_quantity,0)),
262 decode(s1.link_to_line_id,NULL,s2.component_code,
263 substrb(s2.component_code,lengthb(s3.component_code)+2)),
264 SYSDATE,
265 SYSDATE,
266 user_id,
267 login_id,
268 user_id,
269 SYSDATE,
270 req_id,
271 prg_appid,
272 prg_id,
273 SYSDATE,
274 bor.cfm_routing_flag
275 from
276 so_lines_all s3, /* Parent of the ATO Model if any */
277 so_lines_all s2, /* Options or Option Classes */
278 so_lines_all s1, /* Model */
279 bom_operational_routings bor,
280 mtl_system_items_interface m
281 where m.set_id = USERENV('SESSIONID')
282 and m.base_item_id = s1.inventory_item_id
283 and m.demand_source_line = s1.line_id
284 and m.base_item_id = bor.assembly_item_id (+)
285 and m.organization_id = bor.organization_id (+)
286 and bor.alternate_routing_designator (+) is NULL
287 and (s2.ato_line_id = s1.line_id
288 or s2.line_id = s1.line_id)
289 and s2.ordered_quantity > NVL(s2.cancelled_quantity,0)
290 and s3.line_id = nvl(s1.link_to_line_id,s1.line_id);
291
292 return(1);
293 exception
294 when OTHERS THEN
295 error_message := 'BOMPMCFG' || to_char(stmt_num) || ':' ||
296 substrb(sqlerrm,1,150);
297 message_name := 'BOM_ATO_PROCESS_ERROR';
298 table_name := 'SO_LINES_ALL';
299 return(0);
300 end;
301
302
303 function existing_dupl_match (
304 error_message out VARCHAR2,
305 message_name out VARCHAR2,
306 table_name out VARCHAR2
307 )
308 return integer
309 is
310 stmt_num number;
311 NO_MODEL_FOUND EXCEPTION;
312 begin
313 /*
314 ** This function searches
315 ** for an existing configuration that meets the requirements
316 ** of orders being processed in this run of Create Configuration
317 */
318
319 stmt_num :=600;
320 update mtl_demand m
321 set m.duplicated_config_item_id = (
322 select BAC1.config_item_id
323 from BOM_ATO_CONFIGURATIONS BAC1, /* the duplicate */
324 so_lines_all solp, /* Parent of ATO Model if any */
325 so_lines_all sol1, /* processing */
326 mtl_system_items msi,
327 bom_parameters bp
328 where BAC1.base_model_id = sol1.inventory_item_id
329 and BAC1.organization_id = sol1.warehouse_id
330 and BAC1.component_item_id = sol1.inventory_item_id
331 and bp.organization_id = BAC1.organization_id
332 and solp.line_id = nvl(sol1.link_to_line_id,sol1.line_id)
333 and msi.organization_id = BAC1.organization_id
334 and msi.inventory_item_id = BAC1.config_item_id
335 and msi.inventory_item_status_code <> bp.bom_delete_status_code
336 and not exists (select 'X'
337 from so_lines_all sol5 /* current */
338 where sol5.ato_line_id = sol1.line_id
339 and sol5.ordered_quantity > nvl(sol5.cancelled_quantity,0)
340 and sol5.inventory_item_id not in
341 (select BAC2.component_item_id
342 from BOM_ATO_CONFIGURATIONS BAC2 /* duplicates */
343 where BAC2.config_item_id = BAC1.config_item_id
344 and BAC2.component_item_id <> BAC1.component_item_id
345 and BAC2.component_item_id = sol5.inventory_item_id
346 and BAC2.component_code =
347 decode(sol1.link_to_line_id,NULL,sol5.component_code,
348 substrb(sol5.component_code,
349 lengthb(solp.component_code)+2))
350 and BAC2.COMPONENT_QUANTITY =
351 ((sol5.ordered_quantity-nvl(sol5.cancelled_quantity,0))/
352 (sol1.ordered_quantity-nvl(sol1.cancelled_quantity,0)))
353 )
354 )
355 and not exists(select 'X'
356 from BOM_ATO_CONFIGURATIONS BAC3 /* duplicates */
357 where BAC3.config_item_id = BAC1.config_item_id
358 and BAC3.component_item_id <> BAC1.component_item_id
359 having count(*) <> (select count (*)
360 from so_lines_all sol7 /* processing */
361 where sol7.ato_line_id = sol1.line_id
362 and sol7.ordered_quantity>nvl(sol7.cancelled_quantity,0)
363 )
364 )
365 and sol1.line_id = m.demand_source_line
366 and rownum = 1
367 )
368 where m.config_group_id = USERENV('SESSIONID')
369 and m.demand_type = 1
370 and m.duplicated_config_item_id is NULL;
371
372 stmt_num := 700;
373 update bom_ato_configurations
374 set last_referenced_date = SYSDATE
375 where config_item_id in (select m.duplicated_config_item_id
376 from mtl_demand m
377 where m.config_group_id = USERENV('SESSIONID')
378 and m.demand_type = 1
379 and m.duplicated_config_item_id is not NULL);
380
381 return (1);
382 exception
383 when NO_MODEL_FOUND THEN
384 error_message := 'BOMPMCFG:' || to_char(stmt_num) || ':' ||
385 substrb(sqlerrm,1,150);
386 message_name := 'BOM_ATO_PROCESS_ERROR';
387 table_name := 'MTL_DEMAND';
388 return (0);
389
390 when NO_DATA_FOUND THEN
391 return(1);
392 when OTHERS THEN
393
394 error_message := 'BOMPMCFG' || to_char(stmt_num) || ':' ||
395 substrb(sqlerrm,1,150);
396 message_name := 'BOM_ATO_PROCESS_ERROR';
397 table_name := 'SO_LINES_ALL';
398 return(0);
399 end;
400
401 end BOMPMCFG;