Tuesday, December 3, 2013

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)

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect