1 PACKAGE BODY PO_REQUISITION_HEADERS_PKG as
2 /* $Header: POXRIH1B.pls 120.5 2006/04/27 15:26:16 bao noship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Requisition_Header_Id IN OUT NOCOPY NUMBER,
7 X_Preparer_Id NUMBER,
8 X_Last_Update_Date DATE,
9 X_Last_Updated_By NUMBER,
10 X_Segment1 IN OUT NOCOPY VARCHAR2,
11 X_Summary_Flag VARCHAR2,
12 X_Enabled_Flag VARCHAR2,
13 X_Segment2 VARCHAR2,
14 X_Segment3 VARCHAR2,
15 X_Segment4 VARCHAR2,
16 X_Segment5 VARCHAR2,
17 X_Start_Date_Active DATE,
18 X_End_Date_Active DATE,
19 X_Last_Update_Login NUMBER,
20 X_Creation_Date DATE,
21 X_Created_By NUMBER,
22 X_Description VARCHAR2,
23 X_Authorization_Status VARCHAR2,
24 X_Note_To_Authorizer VARCHAR2,
25 X_Type_Lookup_Code VARCHAR2,
26 X_Transferred_To_Oe_Flag VARCHAR2,
27 X_Attribute_Category VARCHAR2,
28 X_Attribute1 VARCHAR2,
29 X_Attribute2 VARCHAR2,
30 X_Attribute3 VARCHAR2,
31 X_Attribute4 VARCHAR2,
32 X_Attribute5 VARCHAR2,
33 X_On_Line_Flag VARCHAR2,
34 X_Preliminary_Research_Flag VARCHAR2,
35 X_Research_Complete_Flag VARCHAR2,
36 X_Preparer_Finished_Flag VARCHAR2,
37 X_Preparer_Finished_Date DATE,
38 X_Agent_Return_Flag VARCHAR2,
39 X_Agent_Return_Note VARCHAR2,
40 X_Cancel_Flag VARCHAR2,
41 X_Attribute6 VARCHAR2,
42 X_Attribute7 VARCHAR2,
43 X_Attribute8 VARCHAR2,
44 X_Attribute9 VARCHAR2,
45 X_Attribute10 VARCHAR2,
46 X_Attribute11 VARCHAR2,
47 X_Attribute12 VARCHAR2,
48 X_Attribute13 VARCHAR2,
49 X_Attribute14 VARCHAR2,
50 X_Attribute15 VARCHAR2,
51 X_Ussgl_Transaction_Code VARCHAR2,
52 X_Government_Context VARCHAR2,
53 X_Interface_Source_Code VARCHAR2,
54 X_Interface_Source_Line_Id NUMBER,
55 X_Closed_Code VARCHAR2,
56 X_Manual BOOLEAN,
57 p_org_id IN NUMBER DEFAULT NULL -- <R12 MOAC>
58 ) IS
59 CURSOR C IS SELECT rowid FROM PO_REQUISITION_HEADERS
60 WHERE requisition_header_id = X_Requisition_Header_Id;
61
62
63 CURSOR S IS SELECT po_requisition_headers_s.nextval FROM sys.dual;
64
65 /* Ben: bug#465696 Locking the po_unique_identifier_control table at this
66 point of the form commit cycle is causing the performance problem.
67 It may take 5 to 10 seconds to commit a PO with many lines, shipments
68 and distributions.
69 The solution is to insert a bogus value into the SEGMENT1 column
70 of po_requisition_headers ( the negative of po_requisition_header)
71 then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
72 trigger on the form, update the po_requisition_headers table with
73 the real SEGMENT1 value from the po_unique_identifier_control table.
74 The advantage of this approach is that the
75 po_unique_identifier_control will be locked for only a short period
76 of time.
77 THEREFORE, taking the S1 cursor out of the logic here.
78
79 CURSOR S1 IS SELECT to_char(current_max_unique_identifier + 1)
80 FROM po_unique_identifier_control
81 WHERE table_name = 'PO_REQUISITION_HEADERS'
82 FOR UPDATE OF current_max_unique_identifier;
83
84 */
85
86 x_progress VARCHAR2(3) := NULL;
87
88 BEGIN
89 if (X_Requisition_Header_Id is NULL) then
90 OPEN S;
91 FETCH S INTO X_Requisition_Header_Id;
92 CLOSE S;
93 end if;
94
95 /* Ben: bug#465696 Commenting this out. see explanation above
96 if ((X_segment1 is NULL) and not(X_manual)) then
97 OPEN S1;
98 FETCH S1 INTO X_Segment1;
99 UPDATE po_unique_identifier_control
100 SET current_max_unique_identifier
101 = current_max_unique_identifier + 1
102 WHERE CURRENT of S1;
103 CLOSE S1;
104 end if;
105 */
106 /* Ben:bug465696 Added the following IF statement.See explanation above */
107 IF ((X_segment1 is NULL) and not(X_manual)) then
108
109 X_segment1 := '-' || to_char(X_Requisition_Header_Id);
110
111 END IF;
112 x_progress := '020';
113
114 po_requisition_headers_pkg.check_unique (X_rowid, X_segment1);
115
116 x_progress := '030';
117
118 INSERT INTO PO_REQUISITION_HEADERS(
119 requisition_header_id,
120 preparer_id,
121 last_update_date,
122 last_updated_by,
123 segment1,
124 summary_flag,
125 enabled_flag,
126 segment2,
127 segment3,
128 segment4,
129 segment5,
130 start_date_active,
131 end_date_active,
132 last_update_login,
133 creation_date,
134 created_by,
135 description,
136 authorization_status,
137 note_to_authorizer,
138 type_lookup_code,
139 transferred_to_oe_flag,
140 attribute_category,
141 attribute1,
142 attribute2,
143 attribute3,
144 attribute4,
145 attribute5,
146 on_line_flag,
147 preliminary_research_flag,
148 research_complete_flag,
149 preparer_finished_flag,
150 preparer_finished_date,
151 agent_return_flag,
152 agent_return_note,
153 cancel_flag,
154 attribute6,
155 attribute7,
156 attribute8,
157 attribute9,
158 attribute10,
159 attribute11,
160 attribute12,
161 attribute13,
162 attribute14,
163 attribute15,
164 government_context,
165 interface_source_code,
166 interface_source_line_id,
167 closed_code,
168 Org_Id, -- <R12 MOAC>
169 tax_attribute_update_code --<eTax Integration R12>
170 ) VALUES (
171 X_Requisition_Header_Id,
172 X_Preparer_Id,
173 X_Last_Update_Date,
174 X_Last_Updated_By,
175 X_Segment1,
176 X_Summary_Flag,
177 X_Enabled_Flag,
178 X_Segment2,
179 X_Segment3,
180 X_Segment4,
181 X_Segment5,
182 X_Start_Date_Active,
183 X_End_Date_Active,
184 X_Last_Update_Login,
185 X_Creation_Date,
186 X_Created_By,
187 X_Description,
188 X_Authorization_Status,
189 X_Note_To_Authorizer,
190 X_Type_Lookup_Code,
191 X_Transferred_To_Oe_Flag,
192 X_Attribute_Category,
193 X_Attribute1,
194 X_Attribute2,
195 X_Attribute3,
196 X_Attribute4,
197 X_Attribute5,
198 X_On_Line_Flag,
199 X_Preliminary_Research_Flag,
200 X_Research_Complete_Flag,
201 X_Preparer_Finished_Flag,
202 X_Preparer_Finished_Date,
203 X_Agent_Return_Flag,
204 X_Agent_Return_Note,
205 X_Cancel_Flag,
206 X_Attribute6,
207 X_Attribute7,
208 X_Attribute8,
209 X_Attribute9,
210 X_Attribute10,
211 X_Attribute11,
212 X_Attribute12,
213 X_Attribute13,
214 X_Attribute14,
215 X_Attribute15,
216 X_Government_Context,
217 X_Interface_Source_Code,
218 X_Interface_Source_Line_Id,
219 X_Closed_Code,
220 p_org_id, -- <R12 MOAC>
221 'CREATE' --<eTax Integration R12>
222 );
223
224 /* Bug #465696 Setting the segment1 back to NULL if using AUTOMATIC
225 numbering. Otherwise, the bogus value of segment1 (see above explanation)
226 will flash on the screen in front of the user.
227 */
228 IF NOT (X_manual) then
229
230 X_segment1 := NULL;
231
232 END IF;
233
234 OPEN C;
235 FETCH C INTO X_Rowid;
236 if (C%NOTFOUND) then
237 CLOSE C;
238 Raise NO_DATA_FOUND;
239 end if;
240 CLOSE C;
241
242 EXCEPTION
243 WHEN OTHERS then
244 po_message_s.sql_error('INSERT_ROW',x_progress,sqlcode);
245 raise;
246
247 END Insert_Row;
248
249 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
250 BEGIN
251 DELETE FROM PO_REQUISITION_HEADERS
252 WHERE rowid = X_Rowid;
253
254 if (SQL%NOTFOUND) then
255 Raise NO_DATA_FOUND;
256 end if;
257 END Delete_Row;
258
259 /***************************************************************************/
260
261 PROCEDURE get_real_segment1(x_requisition_header_id NUMBER,
262 x_type_lookup_code VARCHAR2,
263 x_currency_code VARCHAR2,
264 x_segment1 IN OUT NOCOPY VARCHAR2) is
265
266 x_progress varchar2(3);
267
268 /* Ben: bug#465696 Locking the po_unique_identifier_control table at the
269 beginning of the form commit cycle is causing the performance problem.
270 It may take 5 to 10 seconds to commit a PO with many lines, shipments
271 and distributions.
272 The solution is to insert a bogus value into the SEGMENT1 column
273 of po_requisition_headers ( the negative of po_requisition_header)
274 during the ON-INSERT trigger on the PO_HEADERS,
275 then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
276 trigger on the form, update the po_requisition_headers table with
277 the real SEGMENT1 value from the po_unique_identifier_control table.
278 The advantage of this approach is that the
279 po_unique_identifier_control will be locked for only a short period
280 of time.
281
282 This procedure gets called from the POST_FORMS-COMMIT trigger
283 */
284
285
286 BEGIN
287
288 -- bug5176308 START
289 -- Call API to get the po number
290 x_segment1 :=
291 PO_CORE_SV1.default_po_unique_identifier
292 ( x_table_name => 'PO_REQUISITION_HEADERS'
293 );
294 -- bug5176308 END
295
296
297 UPDATE po_requisition_headers set segment1=x_segment1
298 where requisition_header_id=x_requisition_header_id;
299
300 /* bug# 465696 8/5/97. The previous fix to this performance problem introduced
301 a problem with the notifications (the bogus value used temporarily as the
302 document number was being inserted into the fnd_notifications table, since
303 the call below was made before we called the procedure to get the real
304 document number (segment1) .
305 Therefore, removed the call below from po_reqs_sv.insert_row and moved it to
306 here.
307 */
308 /*hvadlamu : commenting out since notifications will be handled by workflow */
309 /*po_notifications_sv1.send_po_notif (x_type_lookup_code,
310 x_requisition_header_id,
311 x_currency_code,
312 null,
313 null,
314 null,
315 null,
316 null); */
317 EXCEPTION
318 WHEN OTHERS then
319 po_message_s.sql_error('get_real_segment1',x_progress,sqlcode);
320 raise;
321
322 END get_real_segment1;
323
324
325 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
326 X_Segment1 VARCHAR2) IS
327
328 x_progress VARCHAR2(3) := NULL;
329 dummy NUMBER;
330 BEGIN
331
332 x_progress := '010';
333
334 SELECT 1 INTO dummy
335 FROM DUAL
336 WHERE NOT EXISTS
340 AND ((X_Rowid IS NULL) OR (ROWID <> X_ROWID)))
337 ( SELECT 1
338 FROM po_requisition_headers
339 WHERE Segment1 = X_Segment1
341 AND NOT EXISTS
342 ( SELECT 1
343 FROM po_history_requisitions phr
344 WHERE phr.segment1 = X_Segment1);
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 po_message_s.app_error('PO_ALL_ENTER_UNIQUE_VAL');
349 raise;
350 WHEN OTHERS THEN
351 po_message_s.sql_error('check_unique',x_progress,sqlcode);
352 raise;
353
354 END Check_Unique;
355
356
357
358
359 FUNCTION get_req_total
360 (p_header_id number) return number is
361 X_req_total number;
362
363 BEGIN
364 -- <SERVICES FPJ>
365 -- Added a decode statement to use amount for total calculation
366 -- when quantity is null for the new Services lines.
367 SELECT nvl(SUM(decode(quantity,
368 null,
369 amount,
370 (quantity * unit_price)
371 )
372 ), 0)
373 into X_req_total
374 FROM po_requisition_lines
375 WHERE requisition_header_id = p_header_id and
376 nvl(cancel_flag,'N') <> 'Y' and -- Bug 554452 Ignore cancelled lines
377 nvl(MODIFIED_BY_AGENT_FLAG, 'N') = 'N' and -- Bug 574676
378 nvl(CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED'; -- Bug 574676
379
380 RETURN (X_req_total);
381
382 EXCEPTION
383 WHEN OTHERS then
384 X_req_total := 0;
385 END get_req_total;
386 /* Start Bug#3406460 overloaded the function to calculate header total*/
387 /* by rounding the line totals to the precision */
388
389 FUNCTION get_req_total
390 (p_header_id number,
391 p_currency_code varchar2) return number is
392 X_req_total number;
393 l_precision number;
394 l_ext_precision number;
395 l_min_acct_unit number;
396
397 BEGIN
398
399 fnd_currency.get_info(p_currency_code,
400 l_precision,
401 l_ext_precision,
402 l_min_acct_unit);
403
404 -- <BUG 3553405 START> Need to sum the amount (rather than
405 -- quantity*unit_price)in the case of Services line types.
406 --
407 SELECT nvl ( sum ( round ( decode ( order_type_lookup_code
408 , 'FIXED PRICE' , amount
409 , 'RATE' , amount
410 , quantity*unit_price
411 )
412 , l_precision
413 )
414 )
415 , 0
416 )
417 -- <BUG 3553405 END>
418 into X_req_total
419 FROM po_requisition_lines
420 WHERE requisition_header_id = p_header_id and
421 nvl(cancel_flag,'N') <> 'Y' and -- Bug 554452 Ignore cancelled lines
422 nvl(MODIFIED_BY_AGENT_FLAG, 'N') = 'N' and -- Bug 574676
423 nvl(CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED'; -- Bug 574676
424
425 RETURN (X_req_total);
426
427 EXCEPTION
428 WHEN OTHERS then
429 x_req_total := 0;
430 END get_req_total;
431
432
433 /* End Bug3406460 */
434
435 END PO_REQUISITION_HEADERS_PKG;
[Home] [Help]