Sunday, 7 January 2018

Query for AR Transactions Line with GST

SELECT h.trx_uom,
       h.trx_line_quantity,
       h.unit_price,
       h.line_amt,
       h.first_party_primary_reg_num gst_num,
       h.third_party_primary_reg_num cusomer_gst_num,
       b.line_number, b.description, b.uom_code,
       b.quantity_invoiced, b.unit_selling_price rate,
DECODE (b.quantity_invoiced * b.unit_selling_price,NULL, b.extended_amount,b.quantity_invoiced * b.unit_selling_price) totamt,
h.unround_taxable_amt_fun_curr "TAXBLE AMOUNT",
(SELECT jra.reporting_code
FROM jai_reporting_associations jra,
jai_item_templ_hdr jit
WHERE jra.entity_id = jit.template_hdr_id
AND jra.entity_code = 'ITEM'
AND jra.entity_source_table = 'JAI_ITEM_TEMPL_HDR'
AND ROWNUM = 1) "HSN/SAC Code",
h.trx_number,
h.trx_line_number,h.tax_invoice_num,
h.org_id
from
ra_customer_trx_all a ,
ra_customer_trx_lines_all b,
jai_tax_lines_all h,
hz_cust_accounts j
where a.customer_trx_id = h.trx_id
and a.customer_trx_id = b.customer_trx_id
and b.line_type <> 'TAX'
and H.TRX_LINE_ID = b.CUSTOMER_TRX_LINE_ID
and a.trx_number =nvl(:trx_number,a.TRX_NUMBER)
and j.cust_account_id = a.bill_to_customer_id
GROUP BY h.trx_uom,
h.trx_line_quantity,
h.unit_price,
h.line_amt,
h.first_party_primary_reg_num ,
h.third_party_primary_reg_num ,
b.line_number, b.description, b.uom_code,
b.quantity_invoiced, b.unit_selling_price ,
h.unround_taxable_amt_fun_curr,
h.trx_number,
h.trx_line_number,h.tax_invoice_num,
h.org_id,b.extended_amount
Order by trx_number,trx_line_number


6 comments:

  1. Thanks for sharing such a great article about Punchout CXML, it will help me in my work.
    Punchout CXML Catlog




    ReplyDelete

  2. Good to read article about open catalog interface Punchout.
    OCI Punchout




    ReplyDelete
  3. Thanks for sharing article about CXML Punchout
    CXML Punchout

    ReplyDelete
  4. Nice Information.
    PunchOut cXML- Vurbis Interactive used punchout cxml protocol developed by Ariba which helps for online shopping and ordering between e-procurement systems.
    PunchOut cXML

    ReplyDelete
  5. do you have similar type of SQL for Payables

    ReplyDelete