1 PACKAGE XNP_CORE AUTHID CURRENT_USER AS
2 /* $Header: XNPCORES.pls 120.2 2006/02/13 07:43:05 dputhiye ship $ */
3
4 -- Declare global_variables
5
6 g_ENABLE_NRC_FLAG CHAR(1);
7 g_DEFAULT_PORTING_STATUS VARCHAR2(20);
8
9 -- Gets PHASE corresponding to the given status
10 -- The Status to Phase mapping is done at
11 -- configuration time. This procedure looks up the
12 -- Phase conrresponding to status
13 --
14 -- Tables: XNP_SV_STATUS_TYPES_B
15 --
16 PROCEDURE GET_PHASE_FOR_STATUS
17 (p_CUR_STATUS_TYPE_CODE VARCHAR2
18 ,x_PHASE_INDICATOR OUT NOCOPY VARCHAR2
19 ,x_ERROR_CODE OUT NOCOPY NUMBER
20 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
21 );
22
23 -- Gets the Subscription version ID
24 -- corresponding to the given TN and phase.
25 --
26 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
27 --
28 -- TABLES: XNP_SV_SOA
29
30 PROCEDURE GET_SOA_SV_ID
31 (p_PHASE_INDICATOR VARCHAR2
32 ,p_SUBSCRIPTION_TN VARCHAR2
33 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
34 ,x_SV_ID OUT NOCOPY NUMBER
35 ,x_ERROR_CODE OUT NOCOPY NUMBER
36 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
37 );
38
39 -- Gets the number range ID for the given TN range.
40 -- It is ensured that the number range is currently
41 -- Active and effective as of current date
42 --
43 -- TABLES: XNP_NUMBER_RANGES
44
45 PROCEDURE GET_NUMBER_RANGE_ID
46 (p_STARTING_NUMBER VARCHAR2
47 ,p_ENDING_NUMBER VARCHAR2
48 ,x_NUMBER_RANGE_ID OUT NOCOPY NUMBER
49 ,x_ERROR_CODE OUT NOCOPY NUMBER
50 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
51 );
52
53 -- Gets the Subscription Version ID for subscription TN
54 -- from the table maintaining the ported numbers
55 -- The entries in this table are created before
56 -- provisioning a number or a number range using
57 -- XNP_CORE.CREATE_PORTED_NUMBER
58 --
59 -- TABLES: XNP_SV_SMS
60
61 PROCEDURE GET_SMS_SV_ID
62 (p_SUBSCRIPTION_TN VARCHAR2
63 ,x_SV_ID OUT NOCOPY NUMBER
64 ,x_ERROR_CODE OUT NOCOPY NUMBER
65 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
66 );
67
68 -- Creates record(s) of the ported numbers.
69 -- Called During the Provisioning phase of the order
70 -- when requested by the NRC
71 -- Inserts a record in the table XNP_SV_SMS to record
72 -- all the numbers to be provisioned. A record is created
73 -- for each number in the range.
74 --
75 -- TABLES: XNP_SV_SMS
76 --
77 PROCEDURE SMS_CREATE_PORTED_NUMBER
78 (p_PORTING_ID IN VARCHAR2
79 ,p_STARTING_NUMBER IN NUMBER
80 ,p_ENDING_NUMBER IN NUMBER
81 ,p_SUBSCRIPTION_TYPE IN VARCHAR2
82 ,p_ROUTING_NUMBER_ID IN NUMBER
83 ,p_PORTING_TIME IN DATE
84 ,p_CNAM_ADDRESS VARCHAR2
85 ,p_CNAM_SUBSYSTEM VARCHAR2
86 ,p_ISVM_ADDRESS VARCHAR2
87 ,p_ISVM_SUBSYSTEM VARCHAR2
88 ,p_LIDB_ADDRESS VARCHAR2
89 ,p_LIDB_SUBSYSTEM VARCHAR2
90 ,p_CLASS_ADDRESS VARCHAR2
91 ,p_CLASS_SUBSYSTEM VARCHAR2
92 ,p_WSMSC_ADDRESS VARCHAR2
93 ,p_WSMSC_SUBSYSTEM VARCHAR2
94 ,p_RN_ADDRESS VARCHAR2
95 ,p_RN_SUBSYSTEM VARCHAR2
96 ,p_ORDER_ID IN NUMBER
97 ,p_LINEITEM_ID IN NUMBER
98 ,p_WORKITEM_INSTANCE_ID IN NUMBER
99 ,p_FA_INSTANCE_ID IN NUMBER
100 ,x_ERROR_CODE OUT NOCOPY NUMBER
101 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
102 );
103
104 --
105 -- Updates Record(s) in Porting orders table
106 -- for each TN in the range with the old SPCUToff date
107 -- The SVs to update are got by getting the TN
108 -- records in the phase mapping to the current status
109 --
110 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
111 --
112 -- TABLES: XNP_SV_SOA, XNP_SV_STATUS_TYPES_B, XNP_SV_SOA_JN
113 --
114 PROCEDURE SOA_UPDATE_CUTOFF_DATE
115 (p_STARTING_NUMBER VARCHAR2
116 ,p_ENDING_NUMBER VARCHAR2
117 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
118 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
119 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
120 ,p_ORDER_ID IN NUMBER
121 ,p_LINEITEM_ID IN NUMBER
122 ,p_WORKITEM_INSTANCE_ID IN NUMBER
123 ,p_FA_INSTANCE_ID IN NUMBER
124 ,x_ERROR_CODE OUT NOCOPY NUMBER
125 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
126 );
127
128 -- Updates the OLD SP due date
129 -- in table containing porting orders for the given TN range and given phase.
130 --
131 -- The SVs to update are got by getting the TN
132 -- records in the phase mapping to the current status
133 --
134 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
135 --
136 -- TABLES: XNP_SV_SOA, XNP_SV_STATUS_TYPES_B, XNP_SV_SOA_JN
137
138 PROCEDURE SOA_UPDATE_OLD_SP_DUE_DATE
139 (p_STARTING_NUMBER VARCHAR2
140 ,p_ENDING_NUMBER VARCHAR2
141 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
142 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
143 ,p_OLD_SP_DUE_DATE DATE
144 ,p_ORDER_ID IN NUMBER
145 ,p_LINEITEM_ID IN NUMBER
146 ,p_WORKITEM_INSTANCE_ID IN NUMBER
147 ,p_FA_INSTANCE_ID IN NUMBER
148 ,x_ERROR_CODE OUT NOCOPY NUMBER
149 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
150 );
151
152 -- Update the Status and Status Change
153 -- cause for one or more Porting Records
154 -- Description: Procedure to update the status of
155 -- the Porting Order Records to the new status
156 -- for the TN range, with the current status.
157 --
158 -- The SVs to update are found by getting the TN
159 -- records in the phase mapping to the current status
160 --
161 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
162 --
163 -- TABLES: XNP_SV_SOA, XNP_SV_STATUS_TYPES_B, XNP_SV_SOA_JN
164
165 PROCEDURE SOA_UPDATE_SV_STATUS
166 (p_STARTING_NUMBER VARCHAR2
167 ,p_ENDING_NUMBER VARCHAR2
168 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
169 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
170 ,p_NEW_STATUS_TYPE_CODE VARCHAR2
171 ,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
172 ,p_ORDER_ID IN NUMBER
173 ,p_LINEITEM_ID IN NUMBER
174 ,p_WORKITEM_INSTANCE_ID IN NUMBER
175 ,p_FA_INSTANCE_ID IN NUMBER
176 ,x_ERROR_CODE OUT NOCOPY NUMBER
177 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
178 );
179
180 -- Update the status of the Porting Order Records to the new status
181 -- for the given PORTING_ID (a.k.a OBJECT_REFERENCE).
182 --
183 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
184 --
185 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
186 --
187 PROCEDURE SOA_UPDATE_SV_STATUS
188 (p_PORTING_ID VARCHAR2
189 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
190 ,p_NEW_STATUS_TYPE_CODE VARCHAR2
191 ,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
192 ,p_ORDER_ID IN NUMBER
193 ,p_LINEITEM_ID IN NUMBER
194 ,p_WORKITEM_INSTANCE_ID IN NUMBER
195 ,p_FA_INSTANCE_ID IN NUMBER
196 ,x_ERROR_CODE OUT NOCOPY NUMBER
197 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
198 );
199
200 -- Insert rows in the table XNP_SV_SOA to record porting order
201 -- for each TN on the range.
202 -- The porting information, customer information and network
203 -- information including the routing number is inserted
204 --
205 -- Default: OLD_SP_DUE_DATE is set same as NEW_SP_DUE_DATE
206 --
207 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
208
209 PROCEDURE SOA_CREATE_REC_PORT_ORDER
210 (p_PORTING_ID VARCHAR2
211 ,p_STARTING_NUMBER NUMBER
212 ,p_ENDING_NUMBER NUMBER
213 ,p_SUBSCRIPTION_TYPE VARCHAR2
214 ,p_DONOR_SP_ID NUMBER
215 ,p_RECIPIENT_SP_ID NUMBER
216 ,p_ROUTING_NUMBER VARCHAR2
217 ,p_NEW_SP_DUE_DATE DATE
218 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
219 ,p_CUSTOMER_ID VARCHAR2
220 ,p_CUSTOMER_NAME VARCHAR2
221 ,p_CUSTOMER_TYPE VARCHAR2
222 ,p_ADDRESS_LINE1 VARCHAR2
223 ,p_ADDRESS_LINE2 VARCHAR2
224 ,p_CITY VARCHAR2
225 ,p_PHONE VARCHAR2
226 ,p_FAX VARCHAR2
227 ,p_EMAIL VARCHAR2
228 ,p_PAGER VARCHAR2
229 ,p_PAGER_PIN VARCHAR2
230 ,p_INTERNET_ADDRESS VARCHAR2
231 ,p_ZIP_CODE VARCHAR2
232 ,p_COUNTRY VARCHAR2
233 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
234 ,p_CONTACT_NAME VARCHAR2
235 ,p_RETAIN_TN_FLAG VARCHAR2
236 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
237 ,p_CNAM_ADDRESS VARCHAR2
238 ,p_CNAM_SUBSYSTEM VARCHAR2
239 ,p_ISVM_ADDRESS VARCHAR2
240 ,p_ISVM_SUBSYSTEM VARCHAR2
241 ,p_LIDB_ADDRESS VARCHAR2
242 ,p_LIDB_SUBSYSTEM VARCHAR2
243 ,p_CLASS_ADDRESS VARCHAR2
244 ,p_CLASS_SUBSYSTEM VARCHAR2
245 ,p_WSMSC_ADDRESS VARCHAR2
246 ,p_WSMSC_SUBSYSTEM VARCHAR2
247 ,p_RN_ADDRESS VARCHAR2
248 ,p_RN_SUBSYSTEM VARCHAR2
249 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
250 ,p_ACTIVATION_DUE_DATE DATE
251 ,p_ORDER_PRIORITY VARCHAR2
252 ,p_SUBSEQUENT_PORT_FLAG VARCHAR2
253 ,p_COMMENTS VARCHAR2
254 ,p_NOTES VARCHAR2
255 ,p_ORDER_ID IN NUMBER
256 ,p_LINEITEM_ID IN NUMBER
257 ,p_WORKITEM_INSTANCE_ID IN NUMBER
258 ,p_FA_INSTANCE_ID IN NUMBER
259 ,x_ERROR_CODE OUT NOCOPY NUMBER
260 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
261 );
262
263 -- Inserts row in the table XNP_SV_SOA to record porting order
264 -- for each TN on the range.
265 -- The porting information, customer information and network
266 -- information without the routing number is inserted
267 --
268 -- Default: OLD_SP_DUE_DATE is set same as NEW_SP_DUE_DATE
269 --
270 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
271 PROCEDURE SOA_CREATE_DON_PORT_ORDER
272 (p_PORTING_ID VARCHAR2
273 ,p_STARTING_NUMBER NUMBER
274 ,p_ENDING_NUMBER NUMBER
275 ,p_SUBSCRIPTION_TYPE VARCHAR2
276 ,p_DONOR_SP_ID NUMBER
277 ,p_RECIPIENT_SP_ID NUMBER
278 ,p_ROUTING_NUMBER VARCHAR2
279 ,p_NEW_SP_DUE_DATE DATE
280 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
281 ,p_CUSTOMER_ID VARCHAR2
282 ,p_CUSTOMER_NAME VARCHAR2
283 ,p_CUSTOMER_TYPE VARCHAR2
284 ,p_ADDRESS_LINE1 VARCHAR2
285 ,p_ADDRESS_LINE2 VARCHAR2
286 ,p_CITY VARCHAR2
287 ,p_PHONE VARCHAR2
288 ,p_FAX VARCHAR2
289 ,p_EMAIL VARCHAR2
290 ,p_PAGER VARCHAR2
291 ,p_PAGER_PIN VARCHAR2
292 ,p_INTERNET_ADDRESS VARCHAR2
293 ,p_ZIP_CODE VARCHAR2
294 ,p_COUNTRY VARCHAR2
295 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
296 ,p_CONTACT_NAME VARCHAR2
297 ,p_RETAIN_TN_FLAG VARCHAR2
298 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
299 ,p_CNAM_ADDRESS VARCHAR2
300 ,p_CNAM_SUBSYSTEM VARCHAR2
301 ,p_ISVM_ADDRESS VARCHAR2
302 ,p_ISVM_SUBSYSTEM VARCHAR2
303 ,p_LIDB_ADDRESS VARCHAR2
304 ,p_LIDB_SUBSYSTEM VARCHAR2
305 ,p_CLASS_ADDRESS VARCHAR2
306 ,p_CLASS_SUBSYSTEM VARCHAR2
307 ,p_WSMSC_ADDRESS VARCHAR2
308 ,p_WSMSC_SUBSYSTEM VARCHAR2
309 ,p_RN_ADDRESS VARCHAR2
310 ,p_RN_SUBSYSTEM VARCHAR2
311 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
312 ,p_ACTIVATION_DUE_DATE DATE
313 ,p_ORDER_PRIORITY VARCHAR2
314 ,p_SUBSEQUENT_PORT_FLAG VARCHAR2
315 ,p_COMMENTS VARCHAR2
316 ,p_NOTES VARCHAR2
317 ,p_ORDER_ID IN NUMBER
318 ,p_LINEITEM_ID IN NUMBER
319 ,p_WORKITEM_INSTANCE_ID IN NUMBER
320 ,p_FA_INSTANCE_ID IN NUMBER
321 ,x_ERROR_CODE OUT NOCOPY NUMBER
322 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
323 );
324
325 -- Inserts row in the table XNP_SV_SOA to record porting order
326 -- for each TN on the range.
327 -- The porting information, customer information and network
328 -- information with the routing number is inserted
329 --
330 -- Default: OLD_SP_DUE_DATE is set same as NEW_SP_DUE_DATE
331 --
332 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
333
334 PROCEDURE SOA_CREATE_NRC_PORT_ORDER
335 (p_PORTING_ID VARCHAR2
336 ,p_STARTING_NUMBER NUMBER
337 ,p_ENDING_NUMBER NUMBER
338 ,p_SUBSCRIPTION_TYPE VARCHAR2
339 ,p_DONOR_SP_ID NUMBER
340 ,p_RECIPIENT_SP_ID NUMBER
341 ,p_ROUTING_NUMBER VARCHAR2
342 ,p_NEW_SP_DUE_DATE DATE
343 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
344 ,p_CUSTOMER_ID VARCHAR2
345 ,p_CUSTOMER_NAME VARCHAR2
346 ,p_CUSTOMER_TYPE VARCHAR2
347 ,p_ADDRESS_LINE1 VARCHAR2
348 ,p_ADDRESS_LINE2 VARCHAR2
349 ,p_CITY VARCHAR2
350 ,p_PHONE VARCHAR2
351 ,p_FAX VARCHAR2
352 ,p_EMAIL VARCHAR2
353 ,p_PAGER VARCHAR2
354 ,p_PAGER_PIN VARCHAR2
355 ,p_INTERNET_ADDRESS VARCHAR2
356 ,p_ZIP_CODE VARCHAR2
357 ,p_COUNTRY VARCHAR2
358 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
359 ,p_CONTACT_NAME VARCHAR2
360 ,p_RETAIN_TN_FLAG VARCHAR2
361 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
362 ,p_CNAM_ADDRESS VARCHAR2
363 ,p_CNAM_SUBSYSTEM VARCHAR2
364 ,p_ISVM_ADDRESS VARCHAR2
365 ,p_ISVM_SUBSYSTEM VARCHAR2
366 ,p_LIDB_ADDRESS VARCHAR2
367 ,p_LIDB_SUBSYSTEM VARCHAR2
368 ,p_CLASS_ADDRESS VARCHAR2
369 ,p_CLASS_SUBSYSTEM VARCHAR2
370 ,p_WSMSC_ADDRESS VARCHAR2
371 ,p_WSMSC_SUBSYSTEM VARCHAR2
375 ,p_ACTIVATION_DUE_DATE DATE
372 ,p_RN_ADDRESS VARCHAR2
373 ,p_RN_SUBSYSTEM VARCHAR2
374 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
376 ,p_ORDER_PRIORITY VARCHAR2
377 ,p_SUBSEQUENT_PORT_FLAG VARCHAR2
378 ,p_COMMENTS VARCHAR2
379 ,p_NOTES VARCHAR2
380 ,p_ORDER_ID IN NUMBER
381 ,p_LINEITEM_ID IN NUMBER
382 ,p_WORKITEM_INSTANCE_ID IN NUMBER
383 ,p_FA_INSTANCE_ID IN NUMBER
384 ,p_VALIDATION_FLAG IN VARCHAR2
385 ,x_ERROR_CODE OUT NOCOPY NUMBER
386 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
387 );
388
389 -- Donor's XNP_STANDARD.SOA_CHECK_NOTIFY_DIR_SVS
390 --
391 -- Checks with the table XNP_SV_SOA for notification to the directory SVs
392 -- for the given porting ID (object reference)
393 --
394 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
395 --
396 -- TABLES: XNP_SV_SOA
397 -- @return 'Y' if need to notify
398
399 PROCEDURE SOA_CHECK_NOTIFY_DIR_SVS
400 (p_PORTING_ID VARCHAR2
401 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
402 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
403 ,x_ERROR_CODE OUT NOCOPY NUMBER
404 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
405 );
406
407 -- Checks if the given donor SP ID is the
408 -- assigned SP for the given number range
409 --
410 -- TABLES: XNP_NUMBER_RANGES
411 --
412 -- @return 'Y' if TRUE
413
414 PROCEDURE SOA_CHECK_IF_INITIAL_DONOR
415 (p_DONOR_SP_ID NUMBER
416 ,p_STARTING_NUMBER VARCHAR2
417 ,p_ENDING_NUMBER VARCHAR2
418 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
419 ,x_ERROR_CODE OUT NOCOPY NUMBER
420 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
421 );
422
423 -- Updates the Charging information for the port
424 -- for the given range and records in the given status
425 --
426 -- The SVs to update are got by getting the TN
427 -- records in the phase mapping to the current status
428 --
429 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
430 --
431 -- TABLES: XNP_SV_SOA
432
433 PROCEDURE SOA_UPDATE_CHARGING_INFO
434 (p_STARTING_NUMBER VARCHAR2
435 ,p_ENDING_NUMBER VARCHAR2
436 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
437 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
438 ,p_INVOICE_DUE_DATE DATE
439 ,p_CHARGING_INFO VARCHAR2
440 ,p_BILLING_ID NUMBER
441 ,p_USER_LOCTN_VALUE VARCHAR2
442 ,p_USER_LOCTN_TYPE VARCHAR2
443 ,p_ORDER_ID IN NUMBER
444 ,p_LINEITEM_ID IN NUMBER
445 ,p_WORKITEM_INSTANCE_ID IN NUMBER
446 ,p_FA_INSTANCE_ID IN NUMBER
447 ,x_ERROR_CODE OUT NOCOPY NUMBER
448 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
449 );
450
451 -- Creates a mapping row for the SMS sv ID and FE ID
452 -- for the feature type. Usually called by the callback procedure
453 -- Called by : package XNP_FA_CB.PROVISION_FE
454 --
455 -- TABLES: XNP_SV_SMS_FE_MAPS. Accesses XNP_SV_SMS
456
457 PROCEDURE SMS_INSERT_FE_MAP
458 (p_STARTING_NUMBER NUMBER
459 ,p_ENDING_NUMBER NUMBER
460 ,p_FE_ID NUMBER
461 ,p_FEATURE_TYPE VARCHAR2
462 ,p_ORDER_ID IN NUMBER
463 ,p_LINEITEM_ID IN NUMBER
464 ,p_WORKITEM_INSTANCE_ID IN NUMBER
465 ,p_FA_INSTANCE_ID IN NUMBER
466 ,x_ERROR_CODE OUT NOCOPY NUMBER
467 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
468 );
469
470 -- Checks if there exists a Porting record in the given status
471 -- for this TN range.
472 --
473 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
474 --
475 -- TABLES: XNP_SV_SOA
476
477 PROCEDURE CHECK_SOA_STATUS_EXISTS
478 (p_STARTING_NUMBER VARCHAR2
479 ,p_ENDING_NUMBER VARCHAR2
480 ,p_STATUS_TYPE_CODE VARCHAR2
481 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
482 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
483 ,x_ERROR_CODE OUT NOCOPY NUMBER
484 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
485 );
486
487 -- Deletes the FE maps for the given
488 -- for the given feature type, number range and FE ID
489 --
490 -- Usage Notes: This procedure is invoked
491 -- by a callback procedure when a FA_DONE is received
492 -- after a de-provisioning is done.
493 -- The FA_DONE message is a message sent by the SFM
494 -- after the completion of a provisioning activity
495 --
496 -- TABLES: XNP_SV_SMS_FE_MAP, references XNP_SV_SMS
497
498 PROCEDURE SMS_DELETE_FE_MAP
499 (p_STARTING_NUMBER VARCHAR2
500 ,p_ENDING_NUMBER VARCHAR2
501 ,p_FE_ID NUMBER
502 ,p_FEATURE_TYPE VARCHAR2
503 ,x_ERROR_CODE OUT NOCOPY NUMBER
504 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
505 );
506
507 -- Checks if there exists a SV for this TN range
508 -- in the given phase with the (local) SP performing
509 -- the donor role
510 --
511 -- Usage Notes: This procedure is used to check if
512 -- there already exists a porting going on for the
513 -- given TN range with the current SP performing as donor
514 --
518 -- Next, use XNP_CORE.GET_SP_ID given the SP code.
515 -- The SP ID can be got in two steps.
516 -- First, get the SP code from the WI parameter
517 -- SP_NAME or RECIPIENT_NAME, whichever is set.
519 --
520 -- @return 'Y' or 'N'
521 --
522 -- TABLES: XNP_SV_SOA, XNP_SV_STATUS_TYPES_B
523
524 PROCEDURE CHECK_DONOR_PHASE
525 (p_STARTING_NUMBER IN VARCHAR2
526 ,p_ENDING_NUMBER IN VARCHAR2
527 ,p_SP_ID IN NUMBER
528 ,p_PHASE_INDICATOR IN VARCHAR2
529 ,x_CHECK_EXISTS OUT NOCOPY VARCHAR2
530 ,x_ERROR_CODE OUT NOCOPY NUMBER
531 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
532 );
533
534 -- Checks if there exists a SV for this TN range
535 -- in the given phase with the (local) SP performing
536 -- the recipient role
537 --
538 -- Usage Notes: This procedure is used to check if
539 -- there already exists a porting going on for the
540 -- given TN range with the current SP preforming as recipient
541 --
542 -- The SP ID can be got in two steps.
543 -- First, get the SP code from the WI parameter
544 -- SP_NAME or RECIPIENT_NAME, whichever is set
545 -- Next, use XNP_CORE.GET_SP_ID given the SP code
546 --
547 -- @return 'Y' or 'N'
548 --
549 -- TABLES: XNP_SV_SOA, XNP_SV_STATUS_TYPES_B
550
551 PROCEDURE CHECK_RECIPIENT_PHASE
552 (p_STARTING_NUMBER IN VARCHAR2
553 ,p_ENDING_NUMBER IN VARCHAR2
554 ,p_SP_ID IN NUMBER
555 ,p_PHASE_INDICATOR IN VARCHAR2
556 ,x_CHECK_EXISTS OUT NOCOPY VARCHAR2
557 ,x_ERROR_CODE OUT NOCOPY NUMBER
558 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
559 );
560
561 -- Resets porting records in current phase except the one in omit status
562 -- to the phase mentioned in p_reset_phase_indicator.
563 -- The records should also be in the given range
564 -- and created by the 'local SP ID'
565 --
566 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
567 --
568 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
569
570 PROCEDURE SOA_RESET_SV_STATUS
571 (p_STARTING_NUMBER VARCHAR2
572 ,p_ENDING_NUMBER VARCHAR2
573 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
574 ,p_CUR_PHASE_INDICATOR VARCHAR2
575 ,p_RESET_PHASE_INDICATOR VARCHAR2
576 ,p_OMIT_STATUS VARCHAR2
577 ,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
578 ,p_ORDER_ID IN NUMBER
579 ,p_LINEITEM_ID IN NUMBER
580 ,p_WORKITEM_INSTANCE_ID IN NUMBER
581 ,p_FA_INSTANCE_ID IN NUMBER
582 ,x_ERROR_CODE OUT NOCOPY NUMBER
583 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
584 );
585
586 -- Deletes the number which was provisioned
587 -- provisioned numbers for all SVs belonging to the
588 -- given number range.
589 -- The corresponding records from order mappings table
590 -- are also deleted.
591 --
592 -- Usage Notes: Called during the deprovisioning
593 -- of a TN range by the callback procedure for a
594 -- Deprovision FA_DONE
595 -- The FA_DONE message is a message sent by the SFM
596 -- after the completion of a provisioning activity
597 --
598 -- TABLES: XNP_SV_SMS, XNP_SV_ORDER_MAPPINGS
599
600 PROCEDURE SMS_DELETE_PORTED_NUMBER
601 (p_STARTING_NUMBER IN VARCHAR2
602 ,p_ENDING_NUMBER IN VARCHAR2
603 ,x_ERROR_CODE OUT NOCOPY NUMBER
604 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
605 );
606
607 -- Updates the status of the provisioning FE map
608 -- with the given status for the given FE
609 --
610 -- Usage Notes: Called During the provisioning
611 -- of a TN range by the callback procedure for
612 -- FA_DONE with a provisioning response of ERROR, SUCCESS or ABORTED
613 --
614 -- The FA_DONE message is a message sent by the SFM
615 -- after the completion of a provisioning activity
616 --
617 -- TABLES: XNP_SV_SMS_FE_MAPS, XNP_SV_SMS
618
619 PROCEDURE SMS_UPDATE_FE_MAP_STATUS
620 (p_STARTING_NUMBER VARCHAR2
621 ,p_ENDING_NUMBER VARCHAR2
622 ,p_FE_ID NUMBER
623 ,p_FEATURE_TYPE VARCHAR2
624 ,p_PROV_STATUS VARCHAR2
625 ,p_ORDER_ID IN NUMBER
626 ,p_LINEITEM_ID IN NUMBER
627 ,p_WORKITEM_INSTANCE_ID IN NUMBER
628 ,p_FA_INSTANCE_ID IN NUMBER
629 ,x_ERROR_CODE OUT NOCOPY NUMBER
630 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
631 );
632
633 -- Gets the NRC SP_ID for the given number range
634 -- It first recursively gets the root geo area ID for this
635 -- number range and checks the SPs covering it from xnp_service_areas
636 -- and gets ID of the NRC SP amoung them.
637 --
638 -- TABLES: XNP_SERVICE_PROVIDERS, XNP_SERVICE_AREAS,
639 -- XNP_GEO_HIERARCHY, XNP_NUMBER_RANGES
640
641 PROCEDURE GET_NRC_ID
642 (p_STARTING_NUMBER IN VARCHAR2
643 ,p_ENDING_NUMBER IN VARCHAR2
644 ,x_NRC_ID OUT NOCOPY NUMBER
645 ,x_ERROR_CODE OUT NOCOPY NUMBER
646 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
647 );
648
649 -- Gets the routing number ID for the given number ID if active
650 --
651 -- TABLE: XNP_ROUTING_NUMBERS
652
653 PROCEDURE GET_ROUTING_NUMBER_ID
654 (p_ROUTING_NUMBER IN VARCHAR2
655 ,x_ROUTING_NUMBER_ID OUT NOCOPY NUMBER
656 ,x_ERROR_CODE OUT NOCOPY NUMBER
660 -- Return the SP ID for the given SP code if SP is active
657 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
658 );
659
661 --
662 -- TABLE: XNP_SERVICE_PROVIDERS
663
664 PROCEDURE GET_SP_ID
665 (p_SP_NAME IN VARCHAR2
666 ,x_SP_ID OUT NOCOPY NUMBER
667 ,x_ERROR_CODE OUT NOCOPY NUMBER
668 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
669 );
670
671 -- Returns the Assigned SP ID
672 -- corresponding to the given number range if the status is active
673 --
674 -- TABLE: XNP_NUMBER_RANGES
675
676 PROCEDURE GET_ASSIGNED_SP_ID
677 (p_STARTING_NUMBER IN VARCHAR2
678 ,p_ENDING_NUMBER IN VARCHAR2
679 ,x_ASSIGNED_SP_ID OUT NOCOPY NUMBER
680 ,x_ERROR_CODE OUT NOCOPY NUMBER
681 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
682 );
683
684 -- Returns the SP NAME
685 -- corresponding to the given SP ID
686 --
687 -- TABLE: XNP_SERVICE_PROVIDERS
688
689 PROCEDURE GET_SP_NAME
690 (p_SP_ID IN NUMBER
691 ,x_SP_NAME OUT NOCOPY VARCHAR2
692 ,x_ERROR_CODE OUT NOCOPY NUMBER
693 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
694 );
695
696 -- Updates the porting records for each number
697 -- in the TN range record corresponding to the given porting ID
698 -- (a.k.a object_reference)
699 --
700 -- The porting information, customer information and network
701 -- information including the routing number is updated
702 --
703 -- Usage Notes: Usage of parameter p_RECIPIENT_SP_ID is deprecated.
704 --
705 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
706 --
707 -- Comments: Use UPDATE_CUSTOMER_INFO, UPDATE_NOTES_INFO
708 -- and UPDATE_NETWORK_INFO instead
709
710 PROCEDURE SOA_UPDATE_REC_PORT_ORDER
711 (p_PORTING_ID VARCHAR2
712 ,p_STARTING_NUMBER NUMBER
713 ,p_ENDING_NUMBER NUMBER
714 ,p_DONOR_SP_ID NUMBER
715 ,p_RECIPIENT_SP_ID NUMBER DEFAULT NULL
716 ,p_ROUTING_NUMBER_ID NUMBER
717 ,p_NEW_SP_DUE_DATE DATE
718 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
719 ,p_CUSTOMER_ID VARCHAR2
720 ,p_CUSTOMER_NAME VARCHAR2
721 ,p_CUSTOMER_TYPE VARCHAR2
722 ,p_ADDRESS_LINE1 VARCHAR2
723 ,p_ADDRESS_LINE2 VARCHAR2
724 ,p_CITY VARCHAR2
725 ,p_PHONE VARCHAR2
726 ,p_FAX VARCHAR2
727 ,p_EMAIL VARCHAR2
728 ,p_PAGER VARCHAR2
729 ,p_PAGER_PIN VARCHAR2
730 ,p_INTERNET_ADDRESS VARCHAR2
731 ,p_ZIP_CODE VARCHAR2
732 ,p_COUNTRY VARCHAR2
733 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
734 ,p_CONTACT_NAME VARCHAR2
735 ,p_RETAIN_TN_FLAG VARCHAR2
736 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
737 ,p_CNAM_ADDRESS VARCHAR2
738 ,p_CNAM_SUBSYSTEM VARCHAR2
739 ,p_ISVM_ADDRESS VARCHAR2
740 ,p_ISVM_SUBSYSTEM VARCHAR2
741 ,p_LIDB_ADDRESS VARCHAR2
742 ,p_LIDB_SUBSYSTEM VARCHAR2
743 ,p_CLASS_ADDRESS VARCHAR2
744 ,p_CLASS_SUBSYSTEM VARCHAR2
745 ,p_WSMSC_ADDRESS VARCHAR2
746 ,p_WSMSC_SUBSYSTEM VARCHAR2
747 ,p_RN_ADDRESS VARCHAR2
748 ,p_RN_SUBSYSTEM VARCHAR2
749 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
750 ,p_ACTIVATION_DUE_DATE DATE
751 ,p_ORDER_ID IN NUMBER
752 ,p_LINEITEM_ID IN NUMBER
753 ,p_WORKITEM_INSTANCE_ID IN NUMBER
754 ,p_FA_INSTANCE_ID IN NUMBER
755 ,x_ERROR_CODE OUT NOCOPY NUMBER
756 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
757 );
758
759
760 -- Updates the porting records for each number in the
761 -- TN range record corresponding to the given porting ID
762 -- (a.k.a object_reference)
763 --
764 -- The porting information, customer information and network
765 -- information including the routing number is updated
766 --
767 -- Usage Notes: Usage of parameter p_DONOR_SP_ID is deprecated.
768 --
769 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
770 --
771 -- Comments: Use UPDATE_CUSTOMER_INFO, UPDATE_NOTES_INFO
772 -- and UPDATE_NETWORK_INFO instead
773 --
774 PROCEDURE SOA_UPDATE_DON_PORT_ORDER
775 (p_PORTING_ID VARCHAR2
776 ,p_STARTING_NUMBER NUMBER
777 ,p_ENDING_NUMBER NUMBER
778 ,p_DONOR_SP_ID NUMBER DEFAULT NULL
779 ,p_RECIPIENT_SP_ID NUMBER
780 ,p_OLD_SP_DUE_DATE DATE
781 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
782 ,p_CUSTOMER_ID VARCHAR2
783 ,p_CUSTOMER_NAME VARCHAR2
784 ,p_CUSTOMER_TYPE VARCHAR2
785 ,p_ADDRESS_LINE1 VARCHAR2
786 ,p_ADDRESS_LINE2 VARCHAR2
787 ,p_CITY VARCHAR2
788 ,p_PHONE VARCHAR2
789 ,p_FAX VARCHAR2
790 ,p_EMAIL VARCHAR2
791 ,p_PAGER VARCHAR2
792 ,p_PAGER_PIN VARCHAR2
793 ,p_INTERNET_ADDRESS VARCHAR2
794 ,p_ZIP_CODE VARCHAR2
795 ,p_COUNTRY VARCHAR2
799 ,p_RETAIN_DIR_INFO_FLAG VARCHAR2
796 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
797 ,p_CONTACT_NAME VARCHAR2
798 ,p_RETAIN_TN_FLAG VARCHAR2
800 ,p_CNAM_ADDRESS VARCHAR2
801 ,p_CNAM_SUBSYSTEM VARCHAR2
802 ,p_ISVM_ADDRESS VARCHAR2
803 ,p_ISVM_SUBSYSTEM VARCHAR2
804 ,p_LIDB_ADDRESS VARCHAR2
805 ,p_LIDB_SUBSYSTEM VARCHAR2
806 ,p_CLASS_ADDRESS VARCHAR2
807 ,p_CLASS_SUBSYSTEM VARCHAR2
808 ,p_WSMSC_ADDRESS VARCHAR2
809 ,p_WSMSC_SUBSYSTEM VARCHAR2
810 ,p_RN_ADDRESS VARCHAR2
811 ,p_RN_SUBSYSTEM VARCHAR2
812 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
813 ,p_ACTIVATION_DUE_DATE DATE
814 ,p_ORDER_ID IN NUMBER
815 ,p_LINEITEM_ID IN NUMBER
816 ,p_WORKITEM_INSTANCE_ID IN NUMBER
817 ,p_FA_INSTANCE_ID IN NUMBER
818 ,x_ERROR_CODE OUT NOCOPY NUMBER
819 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
820 );
821
822 -- Modifies record(s)and maintains a record of all the numbers provisioned
823 -- All the record correspondin to the range are modified with the new Network information
824 -- and Porting Time
825 --
826 -- TABLES: XNP_SV_SMS
827
828 PROCEDURE SMS_MODIFY_PORTED_NUMBER
829 (p_PORTING_ID IN VARCHAR2
830 ,p_STARTING_NUMBER IN NUMBER
831 ,p_ENDING_NUMBER IN NUMBER
832 ,p_ROUTING_NUMBER_ID IN NUMBER
833 ,p_PORTING_TIME IN DATE
834 ,p_CNAM_ADDRESS VARCHAR2
835 ,p_CNAM_SUBSYSTEM VARCHAR2
836 ,p_ISVM_ADDRESS VARCHAR2
837 ,p_ISVM_SUBSYSTEM VARCHAR2
838 ,p_LIDB_ADDRESS VARCHAR2
839 ,p_LIDB_SUBSYSTEM VARCHAR2
840 ,p_CLASS_ADDRESS VARCHAR2
841 ,p_CLASS_SUBSYSTEM VARCHAR2
842 ,p_WSMSC_ADDRESS VARCHAR2
843 ,p_WSMSC_SUBSYSTEM VARCHAR2
844 ,p_RN_ADDRESS VARCHAR2
845 ,p_RN_SUBSYSTEM VARCHAR2
846 ,p_ORDER_ID IN NUMBER
847 ,p_LINEITEM_ID IN NUMBER
848 ,p_WORKITEM_INSTANCE_ID IN NUMBER
849 ,p_FA_INSTANCE_ID IN NUMBER
850 ,x_ERROR_CODE OUT NOCOPY NUMBER
851 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
852 );
853
854 -- Checks if the number range is portable.
855 -- Procedure checks if the PORTED_INDICATOR
856 -- corresponding to given TN range has the value set to
857 -- 'PORTED_IN_USE' or 'PORTED_UNUSED'
858 -- @return 'Y' if true
859 --
860 -- TABLES: XNP_NUMBER_RANGES
861
862 PROCEDURE CHECK_IF_PORTABLE_RANGE
863 (p_STARTING_NUMBER VARCHAR2
864 ,p_ENDING_NUMBER VARCHAR2
865 ,x_CHECK_STATUS OUT NOCOPY NUMBER
866 ,x_ERROR_CODE OUT NOCOPY NUMBER
867 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
868 );
869
870 -- Updates the OLD_SP_DUE_DATE
871 -- for all the records with the given PORTING_ID.
872 --
873 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
874 --
875 -- TABLES: XNP_SV_SOA,XNP_SV_SOA_JN
876 PROCEDURE SOA_UPDATE_OLD_SP_DUE_DATE
877 (p_PORTING_ID VARCHAR2
878 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
879 ,p_OLD_SP_DUE_DATE DATE
880 ,p_ORDER_ID IN NUMBER
881 ,p_LINEITEM_ID IN NUMBER
882 ,p_WORKITEM_INSTANCE_ID IN NUMBER
883 ,p_FA_INSTANCE_ID IN NUMBER
884 ,x_ERROR_CODE OUT NOCOPY NUMBER
885 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
886 );
887
888 -- Updates the NEW_SP_DUE_DATE for all records
889 -- with the given PORTING_ID
890 --
891 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
892 --
893 -- TABLES: XNP_SV_SOA,XNP_SV_SOA_JN
894
895 PROCEDURE SOA_UPDATE_NEW_SP_DUE_DATE
896 (p_PORTING_ID VARCHAR2
897 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
898 ,p_NEW_SP_DUE_DATE DATE
899 ,p_ORDER_ID IN NUMBER
900 ,p_LINEITEM_ID IN NUMBER
901 ,p_WORKITEM_INSTANCE_ID IN NUMBER
902 ,p_FA_INSTANCE_ID IN NUMBER
903 ,x_ERROR_CODE OUT NOCOPY NUMBER
904 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
905 );
906
907 -- Checks if a porting record exists
908 -- in the given status, where the given
909 -- p_DONOR_SP_ID is the donor SP ID in the
910 --
911 -- Tables: XNP_SV_SOA
912 --
913 -- Usage Notes: Can be used for validating
914 -- the porting request at the Donor End to
915 -- ensure that the requested number range is
916 -- in ACTIVE status (for e.g.)
917 -- So it is ensured that the number is
918 -- not already being ported to another recipient
919 -- @return 'Y or 'N'
920 --
921
922 PROCEDURE CHECK_DONOR_STATUS_EXISTS
923 (p_STARTING_NUMBER VARCHAR2
924 ,p_ENDING_NUMBER VARCHAR2
925 ,p_STATUS_TYPE_CODE VARCHAR2
926 ,p_DONOR_SP_ID NUMBER
927 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
928 ,x_ERROR_CODE OUT NOCOPY NUMBER
929 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
930 );
931
932 -- Checks if a porting record exists
936 -- Tables: XNP_SV_SOA
933 -- in the given status, where the given
934 -- p_RECIPIENT_SP_ID is the Recipient SP ID in the
935 --
937 --
938 -- Usage Notes: Can be used for validating
939 -- the Subsequent porting request at the
940 -- Recipient End to ensure that the requested
941 -- number range is in ACTIVE status (for e.g.)
942 -- This way it is ensured that the recipient
943 -- can do a subsequent port of the number
944 -- @return 'Y ' or 'N'
945 --
946 PROCEDURE CHECK_RECIPIENT_STATUS_EXISTS
947 (p_STARTING_NUMBER VARCHAR2
948 ,p_ENDING_NUMBER VARCHAR2
949 ,p_STATUS_TYPE_CODE VARCHAR2
950 ,p_RECIPIENT_SP_ID NUMBER
951 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
952 ,x_ERROR_CODE OUT NOCOPY NUMBER
953 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
954 );
955
956
957 -- Updates the OLD_SP_CUTOFF_DUE_DATE for all records
958 -- with the given PORTING_ID.
959 --
960 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
961 --
962 -- TABLES: XNP_SV_SOA,XNP_SV_SOA_JN
963
964 PROCEDURE SOA_UPDATE_CUTOFF_DATE
965 (p_PORTING_ID VARCHAR2
966 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
967 ,p_OLD_SP_CUTOFF_DUE_DATE DATE
968 ,p_ORDER_ID IN NUMBER
969 ,p_LINEITEM_ID IN NUMBER
970 ,p_WORKITEM_INSTANCE_ID IN NUMBER
971 ,p_FA_INSTANCE_ID IN NUMBER
972 ,x_ERROR_CODE OUT NOCOPY NUMBER
973 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
974 );
975
976 -- Updates the Billing and charging information
977 -- for this porting transaction
978 -- All records with the given Porting ID are
979 -- updated.
980 --
981 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
982 --
983 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
984
985 PROCEDURE SOA_UPDATE_CHARGING_INFO
986 (p_PORTING_ID VARCHAR2
987 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
988 ,p_INVOICE_DUE_DATE DATE
989 ,p_CHARGING_INFO VARCHAR2
990 ,p_BILLING_ID NUMBER
991 ,p_USER_LOCTN_VALUE VARCHAR2
992 ,p_USER_LOCTN_TYPE VARCHAR2
993 ,p_PRICE_CODE VARCHAR2
994 ,p_PRICE_PER_CALL VARCHAR2
995 ,p_PRICE_PER_MINUTE VARCHAR2
996 ,p_ORDER_ID IN NUMBER
997 ,p_LINEITEM_ID IN NUMBER
998 ,p_WORKITEM_INSTANCE_ID IN NUMBER
999 ,p_FA_INSTANCE_ID IN NUMBER
1000 ,x_ERROR_CODE OUT NOCOPY NUMBER
1001 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1002 );
1003
1004 -- Checks if there exists a Porting record
1005 -- in the given status for this Porting ID.
1006 --
1007 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1008 --
1009 -- TABLES: XNP_SV_SOA
1010
1011 PROCEDURE CHECK_SOA_STATUS_EXISTS
1012 (p_PORTING_ID VARCHAR2
1013 ,p_STATUS_TYPE_CODE VARCHAR2
1014 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1015 ,x_CHECK_STATUS OUT NOCOPY VARCHAR2
1016 ,x_ERROR_CODE OUT NOCOPY NUMBER
1017 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1018 );
1019
1020 -- Updates OLD_SP_AUTHORIZATION_FLAG
1021 -- in table for the rows with given porting ID.
1022 --
1023 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1024 --
1025 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1026
1027 PROCEDURE SOA_UPDATE_OLD_SP_AUTH_FLAG
1028 (p_PORTING_ID VARCHAR2
1029 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1030 ,p_OLD_SP_AUTHORIZATION_FLAG VARCHAR2
1031 ,p_ORDER_ID IN NUMBER
1032 ,p_LINEITEM_ID IN NUMBER
1033 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1034 ,p_FA_INSTANCE_ID IN NUMBER
1035 ,x_ERROR_CODE OUT NOCOPY NUMBER
1036 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1037 );
1038
1039 -- Updates NEW_SP_AUTHORIZATION_FLAG
1040 -- in table for the rows with given porting ID.
1041 --
1042 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1043 --
1044 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1045
1046 PROCEDURE SOA_UPDATE_NEW_SP_AUTH_FLAG
1047 (p_PORTING_ID VARCHAR2
1048 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1049 ,p_NEW_SP_AUTHORIZATION_FLAG VARCHAR2
1050 ,p_ORDER_ID IN NUMBER
1051 ,p_LINEITEM_ID IN NUMBER
1052 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1053 ,p_FA_INSTANCE_ID IN NUMBER
1054 ,x_ERROR_CODE OUT NOCOPY NUMBER
1055 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1056 );
1057
1058 -- Identifies the Fulfillment Elements to provision number range and feature type
1059 -- and creates a map in SMS FE map
1060 -- table with status of 'NOT_PROVISIONED'
1061 --
1062 -- Usage Notes: Should be used only if provisioning
1063 -- is done by a legacy system and not SFM.
1064 --
1065 -- TABLES: XNP_SV_SMS, XNP_SV_SMS_FE_MAPS
1066 --
1067 PROCEDURE SMS_MARK_FES_TO_PROVISION
1068 (p_STARTING_NUMBER VARCHAR2
1069 ,p_ENDING_NUMBER VARCHAR2
1070 ,p_FEATURE_TYPE VARCHAR2
1071 ,p_ORDER_ID IN NUMBER
1072 ,p_LINEITEM_ID IN NUMBER
1073 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1074 ,p_FA_INSTANCE_ID IN NUMBER
1078
1075 ,x_ERROR_CODE OUT NOCOPY NUMBER
1076 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1077 );
1079 -- Identifies the Fulfillment Elements to deprovision number range and feature type
1080 -- The FEs are identified from the
1081 -- list of provisioned FEs for the given number range
1082 -- and feature type
1083 --
1084 -- Usage Notes: Should be used only if deprovisioning
1085 -- is done by a legacy system and not SFM.
1086 --
1087 -- TABLES: XNP_SV_SMS, XNP_SV_SMS_FE_MAPS
1088
1089 PROCEDURE SMS_MARK_FES_TO_DEPROVISION
1090 (p_STARTING_NUMBER VARCHAR2
1091 ,p_ENDING_NUMBER VARCHAR2
1092 ,p_FEATURE_TYPE VARCHAR2
1093 ,p_DEPROVISION_STATUS VARCHAR2
1094 ,p_ORDER_ID IN NUMBER
1095 ,p_LINEITEM_ID IN NUMBER
1096 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1097 ,p_FA_INSTANCE_ID IN NUMBER
1098 ,x_ERROR_CODE OUT NOCOPY NUMBER
1099 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1100 );
1101
1102 -- Sets the Locked flag to the given value
1103 -- for the enties in XNP_SV_SOA with the given
1104 -- object_reference.
1105 --
1106 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1107 --
1108 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1109 PROCEDURE SOA_SET_LOCKED_FLAG
1110 (p_PORTING_ID VARCHAR2
1111 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1112 ,p_LOCKED_FLAG VARCHAR2
1113 ,p_ORDER_ID IN NUMBER
1114 ,p_LINEITEM_ID IN NUMBER
1115 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1116 ,p_FA_INSTANCE_ID IN NUMBER
1117 ,x_ERROR_CODE OUT NOCOPY NUMBER
1118 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1119 );
1120
1121
1122 -- Gets the Locked flag to the given value
1123 -- from the entry in XNP_SV_SOA with the given
1124 -- object_reference.
1125 -- @return 'Y' or 'N'
1126 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1127 --
1128 -- TABLES: XNP_SV_SOA
1129
1130 PROCEDURE SOA_GET_LOCKED_FLAG
1131 (p_porting_id VARCHAR2
1132 ,p_local_sp_id NUMBER DEFAULT NULL
1133 ,x_locked_flag OUT NOCOPY VARCHAR2
1134 ,x_error_code OUT NOCOPY NUMBER
1135 ,x_error_message OUT NOCOPY VARCHAR2
1136 );
1137
1138 -- Gets the STATUS_TYPE_CODE from XNP_SV_SOA
1139 -- for the given PORTING_ID aka object_reference.
1140 -- Returns: Status type code if available, NULL if error
1141 --
1142 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1143 --
1144 -- TABLES: XNP_SV_SOA
1145
1146 PROCEDURE SOA_GET_SV_STATUS
1147 (p_PORTING_ID VARCHAR2
1148 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1149 ,x_SV_STATUS OUT NOCOPY VARCHAR2
1150 ,x_ERROR_CODE OUT NOCOPY NUMBER
1151 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1152 );
1153
1154 -- Checks if the STATUS_TYPE_CODE from XNP_SV_SOA
1155 -- for the given PORTING_ID aka object_reference is same as
1156 -- p_status_type_code
1157 --
1158 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1159 --
1160 -- TABLES: XNP_SV_SOA
1161 --
1162 -- @return 'T' if statuses match, 'F' if they don't
1163
1164 PROCEDURE SOA_CHECK_SV_STATUS
1165 (p_PORTING_ID VARCHAR2
1166 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1167 ,p_STATUS_TYPE_CODE VARCHAR2
1168 ,x_STATUS_MATCHED_FLAG OUT NOCOPY VARCHAR2
1169 ,x_ERROR_CODE OUT NOCOPY NUMBER
1170 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1171 );
1172
1173 -- Sets the Blocked flag to the given value
1174 -- for the enties in XNP_SV_SOA with the given
1175 -- object_reference.
1176 --
1177 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1178 --
1179 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1180 -- @return 'Y' or 'N'
1181
1182 PROCEDURE SOA_SET_BLOCKED_FLAG
1183 (p_PORTING_ID VARCHAR2
1184 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1185 ,p_BLOCKED_FLAG VARCHAR2
1186 ,p_ORDER_ID IN NUMBER
1187 ,p_LINEITEM_ID IN NUMBER
1188 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1189 ,p_FA_INSTANCE_ID IN NUMBER
1190 ,x_ERROR_CODE OUT NOCOPY NUMBER
1191 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1192 );
1193
1194 -- Gets the Blocked flag to the given value
1195 -- from the entry in XNP_SV_SOA with the given
1196 -- object_reference.
1197 -- @return 'Y' or 'N'
1198 --
1199 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1200 --
1201 -- TABLES: XNP_SV_SOA
1202 -- @return 'Y' or 'N'
1203
1204 PROCEDURE SOA_GET_BLOCKED_FLAG
1205 (p_porting_id varchar2
1206 ,p_local_sp_id number DEFAULT NULL
1207 ,x_blocked_flag OUT NOCOPY varchar2
1208 ,x_error_code OUT NOCOPY number
1209 ,x_error_message OUT NOCOPY varchar2
1210 );
1211
1212 -- Gets the New SP Auth flag to the given value
1213 -- from the entry in XNP_SV_SOA with the given
1214 -- object_reference.
1215 -- @return 'Y' or 'N'
1216 --
1217 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1218 --
1219 -- TABLES: XNP_SV_SOA
1220 -- @return 'Y' or 'N'
1221
1222 PROCEDURE SOA_GET_NEW_SP_AUTH_FLAG
1223 (p_porting_id varchar2
1224 ,p_local_sp_id number DEFAULT NULL
1225 ,x_new_sp_auth_flag OUT NOCOPY varchar2
1226 ,x_error_code OUT NOCOPY number
1227 ,x_error_message OUT NOCOPY varchar2
1228 );
1229
1233 -- @return 'Y' or 'N'
1230 -- Gets the Old SP Auth flag to the given value
1231 -- from the entry in XNP_SV_SOA with the given
1232 -- object_reference.
1234 --
1235 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1236 --
1237 -- TABLES: XNP_SV_SOA
1238 -- @return 'Y' or 'N'
1239
1240 PROCEDURE SOA_GET_OLD_SP_AUTH_FLAG
1241 (p_PORTING_ID VARCHAR2
1242 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1243 ,x_OLD_SP_AUTH_FLAG OUT NOCOPY VARCHAR2
1244 ,x_ERROR_CODE OUT NOCOPY NUMBER
1245 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1246 );
1247
1248 -- Updates the ACTIVATION_DUE_DATE for all the records
1249 -- with the given PORTING_ID
1250 --
1251 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1252 --
1253 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1254 -- @return 'Y' or 'N'
1255
1256 PROCEDURE SOA_UPDATE_ACTIVATION_DUE_DATE
1257 (p_PORTING_ID VARCHAR2
1258 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1259 ,p_ACTIVATION_DUE_DATE DATE
1260 ,p_ORDER_ID IN NUMBER
1261 ,p_LINEITEM_ID IN NUMBER
1262 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1263 ,p_FA_INSTANCE_ID IN NUMBER
1264 ,x_ERROR_CODE OUT NOCOPY NUMBER
1265 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1266 );
1267
1268 -- Checks if the given SP ID is the one which provisioned the entire number range
1269 --
1270 -- If the number range hasn't been provisioned
1271 -- explicitly (after a porting transaction), then
1272 -- checks if the SP is the assigned SP ID.
1273 --
1274 -- @return 'Y'if either is true
1275 --
1276 PROCEDURE CHECK_IF_SP_ASSIGNED
1277 (p_STARTING_NUMBER IN VARCHAR2
1278 ,p_ENDING_NUMBER IN VARCHAR2
1279 ,p_SP_ID IN NUMBER
1280 ,x_CHECK_IF_ASSIGNED OUT NOCOPY VARCHAR2
1281 ,x_ERROR_CODE OUT NOCOPY NUMBER
1282 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1283 );
1284
1285
1286 -- Updates the Comments, Notes and Preorder Authorization code
1287 -- for the given Porting id.
1288 --
1289 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1290 --
1291 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1292
1293 PROCEDURE SOA_UPDATE_NOTES_INFO
1294 (p_PORTING_ID VARCHAR2
1295 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1296 ,p_COMMENTS VARCHAR2
1297 ,p_NOTES VARCHAR2
1298 ,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
1299 ,p_ORDER_ID IN NUMBER
1300 ,p_LINEITEM_ID IN NUMBER
1301 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1302 ,p_FA_INSTANCE_ID IN NUMBER
1303 ,x_ERROR_CODE OUT NOCOPY NUMBER
1304 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1305 );
1306
1307 -- Updates the Network information in XNP_SV_SOA
1308 -- for the given Porting ID.
1309 --
1310 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1311 --
1312 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1313
1314 PROCEDURE SOA_UPDATE_NETWORK_INFO
1315 (p_PORTING_ID VARCHAR2
1316 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1317 ,p_ROUTING_NUMBER_ID NUMBER
1318 ,p_CNAM_ADDRESS VARCHAR2
1319 ,p_CNAM_SUBSYSTEM VARCHAR2
1320 ,p_ISVM_ADDRESS VARCHAR2
1321 ,p_ISVM_SUBSYSTEM VARCHAR2
1322 ,p_LIDB_ADDRESS VARCHAR2
1323 ,p_LIDB_SUBSYSTEM VARCHAR2
1324 ,p_CLASS_ADDRESS VARCHAR2
1325 ,p_CLASS_SUBSYSTEM VARCHAR2
1326 ,p_WSMSC_ADDRESS VARCHAR2
1327 ,p_WSMSC_SUBSYSTEM VARCHAR2
1328 ,p_RN_ADDRESS VARCHAR2
1329 ,p_RN_SUBSYSTEM VARCHAR2
1330 ,p_ORDER_ID IN NUMBER
1331 ,p_LINEITEM_ID IN NUMBER
1332 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1333 ,p_FA_INSTANCE_ID IN NUMBER
1334 ,x_ERROR_CODE OUT NOCOPY NUMBER
1335 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1336 );
1337
1338 -- Updates the customer information
1339 -- for the given Porting ID.
1340 --
1341 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1342 --
1343 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1344
1345 PROCEDURE SOA_UPDATE_CUSTOMER_INFO
1346 (p_PORTING_ID VARCHAR2
1347 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1348 ,p_CUSTOMER_ID VARCHAR2
1349 ,p_CUSTOMER_NAME VARCHAR2
1350 ,p_CUSTOMER_TYPE VARCHAR2
1351 ,p_ADDRESS_LINE1 VARCHAR2
1352 ,p_ADDRESS_LINE2 VARCHAR2
1353 ,p_CITY VARCHAR2
1354 ,p_PHONE VARCHAR2
1355 ,p_FAX VARCHAR2
1356 ,p_EMAIL VARCHAR2
1357 ,p_PAGER VARCHAR2
1358 ,p_PAGER_PIN VARCHAR2
1359 ,p_INTERNET_ADDRESS VARCHAR2
1360 ,p_ZIP_CODE VARCHAR2
1361 ,p_COUNTRY VARCHAR2
1362 ,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
1363 ,p_CONTACT_NAME VARCHAR2
1364 ,p_ORDER_ID IN NUMBER
1365 ,p_LINEITEM_ID IN NUMBER
1366 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1367 ,p_FA_INSTANCE_ID IN NUMBER
1368 ,x_ERROR_CODE OUT NOCOPY NUMBER
1372 -- Update the Porting ID
1369 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1370 );
1371
1373 -- for one or more Porting Records and in order header
1374 --
1375 -- The records to update are Identified in the XNP_SV_SOA
1376 -- using the number range and current status.
1377 --
1378 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1379 --
1380 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN, XDP_ORDER_HEADERS
1381 -- Refers to XDP_FULFILLMENT_WORKLIST
1382
1383 PROCEDURE SOA_UPDATE_PORTING_ID
1384 (p_STARTING_NUMBER VARCHAR2
1385 ,p_ENDING_NUMBER VARCHAR2
1386 ,p_CUR_STATUS_TYPE_CODE VARCHAR2
1387 ,p_LOCAL_SP_ID NUMBER DEFAULT NULL
1388 ,p_PORTING_ID VARCHAR2
1389 ,p_ORDER_ID IN NUMBER
1390 ,p_LINEITEM_ID IN NUMBER
1391 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1392 ,p_FA_INSTANCE_ID IN NUMBER
1393 ,x_ERROR_CODE OUT NOCOPY NUMBER
1394 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1395 );
1396
1397 -- Updates the PROVISIONING_DONE_DATE for the number range
1398 -- to the SYSDATE
1399 -- TABLES: XNP_SV_SMS
1400
1401 PROCEDURE SMS_UPDATE_PROV_DONE_DATE
1402 (p_STARTING_NUMBER VARCHAR2
1403 ,p_ENDING_NUMBER VARCHAR2
1404 ,p_ORDER_ID IN NUMBER
1405 ,p_LINEITEM_ID IN NUMBER
1406 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1407 ,p_FA_INSTANCE_ID IN NUMBER
1408 ,x_ERROR_CODE OUT NOCOPY NUMBER
1409 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1410 );
1411
1412 -- Runtime Validation check for NP work item
1413
1414 PROCEDURE RUNTIME_VALIDATION
1415 (p_ORDER_ID IN NUMBER
1416 ,p_LINE_ITEM_ID IN NUMBER
1417 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1418 ,p_STARTING_NUMBER IN NUMBER
1419 ,p_ENDING_NUMBER IN NUMBER
1420 ,p_ROUTING_NUMBER IN VARCHAR2
1421 ,p_DONOR_SP_CODE IN VARCHAR2
1422 ,p_RECIPIENT_SP_CODE IN VARCHAR2
1423 ,x_ERROR_CODE OUT NOCOPY NUMBER
1424 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1425 );
1426
1427 -- Checks whether Routing Number belongs to Recipient SP
1428
1429 PROCEDURE CHECK_RN_FOR_RECIPIENT
1430 (p_RECIPIENT_SP_ID IN NUMBER
1431 ,p_ROUTING_NUMBER_ID IN NUMBER
1432 ,x_ERROR_CODE OUT NOCOPY NUMBER
1433 ,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1434 );
1435
1436 -- Creates Mapping record in XNP_SV_ORADER_MAPPINGS
1437 --
1438 Procedure CREATE_ORDER_MAPPING
1439 ( P_ORDER_ID IN NUMBER ,
1440 P_LINEITEM_ID IN NUMBER ,
1441 P_WORKITEM_INSTANCE_ID iN NUMBER ,
1442 P_FA_INSTANCE_ID IN NUMBER ,
1443 P_SV_SOA_ID IN NUMBER ,
1444 P_SV_SMS_ID IN NUMBER ,
1445 X_ERROR_CODE OUT NOCOPY NUMBER ,
1446 X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1447 );
1448
1449
1450 -- Updates the DISCONNECT_DUE_DATE for all records
1451 -- with the given PORTING_ID
1452 --
1453 --
1454 -- TABLES: XNP_SV_SOA,XNP_SV_SOA_JN
1455
1456 Procedure SOA_UPDATE_DISCONN_DUE_DATE
1457 (p_PORTING_ID VARCHAR2 ,
1458 p_DISCONNECT_DUE_DATE DATE ,
1459 p_ORDER_ID IN NUMBER ,
1460 p_LINEITEM_ID IN NUMBER ,
1461 p_WORKITEM_INSTANCE_ID IN NUMBER ,
1462 p_FA_INSTANCE_ID IN NUMBER ,
1463 x_ERROR_CODE OUT NOCOPY NUMBER ,
1464 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1465 );
1466
1467 -- Updates the EFFECTIVE_RELEASE_DUE_DATE for all records
1468 -- with the given PORTING_ID
1469 --
1470 -- TABLES: XNP_SV_SOA,XNP_SV_SOA_JN
1471 --
1472
1473 PROCEDURE SOA_UPDATE_EFFECT_REL_DUE_DATE
1474 (p_PORTING_ID VARCHAR2 ,
1475 p_EFFECTIVE_RELEASE_DUE_DATE DATE ,
1476 p_ORDER_ID IN NUMBER ,
1477 p_LINEITEM_ID IN NUMBER ,
1478 p_WORKITEM_INSTANCE_ID IN NUMBER ,
1479 p_FA_INSTANCE_ID IN NUMBER ,
1480 x_ERROR_CODE OUT NOCOPY NUMBER ,
1481 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1482 );
1483
1484 -- Updates the NUMBER_RETURNED_DUE_DATE for all records
1485 -- with the given PORTING_ID
1486 -- TABLES: XNP_SV_SOA,XNP_SV_SOA_JN
1487 --
1488
1489 PROCEDURE SOA_UPDATE_NUM_RETURN_DUE_DATE
1490 (p_PORTING_ID VARCHAR2 ,
1491 p_NUMBER_RETURNED_DUE_DATE DATE ,
1492 p_ORDER_ID IN NUMBER ,
1493 p_LINEITEM_ID IN NUMBER ,
1494 p_WORKITEM_INSTANCE_ID IN NUMBER ,
1495 p_FA_INSTANCE_ID IN NUMBER ,
1496 x_ERROR_CODE OUT NOCOPY NUMBER ,
1497 x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1498 );
1499
1500
1501 -- Sets the Concirrence flag to the given value
1502 -- for the enties in XNP_SV_SOA with the given
1503 -- object_reference.
1504 --
1505 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1506 --
1507 -- TABLES: XNP_SV_SOA, XNP_SV_SOA_JN
1508
1509 PROCEDURE SOA_SET_CONCURRENCE_FLAG
1510 (P_PORTING_ID VARCHAR2
1511 ,P_LOCAL_SP_ID NUMBER DEFAULT NULL
1512 ,P_CONCURRENCE_FLAG VARCHAR2
1513 ,p_ORDER_ID IN NUMBER
1514 ,p_LINEITEM_ID IN NUMBER
1515 ,p_WORKITEM_INSTANCE_ID IN NUMBER
1516 ,p_FA_INSTANCE_ID IN NUMBER
1520
1517 ,X_ERROR_CODE OUT NOCOPY NUMBER
1518 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1519 );
1521 -- Gets the values of the Concurrence flag
1522 -- from the entry in XNP_SV_SOA with the given
1523 -- object_reference.
1524 -- @return 'Y' or 'N'
1525 -- Usage Notes: Usage of parameter p_LOCAL_SP_ID is deprecated.
1526 --
1527 -- TABLES: XNP_SV_SOA
1528
1529 PROCEDURE SOA_GET_CONCURRENCE_FLAG
1530 (P_PORTING_ID VARCHAR2
1531 ,P_LOCAL_SP_ID NUMBER DEFAUlT NULL
1532 ,X_CONCURRENCE_FLAG OUT NOCOPY VARCHAR2
1533 ,X_ERROR_CODE OUT NOCOPY NUMBER
1534 ,X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
1535 );
1536
1537 END XNP_CORE;
[Home] [Help]