1 Reply Latest reply on Oct 6, 2014 4:39 PM by Gary Graham-Oracle

    OTL Timecard query for both CWK/Employee

    2755399

      Hello,

       

      I'm trying to build a query for displaying time-card details and I was able to make it work for CWK but for Employees it's not returning any rows as I have joins. But if I try to change it to outer join, then it's returning duplicate rows for Employee because the same time_building_block_id matches two rows one with layout and projects as it ignores outer join for 'Purchasing'. My goal is to be able to retrieve the PONum if available in case of CWK but NULL for Employee and no duplicates. Can anyone suggest how to achieve please?

       

       

      SELECT *

        FROM (  SELECT htbp1.measure hrs,

                       hts.resource_id,

                       TO_CHAR (htbp.start_time, 'DY') par_time,

                       TO_CHAR (htbp.start_time, 'DY, MON DD') par_time_s,

                       :1 wkend_date,

                       htbp.start_time,

                       ppf.full_name,

                       hta.attribute1 project,

                       hta.attribute2 task,

                       hta.attribute3 TYPE,

                       hta1.attribute1 po_num,

                       ppa.name,

                       ppa.segment1 project_num,

                       pt.task_name,

                       pt.task_number,

                       htbp1.comment_text comments,

                       SUM (htbp1.measure) totals

                  FROM hxc_time_building_blocks htbp,

                       hxc_time_building_blocks htbp1,

                       hxc_time_attribute_usages htau,

                       hxc_time_attributes hta,

                       hxc_time_attribute_usages htau1,

                       hxc_time_attributes hta1,

                       pa_projects_all ppa,

                       pa_tasks pt,

                       hxc_timecard_summary hts,

                       per_all_people_f ppf

                 WHERE     hts.RESOURCE_ID = :2

                       AND TO_CHAR (hts.STOP_TIME, 'DD-MON-RRRR') = :1

                       AND htbp.parent_building_block_id = hts.timecard_id

                       AND htbp.parent_building_block_ovn = hts.timecard_ovn

                       AND htbp1.parent_building_block_id =

                              htbp.time_building_block_id

                       AND htbp1.parent_building_block_ovn =

                              htbp.parent_building_block_ovn

                       AND htau.time_building_block_id = htbp1.time_building_block_id

                       AND htau.time_building_block_ovn =

                              htbp1.parent_building_block_ovn

                       AND hta.attribute_category = 'PROJECTS'

                       AND hta.time_attribute_id = htau.time_attribute_id

                       AND htau1.time_building_block_id  = htbp1.time_building_block_id

                       AND htau1.time_building_block_ovn  =

                              htbp1.parent_building_block_ovn

                       AND hta1.attribute_category   = 'PURCHASING'

                       AND hta1.time_attribute_id  = htau1.time_attribute_id 

                       AND hta1.bld_blk_info_type_id  = 8

                       AND ppa.project_id = TO_NUMBER (hta.attribute1)

                       AND pt.project_id = ppa.project_id

                       AND pt.task_id = TO_NUMBER (hta.attribute2)

                       AND ppf.person_id = hts.resource_id

              GROUP BY htbp1.measure,

                       hts.resource_id,

                       htbp.start_time,

                       hta.attribute1,

                       ppf.full_name,

                       htbp1.comment_text,

                       hta.attribute2,

                       hta.attribute3,

                       ppa.name,

                       ppa.segment1,

                       pt.task_name,

                       pt.task_number,

                       hta1.attribute1

              ORDER BY ppa.segment1,pt.task_number ASC) PIVOT (SUM (hrs)

                                            FOR par_time

                                            IN  ('SUN' AS "SUN",

                                                'MON' AS "MON",

                                                'TUE' AS "TUE",

                                                'WED' AS "WED",

                                                'THU' AS "THU",

                                                'FRI' AS "FRI",

                                                'SAT' AS "SAT"))

       

       

      Thanks.