Tuesday, February 13, 2024

OIC - ERP - Detailed implementation steps to import transactions to ERP Fusion accounting hub or ERP FAH sub ledger accounting

Working...13th Feb

Usecase: we have a source who is unable to provide transformed accounting data thats why we can't use FBDI GL journal import so we need to use Fusion Accounting Hub (FAH) -  create the subledger source for the 3rd party system and configure the account transformation rules. Then import the transaction data using the fbdi template. Here, we will see the detailed FAH subledger accounting import implementation steps to ERP.

To know when to use GL or FAH, follow


Implementation steps:

  1. List the files using SFTP connection from a specified directory
  2. Iterate over each file using for each action
  3. Take a body scope
    1. Download file as binary using SFTP connection to stage location.
    2. Assign global variables
    3. Write header Rows using stage C to C79 CSV file, file name : XlaTrxH.csv
    4. Write Line Header names : XlaTrxL.csv , https://soalicious.blogspot.com/2022/01/oic-add-header-or-line-file-field-names.html
    5. Get Open Period dates from ERP using BI report call. See my blog how to call BI report. https://soalicious.blogspot.com/2022/03/oic-how-to-call-and-read-bi-publisher.html
    6. Decode BI base64 response to reference : Take a assign and use decodeBase64ToReference() to store in a variable
    7. Read the open period dates file using stage, sample csv file, reference variable
    8. Read in segments the downloaded source file using stage
    9. For each record
      1. Update variables
      2. If C1 = DHD ( for header records)
        1. If record line count =0, assign transactionNumberString as empty
        2. Create a unique transaction number , we can take a sequence number or generate using data and time like concat(month-from-date(current-date()),day-from-date(current-date()),$p_currentTime,c8,c4) where c8= event type code and C4= transaction date and p_currentTime =concat(hours-from-dateTime(current-dateTime()),minutes-from-dateTime(current-dateTime())) declared as a global variable
        3. For each open period dates row,
          1. Check if transaction date within open period start and end dates and system matches, then assign openperiod flag as Y
        4. IF open period flag = N, update reject count as below
          1. Update closeperiodcount = + 1 and closedperioddates = concat( closedperiod dates , C2 and C4) where C= company code and C4= date
        5. If contains(TransactionNumberString, transactionNunber) = "true" then just logger with trasaction number. This step means we are skipping to write duplicate header record having same transaction number
        6. Else:
          1. update transaction number string.
            1. Transaction number string = concat(transaction number string, transaction number)
          2. Write header file using stage
            1. File name: XlaTrxH.csv
            2. Append : yes
            3. Mandatory mapped fields:
            1. transaction_number : for each unique number , only one header record should be there.
            2. event_type_code
            3. Ledger_name
            4. Transaction_date
            5. Transaction_reversal_flag
      3. If C1 = DLN ( For Line records)
        1. Assign line variables
          1. recordLineCount = recordLineCount +1 
          2. lineNumber = LineNumber +1
        2. Take a switch and check amout for credit and debit validation:
          1. If c30 >= 0.0 then assign debit variables like
            1.  record debit line count = record debit line count +1
            2. record debit line amount = add(record debit line amount, c30). how to add two numbers with fixed decimal points using javascript. check my blog(https://soalicious.blogspot.com/2021/03/oic-use-of-javascript-in-integration.html)
          2. else, assign same for credit variables like record credit line count, record credit line amount
      4. If C1 = BTR ( For Trailer Records)
        1. Assign Trailer records:
          1. File line count
          2. File debit line count
          3. File credit line count
          4. File debit line amount
          5. File credit line amount
    10. IF closePeriodCount >= 1 then throw new fault 
      1. Code like closed period trasaction exists
      2. Reason like closed period transaction exists in the source file
      3. Details like closePeriodDates variable
    11. Write Lines part:
      1. Read file in segments using stage and the downloaded file reference : provide a sample.csv file
      2. For each record
        1. If C1 = DLN
          1. Assign newLineNumber = newLineNumber + 1 (where newLineNumber was globally assigned as 0)
          2. Take a stage and write the lines
            1. File name: XlaTrxL.csv
            2. Append yes
            3. Mandatory mapped fields: 
              1. Transaction_numer: we can take a sequence number or generate using data and time like concat(month-from-date(current-date()),day-from-date(current-date()),$p_currentTime,c8,c4) where c8= event type code and C4= transaction date and p_currentTime =concat(hours-from-dateTime(current-dateTime()),minutes-from-dateTime(current-dateTime())) declared as a global variable (Note: the transaction number should match for header to the lines uniquely)
              2. Line_Number : assign $newLineNumber generated from
              3. Default_Currency
              4. Account_L
              5. STATE
              6. Description
              7. Book_Type
              8. Company
              9. CostCenter
              10. InterCompany
              11. ExpenseFunction
              12. Project
              13. Je_Line_Ref
              14. Source
              15. Default_amount  
    1. Write meta data : Create a meta data file using stage with Metadata_File_InterfaceName.txt which will be helpful to track from ERP scheduled process.
      1. Metadata version number: 3
      2. Application Short Name : SourceName
    2. Zip files : Zip the header and line files using a stage.
    3. Archive zip files : Archive the zip file to a ftp location for support help.
    4. Validation of Trailer record: Take a switch and validate following:
      1. File line count = RecordLineCount
      2. File debit line count = RecordDebitLineCount
      3. File credit line count = RecordCreditLineCount
      4. File debit line amount = RecordDebitLineAmount
      5. File credit line amount = RecordCreditLineAmount
    5. IF validation is success
      1. Encrypt and importhttps://soalicious.blogspot.com/2021/05/oic-erp-import-technique-1.html
      2. Get ess job status for import: https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html
      3. Capture child process idhttps://soalicious.blogspot.com/2022/01/oic-erp-how-to-get-child-process-id.html
      4. Get ess job staus from child process : same way like above step2 : https://soalicious.blogspot.com/2021/07/oic-get-ess-job-status.html
      5. Fah call back part : https://soalicious.blogspot.com/2022/02/oic-custom-callback-from-erp.html
    6. Else:

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...