[Home] [Help]
PACKAGE BODY: APPS.CHV_LOAD_ORDERS
Source
1 PACKAGE BODY CHV_LOAD_ORDERS as
2 /* $Header: CHVPRLOB.pls 120.3 2006/05/22 20:16:48 lswamy noship $ */
3
4 /*=========================== CHV_LOAD_ORDERS ===============================*/
5 /*=============================================================================
6
7 PROCEDURE NAME: load_item_orders()
8
9 =============================================================================*/
10
11 PROCEDURE load_item_orders(x_organization_id IN NUMBER,
12 x_schedule_id IN NUMBER,
13 x_schedule_item_id IN NUMBER,
14 x_vendor_id IN NUMBER,
15 x_vendor_site_id IN NUMBER,
16 x_item_id IN NUMBER,
17 x_purchasing_unit_of_measure IN VARCHAR2,
18 x_primary_unit_of_measure IN VARCHAR2,
19 x_conversion_rate IN NUMBER,
20 x_horizon_start_date IN DATE,
21 x_horizon_end_date IN DATE,
22 x_include_future_rel_flag IN VARCHAR2,
23 x_schedule_type IN VARCHAR2,
24 x_schedule_subtype IN VARCHAR2,
25 x_plan_designator IN VARCHAR2) IS
26
27 X_progress VARCHAR2(3) := '';
28 X_only_past_due_flag VARCHAR2(1) := 'N' ;
29
30 BEGIN
31
32 --dbms_output.put_line('Entering Load Item Orders');
33
34 -- Evaluate schedule type PLANNING or SHIPPING
35 IF x_schedule_type = 'PLAN_SCHEDULE' THEN
36
37 --dbms_output.put_line('Load Item Orders: Planning Schedule'||x_schedule_subtype);
38
39 -- If the Schedule type is PLANNING evaluate the schedule
40 -- subtype.
41 IF x_schedule_subtype = 'FORECAST_ONLY' THEN
42 x_only_past_due_flag := 'Y' ;
43
44 -- DEBUG.
45 -- If releases are not being included for the schedule subtype,
46 -- we still need to get them for past due.
47 -- For load_approved_releases we need to add a only_past_due_flag.
48
49 --dbms_output.put_line('Load Item Orders: Planning Schedule Forecast Only');
50
51 -- If the schedule subtype is FORECAST ONLY then
52 -- execute procedure to load all MRP/MPS/DRP planned orders
53 chv_load_orders.load_planned_orders(x_organization_id,
54 x_schedule_id,
55 x_schedule_item_id,
56 x_vendor_id,
57 x_vendor_site_id,
58 x_item_id,
59 x_purchasing_unit_of_measure,
60 x_primary_unit_of_measure,
61 x_conversion_rate,
62 x_horizon_start_date,
63 x_horizon_end_date,
64 x_schedule_type,
65 x_schedule_subtype,
66 x_plan_designator) ;
67
68 --dbms_output.put_line('Load App Reqs: Planning Schedule Forecast Only');
69 chv_load_orders.load_approved_requisitions(x_organization_id,
70 x_schedule_id,
71 x_schedule_item_id,
72 x_vendor_id,
73 x_vendor_site_id,
74 x_item_id,
75 x_purchasing_unit_of_measure,
76 x_primary_unit_of_measure,
77 x_conversion_rate,
78 x_horizon_start_date,
79 x_horizon_end_date,
80 x_schedule_type,
81 x_schedule_subtype);
82
83 -- DEBUG. call load_approved_releases with only_past_due = 'Y'
84 --dbms_output.put_line('Load App Reqs: Planning Schedule Forecast Only');
85 chv_load_orders.load_approved_releases(x_organization_id,
86 x_schedule_id,
87 x_schedule_item_id,
88 x_vendor_id,
89 x_vendor_site_id,
90 x_item_id,
91 x_purchasing_unit_of_measure,
92 x_primary_unit_of_measure,
93 x_conversion_rate,
94 x_horizon_start_date,
95 x_horizon_end_date,
96 x_only_past_due_flag,
97 x_include_future_rel_flag) ;
98 ELSIF x_schedule_subtype = 'FORECAST_ALL_DOCUMENTS' THEN
99 x_only_past_due_flag := 'N' ;
100
101 --dbms_output.put_line('Load item Orders: Planning Sched - Forecast All Documents');
102
103 -- If the schedule subtype is FORECAST ALL DOCUMENTS then
104 -- execute procedure to load all MRP/MPS/DRP planned orders
105 -- approved requisitions and approved releases as forecast.
106 chv_load_orders.load_planned_orders(x_organization_id,
107 x_schedule_id,
108 x_schedule_item_id,
109 x_vendor_id,
110 x_vendor_site_id,
111 x_item_id,
112 x_purchasing_unit_of_measure,
113 x_primary_unit_of_measure,
114 x_conversion_rate,
115 x_horizon_start_date,
116 x_horizon_end_date,
117 x_schedule_type,
118 x_schedule_subtype,
119 x_plan_designator) ;
120
121 chv_load_orders.load_approved_requisitions(x_organization_id,
122 x_schedule_id,
123 x_schedule_item_id,
124 x_vendor_id,
125 x_vendor_site_id,
126 x_item_id,
127 x_purchasing_unit_of_measure,
128 x_primary_unit_of_measure,
129 x_conversion_rate,
130 x_horizon_start_date,
131 x_horizon_end_date,
132 x_schedule_type,
133 x_schedule_subtype);
134
135 chv_load_orders.load_approved_releases(x_organization_id,
136 x_schedule_id,
137 x_schedule_item_id,
138 x_vendor_id,
139 x_vendor_site_id,
140 x_item_id,
141 x_purchasing_unit_of_measure,
142 x_primary_unit_of_measure,
143 x_conversion_rate,
144 x_horizon_start_date,
145 x_horizon_end_date,
146 x_only_past_due_flag,
147 x_include_future_rel_flag) ;
148
149 ELSIF x_schedule_subtype = 'MATERIAL_RELEASE' THEN
150 x_only_past_due_flag := 'N' ;
151
152 --dbms_output.put_line('Planning Schedule Material Release');
153
154 -- If the schedule subtype is MATERIAL RELEASE then
155 -- execute procedure to load all MRP/MPS/DRP as planned orders
156 -- approved requisitions as approved releases planned orders
157 -- approved requisitions as approved releases
158 chv_load_orders.load_planned_orders(x_organization_id,
159 x_schedule_id,
160 x_schedule_item_id,
161 x_vendor_id,
162 x_vendor_site_id,
163 x_item_id,
164 x_purchasing_unit_of_measure,
165 x_primary_unit_of_measure,
166 x_conversion_rate,
167 x_horizon_start_date,
168 x_horizon_end_date,
169 x_schedule_type,
170 x_schedule_subtype,
171 x_plan_designator) ;
172
173 chv_load_orders.load_approved_requisitions(x_organization_id,
174 x_schedule_id,
175 x_schedule_item_id,
176 x_vendor_id,
177 x_vendor_site_id,
178 x_item_id,
179 x_purchasing_unit_of_measure,
180 x_primary_unit_of_measure,
181 x_conversion_rate,
182 x_horizon_start_date,
183 x_horizon_end_date,
184 x_schedule_type,
185 x_schedule_subtype) ;
186
187 chv_load_orders.load_approved_releases(x_organization_id,
188 x_schedule_id,
189 x_schedule_item_id,
190 x_vendor_id,
191 x_vendor_site_id,
192 x_item_id,
193 x_purchasing_unit_of_measure,
194 x_primary_unit_of_measure,
195 x_conversion_rate,
196 x_horizon_start_date,
197 x_horizon_end_date,
198 x_only_past_due_flag,
199 x_include_future_rel_flag) ;
200 END IF ;
201
202 ELSIF x_schedule_type = 'SHIP_SCHEDULE' THEN
203
204 -- IF schedule type is SHIP SCHEDULE evaluate the
205 -- schedule subtype.
206 IF x_schedule_subtype = 'RELEASE_ONLY' THEN
207 x_only_past_due_flag := 'N' ;
208
209 --dbms_output.put_line('Ship Sched - Release Only');
210
211 -- If schedule subtype is RELEASE ONLY then
212 -- execute procedure to load all approved releases.
213 chv_load_orders.load_approved_releases(x_organization_id,
214 x_schedule_id,
215 x_schedule_item_id,
216 x_vendor_id,
217 x_vendor_site_id,
218 x_item_id,
219 x_purchasing_unit_of_measure,
220 x_primary_unit_of_measure,
221 x_conversion_rate,
222 x_horizon_start_date,
223 x_horizon_end_date,
224 x_only_past_due_flag,
225 x_include_future_rel_flag) ;
226
227 ELSIF x_schedule_subtype = 'RELEASE_WITH_FORECAST' THEN
228 x_only_past_due_flag := 'N' ;
229
230 --dbms_output.put_line('Ship Sched - Release where/ Frecast');
231 -- If schedule subtype is RELEASE WITH FORECAST then
232 -- execute procedure to load all MRP/MPS/DRP as planned orders
233 -- approved requisitions as approved releases planned orders
234 -- and approved requisitions as approved releases
235
236 chv_load_orders.load_planned_orders(x_organization_id,
237 x_schedule_id,
238 x_schedule_item_id,
239 x_vendor_id,
240 x_vendor_site_id,
241 x_item_id,
242 x_purchasing_unit_of_measure,
243 x_primary_unit_of_measure,
244 x_conversion_rate,
245 x_horizon_start_date,
246 x_horizon_end_date,
247 x_schedule_type,
248 x_schedule_subtype,
249 x_plan_designator) ;
250
251 chv_load_orders.load_approved_requisitions(x_organization_id,
252 x_schedule_id,
253 x_schedule_item_id,
254 x_vendor_id,
255 x_vendor_site_id,
256 x_item_id,
257 x_purchasing_unit_of_measure,
258 x_primary_unit_of_measure,
259 x_conversion_rate,
260 x_horizon_start_date,
261 x_horizon_end_date,
262 x_schedule_type,
263 x_schedule_subtype) ;
264
265 chv_load_orders.load_approved_releases(x_organization_id,
266 x_schedule_id,
267 x_schedule_item_id,
268 x_vendor_id,
269 x_vendor_site_id,
270 x_item_id,
271 x_purchasing_unit_of_measure,
272 x_primary_unit_of_measure,
273 x_conversion_rate,
274 x_horizon_start_date,
275 x_horizon_end_date,
276 x_only_past_due_flag,
277 x_include_future_rel_flag) ;
278
279 END IF ;
280
281 END IF ;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 po_message_s.sql_error('load_item_orders', X_progress, sqlcode);
286 raise;
287
288 END load_item_orders ;
289
290 /*=============================================================================
291
292 PROCEDURE NAME: load_planned_orders()
293
294 =============================================================================*/
295 PROCEDURE load_planned_orders(x_organization_id IN NUMBER,
296 x_schedule_id IN NUMBER,
297 x_schedule_item_id IN NUMBER,
298 x_vendor_id IN NUMBER,
299 x_vendor_site_id IN NUMBER,
300 x_item_id IN NUMBER,
301 x_purchasing_unit_of_measure IN VARCHAR2,
302 x_primary_unit_of_measure IN VARCHAR2,
303 x_conversion_rate IN NUMBER,
304 x_horizon_start_date IN DATE,
305 x_horizon_end_date IN DATE,
306 x_schedule_type IN VARCHAR2,
307 x_schedule_subtype IN VARCHAR2,
308 x_plan_designator IN VARCHAR2) IS
309
310 /* Declaring Program Variables */
311 X_need_by_date DATE;
312 X_login_id NUMBER;
313 X_last_updated_by NUMBER;
314 X_progress VARCHAR2(3) := '';
315 X_ord_qty NUMBER;
316 X_new_dock_date DATE;
317 X_transaction_id NUMBER;
318
319 -- The new quantity represents the planned orders. The Quantity in Process
320 -- represents planned orders that have been implemented from the workbench.
321 -- Quantity in mrp_recommendations is always represented in primary uom.
322 -- This coresponds to recrods in the requisition interface table waiting
323 -- for req import to process them. From the email I sent earlier this
324 -- week: In order to get the unimplemented quantity of planned orders you
325 -- should use: NVL(firm_quantity, new_order_quantity) -
326 -- quantity_in_process - implemented_quantity.
327
328 /* Bug 1140926 fixed . Forward port of the bug 1133892.
329 Due date for the planned order should first look at
330 the firm_date and then at the new_dock_date. So changing
331 the select to nvl(firm_date, new_dock_date) and also
332 the where cond. to nvl(firm_date, new_dock_date)
333 between x_horizon_start_date and x_horizon_end_date
334 */
335
336 CURSOR C1 is select transaction_id,
337 nvl(firm_date,new_dock_date),
338 NVL(firm_quantity, new_order_quantity) -
339 (NVL(quantity_in_process,0) + nvl(implemented_quantity,0) )
340 from mrp_recommendations mrp
341 where mrp.organization_id = x_organization_id
342 and mrp.source_vendor_id = x_vendor_id
343 and x_vendor_site_id =
344 (select distinct pvs.vendor_site_id
345 from po_vendor_sites_all pvsa,
346 po_vendor_sites pvs
347 where pvsa.vendor_site_id = mrp.source_vendor_site_id and
348 pvsa.vendor_id = x_vendor_id and
349 pvs.vendor_site_code = pvsa.vendor_site_code and
350 pvs.vendor_id = x_vendor_id)
351 and mrp.inventory_item_id = x_item_id
352 and mrp.transaction_id = mrp.disposition_id
353 and mrp.order_type = 5
354 and mrp.compile_designator = x_plan_designator
355 and mrp.disposition_id = mrp.transaction_id
356 and nvl(mrp.firm_date,mrp.new_dock_date) between
357 x_horizon_start_date and
358 x_horizon_end_date ;
359 BEGIN
360
361 X_login_id := fnd_global.login_id;
362 X_last_updated_by := fnd_global.user_id;
363
364 X_progress := '030';
365
366 OPEN C1;
367
368 LOOP
369 --dbms_output.put_line('SCH ID:'||TO_CHAR(X_SCHEDULE_ID)) ;
370 --dbms_output.put_line('ITEM:'||TO_CHAR(x_schedule_item_id)) ;
371 --dbms_output.put_line('VENDOR ID:'||TO_CHAR(X_VENDOR_ID)) ;
372 --dbms_output.put_line('VENDOR SITE:'||TO_CHAR(X_VENDOR_SITE_ID)) ;
373 --dbms_output.put_line('ITEM:'||TO_CHAR(X_ITEM_ID)) ;
374 --dbms_output.put_line('PUOM:'||x_purchasing_unit_of_measure) ;
375 --dbms_output.put_line('PRI UOM:'||x_primary_unit_of_measure) ;
376 --dbms_output.put_line('CONV RATE:'||x_conversion_rate) ;
377 --dbms_output.put_line('PURCH QTY:'||TO_CHAR(nvl(x_ord_qty * x_conversion_rate,0))) ;
378 --dbms_output.put_line('PRI QTY:'||TO_CHAR(nvl(x_ord_qty,0))) ;
379 --dbms_output.put_line('DOCK DATE:'||TO_CHAR(x_new_dock_date,'DD-MON-YYYY')) ;
380 --dbms_output.put_line('START DATE:'||TO_CHAR(X_HORIZON_START_DATE,'DD-MON-YYYY')) ;
381 --dbms_output.put_line('END DATE:'||TO_CHAR(X_HORIZON_END_DATE,'DD-MON-YYYY')) ;
382 --dbms_output.put_line('PLAN NAME:'||x_plan_designator) ;
383
384
385 --dbms_output.put_line('before fetch');
386
387 X_progress := '040';
388
389 FETCH C1 INTO X_transaction_id,
390 X_new_dock_date,
391 X_ord_qty;
392
393 EXIT WHEN C1%notfound ;
394
395 X_progress := '050';
396 insert into chv_item_orders(schedule_id,
397 schedule_item_id,
398 schedule_order_id,
399 supply_document_type,
400 order_quantity,
401 order_quantity_primary,
402 purchasing_unit_of_measure,
403 primary_unit_of_measure,
404 due_date,
405 last_update_date,
406 last_updated_by,
407 creation_date,
408 created_by,
409 last_update_login)
410 values(x_schedule_id,
411 x_schedule_item_id,
412 CHV_ITEM_ORDERS_S.nextval,
413 'PLANNED_ORDER',
414 nvl(x_ord_qty * x_conversion_rate,0),
415 nvl(x_ord_qty,0),
416 x_purchasing_unit_of_measure,
417 x_primary_unit_of_measure,
418 x_new_dock_date,
419 sysdate,
420 X_last_updated_by,
421 sysdate,
422 X_last_updated_by,
423 X_login_id) ;
424
425 end loop ;
426
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN null;
429 WHEN OTHERS THEN
430 CLOSE C1;
431 po_message_s.sql_error('load_planned_orders', X_progress, sqlcode);
432 RAISE;
433
434
435 END load_planned_orders ;
436
437 /*=============================================================================
438
439 PROCEDURE NAME: load_approved_requisitions()
440
441 =============================================================================*/
442
443 PROCEDURE load_approved_requisitions(x_organization_id IN NUMBER,
444 x_schedule_id IN NUMBER,
445 x_schedule_item_id IN NUMBER,
446 x_vendor_id IN NUMBER,
447 x_vendor_site_id IN NUMBER,
448 x_item_id IN NUMBER,
449 x_purchasing_unit_of_measure IN VARCHAR2,
450 x_primary_unit_of_measure IN VARCHAR2,
451 x_conversion_rate IN NUMBER,
452 x_horizon_start_date IN DATE,
453 x_horizon_end_date IN DATE,
454 x_schedule_type IN VARCHAR2,
455 x_schedule_subtype IN VARCHAR2) IS
456
457 X_requisition_header_id NUMBER;
458 X_requisition_line_id NUMBER;
459 X_to_org_primary_quantity NUMBER;
460 X_to_org_purch_quantity NUMBER;
461 X_need_by_date DATE;
462 X_login_id NUMBER;
463 X_last_updated_by NUMBER;
464 X_progress VARCHAR2(3) := '';
465
466 cursor C1 is select prh.requisition_header_id,
467 prl.requisition_line_id,
468 ms.to_org_primary_quantity,
469 prl.need_by_date
470 from po_requisition_headers prh,
471 po_requisition_lines prl,
472 mtl_supply ms,
473 po_vendors pov,
474 po_vendor_sites pvs
475 where ms.to_organization_id = x_organization_id
476 and ms.supply_type_code = 'REQ'
477 and ms.req_header_id = prh.requisition_header_id
478 and ms.req_line_id = prl.requisition_line_id
479 and ms.quantity <> 0
480 and prl.suggested_vendor_name
481 = pov.vendor_name
482 and pov.vendor_id = x_vendor_id
483 and prl.suggested_vendor_location
484 = pvs.vendor_site_code
485 and pvs.vendor_site_id = x_vendor_site_id
486 and prl.item_id = x_item_id
487 and prl.source_type_code = 'VENDOR'
488 and prl.need_by_date
489 between x_horizon_start_date and
490 x_horizon_end_date ;
491
492 BEGIN
493
494 --dbms_output.put_line('Get Approved Reqs: Calling');
495 --dbms_output.put_line('Get Approved Reqs: Vendor Id'||x_vendor_id);
496 --dbms_output.put_line('Get Approved Reqs: Vendor Site'||x_vendor_site_id);
497 --dbms_output.put_line('Get Approved Reqs: Item'||x_item_id);
498 --dbms_output.put_line('Get Approved Reqs: Start date'||x_horizon_start_date);
499 --dbms_output.put_line('Get Approved Reqs: End date'||x_horizon_end_date);
500
501 X_login_id := fnd_global.login_id;
502 X_last_updated_by := fnd_global.user_id;
503
504 X_progress := '030';
505
506 OPEN C1;
507
508 LOOP
509
510 --dbms_output.put_line('Get Approved Reqs: Before fetch');
511
512 X_progress := '040';
513
514 FETCH C1 INTO
515 X_requisition_header_id,
516 X_requisition_line_id,
517 X_to_org_primary_quantity,
518 X_need_by_date;
519
520 EXIT WHEN C1%notfound;
521
522 -- Calculate the purchasing quantity based on conversion rate
523 -- from primary to purchasing that was passed in.
524
525 -- DEBUG. Pri A. verify that mtl_supply stores primary unit of measure
526 -- rather than primary unit of measure code. The table has
527 -- length of 25.
528
529 -- DEBUG. Pri C. Why do we need a cursor her. He should be able to do
530 -- insert as a select from. Need to test using sequences.
531
532 X_to_org_purch_quantity := X_conversion_rate * X_to_org_primary_quantity;
533
534 X_progress := '050';
535
536 --dbms_output.put_line('Get Approved Reqs: Before insert');
537
538 insert into chv_item_orders(schedule_id,
539 schedule_item_id,
540 schedule_order_id,
541 supply_document_type,
542 order_quantity,
543 order_quantity_primary,
544 purchasing_unit_of_measure,
545 primary_unit_of_measure,
546 due_date,
547 document_header_id,
548 last_update_date,
549 last_updated_by,
550 creation_date,
551 created_by,
552 last_update_login,
553 document_line_id,
554 document_shipment_id)
555 values(x_schedule_id,
556 x_schedule_item_id,
557 CHV_ITEM_ORDERS_S.nextval,
558 'REQUISITION',
559 nvl(X_to_org_purch_quantity,
560 X_to_org_primary_quantity),
561 X_to_org_primary_quantity,
562 X_purchasing_unit_of_measure,
563 X_primary_unit_of_measure,
564 X_need_by_date,
565 X_requisition_header_id,
566 sysdate,
567 X_last_updated_by,
568 sysdate,
569 X_last_updated_by,
570 X_login_id,
571 X_requisition_line_id,
572 null);
573
574 END LOOP ;
575
576 CLOSE C1;
577
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN null;
580 WHEN OTHERS THEN
581 CLOSE C1;
582 po_message_s.sql_error('load_approved_reqs', X_progress, sqlcode);
583 RAISE;
584
585 END load_approved_requisitions ;
586
587 /*=============================================================================
588
589 PROCEDURE NAME: load_approved_releases()
590
591 =============================================================================*/
592
593 -- DEBUG. add only_past_due_flag
594
595 PROCEDURE load_approved_releases(x_organization_id IN NUMBER,
596 x_schedule_id IN NUMBER,
597 x_schedule_item_id IN NUMBER,
598 x_vendor_id IN NUMBER,
599 x_vendor_site_id IN NUMBER,
600 x_item_id IN NUMBER,
601 x_purchasing_unit_of_measure IN VARCHAR2,
602 x_primary_unit_of_measure IN VARCHAR2,
603 x_conversion_rate IN NUMBER,
604 x_horizon_start_date IN DATE,
605 x_horizon_end_date IN DATE,
606 x_only_past_due_flag IN VARCHAR2,
607 x_include_future_rel_flag IN VARCHAR2) IS
608
609 /* Declaring Program Variables */
610 X_po_header_id NUMBER;
611 X_po_line_id NUMBER;
612 X_line_location_id NUMBER;
613 X_to_org_primary_quantity NUMBER;
614 X_to_org_primary_uom VARCHAR2(25);
615 X_need_by_date DATE;
616 X_login_id NUMBER;
617 X_last_updated_by NUMBER;
618 X_progress VARCHAR2(3) := '';
619
620 -- DEBUG. logic
621 -- FORECAST_ALL_DOCUMENTS will always have include future releases = 'N'
622 -- If only_past_due_flag = 'Y' and include future releases = 'N'
623 -- need_by_date < horizon_end_date + 1.
624 --
625 -- MATERIAL_RELEASE,RELEASE_ONLY,RELEASE_WITH_FORECAST WITHOUT FUTURE RELEASES
626 -- If only_past_due_flag = 'N' and include future releases = 'N'
627 -- need_by_date > horizon_end_date
628 --
629 -- MATERIAL_RELEASE,RELEASE_ONLY,RELEASE_WITH_FORECAST WITH FUTURE RELEASES
630 -- If only_past_due_flag = 'N' and include future releases = 'Y'
631 -- Ignore needby date we need to load all open releases.
632
633 -- Document due date is determined by the promised date on the release.
634 -- If the promised date is not available for this document type, it
635 -- is determined by the the need by date.
636
637 /* Bug - 993145 - Added the need_by_date is NOT NULL condition in the
638 ** WHERE clause to avoid loading the NON Planned items. */
639
640 /* Bug 1769274 Added +0 to the poh.vendor_id to diable the index on vendor and
641 vendor site. This will use better indexes on the PO_HEADERS and MTL_SUPPLY
642 tables and hence will improve the performance.
643 */
644 /* Bug 4618577 fixed. Added format mask to to_date function */
645 /* Bug 5075549 fixed. Removed the to_date function to date columns */
646
647 /* bug5065917 : included supply_type_code of SHIPMENT to account for ASN's */
648 CURSOR C1 IS select ms.po_header_id,
649 ms.po_line_id,
650 ms.po_line_location_id,
651 ms.to_org_primary_quantity,
652 ms.to_org_primary_uom,
653 ms.need_by_date
654 from po_headers poh,
655 mtl_supply ms
656 where ms.to_organization_id = x_organization_id
657 and ms.supply_type_code in ('PO','SHIPMENT')
658 and ms.po_header_id = poh.po_header_id
659 and ms.item_id = x_item_id
660 and ms.quantity <> 0
661 and poh.type_lookup_code = 'BLANKET'
662 and poh.vendor_id +0 = x_vendor_id
663 and poh.vendor_site_id = x_vendor_site_id
664 and poh.supply_agreement_flag = 'Y'
665 and ((nvl(x_include_future_rel_flag,'N') = 'N'
666 and x_only_past_due_flag = 'Y'
667 and ms.need_by_date < x_horizon_start_date + 1)
668 OR
669 (nvl(x_include_future_rel_flag,'N') = 'N'
670 and x_only_past_due_flag = 'N'
671 and ms.need_by_date < x_horizon_end_date + 1)
672 OR
673 (x_include_future_rel_flag = 'Y'
674 and x_only_past_due_flag = 'N'
675 and ms.need_by_date is NOT NULL)
676 );
677
678 /* DEBUG. Pri C, Perform We do not need a cursor in this case to loop through
679 all of the records. We could of done a straight insert
680 as select from. We should modify in the future to improve
681 performance */
682
683 BEGIN
684
685 X_login_id := fnd_global.login_id;
686 X_last_updated_by := fnd_global.user_id;
687
688 X_progress := '020';
689
690 OPEN C1;
691
692 LOOP
693
694 --dbms_output.put_line('before fetch');
695
696 X_progress := '030';
697
698 FETCH C1 INTO X_po_header_id,
699 X_po_line_id,
700 X_line_location_id,
701 X_to_org_primary_quantity,
702 X_to_org_primary_uom,
703 X_need_by_date;
704 EXIT WHEN C1%notfound;
705
706 X_progress := '040';
707
708 insert into chv_item_orders(schedule_id,
709 schedule_item_id,
710 schedule_order_id,
711 supply_document_type,
712 order_quantity,
713 order_quantity_primary,
714 purchasing_unit_of_measure,
715 primary_unit_of_measure,
716 due_date,
717 document_header_id,
718 last_update_date,
719 last_updated_by,
720 creation_date,
721 created_by,
722 last_update_login,
723 document_line_id,
724 document_shipment_id)
725 values(x_schedule_id,
726 x_schedule_item_id,
727 CHV_ITEM_ORDERS_S.nextval,
728 'RELEASE',
729 x_conversion_rate *
730 X_to_org_primary_quantity,
731 X_to_org_primary_quantity,
732 x_purchasing_unit_of_measure,
733 X_to_org_primary_uom,
734 X_need_by_date,
735 X_po_header_id,
736 sysdate,
737 X_last_updated_by,
738 sysdate,
739 X_last_updated_by,
740 X_login_id,
741 X_po_line_id,
742 X_line_location_id) ;
743 END LOOP;
744
745 X_progress := '050';
746
747 CLOSE C1;
748
749 EXCEPTION
750 WHEN NO_DATA_FOUND THEN null;
751 WHEN OTHERS THEN
752 CLOSE C1;
753 po_message_s.sql_error('load_approved_releases', X_progress, sqlcode);
754 RAISE;
755
756
757 END load_approved_releases;
758
759 END CHV_LOAD_ORDERS ;