[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT template_id INTO l_id FROM mtl_item_templates
WHERE template_name =l_pk1;
SELECT inventory_item_id INTO l_id FROM mtl_system_items_kfv
WHERE concatenated_segments = l_pk1
AND organization_id = l_pk2;
SELECT organization_id INTO l_id FROM mtl_parameters
WHERE organization_code = l_pk1;
SELECT item_catalog_group_id INTO l_id FROM mtl_item_catalog_groups_kfv
WHERE concatenated_segments = l_pk1;
SELECT proj_element_id INTO l_id FROM pa_ego_lifecycles_v
WHERE name = l_pk1;
SELECT proj_element_id INTO l_id from pa_ego_phases_v
WHERE name = l_pk1
AND parent_structure_id = l_pk2;
SELECT revision_id INTO l_id from mtl_item_rev_highdate_v MIRVH
WHERE organization_id = l_pk1
AND inventory_item_id = l_pk2
AND MIRVH.EFFECTIVITY_DATE < SYSDATE
AND decode(MIRVH.HIGH_DATE,SYSDATE,SYSDATE+1) > SYSDATE;
SELECT hazard_class_id INTO l_id from po_hazard_classes_vl
WHERE hazard_class = l_pk1;
SELECT category_id INTO l_id FROM fa_categories_b_kfv
WHERE concatenated_segments = l_pk1;
SELECT manufacturer_id INTO l_id FROM mtl_manufacturers
WHERE manufacturer_name = l_pk1;
SELECT category_set_id INTO l_id FROM mtl_category_sets_vl
WHERE category_set_name = l_pk1;
SELECT category_id INTO l_id FROM mtl_categories_kfv mck,mtl_category_sets_vl mcs
where mcs.category_set_name = l_pk2
and mcs.structure_id = mck.structure_id
and mck.concatenated_segments = l_pk1;
SELECT template_name INTO l_code
FROM mtl_item_templates_vl
WHERE template_id = l_pk1;
SELECT organization_code INTO l_code
FROM mtl_parameters
WHERE organization_id = l_pk1;
SELECT concatenated_segments INTO l_code
FROM mtl_item_catalog_groups_kfv
WHERE item_catalog_group_id = l_pk1;
SELECT name INTO l_code
FROM pa_ego_lifecycles_v
WHERE proj_element_id = l_pk1;
SELECT phase_code INTO l_code
FROM pa_ego_phases_v
WHERE proj_element_id = l_pk1;
SELECT revision_label INTO l_code
FROM mtl_item_revisions_vl
WHERE revision_id = l_pk1;
SELECT hazard_class INTO l_code
FROM po_hazard_classes_vl
WHERE hazard_class_id = l_pk1;
SELECT concatenated_segments INTO l_code
FROM fa_categories_b_kfv
WHERE category_id = l_pk1 ;
SELECT manufacturer_name INTO l_code
FROM mtl_manufacturers
WHERE manufacturer_id = l_pk1;
SELECT category_set_name INTO l_code
FROM mtl_category_sets_vl
WHERE CATEGORY_SET_ID = l_pk1;
SELECT concatenated_segments INTO l_code
FROM mtl_categories_kfv
WHERE CATEGORY_ID =l_pk1;
SELECT concatenated_segments INTO l_code
FROM mtl_system_items_b_kfv
WHERE organization_id = l_pk1
AND inventory_item_id = l_pk2;
SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = master_organization_id;
SELECT Length(l_rate_in_tbl)-InStr(l_rate_in_tbl,'.') INTO l_length1 FROM dual;
SELECT Length(l_conv_rate_calc)-InStr(l_conv_rate_calc,'.') INTO l_length2 FROM dual;
SELECT Round(l_conv_rate_calc,l_length1) INTO l_conv_rate_calc FROM dual;
SELECT Round(l_rate_in_tbl,l_length2) INTO l_rate_in_tbl FROM dual;
SELECT uom_code FROM mtl_units_of_measure WHERE uom_code = p_uom_code;
select UOM_CLASS into l_class1 from MTL_UNITS_OF_MEASURE where UOM_CODE=uom_conv_details(j).to_uom;
select UOM_CODE into l_baseunit1 from MTL_UNITS_OF_MEASURE where UOM_CLASS=l_class1 and BASE_UOM_FLAG='Y';
for l_list1_uom_rec in (select conversion_rate,UOM_CODE from MTL_UOM_CONVERSIONS where UOM_CLASS = l_class1 and inventory_item_id= 0)
loop
for i in 1..uom_conv_details.count loop
uom_from_v_list1_uom_cur := l_list1_uom_rec.UOM_CODE;
select UOM_CLASS into l_class2 from MTL_UNITS_OF_MEASURE where UOM_CODE=uom_conv_details(j).from_uom;
select UOM_CODE into l_baseunit2 from MTL_UNITS_OF_MEASURE where UOM_CLASS= l_class2 and BASE_UOM_FLAG='Y';
for l_list2_uom_rec in (select conversion_rate,UOM_CODE from MTL_UOM_CONVERSIONS where UOM_CLASS = l_class2 and inventory_item_id= 0) loop
for l in 1..uom_conv_details.Count loop
uom_from_v_list2_uom_cur := l_list2_uom_rec.UOM_CODE;
SELECT new_item_request_reqd INTO l_is_new_item_request_reqd
FROM mtl_item_catalog_groups_vl
WHERE item_catalog_group_id = p_item_catalog_group_id;
SELECT organization_id
INTO l_org_id
FROM mtl_parameters
WHERE organization_code = p_organization_code;
SELECT inventory_item_id INTO l_inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_number
AND organization_id = p_organization_id;
SELECT DECODE(p_segment1,fnd_api.g_miss_char,'',NULL,'',p_segment1) ||
DECODE(p_segment2,fnd_api.g_miss_char,'',NULL,'',p_segment2) ||
DECODE(p_segment3,fnd_api.g_miss_char,'',NULL,'',p_segment3) ||
DECODE(p_segment4,fnd_api.g_miss_char,'',NULL,'',p_segment4) ||
DECODE(p_segment5,fnd_api.g_miss_char,'',NULL,'',p_segment5) ||
DECODE(p_segment6,fnd_api.g_miss_char,'',NULL,'',p_segment6) ||
DECODE(p_segment7,fnd_api.g_miss_char,'',NULL,'',p_segment7) ||
DECODE(p_segment8,fnd_api.g_miss_char,'',NULL,'',p_segment8) ||
DECODE(p_segment9,fnd_api.g_miss_char,'',NULL,'',p_segment9) ||
DECODE(p_segment10,fnd_api.g_miss_char,'',NULL,'',p_segment10) ||
DECODE(p_segment11,fnd_api.g_miss_char,'',NULL,'',p_segment11) ||
DECODE(p_segment12,fnd_api.g_miss_char,'',NULL,'',p_segment12) ||
DECODE(p_segment13,fnd_api.g_miss_char,'',NULL,'',p_segment13) ||
DECODE(p_segment14,fnd_api.g_miss_char,'',NULL,'',p_segment14) ||
DECODE(p_segment15,fnd_api.g_miss_char,'',NULL,'',p_segment15) ||
DECODE(p_segment16,fnd_api.g_miss_char,'',NULL,'',p_segment16) ||
DECODE(p_segment17,fnd_api.g_miss_char,'',NULL,'',p_segment17) ||
DECODE(p_segment18,fnd_api.g_miss_char,'',NULL,'',p_segment18) ||
DECODE(p_segment19,fnd_api.g_miss_char,'',NULL,'',p_segment19) ||
DECODE(p_segment20,fnd_api.g_miss_char,'',NULL,'',p_segment20)
INTO l_item_number
FROM DUAL;
SELECT eng_item_flag INTO l_item_flag
FROM mtl_system_items_kfv
WHERE concatenated_segments = NVL(p_item_number,FND_API.G_MISS_CHAR)
AND organization_id =p_organization_id;
SELECT COUNT(1) INTO l_item_count
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_number
AND organization_id = p_organization_id;
SELECT COUNT(1) INTO l_count
FROM
mtl_item_revisions_b mir ,
mtl_system_items_kfv msi
WHERE
msi.concatenated_segments = p_item_number AND
msi.organization_id = p_organization_id AND
msi.inventory_item_id = mir.inventory_item_id AND
msi.organization_id = mir.organization_id AND
mir.revision = UPPER(p_revision);
SELECT item_desc_gen_method, parent_catalog_group_id
INTO l_item_desc_gen_method, l_parent_catalog_group_id
FROM MTL_ITEM_CATALOG_GROUPS_VL
WHERE item_catalog_group_id = p_item_catalog_group_id;
SELECT item_desc_gen_method,
parent_catalog_group_id
INTO l_item_desc_gen_method,
t_parent_catalog_group_id
FROM mtl_item_catalog_groups_vl
WHERE item_catalog_group_id = l_parent_catalog_group_id;
SELECT pev_p.proj_element_id
FROM pa_proj_element_versions pev_l
,pa_lifecycle_usages plu
,pa_proj_element_versions pev_p
,pa_proj_elements ppe_p
,pa_project_statuses pc
WHERE pev_l.object_type = 'PA_STRUCTURES'
AND pev_l.proj_element_id = p_lifecycle_id
AND pev_l.project_id = 0
AND plu.usage_type = 'PRODUCTS'
AND plu.lifecycle_id = pev_l.proj_element_id
AND pev_l.element_version_id = pev_p.parent_structure_version_id
AND pev_p.proj_element_id = ppe_p.proj_element_id
AND ppe_p.phase_code = pc.project_status_code
AND (pc.start_date_active IS NULL OR pc.start_date_active <= SYSDATE)
AND (pc.end_date_active IS NULL OR pc.end_date_active >= SYSDATE)
ORDER BY pev_p.display_sequence ;
SELECT status.ITEM_STATUS_CODE INTO l_status_code
FROM EGO_LCPHASE_ITEM_STATUS status
,PA_EGO_PHASES_V phases
WHERE phases.PROJ_ELEMENT_ID = p_phase_id
AND status.PHASE_CODE = phases.PHASE_CODE
AND DEFAULT_FLAG = 'Y';
l_apply_template_update VARCHAR2(2000);
SELECT description
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_number
AND organization_id = p_organization_id;
SELECT lifecycle_id ,current_phase_id ,inventory_item_status_code
FROM mtl_system_items_b
WHERE inventory_item_id = cp_item_id
AND organization_id = cp_org_id;
SELECT pev_p.PROJ_ELEMENT_ID
FROM PA_PROJ_ELEMENT_VERSIONS pev_l,
PA_LIFECYCLE_USAGES plu,
PA_PROJ_ELEMENT_VERSIONS pev_p,
PA_PROJ_ELEMENTS PPE_P,
PA_PROJECT_STATUSES pc
WHERE pev_l.OBJECT_TYPE = 'PA_STRUCTURES'
AND pev_l.PROJ_ELEMENT_ID = cp_lifecycle_id
AND pev_l.PROJECT_ID = 0
AND plu.USAGE_TYPE = 'PRODUCTS'
AND plu.LIFECYCLE_ID = pev_l.PROJ_ELEMENT_ID
AND pev_l.ELEMENT_VERSION_ID = pev_p.PARENT_STRUCTURE_VERSION_ID
AND pev_p.PROJ_ELEMENT_ID = ppe_p.PROJ_ELEMENT_ID
AND ppe_p.PHASE_CODE = pc.PROJECT_STATUS_CODE
AND (pc.START_DATE_ACTIVE IS NULL OR pc.START_DATE_ACTIVE <= SYSDATE)
AND (pc.END_DATE_ACTIVE IS NULL OR pc.END_DATE_ACTIVE >= SYSDATE)
ORDER BY pev_p.DISPLAY_SEQUENCE;*/
SELECT nls_language
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = p_language_code;
l_transaction_type := INV_EBI_ITEM_PUB.g_otype_update;
SELECT COUNT(1) into l_revised_item_exists
FROM mtl_item_revisions_b ir
WHERE ir.inventory_item_id = l_item.main_obj_type.inventory_item_id
AND ir.organization_id = l_item.main_obj_type.organization_id
AND ir.revision = l_item.main_obj_type.revision_code
AND(ir.effectivity_date IN
(SELECT first_value(ir2.effectivity_date) over(ORDER BY ir2.effectivity_date DESC)
FROM mtl_item_revisions_b ir2
WHERE ir2.organization_id = ir.organization_id
AND ir2.inventory_item_id = ir.inventory_item_id
AND ir2.effectivity_date <= sysdate
AND ir2.implementation_date IS NOT NULL)
OR ir.effectivity_date > sysdate);
SELECT default_template_id ,default_template_name INTO l_item.main_obj_type.template_id,l_item.main_obj_type.template_name
FROM ego_catalog_groups_v
WHERE catalog_group_id = l_item.main_obj_type.item_catalog_group_id;
IF(l_transaction_type = INV_EBI_ITEM_PUB.g_otype_update ) THEN
l_apply_template_update := INV_EBI_UTIL.get_config_param_value(
p_config_tbl => l_item.name_value_tbl
,p_config_param_name => 'TEMPLATE_FOR_ITEM_UPDATE_ALLOWED'
);
IF( UPPER(l_apply_template_update ) = fnd_api.g_false ) THEN
l_item.main_obj_type.template_id := fnd_api.g_miss_num;
,p_allow_item_desc_update_flag => l_item.purchasing_obj_type.allow_item_desc_update_flag
,p_rfq_required_flag => l_item.purchasing_obj_type.rfq_required_flag
,p_outside_operation_flag => l_item.purchasing_obj_type.outside_operation_flag
,p_outside_operation_uom_type => l_item.purchasing_obj_type.outside_operation_uom_type
,p_taxable_flag => l_item.purchasing_obj_type.taxable_flag
,p_purchasing_tax_code => l_item.purchasing_obj_type.purchasing_tax_code
,p_receipt_required_flag => l_item.purchasing_obj_type.receipt_required_flag
,p_inspection_required_flag => l_item.purchasing_obj_type.inspection_required_flag
,p_buyer_id => l_item.purchasing_obj_type.buyer_id
,p_unit_of_issue => l_item.purchasing_obj_type.unit_of_issue
,p_receive_close_tolerance => l_item.purchasing_obj_type.receive_close_tolerance
,p_invoice_close_tolerance => l_item.purchasing_obj_type.invoice_close_tolerance
,p_un_number_id => l_item.purchasing_obj_type.un_number_id
,p_hazard_class_id => l_item.purchasing_obj_type.hazard_class_id
,p_list_price_per_unit => l_item.purchasing_obj_type.list_price_per_unit
,p_market_price => l_item.purchasing_obj_type.market_price
,p_price_tolerance_percent => l_item.purchasing_obj_type.price_tolerance_percent
,p_rounding_factor => l_item.purchasing_obj_type.rounding_factor
,p_encumbrance_account => l_item.purchasing_obj_type.encumbrance_account
,p_expense_account => l_item.purchasing_obj_type.expense_account
,p_expense_billable_flag => l_item.deprecated_obj_type.expense_billable_flag
,p_asset_category_id => l_item.purchasing_obj_type.asset_category_id
,p_receipt_days_exception_code => l_item.receiving_obj_type.receipt_days_exception_code
,p_days_early_receipt_allowed => l_item.receiving_obj_type.days_early_receipt_allowed
,p_days_late_receipt_allowed => l_item.receiving_obj_type.days_late_receipt_allowed
,p_allow_substitute_receipts_f => l_item.receiving_obj_type.allow_substitute_receipts_f
,p_allow_unordered_receipts_fl => l_item.receiving_obj_type.allow_unordered_receipts_fl
,p_allow_express_delivery_flag => l_item.receiving_obj_type.allow_express_delivery_flag
,p_qty_rcv_exception_code => l_item.receiving_obj_type.qty_rcv_exception_code
,p_qty_rcv_tolerance => l_item.receiving_obj_type.qty_rcv_tolerance
,p_receiving_routing_id => l_item.receiving_obj_type.receiving_routing_id
,p_enforce_ship_to_location_c => l_item.receiving_obj_type.enforce_ship_to_location_c
,p_weight_uom_code => l_item.physical_obj_type.weight_uom_code
,p_unit_weight => l_item.physical_obj_type.unit_weight
,p_volume_uom_code => l_item.physical_obj_type.volume_uom_code
,p_unit_volume => l_item.physical_obj_type.unit_volume
,p_container_item_flag => l_item.physical_obj_type.container_item_flag
,p_vehicle_item_flag => l_item.physical_obj_type.vehicle_item_flag
,p_container_type_code => l_item.physical_obj_type.container_type_code
,p_internal_volume => l_item.physical_obj_type.internal_volume
,p_maximum_load_weight => l_item.physical_obj_type.maximum_load_weight
,p_minimum_fill_percent => l_item.physical_obj_type.minimum_fill_percent
,p_inventory_planning_code => l_item.gplanning_obj_type.inventory_planning_code
,p_planner_code => l_item.gplanning_obj_type.planner_code
,p_planning_make_buy_code => l_item.gplanning_obj_type.planning_make_buy_code
,p_min_minmax_quantity => l_item.gplanning_obj_type.min_minmax_quantity
,p_max_minmax_quantity => l_item.gplanning_obj_type.max_minmax_quantity
,p_minimum_order_quantity => l_item.gplanning_obj_type.minimum_order_quantity
,p_maximum_order_quantity => l_item.gplanning_obj_type.maximum_order_quantity
,p_order_cost => l_item.gplanning_obj_type.order_cost
,p_carrying_cost => l_item.gplanning_obj_type.carrying_cost
,p_source_type => l_item.gplanning_obj_type.source_type
,p_source_organization_id => l_item.gplanning_obj_type.source_organization_id
,p_source_subinventory => l_item.gplanning_obj_type.source_subinventory
,p_mrp_safety_stock_code => l_item.gplanning_obj_type.mrp_safety_stock_code
,p_safety_stock_bucket_days => l_item.gplanning_obj_type.safety_stock_bucket_days
,p_mrp_safety_stock_percent => l_item.gplanning_obj_type.mrp_safety_stock_percent
,p_fixed_order_quantity => l_item.gplanning_obj_type.fixed_order_quantity
,p_fixed_days_supply => l_item.gplanning_obj_type.fixed_days_supply
,p_fixed_lot_multiplier => l_item.gplanning_obj_type.fixed_lot_multiplier
,p_mrp_planning_code => l_item.mrp_obj_type.mrp_planning_code
,p_ato_forecast_control => l_item.mrp_obj_type.ato_forecast_control
,p_planning_exception_set => l_item.mrp_obj_type.planning_exception_set
,p_end_assembly_pegging_flag => l_item.mrp_obj_type.end_assembly_pegging_flag
,p_shrinkage_rate => l_item.mrp_obj_type.shrinkage_rate
,p_rounding_control_type => l_item.mrp_obj_type.rounding_control_type
,p_acceptable_early_days => l_item.mrp_obj_type.acceptable_early_days
,p_repetitive_planning_flag => l_item.mrp_obj_type.repetitive_planning_flag
,p_overrun_percentage => l_item.mrp_obj_type.overrun_percentage
,p_acceptable_rate_increase => l_item.mrp_obj_type.acceptable_rate_increase
,p_acceptable_rate_decrease => l_item.mrp_obj_type.acceptable_rate_decrease
,p_mrp_calculate_atp_flag => l_item.mrp_obj_type.mrp_calculate_atp_flag
,p_auto_reduce_mps => l_item.mrp_obj_type.auto_reduce_mps
,p_planning_time_fence_code => l_item.mrp_obj_type.planning_time_fence_code
,p_planning_time_fence_days => l_item.mrp_obj_type.planning_time_fence_days
,p_demand_time_fence_code => l_item.mrp_obj_type.demand_time_fence_code
,p_demand_time_fence_days => l_item.mrp_obj_type.demand_time_fence_days
,p_release_time_fence_code => l_item.mrp_obj_type.release_time_fence_code
,p_release_time_fence_days => l_item.mrp_obj_type.release_time_fence_days
,p_preprocessing_lead_time => l_item.lead_time_obj_type.preprocessing_lead_time
,p_full_lead_time => l_item.lead_time_obj_type.full_lead_time
,p_postprocessing_lead_time => l_item.lead_time_obj_type.postprocessing_lead_time
,p_fixed_lead_time => l_item.lead_time_obj_type.fixed_lead_time
,p_variable_lead_time => l_item.lead_time_obj_type.variable_lead_time
,p_cum_manufacturing_lead_time => l_item.lead_time_obj_type.cum_manufacturing_lead_time
,p_cumulative_total_lead_time => l_item.lead_time_obj_type.cumulative_total_lead_time
,p_lead_time_lot_size => l_item.lead_time_obj_type.lead_time_lot_size
,p_build_in_wip_flag => l_item.wip_obj_type.build_in_wip_flag
,p_wip_supply_type => l_item.wip_obj_type.wip_supply_type
,p_wip_supply_subinventory => l_item.wip_obj_type.wip_supply_subinventory
,p_wip_supply_locator_id => l_item.wip_obj_type.wip_supply_locator_id
,p_overcompletion_tolerance_ty => l_item.wip_obj_type.overcompletion_tolerance_ty
,p_overcompletion_tolerance_va => l_item.wip_obj_type.overcompletion_tolerance_va
,p_customer_order_flag => l_item.order_obj_type.customer_order_flag
,p_customer_order_enabled_flag => l_item.order_obj_type.customer_order_enabled_flag
,p_shippable_item_flag => l_item.order_obj_type.shippable_item_flag
,p_internal_order_flag => l_item.order_obj_type.internal_order_flag
,p_internal_order_enabled_flag => l_item.order_obj_type.internal_order_enabled_flag
,p_so_transactions_flag => l_item.order_obj_type.so_transactions_flag
,p_pick_components_flag => l_item.order_obj_type.pick_components_flag
,p_atp_flag => l_item.order_obj_type.atp_flag
,p_replenish_to_order_flag => l_item.order_obj_type.replenish_to_order_flag
,p_atp_rule_id => l_item.order_obj_type.atp_rule_id
,p_atp_components_flag => l_item.order_obj_type.atp_components_flag
,p_ship_model_complete_flag => l_item.order_obj_type.ship_model_complete_flag
,p_picking_rule_id => l_item.order_obj_type.picking_rule_id
,p_collateral_flag => l_item.order_obj_type.collateral_flag
,p_default_shipping_org => l_item.order_obj_type.default_shipping_org
,p_returnable_flag => l_item.order_obj_type.returnable_flag
,p_return_inspection_requireme => l_item.order_obj_type.return_inspection_requireme
,p_over_shipment_tolerance => l_item.order_obj_type.over_shipment_tolerance
,p_under_shipment_tolerance => l_item.order_obj_type.under_shipment_tolerance
,p_over_return_tolerance => l_item.order_obj_type.over_return_tolerance
,p_under_return_tolerance => l_item.order_obj_type.under_return_tolerance
,p_invoiceable_item_flag => l_item.invoice_obj_type.invoiceable_item_flag
,p_invoice_enabled_flag => l_item.invoice_obj_type.invoice_enabled_flag
,p_accounting_rule_id => l_item.invoice_obj_type.accounting_rule_id
,p_invoicing_rule_id => l_item.invoice_obj_type.invoicing_rule_id
,p_tax_code => l_item.invoice_obj_type.tax_code
,p_sales_account => l_item.invoice_obj_type.sales_account
,p_payment_terms_id => l_item.invoice_obj_type.payment_terms_id
,p_coverage_schedule_id => l_item.service_obj_type.coverage_schedule_id
,p_service_duration => l_item.service_obj_type.service_duration
,p_service_duration_period_cod => l_item.service_obj_type.service_duration_period_cod
,p_serviceable_product_flag => l_item.service_obj_type.serviceable_product_flag
,p_service_starting_delay => l_item.service_obj_type.service_starting_delay
,p_material_billable_flag => l_item.service_obj_type.material_billable_flag
,p_serviceable_component_flag => l_item.deprecated_obj_type.serviceable_component_flag
,p_preventive_maintenance_flag => l_item.deprecated_obj_type.preventive_maintenance_flag
,p_prorate_service_flag => l_item.deprecated_obj_type.prorate_service_flag
,p_serviceable_item_class_id => l_item.deprecated_obj_type.serviceable_item_class_id
,p_base_warranty_service_id => l_item.deprecated_obj_type.base_warranty_service_id
,p_warranty_vendor_id => l_item.deprecated_obj_type.warranty_vendor_id
,p_max_warranty_amount => l_item.deprecated_obj_type.max_warranty_amount
,p_response_time_period_code => l_item.deprecated_obj_type.response_time_period_code
,p_response_time_value => l_item.deprecated_obj_type.response_time_value
,p_primary_specialist_id => l_item.deprecated_obj_type.primary_specialist_id
,p_secondary_specialist_id => l_item.deprecated_obj_type.secondary_specialist_id
,p_wh_update_date => l_item.deprecated_obj_type.wh_update_date
,p_equipment_type => l_item.physical_obj_type.equipment_type
,p_recovered_part_disp_code => l_item.service_obj_type.recovered_part_disp_code
,p_defect_tracking_on_flag => l_item.service_obj_type.defect_tracking_on_flag
,p_event_flag => l_item.physical_obj_type.event_flag
,p_electronic_flag => l_item.physical_obj_type.electronic_flag
,p_downloadable_flag => l_item.physical_obj_type.downloadable_flag
,p_vol_discount_exempt_flag => l_item.deprecated_obj_type.vol_discount_exempt_flag
,p_coupon_exempt_flag => l_item.deprecated_obj_type.coupon_exempt_flag
,p_comms_nl_trackable_flag => l_item.service_obj_type.comms_nl_trackable_flag
,p_asset_creation_code => l_item.service_obj_type.asset_creation_code
,p_comms_activation_reqd_flag => l_item.deprecated_obj_type.comms_activation_reqd_flag
,p_orderable_on_web_flag => l_item.web_option_obj_type.orderable_on_web_flag
,p_back_orderable_flag => l_item.web_option_obj_type.back_orderable_flag
,p_web_status => l_item.web_option_obj_type.web_status
,p_indivisible_flag => l_item.physical_obj_type.indivisible_flag
,p_dimension_uom_code => l_item.physical_obj_type.dimension_uom_code
,p_unit_length => l_item.physical_obj_type.unit_length
,p_unit_width => l_item.physical_obj_type.unit_width
,p_unit_height => l_item.physical_obj_type.unit_height
,p_bulk_picked_flag => l_item.inventory_obj_type.bulk_picked_flag
,p_lot_status_enabled => l_item.inventory_obj_type.lot_status_enabled
,p_default_lot_status_id => l_item.deprecated_obj_type.default_lot_status_id
,p_serial_status_enabled => l_item.inventory_obj_type.serial_status_enabled
,p_default_serial_status_id => l_item.deprecated_obj_type.default_serial_status_id
,p_lot_split_enabled => l_item.inventory_obj_type.lot_split_enabled
,p_lot_merge_enabled => l_item.inventory_obj_type.lot_merge_enabled
,p_inventory_carry_penalty => l_item.wip_obj_type.inventory_carry_penalty
,p_operation_slack_penalty => l_item.wip_obj_type.operation_slack_penalty
,p_financing_allowed_flag => l_item.order_obj_type.financing_allowed_flag
,p_eam_item_type => l_item.asset_obj_type.eam_item_type
,p_eam_activity_type_code => l_item.asset_obj_type.eam_activity_type_code
,p_eam_activity_cause_code => l_item.asset_obj_type.eam_activity_cause_code
,p_eam_act_notification_flag => l_item.asset_obj_type.eam_act_notification_flag
,p_eam_act_shutdown_status => l_item.asset_obj_type.eam_act_shutdown_status
,p_dual_uom_control => l_item.deprecated_obj_type.dual_uom_control
,p_secondary_uom_code => l_item.main_obj_type.secondary_uom_code
,p_dual_uom_deviation_high => l_item.main_obj_type.dual_uom_deviation_high
,p_dual_uom_deviation_low => l_item.main_obj_type.dual_uom_deviation_low
,p_contract_item_type_code => l_item.asset_obj_type.contract_item_type_code
,p_subscription_depend_flag => l_item.deprecated_obj_type.subscription_depend_flag
,p_serv_req_enabled_code => l_item.asset_obj_type.serv_req_enabled_code
,p_serv_billing_enabled_flag => l_item.asset_obj_type.serv_billing_enabled_flag
,p_serv_importance_level => l_item.deprecated_obj_type.serv_importance_level
,p_planned_inv_point_flag => l_item.mrp_obj_type.planned_inv_point_flag
,p_lot_translate_enabled => l_item.inventory_obj_type.lot_translate_enabled
,p_default_so_source_type => l_item.order_obj_type.default_so_source_type
,p_create_supply_flag => l_item.mrp_obj_type.create_supply_flag
,p_substitution_window_code => l_item.mrp_obj_type.substitution_window_code
,p_substitution_window_days => l_item.mrp_obj_type.substitution_window_days
,p_ib_item_instance_class => l_item.service_obj_type.ib_item_instance_class
,p_config_model_type => l_item.bom_obj_type.config_model_type
,p_lot_substitution_enabled => l_item.inventory_obj_type.lot_substitution_enabled
,p_minimum_license_quantity => l_item.web_option_obj_type.minimum_license_quantity
,p_eam_activity_source_code => l_item.asset_obj_type.eam_activity_source_code
,p_approval_status => l_item.deprecated_obj_type.approval_status
,p_tracking_quantity_ind => l_item.main_obj_type.tracking_quantity_ind
,p_ont_pricing_qty_source => l_item.main_obj_type.ont_pricing_qty_source
,p_secondary_default_ind => l_item.main_obj_type.secondary_default_ind
,p_option_specific_sourced => l_item.deprecated_obj_type.option_specific_sourced
,p_vmi_minimum_units => l_item.gplanning_obj_type.vmi_minimum_units
,p_vmi_minimum_days => l_item.gplanning_obj_type.vmi_minimum_days
,p_vmi_maximum_units => l_item.gplanning_obj_type.vmi_maximum_units
,p_vmi_maximum_days => l_item.gplanning_obj_type.vmi_maximum_days
,p_vmi_fixed_order_quantity => l_item.gplanning_obj_type.vmi_fixed_order_quantity
,p_so_authorization_flag => l_item.gplanning_obj_type.so_authorization_flag
,p_consigned_flag => l_item.gplanning_obj_type.consigned_flag
,p_asn_autoexpire_flag => l_item.gplanning_obj_type.asn_autoexpire_flag
,p_vmi_forecast_type => l_item.gplanning_obj_type.vmi_forecast_type
,p_forecast_horizon => l_item.gplanning_obj_type.forecast_horizon
,p_exclude_from_budget_flag => l_item.mrp_obj_type.exclude_from_budget_flag
,p_days_tgt_inv_supply => l_item.mrp_obj_type.days_tgt_inv_supply
,p_days_tgt_inv_window => l_item.mrp_obj_type.days_tgt_inv_window
,p_days_max_inv_supply => l_item.mrp_obj_type.days_max_inv_supply
,p_days_max_inv_window => l_item.mrp_obj_type.days_max_inv_window
,p_drp_planned_flag => l_item.mrp_obj_type.drp_planned_flag
,p_critical_component_flag => l_item.mrp_obj_type.critical_component_flag
,p_continous_transfer => l_item.mrp_obj_type.continous_transfer
,p_convergence => l_item.mrp_obj_type.convergence
,p_divergence => l_item.mrp_obj_type.divergence
,p_config_orgs => l_item.bom_obj_type.config_orgs
,p_config_match => l_item.bom_obj_type.config_match
,p_item_number => l_item.main_obj_type.item_number
,p_segment1 => l_item.main_obj_type.segment1
,p_segment2 => l_item.main_obj_type.segment2
,p_segment3 => l_item.main_obj_type.segment3
,p_segment4 => l_item.main_obj_type.segment4
,p_segment5 => l_item.main_obj_type.segment5
,p_segment6 => l_item.main_obj_type.segment6
,p_segment7 => l_item.main_obj_type.segment7
,p_segment8 => l_item.main_obj_type.segment8
,p_segment9 => l_item.main_obj_type.segment9
,p_segment10 => l_item.main_obj_type.segment10
,p_segment11 => l_item.main_obj_type.segment11
,p_segment12 => l_item.main_obj_type.segment12
,p_segment13 => l_item.main_obj_type.segment13
,p_segment14 => l_item.main_obj_type.segment14
,p_segment15 => l_item.main_obj_type.segment15
,p_segment16 => l_item.main_obj_type.segment16
,p_segment17 => l_item.main_obj_type.segment17
,p_segment18 => l_item.main_obj_type.segment18
,p_segment19 => l_item.main_obj_type.segment19
,p_segment20 => l_item.main_obj_type.segment20
,p_summary_flag => l_item.main_obj_type.summary_flag
,p_enabled_flag => l_item.main_obj_type.enabled_flag
,p_start_date_active => l_item.main_obj_type.start_date_active
,p_end_date_active => l_item.main_obj_type.end_date_active
,p_attribute_category => l_item.custom_obj_type.attribute_category
,p_attribute1 => l_item.custom_obj_type.attribute1
,p_attribute2 => l_item.custom_obj_type.attribute2
,p_attribute3 => l_item.custom_obj_type.attribute3
,p_attribute4 => l_item.custom_obj_type.attribute4
,p_attribute5 => l_item.custom_obj_type.attribute5
,p_attribute6 => l_item.custom_obj_type.attribute6
,p_attribute7 => l_item.custom_obj_type.attribute7
,p_attribute8 => l_item.custom_obj_type.attribute8
,p_attribute9 => l_item.custom_obj_type.attribute9
,p_attribute10 => l_item.custom_obj_type.attribute10
,p_attribute11 => l_item.custom_obj_type.attribute11
,p_attribute12 => l_item.custom_obj_type.attribute12
,p_attribute13 => l_item.custom_obj_type.attribute13
,p_attribute14 => l_item.custom_obj_type.attribute14
,p_attribute15 => l_item.custom_obj_type.attribute15
,p_attribute16 => l_item.custom_obj_type.attribute16
,p_attribute17 => l_item.custom_obj_type.attribute17
,p_attribute18 => l_item.custom_obj_type.attribute18
,p_attribute19 => l_item.custom_obj_type.attribute19
,p_attribute20 => l_item.custom_obj_type.attribute20
,p_attribute21 => l_item.custom_obj_type.attribute21
,p_attribute22 => l_item.custom_obj_type.attribute22
,p_attribute23 => l_item.custom_obj_type.attribute23
,p_attribute24 => l_item.custom_obj_type.attribute24
,p_attribute25 => l_item.custom_obj_type.attribute25
,p_attribute26 => l_item.custom_obj_type.attribute26
,p_attribute27 => l_item.custom_obj_type.attribute27
,p_attribute28 => l_item.custom_obj_type.attribute28
,p_attribute29 => l_item.custom_obj_type.attribute29
,p_attribute30 => l_item.custom_obj_type.attribute30
,p_global_attribute_category => l_item.custom_obj_type.global_attribute_category
,p_global_attribute1 => l_item.custom_obj_type.global_attribute1
,p_global_attribute2 => l_item.custom_obj_type.global_attribute2
,p_global_attribute3 => l_item.custom_obj_type.global_attribute3
,p_global_attribute4 => l_item.custom_obj_type.global_attribute4
,p_global_attribute5 => l_item.custom_obj_type.global_attribute5
,p_global_attribute6 => l_item.custom_obj_type.global_attribute6
,p_global_attribute7 => l_item.custom_obj_type.global_attribute7
,p_global_attribute8 => l_item.custom_obj_type.global_attribute8
,p_global_attribute9 => l_item.custom_obj_type.global_attribute9
,p_global_attribute10 => l_item.custom_obj_type.global_attribute10
,p_creation_date => l_item.main_obj_type.creation_date
,p_created_by => l_item.main_obj_type.created_by
,p_last_update_date => l_item.main_obj_type.last_update_date
,p_last_updated_by => l_item.main_obj_type.last_updated_by
,p_last_update_login => l_item.main_obj_type.last_update_login
,p_request_id => l_item.main_obj_type.request_id
,p_program_application_id => l_item.main_obj_type.program_application_id
,p_program_id => l_item.main_obj_type.program_id
,p_program_update_date => l_item.main_obj_type.program_update_date
,p_lifecycle_id => l_item.main_obj_type.lifecycle_id
,p_current_phase_id => l_item.main_obj_type.current_phase_id
,p_revision_id => l_item.main_obj_type.revision_id
,p_revision_code => l_item.main_obj_type.revision_code
,p_revision_label => l_item.main_obj_type.revision_label
,p_revision_description => l_item.main_obj_type.revision_description
,p_effectivity_date => l_item.main_obj_type.effectivity_date
,p_rev_lifecycle_id => l_item.main_obj_type.rev_lifecycle_id
,p_rev_current_phase_id => l_item.main_obj_type.rev_current_phase_id
,p_rev_attribute_category => l_item.custom_obj_type.rev_attribute_category
,p_rev_attribute1 => l_item.custom_obj_type.rev_attribute1
,p_rev_attribute2 => l_item.custom_obj_type.rev_attribute2
,p_rev_attribute3 => l_item.custom_obj_type.rev_attribute3
,p_rev_attribute4 => l_item.custom_obj_type.rev_attribute4
,p_rev_attribute5 => l_item.custom_obj_type.rev_attribute5
,p_rev_attribute6 => l_item.custom_obj_type.rev_attribute6
,p_rev_attribute7 => l_item.custom_obj_type.rev_attribute7
,p_rev_attribute8 => l_item.custom_obj_type.rev_attribute8
,p_rev_attribute9 => l_item.custom_obj_type.rev_attribute9
,p_rev_attribute10 => l_item.custom_obj_type.rev_attribute10
,p_rev_attribute11 => l_item.custom_obj_type.rev_attribute11
,p_rev_attribute12 => l_item.custom_obj_type.rev_attribute12
,p_rev_attribute13 => l_item.custom_obj_type.rev_attribute13
,p_rev_attribute14 => l_item.custom_obj_type.rev_attribute14
,p_rev_attribute15 => l_item.custom_obj_type.rev_attribute15
,p_apply_template => l_item.main_obj_type.apply_template
,p_object_version_number => l_item.deprecated_obj_type.object_version_number
,p_process_control => 'PLM_UI:N'
,x_inventory_item_id => x_out.inventory_item_id
,x_organization_id => x_out.organization_id
,x_return_status => x_out.output_status.return_status
,x_msg_count => x_out.output_status.msg_count
,x_msg_data => x_out.output_status.msg_data
,p_cas_number => l_item.process_manufacturing_obj.cas_number
,p_child_lot_flag => l_item.inventory_obj_type.child_lot_flag
,p_child_lot_prefix => l_item.inventory_obj_type.child_lot_prefix
,p_child_lot_starting_number => l_item.inventory_obj_type.child_lot_starting_number
,p_child_lot_validation_flag => l_item.inventory_obj_type.child_lot_validation_flag
,p_copy_lot_attribute_flag => l_item.inventory_obj_type.copy_lot_attribute_flag
,p_default_grade => l_item.inventory_obj_type.default_grade
,p_expiration_action_code => l_item.inventory_obj_type.expiration_action_code
,p_expiration_action_interval => l_item.inventory_obj_type.expiration_action_interval
,p_grade_control_flag => l_item.inventory_obj_type.grade_control_flag
,p_hazardous_material_flag => l_item.process_manufacturing_obj.hazardous_material_flag
,p_hold_days => l_item.inventory_obj_type.hold_days
,p_lot_divisible_flag => l_item.inventory_obj_type.lot_divisible_flag
,p_maturity_days => l_item.inventory_obj_type.maturity_days
,p_parent_child_generation_flag => l_item.inventory_obj_type.parent_child_generation_flag
,p_process_costing_enabled_flag => l_item.process_manufacturing_obj.process_costing_enabled_flag
,p_process_execution_enabled_fl => l_item.process_manufacturing_obj.process_execution_enabled_flag
,p_process_quality_enabled_flag => l_item.process_manufacturing_obj.process_quality_enabled_flag
,p_process_supply_locator_id => l_item.process_manufacturing_obj.process_supply_locator_id
,p_process_supply_subinventory => l_item.process_manufacturing_obj.process_supply_subinventory
,p_process_yield_locator_id => l_item.process_manufacturing_obj.process_yield_locator_id
,p_process_yield_subinventory => l_item.process_manufacturing_obj.process_yield_subinventory
,p_recipe_enabled_flag => l_item.process_manufacturing_obj.recipe_enabled_flag
,p_retest_interval => l_item.inventory_obj_type.retest_interval
,p_charge_periodicity_code => l_item.order_obj_type.charge_periodicity_code
,p_repair_leadtime => l_item.mrp_obj_type.repair_leadtime
,p_repair_yield => l_item.mrp_obj_type.repair_yield
,p_preposition_point => l_item.mrp_obj_type.preposition_point
,p_repair_program => l_item.mrp_obj_type.repair_program
,p_subcontracting_component => l_item.gplanning_obj_type.subcontracting_component
,p_outsourced_assembly => l_item.purchasing_obj_type.outsourced_assembly
);
/*Call the Process_item API for the same item in update mode for the different languages*/
l_transaction_type := INV_EBI_ITEM_PUB.g_otype_update;
INV_EBI_UTIL.debug_line('STEP 110: BEFORE CALLING EGO_ITEM_PUB.process_item IN UPDATE MODE');
INV_EBI_UTIL.debug_line('STEP 110: AFTER CALLING EGO_ITEM_PUB.process_item IN UPDATE MODE: STATUS: '|| x_out.output_status.return_status);
SELECT concatenated_segments,description
INTO x_out.item_number,x_out.description
FROM mtl_system_items_kfv
WHERE organization_id = x_out.organization_id
AND inventory_item_id = x_out.inventory_item_id;
SELECT orig_system_id
INTO l_source_system_id
FROM HZ_ORIG_SYSTEMS_B
WHERE orig_system = l_item.main_obj_type.cross_reference_type;
SELECT COUNT(*)
INTO l_count
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND nvl(MCR.SOURCE_SYSTEM_ID,-99999) = nvl(l_source_system_id,-99999) --Bug 8704166
AND MCR.CROSS_REFERENCE = l_item.main_obj_type.item_number
AND MCR.INVENTORY_ITEM_ID = x_out.inventory_item_id
AND MCR.END_DATE_ACTIVE IS NULL
OR MCR.END_DATE_ACTIVE>SYSDATE;
INV_EBI_UTIL.debug_line('STEP 170: BEFORE CALLING MTL_CROSS_REFERENCES_PKG.insert_row');
MTL_CROSS_REFERENCES_PKG.insert_row(
p_source_system_id => l_source_system_id,
p_start_date_active => SYSDATE,
p_end_date_active => NULL,
p_object_version_number => NULL,
p_uom_code => NULL,
p_revision_id => NULL,
p_epc_gtin_serial => NULL,
p_inventory_item_id => x_out.inventory_item_id,
p_organization_id => NULL,
p_cross_reference_type => 'SS_ITEM_XREF',
p_cross_reference => l_item.main_obj_type.item_number, -- p_source_system_reference,
p_org_independent_flag => 'Y',
p_request_id => 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_attribute_category => NULL,
p_description => l_item.main_obj_type.description,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.user_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login => FND_GLOBAL.login_id,
p_program_application_id => NULL,
p_program_id => NULL,
p_program_update_date => SYSDATE,
x_cross_reference_id => l_xref_id);
INV_EBI_UTIL.debug_line('STEP 180: AFTER CALLING MTL_CROSS_REFERENCES_PKG.insert_row');
SELECT data_level_int_name
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = p_attr_grp_id
AND classification_code IN( SELECT item_catalog_group_id
FROM mtl_item_catalog_groups_kfv
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = p_item_catalog_group_id
);
SELECT revision_id INTO l_revision_id
FROM mtl_item_revisions
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND revision = p_revision_code;
/* Bug 9755814 - Added the below Functions for the Update Category issue */
FUNCTION is_category_set_multi_assign (
p_category_set_id IN NUMBER
) RETURN BOOLEAN
IS
l_multi_cat_assign_flag VARCHAR2(1);
SELECT mult_item_cat_assign_flag
INTO l_multi_cat_assign_flag
FROM mtl_category_sets_b
WHERE category_set_id = p_category_set_id;
SELECT NVL(category_id, 0)
INTO l_item_category
FROM mtl_item_categories
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND category_set_id = p_category_set_id;
/* Bug 9755814 - Added the below API to update the category of an item when a default category set is already been assigned through the Functional area (Inventory, Purchasing etc.,) */
/* From 12.1.3 onwards (and 12.2), please call the EGO_ITEM_PUB.Process_Item_Cat_Assignment() API with UPDATE transaction.*/
/************************************************************************************
-- API name : update_category_assignments
-- Type : Public
-- Function :
-- ************************************************************************************/
PROCEDURE update_category_assignments(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_category_id IN NUMBER,
p_old_category_id IN NUMBER,
p_category_set_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
IF INV_EGO_REVISION_VALIDATE.check_data_security(
p_function => 'EGO_EDIT_ITEM'
,p_object_name => 'EGO_ITEM'
,p_instance_pk1_value => p_inventory_item_id
,p_instance_pk2_value => p_organization_id
,P_User_Id => FND_GLOBAL.user_id) <> 'T'
THEN
FND_MESSAGE.set_name('INV','INV_IOI_ITEM_UPDATE_PRIV');
INV_ITEM_CATEGORY_PUB.Update_Category_Assignment(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_category_id => p_category_id
,p_old_category_id => p_old_category_id
,p_category_set_id => p_category_set_id
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_organization_id
,x_return_status =>x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_msg_data := x_msg_data ||' -> INV_EBI_ITEM_HELPER.update_category_assignments ';
x_msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.update_category_assignments ';
END update_category_assignments;
/* when multi asisgn is disabled updates the category and when multiple category assignments are coming in for the category set it fails */
PROCEDURE process_category_assignments(
p_api_version IN NUMBER DEFAULT 1.0
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_category_id_tbl IN inv_ebi_category_obj_tbl_type
,x_out OUT NOCOPY inv_ebi_item_output_obj
)
IS
l_category_output inv_ebi_category_output_obj;
update_category_assignments(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => FND_API.g_false
,p_category_id => l_category_id
,p_old_category_id => l_old_item_category
,p_category_set_id => p_category_id_tbl(i).cat_set_id
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_organization_id
,x_return_status => x_out.output_status.return_status
,x_errorcode => x_out.category_output.error_code
,x_msg_count => x_out.output_status.msg_count
,x_msg_data => x_out.output_status.msg_data
);
SELECT
rowid
,manufacturer_id
,mfg_part_num
,inventory_item_id
,organization_id
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
FROM mtl_mfg_part_numbers
WHERE manufacturer_id = p_manufacturer_id
AND mfg_part_num = p_mfg_part_num
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
INV_EBI_UTIL.debug_line('STEP 30: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.insert_row ');
MTL_MFG_PART_NUMBERS_PKG.insert_row(
x_rowid => l_rowid
,x_manufacturer_id => p_mfg_part_obj.manufacturer_id
,x_mfg_part_num => p_mfg_part_obj.mfg_part_num
,x_inventory_item_id => p_inventory_item_id
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_creation_date => SYSDATE
,x_created_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_organization_id => p_organization_id
,x_description => p_mfg_part_obj.description
,x_attribute_category => p_mfg_part_obj.attribute_category
,x_attribute1 => p_mfg_part_obj.attribute1
,x_attribute2 => p_mfg_part_obj.attribute2
,x_attribute3 => p_mfg_part_obj.attribute3
,x_attribute4 => p_mfg_part_obj.attribute4
,x_attribute5 => p_mfg_part_obj.attribute5
,x_attribute6 => p_mfg_part_obj.attribute6
,x_attribute7 => p_mfg_part_obj.attribute7
,x_attribute8 => p_mfg_part_obj.attribute8
,x_attribute9 => p_mfg_part_obj.attribute9
,x_attribute10 => p_mfg_part_obj.attribute10
,x_attribute11 => p_mfg_part_obj.attribute11
,x_attribute12 => p_mfg_part_obj.attribute12
,x_attribute13 => p_mfg_part_obj.attribute13
,x_attribute14 => p_mfg_part_obj.attribute14
,x_attribute15 => p_mfg_part_obj.attribute15
);
INV_EBI_UTIL.debug_line('STEP 40: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.insert_row ');
ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_UPDATE THEN
INV_EBI_UTIL.debug_line('STEP 50: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
MTL_MFG_PART_NUMBERS_PKG.update_row(
x_rowid => l_rowid
,x_manufacturer_id => l_manufacturer_id
,x_mfg_part_num => l_mfg_part_num
,x_inventory_item_id => l_inventory_item_id
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_organization_id => l_organization_id
,x_description => l_description
,x_attribute_category => l_attribute_category
,x_attribute1 => l_attribute1
,x_attribute2 => l_attribute2
,x_attribute3 => l_attribute3
,x_attribute4 => l_attribute4
,x_attribute5 => l_attribute5
,x_attribute6 => l_attribute6
,x_attribute7 => l_attribute7
,x_attribute8 => l_attribute8
,x_attribute9 => l_attribute9
,x_attribute10 => l_attribute10
,x_attribute11 => l_attribute11
,x_attribute12 => l_attribute12
,x_attribute13 => l_attribute13
,x_attribute14 => l_attribute14
,x_attribute15 => l_attribute15
);
INV_EBI_UTIL.debug_line('STEP 60: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.update_row ');
ELSIF UPPER(p_mfg_part_obj.transaction_type) = ENG_GLOBALS.G_OPR_DELETE THEN
INV_EBI_UTIL.debug_line('STEP 70: BEFORE CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
MTL_MFG_PART_NUMBERS_PKG.Delete_Row(l_Rowid);
INV_EBI_UTIL.debug_line('STEP 80: AFTER CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
INV_EBI_UTIL.debug_line('STEP 90: BEFORE CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
MTL_MFG_PART_NUMBERS_PKG.insert_row(
x_rowid => l_rowid
,x_manufacturer_id => p_mfg_part_obj.manufacturer_id
,x_mfg_part_num => p_mfg_part_obj.mfg_part_num
,x_inventory_item_id => p_inventory_item_id
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_creation_date => SYSDATE
,x_created_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_organization_id => p_organization_id
,x_description => p_mfg_part_obj.description
,x_attribute_category => p_mfg_part_obj.attribute_category
,x_attribute1 => p_mfg_part_obj.attribute1
,x_attribute2 => p_mfg_part_obj.attribute2
,x_attribute3 => p_mfg_part_obj.attribute3
,x_attribute4 => p_mfg_part_obj.attribute4
,x_attribute5 => p_mfg_part_obj.attribute5
,x_attribute6 => p_mfg_part_obj.attribute6
,x_attribute7 => p_mfg_part_obj.attribute7
,x_attribute8 => p_mfg_part_obj.attribute8
,x_attribute9 => p_mfg_part_obj.attribute9
,x_attribute10 => p_mfg_part_obj.attribute10
,x_attribute11 => p_mfg_part_obj.attribute11
,x_attribute12 => p_mfg_part_obj.attribute12
,x_attribute13 => p_mfg_part_obj.attribute13
,x_attribute14 => p_mfg_part_obj.attribute14
,x_attribute15 => p_mfg_part_obj.attribute15
);
INV_EBI_UTIL.debug_line('STEP 100: AFTER CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
INV_EBI_UTIL.debug_line('STEP 110: BEFORE CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
MTL_MFG_PART_NUMBERS_PKG.update_row(
x_rowid => l_rowid
,x_manufacturer_id => l_manufacturer_id
,x_mfg_part_num => l_mfg_part_num
,x_inventory_item_id => l_inventory_item_id
,x_last_update_date => SYSDATE
,x_last_updated_by => fnd_global.user_id
,x_last_update_login => fnd_global.login_id
,x_organization_id => l_organization_id
,x_description => l_description
,x_attribute_category => l_attribute_category
,x_attribute1 => l_attribute1
,x_attribute2 => l_attribute2
,x_attribute3 => l_attribute3
,x_attribute4 => l_attribute4
,x_attribute5 => l_attribute5
,x_attribute6 => l_attribute6
,x_attribute7 => l_attribute7
,x_attribute8 => l_attribute8
,x_attribute9 => l_attribute9
,x_attribute10 => l_attribute10
,x_attribute11 => l_attribute11
,x_attribute12 => l_attribute12
,x_attribute13 => l_attribute13
,x_attribute14 => l_attribute14
,x_attribute15 => l_attribute15
);
INV_EBI_UTIL.debug_line('STEP 120: AFTER CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
PROCEDURE process_update_item_lifecycle(
p_commit IN VARCHAR2 := FND_API.g_false
,p_update_item_tbl IN inv_ebi_item_attr_tbl
,x_out OUT NOCOPY inv_ebi_item_output_obj
) IS
l_output_status inv_ebi_output_status;
SELECT lifecycle_id,
current_phase_id,
inventory_item_status_code,
item_catalog_group_id
FROM mtl_system_items_b
WHERE inventory_item_id = cp_item_id
AND organization_id = cp_org_id;
SELECT display_sequence
FROM pa_proj_element_versions
WHERE proj_element_id = cp_phase_id;
SELECT p1.proj_element_id, p1.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P1, PA_PROJ_ELEMENT_VERSIONS P2
WHERE P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
AND P2.PROJ_ELEMENT_ID = cp_lifecycle_id
AND P1.display_sequence >
(SELECT P3.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P3
WHERE P3.PROJ_ELEMENT_ID = cp_phase_id
AND P3.PARENT_STRUCTURE_VERSION_ID = P1.parent_structure_version_id
)
ORDER BY p1.DISPLAY_SEQUENCE ASC;
SELECT p1.proj_element_id, p1.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P1, PA_PROJ_ELEMENT_VERSIONS P2
WHERE P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
AND P2.PROJ_ELEMENT_ID = cp_lifecycle_id
AND P1.display_sequence <
(SELECT P3.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P3
WHERE P3.PROJ_ELEMENT_ID = cp_phase_id
AND P3.PARENT_STRUCTURE_VERSION_ID = P1.parent_structure_version_id
)
ORDER BY p1.DISPLAY_SEQUENCE DESC;
INV_EBI_UTIL.debug_line('STEP 10: START INSIDE INV_EBI_ITEM_HELPER.process_update_item_lifecycle');
IF(p_update_item_tbl IS NOT NULL AND p_update_item_tbl.COUNT > 0) THEN
FOR i IN 1..p_update_item_tbl.COUNT LOOP
IF(c_get_item_curr_data % ISOPEN) THEN
CLOSE c_get_item_curr_data;
cp_item_id => p_update_item_tbl(i).item_obj.main_obj_type.inventory_item_id
,cp_org_id => p_update_item_tbl(i).item_obj.main_obj_type.organization_id
);
l_new_lifecycle_id := p_update_item_tbl(i).item_obj.main_obj_type.lifecycle_id ;
l_new_phase_id := p_update_item_tbl(i).item_obj.main_obj_type.current_phase_id;
l_new_status_code := p_update_item_tbl(i).item_obj.main_obj_type.inventory_item_status_code;
p_config_tbl => p_update_item_tbl(i).item_obj.name_value_tbl
,p_config_param_name => G_ALLOW_LIFECYCLE_PHASE_SKIP
);
,p_inventory_item_id => p_update_item_tbl(i).item_obj.main_obj_type.inventory_item_id
,p_item_number => p_update_item_tbl(i).item_obj.main_obj_type.item_number
,p_organization_id => p_update_item_tbl(i).item_obj.main_obj_type.organization_id
,p_organization_code => p_update_item_tbl(i).item_obj.main_obj_type.organization_code
,p_revision_id => NULL
,p_revision => NULL
,p_implement_changes => fnd_api.g_true
,p_status => NULL
,p_effective_date => sysdate
,p_lifecycle_id => l_curr_lifecycle_id
,p_phase_id => cur_next_phase.proj_element_id
,p_new_effective_date => NULL
,x_return_status => x_out.output_status.return_status
,x_msg_count => x_out.output_status.msg_count
,x_msg_data => x_out.output_status.msg_data
) ;
x_out.item_number := p_update_item_tbl(i).item_obj.main_obj_type.item_number;
x_out.organization_code := p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_update_item_tbl(i).item_obj.main_obj_type.item_number || ' -> Org Code: ' || p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
,p_inventory_item_id => p_update_item_tbl(i).item_obj.main_obj_type.inventory_item_id
,p_item_number => p_update_item_tbl(i).item_obj.main_obj_type.item_number
,p_organization_id => p_update_item_tbl(i).item_obj.main_obj_type.organization_id
,p_organization_code => p_update_item_tbl(i).item_obj.main_obj_type.organization_code
,p_revision_id => NULL
,p_revision => NULL
,p_implement_changes => fnd_api.g_true
,p_status => NULL
,p_effective_date => sysdate
,p_lifecycle_id => l_curr_lifecycle_id
,p_phase_id => cur_priv_phase.proj_element_id
,p_new_effective_date => NULL
,x_return_status => x_out.output_status.return_status
,x_msg_count => x_out.output_status.msg_count
,x_msg_data => x_out.output_status.msg_data
) ;
x_out.item_number := p_update_item_tbl(i).item_obj.main_obj_type.item_number;
x_out.organization_code := p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_update_item_tbl(i).item_obj.main_obj_type.item_number || ' -> Org Code: ' || p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
l_item_tbl.DELETE;
l_item_tbl(1).transaction_type := INV_EBI_ITEM_PUB.g_otype_update;
l_item_tbl(1).inventory_item_id := p_update_item_tbl(i).item_obj.main_obj_type.inventory_item_id;
l_item_tbl(1).organization_id := p_update_item_tbl(i).item_obj.main_obj_type.organization_id;
l_item_tbl(1).item_catalog_group_id := p_update_item_tbl(i).item_obj.main_obj_type.item_catalog_group_id;
x_out.item_number := p_update_item_tbl(i).item_obj.main_obj_type.item_number;
x_out.organization_code := p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
x_out.output_status.msg_data := x_out.output_status.msg_data || ' -> Item Number:' || p_update_item_tbl(i).item_obj.main_obj_type.item_number || ' -> Org Code: ' || p_update_item_tbl(i).item_obj.main_obj_type.organization_code;
x_out.output_status.msg_data := x_out.output_status.msg_data ||' -> INV_EBI_ITEM_HELPER.process_update_item_lifecycle ';
x_out.output_status.msg_data := SQLERRM||' INV_EBI_ITEM_HELPER.process_update_item_lifecycle ';
END process_update_item_lifecycle;
SELECT COUNT(1) INTO l_manufacturer_count
FROM mtl_manufacturers
WHERE manufacturer_id = l_mfg_part_num_obj.manufacturer_id;
SELECT
DISTINCT(locator_id)
FROM
mtl_onhand_locator_v
WHERE
locator_id IS NOT NULL AND
inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND
(revision IS NOT NULL OR (revision = p_revision)) AND
(subinventory_code IS NOT NULL OR (subinventory_code = p_subinventory_code));
SELECT
att.attr_name,
ass.attr_group_id,
ass.data_level_int_name
FROM
ego_obj_attr_grp_assocs_v ass,
ego_attrs_v att
WHERE ass.classification_code = TO_CHAR(p_classification_id)
AND ass.attr_group_type = att.attr_group_type
AND att.attr_group_type = p_attr_group_type
AND ass.application_id = att.application_id
AND att.application_id = p_application_id
AND ass.attr_group_name = att.attr_group_name
AND att.attr_group_name = p_attr_group_name
AND ass.data_level_int_name = p_data_level_int_name;*/
SELECT
att.attr_name
FROM
ego_attrs_v att
WHERE
att.attr_group_type = p_attr_group_type
AND att.application_id = p_application_id
AND att.attr_group_name = p_attr_group_name;
SELECT data_level_int_name
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = p_attr_grp_id
AND classification_code IN( SELECT item_catalog_group_id
FROM mtl_item_catalog_groups_kfv
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = p_item_catalog_group_id
);
/*SELECT data_level_int_name INTO l_data_level
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = p_attr_grp_id_tbl(i)
AND classification_code = TO_CHAR(p_classification_id);*/
/*SELECT attr_group_name INTO l_attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE
attr_group_id = p_attr_grp_id_tbl(i) AND
classification_code = TO_CHAR(p_classification_id) AND
data_level_int_name = l_data_level;*/
SELECT attr_group_name INTO l_attr_group_name
FROM ego_attr_groups_v
WHERE attr_group_id = p_attr_grp_id_tbl(i) AND
application_id = p_application_id AND
attr_group_type = p_attr_group_type;
SELECT DISTINCT ems.attr_group_id
FROM
ego_mtl_sy_items_ext_vl ems,
ego_obj_attr_grp_assocs_v ass
WHERE
ems.inventory_item_id = p_inventory_item_id AND
ems.organization_id = p_organization_id AND
ems.item_catalog_group_id IN ( SELECT item_catalog_group_id
FROM mtl_item_catalog_groups_kfv
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = p_item_classification_id
) AND
ems.attr_group_id = ass.attr_group_id;
SELECT COUNT(1) INTO l_count
FROM mtl_system_items_b
WHERE
inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND
item_catalog_group_id = p_item_classification_id;
SELECT
manufacturer_id,mfg_part_num
FROM mtl_mfg_part_numbers
WHERE inventory_item_id = p_item_id AND organization_id=p_org_id;
SELECT inventory_item_id
,organization_id
,description
,item_catalog_group_id
,end_date_active
,start_date_active
,primary_uom_code
,tracking_quantity_ind
,ont_pricing_qty_source
,secondary_uom_code
,secondary_default_ind
,dual_uom_deviation_high
,dual_uom_deviation_low
,allowed_units_lookup_code
,item_type
,description
,inventory_item_status_code
,primary_unit_of_measure
,lifecycle_id
,current_phase_id
,eam_item_type
,eam_activity_type_code
,eam_activity_cause_code
,eam_activity_source_code
,eam_act_notification_flag
,eam_act_shutdown_status
,bom_enabled_flag
,bom_item_type
,base_item_id
,auto_created_config_flag
,effectivity_control
,config_model_type
,config_orgs
,config_match
,eng_item_flag
,costing_enabled_flag
,inventory_asset_flag
,default_include_in_rollup_flag
,cost_of_sales_account
,std_lot_size
,inventory_planning_code
,planner_code
,planning_make_buy_code
,min_minmax_quantity
,max_minmax_quantity
,minimum_order_quantity
,maximum_order_quantity
,order_cost
,carrying_cost
,source_type
,source_organization_id
,source_subinventory
,mrp_safety_stock_code
,safety_stock_bucket_days
,mrp_safety_stock_percent
,fixed_order_quantity
,fixed_days_supply
,fixed_lot_multiplier
,vmi_minimum_units
,vmi_minimum_days
,vmi_maximum_units
,vmi_maximum_days
,vmi_fixed_order_quantity
,so_authorization_flag
,consigned_flag
,asn_autoexpire_flag
,vmi_forecast_type
,forecast_horizon
,inventory_item_flag
,stock_enabled_flag
,mtl_transactions_enabled_flag
,check_shortages_flag
,revision_qty_control_code
,reservable_type
,shelf_life_code
,shelf_life_days
,cycle_count_enabled_flag
,negative_measurement_error
,positive_measurement_error
,lot_control_code
,auto_lot_alpha_prefix
,start_auto_lot_number
,serial_number_control_code
,auto_serial_alpha_prefix
,start_auto_serial_number
,location_control_code
,restrict_subinventories_code
,restrict_locators_code
,bulk_picked_flag
,lot_status_enabled
,serial_status_enabled
,lot_split_enabled
,lot_merge_enabled
,lot_translate_enabled
,lot_substitution_enabled
,invoiceable_item_flag
,invoice_enabled_flag
,accounting_rule_id
,invoicing_rule_id
,tax_code
,sales_account
,payment_terms_id
,preprocessing_lead_time
,full_lead_time
,postprocessing_lead_time
,fixed_lead_time
,variable_lead_time
,cum_manufacturing_lead_time
,cumulative_total_lead_time
,lead_time_lot_size
,mrp_planning_code
,ato_forecast_control
,planning_exception_set
,end_assembly_pegging_flag
,shrinkage_rate
,rounding_control_type
,acceptable_early_days
,repetitive_planning_flag
,overrun_percentage
,acceptable_rate_increase
,acceptable_rate_decrease
,mrp_calculate_atp_flag
,auto_reduce_mps
,planning_time_fence_code
,planning_time_fence_days
,demand_time_fence_code
,demand_time_fence_days
,release_time_fence_code
,release_time_fence_days
,substitution_window_code
,substitution_window_days
,exclude_from_budget_flag
,days_tgt_inv_supply
,days_tgt_inv_window
,days_max_inv_supply
,days_max_inv_window
,drp_planned_flag
,critical_component_flag
,continous_transfer
,convergence
,divergence
,customer_order_flag
,customer_order_enabled_flag
,shippable_item_flag
,internal_order_flag
,internal_order_enabled_flag
,so_transactions_flag
,pick_components_flag
,atp_flag
,replenish_to_order_flag
,atp_rule_id
,atp_components_flag
,ship_model_complete_flag
,picking_rule_id
,collateral_flag
,default_shipping_org
,returnable_flag
,return_inspection_requirement
,over_shipment_tolerance
,under_shipment_tolerance
,over_return_tolerance
,under_return_tolerance
,financing_allowed_flag
,default_so_source_type
,weight_uom_code
,unit_weight
,volume_uom_code
,unit_volume
,container_item_flag
,vehicle_item_flag
,container_type_code
,internal_volume
,maximum_load_weight
,minimum_fill_percent
,equipment_type
,event_flag
,electronic_flag
,downloadable_flag
,indivisible_flag
,dimension_uom_code
,unit_length
,unit_width
,unit_height
,recipe_enabled_flag
,process_costing_enabled_flag
,process_quality_enabled_flag
,process_execution_enabled_flag
,process_supply_subinventory
,process_supply_locator_id
,process_yield_subinventory
,process_yield_locator_id
,hazardous_material_flag
,cas_number
,purchasing_item_flag
,purchasing_enabled_flag
,must_use_approved_vendor_flag
,allow_item_desc_update_flag
,rfq_required_flag
,outside_operation_flag
,outside_operation_uom_type
,taxable_flag
,purchasing_tax_code
,receipt_required_flag
,inspection_required_flag
,buyer_id
,unit_of_issue
,receive_close_tolerance
,invoice_close_tolerance
,un_number_id
,hazard_class_id
,list_price_per_unit
,market_price
,price_tolerance_percent
,rounding_factor
,encumbrance_account
,expense_account
,asset_category_id
,receipt_days_exception_code
,days_early_receipt_allowed
,days_late_receipt_allowed
,allow_substitute_receipts_flag
,allow_unordered_receipts_flag
,allow_express_delivery_flag
,qty_rcv_exception_code
,qty_rcv_tolerance
,receiving_routing_id
,enforce_ship_to_location_code
,coverage_schedule_id
,service_duration
,service_duration_period_code
,serviceable_product_flag
,service_starting_delay
,material_billable_flag
,recovered_part_disp_code
,defect_tracking_on_flag
,comms_nl_trackable_flag
,asset_creation_code
,ib_item_instance_class
,orderable_on_web_flag
,back_orderable_flag
,web_status
,minimum_license_quantity
,build_in_wip_flag
,wip_supply_type
,wip_supply_subinventory
,wip_supply_locator_id
,overcompletion_tolerance_type
,overcompletion_tolerance_value
,inventory_carry_penalty
,operation_slack_penalty
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,ATTRIBUTE21
,ATTRIBUTE22
,ATTRIBUTE23
,ATTRIBUTE24
,ATTRIBUTE25
,ATTRIBUTE26
,ATTRIBUTE27
,ATTRIBUTE28
,ATTRIBUTE29
,ATTRIBUTE30
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,GLOBAL_ATTRIBUTE6
,GLOBAL_ATTRIBUTE7
,GLOBAL_ATTRIBUTE8
,GLOBAL_ATTRIBUTE9
,GLOBAL_ATTRIBUTE10
INTO l_item_core_obj.inventory_item_id
,l_item_core_obj.organization_id
,l_item_core_obj.description
,l_item_core_obj.item_catalog_group_id
,l_item_core_obj.end_date_active
,l_item_core_obj.start_date_active
,l_item_core_obj.primary_uom_code
,l_item_core_obj.tracking_quantity_ind
,l_item_core_obj.ont_pricing_qty_source
,l_item_core_obj.secondary_uom_code
,l_item_core_obj.secondary_default_ind
,l_item_core_obj.dual_uom_deviation_high
,l_item_core_obj.dual_uom_deviation_low
,l_item_core_obj.allowed_units_lookup_code
,l_item_core_obj.item_type
,l_item_core_obj.description
,l_item_core_obj.inventory_item_status_code
,l_item_core_obj.primary_unit_of_measure
,l_item_core_obj.lifecycle_id
,l_item_core_obj.current_phase_id
,l_item_asset_obj.eam_item_type
,l_item_asset_obj.eam_activity_type_code
,l_item_asset_obj.eam_activity_cause_code
,l_item_asset_obj.eam_activity_source_code
,l_item_asset_obj.eam_act_notification_flag
,l_item_asset_obj.eam_act_shutdown_status
,l_item_bom_obj.bom_enabled_flag
,l_item_bom_obj.bom_item_type
,l_item_bom_obj.base_item_id
,l_item_bom_obj.auto_created_config_flag
,l_item_bom_obj.effectivity_control
,l_item_bom_obj.config_model_type
,l_item_bom_obj.config_orgs
,l_item_bom_obj.config_match
,l_item_bom_obj.eng_item_flag
,l_item_costing_obj.costing_enabled_flag
,l_item_costing_obj.inventory_asset_flag
,l_item_costing_obj.default_include_in_rollup_f
,l_item_costing_obj.cost_of_sales_account
,l_item_costing_obj.std_lot_size
,l_item_gplan_obj.inventory_planning_code
,l_item_gplan_obj.planner_code
,l_item_gplan_obj.planning_make_buy_code
,l_item_gplan_obj.min_minmax_quantity
,l_item_gplan_obj.max_minmax_quantity
,l_item_gplan_obj.minimum_order_quantity
,l_item_gplan_obj.maximum_order_quantity
,l_item_gplan_obj.order_cost
,l_item_gplan_obj.carrying_cost
,l_item_gplan_obj.source_type
,l_item_gplan_obj.source_organization_id
,l_item_gplan_obj.source_subinventory
,l_item_gplan_obj.mrp_safety_stock_code
,l_item_gplan_obj.safety_stock_bucket_days
,l_item_gplan_obj.mrp_safety_stock_percent
,l_item_gplan_obj.fixed_order_quantity
,l_item_gplan_obj.fixed_days_supply
,l_item_gplan_obj.fixed_lot_multiplier
,l_item_gplan_obj.vmi_minimum_units
,l_item_gplan_obj.vmi_minimum_days
,l_item_gplan_obj.vmi_maximum_units
,l_item_gplan_obj.vmi_maximum_days
,l_item_gplan_obj.vmi_fixed_order_quantity
,l_item_gplan_obj.so_authorization_flag
,l_item_gplan_obj.consigned_flag
,l_item_gplan_obj.asn_autoexpire_flag
,l_item_gplan_obj.vmi_forecast_type
,l_item_gplan_obj.forecast_horizon
,l_item_inv_obj.inventory_item_flag
,l_item_inv_obj.stock_enabled_flag
,l_item_inv_obj.mtl_transactions_enabled_fl
,l_item_inv_obj.check_shortages_flag
,l_item_inv_obj.revision_qty_control_code
,l_item_inv_obj.reservable_type
,l_item_inv_obj.shelf_life_code
,l_item_inv_obj.shelf_life_days
,l_item_inv_obj.cycle_count_enabled_flag
,l_item_inv_obj.negative_measurement_error
,l_item_inv_obj.positive_measurement_error
,l_item_inv_obj.lot_control_code
,l_item_inv_obj.auto_lot_alpha_prefix
,l_item_inv_obj.start_auto_lot_number
,l_item_inv_obj.serial_number_control_code
,l_item_inv_obj.auto_serial_alpha_prefix
,l_item_inv_obj.start_auto_serial_number
,l_item_inv_obj.location_control_code
,l_item_inv_obj.restrict_subinventories_cod
,l_item_inv_obj.restrict_locators_code
,l_item_inv_obj.bulk_picked_flag
,l_item_inv_obj.lot_status_enabled
,l_item_inv_obj.serial_status_enabled
,l_item_inv_obj.lot_split_enabled
,l_item_inv_obj.lot_merge_enabled
,l_item_inv_obj.lot_translate_enabled
,l_item_inv_obj.lot_substitution_enabled
,l_item_invoice_obj.invoiceable_item_flag
,l_item_invoice_obj.invoice_enabled_flag
,l_item_invoice_obj.accounting_rule_id
,l_item_invoice_obj.invoicing_rule_id
,l_item_invoice_obj.tax_code
,l_item_invoice_obj.sales_account
,l_item_invoice_obj.payment_terms_id
,l_item_lead_time_obj.preprocessing_lead_time
,l_item_lead_time_obj.full_lead_time
,l_item_lead_time_obj.postprocessing_lead_time
,l_item_lead_time_obj.fixed_lead_time
,l_item_lead_time_obj.variable_lead_time
,l_item_lead_time_obj.cum_manufacturing_lead_time
,l_item_lead_time_obj.cumulative_total_lead_time
,l_item_lead_time_obj.lead_time_lot_size
,l_item_mrp_obj.mrp_planning_code
,l_item_mrp_obj.ato_forecast_control
,l_item_mrp_obj.planning_exception_set
,l_item_mrp_obj.end_assembly_pegging_flag
,l_item_mrp_obj.shrinkage_rate
,l_item_mrp_obj.rounding_control_type
,l_item_mrp_obj.acceptable_early_days
,l_item_mrp_obj.repetitive_planning_flag
,l_item_mrp_obj.overrun_percentage
,l_item_mrp_obj.acceptable_rate_increase
,l_item_mrp_obj.acceptable_rate_decrease
,l_item_mrp_obj.mrp_calculate_atp_flag
,l_item_mrp_obj.auto_reduce_mps
,l_item_mrp_obj.planning_time_fence_code
,l_item_mrp_obj.planning_time_fence_days
,l_item_mrp_obj.demand_time_fence_code
,l_item_mrp_obj.demand_time_fence_days
,l_item_mrp_obj.release_time_fence_code
,l_item_mrp_obj.release_time_fence_days
,l_item_mrp_obj.substitution_window_code
,l_item_mrp_obj.substitution_window_days
,l_item_mrp_obj.exclude_from_budget_flag
,l_item_mrp_obj.days_tgt_inv_supply
,l_item_mrp_obj.days_tgt_inv_window
,l_item_mrp_obj.days_max_inv_supply
,l_item_mrp_obj.days_max_inv_window
,l_item_mrp_obj.drp_planned_flag
,l_item_mrp_obj.critical_component_flag
,l_item_mrp_obj.continous_transfer
,l_item_mrp_obj.convergence
,l_item_mrp_obj.divergence
,l_item_order_obj.customer_order_flag
,l_item_order_obj.customer_order_enabled_flag
,l_item_order_obj.shippable_item_flag
,l_item_order_obj.internal_order_flag
,l_item_order_obj.internal_order_enabled_flag
,l_item_order_obj.so_transactions_flag
,l_item_order_obj.pick_components_flag
,l_item_order_obj.atp_flag
,l_item_order_obj.replenish_to_order_flag
,l_item_order_obj.atp_rule_id
,l_item_order_obj.atp_components_flag
,l_item_order_obj.ship_model_complete_flag
,l_item_order_obj.picking_rule_id
,l_item_order_obj.collateral_flag
,l_item_order_obj.default_shipping_org
,l_item_order_obj.returnable_flag
,l_item_order_obj.return_inspection_requireme
,l_item_order_obj.over_shipment_tolerance
,l_item_order_obj.under_shipment_tolerance
,l_item_order_obj.over_return_tolerance
,l_item_order_obj.under_return_tolerance
,l_item_order_obj.financing_allowed_flag
,l_item_order_obj.default_so_source_type
,l_item_phy_obj.weight_uom_code
,l_item_phy_obj.unit_weight
,l_item_phy_obj.volume_uom_code
,l_item_phy_obj.unit_volume
,l_item_phy_obj.container_item_flag
,l_item_phy_obj.vehicle_item_flag
,l_item_phy_obj.container_type_code
,l_item_phy_obj.internal_volume
,l_item_phy_obj.maximum_load_weight
,l_item_phy_obj.minimum_fill_percent
,l_item_phy_obj.equipment_type
,l_item_phy_obj.event_flag
,l_item_phy_obj.electronic_flag
,l_item_phy_obj.downloadable_flag
,l_item_phy_obj.indivisible_flag
,l_item_phy_obj.dimension_uom_code
,l_item_phy_obj.unit_length
,l_item_phy_obj.unit_width
,l_item_phy_obj.unit_height
,l_item_process_obj.recipe_enabled_flag
,l_item_process_obj.process_costing_enabled_flag
,l_item_process_obj.process_quality_enabled_flag
,l_item_process_obj.process_execution_enabled_flag
,l_item_process_obj.process_supply_subinventory
,l_item_process_obj.process_supply_locator_id
,l_item_process_obj.process_yield_subinventory
,l_item_process_obj.process_yield_locator_id
,l_item_process_obj.hazardous_material_flag
,l_item_process_obj.cas_number
,l_item_pur_obj.purchasing_item_flag
,l_item_pur_obj.purchasing_enabled_flag
,l_item_pur_obj.must_use_approved_vendor_fl
,l_item_pur_obj.allow_item_desc_update_flag
,l_item_pur_obj.rfq_required_flag
,l_item_pur_obj.outside_operation_flag
,l_item_pur_obj.outside_operation_uom_type
,l_item_pur_obj.taxable_flag
,l_item_pur_obj.purchasing_tax_code
,l_item_pur_obj.receipt_required_flag
,l_item_pur_obj.inspection_required_flag
,l_item_pur_obj.buyer_id
,l_item_pur_obj.unit_of_issue
,l_item_pur_obj.receive_close_tolerance
,l_item_pur_obj.invoice_close_tolerance
,l_item_pur_obj.un_number_id
,l_item_pur_obj.hazard_class_id
,l_item_pur_obj.list_price_per_unit
,l_item_pur_obj.market_price
,l_item_pur_obj.price_tolerance_percent
,l_item_pur_obj.rounding_factor
,l_item_pur_obj.encumbrance_account
,l_item_pur_obj.expense_account
,l_item_pur_obj.asset_category_id
,l_item_recving_obj.receipt_days_exception_code
,l_item_recving_obj.days_early_receipt_allowed
,l_item_recving_obj.days_late_receipt_allowed
,l_item_recving_obj.allow_substitute_receipts_f
,l_item_recving_obj.allow_unordered_receipts_fl
,l_item_recving_obj.allow_express_delivery_flag
,l_item_recving_obj.qty_rcv_exception_code
,l_item_recving_obj.qty_rcv_tolerance
,l_item_recving_obj.receiving_routing_id
,l_item_recving_obj.enforce_ship_to_location_c
,l_item_service_obj.coverage_schedule_id
,l_item_service_obj.service_duration
,l_item_service_obj.service_duration_period_cod
,l_item_service_obj.serviceable_product_flag
,l_item_service_obj.service_starting_delay
,l_item_service_obj.material_billable_flag
,l_item_service_obj.recovered_part_disp_code
,l_item_service_obj.defect_tracking_on_flag
,l_item_service_obj.comms_nl_trackable_flag
,l_item_service_obj.asset_creation_code
,l_item_service_obj.ib_item_instance_class
,l_item_web_opiton.orderable_on_web_flag
,l_item_web_opiton.back_orderable_flag
,l_item_web_opiton.web_status
,l_item_web_opiton.minimum_license_quantity
,l_item_wip_obj.build_in_wip_flag
,l_item_wip_obj.wip_supply_type
,l_item_wip_obj.wip_supply_subinventory
,l_item_wip_obj.wip_supply_locator_id
,l_item_wip_obj.overcompletion_tolerance_ty
,l_item_wip_obj.overcompletion_tolerance_va
,l_item_wip_obj.inventory_carry_penalty
,l_item_wip_obj.operation_slack_penalty
,l_item_custom_obj.ATTRIBUTE_CATEGORY
,l_item_custom_obj.ATTRIBUTE1
,l_item_custom_obj.ATTRIBUTE2
,l_item_custom_obj.ATTRIBUTE3
,l_item_custom_obj.ATTRIBUTE4
,l_item_custom_obj.ATTRIBUTE5
,l_item_custom_obj.ATTRIBUTE6
,l_item_custom_obj.ATTRIBUTE7
,l_item_custom_obj.ATTRIBUTE8
,l_item_custom_obj.ATTRIBUTE9
,l_item_custom_obj.ATTRIBUTE10
,l_item_custom_obj.ATTRIBUTE11
,l_item_custom_obj.ATTRIBUTE12
,l_item_custom_obj.ATTRIBUTE13
,l_item_custom_obj.ATTRIBUTE14
,l_item_custom_obj.ATTRIBUTE15
,l_item_custom_obj.ATTRIBUTE16
,l_item_custom_obj.ATTRIBUTE17
,l_item_custom_obj.ATTRIBUTE18
,l_item_custom_obj.ATTRIBUTE19
,l_item_custom_obj.ATTRIBUTE20
,l_item_custom_obj.ATTRIBUTE21
,l_item_custom_obj.ATTRIBUTE22
,l_item_custom_obj.ATTRIBUTE23
,l_item_custom_obj.ATTRIBUTE24
,l_item_custom_obj.ATTRIBUTE25
,l_item_custom_obj.ATTRIBUTE26
,l_item_custom_obj.ATTRIBUTE27
,l_item_custom_obj.ATTRIBUTE28
,l_item_custom_obj.ATTRIBUTE29
,l_item_custom_obj.ATTRIBUTE30
,l_item_custom_obj.GLOBAL_ATTRIBUTE_CATEGORY
,l_item_custom_obj.GLOBAL_ATTRIBUTE1
,l_item_custom_obj.GLOBAL_ATTRIBUTE2
,l_item_custom_obj.GLOBAL_ATTRIBUTE3
,l_item_custom_obj.GLOBAL_ATTRIBUTE4
,l_item_custom_obj.GLOBAL_ATTRIBUTE5
,l_item_custom_obj.GLOBAL_ATTRIBUTE6
,l_item_custom_obj.GLOBAL_ATTRIBUTE7
,l_item_custom_obj.GLOBAL_ATTRIBUTE8
,l_item_custom_obj.GLOBAL_ATTRIBUTE9
,l_item_custom_obj.GLOBAL_ATTRIBUTE10
FROM mtl_system_items_vl
WHERE inventory_item_id=l_item_id AND organization_id=l_org_id;
SELECT MASTER_ORGANIZATION_ID INTO l_item_core_obj.MASTER_ORGANIZATION_ID
FROM mtl_parameters WHERE organization_id=l_org_id;
SELECT actual_start_date FROM (
SELECT actual_start_date
FROM fnd_concurrent_requests
WHERE program_application_id = p_appl_id
AND concurrent_program_id = p_conc_prog_id
AND UPPER(phase_code) = 'C'
AND (root_request_id is not null OR resubmit_interval is not null)
AND actual_start_date is not null
ORDER BY actual_start_date DESC)
WHERE ROWNUM = 1;
SELECT actual_start_date INTO l_date FROM (
SELECT actual_start_date
FROM fnd_concurrent_requests
WHERE program_application_id = p_appl_id
AND concurrent_program_id = p_conc_prog_id
AND UPPER(phase_code) = 'C'
AND actual_start_date is not null
ORDER BY actual_start_date DESC)
WHERE ROWNUM = 1;
SELECT msik.inventory_item_id,msik.organization_id,mp.organization_code
FROM mtl_system_items_kfv msik,mtl_parameters mp
WHERE msik.concatenated_segments = p_item_name
AND msik.organization_id = mp.organization_id;
SELECT COUNT(1) into l_entity_exist
FROM mtl_system_items_kfv
WHERE concatenated_segments = l_item_tbl(i);
SELECT COUNT(1) into l_entity_exist
FROM mtl_parameters
WHERE organization_code = l_org_tbl(i);
SELECT inv_ebi_get_operational_attrs(item_pim.inventory_item_id,item_pim.concatenated_segments,item_pim.organization_id,item_pim.organization_code,NULL,NULL)
FROM
(SELECT mcr.inventory_item_id, msik.concatenated_segments, msi.organization_id, mp.organization_code
FROM mtl_cross_references_b mcr, mtl_system_items_b msi, mtl_parameters mp, mtl_system_items_kfv msik, hz_orig_systems_b hz
WHERE mcr.inventory_item_id = msi.inventory_item_id
--AND mcr.organization_id = msi.organization_id --8897962 mcr has organization_id value as null
AND hz.orig_system IN (SELECT * FROM THE(SELECT CAST(l_parsed_tbl AS FND_TABLE_OF_VARCHAR2_255) FROM DUAL))
AND hz.orig_system_id = mcr.source_system_id
AND msi.organization_id = mp.organization_id
AND msik.organization_id = msi.organization_id
AND msik.inventory_item_id = msi.inventory_item_id
AND msi.last_update_date <> msi.creation_date
AND msi.last_update_date >= l_from_date
AND msi.last_update_date <= l_to_date
UNION
SELECT cic.inventory_item_id, msik.concatenated_segments, cic.organization_id, mp.organization_code
FROM mtl_cross_references_b mcr, cst_item_costs cic,mtl_parameters mp, mtl_system_items_kfv msik, hz_orig_systems_b hz
WHERE mcr.inventory_item_id = cic.inventory_item_id
--AND mcr.organization_id = cic.organization_id
AND msik.organization_id = mp.organization_id
AND msik.organization_id = cic.organization_id
AND msik.inventory_item_id = cic.inventory_item_id
AND cic.last_update_date <> cic.creation_date
AND hz.orig_system IN (SELECT * FROM THE(SELECT CAST(l_parsed_tbl AS FND_TABLE_OF_VARCHAR2_255) FROM DUAL))
AND hz.orig_system_id = mcr.source_system_id
AND cic.last_update_date >= l_from_date
AND cic.last_update_date <= l_to_date
UNION
SELECT cql.inventory_item_id, msik.concatenated_segments, cql.organization_id, mp.organization_code
FROM mtl_cross_references_b mcr, cst_quantity_layers cql, mtl_parameters mp, mtl_system_items_kfv msik, hz_orig_systems_b hz
WHERE mcr.inventory_item_id = cql.inventory_item_id
--AND mcr.organization_id = cql.organization_id
AND msik.organization_id = mp.organization_id
AND hz.orig_system IN (SELECT * FROM THE(SELECT CAST(l_parsed_tbl AS FND_TABLE_OF_VARCHAR2_255) FROM DUAL))
AND hz.orig_system_id = mcr.source_system_id
AND msik.organization_id = cql.organization_id
AND msik.inventory_item_id = cql.inventory_item_id
AND cql.last_update_date >= l_from_date
AND cql.last_update_date <= l_to_date) item_pim;
SELECT inv_ebi_get_operational_attrs(item_npim.inventory_item_id,item_npim.concatenated_segments,item_npim.organization_id,item_npim.organization_code,NULL,NULL)
FROM
(SELECT msi.inventory_item_id, msik.concatenated_segments, msi.organization_id, mp.organization_code
FROM mtl_system_items_b msi, mtl_parameters mp, mtl_system_items_kfv msik
WHERE msi.organization_id = mp.organization_id
AND msik.organization_id = msi.organization_id
AND msik.inventory_item_id = msi.inventory_item_id
AND msi.last_update_date <> msi.creation_date
AND msi.last_update_date >= l_from_date
AND msi.last_update_date <= l_to_date
UNION
SELECT cic.inventory_item_id, msik.concatenated_segments, cic.organization_id, mp.organization_code
FROM cst_item_costs cic, mtl_parameters mp, mtl_system_items_kfv msik
WHERE cic.organization_id = mp.organization_id
AND msik.organization_id = cic.organization_id
AND msik.inventory_item_id = cic.inventory_item_id
AND cic.last_update_date <> cic.creation_date
AND cic.last_update_date >= l_from_date
AND cic.last_update_date <= l_to_date
UNION
SELECT cql.inventory_item_id, msik.concatenated_segments, cql.organization_id, mp.organization_code
FROM cst_quantity_layers cql,mtl_parameters mp, mtl_system_items_kfv msik
WHERE cql.organization_id=mp.organization_id
AND msik.organization_id = cql.organization_id
AND msik.inventory_item_id = cql.inventory_item_id
AND cql.last_update_date >= l_from_date
AND cql.last_update_date <= l_to_date
) item_npim;
SELECT inv_ebi_get_operational_attrs(item_flst.item_id,item_flst.item_name,item_flst.organization_id,item_flst.organization_code,NULL,NULL)
FROM (
SELECT a.item_id,a.item_name, a.organization_id,a.organization_code
FROM THE (SELECT CAST( l_item_output_tbl as inv_ebi_get_opr_attrs_tbl)
FROM dual ) a
INTERSECT
SELECT b.item_id,b.item_name, b.organization_id,b.organization_code
FROM THE (SELECT CAST( l_item_tbl as inv_ebi_get_opr_attrs_tbl)
FROM dual ) b
) item_flst;
l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
SELECT inv_ebi_get_operational_attrs(gibp.inventory_item_id,gibp.concatenated_segments, gibp.organization_id,gibp.organization_code,NULL,NULL)
FROM (
SELECT DISTINCT mcr.inventory_item_id,msik.concatenated_segments,moq.organization_id,mp.organization_code
FROM mtl_onhand_quantities_detail moq,mtl_cross_references_b mcr, mtl_parameters mp, mtl_system_items_kfv msik, hz_orig_systems_b hz
WHERE mcr.inventory_item_id = moq.inventory_item_id
--AND mcr.organization_id = moq.organization_id
AND moq.organization_id = mp.organization_id
AND msik.organization_id = moq.organization_id
AND msik.inventory_item_id = moq.inventory_item_id
AND hz.orig_system IN (SELECT * FROM THE(SELECT CAST(l_parsed_tbl AS FND_TABLE_OF_VARCHAR2_255) FROM DUAL))
AND hz.orig_system_id = mcr.source_system_id
AND moq.last_update_date >= l_from_date
AND moq.last_update_date <= l_to_date) gibp;
SELECT inv_ebi_get_operational_attrs(gib.inventory_item_id,gib.concatenated_segments, gib.organization_id,gib.organization_code,NULL,NULL)
FROM (SELECT DISTINCT moq.inventory_item_id,msik.concatenated_segments,moq.organization_id,mp.organization_code
FROM mtl_onhand_quantities_detail moq, mtl_parameters mp, mtl_system_items_kfv msik
WHERE moq.organization_id = mp.organization_id
AND msik.organization_id = moq.organization_id
AND msik.inventory_item_id = moq.inventory_item_id
AND moq.last_update_date >= l_from_date
AND moq.last_update_date <= l_to_date) gib;
SELECT inv_ebi_get_operational_attrs(gibf.item_id,gibf.item_name, gibf.organization_id,gibf.organization_code,NULL,NULL)
FROM (
SELECT a.item_id,a.item_name, a.organization_id,a.organization_code
FROM THE (SELECT cast( l_item_output_tbl as inv_ebi_get_opr_attrs_tbl)
FROM dual ) a
INTERSECT
SELECT b.item_id,b.item_name, b.organization_id,b.organization_code
FROM THE (SELECT cast( l_item_tbl as inv_ebi_get_opr_attrs_tbl)
FROM dual ) b ) gibf;
l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
SELECT operating_unit,name
FROM ORG_ORGANIZATION_DEFINITIONS orgdef,
HR_OPERATING_UNITS hrou
WHERE orgdef.organization_id = cp_organization_id
AND hrou.organization_id=orgdef.operating_unit;
SELECT timezone_code
INTO l_server_tz
FROM fnd_timezones_b
WHERE upgrade_tz_id = fnd_profile.value('SERVER_TIMEZONE_ID')
AND UPPER(enabled_flag)='Y';
SELECT TZ_OFFSET(l_server_tz)
INTO l_tzoffset
FROM DUAL;