VIEW: APPS.OZF_OFFER_CLOB_V

Source

View Text - Preformatted

SELECT HDR.LIST_HEADER_ID, OZF_OFFER_HEADER (HDR.CONTEXT, HDR.ATTRIBUTE1, HDR.ATTRIBUTE2, HDR.ATTRIBUTE3, HDR.ATTRIBUTE4, HDR.ATTRIBUTE5, HDR.ATTRIBUTE6, HDR.ATTRIBUTE7, HDR.ATTRIBUTE8, HDR.ATTRIBUTE9, HDR.ATTRIBUTE10, HDR.ATTRIBUTE11, HDR.ATTRIBUTE12, HDR.ATTRIBUTE13, HDR.ATTRIBUTE14, HDR.ATTRIBUTE15, HDR.CURRENCY_CODE, HDR.SHIP_METHOD_CODE, HDR.FREIGHT_TERMS_CODE, HDR.LIST_HEADER_ID, HDR.CREATION_DATE, HDR.START_DATE_ACTIVE, HDR.END_DATE_ACTIVE, HDR.AUTOMATIC_FLAG, HDR.LIST_TYPE_CODE, HDR.TERMS_ID, HDR.ROUNDING_FACTOR, HDR.REQUEST_ID, HDR.CREATED_BY, HDR.LAST_UPDATE_DATE, HDR.LAST_UPDATED_BY, HDR.LAST_UPDATE_LOGIN, HDR.PROGRAM_APPLICATION_ID, HDR.PROGRAM_ID, HDR.PROGRAM_UPDATE_DATE, HDR.DISCOUNT_LINES_FLAG, HDR.NAME, HDR.DESCRIPTION, HDR.VERSION_NO, HDR.COMMENTS, HDR.GSA_INDICATOR, HDR.PRORATE_FLAG, HDR.SOURCE_SYSTEM_CODE, HDR.ASK_FOR_FLAG, HDR.PARENT_LIST_HEADER_ID, HDR.START_DATE_ACTIVE_FIRST, HDR.END_DATE_ACTIVE_FIRST, HDR.ACTIVE_DATE_FIRST_TYPE, HDR.START_DATE_ACTIVE_SECOND, HDR.END_DATE_ACTIVE_SECOND, HDR.ACTIVE_DATE_SECOND_TYPE, HDR.ACTIVE_FLAG, HDR.CURRENCY_HEADER_ID, HDR.PTE_CODE, HDR.LIST_SOURCE_CODE, HDR.ORIG_SYSTEM_HEADER_REF, HDR.GLOBAL_FLAG, HDR.ORIG_ORG_ID, HDR.VIEW_FLAG, HDR.UPDATE_FLAG, cast(multiset(select PROD.LIST_LINE_ID, PROD.CREATION_DATE, PROD.CREATED_BY, PROD.LAST_UPDATE_DATE, PROD.LAST_UPDATED_BY, PROD.LAST_UPDATE_LOGIN, PROD.PROGRAM_APPLICATION_ID, PROD.PROGRAM_ID, PROD.PROGRAM_UPDATE_DATE, PROD.REQUEST_ID, PROD.LIST_HEADER_ID, PROD.LIST_LINE_TYPE_CODE, PROD.AUTOMATIC_FLAG, PROD.MODIFIER_LEVEL_CODE, PROD.LIST_PRICE, PROD.LIST_PRICE_UOM_CODE, PROD.PRIMARY_UOM_FLAG, PROD.INVENTORY_ITEM_ID, PROD.ORGANIZATION_ID, PROD.RELATED_ITEM_ID, PROD.RELATIONSHIP_TYPE_ID, PROD.SUBSTITUTION_CONTEXT, PROD.SUBSTITUTION_ATTR, PROD.SUBSTITUTION_VAL, PROD.REVISION, PROD.REVISION_DATE, PROD.REVISION_REASON_CODE, PROD.CONTEXT, PROD.ATTRIBUTE1, PROD.ATTRIBUTE2, PROD.COMMENTS, PROD.ATTRIBUTE3, PROD.ATTRIBUTE4, PROD.ATTRIBUTE5, PROD.ATTRIBUTE6, PROD.ATTRIBUTE7, PROD.ATTRIBUTE8, PROD.ATTRIBUTE9, PROD.ATTRIBUTE10, PROD.INCLUDE_ON_RETURNS_FLAG, PROD.ATTRIBUTE11, PROD.ATTRIBUTE12, PROD.ATTRIBUTE13, PROD.ATTRIBUTE14, PROD.ATTRIBUTE15, PROD.PRICE_BREAK_TYPE_CODE, PROD.PERCENT_PRICE, PROD.EFFECTIVE_PERIOD_UOM, PROD.NUMBER_EFFECTIVE_PERIODS, PROD.OPERAND, PROD.ARITHMETIC_OPERATOR, PROD.OVERRIDE_FLAG, PROD.PRINT_ON_INVOICE_FLAG, PROD.REBATE_TRANSACTION_TYPE_CODE, PROD.DB_ESTIM_ACCRUAL_RATE, PROD.PRICE_BY_FORMULA_ID, PROD.GENERATE_USING_FORMULA_ID, PROD.START_DATE_ACTIVE, PROD.END_DATE_ACTIVE, PROD.REPRICE_FLAG, PROD.DB_ACCRUAL_FLAG, PROD.PRICING_GROUP_SEQUENCE, PROD.INCOMPATIBILITY_GRP_CODE, PROD.LIST_LINE_NO, PROD.PRODUCT_PRECEDENCE, PROD.PRICING_PHASE_ID, PROD.DB_EXP_PERIOD_START_DATE, PROD.DB_NUMBER_EXPIRATION_PERIODS, PROD.DB_EXPIRATION_PERIOD_UOM, PROD.DB_EXPIRATION_DATE, PROD.ESTIM_GL_VALUE, PROD.DB_ACCRUAL_CONVERSION_RATE, PROD.BENEFIT_PRICE_LIST_LINE_ID, PROD.PRORATION_TYPE_CODE, PROD.DB_BENEFIT_QTY, PROD.DB_BENEFIT_UOM_CODE, PROD.CHARGE_TYPE_CODE, PROD.CHARGE_SUBTYPE_CODE, PROD.BENEFIT_LIMIT, PROD.PRICING_ATTRIBUTE_ID, PROD.PRODUCT_ATTRIBUTE_CONTEXT, PROD.PRODUCT_ATTR, PROD.PRODUCT_ATTR_VAL, PROD.PRODUCT_UOM_CODE, PROD.COMPARISON_OPERATOR_CODE, PROD.PRICING_ATTRIBUTE_CONTEXT, PROD.PRICING_ATTR, PROD.PRICING_ATTR_VALUE_FROM, PROD.PRICING_ATTR_VALUE_TO, PROD.PRICING_ATTRIBUTE_DATATYPE, PROD.PRODUCT_ATTRIBUTE_DATATYPE, PROD.EXCLUDER_FLAG, PROD.ATTRIBUTE_GROUPING_NO, PROD.PARENT_LIST_LINE_ID, PROD.TO_RLTD_MODIFIER_ID, PROD.RLTD_MODIFIER_GRP_NO, PROD.RLTD_MODIFIER_GRP_TYPE, PROD.RLTD_MODIFIER_ID, PROD.PRORATION_TYPE, PROD.PRICING_PHASE, PROD.INCOMPATIBILITY_GRP, PROD.MODIFIER_LEVEL, PROD.LIST_LINE_TYPE, PROD.PRICE_BREAK_TYPE, PROD.CHARGE_NAME, PROD.FORMULA, PROD.ARITHMETIC_OPERATOR_TYPE, PROD.EXPIRATION_DATE, PROD.EXPIRATION_PERIOD_START_DATE, PROD.NUMBER_EXPIRATION_PERIODS, PROD.EXPIRATION_PERIOD_UOM, PROD.COUP_EXPIRATION_DATE, PROD.COUP_EXP_PERIOD_START_DATE, PROD.COUP_NUMBER_EXPIRATION_PERIODS, PROD.COUP_EXPIRATION_PERIOD_UOM, PROD.BRK_EXPIRATION_DATE, PROD.BRK_EXPIRATION_START_DATE, PROD.BRK_NUMBER_EXPIRATION_PERIODS, PROD.BRK_EXPIRATION_PERIOD_UOM, PROD.REBATE_TRANSACTION_TYPE, PROD.BRK_REB_TRANSACTION_TYPE, PROD.BENEFIT_QTY, PROD.BENEFIT_UOM_CODE, PROD.COUP_BENEFIT_QTY, PROD.COUP_BENEFIT_UOM_CODE, PROD.COUP_LIST_LINE_NO, PROD.ACCRUAL_FLAG, PROD.BRK_ACCRUAL_FLAG, PROD.ACCRUAL_CONVERSION_RATE, PROD.COUP_ACCRUAL_CONVERSION_RATE, PROD.COUP_ESTIM_ACCRUAL_RATE, PROD.BRK_ACCRUAL_CONVERSION_RATE, PROD.ESTIM_ACCRUAL_RATE, PROD.BRK_ESTIM_ACCRUAL_RATE, PROD.BREAK_LINE_TYPE_CODE, PROD.BREAK_LINE_TYPE, PROD.PRODUCT_ID, PROD.DESCRIPTION, PROD.PRICING_ATTR_SEG_NAME, PROD.PROD_ATTR_SEGMENT_NAME, PROD.RELATED_ITEM, PROD.SUBSTITUTION_ATTRIBUTE, PROD.SUBSTITUTION_VALUE, PROD.SUB_SEGMENT_NAME, PROD.PRODUCT_ATTRIBUTE_TYPE, PROD.PRODUCT_ATTR_VALUE, PROD.PRICING_ATTRIBUTE, PROD.NET_AMOUNT_FLAG FROM qp_modifier_summary_v prod WHERE PROD.LIST_HEADER_ID = HDR.LIST_HEADER_ID) AS OZF_PRODUCT_TABLE ), cast(multiset(select QUAL.QUALIFIER_ID, QUAL.CREATION_DATE, QUAL.CREATED_BY, QUAL.LAST_UPDATE_DATE, QUAL.LAST_UPDATED_BY, QUAL.LAST_UPDATE_LOGIN, QUAL.PROGRAM_APPLICATION_ID, QUAL.PROGRAM_ID, QUAL.PROGRAM_UPDATE_DATE, QUAL.REQUEST_ID, QUAL.EXCLUDER_FLAG, QUAL.COMPARISION_OPERATOR_CODE, QUAL.QUALIFIER_CONTEXT, QUAL.QUALIFIER_ATTRIBUTE, QUAL.CONTEXT, QUAL.ATTRIBUTE1, QUAL.ATTRIBUTE2, QUAL.ATTRIBUTE3, QUAL.ATTRIBUTE4, QUAL.ATTRIBUTE5, QUAL.ATTRIBUTE6, QUAL.ATTRIBUTE7, QUAL.ATTRIBUTE8, QUAL.ATTRIBUTE9, QUAL.ATTRIBUTE10, QUAL.ATTRIBUTE11, QUAL.ATTRIBUTE12, QUAL.ATTRIBUTE13, QUAL.ATTRIBUTE14, QUAL.ATTRIBUTE15, QUAL.QUALIFIER_RULE_ID, QUAL.QUALIFIER_GROUPING_NO, QUAL.QUALIFIER_ATTR_VALUE, QUAL.LIST_HEADER_ID, QUAL.LIST_LINE_ID, QUAL.CREATED_FROM_RULE_ID, QUAL.START_DATE_ACTIVE, QUAL.END_DATE_ACTIVE, QUAL.QUALIFIER_DATATYPE, QUAL.QUALIFIER_PRECEDENCE, QUAL.QUALIFIER_ATTR_VALUE_TO, QUAL.RULE_NAME, QUAL.RULE_DESCRIPTION FROM qp_qualifiers_v QUAL WHERE QUAL.LIST_HEADER_ID = HDR.LIST_HEADER_ID) AS OZF_QUALIFIER_TABLE), CAST(MULTISET(select fcst.forecast_id, fcst.forecast_uom_code, dim.product_attribute, dim.product_attr_value, prod.fact_value product_forecast, time.from_date, time.to_date, time.fact_value time_forecast from ozf_act_forecasts_all fcst, ozf_act_metric_facts_all prod , ozf_forecast_dimentions dim , ozf_act_metric_facts_all time where fcst.arc_act_fcast_used_by = 'OFFR' and fcst.act_fcast_used_by_id = HDR.LIST_HEADER_ID and fcst.freeze_flag = 'Y' and prod.arc_act_metric_used_by = 'FCST' and prod.act_metric_used_by_id = fcst.forecast_id and prod.fact_type = 'PRODUCT' and dim.forecast_dimention_id = prod.fact_reference and time.arc_act_metric_used_by = 'FCST' and time.act_metric_used_by_id = fcst.forecast_id and time.fact_type = 'TIME' and time.previous_fact_id = prod.activity_metric_fact_id) AS OZF_FORECAST_TABLE)) OFFER FROM QP_LIST_HEADERS_VL HDR

