APPS.PO_SHIPMENTS_SV2 SQL Statements

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

Line: 23

	 SELECT count(PLL.line_location_id)
	 INTO   X_number_shipments
         FROM   po_line_locations PLL
         WHERE  PLL.po_line_id = X_po_line_id
	 AND    PLL.shipment_type = X_shipment_type;

Line: 110

	 SELECT nvl(PLL.approved_flag,'N'),
	        nvl(PLL.encumbered_flag,'N'),
		nvl(PLL.closed_code,'OPEN'),
		nvl(PLL.cancel_flag,'N')
	 FROM   po_line_locations PLL
         WHERE  PLL.line_location_id = X_line_location_id
	 AND    PLL.shipment_type = X_shipment_type;

Line: 172

  FUNCTION NAME:	update_shipment_qty

===========================================================================*/
   FUNCTION update_shipment_qty
		      (X_line_location_id     IN     NUMBER,
		       X_shipment_type        IN     VARCHAR2,
		       X_line_quantity        IN     NUMBER) RETURN BOOLEAN IS

      X_progress          VARCHAR2(3)  := '';

Line: 182

      X_last_updated_by   NUMBER       := '';

Line: 195

         X_last_updated_by := fnd_global.user_id;

Line: 198

   	 po_debug.put_line('Before update statement');

Line: 201

	 ** Update the purchase order shipment quantity to the lines qty.
	 */
	 UPDATE PO_LINE_LOCATIONS
	 SET    quantity           = X_line_quantity,
	        approved_flag      = decode(approved_flag, 'N', 'N', 'R'),
	        last_update_date   = sysdate,
		last_updated_by    = X_last_updated_by,
		last_update_login  = X_login_id
	 WHERE  line_location_id   = X_line_location_id
         AND    shipment_type      = X_shipment_type;

Line: 219

   	  po_debug.put_line('In UPDATE exception');

Line: 221

	  po_message_s.sql_error('update_shipment_qty', X_progress, sqlcode);

Line: 223

      END update_shipment_qty;

Line: 235

	Issues:		Should you be able to update the quantity
			on a shipment if is finally closed?
			DEBUG.

===========================================================================*/
   FUNCTION val_ship_qty
		      (X_po_line_id           IN     NUMBER,
		       X_shipment_type        IN     VARCHAR2,
		       X_line_quantity        IN     NUMBER) RETURN BOOLEAN IS

      X_progress          VARCHAR2(3)  := '';

Line: 258

	 ** with checks.  Otherwise, we should not update the price.
	 */
	 IF (X_shipment_type = 'STANDARD' OR X_shipment_type =  'PLANNED') THEN
	   null;

Line: 278

	 ** we should not update the quantity on the shipment.
	 */
         IF (X_number_shipments = 1) THEN

	    X_line_location_id := po_shipments_sv3.get_line_location_id(X_po_line_id,
					                         X_shipment_type);

Line: 306

	    ** Allow the quantity to be updated if the shipment is
	    ** not encumbered, cancelled or finally closed..
	    */
	    IF ( (X_encumbered_flag = 'N') AND
	         (X_cancelled_flag  = 'N') AND
                 (X_closed_code <> 'FINALLY CLOSED') )  THEN

		  IF po_shipments_sv2.update_shipment_qty (X_line_location_id,
						    X_shipment_type,
					            X_line_quantity) THEN
		     RETURN(TRUE);

Line: 358

	 ** with checks.  Otherwise, we should not update the price.
	 */
	 IF (X_shipment_type = 'STANDARD' OR X_shipment_type =  'PLANNED') THEN
	   null;

Line: 375

	 IF po_shipments_sv2.update_shipment_price (X_po_line_id,
		  			     X_shipment_type,
					     X_unit_price) THEN
	    IF (g_po_pdoi_write_to_file = 'Y') THEN
   	    po_debug.put_line('Returned TRUE');

Line: 405

  FUNCTION NAME:	update_shipment_price

	   DEBUG:	Should we allow you to update the price
			on a finally closed shipment?

===========================================================================*/
   FUNCTION update_shipment_price
		      (X_po_line_id           IN     NUMBER,
		       X_shipment_type        IN     VARCHAR2,
		       X_unit_price           IN     NUMBER) RETURN BOOLEAN IS

      X_progress          VARCHAR2(3)  := '';

Line: 418

      X_last_updated_by   NUMBER       := '';

Line: 431

         X_last_updated_by := fnd_global.user_id;

Line: 434

   	 po_debug.put_line('Before update statement');

Line: 437

	 ** Update the purchase order shipment price to the lines unit
	 ** price for all shipments that are not cancelled.
	 */
	 UPDATE PO_LINE_LOCATIONS
	 SET    price_override     = X_unit_price,
	        approved_flag      = decode(approved_flag, 'N', 'N', 'R'),
	        last_update_date   = sysdate,
		last_updated_by    = X_last_updated_by,
		last_update_login  = X_login_id
	 WHERE  po_line_id           = X_po_line_id
         AND    nvl(cancel_flag,'N') = 'N'
         AND    nvl(closed_code,'OPEN') <> 'FINALLY CLOSED'
         AND    shipment_type in ('STANDARD','PLANNED');

Line: 458

   	  po_debug.put_line('In UPDATE exception');

Line: 460

	  po_message_s.sql_error('update_shipment_price', X_progress, sqlcode);

Line: 462

      END update_shipment_price;

Line: 496

       SELECT (substrb(rtrim(address1)||'-'||rtrim(city),1,20)) ship_to_location_code
         INTO x_ship_to_location_code
         FROM hz_locations
        WHERE location_id = x_ship_to_location_id;