APPS.GMP_RSRC_AVL_PKG SQL Statements

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

Line: 51

update_flag       VARCHAR2(1) := 'N';

Line: 106

SELECT cdate,shift_no,
decode(sign(ftime - p_from_date),-1,p_from_date,ftime) from_time,
decode(sign(ttime - v_to_time),-1,ttime,v_to_time) to_time
FROM
(
       SELECT bsd.shift_date cdate,
       bsd.shift_num shift_no,
       (bsd.shift_date + (bst.from_time/86400)) ftime,
       (bsd.shift_date + (bst.to_time/86400)) ttime
       FROM   bom_calendars bc,
              bom_shift_dates bsd,
              bom_shift_times bst
       WHERE bsd.calendar_code = bc.calendar_code
         AND bst.calendar_code = bsd.calendar_code
         AND bsd.shift_num = bst.shift_num
         AND bsd.seq_num is not null
         AND bc.calendar_code = p_cal_code
)
WHERE 1= 1
  AND (
      (ftime between p_from_date and v_to_time)
      OR
      (ttime between p_from_date and v_to_time)
      )
ORDER BY cdate,from_time ;

Line: 133

SELECT resource_units,
decode(sign(from_date - p_from_date),-1,p_from_date,from_date) from_time,
decode(sign(to_date - v_to_time),-1,to_date,v_to_time) to_time
FROM gmp_rsrc_unavail_dtl_v
WHERE resource_id = p_resource_id
AND
(
(from_date between p_from_date and v_to_time)
OR
(to_date between p_from_date and v_to_time)
)
ORDER BY from_time;

Line: 147

SELECT assigned_qty FROM cr_rsrc_dtl
WHERE resource_id = p_resource_id;

Line: 183

    SELECT to_char(p_to_date,'HH24:MI:SS')
    INTO temp_date
    FROM DUAL ;

Line: 215

           cal_rec.delete;

Line: 218

      /* Delete the Unavailable PL/SQL table before start */
        if unavail_rec.COUNT > 0
        then
           unavail_rec.delete;

Line: 224

      /* Delete the Out Cal Rec PL/SQL table before start */
        if p_rec.COUNT > 0
        then
           p_rec.delete;

Line: 281

            end of this package will do the necessary inserts */
         NO_NO_EXCP := 'Y' ;

Line: 290

          Insert the resultant into Out Tbl Which is a PL/SQL table
       end loop;

Line: 311

             skip all the calculations and gets directly inserted into
             the ST table - The assumption is that the resource ids
             will be coming in the same order in the Unvailable Cursor
             and the resources cursor - This is taken care in both the
             Cursors */

             /* {  ==A==
              Now check if the Cal date from time is less than Unavailable
              from time - Here the comparisons are made with both date
              and Time
             */
            IF (cal_rec(c).cal_from_date <= unavail_rec(u).u_from_date)
            THEN

              /* If the calendar from time is Yes, then check if the
                 Calendar end time is greater than unavailable from time
              */
              IF /* { Special 1 */
                 (cal_rec(c).cal_to_date > unavail_rec(u).u_from_date)
              THEN
                  /* {
                   Check if the Calendar to time is Less than Unavailable
                   to date
                  */
                IF /* == A 2 and A3 */
                   (cal_rec(c).cal_to_date <= unavail_rec(u).u_to_date)
                THEN
                    /* shorten the shift (remaining shift is consumed)
                       and insert the record */

                    stmt_no := 30;

Line: 383

                     /* Break the shift and insert firt record */
                     /* Assign new values to start and end times of
                        the cal_rec shift */
                     /* preserve the counter u into u1 */
                     /* As you continue to loop check resource_id */

                     /* Break the shift and insert first record */

                    stmt_no := 40;

Line: 428

                     IF update_flag = 'N' THEN
                        update_flag := 'Y' ;

Line: 455

                 /* Call the Insert Procedure */
                        u1 := u ;

Line: 499

                      IF update_flag = 'N' THEN
         	        update_flag := 'Y' ;

Line: 564

          /* Insert into PL/SQL TABLE while looping through the cal_rec
             from current position onwards */
          /* c := c + 1 ; */

Line: 585

	  IF update_flag = 'Y'
          THEN
	      cal_rec(c).cal_from_date := temp_from_time ;

Line: 588

	      update_flag := 'N';

Line: 596

       IF update_flag = 'Y'
       THEN
          cal_rec(c).cal_from_date := temp_from_time ;

Line: 599

          update_flag := 'N';

Line: 655

        log_message('Error in Test rsrc Insert: '||stmt_no);

Line: 696

SELECT organization_id, calendar_code
  FROM cr_rsrc_dtl
 WHERE resource_id = p_resource_id
   AND delete_mark = 0
   AND inactive_ind = 0 ;

Line: 703

 SELECT calendar_code
   FROM mtl_parameters
 WHERE  organization_id = l_organization_id ;