Monday, 30 October 2017

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.

1 comment:

  1. HSN Code was introduced to ensure uniformity of goods all over the world. This was done to reduce costs and efforts and help in the classification of goods in a manner that could be understood by all.

    ReplyDelete