[Home] [Help]
PACKAGE BODY: APPS.GMO_MBR_UTIL
Source
1 PACKAGE BODY GMO_MBR_UTIL AS
2 /* $Header: GMOMBRUB.pls 120.12 2006/10/06 11:20:19 rvsingh noship $ */
3
4 FUNCTION GET_TEMPLATE_CODE(P_EVENT_NAME VARCHAR2,P_EVENT_KEY VARCHAR2) RETURN VARCHAR2 IS
5 L_ame_rule_ids FND_TABLE_OF_VARCHAR2_255;
6 L_ame_rule_descriptions FND_TABLE_OF_VARCHAR2_255;
7 L_variable_names FND_TABLE_OF_VARCHAR2_255;
8 L_variable_values FND_TABLE_OF_VARCHAR2_255;
9 BEGIN
10 edr_utilities.get_rules_and_variables(P_EVENT_NAME => P_EVENT_NAME
11 ,P_EVENT_KEY =>P_EVENT_KEY
12 ,X_AME_RULE_IDS => l_ame_rule_ids
13 ,X_AME_RULE_DESCRIPTIONS => l_ame_rule_descriptions
14 ,X_VARIABLE_NAMES => l_variable_names
15 ,X_VARIABLE_VALUES => l_variable_values);
16 -- Bug 5563819 : start : rvsingh
17 IF l_variable_names IS NOT NULL THEN
18 FOR i in 1..l_variable_names.count
19 LOOP
20 IF l_variable_names(i) = 'EREC_STYLE_SHEET'
21 THEN
22 RETURN l_variable_values(i);
23 END IF;
24 END LOOP;
25 END IF;
26 -- Bug 5563819 : End : rvsingh
27 END;
28 FUNCTION GET_MBR_XML(P_EVENT_NAME VARCHAR2,P_EVENT_KEY VARCHAR2) RETURN CLOB IS
29 l_xml CLOB;
30 l_error_code NUMBER;
31 l_error_msg VARCHAR2(4000);
32 l_log_file VARCHAR2(4000);
33 l_map_code VARCHAR2(50);
34 l_CNT NUMBER;
35 CURSOR GET_MAP_CODE IS
36 SELECT distinct b.STATUS,
37 EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_XML_MAP_CODE',b.GUID) map_code
38 from wf_events_vl a,
39 wf_event_subscriptions b
40 WHERE a.guid=b.EVENT_FILTER_GUID
41 and b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
42 and a.name = p_event_name
43 ORDER BY b.STATUS DESC;
44 BEGIN
45 select count(*) INTO l_cnt
46 from wf_events_vl a,
47 wf_event_subscriptions b
48 WHERE a.guid=b.EVENT_FILTER_GUID
49 and b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
50 and b.status = 'ENABLED'
51 and a.name = p_event_name;
52 IF l_cnt > 1 THEN
53 return null;
54 ELSE
55 l_cnt := 0;
56 FOR GET_MAP_CODE_REC in GET_MAP_CODE
57 LOOP
58 l_map_code := GET_MAP_CODE_REC.map_code;
59 l_cnt := l_cnt + 1;
60 END LOOP;
61 IF L_CNT = 1
62 THEN
63 edr_utilities.generate_xml(P_MAP_CODE => nvl(l_map_code,P_EVENT_NAME)
64 ,P_DOCUMENT_ID => P_EVENT_KEY
65 ,p_xml => l_xml
66 ,p_error_code => l_error_code
67 ,p_error_msg => l_error_msg
68 ,p_log_file => l_log_file);
69 RETURN l_xml;
70 ELSE
71 RETURN NULL;
72 END IF;
73 END IF;
74 RETURN l_xml;
75 END;
76 /************************************************************************
77 ** Local function to retrieve Query ID based on event Name and Key
78 ** EDR Utility Functions.
79 **
80 ************************************************************************/
81
82 FUNCTION GET_QUERY_ID(P_EVENT_NAME VARCHAR2,P_EVENT_KEY VARCHAR2) RETURN NUMBER IS
83 L_event_names FND_TABLE_OF_VARCHAR2_255;
84 L_event_keys FND_TABLE_OF_VARCHAR2_255;
85 L_QUERY_ID NUMBER;
86 BEGIN
87 --Initialize the the event names array containing only one element.
88 L_event_names := FND_TABLE_OF_VARCHAR2_255();
89 L_event_names.EXTEND;
90 --Initialize the the event key array containing only one element.
91 L_event_keys := FND_TABLE_OF_VARCHAR2_255();
92 L_event_keys.EXTEND;
93 L_event_names(1) := P_EVENT_NAME;
94 L_event_keys(1) := P_EVENT_KEY;
95 edr_standard.PSIG_QUERY_ONE ( p_event_name => L_event_names ,
96 p_event_key => L_event_keys ,
97 o_query_id => L_QUERY_ID);
98 RETURN L_QUERY_ID;
99 END;
100
101 PROCEDURE GET_TEMPLATE_CODE_AND_XML(P_EVENT_NAME VARCHAR2,
102 P_EVENT_KEY VARCHAR2,
103 X_TEMPLATE_CODE OUT NOCOPY VARCHAR2,
104 X_QUERY_ID OUT NOCOPY NUMBER,
105 X_MBR_XML OUT NOCOPY CLOB) IS
106 BEGIN
107 X_TEMPLATE_CODE := GMO_MBR_UTIL.get_template_code(p_event_name => P_EVENT_NAME,
108 p_event_key => P_EVENT_KEY);
109
110 -- generate XML only when template code is present
111 IF X_TEMPLATE_CODE IS NOT NULL
112 THEN
113 X_MBR_XML := GMO_MBR_UTIL.get_mbr_xml(p_event_name => P_EVENT_NAME,
114 p_event_key => P_EVENT_KEY);
115 X_QUERY_ID := GMO_MBR_UTIL.GET_QUERY_ID(p_event_name => P_EVENT_NAME,
116 p_event_key => P_EVENT_KEY);
117 END IF;
118 END;
119
120
121 PROCEDURE GET_TEMPLATE_CODE_AND_QUERYID(P_EVENT_NAME VARCHAR2,
122 P_EVENT_KEY VARCHAR2,
123 X_TEMPLATE_CODE OUT NOCOPY VARCHAR2,
124 X_QUERY_ID OUT NOCOPY NUMBER) IS
125 BEGIN
126 X_TEMPLATE_CODE := GMO_MBR_UTIL.get_template_code(p_event_name => P_EVENT_NAME,
127 p_event_key => P_EVENT_KEY);
128 X_QUERY_ID := GMO_MBR_UTIL.GET_QUERY_ID(p_event_name => P_EVENT_NAME,
129 p_event_key => P_EVENT_KEY);
130 END;
131
132 PROCEDURE GET_USER_DISPLAY_NAME (P_USER_ID IN NUMBER, P_USER_DISPLAY_NAME OUT nocopy VARCHAR2)as
133 BEGIN
134 GMO_UTILITIES.GET_USER_DISPLAY_NAME(P_USER_ID,P_USER_DISPLAY_NAME);
135 END GET_USER_DISPLAY_NAME;
136
137 procedure get_organization (P_MBR_EVT_KEY IN VARCHAR2,
138 X_ORG_CODE OUT NOCOPY VARCHAR2,
139 X_ORG_NAME OUT NOCOPY VARCHAR2) IS
140 l_org_id NUMBER(15) :=NULL;
141 BEGIN
142 Select substr(P_MBR_EVT_KEY,
143 to_number(instr(P_MBR_EVT_KEY,'-'))+1,
144 decode(instr(P_MBR_EVT_KEY,'-',1,2),0,length(P_MBR_EVT_KEY),
145 to_number(instr(P_MBR_EVT_KEY,'-',1,2))-to_number(instr(P_MBR_EVT_KEY,'-'))-1))
146 into l_org_id from dual;
147
148 SELECT hou.name ORGANIZATION_NAME , mp.organization_code ORGANIZATION_CODE into X_ORG_NAME,X_ORG_CODE
149 FROM hr_all_organization_units hou, mtl_parameters mp
150 WHERE hou.organization_id = l_org_id
151 and mp.organization_id = hou.organization_id;
152 END get_organization;
153
154 FUNCTION GET_DISPENSE_CONFIG(P_INVENTORY_ITEM_ID IN NUMBER,P_ORGANIZATION_ID IN NUMBER,P_RECIPE_ID IN NUMBER) RETURN NUMBER
155 IS
156 l_config_id NUMBER(15);
157 l_dispense_required varchar2(5);
158 BEGIN
159 GMO_DISPENSE_SETUP_PVT.IS_DISPENSE_ITEM(p_inventory_item_id => p_inventory_item_id,
160 p_organization_id => nvl(G_ORGANIZATION_ID,p_organization_id),
161 p_recipe_id => p_recipe_id,
162 x_is_dispense_required => l_dispense_required,
163 x_dispense_config_id => l_config_id);
164 RETURN nvl(l_config_id,-1);
165 END GET_DISPENSE_CONFIG;
166
167 FUNCTION SET_GLOBAL_ORGID(P_MBR_EVT_KEY IN VARCHAR2) RETURN NUMBER
168 IS
169 l_flag NUMBER :=1;
170 BEGIN
171 Select substr(P_MBR_EVT_KEY,
172 to_number(instr(P_MBR_EVT_KEY,'-'))+1,
173 decode(instr(P_MBR_EVT_KEY,'-',1,2),0,length(P_MBR_EVT_KEY),
174 to_number(instr(P_MBR_EVT_KEY,'-',1,2))-to_number(instr(P_MBR_EVT_KEY,'-'))-1))
175 into G_ORGANIZATION_ID from dual;
176 RETURN l_flag;
177 END SET_GLOBAL_ORGID;
178
179 END GMO_MBR_UTIL;