[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
WHERE concatenated_segments = l_pk1;
SELECT template_name INTO l_code
FROM mtl_item_templates
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
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 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 = 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;
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;
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 INTO l_item.main_obj_type.template_id
FROM ego_catalog_groups_v T
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
);
SELECT concatenated_segments INTO x_out.item_number
FROM mtl_system_items_kfv
WHERE organization_id = x_out.organization_id
AND inventory_item_id = x_out.inventory_item_id;
MTL_CROSS_REFERENCES_PKG.insert_row(
p_source_system_id => NULL,
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 => x_out.organization_id,
p_cross_reference_type => l_item.main_obj_type.cross_reference_type,
p_cross_reference => l_item.main_obj_type.item_number, -- p_source_system_reference,
p_org_independent_flag => 'N',
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);
SELECT data_level_int_name INTO l_data_level
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = l_attributes_row_table(i).attr_group_id
AND classification_code = TO_CHAR(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;
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: 20 '||'START 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: 30 '||'END 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: 40 '||'START 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: 50 '||'END 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: 60 '||'START CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
MTL_MFG_PART_NUMBERS_PKG.Delete_Row(l_Rowid);
INV_EBI_UTIL.debug_line('STEP: 70 '||'END CALLING MTL_MFG_PART_NUMBERS_PKG.delete_row ');
INV_EBI_UTIL.debug_line('STEP: 80 '||'START 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: 90 '||'END CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.insert_row ');
INV_EBI_UTIL.debug_line('STEP: 100 '||'START 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: 110 '||'END CALLING Sync Mode MTL_MFG_PART_NUMBERS_PKG.update_row ');
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 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 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 = 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_b
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, mcr.organization_id, mp.organization_code
FROM mtl_cross_references_b mcr, mtl_system_items_b msi, mtl_parameters mp, mtl_system_items_kfv msik
WHERE mcr.inventory_item_id = msi.inventory_item_id
AND mcr.organization_id = msi.organization_id
AND mcr.cross_reference_type = p_cross_reference_type
AND mcr.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
WHERE mcr.inventory_item_id = cic.inventory_item_id
AND mcr.organization_id = cic.organization_id
AND mcr.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 mcr.cross_reference_type = p_cross_reference_type
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
WHERE mcr.inventory_item_id = cql.inventory_item_id
AND mcr.organization_id = cql.organization_id
AND mcr.organization_id = mp.organization_id
AND mcr.cross_reference_type = p_cross_reference_type
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');
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,mcr.organization_id,mp.organization_code
FROM mtl_onhand_quantities_detail moq,mtl_cross_references_b mcr, mtl_parameters mp, mtl_system_items_kfv msik
WHERE mcr.inventory_item_id = moq.inventory_item_id
AND mcr.organization_id = moq.organization_id
AND mcr.organization_id = mp.organization_id
AND msik.organization_id = moq.organization_id
AND msik.inventory_item_id = moq.inventory_item_id
AND mcr.cross_reference_type = p_cross_reference_type
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');
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;