Hi Experts,
I have a requirement to upload the files from apex and inserts into Oracle Fusion UCM.
Currently I created File Browse Item and passing blob contents to SOAP web service [ APEX_WEB_SERVICE.MAKE_REQUEST ]
Executed the below script in the page process, but file is not moved to UCM and also no error is throwing. Kindly assist me on this.
Note:
1. Content Type should support - CSV,XLSX,Image formats.
2. Response - should be captured and inserted into the custom table.
Apex Env: 19.2
DB - 12C
Declare
l_result XMLTYPE;
l_envelope CLOB;
l_filename varchar2(255);
l_BLOB BLOB;
l_CLOB CLOB;
l_response_msg varchar2(32767);
BEGIN
IF :P2_file_name IS NOT NULL THEN
SELECT filename, BLOB_CONTENT
INTO l_filename, l_BLOB
FROM APEX_APPLICATION_TEMP_FILES
WHERE name = :P2_file_name;
l_CLOB := apex_web_service.blob2clobbase64(l_BLOB);
l_envelope := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/" xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">
<soapenv:Header/>
<soapenv:Body>
<typ:uploadFileToUcm>
<typ:document>
<erp:Content>'||l_CLOB||'</erp:Content>
<erp:FileName>'||l_filename||'</erp:FileName>
<erp:ContentType>csv</erp:ContentType> -- Need support for all types like [ CSV,XLSX,Image ]
<erp:DocumentAuthor>abc@test.com</erp:DocumentAuthor> <erp:DocumentSecurityGroup>FAFusionImportExport</erp:DocumentSecurityGroup> <erp:DocumentAccount>fin/receivables/import</erp:DocumentAccount>
</typ:document>
</typ:uploadFileToUcm>
</soapenv:Body>
</soapenv:Envelope>';
-- Call the web service
l_result := apex_web_service.make_request(
p_url => 'https://ejwe-test.fa.em2.oraclecloud.com/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL',
p_action => 'http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/ErpIntegrationService/uploadFileToUcm',
p_envelope => l_envelope,
p_username => abc@test.com',
p_password => ‘test');
dbms_output.put_line(l_result.getClobVal());
l_response_msg := apex_web_service.parse_response(
p_collection_name=>'FILE_RESPONSE',
p_xpath=>'//idc:CheckInUniversalResponse/idc:CheckInUniversalResult/idc:StatusInfo/idc:statusMessage/text()', p_ns=>'http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/');
:P2_RESPONSE := l_response_msg;
END IF;
END;
Regards.
Robin