Form Personalization query

SELECT   ffcr.SEQUENCE Seq, ffcr.description Description,

         DECODE (ffcr.rule_type,

                 'F', 'Form',

                 'A', 'Function',

                 'Other'

                ) Levels,

         ffcr.enabled Enabled, ffcr.trigger_event Trigger_Event,

         ffcr.trigger_object Trigger_Object, ffcr.condition Condition,

         DECODE (ffcr.fire_in_enter_query,

                 'Y', 'Both',

                 'N', 'Not in Enter-Query Mode',

                 'O', 'Only in Enter-Query Mode',

                 'Other'

                ) Processing_Mode

    FROM apps.fnd_form_custom_rules ffcr

   WHERE ffcr.function_name = function name

     AND ffcr.form_name = 'form name'

ORDER BY ffcr.SEQUENCE;

Find Responsibility for a Request

select frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    From fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    where frt.responsibility_id = fr.responsibility_id
    and frg.request_group_id = fr.request_group_id
    and frgu.request_group_id = frg.request_group_id
    and fcpt.concurrent_program_id = frgu.request_unit_id
    and frt.language = USERENV('LANG')
    and fcpt.language = USERENV('LANG')
    and fcpt.user_concurrent_program_name = :conc_prg_name
    order by 1,2,3,4

SQL to check Pay Groups

SELECT a.lookup_type "Type",
       a.meaning "Meaning",
       b.APPLICATION_NAME "Application",
       a.DESCRIPTION "Description",
       DECODE (a.CUSTOMIZATION_LEVEL, 'U', 'User') "AccessLevel",
       C.LOOKUP_CODE "code",
       c.meaning "Meaning",
       c.DESCRIPTION "Description",
       c.tag "Tag",
       TO_CHAR (c.start_date_active, 'DD-MON-YYYY') "EffectiveDatefrom",
       TO_CHAR (c.end_date_active, 'DD-MON-YYYY') "EffectiveDateto",
       DECODE (c.enabled_flag,  'Y', 'Yes',  'N', 'No') "Enabled"
  FROM apps.FND_LOOKUP_TYPES_VL a,
       apps.fnd_application_tl b,
       apps.FND_LOOKUP_VALUES_VL c
 WHERE     a.application_id = b.application_id
       AND A.LOOKUP_TYPE = c.LOOKUP_TYPE
       AND a.lookup_type IN ('PAY GROUP');

SQL to check Purchasing Period Status

SELECT
(select name from v$database) DBNAME,
(select sysdate from dual) Extract_Date,
gled.name "Ledger",
         hou.name "Operating Unit",
         GPS.SHOW_STATUS "Period Status",
         GPS.PERIOD_NUM "Period Number",
         GPS.PERIOD_YEAR "Fiscal Year",
         GPS.PERIOD_NAME "Period Name",
         TO_CHAR (GPS.START_DATE, 'DD-MON-YYYY') "Begin Date",
         TO_CHAR (GPS.END_DATE, 'DD-MON-YYYY') "End Date"
    FROM apps.GL_PERIOD_STATUSES_V GPS,
         apps.hr_operating_units hou,
         apps.gl_ledgers gled
   WHERE     application_id = 200
         AND gps.PERIOD_YEAR = 2016
         AND hou.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
         AND gled.ledger_id = HOU.SET_OF_BOOKS_ID
     ORDER BY hou.name

SQL to check Payables Option in R12

