/*NEW PUBLIC API - INV_ABC_ASSIGNMENTS_PUB .CREATE_ABC_ASSIGNMENTS IS ADDED TO INSERT/UPDATE ABC ASSIGNMENTS

IF THE ITEM ALREADY EXISTS IT WILL BE UPDATED/ASSIGNED TO THE NEW CLASS, ELSE WILL BE ADDED TO THE CLASS PASSED AS PARAMETER.*/

/* public api PLS file INVPAASS.pls ,INVPAASB.pls  */ 

-- organization V1( master organization, M1 (child org )

-- created item(s) ABC_API_1, ABC_API_2, ABC_API_3

-- This data is from the table MTL_SYSTEM_ITEMS_B

--SEGMENT1                                 INVENTORY_ITEM_ID

---------------------------------------- -----------------

--ABC_API_1                                           24535205

-- created abc group ABC_API_EX   in M1

-- This data is from the table MTL_ABC_ASSIGNMENT_GROUPS

--ASSIGNMENT_GROUP_NAME                    ASSIGNMENT_GROUP_ID

---------------------------------------- -------------------

-- ABC_API_EX                                             10250 

-- These are there clases used in  abc group ABC_API_EX  

-- This data is from  MTL_ABC_ASSGN_GROUP_CLASSES 

--ABC_CLASS_NAME                           ABC_CLASS_ID

---------------------------------------- ------------

--Class A                                            56 

--CLASS B                                            57

--Class C                                            58 

-- In the demo accept statements will be use to get  the 

-- three required . 

SET SERVEROUTPUT ON ;

ACCEPT 1 NUMBER PROMPT 'Please enter the  inventory_item_id :'

ACCEPT 2 NUMBER PROMPT 'Please enter the  assignment_group_id :'

ACCEPT 3 NUMBER PROMPT 'Please enter the  abc_class_id :'

DECLARE

ITEM_ID NUMBER ;

ASSIGN_GRP_ID NUMBER;

ABC_CLS_ID  NUMBER;

Cursor ITEM IS 

select msi.inventory_item_id

from apps.mtl_system_items_b msi,

     apps.mtl_abc_assignments maa,

     apps.mtl_abc_assignment_groups maag,

     apps.mtl_abc_classes mac,

     apps.mtl_abc_compile_headers mach

where msi.organization_id = :P_SOURCE_ORG_ID

and maa.inventory_item_id = msi.inventory_item_id

and maa.assignment_group_id = maag.assignment_group_id

and maag.organization_id = :P_SOURCE_ORG_ID

and maag.compile_id = mach.compile_id

and maa.abc_class_id = mac.abc_class_id 

and exists (select 1

            from apps.mtl_system_items_b msi2

            where msi2.organization_id = :P_DESTINATION_ORG_ID

            and msi2.inventory_item_id = msi.inventory_item_id);

Cursor TGT IS

  select maag.assignment_group_id,mac.abc_class_id

from apps.mtl_system_items_b msi,

     apps.mtl_abc_assignments maa,

     apps.mtl_abc_assignment_groups maag,

     apps.mtl_abc_classes mac,

     apps.mtl_abc_compile_headers mach

where msi.organization_id = :P_DESTINATION_ORG_ID

and maa.inventory_item_id = msi.inventory_item_id

and maa.assignment_group_id = maag.assignment_group_id

and maag.organization_id = :P_DESTINATION_ORG_ID

and maag.compile_id = mach.compile_id

and maa.abc_class_id = mac.abc_class_id;

-- These varible are to capture the success of the api 

-- and print the failure messages 

LSTATUS VARCHAR2(1); -- return status 

LMSG_COUNT NUMBER;   -- message count 

LMSG_DATA VARCHAR2(240); -- message data 

V_MSG VARCHAR2(4000);

I NUMBER ;

j NUMBER ;

ABC_ITEM  INV_ABC_Assignments_PUB.ABC_ASSIGNMENTS_REC_TYPE;

--------------------------------------------------------------------------------

--  abc_assignments_rec_type record type

--  Record type to hold a abc assignment required columns

--                  inventory_item_id

--                  assignmentg_group_id

--                  abc_class_id

BEGIN 

-- these next three line will need to be modified

-- useed in a larger program to add a large number of items programaticly

for i in ITEM LOOP

  For J in TGT LOOP

ABC_ITEM.INVENTORY_ITEM_ID := i.inventory_item_id;

ABC_ITEM.ASSIGNMENT_GROUP_ID := j.assignment_group_id;

ABC_ITEM.abc_class_id := j.abc_class_id ;

 INV_ABC_Assignments_PUB.CREATE_ABC_ASSIGNMENTS( FND_API.G_TRUE,

                                                FND_API.G_TRUE,

                                                1.0,

                                                LSTATUS,

                                                LMSG_COUNT,

                                                LMSG_DATA ,

                                                ABC_ITEM);

                                                

                                                

        DBMS_OUTPUT.PUT_LINE ( 'Status :' || LSTATUS);

         

          IF (lstatus is null  or lstatus = 'S'  ) THEN -- changed for bug 22065914  

              DBMS_OUTPUT.PUT_LINE ( 'SUCCESS');

              COMMIT;

              DBMS_OUTPUT.PUT_LINE ( 'Item_id  ' || ITEM_ID || ' add to group ' || ASSIGN_GRP_ID || ' and Class id  ' || ABC_CLS_ID   );

              ELSE

              DBMS_OUTPUT.PUT_LINE ( 'FAILED, with Status :' || LSTATUS);

              DBMS_OUTPUT.PUT_LINE ( 'message count :' || LMSG_COUNT);

              dbms_output.put_line ( 'message :' || lmsg_data);

          IF ( lmsg_count > 1 ) THEN

             FOR i IN 1 .. lmsg_count

             LOOP

                fnd_msg_pub.get ( p_msg_index => i , p_encoded =>FND_API.G_FALSE,

                p_data => v_msg , p_msg_index_out => j ) ;

                dbms_output.put_line ( 'message :' || v_msg);

             END LOOP ;

           end if;

        END IF;

END LOOP;

 

 END LOOP;

END;