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