ZSP_PRODUCT_GROUPS

Details

Schema: FUSION
Object owner: ZSP
Object type: VIEW

Columns

Name
PRODUCT_ID PROD_OR_PRODGRP_ID ORGANIZATION_ID PRODUCT_CODE PRODUCT_NAME PRODUCT_DESC ZSP_PRODUCT_DESC PRODUCT_TYPE SOURCE_CODE CREATION_DATE ZSP_CREATION_DATE

Name

PRODUCT_ID

PROD_OR_PRODGRP_ID

ORGANIZATION_ID

PRODUCT_CODE

PRODUCT_NAME

PRODUCT_DESC

ZSP_PRODUCT_DESC

PRODUCT_TYPE

SOURCE_CODE

CREATION_DATE

ZSP_CREATION_DATE

Query

SQL_Statement
( SELECT ZSP_PRODUCT_GROUPS.PRODUCT_ID AS PRODUCT_ID, EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID AS PROD_OR_PRODGRP_ID, EGP_SYSTEM_ITEMS_VL.ORGANIZATION_ID AS ORGANIZATION_ID, EGP_SYSTEM_ITEMS_VL.ITEM_NUMBER AS PRODUCT_CODE, EGP_SYSTEM_ITEMS_VL.DESCRIPTION AS PRODUCT_NAME, EGP_SYSTEM_ITEMS_VL.LONG_DESCRIPTION AS PRODUCT_DESC, EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID\|\|'~'\|\|DESCRIPTION AS ZSP_PRODUCT_DESC, 'I' AS PRODUCT_TYPE, ZSP_PRODUCT_GROUPS.SOURCE_CODE AS SOURCE_CODE, EGP_SYSTEM_ITEMS_VL.CREATION_DATE CREATION_DATE, ZSP_PRODUCT_GROUPS.CREATION_DATE ZSP_CREATION_DATE FROM EGP_SYSTEM_ITEMS_VL, ZSP_PRODUCT_GROUPS, QSC_PROD_GRP_ITEMS_TREE_V WHERE EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID = ZSP_PRODUCT_GROUPS.INVENTORY_ITEM_ID AND EGP_SYSTEM_ITEMS_VL.ORGANIZATION_ID = ZSP_PRODUCT_GROUPS.INVENTORY_ORG_ID AND QSC_PROD_GRP_ITEMS_TREE_V.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID AND QSC_PROD_GRP_ITEMS_TREE_V.INV_ORG_ID = EGP_SYSTEM_ITEMS_VL.ORGANIZATION_ID AND QSC_PROD_GRP_ITEMS_TREE_V.PGITEM_ACTIVE_FLAG = 'Y' AND QSC_PROD_GRP_ITEMS_TREE_V.USAGE_CODE = 'BASE' AND QSC_PROD_GRP_ITEMS_TREE_V.CURR_DATE_SNAPSHOT_FLAG = 'Y' AND QSC_PROD_GRP_ITEMS_TREE_V.REL_ACTIVE_FLAG = 'Y' ) UNION ALL ( SELECT ZSP_PRODUCT_GROUPS.PRODUCT_ID AS PRODUCT_ID, QSC_PROD_GRP_DETAILS_V.PROD_GROUP_ID PROD_OR_PRODGRP_ID, NULL ORGANIZATION_ID, QSC_PROD_GRP_DETAILS_V.INTERNAL_NAME AS PRODUCT_CODE, QSC_PROD_GRP_DETAILS_V.PROD_GROUP_NAME AS PRODUCT_NAME, QSC_PROD_GRP_DETAILS_V.PROD_GROUP_DESC_TEXT AS PRODUCT_DESC, QSC_PROD_GRP_DETAILS_V.PROD_GROUP_ID\|\|'~'\|\|QSC_PROD_GRP_DETAILS_V.PROD_GROUP_NAME ZSP_PRODUCT_DESC, 'PG' PRODUCT_TYPE, ZSP_PRODUCT_GROUPS.SOURCE_CODE AS SOURCE_CODE, QSC_PROD_GRP_DETAILS_V.CREATION_DATE CREATION_DATE, ZSP_PRODUCT_GROUPS.CREATION_DATE ZSP_CREATION_DATE FROM QSC_PROD_GRP_DETAILS_V, ZSP_PRODUCT_GROUPS WHERE QSC_PROD_GRP_DETAILS_V.PROD_GROUP_ID = ZSP_PRODUCT_GROUPS.PROD_GROUP_ID AND QSC_PROD_GRP_DETAILS_V.START_DATE <= sysdate AND QSC_PROD_GRP_DETAILS_V.END_DATE > sysdate AND QSC_PROD_GRP_DETAILS_V.ACTIVE_FLAG = 'Y' )

