2 Replies Latest reply on Feb 20, 2014 5:22 PM by 801619

    inv_move_order_pub.process_move_order

    801619

      Hi,

       

      I'm working on EBS R12, aim is to create move orders using 'INV_MOVE_ORDER_PUB.PROCESS_MOVE_ORDER'............I'm getting details from third party system and to create move orders in EBS.

       

      Code:

       

      PROCEDURE create_move_order

       

        IS

      CURSOR C_HD IS SELECT BATCH_ID,ORGANIZATION_ID,FROM_SUBINVENTORY_CODE,TO_SUBINVENTORY_CODE FROM XXAD_MTL_HD;

       

      CURSOR C_LN(P_BATCH_ID) IS SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,QUANTITY,UOM,FROM_SUBINVENTORY_CODE,TO_SUBINVENTORY_CODE

      FROM XXAD_MTL_LN WHERE BATCH_ID=P_BATCH_ID;

       

        l_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec;

        l_line_tbl inv_move_order_pub.trolin_tbl_type := inv_move_order_pub.g_miss_trolin_tbl;

        x_hdr_val_rec inv_move_order_pub.trohdr_val_rec_type;

        x_line_val_tbl inv_move_order_pub.trolin_val_tbl_type;

        v_msg_index_out NUMBER;

        l_rsr_type inv_reservation_global.mtl_reservation_tbl_type;

       

        BEGIN

      FOR R_HD IN C_HD LOOP

       

      x_return_status:=null;

      x_msg_data := null;

      x_msg_count := null;

        l_line_tbl.DELETE;

        x_line_tbl.DELETE;

       

        l_hdr_rec.date_required := SYSDATE;

        l_hdr_rec.header_status := inv_globals.g_to_status_incomplete;

        l_hdr_rec.organization_id := R_HD.ORGANIZATION_ID;

        l_hdr_rec.status_date := SYSDATE;

        l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;

        l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;

        l_hdr_rec.db_flag := fnd_api.g_true;

        l_hdr_rec.operation := inv_globals.g_opr_create;

        l_hdr_rec.description := 'Test Move Order';

        l_hdr_rec.from_subinventory_code :=R_HD.to_subinventory_code';

       

      FOR R_LN IN C_LN(R_HD.BATCH_ID) LOOP

        l_line_tbl (r_ln.batch_id).date_required := SYSDATE;

        l_line_tbl (r_ln.batch_id).inventory_item_id := R_LN.INVNETORY_ITEM_ID;

        l_line_tbl (r_ln.batch_id).line_id := fnd_api.g_miss_num;

        l_line_tbl (r_ln.batch_id).line_status := inv_globals.g_to_status_incomplete;

        l_line_tbl (r_ln.batch_id).transaction_type_id := inv_globals.g_type_transfer_order_issue;

        l_line_tbl (r_ln.batch_id).organization_id := R_LN.organization_id;

        l_line_tbl (r_ln.batch_id).quantity := R_LN.quantity;

        l_line_tbl (r_ln.batch_id).status_date := SYSDATE;

        l_line_tbl (r_ln.batch_id).uom_code :=R_LN.uom_code;

        l_line_tbl (r_ln.batch_id).db_flag := fnd_api.g_true;

        l_line_tbl (r_ln.batch_id).operation := inv_globals.g_opr_create;

        l_line_tbl (r_ln.batch_id).from_subinventory_code :=R_LN.FROM_SUBINVENTORY_CODE;

        END LOOP;

       

        inv_move_order_pub.process_move_order (p_api_version_number => 1.0

        , p_init_msg_list => fnd_api.g_false

        , p_return_values => fnd_api.g_false

        , p_commit => fnd_api.g_false

        , x_return_status => x_return_status

        , x_msg_count => x_msg_count

        , x_msg_data => x_msg_data

        , p_trohdr_rec => l_hdr_rec

        , p_trolin_tbl => l_line_tbl

        , x_trohdr_rec => x_hdr_rec

        , x_trohdr_val_rec => x_hdr_val_rec

        , x_trolin_tbl => x_line_tbl

        , x_trolin_val_tbl => x_line_val_tbl

        );

        DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status);

        DBMS_OUTPUT.put_line ('Message Count is :' || x_msg_count);

        DBMS_OUTPUT.put_line ('Move Order Number is :' || x_hdr_rec.request_number);

        DBMS_OUTPUT.put_line ('Move Order ID is :' || x_hdr_rec.header_id);

        DBMS_OUTPUT.put_line ('Number of Lines Created are :' || x_line_tbl.COUNT);

       

        IF x_return_status = 'S'

        THEN

        COMMIT;

        ELSE

        ROLLBACK;

        END IF;

       

        IF x_msg_count > 0

        THEN

        FOR v_index IN 1 .. x_msg_count

        LOOP

        fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);

        x_msg_data := SUBSTR (x_msg_data, 1, 200);

        DBMS_OUTPUT.put_line (x_msg_data);

        DBMS_OUTPUT.put_line ('============================================================');

        END LOOP;

        END IF;

       

      END LOOP;

      EXCEPTION

      WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLERRM);

        END;

       

      ISSUE: If the header cursor has one record, it's creating successful, but if the header has more than one record, if i process, it's creating move orders; but if any record with return status 'E' and msg_count-for example 3, then for next record,it's carrying msg_count,msg_data though it's return_status is 'S'; If i individually run that successful record, it's giving msg_count is zero and msg_data is null.

       

      Why it's carrying forward msg_count and msg_data to next row if i run in the bulk....Please help me at the earliest, any flaw in the code,i'm resetting x_msg_count,x_msg_data already.

       

      Kindly help.

       

      Thanks.