Query to Fetch Invoice details, HSN Code, Third party Registration number and GST Details.

The below query fetches the details of an Invoice along with the HSN Code of  a respective item present in an Invoice. GST details can also be fetched.


SELECT a.org_id,a.customer_trx_id,a.trx_number invno,a.trx_date, c.trx_line_id,
                         a.cust_trx_type_id,a.bill_to_customer_id,a.bill_to_site_use_id, b.line_number AS linenum,
                         b.inventory_item_id,d.segment1 itm,b.interface_line_attribute6 ,
                         SUBSTR(b.description,1,40) description1,SUBSTR(b.description,40,55)  description2,
                         (SELECT SUBSTR (REPLACE(f.template_name,' ',''),4,11) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id AND ROWNUM =1) hsn_code,
                          (SELECT SUBSTR (REPLACE(f.template_name,' ', ''),12,18) FROM jai_item_templ_hdr e,
                                                       jai_item_templ_hdr f
                                                 WHERE e.inventory_item_id = b.inventory_item_id
                                                   AND e.entity_id = f.template_hdr_id AND ROWNUM =1) hsn_code1,                       
                         b.uom_code,a.primary_salesrep_id,b.sales_order_line,a.interface_header_attribute1 ordernum,
                         TO_NUMBER(a.interface_header_attribute10) organization_id,B.quantity_invoiced,
                         b.unit_standard_price sprice,b.unit_selling_price,b.revenue_amount,b.extended_amount,
                         SUM(c.actual_tax_rate)tax_rate,SUM(c.unround_tax_amt_fun_curr)tax_amount,b.line_type,b.line_number,
--              DECODE(TAX_AMOUNT,0,0,null,0,0.00,0,VAT_ASSESSABLE_VALUE) taxgross,
                         NULL taxgross,warehouse_id, SUBSTR(NVL(d.attribute5,'   '),1,3) AS sch_category
               FROM      ra_customer_trx_all a,
                         ra_customer_trx_lines_all b,
                         jai_tax_lines_all c,
                         (SELECT * FROM mtl_system_items WHERE organization_id = 83) d
                 WHERE    a.customer_trx_id          = b.customer_trx_id
                   AND    a.trx_number                     = :p_trx_number
--                         AND b.interface_line_attribute6= c.line_id
                   AND    d.inventory_item_id               = b.inventory_item_id
                   AND    a.customer_trx_id                   = c.trx_id           
                   AND    b.customer_trx_line_id           = c.trx_line_id       
                   AND    b.line_type                              = 'LINE'
--                  AND a.trx_number = '11111700004'
                  AND a.interface_header_attribute1 IS NOT NULL
       GROUP BY a.org_id,a.customer_trx_id,a.trx_number ,a.trx_date, c.trx_line_id,
                         a.cust_trx_type_id,a.bill_to_customer_id,a.bill_to_site_use_id, b.line_number,
                         b.inventory_item_id,d.segment1,b.description,b.uom_code,B.quantity_invoiced,
                         b.unit_standard_price,b.unit_selling_price,b.revenue_amount,b.extended_amount,
                         c.actual_tax_rate,c.unround_tax_amt_fun_curr,b.interface_line_attribute6,
                         a.primary_salesrep_id,b.sales_order_line,a.interface_header_attribute1,a.interface_header_attribute10,
                         b.line_type,b.line_number,warehouse_id,d.attribute5



Query to fetch Third Party Registration Number(GST Number) :-


SELECT DISTINCT TRIM(TO_CHAR(B.registration_number))GST_NUM
       INTO  mcustvatno
       FROM  jai_party_regs a,
             jai_party_reg_lines b,
             ra_customer_trx_all rct,
             hz_cust_accounts bill_cus,
             hz_parties bill_party,
             hz_cust_site_uses_all hcs_bill,
             hz_cust_acct_sites_all hca_bill,
             hz_party_sites hps_bill,
             hz_locations hl_bill
    WHERE    1 = 1
      AND    a.party_reg_id                 = b.party_reg_id
      AND    a.party_type_code            = 'THIRD_PARTY_SITE'
      AND    b.regime_id                       = 10000
      AND    b.registration_type_code   = 'GST'
      AND    a.party_id                          = rct.bill_to_customer_id
      AND    rct.bill_to_customer_id    = bill_cus.cust_account_id
      AND    bill_party.party_id            = bill_cus.party_id
      AND    rct.bill_to_site_use_id      = hcs_bill.site_use_id
      AND    hcs_bill.site_use_code      = 'BILL_TO'
      AND    hca_bill.cust_acct_site_id = hcs_bill.cust_acct_site_id
      AND    hps_bill.party_site_id     =  hca_bill.party_site_id
      AND    a.party_site_id                =  hca_bill.cust_acct_site_id
      AND    hl_bill.location_id          =  hps_bill.location_id
      AND    rct.customer_trx_id        = :p_customer_trx_id;



Note:    Similarly we can get First Party Registration Number. In the above query replace the party_type_code as 'I/O' which will fetch you first party registration number.