Online Create Accounting Program Stucks in Running and Pending Phase

R12.1:Concurrent Manager Hangs For All Processes (Doc ID 1503898.1)

SYMPTOMS

Concurrent Manager is hanging while executed create accounting program for individual invoices. All programs/ concurrent requests got stuck.

STEPS
--------
The issue can be reproduced at will with the following steps:
1. Invoice created
2. validate invoices
3. Run create accounting program for each invoice separately


OPP Before

Standard Manager WorkShifts Before

SOLUTION

Increase the Standard Manager workshifts Processes to 20 or 25

To change the number of concurrent processes:

1. From the 'System Administrator' responsibility
2. Concurrent : Manager -> Define
3. Query up 'Standard Manager' for Manager
4. Click Work Shifts
5. Change the number of 'Processes' as desired and save
6. Bounce the Concurrent Managers 

Bounce the concurrent manager.

OPP After

Standard Manager WorkShifts After

Opening DFF on Supplier Site Level

Opening DFF to Record Bank Information on Supplier Site

Click on Descriptive Flex Fields

AP Important Tables with Joins

http://aporaclepayables.blogspot.com/2016/01/ap-important-tables-with-joins.html

Table Name	Unique Key of the Table/Viewused in Incremental load
AP_BATCHES_ALL	BATCH_ID
AP_CHECKS_ALL	CHECK_ID
AP_HOLDS_V	HLD_HOLD_ID
AP_INVOICE_DISTRIBUTION_ALL	ID_INVOICE_DISTRIBUTION_ID
AP_INVOICE_LINES_ALL	IL_INVOICE_LINE_ID
AP_INVOICE_PAYMENTS_ALL	IPAY_INVOICE_PAYMENT_ID
AP_INVOICES_ALL	INVOICE_ID
AP_PAYMENT_SCHEDULES_ALL	PS_PAYMENT_SCHEDULES_ID
AP_SUPPLIERS	VEN_VENDOR_ID
AP_TERMS_LINES, AP_TERMS_TL	TERM_ID
Based on View xx_sav_vendor_bank_details
GL_CODE_COMNINATIONS	CODE_COMBINATION_ID
PO_DISTRIBUTIONS_ALL
PO_HEADERS_ALL	PO_HEADER_ID
PO_LINE_LOCATIONS_ALL
PO_LINES_ALL PO_LINE_LOCATIONS_ALLPO_DISTRIBUTIONS_ALL	POL_LINE_LOC_DIST_UNQ_ID (Incremental load is done using revision number instead of Last Update Date)
Vendor Site bank details, View used by  XX_AP_VENDOR_BANK_DETAILS
PO_REQUISITION_HEADERS_ALL	RH_REQUISITION_HEADER_ID
PO_REQUISITION_LINES_ALL	RL_REQUISITION_LINE_ID
RCV_SHIPMENT_HEADERS	RCH_SHIPMENT_HEADER_ID
RCV_SHIPMENT_LINES	RCL_SHIPMENT_LINE_ID

Period Close Process Oracle Application

http://aporaclepayables.blogspot.com/2016/01/period-close-process-oracle-application.html

Automatic Offset Method

http://aporaclepayables.blogspot.com/2016/01/automatic-offset-method.html

Legal Entity is not coming in Bank Account Owner

http://aporaclepayables.blogspot.com/2016/01/legal-entity-is-not-coming-in-bank.html

Adding Legal Entities in User management module for Making it enable in Bank Account Owner  in creating Bank

Web Address: For Help

http://www.project.eu.com/e-business-suite/r12-ce-unable-to-view-legal-entities-lovs-in-the-bank-account-definition-page/

Follow the below steps. There is one more important point along with these steps. The Location attached to the LE organization must be same as what you will define in user management. :-
Before Defining Bank Accounts, you must complete the setup of CE UMX Security functions under User Management Responsibility for cash management responsibility . After doing this setup only the Legal Entity will be visible to you while defining banks.
CE Bank Account Security setup wizard
1) Login as System Administrator
2) Select User Management responsibility
3) Select Roles and Role Inheritance menu function
4) Create (or query an existing) Role.
5) Click Security Wizards button and Run Wizard - CE UMX Security wizard
6) Click Add Legal Entities button, select your LEs and check Use, Maintenance and Bank Account
Transfers to reflect the activities for this Role.
check-box. Apply
8) Assign this role to your user (using the Users tab) 

Payables Interface

http://aporaclepayables.blogspot.com/2016/01/payables-interface.html

