PACKAGE BODY: APPS_NE.OKC_QUEUE_RULE_PVT

Source

1 package body OKC_QUEUE_RULE_PVT as
2 /* $Header: OKCRQURB.pls 120.4 2012/03/07 16:03:00 vgujarat noship $ */
3 
4 /*this function is used to to resove rule based subscription  */
5 FUNCTION get_acn_type (p_corrid  IN  VARCHAR2)
6 RETURN VARCHAR2
7 IS
8 
9 v_acn_type  VARCHAR2(30);
10 l_owner  VARCHAR2(30);
11 l_okc_owner  VARCHAR2(30);
12 
13 BEGIN
14 
15 SELECT oracle_username INTO l_owner FROM system.FND_ORACLE_USERID WHERE read_only_flag = 'E';
16 
17 /*modified for bug 12679276*/
18 EXECUTE IMMEDIATE 'select fou.oracle_username
19 from '||l_owner||'.FND_PRODUCT_INSTALLATIONS# FPI,
20      '||l_owner||'.FND_ORACLE_USERID# FOU,
21      '||l_owner||'.FND_APPLICATION# FA
22       where fpi.application_id = fa.application_id
23       and   fpi.oracle_id = fou.oracle_id
24       and   fa.application_short_name = ''OKC''' INTO l_okc_owner;
25 
26 EXECUTE IMMEDIATE 'SELECT acn_type
27 FROM '||l_okc_owner||'.okc_actions_b#
28 WHERE correlation = :p_corrid' INTO v_acn_type USING p_corrid;
29 
30        IF v_acn_type IS NULL THEN
31          RETURN('Not Available');
32        ELSE
33 	 RETURN(v_acn_type);
34        END IF;
35 EXCEPTION
36   WHEN others THEN
37      RETURN('Not Available');
38 END get_acn_type;
39 
40 end;