Payment Process Profile Setup Queries

Payment Process profiles are manually setup by your Functional Consultants from the Payables's Administration Form.

To verify if the setups are correctly performed, you can use the below queries

-- XML Template Verification Script
select * from xdo_lobs
order by creation_date desc


--Check Payment Format Setup
 SELECT * FROM apps.IBY_FORMATS_B
  where format_code in ('<NAME_OF_FORMAT>') 
 
 
-- Check Payment Format Validation Setup
select b.assignment_entity_id, a.* from apps.IBY_VALIDATION_VALUES a , IBY_VAL_ASSIGNMENTS b
where a.validation_Assignment_id = b.validation_Assignment_id
and assignment_entity_id in ('<NAME_OF_FORMAT>')  
 order by 1,2, val_param_varchar2_value
  

--Check Payment Transmission Configuration Setup
SELECT *
  FROM iby_transmit_values transmitvalueseo,
       iby_transmit_parameters_vl PARAMETERS,
       iby_transmit_configs_vl transmit_config,
       fnd_lookups
 WHERE transmitvalueseo.transmit_parameter_code =
                                            PARAMETERS.transmit_parameter_code
   AND transmitvalueseo.transmit_configuration_id =
                                     transmit_config.transmit_configuration_id
   AND transmit_config.transmit_protocol_code =
                                             PARAMETERS.transmit_protocol_code
   AND NVL (PARAMETERS.dynamic_flag, 'N') <> 'Y'
   AND lookup_type = 'IBY_PARAMETER_TYPES'
   AND lookup_code = transmit_parameter_type
      and transmit_configuration_name like '%<NAME>%'
   order by transmitvalueseo.creation_date desc, display_order

                 
--Check Payment Process Profile Setup           
    SELECT *FROM apps.IBY_SYS_PMT_PROFILES_VL where system_profile_code in ('<NAME_OF_PROFILE>')  

--Payment Process Profile Usage Rules
   select * from IBY_APPLICABLE_PMT_PROFS
   where system_profile_code in ('<NAME_OF_PROFILE>')  
   order by 1 desc


 --Check Bank Account Setup
     select * from apps.ce_bank_Accounts
                order by last_update_Date desc 

 --Check Payment Document Setup
 select * from ce_payment_documents
  where format_code in ('<NAME_OF_FORMAT>')  
 order by creation_DAte desc

 --Business Payment Template Query
    select * from apps.AP_PAYMENT_TEMPLATES
 order by creation_date desc

How to Update Profile Option Value from Backend

 

There is a Standard API fnd_profile.SAVE which can be used to update the Profile Option Value

Below Example is for setting profile option value at User Level.

You can also create a cursor and loop the statement to update multiple level values.

DECLARE

v_profile_option fnd_profile_options.PROFILE_OPTION_NAME%type;

v_value fnd_profile_option_values.PROFILE_OPTION_VALUE%type;

v_level VARCHAR2(4):='USER';

v_user_id NUMBER

BEGIN

v_update :=
         fnd_profile.SAVE (v_profile_option, -- Profile name you are setting
                           v_value, -- Profile value you are setting
                           v_level, -- Level that you're setting at: 'SITE','APPL','RESP','USER', etc.
                          v_user_id ---- Level value that you are setting at, e.g. user id for 'USER' level.
                          );
      COMMIT;
 

END;

 Default Tax ID on Invoice Distribution DFF (India Localization)

 

Oracle says:

1st priority goes to manually attached TDS tax at invoice distribution level 
2nd Priority goes to  TDS tax default from  supplier additional information  site level 
3rd Priority goes to  TDS tax default  form supplier additional information at null site level

Note : If you want to deduct the TDS Invoice as per tax rate passed at TDS  Threshold setup in that case you have to remove the default tax from supplier additional information at site level as well as null site level and keep it only default section code .

Check your threshold setup which means at 

 AP Manager -> AP Tax Setups -> Setup -> Tax Setup -> India - Threshold Setup Form

For almost every Vendor Type - Section Code combination, there is a default Tax decided with the Rate.

To call this Tax as default value on DFF for a Supplier, on the AP Manager -> AP Tax Setups -> Supplier Information form, processor just populates the Default Section code and leaves the Default Tax value.

Only in case wherein user wants to override the tax coming from threshold, processor additionally populates the Default Tax value in the India AP Manager -> R12 AP Tax Setups -> Supplier Information form.

For following Supplier/Sites the value is defaulted in Distribution DFF

Query: 

