1 PACKAGE BODY RCV_RECEIPTS_QUERY_SV AS
2 /* $Header: RCVRCPQB.pls 120.2.12010000.2 2008/10/09 18:55:38 vthevark ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: post_query()
7
8 ===========================================================================*/
9 /* Note: This package has been overloaded as a bug fix of 2730828 */
10
11 /* Bug# 1942953 - Added x_from_org_id as a new parameter in the POST_QUERY */
12
13 PROCEDURE POST_QUERY ( x_line_location_id IN NUMBER,
14 x_shipment_line_id IN NUMBER,
15 x_receipt_source_code IN VARCHAR2,
16 x_org_id IN NUMBER,
17 x_item_id IN NUMBER,
18 x_unit_of_measure_class IN VARCHAR2,
19 x_ship_to_location_id IN NUMBER,
20 x_vendor_id IN NUMBER,
21 x_customer_id IN NUMBER,
22 x_item_rev_control_flag_to IN VARCHAR2,
23 x_available_qty IN OUT NOCOPY NUMBER,
24 x_primary_qty IN OUT NOCOPY NUMBER,
25 x_tolerable_qty IN OUT NOCOPY NUMBER,
26 x_uom IN OUT NOCOPY VARCHAR2,
27 x_primary_uom IN OUT NOCOPY VARCHAR2,
28 x_valid_ship_to_location IN OUT NOCOPY BOOLEAN,
29 x_num_of_distributions IN OUT NOCOPY NUMBER,
30 x_po_distributions_id IN OUT NOCOPY NUMBER,
31 x_destination_type_code IN OUT NOCOPY VARCHAR2,
32 x_destination_type_dsp IN OUT NOCOPY VARCHAR2,
33 x_deliver_to_location_id IN OUT NOCOPY NUMBER,
34 x_deliver_to_location IN OUT NOCOPY VARCHAR2,
35 x_deliver_to_person_id IN OUT NOCOPY NUMBER,
36 x_deliver_to_person IN OUT NOCOPY VARCHAR2,
37 x_deliver_to_sub IN OUT NOCOPY VARCHAR2,
38 x_deliver_to_locator_id IN OUT NOCOPY NUMBER,
39 x_wip_entity_id IN OUT NOCOPY NUMBER,
40 x_wip_repetitive_schedule_id IN OUT NOCOPY NUMBER,
41 x_wip_line_id IN OUT NOCOPY NUMBER,
42 x_wip_operation_seq_num IN OUT NOCOPY NUMBER,
43 x_wip_resource_seq_num IN OUT NOCOPY NUMBER,
44 x_bom_resource_id IN OUT NOCOPY NUMBER,
45 x_to_organization_id IN OUT NOCOPY NUMBER,
46 x_job IN OUT NOCOPY VARCHAR2,
47 x_line_num IN OUT NOCOPY VARCHAR2,
48 x_sequence IN OUT NOCOPY NUMBER,
49 x_department IN OUT NOCOPY VARCHAR2,
50 x_enforce_ship_to_loc IN OUT NOCOPY VARCHAR2,
51 x_allow_substitutes IN OUT NOCOPY VARCHAR2,
52 x_routing_id IN OUT NOCOPY NUMBER,
53 x_qty_rcv_tolerance IN OUT NOCOPY NUMBER,
54 x_qty_rcv_exception IN OUT NOCOPY VARCHAR2,
55 x_days_early_receipt IN OUT NOCOPY NUMBER,
56 x_days_late_receipt IN OUT NOCOPY NUMBER,
57 x_rcv_days_exception IN OUT NOCOPY VARCHAR2,
58 x_item_revision IN OUT NOCOPY VARCHAR2,
59 x_locator_control IN OUT NOCOPY NUMBER,
60 x_inv_destinations IN OUT NOCOPY BOOLEAN,
61 x_rate IN OUT NOCOPY NUMBER,
62 x_rate_date IN OUT NOCOPY DATE,
63 x_asn_type IN VARCHAR2,
64 x_oe_order_header_id IN NUMBER,
65 x_oe_order_line_id IN NUMBER,
66 x_from_org_id IN NUMBER DEFAULT NULL,
67 -- <RCV ENH FPI START>
68 x_kanban_card_number OUT NOCOPY VARCHAR2,
69 x_project_number OUT NOCOPY VARCHAR2,
70 x_task_number OUT NOCOPY VARCHAR2,
71 x_charge_account OUT NOCOPY VARCHAR2
72 -- <RCV ENH FPI END>
73 ) IS
74
75 x_progress VARCHAR2(3) := '010';
76 p_rev_exists BOOLEAN := FALSE;
77 x_success BOOLEAN := FALSE;
78 /*
79 ** This is strickly a throwaway argument for some of these calls. They
80 ** should not be using the _kfv to return this value
81 */
82 x_deliver_to_locator VARCHAR2(1000) := NULL;
83 x_default_subinventory VARCHAR2(10);
84 X_default_locator_id NUMBER;
85 /*Bug 2675920 Added the following variables*/
86 X_cancel_qty NUMBER :=0; --- primary uom
87 X_cancel_qty_po_uom NUMBER :=0;
88 primary_uom VARCHAR2(26);
89
90 X_quantity_shipped NUMBER :=0; -- ASN Phase 2
91 X_quantity_returned NUMBER :=0; -- ASN Phase 2
92 X_available_qty_hold NUMBER :=0; -- ASN Phase 2
93 X_ship_qty_in_int NUMBER :=0; -- ASN Phase 2 bug 623925
94 X_uom_hold VARCHAR2(26); -- 661871 change length to 26 chars
95 x_req_distribution_id number ; --Bug 1205660
96 X_project_id NUMBER ; -- Bug 1662321
97 X_task_id NUMBER ; -- Bug 1662321
98
99 /* Bug# 1548597 */
100 X_secondary_available_qty NUMBER := 0;
101
102 /* Bug# 3672978 */
103 /* Bug# 3680886 : Removed the initialization to zero for X_ms_routing_id */
104 X_ms_routing_id NUMBER;
105
106 -- <RCV ENH FPI START>
107 l_code_combination_id PO_DISTRIBUTIONS.code_combination_id%TYPE;
108 -- <RCV ENH FPI END>
109 BEGIN
110
111 X_progress := '010';
112
113 -- <RCV ENH FPI>
114 -- No Need to initialize x_job
115 -- x_job := 'GVQ';
116
117 /* Go get the quanity to show as available and the total they
118 ** can receive. Make sure to check what type of transaction you're
119 ** attempting to fetch the quantity for.
120 */
121 IF nvl(x_asn_type,'STD') NOT IN ('ASN','ASBN','LCM') THEN -- ASN Phase 2 -- lcm changes
122 IF (x_receipt_source_code = 'VENDOR') THEN
123
124 /* Bug# 1548597 */
125 rcv_quantities_s.get_available_quantity ('RECEIVE',
126 x_line_location_id,
127 'VENDOR', NULL, NULL, NULL, x_available_qty,
128 x_tolerable_qty, x_uom, X_secondary_available_qty);
129
130 -- Added this for ASN Phase 2 to account for shipped_quantity
131
132 select nvl(quantity_shipped,0)
133 into X_quantity_shipped
134 from po_line_locations
135 where
136 line_location_id = x_line_location_id;
137
138 /*Bug 2675920 When an ASN is cancelled from Manageshipments form in Batch mode and
139 if the record with transaction type as 'CANCEL' is existing in rti with pending
140 status then we are restricting the user to query that ASN in Enter Receipts form
141 Modifications done in poxrcv.odf. But at the sametime we need to adjust the quantity
142 inorder to display correct transaction quantity for non ASN line when quried by PO number
143 in Enter receipts form. Hence we are reducing the quantity shipped by the
144 sum(cancelled ASN Quantity) in rti so that x_available_qty is determined correctly. */
145
146
147 SELECT nvl(sum(primary_quantity),0),
148 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
149 INTO x_cancel_qty,
150 primary_uom
151 FROM rcv_transactions_interface
152 WHERE transaction_status_code = 'PENDING'
153 AND transaction_type = 'CANCEL'
154 AND po_line_location_id = x_line_location_id;
155 if (x_cancel_qty = 0) then
156 x_cancel_qty_po_uom := 0;
157 else
158 if (x_uom <> primary_uom) then
159 po_uom_s.uom_convert(x_cancel_qty, primary_uom, x_item_id,
160 x_uom, x_cancel_qty_po_uom);
161 else
162 x_cancel_qty_po_uom := x_cancel_qty;
163 end if;
164 end if;
165 X_quantity_shipped := X_quantity_shipped - x_cancel_qty_po_uom;
166
167 IF X_quantity_shipped > 0 then
168
169 x_available_qty := x_available_qty - X_quantity_shipped;
170 x_tolerable_qty := x_tolerable_qty - X_quantity_shipped;
171
172 If x_available_qty < 0 THEN
173 x_available_qty := 0;
174 end if;
175
176 IF x_tolerable_qty < 0 THEN
177 x_tolerable_qty := 0;
178 end if;
179
180 END IF;
181
182 ELSIF (x_receipt_source_code = 'CUSTOMER') THEN
183
184 /*
185 For use if we need to account for pending transactions in the interface
186 table.
187 */
188 /* Bug# 1548597 */
189 rcv_quantities_s.get_available_rma_quantity('RECEIVE',
190 null,
191 'CUSTOMER',
192 null,null,null,
193 x_oe_order_header_id,
194 x_oe_order_line_id,
195 x_available_qty,
196 x_tolerable_qty,
197 x_uom,
198 X_secondary_available_qty);
199
200 ELSE -- INTERNAL
201 /* Bug# 1548597 */
202 rcv_quantities_s.get_available_quantity ('RECEIVE',
203 x_shipment_line_id,
204 'INVENTORY', NULL, NULL, NULL, x_available_qty,
205 x_tolerable_qty, x_uom, X_secondary_available_qty);
206
207 /* If it is an ASN/ASBN we cannot overreceive
208
209 x_tolerable_qty := x_available_qty; -- ASN Phase 2 */
210
211 END IF;
212
213
214 ELSE
215
216 /*
217 ** If ASN, ASBN then we need to return the available qty
218 ** from rcv_shipment_lines. Faking the call to make it look
219 ** like an internal transfer so the code changes are
220 ** minimal and making use of existing API
221 */
222 /* Bug# 1548597 */
223 rcv_quantities_s.get_available_quantity ('RECEIVE',
224 x_shipment_line_id,
225 'INVENTORY', NULL, NULL, NULL, x_available_qty,
226 x_tolerable_qty, x_uom, X_secondary_available_qty);
227
228 /*
229 ** Now get the tolerable quantity based on the available amount
230 ** on the shipment. The beta users wanted to be able to
231 ** receive more than what was stated on the asn since it all
232 ** gets applied to the shipment line as receipt quantity anyway.
233 */
234 /* Bug# 1548597 */
235 rcv_quantities_s.get_available_quantity ('RECEIVE',
236 x_line_location_id,
237 'VENDOR', NULL, NULL, NULL, x_available_qty_hold,
238 x_tolerable_qty, x_uom_hold, X_secondary_available_qty);
239
240 -- Added this for ASN Phase 2 to account for shipped_quantity
241 select nvl(quantity_shipped,0)
242 into X_quantity_shipped
243 from po_line_locations
244 where
245 line_location_id = x_line_location_id;
246
247 /*
248 ** Adjust only the tolerable quantity here since this is an
249 ** ASN line and will display the quantity shipped
250 */
251 IF X_quantity_shipped > 0 then
252
253 /* bug 623925
254 /* if we are in this condition, that means we have ASN shipment against a PO shipment,
255 the x_tolerable_qty has been subtracted by all pending qty in RTI against this PO shipment
256 the x_available_qty has been subtracted by all pending qty in RTI against this ASN shipment
257 there can be potential double counting, we need to add qty that are both
258 against the PO shipment and ASN shipment in RTI, this would only happen when we are using
259 BATCH mode */
260
261 rcv_quantities_s.get_ship_qty_in_int(x_shipment_line_id, x_line_location_id, x_ship_qty_in_int);
262
263 /* the real tolerable qty should be as follows */
264
265 x_tolerable_qty := x_tolerable_qty - X_quantity_shipped + x_available_qty + x_ship_qty_in_int ;
266
267 IF x_tolerable_qty < 0 THEN
268 x_tolerable_qty := 0;
269 end if;
270
271 END IF;
272
273 -- Handle the return to vendor here
274
275 select sum(nvl(quantity,0)) into x_quantity_returned
276 from rcv_transactions
277 where rcv_transactions.shipment_line_id = x_shipment_line_id and
278 rcv_transactions.transaction_type = 'RETURN TO VENDOR';
279
280 IF X_quantity_returned > 0 then
281
282 x_available_qty := X_available_qty - X_quantity_returned;
283 x_tolerable_qty := x_tolerable_qty - X_quantity_returned;
284
285 If x_available_qty < 0 THEN
286 x_available_qty := 0;
287 end if;
288
289 IF x_tolerable_qty < 0 THEN
290 x_tolerable_qty := 0;
291 end if;
292
293 END IF;
294
295 END IF;
296
297 /*
298 ** If you're receiving a one time item then go get the primary
299 ** Unit of measure based on the unit of measure class that is
300 ** assigned to the base transaction unit of measure.
301 */
302
303 IF (x_item_id IS NULL) THEN
304
305 X_progress := '020';
306
307 SELECT unit_of_measure
308 INTO X_primary_uom
309 FROM mtl_units_of_measure mum
310 WHERE uom_class = x_unit_of_measure_class
311 AND mum.base_uom_flag = 'Y';
312
313 ELSE
314
315 X_progress := '025';
316
317 /*
318 ** Debug: This is only here so that we don't have to patch the
319 ** server. Once RCVRCERC.sql v is in place in the patch driver
320 ** then remove this call. This will then just overwrite the correct
321 ** value.
322 */
323 SELECT msi.primary_unit_of_measure
324 INTO X_primary_uom
325 FROM mtl_system_items msi
326 WHERE inventory_item_id = x_item_id
327 AND organization_id = x_org_id;
328
329
330 END IF;
331
332
333 X_progress := '030';
334
335 /*
336 ** Chk if transaction UOM is same as transaction's Primary UOM
337 ** If not same, then convert the transaction qty to Primary QTy's UOM
338 ** Only to be done if transaction qty > 0
339 */
340 if (x_uom <> x_primary_uom AND x_available_qty <> 0) THEN
341
342 /* Convert the transaction quantity to Primary UOM quantity */
343 PO_UOM_S.UOM_CONVERT ( x_available_qty, x_uom, x_item_id,
344 x_primary_uom, X_primary_qty );
345
346 else
347
348 X_primary_qty := x_available_qty;
349
350 end if; /* transaction UOM VS Primary UOM */
351
352 X_progress := '040';
353
354 /*
355 ** Check to make sure the ship to location is still valid
356 */
357 x_valid_ship_to_location := po_locations_s.val_receipt_site_in_org (
358 x_ship_to_location_id, x_org_id);
359
360 /*
361 ** IF this is a po based transaction then go get the distribution info
362 ** and validate it if there is one distribution. If there's more than
363 ** one then show multiple as the destination type and leave the other
364 ** destination fields blank. If this is an internal based transaction then
365 ** there is only one distribution and that data should be fetched through the view
366 ** and this function is not called but because the view naming conflicts
367 ** with the form I need to do this select temporarily until I can fix the
368 ** view.
369 */
370 IF (x_receipt_source_code = 'VENDOR') THEN
371
372 X_progress := '060';
373
374 rcv_distributions_s.get_distributions_info (x_line_location_id,
375 x_shipment_line_id, x_item_id, x_num_of_distributions,
376 x_po_distributions_id, x_destination_type_code,
377 x_destination_type_dsp, x_deliver_to_location_id ,
378 x_deliver_to_location,
379 x_deliver_to_person_id, x_deliver_to_person,
380 x_deliver_to_sub, x_deliver_to_locator_id, x_deliver_to_locator,
381 x_wip_entity_id, x_wip_repetitive_schedule_id, x_wip_line_id,
382 x_wip_operation_seq_num, x_wip_resource_seq_num, x_bom_resource_id,
383 x_to_organization_id, x_job, x_line_num, x_sequence, x_department,
384 x_rate, x_rate_date,
385 -- <RCV ENH FPI START>
386 x_kanban_card_number,
387 x_project_number,
388 x_task_number,
389 x_charge_account);
390 -- <RCV ENH FPI END>
391
392 ELSIF (x_receipt_source_code = 'CUSTOMER') THEN
393
394 /* -- moved to procedure RCV_TRANSACTIONS_SV.get_rma_dest_info()
395
396 select displayed_field, lookup_code into x_destination_type_dsp, x_destination_type_code
397 from po_lookup_codes where
398 lookup_CODE = 'RECEIVING' and
399 lookup_type = 'RCV DESTINATION TYPE'; */
400
401 /* Bug#4684017 START */
402
403 IF (x_oe_order_line_id IS NOT NULL) THEN
404
405 SELECT project_id, task_id
406 INTO X_project_id,X_task_id
407 FROM oe_order_lines_all
408 WHERE line_id = x_oe_order_line_id;
409
410 IF ( X_project_id IS NOT NULL AND
411 X_task_id IS NOT NULL ) THEN
412
413 Begin
414
415 select pa.project_number,pt.task_number
416 into x_project_number,x_task_number
417 from pjm_projects_all_v pa,
418 pa_tasks_expend_v pt
419 where pa.project_id = X_project_id
420 and pt.task_id = X_task_id
421 and pa.project_id=pt.project_id;
422
423
424 Exception
425 when no_data_found then
426 null;
427 End;
428
429 ELSIF ( X_project_id IS NOT NULL AND
430 X_task_id IS NULL ) THEN
431
432 Begin
433
434 select project_number
435 into x_project_number
436 from pjm_projects_all_v
437 where project_id = X_project_id;
438
439 Exception
440 when no_data_found then
441 null;
442 End;
443
444 END IF;
445
446 END IF;
447
448 /* Bug#4684017 END */
449
450 /* Bug# 1717095 - Need to get the Currency details for the Order */
451
452 -- <ENT RCPT PERF FPI START>
453 -- We will query from RCV_ENTER_RECEIPTS_RMA_V instead of
454 -- RCV_ENTER_RECEIPTS_V to reduce complexity of the view for the same result.
455 -- Actually in Receiving Forms we do not need this SQL because the view for
456 -- block RCV_TRANSACTION has already got the value. This is called for
457 -- INV code that calls this procedure
458
459 /* SELECT currency_conversion_rate,currency_conversion_date
460 INTO x_rate,x_rate_date
461 FROM rcv_enter_receipts_rma_v
462 WHERE oe_order_header_id = x_oe_order_header_id
463 AND oe_order_line_id = x_oe_order_line_id; */
464
465 -- <ENT RCPT PERF FPI END>
466
467 /* Bug# 2864540 - Due to performance problems replaced the view
468 rcv_enter_receipts_rma_v with the underlying base table */
469
470 /* -- moved to procedure RCV_TRANSACTIONS_SV.get_rma_dest_info()
471 SELECT conversion_rate, conversion_rate_date
472 INTO x_rate, x_rate_date
473 FROM oe_order_headers_all
474 WHERE header_id = x_oe_order_header_id; */
475
476 /* Bug 3378162 - For RMA, we need to default the subinventory and
477 * location from the backing OE line -pjiang*/
478 -- get default subinventory, location, location_id from OE
479 -- get the conversion rate and destination_type info
480 RCV_TRANSACTIONS_SV.get_rma_dest_info(x_oe_order_header_id,
481 x_oe_order_line_id,
482 x_item_id,
483 x_deliver_to_sub,
484 x_deliver_to_location_id,
485 x_deliver_to_location,
486 x_destination_type_dsp,
487 x_destination_type_code,
488 x_to_organization_id,
489 x_rate,
490 x_rate_date);
491
492 ELSE
493
494 X_progress := '070';
495
496 /* bug : 971489 - moved the join to hr_employees_current_v into
497 a different select to improve performance.The deliver to
498 person's full name is got from the above view only when
499 the deliver_to_person_id is not null.
500
501 Bug 1205660 - Select the req distribution id which will be used
502 later to get the project_id and task_id from req distributions.
503 GMudgal 2-28-2000
504 */
505
506 SELECT rsl.destination_type_code,
507 polc.displayed_field,
508 rsl.deliver_to_person_id,
509 rsl.deliver_to_location_id,
510 hlo.location_code,
511 rsl.to_subinventory,
512 rsl.req_distribution_id,
513 MKC.kanban_card_number, -- <RCV ENH FPI>
514 PPA.project_number, -- <RCV ENH FPI>
515 PTE.task_number, -- <RCV ENH FPI>
516 PRD.code_combination_id -- <RCV ENH FPI>
517 INTO x_destination_type_code,
518 x_destination_type_dsp,
519 x_deliver_to_person_id,
520 x_deliver_to_location_id ,
521 x_deliver_to_location,
522 x_deliver_to_sub,
523 x_req_distribution_id,
524 x_kanban_card_number, -- <RCV ENH FPI>
525 x_project_number, -- <RCV ENH FPI>
526 x_task_number, -- <RCV ENH FPI>
527 l_code_combination_id -- <RCV ENH FPI>
528 FROM rcv_shipment_lines rsl,
529 hr_locations hlo,
530 po_lookup_codes polc,
531 po_requisition_lines PRL, -- <RCV ENH FPI>
532 po_req_distributions PRD, -- <RCV ENH FPI>
533 mtl_kanban_cards MKC, -- <RCV ENH FPI>
534 pjm_projects_all_v PPA, -- <RCV ENH FPI>
535 pa_tasks_expend_v PTE -- <RCV ENH FPI>
536 WHERE polc.lookup_type = 'RCV DESTINATION TYPE'
537 AND polc.lookup_code = NVL( rsl.destination_type_code, 'INVENTORY')
538 AND rsl.shipment_line_id = x_shipment_line_id
539 AND hlo.location_id(+) = rsl.deliver_to_location_id
540 AND RSL.requisition_line_id = PRL.requisition_line_id (+) -- <RCV ENH FPI>
541 AND PRL.kanban_card_id = MKC.kanban_card_id (+) -- <RCV ENH FPI>
542 AND PRL.requisition_line_id = PRD.requisition_line_id (+) -- <RCV ENH FPI>
543 AND PRD.project_id = PPA.project_id (+) -- <RCV ENH FPI>
544 AND PRD.task_id = PTE.task_id (+); -- <RCV ENH FPI>
545
546 IF (x_deliver_to_person_id is not null) THEN
547
548 /* Bug# 1840300 - Cannot receive against Internal Requisition
549 if requestor has been terminated */
550
551 /* Bug 3582515 : The change in maiden name was not handled correctly by
552 the earlier SQL query. We now call the function
553 po_inq_sv.get_person_name() to get the full name
554 of requester in the variable X_deliver_to_person.
555 */
556
557 X_deliver_to_person := po_inq_sv.get_person_name(X_deliver_to_person_id);
558
559 END IF;
560
561 -- <RCV ENH FPI START>
562 x_progress := '075';
563
564 x_charge_account :=
565 PO_COMPARE_REVISIONS.get_charge_account(l_code_combination_id);
566 -- <RCV ENH FPI END>
567
568 END IF;
569
570 /* get receiving controls */
571 IF (x_receipt_source_code = 'VENDOR') THEN
572
573 X_progress := '080';
574
575 rcv_core_s.get_receiving_controls(
576 x_line_location_id,
577 x_item_id,
578 x_vendor_id,
579 x_org_id,
580 x_enforce_ship_to_loc,
581 x_allow_substitutes,
582 x_routing_id,
583 x_qty_rcv_tolerance,
584 x_qty_rcv_exception,
585 x_days_early_receipt,
586 x_days_late_receipt,
587 x_rcv_days_exception);
588
589 ELSE
590
591 X_progress := '090';
592
593 rcv_core_s.get_receiving_controls(
594 NULL,
595 x_item_id,
596 x_vendor_id,
597 x_org_id,
598 x_enforce_ship_to_loc,
599 x_allow_substitutes,
600 x_routing_id,
601 x_qty_rcv_tolerance,
602 x_qty_rcv_exception,
603 x_days_early_receipt,
604 x_days_late_receipt,
605 x_rcv_days_exception);
606
607 /*
608 ** If this is an internal transaction then you cannot over receive
609 ** this shipment. We copy 'INTERNAL' into qty_rcv_exception_code
610 ** on post query for an internal transaction and in the uom_qty_conversion
611 ** we'll check against the origanal quantity instead of the tolerable
612 ** quantity. THis is used for WVI on the transaction quantity.
613 */
614 x_qty_rcv_exception := 'INTERNAL';
615
616 /* Bug# 1942953 - Intransit receipts doesn't follow shipping network
617 routing */
618
619 IF (x_receipt_source_code <> 'CUSTOMER') THEN
620 x_progress := '092';
621
622 IF (NVL(x_item_id,0) <> 0) THEN
623 x_progress := '094';
624
625 BEGIN
626
627 SELECT NVL(receiving_routing_id,0)
628 INTO x_routing_id
629 FROM mtl_system_items
630 WHERE inventory_item_id = x_item_id
631 AND organization_id = x_org_id;
632
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN NULL;
635 WHEN OTHERS THEN RAISE;
636 END;
637
638 IF (x_routing_id = 0) THEN
639 x_progress := '096';
640
641 BEGIN
642
643 SELECT NVL(ROUTING_HEADER_ID,0)
644 INTO x_routing_id
645 FROM MTL_INTERORG_PARAMETERS
646 WHERE FROM_ORGANIZATION_ID = x_from_org_id
647 AND TO_ORGANIZATION_ID = x_org_id;
648
649 EXCEPTION
650 WHEN NO_DATA_FOUND THEN NULL;
651 WHEN OTHERS THEN RAISE;
652 END;
653
654 END IF;
655
656 /* Bug# 2194792 - For Internal Orders when Routing is 'Direct',
657 ROUTING_HEADER_ID is null. So default the routing from the
658 Receiving Organization */
659
660 IF (x_routing_id = 0) THEN
661 x_progress := '098';
662
663 BEGIN
664
665 SELECT NVL(RECEIVING_ROUTING_ID,0)
666 INTO x_routing_id
667 FROM RCV_PARAMETERS
668 WHERE ORGANIZATION_ID = x_org_id;
669
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN NULL;
672 WHEN OTHERS THEN RAISE;
673 END;
674
675 END IF;
676
677 /* x_routing_id will be zero if none of the above statements
678 returns a value */
679
680 END IF;
681
682 END IF;
683
684 END IF;
685
686 /* Begin Bug# 3672978 - Routing change in the Manage Shipments form
687 was not considered in the Receiving forms */
688
689 IF (x_receipt_source_code <> 'CUSTOMER') THEN
690 x_progress := '100';
691
692 BEGIN
693 SELECT routing_header_id
694 INTO x_ms_routing_id
695 FROM rcv_shipment_lines
696 WHERE shipment_line_id = x_shipment_line_id;
697 EXCEPTION
698 WHEN NO_DATA_FOUND THEN NULL;
699 END;
700
701 IF x_ms_routing_id is not null THEN
702 x_routing_id := x_ms_routing_id;
703 END IF;
704 END IF;
705
706 /* End Bug# 3555251 */
707
708 X_progress := '105';
709
710
711 /* get latest item revision IF null */
712 IF (x_destination_type_code = 'INVENTORY' AND
713 x_item_id is not null AND
714 x_item_revision is not null AND
715 NVL(x_item_rev_control_flag_to, 'N') = 'Y') THEN
716
717 po_items_sv2.get_latest_item_rev(
718 x_item_id,
719 x_org_id,
720 x_item_revision,
721 p_rev_exists);
722
723 END IF;
724
725
726 X_progress := '110';
727
728 IF (x_destination_type_code = 'INVENTORY' AND
729 x_deliver_to_sub IS NULL) THEN
730
731 po_subinventories_s.get_default_subinventory (
732 x_org_id,
733 x_item_id,
734 x_deliver_to_sub);
735
736 END IF;
737
738 X_progress := '120';
739
740 /*
741 ** Go get the locator control value if the locator control has
742 ** not already been selected or if the subinventory has been
743 ** modified
744 */
745 IF (x_destination_type_code = 'INVENTORY' AND
746 x_locator_control IS NULL AND
747 x_deliver_to_sub IS NOT NULL) THEN
748
749 po_subinventories_s.get_locator_control (
750 x_org_id,
751 x_deliver_to_sub,
752 x_item_id,
753 x_locator_control);
754
755 END IF;
756
757 X_progress := '130';
758
759 IF (x_destination_type_code = 'INVENTORY' AND
760 x_deliver_to_sub IS NOT NULL) THEN
761
762 /*
763 ** get default locator
764 */
765 /*
766 ** Anytime a subinventory is selected then the locator field
767 ** should be prepopulated with the default locator_id from
768 ** mtl_item_loc_defaults for the item, org and subinventory
769 ** and where the default_type = 2
770 */
771
772 po_subinventories_s.get_default_locator (
773 x_org_id,
774 x_item_id,
775 x_deliver_to_sub,
776 x_deliver_to_locator_id);
777
778
779 /* Bug 3537022.
780 * Get locator_id from rcv_shipment_lines for intransit
781 * shipments.
782 */
783 x_progress := 80;
784
785 if (x_receipt_source_code = 'INVENTORY') then
786 select locator_id
787 into x_deliver_to_locator_id
788 from rcv_shipment_lines
789 where shipment_line_id = x_shipment_line_id;
790 end if;
791
792 x_progress := 90;
793
794
795
796 END IF;
797
798 IF (x_destination_type_code = 'INVENTORY') THEN
799
800 X_default_subinventory := x_deliver_to_sub;
801 X_default_locator_id := x_deliver_to_locator_id;
802
803 X_success := rcv_sub_locator_sv.put_away_api (
804 x_line_location_id ,
805 x_po_distributions_id ,
806 x_shipment_line_id ,
807 x_receipt_source_code ,
808 x_org_id ,
809 x_to_organization_id ,
810 x_item_id ,
811 x_item_revision ,
812 x_vendor_id ,
813 x_ship_to_location_id ,
814 x_deliver_to_location_id,
815 x_deliver_to_person_id ,
816 x_available_qty ,
817 x_primary_qty ,
818 x_primary_uom ,
819 x_tolerable_qty ,
820 x_uom ,
821 x_routing_id ,
822 x_default_subinventory ,
823 x_default_locator_id ,
824 x_deliver_to_sub ,
825 x_deliver_to_locator_id);
826
827 X_progress := '132';
828
829 IF (x_receipt_source_code <> 'CUSTOMER') THEN
830
831 IF (X_po_distributions_id IS NOT NULL AND
832 x_deliver_to_locator_id IS NOT NULL) THEN
833
834 X_progress := '133';
835
836 SELECT project_id, task_id
837 INTO X_project_id, X_task_id
838 FROM po_distributions
839 WHERE po_distribution_id = X_po_distributions_id;
840
841 ELSIF (x_req_distribution_id IS NOT NULL AND
842 x_deliver_to_locator_id IS NOT NULL) THEN
843
844 /* Bug 1205660. Locator defaulting for Internal Orders */
845
846 /* Fix for 2444052.
847 Ported the fix as done in Rel 10.7 by adding an exception handler
848 for the following select statement.
849 */
850
851 begin
852 X_progress := '134';
853
854 SELECT project_id, task_id
855 INTO X_project_id, X_task_id
856 FROM po_req_distributions
857 WHERE distribution_id = x_req_distribution_id;
858
859 exception
860 when no_data_found then
861 null;
862 end;
863
864 END IF;
865
866 ELSE
867 /* Locator field defaulting for rma's */
868 X_progress := '135';
869
870 IF (x_oe_order_line_id IS NOT NULL AND
871 x_deliver_to_locator_id IS NOT NULL) THEN
872
873 SELECT project_id, task_id
874 INTO X_project_id,X_task_id
875 FROM oe_order_lines_all
876 WHERE line_id = x_oe_order_line_id;
877
878 END IF;
879
880 END IF;
881 /*
882 ** Set the default values for the locator based on a
883 ** project manufacturing call. If the default locator
884 ** does not have the project and task that is specified
885 ** on the po and the locator control is dynamic then
886 ** project manufacturing will create a new locator row
887 ** copying all values from the existing locator row while
888 ** adding the new project and task is values
889 */
890 /* Bug 1349864 - 25-JUL-2000: GMudgal
891 ** Added a begin-end construct around the PJM call
892 ** We don't really need to default the project
893 ** locators when doing a standard receipt. We need
894 ** to still be able to query up the document even if
895 ** we are unable to default the locators.
896 */
897 IF (X_project_id IS NOT NULL AND
898 -- X_locator_control = 3 AND--for bug 588172 as part of bug 1662321
899 X_deliver_to_sub is not null) THEN
900 begin
901
902 X_progress := '150';
903
904 X_default_locator_id := X_deliver_to_locator_id; -- Bug 2772050
905 PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator(
906 X_to_organization_id,
907 X_default_locator_id, -- Bug 2772050
908 X_project_id,
909 X_task_id,
910 X_deliver_to_locator_id);
911 exception
912 when others then
913 null ;
914 end ;
915
916 END IF;
917
918
919 END IF;
920
921 X_progress := '160';
922
923 /*
924 ** Figure out if any distribution for this shipment has an inventory
925 ** destination. If it does and item is under rev control then the
926 ** item revision must be required. If there are inventory destinations
927 ** but the item is not under item rev control then the rev must be
928 ** disabled. If there are no inventory destinations then it does not
929 ** matter
930 */
931 x_inv_destinations := rcv_transactions_sv.val_if_inventory_destination (
932 x_line_location_id,
933 x_shipment_line_id);
934
935
936 EXCEPTION
937
938 WHEN OTHERS THEN
939 po_message_s.sql_error('post_query', x_progress, sqlcode);
940 RAISE;
941
942 END post_query;
943
944 /* Bug 2668645- Added the following procedure to get the default value for the
945 DFF. This procedure is called in the block level post-query of rcv_transactions block . */
946
947 /* Bug 3775987. Removing the get_dff_default procedure as it is raising
948 * flex errors related to Receiving Transactions DFF and not allowing to
949 * create receipts.
950 **/
951
952 /*===========================================================================
953
954 PROCEDURE NAME: post_query()
955
956 ===========================================================================*/
957 /* bug2730828 */
958 /* This procedure overloads another post_query procedure within this package.
959 This procedure is used by other team and for bug fixes of post_query
960 please make changes to the one that this procedure is calling.
961 */
962
963 PROCEDURE post_query ( x_line_location_id IN NUMBER,
964 x_shipment_line_id IN NUMBER,
965 x_receipt_source_code IN VARCHAR2,
966 x_org_id IN NUMBER,
967 x_item_id IN NUMBER,
968 x_unit_of_measure_class IN VARCHAR2,
969 x_ship_to_location_id IN NUMBER,
970 x_vendor_id IN NUMBER,
971 x_customer_id IN NUMBER,
972 x_item_rev_control_flag_to IN VARCHAR2,
973 x_available_qty IN OUT NOCOPY NUMBER,
974 x_primary_qty IN OUT NOCOPY NUMBER,
975 x_tolerable_qty IN OUT NOCOPY NUMBER,
976 x_uom IN OUT NOCOPY VARCHAR2,
977 x_primary_uom IN OUT NOCOPY VARCHAR2,
978 x_valid_ship_to_location IN OUT NOCOPY BOOLEAN,
979 x_num_of_distributions IN OUT NOCOPY NUMBER,
980 x_po_distributions_id IN OUT NOCOPY NUMBER,
981 x_destination_type_code IN OUT NOCOPY VARCHAR2,
982 x_destination_type_dsp IN OUT NOCOPY VARCHAR2,
983 x_deliver_to_location_id IN OUT NOCOPY NUMBER,
984 x_deliver_to_location IN OUT NOCOPY VARCHAR2,
985 x_deliver_to_person_id IN OUT NOCOPY NUMBER,
986 x_deliver_to_person IN OUT NOCOPY VARCHAR2,
987 x_deliver_to_sub IN OUT NOCOPY VARCHAR2,
988 x_deliver_to_locator_id IN OUT NOCOPY NUMBER,
989 x_wip_entity_id IN OUT NOCOPY NUMBER,
990 x_wip_repetitive_schedule_id IN OUT NOCOPY NUMBER,
991 x_wip_line_id IN OUT NOCOPY NUMBER,
992 x_wip_operation_seq_num IN OUT NOCOPY NUMBER,
993 x_wip_resource_seq_num IN OUT NOCOPY NUMBER,
994 x_bom_resource_id IN OUT NOCOPY NUMBER,
995 x_to_organization_id IN OUT NOCOPY NUMBER,
996 x_job IN OUT NOCOPY VARCHAR2,
997 x_line_num IN OUT NOCOPY VARCHAR2,
998 x_sequence IN OUT NOCOPY NUMBER,
999 x_department IN OUT NOCOPY VARCHAR2,
1000 x_enforce_ship_to_loc IN OUT NOCOPY VARCHAR2,
1001 x_allow_substitutes IN OUT NOCOPY VARCHAR2,
1002 x_routing_id IN OUT NOCOPY NUMBER,
1003 x_qty_rcv_tolerance IN OUT NOCOPY NUMBER,
1004 x_qty_rcv_exception IN OUT NOCOPY VARCHAR2,
1005 x_days_early_receipt IN OUT NOCOPY NUMBER,
1006 x_days_late_receipt IN OUT NOCOPY NUMBER,
1007 x_rcv_days_exception IN OUT NOCOPY VARCHAR2,
1008 x_item_revision IN OUT NOCOPY VARCHAR2,
1009 x_locator_control IN OUT NOCOPY NUMBER,
1010 x_inv_destinations IN OUT NOCOPY BOOLEAN,
1011 x_rate IN OUT NOCOPY NUMBER,
1012 x_rate_date IN OUT NOCOPY DATE,
1013 x_asn_type IN VARCHAR2,
1014 x_oe_order_header_id IN NUMBER,
1015 x_oe_order_line_id IN NUMBER,
1016 x_from_org_id IN NUMBER DEFAULT NULL) IS
1017 /* create dummy variables just to store throw-away values */
1018 l_dummy1 MTL_KANBAN_CARDS.kanban_card_number%TYPE;
1019 l_dummy2 PJM_PROJECTS_ALL_V.project_number%TYPE;
1020 l_dummy3 PA_TASKS_EXPEND_V.task_number%TYPE;
1021 l_dummy4 GL_CODE_COMBINATIONS_KFV.concatenated_segments%TYPE;
1022
1023 BEGIN
1024 RCV_RECEIPTS_QUERY_SV.post_query(
1025 x_line_location_id,
1026 x_shipment_line_id,
1027 x_receipt_source_code,
1028 x_org_id,
1029 x_item_id,
1030 x_unit_of_measure_class,
1031 x_ship_to_location_id,
1032 x_vendor_id,
1033 x_customer_id,
1034 x_item_rev_control_flag_to,
1035 x_available_qty,
1036 x_primary_qty,
1037 x_tolerable_qty,
1038 x_uom,
1039 x_primary_uom,
1040 x_valid_ship_to_location,
1041 x_num_of_distributions,
1042 x_po_distributions_id,
1043 x_destination_type_code,
1044 x_destination_type_dsp,
1045 x_deliver_to_location_id,
1046 x_deliver_to_location,
1047 x_deliver_to_person_id,
1048 x_deliver_to_person,
1049 x_deliver_to_sub,
1050 x_deliver_to_locator_id,
1051 x_wip_entity_id,
1052 x_wip_repetitive_schedule_id,
1053 x_wip_line_id,
1054 x_wip_operation_seq_num,
1055 x_wip_resource_seq_num,
1056 x_bom_resource_id,
1057 x_to_organization_id,
1058 x_job,
1059 x_line_num,
1060 x_sequence,
1061 x_department,
1062 x_enforce_ship_to_loc,
1063 x_allow_substitutes,
1064 x_routing_id,
1065 x_qty_rcv_tolerance,
1066 x_qty_rcv_exception,
1067 x_days_early_receipt,
1068 x_days_late_receipt,
1069 x_rcv_days_exception,
1070 x_item_revision,
1071 x_locator_control,
1072 x_inv_destinations,
1073 x_rate,
1074 x_rate_date,
1075 x_asn_type,
1076 x_oe_order_header_id,
1077 x_oe_order_line_id,
1078 x_from_org_id,
1079 l_dummy1,
1080 l_dummy2,
1081 l_dummy3,
1082 l_dummy4);
1083
1084 END post_query;
1085
1086 -- <ENT RCPT PERF FPI START>
1087
1088
1089 /**
1090 * Public Procedure: exec_dynamic_sql
1091 * Requires: the number of records in p_val has to match the number of
1092 * bind variables in p_query, which is a sql string
1093 * the sql to execute should be a clause that check
1094 * existence only
1095 * The number of bind variables should also be less than or equal
1096 * to 7 unless modified.
1097 * Modifies: None
1098 * Effects: Executes a dynamic sql defined in p_query. the value of bind
1099 * variables will be defined in p_val
1100 * Returns:
1101 * x_exist - FND_API.G_TRUE if sql is executed successfully without any error
1102 * FND_API.G_FALSE if sql returns NO_DATA_FOUND
1103 * The procedure raises an exception if p_query returns an error other than
1104 * NO_DATA_FOUND
1105 */
1106
1107 PROCEDURE exec_dynamic_sql(p_query IN VARCHAR2,
1108 p_val IN RCV_RECEIPTS_QUERY_SV.NUM_TBL_TYPE,
1109 x_exist OUT NOCOPY VARCHAR2) IS
1110 l_tmp NUMBER;
1111 l_progress VARCHAR2(3) := '000';
1112 l_exception EXCEPTION;
1113 l_num_bind NUMBER;
1114 BEGIN
1115
1116 l_num_bind := p_val.COUNT;
1117
1118 IF (l_num_bind = 0) THEN
1119 l_progress := '000';
1120 EXECUTE IMMEDIATE p_query INTO l_tmp;
1121
1122 ELSIF (l_num_bind = 1) THEN
1123 l_progress := '010';
1124 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1);
1125
1126 ELSIF (l_num_bind = 2) THEN
1127 l_progress := '020';
1128 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2);
1129
1130 ELSIF (l_num_bind = 3) THEN
1131 l_progress := '030';
1132 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3);
1133
1134 ELSIF (l_num_bind = 4) THEN
1135 l_progress := '040';
1136 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1137 p_val(4);
1138
1139 ELSIF (l_num_bind = 5) THEN
1140 l_progress := '050';
1141 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1142 p_val(4), p_val(5);
1143
1144 ELSIF (l_num_bind = 6) THEN
1145 l_progress := '060';
1146 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1147 p_val(4), p_val(5), p_val(6);
1148
1149 ELSIF (l_num_bind = 7) THEN
1150 l_progress := '070';
1151 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1152 p_val(4), p_val(5), p_val(6),
1153 p_val(7);
1154 ELSE
1155 l_progress := '080';
1156 RAISE l_exception;
1157 END IF;
1158
1159 x_exist := FND_API.G_TRUE;
1160 EXCEPTION
1161 WHEN NO_DATA_FOUND THEN
1162 x_exist := FND_API.G_FALSE;
1163 WHEN OTHERS THEN
1164 PO_MESSAGE_S.sql_error('exec_dynamic_sql', l_progress, sqlcode);
1165 RAISE;
1166 END exec_dynamic_sql;
1167
1168
1169 -- <ENT RCPT PERF FPI END>
1170
1171 END RCV_RECEIPTS_QUERY_SV;
[Home] [Help]