APPS.OKE_DELIVERABLE_UTILS_PUB SQL Statements

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

Line: 31

  SELECT reference2
  FROM oke_deliverable_actions
  WHERE pa_action_id = p_action_id
  AND action_type = 'WSH';

Line: 80

  SELECT reference1
  FROM oke_deliverable_actions
  WHERE pa_action_id = p_action_id
  AND action_type = 'WSH';

Line: 134

  SELECT a.reference1, a.action_id, d.project_id
    FROM oke_deliverable_actions a, oke_deliverables_b d
    WHERE a.pa_action_id = p_action_id  AND action_type = 'REQ'
      AND d.deliverable_id = a.deliverable_id
  ;

Line: 141

   SELECT Decode(process_flag, 'ERROR', G_NO, G_YES)
     FROM po_requisitions_interface_all
     WHERE oke_contract_deliverable_id = l_action_id AND batch_id = l_ref_1
  ;

Line: 147

   SELECT G_YES
     FROM oke_deliverable_requisitions_v
     WHERE ACTION_ID = l_action_id and project_id = l_project_id
  ;

Line: 202

    SELECT 1
    FROM oke_deliverables_b
    WHERE source_code = 'PA'
    AND source_deliverable_id = p_deliverable_id
    AND item_id > 0;

Line: 244

  SELECT NVL(ready_flag, 'N')
  FROM oke_deliverable_actions
  WHERE pa_action_id = p_action_id
  AND action_type = 'WSH';

Line: 288

  SELECT NVL(ready_flag, 'N'), reference1
  FROM oke_deliverable_actions
  WHERE pa_action_id = p_action_id
  AND action_type = 'REQ';

Line: 294

  SELECT 1
  FROM dual
  WHERE EXISTS ( SELECT 1
		 FROM po_requisitions_interface_all
		 WHERE oke_contract_deliverable_id = p_action_id
		 AND process_flag = 'ERROR');

Line: 354

  SELECT NVL(shippable_item_flag, 'N')
  FROM oke_system_items_v
  WHERE id1 = (
	SELECT item_id
	FROM oke_deliverables_b
        WHERE source_code = 'PA'
	AND source_deliverable_id = p_deliverable_id );

Line: 403

  SELECT NVL(invoiceable_item_flag, 'N')
  FROM oke_system_items_v
  WHERE id1 = (
	SELECT item_id
	FROM oke_deliverables_b
        WHERE source_code = 'PA'
	AND source_deliverable_id = p_deliverable_id );

Line: 451

  SELECT NVL(purchasing_enabled_flag, 'N')
  FROM oke_system_items_v
  WHERE id1 = (
	SELECT item_id
	FROM oke_deliverables_b
        WHERE source_code = 'PA'
    	AND source_deliverable_id = p_deliverable_id );

Line: 488

  SELECT 1
  INTO dummy
  FROM oke_deliverable_actions
  WHERE pa_action_id = p_action_id
  AND reference1 > 0
  AND NOT EXISTS ( SELECT 1
	FROM po_requisitions_interface_all
 	WHERE oke_contract_deliverable_id = p_action_id
	AND process_flag = 'ERROR' );

Line: 528

  SELECT deliverable_id, description, comments
  FROM oke_deliverables_vl
  WHERE source_code = 'PA'
  AND source_header_id = p_source_project_id
  AND source_deliverable_id = p_source_deliverable_id;

Line: 559

  SELECT oke_k_deliverables_s.nextval
  INTO l_id
  FROM DUAL;

Line: 569

    INSERT INTO oke_deliverables_b (
	deliverable_id
	, creation_date
	, created_by
	, last_updated_by
	, last_update_date
 	, last_update_login
	, deliverable_number
	, source_code
	, source_header_id
	, source_line_id
	, source_deliverable_id
	, project_id
	, delivery_date
	, item_id
	, currency_code
	, inventory_org_id
	, unit_price
	, uom_code
	, quantity
	, unit_number )
    SELECT l_id
	, sysdate
	, fnd_global.user_id
	, fnd_global.user_id
	, sysdate
	, fnd_global.login_id
	, p_target_deliverable_number
	, 'PA'
	, p_target_project_id
	, null
	, p_target_deliverable_id
	, p_target_project_id
	, delivery_date
	, item_id
	, currency_code
	, inventory_org_id
	, unit_price
	, uom_code
	, quantity
	, unit_number
    FROM oke_deliverables_b
    WHERE source_code = 'PA'
    AND source_header_id = p_source_project_id
    AND source_deliverable_id = p_source_deliverable_id;

