1 PACKAGE BODY PO_INQ_SV as
2 /* $Header: POXPOVPB.pls 120.11.12020000.3 2013/02/10 13:29:57 vegajula ship $*/
3
4 --<HTML Agreement R12 Start>
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_INQ_SV';
6 G_LOG_HEAD CONSTANT VARCHAR2(50) := 'po.plsql.'||G_PKG_NAME||'.';
7 G_DEBUG_STMT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
8 G_DEBUG_UNEXP BOOLEAN := PO_DEBUG.is_debug_unexp_on;
9 --<HTML Agreement R12 End>
10
11 /*============================= PO_INQ_SV ===============================*/
12 /* Local procedure */
13 PROCEDURE get_lookup_dsp(p_lookup_type IN PO_LOOKUP_CODES.lookup_type%TYPE,
14 p_lookup_code IN PO_LOOKUP_CODES.lookup_code%TYPE,
15 x_displayed_field OUT NOCOPY PO_LOOKUP_CODES.displayed_field%TYPE);
16
17 /*===========================================================================
18
19 PROCEDURE NAME: get_action_history_values()
20
21 ===========================================================================*/
22
23 PROCEDURE get_action_history_values (x_object_id IN NUMBER,
24 x_object_type_code IN VARCHAR2,
25 x_subtype_code IN OUT NOCOPY VARCHAR2,
26 x_type_name OUT NOCOPY VARCHAR2,
27 x_document_number OUT NOCOPY VARCHAR2,
28 x_preparer_id OUT NOCOPY NUMBER,
29 x_security_level OUT NOCOPY VARCHAR2,
30 x_security_hierarchy_id OUT NOCOPY NUMBER) is
31
32 x_progress VARCHAR2(3) := '';
33 l_org_id number; -- Bug 11782280
34 BEGIN
35 x_progress := '010';
36
37 BEGIN
38 if x_object_type_code = 'REQUISITION' then
39
40 select org_id
41 into l_org_id
42 from PO_REQUISITION_HEADERS_ALL
43 where requisition_header_id = x_object_id;
44
45 /* bug 12654669 : BPA's and CPA's have object_code as PA. Adding same to below condition
46 Regression due to bug 11782280*/
47 elsif x_object_type_code in ('PO','PA') then
48
49 -- Bug 11782280 start
50 select org_id
51 into l_org_id
52 from po_headers_all
53 where po_header_id = x_object_id;
54
55 elsif x_object_type_code in ('RELEASE') then
56
57 select org_id
58 into l_org_id
59 from po_releases_all
60 where po_release_id = x_object_id;
61
62 end if;
63
64 EXCEPTION
65
66 when no_data_found then
67 null;
68 end;
69
70 -- Bug 11782280
71 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
72
73 -- Bug 11782280 End
74
75 /* Get the subtype and preparer */
76
77 SELECT subtype,
78 preparer_id,
79 type_name,
80 document_number
81 INTO x_subtype_code,
82 x_preparer_id,
83 x_type_name,
84 x_document_number
85 FROM PO_DOCUMENT_HEADERS_VIEW
86 WHERE document_id = x_object_id
87 AND type_code = x_object_type_code;
88
89
90 x_progress := '020';
91
92
93 /* Get the security_level */
94
95 SELECT podt.security_level_code
96 INTO x_security_level
97 FROM po_document_types podt
98 WHERE podt.document_subtype = x_subtype_code
99 AND podt.document_type_code = x_object_type_code;
100
101 x_progress := '030';
102
103 /* Get the security_hierarchy_id */
104
105
106 SELECT psp.security_position_structure_id
107 INTO x_security_hierarchy_id
108 FROM po_system_parameters psp;
109
110
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 -- dbms_output.put_line('In exception');
115 po_message_s.sql_error('get_action_history_values', x_progress, sqlcode);
116 raise;
117
118 END get_action_history_values;
119
120
121 /*===========================================================================
122
123 PROCEDURE NAME: get_po_doc_access_sec_level()
124
125 ===========================================================================*/
126
127 PROCEDURE get_po_doc_access_sec_level(x_standard_security OUT NOCOPY VARCHAR2,
128 x_blanket_security OUT NOCOPY VARCHAR2,
129 x_contract_security OUT NOCOPY VARCHAR2,
130 x_planned_security OUT NOCOPY VARCHAR2,
131 x_blanket_rel_security OUT NOCOPY VARCHAR2,
132 x_scheduled_rel_security OUT NOCOPY VARCHAR2,
133 x_security_hierarchy_id OUT NOCOPY NUMBER)
134
135 is
136
137 /* Start Bug 3336172 */
138 TYPE g_sec_level_tbl_type IS TABLE OF po_document_types_b.security_level_code%TYPE;
139 TYPE g_doc_type_code_tbl_type IS TABLE OF po_document_types_b.document_type_code%TYPE;
140 TYPE g_doc_subtype_tbl_type IS TABLE OF po_document_types_b.document_subtype%TYPE;
141
142 l_sec_level_tbl g_sec_level_tbl_type;
143 l_doc_type_code_tbl g_doc_type_code_tbl_type;
144 l_doc_subtype_tbl g_doc_subtype_tbl_type;
145
146 /* End Bug 3331672 */
147
148 x_progress VARCHAR2(3) := '';
149
150
151 BEGIN
152 x_progress := '010';
153
154 /* Get the access and security level codes for all the four types of
155 * Purchase Orders
156 */
157
158 /* Start Bug 3336172 - Improving performance of the query below by:
159 * A. Removing the 6 way cartesian join all on the same table.
160 * Instead, use a PL-SQL table variable to store the data
161 * we need, and then loop through it. This should avoid the
162 * massive join while also avoiding 6 PL/SQL to SQL context switches.
163 * B. Use the PO_DOCUMENT_TYPES_B base view instead of PO_DOCUMENT_TYPES
164 * There is no need for translation in this code, so the base view, which
165 * does not join with a translation table is a better choice.
166 * Note: we use three PL-SQL tables, as in 8I DB, you cannot bulk collect
167 * into a table of records! In 9I, that is allowed, but for now,
168 * the code must also be compatible with 8I.
169 */
170
171 SELECT security_level_code, document_type_code, document_subtype
172 BULK COLLECT into l_sec_level_tbl, l_doc_type_code_tbl, l_doc_subtype_tbl
173 FROM po_document_types_b
174 WHERE (document_type_code = 'PO' and document_subtype = 'STANDARD')
175 or (document_type_code = 'PA' and document_subtype = 'BLANKET')
176 or (document_type_code = 'PA' and document_subtype = 'CONTRACT')
177 or (document_type_code = 'PO' and document_subtype = 'PLANNED')
178 or (document_type_code = 'RELEASE' and document_subtype = 'BLANKET')
179 or (document_type_code = 'RELEASE' and document_subtype = 'SCHEDULED')
180 ;
181
182 FOR i IN 1..l_sec_level_tbl.COUNT
183 LOOP
184 IF (l_doc_type_code_tbl(i) = 'PO' and l_doc_subtype_tbl(i) = 'STANDARD')
185 THEN x_standard_security := l_sec_level_tbl(i);
186 ELSIF (l_doc_type_code_tbl(i) = 'PA' and l_doc_subtype_tbl(i) = 'BLANKET')
187 THEN x_blanket_security := l_sec_level_tbl(i);
188 ELSIF (l_doc_type_code_tbl(i) = 'PA' and l_doc_subtype_tbl(i) = 'CONTRACT')
189 THEN x_contract_security := l_sec_level_tbl(i);
190 ELSIF (l_doc_type_code_tbl(i) = 'PO' and l_doc_subtype_tbl(i) = 'PLANNED')
191 THEN x_planned_security := l_sec_level_tbl(i);
192 ELSIF (l_doc_type_code_tbl(i) = 'RELEASE' and l_doc_subtype_tbl(i) = 'BLANKET')
193 THEN x_blanket_rel_security := l_sec_level_tbl(i);
194 ELSIF (l_doc_type_code_tbl(i) = 'RELEASE' and l_doc_subtype_tbl(i) = 'SCHEDULED')
195 THEN x_scheduled_rel_security := l_sec_level_tbl(i);
196 END IF;
197 END LOOP;
198
199 /* End Bug 3336172 */
200
201
202 x_progress := '020';
203
204 /* Get the security_hierarchy_id */
205
206
207 SELECT psp.security_position_structure_id
208 INTO x_security_hierarchy_id
209 FROM po_system_parameters psp;
210
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 -- dbms_output.put_line('In exception');
215 po_message_s.sql_error('get_po_doc_access_sec_level', x_progress, sqlcode);
216 raise;
217
218 END get_po_doc_access_sec_level;
219
220
221
222 /*===========================================================================
223
224 FUNCTION NAME: get_active_enc_amount()
225
226 ===========================================================================*/
227
228 FUNCTION get_active_enc_amount(x_rate IN NUMBER,
229 x_enc_amount IN NUMBER,
230 x_shipment_type IN VARCHAR2,
231 x_po_distribution_id IN NUMBER)
232 RETURN NUMBER is
233
234 -- <Encumbrance FPJ START>
235 -- refactored entire procedure to remove duplicate logic
236 -- and call the new get_active_encumbrance_amount API instead
237
238 l_return_status VARCHAR2(1);
239 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
240 l_progress VARCHAR2(3);
241 x_active_enc_amount NUMBER;
242
243 BEGIN
244
245 l_progress := '000';
246
247 -- <Complex Work R12>: Add PREPAYMENT shipment type
248
249 IF (x_shipment_type IN ('STANDARD', 'PLANNED', 'PREPAYMENT')) THEN
250 l_progress := '010';
251 l_doc_type := PO_INTG_DOCUMENT_FUNDS_GRP.g_doc_type_PO;
252 ELSIF (x_shipment_type IN ('BLANKET', 'SCHEDULED')) THEN
253 l_progress := '020';
254 l_doc_type := PO_INTG_DOCUMENT_FUNDS_GRP.g_doc_type_RELEASE;
255 ELSE
256 l_progress := '030';
257 l_doc_type := PO_INTG_DOCUMENT_FUNDS_GRP.g_doc_type_PA;
258 END IF;
259
260 l_progress := '040';
261
262 PO_INTG_DOCUMENT_FUNDS_GRP.get_active_encumbrance_amount(
263 p_api_version => 1.0
264 , p_init_msg_list => FND_API.G_FALSE
265 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
266 , x_return_status => l_return_status
267 , p_doc_type => l_doc_type
268 , p_distribution_id => x_po_distribution_id
269 , x_active_enc_amount => x_active_enc_amount
270 );
271
272 l_progress := '050';
273
274 IF (l_return_status = FND_API.g_ret_sts_UNEXP_ERROR) THEN
275 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
276 END IF;
277
278 --<ENCUMBRANCE FPJ END>
279
280 return(x_active_enc_amount);
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 po_message_s.sql_error('get_active_enc_amount', l_progress, sqlcode);
285 return (-1);
286
287 END get_active_enc_amount;
288
289
290 /*===========================================================================
291
292 FUNCTION NAME: get_dist_amount()
293
294 ===========================================================================*/
295
296 /* Changed due to bug 601388
297 Removed the IN parameter x_rate from the function and the parameters
298 related usage in the function
299 */
300
301 FUNCTION get_dist_amount
302 (
303 p_quantity_ordered IN NUMBER
304 , p_price_override IN NUMBER
305 , p_amount_ordered IN NUMBER -- <SERVICES FPJ>
306 , p_po_line_loc_id IN NUMBER -- <Complex Work R12>
307 , p_po_distribution_id IN NUMBER -- Bug 13440718
308 )
309 RETURN NUMBER
310 IS
311 x_min_unit NUMBER;
312 x_precision NUMBER;
313 x_dist_amount NUMBER;
314
315 l_value_basis PO_LINE_LOCATIONS_ALL.value_basis%TYPE; -- <Complex Work R12>
316 l_qty_cancelled NUMBER := 0;
317 l_amt_cancelled NUMBER := 0;
318
319 BEGIN
320
321 -- <SERVICES FPJ START>
322 -- <Complex Work R12 Start>
323
324 -- Get Value Basis for the line location.
325 --
326
327 SELECT poll.value_basis
328 INTO l_value_basis
329 FROM po_line_locations_all poll
330 WHERE poll.line_location_id = p_po_line_loc_id;
331
332 -- <Complex Work R12 End>
333
334 -- Determine if the Line is Amount or Quantity based
335 -- and calculate the Distribution Amount accordingly.
336
337 /*
338 Bug 13440718: Getting the quantity cancelled/amount ccancelled and subtracted them from
339 p_amount_ordered, p_quantity_ordered
340 */
341
342 SELECT quantity_cancelled, amount_cancelled
343 INTO l_qty_cancelled, l_amt_cancelled
344 FROM PO_DISTRIBUTIONS_ALL
345 WHERE po_distribution_id = p_po_distribution_id;
346
347
348 IF ( l_value_basis IN ('FIXED PRICE','RATE') )
349 THEN
350 x_dist_amount := p_amount_ordered-l_amt_cancelled;
351 ELSE
352 x_dist_amount := (p_quantity_ordered-l_qty_cancelled) * p_price_override;
353 END IF;
354
355 -- Round the Distribution Amount.
356 --
357 get_func_currency_attributes(x_min_unit, x_precision);
358
359 x_dist_amount := round ( x_dist_amount
360 , nvl(x_min_unit, x_precision)
361 );
362
363 -- <SERVICES FPJ END>
364
365 return(x_dist_amount);
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 return(-1);
370
371 END get_dist_amount;
372
373
374 /*===========================================================================
375
376 PROCEDURE NAME: get_func_currency_attributes;
377
378 ===========================================================================*/
379 PROCEDURE get_func_currency_attributes(x_min_unit OUT NOCOPY NUMBER,
380 x_precision OUT NOCOPY NUMBER) is
381
382 BEGIN
383
384 SELECT fc.minimum_accountable_unit,
385 fc.precision
386 INTO x_min_unit,
387 x_precision
388 FROM fnd_currencies fc,
389 gl_sets_of_books sob,
390 financials_system_parameters fsp
391 WHERE fsp.set_of_books_id = sob.set_of_books_id
392 AND sob.currency_code = fc.currency_code;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 /*dbms_output.put_line('In exception');
397 po_message_s.sql_error('get_func_currency_attributes',x_progress, sqlcode);
398 raise;*/
399 null;
400
401 END get_func_currency_attributes;
402
403 /*===========================================================================
404
405 FUNCTION NAME: get_person_name()
406
407 ===========================================================================*/
408 FUNCTION get_person_name (x_person_id IN NUMBER) RETURN VARCHAR2 is
409
410 x_person_name VARCHAR2(240);
411
412 BEGIN
413
414 /* Bug 3223368. Replaced the old statement which was there in the cursor with
415 the below statement to improve performance.
416
417 Any time we will have one record in the per_all_people_f which
418 will be valid in the sysdate and this will be the record with
419 the maximum effective_start_date.
420
421 Now with this fix, we will avoid the sort which was most expensive.
422 */
423
424 SELECT prf.full_name
425 INTO x_person_name
426 FROM per_all_people_f prf
427 WHERE prf.person_id = x_person_id
428 AND trunc(sysdate) between prf.effective_start_date and prf.effective_end_date;
429
430 return(x_person_name);
431
432 EXCEPTION
433 WHEN OTHERS THEN
434 return('');
435
436 END get_person_name;
437
438 /*===========================================================================
439
440 FUNCTION NAME: get_wip_operation_code
441
442 ===========================================================================*/
443 FUNCTION get_wip_operation_code(x_wip_entity_id IN NUMBER,
444 x_wip_operation_seq_num IN NUMBER,
445 x_destination_org_id IN NUMBER,
446 x_wip_rep_schedule_id IN NUMBER)
447 RETURN VARCHAR2 IS
448
449 x_wip_operation_code VARCHAR2(4);
450
451 BEGIN
452
453 SELECT bso.operation_code
454 INTO x_wip_operation_code
455 FROM bom_standard_operations bso,
456 wip_operations wop
457 WHERE ( x_wip_rep_schedule_id IS NULL
458 OR wop.repetitive_schedule_id
459 = x_wip_rep_schedule_id)
460 AND wop.wip_entity_id = x_wip_entity_id
461 AND wop.organization_id = x_destination_org_id
462 AND wop.operation_seq_num = x_wip_operation_seq_num
463 AND wop.standard_operation_id = bso.standard_operation_id(+)
464 AND NVL(bso.organization_id,x_destination_org_id)
465 = x_destination_org_id;
466
467 return(x_wip_operation_code);
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 return('');
472
473 END get_wip_operation_code;
474
475
476 /*===========================================================================
477
478 FUNCTION NAME: get_bom_department_code
479
480 ===========================================================================*/
481 FUNCTION get_bom_department_code(x_wip_entity_id IN NUMBER,
482 x_wip_operation_seq_num IN NUMBER,
483 x_destination_org_id IN NUMBER,
484 x_wip_rep_schedule_id IN NUMBER)
485 RETURN VARCHAR2 IS
486
487 x_bom_department_code VARCHAR2(10);
488
489 BEGIN
490
491 SELECT bod.department_code
492 INTO x_bom_department_code
493 FROM bom_departments bod,
494 wip_operations wop
495 WHERE ( x_wip_rep_schedule_id IS NULL
496 OR wop.repetitive_schedule_id
497 = x_wip_rep_schedule_id)
498 AND wop.wip_entity_id = x_wip_entity_id
499 AND wop.organization_id = x_destination_org_id
500 AND wop.operation_seq_num = x_wip_operation_seq_num
501 AND wop.department_id = bod.department_id
502 AND bod.organization_id = x_destination_org_id;
503
504 return(x_bom_department_code);
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 return('');
509
510 END get_bom_department_code;
511
512
513 /*===========================================================================
514
515 FUNCTION NAME: get_assembly_quantity
516
517 ===========================================================================*/
518 FUNCTION get_assembly_quantity(x_item_id IN NUMBER,
519 x_wip_entity_id IN NUMBER,
520 x_wip_operation_seq_num IN NUMBER,
521 x_wip_resource_seq_num IN NUMBER,
522 x_destination_org_id IN NUMBER,
523 x_wip_rep_schedule_id IN NUMBER,
524 x_quantity_ordered IN NUMBER,
525 p_item_organization_id IN NUMBER) -- <HTMLAC>
526 RETURN NUMBER IS
527
528 x_assembly_quantity NUMBER;
529
530 l_item_organization_id NUMBER; -- <HTMLAC>
531
532 BEGIN
533
534 -- <HTMLAC START>
535 IF (p_item_organization_id IS NOT NULL) THEN
536 l_item_organization_id := p_item_organization_id;
537 ELSE
538 -- use inv org id from current org if it is not specified
539 SELECT inventory_organization_id
540 INTO l_item_organization_id
541 FROM financials_system_parameters;
542 END IF;
543 -- <HTMLAC END>
544
545 SELECT decode(msi.outside_operation_uom_type,
546 'ASSEMBLY',x_quantity_ordered,
547 'RESOURCE',x_quantity_ordered /
548 decode(wor.usage_rate_or_amount,
549 0,x_quantity_ordered,
550 wor.usage_rate_or_amount)
551 )
552 INTO x_assembly_quantity
553 FROM wip_operation_resources wor,
554 mtl_system_items msi
555 WHERE wor.wip_entity_id = x_wip_entity_id
556 AND nvl(wor.repetitive_schedule_id,-1) = nvl(x_wip_rep_schedule_id,-1)
557 AND wor.operation_seq_num = x_wip_operation_seq_num
558 AND wor.resource_seq_num = x_wip_resource_seq_num
559 AND wor.organization_id = x_destination_org_id
560 AND msi.inventory_item_id = x_item_id
561 AND msi.organization_id = l_item_organization_id; -- <HTMLAC>
562
563 /* Bug 2899560. Need to round off the quantity */
564 x_assembly_quantity := round ( x_assembly_quantity,6);
565
566 return(x_assembly_quantity);
567
568 EXCEPTION
569 when no_data_found then
570 --Bug# 2000013 togeorge 09/18/2001
571 --for eAM workorders there wouldn't be any resource info and the above
572 --sql would raise no_data_found exception
573 return(x_quantity_ordered);
574 WHEN OTHERS THEN
575 return(to_number(NULL));
576
577 END get_assembly_quantity;
578
579
580 /*===========================================================================
581
582 FUNCTION NAME: get_resource_quantity
583
584 ===========================================================================*/
585 FUNCTION get_resource_quantity(x_item_id IN NUMBER,
586 x_wip_entity_id IN NUMBER,
587 x_wip_operation_seq_num IN NUMBER,
588 x_wip_resource_seq_num IN NUMBER,
589 x_destination_org_id IN NUMBER,
590 x_wip_rep_schedule_id IN NUMBER,
591 x_quantity_ordered IN NUMBER,
592 p_item_organization_id IN NUMBER) -- <HTMLAC>
593 RETURN NUMBER IS
594
595 x_resource_quantity NUMBER;
596
597 l_item_organization_id NUMBER; -- <HTMLAC>
598
599 BEGIN
600
601 -- <HTMLAC START>
602 IF (p_item_organization_id IS NOT NULL) THEN
603 l_item_organization_id := p_item_organization_id;
604 ELSE
605 -- use inv org id from current org if it is not specified
606 SELECT inventory_organization_id
607 INTO l_item_organization_id
608 FROM financials_system_parameters;
609 END IF;
610 -- <HTMLAC END>
611
612 SELECT decode(msi.outside_operation_uom_type,
613 'ASSEMBLY',x_quantity_ordered * wor.usage_rate_or_amount,
614 'RESOURCE',x_quantity_ordered)
615 INTO x_resource_quantity
616 FROM wip_operation_resources wor,
617 mtl_system_items msi
618 WHERE wor.wip_entity_id = x_wip_entity_id
619 AND nvl(wor.repetitive_schedule_id,-1) = nvl(x_wip_rep_schedule_id,-1)
620 AND wor.operation_seq_num = x_wip_operation_seq_num
621 AND wor.resource_seq_num = x_wip_resource_seq_num
622 AND wor.organization_id = x_destination_org_id
623 AND msi.inventory_item_id = x_item_id
624 AND msi.organization_id = l_item_organization_id; -- <HTMLAC>
625
626 return(x_resource_quantity);
627
628 EXCEPTION
629 when no_data_found then
630 --Bug# 2000013 togeorge 09/18/2001
631 --for eAM workorders there wouldn't be any resource info and the above
632 --sql would raise no_data_found exception
633 return(x_quantity_ordered);
634 WHEN OTHERS THEN
635 return(to_number(NULL));
636
637 END get_resource_quantity;
638
639
640
641 /*===========================================================================
642
643 FUNCTION NAME: get_po_number
644
645 ===========================================================================*/
646 FUNCTION get_po_number (x_line_location_id IN NUMBER) RETURN VARCHAR2 is
647
648 x_po_number VARCHAR2(20);
649
650 BEGIN
651
652 /*
653 Performance Fix (bug 414200)
654 The following code is being replaced for performance reasons:
655 SELECT po_num
656 INTO x_po_number
657 FROM po_line_locations_inq_v
658 WHERE x_line_location_id = line_location_id;
659 */
660
661 SELECT POH.CLM_DOCUMENT_NUMBER
662 INTO x_po_number
663 FROM po_headers_all POH, po_line_locations_all PLL --<Shared Proc FPJ>
664 WHERE POH.po_header_id = PLL.po_header_id and
665 PLL.line_location_id = x_line_location_id;
666
667
668 return(x_po_number);
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 return('');
673
674 END get_po_number;
675
676
677 /*===========================================================================
678
679 FUNCTION NAME: get_so_number()
680
681 ===========================================================================*/
682 FUNCTION get_so_number (x_requisition_header_id IN VARCHAR2,
683 x_requisition_line_id IN NUMBER) RETURN VARCHAR2 is
684
685 x_so_number number;
686 x_order_source_id number;
687
688 BEGIN
689 /* replacing the select statement with the new OE API. */
690 select order_source_id
691 into x_order_source_id
692 from po_system_parameters;
693
694 --Bug# 1392077
695 --Toju George 08/31/2000
696 --Modified the call to procedure to replace req_num and line_num with ids.
697 /* x_so_number := OE_ORDER_IMPORT_INTEROP_PUB.Get_Order_Number(x_order_source_id,
698 x_segment1,
699 to_char(x_line_num));
700 */
701 x_so_number := OE_ORDER_IMPORT_INTEROP_PUB.Get_Order_Number(x_order_source_id,
702 x_requisition_header_id,
703 to_char(x_requisition_line_id));
704
705 /* SELECT distinct(soh.order_number)
706 INTO x_so_number
707 FROM so_lines sol,
708 so_headers soh,
709 po_system_parameters psp
710 WHERE sol.original_system_line_reference = to_char(x_line_num)
711 AND soh.original_system_reference = x_segment1
712 AND sol.header_id = soh.header_id
713 AND soh.original_system_source_code = psp.order_source_id;
714 */
715 return(to_char(x_so_number));
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 return('');
720
721 END get_so_number;
722
723 /*===========================================================================
724
725 FUNCTION NAME: shipment_from_req
726
727 ===========================================================================*/
728
729 FUNCTION shipment_from_req (x_line_location_id IN NUMBER)
730 RETURN BOOLEAN
731 IS
732 x_num_records NUMBER := 0;
733 BEGIN
734
735 IF x_line_location_id IS NOT NULL THEN
736
737 SELECT count(*)
738 INTO x_num_records
739 FROM po_requisition_lines_all --<Shared Proc FPJ>
740 WHERE line_location_id = x_line_location_id;
741
742 IF x_num_records > 0 THEN
743 return (TRUE);
744 ELSE
745 return (FALSE);
746 END IF;
747
748 ELSE
749 return (FALSE);
750 END IF;
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 return(FALSE);
755 END;
756
757 /*===========================================================================
758
759 FUNCTION NAME: get_po_total
760
761 ===========================================================================*/
762
763 FUNCTION get_po_total (x_type_lookup_code IN VARCHAR2,
764 x_po_header_id IN NUMBER,
765 x_po_release_id IN NUMBER)
766 return NUMBER
767 IS
768 x_total_level VARCHAR2(2); -- <GC FPJ>
769 x_total NUMBER;
770 BEGIN
771
772 /* Bug : 1056562 Reverting back to 10.7 status. We should be displaying
773 Released amount not agreed amount.
774 */
775
776 if (x_type_lookup_code = 'STANDARD') then
777
778 x_total_level := 'H';
779
780 elsif (x_type_lookup_code = 'CONTRACT') then
781
782 -- <GC FPJ>
783 -- x_total_level should be 'GC' if it is global contract
784
785 IF (PO_GA_PVT.is_global_agreement(x_po_header_id)) THEN
786 x_total_level := 'GC';
787 ELSE
788 x_total_level := 'C';
789 END IF;
790
791 elsif (x_type_lookup_code = 'BLANKET') then
792
793 -- Bug 2954456 : The level needs to be set as 'G' for a global agreement to calculate the
794 -- correct amount released
795
796 -- <GC FPJ START>
797 -- change x_total_level for global agreemnt from 'G' to 'GA'
798 -- to make it consistent with GC. Also, x_total_level for Blanket should
799 -- be 'B' instead of 'H' (although they mean the same in get_total)
800
801 if PO_GA_PVT.is_global_agreement(x_po_header_id) then
802
803 x_total_level := 'GA';
804 else
805
806 x_total_level := 'B';
807 end if;
808
809 -- <GC FPJ END>
810
811 elsif (x_type_lookup_code = 'PLANNED') then
812
813 x_total_level := 'H';
814
815 end if;
816
817 if (x_po_release_id is NOT NULL) then
818 x_total_level := 'R';
819 x_total := po_core_s.get_total(x_total_level, x_po_release_id);
820
821 else
822 x_total := po_core_s.get_total(x_total_level, x_po_header_id);
823
824 end if;
825
826 return x_total;
827
828 END;
829
830
831 /*===========================================================================
832
833 PROCEDURE NAME: get_post_query_info
834
835 ===========================================================================*/
836
837 PROCEDURE get_post_query_info (
838 x_cancelled_by IN NUMBER,
839 x_closed_by IN NUMBER,
840 x_agent_id IN NUMBER,
841 x_type_lookup_code IN VARCHAR2,
842 x_po_header_id IN NUMBER,
843 x_po_release_id IN NUMBER,
844 x_po_line_id IN NUMBER,
845 x_line_location_id IN NUMBER,
846 x_agent_name IN OUT NOCOPY VARCHAR2,
847 x_closed_by_name IN OUT NOCOPY VARCHAR2,
848 x_cancelled_by_name IN OUT NOCOPY VARCHAR2,
849 x_base_currency IN OUT NOCOPY VARCHAR2,
850 x_amount IN OUT NOCOPY NUMBER
851
852 ) IS
853
854 l_po_header_id number;
855 l_quantity_released number;
856 l_db_amount po_lines_all.amount%type;
857 l_value_basis po_line_types_b.order_type_lookup_code%type;
858
859 BEGIN
860
861 IF x_agent_id IS NOT NULL THEN
862 x_agent_name := get_person_name(x_agent_id);
863 END IF;
864
865 /* Bug 642604 - Getting cancelled by name using cancelled by id
866 instead of agent id */
867 IF x_cancelled_by IS NOT NULL THEN
868 x_cancelled_by_name := get_person_name(x_cancelled_by);
869 END IF;
870
871 /* Bug 1341727 Amitabh use closed by instead of agent_id */
872
873 IF x_closed_by IS NOT NULL THEN
874 x_closed_by_name := get_person_name(x_closed_by);
875 END IF;
876
877 x_base_currency := po_core_s2.get_base_currency;
878
879 IF x_po_line_id IS NOT NULL THEN
880
881 -- Bug 2954456 : The amount released has to be calculated in a different way for GA lines
882 -- SERVICES FPJ : amount is derived from the db for service lines.
883
884 Begin
885 select pol.po_header_id ,
886 pol.amount,
887 plt.order_type_lookup_code
888 into l_po_header_id,
889 l_db_amount, -- SERVICES FPJ
890 l_value_basis -- SERVICES FPJ
891 from po_lines_all pol,
892 po_line_types_b plt
893 where pol.po_line_id = x_po_line_id
894 and pol.line_type_id = plt.line_type_id;
895 Exception
896 when others then
897 l_po_header_id := null;
898 l_db_amount := null;
899 l_value_basis := null;
900 end;
901
902 IF l_value_basis in ('RATE' , 'FIXED PRICE') THEN -- SERVICES FPJ
903
904 x_amount := l_db_amount; -- SERVICES FPJ
905
906 ELSE
907
908 if PO_GA_PVT.is_global_agreement(l_po_header_id) then
909 PO_CORE_S.get_ga_line_amount_released( x_po_line_id, -- IN
910 l_po_header_id, -- OUT
911 l_quantity_released, -- OUT
912 x_amount ); -- OUT
913 else
914 x_amount := PO_CORE_S.GET_TOTAL('L', x_po_line_id);
915 end if;
916
917 END IF;
918
919 ELSIF x_line_location_id IS NOT NULL THEN
920 x_amount := PO_CORE_S.GET_TOTAL('S', x_line_location_id);
921 ELSIF x_po_release_id IS NOT NULL THEN
922 x_amount := get_po_total(x_type_lookup_code, null, x_po_release_id);
923 ELSIF x_po_header_id IS NOT NULL THEN
924 x_amount := GET_PO_TOTAL(x_type_lookup_code, x_po_header_id, NULL);
925 END IF;
926
927 END;
928
929 /*===========================================================================
930
931 PROCEDURE NAME: get_distribution_info
932
933 ===========================================================================*/
934
935 PROCEDURE get_distribution_info (
936 x_deliver_to_person_id IN NUMBER,
937 x_closed_by IN NUMBER,
938 x_agent_id IN NUMBER,
939 x_item_id IN NUMBER,
940 x_wip_entity_id IN NUMBER,
941 x_wip_operation_seq_num IN NUMBER,
942 x_wip_resource_seq_num IN NUMBER,
943 x_destination_org_id IN NUMBER,
944 x_wip_rep_schedule_id IN NUMBER,
945 x_quantity_ordered IN NUMBER,
946 x_rate IN NUMBER,
947 x_price_override IN NUMBER,
948 x_amount_ordered IN NUMBER, -- <SERVICES FPJ>
949 x_po_line_id IN NUMBER, -- <SERVICES FPJ>
950 x_line_location_id IN NUMBER,
951 x_encumbered_amount IN NUMBER,
952 x_shipment_type IN VARCHAR2,
953 x_po_distribution_id IN NUMBER,
954 x_deliver_to_person IN OUT NOCOPY VARCHAR2,
955 x_agent_name IN OUT NOCOPY VARCHAR2,
956 x_closed_by_name IN OUT NOCOPY VARCHAR2,
957 x_base_currency IN OUT NOCOPY VARCHAR2,
958 x_assembly_quantity IN OUT NOCOPY NUMBER,
959 x_resource_quantity IN OUT NOCOPY NUMBER,
960 x_wip_operation_code IN OUT NOCOPY VARCHAR2,
961 x_bom_department_code IN OUT NOCOPY VARCHAR2,
962 x_active_encumb_amount IN OUT NOCOPY NUMBER,
963 x_distribution_amount IN OUT NOCOPY NUMBER
964
965 ) IS BEGIN
966
967 IF x_agent_id IS NOT NULL THEN
968 x_agent_name := get_person_name(x_agent_id);
969 END IF;
970
971 IF x_deliver_to_person_id IS NOT NULL THEN
972 x_deliver_to_person := get_person_name(x_deliver_to_person_id);
973 END IF;
974
975 IF x_closed_by IS NOT NULL THEN
976 x_closed_by_name := get_person_name(x_closed_by);
977 END IF;
978
979 x_base_currency := po_core_s2.get_base_currency;
980
981 IF x_wip_entity_id IS NOT NULL THEN
982 x_assembly_quantity := PO_INQ_SV.GET_ASSEMBLY_QUANTITY(
983 x_item_id,
984 x_wip_entity_id,
985 x_wip_operation_seq_num,
986 x_wip_resource_seq_num,
987 x_destination_org_id,
988 x_wip_rep_schedule_id,
989 x_quantity_ordered);
990
991 x_resource_quantity := PO_INQ_SV.GET_RESOURCE_QUANTITY(
992 x_item_id,
993 x_wip_entity_id,
994 x_wip_operation_seq_num,
995 x_wip_resource_seq_num,
996 x_destination_org_id,
997 x_wip_rep_schedule_id,
998 x_quantity_ordered);
999
1000
1001 x_wip_operation_code := PO_INQ_SV.GET_WIP_OPERATION_CODE(
1002 x_WIP_ENTITY_ID,
1003 x_WIP_OPERATION_SEQ_NUM,
1004 x_DESTINATION_ORG_ID,
1005 x_WIP_REP_SCHEDULE_ID) ;
1006
1007 x_bom_department_code := PO_INQ_SV.GET_BOM_DEPARTMENT_CODE(
1008 x_WIP_ENTITY_ID,
1009 x_WIP_OPERATION_SEQ_NUM,
1010 x_DESTINATION_ORG_ID,
1011 x_WIP_REP_SCHEDULE_ID);
1012
1013 END IF;
1014
1015 /* Changed due to bug 601388
1016 Removed the IN parameter x_rate when calling the function
1017 */
1018
1019 x_distribution_amount := PO_INQ_SV.GET_DIST_AMOUNT(
1020 x_QUANTITY_ORDERED,
1021 x_PRICE_OVERRIDE,
1022 X_AMOUNT_ORDERED, -- <SERVICES FPJ>
1023 X_LINE_LOCATION_ID, -- <Complex Work R12>
1024 x_po_distribution_id); -- Bug 13440718
1025
1026
1027 x_active_encumb_amount := PO_INQ_SV.GET_ACTIVE_ENC_AMOUNT(
1028 x_RATE,
1029 x_ENCUMBERED_AMOUNT,
1030 x_SHIPMENT_TYPE,
1031 x_PO_DISTRIBUTION_ID);
1032
1033 END;
1034
1035 /*===========================================================================
1036
1037 PROCEDURE NAME: get_org_info()
1038
1039 ===========================================================================*/
1040 PROCEDURE get_org_info(x_destination_org_id IN number,
1041 x_expenditure_org_id IN number,
1042 x_ship_to_org_id IN number ,
1043 x_dest_org_name IN OUT NOCOPY varchar2,
1044 x_exp_org_name IN OUT NOCOPY varchar2,
1045 x_ship_to_org_name IN OUT NOCOPY varchar2) is
1046
1047 x_progress varchar2(3) := '000';
1048
1049 cursor c1(x_org_id IN number) is
1050 select hout.name
1051 from hr_all_organization_units_tl hout,
1052 hr_org_units_no_join hrou
1053 where hrou.organization_id = x_org_id
1054 and hrou.organization_id = hout.organization_id
1055 and hout.language = userenv('lang');
1056
1057 BEGIN
1058
1059 /* for deliver to org */
1060 x_progress := '001';
1061 if x_destination_org_id is not null then
1062 OPEN c1(x_destination_org_id);
1063 FETCH c1 into x_dest_org_name ;
1064 IF c1%NOTFOUND then
1065 x_dest_org_name := NULL;
1066 END IF;
1067 CLOSE c1;
1068 end if;
1069
1070 /* for expenditure org */
1071 x_progress := '002';
1072 if x_expenditure_org_id is not null then
1073 OPEN c1(x_expenditure_org_id);
1074 FETCH c1 into x_exp_org_name ;
1075 IF c1%NOTFOUND then
1076 x_exp_org_name := NULL;
1077 END IF;
1078 CLOSE c1;
1079 end if;
1080
1081 /* for ship to org */
1082 x_progress := '003';
1083 if x_ship_to_org_id is not null then
1084 OPEN c1(x_ship_to_org_id);
1085 FETCH c1 into x_ship_to_org_name ;
1086 IF c1%NOTFOUND then
1087 x_ship_to_org_name := NULL;
1088 END IF;
1089 CLOSE c1;
1090 end if;
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 po_message_s.sql_error('po_inq_sv.get_org_info', x_progress, sqlcode);
1095 raise;
1096 END;
1097
1098 /*===========================================================================
1099
1100 PROCEDURE NAME: get_location_info()
1101
1102 ===========================================================================*/
1103 PROCEDURE get_location_info(x_deliver_to_loc_id IN number,
1104 x_bill_to_loc_id IN number,
1105 x_ship_to_loc_id IN number ,
1106 x_dest_location_code IN OUT NOCOPY varchar2,
1107 x_bill_to_loc_code IN OUT NOCOPY varchar2,
1108 x_ship_to_loc_code IN OUT NOCOPY varchar2) is
1109
1110 x_progress varchar2(3) := '000';
1111
1112 cursor c1(x_location_id IN number) is
1113 select location_code
1114 from hr_locations
1115 where location_id = x_location_id;
1116
1117 BEGIN
1118 /* for deliver to location */
1119 x_progress := '001';
1120 if x_deliver_to_loc_id is not null then
1121 OPEN c1(x_deliver_to_loc_id);
1122 FETCH c1 into x_dest_location_code;
1123 IF c1%NOTFOUND then
1124 x_dest_location_code := NULL;
1125 END IF;
1126 CLOSE c1;
1127 end if;
1128
1129 /* for bill to location */
1130 x_progress := '002';
1131 if x_bill_to_loc_id is not null then
1132 OPEN c1(x_bill_to_loc_id);
1133 FETCH c1 into x_bill_to_loc_code;
1134 IF c1%NOTFOUND then
1135 x_bill_to_loc_code := NULL;
1136 END IF;
1137 CLOSE c1;
1138 end if;
1139
1140 /* for ship to location */
1141 x_progress := '003';
1142 if x_ship_to_loc_id is not null then
1143 OPEN c1(x_ship_to_loc_id);
1144 FETCH c1 into x_ship_to_loc_code;
1145 IF c1%NOTFOUND then
1146 x_ship_to_loc_code := NULL;
1147 END IF;
1148 CLOSE c1;
1149 end if;
1150
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 po_message_s.sql_error('po_inq_sv.get_location_info', x_progress, sqlcode);
1154 raise;
1155 END;
1156
1157 /*===========================================================================
1158
1159 PROCEDURE NAME: get_project_info()
1160
1161 ===========================================================================*/
1162 PROCEDURE get_project_info(x_project_id IN number,
1163 x_task_id IN number,
1164 x_project_num IN OUT NOCOPY varchar2,
1165 x_task_num IN OUT NOCOPY varchar2) is
1166
1167 x_progress varchar2(3) := '000';
1168 --< Bug 3265539 Start >
1169 l_return_status VARCHAR2(1);
1170 --< Bug 3265539 End >
1171
1172 BEGIN
1173
1174 x_progress := '001';
1175 --< Bug 3265539 Start >
1176 -- Need to retrieve project info correctly using this utility procedure
1177 PO_PROJECT_DETAILS_SV.get_project_task_num
1178 (x_return_status => l_return_status,
1179 p_project_id => x_project_id,
1180 p_task_id => x_task_id,
1181 x_project_num => x_project_num,
1182 x_task_num => x_task_num);
1183
1184 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
1185 RAISE APP_EXCEPTION.application_exception;
1186 END IF;
1187 --< Bug 3265539 End >
1188
1189 EXCEPTION
1190
1191 WHEN OTHERS THEN
1192 po_message_s.sql_error('po_inq_sv.get_project_info', x_progress, sqlcode);
1193 raise;
1194 END;
1195
1196 /*===========================================================================
1197
1198 PROCEDURE NAME: get_wip_bom_info()
1199
1200 ===========================================================================*/
1201 PROCEDURE get_wip_bom_info(x_wip_entity_id IN number,
1202 x_wip_line_id IN number ,
1203 x_bom_resource_id IN number ,
1204 x_destination_org_id IN number,
1205 x_wip_entity_name IN OUT NOCOPY varchar2,
1206 x_wip_line_code IN OUT NOCOPY varchar2,
1207 x_bom_resource_code IN OUT NOCOPY varchar2,
1208 x_bom_uom IN OUT NOCOPY varchar2) is
1209
1210 x_progress varchar2(3) := '000';
1211
1212 cursor c1 is
1213 select line_code
1214 from wip_lines
1215 where line_id = x_wip_line_id
1216 and organization_id = x_destination_org_id ;
1217
1218 cursor c2 is
1219 select wip_entity_name
1220 from wip_entities
1221 where wip_entity_id = x_wip_entity_id
1222 and organization_id = x_destination_org_id ;
1223
1224 cursor c3 is
1225 select resource_code,
1226 unit_of_measure
1227 from bom_resources
1228 where resource_id = x_bom_resource_id and
1229 organization_id = x_destination_org_id ;
1230
1231 BEGIN
1232
1233 x_progress := '001';
1234 if x_wip_line_id is not null then
1235 OPEN c1;
1236 FETCH c1 into x_wip_line_code ;
1237 IF c1%NOTFOUND then
1238 x_wip_line_code := NULL;
1239 END IF;
1240 CLOSE c1;
1241 end if;
1242
1243 x_progress := '002';
1244 if x_wip_entity_id is not null then
1245 OPEN c2;
1246 FETCH c2 into x_wip_entity_name;
1247 IF c2%NOTFOUND then
1248 x_wip_entity_name := NULL;
1249 END IF;
1250 CLOSE c2;
1251 end if;
1252
1253 x_progress := '003';
1254 if x_bom_resource_id is not null then
1255 OPEN c3;
1256 FETCH c3 into x_bom_resource_code,
1257 x_bom_uom ;
1258 IF c3%NOTFOUND then
1259 x_bom_resource_code := NULL;
1260 x_bom_uom := NULL;
1261 END IF;
1262 CLOSE c3;
1263 end if;
1264
1265
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 po_message_s.sql_error('po_inq_sv.get_wip_bom_info', x_progress, sqlcode);
1269 raise;
1270 END;
1271
1272 /*===========================================================================
1273
1274 PROCEDURE NAME: get_vendor_info()
1275
1276 ===========================================================================*/
1277 PROCEDURE get_vendor_info(x_vendor_id IN number,
1278 x_vendor_site_id IN number ,
1279 x_vendor_name IN OUT NOCOPY varchar2,
1280 x_vendor_site_code IN OUT NOCOPY varchar2) is
1281
1282 x_progress varchar2(3) := '000';
1283 cursor c1 is
1284 select vendor_name
1285 from po_vendors
1286 where vendor_id = x_vendor_id ;
1287
1288 cursor c2 is
1289 select vendor_site_code
1290 from po_vendor_sites_all --<Shared Proc FPJ>
1291 where vendor_site_id = x_vendor_site_id;
1292
1293 BEGIN
1294
1295 x_progress := '001';
1296 if x_vendor_id is not null then
1297 OPEN c1;
1298 FETCH c1 into x_vendor_name;
1299 IF c1%NOTFOUND then
1300 x_vendor_name := NULL;
1301 END IF;
1302 CLOSE c1;
1303 end if;
1304
1305 x_progress := '002';
1306 if x_vendor_site_id is not null then
1307 OPEN c2;
1308 FETCH c2 into x_vendor_site_code;
1309 IF c2%NOTFOUND then
1310 x_vendor_site_code := NULL;
1311 END IF;
1312 CLOSE c2;
1313 end if;
1314
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317 po_message_s.sql_error('po_inq_sv.get_vendor_info', x_progress, sqlcode);
1318 raise;
1319 END;
1320
1321 /*===========================================================================
1322
1323 PROCEDURE NAME: get_ap_terms()
1324
1325 ===========================================================================*/
1326 PROCEDURE get_ap_terms(x_terms_id IN number,
1327 x_ap_terms_name IN OUT NOCOPY varchar2) is
1328
1329 x_progress varchar2(3) := '000';
1330 cursor c1 is
1331 select name
1332 from ap_terms
1333 where term_id = x_terms_id;
1334
1335 BEGIN
1336
1337 OPEN c1;
1338 FETCH c1 into x_ap_terms_name;
1339 IF c1%NOTFOUND then
1340 x_ap_terms_name := NULL;
1341 END IF;
1342 CLOSE c1;
1343
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346 po_message_s.sql_error('po_inq_sv.get_ap_terms', x_progress, sqlcode);
1347 raise;
1348 END;
1349
1350 /*===========================================================================
1351
1352 PROCEDURE NAME: get_dist_info_pq()
1353 added for the performance fix in bug 1338674 to get the values
1354 removed from the view.
1355
1356 ============================================================================*/
1357 PROCEDURE get_dist_info_pq (x_po_header_id IN number,
1358 x_po_line_location_id IN number,
1359 x_deliver_to_loc_id IN number,
1360 x_bill_to_loc_id IN number,
1361 x_destination_org_id IN number,
1362 x_expenditure_org_id IN number,
1363 x_vendor_id IN number,
1364 x_vendor_site_id IN number,
1365 x_project_id IN number,
1366 x_task_id IN number,
1367 x_bom_resource_id IN number,
1368 x_wip_entity_id IN number,
1369 x_wip_line_id IN number,
1370 x_dest_location_code IN OUT NOCOPY varchar2,
1371 x_bill_to_loc_code IN OUT NOCOPY varchar2,
1372 x_ship_to_loc_code IN OUT NOCOPY varchar2,
1373 x_dest_org_name IN OUT NOCOPY varchar2,
1374 x_exp_org_name IN OUT NOCOPY varchar2,
1375 x_ship_to_org_name IN OUT NOCOPY varchar2,
1376 x_project_num IN OUT NOCOPY varchar2,
1377 x_task_num IN OUT NOCOPY varchar2,
1378 x_wip_entity_name IN OUT NOCOPY varchar2,
1379 x_wip_line_code IN OUT NOCOPY varchar2,
1380 x_bom_resource_code IN OUT NOCOPY varchar2,
1381 x_bom_uom IN OUT NOCOPY varchar2,
1382 x_ap_terms_name IN OUT NOCOPY varchar2,
1383 x_vendor_name IN OUT NOCOPY varchar2,
1384 x_vendor_site_code IN OUT NOCOPY varchar2,
1385 --< Shared Proc FPJ Start >
1386 x_purchasing_ou_coa_id OUT NOCOPY NUMBER,
1387 x_ship_to_ou_coa_id OUT NOCOPY NUMBER,
1388 --< Shared Proc FPJ End >
1389 --< Bug 3266689 Start >
1390 x_type_lookup_code IN varchar2
1391 --< Bug 3266689 End >
1392 ) is
1393
1394 cursor c1 is
1395 select ship_to_organization_id ,
1396 ship_to_location_id
1397 from po_line_locations_all --< Shared Proc FPJ >
1398 where line_location_id = x_po_line_location_id;
1399
1400 cursor c2 is
1401 select terms_id
1402 from po_headers_all --< Shared Proc FPJ >
1403 where po_header_id = x_po_header_id;
1404
1405 x_progress varchar2(3) := '000';
1406 x_ship_to_org_id number;
1407 x_ship_to_loc_id number;
1408 x_terms_id number;
1409
1410 --< Shared Proc FPJ Start >
1411 l_ship_to_ou_id HR_ALL_ORGANIZATION_UNITS.organization_id%TYPE;
1412 l_return_status VARCHAR2(1); -- FND_API.g_ret_sts_success
1413 --< Shared Proc FPJ End >
1414
1415 BEGIN
1416
1417 OPEN c1;
1418 FETCH c1 into x_ship_to_org_id,
1419 x_ship_to_loc_id;
1420
1421 if c1%NOTFOUND then
1422 x_ship_to_org_id := null;
1423 x_ship_to_loc_id := null;
1424 end if;
1425 CLOSE c1;
1426
1427 /* gets the organization names and locations correcponding to the id's */
1428
1429 x_progress := '001';
1430 get_org_info(x_destination_org_id ,
1431 x_expenditure_org_id ,
1432 x_ship_to_org_id,
1433 x_dest_org_name,
1434 x_exp_org_name,
1435 x_ship_to_org_name);
1436
1437 --< Shared Proc FPJ Start >
1438 x_progress := '01b';
1439
1440
1441 /* Bug 3266689: Added NOT NULL if condition on line_location_id */
1442 IF ((x_po_line_location_id is NOT NULL) or
1443 (x_type_lookup_code <> 'BLANKET'))
1444 THEN
1445
1446 -- Get the Ship-to OU's Chart of Accounts ID
1447 PO_SHARED_PROC_PVT.get_ou_and_coa_from_inv_org(
1448 p_inv_org_id => x_ship_to_org_id,
1449 x_coa_id => x_ship_to_ou_coa_id,
1450 x_ou_id => l_ship_to_ou_id,
1451 x_return_status => l_return_status);
1452
1453 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
1454 APP_EXCEPTION.raise_exception(
1455 exception_type => 'PO_INQ_SV.get_dist_info_pq',
1456 exception_code => 0,
1457 exception_text => 'Exception in PO_SHARED_PROC_PVT.' ||
1458 'get_ou_and_coa_from_inv_org() - '||
1459 'po_line_location_id='||x_po_line_location_id||
1460 ', ship_to_org_id='||x_ship_to_org_id);
1461 END IF;
1462
1463 x_progress := '01c';
1464 --SQL WHAT: Derive the COA tied to a Set of Books that, in turn, is
1465 -- tied to the Purchasing Operating Unit at the given
1466 -- PO Shipment.
1467 --SQL WHY: To define the Account flexfield structure in PO Summary
1468 -- Form (Distributions Window)
1469 BEGIN
1470 SELECT gsb.chart_of_accounts_id
1471 INTO x_purchasing_ou_coa_id
1472 FROM gl_sets_of_books gsb,
1473 financials_system_params_all fspa,
1474 po_line_locations_all pll
1475 WHERE pll.line_location_id = x_po_line_location_id
1476 AND fspa.org_id = pll.org_id
1477 AND gsb.set_of_books_id = fspa.set_of_books_id;
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 APP_EXCEPTION.raise_exception(
1481 exception_type => 'PO_INQ_SV.get_dist_info_pq',
1482 exception_code => 0,
1483 exception_text => 'Could not find Chart of Accounts for ' ||
1484 'Purchasing Operating Unit - '||
1485 'po_line_location_id='||x_po_line_location_id);
1486 END;
1487 --< Shared Proc FPJ End >
1488
1489 END IF; /* if x_po_line_location_id IS NOT NULL... */
1490
1491 x_progress := '002';
1492 get_location_info( x_deliver_to_loc_id ,
1493 x_bill_to_loc_id,
1494 x_ship_to_loc_id,
1495 x_dest_location_code,
1496 x_bill_to_loc_code,
1497 x_ship_to_loc_code );
1498
1499
1500 /* the following procedures get the values of project/task,wip,bom
1501 and ap related fields */
1502
1503 x_progress := '003';
1504 get_project_info(x_project_id ,
1505 x_task_id ,
1506 x_project_num,
1507 x_task_num );
1508
1509 x_progress := '004';
1510 get_wip_bom_info( x_wip_entity_id,
1511 x_wip_line_id ,
1512 x_bom_resource_id ,
1513 x_destination_org_id ,
1514 x_wip_entity_name ,
1515 x_wip_line_code ,
1516 x_bom_resource_code ,
1517 x_bom_uom );
1518
1519 OPEN c2;
1520 FETCH c2 into x_terms_id;
1521 if c2%FOUND then
1522
1523 x_progress := '005';
1524 get_ap_terms(x_terms_id,
1525 x_ap_terms_name);
1526 end if;
1527
1528 CLOSE c2;
1529
1530 /* this procedure gets the vendor related info*/
1531
1532 x_progress := '006';
1533 get_vendor_info(x_vendor_id ,
1534 x_vendor_site_id,
1535 x_vendor_name ,
1536 x_vendor_site_code );
1537
1538 x_progress := '007';
1539
1540 EXCEPTION
1541 WHEN OTHERS THEN
1542 po_message_s.sql_error('po_inq_sv.get_dist_info_pq', x_progress, sqlcode);
1543 raise;
1544 END;
1545
1546 /*===========================================================================
1547 togeorge 06/14/2001
1548 Bug# 1733951
1549 This procedure fetches the lookup values removed from the view
1550 po_line_locations_inq_v as part of the performance fix.
1551
1552 PROCEDURE NAME: get_shipments_pq_lookups
1553
1554 ===========================================================================*/
1555 PROCEDURE get_shipments_pq_lookups(x_enforce_ship_to_loc_code IN varchar2,
1556 x_receipt_days_excpt_code IN varchar2,
1557 x_qty_rcv_excpt_code IN varchar2,
1558 x_closed_code IN varchar2,
1559 x_shipment_type IN varchar2,
1560 x_authorization_status IN varchar2,
1561 x_fob_code IN varchar2,
1562 x_freight_terms_code IN varchar2,
1563 x_enforce_ship_to_loc_dsp IN OUT NOCOPY varchar2,
1564 x_receipt_days_excpt_dsp IN OUT NOCOPY varchar2,
1565 x_qty_rcv_excpt_dsp IN OUT NOCOPY varchar2,
1566 x_closed_code_dsp IN OUT NOCOPY varchar2,
1567 x_shipment_type_dsp IN OUT NOCOPY varchar2,
1568 x_authorization_status_dsp IN OUT NOCOPY varchar2,
1569 x_fob_code_dsp IN OUT NOCOPY varchar2,
1570 x_freight_terms_code_dsp IN OUT NOCOPY varchar2,
1571 p_match_option IN VARCHAR2,--Bug 2947251
1572 x_match_option_dsp OUT NOCOPY VARCHAR2 --Bug 2947251
1573 ) is
1574
1575 x_progress varchar2(3) := '000';
1576
1577 BEGIN
1578
1579 -- Bug 3816901: added calls to get_lookup_dsp for the
1580 -- lookups instead of duplicating the select for each
1581 -- lookup type
1582
1583 x_progress := '001';
1584 if x_enforce_ship_to_loc_code is not null then
1585 get_lookup_dsp('RECEIVING CONTROL LEVEL',
1586 x_enforce_ship_to_loc_code,
1587 x_enforce_ship_to_loc_dsp);
1588 end if;
1589
1590 x_progress := '002';
1591 if x_receipt_days_excpt_code is not null then
1592 get_lookup_dsp('RECEIVING CONTROL LEVEL',
1593 x_receipt_days_excpt_code,
1594 x_receipt_days_excpt_dsp);
1595 end if;
1596
1597
1598 x_progress := '003';
1599 if x_qty_rcv_excpt_code is not null then
1600 get_lookup_dsp('RECEIVING CONTROL LEVEL',
1601 x_qty_rcv_excpt_code,
1602 x_qty_rcv_excpt_dsp);
1603 end if;
1604
1605 x_progress := '004';
1606 if x_closed_code is not null then
1607 get_lookup_dsp('DOCUMENT STATE',
1608 x_closed_code,
1609 x_closed_code_dsp);
1610 end if;
1611
1612 x_progress := '005';
1613 if x_shipment_type is not null then
1614 get_lookup_dsp('SHIPMENT TYPE',
1615 x_shipment_type,
1616 x_shipment_type_dsp);
1617 end if;
1618
1619 x_progress := '006';
1620 if x_authorization_status is not null then
1621 get_lookup_dsp('AUTHORIZATION STATUS',
1622 x_authorization_status,
1623 x_authorization_status_dsp);
1624 end if;
1625
1626 x_progress := '008';
1627 if x_fob_code is not null then
1628 get_lookup_dsp('FOB',
1629 x_fob_code,
1630 x_fob_code_dsp);
1631 end if;
1632
1633 x_progress := '009';
1634 if x_freight_terms_code is not null then
1635 get_lookup_dsp('FREIGHT TERMS',
1636 x_freight_terms_code,
1637 x_freight_terms_code_dsp);
1638 end if;
1639
1640 --Bug 2947251 START
1641 x_progress := '010';
1642 IF p_match_option IS NOT NULL THEN
1643 get_lookup_dsp('PO INVOICE MATCH OPTION',
1644 UPPER(p_match_option),
1645 x_match_option_dsp);
1646 END IF;
1647 --Bug 2947251 END
1648
1649 EXCEPTION
1650 WHEN OTHERS THEN
1651 po_message_s.sql_error('po_inq_sv.get_shipments_pq_lookups', x_progress, sqlcode);
1652 raise;
1653 END;
1654
1655 /*===========================================================================
1656 togeorge 08/27/2001
1657 Bug# 1870283
1658 This procedure fetches the lookup values removed from the view
1659 po_distributions_inq_v as part of the performance fix.
1660
1661 PROCEDURE NAME: get_dist_pq_lookups
1662
1663 ===========================================================================*/
1664 PROCEDURE get_dist_pq_lookups(
1665 x_destination_type_code IN varchar2,
1666 x_authorization_status IN varchar2,
1667 x_shipment_type IN varchar2,
1668 x_closed_code IN varchar2,
1669 x_destination_type IN OUT NOCOPY varchar2,
1670 x_authorization_status_dsp IN OUT NOCOPY varchar2,
1671 x_shipment_type_dsp IN OUT NOCOPY varchar2,
1672 x_closed_code_dsp IN OUT NOCOPY varchar2) is
1673
1674 x_progress varchar2(3) := '000';
1675
1676 BEGIN
1677
1678 -- Bug 3816901: added calls to get_lookup_dsp for the
1679 -- lookups instead of duplicating the select for each
1680 -- lookup type
1681
1682 x_progress := '001';
1683 if x_destination_type_code is not null then
1684 get_lookup_dsp('DESTINATION TYPE',
1685 x_destination_type_code,
1686 x_destination_type);
1687 end if;
1688
1689 x_progress := '002';
1690 if x_authorization_status is not null then
1691 get_lookup_dsp('AUTHORIZATION STATUS',
1692 x_authorization_status,
1693 x_authorization_status_dsp);
1694 end if;
1695
1696 x_progress := '003';
1697 if x_shipment_type is not null then
1698 get_lookup_dsp('SHIPMENT TYPE',
1699 x_shipment_type,
1700 x_shipment_type_dsp);
1701 end if;
1702
1703 x_progress := '004';
1704 if x_closed_code is not null then
1705 get_lookup_dsp('DOCUMENT STATE',
1706 x_closed_code,
1707 x_closed_code_dsp);
1708 end if;
1709
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 po_message_s.sql_error('po_inq_sv.get_dist_pq_lookups', x_progress, sqlcode);
1713 raise;
1714 END get_dist_pq_lookups;
1715
1716 /*===========================================================================
1717 togeorge 08/31/2001
1718 Bug# 1926525
1719 This procedure fetches the lookup values removed from the view
1720 po_lines_inq_v as part of the performance fix.
1721
1722 PROCEDURE NAME: get_lines_pq_lookups
1723
1724 ===========================================================================*/
1725 PROCEDURE get_lines_pq_lookups(
1726 x_price_type_lookup_code IN varchar2,
1727 x_transaction_reason_code IN varchar2,
1728 x_price_break_lookup_code IN varchar2,
1729 x_closed_code IN varchar2,
1730 x_authorization_status IN varchar2,
1731 x_fob_code IN varchar2,
1732 x_freight_terms_code IN varchar2,
1733 x_price_type IN OUT NOCOPY varchar2,
1734 x_transaction_reason IN OUT NOCOPY varchar2,
1735 x_price_break IN OUT NOCOPY varchar2,
1736 x_closed_code_dsp IN OUT NOCOPY varchar2,
1737 x_authorization_status_dsp IN OUT NOCOPY varchar2,
1738 x_fob_code_dsp IN OUT NOCOPY varchar2,
1739 x_freight_terms_code_dsp IN OUT NOCOPY varchar2) is
1740
1741 x_progress varchar2(3) := '000';
1742
1743 BEGIN
1744
1745 -- Bug 3816901: added calls to get_lookup_dsp for the
1746 -- lookups instead of duplicating the select for each
1747 -- lookup type
1748
1749 x_progress := '001';
1750 if x_price_type_lookup_code is not null then
1751 get_lookup_dsp('PRICE TYPE',
1752 x_price_type_lookup_code,
1753 x_price_type);
1754 end if;
1755
1756 x_progress := '002';
1757 if x_transaction_reason_code is not null then
1758 get_lookup_dsp('TRANSACTION REASON',
1759 x_transaction_reason_code,
1760 x_transaction_reason);
1761 end if;
1762
1763 x_progress := '003';
1764 if x_price_break_lookup_code is not null then
1765 get_lookup_dsp('PRICE BREAK TYPE',
1766 x_price_break_lookup_code,
1767 x_price_break);
1768 end if;
1769
1770 x_progress := '004';
1771 if x_closed_code is not null then
1772 get_lookup_dsp('DOCUMENT STATE',
1773 x_closed_code,
1774 x_closed_code_dsp);
1775 end if;
1776
1777
1778 x_progress := '005';
1779 if x_authorization_status is not null then
1780 get_lookup_dsp('AUTHORIZATION STATUS',
1781 x_authorization_status,
1782 x_authorization_status_dsp);
1783 end if;
1784
1785 x_progress := '006';
1786 if x_fob_code is not null then
1787 get_lookup_dsp('FOB',
1788 x_fob_code,
1789 x_fob_code_dsp);
1790 end if;
1791
1792 x_progress := '007';
1793 if x_freight_terms_code is not null then
1794 get_lookup_dsp('FREIGHT TERMS',
1795 x_freight_terms_code,
1796 x_freight_terms_code_dsp);
1797 end if;
1798
1799
1800 EXCEPTION
1801 WHEN OTHERS THEN
1802 po_message_s.sql_error('po_inq_sv.get_lines_pq_lookups', x_progress, sqlcode);
1803 raise;
1804 END;
1805
1806 /*===========================================================================
1807 togeorge 11/19/2001
1808 Bug# 2038811
1809 This procedure fetches the lookup values removed from the view
1810 po_headers_inq_v as part of the performance fix.
1811
1812 PROCEDURE NAME: get_headers_pq_lookups
1813
1814 ===========================================================================*/
1815 PROCEDURE get_headers_pq_lookups(
1816 x_authorization_status IN varchar2,
1817 x_fob_code IN varchar2,
1818 x_freight_terms_code IN varchar2,
1819 x_closed_code IN varchar2,
1820 x_authorization_status_dsp IN OUT NOCOPY varchar2,
1821 x_fob_code_dsp IN OUT NOCOPY varchar2,
1822 x_freight_terms_code_dsp IN OUT NOCOPY varchar2,
1823 x_closed_code_dsp IN OUT NOCOPY varchar2,
1824 p_shipping_control IN VARCHAR2, -- <INBOUND LOGISTICS FPJ>
1825 x_shipping_control_dsp IN OUT NOCOPY VARCHAR2 -- <INBOUND LOGISTICS FPJ>
1826 ) is
1827
1828 x_progress varchar2(3) := '000';
1829
1830 BEGIN
1831
1832 -- Bug 3816901: added calls to get_lookup_dsp for the
1833 -- lookups instead of duplicating the select for each
1834 -- lookup type
1835
1836 x_progress := '001';
1837 if x_closed_code is not null then
1838 get_lookup_dsp('DOCUMENT STATE',
1839 x_closed_code,
1840 x_closed_code_dsp);
1841 end if;
1842
1843
1844 x_progress := '002';
1845 if x_authorization_status is not null then
1846 get_lookup_dsp('AUTHORIZATION STATUS',
1847 x_authorization_status,
1848 x_authorization_status_dsp);
1849 end if;
1850
1851 x_progress := '003';
1852 if x_fob_code is not null then
1853 get_lookup_dsp('FOB',
1854 x_fob_code,
1855 x_fob_code_dsp);
1856 end if;
1857
1858 x_progress := '004';
1859 if x_freight_terms_code is not null then
1860 get_lookup_dsp('FREIGHT TERMS',
1861 x_freight_terms_code,
1862 x_freight_terms_code_dsp);
1863 end if;
1864
1865 -- <INBOUND LOGISTICS FPJ START>
1866 x_progress := '005';
1867 IF p_shipping_control IS NOT NULL THEN
1868 get_lookup_dsp('SHIPPING CONTROL',
1869 p_shipping_control,
1870 x_shipping_control_dsp);
1871 END IF;
1872 -- <INBOUND LOGISTICS FPJ END>
1873
1874 EXCEPTION
1875 WHEN OTHERS THEN
1876 po_message_s.sql_error('po_inq_sv.get_headers_pq_lookups', x_progress, sqlcode);
1877 raise;
1878 END;
1879
1880 /*===========================================================================
1881
1882 PROCEDURE: get_source_info <GA FPI>
1883
1884 DESCRIPTION: Gets all source document-related information based on a
1885 po_header_id.
1886
1887 ===========================================================================*/
1888 PROCEDURE get_source_info
1889 (
1890 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE,
1891 x_segment1 OUT NOCOPY PO_HEADERS_ALL.segment1%TYPE,
1892 x_type_lookup_code OUT NOCOPY PO_HEADERS_ALL.type_lookup_code%TYPE,
1893 x_global_agreement_flag OUT NOCOPY PO_HEADERS_ALL.global_agreement_flag%TYPE,
1894 x_owning_org_id OUT NOCOPY PO_HEADERS_ALL.org_id%TYPE,
1895 x_quote_vendor_quote_number OUT NOCOPY PO_HEADERS_ALL.quote_vendor_quote_number%TYPE
1896 )
1897 IS
1898 BEGIN
1899
1900 SELECT segment1,
1901 type_lookup_code,
1902 global_agreement_flag,
1903 org_id,
1904 quote_vendor_quote_number
1905 INTO x_segment1,
1906 x_type_lookup_code,
1907 x_global_agreement_flag,
1908 x_owning_org_id,
1909 x_quote_vendor_quote_number
1910 FROM po_headers_all
1911 WHERE po_header_id = p_po_header_id;
1912
1913 EXCEPTION
1914 WHEN OTHERS THEN
1915 po_message_s.sql_error('get_source_info','000',sqlcode);
1916 raise;
1917
1918 END get_source_info;
1919
1920
1921 /*===========================================================================
1922
1923 FUNCTION: get_type_name <GA FPI>
1924
1925 DESCRIPTION: Given the 'document_type_code' and 'document_subtype',
1926 the function will return the 'document_type_name' from
1927 PO_DOCUMENTS_TYPES_VL.
1928
1929 ===========================================================================*/
1930
1931 FUNCTION get_type_name
1932 (
1933 p_document_type_code PO_DOCUMENT_TYPES_VL.document_type_code%TYPE ,
1934 p_document_subtype PO_DOCUMENT_TYPES_VL.document_subtype%TYPE
1935 )
1936 RETURN PO_DOCUMENT_TYPES_VL.type_name%TYPE
1937 IS
1938 x_type_name PO_DOCUMENT_TYPES_VL.type_name%TYPE;
1939 BEGIN
1940
1941 SELECT type_name
1942 INTO x_type_name
1943 FROM po_document_types_vl
1944 WHERE document_type_code = p_document_type_code
1945 AND document_subtype = p_document_subtype;
1946
1947 return (x_type_name);
1948
1949 EXCEPTION
1950
1951 WHEN OTHERS THEN
1952 return (NULL);
1953
1954 END get_type_name;
1955
1956 /* Get Conversion Rate Type
1957 /*===========================================================================
1958
1959 PROCEDURE NAME: get_rate_type()
1960
1961 ===========================================================================*/
1962 PROCEDURE get_rate_type (x_header_id IN NUMBER,
1963 x_rate_type OUT NOCOPY varchar2) is
1964 cursor c1 is
1965 select GLDC.USER_CONVERSION_TYPE from GL_DAILY_CONVERSION_TYPES GLDC
1966 where GLDC.CONVERSION_TYPE = (select POH.RATE_TYPE
1967 from po_headers_all POH
1968 where poh.po_header_id = x_header_id);
1969
1970 BEGIN
1971
1972 open c1;
1973 fetch c1 into x_rate_type;
1974 close c1;
1975
1976
1977 EXCEPTION
1978 WHEN OTHERS THEN
1979 x_rate_type := '';
1980
1981 END get_rate_type;
1982
1983 /* Bug 2788683 start */
1984 /**
1985 * Public Procedure: get_vendor_name
1986 * Requires: None
1987 * Modifies: None
1988 * Effects: Get vendor real name based on log on name
1989 * Returns: x_vendor_name
1990 */
1991 PROCEDURE get_vendor_name
1992 ( l_user_name IN fnd_user.user_name%TYPE,
1993 x_vendor_name OUT NOCOPY hz_parties.party_name%TYPE
1994 )
1995 IS
1996
1997 BEGIN
1998
1999 --SQL What: Query vendor name based on log on name
2000 --SQL Why: Get vendor_name from hz_parties via hz_relationships starting
2001 -- from fnd_user.user_name
2002 /* Patch 12345138 Modified the query because the query based on old
2003 datamodel was not fetching any records*/
2004 SELECT ap.vendor_name
2005 INTO x_vendor_name
2006 FROM hz_relationships hzr,
2007 hz_parties hp,
2008 fnd_user fu,
2009 ap_suppliers ap,
2010 hz_party_usg_assignments hpua
2011 WHERE
2012 hzr.object_id = ap.party_id
2013 AND hzr.subject_type = 'PERSON'
2014 AND hzr.object_type = 'ORGANIZATION'
2015 AND hzr.relationship_type = 'CONTACT'
2016 AND hzr.relationship_code = 'CONTACT_OF'
2017 AND hzr.status = 'A'
2018 AND Nvl(hzr.end_date, SYSDATE) >= sysdate
2019 AND hzr.subject_id = hp.party_id
2020 AND hpua.party_id = hp.party_id
2021 AND hpua.status_flag = 'A'
2022 AND hpua.party_usage_code = 'SUPPLIER_CONTACT'
2023 AND Nvl(hpua.effective_end_date, SYSDATE) >= SYSDATE
2024 AND fu.person_party_id = hp.party_id
2025 AND Nvl(fu.end_date, SYSDATE) >= sysdate
2026 and fu.user_name = l_user_name;
2027
2028 /* Bug 12345138 end*/
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 x_vendor_name := NULL;
2032
2033 END get_vendor_name;
2034
2035 /* Bug 2788683 end */
2036
2037 -- Bug 3816901: Created a procedure to get the displayed values for lookups
2038 -- This will be called from the post query logic instead of having this code
2039 -- multiple times for each lookup type
2040 ---------------------------------------------------------------------------
2041 --Start of Comments
2042 --Name: get_lookup_dsp
2043 --Pre-reqs:
2044 -- None.
2045 --Modifies:
2046 -- None
2047 --Locks:
2048 -- None.
2049 --Function:
2050 -- To get the displayed values for lookups
2051 --Parameters:
2052 --IN:
2053 --p_lookup_type
2054 -- The lookup type
2055 --p_lookup_code
2056 -- The lookup code for which we need he displayed value
2057 --Returns:
2058 -- The displayed value - translated value for the lookup code
2059 --Testing:
2060 --End of Comments
2061 ---------------------------------------------------------------------------
2062 PROCEDURE get_lookup_dsp (p_lookup_type IN PO_LOOKUP_CODES.lookup_type%TYPE,
2063 p_lookup_code IN PO_LOOKUP_CODES.lookup_code%TYPE,
2064 x_displayed_field OUT NOCOPY PO_LOOKUP_CODES.displayed_field%TYPE)
2065 IS
2066
2067 BEGIN
2068
2069 SELECT polc.displayed_field
2070 INTO x_displayed_field
2071 FROM po_lookup_codes polc
2072 WHERE lookup_type = p_lookup_type
2073 AND lookup_code = p_lookup_code;
2074
2075 Exception
2076 When others then
2077 x_displayed_field := null;
2078
2079 END get_lookup_dsp;
2080
2081 --<HTML Agreement R12 Start>
2082 ---------------------------------------------------------------------------
2083 --Start of Comments
2084 --Name: get_party_vendor_name
2085 --Pre-reqs:
2086 -- None.
2087 --Modifies:
2088 -- None
2089 --Locks:
2090 -- None.
2091 --Function:
2092 -- Retrieves the party name + vendor name for acceptance entries entered
2093 -- by suppliers. The result is in the form of "party name(vendor name)",
2094 -- e.g. "Maxwell Olden(Office Supplies, Inc.)".
2095 -- This function is refactored from POXPOEAC.pld
2096 --Parameters:
2097 --IN:
2098 -- p_user_id: user_id of the supplier party
2099 --Returns:
2100 -- "party name(vendor name)" of the given user_id
2101 --Testing:
2102 --End of Comments
2103 ---------------------------------------------------------------------------
2104 FUNCTION get_party_vendor_name (p_user_id IN NUMBER)
2105 RETURN VARCHAR2
2106 IS
2107 l_user_name FND_USER.USER_NAME%TYPE;
2108 l_party_name HZ_PARTIES.PARTY_NAME%TYPE;
2109 l_vendor_name HZ_PARTIES.PARTY_NAME%TYPE;
2110 l_result VARCHAR2(800); --approx. length to hold "party name(vendor name)"
2111 l_progress VARCHAR2(3) := '000';
2112 l_log_head VARCHAR2(100) := g_log_head||'get_party_vendor_name';
2113 BEGIN
2114 l_progress := '010';
2115
2116 -- SQL What: Retrieve user_name and party_name based on user_id
2117 -- SQL Why: Need to construct output and to further retrieve vendor_name
2118 SELECT fu.user_name, hp.party_name
2119 INTO l_user_name, l_party_name
2120 FROM fnd_user fu,hz_parties hp
2121 WHERE hp.party_id = fu.customer_id
2122 AND fu.user_id = p_user_id;
2123
2124 l_progress := '020';
2125 IF g_debug_stmt THEN
2126 PO_DEBUG.debug_stmt(p_log_head => l_log_head,
2127 p_token => l_progress,
2128 p_message => 'user name: '||l_user_name||' party name: '||l_party_name);
2129 END IF;
2130
2131 get_vendor_name(l_user_name => l_user_name,
2132 x_vendor_name => l_vendor_name);
2133
2134 l_progress := '030';
2135
2136 -- 'party_name(vendor_name)' is defined to be a message
2137 -- such that the translation of ( ) will be taken care of
2138 FND_MESSAGE.set_name('PO', 'PO_PARTY_VENDOR_NAME');
2139 FND_MESSAGE.set_token('PARTY_NAME', l_party_name);
2140 FND_MESSAGE.set_token('VENDOR_NAME', l_vendor_name);
2141
2142 l_result := FND_MESSAGE.get;
2143
2144 IF g_debug_stmt THEN
2145 PO_DEBUG.debug_stmt(p_log_head => l_log_head,
2146 p_token => l_progress,
2147 p_message => 'l_result: '||l_result);
2148 END IF;
2149 RETURN l_result;
2150
2151 EXCEPTION
2152 WHEN NO_DATA_FOUND THEN
2153 l_result := '';
2154 IF g_debug_stmt THEN
2155 PO_DEBUG.debug_stmt(p_log_head => l_log_head,
2156 p_token => l_progress,
2157 p_message => 'l_result: '||l_result);
2158 END IF;
2159 RETURN l_result;
2160 WHEN OTHERS THEN
2161 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
2162 p_procedure_name => 'get_party_vendor_name',
2163 p_error_text => 'Progress: '||l_progress||' Error: '||SUBSTRB(SQLERRM,1,215));
2164 IF g_debug_unexp THEN
2165 PO_DEBUG.debug_exc(p_log_head => l_log_head ||'get_party_vendor_name',
2166 p_progress => l_progress);
2167 END IF;
2168 RETURN NULL;
2169 END get_party_vendor_name;
2170
2171 ---------------------------------------------------------------------------
2172 --Start of Comments
2173 --Name: get_vendor_email
2174 --Pre-reqs:
2175 -- None.
2176 --Modifies:
2177 -- None
2178 --Locks:
2179 -- None.
2180 --Function:
2181 -- Retrieves the vendor party email address for acceptance entries entered
2182 -- by suppliers.
2183 --Parameters:
2184 --IN:
2185 -- p_user_id: user_id of the supplier party
2186 --Returns:
2187 -- email address stored in HZ_PARTIES; if that is not available, return
2188 -- email address stored in FND_USER
2189 --Testing:
2190 --End of Comments
2191 ---------------------------------------------------------------------------
2192 FUNCTION get_vendor_email (p_user_id IN NUMBER)
2193 RETURN VARCHAR2
2194 IS
2195 l_fu_email_address FND_USER.EMAIL_ADDRESS%TYPE; -- Varchar2(240)
2196 l_hp_email_address HZ_PARTIES.EMAIL_ADDRESS%TYPE; -- Varchar2(2000)
2197 l_result HZ_PARTIES.EMAIL_ADDRESS%TYPE;
2198 l_progress VARCHAR2(3) := '000';
2199 l_log_head VARCHAR2(100) := g_log_head||'get_vendor_email';
2200 BEGIN
2201 l_progress := '010';
2202
2203 -- SQL What: Retrieve email_address based on user_id
2204 -- SQL Why: Need to return email_address
2205 SELECT fu.email_address, hp.email_address
2206 INTO l_fu_email_address, l_hp_email_address
2207 FROM fnd_user fu,hz_parties hp
2208 WHERE hp.party_id = fu.customer_id
2209 AND fu.user_id = p_user_id;
2210
2211 l_progress := '020';
2212 IF g_debug_stmt THEN
2213 PO_DEBUG.debug_stmt(p_log_head => l_log_head,
2214 p_token => l_progress,
2215 p_message => 'l_fu_email_address: '||l_fu_email_address||' l_hp_email_address: '||l_hp_email_address);
2216 END IF;
2217
2218 IF l_hp_email_address IS NOT NULL THEN
2219 l_result := l_hp_email_address;
2220 ELSE
2221 l_result := l_fu_email_address;
2222 END IF;
2223
2224 RETURN l_result;
2225
2226 EXCEPTION
2227 WHEN NO_DATA_FOUND THEN
2228 IF g_debug_stmt THEN
2229 PO_DEBUG.debug_stmt(p_log_head => l_log_head,
2230 p_token => l_progress,
2231 p_message => 'No data found');
2232 END IF;
2233 RETURN NULL;
2234 WHEN OTHERS THEN
2235 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
2236 p_procedure_name => 'get_vendor_email',
2237 p_error_text => 'Progress: '||l_progress||' Error: '||SUBSTRB(SQLERRM,1,215));
2238 IF g_debug_unexp THEN
2239 PO_DEBUG.debug_exc(p_log_head => l_log_head ||'get_vendor_email',
2240 p_progress => l_progress);
2241 END IF;
2242 RETURN NULL;
2243 END get_vendor_email;
2244 --<HTML Agreement R12 End>
2245
2246 END PO_INQ_SV;
[Home] [Help]