CREATE OR REPLACE PACKAGE APPS.cux_bom_pkg
AS
    PROCEDURE call_expand_bom_sp (
        p_assembly_item   IN       VARCHAR2 ,
        p_assembly_item_id   IN       VARCHAR2 ,
--        p_org_code        IN       VARCHAR2 DEFAULT NULL,
        p_org_id        IN       number DEFAULT null,
        p_identity        IN       VARCHAR2,
        p_order_by        IN       VARCHAR2 DEFAULT NULL,
        p_expand_level    IN       VARCHAR2 DEFAULT 'A',
        o_succ_flag       OUT      VARCHAR2,
        o_error_message   OUT      VARCHAR2);

    PROCEDURE expand_bom_sp (
        p_assembly_item      IN       VARCHAR2,
        p_assembly_item_id   IN       NUMBER,
        p_org_id             IN       VARCHAR2,
        p_identity           IN       VARCHAR2,
        p_order_by           IN       VARCHAR2 DEFAULT NULL,
        p_expand_level       IN       VARCHAR2 DEFAULT 'A',
        o_succ_flag          OUT      VARCHAR2,
        o_error_message      OUT      VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY APPS.cux_bom_pkg
AS
    PROCEDURE call_expand_bom_sp (
        p_assembly_item   IN       VARCHAR2 ,
        p_assembly_item_id   IN       VARCHAR2 ,
--        p_org_code        IN       VARCHAR2 DEFAULT NULL,
        p_org_id        IN       number DEFAULT null,
        p_identity        IN       VARCHAR2,
        p_order_by        IN       VARCHAR2 DEFAULT NULL,
        p_expand_level    IN       VARCHAR2 DEFAULT 'A',
        o_succ_flag       OUT      VARCHAR2,
        o_error_message   OUT      VARCHAR2)
    IS
        l_organization_id    NUMBER;
        l_item_id            NUMBER;
    BEGIN
--        if nvl(p_org_id,0)<=0 then
--        BEGIN
--            SELECT organization_id
--            INTO   l_organization_id
--            FROM   org_organization_definitions
--            WHERE  organization_code = p_org_code;
--        EXCEPTION
--            WHEN NO_DATA_FOUND THEN
--                l_organization_id := 0;
--                o_succ_flag := 'N';
--                o_error_message :=
--                          p_org_code || ' doesnt exist in system, pls check.';
--            WHEN OTHERS THEN
--                l_organization_id := 0;
--                o_succ_flag := 'N';
--                o_error_message := SQLCODE || ' ' || SQLERRM;
--        END;
--        else
--            l_organization_id:=p_org_id;
--        end if;
       

--        BEGIN
--            SELECT inventory_item_id
--            INTO   l_item_id
--            FROM   mtl_system_items_kfv
--            WHERE  concatenated_segments = p_assembly_item
--            AND    organization_id = l_organization_id;
--        EXCEPTION
--            WHEN NO_DATA_FOUND THEN
--                l_item_id := 0;
--                o_succ_flag := 'N';
--                o_error_message :=
--                     p_assembly_item || ' doesnt exist in system, pls check.';
--            WHEN OTHERS THEN
--                l_item_id := 0;
--                o_succ_flag := 'N';
--                o_error_message := SQLCODE || ' ' || SQLERRM;
--        END;

        expand_bom_sp (p_assembly_item          => p_assembly_item,
                       p_assembly_item_id       => p_assembly_item_id,
                       p_org_id                 => p_org_id,
                       p_identity               => p_identity,
                       p_order_by               => p_order_by,
                       p_expand_level           => p_expand_level,
                       o_succ_flag              => o_succ_flag,
                       o_error_message          => o_error_message);
    END;

---------------------------------------------------------------------------------------
-- p_expand_level parameter define as follow
-- A means expand all components all herichy under this assembly part number
    PROCEDURE expand_bom_sp (
        p_assembly_item      IN       VARCHAR2,
        p_assembly_item_id   IN       NUMBER,
        p_org_id             IN       VARCHAR2,
        p_identity           IN       VARCHAR2,
        p_order_by           IN       VARCHAR2 DEFAULT NULL,
        p_expand_level       IN       VARCHAR2 DEFAULT 'A',
        o_succ_flag          OUT      VARCHAR2,
        o_error_message      OUT      VARCHAR2)
    IS
        v_item_id        NUMBER;
        l_order_by       VARCHAR2 (100);
        v_assem_count    NUMBER         := 0;
        v_level          VARCHAR2 (50);

        CURSOR cur_item_id
        IS
            SELECT   bom.assembly_item_id,
                     bic.component_item_id,
                     bic.item_num,
                     bic.component_quantity,
                     bic.created_by,
                     bic.creation_date,
                     bic.last_updated_by,
                     bic.last_update_date,
                     bic.component_remarks,
                     bic.attribute1,
                     bic.attribute2,
                     bom.organization_id,
                     bic.wip_supply_type
            FROM     apps.bom_bill_of_materials bom,
                     apps.bom_inventory_components bic
            WHERE    bom.common_bill_sequence_id = bic.bill_sequence_id
            AND      bom.organization_id = p_org_id
            AND      bic.disable_date IS NULL
            AND      bic.implementation_date IS NOT NULL
            AND      bic.effectivity_date IS NOT NULL
            AND      bom.assembly_item_id = p_assembly_item_id
            ORDER BY bic.item_num;
    BEGIN
        FOR deta_item IN cur_item_id LOOP
            v_assem_count := 0;
            l_order_by :=
                   (CASE
                        WHEN NVL (p_order_by, 'a') = 'a' THEN ''
                        ELSE TRIM (p_order_by)
                    END)
                || TRIM (TO_CHAR (cur_item_id%ROWCOUNT, '0999') );
            v_item_id := deta_item.component_item_id;
            apps.cux_bom_pkg.expand_bom_sp (p_assembly_item,
                                            deta_item.component_item_id,
                                            p_org_id,
                                            p_identity,
                                            l_order_by,
                                            p_expand_level,
                                            o_succ_flag           => o_succ_flag,
                                            o_error_message       => o_error_message);

            IF p_expand_level = 'A' THEN
                INSERT INTO apps.cux_bom_expand
                            (p_item, assembly_item_id,
                             component_item_id,
                             item_num,
                             component_quantity,
                             created_by, creation_date,
                             last_updated_by,
                             last_update_date, identity,
                             order_by, component_remarks,
                             organization_id)
                     VALUES (p_assembly_item, deta_item.assembly_item_id,
                             deta_item.component_item_id,
                             deta_item.item_num,
                             deta_item.component_quantity,
                             deta_item.created_by, deta_item.creation_date,
                             deta_item.last_updated_by,
                             deta_item.last_update_date, p_identity,
                             l_order_by, deta_item.component_remarks,
                             deta_item.organization_id);
            ELSE
                SELECT COUNT (bom.assembly_item_id)
                INTO   v_assem_count
                FROM   apps.bom_bill_of_materials bom,
                       apps.bom_inventory_components bic
                WHERE  bom.common_bill_sequence_id = bic.bill_sequence_id
                AND    bom.organization_id = p_org_id
                AND    bom.implementation_date IS NOT NULL
                AND    bic.disable_date IS NULL
                AND    bic.implementation_date IS NOT NULL
                AND    bom.assembly_item_id = deta_item.component_item_id;

                IF p_expand_level = 'M' AND v_assem_count > 0 THEN
                    INSERT INTO apps.cux_bom_expand
                                (p_item,
                                 assembly_item_id,
                                 component_item_id,
                                 item_num,
                                 component_quantity,
                                 created_by,
                                 creation_date,
                                 last_updated_by,
                                 last_update_date, identity,
                                 order_by, component_remarks,
                                 organization_id)
                         VALUES (p_assembly_item,
                                 deta_item.assembly_item_id,
                                 deta_item.component_item_id,
                                 deta_item.item_num,
                                 deta_item.component_quantity,
                                 deta_item.created_by,
                                 deta_item.creation_date,
                                 deta_item.last_updated_by,
                                 deta_item.last_update_date, p_identity,
                                 l_order_by, deta_item.component_remarks,
                                 deta_item.organization_id);
                END IF;

                IF p_expand_level = 'P' AND v_assem_count = 0 THEN
                    INSERT INTO apps.cux_bom_expand
                                (p_item,
                                 assembly_item_id,
                                 component_item_id,
                                 item_num,
                                 component_quantity,
                                 created_by,
                                 creation_date,
                                 last_updated_by,
                                 last_update_date, identity,
                                 order_by, component_remarks,
                                 organization_id)
                         VALUES (p_assembly_item,
                                 deta_item.assembly_item_id,
                                 deta_item.component_item_id,
                                 deta_item.item_num,
                                 deta_item.component_quantity,
                                 deta_item.created_by,
                                 deta_item.creation_date,
                                 deta_item.last_updated_by,
                                 deta_item.last_update_date, p_identity,
                                 l_order_by, deta_item.component_remarks,
                                 deta_item.organization_id);
                END IF;
            END IF;
        END LOOP;
       
    EXCEPTION
        WHEN OTHERS THEN
            o_succ_flag := 'N';
            o_error_message := SQLCODE || ' ' || SQLERRM;
    END;
END;

/*
--DROP TABLE LERADO.BOM_EXPAND CASCADE CONSTRAINTS;
CREATE TABLE apps.CUX_BOM_EXPAND
(
  ASSEMBLY_ITEM_ID    NUMBER                    NOT NULL,
  COMPONENT_ITEM_ID   NUMBER                    NOT NULL,
  ITEM_NUM            NUMBER,
  COMPONENT_QUANTITY  NUMBER                    NOT NULL,
  CREATED_BY          NUMBER                    NOT NULL,
  CREATION_DATE       DATE                      NOT NULL,
  LAST_UPDATED_BY     NUMBER                    NOT NULL,
  LAST_UPDATE_DATE    DATE                      NOT NULL,
  P_ITEM              VARCHAR2(40 BYTE),
  IDENTITY            VARCHAR2(50 BYTE),
  ORDER_BY            VARCHAR2(100 BYTE),
  COMPONENT_REMARKS   VARCHAR2(240 BYTE),
  ORGANIZATION_ID NUMBER null
)

--ALTER TABLE apps.CUX_BOM_EXPAND add(ORGANIZATION_ID NUMBER null)

*/
/