0 Replies Latest reply on Dec 29, 2019 12:23 PM by 4009777

    Consume SOAP Request Through PLSQL

    4009777

      Hi ,

      we're integrating with third party through consuming their web services to create contracts through using SOAP Request Through PLSQL .

      All are working fine the only thing i need to know what is the syntax logic if i want to pass multi values in-between the XML Tags as the example below

       

      PROCEDURE  XXGPA_PN_CREATEPNDNGEJCONT_PRC (P_LEASE_ID NUMBER )

                IS  

           

            BEGIN

             

              DBMS_OUTPUT.PUT_LINE('call procedure   ');

                    

              UTL_HTTP.SET_WALLET('file:C:\testing');  

                    

              LV_REQ_NAME              := 'CreatePendingTenancyContract';

              LV_CREATION_DATE         := SYSDATE ;

             

                   

            FOR h IN ORCLCONT_HDRDET_C(P_LEASE_ID)

              LOOP   

                     

                      LV_PNAPPROVALREFNUMBER :='0620131205000360';---'0620161229001920';  

                      LV_PNREFERENCENUMBER   := h.PRE_LEASE_NUMBER ;--'201956565' ;      ---Oracle Pre-Lease Num

                      LV_CONT_AMNT           := h.TOT_LEASE_RNT ;

                      LV_CONT_ST_DATE        := TO_CHAR(h.LEASE_START_DATE,'YYYY-MM-DD')||'T09:00:00';

                      LV_CONT_END_DATE       := TO_CHAR(h.LEASE_END_DATE,'YYYY-MM-DD')||'T09:00:00';

                      LV_PAYMENT_CNT         := h.PAYMENT_CNT ;

                     ---LV_EJCONTR_NUM           := '0120190924004480' ;  ---Ejari Contract Num  

                    

                       DBMS_OUTPUT.PUT_LINE ('LV_PNAPPROVALREFNUMBER '   ||LV_PNAPPROVALREFNUMBER);

                        DBMS_OUTPUT.PUT_LINE ('LV_PNREFERENCENUMBER '   ||LV_PNREFERENCENUMBER);

                      

                      -----CreatePendingTenancyContract Webservice ----------

                     

                         LV_SOAP_REQ:= '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/" xmlns:ep4="http://schemas.datacontract.org/2004/07/EP4.Integration.Entities" xmlns:arr="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ep41="http://schemas.datacontract.org/2004/07/EP4.Integration.Entities.Common" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                                          <soapenv:Header/>

                                          <soapenv:Body>

                                             <tem:CreatePendingTenancyContract>

                                               <tem:TenancyContract>

                                                  <ep4:AmmountInformations xsi:nil="true"/>

                                                   <ep4:ContractAmount>'||LV_CONT_AMNT||'</ep4:ContractAmount>

                                                   <ep4:ContractEndDate>'||LV_CONT_END_DATE||'</ep4:ContractEndDate>

                                                   <ep4:ContractNumber xsi:nil="true"/>

                                                   <ep4:ContractStartDate>'||LV_CONT_ST_DATE||'</ep4:ContractStartDate>

                                                   <ep4:ContractStatus xsi:nil="true"/>

                                                   <ep4:DewaMoveInDate>'||LV_CONT_ST_DATE||'</ep4:DewaMoveInDate>                       

                                                   <ep4:OwnersInfo xsi:nil="true"/>

                                                   <ep4:PaymentCount>'||LV_PAYMENT_CNT||'</ep4:PaymentCount>

                                                   <ep4:PaymentsInformations xsi:nil="true"/>

                                                   <ep4:PropertyInformations attr0="RentInfoArray" isNull="false">'||

                                          

                                            FOR l IN ORCLCONT_LOCDET_C(P_LEASE_ID)

                                               LOOP ||'<ep4:PropertyDetailsInfoArray0>

                                                        <ep4:AnnualRent>1000</ep4:AnnualRent>

                                                        <ep4:ApprovalRefNumber>'||LV_PNAPPROVALREFNUMBER||'</ep4:ApprovalRefNumber>

                                                        <ep4:AreaName xsi:nil="true"/>

                                                        <ep4:BuildingName xsi:nil="true"/>

                                                        <ep4:BuiltArea>0</ep4:BuiltArea>

                                                        <ep4:DEWANumber xsi:nil="true"/>

                                                        <ep4:HoldType xsi:nil="true"/>

                                                        <ep4:IsManaged>false</ep4:IsManaged>

                                                        <ep4:IsRented>false</ep4:IsRented>

                                                        <ep4:LandNumber xsi:nil="true"/>

                                                        <ep4:LandSubNumber xsi:nil="true"/>

                                                        <ep4:Location xsi:nil="true"/>

                                                        <ep4:MCNumber xsi:nil="true"/>

                                                        <ep4:MunicipalityNumber xsi:nil="true"/>

                                                        <ep4:MunicipalitySubNumber xsi:nil="true"/>

                                                        <ep4:NoofFloors>0</ep4:NoofFloors>

                                                        <ep4:NumberOfRooms xsi:nil="true"/>

                                                        <ep4:OwnerNamesAr/>

                                                        <ep4:OwnerNamesEn/>

                                                        <ep4:OwnersInfo> <ep4:OwnerInfo xsi:nil="true"/> </ep4:OwnersInfo>

                                                        <ep4:PlotArea>0</ep4:PlotArea>

                                                        <ep4:PropertyType xsi:nil="true"/>

                                                        <ep4:PropertyTypeId>0</ep4:PropertyTypeId>

                                                        <ep4:Status xsi:nil="true"/>

                                                        <ep4:TCNumber xsi:nil="true"/>

                                                        <ep4:TenancyContract xsi:nil="true"/>

                                                        <ep4:TenantInformations xsi:nil="true"/>

                                                        <ep4:UnitNumber xsi:nil="true"/>

                                                        <ep4:UserRefNumber xsi:nil="true"/>

                                                        <ep4:VillaName xsi:nil="true"/>

                                                        <ep4:Zone xsi:nil="true"/>

                                                     </ep4:PropertyDetailsInfoArray0>'||

                                             --  END LOOP ;||

                                               '</ep4:PropertyInformations>

                                                  <ep4:PropertyType xsi:nil="true"/>

                                                  <ep4:ReferenceNumber>'||LV_PNREFERENCENUMBER||'</ep4:ReferenceNumber>

                                                  <ep4:RentInformations>

                                                     <ep4:RentInfo>

                                                        <ep4:ActualValue>12000</ep4:ActualValue>

                                                        <ep4:DiscountType>1</ep4:DiscountType>

                                                        <ep4:DiscountValue>0</ep4:DiscountValue>

                                                        <ep4:FromDate>2020-01-01T09:00:00</ep4:FromDate>

                                                        <ep4:ToDate>2021-12-31T09:00:00</ep4:ToDate>

                                                        <ep4:Value>12000</ep4:Value>

                                                     </ep4:RentInfo>

                                                   </ep4:RentInformations>

                                                  <ep4:SecurityDeposit>5000</ep4:SecurityDeposit>

                                                  <ep4:TenantInformations>

                                                     <ep4:TenantInfo>

                                                        <ep4:Address xsi:nil="true"/>

                                                        <ep4:ContactPersonMobile xsi:nil="true"/>

                                                        <ep4:ContactPersonName xsi:nil="true"/>

                                                        <ep4:Country xsi:nil="true"/>

                                                        <ep4:DateOfBirth>1979-02-10T09:00:00</ep4:DateOfBirth>

                                                        <ep4:Email>dummy_test@gmail.com</ep4:Email>

                                                        <ep4:Fax xsi:nil="true"/>

                                                        <ep4:InitialApprovalNumber xsi:nil="true"/>

                                                        <ep4:Input1 xsi:nil="true"/>

                                                        <ep4:Input2 xsi:nil="true"/>

                                                        <ep4:Input3 xsi:nil="true"/>

                                                        <ep4:IsPrimary>false</ep4:IsPrimary>

                                                        <ep4:LicenseExpiryDate xsi:nil="true"/>

                                                        <ep4:LicenseIssueDate xsi:nil="true"/>

                                                        <ep4:MobileNumber>0508598123</ep4:MobileNumber>

                                                        <ep4:NationalIDNumber>784197994929624</ep4:NationalIDNumber>

                                                        <ep4:POBox>51320</ep4:POBox>

                                                        <ep4:PassportExpiryDate xsi:nil="true"/>

                                                        <ep4:PassportIssueDate xsi:nil="true"/>

                                                        <ep4:PassportIssuePlace xsi:nil="true"/>

                                                        <ep4:PassportNo xsi:nil="true"/>

                                                        <ep4:PhoneNo xsi:nil="true"/>

                                                        <ep4:Sex>false</ep4:Sex>

                                                        <ep4:TenantName>SAAJITH HAYAL AHMED MOHIDEEN THAYAL</ep4:TenantName>

                                                        <ep4:TenantNameAr xsi:nil="true"/>

                                                        <ep4:TenantNumber xsi:nil="true"/>

                                                        <ep4:TenantSource xsi:nil="true"/>

                                                        <ep4:TenantType xsi:nil="true"/>

                                                        <ep4:TradeLicenseIssuer xsi:nil="true"/>

                                                        <ep4:TradeLicenseNumber xsi:nil="true"/>

                                                        <ep4:TradeLicenseState xsi:nil="true"/>

                                                        <ep4:UIDNumber xsi:nil="true"/>

                                                        <ep4:VATNumber xsi:nil="true"/>

                                                        <ep4:VisaExpiryDate xsi:nil="true"/>

                                                        <ep4:VisaNo xsi:nil="true"/>

                                                        <ep4:VisaStartDate xsi:nil="true"/>

                                                        <ep4:Webpage xsi:nil="true"/>

                                                     </ep4:TenantInfo>

                                                  </ep4:TenantInformations>

                                                  <ep4:TermsInformations xsi:nil="true"/>

                                                  <ep4:UsageInformations xsi:nil="true"/>

                                               </tem:TenancyContract>

                                               <tem:Credentials>

                                                  <ep41:Password>d2fz2te254b0d0d74bFa</ep41:Password>

                                                  <ep41:ResponseStatusList>

                                                     <ep4:ResponseStatus>

                                                        <ep4:ErrorMessage>ErrorMessage</ep4:ErrorMessage>

                                                        <ep4:ResponseType>Error</ep4:ResponseType>

                                                     </ep4:ResponseStatus>

                                                  </ep41:ResponseStatusList>

                                                  <ep41:UserName>ejari.amintegration</ep41:UserName>

                                                  <ep41:UserReferenceNumber xsi:nil="true"/>

                                               </tem:Credentials>

                                            </tem:CreatePendingTenancyContract>

                                         </soapenv:Body>

                                      </soapenv:Envelope>';

       

       

                          DBMS_OUTPUT.PUT_LINE ('SOAP REQUEST  call '   ||LV_SOAP_REQ);

                          

                                       

                          LV_HTTP_REQ:= UTL_HTTP.BEGIN_REQUEST('https://qa.dubailand.gov.ae/ejariintegrationv2/TenancyContractService.svc/basicHttpBinding' , 'POST', 'HTTP/1.1' );---'POST'

                          UTL_HTTP.SET_HEADER(LV_HTTP_REQ, 'Content-Type', 'text/xml; charset=UTF-8');

                          UTL_HTTP.SET_HEADER(LV_HTTP_REQ, 'Content-Encoding', 'gzip');   

                          UTL_HTTP.SET_HEADER(LV_HTTP_REQ, 'Content-Length', LENGTH(LV_SOAP_REQ)); 

                          UTL_HTTP.SET_HEADER(LV_HTTP_REQ, 'SOAPAction', 'http://tempuri.org/IExTenancyContractContract/CreatePendingTenancyContract');

                          UTL_HTTP.SET_HEADER(LV_HTTP_REQ, 'Download', ''); -- header requirements of particular web service

                          UTL_HTTP.WRITE_TEXT(LV_HTTP_REQ, LV_SOAP_REQ);

                         

                          LV_HTTP_RESP:= UTL_HTTP.GET_RESPONSE(LV_HTTP_REQ);

                       

                          UTL_HTTP.GET_HEADER_BY_NAME(LV_HTTP_RESP, 'Content-Length', LV_RESP_LEN, 1); -- Obtain the length of the response

                         

                          DBMS_OUTPUT.PUT_LINE ('LV_SOAP_REQ   '    ||LV_SOAP_REQ);

                          DBMS_OUTPUT.PUT_LINE ('Request Length   ' || LENGTH(LV_SOAP_REQ));

                          DBMS_OUTPUT.PUT_LINE ('Response Length  ' || LV_RESP_LEN);

                         

                  BEGIN    

                          

                       FOR I IN 1..CEIL(LV_RESP_LEN/32767) -- obtain response in 32K blocks just in case it is greater than 32K

                         LOOP

                           

                              UTL_HTTP.READ_TEXT(LV_HTTP_RESP, LV_RESP_TXT, CASE WHEN I < CEIL(LV_RESP_LEN/32767) THEN 32767 ELSE MOD(LV_RESP_LEN,32767) END);

                              

                              LV_SOAP_RESP := LV_SOAP_RESP || LV_RESP_TXT; -- build up CLOB

                  

                          

                           BEGIN

                          

                              LV_EJRESP_RSLTCODE  := xmltype( LV_SOAP_RESP ).extract('//'||LV_REQ_NAME||'Result/text()','xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" '  || 'xmlns="http://tempuri.org/').getStringVal();

                          

                               IF UPPER(LV_EJRESP_RSLTCODE) ='NOERRORS'

                                THEN

                                 LV_EJCONTR_NUM  := xmltype( LV_SOAP_RESP ).extract('//ContractNumber/text()','xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" '  || 'xmlns="http://tempuri.org/').getStringVal() ;

                                 LV_EJTENANT_NUM := xmltype( LV_SOAP_RESP ).extract('//TenantNumber/text()','xmlns:s="http://schemas.xmlsoap.org/soap/envelope/" '  || 'xmlns="http://tempuri.org/').getStringVal();

                               END IF ;

                          

                           EXCEPTION WHEN OTHERS

                            THEN

                               LV_REQ_ERROR_MSG  := 'Error in Reading the Request Response Columns';

                              

                           END;

                          

        

                           

                          END LOOP;

                         

                              -----End of CreatePendingTenancyContract Webservice ----------    

                     

                        UTL_HTTP.END_RESPONSE(LV_HTTP_RESP);

                        LV_XML_RESP:= XMLTYPE.CREATEXML(LV_SOAP_RESP); -- Convert Clob To Xmltype

                       

                       

                        INSERT INTO XXGPA_PN_EJARI_RESP_INT_LOG (WEBSERVICE_METHOD,SOAP_RESP,CREATION_DATE,XML_RESP,EJRESP_RSLTCODE,EJCONTR_NUM,EJTENANT_NUM,PNREFERENCENUMBER,PNAPPROVALREFNUMBER,REQ_ERROR_MSG) 

                                                         VALUES (LV_REQ_NAME,LV_SOAP_RESP,LV_CREATION_DATE,LV_XML_RESP,LV_EJRESP_RSLTCODE,LV_EJCONTR_NUM,LV_EJTENANT_NUM, LV_PNREFERENCENUMBER,LV_PNAPPROVALREFNUMBER,LV_REQ_ERROR_MSG);

                       

                        COMMIT;

                       

                     EXCEPTION WHEN OTHERS THEN

                   

                     UTL_HTTP.END_RESPONSE(LV_HTTP_RESP);

                    

                     END;

                         

                    END LOOP ;

                   

             EXCEPTION WHEN OTHERS THEN 

                LV_REQ_ERROR_MSG := ('Error code ' || SQLCODE || ': ' ||  SUBSTR(SQLERRM, 1, 64));

               

                INSERT INTO XXGPA_PN_EJARI_RESP_INT_LOG (WEBSERVICE_METHOD,SOAP_RESP,CREATION_DATE,XML_RESP,EJRESP_RSLTCODE,EJCONTR_NUM,EJTENANT_NUM,PNREFERENCENUMBER,PNAPPROVALREFNUMBER,REQ_ERROR_MSG) 

                                                         VALUES (LV_REQ_NAME,LV_SOAP_RESP,LV_CREATION_DATE,LV_XML_RESP,LV_EJRESP_RSLTCODE,LV_EJCONTR_NUM,LV_EJTENANT_NUM, LV_PNREFERENCENUMBER,LV_PNAPPROVALREFNUMBER,LV_REQ_ERROR_MSG);

                 COMMIT;

               

               END XXGPA_PN_CREATEPNDNGEJCONT_PRC;