item conversion

CREATE OR REPLACE PACKAGE xxcofi_item_con_ext_pkg AUTHID CURRENT_USER
AS
/* ----------------------------------------------------------------- */
/* Program Name : XXCOFI_ITEM_EXT_PKG */
/* */
/* TYPE : PL/SQL Package Specs */
/* */
/* Input Parms : - p_number */
/* */
/* Output Parms : - */
/* */
/* */
/* AUTHOR : JPREDDY */
/* */
/* DATE : 04-JAN-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : */
/* */
/* CHANGE HISTORY */
/* ------------------------------------------------------------------*/
/* DATE AUTHOR VERSION REASON */
/* ------------------------------------------------------------------*/
/* 03-MAR-2009 JPREDDY 1.0 Initial creation */
/* 05-APR-2010 JPREDDY 1.1 UOM would be MIN for PS sku's */
/* 16-APR-2010 JPREDDY 1.2 Added 4 new fields. */
/* 1) pickupallowed_flag */
/* 2) deliveryallowed_flag */
/* 3) return_flag */
/* 4) infiniteinventory_flag */
/* 22-APR-2010 JPREDDY 1.3 Added 2 new columns */
/* 1) inventory_item_id */
/* 2) item_category_group_id */
/* 26-APR-2010 JPREDDY 1.4 ItemType issue corrected */
/**********************************************************************
DESCRIPTION:

This package contains procedures to create the Item extract file.

This procedure extract all the items information to
create the item extract file.

***********************************************************************/

g_currdate DATE;
g_start_time VARCHAR2(25);
g_end_time VARCHAR2(25);
g_user NUMBER;
g_request_id NUMBER;
g_conc_program fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE;
g_err_cnt NUMBER := 0;
g_upl_err_cnt NUMBER := 0;
g_program_name VARCHAR2(100);
g_err_cnt_item NUMBER := 0;
g_errcode NUMBER := 0;
g_curr_sequence NUMBER;
g_datetime DATE;
g_total_count_rec NUMBER := 0;
g_count_records NUMBER := 0;
--g_catalog_value CLOB;

PROCEDURE main(errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_number IN NUMBER
);

PROCEDURE item_extract_first ( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
, p_number IN NUMBER);

PROCEDURE generate_output;

PROCEDURE write(p_type IN VARCHAR2
, p_message IN VARCHAR2);

END xxcofi_item_con_ext_pkg;
/

CREATE OR REPLACE PACKAGE BODY apps.xxcofi_item_con_ext_pkg
IS
/* ----------------------------------------------------------------- */
/* Program Name : XXCOFI_ITEM_EXT_PKG */
/* */
/* TYPE : PL/SQL Package Specs */
/* */
/* Input Parms : - p_number */
/* */
/* Output Parms : - */
/* */
/* */
/* AUTHOR : JPREDDY */
/* */
/* DATE : 04-JAN-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : */
/* */
/* CHANGE HISTORY */
/* ------------------------------------------------------------------*/
/* DATE AUTHOR VERSION REASON */
/* ------------------------------------------------------------------*/
/* 03-MAR-2009 JPREDDY 1.0 Initial creation */
/* 05-APR-2010 JPREDDY 1.1 UOM would be MIN for PS sku's */
/* 16-APR-2010 JPREDDY 1.2 Added 4 new fields. */
/* 1) pickupallowed_flag */
/* 2) deliveryallowed_flag */
/* 3) return_flag */
/* 4) infiniteinventory_flag */
/* 22-APR-2010 JPREDDY 1.3 Added 2 new columns */
/* 1) inventory_item_id */
/* 2) item_category_group_id */
/* 26-APR-2010 JPREDDY 1.4 ItemType issue corrected */
/**********************************************************************
DESCRIPTION:

This package contains procedures to create the Item extract file to Message broker.

The Message Broker using the translation rules will transform the data
into XML strings and pass them to Sterling.

DESCRIPTION:

This package contains procedures to create the Item extract file.

This procedure extract all the items information to
create the item extract file. This populates all the valid Records
in the custom table (xxcofi_replacement_item_ext) with file_seq_num = 1
***********************************************************************/

PROCEDURE main (
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_number IN NUMBER
)
IS
/*****************************************************************************
PURPOSE: This is the main procedure which calls other
procedures for extracting the data and then generating output.
*****************************************************************************/
l_file_directory VARCHAR2(100);
l_file_name vARCHAR2(30);
BEGIN

g_currdate := sysdate;
g_request_id := fnd_global.conc_request_id;

BEGIN
/* Gets current program name from base table(fnd_concurrent_programs_vl)
for use in procedures for generating output - generate_output */

SELECT user_concurrent_program_name
INTO g_program_name
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_id = fnd_global.conc_program_id;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
write('L','NO DATA FOUND while fetching concurrent program name ');
WHEN OTHERS
THEN
write('L','Error while fetching concurrent program name ' || SQLCODE || SQLERRM);
write('L','Message01');
END;

BEGIN

SELECT fnd_profile.value('XXCOFIDATA_OUT')
INTO l_file_directory
FROM DUAL;

l_file_name := 'Items';

write('L',' Path with File Name : '||l_file_directory||'/'||l_file_name||'.'|| 'dat');

EXCEPTION
WHEN NO_DATA_FOUND
THEN
write('L','NO DATA FOUND while fetching Outbound Path');
WHEN OTHERS
THEN
write('L','Error while fetching concurrent program name ' || SQLCODE || SQLERRM);
write('L','Message02');
END;

-- First Time Process procedure.

item_extract_first(l_file_name, l_file_directory, p_number);

generate_output;

END main;

FUNCTION get_upc (p_sku IN VARCHAR2) RETURN VARCHAR2
IS
/******************************************************************/
/* Description: This Function will return UPC Number of the SKU. */
/* IF No value is fetched then it would return the NULL Value. */
/******************************************************************/
l_l_upc VARCHAR2(30);
BEGIN
SELECT xpuc.upc_number INTO l_l_upc
FROM xxcofi_po_upc_codes xpuc
, mtl_system_items_b msi
WHERE msi.inventory_item_id = xpuc.inventory_item_id
AND msi.segment1 = p_sku
AND organization_id = 22
AND rownum =1;
RETURN l_l_upc;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_l_upc := NULL;
RETURN l_l_upc;
WHEN OTHERS THEN
l_l_upc := NULL;
RETURN l_l_upc;
END get_upc;

FUNCTION get_image (p_sku IN VARCHAR2) RETURN VARCHAR2
IS
/**********************************************************************/
/* Description: This function will return Image path (with file name) */
/* based on the SKU. It will return only LARGE image path. */
/**********************************************************************/
l_l_file_name VARCHAR2(100);
l_l_sku VARCHAR2(10);
--l_s VARCHAR2(1000);
BEGIN
l_l_sku := p_sku;

SELECT SUBSTR(ibdav.file_name,INSTR(ibdav.file_name,'/',-1)) INTO l_l_file_name
FROM ibe_dsp_attachments_v ibdav
WHERE ibdav.language_code = 'US'
AND ibdav.file_Name like '/XXCOFIimages%'
AND UPPER(ibdav.access_name) like '%LARGE%'
AND trim(ibdav.access_name) like l_l_sku||'%'
AND rownum =1;

l_l_file_name := '/MCF_IMAGES/MCF_DEV_IMAGES'||l_l_file_name;

RETURN l_l_file_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_l_file_name := '/MCF_IMAGES/MCF_DEV_IMAGES/image_not_available_large_en.jpg';
-- DBMS_OUTPUT.PUT_LINE('Phani');
RETURN l_l_file_name;
WHEN OTHERS THEN
l_l_file_name := '/MCF_IMAGES/MCF_DEV_IMAGES/image_not_available_large_en.jpg';
-- DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
RETURN l_l_file_name;
END get_image;

FUNCTION get_model_style_info (p_sku IN VARCHAR2) RETURN VARCHAR2
IS
/********************************************************************/
/* Description: This Function will return Model SKU */
/*********************************************************************/
l_model_value VARCHAR2(30);
BEGIN

