Order Orchestration Process Definition.sql

-- The following SQL shows the major components of an Order Orchestration Definition (DOO_OrderFulfillmentGenericProcess)- not all attributes are shown and these can be added as required.
SELECT
	dpdv.doo_process_ID
  , dpdv.PROCESS_NAME
  , TL.STEP_NAME
  , b.STEP_NUMBER
  , b.STEP_TYPE
  , b.PARENT_STEP_NUMBER
  , b.STEP_FILTER_RLS_ID
  , b.MANUAL_STEP_FLAG
  , b.COMP_PATTERN_RLS_ID
  , b.LEAD_TIME_RLS_ID
  , b.DEFAULT_LEAD_TIME
  , b.DEFAULT_LEAD_TIME_UOM
  , b.CUSTOMER_LAST_STEP_FLAG
  , b.SWITCH_RLS_ID
  , b.SWITCH_CONDITION_EVAL_SEQ
  , b.DEFAULT_BRANCH_FLAG
  , b.DEFAULT_EXIT_STATUS_CODE
  , b.EXIT_CRITERIA_STATUS_CODE
  , b.USE_DYNAMIC_ATTR_IN_DELTA_COMP
  , b.USE_FLEX_ATTR_IN_DELTA_COMP
  , b.HOLD_ON_WAIT
  , b.OTHERWISE_FLAG
  , b.START_AFTER_COND_RLS_ID
  , b.START_AFTER_COND_RLS_NAME
  , b.ATTRIBUTE_CATEGORY START_DATE_ACTIVE
  , b.END_DATE_ACTIVE
  , b.SUB_PROCESS_IDENTIFIER
  , b.UPDATE_SERVICE_ID
  , b.CANCEL_SERVICE_ID
  , b.HOLD_SERVICE_ID
  , b.ORCHESTRATION_APPLICATION_ID
  , dpdv.DOO_PROCESS_ID
  , dtdv.NAME
  , dtdv.DISPLAY_NAME
  , dtdv.TASK_CODE
  , dttv.TASK_TYPE
  , dttv.description
FROM
	fusion.DOO_PROCESS_DEFINITIONS_VL dpdv
  , fusion.doo_process_steps_b        b
  , fusion.doo_process_steps_tl       tl
  , fusion.DOO_TASK_DEFINITIONS_VL    dtdV
  , fusion.DOO_TASK_TYPES_VL          dttv
WHERE
	dpdv.PROCESS_NAME            = 'DOO_OrderFulfillmentGenericProcess'
	AND dpdv.DOO_PROCESS_VERSION =
	(
		SELECT
			MAX (Mdpdv.DOO_PROCESS_VERSION)
		FROM
			fusion.DOO_PROCESS_DEFINITIONS_VL Mdpdv
		WHERE
			mdpdv.process_name              =dpdv.process_name
			AND PROCESS_RELEASE_STATUS_CODE = 'RELEASED'
	)
	AND dpdv.DOO_PROCESS_ID = b.DOO_PROCESS_ID
	AND b.step_id           = tl.step_id
	AND tl.language         = userenv('LANG')
	AND b.task_id           = dtdv.task_id (+)
	AND dtdv.TASK_TYPE_ID   = dttv.TASK_TYPE_ID (+)
ORDER BY
	tl.STEP_ID
-- The following SQL shows all the major components of an Order Orchestration Definition (DOO_OrderFulfillmentGenericProcess) and includes task statuses, orchestration and fulfillment status conditions.
SELECT
	dpdv.doo_process_ID
  , dpdv.PROCESS_NAME
  , TL.STEP_NAME
  , b.STEP_NUMBER
  , b.STEP_TYPE
  , b.PARENT_STEP_NUMBER
  , b.STEP_FILTER_RLS_ID
  , b.MANUAL_STEP_FLAG
  , b.COMP_PATTERN_RLS_ID
  , b.LEAD_TIME_RLS_ID
  , b.DEFAULT_LEAD_TIME
  , b.DEFAULT_LEAD_TIME_UOM
  , b.CUSTOMER_LAST_STEP_FLAG
  , b.SWITCH_RLS_ID
  , b.SWITCH_CONDITION_EVAL_SEQ
  , b.DEFAULT_BRANCH_FLAG
  , b.DEFAULT_EXIT_STATUS_CODE
  , b.EXIT_CRITERIA_STATUS_CODE
  , b.USE_DYNAMIC_ATTR_IN_DELTA_COMP
  , b.USE_FLEX_ATTR_IN_DELTA_COMP
  , b.HOLD_ON_WAIT
  , b.OTHERWISE_FLAG
  , b.START_AFTER_COND_RLS_ID
  , b.START_AFTER_COND_RLS_NAME
  , b.ATTRIBUTE_CATEGORY START_DATE_ACTIVE
  , b.END_DATE_ACTIVE
  , b.SUB_PROCESS_IDENTIFIER
  , b.UPDATE_SERVICE_ID
  , b.CANCEL_SERVICE_ID
  , b.HOLD_SERVICE_ID
  , b.ORCHESTRATION_APPLICATION_ID
  , dpdv.DOO_PROCESS_ID
  , dtdv.NAME
  , dtdv.DISPLAY_NAME
  , dtdv.TASK_CODE
  , dttv.TASK_TYPE
  , dttv.description
  , dts.STATUS_CODE
  , dpsc.SEQUENCE_NUMBER
  , dtdv.task_id
  , dpsc.CONDITION
  , dtdv.task_id
  , dlsc.condition
