[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT assigned_sp_id
FROM xnp_number_ranges
WHERE starting_number <= p_starting_number
AND ending_number >= p_ending_number
AND active_flag = 'Y';
SELECT sp_id
FROM xnp_service_providers
WHERE code = p_sp_name
AND active_flag = 'Y';
SELECT code
FROM xnp_service_providers
WHERE sp_id = p_sp_id;
SELECT routing_number_id
FROM xnp_routing_numbers
WHERE routing_number = p_routing_number
AND active_flag = 'Y';
SELECT geo_area_id
FROM xnp_number_ranges
WHERE starting_number <= p_starting_number
AND ending_number >= p_ending_number
AND sysdate >= effective_date;
SELECT child_geo_area_id
FROM xnp_geo_hierarchy
START WITH child_geo_area_id = l_starting_geo_id
CONNECT BY PRIOR parent_geo_area_id = child_geo_area_id;
SELECT sp.sp_id
FROM xnp_service_providers sp
,xnp_service_areas sa
WHERE sp.sp_id = sa.sp_id
AND sp.sp_type = 'NRC'
AND sa.geo_area_id = l_geo_id;
SELECT sv_soa_id
FROM xnp_sv_soa SOA , xnp_sv_status_types_b STA
WHERE SOA.subscription_tn = p_subscription_tn
AND STA.phase_indicator = p_phase_indicator
AND STA.status_type_code = SOA.status_type_code;
SELECT phase_indicator
FROM xnp_sv_status_types_b
WHERE status_type_code = p_cur_status_type_code;
SELECT number_range_id
FROM xnp_number_ranges
WHERE starting_number <= p_starting_number
AND ending_number >= p_ending_number
AND sysdate >= effective_date
AND active_flag='Y';
SELECT sv_sms_id
FROM xnp_sv_sms
WHERE subscription_tn = p_subscription_tn ;
SELECT sv_sms_id
FROM xnp_sv_sms
WHERE subscription_tn = l_ph_no
AND subscription_type = p_subscription_type;
SELECT xnp_sv_sms_s.nextval
INTO l_sv_sms_id
FROM dual;
INSERT into xnp_sv_sms
(sv_sms_id ,
object_reference ,
routing_number_id ,
subscription_tn ,
subscription_type ,
mediator_sp_id ,
provision_sent_date ,
cnam_address ,
cnam_subsystem ,
isvm_address ,
isvm_subsystem ,
lidb_address ,
lidb_subsystem ,
class_address ,
class_subsystem ,
wsmsc_address ,
wsmsc_subsystem ,
rn_address ,
rn_subsystem ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(xnp_sv_sms_s.nextval ,
p_porting_id ,
p_routing_number_id ,
to_char((l_init+l_counter)) ,
p_subscription_type ,
l_nrc_id ,
p_porting_time ,
p_cnam_address ,
p_cnam_subsystem ,
p_isvm_address ,
p_isvm_subsystem ,
p_lidb_address ,
p_lidb_subsystem ,
p_class_address ,
p_class_subsystem ,
p_wsmsc_address ,
p_wsmsc_subsystem ,
p_rn_address ,
p_rn_subsystem ,
fnd_global.
user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
) RETURNING sv_sms_id INTO l_sv_sms_id ;
UPDATE xnp_sv_sms
SET object_reference = p_porting_id
,provision_sent_date = p_porting_time
,routing_number_id = p_routing_number_id
,cnam_address = p_cnam_address
,cnam_subsystem = p_cnam_subsystem
,isvm_address = p_isvm_address
,isvm_subsystem = p_isvm_subsystem
,lidb_address = p_lidb_address
,lidb_subsystem = p_lidb_subsystem
,class_address = p_class_address
,class_subsystem = p_class_subsystem
,wsmsc_address = p_wsmsc_address
,wsmsc_subsystem = p_wsmsc_subsystem
,rn_address = p_rn_address
,rn_subsystem = p_rn_subsystem
,last_updated_by = fnd_global.user_id
,last_update_date = sysdate
WHERE sv_sms_id = l_sv_sms_id ;
PROCEDURE SOA_UPDATE_CUTOFF_DATE
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_CUR_STATUS_TYPE_CODE VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_OLD_SP_CUTOFF_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_counter BINARY_INTEGER := 0;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa,
xnp_sv_status_types_b sta
WHERE soa.subscription_tn
BETWEEN l_starting_number AND l_ending_number
AND sta.phase_indicator = l_phase_indicator
AND sta.status_type_code = soa.status_type_code;
UPDATE xnp_sv_soa soa
SET soa.old_sp_cutoff_due_date = p_old_sp_cutoff_due_date,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
,'XNP_CORE.SOA_UPDATE_CUTOFF_DATE');
END SOA_UPDATE_CUTOFF_DATE;
PROCEDURE SOA_UPDATE_SV_STATUS
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_CUR_STATUS_TYPE_CODE VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_NEW_STATUS_TYPE_CODE VARCHAR2
,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_counter BINARY_INTEGER := 0;
SELECT soa.sv_soa_id,
soa.status_type_code BULK COLLECT
INTO l_sv_soa_id,
l_sv_event_code
FROM xnp_sv_soa soa,
xnp_sv_status_types_b sta
WHERE SOA.subscription_tn BETWEEN l_starting_number AND l_ending_number
AND STA.phase_indicator = l_phase_indicator
AND STA.status_type_code = SOA.status_type_code;
UPDATE xnp_sv_soa soa
SET soa.status_type_code = p_new_status_type_code ,
soa.status_change_cause_code = p_status_change_cause_code ,
soa.prev_status_type_code = soa.status_type_code,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i)
AND soa.status_type_code <> p_new_status_type_code;
INSERT INTO XNP_SV_EVENT_HISTORY
(sv_event_history_id ,
sv_soa_id ,
event_code ,
event_type ,
event_timestamp ,
event_cause_code ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_EVENT_HISTORY_S.nextval,
l_sv_soa_id(i) ,
l_sv_event_code(i) ,
'STATUS_CHANGE' ,
sysdate ,
p_status_change_cause_code,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_SV_STATUS');
END SOA_UPDATE_SV_STATUS;
INSERT INTO xnp_sv_soa
(sv_soa_id
,object_reference
,subscription_tn
,subscription_type
,donor_sp_id
,recipient_sp_id
,routing_number_id
,status_type_code
,pto_flag
,created_by_sp_id
,changed_by_sp_id
,mediator_sp_id
,old_sp_cutoff_due_date
,customer_id
,customer_name
,customer_type
,address_line1
,address_line2
,city
,phone
,fax
,email
,zip_code
,country
,new_sp_due_date
,old_sp_due_date
,customer_contact_req_flag
,contact_name
,retain_tn_flag
,retain_dir_info_flag
,pager
,pager_pin
,internet_address
,cnam_address
,cnam_subsystem
,isvm_address
,isvm_subsystem
,lidb_address
,lidb_subsystem
,class_address
,class_subsystem
,wsmsc_address
,wsmsc_subsystem
,rn_address
,rn_subsystem
,preorder_authorization_code
,activation_due_date
,order_priority
,comments
,notes
,created_date
,modified_date
,created_by
,creation_date
,last_updated_by
,last_update_date
)
VALUES
(xnp_sv_soa_s.nextval
,p_porting_id -- obj ref
,l_subscription_tn -- subs TN
,'NP' -- subs type
,p_donor_sp_id
,p_recipient_sp_id
,l_routing_number_id
,l_new_status_type_code
,l_pto_flag
,p_recipient_sp_id
,p_recipient_sp_id
,l_mediator_sp_id
,p_old_sp_cutoff_due_date
,p_customer_id
,p_customer_name
,p_customer_type
,p_address_line1
,p_address_line2
,p_city
,p_phone
,p_fax
,p_email
,p_zip_code
,p_country
,p_new_sp_due_date
,p_new_sp_due_date
,p_customer_contact_req_flag
,p_contact_name
,p_retain_tn_flag
,p_retain_dir_info_flag
,p_pager
,p_pager_pin
,p_internet_address
,p_cnam_address
,p_cnam_subsystem
,p_isvm_address
,p_isvm_subsystem
,p_lidb_address
,p_lidb_subsystem
,p_class_address
,p_class_subsystem
,p_wsmsc_address
,p_wsmsc_subsystem
,p_rn_address
,p_rn_subsystem
,p_preorder_authorization_code
,p_activation_due_date
,p_order_priority
,p_comments
,p_notes
,sysdate
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
) RETURNING sv_soa_id INTO l_sv_soa_id;
SELECT routing_number_id
FROM xnp_routing_numbers
WHERE routing_number = l_routing_number
AND sp_id = l_sp_id
AND interconnect_type = 'LRN' ;
INSERT INTO XNP_ROUTING_NUMBERS
(
ROUTING_NUMBER_ID ,
OBJECT_REFERENCE ,
INTERCONNECT_TYPE ,
SP_ID ,
ROUTING_NUMBER ,
ACTIVE_FLAG ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
XNP_ROUTING_NUMBERS_S.nextval ,
p_RECIPIENT_SP_ID||'-'||p_ROUTING_NUMBER||'-'||'LRN',
'LRN' ,
p_RECIPIENT_SP_ID ,
p_ROUTING_NUMBER ,
'Y' ,
null ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id
) RETURNING routing_number_id INTO l_routing_number_id ;
INSERT INTO XNP_SV_SOA
(sv_soa_id
,object_reference
,subscription_tn
,subscription_type
,donor_sp_id
,recipient_sp_id
,routing_number_id
,status_type_code
,pto_flag
,created_by_sp_id
,changed_by_sp_id
,mediator_sp_id
,old_sp_cutoff_due_date
,customer_id
,customer_name
,customer_type
,address_line1
,address_line2
,city
,phone
,fax
,email
,zip_code
,country
,new_sp_due_date
,old_sp_due_date
,customer_contact_req_flag
,contact_name
,retain_tn_flag
,retain_dir_info_flag
,pager
,pager_pin
,internet_address
,cnam_address
,cnam_subsystem
,isvm_address
,isvm_subsystem
,lidb_address
,lidb_subsystem
,class_address
,class_subsystem
,wsmsc_address
,wsmsc_subsystem
,rn_address
,rn_subsystem
,preorder_authorization_code
,activation_due_date
,order_priority
,comments
,notes
,created_date
,modified_date
,created_by
,creation_date
,last_updated_by
,last_update_date
)
VALUES
(xnp_sv_soa_s.nextval
,p_porting_id -- obj ref
,l_subscription_tn -- subs TN
,'NP' -- subs type
,p_donor_sp_id
,p_recipient_sp_id
,l_routing_number_id
,l_new_status_type_code
,l_pto_flag
,l_mediator_sp_id
,p_recipient_sp_id
,l_mediator_sp_id
,p_old_sp_cutoff_due_date
,p_customer_id
,p_customer_name
,p_customer_type
,p_address_line1
,p_address_line2
,p_city
,p_phone
,p_fax
,p_email
,p_zip_code
,p_country
,p_new_sp_due_date
,p_new_sp_due_date
,p_customer_contact_req_flag
,p_contact_name
,p_retain_tn_flag
,p_retain_dir_info_flag
,p_pager
,p_pager_pin
,p_internet_address
,p_cnam_address
,p_cnam_subsystem
,p_isvm_address
,p_isvm_subsystem
,p_lidb_address
,p_lidb_subsystem
,p_class_address
,p_class_subsystem
,p_wsmsc_address
,p_wsmsc_subsystem
,p_rn_address
,p_rn_subsystem
,p_preorder_authorization_code
,p_activation_due_date
,p_order_priority
,p_comments
,p_notes
,sysdate
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
) RETURNING sv_soa_id INTO l_sv_soa_id;
SELECT xnp_sv_soa_s.nextval
INTO l_sv_soa_id
FROM dual;
INSERT INTO XNP_SV_SOA
(SV_SOA_ID
,OBJECT_REFERENCE
,SUBSCRIPTION_TN
,SUBSCRIPTION_TYPE
,DONOR_SP_ID
,RECIPIENT_SP_ID
,ROUTING_NUMBER_ID
,STATUS_TYPE_CODE
,PTO_FLAG
,CREATED_BY_SP_ID
,CHANGED_BY_SP_ID
,MEDIATOR_SP_ID
,NEW_SP_DUE_DATE
,OLD_SP_DUE_DATE
,OLD_SP_CUTOFF_DUE_DATE
,CUSTOMER_ID
,CUSTOMER_NAME
,CUSTOMER_TYPE
,ADDRESS_LINE1
,ADDRESS_LINE2
,CITY
,PHONE
,FAX
,EMAIL
,ZIP_CODE
,COUNTRY
,CUSTOMER_CONTACT_REQ_FLAG
,CONTACT_NAME
,RETAIN_TN_FLAG
,RETAIN_DIR_INFO_FLAG
,PAGER
,PAGER_PIN
,INTERNET_ADDRESS
,CNAM_ADDRESS
,CNAM_SUBSYSTEM
,ISVM_ADDRESS
,ISVM_SUBSYSTEM
,LIDB_ADDRESS
,LIDB_SUBSYSTEM
,CLASS_ADDRESS
,CLASS_SUBSYSTEM
,WSMSC_ADDRESS
,WSMSC_SUBSYSTEM
,RN_ADDRESS
,RN_SUBSYSTEM
,PREORDER_AUTHORIZATION_CODE
,ACTIVATION_DUE_DATE
,ORDER_PRIORITY
,COMMENTS
,NOTES
,CREATED_DATE
,MODIFIED_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
)
VALUES
(xnp_sv_soa_s.nextval
,p_PORTING_ID
,l_SUBSCRIPTION_TN -- telephone number
,'NP' -- subs type
,p_DONOR_SP_ID
,p_RECIPIENT_SP_ID
,l_ROUTING_NUMBER_ID
,l_NEW_STATUS_TYPE_CODE
,l_PTO_FLAG
,p_DONOR_SP_ID
,p_DONOR_SP_ID
,l_MEDIATOR_SP_ID
,p_NEW_SP_DUE_DATE
,p_NEW_SP_DUE_DATE
,p_OLD_SP_CUTOFF_DUE_DATE
,p_CUSTOMER_ID
,p_CUSTOMER_NAME
,p_CUSTOMER_TYPE
,p_ADDRESS_LINE1
,p_ADDRESS_LINE2
,p_CITY
,p_PHONE
,p_FAX
,p_EMAIL
,p_ZIP_CODE
,p_COUNTRY
,p_CUSTOMER_CONTACT_REQ_FLAG
,p_CONTACT_NAME
,p_RETAIN_TN_FLAG
,p_RETAIN_DIR_INFO_FLAG
,p_PAGER
,p_PAGER_PIN
,p_INTERNET_ADDRESS
,p_CNAM_ADDRESS
,p_CNAM_SUBSYSTEM
,p_ISVM_ADDRESS
,p_ISVM_SUBSYSTEM
,p_LIDB_ADDRESS
,p_LIDB_SUBSYSTEM
,p_CLASS_ADDRESS
,p_CLASS_SUBSYSTEM
,p_WSMSC_ADDRESS
,p_WSMSC_SUBSYSTEM
,p_RN_ADDRESS
,p_RN_SUBSYSTEM
,p_PREORDER_AUTHORIZATION_CODE
,p_ACTIVATION_DUE_DATE
,p_ORDER_PRIORITY
,p_COMMENTS
,p_NOTES
,SYSDATE
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
) RETURNING sv_soa_id INTO l_sv_soa_id;
SELECT retain_dir_info_flag
FROM xnp_sv_soa
WHERE object_reference=p_porting_id;
PROCEDURE SOA_UPDATE_CHARGING_INFO
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_CUR_STATUS_TYPE_CODE VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_INVOICE_DUE_DATE DATE
,p_CHARGING_INFO VARCHAR2
,p_BILLING_ID NUMBER
,p_USER_LOCTN_VALUE VARCHAR2
,p_USER_LOCTN_TYPE VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_counter BINARY_INTEGER := 0;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa,
xnp_sv_status_types_b sta
WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
AND sta.phase_indicator = l_phase_indicator
AND sta.status_type_code = soa.status_type_code;
UPDATE xnp_sv_soa soa
SET soa.invoice_due_date = p_invoice_due_date,
soa.charging_info = p_charging_info,
soa.user_loctn_type = p_user_loctn_type,
soa.user_loctn_value = p_user_loctn_value,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_CHARGING_INFO');
END SOA_UPDATE_CHARGING_INFO;
PROCEDURE SMS_INSERT_FE_MAP
(p_STARTING_NUMBER NUMBER
,p_ENDING_NUMBER NUMBER
,p_FE_ID NUMBER
,p_FEATURE_TYPE VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_SV_ID NUMBER := 0;
SELECT sms_fe_map_id
FROM xnp_sv_sms_fe_maps
WHERE sv_sms_id = l_cur_sv_id
AND fe_id = p_fe_id
AND feature_type = p_feature_type;
SELECT xnp_sv_sms_fe_maps_s.nextval
INTO l_sms_fe_map_id
FROM dual;
INSERT INTO xnp_sv_sms_fe_maps
(SMS_FE_MAP_ID
,SV_SMS_ID
,FE_ID
,FEATURE_TYPE
,PROVISION_STATUS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
)
VALUES (
xnp_sv_sms_fe_maps_s.nextval
,l_SV_ID
,p_FE_ID
,p_FEATURE_TYPE
,'NOT_PROVISIONED'
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,sysdate
);
UPDATE xnp_sv_sms_fe_maps
SET provision_status = 'NOT_PROVISIONED' ,
last_updated_by = fnd_global.user_id ,
last_update_date = sysdate
WHERE sms_fe_map_id = l_sms_fe_map_id;
,'XNP_CORE.SMS_INSERT_FE_MAP');
END SMS_INSERT_FE_MAP;
PROCEDURE SOA_UPDATE_SV_STATUS
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_NEW_STATUS_TYPE_CODE VARCHAR2
,p_STATUS_CHANGE_CAUSE_CODE VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT soa.sv_soa_id,
soa.status_type_code BULK COLLECT
INTO l_sv_soa_id,
l_sv_event_code
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id ;
UPDATE xnp_sv_soa soa
SET soa.status_type_code = p_new_status_type_code ,
soa.status_change_cause_code = p_status_change_cause_code ,
soa.prev_status_type_code = soa.status_type_code,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i)
AND soa.status_type_code <> p_new_status_type_code;
INSERT INTO XNP_SV_EVENT_HISTORY
(sv_event_history_id ,
sv_soa_id ,
event_code ,
event_type ,
event_timestamp ,
event_cause_code ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_EVENT_HISTORY_S.nextval,
l_sv_soa_id(i) ,
l_sv_event_code(i) ,
'STATUS_CHANGE' ,
sysdate ,
p_status_change_cause_code,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token ('FAILED_PROC','XNP_CORE.SOA_UPDATE_SV_STATUS');
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_SV_STATUS');
END SOA_UPDATE_SV_STATUS;
SELECT sv_soa_id
FROM xnp_sv_soa SOA
WHERE SOA.status_type_code = p_status_type_code
AND TO_NUMBER(SOA.subscription_tn) BETWEEN TO_NUMBER(p_starting_number) AND TO_NUMBER(p_ending_number) ;
PROCEDURE SMS_DELETE_FE_MAP
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_FE_ID NUMBER
,p_FEATURE_TYPE VARCHAR2
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_STARTING_NUMBER VARCHAR2(80) := null;
DELETE
FROM xnp_sv_sms_fe_maps
WHERE fe_id = p_fe_id
AND feature_type = p_feature_type
AND sv_sms_id IN
(SELECT sv_sms_id
FROM xnp_sv_sms
WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number) ;
,'XNP_CORE.SMS_DELETE_FE_MAP');
END SMS_DELETE_FE_MAP;
SELECT sv_soa_id
FROM xnp_sv_soa SOA , xnp_sv_status_types_b STA
WHERE SOA.subscription_tn = l_starting_number
AND SOA.donor_sp_id = p_sp_id
AND SOA.status_type_code = STA.status_type_code
AND STA.phase_indicator = p_phase_indicator ;
SELECT sv_soa_id
FROM xnp_sv_soa SOA ,
xnp_sv_status_types_b STA
WHERE SOA.subscription_tn = p_starting_number
AND SOA.recipient_sp_id = p_sp_id
AND SOA.status_type_code = STA.status_type_code
AND STA.phase_indicator = p_phase_indicator ;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
AND soa.status_type_code IN
(SELECT sta.status_type_code
FROM xnp_sv_status_types_b sta
WHERE sta.phase_indicator = p_cur_phase_indicator
AND sta.status_type_code <> p_omit_status);
UPDATE xnp_sv_soa soa
SET soa.status_type_code = (SELECT min(sta.status_type_code)
FROM xnp_sv_status_types_b sta
WHERE sta.phase_indicator = p_reset_phase_indicator) ,
soa.status_change_cause_code = p_status_change_cause_code ,
soa.prev_status_type_code = soa.status_type_code,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i)
RETURNING soa.status_type_code BULK COLLECT INTO l_sv_event_code;
INSERT INTO XNP_SV_EVENT_HISTORY
(sv_event_history_id ,
sv_soa_id ,
event_code ,
event_type ,
event_timestamp ,
event_cause_code ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_EVENT_HISTORY_S.nextval,
l_sv_soa_id(i) ,
l_sv_event_code(i) ,
'STATUS_CHANGE' ,
sysdate ,
p_status_change_cause_code,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
PROCEDURE SOA_UPDATE_OLD_SP_DUE_DATE
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_CUR_STATUS_TYPE_CODE VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_OLD_SP_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_counter BINARY_INTEGER := 0;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa,
xnp_sv_status_types_b sta
WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
AND sta.phase_indicator = l_phase_indicator
AND sta.status_type_code = soa.status_type_code;
UPDATE xnp_sv_soa soa
SET soa.old_sp_due_date = p_old_sp_due_date,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_OLD_SP_DUE_DATE');
END SOA_UPDATE_OLD_SP_DUE_DATE;
PROCEDURE SMS_DELETE_PORTED_NUMBER
(p_STARTING_NUMBER IN VARCHAR2
,p_ENDING_NUMBER IN VARCHAR2
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_sms_map_id NUMBER := 0;
SELECT sms_fe_map_id
FROM xnp_sv_sms_fe_maps
WHERE sv_sms_id in
(SELECT sv_sms_id
FROM xnp_sv_sms
WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number
);
DELETE
FROM xnp_sv_order_mappings
WHERE sv_sms_id IN
(SELECT sv_sms_id
FROM xnp_sv_sms
WHERE subscription_tn BETWEEN l_starting_number
AND l_ending_number);
DELETE
FROM xnp_sv_sms
WHERE subscription_tn BETWEEN l_starting_number
AND l_ending_number ;
,'XNP_CORE.SMS_DELETE_PORTED_NUMBER');
END SMS_DELETE_PORTED_NUMBER;
PROCEDURE SMS_UPDATE_FE_MAP_STATUS
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_FE_ID NUMBER
,p_FEATURE_TYPE VARCHAR2
,p_PROV_STATUS VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_STARTING_NUMBER VARCHAR2(80) := null;
SELECT sv_sms_id BULK COLLECT
INTO l_sv_sms_id
FROM xnp_sv_sms sms
WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number ;
UPDATE xnp_sv_sms_fe_maps
SET provision_status = p_prov_status ,
last_updated_by = fnd_global.user_id ,
last_update_date = sysdate
WHERE sv_sms_id = l_sv_sms_id(i)
AND fe_id = p_fe_id
AND feature_type = p_feature_type;
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_sms_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_sms_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
,'XNP_CORE.SMS_UPDATE_FE_MAP_STATUS');
END SMS_UPDATE_FE_MAP_STATUS;
PROCEDURE SOA_UPDATE_REC_PORT_ORDER
(p_PORTING_ID VARCHAR2
,p_STARTING_NUMBER NUMBER
,p_ENDING_NUMBER NUMBER
,p_DONOR_SP_ID NUMBER
,p_RECIPIENT_SP_ID NUMBER DEFAULT NULL
,p_ROUTING_NUMBER_ID NUMBER
,p_NEW_SP_DUE_DATE DATE
,p_OLD_SP_CUTOFF_DUE_DATE DATE
,p_CUSTOMER_ID VARCHAR2
,p_CUSTOMER_NAME VARCHAR2
,p_CUSTOMER_TYPE VARCHAR2
,p_ADDRESS_LINE1 VARCHAR2
,p_ADDRESS_LINE2 VARCHAR2
,p_CITY VARCHAR2
,p_PHONE VARCHAR2
,p_FAX VARCHAR2
,p_EMAIL VARCHAR2
,p_PAGER VARCHAR2
,p_PAGER_PIN VARCHAR2
,p_INTERNET_ADDRESS VARCHAR2
,p_ZIP_CODE VARCHAR2
,p_COUNTRY VARCHAR2
,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
,p_CONTACT_NAME VARCHAR2
,p_RETAIN_TN_FLAG VARCHAR2
,p_RETAIN_DIR_INFO_FLAG VARCHAR2
,p_CNAM_ADDRESS VARCHAR2
,p_CNAM_SUBSYSTEM VARCHAR2
,p_ISVM_ADDRESS VARCHAR2
,p_ISVM_SUBSYSTEM VARCHAR2
,p_LIDB_ADDRESS VARCHAR2
,p_LIDB_SUBSYSTEM VARCHAR2
,p_CLASS_ADDRESS VARCHAR2
,p_CLASS_SUBSYSTEM VARCHAR2
,p_WSMSC_ADDRESS VARCHAR2
,p_WSMSC_SUBSYSTEM VARCHAR2
,p_RN_ADDRESS VARCHAR2
,p_RN_SUBSYSTEM VARCHAR2
,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
,p_ACTIVATION_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id ;
UPDATE xnp_sv_soa soa
SET SOA.CHANGED_BY_SP_ID = p_RECIPIENT_SP_ID ,
SOA.OLD_SP_CUTOFF_DUE_DATE = p_OLD_SP_CUTOFF_DUE_DATE ,
SOA.CUSTOMER_ID = p_CUSTOMER_ID ,
SOA.CUSTOMER_NAME = p_CUSTOMER_NAME ,
SOA.CUSTOMER_TYPE = p_CUSTOMER_TYPE ,
SOA.ADDRESS_LINE1 = p_ADDRESS_LINE1 ,
SOA.ADDRESS_LINE2 = p_ADDRESS_LINE2 ,
SOA.CITY = p_CITY ,
SOA.PHONE = p_PHONE ,
SOA.FAX = p_FAX ,
SOA.EMAIL = p_EMAIL ,
SOA.ZIP_CODE = p_ZIP_CODE ,
SOA.COUNTRY = p_COUNTRY ,
SOA.NEW_SP_DUE_DATE = p_NEW_SP_DUE_DATE ,
SOA.CUSTOMER_CONTACT_REQ_FLAG = p_CUSTOMER_CONTACT_REQ_FLAG ,
SOA.CONTACT_NAME = p_CONTACT_NAME ,
SOA.RETAIN_TN_FLAG = p_RETAIN_TN_FLAG ,
SOA.RETAIN_DIR_INFO_FLAG = p_RETAIN_DIR_INFO_FLAG ,
SOA.PAGER = p_PAGER ,
SOA.PAGER_PIN = p_PAGER_PIN ,
SOA.INTERNET_ADDRESS = p_INTERNET_ADDRESS ,
SOA.CNAM_ADDRESS = p_CNAM_ADDRESS ,
SOA.CNAM_SUBSYSTEM = p_CNAM_SUBSYSTEM ,
SOA.ISVM_ADDRESS = p_ISVM_ADDRESS ,
SOA.ISVM_SUBSYSTEM = p_ISVM_SUBSYSTEM ,
SOA.LIDB_ADDRESS = p_LIDB_ADDRESS ,
SOA.LIDB_SUBSYSTEM = p_LIDB_SUBSYSTEM ,
SOA.CLASS_ADDRESS = p_CLASS_ADDRESS ,
SOA.CLASS_SUBSYSTEM = p_CLASS_SUBSYSTEM ,
SOA.WSMSC_ADDRESS = p_WSMSC_ADDRESS ,
SOA.WSMSC_SUBSYSTEM = p_WSMSC_SUBSYSTEM ,
SOA.RN_ADDRESS = p_RN_ADDRESS ,
SOA.RN_SUBSYSTEM = p_RN_SUBSYSTEM ,
SOA.PREORDER_AUTHORIZATION_CODE = p_PREORDER_AUTHORIZATION_CODE ,
SOA.ACTIVATION_DUE_DATE = p_ACTIVATION_DUE_DATE ,
SOA.LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
SOA.LAST_UPDATE_DATE = SYSDATE ,
SOA.MODIFIED_DATE = SYSDATE
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_REC_PORT_ORDER');
END SOA_UPDATE_REC_PORT_ORDER;
PROCEDURE SOA_UPDATE_DON_PORT_ORDER
(p_PORTING_ID VARCHAR2
,p_STARTING_NUMBER NUMBER
,p_ENDING_NUMBER NUMBER
,p_DONOR_SP_ID NUMBER DEFAULT NULL
,p_RECIPIENT_SP_ID NUMBER
,p_OLD_SP_DUE_DATE DATE
,p_OLD_SP_CUTOFF_DUE_DATE DATE
,p_CUSTOMER_ID VARCHAR2
,p_CUSTOMER_NAME VARCHAR2
,p_CUSTOMER_TYPE VARCHAR2
,p_ADDRESS_LINE1 VARCHAR2
,p_ADDRESS_LINE2 VARCHAR2
,p_CITY VARCHAR2
,p_PHONE VARCHAR2
,p_FAX VARCHAR2
,p_EMAIL VARCHAR2
,p_PAGER VARCHAR2
,p_PAGER_PIN VARCHAR2
,p_INTERNET_ADDRESS VARCHAR2
,p_ZIP_CODE VARCHAR2
,p_COUNTRY VARCHAR2
,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
,p_CONTACT_NAME VARCHAR2
,p_RETAIN_TN_FLAG VARCHAR2
,p_RETAIN_DIR_INFO_FLAG VARCHAR2
,p_CNAM_ADDRESS VARCHAR2
,p_CNAM_SUBSYSTEM VARCHAR2
,p_ISVM_ADDRESS VARCHAR2
,p_ISVM_SUBSYSTEM VARCHAR2
,p_LIDB_ADDRESS VARCHAR2
,p_LIDB_SUBSYSTEM VARCHAR2
,p_CLASS_ADDRESS VARCHAR2
,p_CLASS_SUBSYSTEM VARCHAR2
,p_WSMSC_ADDRESS VARCHAR2
,p_WSMSC_SUBSYSTEM VARCHAR2
,p_RN_ADDRESS VARCHAR2
,p_RN_SUBSYSTEM VARCHAR2
,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
,p_ACTIVATION_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET SOA.CHANGED_BY_SP_ID = p_DONOR_SP_ID ,
SOA.OLD_SP_CUTOFF_DUE_DATE = p_OLD_SP_CUTOFF_DUE_DATE ,
SOA.CUSTOMER_ID = p_CUSTOMER_ID ,
SOA.CUSTOMER_NAME = p_CUSTOMER_NAME ,
SOA.CUSTOMER_TYPE = p_CUSTOMER_TYPE ,
SOA.ADDRESS_LINE1 = p_ADDRESS_LINE1 ,
SOA.ADDRESS_LINE2 = p_ADDRESS_LINE2 ,
SOA.CITY = p_CITY ,
SOA.PHONE = p_PHONE ,
SOA.FAX = p_FAX ,
SOA.EMAIL = p_EMAIL ,
SOA.ZIP_CODE = p_ZIP_CODE ,
SOA.COUNTRY = p_COUNTRY ,
SOA.OLD_SP_DUE_DATE = p_OLD_SP_DUE_DATE ,
SOA.CUSTOMER_CONTACT_REQ_FLAG = p_CUSTOMER_CONTACT_REQ_FLAG ,
SOA.CONTACT_NAME = p_CONTACT_NAME ,
SOA.RETAIN_TN_FLAG = p_RETAIN_TN_FLAG ,
SOA.RETAIN_DIR_INFO_FLAG = p_RETAIN_DIR_INFO_FLAG ,
SOA.PAGER = p_PAGER ,
SOA.PAGER_PIN = p_PAGER_PIN ,
SOA.INTERNET_ADDRESS = p_INTERNET_ADDRESS ,
SOA.CNAM_ADDRESS = p_CNAM_ADDRESS ,
SOA.CNAM_SUBSYSTEM = p_CNAM_SUBSYSTEM ,
SOA.ISVM_ADDRESS = p_ISVM_ADDRESS ,
SOA.ISVM_SUBSYSTEM = p_ISVM_SUBSYSTEM ,
SOA.LIDB_ADDRESS = p_LIDB_ADDRESS ,
SOA.LIDB_SUBSYSTEM = p_LIDB_SUBSYSTEM ,
SOA.CLASS_ADDRESS = p_CLASS_ADDRESS ,
SOA.CLASS_SUBSYSTEM = p_CLASS_SUBSYSTEM ,
SOA.WSMSC_ADDRESS = p_WSMSC_ADDRESS ,
SOA.WSMSC_SUBSYSTEM = p_WSMSC_SUBSYSTEM ,
SOA.RN_ADDRESS = p_RN_ADDRESS ,
SOA.RN_SUBSYSTEM = p_RN_SUBSYSTEM ,
SOA.PREORDER_AUTHORIZATION_CODE = p_PREORDER_AUTHORIZATION_CODE ,
SOA.ACTIVATION_DUE_DATE = p_ACTIVATION_DUE_DATE ,
SOA.LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
SOA.LAST_UPDATE_DATE = SYSDATE,
SOA.MODIFIED_DATE = SYSDATE
WHERE SOA.SV_SOA_ID = L_SV_SOA_ID(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_DON_PORT_ORDER');
END SOA_UPDATE_DON_PORT_ORDER;
SELECT sv_sms_id BULK COLLECT
INTO l_sv_sms_id
FROM xnp_sv_sms sms
WHERE object_reference = p_porting_id ;
UPDATE XNP_SV_SMS SMS
SET SMS.PROVISION_SENT_DATE = P_PORTING_TIME ,
SMS.ROUTING_NUMBER_ID = p_ROUTING_NUMBER_ID ,
SMS.CNAM_ADDRESS = p_CNAM_ADDRESS ,
SMS.CNAM_SUBSYSTEM = p_CNAM_SUBSYSTEM ,
SMS.ISVM_ADDRESS = p_ISVM_ADDRESS ,
SMS.ISVM_SUBSYSTEM = p_ISVM_SUBSYSTEM ,
SMS.LIDB_ADDRESS = p_LIDB_ADDRESS ,
SMS.LIDB_SUBSYSTEM = p_LIDB_SUBSYSTEM ,
SMS.CLASS_ADDRESS = p_CLASS_ADDRESS ,
SMS.CLASS_SUBSYSTEM = p_CLASS_SUBSYSTEM ,
SMS.WSMSC_ADDRESS = p_WSMSC_ADDRESS ,
SMS.WSMSC_SUBSYSTEM = p_WSMSC_SUBSYSTEM ,
SMS.RN_ADDRESS = p_RN_ADDRESS ,
SMS.RN_SUBSYSTEM = p_RN_SUBSYSTEM ,
SMS.LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
SMS.LAST_UPDATE_DATE = SYSDATE
WHERE SMS.SV_SMS_ID = L_SV_SMS_ID(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_sms_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_sms_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
SELECT ported_indicator
FROM xnp_number_ranges
WHERE starting_number <= p_starting_number
AND ending_number >= p_ending_number;
PROCEDURE SOA_UPDATE_OLD_SP_DUE_DATE
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_OLD_SP_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
e_SOA_UPDATE_OLD_SP_DUE_DATE exception;
raise e_SOA_UPDATE_OLD_SP_DUE_DATE;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.old_sp_due_date = p_old_sp_due_date ,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
WHEN e_SOA_UPDATE_OLD_SP_DUE_DATE THEN
x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_OLD_SP_DUE_DATE');
END SOA_UPDATE_OLD_SP_DUE_DATE;
PROCEDURE SOA_UPDATE_NEW_SP_DUE_DATE
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_NEW_SP_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
e_SOA_UPDATE_NEW_SP_DUE_DATE exception;
raise e_SOA_UPDATE_NEW_SP_DUE_DATE;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.new_sp_due_date = p_new_sp_due_date,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
WHEN e_SOA_UPDATE_NEW_SP_DUE_DATE THEN
x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_NEW_SP_DUE_DATE');
END SOA_UPDATE_NEW_SP_DUE_DATE;
SELECT sv_soa_id
FROM xnp_sv_soa SOA
WHERE status_type_code = p_status_type_code
AND SOA.donor_sp_id = p_donor_sp_id
AND ( (to_number(subscription_tn) >= to_number(p_starting_number))
AND (to_number(subscription_tn) <= to_number(p_ending_number)) ) ;
SELECT sv_soa_id
FROM xnp_sv_soa XSO
WHERE status_type_code = p_status_type_code
AND XSO.recipient_sp_id = p_recipient_sp_id
AND to_number(subscription_tn) BETWEEN to_number(p_starting_number) AND to_number(p_ending_number);
PROCEDURE SOA_UPDATE_CUTOFF_DATE
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_OLD_SP_CUTOFF_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.old_sp_cutoff_due_date = XNP_UTILS.CANONICAL_TO_DATE(p_old_sp_cutoff_due_date) ,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_CUTOFF_DATE');
END SOA_UPDATE_CUTOFF_DATE;
PROCEDURE SOA_UPDATE_CHARGING_INFO
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_INVOICE_DUE_DATE DATE
,p_CHARGING_INFO VARCHAR2
,p_BILLING_ID NUMBER
,p_USER_LOCTN_VALUE VARCHAR2
,p_USER_LOCTN_TYPE VARCHAR2
,p_PRICE_CODE VARCHAR2
,p_PRICE_PER_CALL VARCHAR2
,p_PRICE_PER_MINUTE VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference=p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.invoice_due_date = p_invoice_due_date,
soa.charging_info = p_charging_info ,
soa.user_loctn_type = p_user_loctn_type,
soa.user_loctn_value = p_user_loctn_value,
soa.price_code = p_price_code,
soa.price_per_call = p_price_per_call,
soa.price_per_minute = p_price_per_minute,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_CHARGING_INFO');
END SOA_UPDATE_CHARGING_INFO;
SELECT sv_soa_id
FROM xnp_sv_soa SOA
WHERE SOA.object_reference = p_porting_id
AND SOA.status_type_code = p_status_type_code;
PROCEDURE SOA_UPDATE_OLD_SP_AUTH_FLAG
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_OLD_SP_AUTHORIZATION_FLAG VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.old_sp_authorization_flag = p_old_sp_authorization_flag,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_OLD_SP_AUTH_FLAG');
END SOA_UPDATE_OLD_SP_AUTH_FLAG;
PROCEDURE SOA_UPDATE_NEW_SP_AUTH_FLAG
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_NEW_SP_AUTHORIZATION_FLAG VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.new_sp_authorization_flag = p_new_sp_authorization_flag ,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_NEW_SP_AUTH_FLAG');
END SOA_UPDATE_NEW_SP_AUTH_FLAG;
SELECT SNR.fe_id
FROM XNP_SERVED_NUM_RANGES SNR, XDP_FES FES
WHERE SNR.feature_type = p_feature_type
AND SNR.number_range_id = l_number_range_id
AND SNR.fe_id = FES.fe_id
AND (sysdate BETWEEN FES.valid_date AND NVL(FES.invalid_date, sysdate));
XNP_CORE.SMS_INSERT_FE_MAP
(p_ORDER_ID,
p_LINEITEM_ID,
p_WORKITEM_INSTANCE_ID,
p_FA_INSTANCE_ID,
to_number(p_STARTING_NUMBER),
to_number(p_ENDING_NUMBER),
l_TMP_FE.FE_ID,
p_FEATURE_TYPE,
x_ERROR_CODE,
x_ERROR_MESSAGE
);
SELECT sv_sms_id BULK COLLECT
INTO l_sv_sms_id
FROM xnp_sv_sms
WHERE subscription_tn BETWEEN l_starting_number AND l_ending_number;
UPDATE xnp_sv_sms_fe_maps
SET provision_status = p_deprovision_status ,
last_updated_by = fnd_global.user_id ,
last_update_date = sysdate
WHERE feature_type = p_feature_type
AND sv_sms_id = l_sv_sms_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_sms_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_sms_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.locked_flag = p_locked_flag,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
SELECT locked_flag
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
SELECT status_type_code
FROM xnp_sv_soa soa
WHERE soa.object_reference=p_porting_id;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.blocked_flag = p_blocked_flag,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
SELECT blocked_flag
FROM xnp_sv_soa
WHERE object_reference = p_porting_id;
SELECT new_sp_authorization_flag
FROM xnp_sv_soa
WHERE object_reference = p_porting_id;
SELECT old_sp_authorization_flag
FROM xnp_sv_soa
WHERE object_reference = p_porting_id;
PROCEDURE SOA_UPDATE_ACTIVATION_DUE_DATE
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_ACTIVATION_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.activation_due_date = p_activation_due_date ,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_ACTIVATION_DUE_DATE');
END SOA_UPDATE_ACTIVATION_DUE_DATE;
SELECT DISTINCT sp_id, rownum
FROM xnp_routing_numbers rn ,
xnp_sv_sms sms
WHERE rn.routing_number_id = sms.routing_number_id
AND sms.subscription_tn BETWEEN l_starting_number AND l_ending_number;
PROCEDURE SOA_UPDATE_NOTES_INFO
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_COMMENTS VARCHAR2
,p_NOTES VARCHAR2
,p_PREORDER_AUTHORIZATION_CODE VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.comments = p_comments ,
soa.notes = p_notes ,
soa.preorder_authorization_code = p_preorder_authorization_code,
soa.modified_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_notes_info');
fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_notes_info');
END SOA_UPDATE_NOTES_INFO;
PROCEDURE SOA_UPDATE_NETWORK_INFO
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_ROUTING_NUMBER_ID NUMBER
,p_CNAM_ADDRESS VARCHAR2
,p_CNAM_SUBSYSTEM VARCHAR2
,p_ISVM_ADDRESS VARCHAR2
,p_ISVM_SUBSYSTEM VARCHAR2
,p_LIDB_ADDRESS VARCHAR2
,p_LIDB_SUBSYSTEM VARCHAR2
,p_CLASS_ADDRESS VARCHAR2
,p_CLASS_SUBSYSTEM VARCHAR2
,p_WSMSC_ADDRESS VARCHAR2
,p_WSMSC_SUBSYSTEM VARCHAR2
,p_RN_ADDRESS VARCHAR2
,p_RN_SUBSYSTEM VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.routing_number_id = p_routing_number_id ,
soa.cnam_address = p_cnam_address ,
soa.cnam_subsystem = p_cnam_subsystem ,
soa.isvm_address = p_isvm_address ,
soa.isvm_subsystem = p_isvm_subsystem ,
soa.lidb_address = p_lidb_address ,
soa.lidb_subsystem = p_lidb_subsystem ,
soa.class_address = p_class_address ,
soa.class_subsystem = p_class_subsystem ,
soa.wsmsc_address = p_wsmsc_address ,
soa.wsmsc_subsystem = p_wsmsc_subsystem ,
soa.rn_address = p_rn_address ,
soa.rn_subsystem = p_rn_subsystem ,
soa.modified_date = sysdate ,
soa.last_updated_by = fnd_global.user_id ,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_network_info');
fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_network_info');
END SOA_UPDATE_NETWORK_INFO;
PROCEDURE SOA_UPDATE_CUSTOMER_INFO
(p_PORTING_ID VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_CUSTOMER_ID VARCHAR2
,p_CUSTOMER_NAME VARCHAR2
,p_CUSTOMER_TYPE VARCHAR2
,p_ADDRESS_LINE1 VARCHAR2
,p_ADDRESS_LINE2 VARCHAR2
,p_CITY VARCHAR2
,p_PHONE VARCHAR2
,p_FAX VARCHAR2
,p_EMAIL VARCHAR2
,p_PAGER VARCHAR2
,p_PAGER_PIN VARCHAR2
,p_INTERNET_ADDRESS VARCHAR2
,p_ZIP_CODE VARCHAR2
,p_COUNTRY VARCHAR2
,p_CUSTOMER_CONTACT_REQ_FLAG VARCHAR2
,p_CONTACT_NAME VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.customer_id = p_customer_id ,
soa.customer_name = p_customer_name ,
soa.customer_type = p_customer_type ,
soa.address_line1 = p_address_line1 ,
soa.address_line2 = p_address_line2 ,
soa.city = p_city ,
soa.phone = p_phone ,
soa.fax = p_fax ,
soa.email = p_email ,
soa.zip_code = p_zip_code ,
soa.country = p_country ,
soa.customer_contact_req_flag = p_customer_contact_req_flag ,
soa.contact_name = p_contact_name ,
soa.pager = p_pager ,
soa.pager_pin = p_pager_pin ,
soa.internet_address = p_internet_address ,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
,'xnp_core.soa_update_customer_info');
fnd_message.set_token('ERROR_LOCN','xnp_core.soa_update_customer_info');
END SOA_UPDATE_CUSTOMER_INFO;
PROCEDURE SOA_UPDATE_PORTING_ID
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_CUR_STATUS_TYPE_CODE VARCHAR2
,p_LOCAL_SP_ID NUMBER DEFAULT NULL
,p_PORTING_ID VARCHAR2
,p_ORDER_ID NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
l_SV_ID number :=0;
e_UPDATE_PORTING_ID exception;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa,
xnp_sv_status_types_b sta
WHERE soa.subscription_tn BETWEEN l_starting_number AND l_ending_number
AND sta.phase_indicator = l_phase_indicator
AND sta.status_type_code = soa.status_type_code;
UPDATE xnp_sv_soa soa
SET soa.object_reference = p_porting_id,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
RAISE e_UPDATE_PORTING_ID;
fnd_message.set_token('FAILED_PROC','XNP_CORE.SOA_UPDATE_PORTING_ID');
,'XNP_CORE.SOA_UPDATE_PORTING_ID');
END SOA_UPDATE_PORTING_ID;
PROCEDURE SMS_UPDATE_PROV_DONE_DATE
(p_STARTING_NUMBER VARCHAR2
,p_ENDING_NUMBER VARCHAR2
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_sms_id_tab IS TABLE OF NUMBER;
SELECT sv_sms_id BULK COLLECT
INTO l_sv_sms_id
FROM xnp_sv_sms sms
WHERE sms.subscription_tn BETWEEN p_starting_number AND p_ending_number ;
UPDATE xnp_sv_sms sms
SET sms.provision_done_date = sysdate,
sms.last_updated_by = fnd_global.user_id,
sms.last_update_date = sysdate
WHERE sms.sv_sms_id = l_sv_sms_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_sms_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_sms_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
END SMS_UPDATE_PROV_DONE_DATE;
SELECT '1'
FROM XNP_ROUTING_NUMBERS
WHERE routing_number_id = p_ROUTING_NUMBER_ID
AND sp_id = p_RECIPIENT_SP_ID
AND active_flag='Y';
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
sv_sms_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
p_sv_soa_id ,
p_sv_sms_id ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
PROCEDURE SOA_UPDATE_DISCONN_DUE_DATE
(p_PORTING_ID VARCHAR2
,p_DISCONNECT_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
e_SOA_UPDATE_DISCON_DUE_DATE exception;
raise e_SOA_UPDATE_DISCON_DUE_DATE;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.disconnect_due_date = p_disconnect_due_date,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
WHEN e_SOA_UPDATE_DISCON_DUE_DATE THEN
x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_DISCONNECT_DUE_DATE');
END SOA_UPDATE_DISCONN_DUE_DATE;
PROCEDURE SOA_UPDATE_EFFECT_REL_DUE_DATE
(p_PORTING_ID VARCHAR2
,p_EFFECTIVE_RELEASE_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
e_SOA_UPDATE_EFF_REL_DUE_DATE exception;
raise e_SOA_UPDATE_EFF_REL_DUE_DATE;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.effective_release_due_date = p_effective_release_due_date,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
WHEN e_SOA_UPDATE_EFF_REL_DUE_DATE THEN
x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
fnd_message.set_token('ERROR_LOCN','XNP_CORE.SOA_UPDATE_EFFECT_REL_DUE_DATE');
END SOA_UPDATE_EFFECT_REL_DUE_DATE;
PROCEDURE SOA_UPDATE_NUM_RETURN_DUE_DATE
(p_PORTING_ID VARCHAR2
,p_NUMBER_RETURNED_DUE_DATE DATE
,p_ORDER_ID IN NUMBER
,p_LINEITEM_ID IN NUMBER
,p_WORKITEM_INSTANCE_ID IN NUMBER
,p_FA_INSTANCE_ID IN NUMBER
,x_ERROR_CODE OUT NOCOPY NUMBER
,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2
)
IS
TYPE sv_soa_id_tab IS TABLE OF NUMBER;
e_SOA_UPDATE_NUM_RET_DUE_DATE exception;
raise e_SOA_UPDATE_NUM_RET_DUE_DATE;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.NUMBER_RETURNED_DUE_DATE = p_NUMBER_RETURNED_DUE_DATE,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
WHEN e_SOA_UPDATE_NUM_RET_DUE_DATE THEN
x_error_code := XNP_ERRORS.G_INVALID_DATE_FORMAT;
END SOA_UPDATE_NUM_RETURN_DUE_DATE;
SELECT sv_soa_id BULK COLLECT
INTO l_sv_soa_id
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;
UPDATE xnp_sv_soa soa
SET soa.concurrence_flag = p_concurrence_flag,
soa.modified_date = sysdate,
soa.last_updated_by = fnd_global.user_id,
soa.last_update_date = sysdate
WHERE soa.sv_soa_id = l_sv_soa_id(i);
INSERT INTO XNP_SV_ORDER_MAPPINGS
(sv_order_mapping_id ,
sv_soa_id ,
order_id ,
workitem_instance_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES
(XNP_SV_ORDER_MAPPINGS_S.nextval,
l_sv_soa_id(i) ,
p_order_id ,
p_workitem_instance_id ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate
);
SELECT concurrence_flag
FROM xnp_sv_soa soa
WHERE soa.object_reference = p_porting_id;