--alter session set current_schema=banery;
/
--########## RUN SEPARATE 
create or replace FUNCTION sendSms
(
    pnumber IN VARCHAR2,
    message IN VARCHAR2,
	contry  IN VARCHAR2
)
  RETURN VARCHAR2
AS
    l_service  UTL_DBWS.service;
    l_call     UTL_DBWS.call;
    l_result   ANYDATA;

    l_wsdl_url         VARCHAR2(32767);
    l_namespace        VARCHAR2(32767);
    l_service_qname    UTL_DBWS.qname;
    l_port_qname       UTL_DBWS.qname;
    l_operation_qname  UTL_DBWS.qname;
    l_input_params     UTL_DBWS.anydata_list;
BEGIN
    l_wsdl_url        := 'http://www.zs.loc:81/MTSoap/?wsdl';
    l_namespace       := 'http://www.zs.loc:81/MTSoap/';

    l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'MTSoapService');
    l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'MTSoapPort');
    l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'sendSMSExt');

    l_service := UTL_DBWS.create_service (
    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
    service_name           => l_service_qname);

    l_call := UTL_DBWS.create_call (
    service_handle => l_service,
    port_name      => l_port_qname,
    operation_name => l_operation_qname);

    l_input_params(0) := ANYDATA.ConvertVarchar2(pnumber);
    l_input_params(1) := ANYDATA.ConvertVarchar2(message);
    l_input_params(2) := ANYDATA.ConvertVarchar2(contry);

    l_result := UTL_DBWS.invoke ( 
    call_handle  => l_call,
    input_params => l_input_params);

    UTL_DBWS.release_call (call_handle => l_call);
    UTL_DBWS.release_service (service_handle => l_service);

    RETURN ANYDATA.AccessVarchar2(l_result);
END;
/
--########## RUN SEPARATE 
CREATE OR REPLACE PROCEDURE proc_rs_getIDResANextSameReq
(
  idResArchValue IN RAW,
  idResPrevValue OUT VARCHAR2
)
AS
	archiveDateValue TIMESTAMP;
	requestNumberValue CHAR(32);	
	regularReservationId RAW(16);
	archiveIdReservationStatus RAW(16);
BEGIN

	SELECT archiveDate, requestNumber, idReservation, idReservationStatus 
	INTO archiveDateValue, requestNumberValue, regularReservationId, archiveIdReservationStatus
	FROM rs_reservationarchive 
	WHERE id = idResArchValue;
	
	idResPrevValue := '';
        
	SELECT RE.ID INTO idResPrevValue
	FROM
	( 
		SELECT ROW_NUMBER() OVER (ORDER BY R.archiveDate, R.id) AS RN, RAWTOHEX(R.id) ID
		FROM rs_reservationarchive R
		WHERE 
			--R.requestNumber = requestNumberValue AND
			R.idReservation = regularReservationId AND
			(
				(
					(R.idReservationStatus <> fx_rs_checkId_reservationStatu('id','name','RSOption')) AND 
					(archiveIdReservationStatus <> fx_rs_checkId_reservationStatu('id','name','RSOption'))
				) OR
				(idReservationStatus = archiveIdReservationStatus) OR
				(fx_rs_checkId_reservationStatu('id','name','RSOption') in  (SELECT idReservationStatus FROM rs_reservationArchive where idReservation = regularReservationId))
			) AND  
			(
				(
					R.id <> idResArchValue AND 
					R.archiveDate > archiveDateValue
				) OR
				(
					R.id > idResArchValue AND 
					R.archiveDate = archiveDateValue
				)
			)
		ORDER BY R.archiveDate, R.id
	) RE
	WHERE RE.RN = 1;
EXCEPTION
	WHEN NO_DATA_FOUND THEN 
		idResPrevValue := '';
END;
/