NOTLARIMDAN...: minerva

minerva etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster

11 Nisan 2014 Cuma

Minerva Prosedür ve Testi

DECLARE
P_GUID      VARCHAR2(30000) DEFAULT '';
P_FATNO     VARCHAR2(30000) DEFAULT '';
P_STATU     VARCHAR2(30000) DEFAULT '';
P_CLOB      CLOB;

BEGIN
fre_fatura_xml_gonder_new(P_MMA00_ID => 1094473,P_GUID => P_GUID,P_FATNO => P_FATNO,P_STATU => P_STATU,P_CLOB => P_CLOB);
--commit;
DBMS_OUTPUT.put_line('GUID  :' || P_GUID);
DBMS_OUTPUT.put_line('FATNO :' || P_FATNO);
DBMS_OUTPUT.put_line('STATUS:' || P_STATU);
DBMS_OUTPUT.put_line('CLOB  :' || P_CLOB);
END;

create or replace procedure FRE_FATURA_XML_GONDER_new(
P_MMA00_ID  IN NUMBER,
P_GUID      IN OUT VARCHAR2,
P_FATNO     IN OUT VARCHAR2,
P_STATU     IN OUT VARCHAR2,
P_CLOB      IN OUT CLOB
) IS
  X_CLOB        CLOB;
  XC_gonderici CLOB;
  XC_alici CLOB;
  XC_faturaToplam CLOB;
  XC_faturaBilgisi CLOB;  
XC_irsaliye CLOB;
XC_satirlar CLOB;
XC_siparisler CLOB;
XC_zcem CLOB;
XC_paramLogin CLOB;
XC_paramInvoice CLOB;
XC_satirIskonto  CLOB;
XC_satirVergi CLOB;

  X_VALUE       VARCHAR2(32767) DEFAULT '';
  req           utl_http.req;
  resp          utl_http.resp;
  name      VARCHAR2(256);
  soapAction VARCHAR2(1024);
  value     VARCHAR2(1024);
  buffer_size    NUMBER(10) := 512;
  substring_msg  VARCHAR2(512);
  raw_data RAW(512);
    
  RESPONSE      VARCHAR2(32000) DEFAULT '';
  l_envelope VARCHAR2(32767);

   L_BLOB          BLOB;
   L_DEST_OFFSET   INTEGER := 1;
   L_SOURCE_OFFSET INTEGER := 1;
   L_LANG_CONTEXT  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
   L_WARNING       INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;

   L_BUFFER    RAW(32767);
   L_AMOUNT    BINARY_INTEGER := 20000; --MUST BE SMALL REPLACE ('A','AB') ... BUYUDUGU ICIN
   L_POS       INTEGER := 1;
   L_LENGTH    INTEGER;

   R_FNM00       FNM00%ROWTYPE;
   R_FNM00_SVK   FNM00%ROWTYPE;
   R_MMA00       MMA00%ROWTYPE;
   R_YAA01       YAA01%ROWTYPE;
   R_YAA07       YAA07%ROWTYPE;
   R_YAA07_MRK   YAA07%ROWTYPE;

   P_USERNAME     VARCHAR2(50) DEFAULT ''; 
   P_PASSWORD     VARCHAR2(50) DEFAULT '';
   P_URL          VARCHAR2(50) DEFAULT '';
   X_SIRKET_VKN   VARCHAR2(50) DEFAULT '';
   V_BELGE_SON_NO NUMBER DEFAULT 1;
   X_DESPATCH_ID  VARCHAR2(50) DEFAULT '';
   X_DESPATCH_DATE  VARCHAR2(50) DEFAULT '';
   X_MALZEME_ADI    VARCHAR2(100) DEFAULT '';
   X_BIRIM          VARCHAR2(100) DEFAULT '';
   X_ISKONTOVAR     NUMBER DEFAULT 0;
   P_VAT_AMOUNT     NUMBER DEFAULT 0;
   CDUMMY           VARCHAR2(50) DEFAULT '';
   NDUM             NUMBER DEFAULT 0;
   X_GUID           VARCHAR2(100) DEFAULT '';
   X_FATNO          VARCHAR2(20) DEFAULT '';
   X_STATU          VARCHAR2(20) DEFAULT '';
   X_SENARYO        VARCHAR2(30) DEFAULT '';
   X_YAX02_CODE     VARCHAR2(10) DEFAULT '';
   
   CURSOR CBELGE(cYAA01 VARCHAR2, CYAA05 VARCHAR2) IS SELECT YAF01_LAST_NUMBER FROM PLEGOV.EFY01 WHERE YAA01_CODE = cYAA01 AND YAA05_CODE = cYAA05 AND YAF00_CODE = 'PG' FOR UPDATE;
   
BEGIN

   SELECT * INTO R_MMA00 FROM MMA00 WHERE MMA00_ID = P_MMA00_ID;
   SELECT * INTO R_YAA01 FROM YAA01 WHERE YAA01_CODE = R_MMA00.YAA01_CODE;
   SELECT * INTO R_YAA07 FROM YAA07 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND YAA05_CODE = R_MMA00.YAA05_CODE AND YAA07_CODE = R_MMA00.YAA07_CODE;
   
   -- SIRKET VKN SINI 000 ISYERI UZERINDEN ALIYORUM
   SELECT * INTO R_YAA07_MRK FROM YAA07 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND YAA05_CODE = R_MMA00.YAA05_CODE AND YAA07_CODE = '000';
   
   -- KULLANICI ADI VE KODUNU ADI FRE_EFATURA OLAN DIS SISTEM BILGISI UZERINDEN ALIYORUM
   SELECT EXZ00_HOST_USER, EXZ00_HOST_PASS, EXZ00_HOST INTO P_USERNAME, P_PASSWORD, P_URL FROM EXZ00 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND EXZ00_NAME = 'FRE_EFATURA';
   
   SELECT * INTO R_FNM00 FROM FNM00 WHERE FNM00_ID = R_MMA00.ACC_CODE_ID; 
   IF R_MMA00.ACC_ID_SEVK IS NOT NULL THEN
      SELECT * INTO R_FNM00_SVK FROM FNM00 WHERE FNM00_ID = R_MMA00.ACC_ID_SEVK; 
   ELSE
      R_FNM00_SVK := NULL;
   END IF;


-- Gönderen bilgileri
XC_gonderici := '';
XC_gonderici := XC_gonderici || '';
XC_gonderici := XC_gonderici || '';
XC_gonderici := XC_gonderici || ''||R_YAA07_MRK.YAA07_ADDRESS||'';

IF R_YAA07_MRK.YAA07_E_MAIL IS NOT NULL THEN
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_E_MAIL||'';
ELSE       
XC_gonderici := XC_gonderici ||'';
END IF;       
        
XC_gonderici := XC_gonderici ||'defaultpk';

IF R_YAA07_MRK.YAA07_FAX_1 IS NOT NULL THEN
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_FAX_1||'';
ELSE       
XC_gonderici := XC_gonderici ||'';
END IF;       
        
XC_gonderici := XC_gonderici ||'';
XC_gonderici := XC_gonderici ||'';
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_ZIP_CODE||'';
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAD04_CITY||'';

IF R_YAA07_MRK.YAA07_SEMT IS NOT NULL THEN
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_SEMT||'';
ELSE       
XC_gonderici := XC_gonderici ||'';
END IF;       

IF R_YAA07_MRK.YAA07_PHONE_1 IS NOT NULL THEN
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_PHONE_1||'';
ELSE       
XC_gonderici := XC_gonderici ||'';
END IF;       

XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAD03_COUNTRY||'';
XC_gonderici := XC_gonderici ||''||R_YAA01.YAA01_LEGALNAME||'';
--XC_gonderici := XC_gonderici ||'FRESENİUS ECZA DEPOSU TİCARET ANONİM ŞİRKETİ';
--XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_TAX_NO||'';
XC_gonderici := XC_gonderici ||'9000068418';--3880444538
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_TAX_DEPART||'';
IF R_YAA07_MRK.YAA07_INTERNET IS NOT NULL THEN
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_INTERNET||'';
ELSE
XC_gonderici := XC_gonderici ||'';
END IF;       
XC_gonderici := XC_gonderici ||'';


-- Alıcı Bilgileri
XC_alici := '';
XC_alici := XC_alici ||'';
XC_alici := XC_alici ||'';
XC_alici := XC_alici ||'' ||R_FNM00.FNM00_ADDRESS ||'';
      
IF R_FNM00.FNM00_EMAIL IS NOT NULL THEN
XC_alici := XC_alici ||'' ||R_FNM00.FNM00_EMAIL||'';
ELSE     
XC_alici := XC_alici ||'';
END IF;     
--XC_alici := XC_alici ||'defaultpk'; -- ?
XC_alici := XC_alici ||'defaultpk'; -- ?

IF R_FNM00.FNM00_FAX_1 IS NOT NULL THEN
XC_alici := XC_alici ||'' ||R_FNM00.FNM00_FAX_1  ||'';
ELSE
XC_alici := XC_alici||'';
END IF;

XC_alici := XC_alici||'';
XC_alici := XC_alici||'';

IF R_YAA07.YAA07_ZIP_CODE IS NOT NULL THEN
XC_alici := XC_alici||''||R_YAA07.YAA07_ZIP_CODE||'';
ELSE
XC_alici := XC_alici||'';
END IF;

XC_alici := XC_alici||''||R_FNM00.YAD04_CITY||'';

IF R_FNM00.FNM00_SEMT IS NOT NULL THEN
XC_alici := XC_alici||''||R_FNM00.FNM00_SEMT||'';
ELSE
XC_alici := XC_alici||'';
END IF;

IF R_YAA07.YAA07_PHONE_1 IS NOT NULL THEN
XC_alici := XC_alici||''||R_YAA07.YAA07_PHONE_1||'';
ELSE
XC_alici := XC_alici||'';
END IF;

XC_alici := XC_alici||''||R_FNM00.YAD03_COUNTRY||'';
XC_alici := XC_alici||''||R_FNM00.FNM00_TRADE_NAME||'';
--XC_alici := XC_alici||'İnci Ecza Deposu Tic. Ltd. Şti.';
--XC_alici := XC_alici||''||R_FNM00.FNM00_TAX_NO||'';
XC_alici := XC_alici||'1111111104';--6300388087

