1 PACKAGE IEX_PROMISES_PUB AUTHID CURRENT_USER as
2 /* $Header: iexpyprs.pls 120.11 2010/12/16 16:02:17 snuthala ship $ */
3 /*#
4 Creates a promise.
5 * @rep:scope public
6 * @rep:product IEX
7 * @rep:lifecycle active
8 * @rep:displayname IEX Promises API
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY IEX_PROMISES
11 */
12
13 TYPE ATTRIBUTE_REC_TYPE IS RECORD
14 (
15 ATTRIBUTE_CATEGORY VARCHAR2(30) DEFAULT NULL,
16 ATTRIBUTE1 VARCHAR2(150) DEFAULT NULL,
17 ATTRIBUTE2 VARCHAR2(150) DEFAULT NULL,
18 ATTRIBUTE3 VARCHAR2(150) DEFAULT NULL,
19 ATTRIBUTE4 VARCHAR2(150) DEFAULT NULL,
20 ATTRIBUTE5 VARCHAR2(150) DEFAULT NULL,
21 ATTRIBUTE6 VARCHAR2(150) DEFAULT NULL,
22 ATTRIBUTE7 VARCHAR2(150) DEFAULT NULL,
23 ATTRIBUTE8 VARCHAR2(150) DEFAULT NULL,
24 ATTRIBUTE9 VARCHAR2(150) DEFAULT NULL,
25 ATTRIBUTE10 VARCHAR2(150) DEFAULT NULL,
26 ATTRIBUTE11 VARCHAR2(150) DEFAULT NULL,
27 ATTRIBUTE12 VARCHAR2(150) DEFAULT NULL,
28 ATTRIBUTE13 VARCHAR2(150) DEFAULT NULL,
29 ATTRIBUTE14 VARCHAR2(150) DEFAULT NULL,
30 ATTRIBUTE15 VARCHAR2(150) DEFAULT NULL
31 );
32
33 /* Insert promise record */
34 TYPE PRO_INSRT_REC_TYPE IS RECORD
35 (
36 PROMISE_AMOUNT NUMBER, /* required; should be greater than 0 */
37 CURRENCY_CODE VARCHAR2(15), /* required; should be the same as promise object original currency */
38 PROMISE_DATE DATE, /* required; should be unique for the promise object */
39 PROMISE_PAYMENT_METHOD VARCHAR2(30), /* optional; valid values from iex_lookups_v, lookup_type: IEX_PAYMENT_TYPES */
40 ACCOUNT VARCHAR2(240), /* optional */
41 PROMISE_ITEM_NUMBER VARCHAR2(240), /* optional */
42 PROMISE_TARGET VARCHAR2(30), /* required; valid values: ACCOUNTS, INVOICES, CNSLD, CONTRACTS */
43 CUST_ACCOUNT_ID NUMBER, /* required for all promise targets */
44 DELINQUENCY_ID NUMBER, /* required only for promises on INVOICES */
45 CNSLD_INVOICE_ID NUMBER, /* required only for promises on CNSLD */
46 CONTRACT_ID NUMBER, /* required only for promises on CONTRACTS */
47 CAMPAIGN_SCHED_ID NUMBER, /* optional */
48 TAKEN_BY_RESOURCE_ID NUMBER, /* required */
49 PROMISED_BY_PARTY_REL_ID NUMBER, /* optional; valid combinations for all 3 parties: 1) all 3 parties are not null and valid */
50 PROMISED_BY_PARTY_ORG_ID NUMBER, /* 2) PARTY_ORG_ID is not null, other 2 are null */
51 PROMISED_BY_PARTY_PER_ID NUMBER, /* 3) PARTY_PER_ID is not null, other 2 are null */
52 NOTE VARCHAR2(2000), /* optional */
53 ATTRIBUTES ATTRIBUTE_REC_TYPE /* optional */
54 );
55
56 /* Update promise record */
57 TYPE PRO_UPDT_REC_TYPE IS RECORD
58 (
59 PROMISE_ID NUMBER, /* required */
60 PROMISE_AMOUNT NUMBER, /* required */
61 PROMISE_DATE DATE, /* required */
62 PROMISE_PAYMENT_METHOD VARCHAR2(30), /* optional */
63 ACCOUNT VARCHAR2(240), /* optional */
64 PROMISE_ITEM_NUMBER VARCHAR2(240), /* optional */
65 CAMPAIGN_SCHED_ID NUMBER, /* optional */
66 TAKEN_BY_RESOURCE_ID NUMBER, /* required */
67 PROMISED_BY_PARTY_REL_ID NUMBER, /* optional; valid combinations for all 3 parties: 1) all 3 parties are not null and valid */
68 PROMISED_BY_PARTY_ORG_ID NUMBER, /* 2) PARTY_ORG_ID is not null, other 2 are null */
69 PROMISED_BY_PARTY_PER_ID NUMBER, /* 3) PARTY_PER_ID is not null, other 2 are null */
70 NOTE VARCHAR2(2000), /* optional */
71 ATTRIBUTES ATTRIBUTE_REC_TYPE /* optional */
72 );
73
74 /* Cancel promise record */
75 TYPE PRO_CNCL_REC_TYPE IS RECORD
76 (
77 PROMISE_ID NUMBER, /* required */
78 TAKEN_BY_RESOURCE_ID NUMBER, /* required */
79 PROMISED_BY_PARTY_REL_ID NUMBER, /* optional; valid combinations for all 3 parties: 1) all 3 parties are not null and valid */
80 PROMISED_BY_PARTY_ORG_ID NUMBER, /* 2) PARTY_ORG_ID is not null, other 2 are null */
81 PROMISED_BY_PARTY_PER_ID NUMBER, /* 3) PARTY_PER_ID is not null, other 2 are null */
82 NOTE VARCHAR2(2000), /* optional */
83 ATTRIBUTES ATTRIBUTE_REC_TYPE /* optional */ --Added by snuthala to fix 10373547
84 );
85
86 /* Promise response record for INSERT_PROMISE, UPDATE_PROMISE and CANCEL_PROMISE APIs */
87 TYPE PRO_RESP_REC_TYPE IS RECORD
88 (
89 PROMISE_ID NUMBER,
90 NOTE_ID NUMBER,
91 STATUS VARCHAR2(30),
92 STATE VARCHAR2(30)
93 );
94
95 /* Mass promise common record */
96 TYPE PRO_MASS_REC_TYPE IS RECORD
97 (
98 PROMISE_DATE DATE, /* required */
99 PROMISE_PAYMENT_METHOD VARCHAR2(30), /* optional */
100 ACCOUNT VARCHAR2(240), /* optional */
101 PROMISE_ITEM_NUMBER VARCHAR2(240), /* optional */
102 CAMPAIGN_SCHED_ID NUMBER, /* optional */
103 TAKEN_BY_RESOURCE_ID NUMBER, /* required */
104 PROMISED_BY_PARTY_REL_ID NUMBER, /* optional; valid combinations for all 3 parties: 1) all 3 parties are not null and valid */
105 PROMISED_BY_PARTY_ORG_ID NUMBER, /* 2) PARTY_ORG_ID is not null, other 2 are null */
106 PROMISED_BY_PARTY_PER_ID NUMBER, /* 3) PARTY_PER_ID is not null, other 2 are null */
107 NOTE VARCHAR2(2000), /* optional */
108 ATTRIBUTES ATTRIBUTE_REC_TYPE /* optional */
109 );
110
111 /* Mass promise response record */
112 TYPE PRO_MASS_RESP_REC_TYPE IS RECORD
113 (
114 PROMISE_ID NUMBER, /* promise_id */
115 PROMISE_AMOUNT NUMBER, /* promise amount */
116 CURRENCY_CODE VARCHAR2(15), /* promise original currency */
117 CUST_ACCOUNT_ID NUMBER, /* promise cust_account_id */
118 CUST_SITE_USE_ID NUMBER, /* promise cust_site_use_id */
119 DELINQUENCY_ID NUMBER, /* promise delinquency_id */
120 STATUS VARCHAR2(30), /* promise status */
121 STATE VARCHAR2(30), /* promise state */
122 COLLECTABLE_AMOUNT NUMBER, /* promise remaining amount */
123 NOTE_ID NUMBER /* note_id */
124 );
125
126 /* Mass promise response table */
127 TYPE PRO_MASS_RESP_TBL IS TABLE OF PRO_MASS_RESP_REC_TYPE INDEX BY BINARY_INTEGER;
128
129 /*#
130 * Use this procedure to creates a new promise for a payment schedule or lease contract.
131 *
132 * @param p_api_version Standard Parameter
133 * @param p_init_msg_list Standard parameter
134 * @param p_commit Standard parameter for commiting the data
135 * @param p_validation_level Standard parameter
136 * @param x_return_status Standard parameter
137 * @param x_msg_count Standard parameter
138 * @param x_msg_data Standard parameter
139 * @param P_PROMISE_REC PL/SQL record containing promise details
140 * Record(s) Structure
141 * ~~~~~~~~~~~~~~~~~~~
142 * PROMISE_AMOUNT Promise Amount
143 * Currency Code Currency Code
144 * PROMISE_DATE Promise Date
145 * PROMISE_PAYMENT_METHOD Promise Payment Method
146 * ACCOUNT Payment Account (Optional)
147 * PROMISE_ITEM_NUMBER Promise Item Number (Optional)
148 * PROMISE_TARGET Promise Target. valid values: ACCOUNTS, INVOICES, CNSLD, CONTRACTS
149 * (ACCOUNTS and CNSLD are no longer Used)
150 * CUST_ACCOUNT_ID Customer Account Identifier
151 * DELINQUENCY_ID Delinquency Identifier
152 * CONSLD_INVOICE_ID Consolidated Invoice Identifier (Not Used)
153 * CONTRACT_ID Contract Identifier (Applicable for Lease)
154 * CAMPAIGH_SCHED_ID Campaigh Schedule Identifier
155 * TAKEN_BY_RESOURCE_ID Resource Identifier of the resource who created the promise.
156 * PROMISED_BY_PARTY_REL_ID Relationship Party Identifier
157 * PROMISED_BY_PARTY_ORG_ID Organization Party Identifier
158 * PROMISED_BY_PARTY_PER_ID Person Party Identifier
159 * NOTE Promise Note
160 * ATTRIBUTES PL/SQL Table of Descriptive Flexfield Attributes.
161 *
162 * @param X_PRORESP_REC PL/SQL record returning the details of the created Promise.
163 * Record Structure
164 * ~~~~~~~~~~~~~~~~
165 * PROMISE_ID Promise Identifier of the created Promise
166 * NOTE_ID Note Identifier associated with the created Promise
167 * STATUS Status of the created Promise
168 * STATE State of the created Promise
169 *
170 * return N/A
171 * @rep:scope public
172 * @rep:lifecycle active
173 * @rep:displayname Insert Promise
174 * @rep:compatibility S
175 * @rep:businessevent PROMISE
176 */
177
178 PROCEDURE INSERT_PROMISE(
179 P_API_VERSION IN NUMBER,
180 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
181 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
182 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
183 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
184 X_MSG_COUNT OUT NOCOPY NUMBER,
185 X_MSG_DATA OUT NOCOPY VARCHAR2,
186 P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_INSRT_REC_TYPE,
187 X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE);
188
189 /*#
190 * Use this procedure to update an existing promise for a payment schedule or a lease contract.
191 *
192 * @param p_api_version Standard Parameter
193 * @param p_init_msg_list Standard parameter
194 * @param p_commit Standard parameter for commiting the data
195 * @param p_validation_level Standard parameter
196 * @param x_return_status Standard parameter
197 * @param x_msg_count Standard parameter
198 * @param x_msg_data Standard parameter
199 * @param P_PROMISE_REC PL/SQL record containing promise details
200 * Record(s) Structure
201 * ~~~~~~~~~~~~~~~~~~~
202 * PROMISE_ID Promise Identifier that needs to be updated.
203 * PROMISE_AMOUNT Promise Amount
204 * PROMISE_DATE Promise Date
205 * PROMISE_PAYMENT_METHOD Promise Payment Method
206 * ACCOUNT Payment Account (Optional)
207 * PROMISE_ITEM_NUMBER Promise Item Number (Optional)
208 * CAMPAIGH_SCHED_ID Campaigh Schedule Identifier
209 * TAKEN_BY_RESOURCE_ID Resource Identifier of the resource who created the promise.
210 * PROMISED_BY_PARTY_REL_ID Relationship Party Identifier
211 * PROMISED_BY_PARTY_ORG_ID Organization Party Identifier
212 * PROMISED_BY_PARTY_PER_ID Person Party Identifier
213 * NOTE Promise Note
214 * ATTRIBUTES PL/SQL Table of Descriptive Flexfield Attributes.
215 *
216 * @param X_PRORESP_REC PL/SQL record returning the details of the created Promise.
217 * Record Structure
218 * ~~~~~~~~~~~~~~~~
219 * PROMISE_ID Promise Identifier of the created Promise
220 * NOTE_ID Note Identifier associated with the created Promise
221 * STATUS Status of the created Promise
222 * STATE State of the created Promise
223 *
224 * return N/A
225 * @rep:scope public
226 * @rep:lifecycle active
227 * @rep:displayname Update Promise
228 * @rep:compatibility S
229 * @rep:businessevent PROMISE
230 */
231
232 PROCEDURE UPDATE_PROMISE(
233 P_API_VERSION IN NUMBER,
234 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
235 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
236 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
237 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
238 X_MSG_COUNT OUT NOCOPY NUMBER,
239 X_MSG_DATA OUT NOCOPY VARCHAR2,
240 P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_UPDT_REC_TYPE,
241 X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE);
242
243 /*#
244 * Use this procedure to cancel a promise.
245 *
246 * @param p_api_version Standard Parameter
247 * @param p_init_msg_list Standard parameter
248 * @param p_commit Standard parameter for commiting the data
249 * @param p_validation_level Standard parameter
250 * @param x_return_status Standard parameter
251 * @param x_msg_count Standard parameter
252 * @param x_msg_data Standard parameter
253 * @param P_PROMISE_REC PL/SQL record containing promise details
254 * Record Structure
255 * ~~~~~~~~~~~~~~~~
256 * PROMISE_ID Promise Identifier that needs to be updated.
257 * TAKEN_BY_RESOURCE_ID Resource Identifier of the resource who created the promise.
258 * PROMISED_BY_PARTY_REL_ID Relationship Party Identifier
259 * PROMISED_BY_PARTY_ORG_ID Organization Party Identifier
260 * PROMISED_BY_PARTY_PER_ID Person Party Identifier
261 * NOTE Promise Note
262 *
263 * @param X_PRORESP_REC PL/SQL record returning the details of the created Promise.
264 * Record(s) Structure
265 * ~~~~~~~~~~~~~~~~~~~
266 * PROMISE_ID Promise Identifier of the created Promise
267 * NOTE_ID Note Identifier associated with the created Promise
268 * STATUS Status of the created Promise
269 * STATE State of the created Promise
270 *
271 * return N/A
272 * @rep:scope public
273 * @rep:lifecycle active
274 * @rep:displayname Cancel Promise
275 * @rep:compatibility S
276 * @rep:businessevent PROMISE
277 */
278
279 PROCEDURE CANCEL_PROMISE(
280 P_API_VERSION IN NUMBER,
281 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
282 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
283 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
284 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
285 X_MSG_COUNT OUT NOCOPY NUMBER,
286 X_MSG_DATA OUT NOCOPY VARCHAR2,
287 P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_CNCL_REC_TYPE,
288 X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE);
289
290
291 /*#
292 * Use this procedure to create a mass promise for a list of payment schedules.
293 * It creates a promise for the amount due for each payment schedule.
294 *
295 * @param p_api_version Standard Parameter
296 * @param p_init_msg_list Standard parameter
300 * @param x_msg_count Standard parameter
297 * @param p_commit Standard parameter for commiting the data
298 * @param p_validation_level Standard parameter
299 * @param x_return_status Standard parameter
301 * @param x_msg_data Standard parameter
302 * @param P_MASS_IDS_TBL PL/SQL table of records containing Mass promise IDs
303 * @param P_MASS_PROMISE_REC PL/SQL table of records containing Mass promise details
304 * Record(s) Structure
305 * ~~~~~~~~~~~~~~~~~~~
306 * PROMISE_DATE Promise Date
307 * PROMISE_PAYMENT_METHOD Promise Payment Method
308 * ACCOUNT Payment Account (Optional)
309 * PROMISE_ITEM_NUMBER Promise Item Number (Optional)
310 * CAMPAIGH_SCHED_ID Campaigh Schedule Identifier
311 * TAKEN_BY_RESOURCE_ID Resource Identifier of the resource who created the promise.
312 * PROMISED_BY_PARTY_REL_ID Relationship Party Identifier
313 * PROMISED_BY_PARTY_ORG_ID Organization Party Identifier
314 * PROMISED_BY_PARTY_PER_ID Person Party Identifier
315 * NOTE Promise Note
316 * ATTRIBUTES PL/SQL Table of Descriptive Flexfield Attributes.
317 *
318 * @param X_MASS_PRORESP_TBL PL/SQL table of records containing the details of the Promises
319 * created by the mass Promise.
320 * Record(s) Structure
321 * ~~~~~~~~~~~~~~~~~~~
322 * PROMISE_ID Promise Identifier of the created Promise
323 * PROMISE_AMOUNT Promise Amount
324 * CURRENCY_CODE Currency Code
325 * CUST_ACCOUNT_ID Customer Account Identifier
326 * CUST_SITE_USE_ID Custome Site Use Identifier (Bill To)
327 * DELINQUENCY_ID Delinquency Identifier
328 * STATUS Status of the created Promise
329 * STATE State of the created Promise
330 * COLLECTABLE_AMOUNT Collectable Amount for the Payment Schedule
331 * NOTE_ID Note Identifier associated with the created Promise
332 *
333 * return N/A
334 * @rep:scope public
335 * @rep:lifecycle active
336 * @rep:displayname Mass Promise
337 * @rep:compatibility S
338 * @rep:businessevent PROMISE
339 */
340
341 PROCEDURE MASS_PROMISE(
342 P_API_VERSION IN NUMBER,
343 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
344 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
345 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
346 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
347 X_MSG_COUNT OUT NOCOPY NUMBER,
348 X_MSG_DATA OUT NOCOPY VARCHAR2,
349 P_MASS_IDS_TBL IN DBMS_SQL.NUMBER_TABLE,
350 P_MASS_PROMISE_REC IN IEX_PROMISES_PUB.PRO_MASS_REC_TYPE,
351 X_MASS_PRORESP_TBL OUT NOCOPY IEX_PROMISES_PUB.PRO_MASS_RESP_TBL);
352
353 /*#
354 * Use this procedure to set the strategy for a specified delinquency associated with a promise.
355 * @param p_promise_id Promise Identifier
356 * @param p_status Promise Status
357 * return N/A
358 * @rep:scope public
359 * @rep:lifecycle active
360 * @rep:displayname Set Strategy
361 * @rep:compatibility S
362 * @rep:businessevent STRATEGY
363 */
364
365 PROCEDURE SET_STRATEGY(P_PROMISE_ID IN NUMBER,
366 P_STATUS IN VARCHAR2);
367
368 /*#
369 * Use this procedure to set the status for a promise in Universal Work Queue to
370 * Active, Pending or Complete.
371 * @param p_api_version Standard Parameter
372 * @param p_init_msg_list Standard parameter
373 * @param p_commit Standard parameter for commiting the data
374 * @param p_validation_level Standard parameter
375 * @param x_return_status Standard parameter
376 * @param x_msg_count Standard parameter
377 * @param x_msg_data Standard parameter
378 * @param p_promise_tbl PL/SQL Table of Promise Identifiers whose UWQ status
379 * is changed.
380 * @param p_status UWQ Status
381 * @param p_days Number of days the promise is pending in UWQ
382 * return N/A
383 * @rep:scope public
384 * @rep:lifecycle active
385 * @rep:displayname Show in UWQ
386 * @rep:compatibility S
387 * @rep:businessevent UWQ
388 */
389
390 PROCEDURE SHOW_IN_UWQ(
391 P_API_VERSION IN NUMBER,
392 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
393 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
394 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
395 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
396 X_MSG_COUNT OUT NOCOPY NUMBER,
397 X_MSG_DATA OUT NOCOPY VARCHAR2,
398 P_PROMISE_TBL IN DBMS_SQL.NUMBER_TABLE,
399 P_STATUS IN VARCHAR2,
400 P_DAYS IN NUMBER DEFAULT NULL);
401
402 Procedure update_del_stage_level (
403 p_promise_id IN NUMBER,
404 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
405 X_MSG_COUNT OUT NOCOPY NUMBER,
406 X_MSG_DATA OUT NOCOPY VARCHAR2);
407
408 END;
409