SELECT   aps.vendor_id, aps.vendor_name, apsa.vendor_site_code,

         a.section_type, a.section_code, a.tds_vendor_type_lookup_code,

         jtax.tax_name, jtax.tax_id, tta.tax_id, jtax1.tax_name,

         nvl2(jtax.tax_name,  jtax.tax_name,jtax1.tax_name) final_tax

    FROM apps.jai_ap_tds_vendor_hdrs a,

         ap_suppliers aps,

         apps.jai_cmn_taxes_all jtax,

         apps.ap_supplier_sites_all apsa,

         jai_ap_tds_thhold_hdrs th,

         apps.jai_ap_tds_thhold_types tty,

         jai_ap_tds_thhold_taxes tta,

         apps.jai_cmn_taxes_all jtax1

   WHERE 1=1--(a.tax_id IS NOT NULL OR a.section_code IS NOT NULL)

     AND a.vendor_id = aps.vendor_id

     AND a.tax_id = jtax.tax_id(+)

     AND a.vendor_site_id = apsa.vendor_site_id(+)

     AND th.vendor_type_lookup_code = a.tds_vendor_type_lookup_code

     AND th.section_code = a.section_code

     AND th.threshold_hdr_id = tty.threshold_hdr_id

     AND tty.threshold_type_id = tta.threshold_type_id

     AND tta.tax_id = jtax1.tax_id(+)

     AND NVL (tty.TO_DATE, SYSDATE) >= SYSDATE

ORDER BY 2

Query to Check Expense Line Distribution Mismatch Issue

alter session set current_schema=apps;

DECLARE

TYPE analysis_type IS TABLE OF VARCHAR2(1000);

analysis_list analysis_type := analysis_type();

p_report_header_id NUMBER:= :report_header_id;

CURSOR lines_cursor IS
    SELECT aerl.report_line_id, aerl.amount, aerl.itemization_parent_id,
        aerl.currency_code, aerl.distribution_line_number, aerl.web_parameter_id,
        aerl.daily_amount, aerl.receipt_currency_amount, aep.prompt, aerl.credit_card_trx_id, aerl.allocation_split_code, aerl.org_id
    FROM ap_expense_report_lines_all aerl,
        ap_expense_report_params_all aep
    WHERE aerl.report_header_id = p_report_header_id
    AND aerl.web_parameter_id = aep.parameter_id;

CURSOR dists_cursor IS
    SELECT report_distribution_id, report_line_id, amount, org_id, code_combination_id,
        project_id, task_id, preparer_modified_flag
    FROM ap_exp_report_dists_all
    WHERE report_header_id = p_report_header_id;

l_header_total            ap_expense_report_headers_all.total%TYPE;
l_header_org            ap_expense_report_headers_all.org_id%TYPE;
l_expense_status_code        ap_expense_report_headers_all.expense_status_code%TYPE;
l_workflow_approved_flag    ap_expense_report_headers_all.workflow_approved_flag%TYPE;
l_amt_due_employee        ap_expense_report_headers_all.amt_due_employee%TYPE;
l_amt_due_ccard_company        ap_expense_report_headers_all.amt_due_ccard_company%TYPE;
l_reject_code            ap_expense_report_headers_all.reject_code%TYPE;
l_line_id1            ap_expense_report_lines_all.report_line_id%TYPE;
l_line_id2            ap_expense_report_lines_all.report_line_id%TYPE;
l_line_id3            ap_expense_report_lines_all.report_line_id%TYPE;
l_analysis_count        INTEGER := 0;
l_item_count            NUMBER := 0;
l_item_amount            NUMBER := 0;
l_dist_count            NUMBER := 0;
l_dist_amount            NUMBER := 0;
l_line_total            NUMBER := 0;
l_dist_total            NUMBER := 0;
l_debug_msg            VARCHAR2(1000);

