[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_PKG_S0
Source
1 PACKAGE BODY PO_HEADERS_PKG_S0 as
2 /* $Header: POXP1PHB.pls 120.9 2007/12/18 14:31:34 ggandhi ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: Insert_row()
7
8 ===========================================================================*/
9
10
11
12 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
13 X_Po_Header_Id IN OUT NOCOPY NUMBER,
14 X_Agent_Id NUMBER,
15 X_Type_Lookup_Code VARCHAR2,
16 X_Last_Update_Date DATE,
17 X_Last_Updated_By NUMBER,
18 X_Segment1 IN OUT NOCOPY VARCHAR2,
19 X_Summary_Flag VARCHAR2,
20 X_Enabled_Flag VARCHAR2,
21 X_Segment2 VARCHAR2,
22 X_Segment3 VARCHAR2,
23 X_Segment4 VARCHAR2,
24 X_Segment5 VARCHAR2,
25 X_Start_Date_Active DATE,
26 X_End_Date_Active DATE,
27 X_Last_Update_Login NUMBER,
28 X_Creation_Date DATE,
29 X_Created_By NUMBER,
30 X_Vendor_Id NUMBER,
31 X_Vendor_Site_Id NUMBER,
32 X_Vendor_Contact_Id NUMBER,
33 X_Ship_To_Location_Id NUMBER,
34 X_Bill_To_Location_Id NUMBER,
35 X_Terms_Id NUMBER,
36 X_Ship_Via_Lookup_Code VARCHAR2,
37 X_Fob_Lookup_Code VARCHAR2,
38 X_Pay_On_Code VARCHAR2,
39 X_Freight_Terms_Lookup_Code VARCHAR2,
40 X_Status_Lookup_Code VARCHAR2,
41 X_Currency_Code VARCHAR2,
42 X_Rate_Type VARCHAR2,
43 X_Rate_Date DATE,
44 X_Rate NUMBER,
45 X_From_Header_Id NUMBER,
46 X_From_Type_Lookup_Code VARCHAR2,
47 X_Start_Date DATE,
48 X_End_Date DATE,
49 X_Blanket_Total_Amount NUMBER,
50 X_Authorization_Status VARCHAR2,
51 X_Revision_Num NUMBER,
52 -- Bug 902976, zxzhang, 10/04/99
53 -- Change REVISED_DATE from VarChar(25) to Date.
54 -- X_Revised_Date VARCHAR2,
55 X_Revised_Date DATE,
56 X_Approved_Flag VARCHAR2,
57 X_Approved_Date DATE,
58 X_Amount_Limit NUMBER,
59 X_Min_Release_Amount NUMBER,
60 X_Note_To_Authorizer VARCHAR2,
61 X_Note_To_Vendor VARCHAR2,
62 X_Note_To_Receiver VARCHAR2,
63 X_Print_Count NUMBER,
64 X_Printed_Date DATE,
65 X_Vendor_Order_Num VARCHAR2,
66 X_Confirming_Order_Flag VARCHAR2,
67 X_Comments VARCHAR2,
68 X_Reply_Date DATE,
69 X_Reply_Method_Lookup_Code VARCHAR2,
70 X_Rfq_Close_Date DATE,
71 X_Quote_Type_Lookup_Code VARCHAR2,
72 X_Quotation_Class_Code VARCHAR2,
73 X_Quote_Warning_Delay_Unit VARCHAR2,
74 X_Quote_Warning_Delay NUMBER,
75 X_Quote_Vendor_Quote_Number VARCHAR2,
76 X_Acceptance_Required_Flag VARCHAR2,
77 X_Acceptance_Due_Date DATE,
78 X_Closed_Date DATE,
79 X_User_Hold_Flag VARCHAR2,
80 X_Approval_Required_Flag VARCHAR2,
81 X_Cancel_Flag VARCHAR2,
82 X_Firm_Status_Lookup_Code VARCHAR2,
83 X_Firm_Date DATE,
84 X_Frozen_Flag VARCHAR2,
85 X_Global_Agreement_Flag VARCHAR2,
86 X_Attribute_Category VARCHAR2,
87 X_Attribute1 VARCHAR2,
88 X_Attribute2 VARCHAR2,
89 X_Attribute3 VARCHAR2,
90 X_Attribute4 VARCHAR2,
91 X_Attribute5 VARCHAR2,
92 X_Attribute6 VARCHAR2,
93 X_Attribute7 VARCHAR2,
94 X_Attribute8 VARCHAR2,
95 X_Attribute9 VARCHAR2,
96 X_Attribute10 VARCHAR2,
97 X_Attribute11 VARCHAR2,
98 X_Attribute12 VARCHAR2,
99 X_Attribute13 VARCHAR2,
100 X_Attribute14 VARCHAR2,
101 X_Attribute15 VARCHAR2,
102 X_Closed_Code VARCHAR2,
103 X_Ussgl_Transaction_Code VARCHAR2,
104 X_Government_Context VARCHAR2,
105 X_Supply_Agreement_flag VARCHAR2,
106 X_Manual BOOLEAN,
107 X_Price_Update_Tolerance NUMBER,
108 X_Global_Attribute_Category VARCHAR2,
109 X_Global_Attribute1 VARCHAR2,
110 X_Global_Attribute2 VARCHAR2,
111 X_Global_Attribute3 VARCHAR2,
112 X_Global_Attribute4 VARCHAR2,
113 X_Global_Attribute5 VARCHAR2,
114 X_Global_Attribute6 VARCHAR2,
115 X_Global_Attribute7 VARCHAR2,
116 X_Global_Attribute8 VARCHAR2,
117 X_Global_Attribute9 VARCHAR2,
118 X_Global_Attribute10 VARCHAR2,
119 X_Global_Attribute11 VARCHAR2,
120 X_Global_Attribute12 VARCHAR2,
121 X_Global_Attribute13 VARCHAR2,
122 X_Global_Attribute14 VARCHAR2,
123 X_Global_Attribute15 VARCHAR2,
124 X_Global_Attribute16 VARCHAR2,
125 X_Global_Attribute17 VARCHAR2,
126 X_Global_Attribute18 VARCHAR2,
127 X_Global_Attribute19 VARCHAR2,
128 X_Global_Attribute20 VARCHAR2,
129 p_shipping_control IN VARCHAR2, -- <INBOUND LOGISTICS FPJ>
130 p_encumbrance_required_flag IN VARCHAR2 DEFAULT NULL, --<ENCUMBRANCE FPJ>
131 p_org_id IN NUMBER DEFAULT NULL , -- <R12 MOAC>
132 p_enable_all_sites IN Varchar2 --<R12GCPA>
133 ) IS
134 CURSOR C IS SELECT rowid FROM PO_HEADERS
135 WHERE po_header_id = X_Po_Header_Id;
136
137 CURSOR C2 IS SELECT po_headers_s.nextval FROM sys.dual;
138
139 /* Ben: bug#465696 Locking the po_unique_identifier_control table at this
140 point of the form commit cycle is causing the performance problem.
141 It may take 5 to 10 seconds to commit a PO with many lines, shipments
142 and distributions.
143 The solution is to insert a bogus value into the SEGMENT1 column
144 of po_requisition_headers ( the negative of po_requisition_header)
145 then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
146 trigger on the form, update the po_requisition_headers table with
147 the real SEGMENT1 value from the po_unique_identifier_control table.
148 The advantage of this approach is that the
149 po_unique_identifier_control will be locked for only a short period
150 of time.
151 THEREFORE, taking the C3 cursor out of the logic here.
152
153 CURSOR C3 IS SELECT to_char(current_max_unique_identifier + 1)
154 FROM po_unique_identifier_control
155 WHERE table_name =
156 decode(x_type_lookup_code,
157 'RFQ', 'PO_HEADERS_RFQ',
158 'QUOTATION','PO_HEADERS_QUOTE',
159 'PO_HEADERS')
160 FOR UPDATE OF current_max_unique_identifier;
161 */
162
163 x_progress VARCHAR2(3) := NULL;
164
165 l_document_creation_method po_headers_all.document_creation_method%type := NULL ; --<DBI FPJ>
166
167 l_style_id PO_DOC_STYLE_HEADERS.style_id%type; --<R12 STYLES PHASE II>
168
169 BEGIN
170 x_progress := '005';
171
172 if (X_Po_Header_Id is NULL) then
173 -- dbms_output.put_line('insert row c2');
174 OPEN C2;
175 FETCH C2 INTO X_Po_Header_Id;
176 CLOSE C2;
177 end if;
178
179 x_progress := '010';
180
181 /* Ben: bug#465696 Commenting this out. see explanation above
182 if (X_segment1 is NULL) and not (X_manual) then
183 -- dbms_output.put_line('insert row c3');
184 OPEN C3;
185 FETCH C3 into X_segment1;
186 UPDATE po_unique_identifier_control
187 SET current_max_unique_identifier =
188 current_max_unique_identifier + 1
189 WHERE CURRENT of C3;
190 CLOSE C3;
191 end if;
192 */
193 /* Ben:bug465696 Added the following IF statement.See explanation above */
194 IF ((X_segment1 is NULL) and not(X_manual)) then
195
196 X_segment1 := '-' || to_char(X_Po_Header_Id);
197
198 END IF;
199
200 x_progress := '015';
201
202 -- DBI FPJ for the document types Standard,Blanket,Contract and Planned PO will have the document creation method cloumn as 'ENTER_PO'
203 -- Bug 3648268. Document Creation Method values was hardcoded earlier. Now
204 -- using lookup codes
205 IF X_Type_Lookup_Code in ('STANDARD','CONTRACT','BLANKET','PLANNED') THEN
206 l_document_creation_method:='ENTER_PO';
207 END IF;
208
209 l_style_id := PO_DOC_STYLE_GRP.GET_STANDARD_DOC_STYLE; --<R12 STYLES PHASE II >
210 -- dbms_output.put_line('insert sql');
211 INSERT INTO PO_HEADERS (
212 po_header_id,
213 agent_id,
214 type_lookup_code,
215 last_update_date,
216 last_updated_by,
217 segment1,
218 summary_flag,
219 enabled_flag,
220 segment2,
221 segment3,
222 segment4,
223 segment5,
224 start_date_active,
225 end_date_active,
226 last_update_login,
227 creation_date,
228 created_by,
229 vendor_id,
230 vendor_site_id,
231 vendor_contact_id,
232 ship_to_location_id,
233 bill_to_location_id,
234 terms_id,
235 ship_via_lookup_code,
236 fob_lookup_code,
237 pay_on_code,
238 freight_terms_lookup_code,
239 status_lookup_code,
240 currency_code,
241 rate_type,
242 rate_date,
243 rate,
244 from_header_id,
245 from_type_lookup_code,
246 start_date,
247 end_date,
248 blanket_total_amount,
249 authorization_status,
250 revision_num,
251 revised_date,
252 approved_flag,
253 approved_date,
254 amount_limit,
255 min_release_amount,
256 note_to_authorizer,
257 note_to_vendor,
258 note_to_receiver,
259 print_count,
260 printed_date,
261 vendor_order_num,
262 confirming_order_flag,
263 comments,
264 reply_date,
265 reply_method_lookup_code,
266 rfq_close_date,
267 quote_type_lookup_code,
268 quotation_class_code,
269 quote_warning_delay_unit,
270 quote_warning_delay,
271 quote_vendor_quote_number,
272 acceptance_required_flag,
273 acceptance_due_date,
274 closed_date,
275 user_hold_flag,
276 approval_required_flag,
277 cancel_flag,
278 firm_status_lookup_code,
279 firm_date,
280 frozen_flag,
281 global_agreement_flag,
282 attribute_category,
283 attribute1,
284 attribute2,
285 attribute3,
286 attribute4,
287 attribute5,
288 attribute6,
289 attribute7,
290 attribute8,
291 attribute9,
292 attribute10,
293 attribute11,
294 attribute12,
295 attribute13,
296 attribute14,
297 attribute15,
298 closed_code,
299 government_context,
300 supply_agreement_flag,
301 price_update_tolerance,
302 global_attribute_category,
303 global_attribute1,
304 global_attribute2,
305 global_attribute3,
306 global_attribute4,
307 global_attribute5,
308 global_attribute6,
309 global_attribute7,
310 global_attribute8,
311 global_attribute9,
312 global_attribute10,
313 global_attribute11,
314 global_attribute12,
315 global_attribute13,
316 global_attribute14,
317 global_attribute15,
318 global_attribute16,
319 global_attribute17,
320 global_attribute18,
321 global_attribute19,
322 global_attribute20,
323 shipping_control, -- <INBOUND LOGISTICS FPJ>
324 encumbrance_required_flag, --<ENCUMBRANCE FPJ>
325 document_creation_method, -- <DBI FPJ>
326 Org_Id -- <R12 MOAC>
327 ,style_id --<R12 STYLES PHASE II>
328 ,created_language --<Unified Catalog R12>
329 ,tax_attribute_update_code --<eTax Integration R12>
330 ,enable_all_sites --<R12GCPA>
331 )
332 VALUES (
333 X_Po_Header_Id,
334 X_Agent_Id,
335 X_Type_Lookup_Code,
336 X_Last_Update_Date,
337 X_Last_Updated_By,
338 X_Segment1,
339 X_Summary_Flag,
340 X_Enabled_Flag,
341 X_Segment2,
342 X_Segment3,
343 X_Segment4,
344 X_Segment5,
345 X_Start_Date_Active,
346 X_End_Date_Active,
347 X_Last_Update_Login,
348 X_Creation_Date,
349 X_Created_By,
350 X_Vendor_Id,
351 X_Vendor_Site_Id,
352 X_Vendor_Contact_Id,
353 X_Ship_To_Location_Id,
354 X_Bill_To_Location_Id,
355 X_Terms_Id,
356 X_Ship_Via_Lookup_Code,
357 X_Fob_Lookup_Code,
358 X_Pay_On_Code,
359 X_Freight_Terms_Lookup_Code,
360 X_Status_Lookup_Code,
361 X_Currency_Code,
362 X_Rate_Type,
363 X_Rate_Date,
364 X_Rate,
365 X_From_Header_Id,
366 X_From_Type_Lookup_Code,
367 X_Start_Date,
368 X_End_Date,
369 X_Blanket_Total_Amount,
370 X_Authorization_Status,
371 X_Revision_Num,
372 X_Revised_Date,
373 X_Approved_Flag,
374 X_Approved_Date,
375 X_Amount_Limit,
376 X_Min_Release_Amount,
377 X_Note_To_Authorizer,
378 X_Note_To_Vendor,
379 X_Note_To_Receiver,
380 X_Print_Count,
381 X_Printed_Date,
382 X_Vendor_Order_Num,
383 X_Confirming_Order_Flag,
384 X_Comments,
385 X_Reply_Date,
386 X_Reply_Method_Lookup_Code,
387 X_Rfq_Close_Date,
388 X_Quote_Type_Lookup_Code,
389 X_Quotation_Class_Code,
390 X_Quote_Warning_Delay_Unit,
391 X_Quote_Warning_Delay,
392 X_Quote_Vendor_Quote_Number,
393 X_Acceptance_Required_Flag,
394 X_Acceptance_Due_Date,
395 X_Closed_Date,
396 X_User_Hold_Flag,
397 X_Approval_Required_Flag,
398 X_Cancel_Flag,
399 X_Firm_Status_Lookup_Code,
400 X_Firm_Date,
401 X_Frozen_Flag,
402 decode(X_Global_Agreement_Flag,'Y','Y',null) , -- FPI GA
403 X_Attribute_Category,
404 X_Attribute1,
405 X_Attribute2,
406 X_Attribute3,
407 X_Attribute4,
408 X_Attribute5,
409 X_Attribute6,
410 X_Attribute7,
411 X_Attribute8,
412 X_Attribute9,
413 X_Attribute10,
414 X_Attribute11,
415 X_Attribute12,
416 X_Attribute13,
417 X_Attribute14,
418 X_Attribute15,
419 X_Closed_Code,
420 X_Government_Context,
421 X_Supply_Agreement_Flag,
422 X_Price_Update_Tolerance,
423 X_Global_Attribute_Category,
424 X_Global_Attribute1,
425 X_Global_Attribute2,
426 X_Global_Attribute3,
427 X_Global_Attribute4,
428 X_Global_Attribute5,
429 X_Global_Attribute6,
430 X_Global_Attribute7,
431 X_Global_Attribute8,
432 X_Global_Attribute9,
433 X_Global_Attribute10,
434 X_Global_Attribute11,
435 X_Global_Attribute12,
436 X_Global_Attribute13,
437 X_Global_Attribute14,
438 X_Global_Attribute15,
439 X_Global_Attribute16,
440 X_Global_Attribute17,
441 X_Global_Attribute18,
442 X_Global_Attribute19,
443 X_Global_Attribute20,
444 p_shipping_control, -- <INBOUND LOGISTICS FPJ>
445 p_encumbrance_required_flag, -- <ENCUMBRANCE FPJ>
446 l_document_creation_method, --<DBI FPJ>
447 p_org_id -- <R12 MOAC>
448 ,l_style_id --<R12 STYLES PHASE II>
449 ,userenv('LANG') -- created_language <Unified Catalog R12>
450 , decode(X_Type_Lookup_Code, 'STANDARD', 'CREATE',
451 'PLANNED', 'CREATE', null) --<eTax Integration R12>
452 ,p_enable_all_sites --<R12 GCPA>
453 );
454
455 -- dbms_output.put_line('insert sql');
456
457 /* Bug #465696 Setting the segment1 back to NULL if using AUTOMATIC
458 numbering. Otherwise, the bogus value of segment1 (see above explanation)
459 will flash on the screen in front of the user.
460 */
461 IF NOT (X_manual) then
462
463 X_segment1 := NULL;
464
465 END IF;
466
467 OPEN C;
468 FETCH C INTO X_Rowid;
469 if (C%NOTFOUND) then
470 CLOSE C;
471 Raise NO_DATA_FOUND;
472 end if;
473 CLOSE C;
474
475
476 EXCEPTION
477 WHEN OTHERS then
478 po_message_s.sql_error('INSERT_ROW',x_progress,sqlcode);
479 raise;
480
481 END Insert_Row;
482
483 /***************************************************************************/
484
485 PROCEDURE get_real_segment1(x_po_header_id NUMBER,
486 x_type_lookup_code VARCHAR2,
487 x_date1 DATE,
488 x_date2 DATE,
489 x_quote_warning_delay NUMBER,
490 x_segment1 IN OUT NOCOPY VARCHAR2) is
491
492
493 x_progress varchar2(3);
494
495 /* Ben: bug#465696 Locking the po_unique_identifier_control table at the
496 beginning of the form commit cycle is causing the performance problem.
497 It may take 5 to 10 seconds to commit a PO with many lines, shipments
498 and distributions.
499 The solution is to insert a bogus value into the SEGMENT1 column
500 of po_requisition_headers ( the negative of po_header_id)
501 during the ON-INSERT trigger on the PO_HEADERS,
502 then at the end of the commit cycle, i.e. the POST_FORMS-COMMIT
503 trigger on the form, update the po_headers table with
504 the real SEGMENT1 value from the po_unique_identifier_control table.
505 The advantage of this approach is that the
506 po_unique_identifier_control will be locked for only a short period
507 of time.
508
509 This procedure gets called from the POST_FORMS-COMMIT trigger
510 */
511
512 X_reply_date DATE;
513 X_rfq_close_date DATE;
514 X_end_date DATE;
515
516 -- bug5176308
517 l_unique_id_tbl_name PO_UNIQUE_IDENTIFIER_CONT_ALL.table_name%TYPE;
518 BEGIN
519
520
521 -- bug5176308 START
522 -- Call API to get the po number
523
524 IF (x_type_lookup_code = 'RFQ') THEN
525 l_unique_id_tbl_name := 'PO_HEADERS_RFQ';
526 ELSIF (x_type_lookup_code = 'QUOTATION') THEN
527 l_unique_id_tbl_name := 'PO_HEADERS_QUOTE';
528 ELSE
529 l_unique_id_tbl_name := 'PO_HEADERS';
530 END IF;
531
532 x_segment1 :=
533 PO_CORE_SV1.default_po_unique_identifier
534 ( x_table_name => l_unique_id_tbl_name
535 );
536
537 -- bug5176308 END
538
539 UPDATE po_headers set segment1=x_segment1
540 where po_header_id=x_po_header_id;
541
542 /* bug# 465696 8/5/97. The previous fix to this performance problem introduced
543 a problem with the notifications (the bogus value used temporarily as the
544 document number was being inserted into the fnd_notifications table, since
545 the call below was made before we called the procedure to get the real
546 document number (segment1) .
547 Therefore, removed the call below from po_headers_sv1.insert_row and moved
548 it to here.
549 */
550
551 if (x_type_lookup_code not in ('RFQ', 'QUOTATION')) then
552
553 /*po_notifications_sv1.send_po_notif (x_type_lookup_code,
554 x_po_header_id,
555 null,
556 null,
557 null,
558 null,
559 null,
560 null); */
561 null;
562 elsif (x_type_lookup_code = 'RFQ') then
563
564 X_reply_date := x_date1;
565 X_rfq_close_date := x_date2;
566 /*po_notifications_sv1.send_po_notif (x_type_lookup_code,
567 x_po_header_id,
568 null,
569 X_reply_date,
570 X_rfq_close_date,
571 null,
572 null,
573 null); */
574 null;
575
576 elsif (x_type_lookup_code = 'QUOTATION') then
577
578 X_end_date := x_date1;
579 /* po_notifications_sv1.send_po_notif (x_type_lookup_code,
580 x_po_header_id,
581 null,
582 (X_end_date - X_quote_warning_delay
583 ),
584 X_end_date,
585 null,
586 null,
587 null); */
588 null;
589
590 end if;
591
592 EXCEPTION
593 WHEN OTHERS then
594 po_message_s.sql_error('get_real_segment1',x_progress,sqlcode);
595 raise;
596
597 END get_real_segment1;
598
599
600 END PO_HEADERS_PKG_S0;