[Home] [Help]
PACKAGE BODY: APPS.PQH_CONTEXT
Source
1 package body pqh_context as
2 /* $Header: pqhcntxt.pkb 120.0 2005/05/29 02:02:26 appldev noship $ */
3 function get_global_context (p_context in varchar2) return varchar2
4 is
5 v_global_context pqh_copy_entity_contexts.context%type;
6 v_global_app pqh_copy_entity_contexts.application_short_name%type;
7 v_global_txn pqh_copy_entity_contexts.transaction_short_name%type;
8 v_global_resp pqh_copy_entity_contexts.responsibility_key%type;
9 v_global_legis pqh_copy_entity_contexts.legislation_code%type;
10 --
11 v_local_context pqh_copy_entity_contexts.context%type;
12 v_local_app pqh_copy_entity_contexts.application_short_name%type;
13 v_local_txn pqh_copy_entity_contexts.transaction_short_name%type;
14 v_local_resp pqh_copy_entity_contexts.responsibility_key%type;
15 v_local_legis pqh_copy_entity_contexts.legislation_code%type;
16 --
17 cursor c_current_context (p_context varchar2 default null
18 )
19 is
20 select context
21 , transaction_short_name
22 , application_short_name
23 , responsibility_key
24 , legislation_code
25 from pqh_copy_entity_contexts cec
26 where context = p_context
27 ;
28 cursor c_global_context (p_txn_short_name varchar2 default null
29 , p_current_context varchar2 default null
30 )
31 is
32 select context
33 from pqh_copy_entity_contexts cec
34 where transaction_short_name = p_txn_short_name
35 and context <> p_current_context
36 and application_short_name IS NULL
37 and responsibility_key IS NULL
38 and legislation_code IS NULL
39 ;
40 cursor c_app_context (p_txn_short_name varchar2 default null
41 , p_app_short_name varchar2 default null
42 , p_current_context varchar2 default null
43 )
44 is
45 select context
46 from pqh_copy_entity_contexts cec
47 where transaction_short_name = p_txn_short_name
48 and application_short_name = p_app_short_name
49 and context <> p_current_context
50 and responsibility_key IS NULL
51 and legislation_code IS NULL
52 ;
53 cursor c_resp_context (p_txn_short_name varchar2 default null
54 , p_app_short_name varchar2 default null
55 , p_resp_key varchar2 default null
56 , p_current_context varchar2 default null
57 )
58 is
59 select context
60 from pqh_copy_entity_contexts cec
61 where transaction_short_name = p_txn_short_name
62 and application_short_name = p_app_short_name
63 and responsibility_key = p_resp_key
64 and context <> p_current_context
65 and legislation_code IS NULL
66 ;
67 cursor c_legis_context (p_txn_short_name varchar2 default null
68 , p_app_short_name varchar2 default null
69 , p_legis_code varchar2 default null
70 , p_current_context varchar2 default null
71 )
72 is
73 select context
74 from pqh_copy_entity_contexts cec
75 where transaction_short_name = p_txn_short_name
76 and application_short_name = p_app_short_name
77 and legislation_code = p_legis_code
78 and context <> p_current_context
79 and responsibility_key IS NULL
80 ;
81 begin
82 -- get passed context's information
83 open c_current_context(p_context => p_context);
84 fetch c_current_context into v_local_context
85 , v_local_txn
86 , v_local_app
87 , v_local_resp
88 , v_local_legis;
89 close c_current_context;
90 -- if there is no app defined then this is the global context
91 IF v_local_app IS NULL THEN
92 RETURN p_context;
93 END IF;
94 -- Try with App and Resp
95 IF v_local_resp IS NOT NULL THEN
96 open c_resp_context(p_txn_short_name => v_local_txn
97 , p_current_context=> p_context
98 , p_app_short_name => v_local_app
99 , p_resp_key => v_local_resp);
100 fetch c_resp_context into v_global_context;
101 close c_resp_context;
102 IF v_global_context IS NOT NULL THEN
103 RETURN v_global_context;
104 END IF;
105 END IF;
106 -- Try with App and Legis
107 IF v_local_legis IS NOT NULL THEN
108 open c_legis_context(p_txn_short_name => v_local_txn
109 , p_current_context=> p_context
110 , p_app_short_name => v_local_app
111 , p_legis_code => v_local_legis);
112 fetch c_legis_context into v_global_context;
113 close c_legis_context;
114 IF v_global_context IS NOT NULL THEN
115 RETURN v_global_context;
116 END IF;
117 END IF;
118 -- Try with App only
119 open c_app_context(p_txn_short_name => v_local_txn
120 , p_current_context=> p_context
121 , p_app_short_name => v_local_app);
122 fetch c_app_context into v_global_context;
123 close c_app_context;
124 IF v_global_context IS NOT NULL THEN
125 RETURN v_global_context;
126 END IF;
127 -- get global context
128 open c_global_context(p_txn_short_name => v_local_txn
129 , p_current_context=> p_context);
130 fetch c_global_context into v_global_context;
131 close c_global_context;
132 IF v_global_context IS NOT NULL THEN
133 RETURN v_global_context;
134 END IF;
135 RETURN null ; -- since no global context found.
136 end;
137 end;