CREATE OR REPLACE PACKAGE APPS.cux_oe_pkg IS

/**************************************************************************
    File name                : CUX_OE_PKG
    Doc Ref(s)               :
    Project                  : eTrace/Oracle development
    Description              :
                              
    Change History Information
    --------------------------
    Version  Date         Author           Change Reference / Description
    -------  -----------  ---------------  ------------------------------------
    1.0      2009-09-17   MARK             First Version
                          
    **************************************************************************/
    PROCEDURE validate_dn(p_order_number  IN NUMBER,
                          p_delivery_name IN VARCHAR2,
                          o_item_num      OUT VARCHAR2,
                          o_item_rev      OUT VARCHAR2,
                          o_quantity      OUT NUMBER,
                          o_error_flag    OUT VARCHAR2,
                          o_error_mssg    OUT VARCHAR2);
END cux_oe_pkg;
/

CREATE OR REPLACE PACKAGE BODY APPS.cux_oe_pkg IS

    PROCEDURE validate_order_nm(p_order_number IN NUMBER,
                                o_error_flag   OUT VARCHAR2,
                                o_error_mssg   OUT VARCHAR2) IS
        l_return NUMBER;
    BEGIN
        SELECT COUNT(1)
          INTO l_return
          FROM wsh_delivery_details
         WHERE source_header_number = p_order_number
           AND released_status = 'Y'; --Staged/Pick Confirmed
        IF l_return > 0 THEN
            o_error_flag := 'Y';
            o_error_mssg := NULL;
        ELSE
            o_error_flag := 'Invalid order number or no shipable line';
        END IF;
    END validate_order_nm;

    PROCEDURE validate_delivery(p_delivery_name IN VARCHAR2,
                                o_error_flag    OUT VARCHAR2,
                                o_error_mssg    OUT VARCHAR2) IS
        l_return NUMBER;
    BEGIN
        SELECT COUNT(1)
          INTO l_return
          FROM wsh_delivery_details     wdd,
               wsh_new_deliveries       wnd,
               wsh_delivery_assignments wda
         WHERE wnd.NAME = p_delivery_name
           AND wdd.delivery_detail_id = wda.delivery_detail_id
           AND wda.delivery_id = wnd.delivery_id
           AND wdd.released_status = 'Y'; --Staged/Pick Confirmed
        IF l_return > 0 THEN
            o_error_flag := 'Y';
            o_error_mssg := NULL;
        ELSE
            o_error_flag := 'N';
            o_error_mssg := 'Invalid delivery name or no shipable line';
        END IF;
    END validate_delivery;

    PROCEDURE validate_dn(p_order_number  IN NUMBER,
                          p_delivery_name IN VARCHAR2,
                          o_item_num      OUT VARCHAR2,
                          o_item_rev      OUT VARCHAR2,
                          o_quantity      OUT NUMBER,
                          o_error_flag    OUT VARCHAR2,
                          o_error_mssg    OUT VARCHAR2) IS
    BEGIN
        SELECT wdd.revision,
               wdd.requested_quantity,
               msi.concatenated_segments item_num
          INTO o_item_rev,
               o_quantity,
               o_item_num
          FROM wsh_delivery_details     wdd,
               wsh_new_deliveries       wnd,
               wsh_delivery_assignments wda,
               mtl_system_items_kfv     msi
         WHERE wnd.NAME = p_delivery_name
           AND wdd.source_header_number = p_order_number
           AND wdd.delivery_detail_id = wda.delivery_detail_id
           AND wda.delivery_id = wnd.delivery_id
           AND msi.organization_id = wdd.organization_id
           AND msi.inventory_item_id = wdd.inventory_item_id
           AND wdd.released_status = 'Y';
        o_error_flag := 'Y';
        o_error_mssg := NULL;
    EXCEPTION
        WHEN too_many_rows THEN
            o_error_flag := 'N';
            o_error_mssg := 'Exists multiple rows in DN:' ||
                            p_delivery_name;
        WHEN OTHERS THEN
            o_error_flag := 'N';
            o_error_mssg := 'Invalid order number or delivery note.';
    END validate_dn;

END cux_oe_pkg;
/