[Home] [Help]
PACKAGE BODY: APPS.PO_APPROVE_SV
Source
1 PACKAGE BODY po_approve_sv AS
2 /* $Header: POXAPAPB.pls 115.6 2004/06/24 23:27:26 zxzhang ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: test_get_document_types
7
8 ===========================================================================*/
9
10 PROCEDURE test_get_document_types (x_document_type_code IN VARCHAR2,
11 x_document_subtype IN VARCHAR2) IS
12 x_can_change_forward_from_flag VARCHAR2(30) := '';
13 x_can_change_forward_to_flag VARCHAR2(30) := '';
14 x_can_change_approval_path VARCHAR2(30) := '';
15 x_default_approval_path_id NUMBER := '';
16 x_can_preparer_approve_flag VARCHAR2(30) := '';
17 x_can_approver_modify_flag VARCHAR2(30) := '';
18
19 BEGIN
20
21 --dbms_output.put_line('before call');
22
23 po_approve_sv.get_document_types(x_document_type_code,
24 x_document_subtype,
25 x_can_change_forward_from_flag,
26 x_can_change_forward_to_flag,
27 x_can_change_approval_path,
28 x_default_approval_path_id,
29 x_can_preparer_approve_flag,
30 x_can_approver_modify_flag);
31
32 --dbms_output.put_line('after call');
33 --dbms_output.put_line('Can Change Forward From ='||x_can_change_forward_from_flag);
34 --dbms_output.put_line('Can Change Forward To ='||x_can_change_forward_to_flag);
35 --dbms_output.put_line('Can Change Approval Path ='||x_can_change_approval_path);
36 --dbms_output.put_line('Can Preparer Approve ='||x_can_preparer_approve_flag);
37
38 END test_get_document_types;
39
40
41 /*===========================================================================
42
43 PROCEDURE NAME: get_document_types
44
45 ===========================================================================*/
46
47 PROCEDURE get_document_types (
48 x_document_type_code IN VARCHAR2,
49 x_document_subtype IN VARCHAR2,
50 x_can_change_forward_from_flag IN OUT NOCOPY VARCHAR2,
51 x_can_change_forward_to_flag IN OUT NOCOPY VARCHAR2,
52 x_can_change_approval_path IN OUT NOCOPY VARCHAR2,
53 x_default_approval_path_id IN OUT NOCOPY NUMBER,
54 x_can_preparer_approve_flag IN OUT NOCOPY VARCHAR2,
55 x_can_approver_modify_flag IN OUT NOCOPY VARCHAR2)
56 IS
57 x_progress VARCHAR2(3);
58 BEGIN
59 x_progress := '010';
60
61 IF (x_document_type_code IS NOT NULL AND
62 x_document_subtype IS NOT NULL) THEN
63
64 x_progress := '020';
65
66 SELECT podt.can_change_forward_from_flag,
67 podt.can_change_forward_to_flag,
68 podt.can_change_approval_path_flag,
69 podt.can_preparer_approve_flag,
70 podt.default_approval_path_id,
71 podt.can_approver_modify_doc_flag
72 INTO x_can_change_forward_from_flag,
73 x_can_change_forward_to_flag,
74 x_can_change_approval_path,
75 x_can_preparer_approve_flag,
76 x_default_approval_path_id,
77 x_can_approver_modify_flag
78 FROM po_document_types podt
79 WHERE podt.document_type_code = x_document_type_code
80 AND podt.document_subtype = x_document_subtype;
81
82 --dbms_output.put_line('Can Change Forward From ='||x_can_change_forward_from_flag);
83 --dbms_output.put_line('Can Change Forward To ='||x_can_change_forward_to_flag);
84 --dbms_output.put_line('Can Change Approval Path ='||x_can_change_approval_path);
85 --dbms_output.put_line('Can Preparer Approve ='||x_can_preparer_approve_flag);
86 --dbms_output.put_line('Can Approver Modify Doc ='||x_can_approver_modify_flag);
87
88 END IF;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 --dbms_output.put_line('In Exception');
93 PO_MESSAGE_S.SQL_ERROR('PO_APPROVE_SV.GET_DOCUMENT_TYPES', x_progress, sqlcode);
94 RAISE;
95 END;
96
97 /*===========================================================================
98
99 PROCEDURE NAME: test_get_approval_path
100
101 ===========================================================================*/
102
103 PROCEDURE test_get_approval_path ( x_default_approval_path_id IN NUMBER,
104 x_forward_from_id IN NUMBER,
105 x_object_id IN NUMBER,
106 x_document_type_code IN VARCHAR2,
107 x_document_subtype IN VARCHAR2) IS
108 x_approval_path VARCHAR2(30);
109 x_approval_path_id NUMBER;
110 BEGIN
111
112 --dbms_output.put_line('before call');
113
114 po_approve_sv.get_approval_path (x_default_approval_path_id,
115 x_forward_from_id,
116 x_object_id,
117 x_document_type_code,
118 x_document_subtype,
119 x_approval_path,
120 x_approval_path_id) ;
121
122 --dbms_output.put_line('after call');
123 --dbms_output.put_line('Approval path ='||x_approval_path);
124 --dbms_output.put_line('Approval path ID ='||TO_CHAR(x_approval_path_id));
125
126 END;
127
128
129 /*===========================================================================
130
131 PROCEDURE NAME: get_approval_path()
132
133 ===========================================================================*/
134
135 PROCEDURE get_approval_path (
136 x_default_approval_path_id IN NUMBER,
137 x_forward_from_id IN NUMBER,
138 x_object_id IN NUMBER,
139 x_document_type_code IN VARCHAR2,
140 x_document_subtype IN VARCHAR2,
141 x_approval_path OUT NOCOPY VARCHAR2,
142 x_approval_path_id OUT NOCOPY NUMBER)
143 IS
144 x_progress VARCHAR2(3);
145 x_data_found_flag BOOLEAN;
146 BEGIN
147 IF (x_object_id IS NOT NULL
148 AND x_document_type_code IS NOT NULL
149 AND x_document_subtype IS NOT NULL) THEN
150
151 BEGIN
152
153 -- If this is not the first action on this document, use the
154 -- same approval path as the previous action on this document.
155
156 SELECT pps.name,
157 pah.approval_path_id
158 INTO x_approval_path,
159 x_approval_path_id
160 FROM per_position_structures pps,
161 po_action_history pah
162 WHERE pah.approval_path_id = pps.position_structure_id
163 AND pah.object_id = x_object_id
164 AND pah.object_type_code = x_document_type_code
165 AND pah.object_sub_type_code = x_document_subtype
166 AND pah.sequence_num =
167 (SELECT max(sequence_num)
168 FROM po_action_history PAH2
169 WHERE PAH2.object_id = x_object_id
170 AND PAH2.object_type_code = x_document_type_code
171 AND PAH2.object_sub_type_code = x_document_subtype
172 AND PAH2.action_code is not null);
173
174 x_data_found_flag := TRUE;
175
176 EXCEPTION
177 when NO_DATA_FOUND then
178 x_data_found_flag := FALSE;
179 END;
180
181 ELSE
182 x_data_found_flag := FALSE;
183 END IF;
184
185 -- If this is the first action on the document, use
186 -- the default approval path only if the forward_from
187 -- person belongs to the same path
188
189 IF (x_data_found_flag = FALSE) THEN
190
191 IF (x_default_approval_path_id IS NOT NULL
192 AND x_forward_from_id IS NOT NULL) THEN
193
194 BEGIN
195
196 SELECT DISTINCT pps.name,
197 pps.position_structure_id
198 INTO x_approval_path,
199 x_approval_path_id
200 FROM po_employee_hierarchies peh,
201 per_position_structures pps
202 WHERE peh.position_structure_id = pps.position_structure_id
203 AND pps.position_structure_id = x_default_approval_path_id
204 AND peh.employee_id = x_forward_from_id;
205
206 EXCEPTION
207 when NO_DATA_FOUND then
208 return;
209 END;
210
211 ELSE
212 return;
213 END IF;
214 END IF;
215
216 EXCEPTION
217 -- If this is the first action on the document and the
218 -- foward_from person does not belong to the default approval
219 -- path, then do not default in approval path.
220
221 WHEN NO_DATA_FOUND THEN
222 RETURN;
223 WHEN OTHERS THEN
224 PO_MESSAGE_S.SQL_ERROR('PO_APPROVE_SV.GET_APPROVAL_PATH', x_progress, sqlcode);
225 RAISE;
226 END;
227
228
229 /* RETROACTIVE FPI START */
230 PROCEDURE get_document_types (
231 p_document_type_code IN VARCHAR2,
232 p_document_subtype IN VARCHAR2,
233 x_can_change_forward_from_flag OUT NOCOPY VARCHAR2,
234 x_can_change_forward_to_flag OUT NOCOPY VARCHAR2,
235 x_can_change_approval_path OUT NOCOPY VARCHAR2,
236 x_default_approval_path_id OUT NOCOPY NUMBER,
237 x_can_preparer_approve_flag OUT NOCOPY VARCHAR2,
238 x_can_approver_modify_flag OUT NOCOPY VARCHAR2,
239 x_forwarding_mode_code OUT NOCOPY VARCHAR2,
240 x_wf_approval_itemtype OUT NOCOPY VARCHAR2,
241 x_wf_approval_process OUT NOCOPY VARCHAR2,
242 x_type_name OUT NOCOPY VARCHAR2) IS
243
244 l_progress Varchar2(3);
245
246 begin
247 l_progress := '000';
248
249 If ((p_document_type_code is NOT NULL) AND
250 (p_document_subtype is NOT NULL)) THEN
251 l_progress := '010';
252
253 SELECT podt.can_change_forward_from_flag,
254 podt.can_change_forward_to_flag,
255 podt.can_change_approval_path_flag,
256 podt.can_preparer_approve_flag,
257 podt.default_approval_path_id,
258 podt.can_approver_modify_doc_flag,
259 podt.forwarding_mode_code,
260 podt.wf_approval_itemtype,
261 podt.wf_approval_process,
262 podt.type_name
263 INTO x_can_change_forward_from_flag,
264 x_can_change_forward_to_flag,
265 x_can_change_approval_path,
266 x_can_preparer_approve_flag,
267 x_default_approval_path_id,
268 x_can_approver_modify_flag,
269 x_forwarding_mode_code,
270 x_wf_approval_itemtype,
271 x_wf_approval_process,
272 x_type_name
273 FROM po_document_types podt
274 WHERE podt.document_type_code = p_document_type_code
275 AND podt.document_subtype = p_document_subtype;
276
277 end if;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 PO_MESSAGE_S.SQL_ERROR('PO_APPROVE_SV.GET_DOCUMENT_TYPES',
282 l_progress, sqlcode);
283 RAISE;
284 END get_document_types;
285 /* RETROACTIVE FPI END */
286
287
288 -- <FPJ Redesign Approval Window START>
289 /**
290 * Public Procedure: get_change_summary
291 * Requires:
292 * IN PARAMETERS:
293 * p_document_type_code: The document type code
294 * p_document_header_id: The id of document header
295 *
296 * Modifies: None.
297 * Effects: This procedure gets change summary
298 *
299 * Returns:
300 * x_change_summary: Contains change summary
301 */
302 PROCEDURE get_change_summary(p_document_type_code IN VARCHAR2,
303 p_document_header_id IN NUMBER,
304 x_change_summary OUT NOCOPY VARCHAR2)
305 IS
306 cursor po_reasons(p_po_header_id NUMBER) IS
307 -- SQL What: Querying for response reasons for po change
308 -- SQL Why: Need to concatenate these reasons to default
309 -- field change summary
310 -- SQL Join: po_header_id
311 select response_reason
312 from po_change_requests
313 where document_header_id = p_po_header_id
314 -- Bug 3711787
315 and change_active_flag = 'Y'
316 -- Bug 3326904
317 and po_release_id IS NULL;
318 -- and request_status = 'BUYER_APP';
319
320 cursor release_reasons(p_po_release_id NUMBER) IS
321 -- SQL What: Querying for response reasons for release change
322 -- SQL Why: Need to concatenate these reasons to default
323 -- field change summary
324 -- SQL Join: po_release_id
325 select response_reason
326 from po_change_requests
327 where po_release_id = p_po_release_id
328 -- Bug 3711787
329 and change_active_flag = 'Y';
330 -- Bug 3326904
334 l_reasons dbms_sql.varchar2_table;
331 -- and request_status = 'BUYER_APP';
332
333 l_progress Varchar2(3);
335 l_additional_changes PO_CHANGE_REQUESTS.additional_changes%TYPE := NULL;
336 BEGIN
337
338 l_progress := '000';
339 x_change_summary := '';
340
341 if (p_document_type_code in ('PO', 'PA')) then
342 l_progress := '010';
343 open po_reasons (p_document_header_id);
344 fetch po_reasons bulk collect into l_reasons;
345 for i in 1..l_reasons.COUNT loop
346 x_change_summary := x_change_summary || l_reasons(i);
347 end loop;
348
349 elsif (p_document_type_code = 'RELEASE') then
350 l_progress := '050';
351 open release_reasons (p_document_header_id);
352 fetch release_reasons bulk collect into l_reasons;
353 for i in 1..l_reasons.COUNT loop
354 x_change_summary := x_change_summary || l_reasons(i);
355 end loop;
356
357 else
358 x_change_summary := '';
359 end if; /* (p_document_type_code in ('PO', 'PA')) */
360
361 l_progress := '100';
362 EXCEPTION
363 WHEN OTHERS THEN
364 -- PO_MESSAGE_S.SQL_ERROR('PO_APPROVE_SV.get_change_summary',
365 -- l_progress, sqlcode);
366 -- RAISE;
367 NULL;
368 END get_change_summary;
369 -- <FPJ Redesign Approval Window END>
370
371
372 END PO_APPROVE_SV;