VIEW: APPS.ZX_FIRST_PARTY_ORGS_MOAC_V

Source

View Text - Preformatted

SELECT
    PTP.PARTY_TAX_PROFILE_ID,
    XLEP.NAME PARTY_NAME,
    PTP.PARTY_TYPE_CODE,
    PTP.USE_LE_AS_SUBSCRIBER_FLAG
  FROM ZX_PARTY_TAX_PROFILE PTP,
        HZ_PARTIES HZP,
        XLE_ENTITY_PROFILES XLEP
  WHERE PTP.PARTY_ID = HZP.PARTY_ID
    AND PTP.PARTY_TYPE_CODE IN ('FIRST_PARTY')
    AND HZP.PARTY_ID = XLEP.PARTY_ID
  UNION ALL
    SELECT PTP.PARTY_TAX_PROFILE_ID,
          HOU.NAME  PARTY_NAME,
          PTP.PARTY_TYPE_CODE,
          PTP.USE_LE_AS_SUBSCRIBER_FLAG
    FROM ZX_PARTY_TAX_PROFILE PTP,
          HR_OPERATING_UNITS HOU,
          MO_GLOB_ORG_ACCESS_TMP OA
    WHERE
        PTP.PARTY_TYPE_CODE = 'OU'
        AND PTP.PARTY_ID = HOU.ORGANIZATION_ID
        AND OA.ORGANIZATION_ID = PTP.PARTY_ID
  UNION ALL
    SELECT
         PTP.PARTY_TAX_PROFILE_ID,
         LU.MEANING PARTY_NAME,
         PTP.PARTY_TYPE_CODE,
         PTP.USE_LE_AS_SUBSCRIBER_FLAG
       FROM ZX_PARTY_TAX_PROFILE PTP,
            FND_LOOKUPS LU
       WHERE  PTP.PARTY_TYPE_CODE = 'GCO'
          AND PTP.PARTY_TYPE_CODE = LU.LOOKUP_CODE
          AND LU.LOOKUP_TYPE = 'ZX_PTP_PARTY_TYPE'

View Text - HTML Formatted

SELECT PTP.PARTY_TAX_PROFILE_ID
, XLEP.NAME PARTY_NAME
, PTP.PARTY_TYPE_CODE
, PTP.USE_LE_AS_SUBSCRIBER_FLAG
FROM ZX_PARTY_TAX_PROFILE PTP
, HZ_PARTIES HZP
, XLE_ENTITY_PROFILES XLEP
WHERE PTP.PARTY_ID = HZP.PARTY_ID
AND PTP.PARTY_TYPE_CODE IN ('FIRST_PARTY')
AND HZP.PARTY_ID = XLEP.PARTY_ID UNION ALL SELECT PTP.PARTY_TAX_PROFILE_ID
, HOU.NAME PARTY_NAME
, PTP.PARTY_TYPE_CODE
, PTP.USE_LE_AS_SUBSCRIBER_FLAG
FROM ZX_PARTY_TAX_PROFILE PTP
, HR_OPERATING_UNITS HOU
, MO_GLOB_ORG_ACCESS_TMP OA WHERE PTP.PARTY_TYPE_CODE = 'OU'
AND PTP.PARTY_ID = HOU.ORGANIZATION_ID
AND OA.ORGANIZATION_ID = PTP.PARTY_ID UNION ALL SELECT PTP.PARTY_TAX_PROFILE_ID
, LU.MEANING PARTY_NAME
, PTP.PARTY_TYPE_CODE
, PTP.USE_LE_AS_SUBSCRIBER_FLAG
FROM ZX_PARTY_TAX_PROFILE PTP
, FND_LOOKUPS LU
WHERE PTP.PARTY_TYPE_CODE = 'GCO'
AND PTP.PARTY_TYPE_CODE = LU.LOOKUP_CODE
AND LU.LOOKUP_TYPE = 'ZX_PTP_PARTY_TYPE'