1 PACKAGE BODY PO_APPROVED_SUPPLIER_LIST_SV AS
2 /* $Header: POXVASLB.pls 120.8.12020000.2 2013/02/10 17:35:12 vegajula ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
6
7 -- <INBOUND LOGISTICS FPJ START>
8 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_APPROVED_SUPPLIER_LIST_SV';
9 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
10 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
11 -- <INBOUND LOGISTICS FPJ END>
12
13 /*==================================================================
14 PROCEDURE NAME: create_po_asl_entries()
15
16 DESCRIPTION: This API inserts row into po_approved_supplier_list,
17 po_asl_attributes,po_asl_documents
18
19 PARAMETERS: X_interface_header_id, X_interface_line_id -
20 Sequence number generated from po_headers_interface_s
21 and po_lines_interface_s.
22 X_item_id, X_vendor_id, X_po_header_id,
23 X_po_line_id,X_document_type
24 Values of the document that is created from
25 the PDOI interface tables.
26 X_category_id - Creatgory_id for the Category
27 X_header_processable_flag - Value is N if there was any
28 error encountered. Set in the procedure
29 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
30 X_po_interface_error_code - This is the code used to populate interface_type
31 field in po_interface_errors table.
32 p_sourcing_level
33 This parameter specifies if the Sourcing Rule /ASL should be Global/Local
34 and if the assignment should be Item or Item Organization.
35
36 =======================================================================*/
37
38 PROCEDURE create_po_asl_entries
39 ( x_interface_header_id IN NUMBER,
40 X_interface_line_id IN NUMBER,
41 X_item_id IN NUMBER,
42 X_category_id IN NUMBER,
43 X_po_header_id IN NUMBER,
44 X_po_line_id IN NUMBER,
45 X_document_type IN VARCHAR2,
46 x_vendor_site_id IN NUMBER, -- GA FPI
47 X_rel_gen_method IN VARCHAR2,
48 x_asl_org_id IN NUMBER,
49 X_header_processable_flag OUT NOCOPY VARCHAR2,
50 X_po_interface_error_code IN VARCHAR2,
51 --<LOCAL SR/ASL PROJECT 11i11 START>
52 p_sourcing_level IN VARCHAR2 DEFAULT NULL
53 --<LOCAL SR/ASL PROJECT 11i11 END>
54 )
55 --
56 IS
57 --
58 x_last_update_date date := sysdate;
59 x_last_updated_by number := fnd_global.user_id ;
60 x_creation_date date := sysdate;
61 x_effective_date date ;
62 x_disable_date date;
63 x_last_update_login number := fnd_global.user_id;
64 x_created_by number := fnd_global.user_id;
65 --
66 x_record_unique BOOLEAN;
67 x_asl_id number := null;
68 x_owning_organization_id number := null;
69 x_vs_org_id number := null;
70 x_vendor_product_num VARCHAR2(30);
71 x_progress VARCHAR2(3);
72 --
73 x_asl_status_id number := null;
74 x_sequence_num number := 1;
75 x_purch_uom varchar2(25) := null;
76 x_att_puom varchar2(25) := null;
77 x_dummy_count number := null;
78 X_process_flag varchar2(1) := 'Y';
79 --
80 X_vendor_id number;
81 x_type_lookup_code varchar2(20);
82 l_rel_gen_method varchar2(25);
83
84 x_purchasing_flag varchar2(1) := 'Y';
85 x_osp_flag varchar2(1) := 'Y';
86 --
87 -- <INBOUND LOGISTICS FPJ START>
88 l_api_version CONSTANT NUMBER := 1.0;
89 l_return_status VARCHAR2(1);
90 l_msg_count NUMBER;
91 l_msg_data FND_NEW_MESSAGES.message_text%TYPE;
92 l_msg_buf VARCHAR2(2000);
93 l_api_name CONSTANT VARCHAR2(40) := 'create_po_asl_entries';
94 l_progress VARCHAR2(3) := '001';
95 -- <INBOUND LOGISTICS FPJ END>
96
97 --<LOCAL SR/ASL PROJECT 11i11 START>
98 l_using_organization_id HR_ORGANIZATION_UNITS.organization_id%type;
99 --<LOCAL SR/ASL PROJECT 11i11 END>
100
101 ---------- 10022351
102 x_line_id number := null;
103 x_date date;
104
105 BEGIN
106 x_header_processable_flag := 'Y'; -- Bug 2692597
107
108 -- <ASL ERECORD FPJ>
109 -- Adding a Save Point here. If this procedure has an exception it
110 -- will be rollbacked to this point. We need to do this because Approval WF
111 -- simply ignores the exception and PDOI will treat an exception here as an
112 -- error, but either way the ASL should not be created.
113 SAVEPOINT create_po_asl_entries_SP;
114
115 /* GA FPI Start */
116 /* check to see if the item is valid in the OU of the vendor_site_id passed in */
117
118 SELECT org_id
119 INTO x_vs_org_id
120 FROM po_vendor_sites_all
121 WHERE vendor_site_id = x_vendor_site_id;
122
123 -- Bug 3795146: Handle null org id for a single org instance
124
125 --<LOCAL SR/ASL PROJECT 11i11 START>
126 /*
127 The value of using organization id for Global ASL's is -1. Set the value of
128 l_using_organization_id to -1 if the sourcing level is 'ITEM'. Else if the
129 Sourcing level is 'ITEM-ORGANIZATION'we need to set the value to x_asl_org_id
130
131 We need to select the value of inventory organization id only if the value
132 of sourcing_level is is 'ITEM'. This would happen if the calling program is Approval
133 Workflow or if POASLGEN AND PDOI call the program with Sourcing Level set to Item
134 */
135
136 IF(nvl(p_sourcing_level,'ITEM')='ITEM-ORGANIZATION') THEN
137 l_using_organization_id :=x_asl_org_id;
138 x_owning_organization_id:=x_asl_org_id;
139 ELSE
140 SELECT inventory_organization_id
141 INTO x_owning_organization_id
142 FROM financials_system_params_all
143 WHERE nvl(org_id,-99) = nvl(x_vs_org_id,-99);
144 l_using_organization_id :=-1;
145 END IF;
146
147 --<LOCAL SR/ASL PROJECT 11i11 END>
148
149 IF (g_po_pdoi_write_to_file = 'Y') THEN
150 PO_DEBUG.put_line('Create PO ASL Entry for item: '||x_item_id||
151 ' vendor site id: '||x_vendor_site_id||
152 ' inv org: '||x_owning_organization_id);
153 END IF;
154
155 BEGIN --< Bug 3560121 > Moved BEGIN for exception handling purposes.
156
157 --<LOCAL SR/ASL PROJECT 11i11 START>
158 /*
159 If the value of x_asl_org_id is not null then we need to verify that
160 the item for which ASL needs to be created is enabled in the inventory
161 organization x_asl_org_id
162 */
163 /* Bug 6142693 commented the or clause ("_asl_org_id is not null")
164 in order to ensure only global documents go through this check
165 Reverted the condition introduced in Local SR/ASL project. */
166
167 IF (po_ga_pvt.is_global_agreement(X_po_header_id)) Then
168 -- or (x_asl_org_id is not null)) THEN -- Bug 2737193
169 --<LOCAL SR/ASL PROJECT 11i11 END>
170
171 SELECT purchasing_enabled_flag,
172 outside_operation_flag
173 INTO x_purchasing_flag,
174 x_osp_flag
175 FROM mtl_system_items
176 WHERE inventory_item_id = x_item_id
177 AND organization_id = x_owning_organization_id;
178
179 --<Shared Proc FPJ>
180 --Introducing the NVL around purchasing org
181 IF nvl(x_purchasing_flag, 'N') = 'N'
182 -- OR x_osp_flag = 'Y' <bug#14539961>
183 THEN
184 --< Bug 3560121 Start >
185 IF (g_po_pdoi_write_to_file = 'Y') THEN
186 PO_DEBUG.put_line('Cannot create ASL entry. Purchasable: '
187 ||x_purchasing_flag||' OSP item: '
188 ||x_osp_flag||'. Insert warning msg');
189 END IF;
190 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
191 ( x_interface_type => 'PO_DOCS_OPEN_INTERFACE'
192 , x_error_type => 'WARNING'
193 , x_batch_id => NULL
194 , x_interface_header_id => x_interface_header_id
195 , x_interface_line_id => x_interface_line_id
196 , x_error_message_name => 'PO_PDOI_CREATE_ASL_INVAL_ITEM'
197 , x_table_name => 'PO_LINES_INTERFACE'
198 , x_column_name => 'ITEM_ID'
199 , x_tokenname1 => 'ORG_NAME'
200 , x_tokenname2 => NULL
201 , x_tokenname3 => NULL
202 , x_tokenname4 => NULL
203 , x_tokenname5 => NULL
204 , x_tokenname6 => NULL
205 , x_tokenvalue1 => PO_GA_PVT.get_org_name(p_org_id => x_owning_organization_id)
206 , x_tokenvalue2 => NULL
207 , x_tokenvalue3 => NULL
208 , x_tokenvalue4 => NULL
209 , x_tokenvalue5 => NULL
210 , x_tokenvalue6 => NULL
211 , x_header_processable_flag => x_header_processable_flag
212 , x_interface_dist_id => NULL
213 );
214 -- This is just a warning. Processing should continue, so reset the
215 -- flag back to 'Y' before returning.
216 x_header_processable_flag := 'Y';
217 --< Bug 3560121 End >
218 return;
219 END IF;
220
221 END IF;
222
223 EXCEPTION
224 --< Bug 3560121 Start > Should only catch NO_DATA_FOUND here. Also, need
225 -- to insert a warning message in this case.
226 WHEN NO_DATA_FOUND THEN
227 IF (g_po_pdoi_write_to_file = 'Y') THEN
228 PO_DEBUG.put_line('Cannot create ASL entry; item not defined in inv org. Insert warning msg');
229 END IF;
230 PO_INTERFACE_ERRORS_SV1.handle_interface_errors
231 ( x_interface_type => 'PO_DOCS_OPEN_INTERFACE'
232 , x_error_type => 'WARNING'
233 , x_batch_id => NULL
234 , x_interface_header_id => x_interface_header_id
235 , x_interface_line_id => x_interface_line_id
236 , x_error_message_name => 'PO_PDOI_CREATE_ASL_NO_ITEM'
237 , x_table_name => 'PO_LINES_INTERFACE'
238 , x_column_name => 'ITEM_ID'
239 , x_tokenname1 => 'ORG_NAME'
240 , x_tokenname2 => NULL
241 , x_tokenname3 => NULL
242 , x_tokenname4 => NULL
243 , x_tokenname5 => NULL
244 , x_tokenname6 => NULL
245 , x_tokenvalue1 => PO_GA_PVT.get_org_name(p_org_id => x_owning_organization_id)
246 , x_tokenvalue2 => NULL
247 , x_tokenvalue3 => NULL
248 , x_tokenvalue4 => NULL
249 , x_tokenvalue5 => NULL
250 , x_tokenvalue6 => NULL
251 , x_header_processable_flag => x_header_processable_flag
252 , x_interface_dist_id => NULL
253 );
254 -- This is just a warning. Processing should continue, so reset the
255 -- flag back to 'Y' before returning.
256 x_header_processable_flag := 'Y';
257 return;
258 --< Bug 3560121 End >
259 END;
260 /* GA FPI End */
261
262 -- check to see if default asl status is available
263 -- Get the default status from po_asl_statuses. If no default status has
264 -- been selected, insert error message and terminate transaction.
265 BEGIN
266 --
267 SELECT status_id
268 INTO x_asl_status_id
269 FROM po_asl_statuses
270 WHERE asl_default_flag = 'Y';
271 --
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 IF (g_po_pdoi_write_to_file = 'Y') THEN
275 PO_DEBUG.put_line('** ERROR: Please specify a default status in the ASL');
276 PO_DEBUG.put_line('** Statuses form before proceeding with this.');
280 X_po_interface_error_code,
277 END IF;
278 X_process_flag := 'N';
279 po_interface_errors_sv1.handle_interface_errors(
281 'FATAL',
282 null,
283 X_interface_header_id,
284 X_interface_line_id,
285 'PO_PDOI_NO_ASL_STATUS',
286 'PO_HEADERS_INTERFACE',
287 'APPROVAL_STATUS',
288 null, null,null,null,null,null,
289 null, null,null, null,null,null,
290 X_header_processable_flag);
291
292 END;
293 --
294 -- Get Vendor_id and Vendor_site_id from Blanket
295 --
296
297 /*
298 We would have to replace the views po_headers with table
299 po_headers_all because the concurrent program POASLGEN can
300 access global agreements from OU's which are merely Purchasing Org's
301 for the GA and not necessarily Owning Orgs
302 */
303
304 SELECT vendor_id,
305 type_lookup_code
306 INTO x_vendor_id,
307 x_type_lookup_code
308 FROM po_headers_all --<LOCAL SR/ASL PROJECT 11i11>
309 WHERE po_header_id = X_po_header_id;
310
311 --
312 -- Get the Purchasing UOM from P.O.
313 --
314
315 /*
316 We would have to replace the views po_headers with table
317 po_headers_all because the concurrent program POASLGEN can
318 access global agreements from OU's which are merely Purchasing Org's
319 for the GA and not necessarily Owning Orgs
320 */
321
322 SELECT pol.unit_meas_lookup_code,
323 pol.vendor_product_num
324 INTO x_purch_uom,
325 x_vendor_product_num
326 FROM po_lines_all pol --<LOCAL SR/ASL PROJECT 11i11>
327 WHERE po_line_id = x_po_line_id;
328 --
329 --
330 -- Check if ASL already exists for this combination.
331 --
332 --<LOCAL SR/ASL PROJECT 11i11 START>
333 /*
334 Depending on the value of sourcing level we would have to pass
335 in the value of inventory_organization_id appropriately to check
336 for uniqueness of asl record
337 */
338
339 IF nvl(p_sourcing_level,'ITEM')='ITEM' THEN
340 x_record_unique := po_asl_sv.check_record_unique(
341 NULL,
342 x_vendor_id,
343 x_vendor_site_id,
344 x_item_id,
345 NULL,
346 -1);
347 ELSE
348 x_record_unique := po_asl_sv.check_record_unique(
349 NULL,
350 x_vendor_id,
351 x_vendor_site_id,
352 x_item_id,
353 NULL,
354 x_owning_organization_id);
355
356 END IF;
357 --<LOCAL SR/ASL PROJECT 11i11 END>
358 --
359 IF x_record_unique THEN
360
361 SELECT PO_APPROVED_SUPPLIER_LIST_S.NEXTVAL
362 INTO x_asl_id
363 FROM SYS.DUAL;
364
365 IF (g_po_pdoi_write_to_file = 'Y') THEN
366 PO_DEBUG.put_line('Creating Record in Po approved Supplier List');
367 END IF;
368 --
369 INSERT INTO PO_APPROVED_SUPPLIER_LIST (
370 asl_id ,
371 using_organization_id ,
372 owning_organization_id ,
373 vendor_business_type ,
374 asl_status_id ,
375 last_update_date ,
376 last_updated_by ,
377 creation_date ,
378 created_by ,
379 vendor_id ,
380 vendor_site_id ,
381 item_id ,
382 primary_vendor_item ,
383 last_update_login ,
384 request_id
385 ) VALUES (
386 x_asl_id ,
387 l_using_organization_id, --<LOCAL SR/ASL PROJECT 11i11>
388 x_owning_organization_id ,
389 'DIRECT' ,
390 x_asl_status_id ,
391 x_last_update_date ,
392 x_last_updated_by ,
393 x_creation_date ,
394 x_created_by ,
395 x_vendor_id ,
396 x_vendor_site_id ,
397 x_item_id ,
398 x_vendor_product_num ,
399 x_last_update_login ,
400 null
401 );
402
403 -- <INBOUND LOGISTICS FPJ START>
404 l_progress := '020';
405 l_return_status := FND_API.G_RET_STS_SUCCESS;
406 IF (g_fnd_debug = 'Y') THEN
407 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
408 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
409 MODULE => c_log_head || '.'||l_api_name||'.' || l_progress,
410 MESSAGE => 'Call PO_BUSINESSEVENT_PVT.raise_event'
411 );
412 END IF;
413 END IF;
414
415 PO_BUSINESSEVENT_PVT.raise_event
416 (
417 p_api_version => l_api_version,
418 x_return_status => l_return_status,
419 x_msg_count => l_msg_count,
420 x_msg_data => l_msg_data,
424 );
421 p_event_name => 'oracle.apps.po.event.create_asl',
422 p_entity_name => 'ASL',
423 p_entity_id => x_asl_id
425
426 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
427 IF (g_fnd_debug = 'Y') THEN
428 l_msg_buf := NULL;
429 l_msg_buf := FND_MSG_PUB.Get( p_msg_index => 1,
430 p_encoded => 'F');
431 l_msg_buf := SUBSTR('ASL' || x_asl_id || 'errors out at' || l_progress || l_msg_buf, 1, 2000);
432 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
433 FND_LOG.string( LOG_LEVEL => FND_LOG.level_unexpected,
434 MODULE => c_log_head || '.'||l_api_name||'.error_exception',
435 MESSAGE => l_msg_buf
436 );
437 END IF;
438 END IF;
439 ELSE
440 IF (g_fnd_debug = 'Y') THEN
441 l_msg_buf := NULL;
442 l_msg_buf := SUBSTR('ASL' || x_asl_id||'raised business event successfully', 1, 2000);
443 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
444 FND_LOG.string( LOG_LEVEL => FND_LOG.level_statement,
445 MODULE => c_log_head || '.'||l_api_name,
446 MESSAGE => l_msg_buf
447 );
448 END IF;
449 END IF;
450 END IF; -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
451
452 -- <INBOUND LOGISTICS FPJ END>
453
454 IF (g_po_pdoi_write_to_file = 'Y') THEN
455 PO_DEBUG.put_line('Creating Record in Po Asl Attribbutes ');
456 END IF;
457
458 -- create global ASL in po_asl_documents
459
460
461 INSERT INTO po_asl_attributes(
462 asl_id,
463 using_organization_id,
464 last_update_date,
465 last_updated_by,
466 creation_date,
467 created_by,
468 last_update_login,
469 document_sourcing_method,
470 release_generation_method,
471 enable_plan_schedule_flag,
472 enable_ship_schedule_flag,
473 enable_autoschedule_flag,
474 enable_authorizations_flag,
475 vendor_id,
476 vendor_site_id,
477 purchasing_unit_of_measure,
478 item_id
479 ) VALUES (
480 x_asl_id,
481 l_using_organization_id, --<LOCAL SR/ASL PROJECT 11i11>
482 x_last_update_date,
483 x_last_updated_by,
484 x_creation_date,
485 x_created_by,
486 x_last_update_login,
487 'ASL',
488 DECODE(X_type_lookup_code, 'BLANKET', X_rel_gen_method, NULL),
489 'N',
490 'N',
491 'N',
492 'N',
493 x_vendor_id,
494 x_vendor_site_id,
495 x_purch_uom,
496 x_item_id);
497 --
498
499 -- <ASL ERECORD FPJ START>
500 -- bug3236816: Move the code that raises eres event after
501 -- PO ASL Attribute is created
502
503 PO_ASL_SV.raise_asl_eres_event
504 ( x_return_status => l_return_status,
505 p_asl_id => x_asl_id,
506 p_action => PO_ASL_SV.G_EVENT_INSERT,
507 p_calling_from => 'PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries',
508 p_ackn_note => NULL,
509 p_autonomous_commit => FND_API.G_FALSE
510 );
511
512 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
513 RAISE G_EXC_ERES_ERROR;
514 END IF;
515
516 -- <ASL ERECORD FPJ END>
517
518 ELSE --Record Not Unique
519 -- If supplier-item relation exists, return asl_id for this asl entry.
520 --
521 IF (g_po_pdoi_write_to_file = 'Y') THEN
522 PO_DEBUG.put_line('ASL exists for Vndr' || to_char(x_vendor_id));
523 PO_DEBUG.put_line('ASL exists for VndrSite' || to_char(x_vendor_site_id));
524 PO_DEBUG.put_line('ASL already exists for Item' || to_char(x_item_id));
525 END IF;
526 --
527 /* BUG No.1541387:For the case when the sourcing rule is purged and the
528 corresponding ASL consists of no supplier_site_code -
529 making the provision for vendor_site_id and
530 x_vendor_site_id to be null without an error.
531 */
532
533 x_progress := '050';
534
535
536 SELECT asl_id
537 INTO x_asl_id
538 FROM po_approved_supplier_list pasl
539 WHERE pasl.vendor_id = x_vendor_id
540 AND ( pasl.vendor_site_id = x_vendor_site_id
541 OR ( pasl.vendor_site_id is NULL
542 AND x_vendor_site_id is NULL))
543 AND pasl.item_id = x_item_id
544 AND using_organization_id = l_using_organization_id; --<LOCAL SR/ASL PROJECT 11i11>
545
546
547 /*
548 Bug 2361161 If the ASL entry exists then we update the attributes with the release generation method
549 passed from the approval window. if the Purchasing UOM has not been entered update that with the value
550 from the po line
551 */
552
553 select purchasing_unit_of_measure,
554 release_generation_method
555 into x_att_puom,
556 l_rel_gen_method
557 from po_asl_attributes
558 where asl_id = x_asl_id
559 and using_organization_id =l_using_organization_id --<LOCAL SR/ASL PROJECT 11i11>
560 and vendor_id = x_vendor_id
561 and vendor_site_id = x_vendor_site_id
562 and item_id = x_item_id;
563
564 if x_att_puom is null then
565 x_att_puom := x_purch_uom;
566 end if;
567
568 /* Bug 2438375 fix by davidng. Added the constraint "l_rel_gen_method is null"
569 Hence we are only updating the Release Generation Method if it is originally null */
570 if (X_type_lookup_code = 'BLANKET' and X_rel_gen_method is not null and l_rel_gen_method is null) then
571 l_rel_gen_method := X_rel_gen_method;
572 end if;
573
574
575 UPDATE po_asl_attributes
576 set release_generation_method = l_rel_gen_method,
577 purchasing_unit_of_measure = x_att_puom,
578 last_update_date = x_last_update_date,
579 last_updated_by = x_last_updated_by,
580 last_update_login = last_update_login
581 where asl_id = x_asl_id
582 and using_organization_id =l_using_organization_id --<LOCAL SR/ASL PROJECT 11i11>
583 and vendor_id = x_vendor_id
584 and vendor_site_id = x_vendor_site_id
585 and item_id = x_item_id;
586
587 END IF;
588
589 -- Make sure that this source document does not already exist for
590 -- this ASL entry.
591 x_progress := '020';
592 SELECT count(*)
593 INTO x_dummy_count
594 FROM po_asl_documents
595 WHERE asl_id = x_asl_id
596 AND using_organization_id = l_using_organization_id --<LOCAL SR/ASL PROJECT 11i11>
597 AND document_header_id = x_po_header_id
598 AND document_type_code = x_type_lookup_code;
599
600 IF x_dummy_count > 0 THEN
601
602 ---Bug10022351 START
603 SELECT document_line_id
604 INTO x_line_id
605 FROM po_asl_documents
606 WHERE asl_id = x_asl_id
607 AND using_organization_id = l_using_organization_id --<LOCAL SR/ASL PROJECT 11i11>
608 AND document_header_id = x_po_header_id
609 AND document_type_code = x_type_lookup_code;
610
611 SELECT expiration_date
612 INTO x_date
613 FROM po_lines_all pl
614 WHERE po_header_id = x_po_header_id
615 AND po_line_id = x_line_id;
616
617 IF (NVL(x_date , sysdate+1) >= SYSDATE) THEN ---10192008
618
619 IF (g_po_pdoi_write_to_file = 'Y') THEN
620 PO_DEBUG.put_line('Doc already exists for this ASL');
621 END IF;
622 null;
623
624 ELSE
625
626 UPDATE PO_ASL_DOCUMENTS
627 SET document_line_id = x_po_line_id,
628 last_update_date = x_last_update_date,
629 last_updated_by = x_last_updated_by,
630 last_update_login = last_update_login
631 WHERE asl_id = x_asl_id
632 AND document_header_id = x_po_header_id;
633
634
635 IF (g_po_pdoi_write_to_file = 'Y') THEN
636 PO_DEBUG.put_line('Updating the expired line on this ASL');
637 END IF;
638
639 END IF;
640
641 ---Bug10022351 END
642
643 ELSE
644 --
645 SELECT nvl(max(sequence_num)+1, 1)
646 INTO x_sequence_num
647 FROM po_asl_documents
648 WHERE asl_id = x_asl_id
649 AND using_organization_id = l_using_organization_id; --<LOCAL SR/ASL PROJECT 11i11>
650 --
651 IF (g_po_pdoi_write_to_file = 'Y') THEN
652 PO_DEBUG.put_line('Creating record in Po Asl Docs');
653 END IF;
654 --
655 -- Insert doc into po_asl_documents
656 --
657
658 INSERT INTO PO_ASL_DOCUMENTS(
659 asl_id,
660 using_organization_id,
661 sequence_num,
662 document_type_code,
663 document_header_id,
664 document_line_id,
665 last_update_date,
666 last_updated_by,
667 last_update_login,
668 creation_date,
669 created_by
670 ) VALUES (
671 x_asl_id,
672 l_using_organization_id, --<LOCAL SR/ASL PROJECT 11i11>
673 x_sequence_num,
674 x_type_lookup_code,
675 x_po_header_id,
676 x_po_line_id,
677 x_last_update_date,
678 x_last_updated_by,
679 x_last_update_login,
680 x_creation_date,
681 x_created_by
682 );
683
684 END IF;
685
686 -- END IF;
687
688 EXCEPTION
689 WHEN others THEN
690 ROLLBACK TO create_po_asl_entries_SP; -- <ASL ERECORD FPJ>
691 x_header_processable_flag := 'N'; -- Bug 2692597
692 --< Bug 3560121 Start >
693 IF (g_po_pdoi_write_to_file = 'Y') THEN
694 PO_DEBUG.put_line('Exception caught while creating ASL entries.');
695 END IF;
696 --< Bug 3560121 End >
697 po_message_s.sql_error('create_po_asl_entries', x_progress, sqlcode);
698 raise;
699 END create_po_asl_entries;
700
701 END PO_APPROVED_SUPPLIER_LIST_SV;
[Home] [Help]