log file details:
------------------------------------------------
+---------------------------------------------------------------------------+
Business Online: Version : 12.1
Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.
BLK_COUPA_LOAD_SUP_SITES: BLK Coupa Load Supplier Sites
+---------------------------------------------------------------------------+
Current system time is 16-OCT-2019 05:55:02
+---------------------------------------------------------------------------+
**Starts**16-OCT-2019 05:55:02
**Ends**16-OCT-2019 05:55:06
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
* ***************************************************************************
* ********************Beginning Coupa remit-to-address api.******************
*
* Last run date of this program is: 16-OCT-2019 05:53:00
* Current run date of this program is: 16-OCT-2019 05:55:02
*
*
* In get_url routine...
* Started processing of supplier: "BANCO BRADESCO CARTOES"
* Supplier number: "69531"
*
*
* Checking if supplier exists on Coupa or not.
* Calling procedure GET_CONTENT to check supplier on Coupa
*
* In get_application_code...
* In Get_Key_Value routine...
* In get_proxy...
* GET event start...
* Set Oracle Wallet...
* In get_wallet_loc routine...
* In get_wallet_pwd routine...
* In get_wallet_loc routine...
* wallet loc: file:/dbro1i/oracle/product/12102/owm/wallets/ordbro1i
* Set Proxy...omcs-proxy.oracleoutsourcing.com:80
* GET event - Set URL... https://blackrock-test.coupahost.com/api/suppliers?number=69531
* GET event - retrieve values
* GET Event - status is Success
* GET event - Add content to table for end point id...13
* ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure: 526040
* Processing of GET_CONTENT completed
*
* Supplier-BANCO BRADESCO CARTOES exists on Coupa, now remit-to-address can be uploaded.
* Now checking if address for the supplier- BANCO BRADESCO CARTOES exists on Coupa or not.
* In get_url routine...
* In get_application_code...
* In Get_Key_Value routine...
* In get_proxy...
* GET event start...
* Set Oracle Wallet...
* In get_wallet_loc routine...
* In get_wallet_pwd routine...
* In get_wallet_loc routine...
* wallet loc: file:/dbro1i/oracle/product/12102/owm/wallets/ordbro1i
* Set Proxy...omcs-proxy.oracleoutsourcing.com:80
* GET event - Set URL... https://blackrock-test.coupahost.com/api/suppliers/3452/addresses?name=65279
* GET event - retrieve values
* GET Event - status is Success
* GET event - Add content to table for end point id...13
* ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure for remit-to-address: 526041
*
* Address for supplier-BANCO BRADESCO CARTOES with site-id-65279 already exists on Coupa. Proceeding to update
*
* Calling procedure INSERT_CONTENT to save the data in custom table for future reference
*
* Processing of INSERT_CONTENT completed
*
* l_clob : {"active":true,"city":"Alphaville","country":[{"code":"BR"}],"name":65279,"postal-code":"06454-000","remit-to-code":"WIRE XXXX","state":"Barueri/SP","street1":"Alameda Rio Negro, 585","street2":"Edifício Demini, 1º, 2º, 11º e 12º andar"}
* In get_application_code...
* In get_application_code...
* In Get_Key_Value routine...
* In get_proxy...
* PUT event ...
* Set Oracle Wallet...
* In get_wallet_loc routine...
* In get_wallet_pwd routine...
* Set Proxy...omcs-proxy.oracleoutsourcing.com:80
PUT event Set URL...https://blackrock-test.coupahost.com/api/suppliers/3452/addresses/23438
PUT event - loop thru CLOB and write to url body...
* PUT event error - return status - 400
* Into put content end of body exception
* Raise bad put
* Error while putting data on Coupa, Error is -
* Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa
*
* Processing of PUT_CONTENT completed
*
*
* Program processed one vendor site-65279
* ***************************************************************************
*
*
* Now checking if address for the supplier- BANCO BRADESCO CARTOES exists on Coupa or not.
* In get_url routine...
* In get_application_code...
* In Get_Key_Value routine...
* In get_proxy...
* GET event start...
* Set Oracle Wallet...
* In get_wallet_loc routine...
* In get_wallet_pwd routine...
* In get_wallet_loc routine...
* wallet loc: file:/dbro1i/oracle/product/12102/owm/wallets/ordbro1i
* Set Proxy...omcs-proxy.oracleoutsourcing.com:80
* GET event - Set URL... https://blackrock-test.coupahost.com/api/suppliers/3452/addresses?name=78726
* GET event - retrieve values
* GET Event - status is Success
* GET event - Add content to table for end point id...13
* ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure for remit-to-address: 526043
*
* Address for supplier-BANCO BRADESCO CARTOES with site-id-78726 already exists on Coupa. Proceeding to update
*
* Calling procedure INSERT_CONTENT to save the data in custom table for future reference
*
* Processing of INSERT_CONTENT completed
*
* l_clob : {"active":true,"city":"Alphaville","country":[{"code":"BR"}],"name":78726,"postal-code":"06454-000","remit-to-code":"AMEX V2 BRL 0923","state":"Barueri/SP","street1":"Alameda Rio Negro, 585","street2":"Edifício Demini, 1º, 2º, 11º e 12º andar"}
* In get_application_code...
* In get_application_code...
* In Get_Key_Value routine...
* In get_proxy...
* PUT event ...
* Set Oracle Wallet...
* In get_wallet_loc routine...
* In get_wallet_pwd routine...
* Set Proxy...omcs-proxy.oracleoutsourcing.com:80
PUT event Set URL...https://blackrock-test.coupahost.com/api/suppliers/3452/addresses/23757
PUT event - loop thru CLOB and write to url body...
* PUT event error - return status - 400
* Into put content end of body exception
* Raise bad put
* Error while putting data on Coupa, Error is -
* Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa
*
* Processing of PUT_CONTENT completed
*
*
* Program processed one vendor site-78726
* ***************************************************************************
*
*
* Total remit-to-address got processed: 1
* Total new remit-to-address created on Coupa: 0
* Total existing remit-to-address updated on Coupa:- 2
* Total remit-to-address got rejected: -1
*
*
* **********************End Coupa remit-to-address api. **********************
* ***************************************************************************
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
No completion options were requested.
Output file size:
0
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 16-OCT-2019 05:55:06
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Put content code:
PROCEDURE put_content(p_end_point_id IN NUMBER,p_url IN VARCHAR2, p_content CLOB, p_method IN VARCHAR2 ) AS
v_http_request UTL_HTTP.req;
v_http_response UTL_HTTP.resp;
v_text VARCHAR2(32767);
v_clob CLOB;
v_length NUMBER := LENGTH(p_content) ;
v_Data_bytes NUMBER := 32000;
v_offset NUMBER := 1;
v_chunk_string VARCHAR2(32000);
-- v_put_url VARCHAR2(5000) := p_url; --Commented as part of Jira#26955
v_put_url VARCHAR2(32000) := p_url; --Added as part of Jira#26955
v_application VARCHAR2(10) := get_application_code(p_end_point_id);
v_key VARCHAR2(100) := get_key_value (get_application_code(p_end_point_id));
v_proxy VARCHAR2(200); -- Jira#22630
bad_put EXCEPTION;
server_exception EXCEPTION;
BEGIN
v_proxy := get_proxy(v_application); -- Jira#22630
-- Make a HTTP request and get the response.
print_log('PUT event ...');
--Read the wallet location and password and set the wallet .
print_log('Set Oracle Wallet...');
utl_http.set_wallet(get_wallet_loc('ORACLE_EBS'), get_wallet_pwd('ORACLE_EBS'));
-- Jira#22630
IF v_proxy IS NOT NULL
THEN
print_log('Set Proxy...'||v_proxy);
utl_http.set_proxy(v_proxy);
END IF;
--URL encoding needed for Parameters to be passed otherwise results in bad syntax - online tools add this by default.
IF p_method = 'PUT' THEN
v_put_url := SUBSTR(v_put_url,1,INSTR(v_put_url,'?')) || UTL_URL.ESCAPE(SUBSTR(v_put_url,INSTR(v_put_url,'?')+1));
END IF;
fnd_file.put_line(apps.fnd_file.log,'PUT event Set URL...'||p_url);
v_http_request := utl_http.begin_request(v_put_url,p_method);
IF v_application = 'COUPA' then
UTL_HTTP.set_header(v_http_request, 'X-COUPA-API-KEY',v_key);
END IF;
--Setting length mandatory, this will length of the CLOB, if null add 0
UTL_HTTP.set_header(v_http_request, 'Content-Length', NVL(length(p_content),0));
UTL_HTTP.set_header(v_http_request, 'ACCEPT', 'application/json');
utl_http.set_header(v_http_request, 'Content-Type', 'application/json;charset=UTF-8');
utl_http.set_body_charset(v_http_request,'UTF-8'); --Jira#23773
--Since CLOB can be more that 32K in length, take 32000 bytes , write to body and loop till end of CLOB.
fnd_file.put_line(apps.fnd_file.log,'PUT event - loop thru CLOB and write to url body...');
WHILE v_offset < v_length
LOOP
DBMS_LOB.READ (p_content, v_data_bytes,v_offset, v_chunk_string);
UTL_HTTP.write_text(v_http_request, v_chunk_string );
v_offset := v_offset + v_data_bytes;
END LOOP;
v_http_response := UTL_HTTP.get_response(v_http_request);
--201 is record created
--200 is success query or update
--4xx client error
--5xx server error
IF substr(v_http_response.status_code,1,1) = '2' then
-- in (200,201) then
print_log('PUT event - status Success...');
BEGIN
LOOP
UTL_HTTP.read_text(v_http_response, v_text,LENGTH(v_text));
v_clob := v_clob || v_text ;
END LOOP;
EXCEPTION WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(v_http_response);
END;
UTL_HTTP.end_response(v_http_response);
ELSE IF substr(v_http_response.status_code,1,1) = '5' THEN
print_log('PUT event server error - return status - '|| v_http_response.status_code );
Raise server_exception;
ELSE IF substr(v_http_response.status_code,1,1) = '4' THEN
print_log('PUT event error - return status - '|| v_http_response.status_code );
BEGIN
LOOP
UTL_HTTP.read_text(v_http_response, v_text,LENGTH(v_text));
print_log(v_text);
v_clob := v_clob || v_text ;
END LOOP;
EXCEPTION WHEN utl_http.end_of_body THEN
utl_http.end_response(v_http_response);
print_log('Into put content end of body exception');
END;
UTL_HTTP.end_response(v_http_response);
If v_clob = '{"errors":{"request":["This approval is not the currently pending approval."]}}' then
Null; --this can be fyi candidate for exceptions recording
print_log('Into approval condition');
Else
print_log('Raise bad put ');
RAISE bad_put; --do we need this for 400 code or it should be only for 500 (server error)
-- or mark entire batch as processed so next run fectches new recordsfrom Coupa or other apps.
End if;
END IF; --code = 4
END IF; --code = 5
END IF; --code is 2
EXCEPTION WHEN bad_put THEN
print_log( 'Error while putting data on Coupa, Error is - '|| v_clob);
--insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob);
--insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob,p_end_point_id,v_http_response.status_code);
--CSS-22047
/*Below IF condition on v_clob is added under CSS-23276*/
IF v_clob like '%Parent %have a different Lookup than parent%' THEN
g_parent_err_flag :='Y';
ELSE
insert_audit (
'PUT CONTENT',
'Error while putting data on Coupa, Error is - ' || v_clob,
p_end_point_id,
v_http_response.status_code,
g_reference_details);
--CSS-22047 enhancement
END IF;
--CSS-22047 enhancement
UTL_HTTP.end_response(v_http_response);
WHEN server_exception THEN
print_log( 'Error while putting data on Coupa, Error is - '|| v_clob);
--insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob);
--insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob,p_end_point_id,v_http_response.status_code);
--CSS-22047
insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| v_clob,p_end_point_id,v_http_response.status_code,g_reference_details);
--CSS-22047 enhancement
UTL_HTTP.end_response(v_http_response);
WHEN OTHERS THEN
print_log( 'Error while putting data on Coupa, Error is - '|| SQLERRM);
print_log('PUT event Exception - '||SQLERRM);
--insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM);
--insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM,p_end_point_id,v_http_response.status_code);
--Added If below for Jira#25131
IF SQLERRM LIKE '%ORA-29024%' --AND g_reference_details IS NULL
THEN
insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM,p_end_point_id,v_http_response.status_code,'Certificate validation failure');
ELSE
--CSS-22047
insert_audit('PUT CONTENT', 'Error while putting data on Coupa, Error is - '|| SQLERRM,p_end_point_id,v_http_response.status_code,g_reference_details);
--CSS-22047 enhancement
END IF;
UTL_HTTP.end_response(v_http_response);
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CALLING PROCEDURE CODE:
PROCEDURE coupa_supplier_remits_api (errbuf OUT VARCHAR2, retcode OUT NUMBER, p_last_run_date IN VARCHAR2) as
l_clob CLOB;
l_cursor SYS_REFCURSOR;
v_id NUMBER;
v_json_id NUMBER;
v_json_id1 NUMBER;
v_url VARCHAR2(500);
v_method VARCHAR2(5);
v_flag NUMBER:=0; -- Flag to check if there is any records eligible for processing
v_flag_create NUMBER:=0; -- Flag to count how many remit-to-address got created
v_flag_update NUMBER:=0; -- Flag to count how many remit-to-address got updated
v_last_run_date DATE;
l_vendor_temp ap_suppliers.segment1%TYPE; --Added for CSS-24813
CURSOR cur_ready_adrs (p_last_run DATE,p_current_run DATE) IS --Modified, version 5.1
WITH bank_data
AS (SELECT ieba.bank_account_num bank_account, iepa.supplier_site_id
FROM ar.hz_parties hp,
iby.iby_ext_bank_accounts ieba,
iby.iby_external_payees_all iepa,
iby.iby_pmt_instr_uses_all ipiua
WHERE 1 = 1
AND hp.party_id = ieba.bank_id
AND ipiua.instrument_id = ieba.ext_bank_account_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ipiua.instrument_type = 'BANKACCOUNT'
AND ipiua.payment_flow = 'DISBURSEMENTS'
AND ipiua.order_of_preference = 1)
SELECT apsa.vendor_site_id
name,
/*(CASE
WHEN TRUNC (apsa.inactive_date) < TRUNC (SYSDATE) THEN 'FALSE' -- KBS 20-Feb changed to FALSE
WHEN TRUNC (apsa.inactive_date) >= TRUNC (SYSDATE) THEN 'TRUE' -- KBS 20-Feb changed to TRUE
ELSE 'TRUE'
END)
active, */
DECODE(apsa.inactive_date,NULL,'TRUE','FALSE') active,
DECODE (aps1.vendor_type_lookup_code,
'EMPLOYEE', '',
apsa.address_line1)
line1,
DECODE (aps1.vendor_type_lookup_code,
'EMPLOYEE', '',
apsa.address_line2)
line2,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.city) city,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.state)
state,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.zip)
postal_code,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.country)
country_code,
'RemitToAddress' address_type,
aps1.segment1 supplier_number,
aps1.vendor_id,
aps1.vendor_name,
apsa.vendor_site_code
||' '|| NVL (SUBSTR (bank_temp.bank_account, -4), 'XXXX')
remit_to_code,
apsa.attribute5 JPMC_check_priority,
/*(SELECT req.registration_number
FROM ap_supplier_sites_all apsa1,
Zx_party_tax_profile taxp,
zx_registrations req
WHERE apsa1.vendor_site_id = apsa.vendor_site_id
AND apsa1.party_site_id = taxp.party_id
AND taxp.party_tax_profile_id = req.party_tax_profile_id
AND NVL (TRUNC (req.effective_to), SYSDATE + 1) >=
TRUNC (SYSDATE)) */ --CSS-21554 Manjit's suggestion
apsa.vat_registration_num GST_ID,
'N' Supplier_update --22833
FROM ap.ap_supplier_sites_all apsa
,bank_data bank_temp
,ap.ap_suppliers aps1
WHERE 1 = 1
AND aps1.attribute8 <= fnd_profile.value('BLK_COUPA_PHASE')--'1','2' --ver5.2
AND apsa.vendor_site_id = bank_temp.supplier_site_id(+)
AND apsa.vendor_id = aps1.vendor_id
-- KBS 19-MAR-2018 added line below to stop records trying to go to coupa with no email address
AND aps1.ATTRIBUTE6||aps1.attribute7 != 'email'
AND TRUNC (NVL(apsa.inactive_date,SYSDATE)) >= TRUNC (SYSDATE)
--AND aps1.last_update_date BETWEEN p_last_run AND SYSDATE;
--AND apsa.last_update_date BETWEEN NVL(v_last_run_date, p_last_run) AND SYSDATE; --we need to check last update date on supplier site not supplier
AND apsa.last_update_date BETWEEN NVL(v_last_run_date, p_last_run) AND p_current_run --Modified, version 5.1
--AND aps1.segment1 = '55760';
UNION --Added Union as part of JIRA#22833
SELECT apsa.vendor_site_id
name,
DECODE(apsa.inactive_date,NULL,'TRUE','FALSE') active,
DECODE (aps1.vendor_type_lookup_code,
'EMPLOYEE', '',
apsa.address_line1)
line1,
DECODE (aps1.vendor_type_lookup_code,
'EMPLOYEE', '',
apsa.address_line2)
line2,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.city) city,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.state)
state,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.zip)
postal_code,
DECODE (aps1.vendor_type_lookup_code, 'EMPLOYEE', '', apsa.country)
country_code,
'RemitToAddress' address_type,
aps1.segment1 supplier_number,
aps1.vendor_id,
aps1.vendor_name,
apsa.vendor_site_code
||' '|| NVL (SUBSTR (bank_temp.bank_account, -4), 'XXXX')
remit_to_code,
apsa.attribute5 JPMC_check_priority,
apsa.vat_registration_num GST_ID,
'Y' Supplier_update
FROM ap.ap_supplier_sites_all apsa
,bank_data bank_temp
,ap.ap_suppliers aps1
WHERE 1 = 1
AND aps1.attribute8 <= fnd_profile.value('BLK_COUPA_PHASE')
AND apsa.vendor_site_id = bank_temp.supplier_site_id(+)
AND apsa.vendor_id = aps1.vendor_id
AND aps1.ATTRIBUTE6||aps1.attribute7 != 'email'
AND TRUNC (NVL(apsa.inactive_date,SYSDATE)) >= TRUNC (SYSDATE)
AND aps1.last_update_date BETWEEN NVL(v_last_run_date, p_last_run) AND p_current_run
AND apsa.last_update_date < NVL(v_last_run_date, p_last_run)
ORDER BY supplier_number; --Added for CSS-24813;
/*Above ORDER BY should not be changed. This 'order by' is used to reduce the number of calls made to
-- coupa supplier api while updating the supplier site information. For any chance order by clause is changed, then requirement under jira#84813 must be tested. */
BEGIN
v_last_run_date := fnd_date.canonical_to_date (p_last_run_date);
print_log ('***************************************************************************');
print_log ('********************Beginning Coupa remit-to-address api.******************');
print_log(' ');
--print_log ('Last run date of this program is: ' ||get_last_run_date(fnd_global.conc_request_id));
print_log ('Last run date of this program is: ' ||to_char(get_last_run_date(fnd_global.conc_request_id),'DD-MON-YYYY HH24:MI:SS'));
print_log ('Current run date of this program is: ' ||to_char(get_current_run_date(fnd_global.conc_request_id),'DD-MON-YYYY HH24:MI:SS')); --22833
--more friendly log
print_log(' ');
print_log(' ');
--------------------------------------------------------------------------------------
--- NEW SECTION
--------------------------------------------------------------------------------------
-- First go and collect the data and store it as a JSON into the BLK_API_CONTENT table
FOR cur_rec IN cur_ready_adrs (get_last_run_date(fnd_global.conc_request_id),get_current_run_date(fnd_global.conc_request_id)) LOOP --Modified, version 5.1
IF NVL(l_vendor_temp,'xyz') <> cur_rec.supplier_number THEN --Added for CSS-24813. Check if currecnt supplier is different from last processed supplier
v_json_id := NULL; -- Added for CSS-24813, re-initialization of variable only when supplier number is different than last fetched supplier number
g_reference_details:= 'supplier#'||cur_rec.supplier_number; --CSS-22047 report enhancement
v_flag := v_flag+1; --increment the flag
v_url := get_url('COUPA','COUPA_SUPPLIER','GET');
print_log('Started processing of supplier: '||'"'|| cur_rec.vendor_name||'"');
print_log('Supplier number: '||'"'|| cur_rec.supplier_number||'"');
print_log(' ');
print_log(' ');
print_log('Checking if supplier exists on Coupa or not.');
print_log('Calling procedure GET_CONTENT to check supplier on Coupa');
print_log(' ');
BEGIN
get_content(13, v_url||cur_rec.supplier_number,v_id);
EXCEPTION WHEN OTHERS THEN
print_log('Error while calling get_content from copa_supplier_remit_api proc while checking supplier, Error is - '|| SQLERRM);
-- /*KSS*/ -- below IF-ELSE to decide status of prog and whether to continue or exit
/*IF g_retcode = 2 THEN
retcode:= 2;
EXIT;
ELSE
retcode:=1;
END IF;*/
END;
print_log('ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure: '||v_id);
print_log('Processing of GET_CONTENT completed');
print_log(' ');
BEGIN
SELECT JSON_VALUE(data_content, '$.id')
INTO v_json_id
FROM BOLINF.BLK_API_CONTENT
WHERE id = v_id;
EXCEPTION WHEN OTHERS THEN
print_log('Error while fetching json value ,ID is - '|| v_id || '.Error is-'|| SQLERRM);
v_json_id := NULL;
END;
IF v_json_id IS NULL THEN
print_log ('Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.' );
print_log(' ');
--retcode:= 1; --warning
insert_audit('GET CONTENT', 'Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.',13,NULL,g_reference_details);
--CSS-22047 enhancement
ELSE
print_log ('Supplier-'||cur_rec.vendor_name|| ' exists on Coupa, now remit-to-address can be uploaded.' ); --Added for CSS-24813.
END IF;
END IF;--- Added for CSS-24813
--Below IF-ELSE block has been shifted above, CSS-24813
/*IF v_json_id IS NULL THEN
print_log ('Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.' );
print_log(' ');
--retcode:= 1; --warning
insert_audit('GET CONTENT', 'Supplier-'||cur_rec.vendor_name|| ' does not exist on Coupa, remit-to-address can not be uploaded. First upload the supplier.',13,NULL,g_reference_details);
--CSS-22047 enhancement
ELSE
*/
IF v_json_id IS NOT NULL THEN -- Added IF condition for CSS-24813
g_reference_details:= 'remit address:'||cur_rec.name; --CSS-22047 report enhancement
print_log('Now checking if address for the supplier- '||cur_rec.vendor_name||' exists on Coupa or not.');
v_url := get_url('COUPA','COUPA_SUPPLIER','POST'); --no need to make a new table entry for supplier remit-to-address
BEGIN
get_content(13, v_url||'/'||v_json_id||'/'||'addresses?name='||cur_rec.name,v_id);
EXCEPTION WHEN OTHERS THEN
print_log('Error while calling get_content from copa_supplier_remit_api proc while checking remit-address, Error is - '|| SQLERRM);
-- /*KSS*/ -- below IF-ELSE to decide status of prog and whether to continue or exit
/*IF g_retcode = 2 THEN
retcode:= 2;
EXIT;
ELSE
retcode:=1;
END IF;*/
END;
print_log('ID of bolinf.BLK_API_CONTENT returned from GET_CONTENT procedure for remit-to-address: '||v_id);
print_log(' ');
BEGIN
SELECT JSON_VALUE(data_content, '$.id')
INTO v_json_id1
FROM BOLINF.BLK_API_CONTENT
WHERE id = v_id;
EXCEPTION WHEN OTHERS THEN
print_log('Error while fetching json value for remit-to-address ,ID is - '|| v_id || '.Error is-'|| SQLERRM);
v_json_id1 := NULL;
END;
v_method := NULL; --22833
IF v_json_id1 IS NULL THEN
v_method := 'POST';
v_url := v_url||'/'||v_json_id||'/addresses';
print_log ('Address for supplier-'||cur_rec.vendor_name||' with site-id-'||cur_rec.name||' does not already exist on Coupa. Proceeding to create');
ELSE
IF cur_rec.supplier_update = 'N' --If Condition Added as part of JIRA#22833. To Ensure if supplier is updated again, it should not try to update the Supplier Site
THEN
v_url := v_url||'/'||v_json_id||'/addresses/'||v_json_id1;
v_method := 'PUT';
print_log ('Address for supplier-'||cur_rec.vendor_name||' with site-id-'||cur_rec.name||' already exists on Coupa. Proceeding to update');
END IF;
END IF;
IF v_method IS NULL -- If Block Added as part of JIRA#22833
THEN
CONTINUE;
END IF;
OPEN l_cursor FOR
SELECT cur_rec.active "active",
cur_rec.city "city",
CURSOR(SELECT cur_rec.country_code "code"
FROM dual) "country",
cur_rec.name "name",
cur_rec.postal_code "postal-code",
cur_rec.remit_to_code "remit-to-code",
cur_rec.state "state",
cur_rec.line1 "street1",
cur_rec.line2 "street2",
/*CURSOR(SELECT cur_rec.country_code "code"
FROM dual) "vat-country",*/
cur_rec.jpmc_check_priority "custom-field-2",
cur_rec.gst_id "custom-field-1"
FROM dual;
APEX_JSON.initialize_clob_output;
APEX_JSON.write( l_cursor);
l_clob := REGEXP_REPLACE(REGEXP_REPLACE(APEX_JSON.get_clob_output,'^\[',NULL),'\]$',NULL); -- Find a better way to do this
APEX_JSON.free_output;
-- Call proc to insert into the BLK_API_CONTENT
print_log(' ');
print_log('Calling procedure INSERT_CONTENT to save the data in custom table for future reference');
BEGIN
insert_content (13,l_clob);
EXCEPTION WHEN OTHERS THEN
print_log('Error while call insert_content, Error is - '|| SQLERRM);
--retcode:=1;
END;
print_log(' ');
print_log('Processing of INSERT_CONTENT completed');
print_log(' ');
/* IF v_method = 'PUT' THEN
print_log('Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa');
ELSE -- v_method = 'POST'
print_log('Calling procedure PUT_CONTENT to create new remit-to-address on Coupa');
END IF; */ --Commented as part of JIRA#22833, Same is handled below.
BEGIN
print_log('l_clob : '||l_clob);
put_content(13,v_url, l_clob, v_method);
IF v_method = 'PUT' THEN
print_log('Calling procedure PUT_CONTENT to update existing remit-to-address on Coupa'); --22833
v_flag_update:= v_flag_update+1;
ELSE --v_method = 'POST'
print_log('Calling procedure PUT_CONTENT to create new remit-to-address on Coupa'); --22833
v_flag_create:= v_flag_create+1;
END IF;
EXCEPTION WHEN OTHERS THEN
print_log('Error while calling put_content from copa_supplier_remit_api proc, Error is - '|| SQLERRM);
-- /*KSS*/ -- below IF-ELSE to decide status of prog and whether to continue or exit
/*IF g_retcode = 2 THEN
retcode:= 2;
EXIT;
ELSE
retcode:=1;
END IF;*/
END;
print_log(' ');
print_log('Processing of PUT_CONTENT completed');
UPDATE BOLINF.BLK_API_CONTENT
SET status = 'S'
WHERE request_id = fnd_global.conc_request_id;
print_log(' ');
print_log(' ');
print_log('Program processed one vendor site-'|| cur_rec.name);
print_log ('***************************************************************************');
print_log(' ');
print_log(' ');
END IF;
l_vendor_temp := cur_rec.supplier_number; --Added for CSS-24813, so that we can compare current fetch of cursor with last fetched value at the start of loop
END LOOP;
IF v_flag = 0 THEN
print_log('There was no remit-to-address to process. Kindly re-check if any address was created/updated in system.' );
ELSE
print_log ('Total remit-to-address got processed: '||v_flag);
print_log ('Total new remit-to-address created on Coupa: '||v_flag_create);
print_log ('Total existing remit-to-address updated on Coupa:- '||v_flag_update);
print_log ('Total remit-to-address got rejected: '||(v_flag-v_flag_create-v_flag_update));
END IF;
Hope this helps.
print_log(' ');
print_log(' ');
print_log('**********************End Coupa remit-to-address api. **********************');
print_log ('***************************************************************************');
END;
+---------------------------------------------------------------------------+