SQL_Statement

(

SELECT

ZSP_PRODUCT_GROUPS.PRODUCT_ID AS PRODUCT_ID,

EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID AS PROD_OR_PRODGRP_ID,

EGP_SYSTEM_ITEMS_VL.ORGANIZATION_ID AS ORGANIZATION_ID,

EGP_SYSTEM_ITEMS_VL.ITEM_NUMBER AS PRODUCT_CODE,

EGP_SYSTEM_ITEMS_VL.DESCRIPTION AS PRODUCT_NAME,

EGP_SYSTEM_ITEMS_VL.LONG_DESCRIPTION AS PRODUCT_DESC,

EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID||'~'||DESCRIPTION AS ZSP_PRODUCT_DESC,

'I' AS PRODUCT_TYPE,

ZSP_PRODUCT_GROUPS.SOURCE_CODE AS SOURCE_CODE,

EGP_SYSTEM_ITEMS_VL.CREATION_DATE CREATION_DATE,

ZSP_PRODUCT_GROUPS.CREATION_DATE ZSP_CREATION_DATE

FROM EGP_SYSTEM_ITEMS_VL, ZSP_PRODUCT_GROUPS,

QSC_PROD_GRP_ITEMS_TREE_V

WHERE EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID = ZSP_PRODUCT_GROUPS.INVENTORY_ITEM_ID

AND EGP_SYSTEM_ITEMS_VL.ORGANIZATION_ID = ZSP_PRODUCT_GROUPS.INVENTORY_ORG_ID

AND QSC_PROD_GRP_ITEMS_TREE_V.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_VL.INVENTORY_ITEM_ID

AND QSC_PROD_GRP_ITEMS_TREE_V.INV_ORG_ID = EGP_SYSTEM_ITEMS_VL.ORGANIZATION_ID

AND QSC_PROD_GRP_ITEMS_TREE_V.PGITEM_ACTIVE_FLAG = 'Y'

AND QSC_PROD_GRP_ITEMS_TREE_V.USAGE_CODE = 'BASE'

AND QSC_PROD_GRP_ITEMS_TREE_V.CURR_DATE_SNAPSHOT_FLAG = 'Y'

AND QSC_PROD_GRP_ITEMS_TREE_V.REL_ACTIVE_FLAG = 'Y'

)

UNION ALL

(

SELECT

ZSP_PRODUCT_GROUPS.PRODUCT_ID AS PRODUCT_ID,

QSC_PROD_GRP_DETAILS_V.PROD_GROUP_ID PROD_OR_PRODGRP_ID,

NULL ORGANIZATION_ID,

QSC_PROD_GRP_DETAILS_V.INTERNAL_NAME AS PRODUCT_CODE,

QSC_PROD_GRP_DETAILS_V.PROD_GROUP_NAME AS PRODUCT_NAME,

QSC_PROD_GRP_DETAILS_V.PROD_GROUP_DESC_TEXT AS PRODUCT_DESC,

QSC_PROD_GRP_DETAILS_V.PROD_GROUP_ID||'~'||QSC_PROD_GRP_DETAILS_V.PROD_GROUP_NAME ZSP_PRODUCT_DESC,

'PG' PRODUCT_TYPE,

ZSP_PRODUCT_GROUPS.SOURCE_CODE AS SOURCE_CODE,

QSC_PROD_GRP_DETAILS_V.CREATION_DATE CREATION_DATE,

ZSP_PRODUCT_GROUPS.CREATION_DATE ZSP_CREATION_DATE

FROM

QSC_PROD_GRP_DETAILS_V, ZSP_PRODUCT_GROUPS

WHERE QSC_PROD_GRP_DETAILS_V.PROD_GROUP_ID = ZSP_PRODUCT_GROUPS.PROD_GROUP_ID

AND QSC_PROD_GRP_DETAILS_V.START_DATE <= sysdate

AND QSC_PROD_GRP_DETAILS_V.END_DATE > sysdate

AND QSC_PROD_GRP_DETAILS_V.ACTIVE_FLAG = 'Y'

)