Oracle Time and Projects

SELECT   hts.approval_status ,hts.timecard_id, hts.resource_id, hts.start_time,
                  hts.stop_time, hts.submission_date,
                  htb1.start_time each_day, hta.attribute1 project_id,
                  hta.attribute2 task_id,papa.name, papa.segment1, pt.task_number, htb2.measure, pt.task_name
             FROM hxc_time_building_blocks htb,
                  hxc_time_building_blocks htb1,
                  hxc_time_building_blocks htb2,
                  hxc_time_attribute_usages htau,
                  hxc_time_attributes hta,
                  pa_projects_all papa,
                  hxc_timecard_summary hts,
                  pa_tasks pt
            WHERE htb1.parent_building_block_id = htb.time_building_block_id
              AND htb1.parent_building_block_ovn = htb.object_version_number
              AND htb.date_to = hr_general.end_of_time
              AND htb.SCOPE = 'TIMECARD'
              AND htb1.SCOPE = 'DAY'
              --AND htb1.date_to = hr_general.end_of_time
              AND htb2.parent_building_block_id = htb1.time_building_block_id
              AND htb2.parent_building_block_ovn = htb1.object_version_number
              AND htb2.SCOPE = 'DETAIL'
              --AND htb2.date_to = hr_general.end_of_time
              AND htau.time_building_block_id = htb2.time_building_block_id
              AND htau.time_building_block_ovn = htb2.object_version_number
              AND htau.time_attribute_id = hta.time_attribute_id
              AND papa.project_id = hta.attribute1
              AND hts.start_time = htb.start_time
              AND  hts.start_time > sysdate - 380
              AND hts.resource_id = htb.resource_id
             AND htb.resource_id = :p_resource_id -- person_id
              --AND hts.timecard_id = :p_timecard_id
              AND hta.attribute_category = 'PROJECTS'
              AND hts.approval_status = 'APPROVED'
              AND hta.attribute2 = pt.task_id
              AND hta.attribute1 = pt.project_id
         ORDER BY htb1.start_time;

regexp_like

SELECT :expr, 'narayan is my name raykar. ('
FROM   DUAL
WHERE regexp_like (:expr,'(^|\s)raykar($|\s|\W)','i' )

(^|\s) - starts with or has space
($|\s|\W) - ends with or has space or has a nonword character

Session SQL and Lock

                    SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id, a.concurrent_program_id, d.event
                    , d.*
                    FROM apps.fnd_concurrent_requests a,
                    apps.fnd_concurrent_processes b,
                    gv$process c,
                    gv$session d
                    WHERE a.controlling_manager = b.concurrent_process_id
                    AND c.pid = b.oracle_process_id
                    --AND concurrent_program_id = 32766
                    AND b.session_id=d.audsid
                    AND c.inst_id = d.inst_id
                    AND a.request_id = 197521679
                   
                             SELECT s2.sid
                             ,      s2.lockwait
                             ,      s1.sql_text
                             ,      s1.piece
                             FROM   gv$SQLtext s1
                             ,      gv$session s2
                             WHERE  s1.address =  s2.sql_address
                             AND    s1.inst_id = s2.inst_id
                             --AND    s2.sid = 1380
                             --AND    s2.inst_id = 2
                             AND module IN ('e:XXX:cp:inv/INCOIN')
                             ORDER BY s2.sid, s1.piece


                             SELECT *
                             FROM   gv$session
                             WHERE  1=1
                             --AND    module like '%cp:%INV%'
                             AND    sid in (1380, 731)
                                                       
                            SELECT event, state, p1, p2, p3
                            FROM gv$session_wait
                            WHERE sid = 1380                            
                                       
                    SELECT description, USER_CONCURRENT_PROGRAM_NAME,CONCURRENT_PROGRAM_NAME
                    FROM   fnd_concurrent_programs_vl
                    WHERE  1=1
                    --AND    concurrent_program_id = 32766
                    AND    USER_CONCURRENT_PROGRAM_NAME like '%Receiving Transaction%'
                   


                        SELECT
                           l1.sid || ' is blocking ' || l2.sid blocking_sessions
                        FROM
                           gv$lock l1, gv$lock l2
                        WHERE
                           l1.block = 1 AND
                           l2.request > 0 AND
                           l1.id1 = l2.id1 AND
                           l1.id2 = l2.id2 AND
                           l1.inst_id = l2.inst_id                                    
                            

