1 PACKAGE BODY OKC_MANAGE_DELIVERABLES_GRP AS
2 /* $Header: OKCGMDLB.pls 120.5.12020000.7 2013/04/09 08:47:56 serukull ship $ */
3
4 ---------------------------------------------------------------------------
5 -- TYPE Definitions
6 ---------------------------------------------------------------------------
7 ---------------------------------------------------------------------------
8 -- Global VARIABLES
9 ---------------------------------------------------------------------------
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_MANAGE_DELIVERABLES_GRP';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
12 g_module CONSTANT VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
13 G_ENTITY_NAME CONSTANT VARCHAR2(40) := 'OKC_DELIVERABLES';
14
15 ------------------------------------------------------------------------------
16 -- GLOBAL CONSTANTS
17 ------------------------------------------------------------------------------
18 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
19 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
20 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
21 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
22 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
23 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
24 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
25 ---------------------------------------------------------------------------
26 -- START: Helper Procedures and Functions
27 ---------------------------------------------------------------------------
28
29 /**
30 * This helper procedure check for status history record already in
31 * okc_del_status_history table, if not, creates a new status history
32 * record for given Status.
33 */
34 PROCEDURE checkAndCreateStatusHistory (
35 p_deliverable_id IN NUMBER,
36 p_deliverable_status IN VARCHAR2,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_return_status OUT NOCOPY VARCHAR2)
40 IS
41 l_api_version CONSTANT NUMBER := 1;
42 l_api_name CONSTANT VARCHAR2(30) := 'checkAndCreateStatusHistory';
43 l_del_row_count PLS_INTEGER;
44 BEGIN
45
46 l_del_row_count := 0;
47
48 -- check for existing status history record
49 SELECT count(*) into l_del_row_count
50 FROM okc_del_status_history
51 WHERE deliverable_id = p_deliverable_id
52 AND deliverable_status = p_deliverable_status;
53
54 IF l_del_row_count = 0 THEN
55
56 -- create status history record
57 OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history(
58 p_api_version => l_api_version,
59 p_init_msg_list => G_FALSE,
60 p_del_id => p_deliverable_id,
61 p_deliverable_status => p_deliverable_status,
62 x_msg_data => x_msg_data,
63 x_msg_count => x_msg_count,
64 x_return_status => x_return_status);
65
66 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
67 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'701a: Finished OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history'||x_return_status);
68 END IF;
69 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
71 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
72 RAISE FND_API.G_EXC_ERROR ;
73 END IF;
74 END IF;
75
76 EXCEPTION
77 WHEN FND_API.G_EXC_ERROR THEN
78 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
79 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'100: leaving with G_EXC_ERROR');
80 END IF;
81 x_return_status := G_RET_STS_ERROR;
82 FND_MSG_PUB.Count_And_Get(
83 p_count => x_msg_count,
84 p_data => x_msg_data
85 );
86
87 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
88 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
89 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving with G_EXC_UNEXPECTED_ERROR');
90 END IF;
91 x_return_status := G_RET_STS_UNEXP_ERROR;
92 FND_MSG_PUB.Count_And_Get(
93 p_count => x_msg_count,
94 p_data => x_msg_data
95 );
96
97 WHEN OTHERS THEN
98 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
99 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'100: leaving with G_EXC_UNEXPECTED_ERROR');
100 END IF;
101 x_return_status := G_RET_STS_UNEXP_ERROR;
102 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
103 END IF;
104 FND_MSG_PUB.Count_And_Get(
105 p_count => x_msg_count,
106 p_data => x_msg_data
107 );
108 END; -- checkAndCreateStatusHistory
109
110 /**
111 * Helper method to return proper number value for the day of month.
112 * the stored code returns like DOFMXX
113 */
114 FUNCTION getDayOfMonth(p_code IN VARCHAR2)
115 return NUMBER
116 IS
117 l_day_of_month number;
118 BEGIN
119 -- initialize
120 l_day_of_month := -1;
121
122 -- if input code is not null
123 IF p_code is not null THEN
124 IF p_code = 'LDOFM' THEN
125 return 99;
126 ELSE
127 l_day_of_month := substr(p_code, 5);
128 return (l_day_of_month);
129 END IF;
130 END IF;
131 return NULL;
132 END;
133
134 /**
135 * Helper method to return correct status of new deliverable instances
136 * This method checks, if status of any deliverable intance is 'INACTIVE', the case
137 * where Instances remain INACTIVE, hence new generated instances should be of
138 * same status.
139 */
140 FUNCTION checkStatusOfExistingInstances(p_bus_doc_id IN NUMBER,
141 p_bus_doc_type IN VARCHAR2,
142 p_bus_doc_version IN NUMBER,
143 p_del_id IN NUMBER)
144 return VARCHAR2
145 IS
146 l_api_name CONSTANT VARCHAR2(50) := 'checkStatusOfExistingInstances';
147 l_del_status OKC_DELIVERABLES.deliverable_status%TYPE;
148 BEGIN
149
150 -- initialize
151 l_del_status := null;
152
153 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: INTO '||G_PKG_NAME ||'.'||l_api_name);
155 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: doc id '||p_bus_doc_id);
156 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'102: doc type '||p_bus_doc_type);
157 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'103: doc version '||p_bus_doc_version);
158 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: del id '||p_del_id);
159 END IF;
160
161 SELECT deliverable_status into l_del_status
162 FROM okc_deliverables
163 WHERE business_document_id = p_bus_doc_id
164 AND business_document_type = p_bus_doc_type
165 AND business_document_version = p_bus_doc_version
166 AND recurring_del_parent_id = p_del_id
167 AND rownum = 1;
168
169 IF l_del_status is not null THEN
170 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
171 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'105: Status returning '||l_del_status);
172 END IF;
173
174 IF l_del_status = 'INACTIVE' THEN
175 return 'INACTIVE';
176 ELSE
177 return 'OPEN';
178 END IF;
179 END IF;
180 return NULL;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
185 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
186 END IF;
187 Okc_Api.Set_Message(G_APP_NAME,
188 'OKC_DEL_ERR_CHK_INSTS_STS');
189 RAISE FND_API.G_EXC_ERROR;
190 END;
191
192 /**
193 * Helper method to return 'Y' if recurring deliverable definition already
194 * has instances in place for given bus doc version.
195 */
196 FUNCTION hasInstances(p_bus_doc_id IN NUMBER,
197 p_bus_doc_type IN VARCHAR2,
198 p_bus_doc_version IN NUMBER,
199 p_del_id IN NUMBER)
200 return VARCHAR2
201 IS
202 l_del_row_count number;
203 BEGIN
204 -- initialize
205 l_del_row_count := 0;
206 SELECT count(*) into l_del_row_count
207 FROM okc_deliverables
208 WHERE business_document_id = p_bus_doc_id
209 AND business_document_type = p_bus_doc_type
210 AND business_document_version = p_bus_doc_version
211 AND recurring_del_parent_id = p_del_id;
212
213 IF l_del_row_count > 0 THEN
214 return 'Y';
215 ELSE
216 return 'N';
217 END IF;
218 return NULL;
219 END;
220
221 /**
222 * Helper method to generate recurring deliverable instances and return
223 * table of records containing deliverable details
224 */
225 PROCEDURE generate_del_instances(
226 p_recurr_start_date IN DATE,
227 p_recurr_end_date IN DATE,
228 p_repeat_duration IN NUMBER,
229 p_repeat_day_of_month IN NUMBER,
230 p_repeat_day_of_week IN NUMBER,
231 delRecord okc_deliverables%ROWTYPE,
232 p_change_status_to IN VARCHAR2)
233 IS
234
235 --- for recurring dates
236 l_recurring_dates OKC_DELIVERABLE_PROCESS_PVT.recurring_dates_tab_type;
237 delInstanceRecTab OKC_DELIVERABLE_PROCESS_PVT.delRecTabType;
238
239 l_api_version CONSTANT VARCHAR2(30) := 1;
240 l_api_name CONSTANT VARCHAR2(30) := 'generate_del_instances';
241
242 l_msg_data VARCHAR2(30);
243 l_msg_count NUMBER;
244 l_return_status VARCHAR2(1);
245 l_manage_yn VARCHAR2(1);
246
247 j PLS_INTEGER;
248 st_hist_count PLS_INTEGER;
249 st_hist_count1 PLS_INTEGER;
250 del_count PLS_INTEGER;
251
252 TYPE DelIdList IS TABLE OF NUMBER
253 INDEX BY BINARY_INTEGER;
254 deliverableIds DelIdList;
255
256 delStsTab OKC_DELIVERABLE_PROCESS_PVT.delHistTabType;
257 delStsTab1 OKC_DELIVERABLE_PROCESS_PVT.delHistTabType;
258 /* Bug 10048345 */
259 CURSOR find_if_deliverable_exists(ACTUAL_DUE_DATE_passed IN DATE , RECURRING_DEL_PARENT_ID_passed IN NUMBER) IS
260 SELECT deliverable_id,ACTUAL_DUE_DATE,RECURRING_DEL_PARENT_ID FROM OKC_DELIVERABLES DEL
261 WHERE DEL.RECURRING_DEL_PARENT_ID= RECURRING_DEL_PARENT_ID_passed
262 AND DEL.ACTUAL_DUE_DATE = ACTUAL_DUE_DATE_passed ;
263 del_find_rec find_if_deliverable_exists%ROWTYPE;
264 /* Bug 10048345 */
265
266 x_return_status VARCHAR2(1);
267 x_msg_count NUMBER;
268 x_msg_data VARCHAR2(1000);
269 x_errorcode NUMBER;
270 BEGIN
271
272 -- initialize
273 l_manage_yn := 'N';
274 j := 0;
275 del_count := 0;
276 st_hist_count := 0;
277 st_hist_count1 := 0;
278
279 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
280 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered '||G_PKG_NAME ||'.'||l_api_name);
281 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'200: Recurr start date'||p_recurr_start_date);
282 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: p_recurr_end_date'||p_recurr_end_date);
283 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'400: p_repeat_day_of_month'||p_repeat_day_of_month);
284 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'500: p_change_status_to'||p_change_status_to);
285 END IF;
286
287 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
288 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'501: Calling getRecurringDates');
289 END IF;
290 --- Calculate recurring instances
291 OKC_DELIVERABLE_PROCESS_PVT.get_recurring_dates(
292 p_api_version => l_api_version,
293 p_init_msg_list => G_FALSE,
294 p_start_date => p_recurr_start_date,
295 p_end_date => p_recurr_end_date,
296 p_frequency => p_repeat_duration,
297 p_recurr_day_of_month => p_repeat_day_of_month,
298 p_recurr_day_of_week => p_repeat_day_of_week,
299 x_recurr_dates => l_recurring_dates,
300 x_msg_data => l_msg_data,
301 x_msg_count => l_msg_count,
302 x_return_status => l_return_status);
303 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
304 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished recurring dates api'||l_return_status);
305 END IF;
306
307 -- check status
308 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
310 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
311 RAISE FND_API.G_EXC_ERROR ;
312 END IF;
313
314 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
315 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'601: Recurr Dates Count'||l_recurring_dates.count);
316 END IF;
317
318 IF l_recurring_dates.count > 0 THEN
319 --- loop through returned dates
320 FOR m IN l_recurring_dates.FIRST..l_recurring_dates.LAST LOOP
321
322 ---- Here decide which instances to create
323 /* Bug 10048345 */
324 OPEN find_if_deliverable_exists(l_recurring_dates(m) , delRecord.recurring_del_parent_id);
325 FETCH find_if_deliverable_exists INTO del_find_rec;
326 IF find_if_deliverable_exists%NOTFOUND THEN
327
328 /* Bug 10048345 */
329
330
331 j := j+1;
332 --- Set the deliverable definition to the new instance
333 delInstanceRecTab(j) := delRecord;
334
335 --- set the deliverable id
336 select okc_deliverable_id_s.nextval
337 INTO delInstanceRecTab(j).deliverable_id from dual;
338
339 --- set the actual due date to the new instance
340 delInstanceRecTab(j).actual_due_date :=
341 l_recurring_dates(m);
342
343 --- set/reset other deliverable attributes
344 delInstanceRecTab(j).recurring_yn := 'N';
345
346 -- NULL out definition columns
347 delInstanceRecTab(j).amendment_operation := NULL;
348 delInstanceRecTab(j).amendment_notes := NULL;
349 delInstanceRecTab(j).summary_amend_operation_code := NULL;
350 delInstanceRecTab(j).last_amendment_date := NULL;
351 delInstanceRecTab(j).start_event_date := NULL;
352 delInstanceRecTab(j).end_event_date := NULL;
353
354 -- set the original deliverable id as the original deliverable id
355 -- on recurring deliverable definition
356 delInstanceRecTab(j).original_deliverable_id :=
357 delRecord.original_deliverable_id;
358
359 -- set the recurring del parent id as the deliverable id
360 -- of recurring deliverable definition
361 delInstanceRecTab(j).recurring_del_parent_id :=
362 delRecord.deliverable_id;
363
364 -- copy attachments from the definition, if any
365 -- check if attachments exists
366 IF OKC_DELIVERABLE_PROCESS_PVT.attachment_exists(
367 p_entity_name => G_ENTITY_NAME,
368 p_pk1_value => delRecord.deliverable_id ) THEN
369
370 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
371 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'6011: Call Copy Attachments ');
372 END IF;
373
374 -- copy attachments
375 -- bug#3667712 added X_CREATED_BY,X_LAST_UPDATE_LOGIN params
376 fnd_attached_documents2_pkg.copy_attachments(
377 X_from_entity_name => G_ENTITY_NAME,
378 X_from_pk1_value => delRecord.deliverable_id,
379 X_to_entity_name => G_ENTITY_NAME,
380 X_to_pk1_value => to_char(delInstanceRecTab(j).deliverable_id),
381 X_CREATED_BY => FND_GLOBAL.User_id,
382 X_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id);
383 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'6012: Done Copy Attachments ');
385 END IF;
386
387 END IF;
388
389 -- set status history record for INACTIVE status
390 st_hist_count := st_hist_count+1;
391 delStsTab(st_hist_count).deliverable_id := delInstanceRecTab(j).deliverable_id;
392 delStsTab(st_hist_count).deliverable_status:= 'INACTIVE';
393 delStsTab(st_hist_count).status_change_date:= sysdate;
394 delStsTab(st_hist_count).status_change_notes:= null;
395 delStsTab(st_hist_count).object_version_number:= 1;
396 delStsTab(st_hist_count).created_by:= Fnd_Global.User_Id;
397 delStsTab(st_hist_count).creation_date := sysdate;
398 delStsTab(st_hist_count).last_updated_by:= Fnd_Global.User_Id;
399 delStsTab(st_hist_count).last_update_date := sysdate;
400 delStsTab(st_hist_count).last_update_login := Fnd_Global.Login_Id;
401
402 --- set the status, if required from updateDeliverables
403 IF (p_change_status_to is not null) AND (p_change_status_to = 'OPEN') THEN
404
405 --- change status of this new deliverable and add new status history
406 --- record
407 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
408 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'602: Change status of this new deliverable '||delInstanceRecTab(j).deliverable_id);
409 END IF;
410
411 delInstanceRecTab(j).deliverable_status := p_change_status_to;
412 delInstanceRecTab(j).manage_yn := 'Y';
413
414 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
415 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'607: Set Deliverable status history record to OPEN ');
416 END IF;
417
418 -- set status history record for OPEN status
419 st_hist_count1 := st_hist_count1+1;
420 delStsTab1(st_hist_count1).deliverable_id := delInstanceRecTab(j).deliverable_id;
421 delStsTab1(st_hist_count1).deliverable_status:= p_change_status_to;
422 delStsTab1(st_hist_count1).status_change_date:= sysdate;
423 delStsTab1(st_hist_count1).status_change_notes:= null;
424 delStsTab1(st_hist_count1).object_version_number:= 1;
425 delStsTab1(st_hist_count1).created_by:= Fnd_Global.User_Id;
426 delStsTab1(st_hist_count1).creation_date := sysdate;
427 delStsTab1(st_hist_count1).last_updated_by:= Fnd_Global.User_Id;
428 delStsTab1(st_hist_count1).last_update_date := sysdate;
429 delStsTab1(st_hist_count1).last_update_login := Fnd_Global.Login_Id;
430
431 END IF;
432 --- set object version number
433 delInstanceRecTab(j).object_version_number:= 1;
434
435 --- set who columns
436 delInstanceRecTab(j).created_by:= Fnd_Global.User_Id;
437 delInstanceRecTab(j).creation_date := sysdate;
438 delInstanceRecTab(j).last_updated_by:= Fnd_Global.User_Id;
439 delInstanceRecTab(j).last_update_date := sysdate;
440 delInstanceRecTab(j).last_update_login := Fnd_Global.Login_Id;
441 /* Bug 10048345*/
442 ELSE
443 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
444 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'####1'||del_find_rec.deliverable_id||'##2'||del_find_rec.ACTUAL_DUE_DATE||'#3'||del_find_rec.RECURRING_DEL_PARENT_ID);
445 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'##1'||l_recurring_dates(m)||'#2'|| delRecord.recurring_del_parent_id);
446 END IF;
447
448
449 END IF;
450 IF find_if_deliverable_exists%ISOPEN THEN
451 CLOSE find_if_deliverable_exists ;
452 END IF;
453 /* Bug 10048345*/
454
455
456 END LOOP;
457 END IF;
458
459 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
460 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'603: END of Loop, creating Instances -- count '||delInstanceRecTab.count);
461 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'604: Bulk Inserting Instances Records ');
462 END IF;
463 --- bulk insert for deliverable's recurring instances and
464 --- actual due date
465 IF find_if_deliverable_exists%ISOPEN THEN
466 CLOSE find_if_deliverable_exists ;
467 END IF;
468
469
470 IF delInstanceRecTab.count > 0 THEN
471
472 FOR i IN delInstanceRecTab.FIRST..delInstanceRecTab.LAST LOOP
473 INSERT INTO okc_deliverables
474 (DELIVERABLE_ID,
475 BUSINESS_DOCUMENT_TYPE ,
476 BUSINESS_DOCUMENT_ID ,
477 BUSINESS_DOCUMENT_NUMBER ,
478 DELIVERABLE_TYPE ,
479 RESPONSIBLE_PARTY ,
480 INTERNAL_PARTY_CONTACT_ID ,
481 EXTERNAL_PARTY_CONTACT_ID ,
482 DELIVERABLE_NAME ,
483 DESCRIPTION ,
484 COMMENTS ,
485 DISPLAY_SEQUENCE ,
486 FIXED_DUE_DATE_YN ,
487 ACTUAL_DUE_DATE ,
488 PRINT_DUE_DATE_MSG_NAME ,
489 RECURRING_YN ,
490 NOTIFY_PRIOR_DUE_DATE_VALUE ,
491 NOTIFY_PRIOR_DUE_DATE_UOM ,
492 NOTIFY_PRIOR_DUE_DATE_YN ,
493 NOTIFY_COMPLETED_YN ,
494 NOTIFY_OVERDUE_YN ,
495 NOTIFY_ESCALATION_YN ,
496 NOTIFY_ESCALATION_VALUE ,
497 NOTIFY_ESCALATION_UOM ,
498 ESCALATION_ASSIGNEE ,
499 AMENDMENT_OPERATION ,
500 PRIOR_NOTIFICATION_ID ,
501 AMENDMENT_NOTES ,
502 COMPLETED_NOTIFICATION_ID ,
503 OVERDUE_NOTIFICATION_ID ,
504 ESCALATION_NOTIFICATION_ID ,
505 LANGUAGE ,
506 ORIGINAL_DELIVERABLE_ID ,
507 REQUESTER_ID ,
508 EXTERNAL_PARTY_ID ,
509 EXTERNAL_PARTY_ROLE ,
510 RECURRING_DEL_PARENT_ID ,
511 BUSINESS_DOCUMENT_VERSION ,
512 RELATIVE_ST_DATE_DURATION ,
513 RELATIVE_ST_DATE_UOM ,
514 RELATIVE_ST_DATE_EVENT_ID ,
515 RELATIVE_END_DATE_DURATION ,
516 RELATIVE_END_DATE_UOM ,
517 RELATIVE_END_DATE_EVENT_ID ,
518 REPEATING_DAY_OF_MONTH ,
519 REPEATING_DAY_OF_WEEK ,
520 REPEATING_FREQUENCY_UOM ,
521 REPEATING_DURATION ,
522 FIXED_START_DATE ,
523 FIXED_END_DATE ,
524 MANAGE_YN ,
525 INTERNAL_PARTY_ID ,
526 DELIVERABLE_STATUS ,
527 STATUS_CHANGE_NOTES ,
528 CREATED_BY ,
529 CREATION_DATE ,
530 LAST_UPDATED_BY ,
531 LAST_UPDATE_DATE ,
532 LAST_UPDATE_LOGIN ,
533 OBJECT_VERSION_NUMBER ,
534 ATTRIBUTE_CATEGORY ,
535 ATTRIBUTE1 ,
536 ATTRIBUTE2 ,
537 ATTRIBUTE3 ,
538 ATTRIBUTE4 ,
539 ATTRIBUTE5 ,
540 ATTRIBUTE6 ,
541 ATTRIBUTE7 ,
542 ATTRIBUTE8 ,
543 ATTRIBUTE9 ,
544 ATTRIBUTE10 ,
545 ATTRIBUTE11 ,
546 ATTRIBUTE12 ,
547 ATTRIBUTE13 ,
548 ATTRIBUTE14 ,
549 ATTRIBUTE15 ,
550 DISABLE_NOTIFICATIONS_YN ,
551 LAST_AMENDMENT_DATE ,
552 BUSINESS_DOCUMENT_LINE_ID ,
553 EXTERNAL_PARTY_SITE_ID ,
554 START_EVENT_DATE ,
555 END_EVENT_DATE ,
556 SUMMARY_AMEND_OPERATION_CODE,
557 PAY_HOLD_PRIOR_DUE_DATE_VALUE,
558 PAY_HOLD_PRIOR_DUE_DATE_UOM,
559 PAY_HOLD_PRIOR_DUE_DATE_YN,
560 PAY_HOLD_OVERDUE_YN,
561 RAISE_COMPLETION_EVENT_YN,
562 orig_system_reference_code,
563 orig_system_reference_id1,
564 orig_system_reference_id2,
565 del_category_code,
566 exhibit_code,
567 data_item_number,
568 price_group,
569 estimated_price,
570 uda_template_id,
571 schedule_type
572 )
573 VALUES (
574 delInstanceRecTab(i).DELIVERABLE_ID,
575 delInstanceRecTab(i).BUSINESS_DOCUMENT_TYPE ,
576 delInstanceRecTab(i).BUSINESS_DOCUMENT_ID ,
577 delInstanceRecTab(i).BUSINESS_DOCUMENT_NUMBER ,
578 delInstanceRecTab(i).DELIVERABLE_TYPE ,
579 delInstanceRecTab(i).RESPONSIBLE_PARTY ,
580 delInstanceRecTab(i).INTERNAL_PARTY_CONTACT_ID ,
581 delInstanceRecTab(i).EXTERNAL_PARTY_CONTACT_ID ,
582 delInstanceRecTab(i).DELIVERABLE_NAME ,
583 delInstanceRecTab(i).DESCRIPTION ,
584 delInstanceRecTab(i).COMMENTS ,
585 delInstanceRecTab(i).DISPLAY_SEQUENCE ,
586 delInstanceRecTab(i).FIXED_DUE_DATE_YN ,
587 delInstanceRecTab(i).ACTUAL_DUE_DATE ,
588 delInstanceRecTab(i).PRINT_DUE_DATE_MSG_NAME ,
589 delInstanceRecTab(i).RECURRING_YN ,
590 delInstanceRecTab(i).NOTIFY_PRIOR_DUE_DATE_VALUE ,
591 delInstanceRecTab(i).NOTIFY_PRIOR_DUE_DATE_UOM ,
592 delInstanceRecTab(i).NOTIFY_PRIOR_DUE_DATE_YN ,
593 delInstanceRecTab(i).NOTIFY_COMPLETED_YN ,
594 delInstanceRecTab(i).NOTIFY_OVERDUE_YN ,
595 delInstanceRecTab(i).NOTIFY_ESCALATION_YN ,
596 delInstanceRecTab(i).NOTIFY_ESCALATION_VALUE ,
597 delInstanceRecTab(i).NOTIFY_ESCALATION_UOM ,
598 delInstanceRecTab(i).ESCALATION_ASSIGNEE ,
599 delInstanceRecTab(i).AMENDMENT_OPERATION ,
600 delInstanceRecTab(i).PRIOR_NOTIFICATION_ID ,
601 delInstanceRecTab(i).AMENDMENT_NOTES ,
602 delInstanceRecTab(i).COMPLETED_NOTIFICATION_ID ,
603 delInstanceRecTab(i).OVERDUE_NOTIFICATION_ID ,
604 delInstanceRecTab(i).ESCALATION_NOTIFICATION_ID ,
605 delInstanceRecTab(i).LANGUAGE ,
606 delInstanceRecTab(i).ORIGINAL_DELIVERABLE_ID ,
607 delInstanceRecTab(i).REQUESTER_ID ,
608 delInstanceRecTab(i).EXTERNAL_PARTY_ID ,
609 delInstanceRecTab(i).EXTERNAL_PARTY_ROLE ,
610 delInstanceRecTab(i).RECURRING_DEL_PARENT_ID ,
611 delInstanceRecTab(i).BUSINESS_DOCUMENT_VERSION ,
612 delInstanceRecTab(i).RELATIVE_ST_DATE_DURATION ,
613 delInstanceRecTab(i).RELATIVE_ST_DATE_UOM ,
614 delInstanceRecTab(i).RELATIVE_ST_DATE_EVENT_ID ,
615 delInstanceRecTab(i).RELATIVE_END_DATE_DURATION ,
616 delInstanceRecTab(i).RELATIVE_END_DATE_UOM ,
617 delInstanceRecTab(i).RELATIVE_END_DATE_EVENT_ID ,
618 delInstanceRecTab(i).REPEATING_DAY_OF_MONTH ,
619 delInstanceRecTab(i).REPEATING_DAY_OF_WEEK ,
620 delInstanceRecTab(i).REPEATING_FREQUENCY_UOM ,
621 delInstanceRecTab(i).REPEATING_DURATION ,
622 delInstanceRecTab(i).FIXED_START_DATE ,
623 delInstanceRecTab(i).FIXED_END_DATE ,
624 delInstanceRecTab(i).MANAGE_YN ,
625 delInstanceRecTab(i).INTERNAL_PARTY_ID ,
626 delInstanceRecTab(i).DELIVERABLE_STATUS ,
627 delInstanceRecTab(i).STATUS_CHANGE_NOTES ,
628 delInstanceRecTab(i).CREATED_BY ,
629 delInstanceRecTab(i).CREATION_DATE ,
630 delInstanceRecTab(i).LAST_UPDATED_BY ,
631 delInstanceRecTab(i).LAST_UPDATE_DATE ,
632 delInstanceRecTab(i).LAST_UPDATE_LOGIN ,
633 delInstanceRecTab(i).OBJECT_VERSION_NUMBER ,
634 delInstanceRecTab(i).ATTRIBUTE_CATEGORY ,
635 delInstanceRecTab(i).ATTRIBUTE1 ,
636 delInstanceRecTab(i).ATTRIBUTE2 ,
637 delInstanceRecTab(i).ATTRIBUTE3 ,
638 delInstanceRecTab(i).ATTRIBUTE4 ,
639 delInstanceRecTab(i).ATTRIBUTE5 ,
640 delInstanceRecTab(i).ATTRIBUTE6 ,
641 delInstanceRecTab(i).ATTRIBUTE7 ,
642 delInstanceRecTab(i).ATTRIBUTE8 ,
643 delInstanceRecTab(i).ATTRIBUTE9 ,
644 delInstanceRecTab(i).ATTRIBUTE10 ,
645 delInstanceRecTab(i).ATTRIBUTE11 ,
646 delInstanceRecTab(i).ATTRIBUTE12 ,
647 delInstanceRecTab(i).ATTRIBUTE13 ,
648 delInstanceRecTab(i).ATTRIBUTE14 ,
649 delInstanceRecTab(i).ATTRIBUTE15 ,
650 delInstanceRecTab(i).DISABLE_NOTIFICATIONS_YN ,
651 delInstanceRecTab(i).LAST_AMENDMENT_DATE ,
652 delInstanceRecTab(i).BUSINESS_DOCUMENT_LINE_ID ,
653 delInstanceRecTab(i).EXTERNAL_PARTY_SITE_ID ,
654 delInstanceRecTab(i).START_EVENT_DATE ,
655 delInstanceRecTab(i).END_EVENT_DATE ,
656 delInstanceRecTab(i).SUMMARY_AMEND_OPERATION_CODE,
657 delInstanceRecTab(i).PAY_HOLD_PRIOR_DUE_DATE_VALUE,
658 delInstanceRecTab(i).PAY_HOLD_PRIOR_DUE_DATE_UOM,
659 delInstanceRecTab(i).PAY_HOLD_PRIOR_DUE_DATE_YN,
660 delInstanceRecTab(i).PAY_HOLD_OVERDUE_YN,
661 delInstanceRecTab(i).RAISE_COMPLETION_EVENT_YN,
662 delInstanceRecTab(i).orig_system_reference_code,
663 delInstanceRecTab(i).orig_system_reference_id1,
664 delInstanceRecTab(i).orig_system_reference_id2,
665
666 delInstanceRecTab(i).del_category_code,
667 delInstanceRecTab(i).exhibit_code ,
668 delInstanceRecTab(i).data_item_number ,
669 delInstanceRecTab(i).price_group ,
670 delInstanceRecTab(i).estimated_price,
671 delInstanceRecTab(i).uda_template_id,
672 delInstanceRecTab(i).schedule_type
673 );
674 okc_deliverable_process_pvt.copy_deliverable_udas(
675 delInstanceRecTab(i).orig_system_reference_id1,
676 delInstanceRecTab(i).DELIVERABLE_ID,
677 x_return_status,
678 x_msg_count,
679 x_msg_data,
680 x_errorcode
681 );
682 if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
684 END IF;
685 END LOOP;
686
687 ------------------------------------------------------------------------
688
689 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
690 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'604a: Done Insterting DEL Records');
691 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'604b: Now inserting Status history records for INACTIVE');
692 END IF;
693
694 IF delStsTab.count > 0 THEN
695 --- set status history record to INACTIVE for each deliverable instance
696 OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history(
697 p_api_version => l_api_version,
698 p_init_msg_list => G_FALSE,
699 p_del_st_hist_tab => delStsTab,
700 x_msg_data => l_msg_data,
701 x_msg_count => l_msg_count,
702 x_return_status => l_return_status);
703
704 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
705 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'701a: Finished OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history'||l_return_status);
706 END IF;
707
708 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
710 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
711 RAISE FND_API.G_EXC_ERROR ;
712 END IF;
713 END IF;
714
715 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
716 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'604b: Now inserting Status history records for OPEN');
717 END IF;
718
719 -- insert status histiry records for OPEN status
720 IF delStsTab1.count > 0 THEN
721 --- set status history record to OPEN for each deliverable instance
722 OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history(
723 p_api_version => l_api_version,
724 p_init_msg_list => G_FALSE,
725 p_del_st_hist_tab => delStsTab1,
726 x_msg_data => l_msg_data,
727 x_msg_count => l_msg_count,
728 x_return_status => l_return_status);
729
730 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'701a: Finished OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history'||l_return_status);
732 END IF;
733
734 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
736 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
737 RAISE FND_API.G_EXC_ERROR ;
738 END IF;
739 END IF;
740
741 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'605: Done Insterting Records');
743 END IF;
744
745 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
746 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'606: Do Status Change, if not NULL '||p_change_status_to);
747 END IF;
748
749 END IF;
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
754 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
755 END IF;
756 Okc_Api.Set_Message(G_APP_NAME,
757 'OKC_DEL_ERR_GEN_INSTS');
758 RAISE FND_API.G_EXC_ERROR;
759 END; -- generate_del_instances
760
761 /**
762 * Helper method to return proper number value for the day of week.
763 * the stored code is as varchar value
764 */
765 FUNCTION getDayOfWeek(p_code IN VARCHAR2)
766 return NUMBER
767 IS
768 BEGIN
769 if p_code is not null then
770 return(to_number(p_code));
771 end if;
772 return NULL;
773 END;
774
775 /**
776 * Helper method to return Event Code and Before After value for given
777 * event id, stored in OKC_DELIVERABLES
778 */
779 PROCEDURE getDelEventDetails(
780 p_event_id IN NUMBER,
781 p_end_event_yn IN varchar2,
782 x_event_name OUT NOCOPY VARCHAR2,
783 x_before_after OUT NOCOPY VARCHAR2)
784 IS
785 l_api_name CONSTANT VARCHAR2(30) := 'getDelEventDetails';
786
787 BEGIN
788 -- IF p_end_event_yn = 'Y' THEN
789
790 SELECT business_event_code, before_after into x_event_name, x_before_after
791 FROM OKC_BUS_DOC_EVENTS_B
792 WHERE bus_doc_event_id = p_event_id;
793
794 /* AND (start_end_qualifier = 'BOTH' or start_end_qualifier = 'END');
795 ELSE
796 SELECT business_event_code, before_after into x_event_name, x_before_after
797 FROM OKC_BUS_DOC_EVENTS_B
798 WHERE bus_doc_event_id = p_event_id
799 AND (start_end_qualifier = 'BOTH' or start_end_qualifier = 'START');
800 END IF; */
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
806 END IF;
807 Okc_Api.Set_Message(G_APP_NAME,
808 'OKC_DEL_ERR_EVT_DTLS');
809 RAISE FND_API.G_EXC_ERROR;
810
811 END;
812
813 ---------------------------------------------------------------------------
814 -- END: Helper Procedures and Functions
815 ---------------------------------------------------------------------------
816
817 ---------------------------------------------------------------------------
818 -- START: Public Procedures and Functions
819 ---------------------------------------------------------------------------
820
821 /**
822 * Called by business document teams, to resolve and activate deliverables
823 */
824 PROCEDURE activateDeliverables (
825 p_api_version IN NUMBER,
826 p_init_msg_list IN VARCHAR2,
827 p_commit IN Varchar2,
828 p_bus_doc_id IN NUMBER,
829 p_bus_doc_type IN VARCHAR2,
830 p_bus_doc_version IN NUMBER,
831 p_event_code IN VARCHAR2,
832 p_event_date IN DATE,
833 p_sync_flag IN VARCHAR2,
834 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
835 x_msg_data OUT NOCOPY VARCHAR2,
836 x_msg_count OUT NOCOPY NUMBER,
837 x_return_status OUT NOCOPY VARCHAR2)
838 IS
839 l_api_version CONSTANT NUMBER := 1;
840 l_api_name CONSTANT VARCHAR2(30) := 'activateDeliverables';
841 BEGIN
842
843 -- start procedure
844 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
845 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered '||G_PKG_NAME ||'.'||l_api_name);
846 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'200: Bus dod id'||p_bus_doc_id);
847 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: Bus dod type'||p_bus_doc_type);
848 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'400: Bus dod version'||p_bus_doc_version);
849 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'500: Bus dod event code'||p_event_code);
850 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Bus dod event date'||p_event_date);
851 END IF;
852
853 IF p_bus_doc_id = NULL THEN
854 Okc_Api.Set_Message(G_APP_NAME
855 ,'OKC_DEL_NO_PARAMS');
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858 IF p_bus_doc_type = NULL THEN
859 Okc_Api.Set_Message(G_APP_NAME
860 ,'OKC_DEL_NO_PARAMS');
861 RAISE FND_API.G_EXC_ERROR;
862 END IF;
863 IF p_bus_doc_version = NULL THEN
864 Okc_Api.Set_Message(G_APP_NAME
865 ,'OKC_DEL_NO_PARAMS');
866 RAISE FND_API.G_EXC_ERROR;
867 END IF;
868 IF p_event_code = NULL THEN
869 Okc_Api.Set_Message(G_APP_NAME
870 ,'OKC_DEL_NO_PARAMS');
871 RAISE FND_API.G_EXC_ERROR;
872 END IF;
873 IF p_event_date = NULL THEN
874 Okc_Api.Set_Message(G_APP_NAME
875 ,'OKC_DEL_NO_PARAMS');
876 RAISE FND_API.G_EXC_ERROR;
877 END IF;
878
879 -- Standard Start of API savepoint
880 SAVEPOINT g_activate_del_GRP;
881
882 -- Standard call to check for call compatibility.
883 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 END IF;
886
887 -- Initialize message list if p_init_msg_list is set to TRUE.
888 IF FND_API.to_Boolean( p_init_msg_list ) THEN
889 FND_MSG_PUB.initialize;
890 END IF;
891
892 -- Initialize API return status to success
893 x_return_status := FND_API.G_RET_STS_SUCCESS;
894
895 --- check if sync flag is TRUE
896 IF FND_API.To_Boolean( p_sync_flag ) THEN
897
898 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
899 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'700: Calling '||'syncDeliverables');
900 END IF;
901 --- call deliverables process PVT to sync the deliverables for given version
902 OKC_DELIVERABLE_PROCESS_PVT.sync_deliverables(
903 p_api_version => l_api_version,
904 p_init_msg_list => G_FALSE,
905 p_current_docid => p_bus_doc_id,
906 p_current_doctype => p_bus_doc_type,
907 p_current_doc_version => p_bus_doc_version,
908 x_msg_data => x_msg_data,
909 x_msg_count => x_msg_count,
910 x_return_status => x_return_status);
911
912 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
913 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'701: Finished resolveDeliverables'||x_return_status);
914 END IF;
915
916 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
918 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
919 RAISE FND_API.G_EXC_ERROR ;
920 END IF;
921
922 END IF;
923
924 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
925 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'702: Calling '||'resolveDeliverables');
926 END IF;
927
928 --- resolve deliverables
929 resolveDeliverables(
930 p_api_version => l_api_version,
931 p_init_msg_list => G_FALSE,
932 p_commit => G_FALSE,
933 p_bus_doc_id => p_bus_doc_id,
934 p_bus_doc_type => p_bus_doc_type,
935 p_bus_doc_version => p_bus_doc_version,
936 p_event_code => p_event_code,
937 p_event_date => p_event_date,
938 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
939 x_msg_data => x_msg_data,
940 x_msg_count => x_msg_count,
941 x_return_status => x_return_status,
942 p_sync_flag => p_sync_flag,
943 p_sync_recurr_instances_flag => FND_API.G_TRUE);
944
945 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
946 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'800: Finished resolveDeliverables'||x_return_status);
947 END IF;
948
949 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
951 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
952 RAISE FND_API.G_EXC_ERROR ;
953 END IF;
954
955 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
956 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'900: Calling '||'OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status');
957 END IF;
958
959 --- call change_deliverable_status, to change deliverable status from
960 --- INACTIVE to 'OPEN'
961 OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status(
962 p_api_version => l_api_version,
963 p_init_msg_list => G_FALSE,
964 p_doc_id => p_bus_doc_id,
965 p_doc_version => p_bus_doc_version,
966 p_doc_type => p_bus_doc_type,
967 p_cancel_yn => 'N',
968 p_cancel_event_code => NULL,
969 p_current_status => 'INACTIVE',
970 p_new_status => 'OPEN',
971 p_manage_yn => 'Y',
972 x_msg_data => x_msg_data,
973 x_msg_count => x_msg_count,
974 x_return_status => x_return_status);
975
976 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
977 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Finished OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status'||x_return_status);
978 END IF;
979
980 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
982 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
983 RAISE FND_API.G_EXC_ERROR ;
984 END IF;
985
986 IF FND_API.To_Boolean( p_commit ) THEN
987 COMMIT WORK;
988 END IF;
989
990 -- Standard call to get message count and if count is 1, get message info.
991 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
992
993 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
994 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1100: Leaving activateDeliverables');
995 END IF;
996
997 EXCEPTION
998 WHEN FND_API.G_EXC_ERROR THEN
999 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1000 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1200: Leaving activateDeliverables Unexpected ERROR');
1001 END IF;
1002 ROLLBACK TO g_activate_del_GRP;
1003 x_return_status := G_RET_STS_ERROR ;
1004 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1005
1006 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1007 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1008 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1300: Leaving activateDeliverables Unexpected ERROR');
1009 END IF;
1010 ROLLBACK TO g_activate_del_GRP;
1011 x_return_status := G_RET_STS_UNEXP_ERROR ;
1012 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1013
1014 WHEN OTHERS THEN
1015 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1016 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1400: Leaving activateDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
1017 END IF;
1018 ROLLBACK TO g_activate_del_GRP;
1019 x_return_status := G_RET_STS_UNEXP_ERROR ;
1020 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1021 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1022 END IF;
1023 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1024
1025 END; -- activateDeliverables
1026
1027
1028 FUNCTION resolveRelativeDueEvents(
1029 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
1030 p_event_code IN VARCHAR2,
1031 p_event_date IN DATE,
1032 p_event_id IN NUMBER,
1033 p_event_UOM IN VARCHAR2,
1034 p_event_duration IN NUMBER,
1035 p_end_event_yn IN VARCHAR2)
1036 return DATE
1037 IS
1038 l_api_name CONSTANT VARCHAR2(30) := 'resolveRelativeDueEvents';
1039 l_del_event_name OKC_BUS_DOC_EVENTS_B.business_event_code%TYPE;
1040 l_del_before_after OKC_BUS_DOC_EVENTS_B.before_after%TYPE;
1041 l_actual_date DATE;
1042
1043 BEGIN
1044
1045 -- initialize
1046 l_actual_date := null;
1047
1048 -- start procedure
1049 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1050 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1051 END IF;
1052
1053 IF p_event_id is NULL OR p_event_UOM is NULL OR p_event_duration is NULL THEN
1054
1055 Okc_Api.Set_Message(G_APP_NAME,
1056 'OKC_DEL_NOT_RSLV_EVTS');
1057 RAISE FND_API.G_EXC_ERROR;
1058
1059 END IF;
1060
1061 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: Calling getDelEventDetails');
1063 END IF;
1064
1065 --- get current deliverable's end event details
1066 getDelEventDetails(
1067 p_event_id => p_event_id,
1068 p_end_event_yn => p_end_event_yn,
1069 x_event_name => l_del_event_name,
1070 x_before_after => l_del_before_after);
1071
1072 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1073 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: Finished getDelEventDetails - Event Name'||l_del_event_name);
1074 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'102: Finished getDelEventDetails - Before After'||l_del_before_after);
1075 END IF;
1076
1077 IF (p_event_code is not NULL AND p_event_code = l_del_event_name) THEN
1078 --- Calculate actual date
1079 l_actual_date :=
1080 OKC_DELIVERABLE_PROCESS_PVT.get_actual_date(
1081 p_start_date => p_event_date,
1082 p_timeunit => p_event_UOM,
1083 p_duration => p_event_duration,
1084 p_before_after => l_del_before_after);
1085
1086 IF l_actual_date is NULL THEN
1087 Okc_Api.Set_Message(G_APP_NAME,
1088 'OKC_DEL_DT_NOT_RSLVD');
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091
1092 END IF;
1093
1094 --- if relative, check for event name with the given event names
1095 --- in table of records.
1096 IF p_bus_doc_date_events_tbl.count > 0 THEN
1097 FOR k IN
1098 p_bus_doc_date_events_tbl.FIRST..p_bus_doc_date_events_tbl.LAST LOOP
1099 IF p_bus_doc_date_events_tbl(k).event_code = l_del_event_name THEN
1100
1101 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1102 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'103: Event Matched '||l_del_event_name);
1103 END IF;
1104
1105 --- Calculate actual date
1106 l_actual_date :=
1107 OKC_DELIVERABLE_PROCESS_PVT.get_actual_date(
1108 p_start_date => p_bus_doc_date_events_tbl(k).event_date,
1109 p_timeunit => p_event_UOM,
1110 p_duration => p_event_duration,
1111 p_before_after => l_del_before_after);
1112 /* IF l_actual_date is NULL THEN
1113 Okc_Api.Set_Message(G_APP_NAME,
1114 'OKC_DEL_DT_NOT_RSLVD');
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF; */
1117
1118 END IF;
1119 END LOOP;
1120 END IF;
1121
1122 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1123 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: Returning Resolved Date as '||l_actual_date);
1124 END IF;
1125 return l_actual_date;
1126 END;
1127
1128 /**
1129 * Resolve deliverable due date, recurring instances to OKC_DELIVERABLES
1130 */
1131 PROCEDURE resolveDeliverables (
1132 p_api_version IN NUMBER,
1133 p_init_msg_list IN VARCHAR2,
1134 p_commit IN Varchar2,
1135 p_bus_doc_id IN NUMBER,
1136 p_bus_doc_type IN VARCHAR2,
1137 p_bus_doc_version IN NUMBER,
1138 p_event_code IN VARCHAR2,
1139 p_event_date IN DATE,
1140 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
1141 x_msg_data OUT NOCOPY VARCHAR2,
1142 x_msg_count OUT NOCOPY NUMBER,
1143 x_return_status OUT NOCOPY VARCHAR2,
1144 p_sync_flag IN VARCHAR2,
1145 p_sync_recurr_instances_flag IN VARCHAR2,
1146 p_cancel_flag IN VARCHAR2)
1147 IS
1148 l_api_version CONSTANT NUMBER := 1;
1149 l_api_name CONSTANT VARCHAR2(30) := 'resolveDeliverables';
1150 start_date_for_del date;
1151 -- update cursor for bug#4069955
1152 CURSOR del_cur IS
1153 SELECT *
1154 FROM okc_deliverables del
1155 WHERE del.business_document_id = p_bus_doc_id
1156 AND del.business_document_version = p_bus_doc_version
1157 AND del.business_document_type = p_bus_doc_type
1158 AND del.deliverable_status = 'INACTIVE'
1159 AND del.actual_due_date is NULL
1160 AND Nvl(del.schedule_type,'SYSTEM')='SYSTEM'
1161 AND del.recurring_del_parent_id is NULL
1162 AND (del.amendment_operation is NULL OR del.amendment_operation <> 'DELETED')
1163 AND (del.summary_amend_operation_code is NULL OR del.summary_amend_operation_code <> 'DELETED')
1164 AND del.deliverable_type in (select delTypes.deliverable_type_code from
1165 okc_bus_doc_types_b busDocTypes,
1166 okc_del_bus_doc_combxns delTypes
1167 WHERE busDocTypes.document_type = del.business_document_type
1168 AND delTypes.document_type_class = busDocTypes.document_type_class
1169 AND del.deliverable_type = delTypes.deliverable_type_code)
1170 AND (G_FALSE = p_cancel_flag OR ( del.RELATIVE_ST_DATE_EVENT_ID in
1171 (select docEvents.BUS_DOC_EVENT_ID
1172 from okc_bus_doc_events_b docEvents
1173 where docEvents.BUSINESS_EVENT_CODE = p_event_code
1174 ))
1175 );
1176 del_rec del_cur%ROWTYPE;
1177
1178 CURSOR get_del_ids_cur (p_del_id IN NUMBER, p_actual_date IN DATE) IS
1179 SELECT deliverable_id FROM OKC_DELIVERABLES
1180 WHERE business_document_id = p_bus_doc_id
1181 AND business_document_type = p_bus_doc_type
1182 AND business_document_version = p_bus_doc_version
1183 AND recurring_del_parent_id = p_del_id
1184 AND TRUNC(actual_due_date) > TRUNC(p_actual_date);
1185
1186 -- this cursor has been introduced to fix bug 3574466. The cursor
1187 -- collectes deliverable_id's of all the instances for given recurring
1188 -- deliverable
1189 CURSOR get_del_ids_cur2 (p_del_id IN NUMBER) IS
1190 SELECT deliverable_id FROM OKC_DELIVERABLES
1191 WHERE business_document_id = p_bus_doc_id
1192 AND business_document_type = p_bus_doc_type
1193 AND business_document_version = p_bus_doc_version
1194 AND recurring_del_parent_id = p_del_id;
1195
1196 -- for storage of bulk Fetch
1197 delRecTab OKC_DELIVERABLE_PROCESS_PVT.delRecTabType;
1198
1199 -- for storage recurrign instances
1200 delInstanceRecTab OKC_DELIVERABLE_PROCESS_PVT.delRecTabType;
1201
1202 TYPE DelIdList IS TABLE OF NUMBER
1203 INDEX BY BINARY_INTEGER;
1204 deliverableIds DelIdList;
1205 TYPE DelDueDatetList IS TABLE OF DATE
1206 INDEX BY BINARY_INTEGER;
1207 deliverableDueDates DelDueDatetList;
1208 TYPE DelStartEventDateList IS TABLE OF DATE
1209 INDEX BY BINARY_INTEGER;
1210 deliverableStartEventDates DelStartEventDateList;
1211 TYPE DelEndEventDateList IS TABLE OF DATE
1212 INDEX BY BINARY_INTEGER;
1213 deliverableEndEventDates DelEndEventDateList;
1214
1215 del_count PLS_INTEGER;
1216 j PLS_INTEGER;
1217 k PLS_INTEGER;
1218 l_actual_date DATE;
1219 l_recurr_start_date DATE;
1220 l_recurr_end_date DATE;
1221
1222 l_repeat_day_of_month number;
1223 l_repeat_day_of_week number;
1224
1225 l_has_instances_yn VARCHAR2(1);
1226
1227 l_new_status OKC_DELIVERABLES.deliverable_status%TYPE;
1228
1229 --- for deliverable ids
1230 delIds OKC_DELIVERABLE_PROCESS_PVT.delIdTabType;
1231
1232 generate_new_instances_yn VARCHAR2(1);
1233 l_sync_flag VARCHAR2(1);
1234 BEGIN
1235
1236 -- initialize
1237 del_count := 0;
1238 j := 0;
1239 k := 0;
1240 l_sync_flag := FND_API.G_FALSE;
1241
1242 -- start procedure
1243 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1244 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered '||G_PKG_NAME ||'.'||l_api_name);
1245 END IF;
1246
1247 -- Standard Start of API savepoint
1248 SAVEPOINT g_resolve_del_GRP;
1249
1250 -- Standard call to check for call compatibility.
1251 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1253 END IF;
1254
1255 -- Initialize message list if p_init_msg_list is set to TRUE.
1256 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1257 FND_MSG_PUB.initialize;
1258 END IF;
1259
1260 -- Initialize API return status to success
1261 x_return_status := FND_API.G_RET_STS_SUCCESS;
1262
1263 IF FND_API.To_Boolean (p_sync_recurr_instances_flag) THEN
1264 l_sync_flag := FND_API.G_TRUE;
1265 ELSE
1266 l_sync_flag := p_sync_flag;
1267 END IF;
1268
1269
1270 --- open deliverables cursor and populate records of table
1271 FOR del_rec IN del_cur LOOP
1272 k := k+1;
1273 delRecTab(k).deliverable_id := del_rec.deliverable_id;
1274 delRecTab(k).BUSINESS_DOCUMENT_TYPE:= del_rec.BUSINESS_DOCUMENT_TYPE;
1275 delRecTab(k).BUSINESS_DOCUMENT_ID:= del_rec.BUSINESS_DOCUMENT_ID;
1276 delRecTab(k).BUSINESS_DOCUMENT_NUMBER:= del_rec.BUSINESS_DOCUMENT_NUMBER;
1277 delRecTab(k).DELIVERABLE_TYPE:= del_rec.DELIVERABLE_TYPE;
1278 delRecTab(k).RESPONSIBLE_PARTY:= del_rec.RESPONSIBLE_PARTY;
1279 delRecTab(k).INTERNAL_PARTY_CONTACT_ID:= del_rec.INTERNAL_PARTY_CONTACT_ID;
1280 delRecTab(k).EXTERNAL_PARTY_CONTACT_ID:= del_rec.EXTERNAL_PARTY_CONTACT_ID;
1281 delRecTab(k).DELIVERABLE_NAME:= del_rec.DELIVERABLE_NAME;
1282 delRecTab(k).DESCRIPTION:= del_rec.DESCRIPTION;
1283 delRecTab(k).COMMENTS:= del_rec.COMMENTS;
1284 delRecTab(k).DISPLAY_SEQUENCE:= del_rec.DISPLAY_SEQUENCE;
1285 delRecTab(k).FIXED_DUE_DATE_YN:= del_rec.FIXED_DUE_DATE_YN;
1286 delRecTab(k).ACTUAL_DUE_DATE:= del_rec.ACTUAL_DUE_DATE;
1287 delRecTab(k).PRINT_DUE_DATE_MSG_NAME:= del_rec.PRINT_DUE_DATE_MSG_NAME;
1288 delRecTab(k).RECURRING_YN:= del_rec.RECURRING_YN;
1289 delRecTab(k).NOTIFY_PRIOR_DUE_DATE_VALUE:= del_rec.NOTIFY_PRIOR_DUE_DATE_VALUE;
1290 delRecTab(k).NOTIFY_PRIOR_DUE_DATE_UOM:= del_rec.NOTIFY_PRIOR_DUE_DATE_UOM;
1291 delRecTab(k).NOTIFY_PRIOR_DUE_DATE_YN:= del_rec.NOTIFY_PRIOR_DUE_DATE_YN;
1292 delRecTab(k).NOTIFY_COMPLETED_YN:= del_rec.NOTIFY_COMPLETED_YN;
1293 delRecTab(k).NOTIFY_OVERDUE_YN:= del_rec.NOTIFY_OVERDUE_YN;
1294 delRecTab(k).NOTIFY_ESCALATION_YN:= del_rec.NOTIFY_ESCALATION_YN;
1295 delRecTab(k).NOTIFY_ESCALATION_VALUE:= del_rec.NOTIFY_ESCALATION_VALUE;
1296 delRecTab(k).NOTIFY_ESCALATION_UOM:= del_rec.NOTIFY_ESCALATION_UOM;
1297 delRecTab(k).ESCALATION_ASSIGNEE:= del_rec.ESCALATION_ASSIGNEE;
1298 delRecTab(k).AMENDMENT_OPERATION:= del_rec.AMENDMENT_OPERATION;
1299 delRecTab(k).PRIOR_NOTIFICATION_ID:= del_rec.PRIOR_NOTIFICATION_ID;
1300 delRecTab(k).AMENDMENT_NOTES:= del_rec.AMENDMENT_NOTES;
1301 delRecTab(k).COMPLETED_NOTIFICATION_ID:= del_rec.COMPLETED_NOTIFICATION_ID;
1302 delRecTab(k).OVERDUE_NOTIFICATION_ID:= del_rec.OVERDUE_NOTIFICATION_ID;
1303 delRecTab(k).ESCALATION_NOTIFICATION_ID:= del_rec.ESCALATION_NOTIFICATION_ID;
1304 delRecTab(k).LANGUAGE:= del_rec.LANGUAGE;
1305 delRecTab(k).ORIGINAL_DELIVERABLE_ID:= del_rec.ORIGINAL_DELIVERABLE_ID;
1306 delRecTab(k).REQUESTER_ID:= del_rec.REQUESTER_ID;
1307 delRecTab(k).EXTERNAL_PARTY_ID:= del_rec.EXTERNAL_PARTY_ID;
1308 delRecTab(k).EXTERNAL_PARTY_ROLE:= del_rec.EXTERNAL_PARTY_ROLE;
1309 delRecTab(k).RECURRING_DEL_PARENT_ID:= del_rec.RECURRING_DEL_PARENT_ID;
1310 delRecTab(k).BUSINESS_DOCUMENT_VERSION:= del_rec.BUSINESS_DOCUMENT_VERSION;
1311 delRecTab(k).RELATIVE_ST_DATE_DURATION:= del_rec.RELATIVE_ST_DATE_DURATION;
1312 delRecTab(k).RELATIVE_ST_DATE_UOM:= del_rec.RELATIVE_ST_DATE_UOM;
1313 delRecTab(k).RELATIVE_ST_DATE_EVENT_ID:= del_rec.RELATIVE_ST_DATE_EVENT_ID;
1314 delRecTab(k).RELATIVE_END_DATE_DURATION:= del_rec.RELATIVE_END_DATE_DURATION;
1315 delRecTab(k).RELATIVE_END_DATE_UOM:= del_rec.RELATIVE_END_DATE_UOM;
1316 delRecTab(k).RELATIVE_END_DATE_EVENT_ID:= del_rec.RELATIVE_END_DATE_EVENT_ID;
1317 delRecTab(k).REPEATING_DAY_OF_MONTH:= del_rec.REPEATING_DAY_OF_MONTH;
1318 delRecTab(k).REPEATING_DAY_OF_WEEK:= del_rec.REPEATING_DAY_OF_WEEK;
1319 delRecTab(k).REPEATING_FREQUENCY_UOM:= del_rec.REPEATING_FREQUENCY_UOM;
1320 delRecTab(k).REPEATING_DURATION:= del_rec.REPEATING_DURATION;
1321 delRecTab(k).FIXED_START_DATE:= del_rec.FIXED_START_DATE;
1322 delRecTab(k).FIXED_END_DATE:= del_rec.FIXED_END_DATE;
1323 delRecTab(k).MANAGE_YN:= del_rec.MANAGE_YN;
1324 delRecTab(k).INTERNAL_PARTY_ID:= del_rec.INTERNAL_PARTY_ID;
1325 delRecTab(k).DELIVERABLE_STATUS:= del_rec.DELIVERABLE_STATUS;
1326 delRecTab(k).STATUS_CHANGE_NOTES:= del_rec.STATUS_CHANGE_NOTES;
1327 delRecTab(k).CREATED_BY:= del_rec.CREATED_BY;
1328 delRecTab(k).CREATION_DATE:= del_rec.CREATION_DATE;
1329 delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
1330 delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
1331 delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
1332 delRecTab(k).OBJECT_VERSION_NUMBER:= del_rec.OBJECT_VERSION_NUMBER;
1333 delRecTab(k).ATTRIBUTE_CATEGORY:= del_rec.ATTRIBUTE_CATEGORY;
1334 delRecTab(k).ATTRIBUTE1:= del_rec.ATTRIBUTE1;
1335 delRecTab(k).ATTRIBUTE2:= del_rec.ATTRIBUTE2;
1336 delRecTab(k).ATTRIBUTE3:= del_rec.ATTRIBUTE3;
1337 delRecTab(k).ATTRIBUTE4:= del_rec.ATTRIBUTE4;
1338 delRecTab(k).ATTRIBUTE5:= del_rec.ATTRIBUTE5;
1339 delRecTab(k).ATTRIBUTE6:= del_rec.ATTRIBUTE6;
1340 delRecTab(k).ATTRIBUTE7:= del_rec.ATTRIBUTE7;
1341 delRecTab(k).ATTRIBUTE8:= del_rec.ATTRIBUTE8;
1342 delRecTab(k).ATTRIBUTE9:= del_rec.ATTRIBUTE9;
1343 delRecTab(k).ATTRIBUTE10:= del_rec.ATTRIBUTE10;
1344 delRecTab(k).ATTRIBUTE11:= del_rec.ATTRIBUTE11;
1345 delRecTab(k).ATTRIBUTE12:= del_rec.ATTRIBUTE12;
1346 delRecTab(k).ATTRIBUTE13:= del_rec.ATTRIBUTE13;
1347 delRecTab(k).ATTRIBUTE14:= del_rec.ATTRIBUTE14;
1348 delRecTab(k).ATTRIBUTE15:= del_rec.ATTRIBUTE15;
1349 delRecTab(k).DISABLE_NOTIFICATIONS_YN:= del_rec.DISABLE_NOTIFICATIONS_YN;
1350 delRecTab(k).LAST_AMENDMENT_DATE:= del_rec.LAST_AMENDMENT_DATE;
1351 delRecTab(k).BUSINESS_DOCUMENT_LINE_ID:= del_rec.BUSINESS_DOCUMENT_LINE_ID;
1352 delRecTab(k).EXTERNAL_PARTY_SITE_ID:= del_rec.EXTERNAL_PARTY_SITE_ID;
1353 delRecTab(k).START_EVENT_DATE:= del_rec.START_EVENT_DATE;
1354 delRecTab(k).END_EVENT_DATE:= del_rec.END_EVENT_DATE;
1355 delRecTab(k).SUMMARY_AMEND_OPERATION_CODE:= del_rec.SUMMARY_AMEND_OPERATION_CODE;
1356 delRecTab(k).PAY_HOLD_PRIOR_DUE_DATE_VALUE:=del_rec.PAY_HOLD_PRIOR_DUE_DATE_VALUE;
1357 delRecTab(k).PAY_HOLD_PRIOR_DUE_DATE_UOM:=del_rec.PAY_HOLD_PRIOR_DUE_DATE_UOM;
1358 delRecTab(k).PAY_HOLD_PRIOR_DUE_DATE_YN:=del_rec.PAY_HOLD_PRIOR_DUE_DATE_YN;
1359 delRecTab(k).PAY_HOLD_OVERDUE_YN:=del_rec.PAY_HOLD_OVERDUE_YN;
1360 delRecTab(k).RAISE_COMPLETION_EVENT_YN:=del_rec.RAISE_COMPLETION_EVENT_YN;
1361
1362
1363 delRecTab(k).orig_system_reference_code := 'COPY';
1364 delRecTab(k).orig_system_reference_id1 := del_rec.deliverable_id;
1365 delRecTab(k). orig_system_reference_id2 := null;
1366
1367 -- CDRL
1368 delRecTab(k).del_category_code := del_rec.del_category_code;
1369 delRecTab(k).exhibit_code := del_rec.exhibit_code;
1370 delRecTab(k).data_item_number := del_rec.data_item_number;
1371 delRecTab(k).price_group := del_rec.price_group;
1372 delRecTab(k).estimated_price := del_rec.estimated_price;
1373 delRecTab(k).uda_template_id := del_rec.uda_template_id;
1374 delRecTab(k).schedule_type := del_rec.schedule_type;
1375
1376 END LOOP;
1377
1378 -- commented as this is not supported by 8i PL/SQL Bug#3307941
1379 /*OPEN del_cur;
1380 FETCH del_cur BULK COLLECT INTO delRecTab;*/
1381
1382 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1383 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: Got Deliverables -- Count '||delRecTab.count);
1384 END IF;
1385
1386 IF delRecTab.count > 0 THEN
1387 FOR i IN delRecTab.FIRST..delRecTab.LAST LOOP
1388
1389 --- if deliverable is not recurring and start due date is FIXED
1390 IF delRecTab(i).FIXED_DUE_DATE_YN = 'Y' THEN
1391
1392 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1393 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'102: Fixed Due Date Deliverable = '||delRecTab(i).deliverable_id);
1394 END IF;
1395
1396 --- increment the count
1397 del_count := del_count+1;
1398
1399 -- record the deliverable id to be updated at the end
1400 deliverableIds(del_count) := delRecTab(i).deliverable_id;
1401
1402 -- set actual due date
1403 deliverableDueDates(del_count) := delRecTab(i).fixed_start_date;
1404
1405 -- populate start event date as static date
1406 deliverableStartEventDates(del_count) := delRecTab(i).fixed_start_date;
1407 deliverableEndEventDates(del_count) := NULL;
1408
1409 -- check and create status history record for INACTIVE status
1410 checkAndCreateStatusHistory(p_deliverable_id => delRecTab(i).deliverable_id,
1411 p_deliverable_status => 'INACTIVE',
1412 x_msg_data => x_msg_data,
1413 x_msg_count => x_msg_count,
1414 x_return_status => x_return_status);
1415 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1417 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1418 RAISE FND_API.G_EXC_ERROR ;
1419 END IF;
1420
1421 END IF; -- fixed due date is 'Yes'
1422
1423 -- if deliverable is recurring
1424 IF delRecTab(i).recurring_yn = 'Y' THEN
1425
1426 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'103: Recurring Deliverable = '||delRecTab(i).deliverable_id);
1428 END IF;
1429
1430 --- check if deliverable has recurring instances already in place
1431 --- for given version of the document
1432 l_has_instances_yn := hasInstances(
1433 p_bus_doc_id => p_bus_doc_id,
1434 p_bus_doc_type => p_bus_doc_type,
1435 p_bus_doc_version => p_bus_doc_version,
1436 p_del_id => delRecTab(i).deliverable_id);
1437
1438 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1439 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: Recurring Deliverable, Has Instances = '||l_has_instances_yn);
1440 END IF;
1441
1442 -- unexpected error
1443 IF l_has_instances_yn is null THEN
1444 Okc_Api.Set_Message(G_APP_NAME,
1445 'OKC_DEL_ERR_GET_INSTS');
1446 RAISE FND_API.G_EXC_ERROR;
1447 END IF;
1448
1449 -- by default this is N
1450 generate_new_instances_yn := 'N';
1451
1452 --- if deliverable has recurring instances
1453 IF l_has_instances_yn = 'Y' THEN
1454
1455 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1456 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'105: Checking already exploded Recurring Deliverable = '||delRecTab(i).deliverable_id);
1457 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'105a: Check if deliverable is Amended = '||delRecTab(i).deliverable_id);
1458 END IF;
1459
1460 --- check if amendment operation is (R)evised
1461 /* IF ((delRecTab(i).amendment_operation is not null AND
1462 delRecTab(i).amendment_operation = 'UPDATED') OR
1463 (delRecTab(i).summary_amend_operation_code is not null AND
1464 delRecTab(i).summary_amend_operation_code = 'UPDATED')) THEN */
1465
1466 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1467 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'106: Recurring Definition = '||delRecTab(i).deliverable_id||' with Amendment Operation as '||delRecTab(i).amendment_operation);
1468 END IF;
1469
1470 -- fix Bug 3574466: If sync flag is true, generate only the
1471 -- delta instances.
1472 IF FND_API.To_Boolean( l_sync_flag ) THEN
1473
1474 --- ASSUMPTION: In this case only end date can change
1475 --- get old start date
1476 l_recurr_start_date := delRecTab(i).start_event_date;
1477
1478 --- check if end date is not FIXED, it is relative
1479 IF delRecTab(i).fixed_end_date is null THEN
1480
1481 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1482 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'107: Recurring Definition = '||delRecTab(i).deliverable_id||' End Date is not Fixed ');
1483 END IF;
1484
1485 --- initialize recurr end date
1486 l_recurr_end_date := null;
1487
1488 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1489 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'107: Recurring Definition = '||delRecTab(i).deliverable_id||' Get Event Details ');
1490 END IF;
1491
1492 -- to resolve relative end date check if due dates table is
1493 -- not empty
1494 /*--Commenting out as part of fix for bug 4030982--
1495 IF p_bus_doc_date_events_tbl.count = 0 THEN
1496 Okc_Api.Set_Message(G_APP_NAME,
1497 'OKC_DEL_ERR_DTS_EMPY');
1498 RAISE FND_API.G_EXC_ERROR;
1499 END IF;
1500 */
1501
1502 --- resolve relative end date
1503 l_recurr_end_date := resolveRelativeDueEvents(
1504 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
1505 p_event_code => p_event_code,
1506 p_event_date => p_event_date,
1507 p_event_id => delRecTab(i).relative_end_date_event_id,
1508 p_event_UOM => delRecTab(i).relative_end_date_uom,
1509 p_event_duration => delRecTab(i).relative_end_date_duration,
1510 p_end_event_yn => 'Y');
1511
1512 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1513 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Recurring end date '||l_recurr_end_date);
1514 END IF;
1515
1516 ELSE --- get the fixed end date, provided
1517
1518 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1519 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' End Date is Fixed '||l_recurr_end_date);
1520 END IF;
1521
1522 l_recurr_end_date := delRecTab(i).fixed_end_date;
1523
1524 END IF; --- End Date is Evaluated
1525
1526 -- By this time l_recurr_end_date should not be NULL
1527 IF l_recurr_end_date is NULL THEN
1528 Okc_Api.Set_Message(G_APP_NAME,
1529 'OKC_DEL_END_DT_NOT_FOUND');
1530 RAISE FND_API.G_EXC_ERROR;
1531 END IF;
1532
1533 --- check if new date is less then old date
1534 --- delete instances where actual date is equal to or
1535 --- greater then new date
1536
1537 IF TRUNC(l_recurr_end_date) < TRUNC(delRecTab(i).end_event_date) THEN
1538
1539 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1540 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' New end Date is LESS then OLD End Date '||l_recurr_end_date);
1541 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Hence Delete Remaining Instances '||l_recurr_end_date);
1542 END IF;
1543
1544 --- hard Delete old instances from current version
1545 OPEN get_del_ids_cur(delRecTab(i).deliverable_id, l_recurr_end_date);
1546 FETCH get_del_ids_cur BULK COLLECT INTO delIds;
1547 CLOSE get_del_ids_cur;
1548
1549 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1550 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances for count '||delIds.count);
1551 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances '||x_return_status);
1552 END IF;
1553
1554 -- if there are any deliverable instances to be deleted
1555 IF delIds.count > 0 THEN
1556 --- call delete_del_instances or OKC_DELIVERABLE_PROCESS_PVT
1557 OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
1558 p_api_version => l_api_version,
1559 p_init_msg_list => G_FALSE,
1560 p_doc_id => p_bus_doc_id,
1561 p_doc_type => p_bus_doc_type,
1562 p_doc_version => p_bus_doc_version,
1563 p_Conditional_Delete_Flag => 'Y',
1564 p_delid_tab => delIds,
1565 x_msg_data => x_msg_data,
1566 x_msg_count => x_msg_count,
1567 x_return_status => x_return_status);
1568
1569 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1570 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
1571 ,'108: Recurring Definition = '||delRecTab(i).deliverable_id||
1572 ' Finished delete_del_instances for count '||delIds.count);
1573 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
1574 ,'108: Recurring Definition = '||delRecTab(i).deliverable_id||
1575 ' Finished OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances '
1576 ||x_return_status);
1577 END IF;
1578
1579 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1581 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1582 RAISE FND_API.G_EXC_ERROR ;
1583 END IF;
1584
1585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1586 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
1587 ,'108: Recurring Definition = '||delRecTab(i).deliverable_id||
1588 ' Updating Definition ');
1589 END IF;
1590 END IF; -- delIds count > 0
1591 --- increment the count
1592 del_count := del_count+1;
1593
1594 -- record deliverable id to be updated at the end
1595 deliverableIds(del_count) := delRecTab(i).deliverable_id;
1596
1597 -- record actual date, start event date and end event date
1598 deliverableDueDates(del_count) := NULL;
1599 deliverableStartEventDates(del_count) := delRecTab(i).start_event_date;
1600 deliverableEndEventDates(del_count) := l_recurr_end_date;
1601
1602 END IF; -- New end date is LESS
1603
1604 --- check if new date is greater then old date
1605 --- generate new instances with start date as old end date
1606 --- and end date as new end date, us the same repeat frequency on
1607 --- the given deliverable and resolve it.
1608 IF TRUNC(l_recurr_end_date) > TRUNC(delRecTab(i).end_event_date) THEN
1609
1610 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1611 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' New end Date is GREATER then OLD End Date '||l_recurr_end_date);
1612 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Hence create new Instances '||l_recurr_end_date);
1613 END IF;
1614
1615 --- get the repeat frequency and create new instances
1616 l_repeat_day_of_month := getDayOfMonth(
1617 delRecTab(i).repeating_day_of_month);
1618 l_repeat_day_of_week := getDayOfWeek(
1619 delRecTab(i).repeating_day_of_week);
1620
1621 --- check the status of exiting instances
1622 l_new_status :=
1623 checkStatusOfExistingInstances(
1624 p_bus_doc_id => p_bus_doc_id,
1625 p_bus_doc_type => p_bus_doc_type,
1626 p_bus_doc_version => p_bus_doc_version,
1627 p_del_id => delRecTab(i).deliverable_id);
1628 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Got new Status '||l_new_status);
1630 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Calling generate_del_instances ');
1631 END IF;
1632
1633 -- generate recurring instances for
1634 -- given deliverable definition id
1635 ---
1636
1637
1638 ---Calculate the correct DATE here
1639
1640
1641 SELECT Add_Months(delRecTab(i).FIXED_START_DATE,Trunc( Months_Between(delRecTab(i).end_event_date,delRecTab(i).FIXED_START_DATE)/12)*12 ) INTO start_date_for_del FROM dual;
1642
1643
1644
1645 generate_del_instances(
1646 p_recurr_start_date => (start_date_for_del),
1647 p_recurr_end_date => l_recurr_end_date,
1648 p_repeat_duration => delRecTab(i).repeating_duration,
1649 p_repeat_day_of_month => l_repeat_day_of_month,
1650 p_repeat_day_of_week => l_repeat_day_of_week,
1651 delRecord => delRecTab(i),
1652 p_change_status_to => l_new_status);
1653
1654 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1655 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Finished generate_del_instances ');
1656 END IF;
1657 ----- Done Creating new deliverable instances ---
1658
1659 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1660 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Updating Definition ');
1661 END IF;
1662
1663 --- increment the count
1664 del_count := del_count+1;
1665
1666 -- record deliverable id to be updated at the end
1667 deliverableIds(del_count) := delRecTab(i).deliverable_id;
1668
1669 -- record actual date, start event date and end event date
1670 deliverableDueDates(del_count) := NULL;
1671 deliverableEndEventDates(del_count) := l_recurr_end_date;
1672 deliverableStartEventDates(del_count) := delRecTab(i).start_event_date;
1673
1674 END IF; --- New End date is GREATER
1675 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1676 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Finished generate_del_instances ');
1677 END IF;
1678
1679 ELSE -- if sync flag is NOT true
1680 -- delete existing instances and re-generate new
1681 -- instances
1682
1683 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1684 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1080: Recurring Definition = '||delRecTab(i).deliverable_id||' As Sync Flag is N, Deleting existing instances ');
1685 END IF;
1686
1687 --- hard Delete old instances from current version
1688 OPEN get_del_ids_cur2(delRecTab(i).deliverable_id);
1689 FETCH get_del_ids_cur2 BULK COLLECT INTO delIds;
1690 CLOSE get_del_ids_cur2;
1691
1692 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1693 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
1694 ,'1081: Recurring Definition = '||delRecTab(i).deliverable_id||
1695 ' Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances for count '
1696 ||delIds.count);
1697 END IF;
1698
1699 -- if there are any deliverable instances to be deleted
1700 IF delIds.count > 0 THEN
1701 --- call delete_del_instances or OKC_DELIVERABLE_PROCESS_PVT
1702 OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
1703 p_api_version => l_api_version,
1704 p_init_msg_list => G_FALSE,
1705 p_doc_id => p_bus_doc_id,
1706 p_doc_type => p_bus_doc_type,
1707 p_doc_version => p_bus_doc_version,
1708 p_Conditional_Delete_Flag => 'N',
1709 p_delid_tab => delIds,
1710 x_msg_data => x_msg_data,
1711 x_msg_count => x_msg_count,
1712 x_return_status => x_return_status);
1713
1714 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1716 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1717 RAISE FND_API.G_EXC_ERROR ;
1718 END IF;
1719
1720 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1721 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
1722 ,'108: Recurring Definition = '||delRecTab(i).deliverable_id||
1723 ' Finished OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances for count '||delIds.count);
1724 END IF;
1725 END IF;
1726 -- set the flag to generate new instances
1727 generate_new_instances_yn := 'Y';
1728 END IF;
1729 -- END IF; --- check if amendment operation is (R)evised
1730 END IF; -- hasInstances
1731
1732 --- recurring deliverable is newly created or instances are deleted
1733 IF l_has_instances_yn ='N' OR generate_new_instances_yn = 'Y' THEN
1734
1735 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1736 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' New Recurring Definition ');
1737 END IF;
1738
1739 --- get start date, if relative to an event, evaluate it
1740 --- check if start event id is populated, if yes evaluate the start
1741 --- date or actual date.
1742 IF delRecTab(i).RELATIVE_ST_DATE_EVENT_ID is not null THEN
1743
1744 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1745 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Start date is Relative ');
1746 END IF;
1747
1748 l_recurr_start_date := NULL;
1749
1750 --- resolve relative end date
1751 l_recurr_start_date := resolveRelativeDueEvents(
1752 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
1753 p_event_code => p_event_code,
1754 p_event_date => p_event_date,
1755 p_event_id => delRecTab(i).relative_st_date_event_id,
1756 p_event_UOM => delRecTab(i).relative_st_date_uom,
1757 p_event_duration => delRecTab(i).relative_st_date_duration,
1758 p_end_event_yn => 'N');
1759
1760 ELSE --- start date is Fixed
1761
1762 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1763 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Start date is fixed '||delRecTab(i).fixed_start_date);
1764 END IF;
1765
1766 -- start date is FIXED
1767 l_recurr_start_date := delRecTab(i).fixed_start_date;
1768
1769 -- By this time l_recurr_start_date should not be NULL
1770 IF l_recurr_start_date is NULL THEN
1771 Okc_Api.Set_Message(G_APP_NAME,
1772 'OKC_DEL_ST_DT_NOT_FOUND');
1773 RAISE FND_API.G_EXC_ERROR;
1774 END IF;
1775 END IF; --- Start date resolved
1776
1777 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1778 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Start date is resolved Now resolve End Date');
1779 END IF;
1780
1781 --- if recurring start date is resolved, only in that case
1782 --- go further, resolve end date and generate instances
1783 IF l_recurr_start_date is not NULL THEN
1784
1785 --- get the end date
1786 --- check if end date is fixed
1787 IF delRecTab(i).fixed_end_date is not null THEN
1788
1789 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1790 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' End Date is Fixed'||delRecTab(i).fixed_end_date);
1791 END IF;
1792
1793 -- recurring end date is FIXED
1794 l_recurr_end_date := delRecTab(i).fixed_end_date;
1795
1796 ELSE --- is not fixed, resolve if relative
1797
1798 --- initialize
1799 l_recurr_end_date := NULL;
1800
1801 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1802 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' End Date is Relative');
1803 END IF;
1804
1805 --- resolve relative end date
1806 l_recurr_end_date := resolveRelativeDueEvents(
1807 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
1808 p_event_code => p_event_code,
1809 p_event_date => p_event_date,
1810 p_event_id => delRecTab(i).relative_end_date_event_id,
1811 p_event_UOM => delRecTab(i).relative_end_date_uom,
1812 p_event_duration => delRecTab(i).relative_end_date_duration,
1813 p_end_event_yn => 'Y');
1814
1815 END IF; --- get the end date
1816
1817 IF l_recurr_end_date is NULL THEN
1818 Okc_Api.Set_Message(G_APP_NAME,
1819 'OKC_DEL_END_DT_NOT_FOUND');
1820 RAISE FND_API.G_EXC_ERROR;
1821 END IF;
1822
1823 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1824 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Get Recurring Frequency ');
1825 END IF;
1826
1827 l_repeat_day_of_month := getDayOfMonth(
1828 delRecTab(i).REPEATING_DAY_OF_MONTH);
1829 l_repeat_day_of_week := getDayOfWeek(
1830 delRecTab(i).REPEATING_DAY_OF_WEEK);
1831
1832 -- if both frequency values ar null
1833 IF (l_repeat_day_of_month is NULL AND l_repeat_day_of_week is NULL
1834 AND Nvl(delRecTab(i).repeating_frequency_uom,'ABC') <> 'DAY'
1835 ) THEN
1836 Okc_Api.Set_Message(G_APP_NAME,
1837 'OKC_DEL_RECUR_FRQ_NOT_FOUND');
1838 RAISE FND_API.G_EXC_ERROR;
1839 END IF;
1840
1841 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1842 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Generate Instances ');
1843 END IF;
1844
1845 -- generate recurring instances
1846 generate_del_instances(
1847 p_recurr_start_date => l_recurr_start_date,
1848 p_recurr_end_date => l_recurr_end_date,
1849 p_repeat_duration => delRecTab(i).repeating_duration,
1850 p_repeat_day_of_month => l_repeat_day_of_month,
1851 p_repeat_day_of_week => l_repeat_day_of_week,
1852 delRecord => delRecTab(i),
1853 p_change_status_to => NULL);
1854
1855 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1856 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Recurring Definition = '||delRecTab(i).deliverable_id||' Updating definition ');
1857 END IF;
1858
1859 --- increment the count
1860 del_count := del_count+1;
1861
1862 -- record deliverable id to be updated at the end
1863 deliverableIds(del_count) := delRecTab(i).deliverable_id;
1864
1865 -- record actual date, start event date and end event date
1866 deliverableDueDates(del_count) := NULL;
1867 deliverableStartEventDates(del_count) := l_recurr_start_date;
1868 deliverableEndEventDates(del_count) := l_recurr_end_date;
1869
1870 END IF; --- if recurring start date is resolved
1871
1872 END IF; --- recurring deliverable is newly created or instances are deleted
1873
1874 ELSE -- if not Recurring, deliverable is one time with relative start event
1875
1876 --- check if start event id is populated, if yes evaluate the start
1877 --- date or actual date.
1878 l_actual_date := NULL;
1879 IF delRecTab(i).RELATIVE_ST_DATE_EVENT_ID is not null THEN
1880
1881 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1882 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'110:Deliverable is One Time ');
1883 END IF;
1884
1885 -- check and create status history record for INACTIVE status
1886 checkAndCreateStatusHistory(p_deliverable_id => delRecTab(i).deliverable_id,
1887 p_deliverable_status => 'INACTIVE',
1888 x_msg_data => x_msg_data,
1889 x_msg_count => x_msg_count,
1890 x_return_status => x_return_status);
1891 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1893 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1894 RAISE FND_API.G_EXC_ERROR ;
1895 END IF;
1896
1897
1898 --- resolve relative end date
1899 l_actual_date := resolveRelativeDueEvents(
1900 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
1901 p_event_code => p_event_code,
1902 p_event_date => p_event_date,
1903 p_event_id => delRecTab(i).relative_st_date_event_id,
1904 p_event_UOM => delRecTab(i).relative_st_date_uom,
1905 p_event_duration => delRecTab(i).relative_st_date_duration,
1906 p_end_event_yn => 'N');
1907 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1908 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'113: Updating Definition');
1909 END IF;
1910
1911 --- if actual date is resolved
1912 IF l_actual_date is not NULL THEN
1913
1914 --- increment the count
1915 del_count := del_count+1;
1916
1917 -- record deliverable id to be updated at the end
1918 deliverableIds(del_count) := delRecTab(i).deliverable_id;
1919
1920 -- record actual date, start event date and end event date
1921 deliverableDueDates(del_count) :=l_actual_date;
1922 deliverableStartEventDates(del_count) := l_actual_date;
1923 deliverableEndEventDates(del_count) := NULL;
1924 END IF;
1925 END IF; --- deliverable with Start due date event
1926 END IF;
1927 END LOOP;
1928 END IF;
1929
1930 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1931 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'113: Buld update started');
1932 END IF;
1933
1934
1935 --- bulk update for deliverables actual due date
1936 IF deliverableIds.count > 0 THEN
1937 FORALL i IN deliverableIds.FIRST..deliverableIds.LAST
1938 UPDATE okc_deliverables
1939 SET
1940 actual_due_date = deliverableDueDates(i),
1941 start_event_date = deliverableStartEventDates(i),
1942 end_event_date = deliverableEndEventDates(i),
1943 last_updated_by= Fnd_Global.User_Id,
1944 last_update_date = sysdate,
1945 last_update_login = Fnd_Global.Login_Id
1946 WHERE deliverable_id = deliverableIds(i);
1947 END IF;
1948 IF del_cur %ISOPEN THEN
1949 CLOSE del_cur ;
1950 END IF;
1951
1952 IF FND_API.To_Boolean( p_commit ) THEN
1953 COMMIT WORK;
1954 END IF;
1955
1956 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1957 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving resolveDeliverables');
1958 END IF;
1959
1960 EXCEPTION
1961
1962 WHEN FND_API.G_EXC_ERROR THEN
1963 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1964 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving resolveDeliverables Unexpected ERROR');
1965 END IF;
1966 IF del_cur %ISOPEN THEN
1967 CLOSE del_cur ;
1968 END IF;
1969 IF get_del_ids_cur %ISOPEN THEN
1970 CLOSE get_del_ids_cur;
1971 END IF;
1972
1973 ROLLBACK TO g_resolve_del_GRP;
1974 x_return_status := G_RET_STS_ERROR ;
1975 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1976
1977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1978 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1979 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving resolveDeliverables Unexpected ERROR');
1980 END IF;
1981 IF del_cur %ISOPEN THEN
1982 CLOSE del_cur ;
1983 END IF;
1984 IF get_del_ids_cur %ISOPEN THEN
1985 CLOSE get_del_ids_cur;
1986 END IF;
1987
1988 ROLLBACK TO g_resolve_del_GRP;
1989 x_return_status := G_RET_STS_UNEXP_ERROR ;
1990 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1991
1992 WHEN OTHERS THEN
1993 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1994 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving resolveDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
1995 END IF;
1996 IF del_cur %ISOPEN THEN
1997 CLOSE del_cur ;
1998 END IF;
1999 IF get_del_ids_cur %ISOPEN THEN
2000 CLOSE get_del_ids_cur;
2001 END IF;
2002
2003 ROLLBACK TO g_resolve_del_GRP;
2004 x_return_status := G_RET_STS_UNEXP_ERROR ;
2005 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2006 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2007 END IF;
2008 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2009
2010 END; -- resolveDeliverables
2011
2012 /**
2013 * Update deliverables, re-resolve deliverables for the dates passed by
2014 * bus doc api, assumed to be changed.
2015 */
2016 PROCEDURE updateDeliverables (
2017 p_api_version IN NUMBER,
2018 p_init_msg_list IN VARCHAR2,
2019 p_commit IN Varchar2,
2020 p_bus_doc_id IN NUMBER,
2021 p_bus_doc_type IN VARCHAR2,
2022 p_bus_doc_version IN NUMBER,
2023 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
2024 x_msg_data OUT NOCOPY VARCHAR2,
2025 x_msg_count OUT NOCOPY NUMBER,
2026 x_return_status OUT NOCOPY VARCHAR2)
2027 IS
2028
2029 --- Define cursor to fetch already resolved deliverables
2030 -- update cursor for bug#4069955
2031 -- Updated the cusror bug 5018624
2032 CURSOR del_cur IS
2033 SELECT *
2034 FROM okc_deliverables del
2035 WHERE business_document_id = p_bus_doc_id
2036 AND business_document_type = p_bus_doc_type
2037 AND business_document_version = p_bus_doc_version
2038 AND (
2039 (fixed_due_date_yn = 'N'
2040 AND (amendment_operation is NULL OR amendment_operation <> 'DELETED')
2041 AND (summary_amend_operation_code is NULL OR summary_amend_operation_code <> 'DELETED')
2042 AND recurring_YN ='N'
2043 AND actual_due_date is not null
2044 AND recurring_del_parent_id is null)
2045 OR
2046 (recurring_YN = 'Y'
2047 AND recurring_del_parent_id is null
2048 AND del.deliverable_type in ( select d.deliverable_type_code
2049 from okc_bus_doc_types_b bd,
2050 okc_del_bus_doc_combxns d
2051 WHERE bd.document_type = del.business_document_type
2052 AND d.document_type_class = bd.document_type_class
2053 AND del.deliverable_type = d.deliverable_type_code ))
2054 );
2055 del_rec del_cur%ROWTYPE;
2056 k PLS_INTEGER;
2057 CURSOR get_del_ids_cur1 (p_del_id IN NUMBER) IS
2058 SELECT deliverable_id FROM OKC_DELIVERABLES
2059 WHERE business_document_id = p_bus_doc_id
2060 AND business_document_type = p_bus_doc_type
2061 AND business_document_version = p_bus_doc_version
2062 AND recurring_del_parent_id = p_del_id;
2063
2064 CURSOR get_del_ids_cur2 (p_del_id IN NUMBER, p_actual_date IN DATE) IS
2065 SELECT deliverable_id FROM OKC_DELIVERABLES
2066 WHERE business_document_id = p_bus_doc_id
2067 AND business_document_type = p_bus_doc_type
2068 AND business_document_version = p_bus_doc_version
2069 AND recurring_del_parent_id = p_del_id
2070 AND TRUNC(actual_due_date) > TRUNC(p_actual_date);
2071
2072 --- for deliverable ids
2073 delIds OKC_DELIVERABLE_PROCESS_PVT.delIdTabType;
2074
2075 TYPE DelIdList IS TABLE OF NUMBER
2076 INDEX BY BINARY_INTEGER;
2077 deliverableIds DelIdList;
2078 TYPE DelDueDatetList IS TABLE OF DATE
2079 INDEX BY BINARY_INTEGER;
2080 deliverableDueDates DelDueDatetList;
2081 TYPE DelStartEventDateList IS TABLE OF DATE
2082 INDEX BY BINARY_INTEGER;
2083 deliverableStartEventDates DelStartEventDateList;
2084 TYPE DelEndEventDateList IS TABLE OF DATE
2085 INDEX BY BINARY_INTEGER;
2086 deliverableEndEventDates DelEndEventDateList;
2087
2088 del_count PLS_INTEGER;
2089
2090 l_api_name CONSTANT VARCHAR2(30) := 'updateDeliverables';
2091 l_api_version CONSTANT VARCHAR2(30) := 1;
2092
2093 -- for storage of bulk Fetch
2094 delRecTab OKC_DELIVERABLE_PROCESS_PVT.delRecTabType;
2095
2096 -- for storage recurrign instances
2097 delInstanceRecTab OKC_DELIVERABLE_PROCESS_PVT.delRecTabType;
2098
2099 l_del_event_name OKC_BUS_DOC_EVENTS_B.business_event_code%TYPE;
2100 l_del_before_after OKC_BUS_DOC_EVENTS_B.before_after%TYPE;
2101 l_actual_date DATE;
2102 l_recurr_end_date DATE;
2103
2104 l_repeat_day_of_month number;
2105 l_repeat_day_of_week number;
2106
2107 l_new_status OKC_DELIVERABLES.deliverable_status%TYPE;
2108 l_has_instances_yn VARCHAR2(1);
2109
2110 BEGIN
2111 -- initialize
2112 k := 0;
2113 del_count := 0;
2114 l_actual_date := null;
2115 l_recurr_end_date := null;
2116
2117 --- if any dates have been changed on given business document, and
2118 --- selected deliverables are effected, re-resolve due dates and
2119 --- and carry forward statuses
2120
2121 -- start procedure
2122 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2123 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered '||G_PKG_NAME ||'.'||l_api_name);
2124 END IF;
2125 -- Standard Start of API savepoint
2126 SAVEPOINT g_update_del_GRP;
2127
2128 -- Standard call to check for call compatibility.
2129 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2131 END IF;
2132
2133 -- Initialize message list if p_init_msg_list is set to TRUE.
2134 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2135 FND_MSG_PUB.initialize;
2136 END IF;
2137
2138 -- Initialize API return status to success
2139 x_return_status := FND_API.G_RET_STS_SUCCESS;
2140
2141 --- open deliverables cursor and populate records of table
2142 FOR del_rec IN del_cur LOOP
2143 k := k+1;
2144 delRecTab(k).deliverable_id := del_rec.deliverable_id;
2145 delRecTab(k).BUSINESS_DOCUMENT_TYPE:= del_rec.BUSINESS_DOCUMENT_TYPE;
2146 delRecTab(k).BUSINESS_DOCUMENT_ID:= del_rec.BUSINESS_DOCUMENT_ID;
2147 delRecTab(k).BUSINESS_DOCUMENT_NUMBER:= del_rec.BUSINESS_DOCUMENT_NUMBER;
2148 delRecTab(k).DELIVERABLE_TYPE:= del_rec.DELIVERABLE_TYPE;
2149 delRecTab(k).RESPONSIBLE_PARTY:= del_rec.RESPONSIBLE_PARTY;
2150 delRecTab(k).INTERNAL_PARTY_CONTACT_ID:= del_rec.INTERNAL_PARTY_CONTACT_ID;
2151 delRecTab(k).EXTERNAL_PARTY_CONTACT_ID:= del_rec.EXTERNAL_PARTY_CONTACT_ID;
2152 delRecTab(k).DELIVERABLE_NAME:= del_rec.DELIVERABLE_NAME;
2153 delRecTab(k).DESCRIPTION:= del_rec.DESCRIPTION;
2154 delRecTab(k).COMMENTS:= del_rec.COMMENTS;
2155 delRecTab(k).DISPLAY_SEQUENCE:= del_rec.DISPLAY_SEQUENCE;
2156 delRecTab(k).FIXED_DUE_DATE_YN:= del_rec.FIXED_DUE_DATE_YN;
2157 delRecTab(k).ACTUAL_DUE_DATE:= del_rec.ACTUAL_DUE_DATE;
2158 delRecTab(k).PRINT_DUE_DATE_MSG_NAME:= del_rec.PRINT_DUE_DATE_MSG_NAME;
2159 delRecTab(k).RECURRING_YN:= del_rec.RECURRING_YN;
2160 delRecTab(k).NOTIFY_PRIOR_DUE_DATE_VALUE:= del_rec.NOTIFY_PRIOR_DUE_DATE_VALUE;
2161 delRecTab(k).NOTIFY_PRIOR_DUE_DATE_UOM:= del_rec.NOTIFY_PRIOR_DUE_DATE_UOM;
2162 delRecTab(k).NOTIFY_PRIOR_DUE_DATE_YN:= del_rec.NOTIFY_PRIOR_DUE_DATE_YN;
2163 delRecTab(k).NOTIFY_COMPLETED_YN:= del_rec.NOTIFY_COMPLETED_YN;
2164 delRecTab(k).NOTIFY_OVERDUE_YN:= del_rec.NOTIFY_OVERDUE_YN;
2165 delRecTab(k).NOTIFY_ESCALATION_YN:= del_rec.NOTIFY_ESCALATION_YN;
2166 delRecTab(k).NOTIFY_ESCALATION_VALUE:= del_rec.NOTIFY_ESCALATION_VALUE;
2167 delRecTab(k).NOTIFY_ESCALATION_UOM:= del_rec.NOTIFY_ESCALATION_UOM;
2168 delRecTab(k).ESCALATION_ASSIGNEE:= del_rec.ESCALATION_ASSIGNEE;
2169 delRecTab(k).AMENDMENT_OPERATION:= del_rec.AMENDMENT_OPERATION;
2170 delRecTab(k).PRIOR_NOTIFICATION_ID:= del_rec.PRIOR_NOTIFICATION_ID;
2171 delRecTab(k).AMENDMENT_NOTES:= del_rec.AMENDMENT_NOTES;
2172 delRecTab(k).COMPLETED_NOTIFICATION_ID:= del_rec.COMPLETED_NOTIFICATION_ID;
2173 delRecTab(k).OVERDUE_NOTIFICATION_ID:= del_rec.OVERDUE_NOTIFICATION_ID;
2174 delRecTab(k).ESCALATION_NOTIFICATION_ID:= del_rec.ESCALATION_NOTIFICATION_ID;
2175 delRecTab(k).LANGUAGE:= del_rec.LANGUAGE;
2176 delRecTab(k).ORIGINAL_DELIVERABLE_ID:= del_rec.ORIGINAL_DELIVERABLE_ID;
2177 delRecTab(k).REQUESTER_ID:= del_rec.REQUESTER_ID;
2178 delRecTab(k).EXTERNAL_PARTY_ID:= del_rec.EXTERNAL_PARTY_ID;
2179 delRecTab(k).EXTERNAL_PARTY_ROLE:= del_rec.EXTERNAL_PARTY_ROLE;
2180 delRecTab(k).RECURRING_DEL_PARENT_ID:= del_rec.RECURRING_DEL_PARENT_ID;
2181 delRecTab(k).BUSINESS_DOCUMENT_VERSION:= del_rec.BUSINESS_DOCUMENT_VERSION;
2182 delRecTab(k).RELATIVE_ST_DATE_DURATION:= del_rec.RELATIVE_ST_DATE_DURATION;
2183 delRecTab(k).RELATIVE_ST_DATE_UOM:= del_rec.RELATIVE_ST_DATE_UOM;
2184 delRecTab(k).RELATIVE_ST_DATE_EVENT_ID:= del_rec.RELATIVE_ST_DATE_EVENT_ID;
2185 delRecTab(k).RELATIVE_END_DATE_DURATION:= del_rec.RELATIVE_END_DATE_DURATION;
2186 delRecTab(k).RELATIVE_END_DATE_UOM:= del_rec.RELATIVE_END_DATE_UOM;
2187 delRecTab(k).RELATIVE_END_DATE_EVENT_ID:= del_rec.RELATIVE_END_DATE_EVENT_ID;
2188 delRecTab(k).REPEATING_DAY_OF_MONTH:= del_rec.REPEATING_DAY_OF_MONTH;
2189 delRecTab(k).REPEATING_DAY_OF_WEEK:= del_rec.REPEATING_DAY_OF_WEEK;
2190 delRecTab(k).REPEATING_FREQUENCY_UOM:= del_rec.REPEATING_FREQUENCY_UOM;
2191 delRecTab(k).REPEATING_DURATION:= del_rec.REPEATING_DURATION;
2192 delRecTab(k).FIXED_START_DATE:= del_rec.FIXED_START_DATE;
2193 delRecTab(k).FIXED_END_DATE:= del_rec.FIXED_END_DATE;
2194 delRecTab(k).MANAGE_YN:= del_rec.MANAGE_YN;
2195 delRecTab(k).INTERNAL_PARTY_ID:= del_rec.INTERNAL_PARTY_ID;
2196 delRecTab(k).DELIVERABLE_STATUS:= del_rec.DELIVERABLE_STATUS;
2197 delRecTab(k).STATUS_CHANGE_NOTES:= del_rec.STATUS_CHANGE_NOTES;
2198 delRecTab(k).CREATED_BY:= del_rec.CREATED_BY;
2199 delRecTab(k).CREATION_DATE:= del_rec.CREATION_DATE;
2200 delRecTab(k).LAST_UPDATED_BY:= del_rec.LAST_UPDATED_BY;
2201 delRecTab(k).LAST_UPDATE_DATE:= del_rec.LAST_UPDATE_DATE;
2202 delRecTab(k).LAST_UPDATE_LOGIN:= del_rec.LAST_UPDATE_LOGIN;
2203 delRecTab(k).OBJECT_VERSION_NUMBER:= del_rec.OBJECT_VERSION_NUMBER;
2204 delRecTab(k).ATTRIBUTE_CATEGORY:= del_rec.ATTRIBUTE_CATEGORY;
2205 delRecTab(k).ATTRIBUTE1:= del_rec.ATTRIBUTE1;
2206 delRecTab(k).ATTRIBUTE2:= del_rec.ATTRIBUTE2;
2207 delRecTab(k).ATTRIBUTE3:= del_rec.ATTRIBUTE3;
2208 delRecTab(k).ATTRIBUTE4:= del_rec.ATTRIBUTE4;
2209 delRecTab(k).ATTRIBUTE5:= del_rec.ATTRIBUTE5;
2210 delRecTab(k).ATTRIBUTE6:= del_rec.ATTRIBUTE6;
2211 delRecTab(k).ATTRIBUTE7:= del_rec.ATTRIBUTE7;
2212 delRecTab(k).ATTRIBUTE8:= del_rec.ATTRIBUTE8;
2213 delRecTab(k).ATTRIBUTE9:= del_rec.ATTRIBUTE9;
2214 delRecTab(k).ATTRIBUTE10:= del_rec.ATTRIBUTE10;
2215 delRecTab(k).ATTRIBUTE11:= del_rec.ATTRIBUTE11;
2216 delRecTab(k).ATTRIBUTE12:= del_rec.ATTRIBUTE12;
2217 delRecTab(k).ATTRIBUTE13:= del_rec.ATTRIBUTE13;
2218 delRecTab(k).ATTRIBUTE14:= del_rec.ATTRIBUTE14;
2219 delRecTab(k).ATTRIBUTE15:= del_rec.ATTRIBUTE15;
2220 delRecTab(k).DISABLE_NOTIFICATIONS_YN:= del_rec.DISABLE_NOTIFICATIONS_YN;
2221 delRecTab(k).LAST_AMENDMENT_DATE:= del_rec.LAST_AMENDMENT_DATE;
2222 delRecTab(k).BUSINESS_DOCUMENT_LINE_ID:= del_rec.BUSINESS_DOCUMENT_LINE_ID;
2223 delRecTab(k).EXTERNAL_PARTY_SITE_ID:= del_rec.EXTERNAL_PARTY_SITE_ID;
2224 delRecTab(k).START_EVENT_DATE:= del_rec.START_EVENT_DATE;
2225 delRecTab(k).END_EVENT_DATE:= del_rec.END_EVENT_DATE;
2226 delRecTab(k).SUMMARY_AMEND_OPERATION_CODE:= del_rec.SUMMARY_AMEND_OPERATION_CODE;
2227 delRecTab(k).PAY_HOLD_PRIOR_DUE_DATE_VALUE:=del_rec.PAY_HOLD_PRIOR_DUE_DATE_VALUE;
2228 delRecTab(k).PAY_HOLD_PRIOR_DUE_DATE_UOM:=del_rec.PAY_HOLD_PRIOR_DUE_DATE_UOM;
2229 delRecTab(k).PAY_HOLD_PRIOR_DUE_DATE_YN:=del_rec.PAY_HOLD_PRIOR_DUE_DATE_YN;
2230 delRecTab(k).PAY_HOLD_OVERDUE_YN:=del_rec.PAY_HOLD_OVERDUE_YN;
2231 delRecTab(k).RAISE_COMPLETION_EVENT_YN:=del_rec.RAISE_COMPLETION_EVENT_YN;
2232
2233 delRecTab(k).orig_system_reference_code := 'COPY';
2234 delRecTab(k).orig_system_reference_id1 := del_rec.deliverable_id;
2235 delRecTab(k). orig_system_reference_id2 := null;
2236
2237 -- CDRL
2238 delRecTab(k).del_category_code := del_rec.del_category_code;
2239 delRecTab(k).exhibit_code := del_rec.exhibit_code;
2240 delRecTab(k).data_item_number := del_rec.data_item_number;
2241 delRecTab(k).price_group := del_rec.price_group;
2242 delRecTab(k).estimated_price := del_rec.estimated_price;
2243
2244
2245
2246 END LOOP;
2247
2248 -- commented as this is not supported by 8i PL/SQL Bug#3307941
2249 /*OPEN del_cur;
2250 FETCH del_cur BULK COLLECT INTO delRecTab;*/
2251
2252
2253 -- start procedure
2254 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2255 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: Got Deliverables records --- Count'||delRecTab.count);
2256 END IF;
2257
2258 IF delRecTab.count > 0 THEN
2259
2260 FOR i IN delRecTab.FIRST..delRecTab.LAST LOOP
2261
2262 -- if there's no fixed start date, evaluate relative start date
2263 IF delRecTab(i).fixed_start_date is null THEN
2264
2265 -- start procedure
2266 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2267 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'102: Deliverable = '||delRecTab(i).deliverable_id||' is Not Fixed');
2268 END IF;
2269
2270 --- initialize start date as NULL
2271 l_actual_date := NULL;
2272
2273 --- if start date is relative, resolve the actual date
2274 IF delRecTab(i).RELATIVE_ST_DATE_EVENT_ID is not NULL THEN
2275
2276 -- start procedure
2277 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2278 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'102: Deliverable = '||delRecTab(i).deliverable_id||' is Relative ');
2279 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'103: Deliverable = '||delRecTab(i).deliverable_id||' Resolve Due Date Event ');
2280 END IF;
2281
2282 --- resolve relative end date
2283 l_actual_date := resolveRelativeDueEvents(
2284 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
2285 p_event_code => NULL,
2286 p_event_date => NULL,
2287 p_event_id => delRecTab(i).relative_st_date_event_id,
2288 p_event_UOM => delRecTab(i).relative_st_date_uom,
2289 p_event_duration => delRecTab(i).relative_st_date_duration,
2290 p_end_event_yn => 'N');
2291 -- start procedure
2292 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2293 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: Deliverable = '||delRecTab(i).deliverable_id||' Got actual date as '||l_actual_date);
2294 END IF;
2295
2296 --- here if start date is not resolved, means there's not change
2297 --- in start event date, so take the old start event date
2298 IF l_actual_date is NULL THEN
2299
2300 -- start procedure
2301 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2302 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: Deliverable = '||delRecTab(i).deliverable_id||' Get Start Event Date '||delRecTab(i).start_event_date);
2303 END IF;
2304
2305 --- if Fixed start date
2306 l_actual_date := delRecTab(i).start_event_date;
2307
2308 END IF;
2309 END IF; -- if start date is relative
2310
2311 ELSE -- if start date is fixed
2312
2313 --- initialize start date as NULL
2314 l_actual_date := NULL;
2315
2316 -- start procedure
2317 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2318 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: Deliverable = '||delRecTab(i).deliverable_id||' Setting as START event Date ');
2319 END IF;
2320
2321 --- if Fixed start date
2322 l_actual_date := delRecTab(i).fixed_start_date;
2323
2324 END IF;
2325
2326 --- If deliverable is One Time
2327 IF delRecTab(i).recurring_yn = 'N' THEN
2328
2329 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2330 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'105: Deliverable = '||delRecTab(i).deliverable_id||' IS one time ');
2331 END IF;
2332
2333 --- if resolved start date is not null, check if it is different
2334 --- from old start date
2335 IF l_actual_date is not NULL THEN
2336
2337 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2338 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'105: Deliverable = '||delRecTab(i).deliverable_id||' Actual date is not NULL '||l_actual_date);
2339 END IF;
2340 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2341 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'105a: Deliverable = '||delRecTab(i).deliverable_id||' Event date is '||delRecTab(i).start_event_date);
2342 END IF;
2343
2344 IF TRUNC(l_actual_date) <> TRUNC(delRecTab(i).start_event_date) THEN
2345
2346 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2347 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'106: Deliverable = '||delRecTab(i).deliverable_id||' Actual date DOES not match existing start event date '||l_actual_date);
2348 END IF;
2349
2350 --- increment the count
2351 del_count := del_count+1;
2352
2353 -- record deliverable id to be updated at the end
2354 deliverableIds(del_count) := delRecTab(i).deliverable_id;
2355
2356 -- record actual date, start event date and end event date
2357 deliverableDueDates(del_count) := l_actual_date;
2358 deliverableStartEventDates(del_count) := l_actual_date;
2359 deliverableEndEventDates(del_count) := NULL;
2360 END IF; -- if resolved date is different then old resolved date
2361
2362 END IF; -- if resolved date is not null
2363
2364 ELSE --- if deliverable is recurring
2365
2366 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2367 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Deliverable = '||delRecTab(i).deliverable_id||' is Recurring ');
2368 END IF;
2369
2370 --- check if deliverable has recurring instances already in place
2371 --- for given version of the document
2372 l_has_instances_yn := hasInstances(
2373 p_bus_doc_id => p_bus_doc_id,
2374 p_bus_doc_type => p_bus_doc_type,
2375 p_bus_doc_version => p_bus_doc_version,
2376 p_del_id => delRecTab(i).deliverable_id);
2377
2378 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2379 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: Recurring Deliverable, Has Instances = '||l_has_instances_yn);
2380 END IF;
2381
2382 IF l_has_instances_yn = 'Y' THEN
2383
2384 --- check the status of exiting instances
2385 l_new_status :=
2386 checkStatusOfExistingInstances(
2387 p_bus_doc_id => p_bus_doc_id,
2388 p_bus_doc_type => p_bus_doc_type,
2389 p_bus_doc_version => p_bus_doc_version,
2390 p_del_id => delRecTab(i).deliverable_id);
2391
2392 --- Start date is changed, Re-resolve the deliverable completely.
2393 IF (l_actual_date is not NULL) AND (TRUNC(l_actual_date) <> TRUNC(delRecTab(i).start_event_date)) THEN
2394
2395 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2396 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'109: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Start date is changed -- new '||l_actual_date);
2397 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'110: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Start date is changed -- OLD '||delRecTab(i).start_event_date);
2398 END IF;
2399
2400 OPEN get_del_ids_cur1(delRecTab(i).deliverable_id);
2401 FETCH get_del_ids_cur1 BULK COLLECT INTO delIds;
2402 CLOSE get_del_ids_cur1;
2403
2404 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2405 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'111: Recurring Deliverable = '||delRecTab(i).deliverable_id||' instances to be Deleted Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances');
2406 END IF;
2407
2408 IF delIds.count > 0 THEN
2409 --- call delete_del_instances or OKC_DELIVERABLE_PROCESS_PVT
2410 OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
2411 p_api_version => l_api_version,
2412 p_init_msg_list => G_FALSE,
2413 p_doc_id => p_bus_doc_id,
2414 p_doc_type => p_bus_doc_type,
2415 p_doc_version => p_bus_doc_version,
2416 p_Conditional_Delete_Flag => 'Y',
2417 p_delid_tab => delIds,
2418 x_msg_data => x_msg_data,
2419 x_msg_count => x_msg_count,
2420 x_return_status => x_return_status);
2421
2422 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2423 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'112: Recurring Deliverable = '||delRecTab(i).deliverable_id||' FINISHED OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances'||x_return_status);
2424 END IF;
2425 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2427 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2428 RAISE FND_API.G_EXC_ERROR ;
2429 END IF;
2430 END IF; -- end delIds > 0
2431 --- re-resolve the deliverable
2432 --- you have the start date.
2433 --- get end date, either fixed or relative
2434 l_recurr_end_date := null;
2435 IF delRecTab(i).fixed_end_date is not null THEN
2436
2437 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2438 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'112: Recurring Deliverable = '||delRecTab(i).deliverable_id||' End date is Fixed'||delRecTab(i).fixed_end_date);
2439 END IF;
2440
2441 --- set the end date as fixed end date
2442 l_recurr_end_date := delRecTab(i).fixed_end_date;
2443
2444 ELSE -- end date is relative
2445
2446 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2447 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'113: Recurring Deliverable = '||delRecTab(i).deliverable_id||' End date is Relative, Resolve end data');
2448 END IF;
2449
2450 --- resolve relative end date
2451 l_recurr_end_date := resolveRelativeDueEvents(
2452 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
2453 p_event_code => NULL,
2454 p_event_date => NULL,
2455 p_event_id => delRecTab(i).relative_end_date_event_id,
2456 p_event_UOM => delRecTab(i).relative_end_date_uom,
2457 p_event_duration => delRecTab(i).relative_end_date_duration,
2458 p_end_event_yn => 'Y');
2459
2460 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2461 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'114: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Resolved End date '||l_recurr_end_date);
2462 END IF;
2463
2464 END IF;
2465
2466 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2467 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'115: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Resolved End date '||l_recurr_end_date);
2468 END IF;
2469
2470 --- if resolved end date comes out to be NULL, take the
2471 --- old end event date
2472 IF l_recurr_end_date is NULL THEN
2473
2474 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2475 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'115: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Getting End Event Date '||delRecTab(i).end_event_date);
2476 END IF;
2477
2478 --- set the end date as fixed end date
2479 l_recurr_end_date := delRecTab(i).end_event_date;
2480
2481 END IF;
2482
2483 --- raise exception if END is NULL
2484 IF l_recurr_end_date is NULL THEN
2485 Okc_Api.Set_Message(G_APP_NAME,
2486 'OKC_DEL_END_DT_NOT_FOUND');
2487 RAISE FND_API.G_EXC_ERROR;
2488 END IF;
2489
2490
2491 --- get the repeat frequency and create new instances
2492 l_repeat_day_of_month := getDayOfMonth(
2493 delRecTab(i).REPEATING_DAY_OF_MONTH);
2494 l_repeat_day_of_week := getDayOfWeek(
2495 delRecTab(i).REPEATING_DAY_OF_WEEK);
2496
2497 -- if both frequency values ar null
2498 IF (l_repeat_day_of_month is NULL AND l_repeat_day_of_week is NULL
2499 AND Nvl(delRecTab(i).repeating_frequency_uom,'ABC') <> 'DAY') THEN
2500 Okc_Api.Set_Message(G_APP_NAME,
2501 'OKC_DEL_RECUR_FRQ_NOT_FOUND');
2502 RAISE FND_API.G_EXC_ERROR;
2503 END IF;
2504
2505 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2506 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'116: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Calling generated instances');
2507 END IF;
2508
2509 -- generate recurring instances
2510 generate_del_instances(
2511 p_recurr_start_date => l_actual_date,
2512 p_recurr_end_date => l_recurr_end_date,
2513 p_repeat_duration => delRecTab(i).repeating_duration,
2514 p_repeat_day_of_month => l_repeat_day_of_month,
2515 p_repeat_day_of_week => l_repeat_day_of_week,
2516 delRecord => delRecTab(i),
2517 p_change_status_to => l_new_status);
2518
2519 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2520 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'117: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Done generating instances');
2521 END IF;
2522
2523 --- increment the count
2524 del_count := del_count+1;
2525
2526 -- record deliverable id to be updated at the end
2527 deliverableIds(del_count) := delRecTab(i).deliverable_id;
2528
2529 -- record actual date, start event date and end event date
2530 deliverableDueDates(del_count) := NULL;
2531 deliverableStartEventDates(del_count) := l_actual_date;
2532 deliverableEndEventDates(del_count) := l_recurr_end_date;
2533
2534 ELSE --- if start date is not changed
2535
2536 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2537 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'118: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Start date is not changed');
2538 END IF;
2539
2540 -- initialize recurring end date
2541 l_recurr_end_date := null;
2542
2543 --- check if end date is not FIXED, if it is Fixed, no more
2544 --- further operation.
2545 IF delRecTab(i).fixed_end_date is null THEN
2546
2547 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2548 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'119: Recurring Deliverable = '||delRecTab(i).deliverable_id||' End date is relative, resolve end date');
2549 END IF;
2550
2551 --- resolve relative end date
2552 l_recurr_end_date := resolveRelativeDueEvents(
2553 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
2554 p_event_code => NULL,
2555 p_event_date => NULL,
2556 p_event_id => delRecTab(i).relative_end_date_event_id,
2557 p_event_UOM => delRecTab(i).relative_end_date_uom,
2558 p_event_duration => delRecTab(i).relative_end_date_duration,
2559 p_end_event_yn => 'Y');
2560
2561 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2562 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'120: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Resolved end date '||l_recurr_end_date);
2563 END IF;
2564
2565 ELSE --- get the fixed end date, provided
2566
2567 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2568 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'121: Recurring Deliverable = '||delRecTab(i).deliverable_id||' Take Fixed End date '||delRecTab(i).fixed_end_date);
2569 END IF;
2570
2571 -- set the fixed end date
2572 l_recurr_end_date := delRecTab(i).fixed_end_date;
2573
2574 END IF;
2575
2576 --- raise exception if END is NULL
2577 /* IF l_recurr_end_date is NULL THEN
2578 Okc_Api.Set_Message(G_APP_NAME,
2579 'OKC_DEL_END_DT_NOT_FOUND');
2580 RAISE FND_API.G_EXC_ERROR;
2581 END IF; */
2582
2583 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2584 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'121a: new end date = '||l_recurr_end_date||' Old end date '||delRecTab(i).end_event_date);
2585 END IF;
2586
2587 --- check if new date is less then old date
2588 --- delete instances where actual date is equal to or
2589 --- greater then new date
2590
2591 IF (l_recurr_end_date is not NULL) AND (TRUNC(l_recurr_end_date) < TRUNC(delRecTab(i).end_event_date)) THEN
2592
2593 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2594 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'122: Recurring Deliverable = '||delRecTab(i).deliverable_id||'New End Date is less then old end date');
2595 END IF;
2596
2597 --- hard Delete old instances from current version
2598 OPEN get_del_ids_cur2(delRecTab(i).deliverable_id, l_recurr_end_date);
2599 FETCH get_del_ids_cur2 BULK COLLECT INTO delIds;
2600 CLOSE get_del_ids_cur2;
2601
2602 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2603 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'123: Recurring Deliverable = '||delRecTab(i).deliverable_id||'Calling OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances');
2604 END IF;
2605
2606 IF delIds.count > 0 THEN
2607 --- call delete_del_instances or OKC_DELIVERABLE_PROCESS_PVT
2608 OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances(
2609 p_api_version => l_api_version,
2610 p_init_msg_list => G_FALSE,
2611 p_doc_id => p_bus_doc_id,
2612 p_doc_type => p_bus_doc_type,
2613 p_doc_version => p_bus_doc_version,
2614 p_Conditional_Delete_Flag => 'Y',
2615 p_delid_tab => delIds,
2616 x_msg_data => x_msg_data,
2617 x_msg_count => x_msg_count,
2618 x_return_status => x_return_status);
2619
2620 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2621 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'124: Recurring Deliverable = '||delRecTab(i).deliverable_id||'Finished OKC_DELIVERABLE_PROCESS_PVT.delete_del_instances'||x_return_status);
2622 END IF;
2623 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2625 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2626 RAISE FND_API.G_EXC_ERROR ;
2627 END IF;
2628
2629 END IF;
2630 --- increment the count
2631 del_count := del_count+1;
2632
2633 ----- Done Creating new deliverable instances ---
2634 --- update current deliverable definition with start and end event date
2635 -- record deliverable id to be updated at the end
2636 deliverableIds(del_count) := delRecTab(i).deliverable_id;
2637
2638 -- record actual date, start event date and end event date
2639 deliverableDueDates(del_count) := NULL;
2640 deliverableStartEventDates(del_count) := delRecTab(i).start_event_date;
2641 deliverableEndEventDates(del_count) := l_recurr_end_date;
2642
2643 END IF;
2644
2645 --- check if new date is greater then old date
2646 --- generate new instances with start date as old end date
2647 --- and end date as new end date, us the same repeat frequency on
2648 --- the given deliverable and resolve it.
2649 IF (l_recurr_end_date is not NULL) AND TRUNC(l_recurr_end_date) > TRUNC(delRecTab(i).end_event_date) THEN
2650
2651 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2652 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'125: Recurring Deliverable = '||delRecTab(i).deliverable_id||'New end date is greater then old end dates');
2653 END IF;
2654
2655 --- get the repeat frequency and create new instances
2656 l_repeat_day_of_month := getDayOfMonth(
2657 delRecTab(i).REPEATING_DAY_OF_MONTH);
2658 l_repeat_day_of_week := getDayOfWeek(
2659 delRecTab(i).REPEATING_DAY_OF_WEEK);
2660
2661
2662 -- if both frequency values ar null
2663 IF (l_repeat_day_of_month is NULL AND l_repeat_day_of_week is NULL
2664 AND Nvl(delRecTab(i).repeating_frequency_uom,'ABC') <> 'DAY') THEN
2665 Okc_Api.Set_Message(G_APP_NAME,
2666 'OKC_DEL_RECUR_FRQ_NOT_FOUND');
2667 RAISE FND_API.G_EXC_ERROR;
2668 END IF;
2669
2670 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2671 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'126: Recurring Deliverable = '||delRecTab(i).deliverable_id||'Calling generate_del_instances');
2672 END IF;
2673 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2674 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'126a: Recurring Deliverable = '||delRecTab(i).deliverable_id||'Setting Status'||l_new_status);
2675 END IF;
2676
2677 -- generate delta of recurring instances
2678 generate_del_instances(
2679 p_recurr_start_date => delRecTab(i).end_event_date+1,
2680 p_recurr_end_date => l_recurr_end_date,
2681 p_repeat_duration => delRecTab(i).repeating_duration,
2682 p_repeat_day_of_month => l_repeat_day_of_month,
2683 p_repeat_day_of_week => l_repeat_day_of_week,
2684 delRecord => delRecTab(i),
2685 p_change_status_to => l_new_status);
2686
2687 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2688 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'127: Recurring Deliverable = '||delRecTab(i).deliverable_id||'Done generate_del_instances');
2689 END IF;
2690
2691 --- increment the count
2692 del_count := del_count+1;
2693
2694 ----- Done Creating new deliverable instances ---
2695 --- update current deliverable definition with start and end event date
2696 -- record deliverable id to be updated at the end
2697 deliverableIds(del_count) := delRecTab(i).deliverable_id;
2698
2699 -- record actual date, start event date and end event date
2700 deliverableDueDates(del_count) := NULL;
2701 deliverableStartEventDates(del_count) := delRecTab(i).start_event_date;
2702 deliverableEndEventDates(del_count) := l_recurr_end_date;
2703 END IF; -- if new end date is greater then old end date
2704 END IF; -- if start date is not changed
2705 END IF;
2706 END IF; -- if deliverables is recurring or not
2707
2708 END LOOP;
2709 END IF;
2710
2711 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2712 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'128: Bulk Update Started');
2713 END IF;
2714
2715 IF deliverableIds.count > 0 THEN
2716 --- bulk update for deliverables actual due date
2717 FORALL i IN deliverableIds.FIRST..deliverableIds.LAST
2718 UPDATE okc_deliverables
2719 SET
2720 actual_due_date = deliverableDueDates(i),
2721 start_event_date = deliverableStartEventDates(i),
2722 end_event_date = deliverableEndEventDates(i),
2723 last_updated_by= Fnd_Global.User_Id,
2724 last_update_date = sysdate,
2725 last_update_login = Fnd_Global.Login_Id
2726 WHERE deliverable_id = deliverableIds(i);
2727 END IF;
2728
2729 IF del_cur %ISOPEN THEN
2730 CLOSE del_cur ;
2731 END IF;
2732
2733 IF FND_API.To_Boolean( p_commit ) THEN
2734 COMMIT WORK;
2735 END IF;
2736
2737 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2738 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateDeliverables');
2739 END IF;
2740
2741 EXCEPTION
2742
2743 WHEN FND_API.G_EXC_ERROR THEN
2744 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2745 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateDeliverables Unexpected ERROR');
2746 END IF;
2747 IF del_cur %ISOPEN THEN
2748 CLOSE del_cur ;
2749 END IF;
2750 IF get_del_ids_cur2 %ISOPEN THEN
2751 CLOSE get_del_ids_cur2;
2752 END IF;
2753 IF get_del_ids_cur1 %ISOPEN THEN
2754 CLOSE get_del_ids_cur1;
2755 END IF;
2756
2757 ROLLBACK TO g_update_del_GRP;
2758 x_return_status := G_RET_STS_ERROR ;
2759 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2760
2761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2762 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2763 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateDeliverables Unexpected ERROR');
2764 END IF;
2765 IF del_cur %ISOPEN THEN
2766 CLOSE del_cur ;
2767 END IF;
2768 IF get_del_ids_cur2 %ISOPEN THEN
2769 CLOSE get_del_ids_cur2;
2770 END IF;
2771 IF get_del_ids_cur1 %ISOPEN THEN
2772 CLOSE get_del_ids_cur1;
2773 END IF;
2774
2775 ROLLBACK TO g_update_del_GRP;
2776 x_return_status := G_RET_STS_UNEXP_ERROR ;
2777 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2778
2779 WHEN OTHERS THEN
2780 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2781 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
2782 END IF;
2783 IF del_cur %ISOPEN THEN
2784 CLOSE del_cur ;
2785 END IF;
2786 IF get_del_ids_cur2 %ISOPEN THEN
2787 CLOSE get_del_ids_cur2;
2788 END IF;
2789 IF get_del_ids_cur1 %ISOPEN THEN
2790 CLOSE get_del_ids_cur1;
2791 END IF;
2792
2793 ROLLBACK TO g_update_del_GRP;
2794 x_return_status := G_RET_STS_UNEXP_ERROR ;
2795 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2796 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2797 END IF;
2798 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2799
2800 END; -- updateDeliverables
2801
2802 /**
2803 * Enable Notifications flag of deliverables for given business document id
2804 * and type.
2805 */
2806 PROCEDURE enableNotifications (
2807 p_api_version IN NUMBER,
2808 p_init_msg_list IN VARCHAR2,
2809 p_commit IN Varchar2,
2810 p_bus_doc_id IN NUMBER,
2811 p_bus_doc_type IN VARCHAR2,
2812 p_bus_doc_version IN NUMBER,
2813 x_msg_data OUT NOCOPY VARCHAR2,
2814 x_msg_count OUT NOCOPY NUMBER,
2815 x_return_status OUT NOCOPY VARCHAR2)
2816 IS
2817 l_api_name CONSTANT VARCHAR2(30) := 'enableNotifications';
2818 l_api_version CONSTANT VARCHAR2(30) := 1;
2819
2820 BEGIN
2821
2822 -- start procedure
2823 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2824 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
2825 END IF;
2826
2827 -- Standard Start of API savepoint
2828 SAVEPOINT g_enable_del_ntf_GRP;
2829
2830 -- Standard call to check for call compatibility.
2831 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2833 END IF;
2834
2835 -- Initialize message list if p_init_msg_list is set to TRUE.
2836 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2837 FND_MSG_PUB.initialize;
2838 END IF;
2839
2840 -- Initialize API return status to success
2841 x_return_status := FND_API.G_RET_STS_SUCCESS;
2842
2843 UPDATE OKC_DELIVERABLES set DISABLE_NOTIFICATIONS_YN = 'N'
2844 WHERE business_document_id = p_bus_doc_id
2845 AND business_document_type = p_bus_doc_type
2846 AND business_document_version = p_bus_doc_version;
2847
2848 IF FND_API.To_Boolean( p_commit ) THEN
2849 COMMIT WORK;
2850 END IF;
2851
2852 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2853 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving enableNotifications');
2854 END IF;
2855
2856 EXCEPTION
2857
2858 WHEN FND_API.G_EXC_ERROR THEN
2859 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2860 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving enableNotifications Unexpected ERROR');
2861 END IF;
2862 ROLLBACK TO g_enable_del_ntf_GRP;
2863 x_return_status := G_RET_STS_ERROR ;
2864 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2865
2866 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2867 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2868 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving enableNotifications Unexpected ERROR');
2869 END IF;
2870 ROLLBACK TO g_enable_del_ntf_GRP;
2871 x_return_status := G_RET_STS_UNEXP_ERROR ;
2872 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2873
2874 WHEN OTHERS THEN
2875 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2876 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving enableNotifications because of EXCEPTION: '||substr(sqlerrm,1,200));
2877 END IF;
2878 ROLLBACK TO g_enable_del_ntf_GRP;
2879 x_return_status := G_RET_STS_UNEXP_ERROR ;
2880 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2881 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2882 END IF;
2883 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2884
2885 END; -- enableNotifications
2886
2887 /**
2888 * Cancel deliverables for given business document id and type, without
2889 * activating deliverables for given cancel event code
2890 */
2891 PROCEDURE cancelDeliverables (
2892 p_api_version IN NUMBER,
2893 p_init_msg_list IN VARCHAR2,
2894 p_commit IN Varchar2,
2895 p_bus_doc_id IN NUMBER,
2896 p_bus_doc_type IN VARCHAR2,
2897 p_bus_doc_version IN NUMBER,
2898 x_msg_data OUT NOCOPY VARCHAR2,
2899 x_msg_count OUT NOCOPY NUMBER,
2900 x_return_status OUT NOCOPY VARCHAR2)
2901 IS
2902 l_api_name CONSTANT VARCHAR2(30) := 'cancelDeliverables';
2903 l_api_version CONSTANT VARCHAR2(30) := 1;
2904
2905 BEGIN
2906
2907 -- start procedure
2908 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2909 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
2910 END IF;
2911
2912 -- Standard Start of API savepoint
2913 SAVEPOINT g_cancel_del_GRP;
2914
2915 -- Standard call to check for call compatibility.
2916 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2918 END IF;
2919
2920 -- Initialize message list if p_init_msg_list is set to TRUE.
2921 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2922 FND_MSG_PUB.initialize;
2923 END IF;
2924
2925 -- Initialize API return status to success
2926 x_return_status := FND_API.G_RET_STS_SUCCESS;
2927
2928 -- start procedure
2929 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2930 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||'change_deliverable_status');
2931 END IF;
2932
2933 --- call change_deliverable_status, to change deliverable status
2934 --- to CANCELLED
2935 OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status(
2936 p_api_version => l_api_version,
2937 p_init_msg_list => G_FALSE,
2938 p_doc_id => p_bus_doc_id,
2939 p_doc_version => p_bus_doc_version,
2940 p_doc_type => p_bus_doc_type,
2941 p_cancel_yn => 'Y',
2942 p_cancel_event_code => NULL,
2943 p_current_status => NULL,
2944 p_new_status => 'CANCELLED',
2945 p_manage_yn => 'N',
2946 x_msg_data => x_msg_data,
2947 x_msg_count => x_msg_count,
2948 x_return_status => x_return_status);
2949
2950 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2951 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished change_deliverable_status' ||x_return_status);
2952 END IF;
2953
2954 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2955 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2956 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2957 RAISE FND_API.G_EXC_ERROR ;
2958 END IF;
2959
2960
2961 IF FND_API.To_Boolean( p_commit ) THEN
2962 COMMIT WORK;
2963 END IF;
2964
2965 -- Standard call to get message count and if count is 1, get message info.
2966 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2967
2968 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2969 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving cancelDeliverables');
2970 END IF;
2971
2972 EXCEPTION
2973
2974 WHEN FND_API.G_EXC_ERROR THEN
2975 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2976 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving cancelDeliverables Unexpected ERROR');
2977 END IF;
2978 ROLLBACK TO g_cancel_del_GRP;
2979 x_return_status := G_RET_STS_ERROR ;
2980 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2981
2982 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2983 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2984 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving cancelDeliverables Unexpected ERROR');
2985 END IF;
2986 ROLLBACK TO g_cancel_del_GRP;
2987 x_return_status := G_RET_STS_UNEXP_ERROR ;
2988 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2989
2990 WHEN OTHERS THEN
2991 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2992 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving cancelDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
2993 END IF;
2994 ROLLBACK TO g_cancel_del_GRP;
2995 x_return_status := G_RET_STS_UNEXP_ERROR ;
2996 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2997 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2998 END IF;
2999 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3000
3001 END;
3002
3003 /**
3004 * Cancel deliverables for given business document id and type and
3005 * activate deliverables for given cancel event code
3006 */
3007 PROCEDURE cancelDeliverables (
3008 p_api_version IN NUMBER,
3009 p_init_msg_list IN VARCHAR2,
3010 p_commit IN Varchar2,
3011 p_bus_doc_id IN NUMBER,
3012 p_bus_doc_type IN VARCHAR2,
3013 p_bus_doc_version IN NUMBER,
3014 p_event_code IN VARCHAR2,
3015 p_event_date IN DATE,
3016 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
3017 x_msg_data OUT NOCOPY VARCHAR2,
3018 x_msg_count OUT NOCOPY NUMBER,
3019 x_return_status OUT NOCOPY VARCHAR2)
3020 IS
3021 l_api_name CONSTANT VARCHAR2(30) := 'cancelDeliverables';
3022 l_api_version CONSTANT VARCHAR2(30) := 1;
3023
3024 BEGIN
3025
3026 -- start procedure
3027 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3028 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
3029 END IF;
3030
3031 -- Standard Start of API savepoint
3032 SAVEPOINT g_cancel2_del_GRP;
3033
3034 -- Standard call to check for call compatibility.
3035 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3037 END IF;
3038
3039 -- Initialize message list if p_init_msg_list is set to TRUE.
3040 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3041 FND_MSG_PUB.initialize;
3042 END IF;
3043
3044 -- Initialize API return status to success
3045 x_return_status := FND_API.G_RET_STS_SUCCESS;
3046
3047 -- start procedure
3048 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3049 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||'change_deliverable_status');
3050 END IF;
3051
3052 --- call change_deliverable_status, to change deliverable status
3053 --- to CANCELLED
3054 OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status(
3055 p_api_version => l_api_version,
3056 p_init_msg_list => G_FALSE,
3057 p_doc_id => p_bus_doc_id,
3058 p_doc_version => p_bus_doc_version,
3059 p_doc_type => p_bus_doc_type,
3060 p_cancel_yn => 'Y',
3061 p_cancel_event_code => p_event_code,
3062 p_current_status => null,
3063 p_new_status => 'CANCELLED',
3064 p_manage_yn => 'N',
3065 x_msg_data => x_msg_data,
3066 x_msg_count => x_msg_count,
3067 x_return_status => x_return_status);
3068
3069 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3070 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished change_deliverable_status' ||x_return_status);
3071 END IF;
3072
3073 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3075 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3076 RAISE FND_API.G_EXC_ERROR ;
3077 END IF;
3078
3079 -- Initialize API return status to success
3080 x_return_status := FND_API.G_RET_STS_SUCCESS;
3081
3082 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3083 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||'resolveDeliverables');
3084 END IF;
3085
3086 --- resolve deliverables
3087 resolveDeliverables(
3088 p_api_version => l_api_version,
3089 p_init_msg_list => G_FALSE,
3090 p_commit => G_FALSE,
3091 p_bus_doc_id => p_bus_doc_id,
3092 p_bus_doc_type => p_bus_doc_type,
3093 p_bus_doc_version => p_bus_doc_version,
3094 p_event_code => p_event_code,
3095 p_event_date => p_event_date,
3096 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
3097 x_msg_data => x_msg_data,
3098 x_msg_count => x_msg_count,
3099 x_return_status => x_return_status,
3100 p_cancel_flag => G_TRUE);
3101
3102 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3103 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished resolveDeliverables' ||x_return_status);
3104 END IF;
3105
3106 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3108 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3109 RAISE FND_API.G_EXC_ERROR ;
3110 END IF;
3111
3112 -- Initialize API return status to success
3113 x_return_status := FND_API.G_RET_STS_SUCCESS;
3114
3115 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3116 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||'OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status');
3117 END IF;
3118
3119 --- call change_deliverable_status, to change deliverable status from
3120 --- INACTIVE to 'OPEN'
3121 OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status(
3122 p_api_version => l_api_version,
3123 p_init_msg_list => G_FALSE,
3124 p_doc_id => p_bus_doc_id,
3125 p_doc_version => p_bus_doc_version,
3126 p_doc_type => p_bus_doc_type,
3127 p_cancel_yn => 'N',
3128 p_cancel_event_code => NULL,
3129 p_current_status => 'INACTIVE',
3130 p_new_status => 'OPEN',
3131 p_manage_yn => 'Y',
3132 x_msg_data => x_msg_data,
3133 x_msg_count => x_msg_count,
3134 x_return_status => x_return_status);
3135
3136 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3137 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Finished OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status' ||x_return_status);
3138 END IF;
3139
3140 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3142 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3143 RAISE FND_API.G_EXC_ERROR ;
3144 END IF;
3145
3146 IF FND_API.To_Boolean( p_commit ) THEN
3147 COMMIT WORK;
3148 END IF;
3149
3150 -- Standard call to get message count and if count is 1, get message info.
3151 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3152
3153 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3154 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving cancelDeliverables');
3155 END IF;
3156
3157 EXCEPTION
3158
3159 WHEN FND_API.G_EXC_ERROR THEN
3160 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3161 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving cancelDeliverables Unexpected ERROR');
3162 END IF;
3163 ROLLBACK TO g_cancel2_del_GRP;
3164 x_return_status := G_RET_STS_ERROR ;
3165 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3166
3167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3168
3169 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3170 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving cancelDeliverables Unexpected ERROR');
3171 END IF;
3172 ROLLBACK TO g_cancel2_del_GRP;
3173 x_return_status := G_RET_STS_UNEXP_ERROR ;
3174 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3175
3176 WHEN OTHERS THEN
3177 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3178 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving cancelDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3179 END IF;
3180 ROLLBACK TO g_cancel2_del_GRP;
3181 x_return_status := G_RET_STS_UNEXP_ERROR ;
3182 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3183 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3184 END IF;
3185 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3186
3187 END; -- cancelDeliverables
3188
3189
3190 /**
3191 * Update buyer on deliverables for given business document id and type.
3192 */
3193 PROCEDURE updateBuyerOnDeliverables (
3194 p_api_version IN NUMBER,
3195 p_init_msg_list IN VARCHAR2,
3196 p_commit IN Varchar2,
3197 p_bus_doc_id IN NUMBER,
3198 p_bus_doc_type IN VARCHAR2,
3199 p_bus_doc_version IN NUMBER,
3200 p_original_buyer_id IN NUMBER,
3201 p_new_buyer_id IN NUMBER,
3202 x_msg_data OUT NOCOPY VARCHAR2,
3203 x_msg_count OUT NOCOPY NUMBER,
3204 x_return_status OUT NOCOPY VARCHAR2)
3205 IS
3206 l_api_name CONSTANT VARCHAR2(30) := 'updateBuyerOnDeliverables';
3207 l_api_version CONSTANT VARCHAR2(30) := 1;
3208
3209 BEGIN
3210
3211 -- start procedure
3212 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3213 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
3214 END IF;
3215
3216 -- Standard Start of API savepoint
3217 SAVEPOINT g_update_del_GRP;
3218
3219 -- Standard call to check for call compatibility.
3220 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3222 END IF;
3223
3224 -- Initialize message list if p_init_msg_list is set to TRUE.
3225 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3226 FND_MSG_PUB.initialize;
3227 END IF;
3228
3229 -- Initialize API return status to success
3230 x_return_status := FND_API.G_RET_STS_SUCCESS;
3231
3232 UPDATE OKC_DELIVERABLES
3233 set internal_party_contact_id = p_new_buyer_id,
3234 last_updated_by= Fnd_Global.User_Id,
3235 last_update_date = sysdate,
3236 last_update_login=Fnd_Global.Login_Id
3237 WHERE business_document_id = p_bus_doc_id
3238 AND business_document_type = p_bus_doc_type
3239 AND business_document_version = p_bus_doc_version
3240 AND internal_party_contact_id = p_original_buyer_id;
3241
3242 IF FND_API.To_Boolean( p_commit ) THEN
3243 COMMIT WORK;
3244 END IF;
3245
3246 -- Standard call to get message count and if count is 1, get message info.
3247 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3248
3249 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3250 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateDeliverables');
3251 END IF;
3252
3253 EXCEPTION
3254
3255 WHEN FND_API.G_EXC_ERROR THEN
3256 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3257 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateDeliverables Unexpected ERROR');
3258 END IF;
3259 ROLLBACK TO g_update_del_GRP;
3260 x_return_status := G_RET_STS_ERROR ;
3261 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3262
3263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3264 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3265 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateDeliverables Unexpected ERROR');
3266 END IF;
3267 ROLLBACK TO g_update_del_GRP;
3268 x_return_status := G_RET_STS_UNEXP_ERROR ;
3269 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3270
3271 WHEN OTHERS THEN
3272 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3273 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3274 END IF;
3275 ROLLBACK TO g_update_del_GRP;
3276 x_return_status := G_RET_STS_UNEXP_ERROR ;
3277 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3278 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3279 END IF;
3280 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3281
3282 END; -- updateDeliverables
3283
3284 /**
3285 * Update buyer on deliverables for given business document id and type.
3286 */
3287 PROCEDURE updateBuyerOnDeliverables (
3288 p_api_version IN NUMBER,
3289 p_init_msg_list IN VARCHAR2,
3290 p_commit IN Varchar2,
3291 p_bus_docs_tbl IN BUSDOCS_TBL_TYPE,
3292 p_original_buyer_id IN NUMBER,
3293 p_new_buyer_id IN NUMBER,
3294 x_msg_data OUT NOCOPY VARCHAR2,
3295 x_msg_count OUT NOCOPY NUMBER,
3296 x_return_status OUT NOCOPY VARCHAR2)
3297 IS
3298 l_api_name CONSTANT VARCHAR2(30) := 'updateBuyerDeliverables';
3299 l_api_version CONSTANT VARCHAR2(30) := 1;
3300
3301 TYPE BusDocIdList IS TABLE OF NUMBER
3302 INDEX BY BINARY_INTEGER;
3303 TYPE BusDocTypeList IS TABLE OF VARCHAR2(30)
3304 INDEX BY BINARY_INTEGER;
3305 TYPE BusDocVersionList IS TABLE OF NUMBER
3306 INDEX BY BINARY_INTEGER;
3307
3308 l_bus_doc_ids BusDocIdList;
3309 l_bus_doc_types BusDocTypeList;
3310 l_bus_doc_versions BusDocVersionList;
3311
3312 BEGIN
3313
3314 -- start procedure
3315 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3316 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
3317 END IF;
3318
3319 -- Standard Start of API savepoint
3320 SAVEPOINT g_update2_del_GRP;
3321
3322 -- Standard call to check for call compatibility.
3323 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3325 END IF;
3326
3327 -- Initialize message list if p_init_msg_list is set to TRUE.
3328 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3329 FND_MSG_PUB.initialize;
3330 END IF;
3331
3332 -- Initialize API return status to success
3333 x_return_status := FND_API.G_RET_STS_SUCCESS;
3334
3335 IF p_bus_docs_tbl.count > 0 THEN
3336 FOR i IN p_bus_docs_tbl.FIRST..p_bus_docs_tbl.LAST LOOP
3337 l_bus_doc_ids(i) := p_bus_docs_tbl(i).bus_doc_id;
3338 l_bus_doc_types(i) := p_bus_docs_tbl(i).bus_doc_type;
3339 l_bus_doc_versions(i) := p_bus_docs_tbl(i).bus_doc_version;
3340 END LOOP;
3341 END IF;
3342 --- bulk update for deliverables actual due date
3343 FORALL j IN p_bus_docs_tbl.FIRST..p_bus_docs_tbl.LAST
3344 UPDATE OKC_DELIVERABLES
3345 set internal_party_contact_id = p_new_buyer_id,
3346 last_updated_by= Fnd_Global.User_Id,
3347 last_update_date = sysdate,
3348 last_update_login=Fnd_Global.Login_Id
3349 WHERE internal_party_contact_id = p_original_buyer_id
3350 AND business_document_id = l_bus_doc_ids(j)
3351 AND business_document_type = l_bus_doc_types(j)
3352 AND business_document_version = l_bus_doc_versions(j);
3353
3354 IF FND_API.To_Boolean( p_commit ) THEN
3355 COMMIT WORK;
3356 END IF;
3357
3358 -- Standard call to get message count and if count is 1, get message info.
3359 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3360
3361 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3362 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateBuyerDeliverables');
3363 END IF;
3364
3365 EXCEPTION
3366
3367 WHEN FND_API.G_EXC_ERROR THEN
3368 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3369 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateBuyerDeliverables Unexpected ERROR');
3370 END IF;
3371 ROLLBACK TO g_update2_del_GRP;
3372 x_return_status := G_RET_STS_ERROR ;
3373 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3374
3375
3376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3377 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3378 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateBuyerDeliverables Unexpected ERROR');
3379 END IF;
3380 ROLLBACK TO g_update2_del_GRP;
3381 x_return_status := G_RET_STS_UNEXP_ERROR ;
3382 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3383
3384 WHEN OTHERS THEN
3385 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3386 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateBuyerDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3387 END IF;
3388 ROLLBACK TO g_update2_del_GRP;
3389 x_return_status := G_RET_STS_UNEXP_ERROR ;
3390 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3392 END IF;
3393 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3394
3395 END; -- updateBuyerDeliverables
3396
3397 /**
3398 * This procedure disables execution of deliverables for a given business document
3399 * version.
3400 */
3401 PROCEDURE disableDeliverables (
3402 p_api_version IN NUMBER,
3403 p_init_msg_list IN VARCHAR2,
3404 p_commit IN VARCHAR2,
3405 p_bus_doc_id IN NUMBER,
3406 p_bus_doc_type IN VARCHAR2,
3407 p_bus_doc_version IN NUMBER, -- -99 for Sourcing.
3408 x_msg_data OUT NOCOPY VARCHAR2,
3409 x_msg_count OUT NOCOPY NUMBER,
3410 x_return_status OUT NOCOPY VARCHAR2)
3411 IS
3412 l_api_name CONSTANT VARCHAR2(30) := 'disableDeliverables';
3413 l_api_version CONSTANT VARCHAR2(30) := 1;
3414
3415 BEGIN
3416 -- start procedure
3417 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3418 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered '||G_PKG_NAME ||'.'||l_api_name);
3419 END IF;
3420
3421 -- Standard Start of API savepoint
3422 SAVEPOINT g_disable_del_GRP;
3423
3424 -- Standard call to check for call compatibility.
3425 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3427 END IF;
3428
3429 -- Initialize message list if p_init_msg_list is set to TRUE.
3430 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3431 FND_MSG_PUB.initialize;
3432 END IF;
3433
3434 -- Initialize API return status to success
3435 x_return_status := FND_API.G_RET_STS_SUCCESS;
3436
3437 -- start procedure
3438 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3439 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'200: Calling OKC_DELIVERABLE_PROCESS_PVT.disable_deliverables');
3440 END IF;
3441
3442 --- call change_deliverable_status, to change deliverable status
3443 --- to CANCELLED
3444 OKC_DELIVERABLE_PROCESS_PVT.disable_deliverables(
3445 p_api_version => l_api_version,
3446 p_init_msg_list => G_FALSE,
3447 p_doc_id => p_bus_doc_id,
3448 p_doc_version => p_bus_doc_version,
3449 p_doc_type => p_bus_doc_type,
3450 x_msg_data => x_msg_data,
3451 x_msg_count => x_msg_count,
3452 x_return_status => x_return_status);
3453
3454 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3455 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: Finished disable_deliverables' ||x_return_status);
3456 END IF;
3457
3458 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3460 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3461 RAISE FND_API.G_EXC_ERROR ;
3462 END IF;
3463
3464
3465 IF FND_API.To_Boolean( p_commit ) THEN
3466 COMMIT WORK;
3467 END IF;
3468
3469 -- Standard call to get message count and if count is 1, get message info.
3470 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3471
3472 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3473 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'400: Leaving disableDeliverables');
3474 END IF;
3475
3476 EXCEPTION
3477
3478 WHEN FND_API.G_EXC_ERROR THEN
3479 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3480 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving disableDeliverables Unexpected ERROR');
3481 END IF;
3482 ROLLBACK TO g_disable_del_GRP;
3483 x_return_status := G_RET_STS_ERROR ;
3484 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3485
3486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3487 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3488 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving disableDeliverables Unexpected ERROR');
3489 END IF;
3490 ROLLBACK TO g_disable_del_GRP;
3491 x_return_status := G_RET_STS_UNEXP_ERROR ;
3492 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3493
3494 WHEN OTHERS THEN
3495 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3496 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving disableDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3497 END IF;
3498 ROLLBACK TO g_disable_del_GRP;
3499 x_return_status := G_RET_STS_UNEXP_ERROR ;
3500 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3501 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3502 END IF;
3503 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3504
3505 END; --disableDeliverables
3506
3507
3508
3509
3510 /**
3511 * Update internal contact on deliverables for given business document id and type.
3512 */
3513 PROCEDURE updateIntContactOnDeliverables (
3514 p_api_version IN NUMBER,
3515 p_init_msg_list IN VARCHAR2,
3516 p_commit IN Varchar2,
3517 p_bus_doc_id IN NUMBER,
3518 p_bus_doc_type IN VARCHAR2,
3519 p_bus_doc_version IN NUMBER,
3520 p_original_internal_contact_id IN NUMBER,
3521 p_new_internal_contact_id IN NUMBER,
3522 x_msg_data OUT NOCOPY VARCHAR2,
3523 x_msg_count OUT NOCOPY NUMBER,
3524 x_return_status OUT NOCOPY VARCHAR2)
3525 IS
3526 l_api_name CONSTANT VARCHAR2(30) := 'updateIntContactOnDeliverables';
3527 l_api_version CONSTANT VARCHAR2(30) := 1;
3528
3529 BEGIN
3530
3531 -- start procedure
3532 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3533 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
3534 END IF;
3535
3536 -- Standard Start of API savepoint
3537 SAVEPOINT g_update_del_GRP;
3538
3539 -- Standard call to check for call compatibility.
3540 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3542 END IF;
3543
3544 -- Initialize message list if p_init_msg_list is set to TRUE.
3545 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3546 FND_MSG_PUB.initialize;
3547 END IF;
3548
3549 -- Initialize API return status to success
3550 x_return_status := FND_API.G_RET_STS_SUCCESS;
3551
3552 --bug#4154567 update -99 version aswell
3553 UPDATE OKC_DELIVERABLES
3554 set internal_party_contact_id = p_new_internal_contact_id,
3555 last_updated_by= Fnd_Global.User_Id,
3556 last_update_date = sysdate,
3557 last_update_login=Fnd_Global.Login_Id
3558 WHERE business_document_id = p_bus_doc_id
3559 AND business_document_type = p_bus_doc_type
3560 AND business_document_version IN (-99, p_bus_doc_version)
3561 AND internal_party_contact_id = p_original_internal_contact_id;
3562
3563 IF FND_API.To_Boolean( p_commit ) THEN
3564 COMMIT WORK;
3565 END IF;
3566
3567 -- Standard call to get message count and if count is 1, get message info.
3568 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3569
3570 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3571 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables');
3572 END IF;
3573
3574 EXCEPTION
3575
3576 WHEN FND_API.G_EXC_ERROR THEN
3577 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3578 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateIntContactOnDeliverables Unexpected ERROR');
3579 END IF;
3580 ROLLBACK TO g_update_del_GRP;
3581 x_return_status := G_RET_STS_ERROR ;
3582 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3583
3584 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3585 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3586 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateIntContactOnDeliverables Unexpected ERROR');
3587 END IF;
3588 ROLLBACK TO g_update_del_GRP;
3589 x_return_status := G_RET_STS_UNEXP_ERROR ;
3590 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3591
3592 WHEN OTHERS THEN
3593 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3594 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3595 END IF;
3596 ROLLBACK TO g_update_del_GRP;
3597 x_return_status := G_RET_STS_UNEXP_ERROR ;
3598 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3599 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3600 END IF;
3601 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3602
3603 END; -- updateIntContactOnDeliverables
3604
3605
3606 /**
3607 * Update internal contact on deliverables for given set of business document id and type.
3608 */
3609 PROCEDURE updateIntContactOnDeliverables (
3610 p_api_version IN NUMBER,
3611 p_init_msg_list IN VARCHAR2,
3612 p_commit IN Varchar2,
3613 p_bus_docs_tbl IN BUSDOCS_TBL_TYPE,
3614 p_original_internal_contact_id IN NUMBER,
3615 p_new_internal_contact_id IN NUMBER,
3616 x_msg_data OUT NOCOPY VARCHAR2,
3617 x_msg_count OUT NOCOPY NUMBER,
3618 x_return_status OUT NOCOPY VARCHAR2)
3619 IS
3620 l_api_name CONSTANT VARCHAR2(30) := 'updateIntContactOnDeliverables';
3621 l_api_version CONSTANT VARCHAR2(30) := 1;
3622
3623 TYPE BusDocIdList IS TABLE OF NUMBER
3624 INDEX BY BINARY_INTEGER;
3625 TYPE BusDocTypeList IS TABLE OF VARCHAR2(30)
3626 INDEX BY BINARY_INTEGER;
3627 TYPE BusDocVersionList IS TABLE OF NUMBER
3628 INDEX BY BINARY_INTEGER;
3629
3630 l_bus_doc_ids BusDocIdList;
3631 l_bus_doc_types BusDocTypeList;
3632 l_bus_doc_versions BusDocVersionList;
3633
3634 BEGIN
3635
3636 -- start procedure
3637 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3638 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
3639 END IF;
3640
3641 -- Standard Start of API savepoint
3642 SAVEPOINT g_update2_del_GRP;
3643
3644 -- Standard call to check for call compatibility.
3645 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3647 END IF;
3648
3649 -- Initialize message list if p_init_msg_list is set to TRUE.
3650 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3651 FND_MSG_PUB.initialize;
3652 END IF;
3653
3654 -- Initialize API return status to success
3655 x_return_status := FND_API.G_RET_STS_SUCCESS;
3656
3657 IF p_bus_docs_tbl.count > 0 THEN
3658 FOR i IN p_bus_docs_tbl.FIRST..p_bus_docs_tbl.LAST LOOP
3659 l_bus_doc_ids(i) := p_bus_docs_tbl(i).bus_doc_id;
3660 l_bus_doc_types(i) := p_bus_docs_tbl(i).bus_doc_type;
3661 l_bus_doc_versions(i) := p_bus_docs_tbl(i).bus_doc_version;
3662 END LOOP;
3663 END IF;
3664 --- bulk update for deliverables actual due date
3665 FORALL j IN p_bus_docs_tbl.FIRST..p_bus_docs_tbl.LAST
3666 --bug#4154567 update -99 version aswell
3667 UPDATE OKC_DELIVERABLES
3668 set internal_party_contact_id = p_new_internal_contact_id,
3669 last_updated_by= Fnd_Global.User_Id,
3670 last_update_date = sysdate,
3671 last_update_login=Fnd_Global.Login_Id
3672 WHERE internal_party_contact_id = p_original_internal_contact_id
3673 AND business_document_id = l_bus_doc_ids(j)
3674 AND business_document_type = l_bus_doc_types(j)
3675 AND business_document_version IN (l_bus_doc_versions(j),-99);
3676
3677 IF FND_API.To_Boolean( p_commit ) THEN
3678 COMMIT WORK;
3679 END IF;
3680
3681 -- Standard call to get message count and if count is 1, get message info.
3682 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3683
3684 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3685 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables');
3686 END IF;
3687
3688 EXCEPTION
3689
3690 WHEN FND_API.G_EXC_ERROR THEN
3691 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3692 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateIntContactOnDeliverables Unexpected ERROR');
3693 END IF;
3694 ROLLBACK TO g_update2_del_GRP;
3695 x_return_status := G_RET_STS_ERROR ;
3696 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3697
3698
3699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3700 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3701 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateIntContactOnDeliverables Unexpected ERROR');
3702 END IF;
3703 ROLLBACK TO g_update2_del_GRP;
3704 x_return_status := G_RET_STS_UNEXP_ERROR ;
3705 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3706
3707 WHEN OTHERS THEN
3708 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3709 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateIntContactOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3710 END IF;
3711 ROLLBACK TO g_update2_del_GRP;
3712 x_return_status := G_RET_STS_UNEXP_ERROR ;
3713 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3714 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3715 END IF;
3716 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3717
3718 END; -- updateIntContactOnDeliverables
3719
3720 /**
3721 * This procedure updates external party id and site id
3722 * on deliverables for given draft version of business document.
3723 */
3724 PROCEDURE updateExtPartyOnDeliverables (
3725 p_api_version IN NUMBER,
3726 p_init_msg_list IN VARCHAR2,
3727 p_commit IN VARCHAR2,
3728 p_bus_doc_id IN NUMBER,
3729 p_bus_doc_type IN VARCHAR2,
3730 p_external_party_id IN NUMBER,
3731 p_external_party_site_id IN NUMBER,
3732 x_msg_data OUT NOCOPY VARCHAR2,
3733 x_msg_count OUT NOCOPY NUMBER,
3734 x_return_status OUT NOCOPY VARCHAR2)
3735 IS
3736
3737 l_api_name CONSTANT VARCHAR2(30) := 'updateExtPartyOnDeliverables';
3738 l_api_version CONSTANT VARCHAR2(30) := 1;
3739
3740 BEGIN
3741
3742 -- start procedure
3743 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3744 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
3745 END IF;
3746
3747 -- Standard Start of API savepoint
3748 SAVEPOINT g_update_del_GRP;
3749
3750 -- Standard call to check for call compatibility.
3751 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3753 END IF;
3754
3755 -- Initialize message list if p_init_msg_list is set to TRUE.
3756 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3757 FND_MSG_PUB.initialize;
3758 END IF;
3759
3760 -- Initialize API return status to success
3761 x_return_status := FND_API.G_RET_STS_SUCCESS;
3762
3763 UPDATE OKC_DELIVERABLES
3764 SET external_party_id = p_external_party_id,
3765 external_party_site_id = p_external_party_site_id,
3766 last_updated_by= Fnd_Global.User_Id,
3767 last_update_date = sysdate,
3768 last_update_login=Fnd_Global.Login_Id
3769 WHERE business_document_id = p_bus_doc_id
3770 AND business_document_type = p_bus_doc_type
3771 AND business_document_version = -99;
3772
3773
3774 IF FND_API.To_Boolean( p_commit ) THEN
3775 COMMIT WORK;
3776 END IF;
3777
3778 -- Standard call to get message count and if count is 1, get message info.
3779 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3780
3781 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3782 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables ');
3783 END IF;
3784
3785 EXCEPTION
3786
3787 WHEN FND_API.G_EXC_ERROR THEN
3788 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3789 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
3790 END IF;
3791 ROLLBACK TO g_update_del_GRP;
3792 x_return_status := G_RET_STS_ERROR ;
3793 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3794
3795 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3796 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3797 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
3798 END IF;
3799 ROLLBACK TO g_update_del_GRP;
3800 x_return_status := G_RET_STS_UNEXP_ERROR ;
3801 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3802
3803 WHEN OTHERS THEN
3804 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3805 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3806 END IF;
3807 ROLLBACK TO g_update_del_GRP;
3808 x_return_status := G_RET_STS_UNEXP_ERROR ;
3809 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3810 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3811 END IF;
3812 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3813
3814 END; -- updateExtPartyOnDeliverables
3815
3816 /**
3817 * This procedure updates external party id and site id
3818 * on deliverables for given version of business document.
3819 * Used for Supplier Merge
3820 * 15-JUN-2004 pnayani - bug#3691985 Supplier merge for Sourcing not working
3821 */
3822 PROCEDURE updateExtPartyOnDeliverables (
3823 p_api_version IN NUMBER,
3824 p_init_msg_list IN VARCHAR2,
3825 p_commit IN VARCHAR2,
3826 p_document_class IN VARCHAR2,
3827 p_from_external_party_id IN NUMBER,
3828 p_from_external_party_site_id IN NUMBER,
3829 p_to_external_party_id IN NUMBER,
3830 p_to_external_party_site_id IN NUMBER,
3831 x_msg_data OUT NOCOPY VARCHAR2,
3832 x_msg_count OUT NOCOPY NUMBER,
3833 x_return_status OUT NOCOPY VARCHAR2)
3834 IS
3835
3836 l_api_name CONSTANT VARCHAR2(30) := 'updateExtPartyOnDeliverables';
3837 l_api_version CONSTANT VARCHAR2(30) := 1;
3838
3839 CURSOR del_cur IS
3840 SELECT deliverable_id,external_party_site_id
3841 FROM okc_deliverables
3842 where external_party_id = p_from_external_party_id
3843 and business_document_type IN (select document_type
3844 from okc_bus_doc_types_b
3845 where document_type_class = p_document_class);
3846 del_rec del_cur%ROWTYPE;
3847
3848 TYPE delIdTabType IS TABLE OF NUMBER
3849 INDEX BY BINARY_INTEGER;
3850 j PLS_INTEGER;
3851
3852 delIdTab delIdTabType;
3853 delExtSiteIdTab delIdTabType;
3854
3855
3856 BEGIN
3857
3858 -- start procedure
3859 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3860 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
3861 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: p_from_external_party_id and p_from_external_party_site_id : '||p_from_external_party_site_id ||' and '||p_from_external_party_site_id);
3862 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: p_to_external_party_id and p_to_external_party_site_id : '||p_to_external_party_site_id ||' and '||p_to_external_party_site_id);
3863 END IF;
3864
3865 -- Standard Start of API savepoint
3866 SAVEPOINT g_update_del_GRP;
3867
3868 -- Standard call to check for call compatibility.
3869 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3871 END IF;
3872
3873 -- Initialize message list if p_init_msg_list is set to TRUE.
3874 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3875 FND_MSG_PUB.initialize;
3876 END IF;
3877
3878 -- Initialize API return status to success
3879 x_return_status := FND_API.G_RET_STS_SUCCESS;
3880
3881 -- initialize the table with 0 rows
3882 j := 0;
3883 delIdTab.delete;
3884 delExtSiteIdTab.delete;
3885 FOR del_rec IN del_cur LOOP
3886 IF p_from_external_party_site_id is not null THEN
3887 j := j+1;
3888 delIdTab(j) := del_rec.deliverable_id;
3889 delExtSiteIdTab(j) := p_to_external_party_site_id;
3890 ELSE
3891 j := j+1;
3892 delIdTab(j) := del_rec.deliverable_id;
3893 IF del_rec.external_party_site_id = -1 THEN
3894 delExtSiteIdTab(j) := -1;
3895 ELSE
3896 delExtSiteIdTab(j) := p_to_external_party_site_id;
3897 END IF;
3898 END IF;
3899 END LOOP;
3900
3901 IF delIdTab.COUNT <> 0 THEN
3902 -- bulk update deliverables external party
3903 FORALL i IN delIdTab.FIRST..delIdTab.LAST
3904 UPDATE okc_deliverables
3905 SET external_party_id = p_to_external_party_id,
3906 external_party_site_id = delExtSiteIdTab(i),
3907 last_updated_by= Fnd_Global.User_Id,
3908 last_update_date = sysdate,
3909 last_update_login=Fnd_Global.Login_Id
3910 WHERE deliverable_id = delIdTab(i);
3911 END IF;
3912 IF del_cur %ISOPEN THEN
3913 CLOSE del_cur ;
3914 END IF;
3915
3916
3917 IF FND_API.To_Boolean( p_commit ) THEN
3918 COMMIT WORK;
3919 END IF;
3920
3921
3922 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3923 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables ');
3924 END IF;
3925
3926 EXCEPTION
3927
3928 WHEN FND_API.G_EXC_ERROR THEN
3929 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3930 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
3931 END IF;
3932 -- close any open cursors
3933 IF del_cur %ISOPEN THEN
3934 CLOSE del_cur ;
3935 END IF;
3936 ROLLBACK TO g_update_del_GRP;
3937 x_return_status := G_RET_STS_ERROR ;
3938 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3939
3940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3941 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3942 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving updateExtPartyOnDeliverables Unexpected ERROR');
3943 END IF;
3944 ROLLBACK TO g_update_del_GRP;
3945 x_return_status := G_RET_STS_UNEXP_ERROR ;
3946 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3947
3948 WHEN OTHERS THEN
3949 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3950 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving updateExtPartyOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
3951 END IF;
3952 -- close any open cursors
3953 IF del_cur %ISOPEN THEN
3954 CLOSE del_cur ;
3955 END IF;
3956
3957 ROLLBACK TO g_update_del_GRP;
3958 x_return_status := G_RET_STS_UNEXP_ERROR ;
3959 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3960 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3961 END IF;
3962 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3963
3964 END;
3965
3966 /**
3967 * Create status history rows and send notifications for
3968 * deliverables on this business document whose status has changed since
3969 * last entry in status history.
3970 * Can be called by code that wants to post status changes that have not been reflected in the middle tier (e.g. Bid Submission)
3971 */
3972 PROCEDURE postDelStatusChanges (
3973 p_api_version IN NUMBER,
3974 p_init_msg_list IN VARCHAR2,
3975 p_commit IN Varchar2,
3976 p_bus_doc_id IN NUMBER,
3977 p_bus_doc_type IN VARCHAR2,
3978 p_bus_doc_version IN NUMBER,
3979 x_msg_data OUT NOCOPY VARCHAR2,
3980 x_msg_count OUT NOCOPY NUMBER,
3981 x_return_status OUT NOCOPY VARCHAR2)
3982 IS
3983 l_api_name CONSTANT VARCHAR2(30) := 'postDelStatusChanges';
3984 l_api_version CONSTANT VARCHAR2(30) := 1;
3985
3986
3987 k PLS_INTEGER := 0;
3988 l_msg_code VARCHAR2(30);
3989 l_key number;
3990
3991 --Cursor to select rows where status differs from most recent record in status history
3992 cursor del_cursor IS
3993 select
3994 deliverable.deliverable_status,
3995 deliverable.status_change_notes,
3996 deliverable.deliverable_id,
3997 deliverable.notify_completed_yn
3998 from okc_deliverables deliverable
3999 where
4000 deliverable.deliverable_status <> 'INACTIVE' and
4001 business_document_id = p_bus_doc_id and
4002 business_document_type = p_bus_doc_type and
4003 business_document_version = p_bus_doc_version and
4004 deliverable.deliverable_status <>
4005 (select status_history_inner.deliverable_status
4006 from okc_del_status_history status_history_inner
4007 where status_history_inner.deliverable_id = deliverable.deliverable_id
4008 and status_history_inner.deliverable_status <> 'INACTIVE'
4009 and status_history_inner.status_change_date = (select max(status_change_date)
4010 from okc_del_status_history
4011 where deliverable_id = deliverable.deliverable_id and
4012 deliverable_status <> 'INACTIVE'));
4013
4014
4015 del_rec del_cursor%ROWTYPE;
4016 delStsTab OKC_DELIVERABLE_PROCESS_PVT.delHistTabType;
4017
4018 cursor status_notes_cur IS
4019 select
4020 deliverable.deliverable_status,
4021 deliverable.status_change_notes,
4022 deliverable.deliverable_id,
4023 status_history.status_change_date
4024 from
4025 okc_deliverables deliverable,
4026 okc_del_status_history status_history
4027 where
4028 deliverable.deliverable_status <> 'INACTIVE' and
4029 business_document_id = p_bus_doc_id and
4030 business_document_type = p_bus_doc_type and
4031 business_document_version = p_bus_doc_version and
4032 status_history.deliverable_status = deliverable.deliverable_status and
4033 status_history.deliverable_id = deliverable.deliverable_id and
4034 (deliverable.status_change_notes <> status_history.status_change_notes OR status_history.status_change_notes IS NULL) and
4035 status_history.status_change_date = (select max(status_history_inner.status_change_date)
4036 from okc_del_status_history status_history_inner
4037 where status_history_inner.deliverable_id = deliverable.deliverable_id and status_history_inner.deliverable_status <> 'INACTIVE');
4038 status_notes_rec status_notes_cur%ROWTYPE;
4039
4040 --Acq Plan Message Cleanup
4041 l_resolved_msg_name VARCHAR2(30);
4042 l_resolved_token VARCHAR2(30);
4043
4044 BEGIN
4045
4046 -- start procedure
4047 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4048 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
4049 END IF;
4050
4051 -- Standard Start of API savepoint
4052 SAVEPOINT g_createHistory_GRP;
4053
4054 -- Standard call to check for call compatibility.
4055 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4057 END IF;
4058
4059 -- Initialize message list if p_init_msg_list is set to TRUE.
4060 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4061 FND_MSG_PUB.initialize;
4062 END IF;
4063
4064 --loop thru deliverables where only the notes have changed
4065 FOR status_notes_rec IN status_notes_cur LOOP
4066 update okc_del_status_history
4067 set status_change_notes = status_notes_rec.status_change_notes
4068 where status_change_date = status_notes_rec.status_change_date and
4069 deliverable_id = status_notes_rec.deliverable_id and
4070 deliverable_status = status_notes_rec.deliverable_status;
4071
4072 END LOOP;
4073
4074
4075 --loop through all modified deliverables
4076 FOR del_rec IN del_cursor LOOP
4077 k:=k+1;
4078
4079 --add to status history table
4080 delStsTab(k).deliverable_id := del_rec.deliverable_id;
4081 delStsTab(k).deliverable_status := del_rec.deliverable_status;
4082 delStsTab(k).status_changed_by := fnd_global.user_id;
4083 delStsTab(k).status_change_date := sysdate;
4084 delStsTab(k).status_change_notes := del_rec.status_change_notes;
4085 delStsTab(k).object_version_number := 1;
4086 delStsTab(k).created_by := fnd_global.user_id;
4087 delStsTab(k).creation_date := sysdate;
4088 delStsTab(k).last_update_date := sysdate;
4089 delStsTab(k).last_updated_by := fnd_global.user_id;
4090 delStsTab(k).last_update_login := fnd_global.login_Id;
4091
4092 --if necessary, send notification
4093 if ('Y' = del_rec.notify_completed_yn) then
4094 select OKC_WF_NOTIFY_S1.nextval into l_key from dual;
4095
4096
4097 --Acq Plan Message Cleanup
4098 /*
4099 if ('COMPLETED' = del_rec.deliverable_status) then
4100 l_msg_code := 'OKC_DEL_COMPLETE_NTF_SUBJECT';
4101 elsif ('CANCELLED' = del_rec.deliverable_status) then
4102 l_msg_code := 'OKC_DEL_CANCEL_NTF_SUBJECT';
4103 elsif ('OPEN' = del_rec.deliverable_status) then
4104 l_msg_code := 'OKC_DEL_REOPEN_NTF_SUBJECT';
4105 elsif ('FAILED_TO_PERFORM' = del_rec.deliverable_status) then
4106 l_msg_code := 'OKC_DEL_FAILED_NTF_SUBJECT';
4107 elsif ('REJECTED' = del_rec.deliverable_status ) then
4108 l_msg_code := 'OKC_DEL_REJECT_NTF_SUBJECT';
4109 else
4110 l_msg_code := 'OKC_DEL_SUBMIT_NTF_SUBJECT';
4111 end if;
4112 */
4113
4114
4115 if ('COMPLETED' = del_rec.deliverable_status) THEN
4116 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_COMPLETE_NTF_SUBJECT',p_bus_doc_type);
4117 elsif ('CANCELLED' = del_rec.deliverable_status) THEN
4118 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_CANCEL_NTF_SUBJECT',p_bus_doc_type);
4119 elsif ('OPEN' = del_rec.deliverable_status) THEN
4120 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_REOPEN_NTF_SUBJECT',p_bus_doc_type);
4121 elsif ('FAILED_TO_PERFORM' = del_rec.deliverable_status) THEN
4122 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_FAILED_NTF_SUBJECT',p_bus_doc_type);
4123 elsif ('REJECTED' = del_rec.deliverable_status ) THEN
4124 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_REJECT_NTF_SUBJECT',p_bus_doc_type);
4125 ELSE
4126 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_SUBMIT_NTF_SUBJECT',p_bus_doc_type);
4127 end if;
4128
4129 l_msg_code := l_resolved_msg_name;
4130
4131 begin
4132 --raise bus event to send notification
4133 WF_EVENT.raise2(p_event_name => 'oracle.apps.okc.deliverables.sendNotification',
4134 p_event_key => to_char(l_key),
4135 p_parameter_name1 => 'DELIVERABLE_ID',
4136 p_parameter_value1 => del_rec.deliverable_id,
4137 p_parameter_name2 => 'MSG_CODE',
4138 p_parameter_value2 => l_msg_Code); exception
4139 when others then
4140 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4141 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving postDelStatusChanges because of EXCEPTION in WF_event.raise2:'||sqlerrm);
4142
4143 END IF;
4144 raise;
4145 end;
4146
4147 end if;
4148
4149 END LOOP;
4150
4151 begin
4152 --bulk create status history records
4153 if k > 0 then
4154 OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history(p_api_version => 1.0,
4155 p_init_msg_list => FND_API.G_FALSE,
4156 p_del_st_hist_tab => delStsTab,
4157 x_msg_data => x_msg_data,
4158 x_msg_count => x_msg_count,
4159 x_return_status => x_return_status );
4160 end if;
4161
4162 exception
4163 when others then
4164 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4165 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving postDelStatusChanges because of EXCEPTION in OKC_DELIVERABLE_PROCESS_PVT.create_del_status_history:'||x_msg_data);
4166
4167 END IF;
4168 raise;
4169 end;
4170
4171 IF FND_API.To_Boolean( p_commit ) THEN
4172 COMMIT WORK;
4173 END IF;
4174
4175
4176 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4177 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving create history');
4178 END IF;
4179
4180 EXCEPTION
4181
4182 WHEN FND_API.G_EXC_ERROR THEN
4183 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4184 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving postDelStatusChanges Unexpected ERROR');
4185 END IF;
4186 ROLLBACK TO g_createHistory_GRP;
4187 x_return_status := G_RET_STS_ERROR ;
4188 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4189
4190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4191 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4192 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving postDelStatusChanges Unexpected ERROR');
4193 END IF;
4194 ROLLBACK TO g_createHistory_GRP;
4195 x_return_status := G_RET_STS_UNEXP_ERROR ;
4196 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4197
4198 WHEN OTHERS THEN
4199 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4200 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving postDelStatusChanges because of EXCEPTION: '||substr(sqlerrm,1,200));
4201 END IF;
4202 ROLLBACK TO g_createHistory_GRP;
4203 x_return_status := G_RET_STS_UNEXP_ERROR ;
4204 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4205 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4206 END IF;
4207 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4208
4209 END; -- postDelStatusChanges
4210
4211 /** 11.5.10+ code
4212 Function to check if any deliverables exist for a given external
4213 party for a given contract. Invoked by Repository ContractDetailsAMImpl.java API.
4214 Parameter Details:
4215 p_busdoc_id : Business document Id
4216 p_busdoc_type : Business document type
4217 p_external_party_id ID of internal or external party
4218 p_external_party_role Role of internal or external party
4219 (valid values INTERNAL,SUPPLIER, CUSTOMER, PARTNER)
4220 Returns N or Y, if there is unexpected error then it returns NULL.
4221 **/
4222
4223 FUNCTION deliverablesForExtPartyExist(
4224 p_api_version IN NUMBER,
4225 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4226 x_return_status OUT NOCOPY VARCHAR2,
4227 x_msg_data OUT NOCOPY VARCHAR2,
4228 x_msg_count OUT NOCOPY NUMBER,
4229
4230 p_busdoc_id IN NUMBER,
4231 p_busdoc_type IN VARCHAR2,
4232 p_external_party_id IN NUMBER,
4233 p_external_party_role IN VARCHAR2)
4234 RETURN VARCHAR2
4235 IS
4236 --bug#4170483 removed check for responsible party. and added -99 version check
4237 CURSOR del_cur IS
4238 SELECT 'X'
4239 FROM okc_deliverables
4240 WHERE business_document_type = p_busdoc_type
4241 AND business_document_id = p_busdoc_id
4242 AND business_document_version =-99
4243 AND UPPER(external_party_role) = UPPER(p_external_party_role)
4244 AND external_party_id = p_external_party_id;
4245
4246 del_rec del_cur%ROWTYPE;
4247 l_return_value VARCHAR2(1);
4248 l_api_name VARCHAR2(30) := 'deliverablesForExtPartyExist';
4249
4250
4251
4252 BEGIN
4253
4254 -- Initialize API return status to success
4255 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4256
4257
4258 l_return_value := 'N';
4259
4260 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4261 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000:Inside '||G_PKG_NAME ||'.'||l_api_name);
4262 END IF;
4263
4264 -- check if deliverables exist for the given party on a contract.
4265 OPEN del_cur;
4266 FETCH del_cur INTO del_rec;
4267 IF del_cur%FOUND THEN
4268
4269 l_return_value := 'Y';
4270
4271 END IF;
4272 CLOSE del_cur;
4273 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4274 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
4275 END IF;
4276
4277 RETURN(l_return_value);
4278
4279 EXCEPTION
4280 WHEN OTHERS THEN
4281 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4282 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving '||G_PKG_NAME ||'.'||l_api_name||' with unexpected error');
4283 END IF;
4284 IF del_cur %ISOPEN THEN
4285 CLOSE del_cur ;
4286 END IF;
4287
4288 x_return_status := G_RET_STS_UNEXP_ERROR ;
4289 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4290 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4291 END IF;
4292 FND_MSG_PUB.Count_And_Get(p_encoded=>'F'
4293 , p_count => x_msg_count
4294 , p_data => x_msg_data );
4295
4296 RETURN null;
4297
4298 END deliverablesForExtPartyExist;
4299
4300
4301 /** 11.5.10+ code
4302 Function to check if any maneagable deliverables exist for a given contract. Invoked by Repository ContractDetailsAMImpl.java.
4303 Parameter Details:
4304 p_busdoc_id : Business document Id
4305 p_busdoc_type : Business document type
4306 p_busdoc_version : Business document version
4307 Returns N or Y, if there is unexpected error then it returns NULL.
4308 **/
4309 FUNCTION check_manageable_deliverables(
4310 p_api_version IN NUMBER,
4311 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4312 x_return_status OUT NOCOPY VARCHAR2,
4313 x_msg_data OUT NOCOPY VARCHAR2,
4314 x_msg_count OUT NOCOPY NUMBER,
4315
4316 p_busdoc_id IN NUMBER,
4317 p_busdoc_type IN VARCHAR2,
4318 p_busdoc_version IN NUMBER)
4319 RETURN VARCHAR2
4320 IS
4321 CURSOR del_cur IS
4322 SELECT 'X'
4323 FROM okc_deliverables
4324 WHERE business_document_type = p_busdoc_type
4325 AND business_document_id = p_busdoc_id
4326 AND business_document_version = p_busdoc_version
4327 AND manage_yn = 'Y';
4328 del_rec del_cur%ROWTYPE;
4329 l_return_value VARCHAR2(1);
4330 l_api_name VARCHAR2(30) := 'check_manageable_deliverables';
4331
4332
4333
4334 BEGIN
4335
4336 -- Initialize API return status to success
4337 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4338
4339
4340 l_return_value := 'N';
4341
4342 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4343 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000:Inside '||G_PKG_NAME ||'.'||l_api_name);
4344 END IF;
4345
4346 -- check if maneagable deliverables exist for the given contract.
4347 OPEN del_cur;
4348 FETCH del_cur INTO del_rec;
4349 IF del_cur%FOUND THEN
4350
4351 l_return_value := 'Y';
4352 END IF;
4353 CLOSE del_cur;
4354 RETURN(l_return_value);
4355 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4356 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
4357 END IF;
4358
4359 EXCEPTION
4360 WHEN OTHERS THEN
4361 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4362 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving '||G_PKG_NAME ||'.'||l_api_name||' with unexpected error');
4363 END IF;
4364 IF del_cur %ISOPEN THEN
4365 CLOSE del_cur ;
4366 END IF;
4367
4368 x_return_status := G_RET_STS_UNEXP_ERROR ;
4369 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4370 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4371 END IF;
4372 FND_MSG_PUB.Count_And_Get(p_encoded=>'F'
4373 , p_count => x_msg_count
4374 , p_data => x_msg_data );
4375
4376 RETURN null;
4377
4378 END check_manageable_deliverables;
4379
4380
4381 /**
4382 * 11.5.10+ This procedure updates external party id and site id
4383 * on deliverables for given class of business document.
4384 * This API is for HZ party Merge process, it handles site merge
4385 * within a customer
4386 */
4387 PROCEDURE mergeExtPartyOnDeliverables (
4388 p_api_version IN NUMBER,
4389 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4390 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4391 p_document_class IN VARCHAR2,
4392 p_from_external_party_id IN NUMBER,
4393 p_from_external_party_site_id IN NUMBER,
4394 p_to_external_party_id IN NUMBER,
4395 p_to_external_party_site_id IN NUMBER,
4396 x_msg_data OUT NOCOPY VARCHAR2,
4397 x_msg_count OUT NOCOPY NUMBER,
4398 x_return_status OUT NOCOPY VARCHAR2)
4399
4400 IS
4401
4402 l_api_name CONSTANT VARCHAR2(30) := 'mergeExtPartyOnDeliverables';
4403 l_api_version CONSTANT VARCHAR2(30) := 1;
4404
4405 CURSOR del_cur IS
4406 SELECT deliverable_id,external_party_site_id
4407 FROM okc_deliverables
4408 where external_party_id = NVL(p_from_external_party_id,external_party_id)
4409 and external_party_role <> 'SUPPLIER_ORG'
4410 and business_document_type IN (select document_type
4411 from okc_bus_doc_types_b
4412 where document_type_class = p_document_class);
4413 del_rec del_cur%ROWTYPE;
4414
4415 TYPE delIdTabType IS TABLE OF NUMBER
4416 INDEX BY BINARY_INTEGER;
4417 j PLS_INTEGER;
4418
4419 delIdTab delIdTabType;
4420
4421
4422 BEGIN
4423
4424 -- start procedure
4425 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4426 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: Entered '||G_PKG_NAME ||'.'||l_api_name);
4427 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: p_from_external_party_id and p_from_external_party_site_id : '||p_from_external_party_site_id ||' and '||p_from_external_party_site_id);
4428 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'600: p_to_external_party_id and p_to_external_party_site_id : '||p_to_external_party_site_id ||' and '||p_to_external_party_site_id);
4429 END IF;
4430
4431 -- Standard Start of API savepoint
4432 SAVEPOINT g_update_del_GRP;
4433
4434 -- Standard call to check for call compatibility.
4435 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4437 END IF;
4438
4439 -- Initialize message list if p_init_msg_list is set to TRUE.
4440 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4441 FND_MSG_PUB.initialize;
4442 END IF;
4443
4444 -- Initialize API return status to success
4445 x_return_status := FND_API.G_RET_STS_SUCCESS;
4446
4447 -- initialize the table with 0 rows
4448 j := 0;
4449 delIdTab.delete;
4450 FOR del_rec IN del_cur LOOP
4451 j := j+1;
4452 delIdTab(j) := del_rec.deliverable_id;
4453 END LOOP;
4454
4455 IF delIdTab.COUNT <> 0 THEN
4456 -- bulk update deliverables external party
4457 FORALL i IN delIdTab.FIRST..delIdTab.LAST
4458 UPDATE okc_deliverables
4459 SET external_party_id = NVL(p_to_external_party_id,external_party_id),
4460 external_party_site_id = p_to_external_party_site_id,
4461 last_updated_by= Fnd_Global.User_Id,
4462 last_update_date = sysdate,
4463 last_update_login=Fnd_Global.Login_Id
4464 WHERE deliverable_id = delIdTab(i);
4465 END IF;
4466 IF del_cur %ISOPEN THEN
4467 CLOSE del_cur ;
4468 END IF;
4469
4470
4471 IF FND_API.To_Boolean( p_commit ) THEN
4472 COMMIT WORK;
4473 END IF;
4474
4475
4476 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4477 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving mergeExtPartyOnDeliverables ');
4478 END IF;
4479
4480 EXCEPTION
4481
4482 WHEN FND_API.G_EXC_ERROR THEN
4483 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4484 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving mergeExtPartyOnDeliverables Unexpected ERROR');
4485 END IF;
4486 -- close any open cursors
4487 IF del_cur %ISOPEN THEN
4488 CLOSE del_cur ;
4489 END IF;
4490 ROLLBACK TO g_update_del_GRP;
4491 x_return_status := G_RET_STS_ERROR ;
4492 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4493
4494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4495 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4496 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving mergeExtPartyOnDeliverables Unexpected ERROR');
4497 END IF;
4498 ROLLBACK TO g_update_del_GRP;
4499 x_return_status := G_RET_STS_UNEXP_ERROR ;
4500 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4501
4502 WHEN OTHERS THEN
4503 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4504 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving mergeExtPartyOnDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
4505 END IF;
4506 -- close any open cursors
4507 IF del_cur %ISOPEN THEN
4508 CLOSE del_cur ;
4509 END IF;
4510
4511 ROLLBACK TO g_update_del_GRP;
4512 x_return_status := G_RET_STS_UNEXP_ERROR ;
4513 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4514 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4515 END IF;
4516 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4517
4518 END; -- mergeExtPartyOnDeliverables
4519
4520 /**
4521 * Activate closeout deliverables for given business document id and type
4522 */
4523 PROCEDURE activateCloseoutDeliverables (
4524 p_api_version IN NUMBER,
4525 p_init_msg_list IN VARCHAR2,
4526 p_commit IN Varchar2,
4527 p_bus_doc_id IN NUMBER,
4528 p_bus_doc_type IN VARCHAR2,
4529 p_bus_doc_version IN NUMBER,
4530 p_event_code IN VARCHAR2,
4531 p_event_date IN DATE,
4532 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
4533 x_msg_data OUT NOCOPY VARCHAR2,
4534 x_msg_count OUT NOCOPY NUMBER,
4535 x_return_status OUT NOCOPY VARCHAR2)
4536 IS
4537 l_api_name CONSTANT VARCHAR2(30) := 'activateCloseoutDeliverables';
4538 l_api_version CONSTANT VARCHAR2(30) := 1;
4539
4540 BEGIN
4541
4542 -- start procedure
4543 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4544 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered '||G_PKG_NAME ||'.'||l_api_name);
4545 END IF;
4546
4547 -- Standard Start of API savepoint
4548 SAVEPOINT g_activatecloseout_del_GRP;
4549
4550 -- Standard call to check for call compatibility.
4551 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4553 END IF;
4554
4555 -- Initialize message list if p_init_msg_list is set to TRUE.
4556 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4557 FND_MSG_PUB.initialize;
4558 END IF;
4559
4560 -- Initialize API return status to success
4561 x_return_status := FND_API.G_RET_STS_SUCCESS;
4562
4563 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4564 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'200: Calling '||'resolveDeliverables');
4565 END IF;
4566
4567 --- resolve deliverables
4568 resolveDeliverables(
4569 p_api_version => l_api_version,
4570 p_init_msg_list => G_FALSE,
4571 p_commit => G_FALSE,
4572 p_bus_doc_id => p_bus_doc_id,
4573 p_bus_doc_type => p_bus_doc_type,
4574 p_bus_doc_version => p_bus_doc_version,
4575 p_event_code => p_event_code,
4576 p_event_date => p_event_date,
4577 p_bus_doc_date_events_tbl => p_bus_doc_date_events_tbl,
4578 x_msg_data => x_msg_data,
4579 x_msg_count => x_msg_count,
4580 x_return_status => x_return_status,
4581 p_cancel_flag => G_TRUE);
4582
4583 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4584 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'300: Finished resolveDeliverables' ||x_return_status);
4585 END IF;
4586
4587 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4589 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4590 RAISE FND_API.G_EXC_ERROR ;
4591 END IF;
4592
4593 -- Initialize API return status to success
4594 x_return_status := FND_API.G_RET_STS_SUCCESS;
4595
4596 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4597 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'400: Entered '||'OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status');
4598 END IF;
4599
4600 --- call change_deliverable_status, to change deliverable status from
4601 --- INACTIVE to 'OPEN'
4602 OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status(
4603 p_api_version => l_api_version,
4604 p_init_msg_list => G_FALSE,
4605 p_doc_id => p_bus_doc_id,
4606 p_doc_version => p_bus_doc_version,
4607 p_doc_type => p_bus_doc_type,
4608 p_cancel_yn => 'N',
4609 p_cancel_event_code => NULL,
4610 p_current_status => 'INACTIVE',
4611 p_new_status => 'OPEN',
4612 p_manage_yn => 'Y',
4613 x_msg_data => x_msg_data,
4614 x_msg_count => x_msg_count,
4615 x_return_status => x_return_status);
4616
4617 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4618 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'500: Finished OKC_DELIVERABLE_PROCESS_PVT.change_deliverable_status' ||x_return_status);
4619 END IF;
4620
4621 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4623 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4624 RAISE FND_API.G_EXC_ERROR ;
4625 END IF;
4626
4627 IF FND_API.To_Boolean( p_commit ) THEN
4628 COMMIT WORK;
4629 END IF;
4630
4631 -- Standard call to get message count and if count is 1, get message info.
4632 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4633
4634 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4635 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'1000: Leaving activateCloseoutDeliverables');
4636 END IF;
4637
4638 EXCEPTION
4639
4640 WHEN FND_API.G_EXC_ERROR THEN
4641 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4642 FND_LOG.STRING( FND_LOG.LEVEL_ERROR ,g_module||l_api_name,'800: Leaving activateCloseoutDeliverables Unexpected ERROR');
4643 END IF;
4644 ROLLBACK TO g_activatecloseout_del_GRP;
4645 x_return_status := G_RET_STS_ERROR ;
4646 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4647
4648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4649
4650 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4651 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'900: Leaving activateCloseoutDeliverables Unexpected ERROR');
4652 END IF;
4653 ROLLBACK TO g_activatecloseout_del_GRP;
4654 x_return_status := G_RET_STS_UNEXP_ERROR ;
4655 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4656
4657 WHEN OTHERS THEN
4658 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4659 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'1000: Leaving activateCloseoutDeliverables because of EXCEPTION: '||substr(sqlerrm,1,200));
4660 END IF;
4661 ROLLBACK TO g_activatecloseout_del_GRP;
4662 x_return_status := G_RET_STS_UNEXP_ERROR ;
4663 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4664 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
4665 END IF;
4666 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4667
4668 END; -- activateCloseoutDeliverables
4669
4670 /*-- Start of comments
4671 --API name : applyPaymentHolds
4672 --Type : Private.
4673 --Function : 1. This API returns TRUE if the Invoices for the concerned PO need to be held.False otherwise
4674 -- : (The check will only be made for a Standard PO, and only for 'CONTRACTUAL' deliverables)
4675 -- : 2. It runs through the pay_when_paid deliverables associated with the concerned PO.
4676 -- : It returns true based on which checkbox is checked and by comparing the sysdate with the actual due date.
4677 --Usage : This public API will be used only by the PO team to determine if invoices need to be held for the PO because of any deliverable
4678 --Pre-reqs : None.
4679 --Parameters :
4680 --IN : p_api_version IN NUMBER Required
4681 -- : p_init_msg_list IN VARCHAR2 Optional
4682 -- Default = FND_API.G_FALSE
4683 -- : p_bus_doc_id IN NUMBER Required
4684 -- Header ID of the Standard Purchase Order
4685 -- : p_bus_doc_version IN NUMBER Required
4686 -- Version number of the Standard Purchase Order
4687 --OUT : x_return_status OUT VARCHAR2
4688 -- : x_msg_count OUT NUMBER
4689 -- : x_msg_data OUT VARCHAR2(2000)
4690 --Note :
4691 -- End of comments */
4692 PROCEDURE applyPaymentHolds(
4693 p_api_version IN NUMBER,
4694 p_bus_doc_id IN NUMBER,
4695 p_bus_doc_version IN NUMBER,
4696 x_msg_data OUT NOCOPY VARCHAR2,
4697 x_msg_count OUT NOCOPY NUMBER,
4698 x_return_status OUT NOCOPY VARCHAR2)
4699 IS
4700
4701 CURSOR getDeliverables IS
4702 SELECT
4703 DELIVERABLE_ID,
4704 PAY_HOLD_PRIOR_DUE_DATE_YN,
4705 PAY_HOLD_PRIOR_DUE_DATE_VALUE,
4706 PAY_HOLD_PRIOR_DUE_DATE_UOM,
4707 PAY_HOLD_OVERDUE_YN,
4708 ACTUAL_DUE_DATE
4709 FROM okc_deliverables
4710 WHERE business_document_id = p_bus_doc_id
4711 AND business_document_version = p_bus_doc_version
4712 AND business_document_type = 'PO_STANDARD'
4713 AND deliverable_type = 'CONTRACTUAL'
4714 AND responsible_party = 'SUPPLIER_ORG'
4715 AND (PAY_HOLD_PRIOR_DUE_DATE_YN = 'Y' OR PAY_HOLD_OVERDUE_YN = 'Y')
4716 AND deliverable_status NOT IN ('COMPLETED','CANCELLED','INACTIVE');
4717
4718 l_return_status_true VARCHAR2(1) := G_TRUE;
4719 l_return_status_false VARCHAR2(1) := G_FALSE;
4720 l_effective_beforedue_date DATE;
4721 l_api_name CONSTANT VARCHAR2(50) := 'applyPaymentHolds';
4722
4723
4724 BEGIN
4725 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4726 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inside OKC_MANAGE_DELIVERABLES_GRP.payWhenPaidApplyHolds');
4727 END IF;
4728 x_return_status := l_return_status_false;
4729
4730
4731 FOR del_cur IN getDeliverables LOOP
4732
4733 IF del_cur.PAY_HOLD_PRIOR_DUE_DATE_YN = 'Y' THEN
4734
4735 IF UPPER(del_cur.PAY_HOLD_PRIOR_DUE_DATE_UOM) = 'DAY' THEN
4736 l_effective_beforedue_date := trunc(del_cur.actual_due_date)-del_cur.PAY_HOLD_PRIOR_DUE_DATE_VALUE;
4737 ELSIF UPPER(del_cur.PAY_HOLD_PRIOR_DUE_DATE_UOM) = 'WK' THEN
4738 l_effective_beforedue_date :=trunc(del_cur.actual_due_date)-7*del_cur.PAY_HOLD_PRIOR_DUE_DATE_VALUE;
4739 ELSIF UPPER(del_cur.PAY_HOLD_PRIOR_DUE_DATE_UOM) = 'MTH' THEN
4740 select add_months(del_cur.actual_due_date,-del_cur.PAY_HOLD_PRIOR_DUE_DATE_VALUE)
4741 INTO l_effective_beforedue_date from dual;
4742 END IF;
4743
4744 IF trunc(l_effective_beforedue_date) = trunc(sysdate) OR
4745 trunc(l_effective_beforedue_date) < trunc(sysdate) THEN
4746
4747 x_return_status := l_return_status_true;
4748 RETURN;
4749 END IF;
4750
4751 ELSIF del_cur.PAY_HOLD_OVERDUE_YN = 'Y' THEN
4752
4753 IF trunc(sysdate) > trunc(del_cur.actual_due_date) THEN
4754 x_return_status := l_return_status_true;
4755 RETURN;
4756 END IF;
4757
4758 END IF;
4759
4760 END LOOP;
4761
4762 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4763 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'leaving OKC_MANAGE_DELIVERABLES_GRP.payWhenPaidApplyHolds');
4764 END IF;
4765
4766 EXCEPTION
4767 WHEN OTHERS THEN
4768 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4769 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'leaving OKC_MANAGE_DELIVERABLES_GRP.payWhenPaidApplyHolds in OTHERS');
4770 END IF;
4771 IF getDeliverables %ISOPEN THEN
4772 CLOSE getDeliverables ;
4773 END IF;
4774 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4775 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
4776 END IF;
4777 x_return_status := G_RET_STS_ERROR;
4778 FND_MSG_PUB.Count_And_Get(
4779 p_count => x_msg_count,
4780 p_data => x_msg_data
4781 );
4782
4783
4784 END applyPaymentHolds;
4785
4786 /*-- Start of comments
4787 --Function name : checkDeliverablePayHold
4788 --Type : Public.
4789 --Function : This Function returns TRUE if the deliverable is holding invoices.False otherwise.
4790 --Usage : This public API will be used only by the Projects team to determine if a
4791 -- particular deliverable is holding invoices or not.
4792 --Pre-reqs : None.
4793 --Returns :TRUE or FALSE, if there is unexpected error then it returns NULL.
4794 -- End of comments */
4795
4796 FUNCTION checkDeliverablePayHold (
4797 p_deliverable_id IN NUMBER)
4798 RETURN VARCHAR2
4799 IS
4800
4801 CURSOR del_cur IS
4802 SELECT
4803 PAY_HOLD_PRIOR_DUE_DATE_YN,
4804 PAY_HOLD_PRIOR_DUE_DATE_VALUE,
4805 PAY_HOLD_PRIOR_DUE_DATE_UOM,
4806 PAY_HOLD_OVERDUE_YN,
4807 ACTUAL_DUE_DATE
4808 FROM okc_deliverables
4809 WHERE deliverable_id = p_deliverable_id
4810 AND business_document_type = 'PO_STANDARD'
4811 AND deliverable_type = 'CONTRACTUAL'
4812 AND responsible_party = 'SUPPLIER_ORG'
4813 AND (PAY_HOLD_PRIOR_DUE_DATE_YN = 'Y' OR PAY_HOLD_OVERDUE_YN = 'Y')
4814 AND deliverable_status NOT IN ('COMPLETED','CANCELLED','INACTIVE');
4815
4816 del_rec del_cur%ROWTYPE;
4817 l_return_value VARCHAR2(1);
4818 l_effective_beforedue_date DATE;
4819 l_api_name VARCHAR2(30) := 'checkDeliverablePayHold';
4820
4821 BEGIN
4822 l_return_value := G_FALSE;
4823
4824 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4825 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Inside '||G_PKG_NAME ||'.'||l_api_name);
4826 END IF;
4827
4828 OPEN del_cur;
4829 FETCH del_cur INTO del_rec;
4830
4831 IF del_cur%FOUND THEN
4832 IF del_rec.PAY_HOLD_PRIOR_DUE_DATE_YN = 'Y' THEN
4833
4834 IF UPPER(del_rec.PAY_HOLD_PRIOR_DUE_DATE_UOM) = 'DAY' THEN
4835 l_effective_beforedue_date := trunc(del_rec.actual_due_date)-del_rec.PAY_HOLD_PRIOR_DUE_DATE_VALUE;
4836 ELSIF UPPER(del_rec.PAY_HOLD_PRIOR_DUE_DATE_UOM) = 'WK' THEN
4837 l_effective_beforedue_date :=trunc(del_rec.actual_due_date)-7*del_rec.PAY_HOLD_PRIOR_DUE_DATE_VALUE;
4838 ELSIF UPPER(del_rec.PAY_HOLD_PRIOR_DUE_DATE_UOM) = 'MTH' THEN
4839 l_effective_beforedue_date:= add_months(del_rec.actual_due_date,-del_rec.PAY_HOLD_PRIOR_DUE_DATE_VALUE);
4840 END IF;
4841
4842 IF trunc(l_effective_beforedue_date) = trunc(sysdate) OR
4843 trunc(l_effective_beforedue_date) < trunc(sysdate) THEN
4844
4845 l_return_value := G_TRUE;
4846 END IF;
4847
4848 ELSIF del_rec.PAY_HOLD_OVERDUE_YN = 'Y' THEN
4849
4850 IF trunc(sysdate) > trunc(del_rec.actual_due_date) THEN
4851 l_return_value := G_TRUE;
4852 END IF;
4853
4854 END IF;
4855
4856 END IF;
4857
4858 CLOSE del_cur;
4859 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4860 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Leaving '||G_PKG_NAME ||'.'||l_api_name);
4861 END IF;
4862
4863
4864 RETURN(l_return_value);
4865
4866 EXCEPTION
4867 WHEN OTHERS THEN
4868 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4869 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,g_module||l_api_name,'leaving OKC_MANAGE_DELIVERABLES_GRP.checkDeliverablePayHold in OTHERS');
4870 END IF;
4871 IF del_cur%ISOPEN THEN
4872 CLOSE del_cur ;
4873 END IF;
4874 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4875 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
4876 END IF;
4877 RETURN null;
4878
4879 END checkDeliverablePayHold;
4880
4881
4882 ---------------------------------------------------------------------------
4883 -- END: Public Procedures and Functions
4884 ---------------------------------------------------------------------------
4885
4886
4887 END;
[Home] [Help]