[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT exp.exception_id,
exp.organization_id,
exp.inventory_item_id,
exp.exception_type,
exp.organization_code,
exp.item_segments,
exp.exception_type_text,
NVL(exp.project_number, 'N/A'),
NVL(exp.to_project_number, 'N/A'),
NVL(exp.task_number, 'N/A'),
NVL(exp.to_task_number, 'N/A'),
exp.planning_group,
exp.due_date,
exp.from_date,
exp.to_date,
exp.days_compressed,
exp.quantity,
exp.lot_number,
exp.order_number,
exp.supply_type,
exp.end_item_segments,
exp.end_order_number,
exp.department_line_code,
exp.resource_code,
exp.utilization_rate
FROM mrp_exception_details_v exp, mrp_plan_organizations_v mpo
WHERE exp.exception_type in (1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20)
AND mpo.organization_id = p_owning_org_id
AND mpo.compile_designator = p_designator
AND exp.organization_id = mpo.planned_organization
AND exp.compile_designator = mpo.compile_designator
ORDER BY exp.exception_id;
SELECT vend.vendor_id,
vend.vendor_name
FROM po_vendors vend,
mrp_recommendations rec,
mrp_exception_details_v exp
WHERE vend.vendor_id = rec.vendor_id
AND rec.transaction_id = exp.transaction_id
AND exp.exception_id = p_exception_id;
SELECT vend.vendor_id,
vend.vendor_name,
site.vendor_site_id,
site.vendor_site_code
FROM po_vendor_sites_all site,
po_headers_all poh,
po_vendors vend,
mrp_item_purchase_orders ipo,
mrp_recommendations rec,
mrp_exception_details_v exp
WHERE site.vendor_site_id = poh.vendor_site_id
AND site.org_id = poh.org_id
AND poh.po_header_id = ipo.purchase_order_id
AND ipo.transaction_id = rec.disposition_id
AND vend.vendor_id = rec.vendor_id
AND rec.transaction_id = exp.transaction_id
AND exp.exception_id = p_exception_id;
SELECT cust.cust_account_id,
part.party_name
FROM hz_cust_accounts cust,
hz_parties part,
oe_order_headers_all soh,
oe_order_types_v sot,
mtl_sales_orders mso,
mrp_schedule_dates dates,
mrp_gross_requirements mgr,
mrp_exception_details_v exp
WHERE cust.cust_account_id = soh.sold_to_org_id
AND soh.order_number = to_number(mso.segment1)
AND soh.order_type_id = sot.order_type_id
AND sot.name = mso.segment2
AND mso.sales_order_id = dates.source_sales_order_id
AND dates.supply_demand_type = 1
AND dates.schedule_level = 3
AND dates.mps_transaction_id = mgr.disposition_id
AND mgr.origination_type = 6
AND mgr.demand_id = exp.demand_id
AND exp.exception_id = p_exception_id
AND cust.party_id = part.party_id;
l_org_selection number;
DeleteActivities(p_designator, p_owning_org_id);
SELECT rec.order_type
INTO l_order_type_code
FROM mrp_recommendations rec,
mrp_exception_details_v exp
WHERE rec.transaction_id = exp.transaction_id
AND exp.exception_id = l_exception_id;
SELECT NVL(plan_type, curr_plan_type),
NVL(organization_selection, 1)
INTO l_plan_type,
l_org_selection
FROM mrp_plans
WHERE organization_id = p_owning_org_id
AND compile_designator = p_designator;
if (l_org_selection = 1) then -- single org
if (l_plan_type = 1) then -- MRP plan
l_workbench_function := 'MRPFPPWB-390';
UPDATE mrp_plans
SET min_wf_except_id = l_min_exception_id,
max_wf_except_id = l_max_exception_id
WHERE organization_id = p_owning_org_id
AND compile_designator = p_designator;
SELECT production
FROM mrp_plans_sc_v
/** Bug 2286190 WHERE organization_id = p_organization_id **/
WHERE planned_organization = p_organization_id
AND compile_designator = p_compile_designator;
PROCEDURE SelectPlanner( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
CURSOR PLANNER_C(p_compile_designator in varchar2,
p_organization_id in number,
p_inventory_item_id in number) IS
SELECT mp.employee_id
FROM mtl_planners mp,
mrp_system_items items
WHERE mp.organization_id = items.organization_id
AND mp.planner_code = items.planner_code
AND items.inventory_item_id = p_inventory_item_id
AND items.organization_id = p_organization_id
AND items.compile_designator = p_compile_designator;
wf_core.context('MRP_EXP_WF', 'SelectPlanner', itemtype, itemkey, actid, funcmode);
END SelectPlanner;
PROCEDURE SelectBuyer( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
CURSOR BUYER_C(p_compile_designator in varchar2,
p_organization_id in number,
p_inventory_item_id in number) IS
SELECT buyer_id
FROM mrp_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND compile_designator = p_compile_designator;
wf_core.context('MRP_EXP_WF', 'SelectBuyer', itemtype, itemkey, actid, funcmode);
END SelectBuyer;
PROCEDURE SelectSupplierCnt( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
l_supplier_id number :=
wf_engine.GetItemAttrNumber( itemtype => itemtype,
itemkey => itemkey,
aname => 'SUPPLIER_ID');
SELECT DECODE(fu.employee_id, NULL, fu.user_id, fu.employee_id),
DECODE(fu.employee_id, NULL, 'FND_USR', 'PER')
FROM fnd_user fu,
po_vendor_contacts cont
WHERE fu.supplier_id = cont.vendor_contact_id
AND cont.vendor_site_id = p_supplier_site_id;
wf_core.context('MRP_EXP_WF', 'SelectSupplierCnt', itemtype, itemkey, actid, funcmode);
END SelectSupplierCnt;
PROCEDURE SelectSalesRep( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
CURSOR SALESREP_C(p_exception_id in number) IS
SELECT rep.person_id
FROM
ra_salesreps_all rep,
oe_order_headers_all soh,
oe_order_types_v sot,
mtl_sales_orders mso,
mrp_schedule_dates dates,
mrp_gross_requirements mgr,
mrp_exception_details_v exp
WHERE
rep.org_id = soh.org_id
AND rep.salesrep_id = soh.salesrep_id
AND soh.order_number = to_number(mso.segment1)
AND soh.order_type_id = sot.order_type_id
AND sot.name = mso.segment2
AND mso.sales_order_id = dates.source_sales_order_id
AND dates.supply_demand_type = 1
AND dates.schedule_level = 3
AND dates.mps_transaction_id = mgr.disposition_id
AND mgr.origination_type = 6
AND mgr.demand_id = exp.demand_id
AND exp.exception_id = p_exception_id;
wf_core.context('MRP_EXP_WF', 'SelectSalesRep', itemtype, itemkey, actid, funcmode);
END SelectSalesRep;
PROCEDURE SelectCustomerCnt( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
l_customer_id number :=
wf_engine.GetItemAttrNumber( itemtype => itemtype,
itemkey => itemkey,
aname => 'CUSTOMER_ID');
SELECT DECODE(fu.employee_id, NULL, fu.user_id, fu.employee_id),
DECODE(fu.employee_id, NULL, 'FND_USR', 'PER')
FROM fnd_user fu,
hz_cust_account_roles cont
WHERE fu.customer_id = cont.cust_account_role_id
AND cont.cust_account_id = p_customer_id;
SELECT cust_account_role_id
FROM hz_cust_account_roles
WHERE cust_account_id = p_customer_id;
wf_core.context('MRP_EXP_WF', 'SelectCustomerCnt', itemtype, itemkey, actid, funcmode);
END SelectCustomerCnt;
PROCEDURE SelectTaskMgr( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
CURSOR TASK_MANAGER_C(p_project_number in varchar2,
p_task_number in varchar2) IS
SELECT tasks.task_manager_person_id
FROM pa_tasks tasks,
pa_projects_all proj
WHERE tasks.task_number = p_task_number
AND tasks.project_id = proj.project_id
AND proj.segment1 = p_project_number;
SELECT ppp.person_id
FROM pa_project_players ppp,
pa_projects_all proj
WHERE ppp.project_role_type = 'PROJECT MANAGER'
AND ppp.project_id = proj.project_id
AND proj.segment1 = p_project_number;
wf_core.context('MRP_EXP_WF', 'SelectTaskMgr', itemtype, itemkey, actid, funcmode);
END SelectTaskMgr;
SELECT g.responsibility_id, g.user_id, g.responsibility_application_id
INTO l_responsibility_id, l_user_id, l_application_id
FROM fnd_user u, fnd_user_resp_groups g
WHERE u.user_name = l_assigned_user
AND g.start_date <= SYSDATE
AND NVL(g.end_date, SYSDATE + 1) >= SYSDATE
AND g.user_id = u.user_id
AND u.start_date <= SYSDATE
AND NVL(u.end_date, SYSDATE + 1) >= SYSDATE
AND ROWNUM = 1;
SELECT organization_id
INTO l_owning_org_id
FROM mrp_plan_organizations_v
WHERE planned_organization = l_organization_id
AND compile_designator = l_compile_designator;
SELECT mrp_workbench_query_s.nextval,
wip_job_schedule_interface_s.nextval
INTO l_po_batch_number,
l_wip_group_id
FROM dual;
SELECT transaction_id
INTO l_transaction_id
FROM mrp_exception_details_v
WHERE exception_id = l_exception_id;
UPDATE mrp_recommendations
SET implement_date = new_schedule_date,
implement_quantity = DECODE(l_exception_type, 8, 0,
new_order_quantity),
implement_demand_class = demand_class,
implement_status_code = decode(l_order_type, 3,
decode(l_exception_type, 8, 7, implement_status_code),
implement_status_code), /* Bug 2226979 **/
implement_project_id = project_id,
implement_task_id = task_id,
implement_job_name = FND_PROFILE.VALUE('WIP_JOB_PREFIX')||to_char(wip_job_number_s.nextval),
implement_line_id = line_id,
implement_alternate_bom = alternate_bom_designator,
implement_alternate_routing = alternate_routing_designator,
implement_end_item_unit_number = end_item_unit_number
WHERE transaction_id = l_transaction_id;
SELECT loc.location_id
INTO l_location_id
FROM hr_locations loc,
hr_organization_units unit
WHERE unit.organization_id = l_organization_id
AND unit.location_id = loc.location_id;
UPDATE mrp_recommendations supplies
SET old_order_quantity = new_order_quantity,
quantity_in_process = new_order_quantity,
implement_date = new_schedule_date,
implement_quantity = decode(disposition_status_type,2,0,new_order_quantity), /** Bug 2226979 **/
implement_firm = firm_planned_type,
implement_dock_date = new_dock_date,
implement_location_id = l_location_id,
implement_source_org_id = source_organization_id,
implement_vendor_id = source_vendor_id,
implement_vendor_site_id = source_vendor_site_id,
implement_project_id = project_id,
implement_task_id = task_id,
implement_demand_class = NULL,
implement_employee_id = ( SELECT
decode(msi.planner_code,NULL,mplm.employee_id,mpl.employee_id)
FROM mtl_planners mplm,
mtl_planners mpl,
mtl_parameters mparam,
mtl_system_items master_msi,
mtl_system_items msi
WHERE msi.organization_id = supplies.organization_id
AND msi.inventory_item_id = supplies.inventory_item_id
AND master_msi.organization_id = mparam.master_organization_id
AND master_msi.inventory_item_id = msi.inventory_item_id
AND mpl.organization_id (+) = msi.organization_id
AND mpl.planner_code (+) = NVL(msi.planner_code, 'A')
AND mplm.organization_id (+)= master_msi.organization_id
AND mplm.planner_code (+)= NVL(master_msi.planner_code, 'A')
AND mparam.organization_id = msi.organization_id)
WHERE transaction_id = l_transaction_id;
UPDATE wf_notifications
SET end_date = SYSDATE - 450
WHERE group_id IN
(SELECT notification_id
FROM wf_item_activity_statuses
WHERE item_type = l_item_type
AND item_key = l_item_key
UNION
SELECT notification_id
FROM wf_item_activity_statuses_h
WHERE item_type = l_item_type
AND item_key = l_item_key);
UPDATE wf_items
SET end_date = SYSDATE - 450
WHERE item_type = l_item_type
AND item_key = l_item_key ;
UPDATE wf_item_activity_statuses
SET end_date = SYSDATE - 450
WHERE item_type = l_item_type
AND item_key = l_item_key;
UPDATE wf_item_activity_statuses_h
SET end_date = SYSDATE - 450
WHERE item_type = l_item_type
AND item_key = l_item_key;
PROCEDURE DeleteActivities( arg_compile_desig in varchar2,
arg_organization_id in number) IS
CURSOR DELETE_ACTIVITIES_C(p_min_exception_id in number,
p_max_exception_id in number) IS
SELECT item_key
FROM wf_items
WHERE item_type = 'MRPEXPWF'
AND to_number(item_key) >= p_min_exception_id
AND to_number(item_key) <= p_max_exception_id;
SELECT wn.notification_id
FROM wf_notifications wn,
wf_item_activity_statuses wias
WHERE wn.status = 'OPEN'
AND wn.notification_id = wias.notification_id
AND wias.item_key = p_item_key
AND wias.item_type = p_item_type;
SELECT min_wf_except_id,
max_wf_except_id
INTO l_min_exception_id,
l_max_exception_id
FROM mrp_plans
WHERE organization_id = arg_organization_id
AND compile_designator = arg_compile_desig;
OPEN DELETE_ACTIVITIES_C(l_min_exception_id, l_max_exception_id);
FETCH DELETE_ACTIVITIES_C INTO l_item_key;
EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND OR DELETE_ACTIVITIES_C%NOTFOUND IS NULL;
CLOSE DELETE_ACTIVITIES_C;
END DeleteActivities;