--PROCEDURE xx_update_item_dff
--IS
DECLARE
CURSOR CUR_UPDATE_DFF IS
SELECT XX.ROWID,
XX.ITEM_CODE,
XX.ORGANIZATION_CODE,
XX.FIXED_ORDER_QUANTITY,
XX.MINIMUM_ORDER_QUANTITY,
XX.MAXIMUM_ORDER_QUANTITY,
XX.STATUS,
XX.MESSAGE
FROM XXBEX_ITEM_UPDATE_STG_JG XX
WHERE 1=1;
-- AND XX.ITEM_CODE = '4010001388';
l_item_table ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER (10);
-- xx_msg_data VARCHAR2 (1000);
lv_message_list error_handler.error_tbl_type;
l_counter NUMBER := 0;
x_flag VARCHAR2(10);
x_error VARCHAR2(1000);
lv_item_type VARCHAR2(100);
lv_item_long_description VARCHAR2(4000);
lv_expense_acct NUMBER;
lv_cogs_acct NUMBER;
lv_sales_acct NUMBER;
lv_planner_code VARCHAR2(240);
lv_item_id NUMBER;
lv_organization_id NUMBER;
l_template_id NUMBER;
lv_inventory_item_id Number;
lv_template_id NUMBER;
lv_shelf_life_con VARCHAR2(240);
lv_lot_control VARCHAR2(240);
lv_make_buy VARCHAR2(240);
l_error_message VARCHAR2(2400);
lv_proc_proc_val NUMBER;
LV_FIXED_ORDER_QUANTITY NUMBER;
LV_minimum_order_quantity NUMBER;
LV_maximum_order_quantity NUMBER;
-- lv_item_type VARCHAR2(240);
lv_lot_status_enabled VARCHAR2(10);
lv_lot_merge_enabled VARCHAR2(10);
lv_lot_split_enabled VARCHAR2(10);
lv_lot_translate_enabled VARCHAR2(10);
lv_default_lot_status_id NUMBER;
xx_inventory_item_id NUMBER;
xx_organization_id NUMBER;
xx_return_status VARCHAR2 (1);
xx_msg_count NUMBER;
xx_msg_data VARCHAR2 (2000);
BEGIN
FOR cur_update_dff_rec IN cur_update_dff
LOOP
l_item_table.delete;
lv_item_id := NULL;
lv_organization_id:= NULL;
lv_item_type := NUll;
lv_item_long_description :=NULL;
lv_cogs_acct := NULL;
lv_sales_acct := NULL;
lv_expense_acct := NULL;
lv_planner_code := NULL;
l_counter := 1;
lv_item_type := NULL;
-- l_template_id := NULL;
lv_inventory_item_id := NULL;
lv_template_id := NULL;
lv_shelf_life_con := NULL;
lv_lot_control := NULL;
lv_make_buy := NULL;
l_error_message := NULL;
lv_proc_proc_val := NULL;
lv_lot_status_enabled := NULL;
lv_lot_merge_enabled := NULL;
lv_lot_split_enabled := NULL;
lv_lot_translate_enabled := NULL;
lv_default_lot_status_id := NULL;
lv_fixed_order_quantity := NULL;
lv_minimum_order_quantity := NULL;
LV_maximum_order_quantity := NULL;
-- lv_item_type := NULL;
xx_inventory_item_id := NULL;
xx_organization_id := NULL;
xx_return_status := NULL;
xx_msg_count := NULL;
xx_msg_data := NULL;
x_flag := 'N';
x_error := NULL;
/*
BEGIN
SELECT TEMPLATE_NAME,TEMPLATE_ID
INTO lv_item_type,lv_template_id
FROM mtl_item_templates
WHERE template_name = rtrim(ltrim(cur_update_dff_rec.ITEM_TYPE));
EXCEPTION WHEN OTHERS THEN
x_flag := 'E';
x_error := x_error||' Item TYPE is Invalid..';
lv_template_id := NULL;
lv_item_type := NULL;
END;
*/
BEGIN
SELECT ORGANIZATION_ID
INTO lv_organization_id
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = rtrim(ltrim(cur_update_dff_rec.ORGANIZATION_CODE));
EXCEPTION WHEN OTHERS THEN
x_flag := 'E';
x_error := x_error||' Organization is Invalid..';
lv_organization_id := NULL;
END;
If lv_organization_id is not null then
BEGIN
Select inventory_item_id
Into lv_inventory_item_id
from mtl_system_items_b
Where segment1 = rtrim(ltrim(cur_update_dff_rec.ITEM_CODE))
and organization_id = lv_organization_id;
EXCEPTION WHEN OTHERS THEN
x_flag := 'E';
x_error := x_error||' Item is not assigned to given organization..';
lv_inventory_item_id := NULL;
END;
End If;
/* lv_proc_proc_val := 0;
-- Elsif lv_make_buy = 2 Then
Begin
select postprocessing_lead_time
into lv_proc_proc_val
from mtl_system_items_b
where inventory_item_id = lv_inventory_item_id
and organization_id = lv_organization_id;
Exception when others then
lv_proc_proc_val := 0;
End;
-- End If;
*/
-- If x_flag <> 'E' and x_error is NULL Then
EGO_ITEM_PUB.PROCESS_ITEM (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_true
,p_commit => fnd_api.g_true
,p_transaction_type => 'UPDATE'
,p_language_code => 'US'
,p_organization_id => lv_organization_id
,p_master_organization_id => 1823
,p_inventory_item_id => lv_inventory_item_id
-- ,p_Long_Description => cur_update_dff_rec.item_long_description
-- ,p_Description => cur_update_dff_rec.item_desc
-- ,p_item_type => cur_update_dff_rec.item_type --lv_item_type ---NEW P2
-- ,p_attribute2 => cur_update_dff_rec.attribute2
-- ,p_attribute3 => cur_update_dff_rec.attribute3
-- ,p_attribute10 => cur_update_dff_rec.attribute10
-- ,p_attribute11 => cur_update_dff_rec.attribute11
-- ,p_attribute22 => cur_update_dff_rec.attribute22
-- ,p_attribute23 => cur_update_dff_rec.attribute23
-- ,p_attribute24 => cur_update_dff_rec.attribute24
,p_source_organization_id => lv_organization_id
-- ,p_Segment1 => cur_update_rec.CAT_SEGMENT1 Category is update in XX_UPDATE_CAT_ITEM Procedure
-- ,p_Segment2 => cur_update_rec.CAT_SEGMENT2
-- ,p_Segment3 => cur_update_rec.CAT_SEGMENT3
-- ,p_Segment4 => cur_update_rec.CAT_SEGMENT4
-- ,p_Segment5 => cur_update_rec.CAT_SEGMENT5
-- ,p_Segment6 => cur_update_rec.CAT_SEGMENT6
-- ,p_Segment7 => cur_update_rec.CAT_SEGMENT7
-- ,p_Segment8 => cur_update_rec.CAT_SEGMENT8 Category is update in XX_UPDATE_CAT_ITEM Procedure
-- ,p_Item_Catalog_Group_Id => NULL--cur_update_rec.
-- ,p_attribute16 => cur_update_rec.attribute16
-- ,p_shelf_life_days => lv_shelf_life_con
-- ,p_PLANNER_CODE => cur_update_rec.PLANNER_CODE --lv_planner_code
-- ,p_planning_make_buy_code => lv_make_buy
-- ,p_template_id => lv_template_id
-- ,p_lot_control_code => lv_lot_control
-- ,p_must_use_approved_vendor_fl => REPLACE(cur_update_rec.MUST_USE_APP_VEN_FLAG,CHR(13),'')
-- ,p_COST_OF_SALES_ACCOUNT => lv_cogs_acct
-- ,p_EXPENSE_ACCOUNT => lv_expense_acct
-- ,p_SALES_ACCOUNT => lv_sales_acct
,p_fixed_order_quantity => cur_update_dff_rec.FIXED_ORDER_QUANTITY --lv_fixed_order_quantity --
-- ,p_fixed_lot_multiplier => cur_update_rec.FIXED_LOT_MULTIPLIER
-- ,p_fixed_days_supply => cur_update_rec.FIXED_DAYS_SUPPLY
-- ,p_inventory_planning_code => cur_update_rec.INVENTORY_PLANNING_METHOD
-- ,p_min_minmax_quantity => cur_update_rec.INV_PLANNING_METHOD_MINIMUM
-- ,p_max_minmax_quantity => cur_update_rec.INV_PLANNING_METHOD_MAXIMUM
,p_minimum_order_quantity => cur_update_dff_rec.MINIMUM_ORDER_QUANTITY --lv_minimum_order_quantity --
,p_maximum_order_quantity => cur_update_dff_rec.MAXIMUM_ORDER_QUANTITY --lv_maximum_order_quantity --
-- ,p_source_type => cur_update_rec.SOURCE_TYPE
-- ,p_source_subinventory => cur_update_rec.SOURCE_SUB_INVENTORY
-- ,p_mrp_safety_stock_code => cur_update_rec.SAFETY_STOCK_METHOD
-- ,p_safety_stock_bucket_days => cur_update_rec.SAFETY_STOCK_BUCKET_DAYS
-- ,p_mrp_safety_stock_percent => cur_update_rec.SAFETY_STOCK_PERCENT
-- ,p_list_price_per_unit => cur_update_rec.LIST_PRICE_PER_UNIT
-- ,p_mrp_planning_code => cur_update_rec.MPS_MRP_MPP_PLANNING_METHOD
-- ,p_ato_forecast_control => cur_update_rec.FORECAST_CONTROL
-- ,p_end_assembly_pegging_flag => cur_update_rec.PEGGING
-- ,p_rounding_control_type => cur_update_rec.ROUND_ORDER_QUANTITIES
-- ,p_planning_time_fence_code => cur_update_rec.PLANNING_TIME_FENCE
-- ,p_planning_time_fence_days => cur_update_rec.PLANNING_TIME_FENCE_DAYS
-- ,p_preprocessing_lead_time => cur_update_rec.PREPROCESSING_LEAD_TIME
-- ,p_full_lead_time => cur_update_rec.PROCESSING_LEAD_TIME
-- ,p_postprocessing_lead_time => lv_proc_proc_val
-- ,p_fixed_lead_time => cur_update_rec.FIXED_LEAD_TIME
-- ,p_lot_status_enabled => lv_lot_status_enabled
-- ,p_lot_merge_enabled => lv_lot_merge_enabled
-- ,p_lot_split_enabled => lv_lot_split_enabled
-- ,p_lot_translate_enabled => lv_lot_translate_enabled
-- ,p_default_lot_status_id => lv_default_lot_status_id
,p_creation_date => sysdate
,p_created_by => -1
,p_last_update_date => sysdate
,p_last_updated_by => -1
,x_inventory_item_id => xx_inventory_item_id
,x_organization_id => xx_organization_id
,x_return_status => xx_return_status
,x_msg_count => xx_msg_count
,x_msg_data => xx_msg_data
);
COMMIT;
If xx_return_status <> 'S' Then
error_handler.get_message_list (x_message_list => lv_message_list);
FOR i IN 1 .. lv_message_list.COUNT
LOOP
xx_msg_data := xx_msg_data || '/' || lv_message_list (i).MESSAGE_TEXT;
END LOOP;
UPDATE XXBEX_ITEM_UPDATE_STG_JG
SET STATUS = 'E'
,MESSAGE = xx_msg_data
WHERE rowid = cur_update_dff_rec.rowid;
COMMIT;
FND_FILE.PUT_LINE (fnd_file.LOG,'Item Code:'||cur_update_dff_rec.item_code);
-- FND_FILE.PUT_LINE (fnd_file.LOG,'API EGO_ITEM_PUB.PROCESS_ITEM Failed to Update Item');
ELSE
FND_FILE.PUT_LINE (fnd_file.LOG,x_return_status);
UPDATE XXBEX_ITEM_UPDATE_STG_JG
SET STATUS = 'P',
MESSAGE = NULL
WHERE rowid = cur_update_dff_rec.rowid;
COMMIT;
End If;
END LOOP;
END;
No comments:
Post a Comment