[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO Dummy
FROM dual
WHERE NOT EXISTS (
SELECT 1
FROM mrp_schedule_items mrp
, oke_deliverables_b oke
, oke_deliverable_actions oka
WHERE oka.action_id = p_action_id
AND oke.deliverable_id = oka.deliverable_id
AND mrp.inventory_item_id = oke.item_id
AND mrp.schedule_designator = oka.schedule_designator
AND mrp.organization_id = oka.ship_from_org_id);
PROCEDURE Delete_Row ( P_Action_ID NUMBER ) IS
L_ID NUMBER;
SELECT reference2
FROM oke_deliverable_actions
WHERE action_id = p_action_id;
DELETE FROM mrp_schedule_dates
WHERE mps_transaction_id = l_id;
END Delete_Row;
PROCEDURE Insert_Row ( P_Action_ID NUMBER
, X_Mps_Transaction_ID OUT NOCOPY NUMBER ) IS
L_ID NUMBER;
SELECT rowid
FROM mrp_schedule_dates
WHERE mps_transaction_id = p_transaction_id
AND schedule_level = l_schedule_level
AND supply_demand_type = l_supply_demand_type;
SELECT Maximum_BOM_Level
FROM bom_parameters
WHERE organization_id = p_organization_id;
SELECT b.item_id
, c.ship_from_org_id
, c.schedule_designator
, c.expected_date
, b.uom_code
, b.quantity
, b.project_id
, c.task_id
, b.unit_number
, c.deliverable_id
FROM oke_deliverables_b b
, oke_deliverable_actions c
WHERE c.action_id = p_action_id
AND b.deliverable_id = c.deliverable_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT mrp_schedule_dates_s.nextval
INTO l_id
FROM dual;
INSERT INTO mrp_schedule_items (
inventory_item_id
, organization_id
, schedule_designator
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, mps_explosion_level )
SELECT b.item_id
, c.ship_from_org_id
, c.schedule_designator
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, d.maximum_bom_level
FROM oke_deliverables_b b
, oke_deliverable_actions c
, bom_parameters d
WHERE c.action_id = p_action_id
AND c.deliverable_id = b.deliverable_id
AND c.ship_from_org_id = d.organization_id;
INSERT INTO MRP_SCHEDULE_DATES(
MPS_TRANSACTION_ID,
SCHEDULE_LEVEL,
SUPPLY_DEMAND_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SCHEDULE_DESIGNATOR,
SCHEDULE_DATE,
SCHEDULE_WORKDATE,
SCHEDULE_QUANTITY,
ORIGINAL_SCHEDULE_QUANTITY,
SCHEDULE_ORIGINATION_TYPE,
PROJECT_ID,
TASK_ID,
END_ITEM_UNIT_NUMBER,
SOURCE_CODE,
SOURCE_LINE_ID)
SELECT
L_Id ,
lu.lookup_code ,
L_Supply_Demand_Type ,
Sysdate ,
Fnd_Global.User_Id ,
Sysdate ,
Fnd_Global.User_Id ,
Fnd_Global.Login_Id ,
c3info.Item_Id ,
c3info.Ship_From_Org_Id ,
c3info.Schedule_Designator ,
c3info.Expected_Date ,
l_workdate ,
L_Primary_Qty ,
L_Primary_Qty ,
l_schedule_origination_type,
c3info.Project_Id ,
c3info.Task_Id ,
c3info.Unit_Number ,
'OKE' ,
c3info.deliverable_id
FROM mfg_lookups lu
WHERE lookup_type = 'MRP_SCHEDULE_LEVEL'
AND lookup_code in ( 1 , 2)
AND NOT EXISTS (
SELECT NULL
FROM mrp_schedule_dates
WHERE mps_transaction_id = L_Id
AND schedule_level = lu.lookup_code );
END insert_row;
PROCEDURE update_row ( X_Mps_Transaction_Id IN OUT NOCOPY NUMBER
, P_Action_Id IN NUMBER
) IS
CURSOR c IS
SELECT b.item_id
, c.ship_from_org_id
, c.schedule_designator
, c.expected_date
, b.quantity
, b.project_id
, c.task_id
, b.unit_number
, b.uom_code
, mrp_calendar.prev_work_day(c.ship_from_org_id
, 1
, c.expected_date) workdate
, d.primary_uom_code
FROM oke_deliverables_b b
, oke_deliverable_actions c
, mtl_system_items d
WHERE c.action_id = p_action_id
AND b.deliverable_id = c.deliverable_id
AND d.inventory_item_id = b.item_id
AND d.organization_id = c.ship_from_org_id;
UPDATE MRP_SCHEDULE_DATES
SET
LAST_UPDATE_DATE = Sysdate,
LAST_UPDATED_BY = Fnd_Global.User_Id,
LAST_UPDATE_LOGIN = Fnd_Global.Login_Id,
INVENTORY_ITEM_ID = cinfo.Item_Id,
ORGANIZATION_ID = cinfo.Ship_From_Org_Id,
SCHEDULE_DESIGNATOR = cinfo.Schedule_Designator,
SCHEDULE_DATE = cinfo.Expected_Date,
SCHEDULE_WORKDATE = cinfo.Workdate,
SCHEDULE_QUANTITY = ( select greatest(L_Primary_Qty - nvl(sum(ref.relief_quantity) , 0) , 0)
from mrp_schedule_consumptions ref
where ref.transaction_id = x_mps_transaction_id ),
PROJECT_ID = cinfo.Project_Id,
TASK_ID = cinfo.Task_Id,
END_ITEM_UNIT_NUMBER = cinfo.Unit_Number
WHERE MPS_TRANSACTION_ID = X_MPS_TRANSACTION_ID
AND SCHEDULE_LEVEL = 2
AND SUPPLY_DEMAND_TYPE = 1;
insert_row( P_Action_ID => p_action_id
, X_MPS_Transaction_ID => x_mps_transaction_id );
END update_row;
SELECT reference2
FROM oke_deliverable_actions
WHERE action_id = p_action_id
FOR UPDATE NOWAIT;
SELECT schedule_quantity
FROM mrp_schedule_dates
WHERE mps_transaction_id = l_id;
Insert_Row ( P_Action_ID => P_Action_ID
, X_Mps_Transaction_ID => L_ID );
Update_Row ( P_Action_ID => P_Action_ID
, X_Mps_Transaction_ID => L_ID );
UPDATE oke_deliverable_actions
SET reference2 = L_ID
WHERE action_id = p_action_id
AND action_type = 'WSH';
SELECT b.source_header_id
, b.source_deliverable_id
, b.deliverable_id
, b.project_id
, b.item_id
, nvl(b.quantity, c.quantity) quantity
, nvl(b.uom_code, c.uom_code) uom_code
, b.inventory_org_id
, c.pa_action_id
, c.ship_from_org_id
, c.ship_to_org_id
, c.ship_to_location_id
, c.ship_from_location_id
, c.expected_date
, c.promised_date
, b.unit_number
, decode ( c.inspection_req_flag, 'Y', 'R', 'N') inspection_req_flag
, c.volume
, c.volume_uom_code
, c.weight
, c.weight_uom_code
, nvl(b.currency_code, c.currency_code) currency_code
, c.task_id
FROM oke_deliverables_b b
, oke_deliverable_actions c
WHERE c.action_id = p_action_id
AND b.deliverable_id = c.deliverable_id;
SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id = p_org_id;
SELECT id1, cust_account_id
FROM oke_cust_site_uses_v
WHERE location_id = p_location_id
AND site_use_code = 'SHIP_TO';
SELECT MTL_Transactions_Enabled_Flag
FROM mtl_system_items
where organization_id = p_org_id
and inventory_item_id = p_item_id;
l_ship_rec.last_update_date := sysdate;
l_ship_rec.last_update_login := fnd_global.login_id;
l_ship_rec.last_updated_by := fnd_global.user_id;
UPDATE oke_deliverable_actions
SET reference1 = l_id
, in_process_flag = 'Y'
, initiate_date = sysdate
WHERE action_id = p_action_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = l_requestor;
SELECT b.source_header_id
, c.task_id
, c.destination_type_code
, c.expenditure_type
, c.expenditure_organization_id
, c.expenditure_item_date
, nvl(b.inventory_org_id,c.ship_to_org_id) inventory_org_id
, trunc ( c.expected_date ) expected_date
, c.reference1
, c.reference2
, c.schedule_designator
, b.item_id
, nvl(b.unit_price, c.unit_price) unit_price
, c.exchange_rate
, c.ship_from_org_id
, nvl(b.currency_code, c.currency_code) currency_code
, c.ship_from_location_id
, c.requisition_line_type_id
, c.po_category_id
, nvl(b.quantity, c.quantity) quantity
, nvl(b.uom_code, c.uom_code) uom_code
, c.pa_action_id
, c.ship_to_location_id
, c.deliverable_id
, c.action_id
, b.unit_number
, c.rate_date
, c.rate_type
FROM oke_deliverables_b b
, oke_deliverable_actions c
WHERE c.action_id = p_action_id
AND b.deliverable_id = c.deliverable_id;
DELETE FROM po_requisitions_interface_all
WHERE oke_contract_deliverable_id = c2info.action_id
AND batch_id = c2info.reference1;
INSERT INTO po_requisitions_interface_all(
last_updated_by,
last_update_login,
last_update_date,
creation_date,
created_by,
item_id,
quantity,
unit_price,
need_by_date,
interface_source_code,
deliver_to_location_id,
deliver_to_requestor_id,
preparer_id,
source_type_code,
authorization_status,
uom_code,
batch_id,
charge_account_id,
group_code,
destination_organization_id,
autosource_flag,
org_id,
project_id,
task_id,
project_accounting_context,
oke_contract_header_id,
oke_contract_version_id,
oke_contract_line_id,
oke_contract_deliverable_id,
end_item_unit_number,
expenditure_organization_id,
expenditure_type,
expenditure_item_date,
destination_type_code,
currency_code,
rate,
rate_date,
rate_type,
currency_unit_price,
suggested_vendor_id,
suggested_vendor_site_id,
line_type_id,
category_id,
item_description)
select fnd_global.user_id,
fnd_global.login_id,
sysdate,
sysdate,
fnd_global.user_id,
c2info.item_id,
c2info.quantity,
c2info.unit_price * Nvl(c2info.exchange_rate,1), -- bug#4189882
c2info.expected_date,
'OKE', -- hard code for OKE
c2info.ship_to_location_id,
l_employee,
l_employee,
'VENDOR',
decode(c2info.destination_type_code, 'INVENTORY', 'APPROVED','INCOMPLETE'),
c2info.uom_code,
p_action_id,
decode(c2info.destination_type_code, 'INVENTORY',mp.material_account, l_charge_account),
null, -- to be added later if required
c2info.inventory_org_id,
'N', -- hard coded
ood.operating_unit,
c2info.source_header_id,
c2info.task_id,
l_context,
c2info.source_header_id,
null,
c2info.deliverable_id,
c2info.action_id,
c2info.unit_number,
c2info.expenditure_organization_id,
c2info.expenditure_type,
c2info.expenditure_item_date,
c2info.destination_type_code,
c2info.currency_code,
c2info.exchange_rate,
c2info.rate_date,
c2info.rate_type,
c2info.unit_price,
c2info.ship_from_org_id,
c2info.ship_from_location_id,
c2info.requisition_line_type_id,
c2info.po_category_id,
l_description
from mtl_parameters mp
, org_organization_definitions ood
where ood.organization_id = c2info.inventory_org_id
and mp.organization_id = c2info.inventory_org_id;
UPDATE oke_deliverable_actions
SET reference1 = p_action_id
, in_process_flag = 'Y'
WHERE action_id = p_action_id;
SELECT Expense_Account
FROM mtl_system_items
WHERE Inventory_Item_ID = P_Item_ID
AND Organization_ID = P_Org_ID;
SELECT Expense_Account
FROM mtl_parameters
WHERE ORGANIZATION_ID = P_Org_ID;
PROCEDURE Delete_Action ( P_Action_ID NUMBER ) IS
L_Action_ID NUMBER;
SELECT action_id
, reference2
FROM oke_deliverable_actions
WHERE pa_action_id = p_action_id;
Delete_Row ( L_Action_ID );
DELETE FROM oke_deliverable_actions
WHERE action_id = l_action_id;
END Delete_Action;
PROCEDURE Delete_Deliverable ( P_Deliverable_ID NUMBER ) IS
L_Deliverable_ID NUMBER;
SELECT deliverable_id
FROM oke_deliverables_b
WHERE source_deliverable_id = p_deliverable_id;
SELECT pa_action_id
FROM oke_deliverable_actions
WHERE deliverable_id = l_deliverable_id;
Delete_Action ( c_rec.pa_action_id );
DELETE FROM oke_deliverables_tl
WHERE deliverable_id = l_deliverable_id;
DELETE FROM oke_deliverables_b
WHERE deliverable_id = l_deliverable_id;
END Delete_Deliverable;