BEGIN
    DBMS_OUTPUT.PUT_LINE('*********************Expense Report Data Analysis*******************************');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('****************************************IMPORTANT********************************************************');
    DBMS_OUTPUT.PUT_LINE('THIS REPORT IS FOR INFOMATION PURPOSE ONLY, NO ACTION SHOULD BE TAKEN ON THE DATA BASED ON THIS REPORT.  ');
    DBMS_OUTPUT.PUT_LINE('     Please run the following sqls and provide the data to development.                                  ');
    DBMS_OUTPUT.PUT_LINE('     1) select * from ap_expense_report_headers_all where report_header_id = '||p_report_header_id||'    ');
    DBMS_OUTPUT.PUT_LINE('     2) select * from ap_expense_report_lines_all where report_header_id = '||p_report_header_id||'      ');
    DBMS_OUTPUT.PUT_LINE('     3) select * from ap_exp_report_dists_all where report_header_id = '||p_report_header_id||'          ');
    DBMS_OUTPUT.PUT_LINE('     4) select * from ap_credit_card_trxns_all where report_header_id = '||p_report_header_id||'         ');
    DBMS_OUTPUT.PUT_LINE('*********************************************************************************************************');
    DBMS_OUTPUT.PUT_LINE('');
    l_analysis_count := l_analysis_count + 1;
    analysis_list.extend;
    analysis_list(l_analysis_count) := '==Analysis==';

    l_debug_msg := 'Fetching Header Data';

    SELECT total, reject_code,
        expense_status_code,
        workflow_approved_flag,
        amt_due_employee,
        amt_due_ccard_company,
        org_id
    INTO l_header_total, l_reject_code, l_expense_status_code,
        l_workflow_approved_flag, l_amt_due_employee,
        l_amt_due_ccard_company, l_header_org
    FROM ap_expense_report_headers_all
    WHERE report_header_id = p_report_header_id;
   
    l_debug_msg := 'Printing Header Data';

    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('==Header Data==');
    DBMS_OUTPUT.PUT_LINE('Report Header Id : *'||p_report_header_id||'*');
    DBMS_OUTPUT.PUT_LINE('Header ORG : *'||l_header_org||'*');
    DBMS_OUTPUT.PUT_LINE('Total : *'||l_header_total||'*');
    DBMS_OUTPUT.PUT_LINE('Expense Status Code : *'||l_expense_status_code||'*');
    DBMS_OUTPUT.PUT_LINE('Reject Code : *'||l_reject_code||'*');
    DBMS_OUTPUT.PUT_LINE('Workflow Approved Flag : *'||l_workflow_approved_flag||'*');
    DBMS_OUTPUT.PUT_LINE('Amount Due Employee : *'||l_amt_due_employee||'*');
    DBMS_OUTPUT.PUT_LINE('Amount Due Card Company : *'||l_amt_due_ccard_company||'*');
 
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('==Line Data==');
   
    l_debug_msg := 'Looping Line Data';
    FOR line_rec in lines_cursor
    LOOP
        l_debug_msg := 'Printing Line Data';
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('Report Line Id : *'||line_rec.report_line_id||'*');
        DBMS_OUTPUT.PUT_LINE('Report Line Org : *'||line_rec.org_id||'*');
        DBMS_OUTPUT.PUT_LINE('Expense Type : *'||line_rec.prompt||'*');
        DBMS_OUTPUT.PUT_LINE('Line Amount : *'||line_rec.amount||'*');
        DBMS_OUTPUT.PUT_LINE('Line Currency : *'||line_rec.currency_code||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Line Number : *'||line_rec.distribution_line_number||'*');
        DBMS_OUTPUT.PUT_LINE('Daily Amount : *'||line_rec.daily_amount||'*');
        DBMS_OUTPUT.PUT_LINE('Receipt Amount : *'||line_rec.receipt_currency_amount||'*');
        DBMS_OUTPUT.PUT_LINE('Credit Card Trx Id : *'||line_rec.credit_card_trx_id||'*');
        DBMS_OUTPUT.PUT_LINE('Allocation Split Code : *'||line_rec.allocation_split_code||'*');
        DBMS_OUTPUT.PUT_LINE('Itmization Parent Id : *'||line_rec.itemization_parent_id||'*');       
       
        IF (line_rec.distribution_line_number = line_rec.report_line_id) THEN
            DBMS_OUTPUT.PUT_LINE('The line was potentially itemized in Audit ');   
        END IF;
        IF (line_rec.amount IS NULL) THEN
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '' || line_rec.report_line_id || ': Line Amount is null';
        END IF;

        IF(line_rec.itemization_parent_id IS NOT NULL AND line_rec.itemization_parent_id = -1) THEN
            l_item_count := 0;
            l_item_amount := 0;
            SELECT COUNT(*), SUM(amount) INTO l_item_count, l_item_amount
            FROM ap_expense_report_lines_all WHERE itemization_parent_id = line_rec.report_line_id;

            DBMS_OUTPUT.PUT_LINE('Total Child Lines *'||l_item_count||'*');       

            IF (l_item_count = 0) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Itemized Line with No Child Lines';
            END IF;

            IF (nvl(l_item_amount, -9999) > nvl(line_rec.amount, -9999)) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Itemized total more than parent';
            END IF;

            IF (nvl(l_item_amount, -9999) < nvl(line_rec.amount, -9999)) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Personal Itemized lines potentially exist';
            END IF;
        END IF;
       
        l_dist_count := 0;
        l_dist_amount := 0;
       
        IF (line_rec.itemization_parent_id IS NULL OR line_rec.itemization_parent_id <> -1) THEN
            l_debug_msg := 'Fetching Dist count and totals for line ' || line_rec.report_line_id;

            SELECT COUNT(*) INTO l_dist_count FROM ap_exp_report_dists_all WHERE report_line_id = line_rec.report_line_id;
       
            IF (l_dist_count > 0) THEN           
                SELECT SUM(AMOUNT) INTO l_dist_amount FROM ap_exp_report_dists_all WHERE report_line_id = line_rec.report_line_id;
            END IF;

            IF (l_dist_count = 0) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': No Distributions';
            END IF;

            IF (line_rec.allocation_split_code IS NULL AND l_dist_count > 1) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Multiple distributions exists for line, Allocation Split Code is NULL';
            END IF;

            DBMS_OUTPUT.PUT_LINE('Total Distributions *'||l_dist_count||'*');
            DBMS_OUTPUT.PUT_LINE('Distribution Total *'||l_dist_amount||'*');
           
            IF (nvl(l_dist_amount, -9999) <> nvl(line_rec.amount, -9999)) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Line and Distribution amounts do not match';
            END IF;
        END IF;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('==Dist Data==');

    FOR dists_rec IN dists_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('Report Dist Id : *'||dists_rec.report_distribution_id||'*');
        DBMS_OUTPUT.PUT_LINE('Report Line Id : *'||dists_rec.report_line_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Org Id : *'||dists_rec.org_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Amount : *'||dists_rec.amount||'*');
        DBMS_OUTPUT.PUT_LINE('Dist CCID : *'||dists_rec.code_combination_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Project Id : *'||dists_rec.project_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Task Id : *'||dists_rec.task_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Preparer Modified : *'||dists_rec.preparer_modified_flag||'*');
       
        IF (dists_rec.amount IS NULL) THEN
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := 'Line ' || dists_rec.report_line_id || ':Dist: ' || dists_rec.report_distribution_id || ': Distribution Amount is Null';
        END IF;

    END LOOP;
   
    l_debug_msg := 'Fetching line total for final processing';
    SELECT SUM(amount) INTO l_line_total FROM ap_expense_report_lines_all WHERE report_header_id = p_report_header_id and (itemization_parent_id IS NULL OR itemization_parent_id <> -1);

    l_debug_msg := 'Fetching dist total for final processing';
    SELECT SUM(amount) INTO l_dist_total FROM ap_exp_report_dists_all WHERE report_header_id = p_report_header_id;

    IF (nvl(l_header_total, -9999) <> nvl(l_line_total, -9999)) THEN
        l_analysis_count := l_analysis_count + 1;
        analysis_list.extend;
        analysis_list(l_analysis_count) := 'Header and Line Totals do not match';
        BEGIN
            SELECT report_line_id INTO l_line_id1 FROM ap_expense_report_lines_all WHERE ROUND(amount) = ROUND(l_line_total - l_header_total);
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '1. Line ID ' || l_line_id1 || ' might be problemetic';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
        BEGIN
            SELECT report_line_id INTO l_line_id2 FROM ap_expense_report_lines_all WHERE amount = (l_line_total - l_header_total);
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '2. Line ID ' || l_line_id2 || ' might be problemetic';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
        BEGIN
            SELECT report_line_id INTO l_line_id3 FROM ap_expense_report_lines_all WHERE amount = (l_header_total - l_line_total);
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '3. Line ID ' || l_line_id3 || ' might be problemetic';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END IF;

    IF (nvl(l_header_total, -9999) <> nvl(l_dist_total, -9999)) THEN
        l_analysis_count := l_analysis_count + 1;
        analysis_list.extend;
        analysis_list(l_analysis_count) := 'Header and Distribution Totals do not match';
    END IF;

    IF (nvl(l_line_total, -9999) <> nvl(l_dist_total, -9999)) THEN
        l_analysis_count := l_analysis_count + 1;
        analysis_list.extend;
        analysis_list(l_analysis_count) := 'Line and Distribution Totals do not match';
    END IF;
   
    l_debug_msg := 'Printing Final';
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('Header Total :*'||l_header_total||'*');
    DBMS_OUTPUT.PUT_LINE('Line Total :*'||l_line_total||'*');
    DBMS_OUTPUT.PUT_LINE('Dist Total :*'||l_dist_total||'*');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    FOR i IN 1 .. analysis_list.count LOOP
        dbms_output.put_line(analysis_list(i));
    END LOOP;
   

    IF (analysis_list.count = 1) THEN
        DBMS_OUTPUT.PUT_LINE('Data appears to be valid');
    END IF;

    DBMS_OUTPUT.PUT_LINE('');
   
   
    DBMS_OUTPUT.PUT_LINE('*********************************************************************************');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('An Exception Occurred while ' || l_debug_msg);   
        DBMS_OUTPUT.PUT_LINE('Exception:  ' || SQLERRM);
END;

FND_GLOBAL.APPS_INITIALIZE Error

When you are trying to use the FND_GLOBAL.APPS_INITIALIZE procedure from any other schema other than APPS, you may run into following issues.
Issue:

1.      ORA-20001: Oracle error -942: ORA-00942: table or view does not exist has been detected in fnd_global.set_nls

2.      ORA-20001: Oracle error -1031: ORA-01031: insufficient privileges has been detected in fnd_global.set_nls

Cause:
The root-cause is the application of a newer Release of the FND_GLOBAL package (body), which is delivered by Patch 6241631 - '11i.ATG_PF.H.RUP7'. It includes a code change causing the FND_LANGUAGES table  to be referenced. Under normal circumstances this does not cause any problem since it is an existing table and the APPS account has access to it.

However in this case a custom SCHEMA is used (for example: FIPM). This has in generic only access to a small set of Objects required for Integration. This Object does not have a SYNONYM (or other access) to the FND_LANGUAGES table, so when this is referenced in the package the ORA-942 error is raised.

Solution: To avoid these errors perform the following operation:
1.
CREATE SYNONYM <SCHEMA>.FND_LANGUAGES FOR APPS.FND_LANGUAGES;

2.
GRANT SELECT ON <SCHEMA>.FND_LANGUAGES TO <SCHEMA>;

SQL Query to find Concurrent Request Details

--------------------------Query Starts--------------------------
alter session set current_schema=apps;

SELECT
  FCR.REQUEST_ID,
  FR.RESPONSIBILITY_NAME,
  FCP.CONCURRENT_PROGRAM_NAME,
  FCPT.USER_CONCURRENT_PROGRAM_NAME,
  FCR.ARGUMENT_TEXT,
  FCR.REQUEST_DATE,
  FCR.PHASE_CODE,
  FCR.STATUS_CODE,
  FCR.ACTUAL_START_DATE,
  FCR.ACTUAL_COMPLETION_DATE,
 ceil((FCR.ACTUAL_COMPLETION_DATE- FCR.ACTUAL_START_DATE)*24*60) "Time(Min)",
  FCR.COMPLETION_TEXT,
  FU.USER_NAME,
  FCR.LOGFILE_NAME,
  FCR.OUTFILE_NAME,
  FCR.ORACLE_SESSION_ID
FROM
  FND_CONCURRENT_REQUESTS FCR,
  FND_CONCURRENT_PROGRAMS FCP,
  FND_CONCURRENT_PROGRAMS_TL FCPT,
  FND_RESPONSIBILITY_TL FR,
  FND_USER FU
WHERE
  1=1
  --and REQUEST_ID=25045446
AND FCR.CONCURRENT_PROGRAM_ID=FCP.CONCURRENT_PROGRAM_ID
AND FR.RESPONSIBILITY_ID     = FCR.RESPONSIBILITY_ID
AND FCP.CONCURRENT_PROGRAM_ID=FCPT.CONCURRENT_PROGRAM_ID
AND FCR.REQUESTED_BY         = FU.USER_ID
--AND CONCURRENT_PROGRAM_NAME = 'XLGLCURVAL'
--  AND USER_CONCURRENT_PROGRAM_NAME like  '%Invoice%Validation%'
--  and fr.RESPONSIBILITY_KEY='RESP_KEY'
--  and  FU.USER_NAME= 'USERNAME'
--  AND FCR.REQUEST_ID=30235254
  --AND FCR.REQUEST_DATE > SYSDATE-1
--AND FCR.phase_CODE='P'
and fcr.status_code ='R'
--and fcr.argument_text like '%2041,%'
ORDER BY
  request_id DESC;
--------------------------Query Ends--------------------------

SQL Query to Reset Password

SQL Query to Reset Password from Backend

---------------------Query Starts---------------------

declare
v_user_name varchar2(30):=upper(&USERNAME);
v_new_password varchar2(30):='welcome123';
v_status boolean;
begin
v_status:= fnd_user_pkg.ChangePassword (
username => v_user_name,
newpassword => v_new_password
);
if v_status =true then
dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
commit;
else
DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
rollback;
END if;

end;
---------------------Query Ends---------------------