[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select bic.component_sequence_id,
bic.component_item_id,
msik.concatenated_segments component_item,
msik.description,
bic.component_quantity,
bic.component_yield_factor component_yield,
msik.primary_uom_code uom,
bic.wip_supply_type,
lu.meaning wip_supply_type_disp
from bom_inventory_components bic,
mtl_system_items_kfv msik,
mfg_lookups lu
where bic.bill_sequence_id = l_bill_sequence_id
and bic.effectivity_date <= sysdate
and (bic.disable_date >= sysdate or
bic.disable_date is null)
and i_asset_number >= bic.from_end_item_unit_number
and (i_asset_number <= bic.to_end_item_unit_number or
bic.to_end_item_unit_number is null)
and msik.organization_id = i_organization_id
and msik.inventory_item_id = bic.component_item_id
and lu.lookup_type(+) = g_supply_type
and lu.lookup_code(+) = bic.wip_supply_type
--fix for 3371471.added following condition to fetch only stockable items
and msik.stock_enabled_flag= l_stock_flag
order by component_sequence_id;
SELECT
bic.component_sequence_id,
bic.component_item_id,
msik.concatenated_segments component_item,
msik.description,
bic.component_quantity,
bic.component_yield_factor component_yield,
msik.primary_uom_code uom,
bic.wip_supply_type,
lu.meaning wip_supply_type_disp
FROM
bom_inventory_components bic,
mtl_system_items_kfv msik,
mfg_lookups lu
WHERE
bic.bill_sequence_id = l_bill_sequence_id
and bic.effectivity_date <= sysdate
and (bic.disable_date >= sysdate or
bic.disable_date is null)
and msik.organization_id = i_organization_id
and msik.inventory_item_id = bic.component_item_id
and lu.lookup_type(+) = g_supply_type
and lu.lookup_code(+) = bic.wip_supply_type
--fix for 3371471.added following condition to fetch only stockable items
and msik.stock_enabled_flag= l_stock_flag
ORDER BY component_sequence_id;
select bic.component_sequence_id,
bic.component_item_id,
msik.concatenated_segments component_item,
msik.description,
bic.component_quantity,
bic.component_yield_factor component_yield,
msik.primary_uom_code uom,
bic.wip_supply_type,
lu.meaning wip_supply_type_disp
from bom_inventory_components bic,
bom_bill_of_materials bbom,
mtl_system_items_kfv msik,
mfg_lookups lu
where bbom.assembly_item_id = i_phantom_item_id
and bbom.organization_id = i_organization_id
and bbom.alternate_bom_designator is null
and bic.bill_sequence_id = bbom.common_bill_sequence_id
and bic.effectivity_date <= sysdate
and (bic.disable_date >= sysdate or
bic.disable_date is null)
and i_asset_number >= bic.from_end_item_unit_number
and (i_asset_number <= bic.to_end_item_unit_number or
bic.to_end_item_unit_number is null)
and msik.organization_id = i_organization_id
and msik.inventory_item_id = bic.component_item_id
and lu.lookup_type(+) = g_supply_type
and lu.lookup_code(+) = bic.wip_supply_type
order by component_sequence_id;
l_phantom_bom.delete;
o_bom_table.delete;
select common_bill_sequence_id
into l_bill_sequence_id
from bom_bill_of_materials
where organization_id = i_organization_id
and assembly_item_id = i_asset_group_id
and alternate_bom_designator is null;
SELECT
NVL(msi.eam_item_type,-1) INTO l_eam_item_type
FROM
mtl_system_items_b msi
WHERE
msi.inventory_item_id = i_asset_group_id
and organization_id = i_organization_id;
select bic.component_item_id
into l_component_item_id
from bom_bill_of_materials bbom,
bom_inventory_components bic
where bbom.assembly_item_id = i_assembly_item_id
and bbom.organization_id = i_organization_id
and bbom.alternate_bom_designator is null
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = i_component_item_id
and bic.disable_date is null /*consider only enabled components, added for #6072910*/
and i_serial_number >= bic.from_end_item_unit_number
and (i_serial_number <= bic.to_end_item_unit_number or
bic.to_end_item_unit_number is null);
select common_bill_sequence_id
into l_bill_sequence_id
from bom_bill_of_materials
where organization_id = i_organization_id
and assembly_item_id = i_assembly_item_id
and alternate_bom_designator is null;
select nvl(max(item_num),100)
into l_item_sequence_num
from bom_inventory_components
where bill_sequence_id=l_bill_sequence_id;
select concatenated_segments
into l_assembly_item_name
from mtl_system_items_kfv
where organization_id = i_organization_id
and inventory_item_id = i_asset_group_id;
select operation_seq_num
from wip_operations
where wip_entity_id = i_wip_entity_id;
select min(start_date), max(completion_date)
into l_op_start_date, l_op_completion_date
from wip_operation_resources
where wip_entity_id = i_wip_entity_id
and operation_seq_num = l_op_seq_num;
update wip_operations set
first_unit_start_date = l_op_start_date,
first_unit_completion_date = l_op_completion_date,
last_unit_start_date = l_op_start_date,
last_unit_completion_date = l_op_completion_date
where wip_entity_id = i_wip_entity_id
and operation_seq_num = l_op_seq_num;
select min(first_unit_start_date), max(last_unit_completion_date)
into l_wo_start_date, l_wo_completion_date
from wip_operations
where wip_entity_id = i_wip_entity_id;
update wip_discrete_jobs set
scheduled_start_date = l_wo_start_date,
scheduled_completion_date = l_wo_completion_date
where wip_entity_id = i_wip_entity_id;
select operation_seq_num
from wip_operations
where wip_entity_id = i_wip_entity_id;
| insert into lmtmp (wip_entity_id, op_count)values(
| i_wip_entity_id, 0);--i_operation_table.count);
| insert into lmtmp(wip_entity_id, op_count)values(
| -1, l_index);
| insert into lmtmp (wip_entity_id, op_count)values(
| l_op, l_shift);
select min(first_unit_start_date), max(last_unit_completion_date)
into l_wo_start_date, l_wo_completion_date
from wip_operations
where wip_entity_id = i_wip_entity_id;
update wip_discrete_jobs set
scheduled_start_date = l_wo_start_date,
scheduled_completion_date = l_wo_completion_date
where wip_entity_id = i_wip_entity_id;
update wip_operation_resources set
start_date = start_date, -- + i_operation_table(l_index).time_shift,
completion_date = completion_date --+ i_operation_table(l_index).time_shift
where wip_entity_id = i_wip_entity_id
and operation_seq_num = i_operation_table(l_index).operation_seq_num;
update wip_operations set
first_unit_start_date = first_unit_start_date,
first_unit_completion_date = first_unit_completion_date,
last_unit_start_date = last_unit_start_date,
last_unit_completion_date = last_unit_completion_date
where wip_entity_id = i_wip_entity_id;
update wip_operation_resources set
start_date = start_date,
completion_date = completion_date
where wip_entity_id = i_wip_entity_id;
select prior_operation, next_operation
from wip_operation_networks
where wip_entity_id = i_wip_entity_id;
select first_unit_start_date, last_unit_completion_date
into l_start_date_from, l_end_date_from
from wip_operations
where wip_entity_id = i_wip_entity_id
and operation_seq_num = l_from_op;
select first_unit_start_date, last_unit_completion_date
into l_start_date_to, l_end_date_to
from wip_operations
where wip_entity_id = i_wip_entity_id
and operation_seq_num = l_to_op;
select count(*) as func_count
from
( select level as entry_level, function_id
from fnd_menu_entries me
start with menu_id = p_menu_id
connect by prior sub_menu_id = menu_id
) e
where e.function_id = p_function_id;
select fr.responsibility_key, fr.responsibility_id, fr.application_id,
fr.menu_id
from fnd_user fu, fnd_responsibility fr, fnd_user_resp_groups furg
where fu.user_id = p_user_id
and furg.user_id = fu.user_id
and fr.responsibility_id = furg.responsibility_id
and fr.application_id = furg.responsibility_application_id
and nvl(fr.start_date, sysdate) <= sysdate
and nvl(fr.end_date, sysdate) >= sysdate
and nvl(furg.start_date, sysdate) <= sysdate
and nvl(furg.end_date, sysdate) >= sysdate
and nvl(p_resp_app_id, fr.application_id) = fr.application_id
and eam_workorder_util_pkg.menu_has_function(fr.menu_id, p_function_id) > 0
and NVL(l_org_id, fnd_profile.value_specific('ORG_ID', NULL,
fr.responsibility_id, furg.responsibility_application_id)) =
fnd_profile.value_specific('ORG_ID', NULL,
fr.responsibility_id, furg.responsibility_application_id)
and ROWNUM=1;
select fr.responsibility_key, fr.responsibility_id, fr.application_id,
fr.menu_id
from fnd_user fu, fnd_responsibility fr, fnd_user_resp_groups furg, org_access_view oav
where fu.user_id = p_user_id
and furg.user_id = fu.user_id
and fr.responsibility_id = furg.responsibility_id
and fr.application_id = furg.responsibility_application_id
and nvl(fr.start_date, sysdate) <= sysdate
and nvl(fr.end_date, sysdate) >= sysdate
and nvl(furg.start_date, sysdate) <= sysdate
and nvl(furg.end_date, sysdate) >= sysdate
and nvl(p_resp_app_id, fr.application_id) = fr.application_id
and eam_workorder_util_pkg.menu_has_function(fr.menu_id, p_function_id) > 0
and oav.responsibility_id=fr.responsibility_id
and oav.organization_id=l_organization_id
and oav.resp_application_id=426
and ROWNUM=1;
select function_id
into l_ip_function_id
from fnd_form_functions
where function_name = l_function_name; -- Fix for Bug 3756518
select function_id
into l_eam_function_id
from fnd_form_functions
where function_name = l_function_name;
SELECT UOM_CLASS INTO hour_uom_class_code
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = X_Hour_UOM_Code;
SELECT UOM_CLASS INTO uom_class_code
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = X_Unit_Of_Measure;
SELECT COUNT(*) INTO conversion_exists
FROM MTL_UOM_CONVERSIONS muc1,
MTL_UOM_CONVERSIONS muc2
WHERE muc1.UOM_CLASS = uom_class_code
AND muc1.UOM_CODE = X_Unit_Of_Measure
AND muc1.inventory_item_id = 0
AND nvl(muc1.disable_date, sysdate +1) > sysdate
AND muc2.uom_code = X_Hour_Uom_Code
AND muc2.inventory_item_id = 0
AND muc2.uom_class = muc1.uom_class;
/* select ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left
into quantity_left
from wip_eam_direct_items_v
where work_order_number = x_wip_id; */
SELECT
((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left into quantity_left
FROM
(
SELECT
rql.wip_entity_id,
rql.quantity quantity_ordered,
to_number(null) quantity_received
FROM po_requisition_lines_all rql,
po_requisition_headers_all rqh,
po_line_types plt
WHERE rql.requisition_header_id = rqh.requisition_header_id
AND rql.line_type_id = plt.line_type_id
AND upper(rqh.authorization_status) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND rql.line_location_id is NULL
AND upper(nvl(rql.cancel_flag, 'N')) <> 'Y'
AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
AND rql.destination_type_code = 'SHOP FLOOR'
AND rql.wip_entity_id is not null
UNION
(
SELECT
pd.wip_entity_id,
sum(pd.quantity_ordered) quantity_ordered,
sum(pd.quantity_delivered) quantity_received
FROM po_line_types plt,
(
SELECT
pd1.wip_entity_id,
pd1.wip_operation_seq_num,
pd1.destination_organization_id,
pol.item_description,
pol.unit_price,
pol.quantity,
pd1.quantity_delivered,
pd1.quantity_ordered,
pd1.quantity_cancelled,
pol.po_line_id,
pol.po_header_id,
pd1.req_distribution_id,
pd1.line_location_id,
pol.line_type_id,
pd1.destination_type_code,
pol.cancel_flag,
pol.item_id,
pol.category_id ,
pd1.po_release_id,
pd1.amount_ordered,
pd1.amount_delivered
FROM po_lines_all pol,
po_distributions_all pd1
WHERE pol.po_line_id = pd1.po_line_id
)
pd
WHERE pd.line_type_id = plt.line_type_id
AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
AND pd.destination_type_code = 'SHOP FLOOR'
AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y'
AND pd.wip_entity_id is not null
GROUP BY pd.wip_entity_id,
pd.amount_ordered,
pd.amount_delivered
)
)
WHERE wip_entity_id = x_wip_id ;
SELECT '1'
into charges_exist_1
FROM WIP_DISCRETE_JOBS DJ, WIP_PERIOD_BALANCES WPB
WHERE DJ.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
AND DJ.ORGANIZATION_ID = WPB.ORGANIZATION_ID
AND DJ.WIP_ENTITY_ID = x_wip_id
AND DJ.ORGANIZATION_ID = x_org_id
AND (DJ.QUANTITY_COMPLETED <> 0
OR DJ.QUANTITY_SCRAPPED <> 0
OR WPB.TL_RESOURCE_IN <> 0
OR WPB.TL_OVERHEAD_IN <> 0
OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.PL_MATERIAL_IN <> 0
OR WPB.PL_MATERIAL_OVERHEAD_IN <> 0
OR WPB.PL_RESOURCE_IN <> 0
OR WPB.PL_OVERHEAD_IN <> 0
OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.TL_MATERIAL_OUT <> 0
OR WPB.TL_RESOURCE_OUT <> 0
OR WPB.TL_OVERHEAD_OUT <> 0
OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
OR WPB.PL_MATERIAL_OUT <> 0
OR WPB.PL_MATERIAL_OVERHEAD_OUT <> 0
OR WPB.PL_RESOURCE_OUT <> 0
OR WPB.PL_OVERHEAD_OUT <> 0
OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0)
AND ROWNUM <= 1;
SELECT '1'
into charges_exist_2
FROM DUAL /*fix for 2414244 */
WHERE EXISTS (SELECT '1'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id
AND QUANTITY_ISSUED <> 0)
OR EXISTS (SELECT '1'
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id)
OR EXISTS (SELECT '1'
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id);
SELECT '1'
INTO charges_exist_3
FROM dual
WHERE EXISTS (SELECT '1'
FROM mtl_material_transactions_temp
WHERE organization_id = x_org_id
AND transaction_source_type_id = 5
AND transaction_source_id = x_wip_id);
SELECT DISTINCT '1'
into charges_exist_3
FROM dual
where EXISTS (SELECT '1'
FROM WIP_OPERATION_RESOURCES
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id
AND APPLIED_RESOURCE_UNITS <> 0);
UPDATE WIP_OPERATIONS
SET QUANTITY_WAITING_TO_MOVE = 0,
QUANTITY_SCRAPPED = 0,
QUANTITY_REJECTED = 0,
QUANTITY_IN_QUEUE = 0,
QUANTITY_RUNNING = 0,
QUANTITY_COMPLETED = 0
WHERE WIP_ENTITY_ID = x_wip_id
AND ORGANIZATION_ID = x_org_id;
DELETE FROM wip_period_balances
WHERE wip_entity_id = x_wip_id
AND NVL(repetitive_schedule_id, -1) =
NVL(x_rep_id, -1)
AND organization_id = x_org_id;
/* select ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left
into quantity_left
from wip_eam_direct_items_v
where work_order_number = x_wip_id;
SELECT
((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left into quantity_left
FROM
(
SELECT
rql.wip_entity_id,
rql.quantity quantity_ordered,
to_number(null) quantity_received
FROM po_requisition_lines_all rql,
po_requisition_headers_all rqh,
po_line_types plt
WHERE rql.requisition_header_id = rqh.requisition_header_id
AND rql.line_type_id = plt.line_type_id
AND upper(rqh.authorization_status) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND rql.line_location_id is NULL
AND upper(nvl(rql.cancel_flag, 'N')) <> 'Y'
AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
AND rql.destination_type_code = 'SHOP FLOOR'
AND rql.wip_entity_id is not null
UNION
(
SELECT
pd.wip_entity_id,
sum(pd.quantity_ordered) quantity_ordered,
sum(pd.quantity_delivered) quantity_received
FROM po_line_types plt,
(
SELECT
pd1.wip_entity_id,
pd1.wip_operation_seq_num,
pd1.destination_organization_id,
pol.item_description,
pol.unit_price,
pol.quantity,
pd1.quantity_delivered,
pd1.quantity_ordered,
pd1.quantity_cancelled,
pol.po_line_id,
pol.po_header_id,
pd1.req_distribution_id,
pd1.line_location_id,
pol.line_type_id,
pd1.destination_type_code,
pol.cancel_flag,
pol.item_id,
pol.category_id ,
pd1.po_release_id,
pd1.amount_ordered,
pd1.amount_delivered
FROM po_lines_all pol,
po_distributions_all pd1
WHERE pol.po_line_id = pd1.po_line_id
)
pd
WHERE pd.line_type_id = plt.line_type_id
AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
AND pd.destination_type_code = 'SHOP FLOOR'
AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y'
AND pd.wip_entity_id is not null
GROUP BY pd.wip_entity_id,
pd.amount_ordered,
pd.amount_delivered
)
)
WHERE wip_entity_id = x_wip_id;
SELECT '1'
into charges_exist_1
FROM WIP_DISCRETE_JOBS DJ, WIP_PERIOD_BALANCES WPB
WHERE DJ.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
AND DJ.ORGANIZATION_ID = WPB.ORGANIZATION_ID
AND DJ.WIP_ENTITY_ID = x_wip_id
AND DJ.ORGANIZATION_ID = x_org_id
AND (DJ.QUANTITY_COMPLETED <> 0
OR DJ.QUANTITY_SCRAPPED <> 0
OR WPB.TL_RESOURCE_IN <> 0
OR WPB.TL_OVERHEAD_IN <> 0
OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.PL_MATERIAL_IN <> 0
OR WPB.PL_MATERIAL_OVERHEAD_IN <> 0
OR WPB.PL_RESOURCE_IN <> 0
OR WPB.PL_OVERHEAD_IN <> 0
OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.TL_MATERIAL_OUT <> 0
OR WPB.TL_RESOURCE_OUT <> 0
OR WPB.TL_OVERHEAD_OUT <> 0
OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
OR WPB.PL_MATERIAL_OUT <> 0
OR WPB.PL_MATERIAL_OVERHEAD_OUT <> 0
OR WPB.PL_RESOURCE_OUT <> 0
OR WPB.PL_OVERHEAD_OUT <> 0
OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0)
AND ROWNUM <= 1;
SELECT '1'
into charges_exist_2
FROM DUAL /*fix for 2414244 */
WHERE EXISTS (SELECT '1'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id
AND QUANTITY_ISSUED <> 0)
OR EXISTS (SELECT '1'
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id)
OR EXISTS (SELECT '1'
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id);
SELECT DISTINCT '1'
into charges_exist_3
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID = x_org_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = x_wip_id;
SELECT DISTINCT '1'
into charges_exist_3
FROM dual
where EXISTS (SELECT '1'
FROM WIP_OPERATION_RESOURCES
WHERE ORGANIZATION_ID = x_org_id
AND WIP_ENTITY_ID = x_wip_id
AND APPLIED_RESOURCE_UNITS <> 0);
SELECT value
INTO l_full_path
FROM v$parameter
WHERE name = 'utl_file_dir';
--get the first dir in the list. Removed select statement for bug# 3805306
l_index := instr(l_full_path,',')-1;
SELECT ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0)))
qty_left
INTO quantity_left
FROM wip_eam_direct_items_v
WHERE work_order_number = p_wip_id
AND rownum <=1;
SELECT
((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left INTO quantity_left
FROM
(
SELECT
rql.wip_entity_id,
rql.quantity quantity_ordered,
to_number(null) quantity_received
FROM po_requisition_lines_all rql,
po_requisition_headers_all rqh,
po_line_types plt
WHERE rql.requisition_header_id = rqh.requisition_header_id
AND rql.line_type_id = plt.line_type_id
AND upper(rqh.authorization_status) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND rql.line_location_id is NULL
AND upper(nvl(rql.cancel_flag, 'N')) <> 'Y'
AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
AND rql.destination_type_code = 'SHOP FLOOR'
AND rql.wip_entity_id is not null
UNION
(
SELECT
pd.wip_entity_id,
sum(pd.quantity_ordered) quantity_ordered,
sum(pd.quantity_delivered) quantity_received
FROM po_line_types plt,
(
SELECT
pd1.wip_entity_id,
pd1.wip_operation_seq_num,
pd1.destination_organization_id,
pol.item_description,
pol.unit_price,
pol.quantity,
pd1.quantity_delivered,
pd1.quantity_ordered,
pd1.quantity_cancelled,
pol.po_line_id,
pol.po_header_id,
pd1.req_distribution_id,
pd1.line_location_id,
pol.line_type_id,
pd1.destination_type_code,
pol.cancel_flag,
pol.item_id,
pol.category_id ,
pd1.po_release_id,
pd1.amount_ordered,
pd1.amount_delivered
FROM po_lines_all pol,
po_distributions_all pd1
WHERE pol.po_line_id = pd1.po_line_id
)
pd
WHERE pd.line_type_id = plt.line_type_id
AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
AND pd.destination_type_code = 'SHOP FLOOR'
AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y'
AND pd.wip_entity_id is not null
GROUP BY pd.wip_entity_id,
pd.amount_ordered,
pd.amount_delivered
)
)
WHERE wip_entity_id = p_wip_id
AND rownum <=1;
SELECT DISTINCT '1'
INTO charges_exist_2
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE (ORGANIZATION_ID = p_org_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = p_wip_id
AND rownum <=1)
OR EXISTS (SELECT '1'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = p_wip_id
AND QUANTITY_ISSUED <> 0);
SELECT DISTINCT '1'
into charges_exist_2
FROM dual
where EXISTS (SELECT '1'
FROM WIP_OPERATION_RESOURCES
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = p_wip_id
AND APPLIED_RESOURCE_UNITS <> 0);
SELECT '1'
INTO charges_exist_3
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = p_wip_id
AND QUANTITY_ISSUED <> 0)
OR EXISTS(SELECT '1'
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = p_wip_id)
OR EXISTS(SELECT '1'
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_org_id
AND WIP_ENTITY_ID = p_wip_id);
fnd_message.set_name('EAM','EAM_UPDATE_WO_TXN_OPEN');
fnd_message.set_name('EAM','EAM_UPDATE_WO_TXN_OPEN');
fnd_message.set_name('EAM','EAM_UPDATE_WO_TXN_OPEN');
SELECT 'PO/REQ Linked'
FROM PO_DISTRIBUTIONS_ALL PD,
PO_LINE_LOCATIONS_ALL PLL
WHERE pd.po_line_id IS NOT NULL
AND pd.line_location_id IS NOT NULL
AND PD.WIP_ENTITY_ID = p_wip_id
AND PD.DESTINATION_ORGANIZATION_ID = p_org_id
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND NOT(
NVL(PLL.CANCEL_FLAG,'N')='Y'
OR NVL(PLL.CLOSED_CODE,'N')='FINALLY CLOSED'
OR NVL(PLL.CLOSED_CODE,'N')='CANCELLED'
OR NVL(PLL.CLOSED_CODE,'N')='CLOSED FOR INVOICE'
OR NVL(PLL.CLOSED_CODE,'N')='CLOSED FOR RECEIVING'
OR NVL(PLL.CLOSED_CODE,'N')='CLOSED'
OR NVL(PLL.CLOSED_CODE,'N')='REJECTED'
)
UNION ALL
SELECT 'PO/REQ Linked'
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.WIP_ENTITY_ID = p_wip_id
AND PRL.DESTINATION_ORGANIZATION_ID = p_org_id
AND NOT(
NVL(PRL.CANCEL_FLAG,'N')='Y'
OR NVL(PRL.CLOSED_CODE,'N')='FINALLY CLOSED'
OR NVL(PRL.CLOSED_CODE,'N')='CANCELLED'
OR NVL(PRL.CLOSED_CODE,'N')='CLOSED FOR INVOICE'
OR NVL(PRL.CLOSED_CODE,'N')='CLOSED FOR RECEIVING'
OR NVL(PRL.CLOSED_CODE,'N')='CLOSED'
OR NVL(PRL.CLOSED_CODE,'N')='REJECTED'
)
UNION ALL
SELECT 'PO/REQ Linked'
FROM PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.WIP_ENTITY_ID = p_wip_id
AND PRI.DESTINATION_ORGANIZATION_ID = p_org_id
AND NVL(PRI.PROCESS_FLAG,'FUTURE') <> 'ERROR';
fnd_message.set_name ('EAM','EAM_UPDATE_WO_CONFIRM_CANCEL');
fnd_message.set_name ('EAM','EAM_UPDATE_WO_CONFIRM_CANCEL');
SELECT description
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_rebuild_item_id
AND organization_id = p_organization_id ;
SELECT telephone_number_1
FROM per_addresses
WHERE person_id = p_person_id
AND sysdate BETWEEN date_from AND date_to
AND primary_flag = 'Y';
SELECT telephone_number_1
FROM per_addresses
WHERE sysdate BETWEEN date_from AND date_to
AND primary_flag = 'Y'
AND person_id = (SELECT employee_id
FROM fnd_user
WHERE user_id = p_user_id);
SELECT email_address
FROM per_all_people_f
WHERE person_id = p_person_id
AND sysdate between effective_start_date AND effective_end_date;
SELECT email_address
FROM per_all_people_f
WHERE sysdate between effective_start_date AND effective_end_date
AND person_id = (SELECT employee_id
FROM fnd_user
WHERE user_id = p_user_id);
SELECT email_address
FROM fnd_user
WHERE user_id = p_user_id;
select 1
INTO l_temp
from eam_work_order_details ewod, wip_discrete_jobs wdj
where wdj.status_type not in (3,4,5,12)
and ewod.pending_flag = 'Y'
and ewod.wip_entity_id = wdj.wip_entity_id
and ewod.wip_entity_id = (select wip_entity_id from EAM_WO_WORKFLOWS where wf_item_key=p_item_key);
select fnd_application_id into l_transaction_appl_id from ame_calling_apps where transaction_type_id = l_transaction_type;
select TEXT_VALUE into x_workflow_table(i).comments from
wf_notification_attributes where notification_id =(select notification_id from wf_notifications where item_key=p_item_key and
responder=l_approversOut(i).name and status='CLOSED') and NAME='WF_NOTE';
UPDATE WIP_DISCRETE_JOBS
SET estimation_status = 2
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT wdj.maintenance_object_id
bulk collect into l_maint_objid_tbl
FROM WIP_DISCRETE_JOBS wdj
WHERE wdj.parent_wip_entity_id = p_wip_entity_id
AND wdj.organization_id = p_organization_id
AND wdj.manual_rebuild_flag = 'N'
AND wdj.maintenance_object_type = 3;
SELECT function_id
INTO l_msu_function_id
FROM fnd_form_functions
WHERE function_name = l_function_name;