Oracle Time and Labor (OTL) Table Joins

HXC_TIMECARD_SUMMARYà(timecard_id)

àHXC_TC_AP_LINKSà(Application_period_Id)

àHXC_TIMECARD_SUMMARYà(Approval_Item_Key)

àWF_ITEM_ATTRIBUTE_VALUESà(item_key.)

QUERRY-

SELECT segment1 project_code, NAME

  FROM apps.pa_projects_all

 WHERE project_id IN (

    SELECT DISTINCT attribute1

     FROM apps.hxc_time_attributes

    WHERE time_attribute_id IN (

      (SELECT time_attribute_id

        FROM apps.hxc_time_attribute_usages a,

         (SELECT time_building_block_id,

                 time_building_block_ovn

            FROM apps.hxc_ap_detail_links

           WHERE application_period_id IN (

             SELECT application_period_id

               FROM apps.hxc_app_period_summary

              WHERE application_period_id IN (

                SELECT application_period_id

                  FROM apps.hxc_tc_ap_links

                 WHERE timecard_id IN (

                    SELECT timecard_id

                      FROM apps.hxc_timecard_summary

                     WHERE resource_id =19596

                       AND TRUNC(stop_time) = '15-OCT-06')))) b

       WHERE a.time_building_block_id = b.time_building_block_id

         AND a.time_building_block_ovn = b.time_building_block_ovn)))

   AND project_id <> 42

The Above Chart Shows the relation between Various important tables in Oracle Time and Labor

select * From apps.HXC_TIME_BUILDING_BLOCKS

(SCOPE=’DETAIL’, TIME_BUILDING_BLOCK_ID:OBJECT_VERSION_NUMBER)

TIME_BUILDING_BLOCK_ID:OBJECT_VERSION_NUMBER=

pa.expenditure_items_al. ORIG_TRANSACTION_REFERENCE