APPS.POS_WC_CREATE_UPDATE_PVT SQL Statements

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

Line: 49

      l_module := 'pos.plsql.pos_wc_create_update_pvt.'||p_api_name;

Line: 74

    SELECT  'Y' INTO l_draft_exists
    FROM    RCV_HEADERS_INTERFACE RHI,
			RCV_TRANSACTIONS_INTERFACE RTI
    WHERE   RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
			RTI.PO_HEADER_ID = p_po_header_id AND
			RHI.processing_status_code = 'DRAFT' AND
			RHI.transaction_type = 'DRAFT' AND
			ROWNUM = 1;

Line: 224

	SELECT
		SUM(NVL(RSL.AMOUNT, RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
	INTO x_po_approved
	FROM RCV_SHIPMENT_HEADERS RSH,
			 RCV_SHIPMENT_LINES RSL,
	     PO_LINE_LOCATIONS_ALL POLL
	WHERE RSL.po_header_id = p_po_header_id
		AND RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
		AND	RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
		AND RSH.APPROVAL_STATUS = 'APPROVED'
		AND RSL.APPROVAL_STATUS in ('APPROVED');

Line: 279

	SELECT	SUM(
						NVL((POLL.QUANTITY - NVL(POLL.QUANTITY_CANCELLED,0))
                *POLL.PRICE_OVERRIDE,
						(POLL.AMOUNT - NVL(POLL.AMOUNT_CANCELLED,0))))
	INTO l_po_ordered
	FROM PO_LINE_LOCATIONS_ALL POLL
	WHERE POLL.PO_HEADER_ID = p_po_header_id;

Line: 312

		SELECT SUM(NVL(RTI.AMOUNT, RTI.QUANTITY*POLL.PRICE_OVERRIDE))
		INTO l_intf_request
		FROM	RCV_TRANSACTIONS_INTERFACE RTI,
					RCV_HEADERS_INTERFACE RHI,
					PO_LINE_LOCATIONS_ALL POLL
		WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID and
					RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
					RHI.HEADER_INTERFACE_ID = p_wc_id;

Line: 323

		SELECT SUM(NVL(RSL.AMOUNT_SHIPPED, RSL.QUANTITY_SHIPPED*POLL.PRICE_OVERRIDE))
		INTO l_ship_request
		FROM	RCV_SHIPMENT_LINES RSL,
					RCV_SHIPMENT_HEADERS RSH,
					PO_LINE_LOCATIONS_ALL POLL
		WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
					RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
					RSH.SHIPMENT_HEADER_ID = p_wc_id;

Line: 399

			SELECT RSL.amount,
				     RSL.quantity_shipped,
					   POLL.amount,
						 POLL.quantity,
						POLL.matching_basis,
						POLL.line_location_id
			INTO  l_amount_shipped,
				    l_quantity_shipped,
						l_amount_ordered,
						l_quantity_ordered,
						l_matching_basis,
						l_line_location_id
			FROM  RCV_SHIPMENT_LINES RSL,
						PO_LINE_LOCATIONS_ALL POLL
			WHERE RSL.shipment_line_id = p_wc_pay_item_id and
						POLL.line_location_id = RSL.po_line_location_id;

Line: 488

		SELECT RHI.request_date, RHI.processing_status_code, RHI.transaction_type
		INTO l_request_date, l_header_processing_status, l_header_transaction_type
		FROM   RCV_HEADERS_INTERFACE RHI,
					 RCV_TRANSACTIONS_INTERFACE RTI
		WHERE  RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
					 RTI.INTERFACE_TRANSACTION_ID = p_wc_pay_item_id;

Line: 504

		SELECT RSH.request_date INTO l_request_date
		FROM   RCV_SHIPMENT_HEADERS RSH,
	         RCV_SHIPMENT_LINES RSL
		WHERE  RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
					 RSL.SHIPMENT_LINE_ID = p_wc_pay_item_id;

Line: 512

		SELECT sysdate INTO l_request_date FROM DUAL;

Line: 524

		SELECT sum(NVL(RTI.AMOUNT,RTI.QUANTITY))
		INTO	l_wc_prev_submitted_intf
		FROM	PO_LINE_LOCATIONS_ALL POLL,
					RCV_TRANSACTIONS_INTERFACE RTI,
					RCV_HEADERS_INTERFACE RHI
		WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND
			    RTI.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
				  RTI.PROCESSING_STATUS_CODE = 'PENDING' AND
					RTI.TRANSACTION_STATUS_CODE = 'PENDING' AND
					RTI.PO_LINE_LOCATION_ID = p_po_pay_item_id AND
					RHI.REQUEST_DATE < l_request_date;

Line: 550

		SELECT	sum(NVL(RSL.AMOUNT_SHIPPED, RSL.QUANTITY_SHIPPED))
		INTO	l_wc_prev_submitted_ship
		FROM 	PO_LINE_LOCATIONS_ALL POLL,
					RCV_SHIPMENT_HEADERS RSH,
					RCV_SHIPMENT_LINES RSL
		WHERE RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND
				  RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND
						(RSL.APPROVAL_STATUS is NULL OR
						 RSL.APPROVAL_STATUS in ('APPROVED', 'REJECTED', 'PROCESSED')) AND
          RSL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED' AND
					RSL.PO_LINE_LOCATION_ID = p_po_pay_item_id AND
					RSH.REQUEST_DATE < l_request_date;

Line: 613

  SELECT WF_ITEM_KEY, WF_ITEM_TYPE
  INTO l_wf_item_key,l_wf_item_type
  FROM RCV_SHIPMENT_HEADERS
  WHERE SHIPMENT_HEADER_ID = p_wc_header_id;

Line: 668

			All the updateable fields should have been updated at this point.
  */

  --1)Reset the Approval Status and Comment columns of the Header and the Lines

	UPDATE RCV_SHIPMENT_HEADERS
	SET APPROVAL_STATUS = null, COMMENTS = null
  WHERE SHIPMENT_HEADER_ID = p_wc_header_id;

Line: 677

	UPDATE RCV_SHIPMENT_LINES
	SET APPROVAL_STATUS = null, COMMENTS = null
  WHERE SHIPMENT_HEADER_ID = p_wc_header_id;

Line: 684

	select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
  into l_seq_for_item_key
  from sys.dual;