/* Formatted on 8/22/2021 11:39:45 AM (QP5 v5.136.908.31019) */
  SELECT DISTINCT
         RCTL.LINE_NUMBER AS "SN",
         NVL (mtl.segment1, ' ') AS "Item Code",
         rct.TRX_NUMBER AS "Invoice Number",
         hzp.PARTY_NAME AS "Customer Name",
         rctl.DESCRIPTION AS "Item Description",
         rct.INVOICE_CURRENCY_CODE AS "Currency",
         rctl.QUANTITY_INVOICED AS "Quantity",
         NVL (rctl.UOM_CODE, ' ') AS "UOM",
         ROUND (rctl.UNIT_SELLING_PRICE, 2) AS "Unit Price",
         rctl.EXTENDED_AMOUNT AS "Total",
         rctl.TAX_RECOVERABLE AS "VAT Value",
         NVL (hcsua.TAX_REFERENCE, ' ') AS "VAT Registration Num CUS",
         DECODE (hcsua.TAX_CODE, 'VAT-STANDARD RATED SALES 15%', '15%', '0%')
            AS "VAT",
         rctd.GL_DATE AS "Invoice Date",
         NVL (hscp.TX10, '  ') AS "Phone Number",
         NVL (hzp.ADDRESS1, '  ') AS "Customer Address 0",
         NVL (
            DECODE (hzp.PARTY_NAME,
                    'JEBEL ARAFAT TRADING CO ( LLC )', hzls.ADDRESS1,
                    hzp.ADDRESS1),
            ' ')
            AS "Customer Address 1",
         NVL (hzp.ADDRESS2, '  ') AS "Customer Address 2",
         NVL (
            DECODE (hzp.PARTY_NAME,
                    'JEBEL ARAFAT TRADING CO ( LLC )', hzls.ADDRESS3,
                    hzp.ADDRESS3),
            ' ')
            AS "Customer Address 3",
         NVL (hzp.ADDRESS3, '  ') AS "Customer Address 4",
         NVL (hzp.ADDRESS4, '  ') AS "Customer Address 5",
         apsa.DUE_DATE AS "Due Date",
         NVL (rct.EXCHANGE_RATE, 1) AS "Exchange Rate",
         DECODE (rct.INVOICE_CURRENCY_CODE,
                 'SAR', rctl.UNIT_STANDARD_PRICE,
                 rct.EXCHANGE_RATE * rctl.UNIT_STANDARD_PRICE)
            AS "Unit Price SAR",
         DECODE (rct.INVOICE_CURRENCY_CODE,
                 'SAR', rctl.EXTENDED_AMOUNT,
                 rct.EXCHANGE_RATE * rctl.EXTENDED_AMOUNT)
            AS "Total SAR",
         hzps.PARTY_NAME AS "Supplier Name",
         hzps.COUNTRY AS "Country",
         hzps.ADDRESS1 AS "Supplier Address",
         hzps.CITY AS "City",
         hcsuall.TAX_REFERENCE AS "VAT Registration Num SUPP",
         rctl.EXTENDED_AMOUNT + rctl.TAX_RECOVERABLE AS "Total After VAT",
         hcasa.PARTY_SITE_ID
    FROM hz_parties hzp,
         hz_cust_accounts hca,
         ra_customer_trx_all rct,
         ra_customer_trx_lines_all rctl,
         ra_cust_trx_line_gl_dist_all rctd,
         mtl_system_items_b mtl,
         MTL_SYSTEM_ITEMS_TL mtli,
         RA_TERMS_TL rtt,
         ar_payment_schedules_all apsa,
         hz_cust_site_uses_all hcsua,
         HZ_PARTY_SITES hps,
         HZ_CUST_ACCT_SITES_ALL hcasa,
         hz_parties hzps,
         hz_cust_site_uses_all hcsuall,
         HZ_LOCATIONS hzl,
         HZ_LOCATIONS hzls,
         HZ_STAGED_CONTACT_POINTS hscp
   WHERE     hzp.party_id = hca.party_id
         AND hca.cust_account_id = rct.bill_to_customer_id
         AND rct.customer_trx_id = rctl.customer_trx_id
         AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
         AND rctl.INVENTORY_ITEM_ID = mtl.INVENTORY_ITEM_ID(+)
         AND rtt.TERM_ID = rct.TERM_ID
         AND apsa.CUSTOMER_TRX_ID = rct.customer_trx_id
         AND hps.PARTY_ID = hca.PARTY_ID
         AND hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
         AND hcsua.CUST_ACCT_SITE_ID = hcasa.CUST_ACCT_SITE_ID
         AND hps.LOCATION_ID = hzl.LOCATION_ID
         AND hscp.PARTY_ID(+) = hzp.PARTY_ID
         AND mtl.INVENTORY_ITEM_ID = mtli.INVENTORY_ITEM_ID(+)
         AND rtt.LANGUAGE = 'US'
         AND rct.TRX_NUMBER = '&INVOICE_NO'
         AND hcsua.tax_code IS NOT NULL
         AND rctl.DESCRIPTION IS NOT NULL
         AND hzl.ADDRESS_LINES_PHONETIC IS NOT NULL
         AND hzps.PARTY_NAME = '&PARTY_NAME'
         AND hcsuall.TAX_REFERENCE = '&PARTY_TAX_REF'
ORDER BY RCTL.LINE_NUMBER