Wednesday 25 October 2017

TAX INVOICE RCM(REVERSE CHARGE MECHANISM) - GST

The below query fetches the details of a tax invocie  where in Reverse charge mechanism(RCM) is applied in order to claim the extra tax paid by an organization along with the GST details .



SELECT AIL.description,AIL.amount,1 quantity,AIA.invoice_num,AIA.invoice_date,APS.vendor_name,ASSA.address_line1,ASSA.address_line2,ASSA.address_line3,ASSA.city,ASSA.state, ASSA.zip, JTL.first_party_primary_reg_num,J TL.third_party_primary_reg_num,
 OOD.organization_id,  OOD.organization_name, AIA.invoice_amount, HL.address_line_1 address_1, HL.address_line_2 address_2, HL.address_line_3 address_3,HL.town_or_city, HL.postal_code, HL.region_1,(SELECT TAR.rev_seqno  FROM 
                                                      ,org_organization_definitions OOD
                                                                              ,ap_invoices_all AIA
                                                                               WHERE 1=1
                                                                               AND AIA.invoice_id           = TAR.ap_invoice_id
                                                                              AND OOD.organization_id   = TAR.organization_id
                                                                              AND OOD.ORGANIZATION_ID  = :p_org_id
                                                                              AND AIA.INVOICE_NUM            = :p_invnum ) rev_seqno
FROM ap_invoices_all AIA
    ,ap_invoice_lines_all AIL
    ,ap_suppliers APS
    ,ap_supplier_sites_all ASSA
    ,jai_tax_lines_all JTL
    ,org_organization_definitions OOD
    ,hr_locations HL
WHERE 1=1
AND AIA.invoice_id         = AIL.invoice_id
AND AIA.vendor_id          = ASSA.vendor_id
AND APS.vendor_id          = ASSA.vendor_id
AND AIA.vendor_site_id   = ASSA.vendor_site_id
AND AIA.vendor_id           = JTL.party_id
AND AIA.invoice_id          =  JTL.trx_id
AND AIL.line_number        = JTL.trx_line_number
AND JTL.organization_id   =  OOD.organization_id
AND OOD.organization_id  = HL.inventory_organization_id
-- AND AIA.invoice_id        =3904571
AND OOD.ORGANIZATION_ID  = :p_org_id
AND AIA.INVOICE_NUM      = :p_invnum
GROUP BY AIL.description, AIL.amount, AIA.invoice_num, AIA.invoice_date, APS.vendor_name, ASSA.address_line1,ASSA.address_line2,ASSA.address_line3,ASSA.city,ASSA.state,
ASSA.zip,JTL.first_party_primary_reg_num,JTL.third_party_primary_reg_num,
OOD.organization_id, OOD.organization_name, AIA.invoice_amount, HL.address_line_1, HL.address_line_2,HL.address_line_3,HL.town_or_city,  HL.postal_code, HL.region_1

1 comment:

  1. This query is not working. Alias name TAR is given. For which table the TAR is given.

    ReplyDelete