[Home] [Help]
PACKAGE BODY: APPS.AMS_DELIVERABLES_ALL_PKG
Source
1 package body AMS_DELIVERABLES_ALL_PKG as
2 /* $Header: amsldelb.pls 115.2 2000/02/16 00:53:27 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_DELIVERABLE_ID in NUMBER,
6 X_NON_INV_CTRL_CODE in VARCHAR2,
7 X_NON_INV_QUANTITY_ON_HAND in NUMBER,
8 X_NON_INV_QUANTITY_ON_ORDER in NUMBER,
9 X_NON_INV_QUANTITY_ON_RESERVE in NUMBER,
10 X_REPLACED_BY_DELIVERABLE_ID in NUMBER,
11 X_CAN_FULFILL_ELECTRONIC_FLAG in VARCHAR2,
12 X_CAN_FULFILL_PHYSICAL_FLAG in VARCHAR2,
13 X_TRANSACTION_CURRENCY_CODE in VARCHAR2,
14 X_FUNCTIONAL_CURRENCY_CODE in VARCHAR2,
15 X_BUDGET_AMOUNT_TC in NUMBER,
16 X_BUDGET_AMOUNT_FC in NUMBER,
17 X_ACTUAL_AVAIL_FROM_DATE in DATE,
18 X_ACTUAL_AVAIL_TO_DATE in DATE,
19 X_CHARGEBACK_AMOUNT in NUMBER,
20 X_CHARGEBACK_UOM in VARCHAR2,
21 X_CHARGEBACK_AMOUNT_CURR_CODE in VARCHAR2,
22 X_DELIVERABLE_CODE in VARCHAR2,
23 X_DELIVERABLE_PICK_FLAG in VARCHAR2,
24 X_CURRENCY_CODE in VARCHAR2,
25 X_FORECASTED_COST in NUMBER,
26 X_ACTUAL_COST in NUMBER,
27 X_FORECASTED_RESPONSES in NUMBER,
28 X_ACTUAL_RESPONSES in NUMBER,
29 X_COUNTRY in VARCHAR2,
30 X_DEFAULT_APPROVER_ID in NUMBER,
31 X_ATTRIBUTE_CATEGORY in VARCHAR2,
32 X_ATTRIBUTE1 in VARCHAR2,
33 X_ATTRIBUTE2 in VARCHAR2,
34 X_ATTRIBUTE3 in VARCHAR2,
35 X_ATTRIBUTE4 in VARCHAR2,
36 X_ATTRIBUTE5 in VARCHAR2,
37 X_ATTRIBUTE6 in VARCHAR2,
38 X_ATTRIBUTE7 in VARCHAR2,
39 X_ATTRIBUTE8 in VARCHAR2,
40 X_ATTRIBUTE9 in VARCHAR2,
41 X_ATTRIBUTE10 in VARCHAR2,
42 X_ATTRIBUTE11 in VARCHAR2,
43 X_ATTRIBUTE12 in VARCHAR2,
44 X_ATTRIBUTE13 in VARCHAR2,
45 X_ATTRIBUTE14 in VARCHAR2,
46 X_ATTRIBUTE15 in VARCHAR2,
47 X_OBJECT_VERSION_NUMBER in NUMBER,
48 X_LANGUAGE_CODE in VARCHAR2,
49 X_VERSION in VARCHAR2,
50 X_APPLICATION_ID in NUMBER,
51 X_USER_STATUS_ID in NUMBER,
52 X_STATUS_CODE in VARCHAR2,
53 X_STATUS_DATE in DATE,
54 X_PRIVATE_FLAG in VARCHAR2,
55 X_ACTIVE_FLAG in VARCHAR2,
56 X_OWNER_USER_ID in NUMBER,
57 X_FUND_SOURCE_ID in NUMBER,
58 X_FUND_SOURCE_TYPE in VARCHAR2,
59 X_CATEGORY_TYPE_ID in NUMBER,
60 X_CATEGORY_SUB_TYPE_ID in NUMBER,
61 X_KIT_FLAG in VARCHAR2,
62 X_INVENTORY_FLAG in VARCHAR2,
63 X_INVENTORY_ITEM_ID in NUMBER,
64 X_INVENTORY_ITEM_ORG_ID in NUMBER,
65 X_DELIVERABLE_NAME in VARCHAR2,
66 X_DESCRIPTION in VARCHAR2,
67 X_CREATION_DATE in DATE,
68 X_CREATED_BY in NUMBER,
69 X_LAST_UPDATE_DATE in DATE,
70 X_LAST_UPDATED_BY in NUMBER,
71 X_LAST_UPDATE_LOGIN in NUMBER
72 ) is
73 cursor C is select ROWID from AMS_DELIVERABLES_ALL_B
74 where DELIVERABLE_ID = X_DELIVERABLE_ID
75 ;
76 begin
77 insert into AMS_DELIVERABLES_ALL_B (
78 NON_INV_CTRL_CODE,
79 NON_INV_QUANTITY_ON_HAND,
80 NON_INV_QUANTITY_ON_ORDER,
81 NON_INV_QUANTITY_ON_RESERVE,
82 REPLACED_BY_DELIVERABLE_ID,
83 CAN_FULFILL_ELECTRONIC_FLAG,
84 CAN_FULFILL_PHYSICAL_FLAG,
85 TRANSACTION_CURRENCY_CODE,
86 FUNCTIONAL_CURRENCY_CODE,
87 BUDGET_AMOUNT_TC,
88 BUDGET_AMOUNT_FC,
89 ACTUAL_AVAIL_FROM_DATE,
90 ACTUAL_AVAIL_TO_DATE,
91 CHARGEBACK_AMOUNT,
92 CHARGEBACK_UOM,
93 CHARGEBACK_AMOUNT_CURR_CODE,
94 DELIVERABLE_CODE,
95 DELIVERABLE_PICK_FLAG,
96 CURRENCY_CODE,
97 FORECASTED_COST,
98 ACTUAL_COST,
99 FORECASTED_RESPONSES,
100 ACTUAL_RESPONSES,
101 COUNTRY,
102 DEFAULT_APPROVER_ID,
103 ATTRIBUTE_CATEGORY,
104 ATTRIBUTE1,
105 ATTRIBUTE2,
106 ATTRIBUTE3,
107 ATTRIBUTE4,
108 ATTRIBUTE5,
109 ATTRIBUTE6,
110 ATTRIBUTE7,
111 ATTRIBUTE8,
112 ATTRIBUTE9,
113 ATTRIBUTE10,
114 ATTRIBUTE11,
115 ATTRIBUTE12,
116 ATTRIBUTE13,
117 ATTRIBUTE14,
118 ATTRIBUTE15,
119 DELIVERABLE_ID,
120 OBJECT_VERSION_NUMBER,
121 LANGUAGE_CODE,
122 VERSION,
123 APPLICATION_ID,
124 USER_STATUS_ID,
125 STATUS_CODE,
126 STATUS_DATE,
127 PRIVATE_FLAG,
128 ACTIVE_FLAG,
129 OWNER_USER_ID,
130 FUND_SOURCE_ID,
131 FUND_SOURCE_TYPE,
132 CATEGORY_TYPE_ID,
133 CATEGORY_SUB_TYPE_ID,
134 KIT_FLAG,
135 INVENTORY_FLAG,
136 INVENTORY_ITEM_ID,
137 INVENTORY_ITEM_ORG_ID,
138 CREATION_DATE,
139 CREATED_BY,
140 LAST_UPDATE_DATE,
141 LAST_UPDATED_BY,
142 LAST_UPDATE_LOGIN
143 ) values (
144 X_NON_INV_CTRL_CODE,
145 X_NON_INV_QUANTITY_ON_HAND,
146 X_NON_INV_QUANTITY_ON_ORDER,
147 X_NON_INV_QUANTITY_ON_RESERVE,
148 X_REPLACED_BY_DELIVERABLE_ID,
149 X_CAN_FULFILL_ELECTRONIC_FLAG,
150 X_CAN_FULFILL_PHYSICAL_FLAG,
151 X_TRANSACTION_CURRENCY_CODE,
152 X_FUNCTIONAL_CURRENCY_CODE,
153 X_BUDGET_AMOUNT_TC,
154 X_BUDGET_AMOUNT_FC,
155 X_ACTUAL_AVAIL_FROM_DATE,
156 X_ACTUAL_AVAIL_TO_DATE,
157 X_CHARGEBACK_AMOUNT,
158 X_CHARGEBACK_UOM,
159 X_CHARGEBACK_AMOUNT_CURR_CODE,
160 X_DELIVERABLE_CODE,
161 X_DELIVERABLE_PICK_FLAG,
162 X_CURRENCY_CODE,
163 X_FORECASTED_COST,
164 X_ACTUAL_COST,
165 X_FORECASTED_RESPONSES,
166 X_ACTUAL_RESPONSES,
167 X_COUNTRY,
168 X_DEFAULT_APPROVER_ID,
169 X_ATTRIBUTE_CATEGORY,
170 X_ATTRIBUTE1,
171 X_ATTRIBUTE2,
172 X_ATTRIBUTE3,
173 X_ATTRIBUTE4,
174 X_ATTRIBUTE5,
175 X_ATTRIBUTE6,
176 X_ATTRIBUTE7,
177 X_ATTRIBUTE8,
178 X_ATTRIBUTE9,
179 X_ATTRIBUTE10,
180 X_ATTRIBUTE11,
181 X_ATTRIBUTE12,
182 X_ATTRIBUTE13,
183 X_ATTRIBUTE14,
184 X_ATTRIBUTE15,
185 X_DELIVERABLE_ID,
186 X_OBJECT_VERSION_NUMBER,
187 X_LANGUAGE_CODE,
188 X_VERSION,
189 X_APPLICATION_ID,
190 X_USER_STATUS_ID,
191 X_STATUS_CODE,
192 X_STATUS_DATE,
193 X_PRIVATE_FLAG,
194 X_ACTIVE_FLAG,
195 X_OWNER_USER_ID,
196 X_FUND_SOURCE_ID,
197 X_FUND_SOURCE_TYPE,
198 X_CATEGORY_TYPE_ID,
199 X_CATEGORY_SUB_TYPE_ID,
200 X_KIT_FLAG,
201 X_INVENTORY_FLAG,
202 X_INVENTORY_ITEM_ID,
203 X_INVENTORY_ITEM_ORG_ID,
204 X_CREATION_DATE,
205 X_CREATED_BY,
206 X_LAST_UPDATE_DATE,
207 X_LAST_UPDATED_BY,
208 X_LAST_UPDATE_LOGIN
209 );
210
211 insert into AMS_DELIVERABLES_ALL_TL (
212 DELIVERABLE_ID,
213 LAST_UPDATE_DATE,
214 LAST_UPDATED_BY,
215 CREATION_DATE,
216 CREATED_BY,
217 LAST_UPDATE_LOGIN,
218 DELIVERABLE_NAME,
219 DESCRIPTION,
220 LANGUAGE,
221 SOURCE_LANG
222 ) select
223 X_DELIVERABLE_ID,
224 X_LAST_UPDATE_DATE,
225 X_LAST_UPDATED_BY,
226 X_CREATION_DATE,
227 X_CREATED_BY,
228 X_LAST_UPDATE_LOGIN,
229 X_DELIVERABLE_NAME,
230 X_DESCRIPTION,
231 L.LANGUAGE_CODE,
232 userenv('LANG')
233 from FND_LANGUAGES L
234 where L.INSTALLED_FLAG in ('I', 'B')
235 and not exists
236 (select NULL
237 from AMS_DELIVERABLES_ALL_TL T
238 where T.DELIVERABLE_ID = X_DELIVERABLE_ID
239 and T.LANGUAGE = L.LANGUAGE_CODE);
240
241 open c;
242 fetch c into X_ROWID;
243 if (c%notfound) then
244 close c;
245 raise no_data_found;
246 end if;
247 close c;
248
249 end INSERT_ROW;
250
251 procedure LOCK_ROW (
252 X_DELIVERABLE_ID in NUMBER,
253 X_NON_INV_CTRL_CODE in VARCHAR2,
254 X_NON_INV_QUANTITY_ON_HAND in NUMBER,
255 X_NON_INV_QUANTITY_ON_ORDER in NUMBER,
256 X_NON_INV_QUANTITY_ON_RESERVE in NUMBER,
257 X_REPLACED_BY_DELIVERABLE_ID in NUMBER,
258 X_CAN_FULFILL_ELECTRONIC_FLAG in VARCHAR2,
259 X_CAN_FULFILL_PHYSICAL_FLAG in VARCHAR2,
260 X_TRANSACTION_CURRENCY_CODE in VARCHAR2,
261 X_FUNCTIONAL_CURRENCY_CODE in VARCHAR2,
262 X_BUDGET_AMOUNT_TC in NUMBER,
263 X_BUDGET_AMOUNT_FC in NUMBER,
264 X_ACTUAL_AVAIL_FROM_DATE in DATE,
265 X_ACTUAL_AVAIL_TO_DATE in DATE,
266 X_CHARGEBACK_AMOUNT in NUMBER,
267 X_CHARGEBACK_UOM in VARCHAR2,
268 X_CHARGEBACK_AMOUNT_CURR_CODE in VARCHAR2,
269 X_DELIVERABLE_CODE in VARCHAR2,
270 X_DELIVERABLE_PICK_FLAG in VARCHAR2,
271 X_CURRENCY_CODE in VARCHAR2,
272 X_FORECASTED_COST in NUMBER,
273 X_ACTUAL_COST in NUMBER,
274 X_FORECASTED_RESPONSES in NUMBER,
275 X_ACTUAL_RESPONSES in NUMBER,
276 X_COUNTRY in VARCHAR2,
277 X_DEFAULT_APPROVER_ID in NUMBER,
278 X_ATTRIBUTE_CATEGORY in VARCHAR2,
279 X_ATTRIBUTE1 in VARCHAR2,
280 X_ATTRIBUTE2 in VARCHAR2,
281 X_ATTRIBUTE3 in VARCHAR2,
282 X_ATTRIBUTE4 in VARCHAR2,
283 X_ATTRIBUTE5 in VARCHAR2,
284 X_ATTRIBUTE6 in VARCHAR2,
285 X_ATTRIBUTE7 in VARCHAR2,
286 X_ATTRIBUTE8 in VARCHAR2,
287 X_ATTRIBUTE9 in VARCHAR2,
288 X_ATTRIBUTE10 in VARCHAR2,
289 X_ATTRIBUTE11 in VARCHAR2,
290 X_ATTRIBUTE12 in VARCHAR2,
291 X_ATTRIBUTE13 in VARCHAR2,
292 X_ATTRIBUTE14 in VARCHAR2,
293 X_ATTRIBUTE15 in VARCHAR2,
294 X_OBJECT_VERSION_NUMBER in NUMBER,
295 X_LANGUAGE_CODE in VARCHAR2,
296 X_VERSION in VARCHAR2,
297 X_APPLICATION_ID in NUMBER,
298 X_USER_STATUS_ID in NUMBER,
299 X_STATUS_CODE in VARCHAR2,
300 X_STATUS_DATE in DATE,
301 X_PRIVATE_FLAG in VARCHAR2,
302 X_ACTIVE_FLAG in VARCHAR2,
303 X_OWNER_USER_ID in NUMBER,
304 X_FUND_SOURCE_ID in NUMBER,
305 X_FUND_SOURCE_TYPE in VARCHAR2,
306 X_CATEGORY_TYPE_ID in NUMBER,
307 X_CATEGORY_SUB_TYPE_ID in NUMBER,
308 X_KIT_FLAG in VARCHAR2,
309 X_INVENTORY_FLAG in VARCHAR2,
310 X_INVENTORY_ITEM_ID in NUMBER,
311 X_INVENTORY_ITEM_ORG_ID in NUMBER,
312 X_DELIVERABLE_NAME in VARCHAR2,
313 X_DESCRIPTION in VARCHAR2
314 ) is
315 cursor c is select
316 NON_INV_CTRL_CODE,
317 NON_INV_QUANTITY_ON_HAND,
318 NON_INV_QUANTITY_ON_ORDER,
319 NON_INV_QUANTITY_ON_RESERVE,
320 REPLACED_BY_DELIVERABLE_ID,
321 CAN_FULFILL_ELECTRONIC_FLAG,
322 CAN_FULFILL_PHYSICAL_FLAG,
323 TRANSACTION_CURRENCY_CODE,
324 FUNCTIONAL_CURRENCY_CODE,
325 BUDGET_AMOUNT_TC,
326 BUDGET_AMOUNT_FC,
327 ACTUAL_AVAIL_FROM_DATE,
328 ACTUAL_AVAIL_TO_DATE,
329 CHARGEBACK_AMOUNT,
330 CHARGEBACK_UOM,
331 CHARGEBACK_AMOUNT_CURR_CODE,
332 DELIVERABLE_CODE,
333 DELIVERABLE_PICK_FLAG,
334 CURRENCY_CODE,
335 FORECASTED_COST,
336 ACTUAL_COST,
337 FORECASTED_RESPONSES,
338 ACTUAL_RESPONSES,
339 COUNTRY,
340 DEFAULT_APPROVER_ID,
341 ATTRIBUTE_CATEGORY,
342 ATTRIBUTE1,
343 ATTRIBUTE2,
344 ATTRIBUTE3,
345 ATTRIBUTE4,
346 ATTRIBUTE5,
347 ATTRIBUTE6,
348 ATTRIBUTE7,
349 ATTRIBUTE8,
350 ATTRIBUTE9,
351 ATTRIBUTE10,
352 ATTRIBUTE11,
353 ATTRIBUTE12,
354 ATTRIBUTE13,
355 ATTRIBUTE14,
356 ATTRIBUTE15,
357 OBJECT_VERSION_NUMBER,
358 LANGUAGE_CODE,
359 VERSION,
360 APPLICATION_ID,
361 USER_STATUS_ID,
362 STATUS_CODE,
363 STATUS_DATE,
364 PRIVATE_FLAG,
365 ACTIVE_FLAG,
366 OWNER_USER_ID,
367 FUND_SOURCE_ID,
368 FUND_SOURCE_TYPE,
369 CATEGORY_TYPE_ID,
370 CATEGORY_SUB_TYPE_ID,
371 KIT_FLAG,
372 INVENTORY_FLAG,
373 INVENTORY_ITEM_ID,
374 INVENTORY_ITEM_ORG_ID
375 from AMS_DELIVERABLES_ALL_B
376 where DELIVERABLE_ID = X_DELIVERABLE_ID
377 for update of DELIVERABLE_ID nowait;
378 recinfo c%rowtype;
379
380 cursor c1 is select
381 DELIVERABLE_NAME,
382 DESCRIPTION,
383 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
384 from AMS_DELIVERABLES_ALL_TL
385 where DELIVERABLE_ID = X_DELIVERABLE_ID
386 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
387 for update of DELIVERABLE_ID nowait;
388 begin
389 open c;
390 fetch c into recinfo;
391 if (c%notfound) then
392 close c;
393 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
394 app_exception.raise_exception;
395 end if;
396 close c;
397 if ( ((recinfo.NON_INV_CTRL_CODE = X_NON_INV_CTRL_CODE)
398 OR ((recinfo.NON_INV_CTRL_CODE is null) AND (X_NON_INV_CTRL_CODE is null)))
399 AND ((recinfo.NON_INV_QUANTITY_ON_HAND = X_NON_INV_QUANTITY_ON_HAND)
400 OR ((recinfo.NON_INV_QUANTITY_ON_HAND is null) AND (X_NON_INV_QUANTITY_ON_HAND is null)))
401 AND ((recinfo.NON_INV_QUANTITY_ON_ORDER = X_NON_INV_QUANTITY_ON_ORDER)
402 OR ((recinfo.NON_INV_QUANTITY_ON_ORDER is null) AND (X_NON_INV_QUANTITY_ON_ORDER is null)))
403 AND ((recinfo.NON_INV_QUANTITY_ON_RESERVE = X_NON_INV_QUANTITY_ON_RESERVE)
404 OR ((recinfo.NON_INV_QUANTITY_ON_RESERVE is null) AND (X_NON_INV_QUANTITY_ON_RESERVE is null)))
405 AND ((recinfo.REPLACED_BY_DELIVERABLE_ID = X_REPLACED_BY_DELIVERABLE_ID)
406 OR ((recinfo.REPLACED_BY_DELIVERABLE_ID is null) AND (X_REPLACED_BY_DELIVERABLE_ID is null)))
407 AND ((recinfo.CAN_FULFILL_ELECTRONIC_FLAG = X_CAN_FULFILL_ELECTRONIC_FLAG)
408 OR ((recinfo.CAN_FULFILL_ELECTRONIC_FLAG is null) AND (X_CAN_FULFILL_ELECTRONIC_FLAG is null)))
409 AND ((recinfo.CAN_FULFILL_PHYSICAL_FLAG = X_CAN_FULFILL_PHYSICAL_FLAG)
410 OR ((recinfo.CAN_FULFILL_PHYSICAL_FLAG is null) AND (X_CAN_FULFILL_PHYSICAL_FLAG is null)))
411 AND ((recinfo.TRANSACTION_CURRENCY_CODE = X_TRANSACTION_CURRENCY_CODE)
412 OR ((recinfo.TRANSACTION_CURRENCY_CODE is null) AND (X_TRANSACTION_CURRENCY_CODE is null)))
413 AND ((recinfo.FUNCTIONAL_CURRENCY_CODE = X_FUNCTIONAL_CURRENCY_CODE)
414 OR ((recinfo.FUNCTIONAL_CURRENCY_CODE is null) AND (X_FUNCTIONAL_CURRENCY_CODE is null)))
415 AND ((recinfo.BUDGET_AMOUNT_TC = X_BUDGET_AMOUNT_TC)
416 OR ((recinfo.BUDGET_AMOUNT_TC is null) AND (X_BUDGET_AMOUNT_TC is null)))
417 AND ((recinfo.BUDGET_AMOUNT_FC = X_BUDGET_AMOUNT_FC)
421 AND ((recinfo.ACTUAL_AVAIL_TO_DATE = X_ACTUAL_AVAIL_TO_DATE)
418 OR ((recinfo.BUDGET_AMOUNT_FC is null) AND (X_BUDGET_AMOUNT_FC is null)))
419 AND ((recinfo.ACTUAL_AVAIL_FROM_DATE = X_ACTUAL_AVAIL_FROM_DATE)
420 OR ((recinfo.ACTUAL_AVAIL_FROM_DATE is null) AND (X_ACTUAL_AVAIL_FROM_DATE is null)))
422 OR ((recinfo.ACTUAL_AVAIL_TO_DATE is null) AND (X_ACTUAL_AVAIL_TO_DATE is null)))
423 AND ((recinfo.CHARGEBACK_AMOUNT = X_CHARGEBACK_AMOUNT)
424 OR ((recinfo.CHARGEBACK_AMOUNT is null) AND (X_CHARGEBACK_AMOUNT is null)))
425 AND ((recinfo.CHARGEBACK_UOM = X_CHARGEBACK_UOM)
426 OR ((recinfo.CHARGEBACK_UOM is null) AND (X_CHARGEBACK_UOM is null)))
427 AND ((recinfo.CHARGEBACK_AMOUNT_CURR_CODE = X_CHARGEBACK_AMOUNT_CURR_CODE)
428 OR ((recinfo.CHARGEBACK_AMOUNT_CURR_CODE is null) AND (X_CHARGEBACK_AMOUNT_CURR_CODE is null)))
429 AND ((recinfo.DELIVERABLE_CODE = X_DELIVERABLE_CODE)
430 OR ((recinfo.DELIVERABLE_CODE is null) AND (X_DELIVERABLE_CODE is null)))
431 AND ((recinfo.DELIVERABLE_PICK_FLAG = X_DELIVERABLE_PICK_FLAG)
432 OR ((recinfo.DELIVERABLE_PICK_FLAG is null) AND (X_DELIVERABLE_PICK_FLAG is null)))
433 AND ((recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
434 OR ((recinfo.CURRENCY_CODE is null) AND (X_CURRENCY_CODE is null)))
435 AND ((recinfo.FORECASTED_COST = X_FORECASTED_COST)
436 OR ((recinfo.FORECASTED_COST is null) AND (X_FORECASTED_COST is null)))
437 AND ((recinfo.ACTUAL_COST = X_ACTUAL_COST)
438 OR ((recinfo.ACTUAL_COST is null) AND (X_ACTUAL_COST is null)))
439 AND ((recinfo.FORECASTED_RESPONSES = X_FORECASTED_RESPONSES)
440 OR ((recinfo.FORECASTED_RESPONSES is null) AND (X_FORECASTED_RESPONSES is null)))
441 AND ((recinfo.ACTUAL_RESPONSES = X_ACTUAL_RESPONSES)
442 OR ((recinfo.ACTUAL_RESPONSES is null) AND (X_ACTUAL_RESPONSES is null)))
443 AND ((recinfo.COUNTRY = X_COUNTRY)
444 OR ((recinfo.COUNTRY is null) AND (X_COUNTRY is null)))
445 AND ((recinfo.DEFAULT_APPROVER_ID = X_DEFAULT_APPROVER_ID)
446 OR ((recinfo.DEFAULT_APPROVER_ID is null) AND (X_DEFAULT_APPROVER_ID is null)))
447 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
448 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
449 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
450 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
451 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
452 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
453 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
454 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
455 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
456 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
457 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
458 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
459 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
460 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
461 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
462 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
463 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
464 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
465 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
466 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
467 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
468 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
469 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
470 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
471 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
472 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
473 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
474 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
475 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
476 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
477 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
478 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
479 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
480 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
481 AND (recinfo.LANGUAGE_CODE = X_LANGUAGE_CODE)
482 AND ((recinfo.VERSION = X_VERSION)
483 OR ((recinfo.VERSION is null) AND (X_VERSION is null)))
484 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
485 AND ((recinfo.USER_STATUS_ID = X_USER_STATUS_ID)
486 OR ((recinfo.USER_STATUS_ID is null) AND (X_USER_STATUS_ID is null)))
487 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
488 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
489 AND ((recinfo.STATUS_DATE = X_STATUS_DATE)
490 OR ((recinfo.STATUS_DATE is null) AND (X_STATUS_DATE is null)))
491 AND ((recinfo.PRIVATE_FLAG = X_PRIVATE_FLAG)
492 OR ((recinfo.PRIVATE_FLAG is null) AND (X_PRIVATE_FLAG is null)))
493 AND ((recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
494 OR ((recinfo.ACTIVE_FLAG is null) AND (X_ACTIVE_FLAG is null)))
495 AND ((recinfo.OWNER_USER_ID = X_OWNER_USER_ID)
496 OR ((recinfo.OWNER_USER_ID is null) AND (X_OWNER_USER_ID is null)))
497 AND ((recinfo.FUND_SOURCE_ID = X_FUND_SOURCE_ID)
498 OR ((recinfo.FUND_SOURCE_ID is null) AND (X_FUND_SOURCE_ID is null)))
499 AND ((recinfo.FUND_SOURCE_TYPE = X_FUND_SOURCE_TYPE)
500 OR ((recinfo.FUND_SOURCE_TYPE is null) AND (X_FUND_SOURCE_TYPE is null)))
501 AND ((recinfo.CATEGORY_TYPE_ID = X_CATEGORY_TYPE_ID)
502 OR ((recinfo.CATEGORY_TYPE_ID is null) AND (X_CATEGORY_TYPE_ID is null)))
503 AND ((recinfo.CATEGORY_SUB_TYPE_ID = X_CATEGORY_SUB_TYPE_ID)
504 OR ((recinfo.CATEGORY_SUB_TYPE_ID is null) AND (X_CATEGORY_SUB_TYPE_ID is null)))
505 AND ((recinfo.KIT_FLAG = X_KIT_FLAG)
509 AND ((recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
506 OR ((recinfo.KIT_FLAG is null) AND (X_KIT_FLAG is null)))
507 AND ((recinfo.INVENTORY_FLAG = X_INVENTORY_FLAG)
508 OR ((recinfo.INVENTORY_FLAG is null) AND (X_INVENTORY_FLAG is null)))
510 OR ((recinfo.INVENTORY_ITEM_ID is null) AND (X_INVENTORY_ITEM_ID is null)))
511 AND ((recinfo.INVENTORY_ITEM_ORG_ID = X_INVENTORY_ITEM_ORG_ID)
512 OR ((recinfo.INVENTORY_ITEM_ORG_ID is null) AND (X_INVENTORY_ITEM_ORG_ID is null)))
513 ) then
514 null;
515 else
516 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
517 app_exception.raise_exception;
518 end if;
519
520 for tlinfo in c1 loop
521 if (tlinfo.BASELANG = 'Y') then
522 if ( (tlinfo.DELIVERABLE_NAME = X_DELIVERABLE_NAME)
523 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
524 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
525 ) then
526 null;
527 else
528 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
529 app_exception.raise_exception;
530 end if;
531 end if;
532 end loop;
533 return;
534 end LOCK_ROW;
535
536 procedure UPDATE_ROW (
537 X_DELIVERABLE_ID in NUMBER,
538 X_NON_INV_CTRL_CODE in VARCHAR2,
539 X_NON_INV_QUANTITY_ON_HAND in NUMBER,
540 X_NON_INV_QUANTITY_ON_ORDER in NUMBER,
541 X_NON_INV_QUANTITY_ON_RESERVE in NUMBER,
542 X_REPLACED_BY_DELIVERABLE_ID in NUMBER,
543 X_CAN_FULFILL_ELECTRONIC_FLAG in VARCHAR2,
544 X_CAN_FULFILL_PHYSICAL_FLAG in VARCHAR2,
545 X_TRANSACTION_CURRENCY_CODE in VARCHAR2,
546 X_FUNCTIONAL_CURRENCY_CODE in VARCHAR2,
547 X_BUDGET_AMOUNT_TC in NUMBER,
548 X_BUDGET_AMOUNT_FC in NUMBER,
549 X_ACTUAL_AVAIL_FROM_DATE in DATE,
550 X_ACTUAL_AVAIL_TO_DATE in DATE,
551 X_CHARGEBACK_AMOUNT in NUMBER,
552 X_CHARGEBACK_UOM in VARCHAR2,
553 X_CHARGEBACK_AMOUNT_CURR_CODE in VARCHAR2,
554 X_DELIVERABLE_CODE in VARCHAR2,
555 X_DELIVERABLE_PICK_FLAG in VARCHAR2,
556 X_CURRENCY_CODE in VARCHAR2,
557 X_FORECASTED_COST in NUMBER,
558 X_ACTUAL_COST in NUMBER,
559 X_FORECASTED_RESPONSES in NUMBER,
560 X_ACTUAL_RESPONSES in NUMBER,
561 X_COUNTRY in VARCHAR2,
562 X_DEFAULT_APPROVER_ID in NUMBER,
563 X_ATTRIBUTE_CATEGORY in VARCHAR2,
564 X_ATTRIBUTE1 in VARCHAR2,
565 X_ATTRIBUTE2 in VARCHAR2,
566 X_ATTRIBUTE3 in VARCHAR2,
567 X_ATTRIBUTE4 in VARCHAR2,
568 X_ATTRIBUTE5 in VARCHAR2,
569 X_ATTRIBUTE6 in VARCHAR2,
570 X_ATTRIBUTE7 in VARCHAR2,
571 X_ATTRIBUTE8 in VARCHAR2,
572 X_ATTRIBUTE9 in VARCHAR2,
573 X_ATTRIBUTE10 in VARCHAR2,
574 X_ATTRIBUTE11 in VARCHAR2,
575 X_ATTRIBUTE12 in VARCHAR2,
576 X_ATTRIBUTE13 in VARCHAR2,
577 X_ATTRIBUTE14 in VARCHAR2,
578 X_ATTRIBUTE15 in VARCHAR2,
579 X_OBJECT_VERSION_NUMBER in NUMBER,
580 X_LANGUAGE_CODE in VARCHAR2,
581 X_VERSION in VARCHAR2,
582 X_APPLICATION_ID in NUMBER,
583 X_USER_STATUS_ID in NUMBER,
584 X_STATUS_CODE in VARCHAR2,
585 X_STATUS_DATE in DATE,
586 X_PRIVATE_FLAG in VARCHAR2,
587 X_ACTIVE_FLAG in VARCHAR2,
588 X_OWNER_USER_ID in NUMBER,
589 X_FUND_SOURCE_ID in NUMBER,
590 X_FUND_SOURCE_TYPE in VARCHAR2,
591 X_CATEGORY_TYPE_ID in NUMBER,
592 X_CATEGORY_SUB_TYPE_ID in NUMBER,
593 X_KIT_FLAG in VARCHAR2,
594 X_INVENTORY_FLAG in VARCHAR2,
595 X_INVENTORY_ITEM_ID in NUMBER,
596 X_INVENTORY_ITEM_ORG_ID in NUMBER,
597 X_DELIVERABLE_NAME in VARCHAR2,
598 X_DESCRIPTION in VARCHAR2,
599 X_LAST_UPDATE_DATE in DATE,
600 X_LAST_UPDATED_BY in NUMBER,
601 X_LAST_UPDATE_LOGIN in NUMBER
602 ) is
603 begin
604 update AMS_DELIVERABLES_ALL_B set
605 NON_INV_CTRL_CODE = X_NON_INV_CTRL_CODE,
606 NON_INV_QUANTITY_ON_HAND = X_NON_INV_QUANTITY_ON_HAND,
607 NON_INV_QUANTITY_ON_ORDER = X_NON_INV_QUANTITY_ON_ORDER,
608 NON_INV_QUANTITY_ON_RESERVE = X_NON_INV_QUANTITY_ON_RESERVE,
609 REPLACED_BY_DELIVERABLE_ID = X_REPLACED_BY_DELIVERABLE_ID,
610 CAN_FULFILL_ELECTRONIC_FLAG = X_CAN_FULFILL_ELECTRONIC_FLAG,
611 CAN_FULFILL_PHYSICAL_FLAG = X_CAN_FULFILL_PHYSICAL_FLAG,
612 TRANSACTION_CURRENCY_CODE = X_TRANSACTION_CURRENCY_CODE,
613 FUNCTIONAL_CURRENCY_CODE = X_FUNCTIONAL_CURRENCY_CODE,
614 BUDGET_AMOUNT_TC = X_BUDGET_AMOUNT_TC,
615 BUDGET_AMOUNT_FC = X_BUDGET_AMOUNT_FC,
616 ACTUAL_AVAIL_FROM_DATE = X_ACTUAL_AVAIL_FROM_DATE,
617 ACTUAL_AVAIL_TO_DATE = X_ACTUAL_AVAIL_TO_DATE,
618 CHARGEBACK_AMOUNT = X_CHARGEBACK_AMOUNT,
619 CHARGEBACK_UOM = X_CHARGEBACK_UOM,
620 CHARGEBACK_AMOUNT_CURR_CODE = X_CHARGEBACK_AMOUNT_CURR_CODE,
621 DELIVERABLE_CODE = X_DELIVERABLE_CODE,
622 DELIVERABLE_PICK_FLAG = X_DELIVERABLE_PICK_FLAG,
623 CURRENCY_CODE = X_CURRENCY_CODE,
624 FORECASTED_COST = X_FORECASTED_COST,
625 ACTUAL_COST = X_ACTUAL_COST,
626 FORECASTED_RESPONSES = X_FORECASTED_RESPONSES,
627 ACTUAL_RESPONSES = X_ACTUAL_RESPONSES,
628 COUNTRY = X_COUNTRY,
629 DEFAULT_APPROVER_ID = X_DEFAULT_APPROVER_ID,
630 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
631 ATTRIBUTE1 = X_ATTRIBUTE1,
632 ATTRIBUTE2 = X_ATTRIBUTE2,
633 ATTRIBUTE3 = X_ATTRIBUTE3,
634 ATTRIBUTE4 = X_ATTRIBUTE4,
635 ATTRIBUTE5 = X_ATTRIBUTE5,
636 ATTRIBUTE6 = X_ATTRIBUTE6,
637 ATTRIBUTE7 = X_ATTRIBUTE7,
638 ATTRIBUTE8 = X_ATTRIBUTE8,
639 ATTRIBUTE9 = X_ATTRIBUTE9,
640 ATTRIBUTE10 = X_ATTRIBUTE10,
641 ATTRIBUTE11 = X_ATTRIBUTE11,
642 ATTRIBUTE12 = X_ATTRIBUTE12,
643 ATTRIBUTE13 = X_ATTRIBUTE13,
644 ATTRIBUTE14 = X_ATTRIBUTE14,
645 ATTRIBUTE15 = X_ATTRIBUTE15,
646 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
647 LANGUAGE_CODE = X_LANGUAGE_CODE,
651 STATUS_CODE = X_STATUS_CODE,
648 VERSION = X_VERSION,
649 APPLICATION_ID = X_APPLICATION_ID,
650 USER_STATUS_ID = X_USER_STATUS_ID,
652 STATUS_DATE = X_STATUS_DATE,
653 PRIVATE_FLAG = X_PRIVATE_FLAG,
654 ACTIVE_FLAG = X_ACTIVE_FLAG,
655 OWNER_USER_ID = X_OWNER_USER_ID,
656 FUND_SOURCE_ID = X_FUND_SOURCE_ID,
657 FUND_SOURCE_TYPE = X_FUND_SOURCE_TYPE,
658 CATEGORY_TYPE_ID = X_CATEGORY_TYPE_ID,
659 CATEGORY_SUB_TYPE_ID = X_CATEGORY_SUB_TYPE_ID,
660 KIT_FLAG = X_KIT_FLAG,
661 INVENTORY_FLAG = X_INVENTORY_FLAG,
662 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
663 INVENTORY_ITEM_ORG_ID = X_INVENTORY_ITEM_ORG_ID,
664 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
665 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
666 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
667 where DELIVERABLE_ID = X_DELIVERABLE_ID;
668
669 if (sql%notfound) then
670 raise no_data_found;
671 end if;
672
673 update AMS_DELIVERABLES_ALL_TL set
674 DELIVERABLE_NAME = X_DELIVERABLE_NAME,
675 DESCRIPTION = X_DESCRIPTION,
676 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
677 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
678 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
679 SOURCE_LANG = userenv('LANG')
680 where DELIVERABLE_ID = X_DELIVERABLE_ID
681 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
682
683 if (sql%notfound) then
684 raise no_data_found;
685 end if;
686 end UPDATE_ROW;
687
688 procedure DELETE_ROW (
689 X_DELIVERABLE_ID in NUMBER
690 ) is
691 begin
692 delete from AMS_DELIVERABLES_ALL_TL
693 where DELIVERABLE_ID = X_DELIVERABLE_ID;
694
695 if (sql%notfound) then
696 raise no_data_found;
697 end if;
698
699 delete from AMS_DELIVERABLES_ALL_B
700 where DELIVERABLE_ID = X_DELIVERABLE_ID;
701
702 if (sql%notfound) then
703 raise no_data_found;
704 end if;
705 end DELETE_ROW;
706
707 procedure ADD_LANGUAGE
708 is
709 begin
710 delete from AMS_DELIVERABLES_ALL_TL T
711 where not exists
712 (select NULL
713 from AMS_DELIVERABLES_ALL_B B
714 where B.DELIVERABLE_ID = T.DELIVERABLE_ID
715 );
716
717 update AMS_DELIVERABLES_ALL_TL T set (
718 DELIVERABLE_NAME,
719 DESCRIPTION
720 ) = (select
721 B.DELIVERABLE_NAME,
722 B.DESCRIPTION
723 from AMS_DELIVERABLES_ALL_TL B
724 where B.DELIVERABLE_ID = T.DELIVERABLE_ID
725 and B.LANGUAGE = T.SOURCE_LANG)
726 where (
727 T.DELIVERABLE_ID,
728 T.LANGUAGE
729 ) in (select
730 SUBT.DELIVERABLE_ID,
731 SUBT.LANGUAGE
732 from AMS_DELIVERABLES_ALL_TL SUBB, AMS_DELIVERABLES_ALL_TL SUBT
733 where SUBB.DELIVERABLE_ID = SUBT.DELIVERABLE_ID
734 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
735 and (SUBB.DELIVERABLE_NAME <> SUBT.DELIVERABLE_NAME
736 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
737 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
738 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
739 ));
740
741 insert into AMS_DELIVERABLES_ALL_TL (
742 DELIVERABLE_ID,
743 LAST_UPDATE_DATE,
744 LAST_UPDATED_BY,
745 CREATION_DATE,
746 CREATED_BY,
747 LAST_UPDATE_LOGIN,
748 DELIVERABLE_NAME,
749 DESCRIPTION,
750 LANGUAGE,
751 SOURCE_LANG
752 ) select
753 B.DELIVERABLE_ID,
754 B.LAST_UPDATE_DATE,
755 B.LAST_UPDATED_BY,
756 B.CREATION_DATE,
757 B.CREATED_BY,
758 B.LAST_UPDATE_LOGIN,
759 B.DELIVERABLE_NAME,
760 B.DESCRIPTION,
761 L.LANGUAGE_CODE,
762 B.SOURCE_LANG
763 from AMS_DELIVERABLES_ALL_TL B, FND_LANGUAGES L
764 where L.INSTALLED_FLAG in ('I', 'B')
765 and B.LANGUAGE = userenv('LANG')
766 and not exists
767 (select NULL
768 from AMS_DELIVERABLES_ALL_TL T
769 where T.DELIVERABLE_ID = B.DELIVERABLE_ID
770 and T.LANGUAGE = L.LANGUAGE_CODE);
771 end ADD_LANGUAGE;
772
773 procedure TRANSLATE_ROW(
774 x_deliverable_id in NUMBER
775 , x_deliverable_name in VARCHAR2
776 , x_description in VARCHAR2
777 , x_owner in VARCHAR2
778 ) is
779 begin
780 update AMS_DELIVERABLES_ALL_TL set
781 deliverable_name = nvl(x_deliverable_name, deliverable_name),
782 description = nvl(x_description, description),
783 source_lang = userenv('LANG'),
784 last_update_date = sysdate,
785 last_updated_by = decode(x_owner, 'SEED', 1, 0),
786 last_update_login = 0
787 where deliverable_id = x_deliverable_id
788 and userenv('LANG') in (language, source_lang);
789 end TRANSLATE_ROW;
790
791 procedure LOAD_ROW(
792 X_DELIVERABLE_ID in NUMBER,
793 X_NON_INV_CTRL_CODE in VARCHAR2,
794 X_NON_INV_QUANTITY_ON_HAND in NUMBER,
795 X_NON_INV_QUANTITY_ON_ORDER in NUMBER,
796 X_NON_INV_QUANTITY_ON_RESERVE in NUMBER,
797 X_REPLACED_BY_DELIVERABLE_ID in NUMBER,
798 X_CAN_FULFILL_ELECTRONIC_FLAG in VARCHAR2,
799 X_CAN_FULFILL_PHYSICAL_FLAG in VARCHAR2,
800 X_TRANSACTION_CURRENCY_CODE in VARCHAR2,
801 X_FUNCTIONAL_CURRENCY_CODE in VARCHAR2,
802 X_BUDGET_AMOUNT_TC in NUMBER,
803 X_BUDGET_AMOUNT_FC in NUMBER,
804 X_ACTUAL_AVAIL_FROM_DATE in DATE,
805 X_ACTUAL_AVAIL_TO_DATE in DATE,
806 X_CHARGEBACK_AMOUNT in NUMBER,
807 X_CHARGEBACK_UOM in VARCHAR2,
808 X_CHARGEBACK_AMOUNT_CURR_CODE in VARCHAR2,
809 X_DELIVERABLE_CODE in VARCHAR2,
810 X_DELIVERABLE_PICK_FLAG in VARCHAR2,
811 X_CURRENCY_CODE in VARCHAR2,
812 X_FORECASTED_COST in NUMBER,
816 X_COUNTRY in VARCHAR2,
813 X_ACTUAL_COST in NUMBER,
814 X_FORECASTED_RESPONSES in NUMBER,
815 X_ACTUAL_RESPONSES in NUMBER,
817 X_DEFAULT_APPROVER_ID in NUMBER,
818 X_ATTRIBUTE_CATEGORY in VARCHAR2,
819 X_ATTRIBUTE1 in VARCHAR2,
820 X_ATTRIBUTE2 in VARCHAR2,
821 X_ATTRIBUTE3 in VARCHAR2,
822 X_ATTRIBUTE4 in VARCHAR2,
823 X_ATTRIBUTE5 in VARCHAR2,
824 X_ATTRIBUTE6 in VARCHAR2,
825 X_ATTRIBUTE7 in VARCHAR2,
826 X_ATTRIBUTE8 in VARCHAR2,
827 X_ATTRIBUTE9 in VARCHAR2,
828 X_ATTRIBUTE10 in VARCHAR2,
829 X_ATTRIBUTE11 in VARCHAR2,
830 X_ATTRIBUTE12 in VARCHAR2,
831 X_ATTRIBUTE13 in VARCHAR2,
832 X_ATTRIBUTE14 in VARCHAR2,
833 X_ATTRIBUTE15 in VARCHAR2,
834 X_LANGUAGE_CODE in VARCHAR2,
835 X_VERSION in VARCHAR2,
836 X_APPLICATION_ID in NUMBER,
837 X_USER_STATUS_ID in NUMBER,
838 X_STATUS_CODE in VARCHAR2,
839 X_STATUS_DATE in DATE,
840 X_PRIVATE_FLAG in VARCHAR2,
841 X_ACTIVE_FLAG in VARCHAR2,
842 X_OWNER_USER_ID in NUMBER,
843 X_FUND_SOURCE_ID in NUMBER,
844 X_FUND_SOURCE_TYPE in VARCHAR2,
845 X_CATEGORY_TYPE_ID in NUMBER,
846 X_CATEGORY_SUB_TYPE_ID in NUMBER,
847 X_KIT_FLAG in VARCHAR2,
848 X_INVENTORY_FLAG in VARCHAR2,
849 X_INVENTORY_ITEM_ID in NUMBER,
850 X_INVENTORY_ITEM_ORG_ID in NUMBER,
851 X_DELIVERABLE_NAME in VARCHAR2,
852 X_DESCRIPTION in VARCHAR2,
853 X_Owner VARCHAR2
854 ) is
855
856 l_user_id number := 0;
857 l_obj_verno number;
858 l_dummy_char varchar2(1);
859 l_row_id varchar2(100);
860 l_deliverable_id number;
861
862 cursor c_obj_verno is
863 select object_version_number
864 from AMS_DELIVERABLES_ALL_B
865 where deliverable_id = X_DELIVERABLE_ID;
866
867 cursor c_chk_del_exists is
868 select 'x'
869 from AMS_DELIVERABLES_ALL_B
870 where deliverable_id = X_DELIVERABLE_ID;
871
872 cursor c_get_delid is
873 select AMS_DELIVERABLES_ALL_B_S.nextval
874 from dual;
875
876 BEGIN
877
878 if X_OWNER = 'SEED' then
879 l_user_id := 1;
880 end if;
881
882 open c_chk_del_exists;
883 fetch c_chk_del_exists into l_dummy_char;
884 if c_chk_del_exists%notfound
885 then
886 close c_chk_del_exists;
887 if X_DELIVERABLE_ID is null
888 then
889 open c_get_delid;
890 fetch c_get_delid into l_deliverable_id;
891 close c_get_delid;
892 else
893 l_deliverable_id := X_DELIVERABLE_ID;
894 end if;
895 l_obj_verno := 1;
896 AMS_DELIVERABLES_ALL_PKG.INSERT_ROW(
897 X_ROWID => l_row_id,
898 X_DELIVERABLE_ID => l_deliverable_id,
899 X_OBJECT_VERSION_NUMBER => l_obj_verno,
900 X_NON_INV_CTRL_CODE => X_NON_INV_CTRL_CODE ,
901 X_NON_INV_QUANTITY_ON_HAND => X_NON_INV_QUANTITY_ON_HAND ,
902 X_NON_INV_QUANTITY_ON_ORDER => X_NON_INV_QUANTITY_ON_ORDER ,
903 X_NON_INV_QUANTITY_ON_RESERVE => X_NON_INV_QUANTITY_ON_RESERVE ,
904 X_REPLACED_BY_DELIVERABLE_ID => X_REPLACED_BY_DELIVERABLE_ID ,
905 X_CAN_FULFILL_ELECTRONIC_FLAG => X_CAN_FULFILL_ELECTRONIC_FLAG ,
906 X_CAN_FULFILL_PHYSICAL_FLAG => X_CAN_FULFILL_PHYSICAL_FLAG ,
907 X_TRANSACTION_CURRENCY_CODE => X_TRANSACTION_CURRENCY_CODE ,
908 X_FUNCTIONAL_CURRENCY_CODE => X_FUNCTIONAL_CURRENCY_CODE ,
909 X_BUDGET_AMOUNT_TC => X_BUDGET_AMOUNT_TC ,
910 X_BUDGET_AMOUNT_FC => X_BUDGET_AMOUNT_FC ,
911 X_ACTUAL_AVAIL_FROM_DATE => X_ACTUAL_AVAIL_FROM_DATE ,
912 X_ACTUAL_AVAIL_TO_DATE => X_ACTUAL_AVAIL_TO_DATE ,
913 X_CHARGEBACK_AMOUNT => X_CHARGEBACK_AMOUNT ,
914 X_CHARGEBACK_UOM => X_CHARGEBACK_UOM ,
915 X_CHARGEBACK_AMOUNT_CURR_CODE => X_CHARGEBACK_AMOUNT_CURR_CODE ,
916 X_DELIVERABLE_CODE => X_DELIVERABLE_CODE ,
917 X_DELIVERABLE_PICK_FLAG => X_DELIVERABLE_PICK_FLAG ,
918 X_CURRENCY_CODE => X_CURRENCY_CODE ,
919 X_FORECASTED_COST => X_FORECASTED_COST ,
920 X_ACTUAL_COST => X_ACTUAL_COST ,
921 X_FORECASTED_RESPONSES => X_FORECASTED_RESPONSES ,
922 X_ACTUAL_RESPONSES => X_ACTUAL_RESPONSES ,
923 X_COUNTRY => X_COUNTRY ,
924 X_DEFAULT_APPROVER_ID => X_DEFAULT_APPROVER_ID ,
925 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
926 X_ATTRIBUTE1 => X_ATTRIBUTE1,
927 X_ATTRIBUTE2 => X_ATTRIBUTE2,
928 X_ATTRIBUTE3 => X_ATTRIBUTE3,
929 X_ATTRIBUTE4 => X_ATTRIBUTE4,
930 X_ATTRIBUTE5 => X_ATTRIBUTE5,
931 X_ATTRIBUTE6 => X_ATTRIBUTE6,
932 X_ATTRIBUTE7 => X_ATTRIBUTE7,
933 X_ATTRIBUTE8 => X_ATTRIBUTE8,
934 X_ATTRIBUTE9 => X_ATTRIBUTE9,
935 X_ATTRIBUTE10 => X_ATTRIBUTE10,
936 X_ATTRIBUTE11 => X_ATTRIBUTE11,
937 X_ATTRIBUTE12 => X_ATTRIBUTE12,
938 X_ATTRIBUTE13 => X_ATTRIBUTE13,
939 X_ATTRIBUTE14 => X_ATTRIBUTE14,
940 X_ATTRIBUTE15 => X_ATTRIBUTE15,
941 X_LANGUAGE_CODE => X_LANGUAGE_CODE ,
942 X_VERSION => X_VERSION ,
943 X_APPLICATION_ID => X_APPLICATION_ID ,
944 X_USER_STATUS_ID => X_USER_STATUS_ID ,
945 X_STATUS_CODE => X_STATUS_CODE ,
946 X_STATUS_DATE => X_STATUS_DATE ,
947 X_PRIVATE_FLAG => X_PRIVATE_FLAG ,
948 X_ACTIVE_FLAG => X_ACTIVE_FLAG ,
949 X_OWNER_USER_ID => X_OWNER_USER_ID ,
950 X_FUND_SOURCE_ID => X_FUND_SOURCE_ID ,
951 X_FUND_SOURCE_TYPE => X_FUND_SOURCE_TYPE ,
952 X_CATEGORY_TYPE_ID => X_CATEGORY_TYPE_ID ,
953 X_CATEGORY_SUB_TYPE_ID => X_CATEGORY_SUB_TYPE_ID ,
954 X_KIT_FLAG => X_KIT_FLAG ,
955 X_INVENTORY_FLAG => X_INVENTORY_FLAG ,
956 X_INVENTORY_ITEM_ID => X_INVENTORY_ITEM_ID ,
957 X_INVENTORY_ITEM_ORG_ID => X_INVENTORY_ITEM_ORG_ID ,
958 X_DELIVERABLE_NAME => X_DELIVERABLE_NAME ,
959 X_DESCRIPTION => X_DESCRIPTION,
963 X_LAST_UPDATED_BY => l_user_id,
960 X_CREATION_DATE => SYSDATE,
961 X_CREATED_BY => l_user_id,
962 X_LAST_UPDATE_DATE => SYSDATE,
964 X_LAST_UPDATE_LOGIN => 0
965 );
966 else
967 close c_chk_del_exists;
968 open c_obj_verno;
969 fetch c_obj_verno into l_obj_verno;
970 close c_obj_verno;
971 AMS_DELIVERABLES_ALL_PKG.UPDATE_ROW(
972 X_DELIVERABLE_ID => l_deliverable_id,
973 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
974 X_NON_INV_CTRL_CODE => X_NON_INV_CTRL_CODE ,
975 X_NON_INV_QUANTITY_ON_HAND => X_NON_INV_QUANTITY_ON_HAND ,
976 X_NON_INV_QUANTITY_ON_ORDER => X_NON_INV_QUANTITY_ON_ORDER ,
977 X_NON_INV_QUANTITY_ON_RESERVE => X_NON_INV_QUANTITY_ON_RESERVE ,
978 X_REPLACED_BY_DELIVERABLE_ID => X_REPLACED_BY_DELIVERABLE_ID ,
979 X_CAN_FULFILL_ELECTRONIC_FLAG => X_CAN_FULFILL_ELECTRONIC_FLAG ,
980 X_CAN_FULFILL_PHYSICAL_FLAG => X_CAN_FULFILL_PHYSICAL_FLAG ,
981 X_TRANSACTION_CURRENCY_CODE => X_TRANSACTION_CURRENCY_CODE ,
982 X_FUNCTIONAL_CURRENCY_CODE => X_FUNCTIONAL_CURRENCY_CODE ,
983 X_BUDGET_AMOUNT_TC => X_BUDGET_AMOUNT_TC ,
984 X_BUDGET_AMOUNT_FC => X_BUDGET_AMOUNT_FC ,
985 X_ACTUAL_AVAIL_FROM_DATE => X_ACTUAL_AVAIL_FROM_DATE ,
986 X_ACTUAL_AVAIL_TO_DATE => X_ACTUAL_AVAIL_TO_DATE ,
987 X_CHARGEBACK_AMOUNT => X_CHARGEBACK_AMOUNT ,
988 X_CHARGEBACK_UOM => X_CHARGEBACK_UOM ,
989 X_CHARGEBACK_AMOUNT_CURR_CODE => X_CHARGEBACK_AMOUNT_CURR_CODE ,
990 X_DELIVERABLE_CODE => X_DELIVERABLE_CODE ,
991 X_DELIVERABLE_PICK_FLAG => X_DELIVERABLE_PICK_FLAG ,
992 X_CURRENCY_CODE => X_CURRENCY_CODE ,
993 X_FORECASTED_COST => X_FORECASTED_COST ,
994 X_ACTUAL_COST => X_ACTUAL_COST ,
995 X_FORECASTED_RESPONSES => X_FORECASTED_RESPONSES ,
996 X_ACTUAL_RESPONSES => X_ACTUAL_RESPONSES ,
997 X_COUNTRY => X_COUNTRY ,
998 X_DEFAULT_APPROVER_ID => X_DEFAULT_APPROVER_ID ,
999 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
1000 X_ATTRIBUTE1 => X_ATTRIBUTE1,
1001 X_ATTRIBUTE2 => X_ATTRIBUTE2,
1002 X_ATTRIBUTE3 => X_ATTRIBUTE3,
1003 X_ATTRIBUTE4 => X_ATTRIBUTE4,
1004 X_ATTRIBUTE5 => X_ATTRIBUTE5,
1005 X_ATTRIBUTE6 => X_ATTRIBUTE6,
1006 X_ATTRIBUTE7 => X_ATTRIBUTE7,
1007 X_ATTRIBUTE8 => X_ATTRIBUTE8,
1008 X_ATTRIBUTE9 => X_ATTRIBUTE9,
1009 X_ATTRIBUTE10 => X_ATTRIBUTE10,
1010 X_ATTRIBUTE11 => X_ATTRIBUTE11,
1011 X_ATTRIBUTE12 => X_ATTRIBUTE12,
1012 X_ATTRIBUTE13 => X_ATTRIBUTE13,
1013 X_ATTRIBUTE14 => X_ATTRIBUTE14,
1014 X_ATTRIBUTE15 => X_ATTRIBUTE15,
1015 X_LANGUAGE_CODE => X_LANGUAGE_CODE ,
1016 X_VERSION => X_VERSION ,
1017 X_APPLICATION_ID => X_APPLICATION_ID ,
1018 X_USER_STATUS_ID => X_USER_STATUS_ID ,
1019 X_STATUS_CODE => X_STATUS_CODE ,
1020 X_STATUS_DATE => X_STATUS_DATE ,
1021 X_PRIVATE_FLAG => X_PRIVATE_FLAG ,
1022 X_ACTIVE_FLAG => X_ACTIVE_FLAG ,
1023 X_OWNER_USER_ID => X_OWNER_USER_ID ,
1024 X_FUND_SOURCE_ID => X_FUND_SOURCE_ID ,
1025 X_FUND_SOURCE_TYPE => X_FUND_SOURCE_TYPE ,
1026 X_CATEGORY_TYPE_ID => X_CATEGORY_TYPE_ID ,
1027 X_CATEGORY_SUB_TYPE_ID => X_CATEGORY_SUB_TYPE_ID ,
1028 X_KIT_FLAG => X_KIT_FLAG ,
1029 X_INVENTORY_FLAG => X_INVENTORY_FLAG ,
1030 X_INVENTORY_ITEM_ID => X_INVENTORY_ITEM_ID ,
1031 X_INVENTORY_ITEM_ORG_ID => X_INVENTORY_ITEM_ORG_ID ,
1032 X_DELIVERABLE_NAME => X_DELIVERABLE_NAME ,
1033 X_DESCRIPTION => X_DESCRIPTION,
1034 X_LAST_UPDATE_DATE => SYSDATE,
1035 X_LAST_UPDATED_BY => l_user_id,
1036 X_LAST_UPDATE_LOGIN => 0
1037 );
1038 end if;
1039 END LOAD_ROW;
1040
1041
1042 end AMS_DELIVERABLES_ALL_PKG;