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

2 comments:

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

    ReplyDelete
  2. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete