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