[Home] [Help]
PACKAGE BODY: APPS.OKE_DELIVERABLE_UTILS_PKG
Source
1 package body OKE_DELIVERABLE_UTILS_PKG as
2 /* $Header: OKEDTSUB.pls 120.0 2005/05/25 17:35:33 appldev noship $ */
3
4 /*
5 * Procedure Name: Create_deliverable
6 * Usage: To create a dummy deliverable for non-item
7 * based deliverable
8 * Parameters (IN):
9 * X_DELIVERABLE_NUM Deliverable number
10 * X_SOURCE_CODE Deliverable source
11 * X_SOURCE_HEADER_ID Source header ID
12 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
13 * X_CURRENCY_CODE Currency code
14 *
15 * (OUT): X_DELIVERABLE_ID Deliverable ID
16 */
17 procedure CREATE_DELIVERABLE (
18 X_DELIVERABLE_NUM in VARCHAR2,
19 X_SOURCE_CODE in VARCHAR2,
20 X_SOURCE_HEADER_ID in NUMBER,
21 X_SOURCE_DELIVERABLE_ID in NUMBER,
22 X_CURRENCY_CODE in VARCHAR2,
23 X_DELIVERABLE_ID out NOCOPY NUMBER
24 ) is
25
26 cursor c_check_item is
27 select deliverable_id
28 from oke_deliverables_b
29 where source_code = nvl(X_SOURCE_CODE, '-99')
30 and source_header_id = X_SOURCE_HEADER_ID
31 --and deliverable_number = X_DELIVERABLE_NUM
32 and source_deliverable_id = X_SOURCE_DELIVERABLE_ID;
33
34 --l_count number;
35 l_user_id number;
36 l_row_id varchar2(30);
37 l_project_id number;
38
39 begin
40
41 open c_check_item;
42 fetch c_check_item into x_deliverable_id;
43 if (c_check_item%NOTFOUND) then
44
45 l_user_id := fnd_global.user_id;
46
47 select OKE_K_DELIVERABLES_S.NEXTVAL
48 into X_DELIVERABLE_ID
49 from dual;
50
51 if (nvl(X_SOURCE_CODE, -99) = 'PA') then
52 l_project_id := X_SOURCE_HEADER_ID;
53 end if;
54
55 OKE_DELIVERABLES_PKG.INSERT_ROW (
56 X_DELIVERABLE_ID => X_DELIVERABLE_ID,
57 X_DELIVERABLE_NUMBER => X_DELIVERABLE_NUM,
58 X_SOURCE_CODE => X_SOURCE_CODE,
59 X_SOURCE_HEADER_ID => X_SOURCE_HEADER_ID,
60 X_SOURCE_DELIVERABLE_ID => X_SOURCE_DELIVERABLE_ID,
61 X_CREATION_DATE => sysdate,
62 X_CREATED_BY => l_user_id,
63 X_LAST_UPDATE_DATE => sysdate,
64 X_LAST_UPDATED_BY => l_user_id,
65 X_LAST_UPDATE_LOGIN => l_user_id,
66 X_UNIT_PRICE => 1,
67 X_UOM_CODE => null,
68 X_QUANTITY => null,
69 X_UNIT_NUMBER => null,
70 X_ATTRIBUTE_CATEGORY => null,
71 X_ATTRIBUTE1 => null,
72 X_ATTRIBUTE2 => null,
73 X_ATTRIBUTE3 => null,
74 X_ATTRIBUTE4 => null,
75 X_ATTRIBUTE5 => null,
76 X_ATTRIBUTE6 => null,
77 X_ATTRIBUTE7 => null,
78 X_ATTRIBUTE8 => null,
79 X_ATTRIBUTE9 => null,
80 X_ATTRIBUTE10 => null,
81 X_ATTRIBUTE11 => null,
82 X_ATTRIBUTE12 => null,
83 X_ATTRIBUTE13 => null,
84 X_ATTRIBUTE14 => null,
85 X_ATTRIBUTE15 => null,
86 X_ROWID => l_row_id,
87 X_SOURCE_LINE_ID => null,
88 X_CURRENCY_CODE => X_CURRENCY_CODE,
89 X_INVENTORY_ORG_ID => null,
90 X_DELIVERY_DATE => null,
91 X_ITEM_ID => null,
92 X_DESCRIPTION => null,
93 X_COMMENTS => null,
94 X_PROJECT_ID => l_project_id
95 );
96 end if;
97 close c_check_item;
98
99 end CREATE_DELIVERABLE;
100
101
102 /*
103 * Procedure Name: Default_Ship_Deliverable
104 * Usage: To get item-based deliverable information for shipping action
105 *
106 * Parameters (IN):
107 * X_SOURCE_CODE Deliverable source
108 * X_SOURCE_HEADER_ID Source header ID
109 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
110 *
111 * (OUT): X_SHIP_FROM_ORG_ID Ship from org ID
112 * X_SHIP_FROM_LOCATION_ID Ship from location ID
113 * X_SHIP_FROM_ORG Ship from org
114 * X_SHIP_FROM_LOCATION Ship from location
115 * X_DELIVERABLE_ID Deliverable ID
116 * X_QUANTITY Quantity
117 * X_UOM Uom Code
118 */
119 procedure DEFAULT_SHIP_DELIVERABLE (
120 X_SOURCE_CODE in VARCHAR2,
121 X_SOURCE_HEADER_ID in NUMBER,
122 X_SOURCE_DELIVERABLE_ID in NUMBER,
123 X_SHIP_FROM_ORG_ID out NOCOPY NUMBER,
124 X_SHIP_FROM_LOCATION_ID out NOCOPY NUMBER,
125 X_SHIP_FROM_ORG out NOCOPY VARCHAR2,
126 X_SHIP_FROM_LOCATION out NOCOPY VARCHAR2,
127 X_DELIVERABLE_ID out NOCOPY NUMBER,
128 X_QUANTITY out NOCOPY NUMBER,
129 X_UOM out NOCOPY VARCHAR2
130 ) is
131
132 cursor c_ship is
133 select inventory_org_id,
134 l.id1 location_id,
135 v.name ship_from_org,
136 l.name location_name,
137 b.deliverable_id,
138 b.quantity,
139 b.uom_code
143 --po_supplier_sites_val_v l
140 from oke_deliverables_b b,
141 hr_all_organization_units_vl v,
142 okx_locations_v l
144 where source_code = x_source_code
145 and source_header_id = x_source_header_id
146 and source_deliverable_id = x_source_deliverable_id
147 and v.organization_id(+) = b.inventory_org_id
148 and b.inventory_org_id = l.organization_id(+);
149 --and nvl(l.rfq_only_site_flag, 'N') = 'N';
150 begin
151
152 open c_ship;
153 fetch c_ship into x_ship_from_org_id,
154 x_ship_from_location_id,
155 x_ship_from_org,
156 x_ship_from_location,
157 x_deliverable_id,
158 x_quantity,
159 x_uom;
160 close c_ship;
161
162 end DEFAULT_SHIP_DELIVERABLE;
163
164
165 /*
166 * Procedure Name: Check_flag
167 * Usage: To check the ready flag in action table
168 * for a deliverable
169 * Parameters (IN):
170 * X_DELIVERABLE_ID Deliverable ID
171 *
172 * (OUT): X_FLAG Ready flag
173 */
174 procedure CHECK_FLAG (
175 X_DELIVERABLE_ID in NUMBER,
176 X_FLAG out NOCOPY VARCHAR2
177 ) is
178
179 cursor c_flag is
180 select count(1)
181 from oke_deliverable_actions
182 where deliverable_id = X_DELIVERABLE_ID
183 and nvl(ready_flag, 'N') = 'Y';
184
185 l_count NUMBER := 0;
186 begin
187
188 open c_flag;
189 fetch c_flag into l_count;
190 close c_flag;
191
192 if (l_count > 0) then
193 X_FLAG := 'Y';
194 else
195 X_FLAG := 'N';
196 end if;
197
198 end CHECK_FLAG;
199
200
201 /*
202 * Procedure Name: Update_qty
203 * Usage: To update the quantity in deliverable table
204 * for a non-item based deliverable
205 * Parameters (IN):
206 * X_QUANTITY Quantity
207 * X_SOURCE_CODE Deliverable source
208 * X_SOURCE_HEADER_ID Source header ID
209 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
210 */
211 procedure UPDATE_QTY (
212 X_QUANTITY in NUMBER,
213 X_SOURCE_CODE in VARCHAR2,
214 X_SOURCE_HEADER_ID in NUMBER,
215 X_SOURCE_DELIVERABLE_ID in NUMBER
216 ) is
217 begin
218
219 update oke_deliverables_b
220 set quantity = X_QUANTITY
221 where source_code = X_SOURCE_CODE
222 and source_header_id = X_SOURCE_HEADER_ID
223 and source_deliverable_id = X_SOURCE_DELIVERABLE_ID;
224
225 end UPDATE_QTY;
226
227
228 /*
229 * Procedure Name: Get_deliverable
230 * Usage: To get deliverable information
231 * Parameters (IN):
232 * X_SOURCE_CODE Deliverable source
233 * X_SOURCE_HEADER_ID Source header ID
234 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
235 *
236 * (OUT): X_DELIVERABLE_ID Deliverable ID
237 * X_QUANTITY Quantity
238 * X_UNIT_PRICE Unit price
239 * X_UOM_CODE Uom Code
240 * X_SHIP_TO_ORG_ID Ship to org ID
241 * X_SHIP_TO_ORG Ship to org
242 * X_CURRENCY_CODE Currency code
243 */
244 procedure GET_DELIVERABLE (
245 X_SOURCE_CODE in VARCHAR2,
246 X_SOURCE_HEADER_ID in NUMBER,
247 X_SOURCE_DELIVERABLE_ID in NUMBER,
248 X_DELIVERABLE_ID out NOCOPY NUMBER,
249 X_QUANTITY out NOCOPY NUMBER,
250 X_UNIT_PRICE out NOCOPY NUMBER,
251 X_UOM_CODE out NOCOPY VARCHAR2,
252 X_SHIP_TO_ORG_ID out NOCOPY NUMBER,
253 X_SHIP_TO_ORG out NOCOPY VARCHAR2,
254 X_CURRENCY_CODE out NOCOPY VARCHAR2
255 ) is
256
257 cursor c_id is
258 select deliverable_id,
259 quantity,
260 unit_price,
261 uom_code,
262 inventory_org_id,
263 v.organization_name,
264 currency_code
265 from oke_deliverables_b b,
266 org_organization_definitions v
267 where source_code = X_SOURCE_CODE
268 and source_header_id = X_SOURCE_HEADER_ID
269 and source_deliverable_id = X_SOURCE_DELIVERABLE_ID
270 and b.inventory_org_id = organization_id(+);
271 begin
272
273 open c_id;
274 fetch c_id into X_DELIVERABLE_ID,
275 X_QUANTITY,
276 X_UNIT_PRICE,
277 X_UOM_CODE,
278 X_SHIP_TO_ORG_ID,
279 X_SHIP_TO_ORG,
280 X_CURRENCY_CODE;
281 close c_id;
282
283 end GET_DELIVERABLE;
284
285
286 /*
287 * Procedure Name: Get_deliverable_id
288 * Usage: To get deliverable_id
289 * Parameters (IN):
290 * X_SOURCE_CODE Deliverable source
291 * X_SOURCE_HEADER_ID Source header ID
292 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
293 *
294 * (OUT): X_DELIVERABLE_ID Deliverable ID
295 * X_CURRENCY_CODE Currency code
296 */
297
298 procedure GET_DELIVERABLE_ID (
299 X_SOURCE_CODE in VARCHAR2,
300 X_SOURCE_HEADER_ID in NUMBER,
301 X_SOURCE_DELIVERABLE_ID in NUMBER,
302 X_DELIVERABLE_ID out NOCOPY NUMBER,
303 X_CURRENCY_CODE out NOCOPY VARCHAR2
304 ) is
305
306 cursor c_id is
307 select deliverable_id,
308 currency_code
312 and source_deliverable_id = X_SOURCE_DELIVERABLE_ID;
309 from oke_deliverables_b b
310 where source_code = X_SOURCE_CODE
311 and source_header_id = X_SOURCE_HEADER_ID
313
314 begin
315
316 open c_id;
317 fetch c_id into X_DELIVERABLE_ID, X_CURRENCY_CODE;
318 close c_id;
319
320 end GET_DELIVERABLE_ID;
321
322
323 /*
324 * Procedure Name: Check_action
325 * Usage: To the existance of the action record
326 * Parameters (IN):
327 * X_SOURCE_CODE Deliverable source
328 * X_SOURCE_HEADER_ID Source header ID
329 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
330 * X_ACTION_TYPE Action Type
331 *
332 * (OUT): X_flag Existance flag
333 */
334
335 procedure CHECK_ACTION (
336 X_SOURCE_CODE in VARCHAR2,
337 X_SOURCE_HEADER_ID in NUMBER,
338 X_SOURCE_DELIVERABLE_ID in NUMBER,
339 X_ACTION_TYPE in VARCHAR2,
340 X_FLAG out NOCOPY VARCHAR2
341 ) is
342 cursor c_1 is
343 select 'Y'
344 from oke_deliverable_actions a,
345 oke_deliverables_b b
346 where a.deliverable_id = b.deliverable_id
347 and a.action_type = X_ACTION_TYPE
348 and b.source_code = X_SOURCE_CODE
349 and b.source_deliverable_id = X_SOURCE_DELIVERABLE_ID
350 and b.source_header_id = X_SOURCE_HEADER_ID;
351 begin
352 open c_1;
353 fetch c_1 into X_FLAG;
354 if (c_1%NOTFOUND) then
355 X_FLAG := 'N';
356 end if;
357 close c_1;
358
359 end CHECK_ACTION;
360
361
362 /*
363 * Procedure Name: Update_currency
364 * Usage: To update the currency in deliverable table
365 * for a non-item based deliverable
366 * Parameters (IN):
367 * X_CURRENCY_CODE Currency code
368 * X_SOURCE_CODE Deliverable source
369 * X_SOURCE_HEADER_ID Source header ID
370 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
371 */
372
373 procedure UPDATE_CURRENCY (
374 X_CURRENCY_CODE in VARCHAR2,
375 X_SOURCE_CODE in VARCHAR2,
376 X_SOURCE_HEADER_ID in NUMBER,
377 X_SOURCE_DELIVERABLE_ID in NUMBER
378 ) is
379 begin
380
381 update oke_deliverables_b
382 set currency_code = X_CURRENCY_CODE
383 where source_code = X_SOURCE_CODE
384 and source_header_id = X_SOURCE_HEADER_ID
385 and source_deliverable_id = X_SOURCE_DELIVERABLE_ID;
386
387 end UPDATE_CURRENCY;
388
389 /*
390 * Procedure Name: Update_price
391 * Usage: To update the unit price in deliverable table
392 * for a non-item based deliverable
393 * Parameters (IN):
394 * X_UNIT_PRICE Unit price
395 * X_SOURCE_CODE Deliverable source
396 * X_SOURCE_HEADER_ID Source header ID
397 * X_SOURCE_DELIVERABLE_ID Source deliverable ID
398 */
399
400 procedure UPDATE_PRICE (
401 X_UNIT_PRICE in NUMBER,
402 X_SOURCE_CODE in VARCHAR2,
403 X_SOURCE_HEADER_ID in NUMBER,
404 X_SOURCE_DELIVERABLE_ID in NUMBER
405 ) is
406 begin
407
408 update oke_deliverables_b
409 set unit_price = X_UNIT_PRICE
410 where source_code = X_SOURCE_CODE
411 and source_header_id = X_SOURCE_HEADER_ID
412 and source_deliverable_id = X_SOURCE_DELIVERABLE_ID;
413
414 end UPDATE_PRICE;
415
416
417 /*
418 * Procedure Name: Insert_Row
419 * Usage: To insert a row in oke_deliverables_b
420 *
421 */
422
423 procedure INSERT_ROW (
424 X_ROWID in out NOCOPY VARCHAR2,
425 X_DELIVERABLE_ID in NUMBER,
426 X_DELIVERABLE_NUMBER in VARCHAR2,
427 X_SOURCE_CODE in VARCHAR2,
428 X_UNIT_PRICE in NUMBER,
429 X_UOM_CODE in VARCHAR2,
430 X_QUANTITY in NUMBER,
431 X_UNIT_NUMBER in VARCHAR2,
432 X_ATTRIBUTE_CATEGORY in VARCHAR2,
433 X_ATTRIBUTE1 in VARCHAR2,
434 X_ATTRIBUTE2 in VARCHAR2,
435 X_ATTRIBUTE3 in VARCHAR2,
436 X_ATTRIBUTE4 in VARCHAR2,
437 X_ATTRIBUTE5 in VARCHAR2,
438 X_ATTRIBUTE6 in VARCHAR2,
439 X_ATTRIBUTE7 in VARCHAR2,
440 X_ATTRIBUTE8 in VARCHAR2,
441 X_ATTRIBUTE9 in VARCHAR2,
442 X_ATTRIBUTE10 in VARCHAR2,
443 X_ATTRIBUTE11 in VARCHAR2,
444 X_ATTRIBUTE12 in VARCHAR2,
445 X_ATTRIBUTE13 in VARCHAR2,
446 X_ATTRIBUTE14 in VARCHAR2,
447 X_ATTRIBUTE15 in VARCHAR2,
448 X_SOURCE_HEADER_ID in NUMBER,
449 X_SOURCE_LINE_ID in NUMBER,
450 X_SOURCE_DELIVERABLE_ID in NUMBER,
451 X_PROJECT_ID in NUMBER,
452 X_CURRENCY_CODE in VARCHAR2,
453 X_INVENTORY_ORG_ID in NUMBER,
454 X_DELIVERY_DATE in DATE,
455 X_ITEM_ID in NUMBER,
456 X_DESCRIPTION in VARCHAR2,
457 X_COMMENTS in VARCHAR2,
458 X_CREATION_DATE in DATE,
459 X_CREATED_BY in NUMBER,
460 X_LAST_UPDATE_DATE in DATE,
461 X_LAST_UPDATED_BY in NUMBER,
462 X_LAST_UPDATE_LOGIN in NUMBER
463 ) is
464 x_ship_id NUMBER;
465 x_ship_name PA_PROJ_ELEMENTS.NAME%TYPE;
466 x_ship_due_date DATE;
467 x_proc_id NUMBER;
468 x_proc_name PA_PROJ_ELEMENTS.NAME%TYPE;
469 x_proc_due_date DATE;
470 begin
471 OKE_DELIVERABLES_PKG.INSERT_ROW(
472 X_ROWID => X_ROWID,
473 X_DELIVERABLE_ID => X_DELIVERABLE_ID,
474 X_DELIVERABLE_NUMBER => X_DELIVERABLE_NUMBER,
475 X_SOURCE_CODE => X_SOURCE_CODE,
476 X_UNIT_PRICE => X_UNIT_PRICE,
480 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
477 X_UOM_CODE => X_UOM_CODE,
478 X_QUANTITY => X_QUANTITY,
479 X_UNIT_NUMBER => X_UNIT_NUMBER,
481 X_ATTRIBUTE1 => X_ATTRIBUTE1,
482 X_ATTRIBUTE2 => X_ATTRIBUTE2,
483 X_ATTRIBUTE3 => X_ATTRIBUTE3,
484 X_ATTRIBUTE4 => X_ATTRIBUTE4,
485 X_ATTRIBUTE5 => X_ATTRIBUTE5,
486 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
487 X_ATTRIBUTE7 => X_ATTRIBUTE7,
488 X_ATTRIBUTE8 => X_ATTRIBUTE8,
489 X_ATTRIBUTE9 => X_ATTRIBUTE9,
490 X_ATTRIBUTE10 => X_ATTRIBUTE10,
491 X_ATTRIBUTE11 => X_ATTRIBUTE11,
492 X_ATTRIBUTE12 => X_ATTRIBUTE12,
493 X_ATTRIBUTE13 => X_ATTRIBUTE13,
494 X_ATTRIBUTE14 => X_ATTRIBUTE14,
495 X_ATTRIBUTE15 => X_ATTRIBUTE15,
496 X_SOURCE_HEADER_ID => X_SOURCE_HEADER_ID,
497 X_SOURCE_LINE_ID => X_SOURCE_LINE_ID,
498 X_SOURCE_DELIVERABLE_ID => X_SOURCE_DELIVERABLE_ID,
499 X_PROJECT_ID => X_PROJECT_ID,
500 X_CURRENCY_CODE => X_CURRENCY_CODE,
501 X_INVENTORY_ORG_ID => X_INVENTORY_ORG_ID,
502 X_DELIVERY_DATE => X_DELIVERY_DATE,
503 X_ITEM_ID => X_ITEM_ID,
504 X_DESCRIPTION => X_DESCRIPTION,
505 X_COMMENTS => X_COMMENTS,
506 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
507 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
508 X_CREATION_DATE => X_CREATION_DATE,
509 X_CREATED_BY => X_CREATED_BY,
510 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN
511 );
512
513 PA_DELIVERABLE_UTILS.GET_SHIP_PROC_ACTN_DETAIL(
514 p_dlvr_id => X_SOURCE_DELIVERABLE_ID
515 ,x_ship_id => x_ship_id
516 ,x_ship_name => x_ship_name
517 ,x_ship_due_date => x_ship_due_date
518 ,x_proc_id => x_proc_id
519 ,x_proc_name => x_proc_name
520 ,x_proc_due_date => x_proc_due_date
521 );
522 IF( x_ship_id IS NOT NULL ) THEN
523 INSERT INTO oke_deliverable_actions (
524 ACTION_ID
525 , CREATION_DATE
526 , CREATED_BY
527 , LAST_UPDATE_DATE
528 , LAST_UPDATED_BY
529 , LAST_UPDATE_LOGIN
530 , ACTION_TYPE
531 , ACTION_NAME
532 , DELIVERABLE_ID
533 , PA_ACTION_ID
534 , EXPECTED_DATE
535 , uom_code
536 , quantity
537 , unit_price
538 , currency_code
539 , ship_from_org_id
540 ) VALUES (
541 oke_k_deliverables_s.NEXTVAL
542 , SYSDATE
543 , FND_GLOBAL.USER_ID
544 , SYSDATE
545 , FND_GLOBAL.USER_ID
546 , FND_GLOBAL.LOGIN_ID
547 , 'WSH'
548 , x_ship_name
549 , X_DELIVERABLE_ID
550 , x_ship_id
551 , x_ship_due_date
552 , x_uom_code
553 , x_quantity
554 , x_unit_price
555 , x_currency_code
556 , X_INVENTORY_ORG_ID
557 );
558 END IF;
559 IF( x_proc_id IS NOT NULL ) THEN
560 INSERT INTO oke_deliverable_actions (
561 ACTION_ID
562 , CREATION_DATE
563 , CREATED_BY
564 , LAST_UPDATE_DATE
565 , LAST_UPDATED_BY
566 , LAST_UPDATE_LOGIN
567 , ACTION_TYPE
568 , ACTION_NAME
569 , DELIVERABLE_ID
570 , PA_ACTION_ID
571 , EXPECTED_DATE
572 , uom_code
573 , quantity
574 , unit_price
575 , currency_code
576 , ship_to_org_id
577 , destination_type_code
578 ) VALUES (
579 oke_k_deliverables_s.NEXTVAL
580 , SYSDATE
581 , FND_GLOBAL.USER_ID
582 , SYSDATE
583 , FND_GLOBAL.USER_ID
584 , FND_GLOBAL.LOGIN_ID
585 , 'REQ'
586 , x_proc_name
587 , X_DELIVERABLE_ID
588 , x_proc_id
589 , x_proc_due_date
590 , x_uom_code
591 , x_quantity
592 , x_unit_price
593 , x_currency_code
594 , X_INVENTORY_ORG_ID
595 , 'EXPENSE'
596 );
597 END IF;
598 end INSERT_ROW;
599
600 /*
601 * Procedure Name: Update_Row
602 * Usage: To update a row in oke_deliverables_b
603 *
604 */
605
606 procedure UPDATE_ROW (
607 X_DELIVERABLE_ID in NUMBER,
608 X_DELIVERABLE_NUMBER in VARCHAR2,
609 X_SOURCE_CODE in VARCHAR2,
610 X_UNIT_PRICE in NUMBER,
611 X_UOM_CODE in VARCHAR2,
612 X_QUANTITY in NUMBER,
613 X_UNIT_NUMBER in VARCHAR2,
614 X_ATTRIBUTE_CATEGORY in VARCHAR2,
615 X_ATTRIBUTE1 in VARCHAR2,
616 X_ATTRIBUTE2 in VARCHAR2,
617 X_ATTRIBUTE3 in VARCHAR2,
618 X_ATTRIBUTE4 in VARCHAR2,
619 X_ATTRIBUTE5 in VARCHAR2,
620 X_ATTRIBUTE6 in VARCHAR2,
621 X_ATTRIBUTE7 in VARCHAR2,
622 X_ATTRIBUTE8 in VARCHAR2,
623 X_ATTRIBUTE9 in VARCHAR2,
624 X_ATTRIBUTE10 in VARCHAR2,
625 X_ATTRIBUTE11 in VARCHAR2,
626 X_ATTRIBUTE12 in VARCHAR2,
627 X_ATTRIBUTE13 in VARCHAR2,
628 X_ATTRIBUTE14 in VARCHAR2,
629 X_ATTRIBUTE15 in VARCHAR2,
630 X_SOURCE_HEADER_ID in NUMBER,
631 X_SOURCE_LINE_ID in NUMBER,
632 X_SOURCE_DELIVERABLE_ID in NUMBER,
633 X_PROJECT_ID in NUMBER,
634 X_CURRENCY_CODE in VARCHAR2,
635 X_INVENTORY_ORG_ID in NUMBER,
636 X_DELIVERY_DATE in DATE,
637 X_ITEM_ID in NUMBER,
638 X_DESCRIPTION in VARCHAR2,
639 X_COMMENTS in VARCHAR2,
640 X_LAST_UPDATE_DATE in DATE,
641 X_LAST_UPDATED_BY in NUMBER,
642 X_LAST_UPDATE_LOGIN in NUMBER
643 ) is
644 Cursor c_check_inv_org(b_inv_org_id number,b_deliverable_id number) IS
645 select 'X' from oke_deliverable_actions where
646 deliverable_id=b_deliverable_id and
650 Cursor c_get_location(b_inv_org_id number) is
647 ( ( ship_from_org_id = b_inv_org_id and action_type = 'WSH' )
648 or (ship_to_org_id = b_inv_org_id and action_type = 'REQ' ) );
649
651 select id1
652 from okx_locations_v
653 where organization_id = b_inv_org_id;
654
655 l_inv_changed Varchar2(1) :='Y';
656 l_location_count Number := 0;
657 l_location_id number;
658 begin
659 OKE_DELIVERABLES_PKG.UPDATE_ROW(X_DELIVERABLE_ID => X_DELIVERABLE_ID,
660 X_DELIVERABLE_NUMBER => X_DELIVERABLE_NUMBER,
661 X_SOURCE_CODE => X_SOURCE_CODE,
662 X_UNIT_PRICE => X_UNIT_PRICE,
663 X_UOM_CODE => X_UOM_CODE,
664 X_QUANTITY => X_QUANTITY,
665 X_UNIT_NUMBER => X_UNIT_NUMBER,
666 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
667 X_ATTRIBUTE1 => X_ATTRIBUTE1,
668 X_ATTRIBUTE2 => X_ATTRIBUTE2,
669 X_ATTRIBUTE3 => X_ATTRIBUTE3,
670 X_ATTRIBUTE4 => X_ATTRIBUTE4,
671 X_ATTRIBUTE5 => X_ATTRIBUTE5,
672 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
673 X_ATTRIBUTE7 => X_ATTRIBUTE7,
674 X_ATTRIBUTE8 => X_ATTRIBUTE8,
675 X_ATTRIBUTE9 => X_ATTRIBUTE9,
676 X_ATTRIBUTE10 => X_ATTRIBUTE10,
677 X_ATTRIBUTE11 => X_ATTRIBUTE11,
678 X_ATTRIBUTE12 => X_ATTRIBUTE12,
679 X_ATTRIBUTE13 => X_ATTRIBUTE13,
680 X_ATTRIBUTE14 => X_ATTRIBUTE14,
681 X_ATTRIBUTE15 => X_ATTRIBUTE15,
682 X_SOURCE_HEADER_ID => X_SOURCE_HEADER_ID,
683 X_SOURCE_LINE_ID => X_SOURCE_LINE_ID,
684 X_SOURCE_DELIVERABLE_ID => X_SOURCE_DELIVERABLE_ID,
685 X_PROJECT_ID => X_PROJECT_ID,
686 X_CURRENCY_CODE => X_CURRENCY_CODE,
687 X_INVENTORY_ORG_ID => X_INVENTORY_ORG_ID,
688 X_DELIVERY_DATE => X_DELIVERY_DATE,
689 X_ITEM_ID => X_ITEM_ID,
690 X_DESCRIPTION => X_DESCRIPTION,
691 X_COMMENTS => X_COMMENTS,
692 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
693 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
694 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN);
695
696 if (PA_DELIVERABLE_UTILS.Is_Dlvr_Item_Based ( x_source_deliverable_id ) = 'Y') then
697
698 update oke_deliverable_actions
699 set uom_code = x_uom_code,
700 quantity = x_quantity,
701 unit_price = x_unit_price,
702 currency_code = x_currency_code
703 where deliverable_id = x_deliverable_id;
704 open c_check_inv_org(x_inventory_org_id,x_deliverable_id);
705 fetch c_check_inv_org Into l_inv_changed;
706 close c_check_inv_org;
707
708 If l_inv_changed = 'Y' then
709 open c_get_location(x_inventory_org_id);
710 loop
711 fetch c_get_location into l_location_id;
712 exit when c_get_location%notfound or l_location_count > 1;
713 l_location_count := l_Location_count +1;
714 end loop;
715 close c_get_location;
716
717 update oke_deliverable_actions
718 set ship_from_org_id = x_inventory_org_id,
719 schedule_designator = null,
720 ship_from_location_id = decode( l_location_count, 1 , l_location_id, NULL)
721 where deliverable_id = x_deliverable_id
722 and action_type = 'WSH';
723
724 update oke_deliverable_actions
725 set ship_to_org_id = x_inventory_org_id,
726 ship_to_location_id = decode( l_location_count, 1 , l_location_id, NULL),
727 expenditure_organization_id = NULL
728 where deliverable_id = x_deliverable_id
729 and action_type = 'REQ';
730 end if;
731
732 else
733
734 update oke_deliverable_actions
735 set destination_type_code = 'EXPENSE'
736 where deliverable_id = x_deliverable_id
737 and action_type = 'REQ';
738
739 end if;
740
741 end UPDATE_ROW;
742
743 /*
744 * Procedure Name: Lock_Row
745 * Usage: To lock a row in oke_deliverables_b
746 *
747 */
748
749 procedure LOCK_ROW (
750 X_DELIVERABLE_ID in NUMBER,
751 X_DELIVERABLE_NUMBER in VARCHAR2,
752 X_SOURCE_CODE in VARCHAR2,
753 X_UNIT_PRICE in NUMBER,
754 X_UOM_CODE in VARCHAR2,
755 X_QUANTITY in NUMBER,
756 X_UNIT_NUMBER in VARCHAR2,
757 X_ATTRIBUTE_CATEGORY in VARCHAR2,
758 X_ATTRIBUTE1 in VARCHAR2,
759 X_ATTRIBUTE2 in VARCHAR2,
760 X_ATTRIBUTE3 in VARCHAR2,
761 X_ATTRIBUTE4 in VARCHAR2,
762 X_ATTRIBUTE5 in VARCHAR2,
763 X_ATTRIBUTE6 in VARCHAR2,
764 X_ATTRIBUTE7 in VARCHAR2,
765 X_ATTRIBUTE8 in VARCHAR2,
766 X_ATTRIBUTE9 in VARCHAR2,
767 X_ATTRIBUTE10 in VARCHAR2,
768 X_ATTRIBUTE11 in VARCHAR2,
769 X_ATTRIBUTE12 in VARCHAR2,
770 X_ATTRIBUTE13 in VARCHAR2,
771 X_ATTRIBUTE14 in VARCHAR2,
772 X_ATTRIBUTE15 in VARCHAR2,
773 X_SOURCE_HEADER_ID in NUMBER,
774 X_SOURCE_LINE_ID in NUMBER,
775 X_SOURCE_DELIVERABLE_ID in NUMBER,
776 X_PROJECT_ID in NUMBER,
777 X_CURRENCY_CODE in VARCHAR2,
778 X_INVENTORY_ORG_ID in NUMBER,
779 X_DELIVERY_DATE in DATE,
780 X_ITEM_ID in NUMBER,
781 X_DESCRIPTION in VARCHAR2,
782 X_COMMENTS in VARCHAR2
783 ) is
784 begin
785 OKE_DELIVERABLES_PKG.LOCK_ROW(X_DELIVERABLE_ID => X_DELIVERABLE_ID,
786 X_DELIVERABLE_NUMBER => X_DELIVERABLE_NUMBER,
787 X_SOURCE_CODE => X_SOURCE_CODE,
788 X_UNIT_PRICE => X_UNIT_PRICE,
789 X_UOM_CODE => X_UOM_CODE,
790 X_QUANTITY => X_QUANTITY,
791 X_UNIT_NUMBER => X_UNIT_NUMBER,
792 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
793 X_ATTRIBUTE1 => X_ATTRIBUTE1,
794 X_ATTRIBUTE2 => X_ATTRIBUTE2,
795 X_ATTRIBUTE3 => X_ATTRIBUTE3,
796 X_ATTRIBUTE4 => X_ATTRIBUTE4,
797 X_ATTRIBUTE5 => X_ATTRIBUTE5,
798 X_ATTRIBUTE6 => X_ATTRIBUTE6 ,
799 X_ATTRIBUTE7 => X_ATTRIBUTE7,
800 X_ATTRIBUTE8 => X_ATTRIBUTE8,
801 X_ATTRIBUTE9 => X_ATTRIBUTE9,
802 X_ATTRIBUTE10 => X_ATTRIBUTE10,
803 X_ATTRIBUTE11 => X_ATTRIBUTE11,
804 X_ATTRIBUTE12 => X_ATTRIBUTE12,
805 X_ATTRIBUTE13 => X_ATTRIBUTE13,
806 X_ATTRIBUTE14 => X_ATTRIBUTE14,
807 X_ATTRIBUTE15 => X_ATTRIBUTE15,
808 X_SOURCE_HEADER_ID => X_SOURCE_HEADER_ID,
809 X_SOURCE_LINE_ID => X_SOURCE_LINE_ID,
810 X_SOURCE_DELIVERABLE_ID => X_SOURCE_DELIVERABLE_ID,
811 X_PROJECT_ID => X_PROJECT_ID,
812 X_CURRENCY_CODE => X_CURRENCY_CODE,
813 X_INVENTORY_ORG_ID => X_INVENTORY_ORG_ID,
814 X_DELIVERY_DATE => X_DELIVERY_DATE,
815 X_ITEM_ID => X_ITEM_ID,
816 X_DESCRIPTION => X_DESCRIPTION,
817 X_COMMENTS => X_COMMENTS);
818 end LOCK_ROW;
819
820 /*
821 * Procedure Name: Delete_Row
822 * Usage: To delete a row in oke_deliverables_b
823 *
824 */
825
826 procedure DELETE_ROW (
827 X_DELIVERABLE_ID in NUMBER
828 ) is
829 begin
830 OKE_DELIVERABLES_PKG.DELETE_ROW(X_DELIVERABLE_ID => X_DELIVERABLE_ID);
831 end DELETE_ROW;
832
833 /*
834 * Procedure Name: Add_Language
835 * Usage: Add rows to deliverable translated table
836 *
837 */
838
839 procedure ADD_LANGUAGE
840 is
841 begin
842 OKE_DELIVERABLES_PKG.ADD_LANGUAGE;
843 end ADD_LANGUAGE;
844
845 function Update_Expected_Date (
846 p_pa_action_id in NUMBER,
847 p_expected_date in DATE
848 ) return Varchar2 IS
849 begin
850 update oke_deliverable_actions set expected_date = p_expected_date
851 where pa_action_id=p_pa_action_id
852 ;
853 if sql%rowcount <> 1 then
854 return fnd_api.g_false;
855 else
856 return fnd_api.g_true;
857 end if;
858 exception
859 when others then
860 return fnd_api.g_false;
861 end;
862
863 end OKE_DELIVERABLE_UTILS_PKG;