VIEW: APPS.MSC_PHUB_ORGS_MV#

Source

View Text - Preformatted

select     mtp.sr_instance_id,     nvl(mtp.operating_unit, -23453) operating_unit,     mtp.operating_unit_name,     mtp.sr_tp_id organization_id,     mtp.organization_code,     mio.organization_type,     mtp.currency_code,     mtp.partner_name organization_name,     decode(mtp.modeled_customer_id,null,2,1) modeled_customer_flag,     decode(mtp.modeled_supplier_id,null,2,1) modeled_supplier_flag,     nvl(sub.sub_inventory_code, '-23453') sub_inventory_code,     sub.description sub_inventory_desc from     msc_trading_partners mtp,     msc_instance_orgs mio,     msc_sub_inventories sub where mtp.partner_type=3     and mtp.sr_instance_id=mio.sr_instance_id(+)     and mtp.sr_tp_id=mio.organization_id(+)     and mtp.sr_instance_id=sub.sr_instance_id(+)     and mtp.sr_tp_id=sub.organization_id(+)     and sub.plan_id(+)=-1 union select     mtp.sr_instance_id,     nvl(mtp.operating_unit, -23453) operating_unit,     mtp.operating_unit_name,     mtp.sr_tp_id organization_id,     mtp.organization_code,     mio.organization_type,     mtp.currency_code,     mtp.partner_name organization_name,     decode(mtp.modeled_customer_id,null,2,1) modeled_customer_flag,     decode(mtp.modeled_supplier_id,null,2,1) modeled_supplier_flag,     '-23453' sub_inventory_code,     to_char(null) sub_inventory_desc from     msc_trading_partners mtp,     msc_instance_orgs mio where mtp.partner_type=3     and mtp.sr_instance_id=mio.sr_instance_id(+)     and mtp.sr_tp_id=mio.organization_id(+) union all select     mai.sr_instance_id,     -23453 operating_unit,     to_char(null) operating_unit_name,     -23453 organization_id,     to_char(null) organization_code,     to_number(null) organization_type,     'XXX' currency_code,     to_char(null) organization_name,     to_number(null) modeled_customer_flag,     to_number(null) modeled_supplier_flag,     '-23453' sub_inventory_code,     to_char(null) sub_inventory_desc from (select distinct instance_id sr_instance_id from msc_apps_instances     union all select to_number(-23453) from dual) mai

View Text - HTML Formatted

SELECT MTP.SR_INSTANCE_ID
, NVL(MTP.OPERATING_UNIT
, -23453) OPERATING_UNIT
, MTP.OPERATING_UNIT_NAME
, MTP.SR_TP_ID ORGANIZATION_ID
, MTP.ORGANIZATION_CODE
, MIO.ORGANIZATION_TYPE
, MTP.CURRENCY_CODE
, MTP.PARTNER_NAME ORGANIZATION_NAME
, DECODE(MTP.MODELED_CUSTOMER_ID
, NULL
, 2
, 1) MODELED_CUSTOMER_FLAG
, DECODE(MTP.MODELED_SUPPLIER_ID
, NULL
, 2
, 1) MODELED_SUPPLIER_FLAG
, NVL(SUB.SUB_INVENTORY_CODE
, '-23453') SUB_INVENTORY_CODE
, SUB.DESCRIPTION SUB_INVENTORY_DESC
FROM MSC_TRADING_PARTNERS MTP
, MSC_INSTANCE_ORGS MIO
, MSC_SUB_INVENTORIES SUB
WHERE MTP.PARTNER_TYPE=3
AND MTP.SR_INSTANCE_ID=MIO.SR_INSTANCE_ID(+)
AND MTP.SR_TP_ID=MIO.ORGANIZATION_ID(+)
AND MTP.SR_INSTANCE_ID=SUB.SR_INSTANCE_ID(+)
AND MTP.SR_TP_ID=SUB.ORGANIZATION_ID(+)
AND SUB.PLAN_ID(+)=-1 UNION SELECT MTP.SR_INSTANCE_ID
, NVL(MTP.OPERATING_UNIT
, -23453) OPERATING_UNIT
, MTP.OPERATING_UNIT_NAME
, MTP.SR_TP_ID ORGANIZATION_ID
, MTP.ORGANIZATION_CODE
, MIO.ORGANIZATION_TYPE
, MTP.CURRENCY_CODE
, MTP.PARTNER_NAME ORGANIZATION_NAME
, DECODE(MTP.MODELED_CUSTOMER_ID
, NULL
, 2
, 1) MODELED_CUSTOMER_FLAG
, DECODE(MTP.MODELED_SUPPLIER_ID
, NULL
, 2
, 1) MODELED_SUPPLIER_FLAG
, '-23453' SUB_INVENTORY_CODE
, TO_CHAR(NULL) SUB_INVENTORY_DESC
FROM MSC_TRADING_PARTNERS MTP
, MSC_INSTANCE_ORGS MIO
WHERE MTP.PARTNER_TYPE=3
AND MTP.SR_INSTANCE_ID=MIO.SR_INSTANCE_ID(+)
AND MTP.SR_TP_ID=MIO.ORGANIZATION_ID(+) UNION ALL SELECT MAI.SR_INSTANCE_ID
, -23453 OPERATING_UNIT
, TO_CHAR(NULL) OPERATING_UNIT_NAME
, -23453 ORGANIZATION_ID
, TO_CHAR(NULL) ORGANIZATION_CODE
, TO_NUMBER(NULL) ORGANIZATION_TYPE
, 'XXX' CURRENCY_CODE
, TO_CHAR(NULL) ORGANIZATION_NAME
, TO_NUMBER(NULL) MODELED_CUSTOMER_FLAG
, TO_NUMBER(NULL) MODELED_SUPPLIER_FLAG
, '-23453' SUB_INVENTORY_CODE
, TO_CHAR(NULL) SUB_INVENTORY_DESC
FROM (SELECT DISTINCT INSTANCE_ID SR_INSTANCE_ID
FROM MSC_APPS_INSTANCES UNION ALL SELECT TO_NUMBER(-23453)
FROM DUAL) MAI