Query for Open PO - Purchase Order with Item Attachments

Query for Open PO - Purchase Order with Item Attachments

SELECT line_num,item_number,PO_NUMBER ,
                item_description,
                unit_meas_lookup_code,quantity, unit_price,
                line_status, po_line_id from (
SELECT DISTINCT PLV.line_num, msi1.segment1 item_number, (select segment1 from po_headers where po_header_id= PLV.po_header_id) PO_NUMBER,
                PLV.item_description item_description,
                PLV.unit_meas_lookup_code, PLV.quantity, PLV.unit_price,
                NVL (PLV.closed_code, 'OPEN') line_status, PLV.po_line_id
           FROM po_lines PLV,
                ( SELECT msi.*
        FROM apps.fnd_document_datatypes   dat,
             apps.fnd_document_entities_tl det,
             apps.fnd_documents_tl         dt,
             apps.fnd_documents            d,
             apps.fnd_attached_documents   ad,
             apps.fnd_documents_long_text  fdlt,
             apps.fnd_documents_short_text fdst,
             apps.mtl_system_items_b msi
       WHERE d.document_id = ad.document_id
         AND dt.document_id = d.document_id
         AND dt.LANGUAGE = USERENV('LANG')
         AND d.datatype_id = dat.datatype_id
         AND dat.LANGUAGE = USERENV('LANG')
         AND ad.entity_name = det.data_object_code
         AND det.LANGUAGE = USERENV('LANG')
         AND ad.pk1_value = msi.organization_id
         AND ad.pk2_value = msi.inventory_item_id
         --AND dt.description = v_chr_desc --TRIM(rec_attach.description)
         AND dt.media_id = fdlt.media_id(+)
         AND dt.media_id = fdst.media_id(+)
         AND ad.entity_name = 'MTL_SYSTEM_ITEMS'
         AND dat.user_name IN ('Long Text', 'Short Text')
         AND dt.description ='ATTACHMET'
         AND msi.inventory_item_status_code ='Active'
        -- AND msi.segment1 = 'VE3006'
         AND msi.organization_id = 701) msi1,
                financials_system_parameters fsp,
                po_line_locations pllv,
                po_distributions pdv,
                org_organization_definitions ood
          WHERE msi1.inventory_item_id = PLV.item_id
            AND NVL (msi1.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
            AND PLV.po_line_id = pllv.po_line_id
            AND PLV.po_line_id = pdv.po_line_id
            AND pllv.line_location_id = pdv.line_location_id
            AND ood.organization_id = pllv.ship_to_organization_id
            AND NVL (PLV.closed_code, 'OPEN') NOT IN
                                                 ('FINALLY CLOSED', 'CLOSED')
            AND NVL (PLV.cancel_flag, 'N') = 'N'
            AND EXISTS (
                   SELECT 'x'
                     FROM po_line_locations pll
                    WHERE pll.po_line_id = PLV.po_line_id
                      AND NVL (pll.closed_code, 'OPEN') NOT IN
                                                 ('FINALLY CLOSED', 'CLOSED')
                      AND NVL (pll.cancel_flag, 'N') = 'N'
                      AND pll.quantity - pll.quantity_received >
                                                         pll.qty_rcv_tolerance))