Procedure        EDI_INTERSIL_UPD IS
--
-- To generate EDI AMD Assembly WIP to Unix
-- Person      Date          Comments
-- ---------   ------        -------------------------------------------
-- Emmy        2003-Mar-11   Intersil EDI Files Specification
-- PYFong      2004-Dec-01   Intersil EDI new format
-- Emmy        2005-Jul-07   Add Tracecode field to TEST, qtyScrap to TPRL, Eng lot indicator to ASSM & TEST
-- Emmy        2005-Sep-02   SPLT CETX9 > S200533004600;PRBE as currently reported;SPLT S200533004600 > CETX9;SHIP CETX9
-- Emmy        2005-Oct-18   fix bug on EBR lot 'EL' 
-- Emmy        2005-Oct-19   Lot_Test_Ship; select base on tstactualshipmentdate.
-- Emmy        2005-Oct-19   Lot_Assy_Ship; perform a SPLIT transaction b4 ship.
-- YS Heng     2006-Mar-17   Test & Finishing activity lot number turn to report as customer lot number
-- YS Heng     2006-Mar-22   Request by Fletcher, Philippa, to include all records that report in the daily casSHIP and
--                           camSHIP files (with CAS/CAM internal supplier lot numbers currently) as the SHIP rows
--                           in the casMOVE and camMOVE files with Intersil lot numbers (Old). 
-- YS Heng     2006-Mar-24   Request by Treverton, Robert, to change the PRBE Record data where the existing
--                           quantity in and out will be reported in wafer quantity rather than dies quantity
-- YS Heng     2006-Apr-19   Add new address code H12-17 --> MY4  (Feedback from Fletcher, Philippa)
--                           (Lot_Assy_Ship,Lot_WProbe_Ship,Lot_Test_Ship,Shipment)
-- YS Heng     2006-Apr-21   Intersil QuickTrace LOT MOVE File Specification Revision 1.14
--                           Lead frame lot number, Epoxy lot number, Gold wire lot number, Mold Compound lot number,
--                           Operation description --> 30 characters & add in Die Quantity field
-- YS Heng     2006-Jun-28   Fix W-PHA Probe Step no Equipment Info problem
-- YS Heng     2006-Aug-01   Fix W-PRE step error when no previous TrackIn record but suddenly appear Trackout,
--                           this cause some quantity fields is blank (Only fill up when TrackIn)
-- YS Heng     2006-Aug-27   Fix W-PCA Probe Step no Equipment Info problem
-- YS Heng     2006-Oct-03   To fix Finishing Partial Lot (-P01) lot number not change error
-- YS Heng     2006-Aug-28   Quicktrace Specification Version 1.16 Change :
--                           (1) To include Engineering Lot Indicator (Lot Indicator filed) to TPRL row
--                           (2) To add in new field, Tester Data File to TEST and PRBE rows to shows lots that
--                               DO NOT have a Tester Data File when a Tester Data File is usually expected for
--                               that operation.
--                           Release Date : 11 Oct 2006
-- YS Heng     2006-Oct-16   Request by Fletcher Philippa and Abdullah to stop sending the SHIP file and remove the
--                           duplication SHIP rows in MOVE file for both CAS and CAM site
-- YS Heng     2006-Nov-14   (1) Treverton, Robert, missing other Assembly AF-DAT split sublot merge back at T-PRE record
--                           (2) Treverton, Robert, report Finishing SPLT & MRGE record for reel cration
--                           (3) Abdullah & Dinesh, integrated tape and reel balance lot retrack Test COM (ex, T-ETR-COM)
--                               or integrated Test COM (ex, T-ETR-COM) step Tester Data File flag indicator to report is
--                               based on whether the lot have FwCatns_FinishingTxn record (if yes then it is the TrackIn
--                               lot that suppose to have the Tester Data File generated and FTP)
-- YS Heng     2006-Nov-15   FTP also the QuickTrace file generated to carsem internal FTP server (Sally Foo group)

