Introduction:
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.
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
