1 PACKAGE IEX_DUNNING_PVT AS
2 /* $Header: iexvduns.pls 120.5.12000000.3 2007/04/17 12:38:10 gnramasa ship $ */
3
4 -- this will be passed back by the get_components procedure
5 TYPE FULFILLMENT_BIND_REC IS RECORD(
6 KEY_NAME VARCHAR2(150),
7 KEY_TYPE VARCHAR2(25), -- 'NUMBER' or 'VARCHAR' or 'DATE'
8 KEY_VALUE VARCHAR2(240));
9
10 TYPE FULFILLMENT_BIND_TBL IS TABLE OF FULFILLMENT_BIND_REC INDEX BY binary_integer;
11
12 Procedure Validate_Delinquency(
13 P_Init_Msg_List IN VARCHAR2 ,
14 P_Delinquency_ID IN NUMBER,
15 X_Return_Status OUT NOCOPY VARCHAR2,
16 X_Msg_Count OUT NOCOPY NUMBER,
17 X_Msg_Data OUT NOCOPY VARCHAR2
18 );
19
20 Procedure Create_AG_DN_XREF
21 (p_api_version IN NUMBER := 1.0,
22 p_init_msg_list IN VARCHAR2 ,
23 p_commit IN VARCHAR2 ,
24 P_AG_DN_XREF_REC IN IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 x_AG_DN_XREF_ID OUT NOCOPY NUMBER);
29
30 Procedure Update_AG_DN_XREF
31 (p_api_version IN NUMBER := 1.0,
32 p_init_msg_list IN VARCHAR2 ,
33 p_commit IN VARCHAR2 ,
34 P_AG_DN_XREF_REC IN IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE,
35 p_AG_DN_XREF_ID IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2);
39
40 Procedure Delete_AG_DN_XREF
41 (p_api_version IN NUMBER := 1.0,
42 p_init_msg_list IN VARCHAR2 ,
43 p_commit IN VARCHAR2 ,
44 P_AG_DN_XREF_ID IN NUMBER,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2);
48
49 Procedure Create_Dunning
50 (p_api_version IN NUMBER := 1.0,
51 p_init_msg_list IN VARCHAR2 ,
52 p_commit IN VARCHAR2 ,
53 P_Dunning_REC IN IEX_DUNNING_PUB.DUNNING_REC_TYPE,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 x_Dunning_ID OUT NOCOPY NUMBER);
58
59 Procedure Update_Dunning
60 (p_api_version IN NUMBER := 1.0,
61 p_init_msg_list IN VARCHAR2 ,
62 p_commit IN VARCHAR2 ,
63 P_Dunning_REC IN IEX_DUNNING_PUB.DUNNING_REC_TYPE,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER,
66 x_msg_data OUT NOCOPY VARCHAR2);
67
68 /*=========================================================================
69 clchang update 10/16/2002 -
70 Send Dunning can be in Customer, Account and Transaction levels in 11.5.9;
71 Send_Level_Dunning is for Customer and Account level;
72 Send_Dunning keeps the same, and is for Transaction Level;
73 *=========================================================================*/
74 Procedure Send_Level_Dunning
75 (p_api_version IN NUMBER := 1.0,
76 p_init_msg_list IN VARCHAR2 ,
77 p_commit IN VARCHAR2 ,
78 p_running_level IN VARCHAR2,
79 p_dunning_plan_id in number,
80 p_resend_flag IN VARCHAR2 DEFAULT NULL,
81 p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
82 p_previous_request_id IN NUMBER,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2);
86
87 Procedure Send_Dunning
88 (p_api_version IN NUMBER := 1.0,
89 p_init_msg_list IN VARCHAR2 ,
90 p_commit IN VARCHAR2 ,
91 p_dunning_plan_id in number,
92 p_resend_flag IN VARCHAR2 DEFAULT NULL,
93 p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
94 p_previous_request_id IN NUMBER,
95 x_return_status OUT NOCOPY VARCHAR2,
96 x_msg_count OUT NOCOPY NUMBER,
97 x_msg_data OUT NOCOPY VARCHAR2);
98
99 /*=========================================================================
100 clchang added 03/04/2003 -
101 The following 2 Resend procedures are especially for resend dunnings;
102 Called by FORM, not Concurrent Program;
103 *=========================================================================*/
104 Procedure Resend_Level_Dunning
105 (p_api_version IN NUMBER := 1.0,
106 p_init_msg_list IN VARCHAR2 ,
107 p_commit IN VARCHAR2 ,
108 p_dunning_plan_id in number,
109 p_running_level IN VARCHAR2,
110 p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
111 x_request_id OUT NOCOPY NUMBER,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2);
115
116 Procedure Resend_Dunning
117 (p_api_version IN NUMBER := 1.0,
118 p_init_msg_list IN VARCHAR2 ,
119 p_commit IN VARCHAR2 ,
120 p_dunning_plan_id in number,
121 p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
122 x_request_id OUT NOCOPY NUMBER,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2);
126
127
128 /* clchang added (for 11.5.9)
129 no iex aging in 11.5.9;
130 in send_dunning, aging_bucket_line_id is not from iex_delinquencies;
131 we need to get by ourselves;
132 */
133 Procedure AGING_DEL(
134 p_api_version IN NUMBER := 1.0,
135 p_init_msg_list IN VARCHAR2 ,
136 p_commit IN VARCHAR2 ,
137 p_delinquency_id IN NUMBER,
138 p_dunning_plan_id in number,
139 p_bucket IN VARCHAR2 DEFAULT NULL,
140 p_object_code IN VARCHAR2 DEFAULT NULL,
141 p_object_id IN NUMBER DEFAULT NULL,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER,
144 x_msg_data OUT NOCOPY VARCHAR2,
145 x_AGING_Bucket_line_ID OUT NOCOPY NUMBER);
146
147
148 Procedure Call_FFM(
149 p_api_version IN NUMBER := 1.0,
150 p_init_msg_list IN VARCHAR2 ,
151 p_commit IN VARCHAR2 ,
152 p_key_name IN VARCHAR2,
153 p_key_id IN NUMBER,
154 p_template_id IN NUMBER,
155 p_method IN VARCHAR2,
156 p_party_id IN NUMBER,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2,
160 x_REQUEST_ID OUT NOCOPY NUMBER);
161
162 Procedure Get_Callback_Date(
163 p_init_msg_list IN VARCHAR2 ,
164 p_callback_days IN NUMBER,
165 x_callback_date OUT NOCOPY DATE,
166 X_Return_Status OUT NOCOPY VARCHAR2,
167 X_Msg_Count OUT NOCOPY NUMBER,
168 X_Msg_Data OUT NOCOPY VARCHAR2 );
169
170
171 PROCEDURE Close_Dunning
172 (p_api_version IN NUMBER := 1.0,
173 p_init_msg_list IN VARCHAR2 ,
174 p_commit IN VARCHAR2 ,
175 p_delinquencies_tbl IN IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE,
176 --p_delinquencies_tbl IN IEX_DUNNING_PUB.DelId_NumList,
177 p_running_level IN VARCHAR2,
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2);
181
182
183 /*=========================================================================
184 clchang update 10/02/2002 - no ReOpen Dunning in 115.9
185 -- clchang added 09/04/2002 for reopen delinquencies
186 -- added in 115.9 code line and 115.6 branch
187 PROCEDURE ReOpen_Dunning
188 (p_api_version IN NUMBER := 1.0,
189 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
190 p_commit IN VARCHAR2 := FND_API.G_FALSE,
191 --p_delinquencies_tbl IN IEX_DUNNING_PUB.DelId_NumList,
192 p_delinquencies_tbl IN DBMS_SQL.NUMBER_TABLE,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2);
196 *=========================================================================*/
197 Procedure Daily_Dunning
198 (p_api_version IN NUMBER := 1.0,
199 p_init_msg_list IN VARCHAR2 ,
200 p_commit IN VARCHAR2 ,
201 --p_dunning_tbl IN IEX_DUNNING_PUB.DUNNING_TBL_TYPE,
202 p_running_level IN VARCHAR2,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2);
206
207 Procedure NEW_TASK(
208 p_api_version IN NUMBER := 1.0,
209 p_init_msg_list IN VARCHAR2 ,
210 p_commit IN VARCHAR2 ,
211 p_delinquency_id IN NUMBER,
212 p_dunning_id IN NUMBER,
213 p_dunning_object_id IN NUMBER,
214 p_dunning_level IN VARCHAR2,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2,
218 x_TASK_ID OUT NOCOPY NUMBER);
219
220 /*========================================================================
221 * Clchang updated 09/19/2002 for Bug 2242346
222 * to create a callback,
223 * we got resource_id from iex_delinquencyies before;
224 * now, we get resource_id based on which agent owns the least tasks
225 * in PARTY level;
226 *
227 *========================================================================*/
228 PROCEDURE Get_Resource(p_api_version IN NUMBER,
229 p_commit IN VARCHAR2,
230 p_init_msg_list IN VARCHAR2 ,
231 p_party_id IN NUMBER,
232 x_resource_id OUT NOCOPY NUMBER,
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2);
236
237 /*
238 || Overview: This procedure is an extension of Call_FFM. Call_FFM only allows one bind variable/value
239 || This will allow you to pass in unlimited bind variables in a name/value pair structure
240 ||
241 || Parameter: p_FULFILLMENT_BIND_TBL = name/value pairs for bind variables
242 || p_template_id = fulfillment template
243 || p_method = Fulfillment Type, currently only 'EMAIL' is supported
244 || p_party_id = pk to hz_parties
245 ||
246 || Source Tables: JTF_FM_TEMPLATE_CONTENTS, HZ_PARTIES, HZ_CONTACT_POINTS,
247 || jtf_FM_query_mes
248 || jtf_FM_query
249 ||
250 || Target Tables:
251 ||
252 || Creation date: 03/07/02 11:36:AM
253 ||
254 || Major Modifications: when who what
255 || 03/07/02 11:36:AM raverma created
256 || 08/06/02 10:00:AM pjgomes Added parameter p_email to Send_Fulfillment api
257 || 08/19/02 02:00:PM pjgomes Changed default value of p_email to NULL
258 */
259 Procedure Send_Fulfillment(p_api_version IN NUMBER := 1.0,
260 p_init_msg_list IN VARCHAR2 ,
261 p_commit IN VARCHAR2 ,
262 p_FULFILLMENT_BIND_TBL IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL,
263 p_template_id IN NUMBER,
264 p_method IN VARCHAR2,
265 p_party_id IN NUMBER,
266 p_user_id IN NUMBER DEFAULT NULL,
267 p_email IN VARCHAR2 DEFAULT NULL,
268 p_level IN VARCHAR2 DEFAULT NULL,
269 p_source_id IN NUMBER DEFAULT NULL,
270 p_object_code IN VARCHAR2 DEFAULT NULL,
271 p_object_id IN NUMBER DEFAULT NULL,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2,
275 x_REQUEST_ID OUT NOCOPY NUMBER,
276 x_contact_destination OUT NOCOPY varchar2,
277 x_contact_party_id OUT NOCOPY NUMBER);
278
279
280 /* This is a new procedure for 11.5.11.
281 * To replace FULFILLMENT by XML Publisher.
282 * Copied from Send_Fulfillemtn.
283 */
284 Procedure Send_XML ( p_api_version IN NUMBER := 1.0,
285 p_init_msg_list IN VARCHAR2 ,
286 p_commit IN VARCHAR2 ,
287 p_resend IN VARCHAR2 ,
288 p_request_id IN NUMBER DEFAULT NULL,
289 p_FULFILLMENT_BIND_TBL IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL,
290 p_template_id IN NUMBER,
291 p_method IN VARCHAR2,
292 p_party_id IN NUMBER,
293 p_user_id IN NUMBER DEFAULT NULL,
294 p_email IN VARCHAR2 DEFAULT NULL,
295 p_level IN VARCHAR2 DEFAULT NULL,
296 p_source_id IN NUMBER DEFAULT NULL,
297 p_object_code IN VARCHAR2 DEFAULT NULL,
298 p_object_id IN NUMBER DEFAULT NULL,
299 p_resource_id IN NUMBER DEFAULT NULL,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_msg_count OUT NOCOPY NUMBER,
302 x_msg_data OUT NOCOPY VARCHAR2,
303 x_REQUEST_ID OUT NOCOPY NUMBER,
304 x_contact_destination OUT NOCOPY varchar2,
305 x_contact_party_id OUT NOCOPY NUMBER);
306
307 Procedure GetContactInfo( p_api_version IN NUMBER := 1.0,
308 p_init_msg_list IN VARCHAR2 ,
309 p_commit IN VARCHAR2 ,
310 p_method IN VARCHAR2,
311 p_party_id IN NUMBER,
312 p_dunning_level IN VARCHAR2,
313 p_cust_site_use_id IN VARCHAR2,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2,
317 x_contact OUT NOCOPY VARCHAR2,
318 x_contact_party_id OUT NOCOPY number);
319
320
321 Procedure GetContactPoint( p_api_version IN NUMBER := 1.0,
322 p_init_msg_list IN VARCHAR2 ,
323 p_commit IN VARCHAR2 ,
324 p_method IN VARCHAR2,
325 p_party_id IN NUMBER,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 x_contact OUT NOCOPY VARCHAR2);
330
331
332 Procedure CHK_QUERY_DATA( p_query_id IN NUMBER,
333 p_FULFILLMENT_BIND_TBL IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL,
334 x_keep_flag OUT NOCOPY NUMBER);
335
336 Procedure WriteLog ( p_msg IN VARCHAR2 DEFAULT NULL,
337 p_flag IN NUMBER DEFAULT NULL);
338
339 procedure GET_DEFAULT_DUN_DEST(p_api_version IN NUMBER := 1.0,
340 p_init_msg_list IN VARCHAR2,
341 p_commit IN VARCHAR2,
342 p_level in varchar2,
343 p_source_id in number,
344 p_send_method in varchar2,
345 X_LOCATION_ID OUT NOCOPY NUMBER,
346 X_CONTACT_ID OUT NOCOPY NUMBER,
347 X_CONTACT_POINT_ID OUT NOCOPY NUMBER,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2);
351
352 procedure GET_DEFAULT_DUN_DATA(p_api_version IN NUMBER := 1.0,
353 p_init_msg_list IN VARCHAR2,
354 p_commit IN VARCHAR2,
355 p_level in varchar2,
356 p_source_id in number,
357 p_send_method in varchar2,
358 p_resend IN VARCHAR2 ,
359 p_object_code IN VARCHAR2 ,
360 p_object_id IN NUMBER,
361 p_fulfillment_bind_tbl in out nocopy IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL,
362 x_return_status OUT NOCOPY VARCHAR2,
363 x_msg_count OUT NOCOPY NUMBER,
364 x_msg_data OUT NOCOPY VARCHAR2);
365
366
367 FUNCTION party_currency_code (p_party_id NUMBER) RETURN VARCHAR2;
368
369 FUNCTION acct_currency_code (p_account_id NUMBER) RETURN VARCHAR2;
370
371 FUNCTION site_currency_code (p_customer_site_use_id NUMBER) RETURN VARCHAR2;
372
373 FUNCTION party_amount_due_remaining(p_party_id NUMBER) RETURN NUMBER;
374
375 FUNCTION acct_amount_due_remaining(p_account_id NUMBER) RETURN NUMBER;
376
377 FUNCTION site_amount_due_remaining(p_customer_site_use_id NUMBER) RETURN NUMBER;
378
379 FUNCTION get_party_id(p_account_id NUMBER) RETURN NUMBER;
380
381 FUNCTION GET_DUNNING_LOCATION(P_SITE_USE_ID NUMBER) RETURN NUMBER;
382
383 END IEX_DUNNING_PVT;
[Home] [Help]