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