[Home] [Help]
PACKAGE BODY: APPS.IBE_ATP_PVT
Source
1 PACKAGE BODY IBE_ATP_PVT AS
2 /* $Header: IBEVATPB.pls 115.17 2003/08/29 09:08:49 nsultan ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IBE_ATP_PVT';
5
6 PROCEDURE Check_Availability (
7 p_quote_header_id IN NUMBER,
8 p_date_format IN VARCHAR2,
9 p_lang_code IN VARCHAR2,
10 x_error_flag OUT NOCOPY VARCHAR2,
11 x_error_message OUT NOCOPY VARCHAR2,
12 x_atp_line_tbl IN OUT NOCOPY ATP_Line_Tbl_Typ
13 )
14 IS
15
16 c_api_version CONSTANT NUMBER := 1.0;
17 c_api_name CONSTANT VARCHAR2(30) := 'Check_Availability';
18
19 l_date_format VARCHAR2(30);
20 l_lang_code VARCHAR2(10);
21
22 l_qte_line_tbl ASO_QUOTE_PUB.qte_line_tbl_type;
23 l_shipment_tbl ASO_QUOTE_PUB.shipment_tbl_type;
24 l_atp_tbl ASO_ATP_INT.atp_tbl_typ;
25 i BINARY_INTEGER;
26 j BINARY_INTEGER;
27
28 l_quote_line_id NUMBER;
29 l_organization_id NUMBER;
30 l_inventory_item_id NUMBER;
31 l_quantity NUMBER;
32 l_uom_code VARCHAR2(3);
33 l_request_date DATE;
34 l_sysdate DATE;
35
36 l_return_status VARCHAR2(1);
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(4000);
39 l_error_message VARCHAR2(4000);
40
41 E_IBE_ATP_BAD_DATE_FMT EXCEPTION;
42 E_IBE_ATP_NO_PREV_DATE EXCEPTION;
43
44 l_ship_site_use_id NUMBER;
45 Cursor get_site_use_id(l_party_site_id number) is
46 select site_use.site_use_id
47 from hz_party_sites party_site,
48 hz_cust_acct_sites_all acct_site,
49 hz_cust_site_uses_all site_use
50 where party_site.party_site_id = acct_site.party_site_id
51 and acct_site.cust_acct_site_id = site_use.cust_acct_site_id
52 and party_site.party_site_id = l_party_site_id;
53
54 BEGIN
55
56 x_error_flag := 'N';
57 x_error_message := '';
58
59 -- Make sure p_date_format and p_lang_code have meaningful values:
60 --
61 IF (p_date_format IS NULL OR LENGTH(p_date_format) = 0) THEN
62 l_date_format := 'DD-MON-RRRR';
63 ELSE
64 l_date_format := p_date_format;
65 END IF;
66
67 IF (p_lang_code IS NULL OR LENGTH(p_lang_code) = 0) THEN
68 l_lang_code := NVL(USERENV('LANG'), 'US');
69 ELSE
70 l_lang_code := p_lang_code;
71 END IF;
72
73 -- Always initialize API return message list:
74 --
75 FND_MSG_PUB.Initialize;
76
77 -- Capture sysdate to be used as default need-by date:
78 --
79 -- Changed for performace
80 -- SELECT SYSDATE INTO l_sysdate FROM DUAL;
81 l_sysdate := SYSDATE;
82
83 -- Populate quote line and shipment tables:
84 --
85 i := x_atp_line_tbl.FIRST;
86 j := 1;
87 LOOP
88
89 l_qte_line_tbl(j).quote_header_id :=
90 p_quote_header_id;
91 l_qte_line_tbl(j).quote_line_id :=
92 x_atp_line_tbl(i).quote_line_id;
93 l_qte_line_tbl(j).organization_id :=
94 x_atp_line_tbl(i).organization_id;
95 l_qte_line_tbl(j).inventory_item_id :=
96 x_atp_line_tbl(i).inventory_item_id;
97 l_qte_line_tbl(j).quantity :=
98 x_atp_line_tbl(i).quantity;
99 l_qte_line_tbl(j).uom_code :=
100 x_atp_line_tbl(i).uom_code;
101
102 -- Check whether Customer Id and site id are not null. If yes, then
103 -- get Site_use_id.
104 -- pass null for ship to cust account id and ship party site id if
105 -- site_use_id is null OR customer Id and site Id are null.
106 -- In that case ASO api will pass default Customer Id and site Use Id
107 IF x_atp_line_tbl(i).customer_id IS NOT NULL AND
108 x_atp_line_tbl(i).ship_to_site_id IS NOT NULL THEN
109 l_shipment_tbl(j).ship_to_cust_account_id :=
110 x_atp_line_tbl(i).customer_id;
111 -- Get Site Use Id from party_site_id. ASO_SHIPMENTS_V stores
112 -- Party_site_is instead of site_use_id.
113 open get_site_use_id(x_atp_line_tbl(i).ship_to_site_id);
114 fetch get_site_use_id into l_ship_site_use_id;
115 IF get_site_use_id%FOUND THEN
116 l_shipment_tbl(j).ship_to_party_site_id := l_ship_site_use_id;
117 ELSE
118 l_shipment_tbl(j).ship_to_party_site_id := NULL;
119 l_shipment_tbl(j).ship_to_cust_account_id := NULL;
120 END IF;
121 close get_site_use_id;
122 ELSE
123 l_shipment_tbl(j).ship_to_party_site_id := NULL;
124 l_shipment_tbl(j).ship_to_cust_account_id := NULL;
125 END IF;
126 l_shipment_tbl(j).ship_method_code :=
127 x_atp_line_tbl(i).ship_method_code;
128
129 IF (x_atp_line_tbl(i).request_date IS NULL OR
130 x_atp_line_tbl(i).request_date = '') THEN
131 l_shipment_tbl(j).request_date := l_sysdate;
132 BEGIN
133 x_atp_line_tbl(i).request_date := TO_CHAR(l_sysdate, l_date_format);
134 EXCEPTION
135 WHEN OTHERS THEN
136 x_error_message := SQLERRM;
137 RAISE E_IBE_ATP_BAD_DATE_FMT;
138 END;
139 ELSE
140 BEGIN
141 l_shipment_tbl(j).request_date :=
142 TO_DATE(x_atp_line_tbl(i).request_date, l_date_format);
143 EXCEPTION
144 WHEN OTHERS THEN
145 x_error_message := SQLERRM;
146 RAISE E_IBE_ATP_BAD_DATE_FMT;
147 END;
148 END IF;
149
150 IF x_atp_line_tbl(i).request_date IS NOT NULL AND
151 to_date(x_atp_line_tbl(i).request_date,l_date_format) < to_date(l_sysdate,l_date_format) THEN
152 RAISE E_IBE_ATP_NO_PREV_DATE;
153 END IF;
154
155 IF i < x_atp_line_tbl.LAST THEN
156 i := x_atp_line_tbl.NEXT(i);
157 j := j + 1;
158 ELSE
159 EXIT;
160 END IF;
161
162 END LOOP;
163
164 -- Check availability:
165 --
166 ASO_ATP_INT.Check_ATP (
167 p_api_version_number => c_api_version,
168 p_init_msg_list => FND_API.G_TRUE,
169 p_qte_line_tbl => l_qte_line_tbl,
170 p_shipment_tbl => l_shipment_tbl,
171 x_return_status => l_return_status,
172 x_msg_count => l_msg_count,
173 x_msg_data => l_msg_data,
174 x_atp_tbl => l_atp_tbl
175 );
176
177 -- Standard check for error:
178 --
179 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
180 RAISE FND_API.G_EXC_ERROR;
181 ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183 END IF;
184
185 -- Copy results back into x_atp_line_tbl:
186 --
187 IF l_atp_tbl.COUNT = x_atp_line_tbl.COUNT THEN
188
189 i := l_atp_tbl.FIRST;
190 j := x_atp_line_tbl.FIRST;
191
192 LOOP
193
194 -- Get quantity available on the date requested:
195 --
196 x_atp_line_tbl(j).request_date_quantity :=
197 l_atp_tbl(i).request_date_quantity;
198
199 -- Get earliest date on which requested quantity is available:
200 --
201 BEGIN
202 x_atp_line_tbl(j).available_date :=
203 TO_CHAR(l_atp_tbl(i).ship_date, l_date_format);
204 EXCEPTION
205 WHEN OTHERS THEN
206 x_error_message := SQLERRM;
207 RAISE E_IBE_ATP_BAD_DATE_FMT;
208 END;
209
210 -- Get error code:
211 --
212 x_atp_line_tbl(j).error_code := l_atp_tbl(i).error_code;
213
214 -- If error, get error message; if message is NULL, look it up:
215 --
216 IF l_atp_tbl(i).error_code <> 0 THEN
217
218 IF (l_atp_tbl(i).message IS NOT NULL AND
219 LENGTH(l_atp_tbl(i).message) > 0)
220 THEN
221 x_atp_line_tbl(j).error_message := l_atp_tbl(i).message;
222 ELSE
223 BEGIN
224 SELECT meaning
225 INTO x_atp_line_tbl(j).error_message
226 FROM fnd_lookup_values
227 WHERE language = l_lang_code
228 AND view_application_id = 700
229 AND security_group_id =
230 fnd_global.lookup_security_group(lookup_type,
231 view_application_id)
232 AND lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
233 AND TO_NUMBER(lookup_code) = l_atp_tbl(i).error_code;
234 EXCEPTION
235 WHEN OTHERS THEN
236 x_atp_line_tbl(j).error_message := 'Unknown error';
237 END;
238 END IF;
239
240 END IF;
241
242 IF i < l_atp_tbl.LAST THEN
243 i := l_atp_tbl.NEXT(i);
244 j := x_atp_line_tbl.NEXT(j);
245 ELSE
246 EXIT;
247 END IF;
248
249 END LOOP;
250
251 ELSE
252
253 -- Rowcount mismatch:
254 --
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256
257 END IF;
258
259 RETURN;
260
261 EXCEPTION
262
263 WHEN E_IBE_ATP_BAD_DATE_FMT THEN
264 x_error_flag := 'Y';
265 x_error_message := 'Date format error ('||x_error_message||')';
266
267 WHEN E_IBE_ATP_NO_PREV_DATE THEN
268 x_error_flag := 'Y';
269 x_error_message := 'No previous date ('||x_error_message||')';
270
271 WHEN FND_API.G_EXC_ERROR THEN
272 x_error_flag := 'Y';
273 x_error_message := null;--'ASO_ATP_INT internal error. ';
274 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
275 p_count => l_msg_count,
276 p_data => l_msg_data);
277 IF l_msg_count > 1 THEN
278 LOOP
279 l_error_message := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
280 IF l_error_message IS NULL THEN
281 EXIT;
282 ELSE
283 x_error_message := x_error_message||l_error_message||' ';
284 END IF;
285 END LOOP;
286 ELSIF l_msg_count = 1 THEN
287 x_error_message := x_error_message||l_msg_data;
288 END IF;
289
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 x_error_flag := 'Y';
292 x_error_message := 'ASO_ATP_INT unexpected internal error. ';
293 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
294 p_count => l_msg_count,
295 p_data => l_msg_data);
296 IF l_msg_count > 1 THEN
297 LOOP
298 l_error_message := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
299 IF l_error_message IS NULL THEN
300 EXIT;
301 ELSE
302 x_error_message := x_error_message||l_error_message||' ';
303 END IF;
304 END LOOP;
305 ELSIF l_msg_count = 1 THEN
306 x_error_message := x_error_message||l_msg_data;
307 END IF;
308
309 WHEN OTHERS THEN
310 x_error_flag := 'Y';
311 x_error_message := 'IBE_ATP_PVT unexpected internal error. ';
312 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
313 FND_MESSAGE.Set_Token('ROUTINE', c_api_name);
314 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
315 FND_MESSAGE.Set_Token('REASON', SQLERRM);
316 FND_MSG_PUB.Add;
317 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
318 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, c_api_name);
319 END IF;
320 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
321 p_count => l_msg_count,
322 p_data => l_msg_data);
323 IF l_msg_count > 1 THEN
324 LOOP
325 l_error_message := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
326 IF l_error_message IS NULL THEN
327 EXIT;
328 ELSE
329 x_error_message := x_error_message||l_error_message||' ';
330 END IF;
331 END LOOP;
332 ELSIF l_msg_count = 1 THEN
333 x_error_message := x_error_message||l_msg_data;
334 END IF;
335
336 END Check_Availability;
337
338 END IBE_ATP_PVT;