Tuesday, April 4, 2017

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect