4 Replies Latest reply on Dec 22, 2009 6:56 AM by 656069

    Pending Receipts

    673412
      Hi All,
      I want to develop a report for Pending Receipts (Receipts which are not invocied in AP). So is there any link between
      AP_INVOCIES_ALL & RCV_SHIPMENT_LINES. OR do i need to use some other tables???


      Regards

      Girish
        • 1. Re: Pending Receipts
          680542
          You can use following query to find out such cases . Hope it helps

          select RSH.RECEIPT_NUM,RCV.INVOICE_STATUS_CODE from rcv_shipment_headers rsh,
          rcv_transactioNs rcv
          where rsh.shipment_header_id=rcv.shipment_header_id
          AND RCV.TRANSACTION_TYPE='RECEIVE'
          and rcv.invoice_status_code='INVOICED'
          AND RSH.receipt_num in(
          SELECT substr(invoice_num,5,10)
          FROM ap_invoices_all ai,
          ap_batches_all ad
          WHERE ai.org_id="enter org_id"
          AND ai.batch_id=ad.batch_id
          AND ai.source='ERS'
          AND ai.invoice_amount>0
          AND ai.invoice_date >'enter date'
          AND NOT EXISTS
          (SELECT 1
          FROM ap_invoice_distributions_all adi
          WHERE adi.invoice_id=ai.invoice_id
          ) )


          --

          Prakhar
          • 2. Re: Pending Receipts
            673412
            Thanks Prakhar,

            But i think, we dont need to join ap_inoices_all with ap_batches. Also your query is eliminating the invoices which has disributions(in our case all invoices will have distributions)

            But thanks a lot for sharing this query. I Made some modifications in that to work for me.

            girish
            • 3. Re: Pending Receipts
              680542
              Girish ,
              I have developed this query for one of my requirement . My Purpose is to give u the details of tables that should use to find out such cases ,

              --
              Prakhar
              • 4. Re: Pending Receipts
                656069
                Hi Girish,

                We have issue with Uninvoiced Receipts Report for 2 period some POs are not included in Oct period but those are included in Nov period. i have to fine the reason.

                can you send me the Uninvoices invoice which developed and aslo advice me on this issue.

                Thanks,
                An