[Home] [Help]
PACKAGE BODY: APPS.POS_SUPPLIER_NOTIFY_PKG
Source
1 PACKAGE BODY POS_SUPPLIER_NOTIFY_PKG AS
2 /* $Header: POSSNTFB.pls 120.1.12020000.4 2013/02/09 13:56:48 hvutukur ship $ */
3
4 /*Package to send the Notification message to the selected supplier contacts by invoking the wfengine*/
5
6 PROCEDURE wf_process
7 ( p_process IN VARCHAR2,
8 p_role IN VARCHAR2,
9 p_msg_subject IN VARCHAR2,
10 p_msg_body IN VARCHAR2,
11 p_osn_message IN VARCHAR2,
12 p_defer IN BOOLEAN
13 )
14 IS
15
16 l_itemtype wf_items.item_type%TYPE;
17 l_itemkey wf_items.item_key%TYPE;
18
19 l_threshold NUMBER := WF_ENGINE.threshold;
20
21 BEGIN
22
23 IF (p_defer) THEN
24 WF_ENGINE.threshold := -1;
25 END IF;
26
27 l_itemtype := 'POSNOTIF';
28
29 l_itemkey := 'POSNOTIF' || p_process || '_KEY_' ||
30 TO_CHAR(SYSDATE, 'MMDDYYYY_HH24MISS') || '_' ||
31 fnd_crypto.smallrandomnumber;
32
33 WF_ENGINE.CreateProcess(itemtype => l_itemtype,
34 itemkey => l_itemkey,
35 process => p_process);
36
37 WF_ENGINE.SetItemAttrText(itemtype => l_itemtype,
38 itemkey => l_itemkey,
39 aname => '#FROM_ROLE',
40 avalue => fnd_global.user_name);
41
42 WF_ENGINE.SetItemAttrText(itemtype => l_itemtype,
43 itemkey => l_itemkey,
44 aname => 'NOTIF_RECEIVER_ROLE',
45 avalue => p_role);
46
47 WF_ENGINE.SetItemAttrText(itemtype => l_itemtype,
48 itemkey => l_itemkey,
49 aname => 'SUPPMSGSUB',
50 avalue => p_msg_subject);
51
52 WF_ENGINE.SetItemAttrText(itemtype => l_itemtype,
53 itemkey => l_itemkey,
54 aname => 'SUPPMSGBD',
55 avalue => p_msg_body);
56
57 WF_ENGINE.SetItemAttrText(itemtype => l_itemtype,
58 itemkey => l_itemkey,
59 aname => 'SUPPADTMSG',
60 avalue => p_osn_message);
61
62 WF_ENGINE.StartProcess(itemtype => l_itemtype,
63 itemkey => l_itemkey);
64
65 WF_ENGINE.threshold := l_threshold;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 WF_ENGINE.threshold := l_threshold;
70 RAISE;
71
72 END wf_process;
73
74 PROCEDURE supplier_notification
75 ( p_msg_subject IN VARCHAR2,
76 p_msg_body IN VARCHAR2,
77 p_msg_recipient IN VARCHAR2,
78 p_msg_osn IN VARCHAR2,
79 p_notify_list IN VARCHAR2
80 )
81 IS
82
83 TYPE contact_csr_type IS REF CURSOR;
84
85 l_contact_csr contact_csr_type;
86
87 l_contact_sql VARCHAR2(4000);
88
89 l_first_name hz_parties.person_first_name%TYPE;
90 l_last_name hz_parties.person_last_name%TYPE;
91 l_email_address hz_contact_points.email_address%TYPE;
92 l_user_name fnd_user.user_name%TYPE;
93 l_user_email_address fnd_user.email_address%TYPE;
94
95 l_contact_users_tbl wf_directory.UserTable;
96 l_contact_emails_tbl wf_directory.UserTable;
97
98 l_contact_users_role wf_roles.name%TYPE;
99 l_contact_emails_role wf_roles.name%TYPE;
100
101 l_adhoc_user wf_users.name%TYPE;
102 l_display_name wf_users.display_name%TYPE;
103
104 l_process_user VARCHAR2(20) := 'SUPP_NOTIFY';
105 l_process_email VARCHAR2(20) := 'SUPP_NOTIFY_EMAIL';
106
107 l_osn_message fnd_new_messages.message_text%TYPE;
108
109 l_defer BOOLEAN := FALSE;
110 l_request_id NUMBER;
111
112 BEGIN
113
114 l_contact_sql :=
115 'SELECT DISTINCT ' ||
116 ' hp.person_first_name, ' ||
117 ' hp.person_last_name, ' ||
118 ' hcpe.email_address, ' ||
119 ' fu.user_name, ' ||
120 ' fu.email_address ' ||
121 'FROM hz_parties hp, ' ||
122 ' hz_relationships hzr, ' ||
123 ' hz_party_usg_assignments hpua, ' ||
124 ' hz_contact_points hcpe, ' ||
125 ' fnd_user fu, ' ||
126 ' ap_suppliers aps ' ||
127 'WHERE hp.party_id = hzr.subject_id ' ||
128 ' AND hzr.object_id = aps.party_id ' ||
129 ' AND hzr.relationship_type = ''CONTACT'' ' ||
130 ' AND hzr.relationship_code = ''CONTACT_OF'' ' ||
131 ' AND hzr.subject_type = ''PERSON'' ' ||
132 ' AND hzr.object_type = ''ORGANIZATION'' ' ||
133 ' AND hzr.status = ''A'' ' ||
134 ' AND NVL(hzr.end_date, SYSDATE) >= SYSDATE ' ||
135 ' AND hpua.party_id = hp.party_id ' ||
136 ' AND hpua.status_flag = ''A'' ' ||
137 ' AND hpua.party_usage_code = ''SUPPLIER_CONTACT'' ' ||
138 ' AND NVL(hpua.effective_end_date, SYSDATE) >= SYSDATE ' ||
139 ' AND hcpe.owner_table_name(+) = ''HZ_PARTIES'' ' ||
140 ' AND hcpe.owner_table_id(+) = hzr.party_id ' ||
141 ' AND hcpe.contact_point_type(+) = ''EMAIL'' ' ||
142 ' AND hcpe.primary_flag(+) = ''Y'' ' ||
143 ' AND NVL(hcpe.status, ''A'') = ''A'' ' ||
144 ' AND fu.person_party_id(+) = hp.party_id ' ||
145 ' AND NVL(fu.end_date, SYSDATE) >= SYSDATE ';
146
147 IF p_notify_list <> 'ALL_SUPPLIERS' THEN
148 l_contact_sql := l_contact_sql ||
149 ' AND aps.vendor_id IN (' || p_notify_list || ') ';
150 END IF;
151
152 OPEN l_contact_csr FOR l_contact_sql;
153 LOOP
154 FETCH l_contact_csr INTO l_first_name,
155 l_last_name,
156 l_email_address,
157 l_user_name,
158 l_user_email_address;
159 EXIT WHEN l_contact_csr%NOTFOUND;
160
161 IF (l_user_name IS NOT NULL AND
162 l_user_email_address IS NOT NULL) THEN
163
164 l_contact_users_tbl(l_contact_users_tbl.COUNT + 1) := l_user_name;
165
166 ELSIF (UPPER(p_msg_recipient) = 'ALL CONTACTS' AND
167 l_user_name IS NULL AND
168 l_email_address IS NOT NULL) THEN
169
170 l_adhoc_user := 'ADHOC_USER_' || l_first_name || '_' ||
171 TO_CHAR(SYSDATE, 'MMDDYYYY_HH24MISS') ||
172 fnd_crypto.smallrandomnumber;
173 l_display_name := l_last_name || ',' || l_first_name;
174
175 WF_DIRECTORY.CreateAdHocUser(name => l_adhoc_user,
176 display_name => l_display_name,
177 notification_preference => 'MAILTEXT',
178 email_address => l_email_address);
179
180 l_contact_emails_tbl(l_contact_emails_tbl.COUNT + 1) := l_adhoc_user;
181
182 END IF;
183
184 END LOOP;
185 CLOSE l_contact_csr;
186
187 IF (p_msg_osn = 'Y') THEN
188 l_osn_message := pos_spm_wf_pkg1.get_osn_message();
189 END IF;
190
191 IF p_notify_list = 'ALL_SUPPLIERS' THEN
192 l_defer := TRUE;
193 END IF;
194
195 IF (l_contact_users_tbl.COUNT > 0) THEN
196
197 l_contact_users_role := 'POS_' || l_process_user || '_USERROLE' ||
198 TO_CHAR(SYSDATE, 'MMDDYYYY_HH24MISS') || '_' ||
199 fnd_crypto.smallrandomnumber;
200
201 WF_DIRECTORY.CreateAdHocRole(role_name => l_contact_users_role,
202 role_display_name => l_contact_users_role,
203 expiration_date => SYSDATE + 1);
204
205 WF_DIRECTORY.AddUsersToAdHocRole2(role_name => l_contact_users_role,
206 role_users => l_contact_users_tbl);
207
208 wf_process(p_process => 'SUPP_NOTIFY',
209 p_role => l_contact_users_role,
210 p_msg_subject => p_msg_subject,
211 p_msg_body => p_msg_body,
212 p_osn_message => l_osn_message,
213 p_defer => l_defer);
214
215 END IF;
216
217 IF (l_contact_emails_tbl.COUNT > 0) THEN
218
219 l_contact_emails_role := 'POS_' || l_process_email || '_EMAILROLE' ||
220 TO_CHAR(SYSDATE, 'MMDDYYYY_HH24MISS') || '_' ||
221 fnd_crypto.smallrandomnumber;
222
223 WF_DIRECTORY.CreateAdHocRole(role_name => l_contact_emails_role,
224 role_display_name => l_contact_emails_role,
225 expiration_date => SYSDATE + 1);
226
227 WF_DIRECTORY.AddUsersToAdHocRole2(role_name => l_contact_emails_role,
228 role_users => l_contact_emails_tbl);
229
230 wf_process(p_process => 'SUPP_NOTIFY_EMAIL',
231 p_role => l_contact_emails_role,
232 p_msg_subject => p_msg_subject,
233 p_msg_body => p_msg_body,
234 p_osn_message => l_osn_message,
235 p_defer => l_defer);
236
237 END IF;
238
239 COMMIT;
240
241 IF (l_defer AND
242 (l_contact_users_tbl.COUNT > 0 OR l_contact_emails_tbl.COUNT > 0)) THEN
243
244 l_request_id := fnd_request.submit_request('FND',
245 'FNDWFBG',
246 NULL,
247 NULL,
248 FALSE,
249 'POSNOTIF');
250
251 COMMIT;
252
253 END IF;
254
255 END supplier_notification;
256
257 END POS_SUPPLIER_NOTIFY_PKG;