Wednesday, October 10, 2012

Item Attributes Loading Extract

CREATE OR REPLACE PACKAGE XXCOFI_ATTR_EXTRACT_PKG AUTHID CURRENT_USER
AS

PROCEDURE delete_tables;

PROCEDURE sku_insert (I_SKU IN VARCHAR2);

PROCEDURE attr_extract;

PROCEDURE item_attr;

PROCEDURE attr_init;

END XXCOFI_ATTR_EXTRACT_PKG;
/

CREATE OR REPLACE PACKAGE BODY XXCOFI_ATTR_EXTRACT_PKG
IS

PROCEDURE delete_tables
IS

BEGIN
DELETE FROM XXCOFI.XXCOFI_ATTR_INIT;
COMMIT;
DELETE FROM XXCOFI.XXCOFI_ATTR_ITEM;
COMMIT;
DELETE FROM XXCOFI.XXCOFI_ATTR_SKU;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END delete_tables;

PROCEDURE sku_insert (I_SKU IN VARCHAR2)
IS
temp VARCHAR2(1);

BEGIN
    INSERT INTO XXCOFI.XXCOFI_ATTR_SKU
        (SKU)
    VALUES
        (I_SKU);
    COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
temp := temp; --continue
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END sku_insert;

PROCEDURE attr_extract
IS
-- 1 = enabled, 0 = disabled
stest NUMBER := 0;
sdev NUMBER := 0;
sconv NUMBER := 0;
sprd NUMBER := 0;
--environment variables
snum NUMBER := 4; --number of environments
scont NUMBER; --boolean trigger
sname VARCHAR2(50); --current environment name
surl VARCHAR2(100); --soap url

--INTO fields for cursor
L_SEGMENT1 XXCOFI.XXCOFI_ATTR.SEGMENT1%TYPE;
L_ITEM_CATALOG_GROUP_ID XXCOFI.XXCOFI_ATTR.ITEM_CATALOG_GROUP_ID%TYPE;
L_ELEMENT_SEQUENCE XXCOFI.XXCOFI_ATTR.ELEMENT_SEQUENCE%TYPE;
L_ELEMENT_NAME XXCOFI.XXCOFI_ATTR.ELEMENT_NAME%TYPE;
L_DESCRIPTION XXCOFI.XXCOFI_ATTR.DESCRIPTION%TYPE;
L_ELEMENT_VALUE_ENG XXCOFI.XXCOFI_ATTR.ELEMENT_VALUE_ENG%TYPE;
L_ELEMENT_VALUE_FRC XXCOFI.XXCOFI_ATTR.ELEMENT_VALUE_FRC%TYPE;

SL SYS_REFCURSOR;

soap_request CLOB;
soap_respond CLOB;
st CLOB;
l_intstatus NUMBER;
http_req sys.utl_http.req;
http_resp sys.utl_http.resp;

REC_ELE_ENG VARCHAR2(150);
REC_ELE_FRC VARCHAR2(150);

L_SEG XXCOFI.XXCOFI_ATTR.SEGMENT1%TYPE := 'x';
L_ELE_NM XXCOFI.XXCOFI_ATTR.ELEMENT_NAME%TYPE := 'x';
CE NUMBER:=1;
CV NUMBER:=1;

I NUMBER;

O_INSTANCE_NAME VARCHAR2(16);

BEGIN
SELECT INSTANCE_NAME
INTO O_INSTANCE_NAME
FROM V$INSTANCE;

IF O_INSTANCE_NAME = 'HPRD' THEN
sprd := 1;
ELSE
stest := 1;
END IF;

--DBMS_OUTPUT.ENABLE(buffer_size=>null);
attr_init;
item_attr;

FOR X IN 1..snum
LOOP

scont := 0;
sname := '';

IF X = 1 THEN
    IF stest = 1 THEN
        sname := 'TEST_ENV';
        surl := 'http://ccdev07:7080/MCFFConversionRequestServiceTEST';
        scont := 1;
    END IF;
