Sales Order details with India Localization tax

SELECT h.header_id header_id, party.party_name sold_to,
       h.order_number order_number,
       NVL (TO_CHAR (h.ordered_date, ‘DD-MON-YYYY’),
            TO_CHAR (h.request_date, ‘DD-MON-YYYY’)
           ) ordered_date,
       (SELECT TO_CHAR (MAX (oola.schedule_ship_date),
                        ‘DD-MON-YYYY’
                       )
          FROM oe_order_lines_all oola
         WHERE oola.header_id = h.header_id) dispatch_date,
       bill_su.LOCATION invoice_to_location,
       bill_loc.address1 invoice_to_address1,
       bill_loc.address2 invoice_to_address2,
       bill_loc.address3 invoice_to_address3,
       bill_loc.address4 invoice_to_address4,
          DECODE (bill_loc.city,
                  NULL, NULL,
                  bill_loc.city || ‘, ‘
                 )
       || DECODE (bill_loc.state,
                  NULL, bill_loc.province || ‘, ‘,
                  bill_loc.state || ‘, ‘
                 )
       || DECODE (bill_loc.postal_code,
                  NULL, NULL,
                  bill_loc.postal_code || ‘, ‘
                 )
       || DECODE (ft.territory_short_name,
                  NULL, NULL,
                  ft.territory_short_name || ‘.’
                 ) invoice_to_address5,
       ship_su.LOCATION ship_to_location, ship_loc.address1 ship_to_address1,
       ship_loc.address2 ship_to_address2, ship_loc.address3 ship_to_address3,
       ship_loc.address4 ship_to_address4,
          DECODE (ship_loc.city,
                  NULL, NULL,
                  ship_loc.city || ‘, ‘
                 )
       || DECODE (ship_loc.state,
                  NULL, ship_loc.province || ‘, ‘,
                  ship_loc.state || ‘, ‘
                 )
       || DECODE (ship_loc.postal_code,
                  NULL, NULL,
                  ship_loc.postal_code || ‘, ‘
                 )
       || DECODE (ft.territory_short_name,
                  NULL, NULL,
                  ft.territory_short_name || ‘.’
                 ) ship_to_address5,
       h.cust_po_number reference_number, h.attribute1 transporter_name,
       term.NAME terms, cust_acct.cust_account_id cust_account_id,
       l.line_number line_no,
       (SELECT NVL (l1.user_item_description, msi.description)
          FROM oe_order_lines_all l1, mtl_system_items_b msi
         WHERE l1.inventory_item_id = msi.inventory_item_id
           AND l1.ship_from_org_id = msi.organization_id
           AND l1.header_id = l.header_id
           AND l1.line_id = l.line_id
           AND l1.inventory_item_id = l.inventory_item_id) particulars,
       l.unit_selling_price unit_selling_price,
       l.ordered_quantity ordered_quantity,
      — l.order_quantity_uom order_quantity_uom,
      (SELECT primary_unit_of_measure
          FROM oe_order_lines_all l2, mtl_system_items_b msi1
         WHERE l2.inventory_item_id = msi1.inventory_item_id
           AND l2.ship_from_org_id = msi1.organization_id
           AND l2.header_id = l.header_id
           AND l2.line_id = l.line_id
           AND l2.inventory_item_id = l.inventory_item_id) order_quantity_uom,
       (l.ordered_quantity * l.unit_selling_price) line_amount,
       NVL (jcta.tax_rate, 0) tax_rate,
       (SELECT NVL (SUM (  ABS (NVL (amount_due_remaining, 0))
                         + ABS (NVL (tax_remaining, 0))
                        ),
                    0
                   )
          FROM ar_payment_schedules_all apsa1
         WHERE 1 = 1
           AND apsa1.CLASS IN (‘PMT’)
           AND apsa1.customer_id = h.sold_to_org_id) customer_balance,
       h.attribute2 remarks, fu.user_name prepared_by,
       l.ordered_item item_code, jcca.pan_no pan_no,
       jcca.cst_reg_no cst_reg_no, jcca.vat_reg_no vat_reg_no,
       jcca.service_tax_regno service_tax_regno
  FROM hz_parties party,
       hz_cust_accounts cust_acct,
       oe_order_headers_all h,
       oe_order_lines_all l,
       hz_cust_site_uses_all bill_su,
       hz_locations bill_loc,
       hz_cust_acct_sites_all bill_cas,
       hz_party_sites bill_ps,
       hz_cust_site_uses_all ship_su,
       hz_party_sites ship_ps,
       hz_locations ship_loc,
       hz_cust_acct_sites_all ship_cas,
       ra_terms_tl term,
       jai_om_oe_so_taxes jst,
       jai_cmn_taxes_all jcta,
       fnd_user fu,
       jai_cmn_cus_addresses jcca,
       fnd_territories_vl ft
 WHERE 1 = 1
   AND h.header_id = l.header_id
   AND h.org_id = l.org_id
   AND cust_acct.party_id = party.party_id
   AND h.sold_to_org_id = cust_acct.cust_account_id
   AND h.invoice_to_org_id = bill_su.site_use_id(+)
   AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
   AND bill_cas.party_site_id = bill_ps.party_site_id(+)
   AND bill_loc.location_id(+) = bill_ps.location_id
   AND h.ship_to_org_id = ship_su.site_use_id(+)
   AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
   AND ship_cas.party_site_id = ship_ps.party_site_id(+)
   AND ship_loc.location_id(+) = ship_ps.location_id
   AND h.payment_term_id = term.term_id(+)
   AND term.LANGUAGE(+) = USERENV (‘LANG’)
   AND l.header_id = jst.header_id(+)
   AND l.line_id = jst.line_id(+)
   AND h.created_by = fu.user_id
   AND jst.tax_id = jcta.tax_id(+)
   AND jcta.tax_type IN (‘CST’, ‘VALUE ADDED TAX’)
   AND cust_acct.cust_account_id = jcca.customer_id
   AND ship_su.cust_acct_site_id = jcca.address_id
   AND ship_su.site_use_code = ‘SHIP_TO’
   AND h.flow_status_code IN (‘BOOKED’)
   AND bill_loc.country = ft.territory_code(+)
   AND h.order_number = :p_sales_order

   —Pradipta Behera