9 Replies Latest reply: Mar 8, 2013 10:20 AM by 994926 RSS

    Report to link invoices with attached documents

    994926
      I am trying to spool a report from the oracle back end, for some senior staff at my organisation. The report would contain approved invoices along with their electronically attached documents. I have a similar query that works( see below) the problem is this query only accurately ties invoice ID's(PK1_Value) for invoices that were created using "AP_INVOICES" enitity_name, but majority of our users dont have the payables responsibility and hence can only create using the "AP_INVOICES_INTERFACE" enitity_name, and I cant tie the PK1_VALUE in the fnd_attached_documents table to the invoice ID, Please I need urgent help to locate (a) what PK1_VALUE is being used by AP_INVOICES_INTERFACE (b) How I can reference INVOICE_ID with that value.

      SELECT SUP.vendor_name SUPPLIER, INV.invoice_num INVOICE,
      INV.invoice_amount AMOUNT, INV.remit_to_supplier_name,
      INV.description, APVL.approval_history_id, APVL.approver_name, DOC.url
      FROM ap_suppliers SUP, ap_invoices_all INV, ap_inv_aprvl_hist_all APVL,
      fnd_documents DOC, fnd_attached_documents ATCH
      WHERE SUP.vendor_id = INV.vendor_id
      AND INV.invoice_id = APVL.invoice_id
      AND INV.invoice_id = ATCH.pk1_value
      AND DOC.document_id = ATCH.document_id
      AND INV.wfapproval_status = 'WFAPPROVED'
      AND ATCH.entity_name = 'AP_INVOICES'
      AND APVL.response = 'APPROVED'
      AND INV.cancelled_by IS NULL
      ORDER BY SUPPLIER, INVOICE, APVL.approval_history_id


      Thanks in anticipation.
        • 1. Re: Report to link invoices with attached documents
          nagornyi
          Please, try searching the table on http://etrm.oracle.com
          • 2. Re: Report to link invoices with attached documents
            994926
            Hello nagornyi,

            Please I am not sure what table you are refereing to, I just need help on how to tie AP_INVOICE_INTERFACE(invoice_id) to AP_INVOICES_ALL(invoice_id).


            Thanks.
            • 3. Re: Report to link invoices with attached documents
              nagornyi
              Can't you do it using invoice_id?
              • 4. Re: Report to link invoices with attached documents
                Billy~Verreynne
                You are asking a question about a product and how it works/is designed. (e.g. SAP, Oracle Apps, whatever)

                This has nothing to do with the SQL or PL/SQL languages - the subject matter of this forum.

                Please close your thread here, and ask your question in the relevant product support forum for the product you are dealing with.
                • 5. Re: Report to link invoices with attached documents
                  994926
                  @Nagornyi The invoice ID's are different in AP_INVOICE_INTERFACE and AP_INVOICES, so it cant be tied to PK1_VALUE in FND_ATTACHED_DOCUMENTS.

                  @Billy, This is an SQL query related question, where do you want me to post it ?
                  • 6. Re: Report to link invoices with attached documents
                    Billy~Verreynne
                    991923 wrote:

                    @Billy, This is an SQL query related question, where do you want me to post it ?
                    I do not see a SQL question.

                    I see this question "+I just need help on how to tie AP_INVOICE_INTERFACE(invoice_id) to AP_INVOICES_ALL(invoice_id)+".

                    And that is a question about a data model that I do not have. No one here, to my knowledge, have those tables and that application.

                    Data models of commercial products can also be "intricate" (trying to use a nice word to describe it). If these tables are from a commercial product, then you should ask your question in a forum for that product.

                    If this is an inhouse designed and build data model, you should pose the problem and question to the inhouse designers of that data model.

                    Alternatively, you need to describe in detail the data model to us. But if you can do that, you would understand the data model, know how to relate entities AP_INVOICE_INTERFACE and AP_INVOICES_ALL, and not have the question you have.
                    • 7. Re: Report to link invoices with attached documents
                      994926
                      @Billy

                      This is an oracle related question, and its not an in-built developed model. I was actually refered to this forum from oracle support.
                      The reason i mentioned the tables AP_INVOICES_ALL and AP_INVOICES_INTERFACE is to give as much information about my problem as possible.

                      If you dont have a suggestion for me its okay.

                      But please dont stop others from offering help, if they can
                      • 8. Re: Report to link invoices with attached documents
                        BluShadow
                        991923 wrote:
                        @Billy

                        This is an oracle related question, and its not an in-built developed model. I was actually refered to this forum from oracle support.
                        Billy is correct. Whilst it may be an Oracle question, it is not an SQL or PL/SQL related question.
                        Your question is referring specifically to functions and entities that are only available to people using one of Oracle Apps type things (I don't use them myself)... So probably one of the forums in the E-Business suite (https://forums.oracle.com/forums/category.jspa?categoryID=3) would be more suitable.

                        Oracle Support would not (or certainly should not) be referring someone to ask their question in the forums if it is an urgent commercial issue as these forums are not for commercial support.
                        In these forums, nothing is "urgent" and it is considered rude to presume otherwise, both to the volunteers who help here, and to the other people who would like their questions answered.
                        The reason i mentioned the tables AP_INVOICES_ALL and AP_INVOICES_INTERFACE is to give as much information about my problem as possible.
                        That's great, but those tables do not exist as part of the standard Oracle database installation, and this SQL and PL/SQL forum is for questions related to the SQL and PL/SQL languages, not for questions related to specific applications that people here are unlikely to have, and for which, when they are Oracle provided applications, they have their own specific forums.
                        • 9. Re: Report to link invoices with attached documents
                          994926
                          @Blushadow Thanks foryour response, i didnt raise this an urgent matter. I completely understand your explanation.
                          I was probably a bit lost, and seeking guidance or information to direct me to the appropriate channel.

                          Thanks guys for your response.