[Home] [Help]
PACKAGE BODY: APPS.XNP_SV_ORDERS$SOA_SV
Source
1 package body xnp_sv_orders$soa_sv as
2 /* $Header: XNPSVO2B.pls 120.0 2005/05/30 11:52:54 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_STATUS_DISPLAY_NAME in varchar2 default null,
11 P_CUSTOMER_ID in varchar2 default null,
12 P_CUSTOMER_NAME in varchar2 default null,
13 P_REC_CODE in varchar2 default null,
14 P_DON_CODE in varchar2 default null,
15 P_NRC_CODE in varchar2 default null) return boolean;
16 function PreQuery(
17 P_PORTING_ID in varchar2 default null,
18 P_SUBSCRIPTION_TYPE in varchar2 default null,
19 P_SUBSCRIPTION_TN in varchar2 default null,
20 P_STATUS_DISPLAY_NAME in varchar2 default null,
21 P_CUSTOMER_ID in varchar2 default null,
22 P_CUSTOMER_NAME in varchar2 default null,
23 P_REC_CODE in varchar2 default null,
24 P_DON_CODE in varchar2 default null,
25 P_NRC_CODE in varchar2 default null) return boolean;
26 function PostQuery(Z_POST_DML in boolean) return boolean;
27 procedure CreateQueryJavaScript;
28 procedure CreateListJavaScript;
29 procedure InitialiseDomain(P_ALIAS in varchar2);
30
31 QF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
32 QF_QUERY_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_FIND_BUTTON'));
33 QF_CLEAR_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_CLEAR_BUTTON'));
34 QF_QUERY_BUT_ACTION constant varchar2(10) := 'QUERY';
35 QF_CLEAR_BUT_ACTION constant varchar2(10) := 'CLEAR';
36 QF_NUMBER_OF_COLUMNS constant number(4) := 2;
37 VF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
38 VF_UPDATE_BUT_CAPTION constant varchar2(100) := XNP_WSGL.MsgGetText(6,XNP_WSGLM.CAP006_VF_UPDATE);
39 VF_CLEAR_BUT_CAPTION constant varchar2(100) := XNP_WSGL.MsgGetText(8,XNP_WSGLM.CAP008_VF_REVERT);
40 VF_DELETE_BUT_CAPTION constant varchar2(100) := XNP_WSGL.MsgGetText(7,XNP_WSGLM.CAP007_VF_DELETE);
41 VF_UPDATE_BUT_ACTION constant varchar2(10) := 'UPDATE';
42 VF_CLEAR_BUT_ACTION constant varchar2(10) := 'CLEAR';
43 VF_DELETE_BUT_ACTION constant varchar2(10) := 'DELETE';
44 VF_VERIFIED_DELETE constant varchar2(100) := 'VerifiedDelete';
45 VF_NUMBER_OF_COLUMNS constant number(4) := 2;
46 RL_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
47 RL_NEXT_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_NEXT_BUTTON'));
48 RL_PREV_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_PREVIOUS_BUTTON'));
49 RL_FIRST_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_FIRST_BUTTON'));
50 RL_LAST_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_LAST_BUTTON'));
51 RL_COUNT_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_COUNT_BUTTON'));
52 RL_REQUERY_BUT_CAPTION constant varchar2(100) := 'Refresh';
53 RL_QUERY_BUT_CAPTION constant varchar2(100) := 'New Search';
54 RL_QUERY_BUT_ACTION constant varchar2(10) := 'QUERY';
55 RL_NEXT_BUT_ACTION constant varchar2(10) := 'NEXT';
56 RL_PREV_BUT_ACTION constant varchar2(10) := 'PREV';
57 RL_FIRST_BUT_ACTION constant varchar2(10) := 'FIRST';
58 RL_LAST_BUT_ACTION constant varchar2(10) := 'LAST';
59 RL_COUNT_BUT_ACTION constant varchar2(10) := 'COUNT';
60 RL_REQUERY_BUT_ACTION constant varchar2(10) := 'REQUERY';
61 RL_RECORD_SET_SIZE constant number(4) := 10;
62 RL_TOTAL_COUNT_REQD constant boolean := TRUE;
63 RL_NUMBER_OF_COLUMNS constant number(4) := 1;
64 LOV_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
65 LOV_FIND_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_FIND_BUTTON'));
66 LOV_CLOSE_BUT_CAPTION constant varchar2(100) := htf.escape_sc(fnd_message.get_string('XNP','WEB_CLOSE_BUTTON'));
67 LOV_FIND_BUT_ACTION constant varchar2(10) := 'FIND';
68 LOV_CLOSE_BUT_ACTION constant varchar2(10) := 'CLOSE';
69 LOV_BUTTON_TEXT constant varchar2(100) := htf.italic(fnd_message.get_string('XNP','LIST_OF_VALUES'));
70 LOV_FRAME constant varchar2(20) := null;
71 TF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
72 DEF_BODY_ATTRIBUTES constant varchar2(500) := 'BGCOLOR="CCCCCC"';
73
74 CURR_VAL XNP_SV_SOA_VL%ROWTYPE;
75
76 TYPE FORM_REC IS RECORD
77 (PORTING_ID VARCHAR2(80)
78 ,SUBSCRIPTION_TYPE VARCHAR2(30)
79 ,SUBSCRIPTION_TN VARCHAR2(20)
80 ,PRE_SPLIT_SUBSCRIPTION_TN VARCHAR2(20)
81 ,ROUTING_NUMBER VARCHAR2(20)
82 ,STATUS_PHASE VARCHAR2(20)
83 ,STATUS_DISPLAY_NAME VARCHAR2(40)
84 ,DSP_STATUS VARCHAR2(2000)
85 ,STATUS_CHANGE_DATE VARCHAR2(12)
86 ,STATUS_CHANGE_CAUSE_CODE VARCHAR2(20)
87 ,DSP_CUSTOMER VARCHAR2(2000)
88 ,DSP_CONTACT VARCHAR2(2000)
89 ,CUSTOMER_ID VARCHAR2(80)
90 ,CUSTOMER_NAME VARCHAR2(80)
91 ,CONTACT_NAME VARCHAR2(80)
92 ,PHONE VARCHAR2(20)
93 ,EMAIL VARCHAR2(80)
94 ,ACTIVATION_DUE_DATE VARCHAR2(12)
95 ,ORDER_PRIORITY VARCHAR2(10)
96 ,PREORDER_AUTHORIZATION_CODE VARCHAR2(20)
97 ,DSP_REC VARCHAR2(2000)
98 ,REC_CODE VARCHAR2(20)
99 ,REC_NAME VARCHAR2(80)
100 ,REC_EMAIL VARCHAR2(80)
101 ,DSP_DON VARCHAR2(2000)
102 ,DON_CODE VARCHAR2(20)
103 ,DON_NAME VARCHAR2(80)
104 ,DON_EMAIL VARCHAR2(80)
105 ,DSP_NRC VARCHAR2(2000)
106 ,NRC_CODE VARCHAR2(20)
107 ,NRC_NAME VARCHAR2(80)
108 ,NRC_EMAIL VARCHAR2(80)
109 ,NEW_SP_AUTHORIZATION_FLAG VARCHAR2(5)
110 ,NEW_SP_DUE_DATE VARCHAR2(12)
111 ,OLD_SP_AUTHORIZATION_FLAG VARCHAR2(5)
112 ,OLD_SP_DUE_DATE VARCHAR2(12)
113 ,OLD_SP_CUTOFF_DUE_DATE VARCHAR2(12)
114 ,INVOICE_DUE_DATE VARCHAR2(12)
115 ,RETAIN_DIR_INFO_FLAG VARCHAR2(5)
116 ,LOCKED_FLAG VARCHAR2(5)
117 ,CONCURRENCE_FLAG VARCHAR2(1)
118 ,PTO_FLAG VARCHAR2(5)
119 ,DISCONNECT_DUE_DATE VARCHAR2(12)
120 ,EFFECTIVE_RELEASE_DUE_DATE VARCHAR2(12)
121 ,RETAIN_TN_FLAG VARCHAR2(5)
122 ,BLOCKED_FLAG VARCHAR2(5)
123 ,NUMBER_RETURNED_DUE_DATE VARCHAR2(12)
124 ,DSP_CNAM VARCHAR2(2000)
125 ,DSP_CLASS VARCHAR2(2000)
126 ,DSP_ISVM VARCHAR2(2000)
127 ,DSP_LIDB VARCHAR2(2000)
128 ,DSP_WSMSC VARCHAR2(2000)
129 ,DSP_RN VARCHAR2(2000)
130 ,CLASS_ADDRESS VARCHAR2(80)
131 ,CLASS_SUBSYSTEM VARCHAR2(80)
132 ,CNAM_ADDRESS VARCHAR2(80)
133 ,CNAM_SUBSYSTEM VARCHAR2(80)
134 ,ISVM_ADDRESS VARCHAR2(80)
135 ,ISVM_SUBSYSTEM VARCHAR2(80)
136 ,LIDB_ADDRESS VARCHAR2(80)
137 ,LIDB_SUBSYSTEM VARCHAR2(80)
138 ,WSMSC_ADDRESS VARCHAR2(80)
139 ,WSMSC_SUBSYSTEM VARCHAR2(80)
140 ,RN_ADDRESS VARCHAR2(80)
141 ,RN_SUBSYSTEM VARCHAR2(80)
142 ,SV_SOA_ID VARCHAR2(8)
143 );
144 FORM_VAL FORM_REC;
145
146 TYPE NBT_REC IS RECORD
147 (DSP_STATUS VARCHAR2(2000)
148 ,DSP_CUSTOMER VARCHAR2(2000)
149 ,DSP_CONTACT VARCHAR2(2000)
150 ,DSP_REC VARCHAR2(2000)
151 ,DSP_DON VARCHAR2(2000)
152 ,DSP_NRC VARCHAR2(2000)
153 ,DSP_CNAM VARCHAR2(2000)
154 ,DSP_CLASS VARCHAR2(2000)
155 ,DSP_ISVM VARCHAR2(2000)
156 ,DSP_LIDB VARCHAR2(2000)
157 ,DSP_WSMSC VARCHAR2(2000)
158 ,DSP_RN VARCHAR2(2000)
159 );
160 NBT_VAL NBT_REC;
161
162 ZONE_SQL VARCHAR2(7600) := null;
163
164 D_SUBSCRIPTION_TYPE XNP_WSGL.typDVRecord;
165 D_STATUS_PHASE XNP_WSGL.typDVRecord;
166 D_ORDER_PRIORITY XNP_WSGL.typDVRecord;
167 D_NEW_SP_AUTHORIZATION_FLAG XNP_WSGL.typDVRecord;
168 D_OLD_SP_AUTHORIZATION_FLAG XNP_WSGL.typDVRecord;
169 D_RETAIN_DIR_INFO_FLAG XNP_WSGL.typDVRecord;
170 D_LOCKED_FLAG XNP_WSGL.typDVRecord;
171 D_CONCURRENCE_FLAG XNP_WSGL.typDVRecord;
172 D_PTO_FLAG XNP_WSGL.typDVRecord;
173 D_RETAIN_TN_FLAG XNP_WSGL.typDVRecord;
174 D_BLOCKED_FLAG XNP_WSGL.typDVRecord;
175 --------------------------------------------------------------------------------
176 -- Name: xnp_sv_orders$soa_sv.InitialiseDomain
177 --
178 -- Description: Initialises the Domain Record for the given Column Usage
179 --
180 -- Parameters: P_ALIAS The alias of the column usage
181 --
182 --------------------------------------------------------------------------------
183 procedure InitialiseDomain(P_ALIAS in varchar2) is
184 begin
185
186 if P_ALIAS = 'SUBSCRIPTION_TYPE' and not D_SUBSCRIPTION_TYPE.Initialised then
187 D_SUBSCRIPTION_TYPE.ColAlias := 'SUBSCRIPTION_TYPE';
188 D_SUBSCRIPTION_TYPE.ControlType := XNP_WSGL.DV_TEXT;
189 D_SUBSCRIPTION_TYPE.DispWidth := 30;
190 D_SUBSCRIPTION_TYPE.DispHeight := 1;
191 D_SUBSCRIPTION_TYPE.MaxWidth := 30;
192 D_SUBSCRIPTION_TYPE.UseMeanings := True;
193 D_SUBSCRIPTION_TYPE.ColOptional := False;
194 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_SUBSCRIPTION_TYPE', D_SUBSCRIPTION_TYPE);
195 D_SUBSCRIPTION_TYPE.Initialised := True;
196 end if;
197
198 if P_ALIAS = 'STATUS_PHASE' and not D_STATUS_PHASE.Initialised then
199 D_STATUS_PHASE.ColAlias := 'STATUS_PHASE';
200 D_STATUS_PHASE.ControlType := XNP_WSGL.DV_TEXT;
201 D_STATUS_PHASE.DispWidth := 10;
202 D_STATUS_PHASE.DispHeight := 1;
203 D_STATUS_PHASE.MaxWidth := 20;
204 D_STATUS_PHASE.UseMeanings := False;
205 D_STATUS_PHASE.ColOptional := False;
206 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_PHASE_INDICATOR', D_STATUS_PHASE);
207 D_STATUS_PHASE.Initialised := True;
208 end if;
209
210 if P_ALIAS = 'ORDER_PRIORITY' and not D_ORDER_PRIORITY.Initialised then
211 D_ORDER_PRIORITY.ColAlias := 'ORDER_PRIORITY';
212 D_ORDER_PRIORITY.ControlType := XNP_WSGL.DV_TEXT;
213 D_ORDER_PRIORITY.DispWidth := 10;
214 D_ORDER_PRIORITY.DispHeight := 1;
215 D_ORDER_PRIORITY.MaxWidth := 10;
216 D_ORDER_PRIORITY.UseMeanings := True;
217 D_ORDER_PRIORITY.ColOptional := True;
218 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_ORDER_PRIORITY', D_ORDER_PRIORITY);
219 D_ORDER_PRIORITY.Initialised := True;
220 end if;
221
222 if P_ALIAS = 'NEW_SP_AUTHORIZATION_FLAG' and not D_NEW_SP_AUTHORIZATION_FLAG.Initialised then
223 D_NEW_SP_AUTHORIZATION_FLAG.ColAlias := 'NEW_SP_AUTHORIZATION_FLAG';
224 D_NEW_SP_AUTHORIZATION_FLAG.ControlType := XNP_WSGL.DV_TEXT;
225 D_NEW_SP_AUTHORIZATION_FLAG.DispWidth := 5;
226 D_NEW_SP_AUTHORIZATION_FLAG.DispHeight := 1;
227 D_NEW_SP_AUTHORIZATION_FLAG.MaxWidth := 5;
228 D_NEW_SP_AUTHORIZATION_FLAG.UseMeanings := True;
229 D_NEW_SP_AUTHORIZATION_FLAG.ColOptional := False;
230 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_YES', D_NEW_SP_AUTHORIZATION_FLAG);
231 D_NEW_SP_AUTHORIZATION_FLAG.Initialised := True;
232 end if;
233
234 if P_ALIAS = 'OLD_SP_AUTHORIZATION_FLAG' and not D_OLD_SP_AUTHORIZATION_FLAG.Initialised then
235 D_OLD_SP_AUTHORIZATION_FLAG.ColAlias := 'OLD_SP_AUTHORIZATION_FLAG';
236 D_OLD_SP_AUTHORIZATION_FLAG.ControlType := XNP_WSGL.DV_TEXT;
237 D_OLD_SP_AUTHORIZATION_FLAG.DispWidth := 5;
238 D_OLD_SP_AUTHORIZATION_FLAG.DispHeight := 1;
239 D_OLD_SP_AUTHORIZATION_FLAG.MaxWidth := 5;
240 D_OLD_SP_AUTHORIZATION_FLAG.UseMeanings := True;
241 D_OLD_SP_AUTHORIZATION_FLAG.ColOptional := False;
242 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_YES', D_OLD_SP_AUTHORIZATION_FLAG);
243 D_OLD_SP_AUTHORIZATION_FLAG.Initialised := True;
244 end if;
245
246 if P_ALIAS = 'RETAIN_DIR_INFO_FLAG' and not D_RETAIN_DIR_INFO_FLAG.Initialised then
247 D_RETAIN_DIR_INFO_FLAG.ColAlias := 'RETAIN_DIR_INFO_FLAG';
248 D_RETAIN_DIR_INFO_FLAG.ControlType := XNP_WSGL.DV_TEXT;
249 D_RETAIN_DIR_INFO_FLAG.DispWidth := 5;
250 D_RETAIN_DIR_INFO_FLAG.DispHeight := 1;
251 D_RETAIN_DIR_INFO_FLAG.MaxWidth := 5;
252 D_RETAIN_DIR_INFO_FLAG.UseMeanings := True;
253 D_RETAIN_DIR_INFO_FLAG.ColOptional := True;
254 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_YES', D_RETAIN_DIR_INFO_FLAG);
255 D_RETAIN_DIR_INFO_FLAG.Initialised := True;
256 end if;
257
258 if P_ALIAS = 'LOCKED_FLAG' and not D_LOCKED_FLAG.Initialised then
259 D_LOCKED_FLAG.ColAlias := 'LOCKED_FLAG';
260 D_LOCKED_FLAG.ControlType := XNP_WSGL.DV_TEXT;
261 D_LOCKED_FLAG.DispWidth := 5;
262 D_LOCKED_FLAG.DispHeight := 1;
263 D_LOCKED_FLAG.MaxWidth := 5;
264 D_LOCKED_FLAG.UseMeanings := True;
265 D_LOCKED_FLAG.ColOptional := True;
266 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_YES', D_LOCKED_FLAG);
267 D_LOCKED_FLAG.Initialised := True;
268 end if;
269
270 if P_ALIAS = 'CONCURRENCE_FLAG' and not D_CONCURRENCE_FLAG.Initialised then
271 D_CONCURRENCE_FLAG.ColAlias := 'CONCURRENCE_FLAG';
272 D_CONCURRENCE_FLAG.ControlType := XNP_WSGL.DV_TEXT;
273 D_CONCURRENCE_FLAG.DispWidth := 1;
274 D_CONCURRENCE_FLAG.DispHeight := 1;
275 D_CONCURRENCE_FLAG.MaxWidth := 1;
276 D_CONCURRENCE_FLAG.UseMeanings := False;
277 D_CONCURRENCE_FLAG.ColOptional := True;
278 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_YES', D_CONCURRENCE_FLAG);
279 D_CONCURRENCE_FLAG.Initialised := True;
280 end if;
281
282 if P_ALIAS = 'PTO_FLAG' and not D_PTO_FLAG.Initialised then
283 D_PTO_FLAG.ColAlias := 'PTO_FLAG';
284 D_PTO_FLAG.ControlType := XNP_WSGL.DV_TEXT;
285 D_PTO_FLAG.DispWidth := 5;
286 D_PTO_FLAG.DispHeight := 1;
287 D_PTO_FLAG.MaxWidth := 5;
288 D_PTO_FLAG.UseMeanings := True;
289 D_PTO_FLAG.ColOptional := False;
290 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_NO', D_PTO_FLAG);
291 D_PTO_FLAG.Initialised := True;
292 end if;
293
294 if P_ALIAS = 'RETAIN_TN_FLAG' and not D_RETAIN_TN_FLAG.Initialised then
295 D_RETAIN_TN_FLAG.ColAlias := 'RETAIN_TN_FLAG';
296 D_RETAIN_TN_FLAG.ControlType := XNP_WSGL.DV_TEXT;
297 D_RETAIN_TN_FLAG.DispWidth := 5;
298 D_RETAIN_TN_FLAG.DispHeight := 1;
299 D_RETAIN_TN_FLAG.MaxWidth := 5;
300 D_RETAIN_TN_FLAG.UseMeanings := True;
301 D_RETAIN_TN_FLAG.ColOptional := True;
302 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_YES', D_RETAIN_TN_FLAG);
303 D_RETAIN_TN_FLAG.Initialised := True;
304 end if;
305
306 if P_ALIAS = 'BLOCKED_FLAG' and not D_BLOCKED_FLAG.Initialised then
307 D_BLOCKED_FLAG.ColAlias := 'BLOCKED_FLAG';
308 D_BLOCKED_FLAG.ControlType := XNP_WSGL.DV_TEXT;
309 D_BLOCKED_FLAG.DispWidth := 5;
310 D_BLOCKED_FLAG.DispHeight := 1;
311 D_BLOCKED_FLAG.MaxWidth := 5;
312 D_BLOCKED_FLAG.UseMeanings := True;
313 D_BLOCKED_FLAG.ColOptional := True;
314 XNP_WSGL.LoadDomainValues('CG_REF_CODES', 'XNP_YES_NO_FLAG_DEFAULT_YES', D_BLOCKED_FLAG);
315 D_BLOCKED_FLAG.Initialised := True;
316 end if;
317
318 exception
319 when others then
320 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
321 DEF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.InitialseDomain');
322 end;
323
324 --------------------------------------------------------------------------------
325 -- Name: xnp_sv_orders$soa_sv.Startup
326 --
327 -- Description: Entry point for the 'SOA_SV' module
328 -- component (Porting Ordering Subscriptions).
329 --
330 -- Parameters:
331 --
332 --------------------------------------------------------------------------------
333 procedure Startup(
334 Z_DIRECT_CALL in boolean,
335 Z_CHK in varchar2) is
336 begin
337
338 XNP_WSGL.RegisterURL('xnp_sv_orders$soa_sv.startup');
339 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
340
341 if not Z_DIRECT_CALL then
342 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
343 return;
344 end if;
345 end if;
346
347 XNP_WSGL.StoreURLLink(1, 'Porting Ordering Subscriptions');
348
349
350 FormQuery(
351 Z_DIRECT_CALL=>TRUE);
352
353 exception
354 when others then
355 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
356 DEF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.Startup');
357 end;
358
359 --------------------------------------------------------------------------------
360 -- Name: xnp_sv_orders$soa_sv.ActionQuery
361 --
362 -- Description: Called when a Query form is subitted to action the query request.
363 --
364 -- Parameters:
365 --
366 --------------------------------------------------------------------------------
367 procedure ActionQuery(
368 P_PORTING_ID in varchar2,
369 P_SUBSCRIPTION_TYPE in varchar2,
370 P_SUBSCRIPTION_TN in varchar2,
371 P_STATUS_DISPLAY_NAME in varchar2,
372 P_CUSTOMER_ID in varchar2,
373 P_CUSTOMER_NAME in varchar2,
374 P_REC_CODE in varchar2,
375 P_DON_CODE in varchar2,
376 P_NRC_CODE in varchar2,
377 Z_DIRECT_CALL in boolean default false,
378 Z_ACTION in varchar2,
379 Z_CHK in varchar2) is
380
381 I_PARAM_LIST varchar2(2000) := '?';
382 L_QRY_FIRST_ACTION varchar2(12) := 'INSERTIFNONE';
383 L_QRY_LIST_ACTION varchar2(10) := null;
384 L_CHK varchar2(10) := Z_CHK;
385 L_BUTCHK varchar2(100):= null;
386 begin
387
388 if Z_DIRECT_CALL then
389 L_CHK := to_char(XNP_WSGL.Checksum(''));
390 else
391 if not XNP_WSGL.ValidateChecksum('', L_CHK) then
392 return;
393 end if;
394 end if;
395
396 if P_PORTING_ID is not null then
397 I_PARAM_LIST := I_PARAM_LIST || 'P_PORTING_ID=' || XNP_WSGL.EscapeURLParam(P_PORTING_ID) || '&';
398 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
399 end if;
400 if P_SUBSCRIPTION_TYPE is not null then
401 I_PARAM_LIST := I_PARAM_LIST || 'P_SUBSCRIPTION_TYPE=' || XNP_WSGL.EscapeURLParam(P_SUBSCRIPTION_TYPE) || '&';
402 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
403 end if;
404 if P_SUBSCRIPTION_TN is not null then
405 I_PARAM_LIST := I_PARAM_LIST || 'P_SUBSCRIPTION_TN=' || XNP_WSGL.EscapeURLParam(P_SUBSCRIPTION_TN) || '&';
406 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
407 end if;
408 if P_STATUS_DISPLAY_NAME is not null then
409 I_PARAM_LIST := I_PARAM_LIST || 'P_STATUS_DISPLAY_NAME=' || XNP_WSGL.EscapeURLParam(P_STATUS_DISPLAY_NAME) || '&';
410 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
411 end if;
412 if P_CUSTOMER_ID is not null then
413 I_PARAM_LIST := I_PARAM_LIST || 'P_CUSTOMER_ID=' || XNP_WSGL.EscapeURLParam(P_CUSTOMER_ID) || '&';
414 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
415 end if;
416 if P_CUSTOMER_NAME is not null then
417 I_PARAM_LIST := I_PARAM_LIST || 'P_CUSTOMER_NAME=' || XNP_WSGL.EscapeURLParam(P_CUSTOMER_NAME) || '&';
418 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
419 end if;
420 if P_REC_CODE is not null then
421 I_PARAM_LIST := I_PARAM_LIST || 'P_REC_CODE=' || XNP_WSGL.EscapeURLParam(P_REC_CODE) || '&';
422 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
423 end if;
424 if P_DON_CODE is not null then
425 I_PARAM_LIST := I_PARAM_LIST || 'P_DON_CODE=' || XNP_WSGL.EscapeURLParam(P_DON_CODE) || '&';
426 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
427 end if;
428 if P_NRC_CODE is not null then
429 I_PARAM_LIST := I_PARAM_LIST || 'P_NRC_CODE=' || XNP_WSGL.EscapeURLParam(P_NRC_CODE) || '&';
430 L_QRY_FIRST_ACTION := 'BLANKIFNONE';
431 end if;
432 I_PARAM_LIST := I_PARAM_LIST || 'Z_CHK=' || XNP_WSGL.EscapeURLParam(L_CHK) || '&';
433 I_PARAM_LIST := I_PARAM_LIST||'Z_ACTION=';
434
435 htp.p('<HTML>
436 <TITLE>Monitor Ordering Subscriptions : Porting Ordering Subscriptions</TITLE>
437 <FRAMESET ROWS="100,*,50">
438 ');
439 htp.noframesOpen;
440 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_INFORMATION, XNP_WSGL.MsgGetText(229,XNP_WSGLM.MSG229_NO_FRAME_SUPPORT),
441 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', DEF_BODY_ATTRIBUTES);
442 htp.noframesClose;
443 htp.p('<FRAME NAME="fraTOP" SRC="xnp_sv_orders$soa_sv.textframe?Z_HEADER=Y&Z_TEXT=Y">
444 <FRAMESET COLS="30%,70%">
445 <FRAME NAME="fraRL" SRC="xnp_sv_orders$soa_sv.querylist'||I_PARAM_LIST||L_QRY_LIST_ACTION||'">
446 <FRAME NAME="fraVF" SRC="xnp_sv_orders$soa_sv.queryfirst'||I_PARAM_LIST||L_QRY_FIRST_ACTION||'">
447 </FRAMESET>
448 <FRAME NAME="fraBOTTOM" SRC="xnp_sv_orders$soa_sv.textframe?Z_FOOTER=Y">
449 </FRAMESET>
450 </HTML>
451 ');
452 --if on the query form and insert is allowed
453
454 exception
455 when others then
456 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
457 DEF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.ActionQuery');
458 end;
459
460 --------------------------------------------------------------------------------
461 -- Name: xnp_sv_orders$soa_sv.TextFrame
462 --
463 -- Description:
464 --
465 -- Parameters:
466 --
467 --------------------------------------------------------------------------------
468 procedure TextFrame(Z_HEADER in varchar2,
469 Z_FIRST in varchar2,
470 Z_TEXT in varchar2,
471 Z_FOOTER in varchar2) is
472 L_TEXT_TYPES number := 0;
473 L_ONLY_THING_IN_FRAME boolean := FALSE;
474 begin
475
476 if Z_HEADER is not null then
477 L_TEXT_TYPES := L_TEXT_TYPES + 1;
478 end if;
479 if Z_FIRST is not null then
480 L_TEXT_TYPES := L_TEXT_TYPES + 1;
481 end if;
482 if Z_TEXT is not null then
483 L_TEXT_TYPES := L_TEXT_TYPES + 1;
484 end if;
485 if Z_FOOTER is not null then
486 L_TEXT_TYPES := L_TEXT_TYPES + 1;
487 end if;
488
489 L_ONLY_THING_IN_FRAME := (L_TEXT_TYPES = 1);
490
491 XNP_WSGL.OpenPageHead('Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
492 L_ONLY_THING_IN_FRAME);
493 xnp_sv_orders$.TemplateHeader(TRUE,0);
494 XNP_WSGL.ClosePageHead;
495
496 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>TF_BODY_ATTRIBUTES);
497
498 if Z_HEADER is not null then
499 htp.p(htf.bold(fnd_message.get_string('XNP','WEB_TITLE')));
500 end if;
501
502 if Z_FIRST is not null then
503 xnp_sv_orders$.FirstPage(TRUE);
504 end if;
505
506 if Z_TEXT is not null then
507 htp.p(htf.header(1,fnd_message.get_string('XNP','WEB_XNPSVORD_DETAILS_TITLE')));
508 end if;
509
510 if Z_FOOTER is not null then
511 htp.p(htf.img('/OA_MEDIA/FNDLOGOS.gif'));
512 end if;
513
514 XNP_WSGL.ClosePageBody;
515
516 exception
517 when others then
518 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
519 TF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.TextFrame');
520 XNP_WSGL.ClosePageBody;
521 end;
522 --------------------------------------------------------------------------------
523 -- Name: xnp_sv_orders$soa_sv.QueryHits
524 --
525 -- Description: Returns the number or rows which matches the given search
526 -- criteria (if any).
527 --
528 -- Parameters:
529 --
530 --------------------------------------------------------------------------------
531 function QueryHits(
532 P_PORTING_ID in varchar2,
533 P_SUBSCRIPTION_TYPE in varchar2,
534 P_SUBSCRIPTION_TN in varchar2,
535 P_STATUS_DISPLAY_NAME in varchar2,
536 P_CUSTOMER_ID in varchar2,
537 P_CUSTOMER_NAME in varchar2,
538 P_REC_CODE in varchar2,
539 P_DON_CODE in varchar2,
540 P_NRC_CODE in varchar2) return number is
541 I_QUERY varchar2(2000) := '';
542 I_CURSOR integer;
543 I_VOID integer;
544 I_FROM_POS integer := 0;
545 I_COUNT number(10);
546 begin
547
548 if not BuildSQL(P_PORTING_ID,
549 P_SUBSCRIPTION_TYPE,
550 P_SUBSCRIPTION_TN,
551 P_STATUS_DISPLAY_NAME,
552 P_CUSTOMER_ID,
553 P_CUSTOMER_NAME,
554 P_REC_CODE,
555 P_DON_CODE,
556 P_NRC_CODE) then
557 return -1;
558 end if;
559
560 if not PreQuery(P_PORTING_ID,
561 P_SUBSCRIPTION_TYPE,
562 P_SUBSCRIPTION_TN,
563 P_STATUS_DISPLAY_NAME,
564 P_CUSTOMER_ID,
565 P_CUSTOMER_NAME,
566 P_REC_CODE,
567 P_DON_CODE,
568 P_NRC_CODE) then
569 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
570 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', DEF_BODY_ATTRIBUTES);
571 return -1;
572 end if;
573
574 I_FROM_POS := instr(upper(ZONE_SQL), ' FROM ');
575
576 if I_FROM_POS = 0 then
577 return -1;
578 end if;
579
580 I_QUERY := 'SELECT count(*)' ||
581 substr(ZONE_SQL, I_FROM_POS);
582
583 I_CURSOR := dbms_sql.open_cursor;
584 dbms_sql.parse(I_CURSOR, I_QUERY, dbms_sql.v7);
585 dbms_sql.define_column(I_CURSOR, 1, I_COUNT);
586 I_VOID := dbms_sql.execute(I_CURSOR);
587 I_VOID := dbms_sql.fetch_rows(I_CURSOR);
588 dbms_sql.column_value(I_CURSOR, 1, I_COUNT);
589 dbms_sql.close_cursor(I_CURSOR);
590
591 return I_COUNT;
592
593 exception
594 when others then
595 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
596 DEF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.QueryHits');
597 return -1;
598 end;--------------------------------------------------------------------------------
599 -- Name: xnp_sv_orders$soa_sv.BuildSQL
600 --
601 -- Description: Builds the SQL for the 'SOA_SV' module component (Porting Ordering Subscriptions).
602 -- This incorporates all query criteria and Foreign key columns.
603 --
604 -- Parameters:
605 --
606 --------------------------------------------------------------------------------
607 function BuildSQL(
608 P_PORTING_ID in varchar2,
609 P_SUBSCRIPTION_TYPE in varchar2,
610 P_SUBSCRIPTION_TN in varchar2,
611 P_STATUS_DISPLAY_NAME in varchar2,
612 P_CUSTOMER_ID in varchar2,
613 P_CUSTOMER_NAME in varchar2,
614 P_REC_CODE in varchar2,
615 P_DON_CODE in varchar2,
616 P_NRC_CODE in varchar2) return boolean is
617
618 I_WHERE varchar2(2000);
619 begin
620
621 InitialiseDomain('SUBSCRIPTION_TYPE');
622
623 -- Build up the Where clause
624
625 XNP_WSGL.BuildWhere(P_PORTING_ID, 'XNP_SV_SOA_VL.PORTING_ID', XNP_WSGL.TYPE_CHAR, I_WHERE);
626 XNP_WSGL.BuildWhere(XNP_WSGL.DomainValue(D_SUBSCRIPTION_TYPE, P_SUBSCRIPTION_TYPE), 'XNP_SV_SOA_VL.SUBSCRIPTION_TYPE', XNP_WSGL.TYPE_CHAR, I_WHERE);
627 XNP_WSGL.BuildWhere(P_SUBSCRIPTION_TN, 'XNP_SV_SOA_VL.SUBSCRIPTION_TN', XNP_WSGL.TYPE_CHAR, I_WHERE);
628 XNP_WSGL.BuildWhere(P_STATUS_DISPLAY_NAME, 'XNP_SV_SOA_VL.STATUS_DISPLAY_NAME', XNP_WSGL.TYPE_CHAR, I_WHERE);
629 XNP_WSGL.BuildWhere(P_CUSTOMER_ID, 'XNP_SV_SOA_VL.CUSTOMER_ID', XNP_WSGL.TYPE_CHAR, I_WHERE);
630 XNP_WSGL.BuildWhere(P_CUSTOMER_NAME, 'XNP_SV_SOA_VL.CUSTOMER_NAME', XNP_WSGL.TYPE_CHAR, I_WHERE);
631 XNP_WSGL.BuildWhere(P_REC_CODE, 'XNP_SV_SOA_VL.REC_CODE', XNP_WSGL.TYPE_CHAR, I_WHERE);
632 XNP_WSGL.BuildWhere(P_DON_CODE, 'XNP_SV_SOA_VL.DON_CODE', XNP_WSGL.TYPE_CHAR, I_WHERE);
633 XNP_WSGL.BuildWhere(P_NRC_CODE, 'XNP_SV_SOA_VL.NRC_CODE', XNP_WSGL.TYPE_CHAR, I_WHERE);
634
635 ZONE_SQL := 'SELECT XNP_SV_SOA_VL.PORTING_ID,
636 XNP_SV_SOA_VL.SUBSCRIPTION_TN,
637 XNP_SV_SOA_VL.STATUS_DISPLAY_NAME,
638 XNP_SV_SOA_VL.SV_SOA_ID
639 FROM XNP_SV_SOA_VL XNP_SV_SOA_VL';
640
641 ZONE_SQL := ZONE_SQL || I_WHERE;
642 ZONE_SQL := ZONE_SQL || ' ORDER BY 1';
643
644 return true;
645
646 exception
647 when others then
648 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
649 DEF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.BuildSQL');
650 return false;
651 end;
652
653
654 --------------------------------------------------------------------------------
655 -- Name: xnp_sv_orders$soa_sv.FormQuery
656 --
657 -- Description: This procedure builds an HTML form for entry of query criteria.
658 -- The criteria entered are to restrict the query of the 'SOA_SV'
659 -- module component (Porting Ordering Subscriptions).
660 --
661 -- Parameters:
662 --
663 --------------------------------------------------------------------------------
664 procedure FormQuery(
665 Z_DIRECT_CALL in boolean,
666 Z_CHK in varchar2) is
667 begin
668
669 if not Z_DIRECT_CALL then
670 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
671 return;
672 end if;
673 end if;
674
675 XNP_WSGL.OpenPageHead('Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions');
676 CreateQueryJavaScript;
677 xnp_sv_orders$.TemplateHeader(TRUE,0);
678 XNP_WSGL.ClosePageHead;
679
680 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>QF_BODY_ATTRIBUTES);
681
682 htp.p(htf.bold(fnd_message.get_string('XNP','WEB_TITLE')));
683 xnp_sv_orders$.FirstPage(TRUE);
684 htp.p(htf.header(1,fnd_message.get_string('XNP','WEB_XNPSVORD_DETAILS_TITLE')));
685 htp.para;
686 htp.p(XNP_WSGL.MsgGetText(116,XNP_WSGLM.DSP116_ENTER_QRY_CAPTION,'Porting Ordering Subscriptions'));
687 htp.para;
688
689 htp.formOpen(curl => 'xnp_sv_orders$soa_sv.actionquery', cattributes => 'NAME="frmZero"');
690
691 XNP_WSGL.LayoutOpen(XNP_WSGL.LAYOUT_TABLE);
692
693 XNP_WSGL.LayoutRowStart;
694 for i in 1..QF_NUMBER_OF_COLUMNS loop
695 XNP_WSGL.LayoutHeader(18, 'LEFT', NULL);
696 XNP_WSGL.LayoutHeader(40, 'LEFT', NULL);
697 end loop;
698 XNP_WSGL.LayoutRowEnd;
699
700 XNP_WSGL.LayoutRowStart('TOP');
701 XNP_WSGL.LayoutData(htf.bold('Porting ID:'));
702 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('PORTING_ID', '10', FALSE));
703 XNP_WSGL.LayoutRowEnd;
704 XNP_WSGL.LayoutRowStart('TOP');
705 XNP_WSGL.LayoutData(htf.bold('Subscription Type:'));
706 InitialiseDomain('SUBSCRIPTION_TYPE');
707 XNP_WSGL.LayoutData(XNP_WSGL.BuildDVControl(D_SUBSCRIPTION_TYPE, XNP_WSGL.CTL_QUERY));
708 XNP_WSGL.LayoutRowEnd;
709 XNP_WSGL.LayoutRowStart('TOP');
710 XNP_WSGL.LayoutData(htf.bold('Telephone:'));
711 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('SUBSCRIPTION_TN', '20', FALSE));
712 XNP_WSGL.LayoutRowEnd;
713 XNP_WSGL.LayoutRowStart('TOP');
714 XNP_WSGL.LayoutData(htf.bold('Status:'));
715 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('STATUS_DISPLAY_NAME', '20', FALSE));
716 XNP_WSGL.LayoutRowEnd;
717 XNP_WSGL.LayoutRowStart('TOP');
718 XNP_WSGL.LayoutData(htf.bold('Customer ID:'));
719 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('CUSTOMER_ID', '10', FALSE));
720 XNP_WSGL.LayoutRowEnd;
721 XNP_WSGL.LayoutRowStart('TOP');
722 XNP_WSGL.LayoutData(htf.bold('Customer Name:'));
723 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('CUSTOMER_NAME', '40', FALSE));
724 XNP_WSGL.LayoutRowEnd;
725 XNP_WSGL.LayoutRowStart('TOP');
726 XNP_WSGL.LayoutData(htf.bold('Rec. Code:'));
727 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('REC_CODE', '10', FALSE));
728 XNP_WSGL.LayoutRowEnd;
729 XNP_WSGL.LayoutRowStart('TOP');
730 XNP_WSGL.LayoutData(htf.bold('Don. Code:'));
731 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('DON_CODE', '10', FALSE));
732 XNP_WSGL.LayoutRowEnd;
733 XNP_WSGL.LayoutRowStart('TOP');
734 XNP_WSGL.LayoutData(htf.bold('NRC Code:'));
735 XNP_WSGL.LayoutData(XNP_WSGL.BuildQueryControl('NRC_CODE', '10', FALSE));
736 XNP_WSGL.LayoutRowEnd;
737
738 XNP_WSGL.LayoutClose;
739
740 htp.p ('<SCRIPT><!--');
741 htp.p ('document.write (''<input type=hidden name="Z_ACTION">'')');
742 htp.p ('//-->');
743 htp.p ('</SCRIPT>');
744 htp.p ('<SCRIPT><!--');
745 htp.p ('document.write (''' || htf.formSubmit('', htf.escape_sc(QF_QUERY_BUT_CAPTION), 'onClick="this.form.Z_ACTION.value=\''' || QF_QUERY_BUT_ACTION || '\''"') || ''')');
746 htp.p ('//-->');
747 htp.p ('</SCRIPT>');
748
749 if XNP_WSGL.IsSupported ('NOSCRIPT')
750 then
751
752 htp.p ('<NOSCRIPT>');
753 htp.formSubmit('Z_ACTION', htf.escape_sc(QF_QUERY_BUT_CAPTION));
754 htp.p ('</NOSCRIPT>');
755
756 end if;
757 htp.formReset(htf.escape_sc(QF_CLEAR_BUT_CAPTION));
758
759 XNP_WSGL.HiddenField('Z_CHK',
760 to_char(XNP_WSGL.Checksum('')));
761 htp.formClose;
762
763 htp.p(htf.img('/OA_MEDIA/FNDLOGOS.gif'));
764
765 XNP_WSGL.ClosePageBody;
766
767 exception
768 when others then
769 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
770 QF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.FormQuery');
771 XNP_WSGL.ClosePageBody;
772 end;
773
774 --------------------------------------------------------------------------------
775 -- Name: xnp_sv_orders$soa_sv.FormView
776 --
777 -- Description: This procedure builds an HTML form for view/update of fields in
778 -- the 'SOA_SV' module component (Porting Ordering Subscriptions).
779 --
780 -- Parameters: Z_FORM_STATUS Status of the form
781 --
782 --------------------------------------------------------------------------------
783 procedure FormView(Z_FORM_STATUS in number) is
784
785 SOA_EVENTS_CHCK varchar2(10);
786 SOA_ORDERS_CHCK varchar2(10);
787
788 begin
789
790 XNP_WSGL.OpenPageHead('Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions');
791 xnp_sv_orders$.TemplateHeader(TRUE,0);
792 XNP_WSGL.ClosePageHead;
793
794 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>VF_BODY_ATTRIBUTES);
795
796 InitialiseDomain('SUBSCRIPTION_TYPE');
797 InitialiseDomain('STATUS_PHASE');
798 InitialiseDomain('ORDER_PRIORITY');
799 InitialiseDomain('NEW_SP_AUTHORIZATION_FLAG');
800 InitialiseDomain('OLD_SP_AUTHORIZATION_FLAG');
801 InitialiseDomain('RETAIN_DIR_INFO_FLAG');
802 InitialiseDomain('LOCKED_FLAG');
803 InitialiseDomain('CONCURRENCE_FLAG');
804 InitialiseDomain('PTO_FLAG');
805 InitialiseDomain('RETAIN_TN_FLAG');
806 InitialiseDomain('BLOCKED_FLAG');
807
808
809
810 FORM_VAL.PORTING_ID := CURR_VAL.PORTING_ID;
811 FORM_VAL.SUBSCRIPTION_TYPE := XNP_WSGL.DomainMeaning(D_SUBSCRIPTION_TYPE, CURR_VAL.SUBSCRIPTION_TYPE);
812 FORM_VAL.SUBSCRIPTION_TN := CURR_VAL.SUBSCRIPTION_TN;
813 FORM_VAL.PRE_SPLIT_SUBSCRIPTION_TN := CURR_VAL.PRE_SPLIT_SUBSCRIPTION_TN;
814 FORM_VAL.ROUTING_NUMBER := CURR_VAL.ROUTING_NUMBER;
815 FORM_VAL.STATUS_PHASE := XNP_WSGL.DomainMeaning(D_STATUS_PHASE, CURR_VAL.STATUS_PHASE);
816 FORM_VAL.STATUS_DISPLAY_NAME := CURR_VAL.STATUS_DISPLAY_NAME;
817 FORM_VAL.DSP_STATUS := NBT_VAL.DSP_STATUS;
818 FORM_VAL.STATUS_CHANGE_DATE := ltrim(to_char(CURR_VAL.STATUS_CHANGE_DATE, 'DD-MON-RRRR'));
819 FORM_VAL.STATUS_CHANGE_CAUSE_CODE := CURR_VAL.STATUS_CHANGE_CAUSE_CODE;
820 FORM_VAL.DSP_CUSTOMER := NBT_VAL.DSP_CUSTOMER;
821 FORM_VAL.DSP_CONTACT := NBT_VAL.DSP_CONTACT;
822 FORM_VAL.CUSTOMER_ID := CURR_VAL.CUSTOMER_ID;
823 FORM_VAL.CUSTOMER_NAME := CURR_VAL.CUSTOMER_NAME;
824 FORM_VAL.CONTACT_NAME := CURR_VAL.CONTACT_NAME;
825 FORM_VAL.PHONE := CURR_VAL.PHONE;
826 FORM_VAL.EMAIL := CURR_VAL.EMAIL;
827 FORM_VAL.ACTIVATION_DUE_DATE := ltrim(to_char(CURR_VAL.ACTIVATION_DUE_DATE, 'DD-MON-RRRR'));
828 FORM_VAL.ORDER_PRIORITY := XNP_WSGL.DomainMeaning(D_ORDER_PRIORITY, CURR_VAL.ORDER_PRIORITY);
829 FORM_VAL.PREORDER_AUTHORIZATION_CODE := CURR_VAL.PREORDER_AUTHORIZATION_CODE;
830 FORM_VAL.DSP_REC := NBT_VAL.DSP_REC;
831 FORM_VAL.REC_CODE := CURR_VAL.REC_CODE;
832 FORM_VAL.REC_NAME := CURR_VAL.REC_NAME;
833 FORM_VAL.REC_EMAIL := CURR_VAL.REC_EMAIL;
834 FORM_VAL.DSP_DON := NBT_VAL.DSP_DON;
835 FORM_VAL.DON_CODE := CURR_VAL.DON_CODE;
836 FORM_VAL.DON_NAME := CURR_VAL.DON_NAME;
837 FORM_VAL.DON_EMAIL := CURR_VAL.DON_EMAIL;
838 FORM_VAL.DSP_NRC := NBT_VAL.DSP_NRC;
839 FORM_VAL.NRC_CODE := CURR_VAL.NRC_CODE;
840 FORM_VAL.NRC_NAME := CURR_VAL.NRC_NAME;
841 FORM_VAL.NRC_EMAIL := CURR_VAL.NRC_EMAIL;
842 FORM_VAL.NEW_SP_AUTHORIZATION_FLAG := XNP_WSGL.DomainMeaning(D_NEW_SP_AUTHORIZATION_FLAG, CURR_VAL.NEW_SP_AUTHORIZATION_FLAG);
843 FORM_VAL.NEW_SP_DUE_DATE := ltrim(to_char(CURR_VAL.NEW_SP_DUE_DATE, 'DD-MON-RRRR'));
844 FORM_VAL.OLD_SP_AUTHORIZATION_FLAG := XNP_WSGL.DomainMeaning(D_OLD_SP_AUTHORIZATION_FLAG, CURR_VAL.OLD_SP_AUTHORIZATION_FLAG);
845 FORM_VAL.OLD_SP_DUE_DATE := ltrim(to_char(CURR_VAL.OLD_SP_DUE_DATE, 'DD-MON-RRRR'));
846 FORM_VAL.OLD_SP_CUTOFF_DUE_DATE := ltrim(to_char(CURR_VAL.OLD_SP_CUTOFF_DUE_DATE, 'DD-MON-RRRR'));
847 FORM_VAL.INVOICE_DUE_DATE := ltrim(to_char(CURR_VAL.INVOICE_DUE_DATE, 'DD-MON-RRRR'));
848 FORM_VAL.RETAIN_DIR_INFO_FLAG := XNP_WSGL.DomainMeaning(D_RETAIN_DIR_INFO_FLAG, CURR_VAL.RETAIN_DIR_INFO_FLAG);
849 FORM_VAL.LOCKED_FLAG := XNP_WSGL.DomainMeaning(D_LOCKED_FLAG, CURR_VAL.LOCKED_FLAG);
850 FORM_VAL.CONCURRENCE_FLAG := XNP_WSGL.DomainMeaning(D_CONCURRENCE_FLAG, CURR_VAL.CONCURRENCE_FLAG);
851 FORM_VAL.PTO_FLAG := XNP_WSGL.DomainMeaning(D_PTO_FLAG, CURR_VAL.PTO_FLAG);
852 FORM_VAL.DISCONNECT_DUE_DATE := ltrim(to_char(CURR_VAL.DISCONNECT_DUE_DATE, 'DD-MON-RRRR'));
853 FORM_VAL.EFFECTIVE_RELEASE_DUE_DATE := ltrim(to_char(CURR_VAL.EFFECTIVE_RELEASE_DUE_DATE, 'DD-MON-RRRR'));
854 FORM_VAL.RETAIN_TN_FLAG := XNP_WSGL.DomainMeaning(D_RETAIN_TN_FLAG, CURR_VAL.RETAIN_TN_FLAG);
855 FORM_VAL.BLOCKED_FLAG := XNP_WSGL.DomainMeaning(D_BLOCKED_FLAG, CURR_VAL.BLOCKED_FLAG);
856 FORM_VAL.NUMBER_RETURNED_DUE_DATE := ltrim(to_char(CURR_VAL.NUMBER_RETURNED_DUE_DATE, 'DD-MON-RRRR'));
857 FORM_VAL.DSP_CNAM := NBT_VAL.DSP_CNAM;
858 FORM_VAL.DSP_CLASS := NBT_VAL.DSP_CLASS;
859 FORM_VAL.DSP_ISVM := NBT_VAL.DSP_ISVM;
860 FORM_VAL.DSP_LIDB := NBT_VAL.DSP_LIDB;
861 FORM_VAL.DSP_WSMSC := NBT_VAL.DSP_WSMSC;
862 FORM_VAL.DSP_RN := NBT_VAL.DSP_RN;
863 FORM_VAL.CLASS_ADDRESS := CURR_VAL.CLASS_ADDRESS;
864 FORM_VAL.CLASS_SUBSYSTEM := CURR_VAL.CLASS_SUBSYSTEM;
865 FORM_VAL.CNAM_ADDRESS := CURR_VAL.CNAM_ADDRESS;
866 FORM_VAL.CNAM_SUBSYSTEM := CURR_VAL.CNAM_SUBSYSTEM;
867 FORM_VAL.ISVM_ADDRESS := CURR_VAL.ISVM_ADDRESS;
868 FORM_VAL.ISVM_SUBSYSTEM := CURR_VAL.ISVM_SUBSYSTEM;
869 FORM_VAL.LIDB_ADDRESS := CURR_VAL.LIDB_ADDRESS;
870 FORM_VAL.LIDB_SUBSYSTEM := CURR_VAL.LIDB_SUBSYSTEM;
871 FORM_VAL.WSMSC_ADDRESS := CURR_VAL.WSMSC_ADDRESS;
872 FORM_VAL.WSMSC_SUBSYSTEM := CURR_VAL.WSMSC_SUBSYSTEM;
873 FORM_VAL.RN_ADDRESS := CURR_VAL.RN_ADDRESS;
874 FORM_VAL.RN_SUBSYSTEM := CURR_VAL.RN_SUBSYSTEM;
875 FORM_VAL.SV_SOA_ID := CURR_VAL.SV_SOA_ID;
876
877 if Z_FORM_STATUS = XNP_WSGL.FORM_STATUS_ERROR then
878 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
879 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', VF_BODY_ATTRIBUTES);
880 elsif Z_FORM_STATUS = XNP_WSGL.FORM_STATUS_UPD then
881 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_SUCCESS, XNP_WSGL.MsgGetText(207, XNP_WSGLM.MSG207_ROW_UPDATED),
882 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', VF_BODY_ATTRIBUTES);
883 elsif Z_FORM_STATUS = XNP_WSGL.FORM_STATUS_INS then
884 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_SUCCESS, XNP_WSGL.MsgGetText(208, XNP_WSGLM.MSG208_ROW_INSERTED),
885 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', VF_BODY_ATTRIBUTES);
886 end if;
887
888
889 XNP_WSGL.LayoutOpen(XNP_WSGL.LAYOUT_TABLE, P_BORDER=>TRUE);
890
891 XNP_WSGL.LayoutRowStart;
892 for i in 1..VF_NUMBER_OF_COLUMNS loop
893 XNP_WSGL.LayoutHeader(23, 'LEFT', NULL);
894 XNP_WSGL.LayoutHeader(40, 'LEFT', NULL);
895 end loop;
896 XNP_WSGL.LayoutRowEnd;
897
898 XNP_WSGL.LayoutRowStart('TOP');
899 XNP_WSGL.LayoutData(htf.bold('Porting ID:'));
900 XNP_WSGL.LayoutData(htf.bold(FORM_VAL.PORTING_ID));
901 XNP_WSGL.LayoutRowEnd;
902 XNP_WSGL.LayoutRowStart('TOP');
903 XNP_WSGL.LayoutData(htf.bold('Subscription Type:'));
904 XNP_WSGL.LayoutData(FORM_VAL.SUBSCRIPTION_TYPE);
905 XNP_WSGL.LayoutRowEnd;
906 XNP_WSGL.LayoutRowStart('TOP');
907 XNP_WSGL.LayoutData(htf.bold('Telephone:'));
908 XNP_WSGL.LayoutData(htf.bold(FORM_VAL.SUBSCRIPTION_TN));
909 XNP_WSGL.LayoutRowEnd;
910 XNP_WSGL.LayoutRowStart('TOP');
911 XNP_WSGL.LayoutData(htf.bold('Pre-Split Number:'));
912 XNP_WSGL.LayoutData(FORM_VAL.PRE_SPLIT_SUBSCRIPTION_TN);
913 XNP_WSGL.LayoutRowEnd;
914 XNP_WSGL.LayoutRowStart('TOP');
915 XNP_WSGL.LayoutData(htf.bold('Routing Number:'));
916 XNP_WSGL.LayoutData(FORM_VAL.ROUTING_NUMBER);
917 XNP_WSGL.LayoutRowEnd;
918 if NBT_VAL.DSP_STATUS is not null then
919 XNP_WSGL.LayoutRowStart('TOP');
920 XNP_WSGL.LayoutData(htf.bold('Status:'));
921 XNP_WSGL.LayoutData(htf.bold(FORM_VAL.DSP_STATUS));
922 XNP_WSGL.LayoutRowEnd;
923 end if;
924 if NBT_VAL.DSP_CUSTOMER is not null then
925 XNP_WSGL.LayoutRowStart('TOP');
926 XNP_WSGL.LayoutData(htf.bold('Customer:'));
927 XNP_WSGL.LayoutData(FORM_VAL.DSP_CUSTOMER);
928 XNP_WSGL.LayoutRowEnd;
929 end if;
930 if NBT_VAL.DSP_CONTACT is not null then
931 XNP_WSGL.LayoutRowStart('TOP');
932 XNP_WSGL.LayoutData(htf.bold('Contact:'));
933 XNP_WSGL.LayoutData(FORM_VAL.DSP_CONTACT);
934 XNP_WSGL.LayoutRowEnd;
935 end if;
936 XNP_WSGL.LayoutRowStart('TOP');
937 XNP_WSGL.LayoutData(htf.bold('Contact Email:'));
938 XNP_WSGL.LayoutData(htf.mailto(FORM_VAL.EMAIL, FORM_VAL.EMAIL));
939 XNP_WSGL.LayoutRowEnd;
940 XNP_WSGL.LayoutRowStart('TOP');
941 XNP_WSGL.LayoutData(htf.bold('Activation Due Date:'));
942 XNP_WSGL.LayoutData(FORM_VAL.ACTIVATION_DUE_DATE);
943 XNP_WSGL.LayoutRowEnd;
944 XNP_WSGL.LayoutRowStart('TOP');
945 XNP_WSGL.LayoutData(htf.bold('Priority:'));
946 XNP_WSGL.LayoutData(FORM_VAL.ORDER_PRIORITY);
947 XNP_WSGL.LayoutRowEnd;
948 XNP_WSGL.LayoutRowStart('TOP');
949 XNP_WSGL.LayoutData(htf.bold('Auth Code:'));
950 XNP_WSGL.LayoutData(FORM_VAL.PREORDER_AUTHORIZATION_CODE);
951 XNP_WSGL.LayoutRowEnd;
952 if NBT_VAL.DSP_REC is not null then
953 XNP_WSGL.LayoutRowStart('TOP');
954 XNP_WSGL.LayoutData(htf.bold('Recipient:'));
955 XNP_WSGL.LayoutData(FORM_VAL.DSP_REC);
956 XNP_WSGL.LayoutRowEnd;
957 end if;
958 XNP_WSGL.LayoutRowStart('TOP');
959 XNP_WSGL.LayoutData(htf.bold('Rec Email:'));
960 XNP_WSGL.LayoutData(htf.mailto(FORM_VAL.REC_EMAIL, FORM_VAL.REC_EMAIL));
961 XNP_WSGL.LayoutRowEnd;
962 if NBT_VAL.DSP_DON is not null then
963 XNP_WSGL.LayoutRowStart('TOP');
964 XNP_WSGL.LayoutData(htf.bold('Donor:'));
965 XNP_WSGL.LayoutData(FORM_VAL.DSP_DON);
966 XNP_WSGL.LayoutRowEnd;
967 end if;
968 XNP_WSGL.LayoutRowStart('TOP');
969 XNP_WSGL.LayoutData(htf.bold('Don Email:'));
970 XNP_WSGL.LayoutData(htf.mailto(FORM_VAL.DON_EMAIL, FORM_VAL.DON_EMAIL));
971 XNP_WSGL.LayoutRowEnd;
972 if NBT_VAL.DSP_NRC is not null then
973 XNP_WSGL.LayoutRowStart('TOP');
974 XNP_WSGL.LayoutData(htf.bold('Mediator:'));
975 XNP_WSGL.LayoutData(FORM_VAL.DSP_NRC);
976 XNP_WSGL.LayoutRowEnd;
977 end if;
978 XNP_WSGL.LayoutRowStart('TOP');
979 XNP_WSGL.LayoutData(htf.bold('NRC Email:'));
980 XNP_WSGL.LayoutData(htf.mailto(FORM_VAL.NRC_EMAIL, FORM_VAL.NRC_EMAIL));
981 XNP_WSGL.LayoutRowEnd;
982 XNP_WSGL.LayoutRowStart('TOP');
983 XNP_WSGL.LayoutData(htf.bold('Rec. Authorized?:'));
984 XNP_WSGL.LayoutData(FORM_VAL.NEW_SP_AUTHORIZATION_FLAG);
985 XNP_WSGL.LayoutRowEnd;
986 XNP_WSGL.LayoutRowStart('TOP');
987 XNP_WSGL.LayoutData(htf.bold('Rec. Due Date:'));
988 XNP_WSGL.LayoutData(FORM_VAL.NEW_SP_DUE_DATE);
989 XNP_WSGL.LayoutRowEnd;
990 XNP_WSGL.LayoutRowStart('TOP');
991 XNP_WSGL.LayoutData(htf.bold('Don. Authorized?:'));
992 XNP_WSGL.LayoutData(FORM_VAL.OLD_SP_AUTHORIZATION_FLAG);
993 XNP_WSGL.LayoutRowEnd;
994 XNP_WSGL.LayoutRowStart('TOP');
995 XNP_WSGL.LayoutData(htf.bold('Don. Due Date:'));
996 XNP_WSGL.LayoutData(FORM_VAL.OLD_SP_DUE_DATE);
997 XNP_WSGL.LayoutRowEnd;
998 XNP_WSGL.LayoutRowStart('TOP');
999 XNP_WSGL.LayoutData(htf.bold('Don. Cutoff Due Date:'));
1000 XNP_WSGL.LayoutData(FORM_VAL.OLD_SP_CUTOFF_DUE_DATE);
1001 XNP_WSGL.LayoutRowEnd;
1002 XNP_WSGL.LayoutRowStart('TOP');
1003 XNP_WSGL.LayoutData(htf.bold('Invoice Due Date:'));
1004 XNP_WSGL.LayoutData(FORM_VAL.INVOICE_DUE_DATE);
1005 XNP_WSGL.LayoutRowEnd;
1006 XNP_WSGL.LayoutRowStart('TOP');
1007 XNP_WSGL.LayoutData(htf.bold('Retain Dir Info?:'));
1008 XNP_WSGL.LayoutData(FORM_VAL.RETAIN_DIR_INFO_FLAG);
1009 XNP_WSGL.LayoutRowEnd;
1010 XNP_WSGL.LayoutRowStart('TOP');
1011 XNP_WSGL.LayoutData(htf.bold('Update Allowed?:'));
1012 XNP_WSGL.LayoutData(FORM_VAL.LOCKED_FLAG);
1013 XNP_WSGL.LayoutRowEnd;
1014 XNP_WSGL.LayoutRowStart('TOP');
1015 XNP_WSGL.LayoutData(htf.bold('Porting To Original?:'));
1016 XNP_WSGL.LayoutData(FORM_VAL.PTO_FLAG);
1017 XNP_WSGL.LayoutRowEnd;
1018 XNP_WSGL.LayoutRowStart('TOP');
1019 XNP_WSGL.LayoutData(htf.bold('Disconnect Due Date:'));
1020 XNP_WSGL.LayoutData(FORM_VAL.DISCONNECT_DUE_DATE);
1021 XNP_WSGL.LayoutRowEnd;
1022 XNP_WSGL.LayoutRowStart('TOP');
1023 XNP_WSGL.LayoutData(htf.bold('Eff. Release Due Date:'));
1024 XNP_WSGL.LayoutData(FORM_VAL.EFFECTIVE_RELEASE_DUE_DATE);
1025 XNP_WSGL.LayoutRowEnd;
1026 XNP_WSGL.LayoutRowStart('TOP');
1027 XNP_WSGL.LayoutData(htf.bold('Retain Number?:'));
1028 XNP_WSGL.LayoutData(FORM_VAL.RETAIN_TN_FLAG);
1029 XNP_WSGL.LayoutRowEnd;
1030 XNP_WSGL.LayoutRowStart('TOP');
1031 XNP_WSGL.LayoutData(htf.bold('Number Blocked?:'));
1032 XNP_WSGL.LayoutData(FORM_VAL.BLOCKED_FLAG);
1033 XNP_WSGL.LayoutRowEnd;
1034 XNP_WSGL.LayoutRowStart('TOP');
1035 XNP_WSGL.LayoutData(htf.bold('Number Return Due Date:'));
1036 XNP_WSGL.LayoutData(FORM_VAL.NUMBER_RETURNED_DUE_DATE);
1037 XNP_WSGL.LayoutRowEnd;
1038 if NBT_VAL.DSP_CNAM is not null then
1039 XNP_WSGL.LayoutRowStart('TOP');
1040 XNP_WSGL.LayoutData(htf.bold('CNAM Details:'));
1041 XNP_WSGL.LayoutData(FORM_VAL.DSP_CNAM);
1042 XNP_WSGL.LayoutRowEnd;
1043 end if;
1044 if NBT_VAL.DSP_CLASS is not null then
1045 XNP_WSGL.LayoutRowStart('TOP');
1046 XNP_WSGL.LayoutData(htf.bold('CLASS Details:'));
1047 XNP_WSGL.LayoutData(FORM_VAL.DSP_CLASS);
1048 XNP_WSGL.LayoutRowEnd;
1049 end if;
1050 if NBT_VAL.DSP_ISVM is not null then
1051 XNP_WSGL.LayoutRowStart('TOP');
1052 XNP_WSGL.LayoutData(htf.bold('ISVM Details:'));
1053 XNP_WSGL.LayoutData(FORM_VAL.DSP_ISVM);
1054 XNP_WSGL.LayoutRowEnd;
1055 end if;
1056 if NBT_VAL.DSP_LIDB is not null then
1057 XNP_WSGL.LayoutRowStart('TOP');
1058 XNP_WSGL.LayoutData(htf.bold('LIDB Details:'));
1059 XNP_WSGL.LayoutData(FORM_VAL.DSP_LIDB);
1060 XNP_WSGL.LayoutRowEnd;
1061 end if;
1062 if NBT_VAL.DSP_WSMSC is not null then
1063 XNP_WSGL.LayoutRowStart('TOP');
1064 XNP_WSGL.LayoutData(htf.bold('WSMSC Details:'));
1065 XNP_WSGL.LayoutData(FORM_VAL.DSP_WSMSC);
1066 XNP_WSGL.LayoutRowEnd;
1067 end if;
1068 if NBT_VAL.DSP_RN is not null then
1069 XNP_WSGL.LayoutRowStart('TOP');
1070 XNP_WSGL.LayoutData(htf.bold('RN Details:'));
1071 XNP_WSGL.LayoutData(FORM_VAL.DSP_RN);
1072 XNP_WSGL.LayoutRowEnd;
1073 end if;
1074
1075 XNP_WSGL.LayoutClose;
1076
1077
1078
1079
1080 SOA_EVENTS_CHCK :=
1081 to_char(XNP_WSGL.Checksum(CURR_VAL.SV_SOA_ID));
1082 SOA_ORDERS_CHCK :=
1083 to_char(XNP_WSGL.Checksum(CURR_VAL.SV_SOA_ID));
1084
1085
1086 XNP_WSGL.NavLinks(XNP_WSGL.MENU_LONG, 'Event History',
1087 0, 'xnp_sv_orders$soa_events.startup?P_SV_SOA_ID='||XNP_WSGL.EscapeURLParam(CURR_VAL.SV_SOA_ID)||'&Z_CHK='||SOA_EVENTS_CHCK);
1088 XNP_WSGL.NavLinks(XNP_WSGL.MENU_LONG, 'Order Workitems',
1089 0, 'xnp_sv_orders$soa_orders.startup?P_SV_SOA_ID='||XNP_WSGL.EscapeURLParam(CURR_VAL.SV_SOA_ID)||'&Z_CHK='||SOA_ORDERS_CHCK);
1090 XNP_WSGL.NavLinks;
1091
1092
1093 XNP_WSGL.ClosePageBody;
1094
1095 exception
1096 when others then
1097 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1098 VF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.FormView');
1099 XNP_WSGL.ClosePageBody;
1100 end;
1101
1102 --------------------------------------------------------------------------------
1103 -- Name: xnp_sv_orders$soa_sv.QueryView
1104 --
1105 -- Description: Queries the details of a single row in preparation for display.
1106 --
1107 -- Parameters:
1108 --
1109 --------------------------------------------------------------------------------
1110 procedure QueryView(
1111 P_SV_SOA_ID in varchar2,
1112 Z_POST_DML in boolean,
1113 Z_FORM_STATUS in number,
1114 Z_DIRECT_CALL in boolean,
1115 Z_CHK in varchar2) is
1116 begin
1117
1118 XNP_WSGL.RegisterURL('xnp_sv_orders$soa_sv.queryview');
1119 XNP_WSGL.AddURLParam('P_SV_SOA_ID', P_SV_SOA_ID);
1120 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
1121 if not Z_DIRECT_CALL then
1122 if not XNP_WSGL.ValidateChecksum('' ||
1123 P_SV_SOA_ID, Z_CHK) then
1124 return;
1125 end if;
1126 end if;
1127
1128
1129 if P_SV_SOA_ID is not null then
1130 CURR_VAL.SV_SOA_ID := P_SV_SOA_ID;
1131 end if;
1132 if Z_POST_DML then
1133
1134 null;
1135
1136 else
1137
1138 SELECT XNP_SV_SOA_VL.PORTING_ID,
1139 XNP_SV_SOA_VL.SUBSCRIPTION_TYPE,
1140 XNP_SV_SOA_VL.SUBSCRIPTION_TN,
1141 XNP_SV_SOA_VL.PRE_SPLIT_SUBSCRIPTION_TN,
1142 XNP_SV_SOA_VL.ROUTING_NUMBER,
1143 XNP_SV_SOA_VL.STATUS_PHASE,
1144 XNP_SV_SOA_VL.STATUS_DISPLAY_NAME,
1145 XNP_SV_SOA_VL.STATUS_CHANGE_DATE,
1146 XNP_SV_SOA_VL.STATUS_CHANGE_CAUSE_CODE,
1147 XNP_SV_SOA_VL.CUSTOMER_ID,
1148 XNP_SV_SOA_VL.CUSTOMER_NAME,
1149 XNP_SV_SOA_VL.CONTACT_NAME,
1150 XNP_SV_SOA_VL.PHONE,
1151 XNP_SV_SOA_VL.EMAIL,
1152 XNP_SV_SOA_VL.ACTIVATION_DUE_DATE,
1153 XNP_SV_SOA_VL.ORDER_PRIORITY,
1154 XNP_SV_SOA_VL.PREORDER_AUTHORIZATION_CODE,
1155 XNP_SV_SOA_VL.REC_CODE,
1156 XNP_SV_SOA_VL.REC_NAME,
1157 XNP_SV_SOA_VL.REC_EMAIL,
1158 XNP_SV_SOA_VL.DON_CODE,
1159 XNP_SV_SOA_VL.DON_NAME,
1160 XNP_SV_SOA_VL.DON_EMAIL,
1161 XNP_SV_SOA_VL.NRC_CODE,
1162 XNP_SV_SOA_VL.NRC_NAME,
1163 XNP_SV_SOA_VL.NRC_EMAIL,
1164 XNP_SV_SOA_VL.NEW_SP_AUTHORIZATION_FLAG,
1165 XNP_SV_SOA_VL.NEW_SP_DUE_DATE,
1166 XNP_SV_SOA_VL.OLD_SP_AUTHORIZATION_FLAG,
1167 XNP_SV_SOA_VL.OLD_SP_DUE_DATE,
1168 XNP_SV_SOA_VL.OLD_SP_CUTOFF_DUE_DATE,
1169 XNP_SV_SOA_VL.INVOICE_DUE_DATE,
1170 XNP_SV_SOA_VL.RETAIN_DIR_INFO_FLAG,
1171 XNP_SV_SOA_VL.LOCKED_FLAG,
1172 XNP_SV_SOA_VL.CONCURRENCE_FLAG,
1173 XNP_SV_SOA_VL.PTO_FLAG,
1174 XNP_SV_SOA_VL.DISCONNECT_DUE_DATE,
1175 XNP_SV_SOA_VL.EFFECTIVE_RELEASE_DUE_DATE,
1176 XNP_SV_SOA_VL.RETAIN_TN_FLAG,
1177 XNP_SV_SOA_VL.BLOCKED_FLAG,
1178 XNP_SV_SOA_VL.NUMBER_RETURNED_DUE_DATE,
1179 XNP_SV_SOA_VL.CLASS_ADDRESS,
1180 XNP_SV_SOA_VL.CLASS_SUBSYSTEM,
1181 XNP_SV_SOA_VL.CNAM_ADDRESS,
1182 XNP_SV_SOA_VL.CNAM_SUBSYSTEM,
1183 XNP_SV_SOA_VL.ISVM_ADDRESS,
1184 XNP_SV_SOA_VL.ISVM_SUBSYSTEM,
1185 XNP_SV_SOA_VL.LIDB_ADDRESS,
1186 XNP_SV_SOA_VL.LIDB_SUBSYSTEM,
1187 XNP_SV_SOA_VL.WSMSC_ADDRESS,
1188 XNP_SV_SOA_VL.WSMSC_SUBSYSTEM,
1189 XNP_SV_SOA_VL.RN_ADDRESS,
1190 XNP_SV_SOA_VL.RN_SUBSYSTEM,
1191 XNP_SV_SOA_VL.SV_SOA_ID
1192 INTO CURR_VAL.PORTING_ID,
1193 CURR_VAL.SUBSCRIPTION_TYPE,
1194 CURR_VAL.SUBSCRIPTION_TN,
1195 CURR_VAL.PRE_SPLIT_SUBSCRIPTION_TN,
1196 CURR_VAL.ROUTING_NUMBER,
1197 CURR_VAL.STATUS_PHASE,
1198 CURR_VAL.STATUS_DISPLAY_NAME,
1199 CURR_VAL.STATUS_CHANGE_DATE,
1200 CURR_VAL.STATUS_CHANGE_CAUSE_CODE,
1201 CURR_VAL.CUSTOMER_ID,
1202 CURR_VAL.CUSTOMER_NAME,
1203 CURR_VAL.CONTACT_NAME,
1204 CURR_VAL.PHONE,
1205 CURR_VAL.EMAIL,
1206 CURR_VAL.ACTIVATION_DUE_DATE,
1207 CURR_VAL.ORDER_PRIORITY,
1208 CURR_VAL.PREORDER_AUTHORIZATION_CODE,
1209 CURR_VAL.REC_CODE,
1210 CURR_VAL.REC_NAME,
1211 CURR_VAL.REC_EMAIL,
1212 CURR_VAL.DON_CODE,
1213 CURR_VAL.DON_NAME,
1214 CURR_VAL.DON_EMAIL,
1215 CURR_VAL.NRC_CODE,
1216 CURR_VAL.NRC_NAME,
1217 CURR_VAL.NRC_EMAIL,
1218 CURR_VAL.NEW_SP_AUTHORIZATION_FLAG,
1219 CURR_VAL.NEW_SP_DUE_DATE,
1220 CURR_VAL.OLD_SP_AUTHORIZATION_FLAG,
1221 CURR_VAL.OLD_SP_DUE_DATE,
1222 CURR_VAL.OLD_SP_CUTOFF_DUE_DATE,
1223 CURR_VAL.INVOICE_DUE_DATE,
1224 CURR_VAL.RETAIN_DIR_INFO_FLAG,
1225 CURR_VAL.LOCKED_FLAG,
1226 CURR_VAL.CONCURRENCE_FLAG,
1227 CURR_VAL.PTO_FLAG,
1228 CURR_VAL.DISCONNECT_DUE_DATE,
1229 CURR_VAL.EFFECTIVE_RELEASE_DUE_DATE,
1230 CURR_VAL.RETAIN_TN_FLAG,
1231 CURR_VAL.BLOCKED_FLAG,
1232 CURR_VAL.NUMBER_RETURNED_DUE_DATE,
1233 CURR_VAL.CLASS_ADDRESS,
1234 CURR_VAL.CLASS_SUBSYSTEM,
1235 CURR_VAL.CNAM_ADDRESS,
1236 CURR_VAL.CNAM_SUBSYSTEM,
1237 CURR_VAL.ISVM_ADDRESS,
1238 CURR_VAL.ISVM_SUBSYSTEM,
1239 CURR_VAL.LIDB_ADDRESS,
1240 CURR_VAL.LIDB_SUBSYSTEM,
1241 CURR_VAL.WSMSC_ADDRESS,
1242 CURR_VAL.WSMSC_SUBSYSTEM,
1243 CURR_VAL.RN_ADDRESS,
1244 CURR_VAL.RN_SUBSYSTEM,
1245 CURR_VAL.SV_SOA_ID
1246 FROM XNP_SV_SOA_VL XNP_SV_SOA_VL
1247 WHERE XNP_SV_SOA_VL.SV_SOA_ID = CURR_VAL.SV_SOA_ID
1248 ;
1249
1250 end if;
1251
1252 NBT_VAL.DSP_STATUS := CURR_VAL.STATUS_DISPLAY_NAME || ' (' || CURR_VAL.STATUS_PHASE || ') on ' || to_char(CURR_VAL.STATUS_CHANGE_DATE,'DD-MON-YYYY') || ' ' || CURR_VAL.STATUS_CHANGE_CAUSE_CODE;
1253 NBT_VAL.DSP_CUSTOMER := (CURR_VAL.CUSTOMER_ID || ' - ' || CURR_VAL.CUSTOMER_NAME );
1254 NBT_VAL.DSP_CONTACT := ( CURR_VAL.CONTACT_NAME || ' - ' || CURR_VAL.PHONE );
1255 NBT_VAL.DSP_REC := ( CURR_VAL.REC_CODE || ' - ' || CURR_VAL.REC_NAME );
1256 NBT_VAL.DSP_DON := ( CURR_VAL.DON_CODE || ' - ' || CURR_VAL.DON_NAME );
1257 NBT_VAL.DSP_NRC := ( CURR_VAL.NRC_CODE || ' - ' || CURR_VAL.NRC_NAME );
1258 NBT_VAL.DSP_CNAM := ('Address : ' || CURR_VAL.CNAM_ADDRESS || ' - Subsystem : ' || CURR_VAL.CNAM_SUBSYSTEM);
1259 NBT_VAL.DSP_CLASS := ('Address : ' || CURR_VAL.CLASS_ADDRESS || ' - Subsystem : ' || CURR_VAL.CLASS_SUBSYSTEM);
1260 NBT_VAL.DSP_ISVM := ('Address : ' || CURR_VAL.ISVM_ADDRESS || ' - Subsystem : ' || CURR_VAL.ISVM_SUBSYSTEM);
1261 NBT_VAL.DSP_LIDB := ('Address : ' || CURR_VAL.LIDB_ADDRESS || ' - Subsystem : ' || CURR_VAL.LIDB_SUBSYSTEM);
1262 NBT_VAL.DSP_WSMSC := ('Address : ' || CURR_VAL.WSMSC_ADDRESS || ' - Subsystem : ' || CURR_VAL.WSMSC_SUBSYSTEM);
1263 NBT_VAL.DSP_RN := ('Address : ' || CURR_VAL.RN_ADDRESS || ' - Subsystem : ' || CURR_VAL.RN_SUBSYSTEM);
1264
1265 if not PostQuery(Z_POST_DML) then
1266 FormView(XNP_WSGL.FORM_STATUS_ERROR);
1267 else
1268 FormView(Z_FORM_STATUS);
1269 end if;
1270
1271 exception
1272 when NO_DATA_FOUND then
1273 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_WSGL.MsgGetText(204, XNP_WSGLM.MSG204_ROW_DELETED),
1274 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', VF_BODY_ATTRIBUTES);
1275 when others then
1276 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1277 VF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.QueryView');
1278 end;
1279 --------------------------------------------------------------------------------
1280 -- Name: xnp_sv_orders$soa_sv.QueryList
1281 --
1282 -- Description: This procedure builds the Record list for the 'SOA_SV'
1283 -- module component (Porting Ordering Subscriptions).
1284 --
1285 -- The Record List displays context information for records which
1286 -- match the specified query criteria.
1287 -- Sets of records are displayed (10 records at a time)
1288 -- with Next/Previous buttons to get other record sets.
1289 --
1290 -- The first context column will be created as a link to the
1291 -- xnp_sv_orders$soa_sv.FormView procedure for display of more details
1292 -- of that particular row.
1293 --
1294 -- Parameters: P_PORTING_ID - Porting ID
1295 -- P_SUBSCRIPTION_TYPE - Subscription Type
1296 -- P_SUBSCRIPTION_TN - Telephone
1297 -- P_STATUS_DISPLAY_NAME - Status
1298 -- P_CUSTOMER_ID - Customer ID
1299 -- P_CUSTOMER_NAME - Customer Name
1300 -- P_REC_CODE - Rec. Code
1301 -- P_DON_CODE - Don. Code
1302 -- P_NRC_CODE - NRC Code
1303 -- Z_START - First record to display
1304 -- Z_ACTION - Next or Previous set
1305 --
1306 --------------------------------------------------------------------------------
1307 procedure QueryList(
1308 P_PORTING_ID in varchar2,
1309 P_SUBSCRIPTION_TYPE in varchar2,
1310 P_SUBSCRIPTION_TN in varchar2,
1311 P_STATUS_DISPLAY_NAME in varchar2,
1312 P_CUSTOMER_ID in varchar2,
1313 P_CUSTOMER_NAME in varchar2,
1314 P_REC_CODE in varchar2,
1315 P_DON_CODE in varchar2,
1316 P_NRC_CODE in varchar2,
1317 Z_START in varchar2,
1318 Z_ACTION in varchar2,
1319 Z_DIRECT_CALL in boolean,
1320 Z_CHK in varchar2) is
1321
1322 L_VF_FRAME varchar2(20) := 'fraVF';
1323 I_WHERE varchar2(2000) := '';
1324 I_CURSOR integer;
1325 I_VOID integer;
1326 I_ROWS_FETCHED integer := 0;
1327 I_TOTAL_ROWS integer := 0;
1328 I_START number(6) := to_number(Z_START);
1329 I_COUNT number(10) := 0;
1330 I_OF_TOTAL_TEXT varchar2(200) := '';
1331 I_NEXT_BUT boolean;
1332 I_PREV_BUT boolean;
1333 SOA_EVENTS_CHCK varchar2(10);
1334 SOA_ORDERS_CHCK varchar2(10);
1335 L_CHECKSUM varchar2(10);
1336
1337 begin
1338
1339 XNP_WSGL.RegisterURL('xnp_sv_orders$soa_sv.querylist');
1340 XNP_WSGL.AddURLParam('P_PORTING_ID', P_PORTING_ID);
1341 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TYPE', P_SUBSCRIPTION_TYPE);
1342 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TN', P_SUBSCRIPTION_TN);
1343 XNP_WSGL.AddURLParam('P_STATUS_DISPLAY_NAME', P_STATUS_DISPLAY_NAME);
1344 XNP_WSGL.AddURLParam('P_CUSTOMER_ID', P_CUSTOMER_ID);
1345 XNP_WSGL.AddURLParam('P_CUSTOMER_NAME', P_CUSTOMER_NAME);
1346 XNP_WSGL.AddURLParam('P_REC_CODE', P_REC_CODE);
1347 XNP_WSGL.AddURLParam('P_DON_CODE', P_DON_CODE);
1348 XNP_WSGL.AddURLParam('P_NRC_CODE', P_NRC_CODE);
1349 XNP_WSGL.AddURLParam('Z_START', Z_START);
1350 XNP_WSGL.AddURLParam('Z_ACTION', Z_ACTION);
1351 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
1352 if not Z_DIRECT_CALL then
1353 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
1354 return;
1355 end if;
1356 end if;
1357
1358 if (Z_ACTION = RL_QUERY_BUT_ACTION) or (Z_ACTION = RL_QUERY_BUT_CAPTION) then
1359 FormQuery(
1360 Z_DIRECT_CALL=>TRUE);
1361 return;
1362 end if;
1363
1364 XNP_WSGL.OpenPageHead('Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions');
1365 CreateListJavaScript;
1366 xnp_sv_orders$.TemplateHeader(TRUE,0);
1367 XNP_WSGL.ClosePageHead;
1368
1369 XNP_WSGL.OpenPageBody(FALSE, p_attributes=>RL_BODY_ATTRIBUTES);
1370
1371 if (Z_ACTION = RL_LAST_BUT_ACTION) or (Z_ACTION = RL_LAST_BUT_CAPTION) or
1372 (Z_ACTION = RL_COUNT_BUT_ACTION) or (Z_ACTION = RL_COUNT_BUT_CAPTION) or
1373 (RL_TOTAL_COUNT_REQD)
1374 then
1375
1376 I_COUNT := QueryHits(
1377 P_PORTING_ID,
1378 P_SUBSCRIPTION_TYPE,
1379 P_SUBSCRIPTION_TN,
1380 P_STATUS_DISPLAY_NAME,
1381 P_CUSTOMER_ID,
1382 P_CUSTOMER_NAME,
1383 P_REC_CODE,
1384 P_DON_CODE,
1385 P_NRC_CODE);
1386 if I_COUNT = -1 then
1387 XNP_WSGL.ClosePageBody;
1388 return;
1389 end if;
1390 end if;
1391
1392 if (Z_ACTION = RL_COUNT_BUT_ACTION) or (Z_ACTION = RL_COUNT_BUT_CAPTION) or
1393 RL_TOTAL_COUNT_REQD then
1394 I_OF_TOTAL_TEXT := ' '||XNP_WSGL.MsgGetText(111,XNP_WSGLM.DSP111_OF_TOTAL, to_char(I_COUNT));
1395 end if;
1396
1397 if Z_START IS NULL or (Z_ACTION = RL_FIRST_BUT_ACTION) or (Z_ACTION = RL_FIRST_BUT_CAPTION) then
1398 I_START := 1;
1399 elsif (Z_ACTION = RL_NEXT_BUT_ACTION) or (Z_ACTION = RL_NEXT_BUT_CAPTION) then
1400 I_START := I_START + RL_RECORD_SET_SIZE;
1401 elsif (Z_ACTION = RL_PREV_BUT_ACTION) or (Z_ACTION = RL_PREV_BUT_CAPTION) then
1402 I_START := I_START - RL_RECORD_SET_SIZE;
1403 elsif (Z_ACTION = RL_LAST_BUT_ACTION) or (Z_ACTION = RL_LAST_BUT_CAPTION) then
1404 I_START := 1 + (floor((I_COUNT-1)/RL_RECORD_SET_SIZE)*RL_RECORD_SET_SIZE);
1405 elsif Z_ACTION is null and I_START = 1 then
1406 null;
1407 elsif Z_ACTION IS NULL then
1408 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_WSGL.MsgGetText(215,XNP_WSGLM.MSG215_NO_MULTIPLE_SUBMITS),
1409 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', RL_BODY_ATTRIBUTES);
1410 XNP_WSGL.ClosePageBody;
1411 return;
1412 end if;
1413
1414 if I_START < 1 then
1415 I_START := 1;
1416 end if;
1417
1418 I_PREV_BUT := TRUE;
1419 I_NEXT_BUT := FALSE;
1420 if I_START = 1 or Z_ACTION IS NULL then
1421 I_PREV_BUT := FALSE;
1422 end if;
1423
1424 if nvl(Z_ACTION, 'X') <> 'DONTQUERY' then
1425
1426 if ZONE_SQL IS NULL then
1427 if not BuildSQL(
1428 P_PORTING_ID,
1429 P_SUBSCRIPTION_TYPE,
1430 P_SUBSCRIPTION_TN,
1431 P_STATUS_DISPLAY_NAME,
1432 P_CUSTOMER_ID,
1433 P_CUSTOMER_NAME,
1434 P_REC_CODE,
1435 P_DON_CODE,
1436 P_NRC_CODE) then
1437 XNP_WSGL.ClosePageBody;
1438 return;
1439 end if;
1440 end if;
1441
1442 if not PreQuery(
1443 P_PORTING_ID,
1444 P_SUBSCRIPTION_TYPE,
1445 P_SUBSCRIPTION_TN,
1446 P_STATUS_DISPLAY_NAME,
1447 P_CUSTOMER_ID,
1448 P_CUSTOMER_NAME,
1449 P_REC_CODE,
1450 P_DON_CODE,
1451 P_NRC_CODE) then
1452 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
1453 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', RL_BODY_ATTRIBUTES);
1454 return;
1455 end if;
1456
1457
1458
1459 I_CURSOR := dbms_sql.open_cursor;
1460 dbms_sql.parse(I_CURSOR, ZONE_SQL, dbms_sql.v7);
1461 dbms_sql.define_column(I_CURSOR, 1, CURR_VAL.PORTING_ID, 80);
1462 dbms_sql.define_column(I_CURSOR, 2, CURR_VAL.SUBSCRIPTION_TN, 20);
1463 dbms_sql.define_column(I_CURSOR, 3, CURR_VAL.STATUS_DISPLAY_NAME, 40);
1464 dbms_sql.define_column(I_CURSOR, 4, CURR_VAL.SV_SOA_ID);
1465
1466 I_VOID := dbms_sql.execute(I_CURSOR);
1467 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1468 else
1469 I_ROWS_FETCHED := 0;
1470 end if;
1471 I_TOTAL_ROWS := I_ROWS_FETCHED;
1472
1473 if I_ROWS_FETCHED <> 0 then
1474
1475 XNP_WSGL.LayoutOpen(XNP_WSGL.LAYOUT_TABLE, P_BORDER=>TRUE);
1476
1477 XNP_WSGL.LayoutRowStart;
1478 for i in 1..RL_NUMBER_OF_COLUMNS loop
1479 XNP_WSGL.LayoutHeader(10, 'LEFT', 'Porting ID');
1480 XNP_WSGL.LayoutHeader(20, 'LEFT', 'Telephone');
1481 XNP_WSGL.LayoutHeader(20, 'LEFT', 'Status');
1482 end loop;
1483 XNP_WSGL.LayoutRowEnd;
1484
1485 while I_ROWS_FETCHED <> 0 loop
1486
1487 if I_TOTAL_ROWS >= I_START then
1488 dbms_sql.column_value(I_CURSOR, 1, CURR_VAL.PORTING_ID);
1489 dbms_sql.column_value(I_CURSOR, 2, CURR_VAL.SUBSCRIPTION_TN);
1490 dbms_sql.column_value(I_CURSOR, 3, CURR_VAL.STATUS_DISPLAY_NAME);
1491 dbms_sql.column_value(I_CURSOR, 4, CURR_VAL.SV_SOA_ID);
1492 L_CHECKSUM := to_char(XNP_WSGL.Checksum(''||CURR_VAL.SV_SOA_ID));
1493
1494
1495 XNP_WSGL.LayoutRowStart('TOP');
1496 XNP_WSGL.LayoutData(htf.anchor2('xnp_sv_orders$soa_sv.queryview?P_SV_SOA_ID='||CURR_VAL.SV_SOA_ID||'&Z_CHK='||L_CHECKSUM, CURR_VAL.PORTING_ID, ctarget=>L_VF_FRAME));
1497 XNP_WSGL.LayoutData(CURR_VAL.SUBSCRIPTION_TN);
1498 XNP_WSGL.LayoutData(CURR_VAL.STATUS_DISPLAY_NAME);
1499 XNP_WSGL.LayoutRowEnd;
1500
1501 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1502 if I_TOTAL_ROWS = I_START + RL_RECORD_SET_SIZE - 1 then
1503 if I_ROWS_FETCHED <> 0 then
1504 I_NEXT_BUT := TRUE;
1505 end if;
1506 exit;
1507 end if;
1508 else
1509 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1510 end if;
1511
1512 I_TOTAL_ROWS := I_TOTAL_ROWS + I_ROWS_FETCHED;
1513
1514 end loop;
1515
1516 XNP_WSGL.LayoutClose;
1517
1518 if I_START = I_TOTAL_ROWS then
1519 htp.p(XNP_WSGL.MsgGetText(109,XNP_WSGLM.DSP109_RECORD, to_char(I_TOTAL_ROWS))||I_OF_TOTAL_TEXT);
1520 else
1521 htp.p(XNP_WSGL.MsgGetText(110,XNP_WSGLM.DSP110_RECORDS_N_M,
1522 to_char(I_START), to_char(I_TOTAL_ROWS))||
1523 I_OF_TOTAL_TEXT);
1524 end if;
1525 htp.para;
1526 else
1527 htp.p(XNP_WSGL.MsgGetText(112,XNP_WSGLM.DSP112_NO_RECORDS));
1528 end if;
1529
1530 if nvl(Z_ACTION, 'X') <> 'DONTQUERY' then
1531 dbms_sql.close_cursor(I_CURSOR);
1532 end if;
1533
1534 htp.formOpen(curl => 'xnp_sv_orders$soa_sv.querylist', cattributes => 'NAME="frmZero"');
1535 XNP_WSGL.HiddenField('P_PORTING_ID', P_PORTING_ID);
1536 XNP_WSGL.HiddenField('P_SUBSCRIPTION_TYPE', P_SUBSCRIPTION_TYPE);
1537 XNP_WSGL.HiddenField('P_SUBSCRIPTION_TN', P_SUBSCRIPTION_TN);
1538 XNP_WSGL.HiddenField('P_STATUS_DISPLAY_NAME', P_STATUS_DISPLAY_NAME);
1539 XNP_WSGL.HiddenField('P_CUSTOMER_ID', P_CUSTOMER_ID);
1540 XNP_WSGL.HiddenField('P_CUSTOMER_NAME', P_CUSTOMER_NAME);
1541 XNP_WSGL.HiddenField('P_REC_CODE', P_REC_CODE);
1542 XNP_WSGL.HiddenField('P_DON_CODE', P_DON_CODE);
1543 XNP_WSGL.HiddenField('P_NRC_CODE', P_NRC_CODE);
1544 XNP_WSGL.HiddenField('Z_START', to_char(I_START));
1545 htp.p ('<SCRIPT><!--');
1546 htp.p ('document.write (''<input type=hidden name="Z_ACTION">'')');
1547 htp.p ('//-->');
1548 htp.p ('</SCRIPT>');
1549
1550 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,
1551 'onClick="this.form.Z_ACTION.value=\''' || RL_FIRST_BUT_ACTION || '\''"');
1552 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,
1553 'onClick="this.form.Z_ACTION.value=\''' || RL_PREV_BUT_ACTION || '\''"');
1554 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,
1555 'onClick="this.form.Z_ACTION.value=\''' || RL_NEXT_BUT_ACTION || '\''"');
1556 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,
1557 'onClick="this.form.Z_ACTION.value=\''' || RL_LAST_BUT_ACTION || '\''"');
1558
1559 XNP_WSGL.RecordListButton(TRUE, 'Z_ACTION', htf.escape_sc(RL_REQUERY_BUT_CAPTION),p_dojs=>FALSE,
1560 buttonJS => 'onClick="this.form.Z_ACTION.value=\''' || RL_REQUERY_BUT_ACTION || '\''"');
1561 XNP_WSGL.HiddenField('Z_CHK',
1562 to_char(XNP_WSGL.Checksum('')));
1563 htp.formClose;
1564
1565 htp.formOpen(curl => 'xnp_sv_orders$soa_sv.querylist', ctarget=>'_top', cattributes => 'NAME="frmOne"');
1566 htp.p ('<SCRIPT><!--');
1567 htp.p ('document.write (''<input type=hidden name="Z_ACTION">'')');
1568 htp.p ('//-->');
1569 htp.p ('</SCRIPT>');
1570 htp.p ('<SCRIPT><!--');
1571 htp.p ('document.write (''' || htf.formSubmit('', htf.escape_sc(RL_QUERY_BUT_CAPTION), 'onClick="this.form.Z_ACTION.value=\''' || RL_QUERY_BUT_ACTION || '\''"') || ''')');
1572 htp.p ('//-->');
1573 htp.p ('</SCRIPT>');
1574
1575 if XNP_WSGL.IsSupported ('NOSCRIPT')
1576 then
1577
1578 htp.p ('<NOSCRIPT>');
1579 htp.formSubmit('Z_ACTION', htf.escape_sc(RL_QUERY_BUT_CAPTION));
1580 htp.p ('</NOSCRIPT>');
1581
1582 end if;
1583 XNP_WSGL.HiddenField('Z_CHK',
1584 to_char(XNP_WSGL.Checksum('')));
1585 htp.formClose;
1586
1587 XNP_WSGL.ClosePageBody;
1588
1589 exception
1590 when others then
1591 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1592 RL_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.QueryList');
1593 XNP_WSGL.ClosePageBody;
1594 end;
1595
1596 --------------------------------------------------------------------------------
1597 -- Name: xnp_sv_orders$soa_sv.QueryFirst
1598 --
1599 -- Description: Finds the first row which matches the given search criteria
1600 -- (if any), and calls QueryView for that row
1601 --
1602 -- Parameters:
1603 --
1604 --------------------------------------------------------------------------------
1605 procedure QueryFirst(
1606 P_PORTING_ID in varchar2,
1607 P_SUBSCRIPTION_TYPE in varchar2,
1608 P_SUBSCRIPTION_TN in varchar2,
1609 P_STATUS_DISPLAY_NAME in varchar2,
1610 P_CUSTOMER_ID in varchar2,
1611 P_CUSTOMER_NAME in varchar2,
1612 P_REC_CODE in varchar2,
1613 P_DON_CODE in varchar2,
1614 P_NRC_CODE in varchar2,
1615 Z_ACTION in varchar2,
1616 Z_DIRECT_CALL in boolean,
1617 Z_CHK in varchar2) is
1618
1619 I_CURSOR integer;
1620 I_VOID integer;
1621 I_ROWS_FETCHED integer := 0;
1622
1623 begin
1624
1625 XNP_WSGL.RegisterURL('xnp_sv_orders$soa_sv.queryfirst');
1626 XNP_WSGL.AddURLParam('P_PORTING_ID', P_PORTING_ID);
1627 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TYPE', P_SUBSCRIPTION_TYPE);
1628 XNP_WSGL.AddURLParam('P_SUBSCRIPTION_TN', P_SUBSCRIPTION_TN);
1629 XNP_WSGL.AddURLParam('P_STATUS_DISPLAY_NAME', P_STATUS_DISPLAY_NAME);
1630 XNP_WSGL.AddURLParam('P_CUSTOMER_ID', P_CUSTOMER_ID);
1631 XNP_WSGL.AddURLParam('P_CUSTOMER_NAME', P_CUSTOMER_NAME);
1632 XNP_WSGL.AddURLParam('P_REC_CODE', P_REC_CODE);
1633 XNP_WSGL.AddURLParam('P_DON_CODE', P_DON_CODE);
1634 XNP_WSGL.AddURLParam('P_NRC_CODE', P_NRC_CODE);
1635 XNP_WSGL.AddURLParam('Z_ACTION', Z_ACTION);
1636 XNP_WSGL.AddURLParam('Z_CHK', Z_CHK);
1637 if not Z_DIRECT_CALL then
1638 if not XNP_WSGL.ValidateChecksum('', Z_CHK) then
1639 return;
1640 end if;
1641 end if;
1642
1643 if Z_ACTION = 'BLANK' then
1644 XNP_WSGL.EmptyPage(VF_BODY_ATTRIBUTES);
1645 return;
1646 end if;
1647
1648
1649 if not BuildSQL(
1650 P_PORTING_ID,
1651 P_SUBSCRIPTION_TYPE,
1652 P_SUBSCRIPTION_TN,
1653 P_STATUS_DISPLAY_NAME,
1654 P_CUSTOMER_ID,
1655 P_CUSTOMER_NAME,
1656 P_REC_CODE,
1657 P_DON_CODE,
1658 P_NRC_CODE) then
1659 return;
1660 end if;
1661
1662 if not PreQuery(
1663 P_PORTING_ID,
1664 P_SUBSCRIPTION_TYPE,
1665 P_SUBSCRIPTION_TN,
1666 P_STATUS_DISPLAY_NAME,
1667 P_CUSTOMER_ID,
1668 P_CUSTOMER_NAME,
1669 P_REC_CODE,
1670 P_DON_CODE,
1671 P_NRC_CODE) then
1672 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_ERROR, XNP_cg$errors.GetErrors,
1673 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions', VF_BODY_ATTRIBUTES);
1674 return;
1675 end if;
1676
1677 I_CURSOR := dbms_sql.open_cursor;
1678 dbms_sql.parse(I_CURSOR, ZONE_SQL, dbms_sql.v7);
1679 dbms_sql.define_column(I_CURSOR, 1, CURR_VAL.PORTING_ID, 80);
1680 dbms_sql.define_column(I_CURSOR, 2, CURR_VAL.SUBSCRIPTION_TN, 20);
1681 dbms_sql.define_column(I_CURSOR, 3, CURR_VAL.STATUS_DISPLAY_NAME, 40);
1682 dbms_sql.define_column(I_CURSOR, 4, CURR_VAL.SV_SOA_ID);
1683
1684 I_VOID := dbms_sql.execute(I_CURSOR);
1685
1686 I_ROWS_FETCHED := dbms_sql.fetch_rows(I_CURSOR);
1687
1688 if I_ROWS_FETCHED = 0 then
1689 XNP_WSGL.EmptyPage(VF_BODY_ATTRIBUTES);
1690 else
1691 dbms_sql.column_value(I_CURSOR, 1, CURR_VAL.PORTING_ID);
1692 dbms_sql.column_value(I_CURSOR, 2, CURR_VAL.SUBSCRIPTION_TN);
1693 dbms_sql.column_value(I_CURSOR, 3, CURR_VAL.STATUS_DISPLAY_NAME);
1694 dbms_sql.column_value(I_CURSOR, 4, CURR_VAL.SV_SOA_ID);
1695 xnp_sv_orders$soa_sv.QueryView(Z_DIRECT_CALL=>TRUE);
1696 end if;
1697
1698 dbms_sql.close_cursor(I_CURSOR);
1699
1700 exception
1701 when others then
1702 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1703 VF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.QueryFirst');
1704 XNP_WSGL.ClosePageBody;
1705 end;
1706
1707 --------------------------------------------------------------------------------
1708 -- Name: xnp_sv_orders$soa_sv.PreQuery
1709 --
1710 -- Description: Provides place holder for code to be run prior to a query
1711 -- for the 'SOA_SV' module component (Porting Ordering Subscriptions).
1712 --
1713 -- Parameters: None
1714 --
1715 -- Returns: True If success
1716 -- False Otherwise
1717 --
1718 --------------------------------------------------------------------------------
1719 function PreQuery(
1720 P_PORTING_ID in varchar2,
1721 P_SUBSCRIPTION_TYPE in varchar2,
1722 P_SUBSCRIPTION_TN in varchar2,
1723 P_STATUS_DISPLAY_NAME in varchar2,
1724 P_CUSTOMER_ID in varchar2,
1725 P_CUSTOMER_NAME in varchar2,
1726 P_REC_CODE in varchar2,
1727 P_DON_CODE in varchar2,
1728 P_NRC_CODE in varchar2) return boolean is
1729 L_RET_VAL boolean := TRUE;
1730 begin
1731 return L_RET_VAL;
1732 exception
1733 when others then
1734 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1735 DEF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.PreQuery');
1736 return FALSE;
1737 end;
1738
1739 --------------------------------------------------------------------------------
1740 -- Name: xnp_sv_orders$soa_sv.PostQuery
1741 --
1742 -- Description: Provides place holder for code to be run after a query
1743 -- for the 'SOA_SV' module component (Porting Ordering Subscriptions).
1744 --
1745 -- Parameters: Z_POST_DML Flag indicating if Query after insert or update
1746 --
1747 -- Returns: True If success
1748 -- False Otherwise
1749 --
1750 --------------------------------------------------------------------------------
1751 function PostQuery(Z_POST_DML in boolean) return boolean is
1752 L_RET_VAL boolean := TRUE;
1753 begin
1754 return L_RET_VAL;
1755 exception
1756 when others then
1757 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1758 DEF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.PostQuery');
1759 return FALSE;
1760 end;
1761
1762 --------------------------------------------------------------------------------
1763 -- Name: xnp_sv_orders$soa_sv.CreateQueryJavaScript
1764 --
1765 -- Description:
1766 --
1767 -- Parameters:
1768 --
1769 --------------------------------------------------------------------------------
1770 procedure CreateQueryJavaScript is
1771 begin
1772 htp.p(XNP_WSGJSL.OpenScript);
1773 htp.p('var FormType = "Query";');
1774 htp.p(XNP_WSGJSL.CloseScript);
1775 exception
1776 when others then
1777 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1778 QF_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.CreateQueryJavaScript');
1779 end;
1780
1781 --------------------------------------------------------------------------------
1782 -- Name: xnp_sv_orders$soa_sv.CreateListJavaScript
1783 --
1784 -- Description:
1785 --
1786 -- Parameters:
1787 --
1788 --------------------------------------------------------------------------------
1789 procedure CreateListJavaScript is
1790 begin
1791 htp.p(XNP_WSGJSL.OpenScript);
1792 htp.p('var FormType = "List";');
1793 htp.p(XNP_WSGJSL.CloseScript);
1794 exception
1795 when others then
1796 XNP_WSGL.DisplayMessage(XNP_WSGL.MESS_EXCEPTION, SQLERRM, 'Monitor Ordering Subscriptions'||' : '||'Porting Ordering Subscriptions',
1797 RL_BODY_ATTRIBUTES, 'xnp_sv_orders$soa_sv.CreateListJavaScript');
1798 end;
1799 end;