Home » Applications » Oracle Fusion Apps & E-Business Suite » PO & iProc mapping for Catalog Supplier (11i)
PO & iProc mapping for Catalog Supplier [message #443578] Tue, 16 February 2010 06:01
suman.g
Messages: 88
Registered: June 2009
Member
Hi All,

In my query I am displaying the the spend for all catalog items in iProc for all countries along with oracle category.

My query is:
===========
SELECT pg.Name, 
 orgs.organization_code org_code, 
 mcat.concatenated_segments oracle_category, 
 ven.vendor_name, 
 pl.attribute1 Catalog_Type, 
 SUM(TO_NUMBER (pl.unit_price) * TO_NUMBER (pll.quantity)) quantity_amt, 
 SUM(TO_NUMBER (pl.unit_price) * TO_NUMBER (pll.quantity_received)) qty_received_amt, 
 SUM (TO_NUMBER (pl.unit_price) * TO_NUMBER (pll.quantity_cancelled)) qty_cancelled_amt, 
 SUM (TO_NUMBER (pl.unit_price) * TO_NUMBER (pll.quantity_billed)) qty_billed_amt 
FROM po_headers_all ph, 
 po_lines_all pl, 
 po_line_locations_all pll, 
 po_vendors ven, 
 org_organization_definitions orgs, 
 per_business_groups pg, 
 mtl_categories_b_kfv mcat 
WHERE pl.po_header_id = ph.po_header_id 
 AND pll.po_line_id = pl.po_line_id 
 AND pll.po_header_id = pl.po_header_id 
 AND ven.vendor_id = ph.vendor_id 
 AND ph.authorization_status = 'APPROVED' 
 AND orgs.operating_unit = ph.org_id 
 AND pg.business_group_id =orgs.business_group_id 
 AND orgs.organization_code NOT IN ('ITM', 'GIM') 
 AND pl.category_id=mcat.category_id 
GROUP BY pg.Name, 
 orgs.organization_code, 
 mcat.concatenated_segments, 
 ven.vendor_name, 
 pl.attribute1
order by pg.Name, 
 orgs.organization_code, 
 mcat.concatenated_segments, 
 ven.vendor_name, 
 pl.attribute1
==================

Now I want to add catalog Supplier coulmn in the query but while adding, amount columns are giving incorrect data and is not matching with old query data.

Please let me know if anybody have an idea which and how to joins tables to get the correct results.

Thanking in advance.

Best Regards,
Suman
Previous Topic: Oracle 10.7 - Receivables Adjustment issue
Next Topic: Read only segment in DFF
Goto Forum:
  


Current Time: Sat Sep 24 19:51:39 CDT 2016

Total time taken to generate the page: 0.11670 seconds