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!

Query On Back Order Only

Discussion in 'Oracle Apps Technical' started by mist, Nov 27, 2013.

  1. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi all,

    I have a requirement for ex:-I am having 10 orders which are 10 records are back order for this i want a query on this. I am requesting to all could any one please help me please

    Thank you
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Mist,
    Try the following Query. It may helps you...

    Code (SQL):
    SELECT ool.ordered_quantity quantity
    FROM oe_order_headers_all ooh
    ,oe_order_lines_all ool
    ,wsh_delivery_details wdd
    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.released_status = 'B'
     
    Bharat likes this.
  3. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi Jagadekara,

    I am getting also release_status='C' , i want only in 'B' please help me
    Thank you
     
  4. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    when i check like below, I want to display only in 'B' status records, please suggest me
    select released_status from wsh_delivery_details
    where Source_header_number='66438' --Only disaply 'B' statuses

    Thank you
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    use wdd.released_status = 'B'
     
  6. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi ,

    I used above query what you are posted, but it displays also statuses in B & C records and i want only B status records only

    Thank you
     
  7. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi ,

    I used above query what you are posted, but it displays also statuses in B & C records and i want only B status records only

    Thank you
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Can you send me output?
     
  9. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    Please go through the below o/p please
    R
    -
    C
    B
    C
    C
    C
    C
    C
    C
    C
    C
    C

    Thank you
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    The below query getting the above output?

    SELECT ool.ordered_quantity quantity
    FROM oe_order_headers_all ooh
    ,oe_order_lines_all ool
    ,wsh_delivery_details wdd
    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.released_status = 'B';

    ool.ordered_quantity have that values?
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    R
    -
    C
    B
    C
    C
    C
    C
    C
    C
    C
    C
    C

    Here R means which column and in which table?
     
  12. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    R means released_status on wsh_delivery_details table

    Thank you
    Mist
     
  13. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    We gave wdd.released_status='B' right? Then how other values came?
    Have you gave that condition or not?
     
  14. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi,
    yes, please check it below
     
  15. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi your query is correct. provide full output all cols.. and u r using toad or any other?
     
  16. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    I am getting 11,596 records and i am using Toad only

    Thank you
     
  17. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    ok! then change that condition to wdd.released_status!='B' and check
     
  18. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    it is still running..
     
  19. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Oh! For time being restrict that query by giving date range.
     
  20. mist

    mist Active Member

    Messages:
    59
    Likes Received:
    2
    Trophy Points:
    160
    Hi,

    I add creation_date>sysdate-1 , so, i am getting 2 records, how can i send to you

    Thank you