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