[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Maximum_BOM_Level
FROM bom_parameters
WHERE Organization_ID = P_ID;
SELECT '!' INTO Dummy
FROM mrp_schedule_items
WHERE Inventory_Item_Id = p_Item_Id
AND Schedule_designator = p_Plan
AND Organization_Id = p_Inventory_Org_Id;
MRP_SCHEDULE_ITEMS_PKG.Insert_Row(
X_Rowid => l_rowid,
X_Inventory_Item_Id => p_Item_Id,
X_Organization_Id => p_inventory_org_id,
X_Schedule_Designator => p_plan,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => fnd_global.user_id,
X_Creation_Date => sysdate,
X_Created_By => fnd_global.user_id,
X_Last_Update_Login => fnd_global.login_id,
X_MPS_Explosion_Level => L_Level
);
PROCEDURE INSERT_ROW (
p_Item_ID NUMBER,
p_Inv_Org_ID NUMBER,
p_Designator VARCHAR2,
p_Demand_Date DATE,
p_workdate DATE,
p_Primary_Qty NUMBER,
p_Project_ID NUMBER,
p_Task_ID NUMBER,
p_Unit_Number VARCHAR2,
p_deliverable_id NUMBER,
x_mps_transaction_id OUT NOCOPY NUMBER
) IS
l_id NUMBER;
SELECT MPS_TRANSACTION_ID FROM MRP_SCHEDULE_DATES
WHERE MPS_TRANSACTION_ID = l_id
AND SCHEDULE_LEVEL = L_Schedule_Level
AND SUPPLY_DEMAND_TYPE = L_Supply_Demand_Type;
SELECT mrp_schedule_dates_s.NEXTVAL FROM dual;
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 ,
p_Item_Id ,
p_Inv_Org_ID ,
p_Designator ,
p_Demand_Date ,
p_workdate ,
p_Primary_Qty ,
p_Primary_Qty ,
l_schedule_origination_type,
p_Project_Id ,
p_Task_Id ,
p_Unit_Number ,
'OKE' ,
P_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 (
p_Item_ID NUMBER,
p_Inv_Org_ID NUMBER,
p_Designator VARCHAR2,
p_Demand_Date DATE,
p_workdate DATE,
p_Primary_Qty NUMBER,
p_Project_ID NUMBER,
p_Task_ID NUMBER,
p_Unit_Number VARCHAR2,
p_deliverable_id NUMBER,
p_row_id ROWID
) IS
BEGIN
UPDATE MRP_SCHEDULE_DATES d
SET
LAST_UPDATE_DATE = Sysdate,
LAST_UPDATED_BY = Fnd_Global.User_Id,
LAST_UPDATE_LOGIN = Fnd_Global.Login_Id,
INVENTORY_ITEM_ID = p_Item_Id,
ORGANIZATION_ID = p_Inv_Org_Id,
SCHEDULE_DESIGNATOR = p_Designator,
SCHEDULE_DATE = p_Demand_Date,
SCHEDULE_WORKDATE = p_workdate,
ORIGINAL_SCHEDULE_QUANTITY = p_Primary_Qty,
SCHEDULE_QUANTITY = ( SELECT greatest(p_Primary_Qty - nvl(sum(R.relief_quantity) , 0) , 0)
FROM mrp_schedule_consumptions R
WHERE R.transaction_id = d.mps_transaction_id ),
PROJECT_ID = p_Project_Id,
TASK_ID = p_Task_Id,
END_ITEM_UNIT_NUMBER = p_Unit_Number
WHERE ROWID = p_row_id;
END update_row;
SELECT
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,
RATE_END_DATE,
SCHEDULE_QUANTITY,
ORIGINAL_SCHEDULE_QUANTITY,
REPETITIVE_DAILY_RATE,
SCHEDULE_ORIGINATION_TYPE,
SOURCE_FORECAST_DESIGNATOR,
REFERENCE_SCHEDULE_ID,
SCHEDULE_COMMENTS,
SOURCE_ORGANIZATION_ID,
SOURCE_SCHEDULE_DESIGNATOR,
SOURCE_SALES_ORDER_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
RESERVATION_ID,
FORECAST_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
DDF_CONTEXT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PROJECT_ID,
TASK_ID,
LINE_ID,
END_ITEM_UNIT_NUMBER
FROM MRP_SCHEDULE_DATES
WHERE MPS_TRANSACTION_ID = P_MPS_TRANSACTION_ID
AND SCHEDULE_LEVEL = 2
AND SUPPLY_DEMAND_TYPE = 1
FOR UPDATE OF MPS_TRANSACTION_ID NOWAIT;
Select Mps_Transaction_Id
, Item_Id
, Inventory_Org_Id
, Ndb_Schedule_Designator
, Expected_Shipment_Date
, Quantity
, Project_Id
, Task_Id
, Unit_Number
From oke_k_deliverables_b
Where Deliverable_Id = P_Deliverable_Id;
FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
Select rowid
, item_id
, inventory_org_id
, ndb_schedule_designator
, nvl(expected_shipment_date , need_by_date) demand_date
, quantity
, project_id
, task_id
, unit_number
, uom_code
, mps_transaction_id
From oke_k_deliverables_b
Where deliverable_id = p_deliverable_id
FOR UPDATE NOWAIT;
SELECT Primary_Uom_Code
FROM mtl_system_items
WHERE inventory_item_id = P_Item_ID
AND organization_id = P_Org_ID;
SELECT rowid,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SCHEDULE_DESIGNATOR
FROM mrp_schedule_dates
WHERE MPS_TRANSACTION_ID = l_MPS_TRANSACTION_ID
AND SCHEDULE_LEVEL = 2
AND SUPPLY_DEMAND_TYPE = 1
FOR UPDATE NOWAIT;
IF L_SRow_ID IS NOT NULL THEN -- update if MDS record exists
update_row(
p_Item_ID => l_Item_ID,
p_Inv_Org_ID => l_Inv_Org_ID,
p_Designator => l_Designator,
p_Demand_Date => l_Demand_Date,
p_workdate => l_workdate,
p_Primary_Qty => l_Primary_Qty ,
p_Project_ID => l_Project_ID,
p_Task_ID => l_Task_ID,
p_Unit_Number => l_Unit_Number,
P_Deliverable_Id => p_deliverable_id,
P_ROW_ID => L_SRow_ID
);
UPDATE oke_k_deliverables_b
SET po_ref_2 = 1
WHERE ROWID = L_DRow_ID;
ELSE -- if record wasn't updated - insert it
INSERT_ROW(
p_Item_ID => l_Item_ID,
p_Inv_Org_ID => l_Inv_Org_ID,
p_Designator => l_Designator,
p_Demand_Date => l_Demand_Date,
p_workdate => l_workdate,
p_Primary_Qty => l_Primary_Qty ,
p_Project_ID => l_Project_ID,
p_Task_ID => l_Task_ID,
p_Unit_Number => l_Unit_Number,
P_Deliverable_Id => P_Deliverable_Id,
X_Mps_Transaction_Id => L_mps_transaction_id
);
UPDATE oke_k_deliverables_b
SET mps_transaction_id = l_mps_transaction_id
WHERE ROWID = L_DRow_ID;
select kt1.term_value_pk1 term_value
from oke_k_deliverables_b d
, oke_k_terms kt1
, ( select cle_id , cle_id_ascendant , level_sequence from okc_ancestrys
union all
select id , id , 99999 from okc_k_lines_b ) a
where d.deliverable_id = C_deliverable_id
and kt1.term_code = C_term_code
and kt1.k_header_id = d.k_header_id
and a.cle_id = d.k_line_id
and ( ( kt1.k_line_id is null and a.cle_id = a.cle_id_ascendant )
or kt1.k_line_id = a.cle_id_ascendant )
order by decode(kt1.k_line_id , null , 0 , a.level_sequence) desc;
SELECT shipping_request_id, in_process_flag, initiate_shipment_date
FROM oke_k_deliverables_b
WHERE deliverable_id = p_id
FOR UPDATE OF shipping_request_id, in_process_flag, initiate_shipment_date NOWAIT;
SELECT B.K_HEADER_ID
, H.CUST_PO_NUMBER
, H.CONTRACT_NUMBER
, H.AUTHORING_ORG_ID
, B.DELIVERABLE_NUM
, B.PROJECT_ID
, B.TASK_ID
, B.QUANTITY
, B.ITEM_ID
, B.SHIP_TO_LOCATION_ID
, B.UOM_CODE
, B.EXPECTED_SHIPMENT_DATE
, B.PROMISED_SHIPMENT_DATE
, B.SHIP_FROM_LOCATION_ID
, B.INVENTORY_ORG_ID
, T.DESCRIPTION
, B.COUNTRY_OF_ORIGIN_CODE
, DECODE(B.INSPECTION_REQ_FLAG , 'Y' , 'R' , 'N') INSPECTION_REQ_FLAG
, B.UNIT_NUMBER
, B.CURRENCY_CODE
, B.WEIGHT
, B.WEIGHT_UOM_CODE
, B.VOLUME
, B.VOLUME_UOM_CODE
FROM OKC_K_HEADERS_B H
, OKE_K_DELIVERABLES_B B
, OKE_K_DELIVERABLES_TL T
WHERE B.DELIVERABLE_ID = p_id
AND B.DELIVERABLE_ID = T.DELIVERABLE_ID
AND T.LANGUAGE = USERENV('LANG')
AND H.ID = B.K_HEADER_ID;
SELECT Decode(MTL_Transactions_Enabled_Flag, 'Y', 'Y', 'N')
FROM mtl_system_items
WHERE Inventory_Item_ID = P_ID;
SELECT OPERATING_UNIT
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
select location_id into l_ship_rec.ship_to_location_id
from oke_cust_site_uses_v
where id1 = l_ship_to_location_id;
select cust_account_id into l_customer_id
from oke_cust_site_uses_v
where id1 = l_ship_to_location_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;
l_ship_rec.program_update_date := null;
update oke_k_deliverables_b
set shipping_request_id = l_id,
in_process_flag = 'Y',
initiate_shipment_date = sysdate
where CURRENT OF csr_dts_ship;
select employee_id
from fnd_user
where user_name = l_requestor;
select project_id
, task_id
, destination_type_code
, expenditure_type
, expenditure_organization_id
, expenditure_item_date
, inventory_org_id
, trunc(need_by_date)
, mps_transaction_id
, ndb_schedule_designator
, expected_shipment_date
, item_id
, unit_price
, exchange_rate
, ship_from_org_id
, currency_code
, ship_from_location_id
, requisition_line_type_id
, po_category_id
, quantity
, description
, uom_code
from oke_k_deliverables_vl
where deliverable_id = p_deliverable_id;
SELECT po_ref_1, in_process_flag
FROM oke_k_deliverables_b
WHERE deliverable_id = p_id
FOR UPDATE OF po_ref_1, in_process_flag NOWAIT;
select 'S'
from po_requisitions_interface_all
where oke_contract_deliverable_id = p_id
and nvl(process_flag, 'S') = 'ERROR'
and batch_id = p_batch_id;
select 'OKE_DTS_EXP_PROJECT_INVALID'
from dual
where not exists (
select 'Project is valid'
from pa_projects_expend_v
where project_id = l_project_id )
union all
select 'OKE_PROJECT_NOT_SETUP'
from dual
where not exists (
select 'Project valid for PJM'
from pjm_project_parameters
where organization_id = l_inv_org_id
and project_id = l_project_id )
union all
select 'OKE_DTS_EXP_TASK_INVALID'
from dual
where not exists (
select 'Task valid and chargeable'
from pa_tasks_expend_v t
where project_id = l_project_id
and task_id = l_task_id
and chargeable_flag = 'Y' )
union all
select 'OKE_TASK_REQUIRED'
from dual
where not exists (
select 'Task Reference OK'
from pjm_org_parameters
where organization_id = l_inv_org_id
and not ( project_control_level = 2
and l_task_id is null )
);
select 'OKE_DTS_EXP_PROJECT_INVALID'
from dual
where not exists (
select 'Project is valid'
from pa_projects_expend_v
where project_id = l_project_id )
union all
select 'OKE_DTS_EXP_TASK_INVALID'
from dual
where not exists (
select 'Task is valid and chargeable'
from pa_tasks_expend_v
where project_id = l_project_id
and task_id = l_task_id
and chargeable_flag = 'Y' )
union all
select 'OKE_DTS_EXP_DATE_INVALID'
from dual
where not exists (
select 'Date is valid for task'
from pa_tasks t
, pa_projects_all p
where t.project_id = l_project_id
and t.task_id = l_task_id
and p.project_id = t.project_id
and l_exp_item_date
between nvl(t.start_date , nvl(p.start_date , l_exp_item_date - 1))
and nvl(t.completion_date , nvl(p.completion_date , l_exp_item_date + 1)) )
union all
select 'OKE_DTS_EXP_TYPE_INVALID'
from dual
where not exists (
select 'Expenditure Type exists and is valid'
from pa_expenditure_types_expend_v
where expenditure_type = l_exp_type
and system_linkage_function = 'VI' )
union all
select 'OKE_DTS_EXP_ORG_INVALID'
from dual
where not exists (
select 'Expenditure Org exists and is valid'
from pa_organizations_expend_v
where organization_id = l_exp_org_id );
SELECT gl.Currency_Code
FROM gl_sets_of_books gl, org_organization_definitions org
WHERE org.organization_id = P_ID
AND gl.Set_Of_Books_ID = org.Set_Of_Books_ID;
SELECT Buy_Or_Sell
FROM okc_k_headers_b
WHERE ID = (SELECT K_Header_ID FROM oke_k_deliverables_b WHERE Deliverable_ID = P_Deliverable_ID);
select oke_interface_s.nextval
into l_id
from dual;
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,
d.item_id,
l_quantity,
l_unit_price,
l_need_by_date,
'OKE', -- hard code for OKE
d.ship_to_location_id,
l_employee_id,
l_employee_id,
'VENDOR',
decode(d.destination_type_code, 'INVENTORY', 'APPROVED','INCOMPLETE'),
l_uom_code,
l_id,
decode(d.destination_type_code, 'INVENTORY',mp.material_account, p_charge_account),
null, -- to be added later if required
d.inventory_org_id,
'N', -- hard coded
ood.operating_unit,
l_project_id,
l_task_id,
l_context,
d.k_header_id,
ver.major_version,
d.k_line_id,
d.deliverable_id,
d.unit_number,
l_exp_org_id,
l_exp_type,
l_exp_item_date,
l_dest_type,
nvl(d.currency_code,l_func_currency_code),
d.exchange_rate,
d.rate_date,
d.rate_type,
l_currency_price,
l_vendor_id,
l_vendor_site_id,
l_req_line_type_id,
l_category_id,
l_item_description
from oke_k_deliverables_b d
, okc_k_headers_b h
, mtl_parameters mp
, org_organization_definitions ood
, oke_k_vers_numbers_v ver
where d.deliverable_id = p_deliverable_id
and h.id = d.k_header_id
and ver.chr_id = d.k_header_id
and ood.organization_id = d.inventory_org_id
and mp.organization_id = d.inventory_org_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Req created successfully, update deliverable table');
update oke_k_deliverables_b
set po_ref_1 = l_id
, in_process_flag = 'Y'
where CURRENT OF csr_dts_req;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Update MDS');
SELECT H.k_number_disp k_number
, T.k_type_name doc_type
, L.line_number line_number
, D.deliverable_num deliverable_num
, D.description description
, h.authoring_org_id org_id
--bug 7390122 changed oke_k_deliverables_b to oke_k_deliverables_vl as it has description
FROM oke_k_deliverables_vl D
, okc_k_lines_b L
, oke_k_headers_v H
, oke_k_types_vl T
WHERE D.deliverable_id = p_id
AND L.id = D.k_line_id
AND H.k_header_id = L.dnz_chr_id
AND T.k_type_code = H.k_type_code;
SELECT D.destination_type_code destination_type
, D.expenditure_type
, D.expenditure_item_date
, O.name expenditure_org
, D.need_by_date
FROM oke_k_deliverables_b D
, hr_all_organization_units_tl O
WHERE D.deliverable_id = p_id
AND O.organization_id (+) = D.expenditure_organization_id
AND O.language (+) = userenv('LANG');
SELECT D.ship_from_location_id
, L.location_code ship_from_location
FROM oke_k_deliverables_b D
, hr_locations_all_tl L
WHERE D.deliverable_id = p_id
AND L.location_id = D.ship_from_location_id
AND L.language = userenv('LANG');
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;
SELECT k_header_id, uom_code, shipping_request_id
FROM oke_k_deliverables_b
where deliverable_id = p_deliverable_id;
SELECT Nvl(Sum(CANCELLED_QUANTITY),0)
FROM wsh_delivery_details
where source_code = 'OKE'
AND SOURCE_line_ID = p_deliverable_id
START WITH DELIVERY_DETAIL_ID=l_shipping_request_id
CONNECT BY PRIOR DELIVERY_DETAIL_ID = SPLIT_FROM_DELIVERY_DETAIL_ID;
WSH_INTERFACE.Update_Shipping_Attributes (
p_source_code => 'OKE',
p_changed_attributes => l_changed_attributes,
x_return_status => X_RETURN_STATUS
);