1 2 Previous Next 17 Replies Latest reply on Sep 11, 2019 12:22 PM by 3511817

    Wrong date format is getting exported for few cases

    3511817

      Hi All,

       

      We have received a few cases from the business end for wrong date format issue which was getting processed from WFR.

      While we are going to send a similar format of data from the WFR, the issue did not get replicated in our lower instance.

       

      Can you please advise us to debug this issue?

       

      Thanks,

      Anirban

        • 1. Re: Wrong date format is getting exported for few cases
          3511817

          Hello,

          Can we have an update on this, please?

          • 2. Re: Wrong date format is getting exported for few cases
            3511817

            HI All,

             

            Can you please provide any suggestion fro the above issue ?

            • 3. Re: Wrong date format is getting exported for few cases
              Amol Gavali

              check what do you have in UserExitXMLOutput is there explicit date formatting (you can send your code here) or its just dumping whats verified by the verifier some case date field could have status valid with different date fromat.

              • 4. Re: Wrong date format is getting exported for few cases
                3511817

                Hi Amol,

                 

                Thanks for your update. Any date formatting has not been placed in the code.

                Please check the below code:

                 

                Public Sub UserExitXMLOutput(pWorkdoc As SCBCdrPROJLib.ISCBCdrWorkdoc, LineData() As LineData, GLData() As AccountingData, ByVal lngLine As Long, ByVal strSection As String)

                 

                ' User exit that allows custom fields to be placed within the XML output

                  Select Case strSection

                    Case cXMLDocHeader

                    ' fnWriteXMLField(strParameterNameInIniFile, pWorkdoc.fields("MyField").text)

                    ' fnWriteXMLDateField(strParameterNameInIniFile, pWorkdoc.fields("MyDate").text)

                 

                    Case cXMLInvHeader

                 

                    ' fnWriteXMLField(strParameterNameInIniFile, pWorkdoc.fields("MyField").text)

                    ' fnWriteXMLDateField(strParameterNameInIniFile, pWorkdoc.fields("MyDate").text)

                      fnWriteXMLField("HCEMail", pWorkdoc.Fields("EMail").Text)

                      fnWriteXMLField("HCURN", pWorkdoc.Fields("URN").Text)

                      fnWriteXMLField("HCBtachId", pWorkdoc.Fields("BatchName").Text)

                      fnWriteXMLField("HCMajorOffice", pWorkdoc.Fields("MajorOffice").Text)

                      fnWriteXMLField("HCBudgetCenter", pWorkdoc.Fields("BudgetCenter").Text)

                      fnWriteXMLField("HCglDate", Replace(pWorkdoc.Fields("ScanDate").Text,"/","-"))

                 

                            'Modified on #21-Feb-2018 - Start

                 

                      fnWriteXMLField("HCVATRate", Replace(pWorkdoc.Fields("VATRate").Text,",",""))

                      fnWriteXMLField("HCVATAmount", Replace(pWorkdoc.Fields("VATAmount").Text,",",""))

                      fnWriteXMLField("HCVAT2Rate", Replace(pWorkdoc.Fields("VAT2Rate").Text,",",""))

                      fnWriteXMLField("HCVAT2Amount", Replace(pWorkdoc.Fields("VAT2Amount").Text,",",""))

                      fnWriteXMLField("HCVAT3Rate", Replace(pWorkdoc.Fields("VAT3Rate").Text,",",""))

                      fnWriteXMLField("HCVAT3Amount", Replace(pWorkdoc.Fields("VAT3Amount").Text,",",""))

                      'Modified on #21-Feb-2018 – End

                 

                      ' ******************** Oracle AP: Start *******************

                 

                      Dim dblFreight  As Double

                 

                      dblFreight = fnConvertToDouble(pWorkdoc.Fields("AmountFreightPrepaidAndAdded").Text)

                      fnWriteXMLField("HCHeaderLevelFreight", Replace(Format(CStr(dblFreight), "#.00"), ",", "."))

                 

                      Call wfrWriteFilenameComponentToXML(pWorkdoc)

                      Call wfrSetCreditAmountsNegative(pWorkdoc, odtInvoiceHeader, LineData)

                 

                 

                      ' ********************* Oracle AP: End ********************

                Case cXMLLineItems

                 

                 

                'Modified on #31-May-2018

                If pWorkdoc.Fields("POType").Text = c_xxwho_PO_Type Then

                 

                      Dim Index1 As Long

                      Dim tblLineItems As Object

                      Dim linexAmount As Double

                      Dim linexQuantity As Double

                 

                If UBound(XMLArray) > 37 Then

                         ReDim Preserve XMLArray(37)

                    End If

                 

                      Set tblLineItems = pWorkdoc.Fields("LineItems").Table(0)

                 

                      For Index1 = 0 To tblLineItems.RowCount - 1

                 

                      ReDim Preserve XMLArray(UBound(XMLArray) + 1)

                      XMLArray(UBound(XMLArray)) = "<lineItem>"

                 

                      fnWriteXMLField("TablePO1", pWorkdoc.Fields("LineItems").Table(0).CellText("PO", Index1))

                 

                   If UCase(pWorkdoc.Fields("DocumentType").Text) = "CREDIT" Then

                      linexAmount = fnConvertToDouble(pWorkdoc.Fields("LineItems").Table(0).CellText("Total", Index1))

                      linexQuantity = fnConvertToDouble(pWorkdoc.Fields("LineItems").Table(0).CellText("Quantity", Index1))

                 

                      If linexAmount > 0 Then

                         linexAmount = linexAmount * -1

                         fnWriteXMLField("TableTotal1", CStr(linexAmount))

                      End If

                 

                      If linexQuantity > 0 Then

                         linexQuantity = linexQuantity * -1

                         fnWriteXMLField("TableQuantity", CStr(linexQuantity))

                      End If

                 

                   ElseIf UCase(pWorkdoc.Fields("DocumentType").Text) = "INVOICE" Then

                      fnWriteXMLField("TableTotal1", pWorkdoc.Fields("LineItems").Table(0).CellText("Total", Index1))

                      fnWriteXMLField("TableQuantity", pWorkdoc.Fields("LineItems").Table(0).CellText("Quantity", Index1))

                   End If

                 

                   fnWriteXMLField("TableLineNo1", CStr(Index1+1))

                   fnWriteXMLField("TableUnitPrice1", pWorkdoc.Fields("LineItems").Table(0).CellText("Total", Index1))

                   fnWriteXMLField("TableUOM1", pWorkdoc.Fields("LineItems").Table(0).CellText("UOM", Index1))

                   'fnWriteXMLField("TableDescription1", Left(pWorkdoc.Fields("LineItems").Table(0).CellText("Description", Index1),240))

                 

                'Modified on #15-Aug-2018 - Start

                 

                Dim TempStr1 As String

                   Dim TempStr2 As String

                   Dim regEx As Object

                 

                   Set regEx=CreateObject("VBScript.RegExp")

                   regEx.Global = True

                   regEx.Pattern = "[^A-Za-z0-9]"

                 

                   TempStr1=regEx.Replace(pWorkdoc.Fields("LineItems").Table(0).CellText("Description", Index1),"")

                   fnWriteXMLField("TableDescription1", Left((TempStr1),240))

                 

                'Modified on #15-Aug-2018 - End

                 

                   If Index1 < tblLineItems.RowCount - 1 Then

                   ReDim Preserve XMLArray(UBound(XMLArray) + 1)

                   XMLArray(UBound(XMLArray)) = "</lineItem>"

                End If

                Next Index1

                End If

                'Modified on #31-May-2018

                 

                    Case cXMLGLItems

                 

                    ' fnWriteXMLField(strParameterNameInIniFile, GLData(lngLine).myelement)

                 

                  End Select

                 

                End Sub

                • 5. Re: Wrong date format is getting exported for few cases
                  Amol Gavali

                  Script seems to be good only ScanDate is formatted.

                   

                  Did business only reported for InvoiceDate ? If yes what exactly was the issue. Like Date not matching actual invoice or it just did not covert it right like in some cases invoice date was in MMDDYYYY format but INI file had DDMMYYYY format sort of issue?

                  • 6. Re: Wrong date format is getting exported for few cases
                    3511817

                    Yes, till now what issue we have received is invoice date format issue which was not converted into a standard format.

                     

                    The input value --310319 could not be converted to a date. It does not conform to a short, medium or long date format for the current locale.

                    This issue comes sometimes. This error came in the exported IPM text file and which was also sent from WFR too.

                     

                    Please assist us to fix this issue.

                    • 7. Re: Wrong date format is getting exported for few cases
                      Amol Gavali

                      i am assuming -- i not part of WFR extract if you validate invoice date by length then if its in not expected format at UserExitPostExtract then correct it for downstream processes using something like this

                       

                          Dim strDate As String

                          strDate = pWorkdoc.Fields("InvoiceDate").Text

                          If Len(strDate) = 6 Then      

                                strDate = Mid(strDate, 1, 2) & "-" & Mid(strDate, 3, 2) & "-20" & Mid(strDate, 5, 2)

                                pWorkdoc.Fields("InvoiceDate").Text =  Format(strDate,"MM-DD-YYYY")

                          End If

                      • 8. Re: Wrong date format is getting exported for few cases
                        3511817

                        Dear Amol,

                         

                        We will try this out. Could you please explain what exactly the code does here?

                         

                        Thanks,

                        Anirban

                        • 9. Re: Wrong date format is getting exported for few cases
                          3511817

                          Also please explain why are we using mid function? Is this mendatory to be used?

                          • 10. Re: Wrong date format is getting exported for few cases
                            Amol Gavali

                            its just formats the date if the length is 6 and then reformat into MM-DD-YYYY. Mid is used for string slicing. https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/mid-function

                            • 11. Re: Wrong date format is getting exported for few cases
                              3511817

                              Okay. Why we are writing this code in this public function? Can you please explain?

                              • 12. Re: Wrong date format is getting exported for few cases
                                Amol Gavali

                                its function that gets called before doing actual exports.

                                • 13. Re: Wrong date format is getting exported for few cases
                                  Amol Gavali

                                  did any of my suggestion helped you to resolve this issue or you are still facing issues with date format?

                                  • 14. Re: Wrong date format is getting exported for few cases
                                    3511817

                                    Actually still stuck in the case. Please help me.

                                    1 2 Previous Next