APPS.WSH_DOCUMENT_PVT SQL Statements

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

Line: 109

  SELECT
    type
  FROM
    fnd_document_sequences
  WHERE doc_sequence_id = p_doc_sequence_id;

Line: 253

SELECT
  delivery_leg_id
FROM
  wsh_delivery_legs
WHERE delivery_id = p_delivery_id
  AND pick_up_stop_id = p_pick_up_stop_id
  AND drop_off_stop_id = p_drop_off_stop_id;

Line: 376

  SELECT
    wsh.category_code,
    fnd.method_code
  FROM
    wsh_doc_sequence_categories   wsh
  , fnd_doc_sequence_assignments  fnd
  WHERE  wsh.document_type = p_document_type
    AND  wsh.enabled_flag = 'Y'
    AND  ( (wsh.location_id = p_location_id AND
		       wsh.document_code = p_document_code)
           OR
           (wsh.location_id = p_location_id AND wsh.document_code IS NULL)
           OR
           ((nvl(wsh.location_id,-99) = -99) AND wsh.document_code = p_document_code)
           OR
           ((nvl(wsh.location_id,-99) = -99) AND wsh.document_code IS NULL)
	 )
    AND  wsh.category_code = fnd.category_code
    AND  fnd.application_id = p_application_id
    AND  fnd.set_of_books_id = p_ledger_id
    AND  (fnd.end_date is NULL or fnd.end_date >= trunc(sysdate)) --Bug8608685 added trunc to sysdate
    AND  fnd.start_date <= sysdate ;

Line: 637

    SELECT doc.final_print_date
    INTO l_temp_date
    FROM wsh_document_instances doc
    WHERE doc.entity_id=p_delivery_id
      AND doc.entity_name='WSH_NEW_DELIVERIES'
      AND doc.document_type=p_document_type;

Line: 810

    UPDATE
      wsh_document_instances doc
    SET
      doc.final_print_date = l_temp_date
    WHERE doc.entity_id=p_delivery_id
      AND doc.entity_name='WSH_NEW_DELIVERIES'
      AND doc.document_type=p_document_type;

Line: 1013

SELECT
  delivery_id
, pick_up_stop_id
, drop_off_stop_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = p_entity_id;

Line: 1025

SELECT
  sequence_number
FROM
  wsh_document_instances
WHERE entity_name = c_entity_name
  AND entity_id = c_entity_id
  AND status <> 'CANCELLED'
  -- AND status = 'OPEN'
  AND document_type = p_document_type;

Line: 1039

SELECT
  doc_sequence_category_id
, category_code
, prefix
, suffix
, delimiter
FROM
  wsh_doc_sequence_categories
WHERE  document_type = p_document_type
  AND  enabled_flag = 'Y'
  AND  ((location_id = p_location_id AND document_code = p_document_sub_type)
       OR
       (location_id = p_location_id AND document_code IS NULL)
       OR
--       (location_id IS NULL AND document_code = p_document_sub_type)
--	change location_id is null to location_id = -99 (all locations => -99)
       (location_id = -99 AND document_code = p_document_sub_type)
       OR
       (location_id = -99 AND document_code IS NULL))
 ORDER BY location_id desc, document_code asc; --Bugfix 13944987

Line: 1066

SELECT
  method_code,
  doc_sequence_id
FROM
  fnd_doc_sequence_assignments
WHERE  application_id  = p_application_id
  AND  set_of_books_id = p_ledger_id
  AND  category_code   = c_category_code
  AND  start_date <= sysdate
  AND  ( (end_date IS NULL)
	 OR
	 (end_date >= trunc(sysdate)) --Bug8608685 added trunc to sysdate
  AND  start_date <= sysdate
       );

Line: 1082

SELECT
  delivery_id
FROM
  wsh_new_deliveries
WHERE delivery_id = c_delivery_id;

Line: 1089

SELECT
  delivery_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = c_delivery_leg_id;

Line: 1099

SELECT  p_delivery_leg_id
FROM    wsh_delivery_legs
WHERE   delivery_leg_id  = p_delivery_leg_id
FOR UPDATE NOWAIT;