SELECT a.name "Operating Unit",
(select name from v$database) DBNAME,
(select sysdate from dual) Extract_Date,
         DECODE (WHEN_TO_ACCOUNT_PMT, 'ALWAYS', 'Yes') "When Payment is Issued",
         DECODE (WHEN_TO_ACCOUNT_GAIN_LOSS, 'ALWAYS', 'Yes')
            "Account for Gain/Lost",
         DECODE (FUTURE_DATED_PMT_ACCT_SOURCE, 'BANK ACCOUNT', 'Yes')
            "From Payment Document",
         LIABILITY_POST_LOOKUP_CODE "None",
         DISCOUNT_DISTRIBUTION_METHOD "Discount SystemAccount",
         PRORATE_INT_INV_ACROSS_DISTS "Interest SystemAccount",
         BUILD_PREPAYMENT_ACCOUNTS_FLAG "From Supplier Site",
         DECODE (MULTI_CURRENCY_FLAG,  'Y', 'Yes',  'N', 'No')
            "Use Multiple Currencies",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = b.GAIN_CODE_COMBINATION_ID)    "Realized Gain",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = b.LOSS_CODE_COMBINATION_ID)
            "Realized Loss",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = b.ROUNDING_ERROR_CCID)  "Rounding",
         DECODE (CONFIRM_DATE_AS_INV_NUM_FLAG,  'Y', 'Yes',  'N', 'No')      "Confirm Date as Invoice Number",
         DECODE (APPROVALS_OPTION,  'Y', 'Yes',  'N', 'No')   "Allow Online Validation",
         DECODE (GL_DATE_FROM_RECEIPT_FLAG, 'I', 'Yes') "Invoice Date",
         DECODE (CREATE_EMPLOYEE_VENDOR_FLAG,  'Y', 'Yes',  'N', 'No')
            "CreateEmployeeasSupplier",
         DECODE (CREATE_AWT_DISTS_TYPE, 'NEVER', 'Yes')
            "Apply Withholding TaxNever",
         DECODE (CREATE_AWT_INVOICES_TYPE, 'NEVER', 'Yes')
            "CreateWithholdingInvoiceNever",
         WITHHOLDING_DATE_BASIS "Invoice Date",
         TERMS_DATE_BASIS "Term Date Basis"
    FROM apps.hr_operating_units a, apps.AP_SYSTEM_PARAMETERS_ALL b
   WHERE     a.ORGANIZATION_ID = b.org_id
         ORDER BY a.name

SQL to check Financial Options in R12

SELECT
(select name from v$database) DBNAME,
(select sysdate from dual) Extract_Date,
hou.NAME "Operating Unit",
         fsr.future_period_limit "Future Period Limit",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = fsr.ACCTS_PAY_CODE_COMBINATION_ID)
            "Liability",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = fsr.PREPAY_CODE_COMBINATION_ID)
            "Prepayment",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = fsr.DISC_TAKEN_CODE_COMBINATION_ID)
            "Discount Taken",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = fsr.RATE_VAR_GAIN_CCID)
            "PO Rate Variance Gain",
         (SELECT    segment1
                 || '-'
                 || segment2
                 || '-'
                 || segment3
                 || '-'
                 || segment4
                 || '-'
                 || segment5
                 || '-'
                 || segment6
            FROM apps.gl_code_combinations
           WHERE code_combination_id = fsr.RATE_VAR_LOSS_CCID)
            "PO Rate Variance Loss",
         ood.organization_code || '-' || ood.organization_name
            "Inventory Organization",
         hbg.BUSINESS_GROUP_NAME "Business Group",
         DECODE (fsr.EXPENSE_CHECK_ADDRESS_FLAG, 'H', 'Home')
            "Expense Check Address",
         DECODE (fsr.USE_POSITIONS_FLAG, 'Y', 'Yes','N','No') "Use Approval Hierarchies",
         hbg.EMP_NUMBER_GENERATION_METHOD "Method"
    FROM apps.FINANCIALS_SYSTEM_PARAMS_ALL fsr,
         apps.hr_operating_units hou,
         apps.HRFV_BUSINESS_GROUPS hbg,
         apps.ORG_ORGANIZATION_DEFINITIONS ood
   WHERE     hbg.business_group_id = fsr.business_group_id
         AND HOU.BUSINESS_GROUP_ID = HBG.BUSINESS_GROUP_ID
         AND fsr.INVENTORY_ORGANIZATION_ID = ood.ORGANIZATION_ID
         AND fsr.SET_OF_BOOKS_ID = hou.SET_OF_BOOKS_ID
         AND fsr.org_id = hou.organization_id
         AND hou.name IN
ORDER BY hou.name

Oracle AR Autoinvoice Errors - Troubleshooting

1.  A credit memo is attempting to overapply a specific transaction line, but overapplication is not allowed.

Reason - This issue is due to multiple  times application of credit memo to invoice. 

Fix Steps - 

Please navigate to 

Control > Autoinvoice > Interface Lines  (Responsibility : AR User /  or Super user ) 

1. Query for the Interface Line using attribute as Interface_line_id = *******

2. Check for the field Reference_Line_id  (Please add it using show field if you can not able to see it in application ) . and delete the value then save the changes. 

3. After removing the reference_line_id , Please re run Autinvoice Master Program