XC_alici := XC_alici||''||R_FNM00.FNM00_TAX_DEPART||'';

IF R_FNM00.FNM00_WEB IS NOT NULL THEN
XC_alici := XC_alici||''||R_FNM00.FNM00_WEB||'';
ELSE
XC_alici := XC_alici||'';
END IF;
XC_alici := XC_alici||'';





-- İrsaliye bilgisi saat bilgisine kadar
-- İrsaliye Bilgileri
XC_irsaliye := '';
   BEGIN
   SELECT IRS_NO,TO_CHAR(IRS_TAR, 'DD.MM.YYYY') INTO X_DESPATCH_ID, X_DESPATCH_DATE
      FROM( SELECT MMAV1.BELGE_ID_B AS IRS_NO, MMA00.MMA00_DATE AS IRS_TAR
      FROM PLE01.MMAV1, PLE01.MMA00
   WHERE MMAV1.BELGE_ID_A = TO_CHAR(R_MMA00.MMA00_ID)
         AND MMAV1.YAF00_CODE_A = 'PG'
         AND MMAV1.YAF00_CODE_B = 'PE'
         AND MMA00.YAA01_CODE = R_MMA00.YAA01_CODE
         AND MMAV1.BELGE_ID_B = TO_CHAR(MMA00.MMA00_ID)
   GROUP BY MMAV1.BELGE_ID_B, MMA00.MMA00_DATE,MMA00.MMA00_ID
   ORDER BY MMA00.MMA00_ID DESC ) WHERE ROWNUM=1;
   EXCEPTION WHEN OTHERS THEN
      X_DESPATCH_ID := NULL; 
      X_DESPATCH_DATE := NULL;
   END;
XC_irsaliye := XC_irsaliye || '' || X_DESPATCH_ID || '';
XC_irsaliye := XC_irsaliye || '' || TO_CHAR(X_DESPATCH_DATE,'YYYY-MM-DD') || '';
XC_irsaliye := XC_irsaliye || '';



