CREATE OR REPLACE PACKAGE APPS.XX_ITEM_IMPORT_PKG IS
PROCEDURE XX_ITEM_IMPORT_PROC(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.xx_item_import_pkg
IS
PROCEDURE xx_item_import_proc (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
-- ===========================
-- Cursor declaration Starts
-- ===========================
CURSOR c1
IS
-- SELECT ROWID,a.*
-- FROM xx_item_import_stg a
-- WHERE organization_id in (470)
-- AND SEGMENT1 IN (select SEGMENT1 from mtl_system_items_b where organization_id=470
--AND SEGMENT1 NOT IN (select SEGMENT1 from mtl_system_items_b where organization_id=680));
SELECT ROWID,a.*
FROM xx_item_import_stg a
WHERE status_flag = 'N'
-- and organization_id in (165);
--and INVENTORY_ITEM_ID in (2);
--and INVENTORY_ITEM_ID between 101 and 1000;
v_organization_id NUMBER;
v_err_msg VARCHAR2 (9000);
v_err_flag VARCHAR2 (1);
v_template_id NUMBER;
v_uom_code VARCHAR2 (20);
v_category_set_id NUMBER;
v_category_id NUMBER;
v_seg_count NUMBER;
v_seg_count_int NUMBER;
v_ass_category_id NUMBER;
v_template_name VARCHAR (30);
v_s_uom_code VARCHAR (30);
v_secondary_default_ind VARCHAR (30);
v_rec_cnt NUMBER;
v_tracking_quantity_ind VARCHAR (30);
v_organization_name VARCHAR (300);
v_dual_uom_deviation_high NUMBER;
v_dual_uom_deviation_low NUMBER;
cost_of_sales_account VARCHAR2(181);
expense_account VARCHAR2(181);
encumbrance_account VARCHAR2(181);
sales_account VARCHAR2(181);
v_desc VARCHAR2(181);
l_conc_segs gl_code_combinations_kfv.concatenated_segments%TYPE;
l_segment1 gl_code_combinations.segment1%TYPE;
l_segment2 gl_code_combinations.segment2%TYPE;
l_segment3 gl_code_combinations.segment3%TYPE;
l_segment4 gl_code_combinations.segment4%TYPE;
l_segment5 gl_code_combinations.segment5%TYPE;
l_segment6 gl_code_combinations.segment6%TYPE;
l_segment7 gl_code_combinations.segment7%TYPE;
x_code_combination VARCHAR2(181 byte);
l_error_msg1 varchar2(300);
l_error_msg2 varchar2(300);
l_ccid Number;
V_INVENTORY_ITEM_ID NUMBER;
BEGIN -- Main block validation
FOR i IN c1
LOOP -- Loop for validation
v_err_flag := 'Y';
v_err_msg := NULL;
v_seg_count := NULL;
v_organization_id := NULL;
v_template_name := NULL;
v_uom_code := NULL;
v_s_uom_code := NULL;
v_category_set_id := NULL;
v_category_id := NULL;
v_secondary_default_ind := NULL;
v_dual_uom_deviation_high := NULL;
v_dual_uom_deviation_low := NULL;
v_tracking_quantity_ind := NULL;
-- ===========================
-- Checking for Duplicates
-- ===========================
--select organization_name into v_organization_name from org_organization_definitions where organization_id=v_organization_id;
-- v_seg_count:=null;
-- SELECT COUNT(segment1)
-- INTO v_seg_count
-- FROM mtl_system_items_b
-- WHERE UPPER(segment1) = TRIM(UPPER(i.segment1))
-- AND organization_id = v_organization_id;
-- IF (v_seg_count = 0 )
---- AND v_seg_count_int = 0
-- THEN
-- NULL;
-- ELSE
-- v_err_flag := 'E';
-- v_err_msg := v_err_msg||'/'||'Duplicate item number : '||i.segment1||' '||v_organization_id;
--
-- END IF;
--
--
-- ====================================
-- Inventory organization validation
-- ===================================
-- BEGIN
-- v_organization_id := NULL;
-- SELECT organization_id
-- INTO v_organization_id
-- FROM org_organization_definitions
-- WHERE --organization_code=trim(i.ORGANIZATION_CODE);
-- organization_id = 653;
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- v_err_flag := 'E';
-- v_err_msg :=
-- v_err_msg
-- || ','
-- || 'Invalid Organization Code for the item -- '
-- || i.segment1;
-- -- dbms_output.put_line('Invalid Organization Code for the item -- '||i.item_segment1);
-- fnd_file.put_line (fnd_file.LOG, SQLCODE || '' || SQLERRM);
-- fnd_file.put_line
-- (fnd_file.LOG,
-- 'Invalid Organization Code for the item -- '
-- || i.segment1
-- );
-- END;
-- ====================================
-- Inventory COST_OF_SALES_ACCOUNT validation
-- ===================================
--EXEC AIRTEL_CREATE_CCID(p_segment1,p_segment2,p_segment3,p_segment4,p_segment5,p_segment6,p_segment7,x_error_msg1,x_error_msg2,);
/* JNGUGI SELECT segment1,segment2,segment3,segment4,segment5,segment6,segment7
INTO l_segment1,l_segment2,l_segment3,l_segment4,l_segment5,l_segment6,l_segment7
FROM gl_code_combinations
WHERE segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
|| '.'
|| segment7 = l_conc_segs
AND chart_of_accounts_id = 50445;
--EXECUTE IMMEDIATE AIRTEL_CREATE_CCID(l_segment1,l_segment2,l_segment3,l_segment4,l_segment5,l_segment6,l_segment7);
EXECUTE IMMEDIATE AIRTEL_CREATE_CCID(510,0000,204211,0000,0000,0000,0000);*/
--===========================
-- Checking for Item Description
-- ===========================
--select organization_name into v_organization_name from org_organization_definitions where organization_id=v_organization_id;
BEGIN
SELECT DESCRIPTION,INVENTORY_ITEM_ID
INTO v_desc,V_INVENTORY_ITEM_ID
FROM mtl_system_items_b
WHERE UPPER(segment1) = TRIM(UPPER(i.segment1))
and rownum=1;
EXCEPTION
WHEN OTHERS THEN
NULL;
--v_desc:=NULL;
END;
BEGIN
-- SELECT DECODE(ORGANIZATION_ID,192,704,
-- 193,705,
-- 194,733,
-- 195,703,
-- 196,734,
-- 197,735,
-- 198,736,
-- 230,701,
-- 250,702,
-- 290,715,
-- 310,716,
-- 392,709,
-- 410,698,
-- 411,699,
-- 412,700,
-- 413,696,
-- 414,697,
-- 415,694,
-- 416,693,
-- 417,695,
-- 430,712)
-- INTO V_ORGANIZATION_ID
-- FROM mtl_parameters
-- WHERE ORGANIZATION_ID = i.ORGANIZATION_ID
-- and rownum=1;
SELECT DECODE(ORGANIZATION_ID,165,631
)
INTO V_ORGANIZATION_ID
FROM mtl_parameters
WHERE ORGANIZATION_ID = i.ORGANIZATION_ID
and rownum=1;
EXCEPTION
WHEN OTHERS THEN
NULL;
--v_desc:=NULL;
END;
-- BEGIN
--
-- v_organization_id:=NULL;
-- IF i.organization_id=151 THEN
-- v_organization_id:=712;
-- END IF;
---- IF i.organization_id=103 THEN
---- v_organization_id:=NULL;
---- END IF;
--
-- EXCEPTION
-- WHEN OTHERS THEN
-- NULL;
-- v_organization_id:=NULL;
--
-- END;
--
--
-- ====================================
-- Inventory COST_OF_SALES_ACC validation..
-- ====================================
BEGIN
--v_organization_id := NULL;
dbms_output.put_line('enter');
cost_of_sales_account :=i.COST_OF_SALES_ACC;
dbms_output.put_line('table:Code Combination id -- '||cost_of_sales_account);
SELECT code_combination_id
INTO cost_of_sales_account
FROM gl_code_combinations
WHERE segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.'|| segment5 = TRIM (i.COST_OF_SALES_ACC)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND chart_of_accounts_id = 50445
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('enter exception');
x_code_combination := i.cost_of_sales_acc||'.'||'000'||'.'||'000';
airtel_create_ccid_item(x_code_combination,l_error_msg1,l_error_msg2,l_ccid);
cost_of_sales_account := l_ccid;
dbms_output.put_line('yes:Code Combination id -- '||cost_of_sales_account);
dbms_output.put_line('error1:Code Combination id -- '||l_error_msg1);
dbms_output.put_line('errore2:Code Combination id -- '||l_error_msg2);
/* Jngugi v_err_flag := 'E';
v_err_msg := v_err_msg|| ','
|| 'Invalid COST_OF_SALES_ACCOUNT for the item -- ' || i.cost_of_sales_acc;
dbms_output.put_line('Invalid Organization Code for the item -- '||l_ccid);
--fnd_file.put_line (fnd_file.LOG, SQLCODE || '' || SQLERRM);
--fnd_file.put_line
-- (fnd_file.LOG,
-- 'Invalid COST_OF_SALES_ACCOUNT for the item -- '|| i.COST_OF_SALES_ACC);*/
END;
-- ====================================
-- Inventory EXPENSE_ACCOUNT validation..
-- ====================================
BEGIN
--v_organization_id := NULL;
expense_account :=i.EXPENSE_ACC;
dbms_output.put_line('table:Code Combination id -- '||expense_account);
SELECT code_combination_id
INTO expense_account
FROM gl_code_combinations
WHERE segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.'|| segment5 = TRIM (i.EXPENSE_ACC)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND chart_of_accounts_id = 50445
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('enter exception for exp acc');
x_code_combination := i.EXPENSE_ACC||'.'||'000'||'.'||'000';
airtel_create_ccid_item(x_code_combination,l_error_msg1,l_error_msg2,l_ccid);
expense_account := l_ccid;
dbms_output.put_line('yes:Code Combination id -- '||expense_account);
dbms_output.put_line('error1:Code Combination id -- '||l_error_msg1);
dbms_output.put_line('errore2:Code Combination id -- '||l_error_msg2);
/* Jngugi v_err_flag := 'E';
v_err_msg := v_err_msg|| ','
|| 'Invalid COST_OF_SALES_ACCOUNT for the item -- ' || i.cost_of_sales_acc;
dbms_output.put_line('Invalid Organization Code for the item -- '||l_ccid);
--fnd_file.put_line (fnd_file.LOG, SQLCODE || '' || SQLERRM);
--fnd_file.put_line
-- (fnd_file.LOG,
-- 'Invalid COST_OF_SALES_ACCOUNT for the item -- '|| i.COST_OF_SALES_ACC);*/
END;
-- ====================================
-- Inventory ENCUMBRANCE_ACCOUNT validation
-- ===================================
BEGIN
-- v_organization_id := NULL;
encumbrance_account :=i.ENCUMBRANCE_ACC;
dbms_output.put_line('table:Code Combination id -- '||encumbrance_account);
SELECT code_combination_id
INTO encumbrance_account
FROM gl_code_combinations
WHERE segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.'|| segment5 = TRIM (i.ENCUMBRANCE_ACC)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND chart_of_accounts_id = 50445
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('enter exception for exp acc');
x_code_combination := i.ENCUMBRANCE_ACC||'.'||'000'||'.'||'000';
airtel_create_ccid_item(x_code_combination,l_error_msg1,l_error_msg2,l_ccid);
encumbrance_account := l_ccid;
dbms_output.put_line('yes:Code Combination id -- '||encumbrance_account);
dbms_output.put_line('error1:Code Combination id -- '||l_error_msg1);
dbms_output.put_line('errore2:Code Combination id -- '||l_error_msg2);
END;
-- ====================================
-- Inventory SALES_ACCOUNT validation
-- ===================================
BEGIN
sales_account :=i.sales_acc;
dbms_output.put_line('table:Code Combination id -- '||sales_account);
SELECT code_combination_id
INTO sales_account
FROM gl_code_combinations
WHERE segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.'|| segment5 = TRIM (i.sales_acc)
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND chart_of_accounts_id = 50445
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('enter exception for exp acc');
x_code_combination := i.sales_acc||'.'||'000'||'.'||'000';
airtel_create_ccid_item(x_code_combination,l_error_msg1,l_error_msg2,l_ccid);
sales_account := l_ccid;
dbms_output.put_line('yes:Code Combination id -- '||sales_account);
dbms_output.put_line('error1:Code Combination id -- '||l_error_msg1);
dbms_output.put_line('errore2:Code Combination id -- '||l_error_msg2);
/* v_err_flag := 'E';
v_err_msg :=
v_err_msg
|| ','
|| 'Invalid ENCUMBRANCE_ACCOUNT for the item -- '
|| i.sales_account;
-- dbms_output.put_line('Invalid Organization Code for the item -- '||i.item_segment1);
fnd_file.put_line (fnd_file.LOG, SQLCODE || '' || SQLERRM);
fnd_file.put_line
(fnd_file.LOG,
'Invalid ENCUMBRANCE_ACCOUNT for the item -- '
|| i.sales_account
);*/
END;
---- ====================================
---- Validation for the Template name
---- ===================================
---- BEGIN
---- v_template_name:=null;
---- SELECT template_name
---- INTO v_template_name
---- FROM mtl_item_templates
---- WHERE UPPER(template_name) = TRIM(UPPER(i.template_name));
---- EXCEPTION
---- WHEN OTHERS THEN
---- v_err_flag := 'E';
---- v_err_msg := v_err_msg||','||'Invalid Template name for the item -- '||i.item_segment1;
---- -- dbms_output.put_line('Invalid Template name for the item');
---- FND_FILE.PUT_LINE(FND_FILE.LOG, sqlcode||''|| sqlerrm);
----
----FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invalid Template name for the item'||i.template_name);
---- END;
----
----
---- ========================================
---- Validation for primary unit of measure
---- ========================================
--
-- BEGIN
-- v_uom_code:=null;
-- SELECT uom_code
-- INTO v_uom_code
-- FROM mtl_units_of_measure
-- WHERE UPPER(uom_code) = TRIM(UPPER(i.primary_uom_code));
-- EXCEPTION
-- WHEN OTHERS THEN
-- v_err_flag := 'E';
-- v_err_msg := v_err_msg||','||'Invalid Primary Unit of Measure for the item -- '||i.segment1;
-- -- dbms_output.put_line('Invalid Primary Unit of Measure for the item');
-- FND_FILE.PUT_LINE(FND_FILE.LOG, sqlcode||''|| sqlerrm);
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invalid Primary Unit of Measure for the item'||i.segment1);
-- END;
--
--
---- ========================================
---- Validation for Secondary unit of measure
---- ========================================
-- IF i.secondary_uom_code IS NOT NULL
-- THEN
-- BEGIN
-- v_s_uom_code:=null;
-- SELECT uom_code
-- INTO v_s_uom_code
-- FROM mtl_units_of_measure
-- WHERE UPPER (uom_code) =
-- TRIM (UPPER (i.secondary_uom_code));
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- v_err_flag := 'E';
-- v_err_msg :=
-- v_err_msg
-- || ','
-- || 'Invalid Secondary Unit of Measure for the item -- '
-- || i.segment1;
-- -- DBMS_OUTPUT.put_line ('Invalid Secondary Unit of Measure for the item');
-- FND_FILE.PUT_LINE(FND_FILE.LOG, sqlcode||''|| sqlerrm);
--FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invalid Secondary Unit of Measure for the item'||i.segment1);
-- END;
-- END IF;
--
-- ========================================
-- Validation for Category set name
-- ========================================
-- BEGIN
-- v_category_set_id:=null;
-- SELECT category_set_id
-- INTO v_category_set_id
-- FROM mtl_category_sets
-- WHERE UPPER(category_set_name) = TRIM(UPPER(i.category_set_name));
-- EXCEPTION
-- WHEN OTHERS THEN
-- v_err_flag := 'E';
-- v_err_msg := v_err_msg||','||'Invalid Category Set Name for the item -- '||i.item_segment1;
-- -- dbms_output.put_line('Invalid Category Set Name for the item');
-- FND_FILE.PUT_LINE(FND_FILE.LOG, sqlcode||''|| sqlerrm);
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invalid Category Set Name for the item'||i.item_segment1);
-- END;
--
-- dbms_output.put_line( 'Item Category Set ID is '||v_category_set_id);
-- ========================================
-- Validation for Category name
-- ========================================
-- BEGIN
--v_category_id :=null;
-- SELECT category_id
-- INTO v_category_id
-- FROM mtl_categories
-- WHERE UPPER(segment1) = TRIM(UPPER(i.CAT_SEGMENT1))
-- and UPPER(segment2) = TRIM(UPPER(i.CAT_SEGMENT2));
-- EXCEPTION
-- WHEN OTHERS THEN
-- v_err_flag := 'E';
-- v_err_msg := v_err_msg||','||'Invalid Category Name for the item -- '||i.item_segment1;
-- -- dbms_output.put_line('Invalid Category Name for the item');
-- FND_FILE.PUT_LINE(FND_FILE.LOG, sqlcode||''|| sqlerrm);
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invalid Category Name for the item'||i.item_segment1);
-- END;
--
fnd_file.put_line (fnd_file.LOG, 'Insertion Starts');
IF v_err_flag <> 'E'
THEN
INSERT INTO mtl_system_items_interface
(list_price_per_unit,acceptable_early_days,
acceptable_rate_decrease,
acceptable_rate_increase, accounting_rule_id,
allow_express_delivery_flag,
allow_item_desc_update_flag,
allow_substitute_receipts_flag,
allow_unordered_receipts_flag,
allowed_units_lookup_code, asn_autoexpire_flag,
asset_category_id, asset_creation_code,
ato_forecast_control, atp_components_flag,
atp_flag, atp_rule_id, attribute_category,
attribute1, attribute10, attribute11,
attribute12, attribute13, attribute14,
attribute15, attribute16, attribute17,
attribute18, attribute19, attribute2,
attribute20, attribute21, attribute22,
attribute23, attribute24, attribute25,
attribute26, attribute27, attribute28,
attribute29, attribute3, attribute30,
attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9,
auto_created_config_flag,
auto_lot_alpha_prefix, auto_reduce_mps,
auto_serial_alpha_prefix, back_orderable_flag,
base_item_id, base_warranty_service_id,
bom_enabled_flag, bom_item_type,
build_in_wip_flag, bulk_picked_flag,
buyer_id, carrying_cost, cas_number,
catalog_status_flag, charge_periodicity_code,
check_shortages_flag, child_lot_flag,
child_lot_prefix, child_lot_starting_number,
child_lot_validation_flag, collateral_flag,
comms_activation_reqd_flag,
comms_nl_trackable_flag, config_match,
config_model_type, consigned_flag,
container_item_flag, container_type_code,
continous_transfer, contract_item_type_code,
convergence, copy_lot_attribute_flag,
cost_of_sales_account, costing_enabled_flag,
coupon_exempt_flag, coverage_schedule_id,
create_supply_flag, critical_component_flag,
cum_manufacturing_lead_time,
cumulative_total_lead_time, current_phase_id,
customer_order_enabled_flag,
customer_order_flag, cycle_count_enabled_flag,
days_early_receipt_allowed,
days_late_receipt_allowed, days_max_inv_supply,
days_max_inv_window, days_tgt_inv_supply,
days_tgt_inv_window, default_grade,
default_include_in_rollup_flag,
default_lot_status_id,
default_serial_status_id,
default_so_source_type,
defect_tracking_on_flag,
demand_time_fence_code, demand_time_fence_days,
description, dimension_uom_code, divergence,
downloadable_flag, drp_planned_flag,
dual_uom_control, dual_uom_deviation_high,
dual_uom_deviation_low,
eam_act_notification_flag,
eam_act_shutdown_status,
eam_activity_cause_code,
eam_activity_source_code,
eam_activity_type_code, eam_item_type,
effectivity_control, electronic_flag,
enabled_flag, encumbrance_account,
end_assembly_pegging_flag, end_date_active,
enforce_ship_to_location_code, eng_item_flag,
engineering_date, engineering_ecn_code,
engineering_item_id, equipment_type,
event_flag, exclude_from_budget_flag,
expense_account, expense_billable_flag,
expiration_action_code,
expiration_action_interval,
financing_allowed_flag, fixed_days_supply,
fixed_lead_time, fixed_lot_multiplier,
fixed_order_quantity, forecast_horizon,
full_lead_time, global_attribute_category,
global_attribute1, global_attribute10,
global_attribute2, global_attribute3,
global_attribute4, global_attribute5,
global_attribute6, global_attribute7,
global_attribute8, global_attribute9,
grade_control_flag, hazard_class_id,
hazardous_material_flag, hold_days,
ib_item_instance_class, indivisible_flag,
inspection_required_flag,
internal_order_enabled_flag,
internal_order_flag, internal_volume,
inventory_asset_flag, inventory_carry_penalty,
inventory_item_flag,
inventory_item_id,
inventory_item_status_code,
inventory_planning_code,
invoice_close_tolerance, invoice_enabled_flag,
invoiceable_item_flag, invoicing_rule_id,
item_catalog_group_id, item_type,
lead_time_lot_size, lifecycle_id,
location_control_code, lot_control_code,
lot_divisible_flag, lot_merge_enabled,
lot_split_enabled, lot_status_enabled,
lot_substitution_enabled,
lot_translate_enabled, market_price,
material_billable_flag, maturity_days,
max_minmax_quantity, max_warranty_amount,
maximum_load_weight, maximum_order_quantity,
min_minmax_quantity, minimum_fill_percent,
minimum_license_quantity,
minimum_order_quantity,
model_config_clause_name,
mrp_calculate_atp_flag, mrp_planning_code,
mrp_safety_stock_code,
mrp_safety_stock_percent,
mtl_transactions_enabled_flag,
must_use_approved_vendor_flag,
negative_measurement_error, new_revision_code,
ont_pricing_qty_source,
operation_slack_penalty, order_cost,
orderable_on_web_flag, organization_id,
outside_operation_flag,
outside_operation_uom_type,
outsourced_assembly, over_return_tolerance,
over_shipment_tolerance,
overcompletion_tolerance_type,
overcompletion_tolerance_value,
overrun_percentage,
parent_child_generation_flag, payment_terms_id,
pick_components_flag, picking_rule_id,
planned_inv_point_flag, planner_code,
planning_exception_set, planning_make_buy_code,
planning_time_fence_code,
planning_time_fence_days,
positive_measurement_error,
postprocessing_lead_time, preposition_point,
preprocessing_lead_time,
preventive_maintenance_flag,
price_tolerance_percent, primary_specialist_id,
primary_unit_of_measure, primary_uom_code,
process_costing_enabled_flag,
process_execution_enabled_flag,
process_quality_enabled_flag,
process_supply_locator_id,
process_supply_subinventory,
process_yield_locator_id,
process_yield_subinventory,
product_family_item_id, program_application_id,
program_id, program_update_date,
prorate_service_flag, purchasing_enabled_flag,
purchasing_item_flag, purchasing_tax_code,
qty_rcv_exception_code, qty_rcv_tolerance,
receipt_days_exception_code,
receipt_required_flag, receive_close_tolerance,
receiving_routing_id, recipe_enabled_flag,
recovered_part_disp_code,
release_time_fence_code,
release_time_fence_days, repair_leadtime,
repair_program, repair_yield,
repetitive_planning_flag,
replenish_to_order_flag, request_id,
reservable_type, response_time_period_code,
response_time_value, restrict_locators_code,
restrict_subinventories_code, retest_interval,
return_inspection_requirement, returnable_flag,
revision_qty_control_code, rfq_required_flag,
rounding_control_type, rounding_factor,
safety_stock_bucket_days, sales_account,
secondary_default_ind, secondary_specialist_id,
secondary_uom_code, segment1, segment10,
segment11, segment12, segment13, segment14,
segment15, segment16, segment17, segment18,
segment19, segment2, segment20, segment3,
segment4, segment5, segment6, segment7,
segment8, segment9,
serial_number_control_code,
serial_status_enabled,
serv_billing_enabled_flag,
serv_importance_level, serv_req_enabled_code,
service_duration, service_duration_period_code,
service_item_flag, service_starting_delay,
serviceable_component_flag,
serviceable_item_class_id,
serviceable_product_flag, shelf_life_code,
shelf_life_days, ship_model_complete_flag,
shippable_item_flag, shrinkage_rate,
so_authorization_flag, so_transactions_flag,
source_subinventory, source_type,
start_auto_lot_number,
start_auto_serial_number, start_date_active,
std_lot_size, stock_enabled_flag,
subcontracting_component,
subscription_depend_flag,
substitution_window_code,
substitution_window_days, summary_flag,
tax_code, taxable_flag, time_billable_flag,
tracking_quantity_ind, un_number_id,
under_return_tolerance,
under_shipment_tolerance, unit_height,
unit_length, unit_of_issue, unit_volume,
unit_weight, unit_width, usage_item_flag,
variable_lead_time, vehicle_item_flag,
vendor_warranty_flag, vmi_fixed_order_quantity,
vmi_forecast_type, vmi_maximum_days,
vmi_maximum_units, vmi_minimum_days,
vmi_minimum_units, vol_discount_exempt_flag,
volume_uom_code, warranty_vendor_id,
web_status, weight_uom_code, wh_update_date,
wip_supply_locator_id, wip_supply_subinventory,
wip_supply_type, process_flag, transaction_type,
set_process_id, creation_date, created_by,
last_update_date, last_updated_by
)
VALUES (--i.LIST_PRICE_PER_UNIT,
i.LIST_PRICE_PER_UNIT,
i.acceptable_early_days,
i.acceptable_rate_decrease,
i.acceptable_rate_increase, i.accounting_rule_id,
i.allow_express_delivery_flag,
i.allow_item_desc_update_flag,
i.allow_substitute_receipts_flag,
i.allow_unordered_receipts_flag,
i.allowed_units_lookup_code, i.asn_autoexpire_flag,
i.asset_category_id, i.asset_creation_code,
i.ato_forecast_control, i.atp_components_flag,
i.atp_flag, i.atp_rule_id, i.attribute_category,
i.attribute1, i.attribute10, i.attribute11,
i.attribute12, i.attribute13, i.attribute14,
i.attribute15, i.attribute16, i.attribute17,
i.attribute18, i.attribute19, i.attribute2,
i.attribute20, i.attribute21, i.attribute22,
i.attribute23, i.attribute24, i.attribute25,
i.attribute26, i.attribute27, i.attribute28,
i.attribute29, i.attribute3, i.attribute30,
i.attribute4, i.attribute5, i.attribute6,
i.attribute7, i.attribute8, i.attribute9,
i.auto_created_config_flag,
i.auto_lot_alpha_prefix, i.auto_reduce_mps,
i.auto_serial_alpha_prefix, i.back_orderable_flag,
i.base_item_id, i.base_warranty_service_id,
i.bom_enabled_flag, i.bom_item_type,
i.build_in_wip_flag, i.bulk_picked_flag,
i.buyer_id, i.carrying_cost, i.cas_number,
i.catalog_status_flag, i.charge_periodicity_code,
i.check_shortages_flag, i.child_lot_flag,
i.child_lot_prefix, i.child_lot_starting_number,
i.child_lot_validation_flag, i.collateral_flag,
i.comms_activation_reqd_flag,
i.comms_nl_trackable_flag, i.config_match,
i.config_model_type, i.consigned_flag,
i.container_item_flag, i.container_type_code,
i.continous_transfer, i.contract_item_type_code,
i.convergence, i.copy_lot_attribute_flag,
cost_of_sales_account, i.costing_enabled_flag,
i.coupon_exempt_flag, i.coverage_schedule_id,
i.create_supply_flag,i.critical_component_flag,
i.cum_manufacturing_lead_time,
i.cumulative_total_lead_time, i.current_phase_id,
i.customer_order_enabled_flag,
i.customer_order_flag, i.cycle_count_enabled_flag,
i.days_early_receipt_allowed,
i.days_late_receipt_allowed, i.days_max_inv_supply,
i.days_max_inv_window, i.days_tgt_inv_supply,
i.days_tgt_inv_window, i.default_grade,
i.default_include_in_rollup_flag,
i.default_lot_status_id,
i.default_serial_status_id,
i.default_so_source_type,
i.defect_tracking_on_flag,
i.demand_time_fence_code, i.demand_time_fence_days,
v_desc, i.dimension_uom_code, i.divergence,
i.downloadable_flag, i.drp_planned_flag,
i.dual_uom_control, i.dual_uom_deviation_high,
i.dual_uom_deviation_low,
i.eam_act_notification_flag,
i.eam_act_shutdown_status,
i.eam_activity_cause_code,
i.eam_activity_source_code,
i.eam_activity_type_code, i.eam_item_type,
i.effectivity_control, i.electronic_flag,
i.enabled_flag, encumbrance_account,
i.end_assembly_pegging_flag, i.end_date_active,
i.enforce_ship_to_location_code, i.eng_item_flag,
i.engineering_date, i.engineering_ecn_code,
i.engineering_item_id, i.equipment_type,
i.event_flag, i.exclude_from_budget_flag,
expense_account, i.expense_billable_flag,
i.expiration_action_code,
i.expiration_action_interval,
i.financing_allowed_flag, i.fixed_days_supply,
i.fixed_lead_time, i.fixed_lot_multiplier,
i.fixed_order_quantity, i.forecast_horizon,
i.full_lead_time, i.global_attribute_category,
i.global_attribute1, i.global_attribute10,
i.global_attribute2, i.global_attribute3,
i.global_attribute4, i.global_attribute5,
i.global_attribute6, i.global_attribute7,
i.global_attribute8, i.global_attribute9,
i.grade_control_flag, i.hazard_class_id,
i.hazardous_material_flag, i.hold_days,
i.ib_item_instance_class, i.indivisible_flag,
i.inspection_required_flag,
i.internal_order_enabled_flag,
i.internal_order_flag, i.internal_volume,
i.inventory_asset_flag, i.inventory_carry_penalty,
i.inventory_item_flag,
V_INVENTORY_ITEM_ID,
i.inventory_item_status_code,
i.inventory_planning_code,
i.invoice_close_tolerance, i.invoice_enabled_flag,
i.invoiceable_item_flag, i.invoicing_rule_id,
i.item_catalog_group_id, i.item_type,
i.lead_time_lot_size,i.lifecycle_id,
i.location_control_code, i.lot_control_code,
i.lot_divisible_flag, i.lot_merge_enabled,
i.lot_split_enabled, i.lot_status_enabled,
i.lot_substitution_enabled,
i.lot_translate_enabled, i.market_price,
i.material_billable_flag, i.maturity_days,
i.max_minmax_quantity, i.max_warranty_amount,
i.maximum_load_weight, i.maximum_order_quantity,
i.min_minmax_quantity, i.minimum_fill_percent,
i.minimum_license_quantity,
i.minimum_order_quantity,
i.model_config_clause_name,
i.mrp_calculate_atp_flag, i.mrp_planning_code,
i.mrp_safety_stock_code,
i.mrp_safety_stock_percent,
i.mtl_transactions_enabled_flag,
i.must_use_approved_vendor_flag,
i.negative_measurement_error, i.new_revision_code,
i.ont_pricing_qty_source,
i.operation_slack_penalty, i.order_cost,
i.orderable_on_web_flag,
V_ORGANIZATION_ID,--708,--Child_org
i.outside_operation_flag,
i.outside_operation_uom_type,
i.outsourced_assembly, i.over_return_tolerance,
i.over_shipment_tolerance,
i.overcompletion_tolerance_type,
i.overcompletion_tolerance_value,
i.overrun_percentage,
i.parent_child_generation_flag, i.payment_terms_id,
i.pick_components_flag, i.picking_rule_id,
i.planned_inv_point_flag, i.planner_code,
i.planning_exception_set, i.planning_make_buy_code,
i.planning_time_fence_code,
i.planning_time_fence_days,
i.positive_measurement_error,
i.postprocessing_lead_time, i.preposition_point,
i.preprocessing_lead_time,
i.preventive_maintenance_flag,
i.price_tolerance_percent, i.primary_specialist_id,
i.primary_unit_of_measure, i.primary_uom_code,
i.process_costing_enabled_flag,
i.process_execution_enabled_flag,
i.process_quality_enabled_flag,
i.process_supply_locator_id,
i.process_supply_subinventory,
i.process_yield_locator_id,
i.process_yield_subinventory,
i.product_family_item_id, i.program_application_id,
i.program_id, i.program_update_date,
i.prorate_service_flag, i.purchasing_enabled_flag,
i.purchasing_item_flag, i.purchasing_tax_code,
i.qty_rcv_exception_code, i.qty_rcv_tolerance,
i.receipt_days_exception_code,
i.receipt_required_flag, i.receive_close_tolerance,
i.receiving_routing_id, i.recipe_enabled_flag,
i.recovered_part_disp_code,
i.release_time_fence_code,
i.release_time_fence_days, i.repair_leadtime,
i.repair_program, i.repair_yield,
i.repetitive_planning_flag,
i.replenish_to_order_flag, i.request_id,
i.reservable_type, i.response_time_period_code,
i.response_time_value, i.restrict_locators_code,
i.restrict_subinventories_code, i.retest_interval,
i.return_inspection_requirement, i.returnable_flag,
i.revision_qty_control_code, i.rfq_required_flag,
i.rounding_control_type, i.rounding_factor,
i.safety_stock_bucket_days, sales_account,
i.secondary_default_ind, i.secondary_specialist_id,
i.secondary_uom_code, i.segment1, i.segment10,
i.segment11, i.segment12, i.segment13, i.segment14,
i.segment15, i.segment16, i.segment17, i.segment18,
i.segment19, i.segment2, i.segment20, i.segment3,
i.segment4, i.segment5, i.segment6, i.segment7,
i.segment8, i.segment9,
i.serial_number_control_code,
i.serial_status_enabled,
i.serv_billing_enabled_flag,
i.serv_importance_level, i.serv_req_enabled_code,
i.service_duration, i.service_duration_period_code,
i.service_item_flag, i.service_starting_delay,
i.serviceable_component_flag,
i.serviceable_item_class_id,
i.serviceable_product_flag, i.shelf_life_code,
i.shelf_life_days, i.ship_model_complete_flag,
i.shippable_item_flag, i.shrinkage_rate,
i.so_authorization_flag, i.so_transactions_flag,
i.source_subinventory, i.source_type,
i.start_auto_lot_number,
i.start_auto_serial_number, i.start_date_active,
i.std_lot_size, i.stock_enabled_flag,
i.subcontracting_component,
i.subscription_depend_flag,
i.substitution_window_code,
i.substitution_window_days, i.summary_flag,
i.tax_code, i.taxable_flag, i.time_billable_flag,
i.tracking_quantity_ind, i.un_number_id,
i.under_return_tolerance,
i.under_shipment_tolerance, i.unit_height,
i.unit_length, i.unit_of_issue, i.unit_volume,
i.unit_weight, i.unit_width, i.usage_item_flag,
i.variable_lead_time, i.vehicle_item_flag,
i.vendor_warranty_flag, i.vmi_fixed_order_quantity,
i.vmi_forecast_type, i.vmi_maximum_days,
i.vmi_maximum_units, i.vmi_minimum_days,
i.vmi_minimum_units, i.vol_discount_exempt_flag,
i.volume_uom_code, i.warranty_vendor_id,
i.web_status, i.weight_uom_code, i.wh_update_date,
i.wip_supply_locator_id, i.wip_supply_subinventory,
i.wip_supply_type,
1, -- Process flag
'CREATE', -- Transaction type
1, -- Set process id
SYSDATE, -- Creation date
1110, --fnd_profile.value('user_id'),
SYSDATE, 1110
);
COMMIT;
UPDATE xx_item_import_stg
SET status_flag = 'P'
WHERE ROWID = i.ROWID;
v_rec_cnt := v_rec_cnt + 1;
fnd_file.put_line (fnd_file.LOG, v_rec_cnt);
ELSE
UPDATE xx_item_import_stg
SET status_flag = 'E',
error_messege = v_err_msg
WHERE ROWID = i.ROWID;
fnd_file.put_line (fnd_file.LOG, v_err_msg);
-- Inserting errors into log file.
COMMIT;
END IF;
END LOOP; -- Loop for validation
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
' Error occured in the item validation process'
);
fnd_file.put_line (fnd_file.LOG,
v_err_msg || ' ' || SQLCODE || '' || SQLERRM
);
END; -- Main block Validation
END xx_item_import_pkg;
/
No comments:
Post a Comment