Introduction
This Post is about to Report Will Fetch All VAT details in the AP Module in Oracle EBS-R12.
<dataTemplate name=”IRNGLBFRTVAAP” version=”1.0″>
<parameters>
<parameter name=”P_ACCT_FROM_DT” dataType=”DATE”/>
<parameter name=”P_ACCT_TO_DT” dataType=”DATE”/>
</parameters>
<dataQuery>
<sqlStatement name=”Q_HEAD_PARAMTERS”>
<![CDATA[
Select to_char(:P_ACCT_FROM_DT,’DD-Mon-RRRR’) FROM_DATE,to_char(:P_ACCT_TO_DT,’DD-Mon-RRRR’) TO_DATE from dual
]]>
</sqlStatement>
<sqlStatement name=”Q_1″>
<![CDATA[
SELECT
v_nr “Four”,
name “Fournisseur”,
country “Pays”,
tax_code “Code_TVA”,
”” || check_number “Paiement”,
mont_ht “Mont_Ht”,
tva “TVA”,
period_name “Period”,
”” || invoice_num “Facture”,
converted “Exact”,
DECODE(country,’FR’,DECODE(converted,’Y’,round(mont_ht / 1.2),NULL),NULL) “HT_calcule”,
DECODE(country,’FR’,DECODE(converted,’Y’,round(mont_ht / 1.2 * 0.2,2),NULL),NULL) “TVA_calcule”,
DECODE(immo,’Y’,’Immo’,”) “Immo”
FROM
(
SELECT
v_nr,
name,
nvl(country,’FR’) country,
tax_code,
check_number,
SUM(nvl(item,NULL) ) mont_ht,
SUM(nvl(tax,NULL) ) tva,
period_name,
invoice_num,
converted,
immo
FROM
(
SELECT
p.payment_num,
p.period_name,
i.invoice_num,
sign(p.amount) sgn,
c.check_number,
round(DECODE(aid.line_type_lookup_code,’ITEM’,aid.amount,NULL) /* * sign(p.amount) */ * p.amount / i.invoice_amount,2) item,
round(DECODE(aid.line_type_lookup_code,’REC_TAX’,aid.amount,NULL) /* * sign(p.amount) */ * p.amount / i.invoice_amount,2) tax,
nvl( (
SELECT
tax_rate_code
FROM
apps.zx_rates_b t
WHERE
aid.tax_code_id = t.tax_rate_id
),’Net’) tax_code,
v.segment1 v_nr,
v.vendor_name name,
vs.country,
DECODE(substr(b.batch_name,1,8),’OPEN BAL’,’Y’,”) converted,
aid.assets_addition_flag immo
FROM
apps.ap_invoice_payments_all p,
apps.ap_invoices_all i,
apps.ap_checks_all c,
apps.ap_invoice_distributions_all aid,
apps.po_vendors v,
apps.po_vendor_sites_all vs,
apps.ap_batches_all b
WHERE
i.invoice_id = p.invoice_id
AND p.check_id = c.check_id
AND i.invoice_id = aid.invoice_id
/* changer la date */
AND TRUNC(p.accounting_date) > TRUNC(:P_ACCT_FROM_DT)
AND TRUNC(p.accounting_date) < TRUNC(:P_ACCT_TO_DT)
–and p.accounting_date > to_date(‘30.06.2018′,’DD.MM.YYYY’)
–and p.accounting_date < to_date(‘01.08.2018′,’DD.MM.YYYY’)
AND b.batch_id = i.batch_id
AND v.vendor_id = vs.vendor_id
AND v.vendor_id = i.vendor_id
AND i.vendor_site_id = vs.vendor_site_id
AND i.cancelled_date IS NULL
AND p.org_id = 376
AND nvl(aid.amount,0) <> 0
)
GROUP BY
check_number,
period_name,
invoice_num,
tax_code,
v_nr,
name,
country,
converted,
immo
)
ORDER BY
check_number,
period_name,
invoice_num,
tax_code,
immo
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name=”G_HEAD_PARAMTERS” dataType=”varchar2″ source=”Q_HEAD_PARAMTERS” >
<element name=”FROM_DATE” value=”FROM_DATE”/>
<element name=”TO_DATE” value=”TO_DATE”/>
<group name=”G_MAIN” dataType=”varchar2″ source=”Q_1″>
<element name=”Four” value=”Four”/>
<element name=”Fournisseur” value=”Fournisseur”/>
<element name=”Pays” value=”Pays”/>
<element name=”Code_TVA” value=”Code_TVA”/>
<element name=”Paiement” value=”Paiement”/>
<element name=”Mont_Ht” value=”Mont_Ht”/>
<element name=”TVA” value=”TVA”/>
<element name=”Period” value=”Period”/>
<element name=”Facture” value=”Facture”/>
<element name=”Exact” value=”Exact”/>
<element name=”HT_calcule” value=”HT_calcule”/>
<element name=”TVA_calcule” value=”TVA_calcule”/>
<element name=”Immo” value=”Immo”/>
</group>
</group>
</dataStructure>
</dataTemplate>
Summary
This Post described about to report will fetch all VAT details in the AP module in oracle EBS-R12
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
