List of Open Orders.sql

-- The following SQL can be executed to obtain the Inventory Organization ID:
SELECT
	IOP.ORGANIZATION_CODE
  , IOP.ORGANIZATION_ID
  , IOP.INVENTORY_FLAG
  , (
		SELECT
			NAME
		FROM
			fusion.HR_ORGANIZATION_UNITS_F_TL
		WHERE
			iop.organization_id = organization_id
			AND sysdate BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
			AND language = userenv('LANG')
	)
	"Org Name"
  , IOP.BUSINESS_UNIT_ID
  , (
		SELECT
			name
		FROM
			fusion.HR_ORGANIZATION_UNITS_F_TL
		WHERE
			iop.BUSINESS_UNIT_ID = organization_id
			AND sysdate BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
			AND language = userenv('LANG')
	)
	"BU Name"
  , (
		SELECT
			ORG_INFORMATION4
		FROM
			fusion.hr_organization_information_f
		WHERE
			iop.BUSINESS_UNIT_ID = organization_id
			AND sysdate BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
			AND ORG_INFORMATION_CONTEXT = 'FUN_BUSINESS_UNIT'
	)
	"Set ID"
FROM
	fusion.INV_ORG_PARAMETERS IOP
where
	1=1
ORDER BY
	4
  , 6
-- Using ORGANIZATION_ID returned from above query, execute the following SQL:
-- NOTE: the search criteria on this can be changed to specify different attributes on the inventory item.
SELECT DISTINCT
	a.source_order_number
  , a.creation_Date
FROM
	fusion.doo_fulfill_lines_all a
WHERE
	inventory_item_id IN
	(
		SELECT
			inventory_item_id
		FROM
			fusion.EGP_SYSTEM_ITEMS_B MSI
		WHERE
			--PICK_COMPONENTS_FLAG           ='Y'
			--                        AND BOM_ITEM_TYPE              =4
			--                        AND
			ORGANIZATION_ID                = :ORGANIZATION_ID
			AND CUSTOMER_ORDER_FLAG        ='Y'
			AND CUSTOMER_ORDER_ENABLED_FLAG='Y'
			AND SHIPPABLE_ITEM_FLAG        ='Y'
			--                        AND STOCK_ENABLED_FLAG         ='N'
			AND RETURNABLE_FLAG           ='Y'
			AND upper(msi.item_number) LIKE upper(:ITEM_NUMBER%)
			AND EXISTS
			(
				SELECT
					1
				FROM
					fusion.DOO_FULFILL_LINES_ALL DFL
				WHERE
					OPEN_FLAG                 ='Y'
					AND DFL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
					--and dfl.category_code like 'RETURN'
			)
	)
	AND EXISTS
	(
		SELECT
			1
		FROM
			fusion.doo_headers_all b
		WHERE
			a.header_id         = b.header_id
			AND b.submitted_flag='Y'
			AND b.open_flag     ='Y'
	)
	AND A.OPEN_FLAG ='Y'
ORDER BY
	a.creation_date DESC