Wednesday, December 2, 2020

Oracle Fusion: Distributed Order Orchestration (Sales Order) Lines Query

 Below is the query to get Distributed Order Orchestration (Sales Order) line informations along with its EFF:

SELECT dla.header_id
     , dla.line_id
     , dla.display_line_number
     , dla.source_line_id
     , dla.source_line_number
     , dla.source_order_number
     , dla.source_order_system
     , dla.ordered_qty
     , dla.canceled_qty
     , dla.ordered_uom
     , dla.source_org_id
     , dla.org_id
     , dla.extended_amount
     , dla.line_type_code
     , dla.open_flag
     , dla.canceled_flag
     , dla.shipped_qty
     , dla.inventory_item_id
     , dla.inventory_organization_id
     , dla.unit_list_price
     , dla.unit_selling_price
     , dfla.fulfill_line_id
     , dfla.status_code
     , dfla.priced_on
     , dfla.request_ship_date
     , dfla.schedule_ship_date
     , dfla.packing_instructions
     , dfla.earliest_acceptable_ship_date
     , dfla.bill_to_customer_id
     , dfla.bill_to_site_use_id
     , dfleb.eff_line_id
     , dfleb.context_code
     , dfleb.attribute_char1
     , dfleb.attribute_char2
     , dfleb.attribute_char3
     , dfleb.attribute_char4
     , dfleb.attribute_char5
     , dfleb.attribute_char6
     , dfleb.attribute_char7
     , dfleb.attribute_char8
     , dfleb.attribute_char9
     , dfleb.attribute_char10
     , dfleb.attribute_char11
     , dfleb.attribute_char12
     , dfleb.attribute_char13
     , dfleb.attribute_char14
     , dfleb.attribute_char15
     , dfleb.attribute_char16
     , dfleb.attribute_char17
     , dfleb.attribute_char18
     , dfleb.attribute_char19
     , dfleb.attribute_char20
     , dla.created_by
     , TO_CHAR (FROM_TZ (dla.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , dla.last_updated_by
     , TO_CHAR (FROM_TZ (dla.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM doo_lines_all dla, doo_fulfill_lines_all dfla, doo_fulfill_lines_eff_b dfleb
 WHERE dla.line_id = dfla.line_id
   AND dfla.fulfill_line_id = dfleb.fulfill_line_id(+)

Oracle Fusion: Distributed Order Orchestration (Sales Order) Headers Query

 Below is the query to get Distributed Order Orchestration (Sales Order) header informations along with its EFF:

SELECT dha.header_id
     , dha.order_number
     , dha.customer_po_number
     , dha.source_order_system
     , dha.source_order_number
     , dha.source_order_id
     , dha.sold_to_party_id
     , dha.transactional_currency_code
     , TO_CHAR (dha.ordered_date, 'RRRR-MM-DD HH24:MI:SS') ordered_date
     , dha.org_id bu_id
     , dha.order_type_code
     , dha.legal_entity_id
     , dha.freeze_tax_flag
     , dha.freeze_price_flag
     , dha.freeze_shipping_charge_flag
     , TO_CHAR (dha.priced_on, 'RRRR-MM-DD HH24:MI:SS') priced_on
     , dha.submitted_flag
     , dha.status_code
     , dha.change_version_number
     , dheb.context_code
     , dheb.attribute_char1
     , dheb.attribute_char2
     , dheb.attribute_char3
     , dheb.attribute_char4
     , dheb.attribute_char5
     , dheb.attribute_char6
     , dheb.attribute_char7
     , dheb.attribute_char8
     , dheb.attribute_char9
     , dheb.attribute_char10
     , dheb.attribute_char11
     , dheb.attribute_char12
     , dheb.attribute_char13
     , dheb.attribute_char14
     , dheb.attribute_char15
     , dha.created_by
     , TO_CHAR (FROM_TZ (dha.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , dha.last_updated_by
     , TO_CHAR (FROM_TZ (dha.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM doo_headers_all dha, doo_headers_eff_b dheb
 WHERE dha.header_id = dheb.header_id(+)