Flexfield BQL Query Examples

Use these examples as a reference when you add descriptive flexfields or extensible flexfields to a user-defined query.

The database SQL shows how the source flexfield values are stored. Use the BQL query definition, not the database SQL, in the Data Extraction Tool.

The flexfield paths, context codes, aliases, modules, business objects, and Business Views in these examples are samples. Replace them with values that apply to your flexfield configuration.

Descriptive Flexfield Global Segments

This example extracts three global descriptive flexfield segments from the Work Order business object.

Database SQL Reference

SELECT ATTRIBUTE_CHAR1, ATTRIBUTE_TIMESTAMP1, ATTRIBUTE_NUMBER1
FROM fusion.WIE_WORK_ORDERS_B;

BQL Query Definition

{
  "viewQueries": {
    "workOrderBO": {
      "module": "oraScmCoreManufacturingWorkOrder",
      "businessObject": "WorkOrder",
      "view": "workOrderExtract",
      "fieldAliases": {
        "ora_flex_wieWorkOrders__info.headerChar": "headerCharValue",
        "ora_flex_wieWorkOrders__info.headerDatetime": "headerDatetimeValue",
        "ora_flex_wieWorkOrders__info.headerNum": "headerNumValue"
      }
    }
  },
  "select": "SELECT workOrderBO.headerCharValue, workOrderBO.headerDatetimeValue, workOrderBO.headerNumValue FROM workOrderBO",
  "return": [
    {
      "name": "headerCharValue",
      "type": "string"
    },
    {
      "name": "headerDatetimeValue",
      "type": "date-time"
    },
    {
      "name": "headerNumValue",
      "type": "string"
    }
  ]

Descriptive Flexfield Context Segments

This example shows the database columns and context values for descriptive flexfield context segments on work orders.

Database SQL Reference

SELECT ATTRIBUTE_CHAR1,
       ATTRIBUTE_CHAR3,
       ATTRIBUTE_CHAR11,
       ATTRIBUTE_CHAR12,
       ATTRIBUTE_DATE2,
       ATTRIBUTE_CATEGORY
FROM fusion.WIE_WORK_ORDERS_B a
WHERE a.ATTRIBUTE_CATEGORY IN ('PSPROCESS', 'PSGENERICWHATTRIBUTES');

The ATTRIBUTE_CATEGORY column identifies the context. In BQL, include the context name in angle brackets in the flexfield path. For example:

ora_flex_wieWorkOrders__info<PSPROCESS>.psprocessbagsFk

Descriptive Flexfield Context-Sensitive Segments

This example extracts global segments and fields from the PSPROCESS and PSGENERICWHATTRIBUTES contexts. The query filters the results to the required contexts.

{
  "viewQueries": {
    "workOrderBO": {
      "module": "oraScmCoreManufacturingWorkOrder",
      "businessObject": "WorkOrder",
      "view": "workOrderExtract",
      "fieldAliases": {
        "ora_flex_wieWorkOrders__info.headerChar": "headerCharValue",
        "ora_flex_wieWorkOrders__info.headerDatetime": "headerDatetimeValue",
        "ora_flex_wieWorkOrders__info.headerNum": "headerNumValue",
        "ora_flex_wieWorkOrders__info.abpApprovalStatusFk": "abpApprovalStatusFkValue",
        "ora_flex_wieWorkOrders__info.abpVersionFk": "abpVersionFkValue",
        "ora_flex_wieWorkOrders__info.abpSpecialProjectFk": "abpSpecialProjectFkValue",
        "ora_flex_wieWorkOrders__info<PSGENERICWHATTRIBUTES>.zoneFk": "zoneFk",
        "ora_flex_wieWorkOrders__info.xContextSegmentFk": "xContextSegmentFkValue",
        "ora_flex_wieWorkOrders__info<PSPROCESS>.testboss": "testboss",
        "ora_flex_wieWorkOrders__info<PSPROCESS>.psprocessbagsFk": "psprocessbagsFk",
        "ora_flex_wieWorkOrders__info<PSPROCESS>.psprocesscountryFk": "psprocesscountryFk",
        "ora_flex_wieWorkOrders__info<PSPROCESS>.psprocessblocksFk": "psprocessblocksFk"
      }
    }
  },
  "select": "SELECT workOrderBO.headerCharValue, workOrderBO.headerDatetimeValue, workOrderBO.headerNumValue, workOrderBO.abpApprovalStatusFkValue, workOrderBO.abpVersionFkValue, workOrderBO.abpSpecialProjectFkValue, workOrderBO.zoneFk, workOrderBO.xContextSegmentFkValue, workOrderBO.testboss, workOrderBO.psprocessbagsFk, workOrderBO.psprocesscountryFk, workOrderBO.psprocessblocksFk FROM workOrderBO WHERE workOrderBO.xContextSegmentFkValue IN ('PSPROCESS', 'PSGENERICWHATTRIBUTES')"
}

Extensible Flexfield Segments

This example extracts extensible flexfield values for the Laptop context of the Manufacturer extensible flexfield.

{
  "viewQueries": {
    "EffLaptop": {
      "module": "oraScmCoreManufacturer",
      "businessObject": "ora_flex_egoManufacturerEff__Laptop",
      "view": "LaptopExtract",
      "fieldAliases": {
        "effLineId": "effLineId",
        "contextCode": "contextCode",
        "timeCreated": "timeCreated",
        "timeUpdated": "timeUpdated",
        "color": "color",
        "laptopSize": "laptopSize",
        "manufacturer": "manufacturer"
      }
    }
  },
  "select": "SELECT effLineId, contextCode, timeCreated, timeUpdated, color, laptopSize, manufacturer FROM EffLaptop"
}