--get a dataset of the batches in the test company that do not exist in the live company
--we only expect GL batches, this script does not cover the subledgers
select b.BACHNUMB
into #tmp
from test..sy00500 b
left join prh..sy00500 b2 on b.BACHNUMB = b2.BACHNUMB and b.BCHSOURC = b2.BCHSOURC
where b2.BACHNUMB is null
order by b.BACHNUMB
--move the GL header lines
insert into PRH..gl10000 (BACHNUMB ,BCHSOURC,JRNENTRY,RCTRXSEQ,SOURCDOC,REFRENCE,TRXDATE,RVRSNGDT,RCRNGTRX,BALFRCLC,PSTGSTUS,LASTUSER,LSTDTEDT,USWHPSTD,TRXTYPE,SQNCLINE,GLHDRMSG,GLHDRMS2,TRXSORCE,RVTRXSRC,SERIES,ORPSTDDT,ORTRXSRC,OrigDTASeries,DTAControlNum,DTATRXType,DTA_Index,CURNCYID,CURRNIDX,RATETPID,EXGTBLID,XCHGRATE,EXCHDATE,TIME1,RTCLCMTD,NOTEINDX,GLHDRVAL,PERIODID,OPENYEAR,CLOSEDYR,HISTRX,REVPRDID,REVYEAR,REVCLYR,REVHIST,ERRSTATE,ICTRX,ORCOMID,ORIGINJE,ICDISTS,PRNTSTUS,DENXRATE,MCTRXSTT,DOCDATE,Tax_Date,VOIDED,Original_JE,Original_JE_Year,Original_JE_Seq_Num,Correcting_Trx_Type,Ledger_ID,Adjustment_Transaction)
select gt.BACHNUMB,BCHSOURC,JRNENTRY,RCTRXSEQ,SOURCDOC,REFRENCE,TRXDATE,RVRSNGDT,RCRNGTRX,BALFRCLC,PSTGSTUS,LASTUSER,LSTDTEDT,USWHPSTD,TRXTYPE,SQNCLINE,GLHDRMSG,GLHDRMS2,TRXSORCE,RVTRXSRC,SERIES,ORPSTDDT,ORTRXSRC,OrigDTASeries,DTAControlNum,DTATRXType,DTA_Index,CURNCYID,CURRNIDX,RATETPID,EXGTBLID,XCHGRATE,EXCHDATE,TIME1,RTCLCMTD,NOTEINDX,GLHDRVAL,PERIODID,OPENYEAR,CLOSEDYR,HISTRX,REVPRDID,REVYEAR,REVCLYR,REVHIST,ERRSTATE,ICTRX,ORCOMID,ORIGINJE,ICDISTS,PRNTSTUS,DENXRATE,MCTRXSTT,DOCDATE,Tax_Date,VOIDED,Original_JE,Original_JE_Year,Original_JE_Seq_Num,Correcting_Trx_Type,Ledger_ID,Adjustment_Transaction
from #tmp t
left join test..gl10000 gt on t.BACHNUMB = gt.BACHNUMB
--move the GL lines
insert into prh..gl10001 (BACHNUMB ,JRNENTRY ,SQNCLINE ,ACTINDX ,XCHGRATE ,DSCRIPTN ,GLLINMSG ,GLLINMS2 ,CURRNIDX ,GLLINVAL ,ACCTTYPE ,FXDORVAR ,BALFRCLC ,PSTNGTYP ,DECPLACS ,ORCTRNUM ,ORDOCNUM ,ORMSTRID ,ORMSTRNM ,ORTRXTYP ,OrigSeqNum ,ORTRXDESC ,DTA_GL_Status ,INTERID ,RATETPID ,EXGTBLID ,EXCHDATE ,TIME1 ,RTCLCMTD ,CRDTAMNT ,DEBITAMT ,ORCRDAMT ,ORDBTAMT ,DENXRATE ,MCTRXSTT ,LNESTAT ,CorrespondingUnit)
select gl.BACHNUMB,gl.JRNENTRY,gl.SQNCLINE,gl.ACTINDX,gl.XCHGRATE,gl.DSCRIPTN,gl.GLLINMSG,gl.GLLINMS2,gl.CURRNIDX,gl.GLLINVAL,gl.ACCTTYPE,gl.FXDORVAR,gl.BALFRCLC,gl.PSTNGTYP,gl.DECPLACS,gl.ORCTRNUM,gl.ORDOCNUM,gl.ORMSTRID,gl.ORMSTRNM,gl.ORTRXTYP,gl.OrigSeqNum,gl.ORTRXDESC,gl.DTA_GL_Status,gl.INTERID,gl.RATETPID,gl.EXGTBLID,gl.EXCHDATE,gl.TIME1,gl.RTCLCMTD,gl.CRDTAMNT,gl.DEBITAMT,gl.ORCRDAMT,gl.ORDBTAMT,gl.DENXRATE,gl.MCTRXSTT,gl.LNESTAT,gl.CorrespondingUnit
from #tmp t
join test..gl10000 gt on t.BACHNUMB = gt.BACHNUMB
join test..gl10001 gl on gl.JRNENTRY = gt.JRNENTRY
left join PRH..gl10001 gl2 on gl2.JRNENTRY = gl.JRNENTRY and gl2.SQNCLINE = gl.SQNCLINE
--be sure the lines are not already there
where gl2.JRNENTRY is null
order by t.BACHNUMB, gl.JRNENTRY
--move the batches
insert into prh..sy00500 (GLPOSTDT,BCHSOURC,BACHNUMB ,SERIES,MKDTOPST,NUMOFTRX,RECPSTGS,DELBACH,MSCBDINC,BACHFREQ,RCLPSTDT,NOFPSTGS,BCHCOMNT,BRKDNALL,CHKSPRTD,RVRSBACH,USERID,CHEKBKID,BCHTOTAL,BCHEMSG1,BCHEMSG2,BACHDATE,BCHSTRG1,BCHSTRG2,POSTTOGL,MODIFDT,CREATDDT,NOTEINDX,CURNCYID,BCHSTTUS,CNTRLTRX,CNTRLTOT,PETRXCNT,APPROVL,APPRVLDT,APRVLUSERID,ORIGIN,ERRSTATE,GLBCHVAL,Computer_Check_Doc_Date,Sort_Checks_By,SEPRMTNC,REPRNTED,CHKFRMTS,TRXSORCE,PmtMethod,EFTFileFormat,Workflow_Approval_Status,Workflow_Priority,TIME1,ClearRecAmts,PurchasingPrepaymentBch)
select GLPOSTDT,BCHSOURC,t.BACHNUMB,SERIES,MKDTOPST,NUMOFTRX,RECPSTGS,DELBACH,MSCBDINC,BACHFREQ,RCLPSTDT,NOFPSTGS,BCHCOMNT,BRKDNALL,CHKSPRTD,RVRSBACH,USERID,CHEKBKID,BCHTOTAL,BCHEMSG1,BCHEMSG2,BACHDATE,BCHSTRG1,BCHSTRG2,POSTTOGL,MODIFDT,CREATDDT,NOTEINDX,CURNCYID,BCHSTTUS,CNTRLTRX,CNTRLTOT,PETRXCNT,APPROVL,APPRVLDT,APRVLUSERID,ORIGIN,ERRSTATE,GLBCHVAL,Computer_Check_Doc_Date,Sort_Checks_By,SEPRMTNC,REPRNTED,CHKFRMTS,TRXSORCE,PmtMethod,EFTFileFormat,Workflow_Approval_Status,Workflow_Priority,TIME1,ClearRecAmts,PurchasingPrepaymentBch
from #tmp t
join sy00500 s on s.bachnumb = t.BACHNUMB