1 Reply Latest reply on Jul 15, 2019 3:24 PM by Paul Goodfellow

    Using APIs to Update the Long Description on an Operation

    Paul Goodfellow

      I tried writing a script to update the Long Description on a Work Order Operation. The Work Order and its Operation were created from an Asset Activity. My PL/SQL is below and unfortunately it does not work. Is there anything I am doing wrong? Is there a rule in the system that makes the Long Description read-only if it comes from an Asset Activity? Any suggestions would be welcome.han

       

      Thanks

       

       

       

      DECLARE

       

       

         v_orgid                      wip_operations.organization_id%TYPE;

         v_wip_entityid               wip_operations.wip_entity_id%TYPE;

         v_op_seq                     wip_operations.operation_seq_num%TYPE;

         v_long_desc                  wip_operations.long_description%TYPE;

         l_eam_wo_rec                 eam_process_wo_pub.eam_wo_rec_type;

         l_eam_op_tbl                 eam_process_wo_pub.eam_op_tbl_type;

         l_eam_op_rec                 eam_process_wo_pub.eam_op_rec_type;

         l_eam_op_network_tbl         eam_process_wo_pub.eam_op_network_tbl_type;

         l_eam_res_tbl                eam_process_wo_pub.eam_res_tbl_type;

         l_eam_res_inst_tbl           eam_process_wo_pub.eam_res_inst_tbl_type;

         l_eam_sub_res_tbl            eam_process_wo_pub.eam_sub_res_tbl_type;

         l_eam_res_usage_tbl          eam_process_wo_pub.eam_res_usage_tbl_type;

         l_eam_mat_req_tbl            eam_process_wo_pub.eam_mat_req_tbl_type;

         l_out_eam_direct_items_tbl   eam_process_wo_pub.eam_direct_items_tbl_type;

         x_eam_wo_rec                 eam_process_wo_pub.eam_wo_rec_type;

         x_eam_op_tbl                 eam_process_wo_pub.eam_op_tbl_type;

         x_eam_op_network_tbl         eam_process_wo_pub.eam_op_network_tbl_type;

         x_eam_res_tbl                eam_process_wo_pub.eam_res_tbl_type;

         x_eam_res_inst_tbl           eam_process_wo_pub.eam_res_inst_tbl_type;

         x_eam_sub_res_tbl            eam_process_wo_pub.eam_sub_res_tbl_type;

         x_eam_res_usage_tbl          eam_process_wo_pub.eam_res_usage_tbl_type;

         x_eam_mat_req_tbl            eam_process_wo_pub.eam_mat_req_tbl_type;

         x_eam_direct_items_tbl       eam_process_wo_pub.eam_direct_items_tbl_type;

         x_status                     VARCHAR2 (4000);

         x_msg_cnt                    NUMBER;

         x_msg_data                   VARCHAR2 (4000);

        

         CURSOR c_find_opinfo (cp_orgid wip_operations.organization_id%TYPE, cp_wipentyid wip_operations.wip_entity_id%TYPE) IS

         SELECT operation_seq_num, long_description

         FROM   wip_operations

         WHERE  organization_id = cp_orgid

         AND    wip_entity_id = cp_wipentyid

         ORDER BY operation_seq_num ASC;

        

      BEGIN

       

       

            v_orgid := 10179;

            v_wip_entityid := 29749372;

       

       

            OPEN c_find_opinfo (cp_orgid         => v_orgid

                                    ,cp_wipentyid       => v_wip_entityid);

       

       

            FETCH c_find_opinfo INTO v_op_seq, v_long_desc;

       

       

        IF c_find_opinfo%NOTFOUND THEN

           CLOSE c_find_opinfo;

           dbms_output.put_line('No Operations Found');

        ELSE

           CLOSE c_find_opinfo;

       

       

           dbms_output.put_line('Transaction type being used is '||eam_process_wo_pvt.g_opr_update);

           dbms_output.put_line('Operation Seq Num '||v_op_seq||' Long Description (' || LENGTH(v_long_desc) || '): '||v_long_desc);

       

      v_long_desc := v_long_desc || CHR(10) || ' LINE APPENDED BY ME';

       

       

           dbms_output.put_line('Updating Long Description to (' || LENGTH(v_long_desc) || '): '||v_long_desc);

       

       

           l_eam_wo_rec.header_id                       := 1;

           l_eam_wo_rec.batch_id                        := 1;

           l_eam_wo_rec.row_id                          := NULL;

           l_eam_wo_rec                                 := NULL;

           l_eam_wo_rec.wip_entity_id                   := v_wip_entityid;

           l_eam_wo_rec.organization_id                 := v_orgid;

           l_eam_wo_rec.transaction_type                := eam_process_wo_pvt.g_opr_update;

       

       

           l_eam_op_rec.header_id                       := l_eam_wo_rec.header_id;

           l_eam_op_rec.batch_id                        := l_eam_wo_rec.batch_id;

           l_eam_op_rec.row_id                          := l_eam_wo_rec.row_id;

           l_eam_op_rec.organization_id                 := l_eam_wo_rec.organization_id;

           l_eam_op_rec.operation_seq_num               := v_op_seq;

           l_eam_op_rec.long_description                := v_long_desc;

           l_eam_op_rec.transaction_type                := eam_process_wo_pvt.g_opr_update;

       

       

               l_eam_op_tbl(1) := l_eam_op_rec;

       

       

           eam_process_wo_pub.process_wo

                             (p_bo_identifier             => 'EAM',

                              p_api_version_number        => 1.0,

                              p_init_msg_list             => TRUE,

                              p_commit                    => 'Y',

                              p_eam_wo_rec                => l_eam_wo_rec,

                              p_eam_op_tbl                => l_eam_op_tbl,

                              p_eam_op_network_tbl        => l_eam_op_network_tbl,

                              p_eam_res_tbl               => l_eam_res_tbl,

                              p_eam_res_inst_tbl          => l_eam_res_inst_tbl,

                              p_eam_sub_res_tbl           => l_eam_sub_res_tbl,

                              p_eam_res_usage_tbl         => l_eam_res_usage_tbl,

                              p_eam_mat_req_tbl           => l_eam_mat_req_tbl,

                              p_eam_direct_items_tbl      => l_out_eam_direct_items_tbl,

                              x_eam_wo_rec                => x_eam_wo_rec,

                              x_eam_op_tbl                => x_eam_op_tbl,

                              x_eam_op_network_tbl        => x_eam_op_network_tbl,

                              x_eam_res_tbl               => x_eam_res_tbl,

                              x_eam_res_inst_tbl          => x_eam_res_inst_tbl,

                              x_eam_sub_res_tbl           => x_eam_sub_res_tbl,

                              x_eam_res_usage_tbl         => x_eam_res_usage_tbl,

                              x_eam_mat_req_tbl           => x_eam_mat_req_tbl,

                              x_eam_direct_items_tbl      => x_eam_direct_items_tbl,

                              x_return_status             => x_status,

                              x_msg_count                 => x_msg_cnt,

                              p_debug                     => 'N'

      --                         p_output_dir                => NULL,

      --                         p_debug_filename            => NULL,

      --                         p_debug_file_mode           => 'w'

                             );

           COMMIT;

       

       

           DBMS_OUTPUT.put_line ('After UPDATE Status : ' || x_status);

           IF x_msg_cnt >= 1 AND x_status <> 'S' THEN

              DBMS_OUTPUT.put_line ('Message Count : ' || x_msg_cnt);

      END IF;

        END IF;

       

       

        --Check to see if the long description has been updated

            OPEN c_find_opinfo (cp_orgid         => v_orgid

                                    ,cp_wipentyid       => v_wip_entityid);

       

       

            FETCH c_find_opinfo INTO v_op_seq, v_long_desc;

       

       

        IF c_find_opinfo%NOTFOUND THEN

           CLOSE c_find_opinfo;

           dbms_output.put_line('No Operations Found');

        ELSE

           CLOSE c_find_opinfo;

       

       

           dbms_output.put_line('Operation Seq Num '||v_op_seq||' Long Description: '||v_long_desc);

            END IF;

      END;