END IF;
IF X = 2 THEN
    IF sdev = 1 THEN
        sname := 'DEV_ENV';
        surl := 'http://ccdev07:7080/MCFFConversionRequestServiceDEV';
        scont := 1;
    END IF;
END IF;
IF X = 3 THEN
    IF sconv = 1 THEN
        sname := 'CONV_ENV';
        surl := 'http://ccdev70:7080/MCFFConversionRequestServiceCONV';
        scont := 1;
    END IF;
END IF;
IF X = 4 THEN
    IF sprd = 1 THEN
        sname := 'PRD_ENV';
        surl := 'http://ccib02:7080/MCFFConversionRequestServicePRD';
        scont := 1;
    END IF;
END IF;

--DBMS_OUTPUT.put_line(sname);

IF scont = 1 THEN

OPEN SL FOR
'SELECT *
FROM
(SELECT DISTINCT A.SEGMENT1,
TO_CHAR(A.ITEM_CATALOG_GROUP_ID) ITEM_CATALOG_GROUP_ID,
4030 ELEMENT_SEQUENCE,
'||''''||'Category'||''''||' ELEMENT_NAME,
'||''''||'Category'||''''||' DESCRIPTION,
A.SEGMENT4 ELEMENT_VALUE_ENG,
A.SEGMENT4 ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
UNION ALL
SELECT DISTINCT A.SEGMENT1,
TO_CHAR(A.ITEM_CATALOG_GROUP_ID) ITEM_CATALOG_GROUP_ID,
4020 ELEMENT_SEQUENCE,
'||''''||'Pricing Strategy'||''''||' ELEMENT_NAME,
'||''''||'Pricing Strategy'||''''||' DESCRIPTION,
A.DFFPRICINGSTRATEGY ELEMENT_VALUE_ENG,
A.DFFPRICINGSTRATEGY ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
UNION ALL
SELECT DISTINCT A.SEGMENT1,
TO_CHAR(A.ITEM_CATALOG_GROUP_ID) ITEM_CATALOG_GROUP_ID,
4010 ELEMENT_SEQUENCE,
'||''''||'Brand'||''''||' ELEMENT_NAME,
'||''''||'Brand'||''''||' DESCRIPTION,
A.ELEMENT_VALUE ELEMENT_VALUE_ENG,
MAX(A.ELEMENT_VALUE_FRC) ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
GROUP BY A.SEGMENT1, A.ITEM_CATALOG_GROUP_ID, A.ELEMENT_VALUE
UNION ALL
SELECT DISTINCT A.SEGMENT1,
TO_CHAR(A.ITEM_CATALOG_GROUP_ID) ITEM_CATALOG_GROUP_ID,
4000 ELEMENT_SEQUENCE,
'||''''||'Item Status'||''''||' ELEMENT_NAME,
'||''''||'Item Status'||''''||' DESCRIPTION,
A.ITEM_STATUS ELEMENT_VALUE_ENG,
A.ITEM_STATUS ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
UNION ALL
SELECT DISTINCT '||''''||'Promotion'||''''||' SEGMENT1,
'||''''||'GENERIC_PROMO_ATTR'||''''||' ITEM_CATALOG_GROUP_ID,
1030 ELEMENT_SEQUENCE,
'||''''||'Category'||''''||' ELEMENT_NAME,
'||''''||'Category'||''''||' DESCRIPTION,
A.SEGMENT4 ELEMENT_VALUE_ENG,
A.SEGMENT4 ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
UNION ALL
SELECT DISTINCT '||''''||'Promotion'||''''||' SEGMENT1,
'||''''||'GENERIC_PROMO_ATTR'||''''||' ITEM_CATALOG_GROUP_ID,
1020 ELEMENT_SEQUENCE,
'||''''||'Pricing Strategy'||''''||' ELEMENT_NAME,
'||''''||'Pricing Strategy'||''''||' DESCRIPTION,
A.DFFPRICINGSTRATEGY ELEMENT_VALUE_ENG,
A.DFFPRICINGSTRATEGY ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
UNION ALL
SELECT DISTINCT '||''''||'Promotion'||''''||' SEGMENT1,
'||''''||'GENERIC_PROMO_ATTR'||''''||' ITEM_CATALOG_GROUP_ID,
1010 ELEMENT_SEQUENCE,
'||''''||'Brand'||''''||' ELEMENT_NAME,
'||''''||'Brand'||''''||' DESCRIPTION,
A.ELEMENT_VALUE ELEMENT_VALUE_ENG,
MAX(A.ELEMENT_VALUE_FRC) ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
GROUP BY A.ELEMENT_VALUE
UNION ALL
SELECT DISTINCT '||''''||'Promotion'||''''||' SEGMENT1,
'||''''||'GENERIC_PROMO_ATTR'||''''||' ITEM_CATALOG_GROUP_ID,
1000 ELEMENT_SEQUENCE,
'||''''||'Item Status'||''''||' ELEMENT_NAME,
'||''''||'Item Status'||''''||' DESCRIPTION,
A.ITEM_STATUS ELEMENT_VALUE_ENG,
A.ITEM_STATUS ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
UNION ALL
SELECT DISTINCT A.SEGMENT1,
TO_CHAR(A.ITEM_CATALOG_GROUP_ID) ITEM_CATALOG_GROUP_ID,
4040 ELEMENT_SEQUENCE,
'||''''||'Item Discontinue Date'||''''||' ELEMENT_NAME,
'||''''||'Item Discontinue Date'||''''||' DESCRIPTION,
A.IMPLEMENTED_DATE ELEMENT_VALUE_ENG,
A.IMPLEMENTED_DATE ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_ITEM A
UNION ALL
SELECT A.SEGMENT1,
A.ITEM_CATALOG_GROUP_ID,
A.ELEMENT_SEQUENCE,
A.ELEMENT_NAME,
A.DESCRIPTION,
A.ELEMENT_VALUE_ENG,
A.ELEMENT_VALUE_FRC
FROM XXCOFI.XXCOFI_ATTR_INIT A) D
WHERE NOT EXISTS (SELECT 1
                                FROM XXCOFI.XXCOFI_ATTR C
                               WHERE C.ITEM_CATALOG_GROUP_ID = D.ITEM_CATALOG_GROUP_ID
    AND C.ELEMENT_SEQUENCE = D.ELEMENT_SEQUENCE
    AND (NVL(C.ELEMENT_VALUE_ENG,'||''''||'N/A'||''''||') = NVL(D.ELEMENT_VALUE_ENG,'||''''||'N/A'||''''||'))
    AND C.'||sname||'= 1)
