eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.XTR_WORKFLOW_PKG

Source


1 PACKAGE BODY XTR_WORKFLOW_PKG AS
2 /* $Header: xtrwfpkb.pls 120.3 2011/03/12 20:28:25 nipant ship $*/
3 -- This procedure creates an ad hoc Treasury role
4 FUNCTION CREATE_XTR_ROLES(p_role_users IN VARCHAR2,
5                           p_expiration_date IN DATE) RETURN VARCHAR2
6 IS
7    l_wf_sequence NUMBER;
8    l_role_name VARCHAR2(60);
9    l_role_display_name VARCHAR2(60);
10 
11 BEGIN
12    select XTR_ROLES_S.nextval
13    into   l_wf_sequence
14    from   dual;
15 
16    l_role_name := 'XTR'||to_char(l_wf_sequence);
17    l_role_display_name := 'XTR'||to_char(l_wf_sequence);
18 
19    wf_directory.CreateAdHocRole(l_role_name,
20                                 l_role_display_name,
21                                 null, -- defaults to user setting language
22                                 null, -- defaults to user setting territory
23                                 null,
24                                 'QUERY',
25                                 p_role_users,
26                                 null, -- no email
27                                 null, -- no fax
28                                 'ACTIVE',
29                                 p_expiration_date);
30    return l_role_name;
31 END CREATE_XTR_ROLES;
32 
33 PROCEDURE START_WORKFLOW(p_process     IN VARCHAR2,
34                          p_owner       IN VARCHAR2,
35                          p_deal_no     IN NUMBER,
36                          p_trans_no    IN NUMBER,
37                          p_deal_type   IN VARCHAR2,
38                          p_log_id      IN NUMBER,
39                          p_varnum_1    IN NUMBER,
40                          p_varnum_2    IN NUMBER,
41                          p_varchar_1   IN VARCHAR2,
42                          p_varchar_2   IN VARCHAR2,
43                          p_vardate_1   IN DATE,
44                          p_vardate_2   IN DATE)
45 IS
46    l_role_name VARCHAR2(60);
47    l_user_list VARCHAR2(3000);
48    l_user_name  FND_USER.USER_NAME%type;
49 
50    -- Cursor used for IG
51    cursor cur_notify_users_ig is
52       select fu.USER_NAME, xwf.AMOUNT,xle.EXCEEDED_BY_AMOUNT
53       from XTR_WF_USER_ROLES xwf,
54 	   XTR_INTERGROUP_TRANSFERS_V xds,
55            XTR_LIMIT_EXCESS_LOG_V xle,
56            FND_USER fu
57       where xwf.ROLE_NAME = 'XTR_LIMITS_NOTIFICATION'
58       and xds.DEAL_NUMBER = p_deal_no
59       and xds.TRANSACTION_NUMBER = p_trans_no
60       and xle.LOG_ID = p_log_id
61       and fu.USER_ID = xwf.USER_ID
62       and fu.start_date <= trunc(sysdate)
63       and nvl(fu.end_date, sysdate+1) >= trunc(sysdate)
64       and nvl(xwf.COMPANY, xds.COMPANY_CODE) = xds.COMPANY_CODE
65       and nvl(xwf.PORTFOLIO, xds.PORTFOLIO) =  xds.PORTFOLIO
66       and nvl(xwf.DEAL_TYPE, xds.DEAL_TYPE) = xds.DEAL_TYPE
67       and nvl(xwf.PRODUCT_TYPE, xds.PRODUCT_TYPE) = xds.PRODUCT_TYPE
68       and nvl(xwf.CPARTY, xds.PARTY_CODE) = xds.PARTY_CODE
69       and nvl(xwf.LIMIT_CHECK_TYPE, xle.LIMIT_CHECK_TYPE) = xle.LIMIT_CHECK_TYPE
70       order by fu.USER_NAME, xwf.PRIORITY,nvl(xle.EXCEEDED_BY_AMOUNT,0) desc;
71 
72 
73    -- Cursor used for ONC
74    cursor cur_notify_users_onc is
75       select fu.USER_NAME, xwf.AMOUNT,xle.EXCEEDED_BY_AMOUNT
76       from XTR_WF_USER_ROLES xwf,
77 	   XTR_ROLLOVER_TRANSACTIONS_V xds,
78            XTR_LIMIT_EXCESS_LOG_V xle,
79            FND_USER fu
80       where xwf.ROLE_NAME = 'XTR_LIMITS_NOTIFICATION'
81       and xds.DEAL_NUMBER = p_deal_no
82       and xds.TRANSACTION_NUMBER = p_trans_no
83       and xle.LOG_ID = p_log_id
84       and fu.USER_ID = xwf.USER_ID
85       and fu.start_date <= trunc(sysdate)
86       and nvl(fu.end_date, sysdate+1) >= trunc(sysdate)
87       and nvl(xwf.COMPANY, xds.COMPANY_CODE) = xds.COMPANY_CODE
88       and nvl(xwf.PORTFOLIO, xds.PORTFOLIO_CODE) =  xds.PORTFOLIO_CODE
89       and nvl(xwf.DEAL_TYPE, xds.DEAL_TYPE) = xds.DEAL_TYPE
90       and nvl(xwf.DEAL_SUBTYPE, xds.DEAL_SUBTYPE) = xds.DEAL_SUBTYPE
91       and nvl(xwf.PRODUCT_TYPE, xds.PRODUCT_TYPE) = xds.PRODUCT_TYPE
92       and nvl(xwf.CPARTY, xds.CPARTY_CODE) = xds.CPARTY_CODE
93       and nvl(xwf.DEALER, xds.DEALER_CODE) = xds.DEALER_CODE
94       and nvl(xwf.LIMIT_CHECK_TYPE, xle.LIMIT_CHECK_TYPE) = xle.LIMIT_CHECK_TYPE
95       order by fu.USER_NAME, xwf.PRIORITY,nvl(xle.EXCEEDED_BY_AMOUNT,0) desc;
96 
97 
98    -- Cursor used for other deal types
99    cursor cur_notify_users_deal is
100       select fu.USER_NAME, xwf.AMOUNT,xle.EXCEEDED_BY_AMOUNT
101       from XTR_WF_USER_ROLES xwf,
102            XTR_DEALS_V xds,
103            XTR_LIMIT_EXCESS_LOG_V xle,
104            FND_USER fu
105       where xwf.ROLE_NAME = 'XTR_LIMITS_NOTIFICATION'
106       and xds.DEAL_NO = p_deal_no
107       and nvl(xds.TRANSACTION_NO,0) = nvl(p_trans_no,0)
108       and xle.LOG_ID = p_log_id
109       and fu.USER_ID = xwf.USER_ID
110       and fu.start_date <= trunc(sysdate)
111       and nvl(fu.end_date, sysdate+1) >= trunc(sysdate)
112       and nvl(xwf.COMPANY, xds.COMPANY_CODE) = xds.COMPANY_CODE
113       and nvl(xwf.PORTFOLIO, xds.PORTFOLIO_CODE) =  xds.PORTFOLIO_CODE
114       and nvl(xwf.DEAL_TYPE, xds.DEAL_TYPE) = xds.DEAL_TYPE
115       and nvl(xwf.DEAL_SUBTYPE, xds.DEAL_SUBTYPE) = xds.DEAL_SUBTYPE
116       and nvl(xwf.PRODUCT_TYPE, xds.PRODUCT_TYPE) = xds.PRODUCT_TYPE
117       and nvl(xwf.CPARTY, xds.CPARTY_CODE) = xds.CPARTY_CODE
118       and nvl(xwf.DEALER, xds.DEALER_CODE) = xds.DEALER_CODE
119       and nvl(xwf.LIMIT_CHECK_TYPE, xle.LIMIT_CHECK_TYPE) = xle.LIMIT_CHECK_TYPE
120       order by fu.USER_NAME, xwf.PRIORITY,nvl(xle.EXCEEDED_BY_AMOUNT,0) desc;
121 
122 BEGIN
123    l_user_list := ' ';
124    l_role_name := ' ';
125    l_user_name := ' ';
126 
127    -- begin user selection for IG deal type
128    if (p_deal_type = 'IG') then
129       for limit_user in cur_notify_users_ig loop
130         if limit_user.user_name <> l_user_name then
131           l_user_name :=limit_user.user_name;
132           if nvl(limit_user.EXCEEDED_BY_AMOUNT,0) >=nvl(limit_user.AMOUNT,0) then
133            if (l_user_list = ' ') then
134             l_user_list := limit_user.user_name;
135            else
136             l_user_list := l_user_list||','||limit_user.user_name;
137            end if;
138           end if;
139         end if;
140       end loop;
141 
142    -- begin user selection for ONC deal type
143    elsif (p_deal_type = 'ONC') then
144       for limit_user in cur_notify_users_onc loop
145         if limit_user.user_name <> l_user_name then
146           l_user_name :=limit_user.user_name;
147           if nvl(limit_user.EXCEEDED_BY_AMOUNT,0) >=nvl(limit_user.AMOUNT,0) then
148            if (l_user_list = ' ') then
149             l_user_list := limit_user.user_name;
150            else
151             l_user_list := l_user_list||','||limit_user.user_name;
152            end if;
153           end if;
154         end if;
155       end loop;
156 
157    -- begin user selection for other deal types
158    else
159       for limit_user in cur_notify_users_deal loop
160         if limit_user.user_name <> l_user_name then
161           l_user_name :=limit_user.user_name;
162           if nvl(limit_user.EXCEEDED_BY_AMOUNT,0) >=nvl(limit_user.AMOUNT,0) then
163            if (l_user_list = ' ') then
164             l_user_list := limit_user.user_name;
165            else
166             l_user_list := l_user_list||','||limit_user.user_name;
167            end if;
168           end if;
169         end if;
170       end loop;
171 
172    end if;
173    -- only start WF if there are users matching the critieria
174    if (l_user_list <> ' ') then
175       l_role_name := CREATE_XTR_ROLES(ltrim(l_user_list),SYSDATE+2);
176       -- begin limits notification
177       if (p_process = 'XTR_LIMITS_NOTIFICATION') then
178          START_LIMITS_NTF(p_process, p_owner, l_role_name, p_log_id);
179       end if;
180    end if;
181 
182 END START_WORKFLOW;
183 
184 PROCEDURE START_LIMITS_NTF(p_process  IN VARCHAR2,
185                            p_owner    IN VARCHAR2,
186                            p_receiver IN VARCHAR2,
187                            p_log_id   IN NUMBER)
188 IS
189    l_itemkey  VARCHAR2(40);
190    l_itemtype VARCHAR2(40) := 'XTRWF';
191    l_userkey  VARCHAR2(80);
192    l_wf_sequence NUMBER;
193 
194 BEGIN
195    select XTR_WF_S.nextval
196    into   l_wf_sequence
197    from   dual;
198 
199    l_itemkey := to_char(l_wf_sequence);
200    l_userkey := l_itemtype||l_itemkey;
201    wf_engine.CreateProcess(itemtype => l_itemtype,
202                            itemkey  => l_itemkey,
203                            process  => p_process);
204    wf_engine.SetItemAttrText(itemtype => l_itemtype,
205                              itemkey  => l_itemkey,
206                              aname    => 'XTR_NTF_RECEIVER',
207                              avalue   => p_receiver);
208    wf_engine.SetItemAttrText(itemtype => l_itemtype,
209                              itemkey  => l_itemkey,
210                              aname    => 'XTR_LIMITS_LOG_ID',
211                              avalue   => p_log_id);
212    wf_engine.StartProcess(itemtype => l_itemtype,
213                           itemkey  => l_itemkey );
214 EXCEPTION
215    when others then
216    wf_core.context('XTR_WORKFLOW_PKG', 'START_LIMITS_NTF', l_itemtype, l_itemkey);
217    raise;
218 
219 END START_LIMITS_NTF;
220 
221 
222 PROCEDURE LIMITS_BREACHED_DOC(document_id IN VARCHAR2,
223                               display_type IN VARCHAR2,
224                               document IN OUT NOCOPY VARCHAR2,
225                               document_type IN OUT NOCOPY VARCHAR2)
226 IS
227 
228    cursor cur_limits_breached(p_log_id IN NUMBER) is
229    select xle.deal_number, xle.transaction_number, decode(xle.exception_type,
230 'EXCEEDED', FND_MESSAGE.GET_STRING('XTR','XTR_2242'), 'WARNING', FND_MESSAGE.GET_STRING('XTR','XTR_2241'), 'NO_AUTHO',
231 FND_MESSAGE.GET_STRING('XTR','XTR_2243'), 'NO_LIMIT', FND_MESSAGE.GET_STRING('XTR','XTR_2244')) as exception_type,
232     decode(xle.exception_type, 'EXCEEDED', 'XTR_2245',
233 	'WARNING', 'XTR_2246', 'NO_AUTHO', 'XTR_2247',
234 	'NO_LIMIT','XTR_2248') as exception_desc,
235    decode(xle.LIMIT_CHECK_TYPE,
236 	'GLOBAL',FND_MESSAGE.GET_STRING('XTR','XTR_2233'),
237 	'SOVRN', FND_MESSAGE.GET_STRING('XTR','XTR_2234'),
238 	'DLR_DEAL', FND_MESSAGE.GET_STRING('XTR','XTR_2235'),
239 	'CPARTY', FND_MESSAGE.GET_STRING('XTR','XTR_2237'),
240 	'SETTLE',  FND_MESSAGE.GET_STRING('XTR','XTR_2238'),
241 	'CCY', FND_MESSAGE.GET_STRING('XTR','XTR_2239'),
242 	'GROUP', FND_MESSAGE.GET_STRING('XTR','XTR_2250'),
243 	'TIME', FND_MESSAGE.GET_STRING('XTR','XTR_2240') ) as exception_token,
244           xle.limit_code,
245           xle.exceeded_by_amount exceeded_by_amount_dsp,
246           xle.exceeded_on_date exceeded_on_date_dsp,
247           xle.currency, xle.company_code, xle.limit_party,
248           xle.amount_date amount_date_dsp,
249           xle.limiting_amount limiting_amount_dsp,
250           xle.authorised_by, xle.dealer_code, xle.log_id
251    from XTR_LIMIT_EXCESS_LOG_V xle
252    where xle.log_id = p_log_id
253    order by xle.deal_number, xle.transaction_number;
254 
255    l_deal_no VARCHAR2(50);
256    l_trans_no VARCHAR2(50);
257    l_exc_type VARCHAR2(50);
258    l_limit_code VARCHAR2(50);
259    l_exc_amount VARCHAR2(50);
260    l_exc_on VARCHAR2(50);
261    l_ccy VARCHAR2(50);
262    l_comp_code VARCHAR2(50);
263    l_limit_party VARCHAR2(50);
264    l_amount_date VARCHAR2(50);
265    l_limit_amount VARCHAR2(50);
266    l_auth_by VARCHAR2(50);
267    l_dealer_code VARCHAR2(50);
268    l_exc_desc_title VARCHAR2(50);
269    l_ccy_code VARCHAR2(15);
270    l_exc_desc VARCHAR2(100);
271 
272 BEGIN
273    l_deal_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEAL_NO');
274    l_trans_no := FND_MESSAGE.GET_STRING('XTR','XTR_WF_TRANS_NO');
275    l_exc_type := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_TYPE');
276    l_limit_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_CODE');
277    l_exc_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_AMOUNT');
278    l_exc_on := FND_MESSAGE.GET_STRING('XTR','XTR_WF_EXC_ON');
279    l_ccy := FND_MESSAGE.GET_STRING('XTR','XTR_WF_CCY');
280    l_comp_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_COMP_CODE');
281    l_limit_party := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_PARTY');
282    l_amount_date := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AMOUNT_DATE');
283    l_limit_amount := FND_MESSAGE.GET_STRING('XTR','XTR_WF_LIMIT_AMOUNT');
284    l_auth_by := FND_MESSAGE.GET_STRING('XTR','XTR_WF_AUTH_BY');
285    l_dealer_code := FND_MESSAGE.GET_STRING('XTR','XTR_WF_DEALER_CODE');
286    l_exc_desc_title := FND_MESSAGE.GET_STRING('XTR', 'XTR_WF_EXC_DESC');
287 
288 
289    SELECT nvl(param_value,'USD')
290    INTO l_ccy_code
291    FROM xtr_pro_param_v
292    WHERE param_name = 'SYSTEM_FUNCTIONAL_CCY';
293 
294    if display_type = 'text/html' then
295       document_type := 'text/html';
296       document :=
297          '<BR><BR><LEFT><TABLE BORDER=0 CELLPADDING=5 CELLSPACING=1 BGCOLOR=#FFFFFF>'||
298          '<TR BGCOLOR=#CCCC99>'||
299          '<TH ALIGN=RIGHT><FONT COLOR=#336699><B>'|| l_deal_no ||'</B></TH>'||
300          '<TH ALIGN=RIGHT><FONT COLOR=#336699><B>'|| l_trans_no || '</B></TH>'||
301          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_exc_type || '</B></TH>'||
302          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_exc_desc_title || '</B></TH>'||
303          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_limit_code || '</B></TH>'||
304          '<TH ALIGN=RIGHT><FONT COLOR=#336699><B>' || l_exc_amount || '</B></TH>'||
305          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_exc_on || '</B></TH>'||
306          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_ccy || '</B></TH>'||
307          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_comp_code || '</B></TH>'||
308          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_limit_party || '</B></TH>'||
309          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_amount_date || '</B></TH>'||
310          '<TH ALIGN=RIGHT><FONT COLOR=#336699><B>' || l_limit_amount || '</B></TH>'||
311          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_auth_by || '</B></TH>'||
312          '<TH ALIGN=LEFT><FONT COLOR=#336699><B>' || l_dealer_code || '</B></TH>'||
313          '</TR>';
314    else
315       document_type := 'text/plain';
316       document :=
317          FND_GLOBAL.NEWLINE||rpad(l_deal_no,28)||
318          rpad(l_trans_no,28)||
319          rpad(l_exc_type,28)||
320 	 rpad(l_exc_desc_title,28)||
321          rpad(l_limit_code,28)||
322          rpad(l_exc_amount,28)||
323          rpad(l_exc_on,28)||
324          rpad(l_ccy,28)||
325          rpad(l_comp_code,28)||
326          rpad(l_limit_party,28)||
327          rpad(l_amount_date,28)||
328          rpad(l_limit_amount,28)||
329          rpad(l_auth_by,28)||
330          rpad(l_dealer_code,28)||FND_GLOBAL.NEWLINE;
331    end if;
332    for limit_rec in cur_limits_breached(to_number(document_id)) loop
333        FND_MESSAGE.set_name('XTR', limit_rec.exception_desc);
334        FND_MESSAGE.set_token('LIMIT',limit_rec.exception_token );
335        l_exc_desc := FND_MESSAGE.get;
336       if display_type = 'text/html' then
337          document := document||
338          '<TR BGCOLOR=#F7F7E7>'||
339          '<TD ALIGN=RIGHT>'||limit_rec.deal_number||'</TD>'||
340          '<TD ALIGN=RIGHT>'||limit_rec.transaction_number||'</TD>'||
341          '<TD ALIGN=LEFT>'||limit_rec.exception_type||'</TD>'||
342  	 '<TD ALIGN=LEFT>'||l_exc_desc||'</TD>'||
343          '<TD ALIGN=LEFT>'||limit_rec.limit_code||'</TD>'||
344          '<TD ALIGN=RIGHT>'||TO_CHAR(limit_rec.exceeded_by_amount_dsp,
345 	FND_CURRENCY.GET_FORMAT_MASK(l_ccy_code, 30))||'</TD>'||
346          '<TD ALIGN=LEFT NOWRAP>'||FND_DATE.DATE_TO_CHARDATE(dateval =>limit_rec.exceeded_on_date_dsp, calendar_aware=>1)||'</TD>'||
347          '<TD ALIGN=LEFT>'||limit_rec.currency||'</TD>'||
348          '<TD ALIGN=LEFT>'||limit_rec.company_code||'</TD>'||
349          '<TD ALIGN=LEFT>'||limit_rec.limit_party||'</TD>'||
350          '<TD ALIGN=LEFT NOWRAP>'||FND_DATE.DATE_TO_CHARDATE(dateval=>limit_rec.amount_date_dsp,calendar_aware=>1)||'</TD>'||
351          '<TD ALIGN=RIGHT>'||TO_CHAR(limit_rec.limiting_amount_dsp,
352 	FND_CURRENCY.GET_FORMAT_MASK(l_ccy_code, 30))||'</TD>'||
353          '<TD ALIGN=LEFT>'||limit_rec.authorised_by||'</TD>'||
354          '<TD ALIGN=LEFT>'||limit_rec.dealer_code||'</TD>'||'</TR>';
355       else
356          document := document||rpad(limit_rec.deal_number,28)||
357          rpad(limit_rec.transaction_number,28)||
358          rpad(limit_rec.exception_type,28)||
359 	 rpad(l_exc_desc,28)||
360          rpad(limit_rec.limit_code,28)||
361          rpad(TO_CHAR(limit_rec.exceeded_by_amount_dsp,
362 	FND_CURRENCY.GET_FORMAT_MASK(l_ccy_code, 30)),28)||
363          rpad(FND_DATE.DATE_TO_CHARDATE(dateval=>limit_rec.exceeded_on_date_dsp,calendar_aware=>1),28)||
364          rpad(limit_rec.currency,28)||
365          rpad(limit_rec.company_code,28)||
366          rpad(limit_rec.limit_party,28)||
367          rpad(FND_DATE.DATE_TO_CHARDATE(dateval=>limit_rec.amount_date_dsp,calendar_aware=>1),28)||
368          rpad(TO_CHAR(limit_rec.limiting_amount_dsp,
369 	FND_CURRENCY.GET_FORMAT_MASK(l_ccy_code, 30)),28)||
370          rpad(limit_rec.authorised_by,28)||
371          rpad(limit_rec.dealer_code,28)||FND_GLOBAL.NEWLINE;
372       end if;
373    end loop;
374    if display_type = 'text/html' then
375       document := document||'</TABLE></LEFT><BR>';
376    end if;
377 EXCEPTION
378    when others then
379    wf_core.context('XTR_WORKFLOW_PKG', 'LIMITS_BREACHED_DOC', document_id, display_type);
380    raise;
381 END LIMITS_BREACHED_DOC;
382 
383 
384 -- This procedure removes a Treasury User from WF_LOCAL_USERS
385 PROCEDURE DELETE_XTR_USERS(p_name IN VARCHAR2,
386                            p_dsp_name IN VARCHAR2,
387                            p_email IN VARCHAR2)
388 IS
389 
390 BEGIN
391 
392    delete from WF_LOCAL_USERS
393    where NAME = p_name
394    and DISPLAY_NAME = p_dsp_name
395    and EMAIL_ADDRESS = p_email;
396 
397    delete from WF_LOCAL_USER_ROLES
398    where USER_NAME = p_name
399    and ROLE_ORIG_SYSTEM = 'WF_LOCAL_ROLES';
400 
401 END DELETE_XTR_USERS;
402 
403 END XTR_WORKFLOW_PKG;
404