1 PACKAGE BODY PO_COO_S AS
2 /* $Header: POXRCOOB.pls 120.0.12000000.3 2007/09/28 10:56:23 vrecharl ship $*/
3
4 PROCEDURE get_default_country_of_origin(
5 x_item_id IN NUMBER,
6 x_ship_to_org_id IN NUMBER,
7 x_vendor_id IN NUMBER,
8 x_vendor_site_id IN NUMBER,
9 x_country_of_origin IN OUT NOCOPY VARCHAR2) IS
10
11 CURSOR C is
12 SELECT paa.country_of_origin_code
13 FROM po_approved_supplier_lis_val_v pasl,
14 po_asl_attributes paa
15 WHERE pasl.item_id = x_item_id
16 AND pasl.vendor_id = x_vendor_id
17 AND nvl(pasl.vendor_site_id, -1) = nvl(x_vendor_site_id, -1)
18 AND pasl.using_organization_id IN (-1, x_ship_to_org_id)
19 AND paa.asl_id = pasl.asl_id
20 ORDER BY paa.using_organization_id DESC;
21
22 BEGIN
23
24 if x_item_id is not null then
25 begin
26
27 open c;
28 fetch c into x_country_of_origin;
29 close c;
30
31 end;
32
33 if x_country_of_origin is null then
34 begin
35 select country_of_origin_code
36 into x_country_of_origin
37 from po_vendor_sites_all
38 where vendor_id = x_vendor_id
39 and vendor_site_id = x_vendor_site_id
40 and rownum = 1;
41
42 EXCEPTION
43 WHEN NO_DATA_FOUND THEN x_country_of_origin := NULL;
44 WHEN OTHERS THEN raise;
45 end;
46 end if;
47 -- bug#5917646 added this elsif condition to default country of origin for one-time item
48 -- based on vendor and vendor site.
49 -- bug#5917646 <statrt>
50 elsif x_item_id is null then
51 begin
52 select country_of_origin_code
53 into x_country_of_origin
54 from po_vendor_sites_all
55 where vendor_id = x_vendor_id
56 and vendor_site_id = x_vendor_site_id
57 and rownum = 1;
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND THEN x_country_of_origin := NULL;
61 WHEN OTHERS THEN raise;
62 end;
63 -- bug#5917646 <end>
64 end if; --x_item_id not null
65
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 x_country_of_origin := NULL;
69 WHEN OTHERS THEN
70 RAISE;
71
72 end;
73
74 END PO_COO_S;
[Home] [Help]