Thursday, 19 January 2017

Find the Sql Query of O2C - ORDER to CASH Cycle Table flow in Oracle Apps EBS R12


/* Find the Sql Query of O2C - ORDER to CASH Cycle Table flow in Oracle Apps EBS R12 */

SELECT 'OOH', OOH.HEADER_ID, OOH.ORG_ID, OOH.ORDER_TYPE_ID, OOH.ORDER_NUMBER , OOH.PAYMENT_TERM_ID, OOH.SOLD_FROM_ORG_ID, OOH.SHIP_FROM_ORG_ID,
              OOH.CANCELLED_FLAG , OOH.OPEN_FLAG, OOH.BOOKED_FLAG, OOH.ORDER_CATEGORY_CODE, OOH.FLOW_STATUS_CODE,
       'OOL', OOL.LINE_ID, OOL.ORG_ID, OOL.HEADER_ID, OOL.LINE_TYPE_ID, OOL.LINE_NUMBER, OOL.ORDERED_ITEM, OOL.ORDERED_ITEM_ID, OOL.ORDER_QUANTITY_UOM,
              OOL.PRICING_QUANTITY,OOL.CANCELLED_QUANTITY, OOL.SHIPPED_QUANTITY , OOL.ORDERED_QUANTITY , OOL.SHIP_FROM_ORG_ID, OOL.SOLD_FROM_ORG_ID,
              OOL.INVENTORY_ITEM_ID, OOL.UNIT_SELLING_PRICE, OOL.ITEM_TYPE_CODE, OOL.LINE_CATEGORY_CODE, OOL.CANCELLED_FLAG, OOL.OPEN_FLAG, OOL.BOOKED_FLAG,
              OOL.FLOW_STATUS_CODE,
       'WDD', WDD.DELIVERY_DETAIL_ID, WDD.SOURCE_HEADER_ID "HEADER_ID", WDD.SOURCE_LINE_ID "LINE_ID", WDD.SOURCE_HEADER_TYPE_ID, WDD.SOURCE_HEADER_TYPE_NAME ,
              WDD.CUSTOMER_ID, WDD.INVENTORY_ITEM_ID, WDD.ITEM_DESCRIPTION, WDD.SHIP_FROM_LOCATION_ID, WDD.ORGANIZATION_ID, WDD.ORG_ID,
              WDD.CANCELLED_QUANTITY, WDD.REQUESTED_QUANTITY, WDD.REQUESTED_QUANTITY_UOM, WDD.RELEASED_STATUS ,WDD.CURRENCY_CODE,
              WDD.INSPECTION_FLAG,  WDD.SOURCE_HEADER_NUMBER "ORDER_NUMBER", WDD.PICKABLE_FLAG, WDD.PICKED_QUANTITY,
       'WDA', WDA.DELIVERY_ASSIGNMENT_ID, WDA.DELIVERY_ID, WDA.DELIVERY_DETAIL_ID,WDA.ACTIVE_FLAG, WDA.TYPE ,
       'RCTA',RCTA.CUSTOMER_TRX_ID, RCTA.TRX_NUMBER,RCTA.TRX_DATE, RCTA.CUST_TRX_TYPE_ID, RCTA.SET_OF_BOOKS_ID,RCTA.BATCH_ID, RCTA.SOLD_TO_CUSTOMER_ID,
              RCTA.SHIP_TO_CUSTOMER_ID,  RCTA.TERM_ID,RCTA.INVOICE_CURRENCY_CODE, RCTA.COMPLETE_FLAG , RCTA.INTERFACE_HEADER_ATTRIBUTE1 ORDER_NUMBER,
              RCTA.INTERFACE_HEADER_ATTRIBUTE2 ORDER_TYPE,RCTA.INTERFACE_HEADER_ATTRIBUTE3 DELIVERY_ID, RCTA.STATUS_TRX,RCTA.ORG_ID,
       'RCTLA', RCTLA.CUSTOMER_TRX_ID,RCTLA.CUSTOMER_TRX_LINE_ID, RCTLA.SET_OF_BOOKS_ID,RCTLA.INVENTORY_ITEM_ID, RCTLA.DESCRIPTION,RCTLA.QUANTITY_ORDERED,
                RCTLA.QUANTITY_INVOICED, RCTLA.UNIT_SELLING_PRICE, RCTLA.UOM_CODE, RCTLA.SALES_ORDER, RCTLA.SALES_ORDER_DATE , RCTLA.LINE_TYPE,RCTLA.INTERFACE_LINE_CONTEXT,
                RCTLA.INTERFACE_LINE_ATTRIBUTE1 ORDER_NUMBER, RCTLA.INTERFACE_LINE_ATTRIBUTE2 ORDER_TYPE,RCTLA.INTERFACE_LINE_ATTRIBUTE3 DELIVERTY_ID,
                RCTLA.ORG_ID, RCTLA.WAREHOUSE_ID, RCTLA.SHIP_TO_CUSTOMER_ID,
       'ARPS', ARPS.PAYMENT_SCHEDULE_ID , ARPS.DUE_DATE, ARPS.STATUS, ARPS.INVOICE_CURRENCY_CODE, ARPS.CLASS, ARPS.CUST_TRX_TYPE_ID, ARPS.CUSTOMER_ID,
               ARPS.CUSTOMER_TRX_ID, ARPS.AMOUNT_APPLIED, ARPS.TRX_NUMBER, ARPS.TRX_DATE, ARPS.GL_DATE, ARPS.ORG_ID
      -- 'ARCR', ARCR.CASH_RECEIPT_ID, ARCR.RECEIPT_NUMBER, ARCR.RECEIPT_DATE, ARCR.AMOUNT, ARCR.AMOUNT, ARCR.SET_OF_BOOKS_ID, ARCR.CURRENCY_CODE, ARCR.STATUS, ARCR.TYPE,
              -- ARCR.CONFIRMED_FLAG, ARCR.CUSTOMER_SITE_USE_ID
