[Home] [Help]
PACKAGE BODY: APPS.PV_OPPTY_ROUTING_LOG
Source
1 PACKAGE BODY PV_OPPTY_ROUTING_LOG as
2 /* $Header: pvxvorlb.pls 120.1 2006/03/10 15:02:31 amaram noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_OPPTY_ROUTING_LOG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_OPPTY_ROUTING_LOG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvorlb.pls';
19
20 G_USER_ID CONSTANT NUMBER := NVL(FND_GLOBAL.USER_ID, -1);
21 G_LOGIN_ID CONSTANT NUMBER := NVL(FND_GLOBAL.CONC_LOGIN_ID, -1);
22
23 ---------------------------------------------------------------------
24 -- FUNCTION
25 -- GET_ADDTNL_LOG_DETAILS
26 --
27 -- PURPOSE
28 -- Based on the OPPTY_ROUTING_log_id, event, LEAD_WORKFLOW_ID, LEAD_ASSIGNMENT_ID
29 -- This function returns the additional details about oppty history log
30 --
31 -- PARAMETERS
32 -- OPPTY_ROUTING_log_id, event, LEAD_WORKFLOW_ID, LEAD_ASSIGNMENT_ID
33 -- returns additional details as varchar2
34 --
35 -- NOTES
36 --
37 ---------------------------------------------------------------------
38
39 FUNCTION GET_ADDTNL_LOG_DETAILS ( p_oppty_routing_log_id NUMBER,
40 p_event VARCHAR2,
41 p_lead_workflow_id NUMBER,
42 p_lead_assignment_id NUMBER
43 )
44 RETURN VARCHAR2
45
46 AS
47
48 CURSOR lc_oppty_assign_addtnl_details (pc_oppty_routing_log_id NUMBER)
49 IS
50 SELECT
51 party.party_name,
52 decode (logs.BYPASS_CM_FLAG,
53 'N',fnd_message.get_string('PV','PV_ASSIGN_CM_APPRVL_REQ'),
54 'Y',fnd_message.get_string('PV','PV_ASSIGN_CM_APPRVL_NOT_REQ'),
55 fnd_message.get_string('PV','PV_ASSIGN_CM_APPRVL_NOTREQ')) bypass_message,
56 plkp1.meaning routing_type_meaning
57 FROM
58 pv_lead_assignments assgn,
59 pv_lead_workflows ldwf,
60 pv_partner_profiles partprof,
61 hz_parties party,
62 pv_oppty_routing_logs logs,
63 pv_lookups plkp1
64 WHERE
65 logs.oppty_routing_log_id= pc_oppty_routing_log_id
66 and ldwf.lead_workflow_id= logs.lead_workflow_id
67 --and ldwf.WF_ITEM_TYPE <> 'PVLEADAS'
68 and ldwf.WF_ITEM_TYPE = assgn.WF_ITEM_TYPE
69 and ldwf.WF_ITEM_KEY = assgn.WF_ITEM_KEY
70 and assgn.partner_id = partprof.partner_id
71 and partprof.partner_party_id = party.party_id
72 and plkp1.lookup_type (+)='PV_ASSIGNMENT_TYPE'
73 and plkp1.lookup_code (+) = logs.routing_type
74 ;
75 CURSOR lc_assign_rej_acc_addtnl_dtls (pc_lead_assignment_id NUMBER)
76 IS
77 SELECT
78 party.party_name
79 FROM
80 pv_lead_assignments assgn,
81 pv_partner_profiles partprof,
82 hz_parties party
83 WHERE
84 assgn.lead_assignment_id = pc_lead_assignment_id --10569
85 and assgn.partner_id = partprof.partner_id
86 and partprof.partner_party_id = party.party_id
87 ;
88 CURSOR lc_oppty_timeout_addtnl_dtls (pc_lead_assignment_id NUMBER)
89 IS
90 SELECT
91 party.party_name
92 FROM
93 pv_lead_assignments assgn,
94 pv_partner_profiles partprof,
95 hz_parties party
96 WHERE
97 assgn.lead_assignment_id = pc_lead_assignment_id --10569
98 and assgn.partner_id = partprof.partner_id
99 and partprof.partner_party_id = party.party_id
100 ;
101 CURSOR lc_assign_withdr_addtnl_dtls (pc_lead_assignment_id NUMBER)
102 IS
103 SELECT
104 party.party_name
105 FROM
106 pv_lead_assignments assgn,
107 pv_partner_profiles partprof,
108 hz_parties party
109 WHERE
110 assgn.lead_assignment_id = pc_lead_assignment_id --10569
111 and assgn.partner_id = partprof.partner_id
112 and partprof.partner_party_id = party.party_id
113 ;
114 CURSOR lc_oppty_withdr_addtnl_dtls (pc_lead_assignment_id NUMBER)
115 IS
116 SELECT
117 party.party_name
118 FROM
119 pv_lead_assignments assgn,
120 pv_partner_profiles partprof,
121 hz_parties party
122 WHERE
123 assgn.lead_assignment_id = pc_lead_assignment_id --10569
124 and assgn.partner_id = partprof.partner_id
125 and partprof.partner_party_id = party.party_id
126 ;
127 CURSOR lc_oppty_decl_addtnl_details (pc_oppty_routing_log_id NUMBER)
128 IS
129 select
130 logs.user_response,
131 logs.reason_code,
132 plkp1.meaning response_meaning,
133 plkp2.meaning reason_meaning
134
135 from
136 pv_oppty_routing_logs logs,
137 pv_lookups plkp1,
138 pv_lookups plkp2
139
140 where
141 logs.oppty_routing_log_id= pc_oppty_routing_log_id
142 and plkp1.lookup_type (+) = 'PV_ASSIGNMENT_STATUS'
143 and plkp1.lookup_code (+) = logs.user_response
144 and plkp2.lookup_type (+) = 'PV_REASON_CODES'
145 and plkp2.lookup_code (+) = logs.reason_code
146 ;
147 CURSOR lc_oppty_abdn_addtnl_details (pc_oppty_routing_log_id NUMBER)
148 IS
149 select
150 logs.user_response,
151 logs.reason_code,
152 plkp1.meaning response_meaning,
153 plkp2.meaning reason_meaning
154
155 from
156 pv_oppty_routing_logs logs,
157 pv_lookups plkp1,
158 pv_lookups plkp2
159
160 where
161 logs.oppty_routing_log_id= pc_oppty_routing_log_id
162 and plkp1.lookup_type (+) = 'PV_ASSIGNMENT_STATUS'
163 and plkp1.lookup_code (+) = logs.user_response
164 and plkp2.lookup_type (+) = 'PV_REASON_CODES'
165 and plkp2.lookup_code (+) = logs.reason_code
166 ;
167 CURSOR lc_assgn_fail_addtnl_details (pc_lead_workflow_id NUMBER)
168 IS
169 select
170 ldwf.failure_code,
171 ldwf.failure_message
172 from
173 pv_lead_workflows ldwf
174 where
175 ldwf.lead_workflow_id= pc_lead_workflow_id
176 ;
177
178 CURSOR lc_oppty_check_cm_timeout (pc_lead_assignment_id NUMBER)
179 IS
180 select resource_response
181 from pv_party_notifications
182 where lead_assignment_id=pc_lead_assignment_id
183 and notification_type='MATCHED_TO';
184
185
186 l_bypass_message VARCHAR2(200);
187 l_routing_type_meaning VARCHAR2(200) ;
188 l_addtnl_details VARCHAR2(32000) ;
189 l_cm_timeout VARCHAR2(1) := 'N';
190 l_lead_description VARCHAR2(240);
191
192 BEGIN
193
194 l_bypass_message := '';
195 l_routing_type_meaning := '';
196 l_addtnl_details := '';
197
198 begin
199 if(p_event= 'OPPTY_ASSIGN') then
200
201 for x in lc_oppty_assign_addtnl_details(pc_oppty_routing_log_id => p_oppty_routing_log_id)
202 loop
203 l_routing_type_meaning := x.routing_type_meaning;
204 l_bypass_message := x.bypass_message;
205 l_addtnl_details := l_addtnl_details || gc_partner_message || ': ' || x.party_name || '<br>';
206 end loop;
207
208 l_addtnl_details := gc_assignment_type_message || ': '|| l_routing_type_meaning
209 || '. <br>' || l_bypass_message || '. ' || '<br>' || l_addtnl_details;
210
211
212 elsif (p_event = 'ASSIGN_ACCEPT') then
213 for y in lc_oppty_check_cm_timeout(pc_lead_assignment_id => p_lead_assignment_id)
214 loop
215 if(y.resource_response= 'CM_TIMEOUT') then
216 l_cm_timeout := 'Y';
217 else
218 l_cm_timeout := 'N';
219 exit;
220 end if;
221
222 end loop;
223
224 if(l_cm_timeout = 'Y' ) then
225 select ldall.description
226 into l_lead_description
227 from as_leads_all ldall,
228 pv_lead_assignments ldass
229 where ldass.lead_id=ldall.lead_id and
230 ldass.lead_assignment_id=p_lead_assignment_id;
231
232 l_addtnl_details := l_addtnl_details || gc_oppty_cm_timeout_message || ': ' || l_lead_description || '.<br>';
233 end if;
234
235 for x in lc_assign_rej_acc_addtnl_dtls(pc_lead_assignment_id => p_lead_assignment_id)
236 loop
237 l_addtnl_details := l_addtnl_details || gc_partner_message || ': ' || x.party_name || '<br>';
238 end loop;
239
240 elsif (p_event = 'ASSIGN_REJECT') then
241
242 for x in lc_assign_rej_acc_addtnl_dtls(pc_lead_assignment_id => p_lead_assignment_id)
243 loop
244 l_addtnl_details := l_addtnl_details || gc_partner_message || ': ' || x.party_name || '<br>';
245 end loop;
246
247 elsif (p_event = 'ASSIGN_WITHDRAW') then
248
249 for x in lc_assign_withdr_addtnl_dtls(pc_lead_assignment_id => p_lead_assignment_id)
250 loop
251 l_addtnl_details := l_addtnl_details || gc_partner_message || ': ' || x.party_name || '<br>';
252 end loop;
253
254 elsif (p_event = 'OPPTY_WITHDRAW') then
255
256 for x in lc_oppty_withdr_addtnl_dtls(pc_lead_assignment_id => p_lead_assignment_id)
257 loop
258 l_addtnl_details := l_addtnl_details || gc_partner_message || ': ' || x.party_name || '<br>';
259 end loop;
260
261 elsif (p_event = 'OPPTY_ABANDON') then
262
263 for x in lc_oppty_abdn_addtnl_details(pc_oppty_routing_log_id => p_oppty_routing_log_id)
264 loop
265 l_addtnl_details := l_addtnl_details || gc_decline_reason_message || ': ' || x.reason_meaning || '<br>';
266 end loop;
267
268 elsif (p_event = 'OPPTY_ACCEPT') then
269 l_addtnl_details := ' ';
270
271 elsif (p_event = 'OPPTY_DECLINE') then
272
273 for x in lc_oppty_decl_addtnl_details(pc_oppty_routing_log_id => p_oppty_routing_log_id)
274 loop
275 l_addtnl_details := l_addtnl_details || gc_decline_reason_message || ': ' || x.reason_meaning || '<br>';
276 end loop;
277
278 elsif (p_event = 'ASSIGN_FAIL') then
279
280 for x in lc_assgn_fail_addtnl_details(pc_lead_workflow_id => p_lead_workflow_id)
281 loop
282 l_addtnl_details := l_addtnl_details || x.failure_code || '; ' || x.failure_message || '<br>';
283 end loop;
284
285 elsif (p_event = 'OPPTY_RECYCLE') then
286
287 for x in lc_oppty_timeout_addtnl_dtls(pc_lead_assignment_id => p_lead_assignment_id)
288 loop
289 l_addtnl_details := l_addtnl_details || gc_oppty_timeout_message || ': ' || x.party_name || '<br>';
290 end loop;
291
292 elsif (p_event = 'OPPTY_TAKEN') then
293 l_addtnl_details := ' ';
294 else
295 l_addtnl_details := ' ';
296 end if;
297
298
299 EXCEPTION
300 when others then
301 l_addtnl_details := ' ';
302
303 end;
304
305 --return substr(l_value, 1, length(l_value)-2);
306 return l_addtnl_details;
307
308
309 END GET_ADDTNL_LOG_DETAILS;
310
311
312
313
314
315
316 END PV_OPPTY_ROUTING_LOG;