Purchasing Summary Report
Query to get purchaser order summer with invoice no amount tax amount
SELECT AI.ATTRIBUTE1 LOCATION , AI.ATTRIBUTE13 STATE , PV.VENDOR_NAME , PVS.VENDOR_SITE_CODE , ai.INVOICE_NUM , AI.INVOICE_DATE , ROUND(SUM(AID.AMOUNT)) Inv_Amount -- jiatd.TAX_AMOUNT , JITC.TAX_RATE , jitc.TAX_NAME , SUM(jiatd.TAX_AMOUNT) T_TAX , AI.INVOICE_AMOUNT FROM po_vendors pv , po_vendor_sites_all pvs , ja_in_ap_tax_distributions jiatd, ja_in_tax_codes jitc , ap_invoices_all ai , ap_invoice_distributions_all aid WHERE pv.vendor_id =pvs.vendor_id AND ai.vendor_id =pvs.vendor_id AND ai.vendor_site_id =pvs.vendor_site_id AND jitc.tax_id = jiatd.tax_id AND ai.invoice_id = jiatd.invoice_id AND ai.invoice_id = aid.invoice_id AND JIATD.INVOICE_ID = AID.INVOICE_ID AND JIATD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID AND JIATD.PARENT_INVOICE_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID AND aid.LINE_TYPE_LOOKUP_CODE LIKE 'ITEM' AND ai.CANCELLED_DATE IS NULL --AND ai.ATTRIBUTE1 =NVL(:P_Location,ai.ATTRIBUTE1) AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID --AND AI.INVOICE_NUM LIKE '09806%' AND ai.ATTRIBUTE1=NVL(:P_LOCATION,ai.ATTRIBUTE1) AND ( ai.ATTRIBUTE13)=nvl(:P_STATE,ai.ATTRIBUTE13) AND ai.set_of_books_id = :P_SET_OF_BOOKS_ID AND ai.INVOICE_DATE BETWEEN NVL(:p_from_date,ai.INVOICE_DATE) AND nvl(:p_to_date,ai.INVOICE_DATE) AND pvs.vendor_id =nvl(:p_vendor_id,pvs.vendor_id) AND pvs.vendor_site_id = nvl(:p_vendor_site_id,pvs.vendor_site_id) AND pv.VENDOR_TYPE_LOOKUP_CODE = NVL(:p_vndor_class,pv.vendor_type_lookup_code) AND jitc.TAX_NAME = NVL(:p_tax_type,jitc.tax_name) AND PVS.VENDOR_SITE_CODE =NVL( :p_site,pvs.vendor_site_code) GROUP BY AI.INVOICE_NUM ,--jiatd.TAX_AMOUNT, jitc.TAX_NAME , JITC.TAX_RATE , ai.INVOICE_NUM , JIATD.INVOICE_ID , JIATD.TASK_ID , AI.INVOICE_AMOUNT , AI.INVOICE_DATE , AI.ATTRIBUTE1 , AI.ATTRIBUTE13 , PV.VENDOR_NAME , PVS.VENDOR_SITE_CODE
No comments:
Post a Comment