SELECT SUBSTR(v.element_value,1,30) element_value_us INTO l_model_value
FROM mtl_descr_element_values v
, mtl_system_items_b msi
WHERE msi.organization_id = 22
AND msi.segment1 = p_sku
AND msi.inventory_item_id = v.inventory_item_id
AND v.element_name = 'Model/Style Number'
ORDER BY v.element_sequence;

RETURN l_model_value;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_model_value := NULL;
RETURN l_model_value;
WHEN OTHERS THEN
l_model_value := NULL;
RETURN l_model_value;
END get_model_style_info;

FUNCTION get_vendor_info (p_sku IN VARCHAR2) RETURN VARCHAR2
IS
/********************************************************************/
/* Description: This Function will return vendor SKU */
/*********************************************************************/
l_vendor_value VARCHAR2(50);
BEGIN

SELECT SUBSTR(v.element_value,1,50) element_value_us INTO l_vendor_value
FROM mtl_descr_element_values v
, mtl_system_items_b msi
WHERE msi.organization_id = 22
AND msi.segment1 = p_sku
AND msi.inventory_item_id = v.inventory_item_id
AND v.element_name = 'Brand/Vendor'
ORDER BY v.element_sequence;

RETURN l_vendor_value;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_vendor_value := NULL;
RETURN l_vendor_value;
WHEN OTHERS THEN
l_vendor_value := NULL;
RETURN l_vendor_value;
END get_vendor_info;

FUNCTION get_right_ind (p_sku IN VARCHAR2) RETURN VARCHAR2
IS
/********************************************************************/
/* Description: This Function will return wether refrigerator SKU */
/* is RIght-hand or left-handed. */
/*********************************************************************/
l_l_lh_rh_indicator VARCHAR2(1);
BEGIN
SELECT bbom.attribute1 INTO l_l_lh_rh_indicator
--, msi.SEGMENT1
FROM bom_bill_of_materials bbom, mtl_system_items_b msi
WHERE bbom.assembly_item_id=msi.inventory_item_id
AND bbom.organization_id=22
AND msi.organization_id=22
AND msi.item_catalog_group_id = 52
AND msi.segment1 = p_sku
AND rownum =1;

IF(l_l_lh_rh_indicator = 'Y')
THEN
l_l_lh_rh_indicator := 'R';
ELSE
l_l_lh_rh_indicator := 'L';
END IF;

RETURN l_l_lh_rh_indicator;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_l_lh_rh_indicator := NULL;
RETURN l_l_lh_rh_indicator;
WHEN OTHERS THEN
l_l_lh_rh_indicator := NULL;
RETURN l_l_lh_rh_indicator;
END get_right_ind;

FUNCTION get_catalog_name (p_catalog_id IN VARCHAR2) RETURN VARCHAR2
IS
/********************************************************************/
/* Description: This Function will return catalog name with ID value */
/* */
/**********************************************************************/
l_catalog_id NUMBER;
l_catalog_name VARCHAR2(30);

BEGIN
l_catalog_id := p_catalog_id;

select segment1 INTO l_catalog_name
from mtl_item_catalog_groups
where item_catalog_group_id = l_catalog_id;

l_catalog_name := SUBSTR(l_catalog_name,1,26);

RETURN l_catalog_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_catalog_name := NULL;
RETURN l_catalog_name;
WHEN OTHERS THEN
l_catalog_name := NULL;
RETURN l_catalog_name;
END get_catalog_name;


FUNCTION get_catalog_count (p_sku IN VARCHAR2) RETURN NUMBER
IS
/********************************************************************/
/* Description: This Function will return Count of the number */
/* of elements against the item SKU */
/**********************************************************************/
l_l_group_count NUMBER;
l_l_sku VARCHAR2(10);
--l_s VARCHAR2(1000);
BEGIN
l_l_sku := p_sku;
SELECT count(v.element_sequence) INTO l_l_group_count
--, v.element_name, v.element_value
FROM mtl_descr_element_values_tl vt
, mtl_descr_element_values v
, mtl_system_items_b msi
WHERE msi.inventory_item_id = vt.inventory_item_id
AND vt.language = 'US'
AND msi.organization_id = 22
AND msi.segment1 = l_l_sku
AND v.inventory_item_id = vt.inventory_item_id
AND v.element_name = vt.element_name;
RETURN l_l_group_count;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_l_group_count := NULL;
RETURN l_l_group_count;
WHEN OTHERS THEN
l_l_group_count := NULL;
RETURN l_l_group_count;
END get_catalog_count;

FUNCTION get_lead_time (p_sku IN VARCHAR2) RETURN NUMBER
IS
/**********************************************************************/
/* Description: This Function will return Min Lead time */
/* Acorss all the Organization and -1 */
/**********************************************************************/
l_l_lead_time NUMBER;
l_l_sku VARCHAR2(10);
--l_s VARCHAR2(1000);
BEGIN
l_l_sku := p_sku;

select NVL(DECODE(MIN(msi.full_lead_time),0,2)-1,1) INTO l_l_lead_time
FROM mtl_system_items_b msi
WHERE segment1 = l_l_sku;
RETURN l_l_lead_time;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_l_lead_time := 1;
RETURN l_l_lead_time;
END get_lead_time;

PROCEDURE created_data_file ( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
, p_file_seq_num IN NUMBER
)
IS
/**********************************************************************/
/* Description: This procedure would create the data file */
/* Query all the data from the custom table. */
/**********************************************************************/
-- Variables

v_file_handle UTL_FILE.FILE_TYPE;
v_file_directory VARCHAR2(100) := p_file_directory;

l_report_name VARCHAR2(70) := p_file_name;
l_buff_size NUMBER := 32000;

-- Other temporary Variable Initializations

v_rec_count NUMBER :=0;
l_file_seq_num NUMBER := p_file_seq_num;
--l_date VARCHAR2(30);

-- Cursor to fetch the records from the Custom table.

