[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_PKG_S2
Source
1 PACKAGE BODY PO_HEADERS_PKG_S2 as
2 /* $Header: POXP3PHB.pls 120.11 2011/04/18 06:04:57 lswamina ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: Update_Row()
7
8 ===========================================================================*/
9
10 PROCEDURE Update_Row(X_Rowid VARCHAR2,
11 X_Po_Header_Id NUMBER,
12 X_Agent_Id NUMBER,
13 X_Type_Lookup_Code VARCHAR2,
14 X_Last_Update_Date DATE,
15 X_Last_Updated_By NUMBER,
16 X_Segment1 VARCHAR2,
17 X_Summary_Flag VARCHAR2,
18 X_Enabled_Flag VARCHAR2,
19 X_Segment2 VARCHAR2,
20 X_Segment3 VARCHAR2,
21 X_Segment4 VARCHAR2,
22 X_Segment5 VARCHAR2,
23 X_Start_Date_Active DATE,
24 X_End_Date_Active DATE,
25 X_Last_Update_Login NUMBER,
26 X_Vendor_Id NUMBER,
27 X_Vendor_Site_Id NUMBER,
28 X_Vendor_Contact_Id NUMBER,
29 X_Pcard_Id NUMBER, -- Supplier Pcard FPH
30 X_Ship_To_Location_Id NUMBER,
31 X_Bill_To_Location_Id NUMBER,
32 X_Terms_Id NUMBER,
33 X_Ship_Via_Lookup_Code VARCHAR2,
34 X_Fob_Lookup_Code VARCHAR2,
35 X_Pay_On_Code VARCHAR2,
36 X_Freight_Terms_Lookup_Code VARCHAR2,
37 X_Status_Lookup_Code VARCHAR2,
38 X_Currency_Code VARCHAR2,
39 X_Rate_Type VARCHAR2,
40 X_Rate_Date DATE,
41 X_Rate NUMBER,
42 X_From_Header_Id NUMBER,
43 X_From_Type_Lookup_Code VARCHAR2,
44 X_Start_Date DATE,
45 X_End_Date DATE,
46 X_Blanket_Total_Amount NUMBER,
47 X_Authorization_Status VARCHAR2,
48 X_Revision_Num NUMBER,
49 -- Bug 902976, zxzhang, 10/04/99
50 -- Change REVISED_DATE from VarChar(25) to Date.
51 -- X_Revised_Date VARCHAR2,
52 X_Revised_Date DATE,
53 X_Approved_Flag VARCHAR2,
54 X_Approved_Date DATE,
55 X_Amount_Limit NUMBER,
56 X_Min_Release_Amount NUMBER,
57 X_Note_To_Authorizer VARCHAR2,
58 X_Note_To_Vendor VARCHAR2,
59 X_Note_To_Receiver VARCHAR2,
60 X_Print_Count NUMBER,
61 X_Printed_Date DATE,
62 X_Vendor_Order_Num VARCHAR2,
63 X_Confirming_Order_Flag VARCHAR2,
64 X_Comments VARCHAR2,
65 X_Reply_Date DATE,
66 X_Reply_Method_Lookup_Code VARCHAR2,
67 X_Rfq_Close_Date DATE,
68 X_Quote_Type_Lookup_Code VARCHAR2,
69 X_Quotation_Class_Code VARCHAR2,
70 X_Quote_Warning_Delay_Unit VARCHAR2,
71 X_Quote_Warning_Delay NUMBER,
72 X_Quote_Vendor_Quote_Number VARCHAR2,
73 X_Acceptance_Required_Flag VARCHAR2,
74 X_Acceptance_Due_Date DATE,
75 X_Closed_Date DATE,
76 X_User_Hold_Flag VARCHAR2,
77 X_Approval_Required_Flag VARCHAR2,
78 X_Cancel_Flag VARCHAR2,
79 X_Firm_Status_Lookup_Code VARCHAR2,
80 X_Firm_Date DATE,
81 X_Frozen_Flag VARCHAR2,
82 X_Attribute_Category VARCHAR2,
83 X_Attribute1 VARCHAR2,
84 X_Attribute2 VARCHAR2,
85 X_Attribute3 VARCHAR2,
86 X_Attribute4 VARCHAR2,
87 X_Attribute5 VARCHAR2,
88 X_Attribute6 VARCHAR2,
89 X_Attribute7 VARCHAR2,
90 X_Attribute8 VARCHAR2,
91 X_Attribute9 VARCHAR2,
92 X_Attribute10 VARCHAR2,
93 X_Attribute11 VARCHAR2,
94 X_Attribute12 VARCHAR2,
95 X_Attribute13 VARCHAR2,
96 X_Attribute14 VARCHAR2,
97 X_Attribute15 VARCHAR2,
98 X_Closed_Code VARCHAR2,
99 X_Ussgl_Transaction_Code VARCHAR2,
100 X_Government_Context VARCHAR2,
101 X_Supply_Agreement_flag VARCHAR2,
102 X_Price_Update_Tolerance NUMBER,
103 X_Global_Attribute_Category VARCHAR2,
104 X_Global_Attribute1 VARCHAR2,
105 X_Global_Attribute2 VARCHAR2,
106 X_Global_Attribute3 VARCHAR2,
107 X_Global_Attribute4 VARCHAR2,
108 X_Global_Attribute5 VARCHAR2,
109 X_Global_Attribute6 VARCHAR2,
110 X_Global_Attribute7 VARCHAR2,
111 X_Global_Attribute8 VARCHAR2,
112 X_Global_Attribute9 VARCHAR2,
113 X_Global_Attribute10 VARCHAR2,
114 X_Global_Attribute11 VARCHAR2,
115 X_Global_Attribute12 VARCHAR2,
116 X_Global_Attribute13 VARCHAR2,
117 X_Global_Attribute14 VARCHAR2,
118 X_Global_Attribute15 VARCHAR2,
119 X_Global_Attribute16 VARCHAR2,
120 X_Global_Attribute17 VARCHAR2,
121 X_Global_Attribute18 VARCHAR2,
122 X_Global_Attribute19 VARCHAR2,
123 X_Global_Attribute20 VARCHAR2,
124 p_shipping_control IN VARCHAR2, -- <INBOUND LOGISTICS FPJ>
125 p_encumbrance_required_flag IN VARCHAR2 DEFAULT NULL --<ENCUMBRANCE FPJ>
126 ,p_kterms_art_upd_date IN DATE -- <CONTERMS FPJ>
127 ,p_kterms_deliv_upd_date IN DATE -- <CONTERMS FPJ >
128 ,p_enable_all_sites IN varchar2 --<R12GCPA>
129 ) IS
130
131 l_tax_attribute_update_code PO_HEADERS_ALL.tax_attribute_update_code%type; --< eTax Integration R12>
132
133 BEGIN
134
135 --< eTax Integration R12 Start>
136 IF X_Type_Lookup_Code in ('STANDARD', 'PLANNED') AND
137 PO_TAX_INTERFACE_PVT.any_tax_attributes_updated(
138 p_doc_type =>'PO',
139 p_doc_level => 'HEADER',
140 p_doc_level_id =>X_Po_Header_Id,
141 p_trx_currency => X_Currency_Code,
142 p_rate_type => X_Rate_Type,
143 p_rate_date => X_Rate_Date,
144 p_rate =>X_Rate,
145 p_fob => X_Fob_Lookup_Code,
146 p_vendor_id =>X_Vendor_Id,
147 p_vendor_site_id=>X_Vendor_Site_Id,
148 p_bill_to_loc=>X_Bill_To_Location_Id --<ECO 5524555>
149 ) THEN
150 l_tax_attribute_update_code := 'UPDATE';
151 END IF;
152 --<eTax Integration R12 End>
153
154
155
156 UPDATE PO_HEADERS
157 SET
158 po_header_id = X_Po_Header_Id,
159 agent_id = X_Agent_Id,
160 type_lookup_code = X_Type_Lookup_Code,
161 last_update_date = X_Last_Update_Date,
162 last_updated_by = X_Last_Updated_By,
163 segment1 = X_Segment1,
164 summary_flag = X_Summary_Flag,
165 enabled_flag = X_Enabled_Flag,
166 segment2 = X_Segment2,
167 segment3 = X_Segment3,
168 segment4 = X_Segment4,
169 segment5 = X_Segment5,
170 start_date_active = X_Start_Date_Active,
171 end_date_active = X_End_Date_Active,
172 last_update_login = X_Last_Update_Login,
173 vendor_id = X_Vendor_Id,
174 vendor_site_id = X_Vendor_Site_Id,
175 vendor_contact_id = X_Vendor_Contact_Id,
176 pcard_id = X_Pcard_Id, -- Supplier Pcard FPH
177 ship_to_location_id = X_Ship_To_Location_Id,
178 bill_to_location_id = X_Bill_To_Location_Id,
179 terms_id = X_Terms_Id,
180 ship_via_lookup_code = X_Ship_Via_Lookup_Code,
181 fob_lookup_code = X_Fob_Lookup_Code,
182 pay_on_code = X_Pay_On_Code,
183 freight_terms_lookup_code = X_Freight_Terms_Lookup_Code,
184 status_lookup_code = X_Status_Lookup_Code,
185 currency_code = X_Currency_Code,
186 rate_type = X_Rate_Type,
187 rate_date = X_Rate_Date,
188 rate = X_Rate,
189 from_header_id = X_From_Header_Id,
190 from_type_lookup_code = X_From_Type_Lookup_Code,
191 start_date = X_Start_Date,
192 end_date = X_End_Date,
193 blanket_total_amount = X_Blanket_Total_Amount,
194 authorization_status = X_Authorization_Status,
195 revision_num = X_Revision_Num,
196 revised_date = X_Revised_Date,
197 approved_flag = X_Approved_Flag,
198 approved_date = X_Approved_Date,
199 amount_limit = X_Amount_Limit,
200 min_release_amount = X_Min_Release_Amount,
201 note_to_authorizer = X_Note_To_Authorizer,
202 note_to_vendor = X_Note_To_Vendor,
203 note_to_receiver = X_Note_To_Receiver,
204 print_count = X_Print_Count,
205 printed_date = X_Printed_Date,
206 vendor_order_num = X_Vendor_Order_Num,
207 confirming_order_flag = X_Confirming_Order_Flag,
208 comments = X_Comments,
209 reply_date = X_Reply_Date,
210 reply_method_lookup_code = X_Reply_Method_Lookup_Code,
211 rfq_close_date = X_Rfq_Close_Date,
212 quote_type_lookup_code = X_Quote_Type_Lookup_Code,
213 quotation_class_code = X_Quotation_Class_Code,
214 quote_warning_delay_unit = X_Quote_Warning_Delay_Unit,
215 quote_warning_delay = X_Quote_Warning_Delay,
216 quote_vendor_quote_number = X_Quote_Vendor_Quote_Number,
217 acceptance_required_flag = X_Acceptance_Required_Flag,
218 acceptance_due_date = X_Acceptance_Due_Date,
219 closed_date = X_Closed_Date,
220 user_hold_flag = X_User_Hold_Flag,
221 approval_required_flag = X_Approval_Required_Flag,
222 cancel_flag = X_Cancel_Flag,
223 firm_status_lookup_code = X_Firm_Status_Lookup_Code,
224 firm_date = X_Firm_Date,
225 frozen_flag = X_Frozen_Flag,
226 attribute_category = X_Attribute_Category,
227 attribute1 = X_Attribute1,
228 attribute2 = X_Attribute2,
229 attribute3 = X_Attribute3,
233 attribute7 = X_Attribute7,
230 attribute4 = X_Attribute4,
231 attribute5 = X_Attribute5,
232 attribute6 = X_Attribute6,
234 attribute8 = X_Attribute8,
235 attribute9 = X_Attribute9,
236 attribute10 = X_Attribute10,
237 attribute11 = X_Attribute11,
238 attribute12 = X_Attribute12,
239 attribute13 = X_Attribute13,
240 attribute14 = X_Attribute14,
241 attribute15 = X_Attribute15,
242 closed_code = X_Closed_Code,
243 government_context = X_Government_Context,
244 supply_agreement_flag = X_Supply_Agreement_Flag,
245 price_update_tolerance = X_Price_Update_Tolerance,
246 global_attribute_category = X_Global_Attribute_Category,
247 global_attribute1 = X_Global_Attribute1,
248 global_attribute2 = X_Global_Attribute2,
249 global_attribute3 = X_Global_Attribute3,
250 global_attribute4 = X_Global_Attribute4,
251 global_attribute5 = X_Global_Attribute5,
252 global_attribute6 = X_Global_Attribute6,
253 global_attribute7 = X_Global_Attribute7,
254 global_attribute8 = X_Global_Attribute8,
255 global_attribute9 = X_Global_Attribute9,
256 global_attribute10 = X_Global_Attribute10,
257 global_attribute11 = X_Global_Attribute11,
258 global_attribute12 = X_Global_Attribute12,
259 global_attribute13 = X_Global_Attribute13,
260 global_attribute14 = X_Global_Attribute14,
261 global_attribute15 = X_Global_Attribute15,
262 global_attribute16 = X_Global_Attribute16,
263 global_attribute17 = X_Global_Attribute17,
264 global_attribute18 = X_Global_Attribute18,
265 global_attribute19 = X_Global_Attribute19,
266 global_attribute20 = X_Global_Attribute20,
267 shipping_control = p_shipping_control, -- <INBOUND LOGISTICS FPJ>
268 encumbrance_required_flag = p_encumbrance_required_flag --<ENCUMBRANCE FPJ>
269 ,conterms_articles_upd_date = p_kterms_art_upd_date -- <CONTERMS FPJ>
270 ,conterms_deliv_upd_date = p_kterms_deliv_upd_date -- <CONTERMS FPJ>
271 ,tax_attribute_update_code = NVL(tax_attribute_update_code, --<eTax Integration R12>
272 l_tax_attribute_update_code)
273 ,enable_all_sites = p_enable_all_sites --<R12GCPA>
274 WHERE rowid = X_rowid;
275
276 if (SQL%NOTFOUND) then
277 Raise NO_DATA_FOUND;
278 end if;
279
280
281
282 END Update_Row;
283
284 /*===========================================================================
285
286 PROCEDURE NAME: Delete_Row()
287
288 ===========================================================================*/
289
290 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
291
292 BEGIN
293 DELETE FROM PO_HEADERS_ALL /*Bug6632095: using base table instead of view */
294 WHERE rowid = X_Rowid;
295
296 if (SQL%NOTFOUND) then
297 Raise NO_DATA_FOUND;
298 end if;
299
300 END Delete_Row;
301
302 /*===========================================================================
303
304 PROCEDURE NAME: Check_Unique()
305
306 ===========================================================================*/
307
308 /* Bug 12334421 Added the paramater X_bid_number */
309 FUNCTION Check_Unique(X_Segment1 In VARCHAR2, X_rowid IN VARCHAR2,
310 X_Type_lookup_code IN VARCHAR2, X_bid_number IN NUMBER default NULL )
311 return boolean is
312 X_Unique boolean;
313 X_non_unique_seg1 Varchar2(20);
314
315 X_progress varchar2(3) := '000';
316 X_dummy varchar2(40);
317 --<SOURCING TO PO START>
318 x_pon_install_status varchar2(1);
319 x_status varchar2(10);
320 --<SOURCING TO PO END>
321 BEGIN
322 /* 824106 - SVAIDYAN: Add the condn. X_rowid is null so that it doesn't do
323 a full table scan when rowid is null */
324
325 IF X_Type_lookup_code NOT IN ('RFQ', 'QUOTATION') THEN
326
327 X_progress := '010';
328
329 SELECT 'no duplicates'
330 into X_dummy
331 from sys.dual
332 where not exists
333 (SELECT 'po number is not unique'
334 FROM po_headers ph
335 WHERE ph.segment1 = X_segment1
336 AND ph.type_lookup_code IN
337 ('STANDARD','CONTRACT','BLANKET','PLANNED')
338 AND (X_rowid is null or ph.rowid <> X_rowid) );
339
340 X_Progress := '020';
341
342 SELECT 'no duplicates'
343 into X_dummy
344 from sys.dual
345 where not exists
346 (SELECT 'po number is not unique'
347 FROM po_history_pos ph
348 WHERE ph.segment1 = X_segment1
349 AND ph.type_lookup_code IN
350 ('STANDARD','CONTRACT','BLANKET','PLANNED'));
351
352 --<SOURCING TO PO START>
353 /* Start Bug 2421680 mbhargav
354 Calling the wrapper function get_sourcing_startup to get the
355 install status of PON. The wrapper calls the po_core_s function
356 (commented below) and also checks for a profile option.
357 */
358
359 --x_pon_install_status := po_core_s.get_product_install_status('PON');
360 po_setup_s1.get_sourcing_startup(x_pon_install_status);
361
362 /* End bug 2421680 */
363
364 if nvl(x_pon_install_status,'N') ='I' then
365 if X_Type_lookup_code in ('STANDARD','BLANKET') then
366 /*Bug12334421 changing the parameter list of PON API, as the signature is changed now*/
367 pon_auction_po_pkg.check_unique(po_moac_utils_pvt.get_current_org_id,X_segment1,X_bid_number,x_status); --<R12 MOAC>
368 if x_status = 'SUCCESS' then
369 X_Unique :=TRUE;
370 else
371 raise no_data_found;
372 end if;
373 end if;
374 end if;
375 --<SOURCING TO PO END>
376
377 X_Unique:= TRUE;
378
379 return(X_Unique);
380
381 --< Bug 3649042 Start> Merge RFQ and Quotation logic
382 ELSIF (X_Type_lookup_code IN ('RFQ','QUOTATION')) THEN
383
384 X_progress := '050';
385
386 SELECT 'no duplicates'
387 into X_dummy
388 from sys.dual
389 where not exists
390 (SELECT 'rfq/quote number is not unique'
391 FROM po_headers ph
392 WHERE ph.segment1 = X_segment1
393 AND ph.type_lookup_code = x_type_lookup_code
394 AND (X_rowid is null or ph.rowid <> X_rowid) );
395
396 X_Progress := '060';
397
398 SELECT 'no duplicates'
399 into X_dummy
400 from sys.dual
401 where not exists
402 (SELECT 'rfq/quote number is not unique'
403 FROM po_history_pos ph
404 WHERE ph.segment1 = X_segment1
405 AND ph.type_lookup_code = x_type_lookup_code);
406
407 X_Unique:= TRUE;
408
409 return(X_Unique);
410
411 END IF;
412 --< Bug 3649042 End >
413
414 EXCEPTION
415
416 WHEN NO_DATA_FOUND then
417 -- po_message_s.app_error('PO_ALL_ENTER_UNIQUE_VAL');
418 fnd_message.set_name('PO', 'PO_ALL_ENTER_UNIQUE_VAL');
419 X_Unique:= FALSE;
420 raise;
421 return(X_Unique);
422
423 END Check_Unique;
424
425 /*===========================================================================
426
427 PROCEDURE NAME: po_total()
428
429 ===========================================================================*/
430
431 FUNCTION po_total(X_po_header_id IN NUMBER)
432 return number is
433 X_po_total number := 0;
434
435 X_progress varchar2(3) := '000';
436 X_dummy varchar2(40);
437 BEGIN
438 X_progress := '010';
439 SELECT sum(nvl(pol.quantity,0) * nvl(pol.unit_price,0))
440 INTO X_po_total
441 FROM po_lines pol
442 WHERE pol.po_header_id = X_po_header_id;
443
444 return(X_po_total);
445 EXCEPTION
446 WHEN NO_DATA_FOUND then
447 return(X_po_total);
448
449 WHEN OTHERS then
450 X_po_total := 0;
451 -- po_message_s.sql_error('po_total',X_progress);
452
453 -- Commented out due to BUG 251954 244014
454
455 return(X_po_total);
456 END po_total;
457
458 END PO_HEADERS_PKG_S2;