[Home] [Help]
PACKAGE BODY: APPS.PO_ASL_SV
Source
1 PACKAGE BODY PO_ASL_SV as
2 /* $Header: POXA1LSB.pls 120.2 2010/12/29 22:05:46 yawang ship $ */
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 --Bug10229244, when access mode is 'M', the fsp synonym contains
424 --multiple rows, caused the sql raise exception
425 --Move the logic of getting fsp.inventory_organization_id to the
426 --calling procedure
427 SELECT past.status_id,
428 past.status,
429 plc.lookup_code,
430 plc.displayed_field
431 --fsp.inventory_organization_id
432 INTO x_default_status_id,
433 x_default_status,
434 x_default_business_code,
435 x_default_business
436 --x_po_item_master_org_id
437 FROM po_asl_statuses past,
438 po_lookup_codes plc
439 --financials_system_parameters fsp
440 WHERE past.asl_default_flag = 'Y'
441 AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
442 AND plc.lookup_code = 'DIRECT';
443
444 exception
445 when others then
446 --dbms_output.put_line(x_progress);
447 po_message_s.sql_error('get_startup_values', x_progress, sqlcode);
448 raise;
449 end get_startup_values;
450
451 /*=============================================================================
452
453 FUNCTION NAME: check_record_unique
454
455 =============================================================================*/
456 function check_record_unique(x_manufacturer_id number,
457 x_vendor_id number,
458 x_vendor_site_id number,
459 x_item_id number,
460 x_category_id number,
461 x_using_organization_id number) return boolean is
462
463 x_record_unique BOOLEAN;
464 x_dummy_count NUMBER := 0;
465 x_dummy_count_local_attr NUMBER := 0;
466
467 begin
468
469 -- Check that the record is unique (i.e., no other
470 -- record contains the same supplier, using_org
471 -- and item/commodity).
472
473 if (x_manufacturer_id is not null) then
474
475 /*Bug 1261392
476 A performance issue and to use the index on item id in
477 po_approved_supplier_list split one query into two as below
478 based on the value of x_item_id.
479 */
480
481 -- bug3648471
482 -- item_id and category_id are mutually exclusive fields. Therefore
483 -- when item_id is not null we can assume that item_id is null, and vice versa.
484 -- Thus changing the query to skip unnecessary checks for performance
485 -- reasons.
486
487 if (x_item_id is not null) then
488
489 SELECT count(1)
490 INTO x_dummy_count
491 FROM po_approved_supplier_list pasl
492 WHERE pasl.manufacturer_id = x_manufacturer_id
493 AND pasl.using_organization_id = x_using_organization_id
494 AND pasl.item_id = x_item_id;
495 else
496
497 SELECT count(1)
498 INTO x_dummy_count
499 FROM po_approved_supplier_list pasl
500 WHERE pasl.manufacturer_id = x_manufacturer_id
501 AND pasl.using_organization_id = x_using_organization_id
502 AND pasl.category_id = x_category_id;
503
504 end if;
505 else
506
507 /*Bug 1261392
508 A performance issue and to use the index on item id in
509 po_approved_supplier_list split one query into two as below
510 based on the value of x_item_id.
511 */
512 if (x_item_id is not null) then
513
514 SELECT count(1)
515 INTO x_dummy_count
516 FROM po_approved_supplier_list pasl
517 WHERE pasl.vendor_id = x_vendor_id
518 AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
519 (pasl.vendor_site_id = x_vendor_site_id))
520 AND pasl.using_organization_id = x_using_organization_id
521 AND pasl.item_id = x_item_id;
522 else
523
524 SELECT count(1)
525 INTO x_dummy_count
526 FROM po_approved_supplier_list pasl
527 WHERE pasl.vendor_id = x_vendor_id
528 AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
529 (pasl.vendor_site_id = x_vendor_site_id))
530 AND pasl.using_organization_id = x_using_organization_id
531 AND pasl.category_id = x_category_id;
532
533 end if;
534
535 -- if this is not a global entry check to see if local
536 -- attributes exist for a global entry for that vendor/item/using org
537
538 if (x_using_organization_id <> -1) then
539 SELECT count(1)
540 INTO x_dummy_count_local_attr
541 FROM po_approved_supplier_list pasl,po_asl_attributes paa
542 WHERE pasl.vendor_id = x_vendor_id
543 AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
544 (pasl.vendor_site_id = x_vendor_site_id))
545 AND pasl.using_organization_id = -1
546 AND paa.using_organization_id = x_using_organization_id
547 AND pasl.asl_id = paa.asl_id
548 AND ((pasl.item_id is null AND x_item_id is null) OR
549 (pasl.item_id = x_item_id))
550 AND ((pasl.category_id is null AND x_category_id is null) OR
551 (pasl.category_id = x_category_id));
552
553 end if;
554 end if;
555
556 if (x_dummy_count >= 1 or x_dummy_count_local_attr >= 1) then
557 return FALSE;
558 else
559 return TRUE;
560 end if;
561
562 exception
563 when others then
564 raise;
565 end;
566
567 -- <ASL ERECORD FPJ START>
568
569
570 -----------------------------------------------------------------------
571 --Start of Comments
572 --Name: raise_asl_eres_event
573 --Pre-reqs:
574 --Modifies:
575 --Locks:
576 -- None
577 --Function: Call QA API to raise a ERES business event, which creates an
578 -- eRecord for the ASL. This procedure will also acknowledge
579 -- the eRecord that is created
580 --Parameters:
581 --IN:
582 --p_asl_id
583 -- primary key of the ASL
584 --p_action
585 -- Type of action done to the ASL
586 -- PO_ASL_SV.G_EVENT_INSERT: ASL is inserted
587 -- PO_ASL_SV.G_EVENT_UPDATE: ASL is updated
588 --p_calling_from
589 -- Identifier of the caller
590 --p_ackn_note
591 -- Note for the acknowledge
592 --p_autonomous_commit
593 -- Whehter the acknowledge should be performed as an autonomous transaction
594 --IN OUT:
595 --OUT:
596 --x_return_status
597 -- status of the procedure
598 --Returns:
599 --Notes:
600 --Testing:
601 --End of Comments
602 ------------------------------------------------------------------------
603 PROCEDURE raise_asl_eres_event
604 ( x_return_status OUT NOCOPY VARCHAR2,
605 p_asl_id IN NUMBER,
606 p_action IN VARCHAR2,
607 p_calling_from IN VARCHAR2,
608 p_ackn_note IN VARCHAR2,
609 p_autonomous_commit IN VARCHAR2
610 ) IS
611
612 l_api_name CONSTANT VARCHAR2(30) := 'raise_asl_eres_event';
613 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
614 G_MODULE_PREFIX || l_api_name || '.';
615
616 l_progress VARCHAR2(3);
617
618 l_child_erecords QA_EDR_STANDARD.ERECORD_ID_TBL_TYPE;
619 l_event QA_EDR_STANDARD.ERES_EVENT_REC_TYPE;
620 l_erecord_id NUMBER;
621 l_event_status VARCHAR2(20);
622 l_return_status VARCHAR2(1);
623 l_msg_count NUMBER;
624 l_msg_data VARCHAR2(2000);
625
626 l_subroutine VARCHAR2(100);
627
628 l_identifier FND_NEW_MESSAGES.message_text%TYPE;
629 l_identifier_val VARCHAR2(2000);
630
631 BEGIN
632 l_progress := '000';
633
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 IF (g_debug_stmt) THEN
637 PO_DEBUG.debug_begin
638 ( p_log_head => l_module
639 );
640 END IF;
641
642 IF (g_eres_enabled <> 'Y') THEN
643 IF (g_debug_stmt) THEN
644 PO_DEBUG.debug_stmt
645 ( p_log_head => l_module,
646 p_token => l_progress,
647 p_message => 'g_eres_enabled is ' || g_eres_enabled ||
648 'Quitting procedure raise_asl_eres_event'
649 );
650 END IF;
651
652 RETURN;
653 END IF;
654
655 get_identifier
656 ( p_asl_id => p_asl_id,
657 x_identifier => l_identifier,
658 x_identifier_value => l_identifier_val
659 );
660
661
662 l_event.param_name_1 := 'DEFERRED';
663 l_event.param_value_1 := 'Y';
664
665 l_event.param_name_2 := 'POST_OPERATION_API';
666 l_event.param_value_2 := 'NONE';
667
668 l_event.param_name_3 := 'PSIG_USER_KEY_LABEL';
669 l_event.param_value_3 := l_identifier;
670
671 l_event.param_name_4 := 'PSIG_USER_KEY_VALUE';
672 l_event.param_value_4 := l_identifier_val;
673
674 l_event.param_name_5 := 'PSIG_TRANSACTION_AUDIT_ID';
675 l_event.param_value_5 := '-1';
676
677 l_event.param_name_6 := '#WF_SOURCE_APPLICATION_TYPE';
678 l_event.param_value_6 := 'DB';
679
680 l_event.param_name_7 := '#WF_SIGN_REQUESTER';
681 l_event.param_value_7 := FND_GLOBAL.user_name;
682
683
684 IF (p_action = G_EVENT_INSERT) THEN
685 l_progress := '010';
686 l_event.event_name := 'oracle.apps.po.asl.create';
687 ELSIF (p_action = G_EVENT_UPDATE) THEN
688 l_progress := '020';
689 l_event.event_name := 'oracle.apps.po.asl.update';
690 ELSE
691 l_progress := '030';
692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
693 END IF;
694
695 l_event.event_key := p_asl_id;
696 l_event.event_status := 'SUCCESS';
697
698 IF (g_debug_stmt) THEN
699 PO_DEBUG.debug_var(l_module, l_progress, 'l_event.event_name',
700 l_event.event_name);
701
702 PO_DEBUG.debug_var(l_module, l_progress, 'l_event.event_key',
703 l_event.event_key);
704 END IF;
705
706 -- Call QA API to raise ERES event
707
708 l_progress := '040';
709
710 QA_EDR_STANDARD.raise_eres_event
711 ( p_api_version => 1.0,
712 p_init_msg_list => FND_API.G_TRUE,
713 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
714 x_return_status => l_return_status,
715 x_msg_count => l_msg_count,
716 x_msg_data => l_msg_data,
717 p_child_erecords => l_child_erecords,
718 x_event => l_event
719 );
720
721 IF (g_debug_stmt) THEN
722 PO_DEBUG.debug_stmt
723 ( p_log_head => l_module,
724 p_token => l_progress,
725 p_message => 'Called raise_eres_event. status = ' || l_event.event_status
726 );
727
728
729 PO_DEBUG.debug_var(l_module, l_progress, 'l_event.erecord_id',
730 l_event.erecord_id);
731 END IF;
732
733 --Bug 4745270 dont error incase of 'PENDING' status
734 -- when raise event is called in DB mode, it runs asynchronously
735 -- and returns with pending status
736 IF (l_event.event_status NOT IN ('SUCCESS', 'NOACTION','PENDING')) THEN
737 l_progress := '050';
738 l_subroutine := 'QA_EDR_STANDARD.raise_eres_event';
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END IF;
741
742 IF (l_event.erecord_id > 0) THEN
743 l_progress := '060';
744
745 QA_EDR_STANDARD.send_ackn
746 ( p_api_version => 1.0,
747 p_init_msg_list => FND_API.G_TRUE,
748 x_return_status => l_return_status,
749 x_msg_count => l_msg_count,
750 x_msg_data => l_msg_data,
751 p_event_name => l_event.event_name,
752 p_event_key => l_event.event_key,
753 p_erecord_id => l_event.erecord_id,
754 p_trans_status => 'SUCCESS',
755 p_ackn_by => p_calling_from,
756 p_ackn_note => p_ackn_note,
757 p_autonomous_commit => p_autonomous_commit
758 );
759
760 IF (g_debug_stmt) THEN
761 PO_DEBUG.debug_stmt
762 ( p_log_head => l_module,
763 p_token => l_progress,
764 p_message => 'Called send_ackn. status = ' ||
765 l_event.event_status
766 );
767
768 END IF;
769
770 --Bug 4745270 dont error incase of 'PENDING' status
771 IF (l_event.event_status NOT IN ('SUCCESS', 'NOACTION','PENDING')) THEN
772 l_progress := '070';
773 l_subroutine := 'QA_EDR_STANDARD.send_ackn';
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
775 END IF;
776 END IF;
777
778 IF (g_debug_stmt) THEN
779 PO_DEBUG.debug_end
780 ( p_log_head => l_module
781 );
782 END IF;
783
784 EXCEPTION
785 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787
788 IF (g_debug_unexp) THEN
789 PO_DEBUG.debug_exc
790 ( p_log_head => l_module,
791 p_progress => l_progress
792 );
793
794
795 IF (l_msg_count IS NOT NULL) THEN
796 FOR i IN 1..l_msg_count LOOP
797 l_msg_data := FND_MSG_PUB.get
798 ( p_msg_index => i,
799 p_encoded => 'F');
800 PO_DEBUG.debug_stmt
801 ( p_log_head => l_module,
802 p_token => l_progress,
803 p_message => l_msg_data);
804 END LOOP;
805 END IF;
806 END IF;
807
808 IF (FND_MSG_PUB.G_FIRST IS NOT NULL) THEN
809 l_msg_data := FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_FIRST,
810 p_encoded => 'F');
811 END IF;
812
813 FND_MESSAGE.set_name ('PO', 'PO_ALL_TRACE_ERROR_WITH_MSG');
814 FND_MESSAGE.set_token ('FILE', l_api_name);
815 FND_MESSAGE.set_token ('ERR_NUMBER', l_progress);
816 FND_MESSAGE.set_token ('SUBROUTINE', l_subroutine);
817 FND_MESSAGE.set_token ('ERROR_MSG', l_msg_data);
818
819 WHEN OTHERS THEN
820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
821
822 IF (g_debug_unexp) THEN
823 PO_DEBUG.debug_exc
824 ( p_log_head => l_module,
825 p_progress => l_progress
826 );
827 END IF;
828
829 FND_MSG_PUB.build_exc_msg
830 ( p_pkg_name => g_pkg_name,
831 p_procedure_name => l_api_name
832 );
833
834 END raise_asl_eres_event;
835
836
837 -----------------------------------------------------------------------
838 --Start of Comments
839 --Name: get_identifier
840 --Pre-reqs:
841 --Modifies:
842 --Locks:
843 -- None
844 --Function: Get identifier and identifier value for display purpose on
845 -- an ASL eRecord
846 --Parameters:
847 --IN:
848 --p_asl_id
849 -- primary key of the ASL
850 --IN OUT:
851 --OUT:
852 --x_identifier
853 -- identifier for ASL eRecord. It's a description what identifier_value
854 -- contains
855 --x_identigier_value
856 -- a string to identify an ASL
857 --Returns:
858 --Notes:
859 --Testing:
860 --End of Comments
861 ------------------------------------------------------------------------
862
863 PROCEDURE get_identifier
864 ( p_asl_id IN NUMBER,
865 x_identifier OUT NOCOPY VARCHAR2,
866 x_identifier_value OUT NOCOPY VARCHAR2
867 ) IS
868
869 l_api_name CONSTANT VARCHAR2(30) := 'get_identifier';
870 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
871 G_MODULE_PREFIX || l_api_name || '.';
872 l_progress VARCHAR2(3);
873
874 l_using_org_id PO_APPROVED_SUPPLIER_LIST.using_organization_id%TYPE;
875 l_organization VARCHAR2(30);
876 l_item_id PO_APPROVED_SUPPLIER_LIST.item_id%TYPE;
877 l_vendor_name PO_VENDORS.vendor_name%TYPE;
878 l_vendor_site_code PO_VENDOR_SITES_ALL.vendor_site_code%TYPE;
879 l_category_name MTL_CATEGORIES_KFV.concatenated_segments%TYPE;
880 l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
881
882 BEGIN
883 l_progress := '000';
884
885 IF (g_debug_stmt) THEN
886 PO_DEBUG.debug_begin
887 ( p_log_head => l_module
888 );
889 END IF;
890
891 x_identifier := FND_MESSAGE.get_string('PO', 'PO_ASL_EREC_IDENTIFIER');
892
893 SELECT PASL.using_organization_id,
894 MP.organization_code,
895 PASL.item_id,
896 DECODE (PASL.vendor_business_type,
897 'MANUFACTURER', MM.manufacturer_name,
898 PV.vendor_name),
899 PVS.vendor_site_code,
900 MC.concatenated_segments,
901 MSI.concatenated_segments
902 INTO l_using_org_id,
903 l_organization,
904 l_item_id,
905 l_vendor_name,
906 l_vendor_site_code,
907 l_category_name,
908 l_item_name
909 FROM po_approved_supplier_list PASL,
910 mtl_parameters MP,
911 mtl_manufacturers MM,
912 po_vendors PV,
913 po_vendor_sites_all PVS,
914 mtl_system_items_kfv MSI,
915 mtl_categories_kfv MC
916 WHERE PASL.asl_id = p_asl_id
917 AND PASL.using_organization_id = MP.organization_id (+)
918 AND PASL.manufacturer_id = MM.manufacturer_id (+)
919 AND PASL.vendor_id = PV.vendor_id (+)
920 AND PASL.vendor_site_id = PVS.vendor_site_id (+)
921 AND PASL.item_id = MSI.inventory_item_id (+)
922 AND PASL.owning_organization_id = NVL(MSI.organization_id,
923 PASL.owning_organization_id)
924 AND PASL.category_id = MC.category_id (+);
925
926 x_identifier_value := ' - ' || l_vendor_name || ' - ' ||
927 l_vendor_site_code || ' - ';
928
929 IF ( l_using_org_id = -1 ) THEN
930 l_organization := FND_MESSAGE.get_string('PO', 'PO_ASL_GLOBAL');
931 END IF;
932
933 IF ( l_item_id IS NOT NULL) THEN
934 x_identifier_value := l_organization || x_identifier_value ||
935 l_item_name;
936 ELSE
937 x_identifier_value := l_organization || x_identifier_value ||
938 l_category_name;
939 END IF;
940
941 IF (g_debug_stmt) THEN
942 PO_DEBUG.debug_end
943 ( p_log_head => l_module
944 );
945 END IF;
946
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 IF (g_debug_unexp) THEN
951 PO_DEBUG.debug_exc
952 ( p_log_head => l_module,
953 p_progress => l_progress
954 );
955 END IF;
956
957 FND_MSG_PUB.build_exc_msg
958 ( p_pkg_name => g_pkg_name,
959 p_procedure_name => l_api_name
960 );
961
962 RAISE;
963
964 END get_identifier;
965
966
967
968 -----------------------------------------------------------------------
969 --Start of Comments
970 --Name: init_asl_activity_tbl
971 --Pre-reqs:
972 --Modifies:
973 --Locks:
974 -- None
975 --Function: Initialize g_asl_activities table, which is used to store
976 -- the ASL changes (Insert or Update) happening in the current
977 -- commit cycle. We need these data for calling ERES event
978 -- right before commit happens.
979 --Parameters:
980 --IN:
981 --IN OUT:
982 --OUT:
983 --Returns:
984 --Notes:
985 --Testing:
986 --End of Comments
987 ------------------------------------------------------------------------
988
989 PROCEDURE init_asl_activity_tbl IS
990
991 l_api_name CONSTANT VARCHAR2(30) := 'init_asl_activity_tbl';
992 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
993 G_MODULE_PREFIX || l_api_name || '.';
994 l_progress VARCHAR2(3);
995 l_return_status VARCHAR2(1);
996
997 BEGIN
998
999 l_progress := '000';
1000
1001 IF (g_debug_stmt) THEN
1002 PO_DEBUG.debug_begin
1003 ( p_log_head => l_module
1004 );
1005 END IF;
1006
1007 g_asl_activities.delete;
1008 g_asl_activities_index := 0;
1009
1010
1011 IF (g_debug_stmt) THEN
1012 PO_DEBUG.debug_end
1013 ( p_log_head => l_module
1014 );
1015 END IF;
1016
1017
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 IF (g_debug_unexp) THEN
1021 PO_DEBUG.debug_exc
1022 ( p_log_head => l_module,
1023 p_progress => l_progress
1024 );
1025 END IF;
1026
1027 FND_MSG_PUB.build_exc_msg
1028 ( p_pkg_name => g_pkg_name,
1029 p_procedure_name => l_api_name
1030 );
1031
1032 APP_EXCEPTION.raise_exception;
1033
1034 END init_asl_activity_tbl;
1035
1036 -----------------------------------------------------------------------
1037 --Start of Comments
1038 --Name: add_asl_activity
1039 --Pre-reqs:
1040 --Modifies:
1041 --Locks:
1042 -- None
1043 --Function: Populate a row to g_asl_activities table to record a ASL
1044 -- change (insert or update)
1045 --Parameters:
1046 --IN:
1047 --p_asl_id
1048 -- Unique identifier for the ASL
1049 --p_action
1050 -- Type of action for this ASL ('INSERT' or 'UPDATE')
1051 --IN OUT:
1052 --OUT:
1053 --Returns:
1054 --Notes:
1055 --Testing:
1056 --End of Comments
1057 ------------------------------------------------------------------------
1058
1059 PROCEDURE add_asl_activity
1060 ( p_asl_id IN NUMBER,
1061 p_action IN VARCHAR2
1062 ) IS
1063
1064
1065 l_api_name CONSTANT VARCHAR2(30) := 'add_asl_activity';
1066 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1067 G_MODULE_PREFIX || l_api_name || '.';
1068 l_progress VARCHAR2(3);
1069 l_return_status VARCHAR2(1);
1070
1071 l_asl_activity asl_activity_rec;
1072
1073 BEGIN
1074
1075 IF (g_debug_stmt) THEN
1076 PO_DEBUG.debug_begin
1077 ( p_log_head => l_module
1078 );
1079 END IF;
1080
1081 g_asl_activities_index := g_asl_activities_index + 1;
1082 g_asl_activities(g_asl_activities_index).asl_id := p_asl_id;
1083 g_asl_activities(g_asl_activities_index).action := p_action;
1084
1085
1086 IF (g_debug_stmt) THEN
1087 PO_DEBUG.debug_end
1088 ( p_log_head => l_module
1089 );
1090 END IF;
1091
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 IF (g_debug_unexp) THEN
1095 PO_DEBUG.debug_exc
1096 ( p_log_head => l_module,
1097 p_progress => l_progress
1098 );
1099 END IF;
1100
1101 FND_MSG_PUB.build_exc_msg
1102 ( p_pkg_name => g_pkg_name,
1103 p_procedure_name => l_api_name
1104 );
1105
1106 APP_EXCEPTION.raise_exception;
1107
1108 END add_asl_activity;
1109
1110 -----------------------------------------------------------------------
1111 --Start of Comments
1112 --Name: process_asl_activity_tbl
1113 --Pre-reqs:
1114 --Modifies:
1115 --Locks:
1116 -- None
1117 --Function: Loop through each of the record in g_asl_activity and
1118 -- raise an ERES event. The event type will be based on
1119 -- the action column in the record.
1120 --Parameters:
1121 --IN:
1122 --IN OUT:
1123 --OUT:
1124 --Returns:
1125 --Notes:
1126 --Testing:
1127 --End of Comments
1128 ------------------------------------------------------------------------
1129
1130 PROCEDURE process_asl_activity_tbl IS
1131
1132 l_api_name CONSTANT VARCHAR2(30) := 'process_asl_activity_tbl';
1133 l_module CONSTANT FND_LOG_MESSAGES.module%TYPE :=
1134 G_MODULE_PREFIX || l_api_name || '.';
1135 l_progress VARCHAR2(3);
1136 l_return_status VARCHAR2(1);
1137
1138 BEGIN
1139
1140 IF (g_debug_stmt) THEN
1141 PO_DEBUG.debug_begin
1142 ( p_log_head => l_module
1143 );
1144 END IF;
1145
1146 l_progress := '010';
1147
1148 FOR i IN 1..g_asl_activities_index LOOP
1149 l_progress := '020';
1150
1151
1152 IF (g_debug_stmt) THEN
1153 PO_DEBUG.debug_stmt
1154 ( p_log_head => l_module,
1155 p_token => l_progress,
1156 p_message => 'Processing asl_id= ' || g_asl_activities(i).asl_id
1157 || ', action= ' || g_asl_activities(i).action
1158 );
1159 END IF;
1160
1161 PO_ASL_SV.raise_asl_eres_event
1162 ( x_return_status => l_return_status,
1163 p_asl_id => g_asl_activities(i).asl_id,
1164 p_action => g_asl_activities(i).action,
1165 p_calling_from => 'PO_ASL_SV.process_asl_activity_tbl',
1166 p_ackn_note => NULL,
1167 p_autonomous_commit => FND_API.G_FALSE
1168 );
1169
1170 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1171 l_progress := '030';
1172
1173
1174 IF (g_debug_stmt) THEN
1175 PO_DEBUG.debug_stmt
1176 ( p_log_head => l_module,
1177 p_token => l_progress,
1178 p_message => 'PO_ASL_SV.raise_asl_eres_event failed ' ||
1179 'with status ' || l_return_status
1180 );
1181 END IF;
1182
1183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184 END IF;
1185 END LOOP;
1186
1187 -- bug3539651
1188 -- clean up pl/sql table once all the rows are processed
1189 init_asl_activity_tbl;
1190
1191 IF (g_debug_stmt) THEN
1192 PO_DEBUG.debug_end
1193 ( p_log_head => l_module
1194 );
1195 END IF;
1196
1197
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200
1201 IF (g_debug_unexp) THEN
1202 PO_DEBUG.debug_exc
1203 ( p_log_head => l_module,
1204 p_progress => l_progress
1205 );
1206 END IF;
1207
1208 APP_EXCEPTION.raise_exception;
1209
1210 END process_asl_activity_tbl;
1211
1212
1213 -- <ASL ERECORD FPJ END>
1214
1215 END PO_ASL_SV;