1 PACKAGE OKC_DELIVERABLE_PROCESS_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKCVDPRS.pls 120.4.12020000.5 2013/04/12 14:21:12 serukull ship $ */
3
4 ---------------------------------------------------------------------------
5 -- TYPE Definitions
6 ---------------------------------------------------------------------------
7 TYPE delRecTabType IS TABLE OF okc_deliverables%ROWTYPE
8 INDEX BY BINARY_INTEGER;
9
10 TYPE delHistTabType IS TABLE OF okc_del_status_history%ROWTYPE
11 INDEX BY BINARY_INTEGER;
12
13 TYPE delIdTabType IS TABLE OF NUMBER
14 INDEX BY BINARY_INTEGER;
15
16 TYPE recurring_dates_tab_type IS TABLE OF DATE
17 INDEX BY BINARY_INTEGER;
18
19
20 TYPE del_cur_type IS REF CURSOR RETURN okc_deliverables%ROWTYPE;
21
22 --declaring record type for Bus Doc Type Info
23 TYPE BUSDOCTYPE_REC_TYPE IS RECORD(
24 document_type_class OKC_BUS_DOC_TYPES_B.DOCUMENT_TYPE_CLASS%TYPE
25 ,document_type_intent OKC_BUS_DOC_TYPES_B.INTENT%TYPE);
26
27
28 -- declaring record type for deliverable due date events
29 TYPE BUSDOCDATES_REC_TYPE IS RECORD (
30 event_code VARCHAR2(30),
31 event_date DATE
32 );
33 -- declaring table of records
34 TYPE BUSDOCDATES_TBL_TYPE IS TABLE OF BUSDOCDATES_REC_TYPE
35 INDEX BY BINARY_INTEGER;
36 l_doc_dates_tbl BUSDOCDATES_TBL_TYPE;
37 ---------------------------------------------------------------------------
38 -- Global VARIABLES
39 ---------------------------------------------------------------------------
40 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_DELIVERABLE_PROCESS_PVT';
41 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
42 G_ENTITY_NAME CONSTANT VARCHAR2(40) := 'OKC_DELIVERABLES';
43
44 ------------------------------------------------------------------------------
45 -- GLOBAL CONSTANTS
46 ------------------------------------------------------------------------------
47 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
48 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
49 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
50 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
51 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
52 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
53 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
54 G_DLV_QA_TYPE CONSTANT VARCHAR2(30) := 'DELIVERABLE';
55
56 G_QA_STS_SUCCESS CONSTANT varchar2(1) := 'S';
57 G_QA_STS_ERROR CONSTANT varchar2(1) := 'E';
58 G_QA_STS_WARNING CONSTANT varchar2(1) := 'W';
59
60 G_NORMAL_QA CONSTANT VARCHAR2(30) := 'NORMAL';
61 G_AMEND_QA CONSTANT VARCHAR2(30) := 'AMEND';
62 G_OKC CONSTANT VARCHAR2(3) := 'OKC';
63
64 -----------------------------------------------------------
65
66 ---------------------------------------------------------------------------
67 -- Procedures and Functions
68 ---------------------------------------------------------------------------
69 /*** This API is invoked from OKC_TERMS_PVT.COPY_TC.
70 This API copies deliverables from one busdoc to another
71 of same type. Used in Sourcing amendment process.
72 1. Verify if the source and target business documents
73 are of same Class. If Not, raise error.
74 2. The procedure will query deliverables from source
75 business document WHERE amendment_operation is NOT 'DELETE'.
76 (The reson of this check: In case of RFQ, amendments operation
77 and descriptions are maintained in the current copy,
78 hence all deletes are just soft deletes.
79 So the copy procedure should not copy deliverables which
80 were deleted from the RFQ during amendment).
81 3. Create instances of deliverables for p_target_doc_id
82 and p_target_doc_type, definition copied from
83 p_source_doc_id and p_source_doc_type.
84 Carry forward original deliverable id. Copy attachments.
85 Parameter Details:
86 p_source_doc_id : Source document Id
87 p_source_doc_type : Source document type
88 p_target_doc_id : Target document Id
89 p_target_doc_type : Target document Type
90 p_resetFixedDate_yn : This flag will be used to set null values
91 to deliverables having fixed dates or toset null values
92 (start date, end date) to deliverables with recurring dates
93 ***/
94
95 PROCEDURE copy_del_for_amendment (
96 p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
98 p_source_doc_id IN NUMBER,
99 p_source_doc_type IN VARCHAR2,
100 p_target_doc_id IN NUMBER,
101 p_target_doc_type IN VARCHAR2,
102 p_target_doc_number IN VARCHAR2,
103 p_reset_fixed_date_yn IN VARCHAR2,
104 x_msg_data OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2,
107 p_copy_del_attachments_yn IN VARCHAR2 default 'Y',
108 p_target_contractual_doctype IN Varchar2 default null);
109
110
111 -- Creates deliverable status history
112 PROCEDURE create_del_status_history (
113 p_api_version IN NUMBER,
114 p_init_msg_list IN VARCHAR2,
115 p_del_id IN NUMBER,
116 p_deliverable_status IN VARCHAR2,
117 x_msg_data OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_return_status OUT NOCOPY VARCHAR2);
120
121
122 /*** This API is invoked from OKC_TERMS_COPY_GRP.COPY_DOC.
123 Copies deliverables from source to target documents
124 Template to template, Template to Business document
125 Busdoc to busdoc of same or different types.
126 The procedure will query deliverables from source
127 business document WHERE amendment_operation is NOT 'DELETE'.
128 (The reason of this check: In case of RFQ, amendments operation
129 and descriptions are maintained in the current copy,
130 hence all deletes are just soft deletes.
131 So the copy procedure should not copy deliverables which
132 were deleted from the RFQ during amendment).
133 Parameter Details:
134 p_source_doc_id : Source document Id
135 p_source_doc_type : Source document type
136 p_target_doc_id : Target document Id
137 p_target_doc_type : Target document Type
138 p_target_doc_number : Target document Number
139 p_resetFixedDate_yn : This flag will be used to set null values
140 to deliverables having fixed dates or toset null values
141 (start date, end date) to deliverables with recurring dates
142 p_initializeStatus_yn : Flag indicating status to be reset to INACTIVE.
143 Valid vakues Y and N.
144 p_copy_del_attachments_yn : Flag indicates if attachments should be copied or not.
145 p_target_contractual_doctype : Specifies the target document for the
146 target business document. For example if RFQ is the target busdoc,
147 then the p_target_contractual_doctype could be PO_STANDARD or BPA etc.
148 p_target_response_doctype : Specifies the target response document for the
149 target business document. For example if RFQ is the target busdoc,
150 then the p_target_response_doctype could be RESPONSE etc.
151 p_internal_party_id : Internal party id on the target document
152 p_internal_contact_id: Internal party contact id on the target document
153 p_external_party_id : External party id on the target document
154 p_external_contact_id: External party contact id on the target document
155 Bug#4126344
156 p_carry_forward_ext_party_yn: If set to Y carry forward following attributes
157 from source doc
158 external_party_contact_id,
159 external_party_id,
160 external_party_site_id,
161 external_party_role
162 Else reset from parameters
163 p_carry_forward_int_contact_yn: If set to Y carry forward following attributes from source doc
164 internal_party_contact_id,
165 p_add_only_amend_deliverables : (Not used currently) -- in 12.2.1 we do not support concurrent updates to deliverables (conc mod)
166 but from 12.2.2 onwards we are supporting concurrent updates to deliverables
167 P_RETAIN_LOCK_DELIVERABLES_YN : used during conc mod sync process, this flag will be passed as 'Y'.
168 during sync process first delete doc is called, during delete process we do not delete the locked deliverables.
169 as a second step (of Sync process), we call copy, during which we should not copy the locked deliverables from award to mod.
170 ***/
171 PROCEDURE copy_deliverables (
172 p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
174 p_source_doc_id IN NUMBER,
175 p_source_doc_type IN VARCHAR2,
176 p_target_doc_id IN NUMBER,
177 p_target_doc_type IN VARCHAR2,
178 p_target_doc_number IN VARCHAR2,
179 p_target_contractual_doctype IN VARCHAR2 default null,
180 p_target_response_doctype IN VARCHAR2 default null,
181 p_initialize_status_yn IN VARCHAR2 default 'Y',
182 p_copy_del_attachments_yn IN VARCHAR2 default 'Y',
183 p_internal_party_id IN NUMBER default null,
184 p_reset_fixed_date_yn IN VARCHAR2 default 'N',
185 p_internal_contact_id IN NUMBER default null,
186 p_external_party_id IN NUMBER default null,
187 p_external_party_site_id IN NUMBER default null,
188 p_external_contact_id IN NUMBER default null,
189 x_msg_data OUT NOCOPY VARCHAR2,
190 x_msg_count OUT NOCOPY NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2,
192 p_carry_forward_ext_party_yn IN VARCHAR2 default 'N',
193 p_carry_forward_int_contact_yn IN VARCHAR2 default 'Y'
194 --,p_add_only_amend_deliverables IN VARCHAR2 := 'N'
195 ,P_RETAIN_LOCK_DELIVERABLES_YN IN VARCHAR2 DEFAULT 'N'
196 );
197
198
199
200 /***This API is invoked from OKC_TERMS_PVT.VERSION_DOC.
201 This API creates new set of deliverables for a given
202 version of document.
203 Parameter Details:
204 p_doc_id : Business document Id
205 p_doc_type : Business document type
206 p_doc_version : Business document version
207 ***/
208 PROCEDURE version_deliverables (
209 p_api_version IN NUMBER,
210 p_init_msg_list IN VARCHAR2,
211 p_doc_id IN NUMBER,
212 p_doc_version IN NUMBER,
213 p_doc_type IN VARCHAR2,
214 x_msg_data OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_return_status OUT NOCOPY VARCHAR2);
217
218 /** This API is called by OKC_MANAGE_DELIVERABLES_GRP.activate_deliverables
219 to sync status of the current signed document deliverables with the previous
220 signed document deliverables. Copy status history.
221 Parameter Details:
222 p_current_docid : Current signed Business document Id
223 p_current_doctype : Business document type
224 p_current_doc_version : Business document version
225 ***/
226 PROCEDURE sync_deliverables (
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2,
229 p_current_docid IN NUMBER,
230 p_current_doctype IN VARCHAR2,
231 p_current_doc_version IN NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2);
235
236
237
238
239 /***Invoked From: OKC_TERMS_PVT.VERSION_DOC
240 This API is invoked to clear amendment operation,
241 summary amend operation code and amendment notes
242 on deliverables for a given busdoc.
243 Parameter Details:
244 p_doc_id : Business document Id
245 p_doc_type : Business document type
246 bug#3618448 new param added
247 p_keep_summary: If set to 'N' all amendment attributes should be cleared.
248 If 'Y' then only amendment_operation will be cleared, default is 'N'.
249 ***/
250 PROCEDURE clear_amendment_operation (
251 p_api_version IN NUMBER,
252 p_init_msg_list IN VARCHAR2,
253 p_doc_id IN NUMBER,
254 p_doc_type IN VARCHAR2,
255 x_msg_data OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_return_status OUT NOCOPY VARCHAR2,
258 p_keep_summary IN VARCHAR2 DEFAULT 'N');
259
260 /***
261 This API is invoked from OKC_MANAGE_DELIVERABLES_GRP.activate_deliverables
262 and close_deliverables. It changes the status to a given status for a busdoc.
263 Creates status history for deliverable.
267 p_doc_version : Business document version
264 Parameter Details:
265 p_doc_id : Business document Id
266 p_doc_type : Business document type
268 p_cancel_yn : Indicates if the deliverables is updated because of
269 cancellation. Valid values Y/N
270 p_cancel_event_code : If the call is for cancellation then the cancellation
271 event code should be passed.
272 p_current_status : Current status of deliverable
273 p_new_status : New status of deliverable
274 p_manage_yn : Indicates if Manage flag should be turned on or not
275 ***/
276 PROCEDURE change_deliverable_status (
277 p_api_version IN NUMBER,
278 p_init_msg_list IN VARCHAR2,
279 p_doc_id IN NUMBER,
280 p_doc_version IN NUMBER,
281 p_doc_type IN VARCHAR2,
282 p_cancel_yn IN VARCHAR2,
283 p_cancel_event_code IN VARCHAR2 default null,
284 p_current_status IN VARCHAR2,
285 p_new_status IN VARCHAR2,
286 p_manage_yn IN VARCHAR2,
287 x_msg_data OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_return_status OUT NOCOPY VARCHAR2);
290
291
292 /***
293 This is the Concurrent Program scheduled to run every day
294 to send out notifications about overdue deliverables.
295 It internally calls API overdue_del_notifier
296 to check for overdue deliverabls and send out notifications
297 ***/
298 PROCEDURE overdue_deliverable_manager (
299 errbuf OUT NOCOPY VARCHAR2,
300 retcode OUT NOCOPY VARCHAR2);
301
302
303 /***
304 Invoked by Concurrent Program "overdue_deliverable_manager"
305 Picks all deliverables that are overdue
306 Invokes Deliverable Notifier to send out notifications
307 Update deliverables with overdue_notification_id
308 ***/
309 PROCEDURE overdue_del_notifier(
310 p_api_version IN NUMBER ,
311 p_init_msg_list IN VARCHAR2 ,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2);
315
316 /***
317 This is the Concurrent Program scheduled to run every day
318 to send out notifications about beforedue deliverables.
319 It internally calls API beforedue_del_notifier.
320 ***/
321 PROCEDURE beforedue_deliverable_manager (
322 errbuf OUT NOCOPY VARCHAR2,
323 retcode OUT NOCOPY VARCHAR2);
324
325 /***
326 Invoked by Concurrent Program "beforedue_deliverable_manager"
327 Picks all deliverables eligible for before due date notifications
328 Invokes Deliverable Notifier to send out notifications
329 Update deliverables with prior_notification_id
330 ***/
331 PROCEDURE beforedue_del_notifier(
332 p_api_version IN NUMBER ,
333 p_init_msg_list IN VARCHAR2 ,
334 x_return_status OUT NOCOPY VARCHAR2,
335 x_msg_count OUT NOCOPY NUMBER,
336 x_msg_data OUT NOCOPY VARCHAR2);
337
338 /***
339 This is the Concurrent Program scheduled to run every day
340 to send out escalated notifications about deliverables.
341 It internally calls API escalation_deliverable_notifier
342 ***/
343 PROCEDURE escalation_deliverable_manager (
344 errbuf OUT NOCOPY VARCHAR2,
345 retcode OUT NOCOPY VARCHAR2);
346
347 /***
348 Invoked by Concurrent Program "escalation_deliverable_manager"
349 Picks all deliverables eligible for escalation
350 Invokes Deliverable Notifier to send out notifications only to escalation assignee
351 Update deliverables with escalation_notification_id
352 ***/
353 PROCEDURE esc_del_notifier(
354 p_api_version IN NUMBER ,
355 p_init_msg_list IN VARCHAR2 ,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2);
359
360 /***
361 1. This API performs bulk delete of deliverables for business documents.
362 Invoked by OKC_TERMS_UTIL_GRP.purge_documents
363 2. For each doc_type and doc_id in p_doc_table,
364 find the deliverables that belong to the business document,
365 delete the deliverable, status history and attachments.
366 ***/
367 PROCEDURE purge_doc_deliverables (
368 p_api_version IN NUMBER,
369 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
370 p_doc_table IN OKC_TERMS_UTIL_GRP.doc_tbl_type,
371 x_msg_data OUT NOCOPY VARCHAR2,
372 x_msg_count OUT NOCOPY NUMBER,
373 x_return_status OUT NOCOPY VARCHAR2);
374
375 /***
376 1. This API is invoked by OKC_TERMS_UTIL_PVT.merge_template_working_copy
377 2. This API will select all deliverables for a given
378 business document type and version
379 3. Delete all deliverables along with the attachments and status history
380 Parameter Details:
381 4. If p_retain_lock_deliverables_yn IN VARCHAR2 := 'N' then all the deliverbales
382 with amendment_operation_code not null will not be deleted.
383 p_doc_id : Business document Id
384 p_doc_type : Business document type
385 p_doc_version : Business document version
386 p_retain_lock_deliverables_yn :
387
388 During conc mod sync process this flag will be passed as 'Y'
389 if this is 'Y', then we only delete the deliverables with amend_operation_code as null.
390 so that the modified terms on mod will be retained.
391 ***/
392 PROCEDURE delete_deliverables (
396 p_doc_type IN VARCHAR2,
393 p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2,
395 p_doc_id IN NUMBER,
397 p_doc_version IN NUMBER DEFAULT NULL,
398 x_msg_data OUT NOCOPY VARCHAR2,
399 x_msg_count OUT NOCOPY NUMBER,
400 x_return_status OUT NOCOPY VARCHAR2
401 ,p_retain_lock_deliverables_yn IN VARCHAR2 := 'N'
402 ,p_retain_exhibits IN VARCHAR2 := 'N');
403
404 /***
405 1. This API is invoked by OKC_TERMS_UTIL_PVT.merge_template_working_copy
406 2. This API will select all deliverables for a given source template id
407 3. Update all deliverables on the target template Id
408 set the business_document_id = source template id
409 ***/
410 PROCEDURE update_del_for_template_merge (
411 p_api_version IN NUMBER,
412 p_init_msg_list IN VARCHAR2,
413 p_base_template_id IN NUMBER,
414 p_working_template_id IN NUMBER,
415 x_msg_data OUT NOCOPY VARCHAR2,
416 x_msg_count OUT NOCOPY NUMBER,
417 x_return_status OUT NOCOPY VARCHAR2);
418
419 /**Returns the max date of the last_amendment_date for a busdoc
420 if the deliverables did not get amended then returns the
421 max last update date
422 Parameter Details:
423 p_busdoc_id : Business document Id
424 p_busdoc_type : Business document type
425 p_busdoc_version : Business document version
426 **/
427
428 FUNCTION get_last_amendment_date (
429 p_api_version IN NUMBER
430 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
431
432 ,x_return_status OUT NOCOPY VARCHAR2
433 ,x_msg_data OUT NOCOPY VARCHAR2
434 ,x_msg_count OUT NOCOPY NUMBER
435
436 ,p_busdoc_id IN NUMBER
437 ,p_busdoc_type IN VARCHAR2
438 ,p_busdoc_version IN NUMBER)
439 RETURN DATE;
440
441
442
443
444 /**
445 * This function Returns end date as actual date for given Start date,
446 * time unit(DAYS, WEEKS, MONTHS), duration and (B)efore/(A)fter.
447 */
448 FUNCTION get_actual_date(
449 p_start_date in date,
450 p_timeunit varchar2,
451 p_duration number,
452 p_before_after varchar2)
453 return date;
454
455 /**
456 * This API is called from Terms validate API to validate deliverables
457 * on a given business document.
458 * It will take in a table of records and populate the table if
459 * any error or warning is found on the deliverables on the bus doc.
460 */
461 PROCEDURE validate_deliverable_for_qa (
462 p_api_version IN NUMBER,
463 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
464 p_doc_type IN VARCHAR2,
465 p_doc_id IN NUMBER,
466 p_mode IN VARCHAR2,
467 p_bus_doc_date_events_tbl IN OKC_TERMS_QA_GRP.BUSDOCDATES_TBL_TYPE,
468 p_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
469 x_msg_data OUT NOCOPY VARCHAR2,
470 x_msg_count OUT NOCOPY NUMBER,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_qa_return_status IN OUT NOCOPY VARCHAR2);
473
474
475
476
477 /**
478 * Resolve recurring dates for given start date, end date and repeat
479 * frequency, day of month, day of week. Returns Table of dates resolved.
480 */
481 PROCEDURE get_recurring_dates (
482 p_api_version IN NUMBER,
483 p_init_msg_list IN VARCHAR2,
484 p_start_date in date,
485 p_end_date in date,
486 p_frequency in number,
487 p_recurr_day_of_month number,
488 p_recurr_day_of_week number,
489 x_recurr_dates OUT NOCOPY recurring_dates_tab_type ,
490 x_msg_data OUT NOCOPY VARCHAR2,
491 x_msg_count OUT NOCOPY NUMBER,
492 x_return_status OUT NOCOPY VARCHAR2);
493
494 PROCEDURE delete_del_status_hist_attach(
495 p_api_version IN NUMBER,
496 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
497 p_deliverable_id IN NUMBER,
498 p_bus_doc_id IN NUMBER,
499 p_bus_doc_version IN NUMBER,
500 p_bus_doc_type IN VARCHAR2,
501 x_msg_data OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_return_status OUT NOCOPY VARCHAR2);
504
505 FUNCTION get_ui_bus_doc_event_text(p_event_name IN VARCHAR2,
506 p_before_after IN VARCHAR2) RETURN VARCHAR2;
507
508 /**
509 Parameter Details:
510 p_doc_id : Business document Id
511 p_doc_type : Business document type
512 p_doc_version : Business document version
513 p_Conditional_Delete_Flag : Valid values are 'Y' and 'N'.
514 Pass 'N' if all Deliverable instances for a given Deliverable ID
515 should be deleted unconditionally.
516 Pass 'Y' to delete only those Deliverable instances of a given Deliverable
517 whose Status hasn't been updated by User as part of Manage Deliverables
518 p_delid_tab : Table of deliverable ids
519 **/
520 PROCEDURE delete_del_instances(
521 p_api_version IN NUMBER,
522 p_init_msg_list IN VARCHAR2,
523 p_doc_id IN NUMBER,
524 p_doc_type IN VARCHAR2,
525 p_doc_version IN NUMBER DEFAULT NULL,
526 p_Conditional_Delete_Flag IN VARCHAR2 DEFAULT 'N',
527 p_delid_tab IN OKC_DELIVERABLE_PROCESS_PVT.delIdTabType,
528 x_msg_data OUT NOCOPY VARCHAR2,
529 x_msg_count OUT NOCOPY NUMBER,
530 x_return_status OUT NOCOPY VARCHAR2);
531
532 /*** This procedure will disable or turn manage_yn to 'N'
533 for a given document type and version
534 Parameter Details:
535 p_doc_id : Business document Id
536 p_doc_type : Business document type
540 p_api_version IN NUMBER,
537 p_doc_version : Business document version
538 ***/
539 PROCEDURE disable_deliverables (
541 p_init_msg_list IN VARCHAR2,
542 p_doc_id IN NUMBER,
543 p_doc_version IN NUMBER,
544 p_doc_type IN VARCHAR2,
545 x_msg_data OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2);
548
549 /**Invoked From: OKC_TERMS_UTIL_GRP.get_document_deviations
550 1. This function returns type of deliverables existing on a Business Document
551 for a given version. Invoked by OKC_TERMS_UTIL_GRP.get_document_deviations.
552 2. Select all deliverables for the Business Document. If deliverables exist then
553 a. Check each deliverable type
554 i. If only contractual deliverables exist then return CONTRACTUAL
555 ii. If only internal deliverables exist then return INTERNAL
556 iii.If both contractual and internal deliverables exist then return
557 CONTRACTUAL_AND_INTERNAL
558 3. If no deliverables exist then return NONE
559 Parameter Details:
560 p_docid : Business document Id
561 p_doctype : Business document type
562 **/
563
564
565 FUNCTION deliverables_exist
566 (
567 p_api_version IN NUMBER,
568 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
569 x_return_status OUT NOCOPY VARCHAR2,
570 x_msg_data OUT NOCOPY VARCHAR2,
571 x_msg_count OUT NOCOPY NUMBER,
572 p_doctype IN VARCHAR2,
573 p_docid IN NUMBER
574 ) RETURN VARCHAR2;
575
576
577
578 /** This procedure deletes the deliverable,
579 status history and attachments **/
580 PROCEDURE delete_deliverable (
581 p_api_version IN NUMBER,
582 p_init_msg_list IN VARCHAR2,
583 p_del_id IN NUMBER,
584 x_msg_data OUT NOCOPY VARCHAR2,
585 x_msg_count OUT NOCOPY NUMBER,
586 x_return_status OUT NOCOPY VARCHAR2);
587
588
589
590 /*** This procedure will delete all deliverables that have been
591 created by applying a particular template on a busdoc.
592 It selects all deliverables which have original_deliverable_id
593 belonging to TEMPLATE and deletes them from the busdoc.
594 Parameter Details:
595 p_doc_id : Business document Id
596 p_doc_type : Business document type
597 ***/
598 PROCEDURE delete_template_deliverables (
599 p_api_version IN NUMBER,
600 p_init_msg_list IN VARCHAR2,
601 p_doc_id IN NUMBER,
602 p_doc_type IN VARCHAR2,
603 x_msg_data OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_return_status OUT NOCOPY VARCHAR2);
606
607 -- This function checks if the given deliverable has an attachment
608 FUNCTION attachment_exists(
609 p_entity_name IN VARCHAR2
610 ,p_pk1_value IN VARCHAR2
611 ) RETURN BOOLEAN;
612
613 /**Invoked From: OKC_TERMS_UTIL_GRP.is_deliverable_amended
614 1. This function returns type of deliverables amended on a Business Document
615 2. Select all deliverables definitions (-99 version) for the Business Document.
616 If deliverables exist then
617 a. Check each deliverable type
618 i. If only contractual deliverables amended then return CONTRACTUAL
619 ii. If only internal deliverables amended then return INTERNAL
620 iii.If both contractual and internal deliverables amended then return
621 CONTRACTUAL_AND_INTERNAL
622 iv.If both contractual and sourcing deliverables amended then return
623 CONTRACTUAL_AND_SOURCING
624 v.If both sourcing and internal deliverables amended then return
625 SOURCING_AND_INTERNAL
626 vi. If sourcing deliverables are amended then return SOURCING
627 vii. If all deliverables are amended then return ALL
628 3. If no deliverables amended then return NONE
629 **/
630
631
632 FUNCTION deliverables_amended (
633 p_api_version IN NUMBER,
634 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
635
636 x_return_status OUT NOCOPY VARCHAR2,
637 x_msg_data OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER,
639
640 p_doctype IN VARCHAR2,
641 p_docid IN NUMBER
642 ) RETURN VARCHAR2;
643
644
645 -- Bug#3272824 New API to get due date message text from newly seeded messages
646 FUNCTION getDueDateMsgText(
647 p_relative_st_date_event_id IN NUMBER default null
648 ,p_relative_end_date_event_id IN NUMBER default null
649 ,p_relative_st_date_duration IN NUMBER default null
650 ,p_relative_end_date_duration IN NUMBER default null
651 ,p_repeating_day_of_week IN VARCHAR2 default null
652 ,p_repeating_day_of_month IN VARCHAR2 default null
653 ,p_repeating_duration IN NUMBER default null
654 ,p_print_due_date_msg_name IN VARCHAR2 default null
655 ,p_fixed_start_date IN DATE default null
656 ,p_fixed_end_date IN DATE default null
657 )
658 RETURN VARCHAR2;
659
660
661 /***
662 07-APR-2004 pnayani -- bug#3524864 added copy_response_deliverables API
663 This API is invoked from OKC_TERMS_COPY_GRP.COPY_RESPONSE_DOC.
664 Initially coded to support proxy bidding functionality in Sourcing.
665 Copies deliverables from source response doc to target response documents (bid to bid)
669 p_source_doc_id : Source document Id
666 The procedure will query deliverables from source response
667 business document.
668 Parameter Details:
670 p_source_doc_type : Source document type
671 p_target_doc_id : Target document Id
672 p_target_doc_type : Target document Type
673 p_target_doc_number : Target document Number
674 ***/
675
676 PROCEDURE copy_response_deliverables (
677 p_api_version IN NUMBER,
678 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
679 p_source_doc_id IN NUMBER,
680 p_source_doc_type IN VARCHAR2,
681 p_target_doc_id IN NUMBER,
682 p_target_doc_type IN VARCHAR2,
683 p_target_doc_number IN VARCHAR2,
684 x_msg_data OUT NOCOPY VARCHAR2,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_return_status OUT NOCOPY VARCHAR2) ;
687
688
689 -- Creates status history for a given deliverable status history table
690 PROCEDURE create_del_status_history(
691 p_api_version IN NUMBER,
692 p_init_msg_list IN VARCHAR2,
693 p_del_st_hist_tab IN delHistTabType,
694 x_msg_data OUT NOCOPY VARCHAR2,
695 x_msg_count OUT NOCOPY NUMBER,
696 x_return_status OUT NOCOPY VARCHAR2);
697
698
699 /**
700 3635916 MODIFYING DELIVERABLE DOES NOT REMOVE RELATED CLAUSE
701 FROM AMENDMENT SUMMARY
702 This API will be invoked by
703 OKC_TERMS_UTIL_PVT.deliverable_amendment_exists()
704 Parameter Details:
705 p_bus_doc_id : document Id
706 p_bus_doc_type : document type
707 p_variable_code: deliverable variable code
708 return value is Y or N based on the matching of deliverable
709 to the variable.
710 ***/
711 FUNCTION deliverable_amendment_exists (
712 p_api_version IN NUMBER,
713 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
714 p_bus_doc_type IN VARCHAR2,
715 p_bus_doc_id IN NUMBER,
716 p_variable_code IN VARCHAR2,
717 x_msg_data OUT NOCOPY VARCHAR2,
718 x_msg_count OUT NOCOPY NUMBER,
719 x_return_status OUT NOCOPY VARCHAR2)
720 RETURN VARCHAR2;
721
722
723 /***
724 Function get_party_name
725 This API will be invoked by the Create/Update/ViewOnly Deliverable pages to display the
726 name for an External Party. The External Party could be VENDOR_ID from PO_VENDORS or
727 PARTY_ID from HZ_PARTIES
728 Parameter Details:
729 p_external_party_id: Unique Identifier from PO_VENDORS or HZ_PARTIES
730 p_external_party_role: Resp_Party_Code from OKC_RESP_PARTIES
731 ***/
732 FUNCTION get_party_name(
733 p_external_party_id IN NUMBER,
734 p_external_party_role IN VARCHAR2)
735 RETURN VARCHAR2;
736
737
738 -- bug#4075168 New API for Template Revision
739 PROCEDURE CopyDelForTemplateRevision(
740 p_api_version IN NUMBER,
741 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
742 p_source_doc_id IN NUMBER,
743 p_source_doc_type IN VARCHAR2,
744 p_target_doc_id IN NUMBER,
745 p_target_doc_type IN VARCHAR2,
746 p_target_doc_number IN VARCHAR2,
747 p_copy_del_attachments_yn IN VARCHAR2 default 'Y',
748 x_msg_data OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_return_status OUT NOCOPY VARCHAR2);
751
752
753 -- Start of comments
754 --API name : deleteDeliverables
755 --Type : Private.
756 --Function : 1. Deletes deliverables of the current version of the bus doc (-99).
757 -- : 2. If p_revert_dels = 'Y", re-creating deliverables with -99 version
758 -- : from the deliverable definitions of the previous bus doc version.
759 --Usage : This API is called from Repository while deleting a contract.
760 --Pre-reqs : None.
761 --Parameters :
762 --IN : p_api_version IN NUMBER Required
763 -- : p_init_msg_list IN VARCHAR2 Optional
764 -- Default = FND_API.G_FALSE
765 -- : p_commit IN VARCHAR2 Optional
766 -- Default = FND_API.G_FALSE
767 -- : p_bus_doc_id IN NUMBER Required
768 -- Contract ID of the contract to be deleted
769 -- : p_bus_doc_type IN VARCHAR2 Required
770 -- Type of the contract to be deleted
771 -- : p_bus_doc_version IN NUMBER Required
772 -- Version number of the contract to be deleted
773 -- : p_prev_del_active IN VARCHAR2 Optional
774 -- Flag which tells whether deliverables of the previous business
775 -- document version are activated or not
776 -- Default = 'N'
777 -- : p_revert_dels IN VARCHAR2 Optional
778 -- Flag which tells whether to recreate the -99 deliverables from
779 -- the previous document version's deliverables. This will be "N" if
780 -- the first version of the business document is being deleted
781 -- Default = 'N'
782 --OUT : x_return_status OUT VARCHAR2(1)
783 -- : x_msg_count OUT NUMBER
784 -- : x_msg_data OUT VARCHAR2(2000)
785 --Note :
786 -- End of comments
787 PROCEDURE deleteDeliverables(
788 p_api_version IN NUMBER,
789 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
790 p_commit IN VARCHAR2:=FND_API.G_FALSE,
791 p_bus_doc_id IN NUMBER,
792 p_bus_doc_type IN VARCHAR2,
796 x_msg_data OUT NOCOPY VARCHAR2,
793 p_bus_doc_version IN NUMBER,
794 p_prev_del_active IN VARCHAR2 := 'N',
795 p_revert_dels IN VARCHAR2 := 'N',
797 x_msg_count OUT NOCOPY NUMBER,
798 x_return_status OUT NOCOPY VARCHAR2);
799
800 /** This procedure deletes the deliverable
801 status history and attachments **/
802 PROCEDURE delete_del_attach_and_history (
803 p_api_version IN NUMBER,
804 p_init_msg_list IN VARCHAR2,
805 p_del_id IN NUMBER,
806 x_msg_data OUT NOCOPY VARCHAR2,
807 x_msg_count OUT NOCOPY NUMBER,
808 x_return_status OUT NOCOPY VARCHAR2);
809
810 /*
811 params: p_source_id: source deliverable id from which uda data is copied.
812 p_target_id: target deliverable id to which uda data is copied to.
813 */
814 PROCEDURE copy_deliverable_udas(
815 p_source_id IN NUMBER,
816 p_target_id IN NUMBER,
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_msg_count OUT NOCOPY NUMBER,
819 x_msg_data OUT NOCOPY VARCHAR2,
820 x_errorcode OUT NOCOPY NUMBER
821 );
822
823 FUNCTION getDaysDueDateMsgText(
824 p_relative_st_date_event_id NUMBER
825 ,p_relative_end_date_event_id NUMBER
826 ,p_relative_st_date_duration NUMBER --&START_NUM
827 ,p_relative_st_date_uom VARCHAR2 -- &START_D_W_M
828 ,p_relative_end_date_uom VARCHAR2 -- &END_D_W_M
829 ,p_relative_end_date_duration NUMBER -- &END_NUM
830 ,p_repeating_duration NUMBER -- &NUM_DAYS
831 ,p_print_due_date_msg_name VARCHAR2
832 ,p_fixed_start_date DATE -- &FIXED_START_DATE
833 ,p_fixed_end_date DATE -- &FIXED_END_DATE
834 ) RETURN VARCHAR2;
835
836
837 END OKC_DELIVERABLE_PROCESS_PVT;
[Home] [Help]