Query to get Sales Invoice(Receivables) Details in R12

SELECT RA_CUST_TRX_TYPES_ALL.TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CREATION_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.CREATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER,

       RA_CUSTOMER_TRX_LINES_ALL.SET_OF_BOOKS_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_INVOICED,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_CREDITED,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_STANDARD_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_SELLING_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT,

       RA_CUSTOMER_TRX_LINES_ALL.TAX_RATE,

       RA_CUSTOMER_TRX_LINES_ALL.UOM_CODE,

       RA_CUSTOMER_TRX_LINES_ALL.ORG_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_ALL.TRX_NUMBER,

       RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID,

       RA_CUSTOMER_TRX_ALL.TRX_DATE,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_CUSTOMER_ID,

       OE_ORDER_HEADERS_ALL.SOLD_TO_CONTACT_ID,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.TERM_ID,

       RA_CUSTOMER_TRX_ALL.TERM_DUE_DATE,

       RA_CUSTOMER_TRX_ALL.PRIMARY_SALESREP_ID,

       RA_CUSTOMER_TRX_ALL.PRINTING_ORIGINAL_DATE,

       RA_CUSTOMER_TRX_ALL.PURCHASE_ORDER,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_DATE,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE,

       RA_CUSTOMER_TRX_ALL.TERRITORY_ID,

       RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,

       RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG,

       RA_CUSTOMER_TRX_ALL.PAYING_SITE_USE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL1.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.STATUS_TRX,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE_TYPE,

       RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE,

       RA_CUST_TRX_TYPES_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID,

       OE_ORDER_HEADERS_ALL.PAYMENT_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE,

       OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE,

       OE_ORDER_LINES_ALL.ITEM_TYPE_CODE,

       OE_ORDER_LINES_ALL.SHIPMENT_NUMBER,

       OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID,

       OE_ORDER_LINES_ALL.PROJECT_ID,

       OE_ORDER_LINES_ALL.TASK_ID,

       OE_ORDER_LINES_ALL.OPTION_NUMBER,

       OE_ORDER_LINES_ALL.COMPONENT_NUMBER,

       OE_ORDER_LINES_ALL.SOURCE_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID,

       RA_CUSTOMER_TRX_ALL.LEGAL_ENTITY_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE11

  FROM RA_CUSTOMER_TRX_LINES_ALL  RA_CUSTOMER_TRX_LINES_ALL1,

       RA_CUSTOMER_TRX_LINES_ALL,

       RA_CUSTOMER_TRX_ALL,

       RA_CUST_TRX_TYPES_ALL,

       OE_ORDER_HEADERS_ALL,

       OE_ORDER_LINES_ALL

 WHERE     RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID =

           RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID

       AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = 'Y'

       AND RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID =

           RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID

       AND RA_CUSTOMER_TRX_ALL.ORG_ID = RA_CUST_TRX_TYPES_ALL.ORG_ID

       AND RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID =

           RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID(+)

       AND RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT IN

               ('ORDER ENTRY', 'INTERCOMPANY')

       AND TO_NUMBER (RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6) =

           OE_ORDER_LINES_ALL.LINE_ID(+)

       AND OE_ORDER_LINES_ALL.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID(+)

       AND RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE >=

           TO_DATE (:P_LAST_EXTRACT_DATE, 'MM/D D/YYYY HH24:MI:SS')

Query for AR Sales Representative Name in Oracle Fusion

SELECT hp.party_name sales_representative

  FROM ra_customer_trx_all rcta

      ,jtf_rs_salesreps jrs

  ,hz_parties hp

 WHERE rcta.primary_resource_salesrep_id=jrs.resource_salesrep_id

   AND jrs.resource_id=hp.party_id

Join between Sales Order(SO) and Receivables(AR) in Oracle Fusion

