Discounts Aggregation

https://confluence.oraclecorp.com/confluence/display/DDA/Aggregations+flow

https://confluence.oraclecorp.com/confluence/display/DDA/New+Aggregation+Implementation+after+Spark+no+longer+supported+by+AI+APPS

 main()

  • runProcess

runProcess()

  • extractSettings  //SettingsDaoImpl
  • extractAndAggregateData
  • saveSettings
  • uploadDataInSolrCollection
    • indexSuppliersInfoToSolr
    • deleteOutdatedSolrDocuments

extractSettings 

  • get data from erp_ap_settings table
  • returns MAP with minAPR, maxAPR, conversionType, gblCurrency

extractAndAggregateData()

  • InvoicePaymentsExtractor.prepareDataBeforeExtraction
    • erp_ap_aggregation_pkg.clean_payments
      (DiscountsDatabase/src/main/resources/db/migration/oracle/create)
      • Defaults bucurrency and invoicecurrency on invoice payments if null from invoice & gl_currencies 
  • calculator = new APRCalculator(minAPR, maxAPR);
  • suppliersRDD(id, supplierPojo) = SuppliersExtractor.extractSuppliers(tenantId, jsc, calculator)
    • getCount
    • call extractSuppliersRDD() to return JavaPairRDD<ID, SupplierPOJO>
    • select  ID, CREATION_DATE, CREDIT_SCORE, TOTAL_REVENUE, QUICK_RATIO,
                 INDUSTRY_TYPE, NUM_OF_EMPLOYESS, START_DATE_ACTIVE, NAME,
                 TYPE, END_DATE_ACTIVE, DAASB_INGESTION_DATE,
                  FUSION_INGESTION_DATE, LAST_UPDATE_DATE, ALIAS_NAME,
                  DNB_RATING, PAYMENT_CURRENCY_CODE,
                  LAST_DISC_TAKEN_DATE, FORMATION_YEAR
      FROM erp_ap_suppliers
    • update mLScore, GoodApr, BetterAPR, BestAPR from Calculator
  • supplierSiteAssignments
    • JavaPairRDD<VendorNameSiteNameBillToBuName, SupplierSiteAssignmentPOJO>
    • select ROWNUM rnum, assignments.id,  assignments.supplier_id,
                assignments.supplier_site_id, assignments.bill_to_bu_id,
                suppliers.name  supplier_name, sites.name supplierSiteName, bu.name bu_name
      FROM    erp_ap_supplierSiteAssignments assignments, erp_ap_suppliers suppliers,
                     erp_ap_suppliersSites sites, erp_ap_businessUnits bu
      WHERE assignments.suppliersId = suppliers.id
               and assignments.supplierSitesId = sites.id
               and assignments.billToBuId = bu.id
  • standingOffersList
    • List<StandingOfferAssignmentPOJO> 
    • /fscmRestApi/resources/latest/buyerInitiatedEarlyPaymentOffers
          vendorId; vendorName; vendorSiteId; vendorSiteName;
           billToBU; billToBUName; startDateActive; endDateActive;
           StandingOfferAssignmentStatus status;
  • standingOfferAssignments RDD
    • JavaPairRDD<VendorNameSiteNameBillToBuName, StandingOfferAssignmentPOJO> 
  • supplierSitesPOJO
    • JavaPairRDD<id, SupplierSitePOJO> 
    • SELECT ROWNUM rnum, ss.id id, ss.suppliersId suppliers_id, ss.vendorSiteCode 
                      vendorsitecod, s.name supplier"
         FROM erp_ap_suppliersSites ss, erp_ap_suppliers s on ss.suppliersId = s.id
  • supplierSiteAssignments = Utilities.setEnrollmentStatusForSupplierSiteAssignments(supplierSitesPOJO, supplierSiteAssignments, standingOfferAssignments);
  • invoices
    • JavaPairRDD<Id, InvoiceHeaderPOJO> 
    • SELECT ID,CREATION_DATE,TERMS_DATE,SUPPLIERS_ID,
                      INVOICE_TYPE_LOOK_UP_CODE,INVOICE_AMOUNT,AMOUNT_PAID,
                      DISCOUNT_AMOUNT_TAKEN,PAYMENTS_STATUS_FLAG,
                      SET_OF_BOOKS_ID,INVOICE_CURRENCY,SUPPLIER_SITES_ID,
                      ORG_ID,LAST_UPDATE_DATE,CANCELLED_DATE 
      FROM erp_ap_invoiceHeaders
      WHERE invoice_type_lookup_code <> 'PREPAYMENT' AND cancelled_date IS NULL;
  • payments
    • JavaPairRDD<InvoiceId, InvoicePaymentPOJO> 
    • SELECT ID, INVOICE_ID, LAST_UPDATE_DATE, ACCOUNTING_DATE,
                      EXCHANGE_DATE, DISCOUNT_TAKEN,PAYMENT_AMOUNT,      
                     ANNUAL_PERCENTAGE_RATE,
                     DAYS_ACCELERATED, BU_CURRENCY, INVOICE_CURRENCY
       FROM ERP_AP_INVOICEPAYMENTS
  • JavaPairRDD<InvoiceId, InvoicePaymentSchedulePOJO> paymentSchedules 
    • SELECT INVOICE_ID,DUE_DATE,LAST_UPDATE_DATE,AMOUNT_REMAINING
       FROM ERP_AP_INVOICEPAYMENTSCHEDULE
  •  JavaPairRDD<VendorIdSiteIdBuId, InvoicePrePaymentPOJO> prepayments 
    •   SELECT ROWNUM rnum, id, vendor_id, vendor_site_id, org_id, creation_date,      
                        SET_OF_BOOKS_ID, PAYMENT_STATUS_FLAG, INVOICE_CURRENCY, 
                        INVOICE_AMOUNT, AMOUNT_PAID, line_type_lookup_code,
                        nvl(pd_amount, 0) dist_amount,
                        nvl(pd_prepayamountremaining,0) DIST_PAID_AMOUNT_REMAINING"
       FROM erp_ap_invoiceHeaders ih left outer join erp_ap_invoiceDistributions pd on ih.id = pd.invoiceid
      WHERE invoiceTypeLookUpCode = 'PREPAYMENT'"
       AND NVL(paymentsStatusFlag,'') IN ('Y', 'P')
       AND cancelledDate IS NULL"
       AND NVL(amountPaid,0) > 0"
  • glLedgerMap
    • Map<ledgerId, currency> 
    • ledger_id, currency_code  ERP_AP_GLLEDGERS
  • dailyRateRDD
    •  JavaPairRDD<ID, Rate> 
    • SELECT ROWNUM rnum, id, rate
      FROM erp_ap_glDailyRates WHERE <conversionType> = id
  • dailyRateMap
    • Map<String, Double> 
    • GLDailyRateExtractor.convertToMap(dailyRateRDD);      
  • DailyExchangeRate dailyExchangeRate = new DailyExchangeRate(dailyRateMap);
  • Aggregate_ERP_Data
    • aggregateSupplierSiteAssignmentsWithPrepayments 
      • group by VendorIdSiteIdBuIdKey  to sumGblPrepaymentBalanceAmount & getGblPrepaymentPaidAmount
    • mergeSupplierSupplierSiteAssignmentRDDs
      • group by vendorid to sumGblPrepaymentBalanceAmount & getGblPrepaymentPaidAmount
      • numDiscInvoices
      • numInvoices
      • InvBalAmt
      • ihgblInvAmt
      • InvDiscBalAmt
      • ihgblInvDiscBalAmt
      • numDueDaysToToday
      • Inv1yrAmt
      • ihgblInv1YrAmt
      • InvTotAmt
      • InvTotAmtPd
      • InvTotDiscAmtTkn
      • prepaymentsBalanceAmount
      • ipAPRRates
      • ipAverageDiscountAPR
      • ipAIDiscountsAmtTkn
      • ipDiscountsAmtTkn
      • ipgblPaymentsAmt
      • ipPaymentsAmt
      • ipsgblAmountRemaining 
    • mergeSupplierInvoiceHeaderRDDs
      • copy betterAPR from supplier to invoice
    • Converts currency for payments
    • Group Payments by InvoiceId
    • mergeInvoicePaymentInvoiceHeaderRDDs
      • merge payments data into the Invoice level POJO. (this will have invoices with payments alone)
    • extractPaymentScheduleDetails
      • Get the valid oldest & newest scheduled payment records for each invoice
    • aggregateSupplierSiteAssignmentsWithInvoices
      • returns siteAssignments after aggregation
    • mergeInvoiceHeaderInvoiceHeaderRDDs

    • mergeSupplierSupplierRDDs
      •  to sum prepayment details at supplier level

  • JavaPairRDD<String, SupplierPOJO> aggregatedSuppliers = aggregateTuple._1();
  • JavaPairRDD<SupplierSiteAssignmentPOJO, Optional<InvoiceHeaderPOJO>> aggregatedSupplierSiteAssignments = aggregateTuple._2();
  • denormalizeAtSupplierBusinessUnitLevel
    • erp_ap_suppliersBU
  • denormalizeAtSupplierLevel
    • update erp_ap_suppliers
  • denormalizeSupplierSiteAssignmentData
    • convert rdd to pojo
    • sendSupplierSiteAssignmentDataToDB
      • insert into erp_ap_supplierSiteAssignments
