Oracle eBS經驗分享

UPDATE  wsh_delivery_details wdd
SET     released_status = 'D'
       ,src_requested_quantity = 0
       ,src_requested_quantity2 = decode (src_requested_quantity2
                                         ,NULL
                                         ,NULL
                                         ,0)
       ,requested_quantity = 0
       ,requested_quantity2 = decode (requested_quantity2
                                     ,NULL
                                     ,NULL
                                     ,0)
       ,shipped_quantity = 0
       ,shipped_quantity2 = decode (shipped_quantity2
                                   ,NULL
                                   ,NULL
                                   ,0)
       ,picked_quantity = 0
       ,picked_quantity2 = decode (picked_quantity2
                                  ,NULL
                                  ,NULL
                                  ,0)
       ,cycle_count_quantity = 0
       ,cycle_count_quantity2 = decode (src_requested_quantity2
                                       ,NULL
                                       ,NULL
                                       ,0)
       ,cancelled_quantity = decode (requested_quantity
                                    ,0
                                    ,cancelled_quantity
                                    ,requested_quantity)
       ,cancelled_quantity2 = decode (requested_quantity2
                                     ,NULL
                                     ,NULL
                                     ,0
                                     ,cancelled_quantity2
                                     ,requested_quantity2)
       ,subinventory = NULL
       ,locator_id = NULL
       ,lot_number = NULL
       ,serial_number = NULL
       ,to_serial_number = NULL
       ,transaction_temp_id = NULL
       ,revision = NULL
       ,ship_set_id = NULL
       ,inv_interfaced_flag = 'X'
       ,oe_interfaced_flag = 'X'
       ,last_updated_by = - 1
       ,last_update_date = SYSDATE
WHERE   EXISTS
        (
        SELECT  1
        FROM    oe_order_lines_all oola
               ,oe_order_headers_all ooha
               ,mtl_system_items_b msib
               ,mtl_parameters mp
        WHERE   wdd.source_line_id = oola.line_id
        AND     msib.inventory_item_id = wdd.inventory_item_id
        AND     msib.organization_id = wdd.organization_id
        AND     wdd.organization_id = mp.organization_id
        AND     oola.header_id = ooha.header_id
        AND     wdd.source_code = 'OE'
        AND     wdd.released_status NOT IN ('C','D')
        AND     oola.org_id IN (86,88)
        AND     oola.flow_status_code = 'CANCELLED'
        AND     ooha.open_flag = 'N'
        AND     EXISTS
                (
                SELECT  1
                FROM    oe_order_lines_all ool
                WHERE   ool.line_id = wdd.source_line_id
                AND     oola.open_flag = 'N'
                )
        );