[Home] [Help]
PACKAGE BODY: APPS.POR_WITHDRAW_REQ_SV
Source
1 PACKAGE BODY POR_WITHDRAW_REQ_SV AS
2 /* $Header: PORWDRB.pls 115.4 2002/05/07 12:03:21 pkm ship $*/
3 /******************************************************************
4 ** Function : Rebuild_Requisition
5 ** Description : This is a function called from Java layer
6 ** It will use the information of the new
7 ** requisition to restore the existing one.
8 ******************************************************************/
9
10 function Rebuild_Requisition(
11 p_new_requisition_id in number,
12 p_existing_requisition_id in number,
13 p_agentId in number) return number is
14
15 l_req_number po_requisition_headers_all.segment1%type;
16 l_authorization_status varchar2(50);
17 l_req_doc_type VARCHAR2(20) := 'REQUISITION';
18 l_doc_subtype VARCHAR2(20) := 'PURCHASE';
19 l_req_action_history_code VARCHAR2(20) := 'WITHDRAW';
20 l_req_control_reason VARCHAR2(50) :='';
21 l_info_request VARCHAR2(25);
22 l_document_Status VARCHAR2(240);
23 l_online_Report_Id NUMBER;
24 l_return_Code VARCHAR2(25);
25 l_error_Msg VARCHAR2(2000);
26 p_preparer_id NUMBER;
27 success NUMBER;
28 encFlag VARCHAR2(1);
29
30 l_req_encumber varchar2(20);
31 l_gl_period varchar2(20);
32 l_sob_id number;
33
34 CURSOR findEncCursor(requisition_id VARCHAR2) IS select ENCUMBERED_FLAG,LAST_UPDATED_BY from PO_REQUISITION_LINES_ALL where REQUISITION_HEADER_ID = requisition_id;
35
36
37 begin
38
39 -- depends on POXAPFOB.pls 110.2 or higher
40
41 select segment1, authorization_status
42 into l_req_number, l_authorization_status
43 from PO_REQUISITION_HEADERS_ALL
44 where REQUISITION_HEADER_ID = p_existing_requisition_id;
45
46 po_negotiation_req_notif.call_negotiation_wf('WITHDRAW', p_existing_requisition_id);
47
48 open findEncCursor(p_existing_requisition_id);
49 <<encLoop>>
50 loop
51 fetch findEncCursor into encFlag,p_preparer_id;
52 EXIT WHEN findEncCursor%NOTFOUND;
53 IF encFlag = 'Y' THEN
54 success := PO_DOCUMENT_ACTIONS_SV.PO_REQUEST_ACTION('LIQUIDATE_REQ',
55 'REQUISITION',
56 'PURCHASE',
57 p_existing_requisition_id,
58 NULL, NULL, NULL,
59 p_preparer_id,
60 NULL, NULL, NULL, NULL, NULL,
61 sysdate, 'N',
62 l_info_request,l_document_Status,l_online_Report_Id,l_return_Code, l_error_Msg);
63 EXIT encLoop;
64 END IF;
65 end loop;
66 close findEncCursor;
67
68 delete PO_REQ_DISTRIBUTIONS_ALL
69 where REQUISITION_LINE_ID in
70 ( select requisition_line_id
71 from PO_REQUISITION_LINES_ALL
72 where REQUISITION_HEADER_ID = p_existing_requisition_id);
73
74 delete PO_REQUISITION_LINES_ALL
75 where REQUISITION_HEADER_ID = p_existing_requisition_id;
76
77 delete PO_REQUISITION_HEADERS_ALL
78 where REQUISITION_HEADER_ID = p_existing_requisition_id;
79
80 delete po_approval_list_lines
81 where APPROVAL_LIST_HEADER_ID in
82 ( select approval_list_header_id
83 from po_approval_list_headers
84 where document_id = p_existing_requisition_id
85 and document_type = 'REQUISITION');
86
87 delete po_approval_list_headers
88 where document_id = p_existing_requisition_id
89 and document_type = 'REQUISITION';
90
91 update PO_REQUISITION_LINES_ALL
92 set REQUISITION_HEADER_ID = p_existing_requisition_id
93 where REQUISITION_HEADER_ID = p_new_requisition_id;
94
95 update PO_REQUISITION_HEADERS_ALL
96 set REQUISITION_HEADER_ID = p_existing_requisition_id,
97 SEGMENT1 = l_req_number,
98 AUTHORIZATION_STATUS = 'INCOMPLETE'
99 where REQUISITION_HEADER_ID = p_new_requisition_id;
100
101 update po_approval_list_headers
102 set document_id = p_existing_requisition_id
103 where document_id = p_new_requisition_id;
104
105 /* bug 2338259 */
106 /* reset gl_date and gl_period for each distribution if
107 1. use req encumbrance
108 2. req distribution is not encumbered
109 */
110 begin
111 select nvl(fsp.req_encumbrance_flag, 'N'), set_of_books_id
112 into l_req_encumber, l_sob_id
113 from FINANCIALS_SYSTEM_PARAMETERS fsp;
114
115 if (l_req_encumber = 'Y') then
116
117 po_periods_sv.get_period_name(l_sob_id, sysdate, l_gl_period);
118
119 update po_req_distributions
120 set GL_ENCUMBERED_DATE = sysdate,
121 GL_ENCUMBERED_PERIOD_NAME = l_gl_period
122 where nvl(encumbered_flag,'N') = 'N' and
123 REQUISITION_LINE_ID in
124 ( select requisition_line_id
125 from PO_REQUISITION_LINES_ALL
126 where REQUISITION_HEADER_ID = p_existing_requisition_id);
127 end if;
128
129 exception
130 when others then
131 null;
132 end;
133
134
135 IF l_authorization_status= 'IN PROCESS' OR
136 l_authorization_status= 'PRE-APPROVED' THEN
137 po_forward_sv1.update_action_history (p_existing_requisition_id,
138 l_req_doc_type,
139 NULL,
140 l_req_action_history_code,
141 l_req_control_reason,
142 fnd_global.user_id,
143 fnd_global.login_id);
144 ELSE
145 po_forward_sv1.insert_action_history (p_existing_requisition_id,
146 l_req_doc_type,
147 l_doc_subtype,
148 NULL,
149 l_req_action_history_code,
150 sysdate,
151 p_agentId,
152 NULL,
153 l_req_control_reason,
154 NULL,
155 NULL,
156 NULL,
157 NULL,
158 NULL,
159 NULL,
160 fnd_global.user_id,
161 fnd_global.login_id);
162 END IF;
163
164
165 return 0;
166
167 exception
168
169 when others then
170 rollback;
171 return 1;
172
173 end Rebuild_Requisition;
174
175
176 PROCEDURE withdraw_req (p_headerId in NUMBER) IS
177 l_item_type VARCHAR2(8);
178 l_item_key VARCHAR2(240);
179 l_root_activity VARCHAR2(30);
180 l_activity_status VARCHAR2(8);
181
182 l_progress VARCHAR2(300) := '000';
183
184
185 BEGIN
186
187
188 -- abort workflow
189
190 SELECT wf_item_type, wf_item_key
191 INTO l_item_type, l_item_key
192 FROM po_requisition_headers
193 WHERE requisition_header_id= p_headerId;
194
195 IF l_item_key is NOT NULL THEN
196 l_progress := 'withdraw_req: 01 '|| l_item_key;
197
198 -- insert into jiz_debug values (l_item_type,l_item_key,l_progress);
199
200 BEGIN
201 SELECT root_activity
202 INTO l_root_activity
203 FROM wf_items
204 WHERE item_type = l_item_type AND item_key = l_item_key;
205
206
207 SELECT NVL(activity_status_code, 'N')
208 INTO l_activity_status
209 FROM wf_item_activity_statuses_v
210 WHERE item_type = l_item_type AND item_key = l_item_key
211 AND ACTIVITY_NAME=l_root_activity;
212
213 l_progress := 'withdraw_req: 02 '|| l_activity_status;
214
215 -- insert into jiz_debug values (l_item_type,l_item_key,l_progress);
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 RETURN;
220 END;
221
222 IF (l_activity_status <> 'COMPLETE') THEN
223 l_progress := 'withdraw_req: 03 aborting ';
224
225 -- insert into jiz_debug values (l_item_type,l_item_key,l_progress);
226
227 WF_Engine.AbortProcess(l_item_type, l_item_key);
228 END IF;
229
230 END IF;
231
232
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 RAISE;
237 END;
238
239 END POR_WITHDRAW_REQ_SV;