eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.GMP_CALENDAR_API SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 69


SELECT 1 FROM dual
WHERE EXISTS
(SELECT 1
FROM BOM_shift_dates sd, bom_shift_times sht
WHERE sd.calendar_code = sht.calendar_code
AND sd.shift_date = trunc(c_cal_date)
AND sd.shift_num = sht.shift_num
AND sd.calendar_code = c_calendar_id
AND sd.SEQ_NUM is NOT NULL
AND DECODE(sht.to_time,0,86400,sht.to_time) > sht.from_time);

Line: 81


SELECT COUNT(1)
FROM bom_Calendars
WHERE calendar_code =  p_calendar_code;

Line: 86


SELECT 	calendar_start_date,
 	calendar_end_date
FROM bom_calendars
WHERE calendar_code =  p_calendar_code;

Line: 249


SELECT shift_date,shift_num, from_time,
       (to_time-from_time) diff_shift,to_time, from_date ,
        TO_DATE end_date
FROM apps.temp_cal
-- WHERE shift_DATE >= TO_DATE('06-OCT-2011 00:00:00','DD-MON-YYYY HH24:MI:SS')
WHERE shift_DATE >= trunc(c_start_date)
-- AND TO_DATE >= TO_DATE('06-OCT-2011 23:00:00','DD-MON-YYYY HH24:MI:SS')
AND TO_DATE >= c_start_date
AND calendar_code = c_calendar_code -- '21HR'
ORDER BY shift_date, from_date, TO_DATE ;

Line: 262


SELECT shift_date,shift_num, from_time,
       (to_time-from_time) diff_shift,to_time, from_date ,
        TO_DATE end_date
FROM apps.temp_cal
WHERE shift_DATE <= trunc(c_end_date)
-- WHERE shift_DATE <= TO_DATE('06-OCT-2011 00:00:00','DD-MON-YYYY HH24:MI:SS')
-- AND FROM_DATE <= TO_DATE('06-OCT-2011 01:59:55','DD-MON-YYYY HH24:MI:SS')
AND  FROM_DATE <= c_end_date
AND calendar_code = '21HR'
ORDER BY shift_date desc, TO_DATE desc, from_date ;

Line: 277


SELECT shift_date, shift_num, from_time, to_time,
       DECODE((to_time-from_time),0,86400,(to_time-from_time)) diff_shift, from_date, TO_DATE end_date
  FROM gmp_calendar_detail_gtmp
 WHERE shift_DATE >= trunc(c_start_date)
   AND TO_DATE >= c_start_date
   AND calendar_code = c_calendar_code
 ORDER BY shift_date, from_date, TO_DATE ;

Line: 287


SELECT shift_date, shift_num, from_time, to_time,
       DECODE((to_time-from_time),0,86400,(to_time-from_time)) diff_shift, from_date, TO_DATE end_date
  FROM gmp_calendar_detail_gtmp
 WHERE shift_DATE <= trunc(c_end_date)
   AND FROM_DATE <= c_end_date
   AND calendar_code = c_calendar_code
 ORDER BY shift_date desc, TO_DATE desc, from_date ;

Line: 403


       SELECT ( p_start_date - TRUNC(p_start_date) ) * 86400
       INTO l_start_time FROM DUAL;

Line: 511


       SELECT ( p_end_date - TRUNC(p_end_date) ) * 86400
       INTO l_end_time FROM DUAL;

Line: 663


SELECT COUNT(1)
FROM bom_Calendars
WHERE calendar_code =  c_calendar_code;

Line: 668


SELECT calendar_start_date ,
       calendar_end_date
FROM bom_Calendars
WHERE calendar_code =  c_calendar_code;

Line: 797


SELECT sd.shift_date calendar_date,
      decode(SUM(decode(sd.seq_num,NULL,0,1)),0,0,1) l_work_day
FROM  bom_calendars  cal,
      bom_shift_dates sd,
      bom_shift_times st
WHERE cal.calendar_code = c_calendar_code
AND sd.calendar_code = cal.calendar_code
AND st.calendar_code = sd.calendar_code
AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
AND sd.shift_num = st.shift_num
GROUP BY sd.shift_date
ORDER BY sd.shift_date; /*B5182025 - sowsubra - added order by clause*/

Line: 935


SELECT sd.shift_date calendar_date,
	   SUM((DECODE(st.to_time,0,86400,st.to_time) - st.from_time)/3600) duration
FROM bom_calendars cal,
	 bom_shift_dates sd,
	 bom_shift_times st
WHERE cal.calendar_code =  c_calendar_code
AND sd.calendar_code = cal.calendar_code
AND st.calendar_code = sd.calendar_code
AND sd.shift_date BETWEEN trunc(c_start_date) AND trunc(c_end_date)
AND sd.shift_num = st.shift_num
AND sd.seq_num IS NOT NULL
GROUP BY sd.shift_date
HAVING SUM((DECODE(st.to_time,0,86400,st.to_time) - st.from_time)/3600) > 0 ;

Line: 1050


SELECT shift_num,from_time,DECODE(to_time,0,86400,to_time) to_time
FROM bom_shift_times
WHERE calendar_code = c_calendar_code
AND shift_num = c_shopday_no
ORDER BY from_time ;

Line: 1058


SELECT COUNT(*)
FROM bom_shift_times
WHERE calendar_code = c_calendar_code
AND shift_num = c_shopday_no;

Line: 1167