-- Fatura Bilgileri
XC_faturaBilgisi := '';
X_GUID := UPPER(REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'));
XC_faturaBilgisi := XC_faturaBilgisi||'' || X_GUID || ''; 

OPEN CBELGE(R_MMA00.YAA01_CODE, R_MMA00.YAA05_CODE); FETCH CBELGE INTO V_BELGE_SON_NO; V_BELGE_SON_NO := V_BELGE_SON_NO + 1; 
IF V_BELGE_SON_NO IS NOT NULL THEN 
   UPDATE PLEGOV.EFY01 SET YAF01_LAST_NUMBER = V_BELGE_SON_NO WHERE CURRENT OF CBELGE;                  
END IF;
CLOSE CBELGE;
X_FATNO := 'TST' || R_MMA00.YAA05_CODE || TRIM(TO_CHAR(V_BELGE_SON_NO,'000000000'));
XC_faturaBilgisi := XC_faturaBilgisi || '' || X_FATNO || '';
XC_faturaBilgisi := XC_faturaBilgisi || '' || TO_CHAR(R_MMA00.MMA00_DATE,'YYYY-MM-DD') || '';

X_SENARYO := 'TICARIFATURA';
IF R_MMA00.EFA00_SENARYO = 'TEM' THEN X_SENARYO := 'TEMELFATURA'; END IF;
IF R_MMA00.EFA00_SENARYO = 'TIC' THEN X_SENARYO := 'TICARIFATURA'; END IF;
XC_faturaBilgisi := XC_faturaBilgisi || ''||X_SENARYO||'';
XC_faturaBilgisi := XC_faturaBilgisi || 'SATIS';

IF R_MMA00.MMA00_MUAF_REASON IS NOT NULL THEN
   XC_faturaBilgisi := XC_faturaBilgisi || '' || R_MMA00.MMA00_MUAF_REASON || '';
ELSE
    XC_faturaBilgisi := XC_faturaBilgisi || '';
END IF;

XC_faturaBilgisi := XC_faturaBilgisi || '';
XC_faturaBilgisi := XC_faturaBilgisi || 'TRL';
XC_faturaBilgisi := XC_faturaBilgisi || '' || TRIM(TO_CHAR(R_MMA00.GLA01_BKP_EXCHG,'99.9')) || '';

-- ************ Odeme Bilgisi cekilecek ****************
XC_faturaBilgisi := XC_faturaBilgisi || '';
XC_faturaBilgisi := XC_faturaBilgisi || '';




-- Faturanın Siparişleri
XC_siparisler := '';

FOR CR2 IN (SELECT DISTINCT BELGE_ID_B FROM MMAV1 WHERE YAF00_CODE_A = 'PG' AND BELGE_ID_A = P_MMA00_ID) LOOP

    FOR CR3 IN (SELECT SDB00_NUMBER, SDB00_DATE FROM SDB00 WHERE SDB00_ID = CR2.BELGE_ID_B) LOOP
              XC_siparisler := XC_siparisler || '';
              XC_siparisler := XC_siparisler || ''||CR3.SDB00_NUMBER||''; -- 2265495331
              XC_siparisler := XC_siparisler || ''||TO_CHAR(CR3.SDB00_DATE,'YYYY-MM-DD')||''; -- 2014-04-04
              XC_siparisler := XC_siparisler || '';
   END LOOP;
END LOOP;

XC_siparisler := XC_siparisler || '';



FOR CR IN (SELECT * FROM MMA03 WHERE MMA00_ID = P_MMA00_ID) LOOP
   SELECT MMM00_NAME INTO X_MALZEME_ADI FROM MMM00 WHERE YAA01_CODE =R_MMA00.YAA01_CODE AND MMM00_ITEM_CODE = CR.MMM00_ITEM_CODE AND MMY00_TYPE = CR.MMY00_TYPE;
   --SELECT * FROM FNX10 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND FNX10_RATE = CR.FNX10_CODE;
   -- STOK BIRIMI BOS ISE ADET OALCAK SEKILDE AL
   SELECT NVL(MMU60_EGOV_CODE,'NIU') INTO X_BIRIM FROM MMU60 WHERE YAA01_CODE =R_MMA00.YAA01_CODE AND MMU60_UOM = CR.MMU60_UOM_1;
   IF CR.DISC_SUM > 0 THEN 
      X_ISKONTOVAR := '1'; 
   ELSE 
      X_ISKONTOVAR := '0'; 
   END IF;

   IF CR.DISC_1 <> 0 OR CR.DISC_2 <> 0 OR CR.DISC_3 <> 0 OR CR.DISC_4 <> 0 OR CR.DISC_5 <> 0 THEN
      XC_satirIskonto := '';
      IF CR.DISC_1 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto1';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_1,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_1_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_2 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto2';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_2,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_2_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_3 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto3';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_3,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_3_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_4 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto4';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_4,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_4_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_5 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto5';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_5,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_5_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      XC_satirIskonto := XC_satirIskonto || '';      
   ELSE
      XC_satirIskonto := XC_satirIskonto || '';
   END IF;
   
   -- Satır Vergileri
   XC_satirVergi := '';
   XC_satirVergi := XC_satirVergi || '';   
   XC_satirVergi := XC_satirVergi || 'KDV';
   XC_satirVergi := XC_satirVergi || '0015';
   XC_satirVergi := XC_satirVergi || '' || CR.FNX10_RATE || '';
   XC_satirVergi := XC_satirVergi || '' || TRIM(TO_CHAR(CR.VAT_AMOUNT,'999999999.99')) || '';
   IF CR.MMA03_MUAF_REASON IS NOT NULL THEN
      XC_satirVergi := XC_satirVergi || '' || CR.MMA03_MUAF_REASON || '';
   ELSE
       XC_satirVergi := XC_satirVergi || '';
   END IF;
   XC_satirVergi := XC_satirVergi || '';
   XC_satirVergi := XC_satirVergi || '';

   XC_satirlar := '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '' || X_MALZEME_ADI || '';
   XC_satirlar := XC_satirlar || '' || NVL(X_BIRIM,'NIU') || '';
   XC_satirlar := XC_satirlar || '' || CR.QTY_1 || '';
   XC_satirlar := XC_satirlar || '' || TRIM(TO_CHAR(CR.PRICE,'999999999.99')) || '';
   XC_satirlar := XC_satirlar || 'TRL';
   -- ***** (ürün markası gelmeli)
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';      
   XC_satirlar := XC_satirlar || '';
   -- ***** (ürün kodumuz/sap kodumuz gelmeli)
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   -- birim fiyat * satır miktarı
   XC_satirlar := XC_satirlar || '' || TRIM(TO_CHAR(CR.AMOUNT_NET,'999999999.99')) || '';
   -- Satır İskontoları
   XC_satirlar := XC_satirlar || XC_satirIskonto;
   -- Vergiler
   XC_satirlar := XC_satirlar || XC_satirVergi;

   -- malzemeTutari : (toplamTutar - iskonto) * KDV
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   P_VAT_AMOUNT := P_VAT_AMOUNT + CR.VAT_AMOUNT;
END LOOP;
XC_satirlar := XC_satirlar || '';






-- Fatura ALT TOPLAM bilgileri
XC_faturaToplam := '';
--X_CLOB := X_CLOB||''||TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT,'999999999.99'))||'';
SELECT SUM(MMA03.AMOUNT) INTO NDUM FROM MMA03 WHERE MMA00_ID = R_MMA00.MMA00_ID;
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(NDUM,'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || ''; -- S1220140188036922 # e438a424-e08d-47da-9b9e-0cd2e5550e35
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT,'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '';
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(ROUND(P_VAT_AMOUNT,2),'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT - ROUND(P_VAT_AMOUNT,2),'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '';






XC_paramLogin := '<_invoicelogin xmlns:a="http://schemas.datacontract.org/2004/07/FMC.Turkiye.Lib.EInvoice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">';
XC_paramLogin := XC_paramLogin || 'Uyumsoft';--MRfLnfrq
XC_paramLogin := XC_paramLogin || 'Uyumsoft';--FreseniusEcza_WebServis
XC_paramLogin := XC_paramLogin || '';

XC_paramInvoice := '<_invoice xmlns:a="http://schemas.datacontract.org/2004/07/FMC.Turkiye.Lib.EInvoice.WebServiceClasses" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">';
XC_paramInvoice := XC_paramInvoice || XC_gonderici;
XC_paramInvoice := XC_paramInvoice || XC_alici;
XC_paramInvoice := XC_paramInvoice || XC_irsaliye;
XC_paramInvoice := XC_paramInvoice || XC_faturaBilgisi;
XC_paramInvoice := XC_paramInvoice || XC_siparisler;
XC_paramInvoice := XC_paramInvoice || XC_satirlar;
XC_paramInvoice := XC_paramInvoice || XC_faturaToplam;

XC_paramInvoice := XC_paramInvoice || '';

soapAction := 'http://gib.fresenius.com.tr/FEFatura/IFEInvoice/SendInvoiceFromMinerva';
X_CLOB := '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || XC_paramLogin;
X_CLOB := X_CLOB || XC_paramInvoice;
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';



/*
x_clob := replace(x_clob,'Ü','U');
x_clob := replace(x_clob,'ü','u');
x_clob := replace(x_clob,'Ğ','G');
x_clob := replace(x_clob,'ğ','g');
x_clob := replace(x_clob,'Ş','S');
x_clob := replace(x_clob,'ş','s');
x_clob := replace(x_clob,'İ','I');
x_clob := replace(x_clob,'ı','i');
x_clob := replace(x_clob,'Ç','C');
x_clob := replace(x_clob,'ç','c');
x_clob := replace(x_clob,'Ö','O');
x_clob := replace(x_clob,'ö','o');
*/
htp.p(x_clob);


DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);
DBMS_LOB.CONVERTTOBLOB(DEST_LOB => L_BLOB, 
                       SRC_CLOB => X_CLOB, 
                       AMOUNT => DBMS_LOB.LOBMAXSIZE, 
                       DEST_OFFSET => L_DEST_OFFSET, 
                       SRC_OFFSET => L_SOURCE_OFFSET, 
                       BLOB_CSID => NLS_CHARSET_ID('UTF8'), 
                       LANG_CONTEXT => L_LANG_CONTEXT, 
                       WARNING => L_WARNING);

dbms_output.put_line(LENGTHB(X_CLOB));
dbms_output.put_line(LENGTH(X_CLOB));

  L_LENGTH := DBMS_LOB.getlength(L_BLOB);
dbms_output.put_line(L_LENGTH);
  
--  req := UTL_HTTP.begin_request('http://10.130.214.205:8093/Efatura/FEInvoice.svc', 'POST', 'HTTP/1.1');
  req := UTL_HTTP.begin_request('http://10.130.214.205:8093/tEfatura/FEInvoice.svc', 'POST', 'HTTP/1.1');
  UTL_HTTP.set_header(req, 'Content-Type', 'text/xml');
  UTL_HTTP.set_header(req, 'Content-Length', L_LENGTH);
  UTL_HTTP.set_header(req, 'SOAPAction', soapAction);
  
  WHILE L_POS < L_LENGTH LOOP
      DBMS_LOB.READ(L_BLOB, L_AMOUNT, L_POS, L_BUFFER);
--      DBMS_OUTPUT.put_line(L_BUFFER);
      UTL_HTTP.write_raw(REQ,L_BUFFER);
      l_pos := l_pos + l_amount;      
  END LOOP;

  resp := UTL_HTTP.get_response(req);  
  UTL_HTTP.read_text(resp, l_envelope);
X_STATU := XML_PARSER(L_ENVELOPE,'');
dbms_output.put_line('cevap :'||substr(l_envelope,1,2000));
dbms_output.put_line('Durum :'||X_STATU);
dbms_output.put_line('Invoice Number :'||XML_PARSER(L_ENVELOPE,''));
dbms_output.put_line('Sonuc :'||XML_PARSER(L_ENVELOPE,''));
dbms_output.put_line('Succeed Transaction :'||XML_PARSER(L_ENVELOPE,''));

P_FATNO := X_FATNO;
P_GUID := X_GUID;
P_CLOB := X_CLOB;
P_STATU := X_STATU;
/*
    -- HEADER yazılıyor
    FOR i IN 1..utl_http.get_header_count(resp) LOOP
      utl_http.get_header(resp, i, name, value);
      dbms_output.put_line(name || ': ' || value);
    END LOOP;
*/
  
  UTL_HTTP.end_response(resp);
END;

create or replace procedure FRE_FATURA_XML_GONDER_new(
  P_MMA00_ID  IN NUMBER,
  P_GUID      IN OUT VARCHAR2,
  P_FATNO     IN OUT VARCHAR2,
  P_STATU     IN OUT VARCHAR2,
  P_CLOB      IN OUT CLOB
) IS
  X_CLOB        CLOB;
  XC_gonderici CLOB;
  XC_alici CLOB;
  XC_faturaToplam CLOB;
  XC_faturaBilgisi CLOB;  
  XC_irsaliye CLOB;
  XC_satirlar CLOB;
  XC_siparisler CLOB;
  XC_zcem CLOB;
  XC_paramLogin CLOB;
  XC_paramInvoice CLOB;
  XC_satirIskonto  CLOB;
  XC_satirVergi CLOB;




  X_VALUE       VARCHAR2(32767) DEFAULT '';
  req           utl_http.req;
  resp          utl_http.resp;
  name      VARCHAR2(256);
  soapAction VARCHAR2(1024);
  value     VARCHAR2(1024);
  buffer_size    NUMBER(10) := 512;
  substring_msg  VARCHAR2(512);
  raw_data RAW(512);
    
  RESPONSE      VARCHAR2(32000) DEFAULT '';
  l_envelope VARCHAR2(32767);

   L_BLOB          BLOB;
   L_DEST_OFFSET   INTEGER := 1;
   L_SOURCE_OFFSET INTEGER := 1;
   L_LANG_CONTEXT  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
   L_WARNING       INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;

   L_BUFFER    RAW(32767);
   L_AMOUNT    BINARY_INTEGER := 20000; --MUST BE SMALL REPLACE ('A','AB') ... BUYUDUGU ICIN
   L_POS       INTEGER := 1;
   L_LENGTH    INTEGER;

   R_FNM00       FNM00%ROWTYPE;
   R_FNM00_SVK   FNM00%ROWTYPE;
   R_MMA00       MMA00%ROWTYPE;
   R_YAA01       YAA01%ROWTYPE;
   R_YAA07       YAA07%ROWTYPE;
   R_YAA07_MRK   YAA07%ROWTYPE;

   P_USERNAME     VARCHAR2(50) DEFAULT ''; 
   P_PASSWORD     VARCHAR2(50) DEFAULT '';
   P_URL          VARCHAR2(50) DEFAULT '';
   X_SIRKET_VKN   VARCHAR2(50) DEFAULT '';
   V_BELGE_SON_NO NUMBER DEFAULT 1;
   X_DESPATCH_ID  VARCHAR2(50) DEFAULT '';
   X_DESPATCH_DATE  VARCHAR2(50) DEFAULT '';
   X_MALZEME_ADI    VARCHAR2(100) DEFAULT '';
   X_BIRIM          VARCHAR2(100) DEFAULT '';
   X_ISKONTOVAR     NUMBER DEFAULT 0;
   P_VAT_AMOUNT     NUMBER DEFAULT 0;
   CDUMMY           VARCHAR2(50) DEFAULT '';
   NDUM             NUMBER DEFAULT 0;
   X_GUID           VARCHAR2(100) DEFAULT '';
   X_FATNO          VARCHAR2(20) DEFAULT '';
   X_STATU          VARCHAR2(20) DEFAULT '';
   X_SENARYO        VARCHAR2(30) DEFAULT '';
   X_YAX02_CODE     VARCHAR2(10) DEFAULT '';
   
   CURSOR CBELGE(cYAA01 VARCHAR2, CYAA05 VARCHAR2) IS SELECT YAF01_LAST_NUMBER FROM PLEGOV.EFY01 WHERE YAA01_CODE = cYAA01 AND YAA05_CODE = cYAA05 AND YAF00_CODE = 'PG' FOR UPDATE;
   -- Cem değişkenler
  servisAdresi VARCHAR(255);
  -- Dosyaya yazma
  dirEFaturalar varchar2(100) default 'd:\EFaturalar';
  
  fileEfatura UTL_FILE.FILE_TYPE; 
  len_efaturaBuffer  VARCHAR2(32767);
  len_efaturaAmount  BINARY_INTEGER := 32767;
  len_efaturaCopyPos     INTEGER := 1;
-- // Cem değişkenler


BEGIN

   SELECT * INTO R_MMA00 FROM MMA00 WHERE MMA00_ID = P_MMA00_ID;
   SELECT * INTO R_YAA01 FROM YAA01 WHERE YAA01_CODE = R_MMA00.YAA01_CODE;
   SELECT * INTO R_YAA07 FROM YAA07 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND YAA05_CODE = R_MMA00.YAA05_CODE AND YAA07_CODE = R_MMA00.YAA07_CODE;
   
   -- SIRKET VKN SINI 000 ISYERI UZERINDEN ALIYORUM
   SELECT * INTO R_YAA07_MRK FROM YAA07 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND YAA05_CODE = R_MMA00.YAA05_CODE AND YAA07_CODE = '000';
   
   -- KULLANICI ADI VE KODUNU ADI FRE_EFATURA OLAN DIS SISTEM BILGISI UZERINDEN ALIYORUM
   SELECT EXZ00_HOST_USER, EXZ00_HOST_PASS, EXZ00_HOST INTO P_USERNAME, P_PASSWORD, P_URL FROM EXZ00 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND EXZ00_NAME = 'FRE_EFATURA';
   
   SELECT * INTO R_FNM00 FROM FNM00 WHERE FNM00_ID = R_MMA00.ACC_CODE_ID; 
   IF R_MMA00.ACC_ID_SEVK IS NOT NULL THEN
      SELECT * INTO R_FNM00_SVK FROM FNM00 WHERE FNM00_ID = R_MMA00.ACC_ID_SEVK; 
   ELSE
      R_FNM00_SVK := NULL;
   END IF;


-- Gönderen bilgileri
XC_gonderici := '';
XC_gonderici := XC_gonderici || '';
XC_gonderici := XC_gonderici || '';

IF R_YAA07_MRK.YAA07_ADDRESS IS NOT NULL THEN
XC_gonderici := XC_gonderici || '' || R_YAA07_MRK.YAA07_ADDRESS || '';
ELSE       
XC_gonderici := XC_gonderici ||'';
END IF;       
        


IF R_YAA07_MRK.YAA07_E_MAIL IS NOT NULL THEN
XC_gonderici := XC_gonderici ||'' || R_YAA07_MRK.YAA07_E_MAIL || '';
ELSE       
XC_gonderici := XC_gonderici ||'';
END IF;       
        
XC_gonderici := XC_gonderici ||'defaultpk';

IF R_YAA07_MRK.YAA07_FAX_1 IS NOT NULL THEN
  XC_gonderici := XC_gonderici ||''|| R_YAA07_MRK.YAA07_FAX_1 || '';
ELSE       
  XC_gonderici := XC_gonderici ||'';
END IF;       
        
XC_gonderici := XC_gonderici ||'';
XC_gonderici := XC_gonderici ||'';

IF R_YAA07_MRK.YAA07_ZIP_CODE IS NOT NULL THEN
  XC_gonderici := XC_gonderici || '' || R_YAA07_MRK.YAA07_ZIP_CODE ||'';
ELSE       
  XC_gonderici := XC_gonderici || '';
END IF;       





IF R_YAA07_MRK.YAD04_CITY IS NOT NULL THEN
  XC_gonderici := XC_gonderici || '' || R_YAA07_MRK.YAD04_CITY || '';
ELSE       
  XC_gonderici := XC_gonderici ||'';
END IF;       

IF R_YAA07_MRK.YAA07_SEMT IS NOT NULL THEN
  XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_SEMT||'';
ELSE       
  XC_gonderici := XC_gonderici ||'';
END IF;       

IF R_YAA07_MRK.YAA07_PHONE_1 IS NOT NULL THEN
  XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_PHONE_1||'';
ELSE       
  XC_gonderici := XC_gonderici ||'';
END IF;       

IF R_YAA07_MRK.YAD03_COUNTRY IS NOT NULL THEN
  XC_gonderici := XC_gonderici || '' || R_YAA07_MRK.YAD03_COUNTRY || '';
ELSE       
  XC_gonderici := XC_gonderici || '';
END IF;       
     

IF R_YAA01.YAA01_LEGALNAME IS NOT NULL THEN
  XC_gonderici := XC_gonderici ||''|| R_YAA01.YAA01_LEGALNAME ||'';
ELSE       
  XC_gonderici := XC_gonderici ||'';
END IF;       



--XC_gonderici := XC_gonderici ||'FRESENİUS ECZA DEPOSU TİCARET ANONİM ŞİRKETİ';
--XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_TAX_NO||'';
XC_gonderici := XC_gonderici ||'9000068418';--3880444538
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_TAX_DEPART||'';
IF R_YAA07_MRK.YAA07_INTERNET IS NOT NULL THEN
XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_INTERNET||'';
ELSE
XC_gonderici := XC_gonderici ||'';
END IF;       
XC_gonderici := XC_gonderici ||'';


-- Alıcı Bilgileri
XC_alici := '';
XC_alici := XC_alici ||'';
XC_alici := XC_alici ||'';

IF R_FNM00.FNM00_ADDRESS IS NOT NULL THEN
  XC_alici := XC_alici ||'' || R_FNM00.FNM00_ADDRESS ||'';
ELSE     
  XC_alici := XC_alici ||'';
END IF;     

      
IF R_FNM00.FNM00_Email IS NOT NULL THEN
  XC_alici := XC_alici ||'' ||R_FNM00.FNM00_Email||'';
ELSE     
  XC_alici := XC_alici ||'';
END IF;     
--XC_alici := XC_alici ||'defaultpk'; -- ?
XC_alici := XC_alici ||'defaultpk'; -- ?

IF R_FNM00.FNM00_Fax_1 IS NOT NULL THEN
  XC_alici := XC_alici ||'' ||R_FNM00.FNM00_Fax_1  ||'';
ELSE
  XC_alici := XC_alici||'';
END IF;

XC_alici := XC_alici||'';
XC_alici := XC_alici||'';

IF R_YAA07.YAA07_ZIP_CODE IS NOT NULL THEN
  XC_alici := XC_alici||''||R_YAA07.YAA07_ZIP_CODE||'';
ELSE
  XC_alici := XC_alici||'';
END IF;

IF R_FNM00.YAD04_CITY IS NOT NULL THEN
  XC_alici := XC_alici||''||R_FNM00.YAD04_CITY||'';
ELSE
  XC_alici := XC_alici||'';
END IF;

IF R_FNM00.FNM00_SEMT IS NOT NULL THEN
  XC_alici := XC_alici||''||R_FNM00.FNM00_SEMT||'';
ELSE
  XC_alici := XC_alici||'';
END IF;

IF R_YAA07.YAA07_PHONE_1 IS NOT NULL THEN
  XC_alici := XC_alici||''||R_YAA07.YAA07_PHONE_1||'';
ELSE
  XC_alici := XC_alici||'';
END IF;

IF R_FNM00.YAD03_COUNTRY IS NOT NULL THEN
  XC_alici := XC_alici||'' || R_FNM00.YAD03_COUNTRY || '';
ELSE
  XC_alici := XC_alici||'';
END IF;

IF R_FNM00.FNM00_TRADE_NAME IS NOT NULL THEN
  XC_alici := XC_alici||'' || R_FNM00.FNM00_TRADE_NAME || '';
ELSE
  XC_alici := XC_alici||'';
END IF;

--XC_alici := XC_alici||'İnci Ecza Deposu Tic. Ltd. Şti.';
--XC_alici := XC_alici||''||R_FNM00.FNM00_TAX_NO||'';
XC_alici := XC_alici||'1111111104';--6300388087

XC_alici := XC_alici||''||R_FNM00.FNM00_TAX_DEPART||'';

IF R_FNM00.FNM00_WEB IS NOT NULL THEN
  XC_alici := XC_alici||''||R_FNM00.FNM00_WEB||'';
ELSE
  XC_alici := XC_alici||'';
END IF;

XC_alici := XC_alici||'';





-- İrsaliye bilgisi saat bilgisine kadar
-- İrsaliye Bilgileri
XC_irsaliye := '';
   BEGIN
   SELECT IRS_NO,TO_CHAR(IRS_TAR, 'DD.MM.YYYY') INTO X_DESPATCH_ID, X_DESPATCH_DATE
      FROM( SELECT MMAV1.BELGE_ID_B AS IRS_NO, MMA00.MMA00_DATE AS IRS_TAR
      FROM PLE01.MMAV1, PLE01.MMA00
   WHERE MMAV1.BELGE_ID_A = TO_CHAR(R_MMA00.MMA00_ID)
         AND MMAV1.YAF00_CODE_A = 'PG'
         AND MMAV1.YAF00_CODE_B = 'PE'
         AND MMA00.YAA01_CODE = R_MMA00.YAA01_CODE
         AND MMAV1.BELGE_ID_B = TO_CHAR(MMA00.MMA00_ID)
   GROUP BY MMAV1.BELGE_ID_B, MMA00.MMA00_DATE,MMA00.MMA00_ID
   ORDER BY MMA00.MMA00_ID DESC ) WHERE ROWNUM=1;
   EXCEPTION WHEN OTHERS THEN
      X_DESPATCH_ID := NULL; 
      X_DESPATCH_DATE := NULL;
   END;
XC_irsaliye := XC_irsaliye || '' || X_DESPATCH_ID || '';
-- 2014-12-23 22:34
XC_irsaliye := XC_irsaliye || '' || TO_CHAR(X_DESPATCH_DATE,'YYYY-MM-DD HH:MM') || '';
XC_irsaliye := XC_irsaliye || '';



-- Fatura Bilgileri
XC_faturaBilgisi := '';
X_GUID := UPPER(REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'));
XC_faturaBilgisi := XC_faturaBilgisi||'' || X_GUID || ''; 

OPEN CBELGE(R_MMA00.YAA01_CODE, R_MMA00.YAA05_CODE); FETCH CBELGE INTO V_BELGE_SON_NO; V_BELGE_SON_NO := V_BELGE_SON_NO + 1; 
IF V_BELGE_SON_NO IS NOT NULL THEN 
   UPDATE PLEGOV.EFY01 SET YAF01_LAST_NUMBER = V_BELGE_SON_NO WHERE CURRENT OF CBELGE;                  
END IF;
CLOSE CBELGE;
X_FATNO := 'TST' || R_MMA00.YAA05_CODE || TRIM(TO_CHAR(V_BELGE_SON_NO,'000000000'));
XC_faturaBilgisi := XC_faturaBilgisi || '' || X_FATNO || '';
XC_faturaBilgisi := XC_faturaBilgisi || '' || TO_CHAR(R_MMA00.MMA00_DATE,'YYYY-MM-DD') || '';

X_SENARYO := 'TICARIFATURA';
IF R_MMA00.EFA00_SENARYO = 'TEM' THEN X_SENARYO := 'TEMELFATURA'; END IF;
IF R_MMA00.EFA00_SENARYO = 'TIC' THEN X_SENARYO := 'TICARIFATURA'; END IF;
XC_faturaBilgisi := XC_faturaBilgisi || ''||X_SENARYO||'';
XC_faturaBilgisi := XC_faturaBilgisi || 'SATIS';

IF R_MMA00.MMA00_MUAF_REASON IS NOT NULL THEN
   XC_faturaBilgisi := XC_faturaBilgisi || '' || R_MMA00.MMA00_MUAF_REASON || '';
ELSE
    XC_faturaBilgisi := XC_faturaBilgisi || '';
END IF;

XC_faturaBilgisi := XC_faturaBilgisi || '';
XC_faturaBilgisi := XC_faturaBilgisi || 'TRL';
XC_faturaBilgisi := XC_faturaBilgisi || '' || TRIM(TO_CHAR(R_MMA00.GLA01_BKP_EXCHG,'99.9')) || '';

-- ************ Odeme Bilgisi cekilecek ****************
XC_faturaBilgisi := XC_faturaBilgisi || '';
XC_faturaBilgisi := XC_faturaBilgisi || '';




-- Faturanın Siparişleri
XC_siparisler := '';

FOR CR2 IN (SELECT DISTINCT BELGE_ID_B FROM MMAV1 WHERE YAF00_CODE_A = 'PG' AND BELGE_ID_A = P_MMA00_ID) LOOP

    FOR CR3 IN (SELECT SDB00_NUMBER, SDB00_DATE FROM SDB00 WHERE SDB00_ID = CR2.BELGE_ID_B) LOOP
              XC_siparisler := XC_siparisler || '';
              XC_siparisler := XC_siparisler || ''||CR3.SDB00_NUMBER||''; -- 2265495331
              XC_siparisler := XC_siparisler || ''||TO_CHAR(CR3.SDB00_DATE,'YYYY-MM-DD')||''; -- 2014-04-04
              XC_siparisler := XC_siparisler || '';
   END LOOP;
END LOOP;

XC_siparisler := XC_siparisler || '';



FOR CR IN (SELECT * FROM MMA03 WHERE MMA00_ID = P_MMA00_ID) LOOP
   SELECT MMM00_NAME INTO X_MALZEME_ADI FROM MMM00 WHERE YAA01_CODE =R_MMA00.YAA01_CODE AND MMM00_ITEM_CODE = CR.MMM00_ITEM_CODE AND MMY00_TYPE = CR.MMY00_TYPE;
   --SELECT * FROM FNX10 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND FNX10_RATE = CR.FNX10_CODE;
   -- STOK BIRIMI BOS ISE ADET OALCAK SEKILDE AL
   SELECT NVL(MMU60_EGOV_CODE,'NIU') INTO X_BIRIM FROM MMU60 WHERE YAA01_CODE =R_MMA00.YAA01_CODE AND MMU60_UOM = CR.MMU60_UOM_1;
   IF CR.DISC_SUM > 0 THEN 
      X_ISKONTOVAR := '1'; 
   ELSE 
      X_ISKONTOVAR := '0'; 
   END IF;

   IF CR.DISC_1 <> 0 OR CR.DISC_2 <> 0 OR CR.DISC_3 <> 0 OR CR.DISC_4 <> 0 OR CR.DISC_5 <> 0 THEN
      XC_satirIskonto := '';
      IF CR.DISC_1 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto1';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_1,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_1_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_2 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto2';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_2,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_2_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_3 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto3';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_3,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_3_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_4 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto4';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_4,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_4_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_5 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto5';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_5,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_5_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      XC_satirIskonto := XC_satirIskonto || '';      
   ELSE
      XC_satirIskonto := XC_satirIskonto || '';
   END IF;
   
   -- Satır Vergileri
   XC_satirVergi := '';
   XC_satirVergi := XC_satirVergi || '';   
   XC_satirVergi := XC_satirVergi || 'KDV';
   XC_satirVergi := XC_satirVergi || '0015';
   XC_satirVergi := XC_satirVergi || '' || CR.FNX10_RATE || '';
   XC_satirVergi := XC_satirVergi || '' || TRIM(TO_CHAR(CR.VAT_AMOUNT,'999999999.99')) || '';
   IF CR.MMA03_MUAF_REASON IS NOT NULL THEN
      XC_satirVergi := XC_satirVergi || '' || CR.MMA03_MUAF_REASON || '';
   ELSE
       XC_satirVergi := XC_satirVergi || '';
   END IF;
   XC_satirVergi := XC_satirVergi || '';
   XC_satirVergi := XC_satirVergi || '';

   XC_satirlar := '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '' || X_MALZEME_ADI || '';
   XC_satirlar := XC_satirlar || '' || NVL(X_BIRIM,'NIU') || '';
   XC_satirlar := XC_satirlar || '' || CR.QTY_1 || '';
   XC_satirlar := XC_satirlar || '' || TRIM(TO_CHAR(CR.PRICE,'999999999.99')) || '';
   XC_satirlar := XC_satirlar || 'TRL';
   -- ***** (ürün markası gelmeli)
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';      
   XC_satirlar := XC_satirlar || '';
   -- ***** (ürün kodumuz/sap kodumuz gelmeli)
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   -- birim fiyat * satır miktarı
   XC_satirlar := XC_satirlar || '' || TRIM(TO_CHAR(CR.AMOUNT_NET,'999999999.99')) || '';
   -- Satır İskontoları
   XC_satirlar := XC_satirlar || XC_satirIskonto;
   -- Vergiler
   XC_satirlar := XC_satirlar || XC_satirVergi;

   -- malzemeTutari : (toplamTutar - iskonto) * KDV
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   P_VAT_AMOUNT := P_VAT_AMOUNT + CR.VAT_AMOUNT;
END LOOP;
XC_satirlar := XC_satirlar || '';






-- Fatura ALT TOPLAM bilgileri
XC_faturaToplam := '';
--X_CLOB := X_CLOB||''||TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT,'999999999.99'))||'';
SELECT SUM(MMA03.AMOUNT) INTO NDUM FROM MMA03 WHERE MMA00_ID = R_MMA00.MMA00_ID;
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(NDUM,'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || ''; -- S1220140188036922 # e438a424-e08d-47da-9b9e-0cd2e5550e35
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT,'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '';
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(ROUND(P_VAT_AMOUNT,2),'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT - ROUND(P_VAT_AMOUNT,2),'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '';






XC_paramLogin := '<_invoicelogin xmlns:a="http://schemas.datacontract.org/2004/07/FMC.Turkiye.Lib.EInvoice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">';
XC_paramLogin := XC_paramLogin || 'Uyumsoft';--MRfLnfrq
XC_paramLogin := XC_paramLogin || 'Uyumsoft';--FreseniusEcza_WebServis
XC_paramLogin := XC_paramLogin || '';

XC_paramInvoice := '<_invoice xmlns:a="http://schemas.datacontract.org/2004/07/FMC.Turkiye.Lib.EInvoice.WebServiceClasses" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">';
XC_paramInvoice := XC_paramInvoice || XC_gonderici;
XC_paramInvoice := XC_paramInvoice || XC_alici;
XC_paramInvoice := XC_paramInvoice || XC_irsaliye;
XC_paramInvoice := XC_paramInvoice || XC_faturaBilgisi;
XC_paramInvoice := XC_paramInvoice || XC_siparisler;
XC_paramInvoice := XC_paramInvoice || XC_satirlar;
XC_paramInvoice := XC_paramInvoice || XC_faturaToplam;

XC_paramInvoice := XC_paramInvoice || '';

soapAction := 'http://gib.fresenius.com.tr/FEFatura/IFEInvoice/SendInvoiceFromMinerva';
X_CLOB := '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || XC_paramLogin;
X_CLOB := X_CLOB || XC_paramInvoice;
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';


-- Cem Dosyaya yazma
fileEfatura := UTL_FILE.FOPEN('d:\EFaturalar', X_GUID || '.txt', 'W', 32767); 
LOOP
  BEGIN
    DBMS_LOB.read (x_clob, len_efaturaAmount, len_efaturaCopyPos, len_efaturaBuffer);
    UTL_FILE.put(fileEfatura, len_efaturaBuffer);
    len_efaturaCopyPos := len_efaturaCopyPos + len_efaturaAmount;
    EXCEPTION 
    WHEN OTHERS THEN EXIT;      
  END;
END LOOP; 
UTL_FILE.FCLOSE(fileEfatura); 

--  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256); 
  /*
  bin_output := utl_file.fopen( ,open_mode => 'wb', max_linesize => 32767 );
  UTL_FILE.GET_LINE(bin_output,V1,32767); 
  UTL_FILE.FCLOSE(bin_output); 
  bfil := bfilename( dir_name, att_file );
  l_size := dbms_lob.getlength( bfil );
  dbms_lob.fileopen( bfil, dbms_lob.file_readonly);

 bfil := bfilename( dir_name, att_file );
  l_size := dbms_lob.getlength( bfil );
  dbms_lob.fileopen( bfil, dbms_lob.file_readonly);

  for i in 1 .. ceil( l_size/amt ) loop
         if i= ceil(l_size/amt) then amt := mod(l_size,amt); end if;
         dbms_lob.read( bfil, amt, (i-1) * 2016 + 1, data);

         utl_file.put_raw( bin_output, data );
      END LOOP;
   dbms_lob.fileclose(bfil);

  utl_file.fclose( bin_output );*/
-- //Cem Dosyaya yazma




htp.p(x_clob);


DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);
DBMS_LOB.CONVERTTOBLOB(DEST_LOB => L_BLOB, 
                       SRC_CLOB => X_CLOB, 
                       AMOUNT => DBMS_LOB.LOBMAXSIZE, 
                       DEST_OFFSET => L_DEST_OFFSET, 
                       SRC_OFFSET => L_SOURCE_OFFSET, 
                       BLOB_CSID => NLS_CHARSET_ID('UTF8'), 
                       LANG_CONTEXT => L_LANG_CONTEXT, 
                       WARNING => L_WARNING);

