2 Replies Latest reply on Jun 29, 2016 12:18 PM by Jagadekara

    Problems with the XML Output of a Report (AP)

    ViganQ

      Hello Community,

       

      I am using the "Supplier Open Balance Letter" concurrent program, with the APXSOBLX excecutable.
      I had to modify the excecutable so it would also get the GL_DATE for all the invoices the report prints.

       

      Modified Query:
      SELECT

      aa.as_of_date, aa.oraganization_name, aa.functional_currency_code, aa.supplier_name, aa.supplier_number, aa.vat_registration_number, aa.supplier_site_code, aa.address_line1, aa.address_line2, aa.address_line3, aa.city, aa.state, aa.zip, aa.country, aa.invoice_type, aa.invoice_num inboice_number, aa.invoice_date, bb.gl_date, aa.invoice_currency_code, nvl(aa.invoice_amount,0) invoice_amount, nvl(aa.amount_remaining,0) invoice_amount_remaining, nvl(aa.payment_amount,0) payment_amount, nvl(aa.discount_taken,0) discount_taken, nvl(aa.discount_amount_available,0) discount_amount_available, nvl(aa.inv_prepayment_amount,0) inv_prepayment_amount, nvl(aa.prepay_amount_original,0) prepay_amount_original, nvl(aa.prepay_amount_remaining,0) prepay_amount_remaining, nvl(aa.prepay_amount_applied,0) prepay_amount_applied

       

      FROM AP_SUPPLIER_BALANCE_ITF aa JOIN AP_INVOICES_ALL bb

      ON aa.INVOICE_NUM = bb.INVOICE_NUM JOIN AP_SUPPLIER_SITES_ALL cc

      ON bb.VENDOR_SITE_ID = cc.VENDOR_SITE_ID JOIN AP_SUPPLIERS dd

      ON bb.VENDOR_ID = dd.VENDOR_ID

      WHERE aa.supplier_site_code = cc.vendor_site_code AND aa.supplier_name = dd.vendor_name AND request_id =: P_CONC_REQUEST_ID

       

      The bolded part of the query is the added code.

       

      Now, when I run the report wih the modified executable, the report prints this XML Output:Screenshot_1.jpg

       


      On the other hand if I remove the "request_id =: P_CONC_REQUEST_ID" condition the report output gives all Open balances (It does not filter anything, even thought I am specifying in the parameter fields that I want the output to contain only the open balance of one particular supplier, it still includes everything).
      Output:

      Screenshot_2.jpg

       

       

      Good thing is that it is printing the <GL_DATE> tag in this one.

       

      So, how do I fix this?
      So when I run the report, it prints the XML output based on the parameters that I give, not ALL open balances of the suppliers....

       

      Thank you in advance.

        • 2. Re: Problems with the XML Output of a Report (AP)
          Jagadekara

          Hi,

           

          Just upload old rdf as it is and check. if it gave data then just add one column in that and check. if it gave correct data then the problem with that tables may be.

           

          Otherwise do one thing.

          Just try with formula column.

           

          Give like this...

           

          function CF_GL_DATEFormula return Char is

          v_gl_date varchar2(100);

          begin

          select bb.gl_date into v_gl_date

          from  ap_invoices_all bb

              , ap_supplier_sites_all cc

              , AP_SUPPLIERS dd

          where :invoice_number = bb.invoice_num

          and  bb.vendor_site_id = cc.vendor_site_id

          and  bb.vendor_id = dd.vendor_id

          and  :supplier_site_code = cc.vendor_site_code

            and :supplier_name = dd.vendor_name;

           

            return v_gl_date;

            exception

            when others then

            return null;

            end;

           

           

          Hope this will help you...

          1 person found this helpful