CURSOR inc_info_cur
IS
SELECT NVL(xie.orgcode,' ') orgcode
,NVL(xie.itemtype1,' ') itemtype
-- Based on Item Type (if PROD then other flags values are populated). We need to make the change there as well.
-- ,DECODE(xie.itemtype, '3rd Party Installation', 'PS'
-- , 'Accessory / Spare Part','PROD'
-- , 'Assemble', 'PS'
-- -- Allowance
-- , 'Consignment', 'PROD'
-- , 'Custom', 'PROD'
-- -- , 'Deferred Plan Admin Fee', 'PROD'
-- -- Delivery
-- , 'Environmental Fee', 'PS'
-- , 'Expense', 'PROD'
-- , 'Hook Up', 'PS'
-- , 'Installation', 'PS'
-- , 'Loaner', 'PROD'
-- , 'Non-COFI', 'PROD'
-- , 'Labor', 'PROD'
-- , 'Non-Hbc', 'PROD'
-- -- , 'Purchased item', 'PROD'
-- , 'Repair Expense Charges', 'PS'
-- , 'Repair Labour Charges', 'PS'
-- , 'Repair Parts Charges', 'PS'
-- , 'Salvage Removal', 'DSOPT'
-- , 'Sell/Buy', 'PROD'
-- , 'Special', 'PROD'
-- , 'Standard', 'PROD'
-- , 'Suite', 'PROD'
-- , 'Usage Fee', 'PS'
-- , 'Vendor Model', 'PROD'
-- , 'Warranty', 'PROD'
-- , 'Warranty Fee', 'PROD'
-- , 'Warranty Renewal', 'PROD'
-- , 'PROD') itemtype
,NVL(xie.vendor_model,' ') vendor_model
,NVL(xie.warranty_dur,' ') warranty_dur
,NVL(xie.description,' ') description
,NVL(xie.description_frc,' ') description_frc
,NVL(replace(xie.longdesc,chr(10), ' '),' ') longdesc
,NVL(replace(xie.longdesc_frc,chr(10), ' '),' ') longdesc_frc
,NVL(xie.sku,' ') sku
,NVL(xie.upc,' ') upc
,NVL(xie.taxable,' ') taxable
,NVL(xie.exclusivity,' ') exclusivity
,NVL(xie.life_expectancy,' ') life_expectancy
,NVL(xie.rewards,' ') rewards
,NVL(xie.opp_buy,' ') opp_buy
,NVL(xie.price_profile,' ') price_profile
,NVL(xie.bv_edvp,' ') bv_edvp
,NVL(xie.brand_type,' ') brand_type
,NVL(xie.gma,' ') gma
,NVL(xie.lgroup,' ') lgroup
,NVL(xie.ldept,' ') ldept
,NVL(xie.lcategory,' ') lcategory
,NVL(xie.du,' ') du
,NVL(xie.di,' ') di
,NVL(xie.uom,' ') uom
,NVL(xie.uom_frc,' ') uom_frc
,NVL(xie.action,' ') action
,NVL(xie.wt_uom,' ') wt_uom
,NVL(TO_CHAR(xie.unit_wt),' ') unit_wt
,NVL(xie.wt_dim,' ') wt_dim
,NVL(TO_CHAR(xie.llength),' ') llength
,NVL(TO_CHAR(xie.width),' ') width
,NVL(TO_CHAR(xie.height),' ') height
,NVL(xie.item_status,' ') item_status
,NVL(xie.web_status,' ') web_status
,NVL(TO_CHAR(xie.post_pro_lt),' ') post_pro_lt
,NVL(TO_CHAR(xie.fixed_lt),' ') fixed_lt
,NVL(xie.is_bundle_sku,' ') is_bundle_sku
,NVL(xie.lh_rh_indicator,' ') lh_rh_indicator
,NVL(xie.restock_fee,' ') restock_fee
,NVL(TO_CHAR(xie.dis_cap),' ') dis_cap
,NVL(xie.crew_required,' ') crew_required
,NVL(xie.is_warrant_sku,' ') is_warrant_sku
,NVL(xie.image_path,' ') image_path
,NVL(xie.model_style,' ') model_style
,NVL(xie.bom_allowed,' ') bom_allowed
,NVL(xie.manufacturer,' ') manufacturer
,NVL(xie.pickupallowed_flag,' ') pickupallowed_flag
,NVL(xie.deliveryallowed_flag,' ') deliveryallowed_flag
,NVL(xie.return_flag,' ') return_flag
,NVL(xie.infiniteinventory_flag,' ') infiniteinventory_flag
,NVL(TO_CHAR(xie.group_count),' ') group_count
,NVL(TO_CHAR(xie.catalog_name),' ') catalog_name
-- ,xie.catalog_name catalog_name
-- ,TO_CHAR(xie.catalog_group_id) catalog_group_id
,NVL(TO_CHAR(xie.catalog_group_id),' ') catalog_group_id
-- ,NVL(dbms_lob.substr( xie.catalog_value, 4473, 1 ),' ') catalog_value
,dbms_lob.substr( xie.catalog_value, 4000, 1 ) catalog_value1
,dbms_lob.substr( xie.catalog_value, 473, 4001 ) catalog_value2
FROM xxcofi_replacement_item_ext xie
WHERE xie.file_seq_num = l_file_seq_num
AND xie.extract_flag = 'Extract Pending';

-- Variables for the Header Inform in the Out file.

-- Variables defined for the Header Info in Out file records.

col1e_desc VARCHAR2(20) := 'orgcode';
col2e_desc VARCHAR2(20) := 'itemtype';
col2e_desc_1 VARCHAR2(20) := 'vendor_model';
col2e_desc_1_1 VARCHAR2(20) := 'servicetypeid';
col2e_desc_2 VARCHAR2(20) := 'warranty_dur';
col3e_desc VARCHAR2(20) := 'description';
col4e_desc VARCHAR2(20) := 'description_frc';
col5e_desc VARCHAR2(20) := 'longdesc';
col6e_desc VARCHAR2(20) := 'longdesc_frc';
col7e_desc VARCHAR2(20) := 'sku';
col8e_desc VARCHAR2(20) := 'upc';
col9e_desc VARCHAR2(20) := 'taxable';
col10e_desc VARCHAR2(20) := 'exclusivity';
col11e_desc VARCHAR2(20) := 'life_expectancy';
col12e_desc VARCHAR2(20) := 'rewards';
col13e_desc VARCHAR2(20) := 'opp_buy';
col14e_desc VARCHAR2(20) := 'price_profile';
col15e_desc VARCHAR2(20) := 'bv_edvp';
col16e_desc VARCHAR2(20) := 'brand_type ';
col17e_desc VARCHAR2(20) := 'gma';
col18e_desc VARCHAR2(20) := 'lgroup';
col19e_desc VARCHAR2(20) := 'ldept ';
col20e_desc VARCHAR2(20) := 'lcategory ';
col21e_desc VARCHAR2(20) := 'du';
col22e_desc VARCHAR2(20) := 'di';
col23e_desc VARCHAR2(20) := 'uom';
col24e_desc VARCHAR2(20) := 'uom_frc';
col24e_desc_1 VARCHAR2(20) := 'action';
col25e_desc VARCHAR2(20) := 'wt_uom';
col26e_desc VARCHAR2(20) := 'unit_wt';
col27e_desc VARCHAR2(20) := 'wt_dim';
col28e_desc VARCHAR2(20) := 'llength';
col29e_desc VARCHAR2(20) := 'width ';
col30e_desc VARCHAR2(20) := 'height';
col31e_desc VARCHAR2(20) := 'item_status';
col32e_desc VARCHAR2(20) := 'web_status';
col33e_desc VARCHAR2(20) := 'post_pro_lt';
col34e_desc VARCHAR2(20) := 'fixed_lt';
col35e_desc VARCHAR2(20) := 'is_bundle_sku ';
col36e_desc VARCHAR2(20) := 'lh_rh_indicator';
col37e_desc VARCHAR2(20) := 'restock_fee';
col38e_desc VARCHAR2(20) := 'dis_cap';
col39e_desc VARCHAR2(20) := 'crew_required ';
col40e_desc VARCHAR2(20) := 'is_warrant_sku';
col41e_desc_1 VARCHAR2(20) := 'model_style';
col41e_desc VARCHAR2(20) := 'image_path';
col42e_desc_1 VARCHAR2(20) := 'manufacturer';
col42e_desc VARCHAR2(20) := 'bom_allowed';
col43e_desc_11 VARCHAR2(30) := 'pickupallowed_flag';
col43e_desc_12 VARCHAR2(30) := 'deliveryallowed_flag';
col43e_desc_13 VARCHAR2(30) := 'return_flag';
col43e_desc_14 VARCHAR2(30) := 'infiniteinventory_flag';
col43e_desc VARCHAR2(20) := 'group_count';
col43e_desc_1 VARCHAR2(20) := 'Catalog Name';
col44e_desc VARCHAR2(20) := 'catalog_group_id';
col45e_desc VARCHAR2(20) := 'catalog_value';

l_catalog_value CLOB;

l_servicetypeid VARCHAR2(10);
l_uom_code VARCHAR2(25);

BEGIN

--l_report_name := 'Items';
-- l_date:= TO_CHAR(SYSDATE,'YYYYMMDDHH24MI');
--v_file_handle := UTL_FILE.FOPEN (v_file_directory, v_server_name||'_'||report_name ||'_'||l_date|| '.csv','w', buff_size);

v_file_handle := UTL_FILE.FOPEN (v_file_directory, l_report_name ||'.'|| 'dat','w', l_buff_size);

BEGIN
BEGIN
-- UTL_FILE.put_line(v_file_handle, v_file_directory||'/'||l_report_name||'.'|| 'dat');

-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,v_file_directory||'/'||l_report_name||'.'|| 'dat');

-- write('L',' File Name : '||report_name ||'_'||l_date|| '.csv');

-- write('L',' File Name : '||l_report_name ||'.'|| 'dat');


FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
col1e_desc
|| ','
|| col2e_desc
|| ','
|| col2e_desc_1
|| ','
|| col2e_desc_1_1
|| ','
|| col2e_desc_2
|| ','
|| col3e_desc
|| ','
|| col4e_desc
|| ','
|| col5e_desc
|| ','
|| col6e_desc
|| ','
|| col7e_desc
|| ','
|| col8e_desc
|| ','
|| col9e_desc
|| ','
|| col10e_desc
|| ','
|| col11e_desc
|| ','
|| col12e_desc
|| ','
|| col13e_desc
|| ','
|| col14e_desc
|| ','
|| col15e_desc
|| ','
|| col16e_desc
|| ','
|| col17e_desc
|| ','
|| col18e_desc
|| ','
|| col19e_desc
|| ','
|| col20e_desc
|| ','
|| col21e_desc
|| ','
|| col22e_desc
|| ','
|| col23e_desc
|| ','
|| col24e_desc
|| ','
|| col24e_desc_1
|| ','
|| col25e_desc
|| ','
|| col26e_desc
|| ','
|| col27e_desc
|| ','
|| col28e_desc
|| ','
|| col29e_desc
|| ','
|| col30e_desc
|| ','
|| col31e_desc
|| ','
|| col32e_desc
|| ','
|| col33e_desc
|| ','
|| col34e_desc
|| ','
|| col35e_desc
|| ','
|| col36e_desc
|| ','
|| col37e_desc
|| ','
|| col38e_desc
|| ','
|| col39e_desc
|| ','
|| col40e_desc
|| ','
|| col41e_desc
|| ','
|| col41e_desc_1
|| ','
|| col42e_desc
|| ','
|| col42e_desc_1
|| ','
|| col43e_desc_11
|| ','
|| col43e_desc_12
|| ','
|| col43e_desc_13
|| ','
|| col43e_desc_14
|| ','
|| col43e_desc
|| ','
|| col43e_desc_1
|| ','
|| col44e_desc
|| ','
|| col45e_desc
);


For inc_info_rec in inc_info_cur
loop
v_rec_count := v_rec_count+1;

BEGIN

l_catalog_value := inc_info_rec.catalog_value1||inc_info_rec.catalog_value2;

l_servicetypeid := ' ';

l_uom_code := inc_info_rec.uom;

if(inc_info_rec.itemtype = 'PS') THEN
l_servicetypeid := 'INSTALL';
l_uom_code := 'MIN';
end if;

UTL_FILE.put_line(v_file_handle,
rpad(inc_info_rec.orgcode,13,' ')
||rpad(inc_info_rec.itemtype,40,' ')
||rpad(inc_info_rec.vendor_model,1,' ')
||rpad(l_servicetypeid,7,' ')
||rpad(inc_info_rec.warranty_dur,4,' ')
||rpad(inc_info_rec.description,40,' ')
||rpad(inc_info_rec.description_frc,40,' ')
||rpad(inc_info_rec.longdesc,1970,' ')
||rpad(inc_info_rec.longdesc_frc,1970,' ')
||rpad(inc_info_rec.sku,8,' ')
||rpad(inc_info_rec.upc,18,' ')
||rpad(inc_info_rec.taxable,2,' ')
||rpad(inc_info_rec.exclusivity,2,' ')
||rpad(inc_info_rec.life_expectancy,10,' ')
||rpad(inc_info_rec.rewards,2,' ')
||rpad(inc_info_rec.opp_buy,2,' ')
||rpad(inc_info_rec.price_profile,50,' ')
||rpad(inc_info_rec.bv_edvp,50,' ')
||rpad(inc_info_rec.brand_type,80,' ')
||rpad(inc_info_rec.gma,3,' ')
||rpad(inc_info_rec.lgroup,3,' ')
||rpad(inc_info_rec.ldept,3,' ')
||rpad(inc_info_rec.lcategory,3,' ')
||rpad(inc_info_rec.du,3,' ')
||rpad(inc_info_rec.di,3,' ')
||rpad(l_uom_code,25,' ')
||rpad(inc_info_rec.uom_frc,25,' ')
||rpad(inc_info_rec.action,6,' ')
||rpad(inc_info_rec.wt_uom,25,' ')
||rpad(inc_info_rec.unit_wt,20,' ')
||rpad(inc_info_rec.wt_dim,25,' ')
||rpad(inc_info_rec.llength,20,' ')
||rpad(inc_info_rec.width,20,' ')
||rpad(inc_info_rec.height,20,' ')
||rpad(inc_info_rec.item_status,10,' ')
||rpad(inc_info_rec.web_status,30,' ')
||rpad(inc_info_rec.post_pro_lt,20,' ')
||rpad(inc_info_rec.fixed_lt,20,' ')
||rpad(inc_info_rec.is_bundle_sku,2,' ')
||rpad(inc_info_rec.lh_rh_indicator,4,' ')
||rpad(inc_info_rec.restock_fee,2,' ')
||rpad(inc_info_rec.dis_cap,6,' ')
||rpad(inc_info_rec.crew_required,2,' ')
||rpad(inc_info_rec.is_warrant_sku,2,' ')
||rpad(inc_info_rec.image_path,60,' ')
||rpad(inc_info_rec.model_style,30,' ')
||rpad(inc_info_rec.bom_allowed,2,' ')
||rpad(inc_info_rec.manufacturer,50,' ')
||rpad(inc_info_rec.pickupallowed_flag,1,' ')
||rpad(inc_info_rec.deliveryallowed_flag,1,' ')
||rpad(inc_info_rec.return_flag,1,' ')
||rpad(inc_info_rec.infiniteinventory_flag,1,' ')
||rpad(inc_info_rec.group_count,2,' ')
||rpad(inc_info_rec.catalog_name,26,' ')
||rpad(inc_info_rec.catalog_group_id,3,' ')
||l_catalog_value
);


FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
inc_info_rec.orgcode
||','
||inc_info_rec.itemtype
||','
||inc_info_rec.vendor_model
||','
||l_servicetypeid
||','
||inc_info_rec.warranty_dur
||','
||REPLACE (inc_info_rec.description, ',', ' ')
||','
||REPLACE (inc_info_rec.description_frc, ',', ' ')
||','
||REPLACE (inc_info_rec.longdesc, ',', ' ')
||','
||REPLACE (inc_info_rec.longdesc_frc, ',', ' ')
||','
||inc_info_rec.sku
||','
||inc_info_rec.upc
||','
||inc_info_rec.taxable
||','
||inc_info_rec.exclusivity
||','
||REPLACE (inc_info_rec.life_expectancy, ',', ' ')
||','
||inc_info_rec.rewards
||','
||inc_info_rec.opp_buy
||','
||REPLACE (inc_info_rec.price_profile, ',', ' ')
||','
||REPLACE (inc_info_rec.bv_edvp, ',', ' ')
||','
||REPLACE (inc_info_rec.brand_type, ',', ' ')
||','
||inc_info_rec.gma
||','
||inc_info_rec.lgroup
||','
||inc_info_rec.ldept
||','
||inc_info_rec.lcategory
||','
||inc_info_rec.du
||','
||inc_info_rec.di
||','
||REPLACE (l_uom_code, ',', ' ')
||','
||REPLACE (inc_info_rec.uom_frc, ',', ' ')
||','
||inc_info_rec.action
||','
||REPLACE (inc_info_rec.wt_uom, ',', ' ')
||','
||inc_info_rec.unit_wt
||','
||inc_info_rec.wt_dim
||','
||inc_info_rec.llength
||','
||inc_info_rec.width
||','
||inc_info_rec.height
||','
||REPLACE (inc_info_rec.item_status, ',', ' ')
||','
||inc_info_rec.web_status
||','
||inc_info_rec.post_pro_lt
||','
||inc_info_rec.fixed_lt
||','
||inc_info_rec.is_bundle_sku
||','
||inc_info_rec.lh_rh_indicator
||','
||inc_info_rec.restock_fee
||','
||inc_info_rec.dis_cap
||','
||inc_info_rec.crew_required
||','
||inc_info_rec.is_warrant_sku
||','
||inc_info_rec.image_path
||','
||inc_info_rec.model_style
||','
||inc_info_rec.bom_allowed
||','
||inc_info_rec.manufacturer
||','
||inc_info_rec.pickupallowed_flag
||','
||inc_info_rec.deliveryallowed_flag
||','
||inc_info_rec.return_flag
||','
||inc_info_rec.infiniteinventory_flag
||','
||inc_info_rec.group_count
||','
||inc_info_rec.catalog_name
||','
||inc_info_rec.catalog_group_id
||','
||l_catalog_value
);



