Search This Blog

Sunday, November 14, 2010

Need to Ship Quantity Query

SELECT DISTINCT ooh.order_number, wdd.source_header_type_name,
                wdd.cust_po_number, wdd.organization_id,
                wdd.src_requested_quantity, wdd.requested_quantity,
                wdd.released_status, flv.meaning,
                wdd.oe_interfaced_flag,
                wdd.inv_interfaced_flag, wdd.mvt_stat_status,
                wdd.creation_date, wdd.org_id, wdd.inventory_item_id,
                wnd.status_code,flv1.meaning,flv1.description, mtrl.pick_slip_date, mtrl.primary_quantity,
                mtrh.request_number, ool.flow_status_code,
                ooh.flow_status_code,mil.segment5 Position
           FROM wsh.wsh_trips wt,
                wsh.wsh_trip_stops wtt,
                wsh.wsh_delivery_legs wdl,
                wsh.wsh_new_deliveries wnd,
                wsh.wsh_delivery_assignments wda,
                wsh.wsh_delivery_details wdd,
                ont.oe_order_headers_all ooh,
                ont.oe_order_lines_all ool,
                inv.mtl_txn_request_lines mtrl,
                inv.mtl_txn_request_headers mtrh,
                apps.fnd_lookup_values flv,
                apps.fnd_lookup_values flv1,
                inv.mtl_material_transactions mmt,
                inv.mtl_item_locations MIL                              
          WHERE wt.trip_id(+) = wtt.trip_id
            AND wtt.stop_id(+) = wdl.pick_up_stop_id
            AND wnd.delivery_id = wdl.delivery_id(+)
            AND wnd.delivery_id(+) = wda.delivery_id
            AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
            AND ooh.header_id = wdd.source_header_id
            AND ooh.header_id = ool.header_id(+)
            AND ool.line_id = mtrl.txn_source_line_id(+)
            AND ool.line_id = wdd.source_line_id(+)
            AND mtrh.header_id(+) = mtrl.header_id
            --and flv.view_application_id =665
            and wdd.released_status = flv.lookup_code
            and flv.lookup_type='PICK_STATUS'
            and flv1.view_application_id =665
            and wnd.status_code = flv1.lookup_code
            and flv1.lookup_type='DELIVERY_STATUS'
            AND wdd.source_code = 'OE'
            --AND wt.name = --'142310'
            AND mmt.trx_source_line_id = ool.line_id
            and mmt.organization_id = ool.ship_from_org_id
            AND mmt.locator_id = mil.inventory_location_id
            And mmt.inventory_item_id = ool.inventory_item_id
            AND mmt.transaction_quantity < 0
            AND mmt.transaction_type_id = 52
            And mmt.MOVE_ORDER_LINE_ID = mtrl.LINE_ID
            AND wnd.name = '147190'
           
            AND ooh.order_number = '&order_number'
           
           
  
 SELECT   distinct ooha.ORDER_NUMBER ORDER_NUMBER,wnd.delivery_id delivery_id,ooha.header_id header_id
        FROM OE_ORDER_HEADERS_ALL OOHA,
             OE_ORDER_LINES_ALL OOLA,
             WSH_DELIVERY_ASSIGNMENTS WDA,
             WSH_DELIVERY_DETAILS WDD,
             WSH_NEW_DELIVERIES WND
       WHERE OOHA.HEADER_ID = OOLA.HEADER_ID
         AND WND.DELIVERY_ID(+) = WDA.DELIVERY_ID
         AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID(+)
         AND OOLA.LINE_ID = WDD.SOURCE_LINE_ID
        -- AND WDD.SOURCE_CODE = 'OE'
       -- AND WDD.RELEASED_STATUS IN ('C', 'I', 'Y')



