TRX_NUMBER Throug will get customer detail and order details in oracle apps R12.1.3

 Accounts Recevibles Based on TRX_NUMBER, through will get customer detail and order details in oracle apps R12.1.3


SELECT   hp.party_id,
         hp.party_number,
         hp.party_name,
         hp.status active,
         hp.address1 address1,
         hp.address2 address2,
         hp.city     city,
         hp.county   county,
         hp.country  country,
         hp.postal_code postalcode,
         rcta.trx_number,
         rctta.name,
         apsca.trx_date
FROM     HZ_PARTIES HP,
                 HZ_CUST_ACCOUNTS_ALL HCAA,
                 HZ_CUST_ACCT_SITES_ALL HCASA,
                  RA_CUSTOMER_TRX_ALL RCTA,
                   RA_CUST_TRX_TYPES_ALL RCTTA,
                  AR_PAYMENT_SCHEDULES_ALL APSCA
WHERE    hp.party_id = hcaa.party_id
AND      hcaa.cust_account_id = hcasa.cust_account_id
AND      hcaa.cust_account_id = rcta.bill_to_customer_id
AND      rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND      apsca.customer_trx_id = rcta.customer_trx_id
AND      hcaa.party_id ='1048'
AND      rcta.trx_number ='2428'

Oracle Apps Receivables (AR) Tables

Oracle Apps Receivables (AR) Tables 

TRANSACTIONS TABLES
===========================================================
SELECT * FROM RA_CUSTOMER_TRX_ALL
SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL
SELECT * FROM RA_CUST_TRX_TYPES_ALL
SELECT * FROM RA_CUST_TRX_LINE_GL_DIST_ALL
SELECT * FROM RA_TERMS
============================================================
RECEIPTS TABLES
============================================================
SELECT * FROM AR_RECEIPTS_ALL
SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL
SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL
SELECT * FROM  AR_PAYMENT_SCHEDULES_ALL

Transactions
RA_CUSTOMER_TRX_ALL	Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL	Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL	Distribution for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL	Salesrep information for Transaction Lines
Transaction Interface Tables
RA_INTERFACE_LINES_ALL	Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL	Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL	Transaction Distribution information
RA_INTERFACE_ERRORS_ALL	Transaction errors table
AR_PAYMENTS_INTERFACE_ALL	Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL	Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL	Lockbox transfers the receipts that pass validation to the interim tables
Receipts tables
AR_CASH_RECEIPTS_ALL	Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL	stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL	This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL	This table stores all of the activity that is contained for the life cycle of a receipt. • Each row represents one step. • The status field for that row tells you which step the receipt has reached. • Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.
Customer Tables
HZ_PARTIES	A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS	This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES	This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL	This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL	This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS	A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
Setup tables
RA_CUST_TRX_TYPES_ALL	This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES	This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS	This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

Inventory Item non Transactable query in oracle apps r12.1.3

 Inventory Item non Transactable query in oracle apps r12.1.3

SELECT   msib.segment1 item,
         msib.description,
         moq.subinventory_code,
         sum(moq.transaction_quantity) OHND_QTY,
         mp.organization_code,
         msib.primary_uom_code
FROM     MTL_SYSTEM_ITEMS_B MSIB,
         MTL_PARAMETERS MP,
         MTL_ONHAND_QUANTITIES MOQ
WHERE    1=1 --msib.segment1 = '70710FTOPAZ52'
AND      msib.inventory_item_id = moq.inventory_item_id
AND      msib.organization_id   = moq.organization_id
AND      mp.organization_code = :p_organization_code
AND                 msib.organization_id = mp.organization_id
AND                 msib.MTL_TRANSACTIONS_ENABLED_FLAG = 'N'
GROUP BY  msib.segment1,
                      msib.description,
                     mp.organization_code,
                    moq.subinventory_code,
                     msib.primary_uom_code
ORDER BY msib.segment1, mp.organization_code;

To find out Onhand quantity based on organization, and Subinventory Code:

To find out Onhand quantity based on organization, and Subinventory Code:


SELECT msib.segment1 ITEM_NAME,
       moq.inventory_item_id,
       moq.subinventory_code subinventory_code,
       mp.organization_code, 
       moq.organization_id,
       sum(moq.transaction_quantity)
FROM   MTL_ONHAND_QUANTITIES MOQ,
       MTL_SYSTEM_ITEMS_B MSIB,
       MTL_PARAMETERS MP
WHERE  msib.inventory_item_id = moq.inventory_item_id
AND    msib.organization_id = mp.organization_id
AND    msib.organization_id = moq.organization_id
AND    moq.organization_id = :p_organization_id 
GROUP BY msib.segment1,moq.inventory_item_id, moq.organization_id,moq.subinventory_code,mp.organization_code
ORDER BY msib.segment1, moq.organization_id,sum(moq.transaction_quantity);