UPDATE xxcofi_replacement_item_ext
SET extract_flag= 'Extract Completed'
,extract_date= SYSDATE
WHERE file_seq_num = l_file_seq_num
AND extract_flag = 'Extract Pending'
AND sku = inc_info_rec.sku;

EXCEPTION
WHEN OTHERS
THEN

write('L','Error while Writing Data in Data file ' || SQLCODE || SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.LOG,
rpad(inc_info_rec.orgcode,13,' ')
||rpad(inc_info_rec.itemtype,40,' ')
||rpad(inc_info_rec.vendor_model,1,' ')
||rpad(l_servicetypeid,7,' ')
||rpad(inc_info_rec.warranty_dur,4,' ')
||rpad(inc_info_rec.description,40,' ')
||rpad(inc_info_rec.description_frc,40,' ')
||rpad(inc_info_rec.longdesc,1970,' ')
||rpad(inc_info_rec.longdesc_frc,1970,' ')
||rpad(inc_info_rec.sku,8,' ')
||rpad(inc_info_rec.upc,18,' ')
||rpad(inc_info_rec.taxable,2,' ')
||rpad(inc_info_rec.exclusivity,2,' ')
||rpad(inc_info_rec.life_expectancy,10,' ')
||rpad(inc_info_rec.rewards,2,' ')
||rpad(inc_info_rec.opp_buy,2,' ')
||rpad(inc_info_rec.price_profile,50,' ')
||rpad(inc_info_rec.bv_edvp,50,' ')
||rpad(inc_info_rec.brand_type,80,' ')
||rpad(inc_info_rec.gma,3,' ')
||rpad(inc_info_rec.lgroup,3,' ')
||rpad(inc_info_rec.ldept,3,' ')
||rpad(inc_info_rec.lcategory,3,' ')
||rpad(inc_info_rec.du,3,' ')
||rpad(inc_info_rec.di,3,' ')
||rpad(l_uom_code,25,' ')
||rpad(inc_info_rec.uom_frc,25,' ')
||rpad(inc_info_rec.action,6,' ')
||rpad(inc_info_rec.wt_uom,25,' ')
||rpad(inc_info_rec.unit_wt,20,' ')
||rpad(inc_info_rec.wt_dim,25,' ')
||rpad(inc_info_rec.llength,20,' ')
||rpad(inc_info_rec.width,20,' ')
||rpad(inc_info_rec.height,20,' ')
||rpad(inc_info_rec.item_status,10,' ')
||rpad(inc_info_rec.web_status,30,' ')
||rpad(inc_info_rec.post_pro_lt,20,' ')
||rpad(inc_info_rec.fixed_lt,20,' ')
||rpad(inc_info_rec.is_bundle_sku,2,' ')
||rpad(inc_info_rec.lh_rh_indicator,4,' ')
||rpad(inc_info_rec.restock_fee,2,' ')
||rpad(inc_info_rec.dis_cap,6,' ')
||rpad(inc_info_rec.crew_required,2,' ')
||rpad(inc_info_rec.is_warrant_sku,2,' ')
||rpad(inc_info_rec.image_path,60,' ')
||rpad(inc_info_rec.model_style,60,' ')
||rpad(inc_info_rec.bom_allowed,2,' ')
||rpad(inc_info_rec.manufacturer,50,' ')
||rpad(inc_info_rec.pickupallowed_flag,1,' ')
||rpad(inc_info_rec.deliveryallowed_flag,1,' ')
||rpad(inc_info_rec.return_flag,1,' ')
||rpad(inc_info_rec.infiniteinventory_flag,1,' ')
||rpad(inc_info_rec.group_count,2,' ')
||rpad(inc_info_rec.catalog_name,26,' ')
||rpad(inc_info_rec.catalog_group_id,3,' ')
||l_catalog_value
);

END;

END LOOP;

BEGIN
SELECT COUNT(1)
INTO g_total_count_rec
FROM xxcofi_replacement_item_ext
WHERE file_seq_num=1;
EXCEPTION
WHEN OTHERS
THEN
write('L','Error while fetching Total Number count of Record Extracted and Processed: ' || SQLCODE || SQLERRM);
write('L','Message03');
END;


g_count_records := v_rec_count;

EXCEPTION
WHEN OTHERS
THEN
write('L', 'inc_info_rec loop terminated due to exception '||chr(10)||
SQLCODE||' : '|| SQLERRM);
g_errcode:=2;
END;

-- UTL_FILE.put_line(v_file_handle,'FTAIL'||LPAD(NVL(TO_CHAR(v_rec_count),'0'),10,'0'));

UTL_FILE.fflush(v_file_handle);
UTL_FILE.fclose(v_file_handle);

EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - invalid path');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Mode');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Operation');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Invalid Filehandle');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

WHEN UTL_FILE.write_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Write Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

WHEN UTL_FILE.read_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Read Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Internal Error');
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'EXCEPTION RAISED - Other Error'
|| SQLCODE || SQLERRM);
UTL_FILE.fclose(v_file_handle);
g_errcode:=2;
RETURN;

END;

EXCEPTION
WHEN OTHERS
THEN
write('L', 'Program oh_extract terminated due to error '||chr(10)
|| SQLCODE|| ' : '|| SQLERRM);
g_errcode:=2;
RETURN;

END created_data_file;


PROCEDURE get_category_info
( p_lookup_code IN VARCHAR2
,p_sku IN VARCHAR2
,p_exclusivity OUT VARCHAR2
,p_life_expectancy OUT VARCHAR2
,p_rewards OUT VARCHAR2
,p_opp_buy OUT VARCHAR2
,p_price_profile OUT VARCHAR2
,p_bv_edvp OUT VARCHAR2
,p_brand_type OUT VARCHAR2
)
IS
l_lookup_code VARCHAR2(20) := p_lookup_code;
l_l_sku VARCHAR2(100) := p_sku;
l_exclusivity VARCHAR2(2);
l_life_expectancy VARCHAR2(10);
l_rewards VARCHAR2(2);
l_opp_buy VARCHAR2(2);
l_price_profile VARCHAR2(50);
l_bv_edvp VARCHAR2(50);
l_brand_type VARCHAR2(80);
BEGIN
l_exclusivity := NULL;
l_life_expectancy := NULL;
l_rewards := NULL;
l_opp_buy := NULL;
l_price_profile := NULL;
l_bv_edvp := NULL;
l_brand_type := NULL;

p_exclusivity := l_exclusivity;
p_life_expectancy := l_life_expectancy;
p_rewards := l_rewards;
p_opp_buy := l_opp_buy;
p_price_profile := l_price_profile;
p_bv_edvp := l_bv_edvp;
p_brand_type := l_brand_type;

IF (p_lookup_code = 'WRFEE' OR p_lookup_code = 'CUS' OR p_lookup_code = 'ALL' OR p_lookup_code = 'DEL'
OR p_lookup_code = 'EXP' OR p_lookup_code = 'DPAF' OR p_lookup_code = 'LAB' OR p_lookup_code = 'NC'
OR p_lookup_code = 'SP' OR p_lookup_code = 'VM' OR p_lookup_code = 'SVG' OR p_lookup_code = 'REC'
OR p_lookup_code = '3PINS' OR p_lookup_code = 'ASBL' OR p_lookup_code = 'INS' OR p_lookup_code = 'HU'
OR p_lookup_code = 'USE' OR p_lookup_code = 'RLC' OR p_lookup_code = 'RPC' OR p_lookup_code = 'RPL'
OR p_lookup_code = 'EFEE'
) THEN
SELECT attribute1
INTO p_rewards
FROM mtl_system_items_b
WHERE segment1 = l_l_sku
-- We are getting the values only from the Master organization. The Master organization ID is 22.
AND organization_id = 22;
END IF;


