[Home] [Help]
PACKAGE BODY: APPS.OKE_AMG_GRP
Source
1 PACKAGE BODY OKE_AMG_GRP AS
2 /* $Header: OKEAMGDB.pls 120.1.12020000.2 2012/07/25 17:47:13 vgujarat ship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 ---------------------------------------------------------------------------
6 G_FND_APP CONSTANT VARCHAR2(200) := OKE_API.G_FND_APP;
7 ---------------------------------------------------------------------------
8 -- GLOBAL VARIABLES
9 ---------------------------------------------------------------------------
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKE_AMG_GRP';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKE_API.G_APP_NAME;
12
13 ------------------------------------------------------------------------------
14 -- GLOBAL CONSTANTS
15 ------------------------------------------------------------------------------
16 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
17 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
18
19 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
20 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
21 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
22
23 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE_UNEXPECTED_ERROR';
24 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
25 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
26 G_CRT CONSTANT VARCHAR2(10) := 'CREATE';
27 G_DEL CONSTANT VARCHAR2(10) := 'DELETE';
28 G_UPD CONSTANT VARCHAR2(10) := 'UPDATE';
29 G_SHIP CONSTANT VARCHAR2(10) := 'WSH';
30 G_REQ CONSTANT VARCHAR2(10) := 'REQ';
31 G_MDS CONSTANT VARCHAR2(10) := 'MDS';
32 G_SOURCE_CODE CONSTANT VARCHAR2(3) := 'PA';
33 G_INV CONSTANT VARCHAR2(30) := 'INVENTORY';
34 G_EXP CONSTANT VARCHAR2(30) := 'EXPENSE';
35 G_USER CONSTANT VARCHAR2(4) := 'User';
36
37 l_project_name Varchar2(30);
38 l_deliverable_name Varchar2(150);
39 l_action_name varchar2(240);
40
41 Function get_project_name(p_project_id number) return varchar2 IS
42 cursor c_get_project_name is
43 Select name from pa_projects_all where project_id=p_project_id;
44
45 l_name varchar2(30);
46 begin
47 open c_get_project_name;
48 fetch c_get_project_name into l_name;
49 If c_get_project_name%notfound then
50 close c_get_project_name;
51 raise FND_API.G_EXC_UNEXPECTED_ERROR;
52 end if;
53 close c_get_project_name;
54 return l_name;
55 end;
56
57 Function get_dlv_name(p_deliverable_id number) return varchar2 IS
58 cursor c_dlv_name is
59 Select deliverable_number from oke_deliverables_b
60 where deliverable_id = p_deliverable_id;
61
62 l_name varchar2(150); /*modified for 12983318*/
63 begin
64 open c_dlv_name;
65 fetch c_dlv_name into l_name;
66 If c_dlv_name%notfound then
67 close c_dlv_name;
68 raise FND_API.G_EXC_UNEXPECTED_ERROR;
69 end if;
70 close c_dlv_name;
71 return l_name;
72 end;
73
74 Procedure validate_dlv_attr(
75 p_master_inv_org_id IN NUMBER,
76 p_item_id IN OUT NOCOPY NUMBER,
77 p_inventory_org_id IN OUT NOCOPY NUMBER,
78 p_currency_code IN OUT NOCOPY VARCHAR2,
79 p_uom_code IN OUT NOCOPY VARCHAR2,
80 p_dlv_short_name IN OUT NOCOPY VARCHAR2,
81 p_project_id IN OUT NOCOPY NUMBER,
82 p_quanitity IN OUT NOCOPY NUMBER,
83 p_unit_price IN OUT NOCOPY NUMBER,
84 p_unit_number IN OUT NOCOPY VARCHAR2,
85 p_quantity IN OUT NOCOPY VARCHAR2,
86 p_item_dlv IN VARCHAR2,
87 p_deliverable_id IN NUMBER,
88 x_return_status OUT NOCOPY VARCHAR2) IS
89
90 Cursor c_check_inv(b_master_org_id number,b_inv_org_id number) is
91 select 'x' from mtl_parameters
92 where master_organization_id=b_master_org_id
93 and organization_id=b_inv_org_id;
94
95 Cursor c_check_item(b_item_id number,b_inv_org_id number) is
96 select 'x' from oke_system_items_v
97 where id1=b_item_id and id2=b_inv_org_id;
98
99 Cursor c_check_currency(b_currency_code Varchar2) is
100 select 'x' from fnd_currencies
101 where currency_code=b_currency_code
102 and enabled_flag='Y'
103 and currency_flag='Y'
104 and sysdate >= nvl(start_date_active,sysdate)
105 and sysdate <= nvl(end_date_active,sysdate);
106
107 Cursor c_check_uom(b_item_id number,b_uom varchar2) is
108 select 'x' from mtl_item_uoms_view
109 where inventory_item_id=b_item_id
110 and uom_code=b_uom;
111
112 Cursor c_check_unit_number(b_item_id number,b_unit_number varchar2) is
113 select 'x' from pjm_unit_numbers_lov_v
114 where end_item_id=b_item_id
115 and unit_number=b_unit_number;
116
117 Cursor c_get_primary_uom(b_item_id number) is
118 select primary_uom_code from oke_system_items_v
119 where inventory_item_id=b_item_id;
120
121 l_x varchar2(1);
122 begin
123
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125
126 If p_dlv_short_name is null then
127
128 oke_api.set_message(p_msg_name => 'OKE_DLV_SHORT_NAME',
129 p_token1 =>'PROJECT_NAME',
130 p_token1_value =>l_project_name,
131 p_token2 =>'DELIVERABLE_NAME',
132 p_token2_value =>l_deliverable_name);
133 raise FND_API.G_EXC_ERROR;
134 end if;
135 If p_item_dlv <>'Y' then
136 p_item_id := Null;
137 p_inventory_org_id := Null;
138 p_currency_code := null;
139 p_uom_code := null;
140 p_quantity := null;
141 p_unit_number := null;
142 p_unit_price := null;
143 end if;
144 If p_inventory_org_id is not null then
145 Open c_check_inv(p_master_inv_org_id,p_inventory_org_id);
146 Fetch c_check_inv into l_x;
147 If c_check_inv%rowcount = 0 then
148 oke_api.set_message(p_msg_name => 'OKE_CHECK_DLV_INV',
149 p_token1 =>'PROJECT_NAME',
150 p_token1_value =>l_project_name,
151 p_token2 =>'DELIVERABLE_NAME',
152 p_token2_value =>l_deliverable_name);
153 close c_check_inv;
154 raise FND_API.G_EXC_ERROR;
155 end if;
156 close c_check_inv;
157
158 If p_item_id is not null then
159 Open c_check_item(p_item_id,p_inventory_org_id);
160 Fetch c_check_item into l_x;
161 If c_check_item%rowcount = 0 then
162 oke_api.set_message(p_msg_name =>'OKE_CHECK_DLV_ITEM',
163 p_token1 =>'PROJECT_NAME',
164 p_token1_value =>l_project_name,
165 p_token2 =>'DELIVERABLE_NAME',
166 p_token2_value =>l_deliverable_name);
167 close c_check_item;
168 raise FND_API.G_EXC_ERROR;
169 end if;
170 close c_check_item;
171 end if;
172
173 Else
174 If p_item_id is not null then
175 Open c_check_item(p_item_id,p_master_inv_org_id);
176 Fetch c_check_item into l_x;
177 If c_check_item%rowcount = 0 then
178 oke_api.set_message(p_msg_name =>'OKE_CHECK_DLV_ITEM_MAS',
179 p_token1 =>'PROJECT_NAME',
180 p_token1_value =>l_project_name,
181 p_token2 =>'DELIVERABLE_NAME',
182 p_token2_value =>l_deliverable_name);
183 close c_check_item;
184 raise FND_API.G_EXC_ERROR;
185 end if;
186 close c_check_item;
187 end if;
188 end if;
189
190 If p_uom_code is not null then
191 If p_item_id is not null then
192 Open c_check_uom(p_item_id ,p_uom_code);
193 Fetch c_check_uom into l_x;
194 If c_check_uom%rowcount = 0 then
195 oke_api.set_message(p_msg_name =>'OKE_CHECK_UOM',
196 p_token1 =>'PROJECT_NAME',
197 p_token1_value =>l_project_name,
198 p_token2 =>'DELIVERABLE_NAME',
199 p_token2_value =>l_deliverable_name);
200 close c_check_uom;
201 raise FND_API.G_EXC_ERROR;
202 End if;
203 Close c_check_uom;
204 Else
205 oke_api.set_message(p_msg_name =>'OKE_CHECK_UOM_ENTERABLE',
206 p_token1 =>'PROJECT_NAME',
207 p_token1_value =>l_project_name,
208 p_token2 =>'DELIVERABLE_NAME',
209 p_token2_value =>l_deliverable_name);
210
211 raise FND_API.G_EXC_ERROR;
212 End if;
213 else
214 If p_item_id is not null and p_item_dlv = 'Y' then
215 open c_get_primary_uom(p_item_id);
216 fetch c_get_primary_uom into p_uom_code;
217 close c_get_primary_uom;
218 end if;
219 End if;
220
221 If p_currency_code is not null then
222 Open c_check_currency(p_currency_code);
223 Fetch c_check_currency into l_x;
224 If c_check_currency%rowcount = 0 then
225 oke_api.set_message(p_msg_name =>'OKE_CHECK_CURRENCY',
226 p_token1 =>'PROJECT_NAME',
227 p_token1_value =>l_project_name,
228 p_token2 =>'DELIVERABLE_NAME',
229 p_token2_value =>l_deliverable_name);
230 close c_check_currency;
231 raise FND_API.G_EXC_ERROR;
232 End if;
233 Close c_check_currency;
234 else
235 If p_inventory_org_id is not null then
236 p_currency_code := OKE_ACTION_VALIDATIONS_PKG.functional_currency(p_inventory_org_id);
237 end if;
238 End if;
239
240 If p_unit_number is not null then
241 If p_item_id is not null then
242 Open c_check_unit_number(p_item_id,p_unit_number);
243 Fetch c_check_unit_number into l_x;
244 If c_check_unit_number%rowcount = 0 then
245 oke_api.set_message(p_msg_name =>'OKE_CHECK_UNIT_NO',
246 p_token1 =>'PROJECT_NAME',
247 p_token1_value =>l_project_name,
248 p_token2 =>'DELIVERABLE_NAME',
249 p_token2_value =>l_deliverable_name);
250 close c_check_unit_number;
251 raise FND_API.G_EXC_ERROR;
252 End if;
253 Close c_check_unit_number;
254 Else
255 oke_api.set_message(p_msg_name =>'OKE_CHECK_UNIT_NO_ENTERABLE',
256 p_token1 =>'PROJECT_NAME',
257 p_token1_value =>l_project_name,
258 p_token2 =>'DELIVERABLE_NAME',
259 p_token2_value =>l_deliverable_name);
260
261 raise FND_API.G_EXC_ERROR;
262 end if;
263 end if;
264
265 EXCEPTION
266 WHEN FND_API.G_EXC_ERROR THEN
267 x_return_status := G_RET_STS_ERROR ;
268
269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
270 x_return_status := G_RET_STS_UNEXP_ERROR ;
271
272 WHEN OTHERS THEN
273 x_return_status := G_RET_STS_UNEXP_ERROR ;
274 END;
275
276 Procedure Validate_dlv_action_attr(
277 p_dlv_action_type IN VARCHAR2,
278 p_item_dlv IN VARCHAR2,
279 p_deliverable_id IN NUMBER,
280 p_inv_org_id IN NUMBER,
281 p_currency_code IN OUT NOCOPY VARCHAR2,
282 p_quantity IN OUT NOCOPY NUMBER,
283 p_demand_schedule IN OUT NOCOPY VARCHAR2,
284 p_ship_from_org_id IN OUT NOCOPY NUMBER,
285 p_ship_from_loc_id IN OUT NOCOPY NUMBER,
286 p_ship_to_org_id IN OUT NOCOPY NUMBER,
287 p_ship_to_loc_id IN OUT NOCOPY NUMBER,
288 p_volume IN OUT NOCOPY NUMBER,
289 p_volume_uom IN OUT NOCOPY VARCHAR2,
290 p_weight IN OUT NOCOPY NUMBER,
291 p_weight_uom IN OUT NOCOPY VARCHAR2,
292 p_destination_type_code IN OUT NOCOPY VARCHAR2,
293 p_po_need_by_date IN OUT NOCOPY DATE,
294 p_exchange_rate_type IN OUT NOCOPY VARCHAR2,
295 p_exchange_rate IN OUT NOCOPY NUMBER,
296 P_exchange_rate_date IN OUT NOCOPY DATE,
297 p_expenditure_type IN OUT NOCOPY VARCHAR2,
298 p_expenditure_org_id IN OUT NOCOPY NUMBER,
299 p_requisition_line_type_id IN OUT NOCOPY NUMBER,
300 p_category_id IN OUT NOCOPY NUMBER,
301 p_uom_code IN OUT NOCOPY VARCHAR2,
302 p_currency IN OUT NOCOPY VARCHAR2,
303 p_unit_price IN OUT NOCOPY NUMBER,
304 x_return_status OUT NOCOPY VARCHAR2
305 ) IS
306 l_inv number;
307 Cursor c_check_currency IS
308 Select 'x' from fnd_currencies b
309 where enabled_flag = 'Y'
310 and currency_flag = 'Y'
311 and b.currency_code = p_currency_code
312 and sysdate >= nvl (b.start_date_active, sysdate)
313 and sysdate <= nvl (b.end_date_active, sysdate);
314
315 Cursor c_check_rec_org IS
316 Select 'x' from org_organization_definitions
317 where nvl(inventory_enabled_flag, 'N') = 'Y'
318 and nvl(disable_date, sysdate) >= sysdate
319 and organization_id= p_ship_to_org_id;
320
321 Cursor c_check_rec_loc IS
322 Select 'x' from okx_locations_v ocv
323 where organization_id = p_ship_to_org_id
324 and id1= p_ship_to_loc_id;
325
326 Cursor c_check_vendor IS
327 Select 'x' FROM PO_VENDORS
328 where sysdate between nvl(start_date_active, sysdate)
329 and nvl(end_date_active, sysdate)
330 and vendor_id= p_ship_from_org_id;
331
332 Cursor c_check_vendor_site IS
333 Select 'x' from po_supplier_sites_val_v
334 where nvl(rfq_only_site_flag, 'N') = 'N'
335 and vendor_id =p_ship_from_org_id
336 and vendor_site_id= p_ship_from_loc_id;
337
338 Cursor c_check_exchange_type IS
339 Select 'x' from gl_daily_conversion_types
340 where conversion_type= p_exchange_rate_type;
341
342 Cursor c_check_expend_type IS
343 Select 'x' from pa_expenditure_types_expend_v et
344 where system_linkage_function = 'VI'
345 and et.project_id = (select project_id from oke_deliverables_b
346 where deliverable_id=p_deliverable_id)
347 and expenditure_type = p_expenditure_type
348 union
349 select 'x' from pa_expenditure_types_expend_v et
350 where system_linkage_function = 'VI'
351 and et.project_id is null
352 and expenditure_type = p_expenditure_type;
353
354 Cursor c_check_expend_org IS
355 Select 'x' from pa_organizations_expend_v o
356 where active_flag = 'Y'
357 and trunc(sysdate) between o.date_from and nvl(o.date_to, trunc(sysdate))
358 and organization_id= p_expenditure_org_id;
359
360 Cursor c_check_req_type IS
361 Select 'x' from po_line_types
362 where order_type_lookup_code = 'AMOUNT'
363 and line_type_id= p_requisition_line_type_id;
364 Cursor c_get_inv_org is
365 select inventory_org_id from oke_deliverables_b
366 where deliverable_id=p_deliverable_id;
367
368 Cursor c_check_demand_schedule IS
369 Select 'x' from mrp_designators_view v, oke_deliverables_b b
370 where v.designator_type = 1
371 and nvl( v.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
372 and b.inventory_org_id = v.organization_id
373 and b.deliverable_id=p_deliverable_id
374 and v.designator = p_demand_schedule;
375
376 Cursor c_check_ship_from IS
377 Select 'x' from org_organization_definitions
378 where nvl(inventory_enabled_flag, 'N') = 'Y'
379 and nvl(disable_date, sysdate) >= sysdate
380 and organization_id= p_ship_from_org_id;
381
382 Cursor c_check_ship_from_loc IS
383 Select 'x' from okx_locations_v ocv
384 where organization_id = p_ship_from_org_id
385 and id1= p_ship_from_loc_id;
386
387 Cursor c_check_customer IS
388 Select 'x' from oke_customer_accounts_v
389 where id1= p_ship_to_org_id ;
390
391 Cursor c_check_cust_add IS
392 Select 'x' from oke_cust_site_uses_v
393 where site_use_code = 'SHIP_TO'
394 and cust_account_id = p_ship_to_org_id
395 and location_id= p_ship_to_loc_id;
396
397 Cursor c_check_volume_uom IS
398 Select 'x' from mtl_units_of_measure uom,
399 wsh_shipping_parameters wsp
400 where wsp.organization_id =p_ship_from_org_id
401 and uom.uom_class = wsp.volume_uom_class
402 and sysdate < nvl(disable_date, sysdate + 1)
403 and uom_code= p_volume_uom;
404
405 Cursor c_check_weight_uom IS
406 Select 'x' from mtl_units_of_measure uom,
407 wsh_shipping_parameters wsp
408 where wsp.organization_id =p_ship_from_org_id
409 and uom.uom_class = wsp.weight_uom_class
410 and sysdate < nvl(disable_date, sysdate + 1)
411 and uom_code= p_weight_uom;
412 Cursor c_check_uom IS
413 Select 'x' from mtl_units_of_measure uom
414 where sysdate < nvl(disable_date, sysdate + 1)
415 and uom_code= p_uom_code;
416 Cursor c_check_category IS
417 Select 'x' from mtl_categories_b
418 where category_id =p_category_id;
419
420 l_x Varchar2(1);
421 l_function_currency Varchar2(30);
422 l_currency Varchar2(30);
423
424 Begin
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426 If p_dlv_action_type=G_REQ then
427 p_demand_schedule := Null;
428 p_volume := Null;
429 p_volume_uom := Null;
430 p_weight_uom := Null;
431 p_weight_uom := Null;
432
433 If p_item_dlv='Y' then
434 p_category_id := Null;
435 p_requisition_line_type_id := Null;
436 p_expenditure_type := Null;
437 p_expenditure_org_id := Null;
438 end if;
439
440 -- Check Currency
441 If p_currency_code is not null then
442 Open c_check_currency;
443 fetch c_check_currency into l_x;
444 if c_check_currency%rowcount =0 then
445 oke_api.set_message(p_msg_name =>'OKE_CHECK_CURRENCY',
446 p_token1 =>'PROJECT_NAME',
447 p_token1_value =>l_project_name,
448 p_token2 =>'DELIVERABLE_NAME',
449 p_token2_value =>l_deliverable_name,
450 p_token3 =>'ACTION_NAME',
451 p_token3_value =>l_action_name
452 );
453 Close c_check_currency;
454 raise FND_API.G_EXC_ERROR;
455 end if;
456 Close c_check_currency;
457 end if;
458 -- Check UOM
459 If p_uom_code is not null then
460 Open c_check_uom;
461 fetch c_check_uom into l_x;
462 if c_check_uom%rowcount =0 then
463 oke_api.set_message(p_msg_name =>'OKE_CHECK_UOM',
464 p_token1 =>'PROJECT_NAME',
465 p_token1_value =>l_project_name,
466 p_token2 =>'DELIVERABLE_NAME',
467 p_token2_value =>l_deliverable_name
468 );
469 Close c_check_uom;
470 raise FND_API.G_EXC_ERROR;
471 end if;
472 Close c_check_uom;
473 end if;
474 -- Check Destinattion Type
475
476 If p_destination_type_code is not null then
477 If p_destination_type_code not in (G_INV, G_EXP) then
478 oke_api.set_message(p_msg_name =>'OKE_INVALID_DESTINATION_TYPE',
479 p_token1 =>'PROJECT_NAME',
480 p_token1_value =>l_project_name,
481 p_token2 =>'DELIVERABLE_NAME',
482 p_token2_value =>l_deliverable_name,
483 p_token3 =>'ACTION_NAME',
484 p_token3_value =>l_action_name
485 );
486 raise FND_API.G_EXC_ERROR;
487 end if;
488
489 If p_destination_type_code='INVENTORYE' and p_item_dlv<>'Y' then
490 oke_api.set_message(p_msg_name=>'OKE_INVALID_NONITEM_DEST_TYPE',
491 p_token1 =>'PROJECT_NAME',
492 p_token1_value =>l_project_name,
493 p_token2 =>'DELIVERABLE_NAME',
494 p_token2_value =>l_deliverable_name,
495 p_token3 =>'ACTION_NAME',
496 p_token3_value =>l_action_name
497 );
498 raise FND_API.G_EXC_ERROR;
499 end if;
500 end if;
501 -- Check Receiving Org
502 If p_ship_to_org_id is not null then
503 If p_item_dlv='Y' then
504 If p_ship_to_org_id <> p_inv_org_id then
505 oke_api.set_message(p_msg_name =>'OKE_SAME_REC_ORG',
506 p_token1 =>'PROJECT_NAME',
507 p_token1_value =>l_project_name,
508 p_token2 =>'DELIVERABLE_NAME',
509 p_token2_value =>l_deliverable_name,
510 p_token3 =>'ACTION_NAME',
511 p_token3_value =>l_action_name
512 );
513 raise FND_API.G_EXC_ERROR;
514 end if;
515 Else
516 Open c_check_rec_org;
517 Fetch c_check_rec_org into l_x;
518 if c_check_rec_org%rowcount =0 then
519 oke_api.set_message(p_msg_name =>'OKE_INVALID_REC_ORG',
520 p_token1 =>'PROJECT_NAME',
521 p_token1_value =>l_project_name,
522 p_token2 =>'DELIVERABLE_NAME',
523 p_token2_value =>l_deliverable_name,
524 p_token3 =>'ACTION_NAME',
525 p_token3_value =>l_action_name
526 );
527 close c_check_rec_org;
528 raise FND_API.G_EXC_ERROR;
529 end if;
530 close c_check_rec_org;
531 end if;
532
533 end if;
534 -- Check Receiving Location
535 If p_ship_to_loc_id is not null then
536 If p_ship_to_org_id is not null then
537 Open c_check_rec_loc;
538 Fetch c_check_rec_loc into l_x;
539 if c_check_rec_loc%rowcount =0 then
540 oke_api.set_message(p_msg_name =>'OKE_INVALID_RECV_LOC',
541 p_token1 =>'PROJECT_NAME',
542 p_token1_value =>l_project_name,
543 p_token2 =>'DELIVERABLE_NAME',
544 p_token2_value =>l_deliverable_name,
545 p_token3 =>'ACTION_NAME',
546 p_token3_value =>l_action_name
547 );
548 close c_check_rec_loc;
549 raise FND_API.G_EXC_ERROR;
550 end if;
551 close c_check_rec_loc;
552 else
553 oke_api.set_message(p_msg_name =>'OKE_INVALID_RECV_LOC1',
554 p_token1 =>'PROJECT_NAME',
555 p_token1_value =>l_project_name,
556 p_token2 =>'DELIVERABLE_NAME',
557 p_token2_value =>l_deliverable_name,
558 p_token3 =>'ACTION_NAME',
559 p_token3_value =>l_action_name
560 );
561 close c_check_rec_loc;
562 raise FND_API.G_EXC_ERROR;
563 end if;
564 end if;
565 -- Check Vendor
566 If p_ship_from_org_id is not null then
567 Open c_check_vendor;
568 Fetch c_check_vendor into l_x;
569 if c_check_vendor%rowcount =0 then
570 oke_api.set_message(p_msg_name =>'OKE_INVALID_VENDOR',
571 p_token1 =>'PROJECT_NAME',
572 p_token1_value =>l_project_name,
573 p_token2 =>'DELIVERABLE_NAME',
574 p_token2_value =>l_deliverable_name,
575 p_token3 =>'ACTION_NAME',
576 p_token3_value =>l_action_name
577 );
578 close c_check_vendor;
579 raise FND_API.G_EXC_ERROR;
580 end if;
581 close c_check_vendor;
582 end if;
583 -- Check Vendor address
584 If p_ship_from_loc_id is not null then
585 If p_ship_from_org_id is not null then
586 Open c_check_vendor_site;
587 Fetch c_check_vendor_site into l_x;
588 if c_check_vendor_site%rowcount =0 then
589 oke_api.set_message(p_msg_name =>'OKE_INVALID_VENDOR_SITE',
590 p_token1 =>'PROJECT_NAME',
591 p_token1_value =>l_project_name,
592 p_token2 =>'DELIVERABLE_NAME',
593 p_token2_value =>l_deliverable_name,
594 p_token3 =>'ACTION_NAME',
595 p_token3_value =>l_action_name
596 );
597 close c_check_vendor_site;
598 raise FND_API.G_EXC_ERROR;
599 end if;
600 close c_check_vendor_site;
601 else
602 oke_api.set_message(p_msg_name =>'OKE_INVALID_VENDOR_SITE1',
603 p_token1 =>'PROJECT_NAME',
604 p_token1_value =>l_project_name,
605 p_token2 =>'DELIVERABLE_NAME',
606 p_token2_value =>l_deliverable_name,
607 p_token3 =>'ACTION_NAME',
608 p_token3_value =>l_action_name
609 );
610 raise FND_API.G_EXC_ERROR;
611 end if;
612 end if;
613 -- Check Exchange rate type
614 If p_exchange_rate_type is not null then
615 Open c_check_exchange_type;
616 Fetch c_check_exchange_type into l_x;
617 if c_check_exchange_type%rowcount =0 then
618 oke_api.set_message(p_msg_name =>'OKE_INVALID_EXCHANGE_TYPE',
619 p_token1 =>'PROJECT_NAME',
620 p_token1_value =>l_project_name,
621 p_token2 =>'DELIVERABLE_NAME',
622 p_token2_value =>l_deliverable_name,
623 p_token3 =>'ACTION_NAME',
624 p_token3_value =>l_action_name
625 );
626 close c_check_exchange_type;
627 raise FND_API.G_EXC_ERROR;
628 end if;
629 close c_check_exchange_type;
630 end if;
631 -- Check Exchange rate
632 If p_exchange_rate is not null and p_exchange_rate_type =G_USER then
633 oke_api.set_message(p_msg_name =>'OKE_INVALID_EXCHANGE_RATE',
634 p_token1 =>'PROJECT_NAME',
635 p_token1_value =>l_project_name,
636 p_token2 =>'DELIVERABLE_NAME',
637 p_token2_value =>l_deliverable_name,
638 p_token3 =>'ACTION_NAME',
639 p_token3_value =>l_action_name
640 );
641 raise FND_API.G_EXC_ERROR;
642 end if;
643 If p_exchange_rate is not null then
644 If p_exchange_rate_type <>'User' then
645 If p_exchange_rate_date is NULL or p_ship_to_org_id is null then
646 oke_api.set_message(p_msg_name =>'OKE_INVALID_EXCHANGE_RATE1',
647 p_token1 =>'PROJECT_NAME',
648 p_token1_value =>l_project_name,
649 p_token2 =>'DELIVERABLE_NAME',
650 p_token2_value =>l_deliverable_name,
651 p_token3 =>'ACTION_NAME',
652 p_token3_value =>l_action_name
653 );
654 raise FND_API.G_EXC_ERROR;
655 end if;
656 l_function_currency := OKE_ACTION_VALIDATIONS_PKG.functional_currency(p_ship_to_org_id);
657 Select nvl(p_currency_code,currency_code) into l_currency
658 from oke_deliverables_b
659 where deliverable_id=p_deliverable_id;
660 p_exchange_rate := OKE_ACTION_VALIDATIONS_PKG.exchange_rate(l_currency, l_function_currency, p_exchange_rate_type,p_exchange_rate_date);
661 else
662 p_exchange_rate_date := Null;
663 end if;
664 end if;
665 -- Check Expenditure type
666 If p_expenditure_type is not null then
667 Open c_check_expend_type;
668 Fetch c_check_expend_type into l_x;
669 if c_check_expend_type%rowcount =0 then
670 oke_api.set_message(p_msg_name =>'OKE_INVALID_EXPENDITURE_TYPE',
671 p_token1 =>'PROJECT_NAME',
672 p_token1_value =>l_project_name,
673 p_token2 =>'DELIVERABLE_NAME',
674 p_token2_value =>l_deliverable_name,
675 p_token3 =>'ACTION_NAME',
676 p_token3_value =>l_action_name
677 );
678 close c_check_expend_type;
679 raise FND_API.G_EXC_ERROR;
680 end if;
681 close c_check_expend_type;
682 end if;
683 -- Check Expenditure org
684 If p_expenditure_org_id is not null then
685 Open c_check_expend_org;
686 Fetch c_check_expend_org into l_x;
687 if c_check_expend_org%rowcount =0 then
688 oke_api.set_message(p_msg_name =>'OKE_INVALID_EXPENDITURE_ORG',
689 p_token1 =>'PROJECT_NAME',
690 p_token1_value =>l_project_name,
691 p_token2 =>'DELIVERABLE_NAME',
692 p_token2_value =>l_deliverable_name,
693 p_token3 =>'ACTION_NAME',
694 p_token3_value =>l_action_name
695 );
696 close c_check_expend_org;
697 raise FND_API.G_EXC_ERROR;
698 end if;
699 close c_check_expend_org;
700 end if;
701 -- Check Requisition Line type
702 If p_requisition_line_type_id is not null then
703 Open c_check_req_type;
704 Fetch c_check_req_type into l_x;
705 if c_check_req_type%rowcount =0 then
706 oke_api.set_message(p_msg_name =>'OKE_INVALID_REQ_TYPE',
707 p_token1 =>'PROJECT_NAME',
708 p_token1_value =>l_project_name,
709 p_token2 =>'DELIVERABLE_NAME',
710 p_token2_value =>l_deliverable_name,
711 p_token3 =>'ACTION_NAME',
712 p_token3_value =>l_action_name
713 );
714 close c_check_req_type;
715 raise FND_API.G_EXC_ERROR;
716 end if;
717 close c_check_req_type;
718 end if;
719 If p_category_id is not null then
720 Open c_check_category;
721 Fetch c_check_category into l_x;
722 if c_check_category%rowcount =0 then
723 oke_api.set_message(p_msg_name =>'OKE_INVALID_CATEGORY',
724 p_token1 =>'PROJECT_NAME',
725 p_token1_value =>l_project_name,
726 p_token2 =>'DELIVERABLE_NAME',
727 p_token2_value =>l_deliverable_name,
728 p_token3 =>'ACTION_NAME',
729 p_token3_value =>l_action_name
730 );
731 close c_check_category;
732 raise FND_API.G_EXC_ERROR;
733 end if;
734 close c_check_category;
735 --Check for Item Category:
736 end if;
737
738 elsif p_dlv_action_type=G_SHIP then
739 p_destination_type_code := Null;
740 p_po_need_by_date := Null;
741 p_exchange_rate_type := Null;
742 p_exchange_rate_date := Null;
743 p_exchange_rate := Null;
744 p_expenditure_type := Null;
745 p_expenditure_org_id := Null;
746 p_requisition_line_type_id := Null;
747 p_category_id := Null;
748 p_currency_code := Null;
749 p_unit_price := Null;
750
751
752 If p_item_dlv='Y' then
753 p_volume := Null;
754 p_volume_uom := Null;
755 p_weight := Null;
756 p_weight_uom := Null;
757 else
758 p_demand_schedule := Null;
759 end if;
760 -- Check UOM
761 If p_uom_code is not null then
762 Open c_check_uom;
763 fetch c_check_uom into l_x;
764 if c_check_uom%rowcount =0 then
765 oke_api.set_message(p_msg_name =>'OKE_CHECK_UOM',
766 p_token1 =>'PROJECT_NAME',
767 p_token1_value =>l_project_name,
768 p_token2 =>'DELIVERABLE_NAME',
769 p_token2_value =>l_deliverable_name
770 );
771 Close c_check_uom;
772 raise FND_API.G_EXC_ERROR;
773 end if;
774 Close c_check_uom;
775 end if;
776 --Check Demand Schedule
777 If p_demand_schedule is not null then
778 l_inv := null;
779 open c_get_inv_org;
780 fetch c_get_inv_org into l_inv;
781 close c_get_inv_org;
782 If l_inv is not null then
783 Open c_check_demand_schedule;
784 Fetch c_check_demand_schedule into l_x;
785 If c_check_demand_schedule%rowcount =0 then
786 oke_api.set_message(p_msg_name =>'OKE_INVALID_DEMAND_SCH',
787 p_token1 =>'PROJECT_NAME',
788 p_token1_value =>l_project_name,
789 p_token2 =>'DELIVERABLE_NAME',
790 p_token2_value =>l_deliverable_name,
791 p_token3 =>'ACTION_NAME',
792 p_token3_value =>l_action_name
793 );
794 close c_check_demand_schedule;
795 raise FND_API.G_EXC_ERROR;
796 end if;
797 close c_check_demand_schedule;
798 else
799 oke_api.set_message(p_msg_name =>'OKE_INV_ORG_B4_PLAN',
800 p_token1 =>'PROJECT_NAME',
801 p_token1_value =>l_project_name,
802 p_token2 =>'DELIVERABLE_NAME',
803 p_token2_value =>l_deliverable_name,
804 p_token3 =>'ACTION_NAME',
805 p_token3_value =>l_action_name
806 );
807 raise FND_API.G_EXC_ERROR;
808 end if;
809 end if;
810 --Check Ship From org
811 If p_ship_from_org_id is not null then
812 Open c_check_ship_from;
813 Fetch c_check_ship_from into l_x;
814 If c_check_ship_from%rowcount =0 then
815 oke_api.set_message(p_msg_name =>'OKE_INVALID_SHIP_FROM_ORG',
816 p_token1 =>'PROJECT_NAME',
817 p_token1_value =>l_project_name,
818 p_token2 =>'DELIVERABLE_NAME',
819 p_token2_value =>l_deliverable_name,
820 p_token3 =>'ACTION_NAME',
821 p_token3_value =>l_action_name
822 );
823 close c_check_ship_from;
824 raise FND_API.G_EXC_ERROR;
825 end if;
826 close c_check_ship_from;
827 end if;
828 --Check Ship From loc
829 If p_ship_from_loc_id is not null then
830 If p_ship_from_org_id is not null then
831 Open c_check_ship_from_loc;
832 Fetch c_check_ship_from_loc into l_x;
833 If c_check_ship_from_loc%rowcount =0 then
834 oke_api.set_message(p_msg_name =>'OKE_INVALID_SHIP_FROM_LOC',
835 p_token1 =>'PROJECT_NAME',
836 p_token1_value =>l_project_name,
837 p_token2 =>'DELIVERABLE_NAME',
838 p_token2_value =>l_deliverable_name,
839 p_token3 =>'ACTION_NAME',
840 p_token3_value =>l_action_name
841 );
842 close c_check_ship_from_loc;
843 raise FND_API.G_EXC_ERROR;
844 end if;
845 close c_check_ship_from_loc;
846 else
847 oke_api.set_message(p_msg_name =>'OKE_INVALID_SHIP_FROM_LOC1',
848 p_token1 =>'PROJECT_NAME',
849 p_token1_value =>l_project_name,
850 p_token2 =>'DELIVERABLE_NAME',
851 p_token2_value =>l_deliverable_name,
852 p_token3 =>'ACTION_NAME',
853 p_token3_value =>l_action_name
854 );
855 raise FND_API.G_EXC_ERROR;
856 end if;
857 end if;
858 -- Check Custome Account
859
860 If p_ship_to_org_id is not null then
861 Open c_check_customer;
862 Fetch c_check_customer into l_x;
863 If c_check_customer%rowcount =0 then
864 oke_api.set_message(p_msg_name =>'OKE_INVALID_CUSTOMER',
865 p_token1 =>'PROJECT_NAME',
866 p_token1_value =>l_project_name,
867 p_token2 =>'DELIVERABLE_NAME',
868 p_token2_value =>l_deliverable_name,
869 p_token3 =>'ACTION_NAME',
870 p_token3_value =>l_action_name
871 );
872 close c_check_customer;
873 raise FND_API.G_EXC_ERROR;
874 end if;
875 close c_check_customer;
876 end if;
877 --Check Customer Address
878 If p_ship_to_loc_id is not null then
879 If p_ship_to_org_id is not null then
880 Open c_check_cust_add;
881 Fetch c_check_cust_add into l_x;
882 If c_check_cust_add%rowcount =0 then
883 oke_api.set_message(p_msg_name =>'OKE_INVALID_CUST_ADD',
884 p_token1 =>'PROJECT_NAME',
885 p_token1_value =>l_project_name,
886 p_token2 =>'DELIVERABLE_NAME',
887 p_token2_value =>l_deliverable_name,
888 p_token3 =>'ACTION_NAME',
889 p_token3_value =>l_action_name
890 );
891 close c_check_cust_add;
892 raise FND_API.G_EXC_ERROR;
893 end if;
894 close c_check_cust_add;
895 else
896 oke_api.set_message(p_msg_name =>'OKE_INVALID_CUST_ADD1',
897 p_token1 =>'PROJECT_NAME',
898 p_token1_value =>l_project_name,
899 p_token2 =>'DELIVERABLE_NAME',
900 p_token2_value =>l_deliverable_name,
901 p_token3 =>'ACTION_NAME',
902 p_token3_value =>l_action_name
903 );
904 raise FND_API.G_EXC_ERROR;
905 end if;
906 end if;
907 If p_volume is not null or p_volume_uom is not null
908 or p_weight is not null or p_weight_uom is not null then
909 --Check Volume UOM
910 If p_volume_uom is not null then
911 Open c_check_volume_uom;
912 Fetch c_check_volume_uom into l_x;
913 If c_check_volume_uom%rowcount =0 then
914 oke_api.set_message(p_msg_name =>'OKE_INVALID_VOLUME_UOM',
915 p_token1 =>'PROJECT_NAME',
916 p_token1_value =>l_project_name,
917 p_token2 =>'DELIVERABLE_NAME',
918 p_token2_value =>l_deliverable_name,
919 p_token3 =>'ACTION_NAME',
920 p_token3_value =>l_action_name
921 );
922 close c_check_volume_uom;
923 raise FND_API.G_EXC_ERROR;
924 end if;
925 close c_check_volume_uom;
926 end if;
927 If p_weight_uom is not null then
928 Open c_check_weight_uom;
929 Fetch c_check_weight_uom into l_x;
930 If c_check_weight_uom%rowcount =0 then
931 oke_api.set_message(p_msg_name =>'OKE_INVALID_WEIGHT_UOM',
932 p_token1 =>'PROJECT_NAME',
933 p_token1_value =>l_project_name,
934 p_token2 =>'DELIVERABLE_NAME',
935 p_token2_value =>l_deliverable_name,
936 p_token3 =>'ACTION_NAME',
937 p_token3_value =>l_action_name
938 );
939 close c_check_weight_uom;
940 raise FND_API.G_EXC_ERROR;
941 end if;
942 close c_check_weight_uom;
943 end if;
944 end if;
945 end if;
946 EXCEPTION
947
948 WHEN FND_API.G_EXC_ERROR THEN
949 x_return_status := G_RET_STS_ERROR ;
950
951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
952 x_return_status := G_RET_STS_UNEXP_ERROR ;
953
954 WHEN OTHERS THEN
955 x_return_status := G_RET_STS_UNEXP_ERROR ;
956
957 End;
958
959
960 Procedure manage_dlv (
961 p_api_version IN Number,
962 p_init_msg_list IN Varchar2 default FND_API.G_FALSE,
963 p_commit IN Varchar2 default fnd_api.g_false,
964 p_action IN Varchar2,
965 p_item_dlv IN Varchar2,
966 p_master_inv_org_id IN Number,
967 p_dlv_rec IN OUT NOCOPY dlv_rec_type,
968 x_return_status OUT NOCOPY Varchar2,
969 x_msg_data OUT NOCOPY Varchar2,
970 x_msg_count OUT NOCOPY Number
971 ) IS
972 l_api_version CONSTANT NUMBER := 1;
973 l_api_name CONSTANT VARCHAR2(30) := 'manage_dlv';
974 l_deliverable_id NUMBER;
975 l_x Varchar2(1);
976 l_row_id varchar2(30);
977
978 CURSOR c_get_dlv_id IS
979 SELECT
980 deliverable_id from oke_deliverables_b
981 where project_id=p_dlv_rec.project_id
982 and source_deliverable_id=p_dlv_rec.pa_deliverable_id;
983
984 cursor c_check_action(b_del_id NUMBER) IS
985 SELECT 'x'
986 from oke_deliverable_actions
987 where deliverable_id= b_del_id
988 and reference1 > 0;
989 BEGIN
990
991
992 -- Standard Start of API savepoint
993 SAVEPOINT manage_dlv;
994
995 -- Standard call to check for call compatibility.
996 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 END IF;
999
1000 -- Initialize message list if p_init_msg_list is set to TRUE.
1001 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1002 FND_MSG_PUB.initialize;
1003 END IF;
1004
1005 -- Initialize API return status to success
1006 x_return_status := FND_API.G_RET_STS_SUCCESS;
1007
1008 If p_action not in (G_DEL,G_UPD,G_CRT) then
1009 oke_api.set_message(p_msg_name => OKE_API.G_INVALID_VALUE,
1010 p_token1 =>'COL_NAME',
1011 p_token1_value =>'p_action');
1012 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1013 end if;
1014
1015 l_project_name := get_project_name(p_dlv_rec.project_id);
1016 l_deliverable_name := p_dlv_rec.dlv_short_name;
1017
1018 If p_action in (G_DEL,G_UPD) then
1019 Open c_get_dlv_id;
1020 fetch c_get_dlv_id into l_deliverable_id;
1021
1022 If c_get_dlv_id%notfound then
1023 oke_api.set_message(p_msg_name =>OKE_API.G_INVALID_VALUE,
1024 p_token1 =>'COL_NAME',
1025 p_token1_value =>'source_deliverable_id'
1026 );
1027 close c_get_dlv_id;
1028 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1029 end if;
1030
1031 close c_get_dlv_id;
1032 Open c_check_action(l_deliverable_id);
1033 fetch c_check_action into l_x;
1034
1035 if c_check_action%found then
1036 oke_api.set_message(p_msg_name =>'OKE_DLV_ACTION_INITIATED',
1037 p_token1 =>'PROJECT_NAME',
1038 p_token1_value =>l_project_name,
1039 p_token2 =>'DELIVERABLE_NAME',
1040 p_token2_value =>l_deliverable_name
1041 );
1042 close c_check_action;
1043 raise FND_API.G_EXC_ERROR;
1044 end if;
1045 close c_check_action;
1046 end if;
1047
1048 If p_action in (G_CRT,G_UPD) then
1049
1050 validate_dlv_attr(
1051 p_master_inv_org_id => p_master_inv_org_id,
1052 p_item_id => p_dlv_rec.item_id,
1053 p_inventory_org_id => p_dlv_rec.inventory_org_id,
1054 p_currency_code => p_dlv_rec.currency_code,
1055 p_uom_code => p_dlv_rec.uom_code,
1056 p_dlv_short_name => p_dlv_rec.dlv_short_name,
1057 p_project_id => p_dlv_rec.project_id,
1058 p_quanitity => p_dlv_rec.quantity,
1059 p_unit_price => p_dlv_rec.unit_price,
1060 p_unit_number => p_dlv_rec.unit_number,
1061 p_quantity => p_dlv_rec.quantity,
1062 p_item_dlv => p_item_dlv,
1063 p_deliverable_id => l_deliverable_id,
1064 x_return_status => x_return_status);
1065
1066 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1068 elsif (x_return_status = G_RET_STS_ERROR) then
1069 RAISE FND_API.G_EXC_ERROR ;
1070 end if;
1071
1072 If p_action = G_CRT then
1073 select oke_k_deliverables_s.nextval into l_deliverable_id from dual;
1074 OKE_DELIVERABLES_PKG.insert_row(
1075 X_ROWID => l_row_id,
1076 X_DELIVERABLE_ID => l_deliverable_id,
1077 X_DELIVERABLE_NUMBER => p_dlv_rec.dlv_short_name,
1078 X_SOURCE_CODE => G_SOURCE_CODE,
1079 X_UNIT_PRICE => p_dlv_rec.unit_price,
1080 X_UOM_CODE => p_dlv_rec.uom_code,
1081 X_QUANTITY => p_dlv_rec.quantity,
1082 X_UNIT_NUMBER => p_dlv_rec.unit_number,
1083 X_ATTRIBUTE_CATEGORY => null,
1084 X_ATTRIBUTE1 => null,
1085 X_ATTRIBUTE2 => null,
1086 X_ATTRIBUTE3 => null,
1087 X_ATTRIBUTE4 => null,
1088 X_ATTRIBUTE5 => null,
1089 X_ATTRIBUTE6 => null,
1090 X_ATTRIBUTE7 => null,
1091 X_ATTRIBUTE8 => null,
1092 X_ATTRIBUTE9 => null,
1093 X_ATTRIBUTE10 => null,
1094 X_ATTRIBUTE11 => null,
1095 X_ATTRIBUTE12 => null,
1096 X_ATTRIBUTE13 => null,
1097 X_ATTRIBUTE14 => null,
1098 X_ATTRIBUTE15 => null,
1099 X_SOURCE_HEADER_ID => p_dlv_rec.project_id,
1100 X_SOURCE_LINE_ID => null,
1101 X_SOURCE_DELIVERABLE_ID => p_dlv_rec.pa_deliverable_id,
1102 X_PROJECT_ID => p_dlv_rec.project_id,
1103 X_CURRENCY_CODE => p_dlv_rec.currency_code,
1104 X_INVENTORY_ORG_ID => p_dlv_rec.inventory_org_id,
1105 X_DELIVERY_DATE => NULL,
1106 X_ITEM_ID => p_dlv_rec.item_id,
1107 X_DESCRIPTION => p_dlv_rec.dlv_description,
1108 X_COMMENTS => Null,
1109 X_CREATION_DATE => sysdate,
1110 X_CREATED_BY => Fnd_Global.User_Id,
1111 X_LAST_UPDATE_DATE => sysdate,
1112 X_LAST_UPDATED_BY => Fnd_Global.User_Id,
1113 X_LAST_UPDATE_LOGIN => Fnd_Global.login_id
1114 );
1115 elsif p_action=G_UPD then
1116
1117 update OKE_DELIVERABLES_B
1118 set
1119 CURRENCY_CODE = p_dlv_rec.currency_code,
1120 UNIT_PRICE = p_dlv_rec.unit_price,
1121 UOM_CODE = p_dlv_rec.uom_code,
1122 QUANTITY = p_dlv_rec.quantity,
1123 UNIT_NUMBER = p_dlv_rec.unit_number,
1124 DELIVERABLE_NUMBER= p_dlv_rec.dlv_short_name,
1125 PROJECT_ID = p_dlv_rec.project_id,
1126 ITEM_ID = p_dlv_rec.item_id,
1127 SOURCE_HEADER_ID = p_dlv_rec.project_id,
1128 INVENTORY_ORG_ID = p_dlv_rec.inventory_org_id,
1129 SOURCE_CODE = 'PA',
1130 SOURCE_DELIVERABLE_ID = p_dlv_rec.pa_deliverable_id,
1131 LAST_UPDATE_DATE = sysdate,
1132 LAST_UPDATED_BY = fnd_globaL.user_id,
1133 LAST_UPDATE_LOGIN = fnd_global.login_id
1134 where DELIVERABLE_ID = l_deliverable_id;
1135 if (sql%notfound) then
1136 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1137 end if;
1138
1139 update OKE_DELIVERABLES_TL set
1140 DESCRIPTION = p_dlv_rec.dlv_description,
1141 LAST_UPDATE_DATE = sysdate,
1142 LAST_UPDATED_BY = fnd_globaL.user_id,
1143 LAST_UPDATE_LOGIN = fnd_global.login_id,
1144 SOURCE_LANG = userenv('LANG')
1145 where DELIVERABLE_ID = l_deliverable_id
1146 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1147
1148 If p_item_dlv ='Y' then
1149
1150 update OKE_DELIVERABLE_actions
1151 set
1152 CURRENCY_CODE = decode(action_type,G_REQ,p_dlv_rec.currency_code,currency_code),
1153 UNIT_PRICE = decode(action_type,G_REQ,p_dlv_rec.unit_price,unit_price),
1154 UOM_CODE = p_dlv_rec.uom_code,
1155 QUANTITY = p_dlv_rec.quantity,
1156 LAST_UPDATE_DATE = sysdate,
1157 LAST_UPDATED_BY = fnd_globaL.user_id,
1158 LAST_UPDATE_LOGIN = fnd_global.login_id
1159 where DELIVERABLE_ID = l_deliverable_id;
1160
1161 end if;
1162 end if;
1163 end if;
1164
1165 if p_action = G_DEL then
1166 if p_dlv_rec.pa_deliverable_id is null then
1167 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1168 end if;
1169 OKE_DELIVERABLE_ACTIONS_PKG.delete_deliverable(p_dlv_rec.pa_deliverable_id);
1170 end if;
1171 If fnd_api.to_boolean( p_commit ) then
1172 commit work;
1173 end if;
1174
1175 -- Standard call to get message count and if count is 1, get message info.
1176 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1177
1178 EXCEPTION
1179
1180 WHEN FND_API.G_EXC_ERROR THEN
1181
1182 ROLLBACK TO manage_dlv;
1183 x_return_status := G_RET_STS_ERROR ;
1184 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1185
1186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1187
1188 ROLLBACK TO manage_dlv;
1189 x_return_status := G_RET_STS_UNEXP_ERROR ;
1190 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1191
1192 WHEN OTHERS THEN
1193
1194 ROLLBACK TO manage_dlv;
1195 x_return_status := G_RET_STS_UNEXP_ERROR ;
1196 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1197 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1198 END IF;
1199 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1200 END;
1201
1202 Procedure manage_dlv_action(
1203 p_api_version IN Number,
1204 p_init_msg_list IN Varchar2 default fnd_api.g_false,
1205 p_commit IN Varchar2 default fnd_api.g_false,
1206 p_action IN Varchar2,
1207 p_item_dlv IN Varchar2,
1208 p_master_inv_org_id IN Number,
1209 p_dlv_action_type IN Varchar2,
1210 p_dlv_ship_action_rec IN OUT NOCOPY dlv_ship_action_rec_type,
1211 p_dlv_req_action_rec IN OUT NOCOPY dlv_req_action_rec_type,
1212 x_return_status OUT NOCOPY Varchar2,
1213 x_msg_data OUT NOCOPY Varchar2,
1214 x_msg_count OUT NOCOPY Number
1215 ) IS
1216 l_api_version CONSTANT NUMBER := 1;
1217 l_api_name CONSTANT VARCHAR2(30) := 'manage_dlv_action';
1218 l_action_id NUMBER;
1219 l_deliverable_id NUMBER;
1220 l_project_id NUMBER;
1221 l_x Varchar2(1);
1222 l_pa_action_id Number;
1223 l_pa_deliverable_id Number;
1224 l_inv_org_id number;
1225 l_currency_code varchar2(30);
1226 l_quantity number;
1227 l_demand_schedule varchar2(10);
1228 l_ship_from_org_id number;
1229 l_ship_from_loc_id number;
1230 l_ship_to_org_id number;
1231 l_item_org_id number;
1232 l_ship_to_loc_id number;
1233 l_volume NUMBER;
1234 l_volume_uom VARCHAR2(30);
1235 l_weight NUMBER;
1236 l_weight_uom VARCHAR2(30);
1237 l_unit_price number;
1238 l_destination_type_code VARCHAR2(30);
1239 l_po_need_by_date DATE;
1240 l_exchange_rate_type VARCHAR2(30);
1241 l_exchange_rate NUMBER;
1242 l_exchange_rate_date DATE;
1243 l_expenditure_type VARCHAR2(30);
1244 l_expenditure_org_id NUMBER;
1245 l_EXPENDITURE_ITEM_DATE date;
1246 l_requisition_line_type_id NUMBER;
1247 l_category_id NUMBER;
1248 l_ready_to_procure_flag VARCHAR2(1);
1249 l_ready_to_ship_flag VARCHAR2(1);
1250 l_task_id Number;
1251 l_promised_shipment_date DATE ;
1252 l_expected_shipment_date DATE;
1253 l_INSPECTION_REQ_FLAG varchar2(1);
1254 l_uom_code Varchar2(30);
1255
1256
1257 cursor c_get_actions_ids(b_pa_action_id number) is
1258 Select act.action_id,
1259 act.deliverable_id,
1260 del.project_id
1261 from oke_deliverable_actions act , oke_deliverables_b del
1262 where act.deliverable_id = del.deliverable_id
1263 and pa_action_id= b_pa_action_id;
1264
1265
1266 cursor c_get_del_ids(b_pa_deliverable_id number) is
1267 Select deliverable_id,
1268 project_id
1269 from oke_deliverables_b
1270 where source_deliverable_id = b_pa_deliverable_id;
1271
1272 Cursor c_get_action_status(b_action_id number) is
1273 Select 'x' from oke_deliverable_actions
1274 where action_id=b_action_id and reference1>0;
1275
1276 Cursor c_get_inv_org(b_deliverable_id number) is
1277 select inventory_org_id from oke_deliverables_b where deliverable_id=b_deliverable_id;
1278
1279 Cursor c_get_item_dtl(b_dlv_id number) is
1280 select uom_code,currency_code,unit_price,quantity,inventory_org_id from oke_deliverables_b
1281 where deliverable_id=b_dlv_id ;
1282 BEGIN
1283
1284
1285 -- Standard Start of API savepoint
1286 SAVEPOINT manage_dlv_action;
1287
1288 -- Standard call to check for call compatibility.
1289 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1291 END IF;
1292
1293 -- Initialize message list if p_init_msg_list is set to TRUE.
1294 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1295 FND_MSG_PUB.initialize;
1296 END IF;
1297
1298 -- Initialize API return status to success
1299 x_return_status := FND_API.G_RET_STS_SUCCESS;
1300
1301 If p_action not in (G_CRT,G_DEL,G_UPD) then
1302 oke_api.set_message(p_msg_name => OKE_API.G_INVALID_VALUE,
1303 p_token1 =>'COL_NAME',
1304 p_token1_value =>'p_action');
1305 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1306 end if;
1307
1308 If p_dlv_action_type not in (G_SHIP,G_REQ) then
1309 oke_api.set_message(p_msg_name => OKE_API.G_INVALID_VALUE,
1310 p_token1 =>'COL_NAME',
1311 p_token1_value =>'p_dlv_action_type');
1312 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1313 end if;
1314
1315 Select decode(p_dlv_action_type,G_SHIP,p_dlv_ship_action_rec.pa_action_id,G_REQ, p_dlv_req_action_rec.pa_action_id,NULL),
1316 decode(p_dlv_action_type,G_SHIP,p_dlv_ship_action_rec.pa_deliverable_id,G_REQ, p_dlv_req_action_rec.pa_deliverable_id,NULL),
1317 decode(p_dlv_action_type,G_SHIP,p_dlv_ship_action_rec.action_name,G_REQ, p_dlv_req_action_rec.action_name,NULL)
1318 into l_pa_action_id,l_pa_deliverable_id,l_action_name from dual;
1319
1320 If l_pa_action_id is null then
1321 oke_api.set_message(p_msg_name => OKE_API.G_INVALID_VALUE,
1322 p_token1 =>'COL_NAME',
1323 p_token1_value =>'pa_action_id');
1324 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1325 end if;
1326
1327 If p_action in (G_DEL,G_UPD) then
1328 Open c_get_actions_ids(l_pa_action_id);
1329 Fetch c_get_actions_ids into l_action_id, l_deliverable_id,l_project_id;
1330
1331 If l_action_id is null then
1332 oke_api.set_message(p_msg_name => OKE_API.G_INVALID_VALUE,
1333 p_token1 =>'COL_NAME',
1334 p_token1_value =>'pa_action_id');
1335 close c_get_actions_ids;
1336 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1337 end if;
1338 close c_get_actions_ids;
1339 else
1340 Open c_get_del_ids(l_pa_deliverable_id);
1341 Fetch c_get_del_ids into l_deliverable_id,l_project_id;
1342
1343 If l_deliverable_id is null then
1344 oke_api.set_message(p_msg_name => OKE_API.G_INVALID_VALUE,
1345 p_token1 =>'COL_NAME',
1346 p_token1_value =>'pa_deliverable_id');
1347 close c_get_del_ids;
1348 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1349 end if;
1350 close c_get_del_ids;
1351 end if;
1352
1353 l_project_name := get_project_name(l_project_id);
1354 l_deliverable_name := get_dlv_name(l_deliverable_id);
1355
1356 If p_action in (G_DEL,G_UPD) then
1357 Open c_get_action_status(l_action_id);
1358 fetch c_get_action_status into l_x;
1359 If c_get_action_status%rowcount > 0 then
1360 oke_api.set_message(p_msg_name =>'OKE_DLV_ACTION_INITIATED',
1361 p_token1 =>'PROJECT_NAME',
1362 p_token1_value =>l_project_name,
1363 p_token2 =>'DELIVERABLE_NAME',
1364 p_token2_value =>l_deliverable_name,
1365 p_token3 =>'ACTION_NAME',
1366 p_token3_value =>l_action_name
1367 );
1368 raise FND_API.G_EXC_ERROR;
1369 end if;
1370 close c_get_action_status;
1371
1372 If p_action = G_DEL then
1373 If OKE_DELIVERABLE_UTILS_PUB.Action_Deletable_Yn(l_pa_action_id)='Y' then
1374 OKE_DELIVERABLE_ACTIONS_PKG.Delete_action(l_pa_action_id);
1375 else
1376 oke_api.set_message(p_msg_name =>'OKE_DLV_ACTION_INITIATED',
1377 p_token1 =>'PROJECT_NAME',
1378 p_token1_value =>l_project_name,
1379 p_token2 =>'DELIVERABLE_NAME',
1380 p_token2_value =>l_deliverable_name,
1381 p_token3 =>'ACTION_NAME',
1382 p_token3_value =>l_action_name
1383 );
1384 raise FND_API.G_EXC_ERROR;
1385 end if;
1386 end if;
1387 end if;
1388
1389 If p_action in (G_CRT,G_UPD) then
1390
1391 If p_dlv_action_type = G_SHIP then
1392
1393 l_action_name := p_dlv_ship_action_rec.action_name;
1394 l_demand_schedule := p_dlv_ship_action_rec.demand_schedule;
1395 l_ship_from_org_id := p_dlv_ship_action_rec.ship_from_organization_id;
1396 l_ship_from_loc_id := p_dlv_ship_action_rec.ship_from_location_id;
1397 l_ship_to_org_id := p_dlv_ship_action_rec.ship_to_organization_id;
1398 l_ship_to_loc_id := p_dlv_ship_action_rec.ship_to_location_id;
1399 l_volume := p_dlv_ship_action_rec.volume;
1400 l_volume_uom := p_dlv_ship_action_rec.volume_uom;
1401 l_weight := p_dlv_ship_action_rec.weight;
1402 l_weight_uom := p_dlv_ship_action_rec.weight_uom;
1403 l_promised_shipment_date := p_dlv_ship_action_rec.promised_shipment_date;
1404 l_expected_shipment_date := p_dlv_ship_action_rec.expected_shipment_date;
1405 l_inspection_req_flag := p_dlv_ship_action_rec.inspection_req_flag;
1406 l_ready_to_ship_flag := p_dlv_ship_action_rec.ready_to_ship_flag;
1407 l_task_id := p_dlv_ship_action_rec.ship_finnancial_task_id;
1408 l_quantity := p_dlv_ship_action_rec.quantity;
1409 l_uom_code := p_dlv_ship_action_rec.uom_code;
1410
1411 elsif p_dlv_action_type = G_REQ then
1412
1413 l_action_name := p_dlv_req_action_rec.action_name;
1414 l_quantity := p_dlv_req_action_rec.quantity;
1415 l_uom_code := p_dlv_req_action_rec.uom_code;
1416 l_ship_from_org_id := p_dlv_req_action_rec.vendor_id;
1417 l_ship_from_loc_id := p_dlv_req_action_rec.vendor_site_id;
1418 l_ship_to_org_id := p_dlv_req_action_rec.receiving_org_id;
1419 l_ship_to_loc_id := p_dlv_req_action_rec.receiving_location_id;
1420 l_unit_price := p_dlv_req_action_rec.unit_price;
1421 l_currency_code := p_dlv_req_action_rec.currency;
1422 l_destination_type_code := p_dlv_req_action_rec.destination_type_code;
1423 l_po_need_by_date := p_dlv_req_action_rec.po_need_by_date;
1424 l_exchange_rate_type := p_dlv_req_action_rec.exchange_rate_type;
1425 l_exchange_rate_date := p_dlv_req_action_rec.exchange_rate_date;
1426 l_exchange_rate := p_dlv_req_action_rec.exchange_rate;
1427 l_expenditure_type := p_dlv_req_action_rec.expenditure_type;
1428 l_expenditure_org_id := p_dlv_req_action_rec.expenditure_org_id;
1429 l_requisition_line_type_id := p_dlv_req_action_rec.requisition_line_type_id;
1430 l_category_id := p_dlv_req_action_rec.category_id;
1431 l_ready_to_procure_flag := p_dlv_req_action_rec.ready_to_procure_flag;
1432 l_task_id := p_dlv_req_action_rec.proc_finnancial_task_id;
1433 l_EXPENDITURE_ITEM_DATE := p_dlv_req_action_rec.EXPENDITURE_ITEM_DATE;
1434
1435 end if;
1436
1437 open c_get_inv_org(l_deliverable_id);
1438 fetch c_get_inv_org into l_inv_org_id;
1439 close c_get_inv_org;
1440 If p_item_dlv ='Y' then
1441 Open c_get_item_dtl(l_deliverable_id);
1442 fetch c_get_item_dtl into l_uom_code,l_currency_code,l_unit_price,l_quantity,l_item_org_id;
1443 close c_get_item_dtl;
1444 If p_dlv_action_type=G_SHIP then
1445 l_currency_code := null;
1446 l_unit_price := null;
1447 l_ship_from_org_id := l_item_org_id;
1448 end if;
1449 If p_dlv_action_type=G_REQ then
1450 l_ship_to_org_id := l_item_org_id;
1451 end if;
1452 end if;
1453
1454
1455 VALIDATE_DLV_ACTION_ATTR(
1456 p_dlv_action_type => p_dlv_action_type,
1457 p_item_dlv => p_item_dlv,
1458 p_inv_org_id => l_inv_org_id,
1459 p_deliverable_id => l_deliverable_id,
1460 p_currency_code => l_currency_code,
1461 p_quantity => l_quantity,
1462 p_demand_schedule => l_demand_schedule,
1463 p_ship_from_org_id => l_ship_from_org_id,
1464 p_ship_from_loc_id => l_ship_from_loc_id,
1465 p_ship_to_org_id => l_ship_to_org_id,
1466 p_ship_to_loc_id => l_ship_to_loc_id,
1467 p_volume => l_volume,
1468 p_volume_uom => l_volume_uom,
1469 p_weight => l_weight,
1470 p_weight_uom => l_weight_uom,
1471 p_destination_type_code => l_destination_type_code,
1472 p_po_need_by_date => l_po_need_by_date,
1473 p_exchange_rate_type => l_exchange_rate_type,
1474 p_exchange_rate => l_exchange_rate,
1475 P_exchange_rate_date => l_exchange_rate_date,
1476 p_expenditure_type => l_expenditure_type,
1477 p_expenditure_org_id => l_expenditure_org_id,
1478 p_requisition_line_type_id => l_requisition_line_type_id,
1479 p_category_id => l_category_id,
1480 p_uom_code => l_uom_code,
1481 p_currency => l_currency_code,
1482 p_unit_price => l_unit_price,
1483 x_return_status => x_return_status);
1484
1485 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1487 elsif (x_return_status = G_RET_STS_ERROR) then
1488 RAISE FND_API.G_EXC_ERROR ;
1489 end if;
1490
1491 If p_action=G_CRT then
1492
1493 Select oke_k_deliverables_S.nextval into l_action_id from dual;
1494 Insert into oke_deliverable_actions(ACTION_ID,
1495 CREATION_DATE,
1496 CREATED_BY ,
1497 LAST_UPDATE_DATE,
1498 LAST_UPDATED_BY ,
1499 LAST_UPDATE_LOGIN,
1500 ACTION_TYPE ,
1501 ACTION_NAME ,
1502 PA_ACTION_ID ,
1503 TASK_ID ,
1504 DELIVERABLE_ID,
1505 SHIP_TO_ORG_ID ,
1506 SHIP_TO_LOCATION_ID,
1507 SHIP_FROM_ORG_ID ,
1508 SHIP_FROM_LOCATION_ID,
1509 INSPECTION_REQ_FLAG,
1510 EXPECTED_DATE ,
1511 PROMISED_DATE ,
1512 SCHEDULE_DESIGNATOR,
1513 VOLUME ,
1514 VOLUME_UOM_CODE,
1515 WEIGHT ,
1516 WEIGHT_UOM_CODE,
1517 EXPENDITURE_ORGANIZATION_ID,
1518 EXPENDITURE_TYPE ,
1519 EXPENDITURE_ITEM_DATE,
1520 DESTINATION_TYPE_CODE ,
1521 RATE_TYPE ,
1522 RATE_DATE ,
1523 EXCHANGE_RATE,
1524 REQUISITION_LINE_TYPE_ID,
1525 PO_CATEGORY_ID,
1526 quantity,
1527 uom_code,
1528 unit_price,
1529 currency_code)
1530 Values(l_ACTION_ID,
1531 sysdate,
1532 fnd_global.user_id ,
1533 sysdate,
1534 fnd_global.user_id ,
1535 fnd_global.login_id,
1536 p_dlv_action_type ,
1537 l_ACTION_NAME ,
1538 l_PA_ACTION_ID ,
1539 l_TASK_ID ,
1540 l_DELIVERABLE_ID,
1541 l_SHIP_TO_ORG_ID ,
1542 l_SHIP_TO_LOC_ID,
1543 l_SHIP_FROM_ORG_ID ,
1544 l_SHIP_FROM_LOC_ID,
1545 l_INSPECTION_REQ_FLAG,
1546 decode(p_dlv_action_type,G_REQ,l_po_need_by_date,G_SHIP,l_expected_shipment_date),
1547 l_PROMISED_shipment_DATE ,
1548 l_demand_SCHEDULE,
1549 l_VOLUME ,
1550 l_VOLUME_UOM,
1551 l_WEIGHT ,
1552 l_WEIGHT_UOM,
1553 l_EXPENDITURE_ORG_ID,
1554 l_EXPENDITURE_TYPE ,
1555 l_EXPENDITURE_ITEM_DATE,
1556 l_DESTINATION_TYPE_CODE ,
1557 l_exchange_RATE_TYPE ,
1558 l_exchange_rate_date ,
1559 l_EXCHANGE_RATE,
1560 l_REQUISITION_LINE_TYPE_ID,
1561 l_CATEGORY_ID,
1562 l_quantity,
1563 l_uom_code,
1564 l_unit_price,
1565 l_currency_code);
1566
1567 end if;
1568 If p_action='UPDATE' then
1569 update oke_deliverable_actions set
1570 LAST_UPDATE_DATE = sysdate,
1571 LAST_UPDATED_BY = fnd_global.user_id ,
1572 LAST_UPDATE_LOGIN = fnd_global.login_id,
1573 ACTION_TYPE = p_dlv_action_type ,
1574 ACTION_NAME = l_ACTION_NAME,
1575 PA_ACTION_ID = l_PA_ACTION_ID,
1576 TASK_ID = l_TASK_ID,
1577 DELIVERABLE_ID = l_DELIVERABLE_ID,
1578 SHIP_TO_ORG_ID = l_SHIP_TO_ORG_ID,
1579 SHIP_TO_LOCATION_ID = l_SHIP_TO_LOC_ID,
1580 SHIP_FROM_ORG_ID = l_SHIP_FROM_ORG_ID,
1581 SHIP_FROM_LOCATION_ID = l_SHIP_FROM_LOC_ID,
1582 INSPECTION_REQ_FLAG = l_INSPECTION_REQ_FLAG,
1583 EXPECTED_DATE = decode(p_dlv_action_type,G_REQ,l_po_need_by_date,G_SHIP,l_expected_shipment_date),
1584 PROMISED_DATE = l_promised_shipment_date,
1585 SCHEDULE_DESIGNATOR = l_demand_schedule,
1586 VOLUME = l_VOLUME,
1587 VOLUME_UOM_CODE = l_VOLUME_UOM,
1588 WEIGHT = l_WEIGHT,
1589 WEIGHT_UOM_CODE = l_WEIGHT_UOM,
1590 EXPENDITURE_ORGANIZATION_ID = l_EXPENDITURE_ORG_ID,
1591 EXPENDITURE_TYPE = l_EXPENDITURE_TYPE,
1592 EXPENDITURE_ITEM_DATE = l_EXPENDITURE_ITEM_DATE,
1593 DESTINATION_TYPE_CODE = l_DESTINATION_TYPE_CODE,
1594 RATE_TYPE = l_EXCHANGE_RATE_TYPE,
1595 RATE_DATE = l_EXCHANGE_RATE_DATE,
1596 EXCHANGE_RATE = l_EXCHANGE_RATE,
1597 REQUISITION_LINE_TYPE_ID = l_REQUISITION_LINE_TYPE_ID,
1598 PO_CATEGORY_ID = l_CATEGORY_ID,
1599 quantity = l_quantity,
1600 uom_code = l_uom_code,
1601 unit_price = l_unit_price,
1602 currency_code = l_currency_code
1603 where action_id=l_action_id;
1604
1605
1606 -- Update all columns of oke_deliverable_actions;
1607 end if;
1608 /*
1609 If p_dlv_action_type=G_REQ and p_item_dlv <> 'Y' then
1610 update oke_deliverables_b set quantity = l_quantity,
1611 unit_price = l_unit_price,
1612 currency_code = l_currency_code,
1613 uom_code = l_uom_code
1614 where deliverable_id = l_deliverable_id;
1615 --Update oke_deliverables_b to update quantity, currency, uom and unit_price;
1616 end if;
1617 */
1618 If p_dlv_action_type =G_REQ and p_dlv_req_action_rec.ready_to_procure_flag='Y' then
1619
1620
1621
1622 If OKE_ACTION_VALIDATIONS_PKG.Validate_Req( P_Action_ID => l_action_id
1623 , P_Deliverable_ID => l_deliverable_id
1624 , P_Task_ID => l_task_id
1625 , P_Ship_From_Org_ID => l_Ship_From_Org_ID
1626 , P_Ship_From_Location_ID => l_Ship_From_loc_ID
1627 , P_Ship_To_Org_ID => l_Ship_To_Org_ID
1628 , P_Ship_To_Location_ID => l_Ship_To_Loc_ID
1629 , P_Expected_Date => l_po_need_by_date
1630 , P_Destination_Type_Code => l_destination_type_code
1631 , P_Requisition_Line_Type_ID => l_requisition_line_type_id
1632 , P_Category_ID => l_category_id
1633 , P_Currency_Code => l_currency_code
1634 , P_Quantity => l_quantity
1635 , p_uom_code => l_uom_code
1636 , P_Unit_Price => l_unit_price
1637 , P_Rate_Type => l_exchange_rate_type
1638 , P_Rate_Date => l_exchange_rate_date
1639 , P_Exchange_Rate => l_exchange_rate
1640 , P_Expenditure_Type_Code => l_expenditure_type
1641 , P_Expenditure_Organization_Id => l_Expenditure_Org_Id
1642 , P_Expenditure_Item_Date => l_expenditure_item_DATE ) ='Y' then
1643 update oke_deliverable_actions set
1644 LAST_UPDATE_DATE = sysdate,
1645 LAST_UPDATED_BY = fnd_global.user_id ,
1646 LAST_UPDATE_LOGIN = fnd_global.login_id,
1647 READY_FLAG = 'Y'
1648 where action_id=l_action_id;
1649 else
1650 raise FND_API.G_EXC_ERROR ;
1651 end if;
1652 end if;
1653 If p_dlv_action_type =G_SHIP and p_dlv_ship_action_rec.ready_to_ship_flag='Y' then
1654 If OKE_ACTION_VALIDATIONS_PKG.Validate_Wsh( P_Action_ID => l_action_id
1655 , P_Deliverable_ID => l_deliverable_id
1656 , P_Task_ID => l_task_id
1657 , P_Ship_From_Org_ID => l_Ship_From_Org_ID
1658 , P_Ship_From_Location_ID => l_Ship_From_loc_ID
1659 , P_Ship_To_Org_ID => l_Ship_To_Org_ID
1660 , P_Ship_To_Location_ID => l_Ship_To_Loc_ID
1661 , P_Expected_Date => sysdate
1662 , P_volume => l_volume
1663 , P_volume_uom => l_volume_uom
1664 , P_weight => l_weight
1665 , P_weight_uom => l_weight_uom
1666 , P_quantity => l_quantity
1667 , P_uom_code => l_uom_code
1668 ) ='Y' then
1669 update oke_deliverable_actions set
1670 LAST_UPDATE_DATE = sysdate,
1671 LAST_UPDATED_BY = fnd_global.user_id ,
1672 LAST_UPDATE_LOGIN = fnd_global.login_id,
1673 READY_FLAG = 'Y'
1674 where action_id=l_action_id;
1675 else
1676 raise FND_API.G_EXC_ERROR ;
1677 end if;
1678 end if;
1679 If p_dlv_action_type =G_SHIP then
1680 If p_dlv_ship_action_rec.initiate_planning_flag ='Y' then
1681 initiate_dlv_action(
1682 p_api_version => 1,
1683 p_pa_action_id => l_pa_action_id,
1684 p_dlv_action_type => G_MDS,
1685 x_return_status => x_return_status,
1686 x_msg_data => x_msg_data,
1687 x_msg_count => x_msg_count);
1688 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1690 elsif (x_return_status = G_RET_STS_ERROR) then
1691 RAISE FND_API.G_EXC_ERROR ;
1692 end if;
1693 end if;
1694 If p_dlv_ship_action_rec.initiate_shipping_flag ='Y' then
1695 initiate_dlv_action (
1696 p_api_version => 1,
1697 p_pa_action_id => l_pa_action_id,
1698 p_dlv_action_type => G_SHIP,
1699 x_return_status => x_return_status,
1700 x_msg_data => x_msg_data,
1701 x_msg_count => x_msg_count);
1702 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1703 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1704 elsif (x_return_status = G_RET_STS_ERROR) then
1705 RAISE FND_API.G_EXC_ERROR ;
1706 end if;
1707 end if;
1708 end if;
1709 If p_dlv_action_type =G_REQ and p_dlv_req_action_rec.initiate_procure_flag ='Y' then
1710 initiate_dlv_action (
1711 p_api_version => 1,
1712 p_pa_action_id => l_pa_action_id,
1713 p_dlv_action_type => G_REQ,
1714 x_return_status => x_return_status,
1715 x_msg_data => x_msg_data,
1716 x_msg_count => x_msg_count);
1717 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1719 elsif (x_return_status = G_RET_STS_ERROR) then
1720 RAISE FND_API.G_EXC_ERROR ;
1721 end if;
1722 end if;
1723 end if;
1724 If fnd_api.to_boolean( p_commit ) then
1725 commit work;
1726 end if;
1727
1728 -- Standard call to get message count and if count is 1, get message info.
1729 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1730 EXCEPTION
1731
1732 WHEN FND_API.G_EXC_ERROR THEN
1733
1734 ROLLBACK TO manage_dlv_action;
1735 x_return_status := G_RET_STS_ERROR ;
1736 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1737
1738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1739
1740 ROLLBACK TO manage_dlv_action;
1741 x_return_status := G_RET_STS_UNEXP_ERROR ;
1742 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1743
1744 WHEN OTHERS THEN
1745
1746 ROLLBACK TO manage_dlv_action;
1747 x_return_status := G_RET_STS_UNEXP_ERROR ;
1748 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1749 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1750 END IF;
1751 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1752 END;
1753
1754
1755
1756 Procedure initiate_dlv_action(
1757 p_api_version IN Number,
1758 p_init_msg_list IN Varchar2 default fnd_api.g_false,
1759 p_commit IN Varchar2 default fnd_api.g_false,
1760 p_pa_action_id IN Number,
1761 p_dlv_action_type IN Varchar2,
1762 x_return_status OUT NOCOPY Varchar2,
1763 x_msg_data OUT NOCOPY Varchar2,
1764 x_msg_count OUT NOCOPY Number
1765 ) IS
1766 l_api_version CONSTANT NUMBER := 1;
1767 l_api_name CONSTANT VARCHAR2(30) := 'initiate_dlv_action';
1768 Cursor c_get_detail(b_pa_action_id number) is
1769 Select act.deliverable_id,
1770 action_id,
1771 Task_ID,
1772 Ship_From_Org_ID,
1773 Ship_From_Location_ID,
1774 Ship_To_Org_ID,
1775 Ship_To_Location_ID,
1776 Schedule_Designator,
1777 Expected_Date,
1778 ready_flag,
1779 action_name,
1780 dlv.project_id,
1781 act.quantity,
1782 act.uom_code
1783 from oke_deliverable_actions act,oke_deliverables_b dlv
1784 where pa_action_id=b_pa_action_id
1785 and dlv.deliverable_id = act.deliverable_id;
1786
1787 l_deliverable_id Number;
1788 l_Task_ID Number;
1789 l_Ship_From_Org_ID Number;
1790 l_Ship_From_Location_ID Number;
1791 l_Ship_To_Org_ID Number;
1792 l_Ship_To_Location_ID Number;
1793 l_Schedule_Designator Varchar2(10);
1794 l_Expected_Date Date;
1795 l_ready_flag Varchar2(1);
1796 l_action_id Number;
1797 l_out_id Number;
1798 l_action_name Varchar2(240);
1799 l_project_id Number;
1800 l_project_name Varchar2(30);
1801 l_quantity Number;
1802 l_uom_code Varchar2(30);
1803 l_deliverable_name varchar2(100);
1804 BEGIN
1805
1806 SAVEPOINT initiate_dlv_action;
1807 -- Standard call to check for call compatibility.
1808 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1810 END IF;
1811
1812 -- Initialize message list if p_init_msg_list is set to TRUE.
1813 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1814 FND_MSG_PUB.initialize;
1815 END IF;
1816
1817 -- Initialize API return status to success
1818 x_return_status := FND_API.G_RET_STS_SUCCESS;
1819
1820 If p_dlv_action_type not in (G_SHIP,G_REQ,G_MDS) then
1821 oke_api.set_message(p_msg_name => OKE_API.G_INVALID_VALUE,
1822 p_token1 =>'COL_NAME',
1823 p_token1_value =>'p_dlv_action_type');
1824 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1825 End if;
1826
1827 Open c_get_detail(p_pa_action_id );
1828 Fetch c_get_detail into l_deliverable_id,
1829 l_action_id,
1830 l_Task_ID,
1831 l_Ship_From_Org_ID,
1832 l_Ship_From_Location_ID,
1833 l_Ship_To_Org_ID,
1834 l_Ship_To_Location_ID,
1835 l_Schedule_Designator,
1836 l_Expected_Date,
1837 l_ready_flag,
1838 l_action_name,
1839 l_project_id,
1840 l_quantity,
1841 l_uom_code ;
1842 If c_get_detail%rowcount=0 then
1843 close c_get_detail;
1844 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1845 end if;
1846 close c_get_detail;
1847
1848 l_deliverable_name := get_dlv_name(l_deliverable_id);
1849 l_project_name := get_project_name(l_project_id);
1850
1851 If p_dlv_action_type =G_MDS then
1852 If OKE_ACTION_VALIDATIONS_PKG.Validate_mds(P_Action_ID => l_action_id,
1853 P_Deliverable_ID => l_deliverable_id,
1854 P_Task_ID => l_task_id,
1855 P_Ship_From_Org_ID => l_ship_from_org_id,
1856 P_Ship_From_Location_ID=> l_ship_from_Location_id,
1857 P_Ship_To_Org_ID => l_ship_to_org_id,
1858 P_Ship_To_Location_ID => l_ship_to_location_id,
1859 P_Schedule_Designator => l_Schedule_Designator,
1860 P_Expected_Date => l_expected_date,
1861 P_quantity => l_quantity,
1862 P_uom_code => l_uom_code)='Y' then
1863
1864 OKE_DELIVERABLE_ACTIONS_PKG.Create_Demand( P_Action_ID => l_action_id,
1865 P_Init_Msg_List => fnd_api.g_false,
1866 X_ID => l_out_id,
1867 X_Return_Status => x_return_status,
1868 X_Msg_Count => x_msg_count,
1869 X_Msg_Data => x_msg_data );
1870 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1872 elsif (x_return_status = G_RET_STS_ERROR) then
1873 RAISE FND_API.G_EXC_ERROR ;
1874 end if;
1875 else
1876 RAISE FND_API.G_EXC_ERROR ;
1877 end if;
1878
1879 end if;
1880 If p_dlv_action_type =G_REQ then
1881 If l_ready_flag='Y' then
1882 OKE_DELIVERABLE_ACTIONS_PKG.Create_Requisition( P_Action_ID => l_action_id,
1883 P_Init_Msg_List => fnd_api.g_false,
1884 X_ID => l_out_id,
1885 X_Return_Status => x_return_status,
1886 X_Msg_Count => x_msg_count,
1887 X_Msg_Data => x_msg_data );
1888 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1890 elsif (x_return_status = G_RET_STS_ERROR) then
1891 RAISE FND_API.G_EXC_ERROR ;
1892 end if;
1893 else
1894 oke_api.set_message(p_msg_name => 'OKE_ACTION_NOT_READY',
1895 p_token1 => 'PROJECT_NAME',
1896 p_token1_value => l_project_name,
1897 p_token2 => 'DELIVERABLE_NAME',
1898 p_token2_value => l_deliverable_name,
1899 p_token3 => 'ACTION_NAME',
1900 p_token3_value => l_action_name
1901 );
1902 raise FND_API.G_EXC_ERROR;
1903 end if;
1904 end if;
1905 If p_dlv_action_type =G_SHIP then
1906 If l_ready_flag='Y' then
1907 OKE_DELIVERABLE_ACTIONS_PKG.create_Shipment( P_Action_ID => l_action_id,
1908 P_Init_Msg_List => fnd_api.g_false,
1909 X_ID => l_out_id,
1910 X_Return_Status => x_return_status,
1911 X_Msg_Count => x_msg_count,
1912 X_Msg_Data => x_msg_data );
1913 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1915 elsif (x_return_status = G_RET_STS_ERROR) then
1916 RAISE FND_API.G_EXC_ERROR ;
1917 end if;
1918 else
1919 oke_api.set_message(p_msg_name => 'OKE_ACTION_NOT_READY',
1920 p_token1 => 'PROJECT_NAME',
1921 p_token1_value => l_project_name,
1922 p_token2 => 'DELIVERABLE_NAME',
1923 p_token2_value => l_deliverable_name,
1924 p_token3 => 'ACTION_NAME',
1925 p_token3_value => l_action_name
1926 );
1927 raise FND_API.G_EXC_ERROR;
1928 end if;
1929 end if;
1930
1931 If fnd_api.to_boolean( p_commit ) then
1932 commit work;
1933 end if;
1934
1935 -- Standard call to get message count and if count is 1, get message info.
1936 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1937 EXCEPTION
1938
1939 WHEN FND_API.G_EXC_ERROR THEN
1940
1941 ROLLBACK TO initiate_dlv_action ;
1942 x_return_status := G_RET_STS_ERROR ;
1943 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1944
1945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1946
1947 ROLLBACK TO initiate_dlv_action ;
1948 x_return_status := G_RET_STS_UNEXP_ERROR ;
1949 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1950
1951 WHEN OTHERS THEN
1952
1953 ROLLBACK TO initiate_dlv_action ;
1954 x_return_status := G_RET_STS_UNEXP_ERROR ;
1955 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1956 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1957 END IF;
1958 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1959 End;
1960 END OKE_AMG_GRP;