Oracle Item Price By Supplier Query

SELECT   hou.NAME operating_unit,

         msik.concatenated_segments item,

         msik.description,

         aps.vendor_name,

         aps.segment1 supplier_number,

         pha.segment1 po_number,

         TRUNC (pha.creation_date) po_date,

         NVL (pha.authorization_status, 'INCOMPLETE') status,

         pla.unit_price rate,

         pha.currency_code,

         SUM (pla.quantity) quantity,

         ROUND (SUM (pla.quantity * pla.unit_price), 2) basic_amount,

         SUM (pla.quantity * pla.unit_price) basic_amount_INR,

        

         CASE

            WHEN pha.currency_code <> 'INR'

               THEN ROUND (SUM (  pla.quantity

                                * pla.unit_price

                                * (SELECT gdr.conversion_rate

                                     FROM apps.gl_daily_rates gdr

                                    WHERE gdr.from_currency = pha.currency_code

                                      AND gdr.to_currency = 'INR'

                                      AND gdr.conversion_type = 'Corporate'

                                      AND ROWNUM = 1

                                      AND TRUNC (gdr.conversion_date) = TRUNC (pha.creation_date))

                               ),

                           2

                          )

            ELSE SUM (pla.quantity * pla.unit_price)

         END basic_amount_inr,

        

         (SELECT SUM (jpt.tax_amount)

            FROM apps.jai_po_taxes jpt

           WHERE UPPER (jpt.tax_type) IN

                    ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS', 'ADDITIONAL_CVD',

                     'CVD_EDUCATION_CESS', 'CUSTOMS', 'CVD_SH_EDU_CESS', 'CVD',

                     'CUSTOMS_EDUCATION_CESS', 'CUSTOMS_SH_EDU_CESS')

             AND jpt.line_location_id = pll.line_location_id

             AND jpt.po_line_id = pll.po_line_id

             AND jpt.po_header_id = pll.po_header_id) excise_amount,

        

         (SELECT SUM (tax_amount)

            FROM apps.jai_po_taxes jpt

           WHERE UPPER (jpt.tax_type) IN ('SERVICE', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')

             AND jpt.line_location_id = pll.line_location_id

             AND jpt.po_line_id = pll.po_line_id

             AND jpt.po_header_id = pll.po_header_id) service_tax_amount,

        

         (SELECT SUM (tax_amount)

            FROM apps.jai_po_taxes jpt

           WHERE UPPER (jpt.tax_type) IN ('CST')

             AND jpt.line_location_id = pll.line_location_id

             AND jpt.po_line_id = pll.po_line_id

             AND jpt.po_header_id = pll.po_header_id) cst_amount,

        

         (SELECT SUM (tax_amount)

            FROM apps.jai_po_taxes jpt

           WHERE UPPER (jpt.tax_type) IN ('VAT')

             AND jpt.line_location_id = pll.line_location_id

             AND jpt.po_line_id = pll.po_line_id

             AND jpt.po_header_id = pll.po_header_id) vat_amount,

        

         (SELECT SUM (tax_amount)

            FROM apps.jai_po_taxes jpt

           WHERE UPPER (jpt.tax_type) NOT IN

                    ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS', 'ADDITIONAL_CVD',

                     'CVD_EDUCATION_CESS', 'CUSTOMS', 'CVD_SH_EDU_CESS', 'CVD',

                     'CUSTOMS_EDUCATION_CESS', 'CUSTOMS_SH_EDU_CESS', 'SERVICE',

                     'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS', 'VAT', 'CST')

             AND jpt.line_location_id = pll.line_location_id

             AND jpt.po_line_id = pll.po_line_id

             AND jpt.po_header_id = pll.po_header_id) other_tax,

        

          (  (SELECT NVL (SUM (aia.invoice_amount), 0)

                FROM apps.ap_invoices_all aia

               WHERE aia.quick_po_header_id = pll.po_header_id)

           + (SELECT NVL (SUM (aila.amount), 0)

                FROM apps.ap_invoices_all aia, apps.ap_invoice_lines_all aila

               WHERE aia.invoice_id = aila.invoice_id

                 AND aila.po_header_id = pll.po_header_id

                 AND aila.po_line_id = pll.po_line_id

                 AND aila.po_line_location_id = pll.line_location_id

                 AND aia.quick_po_header_id IS NULL)

          ) invoice_amount,

        

         (SELECT SUM (aid.amount)

            FROM apps.rcv_shipment_headers rsh,

                 apps.rcv_shipment_lines rsl,

                 apps.rcv_transactions rt,

                 apps.ap_invoice_distributions_all aid

           WHERE rsh.shipment_header_id = rsl.shipment_header_id

             AND rsl.shipment_line_id = rt.shipment_line_id

             AND rt.transaction_id = aid.rcv_transaction_id

             AND rsl.po_header_id = pha.po_header_id) invoice_amount,

            

         apt.NAME payment_term,

         SUBSTR (msik.concatenated_segments, -5) packing_size,

         prh.segment1 requisition_number,

         TRUNC (prh.creation_date) requisition_date

   

    FROM apps.po_headers_all pha,

         apps.po_lines_all pla,

         apps.po_line_locations_all pll,

         apps.mtl_system_items_kfv msik,

         apps.ap_suppliers aps,

         apps.ap_terms apt,

         apps.po_requisition_lines_all prl,

         apps.po_requisition_headers_all prh,

         apps.hr_operating_units hou

   WHERE pha.po_header_id = pla.po_header_id

     AND pla.po_line_id = pll.po_line_id

     AND pla.item_id = msik.inventory_item_id

     AND pll.ship_to_organization_id = msik.organization_id

    /*and msik.ORGANIZATION_ID = (SELECT MIN(ORGANIZATION_ID)

                             FROM mtl_system_items_kfv

                             WHERE INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID) */

     AND pha.vendor_id = aps.vendor_id

     AND pha.terms_id = apt.term_id

     AND pll.line_location_id = prl.line_location_id(+)

     AND prl.requisition_header_id = prh.requisition_header_id(+)

     AND pha.org_id = hou.organization_id

     --  AND PHA.CURRENCY_CODE='USD'

     --    AND HOU.NAME  not IN ('DKCI Dadra OU' , 'DKSC Mundra OU')

     --  AND msik.concatenated_segments LIKE '2%'

     --and pha.segment1 = '12100325'

    

GROUP BY hou.NAME,

         msik.concatenated_segments,

         msik.description,

         aps.vendor_name,

         aps.segment1,

         pha.po_header_id,

         pha.segment1,

         TRUNC (pha.creation_date),

         pha.authorization_status,

         pla.unit_price,

         pha.currency_code,

         pll.line_location_id,

         pll.po_line_id,

         pll.po_header_id,

         apt.NAME,

         prh.segment1,

         TRUNC (prh.creation_date)