1 Reply Latest reply: Dec 31, 2012 8:19 AM by clive_t RSS

    OTA_SUPPLIABLE_RESOURCES joins

    Adit102
      Hi,

      I am trying to join below tables.

      XXLOG_EXTRACT_BASIC_PERSON EXT, -- CUSTOM TABLE
      PER_ALL_PEOPLE_F PAPF,
      Per_all_assignments_f PAAF,
      HR_ALL_ORGANIZATION_UNITS HAOU,
      OTA_DELEGATE_BOOKINGS ODB,
      OTA_EVENTS OE,
      OTA_ACTIVITY_VERSIONS OAV,
      OTA_ACTIVITY_DEFINITIONS OAD,
      OTA_CATEGORY_USAGES OCU,
      OTA_SUPPLIABLE_RESOURCES OSR,
      OTA_RESOURCE_DEFINITIONS ORD,
      PO_VENDORS PV


      Below is the query I have used -

      SELECT * FROM
      XXLOG_EXTRACT_BASIC_PERSON EXT,
      PER_ALL_PEOPLE_F PAPF,
      PER_ALL_ASSIGNMENTS_F PAAF,
      HR_ALL_ORGANIZATION_UNITS HAOU,
      OTA_DELEGATE_BOOKINGS ODB,
      OTA_EVENTS OE,
      OTA_ACTIVITY_VERSIONS OAV,
      OTA_ACTIVITY_DEFINITIONS OAD,
      OTA_CATEGORY_USAGES OCU,
      --OTA_SUPPLIABLE_RESOURCES OSR,
      --OTA_RESOURCE_DEFINITIONS ORD,
      PO_VENDORS PV
      WHERE EXT.PERSON_ID = PAPF.PERSON_ID
      AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
      AND PAPF.PERSON_ID = PAAF.PERSON_ID
      AND HAOU.ORGANIZATION_ID = PAAF.ORGANIZATION_ID
      AND PAPF.PERSON_ID = ODB.DELEGATE_PERSON_ID
      AND OE.EVENT_ID = ODB.EVENT_ID
      AND OE.ACTIVITY_VERSION_ID=OAV.ACTIVITY_VERSION_ID
      AND OAD.ACTIVITY_ID = OAV.ACTIVITY_ID
      AND OAD.CATEGORY_USAGE_ID = OCU.CATEGORY_USAGE_ID
      --AND OSR.RESOURCE_DEFINITION_ID = ORD.RESOURCE_DEFINITION_ID
      AND OE.VENDOR_ID= PV.VENDOR_ID


      But in above query I am unable to join OTA_SUPPLIABLE_RESOURCES OSR and OTA_RESOURCE_DEFINITIONS ORD with other tables.
      Where can we find SUPPLIED_RESOURCE_ID from OTA_SUPPLIABLE_RESOURCES in above tables?

      Kindly help.

      Thanks,
      Aditya
        • 1. Re: OTA_SUPPLIABLE_RESOURCES joins
          clive_t
          Hi

          It might help to know what exactly your query is looking to retrieve - I don't think retrieving every column of every table in your table list is going to be particularly useful.

          For what it's worth, I think the table OTA_RESOURCE_BOOKINGS might prove useful somehow. Certainly it has the column SUPPLIED_RESOURCE_ID in it.

          Clive

          Edited to add: and another thing, you might want to apply the TRUNC function to that sysdate reference in your WHERE clause.

          Edited by: clive_t on 31-Dec-2012 14:18