Concurrent programs attached with Data Definitions in R12

SELECT DISTINCT fat.application_name application_name,
                fat.application_short_name appl_short_name,
                fcpt.user_concurrent_program_name concurrent_program_name,
                fcp.concurrent_program_name concurrent_program_short_name,
                fcp.output_file_type output_type,
                fe.executable_name executable_name,
                flv.meaning execution_method_type,
                fat1.application_name executable_application_name,
                fat1.application_short_name executable_appl_short_name,
                xddt.data_source_name data_definition_name,
                xtb.template_code template_code,
                xtb.template_type_code template_type,
                xl.file_content_type file_content, xl.file_name rtf_file_name,
                (SELECT user_name
                   FROM fnd_user
                  WHERE user_id = xtb.created_by) creation_user
           FROM fnd_concurrent_programs_tl fcpt,
                fnd_concurrent_programs fcp,
                fnd_executables fe,
                fnd_application_vl fat,
                fnd_application_vl fat1,
                fnd_lookup_values flv,
                xdo_ds_definitions_tl xddt,
                xdo_templates_b xtb,
                xdo_lobs xl
          WHERE fcp.concurrent_program_id = fcpt.concurrent_program_id
            AND fat.application_id = fcpt.application_id
            AND fcp.executable_id = fe.executable_id
            AND fcp.executable_application_id = fe.application_id
            AND fat1.application_id = fe.application_id
            AND xddt.data_source_code(+) = fcp.concurrent_program_name
            AND flv.lookup_type(+) = 'CP_EXECUTION_METHOD_CODE'
            AND flv.LANGUAGE = 'US'
            AND flv.lookup_code(+) = fe.execution_method_code
            AND xtb.data_source_code(+) = xddt.data_source_code
            AND xl.lob_code(+) = xtb.template_code
            AND xl.lob_type(+) = 'TEMPLATE_SOURCE'
            AND flv.enabled_flag = 'Y'
            --AND fcp.output_file_type=’XML’
            AND fcp.enabled_flag = 'Y'
            AND xddt.LANGUAGE = 'US'
            AND fcpt.LANGUAGE = 'US'
            AND NVL (flv.end_date_active, SYSDATE) >= SYSDATE
            AND fcpt.user_concurrent_program_name =
                   'Russia Payment Transaction Acknowledgment Report'
                                                  --:p_concurrent_program_name

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')