ORDER BY D.SEGMENT1,
D.ELEMENT_NAME,
D.ELEMENT_VALUE_ENG';
LOOP
    FETCH SL INTO
    L_SEGMENT1,
    L_ITEM_CATALOG_GROUP_ID,
    L_ELEMENT_SEQUENCE,
    L_ELEMENT_NAME,
    L_DESCRIPTION,
    L_ELEMENT_VALUE_ENG,
    L_ELEMENT_VALUE_FRC;
    EXIT WHEN SL%NOTFOUND;
        IF NOT (L_ELEMENT_NAME = L_ELE_NM AND L_SEGMENT1 = L_SEG) THEN
            IF NOT L_SEG = 'x' THEN
soap_request := soap_request ||
'        

   
]]>
?
ItemLocalization


';

                BEGIN
                soap_respond := null;

                http_req:= utl_http.begin_request
                (surl
                ,'POST'
                , 'HTTP/1.1'
                );

                --DBMS_OUTPUT.put_line('a1');
                utl_http.set_header(http_req, 'Content-Type', 'text/xml');
                utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
                utl_http.set_header(http_req, 'SOAPAction', '');
                utl_http.write_text(http_req, soap_request);
                http_resp:= utl_http.get_response(http_req);
                utl_http.read_text(http_resp, soap_respond);
                utl_http.end_response(http_resp);