FROM
	fusion.DOO_PROCESS_DEFINITIONS_VL     dpdv
  , fusion.doo_process_steps_b            b
  , fusion.doo_process_steps_tl           tl
  , fusion.DOO_TASK_DEFINITIONS_VL        dtdV
  , fusion.DOO_TASK_TYPES_VL              dttv
  , fusion.DOO_TASK_STATUSES              dts
  , fusion.DOO_PROCESS_STATUS_CONDITIONS  dpsc
  , fusion.DOO_STATUS_RULESET_ASSIGNMENTS dsra
  , fusion.DOO_STATUS_RULESETS_VL         dsrv
  , fusion.DOO_LINE_STATUS_CONDITIONS     dlsc
WHERE
	dpdv.PROCESS_NAME            = 'DOO_OrderFulfillmentGenericProcess'
	AND dpdv.DOO_PROCESS_VERSION =
	(
		SELECT
			MAX (Mdpdv.DOO_PROCESS_VERSION)
		FROM
			fusion.DOO_PROCESS_DEFINITIONS_VL Mdpdv
		WHERE
			mdpdv.process_name              =dpdv.process_name
			AND PROCESS_RELEASE_STATUS_CODE = 'RELEASED'
	)
	AND dpdv.DOO_PROCESS_ID    = b.DOO_PROCESS_ID
	AND b.step_id              = tl.step_id
	AND tl.language            = userenv('LANG')
	AND b.task_id              = dtdv.task_id (+)
	AND dtdv.TASK_TYPE_ID      = dttv.TASK_TYPE_ID (+)
	AND dtdv.task_type_id      = dts.TASK_TYPE_ID (+)
	AND dpdv.doo_process_id    = dsra.process_id
	AND dsra.STATUS_RULESET_ID = dsrv.STATUS_RULESET_ID
	AND dsrv.STATUS_RULESET_ID = dlsc.STATUS_RULESET_ID(+)
	AND dpdv.doo_process_id    = dpsc.process_id (+)
	AND
	(
		dts.status_code          = dpsc.status_code (+)
		AND dpsc.condition(+) LIKE '"' || dtdv.task_id || '%"'
	)
	AND
	(
		dts.status_code          = dlsc.status_code (+)
		AND dlsc.condition(+) LIKE '"' || dtdv.task_id || '%"'
	)
ORDER BY
	tl.STEP_ID
-- The following SQL can be executed to identify the Fulfillment line Status Conditions. This is useful to identify which conditions raise business events or email notifications.
SELECT
	dpdv.process_name
  , dlsc.STATUS_CODE
  , dlsc.condition
  , dlsc.SEQUENCE_NUMBER
  , dlsc.COMPLETE_FLAG
  , dlsc.NOTIFY_FLAG
  , dlsc.EVENT_EMAIL_ENABLED_FLAG
FROM
	fusion.DOO_STATUS_RULESETS_VL         dsrv
  , fusion.DOO_STATUS_RULESET_ASSIGNMENTS dsra
  , fusion.DOO_LINE_STATUS_CONDITIONS     dlsc
  , fusion.DOO_PROCESS_DEFINITIONS_VL     dpdv
WHERE
	dsrv.STATUS_RULESET_ID     = dsra.STATUS_RULESET_ID
	AND dsra.STATUS_RULESET_ID = dlsc.STATUS_RULESET_ID
	AND process_id             = doo_process_id
	AND process_name           = 'DOO_OrderFulfillmentGenericProcess'
ORDER BY
	dpdv.process_name
  , dlsc.SEQUENCE_NUMBER