Obtain Supplier Information on Sales Order Fulfillment Lines.sql

SELECT
	dfla.source_order_number
  , dla.SOURCE_LINE_NUMBER
  , dla.DISPLAY_LINE_NUMBER
  , dfla.FULFILL_LINE_NUMBER
  , dfla.status_code
  , dfla.SUPPLIER_ID
  , dfla.supplier_site_id
  , mtps.PARTNER_NAME
  , mtps.PARTNER_ADDRESS
  , mtps.TP_SITE_CODE
  , MXM1.Attribute_Name
  , MXM1.entity_name
  , MXM1.source_value
  , MXM1.Target_value
  , MXM2.Attribute_Name
  , MXM2.entity_name
  , MXM2.source_value
  , MXM2.Target_value
  , psv.VENDOR_NAME
  , psv.VENDOR_ID
  , psv.PARTY_ID
  , pssam.VENDOR_SITE_CODE
  , pssam.vendor_site_id
  , pssam.PARTY_SITE_ID
	-- , PSSAM.*
	-- , psv.*
FROM
	fusion.doo_fulfill_lines_all     dfla
  , fusion.doo_lines_all             dla
  , fusion.doo_headers_all           dha
  , FUSION.msc_trading_partner_sites mtps
  , Fusion.Msc_Xref_Mapping          Mxm1
  , Fusion.Msc_Xref_Mapping          Mxm2
  , fusion.POZ_SUPPLIERS_V           PSV
  , fusion.POZ_SUPPLIER_SITES_ALL_M  PSSAM
WHERE
	dha.header_id                  = dfla.header_id
	AND dfla.line_id               = dla.line_id
	AND dha.submitted_flag         = 'Y'
	AND dfla.SUPPLIER_ID IS NOT NULL
	AND dfla.Supplier_id           = mtps.partner_id
	AND dfla.supplier_site_id      = mtps.PARTNER_SITE_ID
	AND MXM1.entity_name           = 'SUPPLIERS'
	AND MXM1.TARGET_VALUE          = dfla.SUPPLIER_ID
	AND MXM2.entity_name           = 'SUPPLIER_SITES'
	AND MXM2.TARGET_VALUE          = dfla.SUPPLIER_site_ID
	AND PSV.vendor_id              = MXM1.source_value
	AND PSSAM.Vendor_site_id       = MXM2.source_value
	AND dha.source_order_number    = '&SOURCE_ORDER_NUMBER'
ORDER BY
	dla.DISPLAY_LINE_NUMBER
  , dfla.FULFILL_LINE_NUMBER
;