ORDER TO CASH CYCLE IN ORACLE APPS TECHNICAL FLOW

  --------------ORDER TO CASH CYCLE IN ORACLE APPS TECHINICAL TABLES
==============================================================================
-- ENTER SALES ORDER  and BOOK SALES ORDER AFFECTING TABLES AND DETAILS
=============================================================================

SELECT order_number,
       header_id,
       flow_status_code,
       org_id
FROM   OE_ORDER_HEADERS_ALL
WHERE  ORDER_NUMBER = 1073413

SELECT HEADER_ID,
       LINE_ID,
       FLOW_STATUS_CODE,   ---Afte booking flow_status_code is AWAITING_SHIPPING
       INVENTORY_ITEM_ID
FROM   OE_ORDER_LINES_ALL
WHERE  HEADER_ID = '1530577'

SELECT  SOURCE_HEADER_ID,
        SOURCE_HEADER_NUMBER,
        DELIVERY_DETAIL_ID,
        SHIP_FROM_LOCATION_ID,
        SHIP_TO_LOCATION_ID,
        SHIPPED_QUANTITY,
        SUBINVENTORY,    ---Staging subinventory here
        RELEASED_STATUS,   --c -confirmed
        SHIP_METHOD_CODE
FROM     WSH_DELIVERY_DETAILS
WHERE  SOURCE_HEADER_ID = '1530577'

SELECT delivery_detail_id,
       delivery_id
FROM    WSH_DELIVERY_ASSIGNMENTS
WHERE DELIVERY_DETAIL_ID =877368931

=======================================================================
--- Relase Sales Order affecting Tables
====================================
SELECT
FROM    WSH_PICKING_BATCHES
WHERE ORDER_HEADER_ID =            --order header id = header_id

SELECT
FROM   WSH_NEW_DELIVERIES
WHERE  delivery_id = ''      -- delivery_id = delivery_id

========================================================================
------Shipping Sales Order--------
============================================

SELECT delivery_id
FROM  WSH_DELIVERY_LEGS
WHERE DELIVERY_ID = 1530577

SELECT
FROM    WSH_TRIP_STOPS
WHERE TRIP_ID =

SELECT
FROM    WSH_TRIPS
WHERE   STOP_ID IN()

=====================================================================
-----------AR AUTO INVOICE TABLES-------
======================================
SELECT   STATUS_TRX,
         INTERFACE_HEADER_ATTRIBUTE1,
         SHIP_TO_SITE_USE_ID,
         SHIP_TO_CONTACT_ID,
         SHIP_TO_CUSTOMER_ID,
         BILL_TO_SITE_USE_ID,
         BILL_TO_CUSTOMER_ID,
         SOLD_TO_SITE_USE_ID,
         SOLD_TO_CUSTOMER_ID,
         CUST_TRX_TYPE_ID,
         TRX_DATE,
         TRX_NUMBER,
         CUSTOMER_TRX_ID
FROM    RA_CUSTOMER_TRX_ALL
WHERE INTERFACE_HEADER_ATTRIBUTE1 = '1073413'               --order number

SELECT SALES_ORDER_SOURCE,   --ORDER ENTRY/ ANY OF AUTOMATION LIKE IF ANY MEHTOD
       AMOUNT_DUE_REMAINING,
       AMOUNT_DUE_ORIGINAL,
       LINE_TYPE,
       CUSTOMER_TRX_LINE_ID,
       DESCRIPTION,
       INVENTORY_ITEM_ID,
       QUANTITY_INVOICED,
       QUANTITY_ORDERED,
       UNIT_SELLING_PRICE,
       SALES_ORDER,
       SALES_ORDER_DATE,
       SALES_ORDER_LINE,
       REVENUE_AMOUNT,
       EXTENDED_AMOUNT
FROM    RA_CUSTOMER_TRX_LINES_ALL
WHERE customer_trx_id = '1865249'

SELECT     CUSTOMER_TRX_ID,
         CUST_TRX_LINE_GL_DIST_ID,
         CUSTOMER_TRX_LINE_ID,
         CODE_COMBINATION_ID,
         SET_OF_BOOKS_ID,
         CUST_TRX_LINE_SALESREP_ID,
         ACCOUNT_CLASS,
         CUSTOMER_TRX_ID,
         ACCTD_AMOUNT,
FROM    RA_CUST_TRX_LINE_GL_DIST_ALL
WHERE  CUSTOMER_TRX_ID = '1865249'

SELECT   REVENUE_AMOUNT_SPLIT,
         CUST_TRX_LINE_SALESREP_ID,
         CUSTOMER_TRX_ID,
         SALESREP_ID,
         CUSTOMER_TRX_LINE_ID
FROM    RA_CUST_TRX_LINE_SALESREPS_ALL
WHERE CUSTOMER_TRX_ID = '1865249'

SELECT  PAYMENT_SCHEDULE_ID,
        DUE_DATE,
        AMOUNT_DUE_ORIGINAL,
        AMOUNT_DUE_REMAINING,
        NUMBER_OF_DUE_DATES,
        STATUS,
        CLASS CLS,            --TYPE OF TRANSACTIONS INVOICE/CREDITMENO/DEBITMENO ......
        CUST_TRX_TYPE_ID,
        CUSTOMER_ID,
        CUSTOMER_SITE_USE_ID,
        CUSTOMER_TRX_ID,
        CASH_RECEIPT_ID,
        GL_DATE_CLOSED,
        ACTUAL_DATE_CLOSED,
        AMOUNT_APPLIED,
        AMOUNT_ADJUSTED,
        AMOUNT_IN_DISPUTE,
        AMOUNT_CREDITED,
        FREIGHT_REMAINING,
        FREIGHT_ORIGINAL,
        TRX_DATE,
        TRX_NUMBER,
        ACCTD_AMOUNT_DUE_REMAINING,
        PAYMENT_APPROVAL
FROM    AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID = '1865249'

SQL IMPORTANT QUERIES

Find out the nth row of the table

02:52:43 SQL> select *from emp where rowid = (select max(rowid) from emp where rownum <=4);      

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER          7839 02-APR-81       2975            20

02:53:56 SQL> select * from emp order by empno asc;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 09-DEC-82       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 12-JAN-83       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.

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);

Customer List in Oracle Apps R12.1.3

SELECT hp.party_name Customer_Name
     , hca.account_number Customer_Number
     , hl.address1
     , hl.address2
     , hl.address3
     , hl.city
     , hl.state
     , hl.postal_code ZIP
     , hl.country
FROM   hz_parties hp
     , hz_party_sites hps
     , hz_locations hl
     , hz_cust_accounts_all hca
     , hz_cust_acct_sites_all hcsa
     , hz_cust_site_uses_all hcsu
WHERE  hp.party_id = hps.party_id
AND    hps.location_id = hl.location_id
AND    hp.party_id = hca.party_id
AND    hcsa.party_site_id = hps.party_site_id
AND    hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND    hca.cust_account_id = hcsa.cust_account_id
AND    hp.status = 'A'
AND    hps.status = 'A';