/* 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