Sourcing Rule and Assignment Set Information .sql

SELECT
	mas.assignment_set_name
  , msr.SOURCING_RULE_TYPE
  , msr.ORGANIZATION_ID
  , DECODE (msa.assignment_type
			  , '4','Organization'
			  ,'5','Category and Organization'
			  ,'6','Item and Organization'
			  ,'10','Global'
			  ,'12','Item'
			  ,'11','Category'
			  ,'13','Region'
			  ,'14','Category and Region'
			  ,'15','Item and Region'
			  ,'16','Demand Class'
			  ,'17','Customer'
			  ,'18','Customer and Customer Site'
			  ,'19','Category and Demand Class'
			  ,'20','Category and Customer'
			  ,'21','Category and Customer and Customer Site'
			  ,'22','Item and Demand Class'
			  ,'23','Item and Customer'
			  ,'24','Item and Customer and Customer Site'
			  ,'25','Item and Customer and Customer Site' ) "assignment_type"
  , msr.SOURCING_RULE_NAME
  , (
		SELECT
			mio.organization_code
		FROM
			fusion.MSC_INSTANCE_ORGS mio
		WHERE
			mio.organization_id = msr.ORGANIZATION_ID
	)
	AS "Rule Org ID"
  , (
		SELECT
			mio.organization_code
		FROM
			fusion.MSC_INSTANCE_ORGS mio
		WHERE
			mio.organization_id = sou.SOURCE_ORGANIZATION_ID
	)
	AS Source_Org
  , (
		SELECT
			mio.organization_code
		FROM
			fusion.MSC_INSTANCE_ORGS mio
		WHERE
			mio.organization_id = REC.SR_RECEIPT_ORG
	)
	AS Receiving_Org
  , DECODE (sou.SOURCE_TYPE
			  ,'1','Transfer From'
			  ,'2','Make At'
			  , '3','Buy From')
  , "SOURCE_TYPE"
  , sou.allocation_percent
  , sou.rank
  , mi.item_name
  , sou.SOURCE_PARTNER_ID
  , sou.SOURCE_PARTNER_SITE_ID
  , sou.CARRIER_ID
  , sou.MODE_OF_TRANSPORT
  , sou.SERVICE_LEVEL
  , sou.SHIP_METHOD
  , sou.DELETED_FLAG
  , sou.SOURCE_ORGANIZATION_ID
FROM
	fusion.MSC_SOURCING_RULES  msr
  , fusion.MSC_SR_ASSIGNMENTS  msa
  , fusion.MSC_ASSIGNMENT_SETS mas
  , fusion.msc_sr_receipt_org  rec
  , fusion.msc_sr_source_org   sou
  , fusion.msc_items           mi
WHERE
	msa.sourcing_rule_id      = msr.sourcing_rule_id
	AND msa.inventory_item_id = mi.inventory_item_id (+)
	--       AND mi.item_name LIKE 'PMC%'
	AND rec.sourcing_rule_id = msa.sourcing_rule_id
	--       AND rec.collected_flag         <> -1
	AND sou.sr_receipt_id = rec.sr_receipt_id
	--        AND mas.assignment_set_name = 'Fusion Default Assignment Set'
	AND mas.assignment_set_id = msa.assignment_set_id
	--      AND msr.SOURCING_RULE_NAME LIKE 'KK%'
ORDER BY
	mas.assignment_set_name
  , msa.assignment_type
  , msr.SOURCING_RULE_NAME
  , RANK