dbms_output.put_line(LENGTHB(X_CLOB));
dbms_output.put_line(LENGTH(X_CLOB));

  L_LENGTH := DBMS_LOB.getlength(L_BLOB);
dbms_output.put_line(L_LENGTH);
  
--  req := UTL_HTTP.begin_request('http://10.130.214.205:8093/Efatura/FEInvoice.svc', 'POST', 'HTTP/1.1');
-- yani: http://10.130.214.205:8093/tEFaturaMinerva/FEInvoice.svc
-- eski: http://10.130.214.205:8093/tEfatura/FEInvoice.svc
servisAdresi := 'http://10.130.214.205:8093/tEfatura/FEInvoice.svc';
servisAdresi := 'http://10.130.214.205:8093/tEFaturaMinerva/FEInvoice.svc';
  req := UTL_HTTP.begin_request(servisAdresi, 'POST', 'HTTP/1.1');
  UTL_HTTP.set_header(req, 'Content-Type', 'text/xml');
  UTL_HTTP.set_header(req, 'Content-Length', L_LENGTH);
  UTL_HTTP.set_header(req, 'SOAPAction', soapAction);
  
  WHILE L_POS < L_LENGTH LOOP
      DBMS_LOB.READ(L_BLOB, L_AMOUNT, L_POS, L_BUFFER);
