[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT borrow_transaction_id
, outstanding_quantity
FROM pjm_borrow_transactions
WHERE decode(C_proj_ctrl_level, 1, borrow_project_id, borrow_task_id) =
decode(C_proj_ctrl_level, 1, C_borrow_proj_id, C_borrow_task_id)
AND decode(C_proj_ctrl_level, 1, lending_project_id, lending_task_id) =
decode(C_proj_ctrl_level, 1, C_lending_proj_id, C_lending_task_id)
AND inventory_item_id = C_item_id
AND organization_id = C_organization_id
AND outstanding_quantity > 0
ORDER BY loan_date ASC, borrow_transaction_id ASC
FOR UPDATE OF outstanding_quantity;
SELECT transaction_type_id
, primary_quantity * (-1)
, project_id
, task_id
, to_project_id
, to_task_id
, inventory_item_id
, revision
, organization_id
INTO L_trx_type_id
, L_trx_quantity
, L_proj_id
, L_task_id
, L_to_proj_id
, L_to_task_id
, L_item_id
, L_revision
, L_organization_id
FROM mtl_material_transactions
WHERE transaction_id = X_transaction_id;
select project_control_level
into L_proj_ctrl_level
from mtl_parameters
where organization_id = L_organization_id;
SELECT scheduled_payback_date
INTO L_payback_date
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = X_transaction_temp_id;
INSERT INTO pjm_borrow_transactions
( borrow_transaction_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, borrow_project_id
, borrow_task_id
, lending_project_id
, lending_task_id
, organization_id
, inventory_item_id
, revision
, loan_quantity
, outstanding_quantity
, loan_date
, scheduled_payback_date )
SELECT transaction_id
, sysdate
, created_by
, sysdate
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, sysdate
, to_project_id
, decode(L_proj_ctrl_level, 2, to_task_id, NULL)
, project_id
, decode(L_proj_ctrl_level, 2, task_id, NULL)
, organization_id
, inventory_item_id
, revision
, (-1) * primary_quantity
, (-1) * primary_quantity
, transaction_date
, L_payback_date
FROM mtl_material_transactions
WHERE transaction_id = X_transaction_id;
SELECT sum(outstanding_quantity)
INTO L_outstanding_qty
FROM pjm_borrow_transactions
WHERE decode(L_proj_ctrl_level, 1, borrow_project_id, borrow_task_id) =
decode(L_proj_ctrl_level, 1, L_proj_id, L_task_id)
AND decode(L_proj_ctrl_level, 1, lending_project_id, lending_task_id) =
decode(L_proj_ctrl_level, 1, L_to_proj_id, L_to_task_id)
AND inventory_item_id = L_item_id
AND organization_id = L_organization_id
AND outstanding_quantity > 0;
INSERT INTO pjm_borrow_paybacks
( payback_transaction_id
, borrow_transaction_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, payback_quantity
, borrow_project_id
, borrow_task_id
, lending_project_id
, lending_task_id )
SELECT transaction_id
, L_borrow_trx_id
, sysdate
, created_by
, sysdate
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, sysdate
, L_payback_qty
, project_id
, decode(L_proj_ctrl_level, 2, task_id, NULL)
, to_project_id
, decode(L_proj_ctrl_level, 2, to_task_id, NULL)
FROM mtl_material_transactions
WHERE transaction_id = X_transaction_id;
UPDATE pjm_borrow_transactions
SET outstanding_quantity = outstanding_quantity - L_payback_qty
, last_update_date = sysdate
WHERE borrow_transaction_id = L_borrow_trx_id;
select outstanding_quantity
from pjm_borrow_transactions
where decode(c_project_control_lev, 1, lending_project_id,
lending_task_id) =
decode(c_project_control_lev, 1, c_to_project_id, c_to_task_id)
and decode(c_project_control_lev, 1, borrow_project_id,
borrow_task_id) =
decode(c_project_control_lev, 1, c_from_project_id, c_from_task_id)
and organization_id = C_Organization_Id
and inventory_item_id = C_Inventory_Item_Id
order by loan_date DESC;
select nvl(inventory_asset_flag, 'N')
into l_asset_item_flag
from mtl_system_items
where organization_id = X_Organization_ID
and inventory_item_id = X_Inventory_Item_Id;
select 'N'
into l_asset_inventory
from mtl_secondary_inventories
where organization_id = X_Organization_Id
and (secondary_inventory_name = X_From_Subinventory
or secondary_inventory_name = X_To_Subinventory)
and asset_inventory = 2;
select project_id, task_id,
physical_location_id
into l_from_project_id, l_from_task_id,
l_from_physical_loc
from mtl_item_locations
where organization_id = X_Organization_Id
and inventory_location_id = X_From_Locator_Id ;
select project_id, task_id,
physical_location_id
into l_to_project_id, l_to_task_id,
l_to_physical_loc
from mtl_item_locations
where organization_id = X_Organization_Id
and inventory_location_id = X_To_Locator_Id;
select project_control_level
into l_project_control_lev
from pjm_org_parameters
where organization_id = X_Organization_Id;
select sum(moq.transaction_quantity)
into l_onhand_qty
from mtl_onhand_quantities_detail moq,
mtl_item_locations mil
where mil.project_id = l_from_project_id
and mil.task_id = l_from_task_id
and mil.subinventory_code = X_From_Subinventory
and mil.organization_id = X_Organization_Id
and mil.inventory_location_id = moq.locator_id
and mil.organization_id = moq.organization_id
and mil.subinventory_code = moq.subinventory_code
and moq.inventory_item_id = X_Inventory_Item_Id;
select nvl(sum(outstanding_quantity),0)
into l_outstanding_qty
from pjm_borrow_transactions
where decode(l_project_control_lev, 1, lending_project_id,
lending_task_id) =
decode(l_project_control_lev, 1, l_to_project_id, l_to_task_id)
and decode(l_project_control_lev, 1, borrow_project_id,
borrow_task_id) =
decode(l_project_control_lev, 1, l_from_project_id, l_from_task_id)
and organization_id = X_Organization_Id
and Inventory_item_id = X_Inventory_Item_Id;