6 Replies Latest reply on Mar 22, 2010 5:48 PM by 743277

    Wanted Po to payments??

    Kranthi.K
      Hi,

      I need to form a query from purchase order to payments can this be done with mandatory columns.I have written the query but may be i missed some tables or the joins dint match in some cases.

      So anyone please build me purchase order-->receipts-->invoices-->payments (po to payments query).

      Thanks,
      By,
      Kranthi.
        • 1. Re: Wanted Po to payments??
          578444
          Kranthi,

          You need to use the following tables. But is the requirement is to write a single query??

          po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all
          rcv_shipment_headers, rcv_shipment_lines, rcv_transactions,
          ap_invoices_all, ap_invoice_distributions_all, ap_checks_all, ap_invoice_payments_all.

          Regards,
          Jyoti
          1 person found this helpful
          • 2. Re: Wanted Po to payments??
            Kranthi.K
            hi,

            Thanx for the reply jyoti

            i know the tables.I got the link but the problem arising for me is.

            iam facing problem when joining the tables from receipt tables to invoice tables im getting duplicate data.


            Any ideas will be greatly appreciated!!

            By,
            Kranthi

            Edited by: Kranthi.K on Jun 10, 2009 5:00 AM
            • 3. Re: Wanted Po to payments??
              Kranthi.K
              Hi all,

              I got all join conditions correct and getting the result,but getting some ambiguity.

              The problem iam facing is i needed tax invoice lines also to be captured,these tax invoice lines come from the purchase order i.e purchasing line tax assigned to the concerned invoice.

              Can anyone build me the query for purchase order-->receipts-->invoices

              columns required:- po_number,po_line_number,po_quantity,po_unit_price,receipt_num,transaction_type,
              transaction_qty,invoice_num,invoice_amount,invoice_line_num,line_type,amount,invoice_quantity.

              take conditions like transaction_type='deliver'

              Any answers will be greatly appreciated.


              By,
              Kranthi.

              Edited by: Kranthi.K on Jun 10, 2009 4:58 AM
              • 4. Re: Wanted Po to payments??
                578444
                Hi,

                Please look at the following query. This is in R12. Please modify the query as per your requirement.

                SELECT
                pha.segment1 po_number,
                     pla.line_num,plla.shipment_num,
                     msi.segment1 ordered_item,
                     msi.description ordered_item_description,
                     pla.unit_price po_unit_price,
                     rt.transaction_type,
                     rt.transaction_date,
                rt.subinventory,
                plla.quantity quantity_ordered,
                plla.quantity_received,
                pda.quantity_delivered,
                plla.quantity_Billed,
                rsh.receipt_num,
                aia.invoice_num,
                ail.line_number inv_line_number,
                aid.distribution_line_number inv_dist_number,
                ail.line_type_lookup_code Line_type,
                aid.line_type_lookup_code Dist_line_type,
                aid.amount,
                aid.quantity_invoiced,
                ac.check_number,
                aip.payment_num
                FROM
                apps.rcv_transactions rt,
                apps.po_headers_all pha,
                apps.po_line_locations_all plla,
                apps.po_distributions_all pda,
                apps.po_lines_all pla,
                apps.mtl_system_items msi,
                apps.rcv_shipment_headers rsh,
                apps.ap_invoices_all aia,
                apps.ap_invoice_lines_all ail,
                apps.ap_invoice_distributions_all aid,
                apps.ap_invoice_payments_all aip,
                apps.ap_checks_all ac
                WHERE 1=1
                and rt.po_header_id = pha.po_header_id
                and pha.po_header_id = pla.po_header_id
                and pla.po_line_id = plla.po_line_id
                and plla.line_location_id = pda.line_location_id
                and rt.po_line_location_id = plla.line_location_id
                and pla.item_id = msi.inventory_item_id (+)
                and rt.po_line_id = pla.po_line_id (+)
                and rt.organization_id = msi.organization_id
                and rsh.shipment_header_id (+) = rt. shipment_header_id
                and aip.check_id = ac.check_id (+)
                and aia.invoice_id = aip.invoice_id
                and aia.invoice_id (+) = ail.invoice_id
                and aia.invoice_id = aid.invoice_id
                and pda.po_distribution_id(+) = aid.po_distribution_id
                and rt.transaction_type = 'DELIVER'
                and ail.line_type_lookup_code = 'ITEM'
                and aid.line_type_lookup_code = 'ACCRUAL'
                and pha.segment1 = '&PO_Number'

                Regards,
                Jyoti
                1 person found this helpful
                • 5. Re: Wanted Po to payments??
                  Kranthi.K
                  Hi jyoti,

                  Thanx for your prompt reply.Its very nice of you to provide me query

                  By,
                  Kranthi.
                  • 6. Re: I need HELP  Regarding partial payment With Held Tax
                    743277
                    Hi All ,

                    I payment againts multile invoices and one invoice has multiple lines and eached lines has diffrent wh tax and wheld tax rates,
                    Now i want to get the detail of information like

                    Taxpayer_Business_Name
                    3W SYSTEMS (PVT) LTD     
                    date of payment
                    27/03/2009     
                    Value/ Amount on which tax collectabl or deductable
                    255,500      
                    Rate of Tax  (%)

                    3.50%     
                    Amount of Tax Collected or Deducted
                    8,943      
                    Amount of Tax Deposited

                    8,943.00      
                    date  Tax Deposited
                    06/04/2009     

                    Now I applied partial payment angainst multiple invoices , How can i get the partial payment and wheld tax, above give detail

                    Please Solve my issue