Select  customer_trx_line_id  ,rct.customer_trx_id ,rct.trx_number
From  ra_customer_trx_lines_all rctl, ra_customer_trx_all rct
Where  rct.customer_trx_id = rctl.customer_trx_id
and nvl(rct.interface_header_context,'X') in ('ORDER ENTRY','PROJECTS INVOICES')
and rct.org_id = 82
and rct.trx_date between to_date('01-JAN-2009', 'DD-MON-YYYY') and to_date('31-JAN-2009', 'DD-MON-YYYY')
and exists (select 1 from ra_cust_trx_line_gl_dist_all rctlgd where rctlgd.customer_trx_id = rct.customer_trx_id 
and rctlgd.account_class ='REC' and rctlgd.latest_rec_flag ='Y' and rctlgd.org_id = rct.org_id

26 comments:

  1. Greetings! Very useful advice in this particular post!
    It is the little changes that produce the most significant
    changes. Many thanks for sharing!

    Feel free to visit my web site ... Youtube auto

    ReplyDelete
  2. Wonderful post! We are linking to this particularly great post on our
    site. Keep up the good writing.

    Here is my blog post :: dragonvale Gems

    ReplyDelete
  3. you're actually a excellent webmaster. The web site loading velocity is amazing. It seems that you're doing any distinctive trick.
    Furthermore, The contents are masterpiece. you've performed a great activity on this subject!

    My webpage: psn code generator

    ReplyDelete
  4. Simply desire to say your article is as surprising. The clearness in your post is simply
    great and i can assume you are an expert on this subject.
    Well with your permission let me to grab your feed to keep updated with
    forthcoming post. Thanks a million and please continue the rewarding work.


    Feel free to surf to my blog - miscrits Of Sunfall

    ReplyDelete
  5. Hello! This is my 1st comment here so I just wanted to give a quick shout out and tell you I truly enjoy reading through
    your blog posts. Can you recommend any other blogs/websites/forums that deal with the same subjects?
    Thanks a ton!

    my blog; earn cash for doing stuff you normally do on the internet

    ReplyDelete
  6. For hottest news you have to pay a quick visit the web and on the web I found this web page as a finest web site
    for hottest updates.

    Also visit my webpage: Tube Toolbox

    ReplyDelete
  7. What i do not understood is in fact how you are now not really much more neatly-appreciated than you may be now.

    You're very intelligent. You already know therefore significantly in the case of this topic, produced me personally imagine it from numerous various angles. Its like women and men aren't fascinated unless it is one thing to accomplish with
    Woman gaga! Your personal stuffs nice. All the time deal with it
    up!

    Also visit my blog post link clicker

    ReplyDelete
  8. Today, I went to the beach front with my kids.
    I found a sea shell and gave it to my 4 year old daughter and said "You can hear the ocean if you put this to your ear." She placed the shell to her
    ear and screamed. There was a hermit crab inside and
    it pinched her ear. She never wants to go back! LoL I know this is totally off topic but I had to tell someone!


    my web page; 1.6 cheats

    ReplyDelete
  9. Fastidious answer back in return of this issue with solid
    arguments and describing all about that.

    Look into my webpage ... dragonvale eggs

    ReplyDelete
  10. Thank you, I have recently been looking for info approximately this topic for ages and yours
    is the best I have came upon till now. But, what about the conclusion?

    Are you sure concerning the source?

    Feel free to surf to my webpage; sharecash.org download

    ReplyDelete
  11. You actually make it seem so easy with your presentation but I
    find this topic to be actually something that I think I would never understand.
    It seems too complicated and very broad for me. I'm looking forward for your next post, I'll try to get the hang of it!


    Feel free to surf to my web page Sharecash Hack

    ReplyDelete
  12. I am not sure where you're getting your information, but good topic. I needs to spend some time learning more or understanding more. Thanks for magnificent info I was looking for this information for my mission.

    Also visit my webpage ... Lost Password

    ReplyDelete
  13. I like this post, enjoyed this one thank you for posting.
    "The world is round and the place which may seem like the end may also be only the beginning." by
    George Baker.

    Feel free to surf to my blog; adfocus bot

    ReplyDelete
  14. I visited many sites but the audio quality for audio songs existing
    at this web page is really fabulous.

    Take a look at my site; bypass sharecash

    ReplyDelete
  15. You need to be a part of a contest for one of the best sites on the internet.

    I am going to recommend this blog!

    My weblog - clixsense

    ReplyDelete
  16. Write more, thats all I have to say. Literally, it seems as though
    you relied on the video to make your point. You clearly know what youre talking about,
    why waste your intelligence on just posting videos
    to your site when you could be giving us something informative to read?


    Feel free to surf to my homepage: incentria

    ReplyDelete
  17. Why people still make use of to read news papers when in this technological globe all is available on net?


    my page - Watch TV Series

    ReplyDelete
  18. Great blog! Do you have any helpful hints for aspiring writers?

    I'm planning to start my own site soon but I'm a little lost on everything.
    Would you propose starting with a free platform like Wordpress or go for a
    paid option? There are so many options out there that I'm totally overwhelmed .. Any tips? Thanks!

    Review my web-site ... miscrits locations

    ReplyDelete
  19. It's amazing in support of me to have a site, which is valuable designed for my know-how. thanks admin

    my web blog; Auto Click PTC Sites

    ReplyDelete
  20. I am now not positive where you are getting your information, however great topic.
    I must spend a while learning much more or figuring out more.
    Thanks for magnificent information I was on the lookout for this information
    for my mission.

    My homepage Online TV Shows Collection

    ReplyDelete
  21. I like it when individuals get together and
    share opinions. Great blog, keep it up!

    My web blog - social marketing

    ReplyDelete
  22. I enjoy what you guys are up too. This sort of clever work and reporting!

    Keep up the terrific works guys I've included you guys to my personal blogroll.

    Review my homepage: pdf password remover

    ReplyDelete
  23. Hi colleagues, how is everything, and what you wish for to say on
    the topic of this paragraph, in my view its genuinely amazing in
    favor of me.

    My web blog; psn card generator

    ReplyDelete
  24. It's actually a cool and useful piece of information. I am happy that you simply shared this useful info with us. Please stay us informed like this. Thank you for sharing.

    Also visit my web blog ... Watch TV Series Free

    ReplyDelete
  25. I think what you posted made a bunch of sense. However, what about this?
    suppose you wrote a catchier title? I ain't saying your information isn't solid, but suppose you added a headline to maybe grab a person's attention? I mean "Need to Ship Quantity Query" is a little vanilla. You ought to look at Yahoo's front
    page and watch how they create post titles to get viewers to click.
    You might add a video or a pic or two to get readers excited about what you've written. Just my opinion, it could bring your blog a little bit more interesting.

    Review my web blog Watch TV Series Online For Free

    ReplyDelete
  26. Download All Recent Games, Movies, Apps, Mobile Stuff and everything else for free at http://cinemamoviesdownload.
    tk

    You can download from the following categories

    Full Version Applications for Android, iOS, MAC, Windows
    Full Version Games for Linux, MAC, PC, PS3, Wii, Wii U, XBOX360 and other systems
    Full Movies And Cinema Movies BDRiP, Cam, DVDRiP,
    DVDRiP Old, DVDSCR, HDRiP, R5, SCR, Staff Picks,
    Telecine, Telesync, Workprint
    Full Music Album MP3s and Music Videos Music, Albums,
    iTunes, MViD, Singles/EPs
    Full Version Ebooks eBook Magazines

    Download all you want for free at http://cinemamoviesdownload.

    tk

    ReplyDelete