1 PACKAGE BODY CHV_CUM_PERIODS_S1 as
2 /* $Header: CHVPRCQB.pls 115.6 2003/08/13 08:53:45 nprattip ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: test_get_cum_qty_received
7
8 ===========================================================================*/
9 PROCEDURE test_get_cum_qty_received IS
10
11 X_qty_received_primary NUMBER;
12 X_qty_received_purch NUMBER;
13
14 BEGIN
15
16 --dbms_output.put_line('before call');
17
18 chv_cum_periods_s1.get_cum_qty_received(83,
19 85,
20 45050,
21 205,
22 'N',
23 'sysdate',
24 'sysdate + 30',
25 'Each',
26 X_qty_received_primary,
27 X_qty_received_purch);
28
29 --dbms_output.put_line('after call');
30 --dbms_output.put_line('qty rcv primary'||X_qty_received_primary);
31 --dbms_output.put_line('qty rcv purch'||X_qty_received_purch);
32
33 END test_get_cum_qty_received;
34
35
36 /****************************************************************************
37 **
38 **PROCEDURE NAME: get_cum_qty_received
39 **
40 ****************************************************************************/
41 PROCEDURE get_cum_qty_received (X_vendor_id IN NUMBER,
42 X_vendor_site_id IN NUMBER,
43 X_item_id IN NUMBER,
44 X_organization_id IN NUMBER,
45 X_rtv_transactions_included IN VARCHAR2,
46 X_cum_period_start IN DATE,
47 X_cum_period_end IN DATE,
48 X_purchasing_unit_of_measure IN VARCHAR2,
49 X_qty_received_primary IN OUT NOCOPY NUMBER,
50 X_qty_received_purchasing IN OUT NOCOPY NUMBER) IS
51
52 X_transaction_uom_code varchar2(3);
53 X_purchasing_uom_code varchar2(3);
54 X_primary_uom_code varchar2(3);
55 X_uom_rate number;
56 X_primary_unit_of_measure varchar2(25);
57 X_unit_of_measure varchar2(25);
58 X_conversion_rate number;
59 X_quantity_received number;
60 X_transaction_id number;
61 X_rtv_primary_quantity number;
62 X_rtv_transaction_id number;
63 X_corrtv_primary_quantity number;
64 X_total_qty_received_primary number;
65 X_progress varchar2(3) := '000';
66 X_adjustment_quantity number;
67 X_tot_received_purch number;
68 X_tot_received_primary number;
69
70 -- Define the cursor that gets the receipt transaction plus all
71 -- of the corrections against the receipt
72 -- Note: We must use the item_id on the po_line instead of
73 -- on the receipt to account for substitute receipts.
74 /* Bug 2251090 fixed. In the where clause of the below sql, added
75 the nvl() statement for x_cum_period_end to take care of null
76 condition.
77 */
78 CURSOR C IS
79 SELECT rsl.quantity_received,
80 rsl.unit_of_measure,
81 rsl.primary_unit_of_measure,
82 rct.transaction_id
83 FROM rcv_shipment_lines rsl,
84 po_headers poh,
85 po_lines pol,
86 rcv_transactions rct
87 WHERE rct.shipment_line_id = rsl.shipment_line_id
88 AND rct.transaction_type = 'RECEIVE'
89 AND rsl.po_header_id = poh.po_header_id
90 AND rsl.po_line_id = pol.po_line_id
91 AND poh.vendor_id = X_vendor_id
92 AND poh.vendor_site_id = X_vendor_site_id
93 AND rsl.to_organization_id = X_organization_id
94 AND poh.supply_agreement_flag = 'Y'
95 AND pol.item_id = X_item_id
96 AND rct.transaction_date between X_cum_period_start
97 and nvl(X_cum_period_end,rct.transaction_date+1)
98 /* Bug#3067808 Added the following retrictive coindition to the SQL so that
99 ** the correct value for transaction_id is retrived from receiving tables
100 ** only for which the ASL entries exists.
101 */
102 AND EXISTS (select '1'
103 from po_asl_attributes_val_v paa,
104 po_asl_documents pad
105 WHERE paa.vendor_id = x_vendor_id
106 AND paa.vendor_site_id = x_vendor_site_id
107 AND paa.item_id = x_item_id
108 AND paa.using_organization_id =
109 (SELECT MAX(paa2.using_organization_id)
110 FROM po_asl_attributes_val_v paa2
111 WHERE decode(paa2.using_organization_id, -1,
112 x_organization_id,
113 paa2.using_organization_id) =
114 x_organization_id
115 AND paa2.vendor_id = x_vendor_id
116 AND paa2.vendor_site_id = x_vendor_site_id
117 AND paa2.item_id = x_item_id)
118 AND paa.asl_id = pad.asl_id
119 AND pad.document_header_id = poh.po_header_id);
120 /* Bug#3067808 END */
121
122
123 -- Define the cursor to the the rtv transactions against
124 -- the receipt transaction
125 CURSOR C2 IS
126 SELECT rct.primary_quantity,
127 rct.transaction_id
128 FROM rcv_transactions rct
129 WHERE rct.transaction_type = 'RETURN TO VENDOR'
130 AND rct.parent_transaction_id = X_transaction_id;
131
132 /* Bug 2251090 fixed. In the where clause of the below sql, added
133 the nvl() statement for cum_period_end_date to take care of null
134 condition.
135 */
136 CURSOR C3 IS
137 SELECT rsl.quantity_received,
138 rsl.unit_of_measure,
139 rsl.primary_unit_of_measure
140 FROM rcv_shipment_lines rsl,
141 po_headers poh,
142 po_lines pol
143 WHERE pol.item_id = X_item_id
144 AND rsl.po_line_id = pol.po_line_id
145 AND pol.po_header_id = poh.po_header_id
146 AND poh.vendor_id = X_vendor_id
147 AND poh.vendor_site_id = X_vendor_site_id
148 AND rsl.to_organization_id = X_organization_id
149 AND poh.supply_agreement_flag = 'Y'
150 AND exists
151 (select 1
152 from rcv_transactions rct
153 where rct.transaction_date between x_cum_period_start
154 and nvl(x_cum_period_end,
155 rct.transaction_date+1)
156 and rct.shipment_line_id = rsl.shipment_line_id
157 and rct.transaction_type = 'RECEIVE')
158
159 /* Bug#3067808 Added the following retrictive coindition to the SQL so that
160 ** the correct value for transaction_id is retrived from receiving tables
161 ** only for which the ASL entries exists.
162 */
163 AND EXISTS (select '1'
164 from po_asl_attributes_val_v paa,
165 po_asl_documents pad
166 WHERE paa.vendor_id = x_vendor_id
167 AND paa.vendor_site_id = x_vendor_site_id
168 AND paa.item_id = x_item_id
169 AND paa.using_organization_id =
170 (SELECT MAX(paa2.using_organization_id)
171 FROM po_asl_attributes_val_v paa2
172 WHERE decode(paa2.using_organization_id, -1,
173 x_organization_id,
174 paa2.using_organization_id) =
175 x_organization_id
176 AND paa2.vendor_id = x_vendor_id
177 AND paa2.vendor_site_id = x_vendor_site_id
178 AND paa2.item_id = x_item_id)
179 AND paa.asl_id = pad.asl_id
180 AND pad.document_header_id = poh.po_header_id);
181 /* Bug#3067808 END */
182
183 BEGIN
184
185 -- RTV transactions are included in the CUM period that the
186 -- receipt transactions are done in. This means if the CUM period
187 -- is closed that we performed the receipt transaction in,
188 -- the RTV will be included in the closed CUM period.
189
190
191 IF (x_rtv_transactions_included = 'Y') THEN
192
193 --dbms_output.put_line('Get CUM Qty: Rtv transactions included');
194
195 -- Open the cursor that gets all of the shipment lines that
196 -- match the vendor, vendor site, org, item, in the cum period
197 OPEN C3;
198
199 -- For each of these shipment lines, get each of the rtvs
200 -- against the shipment line.
201 LOOP
202
203 --dbms_output.put_line('Get CUM Qty: before fetch');
204 --dbms_output.put_Line('Get CUM Qty: Item'||X_item_id);
205 --dbms_output.put_line('Get CUM Qty: Vendor'||X_vendor_id);
206 --dbms_output.put_line('Get CUM Qty: Site'||X_vendor_site_id);
207 --dbms_output.put_line('Get CUM Qty: Org'||X_organization_id);
208 --dbms_output.put_line('Get CUM Qty: Start'||X_cum_period_start);
209 --dbms_output.put_line('Get CUM Qty: End'||X_cum_period_end);
210
211 X_progress := '010';
212
213 FETCH C3 INTO X_quantity_received,
214 X_unit_of_measure,
215 X_primary_unit_of_measure;
216
217 EXIT WHEN C3%notfound;
218
219
220 --dbms_output.put_line('Get CUM Qty: X_qty_rcv'||X_quantity_received);
221 --dbms_output.put_line('Get CUM Qty: X_prim_unit_of_meas'||X_primary_unit_of_measure);
222
223 -- We need to convert the shipment line uom to the primary uom
224 -- and the purchasing uom.
225
226 --dbms_output.put_line('Get CUM Qty: X_qty_rcv_prim'||X_qty_received_primary);
227
228 X_progress := '020';
229
230 SELECT uom_code
231 INTO X_transaction_uom_code
232 FROM mtl_units_of_measure
233 WHERE unit_of_measure = X_unit_of_measure;
234
235 X_progress := '030';
236
237 --dbms_output.put_line('purchasing unit of meas'||X_purchasing_unit_of_measure);
238 SELECT uom_code
239 INTO X_purchasing_uom_code
240 FROM mtl_units_of_measure
241 WHERE unit_of_measure = X_purchasing_unit_of_measure;
242
243 --dbms_output.put_line('puom code'||X_purchasing_uom_code);
244
245 X_progress := '040';
246
247 SELECT uom_code
248 INTO X_primary_uom_code
249 FROM mtl_units_of_measure
250 WHERE unit_of_measure = X_primary_unit_of_measure;
251
252 X_progress := '050';
253
254 inv_convert.inv_um_conversion(X_transaction_uom_code,
255 X_primary_uom_code,
256 X_item_id, X_conversion_rate);
257
258 X_qty_received_primary := X_conversion_rate * X_quantity_received;
259
260 --dbms_output.put_line('Qty rcv primary'||to_char(X_qty_received_primary));
261 --dbms_output.put_line('Get CUM Qty: X_conversion_rate'||X_conversion_rate);
262
263 X_progress := '060';
264
265 --dbms_output.put_line('Get CUM Qty: purch_uom'||X_purchasing_uom_code);
266 --dbms_output.put_line('Get CUM Qty: Primary_uom'||X_primary_uom_code);
267
268 inv_convert.inv_um_conversion(X_primary_uom_code,
269 X_purchasing_uom_code,
270 X_item_id, X_uom_rate);
271
272 --dbms_output.put_line('Get CUM Qty: X_uom_rate'||X_uom_rate);
273
274 X_qty_received_purchasing := X_uom_rate * X_qty_received_primary;
275
276 --dbms_output.put_line('X_qty_rcv_purch'||to_char(X_qty_received_purchasing));
277
278 X_tot_received_primary := nvl(X_tot_received_primary,0) +
279 nvl(X_qty_received_primary,0);
280
281 X_tot_received_purch := nvl(X_tot_received_purch,0) +
282 nvl(X_qty_received_purchasing,0);
283
284 END LOOP;
285
286 CLOSE C3;
287
288 /* Bug#2559847 Changed the Where clause of the below SQL from
289 ** ccp.cum_period_end_date = x_cum_period_end to
290 ** ccp.cum_period_end_date >= x_cum_period_end. This is done to find a cum
291 ** period record.The variable x_cum_period_end that gets passed to this
292 ** procedure is set to the horizon_end_date if the horizon_end_date is less
293 ** than cum period end date. In that case,by equating the x_cum_period_end,
294 ** will not find the record. Hence It has to be >= x_cum_period_end
295 */
296
297 select sum(adjustment_quantity)
298 into x_adjustment_quantity
299 from chv_cum_adjustments cha,
300 chv_cum_periods ccp
301 where cha.organization_id = X_organization_id
302 and cha.vendor_id = X_vendor_id
303 and cha.vendor_site_id = X_vendor_site_id
304 and cha.item_id = X_item_id
305 and cha.cum_period_id = ccp.cum_period_id
306 and ccp.cum_period_start_date = X_cum_period_start
307 and ccp.cum_period_end_date >= X_cum_period_end
308 and ccp.organization_id = cha.organization_id;
309
310 X_tot_received_purch := nvl(X_tot_received_purch,0) +
311 nvl(X_adjustment_quantity,0);
312
313
314 --dbms_output.put_line('purch+adjust received'||to_char(X_tot_received_purch));
315
316 -- This will happen if there are no rcv txn's, but an adjustment
317 IF (X_primary_uom_code is null) THEN
318
319 SELECT primary_uom_code
320 INTO X_primary_uom_code
321 FROM mtl_system_items
322 WHERE inventory_item_id = X_item_id
323 AND organization_id = X_organization_id;
324
325 --dbms_output.put_line('primary with no rcv txn'||X_primary_uom_code);
326
327
328 SELECT uom_code
329 INTO X_purchasing_uom_code
330 FROM mtl_units_of_measure
331 WHERE unit_of_measure = X_purchasing_unit_of_measure;
332
333 END IF;
334
335 --dbms_output.put_line('purchasing uom'||X_purchasing_uom_code);
336
340
337 inv_convert.inv_um_conversion(X_purchasing_uom_code,
338 X_primary_uom_code,
339 X_item_id, X_uom_rate);
341 --dbms_output.put_line('uom rate'||to_char(X_uom_rate));
342
343 X_tot_received_primary := X_tot_received_purch * X_uom_rate;
344
345 --dbms_output.put_line('prim+adjust received'||to_char(X_tot_received_primary));
346
347 X_qty_received_purchasing := X_tot_received_purch;
348 X_qty_received_primary := X_tot_received_primary;
349
350 ELSE
351
352 --dbms_output.put_line('Get CUM Qty: before open');
353
354 X_progress := '070';
355
356 -- Open the cursor that gets all of the shipment lines that
357 -- match the vendor, vendor site, org, item, in the cum period
358 OPEN C;
359
360 -- For each of these shipment lines, get each of the rtvs
361 -- against the shipment line.
362 LOOP
363
364 --dbms_output.put_line('Get CUM Qty: before fetch');
365 --dbms_output.put_Line('Get CUM Qty: Item'||X_item_id);
366 --dbms_output.put_line('Get CUM Qty: Vendor'||X_vendor_id);
367 --dbms_output.put_line('Get CUM Qty: Site'||X_vendor_site_id);
368 --dbms_output.put_line('Get CUM Qty: Org'||X_organization_id);
369 --dbms_output.put_line('Get CUM Qty: Start'||X_cum_period_start);
370 --dbms_output.put_line('Get CUM Qty: End'||X_cum_period_end);
371
372
373 X_progress := '080';
374
375 FETCH C INTO X_quantity_received,
376 X_unit_of_measure,
377 X_primary_unit_of_measure,
378 X_transaction_id;
379
380 EXIT WHEN C%notfound;
381
382
383 --dbms_output.put_line('Get CUM Qty: before unit of meas check'||X_unit_of_measure||X_primary_unit_of_measure);
384
385 --dbms_output.put_line('Get CUM Qty: TRANSACTION_ID'||X_transaction_id);
386
387 -- Get the uom code since we only have the unit of measure.
388 -- We need to the uom code to execute uom_convert.
389 -- We CANNOT just get the primary quantity from rcv_transactions
390 -- since it will not have the corrections to that quantity.
391 -- The rcv_shipment line includes the quantity received +
392 -- all corrects to that quantity.
393 X_progress := '090';
394 SELECT uom_code
395 INTO X_transaction_uom_code
396 FROM mtl_units_of_measure
397 WHERE unit_of_measure = X_unit_of_measure;
398
399 X_progress := '100';
400 SELECT uom_code
401 INTO X_primary_uom_code
402 FROM mtl_units_of_measure
403 WHERE unit_of_measure = X_primary_unit_of_measure;
404
405 X_progress := '110';
406 SELECT uom_code
407 INTO X_purchasing_uom_code
408 FROM mtl_units_of_measure
409 WHERE unit_of_measure = X_purchasing_unit_of_measure;
410
411 --dbms_output.put_line('Get CUM Qty: before uom convert call');
412
413 X_progress := '120';
414 inv_convert.inv_um_conversion(X_transaction_uom_code,
415 X_primary_uom_code,
416 X_item_id, X_conversion_rate);
417
418 -- Calculate the qty received in the primary unit of measure.
419 X_qty_received_primary := X_conversion_rate * X_quantity_received;
420
421 --dbms_output.put_line('Get CUM Qty: qty rcv'||X_qty_received_primary);
422
423 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0) +
424 nvl(X_qty_received_primary,0);
425
426 --dbms_output.put_line('Get CUM Qty: top total'||X_total_qty_received_primary);
427
428 --dbms_output.put_line('Get CUM Qty: before open of c2');
429
430 X_progress := '130';
431
432 -- Open the cursor to get the rtv's against the shipment line/
433 -- transaction we are working with.
434 OPEN C2;
435
436 -- For each rtv transaction get the corrections against it.
437 LOOP
438
439 --dbms_output.put_line('Get CUM Qty: before c2 fetch');
440
441 X_progress := '140';
442
443 FETCH C2 INTO X_rtv_primary_quantity,
444 X_rtv_transaction_id;
445
446 EXIT WHEN C2%notfound;
447
448 --dbms_output.put_line('Get CUM Qty: before sum');
449
450
451 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0)
452 + nvl(X_rtv_primary_quantity,0);
453
454 --dbms_output.put_line('Get CUM Qty: middle total'||X_total_qty_received_primary);
455
456 BEGIN
457
458 X_progress := '150';
459
460 SELECT sum(rct.primary_quantity)
461 INTO X_corrtv_primary_quantity
462 FROM rcv_transactions rct
463 WHERE rct.transaction_type = 'CORRECT'
464 AND rct.parent_transaction_id = X_rtv_transaction_id;
465
466 EXCEPTION
467 WHEN NO_DATA_FOUND then null;
468 WHEN OTHERS then raise;
469
470 END;
471
472 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0)
473 + nvl(X_corrtv_primary_quantity,0);
474
475 --dbms_output.put_line('Get CUM Qty: qtyrcvprim'||X_qty_received_primary);
479 --dbms_output.put_line('Get CUM Qty: inner total'||X_total_qty_received_primary);
476 --dbms_output.put_line('Get CUM Qty: qtyrtvprim'||X_rtv_primary_quantity);
477 --dbms_output.put_line('Get CUM Qty: qtyrtvcorprim'||X_corrtv_primary_quantity);
478
480
481 END LOOP;
482
483 CLOSE C2;
484
485 END LOOP;
486
487 CLOSE C;
488
489 --dbms_output.put_line('Get CUM Qty: qty rcv'||X_qty_received_primary);
490 --dbms_output.put_line('Get CUM Qty: total'||X_total_qty_received_primary);
491
492 X_qty_received_primary := x_total_qty_received_primary;
493
494 X_progress := '160';
495 inv_convert.inv_um_conversion(X_primary_uom_code,
496 X_purchasing_uom_code,
497 X_item_id, X_conversion_rate);
498
499 X_qty_received_purchasing :=
500 round((x_qty_received_primary * X_conversion_rate), 5);
501
502 /* Bug#2559847 Changed the Where clause of the below SQL from
503 ** ccp.cum_period_end_date = x_cum_period_end to
507 ** than cum period end date. In that case,by equating the x_cum_period_end,
504 ** ccp.cum_period_end_date >= x_cum_period_end. This is done to find a cum
505 ** period record.The variable x_cum_period_end that gets passed to this
506 ** procedure is set to the horizon_end_date if the horizon_end_date is less
508 ** will not find the record. Hence It has to be >= x_cum_period_end
509 */
510 select sum(adjustment_quantity)
511 into x_adjustment_quantity
512 from chv_cum_adjustments cha,
513 chv_cum_periods ccp
514 where cha.organization_id = X_organization_id
515 and cha.vendor_id = X_vendor_id
516 and cha.vendor_site_id = X_vendor_site_id
517 and cha.item_id = X_item_id
518 and cha.cum_period_id = ccp.cum_period_id
519 and ccp.cum_period_start_date = X_cum_period_start
520 and ccp.cum_period_end_date >= X_cum_period_end
521 and ccp.organization_id = cha.organization_id;
522
523 X_qty_received_purchasing := nvl(X_qty_received_purchasing,0) +
524 nvl(X_adjustment_quantity,0);
525
526
527 -- This will happen if there are no rcv txn's, but an adjustment
528 IF (X_primary_uom_code is null) THEN
529
530 SELECT primary_uom_code
531 INTO X_primary_uom_code
532 FROM mtl_system_items
533 WHERE inventory_item_id = X_item_id
534 AND organization_id = X_organization_id;
535
536 --dbms_output.put_line('primary with no rcv txn'||X_primary_uom_code);
537
538
539 SELECT uom_code
540 INTO X_purchasing_uom_code
541 FROM mtl_units_of_measure
542 WHERE unit_of_measure = X_purchasing_unit_of_measure;
543
544 END IF;
545
546 inv_convert.inv_um_conversion(X_purchasing_uom_code,
547 X_primary_uom_code,
548 X_item_id, X_uom_rate);
549
550 X_qty_received_primary := X_qty_received_purchasing * X_uom_rate;
551
552
553 END IF;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 po_message_s.sql_error('get_cum_qty_received', X_progress, sqlcode);
558 raise;
559
560 END get_cum_qty_received;
561
562 END CHV_CUM_PERIODS_S1;
[Home] [Help]