PO DETAIL REPORT( DFF)- Excel:-
SELECT
ppf.first_name
|| ' '
|| ppf.last_name buyer,
a.authorization_status,
a.match_option invoice_match,
a.closed_code closet_status,
a.bill_to_location,
mca.segment1
|| '.'
|| mca.segment2 catgory,
a.promised_date,
pvs.vendor_site_code dff_project,
a.ship_to_organization shipto_org,
nvl(SUM(b.tax_amount), 0) totaltax,
trunc((a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)), 2) rate,
trunc((a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)) *(a.quantity - nvl(a.quantity_cancelled, 0)),
2) amount,
--Billed Amount--g-
trunc(decode(a.quantity_billed,
0,
0,
NULL,
0,
(a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)) * a.quantity_billed),
2) billed_amt,
trunc(decode(a.quantity_received,
0,
0,
NULL,
0,
(a.price_override + nvl((SUM(b.tax_amount) / a.quantity), 0)) * a.quantity_received),
2) received_amt,
a.price_override,
a.po_num,
pvs.vendor_site_code_alt project,
to_date(a.order_date) po_date,
a.ship_to_location shtoloc,
a.vendor_name vendor_name,
COUNT(b.tax_line_no),
mca.segment1,
pol.item_number item_code,
a.item_description,
round(a.quantity - nvl(a.quantity_cancelled, 0)) quantity,
a.quantity_cancelled,
round(a.quantity_received) quantity_received,
round(a.quantity_billed) quantity_billed,
a.item_id,
a.revision_num,
a.unit_meas_lookup_code unit,
a.currency_code,
a.agent_id,
a.line_location_id,
a.ship_to_organization_id,
a.po_header_id,
poh.attribute14 contract_type
FROM
jai_po_taxes b,
po_headers_all poh,
po_line_locations_inq_v a,
po_lines_v pol,
hr_org_units_no_join hrou,
per_people_f ppf,
po_vendor_sites_all pvs,
po_vendors pov,
mtl_categories mca
WHERE
poh.org_id = a.owning_org_id
AND b.line_location_id (+) = a.line_location_id
AND poh.vendor_site_id = a.vendor_site_id
AND a.po_num = poh.segment1
--AND poh.attribute1 IS NOT NULL
AND a.currency_code = 'INR'--:p_curr
AND poh.po_header_id = pol.po_header_id
AND b.po_header_id (+) = a.po_header_id
AND b.po_line_id (+) = a.po_line_id
AND pol.po_line_id = a.po_line_id
AND hrou.organization_id = a.ship_to_organization_id
AND ppf.person_id (+) = a.agent_id
--AND ppf.employee_number IS NOT NULL
AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND pvs.vendor_site_id = poh.vendor_site_id
AND pov.vendor_id = poh.vendor_id
AND mca.category_id = a.category_id
AND nvl(poh.attribute14, 1) = nvl(:contract_type, nvl(poh.attribute14, 1))
-- AND poh.attribute1 BETWEEN NVL (:from_site, poh.attribute1)
-- AND NVL (:to_site, poh.attribute1)
AND pvs.vendor_site_code BETWEEN nvl(:from_site, pvs.vendor_site_code) AND nvl(:to_site, pvs.vendor_site_code)
AND a.order_date BETWEEN nvl(:from_date, a.order_date) AND nvl(:p_to_date, sysdate)
AND a.vendor_name BETWEEN nvl(:from_vendor, a.vendor_name) AND nvl(:to_vendor, a.vendor_name)
AND ( :from_vendor IS NULL
OR pov.vendor_name >= :from_vendor )
AND ( :to_vendor IS NULL
OR pov.vendor_name <= :to_vendor )
AND ( decode(nvl(:p_item_flag, 'Y'), 'N', 'N') = 'N'
AND pol.item_number IS NULL
OR decode(nvl(:p_item_flag, 'Y'), 'Y', 'Y') = 'Y'
AND pol.item_number BETWEEN nvl(:from_item, pol.item_number) AND nvl(:to_item, pol.item_number) )
AND poh.attribute2 = decode(nvl(:p_item_flag, 'Y'), 'Y', 'POL-Local', 'Work Order')
AND mca.segment1 BETWEEN nvl(:p_category_from, mca.segment1) AND nvl(:p_category_to, mca.segment1)
AND mca.segment2 BETWEEN nvl(:p_subcategory_from, mca.segment2) AND nvl(:p_subcategory_to, mca.segment2)
AND a.ship_to_organization = nvl(:p_shpto_org, a.ship_to_organization)
AND a.ship_to_location_id = nvl(:p_shp_loc, a.ship_to_location_id)
AND ppf.person_id = nvl(:p_buyer, ppf.person_id)
GROUP BY
a.item_id,
a.price_override,
a.quantity,
a.po_num,
a.order_date,
a.ship_to_location,
a.vendor_name,
pvs.vendor_site_code,
mca.segment1,
pol.item_number,
a.item_description,
a.item_id,
a.unit_meas_lookup_code,
a.quantity,
a.quantity_cancelled,
a.currency_code,
pvs.vendor_site_code_alt,
a.agent_id,
a.line_location_id,
a.ship_to_organization_id,
a.po_header_id,
a.quantity_received,
a.quantity_billed,
a.revision_num,
ppf.first_name,
ppf.last_name,
a.authorization_status,
a.match_option,
a.closed_code,
a.bill_to_location,
mca.segment1,
mca.segment2,
a.promised_date,
poh.attribute1,
a.ship_to_organization,
poh.attribute14
ORDER BY
a.po_num
No comments:
Post a Comment