[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_UPDATE;
complete_record finally it can update to NULL */
l_x_flow_schedule_rec := convert_null_to_miss(l_x_flow_schedule_rec);
IF l_flow_schedule_rec.last_updated_by IS NULL THEN
l_flow_schedule_rec.last_updated_by := FND_API.G_MISS_NUM;
IF l_flow_schedule_rec.last_update_date IS NULL THEN
l_flow_schedule_rec.last_update_date := FND_API.G_MISS_DATE;
IF l_flow_schedule_rec.last_update_login IS NULL THEN
l_flow_schedule_rec.last_update_login := FND_API.G_MISS_NUM;
IF l_flow_schedule_rec.program_update_date IS NULL THEN
l_flow_schedule_rec.program_update_date := FND_API.G_MISS_DATE;
, x_last_update_date OUT NOCOPY DATE
, x_last_updated_by OUT NOCOPY NUMBER
, x_last_update_login OUT NOCOPY NUMBER
)
IS
l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type;
l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_UPDATE;
x_last_update_date := l_x_flow_schedule_rec.last_update_date;
x_last_updated_by := l_x_flow_schedule_rec.last_updated_by;
x_last_update_login := l_x_flow_schedule_rec.last_update_login;
PROCEDURE Delete_Row
( x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_wip_entity_id IN NUMBER
)
IS
l_flow_schedule_rec MRP_Flow_Schedule_PVT.Flow_Schedule_PVT_Rec_Type;
l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_DELETE;
, 'Delete_Row'
);
END Delete_Row;
, p_last_updated_by IN NUMBER
, p_last_update_date IN DATE
, p_last_update_login IN NUMBER
, p_line_id IN NUMBER
, p_material_account IN NUMBER
, p_material_overhead_account IN NUMBER
, p_material_variance_account IN NUMBER
, p_mps_net_quantity IN NUMBER
, p_mps_scheduled_comp_date IN DATE
, p_organization_id IN NUMBER
, p_outside_processing_acct IN NUMBER
, p_outside_proc_var_acct IN NUMBER
, p_overhead_account IN NUMBER
, p_overhead_variance_account IN NUMBER
, p_planned_quantity IN NUMBER
, p_primary_item_id IN NUMBER
, p_program_application_id IN NUMBER
, p_program_id IN NUMBER
, p_program_update_date IN DATE
, p_project_id IN NUMBER
, p_quantity_completed IN NUMBER
, p_request_id IN NUMBER
, p_resource_account IN NUMBER
, p_resource_variance_account IN NUMBER
, p_routing_revision IN VARCHAR2
, p_routing_revision_date IN DATE
, p_scheduled_completion_date IN DATE
, p_scheduled_flag IN NUMBER
, p_scheduled_start_date IN DATE
, p_schedule_group_id IN NUMBER
, p_schedule_number IN VARCHAR2
, p_status IN NUMBER
, p_std_cost_adjustment_acct IN NUMBER
, p_task_id IN NUMBER
, p_wip_entity_id IN NUMBER
, p_end_item_unit_number IN VARCHAR2
, p_quantity_scrapped IN NUMBER
)
IS
l_return_status VARCHAR2(1);
l_flow_schedule_rec.last_updated_by := p_last_updated_by;
l_flow_schedule_rec.last_update_date := p_last_update_date;
l_flow_schedule_rec.last_update_login := p_last_update_login;
l_flow_schedule_rec.program_update_date := p_program_update_date;
, p_last_updated_by IN NUMBER
, p_last_update_date IN DATE
, p_last_update_login IN NUMBER
, p_line_id IN NUMBER
, p_material_account IN NUMBER
, p_material_overhead_account IN NUMBER
, p_material_variance_account IN NUMBER
, p_mps_net_quantity IN NUMBER
, p_mps_scheduled_comp_date IN DATE
, p_organization_id IN NUMBER
, p_outside_processing_acct IN NUMBER
, p_outside_proc_var_acct IN NUMBER
, p_overhead_account IN NUMBER
, p_overhead_variance_account IN NUMBER
, p_planned_quantity IN NUMBER
, p_primary_item_id IN NUMBER
, p_program_application_id IN NUMBER
, p_program_id IN NUMBER
, p_program_update_date IN DATE
, p_project_id IN NUMBER
, p_quantity_completed IN NUMBER
, p_request_id IN NUMBER
, p_resource_account IN NUMBER
, p_resource_variance_account IN NUMBER
, p_routing_revision IN VARCHAR2
, p_routing_revision_date IN DATE
, p_scheduled_completion_date IN DATE
, p_scheduled_flag IN NUMBER
, p_scheduled_start_date IN DATE
, p_schedule_group_id IN NUMBER
, p_schedule_number IN VARCHAR2
, p_status IN NUMBER
, p_std_cost_adjustment_acct IN NUMBER
, p_task_id IN NUMBER
, p_wip_entity_id IN NUMBER
, p_end_item_unit_number IN VARCHAR2
, p_quantity_scrapped IN NUMBER
, x_wip_entity_id OUT NOCOPY NUMBER
)
IS
l_return_status VARCHAR2(1);
l_flow_schedule_rec.last_updated_by := p_last_updated_by;
l_flow_schedule_rec.last_update_date := p_last_update_date;
l_flow_schedule_rec.last_update_login := p_last_update_login;
l_flow_schedule_rec.program_update_date := p_program_update_date;
SELECT
row_id,
demand_class,
demand_source_delivery,
demand_source_header_id,
demand_source_line,
demand_source_type,
order_quantity,
inventory_item_id,
project_id,
order_date,
task_id,
end_item_unit_number,
replenish_to_order_flag,
build_in_wip_flag
FROM
/*mrp_unscheduled_orders_v */
(
SELECT
sl1.rowid row_id,
sl1.ship_from_org_id organization_id,
sl1.inventory_item_id,
inv_salesorder.get_salesorder_for_oeheader(SL1.HEADER_ID) demand_source_header_id,
TO_CHAR(SL1.LINE_ID) demand_source_line,
TO_CHAR(NULL) demand_source_delivery,
2 demand_source_type,
wl.line_id,
sl1.schedule_ship_date order_date,
GREATEST((INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SL1.SHIP_FROM_ORG_ID,
SL1.INVENTORY_ITEM_ID,
SL1.ORDER_QUANTITY_UOM,
SL1.ORDERED_QUANTITY) -
MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(SL1.LINE_ID,
2,
TO_CHAR(NULL),
MSI1.REPLENISH_TO_ORDER_FLAG) -
MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY(SL1.SHIP_FROM_ORG_ID,
SL1.INVENTORY_ITEM_ID,
SL1.LINE_ID,
MSI1.REPLENISH_TO_ORDER_FLAG)),
0) order_quantity,
SL1.Project_Id,
SL1.Task_Id,
sl1.demand_class_code demand_class,
sl1.end_item_unit_number,
msi1.replenish_to_order_flag,
msi1.build_in_wip_flag,
MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(SL1.INVENTORY_ITEM_ID,
SL1.SHIP_FROM_ORG_ID,
WL.LINE_ID) alternate_routing_designator
FROM
OE_ORDER_LINES_ALL SL1,
MTL_SYSTEM_ITEMS_KFV MSI1,
WIP_LINES WL,
(select sl2.line_id,
decode((select 1
from oe_order_holds_all oh
where oh.header_id = sl2.header_id
and rownum = 1
and oh.released_flag = 'N'),
null,
0,
decode(sl2.ato_line_id,
null,
mrp_flow_schedule_util.check_holds(sl2.header_id,
sl2.line_id,
'OEOL',
'LINE_SCHEDULING'),
mrp_flow_schedule_util.check_holds(sl2.header_id,
sl2.line_id,
null,
null))) hold
from oe_order_lines_all sl2) line_holds,
(select sl2.line_id,
CTO_WIP_WORKFLOW_API_PK.workflow_build_status(sl2.LINE_ID) status
from oe_order_lines_all sl2) line_build
WHERE
line_build.line_id = sl1.line_id
AND 1 = decode(MSI1.REPLENISH_TO_ORDER_FLAG, 'N', 1, line_build.status)
AND MSI1.BUILD_IN_WIP_FLAG = 'Y'
AND MSI1.PICK_COMPONENTS_FLAG = 'N'
AND MSI1.BOM_ITEM_TYPE = 4
AND MSI1.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
AND MSI1.INVENTORY_ITEM_ID = SL1.INVENTORY_ITEM_ID
AND SL1.ORDERED_QUANTITY > 0
AND SL1.VISIBLE_DEMAND_FLAG = 'Y'
AND SL1.OPEN_FLAG = 'Y'
AND SL1.ITEM_TYPE_CODE in ('STANDARD', 'CONFIG', 'INCLUDED', 'OPTION')
AND OE_INSTALL.GET_ACTIVE_PRODUCT = 'ONT'
AND wl.organization_id = sl1.ship_from_org_id
AND wl.line_id in (select line_id
from bom_operational_routings bor2
where bor2.assembly_item_id = sl1.inventory_item_id
and bor2.organization_id = sl1.ship_from_org_id
and bor2.cfm_routing_flag = 1)
AND SL1.SHIPPED_QUANTITY is NULL
and sl1.line_id = line_holds.line_id
and line_holds.hold = 0
AND NVL(SL1.FULFILLED_FLAG, 'N') <> 'Y'
) so_orders
WHERE line_id = p_line_id
AND organization_id = p_organization_id
AND order_quantity > 0
/* AND unscheduled_order_option = p_unscheduled_order_type */
AND order_date >= p_demand_start_date
AND order_date <= p_demand_end_date
AND ((p_primary_routing = 1 /* Bug 2906442 */
and alternate_routing_designator is null)
or
p_primary_routing = 2 );
SELECT
row_id,
demand_class,
demand_source_delivery,
demand_source_header_id,
demand_source_line,
demand_source_type,
order_quantity,
inventory_item_id,
project_id,
order_date,
task_id,
end_item_unit_number,
replenish_to_order_flag,
build_in_wip_flag
FROM
(
SELECT MR1.ROWID row_id,
MR1.ORGANIZATION_ID,
MR1.INVENTORY_ITEM_ID,
mr1.demand_class,
100 demand_source_type,
null DEMAND_SOURCE_HEADER_ID,
TO_CHAR(MR1.TRANSACTION_ID) DEMAND_SOURCE_LINE,
null demand_source_delivery,
WL.LINE_ID,
NVL(MR1.FIRM_DATE, MR1.NEW_SCHEDULE_DATE) order_date,
GREATEST((NVL(MR1.FIRM_QUANTITY, MR1.NEW_ORDER_QUANTITY) -
MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(TO_CHAR(MR1.TRANSACTION_ID),
100,
NULL,
NULL)),
0) order_quantity,
MR1.PROJECT_ID,
MR1.TASK_ID,
MR1.END_ITEM_UNIT_NUMBER,
KFV.REPLENISH_TO_ORDER_FLAG,
KFV.BUILD_IN_WIP_FLAG,
MRP_FLOW_SCHEDULE_UTIL.GET_ROUTING_DESIGNATOR(MR1.INVENTORY_ITEM_ID,
MR1.ORGANIZATION_ID,
WL.LINE_ID) alternate_routing_designator
FROM MTL_SYSTEM_ITEMS_B KFV,
MRP_SYSTEM_ITEMS RSI1,
MRP_PLANS MP1,
MRP_RECOMMENDATIONS MR1,
WIP_LINES WL
WHERE MP1.PLAN_COMPLETION_DATE IS NOT NULL
AND MP1.DATA_COMPLETION_DATE IS NOT NULL
AND MP1.COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
AND (MP1.ORGANIZATION_ID = MR1.ORGANIZATION_ID OR
(MP1.ORGANIZATION_ID IN
(SELECT ORGANIZATION_ID
FROM MRP_PLAN_ORGANIZATIONS
WHERE COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
AND PLANNED_ORGANIZATION = MR1.ORGANIZATION_ID)))
AND MR1.ORGANIZATION_ID = MR1.SOURCE_ORGANIZATION_ID
AND KFV.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
AND NVL(KFV.RELEASE_TIME_FENCE_CODE, -1) <> 6 /* KANBAN ITEM */
AND MR1.ORDER_TYPE = 5 /* PLANNED ORDER */
AND MR1.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
AND MR1.COMPILE_DESIGNATOR = RSI1.COMPILE_DESIGNATOR
AND MR1.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
AND MR1.COMPILE_DESIGNATOR =
(SELECT DESIGNATOR
FROM MRP_DESIGNATORS_VIEW
WHERE PRODUCTION = 1
AND ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND DESIGNATOR = MR1.COMPILE_DESIGNATOR)
AND RSI1.BUILD_IN_WIP_FLAG = 1 /* YES */
AND RSI1.BOM_ITEM_TYPE = 4
AND (RSI1.IN_SOURCE_PLAN = 2 OR RSI1.IN_SOURCE_PLAN IS NULL)
AND wl.organization_id = MR1.ORGANIZATION_ID
AND wl.line_id in (select line_id
from bom_operational_routings bor2
where bor2.assembly_item_id = MR1.INVENTORY_ITEM_ID
and bor2.organization_id = MR1.ORGANIZATION_ID
and bor2.cfm_routing_flag = 1)
) po_orders
WHERE line_id = p_line_id
AND organization_id = p_organization_id
AND order_quantity > 0
/* AND unscheduled_order_option = p_unscheduled_order_type */
AND order_date >= p_demand_start_date
AND order_date <= p_demand_end_date
AND ((p_primary_routing = 1 /* Bug 2906442 */
and alternate_routing_designator is null)
or
p_primary_routing = 2 );
SELECT
wip_entities_s.nextval
FROM dual;
SELECT rowid
INTO l_rowid
FROM mrp_recommendations
WHERE rowid = l_rowid
FOR UPDATE of quantity_in_process NOWAIT;
p_last_updated_by => FND_API.G_MISS_NUM,
p_last_update_date => FND_API.G_MISS_DATE,
p_last_update_login => FND_API.G_MISS_NUM,
p_line_id => l_line_id,
p_material_account => FND_API.G_MISS_NUM,
p_material_overhead_account => FND_API.G_MISS_NUM,
p_material_variance_account => FND_API.G_MISS_NUM,
p_mps_net_quantity => FND_API.G_MISS_NUM,
p_mps_scheduled_comp_date => FND_API.G_MISS_DATE,
p_organization_id => l_organization_id,
p_outside_processing_acct => FND_API.G_MISS_NUM,
p_outside_proc_var_acct => FND_API.G_MISS_NUM,
p_overhead_account => FND_API.G_MISS_NUM,
p_overhead_variance_account => FND_API.G_MISS_NUM,
p_planned_quantity => l_planned_quantity,
p_primary_item_id => l_primary_item_id,
p_program_application_id => FND_API.G_MISS_NUM,
p_program_id => FND_API.G_MISS_NUM,
p_program_update_date => FND_API.G_MISS_DATE,
p_project_id => l_project_id,
p_quantity_completed => FND_API.G_MISS_NUM,
p_request_id => USERENV('SESSIONID'), -- bug 4529167
p_resource_account => FND_API.G_MISS_NUM,
p_resource_variance_account => FND_API.G_MISS_NUM,
p_routing_revision => FND_API.G_MISS_CHAR,
p_routing_revision_date => FND_API.G_MISS_DATE,
p_scheduled_completion_date => l_scheduled_completion_date,
p_scheduled_flag => l_scheduled_flag,
p_scheduled_start_date => FND_API.G_MISS_DATE,
p_schedule_group_id => l_schedule_group_id,
p_schedule_number => l_schedule_number,
p_status => FND_API.G_MISS_NUM,
p_std_cost_adjustment_acct => FND_API.G_MISS_NUM,
p_task_id => l_task_id,
p_wip_entity_id => l_wip_entity_id,
p_end_item_unit_number => l_end_item_unit_number,
p_quantity_scrapped => FND_API.G_MISS_NUM,
x_wip_entity_id => l_wip_entity_id2
);