NOTLARIMDAN...: pl/sql

pl/sql etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster

11 Nisan 2014 Cuma

PL/SQL Dosya Okuma ve Yazma

Milyon makale var bu konuda ve Oracle dökümanları dahi yeterken ben de "neler öğrendim" diye bir kenara not alayım istiyorum.

F1, F2 dosyaların işlem yapmamız için uçları
V1 satırları içine okuyacağımız değişkenimiz VARCHAR cinsinden ve maks uzunlukta(32767), Bir karakter uzun olursa istisna fırlatacağı için HER TÜRLÜ İSTİSNA (OTHERS) durumunda çık diyoruz (WHEN OTHERS THEN EXIT;)

declare 
  V1 VARCHAR2(32767); 
  F1 UTL_FILE.FILE_TYPE;
  F2 UTL_FILE.FILE_TYPE;
begin
-- R : Read (Okumak için açıyoruz)
   F1 := UTL_FILE.FOPEN('d:\temp','test.txt','R',256);  
-- W : Write (Yazmak için açıyoruz)
   F2 := UTL_FILE.FOPEN('d:\temp','test1.txt','W',256); 
   LOOP
       BEGIN
            UTL_FILE.GET_LINE(F1, V1, 32767);
            DBMS_OUTPUT.PUT_LINE(V1);
            UTL_FILE.PUT_LINE(F2,V1);
       EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
    UTL_FILE.FCLOSE(F1);
    UTL_FILE.FCLOSE(F2);
    dbms_output.put_line('Tüm dosyalar kapandı');
    
end;

Refs: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i998315

CLOB Değişkenin dosyaya yazılması.

DECLARE
  dirEFaturalar        varchar2(100)         default 'd:\EFaturalar';
  X_GUID               VARCHAR2(100)         DEFAULT '';
  fileEfatura          UTL_FILE.FILE_TYPE; 
  len_efaturaBuffer    VARCHAR2(32767);
  len_efaturaAmount    BINARY_INTEGER        := 32767;
  len_efaturaCopyPos   INTEGER               := 1;
BEGIN
  X_GUID := UPPER(REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'));
  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); 
END;

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

PL/SQL Dosya Okuma ve Yazma

Minerva Prosedür ve Testi