[Home] [Help]
PACKAGE BODY: APPS.IEX_WF_DEL_STATUS_PUB
Source
1 PACKAGE BODY IEX_WF_DEL_STATUS_PUB AS
2 /* $Header: iexwfdub.pls 120.0 2004/01/24 03:31:19 appldev noship $ */
3 /*
4 * This procedure needs to be called with an itemtype and workflow process
5 * which'll launch workflow.
6 */
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_DEL_STATUS_WF_PUB';
9
10 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
11 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
12
13 PROCEDURE start_workflow
14 (
15 p_api_version IN NUMBER := 1.0,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_commit IN VARCHAR2 := FND_API.G_FALSE,
18 p_delinquency_id IN NUMBER,
19 p_repossession_id IN NUMBER,
20 p_litigation_id IN NUMBER,
21 p_writeoff_id IN NUMBER,
22 p_requester_id IN NUMBER,
23 p_requester_name IN VARCHAR2,
24 p_approver_id IN NUMBER,
25 p_approver_name IN VARCHAR2,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2)
29 IS
30 l_result VARCHAR2(10);
31 itemtype VARCHAR2(10);
32 itemkey VARCHAR2(30);
33 workflowprocess VARCHAR2(30);
34
35 l_error_msg VARCHAR2(2000);
36 l_return_status VARCHAR2(20);
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(2000);
39 l_api_name VARCHAR2(100) := 'START_WORKFLOW';
40 l_api_version_number CONSTANT NUMBER := 1.0;
41 BEGIN
42 -- Standard Start of API savepoint
43 SAVEPOINT START_WORKFLOW;
44
45 -- Standard call to check for call compatibility.
46 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
47 p_api_version,
48 l_api_name,
49 G_PKG_NAME)
50 THEN
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
52 END IF;
53
54
55 -- Initialize message list if p_init_msg_list is set to TRUE.
56 IF FND_API.to_Boolean( p_init_msg_list )
57 THEN
58 FND_MSG_PUB.initialize;
59 END IF;
60
61 -- Debug Message
62 -- IF PG_DEBUG < 10 THEN
63 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
64 IEX_DEBUG_PUB.logMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Public API: ' || l_api_name || ' start');
65 IEX_DEBUG_PUB.logMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
66 END IF;
67 -- Initialize API return status to SUCCESS
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 itemtype := 'IEXDELST';
71 workflowprocess := 'DEL_STATUS';
72
73 IF p_delinquency_id IS NULL THEN
74 null;
75 ELSIF p_repossession_id IS NOT NULL THEN
76 itemkey := 'REP'||p_repossession_id;
77 ELSIF p_litigation_id IS NOT NULL THEN
78 itemkey := 'LIT'||p_litigation_id;
79 ELSIF p_writeoff_id IS NOT NULL THEN
80 itemkey := 'WRI'||p_writeoff_id;
81 END IF;
82
83 wf_engine.createprocess (itemtype => itemtype,
84 itemkey => itemkey,
85 process => workflowprocess);
86
87 wf_engine.setitemattrnumber(itemtype => itemtype,
88 itemkey => itemkey,
89 aname => 'DELINQUENCY_ID',
90 avalue => P_DELINQUENCY_ID);
91
92 wf_engine.setitemattrnumber(itemtype => itemtype,
93 itemkey => itemkey,
94 aname => 'WRITEOFF_ID',
95 avalue => P_WRITEOFF_ID);
96
97 wf_engine.setitemattrtext(itemtype => itemtype,
98 itemkey => itemkey,
99 aname => 'REPOSSESSION_ID',
100 avalue => P_REPOSSESSION_ID);
101
102 wf_engine.setitemattrtext(itemtype => itemtype,
103 itemkey => itemkey,
104 aname => 'LITIGATION_ID',
105 avalue => P_LITIGATION_ID);
106
107 wf_engine.startprocess(itemtype => itemtype,
108 itemkey => itemkey);
109
110 wf_engine.ItemStatus(itemtype => ItemType,
111 itemkey => ItemKey,
112 status => l_return_status,
113 result => l_result);
114
115 if (l_return_status = 'COMPLETE') THEN
116 x_return_status := 'S';
117 commit;
118 else
119 x_return_status := 'E';
120 end if;
121
122 -- Debug Message
123 -- IF PG_DEBUG < 10 THEN
124 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
125 IEX_DEBUG_PUB.logMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'PUB: ' || l_api_name || ' end');
126 IEX_DEBUG_PUB.logMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'End time:'|| TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
127 END IF;
128
129 -- Standard call to get message count and if count is 1, get message info.
130 FND_MSG_PUB.Count_And_Get
131 ( p_count => x_msg_count,
132 p_data => x_msg_data
133 );
134
135 EXCEPTION
136 WHEN FND_API.G_EXC_ERROR THEN
137 ROLLBACK TO START_WORKFLOW;
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
141 ROLLBACK TO START_WORKFLOW;
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
144 WHEN OTHERS THEN
145 ROLLBACK TO START_WORKFLOW;
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
148 THEN
149 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
150 END IF;
151 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
152 ----------------------------------
153 END start_workflow;
154
155 ----------- procedure update_approval_status -----------------------------
156 PROCEDURE update_approval_status(itemtype IN varchar2,
157 itemkey IN varchar2,
158 actid IN number,
159 funcmode IN varchar2,
160 result OUT NOCOPY varchar2) is
161
162 l_responder varchar2(100);
163 l_text_value varchar2(2000);
164 l_delinquency_id number(30);
165 l_writeoff_id number(30);
166 l_litigation_id number(30);
167 l_repossession_id number(30);
168 l_api_name VARCHAR2(100) := 'update_approval_status';
169 l_errmsg_name VARCHAR2(30);
170 L_API_ERROR EXCEPTION;
171
172 BEGIN
173
174 if funcmode <> 'RUN' then
175 result := wf_engine.eng_null;
176 return;
177 end if;
178
179 l_writeoff_id := to_number(substr(itemkey, 2));
180
181 IF to_number(substr(itemkey, 0, 2)) = 'WRI' THEN
182 update IEX_WRITEOFFS
183 set SUGGESTION_APPROVED_FLAG = 'Y',
184 WRITEOFF_DATE = sysdate
185 where WRITEOFF_ID = to_number(substr(itemkey, 2));
186 ELSIF to_number(substr(itemkey, 0, 2)) = 'REP' THEN
187 update IEX_REPOSSESSIONS
188 set SUGGESTION_APPROVED_FLAG = 'Y',
189 REPOSSESSION_DATE = sysdate
190 where REPOSSESSION_ID = to_number(substr(itemkey, 2));
191 ELSIF to_number(substr(itemkey, 0, 2)) = 'LIT' THEN
192 update IEX_LITIGATIONS
193 set SUGGESTION_APPROVED_FLAG = 'Y'
194 where LITIGATION_ID = to_number(substr(itemkey, 2));
195 END IF;
196
197 COMMIT;
198
199 result := 'COMPLETE';
200
201 EXCEPTION
202 WHEN L_API_ERROR then
203 WF_CORE.Raise(l_errmsg_name);
204 WHEN OTHERS THEN
205 WF_CORE.Context('IEX_DEL_REQ_SERVICE_WF_PUB', 'Approval_status',
206 itemtype, itemkey, actid, funcmode);
207 RAISE;
208 END update_approval_status;
209
210 ----------- procedure update_rejection_status -----------------------------
211 procedure update_rejection_status(
212 itemtype IN varchar2,
213 itemkey IN varchar2,
214 actid IN number,
215 funcmode IN varchar2,
216 result OUT NOCOPY varchar2) is
217
218 l_responder varchar2(100);
219 l_text_value varchar2(2000);
220 l_writeoff_id number(30);
221 l_api_name VARCHAR2(100) := 'update_rejection_status';
222 l_errmsg_name VARCHAR2(30);
223 L_API_ERROR EXCEPTION;
224
225 BEGIN
226
227 if funcmode <> 'RUN' then
228 result := wf_engine.eng_null;
229 return;
230 end if;
231
232 l_writeoff_id := to_number(substr(itemkey, 2));
233
234 IF to_number(substr(itemkey, 0, 2)) = 'WRI' THEN
235 update IEX_WRITEOFFS
236 set SUGGESTION_APPROVED_FLAG = 'N',
237 WRITEOFF_DATE = sysdate
238 where WRITEOFF_ID = to_number(substr(itemkey, 2));
239 ELSIF to_number(substr(itemkey, 0, 2)) = 'REP' THEN
240 update IEX_REPOSSESSIONS
241 set SUGGESTION_APPROVED_FLAG = 'N',
242 REPOSSESSION_DATE = sysdate
243 where REPOSSESSION_ID = to_number(substr(itemkey, 2));
244 ELSIF to_number(substr(itemkey, 0, 2)) = 'LIT' THEN
245 update IEX_LITIGATIONS
246 set SUGGESTION_APPROVED_FLAG = 'N'
247 where LITIGATION_ID = to_number(substr(itemkey, 2));
248 END IF;
249
250 COMMIT;
251
252 result := 'COMPLETE';
253
254 EXCEPTION
255 WHEN L_API_ERROR then
256 WF_CORE.Raise(l_errmsg_name);
257 WHEN OTHERS THEN
258 WF_CORE.Context('IEX_DEL_REQ_SERVICE_WF_PUB', 'Approval_status',
259 itemtype, itemkey, actid, funcmode);
260 RAISE;
261 END update_rejection_status;
262
263 END IEX_WF_DEL_STATUS_PUB;