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
Got any queries?
Do drop a note by writing us at Doyen.ebiz@staging.doyensys.com or use the comment section below to ask your questions.
