13 Replies Latest reply on Jun 25, 2019 7:45 AM by George Manasseh

    Link missing between AP Invoice tables and XLE Tables

    Bommi

      Hi Experts,

       

      We are using R12.2.3

      We created an invoice and invoice details are as below

       

      After invoice is created I done create accounting using 'Actions' button, but selected as Create Accounting > Final. So, by this data will be moved to SLA tables, but not to GL. The distribution details are as below

       

      After the accounting is done as I said before, the accounting details are as below (totally 6 lines)

       

      So, we are trying to fetch the accounting details using a query. I used the query as below, but it is not working Can anyone please help me on how to achieve this

       

       

      select  DISTINCT

              poh.segment1 po_number, poh.comments description,

                      poh.creation_date po_date, ai.invoice_num invoice_number,

                      ai.invoice_date invoice_date,

                      ai.invoice_amount invoice_amount,

                      xel.accounting_class_code accounting_class,

                      xel.accounting_date,

                      xel.entered_dr, xel.entered_cr, xel.accounted_cr,

                      xel.accounted_dr, xel.currency_code,

                      xel.currency_conversion_date, xel.gain_or_loss_flag

      from    ap_invoices_all ai,ap_invoice_lines_all ail,ap_invoice_distributions_all apid,po_headers_all poh

              ,xla.xla_transaction_entities xte,xla_ae_headers xeh,xla_ae_lines xel,xla.xla_events xe

      --        ,xla.xla_distribution_links xdl

      where   1=1

      and     ai.quick_po_header_id IS NULL

      and     ai.invoice_num='11Jun2019-Inv1'

      and     ai.invoice_id=ail.invoice_id

      and     ail.po_header_id=poh.po_header_id(+)

      and     ai.invoice_id=apid.invoice_id

      --AND     poh.segment1 =  NVL(:P_PO_NUMBER,poh.segment1)  --Lexi Param as 2=2

      --AND     trunc(poh.creation_date) between NVL(:P_PO_DATE_FROM,trunc(poh.creation_date)) and NVL(:P_PO_DATE_TO,trunc(poh.creation_date))    --Lexi Param as 2=2

      AND     NVL (xte.source_id_int_1, -99) = ai.invoice_id

      AND     xte.ledger_id = ai.set_of_books_id

      AND     xte.application_id = 200

      AND     xte.entity_code = 'AP_INVOICES'

      AND     xeh.application_id = 200

      AND     xte.entity_id = xeh.entity_id

      AND     xel.application_id = 200

      AND     xel.ae_header_id = xeh.ae_header_id

      and     xel.ae_line_num(+)=ail.line_number

      AND     xe.application_id = 200

      and     xte.entity_id=xe.entity_id

      and     xeh.event_id=xe.event_id

      --and     xeh.ae_header_id=xdl.ae_header_id

      --and     xeh.event_id = xdl.event_id

      --and     xte.application_id = xdl.application_id

      --and     xel.ae_line_num=xdl.ae_line_num

      --and     xdl.source_distribution_id_num_1=apid.invoice_distribution_id

      --and     xdl.applied_to_dist_id_num_1=apid.invoice_distribution_id

      --order by ai.invoice_num,ail.line_number

      ;

       

       

      Thanks in Advance,

      Bommi

        • 1. Re: Link missing between AP Invoice tables and XLE Tables
          George Manasseh

          Hi, Bommi

          Please be informed that when you enter invoice on AP invoice workbench you choose the lines but the system according to the AP setup puts another distributions like IPV and exchange rate variance account and you will be unable to add these distributions or remove it.

          For the mentioned invoice please download the latest version of note 1360390.1 and apply it then please follow the solution provided by the note output.

           

          Thanks

          George

          1 person found this helpful
          • 2. Re: Link missing between AP Invoice tables and XLE Tables
            Bommi

            Hi George,

             

            Is it any issue with the application? Because, we never came with such issue and we never informed by any one about this?

            The note is something like patch? IF yes, then we need approvals to apply the patch.

             

            By the way, what do you mean by IPV? Could you please explain in detail about the issue you are pointing

             

             

            Regards,

            Bommi

            • 3. Re: Link missing between AP Invoice tables and XLE Tables
              J Reinhart

              Hi Bommi,

               

              Your joins are close but not quite right.  Try it like this:

               

              from

              ap_invoices_all ai,  ap_invoice_lines_all ail, ap_invoice_distributions_all apid

                      ,xla.xla_transaction_entities xte,  xla_ae_headers xeh  ,xla_ae_lines xel,  xla.xla_events xe  ,xla.xla_distribution_links xdl

              where  ai.invoice_id = ail.invoice_id -- invoice header to invoice line

              and     ail.invoice_id = apid.invoice_id and ail.line_number = apid.invoice_line_number -- invoice line to invoice distribution, you need both fields here

              and    apid.invoice_distribution_id = xdl.source_distribution_id_num_1 and apid.accounting_event_id = xdl.event_id and xdl.source_distribution_type = 'AP_INV_DIST' -- link the invoice distribution to XLA

              and   xdl.ae_header_id = xeh.ae_header_id and xdl.event_id = xeh.event_id -- xla distribution to xla header

              and  xdl.ae_header_id = xel.ae_header_id and xdl.ae_line_num = xel.ae_line_num and xel.source_id = apid.invoice_distribution_id and xel.application_id = xeh.application_id-- link to xla line

              and  xeh.event_id = xe.event_id and xeh.application_id = xe.application_id and xdl.event_id = xe.event_id -- link to events

              and  xe.entity_id = xte.entity_id and xe.application_id = xte.application_id -- link to transaction entity

               

              If this is useful, please take a moment to mark this response as Helpful.

               

              Thanks and regards,

               

              J

              • 4. Re: Link missing between AP Invoice tables and XLE Tables
                J Reinhart

                Hi Bommi,

                 

                I think George's point about IPV is that invoice price variance is calculated automatically and added to the invoice and you can't manually update it.  The same goes for exchange rate variance.  You can see IPV and ERV but you can't update them.

                 

                If this is useful, please take a moment to mark this response Helpful.

                 

                Thanks and Regards,

                 

                J

                1 person found this helpful
                • 5. Re: Link missing between AP Invoice tables and XLE Tables
                  Bommi

                  Hi Reinhart,

                   

                  I understood what George wants to tell me.

                  Now, I am looking for a query to fetch the data from Sub ledger tables. Like, as mentioned in above screenshots, created an Invoice with PO number at line level. That too, single invoice with 2 POs.

                   

                  As shown above, I created 5 invoice lines for which 6 accounting lines has been created for that invoice. So, looking for the query to fetch those 6 accounting lines

                   

                  I will go through the query you shared and will let you know once tested.

                   

                  Also, you can test the same in your application (I done Create Accounting > Final for that invoice. But not Final, post to GL)

                   

                  Regards,

                  Bommi

                  • 6. Re: Link missing between AP Invoice tables and XLE Tables
                    Bommi

                    Hi,

                     

                    The actual issue I am facing is:

                    We created 2 POs (57041 and 57042) and PO details are as below

                       

                    PO#57041 Line#Shipment#Amount
                    111.11
                    214.44
                    224.44
                    316.66
                    414.44
                    424.44

                       

                    PO#57042 Line#Shipment#Amount
                    115.55
                    2113.32
                    2213.32
                    317.77
                    327.77
                    4117.76

                     

                    Now, created an Invoice 19Jun2019-Inv4 with Invoice lines as below

                          

                    Line#Line TypeAmountPO#PO LinePO Shipment
                    1Item1.115704111
                    2Item5.555704211
                    3Item4.445704121
                    4Item13.325704221
                    5Item4.445704122
                    6Item13.325704222
                    7Item6.665704131
                    8Item4.445704141
                    9Item4.445704142
                    10Item7.775704231
                    11Item7.775704232
                    12Item17.765704241
                    13Item132
                    14Freight234
                    15Misc345
                    16Tax0

                     

                    When I done the accounting, it is created as below

                           

                    LedgerAccountAccount DescriptionGL DateAccounting  ClassAccounted DRAccounted CR
                    Ledger X10-10400-80700-0000-0000-0000XX-Key Account   Section 3-Penalties-Null-Null-Null43635Freight234
                    Ledger X10-00000-10100-0000-0000-0000XX-Null-BofA General Acct.-Null-Null-Null43635Item Expense132
                    Ledger X10-40330-13100-0000-0000-0000XX-Business Systems-Inventory Clearing-Null-Null-Null43635Item Expense6.66
                    Ledger X10-40330-13100-0000-0000-0000XX-Business Systems-Inventory Clearing-Null-Null-Null43635Item Expense35.52
                    Ledger X10-40330-13100-0000-0000-0000XX-Business Systems-Inventory Clearing-Null-Null-Null43635Item Expense22.2
                    Ledger X10-40330-13100-0000-0000-0000XX-Business Systems-Inventory Clearing-Null-Null-Null43635Item Expense8.88
                    Ledger X10-40330-13100-0000-0000-0000XX-Business Systems-Inventory Clearing-Null-Null-Null43635Item Expense17.76
                    Ledger X10-00000-21625-0000-0000-0000XX-Null-Inter Co. Payable / NK Am-Null-Null-Null43635Liability 711
                    Ledger X10-00000-21625-0000-0000-0000XX-Null-Inter Co. Payable / NK Am-Null-Null-Null43635Liability 6.66
                    Ledger X10-00000-21625-0000-0000-0000XX-Null-Inter Co. Payable / NK Am-Null-Null-Null43635Liability 35.52
                    Ledger X10-00000-21625-0000-0000-0000XX-Null-Inter Co. Payable / NK Am-Null-Null-Null43635Liability 22.2
                    Ledger X10-00000-21625-0000-0000-0000XX-Null-Inter Co. Payable / NK Am-Null-Null-Null43635Liability 8.88
                    Ledger X10-00000-21625-0000-0000-0000XX-Null-Inter Co. Payable / NK Am-Null-Null-Null43635Liability 17.76
                    Ledger X10-20602-63200-0000-0000-0000XX-D2\-CA Team-Per Diem & Meals-Null-Null-Null43635Miscellaneous Expense345

                     

                    If you observe the accounting, it is as below:

                          1. Item Expense Line with Dr of 6.66 is sum of Line-1 of both POs

                          2. Item Expense Line with Dr of 35.52 is sum of Line-2 of both POs

                          3. Item Expense Line with Dr of 22.2 is sum of Line-3 of both POs

                    But,

                          4. Item Expense Line with Dr of 8.88 is Line-4 of PO 57041

                          5. Item Expense Line with Dr of 17.76 is Line-4 of PO 57042

                     

                    So, why accounting is done as sum of both POs for few lines, but not for other lines? Is it due to accounting setup rules? Or any thing else?

                     

                    Requirement: Our main client requirement is fetch the accounting information PO wise. But, if accounting is happening like this, how can we fetch the accounting information PO wise

                     

                    Can anyone please help me on this

                     

                    Thanks in Advance,

                    Bommi

                    • 7. Re: Link missing between AP Invoice tables and XLE Tables
                      Bommi

                      Hi,

                       

                      can anyone please help me on this.

                       

                       

                      Regards,

                      Bommi

                      • 8. Re: Link missing between AP Invoice tables and XLE Tables
                        George Manasseh

                        Hi,

                        We think that you want the journals in details, so please review the below note.

                        How To Specify Whether To Import in Summary or Detail (Doc ID 2272085.1)

                        Hope it helps

                        Thanks

                        George

                        1 person found this helpful
                        • 9. Re: Link missing between AP Invoice tables and XLE Tables
                          Bommi

                          Hi George,

                           

                          Thanks for inputs. The details are as below

                           

                          But, why few accounting lines are showing as sum of 2 PO lines, but separately for other lines (as mentioned before)?

                           

                           

                          Regards,

                          Bommi

                          • 10. Re: Link missing between AP Invoice tables and XLE Tables
                            George Manasseh

                            Hi,

                            Please change the value in the field General Ledger Journal Entry Summarization to No Summarization and re-test.

                            Thanks

                            George

                            • 11. Re: Link missing between AP Invoice tables and XLE Tables
                              Bommi

                              That's complicated

                              I should not change it. I am not allowed (even it is non PROD)

                               

                              But, is it the reason for that way of accounting ?

                               

                               

                              Regards,

                              Bommi

                              • 12. Re: Link missing between AP Invoice tables and XLE Tables
                                George Manasseh

                                Hi,

                                As far as we know the answer is yes.

                                About changing it you have to apply this on the Test server to explore the result and if it is not suitable for you you can change the copy of the Test server by having a new clone form Production server.

                                Please review the below notes.

                                What Do "Group By GL Date" And "Group By GL Period" Options in Accounting Setup Manager Do? (Doc ID 1289618.1)

                                What Is the Difference Between Summarize by GL Period and Group by GL Period? (Doc ID 1111196.1)

                                 

                                Thanks

                                George

                                1 person found this helpful
                                • 13. Re: Link missing between AP Invoice tables and XLE Tables
                                  George Manasseh

                                  Hi,

                                  Also please review the below notes.

                                   

                                  R12: GLLEZL Journal Import - How To Get Separate Journals For Different Transactions With The Same Date? (Doc ID 579184.1)

                                   

                                  How To Show Subledger Details in the Journal Line Description Instead of 'Journal Import Created' (Doc ID 552209.1)

                                   

                                  Thanks

                                  George

                                  1 person found this helpful