1 PACKAGE BODY MRP_SCATP_PUB AS
2 /* $Header: MRPPATPB.pls 120.4 2007/11/29 12:34:54 rgurugub ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_SCATP_PUB';
4
5 -- ========================================================================
6 -- This procedure inserts information from so_lines to mtl_demand_interface
7 -- for Supply Chain ATP.
8 -- ========================================================================
9
10 PROCEDURE Insert_Line_MDI(
11 p_api_version IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 p_line_id IN NUMBER,
16 p_assignment_set_id IN NUMBER,
17 p_atp_group_id IN OUT NOCOPY NUMBER ,
18 x_session_id OUT NOCOPY NUMBER)
19 IS
20 l_api_version CONSTANT NUMBER := 1.0;
21 l_api_name CONSTANT VARCHAR2(30):= 'Insert_Line_MDI';
22 l_assignment_set_id number;
23 l_oe_install VARCHAR2(3) := 'OE';
24
25 l_p_atp_group_id NUMBER;
26 BEGIN
27 --5022204
28 --stubbing out the procedure
29 return;
30 END Insert_Line_MDI;
31
32 -- ========================================================================
33 -- This procedure inserts information source org information for the request
34 -- items into mtl_demand_interface for Supply Chain ATP.
35 -- ========================================================================
36 PROCEDURE Insert_Supply_Sources_MDI(
37 p_api_version IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_atp_group_id IN NUMBER,
42 p_assignment_set_id IN NUMBER,
43 x_session_id OUT NOCOPY NUMBER )
44
45 IS
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_api_name CONSTANT VARCHAR2(30):= 'Insert_Supply_Sources_MDI';
48 l_source_org_id number;
49 l_atp_group_id number;
50 l_session_id number;
51 l_lead_time number;
52 l_oe_schedule_window number;
53 l_ship_method VARCHAR2(30);
54 l_vendor_id number;
55 l_vendor_site_id number;
56 l_from_location_id number;
57 l_to_location_id number;
58 l_flag number;
59 l_ship_to_site_use_id number;
60 l_customer_id number;
61 l_dest_org_id number;
62 l_oe_install VARCHAR2(3);
63 BEGIN
64 -- Standard call to check for call compatibility
65 --5022204
66 --stubbed out
67 return;
68 END Insert_Supply_Sources_MDI;
69
70 -- ========================================================================
71 -- This procedure deletes information from mtl_supply_demand_temp for
72 -- the atp_group_id specified by the user
73 -- It also null out some columns in mtl_demand_interface
74 -- ========================================================================
75
76 PROCEDURE Uncheck(
77 p_api_version IN NUMBER,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_count OUT NOCOPY NUMBER,
80 x_msg_data OUT NOCOPY VARCHAR2,
81 p_atp_group_id IN NUMBER)
82 IS
83 l_api_version CONSTANT NUMBER := 1.0;
84 l_api_name CONSTANT VARCHAR2(30):= 'Uncheck';
85
86 BEGIN
87 --stubbed out
88 --5022204
89 return;
90 END Uncheck;
91
92 -- ========================================================================
93 -- This procedure gets resource/line information from mrp_atp_v and then
94 -- inserts into mtl_demand_interface for
95 -- the atp_group_id specified by the user
96 -- ========================================================================
97
98 PROCEDURE Insert_Res_MDI(
99 x_err_num OUT NOCOPY NUMBER,
100 x_err_msg OUT NOCOPY VARCHAR2,
101 p_atp_group_id IN NUMBER)
102 IS
103
104 BEGIN
105 --stubbed out
106 --5022204
107 return;
108 END Insert_Res_MDI;
109
110 PROCEDURE Insert_Comp_MDI(
111 p_api_version IN NUMBER,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2,
115 p_atp_group_id IN NUMBER)
116 IS
117 l_api_version CONSTANT NUMBER := 1.0;
118 l_api_name CONSTANT VARCHAR2(30):= 'Insert_Comp_MDI';
119
120 l_atp_group_id NUMBER;
121 l_organization_id NUMBER;
122 l_inventory_item_id NUMBER;
123 l_atp_rule_id NUMBER;
124 l_line_item_quantity NUMBER;
125 l_primary_uom_quantity NUMBER;
126 l_requirement_date DATE;
127 l_atp_calendar_organization_id NUMBER;
128 l_atp_check NUMBER;
129 l_line_item_uom VARCHAR(3);
130 l_supply_header_id NUMBER;
131
132 CURSOR C1(l_atp_group_id NUMBER) IS
133 SELECT mdi.atp_group_id,
134 mdi.organization_id,
135 be.component_item_id,
136 NVL(wp.component_atp_rule_id,NVL(msi.atp_rule_id,
137 mp.default_atp_rule_id)),
138 (mdi.line_item_quantity * be.extended_quantity),
139 (mdi.primary_uom_quantity * be.extended_quantity),
140 mdi.requirement_date,
141 mdi.atp_calendar_organization_id,
142 1,
143 be.primary_uom_code,
144 mdi.supply_header_id
145 FROM mtl_system_items msi,
146 mtl_parameters mp,
147 wip_parameters wp,
148 bom_explosions be,
149 bom_bill_of_materials bom,
150 mtl_demand_interface mdi
151 WHERE mdi.atp_group_id = l_atp_group_id
152 AND bom.assembly_item_id = mdi.inventory_item_id
153 AND bom.organization_id = mdi.organization_id
154 AND bom.alternate_bom_designator is NULL
155 AND be.top_bill_sequence_id = bom.bill_sequence_id
156 AND be.optional = 2
157 AND be.explosion_type = 'ALL'
158 AND MRP_SCATP_PUB.required_component(be.top_bill_sequence_id,
159 be.plan_level,
160 mdi.requirement_date,
161 be.component_sequence_id,
162 be.component_code) = 1
163 AND be.component_item_id = msi.inventory_item_id
164 AND be.organization_id = msi.organization_id
165 AND mp.organization_id = msi.organization_id
166 AND wp.organization_id = msi.organization_id
167 AND msi.atp_flag in ('Y','C');
168
169 BEGIN
170
171 -- Standard call to check for call compatibility
172 IF NOT FND_API.Compatible_API_Call
173 ( l_api_version
174 , p_api_version
175 , l_api_name
176 , G_PKG_NAME
177 )
178 THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181
182 -- initialize API returm status to success
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 OPEN C1(p_atp_group_id);
186 LOOP
187 FETCH C1 INTO l_atp_group_id,
188 l_organization_id,
189 l_inventory_item_id,
190 l_atp_rule_id,
191 l_line_item_quantity,
192 l_primary_uom_quantity,
193 l_requirement_date,
194 l_atp_calendar_organization_id,
195 l_atp_check,
196 l_line_item_uom,
197 l_supply_header_id;
198
199 IF C1%ROWCOUNT = 0 THEN
200 CLOSE C1;
201 RAISE NO_DATA_FOUND;
202 END IF;
203
204 EXIT WHEN C1%NOTFOUND;
205
206 -- insert component records into mtl_demand_interface
207 INSERT INTO MTL_DEMAND_INTERFACE(
208 atp_group_id,
209 organization_id,
210 inventory_item_id,
211 last_update_date,
212 last_updated_by,
213 creation_date,
214 created_by,
215 last_update_login,
216 atp_rule_id,
217 line_item_quantity,
218 primary_uom_quantity,
219 requirement_date,
220 atp_calendar_organization_id,
221 atp_check,
222 line_item_uom,
223 supply_header_id
224 )
225 VALUES (l_atp_group_id,
226 l_organization_id,
227 l_inventory_item_id,
228 sysdate,
229 1,
230 sysdate,
231 1,
232 1,
233 l_atp_rule_id,
234 l_line_item_quantity,
235 l_primary_uom_quantity,
236 l_requirement_date,
237 l_atp_calendar_organization_id,
238 l_atp_check,
239 l_line_item_uom,
240 l_supply_header_id);
241
242 END LOOP;
243 CLOSE C1;
244
245 EXCEPTION
246
247 WHEN NO_DATA_FOUND THEN
248
249 x_return_status := FND_API.G_RET_STS_ERROR;
250
251 FND_MESSAGE.set_name('MRP','MRP_NO_ATP_COMPONENTS');
252 FND_MSG_PUB.Add;
253
254 FND_MSG_PUB.Count_And_Get
255 ( p_count => x_msg_count
256 , p_data => x_msg_data
257 );
258
259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
260
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262
263 FND_MSG_PUB.Count_And_Get
264 ( p_count => x_msg_count
265 , p_data => x_msg_data
266 );
267
268 WHEN OTHERS THEN
269
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
271
272 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
273 THEN
274 FND_MSG_PUB.Add_Exc_Msg
275 ( G_PKG_NAME
276 , 'l_api_name'
277 );
278 END IF;
279
280 FND_MSG_PUB.Count_And_Get
281 ( p_count => x_msg_count
282 , p_data => x_msg_data
283 );
284
285 END Insert_Comp_MDI;
286
287 /*
288 FUNCTION required_component(p_top_bill_seq_id IN NUMBER,
289 p_plan_level IN NUMBER,
290 p_request_date IN DATE,
291 p_comp_seq_id IN NUMBER,
292 p_component_code IN VARCHAR2)
293 return NUMBER
294 IS
295 i BINARY_INTEGER := 1;
296 l_required BINARY_INTEGER := 0;
297 l_component_code VARCHAR2(1000);
298 BEGIN
299 --5022204
300 --stubbed out
301 return -1;
302 END required_component;
303 */
304
305 FUNCTION required_component(p_top_bill_seq_id IN NUMBER,
306 p_plan_level IN NUMBER,
307 p_request_date IN DATE,
308 p_comp_seq_id IN NUMBER,
309 p_component_code IN VARCHAR2)
310 return NUMBER
311 IS
312 i BINARY_INTEGER := 1;
313 l_required BINARY_INTEGER := 0;
314 l_component_code VARCHAR2(1000);
315 BEGIN
316
317 -- this function is planned to be used to determine if a component
318 -- requirement is required when we calculate flow shedule requirements
319 -- in atp program. we need to explose through phantom.
320 -- for example, we have a bill like this
321 -- A
322 -- .B (phantom)
323 -- ..C (phantom)
324 -- ...D
325 -- .E
326 -- ..C (phantom)
327 -- ...D
328 -- So if we have a flow schedule on A, it should have component requirement
329 -- on D (from A-B-C-D) and E (from A-E) only.
330 -- So we should return 1 for the D and E and return 0 for the rest.
331
332 -- First we need to make sure this item is not a phantom
333 -- (phantom will not be included not matter at which plan level)
334
335 BEGIN
336 SELECT '1'
337 INTO l_required
338 FROM BOM_INVENTORY_COMPONENTS
339 WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id
340 AND NVL(WIP_SUPPLY_TYPE,
341 MRP_SCATP_PUB.mtl_wip_supply_type(
342 p_top_bill_seq_id,
343 component_item_id)) <> 6; /* Bug 2777745 */
344 EXCEPTION WHEN NO_DATA_FOUND THEN
345 l_required := 0;
346 END;
347
348 IF (l_required = 1) AND (p_plan_level > 1) THEN
349 -- now we make sure the item itself is not a phantom. however,
350 -- if this item is not a immediate component, we need to make sure
351 -- all the parent records are phantoms all the way up and
352
353 l_component_code := p_component_code;
354
355 FOR i IN REVERSE 1..p_plan_level-1 LOOP
356 -- we go to the parent level, and i indicates the plan_level of the
357 -- parent record.
358
359 l_component_code := substr(l_component_code, 1,
360 instr(l_component_code,'-',-1,1)-1);
361
362 BEGIN
363 -- bug 1305491: look like bom still does the explosion
364 -- for the model, option class which belong to config item.
365 -- so we end up triple count the component demand for the option.
366 -- so we need to make sure all the parents are not model/oc.
367 SELECT '1'
368 INTO l_required
369 FROM BOM_INVENTORY_COMPONENTS BIC,
370 BOM_EXPLOSIONS BE
371 WHERE BE.TOP_BILL_SEQUENCE_ID = p_top_bill_seq_id
372 AND BE.COMPONENT_CODE = l_component_code
373 AND BE.PLAN_LEVEL = i
374 AND BE.explosion_type = 'ALL'
375 AND TRUNC(BE.EFFECTIVITY_DATE) <= TRUNC(p_request_date)
376 AND TRUNC(BE.DISABLE_DATE) >TRUNC(p_request_date)
377 AND BIC.COMPONENT_SEQUENCE_ID =
378 BE.COMPONENT_SEQUENCE_ID
379 AND nvl(BIC.WIP_SUPPLY_TYPE,
380 MRP_SCATP_PUB.mtl_wip_supply_type(
381 p_top_bill_seq_id,
382 bic.component_item_id)) = 6 /* Bug 2777745 */
383 AND BIC.BOM_ITEM_TYPE NOT IN (1,2); -- not a model or oc
384 EXCEPTION WHEN NO_DATA_FOUND THEN
385 l_required := 0 ;
386 END;
387
388 EXIT WHEN l_required = 0 ;
389
390 END LOOP;
391 END IF; -- end if (l_required = 1) AND (p_plan_level > 1)
392 return l_required;
393
394 END required_component;
395
396
397 /* Bug 2777745 */
398 FUNCTION mtl_wip_supply_type(
399 p_top_bill_seq_id IN NUMBER,
400 p_comp_id IN NUMBER)
401 RETURN NUMBER
402 IS
403 l_wip_supply_type NUMBER;
404 BEGIN
405
406 SELECT NVL(msi.wip_supply_type,1)
407 INTO l_wip_supply_type
408 FROM mtl_system_items msi, bom_bill_of_materials bbm
409 WHERE bbm.bill_sequence_id = p_top_bill_seq_id
410 AND msi.organization_id = bbm.organization_id
411 AND msi.inventory_item_id = p_comp_id;
412
413 RETURN(l_wip_supply_type);
414
415 END mtl_wip_supply_type;
416
417 END MRP_SCATP_PUB;
[Home] [Help]