Reset application user password in R12 in backend

set serveroutput on;
declare
  v_user_name    varchar2(30) := upper('user_name');
  v_new_password varchar2(30) := 'welcome';
  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;

Add Sysadmin responsibility in Backend R12

-- Check the attached responsiblity

Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name='USERNAME'

add sysadmin responsibility in backend

BEGIN
  fnd_user_pkg.addresp('user_name',
                       'SYSADMIN',
                       'SYSTEM_ADMINISTRATOR',
                       'STANDARD',
                       'Add Responsibility to USER using pl/sql',
                       SYSDATE,
                       SYSDATE + 100);
  COMMIT;
  DBMS_OUTPUT.put_line('Responsibility Added Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(' Responsibility is not added due to ' || SQLCODE ||
                         SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
END;

All Supplier Invoice amounts converted into USD

Select /*+ PARRALLEL(ai,16) */ invoice_num,invoice_amount,
 DECODE(ai.invoice_currency_code,
             'USD',
             ai.invoice_amount,
             NVL(gdr.conversion_rate, 1) * ai.invoice_amount) USD_Amnt
    from apps.ap_invoices_all       ai,
         apps.gl_daily_rates        gdr
   where  gdr.from_currency = ai.invoice_currency_code
     AND gdr.to_currency = 'USD'
     AND gdr.conversion_type = ai.exchange_rate_type
     and trunc(gdr.conversion_date) = trunc(ai.invoice_date)
     and =1
   

Supplier Payment Method

The field IBY_EXTERNAL_PAYEES_ALL.DEFAULT_PAYMENT_METHOD_CODE is updated only when a payee is created. This is automatically updated and there is no UI for this field.
If a user updates the default payment method at supplier or supplier site level, then the payment method is updated in the IBY_EXT_PARTY_PMT_MTHDS.PAYMENT_METHOD_CODE.

Column IBY_EXTERNAL_PAYEES_ALL.DEFAULT_PAYMENT_METHOD_CODE is never used for any kind of defaulting. It just serves as a place holder for AP supplied
parameters and populated only at the time of creating payee, remains dormant thereafter.

Actual default payment method is evaluated from active records of table IBY_EXT_PARTY_PMT_MTHDS where it is filtered by the primary_flag (should be 'Y') and by the external payee id of the Site,AddressOU,Address,Supplier
level payee (in the order of preference).

Supplier Header level bank account in R12

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type" 
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER" 
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
and IEP.SUPPLIER_SITE_ID is null

Supplier Site Level Bank Accounts in R12

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type"
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER"
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.HZ_PARTY_SITES SITE_SUPP
, apps.AP_SUPPLIER_SITES_ALL ASS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH,
apps.HR_OPERATING_UNITS hou
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND APS.VENDOR_TYPE_LOOKUP_CODE NOT   IN ('EMPLOYEE', 'INTERCO','CONTRACTOR')
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
and hou.organization_id=ass.org_id
AND hou.name in ('AU_604990')

Payment details query in R12 EBS

 SELECT ipa.payee_name                   supplier_name,
             ipa.payee_supplier_number        supplier_number,
             ipa.payee_supplier_site_name     supplier_site,
             hou.name                         ou_name,
             ipa.payment_process_request_name ppr_name,
             aisc.creation_date               creation_date,
             ipa.payment_status               payment_status,
             ipa.payment_amount               payment_amount_batch,
             ipa.payment_currency_code        payment_currency_code_batch,
             ipa.payment_method_code          payment_method,
             ipa.payment_reference_number     payment_refernce_number,
             ipa.payment_profile_sys_name     payment_process_profile,
             ipa.int_bank_account_name        int_bank_account_name,
             ipa.int_bank_account_number      int_bank_account_number,
             apt.template_name                template_name,
             idpa.calling_app_doc_ref_number  invoice_number,
             idpa.payment_amount              payment_amount,
             idpa.payment_currency_code       payment_currency,
             ipa.payment_date                 payment_date,
             ipa.ext_bank_name                supplier_bank_name,
             ipa.ext_bank_number              supplier_bank_number,
             ipa.ext_eft_swift_code           eft_number,
             ipa.ext_bank_branch_name         bank_branch_name,
             ipa.ext_branch_number            bank_branch_number,
             ipa.ext_bank_account_name        bank_account_name,
             ipa.ext_bank_account_number      bank_acount_number,
             ipa.ext_bank_account_type        account_type,
             ipa.external_bank_account_id,
             isppb.OUTBOUND_PMT_FILE_DIRECTORY, isppb.OUTBOUND_PMT_FILE_EXTENSION,
             isppb.OUTBOUND_PMT_FILE_PREFIX,isppb.POSITIVE_PAY_FILE_DIRECTORY,isppb.POSITIVE_PAY_FILE_PREFIX
       FROM iby_payments_all              ipa,
             hr_operating_units            hou,
             ap_inv_selection_criteria_all aisc,
             ap_payment_templates          apt,
             iby_docs_payable_all          idpa,
             IBY_SYS_PMT_PROFILES_b        isppb,
             IBY_SYS_PMT_PROFILES_tl       ispp
       WHERE 1 = 1
         AND ipa.org_id = hou.organization_id
         AND ipa.payment_process_request_name = aisc.checkrun_name
         AND aisc.template_id = apt.template_id(+)
         AND ipa.payment_id = idpa.payment_id
         AND ipa.Payment_status IN ('FORMATTED', 'ISSUED')
         --AND ipa.paper_document_number IS NULL
         AND ipa.process_type = 'STANDARD'
         AND TRUNC(ipa.creation_date) >= TO_DATE('18/12/2015', 'DD/MM/YYYY')
         AND ispp.language='US'
         AND  isppb.SYSTEM_PROFILE_CODE=ispp.SYSTEM_PROFILE_CODE
         and ipa.payment_profile_sys_name=ispp.SYSTEM_PROFILE_NAME
       ORDER BY aisc.creation_date