[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE_TRANSACTION_TYPE CONSTANT VARCHAR2(10) := 'UPDATE';
SELECT 'x'
FROM mtl_system_items_b
WHERE
inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
g_in_item_tbl(0).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
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
g_in_item_tbl(0).ALLOW_ITEM_DESC_UPDATE_FLAG := cr.ATTRIBUTE_VALUE;
ELSIF (P_TRANSACTION_TYPE = G_UPDATE_TRANSACTION_TYPE) 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_COMMIT 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,
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;
developer_debug ('::2960442::Update_Item_Lifecycle::start');
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
);
developer_debug ('::2960442::Update_Item_Attr_Ext::start');
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);
developer_debug ('::2960442::Update_Item_Attr_Ext::end');
developer_debug ('::2960442::Update_Item_Lifecycle::end');
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_COMMIT IN VARCHAR2,
P_INVENTORY_ITEM_ID IN NUMBER,
P_ITEM_CATALOG_GROUP_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER
)
IS
BEGIN
SAVEPOINT Update_Item_Attr_Ext;
developer_debug ('::2960442::Update_Item_Attr_Ext::start');
DELETE FROM EGO_MTL_SY_ITEMS_EXT_B WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
DELETE FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
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;
developer_debug ('::2960442::Update_Item_Attr_Ext::end');
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;
PROCEDURE Delete_Extra_Item_Long_Desc_AG (
p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
) IS
l_ext_id_to_delete_list VARCHAR2(200);
CURSOR Ext_Id_To_Delete_Cursor (
cp_inventory_item_id IN NUMBER
,cp_organization_id IN NUMBER
) IS
SELECT EXTENSION_ID
FROM EGO_MTL_SY_ITEMS_EXT_VL
WHERE INVENTORY_ITEM_ID = cp_inventory_item_id
AND ORGANIZATION_ID = cp_organization_id
AND ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = 431
AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ItemDetailDesc')
AND C_EXT_ATTR1 = 'D';
FOR ext_rec IN Ext_Id_To_Delete_Cursor(p_inventory_item_id, p_organization_id)
LOOP
IF (Ext_Id_To_Delete_Cursor%ROWCOUNT > 1) THEN
l_ext_id_to_delete_list := l_ext_id_to_delete_list || ext_rec.EXTENSION_ID || ',';
IF (LENGTH(l_ext_id_to_delete_list) > 0) THEN
-----------------------------------------------
-- ...trim the trailing ',' from the list... --
-----------------------------------------------
l_ext_id_to_delete_list := SUBSTR(l_ext_id_to_delete_list, 1, LENGTH(l_ext_id_to_delete_list) - LENGTH(','));
l_dynamic_sql := ' DELETE FROM EGO_MTL_SY_ITEMS_EXT_B'||
' WHERE EXTENSION_ID IN ('||l_ext_id_to_delete_list||')';
l_dynamic_sql := ' DELETE FROM EGO_MTL_SY_ITEMS_EXT_TL'||
' WHERE EXTENSION_ID IN ('||l_ext_id_to_delete_list||')';
END Delete_Extra_Item_Long_Desc_AG;
SELECT object_id
FROM fnd_objects
WHERE obj_name = cp_object_name;
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'EGO';
SELECT revision, revision_id
FROM MTL_ITEM_REVISIONS_B
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id
AND effectivity_date <= SYSDATE
ORDER BY effectivity_date desc;
Delete_Extra_Item_Long_Desc_AG(x_inventory_item_id, x_organization_id);
select count(*) INTO l_total_count
from mtl_system_items_b a
where item_catalog_group_id
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
)
and a.organization_id = p_organization_id
and a.eng_item_flag = p_item_type;
select count(*) INTO l_total_count
from mtl_system_items_b a
where item_catalog_group_id
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
)
and a.organization_id = p_organization_id;
select count(*) into l_total_count
from mtl_item_categories a , mtl_system_items_b b
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
and a.inventory_item_id = b.inventory_item_id
and a.organization_id = b.organization_id
and b.eng_item_flag = P_ITEM_TYPE;
select count(*) into l_total_count
from mtl_item_categories a , mtl_system_items_b b
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
and a.inventory_item_id = b.inventory_item_id
and a.organization_id = b.organization_id;
select count(*) into l_total_count
from mtl_item_categories a , mtl_system_items_b b,mtl_category_set_valid_cats c
where a.organization_id = P_ORGANIZATION_ID
and a.category_set_id = P_CATEGORY_SET_ID
and c.category_set_id = P_CATEGORY_SET_ID
and c.category_id = a.category_id
and a.inventory_item_id = b.inventory_item_id
and a.organization_id = b.organization_id
and b.eng_item_flag = P_ITEM_TYPE;
select count(*) into l_total_count
from mtl_item_categories a , mtl_system_items_b b,mtl_category_set_valid_cats c
where a.organization_id = P_ORGANIZATION_ID
and a.category_set_id = P_CATEGORY_SET_ID
and c.category_set_id = P_CATEGORY_SET_ID
and c.category_id = a.category_id
and a.inventory_item_id = b.inventory_item_id
and a.organization_id = b.organization_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;
,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
-- attribute not in the form
,p_serviceable_item_class_id IN NUMBER DEFAULT G_MISS_NUM
-- attribute not in the form
,p_base_warranty_service_id IN NUMBER DEFAULT G_MISS_NUM
-- attribute not in the form
,p_warranty_vendor_id IN NUMBER DEFAULT G_MISS_NUM
-- attribute not in the form
,p_max_warranty_amount IN NUMBER DEFAULT G_MISS_NUM
-- attribute not in the form
,p_response_time_period_code IN VARCHAR2 DEFAULT G_MISS_CHAR
-- attribute not in the form
,p_response_time_value IN NUMBER DEFAULT G_MISS_NUM
-- attribute not in the form
,p_primary_specialist_id IN NUMBER DEFAULT G_MISS_NUM
-- attribute not in the form
,p_secondary_specialist_id IN NUMBER DEFAULT G_MISS_NUM
,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
-- derived attributes
--,p_service_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
--,p_vendor_warranty_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
--,p_usage_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
,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
--added for 11.5.9 enh
,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
--added for 11.5.10 enh
,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_approval_status IN VARCHAR2 DEFAULT G_MISS_CHAR
--
,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_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_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
-- 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
) IS
------------------------------------------------------------------
-- Start Of comments
--
-- Function name : Process_Item
-- Type : Public
-- Pre-reqs : IOI should be functional
-- Functionality : Process (CREATE/UPDATE) one item using IOI
-- Notes : Scalar Signature to Process Item
--
--
-- History :
-- 23-SEP-2003 Sridhar Rajaparthi Creation (bug 3143834)
--
-- END OF comments
------------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Scalar';
IF p_transaction_type IN (G_CREATE_TRANSACTION_TYPE, G_UPDATE_TRANSACTION_TYPE) THEN
--
-- copy the passed values into the table.
-- create the table to store all the values.
--
-- pre IOI processing
--
IF p_transaction_type = G_CREATE_TRANSACTION_TYPE THEN
l_item_tbl(indx).item_number := p_item_number;
l_item_tbl(indx).allow_item_desc_update_flag := p_allow_item_desc_update_flag;
ELSIF p_transaction_type = G_UPDATE_TRANSACTION_TYPE THEN
--
-- transaction type = 'UPDATE'
--
developer_debug (' ISS: calling Update_item_number ');
EGO_ITEM_PUB.Update_Item_Number (
p_Inventory_Item_Id => x_inventory_item_id
,p_Item_Number => p_Item_Number
,p_Segment1 => NULL
,p_Segment2 => NULL
,p_Segment3 => NULL
,p_Segment4 => NULL
,p_Segment5 => NULL
,p_Segment6 => NULL
,p_Segment7 => NULL
,p_Segment8 => NULL
,p_Segment9 => NULL
,p_Segment10 => NULL
,p_Segment11 => NULL
,p_Segment12 => NULL
,p_Segment13 => NULL
,p_Segment14 => NULL
,p_Segment15 => NULL
,p_Segment16 => NULL
,p_Segment17 => NULL
,p_Segment18 => NULL
,p_Segment19 => NULL
,p_Segment20 => NULL
,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
);
developer_debug (' ISS: returned from Update_Item_Number -> ' || x_return_status);
developer_debug (' ISS: calling EGO_ITEM_PUB.Update_Item_Approval_Status ');
EGO_ITEM_PUB.Update_Item_Approval_Status (
p_inventory_item_id => x_inventory_item_id
,p_organization_id => x_organization_id
,p_approval_status => p_approval_status
);