Modelo 347 y 415 – Oracle eBS

Antes de finalizar este mes, todas las compañías tendrán que presentar el modelo 347 y el 415 correspondiente a operaciones con terceros, sin entrar en detalles hay que declarar el importe total con proveedores y clientes que durante el 2012 hayan superado 3.005,06 €. El año pasado hubo una modificación del modelo que supone que hay que presentar los importes también por trimestres.

Si durante el 2012 se ha producido un upgrade a la R12 o una migración quizás el modelo estándar no te sirva o necesites consolidarlo. Dejo aquí un par de queries que serán muy útiles para hacer comprobaciones:

=============================================================
— —
— MODELO 347 y 415 CLIENTES–
— —
=============================================================

Select

ca.cust_account_id XX_Customer_ID_XX,
pt.PARTY_NAME CLIENTE,
pt.jgzz_fiscal_code NIF,
DECODE(ra.country, ‘ES’, SUBSTR(ra.postal_code, 1, 2), ’99’) COD_PROVINCIA,
SUM (Gld.acctd_amount) importe_anual,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(gld.gl_date, ‘MM’)) <= 3 THEN
ROUND(NVL(gld.acctd_amount, 0), 2)
ELSE
0
END) importe_Q1,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(gld.gl_date, ‘MM’)) BETWEEN 4 AND 6 THEN
ROUND(NVL(gld.acctd_amount, 0), 2)
ELSE
0
END) importe_Q2,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(gld.gl_date, ‘MM’)) BETWEEN 7 AND 9 THEN
ROUND(NVL(gld.acctd_amount, 0), 2)
ELSE
0
END) importe_Q3,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(gld.gl_date, ‘MM’)) >= 10 THEN
ROUND(NVL(gld.acctd_amount, 0), 2)
ELSE
0
END) importe_Q4

From apps.HZ_PARTIES pt, — los parties los unimos por el party_id
apps.hz_cust_acct_sites_all casa,
apps.HZ_CUST_ACCOUNTS ca,
apps.Ra_Customer_Trx_All fa,
apps.HZ_CUST_SITE_USES_ALL Rsu, — la unimos por el site_use_id
apps.Ra_Customer_Trx_Lines_All Rtl,
Apps.Ra_Cust_Trx_Line_Gl_Dist_All Gld,
Apps.Ra_Cust_Trx_Types_All Rtt,
Apps.HZ_PARTY_SITES pts,
Apps.HZ_LOCATIONS ra

Where pt.party_id=ca.party_id
and fa.bill_to_customer_id=ca.cust_account_id
and casa.cust_acct_site_id=rsu.cust_acct_site_id
and pts.party_site_id=casa.party_site_id
and Rsu.site_use_id=fa.bill_to_site_use_id
and rtl.customer_trx_id = fa.customer_trx_id
and gld.customer_trx_line_id = rtl.customer_trx_line_id
and rtt.cust_trx_type_id = fa.cust_trx_type_id
and pt.party_id=pts.party_id
and pts.location_id=ra.location_id

AND rtt.type <> ‘DM’
AND fa.complete_flag = ‘Y’
AND SUBSTR(ra.postal_code, 1, 2) NOT IN (’35’,’38’)  /* PARA EL MOD 415 HAY QUE CAMBIAR NOT IN POR IN*/
AND gld.gl_date BETWEEN TO_DATE(:p_period_from,’DD-MM-YYYY’) AND TO_DATE(:p_period_to,’DD-MM-YYYY’)
AND fa.org_id = :p_org_id
AND (gld.gl_posted_date IS NOT NULL OR rtt.post_to_gl = ‘N’)

GROUP BY ca.cust_account_id, pt.PARTY_NAME, pt.jgzz_fiscal_code, DECODE(ra.country, ‘ES’, SUBSTR(ra.postal_code, 1, 2), ’99’)
ORDER BY 2, 1

=============================================================
— —
— MODELO 347 PROVEEDORES —
— —
=============================================================

 

SELECT NVL(pap.national_identifier, pv.num_1099) nif,
NVL(pv.tax_reporting_name, pv.vendor_name) nombre,
DECODE(pvs.country, ‘ES’, SUBSTR(pvs.zip, 1, 2), ’99’) codigo_provincia,

SUM(ROUND(DECODE(aid.base_amount,
0,
aid.amount,
NULL,
aid.amount,
aid.base_amount),
2)) importe_anual,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(aid.accounting_date, ‘MM’)) <= 3 THEN
ROUND(DECODE(aid.base_amount,
0,
aid.amount,
NULL,
aid.amount,
aid.base_amount),
2)
ELSE
0
END) importe_Q1,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(aid.accounting_date, ‘MM’)) BETWEEN 4 AND 6 THEN
ROUND(DECODE(aid.base_amount,
0,
aid.amount,
NULL,
aid.amount,
aid.base_amount),
2)
ELSE
0
END) importe_Q2,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(aid.accounting_date, ‘MM’)) BETWEEN 7 AND 9 THEN
ROUND(DECODE(aid.base_amount,
0,
aid.amount,
NULL,
aid.amount,
aid.base_amount),
2)
ELSE
0
END) importe_Q3,
SUM(CASE
WHEN TO_NUMBER(TO_CHAR(aid.accounting_date, ‘MM’)) >= 10 THEN
ROUND(DECODE(aid.base_amount,
0,
aid.amount,
NULL,
aid.amount,
aid.base_amount),
2)
ELSE
0
END) importe_Q4
FROM apps.ap_invoices_all ai,
apps.ap_invoice_distributions_all aid,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
(SELECT distinct papf.person_id, papf.national_identifier
FROM apps.per_all_people_f papf
WHERE trunc(sysdate) BETWEEN papf.effective_start_date AND
papf.effective_end_date) pap
WHERE 1 = 1
AND ai.org_id = :p_org_id
AND aid.accounting_date BETWEEN TO_DATE(:p_period_from,’DD-MM-YYYY’) AND TO_DATE(:p_period_to,’DD-MM-YYYY’)
AND ai.invoice_type_lookup_code <> ‘DEBIT’
AND ai.invoice_type_lookup_code <> ‘PREPAYMENT’

AND aid.invoice_id = ai.invoice_id
AND aid.posted_flag IN (‘P’, ‘Y’)
AND aid.line_type_lookup_code <> ‘AWT’
AND pv.vendor_id = ai.vendor_id
AND NVL(pv.vendor_type_lookup_code, ‘XXX’) <> ‘EMPLOYEE’
AND pvs.vendor_id = ai.vendor_id
AND pvs.org_id = ai.org_id
and SUBSTR(pvs.zip, 1, 2) not in (’35’,’38’) /* PARA EL MOD 415 CAMBIAR NOT IN POR IN*/
AND pvs.tax_reporting_site_flag = ‘Y’
AND pap.person_id(+) = NVL(pv.employee_id, -99)
GROUP BY NVL(pap.national_identifier, pv.num_1099), NVL(pv.tax_reporting_name, pv.vendor_name), DECODE(pvs.country, ‘ES’, SUBSTR(pvs.zip, 1, 2), ’99’)
ORDER BY 2, 1″

Autor: Javier Huerta