1 PACKAGE BODY OE_ORDER_MISC_PUB AS
2 /* $Header: OEXPMISB.pls 115.4 2004/03/29 23:13:25 sdatti ship $ */
3
4
5 FUNCTION GET_CONCAT_LINE_NUMBER
6 (p_Line_Id IN NUMBER)
7 RETURN VARCHAR2
8
9 IS
10 p_Line_Number NUMBER;
11 p_Shipment_Number NUMBER;
12 p_Option_Number NUMBER;
13 p_component_Number NUMBER;
14 p_service_Number NUMBER;
15 x_concat_value VARCHAR2(30);
16 p_concat_value VARCHAR2(30);
17 BEGIN
18 IF p_line_id IS NULL
19 THEN
20 RETURN NULL;
21 END IF;
22 SELECT Line_Number,
23 Shipment_Number,
24 Option_Number,
25 Component_Number,
26 Service_Number
27 INTO p_Line_Number,
28 p_Shipment_Number,
29 p_Option_Number,
30 p_Component_Number,
31 p_Service_Number
32 FROM oe_order_lines_all
33 WHERE line_id=p_line_id;
34
35 IF p_service_number is not null then
36 IF p_option_number is not null then
37 IF p_component_number is not null then
38 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
39 p_option_number||'.'||p_component_number||'.'||
40 p_service_number;
41 ELSE
42 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
43 p_option_number||'..'||p_service_number;
44 END IF;
45
46 --- if a option is not attached
47 ELSE
48 IF p_component_number is not null then
49 p_concat_value := p_line_number||'.'||p_shipment_number||'..'||
50 p_component_number||'.'||p_service_number;
51 ELSE
52 p_concat_value := p_line_number||'.'||p_shipment_number||
53 '...'||p_service_number;
54 END IF;
55
56 END IF; /* if p_option_number number is not null */
57
58 -- if the service number is null
59 ELSE
60 IF p_option_number is not null then
61 IF p_component_number is not null then
62 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
63 p_option_number||'.'||p_component_number;
64 ELSE
65 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
66 p_option_number;
67 END IF;
68
69 --- if a option is not attached
70 ELSE
71 IF p_component_number is not null then
72 p_concat_value := p_line_number||'.'||p_shipment_number||'..'||
73 p_component_number;
74 ELSE
75 /*Bug2848734 - Added IF condition */
76 IF (p_line_number is NULL and p_shipment_number is NULL ) THEN
77 p_concat_value := NULL;
78 ELSE
79 p_concat_value := p_line_number||'.'||p_shipment_number;
80 END IF;
81 END IF;
82
83 END IF; /* if p_option_number number is not null */
84
85 END IF; /* if service number is not null */
86 x_concat_value :=p_concat_value;
87 RETURN x_concat_value;
88
89 EXCEPTION
90 WHEN too_many_rows THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92
93 WHEN no_data_found THEN
94 RETURN NULL;
95
96 WHEN others THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98
99 END GET_CONCAT_LINE_NUMBER;
100
101 FUNCTION GET_CONCAT_HIST_LINE_NUMBER
102 (p_Line_Id IN NUMBER,
103 p_Version_Number IN NUMBER)
104 RETURN VARCHAR2
105
106 IS
107 p_Line_Number NUMBER;
108 p_Shipment_Number NUMBER;
109 p_Option_Number NUMBER;
110 p_component_Number NUMBER;
111 p_service_Number NUMBER;
112 x_concat_value VARCHAR2(30);
113 p_concat_value VARCHAR2(30);
114 BEGIN
115 IF p_line_id IS NULL
116 THEN
117 RETURN NULL;
118 END IF;
119 SELECT Line_Number,
120 Shipment_Number,
121 Option_Number,
122 Component_Number,
123 Service_Number
124 INTO p_Line_Number,
125 p_Shipment_Number,
126 p_Option_Number,
127 p_Component_Number,
128 p_Service_Number
129 FROM OE_ORDER_LINES_HISTORY
130 WHERE line_id=p_line_id
131 AND version_number=p_version_number;
132
133 IF p_service_number is not null then
134 IF p_option_number is not null then
135 IF p_component_number is not null then
136 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
137 p_option_number||'.'||p_component_number||'.'||
138 p_service_number;
139 ELSE
140 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
141 p_option_number||'..'||p_service_number;
142 END IF;
143
144 --- if a option is not attached
145 ELSE
146 IF p_component_number is not null then
147 p_concat_value := p_line_number||'.'||p_shipment_number||'..'||
148 p_component_number||'.'||p_service_number;
149 ELSE
150 p_concat_value := p_line_number||'.'||p_shipment_number||
151 '...'||p_service_number;
152 END IF;
153
154 END IF; /* if p_option_number number is not null */
155
156 -- if the service number is null
157 ELSE
158 IF p_option_number is not null then
159 IF p_component_number is not null then
160 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
161 p_option_number||'.'||p_component_number;
162 ELSE
163 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
164 p_option_number;
165 END IF;
166
167 --- if a option is not attached
168 ELSE
169 IF p_component_number is not null then
170 p_concat_value := p_line_number||'.'||p_shipment_number||'..'||
171 p_component_number;
172 ELSE
173 /*Bug2848734 - Added IF condition */
174 IF (p_line_number is NULL and p_shipment_number is NULL ) THEN
175 p_concat_value := NULL;
176 ELSE
177 p_concat_value := p_line_number||'.'||p_shipment_number;
178 END IF;
179 END IF;
180
181 END IF; /* if p_option_number number is not null */
182
183 END IF; /* if service number is not null */
184 x_concat_value :=p_concat_value;
185 RETURN x_concat_value;
186
187 EXCEPTION
188 WHEN too_many_rows THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190
191 WHEN no_data_found THEN
192 RETURN NULL;
193
194 WHEN others THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196
197 END GET_CONCAT_HIST_LINE_NUMBER;
198
199 FUNCTION GET_CONCAT_HIST_LINE_NUMBER
200 (p_Line_Id IN NUMBER)
201 RETURN VARCHAR2
202
203 IS
204 p_Line_Number NUMBER;
205 p_Shipment_Number NUMBER;
206 p_Option_Number NUMBER;
207 p_component_Number NUMBER;
208 p_service_Number NUMBER;
209 x_concat_value VARCHAR2(30);
210 p_concat_value VARCHAR2(30);
211 BEGIN
212 IF p_line_id IS NULL
213 THEN
214 RETURN NULL;
215 END IF;
216 SELECT distinct Line_Number,
217 Shipment_Number,
218 Option_Number,
219 Component_Number,
220 Service_Number
221 INTO p_Line_Number,
222 p_Shipment_Number,
223 p_Option_Number,
224 p_Component_Number,
225 p_Service_Number
226 FROM OE_ORDER_LINES_HISTORY
227 WHERE line_id=p_line_id;
228
229 IF p_service_number is not null then
230 IF p_option_number is not null then
231 IF p_component_number is not null then
232 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
233 p_option_number||'.'||p_component_number||'.'||
234 p_service_number;
235 ELSE
236 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
237 p_option_number||'..'||p_service_number;
238 END IF;
239
240 --- if a option is not attached
241 ELSE
242 IF p_component_number is not null then
243 p_concat_value := p_line_number||'.'||p_shipment_number||'..'||
244 p_component_number||'.'||p_service_number;
245 ELSE
246 p_concat_value := p_line_number||'.'||p_shipment_number||
247 '...'||p_service_number;
248 END IF;
249
250 END IF; /* if p_option_number number is not null */
251
252 -- if the service number is null
253 ELSE
254 IF p_option_number is not null then
255 IF p_component_number is not null then
256 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
257 p_option_number||'.'||p_component_number;
258 ELSE
259 p_concat_value := p_line_number||'.'||p_shipment_number||'.'||
260 p_option_number;
261 END IF;
262
263 --- if a option is not attached
264 ELSE
265 IF p_component_number is not null then
266 p_concat_value := p_line_number||'.'||p_shipment_number||'..'||
267 p_component_number;
268 ELSE
269 /*Bug2848734 - Added IF condition */
270 IF (p_line_number is NULL and p_shipment_number is NULL ) THEN
271 p_concat_value := NULL;
272 ELSE
273 p_concat_value := p_line_number||'.'||p_shipment_number;
274 END IF;
275 END IF;
276
277 END IF; /* if p_option_number number is not null */
278
279 END IF; /* if service number is not null */
280 x_concat_value :=p_concat_value;
281 RETURN x_concat_value;
282
283 EXCEPTION
284 WHEN too_many_rows THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286
287 WHEN no_data_found THEN
288 RETURN NULL;
289
290 WHEN others THEN
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292
293 END GET_CONCAT_HIST_LINE_NUMBER;
294 END OE_ORDER_MISC_PUB;
[Home] [Help]