--      DBMS_OUTPUT.put_line(L_BUFFER);
      UTL_HTTP.write_raw(REQ,L_BUFFER);
      l_pos := l_pos + l_amount;      
  END LOOP;

  resp := UTL_HTTP.get_response(req);  
  UTL_HTTP.read_text(resp, l_envelope);
X_STATU := XML_PARSER(L_ENVELOPE,'');
dbms_output.put_line('cevap :'||substr(l_envelope,1,2000));
dbms_output.put_line('Durum :'||X_STATU);
dbms_output.put_line('Invoice Number :'||XML_PARSER(L_ENVELOPE,''));
dbms_output.put_line('Sonuc :'||XML_PARSER(L_ENVELOPE,''));
dbms_output.put_line('Succeed Transaction :'||XML_PARSER(L_ENVELOPE,''));

P_FATNO := X_FATNO;
P_GUID := X_GUID;
P_CLOB := X_CLOB;
P_STATU := X_STATU;
/*
    -- HEADER yazılıyor
    FOR i IN 1..utl_http.get_header_count(resp) LOOP
      utl_http.get_header(resp, i, name, value);
      dbms_output.put_line(name || ': ' || value);
    END LOOP;
*/
  
  UTL_HTTP.end_response(resp);
END;

13.04.2014 21:54 deki hali