Line: 1431

    INSERT INTO wsh_document_instances
    ( document_instance_id
    , document_type
    , sequence_number
    , status
    , final_print_date
    , entity_name
    , entity_id
    , doc_sequence_category_id
    , created_by
    , creation_date
    , last_updated_by
    , last_update_date
    , last_update_login
    , program_application_id
    , program_id
    , program_update_date
    , request_id
    , attribute_category
    , attribute1
    , attribute2
    , attribute3
    , attribute4
    , attribute5
    , attribute6
    , attribute7
    , attribute8
    , attribute9
    , attribute10
    , attribute11
    , attribute12
    , attribute13
    , attribute14
    , attribute15
    )
    VALUES
    ( wsh_document_instances_s.nextval
    , p_document_type
    , l_document_number
    , l_status
    , null
    , l_entity_name
    , l_entity_id
    , l_doc_sequence_category_id
    , fnd_global.user_id
    , sysdate
    , fnd_global.user_id
    , sysdate
    , fnd_global.login_id
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    );

Line: 1512

			UPDATE  wsh_delivery_legs
			SET  doc_date_issued = SYSDATE
			WHERE  current of get_lock_on_leg;

Line: 1589

    INSERT INTO wsh_document_instances
    ( document_instance_id
    , document_type
    , sequence_number
    , status
    , final_print_date
    , entity_name
    , entity_id
    , doc_sequence_category_id
    , created_by
    , creation_date
    , last_updated_by
    , last_update_date
    , last_update_login
    , program_application_id
    , program_id
    , program_update_date
    , request_id
    , attribute_category
    , attribute1
    , attribute2
    , attribute3
    , attribute4
    , attribute5
    , attribute6
    , attribute7
    , attribute8
    , attribute9
    , attribute10
    , attribute11
    , attribute12
    , attribute13
    , attribute14
    , attribute15
    )
    VALUES
    ( wsh_document_instances_s.nextval
    , p_document_type
    , l_document_number
    , l_status --Bug# 3789154
    , null
    , p_entity_name
    , p_entity_id
    , l_doc_sequence_category_id
    , fnd_global.user_id
    , sysdate
    , fnd_global.user_id
    , sysdate
    , fnd_global.login_id
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    , null
    );

Line: 1687

     SELECT wnd.name INTO l_delivery_name
     FROM   wsh_new_deliveries wnd, wsh_delivery_legs wdl
     WHERE  wnd.delivery_id = wdl.delivery_id
     AND    wdl.delivery_leg_id  = l_delivery_leg_id;

Line: 1700

         WSH_DEBUG_SV.logmsg(l_module_name,'Cannot lock delivery leg for update');

Line: 1836

PROCEDURE Update_Document
( p_api_version               IN  NUMBER
, p_init_msg_list             IN  VARCHAR2 DEFAULT FND_API.g_false
, p_commit                    IN  VARCHAR2 DEFAULT FND_API.g_false
, p_validation_level          IN  NUMBER   DEFAULT FND_API.g_valid_level_full
, x_return_status             OUT NOCOPY  VARCHAR2
, x_msg_count                 OUT NOCOPY  NUMBER
, x_msg_data                  OUT NOCOPY  VARCHAR2
, p_entity_name               IN  VARCHAR2 DEFAULT NULL
, p_entity_id                 IN  NUMBER
, p_document_type             IN  VARCHAR2
, p_ledger_id                 IN  NUMBER   -- LE Uptake
, p_consolidate_option        IN  VARCHAR2 DEFAULT 'BOTH'
) IS
L_API_NAME           CONSTANT VARCHAR2(30) := 'Update_Document';

Line: 1861

SELECT status
--SELECT *
FROM
  wsh_document_instances
WHERE entity_name = c_entity_name
  AND entity_id = c_entity_id
  AND document_type = p_document_type
  AND status not in ('COMPLETE', 'CANCELLED')
  FOR UPDATE;

Line: 1872

SELECT
  delivery_id
, pick_up_stop_id
, drop_off_stop_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = p_entity_id;

Line: 1881

SELECT
  delivery_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = c_delivery_leg_id;

Line: 1894

l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DOCUMENT';

Line: 2041

    UPDATE wsh_document_instances
    SET
      last_update_date           = sysdate
    , last_updated_by            = fnd_global.user_id
    , last_update_login          = fnd_global.login_id
    WHERE CURRENT OF old_values_csr;

