Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Total Matched Spending by Category in Oracle EBS

User_VVOU1Jan 3 2022

Hi Folks,
I am trying to get the query to find total matched spending by category but some how I am not able to get the correct result. Below is the query I am using:
select sum(aia.invoice_amount), MCB.SEGMENT1
from
apps.mtl_system_items_b MSB,
--apps.mtl_categories_kfv MCK,
APPS.MTL_CATEGORIES_B MCB,
apps.mtl_item_categories MIC, apps.mtl_category_sets_tl MCST,
apps.po_lines_All pla, apps.po_headers_all pha,
apps.po_distributions_all pda, apps.ap_invoice_distributions_all aid,apps.ap_invoices_all aia,
apps.rcv_shipment_lines rsl,
apps.rcv_shipment_headers rsh

where MIC.inventory\_item\_id = MSB.inventory\_item\_id and pla.item\_id= MSB.inventory\_item\_id  
and pha.po\_header\_id=pla.po\_header\_id and pha.org\_id=pla.org\_id  

and pha.po_header_id=pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aia.invoice_id=aid.invoice_id
and MIC.category_id = MCB.category_id
--and pla.po_header_id=44
and MSB.organization_id =MIC.organization_id
AND rsl.po_header_id=pha.po_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
--and MSB.organization_id = 784
and category_Set_name='SWN INV Category Set' and MCST.category_set_id=MIC.category_set_id
and aia.invoice_date>TO_DATE('01-JAN-2021','DD-MON-YYYY')
group by MCB.SEGMENT1

Comments
Post Details
Added on Jan 3 2022
3 comments
410 views