1 PACKAGE OKC_MANAGE_DELIVERABLES_GRP AS
2 /* $Header: OKCGMDLS.pls 120.0.12010000.3 2008/11/14 13:14:29 strivedi ship $ */
3
4 ---------------------------------------------------------------------------
5 -- TYPE Definitions
6 ---------------------------------------------------------------------------
7 -- declaring record type for deliverable due date events
8 TYPE BUSDOCDATES_REC_TYPE IS RECORD (
9 event_code VARCHAR2(30),
10 event_date DATE
11 );
12 -- declaring table of records
13 TYPE BUSDOCDATES_TBL_TYPE IS TABLE OF BUSDOCDATES_REC_TYPE
14 INDEX BY BINARY_INTEGER;
15
16 -- declaring record type for business docs
17 TYPE BUSDOCS_REC_TYPE IS RECORD (
18 bus_doc_id NUMBER,
19 bus_doc_version NUMBER,
20 bus_doc_type VARCHAR2(30)
21 );
22 -- declaring table of records
23 TYPE BUSDOCS_TBL_TYPE IS TABLE OF BUSDOCS_REC_TYPE
24 INDEX BY BINARY_INTEGER;
25
26 ---------------------------------------------------------------------------
27 -- Procedures and Functions
28 ---------------------------------------------------------------------------
29
30 /**
31 * This procedure resolves and activates deliverables for a given business
32 * document version.
33 * @param IN p_bus_doc_id target business document id
34 * @param IN p_bus_doc_type target business document type
35 * @param IN p_bus_doc_version target business document version, documents with no valid version number
36 * should pass -99
37 * @param IN p_event_code action based business document event (e.g. PO Signed, PO Cancelled, Bid Received etc.)
38 * @param IN p_event_date action based business document event date
39 * @param IN p_sync_flag if this flag is true, syncing of target deliverables with the previously managed deliverables takes place.
40 * @param IN p_bus_doc_date_events_tbl table type for business document date based events
41 */
42 PROCEDURE activateDeliverables (
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
45 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
46 p_bus_doc_id IN NUMBER,
47 p_bus_doc_type IN VARCHAR2,
48 p_bus_doc_version IN NUMBER,
49 p_event_code IN VARCHAR2,
50 p_event_date IN DATE,
51 p_sync_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
52 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2);
56
57 /**
58 * This procedure cancel deliverables on a given business document version and
59 * implicitly activate deliverables that are based on Cancel Event, passed as
60 * input parameter 'p_event_code'.
61 * @param IN p_bus_doc_id target business document id
62 * @param IN p_bus_doc_type target business document type
63 * @param IN p_bus_doc_version target business document version, documents with no valid version number
64 * should pass -99
65 * @param IN p_event_code action based business document event (e.g. PO Cancelled)
66 * @param IN p_event_date action based business document event date
67 * @param IN p_bus_doc_date_events_tbl table type for business document date based events
68 */
69 PROCEDURE cancelDeliverables (
70 p_api_version IN NUMBER,
71 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
72 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
73 p_bus_doc_id IN NUMBER,
74 p_bus_doc_type IN VARCHAR2,
75 p_bus_doc_version IN NUMBER,
76 p_event_code IN VARCHAR2,
77 p_event_date IN DATE,
78 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2);
82
83 /**
84 * This is the simple cancel deliverables procedure where deliverables are simply cancelled
85 * on the given business document version.
86 * @param IN p_bus_doc_id target business document id
87 * @param IN p_bus_doc_type target business document type
88 * @param IN p_bus_doc_version target business document version, documents with no valid version number
89 * should pass -99
90 */
91 PROCEDURE cancelDeliverables (
92 p_api_version IN NUMBER,
93 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
94 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
95 p_bus_doc_id IN NUMBER,
96 p_bus_doc_type IN VARCHAR2,
97 p_bus_doc_version IN NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2);
101
102 /**
103 * This procedure update deliverables for given business document version. The update
104 * in real sense implies, re-resolving deliverables due dates which are based
105 * on business document dates, if any of the dates are changed and keep the status
106 * of deliverables as is.
107 * @param IN p_bus_doc_id target business document id
108 * @param IN p_bus_doc_type target business document type
109 * @param IN p_bus_doc_version target business document version, documents with no valid version number
110 * should pass -99
111 * @param IN p_bus_doc_date_events_tbl table type for business document date based events, should contain only those date based events where corresponding dates are changed
112 */
113 PROCEDURE updateDeliverables (
114 p_api_version IN NUMBER,
115 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
116 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
117 p_bus_doc_id IN NUMBER,
118 p_bus_doc_type IN VARCHAR2,
119 p_bus_doc_version IN NUMBER,
120 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_return_status OUT NOCOPY VARCHAR2);
124
125 /**
126 * This procedure updates old buyer contact on deliverables for given business document version
127 * with the new buyer contact
128 * @param IN p_bus_doc_id target business document id
129 * @param IN p_bus_doc_type target business document type
130 * @param IN p_bus_doc_version target business document version, documents with no valid version number
131 * should pass -99
132 * @param IN p_original_buyer_id old buyer id
133 * @param IN p_new_buyer_id new buyer id
134 * @deprecated this API is deprecated. Use updateIntContactOnDeliverables()
135 */
136 PROCEDURE updateBuyerOnDeliverables (
137 p_api_version IN NUMBER,
138 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
139 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
140 p_bus_doc_id IN NUMBER,
141 p_bus_doc_type IN VARCHAR2,
142 p_bus_doc_version IN NUMBER,
143 p_original_buyer_id IN NUMBER,
144 p_new_buyer_id IN NUMBER,
145 x_msg_data OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_return_status OUT NOCOPY VARCHAR2);
148
149 /**
150 * This procedure updates buyer contact on deliverables with new buyer contact, for bulk
151 * business documents passed as table of records.
152 * @param IN p_bus_docs_tbl table type for business documents to be updated for buyer contact on deliverables
153 * @param IN p_original_buyer_id old buyer id
154 * @param IN p_new_buyer_id new buyer id
155 * @deprecated this API is deprecated. Use updateIntContactOnDeliverables()
156 */
157 PROCEDURE updateBuyerOnDeliverables (
158 p_api_version IN NUMBER,
159 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
160 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
161 p_bus_docs_tbl IN BUSDOCS_TBL_TYPE,
162 p_original_buyer_id IN NUMBER,
163 p_new_buyer_id IN NUMBER,
164 x_msg_data OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_return_status OUT NOCOPY VARCHAR2);
167
168 /**
169 * This procedure resolve deliverables due dates.
170 * @param IN p_bus_doc_id target business document id
171 * @param IN p_bus_doc_type target business document type
172 * @param IN p_bus_doc_version target business document version, documents with no valid version number
173 * should pass -99
174 * @param IN p_event_code action based business document event (e.g. PO Signed, PO Cancelled, Bid Received etc.)
175 * @param IN p_event_date action based business document event date
176 * @param IN p_bus_doc_date_events_tbl table type for business document date based events
177 * @param IN p_sync_flag if this flag is true, syncing of target deliverables with the previously managed deliverables takes place.
178 * @param IN p_sync_recurr_instances_flag if this flag i true, recurring instances are re-resolved based on new end date and previous instances are syned up as is.
179 * if this flag is false, recurring instances are generated from the scratch (this happens when the API is called directly). If this API is called from activateDeliverables() the value is defaulted to TRUE.
180 */
181 PROCEDURE resolveDeliverables (
182 p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
184 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
185 p_bus_doc_id IN NUMBER,
186 p_bus_doc_type IN VARCHAR2,
187 p_bus_doc_version IN NUMBER,
188 p_event_code IN VARCHAR2,
189 p_event_date IN DATE,
190 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
191 x_msg_data OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_return_status OUT NOCOPY VARCHAR2,
194 p_sync_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
195 p_sync_recurr_instances_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
196 p_cancel_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE);
197
198 /**
199 * This procedure enable notifications on deliverables for given business document
200 * version.
201 * @param IN p_bus_doc_id target business document id
202 * @param IN p_bus_doc_type target business document type
203 * @param IN p_bus_doc_version target business document version, documents with no valid version number
204 * should pass -99
205 */
206 PROCEDURE enableNotifications (
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
209 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
210 p_bus_doc_id IN NUMBER,
211 p_bus_doc_type IN VARCHAR2,
212 p_bus_doc_version IN NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2,
214 x_msg_count OUT NOCOPY NUMBER,
215 x_return_status OUT NOCOPY VARCHAR2);
216
217
218 /**
219 * This procedure disables execution of deliverables for a given business document
220 * version.
221 * @param IN p_bus_doc_id target business document id
222 * @param IN p_bus_doc_type target business document type
223 * @param IN p_bus_doc_version target business document version, documents with no valid version number
224 * should pass -99
225 */
226 PROCEDURE disableDeliverables (
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
229 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
230 p_bus_doc_id IN NUMBER,
231 p_bus_doc_type IN VARCHAR2,
232 p_bus_doc_version IN NUMBER, -- -99 for Sourcing.
233 x_msg_data OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_return_status OUT NOCOPY VARCHAR2);
236
237 /**
238 * This procedure updates internal contact on deliverables for
239 * given business document version with new internal contact id.
240 * @param IN p_bus_doc_id target business document id
241 * @param IN p_bus_doc_type target business document type
242 * @param IN p_bus_doc_version target business document version, documents with no valid version number
243 * should pass -99
244 * @param p_original_internal_contact_id old internal contact
245 * @param p_new_internal_contact_id new internal contact
246 */
247 PROCEDURE updateIntContactOnDeliverables (
248 p_api_version IN NUMBER,
249 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
250 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
251 p_bus_doc_id IN NUMBER,
252 p_bus_doc_type IN VARCHAR2,
253 p_bus_doc_version IN NUMBER,
254 p_original_internal_contact_id IN NUMBER,
255 p_new_internal_contact_id IN NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257 x_msg_count OUT NOCOPY NUMBER,
258 x_return_status OUT NOCOPY VARCHAR2);
259
260 /**
261 * This procedure updates internal contact on deliverables for given set of
262 * business documents.
263 * @param IN p_bus_docs_tbl table type for business documents to be updated for internal contact on deliverables
264 * @param p_original_internal_contact_id old internal contact
265 * @param p_new_internal_contact_id new internal contact
266 */
267 PROCEDURE updateIntContactOnDeliverables (
268 p_api_version IN NUMBER,
269 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
270 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
271 p_bus_docs_tbl IN BUSDOCS_TBL_TYPE,
272 p_original_internal_contact_id IN NUMBER,
276 x_return_status OUT NOCOPY VARCHAR2);
273 p_new_internal_contact_id IN NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2,
275 x_msg_count OUT NOCOPY NUMBER,
277
278 /**
279 * This procedure updates external party id and site id
280 * on deliverables for given draft version of business document.
281 */
282 PROCEDURE updateExtPartyOnDeliverables (
283 p_api_version IN NUMBER,
284 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
285 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
286 p_bus_doc_id IN NUMBER,
287 p_bus_doc_type IN VARCHAR2,
288 p_external_party_id IN NUMBER,
289 p_external_party_site_id IN NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_return_status OUT NOCOPY VARCHAR2);
293
294 /**
295 * This procedure updates external party id and site id
296 * on deliverables for given class of business document.
297 */
298 PROCEDURE updateExtPartyOnDeliverables (
299 p_api_version IN NUMBER,
300 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
301 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
302 p_document_class IN VARCHAR2,
303 p_from_external_party_id IN NUMBER,
304 p_from_external_party_site_id IN NUMBER,
305 p_to_external_party_id IN NUMBER,
306 p_to_external_party_site_id IN NUMBER,
307 x_msg_data OUT NOCOPY VARCHAR2,
308 x_msg_count OUT NOCOPY NUMBER,
309 x_return_status OUT NOCOPY VARCHAR2);
310
311 /**
312 * This API performs post actions when deliverables status change
313 * happens in the middle-tier
314 */
315 PROCEDURE postDelStatusChanges (
316 p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
318 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
319 p_bus_doc_id IN NUMBER,
320 p_bus_doc_type IN VARCHAR2,
321 p_bus_doc_version IN NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2,
323 x_msg_count OUT NOCOPY NUMBER,
324 x_return_status OUT NOCOPY VARCHAR2);
325
326 /** 11.5.10+ code
327 Function to check if any deliverables exist for a given external
328 party for a given contract. Invoked by Repository ContractDetailsAMImpl.java API.
329 Parameter Details:
330 p_busdoc_id : Business document Id
331 p_busdoc_type : Business document type
332 p_external_party_id ID of internal or external party
333 p_external_party_role Role of internal or external party
334 (valid values INTERNAL,SUPPLIER, CUSTOMER, PARTNER)
335 Returns N or Y, if there is unexpected error then it returns NULL.
336 **/
337 FUNCTION deliverablesForExtPartyExist(
338 p_api_version IN NUMBER,
339 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_data OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER,
343
344 p_busdoc_id IN NUMBER,
345 p_busdoc_type IN VARCHAR2,
346 p_external_party_id IN NUMBER,
347 p_external_party_role IN VARCHAR2)
348 RETURN VARCHAR2;
349
350 /** 11.5.10+ code
351 Function to check if any maneagable deliverables exist for a given contract. Invoked by Repository ContractDetailsAMImpl.java.
352 Parameter Details:
353 p_busdoc_id : Business document Id
354 p_busdoc_type : Business document type
355 p_busdoc_version : Business document version
356 Returns N or Y, if there is unexpected error then it returns NULL.
357 **/
358 FUNCTION check_manageable_deliverables(
359 p_api_version IN NUMBER,
360 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_data OUT NOCOPY VARCHAR2,
363 x_msg_count OUT NOCOPY NUMBER,
364
365 p_busdoc_id IN NUMBER,
366 p_busdoc_type IN VARCHAR2,
367 p_busdoc_version IN NUMBER)
368 RETURN VARCHAR2;
369
370 /**
371 * This procedure updates external party id and site id
372 * on deliverables based on external party role for given class of business document.
373 * This API is for HZ party Merge process
374 **/
375 PROCEDURE mergeExtPartyOnDeliverables (
376 p_api_version IN NUMBER,
377 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
378 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
379 p_document_class IN VARCHAR2,
380 p_from_external_party_id IN NUMBER,
381 p_from_external_party_site_id IN NUMBER,
382 p_to_external_party_id IN NUMBER,
383 p_to_external_party_site_id IN NUMBER,
384 x_msg_data OUT NOCOPY VARCHAR2,
385 x_msg_count OUT NOCOPY NUMBER,
386 x_return_status OUT NOCOPY VARCHAR2);
387
388 /**
389 * This procedure resolves activates deliverables based on Cancel/Close/Termination Event
390 * for a given business document version
391 * @param IN p_bus_doc_id target business document id
392 * @param IN p_bus_doc_type target business document type
393 * @param IN p_bus_doc_version target business document version, documents with no valid version number
394 * should pass -99
395 * @param IN p_event_code action based business document event (e.g. PO Cancelled)
396 * @param IN p_event_date action based business document event date
397 * @param IN p_bus_doc_date_events_tbl table type for business document date based events
398 */
399 PROCEDURE activateCloseoutDeliverables (
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
402 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
403 p_bus_doc_id IN NUMBER,
404 p_bus_doc_type IN VARCHAR2,
405 p_bus_doc_version IN NUMBER,
406 p_event_code IN VARCHAR2,
407 p_event_date IN DATE,
408 p_bus_doc_date_events_tbl IN BUSDOCDATES_TBL_TYPE,
409 x_msg_data OUT NOCOPY VARCHAR2,
410 x_msg_count OUT NOCOPY NUMBER,
411 x_return_status OUT NOCOPY VARCHAR2);
412
413 /*-- Start of comments
414 --API name : applyPaymentHolds
415 --Type : Public.
416 --Function : 1. This API returns TRUE if the Invoices for the concerned PO need to be held.False otherwise
417 -- : 2. It runs through the pay_when_paid deliverables associated with the concerned PO.
418 -- : It returns true based on which checkbox is checked and by comparing the sysdate with the actual due date.
419 --Usage : This public API will be used only by the PO team to determine if invoices need to be held
420 -- :for the PO because of any deliverable.
421 --Pre-reqs : None.
422 --Parameters :
423 --IN : p_api_version IN NUMBER Required
424 -- : p_init_msg_list IN VARCHAR2 Optional
425 -- Default = FND_API.G_FALSE
426 -- : p_bus_doc_id IN NUMBER Required
427 -- Header ID of the Standard Purchase Order
428 -- : p_bus_doc_version IN NUMBER Required
429 -- Version number of the Standard Purchase Order
430 --OUT : x_return_status OUT VARCHAR2
431 -- : x_msg_count OUT NUMBER
432 -- : x_msg_data OUT VARCHAR2(2000)
433 --Note :
434 -- End of comments */
435
436 PROCEDURE applyPaymentHolds(
437 p_api_version IN NUMBER,
438 p_bus_doc_id IN NUMBER,
439 p_bus_doc_version IN NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2,
441 x_msg_count OUT NOCOPY NUMBER,
442 x_return_status OUT NOCOPY VARCHAR2);
443
444 /*-- Start of comments
445 --Function name : checkDeliverablePayHold
446 --Type : Public.
447 --Function : This Function returns TRUE if the deliverable is holding invoices.False otherwise.
448 --Usage : This public API will be used only by the Projects team to determine if a
449 -- particular deliverable is holding invoices or not.
450 --Pre-reqs : None.
451 --Returns :TRUE or FALSE, if there is unexpected error then it returns NULL.
452 -- End of comments */
453
454 FUNCTION checkDeliverablePayHold (
455 p_deliverable_id IN NUMBER)
456 RETURN VARCHAR2;
457
458
459 END OKC_MANAGE_DELIVERABLES_GRP;