[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT c.description item_Description,
c.planning_make_buy_code mbf,
c.primary_uom_code uom,
p.ap_accrual_account accru_Acct,
p.invoice_price_var_account ipv_acct,
nvl(p.encumbrance_account, c.encumbrance_account) budget_Acct,
decode(c.inventory_asset_flag, 'Y', p.material_account,
NVL(c.expense_Account, p.expense_Account)) charge_Acct,
NVL(c.source_type, p.source_type) src_type,
DECODE(c.source_type, NULL,
DECODE(p.source_type, NULL, NULL, p.source_organization_id),
c.source_organization_id) src_org,
DECODE(c.source_type, NULL,
DECODE(p.source_type, NULL, NULL, p.source_subinventory),
c.source_subinventory) src_subinv,
c.purchasing_enabled_flag purch_flag,
c.internal_order_enabled_flag order_flag,
c.mtl_transactions_enabled_flag transact_flag,
c.list_price_per_unit unit_price,
c.planner_code planner,
build_in_wip_flag build_in_wip,
pick_components_flag pick_components
FROM mtl_system_items c, mtl_parameters p
WHERE c.inventory_item_id = p_item_id
AND c.organization_id = p.organization_id
AND p.organization_id = p_dest_orgn_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT location_id
FROM hr_organization_units
WHERE organization_id = p_dest_orgn_id;
SELECT operating_unit
FROM org_organization_definitions
WHERE organization_id = p_dest_orgn_id;
SELECT related_item_id
FROM mtl_related_items_view
WHERE relationship_type_id = 18
AND inventory_item_id = p_item_id;
SELECT order_number
INTO l_order_number
FROM oe_order_headers_all
WHERE header_id = l_header_rec.order_header_id;
CSP_Notification_Details_PKG.Insert_Row(
px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
,p_NOTIFICATION_ID => p_notification_id
,p_INVENTORY_ITEM_ID => p_item_id
,p_AVAILABLE_QUANTITY => p_qty
,p_ORDER_BY_DATE => p_need_Date
,p_SOURCE_TYPE => p_src_type
,p_SOURCE_ORGANIZATION_ID => p_Src_orgn_id
,p_SOURCE_SUBINVENTORY => p_src_subinv
,p_CREATED_BY => nvl(fnd_global.user_id, 0)
,p_CREATION_DATE => sysdate
,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,p_REPAIR_SUPPLIER_ID => null
,p_ORDER_NUMBER => l_order_number
);
select nvl(req_encumbrance_flag, 'N')
into l_encum_flag
from financials_system_params_all
where nvl(org_id, -11) = nvl(l_po_org_id, -11);
SELECT po_requisition_headers_s.nextval
INTO l_requisition_header_id
FROM sys.dual;
SELECT po_requisition_lines_s.nextval
INTO l_requisition_line_id
FROM sys.dual;
INSERT INTO po_requisitions_interface_all(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ITEM_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
PREPARER_ID,
INTERFACE_SOURCE_CODE,
REQUISITION_TYPE,
AUTHORIZATION_STATUS,
SOURCE_TYPE_CODE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
DELIVER_TO_REQUESTOR_ID,
DESTINATION_TYPE_CODE,
UOM_CODE,
DELIVER_TO_LOCATION_ID,
ITEM_ID,
ITEM_REVISION,
QUANTITY,
NEED_BY_DATE,
GL_DATE,
CHARGE_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
BUDGET_ACCOUNT_ID,
AUTOSOURCE_FLAG,
ORG_ID,
UNIT_PRICE)
VALUES (
sysdate,
l_user_id,
l_item_attr_rec.item_Description,
sysdate,
nvl(fnd_global.login_id, -1),
l_employee_id,
'CSP',
'PURCHASE',
'APPROVED',
'VENDOR',
nvl(p_src_orgn_id, l_item_attr_rec.src_org),
nvl(p_src_subinv, l_item_attr_rec.src_subinv),
p_dest_orgn_id,
NULL, -- destination subinv
l_employee_id,
'INVENTORY',
l_item_Attr_Rec.uom,
l_location_id,
p_item_id,
NULL, -- DECODE(l_item_revision,'@@@',NULL,l_item_revision),
p_qty,
trunc(p_need_date),
SYSDATE,
l_item_attr_rec.charge_acct,
l_item_attr_rec.accru_acct,
l_item_attr_rec.ipv_acct,
l_item_attr_rec.budget_acct,
'P',
l_po_org_id,
l_item_attr_rec.unit_price);
CSP_Notification_Details_PKG.Insert_Row(
px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
,p_NOTIFICATION_ID => p_notification_id
,p_INVENTORY_ITEM_ID => p_item_id
,p_AVAILABLE_QUANTITY => p_qty
,p_ORDER_BY_DATE => p_need_date
,p_SOURCE_TYPE => 'PO'
,p_SOURCE_ORGANIZATION_ID => p_Src_orgn_id
,p_SOURCE_SUBINVENTORY => p_src_subinv
,p_CREATED_BY => nvl(fnd_global.user_id, 0)
,p_CREATION_DATE => sysdate
,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,p_REPAIR_SUPPLIER_ID => null
,p_ORDER_NUMBER => null
);
SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
INTO l_wip_id
FROM dual;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
GROUP_ID,
PROCESS_PHASE,
PROCESS_STATUS,
ORGANIZATION_ID,
LOAD_TYPE,
LAST_UNIT_COMPLETION_DATE,
PRIMARY_ITEM_ID,
START_QUANTITY,STATUS_TYPE)
VALUES(
sysdate,
l_user_id,
sysdate,
nvl(fnd_global.login_id, 0),
l_wip_id,
2,
1,
p_dest_orgn_id,
1,
p_need_date,
p_item_id,
p_qty,
3); -- Approved
CSP_Notification_Details_PKG.Insert_Row(
px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
,p_NOTIFICATION_ID => p_notification_id
,p_INVENTORY_ITEM_ID => p_item_id
,p_AVAILABLE_QUANTITY => p_qty
,p_ORDER_BY_DATE => p_need_date
,p_SOURCE_TYPE => 'WIP'
,p_SOURCE_ORGANIZATION_ID => p_Src_orgn_id
,p_SOURCE_SUBINVENTORY => p_src_subinv
,p_CREATED_BY => nvl(fnd_global.user_id, 0)
,p_CREATION_DATE => sysdate
,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,p_REPAIR_SUPPLIER_ID => null
,p_ORDER_NUMBER => null
);
SELECT order_number
INTO l_order_number
FROM oe_order_headers_all
WHERE header_id = l_header_rec.order_header_id;
CSP_Notification_Details_PKG.Insert_Row(
px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
,p_NOTIFICATION_ID => p_notification_id
,p_INVENTORY_ITEM_ID => nvl(l_repair_to_item, p_item_id)
,p_AVAILABLE_QUANTITY => p_qty
,p_ORDER_BY_DATE => p_need_date
,p_SOURCE_TYPE => p_src_type
,p_SOURCE_ORGANIZATION_ID => p_repair_supplier_id
,p_SOURCE_SUBINVENTORY => null
,p_CREATED_BY => nvl(fnd_global.user_id, 0)
,p_CREATION_DATE => sysdate
,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,p_REPAIR_SUPPLIER_ID => null
,p_ORDER_NUMBER => l_order_number
);
select x_ret_status,x_msg from dual;