Public Function OPGF_InsertInvoiceEntry(ByVal VsRecUserID As String, _
                                        Optional ByVal dtStartDate As Date, _
                                        Optional ByVal dtFinishDate As Date, _
                                        Optional ByRef RsReportStr As String, _
                                        Optional ByVal ViTranNo As Long) As Boolean
'*******************************************************************************************
' Function Name : OPGF_InsertInvoiceEntry
' Description   : Insert/Update an Invoice in Finance
' Arguments     : VsRecuserId = User Id Of BME,
'                 Optional dtStartDate,dtFinishDate = In case the user wants to transfer based on Start and end date
'                 RsReportStr = Returns the status or any error generated in string Format
'                 Optional viTranNo= Used in Case specific Invoice needs to be transferred
' Return Value  : "True" In case of Successful Transfer "False" In case of failure
' Creator       :
' Modification  :
' Modified by   :
'*******************************************************************************************
'Recordsets Declarations
Dim rsARHdr As ADODB.Recordset      ' Recordset for Invoice Header(ARHDR) of BME
Dim PsReportStr As String           ' Return report String
Dim rsARLIN As ADODB.Recordset      ' Recordset for Invoice Detail(ARLIN) of BME
Dim PlNextTrnNo As Long             ' variable to store Last Transaction No
Dim PiInvoiceCounter As Integer     ' Counter to check the no of Invoice updated to fin
Dim PiNo_Of_Invoice As Integer      ' No of Invoice to update
Dim pbsuccess As Boolean            ' To Store return Value of the Function
Dim PsSQLstr As String              ' To Store SQL Query Strings
Dim PsRecDate As String             ' Insert Record Date
Dim PsRectime As String             ' Insert Record Time
Dim PiFor_Counter As Integer        ' For Loop Variable
Dim PsJrnlBatchNo As String         ' Finance Batch Number
Dim Psjrnlkey As String             ' Finance Series For Batch Number
Dim PiInterrno As Integer           ' Error Number
Dim PsBatchComment As String        ' Finance Batch Number Series Description
Dim PbFirstTime As Boolean
Dim PbsecondTime As Boolean
Dim PiInvoiceType As Integer
Dim PsInvoiceAbbr As String
Dim PcolSubsyskey As New Collection
Dim psAccountKey() As String
Dim pbUpLoadCommission As Boolean       ' Whether Commission is needs to be Upload or Not
'Changes By
Dim piErrCount As Integer
'Accounts
Dim pbLineLevelTax As Boolean 'Line level tax
Dim pdTaxTotal As Double 'Invoice Tax Total
Dim pdDocTotal As Double 'Invoice Total
Dim pdDocFreightAmt As Double 'Invoice Fright Amount
Dim pdDocDiscAmt As Double 'Invoive Total Discount
Dim psFinTranNo As String 'Finance Transaction Number
Dim VouchtotalZero As Boolean
Dim psVatOrFlat As String 'Tax Type Implemented In BME


On Error GoTo ErrHandler1

GsFunctionName = MCON_ModuleName & "OPGF_InsertInvoiceEntry" & GENTRY


pbsuccess = True
PbFirstTime = True
PbsecondTime = False
pbLineLevelTax = False      'Variable to Store Tax if it is Define as Line Level

'find out the Tax type implemented in BME
psVatOrFlat = BMLFGetTaxType_Flat_Or_Vat


' recordsets Initialisations

Set rsARHdr = New ADODB.Recordset
Set rsARLIN = New ADODB.Recordset

'Fetch all the records from invoice (ARHDR) that are  not yet uploaded to the Finance

