5 Replies Latest reply: Jan 19, 2012 1:28 PM by 911908 RSS

    Creating Correspondence Sent report in between two dates using crtiteria

    911908
      Dear all

      I am trying to create a report in Info maker (version 10.5 build 4523) for Correspondence sent module to get the log, I am trying to get the report in between a specific date , I created a query and check the Column Specification Prompt value for correspond date and I imported report but report only generate that specified date only, I need between a specific dates For Example I need correspondence Sent log report generate in Between 1-dec-2011 and 30-dec-2011, any possible way to create such report.
      This is query I wrote
      SELECT vnmt1 . company_name ,
      corr . correspond_date ,
      corr . base_item_type ,
      corr . reference_number ,
      corr . description ,
      corr . spec_section ,
      corr . item_status ,
      corr . logged_by ,
      corr . start_date ,
      corr . completion_date ,
      proj1 . project_name ,
      proj1 . company_name ,
      proj1 . job_number ,
      proj1 . project_number ,
      proj1 . project_title ,
      corr . response_date ,
      corr . to_vendor ,
      item . sys_acronym ,
      sent1 . Response_WBS_Reference ,
      corr . log_number

      FROM corr ,
      (select project_name , company_name , vendor_abbrev from VNMT ) vnmt1 ,
      (select master_key , Response_WBS_Reference From Sent ) Sent1 ,
      (select project_title , project_name , job_number , project_number , company_name from PROJ ) proj1 ,
      item

      WHERE ( corr . to_vendor = vnmt1 . vendor_abbrev ) and
      ( Corr . master_key = sent1 . master_key ) and
      ( corr . project_name = proj1 . project_name ) and
      ( item . acronym = corr . item_type )
      and ( ( Corr.project_name = :project_name ) AND
      ( vnmt1.project_name = :vendor_type ) )

      Any Help in this subject is much appreciated and thanks in advance.
        • 1. Re: Creating Correspondence Sent report in between two dates using crtiteria
          Sunnybains-Oracle
          To prompt on a date, Infomaker must be told which column for which to request data. This is done by clicking on View > Column Specifications

          All columns which are being selected from the database are shown (Note: a column does not need to be displayed on the report to be prompted upon, it only needs to be included in the SELECT statement used to grab the data which populates the report). Put a checkmark next to the desired date column.

          Once the report has been imported into Contract Management it then shows a prompt window, in which your data will be entered.
          For the date range you can put as following example:

          " >=Jan 1,2012
          AND <=1/30/2012 "

          (Not including the quotation marks " " )
          • 2. Re: Creating Correspondence Sent report in between two dates using crtiteria
            911908
            Thanks For your Reply. The prompt is coming up When I insert a single date the report is generating , but when I insert the Date Range as u specified then it is coming the error Saying "DocumentSessionBean.getDocumentDataException occured while creating the report: String index out of range: -4 " I insert >= Dec 1,2011 AND <= 1/30/2011 Do you have any idea ?

            Edited by: 908905 on Jan 19, 2012 3:27 AM

            Edited by: 908905 on Jan 19, 2012 3:30 AM
            • 3. Re: Creating Correspondence Sent report in between two dates using crtiteria
              Sunnybains-Oracle
              it seems your search is after Dec 1st 2011 and before 30th Jan 2011. That's invalid.

              if you can access the oracle support portal you can search for KM 903153.1 "How to Prompt for a Date Range in a Contract Management Report "

              it shows for a date range it should be something like this: -

              Criteria: >= Dec 1,2011
              Or: AND <= 1/30/2012
              • 4. Re: Creating Correspondence Sent report in between two dates using crtiteria
                911908
                Thank you So much for your Prompt reply I tried the way as you discribed then am getting one more Error message
                *"Select Error: SQLSTATE = S0001 Unclosed quotation mark after the character string ''.. SQLSYNTAX="SELECT vnmt1 . company_name , corr . correspond_date , corr . base_item_type , corr . reference_number , corr . description , corr . spec_section , corr . item_status , corr . logged_by , corr . start_date , corr . completion_date , proj1 . project_name , proj1 . company_name , proj1 . job_number , proj1 . project_number , proj1 . project_title , corr . response_date , corr . to_vendor , item . sys_acronym , sent1 . Response_WBS_Reference , corr . log_number FROM corr , (select project_name , company_name , vendor_abbrev from VNMT ) vnmt1 , (select master_key , Response_WBS_Reference From Sent ) Sent1 , (select project_title , project_name , job_number , project_number , company_name from PROJ ) proj1 , item WHERE ( corr . to_vendor = vnmt1 . vendor_abbrev ) and ( Corr . master_key = sent1 . master_key ) and ( corr . project_name = proj1 . project_name ) and ( item . acronym = corr . item_type ) and ( ( Corr.project_name = 'W00ZPM01' ) AND ( vnmt1.project_name = 'W00ZPM01' ) ) AND ((correspond_date >= CONVERT(VARCHAR(10),CAST('"* Do Yo have any Idea about this message ? Once again thank you so much for your reply.
                • 5. Re: Creating Correspondence Sent report in between two dates using crtiteria
                  911908
                  Thank you so much for your answer , It was my mistake to enter Date Format as wrong thanks for your support