[Home] [Help]
PACKAGE BODY: APPS.CSP_PARTS_REQUIREMENT
Source
1 PACKAGE BODY CSP_PARTS_REQUIREMENT AS
2 /* $Header: cspvprqb.pls 120.4.12010000.3 2008/11/05 11:52:22 htank ship $ */
3
4 -- Purpose: Create/Update/Cancel Internal Parts Requirements for Spares
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- phegde 05/17/01 Created new package body
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'csp_parts_requirement';
12 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspvprqb.pls';
13
14 G_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
15
16 PROCEDURE Get_source_organization (
17 P_Inventory_Item_Id IN NUMBER,
18 P_Organization_Id IN NUMBER,
19 P_Secondary_Inventory IN VARCHAR2,
20 x_source_org_id OUT NOCOPY NUMBER
21 )
22 IS
23
24 Cursor c_Get_Source_Org(p_org_id Number) Is
25 Select SOURCE_TYPE,
26 SOURCE_ORGANIZATION_ID,
27 SOURCE_SUBINVENTORY
28 From MTL_PARAMETERS
29 Where ORGANIZATION_ID = p_org_id;
30
31 Cursor c_Get_Source_Subinv(p_org_id Number,p_subinv Varchar2) Is
32 Select SOURCE_TYPE,
33 SOURCE_ORGANIZATION_ID,
34 SOURCE_SUBINVENTORY
35 From MTL_SECONDARY_INVENTORIES
36 Where ORGANIZATION_ID = p_org_id
37 And SECONDARY_INVENTORY_NAME = p_subinv;
38
39 Cursor c_Get_Source_Item(p_inventory_item_id Number,p_org_id Number) Is
40 Select SOURCE_TYPE,
41 SOURCE_ORGANIZATION_ID,
42 SOURCE_SUBINVENTORY
43 From MTL_SYSTEM_ITEMS
44 Where INVENTORY_ITEM_ID = p_inventory_item_id
45 And ORGANIZATION_ID = p_org_id;
46
47 Cursor c_Get_Source_Item_Subinv(p_inventory_item_id Number,p_org_id Number,p_subinventory Varchar2) Is
48
49 Select SOURCE_TYPE,
50 SOURCE_ORGANIZATION_ID,
51 SOURCE_SUBINVENTORY
52 From MTL_ITEM_SUB_INVENTORIES
53 Where INVENTORY_ITEM_ID = p_inventory_item_id
54 And ORGANIZATION_ID = p_org_id
55 And SECONDARY_INVENTORY = p_subinventory;
56
57 l_return_status_full VARCHAR2(1);
58 l_Sqlcode NUMBER;
59 l_Sqlerrm Varchar2(2000);
60 l_api_name CONSTANT VARCHAR2(30) := 'get_source_organization';
61
62 l_source_org_rec c_Get_Source_Org%ROWTYPE;
63
64 l_Inventory_Item_Id Number := 0;
65 l_Organization_Id Number := 0;
66 l_Secondary_Inventory Varchar2(10);
67
68 BEGIN
69 --
70 -- API body
71 --
72 l_Inventory_Item_Id := P_Inventory_Item_Id;
73 l_Organization_Id := P_Organization_Id;
74 l_Secondary_Inventory := P_Secondary_Inventory;
75
76 l_source_org_rec := NULL;
77
78 --- Check to see the source for Item And Subinventory
79 If (l_Secondary_Inventory is NOT NULL AND l_Organization_id is NOT NULL) Then
80
81 Open c_Get_Source_Item_Subinv(p_Inventory_Item_Id ,l_Organization_Id,l_Secondary_Inventory);
82 Fetch c_Get_Source_Item_Subinv Into l_Source_Org_Rec;
83 Close c_Get_Source_Item_Subinv;
84 END If;
85
86 -- If source organization is null, Check to see the source in Item definition
87
88 If (l_source_org_rec.source_organization_id IS NULL) Then
89 Open c_Get_Source_Item(p_Inventory_Item_Id,l_Organization_Id);
90 Fetch c_Get_Source_Item Into l_Source_Org_Rec;
91 Close c_Get_Source_Item;
92 END If;
93
94 --- IF source_organiZation_id is still null, Check to see the source in Subinventory definition
95
96 IF (l_source_org_Rec.source_organization_id IS NULL and l_secondary_inventory IS NOT NULL) THEN
97
98 Open c_Get_Source_Subinv(l_Organization_Id,l_Secondary_Inventory);
99 Fetch c_Get_Source_Subinv Into l_Source_Org_Rec;
100 Close c_Get_Source_Subinv;
101 END If;
102
103 --- Check to see the source in Organization definition
104 if (l_Source_org_Rec.source_organization_id IS NULL and l_organization_id IS NOT NULL) THEN
105
106 Open c_Get_Source_Org(l_Organization_Id);
107 Fetch c_Get_Source_Org INTO l_Source_Org_Rec;
108 Close c_Get_Source_Org;
109 End If;
110
111 -- Assign output variables
112 x_source_org_id := l_source_org_Rec.source_organization_id;
113
114 --
115 -- End of API body
116 --
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 l_sqlcode := SQLCODE;
121 l_sqlerrm := SQLERRM;
122 l_source_org_rec.source_organization_id := FND_API.G_MISS_NUM;
123
124 End Get_source_organization;
125
126
127 PROCEDURE process_requirement
128 ( p_api_version IN NUMBER
129 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
130 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
131 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
132 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
133 ,p_create_order_flag IN VARCHAR2
134 ,x_return_status OUT NOCOPY VARCHAR2
135 ,x_msg_count OUT NOCOPY NUMBER
136 ,x_msg_data OUT NOCOPY VARCHAR2
137 ) IS
138 l_api_version_number CONSTANT NUMBER := 1.0;
139 l_api_name CONSTANT VARCHAR2(30) := 'process_requirement';
140 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
141 l_msg_count NUMBER;
142 l_msg_data VARCHAR2(2000);
143 l_msg_dummy VARCHAR2(2000);
144 l_order_msg VARCHAR2(8000);
145 l_commit VARCHAR2(1) := FND_API.G_FALSE;
146 l_user_id NUMBER;
147 l_login_id NUMBER;
148 l_today DATE;
149 EXCP_USER_DEFINED EXCEPTION;
150 l_check_existence NUMBER;
151 l_window BOOLEAN;
152
153 l_requirement_header_id NUMBER ;
154 l_requirement_line_id NUMBER;
155 l_parts_defined VARCHAR2(30);
156
157 l_header_rec Header_Rec_Type;
158 l_line_rec Line_rec_type;
159 l_line_tbl Line_Tbl_type;
160
161 l_rqmt_header_Rec csp_requirement_headers_pvt.requirement_header_rec_type;
162
163 l_rqmt_line_Rec csp_requirement_lines_pvt.requirement_line_rec_type;
164 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
165 x_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
166 l_req_line_details_tbl csp_parts_requirement.Line_detail_Tbl_Type;
167 j NUMBER;
168
169 --Record types and tbl types for finding availability of parts
170 l_parts_list_rec csp_sch_int_pvt.csp_parts_rec_type;
171 l_parts_list_tbl csp_sch_int_pvt.csp_parts_tbl_typ1;
172 l_avail_list_tbl csp_sch_int_pvt.available_parts_tbl_typ1;
173 l_resource_rec csp_sch_int_pvt.csp_sch_resources_rec_typ;
174 l_req_line_Dtl_id NUMBER;
175 l_timezone_id NUMBER;
176
177 CURSOR rs_loc_cur(p_resource_type VARCHAR2, p_resource_id NUMBER) IS
178 SELECT pla.location_id inv_loc_id,
179 hzl.time_zone
180 from csp_rs_cust_relations rcr,
181 hz_cust_acct_sites cas,
182 hz_cust_site_uses csu,
183 po_location_associations pla,
184 hz_party_sites ps,
185 hz_locations hzl
186 where rcr.customer_id = cas.cust_account_id
187 and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
188 and csu.site_use_code = 'SHIP_TO'
189 and csu.site_use_id = pla.site_use_id
190 and cas.party_site_id = ps.party_site_id
191 and ps.location_id = hzl.location_id
192 and csu.primary_flag = 'Y'
193 and rcr.resource_type = p_resource_type
194 and rcr.resource_id = p_resource_id;
195
196 CURSOR task_asgnmt_loc_cur(p_task_Assignment_id NUMBER) IS
197 SELECT pla.location_id inv_loc_id,
198 hzl.time_zone timezone_id
199 from csp_rs_cust_relations rcr,
200 hz_cust_acct_sites cas,
201 hz_cust_site_uses csu,
202 po_location_associations pla,
203 hz_party_sites ps,
204 hz_locations hzl,
205 jtf_task_assignments jta
206 where rcr.customer_id = cas.cust_account_id
207 and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
208 and csu.site_use_code = 'SHIP_TO'
209 and csu.site_use_id = pla.site_use_id
210 and cas.party_site_id = ps.party_site_id
211 and ps.location_id = hzl.location_id
212 and csu.primary_flag = 'Y'
213 and rcr.resource_type = jta.resource_type_code
214 and rcr.resource_id = jta.resource_id
215 and jta.task_assignment_id = p_task_assignment_id;
216
217 BEGIN
218 SAVEPOINT Process_Requirement_PUB;
219
220 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
221 -- initialize message list
222 FND_MSG_PUB.initialize;
223 END IF;
224
225 -- Standard call to check for call compatibility.
226 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
227 p_api_version,
228 l_api_name,
229 G_PKG_NAME)
230 THEN
231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
232 END IF;
233
234 -- initialize return status
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236
237 l_header_Rec := px_header_rec;
238 l_line_tbl := px_line_table;
239
240 -- user and login information
241 SELECT Sysdate INTO l_today FROM dual;
242 l_user_id := fnd_global.user_id;
243 l_login_id := fnd_global.login_id;
244
245 -- call availability API to get source organization
246 l_resource_rec.resource_type := l_header_Rec.resource_type;
247 l_resource_Rec.resource_id := l_header_Rec.resource_id;
248
249 FOR I IN 1..l_line_tbl.COUNT LOOP
250 IF l_line_tbl(I).source_organization_id IS NULL THEN
251 l_parts_list_rec.item_id := l_line_tbl(I).inventory_item_id;
252 l_parts_list_rec.revision := l_line_Tbl(I).revision;
253 l_parts_list_rec.item_uom := l_line_tbl(I).unit_of_measure;
254 l_parts_list_rec.quantity := l_line_tbl(I).ordered_quantity;
255 l_parts_list_rec.ship_set_name := l_line_tbl(I).ship_complete;
256 l_parts_list_rec.line_id := l_line_tbl(I).line_num;
257 l_parts_list_tbl(i) := l_parts_list_rec;
258 END IF;
259 END LOOP;
260 IF l_parts_list_tbl.count > 0 THEN
261
262 IF sysdate < l_header_rec.need_by_date THEN
263 -- call csp_sch_int_pvt.check_parts_availability()
264 csp_sch_int_pvt.check_parts_availability(
265 p_resource => l_resource_rec,
266 p_organization_id => l_header_rec.dest_organization_id,
267 p_subinv_code => null,
268 p_need_by_date => l_header_rec.need_by_date,
269 p_parts_list => l_parts_list_tbl,
270 p_timezone_id => null,
271 x_availability => l_avail_list_tbl,
272 x_return_status => l_return_status,
273 x_msg_data => l_msg_data,
274 x_msg_count => l_msg_count,
275 p_called_from => 'MOBILE'
276 );
277 ELSE
278 FND_MESSAGE.Set_Name('CSP', 'CSP_NEED_PASSED');
279 l_order_msg := FND_MESSAGE.GET;
280 END IF;
281 END IF;
282 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
283 null; --RAISE FND_API.G_EXC_ERROR;
284 END IF;
285 if l_msg_count > 0 THEN
286 for j in 1..fnd_msg_pub.count_msg loop
287 fnd_msg_pub.get(
288 j,
289 FND_API.G_FALSE,
290 l_msg_data,
291 l_msg_dummy);
292 l_order_msg := l_order_msg || l_msg_data;
293 end loop;
294 END IF;
295 J := 1;
296 IF (l_Avail_list_tbl.COUNT > 0) THEN
297 FOR I IN 1..l_line_tbl.COUNT LOOP
298 IF (l_avail_list_tbl(J).item_id = l_line_tbl(I).inventory_item_id) THEN
299 IF (l_line_tbl(I).source_organization_id IS NULL) THEN
300 l_line_tbl(I).source_organization_id := l_avail_list_tbl(J).source_org_id;
301 l_line_tbl(I).shipping_method_code := l_avail_list_tbl(J).shipping_methode;
302 l_line_Tbl(I).order_by_date := l_Avail_list_tbl(J).order_by_date;
303 END IF;
304 J := J+1;
305 END IF;
306 END LOOP;
307 END IF;
308
309 -- check to see if any of the lines have null source org and if yes, get source organization defined
310 -- for replenishment at item/subinventory/organization level
311 FOR I IN 1..l_line_tbl.COUNT LOOP
312 IF (l_line_Tbl(I).source_organization_id IS NULL) THEN
313 Get_source_organization(P_Inventory_Item_Id => l_line_Tbl(I).inventory_item_id,
314 P_Organization_Id => l_header_Rec.dest_organization_id,
315 P_Secondary_Inventory => l_header_rec.dest_subinventory,
316 x_source_org_id => l_line_Tbl(I).source_organization_id);
317 IF ((l_line_Tbl(I).source_organization_id IS NULL) OR
318 (l_line_Tbl(I).source_organization_id = FND_API.G_MISS_NUM)) THEN
319 -- no source organization defined, create requirement with error status
320 l_rqmt_header_rec.open_requirement := 'E';
321 END IF;
322 END If;
323 END LOOP;
324
325 -- find default ship to of resource if ship_to_location_id is null
326 IF (l_header_Rec.ship_to_location_id IS NULL) THEN
327 -- check if task_assignment_id is passed
328 IF ((l_header_Rec.task_assignment_id IS NOT NULL) AND
329 (l_header_Rec.resource_type IS NULL OR l_header_rec.resource_id IS NULL)) THEN
330 -- get resource and default addr of resource based on task assignment
331 OPEN task_asgnmt_loc_cur(l_header_rec.task_assignment_id);
332 FETCH task_asgnmt_loc_cur INTO l_header_rec.ship_to_location_id, l_timezone_id;
333 CLOSE task_asgnmt_loc_cur;
334 ELSIF (l_header_Rec.resource_type IS NOT NULL AND l_header_rec.resource_id IS NOT NULL) THEN
335 -- get default ship-to location of resource
336 OPEN rs_loc_cur(l_header_rec.resource_type, l_header_Rec.resource_id);
337 FETCH rs_loc_cur into l_header_Rec.ship_to_location_id, l_timezone_id;
338 CLOSE rs_loc_cur;
339 ELSE
340 -- raise error, either ship to location or resource must be specified
341 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
342 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'resource or task assignment or ship to location', FALSE);
343 FND_MSG_PUB.ADD;
344 RAISE EXCP_USER_DEFINED;
345 END If;
346 ELSE
347 BEGIN
348 SELECT hzl.time_zone time_zone_id
349 INTO l_timezone_id
350 from hz_cust_acct_sites cas,
351 hz_cust_site_uses csu,
352 po_location_associations pla,
353 hz_party_sites ps,
354 hz_locations hzl
355 where cas.cust_acct_site_id = csu.cust_acct_site_id (+)
356 and csu.site_use_code = 'SHIP_TO'
357 and csu.site_use_id = pla.site_use_id
358 and pla.location_id = l_header_rec.ship_to_location_id
359 and cas.party_site_id = ps.party_site_id
360 and ps.location_id = hzl.location_id;
361 EXCEPTION
362 when no_data_found then
363 null;
364 when others then
365 null;
366 END;
367 END IF;
368
369 IF (p_create_order_flag = 'Y' AND l_rqmt_header_Rec.open_requirement <> 'E') THEN
370
371 -- call csp_process_order API
372 csp_parts_order.process_order(
373 p_api_version => l_api_version_number
374 ,p_Init_Msg_List => p_init_msg_list
375 ,p_commit => FND_API.G_false
376 ,px_header_rec => l_header_rec
377 ,px_line_table => l_line_tbl
378 ,x_return_status => l_return_status
379 ,x_msg_count => l_msg_count
380 ,x_msg_data => l_msg_data
381 );
382
383 -- set parts_defined to 'Y' if order has been created for this requirement
384 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
385 l_parts_defined := 'Y';
386 l_rqmt_header_rec.open_requirement := 'Y';
387 px_header_rec.order_header_id := l_header_Rec.order_header_id;
388 l_order_msg := null;
389 ELSE
390 l_rqmt_header_rec.open_requirement := 'E';
391 for j in 1..fnd_msg_pub.count_msg loop
392 fnd_msg_pub.get(
393 j,
394 FND_API.G_FALSE,
395 l_msg_data,
396 l_msg_dummy);
397 l_order_msg := l_order_msg || l_msg_data;
398 end loop;
399 END IF;
400 END IF;
401
402 -- SETTING UP THE PARTS REQUIREMENT HEADER RECORD
403 IF (l_header_rec.operation = G_OPR_CREATE) THEN
404 l_rqmt_header_Rec.created_by := nvl(l_user_id, -1);
405 l_rqmt_header_Rec.creation_date := l_today;
406 ELSIF (l_header_rec.operation IN (G_OPR_UPDATE, G_OPR_DELETE)) THEN
407 IF nvl(l_header_rec.requirement_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
408 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
409 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_header_rec.requirement_header_id', FALSE);
410 FND_MSG_PUB.ADD;
411 RAISE EXCP_USER_DEFINED;
412 ELSE
413 BEGIN
414 select requirement_header_id
415 into l_check_existence
416 from csp_requirement_headers
417 where requirement_header_id = l_header_rec.requirement_header_id;
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
421 fnd_message.set_token('HEADER_ID', to_char(l_header_rec.requirement_header_id), FALSE);
422
423 FND_MSG_PUB.ADD;
424 RAISE EXCP_USER_DEFINED;
425 WHEN OTHERS THEN
426 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
427 fnd_message.set_token('ERR_FIELD', 'l_header_rec.requirement_header_id', FALSE);
428 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
429 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_HEADERS', FALSE);
430 FND_MSG_PUB.ADD;
431 RAISE EXCP_USER_DEFINED;
432 END;
433 END IF;
434 END IF;
435
436 l_rqmt_header_Rec.requirement_header_id := nvl(l_header_rec.requirement_header_id, FND_API.G_MISS_NUM); --l_requirement_header_id;
437 l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
438 l_rqmt_header_Rec.last_update_date := l_today;
439 l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
440 l_rqmt_header_Rec.ship_to_location_id := nvl(l_header_rec.ship_to_location_id, FND_API.G_MISS_NUM);
441 l_rqmt_header_Rec.timezone_id := nvl(l_timezone_id, FND_API.G_MISS_NUM);
442 l_rqmt_header_Rec.address_type := csp_parts_Requirement.G_ADDR_RESOURCE;
443 l_rqmt_header_Rec.task_id := nvl(l_header_rec.task_id, FND_API.G_MISS_NUM);
444 l_rqmt_header_Rec.task_assignment_id := nvl(l_header_rec.task_assignment_id, FND_API.G_MISS_NUM);
445 l_rqmt_header_Rec.shipping_method_code := nvl(l_header_rec.shipping_method_code, FND_API.G_MISS_CHAR);
446 l_rqmt_header_Rec.need_by_date := nvl(l_header_rec.need_by_date, FND_API.G_MISS_DATE);
447 l_rqmt_header_Rec.destination_organization_id := nvl(l_header_rec.dest_organization_id, FND_API.G_MISS_NUM);
448 l_rqmt_header_Rec.destination_subinventory := nvl(l_header_rec.dest_subinventory, FND_API.G_MISS_CHAR);
449 l_rqmt_header_Rec.parts_defined := nvl(l_parts_defined, FND_API.G_MISS_CHAR);
450 -- l_rqmt_header_Rec.open_requirement := 'Y';
451 l_rqmt_header_rec.order_type_id := nvl(l_header_rec.order_type_id, FND_API.G_MISS_NUM);
452 l_rqmt_header_rec.attribute_Category := nvl(l_header_rec.attribute_category, FND_API.G_MISS_CHAR);
453 l_rqmt_header_rec.attribute1 := nvl(l_header_rec.attribute1, FND_API.G_MISS_CHAR);
454 l_rqmt_header_rec.attribute2 := nvl(l_header_rec.attribute2, FND_API.G_MISS_CHAR);
455 l_rqmt_header_rec.attribute3 := nvl(l_header_rec.attribute3, FND_API.G_MISS_CHAR);
456 l_rqmt_header_rec.attribute4 := nvl(l_header_rec.attribute4, FND_API.G_MISS_CHAR);
457 l_rqmt_header_rec.attribute5 := nvl(l_header_rec.attribute5, FND_API.G_MISS_CHAR);
458 l_rqmt_header_rec.attribute6 := nvl(l_header_rec.attribute6, FND_API.G_MISS_CHAR);
459 l_rqmt_header_rec.attribute7 := nvl(l_header_rec.attribute7, FND_API.G_MISS_CHAR);
460 l_rqmt_header_rec.attribute8 := nvl(l_header_rec.attribute8, FND_API.G_MISS_CHAR);
461 l_rqmt_header_rec.attribute9 := nvl(l_header_rec.attribute9, FND_API.G_MISS_CHAR);
462 l_rqmt_header_rec.attribute10 := nvl(l_header_rec.attribute10, FND_API.G_MISS_CHAR);
463 l_rqmt_header_rec.attribute11 := nvl(l_header_rec.attribute11, FND_API.G_MISS_CHAR);
464 l_rqmt_header_rec.attribute12 := nvl(l_header_rec.attribute12, FND_API.G_MISS_CHAR);
465 l_rqmt_header_rec.attribute13 := nvl(l_header_rec.attribute13, FND_API.G_MISS_CHAR);
466 l_rqmt_header_rec.attribute14 := nvl(l_header_rec.attribute14, FND_API.G_MISS_CHAR);
467 l_rqmt_header_rec.attribute15 := nvl(l_header_rec.attribute15, FND_API.G_MISS_CHAR);
468
469
470 IF (l_header_Rec.task_assignment_id IS NULL) THEN
471 l_Rqmt_header_rec.resource_type := l_header_rec.resource_type;
472 l_Rqmt_header_rec.resource_id := l_header_Rec.resource_id;
473 END IF;
474
475 -- SETTING UP THE PARTS REQUIREMENT LINE RECORD
476 FOR I IN 1..l_line_tbl.COUNT LOOP
477 l_line_rec := l_line_tbl(I);
478
479 IF (l_header_rec.operation = G_OPR_CREATE) THEN
480 l_rqmt_line_Rec.created_by := nvl(l_user_id, -1);
481 l_rqmt_line_Rec.creation_date := l_today;
482 ELSIF (l_header_rec.operation IN (G_OPR_UPDATE, G_OPR_DELETE)) THEN
483 IF nvl(l_line_rec.requirement_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
484
485 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
486 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requirement_line_id', FALSE);
487 FND_MSG_PUB.ADD;
488 RAISE EXCP_USER_DEFINED;
489 ELSE
490 BEGIN
491 select requirement_line_id
492 into l_check_existence
493 from csp_requirement_lines
494 where requirement_line_id = l_line_rec.requirement_line_id;
495 EXCEPTION
496 WHEN NO_DATA_FOUND THEN
497 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_LINE');
498 fnd_message.set_token ('LINE_ID', to_char(l_line_rec.requirement_line_id), FALSE);
499
500 FND_MSG_PUB.ADD;
501 RAISE EXCP_USER_DEFINED;
502 WHEN OTHERS THEN
503 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
504 fnd_message.set_token('ERR_FIELD', 'l_line_rec.requirement_line_id', FALSE);
505 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
506 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_LINES', FALSE);
507 FND_MSG_PUB.ADD;
508 RAISE EXCP_USER_DEFINED;
509 END;
510 END IF;
511 END IF;
512
513 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
514 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
515 l_rqmt_line_rec.last_update_date := l_today;
516 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
517 --l_rqmt_line_rec.requirement_header_id := nvl(l_header_rec.requirement_header_id, FND_API.G_MISS_NUM);
518
519 l_rqmt_line_rec.inventory_item_id := l_line_rec.inventory_item_id;
520 l_rqmt_line_rec.uom_code := l_line_rec.unit_of_measure;
521 l_rqmt_line_rec.required_quantity := l_line_rec.quantity;
522 l_rqmt_line_rec.ship_complete_flag := l_line_rec.ship_complete;
523 l_rqmt_line_rec.likelihood := l_line_rec.likelihood;
524 l_rqmt_line_rec.revision := l_line_rec.revision;
525 /*l_rqmt_line_rec.source_organization_id := l_line_rec.source_organization_id;
526
527 l_rqmt_line_rec.source_subinventory := l_line_rec.source_subinventory;
528 l_rqmt_line_rec.ordered_quantity := l_line_Rec.ordered_quantity;
529
530 l_rqmt_line_rec.reservation_id := l_line_rec.reservation_id;
531 l_rqmt_line_rec.order_by_date := l_line_rec.order_by_date;*/
532
533 l_rqmt_line_rec.attribute_Category := nvl(l_line_rec.attribute_category, FND_API.G_MISS_CHAR);
534 l_rqmt_line_rec.attribute1 := nvl(l_line_rec.attribute1, FND_API.G_MISS_CHAR);
535 l_rqmt_line_rec.attribute2 := nvl(l_line_rec.attribute2, FND_API.G_MISS_CHAR);
536 l_rqmt_line_rec.attribute3 := nvl(l_line_rec.attribute3, FND_API.G_MISS_CHAR);
537 l_rqmt_line_rec.attribute4 := nvl(l_line_rec.attribute4, FND_API.G_MISS_CHAR);
538 l_rqmt_line_rec.attribute5 := nvl(l_line_rec.attribute5, FND_API.G_MISS_CHAR);
539 l_rqmt_line_rec.attribute6 := nvl(l_line_rec.attribute6, FND_API.G_MISS_CHAR);
540 l_rqmt_line_rec.attribute7 := nvl(l_line_rec.attribute7, FND_API.G_MISS_CHAR);
541 l_rqmt_line_rec.attribute8 := nvl(l_line_rec.attribute8, FND_API.G_MISS_CHAR);
542 l_rqmt_line_rec.attribute9 := nvl(l_line_rec.attribute9, FND_API.G_MISS_CHAR);
543 l_rqmt_line_rec.attribute10 := nvl(l_line_rec.attribute10, FND_API.G_MISS_CHAR);
544 l_rqmt_line_rec.attribute11 := nvl(l_line_rec.attribute11, FND_API.G_MISS_CHAR);
545 l_rqmt_line_rec.attribute12 := nvl(l_line_rec.attribute12, FND_API.G_MISS_CHAR);
546 l_rqmt_line_rec.attribute13 := nvl(l_line_rec.attribute13, FND_API.G_MISS_CHAR);
547 l_rqmt_line_rec.attribute14 := nvl(l_line_rec.attribute14, FND_API.G_MISS_CHAR);
548 l_rqmt_line_rec.attribute15 := nvl(l_line_rec.attribute15, FND_API.G_MISS_CHAR);
549
550 l_rqmt_line_rec.order_line_id := l_line_Rec.order_line_id;
551 px_line_table(I).order_line_id := l_line_Rec.order_line_id;
552 l_rqmt_line_Tbl(I) := l_rqmt_line_rec;
553
554 END LOOP;
555
556 IF ( l_header_rec.operation = 'CREATE') THEN
557
558 -- check to see if requirements exist for a given task
559 IF (l_header_rec.task_id IS NOT NULL AND
560 l_header_rec.requirement_header_id IS NULL) THEN
561 BEGIN
562 SELECT requirement_header_id
563 INTO l_requirement_header_id
564 FROM csp_requirement_headers
565 WHERE task_id = l_header_rec.task_id;
566 EXCEPTION
567 WHEN NO_DATA_FOUND THEN
568 -- call private api for inserting into csp_Requirement_headers
569 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
570 P_Api_Version_Number => l_api_Version_number,
571 P_Init_Msg_List => p_init_msg_list,
572 P_Commit => FND_API.G_FALSE,
573 p_validation_level => null,
574 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
575 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
576 X_Return_Status => l_Return_status,
577 X_Msg_Count => l_msg_count,
578 X_Msg_Data => l_msg_data
579 );
580 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
581 RAISE FND_API.G_EXC_ERROR;
582 ELSE
583 IF l_order_msg IS NOT null THEN
584 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_CREATED');
585 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
586 x_msg_data := FND_MESSAGE.GET;
587 END IF;
588 END IF;
589 WHEN OTHERS THEN
590 NULL;
591 END;
592 ELSIF (l_header_rec.requirement_header_id IS NOT NULL) THEN
593 BEGIN
594 SELECT requirement_header_id
595 INTO l_requirement_header_id
596 FROM csp_requirement_headers
597 WHERE requirement_header_id = l_header_rec.requirement_header_id;
598 EXCEPTION
599 WHEN NO_DATA_FOUND THEN
600 -- call private api for inserting into csp_Requirement_headers
601 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
602 P_Api_Version_Number => l_api_Version_number,
603 P_Init_Msg_List => p_init_msg_list,
604 P_Commit => FND_API.G_FALSE,
605 p_validation_level => null,
606 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
607 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
608 X_Return_Status => l_Return_status,
609 X_Msg_Count => l_msg_count,
610 X_Msg_Data => l_msg_data
611 );
612 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
613 RAISE FND_API.G_EXC_ERROR;
614 ELSE
615 IF l_order_msg IS NOT null THEN
616 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_CREATED');
617 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
618 x_msg_data := FND_MESSAGE.GET;
619 END IF;
620 END IF;
621 END;
622 ELSE
623 -- call private api for inserting into csp_Requirement_headers
624 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
625 P_Api_Version_Number => l_api_Version_number,
626 P_Init_Msg_List => p_init_msg_list,
627 P_Commit => FND_API.G_FALSE,
628 p_validation_level => null,
629 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
630 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
631 X_Return_Status => l_Return_status,
632 X_Msg_Count => l_msg_count,
633 X_Msg_Data => l_msg_data
634 );
635 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
636 RAISE FND_API.G_EXC_ERROR;
637 ELSE
638 IF l_order_msg IS NOT null THEN
639 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_CREATED');
640 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
641 x_msg_data := FND_MESSAGE.GET;
642 END IF;
643 END IF;
644 END IF;
645
646 -- Update return header rec with requirement_header_id
647 px_header_rec.requirement_header_id := l_requirement_header_id;
648
649 -- Fill the requirement_header_id in the line record
650 FOR I in 1..l_rqmt_line_Tbl.COUNT LOOP
651 l_rqmt_line_Tbl(I).requirement_header_id := l_requirement_header_id;
652 END LOOP;
653 x_rqmt_line_tbl := l_rqmt_line_tbl;
654 -- call private api for inserting into csp_requirement_lines
655 CSP_Requirement_Lines_PVT.Create_requirement_lines(
656 P_Api_Version_Number => l_api_version_number,
657 P_Init_Msg_List => p_Init_Msg_List,
658 P_Commit => FND_API.G_FALSE,
659 p_validation_level => null,
660 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
661 x_Requirement_Line_tbl => x_rqmt_line_tbl,
662 X_Return_Status => l_return_status,
663 X_Msg_Count => l_msg_count,
664 X_Msg_Data => l_msg_data
665 );
666
667 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
668 RAISE FND_API.G_EXC_ERROR;
669 ELSE
670 IF x_msg_data IS null and l_order_msg is not null THEN
671 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_UPDATED');
672 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
673 x_msg_data := FND_MESSAGE.GET;
674 END IF;
675 END IF;
676 FOR I IN 1..x_rqmt_line_tbl.count LOOP
677 IF x_rqmt_line_tbl(I).order_line_id IS NOT NULL THEN
678 SELECT csp_req_line_Details_s1.nextval
679 INTO l_req_line_Dtl_id
680 FROM dual;
681
682 csp_req_line_Details_pkg.Insert_Row(
683 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
684 p_REQUIREMENT_LINE_ID => x_rqmt_line_tbl(I).requirement_line_id,
685 p_CREATED_BY => nvl(l_user_id, 1),
686 p_CREATION_DATE => sysdate,
687 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
688 p_LAST_UPDATE_DATE => sysdate,
689 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
690 p_SOURCE_TYPE => 'IO',
691 p_SOURCE_ID => x_rqmt_line_tbl(I).order_line_id);
692 END IF;
693 END LOOP;
694
695 ELSIF (l_header_rec.operation = 'UPDATE') THEN
696 -- call private api for updating requirement headers
697 CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
698 P_Api_Version_Number => l_api_Version_number,
699 P_Init_Msg_List => p_init_msg_list,
700 P_Commit => FND_API.G_FALSE,
701 p_validation_level => null,
702 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
703 X_Return_Status => l_Return_status,
704 X_Msg_Count => l_msg_count,
705 X_Msg_Data => l_msg_data
706 );
707
708 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
709 RAISE FND_API.G_EXC_ERROR;
710 END IF;
711
712 -- call private api for updating requirement lines
713 --FOR I in 1..l_rqmt_line_tbl.COUNT LOOP
714 CSP_Requirement_Lines_PVT.Update_requirement_lines(
715 P_Api_Version_Number => l_api_version_number,
716 P_Init_Msg_List => p_Init_Msg_List,
717 P_Commit => FND_API.G_FALSE,
718 p_validation_level => null,
719 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
720 X_Return_Status => l_return_status,
721 X_Msg_Count => l_msg_count,
722 X_Msg_Data => l_msg_data
723 );
724
725 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728
729 --END LOOP;
730 If p_commit = FND_API.G_TRUE then
731 commit work;
732 end if;
733 END IF;
734 IF x_msg_data is not null or l_order_msg is not null THEN
735 x_msg_data := x_msg_data || ' ' || l_order_msg;
736 END IF;
737 EXCEPTION
738 WHEN FND_API.G_EXC_ERROR THEN
739 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
740 P_API_NAME => L_API_NAME
741 ,P_PKG_NAME => G_PKG_NAME
742 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
743 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
744 ,X_MSG_COUNT => X_MSG_COUNT
745 ,X_MSG_DATA => X_MSG_DATA
746 ,X_RETURN_STATUS => X_RETURN_STATUS);
747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
748 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
749 P_API_NAME => L_API_NAME
750 ,P_PKG_NAME => G_PKG_NAME
751 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
752 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
753 ,X_MSG_COUNT => X_MSG_COUNT
754 ,X_MSG_DATA => X_MSG_DATA
755 ,X_RETURN_STATUS => X_RETURN_STATUS);
756 WHEN OTHERS THEN
757 Rollback to process_requirement_pub;
758 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
759 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
760 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
761 FND_MSG_PUB.ADD;
762 fnd_msg_pub.count_and_get
763 ( p_count => x_msg_count
764 , p_data => x_msg_data);
765 x_return_status := FND_API.G_RET_STS_ERROR;
766
767 END;
768
769
770
771 PROCEDURE csptrreq_fm_order(
772 p_api_version IN NUMBER
773 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
774 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
775 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
776 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
777 ,x_return_status OUT NOCOPY VARCHAR2
778 ,x_msg_count OUT NOCOPY NUMBER
779 ,x_msg_data OUT NOCOPY VARCHAR2
780 ) IS
781 l_api_version_number CONSTANT NUMBER := 1.0;
782 l_api_name CONSTANT VARCHAR2(30) := 'csptrreq_fm_order';
783 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
784 l_msg_count NUMBER;
785 l_msg_data VARCHAR2(4000);
786 l_commit VARCHAR2(1) := FND_API.G_FALSE;
787 l_user_id NUMBER;
788 l_login_id NUMBER;
789 l_today DATE;
790 EXCP_USER_DEFINED EXCEPTION;
791 l_check_existence NUMBER;
792
793 l_requirement_header_id NUMBER ;
794 l_requirement_line_id NUMBER;
795
796 l_header_rec Header_Rec_Type;
797 l_line_rec Line_rec_type;
798 l_line_tbl Line_Tbl_type;
799 l_old_line_tbl Line_Tbl_type;
800 l_po_line_tbl Line_Tbl_type;
801 l_po_line_Rec Line_Rec_Type;
802
803 J NUMBER;
804 K NUMBER;
805 l_qty_to_reserve NUMBER := 0;
806 l_resource_att NUMBER := 0;
807 l_resource_onhand NUMBER := 0;
808 l_resource_org_id NUMBER;
809 l_resource_subinv VARCHAR2(30);
810 l_reservation_rec CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
811 l_local_reservation_id NUMBER;
812
813 l_rqmt_line_Rec csp_requirement_lines_pvt.requirement_line_rec_type;
814 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
815
816 CURSOR rs_info_cur(p_rqmt_header_id NUMBER) IS
817 SELECT cla.organization_id,
818 cla.subinventory_code
819 FROM csp_requirement_headers crh,
820 jtf_task_assignments jta,
821 csp_INV_LOC_ASSIGNMENTS cla
822 WHERE cla.default_code = 'IN'
823 AND cla.resource_type = decode(crh.task_assignment_id,null,crh.resource_type,jta.resource_type_code)
824 AND cla.resource_id = decode(crh.task_assignment_id,null,crh.resource_id,jta.resource_id)
825 and jta.task_assignment_id(+) = crh.task_assignment_id
826 AND crh.requirement_header_id = p_rqmt_header_id;
827
828 BEGIN
829
830 SAVEPOINT csptrreq_fm_order_PUB;
831
832 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
833 -- initialize message list
834 FND_MSG_PUB.initialize;
835 END IF;
836
837 -- Standard call to check for call compatibility.
838 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
839 p_api_version,
840 l_api_name,
841 G_PKG_NAME)
842 THEN
843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
844 END IF;
845
846 -- initialize return status
847 x_return_status := FND_API.G_RET_STS_SUCCESS;
848
849 l_header_Rec := px_header_rec;
850 l_old_line_tbl := px_line_table;
851
852 -- user and login information
853 SELECT Sysdate INTO l_today FROM dual;
854 l_user_id := fnd_global.user_id;
855 l_login_id := fnd_global.login_id;
856
857 -- create reservations and create line table which qualify for creating order
858
859 OPEN rs_info_cur(l_header_rec.requirement_header_id);
860 FETCH rs_info_cur INTO l_resource_org_id, l_resource_subinv;
861 CLOSE rs_info_cur;
862
863 J := 1;
864 K := 1;
865 FOR I IN 1..l_old_line_Tbl.COUNT LOOP
866 l_line_rec := l_old_line_Tbl(I);
867
868 l_qty_to_Reserve := l_line_Rec.quantity - l_line_rec.ordered_quantity;
869
870 IF (l_resource_org_id IS NOT NULL AND l_resource_subinv IS NOT NULL) THEN
871 csp_sch_int_pvt.check_local_inventory(p_org_id => l_resource_org_id,
872 p_subinv_code => l_resource_subinv,
873 p_item_id => l_line_rec.inventory_item_id,
874 p_revision => l_line_Rec.revision,
875 x_att => l_resource_att,
876 x_onhand => l_resource_onhand,
877 x_return_status => l_return_status,
878 x_msg_count => l_msg_count,
879 x_msg_Data => l_msg_data
880 );
881 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
882 RAISE FND_API.G_EXC_ERROR;
883 END If;
884 END IF;
885
886 IF (l_qty_to_Reserve < l_resource_att and l_qty_to_reserve > 0) THEN
887 -- create reservations on qty_to_Reserve in engineers subinv
888 l_reservation_rec.need_by_date := sysdate; --l_header_rec.need_by_date;
889 l_reservation_rec.organization_id := l_resource_org_id;
890 l_reservation_rec.item_id := l_line_Rec.inventory_item_id;
891 l_Reservation_rec.revision := l_line_rec.revision;
892 l_reservation_rec.item_uom_code := l_line_rec.unit_of_measure;
893 l_reservation_rec.quantity_needed := l_qty_to_reserve;
894 l_reservation_rec.sub_inventory_code := l_resource_subinv;
895
896 l_local_reservation_id := csp_sch_int_pvt.create_reservation(
897 p_reservation_parts => l_reservation_rec,
898 x_return_status => l_return_status,
899 --x_msg_count => l_msg_Count,
900 x_msg_data => l_msg_data
901
902 );
903 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
904 RAISE FND_API.G_EXC_ERROR;
905 END If;
906
907 update csp_Requirement_lines
908 set local_Reservation_id = l_local_Reservation_id
909 where requirement_line_id = l_line_Rec.requirement_line_id;
910
911 END IF;
912
913 IF (l_line_rec.sourced_from = 'INVENTORY' AND (l_line_rec.ordered_quantity > 0)) THEN
914 l_line_tbl(J) := l_line_rec;
915 J := J + 1;
916 ELSIF (l_line_rec.sourced_from = 'VENDOR' AND (l_line_rec.ordered_quantity > 0)) THEN
917 l_po_line_tbl(K) := l_line_Rec;
918 K := K+1;
919 END IF;
920
921 END LOOP;
922
923 -- call csp_process_order API only if atleast one line qualifies for order to be created
924
925 IF (l_line_tbl.count > 0) THEN
926 csp_parts_order.process_order(
927 p_api_version => l_api_version_number
928 ,p_Init_Msg_List => p_init_msg_list
929 ,p_commit => p_commit
930 ,px_header_rec => l_header_rec
931 ,px_line_table => l_line_tbl
932 ,x_return_status => l_return_status
933 ,x_msg_count => l_msg_count
934 ,x_msg_data => l_msg_data
935 );
936
937 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
938 RAISE FND_API.G_EXC_ERROR;
939 ELSE
940 -- update csp_requirement_line table with order information
941 FOR I IN 1..l_line_tbl.COUNT LOOP
942 l_line_rec := l_line_tbl(I);
943
944 IF nvl(l_line_rec.requirement_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
945
946 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
947 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requirement_line_id', FALSE);
948
949 FND_MSG_PUB.ADD;
950 RAISE EXCP_USER_DEFINED;
951 ELSE
952 BEGIN
953 select requirement_line_id
954 into l_check_existence
955 from csp_requirement_lines
956 where requirement_line_id = l_line_rec.requirement_line_id;
957 EXCEPTION
958 WHEN NO_DATA_FOUND THEN
959 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_LINE');
960 fnd_message.set_token ('LINE_ID', to_char(l_line_rec.requirement_line_id), FALSE);
961 FND_MSG_PUB.ADD;
962 RAISE EXCP_USER_DEFINED;
963 WHEN OTHERS THEN
964 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
965 fnd_message.set_token('ERR_FIELD', 'l_line_rec.requirement_line_id', FALSE);
966 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
967 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_LINES', FALSE);
968 FND_MSG_PUB.ADD;
969 RAISE EXCP_USER_DEFINED;
970 END;
971 END IF;
972
973 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
974 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
975 l_rqmt_line_rec.last_update_date := l_today;
976 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
977 l_rqmt_line_rec.ordered_quantity := l_line_Rec.ordered_quantity;
978 l_rqmt_line_rec.order_line_id := l_line_Rec.order_line_id;
979 l_rqmt_line_rec.sourced_from := 'INVENTORY';
980 --l_rqmt_line_rec.reservation_id := l_line_rec.reservation_id;
981 --l_rqmt_line_rec.order_by_date := l_line_rec.order_by_date;
982
983 l_rqmt_line_Tbl(I) := l_rqmt_line_rec;
984
985 END LOOP;
986 END IF;
987 END IF;
988
989 -- create purchase requisitions if po_line_tbl has atleast one record
990 IF (l_po_line_tbl.count > 0) THEN
991 csp_parts_order.process_purchase_req(
992 p_api_version => l_api_version_number
993 ,p_init_msg_list => p_init_msg_list
994 ,p_commit => p_commit
995 ,px_header_rec => l_header_Rec
996 ,px_line_Table => l_po_line_tbl
997 ,x_return_status => l_return_status
998 ,x_msg_count => l_msg_count
999 ,x_msg_data => l_msg_data
1000 );
1001
1002 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1003 RAISE FND_API.G_EXC_ERROR;
1004 ELSE
1005 -- now update csp_requirement_line table with purchase req information
1006 J := l_line_tbl.count + 1;
1007 FOR I IN 1..l_po_line_tbl.COUNT LOOP
1008 l_line_rec := l_po_line_tbl(I);
1009
1010 IF nvl(l_line_rec.requirement_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1011
1012 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1013 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requirement_line_id', FALSE);
1014
1015 FND_MSG_PUB.ADD;
1016 RAISE EXCP_USER_DEFINED;
1017 ELSE
1018 BEGIN
1019 select requirement_line_id
1020 into l_check_existence
1021 from csp_requirement_lines
1022 where requirement_line_id = l_line_rec.requirement_line_id;
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN
1025 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_LINE');
1026 fnd_message.set_token ('LINE_ID', to_char(l_line_rec.requirement_line_id), FALSE);
1027 FND_MSG_PUB.ADD;
1028 RAISE EXCP_USER_DEFINED;
1029 WHEN OTHERS THEN
1030 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1031 fnd_message.set_token('ERR_FIELD', 'l_line_rec.requirement_line_id', FALSE);
1032 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
1033 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_LINES', FALSE);
1034 FND_MSG_PUB.ADD;
1035 RAISE EXCP_USER_DEFINED;
1036 END;
1037 END IF;
1038
1039 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
1040 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
1041 l_rqmt_line_rec.last_update_date := l_today;
1042 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
1043 l_rqmt_line_rec.ordered_quantity := l_line_Rec.ordered_quantity;
1044 l_rqmt_line_rec.order_line_id := l_line_Rec.requisition_line_id;
1045 l_rqmt_line_Rec.sourced_from := 'VENDOR';
1046 --l_rqmt_line_rec.reservation_id := l_line_rec.reservation_id;
1047 --l_rqmt_line_rec.order_by_date := l_line_rec.order_by_date;
1048
1049 l_rqmt_line_Tbl(J) := l_rqmt_line_rec;
1050
1051 END LOOP;
1052 END IF;
1053 END IF;
1054
1055 IF (l_rqmt_line_Tbl.count > 0) THEN
1056
1057 CSP_Requirement_Lines_PVT.Update_requirement_lines(
1058 P_Api_Version_Number => l_api_version_number,
1059 P_Init_Msg_List => p_Init_Msg_List,
1060 P_Commit => p_commit,
1061 p_validation_level => null,
1062 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
1063 X_Return_Status => l_return_status,
1064 X_Msg_Count => l_msg_count,
1065 X_Msg_Data => l_msg_data
1066 );
1067
1068 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1069 RAISE FND_API.G_EXC_ERROR;
1070 END IF;
1071
1072 -- assign output variables
1073 px_header_rec := l_header_rec;
1074 px_line_table := l_line_tbl;
1075
1076 END IF;
1077
1078 EXCEPTION
1079 WHEN FND_API.G_EXC_ERROR THEN
1080 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1081 P_API_NAME => L_API_NAME
1082 ,P_PKG_NAME => G_PKG_NAME
1083 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1084 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1085 ,X_MSG_COUNT => X_MSG_COUNT
1086 ,X_MSG_DATA => X_MSG_DATA
1087 ,X_RETURN_STATUS => X_RETURN_STATUS);
1088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1090 P_API_NAME => L_API_NAME
1091 ,P_PKG_NAME => G_PKG_NAME
1092 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1093 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1094 ,X_MSG_COUNT => X_MSG_COUNT
1095 ,X_MSG_DATA => X_MSG_DATA
1096 ,X_RETURN_STATUS => X_RETURN_STATUS);
1097 WHEN OTHERS THEN
1098 Rollback to csptrreq_fm_order_PUB;
1099 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1100 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
1101 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
1102 FND_MSG_PUB.ADD;
1103 fnd_msg_pub.count_and_get
1104 ( p_count => x_msg_count
1105 , p_data => x_msg_data);
1106 x_return_status := FND_API.G_RET_STS_ERROR;
1107
1108 END csptrreq_fm_order;
1109
1110 PROCEDURE csptrreq_order_res(
1111 p_api_version IN NUMBER
1112 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1113 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1114 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
1115 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
1116 ,x_return_status OUT NOCOPY VARCHAR2
1117 ,x_msg_count OUT NOCOPY NUMBER
1118 ,x_msg_data OUT NOCOPY VARCHAR2
1119 ) IS
1120 l_api_version_number CONSTANT NUMBER := 1.0;
1121 l_api_name CONSTANT VARCHAR2(30) := 'csptrreq_fm_order';
1122 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1123 l_msg_count NUMBER;
1124 l_msg_data VARCHAR2(4000);
1125 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1126 l_user_id NUMBER;
1127 l_login_id NUMBER;
1128 l_today DATE;
1129 EXCP_USER_DEFINED EXCEPTION;
1130 l_check_existence NUMBER;
1131 J NUMBER;
1132 K NUMBER;
1133
1134 l_requirement_header_id NUMBER ;
1135 l_requirement_line_id NUMBER;
1136
1137 l_header_rec Header_Rec_Type;
1138 l_line_rec Line_rec_type;
1139 l_line_tbl Line_Tbl_type;
1140 l_oe_line_tbl Line_Tbl_type;
1141 l_oe_line_rec Line_rec_type;
1142 l_po_line_tbl Line_Tbl_type;
1143 l_po_line_Rec Line_Rec_Type;
1144
1145 l_reservation_rec CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1146 l_reservation_id NUMBER;
1147
1148 l_req_line_dtl_id NUMBER;
1149 BEGIN
1150
1151 SAVEPOINT csptrreq_fm_order_PUB;
1152
1153 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1154 -- initialize message list
1155 FND_MSG_PUB.initialize;
1156 END IF;
1157
1158 -- Standard call to check for call compatibility.
1159 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1160 p_api_version,
1161 l_api_name,
1162 G_PKG_NAME)
1163 THEN
1164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1165 END IF;
1166
1167 -- initialize return status
1168 x_return_status := FND_API.G_RET_STS_SUCCESS;
1169
1170 l_header_Rec := px_header_rec;
1171 l_line_tbl := px_line_table;
1172
1173 -- user and login information
1174 SELECT Sysdate INTO l_today FROM dual;
1175 l_user_id := fnd_global.user_id;
1176 l_login_id := fnd_global.login_id;
1177
1178 -- create reservations and create line table which qualify for creating order
1179 K := 1;
1180 J := 1;
1181 FOR I IN 1..l_line_Tbl.COUNT LOOP
1182 l_line_rec := l_line_tbl(I);
1183 IF (l_line_rec.sourced_From = 'RES') THEN
1184 l_reservation_rec.need_by_date := sysdate; --l_header_rec.need_by_date;
1185 l_reservation_rec.organization_id := l_line_rec.source_organization_id;
1186 l_reservation_rec.item_id := l_line_Rec.inventory_item_id;
1187 l_Reservation_rec.revision := l_line_rec.revision;
1188 l_reservation_rec.item_uom_code := l_line_rec.unit_of_measure;
1189 l_reservation_rec.quantity_needed := l_line_rec.ordered_quantity;
1190 l_reservation_rec.sub_inventory_code := l_line_rec.source_subinventory;
1191
1192 l_reservation_id := csp_sch_int_pvt.create_reservation(
1193 p_reservation_parts => l_reservation_rec,
1194 x_return_status => l_return_status,
1195 x_msg_data => l_msg_data
1196 );
1197 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1198 RAISE FND_API.G_EXC_ERROR;
1199 ELSE
1200 -- code for inserting into req line dtls
1201 SELECT csp_req_line_Details_s1.nextval
1202 INTO l_req_line_Dtl_id
1203 FROM dual;
1204
1205 csp_req_line_Details_pkg.Insert_Row(
1206 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
1207 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
1208 p_CREATED_BY => nvl(l_user_id, 1),
1209 p_CREATION_DATE => sysdate,
1210 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
1211 p_LAST_UPDATE_DATE => sysdate,
1212 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
1213 p_SOURCE_TYPE => 'RES',
1214 p_SOURCE_ID => l_Reservation_id
1215 );
1216 END If;
1217 ELSIF (l_line_rec.sourced_from = 'IO') THEN
1218 l_oe_line_tbl(J) := l_line_rec;
1219 J := J + 1;
1220 ELSIF (l_line_rec.sourced_from = 'POREQ') THEN
1221 l_po_line_tbl(K) := l_line_Rec;
1222 K := K+1;
1223 END IF;
1224 END LOOP;
1225
1226 -- call csp_process_order API only if atleast one line qualifies for order to be created
1227
1228 IF (l_oe_line_tbl.count > 0) THEN
1229 csp_parts_order.process_order(
1230 p_api_version => l_api_version_number
1231 ,p_Init_Msg_List => p_init_msg_list
1232 ,p_commit => p_commit
1233 ,px_header_rec => l_header_rec
1234 ,px_line_table => l_oe_line_tbl
1235 ,x_return_status => l_return_status
1236 ,x_msg_count => l_msg_count
1237 ,x_msg_data => l_msg_data
1238 );
1239
1240 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1241 RAISE FND_API.G_EXC_ERROR;
1242 ELSE
1243 -- insert record in csp_req_line_details table with order information
1244 FOR I IN 1..l_oe_line_tbl.COUNT LOOP
1245 l_line_rec := l_oe_line_tbl(I);
1246
1247 SELECT csp_req_line_Details_s1.nextval
1248 INTO l_req_line_Dtl_id
1249 FROM dual;
1250
1251 csp_req_line_Details_pkg.Insert_Row(
1252 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
1253 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
1254 p_CREATED_BY => nvl(l_user_id, 1),
1255 p_CREATION_DATE => sysdate,
1256 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
1257 p_LAST_UPDATE_DATE => sysdate,
1258 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
1259 p_SOURCE_TYPE => 'IO',
1260 p_SOURCE_ID => l_line_rec.order_line_id);
1261 END LOOP;
1262 END IF;
1263 END IF;
1264
1265 -- create purchase requisitions if po_line_tbl has atleast one record
1266 IF (l_po_line_tbl.count > 0) THEN
1267 csp_parts_order.process_purchase_req(
1268 p_api_version => l_api_version_number
1269 ,p_init_msg_list => p_init_msg_list
1270 ,p_commit => p_commit
1271 ,px_header_rec => l_header_Rec
1272 ,px_line_Table => l_po_line_tbl
1273 ,x_return_status => l_return_status
1274 ,x_msg_count => l_msg_count
1275 ,x_msg_data => l_msg_data
1276 );
1277
1278 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1279 RAISE FND_API.G_EXC_ERROR;
1280 ELSE
1281 -- insert into csp_req_line_Details table with purchase req information
1282 FOR I IN 1..l_po_line_tbl.COUNT LOOP
1283 l_line_rec := l_po_line_tbl(I);
1284
1285 SELECT csp_req_line_Details_s1.nextval
1286 INTO l_req_line_Dtl_id
1287 FROM dual;
1288
1289 csp_req_line_Details_pkg.Insert_Row(
1290 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
1291 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
1292 p_CREATED_BY => nvl(l_user_id, 1),
1293 p_CREATION_DATE => sysdate,
1294 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
1295 p_LAST_UPDATE_DATE => sysdate,
1296 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
1297 p_SOURCE_TYPE => 'POREQ',
1298 p_SOURCE_ID => l_line_rec.requisition_line_id);
1299 END LOOP;
1300 END IF;
1301 END IF;
1302 EXCEPTION
1303 WHEN FND_API.G_EXC_ERROR THEN
1304 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1305 P_API_NAME => L_API_NAME
1306 ,P_PKG_NAME => G_PKG_NAME
1307 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1308 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1309 ,X_MSG_COUNT => X_MSG_COUNT
1310 ,X_MSG_DATA => X_MSG_DATA
1311 ,X_RETURN_STATUS => X_RETURN_STATUS);
1312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1313 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1314 P_API_NAME => L_API_NAME
1315 ,P_PKG_NAME => G_PKG_NAME
1316 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1317 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1318 ,X_MSG_COUNT => X_MSG_COUNT
1319 ,X_MSG_DATA => X_MSG_DATA
1320 ,X_RETURN_STATUS => X_RETURN_STATUS);
1321 WHEN OTHERS THEN
1322 Rollback to csptrreq_fm_order_PUB;
1323 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1324 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
1325 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
1326 FND_MSG_PUB.ADD;
1327 fnd_msg_pub.count_and_get
1328 ( p_count => x_msg_count
1329 , p_data => x_msg_data);
1330 x_return_status := FND_API.G_RET_STS_ERROR;
1331 END;
1332
1333 PROCEDURE delete_rqmt_header(
1334 p_api_version IN NUMBER
1335 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1336 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1337 ,p_header_id IN NUMBER
1338 ,x_return_status OUT NOCOPY VARCHAR2
1339 ,x_msg_count OUT NOCOPY NUMBER
1340 ,x_msg_data OUT NOCOPY VARCHAR2
1341 )IS
1342 l_api_version_number CONSTANT NUMBER := 1.0;
1343 l_api_name CONSTANT VARCHAR2(30) := 'delete_rqmt_header';
1344 EXCP_USER_DEFINED EXCEPTION;
1345 l_count NUMBER;
1346 BEGIN
1347 SAVEPOINT delete_rqmt_header_PUB;
1348
1349 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1350 -- initialize message list
1351 FND_MSG_PUB.initialize;
1352 END IF;
1353
1354 -- Standard call to check for call compatibility.
1355 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1356 p_api_version,
1357 l_api_name,
1358 G_PKG_NAME)
1359 THEN
1360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1361 END IF;
1362
1363 -- initialize return status
1364 x_return_status := FND_API.G_RET_STS_SUCCESS;
1365
1366 IF (p_header_id IS NOT NULL) THEN
1367 SELECT count(*)
1368 INTO l_count
1369 FROM csp_requirement_lines crl, csp_req_line_Details crld
1370 WHERE crl.requirement_header_id = p_header_id
1371 AND crl.requirement_line_id = crld.requirement_line_id;
1372
1373 IF l_count > 0 THEN
1374 FND_MESSAGE.SET_NAME ('CSP', 'CSP_RQMT_LINE_DELETE_ERROR');
1375 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_header_id', FALSE);
1376 FND_MSG_PUB.ADD;
1377 RAISE EXCP_USER_DEFINED;
1378 ELSE
1379 DELETE FROM csp_requirement_lines
1380 WHERE requirement_header_id = p_header_id;
1381
1382 DELETE FROM csp_requirement_headers
1383 WHERE requirement_header_id = p_header_id;
1384 END IF;
1385
1386 IF fnd_api.to_boolean(p_commit) THEN
1387 commit work;
1388 END IF;
1389 x_return_status := FND_API.G_RET_STS_SUCCESS;
1390 END IF;
1391 EXCEPTION
1392 WHEN EXCP_USER_DEFINED THEN
1393 Rollback to delete_rqmt_header_PUB;
1394 x_return_status := FND_API.G_RET_STS_ERROR;
1395 fnd_msg_pub.count_and_get
1396 ( p_count => x_msg_count
1397 , p_data => x_msg_data);
1398
1399 WHEN FND_API.G_EXC_ERROR THEN
1400 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1401 P_API_NAME => L_API_NAME
1402 ,P_PKG_NAME => G_PKG_NAME
1403 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1404 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1405 ,X_MSG_COUNT => X_MSG_COUNT
1406 ,X_MSG_DATA => X_MSG_DATA
1407 ,X_RETURN_STATUS => X_RETURN_STATUS);
1408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1409 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1410 P_API_NAME => L_API_NAME
1411 ,P_PKG_NAME => G_PKG_NAME
1412 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1413 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1414 ,X_MSG_COUNT => X_MSG_COUNT
1415 ,X_MSG_DATA => X_MSG_DATA
1416 ,X_RETURN_STATUS => X_RETURN_STATUS);
1417 WHEN OTHERS THEN
1418 Rollback to delete_rqmt_header_PUB;
1419 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1420 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1421 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1422 FND_MSG_PUB.ADD;
1423 fnd_msg_pub.count_and_get
1424 ( p_count => x_msg_count
1425 , p_data => x_msg_data);
1426 x_return_status := FND_API.G_RET_STS_ERROR;
1427 END;
1428
1429 PROCEDURE save_rqmt_line(
1430 p_api_version IN NUMBER
1431 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1432 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1433 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
1434 ,px_line_tbl IN OUT NOCOPY csp_parts_requirement.Line_tbl_type
1435 ,x_return_status OUT NOCOPY VARCHAR2
1436 ,x_msg_count OUT NOCOPY NUMBER
1437 ,x_msg_data OUT NOCOPY VARCHAR2
1438 )IS
1439 l_api_version_number CONSTANT NUMBER := 1.0;
1440 l_api_name CONSTANT VARCHAR2(30) := 'save_rqmt_line';
1441 l_header_rec csp_parts_requirement.Header_rec_type;
1442 l_line_tbl csp_parts_requirement.Line_Tbl_type;
1443 l_line_rec Line_rec_type;
1444 l_user_id NUMBER;
1445 l_login_id NUMBER;
1446 l_today DATE;
1447 l_parts_defined VARCHAR2(30);
1448 l_rqmt_header_Rec csp_requirement_headers_pvt.requirement_header_rec_type;
1449 l_rqmt_line_Rec csp_requirement_lines_pvt.requirement_line_rec_type;
1450 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
1451 l_rqmt_line_Tbl_out csp_requirement_lines_pvt.requirement_line_tbl_type;
1452 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1453 l_msg_count NUMBER;
1454 l_msg_data VARCHAR2(2000);
1455 l_requirement_header_id NUMBER;
1456 l_resource_name VARCHAR2(240);
1457
1458 EXCP_USER_DEFINED EXCEPTION;
1459
1460 l_party_site_id NUMBER;
1461 l_customer_id NUMBER;
1462 l_cust_account_id NUMBER;
1463 I NUMBER;
1464 l_check_existence NUMBER;
1465 l_count NUMBER;
1466
1467 cursor get_party_details( c_incident_id number,c_location_id number) is
1468 select jpl.party_site_id, cia.customer_id, cia.account_id
1469 from jtf_party_locations_v jpl, cs_incidents_all_b cia
1470 where jpl.party_id = cia.customer_id
1471 and cia.incident_id = c_incident_id
1472 and jpl.location_id = c_location_id;
1473
1474 cursor rqmt_lines_cur(p_rqmt_header_id NUMBER) IS
1475 select crl.requirement_line_id,
1476 crl.inventory_item_id ,
1477 crl.revision,
1478 crl.required_quantity,
1479 crl.uom_code
1480 from csp_requirement_lines crl,
1481 csp_req_line_details crld
1482 where crld.requirement_line_id(+) = crl.requirement_line_id
1483 and crl.requirement_header_id = p_rqmt_header_id
1484 and crld.source_id is null;
1485
1486 rqmt_line rqmt_lines_cur%ROWTYPE;
1487
1488 -- File Handling
1489 /* debug_handler utl_file.file_type;
1490 debug_handler1 utl_file.file_type;
1491 l_utl_dir varchar2(1000) := '/appslog/srv_top/utl/srvmntr8/out';
1492 */
1493 BEGIN
1494 SAVEPOINT save_rqmt_line_PUB;
1495
1496 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1497 -- initialize message list
1498 FND_MSG_PUB.initialize;
1499 END IF;
1500
1501 -- Standard call to check for call compatibility.
1502 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1503 p_api_version,
1504 l_api_name,
1505 G_PKG_NAME)
1506 THEN
1507
1508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1509 END IF;
1510
1511 -- initialize return status
1512 x_return_status := FND_API.G_RET_STS_SUCCESS;
1513 l_header_Rec := px_header_rec;
1514 l_line_tbl := px_line_tbl;
1515
1516 -- user and login information
1517 SELECT Sysdate INTO l_today FROM dual;
1518 l_user_id := fnd_global.user_id;
1519
1520 l_login_id := fnd_global.login_id;
1521
1522 IF (l_header_rec.requirement_header_id IS NULL) THEN
1523 -- create header
1524 --get resource information
1525 BEGIN
1526 SELECT resource_id
1527 INTO l_header_rec.resource_id
1528 FROM jtf_rs_resource_extns
1529 WHERE user_id = l_user_id;
1530 EXCEPTION
1531 WHEN no_Data_found THEN
1532 FND_MESSAGE.SET_NAME ('CSP', 'CSP_RS_USER_NOT_DEFINED');
1533 FND_MSG_PUB.ADD;
1534 RAISE EXCP_USER_DEFINED;
1535 END;
1536
1537 /* BEGIN
1538 SELECT resource_type, resource_name
1539 INTO l_header_Rec.resource_type, l_resource_name
1540 FROM jtf_rs_all_resources_vl
1541 WHERE resource_id = l_header_rec.resource_id;
1542 EXCEPTION
1543 WHEN no_Data_found THEN
1544 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_RES_ID_TYPE');
1545 FND_MSG_PUB.ADD;
1546 RAISE EXCP_USER_DEFINED;
1547 END;
1548 */
1549 -- Since wireless deals with employee type resources only, hardcode resource type
1550 l_header_rec.resource_type := 'RS_EMPLOYEE';
1551
1552 BEGIN
1553 SELECT organization_id, subinventory_code
1554 INTO l_header_rec.dest_organization_id, l_header_rec.dest_subinventory
1555 FROM CSP_INV_LOC_ASSIGNMENTS
1556 WHERE resource_id = l_header_rec.resource_id
1557 AND resource_type = l_header_rec.resource_type
1558 AND default_code = 'IN';
1559 EXCEPTION
1560 WHEN no_Data_found THEN
1561
1562 FND_MESSAGE.SET_NAME ('CSP', 'CSP_SCH_DEFAULT_SUBINV');
1563 FND_MESSAGE.SET_TOKEN('RESOURCE_TYPE', l_header_rec.resource_type);
1564 FND_MESSAGE.SET_TOKEN('RESOURCE_NAME', l_resource_name);
1565 FND_MSG_PUB.ADD;
1566 RAISE EXCP_USER_DEFINED;
1567 WHEN TOO_MANY_ROWS THEN
1568
1569 FND_MESSAGE.SET_NAME ('CSP', 'CSP_ONE_DEFAULT_IN_ALLOWED');
1570 FND_MSG_PUB.ADD;
1571 RAISE EXCP_USER_DEFINED;
1572 END;
1573
1574 IF (l_header_rec.task_id IS NOT NULL) THEN
1575 BEGIN
1576 SELECT source_object_id
1577 INTO l_header_rec.incident_id
1578 FROM jtf_tasks_b
1579 WHERE task_id = l_header_rec.task_id;
1580 EXCEPTION
1581 WHEN NO_DATA_FOUND THEN
1582
1583 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETER');
1584 FND_MESSAGE.SET_TOKEN('PARAMETER', 'Service Request');
1585 FND_MSG_PUB.ADD;
1586 RAISE EXCP_USER_DEFINED;
1587 END;
1588 END IF;
1589
1590 IF (l_header_rec.address_type = 'C') THEN
1591 open get_party_details(l_header_Rec.incident_id,l_header_Rec.ship_to_location_id) ;
1592 FETCH get_party_details INTO l_party_site_id,l_customer_id,l_cust_account_id;
1593 CLOSE get_party_details;
1594
1595 csp_ship_to_address_pvt.cust_inv_loc_link
1596 ( p_api_version => 1.0
1597 ,p_Init_Msg_List => FND_API.G_FALSE
1598 ,p_commit => FND_API.G_FALSE
1599 ,px_location_id => l_header_Rec.ship_to_location_id
1600 ,p_party_site_id => l_party_site_id
1601 ,p_cust_account_id => l_cust_account_id
1602 ,p_customer_id => l_customer_id
1603 ,p_attribute_category => null
1604 ,p_attribute1 => null
1605 ,p_attribute2 => null
1606 ,p_attribute3 => null
1607 ,p_attribute4 => null
1608 ,p_attribute5 => null
1609 ,p_attribute6 => null
1610 ,p_attribute7 => null
1611 ,p_attribute8 => null
1612 ,p_attribute9 => null
1613 ,p_attribute10 => null
1614 ,p_attribute11 => null
1615 ,p_attribute12 => null
1616 ,p_attribute13 => null
1617 ,p_attribute14 => null
1618 ,p_attribute15 => null
1619 ,p_attribute16 => null
1620 ,p_attribute17 => null
1621 ,p_attribute18 => null
1622 ,p_attribute19 => null
1623 ,p_attribute20 => null
1624 ,x_return_status => l_return_status
1625 ,x_msg_count => l_msg_count
1626 ,x_msg_data => l_msg_data);
1627 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1628 RAISE FND_API.G_EXC_ERROR;
1629 END IF;
1630 END IF;
1631
1632 l_rqmt_header_Rec.requirement_header_id := nvl(l_header_rec.requirement_header_id, FND_API.G_MISS_NUM);
1633 l_rqmt_header_Rec.created_by := nvl(l_user_id, -1);
1634 l_rqmt_header_Rec.creation_date := l_today;
1635 l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
1636 l_rqmt_header_Rec.last_update_date := l_today;
1637 l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
1638 l_rqmt_header_Rec.ship_to_location_id := nvl(l_header_rec.ship_to_location_id, FND_API.G_MISS_NUM);
1639 l_rqmt_header_Rec.address_type := l_header_Rec.address_type;
1640 l_rqmt_header_Rec.task_id := nvl(l_header_rec.task_id, FND_API.G_MISS_NUM);
1641 l_rqmt_header_Rec.task_assignment_id := nvl(l_header_rec.task_assignment_id, FND_API.G_MISS_NUM);
1642 IF(l_header_rec.task_id IS NULL) THEN
1643 l_rqmt_header_Rec.resource_id := nvl(l_header_rec.resource_id, FND_API.G_MISS_NUM);
1644 l_rqmt_header_Rec.resource_type := nvl(l_header_rec.resource_type, FND_API.G_MISS_CHAR);
1645 END IF;
1646 l_rqmt_header_Rec.need_by_date := nvl(l_header_rec.need_by_date, FND_API.G_MISS_DATE);
1647 l_rqmt_header_Rec.destination_organization_id := nvl(l_header_rec.dest_organization_id, FND_API.G_MISS_NUM);
1648 l_rqmt_header_Rec.destination_subinventory := nvl(l_header_rec.dest_subinventory, FND_API.G_MISS_CHAR);
1649 l_rqmt_header_Rec.shipping_method_code := FND_API.G_MISS_CHAR;
1650 l_rqmt_header_Rec.parts_defined := nvl(l_parts_defined, FND_API.G_MISS_CHAR);
1651 l_rqmt_header_Rec.open_requirement := 'W';
1652 l_rqmt_header_rec.order_type_id := nvl(l_header_rec.order_type_id, FND_PROFILE.value('CSP_ORDER_TYPE'));
1653 l_rqmt_header_rec.attribute_Category := nvl(l_header_rec.attribute_category, FND_API.G_MISS_CHAR);
1654 l_rqmt_header_rec.attribute1 := nvl(l_header_rec.attribute1, FND_API.G_MISS_CHAR);
1655 l_rqmt_header_rec.attribute2 := nvl(l_header_rec.attribute2, FND_API.G_MISS_CHAR);
1656 l_rqmt_header_rec.attribute3 := nvl(l_header_rec.attribute3, FND_API.G_MISS_CHAR);
1657 l_rqmt_header_rec.attribute4 := nvl(l_header_rec.attribute4, FND_API.G_MISS_CHAR);
1658 l_rqmt_header_rec.attribute5 := nvl(l_header_rec.attribute5, FND_API.G_MISS_CHAR);
1659 l_rqmt_header_rec.attribute6 := nvl(l_header_rec.attribute6, FND_API.G_MISS_CHAR);
1660 l_rqmt_header_rec.attribute7 := nvl(l_header_rec.attribute7, FND_API.G_MISS_CHAR);
1661 l_rqmt_header_rec.attribute8 := nvl(l_header_rec.attribute8, FND_API.G_MISS_CHAR);
1662 l_rqmt_header_rec.attribute9 := nvl(l_header_rec.attribute9, FND_API.G_MISS_CHAR);
1663 l_rqmt_header_rec.attribute10 := nvl(l_header_rec.attribute10, FND_API.G_MISS_CHAR);
1664 l_rqmt_header_rec.attribute11 := nvl(l_header_rec.attribute11, FND_API.G_MISS_CHAR);
1665 l_rqmt_header_rec.attribute12 := nvl(l_header_rec.attribute12, FND_API.G_MISS_CHAR);
1666 l_rqmt_header_rec.attribute13 := nvl(l_header_rec.attribute13, FND_API.G_MISS_CHAR);
1667 l_rqmt_header_rec.attribute14 := nvl(l_header_rec.attribute14, FND_API.G_MISS_CHAR);
1668 l_rqmt_header_rec.attribute15 := nvl(l_header_rec.attribute15, FND_API.G_MISS_CHAR);
1669
1670 -- check to see if requirements exist for a given task
1671 /*commenting for 11.5.10 since multiple rqmt headers can exist for a task
1672 IF (l_header_rec.task_id IS NOT NULL) THEN
1673 BEGIN
1674 SELECT requirement_header_id
1675 INTO l_requirement_header_id
1676 FROM csp_requirement_headers
1677 WHERE task_id = l_header_rec.task_id;
1678
1679 IF (l_requirement_header_id IS NOT NULL) THEN
1680 Rollback to save_rqmt_line_PUB;
1681 --FND_MESSAGE.SET_NAME('CSP', 'CSP_TASK_RQMT_EXISTS');
1682 --FND_MSG_PUB.ADD;
1683 --fnd_msg_pub.count_and_get
1684 -- ( p_count => x_msg_count
1685 -- , p_data => x_msg_data);
1686 x_msg_data := fnd_message.get_string('CSP', 'CSP_TASK_RQMT_EXISTS');
1687 x_return_status := 'E';
1688 return;
1689 END IF;
1690 EXCEPTION
1691 WHEN NO_DATA_FOUND THEN
1692 -- call private api for inserting into csp_Requirement_headers
1693 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
1694 P_Api_Version_Number => l_api_Version_number,
1695 P_Init_Msg_List => p_init_msg_list,
1696 P_Commit => FND_API.G_FALSE,
1697 p_validation_level => null,
1698 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
1699 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
1700 X_Return_Status => l_Return_status,
1701 X_Msg_Count => l_msg_count,
1702 X_Msg_Data => l_msg_data
1703 );
1704
1705 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1706 fnd_msg_pub.count_and_get
1707 ( p_count => x_msg_count
1708 , p_data => x_msg_data);
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710 return;
1711 --RAISE FND_API.G_EXC_ERROR;
1712 END IF;
1713 l_header_rec.requirement_header_id := l_requirement_header_id;
1714 WHEN OTHERS THEN
1715 NULL;
1716 END;
1717 ELSE */
1718 -- call private api for inserting into csp_Requirement_headers
1719 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
1720 P_Api_Version_Number => l_api_Version_number,
1721 P_Init_Msg_List => p_init_msg_list,
1722 P_Commit => FND_API.G_FALSE,
1723 p_validation_level => null,
1724 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
1725 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
1726 X_Return_Status => l_Return_status,
1727 X_Msg_Count => l_msg_count,
1728 X_Msg_Data => l_msg_data
1729 );
1730
1731 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1732 RAISE FND_API.G_EXC_ERROR;
1733 END IF;
1734 l_header_rec.requirement_header_id := l_requirement_header_id;
1735 -- END IF;
1736
1737 ELSE
1738 -- update header
1739 -- check to see if requirement_header_id is valid
1740 BEGIN
1741 select requirement_header_id
1742 into l_check_existence
1743 from csp_requirement_headers
1744 where requirement_header_id = l_header_rec.requirement_header_id;
1745 EXCEPTION
1746 WHEN NO_DATA_FOUND THEN
1747 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
1748 fnd_message.set_token('HEADER_ID', to_char(l_header_rec.requirement_header_id), FALSE);
1749 FND_MSG_PUB.ADD;
1750 RAISE EXCP_USER_DEFINED;
1751 WHEN OTHERS THEN
1752 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1753 fnd_message.set_token('ERR_FIELD', 'l_header_rec.requirement_header_id', FALSE);
1754 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
1755 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_HEADERS', FALSE);
1756 FND_MSG_PUB.ADD;
1757 RAISE EXCP_USER_DEFINED;
1758 END;
1759
1760 l_rqmt_header_Rec.requirement_header_id := l_header_rec.requirement_header_id;
1761 l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
1762 l_rqmt_header_Rec.last_update_date := l_today;
1763 l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
1764 l_rqmt_header_Rec.ship_to_location_id := nvl(l_header_rec.ship_to_location_id, FND_API.G_MISS_NUM);
1765 l_rqmt_header_Rec.task_id := nvl(l_header_rec.task_id, FND_API.G_MISS_NUM);
1766 l_rqmt_header_Rec.need_by_date := nvl(l_header_rec.need_by_date, FND_API.G_MISS_DATE);
1767
1768 CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
1769 P_Api_Version_Number => l_api_Version_number,
1770 P_Init_Msg_List => p_init_msg_list,
1771 P_Commit => FND_API.G_FALSE,
1772 p_validation_level => null,
1773 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
1774 X_Return_Status => l_Return_status,
1775 X_Msg_Count => l_msg_count,
1776 X_Msg_Data => l_msg_data
1777 );
1778
1779 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1780 RAISE FND_API.G_EXC_ERROR;
1781 END IF;
1782 END IF;
1783
1784 -- create rqmt line
1785 FOR I IN 1..l_line_tbl.COUNT LOOP
1786 l_line_rec := l_line_tbl(I);
1787 IF (l_line_rec.unit_of_measure IS NULL) THEN
1788 SELECT primary_uom_code
1789 INTO l_line_rec.unit_of_measure
1790 FROM mtl_system_items
1791 WHERE inventory_item_id = l_line_Rec.inventory_item_id
1792 AND organization_id = cs_Std.get_item_valdn_orgzn_id;
1793 END IF;
1794 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
1795
1796 BEGIN
1797 select count(*)
1798 into l_count
1799 from csp_requirement_lines
1800 where requirement_line_id = l_rqmt_line_rec.requirement_line_id;
1801 IF l_count > 0 THEN
1802 fnd_message.set_name('CSP', 'CSP_DUPLICATE_RQMT_LINE');
1803 FND_MSG_PUB.ADD;
1804 RAISE EXCP_USER_DEFINED;
1805 END IF;
1806 EXCEPTION
1807 WHEN NO_DATA_FOUND THEN
1808 null;
1809 END;
1810 l_rqmt_line_Rec.created_by := nvl(l_user_id, -1);
1811 l_rqmt_line_Rec.creation_date := l_today;
1812 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
1813 l_rqmt_line_rec.last_update_date := l_today;
1814 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
1815 l_rqmt_line_rec.requirement_header_id := l_header_rec.requirement_header_id;
1816 l_rqmt_line_rec.inventory_item_id := l_line_rec.inventory_item_id;
1817 l_rqmt_line_rec.uom_code := l_line_rec.unit_of_measure;
1818 l_rqmt_line_rec.required_quantity := l_line_rec.quantity;
1819 l_rqmt_line_rec.ship_complete_flag := l_line_rec.ship_complete;
1820 l_rqmt_line_rec.likelihood := l_line_rec.likelihood;
1821 l_rqmt_line_rec.revision := l_line_rec.revision;
1822 l_rqmt_line_rec.ordered_quantity := l_line_Rec.quantity;
1823 l_rqmt_line_rec.attribute_Category := nvl(l_line_rec.attribute_category, FND_API.G_MISS_CHAR);
1824 l_rqmt_line_rec.attribute1 := nvl(l_line_rec.attribute1, FND_API.G_MISS_CHAR);
1825 l_rqmt_line_rec.attribute2 := nvl(l_line_rec.attribute2, FND_API.G_MISS_CHAR);
1826 l_rqmt_line_rec.attribute3 := nvl(l_line_rec.attribute3, FND_API.G_MISS_CHAR);
1827 l_rqmt_line_rec.attribute4 := nvl(l_line_rec.attribute4, FND_API.G_MISS_CHAR);
1828 l_rqmt_line_rec.attribute5 := nvl(l_line_rec.attribute5, FND_API.G_MISS_CHAR);
1829 l_rqmt_line_rec.attribute6 := nvl(l_line_rec.attribute6, FND_API.G_MISS_CHAR);
1830 l_rqmt_line_rec.attribute7 := nvl(l_line_rec.attribute7, FND_API.G_MISS_CHAR);
1831 l_rqmt_line_rec.attribute8 := nvl(l_line_rec.attribute8, FND_API.G_MISS_CHAR);
1832 l_rqmt_line_rec.attribute9 := nvl(l_line_rec.attribute9, FND_API.G_MISS_CHAR);
1833 l_rqmt_line_rec.attribute10 := nvl(l_line_rec.attribute10, FND_API.G_MISS_CHAR);
1834 l_rqmt_line_rec.attribute11 := nvl(l_line_rec.attribute11, FND_API.G_MISS_CHAR);
1835 l_rqmt_line_rec.attribute12 := nvl(l_line_rec.attribute12, FND_API.G_MISS_CHAR);
1836 l_rqmt_line_rec.attribute13 := nvl(l_line_rec.attribute13, FND_API.G_MISS_CHAR);
1837 l_rqmt_line_rec.attribute14 := nvl(l_line_rec.attribute14, FND_API.G_MISS_CHAR);
1838 l_rqmt_line_rec.attribute15 := nvl(l_line_rec.attribute15, FND_API.G_MISS_CHAR);
1839
1840 l_rqmt_line_tbl(I) := l_rqmt_line_rec;
1841 END LOOP;
1842
1843 CSP_Requirement_Lines_PVT.Create_requirement_lines(
1844 P_Api_Version_Number => l_api_version_number,
1845 P_Init_Msg_List => p_Init_Msg_List,
1846 P_Commit => FND_API.G_FALSE,
1847 p_validation_level => null,
1848 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
1849 x_Requirement_Line_tbl => l_rqmt_line_tbl_out,
1850 X_Return_Status => l_return_status,
1851 X_Msg_Count => l_msg_count,
1852 X_Msg_Data => l_msg_data
1853 );
1854
1855 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1856 RAISE FND_API.G_EXC_ERROR;
1857 END IF;
1858
1859 -- Return header rec and line table.
1860 px_header_rec := l_header_rec;
1861 OPEN rqmt_lines_cur(l_header_rec.requirement_header_id);
1862 I := 1;
1863 LOOP
1864 FETCH rqmt_lines_cur INTO rqmt_line;
1865 EXIT WHEN rqmt_lines_cur%NOTFOUND;
1866 px_line_tbl(I).requirement_line_id := rqmt_line.requirement_line_id;
1867 px_line_tbl(I).inventory_item_id := rqmt_line.inventory_item_id;
1868 px_line_tbl(I).revision := rqmt_line.revision;
1869 px_line_tbl(I).quantity := rqmt_line.required_quantity;
1870 px_line_tbl(I).unit_of_measure := rqmt_line.uom_code;
1871 I := I + 1;
1872 END LOOP;
1873
1874 IF fnd_api.to_boolean(p_commit) THEN
1875 commit work;
1876 END IF;
1877
1878 x_return_status := FND_API.G_RET_STS_SUCCESS;
1879 EXCEPTION
1880 WHEN EXCP_USER_DEFINED THEN
1881 Rollback to save_rqmt_line_PUB;
1882 x_return_status := FND_API.G_RET_STS_ERROR;
1883 fnd_msg_pub.count_and_get
1884 ( p_count => x_msg_count
1885 , p_data => x_msg_data);
1886
1887 WHEN FND_API.G_EXC_ERROR THEN
1888 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1889 P_API_NAME => L_API_NAME
1890 ,P_PKG_NAME => G_PKG_NAME
1891 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1892 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1893 ,X_MSG_COUNT => X_MSG_COUNT
1894 ,X_MSG_DATA => X_MSG_DATA
1895 ,X_RETURN_STATUS => X_RETURN_STATUS);
1896
1897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1898 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1899 P_API_NAME => L_API_NAME
1900 ,P_PKG_NAME => G_PKG_NAME
1901 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1902 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1903 ,X_MSG_COUNT => X_MSG_COUNT
1904 ,X_MSG_DATA => X_MSG_DATA
1905 ,X_RETURN_STATUS => X_RETURN_STATUS);
1906 WHEN OTHERS THEN
1907 Rollback to save_rqmt_line_PUB;
1908 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1909 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1910 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1911 FND_MSG_PUB.ADD;
1912 fnd_msg_pub.count_and_get
1913 ( p_count => x_msg_count
1914 , p_data => x_msg_data);
1915 x_return_status := FND_API.G_RET_STS_ERROR;
1916 END;
1917
1918
1919 PROCEDURE delete_rqmt_line(
1920 p_api_version IN NUMBER
1921 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1922 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1923 ,p_line_tbl IN OUT NOCOPY csp_parts_requirement.Rqmt_Line_tbl_type
1924 ,x_return_status OUT NOCOPY VARCHAR2
1925 ,x_msg_count OUT NOCOPY NUMBER
1926 ,x_msg_data OUT NOCOPY VARCHAR2
1927 ) IS
1928 l_api_version_number CONSTANT NUMBER := 1.0;
1929 l_api_name CONSTANT VARCHAR2(30) := 'delete_rqmt_line';
1930 BEGIN
1931 SAVEPOINT delete_rqmt_line_PUB;
1932
1933 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1934 -- initialize message list
1935 FND_MSG_PUB.initialize;
1936 END IF;
1937
1938 -- Standard call to check for call compatibility.
1939 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1940 p_api_version,
1941 l_api_name,
1942 G_PKG_NAME)
1943 THEN
1944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1945 END IF;
1946
1947 -- initialize return status
1948 x_return_status := FND_API.G_RET_STS_SUCCESS;
1949
1950 IF (p_line_tbl.count > 0) THEN
1951 FOR I IN 1..p_line_Tbl.count LOOP
1952 DELETE FROM csp_Requirement_lines
1953 WHERE requirement_line_id = p_line_tbl(I).requirement_line_id;
1954 END LOOP;
1955 END IF;
1956
1957 IF fnd_api.to_boolean(p_commit) THEN
1958 commit work;
1959 END IF;
1960 x_return_status := FND_API.G_RET_STS_SUCCESS;
1961
1962 EXCEPTION
1963
1964 WHEN FND_API.G_EXC_ERROR THEN
1965 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1966 P_API_NAME => L_API_NAME
1967 ,P_PKG_NAME => G_PKG_NAME
1968 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1969 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1970 ,X_MSG_COUNT => X_MSG_COUNT
1971 ,X_MSG_DATA => X_MSG_DATA
1972 ,X_RETURN_STATUS => X_RETURN_STATUS);
1973 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1974 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1975 P_API_NAME => L_API_NAME
1976 ,P_PKG_NAME => G_PKG_NAME
1977 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1978 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1979 ,X_MSG_COUNT => X_MSG_COUNT
1980 ,X_MSG_DATA => X_MSG_DATA
1981 ,X_RETURN_STATUS => X_RETURN_STATUS);
1982 WHEN OTHERS THEN
1983 Rollback to delete_rqmt_line_PUB;
1984 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1985 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1986 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1987 FND_MSG_PUB.ADD;
1988 fnd_msg_pub.count_and_get
1989 ( p_count => x_msg_count
1990 , p_data => x_msg_data);
1991 x_return_status := FND_API.G_RET_STS_ERROR;
1992 END;
1993
1994 PROCEDURE check_Availability(
1995 p_api_version IN NUMBER
1996 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1997 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1998 ,p_header_id IN NUMBER
1999 ,x_line_Tbl OUT NOCOPY csp_parts_requirement.Line_tbl_type
2000 ,x_avail_flag OUT NOCOPY VARCHAR2
2001 ,x_return_status OUT NOCOPY VARCHAR2
2002 ,x_msg_count OUT NOCOPY NUMBER
2003 ,x_msg_data OUT NOCOPY VARCHAR2
2004 )IS
2005 l_api_version_number CONSTANT NUMBER := 1.0;
2006 l_api_name CONSTANT VARCHAR2(30) := 'check_availability';
2007 l_count NUMBER;
2008 l_header_rec csp_parts_requirement.Header_rec_type;
2009 l_line_rec csp_parts_requirement.line_rec_type;
2010 l_line_tbl csp_parts_Requirement.line_Tbl_type;
2011 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
2012 EXCP_USER_DEFINED EXCEPTION;
2013 l_parts_list_rec csp_sch_int_pvt.csp_parts_rec_type;
2014 l_parts_list_tbl csp_sch_int_pvt.csp_parts_tbl_typ1;
2015 l_avail_list_tbl csp_sch_int_pvt.available_parts_tbl_typ1;
2016 I NUMBER;
2017 J NUMBER;
2018 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2019 l_msg_count NUMBER;
2020 l_msg_data VARCHAR2(4000);
2021
2022 CURSOR rqmt_line_cur IS
2023 select crl.requirement_line_id,
2024 crl.inventory_item_id,
2025 crl.uom_code,
2026 crl.required_quantity
2027 from csp_requirement_lines crl,
2028 csp_req_line_details crld
2029 where crld.requirement_line_id(+) = crl.requirement_line_id
2030 and crl.requirement_header_id = p_header_id
2031 and crld.source_id is null;
2032
2033 BEGIN
2034 SAVEPOINT check_Availability_PUB;
2035
2036 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2037 -- initialize message list
2038 FND_MSG_PUB.initialize;
2039 END IF;
2040
2041 -- Standard call to check for call compatibility.
2042 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2043 p_api_version,
2044 l_api_name,
2045 G_PKG_NAME)
2046 THEN
2047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2048 END IF;
2049
2050 -- initialize return status
2051 x_return_status := FND_API.G_RET_STS_SUCCESS;
2052
2053 IF (p_header_id IS NOT NULL) THEN
2054 select count(*)
2055 into l_count
2056 from csp_requirement_lines crl,
2057 csp_req_line_details crld
2058 where crld.requirement_line_id(+) = crl.requirement_line_id
2059 and crl.requirement_header_id = p_header_id
2060 and crld.source_id is null;
2061
2062 IF (l_count > 0) THEN
2063 BEGIN
2064 SELECT
2065 requirement_header_id,
2066 need_by_date,
2067 destination_organization_id,
2068 destination_subinventory
2069 INTO
2070 l_header_rec.requirement_header_id,
2071 l_header_rec.need_by_Date,
2072 l_header_rec.dest_organization_id,
2073 l_header_rec.dest_subinventory
2074 FROM csp_Requirement_headers
2075 WHERE requirement_header_id = p_header_id;
2076
2077 EXCEPTION
2078 WHEN no_data_found THEN
2079 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
2080 fnd_message.set_token('HEADER_ID', to_char(p_header_id), FALSE);
2081 FND_MSG_PUB.ADD;
2082 RAISE EXCP_USER_DEFINED;
2083 END;
2084
2085 OPEN rqmt_line_cur;
2086 I := 1;
2087 LOOP
2088 FETCH rqmt_line_cur
2089 INTO l_line_rec.requirement_line_id,
2090 l_line_rec.inventory_item_id,
2091 l_line_rec.unit_of_measure,
2092 l_line_rec.quantity;
2093 EXIT WHEN rqmt_line_cur%NOTFOUND;
2094 l_line_tbl(I) := l_line_rec;
2095 l_parts_list_tbl(I).item_id := l_line_rec.inventory_item_id;
2096 l_parts_list_tbl(I).item_uom := l_line_rec.unit_of_measure;
2097 l_parts_list_tbl(I).quantity := l_line_rec.quantity;
2098 l_parts_list_tbl(I).line_id := l_line_rec.requirement_line_id;
2099 I := I + 1;
2100 END LOOP;
2101
2102 IF (l_parts_list_Tbl.count > 0) THEN
2103 FND_MSG_PUB.initialize;
2104 -- call csp_sch_int_pvt.check_parts_availability()
2105 csp_sch_int_pvt.check_parts_availability(
2106 p_resource => null,
2107 p_organization_id => l_header_rec.dest_organization_id,
2108 p_subinv_code => l_header_rec.dest_subinventory,
2109 p_need_by_date => l_header_rec.need_by_date,
2110 p_parts_list => l_parts_list_tbl,
2111 p_timezone_id => null,
2112 x_availability => l_avail_list_tbl,
2113 x_return_status => l_return_status,
2114 x_msg_data => l_msg_data,
2115 x_msg_count => l_msg_count,
2116 p_Called_From => 'MOBILE',
2117 p_location_id => l_header_rec.ship_to_location_id
2118 );
2119
2120 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2121 null;
2122 --RAISE FND_API.G_EXC_ERROR;
2123 END IF;
2124
2125 J := 1;
2126 I := 1;
2127 IF (l_Avail_list_tbl.COUNT > 0) THEN
2128 WHILE (I <= l_line_tbl.COUNT AND J <= l_Avail_list_Tbl.COUNT) LOOP
2129 IF (l_line_tbl(I).requirement_line_id = l_avail_list_Tbl(J).line_id) THEN
2130 IF (nvl(l_Avail_list_tbl(J).recommended_option, 'N') = 'Y') THEN
2131 -- IF (l_avail_list_tbl(J).source_type = 'INVENTORY') THEN
2132 IF (l_line_Tbl(I).inventory_item_id <> l_avail_list_tbl(I).item_id) THEN
2133 l_line_tbl(I).inventory_item_id := l_Avail_list_tbl(I).item_id;
2134 END IF;
2135 IF (l_line_Tbl(I).arrival_date > l_header_rec.need_by_date) THEN
2136 l_line_tbl(I).available_by_need_date := 'N';
2137 ELSE
2138 l_line_tbl(I).available_by_need_date := 'Y';
2139 END IF;
2140 l_line_tbl(I).sourced_from := l_Avail_list_tbl(J).source_type;
2141 l_line_tbl(I).source_organization_id := l_avail_list_tbl(J).source_org_id;
2142 l_line_tbl(I).arrival_Date := l_Avail_list_tbl(J).arraival_date;
2143 l_line_Tbl(I).shipping_method_code := l_Avail_list_tbl(J).shipping_methode;
2144 l_line_tbl(I).order_by_date := l_Avail_list_Tbl(J).order_by_Date;
2145 l_line_Tbl(I).ordered_quantity := l_Avail_list_Tbl(J).ordered_quantity;
2146 J := J + 1;
2147 I := I + 1;
2148 --END IF;
2149 ELSE
2150 J := J+1;
2151 END IF;
2152 ELSE
2153 J := J + 1;
2154 END IF;
2155 END LOOP;
2156 END IF;
2157 FOR I IN 1..l_line_tbl.COUNT LOOP
2158 IF (nvl(l_line_tbl(I).sourced_from, 'IO') = 'IO' and l_line_Tbl(I).source_organization_id IS NULL) THEN
2159 Get_source_organization(P_Inventory_Item_Id => l_line_Tbl(I).inventory_item_id,
2160 P_Organization_Id => l_header_Rec.dest_organization_id,
2161 P_Secondary_Inventory => l_header_rec.dest_subinventory,
2162 x_source_org_id => l_line_Tbl(I).source_organization_id);
2163 IF ((l_line_Tbl(I).source_organization_id IS NULL) OR
2164 (l_line_Tbl(I).source_organization_id = FND_API.G_MISS_NUM)) THEN
2165 -- no source organization defined, create requirement with error status
2166 x_avail_flag := 'N';
2167 END IF;
2168 END If;
2169 -- insert into l_rqmt_line_rec for updation.
2170 l_rqmt_line_tbl(I).requirement_line_id := l_line_tbl(I).requirement_line_id;
2171 l_rqmt_line_tbl(I).last_updated_by := nvl(FND_GLOBAL.user_id, 1);
2172 l_rqmt_line_tbl(I).last_update_date := sysdate;
2173 l_rqmt_line_tbl(I).last_update_login := nvl(FND_GLOBAL.login_id , -1);
2174 l_rqmt_line_tbl(I).inventory_item_id := l_line_tbl(I).inventory_item_id;
2175 l_rqmt_line_tbl(I).sourced_From := l_line_tbl(I).sourced_From;
2176 l_rqmt_line_tbl(I).source_organization_id := nvl(l_line_tbl(I).source_organization_id, FND_API.G_MISS_NUM);
2177 l_rqmt_line_tbl(I).arrival_Date := nvl(l_line_tbl(I).arrival_Date, FND_API.G_MISS_DATE);
2178 l_rqmt_line_tbl(I).shipping_method_code := nvl(l_line_Tbl(I).shipping_method_code, FND_API.G_MISS_CHAR);
2179 l_rqmt_line_tbl(I).order_by_date := nvl(l_line_tbl(I).order_by_date, FND_API.G_MISS_DATE);
2180 l_rqmt_line_tbl(I).ordered_quantity := nvl(l_line_Tbl(I).ordered_quantity, 0);
2181 END LOOP;
2182
2183 IF l_rqmt_line_tbl.COUNT > 0 THEN
2184 CSP_Requirement_Lines_PVT.Update_requirement_lines(
2185 P_Api_Version_Number => l_api_version_number,
2186 P_Init_Msg_List => p_Init_Msg_List,
2187 P_Commit => FND_API.G_FALSE,
2188 p_validation_level => null,
2189 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
2190 X_Return_Status => l_return_status,
2191 X_Msg_Count => l_msg_count,
2192 X_Msg_Data => l_msg_data
2193 );
2194
2195 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2196 RAISE FND_API.G_EXC_ERROR;
2197 END IF;
2198 END IF;
2199 END If;
2200 x_line_tbl := l_line_tbl;
2201 x_avail_flag := nvl(x_avail_flag, 'Y');
2202 END IF;
2203
2204 IF fnd_api.to_boolean(p_commit) THEN
2205 commit work;
2206 END IF;
2207 END IF;
2208 EXCEPTION
2209 WHEN EXCP_USER_DEFINED THEN
2210 Rollback to check_Availability_PUB;
2211 x_return_status := FND_API.G_RET_STS_ERROR;
2212 fnd_msg_pub.count_and_get
2213 ( p_count => x_msg_count
2214 , p_data => x_msg_data);
2215
2216 WHEN FND_API.G_EXC_ERROR THEN
2217 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2218 P_API_NAME => L_API_NAME
2219 ,P_PKG_NAME => G_PKG_NAME
2220 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2221 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2222 ,X_MSG_COUNT => X_MSG_COUNT
2223 ,X_MSG_DATA => X_MSG_DATA
2224 ,X_RETURN_STATUS => X_RETURN_STATUS);
2225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2226 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2227 P_API_NAME => L_API_NAME
2228 ,P_PKG_NAME => G_PKG_NAME
2229 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2230 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2231 ,X_MSG_COUNT => X_MSG_COUNT
2232 ,X_MSG_DATA => X_MSG_DATA
2233 ,X_RETURN_STATUS => X_RETURN_STATUS);
2234 WHEN OTHERS THEN
2235 Rollback to check_Availability_pub;
2236 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2237 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2238 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2239 FND_MSG_PUB.ADD;
2240 fnd_msg_pub.count_and_get
2241 ( p_count => x_msg_count
2242 , p_data => x_msg_data);
2243 x_return_status := FND_API.G_RET_STS_ERROR;
2244 END;
2245
2246 PROCEDURE create_order(
2247 p_api_version IN NUMBER
2248 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2249 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2250 ,p_header_id IN NUMBER
2251 ,x_order_tbl OUT NOCOPY Order_Tbl_Type
2252 ,x_return_status OUT NOCOPY VARCHAR2
2253 ,x_msg_count OUT NOCOPY NUMBER
2254 ,x_msg_data OUT NOCOPY VARCHAR2
2255 )IS
2256 l_api_version_number CONSTANT NUMBER := 1.0;
2257 l_api_name CONSTANT VARCHAR2(30) := 'create_order';
2258 l_count NUMBER;
2259 l_header_rec csp_parts_requirement.Header_rec_type;
2260 l_line_rec csp_parts_requirement.line_rec_type;
2261 l_line_tbl csp_parts_Requirement.line_Tbl_type;
2262 l_oe_line_tbl csp_parts_requirement.line_Tbl_Type;
2263 l_po_line_Tbl csp_parts_requirement.line_tbl_type;
2264 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
2265 EXCP_USER_DEFINED EXCEPTION;
2266 I NUMBER;
2267 J NUMBER;
2268 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2269 l_msg_count NUMBER;
2270 l_msg_data VARCHAR2(2000);
2271 l_order_number NUMBER;
2272 l_open_requirement VARCHAr2(30);
2273 l_req_line_dtl_id NUMBER;
2274 l_user_id NUMBER;
2275 l_login_id NUMBER;
2276 l_today DATE;
2277
2278 CURSOR rqmt_line_cur IS
2279 SELECT c.requirement_line_id,
2280 c.inventory_item_id,
2281 c.revision,
2282 c.uom_code,
2283 c.required_quantity,
2284 c.sourced_from,
2285 c.source_organization_id,
2286 c.source_subinventory,
2287 c.shipping_method_code,
2288 c.arrival_date
2289 FROM csp_requirement_lines c
2290 WHERE c.requirement_header_id = p_header_id
2291 AND not exists (select 1 from csp_req_line_Details d where d.requirement_line_id = c.requirement_line_id)
2292 FOR UPDATE of c.order_line_id NOWAIT;
2293
2294 BEGIN
2295
2296 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2298 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2299 'Begin');
2300 end if;
2301
2302 SAVEPOINT create_order_PUB;
2303
2304 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2305 -- initialize message list
2306 FND_MSG_PUB.initialize;
2307 END IF;
2308
2309 -- Standard call to check for call compatibility.
2310 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2311 p_api_version,
2312 l_api_name,
2313 G_PKG_NAME)
2314 THEN
2315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2316 END IF;
2317
2318 -- initialize return status
2319 x_return_status := FND_API.G_RET_STS_SUCCESS;
2320
2321 -- user and login information
2322 SELECT Sysdate INTO l_today FROM dual;
2323 l_user_id := fnd_global.user_id;
2324 l_login_id := fnd_global.login_id;
2325
2326 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2327 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2328 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2329 'l_user_id = ' || l_user_id
2330 || ', l_login_id = ' || l_login_id);
2331 end if;
2332
2333 IF (p_header_id IS NOT NULL) THEN
2334 select count(*)
2335 into l_count
2336 from csp_requirement_lines crl,
2337 csp_req_line_details crld
2338 where crld.requirement_line_id(+) = crl.requirement_line_id
2339 and crl.requirement_header_id = p_header_id
2340 and crld.source_id is null;
2341
2342 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2343 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2344 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2345 'p_header_id is NOT NULL and number of lines for this requirement is = ' || l_count);
2346 end if;
2347
2348 IF (l_count > 0) THEN
2349 BEGIN
2350 SELECT
2351 requirement_header_id,
2352 nvl(order_type_id, FND_PROFILE.value('CSP_ORDER_TYPE')),
2353 ship_to_location_id,
2354 need_by_date,
2355 destination_organization_id,
2356 destination_subinventory
2357 INTO
2358 l_header_rec.requirement_header_id,
2359 l_header_rec.order_type_id,
2360 l_header_Rec.ship_to_location_id,
2361 l_header_rec.need_by_Date,
2362 l_header_rec.dest_organization_id,
2363 l_header_rec.dest_subinventory
2364 FROM csp_Requirement_headers
2365 WHERE requirement_header_id = p_header_id;
2366
2367 l_header_Rec.operation := G_OPR_CREATE;
2368
2369 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2370 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2371 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2372 'Part Req Header Data... '
2373 || 'l_header_rec.requirement_header_id = ' || l_header_rec.requirement_header_id
2374 || ', l_header_rec.order_type_id = ' || l_header_rec.order_type_id
2375 || ', l_header_Rec.ship_to_location_id = ' || l_header_Rec.ship_to_location_id
2376 || ', l_header_rec.need_by_Date = ' || l_header_rec.need_by_Date
2377 || ', l_header_rec.dest_organization_id = ' || l_header_rec.dest_organization_id
2378 || ', l_header_rec.dest_subinventory = ' || l_header_rec.dest_subinventory
2379 || ', l_header_Rec.operation = ' || l_header_Rec.operation);
2380 end if;
2381
2382 EXCEPTION
2383 WHEN no_data_found THEN
2384 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
2385 fnd_message.set_token('HEADER_ID', to_char(p_header_id), FALSE);
2386 FND_MSG_PUB.ADD;
2387 RAISE EXCP_USER_DEFINED;
2388 END;
2389
2390 OPEN rqmt_line_cur;
2391 I := 1;
2392 J := 1;
2393 LOOP
2394 FETCH rqmt_line_cur
2395 INTO l_line_rec.requirement_line_id,
2396 l_line_rec.inventory_item_id,
2397 l_line_Rec.revision,
2398 l_line_rec.unit_of_measure,
2399 l_line_rec.quantity,
2400 l_line_rec.sourced_from,
2401 l_line_rec.source_organization_id,
2402 l_line_rec.source_subinventory,
2403 l_line_rec.shipping_method_code,
2404 l_line_rec.arrival_Date ;
2405
2406 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2407 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2408 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2409 'Part Req Line Data... '
2410 || 'l_line_rec.requirement_line_id = ' || l_line_rec.requirement_line_id
2411 || ', l_line_rec.inventory_item_id = ' || l_line_rec.inventory_item_id
2412 || ', l_line_Rec.revision = ' || l_line_Rec.revision
2413 || ', l_line_rec.unit_of_measure = ' || l_line_rec.unit_of_measure
2414 || ', l_line_rec.quantity = ' || l_line_rec.quantity
2415 || ', l_line_rec.sourced_from = ' || l_line_rec.sourced_from
2416 || ', l_line_rec.source_organization_id = ' || l_line_rec.source_organization_id
2417 || ', l_line_rec.source_subinventory = ' || l_line_rec.source_subinventory
2418 || ', l_line_rec.shipping_method_code = ' || l_line_rec.shipping_method_code
2419 || ', l_line_rec.arrival_Date = ' || l_line_rec.arrival_Date);
2420 end if;
2421
2422 EXIT WHEN rqmt_line_cur%NOTFOUND;
2423 IF (l_line_rec.sourced_From = 'IO' and l_line_rec.source_organization_id IS NULL) THEN
2424 fnd_message.set_name('CSP', 'CSP_NULL_SRC_ORGN');
2425 fnd_msg_pub.add;
2426 RAISE EXCP_USER_DEFINED;
2427 END If;
2428 l_line_rec.ordered_quantity := l_line_Rec.quantity;
2429 IF (l_line_rec.sourced_from = 'IO') THEN
2430 l_line_rec.line_num := I;
2431 l_oe_line_tbl(I) := l_line_rec;
2432 I := I + 1;
2433 ELSIF (l_line_Rec.sourced_From = 'POREQ') THEN
2434 l_line_Rec.line_num := J;
2435 l_po_line_Tbl(J) := l_line_rec;
2436 J := J + 1;
2437 END IF;
2438 END LOOP;
2439
2440 IF (l_oe_line_tbl.COUNT > 0) THEN
2441 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2442 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2443 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2444 'Calling csp_parts_order.process_order...');
2445 end if;
2446 csp_parts_order.process_order(
2447 p_api_version => l_api_version_number
2448 ,p_Init_Msg_List => p_init_msg_list
2449 ,p_commit => FND_API.G_FALSE
2450 ,px_header_rec => l_header_rec
2451 ,px_line_table => l_oe_line_tbl
2452 ,x_return_status => l_return_status
2453 ,x_msg_count => l_msg_count
2454 ,x_msg_data => l_msg_data
2455 );
2456 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2457 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2458 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2459 'csp_parts_order.process_order return status = ' || l_return_status);
2460 end if;
2461
2462 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2463 RAISE FND_API.G_EXC_ERROR;
2464 ELSE
2465 -- update open_requirement_flag on requirement header
2466 BEGIN
2467 l_open_requirement := 'S';
2468 update csp_requirement_headers
2469 set open_requirement = 'S'
2470 where requirement_header_id = p_header_id;
2471 EXCEPTION
2472 when others then
2473 null;
2474 END;
2475 IF (l_header_rec.order_header_id IS NOT NULL) THEN
2476 BEGIN
2477 SELECT order_number
2478 INTO l_order_number
2479 FROM OE_ORDER_HEADERS_ALL
2480 WHERE header_id = l_header_Rec.order_header_id;
2481
2482 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2483 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2484 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2485 'l_order_number = ' || l_order_number);
2486 end if;
2487 EXCEPTION
2488 when no_data_found then
2489 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2490 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2491 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2492 'No Data Found for l_header_rec.order_header_id = '
2493 || l_header_rec.order_header_id || ' in OE_ORDER_HEADERS_ALL');
2494 end if;
2495 null;
2496 END;
2497 END IF;
2498
2499 x_order_tbl(1).source_type := 'IO';
2500 x_order_tbl(1).order_number := l_order_number;
2501 -- x_order_tbl(1).line_tbl := l_oe_line_tbl;
2502
2503 -- update csp_requirement_line table with order information
2504 FOR I IN 1..l_oe_line_tbl.COUNT LOOP
2505 l_line_rec := l_oe_line_tbl(I);
2506
2507 SELECT csp_req_line_Details_s1.nextval
2508 INTO l_req_line_Dtl_id
2509 FROM dual;
2510
2511 csp_req_line_Details_pkg.Insert_Row(
2512 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
2513 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
2514 p_CREATED_BY => nvl(l_user_id, 1),
2515 p_CREATION_DATE => sysdate,
2516 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
2517 p_LAST_UPDATE_DATE => sysdate,
2518 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
2519 p_SOURCE_TYPE => 'IO',
2520 p_SOURCE_ID => l_line_rec.order_line_id);
2521 END LOOP;
2522 END IF;
2523 END IF;
2524
2525 -- create purchase requisitions if po_line_tbl has atleast one record
2526 IF (l_po_line_tbl.count > 0) THEN
2527
2528 l_header_rec.requisition_header_id := null;
2529 l_header_rec.requisition_number := null;
2530
2531 csp_parts_order.process_purchase_req(
2532 p_api_version => l_api_version_number
2533 ,p_init_msg_list => p_init_msg_list
2534 ,p_commit => p_commit
2535 ,px_header_rec => l_header_Rec
2536 ,px_line_Table => l_po_line_tbl
2537 ,x_return_status => l_return_status
2538 ,x_msg_count => l_msg_count
2539 ,x_msg_data => l_msg_data
2540 );
2541
2542 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2543 RAISE FND_API.G_EXC_ERROR;
2544 ELSE
2545 IF (nvl(l_open_requirement, 'W') <> 'S') THEN
2546 update csp_requirement_headers
2547 set open_requirement = 'S'
2548 where requirement_header_id = p_header_id;
2549 END IF;
2550
2551 x_order_tbl(2).source_type := 'POREQ';
2552 x_order_tbl(2).order_number := l_header_rec.requisition_number;
2553 -- x_order_tbl(2).line_tbl := l_po_line_tbl;
2554
2555 -- insert into csp_req_line_Details table with purchase req information
2556 FOR I IN 1..l_po_line_tbl.COUNT LOOP
2557 l_line_rec := l_po_line_tbl(I);
2558
2559 SELECT csp_req_line_Details_s1.nextval
2560 INTO l_req_line_Dtl_id
2561 FROM dual;
2562
2563 csp_req_line_Details_pkg.Insert_Row(
2564 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
2565 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
2566 p_CREATED_BY => nvl(l_user_id, 1),
2567 p_CREATION_DATE => sysdate,
2568 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
2569 p_LAST_UPDATE_DATE => sysdate,
2570 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
2571 p_SOURCE_TYPE => 'POREQ',
2572 p_SOURCE_ID => l_line_rec.requisition_line_id);
2573 END LOOP;
2574 END IF;
2575 END IF;
2576 END IF;
2577
2578 x_return_status := FND_API.G_RET_STS_SUCCESS;
2579 IF fnd_api.to_boolean(p_commit) THEN
2580 commit work;
2581 END IF;
2582 END If;
2583 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2584 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2585 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2586 'Returning from csp.plsql.CSP_PARTS_REQUIREMENT.create_order with return status = '
2587 || x_return_status);
2588 end if;
2589 EXCEPTION
2590 WHEN EXCP_USER_DEFINED THEN
2591 Rollback to create_order_PUB;
2592 x_return_status := FND_API.G_RET_STS_ERROR;
2593 fnd_msg_pub.count_and_get
2594 ( p_count => x_msg_count
2595 , p_data => x_msg_data);
2596
2597 WHEN FND_API.G_EXC_ERROR THEN
2598 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2599 P_API_NAME => L_API_NAME
2600 ,P_PKG_NAME => G_PKG_NAME
2601 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2602 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2603 ,X_MSG_COUNT => X_MSG_COUNT
2604 ,X_MSG_DATA => X_MSG_DATA
2605 ,X_RETURN_STATUS => X_RETURN_STATUS);
2606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2607 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2608 P_API_NAME => L_API_NAME
2609 ,P_PKG_NAME => G_PKG_NAME
2610 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2611 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2612 ,X_MSG_COUNT => X_MSG_COUNT
2613 ,X_MSG_DATA => X_MSG_DATA
2614 ,X_RETURN_STATUS => X_RETURN_STATUS);
2615 WHEN OTHERS THEN
2616 Rollback to create_order_PUB;
2617 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2618 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2619 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2620 FND_MSG_PUB.ADD;
2621 fnd_msg_pub.count_and_get
2622 ( p_count => x_msg_count
2623 , p_data => x_msg_data);
2624 x_return_status := FND_API.G_RET_STS_ERROR;
2625 END;
2626
2627 PROCEDURE TASK_ASSIGNMENT_POST_UPDATE(x_return_status out nocopy varchar2)IS
2628 l_task_assignment_id NUMBER;
2629 l_resource_id NUMBER;
2630 l_resource_type_code VARCHAR2(30);
2631 l_rqmt_header_id NUMBER;
2632 l_ship_to_location_id NUMBER;
2633 l_organization_id NUMBER;
2634 l_subinventory_code VARCHAR2(30);
2635
2636 CURSOR get_rqmt_Header IS
2637 SELECT requirement_header_id
2638 FROM csp_requirement_headers
2639 WHERE task_assignment_id = l_Task_Assignment_id;
2640
2641 CURSOR get_resource_location IS
2642 SELECT ship_to_location_id
2643 FROM csp_rs_ship_to_addresses_v
2644 WHERE resource_id = l_resource_id
2645 AND resource_type = l_resource_type_code
2646 AND primary_flag = 'Y';
2647
2648 CURSOR get_Resource_org_sub IS
2649 SELECT organization_id, subinventory_code
2650 FROM csp_inv_loc_assignments
2651 WHERE resource_id = l_resource_id
2652 AND resource_type = l_resource_type_code
2653 AND default_code = 'IN';
2654
2655 BEGIN
2656 x_return_status := FND_API.G_RET_STS_SUCCESS;
2657 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
2658 l_resource_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_id;
2659 l_resource_type_code := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_type_code;
2660
2661 IF (nvl(l_Resource_id, 0) <> nvl(G_old_resource_id, 0))THEN
2662 OPEN get_resource_location;
2663 FETCH get_resource_location INTO l_ship_to_location_id;
2664 CLOSE get_resource_location;
2665
2666 OPEN get_Resource_org_sub;
2667 FETCH get_resource_org_sub INTO l_organization_id, l_subinventory_code;
2668 CLOSE get_resource_org_sub;
2669
2670 OPEN get_rqmt_header;
2671 LOOP
2672 FETCH get_rqmt_Header INTO l_rqmt_header_id;
2673 EXIT WHEN get_rqmt_Header%NOTFOUND;
2674
2675 IF l_rqmt_header_id IS NOT NULL THEN
2676 UPDATE csp_requirement_headers
2677 SET task_assignment_id = l_task_assignment_id,
2678 ship_to_location_id = l_ship_to_location_id,
2679 destination_organization_id = l_organization_id,
2680 destination_subinventory = l_subinventory_code
2681 WHERE requirement_header_id = l_rqmt_header_id;
2682 END IF;
2683 END LOOP;
2684 CLOSE get_rqmt_header;
2685 END IF;
2686 END;
2687
2688 PROCEDURE TASK_ASSIGNMENT_PRE_UPDATE
2689 ( x_return_status OUT NOCOPY varchar2) IS
2690
2691 CURSOR c_task_assignment( b_task_assignment_id NUMBER ) IS
2692 SELECT resource_id
2693 FROM jtf_task_assignments -- don't use synonym as that one filters on OWNER records
2694 WHERE task_assignment_id = b_task_assignment_id;
2695
2696 l_task_assignment_id NUMBER;
2697 BEGIN
2698 x_return_status := FND_API.G_RET_STS_SUCCESS;
2699 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
2700
2701 OPEN c_task_assignment(l_task_assignment_id);
2702 FETCH c_task_assignment INTO G_old_resource_id;
2703 CLOSE c_task_assignment;
2704
2705 END;
2706
2707 END;