Line: 2116

END Update_Document;

Line: 2185

SELECT
  entity_id
  , status
FROM
  wsh_document_instances
WHERE entity_name = c_entity_name
  AND entity_id = c_entity_id
  AND document_type = p_document_type
  AND status in ('OPEN', 'PLANNED')
  FOR UPDATE;

Line: 2197

SELECT
  delivery_id
, pick_up_stop_id
, drop_off_stop_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = p_entity_id;

Line: 2206

SELECT
  delivery_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = c_delivery_leg_id;

Line: 2356

    UPDATE wsh_document_instances
    SET status = 'CANCELLED'
    , last_update_date           = sysdate
    , last_updated_by            = fnd_global.user_id
    , last_update_login          = fnd_global.login_id
     WHERE CURRENT OF status_csr;

Line: 2371

    UPDATE wsh_document_instances
    SET status = 'CANCELLED'
    , last_update_date           = sysdate
    , last_updated_by            = fnd_global.user_id
    , last_update_login          = fnd_global.login_id
     WHERE entity_name = l_entity_name
     AND entity_id = p_entity_id
     AND document_type = p_document_type
     AND status in ('OPEN', 'PLANNED');

Line: 2504

SELECT
  entity_id
  , status
FROM
  wsh_document_instances
WHERE entity_name = c_entity_name
  AND entity_id = c_entity_id
  AND document_type = p_document_type
  AND status = 'PLANNED'
  FOR UPDATE;

Line: 2516

SELECT
  delivery_id
, pick_up_stop_id
, drop_off_stop_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = p_entity_id;

Line: 2525

SELECT
  delivery_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = c_delivery_leg_id;

Line: 2672

    UPDATE wsh_document_instances
    SET status = 'OPEN'
    , last_update_date           = sysdate
    , last_updated_by            = fnd_global.user_id
    , last_update_login          = fnd_global.login_id
     WHERE CURRENT OF status_csr;

Line: 2808

SELECT
  entity_id
  , status
FROM
  wsh_document_instances
WHERE entity_name = c_entity_name
  AND entity_id = c_entity_id
  AND document_type = p_document_type
  AND status not in ('CANCELLED')
  FOR UPDATE;

Line: 2820

SELECT
  delivery_id
, pick_up_stop_id
, drop_off_stop_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = p_entity_id;

Line: 2829

SELECT
  delivery_id
FROM
  wsh_delivery_legs
WHERE delivery_leg_id = c_delivery_leg_id;

Line: 2976

    UPDATE wsh_document_instances
    SET status = 'COMPLETE'
    , last_update_date           = sysdate
    , last_updated_by            = fnd_global.user_id
    , last_update_login          = fnd_global.login_id
     WHERE CURRENT OF status_csr;

Line: 3380

  SELECT
    ol.ship_from_org_id
  , ol.source_document_id
  , ol.source_document_line_id
  , ss.cust_acct_site_id
  , bs.cust_acct_site_id
  , DECODE (ol.item_identifier_type,
		  'CUST', ol.ordered_item_id, NULL)
  , ol.cust_po_number
  , ol.industry_attribute1
  , ol.inventory_item_id
  INTO
    l_ship_from_org_id
  , l_source_doc_hdr_id
  , l_source_doc_line_id
  , l_ship_to_address_id
  , l_bill_to_address_id
  , l_customer_item_id
  , l_po_number
  , l_cust_record_year
  , l_inventory_item_id
  FROM
    oe_order_lines_all ol
  , hz_cust_acct_sites_all ss
  , hz_cust_site_uses_all ssu
  , hz_cust_acct_sites_all bs
  , hz_cust_site_uses_all bsu
  WHERE ol.line_id=p_oe_order_line_id
    and ol.ship_to_org_id = ssu.site_use_id (+)
    and ssu.site_use_code = 'SHIP_TO'
    and ol.invoice_to_org_id = bsu.site_use_id (+)
    and bsu.site_use_code = 'BILL_TO'
    and ssu.cust_acct_site_ID = ss.cust_acct_site_ID
    and bsu.cust_acct_site_ID = bs.cust_acct_site_ID;