FROM   OE_ORDER_HEADERS_ALL OOH,
       OE_ORDER_LINES_ALL OOL,
       WSH_DELIVERY_DETAILS WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       AR_PAYMENT_SCHEDULES_ALL ARPS,
       AR_RECEIVABLE_APPLICATIONS_ALL ARRA
WHERE  OOH.HEADER_ID = OOL.HEADER_ID
AND    OOH.HEADER_ID = WDD.SOURCE_HEADER_ID
AND    OOL.LINE_ID   = WDD.SOURCE_LINE_ID
AND    WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND    RCTA.INTERFACE_HEADER_ATTRIBUTE1 = TO_CHAR (OOH.ORDER_NUMBER)
AND    RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID
AND    RCTA.ORG_ID = RCTLA.ORG_ID
AND    RCTLA.INVENTORY_ITEM_ID =OOL.INVENTORY_ITEM_ID
AND    RCTLA.CUSTOMER_TRX_ID = ARPS.CUSTOMER_TRX_ID
AND    ARPS.PAYMENT_SCHEDULE_ID = ARRA.PAYMENT_SCHEDULE_ID (+)
AND    OOH.ORDER_NUMBER = 20000007;


SELECT 'RCTA', RCTA.CUSTOMER_TRX_ID, RCTA.TRX_NUMBER,RCTA.TRX_DATE, RCTA.CUST_TRX_TYPE_ID, RCTA.SET_OF_BOOKS_ID,RCTA.BATCH_ID, RCTA.SOLD_TO_CUSTOMER_ID,
               RCTA.SHIP_TO_CUSTOMER_ID,  RCTA.TERM_ID,RCTA.INVOICE_CURRENCY_CODE, RCTA.COMPLETE_FLAG , RCTA.INTERFACE_HEADER_ATTRIBUTE1 ORDER_NUMBER,
               RCTA.INTERFACE_HEADER_ATTRIBUTE2 ORDER_TYPE,RCTA.INTERFACE_HEADER_ATTRIBUTE3 DELIVERY_ID, RCTA.STATUS_TRX,RCTA.ORG_ID
FROM RA_CUSTOMER_TRX_ALL RCTA
WHERE RCTA.INTERFACE_HEADER_ATTRIBUTE1 = '12130368'



SELECT 'RCTLA', RCTLA.CUSTOMER_TRX_ID,RCTLA.CUSTOMER_TRX_LINE_ID, RCTLA.SET_OF_BOOKS_ID,RCTLA.INVENTORY_ITEM_ID, RCTLA.DESCRIPTION,RCTLA.QUANTITY_ORDERED,
                RCTLA.QUANTITY_INVOICED, RCTLA.UNIT_SELLING_PRICE, RCTLA.UOM_CODE, RCTLA.SALES_ORDER, RCTLA.SALES_ORDER_DATE , RCTLA.LINE_TYPE,RCTLA.INTERFACE_LINE_CONTEXT,
                RCTLA.INTERFACE_LINE_ATTRIBUTE1 ORDER_NUMBER, RCTLA.INTERFACE_LINE_ATTRIBUTE2 ORDER_TYPE,RCTLA.INTERFACE_LINE_ATTRIBUTE3 DELIVERTY_ID,
                RCTLA.ORG_ID, RCTLA.WAREHOUSE_ID, RCTLA.SHIP_TO_CUSTOMER_ID
  FROM RA_CUSTOMER_TRX_LINES_ALL RCTLA
 WHERE CUSTOMER_TRX_ID = '91865';





SELECT interface_header_attribute2, customer_trx_id, trx_number,
       cust_trx_type_id, complete_flag, ship_date_actual
  FROM ra_customer_trx_all
 WHERE interface_header_attribute1 = '12130368';

 SELECT 'ARCR', ARCR.CASH_RECEIPT_ID, ARCR.RECEIPT_NUMBER, ARCR.RECEIPT_DATE, ARCR.AMOUNT, ARCR.AMOUNT, ARCR.SET_OF_BOOKS_ID, ARCR.CURRENCY_CODE, ARCR.STATUS, ARCR.TYPE,
               ARCR.CONFIRMED_FLAG, ARCR.CUSTOMER_SITE_USE_ID
 FROM AR_CASH_RECEIPTS_ALL ARCR
 WHERE RECEIPT_NUMBER = 'DANISH TEST12'


 select 'ARPS', ARPS.PAYMENT_SCHEDULE_ID , ARPS.DUE_DATE, ARPS.STATUS, ARPS.INVOICE_CURRENCY_CODE, ARPS.CLASS, ARPS.CUST_TRX_TYPE_ID, ARPS.CUSTOMER_ID,
        ARPS.CUSTOMER_TRX_ID, ARPS.AMOUNT_APPLIED, ARPS.TRX_NUMBER, ARPS.TRX_DATE, ARPS.GL_DATE, ARPS.ORG_ID
 from AR_PAYMENT_SCHEDULES_ALL ARPS
 where ARPS.CUSTOMER_TRX_ID =91865

 SELECT *
 FROM ar_receivable_applications_all
 WHERE CASH_RECEIPT_ID = 68342

No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...