SELECT *

  FROM ra_customer_trx_all rcta

      ,ra_customer_trx_lines_all ra

  ,doo_fulfill_lines_all l

 WHERE ra.interface_line_attribute5 = TO_CHAR(l.fulfill_line_id)

   AND rcta.customer_trx_id = ra.customer_trx_id

   AND rcta.trx_number = '123456789';

You can use INTERFACE_LINE_ATTRIBUTE5 column in RA_CUSTOMER_TRX_LINES_ALL which stores Fulfill Line ID of Sales Order.

Invalid customer transaction identifier.APPLY : ORA-01403: no data foundORA-01403: no data found in Package AR_RECEIPT_API_PUB Procedure Apply

One of the possible reason is Invoice already paid.

You can validate with below query. 

select * from ra_customer_trx_all where trx_number = '1'

note customer_trx_id from the above query.

select * from ar_receivable_applications_all

where applied_customer_trx_id = <pass customer trx id>

note cash_receipt_id from above query 

select * from ar_cash_receipts_all

where cash_receipt_id = <pass cash receipt id>

AR Receipt Reversal Categories(REVERSAL_CATEGORY_TYPE) in Oracle APPS

SELECT lookup_code

      ,meaning

      ,description

  FROM fnd_lookup_values 

WHERE lookup_type = 'REVERSAL_CATEGORY_TYPE'

    AND LANGUAGE = USERENV('LANG')

    AND lookup_code = 'REV'

Revenue Analyst in Oracle Fusion

Revenue Analyst manages and implements revenue adjustments, revenue recognition, revenue accounting, creation and processing of revenue documents, and the inquiry of revenue documents and adjustments. 

It also manages the defining of open and closed periods and transferring entries to the GL(General Ledger).

Accounts Receivable Specialist in Oracle Fusion

Accounts Receivable Specialist manages and implements all customer related payment activities, including receiving customer payments and electronic remittance advice, performing cash handling activities, processing customer payments, and applying payments to customer accounts.

Accounts Receivable Manager in Oracle Fusion

Account Receivable Manager manages all accounts receivable activities. It also includes below activities:

• Defining policy and controls
• Establishing processes and procedures, resolving issues
• Monitoring and analyzing accounts receivable balances 
• Creating reports 
• Business Intelligence.

Why there is no entry in AR_PAYMENT_SCHEDULES_ALL even after completing the AR Transaction in Oracle APPS

• After completing AR Transaction there is no data or entry in AR_PAYMENT_SCHEDULES_ALL table. 
• Possible reason is "Open Receivables" check box is not checked for the Transaction Type while creating it. 
• If Open Receivable is set to Yes then Receivables updates Customer Balances each time you create a complete Credit Memo, Debit Memo, Chargeback, or On-Account Credit with this Transaction Type. 
• This also includes these Transactions in the Standard Aging and Collection Processes in Receivables.

Unapplied and Unresolved Receipts Register report completing with Error

 "Unapplied and Unresolved Receipts" report is the replacement for "Unapplied Receipt Register". When tried to run "Unapplied and Unresolved Receipts" report for the first time, its completed with below error.

Its not a BIDI char set

Its not a BIDI language

Error executing cursor. ORA-06502: PL/SQL: numeric or value error: NULL index table key value 

ORA-06512: at "APPS.FA_RX_PUBLISH", line 2205 

ORA-06512: at line 1

Unable to execute cursor

To resolve this issue Oracle Provided data fix as mentioned in Note "Unapplied and Unresolved Receipts Register Cannot Be Submitted (Doc ID 866105.1)"

Please make sure that you will do testing in test instance.

As per Oracle the error occurs because the concurrent_program_id is null for the reports having report_ids 492 and 493.

1. Please run the following sql

select report_id, application_id, concurrent_program_id,
last_update_date,concurrent_program_name
from fa_rx_reports
where report_id in (492,493);

2. If the query results concurrent_program_id as NULL then please run the following update statement:

Update fa_rx_reports farr
set concurrent_program_id =
(Select concurrent_program_id
From fnd_concurrent_programs
Where application_id = farr.application_id
And concurrent_program_name = farr.concurrent_program_name);