Line: 3630

  UPDATE wsh_document_instances
  SET status = 'CANCELLED'
  , last_update_date           = sysdate
  , last_updated_by            = fnd_global.user_id
  , last_update_login          = fnd_global.login_id
  WHERE entity_name = p_entity_name
  AND   entity_id = p_entity_id;

Line: 3749

SELECT document_instance_id
	, document_type
	, entity_name
	, entity_id
	, doc_sequence_category_id
	, sequence_number
	, status
	, final_print_date
	, created_by
	, creation_date
	, last_updated_by
	, last_update_date
	, last_update_login
	, program_application_id
	, program_id
	, program_update_date
	, request_id
	, attribute_category
	, attribute1
	, attribute2
	, attribute3
	, attribute4
	, attribute5
	, attribute6
	, attribute7
	, attribute8
	, attribute9
	, attribute10
	, attribute11
	, attribute12
	, attribute13
	, attribute14
	, attribute15
FROM   wsh_document_instances
WHERE  entity_name = p_entity_name
AND    entity_id = p_entity_id;

Line: 3858

    x_document_tab(i).last_updated_by     := doc_rec.last_updated_by;

Line: 3859

    x_document_tab(i).last_update_date    := doc_rec.last_update_date;

Line: 3860

    x_document_tab(i).last_update_login   := doc_rec.last_update_login;

Line: 3864

    x_document_tab(i).program_update_date := doc_rec.program_update_date;

Line: 4055

, p_last_updated_by           IN  NUMBER
, p_last_update_date          IN  DATE
, p_last_update_login         IN  NUMBER
, p_program_application_id    IN  NUMBER
, p_program_id                IN  NUMBER
, p_program_update_date       IN  DATE
, p_request_id                IN  NUMBER
, p_attribute_category        IN  VARCHAR2
, p_attribute1                IN  VARCHAR2
, p_attribute2                IN  VARCHAR2
, p_attribute3                IN  VARCHAR2
, p_attribute4                IN  VARCHAR2
, p_attribute5                IN  VARCHAR2
, p_attribute6                IN  VARCHAR2
, p_attribute7                IN  VARCHAR2
, p_attribute8                IN  VARCHAR2
, p_attribute9                IN  VARCHAR2
, p_attribute10               IN  VARCHAR2
, p_attribute11               IN  VARCHAR2
, p_attribute12               IN  VARCHAR2
, p_attribute13               IN  VARCHAR2
, p_attribute14               IN  VARCHAR2
, p_attribute15               IN  VARCHAR2
, x_return_status             OUT NOCOPY  VARCHAR2
) IS

  counter NUMBER;

Line: 4083

    SELECT
      document_instance_id
    , document_type
    , sequence_number
    , status
    , final_print_date
    , entity_name
    , entity_id
    , doc_sequence_category_id
    , created_by
    , creation_date
    , last_updated_by
    , last_update_date
    , last_update_login
    , program_application_id
    , program_id
    , program_update_date
    , request_id
    , attribute_category
    , attribute1
    , attribute2
    , attribute3
    , attribute4
    , attribute5
    , attribute6
    , attribute7
    , attribute8
    , attribute9
    , attribute10
    , attribute11
    , attribute12
    , attribute13
    , attribute14
    , attribute15
    FROM
      wsh_document_instances
    WHERE rowid = p_rowid
    FOR UPDATE OF document_instance_id NOWAIT;

Line: 4153

      WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATED_BY',P_LAST_UPDATED_BY);

Line: 4154

      WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_DATE',P_LAST_UPDATE_DATE);

Line: 4155

      WSH_DEBUG_SV.log(l_module_name,'P_LAST_UPDATE_LOGIN',P_LAST_UPDATE_LOGIN);

Line: 4158

      WSH_DEBUG_SV.log(l_module_name,'P_PROGRAM_UPDATE_DATE',P_PROGRAM_UPDATE_DATE);

Line: 4183

    FND_MESSAGE.set_name ('FND', 'FORM_RECORD_DELETED');