--2006-Apr-21
--   Unix_sql           varchar2(355);
   Unix_sql             varchar2(380);

   edi_int_data         utl_file.file_type;
   --2006-Oct-16
   --edi_shp_data         utl_file.file_type;   
   edi_int_ftp          utl_file.file_type;
   --2006-Oct-16   
   --edi_int_ftp2         utl_file.file_type;      
   v_msg                varchar2(255);
   from_sysdate         varchar2(18);
   from_sysdate2        varchar2(18);
   To_sysdate           varchar2(18);
   vTrackInTimestamp    varchar2(18);                  
   filename             varchar2(40);
   --2006-Oct-16
   --filename2            varchar2(40);   
   filename3            varchar2(40);      
   Record_type          varchar2(4);
   vWaferScrapped       Number(38);   
   vQuantityIn          Number(38);
   vDieloss             Number(38);
   vTraceCode           varchar2(40);
   vLotnumber           varchar2(40);
   vCustomerlotnumber   varchar2(40);
   vIndicator           varchar2(255);
   var1                 varchar2(18);
   var2                 varchar2(18);
   Step_Description     varchar2(40);
   vEquipment           varchar2(40);
   vOutStep             varchar2(40);
   vLf                  varchar2(10);
   vEpxy                varchar2(10);
   vWr                  varchar2(10);
   vMcpd                varchar2(10);
   vpath                varchar2(50);
   vExist_Counter       Number(8);
   
   --2006-Aug-28
   vTEST_TesterDataFile   varchar2(5);
   vPROBE_TesterDataFile  varchar2(5);

   --2006-Nov-14
   v_RecordType                varchar2(4);         
   vExist_FinishingTxnCounter  Number(8);
   
   --2006-Nov-15
   filename4             varchar2(40);               
   edi_int_ftp4          utl_file.file_type;
          
 Cursor Lot_TrackOut Is
 Select d.Customerlotnumber Lotnumber,CustomerCode,
        d.customerlotnumber CustomerLotnumber,
        w.Wipid, w.OutStep,w.QuantityOut,
        decode(Substr(w.Outstep,1,1),'T',d.testdevice,d.Device) Device,
        w.txntimestamp, nvl(d.labeldatecode,' ') TraceCode,
        nvl(d.labeldatecode,' ') DateCode,
        d.waferquantityinwp, nvl(d.waferquantityoutwp,d.waferquantityinwp) waferquantityoutwp,        
        w.userid, w.lotobject, d.lotorderid,d.lotorderline,f.processingstatus ,f.componentqty
 From   fwlot f, fwcatns_diesinventory d,fwwiptransaction w
 Where  w.lotobject = d.lotobject and f.sysid = d.lotobject and
        ltrim(d.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
        w.txntimestamp >= from_sysdate and
        w.txntimestamp <= to_sysdate and
        w.Activity = 'TrackOut' and
        w.txnsequence = (Select Max(txnsequence) From FwWiptransaction where Activity ='TrackOut' and Outstep = w.Outstep and wipid = w.wipid);

 Cursor Lot_TrackIn (pWipid Varchar2, pStepid varchar2) Is
 Select w.QuantityIn, decode(instr(w.instep,w.Inlocation),0,w.inlocation,' ') Equipment,w.Txntimestamp TrackInTimestamp
 From   Fwwiptransaction  w
 Where  w.wipid = pWipid and
        w.Activity = 'TrackIn' and
        w.Instep = pStepId and
        w.txnsequence = (select max(txnsequence) From fwwiptransaction where Activity ='TrackIn' and Instep = w.Instep and wipid = w.wipid);       

 Cursor Lot_PreAssy_Split Is       
 Select dies.Originalcustomerlotnumber From_Lot, lto.targetcustomerlotnumber To_Lot,
        dies.device Device, lto.creationtxntimestamp LastUpdate, lto.orderquantity Qty,
        lto.creationusername Userid,dies.customercode,'PP-DBK' StepId
 From   fwlot fw, fwcatns_lotorder lto,fwcatns_extenddiesinventory e, fwcatns_diesinventory dies
 Where  fw.sysid = dies.lotobject and e.lotobject= dies.lotobject and
        dies.lotorderid = lto.lotorderid and lto.lotorderline = '300' and
        lto.prodlocation ='S' and instr(fw.appid,'-') = 0 and
        lto.orderstate in ('Active','Created','Finished','Hold') and
        dies.customercode in ('HAT','HUS','ELS','ELA','ELX') and
        (lto.targetcustomerlotnumber <> dies.originalcustomerlotnumber or
        dies.lotobject <> dies.originalparentlotobject ) and        
        lto.creationtxntimestamp >= from_sysdate and
        lto.creationtxntimestamp <= to_sysdate;
 
--2006-Mar-17
/*
 Cursor   Lot_Assy_Split Is
 Select   substr(wpn.valdata,1,INSTR(wpn.valdata,'.')-1) StepId,
          decode(instr(wipid,'-'),0,d.customerlotnumber,wipid) From_Lot,             
          childlotid To_Lot, a.txntimestamp LastUpdate,
          splitqty Qty, a.userid,d.CustomerCode,
          Decode(Substr(wpn.valdata,1,1),'T',d.testdevice,d.device) Device
 From     fwcatns_diesinventory d,  fwsplit c,
          fwsplitlot_n2m b, fwwiptransaction_pn2m wpn, fwwiptransaction a
 Where    a.sysid = b.fromid             and
          d.lotobject = a.lotobject      and
          ltrim(d.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
          b.toid = c.sysid               and
          b.linkname = 'splitCollection' and
          b.fromid = wpn.fromid          and
          wpn.keydata = 'stepId'         and
          a.txntimestamp >= from_sysdate and
          a.txntimestamp <= to_sysdate 
 Order by txnsequence;
*/

--2006-Mar-17
 Cursor   Lot_Assy_Split Is
 Select   substr(wpn.valdata,1,INSTR(wpn.valdata,'.')-1) StepId,
          decode(instr(wipid,'-'),0,d.customerlotnumber,wipid) From_Lot,             
          childlotid To_Lot, a.txntimestamp LastUpdate,
          splitqty Qty, a.userid,d.CustomerCode,
          Decode(Substr(wpn.valdata,1,1),'T',d.testdevice,d.device) Device
 From     fwcatns_diesinventory d,  fwsplit c,
          fwsplitlot_n2m b, fwwiptransaction_pn2m wpn, fwwiptransaction a
 Where    a.sysid = b.fromid             and
          d.lotobject = a.lotobject      and
          ltrim(d.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
          b.toid = c.sysid               and
          b.linkname = 'splitCollection' and
          b.fromid = wpn.fromid          and
          wpn.keydata = 'stepId'         and
          a.txntimestamp >= from_sysdate and
          a.txntimestamp <= to_sysdate   and
          wpn.valdata not like 'T%'
 Order by txnsequence;

 --2006-Mar-17
 Cursor   Lot_Test_Split Is
 Select   substr(wpn.valdata,1,INSTR(wpn.valdata,'.')-1) StepId,
          decode(instr(wipid,'-'),0,d.customerlotnumber,wipid) From_Lot,             
          childlotid To_Lot, a.txntimestamp LastUpdate,
          splitqty Qty, a.userid,d.CustomerCode,
          Decode(Substr(wpn.valdata,1,1),'T',d.testdevice,d.device) Device
 From     fwcatns_diesinventory d,  fwsplit c,
          fwsplitlot_n2m b, fwwiptransaction_pn2m wpn, fwwiptransaction a
 Where    a.sysid = b.fromid             and
          d.lotobject = a.lotobject      and
          ltrim(d.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
          b.toid = c.sysid               and
          b.linkname = 'splitCollection' and
          b.fromid = wpn.fromid          and
          wpn.keydata = 'stepId'         and
          a.txntimestamp >= from_sysdate and
          a.txntimestamp <= to_sysdate   and
          wpn.valdata like 'T%'
 Order by txnsequence;

 -- 20050708 Please can you provide us with the former row only (with the quantity that is being merged).
 -- Requester : Philippa (xclude T-PRE )
 Cursor   Lot_Assy_Merge Is
 Select   wipid To_Lot,d.CustomerCode,
          substr(mergestepid,1,instr(mergestepid,'.')-1) StepId,
          b.valdata From_Lot, a.txntimestamp LastUpdate,
          a.Userid, Sysid, Decode(SubStr(mergestepid,1,1),'T',d.testdevice,d.device) Device
 From     fwcatns_diesinventory d, fwmerge_pn2m b, fwwiptransaction a
 Where    d.lotobject = a.lotobject and
          ltrim(d.customercode) in ('ELS','ELA','ELX','HUS','HAT') and
          a.sysid = b.fromid and
          mergestepid not like 'T-PRE%' and
          b.linkname = 'childLotCollection' and
          a.txntimestamp >= from_sysdate and
          a.txntimestamp <= to_sysdate 
 Order by txnsequence;

 Cursor Lot_Assy_Merge_Qty(pSysid Varchar2) Is
 Select Quantityout Qty
 From   fwwiptransaction
 Where  outstep = ( Select h.laststepid from fwwiptransaction w, fwwiphistory h where w.sysid = h.wiptxn and w.sysid =pSysid) 
        and lotobject = ( Select w.lotobject  from fwwiptransaction w,fwwiphistory h where w.sysid = h.wiptxn and w.sysid =pSysid);

 Cursor Lot_DA_TrackIn (pWipid varchar2) Is
 Select SublotQuantity
 From   Fwcatns_sublotinfo
 Where  sublotid = pWipid and
        State ='Active';

 --2006-Mar-17       
 /*
 Cursor Lot_Assy_Ship Is        
 Select  dies.customerlotnumber lotnumber,
         Fw.appid WipId, dies.device, dies.CustomerCode,
         fw.componentqty,dies.actualshipmentdate ,dies.username,
         nvl((lto.datecode || lto.tracecode),' ') TraceCode,
         dies.originalcustomerlotnumber WaferLot,nvl(lto.dropshipcode,dies.customercode) Warehouse
 From    fwlot fw, fwcatns_lotorder lto, fwcatns_diesinventory dies,fwcatns_extenddiesinventory e
 Where   fw.sysid = dies.lotobject and e.lotobject= dies.lotobject and
         dies.lotorderid = lto.lotorderid and
         lto.lotorderline = '300' and
         lto.requiretest ='False' and
         (instr(fw.appid,'-') > 0 and instr(fw.appid,'-C') = 0 ) and
         dies.lotorderstate  = 'Finished' and
         ltrim(dies.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
         asyinvoicetxntimestamp >= from_sysdate and
         asyinvoicetxntimestamp <= to_sysdate; 
*/

--2006-Mar-17       
Cursor  Lot_Assy_Ship Is
Select  dies.customerlotnumber lotnumber,
        Fw.appid WipId, dies.device, dies.CustomerCode,
--      fw.componentqty,dies.actualshipmentdate, dies.username,
        fw.componentqty, t.Txntimestamp actualshipmentdate, dies.username,
        nvl((lto.datecode || lto.tracecode),' ') TraceCode,
        dies.originalcustomerlotnumber WaferLot,nvl(lto.dropshipcode,dies.customercode) Warehouse
From    fwlot fw, fwcatns_lotorder lto, fwcatns_diesinventory dies,fwcatns_extenddiesinventory e, t_outbound  t
Where   fw.sysid = dies.lotobject and e.lotobject= dies.lotobject and
        dies.lotorderid = lto.lotorderid and
        lto.lotorderline = '300' and
        lto.requiretest ='False' and
        (instr(fw.appid,'-') > 0 and instr(fw.appid,'-C') = 0 ) and
        dies.lotorderstate  = 'Finished' and
        ltrim(dies.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
--      asyinvoicetxntimestamp >= from_sysdate and
--      asyinvoicetxntimestamp <= to_sysdate and
        fw.appid = t.childlot and
        fw.lastlocation not like 'T%' and
        t.loaddatadt >= from_sysdate and
        t.loaddatadt <= to_sysdate and
        fw.processingstatus in ('Active','Finished');
       
 Cursor  Lot_WProbe_Ship Is
 Select  dies.customerlotnumber lotnumber,dies.lotorderid,
         Fw.appid WipId, dies.device, dies.CustomerCode,
         fw.componentqty,dies.wpactualshipmentdate, dies.username Userid,
         nvl((lto.datecode || lto.tracecode),' ') TraceCode,
         nvl(lto.ShipTocode,dies.customercode) Warehouse
 From    fwlot fw, fwcatns_lotorder lto, fwcatns_diesinventory dies
 Where   fw.sysid = dies.lotobject and 
         dies.lotorderid = lto.lotorderid and
         lto.lotorderline = '100' and
         dies.lotorderstate  = 'Finished' and
         dies.customercode in ('ELS','ELA','ELX','HAT','HUS')  and
         wpactualshipmentdate >= from_sysdate and
         wpactualshipmentdate <= to_sysdate;

 --2006-Mar-17       
 /*
 Cursor  Lot_Test_Ship Is        
 Select  dies.customerlotnumber lotnumber,
         Fw.appid WipId,dies.testdevice,dies.CustomerCode,
         fw.componentqty,dies.tstactualshipmentdate,
         nvl(lto.topmark01,' ') TraceCode, nvl(t.shipcode,dies.customercode) WareHouse,
         dies.originalcustomerlotnumber WaferLot,dies.username Userid,tstinvoiceno
 From    fwlot fw,fwcatns_lotorder lto,fwcatns_testlotattr t, fwcatns_diesinventory dies
 Where   fw.sysid = dies.lotobject and 
         dies.lotorderid = lto.lotorderid and t.lotobject= dies.lotobject and
         lto.lotorderline = '400' and
         (instr(fw.appid,'-') > 0 and instr(fw.appid,'-C') = 0 ) and
         dies.customercode in ('ELS','ELA','ELX','HAT','HUS') and
         tstinvoiceno is not null and
         dies.tstactualshipmentdate > from_sysdate2 and
         dies.tstactualshipmentdate <= to_sysdate;
*/

 --2006-Mar-17       
 Cursor  Lot_Test_Ship Is        
 Select  dies.customerlotnumber lotnumber,
         Fw.appid WipId,dies.testdevice,dies.CustomerCode,
--       fw.componentqty,dies.tstactualshipmentdate,
         fw.componentqty, tb.Txntimestamp tstactualshipmentdate,
         nvl(lto.topmark01,' ') TraceCode, nvl(t.shipcode,dies.customercode) WareHouse,
         dies.originalcustomerlotnumber WaferLot,dies.username Userid,tstinvoiceno
 From    fwlot fw,fwcatns_lotorder lto,fwcatns_testlotattr t, fwcatns_diesinventory dies, t_outbound  tb
 Where   fw.sysid = dies.lotobject and 
         dies.lotorderid = lto.lotorderid and t.lotobject= dies.lotobject and
         lto.lotorderline = '400' and
         (instr(fw.appid,'-') > 0 and instr(fw.appid,'-C') = 0 ) and
         dies.customercode in ('ELS','ELA','ELX','HAT','HUS') and
         tstinvoiceno is not null and
--       dies.tstactualshipmentdate > from_sysdate2 and
--       dies.tstactualshipmentdate <= to_sysdate and
         fw.appid = tb.childlot and
         fw.lastlocation like 'T%' and
--       tb.loaddatadt >= from_sysdate2 and
--       tb.loaddatadt <= to_sysdate and
         tb.loaddatadt >= from_sysdate and
         tb.loaddatadt <= to_sysdate and
         fw.processingstatus in ('Active','Finished');
        
 /*        
 Select  dies.customerlotnumber lotnumber,
         Fw.appid WipId,dies.testdevice,dies.CustomerCode,
         fw.componentqty,dies.tstactualshipmentdate ,
         nvl(lto.topmark01,' ') TraceCode, nvl(t.shipcode,dies.customercode) WareHouse,
         dies.originalcustomerlotnumber WaferLot,dies.username Userid,tstinvoiceno
 From    fwlot fw,fwcatns_lotorder lto,fwcatns_testlotattr t, fwcatns_diesinventory dies,fwcatns_extenddiesinventory e
 Where   fw.sysid = dies.lotobject and dies.lotobject = e.lotobject and
         dies.lotorderid = lto.lotorderid and t.lotobject= dies.lotobject and
         lto.lotorderline = '400' and
         (instr(fw.appid,'-') > 0 and instr(fw.appid,'-C') = 0 ) and
         ltrim(dies.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
         tstinvoiceno is not null and
         e.asyinvoicetxntimestamp >= from_sysdate and
         e.asyinvoicetxntimestamp <= to_sysdate;
*/

Cursor Step_Name (pOutStep varchar2) Is
Select nvl(max(Description),max(Stepname)) Description
From   Fwstepversion s
Where  s.Stepname = pOutStep;

Cursor WP_Equipment(pLotobject varchar2,pOutStep varchar2) Is
Select Equipmentid
From   fwcatns_WPTransaction
--Where  operationtype like 'PR%' and                                --2006-Jun-28
--Where  (operationtype like 'PR%' Or operationtype like 'PH%') and  --2006-Jun-28
Where  (operationtype like 'PR%' Or operationtype like 'PH%' Or operationtype like 'PC%') and  --2006-Aug-27
       instr(pOutstep,Step) > 0 and
       activity like '%YieldCheck%' and
       lotobject = pLotobject  and
       lottxncounter = (Select max(lottxncounter) from fwcatns_WPTransaction where 
--                      operationtype like 'PR%' and                               --2006-Jun-28
--                     (operationtype like 'PR%' Or operationtype like 'PH%') and  --2006-Jun-28
                       (operationtype like 'PR%' Or operationtype like 'PH%' Or operationtype like 'PC%') and --2006-Aug-27                       
                        instr(pOutstep,Step) > 0 and activity like '%YieldCheck%' and lotobject = pLotobject);

Cursor Test_Equipment(pLotobject varchar2,pOutStep varchar2) Is
Select Equipmentid
From   fwcatns_testtxn
Where  operationtype like 'ET%' and
       Step like pOutstep ||'%' and
--       instr(Step,pOutstep) > 0 and
       ( activity like '%YieldCheck%' or activity like '%TestQABuyOff%') and
       lotobject = pLotobject  and
       lottxncounter = (Select max(lottxncounter) from fwcatns_testtxn where 
                        operationtype like 'ET%' and Step like pOutStep ||'%'  and ( activity like '%YieldCheck%' or activity like '%TestQABuyOff%') and  lotobject = pLotobject);

Cursor TNR_Equipment(pReelLotobject varchar2, pOutstep varchar2) Is
Select Equipmentid
From   fwcatns_finishingchildtxn
Where  operationtype like 'TN%' and
       instr(pOutStep,Step) > 0 and
       activity like '%YieldCheck%' and
       reellotobject =pReelLotobject and
       lottxncounter = (Select max(lottxncounter) from fwcatns_FinishingChildtxn where 
                        operationtype like 'TN%' and instr(pOutstep,Step) > 0 and activity like '%YieldCheck%' and reellotobject = pReelLotobject);
     
Cursor Assy_Material(pLotorderid varchar2) Is
Select materialid ,
       materialgroup
From   Fwcatns_Orderbom
Where  lotorderid = pLotorderid;

Cursor Lot_Finishing_Split Is
Select d.testdevice Device,Customercode,                     
       a.childlotid From_Lot,
       a.reellotid To_Lot, a.step Stepid,
       a.testqasampleqty Qty, --b.binphysicalqty qty,       
       a.lastupdate, a.userid
From   fwcatns_diesinventory d, fwlot f, fwcatns_finishingchildtxn a,
       fwcatns_finishingbintxn b
Where  d.lotobject= a.reellotobject and
       d.lotobject= f.sysid and 
       a.reellotobject = b.reellotobject and
       a.step = b.step and
       a.operationtype = b.operationtype and
       a.activity = b.activity and
       a.childlotobject = b.childlotobject and
       a.lottxncounter =b.lottxncounter and
       ltrim(d.customercode) in ('ELS','ELA','ELX','HAT','HUS') and
       lastupdate >= from_sysdate and
       lastupdate <= to_sysdate  and
       a.reelstate ='CREATED' and
       b.bintype ='G' and
       a.lottxncounter = (select max(lottxncounter) from fwcatns_finishingchildtxn c
                          where a.reellotobject = c.reellotobject and
                                a.step = c.step and a.operationtype = c.operationtype and
                                a.activity = c.activity and c.childlotobject =  a.childlotobject )
       --2006-Nov-14                                
       order by a.reellotid, decode(instr(a.reellotid, a.childlotid, 1), '1', '0', '1');                                

Cursor Lot_Test_Merge Is
Select dies.testdevice Device,
       t.parentlotid From_Lot, Customercode,
       t.wipid To_Lot, t.fromqty qty,
       t.Lastupdate, t.StepId, t.Userid
From   fwlot fw,fwcatns_diesinventory dies,
       fwcatns_testsplitnmerge t
Where  t.lotobject = dies.lotobject and
       fw.sysid = dies.lotobject and
       ltrim(dies.customercode) in ('ELA','ELS','ELX','HAT','HUS') and
       --2006-Nov-14              
       --t.parentlotid <> t.wipid  and
       t.lastupdate >= from_sysdate and
       t.lastupdate <= to_sysdate  and       
       t.activity ='Merge' and t.stepid = 'T-PRE';

Cursor Shipment Is
Select f.appid  Lotnumber , 'H' Tran_type, substr(t.lotnumber,1,3) CustomerCode,
       t.Device, f.componentqty quantity,
       t.Txntimestamp, --substr(Lotnumber,1,3) PDC_To,
       t.alias PDC_To,
       MAWB, Motherlot,
       --2006-Mar-22
       d.customerlotnumber Customerlotnumber,
       --2006-Mar-17
       f.lastlocation lastlocation
From   fwcatns_diesinventory d, fwlot f, t_outbound  t
Where  substr(lotnumber,1,3) in ('ELS','ELA','ELX','HAT','HUS') and 
       instr(f.appid,'-') > 0 and f.processingstatus in ('Active','Finished') and
       d.lotobject= f.sysid and f.appid = t.childlot and
--     d.customerlotnumber = substr(childlot,5,length(childlot)) and
       loaddatadt >= from_sysdate and
       loaddatadt <= to_sysdate;
/*
Select Childlot Lotnumber, 'H' Tran_type,
       Device, InvAmt Quantity,
       Txntimestamp, substr(Lotnumber,1,3) PDC_To,
       MAWB, Motherlot
From   t_outbound
Where  substr(lotnumber,1,3) in ('ELS','ELA','HAT','HUS') and
       loaddatadt >= from_sysdate and
       loaddatadt <= to_sysdate;
*/

/*
--2006-Mar-17
Cursor  Cutover_Split Is
select  p.appid From_Lot, d.CustomerCode, p.step StepId, p.stepdesc Description,
        d.customerlotnumber To_Lot, '20060510 000000' LastUpdate, p.componentqty Qty,
        'CAS' UserId, Decode(SubStr(p.step,1,1),'T',d.testdevice,d.device) Device
From    fwlot f, fwcatns_diesinventory d, pp_QuickTrace_cutover2 p
Where   f.sysid = d.lotobject and p.appid = f.appid;
*/

/*
Cursor   Cutover_Split_For_RollBack Is
select   ex.other10 From_Lot, d.CustomerCode CustomerCode, p.step StepId, p.stepdesc Description,
         p.customerlotnumber To_Lot, '20060808 000000' LastUpdate, p.componentqty Qty,
         'CAS' UserId, Decode(SubStr(p.step,1,1),'T',nvl(d.testdevice,d.device),d.device) Device         
From     fwcatns_diesinventory d, fwcatns_extenddiesinv2 ex, pp_QuickTrace_cutover4 p
Where    p.customerlotnumber = d.customerlotnumber and d.lotobject = ex.lotobject
and      d.customercode in ('HAT','HUS','ELA','ELX','ELS')
and      d.LOTORDERLINE = '400'
and      d.state = 'Active';
*/
                         
BEGIN
/*
  Select txntimestamp into from_sysdate from edi_wipfeed_trml2
  Where customerlotnumber ='EDI_INTERSIL_UPD';

--  from_sysdate2 := to_char(sysdate-2,'YYYYMMDD') || ' 000000';
--  To_sysdate := to_char(sysdate-1,'YYYYMMDD')|| ' 235959';  

--  from_sysdate2 := '20060803 000000';
--  To_sysdate    := '20060804 235959'; 

  from_sysdate2 := '20060804 000000';
  To_sysdate    := '20060805 235959'; 

--  filename     := 'lotmove.CAS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS') ||'.dat';
--  filename2    := 'lotship.CAS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS') ||'.dat';

  Delete from edi_intersil_data2
  where txntimestamp like substr(to_sysdate,1,8)||'%';
  Commit;

--   Philippa request to change filename as below 2004-Aug-18
  filename     := 'Old_casMOVE.carsemS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS');
  filename2    := 'Old_casSHIP.carsemS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS');

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

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

  Select txntimestamp into from_sysdate from edi_wipfeed_trml
  Where customerlotnumber ='EDI_INTERSIL_UPD';

  from_sysdate2 := to_char(sysdate-2,'YYYYMMDD') || ' 000000';
  To_sysdate    := to_char(sysdate-1,'YYYYMMDD') || ' 235959';  
--  filename     := 'lotmove.CAS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS') ||'.dat';
--  filename2    := 'lotship.CAS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS') ||'.dat';

  --2006-Mar-17 To be unremark
  Delete from edi_intersil_data
  where txntimestamp like substr(to_sysdate,1,8)||'%';
  Commit;

--   Philippa request to change filename as below 2004-Aug-18
  filename     := 'casMOVE.carsemS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS');
  --2006-Oct-16
  --filename2    := 'casSHIP.carsemS.'|| to_char(sysdate-1,'YYYYMMDDHH24MISS');

  select CS_UTILS.GET_UTL_PATH into vpath from dual;  
  edi_int_data := utl_file.fopen(vpath,filename,'W');  
  --2006-Oct-16
  --edi_shp_data := utl_file.fopen(vpath,filename2,'W');  

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

  --2006-Mar-21
  /*
  For Rec in Cutover_Split_For_RollBack Loop
       If (Rec.From_Lot <> Rec.To_Lot) then
            -- Truncate Device for HAT & HUS
            If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
               Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
            End if;       
              
            Unix_Sql := 'SPLTN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                        rpad(Rec.StepId,20,' ')||rpad(Rec.Description,30,' ')||
                        lpad(Rec.Qty,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                        rpad(Rec.Userid,12,' ');
            utl_file.put_line(edi_int_data,Unix_sql);      
       End if;
  End Loop;
  */

  --2006-Mar-17
  /*
  For Rec in Cutover_Split Loop
        -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;       

       If Substr(Rec.StepId,1,1) <> 'T' Then
            If (instr(Rec.From_Lot,'-') > 0 and instr(Rec.From_Lot,'-C') = 0 ) Then
                Rec.To_Lot := Rec.To_Lot || GET_SUBLOT_ID(Rec.From_Lot);
            End If;
       End if;

       Unix_Sql := 'SPLTN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                   --2006-Apr-21
                   --rpad(Rec.StepId,20,' ')||rpad(Step_Description,20,' ')||                   
                   rpad(Rec.StepId,20,' ')||rpad(Rec.Description,30,' ')||
                   lpad(Rec.Qty,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                   rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
  End Loop;
  */

  For Rec in Lot_Trackout Loop         
   If Substr(Rec.OutStep,1,2) = 'AD' or Substr(Rec.OutStep,1,2) = 'AF' or Substr(Rec.OutStep,1,2) = 'AL' or Substr(Rec.OutStep,1,1) = 'T' or Substr(Rec.OutStep,1,1) = 'W' Then
      vIndicator := '';
      
      --2006-Aug-28
      vTEST_TesterDataFile := '';
      vPROBE_TesterDataFile := '';
            
      vCustomerlotnumber := Rec.Customerlotnumber;
      If Substr(Rec.OutStep,1,1) = 'W' Then
         Record_type := 'PRBE';         
      end if;      
      If Substr(Rec.OutStep,1,1) = 'A' Then
         Record_type := 'ASSM';         
         Select MAX(nvl(Datecode || Tracecode,' ')), max(Alias) into vTracecode, vIndicator from fwcatns_lotorder
         where lotorderid = Rec.Lotorderid and Lotorderline = '300';
      end if;
      If Substr(Rec.OutStep,1,1) = 'T' Then
        --if Instr(Rec.Outstep,'PRE') > 0 or Instr(Rec.Outstep,'ETR') > 0 then
        if Instr(Rec.Outstep,'PRE') > 0 or Instr(Rec.Outstep,'INC') > 0 or Instr(Rec.Outstep,'ET') > 0 then        
         Record_type := 'TEST';
        else
         Record_type := 'TPRL';
        End if;
        
        Select MAX(nvl(Topmark01,' ')),max(Alias) into vTracecode,vIndicator from fwcatns_lotorder
        where lotorderid = Rec.Lotorderid and Lotorderline = '400';
        
        /* 2006-Oct-03 
        -- Pretest Out Customerlotnumber 
        Select nvl(Pretestoutcustomerlotnumber,Customerlotnumber) into vCustomerlotnumber
        from Fwcatns_DiesInventory D, Fwcatns_Testlotattr T
        where d.lotobject = t.lotobject and t.lotobject = Rec.lotobject;
        */
        
        --2006-Oct-03 
        If Instr(Rec.Outstep,'PRE') > 0 or Instr(Rec.Outstep,'INC') > 0 then        
           -- Pretest Out Customerlotnumber 
           Select nvl(Pretestoutcustomerlotnumber,Customerlotnumber) into vCustomerlotnumber
           from Fwcatns_DiesInventory D, Fwcatns_Testlotattr T
           where d.lotobject = t.lotobject and t.lotobject = Rec.lotobject;
        Else
           /*
           Select Customerlotnumber into vCustomerlotnumber
           from Fwcatns_DiesInventory D, Fwcatns_Testlotattr T
           where d.lotobject = t.lotobject and t.lotobject = Rec.lotobject;                
           */
           
           Select Customerlotnumber into vCustomerlotnumber
           from   Fwcatns_DiesInventory
           where  lotobject = Rec.lotobject;                           
        End if;
                    
      End if;
      
      vLF:=' ';
      vEpxy :=' ';
      vWr := ' ';
      vMcpd:=' ';
      If Record_type = 'ASSM' Then
         For vRec in Assy_Material(Rec.Lotorderid) Loop
             if substr(Rec.Outstep,1,6) = 'AF-DAT' and vRec.Materialgroup = 'LF' then
                vLF := vRec.MaterialId;
             elsif Substr(Rec.Outstep,1,6) = 'AF-DAT' and vRec.MaterialGroup = 'EPXY' then   
                vEpxy := vRec.MaterialId;                
             elsif Substr(Rec.Outstep,1,6) = 'AF-WBD' and vRec.Materialgroup = 'WR' then
                vWr :=  vRec.MaterialId;
             elsif Substr(Rec.Outstep,1,6) = 'AL-MOL' and vRec.MaterialGroup = 'MCPD' then
                vMcpd :=  vRec.MaterialId;
             end if;         
         End Loop;         
      End if;   
      
      vQuantityIn := '0';
      vEquipment := ' ';
      vTrackInTimestamp :='';      
      For vRec in Lot_TrackIn(Rec.Wipid,Rec.OutSTep) Loop
          vQuantityIn := vRec.QuantityIn;          
          vTrackInTimestamp := vRec.TrackInTimestamp;          
          If instr(Rec.OutStep,'AL-PAC') = 0 and Substr(Rec.Outstep,1,1) = 'A' then          
              vEquipment := vRec.Equipment;
          end if;              
--          IF Substr(Rec.OutStep,1,5) = 'T-ETR' Then
          IF Substr(Rec.OutStep,1,4) = 'T-ET' Then          
             vEquipment :=  ' ';
--          20050721 - to relfect Test equipment for T-ETR-QAB step           
--             For dRec in Test_Equipment (Rec.lotobject,Rec.OutStep) Loop
             For dRec in Test_Equipment (Rec.lotobject,substr(Rec.Outstep,1,INSTR(Rec.Outstep,'.')-1)) Loop
                 vEquipment := dRec.EquipmentId;
             end Loop;
          End if;   
          
          --IF Substr(Rec.OutStep,1,4) = 'W-PR' Then
          IF Substr(Rec.OutStep,1,4) = 'W-PR' or Substr(Rec.OutStep,1,4) = 'W-PH' Then                    
             vEquipment :=  ' ';
             For dRec in WP_Equipment (Rec.lotobject,Rec.OutStep) Loop
                 vEquipment := dRec.EquipmentId;
             end Loop;
          End if;            
      End Loop;         
      
      If substr(Rec.Outstep,1,5) = 'T-PRE' Then
          vQuantityIn := Rec.QuantityOut;
      end if;

      If substr(Rec.Outstep,1,5) = 'T-TNR' Then
          vQuantityIn := Rec.QuantityOut;
          For vRec in TNR_Equipment (Rec.lotobject, Rec.Outstep) Loop
              vEquipment := vRec.Equipmentid;
          End Loop;
      end if;

       -- 2005Feb23
      If ( Rec.Outstep like 'T-TNR%' or Rec.Outstep like 'T-SCN%' or Rec.Outstep like 'T-VME%' ) and Rec.Outstep not like 'T-TNR%-QA%' Then
          Select nvl(sum(testqasampleqty),'0') into vQuantityIn from fwcatns_finishingchildtxn
          Where Reellotid = Rec.Wipid and Step = (substr(Rec.Outstep,1,instr(Rec.Outstep,'.')-1)) and
          lottxncounter = (select max(lottxncounter) from fwcatns_finishingchildtxn a
                           Where a.Reellotid = Rec.Wipid and a.Step = (substr(Rec.Outstep,1,instr(Rec.Outstep,'.')-1)));          
          If vQuantityIn = '0' Then                 
            vQuantityIn := Rec.QuantityOut;                 
          End if;   
      End if;

      If substr(Rec.Outstep,1,6) = 'AF-DAT' Then
-- 2005-jul-08 due report incorrect Trackin qty.      
--          For vRec in Lot_DA_TrackIn (Rec.Wipid) Loop
--               vQuantityIn := vRec.SublotQuantity;
--          End Loop;
          vQuantityIn := Rec.QuantityOut;
          For vRec in Lot_TrackIn(substr(Rec.Wipid,1,13),Rec.outstep) Loop
               vEquipment := vRec.Equipment;            
          End Loop;    
      end if;
      
      Step_Description :=' ';
      vOutStep := Substr(Rec.OutStep,1,instr(Rec.Outstep,'.')-1);       
      For dRec in Step_Name(vOutstep) Loop
         Step_Description := dRec.Description;
         If Substr(Rec.OutStep,1,1) = 'W' Then
            If Substr(Upper(Step_Description),1,11) = 'WAFER PROBE' then
--               Step_Description := substr(dRec.Description, instr(dRec.Description,'Wafer Probe')+12);                       
               Step_Description :=  Trim(substr(dRec.Description,12,length(dRec.Description)));                            
            end if;   
         end if;         
      End Loop;   
      
      vLotnumber := Rec.WipId;
      if Substr(Rec.OutStep,1,2) = 'AD' and instr(Rec.Wipid,'-') = 0  then
         vLotnumber := Rec.Lotnumber;
      End If;

      -- Truncate Device for HAT & HUS
      If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
         Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
      End if;

      -- Engineering Lot Indicator @EBR
      If instr(vIndicator,'@EBR') > 0 Then vIndicator :='EL'; else vIndicator :=' ' ; End if; 

       --Leena 2005-Aug-25 Reqt by Philippa to default eqp as N/A if eqp is null
       if Trim(vequipment) is null or trim(vequipment) = '' then
        vequipment := 'N/A';
       end if;
     
      -- vOutStep := Substr(Rec.OutStep,1,instr(Rec.Outstep,'.')-1);       
       If Record_type ='ASSM' Then     
       If Rec.Outstep like 'AD-%' or Rec.OutStep like 'AF-%' Then
          Rec.Datecode :='NYA';
          vTraceCode :='NYA';
       End if;

       --2006-Mar-17
       If ((Substr(vLotnumber,1,3) = 'S20' Or Substr(vLotnumber,1,3) = 'M20') And Length(vLotnumber) >= 13) Then
          If (instr(vLotnumber,'-') > 0 and instr(vLotnumber,'-C') = 0 ) Then
              vCustomerLotnumber := vCustomerLotnumber || GET_SUBLOT_ID(vLotnumber);
          Else
              vCustomerLotnumber := GET_CUSTLOTNUM_BY_ID(vLotnumber);
          End If;
       End If;
                 
       Unix_Sql := Record_type || 'N'||rpad(vCustomerLotnumber,30,' ')
                   ||rpad(vLotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                   --2006-Apr-21                        
                   --rpad(vOutStep,20,' ')||rpad(Step_Description,10,' ')||
                   rpad(vOutStep,20,' ')||rpad(Step_Description,30,' ')||
                   lpad(vQuantityIn,7,'0')||lpad(Rec.QuantityOut,7,'0')||
                   rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.txntimestamp,10,6),6,' ')||
                   rpad(vIndicator,2,' ')||
                   rpad(vTracecode,10,' ')||rpad(Rec.Datecode,4,' ')||
                   rpad(Rec.Userid,12,' ')||rpad(vEquipment,13,' ')||
                   --2006-Apr-21
                   --rpad(' ',39,' ')||rpad(vLf,11,' ')||rpad(vEpxy,10,' ')|| rpad(vWr,10,' ')|| rpad(vMcpd,10,' ');
                   rpad(' ',39,' ')||rpad(vLf,30,' ')||rpad(vEpxy,30,' ')|| rpad(vWr,30,' ')|| rpad(vMcpd,30,' ');
                                             
       elsif Record_type = 'PRBE' then
       --2006-Mar-17
       /*
       -- 2005Sep02; Perform a dummy Split at W-PRE eg.SPLT CETX9 > S200533004600
       If vOutstep ='W-PRE' Then
          Unix_Sql := 'SPLTN'||rpad(vCustomerlotnumber,30,' ')||rpad(vLotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                      --2006-Apr-21
                      --rpad(vOutStep,20,' ')||rpad(Step_Description,10,' ')||
                      rpad(vOutStep,20,' ')||rpad(Step_Description,30,' ')||
                      lpad(vQuantityIn,7,'0')||rpad(substr(vTrackInTimestamp,1,8),8,' ')|| rpad(substr(vTrackInTimestamp,10,6),6,' ')||
                      rpad(Rec.Userid,12,' ');
          utl_file.put_line(edi_int_data,Unix_sql);                
       End if;
       */

       --2006-Aug-01   
       If vOutstep = 'W-PRE' Then
          If Rec.waferquantityOutwp is null Or Rec.waferquantityInwp is null Then
             If Rec.QuantityOut is not null Then
                Rec.waferquantityOutwp := Rec.QuantityOut;
                Rec.waferquantityInwp := Rec.QuantityOut;
             End If;
          End If;
       End If;
               
       vWaferScrapped := Abs(Rec.waferquantityOutwp - Rec.waferquantityInwp);

       --2006-Aug-28
       vPROBE_TesterDataFile := PROBE_TESTER_DATA_FILE(Rec.lotobject,vOutStep);
        
       --2006-Mar-24
       /*
       Unix_Sql := Record_type || 'N'||rpad(vCustomerLotnumber,30,' ')
                   ||rpad(vLotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                   rpad(vOutStep,20,' ')||rpad(Step_Description,10,' ')||
                   lpad(vQuantityIn,7,'0')||lpad(Rec.QuantityOut,7,'0')||lpad(vWaferScrapped,7,'0')||
                   rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.txntimestamp,10,6),6,' ')||
                   rpad(Rec.Userid,12,' ')||rpad(vEquipment,13,' ')||rpad(' ',39,' ');                             
      */

       --2006-Mar-24
       Unix_Sql := Record_type || 'N'||rpad(vCustomerLotnumber,30,' ')||
                   rpad(vLotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                   --2006-Apr-21
                   --rpad(vOutStep,20,' ')||rpad(Step_Description,10,' ')||
                   --lpad(Rec.waferquantityInwp,7,'0')||lpad(Rec.waferquantityOutwp,7,'0')||lpad(vWaferScrapped,7,'0')||                   
                   rpad(vOutStep,20,' ')||rpad(Step_Description,30,' ')||
                   lpad(Rec.waferquantityInwp,7,'0')||lpad(Rec.waferquantityOutwp,7,'0')||lpad(Rec.QuantityOut,7,'0')||lpad(vWaferScrapped,7,'0')||
                   rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.txntimestamp,10,6),6,' ')||
                   --2006-Aug-28
                   rpad(vPROBE_TesterDataFile,1,' ')||                                      
                   rpad(Rec.Userid,12,' ')||rpad(vEquipment,13,' ')||rpad(' ',39,' ');                             

       elsif Record_type = 'TEST' then
             vDieloss := Abs(Rec.QuantityOut - vQuantityIn);
       
             --2006-Aug-28
             vTEST_TesterDataFile := TEST_TESTER_DATA_FILE(Rec.lotobject,Rec.Wipid,vOutStep);

             --2006-Nov-14
             If  (vTEST_TesterDataFile = ' ') and
                 (Rec.OutStep like 'T-ET%') and (Rec.OutStep not like '%-COM-M%') and 
                 (Rec.OutStep not like '%-SAM%') and (Rec.OutStep not like '%QA%') Then

                 vExist_FinishingTxnCounter := 0;
                 
                 Select Count(*) into vExist_FinishingTxnCounter from fwcatns_finishingtxn
                 Where Reelmotherlotobject = Rec.lotobject;

                 If vExist_FinishingTxnCounter = 0 Then
                    vTEST_TesterDataFile := 'N';
                 End if;
             End If;
        
             Unix_Sql := Record_type || 'N'||rpad(vCustomerlotnumber,30,' ')
                         ||rpad(vLotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                         --2006-Apr-21
                         --rpad(vOutStep,20,' ')||rpad(Step_Description,10,' ')||
                         rpad(vOutStep,20,' ')||rpad(Step_Description,30,' ')||
                         lpad(vQuantityIn,7,'0')||lpad(Rec.QuantityOut,7,'0')||lpad(vDieloss,7,'0')||
                         rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.txntimestamp,10,6),6,' ')||
                         rpad(vIndicator,2,' ')||rpad(vTracecode,12,' ')||
                         --2006-Aug-28
                         rpad(vTEST_TesterDataFile,1,' ')||                   
                         rpad(Rec.Userid,12,' ')||rpad(vEquipment,13,' ')||rpad(' ',39,' ');
                                                      
       else                       
         -- 20050628-01 exclude Terminated Lot with componentqty > 0 
         If (Rec.processingstatus <> 'Terminated' or Rec.componentqty = 0 ) then 
           vDieloss := Abs(Rec.QuantityOut - vQuantityIn);
           
           --2006-Mar-17
           vCustomerlotnumber := GET_CUSTLOTNUM_BY_ID(vLotnumber);           
           
           Unix_Sql := Record_type || 'N'||rpad(vCustomerlotnumber,30,' ')
                       ||rpad(vLotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                       --2006-Apr-21
                       --rpad(vOutStep,20,' ')||rpad(Step_Description,10,' ')||
                       rpad(vOutStep,20,' ')||rpad(Step_Description,30,' ')||
                       lpad(vQuantityIn,7,'0')||lpad(Rec.QuantityOut,7,'0')||lpad(vDieloss,7,'0')||
                       --2006-Aug-28
                       --rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.txntimestamp,10,6),6,' ')||rpad(vTracecode,10,' ')||                       
                       rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.txntimestamp,10,6),6,' ')||rpad(vIndicator,2,' ')||rpad(vTracecode,10,' ')||                                          
                       rpad(' ',30, ' ')||rpad(Rec.Userid,12,' ')||rpad(vEquipment,13,' ')||rpad(' ',39,' ');
         End if;                                      
       end if;                      
       utl_file.put_line(edi_int_data,Unix_sql);
   End if;                      
  End loop;

   For Rec in Lot_Assy_Split Loop
       For dRec in Step_Name(Rec.StepId) Loop
           Step_Description := dRec.Description;
       End Loop;   
       
       -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;

       --2006-Mar-17
       If (instr(Rec.From_Lot,'-') > 0 and instr(Rec.From_Lot,'-C') = 0 ) Then
           Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot) || GET_SUBLOT_ID(Rec.From_Lot);
        Else
            Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot);           
       End If;

       If (instr(Rec.To_Lot,'-') > 0 and instr(Rec.To_Lot,'-C') = 0 ) Then
           Rec.To_Lot := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot) || GET_SUBLOT_ID(Rec.To_Lot);
        Else
           Rec.To_Lot := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot);
       End If;

       Unix_Sql := 'SPLTN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                    --2006-Apr-21
                    --rpad(Rec.StepId,20,' ')||rpad(Step_Description,10,' ')||
                    rpad(Rec.StepId,20,' ')||rpad(Step_Description,30,' ')||
                    lpad(Rec.Qty,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                    rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
  End Loop;

   --2006-Mar-17
   For Rec in Lot_Test_Split Loop
       For dRec in Step_Name(Rec.StepId) Loop
           Step_Description := dRec.Description;
       End Loop;   
       
       -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;

       Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot);               
       Rec.To_Lot := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot);      

       Unix_Sql := 'SPLTN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                    --2006-Apr-21
                    --rpad(Rec.StepId,20,' ')||rpad(Step_Description,10,' ')||
                    rpad(Rec.StepId,20,' ')||rpad(Step_Description,30,' ')||
                    lpad(Rec.Qty,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                    rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
  End Loop;

   For Rec in Lot_PreAssy_Split Loop
       For dRec in Step_Name(Rec.StepId) Loop
           Step_Description := dRec.Description;
       End Loop;   
       
       -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;

       Unix_Sql := 'SPLTN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                    --2006-Apr-21
                    --rpad(Rec.StepId,20,' ')||rpad(Step_Description,10,' ')||
                    rpad(Rec.StepId,20,' ')||rpad(Step_Description,30,' ')||
                    lpad(Rec.Qty,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                    rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
  End Loop;

  For Rec in Lot_Assy_Merge Loop
       For dRec in Step_Name(Rec.StepId) Loop
           Step_Description := dRec.Description;
       End Loop;   
       vQuantityIn := 0;
       For dRec in Lot_Assy_Merge_Qty(Rec.Sysid) Loop
           vQuantityIn := dRec.Qty;
       End Loop;

        -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;

       --2006-Mar-17       
       If Substr(Rec.StepId,1,1) <> 'T' Then
            If (instr(Rec.From_Lot,'-') > 0 and instr(Rec.From_Lot,'-C') = 0 ) Then
                Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot) || GET_SUBLOT_ID(Rec.From_Lot);
            End If;

            --2006-Mar-21
            If (instr(Rec.To_Lot,'-') > 0 and instr(Rec.To_Lot,'-C') = 0 ) Then
                Rec.To_Lot := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot) || GET_SUBLOT_ID(Rec.To_Lot);
            End If;
       Else
          Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot);
          Rec.To_Lot   := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot);              
       End if;
       
       Unix_Sql :=  'MRGEN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                    --2006-Apr-21
                    --rpad(Rec.StepId,20,' ')||rpad(Step_Description,10,' ')||
                    rpad(Rec.StepId,20,' ')||rpad(Step_Description,30,' ')||
                    lpad(vQuantityIn,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                    rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
       
  End Loop;

  For Rec in Lot_Finishing_Split Loop
  
       --2006-Nov-14
       v_RecordType := '    ';
         
       For dRec in Step_Name(Rec.StepId) Loop
           Step_Description := dRec.Description;
       End Loop;   

       --2006-Nov-14
       --If the Reel lot id NOT similiar to Child lot id then it should be the merge lot contributed
       If instr(Rec.To_Lot,Rec.From_Lot) <= 0 Then
          v_RecordType   := 'MRGE';
          --MRGE record must come after the SPLT recotrd for the reel, increase the actual lastupdate times with
          --10 more second to reflect this
          Rec.LastUpdate := to_char(to_date(Rec.LastUpdate,'YYYYMMDD HH24MISS') + 10/24/60/60*1,'YYYYMMDD HH24MISS');          
       Else
          v_RecordType := 'SPLT';                
       End if;       
        
        -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;       

       --2006-Mar-17                    
       Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot);
       Rec.To_Lot   := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot);
      
       --2006-Nov-14      
       --Unix_Sql := 'SPLTN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
       Unix_Sql := v_RecordType||'N'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                   --2006-Apr-21
                   --rpad(Rec.StepId,20,' ')||rpad(Step_Description,10,' ')||
                   rpad(Rec.StepId,20,' ')||rpad(Step_Description,30,' ')||
                   lpad(Rec.Qty,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                   rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
  End Loop;

   For Rec in Lot_Test_Merge Loop
       For dRec in Step_Name(Rec.StepId) Loop
           Step_Description := dRec.Description;
       End Loop;   

       --2006-Mar-17
       --Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot);
       --Rec.To_Lot   := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot);

       --2006-Mar-17
        If (instr(Rec.From_Lot,'-') > 0 and instr(Rec.From_Lot,'-C') = 0 ) Then
            Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot) || GET_SUBLOT_ID(Rec.From_Lot);
        Else
            Rec.From_Lot := GET_CUSTLOTNUM_BY_ID(Rec.From_Lot);
        End If;

        Rec.To_Lot := GET_CUSTLOTNUM_BY_ID(Rec.To_Lot); 
         
         -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;            
      
       Unix_Sql :=  'MRGEN'||rpad(Rec.From_Lot,30,' ')||rpad(Rec.To_Lot,30,' ')||rpad(Rec.Device,20,' ')||
                    --2006-Apr-21
                    --rpad(Rec.StepId,20,' ')||rpad(Step_Description,10,' ')||
                    rpad(Rec.StepId,20,' ')||rpad(Step_Description,30,' ')||
                    lpad(Rec.Qty,7,'0')||rpad(substr(Rec.LastUpdate,1,8),8,' ')|| rpad(substr(Rec.LastUpdate,10,6),6,' ')||
                    rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
  End Loop;
  
  For Rec in Lot_Assy_Ship Loop
       If Rec.WareHouse = 'E20-26' or Rec.WareHouse = 'H12-5' or Rec.WareHouse ='H16-11' Then
          Rec.WareHouse := 'CAM';
       Elsif Rec.WareHouse = 'E20' Then
          Rec.WareHouse :='ELM';                    
       Elsif Rec.WareHouse = 'E20-20' Then
          Rec.WareHouse :='UNM';          
       Elsif Rec.WareHouse = 'E20-23' Then
          Rec.WareHouse :='MY4';          
       Elsif Rec.WareHouse ='H12-4' Then
          Rec.WareHouse :='ATP';
       Elsif Rec.WareHouse ='HUS' or Rec.WareHouse ='H12-3' or Rec.WareHouse = 'H12-2' or Rec.WareHouse = 'H12-1' or Rec.WareHouse = 'H12-3' Then
          Rec.WareHouse :='ISP';
       Elsif Rec.WareHouse = 'HAT' or Rec.WareHouse= 'H16-3' Then
          Rec.WareHouse := 'CPM';   
       Elsif Rec.WareHouse = 'H16-15' Then
          Rec.WareHouse := 'ENG/TX';
       Elsif Rec.WareHouse = 'H16-12' Then
          Rec.WareHouse := 'ENG/NC';
       Elsif Rec.WareHouse = 'H16-5' Then
          Rec.WareHouse := 'ENG/FL';
       Elsif Rec.WareHouse = 'H16-14' Then
          Rec.WareHouse := 'ENG/KOREA'; 
       Elsif Rec.WareHouse ='H12-12' Then
          Rec.WareHouse := 'ASP';
       --2006-Apr-19           
       Elsif Rec.WareHouse ='H12-17' Then
          Rec.WareHouse := 'MY4';
       end if;
  
       -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;

       --2006-Mar-17
        If (instr(Rec.wipid,'-') > 0 and instr(Rec.wipid,'-C') = 0 ) Then
            Rec.wipid := GET_CUSTLOTNUM_BY_ID(Rec.wipid) || GET_SUBLOT_ID(Rec.wipid);
        Else
            Rec.wipid := GET_CUSTLOTNUM_BY_ID(Rec.wipid);
        End If;

       --2006-Mar-17
       /*
       Unix_Sql := 'SPLTN'||rpad(Rec.wipid,30,' ')||rpad(Rec.Lotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                      rpad('AL-FGD',20,' ')||rpad('Asy Finished Goods',10,' ')||
                      lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.actualshipmentdate,1,8),8,' ')|| rpad(substr(Rec.actualshipmentdate,10,6),6,' ')||
                      rpad(Rec.Username,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
       */

       --2006-Mar-17      
       --Unix_Sql := 'SHIPN'||rpad(Rec.Lotnumber,30,' ')||
       Unix_Sql := 'SHIPN'||rpad(Rec.wipid,30,' ')||
                   --rpad(Rec.Wipid,30,' ')||
                   rpad(Rec.Device,20,' ')||
                   --lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.Actualshipmentdate,1,8),8,' ')||rpad(substr(Rec.Actualshipmentdate,10,6),6,' ')||
                   lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.Actualshipmentdate,1,8),8,' ')||rpad(substr(Rec.Actualshipmentdate,9,6),6,' ')||
                   rpad(Rec.WareHouse,20,' ');
                   utl_file.put_line(edi_int_data,Unix_sql);                   
   End Loop;    

  For Rec in Lot_WProbe_Ship Loop
       If Rec.WareHouse = 'E20-26' or Rec.WareHouse = 'H12-5' or Rec.WareHouse ='H16-11' Then
          Rec.WareHouse := 'CAM';         
       Elsif Rec.WareHouse = 'H12-1' Then          
          Rec.WareHouse := 'ISP';
       Elsif Rec.WareHouse = 'E20' Then
          Rec.WareHouse :='ELM';                    
       Elsif Rec.WareHouse = 'HAT' or Rec.WareHouse= 'H12' Then
          Rec.WareHouse := 'CPM';
       Elsif Rec.WareHouse = 'H12-4' or Rec.WareHouse = 'H12-7' Then
           Rec.WareHouse := 'ATP';          
       Elsif Rec.WareHouse = 'H12-9' Then
           Rec.WareHouse := 'CPS';
       Elsif Rec.WareHouse = 'H12-10' Then
           Rec.WareHouse :='UNM';
       Elsif Rec.WareHouse ='H12-6' Then
           Rec.WareHouse :='ATK';
       Elsif Rec.WareHouse ='H12-12' Then
          Rec.WareHouse := 'ASP';
       --2006-Apr-19           
       Elsif Rec.WareHouse ='H12-17' Then
          Rec.WareHouse := 'MY4';                      
       end if; 

       --2006-Mar-17
       /*
       -- 2005Aug29; perform a split before ship transaction
       -- SPLT S200533004600 > CETX9.1
       -- SHIP CETX9.1
       Unix_Sql := 'SPLTN'||rpad(Rec.wipid,30,' ')||rpad(Rec.Lotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                    --2006-Apr-21
                    --rpad('W-FGD',20,' ')||rpad('WProbe Store',10,' ')||
                    rpad('W-FGD',20,' ')||rpad('WProbe Store',30,' ')||
                    lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.wpactualshipmentdate,1,8),8,' ')|| rpad(substr(Rec.wpactualshipmentdate,10,6),6,' ')||
                    rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
       */
      
       Unix_Sql := 'SHIPN'||--rpad(Rec.Lotnumber,30,' ')||
                   rpad(Rec.Lotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                   lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.WPActualshipmentdate,1,8),8,' ')||rpad(substr(Rec.WPActualshipmentdate,10,6),6,' ')||
                   rpad(Rec.WareHouse,20,' ');
       utl_file.put_line(edi_int_data,Unix_sql);
   End Loop;    

  For Rec in Lot_Test_Ship Loop         
    vExist_Counter :=0;
    --2006-Mar-17 To be unremark
    Select Count(*) into vExist_Counter from edi_intersil_data Where Wipid = Rec.Wipid;
    --Select Count(*) into vExist_Counter from edi_intersil_data2 Where Wipid = Rec.Wipid;
    If vExist_Counter = 0 Then        
       If Rec.WareHouse = 'E20-26' or Rec.WareHouse = 'H12-5' or Rec.WareHouse ='H16-11' Then
          Rec.WareHouse := 'CAM';
       Elsif Rec.WareHouse = 'E20' Then
          Rec.WareHouse :='ELM';                    
       Elsif Rec.WareHouse = 'E20-20' Then
          Rec.WareHouse :='UNM';          
       Elsif Rec.WareHouse = 'E20-23' Then
          Rec.WareHouse :='MY4';          
       Elsif Rec.WareHouse ='H12-4' Then
          Rec.WareHouse :='ATP';
       Elsif Rec.WareHouse ='HUS' or Rec.WareHouse ='H12-3' or Rec.WareHouse = 'H12-2' or Rec.WareHouse = 'H12-1' or Rec.WareHouse = 'H12-3' Then
          Rec.WareHouse :='ISP';
       Elsif Rec.WareHouse = 'HAT' or Rec.WareHouse= 'H16-3' Then
          Rec.WareHouse := 'CPM';   
       Elsif Rec.WareHouse = 'H16-15' Then
          Rec.WareHouse := 'ENG/TX';
       Elsif Rec.WareHouse = 'H16-12' Then
          Rec.WareHouse := 'ENG/NC';
       Elsif Rec.WareHouse = 'H16-5' Then
          Rec.WareHouse := 'ENG/FL';
       Elsif Rec.WareHouse = 'H16-14' Then
          Rec.WareHouse := 'ENG/KOREA'; 
       Elsif Rec.WareHouse ='H12-12' Then
          Rec.WareHouse := 'ASP';
       --2006-Apr-19           
       Elsif Rec.WareHouse ='H12-17' Then
          Rec.WareHouse := 'MY4';                  
       end if;  

       -- Truncate Device for HAT & HUS
       If instr(Rec.TestDevice,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.TestDevice := SubStr(Rec.Testdevice,instr(Rec.Testdevice,'/')+1,length(Rec.Testdevice));
       End if;      

       --2006-Mar-17
       /*
       Unix_Sql := 'SPLTN'||rpad(Rec.wipid,30,' ')||rpad(Rec.Lotnumber,30,' ')||rpad(Rec.testDevice,20,' ')||
                      rpad('T-FGS',20,' ')||rpad('Finished Good Store',10,' ')||
                      lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.Tstactualshipmentdate,1,8),8,' ')|| rpad(substr(Rec.tstactualshipmentdate,10,6),6,' ')||
                      rpad(Rec.Userid,12,' ');
       utl_file.put_line(edi_int_data,Unix_sql);      
       */

       Unix_Sql := 'SHIPN'||rpad(Rec.Lotnumber,30,' ')||
                   --rpad(Rec.Wipid,30,' ')||
                   rpad(Rec.testDevice,20,' ')||
                   --lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.Tstactualshipmentdate,1,8),8,' ')|| rpad(substr(Rec.Tstactualshipmentdate,10,6),6,' ')||
                   lpad(Rec.ComponentQty,7,'0')||rpad(substr(Rec.Tstactualshipmentdate,1,8),8,' ')|| rpad(substr(Rec.Tstactualshipmentdate,9,6),6,' ')||
                   rpad(Rec.WareHouse,20,' ');
                   utl_file.put_line(edi_int_data,Unix_sql);

        -- 2005Oct19;emmy filter repeated shipment record
        /*
        Insert into edi_intersil_data2
        (lotnumber,wipid,componentqty,tstactualshipmentdate,tstinvoiceno,txntimestamp)
        values (Rec.Lotnumber,Rec.Wipid,Rec.ComponentQty,Rec.Tstactualshipmentdate,Rec.tstinvoiceno,to_sysdate);        
        */
        
        --2006-Mar-17 To be unremark
        Insert into edi_intersil_data
        (lotnumber,wipid,componentqty,tstactualshipmentdate,tstinvoiceno,txntimestamp)
        values (Rec.Lotnumber,Rec.Wipid,Rec.ComponentQty,Rec.Tstactualshipmentdate,Rec.tstinvoiceno,to_sysdate);        

      End if;
  End Loop;    
  Commit;

  --2006-Oct-16 
  /*
  For Rec in Shipment Loop
       If Rec.PDC_To = 'E20-26' or Rec.PDC_To = 'H12-5' or Rec.PDC_To ='H16-11' Then
          Rec.PDC_To := 'CAM';
       Elsif Rec.PDC_To = 'E20' Then
          Rec.PDC_To :='ELM';                    
       Elsif Rec.PDC_To = 'E20-20' Then
          Rec.PDC_To :='UNM';          
       Elsif Rec.PDC_To = 'E20-23' Then
          Rec.PDC_To :='MY4';          
       Elsif Rec.PDC_To ='H12-4' Then
          Rec.PDC_To :='ATP';
       Elsif Rec.PDC_To ='H16' or Rec.PDC_To ='H12-3' or Rec.PDC_To = 'H12-2' or Rec.PDC_To = 'H12-1' or Rec.PDC_To = 'H12-3' Then
          Rec.PDC_To :='ISP';
       Elsif Rec.PDC_To = 'H12' or Rec.PDC_To = 'H16-3' Then
          Rec.PDC_To := 'CPM';   
       --2006-Apr-19           
       Elsif Rec.PDC_To ='H12-17' Then
          Rec.PDC_To := 'MY4';          
       Else
          Rec.PDC_To := Rec.CustomerCode;
       end if;

         -- Truncate Device for HAT & HUS
       If instr(Rec.Device,'/') > 0 and ( Rec.CustomerCode ='HAT' or Rec.CustomerCode ='HUS' ) Then
          Rec.Device := SubStr(Rec.device,instr(Rec.device,'/')+1,length(Rec.device));
       End if;       
       
       Unix_Sql := 'SHIPN'||rpad(Rec.Tran_Type,13,' ')||rpad(Rec.Lotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                   lpad(Rec.Quantity,7,'0')||rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.Txntimestamp,9,6),6,' ')||
                   rpad('CAS',6,' ')||rpad(Rec.PDC_To,6,' ')||rpad(' ',10,' ')||rpad(Rec.Mawb,30,' ')||rpad(Rec.Motherlot,25,' ')||
                   rpad(' ',14,' ');
                   utl_file.put_line(edi_shp_data,Unix_sql);

      --2006-Mar-22                   
      /*
      Unix_Sql := 'SHIPN'||rpad(Rec.Tran_Type,13,' ')||rpad(Rec.Customerlotnumber,30,' ')||rpad(Rec.Device,20,' ')||
                  lpad(Rec.Quantity,7,'0')||rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.Txntimestamp,9,6),6,' ')||
                  rpad('CAS',6,' ')||rpad(Rec.PDC_To,6,' ')||rpad(' ',10,' ')||rpad(Rec.Mawb,30,' ')||rpad(Rec.Motherlot,25,' ')||
                  rpad(' ',14,' ');
                  utl_file.put_line(edi_int_data,Unix_sql);
      */

      --2006-Oct-16
      /*
      --2006-Mar-17       
      If Substr(Rec.lastlocation,1,1) <> 'T' Then
         If (instr(Rec.Lotnumber,'-') > 0 and instr(Rec.Lotnumber,'-C') = 0) Then
             Rec.Lotnumber:= GET_CUSTLOTNUM_BY_ID(Rec.Lotnumber) || GET_SUBLOT_ID(Rec.Lotnumber);
         Else
             Rec.Lotnumber := GET_CUSTLOTNUM_BY_ID(Rec.Lotnumber); 
         End If;
      Else
         Rec.Lotnumber := GET_CUSTLOTNUM_BY_ID(Rec.Lotnumber);       
      End If;

      --2006-Mar-22
      --2006-Mar-17       
      --Unix_Sql := 'SHIPN'||rpad(Rec.Customerlotnumber,30,' ')||
      Unix_Sql := 'SHIPN'||rpad(Rec.Lotnumber,30,' ')||
                  rpad(Rec.Device,20,' ')||
                  lpad(Rec.Quantity,7,'0')||rpad(substr(Rec.Txntimestamp,1,8),8,' ')|| rpad(substr(Rec.Txntimestamp,9,6),6,' ')||
                  rpad(Rec.PDC_To,20,' ');
                  utl_file.put_line(edi_int_data,Unix_sql);
                    
  End Loop;    
  --2006-Oct-16
  */