PsSQLstr = "SELECT  ARHDR.* ,ARCLASS.Finchrg_Acct,ARCLASS.Rev_Acct,ARCLASS.Cash_Acct, " & _
                   "ARCLASS.Ar_Acct,ARCLASS.Freight_Acct,ARCLASS.Disc_Tk_Acct, " & _
                   "ARCLASS.Disc_Gv_Acct,CMTAX.Txapacctl1,CMTAX.Txapacctl2,CMTAX.Txapacctl3," & _
                   "CMTAX.Txapacctl4,CMTAX.Txratel1,CMTAX.Txratel2, CMTAX.Txratel3, " & _
           "CMTAX.Txratel4,CMTERMS.Description,CMTERMS.Disc_Days,CMTERMS.Perc_Dscnt, " & _
           "CMTERMS.Due_Days " & _
    " FROM ARCLASS JOIN ARCUST " & _
            "on ARCUST.Cust_Class_Ky =ARCLASS.Class_Key " & _
        " join ARHDR " & _
        "on ARCUST.Customer_Key =ARHDR.Custkey " & _
        " LEFT JOIN CMTAX " & _
        "on (ARHDR.TaxKey = CMTAX.Txratekey AND CMTAX.AR_AP = 'AR') " & _
    "LEFT JOIN CMTERMS on (ARHDR.Termskey=CMTERMS.Terms_Key AND CMTERMS.AR_AP = 'AR') "
    
'condition ARHDR.UpdatedtoFin = '0' means Not Uploaded to Finance
PsSQLstr = PsSQLstr & " Where ARHDR.UpdatedtoFin = '0'"

'For Specific Invoice number
If ViTranNo > 0 Then
   PsSQLstr = PsSQLstr & " AND  ARHDR.tranno = " & ViTranNo & ""
ElseIf Not IsEmpty(dtStartDate) Then 'to Fetch In the Range
   PsSQLstr = PsSQLstr & " AND  ARHDR.RecDate >= '" & Format(CStr(dtStartDate), "DD-MMM-YYYY") & "' AND ARHDR.RecDate <= '" & Format(CStr(dtFinishDate), "DD-MMM-YYYY") & "' "
End If
'Open recordset
pbsuccess = ObjMfgDl.OPLFFinOpenRecordset(rsARHdr, PsSQLstr, RsReportStr)

If Not pbsuccess Then
    RsReportStr = RsReportStr & vbCrLf & "Unable to get Invoice Details !"
    'RsReportStr = RsReportStr & vbCrLf & OPGFGetResString(47185)
    GoTo ExitHandler
End If

' Check if there is any Record in recordset
If rsARHdr.RecordCount = 0 Then
   PsReportStr = "                              No Data To Process" & vbCrLf & vbCrLf
   'ReportStr = OPGFGetResString(47135) & vbCrLf & vbCrLf
   GoTo ExitHandler
End If

'  08/07/01
'Get Default Setting from Distribution Parameter setup whether Commision is to be Uploaded
pbsuccess = OPLF_CheckForCommissionUpload(pbUpLoadCommission)
If Not pbsuccess Then GoTo ExitHandler

SECONDROUND:
If PbFirstTime = True Then
   rsARHdr.Filter = "Crmemoflg = 1"
   PbFirstTime = False
   PiInvoiceType = 1
Else
   rsARHdr.Filter = "Crmemoflg = -1"
   PbsecondTime = True
   PiInvoiceType = 2
End If

'Loop for all the Selected Invoice

