[Home] [Help]
PACKAGE BODY: APPS.POS_WF_PO_COMMON_APIS
Source
1 PACKAGE BODY POS_WF_PO_COMMON_APIS AS
2 /* $Header: POSWFCMB.pls 115.2 2002/11/25 19:45:30 sbull noship $ */
3
4 /*
5
6 --
7 --
8 This package contains the common API used by all the WEB Supplier
9 Workflows.
10
11 Be extremely careful when modifying anything here as it will
12 most likey impact all the web supplier workflows.
13
14 --
15 --
16
17 */
18
19
20 --
21
22 /*
23 This activity assumes that you have the fol. attributes defined for the item:
24
25 DOCUMENT_ID - ie. po_header_id
26 DOCUMENT_NUM - segment1
27
28 Only applicable from a SSP env (for web suppliers) - jumps into the ssp4 doc
29 details flow.
30 */
31
32
33 PROCEDURE Get_PO_Details_URL( document_id in varchar2,
34 display_type in varchar2,
35 document in out NOCOPY varchar2,
36 document_type in out NOCOPY varchar2) IS
37
38 p_rowid VARCHAR2(2000);
39 l_param VARCHAR2(2000);
40 Y VARCHAR2(2000);
41 x_progress VARCHAR2(3) := '000';
42 l_language VARCHAR2(5);
43 l_script_name VARCHAR2(240);
44 l_org_id NUMBER;
45 l_user_id NUMBER;
46 l_session_id NUMBER;
47 l_responsibility_id NUMBER;
48 l_item_type wf_items.item_type%TYPE;
49 l_item_key wf_items.item_key%TYPE;
50 l_document_id po_headers.po_header_id%TYPE;
51 l_document_num VARCHAR2(500);
52 l_header_msg VARCHAR2(500);
53 l_document VARCHAR2(32000) := '';
54 NL VARCHAR2(1) := fnd_global.newline;
55
56 BEGIN
57
58 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
59 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
60
61 x_progress := '001';
62
63 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
64 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
65 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
66 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
67 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
68 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
69
70 x_progress := '002';
71
72 l_document_id := wf_engine.GetItemAttrNumber
73 (itemtype => l_item_type,
74 itemkey => l_item_key,
75 aname => 'DOCUMENT_ID');
76
77 l_document_num := wf_engine.GetItemAttrText
78 (itemtype => l_item_type,
79 itemkey => l_item_key,
80 aname => 'DOCUMENT_NUM');
81
82 fnd_client_info.set_org_context(to_char(l_org_id));
83
84 x_progress := '003';
85
86 select rowidtochar(ROWID)
87 into p_rowid
88 from AK_FLOW_REGION_RELATIONS
89 where FROM_REGION_CODE = 'ICX_PO_HEADERS_D'
90 and FROM_REGION_APPL_ID = 178
91 and FROM_PAGE_CODE = 'ICX_PO_HEADERS_D'
92 and FROM_PAGE_APPL_ID = 178
93 and TO_PAGE_CODE = 'ICX_PO_HEADERS_DTL_D'
94 and TO_PAGE_APPL_ID = 178
95 and FLOW_CODE = 'ICX_INQUIRIES'
96 and FLOW_APPLICATION_ID = 178;
97
98 x_progress := '004';
99
100 l_param := icx_on_utilities.buildOracleONstring(p_rowid => p_rowid,
101 p_primary_key => 'ICX_PO_SUPPLIER_ORDERS_PK',
102 p1 => to_char(l_document_id));
103 x_progress := '005';
104 Y := icx_call.encrypt2(l_param,l_session_id);
105 x_progress := '006';
106 document := '<A HREF="OracleON.IC?Y=' || Y || '">' || l_document_num || '</A>';
107
108 --dbms_output.put_line('After Get Po Details ' || document || document_type);
109
110 exception
111 WHEN OTHERS THEN
112 wf_core.context('POS_WF_PO_ACKNOWLEDGE','Get_PO_Details_URL',x_progress);
113 raise;
114 end;
115
116 --
117
118 procedure get_supplier_username ( itemtype in varchar2,
119 itemkey in varchar2,
120 actid in number,
121 funcmode in varchar2,
122 result out NOCOPY varchar2 )
123
124 is
125 x_progress varchar2(3) := '000';
126 x_supplier_id number;
127 x_supplier_username varchar2(100);
128 x_supplier_display_name varchar2(240);
129 x_orig_system varchar2(4);
130 begin
131
132 --dbms_output.put_line (' In Get Supplier Username ');
133 x_supplier_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
134 itemkey => itemkey,
135 aname => 'SUPPLIER_ID');
136
137 --dbms_output.put_line (' Get Supplier Username ' || x_supplier_id);
138 if x_supplier_id is not null then
139
140 x_progress := '003';
141
142 -- Get the supplier user name
143
144 x_orig_system:= 'PER';
145
146 WF_DIRECTORY.GetUserName( x_orig_system,
147 x_supplier_id,
148 x_supplier_username,
149 x_supplier_display_name);
150 x_progress := '004';
151
152 wf_engine.SetItemAttrText ( itemtype => itemtype,
153 itemkey => itemkey,
154 aname => 'SUPPLIER_USER_NAME',
155 avalue => x_supplier_username);
156 end if;
157 --dbms_output.put_line (' result in Get Supplier Username ' || x_supplier_username);
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 wf_core.context('POS_WF_PO_COMMON_APIS','Get_Supplier_username',x_progress);
162 raise;
163 end;
164
165 --
166
167 procedure get_buyer_username ( itemtype in varchar2,
168 itemkey in varchar2,
169 actid in number,
170 funcmode in varchar2,
171 result out NOCOPY varchar2 )
172
173 is
174 x_progress varchar2(3) := '000';
175 x_buyer_id number;
176 x_buyer_username varchar2(100);
177 x_buyer_display_name varchar2(240);
178 x_orig_system varchar2(4);
179 begin
180
181 x_buyer_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
182 itemkey => itemkey,
183 aname => 'BUYER_USER_ID');
184
185 if x_buyer_id is not null then
186
187 x_progress := '003';
188
189 -- Get the buyer user name
190
191 x_orig_system:= 'PER';
192
193 WF_DIRECTORY.GetUserName( x_orig_system,
194 x_buyer_id,
195 x_buyer_username,
196 x_buyer_display_name);
197 x_progress := '004';
198
199 wf_engine.SetItemAttrText ( itemtype => itemtype,
200 itemkey => itemkey,
201 aname => 'BUYER_USER_NAME',
202 avalue => x_buyer_username);
203 end if;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 wf_core.context('POS_WF_PO_COMMON_APIS','Get_Buyer_username',x_progress);
208 raise;
209 end;
210
211 --
212
213 procedure get_default_inventory_org ( itemtype in varchar2,
214 itemkey in varchar2,
215 actid in number,
216 funcmode in varchar2,
217 result out NOCOPY varchar2 )
218 is
219 x_def_inv_org number;
220 x_progress varchar2(3) := '000';
221 begin
222
223 select inventory_organization_id
224 into x_def_inv_org
225 from financials_system_parameters;
226
227 wf_engine.SetItemAttrText ( itemtype => itemtype,
228 itemkey => itemkey,
229 aname => 'SHIP_TO_ORGANIZATION_ID',
230 avalue => x_def_inv_org);
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 wf_core.context('POS_WF_PO_COMMON_APIS','get_default_inventory_org',x_progress);
235 raise;
236 end;
237
238 --
239
240 procedure set_attributes ( itemtype in varchar2,
241 itemkey in varchar2,
242 actid in number,
243 funcmode in varchar2,
244 result out NOCOPY varchar2 )
245 is
246 x_default_inv_org number;
247 x_item_id number;
248 x_buyer_id number;
249 x_progress varchar2(3) := '000';
250 begin
251
252 x_item_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
253 itemkey => itemkey,
254 aname => 'ITEM_ID');
255
256 x_default_inv_org := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
257 itemkey => itemkey,
258 aname => 'SHIP_TO_ORGANIZATION_ID');
259
260 x_progress := '001';
261
262 select buyer_id into x_buyer_id
263 from mtl_system_items
264 where inventory_item_id = x_item_id
265 and organization_id = x_default_inv_org;
266
267 wf_engine.SetItemAttrText ( itemtype => itemtype,
268 itemkey => itemkey,
269 aname => 'BUYER_USER_ID',
270 avalue => x_buyer_id);
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 wf_core.context('POS_WF_PO_COMMON_APIS','set_attributes',x_progress);
275 raise;
276 end;
277
278
279 --
280
281 procedure get_supplier ( itemtype in varchar2,
282 itemkey in varchar2,
283 actid in number,
284 funcmode in varchar2,
285 result out NOCOPY varchar2 )
286 is
287 x_document_id number;
288 x_vendor_id number;
289 x_vendor varchar2(80);
290 x_progress varchar2(3) := '000';
291 begin
292 --dbms_output.put_line (' In Get Supplier ');
293 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
294 itemkey => itemkey,
295 aname => 'DOCUMENT_ID');
296
297 x_vendor_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
298 itemkey => itemkey,
299 aname => 'SUPPLIER_ID');
300
301 --if x_document_id is not null and x_vendor_id is null then
302 if x_document_id is not null then
303
304 begin
305 x_progress := '001';
306 select vendor_id into x_vendor_id
307 from po_headers_all
308 where po_header_id = x_document_id;
309
310 x_progress := '002';
311 exception
312 when others then
313 null;
314 end;
315
316 if x_vendor_id is null then
317 begin
318 x_progress := '003';
319 select vendor_id
320 into x_vendor_id
321 from po_headers_all
322 where po_header_id = (select po_header_id from po_releases_all
323 where po_release_id = x_document_id);
324
325 x_progress := '004';
326 exception
327 when others then
328 null;
329 end ;
330 end if;
331 end if;
332 x_progress := '005';
333
334 if x_vendor_id is not null then
335 begin
336 select vendor_name into x_vendor
337 from po_vendors
338 where vendor_id = x_vendor_id;
339
340 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
341 itemkey => itemkey,
342 aname => 'SUPPLIER_ID',
343 avalue => x_vendor_id);
344
345 wf_engine.SetItemAttrText ( itemtype => itemtype,
346 itemkey => itemkey,
347 aname => 'SUPPLIER',
348 avalue => x_vendor);
349 exception
350 when others then
351 null;
352 end ;
353 end if;
354 -- call get supplier user name
355 get_supplier_username ( itemtype,itemkey, actid, funcmode , result );
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 wf_core.context('POS_WF_PO_COMMON_APIS','get_supplier',x_progress);
360 raise;
361 end;
362
363 --
364
365 procedure purge_workflow ( x_document_id in number )
366 is
367 x_doc_revision number;
368 x_progress varchar2(3) := '000';
369 l_item_type varchar2(10) := 'POSPOACK';
370 l_item_key varchar2(240);
371 begin
372
373 -- abort any outstanding acceptance notifications for any previous revision of the document.
374
375 select nvl(revision_num, 0)
376 into x_doc_revision
377 from po_headers
378 where po_header_id = x_document_id;
379
380 while x_doc_revision >= 0 loop
381
382 l_item_key := 'POS_ACK_' || to_char (x_document_id) || '_' || to_char(x_doc_revision);
383
384 begin
385 -- Abort process if it exists
386 wf_engine.abortprocess (l_item_type, l_item_key);
387 exception
388 when others then
389 null;
390 end;
391
392 -- purge the workflow
393
394 wf_purge.items (l_item_type, l_item_key);
395
396 x_doc_revision := x_doc_revision - 1;
397
398 end loop;
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 raise;
403 end;
404
405
406
407 END POS_WF_PO_COMMON_APIS;