1 PACKAGE BODY RCV_RECEIPTS_QUERY_SV AS
2 /* $Header: RCVRCPQB.pls 120.2.12010000.4 2010/01/29 02:48:19 jastang 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 x_subinv VARCHAR2 (60); -- bug 9298154
110 BEGIN
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 -- Bug 7681237: Removed the Return qty subtraction code
274 END IF;
275
276 /*
277 ** If you're receiving a one time item then go get the primary
278 ** Unit of measure based on the unit of measure class that is
279 ** assigned to the base transaction unit of measure.
280 */
281
282 IF (x_item_id IS NULL) THEN
283
284 X_progress := '020';
285
286 SELECT unit_of_measure
287 INTO X_primary_uom
288 FROM mtl_units_of_measure mum
289 WHERE uom_class = x_unit_of_measure_class
290 AND mum.base_uom_flag = 'Y';
291
292 ELSE
293
294 X_progress := '025';
295
296 /*
297 ** Debug: This is only here so that we don't have to patch the
298 ** server. Once RCVRCERC.sql v is in place in the patch driver
299 ** then remove this call. This will then just overwrite the correct
300 ** value.
301 */
302 SELECT msi.primary_unit_of_measure
303 INTO X_primary_uom
304 FROM mtl_system_items msi
305 WHERE inventory_item_id = x_item_id
306 AND organization_id = x_org_id;
307
308
309 END IF;
310
311
312 X_progress := '030';
313
314 /*
315 ** Chk if transaction UOM is same as transaction's Primary UOM
316 ** If not same, then convert the transaction qty to Primary QTy's UOM
317 ** Only to be done if transaction qty > 0
318 */
319 if (x_uom <> x_primary_uom AND x_available_qty <> 0) THEN
320
321 /* Convert the transaction quantity to Primary UOM quantity */
322 PO_UOM_S.UOM_CONVERT ( x_available_qty, x_uom, x_item_id,
323 x_primary_uom, X_primary_qty );
324
325 else
326
327 X_primary_qty := x_available_qty;
328
329 end if; /* transaction UOM VS Primary UOM */
330
331 X_progress := '040';
332
333 /*
334 ** Check to make sure the ship to location is still valid
335 */
336 x_valid_ship_to_location := po_locations_s.val_receipt_site_in_org (
337 x_ship_to_location_id, x_org_id);
338
339 /*
340 ** IF this is a po based transaction then go get the distribution info
341 ** and validate it if there is one distribution. If there's more than
342 ** one then show multiple as the destination type and leave the other
343 ** destination fields blank. If this is an internal based transaction then
344 ** there is only one distribution and that data should be fetched through the view
345 ** and this function is not called but because the view naming conflicts
346 ** with the form I need to do this select temporarily until I can fix the
347 ** view.
348 */
349 IF (x_receipt_source_code = 'VENDOR') THEN
350
351 X_progress := '060';
352
353 rcv_distributions_s.get_distributions_info (x_line_location_id,
354 x_shipment_line_id, x_item_id, x_num_of_distributions,
355 x_po_distributions_id, x_destination_type_code,
356 x_destination_type_dsp, x_deliver_to_location_id ,
357 x_deliver_to_location,
358 x_deliver_to_person_id, x_deliver_to_person,
359 x_deliver_to_sub, x_deliver_to_locator_id, x_deliver_to_locator,
360 x_wip_entity_id, x_wip_repetitive_schedule_id, x_wip_line_id,
361 x_wip_operation_seq_num, x_wip_resource_seq_num, x_bom_resource_id,
362 x_to_organization_id, x_job, x_line_num, x_sequence, x_department,
363 x_rate, x_rate_date,
364 -- <RCV ENH FPI START>
365 x_kanban_card_number,
366 x_project_number,
367 x_task_number,
368 x_charge_account);
369 -- <RCV ENH FPI END>
370
371 ELSIF (x_receipt_source_code = 'CUSTOMER') THEN
372
373 /* -- moved to procedure RCV_TRANSACTIONS_SV.get_rma_dest_info()
374
375 select displayed_field, lookup_code into x_destination_type_dsp, x_destination_type_code
376 from po_lookup_codes where
377 lookup_CODE = 'RECEIVING' and
378 lookup_type = 'RCV DESTINATION TYPE'; */
379
380 /* Bug#4684017 START */
381
382 IF (x_oe_order_line_id IS NOT NULL) THEN
383
384 SELECT project_id, task_id
385 INTO X_project_id,X_task_id
386 FROM oe_order_lines_all
387 WHERE line_id = x_oe_order_line_id;
388
389 IF ( X_project_id IS NOT NULL AND
390 X_task_id IS NOT NULL ) THEN
391
392 Begin
393
394 select pa.project_number,pt.task_number
395 into x_project_number,x_task_number
396 from pjm_projects_all_v pa,
397 pa_tasks_expend_v pt
398 where pa.project_id = X_project_id
399 and pt.task_id = X_task_id
400 and pa.project_id=pt.project_id;
401
402
403 Exception
404 when no_data_found then
405 null;
406 End;
407
408 ELSIF ( X_project_id IS NOT NULL AND
409 X_task_id IS NULL ) THEN
410
411 Begin
412
413 select project_number
414 into x_project_number
415 from pjm_projects_all_v
416 where project_id = X_project_id;
417
418 Exception
419 when no_data_found then
420 null;
421 End;
422
423 END IF;
424
425 END IF;
426
427 /* Bug#4684017 END */
428
429 /* Bug# 1717095 - Need to get the Currency details for the Order */
430
431 -- <ENT RCPT PERF FPI START>
432 -- We will query from RCV_ENTER_RECEIPTS_RMA_V instead of
433 -- RCV_ENTER_RECEIPTS_V to reduce complexity of the view for the same result.
434 -- Actually in Receiving Forms we do not need this SQL because the view for
435 -- block RCV_TRANSACTION has already got the value. This is called for
436 -- INV code that calls this procedure
437
438 /* SELECT currency_conversion_rate,currency_conversion_date
439 INTO x_rate,x_rate_date
440 FROM rcv_enter_receipts_rma_v
441 WHERE oe_order_header_id = x_oe_order_header_id
442 AND oe_order_line_id = x_oe_order_line_id; */
443
444 -- <ENT RCPT PERF FPI END>
445
446 /* Bug# 2864540 - Due to performance problems replaced the view
447 rcv_enter_receipts_rma_v with the underlying base table */
448
449 /* -- moved to procedure RCV_TRANSACTIONS_SV.get_rma_dest_info()
450 SELECT conversion_rate, conversion_rate_date
451 INTO x_rate, x_rate_date
452 FROM oe_order_headers_all
453 WHERE header_id = x_oe_order_header_id; */
454
455 /* Bug 3378162 - For RMA, we need to default the subinventory and
456 * location from the backing OE line -pjiang*/
457 -- get default subinventory, location, location_id from OE
458 -- get the conversion rate and destination_type info
459 RCV_TRANSACTIONS_SV.get_rma_dest_info(x_oe_order_header_id,
460 x_oe_order_line_id,
461 x_item_id,
462 x_deliver_to_sub,
463 x_deliver_to_location_id,
464 x_deliver_to_location,
465 x_destination_type_dsp,
466 x_destination_type_code,
467 x_to_organization_id,
468 x_rate,
469 x_rate_date);
470
471 ELSE
472
473 X_progress := '070';
474
475 /* bug : 971489 - moved the join to hr_employees_current_v into
476 a different select to improve performance.The deliver to
477 person's full name is got from the above view only when
478 the deliver_to_person_id is not null.
479
480 Bug 1205660 - Select the req distribution id which will be used
481 later to get the project_id and task_id from req distributions.
482 GMudgal 2-28-2000
483 */
484
485 SELECT rsl.destination_type_code,
486 polc.displayed_field,
487 rsl.deliver_to_person_id,
488 rsl.deliver_to_location_id,
489 hlo.location_code,
490 rsl.to_subinventory,
491 rsl.req_distribution_id,
492 MKC.kanban_card_number, -- <RCV ENH FPI>
493 PPA.project_number, -- <RCV ENH FPI>
494 PTE.task_number, -- <RCV ENH FPI>
495 PRD.code_combination_id -- <RCV ENH FPI>
496 INTO x_destination_type_code,
497 x_destination_type_dsp,
498 x_deliver_to_person_id,
499 x_deliver_to_location_id ,
500 x_deliver_to_location,
501 x_deliver_to_sub,
502 x_req_distribution_id,
503 x_kanban_card_number, -- <RCV ENH FPI>
504 x_project_number, -- <RCV ENH FPI>
505 x_task_number, -- <RCV ENH FPI>
506 l_code_combination_id -- <RCV ENH FPI>
507 FROM rcv_shipment_lines rsl,
508 hr_locations hlo,
509 po_lookup_codes polc,
510 po_requisition_lines PRL, -- <RCV ENH FPI>
511 po_req_distributions PRD, -- <RCV ENH FPI>
512 mtl_kanban_cards MKC, -- <RCV ENH FPI>
513 pjm_projects_all_v PPA, -- <RCV ENH FPI>
514 pa_tasks_expend_v PTE -- <RCV ENH FPI>
515 WHERE polc.lookup_type = 'RCV DESTINATION TYPE'
516 AND polc.lookup_code = NVL( rsl.destination_type_code, 'INVENTORY')
517 AND rsl.shipment_line_id = x_shipment_line_id
518 AND hlo.location_id(+) = rsl.deliver_to_location_id
519 AND RSL.requisition_line_id = PRL.requisition_line_id (+) -- <RCV ENH FPI>
520 AND PRL.kanban_card_id = MKC.kanban_card_id (+) -- <RCV ENH FPI>
521 AND PRL.requisition_line_id = PRD.requisition_line_id (+) -- <RCV ENH FPI>
522 AND PRD.project_id = PPA.project_id (+) -- <RCV ENH FPI>
523 AND PRD.task_id = PTE.task_id (+); -- <RCV ENH FPI>
524
525 IF (x_deliver_to_person_id is not null) THEN
526
527 /* Bug# 1840300 - Cannot receive against Internal Requisition
528 if requestor has been terminated */
529
530 /* Bug 3582515 : The change in maiden name was not handled correctly by
531 the earlier SQL query. We now call the function
532 po_inq_sv.get_person_name() to get the full name
533 of requester in the variable X_deliver_to_person.
534 */
535
536 X_deliver_to_person := po_inq_sv.get_person_name(X_deliver_to_person_id);
537
538 END IF;
539
540 -- <RCV ENH FPI START>
541 x_progress := '075';
542
543 x_charge_account :=
544 PO_COMPARE_REVISIONS.get_charge_account(l_code_combination_id);
545 -- <RCV ENH FPI END>
546
547 END IF;
548
549 /* get receiving controls */
550 IF (x_receipt_source_code = 'VENDOR') THEN
551
552 X_progress := '080';
553
554 rcv_core_s.get_receiving_controls(
555 x_line_location_id,
556 x_item_id,
557 x_vendor_id,
558 x_org_id,
559 x_enforce_ship_to_loc,
560 x_allow_substitutes,
561 x_routing_id,
562 x_qty_rcv_tolerance,
563 x_qty_rcv_exception,
564 x_days_early_receipt,
565 x_days_late_receipt,
566 x_rcv_days_exception);
567
568 ELSE
569
570 X_progress := '090';
571
572 rcv_core_s.get_receiving_controls(
573 NULL,
574 x_item_id,
575 x_vendor_id,
576 x_org_id,
577 x_enforce_ship_to_loc,
578 x_allow_substitutes,
579 x_routing_id,
580 x_qty_rcv_tolerance,
581 x_qty_rcv_exception,
582 x_days_early_receipt,
583 x_days_late_receipt,
584 x_rcv_days_exception);
585
586 /*
587 ** If this is an internal transaction then you cannot over receive
588 ** this shipment. We copy 'INTERNAL' into qty_rcv_exception_code
589 ** on post query for an internal transaction and in the uom_qty_conversion
590 ** we'll check against the origanal quantity instead of the tolerable
591 ** quantity. THis is used for WVI on the transaction quantity.
592 */
593 x_qty_rcv_exception := 'INTERNAL';
594
595 /* Bug# 1942953 - Intransit receipts doesn't follow shipping network
596 routing */
597
598 IF (x_receipt_source_code <> 'CUSTOMER') THEN
599 x_progress := '092';
600
601 IF (NVL(x_item_id,0) <> 0) THEN
602 x_progress := '094';
603
604 BEGIN
605
606 SELECT NVL(receiving_routing_id,0)
607 INTO x_routing_id
608 FROM mtl_system_items
609 WHERE inventory_item_id = x_item_id
610 AND organization_id = x_org_id;
611
612 EXCEPTION
613 WHEN NO_DATA_FOUND THEN NULL;
614 WHEN OTHERS THEN RAISE;
615 END;
616
617 IF (x_routing_id = 0) THEN
618 x_progress := '096';
619
620 BEGIN
621
622 SELECT NVL(ROUTING_HEADER_ID,0)
623 INTO x_routing_id
624 FROM MTL_INTERORG_PARAMETERS
625 WHERE FROM_ORGANIZATION_ID = x_from_org_id
626 AND TO_ORGANIZATION_ID = x_org_id;
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN NULL;
630 WHEN OTHERS THEN RAISE;
631 END;
632
633 END IF;
634
635 /* Bug# 2194792 - For Internal Orders when Routing is 'Direct',
636 ROUTING_HEADER_ID is null. So default the routing from the
637 Receiving Organization */
638
639 IF (x_routing_id = 0) THEN
640 x_progress := '098';
641
642 BEGIN
643
644 SELECT NVL(RECEIVING_ROUTING_ID,0)
645 INTO x_routing_id
646 FROM RCV_PARAMETERS
647 WHERE 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 /* x_routing_id will be zero if none of the above statements
657 returns a value */
658
659 END IF;
660
661 END IF;
662
663 END IF;
664
665 /* Begin Bug# 3672978 - Routing change in the Manage Shipments form
666 was not considered in the Receiving forms */
667
668 IF (x_receipt_source_code <> 'CUSTOMER') THEN
669 x_progress := '100';
670
671 BEGIN
672 SELECT routing_header_id
673 INTO x_ms_routing_id
674 FROM rcv_shipment_lines
675 WHERE shipment_line_id = x_shipment_line_id;
676 EXCEPTION
677 WHEN NO_DATA_FOUND THEN NULL;
678 END;
679
680 IF x_ms_routing_id is not null THEN
681 x_routing_id := x_ms_routing_id;
682 END IF;
683 END IF;
684
685 /* End Bug# 3555251 */
686
687 X_progress := '105';
688
689
690 /* get latest item revision IF null */
691 IF (x_destination_type_code = 'INVENTORY' AND
692 x_item_id is not null AND
693 x_item_revision is not null AND
694 NVL(x_item_rev_control_flag_to, 'N') = 'Y') THEN
695
696 po_items_sv2.get_latest_item_rev(
697 x_item_id,
698 x_org_id,
699 x_item_revision,
700 p_rev_exists);
701
702 END IF;
703
704 -- Bug 9298154
705 IF (x_deliver_to_sub IS NOT NULL) AND (x_org_id IS NOT NULL) THEN
706 BEGIN
707 X_progress := 106;
708
709 SELECT 'Check to see if subinventory is valid'
710 INTO x_subinv
711 FROM mtl_secondary_inventories
712 WHERE (disable_date IS NULL OR disable_date > SYSDATE)
713 AND organization_id = x_org_id
714 AND secondary_inventory_name = x_deliver_to_sub;
715 EXCEPTION
716 WHEN NO_DATA_FOUND THEN
717 x_deliver_to_sub := '';
718 WHEN OTHERS THEN
719 RAISE;
720 END;
721 END IF;
722 -- End bug 9298154
723
724 X_progress := '110';
725
726 IF (x_destination_type_code = 'INVENTORY' AND
727 x_deliver_to_sub IS NULL) THEN
728
729 po_subinventories_s.get_default_subinventory (
730 x_org_id,
731 x_item_id,
732 x_deliver_to_sub);
733
734 END IF;
735
736 X_progress := '120';
737
738 /*
739 ** Go get the locator control value if the locator control has
740 ** not already been selected or if the subinventory has been
741 ** modified
742 */
743 IF (x_destination_type_code = 'INVENTORY' AND
744 x_locator_control IS NULL AND
745 x_deliver_to_sub IS NOT NULL) THEN
746
747 po_subinventories_s.get_locator_control (
748 x_org_id,
749 x_deliver_to_sub,
750 x_item_id,
751 x_locator_control);
752
753 END IF;
754
755 X_progress := '130';
756
757 IF (x_destination_type_code = 'INVENTORY' AND
758 x_deliver_to_sub IS NOT NULL) THEN
759
760 /*
761 ** get default locator
762 */
763 /*
764 ** Anytime a subinventory is selected then the locator field
765 ** should be prepopulated with the default locator_id from
766 ** mtl_item_loc_defaults for the item, org and subinventory
767 ** and where the default_type = 2
768 */
769
770 po_subinventories_s.get_default_locator (
771 x_org_id,
772 x_item_id,
773 x_deliver_to_sub,
774 x_deliver_to_locator_id);
775
776
777 /* Bug 3537022.
778 * Get locator_id from rcv_shipment_lines for intransit
779 * shipments.
780 */
781 x_progress := 80;
782
783 if (x_receipt_source_code = 'INVENTORY') then
784 select locator_id
785 into x_deliver_to_locator_id
786 from rcv_shipment_lines
787 where shipment_line_id = x_shipment_line_id;
788 end if;
789
790 x_progress := 90;
791
792
793
794 END IF;
795
796 IF (x_destination_type_code = 'INVENTORY') THEN
797
798 X_default_subinventory := x_deliver_to_sub;
799 X_default_locator_id := x_deliver_to_locator_id;
800
801 X_success := rcv_sub_locator_sv.put_away_api (
802 x_line_location_id ,
803 x_po_distributions_id ,
804 x_shipment_line_id ,
805 x_receipt_source_code ,
806 x_org_id ,
807 x_to_organization_id ,
808 x_item_id ,
809 x_item_revision ,
810 x_vendor_id ,
811 x_ship_to_location_id ,
812 x_deliver_to_location_id,
813 x_deliver_to_person_id ,
814 x_available_qty ,
815 x_primary_qty ,
816 x_primary_uom ,
817 x_tolerable_qty ,
818 x_uom ,
819 x_routing_id ,
820 x_default_subinventory ,
821 x_default_locator_id ,
822 x_deliver_to_sub ,
823 x_deliver_to_locator_id);
824
825 X_progress := '132';
826
827 IF (x_receipt_source_code <> 'CUSTOMER') THEN
828
829 IF (X_po_distributions_id IS NOT NULL AND
830 x_deliver_to_locator_id IS NOT NULL) THEN
831
832 X_progress := '133';
833
834 SELECT project_id, task_id
835 INTO X_project_id, X_task_id
836 FROM po_distributions
837 WHERE po_distribution_id = X_po_distributions_id;
838
839 ELSIF (x_req_distribution_id IS NOT NULL AND
840 x_deliver_to_locator_id IS NOT NULL) THEN
841
842 /* Bug 1205660. Locator defaulting for Internal Orders */
843
844 /* Fix for 2444052.
845 Ported the fix as done in Rel 10.7 by adding an exception handler
846 for the following select statement.
847 */
848
849 begin
850 X_progress := '134';
851
852 SELECT project_id, task_id
853 INTO X_project_id, X_task_id
854 FROM po_req_distributions
855 WHERE distribution_id = x_req_distribution_id;
856
857 exception
858 when no_data_found then
859 null;
860 end;
861
862 END IF;
863
864 ELSE
865 /* Locator field defaulting for rma's */
866 X_progress := '135';
867
868 IF (x_oe_order_line_id IS NOT NULL AND
869 x_deliver_to_locator_id IS NOT NULL) THEN
870
871 SELECT project_id, task_id
872 INTO X_project_id,X_task_id
873 FROM oe_order_lines_all
874 WHERE line_id = x_oe_order_line_id;
875
876 END IF;
877
878 END IF;
879 /*
880 ** Set the default values for the locator based on a
881 ** project manufacturing call. If the default locator
882 ** does not have the project and task that is specified
883 ** on the po and the locator control is dynamic then
884 ** project manufacturing will create a new locator row
885 ** copying all values from the existing locator row while
886 ** adding the new project and task is values
887 */
888 /* Bug 1349864 - 25-JUL-2000: GMudgal
889 ** Added a begin-end construct around the PJM call
890 ** We don't really need to default the project
891 ** locators when doing a standard receipt. We need
892 ** to still be able to query up the document even if
893 ** we are unable to default the locators.
894 */
895 IF (X_project_id IS NOT NULL AND
896 -- X_locator_control = 3 AND--for bug 588172 as part of bug 1662321
897 X_deliver_to_sub is not null) THEN
898 begin
899
900 X_progress := '150';
901
902 X_default_locator_id := X_deliver_to_locator_id; -- Bug 2772050
903 PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator(
904 X_to_organization_id,
905 X_default_locator_id, -- Bug 2772050
906 X_project_id,
907 X_task_id,
908 X_deliver_to_locator_id);
909 exception
910 when others then
911 null ;
912 end ;
913
914 END IF;
915
916
917 END IF;
918
919 X_progress := '160';
920
921 /*
922 ** Figure out if any distribution for this shipment has an inventory
923 ** destination. If it does and item is under rev control then the
924 ** item revision must be required. If there are inventory destinations
925 ** but the item is not under item rev control then the rev must be
926 ** disabled. If there are no inventory destinations then it does not
927 ** matter
928 */
929 x_inv_destinations := rcv_transactions_sv.val_if_inventory_destination (
930 x_line_location_id,
931 x_shipment_line_id);
932
933
934 EXCEPTION
935
936 WHEN OTHERS THEN
937 po_message_s.sql_error('post_query', x_progress, sqlcode);
938 RAISE;
939
940 END post_query;
941
942 /* Bug 2668645- Added the following procedure to get the default value for the
943 DFF. This procedure is called in the block level post-query of rcv_transactions block . */
944
945 /* Bug 3775987. Removing the get_dff_default procedure as it is raising
946 * flex errors related to Receiving Transactions DFF and not allowing to
947 * create receipts.
948 **/
949
950 /*===========================================================================
951
952 PROCEDURE NAME: post_query()
953
954 ===========================================================================*/
955 /* bug2730828 */
956 /* This procedure overloads another post_query procedure within this package.
957 This procedure is used by other team and for bug fixes of post_query
958 please make changes to the one that this procedure is calling.
959 */
960
961 PROCEDURE post_query ( x_line_location_id IN NUMBER,
962 x_shipment_line_id IN NUMBER,
963 x_receipt_source_code IN VARCHAR2,
964 x_org_id IN NUMBER,
965 x_item_id IN NUMBER,
966 x_unit_of_measure_class IN VARCHAR2,
967 x_ship_to_location_id IN NUMBER,
968 x_vendor_id IN NUMBER,
969 x_customer_id IN NUMBER,
970 x_item_rev_control_flag_to IN VARCHAR2,
971 x_available_qty IN OUT NOCOPY NUMBER,
972 x_primary_qty IN OUT NOCOPY NUMBER,
973 x_tolerable_qty IN OUT NOCOPY NUMBER,
974 x_uom IN OUT NOCOPY VARCHAR2,
975 x_primary_uom IN OUT NOCOPY VARCHAR2,
976 x_valid_ship_to_location IN OUT NOCOPY BOOLEAN,
977 x_num_of_distributions IN OUT NOCOPY NUMBER,
978 x_po_distributions_id IN OUT NOCOPY NUMBER,
979 x_destination_type_code IN OUT NOCOPY VARCHAR2,
980 x_destination_type_dsp IN OUT NOCOPY VARCHAR2,
981 x_deliver_to_location_id IN OUT NOCOPY NUMBER,
982 x_deliver_to_location IN OUT NOCOPY VARCHAR2,
983 x_deliver_to_person_id IN OUT NOCOPY NUMBER,
984 x_deliver_to_person IN OUT NOCOPY VARCHAR2,
985 x_deliver_to_sub IN OUT NOCOPY VARCHAR2,
986 x_deliver_to_locator_id IN OUT NOCOPY NUMBER,
987 x_wip_entity_id IN OUT NOCOPY NUMBER,
988 x_wip_repetitive_schedule_id IN OUT NOCOPY NUMBER,
989 x_wip_line_id IN OUT NOCOPY NUMBER,
990 x_wip_operation_seq_num IN OUT NOCOPY NUMBER,
991 x_wip_resource_seq_num IN OUT NOCOPY NUMBER,
992 x_bom_resource_id IN OUT NOCOPY NUMBER,
993 x_to_organization_id IN OUT NOCOPY NUMBER,
994 x_job IN OUT NOCOPY VARCHAR2,
995 x_line_num IN OUT NOCOPY VARCHAR2,
996 x_sequence IN OUT NOCOPY NUMBER,
997 x_department IN OUT NOCOPY VARCHAR2,
998 x_enforce_ship_to_loc IN OUT NOCOPY VARCHAR2,
999 x_allow_substitutes IN OUT NOCOPY VARCHAR2,
1000 x_routing_id IN OUT NOCOPY NUMBER,
1001 x_qty_rcv_tolerance IN OUT NOCOPY NUMBER,
1002 x_qty_rcv_exception IN OUT NOCOPY VARCHAR2,
1003 x_days_early_receipt IN OUT NOCOPY NUMBER,
1004 x_days_late_receipt IN OUT NOCOPY NUMBER,
1005 x_rcv_days_exception IN OUT NOCOPY VARCHAR2,
1006 x_item_revision IN OUT NOCOPY VARCHAR2,
1007 x_locator_control IN OUT NOCOPY NUMBER,
1008 x_inv_destinations IN OUT NOCOPY BOOLEAN,
1009 x_rate IN OUT NOCOPY NUMBER,
1010 x_rate_date IN OUT NOCOPY DATE,
1011 x_asn_type IN VARCHAR2,
1012 x_oe_order_header_id IN NUMBER,
1013 x_oe_order_line_id IN NUMBER,
1014 x_from_org_id IN NUMBER DEFAULT NULL) IS
1015 /* create dummy variables just to store throw-away values */
1016 l_dummy1 MTL_KANBAN_CARDS.kanban_card_number%TYPE;
1017 l_dummy2 PJM_PROJECTS_ALL_V.project_number%TYPE;
1018 l_dummy3 PA_TASKS_EXPEND_V.task_number%TYPE;
1019 l_dummy4 GL_CODE_COMBINATIONS_KFV.concatenated_segments%TYPE;
1020
1021 BEGIN
1022 RCV_RECEIPTS_QUERY_SV.post_query(
1023 x_line_location_id,
1024 x_shipment_line_id,
1025 x_receipt_source_code,
1026 x_org_id,
1027 x_item_id,
1028 x_unit_of_measure_class,
1029 x_ship_to_location_id,
1030 x_vendor_id,
1031 x_customer_id,
1032 x_item_rev_control_flag_to,
1033 x_available_qty,
1034 x_primary_qty,
1035 x_tolerable_qty,
1036 x_uom,
1037 x_primary_uom,
1038 x_valid_ship_to_location,
1039 x_num_of_distributions,
1040 x_po_distributions_id,
1041 x_destination_type_code,
1042 x_destination_type_dsp,
1043 x_deliver_to_location_id,
1044 x_deliver_to_location,
1045 x_deliver_to_person_id,
1046 x_deliver_to_person,
1047 x_deliver_to_sub,
1048 x_deliver_to_locator_id,
1049 x_wip_entity_id,
1050 x_wip_repetitive_schedule_id,
1051 x_wip_line_id,
1052 x_wip_operation_seq_num,
1053 x_wip_resource_seq_num,
1054 x_bom_resource_id,
1055 x_to_organization_id,
1056 x_job,
1057 x_line_num,
1058 x_sequence,
1059 x_department,
1060 x_enforce_ship_to_loc,
1061 x_allow_substitutes,
1062 x_routing_id,
1063 x_qty_rcv_tolerance,
1064 x_qty_rcv_exception,
1065 x_days_early_receipt,
1066 x_days_late_receipt,
1067 x_rcv_days_exception,
1068 x_item_revision,
1069 x_locator_control,
1070 x_inv_destinations,
1071 x_rate,
1072 x_rate_date,
1073 x_asn_type,
1074 x_oe_order_header_id,
1075 x_oe_order_line_id,
1076 x_from_org_id,
1077 l_dummy1,
1078 l_dummy2,
1079 l_dummy3,
1080 l_dummy4);
1081
1082 END post_query;
1083
1084 -- <ENT RCPT PERF FPI START>
1085
1086
1087 /**
1088 * Public Procedure: exec_dynamic_sql
1089 * Requires: the number of records in p_val has to match the number of
1090 * bind variables in p_query, which is a sql string
1091 * the sql to execute should be a clause that check
1092 * existence only
1093 * The number of bind variables should also be less than or equal
1094 * to 7 unless modified.
1095 * Modifies: None
1096 * Effects: Executes a dynamic sql defined in p_query. the value of bind
1097 * variables will be defined in p_val
1098 * Returns:
1099 * x_exist - FND_API.G_TRUE if sql is executed successfully without any error
1100 * FND_API.G_FALSE if sql returns NO_DATA_FOUND
1101 * The procedure raises an exception if p_query returns an error other than
1102 * NO_DATA_FOUND
1103 */
1104
1105 PROCEDURE exec_dynamic_sql(p_query IN VARCHAR2,
1106 p_val IN RCV_RECEIPTS_QUERY_SV.NUM_TBL_TYPE,
1107 x_exist OUT NOCOPY VARCHAR2) IS
1108 l_tmp NUMBER;
1109 l_progress VARCHAR2(3) := '000';
1110 l_exception EXCEPTION;
1111 l_num_bind NUMBER;
1112 BEGIN
1113
1114 l_num_bind := p_val.COUNT;
1115
1116 IF (l_num_bind = 0) THEN
1117 l_progress := '000';
1118 EXECUTE IMMEDIATE p_query INTO l_tmp;
1119
1120 ELSIF (l_num_bind = 1) THEN
1121 l_progress := '010';
1122 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1);
1123
1124 ELSIF (l_num_bind = 2) THEN
1125 l_progress := '020';
1126 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2);
1127
1128 ELSIF (l_num_bind = 3) THEN
1129 l_progress := '030';
1130 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3);
1131
1132 ELSIF (l_num_bind = 4) THEN
1133 l_progress := '040';
1134 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1135 p_val(4);
1136
1137 ELSIF (l_num_bind = 5) THEN
1138 l_progress := '050';
1139 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1140 p_val(4), p_val(5);
1141
1142 ELSIF (l_num_bind = 6) THEN
1143 l_progress := '060';
1144 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1145 p_val(4), p_val(5), p_val(6);
1146
1147 ELSIF (l_num_bind = 7) THEN
1148 l_progress := '070';
1149 EXECUTE IMMEDIATE p_query INTO l_tmp USING p_val(1), p_val(2), p_val(3),
1150 p_val(4), p_val(5), p_val(6),
1151 p_val(7);
1152 ELSE
1153 l_progress := '080';
1154 RAISE l_exception;
1155 END IF;
1156
1157 x_exist := FND_API.G_TRUE;
1158 EXCEPTION
1159 WHEN NO_DATA_FOUND THEN
1160 x_exist := FND_API.G_FALSE;
1161 WHEN OTHERS THEN
1162 PO_MESSAGE_S.sql_error('exec_dynamic_sql', l_progress, sqlcode);
1163 RAISE;
1164 END exec_dynamic_sql;
1165
1166
1167 -- <ENT RCPT PERF FPI END>
1168
1169 END RCV_RECEIPTS_QUERY_SV;
[Home] [Help]