AggregateERPData
  • returns  Tuple2<JavaPairRDD<String, SupplierPOJO>, JavaPairRDD<SupplierSiteAssignmentPOJO, Optional<InvoiceHeaderPOJO>>> aggregateTuple 
  •  
  • aggregateSupplierSiteAssignmentsWithPrepayments
    • siteAssignments =
      • groupby/key supplierId, supplierSiteId, billToBuId combination both siteassignments & prepayments
      • prepayments.reduceByKey adding up prepay balance and paid amount fields
      • Join siteassignments with prepayments
      • Update suppier site pojo with prepay balance and paid amount fields
  • mergeSupplierSupplierSiteAssignmentRDDs
    •  suppliers =
      • set supplier as Key for siteAssignments
      • Join supplier and site assignments.
      • Copy setStandingOfferAssignmentStatus & bu from Site to Supplier
      • Update supplier pojo with  prepay balance and paid amount fields
  • mergeSupplierInvoiceHeaderRDDs
    • invoices=
      • set new key for invoices as vendorid & buid
      • join invoice header with supplier
      • Copy APR from suppliers to Invoices 
  • Convert payment amounts into Global currency
  • Group payments by invoiceid
  • mergeInvoicePaymentInvoiceHeaderRDDs
    • paymentInvoices
      • merge payments data into the Invoice level POJO
  • mergeInvoiceHeaderInvoiceHeaderRDDs
      •  merge filtered invoice payments details with the base
  • InvoicePaymentSchedulesExtractor.extractPaymentScheduleDetails
    • paymentSchedules
      • Get the valid oldest & newest scheduled payment records
  • mergeInvoiceHeaderInvoicePaymentScheduleRDDs
      •  suppliersInvoices
        • copy earliestduedate & amount remining to invoice header
  • Set the Invoice header Global Currency attribute values
  • aggregateSupplierSiteAssignmentsWithInvoices
    • aggregateSupplierSiteAssignments
      •  
  • mergeSupplierSupplierRDDs
    • suppliers
      • Add prepayBalanceAmount, prepaymentspaidamount, ipsgblAmountRemaining
  • combineByKey
  • denormalizeAtSupplierLevel
    • supplierBUtoSupplierRollUp 
      • reduce by 
        • Invoice header rollup data
          • numInvoices
          • InvBalAmt
          • ihgblInvAmt
          • InvDiscBalAmt
          • ihgblInvDiscBalAmt
          • numDueDaysToToday
          • Inv1yrAmt
          • ihgblInv1YrAmt
          • InvTotAmt
          • InvTotAmtPd
          • InvTotDiscAmtTkn
        • Prepayments rollup data.
          • prepaymentsBalanceAmount
          • prepaymentsPaidAmount
        • Invoice Payment rollup data
          • ipAPRRates
          • ipAverageDiscountAPR
          • ipAIDiscountsAmtTkn
          • ipDiscountsAmtTkn
          • ipgblAIDiscountsAmtTkn
          • ipgblPaymentsAmt
          • ipgblPaymentsNotOlderThanAYearAmt
          • ipPaymentsAmt
          • ipLastDiscountDate
          • ipsgblAmountRemaining
          • setMissingCurrencyCode
    • denormalizeAggregatedDataByBlocks
      • updateAggregatedData
      • sendSupplierDataToDB 
    • denormalizeSupplierSiteAssignmentData

-- 

erp_ap_suppliers

erp_ap_suppliersBU

erp_ap_supplierSiteAssignments

 

new solution

--

ap_standingOffersTemp

erp_ap_standingOffersTemp 

 

git pull origin integration

 git fetch --all

git checkout feature-AIDISCOUNTS-3681-PREPROCESS_SITE_ASSIGNMENTS 

Comments

Post a Comment

Popular posts from this blog

OCI

OCI Platform Infrastructure Databases Data & AI Analytics Applications Developer services Governance and Applications 41 regions + 9 planned 12 Azure interconnected regions Infrastructure Compute(perform calculations, execute logic, running applns) VM, BareMetal servers,  containers,  managed Kubernetes service & managed vm ware service Storage -> Store, access, govern, Analyze both structured and unstructured data discs attached to vms file storage object storage archive storage Networking Databases Oracle db and open dbs ATP data bases(oltp, olap, json, apex)  no sql and my sql Data & AI Data flow - Managed apache spark service Big data Data catalog Managed data science platform  Apache kafka service for event streaming  Analytics Oracle analytics cloud Applications serverless offering called events, functions, api gateway helps build micro services and event driven architectures Developer services apex vbcs Governance and Applications Security ...
Read more