[Home] [Help]
PACKAGE BODY: APPS.MRP_UPDATE_MRP_INFO_PK
Source
1 PACKAGE BODY MRP_UPDATE_MRP_INFO_PK AS
2 /* $Header: MRPPUPDB.pls 120.1 2006/08/29 13:48:45 arrsubra noship $ */
3
4 PROCEDURE mrp_update_mrp_cols(
5 arg_org_id IN NUMBER,
6 arg_item_id IN NUMBER,
7 arg_user_id IN NUMBER,
8 arg_request_id IN NUMBER) IS
9 -- Constant declarations
10 MPS_RELIEF_TYPE CONSTANT INTEGER := 2;
11 R_WORK_ORDER CONSTANT INTEGER := 1;
12 R_PURCH_ORDER CONSTANT INTEGER := 2;
13 R_PURCH_REQ CONSTANT INTEGER := 5;
14 R_PO_RECV CONSTANT INTEGER := 6;
15 R_SHIPMENT CONSTANT INTEGER := 7;
16 R_SHIPMENT_RCV CONSTANT INTEGER := 8;
17 R_FLOW_SCHEDULE CONSTANT INTEGER := 9;
18 NULL_VALUE CONSTANT INTEGER := -23453;
19 ALREADY_PROCESSED CONSTANT INTEGER := 5;
20 IN_PROCESS CONSTANT INTEGER := 3;
21 SCHEDULE_SUPPLY CONSTANT INTEGER := 2;
22 PLANNED_ORDER CONSTANT INTEGER := 5;
23 PSEUDO_SCHEDULE CONSTANT INTEGER := -100;
24 PSEUDO_PLANNED_ORDER CONSTANT INTEGER := -100;
25 UPDATED_SCHEDULE CONSTANT INTEGER := 2;
26 SYS_NO CONSTANT INTEGER := 2;
27 var_watch_id NUMBER;
28 var_rowid VARCHAR2(20);
29 var_wip_entity_id NUMBER;
30 var_org_id NUMBER;
31 prev_wip_entity_id NUMBER := -1;
32 prev_org_id NUMBER := -1;
33 var_row_count NUMBER;
34 busy EXCEPTION;
35 deadlock EXCEPTION;
36
37 PRAGMA EXCEPTION_INIT(busy, -54);
38 PRAGMA EXCEPTION_INIT(deadlock, -60);
39
40 CURSOR jobs_cursor IS
41 SELECT jobs.rowid,
42 jobs.wip_entity_id,
43 jobs.organization_id
44 FROM wip_requirement_operations ops,
45 wip_discrete_jobs jobs,
46 mrp_relief_interface mrp
47 WHERE ops.wip_entity_id (+) = jobs.wip_entity_id
48 AND ops.organization_id (+) = jobs.organization_id
49 AND mrp.disposition_type = R_WORK_ORDER
50 AND mrp.relief_type = MPS_RELIEF_TYPE
51 AND mrp.request_id = arg_request_id
52 AND mrp.process_status = IN_PROCESS
53 AND mrp.error_message is NULL
54 AND mrp.inventory_item_id =
55 DECODE(arg_item_id,NULL_VALUE,
56 mrp.inventory_item_id,
57 arg_item_id)
58 AND mrp.organization_id =
59 DECODE(arg_org_id,NULL_VALUE,mrp.organization_id,
60 arg_org_id)
61 AND jobs.primary_item_id = mrp.inventory_item_id
62 AND jobs.organization_id = mrp.organization_id
63 AND jobs.wip_entity_id = mrp.disposition_id
64 FOR UPDATE OF jobs.mps_net_quantity,
65 ops.mps_required_quantity
66 ORDER BY jobs.organization_id, jobs.wip_entity_id;
67
68 CURSOR flow_schedules_cursor IS
69 SELECT fs.rowid,
70 fs.wip_entity_id,
71 fs.organization_id
72 FROM wip_flow_schedules fs,
73 mrp_relief_interface mrp
74 WHERE mrp.disposition_type = R_FLOW_SCHEDULE
75 AND mrp.relief_type = MPS_RELIEF_TYPE
76 AND mrp.request_id = arg_request_id
77 AND mrp.process_status = IN_PROCESS
78 AND mrp.error_message is NULL
79 AND mrp.inventory_item_id =
80 DECODE(arg_item_id,NULL_VALUE,
81 mrp.inventory_item_id,
82 arg_item_id)
83 AND mrp.organization_id =
84 DECODE(arg_org_id,NULL_VALUE,mrp.organization_id,
85 arg_org_id)
86 AND fs.primary_item_id = mrp.inventory_item_id
87 AND fs.organization_id = mrp.organization_id
88 AND fs.wip_entity_id = mrp.disposition_id
89 FOR UPDATE OF fs.mps_net_quantity,
90 fs.mps_scheduled_completion_date
91 ORDER BY fs.organization_id, fs.wip_entity_id;
92
93 CURSOR consol_cursor_ms IS
94 SELECT ms.rowid
95 FROM mtl_supply ms
96 where ms.rowid in (
97 SELECT /*+ INDEX(supply MTL_SUPPLY_N7) */
98 supply.rowid
99 FROM mtl_supply supply,
100 mrp_relief_interface mrp
101 WHERE mrp.disposition_type = R_PURCH_REQ
102 AND mrp.relief_type = MPS_RELIEF_TYPE
103 AND mrp.inventory_item_id =
104 DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
105 arg_item_id)
106 AND mrp.organization_id =
107 DECODE(arg_org_id, NULL_VALUE, organization_id,
108 arg_org_id)
109 AND supply.item_id = mrp.inventory_item_id
110 AND supply.supply_type_code = 'REQ'
111 AND mrp.line_num = supply.req_line_id
112 AND supply.to_organization_id = mrp.organization_id
113 AND mrp.disposition_id= supply.req_header_id
114 AND supply.destination_type_code = 'INVENTORY'
115 AND mrp.error_message is NULL
116 AND mrp.process_status = IN_PROCESS
117 AND mrp.request_id = arg_request_id
118 UNION
119 SELECT /*+ INDEX(supply MTL_SUPPLY_N5) */
120 supply.rowid
121 FROM mtl_supply supply,
122 mrp_relief_interface mrp
123 WHERE mrp.disposition_type = R_PURCH_ORDER
124 AND mrp.relief_type = MPS_RELIEF_TYPE
125 AND mrp.inventory_item_id =
126 DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
127 arg_item_id)
128 AND mrp.organization_id =
129 DECODE(arg_org_id, NULL_VALUE, organization_id,
130 arg_org_id)
131 AND supply.item_id = mrp.inventory_item_id
132 AND mrp.line_num = supply.po_line_id
133 AND supply.to_organization_id = mrp.organization_id
134 AND mrp.disposition_id= supply.po_header_id
135 AND supply.supply_type_code = 'PO'
136 AND supply.destination_type_code = 'INVENTORY'
137 AND mrp.error_message is NULL
138 AND mrp.request_id = arg_request_id
139 AND mrp.process_status = IN_PROCESS
140 UNION
141 SELECT /*+ INDEX(supply MTL_SUPPLY_N9) */
142 supply.rowid
143 FROM mtl_supply supply,
144 mrp_relief_interface mrp
145 WHERE mrp.disposition_type = R_SHIPMENT
146 AND mrp.relief_type = MPS_RELIEF_TYPE
147 AND mrp.inventory_item_id =
148 DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
149 arg_item_id)
150 AND mrp.organization_id =
151 DECODE(arg_org_id, NULL_VALUE, organization_id,
152 arg_org_id)
153 AND supply.item_id = mrp.inventory_item_id
154 AND mrp.line_num = supply.shipment_line_id
155 AND supply.to_organization_id = mrp.organization_id
156 AND mrp.disposition_id= supply.shipment_header_id
157 AND supply.supply_type_code = 'SHIPMENT'
158 AND supply.destination_type_code = 'INVENTORY'
159 AND mrp.error_message is NULL
160 AND mrp.process_status = IN_PROCESS
161 AND mrp.request_id = arg_request_id
162 UNION
163 SELECT /*+ INDEX(supply MTL_SUPPLY_N5) */
164 supply.rowid
165 FROM mtl_supply supply,
166 mrp_relief_interface mrp
167 WHERE mrp.disposition_type = R_PO_RECV
168 AND mrp.relief_type = MPS_RELIEF_TYPE
169 AND mrp.inventory_item_id =
170 DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
171 arg_item_id)
172 AND mrp.organization_id =
173 DECODE(arg_org_id, NULL_VALUE, organization_id,
174 arg_org_id)
175 AND supply.item_id = mrp.inventory_item_id
176 AND mrp.line_num = supply.po_line_id
177 AND supply.to_organization_id = mrp.organization_id
178 AND mrp.disposition_id= supply.po_header_id
179 AND supply.supply_type_code = 'RECEIVING'
180 AND supply.destination_type_code = 'INVENTORY'
181 AND mrp.error_message is NULL
182 AND mrp.process_status = IN_PROCESS
183 AND mrp.request_id = arg_request_id
184 UNION
185 SELECT /*+ INDEX(supply MTL_SUPPLY_N9) */
186 supply.rowid
187 FROM mtl_supply supply,
188 mrp_relief_interface mrp
189 WHERE mrp.disposition_type = R_SHIPMENT_RCV
190 AND mrp.relief_type = MPS_RELIEF_TYPE
191 AND mrp.inventory_item_id =
192 DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
193 arg_item_id)
194 AND mrp.organization_id =
195 DECODE(arg_org_id, NULL_VALUE, organization_id,
196 arg_org_id)
197 AND mrp.error_message is NULL
198 AND supply.item_id = mrp.inventory_item_id
199 AND mrp.line_num = supply.shipment_line_id
200 AND supply.to_organization_id = mrp.organization_id
201 AND mrp.disposition_id= supply.shipment_header_id
202 AND supply.supply_type_code = 'RECEIVING'
203 AND supply.destination_type_code = 'INVENTORY'
204 AND mrp.process_status = IN_PROCESS
205 AND mrp.request_id = arg_request_id)
206 FOR UPDATE OF ms.mrp_expected_delivery_date ;
207
208 CURSOR dates_cursor IS
209 SELECT dates.rowid
210 FROM
211 mrp_schedule_dates dates
212 WHERE dates.schedule_quantity >= 0
213 AND dates.original_schedule_quantity >= 0
214 AND dates.schedule_level = PSEUDO_SCHEDULE
215 AND dates.schedule_origination_type = NULL_VALUE
216 AND dates.supply_demand_type = SCHEDULE_SUPPLY
217 AND (dates.organization_id,dates.schedule_designator) IN
218 (select
219 nvl(plans.planned_organization,
220 desig.organization_id),
221 desig.schedule_designator
222 from
223 mrp_schedule_designators desig,
224 mrp_plan_organizations_v plans
225 WHERE
226 NVL(desig.disable_date, TRUNC(SYSDATE)+1)>TRUNC(SYSDATE)
227 AND desig.mps_relief = 1
228 AND desig.schedule_type = 2
229 AND desig.schedule_designator =plans.compile_designator(+)
230 AND desig.organization_id = plans.organization_id (+)
231 AND nvl(plans.planned_organization,
232 desig.organization_id) in
233 (select distinct mrp.organization_id
234 from mrp_relief_interface mrp
235 where
236 mrp.relief_type = MPS_RELIEF_TYPE
237 AND mrp.error_message is NULL
238 AND mrp.process_status = IN_PROCESS
239 AND mrp.request_id = arg_request_id)
240 );
241
242 CURSOR recommendations_cursor IS
243 SELECT /* ORDERED
244 INDEX(recom MRP_RECOMMENDATIONS_N1)
245 INDEX(mrp MRP_RELIEF_INTERFACE_N1)
246 INDEX(plans MRP_PLANS_U1) */
247 recom.rowid
248 FROM mrp_recommendations recom
249 WHERE recom.new_order_quantity >= 0
250 AND recom.firm_planned_type = SYS_NO
251 AND recom.disposition_status_type = NULL_VALUE
252 AND recom.order_type = PSEUDO_PLANNED_ORDER
253 AND (recom.compile_designator,recom.organization_id)
254 IN
255 (select plans.compile_designator ,
256 plans.planned_organization
257 from
258 mrp_designators_view desig,
259 mrp_plan_organizations_v plans
260 where
261 NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
262 AND desig.organization_id = plans.organization_id
263 AND desig.designator = plans.compile_designator
264 AND plans.planned_organization in
265 (select distinct mrp.organization_id
266 from mrp_relief_interface mrp
267 where
268 mrp.relief_type = MPS_RELIEF_TYPE
269 AND mrp.error_message is NULL
270 AND mrp.process_status = IN_PROCESS
271 AND mrp.request_id = arg_request_id)
272 );
273
274 BEGIN
275
276
277 var_watch_id := mrp_print_pk.start_watch(
278 'GEN-deleted from table', arg_request_id, arg_user_id,
279 'TABLE', 'mrp_schedule_dates', 'N');
280 var_row_count := 0;
281 OPEN dates_cursor;
282 LOOP
283
284 FETCH dates_cursor INTO var_rowid;
285 exit when dates_cursor%notfound;
286
287 DELETE FROM mrp_schedule_dates dates
291
288 WHERE rowid = var_rowid;
289
290 var_row_count := var_row_count + NVL(SQL%ROWCOUNT, 0);
292 END LOOP;
293 CLOSE dates_cursor;
294 mrp_print_pk.stop_watch(arg_request_id, var_watch_id, var_row_count);
295
296
297 var_watch_id := mrp_print_pk.start_watch(
298 'GEN-deleted from table', arg_request_id, arg_user_id,
299 'TABLE', 'mrp_recommendations', 'N');
300 var_row_count := 0;
301 OPEN recommendations_cursor;
302 LOOP
303
304 FETCH recommendations_cursor INTO var_rowid;
305 exit when recommendations_cursor%notfound;
306
307 DELETE FROM mrp_recommendations recom
308 WHERE rowid = var_rowid;
309
310 var_row_count := var_row_count + NVL(SQL%ROWCOUNT, 0);
311
312 END LOOP;
313 CLOSE recommendations_cursor;
314 mrp_print_pk.stop_watch(arg_request_id, var_watch_id, var_row_count);
315
316 --
317 -- If called for wip jobs set the completion_date and completion
318 -- quantities for all the jobs that we performed relief
319 --
320
321 var_watch_id := mrp_print_pk.start_watch(
322 'GEN-updated', arg_request_id, arg_user_id, 'ENTITY',
323 'wip_discrete_jobs', 'N');
324 var_row_count := 0;
325 SAVEPOINT jobs;
326 LOOP
327 BEGIN
328 OPEN jobs_cursor;
329 EXIT;
330 EXCEPTION
331 WHEN deadlock THEN
332 ROLLBACK TO SAVEPOINT jobs;
333 dbms_lock.sleep(5);
334 END;
335 END LOOP;
336
337 LOOP
338
339 FETCH jobs_cursor INTO var_rowid, var_wip_entity_id, var_org_id;
340 exit when jobs_cursor%notfound;
341
342 if (prev_wip_entity_id <> var_wip_entity_id OR
343 prev_org_id <> var_org_id)
344 then
345 UPDATE wip_discrete_jobs jobs
346 SET mps_scheduled_completion_date = scheduled_completion_date,
347 mps_net_quantity = net_quantity,
348 last_update_date = SYSDATE,
349 last_updated_by = arg_user_id
350 WHERE rowid = var_rowid;
351
352 var_row_count := var_row_count + NVL(SQL%ROWCOUNT, 0) ;
353
354 UPDATE wip_requirement_operations ops
355 SET ops.mps_required_quantity = ops.required_quantity,
356 ops.mps_date_required = ops.date_required,
357 ops.last_update_date = SYSDATE,
358 ops.last_updated_by = arg_user_id
359 WHERE wip_entity_id = var_wip_entity_id
360 AND organization_id = var_org_id;
361
362 var_row_count := var_row_count + NVL(SQL%ROWCOUNT, 0) ;
363 end if;
364 prev_wip_entity_id := var_wip_entity_id;
365 prev_org_id := var_org_id;
366
367 END LOOP;
368 CLOSE jobs_cursor;
369 mrp_print_pk.stop_watch(arg_request_id, var_watch_id,var_row_count);
370
371 --
372 -- WIP Flow Schedules: set the completion_date and completion
373 -- quantities for all the flow schedules that we performed relief
374 --
375 var_watch_id := mrp_print_pk.start_watch(
376 'GEN-updated', arg_request_id, arg_user_id, 'ENTITY',
377 'wip_flow_schedules', 'N');
378 var_row_count := 0;
379 prev_wip_entity_id := -1;
380 prev_org_id := -1;
381 SAVEPOINT flow;
382
383 LOOP
384 BEGIN
385 OPEN flow_schedules_cursor;
386 EXIT;
387 EXCEPTION
388 WHEN deadlock THEN
389 ROLLBACK TO SAVEPOINT flow;
390 dbms_lock.sleep(5);
391 END;
392 END LOOP;
393
394 LOOP
395
396 FETCH flow_schedules_cursor
397 INTO var_rowid, var_wip_entity_id, var_org_id;
398 EXIT WHEN flow_schedules_cursor%notfound;
399
400 IF (prev_wip_entity_id <> var_wip_entity_id OR
401 prev_org_id <> var_org_id)
402 THEN
403 UPDATE wip_flow_schedules fs
404 SET mps_scheduled_completion_date = scheduled_completion_date,
405 mps_net_quantity = planned_quantity,
406 last_update_date = SYSDATE,
407 last_updated_by = arg_user_id
408 WHERE rowid = var_rowid;
409
410 var_row_count := var_row_count + NVL(SQL%ROWCOUNT, 0) ;
411 END IF;
412
413 prev_wip_entity_id := var_wip_entity_id;
414 prev_org_id := var_org_id;
415
416 END LOOP;
417 CLOSE flow_schedules_cursor;
418 mrp_print_pk.stop_watch(arg_request_id, var_watch_id, var_row_count);
419
420 var_watch_id := mrp_print_pk.start_watch(
421 'GEN-updated', arg_request_id, arg_user_id,
422 'ENTITY', 'mtl_supply', 'N');
423
424 var_row_count := 0;
425
426 -- Req Cursor
427 SAVEPOINT mtl_sup;
428
429 LOOP
430 BEGIN
431 OPEN consol_cursor_ms;
432 EXIT;
433 EXCEPTION
434 WHEN deadlock THEN
435 ROLLBACK TO SAVEPOINT mtl_sup;
436 dbms_lock.sleep(5);
437 END;
438 END LOOP;
439
440 LOOP
441
442 FETCH consol_cursor_ms INTO var_rowid;
443 exit when consol_cursor_ms%notfound;
444
445 UPDATE mtl_supply supply
446 SET mrp_expected_delivery_date = expected_delivery_date ,
447 mrp_primary_quantity = to_org_primary_quantity,
448 mrp_to_organization_id = to_organization_id,
449 mrp_destination_type_code = destination_type_code,
450 mrp_to_subinventory = to_subinventory,
451 last_update_date = SYSDATE,
452 last_updated_by = arg_user_id,
453 mrp_primary_uom =
454 (SELECT uom_code
455 FROM mtl_units_of_measure
456 WHERE unit_of_measure = supply.to_org_primary_uom)
457 WHERE rowid = var_rowid;
458
459 var_row_count := var_row_count + NVL(SQL%ROWCOUNT, 0);
460
461 END LOOP;
462 CLOSE consol_cursor_ms;
463
464 mrp_print_pk.stop_watch(arg_request_id, var_watch_id,var_row_count);
465
466 END mrp_update_mrp_cols;
467
468 END MRP_UPDATE_MRP_INFO_PK;