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;
/