create or replace procedure FRE_FATURA_XML_GONDER_new(
  P_MMA00_ID  IN NUMBER,
  P_GUID      IN OUT VARCHAR2,
  P_FATNO     IN OUT VARCHAR2,
  P_STATU     IN OUT VARCHAR2,
  P_CLOB      IN OUT CLOB
) IS
  X_CLOB        CLOB;
  XC_gonderici CLOB;
  XC_alici CLOB;
  XC_faturaToplam CLOB;
  XC_faturaBilgisi CLOB;  
  XC_irsaliye CLOB;
  XC_satirlar CLOB;
  XC_siparisler CLOB;
  XC_zcem CLOB;
  XC_paramLogin CLOB;
  XC_paramInvoice CLOB;
  XC_satirIskonto  CLOB;
  XC_satirVergi CLOB;

-- Gönderici ve Alici ortak degiskenleri
unvan        CLOB;
vkn          CLOB;
etiket       CLOB;
vergiDairesi CLOB;
binaAdi      CLOB;
binaNo       CLOB;
kapiNo       CLOB;
cadde        CLOB;
semt         CLOB;
ilce         CLOB;
sehir        CLOB;
ulke         CLOB;
postaKodu    CLOB;
telefon      CLOB;
faks         CLOB;
email        CLOB;
webAdresi    CLOB;
-- // Gonderici ve alici ortak degiskenleri





  X_VALUE       VARCHAR2(32767) DEFAULT '';
  req           utl_http.req;
  resp          utl_http.resp;
  name      VARCHAR2(256);
  soapAction VARCHAR2(1024);
  value     VARCHAR2(1024);
  buffer_size    NUMBER(10) := 512;
  substring_msg  VARCHAR2(512);
  raw_data RAW(512);
    
  RESPONSE      VARCHAR2(32000) DEFAULT '';
  l_envelope VARCHAR2(32767);

   L_BLOB          BLOB;
   L_DEST_OFFSET   INTEGER := 1;
   L_SOURCE_OFFSET INTEGER := 1;
   L_LANG_CONTEXT  INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
   L_WARNING       INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;

   L_BUFFER    RAW(32767);
   L_AMOUNT    BINARY_INTEGER := 20000; --MUST BE SMALL REPLACE ('A','AB') ... BUYUDUGU ICIN
   L_POS       INTEGER := 1;
   L_LENGTH    INTEGER;

   R_FNM00       FNM00%ROWTYPE;
   R_FNM00_SVK   FNM00%ROWTYPE;
   R_MMA00       MMA00%ROWTYPE;
   R_YAA01       YAA01%ROWTYPE;
   R_YAA07       YAA07%ROWTYPE;
   R_YAA07_MRK   YAA07%ROWTYPE;

   P_USERNAME     VARCHAR2(50) DEFAULT ''; 
   P_PASSWORD     VARCHAR2(50) DEFAULT '';
   P_URL          VARCHAR2(50) DEFAULT '';
   X_SIRKET_VKN   VARCHAR2(50) DEFAULT '';
   V_BELGE_SON_NO NUMBER DEFAULT 1;
   X_DESPATCH_ID  VARCHAR2(50) DEFAULT '';
   X_DESPATCH_DATE  VARCHAR2(50) DEFAULT '';
   X_MALZEME_ADI    VARCHAR2(100) DEFAULT '';
   X_BIRIM          VARCHAR2(100) DEFAULT '';
   X_ISKONTOVAR     NUMBER DEFAULT 0;
   P_VAT_AMOUNT     NUMBER DEFAULT 0;
   CDUMMY           VARCHAR2(50) DEFAULT '';
   NDUM             NUMBER DEFAULT 0;
   X_GUID           VARCHAR2(100) DEFAULT '';
   X_FATNO          VARCHAR2(20) DEFAULT '';
   X_STATU          VARCHAR2(20) DEFAULT '';
   X_SENARYO        VARCHAR2(30) DEFAULT '';
   X_YAX02_CODE     VARCHAR2(10) DEFAULT '';
   
   CURSOR CBELGE(cYAA01 VARCHAR2, CYAA05 VARCHAR2) IS SELECT YAF01_LAST_NUMBER FROM PLEGOV.EFY01 WHERE YAA01_CODE = cYAA01 AND YAA05_CODE = cYAA05 AND YAF00_CODE = 'PG' FOR UPDATE;
   -- Cem değişkenler
  servisAdresi VARCHAR(255);
  -- Dosyaya yazma
  dirEFaturalar varchar2(100) default 'd:\EFaturalar';
  
  fileEfatura UTL_FILE.FILE_TYPE; 
  len_efaturaBuffer  VARCHAR2(32767);
  len_efaturaAmount  BINARY_INTEGER := 32767;
  len_efaturaCopyPos     INTEGER := 1;
-- // Cem değişkenler


BEGIN

   SELECT * INTO R_MMA00 FROM MMA00 WHERE MMA00_ID = P_MMA00_ID;
   SELECT * INTO R_YAA01 FROM YAA01 WHERE YAA01_CODE = R_MMA00.YAA01_CODE;
   SELECT * INTO R_YAA07 FROM YAA07 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND YAA05_CODE = R_MMA00.YAA05_CODE AND YAA07_CODE = R_MMA00.YAA07_CODE;
   
   -- SIRKET VKN SINI 000 ISYERI UZERINDEN ALIYORUM
   SELECT * INTO R_YAA07_MRK FROM YAA07 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND YAA05_CODE = R_MMA00.YAA05_CODE AND YAA07_CODE = '000';
   
   -- KULLANICI ADI VE KODUNU ADI FRE_EFATURA OLAN DIS SISTEM BILGISI UZERINDEN ALIYORUM
   SELECT EXZ00_HOST_USER, EXZ00_HOST_PASS, EXZ00_HOST INTO P_USERNAME, P_PASSWORD, P_URL FROM EXZ00 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND EXZ00_NAME = 'FRE_EFATURA';
   
   SELECT * INTO R_FNM00 FROM FNM00 WHERE FNM00_ID = R_MMA00.ACC_CODE_ID; 
   IF R_MMA00.ACC_ID_SEVK IS NOT NULL THEN
      SELECT * INTO R_FNM00_SVK FROM FNM00 WHERE FNM00_ID = R_MMA00.ACC_ID_SEVK; 
   ELSE
      R_FNM00_SVK := NULL;
   END IF;


-- Gönderen bilgileri
binaAdi := '';
binaNo := '';

IF R_YAA07_MRK.YAA07_ADDRESS IS NOT NULL THEN
  cadde := '' || R_YAA07_MRK.YAA07_ADDRESS || '';
ELSE       
  cadde := '';
END IF;       
        


IF R_YAA07_MRK.YAA07_E_MAIL IS NOT NULL THEN
  email := '' || R_YAA07_MRK.YAA07_E_MAIL || '';
ELSE       
  email := '';
END IF;       
        
etiket := 'defaultpk';

IF R_YAA07_MRK.YAA07_FAX_1 IS NOT NULL THEN
  faks := ''|| R_YAA07_MRK.YAA07_FAX_1 || '';
ELSE       
  faks := '';
END IF;       
        
ilce := '';
kapiNo := '';

IF R_YAA07_MRK.YAA07_ZIP_CODE IS NOT NULL THEN
  postaKodu := '' || R_YAA07_MRK.YAA07_ZIP_CODE ||'';
ELSE       
  postaKodu := '';
END IF;       





IF R_YAA07_MRK.YAD04_CITY IS NOT NULL THEN
  sehir := '' || R_YAA07_MRK.YAD04_CITY || '';
ELSE       
  sehir := '';
END IF;       

IF R_YAA07_MRK.YAA07_SEMT IS NOT NULL THEN
  semt := ''||R_YAA07_MRK.YAA07_SEMT||'';
ELSE       
  semt := '';
END IF;       

IF R_YAA07_MRK.YAA07_PHONE_1 IS NOT NULL THEN
  telefon := ''||R_YAA07_MRK.YAA07_PHONE_1||'';
ELSE       
  telefon := '';
END IF;       

IF R_YAA07_MRK.YAD03_COUNTRY IS NOT NULL THEN
  ulke := '' || R_YAA07_MRK.YAD03_COUNTRY || '';
ELSE       
  ulke := '';
END IF;       
     

IF R_YAA01.YAA01_LEGALNAME IS NOT NULL THEN
  unvan := ''|| R_YAA01.YAA01_LEGALNAME ||'';
ELSE       
  unvan := '';
END IF;       