View Text - HTML Formatted

SELECT HDR.LIST_HEADER_ID
, OZF_OFFER_HEADER (HDR.CONTEXT
, HDR.ATTRIBUTE1
, HDR.ATTRIBUTE2
, HDR.ATTRIBUTE3
, HDR.ATTRIBUTE4
, HDR.ATTRIBUTE5
, HDR.ATTRIBUTE6
, HDR.ATTRIBUTE7
, HDR.ATTRIBUTE8
, HDR.ATTRIBUTE9
, HDR.ATTRIBUTE10
, HDR.ATTRIBUTE11
, HDR.ATTRIBUTE12
, HDR.ATTRIBUTE13
, HDR.ATTRIBUTE14
, HDR.ATTRIBUTE15
, HDR.CURRENCY_CODE
, HDR.SHIP_METHOD_CODE
, HDR.FREIGHT_TERMS_CODE
, HDR.LIST_HEADER_ID
, HDR.CREATION_DATE
, HDR.START_DATE_ACTIVE
, HDR.END_DATE_ACTIVE
, HDR.AUTOMATIC_FLAG
, HDR.LIST_TYPE_CODE
, HDR.TERMS_ID
, HDR.ROUNDING_FACTOR
, HDR.REQUEST_ID
, HDR.CREATED_BY
, HDR.LAST_UPDATE_DATE
, HDR.LAST_UPDATED_BY
, HDR.LAST_UPDATE_LOGIN
, HDR.PROGRAM_APPLICATION_ID
, HDR.PROGRAM_ID
, HDR.PROGRAM_UPDATE_DATE
, HDR.DISCOUNT_LINES_FLAG
, HDR.NAME
, HDR.DESCRIPTION
, HDR.VERSION_NO
, HDR.COMMENTS
, HDR.GSA_INDICATOR
, HDR.PRORATE_FLAG
, HDR.SOURCE_SYSTEM_CODE
, HDR.ASK_FOR_FLAG
, HDR.PARENT_LIST_HEADER_ID
, HDR.START_DATE_ACTIVE_FIRST
, HDR.END_DATE_ACTIVE_FIRST
, HDR.ACTIVE_DATE_FIRST_TYPE
, HDR.START_DATE_ACTIVE_SECOND
, HDR.END_DATE_ACTIVE_SECOND
, HDR.ACTIVE_DATE_SECOND_TYPE
, HDR.ACTIVE_FLAG
, HDR.CURRENCY_HEADER_ID
, HDR.PTE_CODE
, HDR.LIST_SOURCE_CODE
, HDR.ORIG_SYSTEM_HEADER_REF
, HDR.GLOBAL_FLAG
, HDR.ORIG_ORG_ID
, HDR.VIEW_FLAG
, HDR.UPDATE_FLAG
, CAST(MULTISET(SELECT PROD.LIST_LINE_ID
, PROD.CREATION_DATE
, PROD.CREATED_BY
, PROD.LAST_UPDATE_DATE
, PROD.LAST_UPDATED_BY
, PROD.LAST_UPDATE_LOGIN
, PROD.PROGRAM_APPLICATION_ID
, PROD.PROGRAM_ID
, PROD.PROGRAM_UPDATE_DATE
, PROD.REQUEST_ID
, PROD.LIST_HEADER_ID
, PROD.LIST_LINE_TYPE_CODE
, PROD.AUTOMATIC_FLAG
, PROD.MODIFIER_LEVEL_CODE
, PROD.LIST_PRICE
, PROD.LIST_PRICE_UOM_CODE
, PROD.PRIMARY_UOM_FLAG
, PROD.INVENTORY_ITEM_ID
, PROD.ORGANIZATION_ID
, PROD.RELATED_ITEM_ID
, PROD.RELATIONSHIP_TYPE_ID
, PROD.SUBSTITUTION_CONTEXT
, PROD.SUBSTITUTION_ATTR
, PROD.SUBSTITUTION_VAL
, PROD.REVISION
, PROD.REVISION_DATE
, PROD.REVISION_REASON_CODE
, PROD.CONTEXT
, PROD.ATTRIBUTE1
, PROD.ATTRIBUTE2
, PROD.COMMENTS
, PROD.ATTRIBUTE3
, PROD.ATTRIBUTE4
, PROD.ATTRIBUTE5
, PROD.ATTRIBUTE6
, PROD.ATTRIBUTE7
, PROD.ATTRIBUTE8
, PROD.ATTRIBUTE9
, PROD.ATTRIBUTE10
, PROD.INCLUDE_ON_RETURNS_FLAG
, PROD.ATTRIBUTE11
, PROD.ATTRIBUTE12
, PROD.ATTRIBUTE13
, PROD.ATTRIBUTE14
, PROD.ATTRIBUTE15
, PROD.PRICE_BREAK_TYPE_CODE
, PROD.PERCENT_PRICE
, PROD.EFFECTIVE_PERIOD_UOM
, PROD.NUMBER_EFFECTIVE_PERIODS
, PROD.OPERAND
, PROD.ARITHMETIC_OPERATOR
, PROD.OVERRIDE_FLAG
, PROD.PRINT_ON_INVOICE_FLAG
, PROD.REBATE_TRANSACTION_TYPE_CODE
, PROD.DB_ESTIM_ACCRUAL_RATE
, PROD.PRICE_BY_FORMULA_ID
, PROD.GENERATE_USING_FORMULA_ID
, PROD.START_DATE_ACTIVE
, PROD.END_DATE_ACTIVE
, PROD.REPRICE_FLAG
, PROD.DB_ACCRUAL_FLAG
, PROD.PRICING_GROUP_SEQUENCE
, PROD.INCOMPATIBILITY_GRP_CODE
, PROD.LIST_LINE_NO
, PROD.PRODUCT_PRECEDENCE
, PROD.PRICING_PHASE_ID
, PROD.DB_EXP_PERIOD_START_DATE
, PROD.DB_NUMBER_EXPIRATION_PERIODS
, PROD.DB_EXPIRATION_PERIOD_UOM
, PROD.DB_EXPIRATION_DATE
, PROD.ESTIM_GL_VALUE
, PROD.DB_ACCRUAL_CONVERSION_RATE
, PROD.BENEFIT_PRICE_LIST_LINE_ID
, PROD.PRORATION_TYPE_CODE
, PROD.DB_BENEFIT_QTY
, PROD.DB_BENEFIT_UOM_CODE
, PROD.CHARGE_TYPE_CODE
, PROD.CHARGE_SUBTYPE_CODE
, PROD.BENEFIT_LIMIT
, PROD.PRICING_ATTRIBUTE_ID
, PROD.PRODUCT_ATTRIBUTE_CONTEXT
, PROD.PRODUCT_ATTR
, PROD.PRODUCT_ATTR_VAL
, PROD.PRODUCT_UOM_CODE
, PROD.COMPARISON_OPERATOR_CODE
, PROD.PRICING_ATTRIBUTE_CONTEXT
, PROD.PRICING_ATTR
, PROD.PRICING_ATTR_VALUE_FROM
, PROD.PRICING_ATTR_VALUE_TO
, PROD.PRICING_ATTRIBUTE_DATATYPE
, PROD.PRODUCT_ATTRIBUTE_DATATYPE
, PROD.EXCLUDER_FLAG
, PROD.ATTRIBUTE_GROUPING_NO
, PROD.PARENT_LIST_LINE_ID
, PROD.TO_RLTD_MODIFIER_ID
, PROD.RLTD_MODIFIER_GRP_NO
, PROD.RLTD_MODIFIER_GRP_TYPE
, PROD.RLTD_MODIFIER_ID
, PROD.PRORATION_TYPE
, PROD.PRICING_PHASE
, PROD.INCOMPATIBILITY_GRP
, PROD.MODIFIER_LEVEL
, PROD.LIST_LINE_TYPE
, PROD.PRICE_BREAK_TYPE
, PROD.CHARGE_NAME
, PROD.FORMULA
, PROD.ARITHMETIC_OPERATOR_TYPE
, PROD.EXPIRATION_DATE
, PROD.EXPIRATION_PERIOD_START_DATE
, PROD.NUMBER_EXPIRATION_PERIODS
, PROD.EXPIRATION_PERIOD_UOM
, PROD.COUP_EXPIRATION_DATE
, PROD.COUP_EXP_PERIOD_START_DATE
, PROD.COUP_NUMBER_EXPIRATION_PERIODS
, PROD.COUP_EXPIRATION_PERIOD_UOM
, PROD.BRK_EXPIRATION_DATE
, PROD.BRK_EXPIRATION_START_DATE
, PROD.BRK_NUMBER_EXPIRATION_PERIODS
, PROD.BRK_EXPIRATION_PERIOD_UOM
, PROD.REBATE_TRANSACTION_TYPE
, PROD.BRK_REB_TRANSACTION_TYPE
, PROD.BENEFIT_QTY
, PROD.BENEFIT_UOM_CODE
, PROD.COUP_BENEFIT_QTY
, PROD.COUP_BENEFIT_UOM_CODE
, PROD.COUP_LIST_LINE_NO
, PROD.ACCRUAL_FLAG
, PROD.BRK_ACCRUAL_FLAG
, PROD.ACCRUAL_CONVERSION_RATE
, PROD.COUP_ACCRUAL_CONVERSION_RATE
, PROD.COUP_ESTIM_ACCRUAL_RATE
, PROD.BRK_ACCRUAL_CONVERSION_RATE
, PROD.ESTIM_ACCRUAL_RATE
, PROD.BRK_ESTIM_ACCRUAL_RATE
, PROD.BREAK_LINE_TYPE_CODE
, PROD.BREAK_LINE_TYPE
, PROD.PRODUCT_ID
, PROD.DESCRIPTION
, PROD.PRICING_ATTR_SEG_NAME
, PROD.PROD_ATTR_SEGMENT_NAME
, PROD.RELATED_ITEM
, PROD.SUBSTITUTION_ATTRIBUTE
, PROD.SUBSTITUTION_VALUE
, PROD.SUB_SEGMENT_NAME
, PROD.PRODUCT_ATTRIBUTE_TYPE
, PROD.PRODUCT_ATTR_VALUE
, PROD.PRICING_ATTRIBUTE
, PROD.NET_AMOUNT_FLAG
FROM QP_MODIFIER_SUMMARY_V PROD
WHERE PROD.LIST_HEADER_ID = HDR.LIST_HEADER_ID) AS OZF_PRODUCT_TABLE )
, CAST(MULTISET(SELECT QUAL.QUALIFIER_ID
, QUAL.CREATION_DATE
, QUAL.CREATED_BY
, QUAL.LAST_UPDATE_DATE
, QUAL.LAST_UPDATED_BY
, QUAL.LAST_UPDATE_LOGIN
, QUAL.PROGRAM_APPLICATION_ID
, QUAL.PROGRAM_ID
, QUAL.PROGRAM_UPDATE_DATE
, QUAL.REQUEST_ID
, QUAL.EXCLUDER_FLAG
, QUAL.COMPARISION_OPERATOR_CODE
, QUAL.QUALIFIER_CONTEXT
, QUAL.QUALIFIER_ATTRIBUTE
, QUAL.CONTEXT
, QUAL.ATTRIBUTE1
, QUAL.ATTRIBUTE2
, QUAL.ATTRIBUTE3
, QUAL.ATTRIBUTE4
, QUAL.ATTRIBUTE5
, QUAL.ATTRIBUTE6
, QUAL.ATTRIBUTE7
, QUAL.ATTRIBUTE8
, QUAL.ATTRIBUTE9
, QUAL.ATTRIBUTE10
, QUAL.ATTRIBUTE11
, QUAL.ATTRIBUTE12
, QUAL.ATTRIBUTE13
, QUAL.ATTRIBUTE14
, QUAL.ATTRIBUTE15
, QUAL.QUALIFIER_RULE_ID
, QUAL.QUALIFIER_GROUPING_NO
, QUAL.QUALIFIER_ATTR_VALUE
, QUAL.LIST_HEADER_ID
, QUAL.LIST_LINE_ID
, QUAL.CREATED_FROM_RULE_ID
, QUAL.START_DATE_ACTIVE
, QUAL.END_DATE_ACTIVE
, QUAL.QUALIFIER_DATATYPE
, QUAL.QUALIFIER_PRECEDENCE
, QUAL.QUALIFIER_ATTR_VALUE_TO
, QUAL.RULE_NAME
, QUAL.RULE_DESCRIPTION
FROM QP_QUALIFIERS_V QUAL
WHERE QUAL.LIST_HEADER_ID = HDR.LIST_HEADER_ID) AS OZF_QUALIFIER_TABLE)
, CAST(MULTISET(SELECT FCST.FORECAST_ID
, FCST.FORECAST_UOM_CODE
, DIM.PRODUCT_ATTRIBUTE
, DIM.PRODUCT_ATTR_VALUE
, PROD.FACT_VALUE PRODUCT_FORECAST
, TIME.FROM_DATE
, TIME.TO_DATE
, TIME.FACT_VALUE TIME_FORECAST
FROM OZF_ACT_FORECASTS_ALL FCST
, OZF_ACT_METRIC_FACTS_ALL PROD
, OZF_FORECAST_DIMENTIONS DIM
, OZF_ACT_METRIC_FACTS_ALL TIME
WHERE FCST.ARC_ACT_FCAST_USED_BY = 'OFFR'
AND FCST.ACT_FCAST_USED_BY_ID = HDR.LIST_HEADER_ID
AND FCST.FREEZE_FLAG = 'Y'
AND PROD.ARC_ACT_METRIC_USED_BY = 'FCST'
AND PROD.ACT_METRIC_USED_BY_ID = FCST.FORECAST_ID
AND PROD.FACT_TYPE = 'PRODUCT'
AND DIM.FORECAST_DIMENTION_ID = PROD.FACT_REFERENCE
AND TIME.ARC_ACT_METRIC_USED_BY = 'FCST'
AND TIME.ACT_METRIC_USED_BY_ID = FCST.FORECAST_ID
AND TIME.FACT_TYPE = 'TIME'
AND TIME.PREVIOUS_FACT_ID = PROD.ACTIVITY_METRIC_FACT_ID) AS OZF_FORECAST_TABLE)) OFFER
FROM QP_LIST_HEADERS_VL HDR