--2006-Mar-17 To be unremark
 filename3 := 'toftp.hatm.'||to_char(sysdate,'YYYYMMDDHH24MISS'); 
 select CS_UTILS.GET_UTL_PATH into vpath from dual;
 edi_int_ftp  := utl_file.fopen(vpath,filename3,'W'); 
 Unix_Sql := '-sfile='||filename||' -ddir=/carsemftp -dfile='||filename||' -user=intersil\\/carsemftp -pass=40t+bqR -ip=192.157.179.34';  
-- Unix_Sql := '-sfile='||filename||' -ddir=/carsem -dfile='||filename||' -user=intersil\\/carsemftp -pass=40t+bqR -ip=192.157.179.7';   
 Utl_File.put_line(edi_int_ftp,Unix_Sql);

 /* 2006-Oct-16
 filename3 := 'toftp.hats.'||to_char(sysdate,'YYYYMMDDHH24MISS'); 
 select CS_UTILS.GET_UTL_PATH into vpath from dual;
 edi_int_ftp2  := utl_file.fopen(vpath,filename3,'W'); 
 Unix_Sql := '-sfile='||filename2||' -ddir=/carsemftp -dfile='||filename2||' -user=intersil\\/carsemftp -pass=40t+bqR -ip=192.157.179.34';  
-- Unix_Sql := '-sfile='||filename2||' -ddir=/carsem -dfile='||filename2||' -user=intersil\\/carsemftp -pass=40t+bqR -ip=192.157.179.7';   
 Utl_File.put_line(edi_int_ftp2,Unix_Sql);
 */

