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