1 PACKAGE BODY PO_SUP_CHG_REQUEST_WF_GRP AS
2 /* $Header: POXGSCWB.pls 120.2 2006/09/12 10:12:10 jbalakri noship $ */
3
4
5 procedure Buyer_CancelDocWithChn( p_api_version in number,
6 x_return_status out NOCOPY varchar2,
7 p_header_id in number,
8 p_release_id in number,
9 p_revision_num in number,
10 p_chn_grp_id in number,
11 x_return_msg out NOCOPY varchar2)
12 IS
13 l_return_status varchar2(1);
14 l_err_msg varchar2(2000);
15 l_return_code varchar2(10);
16 l_doc_check_rec_type POS_ERR_TYPE;
17 l_progress varchar2(3) := '000';
18 l_all_responded varchar2(1);
19 l_acc_req_flag varchar2(1);
20 l_call_PR_flag varchar2(1);
21 l_pending_count number;
22 BEGIN
23 x_return_status := FND_API.G_RET_STS_SUCCESS;
24
25 if(p_release_id is null) then
26 select nvl(acceptance_required_flag,'N')
27 into l_acc_req_flag
28 from po_headers_all
29 where po_header_id = p_header_id;
30
31 select count(1) into
32 l_pending_count
33 from po_change_requests
34 where initiator = 'SUPPLIER'
35 and document_header_id = p_header_id
36 and request_status = 'PENDING'
37 and change_active_flag = 'Y';
38
39 else
40 select nvl(acceptance_required_flag,'N')
41 into l_acc_req_flag
42 from po_releases_all
43 where po_release_id = p_release_id;
44
45 select count(1) into
46 l_pending_count
47 from po_change_requests
48 where initiator = 'SUPPLIER'
49 and document_header_id = p_header_id
50 and po_release_id = p_release_id
51 and request_status = 'PENDING'
52 and change_active_flag = 'Y';
53
54 end if;
55
56 l_call_PR_flag := 'N';
57 if(l_pending_count = 0) then
58 if(l_acc_req_flag = 'Y') then
59 l_all_responded := po_acknowledge_po_grp.all_shipments_responded(
60 p_api_version => 1.0,
61 p_init_msg_list => FND_API.G_FALSE,
62 p_po_header_id => p_header_id,
63 p_po_release_id => p_release_id,
64 p_revision_num => p_revision_num);
65 if(l_all_responded = 'T') then
66 l_call_PR_flag := 'Y';
67 end if;
68 else
69 l_call_PR_flag := 'Y';
70 end if;
71 end if;
72
73 if(l_call_PR_flag = 'Y') then
74 l_progress := '003';
75 ProcessResponse(
76 p_api_version => 1.0,
77 x_return_status => l_return_status,
78 p_header_id => p_header_id,
79 p_release_id => p_release_id,
80 p_revision_num => p_revision_num,
81 p_chg_req_grp_id => p_chn_grp_id,
82 p_user_id => fnd_global.user_id,
83 x_err_msg => l_err_msg,
84 x_return_code => l_return_code,
85 x_doc_check_rec_type => l_doc_check_rec_type);
86 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) then
87 x_return_status := FND_API.G_RET_STS_ERROR;
88 x_return_msg := 'BCD:ERROR:'||l_err_msg;
89 end if;
90 end if;
91 exception when others then
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
93 x_return_msg := 'BCD:UNEXP:'||l_progress||':'||l_return_status||':'||sqlerrm;
94 END Buyer_CancelDocWithChn;
95
96
97 /*
98 *taken from PO_REQAPPROVAL_INIT1
99 *PO_REQAPPROVAL_INIT1.Register_rejection will be updated to support older version of poxwfpoa.wft
100 *In other words, initial version of poxwfpoa.wft only calls PO_REQ_APPROVAL_INIT1.Register_rejection.
101 *New version will call po_sup_chg_request_wf_grp.IS_PO_HDR_REJECTED followed by po_sup_chg_request_wf_grp.Register_rejection.
102 *In order for older version of workflow to have the new functionality, PO_REQ_APPROVAL_INIT1.Register_rejection will
103 *need to include the logic of po_sup_chg_request_wf_grp.IS_PO_HDR_REJECTED within.
104 */
105 procedure Register_rejection ( itemtype in varchar2,
106 itemkey in varchar2,
107 actid in number,
108 funcmode in varchar2,
109 result out NOCOPY varchar2 )
110 is
111 begin
112 PO_ChangeOrderWF_PVT.Register_rejection( itemtype,
113 itemkey,
114 actid,
115 funcmode,
116 result);
117 end Register_rejection;
118
119 /*
120 *Kicks of POAPPRV workflow for supplier change or for requester change.
121 */
122 procedure KickOffPOApproval( p_api_version in number,
123 x_return_status out NOCOPY varchar2,
124 p_header_id in number,
125 p_release_id in number,
126 x_return_msg out NOCOPY varchar2)
127 IS
128 BEGIN
129 PO_ChangeOrderWF_PVT.KickOffPOApproval( p_api_version,
130 x_return_status,
131 p_header_id,
132 p_release_id,
133 x_return_msg);
134 END KickOffPOApproval;
135
136
137 /*
138 *Called from POAPPRV workflow, to execute IsPOHeaderRejected
139 */
140 procedure IS_PO_HDR_REJECTED( itemtype in varchar2,
141 itemkey in varchar2,
142 actid in number,
143 funcmode in varchar2,
144 resultout out NOCOPY varchar2)
145 IS
146 BEGIN
147 PO_ChangeOrderWF_PVT.IS_PO_HDR_REJECTED( itemtype,
148 itemkey,
149 actid,
150 funcmode,
151 resultout);
152 END IS_PO_HDR_REJECTED;
153
154
155
156 /*
157 *Prorate is needed if supplier has changed the Quantity of a PO SHipment with multiple distributions.
158 */
159 procedure IS_PRORATE_NEEDED( itemtype in varchar2,
160 itemkey in varchar2,
161 actid in number,
162 funcmode in varchar2,
163 resultout out NOCOPY varchar2)
164 IS
165 BEGIN
166 PO_ChangeOrderWF_PVT.IS_PRORATE_NEEDED( itemtype,
167 itemkey,
168 actid,
169 funcmode,
170 resultout);
171 END IS_PRORATE_NEEDED;
172
173 /*
174 *update authorization_status of PO to "APPROVED".
175 */
176 procedure CHG_STATUS_TO_APPROVED(itemtype in varchar2,
177 itemkey in varchar2,
178 actid in number,
179 funcmode in varchar2,
180 resultout out NOCOPY varchar2)
181 IS
182 BEGIN
183 PO_ChangeOrderWF_PVT.CHG_STATUS_TO_APPROVED(itemtype, itemkey, actid,
184 funcmode, resultout);
185 END CHG_STATUS_TO_APPROVED;
186
187
188 /*
189 *Supplier could have changed and accepted shipments at the same time. Once the change requests are responded, we will need
190 *to carry over the previously accepted shipments to the new revision, by Calling
191 *PO_ACKNOWLEDGE_PO_GRP.carry_over_acknowledgement
192 */
193 procedure CARRY_SUP_ACK_TO_NEW_REV( itemtype in varchar2,
194 itemkey in varchar2,
195 actid in number,
196 funcmode in varchar2,
197 resultout out NOCOPY varchar2)
198 IS
199 BEGIN
200 PO_ChangeOrderWF_PVT.CARRY_SUP_ACK_TO_NEW_REV( itemtype,
201 itemkey,
202 actid,
203 funcmode,
204 resultout);
205 END CARRY_SUP_ACK_TO_NEW_REV;
206
207 /*
208 *Checks if acceptance_required_flag = 'Y'
209 */
210 procedure DOES_PO_REQ_SUP_ACK( itemtype in varchar2,
211 itemkey in varchar2,
212 actid in number,
213 funcmode in varchar2,
214 resultout out NOCOPY varchar2)
215 IS
216 BEGIN
217 PO_ChangeOrderWF_PVT.DOES_PO_REQ_SUP_ACK( itemtype,
218 itemkey,
219 actid,
220 funcmode,
221 resultout);
222 END DOES_PO_REQ_SUP_ACK;
223
224 /*
225 *Checks if PO Change request is approved/rejected by the PO Approval Hierachy.
226 *Meanwhile, prepare the notification which is to be sent to the supplier, informing him/her of buyer's response to
227 *Supplier's Change request
228 */
229 procedure is_po_approved_by_hie( itemtype in varchar2,
230 itemkey in varchar2,
231 actid in number,
232 funcmode in varchar2,
233 resultout out NOCOPY varchar2)
234 IS
235 BEGIN
236 PO_ChangeOrderWF_PVT.is_po_approved_by_hie( itemtype,
237 itemkey,
238 actid,
239 funcmode,
240 resultout);
241 END is_po_approved_by_hie;
242
243
244 procedure set_data_sup_chn_evt( itemtype in varchar2,
245 itemkey in varchar2,
246 actid in number,
247 funcmode in varchar2,
248 resultout out NOCOPY varchar2)
249 IS
250 BEGIN
251 PO_ChangeOrderWF_PVT.set_data_sup_chn_evt( itemtype,
252 itemkey,
253 actid,
254 funcmode,
255 resultout);
256 END set_data_sup_chn_evt;
257
258
259 procedure ANY_NEW_SUP_CHN( itemtype in varchar2,
260 itemkey in varchar2,
261 actid in number,
262 funcmode in varchar2,
263 resultout out NOCOPY varchar2)
264 IS
265 BEGIN
266 PO_ChangeOrderWF_PVT.ANY_NEW_SUP_CHN( itemtype,
267 itemkey,
268 actid,
269 funcmode,
270 resultout);
271 END ANY_NEW_SUP_CHN;
272
273 procedure any_supplier_change( itemtype in varchar2,
274 itemkey in varchar2,
275 actid in number,
276 funcmode in varchar2,
277 resultout out NOCOPY varchar2)
278 IS
279 BEGIN
280 PO_ChangeOrderWF_PVT.any_supplier_change( itemtype,
281 itemkey,
282 actid,
283 funcmode,
284 resultout);
285 END any_supplier_change;
286
287 procedure ProcessHdrCancelResponse( p_api_version in number,
288 x_return_status out NOCOPY varchar2,
289 p_header_id in number,
290 p_release_id in number,
291 p_revision_num in number,
292 p_user_id in number,
293 p_acc_rej in varchar2,
294 p_reason in varchar2)
295 IS
296 BEGIN
297 PO_ChangeOrderWF_PVT.ProcessHdrCancelResponse( p_api_version,
298 x_return_status,
299 p_header_id,
300 p_release_id,
301 p_revision_num,
302 p_user_id,
303 p_acc_rej,
304 p_reason);
305 End ProcessHdrCancelResponse;
306
307
308 /*
309 *This API could originate from 2 sources
310 *1. Buyer Accept or Reject Supplier Change through Notification => Buyer Accept ALL OR Rejects ALL
311 *2. Buyer Accept or Reject Supplier Change through UI. If response DOES NOT cover all changes
312 * return;
313 * Else (response cover all changes)
314 * continue...
315 * 1. Send Notification to Supplier if all changes are responded
316 * => NO BUYER_APP/PENDING
317 * 2. Send Notification to Buyer if PO requires Acknowledgement, and everything is responded
318 * => ACC_REQUIRED_FLAG = 'Y'
319 * NO BUYER_APP/PENDING
320 * ALL SHIPMENTS ACCEPTED/REJECTED
321 *
322 *
323 */
324 procedure ProcessResponse( p_api_version in number,
325 x_return_status out NOCOPY varchar2,
326 p_header_id in number,
327 p_release_id in number,
328 p_revision_num in number,
329 p_chg_req_grp_id in number,
330 p_user_id in number,
331 x_err_msg out NOCOPY varchar2,
332 x_return_code out NOCOPY number,
333 x_doc_check_rec_type out NOCOPY POS_ERR_TYPE,
334 p_flag in varchar2 default null,
335 p_launch_approvals_flag in varchar2 default 'Y',
336 p_mass_update_releases IN VARCHAR2 DEFAULT NULL -- Bug 3373453
337 )
338 IS
339 BEGIN
340 PO_ChangeOrderWF_PVT.ProcessResponse( p_api_version,
341 x_return_status,
342 p_header_id,
343 p_release_id,
344 p_revision_num,
345 p_chg_req_grp_id,
346 p_user_id,
347 x_err_msg,
348 x_return_code,
349 x_doc_check_rec_type,
350 p_flag,
351 p_launch_approvals_flag,
352 p_mass_update_releases
353 );
354 END ProcessResponse;
355
356 procedure NOTIFY_REQ_PLAN ( itemtype in varchar2,
357 itemkey in varchar2,
358 actid in number,
359 funcmode in varchar2,
360 resultout out NOCOPY varchar2)
361 IS
362 BEGIN
363 PO_ChangeOrderWF_PVT.NOTIFY_REQ_PLAN ( itemtype,
364 itemkey,
365 actid,
366 funcmode,
367 resultout);
368 END NOTIFY_REQ_PLAN;
369
370
371 procedure PROCESS_RESPONSE ( itemtype in varchar2,
372 itemkey in varchar2,
373 actid in number,
374 funcmode in varchar2,
375 resultout out NOCOPY varchar2)
376 IS
377 BEGIN
378 PO_ChangeOrderWF_PVT.PROCESS_RESPONSE ( itemtype,
379 itemkey,
380 actid,
381 funcmode,
382 resultout);
383 END PROCESS_RESPONSE;
384
385 procedure BUYER_ACCEPT_CHANGE ( itemtype in varchar2,
386 itemkey in varchar2,
387 actid in number,
388 funcmode in varchar2,
389 resultout out NOCOPY varchar2)
390 IS
391 BEGIN
392 PO_ChangeOrderWF_PVT.BUYER_ACCEPT_CHANGE ( itemtype,
393 itemkey,
394 actid,
395 funcmode,
396 resultout);
397 end BUYER_ACCEPT_CHANGE;
398
399 procedure BUYER_REJECT_CHANGE ( itemtype in varchar2,
400 itemkey in varchar2,
401 actid in number,
402 funcmode in varchar2,
403 resultout out NOCOPY varchar2)
404 IS
405 BEGIN
406 PO_ChangeOrderWF_PVT.BUYER_REJECT_CHANGE ( itemtype,
407 itemkey,
408 actid,
409 funcmode,
410 resultout);
411 end BUYER_REJECT_CHANGE;
412
413 /*
414 This API is called from ISP Change Details Page. If the supplier submits a change, OR, if the supplier completely finishes
415 acknowledging the PO at the shipment level (which may contain accept/reject/change), this API will be executed.
416 */
417 procedure StartSupplierChangeWF( p_api_version in number,
418 x_return_status out NOCOPY varchar2,
419 p_header_id in number,
420 p_release_id in number,
421 p_revision_num in number,
422 p_chg_req_grp_id in number,
423 p_acc_req_flag in varchar2)
424 IS
425 BEGIN
426 PO_ChangeOrderWF_PVT.StartSupplierChangeWF( p_api_version,
427 x_return_status,
428 p_header_id,
429 p_release_id,
430 p_revision_num,
431 p_chg_req_grp_id,
432 p_acc_req_flag);
433 end StartSupplierChangeWF;
434
435 procedure set_data_chn_resp_evt (itemtype in varchar2,
436 itemkey in varchar2,
437 actid in number,
438 funcmode in varchar2,
439 resultout out NOCOPY varchar2)
440 IS
441 l_change_request_group_id number;
442 l_seq_for_item_key number;
443 l_itemKey varchar2(256);
444
445 BEGIN
446 l_change_request_group_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
447 itemkey => itemkey,
448 aname => 'CHANGE_REQUEST_GROUP_ID');
449
450 /* Get the unique sequence to make sure item key will be unique */
451
452 select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
453 into l_seq_for_item_key
454 from sys.dual;
455
456 /* The item key is the req_line_id concatenated with the
457 * unique id from a seq.
458 */
459
460 l_ItemKey := to_char(l_change_request_group_id) || '-' || l_seq_for_item_key;
461
462 wf_engine.SetItemAttrText (itemtype => itemtype,
463 itemkey => itemkey,
464 aname => 'CHN_RESP_EVENT_KEY',
465 avalue => l_ItemKey);
466
467
468
469 END set_data_chn_resp_evt;
470
471 procedure IS_XML_CHN_REQ_SOURCE(itemtype in varchar2,
472 itemkey in varchar2,
473 actid in number,
474 funcmode in varchar2,
475 resultout out NOCOPY varchar2)
476 IS
477 l_change_request_group_id number;
478 src varchar2(30);
479 BEGIN
480 l_change_request_group_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
481 itemkey => itemkey,
482 aname => 'CHANGE_REQUEST_GROUP_ID');
483
484 begin
485 /*
486 Note: The Change request handling sometimes can create new rows with request_origin null.
487 */
488 select min(request_origin) into src
489 from po_change_requests
490 where change_request_group_id = l_change_request_group_id and
491 request_origin is not null;
492
493 exception when no_data_found then
494 src := null;
495 end;
496
497 if (src is null or src = 'UI') then
498 resultout := 'N';
499 else --it can be XML or 9iAS or OTA
500 resultout := 'Y';
501 end if;
502
503
504 END IS_XML_CHN_REQ_SOURCE;
505 /*
506 This procedure sets the supplier user context after the auto acceptance of
507 the PO.
508 */
509
510 procedure SET_SUPPLIER_CONTEXT(itemtype in varchar2,
511 itemkey in varchar2,
512 actid in number,
513 funcmode in varchar2,
514 resultout out NOCOPY varchar2)
515 IS
516 l_supplier_username FND_USER.USER_NAME%TYPE;
517 l_supplier_user_id FND_USER.USER_ID%TYPE;
518 l_appl_resp_id NUMBER;
519 l_resp_id NUMBER;
520 BEGIN
521
522 l_supplier_username := wf_engine.GetItemAttrText (itemtype => itemtype,
523 itemkey => itemkey,
524 aname => 'FROM_SUPPLIER');
525 l_resp_id := wf_engine.GetItemAttrText (itemtype => itemtype,
526 itemkey => itemkey,
527 aname => 'RESP_ID');
528 l_appl_resp_id := wf_engine.GetItemAttrText (itemtype => itemtype,
529 itemkey => itemkey,
530 aname => 'APPL_RESP_ID');
531
532 select user_id
533 into l_supplier_user_id
534 from fnd_user
535 where user_name=l_supplier_username
536 and rownum=1;
537
538 PO_ChangeOrderWF_PVT.SET_SUPPLIER_CONTEXT(l_supplier_user_id,l_resp_id,l_appl_resp_id);
539
540 END SET_SUPPLIER_CONTEXT;
541
542 END PO_SUP_CHG_REQUEST_WF_GRP;
[Home] [Help]