1 Reply Latest reply: Feb 15, 2012 10:00 AM by Crawler409 RSS

    Reference Data Model Parameters in the Bursting SQL query?

    Crawler409
      Good Afternoon:

      I have been trying unsuccessfully to pass the APP_USER item from my APEX application (4.1.0.00.32) to the SQL query for bursting in BI Publisher (11.1.1.5.0)

      Anyone know how to reference passed Data Model Parameters in the Bursting SQL query?


      Currently I am passing the APP_USER value to a Data Model Parameter (:SEND_TO) and I also have the APP_USER value in the Report Data Set Results. I just cannot seem to find a way to reference it in the SQL Query for Bursting.

      The whole report bursts just fine when I have a static value in the SQL Query and also when I insert the APP_USER into a table. Inserting into a table doesn't work well when you have multiple users trying to burst at the same time.



      My report parameters are coming across fine but the job process error I receive is ORA-00911: invalid character (which I am assuming is because of the semicolon).


      My Bursting SQL:
      SELECT DISTINCT
          epp.SSN_SM KEY,
          'NGB4100_SENIOR' TEMPLATE,
          'PDF' TEMPLATE_FORMAT,
          'en-US' LOCALE,
          'NGB4100_1B_' || PAY_GR OUTPUT_NAME,
          'PDF' OUTPUT_FORMAT,
          'EMAIL' DEL_CHANNEL,
          :SEND_TO||'@XXXXXX' PARAMETER1, 
          :SEND_TO||'@XXXXX' PARAMETER3,
          'NGB 4100-1B for '   || epp.PAY_GR PARAMETER4,
          'Please find attached your 4100-1B for ' || PARENT_UPC PARAMETER5,
          'true' PARAMETER6,
          :SEND_TO  ||'@XXXXX' PARAMETER7
        FROM
          MNPEPP.VO_EPP_BASE_ADMIN epp,
          MNPEPP.MV_HIERARCHY_UNITS pu
        WHERE
          epp.parent_upc = pu.id;
      My Web Service Call from APEX.
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v2="http://xmlns.oracle.com/oxp/service/v2">
         <soapenv:Header/>
         <soapenv:Body>
            <v2:scheduleReport>
               <v2:scheduleRequest>
                  <v2:deliveryChannels>
                      <v2:emailOptions>
                               <v2:item>
                                 <v2:emailBody>webservice test</v2:emailBody>
                                 <v2:emailFrom>sean@XXXXl</v2:emailFrom>
                                 <v2:emailSubject>test</v2:emailSubject>
                                 <v2:emailTo>sean@XXXX</v2:emailTo>
                            </v2:item>
                     </v2:emailOptions>
                  </v2:deliveryChannels>
                     <v2:scheduleBurstringOption>true</v2:scheduleBurstringOption>
                  <v2:notificationTo>sean@XXXX</v2:notificationTo>
                  <v2:notifyWhenFailed>true</v2:notifyWhenFailed>
                  <v2:notifyWhenSuccess>false</v2:notifyWhenSuccess>
                  <v2:notifyWhenWarning>false</v2:notifyWhenWarning>
                  <v2:reportRequest>
                     <v2:parameterNameValues>
                        <v2:listOfParamNameValues>
                                 <v2:item>
                                    <v2:multiValuesAllowed>false</v2:multiValuesAllowed>
                                     <v2:name>PAY_GR</v2:name>
                                     <v2:values>
                                         <v2:item>#P350_PAY_GR#</v2:item>
                                     </v2:values>
                                 </v2:item>
                                 <v2:item>
                                    <v2:multiValuesAllowed>false</v2:multiValuesAllowed>
                                     <v2:name>RANK</v2:name>
                                     <v2:values>
                                         <v2:item>#P350_RANK#</v2:item>
                                     </v2:values>
                                 </v2:item>
                                 <v2:item>
                                 <v2:multiValuesAllowed>false</v2:multiValuesAllowed>
                                     <v2:name>RSC</v2:name>
                                     <v2:values>
                                         <v2:item>#P350_RSC#%</v2:item>
                                     </v2:values>
                                 </v2:item>
                                 <v2:item>
                                 <v2:multiValuesAllowed>false</v2:multiValuesAllowed>
                                     <v2:name>SEND_TO</v2:name>
                                     <v2:values>
                                         <v2:item>#P350_SEND_TO#</v2:item>
                                     </v2:values>
                                 </v2:item>
      
                        </v2:listOfParamNameValues>
                     </v2:parameterNameValues>
                     <v2:attributeFormat>pdf</v2:attributeFormat>
                        <v2:attributeTemplate>NGB4100_SENIOR_GRADE.rtf</v2:attributeTemplate>
                     <v2:reportAbsolutePath>/open_reports/MNPEPP/NGB_4100_SENIOR_REPORT.xdo</v2:reportAbsolutePath>
                  </v2:reportRequest>
                  <v2:userJobName>NGB 4100 SENIOR</v2:userJobName>
               </v2:scheduleRequest>
               <v2:userID>XXXXX</v2:userID>
               <v2:password>XXXXX</v2:password>
            </v2:scheduleReport>
         </soapenv:Body>
      </soapenv:Envelope>
      Edited by: Crawler409 on Feb 14, 2012 3:10 PM