APIL Payment details for a given Purchase Order(AVANTHA)

SELECT ROWNUM,
           po_no ,
           po_date,
           po_qty,
           grn_no,
           grn_date,
           received_qty,
           accepted_qty,
           invoiced_qty,
           payment_amt,
           payment_terms,
           payment_date,
           payment_mode,
           checknumber,
           checkdate
      
            FROM(SELECT pha.segment1 po_no ,
            pha.creation_date po_date,
            SUM(pla.quantity) po_qty,
            rsh.receipt_num grn_no,
            rt.transaction_date grn_date,
            SUM(plla.quantity_received) received_qty,
            SUM(plla.quantity_accepted) accepted_qty,
            SUM(aila.quantity_invoiced) invoiced_qty,
            aia.amount_paid payment_amt,
            apt.name payment_terms,
            apsa.due_date payment_date,
            aca.payment_method_code payment_mode,
            decode(aca.payment_method_code,'CHECK',aca.check_number,
            'NETTING',aca.check_number) checknumber,
             decode(aca.payment_method_code,'CHECK',aca.check_date,
            'NETTING',aca.check_date) checkdate            

            FROM po_headers_all pha,
            po_lines_all pla,
            po_line_locations_all plla,
            po_distributions_all pda,
            rcv_transactions rt,
            rcv_shipment_headers rsh,
            rcv_shipment_lines rsl,
            ap_invoices_all aia,
            ap_invoice_lines_all aila,
            ap_invoice_distributions_all aida,
            ap_invoice_payments_all aipa,
            ap_payment_schedules_all apsa,
            ap_checks_all aca,
            ap_terms apt,
            mtl_system_items_b msib,
            org_organization_definitions ood
          
            WHERE 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 rsh.shipment_header_id=rsl.shipment_header_id
            AND pda.po_distribution_id=rsl.po_distribution_id
            AND rsl.shipment_line_id=rt.shipment_line_id
            AND aia.invoice_id=aila.invoice_id
            AND aia.invoice_id=aida.invoice_id
            AND aipa.invoice_id(+)=aia.invoice_id
            AND aia.invoice_id=apsa.invoice_id
            AND aipa.check_id=aca.check_id(+)
            AND apt.term_id=aia.terms_id
            AND pla.item_id=msib.inventory_item_id(+)
            AND ood.organization_id(+)=msib.organization_id
            AND rt.transaction_id=aida.rcv_transaction_id
            AND msib.inventory_item_id BETWEEN NVL(:FROM_INVENTORY_ITEM_ID,
            msib.inventory_item_id) AND NVL(:TO_INVENTORY_ITEM_ID,msib.inventory_item_id)
            AND pha.segment1=NVL(:PURCHASE_ORDER_NO,pha.segment1)
            AND ood.organization_id=NVL(:P_ORGANIZATION_ID,ood.organization_id)
            AND TRUNC(rt.transaction_date) BETWEEN nvl(:FROM_RECEIPT_DATE,to_date
            (rt.transaction_date,'DD-MON-RR')) AND nvl(:TO_RECEIPT_DATE,to_date
            (rt.transaction_date,'DD-MON-RR'))

                 GROUP BY  pha.segment1,
                                    pha.creation_date,
                                    rsh.receipt_num,
                                    rt.transaction_date,
                                    aia.amount_paid,
                                    apt.name,
                                    apsa.due_date,
                                    aca.payment_method_code,
                                    aca.check_number,
                                    aca.check_date
           
              ORDER BY pha.segment1)  






Report Name           :APILPDPO


Concurrent Name    :APIL Payment details for a given Purchase Order.


Parameters:

P_ORGANIZATION_ID :50M:APIL ITEM MASTER

FROM_RECEIPT_DATE :01-FEB-2011

TO_RECEIPT_DATE :31-MAR-2011

FROM_INVENTORY_ITEM_ID :PIPE 200 NB ERW

TO_INVENTORY_ITEM_ID :FLANGES 200 NB

PURCHASE_ORDER_NO :30058