--XC_gonderici := XC_gonderici ||'FRESENİUS ECZA DEPOSU TİCARET ANONİM ŞİRKETİ';
--XC_gonderici := XC_gonderici ||''||R_YAA07_MRK.YAA07_TAX_NO||'';
 vkn := '9000068418';--3880444538
 vergiDairesi := ''||R_YAA07_MRK.YAA07_TAX_DEPART||'';
 
IF R_YAA07_MRK.YAA07_INTERNET IS NOT NULL THEN
 webAdresi := ''||R_YAA07_MRK.YAA07_INTERNET||'';
ELSE
 webAdresi := '';
END IF;       

-- Gonderici bilgilerini toparliyoruz
XC_gonderici := '';
XC_gonderici := XC_gonderici || unvan || vkn || etiket || vergiDairesi || binaAdi || binaNo || kapiNo || cadde;
XC_gonderici := XC_gonderici || semt || ilce || sehir || ulke || postaKodu || telefon || faks || email || webAdresi;
XC_gonderici := XC_gonderici || '';

-- Alıcı Bilgileri
XC_alici := '';
binaAdi := '';
binaNo := '';

IF R_FNM00.FNM00_ADDRESS IS NOT NULL THEN
  cadde := '' || R_FNM00.FNM00_ADDRESS ||'';
ELSE     
  cadde := '';
END IF;     

      
IF R_FNM00.FNM00_Email IS NOT NULL THEN
  email := '' ||R_FNM00.FNM00_Email||'';
ELSE     
  email := '';
END IF;     
--XC_alici := XC_alici ||'defaultpk'; -- ?
etiket := 'defaultpk'; -- ?

IF R_FNM00.FNM00_Fax_1 IS NOT NULL THEN
  faks := '' ||R_FNM00.FNM00_Fax_1  ||'';
ELSE
  faks := '';
END IF;

ilce := '';
kapiNo := '';

IF R_YAA07.YAA07_ZIP_CODE IS NOT NULL THEN
  postaKodu := ''||R_YAA07.YAA07_ZIP_CODE||'';
ELSE
 postaKodu := '';
END IF;

IF R_FNM00.YAD04_CITY IS NOT NULL THEN
  sehir := ''||R_FNM00.YAD04_CITY||'';
ELSE
  sehir := '';
END IF;

IF R_FNM00.FNM00_SEMT IS NOT NULL THEN
  semt := ''||R_FNM00.FNM00_SEMT||'';
ELSE
  semt := '';
END IF;

IF R_YAA07.YAA07_PHONE_1 IS NOT NULL THEN
  telefon := ''||R_YAA07.YAA07_PHONE_1||'';
ELSE
  telefon := '';
END IF;

IF R_FNM00.YAD03_COUNTRY IS NOT NULL THEN
  ulke := '' || R_FNM00.YAD03_COUNTRY || '';
ELSE
  ulke := '';
END IF;

IF R_FNM00.FNM00_TRADE_NAME IS NOT NULL THEN
  unvan := '' || R_FNM00.FNM00_TRADE_NAME || '';
ELSE
  unvan := '';
END IF;

--XC_alici := XC_alici||'İnci Ecza Deposu Tic. Ltd. Şti.';
--XC_alici := XC_alici||''||R_FNM00.FNM00_TAX_NO||'';
vkn := '1111111104';--6300388087
vergiDairesi := ''||R_FNM00.FNM00_TAX_DEPART||'';

IF R_FNM00.FNM00_WEB IS NOT NULL THEN
  webAdresi := ''||R_FNM00.FNM00_WEB||'';
ELSE
  webAdresi := '';
END IF;


-- Gonderici bilgilerini toparliyoruz
XC_alici := '';
XC_alici := XC_alici || unvan || vkn || etiket || vergiDairesi || binaAdi || binaNo || kapiNo || cadde ;
XC_alici := XC_alici || semt || ilce || sehir || ulke || postaKodu || telefon || faks || email || webAdresi;
XC_alici := XC_alici || '';




-- İrsaliye bilgisi saat bilgisine kadar
-- İrsaliye Bilgileri
XC_irsaliye := '';
   BEGIN
   SELECT IRS_NO,TO_CHAR(IRS_TAR, 'DD.MM.YYYY') INTO X_DESPATCH_ID, X_DESPATCH_DATE
      FROM( SELECT MMAV1.BELGE_ID_B AS IRS_NO, MMA00.MMA00_DATE AS IRS_TAR
      FROM PLE01.MMAV1, PLE01.MMA00
   WHERE MMAV1.BELGE_ID_A = TO_CHAR(R_MMA00.MMA00_ID)
         AND MMAV1.YAF00_CODE_A = 'PG'
         AND MMAV1.YAF00_CODE_B = 'PE'
         AND MMA00.YAA01_CODE = R_MMA00.YAA01_CODE
         AND MMAV1.BELGE_ID_B = TO_CHAR(MMA00.MMA00_ID)
   GROUP BY MMAV1.BELGE_ID_B, MMA00.MMA00_DATE,MMA00.MMA00_ID
   ORDER BY MMA00.MMA00_ID DESC ) WHERE ROWNUM=1;
   EXCEPTION WHEN OTHERS THEN
      X_DESPATCH_ID := NULL; 
      X_DESPATCH_DATE := NULL;
   END;
XC_irsaliye := XC_irsaliye || '' || X_DESPATCH_ID || '';
-- 2014-12-23 22:34
XC_irsaliye := XC_irsaliye || '' || TO_CHAR(X_DESPATCH_DATE,'YYYY-MM-DD HH:MM') || '';
XC_irsaliye := XC_irsaliye || '';



-- Fatura Bilgileri
XC_faturaBilgisi := '';
X_GUID := UPPER(REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'));
XC_faturaBilgisi := XC_faturaBilgisi||'' || X_GUID || ''; 

OPEN CBELGE(R_MMA00.YAA01_CODE, R_MMA00.YAA05_CODE); FETCH CBELGE INTO V_BELGE_SON_NO; V_BELGE_SON_NO := V_BELGE_SON_NO + 1; 
IF V_BELGE_SON_NO IS NOT NULL THEN 
   UPDATE PLEGOV.EFY01 SET YAF01_LAST_NUMBER = V_BELGE_SON_NO WHERE CURRENT OF CBELGE;                  
END IF;
CLOSE CBELGE;
X_FATNO := 'TST' || R_MMA00.YAA05_CODE || TRIM(TO_CHAR(V_BELGE_SON_NO,'000000000'));
XC_faturaBilgisi := XC_faturaBilgisi || '' || X_FATNO || '';
XC_faturaBilgisi := XC_faturaBilgisi || '' || TO_CHAR(R_MMA00.MMA00_DATE,'YYYY-MM-DD') || '';

X_SENARYO := 'TICARIFATURA';
IF R_MMA00.EFA00_SENARYO = 'TEM' THEN X_SENARYO := 'TEMELFATURA'; END IF;
IF R_MMA00.EFA00_SENARYO = 'TIC' THEN X_SENARYO := 'TICARIFATURA'; END IF;
XC_faturaBilgisi := XC_faturaBilgisi || ''||X_SENARYO||'';
XC_faturaBilgisi := XC_faturaBilgisi || 'SATIS';

IF R_MMA00.MMA00_MUAF_REASON IS NOT NULL THEN
   XC_faturaBilgisi := XC_faturaBilgisi || '' || R_MMA00.MMA00_MUAF_REASON || '';
ELSE
    XC_faturaBilgisi := XC_faturaBilgisi || '';
END IF;

XC_faturaBilgisi := XC_faturaBilgisi || '';
XC_faturaBilgisi := XC_faturaBilgisi || 'TRL';
XC_faturaBilgisi := XC_faturaBilgisi || '' || TRIM(TO_CHAR(R_MMA00.GLA01_BKP_EXCHG,'99.9')) || '';

-- ************ Odeme Bilgisi cekilecek ****************
XC_faturaBilgisi := XC_faturaBilgisi || '';
XC_faturaBilgisi := XC_faturaBilgisi || '';




-- Faturanın Siparişleri
XC_siparisler := '';

FOR CR2 IN (SELECT DISTINCT BELGE_ID_B FROM MMAV1 WHERE YAF00_CODE_A = 'PG' AND BELGE_ID_A = P_MMA00_ID) LOOP

    FOR CR3 IN (SELECT SDB00_NUMBER, SDB00_DATE FROM SDB00 WHERE SDB00_ID = CR2.BELGE_ID_B) LOOP
              XC_siparisler := XC_siparisler || '';
              XC_siparisler := XC_siparisler || ''||CR3.SDB00_NUMBER||''; -- 2265495331
              XC_siparisler := XC_siparisler || ''||TO_CHAR(CR3.SDB00_DATE,'YYYY-MM-DD')||''; -- 2014-04-04
              XC_siparisler := XC_siparisler || '';
   END LOOP;
END LOOP;

XC_siparisler := XC_siparisler || '';



FOR CR IN (SELECT * FROM MMA03 WHERE MMA00_ID = P_MMA00_ID) LOOP
   SELECT MMM00_NAME INTO X_MALZEME_ADI FROM MMM00 WHERE YAA01_CODE =R_MMA00.YAA01_CODE AND MMM00_ITEM_CODE = CR.MMM00_ITEM_CODE AND MMY00_TYPE = CR.MMY00_TYPE;
   --SELECT * FROM FNX10 WHERE YAA01_CODE = R_MMA00.YAA01_CODE AND FNX10_RATE = CR.FNX10_CODE;
   -- STOK BIRIMI BOS ISE ADET OALCAK SEKILDE AL
   SELECT NVL(MMU60_EGOV_CODE,'NIU') INTO X_BIRIM FROM MMU60 WHERE YAA01_CODE =R_MMA00.YAA01_CODE AND MMU60_UOM = CR.MMU60_UOM_1;
   IF CR.DISC_SUM > 0 THEN 
      X_ISKONTOVAR := '1'; 
   ELSE 
      X_ISKONTOVAR := '0'; 
   END IF;

   IF CR.DISC_1 <> 0 OR CR.DISC_2 <> 0 OR CR.DISC_3 <> 0 OR CR.DISC_4 <> 0 OR CR.DISC_5 <> 0 THEN
      XC_satirIskonto := '';
      IF CR.DISC_1 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto1';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_1,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_1_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_2 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto2';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_2,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_2_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_3 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto3';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_3,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_3_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_4 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto4';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_4,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_4_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      IF CR.DISC_5 != 0 THEN
        XC_satirIskonto := XC_satirIskonto || '';
        XC_satirIskonto := XC_satirIskonto || 'iskonto5';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_5,'99.00'))||'';
        XC_satirIskonto := XC_satirIskonto || ''||TRIM(TO_CHAR(CR.DISC_5_AMOUNT,'999999999.99'))||'';
        XC_satirIskonto := XC_satirIskonto || '';
      END IF;
      XC_satirIskonto := XC_satirIskonto || '';      
   ELSE
      XC_satirIskonto := XC_satirIskonto || '';
   END IF;
   
   -- Satır Vergileri
   XC_satirVergi := '';
   XC_satirVergi := XC_satirVergi || '';   
   XC_satirVergi := XC_satirVergi || 'KDV';
   XC_satirVergi := XC_satirVergi || '0015';
   XC_satirVergi := XC_satirVergi || '' || CR.FNX10_RATE || '';
   XC_satirVergi := XC_satirVergi || '' || TRIM(TO_CHAR(CR.VAT_AMOUNT,'999999999.99')) || '';
   IF CR.MMA03_MUAF_REASON IS NOT NULL THEN
      XC_satirVergi := XC_satirVergi || '' || CR.MMA03_MUAF_REASON || '';
   ELSE
       XC_satirVergi := XC_satirVergi || '';
   END IF;
   XC_satirVergi := XC_satirVergi || '';
   XC_satirVergi := XC_satirVergi || '';

   XC_satirlar := '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '' || X_MALZEME_ADI || '';
   XC_satirlar := XC_satirlar || '' || NVL(X_BIRIM,'NIU') || '';
   XC_satirlar := XC_satirlar || '' || CR.QTY_1 || '';
   XC_satirlar := XC_satirlar || '' || TRIM(TO_CHAR(CR.PRICE,'999999999.99')) || '';
   XC_satirlar := XC_satirlar || 'TRL';
   -- ***** (ürün markası gelmeli)
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';      
   XC_satirlar := XC_satirlar || '';
   -- ***** (ürün kodumuz/sap kodumuz gelmeli)
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   -- birim fiyat * satır miktarı
   XC_satirlar := XC_satirlar || '' || TRIM(TO_CHAR(CR.AMOUNT_NET,'999999999.99')) || '';
   -- Satır İskontoları
   XC_satirlar := XC_satirlar || XC_satirIskonto;
   -- Vergiler
   XC_satirlar := XC_satirlar || XC_satirVergi;

   -- malzemeTutari : (toplamTutar - iskonto) * KDV
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   XC_satirlar := XC_satirlar || '';
   P_VAT_AMOUNT := P_VAT_AMOUNT + CR.VAT_AMOUNT;
