[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_DEL CONSTANT VARCHAR2(10) := 'DELETE';
G_UPD CONSTANT VARCHAR2(10) := 'UPDATE';
Select name from pa_projects_all where project_id=p_project_id;
Select deliverable_number from oke_deliverables_b
where deliverable_id = p_deliverable_id;
select 'x' from mtl_parameters
where master_organization_id=b_master_org_id
and organization_id=b_inv_org_id;
select 'x' from oke_system_items_v
where id1=b_item_id and id2=b_inv_org_id;
select 'x' from fnd_currencies
where currency_code=b_currency_code
and enabled_flag='Y'
and currency_flag='Y'
and sysdate >= nvl(start_date_active,sysdate)
and sysdate <= nvl(end_date_active,sysdate);
select 'x' from mtl_item_uoms_view
where inventory_item_id=b_item_id
and uom_code=b_uom;
select 'x' from pjm_unit_numbers_lov_v
where end_item_id=b_item_id
and unit_number=b_unit_number;
select primary_uom_code from oke_system_items_v
where inventory_item_id=b_item_id;
Select 'x' from fnd_currencies b
where enabled_flag = 'Y'
and currency_flag = 'Y'
and b.currency_code = p_currency_code
and sysdate >= nvl (b.start_date_active, sysdate)
and sysdate <= nvl (b.end_date_active, sysdate);
Select 'x' from org_organization_definitions
where nvl(inventory_enabled_flag, 'N') = 'Y'
and nvl(disable_date, sysdate) >= sysdate
and organization_id= p_ship_to_org_id;
Select 'x' from okx_locations_v ocv
where organization_id = p_ship_to_org_id
and id1= p_ship_to_loc_id;
Select 'x' FROM PO_VENDORS
where sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate)
and vendor_id= p_ship_from_org_id;
Select 'x' from po_supplier_sites_val_v
where nvl(rfq_only_site_flag, 'N') = 'N'
and vendor_id =p_ship_from_org_id
and vendor_site_id= p_ship_from_loc_id;
Select 'x' from gl_daily_conversion_types
where conversion_type= p_exchange_rate_type;
Select 'x' from pa_expenditure_types_expend_v et
where system_linkage_function = 'VI'
and et.project_id = (select project_id from oke_deliverables_b
where deliverable_id=p_deliverable_id)
and expenditure_type = p_expenditure_type
union
select 'x' from pa_expenditure_types_expend_v et
where system_linkage_function = 'VI'
and et.project_id is null
and expenditure_type = p_expenditure_type;
Select 'x' from pa_organizations_expend_v o
where active_flag = 'Y'
and trunc(sysdate) between o.date_from and nvl(o.date_to, trunc(sysdate))
and organization_id= p_expenditure_org_id;
Select 'x' from po_line_types
where order_type_lookup_code = 'AMOUNT'
and line_type_id= p_requisition_line_type_id;
select inventory_org_id from oke_deliverables_b
where deliverable_id=p_deliverable_id;
Select 'x' from mrp_designators_view v, oke_deliverables_b b
where v.designator_type = 1
and nvl( v.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
and b.inventory_org_id = v.organization_id
and b.deliverable_id=p_deliverable_id
and v.designator = p_demand_schedule;
Select 'x' from org_organization_definitions
where nvl(inventory_enabled_flag, 'N') = 'Y'
and nvl(disable_date, sysdate) >= sysdate
and organization_id= p_ship_from_org_id;
Select 'x' from okx_locations_v ocv
where organization_id = p_ship_from_org_id
and id1= p_ship_from_loc_id;
Select 'x' from oke_customer_accounts_v
where id1= p_ship_to_org_id ;
Select 'x' from oke_cust_site_uses_v
where site_use_code = 'SHIP_TO'
and cust_account_id = p_ship_to_org_id
and location_id= p_ship_to_loc_id;
Select 'x' from mtl_units_of_measure uom,
wsh_shipping_parameters wsp
where wsp.organization_id =p_ship_from_org_id
and uom.uom_class = wsp.volume_uom_class
and sysdate < nvl(disable_date, sysdate + 1)
and uom_code= p_volume_uom;
Select 'x' from mtl_units_of_measure uom,
wsh_shipping_parameters wsp
where wsp.organization_id =p_ship_from_org_id
and uom.uom_class = wsp.weight_uom_class
and sysdate < nvl(disable_date, sysdate + 1)
and uom_code= p_weight_uom;
Select 'x' from mtl_units_of_measure uom
where sysdate < nvl(disable_date, sysdate + 1)
and uom_code= p_uom_code;
Select 'x' from mtl_categories_b
where category_id =p_category_id;
Select nvl(p_currency_code,currency_code) into l_currency
from oke_deliverables_b
where deliverable_id=p_deliverable_id;
SELECT
deliverable_id from oke_deliverables_b
where project_id=p_dlv_rec.project_id
and source_deliverable_id=p_dlv_rec.pa_deliverable_id;
SELECT 'x'
from oke_deliverable_actions
where deliverable_id= b_del_id
and reference1 > 0;
select oke_k_deliverables_s.nextval into l_deliverable_id from dual;
OKE_DELIVERABLES_PKG.insert_row(
X_ROWID => l_row_id,
X_DELIVERABLE_ID => l_deliverable_id,
X_DELIVERABLE_NUMBER => p_dlv_rec.dlv_short_name,
X_SOURCE_CODE => G_SOURCE_CODE,
X_UNIT_PRICE => p_dlv_rec.unit_price,
X_UOM_CODE => p_dlv_rec.uom_code,
X_QUANTITY => p_dlv_rec.quantity,
X_UNIT_NUMBER => p_dlv_rec.unit_number,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_SOURCE_HEADER_ID => p_dlv_rec.project_id,
X_SOURCE_LINE_ID => null,
X_SOURCE_DELIVERABLE_ID => p_dlv_rec.pa_deliverable_id,
X_PROJECT_ID => p_dlv_rec.project_id,
X_CURRENCY_CODE => p_dlv_rec.currency_code,
X_INVENTORY_ORG_ID => p_dlv_rec.inventory_org_id,
X_DELIVERY_DATE => NULL,
X_ITEM_ID => p_dlv_rec.item_id,
X_DESCRIPTION => p_dlv_rec.dlv_description,
X_COMMENTS => Null,
X_CREATION_DATE => sysdate,
X_CREATED_BY => Fnd_Global.User_Id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => Fnd_Global.User_Id,
X_LAST_UPDATE_LOGIN => Fnd_Global.login_id
);
update OKE_DELIVERABLES_B
set
CURRENCY_CODE = p_dlv_rec.currency_code,
UNIT_PRICE = p_dlv_rec.unit_price,
UOM_CODE = p_dlv_rec.uom_code,
QUANTITY = p_dlv_rec.quantity,
UNIT_NUMBER = p_dlv_rec.unit_number,
DELIVERABLE_NUMBER= p_dlv_rec.dlv_short_name,
PROJECT_ID = p_dlv_rec.project_id,
ITEM_ID = p_dlv_rec.item_id,
SOURCE_HEADER_ID = p_dlv_rec.project_id,
INVENTORY_ORG_ID = p_dlv_rec.inventory_org_id,
SOURCE_CODE = 'PA',
SOURCE_DELIVERABLE_ID = p_dlv_rec.pa_deliverable_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_globaL.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
where DELIVERABLE_ID = l_deliverable_id;
update OKE_DELIVERABLES_TL set
DESCRIPTION = p_dlv_rec.dlv_description,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_globaL.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id,
SOURCE_LANG = userenv('LANG')
where DELIVERABLE_ID = l_deliverable_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update OKE_DELIVERABLE_actions
set
CURRENCY_CODE = decode(action_type,G_REQ,p_dlv_rec.currency_code,currency_code),
UNIT_PRICE = decode(action_type,G_REQ,p_dlv_rec.unit_price,unit_price),
UOM_CODE = p_dlv_rec.uom_code,
QUANTITY = p_dlv_rec.quantity,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_globaL.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id
where DELIVERABLE_ID = l_deliverable_id;
OKE_DELIVERABLE_ACTIONS_PKG.delete_deliverable(p_dlv_rec.pa_deliverable_id);
Select act.action_id,
act.deliverable_id,
del.project_id
from oke_deliverable_actions act , oke_deliverables_b del
where act.deliverable_id = del.deliverable_id
and pa_action_id= b_pa_action_id;
Select deliverable_id,
project_id
from oke_deliverables_b
where source_deliverable_id = b_pa_deliverable_id;
Select 'x' from oke_deliverable_actions
where action_id=b_action_id and reference1>0;
select inventory_org_id from oke_deliverables_b where deliverable_id=b_deliverable_id;
select uom_code,currency_code,unit_price,quantity,inventory_org_id from oke_deliverables_b
where deliverable_id=b_dlv_id ;
Select decode(p_dlv_action_type,G_SHIP,p_dlv_ship_action_rec.pa_action_id,G_REQ, p_dlv_req_action_rec.pa_action_id,NULL),
decode(p_dlv_action_type,G_SHIP,p_dlv_ship_action_rec.pa_deliverable_id,G_REQ, p_dlv_req_action_rec.pa_deliverable_id,NULL),
decode(p_dlv_action_type,G_SHIP,p_dlv_ship_action_rec.action_name,G_REQ, p_dlv_req_action_rec.action_name,NULL)
into l_pa_action_id,l_pa_deliverable_id,l_action_name from dual;
OKE_DELIVERABLE_ACTIONS_PKG.Delete_action(l_pa_action_id);
Select oke_k_deliverables_S.nextval into l_action_id from dual;
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 ,
TASK_ID ,
DELIVERABLE_ID,
SHIP_TO_ORG_ID ,
SHIP_TO_LOCATION_ID,
SHIP_FROM_ORG_ID ,
SHIP_FROM_LOCATION_ID,
INSPECTION_REQ_FLAG,
EXPECTED_DATE ,
PROMISED_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,
quantity,
uom_code,
unit_price,
currency_code)
Values(l_ACTION_ID,
sysdate,
fnd_global.user_id ,
sysdate,
fnd_global.user_id ,
fnd_global.login_id,
p_dlv_action_type ,
l_ACTION_NAME ,
l_PA_ACTION_ID ,
l_TASK_ID ,
l_DELIVERABLE_ID,
l_SHIP_TO_ORG_ID ,
l_SHIP_TO_LOC_ID,
l_SHIP_FROM_ORG_ID ,
l_SHIP_FROM_LOC_ID,
l_INSPECTION_REQ_FLAG,
decode(p_dlv_action_type,G_REQ,l_po_need_by_date,G_SHIP,l_expected_shipment_date),
l_PROMISED_shipment_DATE ,
l_demand_SCHEDULE,
l_VOLUME ,
l_VOLUME_UOM,
l_WEIGHT ,
l_WEIGHT_UOM,
l_EXPENDITURE_ORG_ID,
l_EXPENDITURE_TYPE ,
l_EXPENDITURE_ITEM_DATE,
l_DESTINATION_TYPE_CODE ,
l_exchange_RATE_TYPE ,
l_exchange_rate_date ,
l_EXCHANGE_RATE,
l_REQUISITION_LINE_TYPE_ID,
l_CATEGORY_ID,
l_quantity,
l_uom_code,
l_unit_price,
l_currency_code);
If p_action='UPDATE' then
update oke_deliverable_actions set
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_LOGIN = fnd_global.login_id,
ACTION_TYPE = p_dlv_action_type ,
ACTION_NAME = l_ACTION_NAME,
PA_ACTION_ID = l_PA_ACTION_ID,
TASK_ID = l_TASK_ID,
DELIVERABLE_ID = l_DELIVERABLE_ID,
SHIP_TO_ORG_ID = l_SHIP_TO_ORG_ID,
SHIP_TO_LOCATION_ID = l_SHIP_TO_LOC_ID,
SHIP_FROM_ORG_ID = l_SHIP_FROM_ORG_ID,
SHIP_FROM_LOCATION_ID = l_SHIP_FROM_LOC_ID,
INSPECTION_REQ_FLAG = l_INSPECTION_REQ_FLAG,
EXPECTED_DATE = decode(p_dlv_action_type,G_REQ,l_po_need_by_date,G_SHIP,l_expected_shipment_date),
PROMISED_DATE = l_promised_shipment_date,
SCHEDULE_DESIGNATOR = l_demand_schedule,
VOLUME = l_VOLUME,
VOLUME_UOM_CODE = l_VOLUME_UOM,
WEIGHT = l_WEIGHT,
WEIGHT_UOM_CODE = l_WEIGHT_UOM,
EXPENDITURE_ORGANIZATION_ID = l_EXPENDITURE_ORG_ID,
EXPENDITURE_TYPE = l_EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE = l_EXPENDITURE_ITEM_DATE,
DESTINATION_TYPE_CODE = l_DESTINATION_TYPE_CODE,
RATE_TYPE = l_EXCHANGE_RATE_TYPE,
RATE_DATE = l_EXCHANGE_RATE_DATE,
EXCHANGE_RATE = l_EXCHANGE_RATE,
REQUISITION_LINE_TYPE_ID = l_REQUISITION_LINE_TYPE_ID,
PO_CATEGORY_ID = l_CATEGORY_ID,
quantity = l_quantity,
uom_code = l_uom_code,
unit_price = l_unit_price,
currency_code = l_currency_code
where action_id=l_action_id;
update oke_deliverables_b set quantity = l_quantity,
unit_price = l_unit_price,
currency_code = l_currency_code,
uom_code = l_uom_code
where deliverable_id = l_deliverable_id;
update oke_deliverable_actions set
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_LOGIN = fnd_global.login_id,
READY_FLAG = 'Y'
where action_id=l_action_id;
update oke_deliverable_actions set
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_LOGIN = fnd_global.login_id,
READY_FLAG = 'Y'
where action_id=l_action_id;
Select act.deliverable_id,
action_id,
Task_ID,
Ship_From_Org_ID,
Ship_From_Location_ID,
Ship_To_Org_ID,
Ship_To_Location_ID,
Schedule_Designator,
Expected_Date,
ready_flag,
action_name,
dlv.project_id,
act.quantity,
act.uom_code
from oke_deliverable_actions act,oke_deliverables_b dlv
where pa_action_id=b_pa_action_id
and dlv.deliverable_id = act.deliverable_id;