1 PACKAGE BODY CHV_INQ_SV2 as
2 /* $Header: CHVSIN2B.pls 115.1 99/07/17 01:31:29 porting sh $*/
3
4 /*============================= CHV_INQ_SV2 ===============================*/
5
6 FUNCTION get_last_receipt_num(p_org_id IN NUMBER,
7 p_item_id in NUMBER,
8 p_vendor_id in NUMBER,
9 p_vendor_site_id in NUMBER,
10 p_cum_period_start_date in DATE,
11 p_cum_period_end_date in DATE)
12 RETURN varchar2 is
13
14 x_last_receipt_id number := null ;
15 x_last_receipt_num varchar2(30) := null;
16
17 begin
18
19 select max(rct.transaction_id)
20 into x_last_receipt_id
21 from rcv_transactions rct,
22 rcv_shipment_lines rsl,
23 po_headers poh
24 where rct.shipment_line_id = rsl.shipment_line_id
25 and rct.transaction_type = 'RECEIVE'
26 and rct.transaction_date between
27 to_date(p_cum_period_start_date) and
28 to_date(p_cum_period_end_date)
29 and rsl.to_organization_id = p_org_id
30 and rsl.item_id = p_item_id
31 and rsl.po_header_id = poh.po_header_id
32 and poh.vendor_id = p_vendor_id
33 and poh.vendor_site_id = p_vendor_site_id
34 and poh.supply_agreement_flag = 'Y'
35 and rct.transaction_date in
36 (select max(rct2.transaction_date)
37 from rcv_transactions rct2,
38 rcv_shipment_lines rsl2,
39 po_headers poh2
40 where rct2.shipment_line_id = rsl2.shipment_line_id
41 and rct2.transaction_type = 'RECEIVE'
42 and rct2.transaction_date between
43 to_date(p_cum_period_start_date) and
44 to_date(p_cum_period_end_date)
45 and rsl2.to_organization_id = p_org_id
46 and rsl2.item_id = p_item_id
47 and rsl2.po_header_id = poh2.po_header_id
48 and poh2.vendor_id = p_vendor_id
49 and poh2.vendor_site_id = p_vendor_site_id
50 and poh2.supply_agreement_flag = 'Y');
51
52
53 select receipt_num
54 into x_last_receipt_num
55 from rcv_transactions rct,
56 rcv_shipment_headers rsh
57 where rct.transaction_id = x_last_receipt_id
58 and rct.shipment_header_id = rsh.shipment_header_id;
59
60 return(x_last_receipt_num) ;
61
62 exception when others then
63 return('') ;
64
65 end get_last_receipt_num;
66
67 FUNCTION get_last_receipt_date(p_org_id IN NUMBER,
68 p_item_id in NUMBER,
69 p_vendor_id in NUMBER,
70 p_vendor_site_id in NUMBER,
71 p_cum_period_start_date in DATE,
72 p_cum_period_end_date in DATE)
73 RETURN date is
74
75 x_last_receipt_id number := null ;
76 x_last_receipt_date date := null;
77
78 begin
79
80 select max(rct.transaction_id)
81 into x_last_receipt_id
82 from rcv_transactions rct,
83 rcv_shipment_lines rsl,
84 po_headers poh
85 where rct.shipment_line_id = rsl.shipment_line_id
86 and rct.transaction_type = 'RECEIVE'
87 and rct.transaction_date between
88 to_date(p_cum_period_start_date) and
89 to_date(p_cum_period_end_date)
90 and rsl.to_organization_id = p_org_id
91 and rsl.item_id = p_item_id
92 and rsl.po_header_id = poh.po_header_id
93 and poh.vendor_id = p_vendor_id
94 and poh.vendor_site_id = p_vendor_site_id
95 and poh.supply_agreement_flag = 'Y'
96 and rct.transaction_date in
97 (select max(rct2.transaction_date)
98 from rcv_transactions rct2,
99 rcv_shipment_lines rsl2,
100 po_headers poh2
101 where rct2.shipment_line_id = rsl2.shipment_line_id
102 and rct2.transaction_type = 'RECEIVE'
103 and rct2.transaction_date between
104 to_date(p_cum_period_start_date) and
105 to_date(p_cum_period_end_date)
106 and rsl2.to_organization_id = p_org_id
107 and rsl2.item_id = p_item_id
108 and rsl2.po_header_id = poh.po_header_id
109 and poh2.vendor_id = p_vendor_id
110 and poh2.vendor_site_id = p_vendor_site_id
111 and poh2.supply_agreement_flag = 'Y');
112
113
114 select transaction_date
115 into x_last_receipt_date
116 from rcv_transactions rct
117 where rct.transaction_id = x_last_receipt_id;
118
119 return(x_last_receipt_date) ;
120
121 exception when others then
122 return('') ;
123
124 end get_last_receipt_date;
125
126 FUNCTION get_last_receipt_quantity(p_org_id IN NUMBER,
127 p_item_id in NUMBER,
128 p_vendor_id in NUMBER,
129 p_vendor_site_id in NUMBER,
130 p_cum_period_start_date in DATE,
131 p_cum_period_end_date in DATE,
132 p_purchasing_uom VARCHAR2)
133 RETURN number is
134
135 x_last_receipt_id number := null ;
136 x_last_receipt_quantity number := null;
137 x_primary_quantity number := null;
138 x_primary_unit_of_measure varchar2(25) := null;
139 x_primary_uom_code varchar2(3);
140 x_purchasing_uom_code varchar2(3);
141 x_conversion number;
142
143 begin
144
145 select max(rct.transaction_id)
146 into x_last_receipt_id
147 from rcv_transactions rct,
148 rcv_shipment_lines rsl,
149 po_headers poh
150 where rct.shipment_line_id = rsl.shipment_line_id
151 and rct.transaction_type = 'RECEIVE'
152 and rct.transaction_date between
153 to_date(p_cum_period_start_date) and
154 to_date(p_cum_period_end_date)
155 and rsl.to_organization_id = p_org_id
156 and rsl.item_id = p_item_id
157 and rsl.po_header_id = poh.po_header_id
158 and poh.vendor_id = p_vendor_id
159 and poh.vendor_site_id = p_vendor_site_id
160 and poh.supply_agreement_flag = 'Y'
161 and rct.transaction_date in
162 (select max(rct2.transaction_date)
163 from rcv_transactions rct2,
164 rcv_shipment_lines rsl2,
165 po_headers poh2
166 where rct2.shipment_line_id = rsl2.shipment_line_id
167 and rct2.transaction_type = 'RECEIVE'
168 and rct2.transaction_date between
169 to_date(p_cum_period_start_date) and
170 to_date(p_cum_period_end_date)
171 and rsl2.to_organization_id = p_org_id
172 and rsl2.item_id = p_item_id
173 and rsl2.po_header_id = poh2.po_header_id
174 and poh2.vendor_id = p_vendor_id
175 and poh2.vendor_site_id = p_vendor_site_id
176 and poh2.supply_agreement_flag = 'Y');
177
178
179 select primary_quantity,
180 primary_unit_of_measure
181 into x_primary_quantity,
182 x_primary_unit_of_measure
183 from rcv_transactions rct
184 where rct.transaction_id = x_last_receipt_id;
185
186 BEGIN
187
188 SELECT uom_code
189 INTO x_primary_uom_code
190 FROM mtl_units_of_measure
191 WHERE unit_of_measure = x_primary_unit_of_measure;
192
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN null;
195 WHEN OTHERS THEN raise;
196 END;
197
198 -- Get the uom code (3 characters) for the purch unit of measure
199
200 BEGIN
201
202 SELECT uom_code
203 INTO x_purchasing_uom_code
204 FROM mtl_units_of_measure
205 WHERE unit_of_measure = p_purchasing_uom;
206
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN null;
209 WHEN OTHERS THEN raise;
210 END;
211
212 inv_convert.inv_um_conversion(x_primary_uom_code,
213 x_purchasing_uom_code,
214 p_item_id, x_conversion);
215
216
217 x_last_receipt_quantity := x_conversion * x_primary_quantity;
218
219 return(x_last_receipt_quantity) ;
220
221 exception when others then
222 return('') ;
223
224 end get_last_receipt_quantity;
225
226 FUNCTION get_cum_received_purch (X_vendor_id IN NUMBER,
227 X_vendor_site_id IN NUMBER,
228 X_item_id IN NUMBER,
229 X_organization_id IN NUMBER,
230 X_rtv_transactions_included IN VARCHAR2,
231 X_cum_period_start IN DATE,
232 X_cum_period_end IN DATE,
233 X_purchasing_unit_of_measure IN VARCHAR2)
234 return NUMBER is
235
236
237 X_transaction_uom_code varchar2(3);
238 X_purchasing_uom_code varchar2(3);
239 X_primary_uom_code varchar2(3);
240 X_uom_rate number;
241 X_primary_unit_of_measure varchar2(25);
242 X_unit_of_measure varchar2(25);
243 X_conversion_rate number;
244 X_quantity_received number;
245 X_transaction_id number;
246 X_rtv_primary_quantity number;
247 X_rtv_transaction_id number;
248 X_corrtv_primary_quantity number;
249 X_total_qty_received_primary number;
250 X_progress varchar2(3) := '000';
251 X_adjustment_quantity number;
252 X_tot_received_purch number;
253 X_tot_received_primary number;
254 X_qty_received_purchasing varchar2(25);
255 X_qty_received_primary varchar2(25);
256
257 -- Define the cursor that gets the receipt transaction plus all
258 -- of the corrections against the receipt
259 -- Note: We must use the item_id on the po_line instead of
260 -- on the receipt to account for substitute receipts.
261 CURSOR C IS
262 SELECT rsl.quantity_received,
263 rsl.unit_of_measure,
264 rsl.primary_unit_of_measure,
265 rct.transaction_id
266 FROM rcv_shipment_lines rsl,
267 po_headers poh,
268 po_lines pol,
269 rcv_transactions rct
270 WHERE rct.shipment_line_id = rsl.shipment_line_id
271 AND rct.transaction_type = 'RECEIVE'
272 AND rsl.po_header_id = poh.po_header_id
273 AND rsl.po_line_id = pol.po_line_id
274 AND poh.vendor_id = X_vendor_id
275 AND poh.vendor_site_id = X_vendor_site_id
276 AND rsl.to_organization_id = X_organization_id
277 AND poh.supply_agreement_flag = 'Y'
278 AND pol.item_id = X_item_id
279 AND rct.transaction_date between X_cum_period_start
280 and X_cum_period_end;
281
282
283 -- Define the cursor to the the rtv transactions against
284 -- the receipt transaction
285 CURSOR C2 IS
286 SELECT rct.primary_quantity,
287 rct.transaction_id
288 FROM rcv_transactions rct
289 WHERE rct.transaction_type = 'RETURN TO VENDOR'
290 AND rct.parent_transaction_id = X_transaction_id;
291
292 CURSOR C3 IS
293 SELECT rsl.quantity_received,
294 rsl.unit_of_measure,
295 rsl.primary_unit_of_measure
296 FROM rcv_shipment_lines rsl,
297 po_headers poh,
298 po_lines pol
299 WHERE pol.item_id = X_item_id
300 AND rsl.po_line_id = pol.po_line_id
301 AND pol.po_header_id = poh.po_header_id
302 AND poh.vendor_id = X_vendor_id
303 AND poh.vendor_site_id = X_vendor_site_id
304 AND rsl.to_organization_id = X_organization_id
305 AND poh.supply_agreement_flag = 'Y'
306 AND exists
307 (select 1
308 from rcv_transactions rct
309 where rct.transaction_date between x_cum_period_start
310 and x_cum_period_end
311 and rct.shipment_line_id = rsl.shipment_line_id
312 and rct.transaction_type = 'RECEIVE');
313
314
315 BEGIN
316
317 -- RTV transactions are included in the CUM period that the
318 -- receipt transactions are done in. This means if the CUM period
319 -- is closed that we performed the receipt transaction in,
320 -- the RTV will be included in the closed CUM period.
321
322
323 IF (x_rtv_transactions_included = 'N') THEN
324
325
326 -- Open the cursor that gets all of the shipment lines that
327 -- match the vendor, vendor site, org, item, in the cum period
328 OPEN C3;
329
330 -- For each of these shipment lines, get each of the rtvs
331 -- against the shipment line.
332 LOOP
333
334 X_progress := '010';
335
336 FETCH C3 INTO X_quantity_received,
337 X_unit_of_measure,
338 X_primary_unit_of_measure;
339
340 EXIT WHEN C3%notfound;
341
342
343 -- We need to convert the shipment line uom to the primary uom
344 -- and the purchasing uom.
345
346
347 X_progress := '020';
348
349 SELECT uom_code
350 INTO X_transaction_uom_code
351 FROM mtl_units_of_measure
352 WHERE unit_of_measure = X_unit_of_measure;
353
354 X_progress := '030';
355
356
357 SELECT uom_code
358 INTO X_purchasing_uom_code
359 FROM mtl_units_of_measure
360 WHERE unit_of_measure = X_purchasing_unit_of_measure;
361
362
363 X_progress := '040';
364
365 SELECT uom_code
366 INTO X_primary_uom_code
367 FROM mtl_units_of_measure
368 WHERE unit_of_measure = X_primary_unit_of_measure;
369
370 X_progress := '050';
371
375
372 inv_convert.inv_um_conversion(X_transaction_uom_code,
373 X_primary_uom_code,
374 X_item_id, X_conversion_rate);
376 X_qty_received_primary := X_conversion_rate * X_quantity_received;
377
378 X_progress := '060';
379
380 inv_convert.inv_um_conversion(X_primary_uom_code,
381 X_purchasing_uom_code,
382 X_item_id, X_uom_rate);
383
384 X_qty_received_purchasing := X_uom_rate * X_qty_received_primary;
385
386
387 X_tot_received_primary := nvl(X_tot_received_primary,0) +
388 nvl(X_qty_received_primary,0);
389
390 X_tot_received_purch := nvl(X_tot_received_purch,0) +
391 nvl(X_qty_received_purchasing,0);
392
393 END LOOP;
394
395 CLOSE C3;
396
397 select sum(adjustment_quantity)
398 into x_adjustment_quantity
399 from chv_cum_adjustments cha,
400 chv_cum_periods ccp
401 where cha.organization_id = X_organization_id
402 and cha.vendor_id = X_vendor_id
403 and cha.vendor_site_id = X_vendor_site_id
404 and cha.item_id = X_item_id
405 and cha.cum_period_id = ccp.cum_period_id
406 and ccp.cum_period_start_date = X_cum_period_start
407 and ccp.cum_period_end_date = X_cum_period_end
408 and ccp.organization_id = cha.organization_id;
409
410 X_tot_received_purch := nvl(X_tot_received_purch,0) +
411 nvl(X_adjustment_quantity,0);
412
413 -- This will happen if there are no rcv txn's, but an adjustment
414 IF (X_primary_uom_code is null) THEN
415
416 SELECT primary_uom_code
417 INTO X_primary_uom_code
418 FROM mtl_system_items
419 WHERE inventory_item_id = X_item_id
420 AND organization_id = X_organization_id;
421
422 SELECT uom_code
423 INTO X_purchasing_uom_code
424 FROM mtl_units_of_measure
425 WHERE unit_of_measure = X_purchasing_unit_of_measure;
426
427 END IF;
428
429 inv_convert.inv_um_conversion(X_purchasing_uom_code,
430 X_primary_uom_code,
431 X_item_id, X_uom_rate);
432
433 X_tot_received_primary := X_tot_received_purch * X_uom_rate;
434
435 X_qty_received_purchasing := X_tot_received_purch;
436 X_qty_received_primary := X_tot_received_primary;
437
438 return(X_qty_received_purchasing);
439
440 ELSE
441
442 X_progress := '070';
443
444 -- Open the cursor that gets all of the shipment lines that
445 -- match the vendor, vendor site, org, item, in the cum period
446 OPEN C;
447
448 -- For each of these shipment lines, get each of the rtvs
449 -- against the shipment line.
450 LOOP
451
452 X_progress := '080';
453
454 FETCH C INTO X_quantity_received,
455 X_unit_of_measure,
456 X_primary_unit_of_measure,
457 X_transaction_id;
458
459 EXIT WHEN C%notfound;
460
461 -- Get the uom code since we only have the unit of measure.
462 -- We need to the uom code to execute uom_convert.
463 -- We CANNOT just get the primary quantity from rcv_transactions
464 -- since it will not have the corrections to that quantity.
465 -- The rcv_shipment line includes the quantity received +
466 -- all corrects to that quantity.
467 X_progress := '090';
468 SELECT uom_code
469 INTO X_transaction_uom_code
470 FROM mtl_units_of_measure
471 WHERE unit_of_measure = X_unit_of_measure;
472
473 X_progress := '100';
474 SELECT uom_code
475 INTO X_primary_uom_code
476 FROM mtl_units_of_measure
477 WHERE unit_of_measure = X_primary_unit_of_measure;
478
479 X_progress := '110';
480 SELECT uom_code
481 INTO X_purchasing_uom_code
482 FROM mtl_units_of_measure
483 WHERE unit_of_measure = X_purchasing_unit_of_measure;
484
485
486 X_progress := '120';
487 inv_convert.inv_um_conversion(X_transaction_uom_code,
488 X_primary_uom_code,
489 X_item_id, X_conversion_rate);
490
491 -- Calculate the qty received in the primary unit of measure.
492 X_qty_received_primary := X_conversion_rate * X_quantity_received;
493
494 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0) +
495 nvl(X_qty_received_primary,0);
496
497 X_progress := '130';
498
499 -- Open the cursor to get the rtv's against the shipment line/
500 -- transaction we are working with.
501 OPEN C2;
502
503 -- For each rtv transaction get the corrections against it.
504 LOOP
505
506 X_progress := '140';
507
508 FETCH C2 INTO X_rtv_primary_quantity,
509 X_rtv_transaction_id;
510
511 EXIT WHEN C2%notfound;
512
513 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0)
514 - nvl(X_rtv_primary_quantity,0);
515
516 BEGIN
517
518 X_progress := '150';
519
520 SELECT sum(rct.primary_quantity)
521 INTO X_corrtv_primary_quantity
522 FROM rcv_transactions rct
523 WHERE rct.transaction_type = 'CORRECT'
524 AND rct.parent_transaction_id = X_rtv_transaction_id;
525
526 EXCEPTION
527 WHEN NO_DATA_FOUND then null;
528 WHEN OTHERS then raise;
529
530 END;
531
535 END LOOP;
532 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0)
533 + nvl(X_corrtv_primary_quantity,0);
534
536
537 CLOSE C2;
538
539 END LOOP;
540
541 CLOSE C;
542
543
544 X_qty_received_primary := x_total_qty_received_primary;
545
546 X_progress := '160';
547 inv_convert.inv_um_conversion(X_primary_uom_code,
548 X_purchasing_uom_code,
549 X_item_id, X_conversion_rate);
550
551 X_qty_received_purchasing :=
552 round((x_qty_received_primary * X_conversion_rate), 5);
553
554
555 select sum(adjustment_quantity)
556 into x_adjustment_quantity
557 from chv_cum_adjustments cha,
558 chv_cum_periods ccp
559 where cha.organization_id = X_organization_id
560 and cha.vendor_id = X_vendor_id
561 and cha.vendor_site_id = X_vendor_site_id
562 and cha.item_id = X_item_id
563 and cha.cum_period_id = ccp.cum_period_id
564 and ccp.cum_period_start_date = X_cum_period_start
565 and ccp.cum_period_end_date = X_cum_period_end
566 and ccp.organization_id = cha.organization_id;
567
568 X_qty_received_purchasing := nvl(X_qty_received_purchasing,0) +
569 nvl(X_adjustment_quantity,0);
570
571 return(X_qty_received_purchasing);
572
573 END IF;
574
575 EXCEPTION
576 WHEN OTHERS THEN
577 return('');
578
579 END get_cum_received_purch;
580
581 function get_purchasing_uom_qty(x_primary_quantity in number,
582 x_primary_unit_of_measure in varchar2,
583 x_vendor_id in number,
584 x_vendor_site_id in number,
585 x_organization_id in number,
586 x_item_id in number)
587 return number is
588
589 x_primary_uom_code varchar2(3);
590 x_purchasing_uom_code varchar2(3);
591 x_purchasing_unit_of_measure varchar2(25);
592 x_conversion_rate number;
593 x_purchasing_qty number;
594
595 begin
596
597 SELECT paa.purchasing_unit_of_measure
598 INTO x_purchasing_unit_of_measure
599 FROM po_asl_attributes_val_v paa
600 WHERE paa.vendor_id = x_vendor_id
601 AND paa.vendor_site_id = x_vendor_site_id
602 AND paa.item_id = x_item_id
603 AND paa.using_organization_id =
604 (SELECT max(paa2.using_organization_id)
605 FROM po_asl_attributes_val_v paa2
606 WHERE decode(paa2.using_organization_id, -1,
607 x_organization_id,
608 paa2.using_organization_id) =
609 x_organization_id
610 AND paa2.vendor_id = x_vendor_id
611 AND paa2.vendor_site_id = x_vendor_site_id
612 AND paa2.item_id = x_item_id) ;
613
614 SELECT uom_code
615 INTO x_primary_uom_code
616 FROM mtl_units_of_measure
617 WHERE unit_of_measure = X_primary_unit_of_measure;
618
619 SELECT uom_code
620 INTO x_purchasing_uom_code
621 FROM mtl_units_of_measure
622 WHERE unit_of_measure = X_purchasing_unit_of_measure;
623
624 inv_convert.inv_um_conversion(x_primary_uom_code,
625 x_purchasing_uom_code,
626 x_item_id, x_conversion_rate);
627
628 x_purchasing_qty := x_primary_quantity * x_conversion_rate;
629
630 return(x_purchasing_qty) ;
631
632 exception when others then
633
634 return('') ;
635
636 end ;
637
638
639 END CHV_INQ_SV2;
[Home] [Help]