[Home] [Help]
PACKAGE BODY: APPS.POS_WF_PO_ACKNOWLEDGE
Source
1 PACKAGE BODY POS_WF_PO_ACKNOWLEDGE AS
2 /* $Header: POSWPOAB.pls 115.4 2003/07/24 02:41:52 rbairraj noship $ */
3
4
5 --
6
7 /*
8 Private Procedure
9 */
10
11 Procedure Insert_Acc_Rejection_Row(itemtype in varchar2,
12 itemkey in varchar2,
13 actid in number,
14 flag in varchar2);
15
16
17 --
18 --
19 --
20
21 procedure acceptance_required ( itemtype in varchar2,
22 itemkey in varchar2,
23 actid in number,
24 funcmode in varchar2,
25 result out NOCOPY varchar2 )
26 is
27 x_doc_header_id number;
28 x_acceptance_flag varchar2(1) := null;
29 x_acceptance_due_date date;
30 x_progress varchar2(3) := '000';
31 begin
32 x_progress := '001';
33
34 x_acceptance_due_date := wf_engine.GetItemAttrDate ( itemtype => itemtype,
35 itemkey => itemkey,
36 aname => 'ACCEPTANCE_DUE_DATE');
37
38 x_acceptance_flag := wf_engine.GetItemAttrText ( itemtype => itemtype,
39 itemkey => itemkey,
40 aname => 'ACCEPTANCE_REQUIRED');
41
42 if (x_acceptance_due_date is NULL or nvl(x_acceptance_flag, 'N') <> 'Y') then
43
44 wf_engine.SetItemAttrText ( itemtype => itemtype,
45 itemkey => itemkey,
46 aname => 'BY',
47 avalue => '');
48 end if;
49
50 result := 'COMPLETE:' || nvl(x_acceptance_flag, 'N');
51
52 exception
53 WHEN OTHERS THEN
54 wf_core.context('POS_WF_PO_ACKNOWLEDGE','acceptance_required',x_progress);
55 raise;
56 end;
57
58 --
59
60 procedure Register_acceptance ( itemtype in varchar2,
61 itemkey in varchar2,
62 actid in number,
63 funcmode in varchar2,
64 result out NOCOPY varchar2 )
65 is
66 x_progress varchar2(3) := '000';
67 x_acceptance_result varchar2(30);
68 x_org_id number;
69 begin
70
71 -- set the org context
72 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
73 itemkey => itemkey,
74 aname => 'ORG_ID');
75
76 fnd_client_info.set_org_context(to_char(x_org_id));
77
78 fnd_message.set_name ('ICX','POS_PO_WF_ACCEPTED_VALUE');
79 x_acceptance_result := fnd_message.get;
80
81 wf_engine.SetItemAttrText ( itemtype => itemtype,
82 itemkey => itemkey,
83 aname => 'ACCEPTANCE_RESULT',
84 avalue => nvl(x_acceptance_result, 'Accepted'));
85
86 -- insert acceptance record.
87
88 Insert_Acc_Rejection_Row(itemtype, itemkey, actid, 'Y');
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 wf_core.context('POS_WF_PO_ACKNOWLEDGE','Register_acceptance',x_progress);
93 raise;
94 end;
95
96 --
97
98 procedure Register_rejection ( itemtype in varchar2,
99 itemkey in varchar2,
100 actid in number,
101 funcmode in varchar2,
102 result out NOCOPY varchar2 )
103 is
104 x_progress varchar2(3) := '000';
105 x_acceptance_result varchar2(30);
106 x_org_id number;
107 begin
108
109 -- set the org context
110 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
111 itemkey => itemkey,
112 aname => 'ORG_ID');
113
114 fnd_client_info.set_org_context(to_char(x_org_id));
115
116 fnd_message.set_name ('ICX','POS_PO_WF_REJECTED_VALUE');
117 x_acceptance_result := fnd_message.get;
118
119 wf_engine.SetItemAttrText ( itemtype => itemtype,
120 itemkey => itemkey,
121 aname => 'ACCEPTANCE_RESULT',
122 avalue => nvl(x_acceptance_result, 'Rejected'));
123
124 -- insert rejection record.
125
126 Insert_Acc_Rejection_Row(itemtype, itemkey, actid, 'N');
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 wf_core.context('POS_WF_PO_ACKNOWLEDGE','Register_rejection',x_progress);
131 raise;
132 end;
133
134 --
135
136 procedure Initialize_Attributes( itemtype in varchar2,
137 itemkey in varchar2,
138 actid in number,
139 funcmode in varchar2,
140 result out NOCOPY varchar2 )
141 is
142 x_document_id number;
143 x_document_type_code varchar2(60);
144 x_document_num varchar2(60);
145 x_document_type varchar2(80);
146 x_release_num number := null;
147 x_agent_id number;
148 x_acceptance_required_flag varchar2(1);
149 x_acceptance_due_date date := null;
150 x_progress varchar2(3) := '000';
151 x_org_id number;
152 p_rowid VARCHAR2(2000);
153 l_param VARCHAR2(2000);
154 begin
155 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
156 itemkey => itemkey,
157 aname => 'DOCUMENT_ID');
158
159 x_document_type_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
160 itemkey => itemkey,
161 aname => 'DOCUMENT_TYPE_CODE');
162 --dbms_output.put_line('Document Code is ' || x_document_type_code );
163 if x_document_type_code <> 'RELEASE' then
164
165 select poh.segment1 || '-' || poh.revision_num, polc.displayed_field, poh.agent_id,
166 poh.acceptance_required_flag, poh.acceptance_due_date, poh.org_id
167 into x_document_num, x_document_type, x_agent_id, x_acceptance_required_flag,
168 x_acceptance_due_date, x_org_id
169 from po_headers_all poh,
170 po_lookup_codes polc
171 where poh.po_header_id = x_document_id
172 and poh.type_lookup_code = polc.lookup_code
173 and polc.lookup_type = 'PO TYPE';
174
175 wf_engine.SetItemAttrText ( itemtype => itemtype,
176 itemkey => itemkey,
177 aname => 'FOR',
178 avalue => '');
179
180 else
181 select por.release_num, por.agent_id, poh.segment1 || '-' || poh.revision_num, polc.displayed_field,
182 por.acceptance_required_flag, por.acceptance_due_date, por.agent_id
183 into x_release_num, x_agent_id, x_document_num, x_document_type, x_acceptance_required_flag,
184 x_acceptance_due_date, x_agent_id
185 from po_releases_all por,
186 po_headers_all poh,
187 po_lookup_codes polc
188 where por.po_release_id = x_document_id
189 and por.po_header_id = poh.po_header_id
190 and polc.lookup_type = 'DOCUMENT TYPE'
191 and polc.lookup_code = 'RELEASE';
192 end if;
193
194 -- Set Item Attributes.
195
196 wf_engine.SetItemAttrText ( itemtype => itemtype,
197 itemkey => itemkey,
198 aname => 'DOCUMENT_NUM',
199 avalue => x_document_num);
200
201 wf_engine.SetItemAttrText ( itemtype => itemtype,
202 itemkey => itemkey,
203 aname => 'DOCUMENT_TYPE',
204 avalue => x_document_type);
205
206 wf_engine.SetItemAttrText ( itemtype => itemtype,
207 itemkey => itemkey,
208 aname => 'ACCEPTANCE_REQUIRED',
209 avalue => x_acceptance_required_flag);
210
211 wf_engine.SetItemAttrDate ( itemtype => itemtype,
212 itemkey => itemkey,
213 aname => 'ACCEPTANCE_DUE_DATE',
214 avalue => x_acceptance_due_date);
215
216 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
217 itemkey => itemkey,
218 aname => 'RELEASE_NUM',
219 avalue => x_release_num);
220
221 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
222 itemkey => itemkey,
223 aname => 'BUYER_USER_ID',
224 avalue => x_agent_id);
225
226 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
227 itemkey => itemkey,
228 aname => 'ORG_ID',
229 avalue => x_org_id);
230
231 wf_engine.SetItemAttrText( itemtype => itemtype,
232 itemkey => itemkey,
233 aname => 'PO_DETAILS_URL',
234 avalue => 'PLSQL:POS_WF_PO_COMMON_APIS.GET_PO_DETAILS_URL/' ||
235 itemtype || ':' || itemkey);
236 EXCEPTION
237 WHEN OTHERS THEN
238 wf_core.context('POS_WF_PO_ACKNOWLEDGE','Initialize_Attributes',x_progress);
239 raise;
240 end;
241
242
243 --
244
245
246 Procedure Insert_Acc_Rejection_Row(itemtype in varchar2,
247 itemkey in varchar2,
248 actid in number,
249 flag in varchar2)
250 is
251
252 x_row_id varchar2(30);
253 x_Acceptance_id number;
254 -- Bug 2850566
255 x_Last_Update_Date date ;
256 x_Last_Updated_By number;
257 l_Last_Update_Login PO_ACCEPTANCES.last_update_login%TYPE;
258 -- End of Bug 2850566
259 x_Creation_Date date := TRUNC(SYSDATE);
260 x_Created_By number := fnd_global.user_id;
261 x_Po_Header_Id number;
262 x_Po_Release_Id number;
263 x_Action varchar2(240) := 'NEW';
264 x_Action_Date date := TRUNC(SYSDATE);
265 x_Employee_Id number;
266 x_Revision_Num number;
267 x_Accepted_Flag varchar2(1) := flag;
268 x_Acceptance_Lookup_Code varchar2(25);
269 x_Attribute_Category varchar2(30);
270 x_Attribute1 varchar2(150);
271 x_Attribute2 varchar2(150);
272 x_Attribute3 varchar2(150);
273 x_Attribute4 varchar2(150);
274 x_Attribute5 varchar2(150);
275 x_Attribute6 varchar2(150);
276 x_Attribute7 varchar2(150);
277 x_Attribute8 varchar2(150);
278 x_Attribute9 varchar2(150);
279 x_Attribute10 varchar2(150);
280 x_Attribute11 varchar2(150);
281 x_Attribute12 varchar2(150);
282 x_Attribute13 varchar2(150);
283 x_Attribute14 varchar2(150);
284 x_Attribute15 varchar2(150);
285 x_document_id number;
286 x_document_type_code varchar2(30);
287 begin
288
289 -- Bug 2850566 RBAIRRAJ
290 -- Commenting out the code for selecting the Acceptance Id as it is
291 -- handled in the PO_ACCEPTANCE row handler
292
293 -- SELECT po_acceptances_s.nextval into x_Acceptance_id FROM sys.dual;
294
295 if flag = 'Y' then
296 x_Acceptance_Lookup_Code := 'Accepted Terms';
297 else
298 x_Acceptance_Lookup_Code := 'REJECTED';
299 end if;
300
301 x_document_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
302 itemkey => itemkey,
303 aname => 'DOCUMENT_ID');
304
305 x_document_type_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
306 itemkey => itemkey,
307 aname => 'DOCUMENT_TYPE_CODE');
308
309 -- abort any outstanding acceptance notifications for any previous revision of the document.
310
311 if x_document_type_code <> 'RELEASE' then
312 x_Po_Header_Id := x_document_id;
313
314 select revision_num
315 into x_revision_num
316 from po_headers
317 where po_header_id = x_document_id;
318 else
319 x_Po_Release_Id := x_document_id;
320
321 select po_header_id, revision_num
322 into x_Po_Header_Id, x_revision_num
323 from po_releases
324 where po_release_id = x_document_id;
325 end if;
326
327 -- Bug 2850566 RBAIRRAJ
328 -- Calling the Acceptances row handler to insert into the PO_ACCEPTANCES table
329 -- instead of writing an Insert statement.
330 PO_ACCEPTANCES_INS_PVT.insert_row(
331 x_rowid => x_row_id,
332 x_acceptance_id => x_Acceptance_id,
333 x_Last_Update_Date => x_Last_Update_Date,
334 x_Last_Updated_By => x_Last_Updated_By,
335 x_Last_Update_Login => l_Last_Update_Login,
336 p_creation_date => x_Creation_Date,
337 p_created_by => x_Created_by,
338 p_po_header_id => x_Po_Header_Id,
339 p_po_release_id => x_Po_Release_Id,
340 p_action => x_Action,
341 p_action_date => x_Action_Date,
342 p_employee_id => x_Employee_Id,
343 p_revision_num => x_Revision_Num,
344 p_accepted_flag => x_Accepted_Flag,
345 p_acceptance_lookup_code=> x_Acceptance_Lookup_Code,
346 p_attribute_category => x_Attribute_Category,
347 p_attribute1 => x_Attribute1,
348 p_attribute2 => x_Attribute2,
349 p_attribute3 => x_Attribute3,
350 p_attribute4 => x_Attribute4,
351 p_attribute5 => x_Attribute5,
352 p_attribute6 => x_Attribute6,
353 p_attribute7 => x_Attribute7,
354 p_attribute8 => x_Attribute8,
355 p_attribute9 => x_Attribute9,
356 p_attribute10 => x_Attribute10,
357 p_attribute11 => x_Attribute11,
358 p_attribute12 => x_Attribute12,
359 p_attribute13 => x_Attribute13,
360 p_attribute14 => x_Attribute14,
361 p_attribute15 => x_Attribute15);
362
363 exception
364 when others then
365 raise;
366 end;
367
368 --
369
370 procedure abort_notification ( document_id in number, document_rev in number, document_type varchar2)
371 is
372 x_progress varchar2(3) := '000';
373 l_item_type varchar2(10) := 'POSPOACK';
374 l_item_key varchar2(240);
375 x_document_type_code varchar2(60);
376 x_acceptance_required varchar2(1) := 'N';
377 x_org_id number;
378 begin
379
380 l_item_key := 'POS_ACK_' || to_char (document_id) || '_' || to_char(nvl(document_rev, 0));
381
382 -- set the org context
383 begin
384 x_org_id := wf_engine.GetItemAttrNumber ( itemtype => l_item_type,
385 itemkey => l_item_key,
386 aname => 'ORG_ID');
387
388 fnd_client_info.set_org_context(to_char(x_org_id));
389
390 if document_type = 'RELEASE' then
391 select nvl(acceptance_required_flag, 'N')
392 into x_acceptance_required
393 from po_releases
394 where po_release_id = document_id;
395 else
396 select nvl(acceptance_required_flag, 'N')
397 into x_acceptance_required
398 from po_headers
399 where po_header_id = document_id;
400 end if;
401 exception
402 when others then
403 null;
404 end;
405
406 if x_acceptance_required = 'Y' then
407 begin
408 -- Abort the notification - workflow will take the default transition.
409
410 wf_engine.completeActivity ( l_item_type, l_item_key, 'NOTIFY_SUPPLIER', 'Abort' );
411
412 exception
413 when others then
414 null;
415 end;
416 end if;
417
418 EXCEPTION
419 WHEN OTHERS THEN
420 wf_core.context('POS_WF_PO_ACKNOWLEDGE','abort_notifications',x_progress);
421 raise;
422 end;
423
424 --
425
426 END POS_WF_PO_ACKNOWLEDGE;