I := 1;
--WHILE I <= LENGTH(soap_respond) LOOP
--DBMS_OUTPUT.PUT_LINE(SUBSTR(soap_respond, I, 255));   
--I := I + 255;   
--END LOOP;   

                EXCEPTION
                WHEN OTHERS THEN
                --DBMS_OUTPUT.put_line('UTL_HTTP Calling FOR CATALOG NAMES Error Code'||' '||SQLCODE);
                --DBMS_OUTPUT.put_line('UTL_HTTP Calling FOR CATALOG NAMES Error Message'||' '||SQLERRM);
                ROLLBACK;
                END;
               
                SELECT INSTR(soap_respond,'SUCCESSFUL')
                INTO l_intstatus
                FROM dual;

                IF l_intstatus>0 THEN
                --DBMS_OUTPUT.put_line('l_intstatus: '||l_intstatus);
                COMMIT;
                ELSE
                --DBMS_OUTPUT.put_line('l_intstatusr: '||l_intstatus);
                ROLLBACK;
                END IF;
            END IF;
soap_request := '
 
 
 
<![CDATA[

        IsAllowedValueDefined="Y" IsValueMandatory="N" LongDescription="'||L_ELEMENT_NAME||'"
    Operation="Manage" OrganizationCode="BAY" SequenceNo="'||TO_CHAR(CE)||'" ShortDescription="'||L_ELEMENT_NAME||'">
       
                        Operation="Manage" ShortDescription="'||L_DESCRIPTION||'"/>
       
        '||CHR(13)||CHR(10);
           
            --DBMS_OUTPUT.PUT_LINE(L_SEGMENT1||'    '||L_ITEM_CATALOG_GROUP_ID||'    '||L_ELEMENT_SEQUENCE||'    '||L_ELEMENT_NAME||'    '||L_DESCRIPTION||'    '||CE);
            L_SEG := L_SEGMENT1;
            L_ELE_NM := L_ELEMENT_NAME;
            CE := CE + 1;
            CV := 1;
        END IF;
       
        REC_ELE_ENG := NVL(REPLACE(REPLACE(REPLACE(REPLACE(L_ELEMENT_VALUE_ENG,'&','&'||'amp;'),'"','&'||'quot;'),'<','&'||'lt;'),'>','&'||'gt;'),'N/A');
        REC_ELE_FRC := NVL(REPLACE(REPLACE(REPLACE(REPLACE(L_ELEMENT_VALUE_FRC,'&','&'||'amp;'),'"','&'||'quot;'),'<','&'||'lt;'),'>','&'||'gt;'),REC_ELE_ENG);
       
st := '                         NumericStep="" Operation="Create" SequenceNo="'||TO_CHAR(CV)||'"  ShortDescription="'||REC_ELE_ENG||'"
             Value="'||REC_ELE_ENG||'">
               
                                        Operation="" ShortDescription="'||REC_ELE_FRC||'" Variant=""/>
               
            '||CHR(13)||CHR(10);

        IF LENGTH(soap_request) + LENGTH(st) <= 32767 - 266 THEN
soap_request := soap_request || st;
        ELSE
soap_request := soap_request ||
'        
    ]]>
?
ItemLocalization
 
 ';

                BEGIN
                soap_respond := null;

                http_req:= utl_http.begin_request
                (surl
                ,'POST'
                , 'HTTP/1.1'
                );

                --DBMS_OUTPUT.put_line('c1');
                utl_http.set_header(http_req, 'Content-Type', 'text/xml');
                utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
                utl_http.set_header(http_req, 'SOAPAction', '');
                utl_http.write_text(http_req, soap_request);
                http_resp:= utl_http.get_response(http_req);
                utl_http.read_text(http_resp, soap_respond);
                utl_http.end_response(http_resp);
                I := 1;
