[Home] [Help]
PACKAGE BODY: APPS.AR_CONFIRMATION
Source
1 package body AR_CONFIRMATION AS
2 /*$Header: ARCONFMB.pls 115.3 2002/12/23 22:23:36 tkoshio noship $ */
3
4 procedure initiate_confirmation_process(P_STATUS in VARCHAR2,
5 P_ID in VARCHAR2,
6 P_REASON_CODE in VARCHAR2,
7 P_DESCRIPTION in VARCHAR2,
8 P_INT_CTR_NUM in VARCHAR2) is
9 cursor ar is
10 select DISTINCT 'FND_RESP222:'||to_char(responsibility_id) role_name
11 from fnd_responsibility_tl
12 where application_id = 222;
13 cursor action is
14 select handler_name, handler_type from ar_confirmation_actions
15 where status = p_status and (reason_code = p_reason_code or p_reason_code is null);
16 cursor msgid is
17 select msgid from ecx_doclogs where internal_control_number = p_int_ctr_num;
18 l_handler_name varchar2(70);
19 l_handler_type varchar2(30);
20 l_sqlerrm varchar2(2000);
21 l_subject varchar2(200);
22 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
23 l_plsql varchar2(2000);
24 l_msgid raw(16);
25 l_url varchar2(500);
26 l_doc_transfer_id NUMBER;
27 cursor global is
28 select application_id, responsibility_id, last_updated_by
29 from ar_document_transfers
30 where document_transfer_id = l_doc_transfer_id;
31 global_rec global%rowtype;
32 l_sid number;
33
34 begin
35 l_doc_transfer_id := rtrim(ltrim(substrb(p_id,1,instrb(p_id,':',1,1)-1)));
36 open global; fetch global into global_rec; close global;
37 if global_rec.application_id is null or
38 global_rec.responsibility_id is null or
39 global_rec.last_updated_by is null then
40 fnd_message.set_name('AR','AR_CONF_ACT_INV_ID_SBJ');
41 l_subject := fnd_message.get;
42 fnd_message.set_name('AR','AR_CONF_ACT_INV_ID_BODY');
43 fnd_message.set_token('P_ID', p_id);
44 l_sqlerrm := fnd_message.get;
45 ar_notification_standard.notifyToSysadmin(l_subject, l_sqlerrm);
46 return;
47 else
48 fnd_global.initialize(l_sid, global_rec.last_updated_by, global_rec.responsibility_id,
49 global_rec.application_id, null,null,0,0,null,null,null,null);
50 end if;
51 if ar_doc_transfer_standard.isDebugOn then
52 ar_doc_transfer_standard.debug('AR_CONFIRMATION.initiate_confirmation_process(+)');
53 ar_doc_transfer_standard.debug('p_status:'||p_status);
54 ar_doc_transfer_standard.debug('p_reason_code:'||p_reason_code);
55 end if;
56 open msgid; fetch msgid into l_msgid; close msgid;
57 if ar_doc_transfer_standard.isDebugOn then
58 ar_doc_transfer_standard.debug('p_int_ctr_num:'||p_int_ctr_num);
59 ar_doc_transfer_standard.debug('l_msgid:'||l_msgid);
60 end if;
61 open action; fetch action into l_handler_name, l_handler_type; close action;
62 if l_handler_type = 'PLSQL' and l_handler_name is not null then
63 l_plsql := 'BEGIN '||l_handler_name||'(:status,:id,:reason_code,:description,:msgid); END;';
64 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('l_plsql:'||l_plsql); end if;
65 execute immediate l_plsql using p_status, p_id, p_reason_code, p_description, l_msgid;
66
67 elsif l_handler_type = 'EVENT' and l_handler_name is not null then
68 wf_event.AddParameterToList(
69 p_name => 'P_STATUS',
70 p_value => p_status,
71 p_parameterlist => l_parameter_list);
72 wf_event.AddParameterToList(
73 p_name => 'P_ID',
74 p_value => p_id,
75 p_parameterlist => l_parameter_list);
76 wf_event.AddParameterToList(
77 p_name => 'P_REASON_CODE',
78 p_value => p_reason_code,
79 p_parameterlist => l_parameter_list);
80 wf_event.AddParameterToList(
81 p_name => 'P_DESCRIPTION',
82 p_value => p_description,
83 p_parameterlist => l_parameter_list);
84 wf_event.AddParameterToList(
85 p_name => 'P_MSGID',
86 p_value => l_msgid,
87 p_parameterlist => l_parameter_list);
88 wf_event.raise(
89 p_event_name => l_handler_name,
90 p_event_key => to_char(sysdate, 'DD/MON/RRRR HH:MI:SS'),
91 p_parameters => l_parameter_list);
92 l_parameter_list.DELETE;
93
94 else
95 fnd_message.set_name('AR', 'AR_CONF_INVALID_SBJ');
96 l_subject := fnd_message.get;
97 fnd_message.set_name('AR', 'AR_CONF_INVALID');
98 fnd_message.set_token('STATUS', p_status);
99 fnd_message.set_token('REASON_CODE', p_reason_code);
100 l_sqlerrm := fnd_message.get;
101
102 l_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=CONFIRMATIONACTIONS_PG'||'&'||
103 'akRegionApplicationId=222';
104 for ar_rec in ar loop
105 ar_notification_standard.notify(l_subject,
106 l_sqlerrm,
107 ar_rec.role_name,
108 l_url);
109 end loop;
110 end if;
111 if ar_doc_transfer_standard.isDebugOn then ar_doc_transfer_standard.debug('AR_CONFIRMATION.initiate_confirmation_process(-)'); end if;
112 exception
113 when others then
114 l_sqlerrm := sqlerrm || fnd_global.newline||
115 'Location: AR_CONFIRMATION.initiate_confirmation_process()'||fnd_global.newline||
116 'Time: '||to_char(sysdate, 'DD-MON-RRRR HH:MI:SS');
117 fnd_message.set_name('AR','AR_CONF_ERROR');
118 l_subject := fnd_message.get;
119 ar_notification_standard.notifyToSysadmin(l_subject, l_sqlerrm);
120 end;
121 end;