/* Thanks to Raghavendra for sharing this */
CREATE OR REPLACE PACKAGE APPS.XXCOFI_ITEM_CATALOG_CONV_PKG AS
PROCEDURE XXCOFI_ITEM_CATALOG(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2 );
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXCOFI_ITEM_CATALOG_CONV_PKG AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_itemcatalog_conv_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Raghavendar G */
/* */
/* DATE : 18-JUN-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will ExtractItem Catalog Values and */
/* send it to Webservice */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 18/06/10 Raghavendar G 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE XXCOFI_ITEM_CATALOG(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
) IS
l_errcode VARCHAR2(100);
l_errmsg VARCHAR2(1000);
l_count NUMBER:=0;
l_elemvalueeng VARCHAR2(100);
l_elemvaluefrc VARCHAR2(100);
l_namecnt NUMBER:=0;
l_intstatus NUMBER;
l_valuecnt NUMBER:=0;
l_catalogid NUMBER;
l_catalognameeng VARCHAR2(100);
l_catalognamefrc VARCHAR2(100);
l_elemnameeng VARCHAR2(100);
l_elemnamefrc VARCHAR2(100);
l_elemseqeng NUMBER;
l_catalognameengsd VARCHAR2(100);
l_catalognameengld VARCHAR2(100);
l_catalognamefrcsd VARCHAR2(100);
l_catalognamefrcld VARCHAR2(100);
l_elemvalueengsd VARCHAR2(100);
l_elemvalueengld VARCHAR2(100);
l_elemvaluefrcsd VARCHAR2(100);
l_elemvaluefrcld VARCHAR2(100);
l_elemvalue_request CLOB;
soap_request CLOB;
soap_respond CLOB;
http_req utl_http.req;
http_resp utl_http.resp;
TYPE xxcofi_ic_header IS RECORD ( CATALOGGROUPID NUMBER
,ELEMSEQENG NUMBER
,ELEMENTNAMEENG VARCHAR2(100)
,ELEMENTVALUEEENG VARCHAR2(100)
,ELEMSEQFRC NUMBER
,ELEMENTNAMEFRC VARCHAR2(100)
,ELEMENTVALUEEFRC VARCHAR2(100)
);
l_ic_header xxcofi_ic_header;
CURSOR c_icg IS
SELECT micg1.item_catalog_group_id "CATALOGID"
,micg1.segment1 "CATALOGNAMEENG"
,micg2.segment1 "CATALOGNAMEFRC"
FROM mtl_item_Catalog_groups_tl micg1
,mtl_item_Catalog_groups_tl micg2
WHERE micg1.item_catalog_group_id=micg2.item_catalog_group_id
AND micg1.language='US'
AND micg2.language='FRC'
ORDER BY micg1.item_catalog_group_id ;
CURSOR c_geticvalue IS
SELECT DISTINCT msib.item_catalog_group_id "CATALOGGROUPID"
,mde.element_sequence "ELEMSEQENG"
,mdev1.element_name "ELEMENTNAMEENG"
,mdev1.element_value "ELEMENTVALUEENG"
,mde.element_sequence "ELEMSEQFRC"
,mdev2.element_name "ELEMENTNAMEFRC"
,mdev2.element_value "ELEMENTVALUEFRC"
FROM mtl_system_items_b msib
,mtl_descr_element_values_tl mdev1
,mtl_descr_element_values_tl mdev2
,mtl_descriptive_elements mde
WHERE msib.inventory_item_id=mdev1.inventory_item_id
AND mdev1.language='US'
AND mdev1.element_name=mde.element_name
AND msib.inventory_item_id=mdev2.inventory_item_id
AND mdev2.language='FRC'
AND mdev2.element_name=mde.element_name
AND msib.item_Catalog_group_id=mde.item_catalog_group_id
AND (mdev1.element_value is not null or mdev2.element_value is not null)
AND msib.item_catalog_group_id=4;
-- AND mde.element_name IN('# of Cassettes','# of Discs');
CURSOR c_ic IS
SELECT DISTINCT catalogid CATALOGID
,elementnameeng "ELEMENTNAMEENG"
,elemseqeng "ELEMSEQENG"
,elementnamefrc "ELEMENTNAMEFRC"
FROM xxcofi_itemcatalog_iface
ORDER BY elementnameeng;
-- WHERE elementnameeng='Description';
CURSOR c_icvalue(p_elementname VARCHAR2) IS
SELECT ELEMENTVALUEENG "ELEMENTVALUEENG"
,ELEMENTVALUEFRC "ELEMENTVALUEFRC"
FROM XXCOFI_ITEMCATALOG_IFACE
WHERE elementnameeng=p_elementname;
BEGIN
FOR rec_icg IN c_icg LOOP
l_catalogid :=rec_icg.CATALOGID;
l_catalognameengsd:=rec_icg.CATALOGNAMEENG;
l_catalognamefrcsd:=rec_icg.CATALOGNAMEFRC;
l_catalognameengld:=rec_icg.CATALOGNAMEENG;
l_catalognamefrcld:=rec_icg.CATALOGNAMEFRC;
BEGIN
soap_request:= '
<![CDATA[
]]>
?
ItemLocalization
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_request);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST FOR CATALOG NAMES Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST FRO CATALOG NAMES Error Message'||' '||l_errmsg);
END;
BEGIN
http_req:= utl_http.begin_request
( 'http://ccdev07:7080/MCFFConversionRequestServiceDEV'
,'POST'
, 'HTTP/1.1'
);
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);
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_respond);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling FOR CTALOG NAMES Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling FOR CATALOG NAMES Error Message'||' '||l_errmsg);
END;
SELECT INSTR(soap_respond,'SUCCESSFUL')
INTO l_intstatus
FROM dual;
IF l_intstatus>0 THEN
INSERT INTO XXCOFI_ICG_XMLMSG VALUES(l_catalogid,'S',soap_request,soap_respond,sysdate);
COMMIT;
ELSE
INSERT INTO XXCOFI_ICG_XMLMSG VALUES(l_catalogid,'F',soap_request,soap_respond,sysdate);
END IF;
COMMIT;
soap_request:=null;
END LOOP;
DELETE FROM XXCOFI_ITEMCATALOG_IFACE;
DELETE FROM XXCOFI_ITEMCATALOG_XMLMSG;
COMMIT;
soap_request:=null;
l_intstatus:=NULL;
FOR rec_geticvalue IN c_geticvalue LOOP
l_ic_header.CATALOGGROUPID := rec_geticvalue.CATALOGGROUPID;
l_ic_header.ELEMSEQENG := rec_geticvalue.ELEMSEQENG;
l_ic_header.ELEMENTNAMEENG := rec_geticvalue.ELEMENTNAMEENG;
l_ic_header.ELEMENTVALUEEENG := rec_geticvalue.ELEMENTVALUEENG;
l_ic_header.ELEMSEQFRC := rec_geticvalue.ELEMSEQFRC;
l_ic_header.ELEMENTNAMEFRC := rec_geticvalue.ELEMENTNAMEFRC;
l_ic_header.ELEMENTVALUEEFRC := rec_geticvalue.ELEMENTVALUEFRC;
INSERT INTO xxcofi_itemcatalog_iface VALUES(l_ic_header.CATALOGGROUPID
,l_ic_header.ELEMSEQENG
,l_ic_header.ELEMENTNAMEENG
,l_ic_header.ELEMENTVALUEEENG
,l_ic_header.ELEMSEQFRC
,l_ic_header.ELEMENTNAMEFRC
,l_ic_header.ELEMENTVALUEEFRC
,SYSDATE);
COMMIT;
--fnd_file.put_line(fnd_file.LOG,l_ic_header.CATALOGGROUPID||' ~ '||l_ic_header.ELEMENTNAMEENG||' ~ '||l_ic_header.ELEMENTVALUEEENG||' ~ '||l_ic_header.ELEMENTNAMEFRC||' ~ '||l_ic_header.ELEMENTVALUEEFRC);
l_count:=l_count+1;
END LOOP;
fnd_file.put_line(fnd_file.LOG,'Total Number of Records fetched:'||' '||l_count);
FOR rec_ic IN c_ic LOOP
l_catalogid:=rec_ic.CATALOGID;
l_elemnameeng:=rec_ic.ELEMENTNAMEENG;
l_elemseqeng:=rec_ic.ELEMSEQENG;
l_elemnamefrc:=rec_ic.ELEMENTNAMEFRC;
fnd_file.put_line(fnd_file.LOG,'ELEMENT NAME ENGLISH ~ SEQENG:'||' '||rec_ic.ELEMENTNAMEENG||' ~ '||rec_ic.ELEMSEQENG);
FOR rec_icvalue IN c_icvalue(rec_ic.ELEMENTNAMEENG) LOOP
BEGIN
l_elemvalueengsd:=rec_icvalue.ELEMENTVALUEENG;
l_elemvalueengld:=rec_icvalue.ELEMENTVALUEENG;
l_elemvaluefrcsd:=rec_icvalue.ELEMENTVALUEFRC;
l_elemvaluefrcld:=rec_icvalue.ELEMENTVALUEFRC;
l_elemvalue_request:= l_elemvalue_request||
''||' '||CHR(13)||CHR(10)||
' ''||' '||CHR(13)||CHR(10)||
''||' '||CHR(13)||CHR(10)||
' '||' '||CHR(13)||CHR(10)||
' '||CHR(13)||CHR(10);
l_valuecnt:=l_valuecnt+1;
--fnd_file.put_line(fnd_file.LOG,'CATALOG ELEMENT VALUE ENGLISH ~ FRENCH:'||' '||l_elemvalueengsd||' ~ '||l_elemvaluefrcsd);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO CATALOG ELEMENT VALUE Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO CATALOG ELEMENT VALUE Error Message'||' '||l_errmsg);
END;
END LOOP;
l_namecnt:=l_namecnt+1;
fnd_file.put_line(fnd_file.LOG,'Total Number of Catalog Element Values fetched:'||' '||l_valuecnt);
BEGIN
soap_request:= '
<![CDATA[
'||l_elemvalue_request||'
]]>
?
ItemLocalization
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_request);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST Error Message'||' '||l_errmsg);
END;
BEGIN
--utl_http.set_transfer_timeout(180);
http_req:= utl_http.begin_request
( 'http://ccdev07:7080/MCFFConversionRequestServiceDEV'
,'POST'
, 'HTTP/1.1'
);
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);
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_respond);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling Error Message'||' '||l_errmsg);
END;
SELECT INSTR(soap_respond,'SUCCESSFUL')
INTO l_intstatus
FROM dual;
--If Message is SUCCESS Insert the recod into XXCOFI_SR_XMLMSG
IF l_intstatus>0 THEN
INSERT INTO XXCOFI_ITEMCATALOG_XMLMSG VALUES(l_catalogid,'S',0,l_elemnameeng,soap_request,soap_respond,sysdate);
COMMIT;
ELSE
INSERT INTO XXCOFI_ITEMCATALOG_XMLMSG VALUES(l_catalogid,'F',0,l_elemnameeng,soap_request,soap_respond,sysdate);
END IF;
COMMIT;
l_elemvalue_request:= null;
soap_request:= null;
END LOOP;
fnd_file.put_line(fnd_file.LOG,'Total Number of Catalog Element Names fetched:'||' '||l_namecnt);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'PROCEDURE LEVEL Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'PROCEDURE LEVEL Error Message'||' '||l_errmsg);
END XXCOFI_ITEM_CATALOG;
END XXCOFI_ITEM_CATALOG_CONV_PKG ;
/
CREATE OR REPLACE PACKAGE APPS.XXCOFI_ITEM_CATALOG_CONV_PKG AS
PROCEDURE XXCOFI_ITEM_CATALOG(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2 );
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXCOFI_ITEM_CATALOG_CONV_PKG AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_itemcatalog_conv_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Raghavendar G */
/* */
/* DATE : 18-JUN-2010 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will ExtractItem Catalog Values and */
/* send it to Webservice */
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 18/06/10 Raghavendar G 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE XXCOFI_ITEM_CATALOG(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
) IS
l_errcode VARCHAR2(100);
l_errmsg VARCHAR2(1000);
l_count NUMBER:=0;
l_elemvalueeng VARCHAR2(100);
l_elemvaluefrc VARCHAR2(100);
l_namecnt NUMBER:=0;
l_intstatus NUMBER;
l_valuecnt NUMBER:=0;
l_catalogid NUMBER;
l_catalognameeng VARCHAR2(100);
l_catalognamefrc VARCHAR2(100);
l_elemnameeng VARCHAR2(100);
l_elemnamefrc VARCHAR2(100);
l_elemseqeng NUMBER;
l_catalognameengsd VARCHAR2(100);
l_catalognameengld VARCHAR2(100);
l_catalognamefrcsd VARCHAR2(100);
l_catalognamefrcld VARCHAR2(100);
l_elemvalueengsd VARCHAR2(100);
l_elemvalueengld VARCHAR2(100);
l_elemvaluefrcsd VARCHAR2(100);
l_elemvaluefrcld VARCHAR2(100);
l_elemvalue_request CLOB;
soap_request CLOB;
soap_respond CLOB;
http_req utl_http.req;
http_resp utl_http.resp;
TYPE xxcofi_ic_header IS RECORD ( CATALOGGROUPID NUMBER
,ELEMSEQENG NUMBER
,ELEMENTNAMEENG VARCHAR2(100)
,ELEMENTVALUEEENG VARCHAR2(100)
,ELEMSEQFRC NUMBER
,ELEMENTNAMEFRC VARCHAR2(100)
,ELEMENTVALUEEFRC VARCHAR2(100)
);
l_ic_header xxcofi_ic_header;
CURSOR c_icg IS
SELECT micg1.item_catalog_group_id "CATALOGID"
,micg1.segment1 "CATALOGNAMEENG"
,micg2.segment1 "CATALOGNAMEFRC"
FROM mtl_item_Catalog_groups_tl micg1
,mtl_item_Catalog_groups_tl micg2
WHERE micg1.item_catalog_group_id=micg2.item_catalog_group_id
AND micg1.language='US'
AND micg2.language='FRC'
ORDER BY micg1.item_catalog_group_id ;
CURSOR c_geticvalue IS
SELECT DISTINCT msib.item_catalog_group_id "CATALOGGROUPID"
,mde.element_sequence "ELEMSEQENG"
,mdev1.element_name "ELEMENTNAMEENG"
,mdev1.element_value "ELEMENTVALUEENG"
,mde.element_sequence "ELEMSEQFRC"
,mdev2.element_name "ELEMENTNAMEFRC"
,mdev2.element_value "ELEMENTVALUEFRC"
FROM mtl_system_items_b msib
,mtl_descr_element_values_tl mdev1
,mtl_descr_element_values_tl mdev2
,mtl_descriptive_elements mde
WHERE msib.inventory_item_id=mdev1.inventory_item_id
AND mdev1.language='US'
AND mdev1.element_name=mde.element_name
AND msib.inventory_item_id=mdev2.inventory_item_id
AND mdev2.language='FRC'
AND mdev2.element_name=mde.element_name
AND msib.item_Catalog_group_id=mde.item_catalog_group_id
AND (mdev1.element_value is not null or mdev2.element_value is not null)
AND msib.item_catalog_group_id=4;
-- AND mde.element_name IN('# of Cassettes','# of Discs');
CURSOR c_ic IS
SELECT DISTINCT catalogid CATALOGID
,elementnameeng "ELEMENTNAMEENG"
,elemseqeng "ELEMSEQENG"
,elementnamefrc "ELEMENTNAMEFRC"
FROM xxcofi_itemcatalog_iface
ORDER BY elementnameeng;
-- WHERE elementnameeng='Description';
CURSOR c_icvalue(p_elementname VARCHAR2) IS
SELECT ELEMENTVALUEENG "ELEMENTVALUEENG"
,ELEMENTVALUEFRC "ELEMENTVALUEFRC"
FROM XXCOFI_ITEMCATALOG_IFACE
WHERE elementnameeng=p_elementname;
BEGIN
FOR rec_icg IN c_icg LOOP
l_catalogid :=rec_icg.CATALOGID;
l_catalognameengsd:=rec_icg.CATALOGNAMEENG;
l_catalognamefrcsd:=rec_icg.CATALOGNAMEFRC;
l_catalognameengld:=rec_icg.CATALOGNAMEENG;
l_catalognamefrcld:=rec_icg.CATALOGNAMEFRC;
BEGIN
soap_request:= '
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_request);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST FOR CATALOG NAMES Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST FRO CATALOG NAMES Error Message'||' '||l_errmsg);
END;
BEGIN
http_req:= utl_http.begin_request
( 'http://ccdev07:7080/MCFFConversionRequestServiceDEV'
,'POST'
, 'HTTP/1.1'
);
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);
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_respond);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling FOR CTALOG NAMES Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling FOR CATALOG NAMES Error Message'||' '||l_errmsg);
END;
SELECT INSTR(soap_respond,'SUCCESSFUL')
INTO l_intstatus
FROM dual;
IF l_intstatus>0 THEN
INSERT INTO XXCOFI_ICG_XMLMSG VALUES(l_catalogid,'S',soap_request,soap_respond,sysdate);
COMMIT;
ELSE
INSERT INTO XXCOFI_ICG_XMLMSG VALUES(l_catalogid,'F',soap_request,soap_respond,sysdate);
END IF;
COMMIT;
soap_request:=null;
END LOOP;
DELETE FROM XXCOFI_ITEMCATALOG_IFACE;
DELETE FROM XXCOFI_ITEMCATALOG_XMLMSG;
COMMIT;
soap_request:=null;
l_intstatus:=NULL;
FOR rec_geticvalue IN c_geticvalue LOOP
l_ic_header.CATALOGGROUPID := rec_geticvalue.CATALOGGROUPID;
l_ic_header.ELEMSEQENG := rec_geticvalue.ELEMSEQENG;
l_ic_header.ELEMENTNAMEENG := rec_geticvalue.ELEMENTNAMEENG;
l_ic_header.ELEMENTVALUEEENG := rec_geticvalue.ELEMENTVALUEENG;
l_ic_header.ELEMSEQFRC := rec_geticvalue.ELEMSEQFRC;
l_ic_header.ELEMENTNAMEFRC := rec_geticvalue.ELEMENTNAMEFRC;
l_ic_header.ELEMENTVALUEEFRC := rec_geticvalue.ELEMENTVALUEFRC;
INSERT INTO xxcofi_itemcatalog_iface VALUES(l_ic_header.CATALOGGROUPID
,l_ic_header.ELEMSEQENG
,l_ic_header.ELEMENTNAMEENG
,l_ic_header.ELEMENTVALUEEENG
,l_ic_header.ELEMSEQFRC
,l_ic_header.ELEMENTNAMEFRC
,l_ic_header.ELEMENTVALUEEFRC
,SYSDATE);
COMMIT;
--fnd_file.put_line(fnd_file.LOG,l_ic_header.CATALOGGROUPID||' ~ '||l_ic_header.ELEMENTNAMEENG||' ~ '||l_ic_header.ELEMENTVALUEEENG||' ~ '||l_ic_header.ELEMENTNAMEFRC||' ~ '||l_ic_header.ELEMENTVALUEEFRC);
l_count:=l_count+1;
END LOOP;
fnd_file.put_line(fnd_file.LOG,'Total Number of Records fetched:'||' '||l_count);
FOR rec_ic IN c_ic LOOP
l_catalogid:=rec_ic.CATALOGID;
l_elemnameeng:=rec_ic.ELEMENTNAMEENG;
l_elemseqeng:=rec_ic.ELEMSEQENG;
l_elemnamefrc:=rec_ic.ELEMENTNAMEFRC;
fnd_file.put_line(fnd_file.LOG,'ELEMENT NAME ENGLISH ~ SEQENG:'||' '||rec_ic.ELEMENTNAMEENG||' ~ '||rec_ic.ELEMSEQENG);
FOR rec_icvalue IN c_icvalue(rec_ic.ELEMENTNAMEENG) LOOP
BEGIN
l_elemvalueengsd:=rec_icvalue.ELEMENTVALUEENG;
l_elemvalueengld:=rec_icvalue.ELEMENTVALUEENG;
l_elemvaluefrcsd:=rec_icvalue.ELEMENTVALUEFRC;
l_elemvaluefrcld:=rec_icvalue.ELEMENTVALUEFRC;
l_elemvalue_request:= l_elemvalue_request||
'
'
'
'
'
l_valuecnt:=l_valuecnt+1;
--fnd_file.put_line(fnd_file.LOG,'CATALOG ELEMENT VALUE ENGLISH ~ FRENCH:'||' '||l_elemvalueengsd||' ~ '||l_elemvaluefrcsd);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO CATALOG ELEMENT VALUE Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO CATALOG ELEMENT VALUE Error Message'||' '||l_errmsg);
END;
END LOOP;
l_namecnt:=l_namecnt+1;
fnd_file.put_line(fnd_file.LOG,'Total Number of Catalog Element Values fetched:'||' '||l_valuecnt);
BEGIN
soap_request:= '
'||l_elemvalue_request||'
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_request);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'ASSIGNING VALUES TO SOAP_REQUEST Error Message'||' '||l_errmsg);
END;
BEGIN
--utl_http.set_transfer_timeout(180);
http_req:= utl_http.begin_request
( 'http://ccdev07:7080/MCFFConversionRequestServiceDEV'
,'POST'
, 'HTTP/1.1'
);
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);
fnd_file.put_line(fnd_file.LOG,'Response Message from WebService' ||' '||soap_respond);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'UTL_HTTP Calling Error Message'||' '||l_errmsg);
END;
SELECT INSTR(soap_respond,'SUCCESSFUL')
INTO l_intstatus
FROM dual;
--If Message is SUCCESS Insert the recod into XXCOFI_SR_XMLMSG
IF l_intstatus>0 THEN
INSERT INTO XXCOFI_ITEMCATALOG_XMLMSG VALUES(l_catalogid,'S',0,l_elemnameeng,soap_request,soap_respond,sysdate);
COMMIT;
ELSE
INSERT INTO XXCOFI_ITEMCATALOG_XMLMSG VALUES(l_catalogid,'F',0,l_elemnameeng,soap_request,soap_respond,sysdate);
END IF;
COMMIT;
l_elemvalue_request:= null;
soap_request:= null;
END LOOP;
fnd_file.put_line(fnd_file.LOG,'Total Number of Catalog Element Names fetched:'||' '||l_namecnt);
EXCEPTION
WHEN OTHERS THEN
l_errcode:=SQLCODE;
l_errmsg :=SQLERRM;
fnd_file.put_line(fnd_file.LOG,'PROCEDURE LEVEL Error Code'||' '||l_errcode);
fnd_file.put_line(fnd_file.LOG,'PROCEDURE LEVEL Error Message'||' '||l_errmsg);
END XXCOFI_ITEM_CATALOG;
END XXCOFI_ITEM_CATALOG_CONV_PKG ;
/
No comments:
Post a Comment