[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Inserts error msg into log file. *
* *
************************************************************************/
PROCEDURE log(info VARCHAR2) is
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG, info);
SELECT concatenated_segments
INTO l_comp_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_comp_id
AND organization_id = p_organization_id;
SELECT count(*)
INTO l_cnt
FROM bom_operation_networks
WHERE from_op_seq_id = p_op_seq_id
AND nvl(transition_type, 3) = 1;
SELECT count(*)
INTO l_cnt
FROM bom_operation_networks
WHERE to_op_seq_id = p_op_seq_id
AND nvl(transition_type, 3) = 1;
SELECT count(*)
INTO l_cnt
FROM bom_operation_sequences
WHERE operation_type = 3
AND routing_sequence_id = (
SELECT max(routing_sequence_id)
FROM bom_operation_sequences
WHERE operation_sequence_id = p_op_seq_id
);
SELECT mkps.pull_sequence_id
INTO l_pull_sequence_id
FROM mtl_kanban_pull_sequences mkps,
bom_inventory_components bic,
mtl_system_items msi
WHERE mkps.inventory_item_id = p_item_id
AND mkps.organization_id = p_organization_id
AND mkps.auto_request = 'Y'
AND mkps.release_kanban_flag = 1
AND mkps.inventory_item_id = bic.component_item_id
AND msi.inventory_item_id = p_item_id
AND msi.organization_id = p_organization_id
AND msi.release_time_fence_code = G_Release_Time_Kanban_Item
AND bic.component_sequence_id = p_item_sequence_id
AND ( (bic.supply_subinventory IS NOT NULL AND mkps.subinventory_name = bic.supply_subinventory)
OR (bic.supply_subinventory IS NULL AND mkps.subinventory_name = msi.wip_supply_subinventory) )
AND ( (bic.supply_subinventory IS NOT NULL AND nvl(mkps.locator_id, '-0909090909') = nvl(bic.supply_locator_id, '-0909090909'))
OR (bic.supply_subinventory IS NULL AND nvl(mkps.locator_id, '-0909090909') = nvl(msi.wip_supply_locator_id, '-0909090909')) )
AND bic.wip_supply_type IN (G_Supply_Type_Assembly_Pull, G_Supply_Type_Operation_Pull);
SELECT expl.component_item_id component_item_id,
comp.operation_seq_num operation_seq_num,
SUM(comp.component_quantity) extended_quantity,
MIN(DECODE(comp.wip_supply_type, NULL,
DECODE(sys.wip_supply_type, NULL,
1, sys.wip_supply_type),
comp.wip_supply_type)) wip_supply_type,
MIN(comp.component_quantity) component_quantity,
MIN(nvl(comp.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL)) basis_type
FROM mtl_system_items sys,
bom_inventory_components comp,
bom_explosions expl,
bom_bill_of_materials bbm
WHERE bbm.organization_id = sys.organization_id
AND comp.component_item_id = sys.inventory_item_id
AND comp.component_sequence_id = expl.component_sequence_id
AND sys.bom_item_type = 4
AND comp.component_item_id = expl.component_item_id
AND comp.bill_sequence_id = expl.bill_sequence_id
AND bbm.organization_id = p_org_id
AND bbm.assembly_item_id = l_assy_id
AND (NVL(bbm.alternate_bom_designator, 'ABD756fhh466')
= NVL(p_alt_bom_desig, 'ABD756fhh466')
OR
(bbm.alternate_bom_designator is null AND
NOT EXISTS
(SELECT null
FROM bom_bill_of_materials bbm1
WHERE bbm1.alternate_bom_designator = p_alt_bom_desig
AND bbm1.organization_id = bbm.organization_id
AND bbm1.assembly_item_id = bbm.assembly_item_id)))
AND bbm.common_bill_sequence_id = expl.bill_sequence_id
AND expl.top_bill_sequence_id = l_top_bill_sequence_id
AND expl.assembly_item_id is not null
--Bug 6691128 Removing Trunc So that Time to be considered
-- AND trunc(expl.effectivity_date) <= trunc(p_sched_start_date)
-- AND NVL(expl.disable_date, p_sched_start_date + 1)
-- > trunc(p_sched_start_date)
AND expl.effectivity_date <= p_sched_start_date
AND NVL(expl.disable_date, p_sched_start_date + 1)
> p_sched_start_date
AND expl.explosion_type = 'ALL'
GROUP BY expl.component_item_id,comp.operation_seq_num;
SELECT component_sequence_id
FROM bom_explosions
WHERE top_bill_sequence_id = l_top_bill_sequence_id
AND explosion_type = 'ALL'
AND component_item_id = l_comp_id
AND operation_seq_num = l_operation_seq_num
--Bug 6691128 Removing Trunc So that Time to be considered
-- AND trunc(effectivity_date) <= trunc(p_sched_start_date);
SELECT routing_sequence_id
INTO l_routing_sequence_id
FROM bom_operational_routings
WHERE organization_id = p_org_id
AND assembly_item_id = p_top_assy_id
AND NVL(alternate_routing_designator, 'ABD756fhh456') =
NVL(p_alt_rtg_desig, 'ABD756fhh456');
SELECT inherit_phantom_op_seq
INTO l_inherit_phantom
FROM bom_parameters
WHERE organization_id = p_org_id;
SELECT bill_sequence_id
INTO l_bill_sequence_id
FROM bom_bill_of_materials
WHERE organization_id = p_org_id
AND assembly_item_id = p_top_assy_id
AND nvl(alternate_bom_designator, '@@@@') =
nvl(p_alt_bom_desig, '@@@@');
SELECT max(top_bill_sequence_id)
INTO l_top_bill_sequence_id
FROM bom_explosions
WHERE component_item_id = p_top_assy_id
AND organization_id = p_org_id;
SELECT line_op_seq_id
INTO l_line_op_seq_id
FROM bom_operation_sequences
WHERE routing_sequence_id = l_routing_sequence_id
AND operation_seq_num = l_operation_seq_num
AND operation_type = 1
AND effectivity_date =
(SELECT max(effectivity_date)
FROM bom_operation_sequences
WHERE routing_sequence_id = l_routing_sequence_id
AND operation_seq_num = l_operation_seq_num
AND operation_type = 1);
SELECT CEIL(NVL(total_time_calc, 0) / p_takt_time) * p_takt_time
INTO l_total_time
FROM bom_operation_sequences
WHERE operation_sequence_id = p_line_op_seq_id;
SELECT to_op_seq_id
FROM Bom_Operation_Networks
CONNECT BY PRIOR to_op_seq_id = from_op_seq_id
AND nvl(transition_type, 0) NOT IN (2,3)
START WITH from_op_seq_id = i_start_operation_sequence_id
AND nvl(transition_type, 0) NOT IN (2,3);
SELECT start_time,
stop_time,
1/maximum_rate
INTO l_start_time,
l_stop_time,
l_takt_time
FROM wip_lines
WHERE line_id = p_line_id
AND organization_id = p_organization_id;
SELECT nvl(fixed_lead_time, 0) + (p_quantity - 1) * nvl(variable_lead_time, 0)
INTO l_lead_time
FROM mtl_system_items
WHERE inventory_item_id = p_assembly_item_id
AND organization_id = p_organization_id;
* PROCEDURE Update_Flow_Schedule *
* Updates the Auto Replenish flag of the given Flow Schedule. *
* *
************************************************************************/
PROCEDURE Update_Flow_Schedule(
p_schedule_number IN VARCHAR2) IS
BEGIN
UPDATE wip_flow_schedules
SET auto_replenish = 'Y'
WHERE schedule_number = p_Schedule_Number;
END Update_Flow_Schedule;
SELECT MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
INTO l_report_id
FROM dual;
INSERT into MTL_KANBAN_CARD_PRINT_TEMP(
REPORT_ID,
KANBAN_CARD_ID)
VALUES (l_report_id,
p_Kanban_Card_Ids(l_card_count));
DELETE FROM mtl_kanban_card_print_temp
WHERE report_id = l_report_id;
update_exception EXCEPTION;
SELECT flow.schedule_number,
flow.build_sequence build_sequence,
flow.primary_item_id primary_item_id,
flow.line_id line_id,
(flow.planned_quantity - nvl(flow.quantity_completed, 0)) open_quantity,
flow.scheduled_start_date scheduled_start_date,
flow.scheduled_completion_date scheduled_completion_date,
flow.alternate_bom_designator alternate_bom_designator,
flow.alternate_routing_designator alternate_routing_designator
FROM wip_flow_schedules flow,
wip_lines lines
WHERE flow.planned_quantity - nvl(flow.quantity_completed, 0) > 0
AND flow.status <> 2
AND flow.scheduled_completion_date <= (l_server_compl_date + 1)
AND flow.scheduled_completion_date >= sysdate
AND flow.line_id = lines.line_id
AND flow.organization_id = lines.organization_id
AND lines.organization_id = p_organization_id
AND lines.line_code BETWEEN p_min_line_code AND p_max_line_code
AND ( (p_build_sequence is not null AND flow.build_sequence <= p_build_sequence)
OR (p_build_sequence is null AND flow.build_sequence is not null))
AND nvl(flow.auto_replenish, 'N') = 'N';
SELECT auto_replenish
INTO l_auto_replenish
FROM wip_flow_schedules
WHERE schedule_number = l_schedule_number
FOR UPDATE OF auto_replenish NOWAIT;
raise update_exception;
l_comp_table.DELETE;
SELECT wip_entity_id
INTO l_wip_entity_id
FROM wip_flow_schedules
WHERE schedule_number = l_comp_table(l_comp_count).schedule_number
AND organization_id = p_organization_id;
INV_Kanban_GRP.Update_Card_Supply_Status(
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
p_api_version_number => l_api_version_number,
p_init_msg_list => NULL,
p_commit => NULL,
p_Kanban_Card_Id => l_Kanban_Card_Id,
p_Supply_Status => G_Supply_Status_Empty,
p_Document_Type => NULL,
p_Document_Header_Id => NULL,
p_Document_Detail_Id => NULL,
p_Need_By_Date => l_need_by_date,
p_Source_Wip_Entity_Id => l_wip_entity_id);
Update_Flow_Schedule(l_comp_table(l_comp_count).schedule_number);
log('Updated Auto Replenish flag of Flow schedules, Schedule number = '|| l_comp_table(l_comp_count).schedule_number);
WHEN update_exception THEN
fnd_message.set_name('FLM', 'FLM_AR_ERR_LOCK_SCHEDULE');
| If Print Kanban Cards option is selected and cards have been created then only print cards|
+-------------------------------------------------------------------------------------------*/
IF (p_print_card = 1 AND l_Kanban_Card_Ids.COUNT > 0) THEN
Print_Kanban_Cards(l_Kanban_Card_Ids);