VIEW: APPS.CSD_RETURNS_BI_POM_MV#

Source

View Text - Preformatted

(
select
       rcv.operating_unit_id,
       rcv.inventory_org_id,
       rcv.inventory_item_id,
       -1*sum(rcv.received_quantity) total_quantity,
       rcv.primary_uom_code,
       rcv.transaction_date,
       -1*sum(rcv.received_weight) total_weight,
       rcv.weight_uom_code,
       rcv.category_id,
       rcv.category_set_id,
       rcv.ship_geography_type,
       rcv.ship_geography_id,
       rcv.customer_type  
from CSD_RETURNS_BI_RCV_TXNS_F rcv
where rcv.pom_offset = 'T'
group by
    inventory_item_id,
    primary_uom_code,
    inventory_org_id,
    transaction_date,
    operating_unit_id,
    weight_uom_code,
    category_id,
    category_set_id,
    ship_geography_type,
    ship_geography_id,
    customer_type
)
UNION ALL
(
select
       shp.operating_unit_id,
       shp.inventory_org_id,
       shp.inventory_item_id,
       sum(shp.shipped_quantity) total_quantity,
       shp.primary_uom_code,
       shp.transaction_date,
       sum(shp.shipped_weight) total_weight,
       shp.weight_uom_code,
       shp.category_id,
       shp.category_set_id,
       shp.ship_geography_type,
       shp.ship_geography_id,
       shp.customer_type  
from CSD_RETURNS_BI_SHP_TXNS_F shp
group by
    inventory_item_id,
    primary_uom_code,
    inventory_org_id,
    transaction_date,
    operating_unit_id,
    weight_uom_code,
    category_id,
    category_set_id,
    ship_geography_type,
    ship_geography_id,
    customer_type
)

View Text - HTML Formatted

( SELECT RCV.OPERATING_UNIT_ID
, RCV.INVENTORY_ORG_ID
, RCV.INVENTORY_ITEM_ID
, -1*SUM(RCV.RECEIVED_QUANTITY) TOTAL_QUANTITY
, RCV.PRIMARY_UOM_CODE
, RCV.TRANSACTION_DATE
, -1*SUM(RCV.RECEIVED_WEIGHT) TOTAL_WEIGHT
, RCV.WEIGHT_UOM_CODE
, RCV.CATEGORY_ID
, RCV.CATEGORY_SET_ID
, RCV.SHIP_GEOGRAPHY_TYPE
, RCV.SHIP_GEOGRAPHY_ID
, RCV.CUSTOMER_TYPE FROM CSD_RETURNS_BI_RCV_TXNS_F RCV WHERE RCV.POM_OFFSET = 'T' GROUP BY INVENTORY_ITEM_ID
, PRIMARY_UOM_CODE
, INVENTORY_ORG_ID
, TRANSACTION_DATE
, OPERATING_UNIT_ID
, WEIGHT_UOM_CODE
, CATEGORY_ID
, CATEGORY_SET_ID
, SHIP_GEOGRAPHY_TYPE
, SHIP_GEOGRAPHY_ID
, CUSTOMER_TYPE ) UNION ALL ( SELECT SHP.OPERATING_UNIT_ID
, SHP.INVENTORY_ORG_ID
, SHP.INVENTORY_ITEM_ID
, SUM(SHP.SHIPPED_QUANTITY) TOTAL_QUANTITY
, SHP.PRIMARY_UOM_CODE
, SHP.TRANSACTION_DATE
, SUM(SHP.SHIPPED_WEIGHT) TOTAL_WEIGHT
, SHP.WEIGHT_UOM_CODE
, SHP.CATEGORY_ID
, SHP.CATEGORY_SET_ID
, SHP.SHIP_GEOGRAPHY_TYPE
, SHP.SHIP_GEOGRAPHY_ID
, SHP.CUSTOMER_TYPE FROM CSD_RETURNS_BI_SHP_TXNS_F SHP GROUP BY INVENTORY_ITEM_ID
, PRIMARY_UOM_CODE
, INVENTORY_ORG_ID
, TRANSACTION_DATE
, OPERATING_UNIT_ID
, WEIGHT_UOM_CODE
, CATEGORY_ID
, CATEGORY_SET_ID
, SHIP_GEOGRAPHY_TYPE
, SHIP_GEOGRAPHY_ID
, CUSTOMER_TYPE )