1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Requires Report Name: Pending Sales Order

Discussion in 'Oracle CRM' started by manikvengurlekar, May 25, 2012.

  1. manikvengurlekar

    manikvengurlekar Forum Advisor

    Messages:
    54
    Likes Received:
    4
    Trophy Points:
    235
    Dear All,
    I want to track the 'Pending Sales order' for an oragainzation. Can anybody tell me is any standard report available. please provide me the name of the report. :confused:
    Thanks in Advance....


    Regards
    Manik
     
  2. money3388

    money3388 Active Member

    Messages:
    15
    Likes Received:
    8
    Trophy Points:
    90
    Hi Manik

    What is 'Pending Sales order' ??
     
  3. manikvengurlekar

    manikvengurlekar Forum Advisor

    Messages:
    54
    Likes Received:
    4
    Trophy Points:
    235
    I require the list of sales orders whose status is not 'Closed'.


    Regards
    Manik
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Dear manik,

    Only booked sales order right!. Because there may be cancelled orders or any other orders may exists apart from closed. I think there is no standard report exists for your requirements.
     
  5. manikvengurlekar

    manikvengurlekar Forum Advisor

    Messages:
    54
    Likes Received:
    4
    Trophy Points:
    235
    Hi Bharat,
    Thanks for the reply...
    Ok is there any SQL query so that i can try it from back end database.


    Regards
    Manik
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Do you need only booked orders or you need all orders except closed one in that report. By that I can provide you the query. And one more is what are the fields you required in that.
     
  7. manikvengurlekar

    manikvengurlekar Forum Advisor

    Messages:
    54
    Likes Received:
    4
    Trophy Points:
    235
    Hi Bharat
    I need the query which consist of Order no with order lines, reserved qty,Each line status eg, line 1 'Awaiting shipping', Line 2 'Back ordered' etc. I want all these before the SO lines gets closed.
     
  8. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    Dear Bharat,

    You need to prepare one for the lines. The Order Header status will be the same while lines can have different statuses. I have the query somewhere but too will save searching if you help manik.
    cheers
    Ramji

    PS: Also apologies for the typo in another post - Intended was Bharathappa! :)
     
    Bharat likes this.
  9. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Manik,

    Here is the query for you.

    Code (Text):
    SELECT OOHA.ORDER_NUMBER"ORDER NUMBER"
          ,HP.PARTY_NAME"CUSTOMER"
          ,OOLA.LINE_ID"LINE ID"
          ,OOLA.LINE_NUMBER"LINE NUM"
          ,MSIB.SEGMENT1"ITEM"
          ,OOLA.ORDERED_QUANTITY"ORDERED QUANTITY"
          ,OOLA.FLOW_STATUS_CODE"LINE STATUS"

    FROM OE_ORDER_HEADERS_ALL OOHA
        ,OE_ORDER_LINES_ALL OOLA
        ,HZ_PARTIES HP
        ,HZ_CUST_ACCOUNTS HCA
        ,MTL_SYSTEM_ITEMS_B MSIB

    WHERE
          OOLA.HEADER_ID=OOHA.HEADER_ID
      AND HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID
      AND HP.PARTY_ID=HCA.PARTY_ID
      AND MSIB.INVENTORY_ITEM_ID=OOLA.INVENTORY_ITEM_ID
      AND MSIB.ORGANIZATION_ID=OOHA.SHIP_FROM_ORG_ID
      AND OOLA.FLOW_STATUS_CODE!='CLOSED'
      ORDER BY 1,4;

     
    Let me know if any modifications needed for you on this.
     
  10. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    Manik,

    Do post a follow up after you test the solution always. This will help others.
    Cheers
    Ramji
     
    Bharat likes this.
  11. manikvengurlekar

    manikvengurlekar Forum Advisor

    Messages:
    54
    Likes Received:
    4
    Trophy Points:
    235
    Hi Bharat & Ramji,

    @Ramji: Sorry for the Late reply as i was busy last whole week.

    @Bharat: Thanks for the wonderful solution it's really working as per my requirement. Thanks of the code.
     
    Ramji and Bharat like this.
  12. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    Full Credit to Bharat!
    cheers
    Ramji
     
    Bharat likes this.
  13. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Credit all goes to the people who guides me and made me like this. Iam nothing without their guidance. Anyhow thanks for your valuable reply.