Handle LISTAGG length issue for CSV

DECLARE


                              CURSOR rsrv_cur
                              IS
                              SELECT distinct stg.so_line_id
                              FROM   apps.wwt_xxwms_rsrv_onhand_cnv_stg stg
                              WHERE  1 = 1
                              AND    stg.source_code = 'VALIDATION_DATA'
                             --AND    stg.s_status_code = 'V'
                              AND    stg.l_source_organization_id = :g_source_organization_id
                              AND    stg.l_dest_organization_id = :g_destination_organization_id
                              AND    stg.lpn IS NULL
                              AND    stg.serial_number IS NOT NULL
                              AND    stg.serial_control_flag = 'Y'
                              GROUP BY stg.so_line_id,
                                       'SERIAL';

   l_rsrv_value  VARCHAR2(32767) ;

BEGIN

 for rsrv_rec IN rsrv_cur LOOP

    dbms_output.put_line ('SO_LINE_ID => '||rsrv_rec.so_line_id);

   BEGIN

    SELECT
    RTRIM (REGEXP_REPLACE ( (LISTAGG (stg.serial_number, ',') WITHIN GROUP (ORDER BY stg.serial_number) ),
                                            '([^,]*)(,\1)+($|,)',
                                            '\1\3'),
                                     ',') rsrv_value
    INTO l_rsrv_value
    FROM apps.wwt_xxwms_rsrv_onhand_cnv_stg stg
    WHERE  stg.so_line_id = rsrv_rec.so_line_id ;

   EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line ('SO_LINE_ID => '||rsrv_rec.so_line_id||' '||SQLERRM);


      SELECT regexp_replace( XMLAGG(XMLELEMENT(E,stg.serial_number||',').EXTRACT('//text()') ).getclobval(),'^,|,$', '') Result
       INTO l_rsrv_value
      FROM apps.wwt_xxwms_rsrv_onhand_cnv_stg stg
      WHERE  stg.so_line_id =  rsrv_rec.so_line_id ;

      dbms_output.put_line ('l_rsrv_value => '||l_rsrv_value);


   END ;

 END LOOP rsrv_rec;

EXCEPTION
   WHEN OTHERS THEN
    dbms_output.put_line ('Error '||SQLERRM);


END;

Parsing parent child XML structure using PL/SQL

Source


Doing some investigation about processing a XML stucture where we have a parent child type relationship where we want to get all the details for each child record and loop through them.
As part of looping through the child records we want the parent details to be available without the need to go back and read the XML again.

The following example displays each RaceLap tag and includes parent information. Including the case where there may be no child RaceLap entries.
The trick for me to understand was using the laptimes column extracted from the v_xml_example as an XMLTYPE as input into the second part of the FROM clause (laps) using the PASSING race.laptimes.


If there are any questions add a comment.


