[Home] [Help]
PACKAGE BODY: APPS.CSC_ACTION_ASSEMBLER_PVT
Source
1 PACKAGE BODY CSC_ACTION_ASSEMBLER_PVT AS
2 /* $Header: cscvrenb.pls 115.22 2004/06/22 11:09:21 bhroy ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_Action_Assembler_PVT' ;
5
6 l_Param_tbl Params_Tab_TYPE;
7 l_tbl_count number := 0;
8 PROCEDURE ENABLE_PLAN (P_PARTY_ID NUMBER,
9 P_CUST_ACCOUNT_ID NUMBER,
10 P_END_USER_TYPE VARCHAR2 := NULL,
11 X_CONDITION_ID_TBL OUT NOCOPY CONDITION_ID_Tab_Type )
12 IS
13 no_of_rows NUMBER := 0;
14 Cursor C1 IS
15 select a.plan_id
16 from csc_cust_plans a,
17 csc_plan_headers_b b
18 where a.party_id = p_Party_id
19 and a.plan_id = b.plan_id
20 and nvl(b.end_user_type, 'Y') = nvl(p_end_user_type, nvl(b.end_user_type, 'Y') )
21 and a.cust_account_id is null
22 and a.plan_status_code in ('APPLIED', 'ENABLED', 'TRANSFERED')
23 and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate))
24 and trunc(nvl(b.end_date_active, sysdate))
25 UNION
26 select a.plan_id
27 from csc_cust_plans a,
28 csc_plan_headers_b b
29 where a.party_id = p_party_id
30 and a.plan_id = b.plan_id
31 and nvl(b.end_user_type, 'Y') = nvl(p_end_user_type, nvl(b.end_user_type, 'Y') )
32 and a.cust_account_id = p_cust_account_id
33 and a.plan_status_code in ('APPLIED', 'ENABLED', 'TRANSFERED')
34 and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate))
35 and trunc(nvl(b.end_date_active, sysdate));
36
37 Cursor C2 ( c_plan_id NUMBER ) IS
38 SELECT och.id Condition_id
39 FROM csc_plan_lines cpl,okc_condition_headers_v och
40 WHERE cpl.condition_id = och.id
41 AND cpl.plan_id = c_plan_id;
42
43 BEGIN
44 X_Condition_ID_TBL.DELETE;
45 FOR C1_REC IN C1 LOOP
46 FOR C2_rec in C2 ( c1_rec.plan_id ) LOOP
47 no_of_rows := no_of_rows + 1;
48 X_CONDITION_ID_TBL(no_of_rows).CONDITION_ID := C2_rec.Condition_ID;
49 END LOOP;
50 END LOOP;
51 END ENABLE_PLAN;
52
53 /*
54 PROCEDURE CHECK_ACTION_ATTRIBUTES (p_Action_id NUMBER,
55 p_Msg_Tbl IN OKC_AQ_PVT.MSG_TAB_TYP,
56 x_Action_attr_tbl OUT NOCOPY OKC_AQ_PVT.MSG_TAB_TYP)
57 IS
58 Cursor C1 is
59 SELECT element_name
60 FROM okc_action_attributes_v
61 WHERE acn_id = p_Action_id;
62
63 TYPE Element_Name_Rec_Type IS RECORD ( Element_Name VARCHAR2(1000));
64
65 TYPE Element_Name_Tab_Type IS TABLE OF Element_Name_Rec_Type
66 INDEX BY BINARY_INTEGER;
67
68 l_Element_Name_Tbl Element_Name_Tab_Type;
69 l_no_of_rows Number := 0;
70 l_no_of_recs Number := 0;
71
72 BEGIN
73 FOR C1_rec in C1 LOOP
74 l_no_of_rows := l_no_of_rows + 1;
75 l_Element_Name_Tbl(l_no_of_rows).Element_Name := C1_rec.Element_Name;
76 END LOOP;
77 x_Action_Attr_tbl := okc_aq_pvt.msg_tab_typ();
78 IF p_Msg_Tbl.count > 0
79 THEN
80 FOR i in p_Msg_tbl.FIRST..p_Msg_Tbl.LAST LOOP
81 FOR j in 1..l_Element_Name_tbl.COUNT LOOP
82 IF l_Element_Name_tbl(j).Element_Name = p_Msg_Tbl(i).Element_Name
83 THEN
84 x_Action_Attr_Tbl.Extend;
85 l_no_of_recs := l_no_of_recs + 1;
86 x_Action_Attr_Tbl(l_no_of_recs).Element_Name := p_Msg_Tbl(i).Element_Name;
87 x_Action_Attr_Tbl(l_no_of_recs).Element_Value := p_Msg_Tbl(i).Element_Value;
88 END IF;
89 END LOOP;
90 END LOOP;
91 END IF;
92 END CHECK_ACTION_ATTRIBUTES;
93 */
94
95 PROCEDURE ENABLE_PLAN_AND_GET_OUTCOMES (
96 P_PARTY_ID IN NUMBER,
97 P_Cust_Account_Id IN NUMBER,
98 P_End_User_Type IN VARCHAR2 := NULL,
99 P_Application_Short_Name IN VARCHAR2,
100 P_Msg_Tbl IN OKC_AQ_PVT.MSG_TAB_TYP,
101 X_Results_Tbl OUT NOCOPY RESULTS_TAB_TYPE )
102 IS
103 x_return_status varchar2(1);
104 x_msg_count number;
105 x_msg_data varchar2(2000);
106 l_Condition_Tbl Condition_Id_Tab_Type;
107 l_results_tbl RESULTS_TAB_TYPE;
108 BEGIN
109 ENABLE_PLAN ( P_PARTY_ID => p_Party_Id,
110 P_CUST_ACCOUNT_ID => P_CUST_ACCOUNT_ID,
111 P_END_USER_TYPE => P_END_USER_TYPE,
112 X_CONDITION_ID_TBL => l_Condition_Tbl );
113
114 IF l_Condition_tbl.Count > 0 THEN
115 FOR i in l_Condition_tbl.FIRST..l_Condition_tbl.LAST LOOP
116
117 GET_OUTCOMES(
118 p_api_version_number => 1,
119 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
120 p_Condition_id => l_Condition_tbl(i).Condition_id,
121 p_Application_Short_Name => p_Application_Short_Name,
122 P_Msg_Tbl =>P_Msg_Tbl,
123 x_return_status => x_return_status,
124 x_msg_count => x_msg_count ,
125 x_msg_data => x_msg_data ,
126 x_Results_Tbl => l_results_tbl );
127 IF x_return_status <> 'S' THEN
128 NULL; --Need to add stuff here
129 END IF;
130 END LOOP;
131 x_results_tbl := l_results_tbl;
132 END IF;
133 END ENABLE_PLAN_AND_GET_OUTCOMES;
134
135
136 PROCEDURE GET_OUTCOMES(
137 p_api_version_number IN NUMBER,
138 p_init_msg_list IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
139 P_Condition_Id IN okc_condition_headers_b.id%TYPE,
140 P_Application_short_name IN VARCHAR2,
141 P_Msg_Tbl IN OKC_AQ_PVT.MSG_TAB_TYP,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER,
144 x_msg_data OUT NOCOPY VARCHAR2,
145 X_Results_Tbl IN OUT NOCOPY RESULTS_TAB_TYPE )
146 IS
147 --local variables
148 l_OUTCOME_TBL OKC_CONDITION_EVAL_PUB.OUTCOME_TAB_TYPE ;
149 l_RESULTS_TBL RESULTS_TAB_TYPE ;
150
151 l_MSG_REC OKC_AQ_PVT.MSG_REC_TYP;
152 l_MSG_TBL OKC_AQ_PVT.MSG_TAB_TYP ;
153
154 l_param_tbl params_tab_type;
155 l_Name VARCHAR2(1000);
156 l_Description VARCHAR2(1800);
157 l_no_of_recs NUMBER ;
158 BEGIN
159 OKC_CONDITION_EVAL_PUB.EVALUATE_PLAN_CONDITION (
160 p_api_version => p_api_version_number,
161 p_init_msg_list => p_init_msg_list,
162 x_return_status => x_return_status,
163 x_msg_count => x_msg_Count,
164 x_msg_data => x_msg_data,
165 P_Cnh_Id => P_Condition_Id,
166 P_Msg_Tab => P_Msg_Tbl,
167 X_Sync_Outcome_Tab => l_outcome_tbl
168 );
169 IF l_outcome_tbl.count > 0 THEN
170 FOR i in l_outcome_tbl.FIRST..l_outcome_tbl.LAST LOOP
171 l_no_of_recs := nvl(X_results_tbl.count,0) + 1;
172 IF l_outcome_tbl(i).type = 'ALERT' THEN
173 l_Description := GET_ALERT_NAME( l_outcome_tbl(i).Name,p_Application_Short_Name, l_Name );
174 ELSIF l_outcome_tbl(i).type = 'SCRIPT' THEN
175 l_Name := GET_SCRIPT_NAME( l_outcome_tbl(i).Name );
176 --l_Name := l_outcome_tbl(i).Name;
177 l_Description := l_Name;
178 END IF;
179 X_results_tbl(l_no_of_recs).Name := l_Name;
180 X_results_tbl(l_no_of_recs).Type := l_Outcome_Tbl(i).Type;
181 X_results_tbl(l_no_of_recs).Description := l_Description;
182 END LOOP;
183 END IF;
184 EXCEPTION
185 WHEN OTHERS THEN
186 FND_MSG_PUB.Build_Exc_Msg;
187 APP_EXCEPTION.RAISE_EXCEPTION;
188 END GET_OUTCOMES;
189
190 FUNCTION GET_ALERT_NAME(
191 P_String VARCHAR2,
192 p_Application_Short_Name IN VARCHAR2,
193 x_Name OUT NOCOPY VARCHAR2 )
194 RETURN VARCHAR2
195 IS
196 l_message_text VARCHAR2(1800);
197 l_alert_msg VARCHAR2(4000);
198 l_alert_count NUMBER := 0;
199 l_params_tbl PARAMS_TAB_TYPE;
200
201 temp number(2) := 0;
202 BEGIN
203 l_Params_tbl := Detach_String ( p_String, x_Name );
204 l_message_text := fnd_message.get_string(
205 appin => p_application_short_name,
206 namein => x_name );
207 --FND_MSG_PUB.INITIALIZE;
208 FOR i in 1..l_params_tbl.COUNT LOOP
209 IF i = 1 THEN
210 FND_MESSAGE.SET_NAME(p_Application_Short_Name,l_params_tbl(i).PName);
211 END IF;
212 IF instr(l_message_text, '&'||l_params_tbl(i).Name ) <> 0 then
213 FND_MESSAGE.SET_TOKEN(l_params_tbl(i).Name, l_params_tbl(i).Value);
214 END IF;
215 END LOOP;
216
217 FND_MSG_PUB.INITIALIZE;
218 l_alert_msg := FND_MESSAGE.GET;
219 RETURN ( l_alert_msg );
220 END GET_ALERT_NAME;
221
222 FUNCTION GET_SCRIPT_NAME( P_String VARCHAR2 ) RETURN VARCHAR2
223 IS
224 l_Name VARCHAR2(4000);
225 l_params_tbl PARAMS_TAB_TYPE;
226 BEGIN
227 l_Params_tbl := Detach_String (p_String, l_Name );
228 return( l_Name );
229 END GET_SCRIPT_NAME;
230
231 -- bug 3712807, increased l_string, l_name to handle multiple parameters, a long list.
232
233 FUNCTION DETACH_STRING ( p_string VARCHAR2, x_Name OUT NOCOPY VARCHAR2 )
234 RETURN params_tab_type
235 IS
236 l_string varchar2(2000) := p_string;
237 l_brac_cnt number := 0;
238 l_proc_name varchar2(150);
239 l_name varchar2(2000);
240 x_param_tbl params_tab_type;
241
242 i number(6) := 0;
243 j number(6);
244 l_pos number(6) := 0;
245 l_param varchar2(250);
246 l_value varchar2(360);
247 BEGIN
248
249 l_brac_cnt := instr(l_string, '(' ) + 1;
250
251 -- get the executable name
252 if l_brac_cnt > 1 then
253 l_proc_name := substr(l_string, 1, l_brac_cnt - 2);
254 else
255 l_proc_name := rtrim(l_string, ';');
256 end if;
257 x_name := l_proc_name;
258
259 -- get the parameter/value list
260 l_name := substr( l_string, l_brac_cnt+1 );
261
262 -- translate is done to make the last parenthesis a comma to make the
263 -- detach of the value consistent in the loop below.
264 l_name := translate(l_name, ')', ',');
265
266 -- get the total number of parameter/value combinations in the string.
267 loop
268 l_pos := instr(l_name, '=>', 1, i+1);
269 exit when l_pos = 0;
270 i := i + 1;
271 end loop;
272
273 for j in 1..i
274 loop
275 x_param_tbl(j).name := substr(l_name, 1, instr(l_name, '=>')-1 );
276 x_param_tbl(j).value := substr(l_name, instr(l_name, '=>')+2, instr(l_name, ',') -
277 (instr(l_name, '=>')+2) );
278
279 -- Bug# 3457037
280 If ( ( trim(x_param_tbl(j).value) = '''OKC_API.G_MISS_CHAR''' ) OR ( trim(x_param_tbl(j).value) = 'OKC_API.G_MISS_NUM') OR ( trim(x_param_tbl(j).value) = '''OKC_API.G_MISS_DATE''' ) ) Then
281 x_param_tbl(j).value := '';
282 End If;
283
284 l_name := trim (leading ' ' FROM substr(l_name, instr(l_name, ',')+1 ) );
285 x_param_tbl(j).PName := l_proc_Name;
286 end loop;
287
288 If (i < 1) Then
289 x_param_tbl(1).name := '';
290 x_param_tbl(1).value := '';
291 x_param_tbl(1).PName := l_proc_Name;
292 End If;
293
294 RETURN (x_param_tbl);
295
296 END Detach_String;
297
298 END CSC_ACTION_ASSEMBLER_PVT;