Hi All,
Are there any build in way to supply "dynamic" credentials means current logged in user credentials to PLSQL API call COde ?
We are trying to create an APEX Application extending the Oracle SAAS Fusion cloud (ERP), where each user are connected using their SAAS Credentials or SSO (Azure AD). Is it possible to pass the current logged in user credentials normal apex auth or SSO to APEX_WEB_SERVICE PL/SQL API code. Please help me on this.
Please let me know the solution if the above request is possible.
**Sample API PLSQL CODE: ** Currently passing single account credentials via Substitution string named CLOUD_UNAME,CLOUD_PWD.
Code:
DECLARE
l_resp_body CLOB;
l_request_id VARCHAR2(4000) ;
l_var VARCHAR2(100);
l_var2 VARCHAR2(100);
l_var3 VARCHAR2(100);
l_var4 VARCHAR2(100);
l_var5 VARCHAR2(100);
l_var6 VARCHAR2(100);
l_var7 VARCHAR2(100);
l_var8 VARCHAR2(100);
l_led NUMBER;
L_BU VARCHAR2(1000);
v_led VARCHAR2(4000);
v_BU VARCHAR2(4000);
CURSOR C_LED IS
SELECT DISTINCT ABU.PRIMARYLEDGERID,ABU.BUSINESSUNITID FROM ALL_BUSINESS_UNITS ABU WHERE
ABU.PRIMARYLEDGERID in (SELECT distinct xal.ledgerid from xx_all_ledgers xal where 1=1 and xal.ledgerid not in (SELECT DISTINCT LEDGER_SET_ID FROM XX_XML_LEDGER_SETS ) AND (XAL.LEDGERID=:P31_PRIMARY_LEDGER) OR (xal.ledgerid in (SELECT DISTINCT LEDGER_ID FROM XX_XML_LEDGER_SETS WHERE LEDGER_SET_ID = :P31_PRIMARY_LEDGER )))
AND ABU.businessunitid in (select t.column_value as buid from table(Apex_String.split(RTRIM(LTRIM(:P3_BU, ':'), ':'), ':')) t);
-- SELECT DISTINCT XAL.LEDGERid,ABU.BUSINESSUNITID FROM xx_all_ledgers XAL,ALL_BUSINESS_UNITS ABU WHERE XAL.LEDGERID = ABU.PRIMARYLEDGERID AND (XAL.ledgerid=:P31_PRIMARY_LEDGER) OR (XAL.LEDGERID IN (SELECT DISTINCT LEDGER_ID FROM XX_XML_LEDGER_SETS WHERE LEDGER_SET_ID = :P31_PRIMARY_LEDGER)) AND ABU.businessunitid in (select t.column_value as buid from table(Apex_String.split(RTRIM(LTRIM(:P3_BU, ':'), ':'), ':')) t);
-- select distinct businessunitid from ALL_BUSINESS_UNITS where businessunitid in (select t.column_value as buid from table(Apex_String.split(RTRIM(LTRIM(:P3_BU, ':'), ':'), ':')) t);
BEGIN
select nvl(:P31_INV_NUM,'#NULL') into l_var from dual;
Select nvl(:P31_TO_INV_DATE,'#NULL') into l_var2 from dual;
Select nvl(:P31_FROM_INV_DATE,'#NULL') into l_var3 from dual;
Select nvl(:P31_SUPOPAR,'#NULL') into l_var4 from dual;
select nvl(:P31_PAY_GRP,'#NULL') into l_var5 from dual;
select nvl(:P31_MAX_INV_COUNT,'#NULL' ) into l_var6 from dual;
select nvl(:P31_INV_GRP,'#NULL') into l_var7 from dual;
select nvl(:P31_NUM_PARALLEL,'#NULL') into l_var8 from dual;
delete from xx_requests where program_name='Validate Payables Invoices';
For i in c_led loop
-- FOR j IN C_BU LOOP
L_LED:=NVL(I.PRIMARYLEDGERID,:P31_PRIMARY_LEDGER);
L_BU:=I.BUSINESSUNITID;
SELECT NAME INTO V_LED FROM XX_ALL_LEDGERS WHERE LEDGERID=I.PRIMARYLEDGERID;
SELECT BUSINESSUNITNAME INTO V_BU FROM ALL_BUSINESS_UNITS WHERE BUSINESSUNITID=I.BUSINESSUNITID;
apex_web_service.g_request_headers(1).name := 'Content-Type'; apex_web_service.g_request_headers(1).value := 'application/json';
l_resp_body := apex_web_service.make_rest_request(
p_url => 'https://'||:CLOUD_SERVER||'/fscmRestApi/resources/11.13.18.05/erpintegrations',
p_http_method => 'POST',
p_username => :CLOUD_UNAME, -- substituion string declared in app defination
p_password => :CLOUD_PWD,
P_body => '{"OperationName":"submitESSJobRequest","JobPackageName":"/oracle/apps/ess/financials/payables/invoices/transactions/","JobDefName":"APXAPRVL","ESSParameters": "'||L_BU||','||:P31_OPTIONS||','||l_var3||','||l_var2||','||l_var4||','||l_var5||','||l_var||','||:P31_ENTERED_BY||','||l_var7||','||L_LED||','||l_var6||','||l_var8||'"}'
);
APEX_JSON.parse(l_resp_body);
l_request_id:= APEX_JSON.get_number(p_path => 'ReqstId');
INSERT INTO xx_requests VALUES(l_request_id,'Validate Payables Invoices','NULL',V_LED,V_BU);
-- To get latest Status
end loop;
END;
