[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF (p_Transaction_Type = G_TTYPE_UPDATE) THEN
Update_Item_Number(
p_Inventory_Item_Id
, p_Item_Number
, p_Segment1
, p_Segment2
, p_Segment3
, p_Segment4
, p_Segment5
, p_Segment6
, p_Segment7
, p_Segment8
, p_Segment9
, p_Segment10
, p_Segment11
, p_Segment12
, p_Segment13
, p_Segment14
, p_Segment15
, p_Segment16
, p_Segment17
, p_Segment18
, p_Segment19
, p_Segment20
, p_New_Segment1
, p_New_Segment2
, p_New_Segment3
, p_New_Segment4
, p_New_Segment5
, p_New_Segment6
, p_New_Segment7
, p_New_Segment8
, p_New_Segment9
, p_New_Segment10
, p_New_Segment11
, p_New_Segment12
, p_New_Segment13
, p_New_Segment14
, p_New_Segment15
, p_New_Segment16
, p_New_Segment17
, p_New_Segment18
, p_New_Segment19
, p_New_Segment20
, l_Item_Tbl
, l_return_status);
,p_allow_item_desc_update_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rfq_required_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_outside_operation_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_outside_operation_uom_type IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_taxable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_purchasing_tax_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_receipt_required_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_inspection_required_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_buyer_id IN NUMBER DEFAULT G_MISS_NUM
,p_unit_of_issue IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_receive_close_tolerance IN NUMBER DEFAULT G_MISS_NUM
,p_invoice_close_tolerance IN NUMBER DEFAULT G_MISS_NUM
,p_un_number_id IN NUMBER DEFAULT G_MISS_NUM
,p_hazard_class_id IN NUMBER DEFAULT G_MISS_NUM
,p_list_price_per_unit IN NUMBER DEFAULT G_MISS_NUM
,p_market_price IN NUMBER DEFAULT G_MISS_NUM
,p_price_tolerance_percent IN NUMBER DEFAULT G_MISS_NUM
,p_rounding_factor IN NUMBER DEFAULT G_MISS_NUM
,p_encumbrance_account IN NUMBER DEFAULT G_MISS_NUM
,p_expense_account IN NUMBER DEFAULT G_MISS_NUM
,p_expense_billable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_asset_category_id IN NUMBER DEFAULT G_MISS_NUM
,p_receipt_days_exception_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_days_early_receipt_allowed IN NUMBER DEFAULT G_MISS_NUM
,p_days_late_receipt_allowed IN NUMBER DEFAULT G_MISS_NUM
,p_allow_substitute_receipts_f IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_allow_unordered_receipts_fl IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_allow_express_delivery_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_qty_rcv_exception_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_qty_rcv_tolerance IN NUMBER DEFAULT G_MISS_NUM
,p_receiving_routing_id IN NUMBER DEFAULT G_MISS_NUM
,p_enforce_ship_to_location_c IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_weight_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_unit_weight IN NUMBER DEFAULT G_MISS_NUM
,p_volume_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_unit_volume IN NUMBER DEFAULT G_MISS_NUM
,p_container_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_vehicle_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_container_type_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_internal_volume IN NUMBER DEFAULT G_MISS_NUM
,p_maximum_load_weight IN NUMBER DEFAULT G_MISS_NUM
,p_minimum_fill_percent IN NUMBER DEFAULT G_MISS_NUM
,p_inventory_planning_code IN NUMBER DEFAULT G_MISS_NUM
,p_planner_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_planning_make_buy_code IN NUMBER DEFAULT G_MISS_NUM
,p_min_minmax_quantity IN NUMBER DEFAULT G_MISS_NUM
,p_max_minmax_quantity IN NUMBER DEFAULT G_MISS_NUM
,p_minimum_order_quantity IN NUMBER DEFAULT G_MISS_NUM
,p_maximum_order_quantity IN NUMBER DEFAULT G_MISS_NUM
,p_order_cost IN NUMBER DEFAULT G_MISS_NUM
,p_carrying_cost IN NUMBER DEFAULT G_MISS_NUM
,p_source_type IN NUMBER DEFAULT G_MISS_NUM
,p_source_organization_id IN NUMBER DEFAULT G_MISS_NUM
,p_source_subinventory IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_mrp_safety_stock_code IN NUMBER DEFAULT G_MISS_NUM
,p_safety_stock_bucket_days IN NUMBER DEFAULT G_MISS_NUM
,p_mrp_safety_stock_percent IN NUMBER DEFAULT G_MISS_NUM
,p_fixed_order_quantity IN NUMBER DEFAULT G_MISS_NUM
,p_fixed_days_supply IN NUMBER DEFAULT G_MISS_NUM
,p_fixed_lot_multiplier IN NUMBER DEFAULT G_MISS_NUM
,p_mrp_planning_code IN NUMBER DEFAULT G_MISS_NUM
,p_ato_forecast_control IN NUMBER DEFAULT G_MISS_NUM
,p_planning_exception_set IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_end_assembly_pegging_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_shrinkage_rate IN NUMBER DEFAULT G_MISS_NUM
,p_rounding_control_type IN NUMBER DEFAULT G_MISS_NUM
,p_acceptable_early_days IN NUMBER DEFAULT G_MISS_NUM
,p_repetitive_planning_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_overrun_percentage IN NUMBER DEFAULT G_MISS_NUM
,p_acceptable_rate_increase IN NUMBER DEFAULT G_MISS_NUM
,p_acceptable_rate_decrease IN NUMBER DEFAULT G_MISS_NUM
,p_mrp_calculate_atp_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_auto_reduce_mps IN NUMBER DEFAULT G_MISS_NUM
,p_planning_time_fence_code IN NUMBER DEFAULT G_MISS_NUM
,p_planning_time_fence_days IN NUMBER DEFAULT G_MISS_NUM
,p_demand_time_fence_code IN NUMBER DEFAULT G_MISS_NUM
,p_demand_time_fence_days IN NUMBER DEFAULT G_MISS_NUM
,p_release_time_fence_code IN NUMBER DEFAULT G_MISS_NUM
,p_release_time_fence_days IN NUMBER DEFAULT G_MISS_NUM
,p_preprocessing_lead_time IN NUMBER DEFAULT G_MISS_NUM
,p_full_lead_time IN NUMBER DEFAULT G_MISS_NUM
,p_postprocessing_lead_time IN NUMBER DEFAULT G_MISS_NUM
,p_fixed_lead_time IN NUMBER DEFAULT G_MISS_NUM
,p_variable_lead_time IN NUMBER DEFAULT G_MISS_NUM
,p_cum_manufacturing_lead_time IN NUMBER DEFAULT G_MISS_NUM
,p_cumulative_total_lead_time IN NUMBER DEFAULT G_MISS_NUM
,p_lead_time_lot_size IN NUMBER DEFAULT G_MISS_NUM
,p_build_in_wip_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_wip_supply_type IN NUMBER DEFAULT G_MISS_NUM
,p_wip_supply_subinventory IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_wip_supply_locator_id IN NUMBER DEFAULT G_MISS_NUM
,p_overcompletion_tolerance_ty IN NUMBER DEFAULT G_MISS_NUM
,p_overcompletion_tolerance_va IN NUMBER DEFAULT G_MISS_NUM
,p_customer_order_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_customer_order_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_shippable_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_internal_order_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_internal_order_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_so_transactions_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_pick_components_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_atp_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_replenish_to_order_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_atp_rule_id IN NUMBER DEFAULT G_MISS_NUM
,p_atp_components_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_ship_model_complete_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_picking_rule_id IN NUMBER DEFAULT G_MISS_NUM
,p_collateral_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_default_shipping_org IN NUMBER DEFAULT G_MISS_NUM
,p_returnable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_return_inspection_requireme IN NUMBER DEFAULT G_MISS_NUM
,p_over_shipment_tolerance IN NUMBER DEFAULT G_MISS_NUM
,p_under_shipment_tolerance IN NUMBER DEFAULT G_MISS_NUM
,p_over_return_tolerance IN NUMBER DEFAULT G_MISS_NUM
,p_under_return_tolerance IN NUMBER DEFAULT G_MISS_NUM
,p_invoiceable_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_invoice_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_accounting_rule_id IN NUMBER DEFAULT G_MISS_NUM
,p_invoicing_rule_id IN NUMBER DEFAULT G_MISS_NUM
,p_tax_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_sales_account IN NUMBER DEFAULT G_MISS_NUM
,p_payment_terms_id IN NUMBER DEFAULT G_MISS_NUM
,p_coverage_schedule_id IN NUMBER DEFAULT G_MISS_NUM
,p_service_duration IN NUMBER DEFAULT G_MISS_NUM
,p_service_duration_period_cod IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_serviceable_product_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_service_starting_delay IN NUMBER DEFAULT G_MISS_NUM
,p_material_billable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_serviceable_component_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_preventive_maintenance_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_prorate_service_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
-- Start attributes not in the form
,p_serviceable_item_class_id IN NUMBER DEFAULT G_MISS_NUM
,p_base_warranty_service_id IN NUMBER DEFAULT G_MISS_NUM
,p_warranty_vendor_id IN NUMBER DEFAULT G_MISS_NUM
,p_max_warranty_amount IN NUMBER DEFAULT G_MISS_NUM
,p_response_time_period_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_response_time_value IN NUMBER DEFAULT G_MISS_NUM
,p_primary_specialist_id IN NUMBER DEFAULT G_MISS_NUM
,p_secondary_specialist_id IN NUMBER DEFAULT G_MISS_NUM
-- End attributes not in the form
,p_wh_update_date IN DATE DEFAULT G_MISS_DATE
,p_equipment_type IN NUMBER DEFAULT G_MISS_NUM
,p_recovered_part_disp_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_defect_tracking_on_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_event_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_electronic_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_downloadable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_vol_discount_exempt_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_coupon_exempt_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_comms_nl_trackable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_asset_creation_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_comms_activation_reqd_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_orderable_on_web_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_back_orderable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_web_status IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_indivisible_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_dimension_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_unit_length IN NUMBER DEFAULT G_MISS_NUM
,p_unit_width IN NUMBER DEFAULT G_MISS_NUM
,p_unit_height IN NUMBER DEFAULT G_MISS_NUM
,p_bulk_picked_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_lot_status_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_default_lot_status_id IN NUMBER DEFAULT G_MISS_NUM
,p_serial_status_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_default_serial_status_id IN NUMBER DEFAULT G_MISS_NUM
,p_lot_split_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_lot_merge_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_inventory_carry_penalty IN NUMBER DEFAULT G_MISS_NUM
,p_operation_slack_penalty IN NUMBER DEFAULT G_MISS_NUM
,p_financing_allowed_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_eam_item_type IN NUMBER DEFAULT G_MISS_NUM
,p_eam_activity_type_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_eam_activity_cause_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_eam_act_notification_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_eam_act_shutdown_status IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_dual_uom_control IN NUMBER DEFAULT G_MISS_NUM
,p_secondary_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_dual_uom_deviation_high IN NUMBER DEFAULT G_MISS_NUM
,p_dual_uom_deviation_low IN NUMBER DEFAULT G_MISS_NUM
,p_contract_item_type_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_subscription_depend_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_serv_req_enabled_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_serv_billing_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_serv_importance_level IN NUMBER DEFAULT G_MISS_NUM
,p_planned_inv_point_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_lot_translate_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_default_so_source_type IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_create_supply_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_substitution_window_code IN NUMBER DEFAULT G_MISS_NUM
,p_substitution_window_days IN NUMBER DEFAULT G_MISS_NUM
,p_ib_item_instance_class IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_config_model_type IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_lot_substitution_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_minimum_license_quantity IN NUMBER DEFAULT G_MISS_NUM
,p_eam_activity_source_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_approval_status IN VARCHAR2 DEFAULT G_MISS_CHAR
--Start: 26 new attributes
,p_tracking_quantity_ind IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_ont_pricing_qty_source IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_secondary_default_ind IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_option_specific_sourced IN NUMBER DEFAULT G_MISS_NUM
,p_vmi_minimum_units IN NUMBER DEFAULT G_MISS_NUM
,p_vmi_minimum_days IN NUMBER DEFAULT G_MISS_NUM
,p_vmi_maximum_units IN NUMBER DEFAULT G_MISS_NUM
,p_vmi_maximum_days IN NUMBER DEFAULT G_MISS_NUM
,p_vmi_fixed_order_quantity IN NUMBER DEFAULT G_MISS_NUM
,p_so_authorization_flag IN NUMBER DEFAULT G_MISS_NUM
,p_consigned_flag IN NUMBER DEFAULT G_MISS_NUM
,p_asn_autoexpire_flag IN NUMBER DEFAULT G_MISS_NUM
,p_vmi_forecast_type IN NUMBER DEFAULT G_MISS_NUM
,p_forecast_horizon IN NUMBER DEFAULT G_MISS_NUM
,p_exclude_from_budget_flag IN NUMBER DEFAULT G_MISS_NUM
,p_days_tgt_inv_supply IN NUMBER DEFAULT G_MISS_NUM
,p_days_tgt_inv_window IN NUMBER DEFAULT G_MISS_NUM
,p_days_max_inv_supply IN NUMBER DEFAULT G_MISS_NUM
,p_days_max_inv_window IN NUMBER DEFAULT G_MISS_NUM
,p_drp_planned_flag IN NUMBER DEFAULT G_MISS_NUM
,p_critical_component_flag IN NUMBER DEFAULT G_MISS_NUM
,p_continous_transfer IN NUMBER DEFAULT G_MISS_NUM
,p_convergence IN NUMBER DEFAULT G_MISS_NUM
,p_divergence IN NUMBER DEFAULT G_MISS_NUM
,p_config_orgs IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_config_match IN VARCHAR2 DEFAULT G_MISS_CHAR
--End: 26 new attributes
,p_Item_Number IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment1 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment2 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment3 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment4 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment5 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment6 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment7 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment8 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment9 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment10 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment11 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment12 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment13 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment14 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment15 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment16 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment17 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment18 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment19 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_segment20 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_summary_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_start_date_active IN DATE DEFAULT G_MISS_DATE
,p_end_date_active IN DATE DEFAULT G_MISS_DATE
,p_attribute_category IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute1 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute2 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute3 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute4 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute5 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute6 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute7 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute8 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute9 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute10 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute11 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute12 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute13 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute14 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute15 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute16 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute17 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute18 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute19 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute20 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute21 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute22 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute23 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute24 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute25 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute26 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute27 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute28 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute29 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_attribute30 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute_category IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute1 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute2 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute3 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute4 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute5 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute6 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute7 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute8 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute9 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute10 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute11 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute12 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute13 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute14 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute15 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute16 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute17 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute18 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute19 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_global_attribute20 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_creation_date IN DATE DEFAULT G_MISS_DATE
,p_created_by IN NUMBER DEFAULT G_MISS_NUM
,p_last_update_date IN DATE DEFAULT G_MISS_DATE
,p_last_updated_by IN NUMBER DEFAULT G_MISS_NUM
,p_last_update_login IN NUMBER DEFAULT G_MISS_NUM
,p_request_id IN NUMBER DEFAULT G_MISS_NUM
,p_program_application_id IN NUMBER DEFAULT G_MISS_NUM
,p_program_id IN NUMBER DEFAULT G_MISS_NUM
,p_program_update_date IN DATE DEFAULT G_MISS_DATE
,p_lifecycle_id IN NUMBER DEFAULT G_MISS_NUM
,p_current_phase_id IN NUMBER DEFAULT G_MISS_NUM
-- Revision attribute parameter
,p_revision_id IN NUMBER DEFAULT G_MISS_NUM
,p_revision_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_revision_label IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_revision_description IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_effectivity_Date IN DATE DEFAULT G_MISS_DATE
,p_rev_lifecycle_id IN NUMBER DEFAULT G_MISS_NUM
,p_rev_current_phase_id IN NUMBER DEFAULT G_MISS_NUM
-- 5208102: Supporting template for UDA's at revisions
,p_rev_template_id IN NUMBER DEFAULT G_MISS_NUM
,p_rev_template_name IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute_category IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute1 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute2 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute3 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute4 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute5 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute6 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute7 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute8 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute9 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute10 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute11 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute12 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute13 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute14 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_rev_attribute15 IN VARCHAR2 DEFAULT G_MISS_CHAR
-- Returned item id
,x_Inventory_Item_Id OUT NOCOPY NUMBER
,x_Organization_Id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_apply_template IN VARCHAR2 DEFAULT 'ALL'
,p_object_version_number IN NUMBER DEFAULT G_MISS_NUM
,p_process_control IN VARCHAR2 DEFAULT NULL
,p_process_item IN NUMBER DEFAULT G_MISS_NUM
--R12 Enhancement adding attributes
,P_CAS_NUMBER IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_CHILD_LOT_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_CHILD_LOT_PREFIX IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_CHILD_LOT_STARTING_NUMBER IN NUMBER DEFAULT G_MISS_NUM
,P_CHILD_LOT_VALIDATION_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_COPY_LOT_ATTRIBUTE_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_DEFAULT_GRADE IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_EXPIRATION_ACTION_CODE IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_EXPIRATION_ACTION_INTERVAL IN NUMBER DEFAULT G_MISS_NUM
,P_GRADE_CONTROL_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_HAZARDOUS_MATERIAL_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_HOLD_DAYS IN NUMBER DEFAULT G_MISS_NUM
,P_LOT_DIVISIBLE_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_MATURITY_DAYS IN NUMBER DEFAULT G_MISS_NUM
,P_PARENT_CHILD_GENERATION_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_PROCESS_COSTING_ENABLED_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_PROCESS_EXECUTION_ENABLED_FL IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_PROCESS_QUALITY_ENABLED_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_PROCESS_SUPPLY_LOCATOR_ID IN NUMBER DEFAULT G_MISS_NUM
,P_PROCESS_SUPPLY_SUBINVENTORY IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_PROCESS_YIELD_LOCATOR_ID IN NUMBER DEFAULT G_MISS_NUM
,P_PROCESS_YIELD_SUBINVENTORY IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_RECIPE_ENABLED_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_RETEST_INTERVAL IN NUMBER DEFAULT G_MISS_NUM
,P_CHARGE_PERIODICITY_CODE IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_REPAIR_LEADTIME IN NUMBER DEFAULT G_MISS_NUM
,P_REPAIR_YIELD IN NUMBER DEFAULT G_MISS_NUM
,P_PREPOSITION_POINT IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_REPAIR_PROGRAM IN NUMBER DEFAULT G_MISS_NUM
,P_SUBCONTRACTING_COMPONENT IN NUMBER DEFAULT G_MISS_NUM
,P_OUTSOURCED_ASSEMBLY IN NUMBER DEFAULT G_MISS_NUM
-- R12 C Attributes
,P_GDSN_OUTBOUND_ENABLED_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_TRADE_ITEM_DESCRIPTOR IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_STYLE_ITEM_FLAG IN VARCHAR2 DEFAULT G_MISS_CHAR
,P_STYLE_ITEM_ID IN NUMBER DEFAULT G_MISS_NUM ) IS
l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Scalar';
SELECT object_version_number
FROM mtl_system_items_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
IF p_transaction_type = G_TTYPE_UPDATE
AND Nvl(p_object_version_number,-1) <> G_MISS_NUM
THEN
OPEN c_get_obj_version_num (cp_inventory_item_id => p_inventory_item_id
,cp_organization_id => p_organization_id);
IF p_transaction_type = G_TTYPE_UPDATE
AND p_Template_Id IS NOT NULL
AND p_Template_Id <> G_MISS_NUM
AND UPPER(p_apply_template) IN ('ALL','BASE_TEMPLATE')
AND INVIDIT3.CHECK_ITEM_APPROVED(p_inventory_item_id,p_organization_id)
AND INSTR(p_process_control,'PLM_UI:Y') = 0
THEN
initialize_template_info
(p_template_id => p_template_id
,p_template_name => p_template_name
,p_organization_id => p_organization_id
,p_organization_code => NULL
,p_tab_index => indx
,x_item_table => l_item_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count);
IF p_transaction_type = G_TTYPE_UPDATE AND l_item_tbl(indx).process_item_record = 1 THEN
EGO_ITEM_PUB.Update_Item_Number (
p_Inventory_Item_Id => REPLACE_G_MISS_NUM(p_inventory_item_id,null)
,p_item_number => p_item_number
,p_New_Segment1 => p_Segment1
,p_New_Segment2 => p_Segment2
,p_New_Segment3 => p_Segment3
,p_New_Segment4 => p_Segment4
,p_New_Segment5 => p_Segment5
,p_New_Segment6 => p_Segment6
,p_New_Segment7 => p_Segment7
,p_New_Segment8 => p_Segment8
,p_New_Segment9 => p_Segment9
,p_New_Segment10 => p_Segment10
,p_New_Segment11 => p_Segment11
,p_New_Segment12 => p_Segment12
,p_New_Segment13 => p_Segment13
,p_New_Segment14 => p_Segment14
,p_New_Segment15 => p_Segment15
,p_New_Segment16 => p_Segment16
,p_New_Segment17 => p_Segment17
,p_New_Segment18 => p_Segment18
,p_New_Segment19 => p_Segment19
,p_New_Segment20 => p_Segment20
,x_Item_Tbl => l_item_created_tbl
,x_return_status => x_return_status);
p_transaction_type IN (G_TTYPE_CREATE, G_TTYPE_UPDATE) THEN
l_item_tbl(indx).item_number := p_item_number;
l_item_tbl(indx).allow_item_desc_update_flag := REPLACE_G_MISS_CHAR(p_allow_item_desc_update_flag,l_item_tbl(indx).allow_item_desc_update_flag);
EGO_ITEM_PUB.Update_Item_Approval_Status (
p_inventory_item_id => x_inventory_item_id
,p_organization_id => x_organization_id
,p_approval_status => l_approval_status );
END IF; -- TRANSACTION_TYPE IN CREATE/UPDATE
PROCEDURE Update_Item_Number(
p_Inventory_Item_Id IN NUMBER
,p_Item_Number IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment1 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment2 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment3 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment4 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment5 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment6 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment7 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment8 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment9 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment10 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment11 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment12 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment13 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment14 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment15 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment16 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment17 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment18 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment19 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_Segment20 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment1 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment2 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment3 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment4 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment5 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment6 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment7 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment8 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment9 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment10 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment11 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment12 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment13 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment14 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment15 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment16 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment17 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment18 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment19 IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_New_Segment20 IN VARCHAR2 DEFAULT G_MISS_CHAR
,x_Item_Tbl IN OUT NOCOPY EGO_Item_PUB.Item_Tbl_Type
,x_return_status OUT NOCOPY VARCHAR2) IS
l_Segment_Rec INV_ITEM_API.Item_rec_type;
,p_token_value2 => 'Update_Item_Number'
,p_translate2 => FALSE
,p_token_name3 => 'API_VERSION'
,p_token_value3 => '1.0'
,p_translate3 => FALSE);
UPDATE mtl_system_items_b
SET segment1 = l_Segment_Rec.segment1
,segment2 = l_Segment_Rec.segment2
,segment3 = l_Segment_Rec.segment3
,segment4 = l_Segment_Rec.segment4
,segment5 = l_Segment_Rec.segment5
,segment6 = l_Segment_Rec.segment6
,segment7 = l_Segment_Rec.segment7
,segment8 = l_Segment_Rec.segment8
,segment9 = l_Segment_Rec.segment9
,segment10 = l_Segment_Rec.segment10
,segment11 = l_Segment_Rec.segment11
,segment12 = l_Segment_Rec.segment12
,segment13 = l_Segment_Rec.segment13
,segment14 = l_Segment_Rec.segment14
,segment15 = l_Segment_Rec.segment15
,segment16 = l_Segment_Rec.segment16
,segment17 = l_Segment_Rec.segment17
,segment18 = l_Segment_Rec.segment18
,segment19 = l_Segment_Rec.segment19
,segment20 = l_Segment_Rec.segment20
WHERE inventory_item_id = P_Inventory_Item_Id;
END Update_Item_Number;
PROCEDURE Update_Item_Approval_Status (
p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_approval_status IN VARCHAR2
,p_nir_id IN NUMBER DEFAULT NULL
,p_commit IN VARCHAR2 DEFAULT G_FALSE)
IS
l_eng_item_flag VARCHAR2(10);
SELECT APPROVAL_STATUS INTO old_approval_status
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
UPDATE MTL_SYSTEM_ITEMS_B
SET APPROVAL_STATUS = p_approval_status
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id
RETURNING ENG_ITEM_FLAG INTO l_eng_item_flag;
DELETE MTL_ITEM_CATEGORIES
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id
AND CATEGORY_SET_ID IN (SELECT CATEGORY_SET_ID
FROM MTL_DEFAULT_CATEGORY_SETS);
INVIDIT2.Insert_Categories
(
X_event => 'INSERT'
, X_item_id => p_inventory_item_id
, X_org_id => p_organization_id
, X_master_org_id => p_organization_id
, X_inventory_item_flag => 'N'
, X_purchasing_item_flag => 'N'
, X_internal_order_flag => 'N'
, X_mrp_planning_code => NULL
, X_serviceable_product_flag => 'N'
, X_costing_enabled_flag => 'N'
, X_eng_item_flag => l_eng_item_flag
, X_customer_order_flag => 'N'
, X_eam_item_type => NULL
, X_contract_item_type_code => NULL
, p_Folder_Category_Set_id => NULL
, p_Folder_Item_Category_id => NULL
, X_last_updated_by => G_USER_ID
);
UPDATE MTL_SYSTEM_ITEMS_B
SET LAST_SUBMITTED_NIR_ID = p_nir_id
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
END Update_Item_Approval_Status;
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
x_item_table(p_tab_index).ALLOW_ITEM_DESC_UPDATE_FLAG := l_orig_item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG;
SELECT context_organization_id
FROM mtl_item_templates mit
WHERE mit.template_id = cp_template_id;
SELECT attribute_name, attribute_value
FROM mtl_item_templ_attributes
WHERE template_id = cp_template_id
AND enabled_flag = 'Y'
AND attribute_name IN
( SELECT a.attribute_name
FROM mtl_item_attributes a
WHERE NVL(a.status_control_code, 3) <> 1
AND a.control_level IN (1, 2)
AND a.attribute_group_id_gui IS NOT NULL
AND a.attribute_name NOT IN
('MTL_SYSTEM_ITEMS.BASE_ITEM_ID',
'MTL_SYSTEM_ITEMS.WIP_SUPPLY_LOCATOR_ID',
'MTL_SYSTEM_ITEMS.WIP_SUPPLY_SUBINVENTORY',
'MTL_SYSTEM_ITEMS.BASE_WARRANTY_SERVICE_ID',
'MTL_SYSTEM_ITEMS.PLANNER_CODE',
'MTL_SYSTEM_ITEMS.ENCUMBRANCE_ACCOUNT',
'MTL_SYSTEM_ITEMS.EXPENSE_ACCOUNT',
'MTL_SYSTEM_ITEMS.SALES_ACCOUNT',
'MTL_SYSTEM_ITEMS.COST_OF_SALES_ACCOUNT',
'MTL_SYSTEM_ITEMS.PLANNING_EXCEPTION_SET')
);
SELECT attribute_name, attribute_value
FROM mtl_item_templ_attributes
WHERE template_id = cp_template_id
AND enabled_flag = 'Y'
AND attribute_name IN
( SELECT a.attribute_name
FROM mtl_item_attributes a
WHERE NVL(a.status_control_code, 3) <> 1
AND a.control_level IN (1, 2)
AND a.attribute_group_id_gui IS NOT NULL
AND a.attribute_name IN
('MTL_SYSTEM_ITEMS.BASE_ITEM_ID',
'MTL_SYSTEM_ITEMS.WIP_SUPPLY_LOCATOR_ID',
'MTL_SYSTEM_ITEMS.WIP_SUPPLY_SUBINVENTORY',
'MTL_SYSTEM_ITEMS.BASE_WARRANTY_SERVICE_ID',
'MTL_SYSTEM_ITEMS.PLANNER_CODE',
'MTL_SYSTEM_ITEMS.ENCUMBRANCE_ACCOUNT',
'MTL_SYSTEM_ITEMS.EXPENSE_ACCOUNT',
'MTL_SYSTEM_ITEMS.SALES_ACCOUNT',
'MTL_SYSTEM_ITEMS.COST_OF_SALES_ACCOUNT',
'MTL_SYSTEM_ITEMS.PLANNING_EXCEPTION_SET')
);
SELECT GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20
FROM MTL_ITEM_TEMPLATES MIT
WHERE MIT.template_id = cp_template_id;
ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ALLOW_ITEM_DESC_UPDATE_FLAG' THEN
x_item_table(p_tab_index).ALLOW_ITEM_DESC_UPDATE_FLAG := cr.ATTRIBUTE_VALUE;
ELSIF (P_TRANSACTION_TYPE = G_TTYPE_UPDATE) THEN
Update_Item_Lifecycle(
P_API_VERSION => P_API_VERSION,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
P_ORGANIZATION_ID => P_ORGANIZATION_ID,
P_CATALOG_GROUP_ID => P_CATALOG_GROUP_ID,
P_LIFECYCLE_ID => P_LIFECYCLE_ID,
P_CURRENT_PHASE_ID => P_CURRENT_PHASE_ID,
P_ITEM_STATUS => P_ITEM_STATUS,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT);
UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = P_LIFECYCLE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = P_CURRENT_PHASE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
UPDATE MTL_SYSTEM_ITEMS_B SET INVENTORY_ITEM_STATUS_CODE = P_ITEM_STATUS
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
UPDATE MTL_PENDING_ITEM_STATUS SET LIFECYCLE_ID = P_LIFECYCLE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
UPDATE MTL_PENDING_ITEM_STATUS SET PHASE_ID = P_CURRENT_PHASE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
UPDATE MTL_PENDING_ITEM_STATUS SET STATUS_CODE = P_ITEM_STATUS
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
Procedure Update_Item_Lifecycle(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ORGANIZATION_ID IN NUMBER,
P_CATALOG_GROUP_ID IN NUMBER,
P_LIFECYCLE_ID IN NUMBER,
P_CURRENT_PHASE_ID IN NUMBER,
P_ITEM_STATUS IN VARCHAR2,
P_COMMIT IN VARCHAR2 DEFAULT G_FALSE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER) IS
CURSOR ego_item_assigned_org_csr (
v_inventory_item_id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,
v_master_organization_id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE)
IS
SELECT ORGANIZATION_ID
FROM MTL_SYSTEM_ITEMS_VL
WHERE INVENTORY_ITEM_ID = v_inventory_item_id
AND ORGANIZATION_ID <> v_master_organization_id;
SAVEPOINT Update_Item_Lifecycle;
UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = NULL
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = NULL
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
INSERT INTO MTL_PENDING_ITEM_STATUS(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
EFFECTIVE_DATE,
IMPLEMENTED_DATE,
PENDING_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LIFECYCLE_ID,
PHASE_ID,
STATUS_CODE)
VALUES(
P_INVENTORY_ITEM_ID,
L_MASTER_ORGANIZATION_ID,
L_SYSDATE,
L_SYSDATE,
'N',
L_SYSDATE,
g_USER_ID,
L_SYSDATE,
g_USER_ID,
P_LIFECYCLE_ID,
P_CURRENT_PHASE_ID,
P_ITEM_STATUS);
UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = P_LIFECYCLE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = L_MASTER_ORGANIZATION_ID;
UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = P_CURRENT_PHASE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ORGANIZATION_ID = L_MASTER_ORGANIZATION_ID;
INSERT INTO MTL_PENDING_ITEM_STATUS(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
EFFECTIVE_DATE,
PENDING_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LIFECYCLE_ID,
PHASE_ID,
STATUS_CODE)
VALUES(
P_INVENTORY_ITEM_ID,
L_ITEM_ASSIGNED_ORG_REC.ORGANIZATION_ID,
L_SYSDATE,
'Y',
L_SYSDATE,
g_USER_ID,
L_SYSDATE,
g_USER_ID,
P_LIFECYCLE_ID,
P_CURRENT_PHASE_ID,
P_ITEM_STATUS);
UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = P_LIFECYCLE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = P_CURRENT_PHASE_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
INSERT INTO MTL_PENDING_ITEM_STATUS(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
EFFECTIVE_DATE,
IMPLEMENTED_DATE,
PENDING_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LIFECYCLE_ID,
PHASE_ID,
STATUS_CODE)
VALUES(
P_INVENTORY_ITEM_ID,
L_ITEM_ASSIGNED_ORG_REC.ORGANIZATION_ID,
L_SYSDATE,
L_SYSDATE,
'N',
L_SYSDATE,
g_USER_ID,
L_SYSDATE,
g_USER_ID,
P_LIFECYCLE_ID,
P_CURRENT_PHASE_ID,
P_ITEM_STATUS);
Update_Item_Attr_Ext(
P_API_VERSION => P_API_VERSION,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
P_ITEM_CATALOG_GROUP_ID => P_CATALOG_GROUP_ID,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT);
ROLLBACK TO Update_Item_Lifecycle;
ROLLBACK TO Update_Item_Lifecycle;
END Update_Item_Lifecycle;
Procedure Update_Item_Attr_Ext(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ITEM_CATALOG_GROUP_ID IN NUMBER,
P_COMMIT IN VARCHAR2 DEFAULT G_FALSE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER) IS
BEGIN
-- create save point
IF FND_API.To_Boolean(p_commit) THEN
SAVEPOINT Update_Item_Attr_Ext;
IF (P_ITEM_CATALOG_GROUP_ID IS NULL) THEN --delete query changed by absinha for Bug 3542129
DELETE FROM EGO_MTL_SY_ITEMS_EXT_B WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ATTR_GROUP_ID NOT IN (SELECT ATTR_GROUP_ID FROM EGO_ATTR_GROUPS_V WHERE APPLICATION_ID = 431
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND (ATTR_GROUP_NAME = 'ItemDetailDesc' OR ATTR_GROUP_NAME = 'ItemDetailImage'));
DELETE FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND ATTR_GROUP_ID NOT IN (SELECT ATTR_GROUP_ID FROM EGO_ATTR_GROUPS_V WHERE APPLICATION_ID = 431
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP' AND (ATTR_GROUP_NAME = 'ItemDetailDesc' OR ATTR_GROUP_NAME = 'ItemDetailImage'));
UPDATE EGO_MTL_SY_ITEMS_EXT_B
SET ITEM_CATALOG_GROUP_ID = P_ITEM_CATALOG_GROUP_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
UPDATE EGO_MTL_SY_ITEMS_EXT_TL
SET ITEM_CATALOG_GROUP_ID = P_ITEM_CATALOG_GROUP_ID
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
ROLLBACK TO Update_Item_Attr_Ext;
ROLLBACK TO Update_Item_Attr_Ext;
END Update_Item_Attr_Ext;
SELECT MP.MASTER_ORGANIZATION_ID INTO L_MASTER_ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = P_ORGANIZATION_ID;
SELECT LOOKUP_CODE2 INTO L_CONTROL_LEVEL
FROM MTL_ITEM_ATTRIBUTES_V
WHERE ATTRIBUTE_NAME = P_ITEM_ATTRIBUTE;
FOR i IN (SELECT item_catalog_group_id
FROM mtl_item_catalog_groups_b b
CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id
START WITH b.item_catalog_group_id = p_catalog_group_id)
LOOP
IF l_str_cat_id IS NULL THEN
l_str_cat_id := l_str_cat_id || i.item_catalog_group_id;
l_sql := 'SELECT COUNT(1) FROM mtl_system_items_b a ' ||
'WHERE a.organization_id = ' || p_organization_id;
l_sql := 'SELECT COUNT(1) FROM mtl_system_items_b a ' ||
'WHERE a.organization_id = ' || p_organization_id ||
' AND item_catalog_group_id in (' || l_str_cat_id || ')';
select count(*) into l_total_count
from mtl_item_categories a
where category_id in (
select category_id
from mtl_category_set_valid_cats
start with category_id = P_CATEGORY_ID
and category_set_id = P_CATEGORY_SET_ID --Corrected the connect clause in count query
connect by prior category_id = parent_category_id
and category_set_id = P_CATEGORY_SET_ID)
and a.organization_id = P_ORGANIZATION_ID
and a.category_set_id = P_CATEGORY_SET_ID;
select count(*) into l_total_count
from mtl_item_categories a
where a.organization_id = P_ORGANIZATION_ID
and a.category_set_id = P_CATEGORY_SET_ID;
SELECT IC.CATEGORY_ID,
IC.PARENT_CATEGORY_ID
FROM MTL_CATEGORY_SET_VALID_CATS IC
START WITH CATEGORY_ID = p_category_id --3030474
AND CATEGORY_SET_ID = p_category_set_id
CONNECT BY PRIOR PARENT_CATEGORY_ID = CATEGORY_ID
AND CATEGORY_SET_ID = p_category_set_id;
SELECT CATEGORY_SET_NAME into l_category_set_name
FROM MTL_CATEGORY_SETS_VL
WHERE CATEGORY_SET_ID = P_CATEGORY_SET_ID;
SELECT C.CONCATENATED_SEGMENTS into l_category_name
FROM MTL_CATEGORIES_KFV C
WHERE C.CATEGORY_ID = l_parent_categories.CATEGORY_ID;
SELECT C.CONCATENATED_SEGMENTS into l_category_hierarchy_names
FROM MTL_CATEGORIES_KFV C
WHERE C.CATEGORY_ID = p_category_id;
SELECT ic.item_catalog_group_id
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS
(
SELECT olc.object_classification_code CatalogId
FROM ego_obj_type_lifecycles olc, fnd_objects o
WHERE o.obj_name = 'EGO_ITEM'
AND olc.object_id = o.object_id
AND olc.lifecycle_id = cp_lifecycle_id
AND olc.object_classification_code = to_char(ic.item_catalog_group_id)
)
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = cp_catalog_category_id;
' SELECT item_catalog_group_id, lifecycle_id, current_phase_id' ||
' FROM mtl_system_items_b ' ||
' WHERE inventory_item_id = :1' ||
' AND organization_id = :2';
SELECT DISTINCT attribute_group_id
BULK COLLECT INTO l_attr_group_ids
FROM ego_templ_attributes
WHERE template_id = p_template_id;
l_attr_grp_dyn_sql := l_attr_grp_dyn_sql || 'SELECT DISTINCT ATTRIBUTE_GROUP_ID ';
l_attr_grp_dyn_sql := l_attr_grp_dyn_sql || ' SELECT E.DESCRIPTIVE_FLEXFIELD_NAME ';
, position => 1 -- select position --
, c_tab => l_attr_grp_table -- table of numbers --
, cnt => 2500 -- rows requested --
, lower_bound => 1 -- start at --
);
SELECT ORGANIZATION_ID INTO l_organization_id
FROM MTL_PARAMETERS
WHERE ORGANIZATION_CODE = p_organization_code;
SELECT ORGANIZATION_ID INTO l_organization_id
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT INVENTORY_ITEM_ID INTO l_inventory_item_id
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE ORGANIZATION_ID = l_organization_id
AND CONCATENATED_SEGMENTS = p_item_number;
SELECT INVENTORY_ITEM_ID INTO l_inventory_item_id
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE ORGANIZATION_ID = l_organization_id
AND INVENTORY_ITEM_ID = p_inventory_item_id;
p_Message_Name =>'INV_IOI_ITEM_UPDATE_PRIV');
ELSIF p_transaction_type = 'DELETE' THEN
INV_ITEM_CATEGORY_PUB.Delete_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_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
);