SELECT NVL(TPAGV.EXTENSION_ID , ITEMAGV.EXTENSION_ID ) EXTENSION_ID , NVL(TPAGV.INVENTORY_ITEM_ID , ITEMAGV.INVENTORY_ITEM_ID ) INVENTORY_ITEM_ID , NVL(TPAGV.MASTER_ORGANIZATION_ID , ITEMAGV.ORGANIZATION_ID ) MASTER_ORGANIZATION_ID , TPAGV.PARTY_SITE_ID PARTY_SITE_ID , NVL(TPAGV.FIRST_DELIVERY_DATE_TIME , ITEMAGV.FIRST_DELIVERY_DATE_TIME ) FIRST_DELIVERY_DATE_TIME , NVL(TPAGV.GOODS_PICK_UP_LEAD_TIME , ITEMAGV.GOODS_PICK_UP_LEAD_TIME ) GOODS_PICK_UP_LEAD_TIME , NVL(TPAGV.FIRST_ORDER_DATE , ITEMAGV.FIRST_ORDER_DATE ) FIRST_ORDER_DATE , NVL(TPAGV.LAST_ORDER_DATE , ITEMAGV.LAST_ORDER_DATE ) LAST_ORDER_DATE , NVL(TPAGV.IS_TRADE_ITEM_REORDERABLE , ITEMAGV.IS_TRADE_ITEM_REORDERABLE ) IS_TRADE_ITEM_REORDERABLE FROM EGO_SBDH_ORDERING_INFO_AGV ITEMAGV , EGO_SBDH_ORDERING_INFO_TPAGV TPAGV WHERE ITEMAGV.INVENTORY_ITEM_ID = TPAGV.INVENTORY_ITEM_ID(+) AND ITEMAGV.ORGANIZATION_ID = TPAGV.MASTER_ORGANIZATION_ID(+) UNION ALL SELECT EXTENSION_ID , INVENTORY_ITEM_ID , MASTER_ORGANIZATION_ID , PARTY_SITE_ID , FIRST_DELIVERY_DATE_TIME , GOODS_PICK_UP_LEAD_TIME , FIRST_ORDER_DATE , LAST_ORDER_DATE , IS_TRADE_ITEM_REORDERABLE FROM EGO_SBDH_ORDERING_INFO_TPAGV TPAGV WHERE NOT EXISTS ( SELECT 'X' FROM EGO_SBDH_ORDERING_INFO_AGV ITEMAGV WHERE INVENTORY_ITEM_ID = TPAGV.INVENTORY_ITEM_ID AND ORGANIZATION_ID = TPAGV.MASTER_ORGANIZATION_ID ) UNION ALL SELECT EXTENSION_ID , INVENTORY_ITEM_ID , MASTER_ORGANIZATION_ID , PARTY_SITE_ID , FIRST_DELIVERY_DATE_TIME , GOODS_PICK_UP_LEAD_TIME , FIRST_ORDER_DATE , LAST_ORDER_DATE , IS_TRADE_ITEM_REORDERABLE FROM (SELECT NULL PARTY_SITE_ID, ITEMAGV.* , ITEMAGV.ORGANIZATION_ID MASTER_ORGANIZATION_ID FROM EGO_SBDH_ORDERING_INFO_AGV ITEMAGV WHERE EXISTS ( SELECT 'X' FROM EGO_SBDH_ORDERING_INFO_TPAGV TPAGV WHERE INVENTORY_ITEM_ID = ITEMAGV.INVENTORY_ITEM_ID AND MASTER_ORGANIZATION_ID = ITEMAGV.ORGANIZATION_ID ) )
SELECT NVL(TPAGV.EXTENSION_ID
, ITEMAGV.EXTENSION_ID ) EXTENSION_ID
, NVL(TPAGV.INVENTORY_ITEM_ID
, ITEMAGV.INVENTORY_ITEM_ID ) INVENTORY_ITEM_ID
, NVL(TPAGV.MASTER_ORGANIZATION_ID
, ITEMAGV.ORGANIZATION_ID ) MASTER_ORGANIZATION_ID
, TPAGV.PARTY_SITE_ID PARTY_SITE_ID
, NVL(TPAGV.FIRST_DELIVERY_DATE_TIME
, ITEMAGV.FIRST_DELIVERY_DATE_TIME ) FIRST_DELIVERY_DATE_TIME
, NVL(TPAGV.GOODS_PICK_UP_LEAD_TIME
, ITEMAGV.GOODS_PICK_UP_LEAD_TIME ) GOODS_PICK_UP_LEAD_TIME
, NVL(TPAGV.FIRST_ORDER_DATE
, ITEMAGV.FIRST_ORDER_DATE ) FIRST_ORDER_DATE
, NVL(TPAGV.LAST_ORDER_DATE
, ITEMAGV.LAST_ORDER_DATE ) LAST_ORDER_DATE
, NVL(TPAGV.IS_TRADE_ITEM_REORDERABLE
, ITEMAGV.IS_TRADE_ITEM_REORDERABLE ) IS_TRADE_ITEM_REORDERABLE
FROM EGO_SBDH_ORDERING_INFO_AGV ITEMAGV
, EGO_SBDH_ORDERING_INFO_TPAGV TPAGV
WHERE ITEMAGV.INVENTORY_ITEM_ID = TPAGV.INVENTORY_ITEM_ID(+)
AND ITEMAGV.ORGANIZATION_ID = TPAGV.MASTER_ORGANIZATION_ID(+) UNION ALL SELECT EXTENSION_ID
, INVENTORY_ITEM_ID
, MASTER_ORGANIZATION_ID
, PARTY_SITE_ID
, FIRST_DELIVERY_DATE_TIME
, GOODS_PICK_UP_LEAD_TIME
, FIRST_ORDER_DATE
, LAST_ORDER_DATE
, IS_TRADE_ITEM_REORDERABLE
FROM EGO_SBDH_ORDERING_INFO_TPAGV TPAGV
WHERE NOT EXISTS ( SELECT 'X'
FROM EGO_SBDH_ORDERING_INFO_AGV ITEMAGV
WHERE INVENTORY_ITEM_ID = TPAGV.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = TPAGV.MASTER_ORGANIZATION_ID ) UNION ALL SELECT EXTENSION_ID
, INVENTORY_ITEM_ID
, MASTER_ORGANIZATION_ID
, PARTY_SITE_ID
, FIRST_DELIVERY_DATE_TIME
, GOODS_PICK_UP_LEAD_TIME
, FIRST_ORDER_DATE
, LAST_ORDER_DATE
, IS_TRADE_ITEM_REORDERABLE
FROM (SELECT NULL PARTY_SITE_ID
, ITEMAGV.*
, ITEMAGV.ORGANIZATION_ID MASTER_ORGANIZATION_ID
FROM EGO_SBDH_ORDERING_INFO_AGV ITEMAGV
WHERE EXISTS ( SELECT 'X'
FROM EGO_SBDH_ORDERING_INFO_TPAGV TPAGV
WHERE INVENTORY_ITEM_ID = ITEMAGV.INVENTORY_ITEM_ID
AND MASTER_ORGANIZATION_ID = ITEMAGV.ORGANIZATION_ID ) )
|
|
|