[Home] [Help]
PACKAGE BODY: APPS.QPR_WKFL_UTIL
Source
1 PACKAGE BODY QPR_WKFL_UTIL AS
2 /* $Header: QPRUWKFLB.pls 120.10 2008/05/30 11:41:10 vinnaray ship $ */
3
4 QPR_NTFN_ITM_TYPE varchar2(30) := 'QPRDEAL';
5 g_cb_nfn_usr_ctr number ;
6 g_cb_usr_tbl char_type;
7 g_apst_nfn_usr_ctr number;
8 g_apst_usr_tbl char_type;
9
10 NL varchar2(1) := fnd_global.newline;
11 L_TABLE_STYLE VARCHAR2(100) := ' cellspacing="1" cellpadding="3" border="0" width="100%" bgcolor="white"';
12
13 L_TABLE_BOR_STYLE VARCHAR2(100) := ' cellspacing="1" cellpadding="3" border="1" width="100%" bgcolor="white"';
14
15 L_TABLE_HEADER_STYLE VARCHAR2(100) := 'bgcolor="#cfe0f1" align=left';
16
17 L_TABLE_LABEL_STYLE VARCHAR2(100) := ' bgcolor="#cfe0f1" align=right ';
18
19 L_TABLE_CELL_STYLE VARCHAR2(100) := ' bgcolor="#f7f7e7" nowrap align=left ';
20
21 L_TABLE_CELL_WRAP_STYLE VARCHAR2(100) := ' align=left bgcolor="#f7f7e7" ';
22
23
24 type char240_type is table of varchar2(240) index by pls_integer;
25 type num_type is table of number index by pls_integer;
26
27 type LINE_DET_TYPE is record (
28 LINE_NUM char240_type,
29 PR_SEG_DESC char240_type,
30 PDT_DESC char240_type,
31 UOM char240_type,
32 ORD_QTY num_type,
33 LISTPRICE num_type,
34 PROPOSED_PRICE num_type,
35 REVISED_OQ num_type,
36 ONINV_PERC num_type,
37 INVPRICE num_type,
38 RECOMMEND_PRICE num_type,
39 INVPRICE_REV num_type,
40 TOTOFFADJ num_type,
41 POC_REV num_type,
42 UNIT_COST num_type,
43 MARGIN_AMT num_type,
44 MARGIN_PERC num_type,
45 LINE_SCORE num_type);
46
47 procedure approve_deal(
48 item_type in varchar2,
49 itemkey in varchar2,
50 actid in number,
51 funcmode in varchar2,
52 resultout out nocopy varchar2) is
53 l_resp_hdr_id number;
54 l_resp_user varchar2(500);
55 l_comments varchar2(2000);
56 l_app_complete varchar2(30);
57 l_ret_code varchar2(240):= fnd_api.g_ret_sts_success ;
58 begin
59
60 if funcmode = 'RUN' then
61
62 l_resp_hdr_id := wf_engine.GetItemAttrNumber(item_type, itemkey,
63 'QPR_PN_RESP_ID');
64 l_resp_user := wf_engine.GetItemAttrText(item_type, itemkey,
65 'QPR_FWD_TO_USR');
66
67 l_comments := wf_engine.GetItemAttrText(item_type, itemkey,
68 'QPR_COMMENTS');
69
70 qpr_deal_approvals_pvt.process_user_action(l_resp_hdr_id,
71 l_resp_user, 'APPROVE',
72 l_comments, true,l_app_complete,
73 l_ret_code
74 );
75 if l_ret_code = fnd_api.g_ret_sts_success then
76 resultout := 'COMPLETE';
77 else
78 resultout := 'ERROR';
79 end if;
80 else
81 resultout := 'COMPLETE';
82 end if;
83
84 exception
85 when others then
86 WF_CORE.CONTEXT ('QPR_WKFL_UTIL', 'approve_deal',
87 item_type,itemkey, to_char(actid), funcmode);
88 raise;
89 end approve_deal;
90
91 procedure reject_deal(
92 item_type in varchar2,
93 itemkey in varchar2,
94 actid in number,
95 funcmode in varchar2,
96 resultout out nocopy varchar2) is
97 l_resp_hdr_id number;
98 l_resp_user varchar2(500);
99 l_comments varchar2(2000);
100 l_app_complete varchar2(30);
101 l_ret_code varchar2(240):= fnd_api.g_ret_sts_success ;
102 begin
103
104 if funcmode = 'RUN' then
105
106 l_resp_hdr_id := wf_engine.GetItemAttrNumber(item_type, itemkey,
107 'QPR_PN_RESP_ID');
108 l_resp_user := wf_engine.GetItemAttrText(item_type, itemkey,
109 'QPR_FWD_TO_USR');
110
111 l_comments := wf_engine.GetItemAttrText(item_type, itemkey,
112 'QPR_COMMENTS');
113 qpr_deal_approvals_pvt.process_user_action(l_resp_hdr_id,
114 l_resp_user, 'REJECT',
115 l_comments,true, l_app_complete,
116 l_ret_code
117 );
118 if l_ret_code = fnd_api.g_ret_sts_success then
119 resultout := 'COMPLETE';
120 else
121 resultout := 'ERROR';
122 end if;
123 else
124 resultout := 'COMPLETE';
125 end if;
126 exception
127 when others then
128 WF_CORE.CONTEXT ('QPR_WKFL_UTIL', 'reject_deal',
129 item_type,itemkey, to_char(actid), funcmode);
130 raise;
131 end reject_deal;
132
133
134 procedure set_callback_nfn_details(
135 item_type in varchar2,
136 itemkey in varchar2,
137 actid in number,
138 funcmode in varchar2,
139 resultout out nocopy varchar2) is
140
141 l_nid number;
142 l_response_id number;
143 l_to_user varchar2(240);
144 begin
145
146 if (funcmode = 'RUN') then
147 l_nid := wf_engine.context_nid;
148 if g_cb_usr_tbl is not null then
149 if g_cb_nfn_usr_ctr = -1 then
150 g_cb_nfn_usr_ctr := g_cb_usr_tbl.first;
151 else
152 g_cb_nfn_usr_ctr := g_cb_nfn_usr_ctr + 1;
153 end if;
154 end if;
155
156 if g_cb_usr_tbl.exists(g_cb_nfn_usr_ctr) then
157 l_to_user := g_cb_usr_tbl(g_cb_nfn_usr_ctr);
158 wf_engine.SetItemAttrText(item_type, itemkey,
159 'QPR_FWD_TO_USR' , l_to_user);
160
161 resultout := 'COMPLETE:COMPLETE';
162 else
163 resultout := 'ERROR';
164 end if;
165 elsif (funcmode = 'SKIP') then
166 resultout := wf_engine.eng_noskip;
167 else -- funcmode = 'CANCEL' / 'RETRY'
168 resultout := 'COMPLETE:COMPLETE';
169 end if;
170
171 exception
172 when others then
173 WF_CORE.CONTEXT ('QPR_WKFL_UTIL', 'set_callback_nfn_details',
174 item_type,itemkey, to_char(actid), funcmode);
175 raise;
176 end set_callback_nfn_details;
177
178 procedure set_app_status_nfn_details(
179 item_type in varchar2,
180 itemkey in varchar2,
181 actid in number,
182 funcmode in varchar2,
183 resultout out nocopy varchar2) is
184
185 l_nid number;
186 l_response_id number;
187 l_to_user varchar2(240);
188 begin
189
190 if (funcmode = 'RUN') then
191 l_nid := wf_engine.context_nid;
192
193 if g_apst_usr_tbl is not null then
194 if g_apst_nfn_usr_ctr = -1 then
195 g_apst_nfn_usr_ctr := g_apst_usr_tbl.first;
196 else
197 g_apst_nfn_usr_ctr := g_apst_nfn_usr_ctr + 1;
198 end if;
199 end if;
200
201 if g_apst_usr_tbl.exists(g_apst_nfn_usr_ctr) then
202 l_to_user := g_apst_usr_tbl(g_apst_nfn_usr_ctr);
203 wf_engine.SetItemAttrText(item_type, itemkey,
204 'QPR_FWD_TO_USR' , l_to_user);
205
206 resultout := 'COMPLETE:COMPLETE';
207 else
208 resultout := 'ERROR';
209 end if;
210 elsif (funcmode = 'SKIP') then
211 resultout := wf_engine.eng_noskip;
212 else -- funcmode = 'CANCEL' / 'RETRY'
213 resultout := 'COMPLETE:COMPLETE';
214 end if;
215
216 exception
217 when others then
218 WF_CORE.CONTEXT ('QPR_WKFL_UTIL', 'set_app_status_nfn_details',
219 item_type,itemkey, to_char(actid), funcmode);
220 raise;
221 end set_app_status_nfn_details;
222
223 function print_heading(l_text in varchar2) return varchar2 is
224
225 l_document varchar2(1000) := '';
226
227 NL VARCHAR2(1) := fnd_global.newline;
228 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
229
230 begin
231
232 l_document := '<TABLE width="100%" border=0 cellpadding=0 cellspacing=0 SUMMARY="">';
233 l_document := l_document || '<TR>'||NL;
234 l_document := l_document || '<TD class=subheader1><B>'|| l_text;
235 l_document := l_document || '</B></TD></TR>';
236
237 -- horizontal line
238 l_document := l_document || '<TR>' || NL;
239 l_document := l_document || '<TD colspan=2 height=1 bgcolor=#cccc99><img src=' || l_base_href
240 || '/OA_MEDIA/FNDITPNT.gif ALT=""></TD></TR>';
241
242 l_document := l_document || '<TR><TD colspan=2 height=5> </TR></TABLE>' || NL;
243
244 return l_document;
245
246 end print_heading;
247
248 function get_table_label(p_content in varchar2) return varchar2
249 is
250 begin
251 return( '<TD '|| L_TABLE_LABEL_STYLE || '> <B>' || p_content || '</B> </TD>' );
252 end get_table_label;
253
254 function get_table_cell(p_content in varchar2, p_wrap in boolean default false)
255 return varchar2 is
256 begin
257 if p_wrap then
258 return('<TD ' || L_TABLE_CELL_WRAP_STYLE ||'>' || p_content || ' </TD>');
259 else
260 return('<TD ' || L_TABLE_CELL_STYLE ||'>' || p_content || ' </TD>');
261 end if;
262 end get_table_cell;
263
264 function get_table_header(p_content in varchar2) return varchar2
265 is
266 begin
267 return('<TD '|| L_TABLE_HEADER_STYLE || '><B>' || p_content || '</B> </TD>');
268 end get_table_header;
269
270 procedure form_deal_doc(p_response_header_id in number,
271 p_blob in out nocopy clob) is
272 l_hdr_score qpr_pn_response_hdrs.DEAL_HEADER_SCORE%type;
273 l_version_no qpr_pn_response_hdrs.VERSION_NUMBER%type;
274 l_ref_name varchar2(10000);
275 l_cus_name qpr_pn_request_hdrs_b.CUSTOMER_LONG_DESC%type;
276 l_rep_name qpr_pn_request_hdrs_b.SALES_REP_LONG_DESC%type;
277 l_hdr_curr qpr_pn_request_hdrs_b.currency_short_desc%type;
278 l_hdr_status qpr_pn_response_hdrs.response_status%type;
279 l_description qpr_pn_response_hdrs.description%type;
280 l_req_date qpr_pn_request_hdrs_b.deal_creation_date%type;
281 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
282
283 l_document varchar2(32000);
284 l_request_hdr_id number;
285
286 t_line_det LINE_DET_TYPE;
287
288 b_first boolean := true;
289 l_rows number := 1000;
290
291 cursor c_line_details(p_precision number) is
292 select source_request_line_number,
293 nvl((select name from qpr_pr_segments_all_vl
294 where pr_segment_id = l.pr_segment_id
295 and rownum < 2), l.pr_segment_id) pr_segment,
296 l.inventory_item_short_desc,
297 l.uom_code,
298 l.ordered_qty,
299 round(pr.listprice, p_precision),
300 round(l.proposed_price, p_precision),
301 l.revised_oq,
302 round(decode(pr.listpricerev, 0, 0, (100 * pd.totoninv/pr.listpricerev)), p_precision),
303 round(pr.invprice, p_precision),
304 round(l.recommended_price, p_precision),
305 round(pr.invpricerev, p_precision),
306 round(pd.totoffinv, p_precision),
307 round(pr.pocpricerev,p_precision ),
308 round(pd.unit_cost, p_precision),
309 round(pr.pocmarginamnt, p_precision),
310 round(pr.pocmarginperc, p_precision),
311 round(l.line_pricing_score, 2)
312 from qpr_pn_lines l,
313 (select response_header_id, pn_line_id,
314 sum(decode(pn_pr_type_id, 1, amount, 0 )) listpricerev,
315 sum(decode(pn_pr_type_id, 2, amount, 0 )) invpricerev,
316 sum(decode(pn_pr_type_id, 3, amount, 0 )) pocpricerev,
317 sum(decode(pn_pr_type_id, 4, amount, 0 )) pocmarginamnt,
318 sum(decode(pn_pr_type_id, 1, unit_price, 0 )) listprice,
319 sum(decode(pn_pr_type_id, 2, unit_price, 0 )) invprice,
320 sum(decode(pn_pr_type_id, 3, unit_price, 0 )) pocprice,
321 sum(decode(pn_pr_type_id, 4, unit_price, 0 )) pocmargin,
322 sum(decode(pn_pr_type_id, 2, percent_price, 0 )) invpriceperc,
323 sum(decode(pn_pr_type_id, 3, percent_price, 0 )) pocpriceperc,
324 sum(decode(pn_pr_type_id, 4, percent_price, 0 )) pocmarginperc
325 from qpr_pn_prices
326 where pn_line_id is not null
327 group by response_header_id, pn_line_id) pr,
328 (select response_header_id, pn_line_id,
329 sum(decode(erosion_type, 'ALL_COST', erosion_per_unit, 0 )) unit_cost,
330 sum(decode(erosion_type, 'ALL_ONINVOICE',erosion_per_unit, 0) ) unit_oninv,
331 sum(decode(erosion_type, 'ALL_OFFINVOICE', erosion_per_unit, 0 )) unit_offinv,
332 sum(decode(erosion_type, 'ALL_COST', erosion_amount, 0 )) totcost,
333 sum(decode(erosion_type, 'ALL_ONINVOICE',erosion_amount, 0) ) totoninv,
334 sum(decode(erosion_type, 'ALL_OFFINVOICE', erosion_amount, 0 )) totoffinv
335 from qpr_pn_pr_details
336 where pn_line_id is not null
337 and erosion_type like 'ALL_%'
338 group by response_header_id, pn_line_id) pd
339 where l.response_header_id = pd.response_header_id
340 and l.pn_line_id = pd.pn_line_id
341 and l.response_header_id = pr.response_header_id
342 and l.pn_line_id = pr.pn_line_id
343 and l.response_header_id = p_response_header_id
344 order by l.response_header_id, l.pn_line_id;
345
346 l_std_precision number;
347 l_extnd_precision number;
348 l_min_amnt number;
349 l_precision number;
350 begin
351
352 begin
353 select round(resp.deal_header_score,2), resp.version_number,
354 req.reference_name, req.customer_long_desc,
355 req.sales_rep_long_desc, req.currency_short_desc,
356 (select meaning from qpr_lookups
357 where lookup_type = 'PN_STATUS'
358 and lookup_code = resp.response_status and rownum < 2),
359 resp.description, req.deal_creation_date , req.request_header_id
360 into l_hdr_score, l_version_no, l_ref_name,
361 l_cus_name, l_rep_name, l_hdr_curr, l_hdr_status,
362 l_description, l_req_date, l_request_hdr_id
363 from qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_vl req
364 where resp.response_header_id = p_response_header_id
365 and resp.request_header_id = req.request_header_id;
366 exception
367 when no_data_found then
368 return;
369 end;
370
371 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href ||
372 '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
373
374 l_document := l_document || print_heading(fnd_message.get_String('QPR', 'QPR_DEAL_SUMMARY'));
375 l_document := l_document || '<TABLE' || L_TABLE_STYLE || ' summary="">';
376 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_REQUEST_NO')) || NL;
377 l_document := l_document || get_table_cell(l_request_hdr_id) || '</TR>' || NL;
378 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_CUSTOMER')) || NL;
379 l_document := l_document || get_table_cell(l_cus_name) || '</TR>' || NL;
380 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR', 'QPR_REFERENCE')) || NL;
381 l_document := l_document || get_table_cell(l_ref_name) || '</TR>' || NL;
382 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_STATUS')) || NL;
383 l_document := l_document || get_table_cell(l_hdr_status) || '</TR>' || NL;
384 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_string('QPR', 'QPR_SCORE')) || NL;
385 l_document := l_document || get_table_cell(l_hdr_score) || '</TR>' || NL;
386 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_SCENARIO')) || NL;
387 l_document := l_document || get_table_cell(l_version_no) || '</TR>' || NL;
388 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_string('QPR', 'QPR_REQUESTOR')) || NL;
389 l_document := l_document || get_table_cell(l_rep_name) || '</TR>' || NL;
390 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_string('QPR', 'QPR_REQ_DATE')) || NL;
391 l_document := l_document || get_table_cell(l_req_date) || '</TR>' || NL;
392 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR', 'QPR_CURRENCY')) || NL;
393 l_document := l_document || get_table_cell(l_hdr_curr) || '</TR>' || NL;
394 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR', 'QPR_DEAL_DESC'))|| NL;
395 l_document := l_document || get_table_cell(l_description) || '</TR>' || NL;
396 l_document := l_document ||'</TABLE>';
397 l_document := l_document ||'</P>';
398
399 Wf_notification.WriteToClob(p_blob, l_document);
400
401 l_document := '';
402
403 fnd_currency.get_info(l_hdr_curr, l_std_precision, l_extnd_precision,
404 l_min_amnt);
405 if nvl(fnd_profile.value('QPR_REPORT_ROUNDING_PRECISION'),
406 'STANDARD') = 'STANDARD' then
407 l_precision := l_std_precision;
408 else
409 l_precision := l_extnd_precision;
410 end if;
411
412 open c_line_details(l_precision);
413 loop
414 fetch c_line_details bulk collect into t_line_det limit l_rows;
415 exit when t_line_det.LINE_NUM.count = 0;
416 if b_first then
417 l_document := l_document || '<TABLE' || L_TABLE_BOR_STYLE || ' summary="">';
418 l_document := l_document || '<TR>';
419 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_LINE_NO'))|| NL;
420 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_SEGMENT'))|| NL;
421 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_PRODUCT'))|| NL;
422 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_UOM'))|| NL;
423 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_PROPOSED_VOL'))|| NL;
424 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_LIST_PRICE'))|| NL;
425 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_PROPOSED_PRICE'))|| NL;
426 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_REVISED_VOLUME'))|| NL;
427 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_ONADJ_PERC'))|| NL;
428 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_REVISED_PRICE'))|| NL;
429 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_RECOMMEND_PRICE'))|| NL;
430 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_INV_REV'))|| NL;
431 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_OFFADJ_AMT'))|| NL;
432 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_POC_REV'))|| NL;
433 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_UNIT_COST'))|| NL;
434 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_MAR_AMT'))|| NL;
435 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_MAR_PERC'))|| NL;
436 l_document := l_document || get_table_header(fnd_message.get_String('QPR','QPR_LINE_SCORE'))|| NL;
437 l_document := l_document || '</TR>';
438
439 Wf_notification.WriteToClob(p_blob, l_document);
440 b_first := false;
441 l_document := '';
442 end if;
443
444 for i in t_line_det.LINE_NUM.first..t_line_det.LINE_NUM.last loop
445 l_document := '';
446 l_document := l_document || '<TR>';
447 l_document := l_document || get_table_cell(t_line_det.LINE_NUM(i), true) || NL;
448 l_document := l_document || get_table_cell(t_line_det.PR_SEG_DESC(i), true) || NL;
449 l_document := l_document || get_table_cell(t_line_det.PDT_DESC(i), true) || NL;
450 l_document := l_document || get_table_cell(t_line_det.UOM(i), true) || NL;
451 l_document := l_document || get_table_cell(t_line_det.ORD_QTY(i), true) || NL;
452 l_document := l_document || get_table_cell(t_line_det.LISTPRICE(i), true) || NL;
453 l_document := l_document || get_table_cell(t_line_det.PROPOSED_PRICE(i), true) || NL;
454 l_document := l_document || get_table_cell(t_line_det.REVISED_OQ(i), true) || NL;
455 l_document := l_document || get_table_cell(t_line_det.ONINV_PERC(i), true) || NL;
456 l_document := l_document || get_table_cell(t_line_det.INVPRICE(i), true) || NL;
457 l_document := l_document || get_table_cell(t_line_det.RECOMMEND_PRICE(i), true) || NL;
458 l_document := l_document || get_table_cell(t_line_det.INVPRICE_REV(i), true) || NL;
459 l_document := l_document || get_table_cell(t_line_det.TOTOFFADJ(i), true) || NL;
460 l_document := l_document || get_table_cell(t_line_det.POC_REV(i), true) || NL;
461 l_document := l_document || get_table_cell(t_line_det.UNIT_COST(i), true) || NL;
462 l_document := l_document || get_table_cell(t_line_det.MARGIN_AMT(i), true) || NL;
463 l_document := l_document || get_table_cell(t_line_det.MARGIN_PERC(i), true) || NL;
464 l_document := l_document || get_table_cell(t_line_det.LINE_SCORE(i), true) || NL;
465 l_document := l_document || '</TR>';
466
467 Wf_notification.WriteToClob(p_blob, l_document);
468 end loop;
469
470 t_line_det.LINE_NUM.delete;
471 t_line_det.PR_SEG_DESC.delete;
472 t_line_det.PDT_DESC.delete;
473 t_line_det.UOM.delete;
474 t_line_det.ORD_QTY.delete;
475 t_line_det.LISTPRICE.delete;
476 t_line_det.PROPOSED_PRICE.delete;
477 t_line_det.REVISED_OQ.delete;
478 t_line_det.ONINV_PERC.delete;
479 t_line_det.INVPRICE.delete;
480 t_line_det.RECOMMEND_PRICE.delete;
481 t_line_det.INVPRICE_REV.delete;
482 t_line_det.TOTOFFADJ.delete;
483 t_line_det.POC_REV.delete;
484 t_line_det.UNIT_COST.delete;
485 t_line_det.MARGIN_AMT.delete;
486 t_line_det.MARGIN_PERC.delete;
487 t_line_det.LINE_SCORE.delete;
488 end loop;
489
490 -- if line was never executed then b_first will be true
491 -- we need not add closing tag
492 if not b_first then
493 Wf_notification.WriteToClob(p_blob, '</TABLE>');
494 end if;
495 end form_deal_doc;
496
497 procedure show_deal_details(document_id in varchar2,
498 display_type in varchar2,
499 document in out nocopy varchar2,
500 document_type in out nocopy varchar2) is
501
502
503 l_resp_hdr_id number;
504 l_document varchar2(32000) := '';
505
506 l_hdr_score qpr_pn_response_hdrs.DEAL_HEADER_SCORE%type;
507 l_version_no qpr_pn_response_hdrs.VERSION_NUMBER%type;
508 l_ref_name varchar2(10000);
509 l_cus_name qpr_pn_request_hdrs_b.CUSTOMER_LONG_DESC%type;
510 l_rep_name qpr_pn_request_hdrs_b.SALES_REP_LONG_DESC%type;
511 l_hdr_curr qpr_pn_request_hdrs_b.currency_short_desc%type;
512 l_hdr_status qpr_pn_response_hdrs.response_status%type;
513 l_description qpr_pn_response_hdrs.description%type;
514 l_req_date qpr_pn_request_hdrs_b.deal_creation_date%type;
515
516 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
517 l_url varchar2(1000);
518 l_hdr_msg varchar2(2000);
519 l_request_hdr_id number;
520 begin
521
522 l_resp_hdr_id := to_number(document_id);
523 begin
524 select round(resp.deal_header_score, 2), resp.version_number,
525 req.reference_name, req.customer_long_desc,
526 req.sales_rep_long_desc, req.currency_short_desc,
527 (select meaning from qpr_lookups
528 where lookup_type = 'PN_STATUS'
529 and lookup_code = resp.response_status and rownum < 2),
530 resp.description, req.deal_creation_date ,
531 resp.request_header_id
532 into l_hdr_score, l_version_no, l_ref_name,
533 l_cus_name, l_rep_name, l_hdr_curr, l_hdr_status,
534 l_description, l_req_date, l_request_hdr_id
535 from qpr_pn_response_hdrs resp, qpr_pn_request_hdrs_vl req
536 where resp.response_header_id = l_resp_hdr_id
537 and resp.request_header_id = req.request_header_id;
538 exception
539 when no_data_found then
540 return;
541 end;
542
543
544 l_url := l_base_href ||
545 '/OA_HTML/OA.jsp?OAFunc=QPR_DEAL_WORKBENCH&RESPONSE_HEADER_ID=' || l_resp_hdr_id || '&READ_ONLY=True&RESET=True';
546
547 /*
548 fnd_message.set_name('QPR', 'QPR_DEAL_APP');
549 fnd_message.set_token('DEAL_ID', l_resp_hdr_id);
550 l_hdr_msg := fnd_message.get;*/
551
552 if display_type = 'text/html' then
553 -- l_document := l_document || '<BR>' || l_hdr_msg || NL;
554 l_document := l_document || '<BR>';
555 l_document := l_document || NL || '<!-- DEAL SUMMARY -->'|| NL || NL || '<P>';
556 l_document := l_document || print_heading(fnd_message.get_String('QPR', 'QPR_DEAL_SUMMARY'));
557 l_document := l_document || '<TABLE' || L_TABLE_STYLE || ' summary="">';
558 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_REQUEST_NO')) || NL;
559 l_document := l_document || get_table_cell(l_request_hdr_id) || '</TR>' || NL;
560 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_CUSTOMER')) || NL;
561 l_document := l_document || get_table_cell(l_cus_name) || '</TR>' || NL;
562 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR', 'QPR_REFERENCE')) || NL;
563 l_document := l_document || get_table_cell(l_ref_name) || '</TR>' || NL;
564 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_STATUS')) || NL;
565 l_document := l_document || get_table_cell(l_hdr_status) || '</TR>' || NL;
566 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_string('QPR', 'QPR_SCORE')) || NL;
567 l_document := l_document || get_table_cell(l_hdr_score) || '</TR>' || NL;
568 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR','QPR_SCENARIO')) || NL;
569 l_document := l_document || get_table_cell(l_version_no) || '</TR>' || NL;
570 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_string('QPR', 'QPR_REQUESTOR')) || NL;
571 l_document := l_document || get_table_cell(l_rep_name) || '</TR>' || NL;
572 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_string('QPR', 'QPR_REQ_DATE')) || NL;
573 l_document := l_document || get_table_cell(l_req_date) || '</TR>' || NL;
574 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR', 'QPR_CURRENCY')) || NL;
575 l_document := l_document || get_table_cell(l_hdr_curr) || '</TR>' || NL;
576 l_document := l_document || '<TR>' || get_table_label(fnd_message.get_String('QPR', 'QPR_DEAL_DESC'))|| NL;
577 l_document := l_document || get_table_cell(l_description) || '</TR>' || NL;
578 l_document := l_document ||'</TABLE>';
579 l_document := l_document ||'</P>';
580 l_document := l_document || '<P>' || '<a href="' || l_url || '">' || fnd_message.get_String('QPR', 'QPR_DEAL_URL')||'<a>';
581 l_document := l_document || '<BR>';
582 document := l_document;
583 else
584 l_document := l_document || NL;
585 -- l_document := l_document || l_hdr_msg || NL || NL;
586 l_document := l_document || fnd_message.get_String('QPR', 'QPR_DEAL_SUMMARY') || NL;
587 l_document := l_document || fnd_message.get_String('QPR','QPR_REQUEST_NO') || ': ' || l_request_hdr_id || NL;
588 l_document := l_document || fnd_message.get_String('QPR','QPR_CUSTOMER') || ': ' || l_cus_name || NL;
589 l_document := l_document || fnd_message.get_String('QPR', 'QPR_REFERENCE') || ': ' || l_ref_name || NL;
590 l_document := l_document || fnd_message.get_String('QPR','QPR_STATUS') || ': ' || l_hdr_status || NL;
591 l_document := l_document || fnd_message.get_string('QPR', 'QPR_SCORE') || ': ' || l_hdr_score || NL;
592 l_document := l_document || fnd_message.get_String('QPR','QPR_SCENARIO') || ': ' || l_version_no|| NL;
593 l_document := l_document || fnd_message.get_string('QPR', 'QPR_REQUESTOR') || ': ' || l_rep_name|| NL;
594 l_document := l_document || fnd_message.get_string('QPR', 'QPR_REQ_DATE') || ': ' || l_req_date || NL;
595 l_document := l_document || fnd_message.get_String('QPR', 'QPR_CURRENCY') || ': ' || l_hdr_curr || NL;
596 l_document := l_document || fnd_message.get_String('QPR', 'QPR_DEAL_DESC')|| ': ' || l_description || NL;
597 l_document := l_document || fnd_message.get_String('QPR', 'QPR_DEAL_URL')|| ':' || l_url || NL;
598 document := l_document;
599 end if;
600
601 exception
602 when others then
603 wf_core.context('qpr_wkfl_util'
604 ,'show_deal_details'
605 ,document_id
606 ,display_type);
607 raise;
608 end show_deal_details;
609
610 procedure attach_deal_details (
611 document_id in varchar2,
612 display_type in varchar2,
613 document in out nocopy clob,
614 document_type in out nocopy varchar2
615 ) is
616 lob_id number;
617 bdoc blob;
618 content_type varchar2(100);
619 filename varchar2(300);
620 begin
621 lob_id := to_number(document_id);
622
623 document_type := Wf_Notification.doc_html;
624
625 form_deal_doc(lob_id, document);
626
627 exception
628 when others then
629 wf_core.context('qpr_wkfl_util'
630 ,'attach_deal_details'
631 ,document_id
632 ,display_type);
633 raise;
634 end attach_deal_details;
635
636 procedure invoke_toapp_nfn_process(p_response_id in number,
637 p_fwd_to_user in varchar2,
638 retcode out nocopy number,
639 errbuf out nocopy varchar2) is
640 l_item_key varchar2(240);
641 l_process_name varchar2(240) := 'QPRTAPPNFNP';
642 l_current_user varchar2(500);
643 l_deal_url varchar2(1000);
644 l_request_hdr_id number;
645 l_version_no number;
646 l_init_key varchar2(240);
647 l_end_date date;
648 l_ct number;
649 begin
650
651 select user_name into l_current_user
652 from fnd_user
653 where user_id = fnd_global.user_id;
654
655 select request_header_id, version_number
656 into l_request_hdr_id, l_version_no
657 from qpr_pn_response_hdrs
658 where response_header_id = p_response_id
659 and rownum < 2;
660
661 l_init_key := 'TOAPP_' || p_response_id || p_fwd_to_user;
662
663 begin
664 select item_key, end_date
665 into l_item_key, l_end_date
666 from
667 (select item_key, end_date
668 from wf_items where item_type = QPR_NTFN_ITM_TYPE
669 and item_key like (l_init_key || '%')
670 order by begin_date desc)
671 where rownum < 2;
672 if l_end_date is null then
673 retcode := 2;
674 errbuf := 'Unable to invoke workflow notification process.' ||
675 'Active notification exists.';
676 return;
677 else
678 l_ct := to_number(nvl(substrb(l_item_key, length(l_init_key)+2), '0')) + 1;
679 end if;
680 exception
681 when no_data_found then
682 l_ct := 0;
683 end;
684
685 l_item_key := l_init_key || '_' || l_ct;
686
687 wf_engine.CreateProcess(QPR_NTFN_ITM_TYPE ,l_item_key , l_process_name,
688 p_response_id, l_current_user);
689
690 wf_engine.SetItemAttrText(QPR_NTFN_ITM_TYPE , l_item_key,
691 'QPR_FWD_TO_USR' , p_fwd_to_user);
692
693 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_PN_RESP_ID',
694 p_response_id);
695
696 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_REQUEST_HDR_ID',
697 l_request_hdr_id);
698
699 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_SCENARIO_NO',
700 l_version_no);
701
702 wf_engine.StartProcess(QPR_NTFN_ITM_TYPE , l_item_key);
703
704 exception
705 when others then
706 retcode := 2;
707 errbuf := 'Unable to create workflow notification process';
708 end invoke_toapp_nfn_process;
709
710 procedure complete_toapp_nfn_process(p_response_id in number,
711 p_current_user in varchar2,
712 p_status in varchar2,
713 retcode out nocopy number,
714 errbuf out nocopy varchar2) is
715 l_item_key varchar2(240);
716 l_activity_name wf_process_activities.activity_name%type;
717 l_status varchar2(240);
718 l_init_key varchar2(240);
719 begin
720 l_init_key := 'TOAPP_' || p_response_id || p_current_user;
721
722 -- Done with the assumption that there will be only one active item for
723 -- key l_init_key --
724 begin
725 select item_key into l_item_key
726 from wf_items
727 where item_type = QPR_NTFN_ITM_TYPE
728 and item_key like (l_init_key || '%')
729 and end_date is null
730 and rownum < 2;
731 exception
732 when no_data_found then
733 return;
734 end;
735
736 select pa.activity_name into l_activity_name
737 from WF_ITEM_ACTIVITY_STATUSES act, wf_process_activities pa
738 where act.item_type = QPR_NTFN_ITM_TYPE
739 and act.item_key = l_item_key
740 and act.activity_status = 'NOTIFIED'
741 and act.process_activity = pa.instance_id
742 and rownum < 2;
743
744 if p_status = 'APPROVE' then
745 l_status := 'APPROVED';
746 elsif p_status = 'REJECT' then
747 l_status := 'REJECTED';
748 end if;
749
750 wf_engine.CompleteActivity(QPR_NTFN_ITM_TYPE, l_item_key,
751 l_activity_name, l_status );
752
753 exception
754 when others then
755 retcode := 2;
756 errbuf := 'Unable to complete workflow notification process';
757 end complete_toapp_nfn_process;
758
759 procedure cancel_toapp_nfn_process(p_response_id in number,
760 p_usr_list in char_type,
761 retcode out nocopy number,
762 errbuf out nocopy varchar2) is
763 l_item_key varchar2(240);
764 l_init_key varchar2(240);
765 begin
766
767 if p_usr_list.count = 0 then
768 return;
769 end if;
770
771 for i in p_usr_list.first..p_usr_list.last loop
772
773 l_init_key := 'TOAPP_' || p_response_id || p_usr_list(i);
774
775 -- Done with the assumption that there will be only one active item for
776 -- key l_init_key --
777 begin
778 select item_key into l_item_key
779 from wf_items
780 where item_type = QPR_NTFN_ITM_TYPE
781 and item_key like (l_init_key || '%')
782 and end_date is null
783 and rownum < 2;
784
785 wf_engine.AbortProcess(QPR_NTFN_ITM_TYPE, l_item_key,
786 null , WF_ENGINE.eng_null);
787 exception
788 when no_data_found then
789 null;
790 end;
791 end loop;
792
793 exception
794 when others then
795 retcode := 2;
796 errbuf := 'Unable to cancel workflow notification process';
797 end cancel_toapp_nfn_process;
798
799
800 procedure invoke_cb_nfn_process(p_response_id in number,
801 p_usr_list in char_type,
802 p_comments in varchar2,
803 retcode out nocopy number,
804 errbuf out nocopy varchar2)
805 is
806 l_item_key varchar2(240);
807 l_process_name varchar2(240) := 'QPRCBNFNP';
808 l_current_user varchar2(500);
809 l_to_user varchar2(500);
810 l_request_hdr_id number;
811 l_version_no number;
812 begin
813 select user_name into l_current_user
814 from fnd_user
815 where user_id = fnd_global.user_id;
816
817 select request_header_id, version_number
818 into l_request_hdr_id, l_version_no
819 from qpr_pn_response_hdrs
820 where response_header_id = p_response_id
821 and rownum < 2;
822
823 if p_usr_list.count = 0 then
824 return;
825 end if;
826
827 g_cb_nfn_usr_ctr := -1;
828
829 if g_cb_usr_tbl is not null then
830 g_cb_usr_tbl.delete;
831 end if;
832
833 for i in p_usr_list.first..p_usr_list.last loop
834 g_cb_usr_tbl(i) := p_usr_list(i);
835 end loop;
836 l_item_key := 'CALLBACK_' || p_response_id || l_current_user || sysdate;
837
838 wf_engine.CreateProcess(QPR_NTFN_ITM_TYPE ,l_item_key , l_process_name,
839 p_response_id,l_current_user);
840
841 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_PN_RESP_ID',
842 p_response_id);
843
844 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_REQUEST_HDR_ID',
845 l_request_hdr_id);
846
847 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_SCENARIO_NO',
848 l_version_no);
849
850 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key,'QPR_NUM_OF_USERS',
851 g_cb_usr_tbl.count);
852
853 wf_engine.SetItemAttrText(QPR_NTFN_ITM_TYPE , l_item_key,'QPR_FROM_USER',
854 l_current_user);
855
856 wf_engine.SetItemAttrText(QPR_NTFN_ITM_TYPE , l_item_key,'QPR_COMMENTS',
857 p_comments);
858
859 wf_engine.StartProcess(QPR_NTFN_ITM_TYPE , l_item_key);
860
861 exception
862 when others then
863 retcode := 2;
864 errbuf := 'Unable to create workflow notification process';
865 end invoke_cb_nfn_process;
866
867 procedure invoke_appstat_nfn_process(p_response_id in number,
868 p_usr_list in char_type,
869 p_comments in varchar2,
870 p_status in varchar2,
871 retcode out nocopy number,
872 errbuf out nocopy varchar2)
873 is
874 l_item_key varchar2(240);
875 l_process_name varchar2(240) := 'QPRAPPSTATNFNP';
876 l_current_user varchar2(500);
877 l_to_user varchar2(500);
878 l_request_hdr_id number;
879 l_version_no number;
880 l_status varchar2(240);
881
882 begin
883 select user_name into l_current_user
884 from fnd_user
885 where user_id = fnd_global.user_id;
886
887 select request_header_id, version_number
888 into l_request_hdr_id, l_version_no
889 from qpr_pn_response_hdrs
890 where response_header_id = p_response_id
891 and rownum < 2;
892
893 if p_usr_list.count = 0 then
894 return;
895 end if;
896
897 g_apst_nfn_usr_ctr := -1;
898
899 if g_apst_usr_tbl is not null then
900 g_apst_usr_tbl.delete;
901 end if;
902
903 for i in p_usr_list.first..p_usr_list.last loop
904 g_apst_usr_tbl(i) := p_usr_list(i);
905 end loop;
906
907 l_item_key := 'APPSTAT_' || p_response_id || l_current_user || sysdate;
908
909 wf_engine.CreateProcess(QPR_NTFN_ITM_TYPE ,l_item_key , l_process_name,
910 p_response_id, l_current_user);
911
912 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_PN_RESP_ID',
913 p_response_id);
914
915 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_REQUEST_HDR_ID',
916 l_request_hdr_id);
917
918 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key, 'QPR_SCENARIO_NO',
919 l_version_no);
920
921 wf_engine.SetItemAttrNumber(QPR_NTFN_ITM_TYPE , l_item_key,'QPR_NUM_OF_USERS',
922 g_apst_usr_tbl.count);
923
924 wf_engine.SetItemAttrText(QPR_NTFN_ITM_TYPE , l_item_key,'QPR_FROM_USER',
925 l_current_user);
926
927 wf_engine.SetItemAttrText(QPR_NTFN_ITM_TYPE , l_item_key,'QPR_COMMENTS',
928 p_comments);
929
930 if p_status = 'APPROVE' then
931 select meaning into l_status from qpr_lookups where lookup_type = 'AME_STATUS'
932 and lookup_code = p_status;
933
934 wf_engine.SetItemAttrText(QPR_NTFN_ITM_TYPE, l_item_key,'QPR_PN_RESP_STATUS',
935 l_status);
936 elsif p_status = 'REJECT' then
937 select meaning into l_status from qpr_lookups where lookup_type = 'AME_STATUS'
938 and lookup_code = p_status;
939
940 wf_engine.SetItemAttrText(QPR_NTFN_ITM_TYPE, l_item_key,'QPR_PN_RESP_STATUS',
941 l_status);
942
943 end if;
944
945 wf_engine.StartProcess(QPR_NTFN_ITM_TYPE , l_item_key);
946
947 exception
948 when others then
949 retcode := 2;
950 errbuf := 'Unable to create workflow notification process';
951 end invoke_appstat_nfn_process;
952
953
954 END;
955