Line: 617

   INSERT INTO oke_deliverables_b (
	deliverable_id
	, creation_date
	, created_by
	, last_updated_by
	, last_update_date
 	, last_update_login
	, deliverable_number
	, source_code
	, source_header_id
	, source_line_id
	, source_deliverable_id
	, project_id
	, delivery_date)
    SELECT l_id
	, sysdate
	, fnd_global.user_id
	, fnd_global.user_id
	, sysdate
	, fnd_global.login_id
	, p_target_deliverable_number
	, 'PA'
	, p_target_project_id
	, null
	, p_target_deliverable_id
	, p_target_project_id
	, delivery_date
    FROM oke_deliverables_b
    WHERE source_code = 'PA'
    AND source_header_id = p_source_project_id
    AND source_deliverable_id = p_source_deliverable_id;

Line: 650

  INSERT INTO oke_deliverables_tl (
  	deliverable_id
	, language
	, creation_date
	, created_by
	, last_update_date
	, last_updated_by
	, last_update_login
	, source_lang
	, description
	, comments )
  SELECT l_id
	, l.language_code
	, sysdate
	, fnd_global.user_id
	, sysdate
	, fnd_global.user_id
	, fnd_global.login_id
	, oke_utils.get_userenv_lang
	, L_Deliverable_Description
	, L_Deliverable_Comments
  FROM fnd_languages l
  WHERE L.INSTALLED_FLAG in ('I', 'B')
  AND  not exists
      (select NULL
      from OKE_DELIVERABLES_TL T
      where T.DELIVERABLE_ID = l_id
      and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 737

  SELECT deliverable_id, currency_code, inventory_org_id,
         unit_price, uom_code, quantity,
         PA_DELIVERABLE_UTILS.IS_DLVR_ITEM_BASED(p_target_deliverable_id) Item_Based_YN
  FROM oke_deliverables_b
  WHERE source_code = 'PA'
  AND source_header_id = p_target_project_id
  AND source_deliverable_id = p_target_deliverable_id;

Line: 776

  INSERT INTO oke_deliverable_actions (
    action_id
    , creation_date
    , created_by
    , last_update_date
    , last_updated_by
    , last_update_login
    , action_type
    , action_name
    , pa_action_id
    , deliverable_id
    , ship_to_org_id
    , ship_to_location_id
    , ship_from_org_id
    , ship_from_location_id
    , inspection_req_flag
    , expected_date
    , schedule_designator
    , volume
    , volume_uom_code
    , weight
    , weight_uom_code
    , expenditure_organization_id
    , expenditure_type
    , expenditure_item_date
    , destination_type_code
    , rate_type
    , rate_date
    , exchange_rate
    , requisition_line_type_id
    , po_category_id
    , unit_price
    , currency_code
    , quantity
    , uom_code)
  SELECT oke_k_deliverables_s.nextval
    , sysdate
    , fnd_global.user_id
    , sysdate
    , fnd_global.user_id
    , fnd_global.login_id
    , action_type
    , p_target_action_name
    , p_target_action_id
    , l_deliverable_id

    , Decode( action_type||','||l_Item_Based_YN,
            'REQ,Y', l_inventory_org_id, ship_to_org_id ) ship_to_org_id
    , Decode( action_type||','||l_Item_Based_YN||','||Nvl(l_inventory_org_id,''),
            'REQ,Y,', NULL, ship_to_location_id ) ship_to_location_id

    , Decode( action_type||','||l_Item_Based_YN,
            'WSH,Y', l_inventory_org_id, ship_from_org_id ) ship_from_org_id
    , Decode( action_type||','||l_Item_Based_YN||','||Nvl(l_inventory_org_id,''),
            'WSH,Y,', NULL, ship_from_location_id ) ship_from_location_id

    , inspection_req_flag
    , p_target_action_date
    , Decode( l_inventory_org_id, NULL, NULL, schedule_designator) schedule_designator
    , volume
    , volume_uom_code
    , weight
    , weight_uom_code
    , expenditure_organization_id
    , expenditure_type
    , expenditure_item_date
    , destination_type_code
    , rate_type
    , rate_date
    , exchange_rate
    , requisition_line_type_id
    , po_category_id
    , Decode( l_Item_Based_YN, 'Y', l_unit_price, unit_price)
    , Decode( l_Item_Based_YN, 'Y', l_currency_code, currency_code)
    , Decode( l_Item_Based_YN, 'Y', l_quantity, quantity)
    , Decode( l_Item_Based_YN, 'Y', l_uom_code, uom_code)
  FROM oke_deliverable_actions
  WHERE pa_action_id = p_source_action_id;

Line: 888

PROCEDURE Delete_Action ( P_Action_ID 		NUMBER -- PA_ACTION_ID
			, X_Return_Status	OUT NOCOPY VARCHAR2
			, X_Msg_Count		OUT NOCOPY NUMBER
			, X_Msg_Data		OUT NOCOPY VARCHAR2 ) IS

  L_API_Name		CONSTANT VARCHAR2(30) := 'DELETE_ACTION';

Line: 910

  OKE_DELIVERABLE_ACTIONS_PKG.Delete_Action ( P_Action_ID );

Line: 943

END Delete_Action;

Line: 945

PROCEDURE Delete_Demand ( P_Action_ID 		NUMBER -- OKE_ACTION_ID
			, X_Return_Status	OUT NOCOPY VARCHAR2
			, X_Msg_Count		OUT NOCOPY NUMBER
			, X_Msg_Data		OUT NOCOPY VARCHAR2 ) IS

  L_API_Name		CONSTANT VARCHAR2(30) := 'DELETE_DEMAND';

Line: 967

  OKE_DELIVERABLE_ACTIONS_PKG.Delete_Row ( P_Action_ID );

Line: 1000

END Delete_Demand;

Line: 1002

PROCEDURE Delete_Deliverable ( P_Deliverable_ID	NUMBER
			, X_Return_Status	OUT NOCOPY VARCHAR2
			, X_Msg_Count		OUT NOCOPY NUMBER
			, X_Msg_Data		OUT NOCOPY VARCHAR2 ) IS

  L_API_Name		CONSTANT VARCHAR2(30) := 'DELETE_DELIVERABLE';

Line: 1024

  OKE_DELIVERABLE_ACTIONS_PKG.Delete_Deliverable ( P_Deliverable_ID );

Line: 1057

END Delete_Deliverable;

Line: 1068

  select list_price_per_unit
  from mtl_system_items_b
  where organization_id = p_org_id
  and inventory_item_id = p_item_id;

Line: 1114

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND a.reference2 is null
  AND b.item_id > 0
  AND b.quantity > 0
  AND b.inventory_org_id > 0
  AND b.uom_code IS NOT NULL
  AND a.schedule_designator IS NOT NULL
  AND a.action_type = 'WSH'
  AND a.expected_date >= sysdate;

Line: 1130

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND a.reference2 is null
  AND b.project_id = p_project_id
  AND b.item_id > 0
  AND b.quantity > 0
  AND b.inventory_org_id > 0
  AND b.uom_code IS NOT NULL
  AND a.schedule_designator IS NOT NULL
  AND a.action_type = 'WSH'
  AND a.expected_date >= sysdate;

Line: 1147

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND a.reference2 is null
  AND a.task_id = p_task_id
  AND b.item_id > 0
  AND b.quantity > 0
  AND b.inventory_org_id > 0
  AND b.uom_code IS NOT NULL
  AND a.schedule_designator IS NOT NULL
  AND a.action_type = 'WSH'
  AND a.expected_date >= sysdate;

Line: 1271

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND b.source_code = 'PA'
  AND ( a.reference1 is null OR EXISTS (
		SELECT 1
 		FROM po_requisitions_interface_all
 		WHERE oke_contract_deliverable_id > 0
		AND process_flag = 'ERROR'
		AND batch_id = a.reference1 ))
  AND NVL ( a.ready_flag, 'N' ) = 'Y'
  AND a.action_type = 'REQ'
  AND a.expected_date >= sysdate;

Line: 1289

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND b.source_code = 'PA'
  AND b.source_header_id = p_project_id
  AND ( a.reference1 is null OR EXISTS (
		SELECT 1
 		FROM po_requisitions_interface_all
 		WHERE oke_contract_deliverable_id > 0
		AND process_flag = 'ERROR'
		AND batch_id = a.reference1 ))
  AND NVL ( a.ready_flag, 'N' ) = 'Y'
  AND a.action_type = 'REQ'
  AND a.expected_date >= sysdate;

Line: 1308

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND b.source_code = 'PA'
  AND b.source_header_id = p_project_id
  AND ( a.reference1 is null OR EXISTS (
		SELECT 1
 		FROM po_requisitions_interface_all
 		WHERE oke_contract_deliverable_id > 0
		AND process_flag = 'ERROR'
		AND batch_id = a.reference1 ))
  AND NVL ( a.ready_flag, 'N' ) = 'Y'
  AND a.action_type = 'REQ'
  AND a.task_id = p_task_id
  AND a.expected_date >= sysdate;

Line: 1437

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND b.source_code = 'PA'
  AND a.reference1 is null
  AND NVL ( a.ready_flag, 'N' ) = 'Y'
  AND a.action_type = 'WSH'
  AND a.expected_date >= sysdate;

Line: 1450

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND b.source_code = 'PA'
  AND b.source_header_id = p_project_id
  AND a.reference1 is null
  AND NVL ( a.ready_flag, 'N' ) = 'Y'
  AND a.action_type = 'WSH'
  AND a.expected_date >= sysdate;

Line: 1464

  SELECT a.action_id
  FROM oke_deliverables_b b
  , oke_deliverable_actions a
  WHERE b.deliverable_id = a.deliverable_id
  AND b.source_code = 'PA'
  AND b.source_header_id = p_project_id
  AND a.reference1 is null
  AND NVL ( a.ready_flag, 'N' ) = 'Y'
  AND a.action_type = 'WSH'
  AND a.task_id = p_task_id
  AND a.expected_date >= sysdate;

Line: 1584

    SELECT 1
    FROM oke_deliverables_b
    WHERE source_code = 'PA'
    AND source_deliverable_id = p_deliverable_id
    AND item_id > 0;

Line: 1617

    SELECT DELIVERABLE_ID
    FROM oke_deliverables_b
    WHERE SOURCE_CODE = P_SOURCE_CODE
    AND SOURCE_DELIVERABLE_ID = P_SOURCE_DELIVERABLE_ID;

Line: 1624

    SELECT oke_k_deliverables_s.NEXTVAL INTO L_ID FROM DUAL;

Line: 1630

    INSERT INTO oke_deliverable_actions (
      ACTION_ID
    , CREATION_DATE
    , CREATED_BY
    , LAST_UPDATE_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_LOGIN
    , ACTION_TYPE
    , ACTION_NAME
    , DELIVERABLE_ID
    , PA_ACTION_ID
    , EXPECTED_DATE
    , destination_type_code
    ) VALUES ( L_ID
    , SYSDATE
    , FND_GLOBAL.USER_ID
    , SYSDATE
    , FND_GLOBAL.USER_ID
    , FND_GLOBAL.LOGIN_ID
    , P_ACTION_TYPE
    , P_SOURCE_ACTION_NAME
    , L_DELIVERABLE_ID
    , P_SOURCE_ACTION_ID
    , P_ACTION_DATE
    , Decode( P_ACTION_TYPE, 'REQ', 'EXPENSE', NULL)
    );

Line: 1664

    SELECT G_Yes
    FROM oke_deliverable_actions
    WHERE task_id = p_task_id
    AND action_type = 'WSH'
--    AND reference1 > 0 -- bug# 4007769  commented out
;

Line: 1691

    SELECT G_Yes
    FROM oke_deliverable_actions
    WHERE task_id = p_task_id
    AND action_type = 'REQ'
--    AND reference1 > 0 -- bug# 4007769  commented out
;