4 /*===========================================================================
1 PACKAGE BODY CHV_CUM_PERIODS_S1 as
2 /* $Header: CHVPRCQB.pls 120.1.12020000.2 2013/02/11 00:39:06 vegajula ship $ */
3
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';
69
66 X_adjustment_quantity number;
67 X_tot_received_purch number;
68 X_tot_received_primary number;
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 /*bug 8881513 While running the auto schedule program in supplier scheduling
137 product there was a performance issue.
138 Modified the sql in the cursor c_trxn_date as per the receiving
139 team advice.*/
140 CURSOR C3 IS
141 SELECT rsl.quantity_received,
142 rsl.unit_of_measure,
143 rsl.primary_unit_of_measure
144 FROM rcv_shipment_lines rsl,
145 po_headers poh,
146 po_lines pol
147 WHERE pol.item_id = X_item_id
148 AND rsl.po_line_id = pol.po_line_id
149 AND pol.po_header_id = poh.po_header_id
150 AND poh.vendor_id = X_vendor_id
151 AND poh.vendor_site_id = X_vendor_site_id
152 AND rsl.to_organization_id = X_organization_id
153 AND poh.supply_agreement_flag = 'Y'
154 AND exists
155 (select 1
156 from rcv_transactions rct
157 where rct.transaction_date between x_cum_period_start
158 and nvl(x_cum_period_end,
159 rct.transaction_date+1)
160 and rct.shipment_line_id = rsl.shipment_line_id
161 AND rct.shipment_header_id = rsl.shipment_header_id --bug 8881513
162 AND rct.organization_id = rsl.to_organization_id --bug 8881513
163 and rct.transaction_type = 'RECEIVE')
164
165 /* Bug#3067808 Added the following retrictive coindition to the SQL so that
166 ** the correct value for transaction_id is retrived from receiving tables
167 ** only for which the ASL entries exists.
168 */
169 AND EXISTS (select '1'
170 from po_asl_attributes_val_v paa,
171 po_asl_documents pad
172 WHERE paa.vendor_id = x_vendor_id
173 AND paa.vendor_site_id = x_vendor_site_id
174 AND paa.item_id = x_item_id
175 AND paa.using_organization_id =
176 (SELECT MAX(paa2.using_organization_id)
177 FROM po_asl_attributes_val_v paa2
178 WHERE decode(paa2.using_organization_id, -1,
179 x_organization_id,
180 paa2.using_organization_id) =
181 x_organization_id
182 AND paa2.vendor_id = x_vendor_id
183 AND paa2.vendor_site_id = x_vendor_site_id
184 AND paa2.item_id = x_item_id)
185 AND paa.asl_id = pad.asl_id
189 BEGIN
186 AND pad.document_header_id = poh.po_header_id);
187 /* Bug#3067808 END */
188
190
191 -- RTV transactions are included in the CUM period that the
192 -- receipt transactions are done in. This means if the CUM period
193 -- is closed that we performed the receipt transaction in,
194 -- the RTV will be included in the closed CUM period.
195
196
197 IF (x_rtv_transactions_included = 'Y') THEN
198
199 --dbms_output.put_line('Get CUM Qty: Rtv transactions included');
200
201 -- Open the cursor that gets all of the shipment lines that
202 -- match the vendor, vendor site, org, item, in the cum period
203 OPEN C3;
204
205 -- For each of these shipment lines, get each of the rtvs
206 -- against the shipment line.
207 LOOP
208
209 --dbms_output.put_line('Get CUM Qty: before fetch');
210 --dbms_output.put_Line('Get CUM Qty: Item'||X_item_id);
211 --dbms_output.put_line('Get CUM Qty: Vendor'||X_vendor_id);
212 --dbms_output.put_line('Get CUM Qty: Site'||X_vendor_site_id);
213 --dbms_output.put_line('Get CUM Qty: Org'||X_organization_id);
214 --dbms_output.put_line('Get CUM Qty: Start'||X_cum_period_start);
215 --dbms_output.put_line('Get CUM Qty: End'||X_cum_period_end);
216
217 X_progress := '010';
218
219 FETCH C3 INTO X_quantity_received,
220 X_unit_of_measure,
221 X_primary_unit_of_measure;
222
223 EXIT WHEN C3%notfound;
224
225
226 --dbms_output.put_line('Get CUM Qty: X_qty_rcv'||X_quantity_received);
227 --dbms_output.put_line('Get CUM Qty: X_prim_unit_of_meas'||X_primary_unit_of_measure);
228
229 -- We need to convert the shipment line uom to the primary uom
230 -- and the purchasing uom.
231
232 --dbms_output.put_line('Get CUM Qty: X_qty_rcv_prim'||X_qty_received_primary);
233
234 X_progress := '020';
235
236 SELECT uom_code
237 INTO X_transaction_uom_code
238 FROM mtl_units_of_measure
239 WHERE unit_of_measure = X_unit_of_measure;
240
241 X_progress := '030';
242
243 --dbms_output.put_line('purchasing unit of meas'||X_purchasing_unit_of_measure);
244 SELECT uom_code
245 INTO X_purchasing_uom_code
246 FROM mtl_units_of_measure
247 WHERE unit_of_measure = X_purchasing_unit_of_measure;
248
249 --dbms_output.put_line('puom code'||X_purchasing_uom_code);
250
251 X_progress := '040';
252
253 SELECT uom_code
254 INTO X_primary_uom_code
255 FROM mtl_units_of_measure
256 WHERE unit_of_measure = X_primary_unit_of_measure;
257
258 X_progress := '050';
259
260 inv_convert.inv_um_conversion(X_transaction_uom_code,
261 X_primary_uom_code,
262 X_item_id, X_conversion_rate);
263
264 X_qty_received_primary := X_conversion_rate * X_quantity_received;
265
266 --dbms_output.put_line('Qty rcv primary'||to_char(X_qty_received_primary));
267 --dbms_output.put_line('Get CUM Qty: X_conversion_rate'||X_conversion_rate);
268
269 X_progress := '060';
270
271 --dbms_output.put_line('Get CUM Qty: purch_uom'||X_purchasing_uom_code);
272 --dbms_output.put_line('Get CUM Qty: Primary_uom'||X_primary_uom_code);
273
274 inv_convert.inv_um_conversion(X_primary_uom_code,
275 X_purchasing_uom_code,
276 X_item_id, X_uom_rate);
277
278 --dbms_output.put_line('Get CUM Qty: X_uom_rate'||X_uom_rate);
279
280 X_qty_received_purchasing := X_uom_rate * X_qty_received_primary;
281
282 --dbms_output.put_line('X_qty_rcv_purch'||to_char(X_qty_received_purchasing));
283
284 X_tot_received_primary := nvl(X_tot_received_primary,0) +
285 nvl(X_qty_received_primary,0);
286
287 X_tot_received_purch := nvl(X_tot_received_purch,0) +
288 nvl(X_qty_received_purchasing,0);
289
290 END LOOP;
291
292 CLOSE C3;
293
294 /* Bug#2559847 Changed the Where clause of the below SQL from
295 ** ccp.cum_period_end_date = x_cum_period_end to
296 ** ccp.cum_period_end_date >= x_cum_period_end. This is done to find a cum
297 ** period record.The variable x_cum_period_end that gets passed to this
298 ** procedure is set to the horizon_end_date if the horizon_end_date is less
299 ** than cum period end date. In that case,by equating the x_cum_period_end,
300 ** will not find the record. Hence It has to be >= x_cum_period_end
301 */
302
303 select sum(adjustment_quantity)
304 into x_adjustment_quantity
305 from chv_cum_adjustments cha,
306 chv_cum_periods ccp
307 where cha.organization_id = X_organization_id
308 and cha.vendor_id = X_vendor_id
309 and cha.vendor_site_id = X_vendor_site_id
310 and cha.item_id = X_item_id
311 and cha.cum_period_id = ccp.cum_period_id
312 and ccp.cum_period_start_date = X_cum_period_start
313 and ccp.cum_period_end_date >= X_cum_period_end
314 and ccp.organization_id = cha.organization_id;
315
316 X_tot_received_purch := nvl(X_tot_received_purch,0) +
317 nvl(X_adjustment_quantity,0);
318
319
320 --dbms_output.put_line('purch+adjust received'||to_char(X_tot_received_purch));
321
322 -- This will happen if there are no rcv txn's, but an adjustment
323 IF (X_primary_uom_code is null) THEN
324
325 SELECT primary_uom_code
329 AND organization_id = X_organization_id;
326 INTO X_primary_uom_code
327 FROM mtl_system_items
328 WHERE inventory_item_id = X_item_id
330
331 --dbms_output.put_line('primary with no rcv txn'||X_primary_uom_code);
332
333
334 SELECT uom_code
335 INTO X_purchasing_uom_code
336 FROM mtl_units_of_measure
337 WHERE unit_of_measure = X_purchasing_unit_of_measure;
338
339 END IF;
340
341 --dbms_output.put_line('purchasing uom'||X_purchasing_uom_code);
342
343 inv_convert.inv_um_conversion(X_purchasing_uom_code,
344 X_primary_uom_code,
345 X_item_id, X_uom_rate);
346
347 --dbms_output.put_line('uom rate'||to_char(X_uom_rate));
348
349 X_tot_received_primary := X_tot_received_purch * X_uom_rate;
350
351 --dbms_output.put_line('prim+adjust received'||to_char(X_tot_received_primary));
352
353 X_qty_received_purchasing := X_tot_received_purch;
354 X_qty_received_primary := X_tot_received_primary;
355
356 ELSE
357
358 --dbms_output.put_line('Get CUM Qty: before open');
359
360 X_progress := '070';
361
362 -- Open the cursor that gets all of the shipment lines that
363 -- match the vendor, vendor site, org, item, in the cum period
364 OPEN C;
365
366 -- For each of these shipment lines, get each of the rtvs
367 -- against the shipment line.
368 LOOP
369
370 --dbms_output.put_line('Get CUM Qty: before fetch');
371 --dbms_output.put_Line('Get CUM Qty: Item'||X_item_id);
372 --dbms_output.put_line('Get CUM Qty: Vendor'||X_vendor_id);
373 --dbms_output.put_line('Get CUM Qty: Site'||X_vendor_site_id);
374 --dbms_output.put_line('Get CUM Qty: Org'||X_organization_id);
375 --dbms_output.put_line('Get CUM Qty: Start'||X_cum_period_start);
376 --dbms_output.put_line('Get CUM Qty: End'||X_cum_period_end);
377
378
379 X_progress := '080';
380
381 FETCH C INTO X_quantity_received,
382 X_unit_of_measure,
383 X_primary_unit_of_measure,
384 X_transaction_id;
385
386 EXIT WHEN C%notfound;
387
388
389 --dbms_output.put_line('Get CUM Qty: before unit of meas check'||X_unit_of_measure||X_primary_unit_of_measure);
390
391 --dbms_output.put_line('Get CUM Qty: TRANSACTION_ID'||X_transaction_id);
392
393 -- Get the uom code since we only have the unit of measure.
394 -- We need to the uom code to execute uom_convert.
395 -- We CANNOT just get the primary quantity from rcv_transactions
396 -- since it will not have the corrections to that quantity.
397 -- The rcv_shipment line includes the quantity received +
398 -- all corrects to that quantity.
399 X_progress := '090';
400 SELECT uom_code
401 INTO X_transaction_uom_code
402 FROM mtl_units_of_measure
403 WHERE unit_of_measure = X_unit_of_measure;
404
405 X_progress := '100';
406 SELECT uom_code
407 INTO X_primary_uom_code
408 FROM mtl_units_of_measure
409 WHERE unit_of_measure = X_primary_unit_of_measure;
410
411 X_progress := '110';
412 SELECT uom_code
413 INTO X_purchasing_uom_code
414 FROM mtl_units_of_measure
415 WHERE unit_of_measure = X_purchasing_unit_of_measure;
416
417 --dbms_output.put_line('Get CUM Qty: before uom convert call');
418
419 X_progress := '120';
420 inv_convert.inv_um_conversion(X_transaction_uom_code,
421 X_primary_uom_code,
422 X_item_id, X_conversion_rate);
423
424 -- Calculate the qty received in the primary unit of measure.
425 X_qty_received_primary := X_conversion_rate * X_quantity_received;
426
427 --dbms_output.put_line('Get CUM Qty: qty rcv'||X_qty_received_primary);
428
429 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0) +
430 nvl(X_qty_received_primary,0);
431
432 --dbms_output.put_line('Get CUM Qty: top total'||X_total_qty_received_primary);
433
434 --dbms_output.put_line('Get CUM Qty: before open of c2');
435
436 X_progress := '130';
437
438 -- Open the cursor to get the rtv's against the shipment line/
439 -- transaction we are working with.
440 OPEN C2;
441
442 -- For each rtv transaction get the corrections against it.
443 LOOP
444
445 --dbms_output.put_line('Get CUM Qty: before c2 fetch');
446
447 X_progress := '140';
448
449 FETCH C2 INTO X_rtv_primary_quantity,
450 X_rtv_transaction_id;
451
452 EXIT WHEN C2%notfound;
453
454 --dbms_output.put_line('Get CUM Qty: before sum');
455
456
457 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0)
458 + nvl(X_rtv_primary_quantity,0);
459
460 --dbms_output.put_line('Get CUM Qty: middle total'||X_total_qty_received_primary);
461
462 BEGIN
463
464 X_progress := '150';
465
466 SELECT sum(rct.primary_quantity)
467 INTO X_corrtv_primary_quantity
468 FROM rcv_transactions rct
469 WHERE rct.transaction_type = 'CORRECT'
470 AND rct.parent_transaction_id = X_rtv_transaction_id;
471
472 EXCEPTION
473 WHEN NO_DATA_FOUND then null;
474 WHEN OTHERS then raise;
475
476 END;
477
478 X_total_qty_received_primary := nvl(X_total_qty_received_primary,0)
482 --dbms_output.put_line('Get CUM Qty: qtyrtvprim'||X_rtv_primary_quantity);
479 + nvl(X_corrtv_primary_quantity,0);
480
481 --dbms_output.put_line('Get CUM Qty: qtyrcvprim'||X_qty_received_primary);
483 --dbms_output.put_line('Get CUM Qty: qtyrtvcorprim'||X_corrtv_primary_quantity);
484
485 --dbms_output.put_line('Get CUM Qty: inner total'||X_total_qty_received_primary);
486
487 END LOOP;
488
489 CLOSE C2;
490
491 END LOOP;
492
493 CLOSE C;
494
495 --dbms_output.put_line('Get CUM Qty: qty rcv'||X_qty_received_primary);
496 --dbms_output.put_line('Get CUM Qty: total'||X_total_qty_received_primary);
497
498 X_qty_received_primary := x_total_qty_received_primary;
499
500 X_progress := '160';
501 inv_convert.inv_um_conversion(X_primary_uom_code,
502 X_purchasing_uom_code,
503 X_item_id, X_conversion_rate);
504
505 X_qty_received_purchasing :=
506 round((x_qty_received_primary * X_conversion_rate), 5);
507
508 /* Bug#2559847 Changed the Where clause of the below SQL from
509 ** ccp.cum_period_end_date = x_cum_period_end to
510 ** ccp.cum_period_end_date >= x_cum_period_end. This is done to find a cum
511 ** period record.The variable x_cum_period_end that gets passed to this
512 ** procedure is set to the horizon_end_date if the horizon_end_date is less
513 ** than cum period end date. In that case,by equating the x_cum_period_end,
514 ** will not find the record. Hence It has to be >= x_cum_period_end
515 */
516 select sum(adjustment_quantity)
517 into x_adjustment_quantity
518 from chv_cum_adjustments cha,
519 chv_cum_periods ccp
520 where cha.organization_id = X_organization_id
521 and cha.vendor_id = X_vendor_id
522 and cha.vendor_site_id = X_vendor_site_id
523 and cha.item_id = X_item_id
524 and cha.cum_period_id = ccp.cum_period_id
525 and ccp.cum_period_start_date = X_cum_period_start
526 and ccp.cum_period_end_date >= X_cum_period_end
527 and ccp.organization_id = cha.organization_id;
528
529 X_qty_received_purchasing := nvl(X_qty_received_purchasing,0) +
530 nvl(X_adjustment_quantity,0);
531
532
533 -- This will happen if there are no rcv txn's, but an adjustment
534 IF (X_primary_uom_code is null) THEN
535
536 SELECT primary_uom_code
537 INTO X_primary_uom_code
538 FROM mtl_system_items
539 WHERE inventory_item_id = X_item_id
540 AND organization_id = X_organization_id;
541
542 --dbms_output.put_line('primary with no rcv txn'||X_primary_uom_code);
543
544
545 SELECT uom_code
546 INTO X_purchasing_uom_code
547 FROM mtl_units_of_measure
548 WHERE unit_of_measure = X_purchasing_unit_of_measure;
549
550 END IF;
551
552 inv_convert.inv_um_conversion(X_purchasing_uom_code,
553 X_primary_uom_code,
554 X_item_id, X_uom_rate);
555
556 X_qty_received_primary := X_qty_received_purchasing * X_uom_rate;
557
558
559 END IF;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 po_message_s.sql_error('get_cum_qty_received', X_progress, sqlcode);
564 raise;
565
566 END get_cum_qty_received;
567
568 END CHV_CUM_PERIODS_S1;
[Home] [Help]