1 PACKAGE BODY WSH_CORE AS
2 /* $Header: WSHCOREB.pls 115.2 99/08/11 12:23:45 porting shi $ */
3
4
5 -- Name item_flex_name
6 -- Purpose converts inventory_item_id into its name
7 -- Assumption The id parameters are valid.
8 -- Arguments
9 -- inventory_item_id
10 -- warehouse_id
11 -- RETURN VARCHAR if name not found, '?' will be returned.
12 FUNCTION item_flex_name(inventory_item_id IN NUMBER,
13 warehouse_id IN NUMBER)
14 RETURN VARCHAR2
15 IS
16 name VARCHAR(2000) := '?';
17 result BOOLEAN := TRUE;
18 BEGIN
19 result := FND_FLEX_KEYVAL.validate_ccid(
20 appl_short_name=>'INV',
21 key_flex_code=>'MSTK',
22 structure_number=>101,
23 combination_id=>inventory_item_id,
24 data_set=>warehouse_id);
25 IF result THEN
26 name := FND_FLEX_KEYVAL.concatenated_values;
27 END IF;
28 RETURN name;
29 END item_flex_name;
30
31
32 -- Name locator_flex_name
33 -- Purpose Converts Locator_id into its name
34 -- Assumption The id parameters are valid.
35 --
36 FUNCTION locator_flex_name(locator_id IN NUMBER,
37 warehouse_id IN NUMBER)
38 RETURN VARCHAR2
39 IS
40 name VARCHAR(2000) := '?';
41 result BOOLEAN := TRUE;
42 BEGIN
43 result := FND_FLEX_KEYVAL.validate_ccid(
44 appl_short_name=>'INV',
45 key_flex_code=>'MTLL',
46 structure_number=>101,
47 combination_id=>locator_id,
48 data_set=>warehouse_id);
49 IF result THEN
50 name := FND_FLEX_KEYVAL.concatenated_values;
51 END IF;
52 RETURN name;
53 END locator_flex_name;
54
55
56 -- Name generic_flex_name
57 -- Purpose converts entity_id into its name
58 -- Arguments
59 -- entity_id
60 -- warehouse_id
61 -- app_name (short app name; e.g. 'INV')
62 -- k_flex_code (key flexfield code; e.g., 'MSTK')
63 -- struct_num (structure number; e.g., 101)
64 -- Assumption The parameters are valid.
65 -- RETURN VARCHAR2 if name not found, NULL will be returned.
66 FUNCTION generic_flex_name(
67 entity_id IN NUMBER,
68 warehouse_id IN NUMBER,
69 app_name IN VARCHAR2,
70 k_flex_code IN VARCHAR2,
71 struct_num IN NUMBER)
72 RETURN VARCHAR2
73 IS
74 name VARCHAR(2000) := NULL;
75 result BOOLEAN := TRUE;
76 BEGIN
77 result := FND_FLEX_KEYVAL.validate_ccid(
78 appl_short_name=>'INV',
79 key_flex_code=>k_flex_code,
80 structure_number=>struct_num,
81 combination_id=>entity_id,
82 data_set=>warehouse_id);
83 IF result THEN
84 name := FND_FLEX_KEYVAL.concatenated_values;
85 END IF;
86 RETURN name;
87 END generic_flex_name;
88
89
90 -- Name shipper_address
91 -- Purpose obtain the shipper's address (ie the shipping warehouse).
92 -- Assumption org_id exists, and the address is available.
93 -- Input Argument
94 -- org_id
95 -- Output Arguments (all are VARCHAR2(30))
96 -- org_name
97 -- address1
98 -- address2
99 -- address3
100 -- city
101 -- region (state)
102 -- postal_code (zip)
103 -- country
104 --
105 PROCEDURE Shipper_Address(
106 org_id in number,
107 org_name out varchar2,
108 address1 out varchar2,
109 address2 out varchar2,
110 address3 out varchar2,
111 city out varchar2,
112 region out varchar2,
113 postal_code out varchar2,
114 country out varchar2)
115 IS
116
117 CURSOR shipper(o_id NUMBER) IS
118 select ou.name org_name,
119 loc.address_line_1 ship_address1,
120 loc.address_line_2 ship_address2,
121 loc.address_line_3 ship_address3,
122 loc.town_or_city city,
123 loc.region_2 region,
124 loc.postal_code postal_code,
125 terr.territory_short_name country
126 from hr_organization_units ou,
127 fnd_territories_VL terr,
128 hr_locations_no_join loc
129 where ou.organization_id = o_id
130 and loc.location_id(+) = ou.location_id
131 and loc.country = terr.territory_code(+);
132
133 BEGIN
134
135 open shipper(org_id);
136 fetch shipper into
137 org_name,
138 address1,
139 address2,
140 address3,
141 city,
142 region,
143 postal_code,
144 country;
145 close shipper;
146
147 END;
148
149
150 -- Name city_region_postal
151 -- Purpose concatenates the three fields for the reports
152 -- Input Arguments
153 -- city
154 -- region (state)
155 -- postal_code (zip)
156 -- RETURN VARCHAR2
157 --
158 FUNCTION city_region_postal(
159 city in varchar2,
160 region in varchar2,
161 postal_code in varchar2)
162 RETURN VARCHAR2
163 IS
164 c_r_p VARCHAR2(100);
165 BEGIN
166
167 IF city IS NOT NULL AND region IS NOT NULL THEN
168 c_r_p := city || ', ' || region || ' ' || postal_code;
169 ELSIF city IS NOT NULL AND region IS NULL THEN
170 -- bug 958797 (from bug 944851): use city instead of c_r_p
171 c_r_p := city || ' ' || postal_code;
172 ELSIF city IS NULL AND region IS NOT NULL THEN
173 c_r_p := region || ' ' || postal_code;
174 ELSIF city IS NULL AND region IS NULL THEN
175 c_r_p := postal_code;
176 END IF;
177
178 RETURN c_r_p;
179
180 END;
181
182 END WSH_CORE;
[Home] [Help]