3. Then run the report.

"Unapplied Receipt Register" report completing with Error in Oracle Apps R12

While running "Unapplied Receipts Register" completing with below error in Oracle APPS R12.

REP-0004: Warning: Unable to open user preference file.

MSG-00100: BeforeReport_Procs.Set_Sort_Order

MSG-00200: Total forAGENCY: 

REP-1401: 'report_subtitleformula': Fatal PL/SQL error occurred.

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "APPS.AR_MO_GLOBAL_CACHE", line 55

ORA-06512: at "APPS.ARP_STANDARD", line 3021

REP-0069: Internal error

REP-57054: In-process job terminated:Terminated with error: m

REP-1401: MSG-00100: BeforeReport_Procs.Set_Sort_Order

MSG-00200: Total forAGENCY: 

REP-1401: 'report_subtitleformula': Fatal PL/SQL error occurred.

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "APPS.AR_MO_GLOBAL_CACHE", line 55

ORA-06512: at "APPS.ARP_STANDARD", line 3021

Solution: As mentionbed in Oracle Note Document ID 418261.1 "Unapplied Receipt Register" is Obsoleted in R12. 

Below are the comments from Oracle Note.

"In Release 12, the Unapplied Receipt Register has been made Obsoleted. 

The Unapplied and Unresolved Receipts Register is a new RXI report which gives you the ability to change the columns on the report as needed with the Report eXchange Designer responsibility."

Query to get Customer Details in Oracle APPS R12

SELECT DISTINCT ou.name ou_name 

      ,ps.customer_id

  ,party.party_name customer_name

      ,hzca.account_number customer_number

  ,hzca.status customer_status

  ,hcsu.location

  ,hcsu.site_use_code

  ,hcsu.status location_status

  ,ps.class

  ,hcsu.site_use_id

  ,hcpc.name customer_profile_name

  ,hzl.address1 address_line1

  ,hzl.address2 address_line2

  ,hzl.address3 address_line3

  ,hzl.city

  ,hzl.state

  ,hzl.postal_code  

  ,ps.customer_site_use_id

  ,party_site.identifying_address_flag

  ,ps.trx_date

  FROM apps.hz_parties party

      ,apps.hz_party_sites party_site

  ,apps.hz_locations hzl

  ,apps.hz_cust_accounts hzca

  ,apps.hz_cust_acct_sites hcas

  ,apps.hz_cust_site_uses hcsu

  ,apps.hz_customer_profiles cust_profile

  ,apps.hz_cust_profile_classes hcpc

  ,apps.ar_payment_schedules_all ps

  ,apps.hr_operating_units ou 

 WHERE party.party_id = hzca.party_id(+) 

   AND party.party_id = cust_profile.party_id 

   AND party.party_id = party_site.party_id 

   AND party_site.party_site_id = hcas.party_site_id 

   AND party_site.location_id = hzl.location_id 

   AND hzca.cust_account_id = hcas.cust_account_id 

   AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id 

   AND hzca.cust_account_id = cust_profile.cust_account_id 

   AND hzca.cust_account_id = ps.customer_id 

   AND cust_profile.profile_class_id = hcpc.profile_class_id 

   AND ps.customer_site_use_id = hcsu.site_use_id 

   AND hcsu.org_id = ou.organization_id

Steps to define Auto Lockbox in Oracle APPS

• Define the Bank, Bank Branch and Bank Account 
• Define Receipt Class for Auto Lockbox 
• Define Receipt Source
• Define Autocash Ruleset
• Define Customer, attach the receipt method and create the customer bank account (Branch number and account number combination decides the MICR number) 
• Define Lockbox 
• Navigation: Setup --> Receipts --> Lockboxes --> Lockboxes
• Define Transmission Formats 
• Navigation: Setup --> Receipts --> Lockboxes --> Transmission Formats
• Control file changes
• Run Autolockbox 
• Submit Validation 
• Submit Post Quickcash

