Query For Debit Rate Report In EBS 12

 SPCL - DEBIT RATE REPORT

In this Report we get all detail PROJECT for which item is purchased for project and how many time items purchased and its total purchase rate and total tax amount on that item on using this info we calculate AVERAGE purchase amount for particular ITEM.



SELECT pvs.vendor_site_code dff_project, pol.item_number item_code, a.unit_meas_lookup_code unit, -- nvl(pvs.vendor_site_code_alt,pvs.vendor_site_code) project, SUM(round(a.quantity - nvl(a.quantity_cancelled, 0))) quantity, SUM(pol.unit_price) unit_price_sum, SUM(tax_amt) tax_amt, COUNT(pol.item_number) purchase_time FROM po_headers_all poh, -- jai_po_taxes b, po_line_locations_inq_v a, po_lines_v pol, -- hr_org_units_no_join hrou, po_vendor_sites_all pvs, po_vendors pov, ( SELECT SUM(b.tax_amount) tax_amt, b.po_line_id po_line_id FROM jai_po_taxes b GROUP BY b.po_header_id, b.po_line_id ) c WHERE poh.org_id = a.owning_org_id AND poh.vendor_site_id = a.vendor_site_id AND a.po_line_id = c.po_line_id (+)--------------------------------------------- --AND pvs.vendor_site_code_alt=:project_name AND pvs.vendor_site_code = :p_vendor_site AND round(a.quantity - nvl(a.quantity_cancelled, 0)) <> 0 AND pol.item_number IS NOT NULL --AND pol.item_number IN ( '0986' ) -- and b.PO_LINE_ID(+)=a.PO_LINE_ID --------------------------------------------- AND a.po_num = poh.segment1 -- AND poh.attribute1 IS NOT NULL -- AND a.currency_code = :p_curr AND poh.po_header_id = pol.po_header_id AND pol.po_line_id = a.po_line_id -- AND hrou.organization_id = a.ship_to_organization_id AND pvs.vendor_site_id = poh.vendor_site_id AND pov.vendor_id = poh.vendor_id--------------------------------------------- GROUP BY pvs.vendor_site_code, --a.item_id, pol.item_number, a.unit_meas_lookup_code -----------------------------------------------------------------------------------------------