/
Databuild Purchase Order Integration - Reference

Databuild Purchase Order Integration - Reference

Owned by Julie Doyle-Whitelock (Deactivated)
Last updated: Aug 09, 2017

Integrated Fields

The integration of Databuild Purchase Orders to Framework is fixed in the content that is exchanged between the systems. There are no user-configurable integrated fields available to this type of integration.

Database Models

The following database model(s) displays the manner in which data is integrated between the third party application and Framework Integration.


Integration Process

The following information is a low level account of the integration process including criteria, decisions, and outcomes. This information is technical in nature and is provided "as-is" for a detailed analysis of the integration process for system administrators.

  1. Read profile keys to determine which actions to process.
  2. Process Cost Centres
    1. Read all eligible records from Framework table int_cCentreGrp where
      1. Int_cCentreGrp.l_link_sys_gl_id = -2027 (Databuild)
      2. Int_cCentreGrp.l_context_id = ‘Context’ (profile key)
      3. Int_cCentreGrp.l_int_cCentreGrp_id <> 0
    2. For each record get matching Databuild record in table ccBanks where
      1. ccBanks.ccBankCode = int_cCentreGrp.s_link_cCentreGrp
    3. Update the int_cCentreGrp.s_link_name field
    4. Read all eligible records from Framework table int_cCentre where
      1. Int_cCentre.l_link_sys_gl_id = -2027 (Databuild)
      2. Int_cCentre.l_context_id = ‘Context’ (profile key)
      3. Int_cCentre.l_int_cCentre_id <> 0
      4. Int_cCentre.l_int_cCentreGrp_id <> 0
    5. For each record make sure the int_cCentreGrp linked record exists, and if so get the matching databuild record in table costCentres where
      1. costCentres.code = int_cCentre.s_link_cCentre
      2. costCentres.tier = int_cCentreGrp.s_link_cCentreGrp
  3. Process Entities
    1. Read all eligible records from Framework table int_entity where
      1. Int_entity.l_link_sys_gl_id = -2027 (Databuild)
      2. Int_entity.l_context_id = ‘Context’ (profile key)
      3. Int_entity.l_int_entity_id <> 0
    2. For each record get matching Databuild record in table supplier where
      1. Supplier.supplier_code = int_entity.s_link_entity
    3. Update the int_entity.s_name, int_entity.f_nonCompliance and int_entity.f_preventAlloc fields.
    4. Get matching int_entityGrp.l_int_EntityGrp_id record where supplier.supp_group = int_entityGrp.s_link_entityGrp, update the int_entity record with the correct group link.
    5. Read all eligible records from Framework table int_entityGrp where
      1. Int_entityGrp.l_link_sys_gl_id = -2027 (Databuild)
      2. Int_entityGrp.l_context_id = ‘Context’ (profile key)
      3. Int_entityGrp.l_int_entityGrp_id <> 0
    6. For each record get the matching Databuild record from SupplierGroup where
      1. SupplierGroup.GroupNumber = int_entityGrp. l_link_entityGroup
    7. Update the Group name
  4. Process Items
    1. Get all Databuild records from PriceList and PerCodes where PriceList.perCode = PerCode.Code
    2. For each databuild record get matching Framework record from int_estItem where
      1. Int_estItem.s_link_item = priceList. PriceCode
      2. Int_estItem.l_context_id = ‘Context’ (profile key)
    3. Update Existing records d. Get all Framework records from int_estItem where
      1. Int_estItem.l_context_id = ‘Context’ (profile key)
      2. Int_estItem.l_int_estItem_id <> 0
    4. For each int_estItem record get the Databuild prices from table prices where prices.priceCode = int_estItem.s_link_item
    5. Look for this price in int_estIPrice where
      1. Int_estIPrice. l_link_price = prices. Counter If found update, otherwise add the int_estIPrice record
    6. For each int_estItem record get the Databuild prices from table SuppliersPrices where SuppliersPrices.itemCode = int_estItem.s_link_item
    7. Look for this price in int_estIPriceE where
      1. Int_estIPriceE.l_link_price = SuppliersPrices.counter
    8. If found update, otherwise add the int_estIPriceE record
  5. Process Orders
    1. Get eligible Framework jobs from v_sched_dbJobOrders where
      1. Job.s_link_boq <> ‘N/A’
      2. Job.l_context_id = ‘Context’ (profile key, if 0 all contexts)
      3. Job.l_wfl_stgMajor_id = -4(Construction) or job.l_wfl_stgMajor_id = -3(Administration) and job.l_wfl_stgMinor_id = -18(Maintenance) or job.l_wfl_stgMinor_id = -19(Service)
      4. and apply order criteria where OrderCriteria is
      5. 2 – Construction Manager • Cst.l_mgr_e_id = ‘OrderCriteriaID’
      6. 3 – Supervsor • Cst.l_super_e_id = ‘OrderCriteriaID’
      7. 4 – Single Job • job.s_job_num = ‘OrderCriteriaID’
    2. For each job get orders and items from Databuild where
      1. Jobs.job_no = job.s_job_num (job.s_link_boq is used instead if not empty)
      2. Orders.orderNumber is not null
      3. Orders.ordered = 1
      4. If profile key OnlyIntegrateNew is true, add additional criteria where
      5. Orders.orderDate >= ‘StartIntegrationDate’ – 7 days
      6. Orders.orderDate <= ‘EndIntegrationDate’ or now if endIntegrationDate is none.
    3. Once we have the Databuild Order, see if it exists in po_order table where
      1. Po_order.s_jobNo = orders.job
      2. Po_order.s_costCentreCode = orders.costCentre
      3. Po_order.s_orderLoad = orders.bLoad
    4. And we check each Databuild Order Item, by seeing if it exists in po_item table where
      1. Po_item.l_link_item = bill.counter
    5. Orders and items are updated or added. At the end of integration items are checked to see if there are any that need to be removed from Framework.
    1. Process Order Approvals
      1. If processApprovals is 1 or 3 then:
      2. If valid, then Framework p_orderAppr records are retrieved based on the approval type being processed (from preferences).
      3. If type is Approval 1:
        1. order.ok2Pay.date is d_payment1_appd
      4. If type is Approval 2:
        1. order.ok2Pay.date is d_payment2_appd
      5. If type is Latter of Approval 1 or Approval 2 then:
        1. order.ok2Pay.date is the latest date entered in either d_payment1_appd or d_payment2_appd where only 1 of those 2 dates has a value entered.
      6. If type is Latter of Approval 1 AND Approval 2 then:
        1. order.ok2Pay.date is the latest date entered in either d_payment1_appd or d_payment2_appd where both dates must have a value.