--WHILE I <= LENGTH(soap_respond) LOOP
--DBMS_OUTPUT.PUT_LINE(SUBSTR(soap_respond, I, 255));   
--I := I + 255;   
--END LOOP;   
                EXCEPTION
                WHEN OTHERS THEN
                --DBMS_OUTPUT.put_line('UTL_HTTP Calling FOR CATALOG NAMES Error Code'||' '||SQLCODE);
                --DBMS_OUTPUT.put_line('UTL_HTTP Calling FOR CATALOG NAMES Error Message'||' '||SQLERRM);
                ROLLBACK;
                END;
               
                SELECT INSTR(soap_respond,'SUCCESSFUL')
                INTO l_intstatus
                FROM dual;

                IF l_intstatus>0 THEN
                --DBMS_OUTPUT.put_line('l_intstatus: '||l_intstatus);
                COMMIT;
                ELSE
                --DBMS_OUTPUT.put_line('l_intstatusr: '||l_intstatus);
                ROLLBACK;
                END IF;
soap_request := '
 
 
 
<![CDATA[

        IsAllowedValueDefined="Y" IsValueMandatory="N" LongDescription="'||L_ELEMENT_NAME||'"
    Operation="Manage" OrganizationCode="BAY" SequenceNo="'||TO_CHAR(CE - 1)||'" ShortDescription="'||L_ELEMENT_NAME||'">
       
                        Operation="Manage" ShortDescription="'||L_DESCRIPTION||'"/>
       
        '||CHR(13)||CHR(10);

        --DBMS_OUTPUT.PUT_LINE(L_SEGMENT1||'    '||L_ITEM_CATALOG_GROUP_ID||'    '||L_ELEMENT_SEQUENCE||'    '||L_ELEMENT_NAME||'    '||L_DESCRIPTION||'    '||(CE - 1));
soap_request := soap_request || st;
        END IF;
       
        BEGIN
            EXECUTE IMMEDIATE ('DECLARE
            L_dummy  VARCHAR2(1) := NULL;
            BEGIN
                SELECT '||''''||'x'||''''||'
                INTO L_dummy
                FROM XXCOFI.XXCOFI_ATTR
                WHERE ITEM_CATALOG_GROUP_ID = '||''''||TRIM(L_ITEM_CATALOG_GROUP_ID)||''''||'
                AND ELEMENT_SEQUENCE = '||L_ELEMENT_SEQUENCE||'
                AND ELEMENT_VALUE_ENG = '||''''||REPLACE(L_ELEMENT_VALUE_ENG,'''','''||''''''''||''')||''''||';
            END;');
               
            EXECUTE IMMEDIATE('UPDATE XXCOFI.XXCOFI_ATTR
            SET '||sname||' = 1
            WHERE ITEM_CATALOG_GROUP_ID = '||''''||TRIM(L_ITEM_CATALOG_GROUP_ID)||''''||'
            AND ELEMENT_SEQUENCE = '||L_ELEMENT_SEQUENCE||'
            AND ELEMENT_VALUE_ENG = '||''''||REPLACE(L_ELEMENT_VALUE_ENG,'''','''||''''''''||''')||'''');
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            EXECUTE IMMEDIATE('INSERT INTO XXCOFI.XXCOFI_ATTR
                        (SEGMENT1, ITEM_CATALOG_GROUP_ID, ELEMENT_SEQUENCE, ELEMENT_NAME, DESCRIPTION, ELEMENT_VALUE_ENG, ELEMENT_VALUE_FRC, '||sname||')
            VALUES
                        ('||''''||L_SEGMENT1||''''||','||''''||TRIM(L_ITEM_CATALOG_GROUP_ID)||''''||','||L_ELEMENT_SEQUENCE||','||''''||REPLACE(L_ELEMENT_NAME,'''','''||''''''''||''')||''''||','||''''||REPLACE(L_DESCRIPTION,'''','''||''''''''||''')||''''||','||''''||REPLACE(L_ELEMENT_VALUE_ENG,'''','''||''''''''||''')||''''||','||''''||REPLACE(L_ELEMENT_VALUE_FRC,'''','''||''''''''||''')||''''||', 1)');
        END;

    --DBMS_OUTPUT.PUT_LINE(L_ELEMENT_VALUE_ENG||'    '||L_ELEMENT_VALUE_FRC||'    '||CV);
    CV := CV + 1;
END LOOP;

IF NOT L_SEG = 'x' THEN
    soap_request := soap_request ||
    '        
       
    ]]>
    ?
    ItemLocalization
    
    
    ';

    BEGIN
        soap_respond := null;

        http_req:= utl_http.begin_request
        (surl
        ,'POST'
        , 'HTTP/1.1'
        );

        --DBMS_OUTPUT.put_line('b1');
        utl_http.set_header(http_req, 'Content-Type', 'text/xml');
        utl_http.set_header(http_req, 'Content-Length', LENGTH(soap_request));
        utl_http.set_header(http_req, 'SOAPAction', '');
        utl_http.write_text(http_req, soap_request);
        http_resp:= utl_http.get_response(http_req);
        utl_http.read_text(http_resp, soap_respond);
        utl_http.end_response(http_resp);
       
        I := 1;
--WHILE I <= LENGTH(soap_respond) LOOP
--DBMS_OUTPUT.PUT_LINE(SUBSTR(soap_respond, I, 255));   
--I := I + 255;   
--END LOOP;   
       
    EXCEPTION
    WHEN OTHERS THEN
    --DBMS_OUTPUT.put_line('UTL_HTTP Calling FOR CATALOG NAMES Error Code'||' '||SQLCODE);
    --DBMS_OUTPUT.put_line('UTL_HTTP Calling FOR CATALOG NAMES Error Message'||' '||SQLERRM);
    ROLLBACK;
    END;

    SELECT INSTR(soap_respond,'SUCCESSFUL')
    INTO l_intstatus
    FROM dual;

    IF l_intstatus>0 THEN
    --DBMS_OUTPUT.put_line('l_intstatus: '||l_intstatus);
    COMMIT;
    ELSE
    --DBMS_OUTPUT.put_line('l_intstatusr: '||l_intstatus);
    ROLLBACK;
    END IF;
END IF;
CLOSE SL;

END IF;

END LOOP; --X LOOP

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END attr_extract;

PROCEDURE item_attr
IS

temp VARCHAR2(1);
O_IMPLEMENTED_DATE VARCHAR2(30);

CURSOR SL IS
SELECT A.*,
DECODE (A.lookup_code,'SB',B.element_value,'CON',B.element_value,'STD',B.element_value,'SPE',B.element_value,'SUITE',B.element_value,'') ELEMENT_VALUE,
DECODE (A.lookup_code,'SB',B.element_value_frc,'CON',B.element_value_frc,'STD',B.element_value_frc,'SPE',B.element_value_frc,'SUITE',B.element_value_frc,'') ELEMENT_VALUE_FRC
FROM
(SELECT msi.segment1 sku,
msi.inventory_item_id,
micg.segment1,
msi.item_catalog_group_id,
mc.segment4,
DECODE(msi.inventory_item_status_code,'COFI Activ','Active','Suppressed','Disc','EOL','Disc','Disc') item_status,
msi.attribute14 dffpricingstrategy,
flv.lookup_code
FROM mtl_system_items_b msi,
          mtl_item_categories mic,
          mtl_categories_b mc,
          mtl_category_sets_b mcs,
          mtl_category_sets_tl mcst,
          fnd_lookup_values_vl flv,
          mtl_item_catalog_groups micg
WHERE msi.organization_id = '22'
AND msi.segment1 IN (SELECT SKU FROM XXCOFI.XXCOFI_ATTR_SKU)
AND micg.item_catalog_group_id = msi.item_catalog_group_id

AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND mic.category_id = mc.category_id

AND mic.category_set_id = mcs.category_set_id
AND mcs.category_set_id = mcst.category_set_id
AND mcst.category_set_name = 'Inventory'
AND mcst.LANGUAGE = 'US'

AND flv.lookup_code = msi.item_type

AND (flv.meaning NOT IN --Can't be included in subsequent not in section as it is different.
               ('Warranty',
                '3rd Party Installation',
                'Assemble',
                'Hook Up',
                'Installation'
                )
             AND NOT NVL(SUBSTR (msi.item_catalog_group_id, 1, 3),'xx') = 'xx'
            )
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.meaning NOT IN
               ('Repair Parts Charges',
                'Repair Expense Charges',
                'Labor',
                'Expense',
                'Non-Hbc',
                'Repair Labour Charges',
                'Usage Fee',
                'Allowance',
                'Purchased item',
                'ATO item',
                'Deferred Plan Admin Fee',
                'Environmental Fee'
               )) A LEFT OUTER JOIN

(SELECT v.inventory_item_id IID,
             v.element_value  element_value,
             vt.element_value element_value_frc
 FROM mtl_descr_element_values v,
          APPS.mtl_descr_element_values_tl vt
WHERE v.inventory_item_id = vt.inventory_item_id
AND v.element_name = vt.element_name
AND vt.element_name = 'Brand/Vendor'
AND vt.LANGUAGE = 'FRC') B
ON A.inventory_item_id = B.IID;
              
BEGIN

--DBMS_OUTPUT.ENABLE(buffer_size=>null);
FOR REC IN SL LOOP
            BEGIN
                BEGIN
                SELECT MAX(IMPLEMENTED_DATE)
                INTO O_IMPLEMENTED_DATE
                FROM mtl_pending_item_status mpis
                WHERE mpis.status_code = 'Disc'
                AND mpis.organization_id = '22'
                AND mpis.inventory_item_id = REC.inventory_item_id;

                EXCEPTION
                WHEN OTHERS THEN
                O_IMPLEMENTED_DATE := NULL;
                END;

            INSERT INTO XXCOFI.XXCOFI_ATTR_ITEM
                        (SKU, INVENTORY_ITEM_ID, SEGMENT1, ITEM_CATALOG_GROUP_ID, SEGMENT4, ITEM_STATUS, DFFPRICINGSTRATEGY, LOOKUP_CODE, ELEMENT_VALUE, ELEMENT_VALUE_FRC, IMPLEMENTED_DATE)
            VALUES
                        (REC.SKU, REC.INVENTORY_ITEM_ID, REC.SEGMENT1, REC.ITEM_CATALOG_GROUP_ID, REC.SEGMENT4, REC.ITEM_STATUS, REC.LOOKUP_CODE, REC.DFFPRICINGSTRATEGY, REC.ELEMENT_VALUE, REC.ELEMENT_VALUE_FRC, O_IMPLEMENTED_DATE);
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
            temp := temp; --continue
            WHEN OTHERS THEN
            fnd_file.put_line (fnd_file.LOG, SQLERRM);
            END;
            COMMIT;
END LOOP;

END item_attr;

PROCEDURE attr_init
IS

temp VARCHAR2(1);

REC_ELE_ENG VARCHAR2(150);
REC_ELE_FRC VARCHAR2(150);

CURSOR SL IS
SELECT A.SEGMENT1,
    TO_CHAR(A.ITEM_CATALOG_GROUP_ID) ITEM_CATALOG_GROUP_ID,
    A.ELEMENT_SEQUENCE,
    A.ELEMENT_NAME,
    A.DESCRIPTION,
    A.ELEMENT_VALUE_ENG,
    MAX(VT.ELEMENT_VALUE) ELEMENT_VALUE_FRC
    FROM
        (SELECT MICG.SEGMENT1,
        MSI.ITEM_CATALOG_GROUP_ID,
        V.ELEMENT_SEQUENCE,
        V.ELEMENT_NAME,
        MDET2.DESCRIPTION,
        V.ELEMENT_VALUE ELEMENT_VALUE_ENG,
        V.INVENTORY_ITEM_ID
        FROM APPS.MTL_ITEM_CATALOG_GROUPS MICG,
        APPS.MTL_SYSTEM_ITEMS_B MSI,
        APPS.MTL_DESCRIPTIVE_ELEMENTS_TL MDET2,
        APPS.MTL_DESCR_ELEMENT_VALUES V
        WHERE MICG.ITEM_CATALOG_GROUP_ID = MSI.ITEM_CATALOG_GROUP_ID
        AND MSI.ORGANIZATION_ID = 22
        AND MSI.ITEM_CATALOG_GROUP_ID = MDET2.ITEM_CATALOG_GROUP_ID
        AND MSI.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID
        AND MDET2.LANGUAGE = 'FRC'
        AND MDET2.ELEMENT_NAME = V.ELEMENT_NAME
        AND MSI.SEGMENT1 IN (SELECT SKU FROM XXCOFI.XXCOFI_ATTR_SKU)
        GROUP BY MICG.SEGMENT1,
        MSI.ITEM_CATALOG_GROUP_ID,
        V.ELEMENT_SEQUENCE,
        V.ELEMENT_NAME,
        MDET2.DESCRIPTION,
        V.ELEMENT_VALUE,
        V.INVENTORY_ITEM_ID) A,
    APPS.MTL_DESCR_ELEMENT_VALUES_TL VT
    WHERE A.INVENTORY_ITEM_ID = VT.INVENTORY_ITEM_ID
    AND A.ELEMENT_NAME = VT.ELEMENT_NAME
    AND VT.LANGUAGE = 'FRC'
    GROUP BY A.SEGMENT1,
    A.ITEM_CATALOG_GROUP_ID,
    A.ELEMENT_SEQUENCE,
    A.ELEMENT_NAME,
    A.DESCRIPTION,
    A.ELEMENT_VALUE_ENG;
              
BEGIN


--DBMS_OUTPUT.ENABLE(buffer_size=>null);
FOR REC IN SL LOOP
            BEGIN
            REC_ELE_ENG := NVL(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REC.ELEMENT_VALUE_ENG,CHR(133),' '),CHR(149),' '),CHR(150),' '),CHR(153),' '),CHR(191),' ')),'N/A');
            REC_ELE_FRC := NVL(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REC.ELEMENT_VALUE_FRC,CHR(133),' '),CHR(149),' '),CHR(150),' '),CHR(153),' '),CHR(191),' ')),REC_ELE_ENG);

            INSERT INTO XXCOFI.XXCOFI_ATTR_INIT
                        (SEGMENT1, ITEM_CATALOG_GROUP_ID, ELEMENT_SEQUENCE, ELEMENT_NAME, DESCRIPTION, ELEMENT_VALUE_ENG, ELEMENT_VALUE_FRC)
            VALUES
                        (REC.SEGMENT1,TRIM(REC.ITEM_CATALOG_GROUP_ID),REC.ELEMENT_SEQUENCE,REC.ELEMENT_NAME,REC.DESCRIPTION,REC_ELE_ENG,REC_ELE_FRC);
            EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
            temp := temp;
            WHEN OTHERS THEN
            fnd_file.put_line (fnd_file.LOG, SQLERRM);
            END;
            COMMIT;
END LOOP;

END attr_init;

END xxcofi_attr_extract_pkg;
/
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)