IF (p_lookup_code = 'WTY' OR p_lookup_code = 'WR') THEN
SELECT attribute3
INTO p_rewards
FROM mtl_system_items_b
WHERE segment1 = l_l_sku
-- We are getting the values only from the Master organization. The Master organization ID is 22.
AND organization_id = 22;
END IF;
IF (p_lookup_code = 'SB' OR p_lookup_code = 'CON' OR p_lookup_code = 'STD'
OR p_lookup_code = 'SPE' OR p_lookup_code = 'SUITE') THEN
SELECT attribute1
, attribute4
, attribute9
, attribute11
, attribute12
, attribute14
, attribute15
INTO
p_exclusivity
, p_life_expectancy
, p_rewards
, p_opp_buy
, p_price_profile
, p_bv_edvp
, p_brand_type
FROM mtl_system_items_b
WHERE segment1 = l_l_sku
-- We are getting the values only from the Master organization. The Master organization ID is 22.
-- AND 1=2
AND organization_id = 22;
END IF;

EXCEPTION
WHEN OTHERS THEN
p_exclusivity := 'E';
p_bv_edvp := SUBSTR(SQLCODE,1,50);
p_brand_type := SUBSTR(SQLERRM,1,80);
END get_category_info;

PROCEDURE get_catalog_info
( p_sku IN VARCHAR2
, p_catalog_value OUT CLOB
)
IS
l_l_sku VARCHAR2(100) := p_sku;
l_l_catalog_value CLOB := NULL;
CURSOR get_catalog_cur
IS
SELECT rpad(v.element_sequence,3,' ') element_sequence
, rpad(NVL(v.element_value,' '),30,' ') element_value_us
, rpad(NVL(vt.element_value,' '),30,' ') element_value_frc
FROM mtl_descr_element_values_tl vt
, mtl_descr_element_values v
, mtl_system_items_b msi
WHERE msi.inventory_item_id = vt.inventory_item_id
AND vt.language = 'FRC'
AND msi.organization_id = 22
AND msi.segment1 = p_sku
AND v.inventory_item_id = vt.inventory_item_id
AND v.element_name = vt.element_name
ORDER BY v.element_sequence;

BEGIN
FOR get_catalog_rec IN get_catalog_cur
LOOP
IF (trim(get_catalog_rec.element_value_us) IS NOT NULL)
THEN
l_l_catalog_value :=

l_l_catalog_value||get_catalog_rec.element_sequence||get_catalog_rec.element_value_us||get_catalog_rec.element_value_frc;
ELSE
l_l_catalog_value := l_l_catalog_value||get_catalog_rec.element_sequence||rpad('N/A',30,' ')||rpad('N/A',30,' ');
END IF;
END LOOP;
p_catalog_value := l_l_catalog_value;
EXCEPTION
WHEN OTHERS THEN
p_catalog_value := l_l_catalog_value;
END get_catalog_info;

PROCEDURE item_extract_first ( p_file_name IN VARCHAR2
, p_file_directory IN VARCHAR2
, p_number IN NUMBER)
IS
/************************************************************************/
/* Description: procedure for first time run. */
/* This will fetch all the Items information of Master */
/* organization that is Organization_id = 22 to process. */
/* All the Records in the Custom table will be */
/* Inserted with sequence Number 1 */
/* */
/************************************************************************/

-- Cursor would fetch all the items Information from the Master organization.
-- This will not the fetch the Records which are already processed in previous run

CURSOR cur_current_items
IS
SELECT DECODE (SUBSTR (hou.NAME, 1, 1), '0', 'ZELLERS', 'BAY') orgcode
, flv.meaning itemtype
,DECODE(flv.meaning, '3rd Party Installation', 'PS'
, 'Accessory / Spare Part','PROD'
, 'Assemble', 'PS'
-- Allowance
, 'Consignment', 'PROD'
, 'Custom', 'PROD'
-- , 'Deferred Plan Admin Fee', 'PROD'
-- Delivery
, 'Environmental Fee', 'PS'
, 'Expense', 'PROD'
, 'Hook Up', 'PS'
, 'Installation', 'PS'
, 'Loaner', 'PROD'
, 'Non-COFI', 'PROD'
, 'Labor', 'PROD'
, 'Non-Hbc', 'PROD'
-- , 'Purchased item', 'PROD'
, 'Repair Expense Charges', 'PS'
, 'Repair Labour Charges', 'PS'
, 'Repair Parts Charges', 'PS'
, 'Salvage Removal', 'DSOPT'
, 'Sell/Buy', 'PROD'
, 'Special', 'PROD'
, 'Standard', 'PROD'
, 'Suite', 'PROD'
, 'Usage Fee', 'PS'
, 'Vendor Model', 'PROD'
, 'Warranty', 'PROD'
, 'Warranty Fee', 'PROD'
, 'Warranty Renewal', 'PROD'
, 'PROD') itemtype1
, msil.description description
, msilf.description description_frc
, LTRIM(RTRIM (msil.long_description,CHR(10)),CHR(10)) longdesc
, LTRIM(RTRIM (msilf.long_description,CHR(10)),CHR(10)) longdesc_frc
, msi.segment1 sku
, msi.inventory_item_id inventory_item_id
-- , upc.upc_number upc(It can not be handled at cursor query level).
, msi.taxable_flag taxable
, flv.lookup_code lookup_code
--, exclusivity
--life_expectancy
--rewards
-- opp_buy
-- price_profile
-- bv_edvp
-- brand_type
, mc.segment1 gma
, mc.segment2 lgroup
, mc.segment3 dept
, mc.segment4 cat
, mc.segment5 du
, mc.segment6 di
, muomt.unit_of_measure_tl uom
, muomtf.unit_of_measure_tl uom_frc
, msi.primary_uom_code primary_uom_code
, msi.weight_uom_code wt_uom
-- , muomt1.unit_of_measure_tl wt_uom
-- , muomt1.uom_code wt_uom
, msi.unit_weight unit_wt
, msi.dimension_uom_code wt_dim
-- , muomt2.unit_of_measure_tl wt_dim
-- , muomt2.uom_code wt_dim
, msi.unit_length length1
, msi.unit_width width
, msi.unit_height height
, msi.inventory_item_status_code item_status
, DECODE(msi.web_status, 'PUBLISHED', '3000', '2000') web_status
, msi.postprocessing_lead_time post_pro_lt
, msi.full_lead_time fixed_lt
, NULL is_bundle_sku
-- lh_rh_indicator
, NULL restock_fee
, NULL dis_cap
, NULL crew_required
, msi.service_item_flag is_warrant_sku
-- image_path
, msi.bom_enabled_flag bom_allowed
-- group_count
, msi.item_catalog_group_id catalog_group_id
-- seq_num
-- catalog_value
-- catalog_value_frc
FROM fnd_lookup_values_vl flv
, mtl_item_categories mic
, mtl_category_sets_tl mcst
, mtl_category_sets_b mcs
, mfg_lookups ml
, mtl_categories_b mc
, mtl_units_of_measure_tl muomt
, mtl_units_of_measure_tl muomt1
, mtl_units_of_measure_tl muomt2
, mtl_units_of_measure_tl muomtf
, mtl_system_items_b msi
, mtl_system_items_tl msil
, mtl_system_items_tl msilf
, hr_all_organization_units hou
WHERE (flv.lookup_type LIKE 'ITEM_TYPE')
--AND (flv.view_application_id = 3)
AND msi.inventory_item_id = msil.inventory_item_id
AND msi.item_type = flv.lookup_code
AND msi.organization_id = hou.organization_id
AND msilf.inventory_item_id = msil.inventory_item_id
AND msil.organization_id = msilf.organization_id
AND msil.organization_id = msi.organization_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.category_set_id = mcst.category_set_id
AND mic.category_id = mc.category_id
AND mcs.control_level = ml.lookup_code
AND ml.lookup_type = 'ITEM_CONTROL_LEVEL_GUI'
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
--AND xie.sku != msi.segment1
AND mcst.category_set_name = 'Inventory'
AND msi.primary_unit_of_measure = muomt.unit_of_measure
AND muomt.unit_of_measure = muomtf.unit_of_measure
AND NVL(msi.weight_uom_code, msi.primary_uom_code) = muomt1.uom_code
AND NVL(msi.dimension_uom_code, msi.primary_uom_code) = muomt2.uom_code
AND muomt1.LANGUAGE = 'US'
AND muomt2.LANGUAGE = 'US'
AND muomt.LANGUAGE = 'US'
AND muomtf.LANGUAGE = 'FRC'
AND mcst.LANGUAGE = 'US'
-- We are processing only Master Organization Records. Master Organization Code is 22
AND msi.organization_id = 22
AND msilf.language = 'FRC'
AND msil.language = 'US'
AND NOT EXISTS (SELECT 1 FROM xxcofi_replacement_item_ext xie where xie.sku = msi.segment1 and file_seq_num = 1)
-- AND msi.segment1 IN ('43825330', '57093817','84013211','60383163','70843057','83439162','50483080','05226637','09724378','66859620','74375643','03340021','55141030','55115984','54920392','55140453','71086698','06559017','44740884','44785806','62409628','83978533','83980244','83979836','83516309','83521730','80498728','83167536','83711274','83719062','03013318','43446434','43452838','43445923','76904903','06827325','06820961','43160225','83390165','41941774','43222116','43221506','43221936', '70633177' )
AND flv.meaning NOT IN ('Repair Parts Charges', 'Repair Expense Charges', 'Labor', 'Expense', 'Non-Hbc', 'Repair Labour Charges', 'Special', 'Warranty Fee', 'Standard', 'Usage Fee','Delivery', 'Allowance', 'Purchased item', 'ATO item', 'Deferred Plan Admin Fee', 'Environmental Fee', 'Salvage Removal')
AND msi.inventory_item_status_code NOT IN ('Active', 'Inactive')
AND rownum <= p_number
ORDER BY msi.segment1;

