[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_SV5
Source
1 PACKAGE BODY PO_HEADERS_SV5 as
2 /* $Header: POXPOH5B.pls 120.3 2007/12/18 11:44:47 ggandhi ship $*/
3
4
5 /*===========================================================================
6
7 PROCEDURE NAME: update_header()
8
9 ===========================================================================*/
10
11 PROCEDURE update_header(X_Rowid VARCHAR2,
12 X_Po_Header_Id NUMBER,
13 X_Agent_Id NUMBER,
14 X_Type_Lookup_Code VARCHAR2,
15 X_Last_Update_Date DATE,
16 X_Last_Updated_By NUMBER,
17 X_Segment1 VARCHAR2,
18 X_Summary_Flag VARCHAR2,
19 X_Enabled_Flag VARCHAR2,
20 X_Segment2 VARCHAR2,
21 X_Segment3 VARCHAR2,
22 X_Segment4 VARCHAR2,
23 X_Segment5 VARCHAR2,
24 X_Start_Date_Active DATE,
25 X_End_Date_Active DATE,
26 X_Last_Update_Login NUMBER,
27 X_Vendor_Id NUMBER,
28 X_Vendor_Site_Id NUMBER,
29 X_Vendor_Contact_Id NUMBER,
30 X_PCard_Id NUMBER, -- Supplier Pcard FPH
31 X_Ship_To_Location_Id NUMBER,
32 X_Bill_To_Location_Id NUMBER,
33 X_Terms_Id NUMBER,
34 X_Ship_Via_Lookup_Code VARCHAR2,
35 X_Fob_Lookup_Code VARCHAR2,
36 X_Pay_On_Code VARCHAR2,
37 X_Freight_Terms_Lookup_Code VARCHAR2,
38 X_Status_Lookup_Code VARCHAR2,
39 X_Currency_Code VARCHAR2,
40 X_Rate_Type VARCHAR2,
41 X_Rate_Date DATE,
42 X_Rate NUMBER,
43 X_From_Header_Id NUMBER,
44 X_From_Type_Lookup_Code VARCHAR2,
45 X_Start_Date DATE,
46 X_End_Date DATE,
47 X_Blanket_Total_Amount NUMBER,
48 X_Authorization_Status IN OUT NOCOPY VARCHAR2,
49 X_Revision_Num NUMBER,
50 -- Bug 902976, zxzhang, 10/04/99
51 -- Change REVISED_DATE from VarChar(25) to Date.
52 -- X_Revised_Date VARCHAR2,
53 X_Revised_Date DATE,
54 X_Approved_Flag IN OUT NOCOPY VARCHAR2,
55 X_Approved_Date DATE,
56 X_Amount_Limit NUMBER,
57 X_Min_Release_Amount NUMBER,
58 X_Note_To_Authorizer VARCHAR2,
59 X_Note_To_Vendor VARCHAR2,
60 X_Note_To_Receiver VARCHAR2,
61 X_Print_Count NUMBER,
62 X_Printed_Date DATE,
63 X_Vendor_Order_Num VARCHAR2,
64 X_Confirming_Order_Flag VARCHAR2,
65 X_Comments VARCHAR2,
66 X_Reply_Date DATE,
67 X_Reply_Method_Lookup_Code VARCHAR2,
68 X_Rfq_Close_Date DATE,
69 X_Quote_Type_Lookup_Code VARCHAR2,
70 X_Quotation_Class_Code VARCHAR2,
71 X_Quote_Warning_Delay_Unit VARCHAR2,
72 X_Quote_Warning_Delay NUMBER,
73 X_Quote_Vendor_Quote_Number VARCHAR2,
74 X_Acceptance_Required_Flag VARCHAR2,
75 X_Acceptance_Due_Date DATE,
76 X_Closed_Date DATE,
77 X_User_Hold_Flag VARCHAR2,
78 X_Approval_Required_Flag VARCHAR2,
79 X_Cancel_Flag VARCHAR2,
80 X_Firm_Status_Lookup_Code VARCHAR2,
81 X_Firm_Date DATE,
82 X_Frozen_Flag VARCHAR2,
83 X_Attribute_Category VARCHAR2,
84 X_Attribute1 VARCHAR2,
85 X_Attribute2 VARCHAR2,
86 X_Attribute3 VARCHAR2,
87 X_Attribute4 VARCHAR2,
88 X_Attribute5 VARCHAR2,
89 X_Attribute6 VARCHAR2,
90 X_Attribute7 VARCHAR2,
91 X_Attribute8 VARCHAR2,
92 X_Attribute9 VARCHAR2,
93 X_Attribute10 VARCHAR2,
94 X_Attribute11 VARCHAR2,
95 X_Attribute12 VARCHAR2,
96 X_Attribute13 VARCHAR2,
97 X_Attribute14 VARCHAR2,
98 X_Attribute15 VARCHAR2,
99 X_Closed_Code VARCHAR2,
100 X_Ussgl_Transaction_Code VARCHAR2,
101 X_Government_Context VARCHAR2,
102 X_Supply_Agreement_Flag VARCHAR2,
103 X_unapprove_doc IN OUT NOCOPY BOOLEAN,
104 X_Price_Update_Tolerance NUMBER,
105 X_Global_Attribute_Category VARCHAR2,
106 X_Global_Attribute1 VARCHAR2,
107 X_Global_Attribute2 VARCHAR2,
108 X_Global_Attribute3 VARCHAR2,
109 X_Global_Attribute4 VARCHAR2,
110 X_Global_Attribute5 VARCHAR2,
111 X_Global_Attribute6 VARCHAR2,
112 X_Global_Attribute7 VARCHAR2,
113 X_Global_Attribute8 VARCHAR2,
114 X_Global_Attribute9 VARCHAR2,
115 X_Global_Attribute10 VARCHAR2,
116 X_Global_Attribute11 VARCHAR2,
117 X_Global_Attribute12 VARCHAR2,
118 X_Global_Attribute13 VARCHAR2,
119 X_Global_Attribute14 VARCHAR2,
120 X_Global_Attribute15 VARCHAR2,
121 X_Global_Attribute16 VARCHAR2,
122 X_Global_Attribute17 VARCHAR2,
123 X_Global_Attribute18 VARCHAR2,
124 X_Global_Attribute19 VARCHAR2,
125 X_Global_Attribute20 VARCHAR2,
126 p_shipping_control IN VARCHAR2, -- <INBOUND LOGISTICS FPJ>
127 p_encumbrance_required_flag IN VARCHAR2 DEFAULT NULL --<ENCUMBRANCE FPJ>
128 ,p_kterms_art_upd_date IN DATE --<CONTERMS FPJ>
129 ,p_kterms_deliv_upd_date IN DATE --<CONTERMS FPJ>
130 ,p_enable_all_sites IN varchar2 --<R12GCPA>
131 ) IS
132
133 X_progress VARCHAR2(3) := NULL;
134 X_allow_delete boolean;
135 x_item_type varchar2(8);
136 x_item_key varchar2(240);
137 l_doc_header_id number;
138 l_doc_subtype varchar2(25);
139
140 CURSOR unapproved_releases IS
141 SELECT PORH.PO_release_ID,
142 POH.Type_Lookup_Code
143 FROM PO_RELEASES PORH, PO_HEADERS POH
144 WHERE NVL(PORH.authorization_status,'INCOMPLETE') IN
145 ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
146 AND NVL(PORH.cancel_flag,'N') = 'N'
147 AND NVL(PORH.closed_code,'OPEN') <> 'FINALLY CLOSED'
148 AND POH.PO_HEADER_ID = PORH.PO_HEADER_ID
149 AND PORH.PO_HEADER_ID = X_Po_Header_Id;
150
151 /* FPJ CONTERMS START */
152 l_contracts_document_type VARCHAR2(150);
153 l_conterms_exist_flag po_headers_all.conterms_exist_flag%TYPE;
154 l_vendor_id po_headers_all.vendor_id%TYPE;
155 l_vendor_site_id po_headers_all.vendor_site_id%TYPE;
156 l_vendor_information_updated boolean := FND_API.To_boolean(FND_API.G_FALSE);
157
158 l_msg_data VARCHAR2(2000);
159 l_msg_count NUMBER;
160 l_return_status VARCHAR2(1);
161 /* FPJ CONTERMS END */
162
163
164 BEGIN
165
166 /* Check if the document has to be unapproved. If the header is
167 already unapproved or never approved don't have to
168 unapprove it - this applies to POs and PAs */
169
170 if ((X_type_lookup_code = 'STANDARD') or
171 (X_type_lookup_code = 'PLANNED') or
172 (X_type_lookup_code = 'BLANKET') or
173 (X_type_lookup_code = 'CONTRACT') ) then
174
175 -- dbms_output.put_line('Checking if the doc has to be unapproved');
176
177 X_progress := '010';
178
179 -- Bug 1287970 Amitabh
180
181 if (X_Approved_Flag = 'Y') OR
182 ( X_Authorization_Status = 'PRE-APPROVED') THEN
183
184 X_unapprove_doc := po_headers_sv2.val_approval_status(
185 X_po_header_id ,
186 X_agent_id ,
187 X_vendor_site_id ,
188 X_vendor_contact_id ,
189 X_confirming_order_flag ,
190 X_ship_to_location_id ,
191 X_bill_to_location_id ,
192 X_terms_id ,
193 X_ship_via_lookup_code ,
194 X_fob_lookup_code ,
195 X_freight_terms_lookup_code ,
196 X_note_to_vendor ,
197 X_acceptance_required_flag ,
198 X_acceptance_due_date ,
199 X_blanket_total_amount ,
200 X_start_date ,
201 X_end_date ,
202 X_amount_limit
203 ,p_kterms_art_upd_date --<CONTERMS FPJ>
204 ,p_kterms_deliv_upd_date --<CONTERMS FPJ>
205 , p_shipping_control -- <INBOUND LOGISTICS FPJ>
206 );
207
208
209 /* If the document has to be unapproved, set the approved_flag to be 'R' */
210 /* If returning true then the document needs to be unapproved. */
211 /* Amitabh: Bug 1287970
212 ** Change the status to IN PROCESS if it is PRE-APPROVED.
213 */
214
215 -- Bug 3663073: Changed IF statement logic so that if document
216 -- has to be unapproved, and doc is PRE-APPROVED, then
217 -- doc goes to 'IN PROCESS'.
218 -- [fix for bug 1287970 does not seem to have correct logic]
219
220 if X_unapprove_doc then
221
222 IF (X_Authorization_Status = 'PRE-APPROVED')
223 THEN
224 X_Approved_Flag := 'N';
225 X_Authorization_Status := 'IN PROCESS';
226 ELSE
227 X_Approved_Flag := 'R';
228 X_Authorization_Status := 'REQUIRES REAPPROVAL';
229 END IF;
230
231 end if;
232
233 end if;
234
235
236 end if; /* End of PO/PA specific check */
237
238 /* FPJ CONTERMS START*/
239 -- call this before the update occurs
240 -- SQL WHAT: select vendor information and conterms flag
241 -- SQL WHY : to check if vendor info was updated on a po with terms
242 -- SQL JOIN: rowid
243
244 SELECT vendor_id, vendor_site_id, conterms_exist_flag
245 INTO l_vendor_id, l_vendor_site_id, l_conterms_exist_flag
246 FROM po_headers_all
247 WHERE rowid = X_Rowid;
248 /* FPJ CONTERMS END*/
249
250 -- dbms_output.put_line('Before the Table Handler Update');
251 X_progress := '020';
252
253 po_headers_pkg_s2.update_row(
254 X_Rowid ,
255 X_Po_Header_Id ,
256 X_Agent_Id ,
257 X_Type_Lookup_Code ,
258 X_Last_Update_Date ,
259 X_Last_Updated_By ,
260 X_Segment1 ,
261 X_Summary_Flag ,
262 X_Enabled_Flag ,
263 X_Segment2 ,
264 X_Segment3 ,
265 X_Segment4 ,
266 X_Segment5 ,
267 X_Start_Date_Active ,
268 X_End_Date_Active ,
269 X_Last_Update_Login ,
270 X_Vendor_Id ,
271 X_Vendor_Site_Id ,
272 X_Vendor_Contact_Id ,
273 X_Pcard_Id , -- Supplier Pcard FPH
274 X_Ship_To_Location_Id ,
275 X_Bill_To_Location_Id ,
276 X_Terms_Id ,
277 X_Ship_Via_Lookup_Code ,
278 X_Fob_Lookup_Code ,
279 X_Pay_On_Code ,
280 X_Freight_Terms_Lookup_Code ,
281 X_Status_Lookup_Code ,
282 X_Currency_Code ,
283 X_Rate_Type ,
284 X_Rate_Date ,
285 X_Rate ,
286 X_From_Header_Id ,
287 X_From_Type_Lookup_Code ,
288 X_Start_Date ,
289 X_End_Date ,
290 X_Blanket_Total_Amount ,
291 X_Authorization_Status ,
292 X_Revision_Num ,
293 X_Revised_Date ,
294 X_Approved_Flag ,
295 X_Approved_Date ,
296 X_Amount_Limit ,
297 X_Min_Release_Amount ,
298 X_Note_To_Authorizer ,
299 X_Note_To_Vendor ,
300 X_Note_To_Receiver ,
301 X_Print_Count ,
302 X_Printed_Date ,
303 X_Vendor_Order_Num ,
304 X_Confirming_Order_Flag ,
305 X_Comments ,
306 X_Reply_Date ,
307 X_Reply_Method_Lookup_Code ,
308 X_Rfq_Close_Date ,
309 X_Quote_Type_Lookup_Code ,
310 X_Quotation_Class_Code ,
311 X_Quote_Warning_Delay_Unit ,
312 X_Quote_Warning_Delay ,
313 X_Quote_Vendor_Quote_Number ,
314 X_Acceptance_Required_Flag ,
315 X_Acceptance_Due_Date ,
316 X_Closed_Date ,
317 X_User_Hold_Flag ,
318 X_Approval_Required_Flag ,
319 X_Cancel_Flag ,
320 X_Firm_Status_Lookup_Code ,
321 X_Firm_Date ,
322 X_Frozen_Flag ,
323 X_Attribute_Category ,
324 X_Attribute1 ,
325 X_Attribute2 ,
326 X_Attribute3 ,
327 X_Attribute4 ,
328 X_Attribute5 ,
329 X_Attribute6 ,
330 X_Attribute7 ,
331 X_Attribute8 ,
332 X_Attribute9 ,
333 X_Attribute10 ,
334 X_Attribute11 ,
335 X_Attribute12 ,
336 X_Attribute13 ,
337 X_Attribute14 ,
338 X_Attribute15 ,
339 X_Closed_Code ,
340 NULL, --<R12 SLA>
341 X_Government_Context ,
342 X_Supply_Agreement_Flag ,
343 X_Price_Update_Tolerance ,
344 X_Global_Attribute_Category ,
345 X_Global_Attribute1 ,
346 X_Global_Attribute2 ,
347 X_Global_Attribute3 ,
348 X_Global_Attribute4 ,
349 X_Global_Attribute5 ,
350 X_Global_Attribute6 ,
351 X_Global_Attribute7 ,
352 X_Global_Attribute8 ,
353 X_Global_Attribute9 ,
354 X_Global_Attribute10 ,
355 X_Global_Attribute11 ,
356 X_Global_Attribute12 ,
357 X_Global_Attribute13 ,
358 X_Global_Attribute14 ,
359 X_Global_Attribute15 ,
360 X_Global_Attribute16 ,
361 X_Global_Attribute17 ,
362 X_Global_Attribute18 ,
363 X_Global_Attribute19 ,
364 X_Global_Attribute20 ,
365 p_shipping_control, -- <INBOUND LOGISTICS FPJ>
366 p_encumbrance_required_flag --<ENCUMBRANCE FPJ>
367 ,p_kterms_art_upd_date --<CONTERMS FPJ>
368 ,p_kterms_deliv_upd_date --<CONTERMS FPJ>
369 ,p_enable_all_sites --<R12GCPA>
370 );
371
372 /* FPJ CONTERMS START*/
373
374 X_progress := '025';
375 IF (l_vendor_id = X_Vendor_Id) THEN
376 IF (l_vendor_site_id <> X_vendor_Site_Id) THEN
377 l_vendor_information_updated := FND_API.To_boolean(FND_API.G_TRUE);
378 END IF;
379 ELSE
380 l_vendor_information_updated := FND_API.To_boolean(FND_API.G_TRUE);
381 END IF;
382
383
384 IF ((l_vendor_information_updated) AND
385 (NVL(l_conterms_exist_flag, 'N')='Y')) THEN
386 IF (X_Type_Lookup_Code IN ('BLANKET', 'CONTRACT')) THEN
387 l_contracts_document_type := 'PA_'||X_Type_Lookup_Code;
388 ELSIF (X_Type_Lookup_Code = 'STANDARD') THEN
389 l_contracts_document_type := 'PO_'||X_Type_Lookup_Code;
390 END IF;
391
392 -- call contracts API to update the supplier information on terms
393 OKC_MANAGE_DELIVERABLES_GRP.updateExtPartyOnDeliverables (
394 p_api_version => 1.0,
395 p_bus_doc_id => X_po_header_id,
396 p_bus_doc_type => l_contracts_document_type,
397 p_external_party_id => X_Vendor_Id,
398 p_external_party_site_id => X_Vendor_Site_Id,
399 x_msg_data => l_msg_data,
400 x_msg_count => l_msg_count,
401 x_return_status => l_return_status);
402
403 END IF;
404
405
406 /* Call routine to send notifications/update notifications */
407 x_progress := '025';
408 if (x_type_lookup_code not in ('RFQ', 'QUOTATION')) then
409 if ((x_type_lookup_code = 'BLANKET') or (x_type_lookup_code = 'PLANNED')) then
410
411 OPEN unapproved_releases ;
412 LOOP
413 FETCH unapproved_releases
414 into l_doc_header_id,
415 l_doc_subtype;
416 EXIT WHEN Unapproved_releases%NOTFOUND;
417
418 if(l_doc_subtype = 'PLANNED') then
419 l_doc_subtype := 'SCHEDULED';
420 end if;
421
422 select wf_item_type,wf_item_key
423 into x_item_type,x_item_key
424 from po_releases
425 where po_release_id = l_doc_header_id;
426
427 if (x_item_type is null and x_item_key is null ) then
428 po_approval_reminder_sv.cancel_notif(l_doc_subtype,l_doc_header_id,'Y');
429 else
430 po_approval_reminder_sv.cancel_notif(l_doc_subtype,l_doc_header_id,'Y');
431 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
432 end if;
433
434
435 END LOOP;
436
437 CLOSE unapproved_releases;
438 else /*not blanket PO */
439 /* no need to do anything in the case of a standard PO */
440 null;
441 end if;
442
443 /* po_notifications_sv1.send_po_notif (x_type_lookup_code,
444 x_po_header_id,
445 null,
446 null,
447 null,
448 null,
449 null,
450 null); */
451 /*hvadlamu : commenting out the send notifications call. will now be handled by workflow*/
452 elsif (x_type_lookup_code = 'RFQ') then
453 if (x_status_lookup_code = 'C') then
454
455 /* Validate if the Document can be deleted */
456
457 X_allow_delete := po_headers_sv1.val_delete (X_po_header_id, X_type_lookup_code);
458
459 /* If the doc can be deleted, */
460
461 if (X_allow_delete) then
462
463 /* Call routine to delete PO notifications */
464 /*hvadlamu commenting out and adding the WF_engine call*/
465
466 /* po_notifications_sv1.delete_po_notif (x_type_lookup_code,
467 x_po_header_id); */
468 null;
469 end if;
470 else
471
472 /* po_notifications_sv1.send_po_notif (x_type_lookup_code,
473 x_po_header_id,
474 null,
475 X_reply_date,
476 X_rfq_close_date,
477 null,
478 null,
479 null); */
480 null;
481 /*hvadlamu : commenting out the send notifications call. will now be handled by workflow*/
482 end if;
483
484 elsif (x_type_lookup_code = 'QUOTATION') then
485
486 /*po_notifications_sv1.send_po_notif (x_type_lookup_code,
487 x_po_header_id,
488 null,
489 (X_end_date - X_quote_warning_delay),
490 X_end_date,
491 null,
492 null,
493 null); */
494 null;
495 /*hvadlamu : commenting out the send notifications call. will now be handled by workflow*/
496
497 end if;
498
499
500 EXCEPTION
501 when others then
502 po_message_s.sql_error('update_header', X_progress, sqlcode);
503 raise;
504
505 END update_header;
506
507
508 END PO_HEADERS_SV5;