[Home] [Help]
PACKAGE BODY: APPS.XNP_SV_NETWORK$SMS_SV
Source
1 package body xnp_sv_network$sms_sv as
2 /* $Header: XNPSVN2B.pls 120.0 2005/05/30 11:52:10 appldev noship $ */
3
4
5 procedure FormView(Z_FORM_STATUS in number);
6 function BuildSQL(
7 P_PORTING_ID in varchar2 default null,
8 P_SUBSCRIPTION_TYPE in varchar2 default null,
9 P_SUBSCRIPTION_TN in varchar2 default null,
10 P_NRC_CODE in varchar2 default null,
11 P_REC_CODE in varchar2 default null) return boolean;
12 function PreQuery(
13 P_PORTING_ID in varchar2 default null,
14 P_SUBSCRIPTION_TYPE in varchar2 default null,
15 P_SUBSCRIPTION_TN in varchar2 default null,
16 P_NRC_CODE in varchar2 default null,
17 P_REC_CODE in varchar2 default null) return boolean;
18 function PostQuery(Z_POST_DML in boolean) return boolean;
19 procedure CreateQueryJavaScript;
20 procedure CreateListJavaScript;
21 procedure InitialiseDomain(P_ALIAS in varchar2);
22
23 QF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
24 QF_QUERY_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_FIND_BUTTON'));
25 QF_CLEAR_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_CLEAR_BUTTON'));
26 QF_QUERY_BUT_ACTION constant varchar2(10) := 'QUERY';
27 QF_CLEAR_BUT_ACTION constant varchar2(10) := 'CLEAR';
28 QF_NUMBER_OF_COLUMNS constant number(4) := 2;
29 VF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
30 VF_UPDATE_BUT_CAPTION constant varchar2(100) := XNP_WSGL.MsgGetText(6,XNP_WSGLM.CAP006_VF_UPDATE);
31 VF_CLEAR_BUT_CAPTION constant varchar2(100) := XNP_WSGL.MsgGetText(8,XNP_WSGLM.CAP008_VF_REVERT);
32 VF_DELETE_BUT_CAPTION constant varchar2(100) := XNP_WSGL.MsgGetText(7,XNP_WSGLM.CAP007_VF_DELETE);
33 VF_UPDATE_BUT_ACTION constant varchar2(10) := 'UPDATE';
34 VF_CLEAR_BUT_ACTION constant varchar2(10) := 'CLEAR';
35 VF_DELETE_BUT_ACTION constant varchar2(10) := 'DELETE';
36 VF_VERIFIED_DELETE constant varchar2(100) := 'VerifiedDelete';
37 VF_NUMBER_OF_COLUMNS constant number(4) := 2;
38 RL_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
39 RL_NEXT_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_NEXT_BUTTON'));
40 RL_PREV_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_PREVIOUS_BUTTON'));
41 RL_FIRST_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_FIRST_BUTTON'));
42 RL_LAST_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_LAST_BUTTON'));
43 RL_COUNT_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_COUNT_BUTTON'));
44 RL_REQUERY_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_REFRESH_BUTTON'));
45 RL_QUERY_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_SEARCH_BUTTON'));
46 RL_QUERY_BUT_ACTION constant varchar2(10) := 'QUERY';
47 RL_NEXT_BUT_ACTION constant varchar2(10) := 'NEXT';
48 RL_PREV_BUT_ACTION constant varchar2(10) := 'PREV';
49 RL_FIRST_BUT_ACTION constant varchar2(10) := 'FIRST';
50 RL_LAST_BUT_ACTION constant varchar2(10) := 'LAST';
51 RL_COUNT_BUT_ACTION constant varchar2(10) := 'COUNT';
52 RL_REQUERY_BUT_ACTION constant varchar2(10) := 'REQUERY';
53 RL_RECORD_SET_SIZE constant number(4) := 10;
54 RL_TOTAL_COUNT_REQD constant boolean := TRUE;
55 RL_NUMBER_OF_COLUMNS constant number(4) := 1;
56 LOV_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
57 LOV_FIND_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_FIND_BUTTON'));
58 LOV_CLOSE_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_CLOSE_BUTTON'));
59 LOV_FIND_BUT_ACTION constant varchar2(10) := 'FIND';
60 LOV_CLOSE_BUT_ACTION constant varchar2(10) := 'CLOSE';
61 LOV_BUTTON_TEXT constant varchar2(100) := htf.italic(fnd_message.get_string('XNP','LIST_OF_VALUES'));
62 LOV_FRAME constant varchar2(20) := null;
63 TF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
64 DEF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
65
66 CURR_VAL XNP_SV_SMS_V%ROWTYPE;
67
68 TYPE FORM_REC IS RECORD
69 (SV_SMS_ID VARCHAR2(8)
70 ,PORTING_ID VARCHAR2(80)
71 ,ROUTING_NUMBER_ID VARCHAR2(8)
72 ,SUBSCRIPTION_TYPE VARCHAR2(30)
73 ,SUBSCRIPTION_TN VARCHAR2(20)
74 ,MEDIATOR_SP_ID VARCHAR2(8)
75 ,ROUTING_NUMBER VARCHAR2(20)
76 ,PROVISION_SENT_DATE VARCHAR2(23)
77 ,CNAM_ADDRESS VARCHAR2(80)
78 ,CNAM_SUBSYSTEM VARCHAR2(80)
79 ,ISVM_ADDRESS VARCHAR2(80)
80 ,ISVM_SUBSYSTEM VARCHAR2(80)
81 ,LIDB_ADDRESS VARCHAR2(80)
82 ,LIDB_SUBSYSTEM VARCHAR2(80)
83 ,CLASS_ADDRESS VARCHAR2(80)
84 ,CLASS_SUBSYSTEM VARCHAR2(80)
85 ,WSMSC_ADDRESS VARCHAR2(80)
86 ,WSMSC_SUBSYSTEM VARCHAR2(80)
87 ,RN_ADDRESS VARCHAR2(80)
88 ,RN_SUBSYSTEM VARCHAR2(80)
89 ,PROVISION_DONE_DATE VARCHAR2(23)
90 ,NRC_CODE VARCHAR2(20)
91 ,NRC_NAME VARCHAR2(80)
92 ,NRC_INTERNET VARCHAR2(40)
93 ,ROUTING_REF VARCHAR2(80)
94 ,ROUTING_SP_ID VARCHAR2(8)
95 ,REC_SP_ID VARCHAR2(80)
96 ,REC_CODE VARCHAR2(20)
97 ,REC_NAME VARCHAR2(80)
98 ,REC_INTERNET VARCHAR2(40)
99 ,DSP_REC VARCHAR2(2000)
100 ,REC_EMAIL VARCHAR2(80)
101 ,DSP_NRC VARCHAR2(2000)
102 ,NRC_EMAIL VARCHAR2(80)
103 ,INTERCONNECT_TYPE VARCHAR2(30)
104 ,DSP_CNAM VARCHAR2(2000)
105 ,DSP_CLASS VARCHAR2(2000)
106 ,DSP_ISVM VARCHAR2(2000)
107 ,DSP_LIDB VARCHAR2(2000)
108 ,DSP_WSMSC VARCHAR2(2000)
109 ,DSP_RN VARCHAR2(2000)
110 );
111 FORM_VAL FORM_REC;
112
113 TYPE NBT_REC IS RECORD
114 (DSP_REC VARCHAR2(2000)
115 ,DSP_NRC VARCHAR2(2000)
116 ,DSP_CNAM VARCHAR2(2000)
117 ,DSP_CLASS VARCHAR2(2000)
118 ,DSP_ISVM VARCHAR2(2000)
119 ,DSP_LIDB VARCHAR2(2000)
120 ,DSP_WSMSC VARCHAR2(2000)
121 ,DSP_RN VARCHAR2(2000)
122 );
123 NBT_VAL NBT_REC;
124
125 ZONE_SQL VARCHAR2(4800) := null;
126
127 D_SUBSCRIPTION_TYPE XNP_WSGL.typDVRecord;
128 D_INTERCONNECT_TYPE XNP_WSGL.typDVRecord;
129 --------------------------------------------------------------------------------
130 -- Name: xnp_sv_network$sms_sv.InitialiseDomain
131 --
132 -- Description: Initialises the Domain Record for the given Column Usage
133 --
134 -- Parameters: P_ALIAS The alias of the column usage
135 --
136 --------------------------------------------------------------------------------
137 procedure InitialiseDomain(P_ALIAS in varchar2) is
138 begin
139
140 if P_ALIAS = 'SUBSCRIPTION_TYPE' and not D_SUBSCRIPTION_TYPE.Initialised then
141 D_SUBSCRIPTION_TYPE.ColAlias := 'SUBSCRIPTION_TYPE';
142 D_SUBSCRIPTION_TYPE.ControlType := XNP_WSGL.DV_TEXT;
143 D_SUBSCRIPTION_TYPE.DispWidth := 30;
144 D_SUBSCRIPTION_TYPE.DispHeight := 1;
145 D_SUBSCRIPTION_TYPE.MaxWidth := 30;
146 D_SUBSCRIPTION_TYPE.UseMeanings := True;
147 D_SUBSCRIPTION_TYPE.ColOptional := False;
148 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_SUBSCRIPTION_TYPE', D_SUBSCRIPTION_TYPE);
149 D_SUBSCRIPTION_TYPE.Initialised := True;
150 end if;
151
152 if P_ALIAS = 'INTERCONNECT_TYPE' and not D_INTERCONNECT_TYPE.Initialised then
153 D_INTERCONNECT_TYPE.ColAlias := 'INTERCONNECT_TYPE';
154 D_INTERCONNECT_TYPE.ControlType := XNP_WSGL.DV_TEXT;
155 D_INTERCONNECT_TYPE.DispWidth := 30;
156 D_INTERCONNECT_TYPE.DispHeight := 1;
157 D_INTERCONNECT_TYPE.MaxWidth := 30;
158 D_INTERCONNECT_TYPE.UseMeanings := True;
159 D_INTERCONNECT_TYPE.ColOptional := False;
160 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_INTERCONNECT_TYPE', D_INTERCONNECT_TYPE);
161 D_INTERCONNECT_TYPE.Initialised := True;
162 end if;
163
164 exception
165 when others then
166 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
167 DEF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.InitialseDomain');
168 end;
169
170 --------------------------------------------------------------------------------
171 -- Name: xnp_sv_network$sms_sv.Startup
172 --
173 -- Description: Entry point for the 'SMS_SV' module
174 -- component (Porting Network Subscriptions).
175 --
176 -- Parameters:
177 --
178 --------------------------------------------------------------------------------
179 procedure Startup(
180 Z_DIRECT_CALL in boolean,
181 Z_CHK in varchar2) is
182 begin
183
184 XNP_WSGL.RegisterURL('xnp_sv_network$sms_sv.startup');
185 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
186
187 if not Z_DIRECT_CALL then
188 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
189 return;
190 end if;
191 end if;
192
193 XNP_WSGL.StoreURLLink(1, 'Porting Network Subscriptions');
194
195
196 FormQuery(
197 Z_DIRECT_CALL=>TRUE);
198
199 exception
200 when others then
201 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
202 DEF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.Startup');
203 end;
204
205 --------------------------------------------------------------------------------
206 -- Name: xnp_sv_network$sms_sv.ActionQuery
207 --
208 -- Description: Called when a Query form is subitted to action the query request.
209 --
210 -- Parameters:
211 --
212 --------------------------------------------------------------------------------
213 procedure ActionQuery(
214 P_PORTING_ID in varchar2,
215 P_SUBSCRIPTION_TYPE in varchar2,
216 P_SUBSCRIPTION_TN in varchar2,
217 P_NRC_CODE in varchar2,
218 P_REC_CODE in varchar2,
219 Z_DIRECT_CALL in boolean default false,
220 Z_ACTION in varchar2,
221 Z_CHK in varchar2) is
222
223 I_PARAM_LIST varchar2(2000) := '?';
224 L_QRY_FIRST_ACTION varchar2(12) := 'INSERTIFNONE';
225 L_QRY_LIST_ACTION varchar2(10) := null;
226 L_CHK varchar2(10) := Z_CHK;
227 L_BUTCHK varchar2(100):= null;
228 begin
229
230 if Z_DIRECT_CALL then
231 L_CHK := to_char(XNP_WSGL.Checksum(''));
232 else
233 if not XNP_WSGL.ValidateChecksum('', L_CHK) then
234 return;
235 end if;
236 end if;
237
238 if P_PORTING_ID is not null then
239 I_PARAM_LIST := I_PARAM_LIST || 'P_PORTING_ID=' || XNP_WSGL.EscapeURLParam(P_PORTING_ID) || '&';
240 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
241 end if;
242 if P_SUBSCRIPTION_TYPE is not null then
243 I_PARAM_LIST := I_PARAM_LIST || 'P_SUBSCRIPTION_TYPE=' || XNP_WSGL.EscapeURLParam(P_SUBSCRIPTION_TYPE) || '&';
244 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
245 end if;
246 if P_SUBSCRIPTION_TN is not null then
247 I_PARAM_LIST := I_PARAM_LIST || 'P_SUBSCRIPTION_TN=' || XNP_WSGL.EscapeURLParam(P_SUBSCRIPTION_TN) || '&';
248 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
249 end if;
250 if P_NRC_CODE is not null then
251 I_PARAM_LIST := I_PARAM_LIST || 'P_NRC_CODE=' || XNP_WSGL.EscapeURLParam(P_NRC_CODE) || '&';
252 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
253 end if;
254 if P_REC_CODE is not null then
255 I_PARAM_LIST := I_PARAM_LIST || 'P_REC_CODE=' || XNP_WSGL.EscapeURLParam(P_REC_CODE) || '&';
256 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
257 end if;
258 I_PARAM_LIST := I_PARAM_LIST || 'Z_CHK=' || XNP_WSGL.EscapeURLParam(L_CHK) || '&';
259 I_PARAM_LIST := I_PARAM_LIST||'Z_ACTION=';
260
261 htp.p('<HTML>
262 <TITLE>Monitor Network Subscriptions : Porting Network Subscriptions</TITLE>
263 <FRAMESET ROWS="100,*,50">
264 ');
265 htp.noframesOpen;
266 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_INFORMATION, XNP_WSGL.MsgGetText(229,XNP_WSGLM.MSG229_NO_FRAME_SUPPORT),
267 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', DEF_BODY_ATTRIBUTES);
268 htp.noframesClose;
269 htp.p('<FRAME NAME="fraTOP" SRC="xnp_sv_network$sms_sv.textframe?Z_HEADER=Y&Z_TEXT=Y">
270 <FRAMESET COLS="30%,70%">
271 <FRAME NAME="fraRL" SRC="xnp_sv_network$sms_sv.querylist'||I_PARAM_LIST||L_QRY_LIST_ACTION||'">
272 <FRAME NAME="fraVF" SRC="xnp_sv_network$sms_sv.queryfirst'||I_PARAM_LIST||L_QRY_FIRST_ACTION||'">
273 </FRAMESET>
274 <FRAME NAME="fraBOTTOM" SRC="xnp_sv_network$sms_sv.textframe?Z_FOOTER=Y">
275 </FRAMESET>
276 </HTML>
277 ');
278 --if on the query form and insert is allowed
279
280 exception
281 when others then
282 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
283 DEF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.ActionQuery');
284 end;
285
286 --------------------------------------------------------------------------------
287 -- Name: xnp_sv_network$sms_sv.TextFrame
288 --
289 -- Description:
290 --
291 -- Parameters:
292 --
293 --------------------------------------------------------------------------------
294 procedure TextFrame(Z_HEADER in varchar2,
295 Z_FIRST in varchar2,
296 Z_TEXT in varchar2,
297 Z_FOOTER in varchar2) is
298 L_TEXT_TYPES number := 0;
299 L_ONLY_THING_IN_FRAME boolean := FALSE;
300 begin
301
302 if Z_HEADER is not null then
303 L_TEXT_TYPES := L_TEXT_TYPES + 1;
304 end if;
305 if Z_FIRST is not null then
306 L_TEXT_TYPES := L_TEXT_TYPES + 1;
307 end if;
308 if Z_TEXT is not null then
309 L_TEXT_TYPES := L_TEXT_TYPES + 1;
310 end if;
311 if Z_FOOTER is not null then
312 L_TEXT_TYPES := L_TEXT_TYPES + 1;
313 end if;
314
315 L_ONLY_THING_IN_FRAME := (L_TEXT_TYPES = 1);
316
317 XNP_WSGL.OpenPageHead('Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
318 L_ONLY_THING_IN_FRAME);
319 xnp_sv_network$.TemplateHeader(TRUE,0);
320 XNP_WSGL.ClosePageHead;
321
322 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>TF_BODY_ATTRIBUTES);
323
324 if Z_HEADER is not null then
325 htp.p(htf.bold(fnd_message.get_string('XNP','WEB_TITLE')));
326 end if;
327
328 if Z_FIRST is not null then
329 xnp_sv_network$.FirstPage(TRUE);
330 end if;
331
332 if Z_TEXT is not null then
333 htp.p(htf.header(1,fnd_message.get_string('XNP','WEB_XNPSVNWK_DETAILS_TITLE')));
334 end if;
335
336 if Z_FOOTER is not null then
337 htp.p(htf.img('/OA_MEDIA/FNDLOGOS.gif'));
338 end if;
339
340 XNP_WSGL.ClosePageBody;
341
342 exception
343 when others then
344 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
345 TF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.TextFrame');
346 XNP_WSGL.ClosePageBody;
347 end;
348 --------------------------------------------------------------------------------
349 -- Name: xnp_sv_network$sms_sv.QueryHits
350 --
351 -- Description: Returns the number or rows which matches the given search
352 -- criteria (if any).
353 --
354 -- Parameters:
355 --
356 --------------------------------------------------------------------------------
357 function QueryHits(
358 P_PORTING_ID in varchar2,
359 P_SUBSCRIPTION_TYPE in varchar2,
360 P_SUBSCRIPTION_TN in varchar2,
361 P_NRC_CODE in varchar2,
362 P_REC_CODE in varchar2) return number is
363 I_QUERY varchar2(2000) := '';
364 I_CURSOR integer;
365 I_VOID integer;
366 I_FROM_POS integer := 0;
367 I_COUNT number(10);
368 begin
369
370 if not BuildSQL(P_PORTING_ID,
371 P_SUBSCRIPTION_TYPE,
372 P_SUBSCRIPTION_TN,
373 P_NRC_CODE,
374 P_REC_CODE) then
375 return -1;
376 end if;
377
378 if not PreQuery(P_PORTING_ID,
379 P_SUBSCRIPTION_TYPE,
380 P_SUBSCRIPTION_TN,
381 P_NRC_CODE,
382 P_REC_CODE) then
383 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
384 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', DEF_BODY_ATTRIBUTES);
385 return -1;
386 end if;
387
388 I_FROM_POS := instr(upper(ZONE_SQL), ' FROM ');
389
390 if I_FROM_POS = 0 then
391 return -1;
392 end if;
393
394 I_QUERY := 'SELECT count(*)' ||
395 substr(ZONE_SQL, I_FROM_POS);
396
397 I_CURSOR := dbms_sql.open_cursor;
398 dbms_sql.parse(I_CURSOR, I_QUERY, dbms_sql.v7);
399 dbms_sql.define_column(I_CURSOR, 1, I_COUNT);
400 I_VOID := dbms_sql.execute(I_CURSOR);
401 I_VOID := dbms_sql.fetch_rows(I_CURSOR);
402 dbms_sql.column_value(I_CURSOR, 1, I_COUNT);
403 dbms_sql.close_cursor(I_CURSOR);
404
405 return I_COUNT;
406
407 exception
408 when others then
409 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
410 DEF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.QueryHits');
411 return -1;
412 end;--------------------------------------------------------------------------------
413 -- Name: xnp_sv_network$sms_sv.BuildSQL
414 --
415 -- Description: Builds the SQL for the 'SMS_SV' module component (Porting Network Subscriptions).
416 -- This incorporates all query criteria and Foreign key columns.
417 --
418 -- Parameters:
419 --
420 --------------------------------------------------------------------------------
421 function BuildSQL(
422 P_PORTING_ID in varchar2,
423 P_SUBSCRIPTION_TYPE in varchar2,
424 P_SUBSCRIPTION_TN in varchar2,
425 P_NRC_CODE in varchar2,
426 P_REC_CODE in varchar2) return boolean is
427
428 I_WHERE varchar2(2000);
429 begin
430
431 InitialiseDomain('SUBSCRIPTION_TYPE');
432
433 -- Build up the Where clause
434
435 XNP_WSGL.BuildWhere(P_PORTING_ID, 'XNP_SV_SMS_V.PORTING_ID', XNP_WSGL.TYPE_CHAR, I_WHERE);
436 XNP_WSGL.BuildWhere(XNP_WSGL.DomainValue(D_SUBSCRIPTION_TYPE, P_SUBSCRIPTION_TYPE), 'XNP_SV_SMS_V.SUBSCRIPTION_TYPE', XNP_WSGL.TYPE_CHAR, I_WHERE);
437 XNP_WSGL.BuildWhere(P_SUBSCRIPTION_TN, 'XNP_SV_SMS_V.SUBSCRIPTION_TN', XNP_WSGL.TYPE_CHAR, I_WHERE);
438 XNP_WSGL.BuildWhere(P_NRC_CODE, 'XNP_SV_SMS_V.NRC_CODE', XNP_WSGL.TYPE_CHAR, I_WHERE);
439 XNP_WSGL.BuildWhere(P_REC_CODE, 'XNP_SV_SMS_V.REC_CODE', XNP_WSGL.TYPE_CHAR, I_WHERE);
440
441 ZONE_SQL := 'SELECT XNP_SV_SMS_V.SV_SMS_ID,
442 XNP_SV_SMS_V.PORTING_ID,
443 XNP_SV_SMS_V.SUBSCRIPTION_TN,
444 XNP_SV_SMS_V.ROUTING_NUMBER
445 FROM XNP_SV_SMS_V XNP_SV_SMS_V';
446
447 ZONE_SQL := ZONE_SQL || I_WHERE;
448 ZONE_SQL := ZONE_SQL || ' ORDER BY XNP_SV_SMS_V.PORTING_ID Desc ';
449 return true;
450
451 exception
452 when others then
453 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
454 DEF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.BuildSQL');
455 return false;
456 end;
457
458
459 --------------------------------------------------------------------------------
460 -- Name: xnp_sv_network$sms_sv.FormQuery
461 --
462 -- Description: This procedure builds an HTML form for entry of query criteria.
463 -- The criteria entered are to restrict the query of the 'SMS_SV'
464 -- module component (Porting Network Subscriptions).
465 --
466 -- Parameters:
467 --
468 --------------------------------------------------------------------------------
469 procedure FormQuery(
470 Z_DIRECT_CALL in boolean,
471 Z_CHK in varchar2) is
472 begin
473
474 if not Z_DIRECT_CALL then
475 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
476 return;
477 end if;
478 end if;
479
480 XNP_WSGL.OpenPageHead('Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions');
481 CreateQueryJavaScript;
482 xnp_sv_network$.TemplateHeader(TRUE,0);
483 XNP_WSGL.ClosePageHead;
484
485 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>QF_BODY_ATTRIBUTES);
486
487 htp.p(htf.bold(fnd_message.get_string('XNP','WEB_TITLE')));
488 xnp_sv_network$.FirstPage(TRUE);
489 htp.p(htf.header(1,fnd_message.get_string('XNP','WEB_XNPSVNWK_DETAILS_TITLE')));
490 htp.para;
491 htp.p(XNP_WSGL.MsgGetText(116,XNP_WSGLM.DSP116_ENTER_QRY_CAPTION,'Porting Network Subscriptions'));
492 htp.para;
493
494 htp.formOpen(curl => 'xnp_sv_network$sms_sv.actionquery', cattributes => 'NAME="frmZero"');
495
496 XNP_WSGL.LayoutOpen(XNP_WSGL.LAYOUT_TABLE);
497
498 XNP_WSGL.LayoutRowStart;
499 for i in 1..QF_NUMBER_OF_COLUMNS loop
500 XNP_WSGL.LayoutHeader(18, 'LEFT', NULL);
501 XNP_WSGL.LayoutHeader(30, 'LEFT', NULL);
502 end loop;
503 XNP_WSGL.LayoutRowEnd;
504
505 XNP_WSGL.LayoutRowStart('TOP');
506 XNP_WSGL.LayoutData(htf.bold('Porting ID:'));
507 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('PORTING_ID', '10', FALSE));
508 XNP_WSGL.LayoutRowEnd;
509 XNP_WSGL.LayoutRowStart('TOP');
510 XNP_WSGL.LayoutData(htf.bold('Subscription Type:'));
511 InitialiseDomain('SUBSCRIPTION_TYPE');
512 XNP_WSGL.LayoutData(XNP_WSGL.BuildDVControl(D_SUBSCRIPTION_TYPE, XNP_WSGL.CTL_QUERY));
513 XNP_WSGL.LayoutRowEnd;
514 XNP_WSGL.LayoutRowStart('TOP');
515 XNP_WSGL.LayoutData(htf.bold('Telephone:'));
516 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('SUBSCRIPTION_TN', '20', FALSE));
517 XNP_WSGL.LayoutRowEnd;
518 XNP_WSGL.LayoutRowStart('TOP');
519 XNP_WSGL.LayoutData(htf.bold('NRC Code:'));
520 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('NRC_CODE', '10', FALSE));
521 XNP_WSGL.LayoutRowEnd;
522 XNP_WSGL.LayoutRowStart('TOP');
523 XNP_WSGL.LayoutData(htf.bold('Rec Code:'));
524 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('REC_CODE', '20', FALSE));
525 XNP_WSGL.LayoutRowEnd;
526
527 XNP_WSGL.LayoutClose;
528
529 htp.p ('<SCRIPT><!--');
530 htp.p ('document.write (''<input type=hidden name="Z_ACTION">'')');
531 htp.p ('//-->');
532 htp.p ('</SCRIPT>');
533 htp.p ('<SCRIPT><!--');
534 htp.p ('document.write (''' || htf.formSubmit('', htf.escape_sc(QF_QUERY_BUT_CAPTION), 'onClick="this.form.Z_ACTION.value=\''' || QF_QUERY_BUT_ACTION || '\''"') || ''')');
535 htp.p ('//-->');
536 htp.p ('</SCRIPT>');
537
538 if XNP_WSGL.IsSupported ('NOSCRIPT')
539 then
540
541 htp.p ('<NOSCRIPT>');
542 htp.formSubmit('Z_ACTION', htf.escape_sc(QF_QUERY_BUT_CAPTION));
543 htp.p ('</NOSCRIPT>');
544
545 end if;
546 htp.formReset(htf.escape_sc(QF_CLEAR_BUT_CAPTION));
547
548 XNP_WSGL.HiddenField('Z_CHK',
549 to_char(XNP_WSGL.Checksum('')));
550 htp.formClose;
551
552 htp.p(htf.img('/OA_MEDIA/FNDLOGOS.gif'));
553
554 XNP_WSGL.ClosePageBody;
555
556 exception
557 when others then
558 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
559 QF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.FormQuery');
560 XNP_WSGL.ClosePageBody;
561 end;
562
563 --------------------------------------------------------------------------------
564 -- Name: xnp_sv_network$sms_sv.FormView
565 --
566 -- Description: This procedure builds an HTML form for view/update of fields in
567 -- the 'SMS_SV' module component (Porting Network Subscriptions).
568 --
569 -- Parameters: Z_FORM_STATUS Status of the form
570 --
571 --------------------------------------------------------------------------------
572 procedure FormView(Z_FORM_STATUS in number) is
573
574 SMS_FE_MAP_CHCK varchar2(10);
575 SMS_ORDER_CHCK varchar2(10);
576
577 begin
578
579 XNP_WSGL.OpenPageHead('Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions');
580 xnp_sv_network$.TemplateHeader(TRUE,0);
581 XNP_WSGL.ClosePageHead;
582
583 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>VF_BODY_ATTRIBUTES);
584
585 InitialiseDomain('SUBSCRIPTION_TYPE');
586 InitialiseDomain('INTERCONNECT_TYPE');
587
588
589
590 FORM_VAL.SV_SMS_ID := CURR_VAL.SV_SMS_ID;
591 FORM_VAL.PORTING_ID := CURR_VAL.PORTING_ID;
592 FORM_VAL.ROUTING_NUMBER_ID := CURR_VAL.ROUTING_NUMBER_ID;
593 FORM_VAL.SUBSCRIPTION_TYPE := XNP_WSGL.DomainMeaning(D_SUBSCRIPTION_TYPE, CURR_VAL.SUBSCRIPTION_TYPE);
594 FORM_VAL.SUBSCRIPTION_TN := CURR_VAL.SUBSCRIPTION_TN;
595 FORM_VAL.MEDIATOR_SP_ID := CURR_VAL.MEDIATOR_SP_ID;
596 FORM_VAL.ROUTING_NUMBER := CURR_VAL.ROUTING_NUMBER;
597 FORM_VAL.PROVISION_SENT_DATE := ltrim(to_char(CURR_VAL.PROVISION_SENT_DATE, 'DD-MON-YYYY HH:MI:SS AM'));
598 FORM_VAL.CNAM_ADDRESS := CURR_VAL.CNAM_ADDRESS;
599 FORM_VAL.CNAM_SUBSYSTEM := CURR_VAL.CNAM_SUBSYSTEM;
600 FORM_VAL.ISVM_ADDRESS := CURR_VAL.ISVM_ADDRESS;
601 FORM_VAL.ISVM_SUBSYSTEM := CURR_VAL.ISVM_SUBSYSTEM;
602 FORM_VAL.LIDB_ADDRESS := CURR_VAL.LIDB_ADDRESS;
603 FORM_VAL.LIDB_SUBSYSTEM := CURR_VAL.LIDB_SUBSYSTEM;
604 FORM_VAL.CLASS_ADDRESS := CURR_VAL.CLASS_ADDRESS;
605 FORM_VAL.CLASS_SUBSYSTEM := CURR_VAL.CLASS_SUBSYSTEM;
606 FORM_VAL.WSMSC_ADDRESS := CURR_VAL.WSMSC_ADDRESS;
607 FORM_VAL.WSMSC_SUBSYSTEM := CURR_VAL.WSMSC_SUBSYSTEM;
608 FORM_VAL.RN_ADDRESS := CURR_VAL.RN_ADDRESS;
609 FORM_VAL.RN_SUBSYSTEM := CURR_VAL.RN_SUBSYSTEM;
610 FORM_VAL.PROVISION_DONE_DATE := ltrim(to_char(CURR_VAL.PROVISION_DONE_DATE, 'DD-MON-YYYY HH:MI:SS AM'));
611 FORM_VAL.NRC_CODE := CURR_VAL.NRC_CODE;
612 FORM_VAL.NRC_NAME := CURR_VAL.NRC_NAME;
613 FORM_VAL.NRC_INTERNET := CURR_VAL.NRC_INTERNET;
614 FORM_VAL.ROUTING_REF := CURR_VAL.ROUTING_REF;
615 FORM_VAL.ROUTING_SP_ID := CURR_VAL.ROUTING_SP_ID;
616 FORM_VAL.REC_SP_ID := CURR_VAL.REC_SP_ID;
617 FORM_VAL.REC_CODE := CURR_VAL.REC_CODE;
618 FORM_VAL.REC_NAME := CURR_VAL.REC_NAME;
619 FORM_VAL.REC_INTERNET := CURR_VAL.REC_INTERNET;
620 FORM_VAL.DSP_REC := NBT_VAL.DSP_REC;
621 FORM_VAL.REC_EMAIL := CURR_VAL.REC_EMAIL;
622 FORM_VAL.DSP_NRC := NBT_VAL.DSP_NRC;
623 FORM_VAL.NRC_EMAIL := CURR_VAL.NRC_EMAIL;
624 FORM_VAL.INTERCONNECT_TYPE := XNP_WSGL.DomainMeaning(D_INTERCONNECT_TYPE, CURR_VAL.INTERCONNECT_TYPE);
625 FORM_VAL.DSP_CNAM := NBT_VAL.DSP_CNAM;
626 FORM_VAL.DSP_CLASS := NBT_VAL.DSP_CLASS;
627 FORM_VAL.DSP_ISVM := NBT_VAL.DSP_ISVM;
628 FORM_VAL.DSP_LIDB := NBT_VAL.DSP_LIDB;
629 FORM_VAL.DSP_WSMSC := NBT_VAL.DSP_WSMSC;
630 FORM_VAL.DSP_RN := NBT_VAL.DSP_RN;
631
632 if Z_FORM_STATUS = XNP_WSGL.FORM_STATUS_ERROR then
633 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
634 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', VF_BODY_ATTRIBUTES);
635 elsif Z_FORM_STATUS = XNP_WSGL.FORM_STATUS_UPD then
636 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_SUCCESS, XNP_WSGL.MsgGetText(207, XNP_WSGLM.MSG207_ROW_UPDATED),
637 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', VF_BODY_ATTRIBUTES);
638 elsif Z_FORM_STATUS = XNP_WSGL.FORM_STATUS_INS then
639 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_SUCCESS, XNP_WSGL.MsgGetText(208, XNP_WSGLM.MSG208_ROW_INSERTED),
640 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', VF_BODY_ATTRIBUTES);
641 end if;
642
643
644 XNP_WSGL.LayoutOpen(XNP_WSGL.LAYOUT_TABLE, P_BORDER=>TRUE);
645
646 XNP_WSGL.LayoutRowStart;
647 for i in 1..VF_NUMBER_OF_COLUMNS loop
648 XNP_WSGL.LayoutHeader(20, 'LEFT', NULL);
649 XNP_WSGL.LayoutHeader(80, 'LEFT', NULL);
650 end loop;
651 XNP_WSGL.LayoutRowEnd;
652
653 XNP_WSGL.LayoutRowStart('TOP');
654 XNP_WSGL.LayoutData(htf.bold('Porting ID:'));
655 XNP_WSGL.LayoutData(htf.bold(FORM_VAL.PORTING_ID));
656 XNP_WSGL.LayoutRowEnd;
657 XNP_WSGL.LayoutRowStart('TOP');
658 XNP_WSGL.LayoutData(htf.bold('Subscription Type:'));
659 XNP_WSGL.LayoutData(FORM_VAL.SUBSCRIPTION_TYPE);
660 XNP_WSGL.LayoutRowEnd;
661 XNP_WSGL.LayoutRowStart('TOP');
662 XNP_WSGL.LayoutData(htf.bold('Telephone:'));
663 XNP_WSGL.LayoutData(htf.bold(FORM_VAL.SUBSCRIPTION_TN));
664 XNP_WSGL.LayoutRowEnd;
665 XNP_WSGL.LayoutRowStart('TOP');
666 XNP_WSGL.LayoutData(htf.bold('Routing Number:'));
667 XNP_WSGL.LayoutData(htf.bold(FORM_VAL.ROUTING_NUMBER));
668 XNP_WSGL.LayoutRowEnd;
669 XNP_WSGL.LayoutRowStart('TOP');
670 XNP_WSGL.LayoutData(htf.bold('Provision Sent Date:'));
671 XNP_WSGL.LayoutData(FORM_VAL.PROVISION_SENT_DATE);
672 XNP_WSGL.LayoutRowEnd;
673 XNP_WSGL.LayoutRowStart('TOP');
674 XNP_WSGL.LayoutData(htf.bold('Provision Done Date:'));
675 XNP_WSGL.LayoutData(FORM_VAL.PROVISION_DONE_DATE);
676 XNP_WSGL.LayoutRowEnd;
677 if NBT_VAL.DSP_REC is not null then
678 XNP_WSGL.LayoutRowStart('TOP');
679 XNP_WSGL.LayoutData(htf.bold('Recipient:'));
680 XNP_WSGL.LayoutData(FORM_VAL.DSP_REC);
681 XNP_WSGL.LayoutRowEnd;
682 end if;
683 XNP_WSGL.LayoutRowStart('TOP');
684 XNP_WSGL.LayoutData(htf.bold('Rec. Email:'));
685 XNP_WSGL.LayoutData(htf.mailto(FORM_VAL.REC_EMAIL, FORM_VAL.REC_EMAIL));
686 XNP_WSGL.LayoutRowEnd;
687 if NBT_VAL.DSP_NRC is not null then
688 XNP_WSGL.LayoutRowStart('TOP');
689 XNP_WSGL.LayoutData(htf.bold('Mediator:'));
690 XNP_WSGL.LayoutData(FORM_VAL.DSP_NRC);
691 XNP_WSGL.LayoutRowEnd;
692 end if;
693 XNP_WSGL.LayoutRowStart('TOP');
694 XNP_WSGL.LayoutData(htf.bold('NRC Email:'));
695 XNP_WSGL.LayoutData(htf.mailto(FORM_VAL.NRC_EMAIL, FORM_VAL.NRC_EMAIL));
696 XNP_WSGL.LayoutRowEnd;
697 XNP_WSGL.LayoutRowStart('TOP');
698 XNP_WSGL.LayoutData(htf.bold('Interconnect Type:'));
699 XNP_WSGL.LayoutData(FORM_VAL.INTERCONNECT_TYPE);
700 XNP_WSGL.LayoutRowEnd;
701 if NBT_VAL.DSP_CNAM is not null then
702 XNP_WSGL.LayoutRowStart('TOP');
703 XNP_WSGL.LayoutData(htf.bold('CNAM Details:'));
704 XNP_WSGL.LayoutData(FORM_VAL.DSP_CNAM);
705 XNP_WSGL.LayoutRowEnd;
706 end if;
707 if NBT_VAL.DSP_CLASS is not null then
708 XNP_WSGL.LayoutRowStart('TOP');
709 XNP_WSGL.LayoutData(htf.bold('CLASS Details:'));
710 XNP_WSGL.LayoutData(FORM_VAL.DSP_CLASS);
711 XNP_WSGL.LayoutRowEnd;
712 end if;
713 if NBT_VAL.DSP_ISVM is not null then
714 XNP_WSGL.LayoutRowStart('TOP');
715 XNP_WSGL.LayoutData(htf.bold('ISVM Details:'));
716 XNP_WSGL.LayoutData(FORM_VAL.DSP_ISVM);
717 XNP_WSGL.LayoutRowEnd;
718 end if;
719 if NBT_VAL.DSP_LIDB is not null then
720 XNP_WSGL.LayoutRowStart('TOP');
721 XNP_WSGL.LayoutData(htf.bold('LIDB Details:'));
722 XNP_WSGL.LayoutData(FORM_VAL.DSP_LIDB);
723 XNP_WSGL.LayoutRowEnd;
724 end if;
725 if NBT_VAL.DSP_WSMSC is not null then
726 XNP_WSGL.LayoutRowStart('TOP');
727 XNP_WSGL.LayoutData(htf.bold('WSMSC Details:'));
728 XNP_WSGL.LayoutData(FORM_VAL.DSP_WSMSC);
729 XNP_WSGL.LayoutRowEnd;
730 end if;
731 if NBT_VAL.DSP_RN is not null then
732 XNP_WSGL.LayoutRowStart('TOP');
733 XNP_WSGL.LayoutData(htf.bold('RN Details:'));
734 XNP_WSGL.LayoutData(FORM_VAL.DSP_RN);
735 XNP_WSGL.LayoutRowEnd;
736 end if;
737
738 XNP_WSGL.LayoutClose;
739
740
741
742
743 SMS_FE_MAP_CHCK :=
744 to_char(XNP_WSGL.Checksum(CURR_VAL.SV_SMS_ID));
745 SMS_ORDER_CHCK :=
746 to_char(XNP_WSGL.Checksum(CURR_VAL.SV_SMS_ID));
747
748
749 XNP_WSGL.NavLinks(XNP_WSGL.MENU_LONG, 'Provisioning Status',
750 0, 'xnp_sv_network$sms_fe_map.startup?P_SV_SMS_ID='||XNP_WSGL.EscapeURLParam(CURR_VAL.SV_SMS_ID)||'&Z_CHK='||SMS_FE_MAP_CHCK);
751 XNP_WSGL.NavLinks(XNP_WSGL.MENU_LONG, 'Order Workitems',
752 0, 'xnp_sv_network$sms_order.startup?P_SV_SMS_ID='||XNP_WSGL.EscapeURLParam(CURR_VAL.SV_SMS_ID)||'&Z_CHK='||SMS_ORDER_CHCK);
753 XNP_WSGL.NavLinks;
754
755
756 XNP_WSGL.ClosePageBody;
757
758 exception
759 when others then
760 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
761 VF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.FormView');
762 XNP_WSGL.ClosePageBody;
763 end;
764
765 --------------------------------------------------------------------------------
766 -- Name: xnp_sv_network$sms_sv.QueryView
767 --
768 -- Description: Queries the details of a single row in preparation for display.
769 --
770 -- Parameters:
771 --
772 --------------------------------------------------------------------------------
773 procedure QueryView(
774 P_SV_SMS_ID in varchar2,
775 Z_POST_DML in boolean,
776 Z_FORM_STATUS in number,
777 Z_DIRECT_CALL in boolean,
778 Z_CHK in varchar2) is
779 begin
780
781 XNP_WSGL.RegisterURL('xnp_sv_network$sms_sv.queryview');
782 XNP_WSGL.AddURLParam('P_SV_SMS_ID', P_SV_SMS_ID);
783 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
784 if not Z_DIRECT_CALL then
785 if not XNP_WSGL.ValidateChecksum('' ||
786 P_SV_SMS_ID, Z_CHK) then
787 return;
788 end if;
789 end if;
790
791
792 if P_SV_SMS_ID is not null then
793 CURR_VAL.SV_SMS_ID := P_SV_SMS_ID;
794 end if;
795 if Z_POST_DML then
796
797 null;
798
799 else
800
801 SELECT XNP_SV_SMS_V.SV_SMS_ID,
802 XNP_SV_SMS_V.PORTING_ID,
803 XNP_SV_SMS_V.ROUTING_NUMBER_ID,
804 XNP_SV_SMS_V.SUBSCRIPTION_TYPE,
805 XNP_SV_SMS_V.SUBSCRIPTION_TN,
806 XNP_SV_SMS_V.MEDIATOR_SP_ID,
807 XNP_SV_SMS_V.ROUTING_NUMBER,
808 XNP_SV_SMS_V.PROVISION_SENT_DATE,
809 XNP_SV_SMS_V.CNAM_ADDRESS,
810 XNP_SV_SMS_V.CNAM_SUBSYSTEM,
811 XNP_SV_SMS_V.ISVM_ADDRESS,
812 XNP_SV_SMS_V.ISVM_SUBSYSTEM,
813 XNP_SV_SMS_V.LIDB_ADDRESS,
814 XNP_SV_SMS_V.LIDB_SUBSYSTEM,
815 XNP_SV_SMS_V.CLASS_ADDRESS,
816 XNP_SV_SMS_V.CLASS_SUBSYSTEM,
817 XNP_SV_SMS_V.WSMSC_ADDRESS,
818 XNP_SV_SMS_V.WSMSC_SUBSYSTEM,
819 XNP_SV_SMS_V.RN_ADDRESS,
820 XNP_SV_SMS_V.RN_SUBSYSTEM,
821 XNP_SV_SMS_V.PROVISION_DONE_DATE,
822 XNP_SV_SMS_V.NRC_CODE,
823 XNP_SV_SMS_V.NRC_NAME,
824 XNP_SV_SMS_V.NRC_INTERNET,
825 XNP_SV_SMS_V.ROUTING_REF,
826 XNP_SV_SMS_V.ROUTING_SP_ID,
827 XNP_SV_SMS_V.REC_SP_ID,
828 XNP_SV_SMS_V.REC_CODE,
829 XNP_SV_SMS_V.REC_NAME,
830 XNP_SV_SMS_V.REC_INTERNET,
831 XNP_SV_SMS_V.REC_EMAIL,
832 XNP_SV_SMS_V.NRC_EMAIL,
833 XNP_SV_SMS_V.INTERCONNECT_TYPE
834 INTO CURR_VAL.SV_SMS_ID,
835 CURR_VAL.PORTING_ID,
836 CURR_VAL.ROUTING_NUMBER_ID,
837 CURR_VAL.SUBSCRIPTION_TYPE,
838 CURR_VAL.SUBSCRIPTION_TN,
839 CURR_VAL.MEDIATOR_SP_ID,
840 CURR_VAL.ROUTING_NUMBER,
841 CURR_VAL.PROVISION_SENT_DATE,
842 CURR_VAL.CNAM_ADDRESS,
843 CURR_VAL.CNAM_SUBSYSTEM,
844 CURR_VAL.ISVM_ADDRESS,
845 CURR_VAL.ISVM_SUBSYSTEM,
846 CURR_VAL.LIDB_ADDRESS,
847 CURR_VAL.LIDB_SUBSYSTEM,
848 CURR_VAL.CLASS_ADDRESS,
849 CURR_VAL.CLASS_SUBSYSTEM,
850 CURR_VAL.WSMSC_ADDRESS,
851 CURR_VAL.WSMSC_SUBSYSTEM,
852 CURR_VAL.RN_ADDRESS,
853 CURR_VAL.RN_SUBSYSTEM,
854 CURR_VAL.PROVISION_DONE_DATE,
855 CURR_VAL.NRC_CODE,
856 CURR_VAL.NRC_NAME,
857 CURR_VAL.NRC_INTERNET,
858 CURR_VAL.ROUTING_REF,
859 CURR_VAL.ROUTING_SP_ID,
860 CURR_VAL.REC_SP_ID,
861 CURR_VAL.REC_CODE,
862 CURR_VAL.REC_NAME,
863 CURR_VAL.REC_INTERNET,
864 CURR_VAL.REC_EMAIL,
865 CURR_VAL.NRC_EMAIL,
866 CURR_VAL.INTERCONNECT_TYPE
867 FROM XNP_SV_SMS_V XNP_SV_SMS_V
868 WHERE XNP_SV_SMS_V.SV_SMS_ID = CURR_VAL.SV_SMS_ID
869 ;
870
871 end if;
872
873 NBT_VAL.DSP_REC := ( CURR_VAL.REC_CODE || ' - ' || CURR_VAL.REC_NAME );
874 NBT_VAL.DSP_NRC := ( CURR_VAL.NRC_CODE || ' - ' || CURR_VAL.NRC_NAME );
875 NBT_VAL.DSP_CNAM := ('Address : ' || CURR_VAL.CNAM_ADDRESS || ' - Subsystem : ' || CURR_VAL.CNAM_SUBSYSTEM);
876 NBT_VAL.DSP_CLASS := ('Address : ' || CURR_VAL.CLASS_ADDRESS || ' - Subsystem : ' || CURR_VAL.CLASS_SUBSYSTEM);
877 NBT_VAL.DSP_ISVM := ('Address : ' || CURR_VAL.ISVM_ADDRESS || ' - Subsystem : ' || CURR_VAL.ISVM_SUBSYSTEM);
878 NBT_VAL.DSP_LIDB := ('Address : ' || CURR_VAL.LIDB_ADDRESS || ' - Subsystem : ' || CURR_VAL.LIDB_SUBSYSTEM);
879 NBT_VAL.DSP_WSMSC := ('Address : ' || CURR_VAL.WSMSC_ADDRESS || ' - Subsystem : ' || CURR_VAL.WSMSC_SUBSYSTEM);
880 NBT_VAL.DSP_RN := ('Address : ' || CURR_VAL.RN_ADDRESS || ' - Subsystem : ' || CURR_VAL.RN_SUBSYSTEM);
881
882 if not PostQuery(Z_POST_DML) then
883 FormView(XNP_WSGL.FORM_STATUS_ERROR);
884 else
885 FormView(Z_FORM_STATUS);
886 end if;
887
888 exception
889 when NO_DATA_FOUND then
890 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_WSGL.MsgGetText(204, XNP_WSGLM.MSG204_ROW_DELETED),
891 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', VF_BODY_ATTRIBUTES);
892 when others then
893 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
894 VF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.QueryView');
895 end;
896 --------------------------------------------------------------------------------
897 -- Name: xnp_sv_network$sms_sv.QueryList
898 --
899 -- Description: This procedure builds the Record list for the 'SMS_SV'
900 -- module component (Porting Network Subscriptions).
901 --
902 -- The Record List displays context information for records which
903 -- match the specified query criteria.
904 -- Sets of records are displayed (10 records at a time)
905 -- with Next/Previous buttons to get other record sets.
906 --
907 -- The first context column will be created as a link to the
908 -- xnp_sv_network$sms_sv.FormView procedure for display of more details
909 -- of that particular row.
910 --
911 -- Parameters: P_PORTING_ID - Porting ID
912 -- P_SUBSCRIPTION_TYPE - Subscription Type
913 -- P_SUBSCRIPTION_TN - Telephone
914 -- P_NRC_CODE - NRC Code
915 -- P_REC_CODE - Rec Code
916 -- Z_START - First record to display
917 -- Z_ACTION - Next or Previous set
918 --
919 --------------------------------------------------------------------------------
920 procedure QueryList(
921 P_PORTING_ID in varchar2,
922 P_SUBSCRIPTION_TYPE in varchar2,
923 P_SUBSCRIPTION_TN in varchar2,
924 P_NRC_CODE in varchar2,
925 P_REC_CODE in varchar2,
926 Z_START in varchar2,
927 Z_ACTION in varchar2,
928 Z_DIRECT_CALL in boolean,
929 Z_CHK in varchar2) is
930
931 L_VF_FRAME varchar2(20) := 'fraVF';
932 I_WHERE varchar2(2000) := '';
933 I_CURSOR integer;
934 I_VOID integer;
935 I_ROWS_FETCHED integer := 0;
936 I_TOTAL_ROWS integer := 0;
937 I_START number(6) := to_number(Z_START);
938 I_COUNT number(10) := 0;
939 I_OF_TOTAL_TEXT varchar2(200) := '';
940 I_NEXT_BUT boolean;
941 I_PREV_BUT boolean;
942 SMS_FE_MAP_CHCK varchar2(10);
943 SMS_ORDER_CHCK varchar2(10);
944 L_CHECKSUM varchar2(10);
945
946 begin
947
948 XNP_WSGL.RegisterURL('xnp_sv_network$sms_sv.querylist');
949 XNP_WSGL.AddURLParam('P_PORTING_ID', P_PORTING_ID);
950 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TYPE', P_SUBSCRIPTION_TYPE);
951 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TN', P_SUBSCRIPTION_TN);
952 XNP_WSGL.AddURLParam('P_NRC_CODE', P_NRC_CODE);
953 XNP_WSGL.AddURLParam('P_REC_CODE', P_REC_CODE);
954 XNP_WSGL.AddURLParam('Z_START', Z_START);
955 XNP_WSGL.AddURLParam('Z_ACTION', Z_ACTION);
956 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
957 if not Z_DIRECT_CALL then
958 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
959 return;
960 end if;
961 end if;
962
963 if (Z_ACTION = RL_QUERY_BUT_ACTION) or (Z_ACTION = RL_QUERY_BUT_CAPTION) then
964 FormQuery(
965 Z_DIRECT_CALL=>TRUE);
966 return;
967 end if;
968
969 XNP_WSGL.OpenPageHead('Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions');
970 CreateListJavaScript;
971 xnp_sv_network$.TemplateHeader(TRUE,0);
972 XNP_WSGL.ClosePageHead;
973
974 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>RL_BODY_ATTRIBUTES);
975
976 if (Z_ACTION = RL_LAST_BUT_ACTION) or (Z_ACTION = RL_LAST_BUT_CAPTION) or
977 (Z_ACTION = RL_COUNT_BUT_ACTION) or (Z_ACTION = RL_COUNT_BUT_CAPTION) or
978 (RL_TOTAL_COUNT_REQD)
979 then
980
981 I_COUNT := QueryHits(
982 P_PORTING_ID,
983 P_SUBSCRIPTION_TYPE,
984 P_SUBSCRIPTION_TN,
985 P_NRC_CODE,
986 P_REC_CODE);
987 if I_COUNT = -1 then
988 XNP_WSGL.ClosePageBody;
989 return;
990 end if;
991 end if;
992
993 if (Z_ACTION = RL_COUNT_BUT_ACTION) or (Z_ACTION = RL_COUNT_BUT_CAPTION) or
994 RL_TOTAL_COUNT_REQD then
995 I_OF_TOTAL_TEXT := ' '||XNP_WSGL.MsgGetText(111,XNP_WSGLM.DSP111_OF_TOTAL, to_char(I_COUNT));
996 end if;
997
998 if Z_START IS NULL or (Z_ACTION = RL_FIRST_BUT_ACTION) or (Z_ACTION = RL_FIRST_BUT_CAPTION) then
999 I_START := 1;
1000 elsif (Z_ACTION = RL_NEXT_BUT_ACTION) or (Z_ACTION = RL_NEXT_BUT_CAPTION) then
1001 I_START := I_START + RL_RECORD_SET_SIZE;
1002 elsif (Z_ACTION = RL_PREV_BUT_ACTION) or (Z_ACTION = RL_PREV_BUT_CAPTION) then
1003 I_START := I_START - RL_RECORD_SET_SIZE;
1004 elsif (Z_ACTION = RL_LAST_BUT_ACTION) or (Z_ACTION = RL_LAST_BUT_CAPTION) then
1005 I_START := 1 + (floor((I_COUNT-1)/RL_RECORD_SET_SIZE)*RL_RECORD_SET_SIZE);
1006 elsif Z_ACTION is null and I_START = 1 then
1007 null;
1008 elsif Z_ACTION IS NULL then
1009 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_WSGL.MsgGetText(215,XNP_WSGLM.MSG215_NO_MULTIPLE_SUBMITS),
1010 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', RL_BODY_ATTRIBUTES);
1011 XNP_WSGL.ClosePageBody;
1012 return;
1013 end if;
1014
1015 if I_START < 1 then
1016 I_START := 1;
1017 end if;
1018
1019 I_PREV_BUT := TRUE;
1020 I_NEXT_BUT := FALSE;
1021 if I_START = 1 or Z_ACTION IS NULL then
1022 I_PREV_BUT := FALSE;
1023 end if;
1024
1025 if nvl(Z_ACTION, 'X') <> 'DONTQUERY' then
1026
1027 if ZONE_SQL IS NULL then
1028 if not BuildSQL(
1029 P_PORTING_ID,
1030 P_SUBSCRIPTION_TYPE,
1031 P_SUBSCRIPTION_TN,
1032 P_NRC_CODE,
1033 P_REC_CODE) then
1034 XNP_WSGL.ClosePageBody;
1035 return;
1036 end if;
1037 end if;
1038
1039 if not PreQuery(
1040 P_PORTING_ID,
1041 P_SUBSCRIPTION_TYPE,
1042 P_SUBSCRIPTION_TN,
1043 P_NRC_CODE,
1044 P_REC_CODE) then
1045 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
1046 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', RL_BODY_ATTRIBUTES);
1047 return;
1048 end if;
1049
1050
1051
1052 I_CURSOR := dbms_sql.open_cursor;
1053 dbms_sql.parse(I_CURSOR, ZONE_SQL, dbms_sql.v7);
1054 dbms_sql.define_column(I_CURSOR, 1, CURR_VAL.SV_SMS_ID);
1055 dbms_sql.define_column(I_CURSOR, 2, CURR_VAL.PORTING_ID, 80);
1056 dbms_sql.define_column(I_CURSOR, 3, CURR_VAL.SUBSCRIPTION_TN, 20);
1057 dbms_sql.define_column(I_CURSOR, 4, CURR_VAL.ROUTING_NUMBER, 20);
1058
1059 I_VOID := dbms_sql.execute(I_CURSOR);
1060 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1061 else
1062 I_ROWS_FETCHED := 0;
1063 end if;
1064 I_TOTAL_ROWS := I_ROWS_FETCHED;
1065
1066 if I_ROWS_FETCHED <> 0 then
1067
1068 XNP_WSGL.LayoutOpen(XNP_WSGL.LAYOUT_TABLE, P_BORDER=>TRUE);
1069
1070 XNP_WSGL.LayoutRowStart;
1071 for i in 1..RL_NUMBER_OF_COLUMNS loop
1072 XNP_WSGL.LayoutHeader(10, 'LEFT', 'Porting ID');
1073 XNP_WSGL.LayoutHeader(20, 'LEFT', 'Telephone');
1074 XNP_WSGL.LayoutHeader(20, 'LEFT', 'Routing Number');
1075 end loop;
1076 XNP_WSGL.LayoutRowEnd;
1077
1078 while I_ROWS_FETCHED <> 0 loop
1079
1080 if I_TOTAL_ROWS >= I_START then
1081 dbms_sql.column_value(I_CURSOR, 1, CURR_VAL.SV_SMS_ID);
1082 dbms_sql.column_value(I_CURSOR, 2, CURR_VAL.PORTING_ID);
1083 dbms_sql.column_value(I_CURSOR, 3, CURR_VAL.SUBSCRIPTION_TN);
1084 dbms_sql.column_value(I_CURSOR, 4, CURR_VAL.ROUTING_NUMBER);
1085 L_CHECKSUM := to_char(XNP_WSGL.Checksum(''||CURR_VAL.SV_SMS_ID));
1086
1087
1088 XNP_WSGL.LayoutRowStart('TOP');
1089 XNP_WSGL.LayoutData(htf.anchor2('xnp_sv_network$sms_sv.queryview?P_SV_SMS_ID='||CURR_VAL.SV_SMS_ID||'&Z_CHK='||L_CHECKSUM, CURR_VAL.PORTING_ID, ctarget=>L_VF_FRAME));
1090 XNP_WSGL.LayoutData(CURR_VAL.SUBSCRIPTION_TN);
1091 XNP_WSGL.LayoutData(CURR_VAL.ROUTING_NUMBER);
1092 XNP_WSGL.LayoutRowEnd;
1093
1094 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1095 if I_TOTAL_ROWS = I_START + RL_RECORD_SET_SIZE - 1 then
1096 if I_ROWS_FETCHED <> 0 then
1097 I_NEXT_BUT := TRUE;
1098 end if;
1099 exit;
1100 end if;
1101 else
1102 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1103 end if;
1104
1105 I_TOTAL_ROWS := I_TOTAL_ROWS + I_ROWS_FETCHED;
1106
1107 end loop;
1108
1109 XNP_WSGL.LayoutClose;
1110
1111 if I_START = I_TOTAL_ROWS then
1112 htp.p(XNP_WSGL.MsgGetText(109,XNP_WSGLM.DSP109_RECORD, to_char(I_TOTAL_ROWS))||I_OF_TOTAL_TEXT);
1113 else
1114 htp.p(XNP_WSGL.MsgGetText(110,XNP_WSGLM.DSP110_RECORDS_N_M,
1115 to_char(I_START), to_char(I_TOTAL_ROWS))||
1116 I_OF_TOTAL_TEXT);
1117 end if;
1118 htp.para;
1119 else
1120 htp.p(XNP_WSGL.MsgGetText(112,XNP_WSGLM.DSP112_NO_RECORDS));
1121 end if;
1122
1123 if nvl(Z_ACTION, 'X') <> 'DONTQUERY' then
1124 dbms_sql.close_cursor(I_CURSOR);
1125 end if;
1126
1127 htp.formOpen(curl => 'xnp_sv_network$sms_sv.querylist', cattributes => 'NAME="frmZero"');
1128 XNP_WSGL.HiddenField('P_PORTING_ID', P_PORTING_ID);
1129 XNP_WSGL.HiddenField('P_SUBSCRIPTION_TYPE', P_SUBSCRIPTION_TYPE);
1130 XNP_WSGL.HiddenField('P_SUBSCRIPTION_TN', P_SUBSCRIPTION_TN);
1131 XNP_WSGL.HiddenField('P_NRC_CODE', P_NRC_CODE);
1132 XNP_WSGL.HiddenField('P_REC_CODE', P_REC_CODE);
1133 XNP_WSGL.HiddenField('Z_START', to_char(I_START));
1134 htp.p ('<SCRIPT><!--');
1135 htp.p ('document.write (''<input type=hidden name="Z_ACTION">'')');
1136 htp.p ('//-->');
1137 htp.p ('</SCRIPT>');
1138
1139 XNP_WSGL.RecordListButton(I_PREV_BUT, 'Z_ACTION', htf.escape_sc(RL_FIRST_BUT_CAPTION), XNP_WSGL.MsgGetText(213,XNP_WSGLM.MSG213_AT_FIRST), FALSE,
1140 'onClick="this.form.Z_ACTION.value=\''' || RL_FIRST_BUT_ACTION || '\''"');
1141 XNP_WSGL.RecordListButton(I_PREV_BUT, 'Z_ACTION', htf.escape_sc(RL_PREV_BUT_CAPTION), XNP_WSGL.MsgGetText(213,XNP_WSGLM.MSG213_AT_FIRST), FALSE,
1142 'onClick="this.form.Z_ACTION.value=\''' || RL_PREV_BUT_ACTION || '\''"');
1143 XNP_WSGL.RecordListButton(I_NEXT_BUT,'Z_ACTION', htf.escape_sc(RL_NEXT_BUT_CAPTION), XNP_WSGL.MsgGetText(214,XNP_WSGLM.MSG214_AT_LAST), FALSE,
1144 'onClick="this.form.Z_ACTION.value=\''' || RL_NEXT_BUT_ACTION || '\''"');
1145 XNP_WSGL.RecordListButton(I_NEXT_BUT,'Z_ACTION', htf.escape_sc(RL_LAST_BUT_CAPTION), XNP_WSGL.MsgGetText(214,XNP_WSGLM.MSG214_AT_LAST), FALSE,
1146 'onClick="this.form.Z_ACTION.value=\''' || RL_LAST_BUT_ACTION || '\''"');
1147
1148 XNP_WSGL.RecordListButton(TRUE, 'Z_ACTION', htf.escape_sc(RL_REQUERY_BUT_CAPTION),p_dojs=>FALSE,
1149 buttonJS => 'onClick="this.form.Z_ACTION.value=\''' || RL_REQUERY_BUT_ACTION || '\''"');
1150 XNP_WSGL.HiddenField('Z_CHK',
1151 to_char(XNP_WSGL.Checksum('')));
1152 htp.formClose;
1153
1154 htp.formOpen(curl => 'xnp_sv_network$sms_sv.querylist', ctarget=>'_top', cattributes => 'NAME="frmOne"');
1155 htp.p ('<SCRIPT><!--');
1156 htp.p ('document.write (''<input type=hidden name="Z_ACTION">'')');
1157 htp.p ('//-->');
1158 htp.p ('</SCRIPT>');
1159 htp.p ('<SCRIPT><!--');
1160 htp.p ('document.write (''' || htf.formSubmit('', htf.escape_sc(RL_QUERY_BUT_CAPTION), 'onClick="this.form.Z_ACTION.value=\''' || RL_QUERY_BUT_ACTION || '\''"') || ''')');
1161 htp.p ('//-->');
1162 htp.p ('</SCRIPT>');
1163
1164 if XNP_WSGL.IsSupported ('NOSCRIPT')
1165 then
1166
1167 htp.p ('<NOSCRIPT>');
1168 htp.formSubmit('Z_ACTION', htf.escape_sc(RL_QUERY_BUT_CAPTION));
1169 htp.p ('</NOSCRIPT>');
1170
1171 end if;
1172 XNP_WSGL.HiddenField('Z_CHK',
1173 to_char(XNP_WSGL.Checksum('')));
1174 htp.formClose;
1175
1176 XNP_WSGL.ClosePageBody;
1177
1178 exception
1179 when others then
1180 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
1181 RL_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.QueryList');
1182 XNP_WSGL.ClosePageBody;
1183 end;
1184
1185 --------------------------------------------------------------------------------
1186 -- Name: xnp_sv_network$sms_sv.QueryFirst
1187 --
1188 -- Description: Finds the first row which matches the given search criteria
1189 -- (if any), and calls QueryView for that row
1190 --
1191 -- Parameters:
1192 --
1193 --------------------------------------------------------------------------------
1194 procedure QueryFirst(
1195 P_PORTING_ID in varchar2,
1196 P_SUBSCRIPTION_TYPE in varchar2,
1197 P_SUBSCRIPTION_TN in varchar2,
1198 P_NRC_CODE in varchar2,
1199 P_REC_CODE in varchar2,
1200 Z_ACTION in varchar2,
1201 Z_DIRECT_CALL in boolean,
1202 Z_CHK in varchar2) is
1203
1204 I_CURSOR integer;
1205 I_VOID integer;
1206 I_ROWS_FETCHED integer := 0;
1207
1208 begin
1209
1210 XNP_WSGL.RegisterURL('xnp_sv_network$sms_sv.queryfirst');
1211 XNP_WSGL.AddURLParam('P_PORTING_ID', P_PORTING_ID);
1212 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TYPE', P_SUBSCRIPTION_TYPE);
1213 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TN', P_SUBSCRIPTION_TN);
1214 XNP_WSGL.AddURLParam('P_NRC_CODE', P_NRC_CODE);
1215 XNP_WSGL.AddURLParam('P_REC_CODE', P_REC_CODE);
1216 XNP_WSGL.AddURLParam('Z_ACTION', Z_ACTION);
1217 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
1218 if not Z_DIRECT_CALL then
1219 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
1220 return;
1221 end if;
1222 end if;
1223
1224 if Z_ACTION = 'BLANK' then
1225 XNP_WSGL.EmptyPage(VF_BODY_ATTRIBUTES);
1226 return;
1227 end if;
1228
1229
1230 if not BuildSQL(
1231 P_PORTING_ID,
1232 P_SUBSCRIPTION_TYPE,
1233 P_SUBSCRIPTION_TN,
1234 P_NRC_CODE,
1235 P_REC_CODE) then
1236 return;
1237 end if;
1238
1239 if not PreQuery(
1240 P_PORTING_ID,
1241 P_SUBSCRIPTION_TYPE,
1242 P_SUBSCRIPTION_TN,
1243 P_NRC_CODE,
1244 P_REC_CODE) then
1245 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
1246 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions', VF_BODY_ATTRIBUTES);
1247 return;
1248 end if;
1249
1250 I_CURSOR := dbms_sql.open_cursor;
1251 dbms_sql.parse(I_CURSOR, ZONE_SQL, dbms_sql.v7);
1252 dbms_sql.define_column(I_CURSOR, 1, CURR_VAL.SV_SMS_ID);
1253 dbms_sql.define_column(I_CURSOR, 2, CURR_VAL.PORTING_ID, 80);
1254 dbms_sql.define_column(I_CURSOR, 3, CURR_VAL.SUBSCRIPTION_TN, 20);
1255 dbms_sql.define_column(I_CURSOR, 4, CURR_VAL.ROUTING_NUMBER, 20);
1256
1257 I_VOID := dbms_sql.execute(I_CURSOR);
1258
1259 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1260
1261 if I_ROWS_FETCHED = 0 then
1262 XNP_WSGL.EmptyPage(VF_BODY_ATTRIBUTES);
1263 else
1264 dbms_sql.column_value(I_CURSOR, 1, CURR_VAL.SV_SMS_ID);
1265 dbms_sql.column_value(I_CURSOR, 2, CURR_VAL.PORTING_ID);
1266 dbms_sql.column_value(I_CURSOR, 3, CURR_VAL.SUBSCRIPTION_TN);
1267 dbms_sql.column_value(I_CURSOR, 4, CURR_VAL.ROUTING_NUMBER);
1268 xnp_sv_network$sms_sv.QueryView(Z_DIRECT_CALL=>TRUE);
1269 end if;
1270
1271 dbms_sql.close_cursor(I_CURSOR);
1272
1273 exception
1274 when others then
1275 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
1276 VF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.QueryFirst');
1277 XNP_WSGL.ClosePageBody;
1278 end;
1279
1280 --------------------------------------------------------------------------------
1281 -- Name: xnp_sv_network$sms_sv.PreQuery
1282 --
1283 -- Description: Provides place holder for code to be run prior to a query
1284 -- for the 'SMS_SV' module component (Porting Network Subscriptions).
1285 --
1286 -- Parameters: None
1287 --
1288 -- Returns: True If success
1289 -- False Otherwise
1290 --
1291 --------------------------------------------------------------------------------
1292 function PreQuery(
1293 P_PORTING_ID in varchar2,
1294 P_SUBSCRIPTION_TYPE in varchar2,
1295 P_SUBSCRIPTION_TN in varchar2,
1296 P_NRC_CODE in varchar2,
1297 P_REC_CODE in varchar2) return boolean is
1298 L_RET_VAL boolean := TRUE;
1299 begin
1300 return L_RET_VAL;
1301 exception
1302 when others then
1303 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
1304 DEF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.PreQuery');
1305 return FALSE;
1306 end;
1307
1308 --------------------------------------------------------------------------------
1309 -- Name: xnp_sv_network$sms_sv.PostQuery
1310 --
1311 -- Description: Provides place holder for code to be run after a query
1312 -- for the 'SMS_SV' module component (Porting Network Subscriptions).
1313 --
1314 -- Parameters: Z_POST_DML Flag indicating if Query after insert or update
1315 --
1316 -- Returns: True If success
1317 -- False Otherwise
1318 --
1319 --------------------------------------------------------------------------------
1320 function PostQuery(Z_POST_DML in boolean) return boolean is
1321 L_RET_VAL boolean := TRUE;
1322 begin
1323 return L_RET_VAL;
1324 exception
1325 when others then
1326 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
1327 DEF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.PostQuery');
1328 return FALSE;
1329 end;
1330
1331 --------------------------------------------------------------------------------
1332 -- Name: xnp_sv_network$sms_sv.CreateQueryJavaScript
1333 --
1334 -- Description:
1335 --
1336 -- Parameters:
1337 --
1338 --------------------------------------------------------------------------------
1339 procedure CreateQueryJavaScript is
1340 begin
1341 htp.p(XNP_WSGJSL.OpenScript);
1342 htp.p('var FormType = "Query";');
1343 htp.p(XNP_WSGJSL.CloseScript);
1344 exception
1345 when others then
1346 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
1347 QF_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.CreateQueryJavaScript');
1348 end;
1349
1350 --------------------------------------------------------------------------------
1351 -- Name: xnp_sv_network$sms_sv.CreateListJavaScript
1352 --
1353 -- Description:
1354 --
1355 -- Parameters:
1356 --
1357 --------------------------------------------------------------------------------
1358 procedure CreateListJavaScript is
1359 begin
1360 htp.p(XNP_WSGJSL.OpenScript);
1361 htp.p('var FormType = "List";');
1362 htp.p(XNP_WSGJSL.CloseScript);
1363 exception
1364 when others then
1365 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Network Subscriptions'||' : '||'Porting Network Subscriptions',
1366 RL_BODY_ATTRIBUTES, 'xnp_sv_network$sms_sv.CreateListJavaScript');
1367 end;
1368 end;