AP Return To Vendor Transactions Report

 AP Return To Vendor Transactions Report

SELECT

    al1.inv_org_code,

    al1.inv_org_id,

    al1.supplier_name,

    al1.rcv_date,

    al1.rcv_transaction_type,

    al1.receipt_number,

    al1.packing_slip_number,

    al1.po_number,

    al1.po_release_number,

    al1.comments,

    al1.rma_number,

    al1.quantity,

    al1.po_unit_price,

    al1.return_amount

FROM

    (

        SELECT

            rt.transaction_type rcv_transaction_type,

            rt.transaction_date rcv_date,

            --pis.receipt_number receipt_number,

rsh.receipt_num receipt_number,

            --pis.packing_slip_number packing_slip_number,

rsh.shipment_num packing_slip_number,

            rt.currency_code curr_code,

            --prt.transaction_quantity quantity,

rt.quantity quantity,

            --prt.purchase_order_unit_price po_unit_price,

rt.po_unit_price po_unit_price,

            --prt.purchase_order_unit_price * prt.transaction_quantity return_amount,

rt.po_unit_price*quantity return_amount,

            --prt.comments comments,

rt.comments comments,

            org.organization_code inv_org_code,

            substr(hro.name,1,3) company_code,

            --prt.supplier_name supplier_name,

            --prt.purchase_order_number po_number,

            --prt.po_release_number po_release_number,

hp.party_name supplier_name,

ps.segment1 po_number,

null po_release_number,

            rt.po_header_id po_header_id,

            rt.po_line_id po_line_id,

            rt.po_line_location_id po_line_location_id,

            rt.organization_id inv_org_id,

            rt.rma_reference rma_number

        FROM

            --apps.pofv_inbound_shipments pis,

            --apps.pofv_receiving_transactions prt,

rcv_shipment_headers rsh,

            rcv_transactions rt,

            --org_organization_definitions org,

inv_org_parameters org,

HR_ORGANIZATION_UNITS_F_TL hro,

po_headers_all pha,

poz_suppliers ps,

hz_parties hp

        WHERE 1=1

AND rsh.shipment_header_id(+) = rt.shipment_header_id

AND rt.po_header_id = pha.po_header_id

            --pis.shipment_id (+) = prt.shipment_id

            AND upper(rt.transaction_type) = 'RETURN TO VENDOR'

            --AND prt.transaction_id = rt.transaction_id

            AND org.organization_id = rt.organization_id

AND ps.vendor_id = pha.vendor_id

AND ps.party_id = hp.party_id

AND org.organization_id = hro.organization_id

    ) al1

WHERE

    1=1

AND al1.rcv_date = NVL(:P_rcv_date,al1.rcv_date)                   

AND al1.supplier_name IN (:P_SUPPLIER_NAME) or 

decode (:P_SUPPLIER_NAME,null,1,2)=1

--AND al1.inv_org_code IN NVL(:P_ORG_CODE,al1.inv_org_code)

AND al1.inv_org_code IN (:P_ORG_CODE) or 

decode (:P_ORG_CODE,null,1,2)=1

ORDER BY

    al1.inv_org_code,

    al1.rcv_date