-- Parameter Variables

l_file_name VARCHAR2(20) := p_file_name;
l_file_directory VARCHAR2(150) := p_file_directory;


-- Variables

l_file_seq_num NUMBER;
l_orgcode VARCHAR2(13);
l_itemtype VARCHAR2(40);
l_itemtype1 VARCHAR2(40);
l_description VARCHAR2(40);
l_description_frc VARCHAR2(40);
l_longdesc VARCHAR2(1970);
l_longdesc_frc VARCHAR2(1970);
l_sku VARCHAR2(10);
l_upc VARCHAR2(30);
l_taxable VARCHAR2(2);
l_lookup_code VARCHAR2(20);
l_exclusivity VARCHAR2(2);
l_life_expectancy VARCHAR2(10);
l_rewards VARCHAR2(2);
l_opp_buy VARCHAR2(2);
l_price_profile VARCHAR2(50);
l_bv_edvp VARCHAR2(50);
l_brand_type VARCHAR2(80);
l_gma VARCHAR2(3);
l_lgroup VARCHAR2(3);
l_ldept VARCHAR2(3);
l_lcategory VARCHAR2(3);
l_du VARCHAR2(3);
l_di VARCHAR2(3);
l_uom VARCHAR2(25);
l_uom_frc VARCHAR2(25);
l_wt_uom VARCHAR2(25);
l_unit_wt NUMBER;
l_wt_dim VARCHAR2(25);
l_llength NUMBER;
l_width NUMBER;
l_height NUMBER;
l_item_status VARCHAR2(10);
l_web_status VARCHAR2(30);
l_post_pro_lt NUMBER;
l_fixed_lt NUMBER;
l_is_bundle_sku VARCHAR2(2);
l_lh_rh_indicator VARCHAR2(4);
l_restock_fee VARCHAR2(2);
l_dis_cap NUMBER;
l_crew_required VARCHAR2(2);
l_is_warrant_sku VARCHAR2(2);
l_image_path VARCHAR2(200);
l_bom_allowed VARCHAR2(2);
l_group_count NUMBER;
l_catalog_group_id NUMBER;
l_catalog_value CLOB;
l_extract_flag VARCHAR2(30);
l_extract_date DATE;
l_valid VARCHAR2(1);
l_errbuf VARCHAR2(2000);
l_retcode VARCHAR2(50);
l_excep EXCEPTION;
l_action VARCHAR2(6);

l_vm VARCHAR2(1);
l_warr_dur VARCHAR2(4);
l_model_value VARCHAR2(30);
l_vendor_value VARCHAR2(50);
l_catalog_name VARCHAR2(26);

l_pickupallowed_flag VARCHAR2(1);
l_deliveryallowed_flag VARCHAR2(1);
l_return_flag VARCHAR2(1);
l_infiniteinventory_flag VARCHAR2(1);

BEGIN

-- All the Records after validation get Inserted in Custom table
-- with file_seq_num = 1

l_file_seq_num := 1;

-- Item Fetch Cursor Open. Started.
FOR rec_current_items IN cur_current_items
LOOP

BEGIN
-- Initial Valid variable is set to 'Y' and if there is any error then set to 'N'

l_valid := 'Y';

/* Organization code would like BAY or ZELLERS */
l_orgcode := SUBSTR(rec_current_items.orgcode,1,13);

/* itemtype of the item */
l_itemtype := SUBSTR(rec_current_items.itemtype,1,40);

/* itemtype of the item */
l_itemtype1 := SUBSTR(rec_current_items.itemtype1,1,40);

/* Vendor Model validation */
l_vm := 'N';

if(TRIM(rec_current_items.itemtype) = 'Vendor Model')
THEN
l_vm := 'Y';
end if;

/* Warranty Duration in days */

l_warr_dur := NULL;

if(TRIM(rec_current_items.itemtype) = 'Warranty')
THEN
--l_warr_dur := SUBSTR(rec_current_items.primary_uom_code,1,1) * 365;
l_warr_dur := RTRIM(rec_current_items.primary_uom_code,'Y') * 365;
end if;

/* Description of the Item in US language */
l_description := SUBSTR(rec_current_items.description,1,40);

/* Description of the item in FRC Language */
l_description_frc := SUBSTR(rec_current_items.description_frc,1,40);

/* Long Description of the item in US language */
l_longdesc := SUBSTR(rec_current_items.longdesc,1,1970);

/* Long Description of the item in FRC Language */
l_longdesc_frc := SUBSTR(rec_current_items.longdesc_frc,1,1970);

/* SKU of the Item */
l_sku := SUBSTR(rec_current_items.sku,1,8);

/* UPC Number of the Item (Alias Value)*/
-- It may have NULL value return.

l_upc := get_upc(l_sku);

/* Taxable Enable check for Item */
l_taxable := SUBSTR(rec_current_items.taxable,1,2);

l_lookup_code := SUBSTR(rec_current_items.lookup_code,1,20);

/* lookup_code and SKU values used to get all the Other Values of attribute */
--

get_category_info ( l_lookup_code
,l_sku
,l_exclusivity
,l_life_expectancy
,l_rewards
,l_opp_buy
,l_price_profile
,l_bv_edvp
,l_brand_type
);

IF(l_exclusivity = 'E') THEN
l_errbuf := ('Category Information '||':'||l_brand_type);
l_retcode := l_bv_edvp;
raise l_excep;
END IF;

-- Category Is the Key Flexfield.
/* Catagory Information GMA */
l_gma := SUBSTR(rec_current_items.gma,1,3);

/* Catagory Information Group */
l_lgroup := SUBSTR(rec_current_items.lgroup,1,3);

