1 PACKAGE IEX_OPI_PVT AUTHID CURRENT_USER AS
2 /* $Header: IEXROPIS.pls 120.0 2004/01/24 03:15:23 appldev noship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 -- Sub type Open Interface records
7 subtype oinv_rec_type is okl_oin_pvt.oinv_rec_type;
8 subtype oinv_tbl_type is okl_oin_pvt.oinv_tbl_type;
9
10 subtype iohv_rec_type is iex_ioh_pvt.iohv_rec_type;
11 subtype iohv_tbl_type is iex_ioh_pvt.iohv_tbl_type;
12
13 ---------------------------------------------------------------------------
14 -- GLOBAL MESSAGE CONSTANTS
15 ---------------------------------------------------------------------------
16 G_FND_APP CONSTANT VARCHAR2(200) := Okc_Api.G_FND_APP;
17 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
18 G_INVALID_VALUE CONSTANT VARCHAR2(200) := Okc_Api.G_INVALID_VALUE;
19 G_INVALID_ACTION_STATUS CONSTANT VARCHAR2(200) := 'INVALID_ACTION_STATUS';
20 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := 'COL_NAME';
21 G_COL_NAME1_TOKEN CONSTANT VARCHAR2(200) := 'COL_NAME1';
22 G_COL_NAME2_TOKEN CONSTANT VARCHAR2(200) := 'COL_NAME2';
23 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := 'PARENT_TABLE';
24 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'IEX_UNEXPECTED_ERROR';
25 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'IEX_SQLERRM';
26 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'IEX_SQLCODE';
27 G_TASK_CREATION_FAILURE CONSTANT VARCHAR2(200) := 'IEX_TASK_CREATION_FAILURE';
28
29 ---------------------------------------------------------------------------
30 -- GLOBAL VARIABLES
31 ---------------------------------------------------------------------------
32 G_PKG_NAME CONSTANT VARCHAR2(200) := 'IEX_OPI_PVT';
33 G_APP_NAME CONSTANT VARCHAR2(3) := 'IEX';
34
35 ---------------------------------------------------------------------------
36 -- GLOBAL EXCEPTION
37 ---------------------------------------------------------------------------
38 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
39 G_INVALID_PARAMETERS EXCEPTION;
40
41 ---------------------------------------------------------------------------
42 -- CONSTANTS FOR ACTION AND STATUS
43 ---------------------------------------------------------------------------
44 ACTION_NOTIFY_CUST CONSTANT VARCHAR2(30) := 'NOTIFY_CUST';
45 ACTION_REPORT_CB CONSTANT VARCHAR2(30) := 'REPORT_CB';
46 ACTION_TRANSFER_EXT_AGNCY CONSTANT VARCHAR2(30) := 'TRANSFER_EXT_AGNCY';
47 ACTION_NOTIFY_EXT_AGNCY CONSTANT VARCHAR2(30) := 'NOTIFY_EXT_AGNCY';
48 ACTION_RECALL_NOTICE CONSTANT VARCHAR2(30) := 'RECALL_NOTICE';
49 ACTION_NOTIFY_RECALL CONSTANT VARCHAR2(30) := 'NOTIFY_RECALL';
50
51
52 STATUS_PENDING_AUTO CONSTANT VARCHAR2(30) := 'PENDING_AUTO';
53 STATUS_PENDING_MANUAL CONSTANT VARCHAR2(30) := 'PENDING_MANUAL';
54 STATUS_PENDING_ALL CONSTANT VARCHAR2(30) := 'PENDING_ALL';
55 STATUS_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
56 STATUS_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
57 STATUS_RECALLED CONSTANT VARCHAR2(30) := 'RECALLED';
58 STATUS_NOTIFIED CONSTANT VARCHAR2(30) := 'NOTIFIED';
59 STATUS_COLLECTED CONSTANT VARCHAR2(30) := 'COLLECTED';
60
61 ---------------------------------------------------------------------------
62 -- CONSTANTS FOR CASE DELINQUENCY STATUS
63 ---------------------------------------------------------------------------
64 CASE_STATUS_CURRENT CONSTANT VARCHAR2(30) := 'CURRENT';
65 ---------------------------------------------------------------------------
66 -- Procedures and Functions
67 ---------------------------------------------------------------------------
68
69 PROCEDURE qc;
70 PROCEDURE change_version;
71 PROCEDURE api_copy;
72
73 PROCEDURE report_all_credit_bureau(
74 errbuf OUT NOCOPY VARCHAR2,
75 retcode OUT NOCOPY NUMBER);
76
77 PROCEDURE insert_pending_hst(
78 p_api_version IN NUMBER,
79 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
80 p_iohv_rec IN iohv_rec_type,
81 x_iohv_rec OUT NOCOPY iohv_rec_type,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2);
85
86 PROCEDURE process_pending_hst(
87 p_api_version IN NUMBER,
88 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
89 p_oinv_rec IN oinv_rec_type,
90 p_iohv_rec IN iohv_rec_type,
91 x_iohv_rec OUT NOCOPY iohv_rec_type,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER,
94 x_msg_data OUT NOCOPY VARCHAR2);
95
96 PROCEDURE process_pending(
97 errbuf OUT NOCOPY VARCHAR2,
98 retcode OUT NOCOPY NUMBER,
99 p_case_number IN VARCHAR2);
100
101 ---------------------------------------------------------------------------
102 -- API for the credit bureau to update the date the record was accesed
103 -- and free text comment
104 ---------------------------------------------------------------------------
105 PROCEDURE complete_report_cb(
106 p_api_version IN NUMBER,
107 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
108 p_interface_id IN NUMBER,
109 p_report_date IN DATE,
110 p_comments IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2);
114
115 ---------------------------------------------------------------------------
116 -- API to update the notification date, the customer was notified about
117 -- intent to report to the credit bureau
118 ---------------------------------------------------------------------------
119 PROCEDURE complete_notify(
120 p_api_version IN NUMBER,
121 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
122 p_interface_id IN NUMBER,
123 p_hst_id IN NUMBER,
124 p_notification_date IN DATE,
125 p_comments IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2);
129
130 ---------------------------------------------------------------------------
131 -- API for the external agency to update the date the record was accesed
132 -- and free text comment
133 ---------------------------------------------------------------------------
134 PROCEDURE complete_transfer(
135 p_api_version IN NUMBER,
136 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
137 p_interface_id IN NUMBER,
138 p_transfer_date IN DATE,
139 p_comments IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2);
143
144 ---------------------------------------------------------------------------
145 -- API to update the date, the external agency was notified about
146 -- intent to recall or recall, of the contract
147 ---------------------------------------------------------------------------
148 PROCEDURE complete_notify_ext_agncy(
149 p_api_version IN NUMBER,
150 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
151 p_interface_id IN NUMBER,
152 p_hst_id IN NUMBER,
153 p_notification_date IN DATE,
154 p_comments IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2);
158
159 ---------------------------------------------------------------------------
160 -- API to update the date the contract was actually recalled from the
161 -- external agency. Optionally, the ext_agncy id of the agency to transfer the
162 -- case to, after it has been recalled, can also be passed
163 ---------------------------------------------------------------------------
164 PROCEDURE recall_transfer(
165 p_api_version IN NUMBER,
166 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
167 p_interface_id IN NUMBER,
168 p_recall_date IN DATE,
169 p_comments IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
170 p_ext_agncy_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2);
174
175
176 ---------------------------------------------------------------------------
177 -- API to review the transfer of the contract to the external agency. It rescores
178 -- the case to which the contract belongs, checks for score progress and recalls
179 -- contracts with unsatisfactory score channges
180 ---------------------------------------------------------------------------
181 PROCEDURE review_transfer(
182 p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
184 p_oinv_rec IN oinv_rec_type,
185 p_iohv_rec IN iohv_rec_type,
186 x_oinv_rec OUT NOCOPY oinv_rec_type,
187 x_iohv_rec OUT NOCOPY iohv_rec_type,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2);
191
192 ---------------------------------------------------------------------------
193 -- API to create a followup task to review the transfer of the contract before recalling
194 -- from the external agency
195 ---------------------------------------------------------------------------
196 PROCEDURE create_followup(
197 p_api_version IN NUMBER,
198 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
199 p_oinv_rec IN oinv_rec_type,
200 p_iohv_rec IN iohv_rec_type,
201 p_task_name IN VARCHAR2,
202 p_description IN VARCHAR2,
203 p_start_date IN DATE DEFAULT SYSDATE,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2);
207
208 ---------------------------------------------------------------------------
209 -- Concurrent API to send out NOCOPY notifications to the customer about intent to
210 -- report to credit bureau
211 ---------------------------------------------------------------------------
212 PROCEDURE notify_customer(
213 errbuf OUT NOCOPY VARCHAR2,
214 retcode OUT NOCOPY NUMBER,
215 p_case_number IN VARCHAR2,
216 p_party_id IN NUMBER,
217 p_agent_id IN NUMBER,
218 p_content_id IN VARCHAR2,
219 p_from IN VARCHAR2,
220 p_subject IN VARCHAR2,
221 p_email IN VARCHAR2);
222
223 ---------------------------------------------------------------------------
224 -- Concurrent API to initiate the recall of contracts from external agencies.
225 -- This API creates pending notifications to be sent to the external agency
226 -- to inform them of intent to recall the contract.
227 ---------------------------------------------------------------------------
228 PROCEDURE notify_recall_external_agency(
229 errbuf OUT NOCOPY VARCHAR2,
230 retcode OUT NOCOPY NUMBER,
231 p_case_number IN VARCHAR2,
232 p_ext_agncy_id IN NUMBER,
233 p_comments IN VARCHAR2);
234
235 ---------------------------------------------------------------------------
236 -- Concurrent API to send out NOCOPY notifications to the external agency about
237 -- recall/intent to recall contract
238 ---------------------------------------------------------------------------
239 PROCEDURE notify_external_agency(
240 errbuf OUT NOCOPY VARCHAR2,
241 retcode OUT NOCOPY NUMBER,
242 p_case_number IN VARCHAR2,
243 p_ext_agncy_id IN NUMBER,
244 p_agent_id IN NUMBER,
245 p_content_id IN VARCHAR2,
246 p_from IN VARCHAR2,
247 p_subject IN VARCHAR2,
248 p_email IN VARCHAR2);
249
250 ---------------------------------------------------------------------------
251 -- Concurrent API to recall contracts from external agencies.
252 -- This API will recall contracts which the EA has been notified about
253 -- and send a notification to inform them about the recall of the contract.
254 ---------------------------------------------------------------------------
255 PROCEDURE recall_from_external_agency(
256 errbuf OUT NOCOPY VARCHAR2,
257 retcode OUT NOCOPY NUMBER,
258 p_case_number IN VARCHAR2,
259 p_ext_agncy_id IN NUMBER,
260 p_comments IN VARCHAR2);
261
262 PROCEDURE get_hst_info(
263 p_hst_id IN NUMBER,
264 x_action OUT NOCOPY VARCHAR2,
265 x_status OUT NOCOPY VARCHAR2,
266 x_return_status OUT NOCOPY VARCHAR2);
267
268 PROCEDURE get_party_email(
269 p_party_id IN NUMBER,
270 x_email OUT NOCOPY VARCHAR2,
271 x_return_status OUT NOCOPY VARCHAR2);
272
273 PROCEDURE get_ext_agncy_email(
274 p_ext_agncy_id IN NUMBER,
275 x_email OUT NOCOPY VARCHAR2,
276 x_return_status OUT NOCOPY VARCHAR2);
277
278 PROCEDURE get_external_agency(
279 p_oinv_rec IN oinv_rec_type,
280 p_iohv_rec IN iohv_rec_type,
281 x_ext_agncy_id OUT NOCOPY NUMBER,
282 x_return_status OUT NOCOPY VARCHAR2);
283
284 PROCEDURE get_contract_recall(
285 p_oinv_rec IN oinv_rec_type,
286 p_iohv_rec IN iohv_rec_type,
287 x_recall OUT NOCOPY VARCHAR2,
288 x_return_status OUT NOCOPY VARCHAR2);
289
290 PROCEDURE get_contract_delinquency_stat(
291 p_oinv_rec IN oinv_rec_type,
292 p_iohv_rec IN iohv_rec_type,
293 x_delinquency_status OUT NOCOPY VARCHAR2,
294 x_return_status OUT NOCOPY VARCHAR2);
295 END IEX_OPI_PVT;