APPS.XNP_SV_SOA_VL
[Home] [Help]
| Product: | XNP - Number Portability |
|---|---|
| Description: | Shows porting information for a single telephone number. Removed ( SVA.CREATED_BY_SP_ID=SPR_OWN.SP_ID ) from WHERE clausefor Service Bureau, since this will be handled automagically by VPD. rnyberg, 11/06/2000. |
| Implementation/DBA Data: |
APPS.XNP_SV_SOA_VL
|
SELECT SVA.SV_SOA_ID
, SVA.OBJECT_REFERENCE PORTING_ID
, SVA.SUBSCRIPTION_TN
, SVA.SUBSCRIPTION_TYPE
, SVA.DONOR_SP_ID
, SVA.RECIPIENT_SP_ID
, SVA.STATUS_TYPE_CODE
, SVA.PTO_FLAG
, SVA.CREATED_BY_SP_ID
, SVA.CHANGED_BY_SP_ID
, SVA.MEDIATOR_SP_ID
, SVA.ROUTING_NUMBER_ID
, SVA.PRE_SPLIT_SUBSCRIPTION_TN
, SVA.PREV_STATUS_TYPE_CODE
, SVA.CNAM_ADDRESS
, SVA.CNAM_SUBSYSTEM
, SVA.ISVM_ADDRESS
, SVA.ISVM_SUBSYSTEM
, SVA.LIDB_ADDRESS
, SVA.LIDB_SUBSYSTEM
, SVA.CLASS_ADDRESS
, SVA.CLASS_SUBSYSTEM
, SVA.WSMSC_ADDRESS
, SVA.WSMSC_SUBSYSTEM
, SVA.RN_ADDRESS
, SVA.RN_SUBSYSTEM
, SVA.ACTIVATION_DUE_DATE
, SVA.NEW_SP_DUE_DATE
, SVA.OLD_SP_DUE_DATE
, SVA.OLD_SP_CUTOFF_DUE_DATE
, SVA.EFFECTIVE_RELEASE_DUE_DATE
, SVA.NUMBER_RETURNED_DUE_DATE
, SVA.DISCONNECT_DUE_DATE
, SVA.INVOICE_DUE_DATE
, SVA.STATUS_CHANGE_DATE
, SVA.CREATED_DATE
, SVA.MODIFIED_DATE
, SVA.CONCURRENCE_FLAG
, SVA.NEW_SP_AUTHORIZATION_FLAG
, SVA.OLD_SP_AUTHORIZATION_FLAG
, SVA.RETAIN_DIR_INFO_FLAG
, SVA.RETAIN_TN_FLAG
, SVA.BLOCKED_FLAG
, SVA.LOCKED_FLAG
, SVA.CUSTOMER_CONTACT_REQ_FLAG
, SVA.BILLING_ID
, SVA.USER_LOCTN_VALUE
, SVA.USER_LOCTN_TYPE
, SVA.CHARGING_INFO
, SVA.ORDER_PRIORITY
, SVA.STATUS_CHANGE_CAUSE_CODE
, SVA.PREORDER_AUTHORIZATION_CODE
, SVA.CUSTOMER_ID
, SVA.CUSTOMER_NAME
, SVA.CUSTOMER_TYPE
, SVA.CONTACT_NAME
, SVA.ADDRESS_LINE1
, SVA.ADDRESS_LINE2
, SVA.CITY
, SVA.STATE
, SVA.ZIP_CODE
, SVA.COUNTRY
, SVA.PHONE
, SVA.MOBILE
, SVA.FAX
, SVA.PAGER
, SVA.PAGER_PIN
, SVA.EMAIL
, SVA.INTERNET_ADDRESS
, SVA.PRICE_CODE
, SVA.PRICE_PER_CALL
, SVA.PRICE_PER_MINUTE
, SVA.CREATED_BY
, SVA.CREATION_DATE
, SVA.LAST_UPDATED_BY
, SVA.LAST_UPDATE_DATE
, SVA.LAST_UPDATE_LOGIN
, STE_B.PHASE_INDICATOR STATUS_PHASE
, STE_TL.DISPLAY_NAME STATUS_DISPLAY_NAME
, STE_TL.DESCRIPTION STATUS_DESC
, SPR_DON.SP_TYPE DON_TYPE
, SPR_DON.CODE DON_CODE
, SPR_DON.NAME DON_NAME
, SPR_DON.TIMEZONE DON_TIMEZONE
, SPR_DON.SERVED_BY_FLAG DON_SERVED_BY_FLAG
, SPR_DON.ADDRESS_LINE1 DON_ADDRESS_LINE1
, SPR_DON.CITY DON_CITY
, SPR_DON.STATE DON_STATE
, SPR_DON.ZIP_CODE DON_ZIP_CODE
, SPR_DON.COUNTRY DON_COUNTRY
, SPR_DON.PHONE DON_PHONE
, SPR_DON.ADDRESS_LINE2 DON_ADDRESS_LINE2
, SPR_DON.MOBILE DON_MOBILE
, SPR_DON.FAX DON_FAX
, SPR_DON.PAGER DON_PAGER
, SPR_DON.PAGER_PIN DON_PAGER_PIN
, SPR_DON.EMAIL DON_EMAIL
, SPR_DON.INTERNET_ADDRESS DON_INTERNET
, SPR_DON.DEPARTMENT DON_DEPT
, SPR_REC.SP_TYPE REC_SP_TYPE
, SPR_REC.CODE REC_CODE
, SPR_REC.NAME REC_NAME
, SPR_REC.TIMEZONE REC_TIMEZONE
, SPR_REC.SERVED_BY_FLAG REC_SERVED_BY_FLAG
, SPR_REC.ADDRESS_LINE1 REC_ADDRESS_LINE1
, SPR_REC.CITY REC_CITY
, SPR_REC.STATE REC_STATE
, SPR_REC.ZIP_CODE REC_ZIP_CODE
, SPR_REC.COUNTRY REC_COUNTRY
, SPR_REC.PHONE REC_PHONE
, SPR_REC.ADDRESS_LINE2 REC_ADDRESS_LINE2
, SPR_REC.MOBILE REC_MOBILE
, SPR_REC.FAX REC_FAX
, SPR_REC.PAGER REC_PAGER
, SPR_REC.PAGER_PIN REC_PAGER_PIN
, SPR_REC.EMAIL REC_EMAIL
, SPR_REC.INTERNET_ADDRESS REC_INTERNET
, SPR_REC.DEPARTMENT REC_DEPT
, SPR_NRC.SP_TYPE NRC_SP_TYPE
, SPR_NRC.CODE NRC_CODE
, SPR_NRC.NAME NRC_NAME
, SPR_NRC.TIMEZONE NRC_TIMEZONE
, SPR_NRC.SERVED_BY_FLAG NRC_SERVED_BY_FLAG
, SPR_NRC.ADDRESS_LINE1 NRC_ADDRESS_LINE1
, SPR_NRC.CITY NRC_CITY
, SPR_NRC.STATE NRC_STATE
, SPR_NRC.ZIP_CODE NRC_ZIP_CODE
, SPR_NRC.COUNTRY NRC_COUNTRY
, SPR_NRC.PHONE NRC_PHONE
, SPR_NRC.ADDRESS_LINE2 NRC_ADDRESS_LINE2
, SPR_NRC.MOBILE NRC_MOBILE
, SPR_NRC.FAX NRC_FAX
, SPR_NRC.PAGER NRC_PAGER
, SPR_NRC.PAGER_PIN NRC_PAGER_PIN
, SPR_NRC.EMAIL NRC_EMAIL
, SPR_NRC.INTERNET_ADDRESS NRC_INTERNET
, SPR_NRC.DEPARTMENT NRC_DEPT
, RNR.OBJECT_REFERENCE ROUTING_REF
, RNR.INTERCONNECT_TYPE INTERCONNECT_TYPE
, RNR.SP_ID ROUTING_SP_ID
, RNR.ROUTING_NUMBER ROUTING_NUMBER
, RNR.STATUS ROUTING_STATUS
, SVA.COMMENTS
, SVA.NOTES
FROM XNP_SV_SOA SVA
, XNP_SV_STATUS_TYPES_B STE_B
, XNP_SV_STATUS_TYPES_TL STE_TL
, XNP_SERVICE_PROVIDERS SPR_DON
, XNP_SERVICE_PROVIDERS SPR_REC
, XNP_SERVICE_PROVIDERS SPR_NRC
, XNP_ROUTING_NUMBERS RNR
WHERE SVA.STATUS_TYPE_CODE=STE_B.STATUS_TYPE_CODE
AND STE_B.STATUS_TYPE_CODE=STE_TL.STATUS_TYPE_CODE
AND STE_TL.LANGUAGE = USERENV('LANG')
AND SVA.DONOR_SP_ID=SPR_DON.SP_ID
AND SVA.RECIPIENT_SP_ID=SPR_REC.SP_ID
AND SVA.MEDIATOR_SP_ID=SPR_NRC.SP_ID (+)
AND SVA.ROUTING_NUMBER_ID=RNR.ROUTING_NUMBER_ID (+)