END LOOP;
XC_satirlar := XC_satirlar || '';






-- Fatura ALT TOPLAM bilgileri
XC_faturaToplam := '';
--X_CLOB := X_CLOB||''||TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT,'999999999.99'))||'';
SELECT SUM(MMA03.AMOUNT) INTO NDUM FROM MMA03 WHERE MMA00_ID = R_MMA00.MMA00_ID;
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(NDUM,'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || ''; -- S1220140188036922 # e438a424-e08d-47da-9b9e-0cd2e5550e35
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT,'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '';
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(ROUND(P_VAT_AMOUNT,2),'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '' || TRIM(TO_CHAR(R_MMA00.GLA01_ORJ_AMOUNT - ROUND(P_VAT_AMOUNT,2),'999999999.99')) || '';
XC_faturaToplam := XC_faturaToplam || '';






XC_paramLogin := '<_invoiceLogin xmlns:a="http://schemas.datacontract.org/2004/07/FMC.Turkiye.Lib.EInvoice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">';
XC_paramLogin := XC_paramLogin || 'Uyumsoft';--MRfLnfrq
XC_paramLogin := XC_paramLogin || 'Uyumsoft';--FreseniusEcza_WebServis
XC_paramLogin := XC_paramLogin || '';

XC_paramInvoice := '<_invoice xmlns:a="http://schemas.datacontract.org/2004/07/FMC.Turkiye.Lib.EInvoice.WebServiceClasses" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">';
XC_paramInvoice := XC_paramInvoice || XC_gonderici;
XC_paramInvoice := XC_paramInvoice || XC_alici;
XC_paramInvoice := XC_paramInvoice || XC_irsaliye;
XC_paramInvoice := XC_paramInvoice || XC_faturaBilgisi;
XC_paramInvoice := XC_paramInvoice || XC_siparisler;
XC_paramInvoice := XC_paramInvoice || XC_satirlar;
XC_paramInvoice := XC_paramInvoice || XC_faturaToplam;

XC_paramInvoice := XC_paramInvoice || '';

soapAction := 'http://gib.fresenius.com.tr/FEFatura/IFEInvoice/SendInvoiceFromMinerva';
soapAction := 'http://gib.fresenius.com.tr/tEFaturaMinerva/IFEInvoice/SendInvoiceFromMinerva';
X_CLOB := '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || XC_paramLogin;
X_CLOB := X_CLOB || XC_paramInvoice;
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';
X_CLOB := X_CLOB || '';


-- Cem Dosyaya yazma
fileEfatura := UTL_FILE.FOPEN('d:\EFaturalar', X_GUID || '.txt', 'W', 32767); 
LOOP
  BEGIN
    DBMS_LOB.read (x_clob, len_efaturaAmount, len_efaturaCopyPos, len_efaturaBuffer);
    UTL_FILE.put(fileEfatura, len_efaturaBuffer);
    len_efaturaCopyPos := len_efaturaCopyPos + len_efaturaAmount;
    EXCEPTION 
    WHEN OTHERS THEN EXIT;      
  END;
END LOOP; 
UTL_FILE.FCLOSE(fileEfatura); 

--  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256); 
  /*
  bin_output := utl_file.fopen( ,open_mode => 'wb', max_linesize => 32767 );
  UTL_FILE.GET_LINE(bin_output,V1,32767); 
  UTL_FILE.FCLOSE(bin_output); 
  bfil := bfilename( dir_name, att_file );
  l_size := dbms_lob.getlength( bfil );
  dbms_lob.fileopen( bfil, dbms_lob.file_readonly);

 bfil := bfilename( dir_name, att_file );
  l_size := dbms_lob.getlength( bfil );
  dbms_lob.fileopen( bfil, dbms_lob.file_readonly);

  for i in 1 .. ceil( l_size/amt ) loop
         if i= ceil(l_size/amt) then amt := mod(l_size,amt); end if;
         dbms_lob.read( bfil, amt, (i-1) * 2016 + 1, data);

         utl_file.put_raw( bin_output, data );
      END LOOP;
   dbms_lob.fileclose(bfil);

  utl_file.fclose( bin_output );*/
-- //Cem Dosyaya yazma




htp.p(x_clob);


DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);
DBMS_LOB.CONVERTTOBLOB(DEST_LOB => L_BLOB, 
                       SRC_CLOB => X_CLOB, 
                       AMOUNT => DBMS_LOB.LOBMAXSIZE, 
                       DEST_OFFSET => L_DEST_OFFSET, 
                       SRC_OFFSET => L_SOURCE_OFFSET, 
                       BLOB_CSID => NLS_CHARSET_ID('UTF8'), 
                       LANG_CONTEXT => L_LANG_CONTEXT, 
                       WARNING => L_WARNING);

dbms_output.put_line(LENGTHB(X_CLOB));
dbms_output.put_line(LENGTH(X_CLOB));

  L_LENGTH := DBMS_LOB.getlength(L_BLOB);
dbms_output.put_line(L_LENGTH);
  
--  req := UTL_HTTP.begin_request('http://10.130.214.205:8093/Efatura/FEInvoice.svc', 'POST', 'HTTP/1.1');
-- yani: http://10.130.214.205:8093/tEFaturaMinerva/FEInvoice.svc
-- eski: http://10.130.214.205:8093/tEfatura/FEInvoice.svc
servisAdresi := 'http://10.130.214.205:8093/tEfatura/FEInvoice.svc';
servisAdresi := 'http://10.130.214.205:8093/tEFaturaMinerva/FEInvoice.svc';
  req := UTL_HTTP.begin_request(servisAdresi, 'POST', 'HTTP/1.1');
  UTL_HTTP.set_header(req, 'Content-Type', 'text/xml');
  UTL_HTTP.set_header(req, 'Content-Length', L_LENGTH);
  UTL_HTTP.set_header(req, 'SOAPAction', soapAction);
  
  WHILE L_POS < L_LENGTH LOOP
      DBMS_LOB.READ(L_BLOB, L_AMOUNT, L_POS, L_BUFFER);
--      DBMS_OUTPUT.put_line(L_BUFFER);
      UTL_HTTP.write_raw(REQ,L_BUFFER);
      l_pos := l_pos + l_amount;      
  END LOOP;

  resp := UTL_HTTP.get_response(req);  
  UTL_HTTP.read_text(resp, l_envelope);
X_STATU := XML_PARSER(L_ENVELOPE,'');
dbms_output.put_line('cevap :'||substr(l_envelope,1,2000));
dbms_output.put_line('Durum :'||X_STATU);
dbms_output.put_line('Invoice Number :'||XML_PARSER(L_ENVELOPE,''));
dbms_output.put_line('Sonuc :'||XML_PARSER(L_ENVELOPE,''));
dbms_output.put_line('Succeed Transaction :'||XML_PARSER(L_ENVELOPE,''));

P_FATNO := X_FATNO;
P_GUID := X_GUID;
P_CLOB := X_CLOB;
P_STATU := X_STATU;
/*
    -- HEADER yazılıyor
    FOR i IN 1..utl_http.get_header_count(resp) LOOP
      utl_http.get_header(resp, i, name, value);
      dbms_output.put_line(name || ': ' || value);
    END LOOP;
*/
  
  UTL_HTTP.end_response(resp);
END;

Aklımda Kalası Kelimeler

11 Nisan 2014 Cuma

Minerva Prosedür ve Testi