APPS.PO_RESCHEDULE_PKG SQL Statements

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

Line: 53

    SELECT X_need_by_date_old,
	   X_need_by_date,
	   X_po_line_id
	   /*  Bug 5610714 ,
	   X_shipment_num,
	   p_estimated_pickup_date,
	   p_ship_method */
    BULK COLLECT INTO
           l_need_by_dates_old,
           l_need_by_dates,
           l_po_line_ids
	   /*  Bug 5610714,
           l_shipment_nums,
	   l_estimated_pickup_dates,
	   l_ship_methods */
     FROM  DUAL;

Line: 163

 select instr(x_supply_number, '(')
  into   x_pare_left
  from   dual;

Line: 179

    select instr(x_supply_number, ')')
    into   x_pare_right
    from   dual;

Line: 192

    select to_number(substr(x_supply_number, x_pare_left+1,
           x_pare_right - x_pare_left-1))
    into   x_release_num
    from   dual;

Line: 205

      select po_release_id,release_type
      into   l_po_release_id,l_release_type
      from   po_releases_all
      where  po_header_id = x_po_header_id
      and  release_num  = x_release_num;

Line: 240

      SELECT org_id
      INTO l_document_org_id
      FROM po_releases_all
      WHERE po_release_id = l_po_release_id;/*Bug 5255550*/

Line: 245

      SELECT org_id
      INTO l_document_org_id
      FROM po_headers_all
      WHERE po_header_id = x_po_header_id;

Line: 269

    Hence for performance reasons, we will use scratch pad, to insert data
   and then use bulk collect to get all the data in plsql table*/


/* If l_po_release_id is present, it means that the document is a release */
l_session_gt_key := PO_CORE_S.get_session_gt_nextval();

Line: 302

	    insert into po_session_gt (key,
				       num1,
				       num2,
				       date1,
				       date2,
				       char1, -- we're using this for date passed in.
				       char2  -- we're using this to mark a valid record
				       )
	    select l_session_gt_key,
		   pll.line_location_id,
		   pll.shipment_num,
		   -- Have picked this from the existing code. From the planning perspective
		   -- promised date becomes important and should be preferred over nbd
		   Nvl(pll.promised_date,pll.need_by_date),
		   X_need_by_dates(i),
		   To_Char(X_need_by_dates_old(i),'DD-MON-YYYY:hh:mi:ss'),
		   'Y'
	      from po_line_locations_all pll
	     where pll.po_line_id = X_po_line_ids(i)
	       and pll.po_header_id = X_po_header_id
	       and (l_po_release_id is NULL OR
				pll.po_release_id = l_po_release_id);

Line: 341

	    insert into po_session_gt (key,
				       num1,
				       num2,
				       date1,
				       date2,
				       char1, -- we're using this for date passed in.
				       char2  -- we're using this to mark a valid record
				       )
	    select l_session_gt_key,
		   pll.line_location_id,
		   X_shipment_nums(i),
		   -- Have picked this from the existing code. From the planning perspective
		   -- promised date becomes important and should be preferred over nbd
		   Nvl(pll.promised_date,pll.need_by_date),
		   X_need_by_dates(i),
		   To_Char(X_need_by_dates_old(i),'DD-MON-YYYY:hh:mi:ss'),
		   'Y'
	      from po_line_locations_all pll
	     where pll.po_line_id = X_po_line_ids(i)
	       and pll.shipment_num = Nvl(X_shipment_nums(i), pll.shipment_num)
	       and pll.po_header_id = X_po_header_id
	       and (l_po_release_id is NULL OR
				pll.po_release_id = l_po_release_id);

Line: 374

UPDATE po_session_gt scratch
SET char2 = 'N'
WHERE  scratch.char1 <>
                To_char(scratch.date1,'DD-MON-YYYY:hh:mi:ss')
  AND scratch.KEY = l_session_gt_key;

Line: 381

SELECT 'Supply Number '||x_supply_number||' Shipment Number '
       ||to_char(scratch.num2)||': Need by date does not match the date passed in'
BULK collect INTO l_nbd_validate_tbl
FROM po_session_gt scratch
WHERE scratch.KEY = l_session_gt_key
  AND Nvl(scratch.char2,'Y') = 'N';

Line: 399

UPDATE po_session_gt scratch
SET char2 = 'N'
WHERE  scratch.date2 IS NULL
  AND scratch.KEY = l_session_gt_key;

Line: 404

SELECT 'Supply Number '||x_supply_number||' Shipment Number '
       ||scratch.num2||': New Need by date is NULL'
BULK collect INTO l_nbd_validate_tbl
FROM po_session_gt scratch
WHERE scratch.KEY = l_session_gt_key
  AND scratch.date2 IS NULL;

Line: 423

select num1, date2
bulk collect into l_line_location_id_tbl, l_need_by_date_tbl
from po_session_gt SCRATCH
WHERE KEY = l_session_gt_key
AND Nvl(scratch.char2,'Y') = 'Y' ;

Line: 464

    select date2
    bulk collect into l_promised_by_date_tbl
    from po_session_gt SCRATCH
    WHERE KEY = l_session_gt_key
    AND Nvl(scratch.char2,'Y') = 'Y' ;

Line: 493

/*Step 7: Call update_documents; */

Line: 495

PO_DOCUMENT_UPDATE_PVT.update_document(
    p_api_version => 1.0,
    p_init_msg_list => FND_API.G_TRUE,
    x_return_status => l_return_status,
    p_changes => l_changes,
    p_run_submission_checks => FND_API.G_FALSE,
    p_launch_approvals_flag => FND_API.G_TRUE,
    p_buyer_id => NULL,
    p_update_source => NULL,
    p_override_date => NULL,
    x_api_errors => l_api_errors
  );

Line: 511

  PO_DEBUG.debug_stmt(l_log_head,l_progress,'After return from update document');

Line: 533

  /* Delete all the data from po_session_gt */
l_progress:= '170';

Line: 541

  DELETE FROM po_session_gt
  WHERE KEY = l_session_gt_key;

Line: 555

    DELETE FROM po_session_gt
    WHERE KEY = l_session_gt_key;

Line: 560

      DELETE FROM po_session_gt
      WHERE KEY = l_session_gt_key;

Line: 621

        UPDATE po_requisition_lines_all REQ
           SET REQ.estimated_pickup_date =
                   NVL(p_estimated_pickup_dates(i), REQ.estimated_pickup_date),
               REQ.ship_method = NVL(p_ship_methods(i), REQ.ship_method)
         WHERE REQ.line_location_id = p_line_location_id_tbl(i);

Line: 637

            p_message  => 'Can not update estimated_pickup_date and ship_method on requisitions');