VIEW: APPS.XNP_SV_SOA_VL

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)