[Home] [Help]
PACKAGE BODY: APPS.PO_ASL_SV
Source
1 PACKAGE BODY PO_ASL_SV as
2 /* $Header: POXA1LSB.pls 120.1 2006/04/10 02:52:34 scolvenk noship $ */
3
4 -- <ASL ERECORD FPJ START>
5 G_ERES_ENABLED CONSTANT VARCHAR2(1) :=
6 NVL(FND_PROFILE.value('EDR_ERES_ENABLED'), 'N');
7
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'PO_ASL_SV';
9
10 G_MODULE_PREFIX CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
11
12 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
13 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
14
15
16 TYPE asl_activity_rec IS RECORD
17 ( asl_id NUMBER,
18 action VARCHAR2(10)
19 );
20
21 TYPE asl_activity_tbl IS TABLE OF asl_activity_rec INDEX BY BINARY_INTEGER;
22
23 -- Global variables that maintain ASLs that need to be processed
24
25 g_asl_activities asl_activity_tbl;
26
27 -- bug3539651
28 -- g_asl_activities_index should never be null
29 g_asl_activities_index NUMBER := 0;
30
31
32 PROCEDURE get_identifier
33 ( p_asl_id IN NUMBER,
34 x_identifier OUT NOCOPY VARCHAR2,
35 x_identifier_value OUT NOCOPY VARCHAR2
36 );
37
38 -- <ASL ERECORD FPJ END>
39
40 /*=============================================================================
41
42 FUNCTION NAME: check_asl_action()
43
44 =============================================================================*/
45
46 function check_asl_action(x_action varchar2,
47 x_vendor_id number,
48 x_vendor_site_id number,
49 x_item_id number,
50 x_category_id number,
51 -- DEBUG for now, we just pass in one org;
52 -- DEBUG in the future, we need to implement passing a org_list
53 -- DEBUG x_ship_to_org_list orgTab
54 x_ship_to_org number) return number is
55
56 x_progress VARCHAR2(3) := NULL;
57 x_asl_status NUMBER := NULL;
58
59
60 -- Bug 2072963
61 cursor c_category_ids (p_item_id Number, p_org_id Number) IS
62 select category_id
63 from mtl_item_categories
64 where inventory_item_id = p_item_id
65 and organization_id = p_org_id;
66
67 x_asl_s NUMBER := 0;
68
69 begin
70
71 --dbms_output.put_line('Entering check_asl_action');
72
73 -- Query from po_approved_supplier_list to get the status (asl_status_id)
74 -- of the supplier, site, item, organization, category.
75 x_progress := '010';
76
77 -- Based on the status_id query from po_asl_status_rules to see
78 -- if the allow_action_flag is set to Y for the buisness rule
79 -- that is the same as the action that is passed in.
80 -- The action that is passed in should be equal to one of the
81 -- following based on where we are calling this from:
82 -- 1_PO_APPROVAL
83 -- 2_SOURCING
84 -- 3_SCHEDULE_CONFIRMATION
85 -- 4_DISTRIBUTOR_MFR_LINK
86 /*
87 commented the following as part of the performance fix for 1517028
88 SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
89 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
90 WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
91 AND ASL.vendor_id = x_vendor_id
92 AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
93 AND ( (ASL.item_id = x_item_id) OR
94 (ASL.category_id = x_category_id) OR
95 (ASL.category_id in (SELECT MIC.category_id
96 FROM MTL_ITEM_CATEGORIES MIC
97 WHERE MIC.inventory_item_id = x_item_id
98 AND MIC.organization_id = x_ship_to_org)))
99 AND ASL.asl_status_id = ASR.status_id
100 AND ASR.business_rule = x_action;
101 */
102
103 /*1517028 Breaking sql statement into three diffrent sql statements to
104 remove OR between checks for item_id and category id...never combine them...*/
105
106 /* Bug: 1968168 Replace PO_APPROVED_SUPPLIER_LIST with
107 PO_APPROVED_SUPPLIER_LIS_VAL_V and PO_ASL_STATUS_RULES ASR with
108 PO_ASL_STATUS_RULES_V
109 */
110
111 SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
112 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
113 WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
114 AND ASL.vendor_id = x_vendor_id
115 AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
116 AND ASL.item_id = x_item_id
117 AND ASL.asl_status_id = ASR.status_id
118 AND ASR.business_rule = x_action;
119
120 /* Bug: 1968168 Replace PO_APPROVED_SUPPLIER_LIST with
121 PO_APPROVED_SUPPLIER_LIS_VAL_V and PO_ASL_STATUS_RULES ASR with
122 PO_ASL_STATUS_RULES_V
123 */
124 IF x_asl_status IS NULL THEN
125 SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
126 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
127 WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
128 AND ASL.vendor_id = x_vendor_id
129 AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
130 AND ASL.category_id = x_category_id
131 AND ASL.asl_status_id = ASR.status_id
132 AND ASR.business_rule = x_action;
133 END IF;
134
135 /* Bug: 1968168 Replace PO_APPROVED_SUPPLIER_LIST with
136 PO_APPROVED_SUPPLIER_LIS_VAL_V and PO_ASL_STATUS_RULES ASR with
137 PO_ASL_STATUS_RULES_V
138 */
139
140 IF x_asl_status IS NULL THEN
141
142 -- Bug 2072963
143 -- Take out of IN statement to force using index,
144 -- avoiding full-table scan
145 /*
146 SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
147 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
148 WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
149 AND ASL.vendor_id = x_vendor_id
150 AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
151 AND ASL.category_id in (SELECT MIC.category_id
152 FROM MTL_ITEM_CATEGORIES MIC
153 WHERE MIC.inventory_item_id = x_item_id
154 AND MIC.organization_id = x_ship_to_org)
155 AND ASL.asl_status_id = ASR.status_id
156 AND ASR.business_rule = x_action;
157
158 */
159 for v_category in c_category_ids(x_item_id, x_ship_to_org) loop
160
161 SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_s
162 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
163 WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
164 AND ASL.vendor_id = x_vendor_id
165 AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
166 AND ASL.category_id = v_category.category_id
167 AND ASL.asl_status_id = ASR.status_id
168 AND ASR.business_rule = x_action;
169
170 if (x_asl_s is not null) then
171 if (x_asl_status is null) then
172 x_asl_status := x_asl_s;
173 else
174 x_asl_status := x_asl_status + x_asl_s;
175 end if;
176 end if;
177
178 end loop;
179
180 END IF;
181
182 -- If there is a debarred asl then return 0 else return 1. If no asl then
183 -- x_asl_status will be null, return -1
184 IF x_asl_status < 0 THEN
185 RETURN 0;
186 ELSIF x_asl_status IS NOT NULL THEN
187 RETURN 1;
188 ELSE
189 RETURN -1;
190 END IF;
191
192 --dbms_output.put_line('Exiting check_asl_action');
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 -- po_message_s.sql_error('check_asl_action', x_progress, sqlcode);
197 RAISE;
198 end check_asl_action;
199
200
201 procedure update_vendor_status(x_organization_id in number,
202 x_vendor_id in number,
203 x_status in varchar2,
204 x_vendor_site_id in number default null,
205 x_item_id in number default null,
206 x_global_asl_update in varchar2 ,
207 x_org_id in number default null,
208 x_return_code in out NOCOPY varchar2) is
209 x_status_id number;
210
211 -- <ASL ERECORD FPJ START>
212 l_api_name CONSTANT VARCHAR2(30) := 'update_vendor_status';
213 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
214 G_MODULE_PREFIX || l_api_name || '.';
215
216 l_asl_id_tbl PO_TBL_NUMBER;
217 l_return_status VARCHAR2(1);
218 l_progress VARCHAR2(3);
219 l_msg_buf VARCHAR2(2000);
220 l_msg_count NUMBER;
221 l_msg_data VARCHAR2(2000);
222 -- <ASL ERECORD FPJ END>
223
224 begin
225
226 -- <ASL ERECORD FPJ START>
227 IF (g_debug_stmt) THEN
228 PO_DEBUG.debug_begin
229 ( p_log_head => l_module
230 );
231 END IF;
232 -- <ASL ERECORD FPJ END>
233
234 l_progress := '000';
235
236 select status_id
237 into x_status_id
238 from po_asl_statuses
239 where status = x_status;
240
241 if NVL(x_global_asl_update,'N') = 'N' then
242 l_progress := '010';
243
244 update po_approved_supplier_list pasl
245 set pasl.asl_status_id = x_status_id
246 where pasl.using_organization_id = x_organization_id
247 and pasl.vendor_id = x_vendor_id
248 and pasl.vendor_site_id = NVL(x_vendor_site_id,pasl.vendor_site_id)
249 and pasl.item_id = NVL(x_item_id,pasl.item_id)
250 and exists (select null from po_vendor_sites_all pvsa
251 where NVL(pvsa.org_id, -99) =
252 NVL(x_org_id, NVL(pvsa.org_id, -99))
253 and pvsa.vendor_site_id = pasl.vendor_site_id)
254 RETURNING PASL.asl_id -- <ASL ERECORD FPJ>
255 BULK COLLECT INTO l_asl_id_tbl; -- <ASL ERECORD FPJ>
256 else
257 l_progress := '020';
258
259 update po_approved_supplier_list pasl
260 set pasl.asl_status_id = x_status_id
261 where pasl.using_organization_id in (x_organization_id,-1)
262 and pasl.vendor_id = x_vendor_id
263 and pasl.vendor_site_id = NVL(x_vendor_site_id,pasl.vendor_site_id)
264 and pasl.item_id = NVL(x_item_id,pasl.item_id)
265 and exists (select null from po_vendor_sites_all pvsa
266 where NVL(pvsa.org_id, -99) =
267 NVL(x_org_id, NVL(pvsa.org_id, -99))
268 and pvsa.vendor_site_id = pasl.vendor_site_id)
269 RETURNING PASL.asl_id -- <ASL ERECORD FPJ>
270 BULK COLLECT INTO l_asl_id_tbl; -- <ASL ERECORD FPJ>
271
272 end if;
273 if SQL%rowcount = 0 then
274 x_return_code := 'F';
275 else
276 x_return_code := 'S';
277 end if;
278
279 -- <ASL ERECORD FPJ START>
280 FOR i IN 1..l_asl_id_tbl.COUNT LOOP
281
282 l_progress := '030';
283
284 l_return_status := FND_API.G_RET_STS_SUCCESS;
285
286 IF (g_debug_stmt) THEN
287 PO_DEBUG.debug_stmt
288 ( p_log_head => l_module,
289 p_token => l_progress,
290 p_message => 'Call PO_BUSINESSEVENT_PVT.raise_event'
291 );
292 END IF;
293
294 -- Raise ASL Business Event
295 PO_BUSINESSEVENT_PVT.raise_event
296 (
297 p_api_version => 1.0,
298 x_return_status => l_return_status,
299 x_msg_count => l_msg_count,
300 x_msg_data => l_msg_data,
301 p_event_name => 'oracle.apps.po.event.create_asl',
302 p_entity_name => 'ASL',
303 p_entity_id => l_asl_id_tbl(i)
304 );
305
306 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
307 l_progress := '040';
308
309 IF (g_debug_stmt) THEN
310
311 l_msg_buf := FND_MSG_PUB.Get( p_msg_index => 1,
312 p_encoded => 'F');
313
314 l_msg_buf := SUBSTRB('ASL' || l_asl_id_tbl(i) || 'errors out at'
315 || l_progress || l_msg_buf, 1, 2000);
316
317 PO_DEBUG.debug_stmt
318 ( p_log_head => l_module,
319 p_token => l_progress,
320 p_message => l_msg_buf
321 );
322
323 END IF;
324 ELSE
325 IF (g_debug_stmt) THEN
326
327 l_msg_buf := SUBSTRB('ASL' || l_asl_id_tbl(i) ||
328 'raised business event successfully',
329 1, 2000);
330
331 PO_DEBUG.debug_stmt
332 ( p_log_head => l_module,
333 p_token => l_progress,
334 p_message => l_msg_buf
335 );
336 END IF;
337 END IF; -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
338
339 PO_ASL_SV.raise_asl_eres_event
340 ( x_return_status => l_return_status,
341 p_asl_id => l_asl_id_tbl(i),
342 p_action => PO_ASL_SV.G_EVENT_UPDATE,
343 p_calling_from => 'PO_ASL_SV.udpate_vendor_status',
344 p_ackn_note => NULL,
345 p_autonomous_commit => FND_API.G_FALSE
346 );
347
348 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 END IF;
351 END LOOP;
352
353 IF (g_debug_stmt) THEN
354 PO_DEBUG.debug_end
355 ( p_log_head => l_module
356 );
357 END IF;
358
359 -- <ASL ERECORD FPJ END>
360
361 exception
362 when no_data_found then
363 x_return_code := 'F';
364 when others then
365 x_return_code := 'F';
366 raise;
367 end update_vendor_status;
368
369
370
371 /*=============================================================================
372
373 PROCEDURE NAME: get_startup_values()
374
375 ===============================================================================*/
376 procedure get_startup_values(x_current_form_org in number,
377 x_po_item_master_org_id in out NOCOPY number,
378 x_po_category_set_id in out NOCOPY number,
379 x_po_structure_id in out NOCOPY number,
380 x_default_status_id in out NOCOPY number,
381 x_default_status in out NOCOPY varchar2,
382 x_default_business_code in out NOCOPY varchar2,
383 x_default_business in out NOCOPY varchar2,
384 x_chv_install in out NOCOPY varchar2,
385 x_chv_cum_flag in out NOCOPY varchar2) is
386
387 x_progress varchar2(3) := '010';
388 dummy number;
389
390 begin
391
392 x_chv_cum_flag := 'N';
393
394 po_core_s.get_item_category_structure(x_po_category_set_id,
395 x_po_structure_id);
396
397 x_progress := '020';
398
399 x_chv_install := po_core_s.get_product_install_status('CHV');
400
401 /* If Supplier Scheduling is installed, check to see whether we have
402 ** an open CUM period for the current form organization.
403 */
404
405 if (x_chv_install = 'I') then
406
407 x_progress := '021';
408
409 SELECT count(1)
410 INTO dummy
411 FROM chv_cum_periods
412 WHERE organization_id = x_current_form_org
413 AND sysdate between cum_period_start_date and
414 cum_period_end_date;
415
416 if (dummy >= 1) then
417 x_chv_cum_flag := 'Y';
418 end if;
419 end if;
420
421 x_progress := '030';
422
423 SELECT past.status_id,
424 past.status,
425 plc.lookup_code,
426 plc.displayed_field,
427 fsp.inventory_organization_id
428 INTO x_default_status_id,
429 x_default_status,
430 x_default_business_code,
431 x_default_business,
432 x_po_item_master_org_id
433 FROM po_asl_statuses past,
434 po_lookup_codes plc,
435 financials_system_parameters fsp
436 WHERE past.asl_default_flag = 'Y'
437 AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
438 AND plc.lookup_code = 'DIRECT';
439
440 exception
441 when others then
442 --dbms_output.put_line(x_progress);
443 po_message_s.sql_error('get_startup_values', x_progress, sqlcode);
444 raise;
445 end get_startup_values;
446
447 /*=============================================================================
448
449 FUNCTION NAME: check_record_unique
450
451 =============================================================================*/
452 function check_record_unique(x_manufacturer_id number,
453 x_vendor_id number,
454 x_vendor_site_id number,
455 x_item_id number,
456 x_category_id number,
457 x_using_organization_id number) return boolean is
458
459 x_record_unique BOOLEAN;
460 x_dummy_count NUMBER := 0;
461 x_dummy_count_local_attr NUMBER := 0;
462
466 -- record contains the same supplier, using_org
463 begin
464
465 -- Check that the record is unique (i.e., no other
467 -- and item/commodity).
468
469 if (x_manufacturer_id is not null) then
470
471 /*Bug 1261392
472 A performance issue and to use the index on item id in
473 po_approved_supplier_list split one query into two as below
474 based on the value of x_item_id.
475 */
476
477 -- bug3648471
478 -- item_id and category_id are mutually exclusive fields. Therefore
479 -- when item_id is not null we can assume that item_id is null, and vice versa.
480 -- Thus changing the query to skip unnecessary checks for performance
481 -- reasons.
482
483 if (x_item_id is not null) then
484
485 SELECT count(1)
486 INTO x_dummy_count
487 FROM po_approved_supplier_list pasl
488 WHERE pasl.manufacturer_id = x_manufacturer_id
489 AND pasl.using_organization_id = x_using_organization_id
490 AND pasl.item_id = x_item_id;
491 else
492
493 SELECT count(1)
494 INTO x_dummy_count
495 FROM po_approved_supplier_list pasl
496 WHERE pasl.manufacturer_id = x_manufacturer_id
497 AND pasl.using_organization_id = x_using_organization_id
498 AND pasl.category_id = x_category_id;
499
500 end if;
501 else
502
503 /*Bug 1261392
504 A performance issue and to use the index on item id in
505 po_approved_supplier_list split one query into two as below
506 based on the value of x_item_id.
507 */
508 if (x_item_id is not null) then
509
510 SELECT count(1)
511 INTO x_dummy_count
512 FROM po_approved_supplier_list pasl
513 WHERE pasl.vendor_id = x_vendor_id
514 AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
515 (pasl.vendor_site_id = x_vendor_site_id))
516 AND pasl.using_organization_id = x_using_organization_id
517 AND pasl.item_id = x_item_id;
518 else
519
520 SELECT count(1)
521 INTO x_dummy_count
522 FROM po_approved_supplier_list pasl
523 WHERE pasl.vendor_id = x_vendor_id
524 AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
525 (pasl.vendor_site_id = x_vendor_site_id))
526 AND pasl.using_organization_id = x_using_organization_id
527 AND pasl.category_id = x_category_id;
528
529 end if;
530
531 -- if this is not a global entry check to see if local
532 -- attributes exist for a global entry for that vendor/item/using org
533
534 if (x_using_organization_id <> -1) then
535 SELECT count(1)
536 INTO x_dummy_count_local_attr
537 FROM po_approved_supplier_list pasl,po_asl_attributes paa
538 WHERE pasl.vendor_id = x_vendor_id
539 AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
540 (pasl.vendor_site_id = x_vendor_site_id))
541 AND pasl.using_organization_id = -1
542 AND paa.using_organization_id = x_using_organization_id
543 AND pasl.asl_id = paa.asl_id
544 AND ((pasl.item_id is null AND x_item_id is null) OR
545 (pasl.item_id = x_item_id))
546 AND ((pasl.category_id is null AND x_category_id is null) OR
547 (pasl.category_id = x_category_id));
548
549 end if;
550 end if;
551
552 if (x_dummy_count >= 1 or x_dummy_count_local_attr >= 1) then
553 return FALSE;
554 else
555 return TRUE;
556 end if;
557
558 exception
559 when others then
560 raise;
561 end;
562
563 -- <ASL ERECORD FPJ START>
564
565
566 -----------------------------------------------------------------------
567 --Start of Comments
568 --Name: raise_asl_eres_event
569 --Pre-reqs:
570 --Modifies:
571 --Locks:
572 -- None
573 --Function: Call QA API to raise a ERES business event, which creates an
574 -- eRecord for the ASL. This procedure will also acknowledge
575 -- the eRecord that is created
576 --Parameters:
577 --IN:
578 --p_asl_id
579 -- primary key of the ASL
580 --p_action
581 -- Type of action done to the ASL
582 -- PO_ASL_SV.G_EVENT_INSERT: ASL is inserted
583 -- PO_ASL_SV.G_EVENT_UPDATE: ASL is updated
584 --p_calling_from
585 -- Identifier of the caller
586 --p_ackn_note
587 -- Note for the acknowledge
588 --p_autonomous_commit
589 -- Whehter the acknowledge should be performed as an autonomous transaction
590 --IN OUT:
591 --OUT:
592 --x_return_status
593 -- status of the procedure
594 --Returns:
595 --Notes:
596 --Testing:
597 --End of Comments
598 ------------------------------------------------------------------------
599 PROCEDURE raise_asl_eres_event
600 ( x_return_status OUT NOCOPY VARCHAR2,
601 p_asl_id IN NUMBER,
602 p_action IN VARCHAR2,
603 p_calling_from IN VARCHAR2,
607
604 p_ackn_note IN VARCHAR2,
605 p_autonomous_commit IN VARCHAR2
606 ) IS
608 l_api_name CONSTANT VARCHAR2(30) := 'raise_asl_eres_event';
609 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
610 G_MODULE_PREFIX || l_api_name || '.';
611
612 l_progress VARCHAR2(3);
613
614 l_child_erecords QA_EDR_STANDARD.ERECORD_ID_TBL_TYPE;
615 l_event QA_EDR_STANDARD.ERES_EVENT_REC_TYPE;
616 l_erecord_id NUMBER;
617 l_event_status VARCHAR2(20);
618 l_return_status VARCHAR2(1);
619 l_msg_count NUMBER;
620 l_msg_data VARCHAR2(2000);
621
622 l_subroutine VARCHAR2(100);
623
624 l_identifier FND_NEW_MESSAGES.message_text%TYPE;
625 l_identifier_val VARCHAR2(2000);
626
627 BEGIN
628 l_progress := '000';
629
630 x_return_status := FND_API.G_RET_STS_SUCCESS;
631
632 IF (g_debug_stmt) THEN
633 PO_DEBUG.debug_begin
634 ( p_log_head => l_module
635 );
636 END IF;
637
638 IF (g_eres_enabled <> 'Y') THEN
639 IF (g_debug_stmt) THEN
640 PO_DEBUG.debug_stmt
641 ( p_log_head => l_module,
642 p_token => l_progress,
643 p_message => 'g_eres_enabled is ' || g_eres_enabled ||
644 'Quitting procedure raise_asl_eres_event'
645 );
646 END IF;
647
648 RETURN;
649 END IF;
650
651 get_identifier
652 ( p_asl_id => p_asl_id,
653 x_identifier => l_identifier,
654 x_identifier_value => l_identifier_val
655 );
656
657
658 l_event.param_name_1 := 'DEFERRED';
659 l_event.param_value_1 := 'Y';
660
661 l_event.param_name_2 := 'POST_OPERATION_API';
662 l_event.param_value_2 := 'NONE';
663
664 l_event.param_name_3 := 'PSIG_USER_KEY_LABEL';
665 l_event.param_value_3 := l_identifier;
666
667 l_event.param_name_4 := 'PSIG_USER_KEY_VALUE';
668 l_event.param_value_4 := l_identifier_val;
669
670 l_event.param_name_5 := 'PSIG_TRANSACTION_AUDIT_ID';
671 l_event.param_value_5 := '-1';
672
673 l_event.param_name_6 := '#WF_SOURCE_APPLICATION_TYPE';
674 l_event.param_value_6 := 'DB';
675
676 l_event.param_name_7 := '#WF_SIGN_REQUESTER';
677 l_event.param_value_7 := FND_GLOBAL.user_name;
678
679
680 IF (p_action = G_EVENT_INSERT) THEN
681 l_progress := '010';
682 l_event.event_name := 'oracle.apps.po.asl.create';
683 ELSIF (p_action = G_EVENT_UPDATE) THEN
684 l_progress := '020';
685 l_event.event_name := 'oracle.apps.po.asl.update';
686 ELSE
687 l_progress := '030';
688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
689 END IF;
690
691 l_event.event_key := p_asl_id;
692 l_event.event_status := 'SUCCESS';
693
694 IF (g_debug_stmt) THEN
695 PO_DEBUG.debug_var(l_module, l_progress, 'l_event.event_name',
696 l_event.event_name);
697
698 PO_DEBUG.debug_var(l_module, l_progress, 'l_event.event_key',
699 l_event.event_key);
700 END IF;
701
702 -- Call QA API to raise ERES event
703
704 l_progress := '040';
705
706 QA_EDR_STANDARD.raise_eres_event
707 ( p_api_version => 1.0,
708 p_init_msg_list => FND_API.G_TRUE,
709 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
710 x_return_status => l_return_status,
711 x_msg_count => l_msg_count,
712 x_msg_data => l_msg_data,
713 p_child_erecords => l_child_erecords,
714 x_event => l_event
715 );
716
717 IF (g_debug_stmt) THEN
718 PO_DEBUG.debug_stmt
719 ( p_log_head => l_module,
720 p_token => l_progress,
721 p_message => 'Called raise_eres_event. status = ' || l_event.event_status
722 );
723
724
725 PO_DEBUG.debug_var(l_module, l_progress, 'l_event.erecord_id',
726 l_event.erecord_id);
727 END IF;
728
729 --Bug 4745270 dont error incase of 'PENDING' status
730 -- when raise event is called in DB mode, it runs asynchronously
731 -- and returns with pending status
732 IF (l_event.event_status NOT IN ('SUCCESS', 'NOACTION','PENDING')) THEN
733 l_progress := '050';
734 l_subroutine := 'QA_EDR_STANDARD.raise_eres_event';
735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
736 END IF;
737
738 IF (l_event.erecord_id > 0) THEN
739 l_progress := '060';
740
741 QA_EDR_STANDARD.send_ackn
742 ( p_api_version => 1.0,
743 p_init_msg_list => FND_API.G_TRUE,
744 x_return_status => l_return_status,
745 x_msg_count => l_msg_count,
746 x_msg_data => l_msg_data,
747 p_event_name => l_event.event_name,
748 p_event_key => l_event.event_key,
749 p_erecord_id => l_event.erecord_id,
750 p_trans_status => 'SUCCESS',
754 );
751 p_ackn_by => p_calling_from,
752 p_ackn_note => p_ackn_note,
753 p_autonomous_commit => p_autonomous_commit
755
756 IF (g_debug_stmt) THEN
757 PO_DEBUG.debug_stmt
758 ( p_log_head => l_module,
759 p_token => l_progress,
760 p_message => 'Called send_ackn. status = ' ||
761 l_event.event_status
762 );
763
764 END IF;
765
766 --Bug 4745270 dont error incase of 'PENDING' status
767 IF (l_event.event_status NOT IN ('SUCCESS', 'NOACTION','PENDING')) THEN
768 l_progress := '070';
769 l_subroutine := 'QA_EDR_STANDARD.send_ackn';
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END IF;
772 END IF;
773
774 IF (g_debug_stmt) THEN
775 PO_DEBUG.debug_end
776 ( p_log_head => l_module
777 );
778 END IF;
779
780 EXCEPTION
781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
783
784 IF (g_debug_unexp) THEN
785 PO_DEBUG.debug_exc
786 ( p_log_head => l_module,
787 p_progress => l_progress
788 );
789
790
791 IF (l_msg_count IS NOT NULL) THEN
792 FOR i IN 1..l_msg_count LOOP
793 l_msg_data := FND_MSG_PUB.get
794 ( p_msg_index => i,
795 p_encoded => 'F');
796 PO_DEBUG.debug_stmt
797 ( p_log_head => l_module,
798 p_token => l_progress,
799 p_message => l_msg_data);
800 END LOOP;
801 END IF;
802 END IF;
803
804 IF (FND_MSG_PUB.G_FIRST IS NOT NULL) THEN
805 l_msg_data := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_FIRST,
806 p_encoded => 'F');
807 END IF;
808
809 FND_MESSAGE.set_name ('PO', 'PO_ALL_TRACE_ERROR_WITH_MSG');
810 FND_MESSAGE.set_token ('FILE', l_api_name);
811 FND_MESSAGE.set_token ('ERR_NUMBER', l_progress);
812 FND_MESSAGE.set_token ('SUBROUTINE', l_subroutine);
813 FND_MESSAGE.set_token ('ERROR_MSG', l_msg_data);
814
815 WHEN OTHERS THEN
816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
817
818 IF (g_debug_unexp) THEN
819 PO_DEBUG.debug_exc
820 ( p_log_head => l_module,
821 p_progress => l_progress
822 );
823 END IF;
824
825 FND_MSG_PUB.build_exc_msg
826 ( p_pkg_name => g_pkg_name,
827 p_procedure_name => l_api_name
828 );
829
830 END raise_asl_eres_event;
831
832
833 -----------------------------------------------------------------------
834 --Start of Comments
835 --Name: get_identifier
836 --Pre-reqs:
837 --Modifies:
838 --Locks:
839 -- None
840 --Function: Get identifier and identifier value for display purpose on
841 -- an ASL eRecord
842 --Parameters:
843 --IN:
844 --p_asl_id
845 -- primary key of the ASL
846 --IN OUT:
847 --OUT:
848 --x_identifier
849 -- identifier for ASL eRecord. It's a description what identifier_value
850 -- contains
851 --x_identigier_value
852 -- a string to identify an ASL
853 --Returns:
854 --Notes:
855 --Testing:
856 --End of Comments
857 ------------------------------------------------------------------------
858
859 PROCEDURE get_identifier
860 ( p_asl_id IN NUMBER,
861 x_identifier OUT NOCOPY VARCHAR2,
862 x_identifier_value OUT NOCOPY VARCHAR2
863 ) IS
864
865 l_api_name CONSTANT VARCHAR2(30) := 'get_identifier';
866 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
867 G_MODULE_PREFIX || l_api_name || '.';
868 l_progress VARCHAR2(3);
869
870 l_using_org_id PO_APPROVED_SUPPLIER_LIST.using_organization_id%TYPE;
871 l_organization VARCHAR2(30);
872 l_item_id PO_APPROVED_SUPPLIER_LIST.item_id%TYPE;
873 l_vendor_name PO_VENDORS.vendor_name%TYPE;
874 l_vendor_site_code PO_VENDOR_SITES_ALL.vendor_site_code%TYPE;
875 l_category_name MTL_CATEGORIES_KFV.concatenated_segments%TYPE;
876 l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
877
878 BEGIN
879 l_progress := '000';
880
881 IF (g_debug_stmt) THEN
882 PO_DEBUG.debug_begin
883 ( p_log_head => l_module
884 );
885 END IF;
886
887 x_identifier := FND_MESSAGE.get_string('PO', 'PO_ASL_EREC_IDENTIFIER');
888
889 SELECT PASL.using_organization_id,
890 MP.organization_code,
891 PASL.item_id,
892 DECODE (PASL.vendor_business_type,
893 'MANUFACTURER', MM.manufacturer_name,
894 PV.vendor_name),
895 PVS.vendor_site_code,
896 MC.concatenated_segments,
897 MSI.concatenated_segments
898 INTO l_using_org_id,
902 l_vendor_site_code,
899 l_organization,
900 l_item_id,
901 l_vendor_name,
903 l_category_name,
904 l_item_name
905 FROM po_approved_supplier_list PASL,
906 mtl_parameters MP,
907 mtl_manufacturers MM,
908 po_vendors PV,
909 po_vendor_sites_all PVS,
910 mtl_system_items_kfv MSI,
911 mtl_categories_kfv MC
912 WHERE PASL.asl_id = p_asl_id
913 AND PASL.using_organization_id = MP.organization_id (+)
914 AND PASL.manufacturer_id = MM.manufacturer_id (+)
915 AND PASL.vendor_id = PV.vendor_id (+)
916 AND PASL.vendor_site_id = PVS.vendor_site_id (+)
917 AND PASL.item_id = MSI.inventory_item_id (+)
918 AND PASL.owning_organization_id = NVL(MSI.organization_id,
919 PASL.owning_organization_id)
920 AND PASL.category_id = MC.category_id (+);
921
922 x_identifier_value := ' - ' || l_vendor_name || ' - ' ||
923 l_vendor_site_code || ' - ';
924
925 IF ( l_using_org_id = -1 ) THEN
926 l_organization := FND_MESSAGE.get_string('PO', 'PO_ASL_GLOBAL');
927 END IF;
928
929 IF ( l_item_id IS NOT NULL) THEN
930 x_identifier_value := l_organization || x_identifier_value ||
931 l_item_name;
932 ELSE
933 x_identifier_value := l_organization || x_identifier_value ||
934 l_category_name;
935 END IF;
936
937 IF (g_debug_stmt) THEN
938 PO_DEBUG.debug_end
939 ( p_log_head => l_module
940 );
941 END IF;
942
943
944 EXCEPTION
945 WHEN OTHERS THEN
946 IF (g_debug_unexp) THEN
947 PO_DEBUG.debug_exc
948 ( p_log_head => l_module,
949 p_progress => l_progress
950 );
951 END IF;
952
953 FND_MSG_PUB.build_exc_msg
954 ( p_pkg_name => g_pkg_name,
955 p_procedure_name => l_api_name
956 );
957
958 RAISE;
959
960 END get_identifier;
961
962
963
964 -----------------------------------------------------------------------
965 --Start of Comments
966 --Name: init_asl_activity_tbl
967 --Pre-reqs:
968 --Modifies:
969 --Locks:
970 -- None
971 --Function: Initialize g_asl_activities table, which is used to store
972 -- the ASL changes (Insert or Update) happening in the current
973 -- commit cycle. We need these data for calling ERES event
974 -- right before commit happens.
975 --Parameters:
976 --IN:
977 --IN OUT:
978 --OUT:
979 --Returns:
980 --Notes:
981 --Testing:
982 --End of Comments
983 ------------------------------------------------------------------------
984
985 PROCEDURE init_asl_activity_tbl IS
986
987 l_api_name CONSTANT VARCHAR2(30) := 'init_asl_activity_tbl';
988 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
989 G_MODULE_PREFIX || l_api_name || '.';
990 l_progress VARCHAR2(3);
991 l_return_status VARCHAR2(1);
992
993 BEGIN
994
995 l_progress := '000';
996
997 IF (g_debug_stmt) THEN
998 PO_DEBUG.debug_begin
999 ( p_log_head => l_module
1000 );
1001 END IF;
1002
1003 g_asl_activities.delete;
1004 g_asl_activities_index := 0;
1005
1006
1007 IF (g_debug_stmt) THEN
1008 PO_DEBUG.debug_end
1009 ( p_log_head => l_module
1010 );
1011 END IF;
1012
1013
1014 EXCEPTION
1015 WHEN OTHERS THEN
1016 IF (g_debug_unexp) THEN
1017 PO_DEBUG.debug_exc
1018 ( p_log_head => l_module,
1019 p_progress => l_progress
1020 );
1021 END IF;
1022
1023 FND_MSG_PUB.build_exc_msg
1024 ( p_pkg_name => g_pkg_name,
1025 p_procedure_name => l_api_name
1026 );
1027
1028 APP_EXCEPTION.raise_exception;
1029
1030 END init_asl_activity_tbl;
1031
1032 -----------------------------------------------------------------------
1033 --Start of Comments
1034 --Name: add_asl_activity
1035 --Pre-reqs:
1036 --Modifies:
1037 --Locks:
1038 -- None
1039 --Function: Populate a row to g_asl_activities table to record a ASL
1040 -- change (insert or update)
1041 --Parameters:
1042 --IN:
1043 --p_asl_id
1044 -- Unique identifier for the ASL
1045 --p_action
1046 -- Type of action for this ASL ('INSERT' or 'UPDATE')
1047 --IN OUT:
1048 --OUT:
1049 --Returns:
1050 --Notes:
1051 --Testing:
1052 --End of Comments
1053 ------------------------------------------------------------------------
1054
1055 PROCEDURE add_asl_activity
1056 ( p_asl_id IN NUMBER,
1057 p_action IN VARCHAR2
1058 ) IS
1059
1060
1061 l_api_name CONSTANT VARCHAR2(30) := 'add_asl_activity';
1062 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1063 G_MODULE_PREFIX || l_api_name || '.';
1064 l_progress VARCHAR2(3);
1065 l_return_status VARCHAR2(1);
1066
1067 l_asl_activity asl_activity_rec;
1068
1069 BEGIN
1070
1071 IF (g_debug_stmt) THEN
1072 PO_DEBUG.debug_begin
1073 ( p_log_head => l_module
1074 );
1075 END IF;
1076
1077 g_asl_activities_index := g_asl_activities_index + 1;
1078 g_asl_activities(g_asl_activities_index).asl_id := p_asl_id;
1079 g_asl_activities(g_asl_activities_index).action := p_action;
1080
1081
1082 IF (g_debug_stmt) THEN
1083 PO_DEBUG.debug_end
1084 ( p_log_head => l_module
1085 );
1086 END IF;
1087
1088 EXCEPTION
1089 WHEN OTHERS THEN
1090 IF (g_debug_unexp) THEN
1091 PO_DEBUG.debug_exc
1092 ( p_log_head => l_module,
1093 p_progress => l_progress
1094 );
1095 END IF;
1096
1097 FND_MSG_PUB.build_exc_msg
1098 ( p_pkg_name => g_pkg_name,
1099 p_procedure_name => l_api_name
1100 );
1101
1102 APP_EXCEPTION.raise_exception;
1103
1104 END add_asl_activity;
1105
1106 -----------------------------------------------------------------------
1107 --Start of Comments
1108 --Name: process_asl_activity_tbl
1109 --Pre-reqs:
1110 --Modifies:
1111 --Locks:
1112 -- None
1113 --Function: Loop through each of the record in g_asl_activity and
1114 -- raise an ERES event. The event type will be based on
1115 -- the action column in the record.
1116 --Parameters:
1117 --IN:
1118 --IN OUT:
1119 --OUT:
1120 --Returns:
1121 --Notes:
1122 --Testing:
1123 --End of Comments
1124 ------------------------------------------------------------------------
1125
1126 PROCEDURE process_asl_activity_tbl IS
1127
1128 l_api_name CONSTANT VARCHAR2(30) := 'process_asl_activity_tbl';
1129 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1130 G_MODULE_PREFIX || l_api_name || '.';
1131 l_progress VARCHAR2(3);
1132 l_return_status VARCHAR2(1);
1133
1134 BEGIN
1135
1136 IF (g_debug_stmt) THEN
1137 PO_DEBUG.debug_begin
1138 ( p_log_head => l_module
1139 );
1140 END IF;
1141
1142 l_progress := '010';
1143
1144 FOR i IN 1..g_asl_activities_index LOOP
1145 l_progress := '020';
1146
1147
1148 IF (g_debug_stmt) THEN
1149 PO_DEBUG.debug_stmt
1150 ( p_log_head => l_module,
1151 p_token => l_progress,
1152 p_message => 'Processing asl_id= ' || g_asl_activities(i).asl_id
1153 || ', action= ' || g_asl_activities(i).action
1154 );
1155 END IF;
1156
1157 PO_ASL_SV.raise_asl_eres_event
1158 ( x_return_status => l_return_status,
1159 p_asl_id => g_asl_activities(i).asl_id,
1160 p_action => g_asl_activities(i).action,
1161 p_calling_from => 'PO_ASL_SV.process_asl_activity_tbl',
1162 p_ackn_note => NULL,
1163 p_autonomous_commit => FND_API.G_FALSE
1164 );
1165
1166 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1167 l_progress := '030';
1168
1169
1170 IF (g_debug_stmt) THEN
1171 PO_DEBUG.debug_stmt
1172 ( p_log_head => l_module,
1173 p_token => l_progress,
1174 p_message => 'PO_ASL_SV.raise_asl_eres_event failed ' ||
1175 'with status ' || l_return_status
1176 );
1177 END IF;
1178
1179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1180 END IF;
1181 END LOOP;
1182
1183 -- bug3539651
1184 -- clean up pl/sql table once all the rows are processed
1185 init_asl_activity_tbl;
1186
1187 IF (g_debug_stmt) THEN
1188 PO_DEBUG.debug_end
1189 ( p_log_head => l_module
1190 );
1191 END IF;
1192
1193
1194 EXCEPTION
1195 WHEN OTHERS THEN
1196
1197 IF (g_debug_unexp) THEN
1198 PO_DEBUG.debug_exc
1199 ( p_log_head => l_module,
1200 p_progress => l_progress
1201 );
1202 END IF;
1203
1204 APP_EXCEPTION.raise_exception;
1205
1206 END process_asl_activity_tbl;
1207
1208
1209 -- <ASL ERECORD FPJ END>
1210
1211 END PO_ASL_SV;