PROCEDURE EDI_HAT_ASN2 IS

   Unix_sql         varchar2(355);
   Hdr_Sql          varchar2(255);
   edi_hat_data     utl_file.file_type;
   edi_ftp_data     utl_file.file_type;
   v_msg            varchar2(255);
   v_sysdate        varchar2(8);
   from_sysdate     varchar2(20);
   from_sysdate2    varchar2(20);
   to_sysdate       varchar2(20);
   to_sysdate2       varchar2(20);
   vTotal_Reject    varchar2(20);
   filename         varchar2(50);
   filename2        varchar2(40);
   nRecord          Number(6);
   var1             varchar2(18);
   var2             varchar2(18);
   vEst_Date_Out    varchar2(18);
   vEst_Time_Out    varchar2(18);
   vLotCode         varchar2(20);
   vLotNo           varchar2(45);
   vExist_Counter   Number(8);
   vpath            varchar2(50);
   PrevWipid        varchar2(50);
   LotQuantity      varchar2(10);
   vReason          varchar2(40);
   vFrom_Org_Code   varchar2(10);
   vTran_Time       varchar2(40);
   vDept            varchar2(20);
   vOrderstate      varchar2(50);
   vTran_Type       varchar2(40);
   vScrap_Authorization_No      varchar2(255);
   vTemp_Die_Qty                varchar2(50);
   vLot_Qty                     varchar2(50);
   RejectQuantity               varchar2(10);
   vCount                       Number(8);
   vExist_WPS_Split_counter     Number(8);
   vBriefDescription            varchar2(255);
   vPrevBriefDescription        varchar2(255);
   vReportTestReceiveTxn        varchar2(45);
   vLotQuantity                 varchar2(10);
   vExist_WaferOut              varchar2(10);
   var3                         number;
   vExist_Counter2              varchar2(10);
   vTran_Time2                  varchar2(40);
   vTxnSequence                 varchar2(30);
   vTxnSequence2                varchar2(30);
   vQuantityOut                 varchar2(30);
   vQuantityOut2                varchar2(30);
   vAppid                       varchar2(100);
   vAssyReverse                 number (15);
   vQuantityIn                  varchar2 (100);

   vShipFrom varchar2(3);
   vShipTo varchar2(3);
   vInvoice varchar2(10);

   Subcontractor varchar(10) default 'CAS';


Cursor Diebank_RRTC_Lot Is
/* Formatted on 2009/06/29 14:37 (Formatter Plus v4.8.8) */
SELECT /*+leading(w)*/
'D' record_id, 'DieBank' dept, invoiceno invoiceno,
DECODE (INSTR ((d.device), '/'),
'0', (d.device),
SUBSTR ((d.device),
INSTR ((d.device), '/') + 1,
LENGTH ((d.device))
)
) part_name,
' ' pd_part_name,
DECODE (INSTR (f.appid, '-'),
0, d.customerlotnumber,
DECODE (INSTR (f.appid, '-C'),
0, f.appid,
d.customerlotnumber
)
) intersil_lot_no,
ex.waferlotnumber waferlotnumber,
NVL (wip.lotquantity, f.componentqty) die_qty,
NVL (d.waferquantity, 0) wafer_quantity, mawb, hawb,
shipfrom from_org_code, shipto to_org_code,
SUBSTR (invoicedate, 3, 6) ship_out_date,
SUBSTR (invoicedate, 10, 6) ship_out_time,
DECODE ((d.customercode || d.PACKAGE),
'HUSTBA', 'E',
(f.lottype)
) owner_code,
labeldatecode datecode, datecode tracecode
FROM fwcatns_diesinventory d,
fwcatns_extenddiesinventory ex,
fwlot f,
fwwiphistory wip,
fwwiptransaction w,
proman_invoice_info pr
WHERE d.lotobject = w.lotobject
AND d.lotobject = ex.lotobject
and d.lotobjectline = ex.lotobjectline
AND d.asyinvoiceno = pr.invoiceno
AND activity = 'Terminated'
AND wip.wiptxn = w.sysid
AND INSTR (f.appid, '-') = 0
AND LTRIM (d.customercode) IN ('HAT', 'HUS', 'ISL', 'ELS', 'ELX')
AND f.sysid = d.lotobject
AND wip.lotquantity > 0
AND d.state IN ('RDUMMY', 'RRTC', 'RDSCRAP')
AND (w.txntimestamp > '20090625 235959'
AND w.txntimestamp <= '20090626 235959');




BEGIN


  v_sysdate    := to_char(sysdate-1,'YYYYMMDD');
  to_sysdate := to_char(sysdate-1,'YYYYMMDD')||' 235959';
  from_sysdate := to_char(sysdate-2,'YYYYMMDD')||' 235959';


  filename := ?

  select CS_UTILS.GET_UTL_PATH into vpath from dual;
  edi_hat_data := utl_file.fopen(vpath,filename,'W');

  var1 := to_char(sysdate,'YYYYMMDD HH24MISS');
  dbms_output.put_line('Start      '||var1);


  Hdr_Sql := 'H'||to_char(sysdate-1,'YYMMDD');


  utl_file.put_line(edi_hat_data,Hdr_sql);
  nRecord := '0';


      For Lotcur in Diebank_RRTC_Lot Loop
      vLotCode := ' ';


        If    instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
              instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
              instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
              vLotCode := 'Rework';
        Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
              vLotCode := 'Engineering';
        Elsif instr(vLotCode,'P') > 0 or instr(vLotCode,'IE') > 0 Then
              vLotCode := 'Production';
        Else
              vLotCode := 'Production';
        End if;


      Unix_Sql := rpad(LotCur.Record_Id,1,' ')||rpad(Lotcur.InvoiceNo,20,' ')||rpad(Lotcur.MAWB,35,' ')||rpad(Lotcur.HAWB,35,' ')||
                  rpad(Lotcur.Ship_Out_Date,6,' ')||rpad(Lotcur.Ship_Out_Time,6,' ')||
                  rpad(Lotcur.From_Org_Code,3,' ')||
                  rpad(Lotcur.To_Org_Code,3,' ')||
                  rpad(Lotcur.Intersil_Lot_No,20,' ')||rpad(Lotcur.Dept,8,' ')||rpad(Lotcur.WaferLotNumber,20,' ')||
                  rpad(Lotcur.Part_Name,25,' ')||
                  rpad(Lotcur.PD_Part_Name,25,' ')||rpad(Lotcur.Die_Qty,10,' ')||
                  rpad(Lotcur.Wafer_Quantity,2,' ')||
                  rpad(Lotcur.Tracecode,10,' ')||rpad(Lotcur.Datecode,4,' ')||rpad(vLotCode,20,' ')||
                  rpad(' ',30,' ');

      utl_file.put_line(edi_hat_data,Unix_sql);
      nRecord := nRecord + 1;
  End loop;


Hdr_Sql := 'T'||lpad(nRecord,5,'0');

utl_file.put_line(edi_hat_data,Hdr_sql);

v_msg := 'Close Unix File';
utl_file.fclose(edi_hat_data);

utl_file.fclose(edi_ftp_data);

var2 := to_char(sysdate,'YYYYMMDD HH24MISS');

dbms_output.put_line('End        '||var2);
dbms_output.put_line('Statement processed in '||round((utl_cal.delta(var1,var2)*24)*60,5)||' minutes.');

END;