[Home] [Help]
PACKAGE BODY: APPS.CSP_PARTS_REQUIREMENT
Source
1 PACKAGE BODY CSP_PARTS_REQUIREMENT AS
2 /* $Header: cspvprqb.pls 120.19.12020000.7 2013/03/06 17:28:09 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 g_old_tsk_asgn_sts_id number;
16
17 PROCEDURE Get_source_organization (
18 P_Inventory_Item_Id IN NUMBER,
19 P_Organization_Id IN NUMBER,
20 P_Secondary_Inventory IN VARCHAR2,
21 x_source_org_id OUT NOCOPY NUMBER,
22 x_source_subinv OUT NOCOPY VARCHAR2
23 )
24 IS
25
26 Cursor c_Get_Source_Org(p_org_id Number) Is
27 Select SOURCE_TYPE,
28 SOURCE_ORGANIZATION_ID,
29 SOURCE_SUBINVENTORY
30 From MTL_PARAMETERS
31 Where ORGANIZATION_ID = p_org_id;
32
33 Cursor c_Get_Source_Subinv(p_org_id Number,p_subinv Varchar2) Is
34 Select SOURCE_TYPE,
35 SOURCE_ORGANIZATION_ID,
36 SOURCE_SUBINVENTORY
37 From MTL_SECONDARY_INVENTORIES
38 Where ORGANIZATION_ID = p_org_id
39 And SECONDARY_INVENTORY_NAME = p_subinv;
40
41 Cursor c_Get_Source_Item(p_inventory_item_id Number,p_org_id Number) Is
42 Select SOURCE_TYPE,
43 SOURCE_ORGANIZATION_ID,
44 SOURCE_SUBINVENTORY
45 From MTL_SYSTEM_ITEMS
46 Where INVENTORY_ITEM_ID = p_inventory_item_id
47 And ORGANIZATION_ID = p_org_id;
48
49 Cursor c_Get_Source_Item_Subinv(p_inventory_item_id Number,p_org_id Number,p_subinventory Varchar2) Is
50
51 Select SOURCE_TYPE,
52 SOURCE_ORGANIZATION_ID,
53 SOURCE_SUBINVENTORY
54 From MTL_ITEM_SUB_INVENTORIES
55 Where INVENTORY_ITEM_ID = p_inventory_item_id
56 And ORGANIZATION_ID = p_org_id
57 And SECONDARY_INVENTORY = p_subinventory;
58
59 l_return_status_full VARCHAR2(1);
60 l_Sqlcode NUMBER;
61 l_Sqlerrm Varchar2(2000);
62 l_api_name CONSTANT VARCHAR2(30) := 'get_source_organization';
63
64 l_source_org_rec c_Get_Source_Org%ROWTYPE;
65
66 l_Inventory_Item_Id Number := 0;
67 l_Organization_Id Number := 0;
68 l_Secondary_Inventory Varchar2(10);
69
70 BEGIN
71 --
72 -- API body
73 --
74 l_Inventory_Item_Id := P_Inventory_Item_Id;
75 l_Organization_Id := P_Organization_Id;
76 l_Secondary_Inventory := P_Secondary_Inventory;
77
78 l_source_org_rec := NULL;
79
80 --- Check to see the source for Item And Subinventory
81 If (l_Secondary_Inventory is NOT NULL AND l_Organization_id is NOT NULL) Then
82
83 Open c_Get_Source_Item_Subinv(p_Inventory_Item_Id ,l_Organization_Id,l_Secondary_Inventory);
84 Fetch c_Get_Source_Item_Subinv Into l_Source_Org_Rec;
85 Close c_Get_Source_Item_Subinv;
86 END If;
87
88 -- If source organization is null, Check to see the source in Item definition
89
90 If (l_source_org_rec.source_organization_id IS NULL) Then
91 Open c_Get_Source_Item(p_Inventory_Item_Id,l_Organization_Id);
92 Fetch c_Get_Source_Item Into l_Source_Org_Rec;
93 Close c_Get_Source_Item;
94 END If;
95
96 --- IF source_organiZation_id is still null, Check to see the source in Subinventory definition
97
98 IF (l_source_org_Rec.source_organization_id IS NULL and l_secondary_inventory IS NOT NULL) THEN
99
100 Open c_Get_Source_Subinv(l_Organization_Id,l_Secondary_Inventory);
101 Fetch c_Get_Source_Subinv Into l_Source_Org_Rec;
102 Close c_Get_Source_Subinv;
103 END If;
104
105 --- Check to see the source in Organization definition
106 if (l_Source_org_Rec.source_organization_id IS NULL and l_organization_id IS NOT NULL) THEN
107
108 Open c_Get_Source_Org(l_Organization_Id);
109 Fetch c_Get_Source_Org INTO l_Source_Org_Rec;
110 Close c_Get_Source_Org;
111 End If;
112
113 -- Assign output variables
114 x_source_org_id := l_source_org_Rec.source_organization_id;
115 x_source_subinv := l_source_org_rec.source_subinventory;
116
117 --
118 -- End of API body
119 --
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 l_sqlcode := SQLCODE;
124 l_sqlerrm := SQLERRM;
125 l_source_org_rec.source_organization_id := FND_API.G_MISS_NUM;
126
127 End Get_source_organization;
128
129
130 PROCEDURE process_requirement
131 ( p_api_version IN NUMBER
132 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
133 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
134 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
135 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
136 ,p_create_order_flag IN VARCHAR2
137 ,x_return_status OUT NOCOPY VARCHAR2
138 ,x_msg_count OUT NOCOPY NUMBER
139 ,x_msg_data OUT NOCOPY VARCHAR2
140 ) IS
141 l_api_version_number CONSTANT NUMBER := 1.0;
142 l_api_name CONSTANT VARCHAR2(30) := 'process_requirement';
143 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
144 l_msg_count NUMBER;
145 l_msg_data VARCHAR2(2000);
146 l_msg_dummy VARCHAR2(2000);
147 l_order_msg VARCHAR2(8000);
148 l_commit VARCHAR2(1) := FND_API.G_FALSE;
149 l_user_id NUMBER;
150 l_login_id NUMBER;
151 l_today DATE;
152 EXCP_USER_DEFINED EXCEPTION;
153 l_check_existence NUMBER;
154 l_window BOOLEAN;
155
156 l_requirement_header_id NUMBER ;
157 l_requirement_line_id NUMBER;
158 l_parts_defined VARCHAR2(30);
159
160 l_header_rec Header_Rec_Type;
161 l_line_rec Line_rec_type;
162 l_line_tbl Line_Tbl_type;
163
164 l_rqmt_header_Rec csp_requirement_headers_pvt.requirement_header_rec_type;
165
166 l_rqmt_line_Rec csp_requirement_lines_pvt.requirement_line_rec_type;
167 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
168 x_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
169 l_req_line_details_tbl csp_parts_requirement.Line_detail_Tbl_Type;
170 j NUMBER;
171
172 --Record types and tbl types for finding availability of parts
173 l_parts_list_rec csp_sch_int_pvt.csp_parts_rec_type;
174 l_parts_list_tbl csp_sch_int_pvt.csp_parts_tbl_typ1;
175 l_avail_list_tbl csp_sch_int_pvt.available_parts_tbl_typ1;
176 l_resource_rec csp_sch_int_pvt.csp_sch_resources_rec_typ;
177 l_req_line_Dtl_id NUMBER;
178 l_timezone_id NUMBER;
179
180 CURSOR rs_loc_cur(p_resource_type VARCHAR2, p_resource_id NUMBER) IS
181 SELECT pla.location_id inv_loc_id,
182 hzl.time_zone
183 from csp_rs_cust_relations rcr,
184 hz_cust_acct_sites cas,
185 hz_cust_site_uses csu,
186 po_location_associations pla,
187 hz_party_sites ps,
188 hz_locations hzl
189 where rcr.customer_id = cas.cust_account_id
190 and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
191 and csu.site_use_code = 'SHIP_TO'
192 and csu.site_use_id = pla.site_use_id
193 and cas.party_site_id = ps.party_site_id
194 and ps.location_id = hzl.location_id
195 and csu.primary_flag = 'Y'
196 and rcr.resource_type = p_resource_type
197 and rcr.resource_id = p_resource_id;
198
199 CURSOR task_asgnmt_loc_cur(p_task_Assignment_id NUMBER) IS
200 SELECT pla.location_id inv_loc_id,
201 hzl.time_zone timezone_id
202 from csp_rs_cust_relations rcr,
203 hz_cust_acct_sites cas,
204 hz_cust_site_uses csu,
205 po_location_associations pla,
206 hz_party_sites ps,
207 hz_locations hzl,
208 jtf_task_assignments jta
209 where rcr.customer_id = cas.cust_account_id
210 and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
211 and csu.site_use_code = 'SHIP_TO'
212 and csu.site_use_id = pla.site_use_id
213 and cas.party_site_id = ps.party_site_id
214 and ps.location_id = hzl.location_id
215 and csu.primary_flag = 'Y'
216 and rcr.resource_type = jta.resource_type_code
217 and rcr.resource_id = jta.resource_id
218 and jta.task_assignment_id = p_task_assignment_id;
219
220 BEGIN
221 SAVEPOINT Process_Requirement_PUB;
222
223 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
224 -- initialize message list
225 FND_MSG_PUB.initialize;
226 END IF;
227
228 -- Standard call to check for call compatibility.
229 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
230 p_api_version,
231 l_api_name,
232 G_PKG_NAME)
233 THEN
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235 END IF;
236
237 -- initialize return status
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239
240 l_header_Rec := px_header_rec;
241 l_line_tbl := px_line_table;
242
243 -- user and login information
244 SELECT Sysdate INTO l_today FROM dual;
245 l_user_id := fnd_global.user_id;
246 l_login_id := fnd_global.login_id;
247
248 -- call availability API to get source organization
249 l_resource_rec.resource_type := l_header_Rec.resource_type;
250 l_resource_Rec.resource_id := l_header_Rec.resource_id;
251
252 FOR I IN 1..l_line_tbl.COUNT LOOP
253 IF l_line_tbl(I).source_organization_id IS NULL THEN
254 l_parts_list_rec.item_id := l_line_tbl(I).inventory_item_id;
255 l_parts_list_rec.revision := l_line_Tbl(I).revision;
256 l_parts_list_rec.item_uom := l_line_tbl(I).unit_of_measure;
257 l_parts_list_rec.quantity := l_line_tbl(I).ordered_quantity;
258 l_parts_list_rec.ship_set_name := l_line_tbl(I).ship_complete;
259 l_parts_list_rec.line_id := l_line_tbl(I).line_num;
260 l_parts_list_tbl(i) := l_parts_list_rec;
261 END IF;
262 END LOOP;
263 IF l_parts_list_tbl.count > 0 THEN
264
265 IF sysdate < l_header_rec.need_by_date THEN
266 -- call csp_sch_int_pvt.check_parts_availability()
267 csp_sch_int_pvt.check_parts_availability(
268 p_resource => l_resource_rec,
269 p_organization_id => l_header_rec.dest_organization_id,
270 p_subinv_code => null,
271 p_need_by_date => l_header_rec.need_by_date,
272 p_parts_list => l_parts_list_tbl,
273 p_timezone_id => null,
274 x_availability => l_avail_list_tbl,
275 x_return_status => l_return_status,
276 x_msg_data => l_msg_data,
277 x_msg_count => l_msg_count,
278 p_called_from => 'MOBILE'
279 );
280 ELSE
281 FND_MESSAGE.Set_Name('CSP', 'CSP_NEED_PASSED');
282 l_order_msg := FND_MESSAGE.GET;
283 END IF;
284 END IF;
285 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
286 null; --RAISE FND_API.G_EXC_ERROR;
287 END IF;
288 if l_msg_count > 0 THEN
289 for j in 1..fnd_msg_pub.count_msg loop
290 fnd_msg_pub.get(
291 j,
292 FND_API.G_FALSE,
293 l_msg_data,
294 l_msg_dummy);
295 l_order_msg := l_order_msg || l_msg_data;
296 end loop;
297 END IF;
298 J := 1;
299 IF (l_Avail_list_tbl.COUNT > 0) THEN
300 FOR I IN 1..l_line_tbl.COUNT LOOP
301 IF (l_avail_list_tbl(J).item_id = l_line_tbl(I).inventory_item_id) THEN
302 IF (l_line_tbl(I).source_organization_id IS NULL) THEN
303 l_line_tbl(I).source_organization_id := l_avail_list_tbl(J).source_org_id;
304 l_line_tbl(I).shipping_method_code := l_avail_list_tbl(J).shipping_methode;
305 l_line_Tbl(I).order_by_date := l_Avail_list_tbl(J).order_by_date;
306 END IF;
307 J := J+1;
308 END IF;
309 END LOOP;
310 END IF;
311
312 -- check to see if any of the lines have null source org and if yes, get source organization defined
313 -- for replenishment at item/subinventory/organization level
314 -- not required since this will be part of the check_availability call
315 /*
316 FOR I IN 1..l_line_tbl.COUNT LOOP
317 IF (l_line_Tbl(I).source_organization_id IS NULL) THEN
318 Get_source_organization(P_Inventory_Item_Id => l_line_Tbl(I).inventory_item_id,
319 P_Organization_Id => l_header_Rec.dest_organization_id,
320 P_Secondary_Inventory => l_header_rec.dest_subinventory,
321 x_source_org_id => l_line_Tbl(I).source_organization_id);
322 IF ((l_line_Tbl(I).source_organization_id IS NULL) OR
323 (l_line_Tbl(I).source_organization_id = FND_API.G_MISS_NUM)) THEN
324 -- no source organization defined, create requirement with error status
325 l_rqmt_header_rec.open_requirement := 'E';
326 END IF;
327 END If;
328 END LOOP;
329 */
330
331 -- find default ship to of resource if ship_to_location_id is null
332 IF (l_header_Rec.ship_to_location_id IS NULL) THEN
333 -- check if task_assignment_id is passed
334 IF ((l_header_Rec.task_assignment_id IS NOT NULL) AND
335 (l_header_Rec.resource_type IS NULL OR l_header_rec.resource_id IS NULL)) THEN
336 -- get resource and default addr of resource based on task assignment
337 OPEN task_asgnmt_loc_cur(l_header_rec.task_assignment_id);
338 FETCH task_asgnmt_loc_cur INTO l_header_rec.ship_to_location_id, l_timezone_id;
339 CLOSE task_asgnmt_loc_cur;
340 ELSIF (l_header_Rec.resource_type IS NOT NULL AND l_header_rec.resource_id IS NOT NULL) THEN
341 -- get default ship-to location of resource
342 OPEN rs_loc_cur(l_header_rec.resource_type, l_header_Rec.resource_id);
343 FETCH rs_loc_cur into l_header_Rec.ship_to_location_id, l_timezone_id;
344 CLOSE rs_loc_cur;
345 ELSE
346 -- raise error, either ship to location or resource must be specified
347 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
348 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'resource or task assignment or ship to location', FALSE);
349 FND_MSG_PUB.ADD;
350 RAISE EXCP_USER_DEFINED;
351 END If;
352 ELSE
353 BEGIN
354 SELECT hzl.time_zone time_zone_id
355 INTO l_timezone_id
356 from hz_cust_acct_sites cas,
357 hz_cust_site_uses csu,
358 po_location_associations pla,
359 hz_party_sites ps,
360 hz_locations hzl
361 where cas.cust_acct_site_id = csu.cust_acct_site_id (+)
362 and csu.site_use_code = 'SHIP_TO'
363 and csu.site_use_id = pla.site_use_id
364 and pla.location_id = l_header_rec.ship_to_location_id
365 and cas.party_site_id = ps.party_site_id
366 and ps.location_id = hzl.location_id;
367 EXCEPTION
368 when no_data_found then
369 null;
370 when others then
371 null;
372 END;
373 END IF;
374
375 IF (p_create_order_flag = 'Y' AND l_rqmt_header_Rec.open_requirement <> 'E') THEN
376
377 -- call csp_process_order API
378 csp_parts_order.process_order(
379 p_api_version => l_api_version_number
380 ,p_Init_Msg_List => p_init_msg_list
381 ,p_commit => FND_API.G_false
382 ,px_header_rec => l_header_rec
383 ,px_line_table => l_line_tbl
384 ,x_return_status => l_return_status
385 ,x_msg_count => l_msg_count
386 ,x_msg_data => l_msg_data
387 );
388
389 -- set parts_defined to 'Y' if order has been created for this requirement
390 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
391 l_parts_defined := 'Y';
392 l_rqmt_header_rec.open_requirement := 'Y';
393 px_header_rec.order_header_id := l_header_Rec.order_header_id;
394 l_order_msg := null;
395 ELSE
396 l_rqmt_header_rec.open_requirement := 'E';
397 for j in 1..fnd_msg_pub.count_msg loop
398 fnd_msg_pub.get(
399 j,
400 FND_API.G_FALSE,
401 l_msg_data,
402 l_msg_dummy);
403 l_order_msg := l_order_msg || l_msg_data;
404 end loop;
405 END IF;
406 END IF;
407
408 -- SETTING UP THE PARTS REQUIREMENT HEADER RECORD
409 IF (l_header_rec.operation = G_OPR_CREATE) THEN
410 l_rqmt_header_Rec.created_by := nvl(l_user_id, -1);
411 l_rqmt_header_Rec.creation_date := l_today;
412 ELSIF (l_header_rec.operation IN (G_OPR_UPDATE, G_OPR_DELETE)) THEN
413 IF nvl(l_header_rec.requirement_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
414 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
415 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_header_rec.requirement_header_id', FALSE);
416 FND_MSG_PUB.ADD;
417 RAISE EXCP_USER_DEFINED;
418 ELSE
419 BEGIN
420 select requirement_header_id
421 into l_check_existence
422 from csp_requirement_headers
423 where requirement_header_id = l_header_rec.requirement_header_id;
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
427 fnd_message.set_token('HEADER_ID', to_char(l_header_rec.requirement_header_id), FALSE);
428
429 FND_MSG_PUB.ADD;
430 RAISE EXCP_USER_DEFINED;
431 WHEN OTHERS THEN
432 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
433 fnd_message.set_token('ERR_FIELD', 'l_header_rec.requirement_header_id', FALSE);
434 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
435 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_HEADERS', FALSE);
436 FND_MSG_PUB.ADD;
437 RAISE EXCP_USER_DEFINED;
438 END;
439 END IF;
440 END IF;
441
442 l_rqmt_header_Rec.requirement_header_id := nvl(l_header_rec.requirement_header_id, FND_API.G_MISS_NUM); --l_requirement_header_id;
443 l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
444 l_rqmt_header_Rec.last_update_date := l_today;
445 l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
446 l_rqmt_header_Rec.ship_to_location_id := nvl(l_header_rec.ship_to_location_id, FND_API.G_MISS_NUM);
447 l_rqmt_header_Rec.timezone_id := nvl(l_timezone_id, FND_API.G_MISS_NUM);
448 l_rqmt_header_Rec.address_type := l_header_rec.address_type;-- csp_parts_Requirement.G_ADDR_RESOURCE;
449 l_rqmt_header_Rec.task_id := nvl(l_header_rec.task_id, FND_API.G_MISS_NUM);
450 l_rqmt_header_Rec.task_assignment_id := nvl(l_header_rec.task_assignment_id, FND_API.G_MISS_NUM);
451 l_rqmt_header_Rec.shipping_method_code := nvl(l_header_rec.shipping_method_code, FND_API.G_MISS_CHAR);
452 l_rqmt_header_Rec.need_by_date := nvl(l_header_rec.need_by_date, FND_API.G_MISS_DATE);
453 l_rqmt_header_Rec.destination_organization_id := nvl(l_header_rec.dest_organization_id, FND_API.G_MISS_NUM);
454 l_rqmt_header_Rec.destination_subinventory := nvl(l_header_rec.dest_subinventory, FND_API.G_MISS_CHAR);
455 l_rqmt_header_Rec.parts_defined := nvl(l_parts_defined, FND_API.G_MISS_CHAR);
456 -- l_rqmt_header_Rec.open_requirement := 'Y';
457 l_rqmt_header_rec.order_type_id := nvl(l_header_rec.order_type_id, FND_API.G_MISS_NUM);
458 l_rqmt_header_rec.attribute_Category := nvl(l_header_rec.attribute_category, FND_API.G_MISS_CHAR);
459 l_rqmt_header_rec.attribute1 := nvl(l_header_rec.attribute1, FND_API.G_MISS_CHAR);
460 l_rqmt_header_rec.attribute2 := nvl(l_header_rec.attribute2, FND_API.G_MISS_CHAR);
461 l_rqmt_header_rec.attribute3 := nvl(l_header_rec.attribute3, FND_API.G_MISS_CHAR);
462 l_rqmt_header_rec.attribute4 := nvl(l_header_rec.attribute4, FND_API.G_MISS_CHAR);
463 l_rqmt_header_rec.attribute5 := nvl(l_header_rec.attribute5, FND_API.G_MISS_CHAR);
464 l_rqmt_header_rec.attribute6 := nvl(l_header_rec.attribute6, FND_API.G_MISS_CHAR);
465 l_rqmt_header_rec.attribute7 := nvl(l_header_rec.attribute7, FND_API.G_MISS_CHAR);
466 l_rqmt_header_rec.attribute8 := nvl(l_header_rec.attribute8, FND_API.G_MISS_CHAR);
467 l_rqmt_header_rec.attribute9 := nvl(l_header_rec.attribute9, FND_API.G_MISS_CHAR);
468 l_rqmt_header_rec.attribute10 := nvl(l_header_rec.attribute10, FND_API.G_MISS_CHAR);
469 l_rqmt_header_rec.attribute11 := nvl(l_header_rec.attribute11, FND_API.G_MISS_CHAR);
470 l_rqmt_header_rec.attribute12 := nvl(l_header_rec.attribute12, FND_API.G_MISS_CHAR);
471 l_rqmt_header_rec.attribute13 := nvl(l_header_rec.attribute13, FND_API.G_MISS_CHAR);
472 l_rqmt_header_rec.attribute14 := nvl(l_header_rec.attribute14, FND_API.G_MISS_CHAR);
473 l_rqmt_header_rec.attribute15 := nvl(l_header_rec.attribute15, FND_API.G_MISS_CHAR);
474 l_rqmt_header_rec.ship_to_contact_id := nvl(l_header_rec.ship_to_contact_id, FND_API.G_MISS_NUM);
475
476 if (l_header_Rec.task_assignment_id IS NULL)
477 and (l_header_Rec.resource_id is null
478 or l_header_rec.resource_type is null) then
479 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
480 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_header_Rec.resource_id', FALSE);
481 FND_MSG_PUB.ADD;
482 RAISE EXCP_USER_DEFINED;
483 end if;
484
485 --IF (l_header_Rec.task_assignment_id IS NULL) THEN
486 l_Rqmt_header_rec.resource_type := l_header_rec.resource_type;
487 l_Rqmt_header_rec.resource_id := l_header_Rec.resource_id;
488 --END IF;
489
490 -- SETTING UP THE PARTS REQUIREMENT LINE RECORD
491 FOR I IN 1..l_line_tbl.COUNT LOOP
492 l_line_rec := l_line_tbl(I);
493
494 IF (l_header_rec.operation = G_OPR_CREATE) THEN
495 l_rqmt_line_Rec.created_by := nvl(l_user_id, -1);
496 l_rqmt_line_Rec.creation_date := l_today;
497 ELSIF (l_header_rec.operation IN (G_OPR_UPDATE, G_OPR_DELETE)) THEN
498 IF nvl(l_line_rec.requirement_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
499
500 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
501 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requirement_line_id', FALSE);
502 FND_MSG_PUB.ADD;
503 RAISE EXCP_USER_DEFINED;
504 ELSE
505 BEGIN
506 select requirement_line_id
507 into l_check_existence
508 from csp_requirement_lines
509 where requirement_line_id = l_line_rec.requirement_line_id;
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN
512 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_LINE');
513 fnd_message.set_token ('LINE_ID', to_char(l_line_rec.requirement_line_id), FALSE);
514
515 FND_MSG_PUB.ADD;
516 RAISE EXCP_USER_DEFINED;
517 WHEN OTHERS THEN
518 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
519 fnd_message.set_token('ERR_FIELD', 'l_line_rec.requirement_line_id', FALSE);
520 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
521 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_LINES', FALSE);
522 FND_MSG_PUB.ADD;
523 RAISE EXCP_USER_DEFINED;
524 END;
525 END IF;
526 END IF;
527
528 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
529 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
530 l_rqmt_line_rec.last_update_date := l_today;
531 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
532 --l_rqmt_line_rec.requirement_header_id := nvl(l_header_rec.requirement_header_id, FND_API.G_MISS_NUM);
533
534 l_rqmt_line_rec.inventory_item_id := l_line_rec.inventory_item_id;
535 l_rqmt_line_rec.uom_code := l_line_rec.unit_of_measure;
536 l_rqmt_line_rec.required_quantity := l_line_rec.quantity;
537 l_rqmt_line_rec.ship_complete_flag := l_line_rec.ship_complete;
538 l_rqmt_line_rec.likelihood := l_line_rec.likelihood;
539 l_rqmt_line_rec.revision := l_line_rec.revision;
540 /*l_rqmt_line_rec.source_organization_id := l_line_rec.source_organization_id;
541
542 l_rqmt_line_rec.source_subinventory := l_line_rec.source_subinventory;
543 l_rqmt_line_rec.ordered_quantity := l_line_Rec.ordered_quantity;
544
545 l_rqmt_line_rec.reservation_id := l_line_rec.reservation_id;
546 l_rqmt_line_rec.order_by_date := l_line_rec.order_by_date;*/
547
548 l_rqmt_line_rec.attribute_Category := nvl(l_line_rec.attribute_category, FND_API.G_MISS_CHAR);
549 l_rqmt_line_rec.attribute1 := nvl(l_line_rec.attribute1, FND_API.G_MISS_CHAR);
550 l_rqmt_line_rec.attribute2 := nvl(l_line_rec.attribute2, FND_API.G_MISS_CHAR);
551 l_rqmt_line_rec.attribute3 := nvl(l_line_rec.attribute3, FND_API.G_MISS_CHAR);
552 l_rqmt_line_rec.attribute4 := nvl(l_line_rec.attribute4, FND_API.G_MISS_CHAR);
553 l_rqmt_line_rec.attribute5 := nvl(l_line_rec.attribute5, FND_API.G_MISS_CHAR);
554 l_rqmt_line_rec.attribute6 := nvl(l_line_rec.attribute6, FND_API.G_MISS_CHAR);
555 l_rqmt_line_rec.attribute7 := nvl(l_line_rec.attribute7, FND_API.G_MISS_CHAR);
556 l_rqmt_line_rec.attribute8 := nvl(l_line_rec.attribute8, FND_API.G_MISS_CHAR);
557 l_rqmt_line_rec.attribute9 := nvl(l_line_rec.attribute9, FND_API.G_MISS_CHAR);
558 l_rqmt_line_rec.attribute10 := nvl(l_line_rec.attribute10, FND_API.G_MISS_CHAR);
559 l_rqmt_line_rec.attribute11 := nvl(l_line_rec.attribute11, FND_API.G_MISS_CHAR);
560 l_rqmt_line_rec.attribute12 := nvl(l_line_rec.attribute12, FND_API.G_MISS_CHAR);
561 l_rqmt_line_rec.attribute13 := nvl(l_line_rec.attribute13, FND_API.G_MISS_CHAR);
562 l_rqmt_line_rec.attribute14 := nvl(l_line_rec.attribute14, FND_API.G_MISS_CHAR);
563 l_rqmt_line_rec.attribute15 := nvl(l_line_rec.attribute15, FND_API.G_MISS_CHAR);
564
565 l_rqmt_line_rec.order_line_id := l_line_Rec.order_line_id;
566 px_line_table(I).order_line_id := l_line_Rec.order_line_id;
567 l_rqmt_line_Tbl(I) := l_rqmt_line_rec;
568
569 END LOOP;
570
571 IF ( l_header_rec.operation = 'CREATE') THEN
572
573 -- check to see if requirements exist for a given task
574 IF (l_header_rec.task_id IS NOT NULL AND
575 l_header_rec.requirement_header_id IS NULL) THEN
576 BEGIN
577 SELECT requirement_header_id
578 INTO l_requirement_header_id
579 FROM csp_requirement_headers
580 WHERE task_id = l_header_rec.task_id;
581 EXCEPTION
582 WHEN NO_DATA_FOUND THEN
583 -- call private api for inserting into csp_Requirement_headers
584 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
585 P_Api_Version_Number => l_api_Version_number,
586 P_Init_Msg_List => p_init_msg_list,
587 P_Commit => FND_API.G_FALSE,
588 p_validation_level => null,
589 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
590 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
591 X_Return_Status => l_Return_status,
592 X_Msg_Count => l_msg_count,
593 X_Msg_Data => l_msg_data
594 );
595 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
596 RAISE FND_API.G_EXC_ERROR;
597 ELSE
598 IF l_order_msg IS NOT null THEN
599 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_CREATED');
600 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
601 x_msg_data := FND_MESSAGE.GET;
602 END IF;
603 END IF;
604 WHEN OTHERS THEN
605 NULL;
606 END;
607 ELSIF (l_header_rec.requirement_header_id IS NOT NULL) THEN
608 BEGIN
609 SELECT requirement_header_id
610 INTO l_requirement_header_id
611 FROM csp_requirement_headers
612 WHERE requirement_header_id = l_header_rec.requirement_header_id;
613 EXCEPTION
614 WHEN NO_DATA_FOUND THEN
615 -- call private api for inserting into csp_Requirement_headers
616 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
617 P_Api_Version_Number => l_api_Version_number,
618 P_Init_Msg_List => p_init_msg_list,
619 P_Commit => FND_API.G_FALSE,
620 p_validation_level => null,
621 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
622 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
623 X_Return_Status => l_Return_status,
624 X_Msg_Count => l_msg_count,
625 X_Msg_Data => l_msg_data
626 );
627 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
628 RAISE FND_API.G_EXC_ERROR;
629 ELSE
630 IF l_order_msg IS NOT null THEN
631 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_CREATED');
632 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
633 x_msg_data := FND_MESSAGE.GET;
634 END IF;
635 END IF;
636 END;
637 ELSE
638 -- call private api for inserting into csp_Requirement_headers
639 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
640 P_Api_Version_Number => l_api_Version_number,
641 P_Init_Msg_List => p_init_msg_list,
642 P_Commit => FND_API.G_FALSE,
643 p_validation_level => null,
644 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
645 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
646 X_Return_Status => l_Return_status,
647 X_Msg_Count => l_msg_count,
648 X_Msg_Data => l_msg_data
649 );
650 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
651 RAISE FND_API.G_EXC_ERROR;
652 ELSE
653 IF l_order_msg IS NOT null THEN
654 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_CREATED');
655 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
656 x_msg_data := FND_MESSAGE.GET;
657 END IF;
658 END IF;
659 END IF;
660
661 -- Update return header rec with requirement_header_id
662 px_header_rec.requirement_header_id := l_requirement_header_id;
663
664 -- Fill the requirement_header_id in the line record
665 FOR I in 1..l_rqmt_line_Tbl.COUNT LOOP
666 l_rqmt_line_Tbl(I).requirement_header_id := l_requirement_header_id;
667 END LOOP;
668 x_rqmt_line_tbl := l_rqmt_line_tbl;
669 -- call private api for inserting into csp_requirement_lines
670 CSP_Requirement_Lines_PVT.Create_requirement_lines(
671 P_Api_Version_Number => l_api_version_number,
672 P_Init_Msg_List => p_Init_Msg_List,
673 P_Commit => FND_API.G_FALSE,
674 p_validation_level => null,
675 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
676 x_Requirement_Line_tbl => x_rqmt_line_tbl,
677 X_Return_Status => l_return_status,
678 X_Msg_Count => l_msg_count,
679 X_Msg_Data => l_msg_data
680 );
681
682 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
683 RAISE FND_API.G_EXC_ERROR;
684 ELSE
685 IF x_msg_data IS null and l_order_msg is not null THEN
686 FND_MESSAGE.Set_Name('CSP', 'CSP_REQ_UPDATED');
687 FND_MESSAGE.Set_Token('REQ_NUMBER',to_char(l_requirement_header_id));
688 x_msg_data := FND_MESSAGE.GET;
689 END IF;
690 END IF;
691 FOR I IN 1..x_rqmt_line_tbl.count LOOP
692 IF x_rqmt_line_tbl(I).order_line_id IS NOT NULL THEN
693 SELECT csp_req_line_Details_s1.nextval
694 INTO l_req_line_Dtl_id
695 FROM dual;
696
697 csp_req_line_Details_pkg.Insert_Row(
698 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
699 p_REQUIREMENT_LINE_ID => x_rqmt_line_tbl(I).requirement_line_id,
700 p_CREATED_BY => nvl(l_user_id, 1),
701 p_CREATION_DATE => sysdate,
702 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
703 p_LAST_UPDATE_DATE => sysdate,
704 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
705 p_SOURCE_TYPE => 'IO',
706 p_SOURCE_ID => x_rqmt_line_tbl(I).order_line_id);
707 END IF;
708 END LOOP;
709
710 ELSIF (l_header_rec.operation = 'UPDATE') THEN
711 -- call private api for updating requirement headers
712 CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
713 P_Api_Version_Number => l_api_Version_number,
714 P_Init_Msg_List => p_init_msg_list,
715 P_Commit => FND_API.G_FALSE,
716 p_validation_level => null,
717 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
718 X_Return_Status => l_Return_status,
719 X_Msg_Count => l_msg_count,
720 X_Msg_Data => l_msg_data
721 );
722
723 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
724 RAISE FND_API.G_EXC_ERROR;
725 END IF;
726
727 -- call private api for updating requirement lines
728 --FOR I in 1..l_rqmt_line_tbl.COUNT LOOP
729 CSP_Requirement_Lines_PVT.Update_requirement_lines(
730 P_Api_Version_Number => l_api_version_number,
731 P_Init_Msg_List => p_Init_Msg_List,
732 P_Commit => FND_API.G_FALSE,
733 p_validation_level => null,
734 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
735 X_Return_Status => l_return_status,
736 X_Msg_Count => l_msg_count,
737 X_Msg_Data => l_msg_data
738 );
739
740 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
741 RAISE FND_API.G_EXC_ERROR;
742 END IF;
743
744 --END LOOP;
745 If p_commit = FND_API.G_TRUE then
746 commit work;
747 end if;
748 END IF;
749 IF x_msg_data is not null or l_order_msg is not null THEN
750 x_msg_data := x_msg_data || ' ' || l_order_msg;
751 END IF;
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
755 P_API_NAME => L_API_NAME
756 ,P_PKG_NAME => G_PKG_NAME
757 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
758 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
759 ,X_MSG_COUNT => X_MSG_COUNT
760 ,X_MSG_DATA => X_MSG_DATA
761 ,X_RETURN_STATUS => X_RETURN_STATUS);
762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
763 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
764 P_API_NAME => L_API_NAME
765 ,P_PKG_NAME => G_PKG_NAME
766 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
767 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
768 ,X_MSG_COUNT => X_MSG_COUNT
769 ,X_MSG_DATA => X_MSG_DATA
770 ,X_RETURN_STATUS => X_RETURN_STATUS);
771 WHEN OTHERS THEN
772 Rollback to process_requirement_pub;
773 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
774 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
775 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
776 FND_MSG_PUB.ADD;
777 fnd_msg_pub.count_and_get
778 ( p_count => x_msg_count
779 , p_data => x_msg_data);
780 x_return_status := FND_API.G_RET_STS_ERROR;
781
782 END;
783
784
785
786 PROCEDURE csptrreq_fm_order(
787 p_api_version IN NUMBER
788 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
789 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
790 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
791 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
792 ,x_return_status OUT NOCOPY VARCHAR2
793 ,x_msg_count OUT NOCOPY NUMBER
794 ,x_msg_data OUT NOCOPY VARCHAR2
795 ) IS
796 l_api_version_number CONSTANT NUMBER := 1.0;
797 l_api_name CONSTANT VARCHAR2(30) := 'csptrreq_fm_order';
798 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
799 l_msg_count NUMBER;
800 l_msg_data VARCHAR2(4000);
801 l_commit VARCHAR2(1) := FND_API.G_FALSE;
802 l_user_id NUMBER;
803 l_login_id NUMBER;
804 l_today DATE;
805 EXCP_USER_DEFINED EXCEPTION;
806 l_check_existence NUMBER;
807
808 l_requirement_header_id NUMBER ;
809 l_requirement_line_id NUMBER;
810
811 l_header_rec Header_Rec_Type;
812 l_line_rec Line_rec_type;
813 l_line_tbl Line_Tbl_type;
814 l_old_line_tbl Line_Tbl_type;
815 l_po_line_tbl Line_Tbl_type;
816 l_po_line_Rec Line_Rec_Type;
817
818 J NUMBER;
819 K NUMBER;
820 l_qty_to_reserve NUMBER := 0;
821 l_resource_att NUMBER := 0;
822 l_resource_onhand NUMBER := 0;
823 l_resource_org_id NUMBER;
824 l_resource_subinv VARCHAR2(30);
825 l_reservation_rec CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
826 l_local_reservation_id NUMBER;
827
828 l_rqmt_line_Rec csp_requirement_lines_pvt.requirement_line_rec_type;
829 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
830
831 CURSOR rs_info_cur(p_rqmt_header_id NUMBER) IS
832 SELECT cla.organization_id,
833 cla.subinventory_code
834 FROM csp_requirement_headers crh,
835 jtf_task_assignments jta,
836 csp_INV_LOC_ASSIGNMENTS cla
837 WHERE cla.default_code = 'IN'
838 AND cla.resource_type = decode(crh.task_assignment_id,null,crh.resource_type,jta.resource_type_code)
839 AND cla.resource_id = decode(crh.task_assignment_id,null,crh.resource_id,jta.resource_id)
840 and jta.task_assignment_id(+) = crh.task_assignment_id
841 AND crh.requirement_header_id = p_rqmt_header_id;
842
843 BEGIN
844
845 SAVEPOINT csptrreq_fm_order_PUB;
846
847 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
848 -- initialize message list
849 FND_MSG_PUB.initialize;
850 END IF;
851
852 -- Standard call to check for call compatibility.
853 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
854 p_api_version,
855 l_api_name,
856 G_PKG_NAME)
857 THEN
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 END IF;
860
861 -- initialize return status
862 x_return_status := FND_API.G_RET_STS_SUCCESS;
863
864 l_header_Rec := px_header_rec;
865 l_old_line_tbl := px_line_table;
866
867 -- user and login information
868 SELECT Sysdate INTO l_today FROM dual;
869 l_user_id := fnd_global.user_id;
870 l_login_id := fnd_global.login_id;
871
872 -- create reservations and create line table which qualify for creating order
873
874 OPEN rs_info_cur(l_header_rec.requirement_header_id);
875 FETCH rs_info_cur INTO l_resource_org_id, l_resource_subinv;
876 CLOSE rs_info_cur;
877
878 J := 1;
879 K := 1;
880 FOR I IN 1..l_old_line_Tbl.COUNT LOOP
881 l_line_rec := l_old_line_Tbl(I);
882
883 l_qty_to_Reserve := l_line_Rec.quantity - l_line_rec.ordered_quantity;
884
885 IF (l_resource_org_id IS NOT NULL AND l_resource_subinv IS NOT NULL) THEN
886 csp_sch_int_pvt.check_local_inventory(p_org_id => l_resource_org_id,
887 p_subinv_code => l_resource_subinv,
888 p_item_id => l_line_rec.inventory_item_id,
889 p_revision => l_line_Rec.revision,
890 x_att => l_resource_att,
891 x_onhand => l_resource_onhand,
892 x_return_status => l_return_status,
893 x_msg_count => l_msg_count,
894 x_msg_Data => l_msg_data
895 );
896 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
897 RAISE FND_API.G_EXC_ERROR;
898 END If;
899 END IF;
900
901 IF (l_qty_to_Reserve < l_resource_att and l_qty_to_reserve > 0) THEN
902 -- create reservations on qty_to_Reserve in engineers subinv
903 l_reservation_rec.need_by_date := sysdate; --l_header_rec.need_by_date;
904 l_reservation_rec.organization_id := l_resource_org_id;
905 l_reservation_rec.item_id := l_line_Rec.inventory_item_id;
906 l_Reservation_rec.revision := l_line_rec.revision;
907 l_reservation_rec.item_uom_code := l_line_rec.unit_of_measure;
908 l_reservation_rec.quantity_needed := l_qty_to_reserve;
909 l_reservation_rec.sub_inventory_code := l_resource_subinv;
910
911 l_local_reservation_id := csp_sch_int_pvt.create_reservation(
912 p_reservation_parts => l_reservation_rec,
913 x_return_status => l_return_status,
914 --x_msg_count => l_msg_Count,
915 x_msg_data => l_msg_data
916
917 );
918 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
919 RAISE FND_API.G_EXC_ERROR;
920 END If;
921
922 update csp_Requirement_lines
923 set local_Reservation_id = l_local_Reservation_id
924 where requirement_line_id = l_line_Rec.requirement_line_id;
925
926 END IF;
927
928 IF (l_line_rec.sourced_from = 'INVENTORY' AND (l_line_rec.ordered_quantity > 0)) THEN
929 l_line_tbl(J) := l_line_rec;
930 J := J + 1;
931 ELSIF (l_line_rec.sourced_from = 'VENDOR' AND (l_line_rec.ordered_quantity > 0)) THEN
932 l_po_line_tbl(K) := l_line_Rec;
933 K := K+1;
934 END IF;
935
936 END LOOP;
937
938 -- call csp_process_order API only if atleast one line qualifies for order to be created
939
940 IF (l_line_tbl.count > 0) THEN
941 csp_parts_order.process_order(
942 p_api_version => l_api_version_number
943 ,p_Init_Msg_List => p_init_msg_list
944 ,p_commit => p_commit
945 ,px_header_rec => l_header_rec
946 ,px_line_table => l_line_tbl
947 ,x_return_status => l_return_status
948 ,x_msg_count => l_msg_count
949 ,x_msg_data => l_msg_data
950 );
951
952 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
953 RAISE FND_API.G_EXC_ERROR;
954 ELSE
955 -- update csp_requirement_line table with order information
956 FOR I IN 1..l_line_tbl.COUNT LOOP
957 l_line_rec := l_line_tbl(I);
958
959 IF nvl(l_line_rec.requirement_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
960
961 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
962 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requirement_line_id', FALSE);
963
964 FND_MSG_PUB.ADD;
965 RAISE EXCP_USER_DEFINED;
966 ELSE
967 BEGIN
968 select requirement_line_id
969 into l_check_existence
970 from csp_requirement_lines
971 where requirement_line_id = l_line_rec.requirement_line_id;
972 EXCEPTION
973 WHEN NO_DATA_FOUND THEN
974 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_LINE');
975 fnd_message.set_token ('LINE_ID', to_char(l_line_rec.requirement_line_id), FALSE);
976 FND_MSG_PUB.ADD;
977 RAISE EXCP_USER_DEFINED;
978 WHEN OTHERS THEN
979 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
980 fnd_message.set_token('ERR_FIELD', 'l_line_rec.requirement_line_id', FALSE);
981 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
982 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_LINES', FALSE);
983 FND_MSG_PUB.ADD;
984 RAISE EXCP_USER_DEFINED;
985 END;
986 END IF;
987
988 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
989 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
990 l_rqmt_line_rec.last_update_date := l_today;
991 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
992 l_rqmt_line_rec.ordered_quantity := l_line_Rec.ordered_quantity;
993 l_rqmt_line_rec.order_line_id := l_line_Rec.order_line_id;
994 l_rqmt_line_rec.sourced_from := 'INVENTORY';
995 --l_rqmt_line_rec.reservation_id := l_line_rec.reservation_id;
996 --l_rqmt_line_rec.order_by_date := l_line_rec.order_by_date;
997
998 l_rqmt_line_Tbl(I) := l_rqmt_line_rec;
999
1000 END LOOP;
1001 END IF;
1002 END IF;
1003
1004 -- create purchase requisitions if po_line_tbl has atleast one record
1005 IF (l_po_line_tbl.count > 0) THEN
1006 csp_parts_order.process_purchase_req(
1007 p_api_version => l_api_version_number
1008 ,p_init_msg_list => p_init_msg_list
1009 ,p_commit => p_commit
1010 ,px_header_rec => l_header_Rec
1011 ,px_line_Table => l_po_line_tbl
1012 ,x_return_status => l_return_status
1013 ,x_msg_count => l_msg_count
1014 ,x_msg_data => l_msg_data
1015 );
1016
1017 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1018 RAISE FND_API.G_EXC_ERROR;
1019 ELSE
1020 -- now update csp_requirement_line table with purchase req information
1021 J := l_line_tbl.count + 1;
1022 FOR I IN 1..l_po_line_tbl.COUNT LOOP
1023 l_line_rec := l_po_line_tbl(I);
1024
1025 IF nvl(l_line_rec.requirement_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1026
1027 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1028 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requirement_line_id', FALSE);
1029
1030 FND_MSG_PUB.ADD;
1031 RAISE EXCP_USER_DEFINED;
1032 ELSE
1033 BEGIN
1034 select requirement_line_id
1035 into l_check_existence
1036 from csp_requirement_lines
1037 where requirement_line_id = l_line_rec.requirement_line_id;
1038 EXCEPTION
1039 WHEN NO_DATA_FOUND THEN
1040 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_LINE');
1041 fnd_message.set_token ('LINE_ID', to_char(l_line_rec.requirement_line_id), FALSE);
1042 FND_MSG_PUB.ADD;
1043 RAISE EXCP_USER_DEFINED;
1044 WHEN OTHERS THEN
1045 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1046 fnd_message.set_token('ERR_FIELD', 'l_line_rec.requirement_line_id', FALSE);
1047 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
1048 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_LINES', FALSE);
1049 FND_MSG_PUB.ADD;
1050 RAISE EXCP_USER_DEFINED;
1051 END;
1052 END IF;
1053
1054 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
1055 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
1056 l_rqmt_line_rec.last_update_date := l_today;
1057 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
1058 l_rqmt_line_rec.ordered_quantity := l_line_Rec.ordered_quantity;
1059 l_rqmt_line_rec.order_line_id := l_line_Rec.requisition_line_id;
1060 l_rqmt_line_Rec.sourced_from := 'VENDOR';
1061 --l_rqmt_line_rec.reservation_id := l_line_rec.reservation_id;
1062 --l_rqmt_line_rec.order_by_date := l_line_rec.order_by_date;
1063
1064 l_rqmt_line_Tbl(J) := l_rqmt_line_rec;
1065
1066 END LOOP;
1067 END IF;
1068 END IF;
1069
1070 IF (l_rqmt_line_Tbl.count > 0) THEN
1071
1072 CSP_Requirement_Lines_PVT.Update_requirement_lines(
1073 P_Api_Version_Number => l_api_version_number,
1074 P_Init_Msg_List => p_Init_Msg_List,
1075 P_Commit => p_commit,
1076 p_validation_level => null,
1077 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
1078 X_Return_Status => l_return_status,
1079 X_Msg_Count => l_msg_count,
1080 X_Msg_Data => l_msg_data
1081 );
1082
1083 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1084 RAISE FND_API.G_EXC_ERROR;
1085 END IF;
1086
1087 -- assign output variables
1088 px_header_rec := l_header_rec;
1089 px_line_table := l_line_tbl;
1090
1091 END IF;
1092
1093 EXCEPTION
1094 WHEN FND_API.G_EXC_ERROR THEN
1095 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1096 P_API_NAME => L_API_NAME
1097 ,P_PKG_NAME => G_PKG_NAME
1098 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1099 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1100 ,X_MSG_COUNT => X_MSG_COUNT
1101 ,X_MSG_DATA => X_MSG_DATA
1102 ,X_RETURN_STATUS => X_RETURN_STATUS);
1103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1104 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1105 P_API_NAME => L_API_NAME
1106 ,P_PKG_NAME => G_PKG_NAME
1107 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1108 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1109 ,X_MSG_COUNT => X_MSG_COUNT
1110 ,X_MSG_DATA => X_MSG_DATA
1111 ,X_RETURN_STATUS => X_RETURN_STATUS);
1112 WHEN OTHERS THEN
1113 Rollback to csptrreq_fm_order_PUB;
1114 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1115 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
1116 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
1117 FND_MSG_PUB.ADD;
1118 fnd_msg_pub.count_and_get
1119 ( p_count => x_msg_count
1120 , p_data => x_msg_data);
1121 x_return_status := FND_API.G_RET_STS_ERROR;
1122
1123 END csptrreq_fm_order;
1124
1125 PROCEDURE csptrreq_order_res(
1126 p_api_version IN NUMBER
1127 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1128 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1129 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
1130 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
1131 ,x_return_status OUT NOCOPY VARCHAR2
1132 ,x_msg_count OUT NOCOPY NUMBER
1133 ,x_msg_data OUT NOCOPY VARCHAR2
1134 ) IS
1135 l_api_version_number CONSTANT NUMBER := 1.0;
1136 l_api_name CONSTANT VARCHAR2(30) := 'csptrreq_fm_order';
1137 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1138 l_msg_count NUMBER;
1139 l_msg_data VARCHAR2(4000);
1140 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1141 l_user_id NUMBER;
1142 l_login_id NUMBER;
1143 l_today DATE;
1144 EXCP_USER_DEFINED EXCEPTION;
1145 l_check_existence NUMBER;
1146 J NUMBER;
1147 K NUMBER;
1148
1149 l_requirement_header_id NUMBER ;
1150 l_requirement_line_id NUMBER;
1151
1152 l_header_rec Header_Rec_Type;
1153 l_line_rec Line_rec_type;
1154 l_line_tbl Line_Tbl_type;
1155 l_oe_line_tbl Line_Tbl_type;
1156 l_oe_line_rec Line_rec_type;
1157 l_po_line_tbl Line_Tbl_type;
1158 l_po_line_Rec Line_Rec_Type;
1159
1160 l_reservation_rec CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1161 l_reservation_id NUMBER;
1162
1163 l_req_line_dtl_id NUMBER;
1164
1165 l_party_site_id NUMBER;
1166 l_customer_id NUMBER;
1167 l_cust_account_id NUMBER;
1168 l_org_id number;
1169
1170
1171 cursor get_party_details( c_incident_id number,c_location_id number) is
1172 select jpl.party_site_id, cia.customer_id, cia.account_id, cia.org_id
1173 from jtf_party_locations_v jpl, cs_incidents_all_b cia
1174 where jpl.party_id = cia.customer_id
1175 and cia.incident_id = c_incident_id
1176 and jpl.location_id = c_location_id;
1177
1178 BEGIN
1179
1180 SAVEPOINT csptrreq_fm_order_PUB;
1181
1182 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1183 -- initialize message list
1184 FND_MSG_PUB.initialize;
1185 END IF;
1186
1187 -- Standard call to check for call compatibility.
1188 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1189 p_api_version,
1190 l_api_name,
1191 G_PKG_NAME)
1192 THEN
1193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END IF;
1195
1196 -- initialize return status
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198
1199 l_header_Rec := px_header_rec;
1200 l_line_tbl := px_line_table;
1201
1202 -- user and login information
1203 SELECT Sysdate INTO l_today FROM dual;
1204 l_user_id := fnd_global.user_id;
1205 l_login_id := fnd_global.login_id;
1206
1207 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1208 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1209 'csp.plsql.csp_parts_requirement.csptrreq_order_res',
1210 'l_header_rec.address_type = ' || l_header_rec.address_type
1211 || ', l_header_rec.incident_id = ' || l_header_rec.incident_id
1212 || ', l_header_rec.task_id = ' || l_header_rec.task_id
1213 || ', l_header_Rec.ship_to_location_id = ' || l_header_Rec.ship_to_location_id);
1214 end if;
1215
1216 IF (l_header_rec.address_type = 'C') THEN
1217 IF (l_header_rec.incident_id IS NULL AND l_header_rec.task_id IS NOT NULL) THEN
1218 BEGIN
1219 SELECT source_object_id
1220 INTO l_header_rec.incident_id
1221 FROM jtf_Tasks_b
1222 where task_id = l_header_rec.task_id;
1223 EXCEPTION
1224 when no_data_found then
1225 null;
1226 END;
1227 END IF;
1228
1229 open get_party_details(l_header_Rec.incident_id,l_header_Rec.ship_to_location_id) ;
1230 FETCH get_party_details INTO l_party_site_id,l_customer_id,l_cust_account_id, l_org_id;
1231 CLOSE get_party_details;
1232
1233 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1234 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1235 'csp.plsql.csp_parts_requirement.csptrreq_order_res',
1236 'l_party_site_id = ' || l_party_site_id
1237 || ', l_customer_id = ' || l_customer_id
1238 || ', l_cust_account_id = ' || l_cust_account_id
1239 || ', l_org_id = ' || l_org_id);
1240 end if;
1241
1242 csp_ship_to_address_pvt.cust_inv_loc_link
1243 ( p_api_version => 1.0
1244 ,p_Init_Msg_List => FND_API.G_FALSE
1245 ,p_commit => FND_API.G_FALSE
1246 ,px_location_id => l_header_Rec.ship_to_location_id
1247 ,p_party_site_id => l_party_site_id
1248 ,p_cust_account_id => l_cust_account_id
1249 ,p_customer_id => l_customer_id
1250 ,p_org_id => l_org_id
1251 ,p_attribute_category => null
1252 ,p_attribute1 => null
1253 ,p_attribute2 => null
1254 ,p_attribute3 => null
1255 ,p_attribute4 => null
1256 ,p_attribute5 => null
1257 ,p_attribute6 => null
1258 ,p_attribute7 => null
1259 ,p_attribute8 => null
1260 ,p_attribute9 => null
1261 ,p_attribute10 => null
1262 ,p_attribute11 => null
1263 ,p_attribute12 => null
1264 ,p_attribute13 => null
1265 ,p_attribute14 => null
1266 ,p_attribute15 => null
1267 ,p_attribute16 => null
1268 ,p_attribute17 => null
1269 ,p_attribute18 => null
1270 ,p_attribute19 => null
1271 ,p_attribute20 => null
1272 ,x_return_status => l_return_status
1273 ,x_msg_count => l_msg_count
1274 ,x_msg_data => l_msg_data);
1275
1276 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1277 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1278 'csp.plsql.csp_parts_requirement.csptrreq_order_res',
1279 'l_return_status = ' || l_return_status);
1280 end if;
1281
1282 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1283 RAISE FND_API.G_EXC_ERROR;
1284 END IF;
1285 END IF;
1286
1287 -- create reservations and create line table which qualify for creating order
1288 K := 1;
1289 J := 1;
1290 FOR I IN 1..l_line_Tbl.COUNT LOOP
1291 l_line_rec := l_line_tbl(I);
1292 IF (l_line_rec.sourced_From = 'RES') THEN
1293 l_reservation_rec.need_by_date := sysdate; --l_header_rec.need_by_date;
1294 l_reservation_rec.organization_id := l_line_rec.source_organization_id;
1295 l_reservation_rec.item_id := l_line_Rec.inventory_item_id;
1296 l_Reservation_rec.revision := l_line_rec.revision;
1297 l_reservation_rec.item_uom_code := l_line_rec.unit_of_measure;
1298 l_reservation_rec.quantity_needed := l_line_rec.ordered_quantity;
1299 l_reservation_rec.sub_inventory_code := l_line_rec.source_subinventory;
1300
1301 l_reservation_id := csp_sch_int_pvt.create_reservation(
1302 p_reservation_parts => l_reservation_rec,
1303 x_return_status => l_return_status,
1304 x_msg_data => l_msg_data
1305 );
1306 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1307 RAISE FND_API.G_EXC_ERROR;
1308 ELSE
1309 -- code for inserting into req line dtls
1310 SELECT csp_req_line_Details_s1.nextval
1311 INTO l_req_line_Dtl_id
1312 FROM dual;
1313
1314 csp_req_line_Details_pkg.Insert_Row(
1315 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
1316 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
1317 p_CREATED_BY => nvl(l_user_id, 1),
1318 p_CREATION_DATE => sysdate,
1319 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
1320 p_LAST_UPDATE_DATE => sysdate,
1321 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
1322 p_SOURCE_TYPE => 'RES',
1323 p_SOURCE_ID => l_Reservation_id
1324 );
1325 END If;
1326 ELSIF (l_line_rec.sourced_from = 'IO') THEN
1327 l_oe_line_tbl(J) := l_line_rec;
1328 J := J + 1;
1329 ELSIF (l_line_rec.sourced_from = 'POREQ') THEN
1330 l_po_line_tbl(K) := l_line_Rec;
1331 K := K+1;
1332 END IF;
1333 END LOOP;
1334
1335 -- call csp_process_order API only if atleast one line qualifies for order to be created
1336
1337 IF (l_oe_line_tbl.count > 0) THEN
1338 csp_parts_order.process_order(
1339 p_api_version => l_api_version_number
1340 ,p_Init_Msg_List => p_init_msg_list
1341 ,p_commit => p_commit
1342 ,px_header_rec => l_header_rec
1343 ,px_line_table => l_oe_line_tbl
1344 ,x_return_status => l_return_status
1345 ,x_msg_count => l_msg_count
1346 ,x_msg_data => l_msg_data
1347 );
1348
1349 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1350 RAISE FND_API.G_EXC_ERROR;
1351 ELSE
1352 -- insert record in csp_req_line_details table with order information
1353 FOR I IN 1..l_oe_line_tbl.COUNT LOOP
1354 l_line_rec := l_oe_line_tbl(I);
1355
1356 SELECT csp_req_line_Details_s1.nextval
1357 INTO l_req_line_Dtl_id
1358 FROM dual;
1359
1360 csp_req_line_Details_pkg.Insert_Row(
1361 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
1362 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
1363 p_CREATED_BY => nvl(l_user_id, 1),
1364 p_CREATION_DATE => sysdate,
1365 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
1366 p_LAST_UPDATE_DATE => sysdate,
1367 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
1368 p_SOURCE_TYPE => 'IO',
1369 p_SOURCE_ID => l_line_rec.order_line_id);
1370 END LOOP;
1371 END IF;
1372 END IF;
1373
1374 -- create purchase requisitions if po_line_tbl has atleast one record
1375 IF (l_po_line_tbl.count > 0) THEN
1376 csp_parts_order.process_purchase_req(
1377 p_api_version => l_api_version_number
1378 ,p_init_msg_list => p_init_msg_list
1379 ,p_commit => p_commit
1380 ,px_header_rec => l_header_Rec
1381 ,px_line_Table => l_po_line_tbl
1382 ,x_return_status => l_return_status
1383 ,x_msg_count => l_msg_count
1384 ,x_msg_data => l_msg_data
1385 );
1386
1387 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1388 RAISE FND_API.G_EXC_ERROR;
1389 ELSE
1390 -- insert into csp_req_line_Details table with purchase req information
1391 FOR I IN 1..l_po_line_tbl.COUNT LOOP
1392 l_line_rec := l_po_line_tbl(I);
1393
1394 SELECT csp_req_line_Details_s1.nextval
1395 INTO l_req_line_Dtl_id
1396 FROM dual;
1397
1398 csp_req_line_Details_pkg.Insert_Row(
1399 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
1400 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
1401 p_CREATED_BY => nvl(l_user_id, 1),
1402 p_CREATION_DATE => sysdate,
1403 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
1404 p_LAST_UPDATE_DATE => sysdate,
1405 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
1406 p_SOURCE_TYPE => 'POREQ',
1407 p_SOURCE_ID => l_line_rec.requisition_line_id);
1408 END LOOP;
1409 END IF;
1410 END IF;
1411
1412 px_header_rec := l_header_rec;
1413 px_line_table := l_line_tbl;
1414
1415 EXCEPTION
1416 WHEN FND_API.G_EXC_ERROR THEN
1417 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1418 P_API_NAME => L_API_NAME
1419 ,P_PKG_NAME => G_PKG_NAME
1420 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1421 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1422 ,X_MSG_COUNT => X_MSG_COUNT
1423 ,X_MSG_DATA => X_MSG_DATA
1424 ,X_RETURN_STATUS => X_RETURN_STATUS);
1425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1426 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1427 P_API_NAME => L_API_NAME
1428 ,P_PKG_NAME => G_PKG_NAME
1429 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1430 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1431 ,X_MSG_COUNT => X_MSG_COUNT
1432 ,X_MSG_DATA => X_MSG_DATA
1433 ,X_RETURN_STATUS => X_RETURN_STATUS);
1434 WHEN OTHERS THEN
1435 Rollback to csptrreq_fm_order_PUB;
1436 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1437 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
1438 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
1439 FND_MSG_PUB.ADD;
1440 fnd_msg_pub.count_and_get
1441 ( p_count => x_msg_count
1442 , p_data => x_msg_data);
1443 x_return_status := FND_API.G_RET_STS_ERROR;
1444 END;
1445
1446 PROCEDURE delete_rqmt_header(
1447 p_api_version IN NUMBER
1448 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1449 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1450 ,p_header_id IN NUMBER
1451 ,x_return_status OUT NOCOPY VARCHAR2
1452 ,x_msg_count OUT NOCOPY NUMBER
1453 ,x_msg_data OUT NOCOPY VARCHAR2
1454 )IS
1455 l_api_version_number CONSTANT NUMBER := 1.0;
1456 l_api_name CONSTANT VARCHAR2(30) := 'delete_rqmt_header';
1457 EXCP_USER_DEFINED EXCEPTION;
1458 l_count NUMBER;
1459 BEGIN
1460 SAVEPOINT delete_rqmt_header_PUB;
1461
1462 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1463 -- initialize message list
1464 FND_MSG_PUB.initialize;
1465 END IF;
1466
1467 -- Standard call to check for call compatibility.
1468 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1469 p_api_version,
1470 l_api_name,
1471 G_PKG_NAME)
1472 THEN
1473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1474 END IF;
1475
1476 -- initialize return status
1477 x_return_status := FND_API.G_RET_STS_SUCCESS;
1478
1479 IF (p_header_id IS NOT NULL) THEN
1480 SELECT count(*)
1481 INTO l_count
1482 FROM csp_requirement_lines crl, csp_req_line_Details crld
1483 WHERE crl.requirement_header_id = p_header_id
1484 AND crl.requirement_line_id = crld.requirement_line_id;
1485
1486 IF l_count > 0 THEN
1487 FND_MESSAGE.SET_NAME ('CSP', 'CSP_RQMT_LINE_DELETE_ERROR');
1488 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_header_id', FALSE);
1489 FND_MSG_PUB.ADD;
1490 RAISE EXCP_USER_DEFINED;
1491 ELSE
1492 DELETE FROM csp_requirement_lines
1493 WHERE requirement_header_id = p_header_id;
1494
1495 DELETE FROM csp_requirement_headers
1496 WHERE requirement_header_id = p_header_id;
1497 END IF;
1498
1499 IF fnd_api.to_boolean(p_commit) THEN
1500 commit work;
1501 END IF;
1502 x_return_status := FND_API.G_RET_STS_SUCCESS;
1503 END IF;
1504 EXCEPTION
1505 WHEN EXCP_USER_DEFINED THEN
1506 Rollback to delete_rqmt_header_PUB;
1507 x_return_status := FND_API.G_RET_STS_ERROR;
1508 fnd_msg_pub.count_and_get
1509 ( p_count => x_msg_count
1510 , p_data => x_msg_data);
1511
1512 WHEN FND_API.G_EXC_ERROR THEN
1513 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1514 P_API_NAME => L_API_NAME
1515 ,P_PKG_NAME => G_PKG_NAME
1516 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1517 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1518 ,X_MSG_COUNT => X_MSG_COUNT
1519 ,X_MSG_DATA => X_MSG_DATA
1520 ,X_RETURN_STATUS => X_RETURN_STATUS);
1521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1522 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1523 P_API_NAME => L_API_NAME
1524 ,P_PKG_NAME => G_PKG_NAME
1525 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1526 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1527 ,X_MSG_COUNT => X_MSG_COUNT
1528 ,X_MSG_DATA => X_MSG_DATA
1529 ,X_RETURN_STATUS => X_RETURN_STATUS);
1530 WHEN OTHERS THEN
1531 Rollback to delete_rqmt_header_PUB;
1532 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1533 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1534 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1535 FND_MSG_PUB.ADD;
1536 fnd_msg_pub.count_and_get
1537 ( p_count => x_msg_count
1538 , p_data => x_msg_data);
1539 x_return_status := FND_API.G_RET_STS_ERROR;
1540 END;
1541
1542 PROCEDURE save_rqmt_line(
1543 p_api_version IN NUMBER
1544 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1545 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1546 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.Header_rec_type
1547 ,px_line_tbl IN OUT NOCOPY csp_parts_requirement.Line_tbl_type
1548 ,x_return_status OUT NOCOPY VARCHAR2
1549 ,x_msg_count OUT NOCOPY NUMBER
1550 ,x_msg_data OUT NOCOPY VARCHAR2
1551 )IS
1552 l_api_version_number CONSTANT NUMBER := 1.0;
1553 l_api_name CONSTANT VARCHAR2(30) := 'save_rqmt_line';
1554 l_header_rec csp_parts_requirement.Header_rec_type;
1555 l_line_tbl csp_parts_requirement.Line_Tbl_type;
1556 l_line_rec Line_rec_type;
1557 l_user_id NUMBER;
1558 l_login_id NUMBER;
1559 l_today DATE;
1560 l_parts_defined VARCHAR2(30);
1561 l_rqmt_header_Rec csp_requirement_headers_pvt.requirement_header_rec_type;
1562 l_rqmt_line_Rec csp_requirement_lines_pvt.requirement_line_rec_type;
1563 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
1564 l_rqmt_line_Tbl_out csp_requirement_lines_pvt.requirement_line_tbl_type;
1565 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1566 l_msg_count NUMBER;
1567 l_msg_data VARCHAR2(2000);
1568 l_requirement_header_id NUMBER;
1569 l_resource_name VARCHAR2(240);
1570
1571 EXCP_USER_DEFINED EXCEPTION;
1572
1573 l_party_site_id NUMBER;
1574 l_customer_id NUMBER;
1575 l_cust_account_id NUMBER;
1576 I NUMBER;
1577 l_check_existence NUMBER;
1578 l_count NUMBER;
1579 l_org_id number;
1580
1581 cursor get_party_details( c_incident_id number,c_location_id number) is
1582 select jpl.party_site_id, cia.customer_id, cia.account_id, cia.org_id
1583 from jtf_party_locations_v jpl, cs_incidents_all_b cia
1584 where jpl.party_id = cia.customer_id
1585 and cia.incident_id = c_incident_id
1586 and jpl.location_id = c_location_id;
1587
1588 cursor rqmt_lines_cur(p_rqmt_header_id NUMBER) IS
1589 select crl.requirement_line_id,
1590 crl.inventory_item_id ,
1591 crl.revision,
1592 crl.required_quantity,
1593 crl.uom_code
1594 from csp_requirement_lines crl,
1595 csp_req_line_details crld
1596 where crld.requirement_line_id(+) = crl.requirement_line_id
1597 and crl.requirement_header_id = p_rqmt_header_id
1598 and crld.source_id is null;
1599
1600 rqmt_line rqmt_lines_cur%ROWTYPE;
1601
1602 -- File Handling
1603 /* debug_handler utl_file.file_type;
1604 debug_handler1 utl_file.file_type;
1605 l_utl_dir varchar2(1000) := '/appslog/srv_top/utl/srvmntr8/out';
1606 */
1607 BEGIN
1608 SAVEPOINT save_rqmt_line_PUB;
1609
1610 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1611 -- initialize message list
1612 FND_MSG_PUB.initialize;
1613 END IF;
1614
1615 -- Standard call to check for call compatibility.
1616 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1617 p_api_version,
1618 l_api_name,
1619 G_PKG_NAME)
1620 THEN
1621
1622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1623 END IF;
1624
1625 -- initialize return status
1626 x_return_status := FND_API.G_RET_STS_SUCCESS;
1627 l_header_Rec := px_header_rec;
1628 l_line_tbl := px_line_tbl;
1629
1630 -- user and login information
1631 SELECT Sysdate INTO l_today FROM dual;
1632 l_user_id := fnd_global.user_id;
1633
1634 l_login_id := fnd_global.login_id;
1635
1636 IF (l_header_rec.requirement_header_id IS NULL) THEN
1637 -- create header
1638 --get resource information
1639 BEGIN
1640 SELECT resource_id
1641 INTO l_header_rec.resource_id
1642 FROM jtf_rs_resource_extns
1643 WHERE user_id = l_user_id;
1644 EXCEPTION
1645 WHEN no_Data_found THEN
1646 FND_MESSAGE.SET_NAME ('CSP', 'CSP_RS_USER_NOT_DEFINED');
1647 FND_MSG_PUB.ADD;
1648 RAISE EXCP_USER_DEFINED;
1649 END;
1650
1651 /* BEGIN
1652 SELECT resource_type, resource_name
1653 INTO l_header_Rec.resource_type, l_resource_name
1654 FROM jtf_rs_all_resources_vl
1655 WHERE resource_id = l_header_rec.resource_id;
1656 EXCEPTION
1657 WHEN no_Data_found THEN
1658 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_RES_ID_TYPE');
1659 FND_MSG_PUB.ADD;
1660 RAISE EXCP_USER_DEFINED;
1661 END;
1662 */
1663 -- Since wireless deals with employee type resources only, hardcode resource type
1664 l_header_rec.resource_type := 'RS_EMPLOYEE';
1665
1666 BEGIN
1667 SELECT organization_id, subinventory_code
1668 INTO l_header_rec.dest_organization_id, l_header_rec.dest_subinventory
1669 FROM CSP_INV_LOC_ASSIGNMENTS
1670 WHERE resource_id = l_header_rec.resource_id
1671 AND resource_type = l_header_rec.resource_type
1672 AND default_code = 'IN';
1673 EXCEPTION
1674 WHEN no_Data_found THEN
1675
1676 FND_MESSAGE.SET_NAME ('CSP', 'CSP_SCH_DEFAULT_SUBINV');
1677 FND_MESSAGE.SET_TOKEN('RESOURCE_TYPE', l_header_rec.resource_type);
1678 FND_MESSAGE.SET_TOKEN('RESOURCE_NAME', l_resource_name);
1679 FND_MSG_PUB.ADD;
1680 RAISE EXCP_USER_DEFINED;
1681 WHEN TOO_MANY_ROWS THEN
1682
1683 FND_MESSAGE.SET_NAME ('CSP', 'CSP_ONE_DEFAULT_IN_ALLOWED');
1684 FND_MSG_PUB.ADD;
1685 RAISE EXCP_USER_DEFINED;
1686 END;
1687
1688 IF (l_header_rec.task_id IS NOT NULL) THEN
1689 BEGIN
1690 SELECT source_object_id
1691 INTO l_header_rec.incident_id
1692 FROM jtf_tasks_b
1693 WHERE task_id = l_header_rec.task_id;
1694 EXCEPTION
1695 WHEN NO_DATA_FOUND THEN
1696
1697 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETER');
1698 FND_MESSAGE.SET_TOKEN('PARAMETER', 'Service Request');
1699 FND_MSG_PUB.ADD;
1700 RAISE EXCP_USER_DEFINED;
1701 END;
1702 END IF;
1703
1704 IF (l_header_rec.address_type = 'C') THEN
1705 open get_party_details(l_header_Rec.incident_id,l_header_Rec.ship_to_location_id) ;
1706 FETCH get_party_details INTO l_party_site_id,l_customer_id,l_cust_account_id, l_org_id;
1707 CLOSE get_party_details;
1708
1709 csp_ship_to_address_pvt.cust_inv_loc_link
1710 ( p_api_version => 1.0
1711 ,p_Init_Msg_List => FND_API.G_FALSE
1712 ,p_commit => FND_API.G_FALSE
1713 ,px_location_id => l_header_Rec.ship_to_location_id
1714 ,p_party_site_id => l_party_site_id
1715 ,p_cust_account_id => l_cust_account_id
1716 ,p_customer_id => l_customer_id
1717 ,p_org_id => l_org_id
1718 ,p_attribute_category => null
1719 ,p_attribute1 => null
1720 ,p_attribute2 => null
1721 ,p_attribute3 => null
1722 ,p_attribute4 => null
1723 ,p_attribute5 => null
1724 ,p_attribute6 => null
1725 ,p_attribute7 => null
1726 ,p_attribute8 => null
1727 ,p_attribute9 => null
1728 ,p_attribute10 => null
1729 ,p_attribute11 => null
1730 ,p_attribute12 => null
1731 ,p_attribute13 => null
1732 ,p_attribute14 => null
1733 ,p_attribute15 => null
1734 ,p_attribute16 => null
1735 ,p_attribute17 => null
1736 ,p_attribute18 => null
1737 ,p_attribute19 => null
1738 ,p_attribute20 => null
1739 ,x_return_status => l_return_status
1740 ,x_msg_count => l_msg_count
1741 ,x_msg_data => l_msg_data);
1742 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1743 RAISE FND_API.G_EXC_ERROR;
1744 END IF;
1745 END IF;
1746
1747 l_rqmt_header_Rec.requirement_header_id := nvl(l_header_rec.requirement_header_id, FND_API.G_MISS_NUM);
1748 l_rqmt_header_Rec.created_by := nvl(l_user_id, -1);
1749 l_rqmt_header_Rec.creation_date := l_today;
1750 l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
1751 l_rqmt_header_Rec.last_update_date := l_today;
1752 l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
1753 l_rqmt_header_Rec.ship_to_location_id := nvl(l_header_rec.ship_to_location_id, FND_API.G_MISS_NUM);
1754 l_rqmt_header_Rec.address_type := l_header_Rec.address_type;
1755 l_rqmt_header_Rec.task_id := nvl(l_header_rec.task_id, FND_API.G_MISS_NUM);
1756 l_rqmt_header_Rec.task_assignment_id := nvl(l_header_rec.task_assignment_id, FND_API.G_MISS_NUM);
1757 IF(l_header_rec.task_id IS NULL) THEN
1758 l_rqmt_header_Rec.resource_id := nvl(l_header_rec.resource_id, FND_API.G_MISS_NUM);
1759 l_rqmt_header_Rec.resource_type := nvl(l_header_rec.resource_type, FND_API.G_MISS_CHAR);
1760 END IF;
1761 l_rqmt_header_Rec.need_by_date := nvl(l_header_rec.need_by_date, FND_API.G_MISS_DATE);
1762 l_rqmt_header_Rec.destination_organization_id := nvl(l_header_rec.dest_organization_id, FND_API.G_MISS_NUM);
1763 l_rqmt_header_Rec.destination_subinventory := nvl(l_header_rec.dest_subinventory, FND_API.G_MISS_CHAR);
1764 l_rqmt_header_Rec.shipping_method_code := FND_API.G_MISS_CHAR;
1765 l_rqmt_header_Rec.parts_defined := nvl(l_parts_defined, FND_API.G_MISS_CHAR);
1766 l_rqmt_header_Rec.open_requirement := 'W';
1767 l_rqmt_header_rec.order_type_id := nvl(l_header_rec.order_type_id, FND_PROFILE.value('CSP_ORDER_TYPE'));
1768 l_rqmt_header_rec.attribute_Category := nvl(l_header_rec.attribute_category, FND_API.G_MISS_CHAR);
1769 l_rqmt_header_rec.attribute1 := nvl(l_header_rec.attribute1, FND_API.G_MISS_CHAR);
1770 l_rqmt_header_rec.attribute2 := nvl(l_header_rec.attribute2, FND_API.G_MISS_CHAR);
1771 l_rqmt_header_rec.attribute3 := nvl(l_header_rec.attribute3, FND_API.G_MISS_CHAR);
1772 l_rqmt_header_rec.attribute4 := nvl(l_header_rec.attribute4, FND_API.G_MISS_CHAR);
1773 l_rqmt_header_rec.attribute5 := nvl(l_header_rec.attribute5, FND_API.G_MISS_CHAR);
1774 l_rqmt_header_rec.attribute6 := nvl(l_header_rec.attribute6, FND_API.G_MISS_CHAR);
1775 l_rqmt_header_rec.attribute7 := nvl(l_header_rec.attribute7, FND_API.G_MISS_CHAR);
1776 l_rqmt_header_rec.attribute8 := nvl(l_header_rec.attribute8, FND_API.G_MISS_CHAR);
1777 l_rqmt_header_rec.attribute9 := nvl(l_header_rec.attribute9, FND_API.G_MISS_CHAR);
1778 l_rqmt_header_rec.attribute10 := nvl(l_header_rec.attribute10, FND_API.G_MISS_CHAR);
1779 l_rqmt_header_rec.attribute11 := nvl(l_header_rec.attribute11, FND_API.G_MISS_CHAR);
1780 l_rqmt_header_rec.attribute12 := nvl(l_header_rec.attribute12, FND_API.G_MISS_CHAR);
1781 l_rqmt_header_rec.attribute13 := nvl(l_header_rec.attribute13, FND_API.G_MISS_CHAR);
1782 l_rqmt_header_rec.attribute14 := nvl(l_header_rec.attribute14, FND_API.G_MISS_CHAR);
1783 l_rqmt_header_rec.attribute15 := nvl(l_header_rec.attribute15, FND_API.G_MISS_CHAR);
1784 l_rqmt_header_rec.ship_to_contact_id := nvl(l_header_rec.ship_to_contact_id, FND_API.G_MISS_NUM);
1785
1786 -- check to see if requirements exist for a given task
1787 /*commenting for 11.5.10 since multiple rqmt headers can exist for a task
1788 IF (l_header_rec.task_id IS NOT NULL) THEN
1789 BEGIN
1790 SELECT requirement_header_id
1791 INTO l_requirement_header_id
1792 FROM csp_requirement_headers
1793 WHERE task_id = l_header_rec.task_id;
1794
1795 IF (l_requirement_header_id IS NOT NULL) THEN
1796 Rollback to save_rqmt_line_PUB;
1797 --FND_MESSAGE.SET_NAME('CSP', 'CSP_TASK_RQMT_EXISTS');
1798 --FND_MSG_PUB.ADD;
1799 --fnd_msg_pub.count_and_get
1800 -- ( p_count => x_msg_count
1801 -- , p_data => x_msg_data);
1802 x_msg_data := fnd_message.get_string('CSP', 'CSP_TASK_RQMT_EXISTS');
1803 x_return_status := 'E';
1804 return;
1805 END IF;
1806 EXCEPTION
1807 WHEN NO_DATA_FOUND THEN
1808 -- call private api for inserting into csp_Requirement_headers
1809 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
1810 P_Api_Version_Number => l_api_Version_number,
1811 P_Init_Msg_List => p_init_msg_list,
1812 P_Commit => FND_API.G_FALSE,
1813 p_validation_level => null,
1814 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
1815 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
1816 X_Return_Status => l_Return_status,
1817 X_Msg_Count => l_msg_count,
1818 X_Msg_Data => l_msg_data
1819 );
1820
1821 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1822 fnd_msg_pub.count_and_get
1823 ( p_count => x_msg_count
1824 , p_data => x_msg_data);
1825 x_return_status := FND_API.G_RET_STS_ERROR;
1826 return;
1827 --RAISE FND_API.G_EXC_ERROR;
1828 END IF;
1829 l_header_rec.requirement_header_id := l_requirement_header_id;
1830 WHEN OTHERS THEN
1831 NULL;
1832 END;
1833 ELSE */
1834 -- call private api for inserting into csp_Requirement_headers
1835 CSP_REQUIREMENT_HEADERS_PVT.Create_requirement_headers(
1836 P_Api_Version_Number => l_api_Version_number,
1837 P_Init_Msg_List => p_init_msg_list,
1838 P_Commit => FND_API.G_FALSE,
1839 p_validation_level => null,
1840 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
1841 X_REQUIREMENT_HEADER_ID => l_requirement_header_id,
1842 X_Return_Status => l_Return_status,
1843 X_Msg_Count => l_msg_count,
1844 X_Msg_Data => l_msg_data
1845 );
1846
1847 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1848 RAISE FND_API.G_EXC_ERROR;
1849 END IF;
1850 l_header_rec.requirement_header_id := l_requirement_header_id;
1851 -- END IF;
1852
1853 ELSE
1854 -- update header
1855 -- check to see if requirement_header_id is valid
1856 BEGIN
1857 select requirement_header_id
1858 into l_check_existence
1859 from csp_requirement_headers
1860 where requirement_header_id = l_header_rec.requirement_header_id;
1861 EXCEPTION
1862 WHEN NO_DATA_FOUND THEN
1863 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
1864 fnd_message.set_token('HEADER_ID', to_char(l_header_rec.requirement_header_id), FALSE);
1865 FND_MSG_PUB.ADD;
1866 RAISE EXCP_USER_DEFINED;
1867 WHEN OTHERS THEN
1868 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1869 fnd_message.set_token('ERR_FIELD', 'l_header_rec.requirement_header_id', FALSE);
1870 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
1871 fnd_message.set_token('TABLE', 'CSP_REQUIREMENT_HEADERS', FALSE);
1872 FND_MSG_PUB.ADD;
1873 RAISE EXCP_USER_DEFINED;
1874 END;
1875
1876 l_rqmt_header_Rec.requirement_header_id := l_header_rec.requirement_header_id;
1877 l_rqmt_header_Rec.last_updated_by := nvl(l_user_id, -1);
1878 l_rqmt_header_Rec.last_update_date := l_today;
1879 l_rqmt_header_Rec.last_update_login := nvl(l_login_id, -1);
1880 l_rqmt_header_Rec.ship_to_location_id := nvl(l_header_rec.ship_to_location_id, FND_API.G_MISS_NUM);
1881 l_rqmt_header_Rec.task_id := nvl(l_header_rec.task_id, FND_API.G_MISS_NUM);
1882 l_rqmt_header_Rec.need_by_date := nvl(l_header_rec.need_by_date, FND_API.G_MISS_DATE);
1883
1884 CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
1885 P_Api_Version_Number => l_api_Version_number,
1886 P_Init_Msg_List => p_init_msg_list,
1887 P_Commit => FND_API.G_FALSE,
1888 p_validation_level => null,
1889 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
1890 X_Return_Status => l_Return_status,
1891 X_Msg_Count => l_msg_count,
1892 X_Msg_Data => l_msg_data
1893 );
1894
1895 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1896 RAISE FND_API.G_EXC_ERROR;
1897 END IF;
1898 END IF;
1899
1900 -- create rqmt line
1901 FOR I IN 1..l_line_tbl.COUNT LOOP
1902 l_line_rec := l_line_tbl(I);
1903 IF (l_line_rec.unit_of_measure IS NULL) THEN
1904 SELECT primary_uom_code
1905 INTO l_line_rec.unit_of_measure
1906 FROM mtl_system_items
1907 WHERE inventory_item_id = l_line_Rec.inventory_item_id
1908 AND organization_id = cs_Std.get_item_valdn_orgzn_id;
1909 END IF;
1910 l_rqmt_line_Rec.requirement_line_id := l_line_rec.requirement_line_id;
1911
1912 BEGIN
1913 select count(*)
1914 into l_count
1915 from csp_requirement_lines
1916 where requirement_line_id = l_rqmt_line_rec.requirement_line_id;
1917 IF l_count > 0 THEN
1918 fnd_message.set_name('CSP', 'CSP_DUPLICATE_RQMT_LINE');
1919 FND_MSG_PUB.ADD;
1920 RAISE EXCP_USER_DEFINED;
1921 END IF;
1922 EXCEPTION
1923 WHEN NO_DATA_FOUND THEN
1924 null;
1925 END;
1926 l_rqmt_line_Rec.created_by := nvl(l_user_id, -1);
1927 l_rqmt_line_Rec.creation_date := l_today;
1928 l_rqmt_line_rec.last_updated_by := nvl(l_user_id, 1);
1929 l_rqmt_line_rec.last_update_date := l_today;
1930 l_rqmt_line_rec.last_update_login := nvl(l_login_id, -1);
1931 l_rqmt_line_rec.requirement_header_id := l_header_rec.requirement_header_id;
1932 l_rqmt_line_rec.inventory_item_id := l_line_rec.inventory_item_id;
1933 l_rqmt_line_rec.uom_code := l_line_rec.unit_of_measure;
1934 l_rqmt_line_rec.required_quantity := l_line_rec.quantity;
1935 l_rqmt_line_rec.ship_complete_flag := l_line_rec.ship_complete;
1936 l_rqmt_line_rec.likelihood := l_line_rec.likelihood;
1937 l_rqmt_line_rec.revision := l_line_rec.revision;
1938 l_rqmt_line_rec.ordered_quantity := l_line_Rec.quantity;
1939 l_rqmt_line_rec.attribute_Category := nvl(l_line_rec.attribute_category, FND_API.G_MISS_CHAR);
1940 l_rqmt_line_rec.attribute1 := nvl(l_line_rec.attribute1, FND_API.G_MISS_CHAR);
1941 l_rqmt_line_rec.attribute2 := nvl(l_line_rec.attribute2, FND_API.G_MISS_CHAR);
1942 l_rqmt_line_rec.attribute3 := nvl(l_line_rec.attribute3, FND_API.G_MISS_CHAR);
1943 l_rqmt_line_rec.attribute4 := nvl(l_line_rec.attribute4, FND_API.G_MISS_CHAR);
1944 l_rqmt_line_rec.attribute5 := nvl(l_line_rec.attribute5, FND_API.G_MISS_CHAR);
1945 l_rqmt_line_rec.attribute6 := nvl(l_line_rec.attribute6, FND_API.G_MISS_CHAR);
1946 l_rqmt_line_rec.attribute7 := nvl(l_line_rec.attribute7, FND_API.G_MISS_CHAR);
1947 l_rqmt_line_rec.attribute8 := nvl(l_line_rec.attribute8, FND_API.G_MISS_CHAR);
1948 l_rqmt_line_rec.attribute9 := nvl(l_line_rec.attribute9, FND_API.G_MISS_CHAR);
1949 l_rqmt_line_rec.attribute10 := nvl(l_line_rec.attribute10, FND_API.G_MISS_CHAR);
1950 l_rqmt_line_rec.attribute11 := nvl(l_line_rec.attribute11, FND_API.G_MISS_CHAR);
1951 l_rqmt_line_rec.attribute12 := nvl(l_line_rec.attribute12, FND_API.G_MISS_CHAR);
1952 l_rqmt_line_rec.attribute13 := nvl(l_line_rec.attribute13, FND_API.G_MISS_CHAR);
1953 l_rqmt_line_rec.attribute14 := nvl(l_line_rec.attribute14, FND_API.G_MISS_CHAR);
1954 l_rqmt_line_rec.attribute15 := nvl(l_line_rec.attribute15, FND_API.G_MISS_CHAR);
1955
1956 l_rqmt_line_tbl(I) := l_rqmt_line_rec;
1957 END LOOP;
1958
1959 CSP_Requirement_Lines_PVT.Create_requirement_lines(
1960 P_Api_Version_Number => l_api_version_number,
1961 P_Init_Msg_List => p_Init_Msg_List,
1962 P_Commit => FND_API.G_FALSE,
1963 p_validation_level => null,
1964 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
1965 x_Requirement_Line_tbl => l_rqmt_line_tbl_out,
1966 X_Return_Status => l_return_status,
1967 X_Msg_Count => l_msg_count,
1968 X_Msg_Data => l_msg_data
1969 );
1970
1971 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1972 RAISE FND_API.G_EXC_ERROR;
1973 END IF;
1974
1975 -- Return header rec and line table.
1976 px_header_rec := l_header_rec;
1977 OPEN rqmt_lines_cur(l_header_rec.requirement_header_id);
1978 I := 1;
1979 LOOP
1980 FETCH rqmt_lines_cur INTO rqmt_line;
1981 EXIT WHEN rqmt_lines_cur%NOTFOUND;
1982 px_line_tbl(I).requirement_line_id := rqmt_line.requirement_line_id;
1983 px_line_tbl(I).inventory_item_id := rqmt_line.inventory_item_id;
1984 px_line_tbl(I).revision := rqmt_line.revision;
1985 px_line_tbl(I).quantity := rqmt_line.required_quantity;
1986 px_line_tbl(I).unit_of_measure := rqmt_line.uom_code;
1987 I := I + 1;
1988 END LOOP;
1989
1990 IF fnd_api.to_boolean(p_commit) THEN
1991 commit work;
1992 END IF;
1993
1994 x_return_status := FND_API.G_RET_STS_SUCCESS;
1995 EXCEPTION
1996 WHEN EXCP_USER_DEFINED THEN
1997 Rollback to save_rqmt_line_PUB;
1998 x_return_status := FND_API.G_RET_STS_ERROR;
1999 fnd_msg_pub.count_and_get
2000 ( p_count => x_msg_count
2001 , p_data => x_msg_data);
2002
2003 WHEN FND_API.G_EXC_ERROR THEN
2004 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2005 P_API_NAME => L_API_NAME
2006 ,P_PKG_NAME => G_PKG_NAME
2007 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2008 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2009 ,X_MSG_COUNT => X_MSG_COUNT
2010 ,X_MSG_DATA => X_MSG_DATA
2011 ,X_RETURN_STATUS => X_RETURN_STATUS);
2012
2013 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2014 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2015 P_API_NAME => L_API_NAME
2016 ,P_PKG_NAME => G_PKG_NAME
2017 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2018 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2019 ,X_MSG_COUNT => X_MSG_COUNT
2020 ,X_MSG_DATA => X_MSG_DATA
2021 ,X_RETURN_STATUS => X_RETURN_STATUS);
2022 WHEN OTHERS THEN
2023 Rollback to save_rqmt_line_PUB;
2024 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2025 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2026 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2027 FND_MSG_PUB.ADD;
2028 fnd_msg_pub.count_and_get
2029 ( p_count => x_msg_count
2030 , p_data => x_msg_data);
2031 x_return_status := FND_API.G_RET_STS_ERROR;
2032 END;
2033
2034
2035 PROCEDURE delete_rqmt_line(
2036 p_api_version IN NUMBER
2037 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2038 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2039 ,p_line_tbl IN OUT NOCOPY csp_parts_requirement.Rqmt_Line_tbl_type
2040 ,x_return_status OUT NOCOPY VARCHAR2
2041 ,x_msg_count OUT NOCOPY NUMBER
2042 ,x_msg_data OUT NOCOPY VARCHAR2
2043 ) IS
2044 l_api_version_number CONSTANT NUMBER := 1.0;
2045 l_api_name CONSTANT VARCHAR2(30) := 'delete_rqmt_line';
2046 BEGIN
2047 SAVEPOINT delete_rqmt_line_PUB;
2048
2049 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2050 -- initialize message list
2051 FND_MSG_PUB.initialize;
2052 END IF;
2053
2054 -- Standard call to check for call compatibility.
2055 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2056 p_api_version,
2057 l_api_name,
2058 G_PKG_NAME)
2059 THEN
2060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2061 END IF;
2062
2063 -- initialize return status
2064 x_return_status := FND_API.G_RET_STS_SUCCESS;
2065
2066 IF (p_line_tbl.count > 0) THEN
2067 FOR I IN 1..p_line_Tbl.count LOOP
2068 DELETE FROM csp_Requirement_lines
2069 WHERE requirement_line_id = p_line_tbl(I).requirement_line_id;
2070 END LOOP;
2071 END IF;
2072
2073 IF fnd_api.to_boolean(p_commit) THEN
2074 commit work;
2075 END IF;
2076 x_return_status := FND_API.G_RET_STS_SUCCESS;
2077
2078 EXCEPTION
2079
2080 WHEN FND_API.G_EXC_ERROR THEN
2081 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2082 P_API_NAME => L_API_NAME
2083 ,P_PKG_NAME => G_PKG_NAME
2084 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2085 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2086 ,X_MSG_COUNT => X_MSG_COUNT
2087 ,X_MSG_DATA => X_MSG_DATA
2088 ,X_RETURN_STATUS => X_RETURN_STATUS);
2089 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2090 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2091 P_API_NAME => L_API_NAME
2092 ,P_PKG_NAME => G_PKG_NAME
2093 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2094 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2095 ,X_MSG_COUNT => X_MSG_COUNT
2096 ,X_MSG_DATA => X_MSG_DATA
2097 ,X_RETURN_STATUS => X_RETURN_STATUS);
2098 WHEN OTHERS THEN
2099 Rollback to delete_rqmt_line_PUB;
2100 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2101 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2102 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2103 FND_MSG_PUB.ADD;
2104 fnd_msg_pub.count_and_get
2105 ( p_count => x_msg_count
2106 , p_data => x_msg_data);
2107 x_return_status := FND_API.G_RET_STS_ERROR;
2108 END;
2109
2110 PROCEDURE check_Availability(
2111 p_api_version IN NUMBER
2112 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2113 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2114 ,p_header_id IN NUMBER
2115 ,x_line_Tbl OUT NOCOPY csp_parts_requirement.Line_tbl_type
2116 ,x_avail_flag OUT NOCOPY VARCHAR2
2117 ,x_return_status OUT NOCOPY VARCHAR2
2118 ,x_msg_count OUT NOCOPY NUMBER
2119 ,x_msg_data OUT NOCOPY VARCHAR2
2120 )IS
2121 l_api_version_number CONSTANT NUMBER := 1.0;
2122 l_api_name CONSTANT VARCHAR2(30) := 'check_availability';
2123 l_count NUMBER;
2124 l_header_rec csp_parts_requirement.Header_rec_type;
2125 l_line_rec csp_parts_requirement.line_rec_type;
2126 l_line_tbl csp_parts_Requirement.line_Tbl_type;
2127 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
2128 EXCP_USER_DEFINED EXCEPTION;
2129 l_parts_list_rec csp_sch_int_pvt.csp_parts_rec_type;
2130 l_parts_list_tbl csp_sch_int_pvt.csp_parts_tbl_typ1;
2131 l_avail_list_tbl csp_sch_int_pvt.available_parts_tbl_typ1;
2132 I NUMBER;
2133 J NUMBER;
2134 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2135 l_msg_count NUMBER;
2136 l_msg_data VARCHAR2(4000);
2137
2138 CURSOR rqmt_line_cur IS
2139 select crl.requirement_line_id,
2140 crl.inventory_item_id,
2141 crl.uom_code,
2142 crl.required_quantity
2143 from csp_requirement_lines crl,
2144 csp_req_line_details crld
2145 where crld.requirement_line_id(+) = crl.requirement_line_id
2146 and crl.requirement_header_id = p_header_id
2147 and crld.source_id is null;
2148
2149 BEGIN
2150 SAVEPOINT check_Availability_PUB;
2151
2152 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2153 -- initialize message list
2154 FND_MSG_PUB.initialize;
2155 END IF;
2156
2157 -- Standard call to check for call compatibility.
2158 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2159 p_api_version,
2160 l_api_name,
2161 G_PKG_NAME)
2162 THEN
2163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2164 END IF;
2165
2166 -- initialize return status
2167 x_return_status := FND_API.G_RET_STS_SUCCESS;
2168
2169 IF (p_header_id IS NOT NULL) THEN
2170 select count(*)
2171 into l_count
2172 from csp_requirement_lines crl,
2173 csp_req_line_details crld
2174 where crld.requirement_line_id(+) = crl.requirement_line_id
2175 and crl.requirement_header_id = p_header_id
2176 and crld.source_id is null;
2177
2178 IF (l_count > 0) THEN
2179 BEGIN
2180 SELECT
2181 requirement_header_id,
2182 need_by_date,
2183 destination_organization_id,
2184 destination_subinventory
2185 INTO
2186 l_header_rec.requirement_header_id,
2187 l_header_rec.need_by_Date,
2188 l_header_rec.dest_organization_id,
2189 l_header_rec.dest_subinventory
2190 FROM csp_Requirement_headers
2191 WHERE requirement_header_id = p_header_id;
2192
2193 EXCEPTION
2194 WHEN no_data_found THEN
2195 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
2196 fnd_message.set_token('HEADER_ID', to_char(p_header_id), FALSE);
2197 FND_MSG_PUB.ADD;
2198 RAISE EXCP_USER_DEFINED;
2199 END;
2200
2201 OPEN rqmt_line_cur;
2202 I := 1;
2203 LOOP
2204 FETCH rqmt_line_cur
2205 INTO l_line_rec.requirement_line_id,
2206 l_line_rec.inventory_item_id,
2207 l_line_rec.unit_of_measure,
2208 l_line_rec.quantity;
2209 EXIT WHEN rqmt_line_cur%NOTFOUND;
2210 l_line_tbl(I) := l_line_rec;
2211 l_parts_list_tbl(I).item_id := l_line_rec.inventory_item_id;
2212 l_parts_list_tbl(I).item_uom := l_line_rec.unit_of_measure;
2213 l_parts_list_tbl(I).quantity := l_line_rec.quantity;
2214 l_parts_list_tbl(I).line_id := l_line_rec.requirement_line_id;
2215 I := I + 1;
2216 END LOOP;
2217
2218 IF (l_parts_list_Tbl.count > 0) THEN
2219 FND_MSG_PUB.initialize;
2220 -- call csp_sch_int_pvt.check_parts_availability()
2221 csp_sch_int_pvt.check_parts_availability(
2222 p_resource => null,
2223 p_organization_id => l_header_rec.dest_organization_id,
2224 p_subinv_code => l_header_rec.dest_subinventory,
2225 p_need_by_date => l_header_rec.need_by_date,
2226 p_parts_list => l_parts_list_tbl,
2227 p_timezone_id => null,
2228 x_availability => l_avail_list_tbl,
2229 x_return_status => l_return_status,
2230 x_msg_data => l_msg_data,
2231 x_msg_count => l_msg_count,
2232 p_Called_From => 'MOBILE',
2233 p_location_id => l_header_rec.ship_to_location_id
2234 );
2235
2236 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2237 null;
2238 --RAISE FND_API.G_EXC_ERROR;
2239 END IF;
2240
2241 J := 1;
2242 I := 1;
2243 IF (l_Avail_list_tbl.COUNT > 0) THEN
2244 WHILE (I <= l_line_tbl.COUNT AND J <= l_Avail_list_Tbl.COUNT) LOOP
2245 IF (l_line_tbl(I).requirement_line_id = l_avail_list_Tbl(J).line_id) THEN
2246 IF (nvl(l_Avail_list_tbl(J).recommended_option, 'N') = 'Y') THEN
2247 -- IF (l_avail_list_tbl(J).source_type = 'INVENTORY') THEN
2248 IF (l_line_Tbl(I).inventory_item_id <> l_avail_list_tbl(I).item_id) THEN
2249 l_line_tbl(I).inventory_item_id := l_Avail_list_tbl(I).item_id;
2250 END IF;
2251 IF (l_line_Tbl(I).arrival_date > l_header_rec.need_by_date) THEN
2252 l_line_tbl(I).available_by_need_date := 'N';
2253 ELSE
2254 l_line_tbl(I).available_by_need_date := 'Y';
2255 END IF;
2256 l_line_tbl(I).sourced_from := l_Avail_list_tbl(J).source_type;
2257 l_line_tbl(I).source_organization_id := l_avail_list_tbl(J).source_org_id;
2258 l_line_tbl(I).arrival_Date := l_Avail_list_tbl(J).arraival_date;
2259 l_line_Tbl(I).shipping_method_code := l_Avail_list_tbl(J).shipping_methode;
2260 l_line_tbl(I).order_by_date := l_Avail_list_Tbl(J).order_by_Date;
2261 l_line_Tbl(I).ordered_quantity := l_Avail_list_Tbl(J).ordered_quantity;
2262 J := J + 1;
2263 I := I + 1;
2264 --END IF;
2265 ELSE
2266 J := J+1;
2267 END IF;
2268 ELSE
2269 J := J + 1;
2270 END IF;
2271 END LOOP;
2272 END IF;
2273 FOR I IN 1..l_line_tbl.COUNT LOOP
2274 IF (nvl(l_line_tbl(I).sourced_from, 'IO') = 'IO') THEN
2275 -- not required since this will be part of the call to check_Avail
2276 /*
2277 and l_line_Tbl(I).source_organization_id IS NULL) THEN
2278 Get_source_organization(P_Inventory_Item_Id => l_line_Tbl(I).inventory_item_id,
2279 P_Organization_Id => l_header_Rec.dest_organization_id,
2280 P_Secondary_Inventory => l_header_rec.dest_subinventory,
2281 x_source_org_id => l_line_Tbl(I).source_organization_id);
2282 */
2283 IF ((l_line_Tbl(I).source_organization_id IS NULL) OR
2284 (l_line_Tbl(I).source_organization_id = FND_API.G_MISS_NUM)) THEN
2285 -- no source organization defined, create requirement with error status
2286 x_avail_flag := 'N';
2287 END IF;
2288 END If;
2289 -- insert into l_rqmt_line_rec for updation.
2290 l_rqmt_line_tbl(I).requirement_line_id := l_line_tbl(I).requirement_line_id;
2291 l_rqmt_line_tbl(I).last_updated_by := nvl(FND_GLOBAL.user_id, 1);
2292 l_rqmt_line_tbl(I).last_update_date := sysdate;
2293 l_rqmt_line_tbl(I).last_update_login := nvl(FND_GLOBAL.login_id , -1);
2294 l_rqmt_line_tbl(I).inventory_item_id := l_line_tbl(I).inventory_item_id;
2295 l_rqmt_line_tbl(I).sourced_From := l_line_tbl(I).sourced_From;
2296 l_rqmt_line_tbl(I).source_organization_id := nvl(l_line_tbl(I).source_organization_id, FND_API.G_MISS_NUM);
2297 l_rqmt_line_tbl(I).arrival_Date := nvl(l_line_tbl(I).arrival_Date, FND_API.G_MISS_DATE);
2298 l_rqmt_line_tbl(I).shipping_method_code := nvl(l_line_Tbl(I).shipping_method_code, FND_API.G_MISS_CHAR);
2299 l_rqmt_line_tbl(I).order_by_date := nvl(l_line_tbl(I).order_by_date, FND_API.G_MISS_DATE);
2300 l_rqmt_line_tbl(I).ordered_quantity := nvl(l_line_Tbl(I).ordered_quantity, 0);
2301 END LOOP;
2302
2303 IF l_rqmt_line_tbl.COUNT > 0 THEN
2304 CSP_Requirement_Lines_PVT.Update_requirement_lines(
2305 P_Api_Version_Number => l_api_version_number,
2306 P_Init_Msg_List => p_Init_Msg_List,
2307 P_Commit => FND_API.G_FALSE,
2308 p_validation_level => null,
2309 P_Requirement_Line_Tbl => l_rqmt_line_tbl,
2310 X_Return_Status => l_return_status,
2311 X_Msg_Count => l_msg_count,
2312 X_Msg_Data => l_msg_data
2313 );
2314
2315 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2316 RAISE FND_API.G_EXC_ERROR;
2317 END IF;
2318 END IF;
2319 END If;
2320 x_line_tbl := l_line_tbl;
2321 x_avail_flag := nvl(x_avail_flag, 'Y');
2322 END IF;
2323
2324 IF fnd_api.to_boolean(p_commit) THEN
2325 commit work;
2326 END IF;
2327 END IF;
2328 EXCEPTION
2329 WHEN EXCP_USER_DEFINED THEN
2330 Rollback to check_Availability_PUB;
2331 x_return_status := FND_API.G_RET_STS_ERROR;
2332 fnd_msg_pub.count_and_get
2333 ( p_count => x_msg_count
2334 , p_data => x_msg_data);
2335
2336 WHEN FND_API.G_EXC_ERROR THEN
2337 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2338 P_API_NAME => L_API_NAME
2339 ,P_PKG_NAME => G_PKG_NAME
2340 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2341 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2342 ,X_MSG_COUNT => X_MSG_COUNT
2343 ,X_MSG_DATA => X_MSG_DATA
2344 ,X_RETURN_STATUS => X_RETURN_STATUS);
2345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2346 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2347 P_API_NAME => L_API_NAME
2348 ,P_PKG_NAME => G_PKG_NAME
2349 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2350 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2351 ,X_MSG_COUNT => X_MSG_COUNT
2352 ,X_MSG_DATA => X_MSG_DATA
2353 ,X_RETURN_STATUS => X_RETURN_STATUS);
2354 WHEN OTHERS THEN
2355 Rollback to check_Availability_pub;
2356 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2357 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2358 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2359 FND_MSG_PUB.ADD;
2360 fnd_msg_pub.count_and_get
2361 ( p_count => x_msg_count
2362 , p_data => x_msg_data);
2363 x_return_status := FND_API.G_RET_STS_ERROR;
2364 END;
2365
2366 PROCEDURE create_order(
2367 p_api_version IN NUMBER
2368 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
2369 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2370 ,p_header_id IN NUMBER
2371 ,x_order_tbl OUT NOCOPY Order_Tbl_Type
2372 ,x_return_status OUT NOCOPY VARCHAR2
2373 ,x_msg_count OUT NOCOPY NUMBER
2374 ,x_msg_data OUT NOCOPY VARCHAR2
2375 )IS
2376 l_api_version_number CONSTANT NUMBER := 1.0;
2377 l_api_name CONSTANT VARCHAR2(30) := 'create_order';
2378 l_count NUMBER;
2379 l_header_rec csp_parts_requirement.Header_rec_type;
2380 l_line_rec csp_parts_requirement.line_rec_type;
2381 l_line_tbl csp_parts_Requirement.line_Tbl_type;
2382 l_oe_line_tbl csp_parts_requirement.line_Tbl_Type;
2383 l_po_line_Tbl csp_parts_requirement.line_tbl_type;
2384 l_rqmt_line_Tbl csp_requirement_lines_pvt.requirement_line_tbl_type;
2385 EXCP_USER_DEFINED EXCEPTION;
2386 I NUMBER;
2387 J NUMBER;
2388 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2389 l_msg_count NUMBER;
2390 l_msg_data VARCHAR2(2000);
2391 l_order_number NUMBER;
2392 l_open_requirement VARCHAr2(30);
2393 l_req_line_dtl_id NUMBER;
2394 l_user_id NUMBER;
2395 l_login_id NUMBER;
2396 l_today DATE;
2397
2398 CURSOR rqmt_line_cur IS
2399 SELECT c.requirement_line_id,
2400 c.inventory_item_id,
2401 c.revision,
2402 c.uom_code,
2403 c.required_quantity,
2404 c.sourced_from,
2405 c.source_organization_id,
2406 c.source_subinventory,
2407 c.shipping_method_code,
2408 c.arrival_date
2409 FROM csp_requirement_lines c
2410 WHERE c.requirement_header_id = p_header_id
2411 AND not exists (select 1 from csp_req_line_Details d where d.requirement_line_id = c.requirement_line_id)
2412 FOR UPDATE of c.order_line_id NOWAIT;
2413
2414 BEGIN
2415
2416 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2417 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2418 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2419 'Begin');
2420 end if;
2421
2422 SAVEPOINT create_order_PUB;
2423
2424 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2425 -- initialize message list
2426 FND_MSG_PUB.initialize;
2427 END IF;
2428
2429 -- Standard call to check for call compatibility.
2430 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2431 p_api_version,
2432 l_api_name,
2433 G_PKG_NAME)
2434 THEN
2435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2436 END IF;
2437
2438 -- initialize return status
2439 x_return_status := FND_API.G_RET_STS_SUCCESS;
2440
2441 -- user and login information
2442 SELECT Sysdate INTO l_today FROM dual;
2443 l_user_id := fnd_global.user_id;
2444 l_login_id := fnd_global.login_id;
2445
2446 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2447 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2448 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2449 'l_user_id = ' || l_user_id
2450 || ', l_login_id = ' || l_login_id);
2451 end if;
2452
2453 IF (p_header_id IS NOT NULL) THEN
2454 select count(*)
2455 into l_count
2456 from csp_requirement_lines crl,
2457 csp_req_line_details crld
2458 where crld.requirement_line_id(+) = crl.requirement_line_id
2459 and crl.requirement_header_id = p_header_id
2460 and crld.source_id is null;
2461
2462 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2463 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2464 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2465 'p_header_id is NOT NULL and number of lines for this requirement is = ' || l_count);
2466 end if;
2467
2468 IF (l_count > 0) THEN
2469 BEGIN
2470 SELECT
2471 requirement_header_id,
2472 nvl(order_type_id, FND_PROFILE.value('CSP_ORDER_TYPE')),
2473 ship_to_location_id,
2474 need_by_date,
2475 destination_organization_id,
2476 destination_subinventory
2477 INTO
2478 l_header_rec.requirement_header_id,
2479 l_header_rec.order_type_id,
2480 l_header_Rec.ship_to_location_id,
2481 l_header_rec.need_by_Date,
2482 l_header_rec.dest_organization_id,
2483 l_header_rec.dest_subinventory
2484 FROM csp_Requirement_headers
2485 WHERE requirement_header_id = p_header_id;
2486
2487 l_header_Rec.operation := G_OPR_CREATE;
2488
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 'Part Req Header Data... '
2493 || 'l_header_rec.requirement_header_id = ' || l_header_rec.requirement_header_id
2494 || ', l_header_rec.order_type_id = ' || l_header_rec.order_type_id
2495 || ', l_header_Rec.ship_to_location_id = ' || l_header_Rec.ship_to_location_id
2496 || ', l_header_rec.need_by_Date = ' || l_header_rec.need_by_Date
2497 || ', l_header_rec.dest_organization_id = ' || l_header_rec.dest_organization_id
2498 || ', l_header_rec.dest_subinventory = ' || l_header_rec.dest_subinventory
2499 || ', l_header_Rec.operation = ' || l_header_Rec.operation);
2500 end if;
2501
2502 EXCEPTION
2503 WHEN no_data_found THEN
2504 fnd_message.set_name('CSP', 'CSP_INVALID_REQUIREMENT_HEADER');
2505 fnd_message.set_token('HEADER_ID', to_char(p_header_id), FALSE);
2506 FND_MSG_PUB.ADD;
2507 RAISE EXCP_USER_DEFINED;
2508 END;
2509
2510 OPEN rqmt_line_cur;
2511 I := 1;
2512 J := 1;
2513 LOOP
2514 FETCH rqmt_line_cur
2515 INTO l_line_rec.requirement_line_id,
2516 l_line_rec.inventory_item_id,
2517 l_line_Rec.revision,
2518 l_line_rec.unit_of_measure,
2519 l_line_rec.quantity,
2520 l_line_rec.sourced_from,
2521 l_line_rec.source_organization_id,
2522 l_line_rec.source_subinventory,
2523 l_line_rec.shipping_method_code,
2524 l_line_rec.arrival_Date ;
2525
2526 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2527 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2528 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2529 'Part Req Line Data... '
2530 || 'l_line_rec.requirement_line_id = ' || l_line_rec.requirement_line_id
2531 || ', l_line_rec.inventory_item_id = ' || l_line_rec.inventory_item_id
2532 || ', l_line_Rec.revision = ' || l_line_Rec.revision
2533 || ', l_line_rec.unit_of_measure = ' || l_line_rec.unit_of_measure
2534 || ', l_line_rec.quantity = ' || l_line_rec.quantity
2535 || ', l_line_rec.sourced_from = ' || l_line_rec.sourced_from
2536 || ', l_line_rec.source_organization_id = ' || l_line_rec.source_organization_id
2537 || ', l_line_rec.source_subinventory = ' || l_line_rec.source_subinventory
2538 || ', l_line_rec.shipping_method_code = ' || l_line_rec.shipping_method_code
2539 || ', l_line_rec.arrival_Date = ' || l_line_rec.arrival_Date);
2540 end if;
2541
2542 EXIT WHEN rqmt_line_cur%NOTFOUND;
2543 IF (l_line_rec.sourced_From = 'IO' and l_line_rec.source_organization_id IS NULL) THEN
2544 fnd_message.set_name('CSP', 'CSP_NULL_SRC_ORGN');
2545 fnd_msg_pub.add;
2546 RAISE EXCP_USER_DEFINED;
2547 END If;
2548 l_line_rec.ordered_quantity := l_line_Rec.quantity;
2549 IF (l_line_rec.sourced_from = 'IO') THEN
2550 l_line_rec.line_num := I;
2551 l_oe_line_tbl(I) := l_line_rec;
2552 I := I + 1;
2553 ELSIF (l_line_Rec.sourced_From = 'POREQ') THEN
2554 l_line_Rec.line_num := J;
2555 l_po_line_Tbl(J) := l_line_rec;
2556 J := J + 1;
2557 END IF;
2558 END LOOP;
2559
2560 IF (l_oe_line_tbl.COUNT > 0) THEN
2561 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2562 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2563 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2564 'Calling csp_parts_order.process_order...');
2565 end if;
2566 csp_parts_order.process_order(
2567 p_api_version => l_api_version_number
2568 ,p_Init_Msg_List => p_init_msg_list
2569 ,p_commit => FND_API.G_FALSE
2570 ,px_header_rec => l_header_rec
2571 ,px_line_table => l_oe_line_tbl
2572 ,x_return_status => l_return_status
2573 ,x_msg_count => l_msg_count
2574 ,x_msg_data => l_msg_data
2575 );
2576 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2577 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2578 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2579 'csp_parts_order.process_order return status = ' || l_return_status);
2580 end if;
2581
2582 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2583 RAISE FND_API.G_EXC_ERROR;
2584 ELSE
2585 -- update open_requirement_flag on requirement header
2586 BEGIN
2587 l_open_requirement := 'S';
2588 update csp_requirement_headers
2589 set open_requirement = 'S'
2590 where requirement_header_id = p_header_id;
2591 EXCEPTION
2592 when others then
2593 null;
2594 END;
2595 IF (l_header_rec.order_header_id IS NOT NULL) THEN
2596 BEGIN
2597 SELECT order_number
2598 INTO l_order_number
2599 FROM OE_ORDER_HEADERS_ALL
2600 WHERE header_id = l_header_Rec.order_header_id;
2601
2602 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2603 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2604 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2605 'l_order_number = ' || l_order_number);
2606 end if;
2607 EXCEPTION
2608 when no_data_found then
2609 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2610 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2611 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2612 'No Data Found for l_header_rec.order_header_id = '
2613 || l_header_rec.order_header_id || ' in OE_ORDER_HEADERS_ALL');
2614 end if;
2615 null;
2616 END;
2617 END IF;
2618
2619 x_order_tbl(1).source_type := 'IO';
2620 x_order_tbl(1).order_number := l_order_number;
2621 -- x_order_tbl(1).line_tbl := l_oe_line_tbl;
2622
2623 -- update csp_requirement_line table with order information
2624 FOR I IN 1..l_oe_line_tbl.COUNT LOOP
2625 l_line_rec := l_oe_line_tbl(I);
2626
2627 SELECT csp_req_line_Details_s1.nextval
2628 INTO l_req_line_Dtl_id
2629 FROM dual;
2630
2631 csp_req_line_Details_pkg.Insert_Row(
2632 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
2633 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
2634 p_CREATED_BY => nvl(l_user_id, 1),
2635 p_CREATION_DATE => sysdate,
2636 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
2637 p_LAST_UPDATE_DATE => sysdate,
2638 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
2639 p_SOURCE_TYPE => 'IO',
2640 p_SOURCE_ID => l_line_rec.order_line_id);
2641 END LOOP;
2642 END IF;
2643 END IF;
2644
2645 -- create purchase requisitions if po_line_tbl has atleast one record
2646 IF (l_po_line_tbl.count > 0) THEN
2647
2648 l_header_rec.requisition_header_id := null;
2649 l_header_rec.requisition_number := null;
2650
2651 csp_parts_order.process_purchase_req(
2652 p_api_version => l_api_version_number
2653 ,p_init_msg_list => p_init_msg_list
2654 ,p_commit => p_commit
2655 ,px_header_rec => l_header_Rec
2656 ,px_line_Table => l_po_line_tbl
2657 ,x_return_status => l_return_status
2658 ,x_msg_count => l_msg_count
2659 ,x_msg_data => l_msg_data
2660 );
2661
2662 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2663 RAISE FND_API.G_EXC_ERROR;
2664 ELSE
2665 IF (nvl(l_open_requirement, 'W') <> 'S') THEN
2666 update csp_requirement_headers
2667 set open_requirement = 'S'
2668 where requirement_header_id = p_header_id;
2669 END IF;
2670
2671 x_order_tbl(2).source_type := 'POREQ';
2672 x_order_tbl(2).order_number := l_header_rec.requisition_number;
2673 -- x_order_tbl(2).line_tbl := l_po_line_tbl;
2674
2675 -- insert into csp_req_line_Details table with purchase req information
2676 FOR I IN 1..l_po_line_tbl.COUNT LOOP
2677 l_line_rec := l_po_line_tbl(I);
2678
2679 SELECT csp_req_line_Details_s1.nextval
2680 INTO l_req_line_Dtl_id
2681 FROM dual;
2682
2683 csp_req_line_Details_pkg.Insert_Row(
2684 px_REQ_LINE_DETAIL_ID => l_Req_line_Dtl_id,
2685 p_REQUIREMENT_LINE_ID => l_line_rec.requirement_line_id,
2686 p_CREATED_BY => nvl(l_user_id, 1),
2687 p_CREATION_DATE => sysdate,
2688 p_LAST_UPDATED_BY => nvl(l_user_id, 1),
2689 p_LAST_UPDATE_DATE => sysdate,
2690 p_LAST_UPDATE_LOGIN => nvl(l_login_id, -1),
2691 p_SOURCE_TYPE => 'POREQ',
2692 p_SOURCE_ID => l_line_rec.requisition_line_id);
2693 END LOOP;
2694 END IF;
2695 END IF;
2696 END IF;
2697
2698 x_return_status := FND_API.G_RET_STS_SUCCESS;
2699 IF fnd_api.to_boolean(p_commit) THEN
2700 commit work;
2701 END IF;
2702 END If;
2703 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2704 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2705 'csp.plsql.CSP_PARTS_REQUIREMENT.create_order',
2706 'Returning from csp.plsql.CSP_PARTS_REQUIREMENT.create_order with return status = '
2707 || x_return_status);
2708 end if;
2709 EXCEPTION
2710 WHEN EXCP_USER_DEFINED THEN
2711 Rollback to create_order_PUB;
2712 x_return_status := FND_API.G_RET_STS_ERROR;
2713 fnd_msg_pub.count_and_get
2714 ( p_count => x_msg_count
2715 , p_data => x_msg_data);
2716
2717 WHEN FND_API.G_EXC_ERROR THEN
2718 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2719 P_API_NAME => L_API_NAME
2720 ,P_PKG_NAME => G_PKG_NAME
2721 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2722 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2723 ,X_MSG_COUNT => X_MSG_COUNT
2724 ,X_MSG_DATA => X_MSG_DATA
2725 ,X_RETURN_STATUS => X_RETURN_STATUS);
2726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2727 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2728 P_API_NAME => L_API_NAME
2729 ,P_PKG_NAME => G_PKG_NAME
2730 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2731 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2732 ,X_MSG_COUNT => X_MSG_COUNT
2733 ,X_MSG_DATA => X_MSG_DATA
2734 ,X_RETURN_STATUS => X_RETURN_STATUS);
2735 WHEN OTHERS THEN
2736 Rollback to create_order_PUB;
2737 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2738 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2739 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2740 FND_MSG_PUB.ADD;
2741 fnd_msg_pub.count_and_get
2742 ( p_count => x_msg_count
2743 , p_data => x_msg_data);
2744 x_return_status := FND_API.G_RET_STS_ERROR;
2745 END;
2746
2747 PROCEDURE TASK_ASSIGNMENT_POST_UPDATE(x_return_status out nocopy varchar2)IS
2748 l_task_assignment_id NUMBER;
2749 l_resource_id NUMBER;
2750 l_resource_type_code VARCHAR2(30);
2751 l_rqmt_header_id NUMBER;
2752 l_ship_to_location_id NUMBER;
2753 l_organization_id NUMBER;
2754 l_subinventory_code VARCHAR2(30);
2755
2756 CURSOR get_rqmt_Header IS
2757 SELECT requirement_header_id, address_type, destination_organization_id, destination_subinventory
2758 FROM csp_requirement_headers
2759 WHERE task_assignment_id = l_Task_Assignment_id;
2760
2761 CURSOR get_resource_location IS
2762 SELECT csp.ship_to_location_id
2763 FROM csp_rs_ship_to_addresses_all_v csp,
2764 hz_cust_acct_sites_All hz,
2765 cs_incidents_all_b cs,
2766 jtf_tasks_b jtb,
2767 jtf_task_assignments jta
2768 WHERE csp.cust_acct_site_id = hz.cust_acct_site_id
2769 AND csp.resource_id = jta.resource_id
2770 AND csp.resource_type = jta.resource_type_code
2771 AND primary_flag = 'Y'
2772 AND hz.org_id = cs.org_id
2773 AND jta.task_assignment_id = l_Task_Assignment_id
2774 AND jta.task_id = jtb.task_id
2775 AND jtb.source_object_type_code = 'SR'
2776 AND jtb.source_object_id = cs.incident_id
2777 AND csp.resource_id = l_resource_id
2778 AND csp.resource_type = l_resource_type_code;
2779
2780 CURSOR get_Resource_org_sub IS
2781 SELECT organization_id, subinventory_code
2782 FROM csp_inv_loc_assignments
2783 WHERE resource_id = l_resource_id
2784 AND resource_type = l_resource_type_code
2785 AND default_code = 'IN';
2786
2787 l_ship_to_type varchar2(1);
2788 l_dummy number;
2789 EXCP_USER_DEFINED EXCEPTION;
2790 l_source_type varchar2(10);
2791 l_source_id number;
2792 l_req_line_dtl_id number;
2793 x_msg_data varchar2(4000);
2794 x_msg_count number;
2795 l_assignment_status_id number := -999;
2796 l_assg_sts_flag varchar2(1);
2797 l_o_assg_sts_flag varchar2(1);
2798 l_oe_header_id number := -999;
2799 l_oe_order_num varchar2(100) := NULL;
2800 l_assignee_role VARCHAR2(10);
2801 l_is_cancelled varchar2(1);
2802 l_old_dest_OU number;
2803 l_new_dest_OU number;
2804 l_dest_org_id number;
2805 l_dest_subinv varchar2(30);
2806 l_res_same_org number;
2807 l_resource_name varchar2(1000);
2808 l_resource_type_name varchar2(1000);
2809 l_task_id number;
2810
2811
2812 CURSOR check_IO_booked IS
2813 select head.requirement_header_id, count(dtl.source_type)
2814 from
2815 csp_requirement_headers head,
2816 csp_requirement_lines line,
2817 csp_req_line_details dtl,
2818 oe_order_lines_all oel
2819 where
2820 head.task_assignment_id = l_Task_Assignment_id
2821 and head.requirement_header_id = line.requirement_header_id
2822 and line.requirement_line_id = dtl.requirement_line_id
2823 and dtl.source_type = 'IO'
2824 and dtl.source_id = oel.line_id
2825 and oel.booked_flag = 'Y'
2826 group by head.requirement_header_id;
2827
2828 CURSOR get_req_line_dtl(v_req_header_id number) is
2829 select dtl.req_line_detail_id, dtl.source_id, dtl.source_type
2830 from
2831 csp_requirement_headers head,
2832 csp_requirement_lines line,
2833 csp_req_line_details dtl
2834 where
2835 head.requirement_header_id = v_req_header_id
2836 and head.requirement_header_id = line.requirement_header_id
2837 and line.requirement_line_id = dtl.requirement_line_id;
2838
2839 cursor get_task_asg_sts_id is
2840 select assignment_status_id
2841 from jtf_task_assignments
2842 where task_assignment_id = l_task_assignment_id;
2843
2844 cursor get_order_header_id is
2845 SELECT oel.header_id
2846 FROM jtf_task_assignments jta,
2847 csp_requirement_headers ch,
2848 csp_requirement_lines cl,
2849 csp_req_line_details cld,
2850 oe_order_lines_all oel
2851 WHERE jta.task_assignment_id = l_task_assignment_id
2852 AND ch.task_assignment_id = jta.task_assignment_id
2853 AND ch.task_id = jta.task_id
2854 AND ch.requirement_header_id = cl.requirement_header_id
2855 AND cl.requirement_line_id = cld.requirement_line_id
2856 AND cld.source_type = 'IO'
2857 AND cld.source_id = oel.line_id
2858 AND oel.booked_flag = 'N'
2859 AND oel.cancelled_flag = 'N'
2860 AND oel.open_flag = 'Y';
2861
2862 l_sch_option CSP_SCH_INT_PVT.csp_sch_options_rec_typ;
2863 l_booking_start_date date;
2864 l_partial_line number;
2865 l_fl_rcvd_lines number;
2866 l_fl_rcvd_multi_source number;
2867 l_oth_req_line number;
2868 l_non_src_line number;
2869 l_shpd_lines number;
2870 l_tech_spec_pr number;
2871 l_ship_multi_src number;
2872 l_dest_ou number;
2873 l_rs_ou number;
2874 l_module varchar2(100) := 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE';
2875 l_requirement_header CSP_Requirement_Headers_PVT.REQUIREMENT_HEADER_Rec_Type;
2876 l_order_number varchar2(100);
2877 l_dest_org_name varchar2(240);
2878
2879 cursor c_parts_req (v_task_id number, v_task_assignment_id number) is
2880 select requirement_header_id
2881 from csp_requirement_headers
2882 where task_id = v_task_id
2883 and task_assignment_id = v_task_assignment_id;
2884
2885 cursor get_rs_ou(v_rs_type varchar2, v_rs_id number) is
2886 SELECT csp.organization_id
2887 FROM CSP_RS_SUBINVENTORIES_V csp
2888 WHERE csp.resource_type = v_rs_type
2889 AND csp.resource_id = v_rs_id
2890 AND csp.condition_type = 'G'
2891 AND csp.default_flag = 'Y';
2892
2893 cursor get_non_src_req is
2894 SELECT h.requirement_header_id,
2895 h.address_type
2896 FROM csp_requirement_headers h,
2897 csp_requirement_lines l
2898 WHERE h.task_id = l_task_id
2899 AND h.task_assignment_id = l_task_assignment_id
2900 AND l.requirement_header_id = h.requirement_header_id
2901 AND (SELECT COUNT(d.source_id)
2902 FROM csp_req_line_details d
2903 WHERE d.requirement_line_id = l.requirement_line_id) = 0;
2904
2905 BEGIN
2906
2907 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2908 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'Begin');
2909 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
2910 'l_task_assignment_id = ' || l_task_assignment_id
2911 || ', l_resource_id = ' || l_resource_id
2912 || ', l_resource_type_code = ' || l_resource_type_code
2913 || ', l_rqmt_header_id = ' || l_rqmt_header_id
2914 || ', l_ship_to_location_id = ' || l_ship_to_location_id
2915 || ', l_organization_id = ' || l_organization_id
2916 || ', l_subinventory_code = ' || l_subinventory_code);
2917 end if;
2918
2919 x_return_status := FND_API.G_RET_STS_SUCCESS;
2920 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
2921 l_resource_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_id;
2922 l_resource_type_code := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_type_code;
2923 l_assignee_role := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.assignee_role;
2924 l_task_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_id;
2925 l_booking_start_date := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.booking_start_date;
2926
2927 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2928 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
2929 'l_task_assignment_id = ' || l_task_assignment_id
2930 || ', l_task_id =' || l_task_id
2931 || ', l_resource_id = ' || l_resource_id
2932 || ', l_resource_type_code = ' || l_resource_type_code
2933 || ', l_booking_start_date = ' || to_char(l_booking_start_date, 'dd-mon-yyyy hh24:mi:ss')
2934 || ', l_assignee_role = ' || l_assignee_role
2935 || ', G_old_resource_id = ' || G_old_resource_id);
2936 end if;
2937
2938 l_oth_req_line := 0;
2939 SELECT COUNT(l.requirement_line_id)
2940 INTO l_oth_req_line
2941 FROM csp_requirement_headers h,
2942 csp_requirement_lines l
2943 WHERE h.task_id = l_task_id
2944 AND h.task_assignment_id = l_task_assignment_id
2945 AND h.requirement_header_id = l.requirement_header_id;
2946
2947 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2948 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
2949 'l_oth_req_line = ' || l_oth_req_line);
2950 end if;
2951
2952 if l_oth_req_line = 0 then
2953 return;
2954 end if;
2955
2956 if l_assignee_role = 'ASSIGNEE' then
2957 IF (l_Resource_id is not null and G_old_resource_id is not null and
2958 l_Resource_id <> G_old_resource_id)THEN
2959
2960 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2961 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
2962 'checking for re-assignment case...');
2963 end if;
2964
2965 OPEN get_Resource_org_sub;
2966 FETCH get_resource_org_sub INTO l_organization_id, l_subinventory_code;
2967 CLOSE get_resource_org_sub;
2968
2969 OPEN get_resource_location;
2970 FETCH get_resource_location INTO l_ship_to_location_id;
2971 CLOSE get_resource_location;
2972
2973 /*
2974 open check_IO_booked;
2975 loop
2976 fetch check_IO_booked into l_rqmt_header_id, l_dummy;
2977 exit when check_IO_booked%NOTFOUND;
2978
2979 if l_dummy > 0 then
2980 FND_MESSAGE.SET_NAME('CSP', 'CSP_TSK_ASGN_ORDER_MSG');
2981 FND_MESSAGE.SET_TOKEN('REQ_NUM', l_rqmt_header_id, TRUE);
2982 FND_MSG_PUB.ADD;
2983 raise EXCP_USER_DEFINED;
2984 end if;
2985 end loop;
2986 close check_IO_booked;
2987
2988 savepoint csp_correct_req_record;
2989 OPEN get_rqmt_header;
2990 LOOP
2991 FETCH get_rqmt_Header INTO l_rqmt_header_id, l_ship_to_type, l_dest_org_id, l_dest_subinv;
2992 EXIT WHEN get_rqmt_Header%NOTFOUND;
2993
2994 IF l_rqmt_header_id IS NOT NULL THEN
2995
2996 -- clean reservations and remove cancelled IO links
2997 open get_req_line_dtl(l_rqmt_header_id);
2998 loop
2999 fetch get_req_line_dtl into l_req_line_dtl_id, l_source_id, l_source_type;
3000 exit when get_req_line_dtl%NOTFOUND;
3001
3002 if l_source_type = 'IO' then
3003
3004
3005 select nvl(cancelled_flag, 'N')
3006 into l_is_cancelled
3007 from oe_order_lines_all
3008 where line_id = l_source_id;
3009
3010 if l_is_cancelled = 'N' then -- entered status
3011 -- check ship to type
3012 if l_ship_to_type = 'R' or l_ship_to_type = 'S' then
3013 -- raise an error message
3014 FND_MESSAGE.SET_NAME('CSP', 'CSP_TSK_ASGN_SHIP_MSG');
3015 FND_MESSAGE.SET_TOKEN('REQ_NUM', l_rqmt_header_id, TRUE);
3016 FND_MSG_PUB.ADD;
3017 raise EXCP_USER_DEFINED;
3018 end if;
3019
3020 -- check new and old INV org in same OU
3021 SELECT operating_unit
3022 INTO l_old_dest_OU
3023 FROM org_organization_Definitions
3024 WHERE organization_id = l_dest_org_id;
3025
3026 if l_organization_id is null or l_subinventory_code is null then
3027 SELECT NAME
3028 INTO l_resource_type_name
3029 FROM JTF_OBJECTS_VL
3030 WHERE OBJECT_CODE = l_resource_type_code;
3031
3032 l_resource_name := csp_pick_utils.get_object_name(l_resource_type_code, l_resource_id);
3033
3034 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_DEFAULT_SUBINV');
3035 FND_MESSAGE.SET_TOKEN('RESOURCE_TYPE', l_resource_type_name, TRUE);
3036 FND_MESSAGE.SET_TOKEN('RESOURCE_NAME', l_resource_name, TRUE);
3037 FND_MSG_PUB.ADD;
3038 raise EXCP_USER_DEFINED;
3039 end if;
3040
3041 SELECT operating_unit
3042 INTO l_new_dest_OU
3043 FROM org_organization_Definitions
3044 WHERE organization_id = l_organization_id;
3045
3046 if l_old_dest_OU <> l_new_dest_OU then
3047 -- raise an error message
3048 FND_MESSAGE.SET_NAME('CSP', 'CSP_TSK_ASGN_OU_MSG');
3049 FND_MESSAGE.SET_TOKEN('REQ_NUM', l_rqmt_header_id, TRUE);
3050 FND_MSG_PUB.ADD;
3051 raise EXCP_USER_DEFINED;
3052 end if;
3053
3054 UPDATE po_requisition_lines_all
3055 SET destination_subinventory = l_subinventory_code,
3056 destination_organization_id = l_organization_id
3057 WHERE requisition_line_id =
3058 (SELECT source_document_line_id
3059 FROM oe_order_lines_all
3060 WHERE line_id = l_source_id);
3061
3062 else
3063 CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_line_dtl_id);
3064 end if; -- if l_is_cancelled = 'N' then -- entered status
3065
3066
3067 elsif l_source_type = 'RES' then
3068
3069 -- check if the reservation is under same dest org then remove it
3070 -- otherwise leave it
3071
3072 SELECT count(*)
3073 into l_res_same_org
3074 FROM mtl_reservations
3075 WHERE reservation_id = l_source_id
3076 AND organization_id = l_dest_org_id
3077 AND subinventory_code = l_dest_subinv;
3078
3079 if l_res_same_org > 0 then
3080 CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_source_id, x_return_status, x_msg_data, x_msg_count);
3081 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3082 rollback to csp_correct_req_record;
3083 FND_MESSAGE.SET_NAME('CSP', 'CSP_MAN_NOT_CANCEL_RESERV');
3084 FND_MESSAGE.SET_TOKEN('REQ_NUM', l_rqmt_header_id, TRUE);
3085 FND_MSG_PUB.ADD;
3086 raise EXCP_USER_DEFINED;
3087 else
3088 CSP_REQ_LINE_DETAILS_PKG.Delete_Row(l_req_line_dtl_id);
3089 end if;
3090
3091 -- we need to source the part for the new tech
3092 l_sch_option.resource_id := l_resource_id;
3093 l_sch_option.resource_type := l_resource_type_code;
3094 l_sch_option.start_time := l_booking_start_date;
3095 l_sch_option.transfer_cost := 9999999; --- this should be a high number to avoid cost check in choose option
3096
3097 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3098 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3099 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3100 'before calling CSP_SCH_INT_PVT.CHOOSE_OPTION...');
3101 end if;
3102 CSP_SCH_INT_PVT.CHOOSE_OPTION(
3103 p_api_version_number => 1.0,
3104 p_task_id => l_task_id,
3105 p_task_assignment_id => l_task_assignment_id,
3106 p_likelihood => 0,
3107 p_mandatory => true,
3108 p_trunk => true,
3109 p_warehouse => false,
3110 p_options => l_sch_option,
3111 x_return_status => x_return_status,
3112 x_msg_data => x_msg_data,
3113 x_msg_count => x_msg_count
3114 );
3115 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3116 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3117 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3118 'after calling CSP_SCH_INT_PVT.CHOOSE_OPTION...x_return_status=' || x_return_status);
3119 end if;
3120
3121 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3122 -- now try to find the part in warehouse
3123 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3124 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3125 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3126 'before calling CSP_SCH_INT_PVT.CHOOSE_OPTION...');
3127 end if;
3128 CSP_SCH_INT_PVT.CHOOSE_OPTION(
3129 p_api_version_number => 1.0,
3130 p_task_id => l_task_id,
3131 p_task_assignment_id => l_task_assignment_id,
3132 p_likelihood => 0,
3133 p_mandatory => true,
3134 p_trunk => false,
3135 p_warehouse => true,
3136 p_options => l_sch_option,
3137 x_return_status => x_return_status,
3138 x_msg_data => x_msg_data,
3139 x_msg_count => x_msg_count
3140 );
3141 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3142 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3143 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3144 'after calling CSP_SCH_INT_PVT.CHOOSE_OPTION...x_return_status=' || x_return_status);
3145 end if;
3146 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3147 rollback to csp_correct_req_record;
3148 raise EXCP_USER_DEFINED;
3149 else
3150 return; -- early exit
3151 end if;
3152 else
3153 return; -- early exit
3154 end if;
3155 end if;
3156
3157 end if; -- if l_source_type = 'IO' then
3158
3159 end loop;
3160 close get_req_line_dtl;
3161
3162 UPDATE csp_requirement_headers
3163 SET task_assignment_id = l_task_assignment_id,
3164 ship_to_location_id = decode(l_ship_to_type, 'C', ship_to_location_id, 'T', ship_to_location_id, l_ship_to_location_id),
3165 destination_organization_id = l_organization_id,
3166 destination_subinventory = l_subinventory_code,
3167 resource_type = l_resource_type_code,
3168 resource_id = l_resource_id,
3169 address_type = decode(l_ship_to_type, 'S', 'R', l_ship_to_type)
3170 WHERE requirement_header_id = l_rqmt_header_id;
3171 END IF;
3172 END LOOP;
3173 CLOSE get_rqmt_header;
3174 */
3175
3176 -- check if the requirement is without any details
3177 l_rqmt_header_id := 0;
3178 open get_non_src_req;
3179 fetch get_non_src_req into l_rqmt_header_id, l_ship_to_type;
3180 close get_non_src_req;
3181
3182 if l_rqmt_header_id <> 0 then
3183 UPDATE csp_requirement_headers
3184 SET task_assignment_id = l_task_assignment_id,
3185 ship_to_location_id = decode(l_ship_to_type, 'C', ship_to_location_id, 'T', ship_to_location_id, l_ship_to_location_id),
3186 destination_organization_id = l_organization_id,
3187 destination_subinventory = l_subinventory_code,
3188 resource_type = l_resource_type_code,
3189 resource_id = l_resource_id,
3190 address_type = decode(l_ship_to_type, 'S', 'R', l_ship_to_type),
3191 ship_to_contact_id = decode(l_ship_to_type, 'C', ship_to_contact_id, 'T', ship_to_contact_id, null)
3192 WHERE requirement_header_id = l_rqmt_header_id;
3193 return;
3194 end if;
3195
3196 -- check if we need to source the item and schedule dates are already in past
3197 -- then stop reassignment
3198 if l_booking_start_date is null or l_booking_start_date <= sysdate then
3199 x_return_status := FND_API.G_RET_STS_ERROR;
3200 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_DATE_PST_ASSGN');
3201 FND_MSG_PUB.ADD;
3202 fnd_msg_pub.count_and_get
3203 ( p_count => x_msg_count
3204 , p_data => x_msg_data);
3205 return;
3206 end if;
3207
3208
3209 -- first check for the partial received order line status
3210 l_partial_line := 0;
3211 SELECT count(l.requirement_line_id)
3212 into l_partial_line
3213 FROM csp_requirement_headers h,
3214 csp_requirement_lines l,
3215 csp_req_line_details d,
3216 oe_order_lines_all oola
3217 WHERE h.task_id = l_task_id
3218 AND h.task_assignment_id = l_Task_Assignment_id
3219 AND h.requirement_header_id = l.requirement_header_id
3220 AND l.requirement_line_id = d.requirement_line_id
3221 and d.source_type = 'IO'
3222 AND d.source_id = oola.line_id
3223 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'PARTIALLY RECEIVED';
3224
3225 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3226 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3227 'l_partial_line = ' || l_partial_line);
3228 end if;
3229
3230 if l_partial_line > 0 then
3231 x_return_status := FND_API.G_RET_STS_ERROR;
3232 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3233 FND_MSG_PUB.ADD;
3234 fnd_msg_pub.count_and_get
3235 ( p_count => x_msg_count
3236 , p_data => x_msg_data);
3237 return;
3238 end if;
3239
3240 -- bug # 14182971
3241 -- block rescheduling if part is already received for Tech or Special
3242 -- ship to address type
3243 l_fl_rcvd_lines := 0;
3244 SELECT COUNT(l.requirement_line_id)
3245 into l_fl_rcvd_lines
3246 FROM csp_requirement_headers h,
3247 csp_requirement_lines l,
3248 csp_req_line_details dio,
3249 csp_req_line_details dres,
3250 oe_order_lines_all oola,
3251 mtl_reservations mr
3252 WHERE h.task_id = l_task_id
3253 AND h.task_assignment_id = l_Task_Assignment_id
3254 AND h.address_type IN ('R', 'S')
3255 AND h.requirement_header_id = l.requirement_header_id
3256 AND l.requirement_line_id = dio.requirement_line_id
3257 AND dio.source_type = 'IO'
3258 and dio.source_id = oola.line_id
3259 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
3260 AND dio.requirement_line_id = dres.requirement_line_id
3261 AND oola.inventory_item_id = mr.inventory_item_id
3262 AND oola.ordered_quantity = mr.reservation_quantity
3263 AND dres.source_type = 'RES'
3264 AND dres.source_id = mr.reservation_id;
3265
3266 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3267 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3268 'l_fl_rcvd_lines (R or S) = ' || l_fl_rcvd_lines);
3269 end if;
3270
3271 if l_fl_rcvd_lines > 0 then
3272 SELECT ooha.order_number
3273 into l_order_number
3274 FROM csp_requirement_headers h,
3275 csp_requirement_lines l,
3276 csp_req_line_details dio,
3277 oe_order_lines_all oola,
3278 oe_order_headers_all ooha
3279 WHERE h.task_id = l_task_id
3280 AND h.task_assignment_id = l_Task_Assignment_id
3281 AND h.address_type IN ('R', 'S')
3282 AND h.requirement_header_id = l.requirement_header_id
3283 AND l.requirement_line_id = dio.requirement_line_id
3284 AND dio.source_type = 'IO'
3285 and dio.source_id = oola.line_id
3286 and oola.header_id = ooha.header_id
3287 AND rownum = 1;
3288
3289 x_return_status := FND_API.G_RET_STS_ERROR;
3290 FND_MESSAGE.SET_NAME('CSP', 'CSP_TSK_ASSGN_NO_IO_CAN');
3291 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER',l_order_number, FALSE);
3292 FND_MSG_PUB.ADD;
3293 fnd_msg_pub.count_and_get
3294 ( p_count => x_msg_count
3295 , p_data => x_msg_data);
3296 return;
3297 end if;
3298
3299 -- for fully rcvd case
3300 l_fl_rcvd_lines := 0;
3301 SELECT COUNT(l.requirement_line_id)
3302 into l_fl_rcvd_lines
3303 FROM csp_requirement_headers h,
3304 csp_requirement_lines l,
3305 csp_req_line_details dio,
3306 csp_req_line_details dres,
3307 oe_order_lines_all oola,
3308 mtl_reservations mr
3309 WHERE h.task_id = l_task_id
3310 AND h.task_assignment_id = l_Task_Assignment_id
3311 AND h.address_type IN ('T', 'C', 'P')
3312 AND h.requirement_header_id = l.requirement_header_id
3313 AND l.requirement_line_id = dio.requirement_line_id
3314 AND dio.source_type = 'IO'
3315 AND dio.source_id = oola.line_id
3316 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
3317 AND dio.requirement_line_id = dres.requirement_line_id
3318 AND oola.inventory_item_id = mr.inventory_item_id
3319 AND oola.ordered_quantity = mr.reservation_quantity
3320 AND dres.source_type = 'RES'
3321 AND dres.source_id = mr.reservation_id;
3322
3323 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3324 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3325 'l_fl_rcvd_lines = ' || l_fl_rcvd_lines);
3326 end if;
3327
3328 if l_fl_rcvd_lines > 0 then
3329
3330 -- get fully received lines for task and customer ship to case
3331 l_fl_rcvd_multi_source := 0;
3332 SELECT COUNT(l.requirement_line_id)
3333 into l_fl_rcvd_multi_source
3334 FROM csp_requirement_headers h,
3335 csp_requirement_lines l,
3336 csp_req_line_details dio,
3337 csp_req_line_details dres,
3338 csp_req_line_details dother,
3339 oe_order_lines_all oola,
3340 mtl_reservations mr
3341 WHERE h.task_id = l_task_id
3342 AND h.task_assignment_id = l_Task_Assignment_id
3343 AND h.address_type IN ('T', 'C', 'P')
3344 AND h.requirement_header_id = l.requirement_header_id
3345 AND l.requirement_line_id = dio.requirement_line_id
3346 AND dio.source_type = 'IO'
3347 AND dio.source_id = oola.line_id
3348 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
3349 AND dio.requirement_line_id = dres.requirement_line_id
3350 AND oola.inventory_item_id = mr.inventory_item_id
3351 AND oola.ordered_quantity = mr.reservation_quantity
3352 AND dres.source_type = 'RES'
3353 AND dres.source_id = mr.reservation_id
3354 AND l.requirement_line_id = dother.requirement_line_id
3355 AND dother.source_id <> dio.source_id
3356 AND dother.source_id <> dres.source_id;
3357
3358 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3359 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3360 'l_fl_rcvd_multi_source = ' || l_fl_rcvd_multi_source);
3361 end if;
3362
3363 if l_fl_rcvd_multi_source > 0 then
3364 x_return_status := FND_API.G_RET_STS_ERROR;
3365 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3366 FND_MSG_PUB.ADD;
3367 fnd_msg_pub.count_and_get
3368 ( p_count => x_msg_count
3369 , p_data => x_msg_data);
3370 return;
3371 end if;
3372
3373 -- check if any other req line sourced
3374 l_oth_req_line := 0;
3375 SELECT COUNT(requirement_line_id)
3376 into l_oth_req_line
3377 FROM
3378 (SELECT l.requirement_line_id
3379 FROM csp_requirement_headers h,
3380 csp_requirement_lines l,
3381 csp_req_line_details d
3382 WHERE h.task_id = l_task_id
3383 AND h.task_assignment_id = l_Task_Assignment_id
3384 AND h.requirement_header_id = l.requirement_header_id
3385 AND h.address_type IN ('T', 'C', 'P')
3386 AND l.requirement_line_id = d.requirement_line_id
3387 MINUS
3388 SELECT l.requirement_line_id
3389 FROM csp_requirement_headers h,
3390 csp_requirement_lines l,
3391 csp_req_line_details dio,
3392 csp_req_line_details dres,
3393 oe_order_lines_all oola,
3394 mtl_reservations mr
3395 WHERE h.task_id = l_task_id
3396 AND h.task_assignment_id = l_Task_Assignment_id
3397 AND h.address_type IN ('T', 'C', 'P')
3398 AND h.requirement_header_id = l.requirement_header_id
3399 AND l.requirement_line_id = dio.requirement_line_id
3400 AND dio.source_type = 'IO'
3401 AND dio.source_id = oola.line_id
3402 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
3403 AND dio.requirement_line_id = dres.requirement_line_id
3404 AND oola.inventory_item_id = mr.inventory_item_id
3405 AND oola.ordered_quantity = mr.reservation_quantity
3406 AND dres.source_type = 'RES'
3407 AND dres.source_id = mr.reservation_id
3408 );
3409
3410 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3412 'l_oth_req_line = ' || l_oth_req_line);
3413 end if;
3414
3415 if l_oth_req_line > 0 then
3416 x_return_status := FND_API.G_RET_STS_ERROR;
3417 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3418 FND_MSG_PUB.ADD;
3419 fnd_msg_pub.count_and_get
3420 ( p_count => x_msg_count
3421 , p_data => x_msg_data);
3422 return;
3423 end if;
3424
3425 --check if there is any not sourced line
3426 l_non_src_line := 0;
3427 SELECT COUNT(requirement_line_id)
3428 into l_non_src_line
3429 FROM
3430 (SELECT l.requirement_line_id
3431 FROM csp_requirement_headers h,
3432 csp_requirement_lines l
3433 WHERE h.task_id = l_task_id
3434 AND h.task_assignment_id = l_Task_Assignment_id
3435 AND h.requirement_header_id = l.requirement_header_id
3436 AND h.address_type IN ('T', 'C', 'P')
3437 AND (SELECT COUNT (d.requirement_line_id)
3438 FROM csp_req_line_details d
3439 WHERE d.requirement_line_id = l.requirement_line_id) = 0
3440 MINUS
3441 SELECT l.requirement_line_id
3442 FROM csp_requirement_headers h,
3443 csp_requirement_lines l,
3444 csp_req_line_details dio,
3445 csp_req_line_details dres,
3446 oe_order_lines_all oola,
3447 mtl_reservations mr
3448 WHERE h.task_id = l_task_id
3449 AND h.task_assignment_id = l_Task_Assignment_id
3450 AND h.address_type IN ('T', 'C', 'P')
3451 AND h.requirement_header_id = l.requirement_header_id
3452 AND l.requirement_line_id = dio.requirement_line_id
3453 AND dio.source_type = 'IO'
3454 AND dio.source_id = oola.line_id
3455 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'FULLY RECEIVED'
3456 AND dio.requirement_line_id = dres.requirement_line_id
3457 AND oola.inventory_item_id = mr.inventory_item_id
3458 AND oola.ordered_quantity = mr.reservation_quantity
3459 AND dres.source_type = 'RES'
3460 AND dres.source_id = mr.reservation_id
3461 );
3462
3463 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3464 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3465 'l_non_src_line = ' || l_non_src_line);
3466 end if;
3467
3468 if l_non_src_line = 0 then
3469 -- if we are here that means all the lines source have been
3470 -- already recived
3471 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3472 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3473 'before calling move_parts_on_reassign...');
3474 end if;
3475 CSP_SCH_INT_PVT.move_parts_on_reassign(
3476 p_task_id => l_task_id,
3477 p_task_asgn_id => l_Task_Assignment_id,
3478 p_new_task_asgn_id => l_Task_Assignment_id,
3479 p_new_need_by_date => l_booking_start_date,
3480 p_new_resource_id => l_resource_id,
3481 p_new_resource_type => l_resource_type_code,
3482 x_return_status => x_return_status,
3483 x_msg_count => x_msg_count,
3484 x_msg_data => x_msg_data
3485 );
3486 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3487 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3488 'after calling move_parts_on_reassign...x_return_status=' || x_return_status);
3489 end if;
3490 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3491 x_return_status := FND_API.G_RET_STS_ERROR;
3492 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3493 FND_MSG_PUB.ADD;
3494 fnd_msg_pub.count_and_get
3495 ( p_count => x_msg_count
3496 , p_data => x_msg_data);
3497 end if;
3498 return;
3499 end if;
3500 end if;
3501
3502 -- now check for shipped lines
3503 l_shpd_lines := 0;
3504 SELECT COUNT(l.requirement_line_id)
3505 into l_shpd_lines
3506 FROM csp_requirement_headers h,
3507 csp_requirement_lines l,
3508 csp_req_line_details d,
3509 oe_order_lines_all oola
3510 WHERE h.task_id = l_task_id
3511 AND h.task_assignment_id = l_Task_Assignment_id
3512 AND h.requirement_header_id = l.requirement_header_id
3513 AND l.requirement_line_id = d.requirement_line_id
3514 AND d.source_type = 'IO'
3515 AND d.source_id = oola.line_id
3516 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED');
3517
3518 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3519 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3520 'l_shpd_lines = ' || l_shpd_lines);
3521 end if;
3522
3523 if l_shpd_lines > 0 then
3524 l_tech_spec_pr := 0;
3525 SELECT count(h.address_type)
3526 into l_tech_spec_pr
3527 FROM csp_requirement_headers h
3528 WHERE h.task_id = l_task_id
3529 AND h.task_assignment_id = l_Task_Assignment_id
3530 AND h.address_type IN ('R', 'S');
3531
3532 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3533 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3534 'l_tech_spec_pr = ' || l_tech_spec_pr);
3535 end if;
3536
3537 if l_tech_spec_pr > 0 then
3538 SELECT ooha.order_number
3539 INTO l_order_number
3540 FROM csp_requirement_headers h,
3541 csp_requirement_lines l,
3542 csp_req_line_details d,
3543 oe_order_lines_all oola,
3544 oe_order_headers_all ooha
3545 WHERE h.task_assignment_id = l_Task_Assignment_id
3546 AND h.task_id = l_task_id
3547 AND h.requirement_header_id = l.requirement_header_id
3548 AND l.requirement_line_id = d.requirement_line_id
3549 AND d.source_type = 'IO'
3550 AND d.source_id = oola.line_id
3551 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) IN ('SHIPPED', 'EXPECTED')
3552 AND oola.header_id = ooha.header_id
3553 AND rownum = 1;
3554
3555 x_return_status := FND_API.G_RET_STS_ERROR;
3556 FND_MESSAGE.SET_NAME('CSP', 'CSP_TSK_ASSGN_NO_IO_CAN');
3557 FND_MESSAGE.SET_TOKEN('ORDER_NUMBER',l_order_number, FALSE);
3558 FND_MSG_PUB.ADD;
3559 fnd_msg_pub.count_and_get
3560 ( p_count => x_msg_count
3561 , p_data => x_msg_data);
3562 return;
3563 else
3564 -- now check for same line multiple IO case
3565 l_ship_multi_src := 0;
3566 SELECT COUNT(l.requirement_line_id)
3567 into l_ship_multi_src
3568 FROM csp_requirement_headers h,
3569 csp_requirement_lines l,
3570 csp_req_line_details d,
3571 csp_req_line_details dother,
3572 oe_order_lines_all oola
3573 WHERE h.task_id = l_task_id
3574 AND h.task_assignment_id = l_Task_Assignment_id
3575 AND h.requirement_header_id = l.requirement_header_id
3576 AND l.requirement_line_id = d.requirement_line_id
3577 AND h.address_type IN ('T', 'C', 'P')
3578 AND d.source_type = 'IO'
3579 AND d.source_id = oola.line_id
3580 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED')
3581 AND dother.requirement_line_id = l.requirement_line_id
3582 AND dother.source_id <> d.source_id;
3583
3584 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3585 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3586 'l_ship_multi_src = ' || l_ship_multi_src);
3587 end if;
3588
3589 if l_ship_multi_src > 0 then
3590 x_return_status := FND_API.G_RET_STS_ERROR;
3591 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3592 FND_MSG_PUB.ADD;
3593 fnd_msg_pub.count_and_get
3594 ( p_count => x_msg_count
3595 , p_data => x_msg_data);
3596 return;
3597 end if;
3598
3599 -- check of other source line
3600 l_oth_req_line := 0;
3601 SELECT COUNT(requirement_line_id)
3602 into l_oth_req_line
3603 FROM
3604 (SELECT l.requirement_line_id
3605 FROM csp_requirement_headers h,
3606 csp_requirement_lines l,
3607 csp_req_line_details d
3608 WHERE h.task_id = l_task_id
3609 AND h.task_assignment_id = l_Task_Assignment_id
3610 AND h.requirement_header_id = l.requirement_header_id
3611 AND l.requirement_line_id = d.requirement_line_id
3612 MINUS
3613 SELECT l.requirement_line_id
3614 FROM csp_requirement_headers h,
3615 csp_requirement_lines l,
3616 csp_req_line_details d,
3617 oe_order_lines_all oola
3618 WHERE h.task_id = l_task_id
3619 AND h.task_assignment_id = l_Task_Assignment_id
3620 AND h.requirement_header_id = l.requirement_header_id
3621 AND l.requirement_line_id = d.requirement_line_id
3622 AND d.source_type = 'IO'
3623 AND d.source_id = oola.line_id
3624 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED')
3625 );
3626
3627 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3628 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3629 'l_oth_req_line = ' || l_oth_req_line);
3630 end if;
3631
3632 if l_oth_req_line > 0 then
3633 x_return_status := FND_API.G_RET_STS_ERROR;
3634 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3635 FND_MSG_PUB.ADD;
3636 fnd_msg_pub.count_and_get
3637 ( p_count => x_msg_count
3638 , p_data => x_msg_data);
3639 return;
3640 end if;
3641
3642 -- now check for non sourced lines
3643 l_non_src_line := 0;
3644 SELECT COUNT(requirement_line_id)
3645 into l_non_src_line
3646 FROM
3647 (SELECT l.requirement_line_id
3648 FROM csp_requirement_headers h,
3649 csp_requirement_lines l
3650 WHERE h.task_id = l_task_id
3651 AND h.task_assignment_id = l_Task_Assignment_id
3652 AND h.requirement_header_id = l.requirement_header_id
3653 AND (SELECT COUNT (d.requirement_line_id)
3654 FROM csp_req_line_details d
3655 WHERE d.requirement_line_id = l.requirement_line_id) = 0
3656 MINUS
3657 SELECT l.requirement_line_id
3658 FROM csp_requirement_headers h,
3659 csp_requirement_lines l,
3660 csp_req_line_details d,
3661 oe_order_lines_all oola
3662 WHERE h.task_id = l_task_id
3663 AND h.task_assignment_id = l_Task_Assignment_id
3664 AND h.requirement_header_id = l.requirement_header_id
3665 AND l.requirement_line_id = d.requirement_line_id
3666 AND d.source_type = 'IO'
3667 AND d.source_id = oola.line_id
3668 AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED')
3669 );
3670
3671 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3672 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3673 'l_non_src_line = ' || l_non_src_line);
3674 end if;
3675
3676 if l_non_src_line = 0 then
3677 l_dest_ou := -999;
3678 SELECT ch.destination_organization_id, h.name
3679 into l_dest_ou, l_dest_org_name
3680 FROM csp_requirement_headers ch, HR_ALL_ORGANIZATION_UNITS h
3681 WHERE ch.task_id = l_task_id
3682 AND ch.task_assignment_id = l_Task_Assignment_id
3683 AND ch.destination_organization_id = h.organization_id
3684 AND rownum = 1;
3685
3686 l_rs_ou := -999;
3687 open get_rs_ou(l_resource_type_code, l_resource_id);
3688 fetch get_rs_ou into l_rs_ou;
3689 close get_rs_ou;
3690 if(l_rs_ou <> -999 and l_rs_ou = l_dest_ou) then
3691 for cr in c_parts_req(l_task_id, l_Task_Assignment_id)
3692 loop
3693 l_requirement_header := CSP_Requirement_headers_PVT.G_MISS_REQUIREMENT_HEADER_REC;
3694 l_requirement_header.requirement_header_id := cr.requirement_header_id;
3695 l_requirement_header.last_update_date := sysdate;
3696 l_requirement_header.destination_organization_id := l_organization_id;
3697 l_requirement_header.destination_subinventory := l_subinventory_code;
3698 l_requirement_header.resource_type := l_resource_type_code;
3699 l_requirement_header.resource_id := l_resource_id;
3700
3701 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3702 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3703 'before calling Update_requirement_headers...');
3704 end if;
3705 CSP_Requirement_Headers_PVT.Update_requirement_headers(
3706 P_Api_Version_Number => 1.0,
3707 P_Init_Msg_List => FND_API.G_FALSE,
3708 P_Commit => FND_API.G_FALSE,
3709 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3710 P_REQUIREMENT_HEADER_Rec => l_requirement_header,
3711 X_Return_Status => x_return_status,
3712 X_Msg_Count => x_msg_count,
3713 x_msg_data => x_msg_data
3714 );
3715 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3716 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3717 'after calling Update_requirement_headers...x_return_status=' || x_return_status);
3718 end if;
3719
3720 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3721 x_return_status := FND_API.G_RET_STS_ERROR;
3722 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3723 FND_MSG_PUB.ADD;
3724 fnd_msg_pub.count_and_get
3725 ( p_count => x_msg_count
3726 , p_data => x_msg_data);
3727 return;
3728 end if;
3729 end loop;
3730 else
3731 x_return_status := FND_API.G_RET_STS_ERROR;
3732 FND_MESSAGE.SET_NAME('CSP', 'CSP_RASG_IO_UPD_OU_ERR');
3733 FND_MESSAGE.SET_TOKEN('ORG_NAME', l_dest_org_name, TRUE);
3734 FND_MSG_PUB.ADD;
3735 fnd_msg_pub.count_and_get
3736 ( p_count => x_msg_count
3737 , p_data => x_msg_data);
3738 return;
3739 end if;
3740 return;
3741 end if;
3742 end if;
3743 end if;
3744
3745 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3747 'before making call to CLEAN_REQUIREMENT...');
3748 end if;
3749
3750 CSP_SCH_INT_PVT.CLEAN_REQUIREMENT(
3751 p_api_version_number => 1.0,
3752 p_task_assignment_id => l_Task_Assignment_id,
3753 x_return_status => x_return_status,
3754 x_msg_count => x_msg_count,
3755 x_msg_data => x_msg_data
3756 );
3757
3758 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3759 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3760 'after making call to CLEAN_REQUIREMENT... x_return_status = ' || x_return_status);
3761 end if;
3762
3763 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3764 -- we need to source the part for the new tech
3765 l_sch_option.resource_id := l_resource_id;
3766 l_sch_option.resource_type := l_resource_type_code;
3767 l_sch_option.start_time := l_booking_start_date;
3768 l_sch_option.transfer_cost := 9999999; --- this should be a high number to avoid cost check in choose option
3769
3770 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3771 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3772 'before calling CSP_SCH_INT_PVT.CHOOSE_OPTION...');
3773 end if;
3774 CSP_SCH_INT_PVT.CHOOSE_OPTION(
3775 p_api_version_number => 1.0,
3776 p_task_id => l_task_id,
3777 p_task_assignment_id => l_task_assignment_id,
3778 p_likelihood => 0,
3779 p_mandatory => true,
3780 p_trunk => true,
3781 p_warehouse => false,
3782 p_options => l_sch_option,
3783 x_return_status => x_return_status,
3784 x_msg_data => x_msg_data,
3785 x_msg_count => x_msg_count
3786 );
3787 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3788 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3789 'after calling CSP_SCH_INT_PVT.CHOOSE_OPTION...x_return_status=' || x_return_status);
3790 end if;
3791
3792 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3793 -- now try to find the part in warehouse
3794 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3795 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3796 'before calling CSP_SCH_INT_PVT.CHOOSE_OPTION...');
3797 end if;
3798 CSP_SCH_INT_PVT.CHOOSE_OPTION(
3799 p_api_version_number => 1.0,
3800 p_task_id => l_task_id,
3801 p_task_assignment_id => l_task_assignment_id,
3802 p_likelihood => 0,
3803 p_mandatory => true,
3804 p_trunk => false,
3805 p_warehouse => true,
3806 p_options => l_sch_option,
3807 x_return_status => x_return_status,
3808 x_msg_data => x_msg_data,
3809 x_msg_count => x_msg_count
3810 );
3811 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3812 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3813 'after calling CSP_SCH_INT_PVT.CHOOSE_OPTION...x_return_status=' || x_return_status);
3814 end if;
3815 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3816 FND_MSG_PUB.initialize;
3817 l_resource_name := csp_pick_utils.get_object_name(l_resource_type_code, l_resource_id);
3818 FND_MESSAGE.SET_NAME('CSP', 'CSP_TSK_ASSGN_NO_SRC');
3819 FND_MESSAGE.SET_TOKEN('RESOURCE_NAME',l_resource_name, FALSE);
3820 FND_MSG_PUB.ADD;
3821 fnd_msg_pub.count_and_get
3822 ( p_count => x_msg_count
3823 , p_data => x_msg_data);
3824 end if;
3825 end if;
3826 elsif x_return_status = 'C' then
3827 l_dest_ou := -999;
3828 SELECT ch.destination_organization_id, h.name
3829 into l_dest_ou, l_dest_org_name
3830 FROM csp_requirement_headers ch, HR_ALL_ORGANIZATION_UNITS h
3831 WHERE ch.task_id = l_task_id
3832 AND ch.task_assignment_id = l_Task_Assignment_id
3833 AND ch.destination_organization_id = h.organization_id
3834 AND rownum = 1;
3835
3836 l_rs_ou := -999;
3837 open get_rs_ou(l_resource_type_code, l_resource_id);
3838 fetch get_rs_ou into l_rs_ou;
3839 close get_rs_ou;
3840 if(l_rs_ou <> -999 and l_rs_ou = l_dest_ou) then
3841 for cr in c_parts_req(l_task_id, l_Task_Assignment_id)
3842 loop
3843 l_requirement_header := CSP_Requirement_headers_PVT.G_MISS_REQUIREMENT_HEADER_REC;
3844 l_requirement_header.requirement_header_id := cr.requirement_header_id;
3845 l_requirement_header.last_update_date := sysdate;
3846 l_requirement_header.destination_organization_id := l_organization_id;
3847 l_requirement_header.destination_subinventory := l_subinventory_code;
3848 l_requirement_header.resource_type := l_resource_type_code;
3849 l_requirement_header.resource_id := l_resource_id;
3850
3851 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3852 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3853 'before calling Update_requirement_headers...');
3854 end if;
3855 CSP_Requirement_Headers_PVT.Update_requirement_headers(
3856 P_Api_Version_Number => 1.0,
3857 P_Init_Msg_List => FND_API.G_FALSE,
3858 P_Commit => FND_API.G_FALSE,
3859 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3860 P_REQUIREMENT_HEADER_Rec => l_requirement_header,
3861 X_Return_Status => x_return_status,
3862 X_Msg_Count => x_msg_count,
3863 x_msg_data => x_msg_data
3864 );
3865 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3866 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
3867 'after calling Update_requirement_headers...x_return_status=' || x_return_status);
3868 end if;
3869
3870 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3871 x_return_status := FND_API.G_RET_STS_ERROR;
3872 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3873 FND_MSG_PUB.ADD;
3874 fnd_msg_pub.count_and_get
3875 ( p_count => x_msg_count
3876 , p_data => x_msg_data);
3877 return;
3878 end if;
3879 end loop;
3880 else
3881 x_return_status := FND_API.G_RET_STS_ERROR;
3882 FND_MESSAGE.SET_NAME('CSP', 'CSP_RASG_IO_UPD_OU_ERR');
3883 FND_MESSAGE.SET_TOKEN('ORG_NAME', l_dest_org_name, TRUE);
3884 FND_MSG_PUB.ADD;
3885 fnd_msg_pub.count_and_get
3886 ( p_count => x_msg_count
3887 , p_data => x_msg_data);
3888 return;
3889 end if;
3890 return;
3891 else
3892 x_return_status := FND_API.G_RET_STS_ERROR;
3893 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
3894 FND_MSG_PUB.ADD;
3895 fnd_msg_pub.count_and_get
3896 ( p_count => x_msg_count
3897 , p_data => x_msg_data);
3898 END IF;
3899 return;
3900 -- this else part is to book the order
3901 else
3902
3903 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3904 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3905 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3906 'checking for task assignment status change...');
3907 end if;
3908
3909 open get_task_asg_sts_id;
3910 fetch get_task_asg_sts_id into l_assignment_status_id;
3911 close get_task_asg_sts_id;
3912
3913 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3914 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3915 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3916 'l_assignment_status_id = ' || l_assignment_status_id);
3917 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3918 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3919 'g_old_tsk_asgn_sts_id = ' || g_old_tsk_asgn_sts_id);
3920 end if;
3921
3922 SELECT nvl(cancelled_flag, 'N')
3923 INTO l_o_assg_sts_flag
3924 FROM JTF_TASK_STATUSES_B
3925 WHERE task_status_id = g_old_tsk_asgn_sts_id;
3926
3927 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3928 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3929 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3930 'l_o_assg_sts_flag = ' || l_o_assg_sts_flag);
3931 end if;
3932
3933 if l_o_assg_sts_flag = 'Y' then
3934
3935 SELECT decode(count(*), 1, 'Y', 'N')
3936 INTO l_assg_sts_flag
3937 FROM JTF_TASK_STATUSES_B
3938 WHERE task_status_id = l_assignment_status_id
3939 and (nvl(assigned_flag, 'N') = 'Y'
3940 or nvl(accepted_flag, 'N') = 'Y' or nvl(planned_flag, 'N') = 'Y');
3941
3942 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3943 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3944 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3945 'l_assg_sts_flag = ' || l_assg_sts_flag);
3946 end if;
3947
3948 if l_assg_sts_flag = 'Y' then
3949 TASK_ASSIGNMENT_POST_INSERT(x_return_status => x_return_status);
3950
3951 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3952 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3953 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3954 'after calling TASK_ASSIGNMENT_POST_INSERT.... x_return_status = ' || x_return_status);
3955 end if;
3956
3957 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3958 return;
3959 end if;
3960
3961 end if;
3962
3963 end if;
3964
3965
3966 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3967 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3968 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3969 'checking for accepted status change');
3970 end if;
3971
3972 SELECT nvl(accepted_flag, 'N')
3973 INTO l_assg_sts_flag
3974 FROM JTF_TASK_STATUSES_B
3975 WHERE task_status_id = l_assignment_status_id;
3976
3977 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3978 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3979 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3980 'l_assg_sts_flag = ' || l_assg_sts_flag);
3981 end if;
3982
3983 if l_assg_sts_flag = 'Y' then
3984
3985 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3986 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3987 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3988 'fetching entered status order header_id...');
3989 end if;
3990
3991 savepoint csp_book_order;
3992 for cr in get_order_header_id loop
3993
3994 l_oe_header_id := cr.header_id;
3995
3996 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3997 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3998 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
3999 'l_oe_header_id = ' || l_oe_header_id);
4000 end if;
4001
4002 if l_oe_header_id is not null then
4003
4004 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4005 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4006 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4007 'before calling CSP_PARTS_ORDER.book_order ...');
4008 end if;
4009
4010 CSP_PARTS_ORDER.book_order(
4011 p_oe_header_id => l_oe_header_id,
4012 x_return_status => x_return_status,
4013 x_msg_count => x_msg_count,
4014 x_msg_data => x_msg_data
4015 );
4016
4017 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4018 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4019 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4020 'after calling CSP_PARTS_ORDER.book_order ...');
4021 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4022 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4023 'x_return_status = ' || x_return_status);
4024 end if;
4025
4026 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4027 rollback to csp_book_order;
4028
4029 select order_number into l_oe_order_num
4030 from oe_order_headers_all where header_id = l_oe_header_id;
4031
4032 FND_MESSAGE.SET_NAME('CSP', 'CSP_IO_BOOK_ERROR');
4033 FND_MESSAGE.SET_TOKEN('ORDER_NUM', l_oe_order_num, TRUE);
4034 FND_MSG_PUB.ADD;
4035 raise EXCP_USER_DEFINED;
4036 end if;
4037
4038 end if;
4039 end loop;
4040 end if; -- if l_assg_sts_flag = 'Y' then -- accepted
4041
4042 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4043 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4044 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4045 'checking for cancelled status change');
4046 end if;
4047
4048 SELECT nvl(cancelled_flag, 'N')
4049 INTO l_assg_sts_flag
4050 FROM JTF_TASK_STATUSES_B
4051 WHERE task_status_id = l_assignment_status_id;
4052
4053 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4054 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4055 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4056 'l_assg_sts_flag = ' || l_assg_sts_flag);
4057 end if;
4058
4059 if l_assg_sts_flag = 'Y' then
4060
4061 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4062 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4063 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4064 'before calling CSP_SCH_INT_PVT.CLEAN_REQUIREMENT...');
4065 end if;
4066
4067 CSP_SCH_INT_PVT.CLEAN_REQUIREMENT(
4068 p_api_version_number => 1.0,
4069 p_task_assignment_id => l_task_assignment_id,
4070 x_return_status => x_return_status,
4071 x_msg_count => x_msg_count,
4072 x_msg_data => x_msg_data
4073 );
4074
4075 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4076 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4077 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4078 'after calling CSP_SCH_INT_PVT.CLEAN_REQUIREMENT... x_return_status = ' || x_return_status);
4079 end if;
4080
4081 if x_return_status = 'C' then
4082 -- bug # 16028705
4083 if(nvl(CSF_TASKS_PUB.g_reschedule, 'N') = 'Y') then
4084 -- ignore it
4085 x_return_status := FND_API.G_RET_STS_SUCCESS;
4086 else
4087 x_return_status := 'E';
4088 end if;
4089 end if;
4090
4091 end if; -- if l_assg_sts_flag = 'Y' then -- cancelled
4092 END IF;
4093 END IF;
4094
4095 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4096 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4097 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_UPDATE',
4098 'done ...');
4099 end if;
4100 END;
4101
4102 PROCEDURE TASK_ASSIGNMENT_PRE_UPDATE
4103 ( x_return_status OUT NOCOPY varchar2) IS
4104
4105 CURSOR c_task_assignment( b_task_assignment_id NUMBER ) IS
4106 SELECT resource_id, assignment_status_id
4107 FROM jtf_task_assignments -- don't use synonym as that one filters on OWNER records
4108 WHERE task_assignment_id = b_task_assignment_id;
4109
4110 l_task_assignment_id NUMBER;
4111 l_assignee_role VARCHAR2(10);
4112
4113 BEGIN
4114 x_return_status := FND_API.G_RET_STS_SUCCESS;
4115 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
4116 l_assignee_role := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.assignee_role;
4117
4118 if l_assignee_role = 'ASSIGNEE' then
4119 OPEN c_task_assignment(l_task_assignment_id);
4120 FETCH c_task_assignment INTO G_old_resource_id, g_old_tsk_asgn_sts_id;
4121 end if;
4122
4123 END;
4124
4125 PROCEDURE TASK_ASSIGNMENT_POST_INSERT(
4126 x_return_status out nocopy varchar2)
4127 IS
4128
4129 l_task_assignment_id NUMBER;
4130 l_assignee_role VARCHAR2(10);
4131 l_rqmt_header_id number;
4132 l_organization_id number;
4133 l_subinventory_code VARCHAR2(30);
4134 l_dflt_ship_add_prf varchar2(10);
4135 l_ship_to_type varchar2(1);
4136 l_ship_to_location_id number;
4137 l_ship_hz_loc_id number := -999;
4138 l_party_site_id number;
4139 l_cust_act_id number;
4140 l_cust_id number;
4141 l_org_id number;
4142 x_msg_data varchar2(4000);
4143 x_msg_count number;
4144 l_resource_id NUMBER;
4145 l_resource_type_code VARCHAR2(30);
4146
4147 cursor get_current_ship_to (v_req_header_id number) is
4148 SELECT rh.ship_to_location_id, rh.ADDRESS_TYPE
4149 FROM csp_requirement_headers rh
4150 WHERE rh.requirement_header_id = v_req_header_id;
4151
4152 cursor get_blank_req_header is
4153 SELECT rh.requirement_header_id
4154 FROM csp_requirement_headers rh,
4155 jtf_task_assignments jta
4156 WHERE jta.task_assignment_id = l_task_assignment_id
4157 AND rh.task_id = jta.task_id
4158 AND rh.task_assignment_id IS NULL
4159 AND NOT EXISTS
4160 (SELECT 1
4161 FROM csp_req_line_details rld,
4162 csp_requirement_lines rl
4163 WHERE rl.requirement_line_id = rld.requirement_line_id
4164 AND rl.requirement_header_id = rh.requirement_header_id);
4165
4166 CURSOR get_Resource_org_sub IS
4167 SELECT organization_id, subinventory_code
4168 FROM csp_inv_loc_assignments
4169 WHERE resource_id = l_resource_id
4170 AND resource_type = l_resource_type_code
4171 AND default_code = 'IN';
4172
4173 CURSOR get_task_ship_add_loc IS
4174 SELECT
4175 pa.location_id as hrLocationId,
4176 jpl.location_id as hzLocationId,
4177 jpl.party_site_id as partySiteId,
4178 hcas.cust_account_id as custAccountId,
4179 jpl.party_id as customerId,
4180 cia.org_id
4181 FROM jtf_party_locations_v jpl,
4182 cs_incidents_all_b cia,
4183 jtf_tasks_b jtb,
4184 hz_cust_site_uses_all hcsu,
4185 hz_cust_acct_sites_all hcas,
4186 po_location_associations_all pa,
4187 jtf_task_assignments jta
4188 WHERE jta.task_assignment_id = l_Task_Assignment_id
4189 AND jtb.task_id = jta.task_id
4190 AND jtb.source_object_type_code = 'SR'
4191 and jtb.source_object_id = cia.incident_id
4192 AND jpl.party_id = cia.customer_id
4193 AND jpl.type = 'SHIP_TO'
4194 AND jpl.type = hcsu.site_use_code
4195 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
4196 AND hcas.party_site_id = jpl.party_site_id
4197 AND hcas.cust_account_id = cia.account_id
4198 AND hcsu.site_use_id = pa.site_use_id(+)
4199 AND pa.address_id(+) = hcsu.cust_acct_site_id
4200 AND hcas.org_id = cia.org_id
4201 AND jpl.party_site_id = jtb.address_id;
4202
4203 CURSOR get_cust_add_location IS
4204 SELECT
4205 pa.location_id as hrLocationId,
4206 jpl.location_id as hzLocationId,
4207 jpl.party_site_id as partySiteId,
4208 hcas.cust_account_id as custAccountId,
4209 jpl.party_id as customerId,
4210 cia.org_id
4211 FROM jtf_party_locations_v jpl,
4212 cs_incidents_all_b cia,
4213 jtf_tasks_b jtb,
4214 hz_cust_site_uses_all hcsu,
4215 hz_cust_acct_sites_all hcas,
4216 po_location_associations_all pa,
4217 jtf_task_assignments jta
4218 WHERE jta.task_assignment_id = l_Task_Assignment_id
4219 AND jtb.task_id = jta.task_id
4220 AND jtb.source_object_type_code = 'SR'
4221 and jtb.source_object_id = cia.incident_id
4222 AND jpl.party_id = cia.customer_id
4223 AND jpl.type = 'SHIP_TO'
4224 AND jpl.type = hcsu.site_use_code
4225 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
4226 AND hcas.party_site_id = jpl.party_site_id
4227 AND hcas.cust_account_id = cia.account_id
4228 AND hcsu.site_use_id = pa.site_use_id(+)
4229 AND pa.address_id(+) = hcsu.cust_acct_site_id
4230 AND hcas.org_id = cia.org_id
4231 AND jpl.primary_flag = 'Y';
4232
4233 CURSOR get_resource_location IS
4234 SELECT csp.ship_to_location_id
4235 FROM csp_rs_ship_to_addresses_all_v csp,
4236 hz_cust_acct_sites_All hz,
4237 cs_incidents_all_b cs,
4238 jtf_tasks_b jtb,
4239 jtf_task_assignments jta
4240 WHERE csp.cust_acct_site_id = hz.cust_acct_site_id
4241 AND csp.resource_id = jta.resource_id
4242 AND csp.resource_type = jta.resource_type_code
4243 AND primary_flag = 'Y'
4244 AND hz.org_id = cs.org_id
4245 AND jta.task_assignment_id = l_Task_Assignment_id
4246 AND jta.task_id = jtb.task_id
4247 AND jtb.source_object_type_code = 'SR'
4248 AND jtb.source_object_id = cs.incident_id
4249 AND csp.resource_id = l_resource_id
4250 AND csp.resource_type = l_resource_type_code;
4251
4252 BEGIN
4253
4254 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4255 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4256 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4257 'Begin');
4258 end if;
4259
4260 x_return_status := FND_API.G_RET_STS_SUCCESS;
4261 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
4262 l_assignee_role := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.assignee_role;
4263 l_resource_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_id;
4264 l_resource_type_code := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_type_code;
4265
4266 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4267 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4268 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4269 'l_task_assignment_id = ' || l_task_assignment_id
4270 || ', l_assignee_role = ' || l_assignee_role
4271 || ', l_resource_id = ' || l_resource_id
4272 || ', l_resource_type_code = ' || l_resource_type_code);
4273 end if;
4274
4275 if l_assignee_role = 'ASSIGNEE' then
4276
4277 -- check any requirement without task_assignment_id, if yes popuplate assignment details...
4278
4279 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4280 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4281 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4282 'checking if there is no task assignment posted on the part req...');
4283 end if;
4284
4285 for cr in get_blank_req_header loop
4286 l_rqmt_header_id := cr.requirement_header_id;
4287
4288 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4289 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4290 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4291 'found one l_rqmt_header_id = ' || l_rqmt_header_id);
4292 end if;
4293
4294 -- we need to default following things
4295 -- 1. task_assignment_id
4296 -- 2. resource_type and resource_id
4297 -- 3. destination_org and subinv
4298 -- 4. Ship To address Type
4299 -- 5. Ship To address
4300 --
4301 -- we can default 1 to 3 items easily
4302 -- for item 4, we need to read profile value CSP_PART_REQ_DEF_SHIP_TO
4303 -- for item 5, we need to get the address and generate HR_LOCATION_ID if not already available
4304
4305 -- we already have information for item 1 and 2
4306
4307 -- this is to get item 3
4308 OPEN get_Resource_org_sub;
4309 FETCH get_resource_org_sub INTO l_organization_id, l_subinventory_code;
4310 CLOSE get_resource_org_sub;
4311
4312 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4313 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4314 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4315 'l_organization_id = ' || l_organization_id
4316 || ', l_subinventory_code = ' || l_subinventory_code);
4317 end if;
4318
4319 open get_current_ship_to(l_rqmt_header_id);
4320 fetch get_current_ship_to into l_ship_to_location_id, l_ship_to_type;
4321 close get_current_ship_to;
4322
4323 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4324 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4325 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4326 'l_ship_to_location_id = ' || l_ship_to_location_id);
4327 end if;
4328
4329 if l_ship_to_location_id is null then
4330 -- this is to get item 4 and 5
4331
4332 /*
4333 There is a bug in scheduler code. It creates task assignment first and
4334 then checks for Spares availability! So, if this code updates a part req
4335 then spares-scheduler integration code will break. So, removing this code to
4336 default ship to address based on profile and always default technician
4337 primary ship to address as there is a similar logic in spares-scheduler
4338 integration code as well.
4339 */
4340
4341
4342 --l_dflt_ship_add_prf := FND_PROFILE.value('CSP_PART_REQ_DEF_SHIP_TO');
4343
4344 if l_dflt_ship_add_prf is null then
4345 l_dflt_ship_add_prf := 'NONE';
4346 end if;
4347
4348
4349 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4350 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4351 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4352 'profile CSP_PART_REQ_DEF_SHIP_TO is l_dflt_ship_add_prf = ' || l_dflt_ship_add_prf);
4353 end if;
4354
4355 if l_dflt_ship_add_prf = 'TASK' then
4356
4357 l_ship_to_type := 'T';
4358
4359 open get_task_ship_add_loc;
4360 fetch get_task_ship_add_loc into l_ship_to_location_id,
4361 l_ship_hz_loc_id,
4362 l_party_site_id,
4363 l_cust_act_id,
4364 l_cust_id,
4365 l_org_id;
4366 close get_task_ship_add_loc;
4367
4368 elsif l_dflt_ship_add_prf = 'CUSTOMER' then
4369
4370 l_ship_to_type := 'C';
4371
4372 open get_cust_add_location;
4373 fetch get_cust_add_location into l_ship_to_location_id,
4374 l_ship_hz_loc_id,
4375 l_party_site_id,
4376 l_cust_act_id,
4377 l_cust_id,
4378 l_org_id;
4379 close get_cust_add_location;
4380
4381 else
4382 l_ship_to_type := 'R';
4383
4384 OPEN get_resource_location;
4385 FETCH get_resource_location INTO l_ship_to_location_id;
4386 CLOSE get_resource_location;
4387
4388 end if;
4389
4390 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4391 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4392 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4393 'l_ship_to_type = ' || l_ship_to_type
4394 || ', l_ship_to_location_id = ' || l_ship_to_location_id
4395 || ', l_ship_hz_loc_id = ' || l_ship_hz_loc_id);
4396 end if;
4397
4398 -- assuming technician ship to address will have proper hr_location_id
4399 /*
4400 if l_ship_to_location_id is null and nvl(l_ship_hz_loc_id, -999) <> -999 then
4401 -- need to generate HR_LOCATION_ID
4402
4403 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4404 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4405 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4406 'Before calling csp_ship_to_address_pvt.cust_inv_loc_link...');
4407 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4408 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4409 'l_party_site_id = ' || l_party_site_id
4410 || ', l_cust_act_id = ' || l_cust_act_id
4411 || ', l_cust_id = ' || l_cust_id
4412 || ', l_org_id = ' || l_org_id);
4413 end if;
4414
4415 csp_ship_to_address_pvt.cust_inv_loc_link
4416 (p_api_version => 1.0
4417 ,p_Init_Msg_List => FND_API.G_FALSE
4418 ,p_commit => FND_API.G_FALSE
4419 ,px_location_id => l_ship_hz_loc_id
4420 ,p_party_site_id => l_party_site_id
4421 ,p_cust_account_id => l_cust_act_id
4422 ,p_customer_id => l_cust_id
4423 ,p_org_id => l_org_id
4424 ,p_attribute_category => null
4425 ,p_attribute1 => null
4426 ,p_attribute2 => null
4427 ,p_attribute3 => null
4428 ,p_attribute4 => null
4429 ,p_attribute5 => null
4430 ,p_attribute6 => null
4431 ,p_attribute7 => null
4432 ,p_attribute8 => null
4433 ,p_attribute9 => null
4434 ,p_attribute10 => null
4435 ,p_attribute11 => null
4436 ,p_attribute12 => null
4437 ,p_attribute13 => null
4438 ,p_attribute14 => null
4439 ,p_attribute15 => null
4440 ,p_attribute16 => null
4441 ,p_attribute17 => null
4442 ,p_attribute18 => null
4443 ,p_attribute19 => null
4444 ,p_attribute20 => null
4445 ,x_return_status => x_return_status
4446 ,x_msg_count => x_msg_count
4447 ,x_msg_data => x_msg_data
4448 );
4449
4450 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4451 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4452 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4453 'After calling csp_ship_to_address_pvt.cust_inv_loc_link...');
4454 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4455 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4456 'x_return_status = ' || x_return_status
4457 || ', x_msg_count = ' || x_msg_count
4458 || ', x_msg_data = ' || x_msg_data
4459 || ', l_ship_hz_loc_id = ' || l_ship_hz_loc_id);
4460 end if;
4461
4462 if nvl(x_return_status, 'S') = FND_API.G_RET_STS_SUCCESS then
4463 l_ship_to_location_id := l_ship_hz_loc_id;
4464 x_return_status := 'S';
4465 end if;
4466
4467 end if;
4468 */
4469 end if;
4470
4471 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4472 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4473 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4474 'before calling CSP_REQUIREMENT_HEADERS_PKG.Update_Row...');
4475 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4476 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4477 'l_ship_to_location_id = ' || l_ship_to_location_id);
4478 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4479 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4480 'l_ship_to_type = ' || l_ship_to_type);
4481 end if;
4482
4483 CSP_REQUIREMENT_HEADERS_PKG.Update_Row(
4484 p_REQUIREMENT_HEADER_ID => l_rqmt_header_id,
4485 p_TASK_ASSIGNMENT_ID => l_task_assignment_id,
4486 p_resource_id => l_resource_id,
4487 p_resource_type => l_resource_type_code,
4488 p_DESTINATION_ORGANIZATION_ID => l_organization_id,
4489 P_DESTINATION_SUBINVENTORY => l_subinventory_code,
4490 p_ADDRESS_TYPE => l_ship_to_type,
4491 p_SHIP_TO_LOCATION_ID => l_ship_to_location_id,
4492 p_ship_to_contact_id => null
4493 );
4494
4495 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4496 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4497 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4498 'after calling CSP_REQUIREMENT_HEADERS_PKG.Update_Row...');
4499 end if;
4500
4501 end loop;
4502 end if;
4503
4504 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4505 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4506 'csp.plsql.CSP_PARTS_REQUIREMENT.TASK_ASSIGNMENT_POST_INSERT',
4507 'done ...');
4508 end if;
4509 END;
4510
4511 PROCEDURE TASK_ASSIGNMENT_PRE_DELETE(x_return_status out nocopy varchar2)
4512 IS
4513 l_log_module varchar2(100) := 'csp.plsql.csp_parts_requirement.task_assignment_pre_delete';
4514 l_task_assignment_id NUMBER;
4515 x_msg_data varchar2(4000);
4516 x_msg_count number;
4517 l_task_id number;
4518 l_dtl_count number;
4519 EXCP_USER_DEFINED EXCEPTION;
4520 BEGIN
4521 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4522 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
4523 'begin ...');
4524 end if;
4525
4526 x_return_status := FND_API.G_RET_STS_SUCCESS;
4527 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
4528 l_task_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_id;
4529
4530 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4531 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
4532 'before calling CSP_SCH_INT_PVT.CLEAN_REQUIREMENT...');
4533 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
4534 'l_task_assignment_id=' || l_task_assignment_id);
4535 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
4536 'l_task_id=' || l_task_id);
4537 end if;
4538
4539 CSP_SCH_INT_PVT.CLEAN_REQUIREMENT(
4540 p_api_version_number => 1.0,
4541 p_task_assignment_id => l_task_assignment_id,
4542 x_return_status => x_return_status,
4543 x_msg_count => x_msg_count,
4544 x_msg_data => x_msg_data
4545 );
4546
4547 -- check if we still has any req line details
4548 l_dtl_count := 0;
4549 SELECT COUNT(*)
4550 INTO l_dtl_count
4551 FROM csp_requirement_headers h,
4552 csp_requirement_lines l,
4553 csp_req_line_details d
4554 WHERE h.task_assignment_id = l_task_assignment_id
4555 AND h.task_id = l_task_id
4556 AND h.requirement_header_id = l.requirement_header_id
4557 AND l.requirement_line_id = d.requirement_line_id;
4558
4559 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4560 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
4561 'l_dtl_count=' || l_dtl_count);
4562 end if;
4563
4564 if l_dtl_count > 0 then
4565 x_return_status := FND_API.G_RET_STS_ERROR;
4566 FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_NOT_CANCEL_ORDER');
4567 FND_MSG_PUB.ADD;
4568 fnd_msg_pub.count_and_get
4569 ( p_count => x_msg_count
4570 , p_data => x_msg_data);
4571 raise EXCP_USER_DEFINED;
4572 end if;
4573
4574 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4575 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
4576 'after calling CSP_SCH_INT_PVT.CLEAN_REQUIREMENT... x_return_status = ' || x_return_status);
4577 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_log_module,
4578 'done ...');
4579 end if;
4580 END;
4581
4582 procedure log(p_procedure in varchar2,p_message in varchar2) as
4583 begin
4584 --dbms_output.put_line(p_procedure||' - '||p_message);
4585 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4586 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4587 'csp.plsql.csp_parts_requirement.'||p_procedure,
4588 p_message);
4589 end if;
4590 end;
4591
4592 procedure get_resource_shift_end(
4593 p_resource_id in number
4594 ,p_resource_type in varchar2
4595 ,x_shift_end_datetime out nocopy date
4596 ,x_return_status out nocopy varchar2
4597 ,x_msg_count out nocopy number
4598 ,x_msg_data out nocopy varchar2
4599 )
4600 is
4601 l_start_date date;
4602 l_end_date date;
4603 l_shifts apps.csf_resource_pub.shift_tbl_type;
4604 l_lead_time_days number;
4605 l_shift_date_start date;
4606 l_shift_date_start_local date;
4607 l_shift_datetime_end date;
4608 l_shift_datetime_end_local date;
4609 begin
4610 l_lead_time_days := nvl(fnd_profile.value('CSP_PR_NEED_BY_LEAD_TIME'), 1);
4611 l_start_date := sysdate+l_lead_time_days;
4612 l_end_date := sysdate+l_lead_time_days+30;
4613 x_shift_end_datetime := l_start_date;
4614
4615 csf_resource_pub.get_resource_shifts(
4616 p_api_version => 1.0,
4617 p_init_msg_list => fnd_api.g_false,
4618 x_return_status => x_return_status,
4619 x_msg_count => x_msg_count,
4620 x_msg_data => x_msg_data,
4621 p_resource_id => p_resource_id,
4622 p_resource_type => p_resource_type,
4623 p_start_date => l_start_date,
4624 p_end_date => l_end_date,
4625 p_shift_type => null,
4626 x_shifts => l_shifts
4627 );
4628 log('get_resource_shift_end', 'x_return_status = ' || x_return_status);
4629 log('get_resource_shift_end', 'x_msg_count = ' || x_msg_count);
4630 log('get_resource_shift_end', 'x_msg_data = ' || x_msg_data);
4631
4632 if (x_return_status = 'S') and (l_shifts is not null) and (l_shifts.count > 0) then
4633 for i in 1..l_shifts.count loop
4634 log('get_resource_shift_end', i||' ====================================================== '||i);
4635 log('get_resource_shift_end', 'shift_construct_id: '||l_shifts(i).shift_construct_id);
4636 log('get_resource_shift_end', 'start_datetime: '||to_char(l_shifts(i).start_datetime, 'DD-MM-YYYY HH24:MI'));
4637 log('get_resource_shift_end', 'end_datetime: '||to_char(l_shifts(i).end_datetime, 'DD-MM-YYYY HH24:MI'));
4638 log('get_resource_shift_end', 'availability_type: '||l_shifts(i).availability_type);
4639 log('get_resource_shift_end', i||' ====================================================== '||i);
4640
4641 l_shift_date_start_local := to_date(to_char(l_shifts(i).start_datetime, 'DD-MM-YYYY'), 'DD-MM-YYYY');
4642 l_shift_datetime_end_local := l_shifts(i).end_datetime;
4643
4644 if l_shift_date_start is null then
4645 l_shift_date_start := l_shift_date_start_local;
4646 l_shift_datetime_end := l_shift_datetime_end_local;
4647 elsif l_shift_date_start = l_shift_date_start_local then
4648 l_shift_datetime_end := l_shift_datetime_end_local;
4649 else
4650 exit;
4651 end if;
4652 end loop;
4653
4654 log('get_resource_shift_end', 'Next Available Shift End Datetime: '||to_char(l_shift_datetime_end, 'DD-MM-YYYY HH24:MI'));
4655 x_shift_end_datetime := l_shift_datetime_end;
4656 end if;
4657 end;
4658
4659 END;