SELECT COUNT(1)
FROM bom_Calendars
WHERE calendar_code =  p_calendar_code;

Line: 1172


SELECT calendar_start_date ,
       calendar_end_date
FROM bom_Calendars
WHERE calendar_code =  p_calendar_code;

Line: 1261


SELECT COUNT(1)
FROM bom_Calendars
WHERE calendar_code =  c_calendar_code;

Line: 1384


  SELECT 1
  FROM  bom_calendars cal,
        bom_shift_dates sd,
        bom_shift_times st
  WHERE cal.calendar_code = c_calendar_code
  AND sd.calendar_code = cal.calendar_code
  AND st.calendar_code = sd.calendar_code
  AND sd.shift_num = st.shift_num
  -- B4610901, Rajesh Patangya 15-Sep-2005
  AND (sd.shift_date + (st.from_time/86400)) <= c_cal_date
  AND DECODE(SIGN(st.from_time - DECODE(st.to_time,0,86400,st.to_time)),
	1,(sd.shift_date+1), sd.shift_date
	     ) + (decode(st.to_time,0,86400,st.to_time)/86400) >=  c_cal_date
 /* AND DECODE(
        SIGN(st.from_time - st.to_time),
	1,(sd.shift_date+1), sd.shift_date
	     ) + (st.to_time/86400) >=  c_cal_date */
  AND sd.seq_num IS NOT NULL;

Line: 1557


SELECT 1
FROM sys.dual
WHERE EXISTS (
select 'x'
              FROM bom_calendars  bd,
                   bom_shift_dates sd,
                    bom_shift_times st
              WHERE bd.calendar_code = p_calendar_id
                 AND sd.calendar_code = bd.calendar_code
                AND sd.calendar_code= st.calendar_code
                AND sd.shift_num = st.shift_num
                AND (sd.shift_date + (st.from_time/86400))   <= p_date
				AND sd.seq_num IS NOT NULL
                AND ((decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time) - 86400),1,
                     (sd.shift_date+1),sd.shift_date) ) +
                     (decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),1,
                     (st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),
                     (st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)))/86400 ) ) >= p_date);

Line: 1578


SELECT min (sd.shift_date + (st.from_time/86400))
FROM bom_calendars  bd,
     bom_shift_dates sd,
     bom_shift_times st
WHERE bd.calendar_code = p_calendar_id
  AND sd.calendar_code = bd.calendar_code
  AND sd.calendar_code= st.calendar_code
  AND sd.shift_num = st.shift_num
  AND sd.seq_num IS NOT NULL
  AND (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  > 0
  AND (sd.shift_date + (st.from_time/86400))   > p_cal_date ;

Line: 1592


 SELECT max (
((decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),1,
    (sd.shift_date+1),sd.shift_date) ) +
    (decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),1,(st.from_time
    + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)))/86400 )  )
)
FROM bom_calendars  bd,
     bom_shift_dates sd,
     bom_shift_times st
WHERE bd.calendar_code = p_calendar_id
  AND sd.calendar_code = bd.calendar_code
  AND sd.calendar_code= st.calendar_code
  AND sd.shift_num = st.shift_num
  AND sd.seq_num IS NOT NULL

  AND (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  > 0
  AND ((decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),1,
    (sd.shift_date+1),sd.shift_date) ) +
    (decode(sign(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),1,(st.from_time
    + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)  - 86400),(st.from_time + (Decode(St.To_Time,0,86400,St.To_Time)- st.from_time)))/86400 ))
< p_date;

Line: 1800


    /* Insert for Net Resource starts here, The following select statement gets
      the period that are availble for a given calendar, From time and To Time
      are taken in seconds here.   SEELCT ONLY 800 days row for batches
    */

       sql_cal := ' SELECT sd.shift_date calendar_date, '
               || '    sd.shift_num shift_no, '
	             || '        st.from_time from_time, '
	             || '        decode(st.to_time,0,86400,st.to_time) to_time '
               || ' FROM   bom_calendars cal, '
	             || '        bom_shift_dates sd, '
               || '        bom_shift_times st '
               || ' WHERE  cal.calendar_code = :curr_cal_code '
               || ' AND sd.calendar_code = cal.calendar_code '
               || ' AND st.calendar_code = sd.calendar_code '
               || ' AND sd.shift_num = st.shift_num '
               || ' AND sd.seq_num is not null '
               || ' AND sd.shift_date >= (sysdate - 70) '
               || ' AND sd.shift_date <= (sysdate + 400) '
               || ' ORDER BY  calendar_date,from_time,to_time  ';

Line: 1823


          new_rec.delete;

Line: 1970


      ins_stmt := 'INSERT INTO gmp_calendar_detail_gtmp'
                          ||' ( '
                          ||'   calendar_code, '
                          ||'   shift_num, '
                          ||'   shift_date, '
                          ||'   from_time, '
                          ||'   to_time, '
                          ||'   from_date, '
                          ||'   to_date '
                          ||' ) '
                          ||' VALUES '
                          ||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';

Line: 1983


    /* ins_stmt1 := 'INSERT INTO temp_cal'
                          ||' ( '
                          ||'   calendar_code, '
                          ||'   shift_num, '
                          ||'   shift_date, '
                          ||'   from_time, '
                          ||'   to_time, '
                          ||'   from_date, '
                          ||'   to_date '
                          ||' ) '
                          ||' VALUES '
                          ||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';

Line: 1997


      delete from gmp_calendar_detail_gtmp  ;