/* Catagory Information Dept */
l_ldept := SUBSTR(rec_current_items.dept,1,3);

/* Catagory Information Category */
l_lcategory := SUBSTR(rec_current_items.cat,1,3);

/* Catagory Information DU */
l_du := SUBSTR(rec_current_items.du,1,3);

/* Catagory Information DI */
l_di := SUBSTR(rec_current_items.di,1,3);

/* UOM of item in US Lanuage */
l_uom := SUBSTR(rec_current_items.uom,1,25);
IF(SUBSTR(TRIM(rec_current_items.primary_uom_code), -1,1) = 'Y')
THEN
l_uom := 'DAYS';
END IF;

/* UOM of item in FRC Lanuage */
l_uom_frc := SUBSTR(rec_current_items.uom_frc,1,25);

/* Action value based on category and UOM change */
/* For conversion it will be always NULL */
l_action := NULL;

/*Weight UOM of the Item */
l_wt_uom := SUBSTR(rec_current_items.wt_uom,1,25);

/* Unit of Weight */
l_unit_wt := SUBSTR(rec_current_items.unit_wt,1,20);

/* Dimension on Weight */
l_wt_dim := SUBSTR(rec_current_items.wt_dim,1,25);

/* Length of the Item */
l_llength := SUBSTR(rec_current_items.length1,1,20);

/* Width of the Item */
l_width := SUBSTR(rec_current_items.width,1,20);

/* Height of the Item */
l_height := SUBSTR(rec_current_items.height,1,20);

/* Status of the Item */
l_item_status := SUBSTR(rec_current_items.item_status,1,10);

/* Web status of the Item */
l_web_status := SUBSTR(rec_current_items.web_status,1,30);

/* Post Processing Lead time */
l_post_pro_lt := SUBSTR(rec_current_items.post_pro_lt,1,20);

/* Full Lead time */
l_fixed_lt := get_lead_time(l_sku);

-- Lead time should always be 1 and greater then 1.

IF(l_fixed_lt < 1) THEN
l_fixed_lt := 1;
END IF;

l_is_bundle_sku := NULL;

l_restock_fee := NULL;

l_dis_cap := NULL;

l_crew_required := NULL;

l_is_warrant_sku := SUBSTR(rec_current_items.is_warrant_sku,1,2);

/* Image path of the item */
l_image_path := get_image(l_sku);

/* Model Infrmation of the Item Catalog */
l_model_value := get_model_style_info(l_sku);

/* Vendor Information of the item Catalog */
l_vendor_value := get_vendor_info(l_sku);

/* BOM Allowed Indicator */
l_bom_allowed := SUBSTR(rec_current_items.bom_allowed,1,2);

/* Extract flag and date information */
l_extract_flag := 'Extract Pending';
l_extract_date := NULL;

/* Pickup and deliveryallowed and infinite inventory flag validation */

l_pickupallowed_flag := 'Y';
l_deliveryallowed_flag := 'Y';
l_return_flag := 'Y';
l_infiniteinventory_flag := 'Y';

IF((l_is_warrant_sku = 'Y') AND (l_itemtype1 = 'PROD'))
THEN
l_pickupallowed_flag := 'Y';
l_deliveryallowed_flag := 'N';
l_infiniteinventory_flag := 'Y';
END IF;


/* Catalog Group Count */
l_group_count := get_catalog_count(l_sku);

/* Catalog Group ID */
l_catalog_group_id := SUBSTR(rec_current_items.catalog_group_id,1,3);

/* Category Name */
l_catalog_name := get_catalog_name(l_catalog_group_id);

/* Right hand Indicator */
--- First check if it is Ref Type Item and then check if ref is right or left
IF(l_catalog_group_id=52)
THEN
l_lh_rh_indicator := get_right_ind(l_sku);
ELSE
l_lh_rh_indicator := NULL;
END IF;

/* Catalog Information with all the sequence and values */
-- This values should get from loop.

get_catalog_info (l_sku
,l_catalog_value);

IF (l_valid = 'Y') THEN

l_catalog_value := dbms_lob.substr( l_catalog_value, 4473, 1 );

-- INSERT INTO THE CUSTOM TABLE
INSERT INTO xxcofi_replacement_item_ext
( file_seq_num
, orgcode
, itemtype
, itemtype1
, vendor_model
, warranty_dur
, description
, description_frc
, longdesc
, longdesc_frc
, sku
, inventory_item_id
, item_catalog_group_id
, upc
, taxable
, exclusivity
, life_expectancy
, rewards
, opp_buy
, price_profile
, bv_edvp
, brand_type
, gma
, lgroup
, ldept
, lcategory
, du
, di
, uom
, uom_frc
, action
, wt_uom
, unit_wt
, wt_dim
, llength
, width
, height
, item_status
, web_status
, post_pro_lt
, fixed_lt
, is_bundle_sku
, lh_rh_indicator
, restock_fee
, dis_cap
, crew_required
, is_warrant_sku
, image_path
, model_style
, bom_allowed
, manufacturer
, pickupallowed_flag
, deliveryallowed_flag
, return_flag
, infiniteinventory_flag
, group_count
, catalog_name
, catalog_group_id
, catalog_value
, extract_flag
, extract_date )
VALUES
(
l_file_seq_num
,l_orgcode
,l_itemtype
,l_itemtype1
,l_vm
,l_warr_dur
,l_description
,l_description_frc
,l_longdesc
,l_longdesc_frc
,l_sku
,rec_current_items.inventory_item_id
,rec_current_items.catalog_group_id
,l_upc
,l_taxable
,l_exclusivity
,l_life_expectancy
,l_rewards
,l_opp_buy
,l_price_profile
,l_bv_edvp
,l_brand_type
,l_gma
,l_lgroup
,l_ldept
,l_lcategory
,l_du
,l_di
,l_uom
,l_uom_frc
,l_action
,l_wt_uom
,l_unit_wt
,l_wt_dim
,l_llength
,l_width
,l_height
,l_item_status
,l_web_status
,l_post_pro_lt
,l_fixed_lt
,l_is_bundle_sku
,l_lh_rh_indicator
,l_restock_fee
,l_dis_cap
,l_crew_required
,l_is_warrant_sku
,l_image_path
,l_model_value
,l_bom_allowed
,l_vendor_value
,l_pickupallowed_flag
,l_deliveryallowed_flag
,l_return_flag
,l_infiniteinventory_flag
,l_group_count
,l_catalog_name
,l_catalog_group_id
,l_catalog_value
,l_extract_flag
,l_extract_date
);

END IF;

COMMIT;

EXCEPTION
WHEN l_excep THEN
write('L','Error while fetching catagory Information: ' || l_retcode || l_errbuf);
write('L','For the SKU: ' || l_sku);
WHEN OTHERS THEN
write('L','Error while fetching Item Information: ' || SQLCODE || SQLERRM);
write('L','For SKU ' || rec_current_items.sku );

END;

END LOOP;
-- Item Fetch Cursor Open. Ended.

/* Calling the Procedure to create the Data file */

created_data_file ( l_file_name
, l_file_directory
, l_file_seq_num);

END item_extract_first;


/* procedure to send output to the fnd log */

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
BEGIN

IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.log, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;

END write;

/* procedure to write output to the log and output file */

PROCEDURE generate_output IS
BEGIN

--The following parameters will be displayed every time
--in log file

write('L', '.........1.........2.........3.........4.........5.........6.........7.........8');
write('L', '________________________________________________________________________________');
write('L','');
write('L','Request Name : '||g_program_name);
write('L','Request Id : '||g_request_id);
write('L','Date : '||TO_CHAR(SYSDATE,'MM/DD/YY'));
write('L','Total Number of Records processed : '||g_total_count_rec);
write('L','Number of Record Extracted in current run : '||g_count_records);

EXCEPTION

WHEN OTHERS THEN
write('L','Error : '||SQLCODE||SQLERRM);

END generate_output;

END xxcofi_item_con_ext_pkg;
/

SHOW ERROR
/