2 Replies Latest reply on Feb 11, 2013 3:25 PM by Solomon Yakobson

    Re: Remove log messages from loop

    950732
      CURSOR cur_item_rev_child IS
      SELECT ffv.flex_value, --org code
      mp.organization_id
      FROM fnd_flex_values ffv,
      fnd_flex_value_sets ffvs,
      mtl_parameters mp
      WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
      AND ffvs.flex_value_set_name = 'EMR Add Spec Org' -- Changed by WIPRO on 01-OCT-12 (SR # 1078990 )--
      AND NVL(ffv.enabled_flag, 'N') = 'Y'
      AND ffv.flex_value = mp.organization_code
      AND mp.master_organization_id = v_num_master_org;
      CURSOR cur_item_rev (i_num_organization_id NUMBER)
      IS
      SELECT /*+ index(xvasd XXINV_VLVS_ADD_SPEC_DETAILS_N1 )*/mirb.inventory_item_id,
      mirb.organization_id,
      MAX(mirb.revision) item_revision,
      msib.segment1, -- Added by Infosys on 19-May-09
      xvasd.spec_revision spec_revision -- Added by Infosys on 19-May-09
      FROM xxinv_vlvs_add_spec_details xvasd,
      xxinv_vlvs_item_add_spec xvias,
      mtl_parameters mp,
      mtl_system_items_b msib,
      mtl_item_revisions_b mirb
      WHERE xvasd.spec_number = xvias.spec_number
      AND xvasd.spec_type = xvias.spec_type
      AND xvias.spec_type = v_chr_spec_type
      AND xvasd.spec_status='ACTIVE'
      AND xvias.inv_item_id = msib.inventory_item_id
      AND mirb.inventory_item_id=msib.inventory_item_id
      AND xvias.organization_id = msib.organization_id
      AND msib.organization_id = mirb.organization_id
      AND mirb.organization_id = mp.organization_id
      AND mp.organization_id = i_num_organization_id
      --AND LPAD (xvasd.spec_revision, 3, 0) LPAD (b.revision, 3, 0)
      GROUP BY mirb.inventory_item_id,
      mirb.organization_id,
      msib.segment1,
      xvasd.spec_revision;
      BEGIN
      o_chr_errbuf := 'Program Completed Successfully';
      o_num_retcode := 0;
      fnd_file.put_line(fnd_file.output,
      '********************************************************');
      fnd_file.put_line(fnd_file.output,
      ' EMR INV Item Revisions Update Program VLVS');
      fnd_file.put_line(fnd_file.output,
      '********************************************************');
      fnd_file.put_line(fnd_file.output, '');
      --Starting the Program
      fnd_file.put_line(fnd_file.LOG,
      '********************************************************');
      fnd_file.put_line(fnd_file.LOG,
      ' EMR INV Item Revisions Update Program VLVS');
      fnd_file.put_line(fnd_file.LOG,
      '********************************************************');
      fnd_file.put_line(fnd_file.LOG, '');
      fnd_file.put_line(fnd_file.LOG, 'Input Parameter');
      fnd_file.put_line(fnd_file.LOG, '---------------');
      fnd_file.put_line(fnd_file.LOG, 'Debug Mode: ' || v_chr_debug_mode);
      fnd_file.put_line(fnd_file.LOG, '');
      fnd_file.put_line(fnd_file.LOG,
      '-------------------------------------------------------------');

      --
      -- Get the value of the spec type from the lookup. If no value is set then display the error message and raise exception
      --
      IF v_chr_spec_type IS NULL
      THEN
      fnd_file.put_line(fnd_file.LOG,
      'Error: Set a value for the profile: XXINV : Additional Spec Type VLVS');
      RAISE excp_user;
      ELSE
      fnd_file.put_line(fnd_file.LOG,
      'Processing for the addition spec item type: ' ||
      v_chr_spec_type);
      END IF;

      --
      -- Select all the eligible records for processing
      --
      FOR rec_cur_item_rev_child IN cur_item_rev_child
      LOOP

      FOR rec_cur_item_rev IN cur_item_rev (rec_cur_item_rev_child.organization_id)
      LOOP

      -- v_chr_spec_rev := NULL;
      -- v_chr_item_number := NULL; Commented by Infosys on 19-May-09

      /* BEGIN --Start of comments by Infosys on 19-May-09
      SELECT segment1
      INTO v_chr_item_number
      FROM mtl_system_items_b
      WHERE inventory_item_id = rec_cur_item_rev.inventory_item_id
      AND organization_id=rec_cur_item_rev.organization_id;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      v_chr_item_number := NULL;
      fnd_file.put_line(fnd_file.LOG,'Item Number not found in MTL_SYSTEM_ITEMS_B Table for the inv item id '
      ||rec_cur_item_rev.inventory_item_id);
      WHEN OTHERS THEN
      v_chr_item_number := NULL;
      RAISE excp_loop;
      END;*/--End of comments by Infosys on 19-May-09

      /*BEGIN --Start of comments by Infosys on 19-May-09
      SELECT MAX(spec_revision)
      INTO v_chr_spec_rev
      FROM xxinv_vlvs_add_spec_details xvasd,
      xxinv_vlvs_item_add_spec xvias
      WHERE xvasd.spec_number = xvias.spec_number
      AND xvasd.spec_type = xvias.spec_type
      AND xvias.spec_type = v_chr_spec_type
      AND xvias.inv_item_id =rec_cur_item_rev.inventory_item_id
      AND organization_id=rec_cur_item_rev.organization_id
      GROUP BY xvias.inv_item_id,
      organization_id,
      xvias.spec_type;

      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      v_chr_spec_rev := NULL;
      fnd_file.put_line(fnd_file.LOG,'Spec Revision not found in Additional Specs Table for the inv item id '
      ||rec_cur_item_rev.inventory_item_id);
      WHEN OTHERS THEN
      v_chr_spec_rev := NULL;
      RAISE excp_loop;
      END;*/--End of comments by Infosys on 19-May-09

      IF LPAD (rec_cur_item_rev.spec_revision, 3, 0) LPAD (rec_cur_item_rev.item_revision, 3, 0)
      THEN

      BEGIN

      v_num_total_cnt := v_num_total_cnt + 1;
      --to take count of the total records processed
      v_chr_error_flag := 'N'; reset the error flag to N before processing each record
      v_num_revision_id := NULL;

      IF v_num_total_cnt = 1
      THEN
      v_chr_output_hdr := 'ORG CODE' ||
      RPAD('|ITEM NUMBER', 51, ' ') ||
      '|ITEM REV' || '|SPEC REV' ||
      '|ERROR REASON';
      END IF;

      --
      -- Check if the revision on the spec is greater that the revision on the item
      --

      IF LPAD (rec_cur_item_rev.spec_revision, 3, 0) > LPAD (rec_cur_item_rev.item_revision, 3, 0)
      THEN

      BEGIN
      SELECT mtl_item_revisions_b_s.NEXTVAL
      INTO v_num_revision_id
      FROM DUAL;
      EXCEPTION
      WHEN OTHERS THEN
      v_chr_temp_msg := 'Error when getting the new revision id from the sequence MTL_ITEM_REVISIONS_B_S: ' ||
      SQLERRM;
      RAISE excp_loop;
      END;

      -- begin block for assigning values and calling API to update the item revisions
      BEGIN
      v_rec_item_revision.inventory_item_id := rec_cur_item_rev.inventory_item_id;
      v_rec_item_revision.organization_id := rec_cur_item_rev_child.organization_id;
      v_rec_item_revision.revision_id := v_num_revision_id;
      v_rec_item_revision.revision := rec_cur_item_rev.spec_revision;--v_chr_spec_rev;
      v_rec_item_revision.revision_label := rec_cur_item_rev.spec_revision;--v_chr_spec_rev;
      v_rec_item_revision.revision_reason := 'Updated the Item Revision';
      v_rec_item_revision.implementation_date := v_dte_sysdate;
      v_rec_item_revision.effectivity_date := v_dte_sysdate;
      v_rec_item_revision.attribute_category := NULL;
      v_rec_item_revision.attribute1 := NULL;
      v_rec_item_revision.attribute2 := NULL;
      v_rec_item_revision.attribute3 := NULL;
      v_rec_item_revision.attribute4 := NULL;
      v_rec_item_revision.attribute5 := NULL;
      v_rec_item_revision.attribute6 := NULL;
      v_rec_item_revision.attribute7 := NULL;
      v_rec_item_revision.attribute8 := NULL;
      v_rec_item_revision.attribute9 := NULL;
      v_rec_item_revision.attribute10 := NULL;
      v_rec_item_revision.attribute11 := NULL;
      v_rec_item_revision.attribute12 := NULL;
      v_rec_item_revision.attribute13 := NULL;
      v_rec_item_revision.attribute14 := NULL;
      v_rec_item_revision.attribute15 := NULL;
      v_rec_item_revision.description := NULL;
      v_rec_item_revision.creation_date := v_dte_sysdate;
      v_rec_item_revision.created_by := v_num_user_id;
      v_rec_item_revision.last_update_date := v_dte_sysdate;
      v_rec_item_revision.last_updated_by := v_num_user_id;
      v_rec_item_revision.last_update_login := v_num_login_id;
      v_rec_item_revision.request_id := v_num_request_id;
      v_rec_item_revision.program_id := v_num_program_id;
      v_rec_item_revision.program_application_id := v_num_prog_appln_id;
      mtl_item_revisions_util.insert_row(p_item_revision_rec => v_rec_item_revision,
      x_rowid => v_chr_ret_rowid);
      v_num_succ_cnt := v_num_succ_cnt + 1;
      COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
      v_chr_temp_msg := 'Error when updating the revisions: ' ||
      SQLERRM;
      RAISE excp_loop;
      END;

      ELSE -- LPAD (cur_item_rev.spec_revision, 3, 0) < LPAD (cur_item_rev.item_revision, 3, 0)

      v_chr_temp_msg := 'Item Revision is greater than the Spec Revision';

      o_num_retcode := 1;
      v_num_err_cnt := v_num_err_cnt + 1;
      ROLLBACK;
      fnd_file.put_line(fnd_file.LOG, '');
      fnd_file.put_line(fnd_file.LOG,
      'Organization Code: ' ||
      rec_cur_item_rev_child.flex_value); -- added on 05-May-09 by infosys --
      fnd_file.put_line(fnd_file.LOG,
      'Item Number: ' ||
      rec_cur_item_rev.segment1);
      fnd_file.put_line(fnd_file.LOG,
      'Item Revision: ' ||
      rec_cur_item_rev.item_revision); -- v_chr_item_revision_child -- -- added on 05-May-09 by infosys --
      fnd_file.put_line(fnd_file.LOG,
      'Spec Revision: ' ||
      rec_cur_item_rev.spec_revision);
      fnd_file.put_line(fnd_file.LOG,
      'Error: ' ||
      v_chr_temp_msg);
      fnd_file.put_line(fnd_file.LOG,
      '---------------------------------------------------');
      v_chr_output_msg := v_chr_output_msg ||
      RPAD(rec_cur_item_rev_child.flex_value, -- added on 05-May-09 by infosys --
      8,
      ' ') || '|' ||
      RPAD(rec_cur_item_rev.segment1,
      50,
      ' ') || '|' ||
      RPAD(rec_cur_item_rev.item_revision, v_chr_item_revision_child, added on 05-May-09 by infosys --
      8,
      ' ') || '|' ||
      RPAD(rec_cur_item_rev.spec_revision,
      8,
      ' ') || '|' ||
      v_chr_temp_msg ||
      CHR(10);
      v_chr_mail_body := v_chr_mail_body ||
      RPAD(rec_cur_item_rev_child.flex_value, -- added on 05-May-09 by infosys --
      8,
      ' ') || '|' ||
      RPAD(rec_cur_item_rev.segment1,
      50,
      ' ') || '|' ||
      RPAD(rec_cur_item_rev.item_revision, v_chr_item_revision_child added on 05-May-09 by infosys --
      8,
      ' ') || '|' ||
      RPAD(rec_cur_item_rev.spec_revision,
      8,
      ' ') || '|' ||
      v_chr_temp_msg ||
      '
      ';

      --
      -- Display the statistics details in the output file
      --
      fnd_file.put_line(fnd_file.output, '');
      fnd_file.put_line(fnd_file.output,
      'Number of items selected for update: ' ||
      v_num_total_cnt);
      fnd_file.put_line(fnd_file.output,
      'Number of items updated: ' || v_num_succ_cnt);
      fnd_file.put_line(fnd_file.output,
      'Number of items not updated: ' || v_num_err_cnt);
      fnd_file.put_line(fnd_file.output, '');
      fnd_file.put_line(fnd_file.output, v_chr_output_hdr);
      /*
      * Print out the output message from CLOB variable "v_chr_output_msg"
      * Substr each line whenever we find the separator CHR(10)
      * Print each line with fnd_file.output function
      */
      BEGIN
      v_num_offset := 1;
      v_num_instr := 0;
      LOOP
      EXIT WHEN v_num_offset > dbms_lob.getlength(v_chr_output_msg);

      -- Get the position of CHR(10) when first time appearence.
      v_num_instr := dbms_lob.instr(v_chr_output_msg,
      CHR(10),
      v_num_offset,
      1);
      v_num_line_length := v_num_instr - v_num_offset + 1;

      -- Substr the each line and print it out
      fnd_file.put_line(fnd_file.output,
      dbms_lob.substr(v_chr_output_msg,
      v_num_line_length - 1,
      v_num_offset));
      v_num_offset := v_num_offset + v_num_line_length;
      END LOOP;
      END;

      END IF; -- LPAD (cur_item_rev.spec_revision, 3, 0) < LPAD (cur_item_rev.item_revision, 3, 0) --

      EXCEPTION
      WHEN OTHERS THEN

      ROLLBACK;
      fnd_file.put_line(fnd_file.LOG,
      'Porcessing next record as a Error occured in cursor loop cur_item_rev : ' ||
      SQLERRM);

      END;
      END IF;
      END LOOP; -- cur_item_rev --

      END LOOP; -- cur_item_rev_child --

      -- calling the mail procedure --
      IF v_num_err_cnt > 0
      THEN
      BEGIN
      fnd_file.put_line(fnd_file.LOG, '');
      fnd_file.put_line(fnd_file.LOG,
      'Calling send_notification Procedure...');
      send_notification(o_chr_ret_code => v_chr_status,
      o_chr_ret_mesg => v_chr_temp_msg,
      i_chr_subject => 'EMR INV Item Revisions Update Program VLVS - ' ||
      v_dte_sysdate,
      i_chr_body => v_chr_mail_body);
      EXCEPTION
      WHEN OTHERS THEN
      v_chr_temp_msg := 'Error when calling Procedure send_notification .';
      fnd_file.put_line(fnd_file.LOG,
      'Error details : ' || v_chr_temp_msg ||
      ' - ' || SQLERRM);
      RAISE excp_user;
      END;
      END IF;

      EXCEPTION
      /*WHEN excp_loop THEN
      ROLLBACK;
      o_chr_errbuf := o_chr_errbuf ||
      'Program completed with error when getting the max revision';
      o_num_retcode := 2;*/
      WHEN excp_user THEN
      ROLLBACK;
      o_chr_errbuf := o_chr_errbuf || 'Program completed with error';
      o_num_retcode := 2;
      WHEN OTHERS THEN
      ROLLBACK;
      fnd_file.put_line(fnd_file.LOG,
      'Error in Item Revisions Update Program: ' ||
      SQLERRM);
      o_chr_errbuf := o_chr_errbuf || 'Program completed with error';
      o_num_retcode := 2;

      END update_item_revision;