DECLARE

 v_xml_example                  SYS.xmltype := xmltype(

'<Races>
 <RaceResult>
  <Id>743845</Id>
  <PlateNo>420</PlateNo>
  <Completed_ind>N</Completed_ind>
  <Comments>DNF. No laps recorded</Comments>
 </RaceResult>
 <RaceResult>
  <Id>123145</Id>
  <PlateNo>233</PlateNo>
  <Completed_ind>Y</Completed_ind>
  <Comments>Finished after 3 laps</Comments>
  <RaceLap>
   <Lap>1</Lap>
   <Time>34.34</Time>
  </RaceLap>
  <RaceLap>
   <Lap>2</Lap>
   <Time>35.66</Time>
  </RaceLap>
  <RaceLap>
   <Lap>3</Lap>
   <Time>34.00</Time>
  </RaceLap>
 </RaceResult>
</Races>');


CURSOR c_race_laps IS
 SELECT race.id,
  race.plate_num,
  race.completed_ind,
  race.comments,
  laps.lap,
  laps.lap_time
 FROM    XMLTABLE('/Races/RaceResult'  -- XQuery string to get RaceResult tag
                PASSING v_xml_example
                COLUMNS
   id  VARCHAR2(100) PATH 'Id',
   plate_num NUMBER(10) PATH 'PlateNo',
   completed_ind VARCHAR2(1) PATH 'Completed_ind',
   comments VARCHAR2(100) PATH 'Comments',
   laptimes XMLTYPE  PATH 'RaceLap') race
  LEFT OUTER JOIN -- want parent with child nodes
  XMLTABLE('/RaceLap'  -- XQuery string to get RaceLap tag
                PASSING race.laptimes   -- the laptimes XMLTYPE output from the first xmltable containing the laptimes
                COLUMNS
                        lap  NUMBER  PATH 'Lap',
   lap_time NUMBER  PATH 'Time') laps
  ON (1 = 1); -- left outer join always join

BEGIN


 FOR v_race_laps_rec IN c_race_laps LOOP


  dbms_output.put_line('atr id:' || v_race_laps_rec.id ||
    ' plate_num:' || v_race_laps_rec.plate_num ||
    ' completed_ind:' || v_race_laps_rec.completed_ind ||
    ' Comments:' || v_race_laps_rec.comments ||
    ' Lap Number:' || v_race_laps_rec.lap ||
    ' Lap Time:' || v_race_laps_rec.lap_time);

 END LOOP;


END;

Oracle Converting To Text in R12

Converting Oracle EBS R12 RDF to REX 

Command: 

           rwconverter.sh stype=rdffile source=ARXINVAD.rdf dtype=rexfile dest=ARXINVAD.rex batch=yes

Benefits: 

            You don’t need to install Reports designer to read RDF code. You can grep the rex file to check code or you can also open the rex file in any text editor to read the code.

 

Converting Oracle EBS R12 FMB  to TXT

Command: 

  frmcmp_batch module=APXINWKB.fmb userid=apps/apps  Script=YES Forms_Doc=YES module_type=FORM

Benefits: 

          You don’t need to install Forms designer to read FMB code. You can grep the txt file to check code or you can also open the txt file in any text editor to read the code. Another advantage is you don’t need to copy all the plls to your desktop to open the fmb.

Converting Oracle EBS R12 PLL  to PLD

Command: 

 frmcmp_batch module=ARXRWAPP.pll userid=apps/apps  Script=YES module_type=LIBRARY Output_File=ARXRWAPP.pld

Benefits: 

          You don’t need to install Forms designer to read PLL code.You can grep the pld file to check code or you can also open the txt file in any text editor to read the code. Another advantage is you don’t need to copy all the plls to your desktop to open the pll.

Academic v/s Practical Engineering

In a statement to the U.S. Congress in 1953, early on in the development of nuclear reactors, Rickover addressed the confusion amongst the nation's decision-makers in his typical head-on fashion, and notably pointed out the chasm between the world of academia and the world of practical engineering:

“An academic reactor or reactor plant almost always has the following basic characteristics: (1) It is simple. (2) It is small. (3) It is cheap. (4) It is light. (5) It can be built very quickly. (6) It is very flexible in purpose (“omnibus reactor”). (7) Very little development is required. It will use mostly “off-the-shelf” components. (8) The reactor is in the study phase. It is not being built now.

“On the other hand, a practical reactor plant can be distinguished by the following characteristics: (1) It is being built now. (2) It is behind schedule. (3) It is requiring an immense amount of development on apparently trivial items. Corrosion, in particular, is a problem. (4) It is very expensive. (5) It takes a long time to build because of the engineering development problems. (6) It is large. (7) It is heavy. (8) It is complicated.

"The tools of the academic-reactor designer are a piece of paper and pencil with an eraser. It a mistake is made, it can always be erased and changed. If the practical-reactor designer errs, he wears the mistake around his neck; it cannot be erased. Everyone can see it.

“The academic-reactor designer is a dilettante. He has not had to assume any real responsibility in connection with his projects. He is free to luxuriate in elegant ideas, the practical shortcomings of which can be relegated to the category of “mere technical details.” The practical-reactor designer must live with these same technical details. Although recalcitrant and awkward, they must be solved and cannot be put off until tomorrow. Their solutions require manpower, time and money."