For PiFor_Counter = 0 To rsARHdr.RecordCount - 1
    
    If PiInvoiceCounter = 50 Then
       PiInvoiceCounter = 0 ' If Invoice counter is 50 reinitialise it to 0
    End If
    
    PiInvoiceCounter = PiInvoiceCounter + 1
    PlNextTrnNo = PlNextTrnNo + 1
    'Get Finance Batch Number
    pbsuccess = OPLF_GetFinJrnlBatchNo(PsJrnlBatchNo, Psjrnlkey, "OS_" & rsARHdr.Fields("Tranno").Value, VsRecUserID, PsRecDate, PsRectime, PiInterrno, PsBatchComment, "Invoice Entry")
    If Not pbsuccess Then
            Select Case PiInterrno
                  Case 2
                       PsReportStr = "Maintain JrnlBatch Finance series in Distribution Parameters" & vbCrLf
                       'PsReportStr = OPGFGetResString(47138) & vbCrLf
                  Case 3
                       PsReportStr = "Primary Key Voilation in Table Sy00500" & vbCrLf
                       'PsReportStr = OPGFGetResString(47126) & vbCrLf
                  Case 4
                       PsReportStr = "Primary Key Voilation in Table GL40000" & vbCrLf
                       'PsReportStr = OPGFGetResString(47127) & vbCrLf
            End Select
               
            GoTo ErrHandler
    End If
    
    'Insert Sales Tax Details Here
    mReportStr = ""
    
    'Get ARLIN's recordset
    PsSQLstr = "Select * from ARLIN "
    PsSQLstr = PsSQLstr & " Where TranNo = " & rsARHdr.Fields("TranNo").Value
    
    pbsuccess = ObjMfgDl.OPLFFinOpenRecordset(rsARLIN, PsSQLstr, RsReportStr)
    If Not pbsuccess Then GoTo ErrHandler1
    
    pdDocTotal = CNToN(rsARHdr.Fields("DocTot").Value)
    pdDocDiscAmt = CNToN(rsARHdr.Fields("TotalDiscount").Value)
    pdDocFreightAmt = CNToN(rsARHdr.Fields("FreightAmt").Value)
    pdTaxTotal = CNToN(rsARHdr.Fields("TotalTax").Value)
    
    If pdDocTotal = 0 Then
        VouchtotalZero = True
    Else
        VouchtotalZero = False
    End If
    
    
    
    
    Dim piInvcTyp As Integer
    Dim PiIDTRX As Integer  'Document Type
    
    If rsARHdr.Fields("Crmemoflg").Value = -1 Then
        piInvcTyp = 3   '2      'Credit Memo
        PiIDTRX = 32    '22     'Summary Entered
    Else
        piInvcTyp = 1       'Invoice
        PiIDTRX = 12        'Summary Entered
    End If
    
'----------------------Finance Update Starts From Here------------------------------
    'Initialize Fin Recordset here
    'Transfer Values in the Fin Hedear Recordsets
    'Transfer Values in the Fin Detail Recordsets
    'Update Fin Detail Recordset
    'Update Fin Hadear Recordset
    'Update the  Finance upload Flag in the BME
    
    PsSQLstr = "Update ARHDR set UpdatedToFin =1, "
    PsSQLstr = PsSQLstr & "FinTranNo = '" & psFinTranNo & "' "
    PsSQLstr = PsSQLstr & "Where TranNo = '" & rsARHdr.Fields("TranNo").Value & "'"
    
    pbsuccess = ObjMfgDl.OPGFFINExecComm(PsSQLstr, , , RsReportStr)
    If Not pbsuccess Then GoTo ExitHandler
    
    'Print Report with the Finbatchnumber
    
    
STARTAGAIN:
    'Loop for Next Record
    rsARHdr.MoveNext
    
Next PiFor_Counter
If PbsecondTime = False Then
   rsARHdr.Filter = ""
   GoTo SECONDROUND
End If

ExitHandler:
    On Error Resume Next ' Added By Infocus/ #JULY#2002#
    'Free Up Memory Here
    
    RsReportStr = RsReportStr & PsReportStr & vbCrLf & vbCrLf
   
    OPGF_InsertInvoiceEntry = pbsuccess
    GsFunctionName = MCON_ModuleName & "OPGF_InsertInvoiceEntry" & GEXIT
    Exit Function
ErrHandler1:
    If piErrCount < 2 Then
        If InStr(1, Err.Description, "View call failed") <> 0 Then
            piErrCount = piErrCount + 1
            Resume
        End If
    End If

    ErrorHandler Err.Number, GsFunctionName, Err.Source
    pbsuccess = False
    Resume ExitHandler
ErrHandler:
    rsARHdr.CancelUpdate
    'RollBack the Transaction
    ErrorHandler Err.Number, GsFunctionName, Err.Source
    pbsuccess = False
    PsReportStr = PsReportStr & "         " & Pad(rsARHdr("Tranno").Value, Len(CStr(rsARHdr("Tranno").Value))) & OPGFGetResString(47134) & "  " & vbCrLf
    GoTo STARTAGAIN
    'Resume ExitHandler
End Function