Friday, 10 July 2015

Order to Cash cycle (O2C) Query


SELECT
     AC.CUSTOMER_NAME
    ,OOH.ORDER_NUMBER
    ,(Select  ORGANIZATION_CODE From mtl_parameters where ORGANIZATION_ID=OOH.SHIP_FROM_ORG_ID) ORG_CODE
    ,OT.NAME ORDER_TYPE
    ,OOL.ORDERED_QUANTITY
    ,OOL.SHIPPED_QUANTITY
    ,OOL.UNIT_SELLING_PRICE UNIT_PRICE
    ,WND.NAME DELIVERY_NO
    ,(SELECT RAT.NAME FROM RA_TERMS RAT WHERE RAT.TERM_ID=RCT.TERM_ID) TERM_NAME
    ,RCT.TRX_NUMBER
    ,ARPS.AMOUNT_LINE_ITEMS_ORIGINAL TOTAL_INVOICE_AMOUNT
    ,ARPS.TAX_ORIGINAL      ORIGINAL_TAX_AMT
    ,ARPS.TAX_REMAINING     REMAINING_TAX_AMT
    ,ARPS.AMOUNT_DUE_REMAINING DUE_AMOUNT
FROM
    OE_ORDER_HEADERS_ALL OOH
    ,OE_ORDER_LINES_ALL OOL
    ,OE_TRANSACTION_TYPES_TL OT
    ,AR_CUSTOMERS AC
    ,WSH_DELIVERY_DETAILS WDD
    ,WSH_NEW_DELIVERIES   WND
    ,WSH_DELIVERY_ASSIGNMENTS WDA
    ,RA_CUSTOMER_TRX_ALL RCT
    --,RA_CUSTOMER_TRX_LINES_ALL RCTL
    ,AR_PAYMENT_SCHEDULES_ALL ARPS
WHERE
 OOH.HEADER_ID=OOL.HEADER_ID
AND  OOH.ORDER_TYPE_ID=OT.TRANSACTION_TYPE_ID
AND  AC.CUSTOMER_ID=OOH.SOLD_TO_ORG_ID
AND  WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
AND  WDD.SOURCE_LINE_ID(+)=OOL.LINE_ID
AND  WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID(+)
AND  WDA.DELIVERY_ID=WND.DELIVERY_ID(+)
--AND  WND.CUSTOMER_ID=AC.CUSTOMER_ID
--AND  OOH.ORG_ID=RCT.ORG_ID
--AND  OOH.ORDER_NUMBER=RCT.INTERFACE_HEADER_ATTRIBUTE1(+)
AND  WDA.DELIVERY_ID=RCT.INTERFACE_HEADER_ATTRIBUTE3(+)
AND ARPS.CUSTOMER_TRX_ID(+)=RCT.CUSTOMER_TRX_ID
--AND  RCT.ORG_ID=RCTL.ORG_ID
--AND  RCT.CUSTOMER_TRX_ID=RCTL.CUSTOMER_TRX_ID(+)
--AND RCTL.INTERFACE_LINE_ATTRIBUTE6(+)=TO_CHAR(OOL.LINE_ID)
--AND RCTL.interface_line_context = 'ORDER ENTRY'
--AND RCTL.LINE_TYPE='LINE'
--AND OOH.ORDER_NUMBER=RCTL.INTERFACE_LINE_ATTRIBUTE1
Order By ORDER_NUMBER

No comments:

Post a Comment