Script for All Purchase Order Details with Invoice and Payment Information in Details

Script for All Purchase Order Details with Invoice and Payment Information in Details

  SELECT aia.invoice_date,

--#############################################################################

--All Purchase Order Details with Invoice and Payment Information in Details

--#############################################################################      

         aia.invoice_num,

         apca.check_date,

         apca.check_number,

         pha.segment1 po_number,

         pha.creation_date po_date,                                         --

         aia.invoice_currency_code "Currency",

         --aia.invoice_amount,

         --       aia.description,

         --       aila.line_number,

         --       aila.description "LineDescription",

         --       aila.quantity_invoiced,

         --       aila.amount "LineAmount",

         --       aila.unit_price,

         --       aila.original_amount,

         --pha.po_header_id,

         --pha.vendor_id,

         pha.comments,

         pv.vendor_name,

         pv.segment1 vendor_num,

         /*       pha.vendor_site_id,

                pvsa.address_line1||' '||

                pvsa.address_line2||' '||

                pvsa.address_line3||' '||

                pvsa.city||' '||

                ftt.territory_short_name ADDRESS,

                pvsa.address_line1,

                pvsa.address_line2,

                pvsa.address_line3,

                pvsa.city,

                ftt.territory_short_name,

                            pvsa.fax,

                pha.terms_id,*/

         apt.Description pay_terms,

         pha.currency_code,

         --pha.rate,

         --pla.po_line_id,

         pla.line_num,

         pla.item_description,

         pla.UNIT_MEAS_LOOKUP_CODE,

         pla.unit_price,

         LTRIM (pla.quantity) Quantity,

         pla.quantity * pla.unit_price * NVL (pha.rate, 1) amount,

         MTL_CATEGORIES_B.segment1 catdesc,

         (SELECT CONCATENATED_SEGMENTS

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = pod.code_combination_id)

            dist_account        

    FROM apps.po_headers_all pha,

         apps.po_lines_all pla,

         apps.ap_terms apt,

         apps.po_vendors pv,

         apps.po_vendor_sites_all pvsa,

         apps.fnd_territories_tl ftt,

         apps.MTL_CATEGORIES_B,

         apps.po_line_locations_all poll,

         apps.po_distributions_all pod,

         apps.ap_invoice_distributions_all apid,

         ap.ap_invoice_lines_all aila,

         ap.ap_invoices_all aia,

         ap.ap_invoice_payments_all aipa,

         ap.ap_checks_all apca

   WHERE     pha.org_id = 81

         --AND PHA.segment1 = '151900005'

         AND pha.PO_HEADER_ID = pla.PO_HEADER_ID

         AND pha.terms_id = apt.term_id

         AND pha.vendor_id = pv.vendor_id

         AND pha.vendor_site_id = pvsa.vendor_site_id

         AND pvsa.country = ftt.territory_code(+)

         AND ftt.language(+) = 'AR'

         AND MTL_CATEGORIES_B.CATEGORY_ID = pla.CATEGORY_ID

         AND pla.PO_LINE_ID = poll.PO_LINE_ID

         AND poll.LINE_LOCATION_ID = pod.LINE_LOCATION_ID

         AND pod.po_distribution_id = apid.po_distribution_id(+)

         AND aila.invoice_id = apid.invoice_id

         AND aila.line_number = apid.INVOICE_LINE_NUMBER

         AND aia.invoice_id = aila.invoice_id

         AND aipa.check_id = apca.check_id

         AND aipa.invoice_id = aia.invoice_id

         AND pha.creation_date BETWEEN :P_FROM AND :P_TO

ORDER BY pha.segment1, pla.line_num