insert into ap_invoices_interface
(INVOICE_ID,  
INVOICE_NUM, 
INVOICE_TYPE_LOOKUP_CODE, 
INVOICE_DATE,   
VENDOR_ID,
VENDOR_SITE_CODE,
INVOICE_AMOUNT, 
INVOICE_CURRENCY_CODE, 
TERMS_NAME,
SOURCE,
GL_DATE,  
ACCTS_PAY_CODE_COMBINATION_ID,  
TERMS_DATE,   
operating_unit)

values (AP_INVOICES_INTERFACE_S.NEXTVAL,'08','STANDARD','25-JUL-15','16563',
'?',8000,'PKR','Immediate','LEGACY SYSTEM',   
'25-JUL-15',102769,'25-JUL-15','TEST_1');
commit;
-------------------------------------------------------------------------------

INSERT
INTO ap_invoice_lines_interface
  (invoice_id,
    invoice_line_id,
    line_number,
    line_type_lookup_code,
    amount,
    dist_code_combination_id
  )
  VALUES
  (AP_INVOICES_INTERFACE_S.CURRVAL
   ,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL
   ,1        --<some unique_number>,
   ,'ITEM'   --<ITEM>/<FREIGHT>/<TAX>
   ,8000     --<line_amount> sum of line_amount should always be equal to invoice_amount,
   ,102769
--'<account code>'
  );
COMMIT;

---------------------------------------------------------------------------------------

the invoice will be made in Open Interface Invoices
------------------------------------------------------

Run Payables import interface Program to Import invoices from interface to Invoices Workbench/Invoices.

Payment Clearing Query

http://aporaclepayables.blogspot.com/2016/01/payment-clearing-query.html

How to know the User who has cleared the Payment.

SELECT *

  FROM AP_PAYMENT_HISTORY_ALL

 WHERE check_id IN (SELECT distinct check_id

                      FROM AP_INVOICE_PAYMENTS_ALL

                     WHERE invoice_id = '?')

 ORDER BY accounting_event_id asc

--------------------------------------------------------------------------------------------------------------------------

--create or replace view Clearing_User as
select  * /*aca.DOC_SEQUENCE_VALUE APV_NUMBER,aca.CHECK_NUMBER Document_Number, aca.VENDOR_NAME,aca.VENDOR_SITE_CODE,
aca.CHECK_DATE,aca.CLEARED_AMOUNT, aca.CLEARED_DATE, APHA.TRANSACTION_TYPE, aca.LAST_UPDATE_DATE CHK_LAST_UPDATE, FU.USER_NAME CLEARING_USER_NAME
,APHA.LAST_UPDATE_DATE CLR_LAST_UPDATE,ACA.BANK_ACCOUNT_NAME*/
FROM AP_PAYMENT_HISTORY_ALL APHA, FND_USER FU, ap_checks_all aca 
 where aca.CHECK_ID=APHA.CHECK_ID 
 and APHA.CHECK_ID IN (SELECT distinct check_id
                      FROM AP_INVOICE_PAYMENTS_ALL
                  WHERE invoice_id ='&P_INVOICE_ID'
            )
                     and APHA.CREATED_BY=FU.USER_ID
                     AND APHA.TRANSACTION_TYPE='PAYMENT CLEARING'
                     and aca.CHECK_DATE is not null              

--------------------------------------------------------------------------------------------------------------------------

                select * 
               from Clearing_User TT where tt.CLR_LAST_UPDATE in (select max(last_update_date) from AP_PAYMENT_HISTORY_ALL)
              --7342114

Rounding Account in Payables

http://aporaclepayables.blogspot.com/2016/01/rounding-account-in-payables.html

Payables creates journal entries in the transaction currency and your ledger currency for all invoices and payments. Payables continues to record gains and losses in the Gain/Loss Accounts you assign. When you pay invoices in a different currency than the payment currency, Payables uses the Rounding account that you define in the Currencies region of the Payables Options window to record the rounding error.

For example, suppose the french franc were still a valid NCU. Your ledger currency is FRF and you enter an invoice in 1000 FRF and enter EUR as the Payment Currency. The Payment Amount converts to 152.53917 EUR, at the fixed conversion rate of 6.555693, and rounds to 152.54 EUR. The payment, converted back to your ledger currency is 1000.0054, which rounds to 1000.01. The .01 FRF difference is recorded in the Rounding account.

The following diagram shows the accounting entries for the previously described example.

Is it Possible to Add Additional Invoice Line Types

http://aporaclepayables.blogspot.com/2016/01/is-it-possible-to-add-additional.html

SOLUTION

R11i/R12: AP: Miscellaneous Line Type Not Available for New Prepayment Invoices (Doc ID 300950.1)

Is it Possible to Add Additional Invoice Line Types (Doc ID 2038397.1)

 

R12: Payments Workbench Error: ORA-01403 No Data Found (APXPAWKB.FMB)

GOAL When attempting to create a quick check, the following error occurs when selecting a Payment Process Profile value: ORA:01403: no data found. Details:FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403 SOLUTION R11i / R12: Latest Available Version of the AP Payments Workbench form (APXPAWKB.fmb) (Doc ID 1303110.1) R12: Payments Workbench Error: ORA-01403 No Data Found (APXPAWKB.FMB) (Doc ID 1303129.1)

Invoice Partail Accounting Issue in Payables

http://aporaclepayables.blogspot.com/2016/01/invoice-partail-accounting-issue-in.html

when i was on EBS R12.0 i have faced the following issue. 
Partial Accounting Issue:= 
The Payables implemented in the customer's are on following options:- 
Withholding Tax Options (WHT (apply or deduct) on payment time) 
Accounting Generation on Clearing time only 

After using the clearing option invoice status is on partial status in 12.0.6 and in 12.1.3 
for which u have provide me a following solution. 

Solution:

Fix partial accounting issue on 12.0.6 and 12.1.3 
R12: RCA:POST ACCOUNTING NOT SETTING POSTED FLAG TO 'Y' FOR N PREPAY ( Doc ID 1382017.1 ) 
Patch.9492673:R12.AP.A ===>> fix issue on 12.0.6 
Patch.10182367:R12.AP.B ===>> Fix issue on 12.1.3 

but now i was upgraded my EBS R12.0 to 12.2.4 so please kindly provide me solution for 12.2.4. 

SOLUTION

Patch 22194532:R12.AP.C CODEFIX: PAYMENT TIME AWT POSTED FLAG=S WHEN CUSTOMER USE PMT CLEAR ONLY ACCTG 

Invoices Header+Distribution+Lines (Query)

http://aporaclepayables.blogspot.com/2016/01/invoices-headerdistributionlines-query.html

begin
MO_GLOBAL.SET_POLICY_CONTEXT('S', '?');
end;

-------------------------------------------------------

select count(*) from ap_invoices_all aia, ap_invoice_lines_all aila, ap_invoice_distributions_all aida
,ap_invoices_V aiv
where aia.invoice_id=aila.invoice_id
and aia.invoice_id=aida.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
and aiv.invoice_id=aia.invoice_id
and aiv.invoice_id=aila.invoice_id
and aiv.invoice_id=aida.invoice_id
--and aia.org_id=?
--and aida.invoice_id=10305  ---249912

Invoice Speedometer Query

http://aporaclepayables.blogspot.com/2016/01/invoice-speedometer-query.html

begin
MO_GLOBAL.SET_POLICY_CONTEXT('S', '?');
end;

--------------------------------------------------------------

SELECT distinct i.invoice_num, i.invoice_date, k.invoice_amount, k.doc_sequence_value
         ,DECODE
             (apps.ap_invoices_pkg.get_approval_status
                                                   (i.invoice_id,
                                                    i.invoice_amount,
                                                    i.payment_status_flag,
                                                    i.invoice_type_lookup_code
                                                   ),
              'NEVER APPROVED', 'Never Validated',
              'NEEDS REAPPROVAL', 'Needs Revalidation',
              'APPROVED', 'Validated',
              'CANCELLED', 'Cancelled',
              'AVAILABLE PREPAYMENT', 'Available Prepayment',
              'AVAILABLE', 'Available'
         
              ) invoice_status, i.payment_status_flag "Payment Status",        --'Y' -- Fully Paid, 'P' -- Partially Paid , 'N'-- Not Paid
               k.posting_flag "Invoice Accounting Status"               --'Y' -- Accounted, 'P' -- Partial , 'N'-- No Accounted
   FROM   ap_invoices_all i, Ap_Invoices_v k
 
   where 1=1
   and i.invoice_id=k.invoice_id
  --   and i.invoice_id=69912

Vendor Sites Active/InActive PLSQL Query & Supplier Query

http://aporaclepayables.blogspot.com/2016/01/vendor-sites-activeinactive-plsql-query.htm

select asa.vendor_name,pvsa.VENDOR_SITE_ID, pvsa.VENDOR_SITE_CODE, pvsa.ORG_ID from po_vendor_sites_all pvsa, ap_suppliers asa
where asa.vendor_id=pvsa.VENDOR_ID
and asa.vendor_name like '3%'
for update

update po_vendor_sites_all pvsa
set inactive_date=''
where pvsa.VENDOR_SITE_ID=539

select hps.party_site_id, asa.vendor_name, pvsa.VENDOR_SITE_CODE, pvsa.ORG_ID, hps.status from hz_party_sites hps, po_vendor_sites_all pvsa, ap_suppliers asa
where hps.party_site_id=pvsa.PARTY_SITE_ID
and asa.vendor_id=pvsa.VENDOR_ID
and asa.vendor_name like '7%'

