Friday, 5 August 2016

How to rectify FUN_INTER_PAY_NOT_VALID error in create accounting

During month close an error with invoices/receipts/transactions   "FUN_INTER_PAY_NOT_VALID"  is a blocker for the close. When this error occurs for AR, follow below steps to identify and fix this issue.

Step 1: Execute below query and identify the transaction that is causing the issue.

SELECT xlt.source_id_int_1 ID, xlt.security_id_int_1 org_id, xe.event_id,
xe.event_type_code, xe.event_status_code, xe.process_status_code,
FROM apps.xla_events xe,
xla.xla_transaction_entities xlt,
apps.xla_accounting_errors xae
WHERE xe.entity_id = xlt.entity_id
AND xe.application_id = xlt.application_id
AND xlt.source_application_id = 222 -- Change Value to 200 for AP
--AND xlt.ledger_id = 12010
--AND xe.event_date between to_date('01-APR-2016','DD-MON-YYYY') and to_date('30-APR-2016','DD-MON-YYYY')

AND xe.process_status_code <> 'P'
AND xe.event_status_code <> 'P'
AND xe.event_id = xae.event_id
ORDER BY xe.event_date, xe.event_id;

Note: ID is the customer_trx_id if the event is invoice, ID is cash_receipt_id if the event is receipt.
Step 2: Query the transaction and find the GL strings that is being used. Make sure the code combination is active, and the individual values are active and enabled. 
Step3: Go to Accounting Setups > Accounting Options > SOB > Intercompany Accounts
Identify the Balancing segment value and replace the transaction balancing segment. Then make sure the GL string (replaced) and values are active and enabled.

Step4: Enable the code combination or values that are end dated/inactive. Now you will be able to account the transaction.

Step5:  Run Submit Accounting [ Or create Accounting in case of AP]. Make sure the create accounting program completes successfully. Also run the query mentioned in Step1 and make sure the records is not fetching.

Query to find list of suppliers who has attachments

Often times you might have to find a list of suppliers who has a specific attachment. For example, if you need to find list of suppliers who has attached with a W9 form, then in that case use the below query.

SELECT asup.vendor_name supplier_name, asup.segment1 supplier_number,
       asup.enabled_flag, asup.creation_date,
       asup.vendor_type_lookup_code lookup_code,asup.num_1099,
       asup.tax_reporting_name, assa.vendor_site_code, assa.inactive_date,
       assa.address_line1, assa.address_line2,, assa.state,,,assa.org_id, fdt.description, fdt.title
  FROM apps.ap_suppliers asup,
       apps.ap_supplier_sites_all assa,
       apps.fnd_attached_documents fad,
       apps.fnd_documents_tl fdt
 WHERE asup.vendor_id = assa.vendor_id
   AND TO_CHAR ( asup.vendor_id ) = fad.pk1_value
   AND fad.document_id = fdt.document_id
   AND fad.entity_name = 'PO_VENDORS'
   AND fdt.title IN   ( 'W9', 'W8', 'W-9', 'W-8', 'w9', 'w8', 'w-9', 'w-8' )
   --AND asup.vendor_id = 408801