"This record in table hz_cust_accounts cannot be locked as it has been updated by another user." while updating Customer Account using HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT API in oracle apps

The error indicates that incorrect value of object_version_number is used in the TCA API.

Check object_version_number for the Customer Account which you are trying to update.

SELECT account_number, cust_account_id, object_version_number

FROM hz_cust_accounts

WHERE account_number in ('1234567890')

Pass the object_version_number returned from the above script to p_object_version_number and run the TCA API script.

This information you can find from Oracle Note, 1518466.1

Query to display Transaction Type Details In Oracle Receivables

SELECT hou.name operating_unit      

      ,xep.name legal_entity

      ,rctta.name      

      ,rctta.description

      ,al.meaning class

      ,al2.meaning creation_sign

      ,al3.meaning transaction_status

      ,al4.meaning printing_option

      ,null invoice_type

      ,rctt2.name credit_memo_type

      ,aars.rule_set_name application_rule_set

      ,aat.payment_term_name terms

      ,rctta.start_date

      ,rctta.end_date      

      ,rctta.accounting_affect_flag open_receivable

      ,rctta.adj_post_to_gl allow_adjustment_posting

      ,rctta.post_to_gl post_to_gl

      ,rctta.allow_freight_flag allow_freight

      ,rctta.natural_application_only_flag natural_application_only

      ,rctta.tax_calculation_flag default_tax_classification

      ,rctta.exclude_from_late_charges exclude_from_late_charges_cal

      ,rctta.allow_overapplication_flag allow_over_application

      ,gcck.concatenated_segments receivable_account

      ,gcck3.concatenated_segments freight_account

      ,gcck2.concatenated_segments revenue_account

      ,gcck4.concatenated_segments clearing_account

      ,gcck5.concatenated_segments unbilled_receivable_account

      ,gcck6.concatenated_segments unearned_revenue_account

      ,gcck7.concatenated_segments tax_account

  FROM ra_cust_trx_types_all rctta

      ,hr_operating_units hou

      ,xle_entity_profiles xep

      ,ar_lookups al

      ,ar_lookups al2

      ,ar_lookups al3

      ,ar_lookups al4

      ,ra_cust_trx_types_all rctta2

      ,ar_app_rule_sets aars

      ,arfv_ar_terms aat

      ,gl_code_combinations_kfv gcck

      ,gl_code_combinations_kfv gcck2

      ,gl_code_combinations_kfv gcck3

      ,gl_code_combinations_kfv gcck4

      ,gl_code_combinations_kfv gcck5

      ,gl_code_combinations_kfv gcck6

      ,gl_code_combinations_kfv gcck7

 WHERE 1=1

   AND rctta.org_id= hou.organization_id

   AND xep.legal_entity_id(+)=rctta.legal_entity_id

   AND al.lookup_type='INV/CM'

   AND al.lookup_code=rctta.type

   AND al2.lookup_type='SIGN'

   AND al2.lookup_code=rctta.creation_sign

   AND al3.lookup_type='INVOICE_TRX_STATUS'

   AND al3.lookup_code=rctta.default_status

   AND al4.lookup_type='INVOICE_PRINT_OPTIONS'

   AND al4.lookup_code=rctta.default_printing_option

   AND rctta.credit_memo_type_id=rctta2.cust_trx_type_id(+)

   AND rctta.org_id=rctta2.org_id(+)

   AND rctta.rule_set_id=aars.rule_set_id(+)

   AND rctta.default_term=aat.term_id(+)

   AND gcck.code_combination_id(+)=rctta.gl_id_rec

   AND gcck2.code_combination_id(+)=rctta.gl_id_rev

   AND gcck3.code_combination_id(+)=rctta.gl_id_freight

   AND gcck4.code_combination_id(+)=rctta.gl_id_clearing

   AND gcck5.code_combination_id(+)=rctta.gl_id_unbilled

   AND gcck6.code_combination_id(+)=rctta.gl_id_unearned

   AND gcck7.code_combination_id(+)=rctta.gl_id_tax

;