1 PACKAGE IEX_CASE_UTL_PUB AUTHID CURRENT_USER AS
2 /* $Header: iexucass.pls 120.8 2006/08/24 17:52:36 raverma ship $ */
3 /*#
4 * Case Utility APIs allow users to create,
5 * update, and close Collections cases.
6 * @rep:scope public
7 * @rep:product IEX
8 * @rep:displayname Create Update Collections Case
9 * @rep:lifecycle active
10 * @rep:compatibility S
11 * @rep:category BUSINESS_ENTITY IEX_COLLECTION_CASE
12 */
13 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
14 G_YES CONSTANT VARCHAR2(1) := 'Y';
15 G_NO CONSTANT VARCHAR2(1) := 'N';
16 G_NUMBER CONSTANT NUMBER := 1; -- data type is number
17 G_VARCHAR2 CONSTANT NUMBER := 2; -- data type is varchar2
18
19 TYPE cas_Rec_Type IS RECORD
20 ( CAS_ID NUMBER := FND_API.G_MISS_NUM,
21 CASE_NUMBER VARCHAR2(240) := FND_API.G_MISS_CHAR,
22 ACTIVE_FLAG VARCHAR2(240) := FND_API.G_MISS_CHAR,
23 PARTY_ID NUMBER := FND_API.G_MISS_NUM,
24 ORIG_CAS_ID NUMBER := FND_API.G_MISS_NUM,
25 CASE_STATE VARCHAR2(240) := FND_API.G_MISS_CHAR,
26 STATUS_CODE VARCHAR2(240) := FND_API.G_MISS_CHAR,
27 OBJECT_VERSION_NUMBER NUMBER := FND_API.G_MISS_NUM,
28 CASE_ESTABLISHED_DATE DATE := FND_API.G_MISS_DATE,
29 CASE_CLOSING_DATE DATE := FND_API.G_MISS_DATE,
30 OWNER_RESOURCE_ID NUMBER := FND_API.G_MISS_NUM,
31 ACCESS_RESOURCE_ID NUMBER := FND_API.G_MISS_NUM,
32 REQUEST_ID NUMBER := FND_API.G_MISS_NUM,
33 PROGRAM_APPLICATION_ID NUMBER := FND_API.G_MISS_NUM,
34 PROGRAM_ID NUMBER := FND_API.G_MISS_NUM,
35 PROGRAM_UPDATE_DATE DATE := FND_API.G_MISS_DATE,
36 ATTRIBUTE_CATEGORY VARCHAR2(240) := FND_API.G_MISS_CHAR,
37 ATTRIBUTE1 VARCHAR2(240) := FND_API.G_MISS_CHAR,
38 ATTRIBUTE2 VARCHAR2(240) := FND_API.G_MISS_CHAR,
39 ATTRIBUTE3 VARCHAR2(240) := FND_API.G_MISS_CHAR,
40 ATTRIBUTE4 VARCHAR2(240) := FND_API.G_MISS_CHAR,
41 ATTRIBUTE5 VARCHAR2(240) := FND_API.G_MISS_CHAR,
42 ATTRIBUTE6 VARCHAR2(240) := FND_API.G_MISS_CHAR,
43 ATTRIBUTE7 VARCHAR2(240) := FND_API.G_MISS_CHAR,
44 ATTRIBUTE8 VARCHAR2(240) := FND_API.G_MISS_CHAR,
45 ATTRIBUTE9 VARCHAR2(240) := FND_API.G_MISS_CHAR,
46 ATTRIBUTE10 VARCHAR2(240) := FND_API.G_MISS_CHAR,
47 ATTRIBUTE11 VARCHAR2(240) := FND_API.G_MISS_CHAR,
48 ATTRIBUTE12 VARCHAR2(240) := FND_API.G_MISS_CHAR,
49 ATTRIBUTE13 VARCHAR2(240) := FND_API.G_MISS_CHAR,
50 ATTRIBUTE14 VARCHAR2(240) := FND_API.G_MISS_CHAR,
51 ATTRIBUTE15 VARCHAR2(240) := FND_API.G_MISS_CHAR,
52 CREATED_BY NUMBER := FND_API.G_MISS_NUM,
53 CREATION_DATE DATE := FND_API.G_MISS_DATE,
54 LAST_UPDATED_BY NUMBER := FND_API.G_MISS_NUM,
55 LAST_UPDATE_DATE DATE := FND_API.G_MISS_DATE,
56 LAST_UPDATE_LOGIN NUMBER := FND_API.G_MISS_NUM,
57 CLOSE_REASON VARCHAR2(240) := FND_API.G_MISS_CHAR,
58 ORG_ID NUMBER := FND_API.G_MISS_NUM,
59 COMMENTS VARCHAR2(240) := FND_API.G_MISS_CHAR,
60 PREDICTED_RECOVERY_AMOUNT NUMBER := FND_API.G_MISS_NUM,
61 PREDICTED_CHANCE NUMBER := FND_API.G_MISS_NUM
62 );
63
64 G_MISS_cas_REC cas_Rec_Type;
65 TYPE cas_Tbl_Type IS TABLE OF cas_Rec_Type INDEX BY BINARY_INTEGER;
66 G_MISS_cas_TBL cas_Tbl_Type;
67
68 TYPE case_definition_Rec_Type IS RECORD
69 (
70 COLUMN_NAME VARCHAR2(240) ,
71 COLUMN_VALUE VARCHAR2(240) ,
72 TABLE_NAME VARCHAR2(240) DEFAULT NULL,
73 DATA_TYPE NUMBER DEFAULT G_NUMBER
74 );
75
76 G_MISS_CASE_DEFINITION_REC CASE_DEFINITION_REC_TYPE;
77 TYPE CASE_DEFINITION_TBL_TYPE IS TABLE OF CASE_DEFINITION_REC_TYPE INDEX BY BINARY_INTEGER;
78 G_MISS_CASE_DEF_TBL CASE_DEFINITION_TBL_TYPE;
79
80 /** Case contact table definition **/
81
82 TYPE case_contact_Rec_Type IS RECORD
83 (
84 CONTACT_PARTY_ID VARCHAR2(30) ,
85 PRIMARY_FLAG VARCHAR2(1) DEFAULT G_NO,
86 ADDRESS_ID NUMBER DEFAULT NULL,
87 PHONE_ID NUMBER DEFAULT NULL
88 );
89
90 G_MISS_CASE_CONTACT_REC CASE_CONTACT_REC_TYPE;
91 TYPE CASE_CONTACT_TBL_TYPE IS TABLE OF CASE_CONTACT_REC_TYPE INDEX BY BINARY_INTEGER;
92 G_MISS_CASE_CONTACT_TBL CASE_CONTACT_TBL_TYPE;
93
94 /* Name CheckCaseDef
95 ** It Checks if all the elements of the case which defines a case are valid
96 ** Values
97 */
98 /*#
99 * validates all elements that define a case
100 * @param P_case_definition_tbl Collections Case Definition Pl/sql Table
101 * @rep:scope public
102 * @rep:lifecycle active
103 * @rep:displayname Check Case Definition
104 */
105 Function CheckCaseDef(P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE DEFAULT G_MISS_CASE_DEF_TBL)Return BOOLEAN ;
106
107 /* Name GetCaseID
108 ** Return matching case id for the given case definition
109 */
110 /*#
111 * retrieves the case ID for a case definition.
112 * @param P_case_definition_tbl Collections Case Definition Pl/sql Table
113 * @param x_cas_id Case Iddentifier
114 * @rep:scope public
115 * @rep:lifecycle active
116 * @rep:displayname Fetch Case ID
117 */
118 Procedure GetCaseID
119 (P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE DEFAULT G_MISS_CASE_DEF_TBL,
120 x_cas_id OUT NOCOPY NUMBER );
121
122 /* Name PopulateCaseDefTbl
123 ** Populates case definition for the given cas_id
124 */
125 /*#
126 * Adds the case definition for a case ID to the PL/SQL table
127 * when creating, reassigning, or checking Collections cases.
128 * Do not run this sub-routine by itself.
129 * @param p_cas_id Case identifier for case definition to be populated
130 * @param x_case_definition_tbl Collections Case Definition PL/SQL Table-output
131 * @rep:scope public
132 * @rep:lifecycle active
133 * @rep:displayname Populate Case Definition PL/SQL Table
134 */
135
136 Procedure PopulateCaseDefTbl
137 ( p_cas_id IN NUMBER,
138 X_case_definition_tbl OUT NOCOPY CASE_DEFINITION_TBL_TYPE
139 );
140
141 /* Name Close Case
142 /*#
143 * updates the status of a case to Closed.
144 * You can also copy the existing case objects to create a new case by
145 * setting the p_copy_objects parameter to Y.
146 * @param p_api_version API Version Number
147 * @param p_init_msg_list Intialize Message Stack
148 * @param p_commit Commit flag
149 * @param P_Validation_Level Validation level
150 * @param p_cas_id Case identifier for case definition to be populated
151 * @param p_close_date Close Date for collections Case
152 * @param p_copy_objects If Y, then create new case and copy all the case objects to the new case. If N, then do not copy the case objects. Default is N.
153 * @param p_cas_Rec Collections case record type
154 * @param x_return_status API return status
155 * @param x_msg_count Number of error messages
156 * @param x_msg_data Error message data
157 * @rep:scope public
158 * @rep:lifecycle planned
159 * @rep:displayname Close Collections Case
160 */
161 PROCEDURE CloseCase(
162 P_Api_Version_Number IN NUMBER,
163 P_Init_Msg_List IN VARCHAR2 ,
164 P_Commit IN VARCHAR2 ,
165 P_validation_level IN NUMBER ,
166 P_cas_id IN NUMBER,
167 p_close_date IN DATE ,
168 p_copy_objects IN VARCHAR2 ,
169 p_cas_Rec IN cas_Rec_Type := G_MISS_cas_REC,
170 X_Return_Status OUT NOCOPY VARCHAR2,
171 X_Msg_Count OUT NOCOPY NUMBER,
172 X_Msg_Data OUT NOCOPY VARCHAR2
173 );
174
175 /*#
176 * Creates case objects and validates case definition
177 * elements.If a case does not exist then it creates a new case with the
178 * given case definition.
179 * @param p_api_version_number API Version Number
180 * @param p_init_msg_list Intialize Message Stack
181 * @param p_commit Commit flag
182 * @param P_Validation_Level Validation level
183 * @param p_cas_id Case identifier for case definition to be populated
184 * @param P_case_definition_tbl Collections Case Definition Pl/sql Table
185 * @param P_case_number Collections case number
186 * @param P_case_comments Description for collections case
187 * @param P_case_established_date Collections case start date
188 * @param P_org_id Organization identifier
189 * @param P_object_code Object code of the object ID
190 * @param P_party_id Party identifier
191 * @param P_object_id Case object identifier
192 * @param X_case_object_id output collections case identifier
193 * @param p_cas_Rec Collections case record type
194 * @param x_return_status API return status
195 * @param x_msg_count Number of error messages
196 * @param x_msg_data Error message data
197 * @rep:scope public
198 * @rep:lifecycle active
199 * @rep:displayname Create Collections Case Objects
200 */
201 PROCEDURE CreateCaseObjects(
202 P_Api_Version_Number IN NUMBER,
203 P_Init_Msg_List IN VARCHAR2 ,
204 P_Commit IN VARCHAR2 ,
205 P_validation_level IN NUMBER ,
206 P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE,
207 P_cas_id IN NUMBER ,
208 P_case_number IN VARCHAR2 ,
209 P_case_comments IN VARCHAR2 ,
210 P_case_established_date IN DATE ,
211 P_org_id IN NUMBER ,
212 P_object_code IN VARCHAR2 ,
213 P_party_id IN NUMBER,
214 P_object_id IN NUMBER,
215 p_cas_rec IN CAS_Rec_Type DEFAULT G_MISS_CAS_REC,
216 X_case_object_id OUT NOCOPY NUMBER,
217 X_Return_Status OUT NOCOPY VARCHAR2,
218 X_Msg_Count OUT NOCOPY NUMBER,
219 X_Msg_Data OUT NOCOPY VARCHAR2
220 );
221
222 /*#
223 * Updates collections case.
224 * Use this procedure to update a case or change the status from delinquent to current when
225 * the case is no longer delinquent. * @param p_api_version API Version Number
226 * @param p_api_version_number API Version Number
227 * @param p_init_msg_list Intialize message stack
228 * @param p_commit Commit flag
229 * @param P_Validation_Level Validation level
230 * @param p_cas_Rec Collections case record type
231 * @param x_return_status API return status
232 * @param x_msg_count Number of error messages
233 * @param x_msg_data Error message data
234 * @rep:scope public
235 * @rep:lifecycle planned
236 * @rep:displayname Update Collections Cases
237 */
238 PROCEDURE UpdateCase(
239 P_Api_Version_Number IN NUMBER,
240 P_Init_Msg_List IN VARCHAR2 ,
241 P_Commit IN VARCHAR2 ,
242 P_validation_level IN NUMBER ,
243 p_cas_rec IN CAS_Rec_Type DEFAULT G_MISS_CAS_REC,
244 X_Return_Status OUT NOCOPY VARCHAR2,
245 X_Msg_Count OUT NOCOPY NUMBER,
246 X_Msg_Data OUT NOCOPY VARCHAR2 );
247
248 /*#
249 * Creates collections case contacts.
250 * @param p_api_version_number API Version Number
251 * @param p_init_msg_list Intialize message stack
252 * @param p_commit Commit flag
253 * @param P_Validation_Level Validation level
254 * @param P_case_contact_tbl Collections case definition PL/SQL table
255 * @param p_cas_id Case identifier for case definition to be populated
256 * @param x_return_status API return status
257 * @param x_msg_count Number of error messages
258 * @param x_msg_data Error message data
259 * @rep:scope public
260 * @rep:lifecycle planned
261 * @rep:displayname Create Collections Case Contacts
262 */
263 PROCEDURE CreateCasecontacts(
264 P_Api_Version_Number IN NUMBER,
265 P_Init_Msg_List IN VARCHAR2 ,
266 P_Commit IN VARCHAR2 ,
267 P_validation_level IN NUMBER ,
268 P_case_contact_tbl IN CASE_CONTACT_TBL_TYPE DEFAULT G_MISS_CASE_CONTACT_TBL,
269 P_cas_id IN NUMBER ,
270 X_Return_Status OUT NOCOPY VARCHAR2,
271 X_Msg_Count OUT NOCOPY NUMBER,
272 X_Msg_Data OUT NOCOPY VARCHAR2
273 );
274
275
276 /*#
277 * Creates a new case object or reassigns a case when the Bill To address or any case attribute changes.* @rep:scope public
278 * @param P_ObjectID Object identifier
279 * @rep:lifecycle active
280 * @rep:displayname Check Contract
281 */
282 Function CheckContract
283 (P_ObjectID IN NUMBER
284 )Return BOOLEAN ;
285
286
287 /*#
288 * Reassigns a contract or creates a new case if no existing case matches the new case definition.
289 * @param p_api_version_number API Version Number
290 * @param p_init_msg_list Intialize message stack
291 * @param p_commit Commit flag
292 * @param P_Validation_Level Validation level
293 * @param p_cas_id Case identifier for case definition to be populated
294 * @param P_case_definition_tbl Collections case definition PL/SQL table
295 * @param P_case_number Collections case number
296 * @param P_case_comments Description for collections case
297 * @param P_case_established_date Collections case start date
298 * @param P_org_id Organization Identifier
299 * @param P_object_code Object Code of the Object ID
300 * @param P_party_id Party identifier
301 * @param P_object_id Case object identifier
302 * @param X_case_object_id Output collections case identifier
303 * @param p_cas_Rec Collections case record type
304 * @param x_return_status API return status
305 * @param x_msg_count Number of error messages
306 * @param x_msg_data Error message data
307 * @rep:scope public
308 * @rep:lifecycle active
309 * @rep:displayname Reassign Collections Case Objects
310 */
311 PROCEDURE ReassignCaseObjects(
312 P_Api_Version_Number IN NUMBER,
313 P_Init_Msg_List IN VARCHAR2 ,
314 P_Commit IN VARCHAR2 ,
315 P_validation_level IN NUMBER ,
316 P_case_definition_tbl IN CASE_DEFINITION_TBL_TYPE,
317 P_cas_id IN NUMBER ,
318 P_case_number IN VARCHAR2 ,
319 P_case_comments IN VARCHAR2 ,
320 P_case_established_date IN DATE ,
321 P_org_id IN NUMBER ,
322 P_object_code IN VARCHAR2 ,
323 P_party_id IN NUMBER,
324 P_object_id IN NUMBER,
325 p_cas_rec IN CAS_Rec_Type ,
326 X_case_object_id OUT NOCOPY NUMBER,
327 X_Return_Status OUT NOCOPY VARCHAR2,
328 X_Msg_Count OUT NOCOPY NUMBER,
329 X_Msg_Data OUT NOCOPY VARCHAR2
330 );
331 END IEX_CASE_UTL_PUB;
[Home] [Help]