/*
 Update edi_wipfeed_trml2
 Set txntimestamp = To_sysdate
 Where customerlotnumber ='EDI_INTERSIL_UPD';
 Commit;
*/

 Update edi_wipfeed_trml
 Set txntimestamp = To_sysdate 
 Where customerlotnumber ='EDI_INTERSIL_UPD';
 Commit;

 --2006-Nov-15
 filename4 := 'toftp.hatmInt.'||to_char(sysdate,'YYYYMMDDHH24MISS'); 
 select CS_UTILS.GET_UTL_PATH into vpath from dual;
 edi_int_ftp4  := utl_file.fopen(vpath,filename4,'W'); 
 Unix_Sql := '-sfile='||filename||' -ddir=/isiledi -dfile='||filename||' -user=isiledi -pass=ftpisiledi -ip=128.10.200.200'; 
 Utl_File.put_line(edi_int_ftp4,Unix_Sql);

 v_msg := 'Close Unix File';
 utl_file.fclose(edi_int_data);
 --2006-Oct-16 
 --utl_file.fclose(edi_shp_data);

 --2006-Mar-17 To be unremark
 utl_file.fclose(edi_int_ftp); 
 --2006-Oct-16
 --utl_file.fclose(edi_int_ftp2);

 --2006-Nov-15 
 utl_file.fclose(edi_int_ftp4); 
 
 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.');
  
EXCEPTION
    when utl_file.internal_error then
         dbms_output.put_line
             ('utl_file; an internal error occurred.');
         utl_file.fclose_all;
    when utl_file.invalid_filehandle then
         dbms_output.put_line
              ('utl_file: the file handle was invalid.');
           utl_file.fclose_all;
    when utl_file.invalid_mode then
         dbms_output.put_line
              ('utl_file: an invalid open mode was given ');
         utl_file.fclose_all;
    when utl_file.invalid_operation then
         dbms_output.put_line
              ('utl_file: an invalid operation was attempted.'||   v_msg );
         utl_file.fclose_all;
    when utl_file.invalid_path then
         dbms_output.put_line
              ('utl_file: an invalid path was given for the file .');
         utl_file.fclose_all;
    when utl_file.read_error then
         dbms_output.put_line
              ('utl_file: a read error occurred.');
         utl_file.fclose_all;
    when utl_file.write_error then
         dbms_output.put_line
              ('utl_file: a write error occurred.');
         utl_file.fclose_all;
    when others then
         dbms_output.put_line
              ('some other error occurred.'|| v_msg || SUBSTR(SQLERRM, 1, 255));
         utl_file.fclose_all;

END; -- Procedure