update hz_party_sites hps
set hps.status='I'

where hps.party_site_id=81418


Supplier Query

select asa.vendor_id, asa.vendor_name, asa.creation_date, pvsa.VENDOR_SITE_ID, pvsa.VENDOR_SITE_CODE, pvsa.ADDRESS_LINE1
,pvsa.ORG_ID, hp.party_id, hp.party_name, hps.party_site_id, hps.party_site_name, hl.location_id, hl.address1
 from hz_party_sites hps, hz_parties hp, hz_locations hl, po_vendor_sites_all pvsa, ap_suppliers asa
where hps.party_id=hp.party_id
and hps.location_id=hl.location_id
and hps.party_site_id=pvsa.PARTY_SITE_ID
and asa.vendor_id=pvsa.VENDOR_ID

and hp.party_name = '?'

Invoice Created cannot be Validated by same user

http://aporaclepayables.blogspot.com/2016/01/invoice-created-cannot-be-validated-by.html

Invoice Created cannot be Validated by same user

Personalization

WHEN-VALIDATE-RECORD
INV_SUM_ACTIONS

Not in Enter-Query Mood

${item.inv_sum_folder.Operating_unit.Value} = '?'
AND ${item.inv_sum_folder.created_by.value}  =  fnd_global.user_id
AND ${item.inv_sum_actions.INITIATE_WFAPPROVAL.value} = 'Y'

Message Error:-
--------------

Invoice created cannot be approved by the same user.

Payment Status PLSQL QUERY

http://aporaclepayables.blogspot.com/2016/01/payment-status-plsql-query.html

begin
MO_GLOBAL.SET_POLICY_CONTEXT('S', '?');
end;

-----------------------------------------
SELECT  b.vendor_name vendor_name,
       c.vendor_site_code,
       a.invoice_num invoice_number,
       a.invoice_date,
       a.invoice_currency_code,
       a.invoice_amount,
       a.amount_paid,
       a.payment_status_flag,  -- --'Y' -- Fully Paid, 'P' -- Partially Paid , 'N'-- Not Paid
       al.posting_flag "Invoice Accounting Status",
     
       DECODE
             (apps.ap_invoices_pkg.get_approval_status
                                                   (a.invoice_id,
                                                    a.invoice_amount,
                                                    a.payment_status_flag,
                                                    a.invoice_type_lookup_code
                                                   ),
              'NEVER APPROVED', 'Never Validated',
              'NEEDS REAPPROVAL', 'Needs Revalidation',
              'APPROVED', 'Validated',
              'CANCELLED', 'Cancelled',
              'AVAILABLE PREPAYMENT', 'Available Prepayment',
              'AVAILABLE', 'Available'
         
              ) invoice_status,
              ac.status_lookup_code "Payment Status",
              asd.posting_flag    -- --'Y' -- Processed, 'P' -- Partial , 'N'-- UnProcessed
  FROM apps.ap_invoices_all a,
       apps.ap_suppliers b,
       apps.ap_supplier_sites_all c,
       apps.ap_payment_schedules_all d,
       apps.ap_invoice_payments_all ap,
       ap_checks_all ac,
       Ap_Invoices_v al,
       ap_checks_v asd
WHERE     a.vendor_id = b.vendor_id
       AND a.vendor_site_id = c.vendor_site_id
       AND b.vendor_id = c.vendor_id
       AND a.invoice_id = d.invoice_id
       AND ap.invoice_id = a.invoice_id
       AND ac.CHECK_ID = ap.CHECK_ID
       and ac.check_id=asd.check_id
       and ap.check_id=asd.check_id
       and a.invoice_id=al.invoice_id
       and d.invoice_id=al.invoice_id
     --  and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
     --  AND a.org_id =?
       and ac.check_number='?'
       --and a.invoice_id= 1234
       --AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
      -- AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')]

R12 AP There is Currently Another Supplier In The System With The Same Taxpayer ID

http://aporaclepayables.blogspot.com/2016/01/r12-ap-there-is-currently-another.html

GOAL

If you attempt to enter two suppliers with the same taxpayer ID, the second supplier receives the following message:

There is currently another supplier in the system with the same Taxpayer ID.

How can you create multiple suppliers with the same taxpayer ID?

SOLUTION

Set the system profile option ''POS: Allow Suppliers with Duplicate Taxpayer ID'' to Yes, which will allow you to save two suppliers with the same Taxpayer ID.

The profile needs to be set for the application that is linked to the responsibility from where the supplier is edited.
(In most cases it is Purchasing)
If the client has a custom application created then the profile option needs to be set for that application.