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