[Home] [Help]
PACKAGE BODY: APPS.POS_COMMON_APIS
Source
1 PACKAGE BODY POS_COMMON_APIS AS
2 /* $Header: POSCOMAB.pls 115.4 2002/11/26 02:31:35 ammitra ship $ */
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
107 document := l_document_num;
108 /*document := '<A HREF="OracleON.IC?Y=' || Y || '">' || l_document_num || '</A>'; */
109
110 --dbms_output.put_line('After Get Po Details ' || document || document_type);
111
112 exception
113 WHEN OTHERS THEN
114 wf_core.context('POS_WF_PO_ACKNOWLEDGE','Get_PO_Details_URL',x_progress);
115 raise;
116 end;
117
118 --
119
120 procedure get_supplier_username ( itemtype in varchar2,
121 itemkey in varchar2,
122 actid in number,
123 funcmode in varchar2,
124 result out nocopy varchar2 )
125
126 is
127 x_progress varchar2(3) := '000';
128 x_supplier_user_id number;
129 x_supplier_username varchar2(100);
130 x_supplier_display_name varchar2(240);
131 x_orig_system varchar2(4);
132 begin
133
134 --dbms_output.put_line (' In Get Supplier Username ');
135 x_supplier_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
136 itemkey => itemkey,
137 aname => 'SUPPLIER_USER_ID');
138
139 --dbms_output.put_line (' Get Supplier Username ' || x_supplier_user_id);
140 if x_supplier_user_id is not null then
141
142 x_progress := '003';
143
144 -- Get the supplier user name
145
146 x_orig_system:= 'PER';
147
148 WF_DIRECTORY.GetUserName( x_orig_system,
149 x_supplier_user_id,
150 x_supplier_username,
151 x_supplier_display_name);
152 x_progress := '004';
153
154 wf_engine.SetItemAttrText ( itemtype => itemtype,
155 itemkey => itemkey,
156 aname => 'SUPPLIER_USER_NAME',
157 avalue => x_supplier_username);
158 end if;
159 --dbms_output.put_line (' result in Get Supplier Username ' || x_supplier_username);
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 wf_core.context('POS_WF_PO_COMMON_APIS','Get_Supplier_username',x_progress);
164 raise;
165 end;
166
167 --
168
169 procedure get_buyer_username ( itemtype in varchar2,
170 itemkey in varchar2,
171 actid in number,
172 funcmode in varchar2,
173 result out nocopy varchar2 )
174
175 is
176 x_progress varchar2(3) := '000';
177 x_buyer_id number;
178 x_buyer_username varchar2(100);
179 x_buyer_display_name varchar2(240);
180 x_orig_system varchar2(4);
181 begin
182
183 x_buyer_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
184 itemkey => itemkey,
185 aname => 'BUYER_USER_ID');
186
187 if x_buyer_id is not null then
188
189 x_progress := '003';
190
191 -- Get the buyer user name
192
193 x_orig_system:= 'PER';
194
195 WF_DIRECTORY.GetUserName( x_orig_system,
196 x_buyer_id,
197 x_buyer_username,
198 x_buyer_display_name);
199 x_progress := '004';
200
201 wf_engine.SetItemAttrText ( itemtype => itemtype,
202 itemkey => itemkey,
203 aname => 'BUYER_USER_NAME',
204 avalue => x_buyer_username);
205 end if;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 wf_core.context('POS_WF_PO_COMMON_APIS','Get_Buyer_username',x_progress);
210 raise;
211 end;
212
213 --
214
215 procedure get_default_inventory_org ( itemtype in varchar2,
216 itemkey in varchar2,
217 actid in number,
218 funcmode in varchar2,
219 result out nocopy varchar2 )
220 is
221 x_def_inv_org number;
222 x_progress varchar2(3) := '000';
223 begin
224
225 select inventory_organization_id
226 into x_def_inv_org
227 from financials_system_parameters;
228
229 wf_engine.SetItemAttrText ( itemtype => itemtype,
230 itemkey => itemkey,
231 aname => 'SHIP_TO_ORGANIZATION_ID',
232 avalue => x_def_inv_org);
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 wf_core.context('POS_WF_PO_COMMON_APIS','get_default_inventory_org',x_progress);
237 raise;
238 end;
239
240 --
241
242 procedure set_attributes ( itemtype in varchar2,
243 itemkey in varchar2,
244 actid in number,
245 funcmode in varchar2,
246 result out nocopy varchar2 )
247 is
248 x_default_inv_org number;
249 x_item_id number;
250 x_buyer_id number;
251 x_progress varchar2(3) := '000';
252 begin
253
254 x_item_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
255 itemkey => itemkey,
256 aname => 'ITEM_ID');
257
258 x_default_inv_org := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
259 itemkey => itemkey,
260 aname => 'SHIP_TO_ORGANIZATION_ID');
261
262 x_progress := '001';
263
264 select buyer_id into x_buyer_id
265 from mtl_system_items
266 where inventory_item_id = x_item_id
267 and organization_id = x_default_inv_org;
268
269 wf_engine.SetItemAttrText ( itemtype => itemtype,
270 itemkey => itemkey,
271 aname => 'BUYER_USER_ID',
272 avalue => x_buyer_id);
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 wf_core.context('POS_WF_PO_COMMON_APIS','set_attributes',x_progress);
277 raise;
278 end;
279
280
281 --
282
283 procedure get_supplier ( itemtype in varchar2,
284 itemkey in varchar2,
285 actid in number,
286 funcmode in varchar2,
287 result out nocopy varchar2 )
288 is
289 x_document_id number;
290 x_vendor_id number;
291 x_vendor varchar2(240);
292 x_progress varchar2(3) := '000';
293 begin
294 --dbms_output.put_line (' In Get Supplier ');
295 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
296 itemkey => itemkey,
297 aname => 'DOCUMENT_ID');
298
299 x_vendor_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
300 itemkey => itemkey,
301 aname => 'SUPPLIER_ID');
302
303 --if x_document_id is not null and x_vendor_id is null then
304 if x_document_id is not null then
305
306 begin
307 x_progress := '001';
308 select vendor_id into x_vendor_id
309 from po_headers_all
310 where po_header_id = x_document_id;
311
312 x_progress := '002';
313 exception
314 when others then
315 null;
316 end;
317
318 if x_vendor_id is null then
319 begin
320 x_progress := '003';
321 select vendor_id
322 into x_vendor_id
323 from po_headers_all
324 where po_header_id = (select po_header_id from po_releases_all
325 where po_release_id = x_document_id);
326
327 x_progress := '004';
328 exception
329 when others then
330 null;
331 end ;
332 end if;
333 end if;
334 x_progress := '005';
335
336 if x_vendor_id is not null then
337 begin
338 select vendor_name into x_vendor
339 from po_vendors
340 where vendor_id = x_vendor_id;
341
342 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
343 itemkey => itemkey,
344 aname => 'SUPPLIER_ID',
345 avalue => x_vendor_id);
346
347 wf_engine.SetItemAttrText ( itemtype => itemtype,
348 itemkey => itemkey,
349 aname => 'SUPPLIER',
350 avalue => x_vendor);
351 exception
352 when others then
353 null;
354 end ;
355 end if;
356 -- call get supplier user name
357 get_supplier_username ( itemtype,itemkey, actid, funcmode , result );
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 wf_core.context('POS_WF_PO_COMMON_APIS','get_supplier',x_progress);
362 raise;
363 end;
364
365 --
366
367 procedure purge_workflow ( x_document_id in number )
368 is
369 x_doc_revision number;
370 x_progress varchar2(3) := '000';
371 l_item_type varchar2(10) := 'POSPOACK';
372 l_item_key varchar2(240);
373 begin
374
375 -- abort any outstanding acceptance notifications for any previous revision of the document.
376
377 select nvl(revision_num, 0)
378 into x_doc_revision
379 from po_headers
380 where po_header_id = x_document_id;
381
382 while x_doc_revision >= 0 loop
383
384 l_item_key := 'POS_ACK_' || to_char (x_document_id) || '_' || to_char(x_doc_revision);
385
386 begin
387 -- Abort process if it exists
388 wf_engine.abortprocess (l_item_type, l_item_key);
389 exception
390 when others then
391 null;
392 end;
393
394 -- purge the workflow
395
396 wf_purge.items (l_item_type, l_item_key);
397
398 x_doc_revision := x_doc_revision - 1;
399
400 end loop;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 raise;
405 end;
406
407
408
409 END POS_COMMON_APIS;