Line: 4196

    AND lock_rec.last_updated_by      = p_last_updated_by
    AND lock_rec.last_update_date     = p_last_update_date

    -- verify the nullable columns are either identical or both null
    AND ((lock_rec.status = p_status)
        OR
        (lock_rec.status IS NULL AND p_status IS NULL))
    AND ((lock_rec.final_print_date = p_final_print_date)
	   OR
	   (lock_rec.final_print_date IS NULL AND p_final_print_date IS NULL))
    AND ((lock_rec.doc_sequence_category_id = p_doc_sequence_category_id)
	   OR
	   (lock_rec.doc_sequence_category_id IS NULL AND
								  p_doc_sequence_category_id IS NULL))
    AND ((lock_rec.last_update_login = p_last_update_login)
	   OR
	   (lock_rec.last_update_login IS NULL AND p_last_update_login IS NULL))
    AND ((lock_rec.program_application_id = p_program_application_id)
	   OR
	   (lock_rec.program_application_id IS NULL AND
									 p_program_application_id IS NULL))
    AND ((lock_rec.program_id = p_program_id)
	   OR
	   (lock_rec.program_id IS NULL AND p_program_id IS NULL))
    AND ((lock_rec.program_update_date = p_program_update_date)
	   OR
       (lock_rec.program_update_date IS NULL AND p_program_update_date IS NULL))
    AND ((lock_rec.request_id = p_request_id)
	   OR
	   (lock_rec.request_id IS NULL AND p_request_id IS NULL))
    AND ((lock_rec.attribute_category = p_attribute_category)
	   OR
	   (lock_rec.attribute_category IS NULL AND p_attribute_category IS NULL))
    AND ((lock_rec.attribute1 = p_attribute1)
	   OR
	   (lock_rec.attribute1 IS NULL AND p_attribute1 IS NULL))
    AND ((lock_rec.attribute2 = p_attribute2)
	   OR
	   (lock_rec.attribute2 IS NULL AND p_attribute2 IS NULL))
    AND ((lock_rec.attribute3 = p_attribute3)
	   OR
	   (lock_rec.attribute3 IS NULL AND p_attribute3 IS NULL))
    AND ((lock_rec.attribute4 = p_attribute4)
	   OR
	   (lock_rec.attribute4 IS NULL AND p_attribute4 IS NULL))
    AND ((lock_rec.attribute5 = p_attribute5)
	   OR
	   (lock_rec.attribute5 IS NULL AND p_attribute5 IS NULL))
    AND ((lock_rec.attribute6 = p_attribute6)
	   OR
	   (lock_rec.attribute6 IS NULL AND p_attribute6 IS NULL))
    AND ((lock_rec.attribute7 = p_attribute7)
	   OR
	   (lock_rec.attribute7 IS NULL AND p_attribute7 IS NULL))
    AND ((lock_rec.attribute8 = p_attribute8)
	   OR
	   (lock_rec.attribute8 IS NULL AND p_attribute8 IS NULL))
    AND ((lock_rec.attribute9 = p_attribute9)
	   OR
	   (lock_rec.attribute9 IS NULL AND p_attribute9 IS NULL))
    AND ((lock_rec.attribute10 = p_attribute10)
	   OR
	   (lock_rec.attribute10 IS NULL AND p_attribute10 IS NULL))
    AND ((lock_rec.attribute11 = p_attribute11)
	   OR
	   (lock_rec.attribute11 IS NULL AND p_attribute11 IS NULL))
    AND ((lock_rec.attribute12 = p_attribute12)
	   OR
	   (lock_rec.attribute12 IS NULL AND p_attribute12 IS NULL))
    AND ((lock_rec.attribute13 = p_attribute13)
	   OR
	   (lock_rec.attribute13 IS NULL AND p_attribute13 IS NULL))
    AND ((lock_rec.attribute14 = p_attribute14)
	   OR
	   (lock_rec.attribute14 IS NULL AND p_attribute14 IS NULL))
    AND ((lock_rec.attribute15 = p_attribute15)
	   OR
	   (lock_rec.attribute15 IS NULL AND p_attribute15 IS NULL))
     )
  THEN
    --
    -- Debug Statements
    --
    IF l_debug_on THEN
        WSH_DEBUG_SV.pop(l_module_name);

Line: 4408

	SELECT ISO_LANGUAGE, ISO_TERRITORY INTO l_language, l_territory
	FROM FND_LANGUAGES
	WHERE LANGUAGE_CODE = userenv('LANG');