This discussion is archived
1 Reply Latest reply: Dec 31, 2012 6:19 AM by clive_t RSS

OTA_SUPPLIABLE_RESOURCES joins

Adit102 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points