3 Replies Latest reply on Mar 21, 2017 9:41 AM by Bommi

    Need help on printing the Parameters in RTF

    Bommi

      Hi Experts,

           We have developed an 'PL/SQL Stored Procedure' method Concurrent Program and output we are generating in Excel File (using XML Tags)

           Here, we have one parameter Transaction#. For this parameter we are using the Valueset and Valueset ID is CUSTOMER_TRX_ID and value is TRX_NUMBER (i.e., while running the Concurrent Program user can enter based on TRX_NUMBER, but backend, it will take CUSTOMER_TRX_ID and used in package logic).

           In my RTF we are printing parameters details also. But, we want to print the TRX_NUMBER which user entered, but not CUSTOMER_TRX_ID. How to achieve this.

       

      Thanks in Advance,

      Bommi

        • 1. Re: Need help on printing the Parameters in RTF
          anasazii

          in your pl/sql get the transaction number for the supplied ID parameter and add that value as a one-time of your xml tags before you end your xml string/footer.

          • 2. Re: Need help on printing the Parameters in RTF
            Bommi

            Hi Anasazii,

                I am not sure how to do that.

                I am attaching the sample code and screenshot of XML Tags that generated. Our main procedure name is 'Generate_DefCOSXml' and it call the procedure 'add_parameters', I hope this 'add_parameters' will generate the XML Tags and its value (I hope, not sure). If Ia m not wrong, could you please let me know where and what changes need to make in this 'add_parameters' procedure.

             

            /*===========================================================================*/
            --      API name        : add_parameters
            --      Type            : Private
            --      Function        : Generate XML data for Parameters and append it to
            --                        output
            --      Pre-reqs        : None.
            --      Parameters      :
            --      in              : p_api_version           in number
            --                      : p_init_msg_list         in varchar2
            --                      : p_validation_level      in number
            --                      : p_cost_method           in number
            --                      : p_operating_unit        in number
            --                      : p_ledger_id             in number
            --                      : p_pac_legal_entity      in number
            --                      : p_pac_cost_type         in number
            --                      : p_pac_cost_group        in number
            --                      : p_period_name           in varchar2
            --                      : p_sales_order_date_low  in varchar2
            --                      : p_sales_order_date_high in varchar2
            --                      : p_all_lines             in varchar2
            --                      : i_amt_tolerance           in number
            --
            --      out             :
            --                      : x_return_status         out nocopy varchar2
            --                      : x_msg_count             out nocopy number
            --                      : x_msg_data              out nocopy varchar2
            --
            --      in out          :
            --                      : x_xml_doc               in out nocopy clob
            --
            --      Version         : Current version         1.0
            --                      : Initial version         1.0
            --      History         : 
            --      Notes           : This Procedure is called by Generate_DefCOSXml
            --                        procedure. The procedure generates XML data for the
            --                        report parameters and appends it to the report
            --                        output.
            -- End of comments
            /*===========================================================================*/
            PROCEDURE add_parameters (
                  p_api_version             IN              NUMBER,
                  p_init_msg_list           IN              VARCHAR2,
                  p_validation_level        IN              NUMBER,
                  x_return_status           OUT NOCOPY      VARCHAR2,
                  x_msg_count               OUT NOCOPY      NUMBER,
                  x_msg_data                OUT NOCOPY      VARCHAR2,
                  i_cost_method             IN              NUMBER,
                  i_operating_unit          IN              NUMBER,
                  i_ledger_id               IN              NUMBER,
                  i_pac_legal_entity        IN              NUMBER,
                  i_pac_cost_type           IN              NUMBER,
                  i_pac_cost_group          IN              NUMBER,
                  i_period_name             IN              VARCHAR2,
                  i_sales_order_date_low    IN              VARCHAR2,
                  i_sales_order_date_high   IN              VARCHAR2,
                  i_all_lines               IN              VARCHAR2,
                  i_amt_tolerance           IN              NUMBER,
                  i_order_type              IN              VARCHAR2,
                                                                    --Added by Bommi
                  i_trx_number              IN              NUMBER,
                                                                   --Added by Bommi
               x_xml_doc                 IN OUT NOCOPY   CLOB
               )
               IS
                  l_api_name      CONSTANT VARCHAR2 (30)              := 'ADD_PARAMETERS';
                  l_api_version   CONSTANT NUMBER                                  := 1.0;
                  l_full_name     CONSTANT VARCHAR2 (2000)
                                                       := g_pkg_name || '.' || l_api_name;
                  l_module        CONSTANT VARCHAR2 (2000) := 'cst.plsql.' || l_full_name;
                  l_ref_cur                sys_refcursor;
                  l_qryctx                 NUMBER;
                  l_xml_temp               CLOB;
                  l_age_option             NUMBER;
                  l_offset                 PLS_INTEGER;
                  l_org_code               cst_organization_definitions.organization_code%TYPE;
                  l_cost_type              cst_cost_types.cost_type%TYPE;
                  l_cost_group             cst_cost_groups.cost_group%TYPE;
                  l_period_name            cst_pac_periods.period_name%TYPE;
                  l_meaning                fnd_lookups.meaning%TYPE;
                  l_stmt_num               NUMBER;
                  l_ulog          CONSTANT BOOLEAN
                     :=     (fnd_log.level_unexpected >= g_log_level)
                        AND fnd_log.TEST (fnd_log.level_unexpected, l_module);
                  l_errorlog      CONSTANT BOOLEAN
                                        := l_ulog
                                           AND (fnd_log.level_error >= g_log_level);
                  l_eventlog      CONSTANT BOOLEAN
                                    := l_errorlog
                                       AND (fnd_log.level_event >= g_log_level);
                  l_plog          CONSTANT BOOLEAN
                                := l_eventlog
                                   AND (fnd_log.level_procedure >= g_log_level);
               BEGIN
                  -- Write the module name to fnd log file
            
            
                  --IF l_pLog THEN
                  --FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,l_module||'.begin',
                  DEBUG ('Add_Parameters+');
                  DEBUG ('i_cost_method = ' || i_cost_method);
                  DEBUG ('i_operating_unit = ' || i_operating_unit);
                  DEBUG ('i_ledger_id = ' || i_ledger_id);
                  DEBUG ('i_pac_legal_entity = ' || i_pac_legal_entity);
                  DEBUG ('i_pac_cost_type = ' || i_pac_cost_type);
                  DEBUG ('i_pac_cost_group = ' || i_pac_cost_group);
                  DEBUG ('i_period_name = ' || i_period_name);
                  DEBUG ('i_sales_order_date_low = ' || i_sales_order_date_low);
                  DEBUG ('i_sales_order_date_high = ' || i_sales_order_date_high);
                  DEBUG ('i_all_lines = ' || i_all_lines);
                  DEBUG ('i_amt_tolerance = ' || i_amt_tolerance);
                  DEBUG ('i_order_type = ' || i_order_type);
               DEBUG ('i_trx_number = ' || i_trx_number);
            
            
                  -- Standard call to check for call compatibility.
                  IF (NOT fnd_api.compatible_api_call (l_api_version,
                                                       p_api_version,
                                                       l_api_name,
                                                       g_pkg_name
                                                      )
                     )
                  THEN
                     RAISE fnd_api.g_exc_unexpected_error;
                  END IF;
            
            
                  -- Initialize message list if p_init_msg_list is set to TRUE.
                  IF (fnd_api.to_boolean (p_init_msg_list))
                  THEN
                     fnd_msg_pub.initialize;
                  END IF;
            
            
                  -- Initialize API return status to success
                  x_return_status := fnd_api.g_ret_sts_success;
                  -- Initialize temporary variable to hold xml data
                  DBMS_LOB.createtemporary (l_xml_temp, TRUE);
                  l_offset := 21;
                  -- Get the proile value to determine the aging basis
                  fnd_profile.get ('CST_ACCRUAL_AGE_IN_DAYS', l_age_option);
                  l_stmt_num := 10;
            
            
                  -- Open Ref Cursor to collect the report parameters
            
            
                  /* Perpetual */
                  IF (i_cost_method = 1)
                  THEN
                     OPEN l_ref_cur FOR 'select :i_cost_method cost_method,
             haou.name operating_unit,
             gsb.name ledger,
             :i_period_name period_name,
             :i_sales_order_date_low sales_order_date_from,
             :i_sales_order_date_high sales_order_date_to,
             fl.meaning all_lines,
             :i_amt_tolerance tolerance_amount,
             :i_order_type i_order_type, --Added by Srivathsava
             :i_trx_number i_trx_number --Added by Srivathsava
             from hr_all_organization_units haou,
             gl_sets_of_books gsb,
             fnd_lookups fl
             where haou.organization_id = :i_operating_unit
             and gsb.set_of_books_id = :i_ledger_id
             and fl.lookup_code = :i_all_lines
             and fl.lookup_type = ''YES_NO'''
                     USING i_cost_method,
                           i_period_name,
                           i_sales_order_date_low,
                           i_sales_order_date_high,
                           i_amt_tolerance,
                           i_order_type,
                i_trx_number,
                           i_operating_unit,
                           i_ledger_id,
                           i_all_lines;
                  /* Periodic */
                  ELSIF (i_cost_method = 3)
                  THEN
                     SELECT cost_type
                       INTO l_cost_type
                       FROM cst_cost_types
                      WHERE cost_type_id = i_pac_cost_type;
            
            
                     SELECT cost_group
                       INTO l_cost_group
                       FROM cst_cost_groups
                      WHERE cost_group_id = i_pac_cost_group;
            
            
                     SELECT period_name
                       INTO l_period_name
                       FROM cst_pac_periods
                      WHERE pac_period_id = TO_NUMBER (i_period_name);
            
            
                     SELECT meaning
                       INTO l_meaning
                       FROM fnd_lookups
                      WHERE lookup_type = 'YES_NO' AND lookup_code = i_all_lines;
            
            
                     OPEN l_ref_cur FOR 'select :i_cost_method cost_method,
             xle.name legal_entity,
             :l_cost_type cost_type,
             :l_cost_group cost_group,
             :l_period_name period_name,
             :i_sales_order_date_low sales_order_date_from,
             :i_sales_order_date_high sales_order_date_to,
             :l_meaning all_lines
             from xle_firstparty_information_v xle
             where xle.legal_entity_id = :i_pac_legal_entity'
                     USING i_cost_method,
                           l_cost_type,
                           l_cost_group,
                           l_period_name,
                           i_sales_order_date_low,
                           i_sales_order_date_high,
                           l_meaning,
                           i_pac_legal_entity;
                  END IF;                        /* p_cost_method = 1, p_cost_method = 3*/
            
            
                  -- create new context
                  l_stmt_num := 20;
                  l_qryctx := DBMS_XMLGEN.newcontext (l_ref_cur);
                  DBMS_XMLGEN.setrowsettag (l_qryctx, 'PARAMETERS');
                  DBMS_XMLGEN.setrowtag (l_qryctx, NULL);
                  l_stmt_num := 30;
                  -- get XML into the temporary clob variable
                  DBMS_XMLGEN.getxml (l_qryctx, l_xml_temp, DBMS_XMLGEN.NONE);
            
            
                  -- remove the header (21 characters) and append the rest to xml output
                  IF (DBMS_XMLGEN.getnumrowsprocessed (l_qryctx) > 0)
                  THEN
                     DBMS_LOB.ERASE (l_xml_temp, l_offset, 1);
                     DBMS_LOB.append (x_xml_doc, l_xml_temp);
                  END IF;
            
            
                  -- close context and free memory
                  DBMS_XMLGEN.closecontext (l_qryctx);
            
            
                  CLOSE l_ref_cur;
            
            
                  DBMS_LOB.freetemporary (l_xml_temp);
                  -- Standard call to get message count and if count is 1, get message info.
                  fnd_msg_pub.count_and_get (p_count      => x_msg_count,
                                             p_data       => x_msg_data);
                  -- Write the module name to fnd log file
                  DEBUG ('x_return_status = ' || x_return_status);
                  DEBUG ('x_msg_data =' || x_msg_data);
                  DEBUG ('Add_Parameters-');
               EXCEPTION
                  WHEN fnd_api.g_exc_error
                  THEN
                     x_return_status := fnd_api.g_ret_sts_error;
                     fnd_msg_pub.count_and_get (p_count      => x_msg_count,
                                                p_data       => x_msg_data
                                               );
                  WHEN fnd_api.g_exc_unexpected_error
                  THEN
                     x_return_status := fnd_api.g_ret_sts_unexp_error;
                     fnd_msg_pub.count_and_get (p_count      => x_msg_count,
                                                p_data       => x_msg_data
                                               );
                  WHEN OTHERS
                  THEN
                     x_return_status := fnd_api.g_ret_sts_unexp_error;
            
            
                     IF (fnd_log.level_unexpected >= g_log_level)
                     THEN
                        fnd_log.STRING (fnd_log.level_unexpected,
                                        l_module || '.' || l_stmt_num,
                                        SUBSTRB (SQLERRM, 1, 240)
                                       );
                     END IF;
            
            
                     IF (fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
                        )
                     THEN
                        fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
                     END IF;
            
            
                     fnd_msg_pub.count_and_get (p_count      => x_msg_count,
                                                p_data       => x_msg_data
                                               );
               END add_parameters;
            

             

             

            Community.png

             

            Thanks in Advance,

            Bommi

            • 3. Re: Need help on printing the Parameters in RTF
              Bommi

              Hi anasazii,

                  I tried as you suggested.

                  It is working fine as expected.

                  Thank you so much

                  Attached the sample code I added

               

              /* Perpetual */
                    IF (i_cost_method = 1)
                    THEN
                       OPEN l_ref_cur FOR 'select :i_cost_method cost_method,
               haou.name operating_unit,
               gsb.name ledger,
               :i_period_name period_name,
               :i_sales_order_date_low sales_order_date_from,
               :i_sales_order_date_high sales_order_date_to,
               fl.meaning all_lines,
               :i_amt_tolerance tolerance_amount,
               :i_order_type i_order_type, --Added by Bommi on 20Mar2017
               :v_party_name i_cust_name,  --Added by Bommi on 20Mar2017
               :v_trx_number i_trx_number  --Added by Bommi on 20Mar2017
               from hr_all_organization_units haou,
               gl_sets_of_books gsb,
               fnd_lookups fl
               where haou.organization_id = :i_operating_unit
               and gsb.set_of_books_id = :i_ledger_id
               and fl.lookup_code = :i_all_lines
               and fl.lookup_type = ''YES_NO'''
                       USING i_cost_method,
                             i_period_name,
                             i_sales_order_date_low,
                             i_sales_order_date_high,
                             i_amt_tolerance,
                             i_order_type,               --Added by Bommi on 20Mar2017
                             v_party_name,               --Added by Bommi on 20Mar2017
                             v_trx_number,               --Added by Bommi on 20Mar2017
                             i_operating_unit,
                             i_ledger_id,
                             i_all_lines;
              

               

                   Here, for trx_number and party_name fetched by using separate BEGIN END block above this as below.

               

               BEGIN
                          SELECT party_name
                            INTO v_party_name
                            FROM hz_parties
                           WHERE 1 = 1 AND party_id = i_cust_name;
                       EXCEPTION
                          WHEN OTHERS
                          THEN
                             fnd_file.put_line
                                (fnd_file.LOG,
                                    'Error in fetching the Party Name using Party ID. Error: '
                                 || SQLERRM
                                );
              END;
              
              
              BEGIN
                          SELECT trx_number
                            INTO v_trx_number
                            FROM ra_customer_trx_all
                           WHERE 1 = 1 AND customer_trx_id = i_trx_number;
                       EXCEPTION
                          WHEN OTHERS
                          THEN
                             fnd_file.put_line
                                (fnd_file.LOG,
                                    'Error in fetching the Trx Number using the Trx ID. Error: '
                                 || SQLERRM
                                );
              END;
              

               

              Regards,

              Bommi