1 PACKAGE BODY RCV_RETURN_SV AS
2 /* $Header: RCVTXREB.pls 120.2 2010/07/09 23:21:47 vthevark ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: post_query()
7
8 ===========================================================================*/
9
10 PROCEDURE POST_QUERY ( x_transaction_id IN NUMBER,
11 x_parent_transaction_type IN VARCHAR2,
12 x_destination_type_code IN VARCHAR2,
13 x_organization_id IN NUMBER,
14 x_wip_entity_id IN NUMBER,
15 x_wip_repetitive_schd_id IN NUMBER,
16 x_wip_operation_seq_num IN NUMBER,
17 x_wip_resource_seq_num IN NUMBER,
18 x_wip_line_id IN NUMBER,
19 x_hazard_class_id IN NUMBER,
20 x_un_number_id IN NUMBER,
21 x_primary_uom IN VARCHAR2,
22 x_transaction_uom IN VARCHAR2,
23 x_primary_transaction_qty IN NUMBER,
24 x_item_id IN NUMBER,
25 x_final_location_id IN NUMBER,
26 x_receiving_location_id IN NUMBER,
27 x_deliver_to_person_id IN NUMBER,
28 x_vendor_id IN NUMBER,
29 x_subinventory IN VARCHAR2,
30
31 x_source_document_code IN VARCHAR2,
32 --x_inspection_status_code IN VARCHAR2,
33 x_secondary_ordered_uom IN VARCHAR2,
34 x_lpn_id IN VARCHAR2,
35 x_transfer_lpn_id IN VARCHAR2,
36 x_po_type_code IN VARCHAR2,
37 x_ordered_uom IN VARCHAR2,
38 x_customer_id IN VARCHAR2,
39
40 x_subinv_locator_type OUT NOCOPY VARCHAR2,
41 x_final_location OUT NOCOPY VARCHAR2,
42 x_receiving_location OUT NOCOPY VARCHAR2,
43 x_person OUT NOCOPY VARCHAR2,
44 x_supply_qty OUT NOCOPY NUMBER,
45 x_wip_entity_name OUT NOCOPY VARCHAR2,
46 x_operation_seq_num OUT NOCOPY VARCHAR2,
47 x_department_code OUT NOCOPY VARCHAR2,
48 x_line_code OUT NOCOPY VARCHAR2,
49 x_hazard_class OUT NOCOPY VARCHAR2,
50 x_un_number OUT NOCOPY VARCHAR2,
51 x_vendor_name OUT NOCOPY VARCHAR2,
52
53 x_parent_transaction_type_dsp OUT NOCOPY VARCHAR2,
54 --x_inspection_status_dsp OUT NOCOPY VARCHAR2,
55 x_destination_type_dsp OUT NOCOPY VARCHAR2,
56 --x_primary_uom_class OUT NOCOPY VARCHAR2,
57 x_transaction_uom_class OUT NOCOPY VARCHAR2,
58 x_secondary_ordered_uom_out OUT NOCOPY VARCHAR2,
59 x_license_plate_number OUT NOCOPY VARCHAR2,
60 x_transfer_license_plate_num OUT NOCOPY VARCHAR2,
61 x_order_type OUT NOCOPY VARCHAR2,
62 x_ordered_uom_out OUT NOCOPY VARCHAR2,
63 x_customer OUT NOCOPY VARCHAR2
64 ) is
65
66 x_progress VARCHAR2(3) := NULL;
67 x_primary_child_qty NUMBER;
68 x_primary_child_qty_correct NUMBER;
69 x_primary_interface_qty NUMBER;
70 x_primary_supply_qty NUMBER;
71 x_primary_qty NUMBER;
72
73 cursor get_po_lookup_code(p_lookup_type in varchar2,p_lookup_code in varchar2) is
74 select displayed_field
75 from po_lookup_codes
76 where lookup_type = p_lookup_type
77 and lookup_code = p_lookup_code
78 AND ROWNUM<=1;
79
80 cursor get_uom_class(p_uom in varchar2) is
81 select uom_class
82 from mtl_units_of_measure
83 where unit_of_measure = p_uom
84 AND ROWNUM<=1;
85
86 cursor get_uom(p_uom_code in varchar2) is
87 select unit_of_measure
88 from mtl_units_of_measure
89 where uom_code = p_uom_code
90 AND ROWNUM<=1;
91
92 /* Changed for Bug 6761395
93 * Replaced Substr with Substrb to handle the conversion of bytes
94 * into char in different languages.
95 */
96 cursor get_customer(p_customer_id in varchar2) is
97 select SUBSTRB(HZP.PARTY_NAME,1,50)
98 from HZ_CUST_ACCOUNTS HZCA,
99 HZ_PARTIES HZP
100 where HZCA.CUST_ACCOUNT_ID = p_customer_id
101 and HZCA.PARTY_ID = HZP.PARTY_ID
102 AND ROWNUM<=1;
103
104 cursor get_license_plate_number(p_license_plate_id in varchar2) is
105 select LICENSE_PLATE_NUMBER
106 from WMS_LICENSE_PLATE_NUMBERS
107 where LPN_ID = p_license_plate_id
108 AND ROWNUM<=1;
109
110 x_create_shipment_flag VARCHAR2(1) := NVL(FND_PROFILE.VALUE('RCV_CREATE_SHIPMENT_FOR_RETURNS'),'N'); -- rtv project
111 BEGIN
112
113 if (x_parent_transaction_type is not null) then
114 open get_po_lookup_code('RCV TRANSACTION TYPE',x_parent_transaction_type);
115 fetch get_po_lookup_code into x_parent_transaction_type_dsp;
116 close get_po_lookup_code;
117 end if;
118
119 /*
120 if (x_inspection_status_code is not null) then
121 open get_po_lookup_code('INSPECTION STATUS',x_inspection_status_code);
122 fetch get_po_lookup_code into x_inspection_status_dsp;
123 close get_po_lookup_code;
124 end if;
125 */
126
127 if (x_destination_type_code is not null) then
128 open get_po_lookup_code('RCV DESTINATION TYPE',x_destination_type_code);
129 fetch get_po_lookup_code into x_destination_type_dsp;
130 close get_po_lookup_code;
131 end if;
132
133 /*
134 if (x_primary_uom is not null) then
135 open get_uom_class(x_primary_uom);
136 fetch get_uom_class into x_primary_uom_class;
137 close get_uom_class;
138 end if;
139 */
140
141 if (x_transaction_uom is not null) then
142 open get_uom_class(x_transaction_uom);
143 fetch get_uom_class into x_transaction_uom_class;
144 close get_uom_class;
145 end if;
146
147 if (x_lpn_id is not null) then
148 open get_license_plate_number(x_lpn_id);
149 fetch get_license_plate_number into x_license_plate_number;
150 close get_license_plate_number;
151 end if;
152
153 if (x_transfer_lpn_id is not null) then
154 open get_license_plate_number(x_transfer_lpn_id);
155 fetch get_license_plate_number into x_transfer_license_plate_num;
156 close get_license_plate_number;
157 end if;
158
159 if (x_source_document_code = 'PO') then
160
161 if (x_po_type_code is not null) then
162 if (x_parent_transaction_type = 'UNORDERED') then
163 open get_po_lookup_code('PO TYPE','STANDARD');
164 else
165 open get_po_lookup_code('PO TYPE',x_po_type_code);
166 end if;
167 fetch get_po_lookup_code into x_order_type;
168 close get_po_lookup_code;
169 end if;
170
171 /* do not overwrite p_order_uom for PO */
172 x_ordered_uom_out := x_ordered_uom;
173
174 /* do not overwrite p_secondary_ordered_uom */
175 x_secondary_ordered_uom_out := x_secondary_ordered_uom;
176
177 /* leave customer null for PO */
178 x_customer := null;
179
180 else --x_source_document_type = 'RMA'
181
182 /* do not overwrite x_order_type for RMA */
183 x_order_type := x_po_type_code;
184
185 if (x_ordered_uom is not null) then
186 open get_uom(x_ordered_uom);
187 fetch get_uom into x_ordered_uom_out;
188 close get_uom;
189 end if;
190
191 if (x_secondary_ordered_uom is not null) then
192 open get_uom(x_secondary_ordered_uom);
193 fetch get_uom into x_secondary_ordered_uom_out;
194 close get_uom;
195 end if;
196
197 if (x_customer_id is not null) then
198 open get_customer(x_customer_id);
199 fetch get_customer into x_customer;
200 close get_customer;
201 end if;
202
203 end if; --x_source_document_type = 'PO'
204
205 /*
206 ** Based on the transaction type, get maximum supply qty agst each
207 ** transaction
208 ** Should be using Sanjay's RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY
209 ** package, but for the time being calling our own logic
210 */
211
212 if x_parent_transaction_type = 'DELIVER' then
213
214 x_progress := 10;
215 /*
216 ** Sum the total of the children transactions that are not corrections.
217 */
218 SELECT nvl(sum(rt.primary_quantity), 0)
219 INTO x_primary_child_qty
220 FROM rcv_transactions rt
221 WHERE rt.parent_transaction_id = x_transaction_id
222 AND rt.transaction_type <> 'CORRECT';
223
224 x_progress := 20;
225 /*
226 ** Sum the total of the children transactions that are corrections.
227 */
228 SELECT nvl(sum(rt.primary_quantity), 0)
229 INTO x_primary_child_qty_correct
230 FROM rcv_transactions rt
231 WHERE rt.parent_transaction_id = x_transaction_id
232 AND rt.transaction_type = 'CORRECT';
233
234 x_progress := 30;
235 /*
236 ** must hit this table for any unprocessed transactions
237 */
238 SELECT nvl(sum(decode(rti.transaction_type,'CORRECT',
239 rti.primary_quantity * -1,
240 rti.primary_quantity )
241 ),0)
242 INTO x_primary_interface_qty
243 FROM rcv_transactions_interface rti
244 WHERE rti.parent_transaction_id = x_transaction_id
245 AND rti.processing_status_code in ('PENDING' , 'WSH_INTERFACED') -- rtv vidya
246 AND rti.transaction_status_code = 'PENDING'
247 -- rtv project : start
248 AND NOT EXISTS (select 1 from wsh_delivery_details wdd
249 where wdd.delivery_detail_id = rti.interface_source_line_id
250 and wdd.source_code = 'RTV'
251 and rti.transaction_type = 'RETURN TO VENDOR'
252 and rti.processing_status_code = 'PENDING');
253 -- rtv project : end
254 /*
255 ** Modified on 09-20-1995 based on talk with George Kellner
256
257 AND rti.transaction_type NOT IN
258 ('RETURN TO VENDOR', 'RETURN TO RECEIVING');
259 */
260
261 x_progress := 40;
262 /*
263 ** Calculate the final primary quantity
264 */
265 x_primary_qty := x_primary_transaction_qty - x_primary_child_qty
266 + x_primary_child_qty_correct
267 - x_primary_interface_qty;
268
269 else
270
271 x_progress := 50;
272 /*
273 ** Determine the receiving supply for the current transaction.
274 */
275 SELECT nvl(rs.to_org_primary_quantity,0)
276 INTO x_primary_supply_qty
277 FROM rcv_supply rs
278 WHERE rs.rcv_transaction_id = x_transaction_id;
279
280 x_progress := 60;
281 /*
282 ** Determine the sum of the transactions in the interface table
283 */
284 SELECT nvl(sum(rti.primary_quantity),0)
285 INTO x_primary_interface_qty
286 FROM rcv_transactions_interface rti
287 WHERE rti.parent_transaction_id = x_transaction_id
288 AND rti.processing_status_code = 'PENDING'
289 AND rti.transaction_status_code = 'PENDING';
290 /*
291 ** Modified on 09-20-1995 based on talk with G.Kellner
292 AND rti.transaction_type NOT IN
293 ('RETURN TO VENDOR', 'RETURN TO RECEIVING');
294 */
295
296 /*
297 ** Reduce supply by the quantity in the interface table. This is
298 ** the quantity that has not been transacted but should be taken
299 ** into account during the return.
300 */
301 x_progress := 70;
302 x_primary_qty := x_primary_supply_qty - x_primary_interface_qty;
303
304 end if; /* End of transaction type */
305
306 /*
307 ** Return the available qty in transaction UOM
308 */
309
310 /* Bug2794344 Uom_convert call is made only if primary uom is different
311 from transaction uom to avoid quantity rounding problems
312 */
313 if nvl(x_primary_qty,0) > 0 then
314 x_progress := 75;
315 if (x_primary_uom <> x_transaction_uom ) then
316 PO_UOM_S.UOM_CONVERT ( x_primary_qty,
317 x_primary_uom,
318 x_item_id,
319 x_transaction_uom,
320 x_supply_qty);
321 else
322 x_supply_qty :=x_primary_qty;
323 end if;
324 end if;
325
326 /*
327 ** Check if destination type code = 'SHOP FLOOR'
328 ** If shop floor, then retreive outside_processing details
329 */
330 if x_destination_type_code = 'SHOP FLOOR' then
331
332 x_progress := 80;
333 --Bug# 2000013 togeorge 09/18/2001
334 --Eam: Split the following sql to 3 different sqls because eAM w/o would
335 -- not have resource information and this sql will fail.
336 /*
337 select we.wip_entity_name,
338 wn.operation_seq_num,
339 bd.department_code
340 into x_wip_entity_name,
341 x_operation_seq_num,
342 x_department_code
343 from wip_entities we,
344 bom_departments bd,
345 wip_operation_resources wr,
346 wip_operations wn,
347 wip_operations wo
348 where wo.wip_entity_id = x_wip_entity_id
349 and wo.organization_id = x_organization_id
350 and nvl(wo.repetitive_schedule_id, -1) =
351 nvl(x_wip_repetitive_schd_id,-1)
352 and wo.operation_seq_num = x_wip_operation_seq_num
353 and wr.wip_entity_id = x_wip_entity_id
354 and wr.organization_id = x_organization_id
355 and nvl(wr.repetitive_schedule_id, -1) =
356 nvl(x_wip_repetitive_schd_id,-1)
357 and wr.operation_seq_num = x_wip_operation_seq_num
358 and wr.resource_seq_num = x_wip_resource_seq_num
359 and wn.wip_entity_id = x_wip_entity_id
360 and wn.organization_id = x_organization_id
361 and nvl(wn.repetitive_schedule_id, -1) =
362 nvl(x_wip_repetitive_schd_id,-1)
363 and wn.operation_seq_num =
364 decode(wr.autocharge_type,
365 4, nvl(wo.next_operation_seq_num, wo.operation_seq_num),
366 wo.operation_seq_num)
367 and bd.department_id = wn.department_id
368 and we.wip_entity_id = x_wip_entity_id
369 and we.organization_id = x_organization_id;
370 */
371
372 if X_wip_entity_id is not null then
373 x_progress := 81;
374 begin
375 SELECT we.wip_entity_name job
376 INTO x_wip_entity_name
377 FROM wip_entities we
378 WHERE we.wip_entity_id = x_wip_entity_id
379 AND we.organization_id = x_organization_id;
380 exception
381 when others then
382 x_wip_entity_name := null;
383 x_progress := 82;
384 end;
385 end if;
386
387 if x_wip_entity_id is not null and x_wip_operation_seq_num is not null then
388 x_progress := 83;
389 begin
390 SELECT wn.operation_seq_num sequence,
391 bd.department_code department
392 INTO x_operation_seq_num, x_department_code
393 FROM bom_departments bd,
394 wip_operation_resources wr,
395 wip_operations wn,
396 wip_operations wo
397 WHERE wo.wip_entity_id = x_wip_entity_id
398 AND wo.organization_id = x_organization_id
399 AND nvl(wo.repetitive_schedule_id, -1) =
400 nvl(x_wip_repetitive_schd_id, -1)
401 AND wo.operation_seq_num = x_wip_operation_seq_num
402 AND wr.wip_entity_id = x_wip_entity_id
403 AND wr.organization_id = x_organization_id
404 AND nvl(wr.repetitive_schedule_id, -1) =
405 nvl(x_wip_repetitive_schd_id, -1)
406 AND wr.operation_seq_num = x_wip_operation_seq_num
407 AND wr.resource_seq_num = x_wip_resource_seq_num
408 AND wn.wip_entity_id = x_wip_entity_id
409 AND wn.organization_id = x_organization_id
410 AND nvl(wn.repetitive_schedule_id, -1) =
411 nvl(x_wip_repetitive_schd_id, -1)
412 AND wn.operation_seq_num =
413 decode(wr.autocharge_type, 4,
414 nvl(wo.next_operation_seq_num, wo.operation_seq_num),
415 wo.operation_seq_num)
416 AND bd.department_id = wn.department_id;
417 exception
418 when no_data_found then
419 --for EAM workorders the above sql would raise no_data_found.
420 --find department code and sequence with out touching resource table.
421 x_progress := 84;
422 begin
423 select bd.department_code department
424 into X_department_code
425 from bom_departments bd,wip_operations wn
426 where wn.wip_entity_id = x_wip_entity_id
427 and wn.organization_id = x_organization_id
428 and nvl(wn.repetitive_schedule_id, -1) =
429 nvl(x_wip_repetitive_schd_id, -1)
430 and bd.department_id = wn.department_id;
431 exception
432 when others then
433 x_department_code :=null;
434 x_progress := 85;
435 end;
436
437 begin
438 SELECT wo.operation_seq_num sequence
439 INTO x_operation_seq_num
440 FROM wip_operations wo
441 WHERE wo.wip_entity_id = x_wip_entity_id
442 AND wo.organization_id = x_organization_id
443 AND nvl(wo.repetitive_schedule_id, -1) =
444 nvl(x_wip_repetitive_schd_id, -1)
445 AND wo.operation_seq_num = x_wip_operation_seq_num;
446 exception
447 when others then
448 x_operation_seq_num := null;
449 x_progress := 86;
450 end;
451 when others then
452 x_operation_seq_num := null;
453 x_department_code :=null;
454 x_progress := 87;
455 end;
456 end if;
457 --
458
459 end if;
460
461 if (NVL(x_wip_line_id, -1) <> -1) then
462 x_progress := 90;
463
464 SELECT line_code
465 INTO x_line_code
466 FROM wip_lines
467 WHERE line_id = x_wip_line_id
468 AND organization_id = x_organization_id;
469 end if;
470
471 /*
472 ** Get the hazard class information if the hazard class id is
473 ** not null
474 */
475
476 IF (x_hazard_class_id is NOT NULL) THEN
477
478 x_progress := 100;
479
480 SELECT hazard_class
481 INTO x_hazard_class
482 FROM po_hazard_classes
483 WHERE hazard_class_id = x_hazard_class_id;
484
485 END IF;
486
487 /*
488 ** Get the UN Number info if the un number id is not null
489 */
490
491 IF (x_un_number_id is NOT NULL) THEN
492
493 x_progress := 110;
494
495 SELECT un_number
496 INTO x_un_number
497 FROM po_un_numbers
498 WHERE un_number_id = x_un_number_id;
499
500 END IF;
501
502 /*
503 ** Depending on the destination type code, get the appropraite values
504 */
505 if NVL(x_receiving_location_id, 0) <> 0 then
506 begin
507 x_progress := 120;
508 select location_code
509 into x_receiving_location
510 from hr_locations
511 where location_id = x_receiving_location_id;
512 --Bug#2253273. Added this exception as the hr_locations view which
513 --was a join of hr_locations_all and hz_locations has been changed.
514 --Now hr_locations dose not contain hz_locations table.So added the
515 --following condition to take care of the RMA.
516 exception
517 WHEN NO_DATA_FOUND then
518 PO_SHIPMENTS_SV2.get_drop_ship_cust_locations(x_receiving_location_id,
519 x_receiving_location);
520 end;
521 end if;
522
523 if NVL(x_final_location_id,0) <> 0 then
524 begin
525 x_progress := 130;
526 select location_code
527 into x_final_location
528 from hr_locations
529 where location_id = x_final_location_id;
530 --Bug#2253273.
531 exception
532 WHEN NO_DATA_FOUND then
533 PO_SHIPMENTS_SV2.get_drop_ship_cust_locations(x_final_location_id,
534 x_final_location);
535 end;
536 end if;
537 /*Bug 2713129 hr_employees view doesn't contain terminated employees.Hence
538 when quering the records in returns form if the deliver_to_person corresponding
539 to a record is terminated the sql below is returning no data found Expection.
540 Returns form should even show the records whose deliver to person
541 is terminated.Hence getting the employee name from per_all_people_f.
542 */
543 if x_destination_type_code in ('EXPENSE','SHOP FLOOR','INVENTORY') then
544
545 if NVL(x_deliver_to_person_id,0) <> 0 then
546 x_progress := 140;
547 x_person := po_inq_sv.get_person_name(x_deliver_to_person_id);
548 /*
549 select full_name
550 into x_person
551 from hr_employees
552 where employee_id = x_deliver_to_person_id;
553 */
554 end if;
555 end if;
556
557 if x_destination_type_code = 'INVENTORY' then
558 -- rtv project
559 if (x_source_document_code = 'PO' AND
560 x_create_shipment_flag = 'Y' AND
561 x_subinventory IS NULL) THEN
562 x_progress := 143;
563 else
564 x_progress := 145;
565 select locator_type
566 into x_subinv_locator_type
567 from mtl_secondary_inventories
568 where organization_id = x_organization_id
569 and secondary_inventory_name = x_subinventory;
570 end if;
571 end if;
572
573 if x_vendor_id is not null then
574 x_progress := 150;
575 SELECT v.vendor_name
576 INTO x_vendor_name
577 from po_vendors v
578 where v.vendor_id = x_vendor_id;
579 end if;
580
581 EXCEPTION
582
583 WHEN OTHERS THEN
584 po_message_s.sql_error('post_query', x_progress, sqlcode);
585 RAISE;
586
587 END post_query;
588
589
590 END RCV_RETURN_SV;
[Home] [Help]