/* Formatted on 2020/07/15 00:08 (Formatter Plus v4.8.

0) */
CREATE OR REPLACE PACKAGE BODY xxvat_poso_reg_pkg_egg
AS
/* =============================================================================
* module type : pl/sql
* module name : xxvat_poso_reg_pkg.pks
* description : package is created for vat purchase and sales register
details reporting

* run env. : toad/sql*plus

* program name : xxvat sales register report

xxvat purchase register report

* procedure and function name description

----------------------------
-----------------------------------------------------------------------------------
------------------
get_registration_value function to return a
supplier/customer registration status (registered / unregistered / custom
authority)
function to return a
supplier/customer registration number and effective date

get_supply_classification function to return a supply

classification (domestic and export / import)

get_tax_classification function to return a tax

classification code from transaction or inventory

get_customer_type function to return a customer type

(internal / external)

get_vat_regime_code function to return a regime

get_vat_reg_num function to return a compony

registration number

get_customer_exemption function to return a customer type

(exempt / non-exempt)

xx_purchase_register procedure to get vat purchase

register details

sales_register procedure to get vat sales register

details

* calling module: ar , ap

* history
* =======
*
* version name date description of change
company
* ------- ------------- ---------- -----------------------
-----------
* 1.0 taufique ahmad 31-jan-2018 initial creation.
arihant
* 1.1 taufique ahmad 06-feb-2018 modification.
arihant
*
===================================================================================
==================

/*=======================
| package body
========================*/
FUNCTION get_registration_value (
p_value IN VARCHAR2,
p_party_site_id IN NUMBER
)
RETURN VARCHAR2
IS
l_reg_num VARCHAR2 (60);
l_reg_date DATE;
l_reg_status VARCHAR2 (60);
lv_party_id NUMBER;
BEGIN
BEGIN
SELECT party_id
INTO lv_party_id
FROM hz_party_sites hps
WHERE 1 = 1 AND hps.party_site_id = p_party_site_id;
EXCEPTION
WHEN OTHERS
THEN
lv_party_id := NULL;
END;

BEGIN
SELECT NVL (zxr.registration_number, 'NOT REGISTERED'),
CASE
WHEN registration_number IS NOT NULL
THEN NVL (registration_status_code, 'REGISTERED')
ELSE NVL (registration_status_code, 'NOT REGISTERED')
END,
TRUNC (effective_from)
INTO l_reg_num,
l_reg_status,
l_reg_date
FROM zx_registrations zxr
WHERE zxr.party_tax_profile_id =
(SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_party_site_id
AND party_type_code = 'THIRD_PARTY_SITE')
AND zxr.effective_to IS NULL;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
SELECT NVL (zxr.registration_number, 'NOT REGISTERED'),
CASE
WHEN registration_number IS NOT NULL
THEN NVL (registration_status_code, 'REGISTERED')
ELSE NVL (registration_status_code, 'NOT REGISTERED')
END,
TRUNC (effective_from)
 INTO l_reg_num,
l_reg_status,
l_reg_date
FROM zx_registrations zxr
WHERE zxr.party_tax_profile_id =
(SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE 1 = 1
AND party_id = lv_party_id
AND party_type_code = 'THIRD_PARTY')
AND zxr.effective_to IS NULL;
EXCEPTION
WHEN OTHERS
THEN
l_reg_num := 'NOT REGISTERED';
l_reg_status := 'NOT REGISTERED';
l_reg_date := NULL;
END;
END;

IF p_value = 'NUMBER'
THEN
RETURN l_reg_num;
ELSIF p_value = 'STATUS'
THEN
RETURN l_reg_status;
ELSIF p_value = 'DATE'
THEN
RETURN l_reg_date;
END IF;
END get_registration_value;

FUNCTION get_supply_classification (
p_party_type IN VARCHAR2,
p_supp_country VARCHAR2,
p_regime_code VARCHAR2
)
RETURN VARCHAR2
IS
l_supply_classification VARCHAR2 (50);
l_regime_country VARCHAR2 (50);
BEGIN
BEGIN
SELECT country_code
INTO l_regime_country
FROM zx_regimes_b
WHERE 1 = 1 AND tax_regime_code = p_regime_code;
EXCEPTION
WHEN OTHERS
THEN
l_regime_country := 'XY';
END;

IF l_regime_country = p_supp_country
THEN
l_supply_classification := 'DOMESTIC';
ELSIF p_party_type = 'CUSTOMER'
THEN
l_supply_classification := 'EXPORT';
 ELSIF p_party_type = 'SUPPLIER'
THEN
l_supply_classification := 'IMPORT';
END IF;

RETURN l_supply_classification;
END get_supply_classification;

FUNCTION get_tax_classification (
p_tax_type IN VARCHAR2,
p_trx_id IN NUMBER,
p_trx_line_id IN NUMBER
)
RETURN VARCHAR2
AS
l_tax_code VARCHAR2 (60);
BEGIN
IF p_tax_type = 'PURCHASE'
THEN
BEGIN
SELECT NVL (product_fisc_classification,
NVL (tax_classification_code, 'NA')
)
INTO l_tax_code
FROM ap_invoice_lines_all
WHERE 1 = 1
AND invoice_id = p_trx_id
AND line_number = p_trx_line_id;
EXCEPTION
WHEN OTHERS
THEN
l_tax_code := 'NA';
END;

IF l_tax_code = 'NA'
THEN
BEGIN
SELECT concatenated_segments
INTO l_tax_code
FROM ap_invoice_lines_all aila,
mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories_kfv mc
WHERE 1 = 1
AND aila.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.structure_id = mc.structure_id
AND mic.category_id = mc.category_id
AND invoice_id = p_trx_id
AND line_number = p_trx_line_id
AND category_set_name IN ('SA_VAT_CATEGORY_SET')
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_tax_code := NULL;
END;
END IF;
ELSIF p_tax_type = 'SALES'
 THEN
BEGIN
SELECT NVL (tax_classification_code, 'NA')
INTO l_tax_code
FROM ra_customer_trx_lines_all
WHERE 1 = 1
AND customer_trx_id = p_trx_id
AND customer_trx_line_id = p_trx_line_id;
EXCEPTION
WHEN OTHERS
THEN
l_tax_code := 'NA';
END;

IF l_tax_code = 'NA'
THEN
BEGIN
SELECT concatenated_segments
INTO l_tax_code
FROM ra_customer_trx_lines_all rctla,
mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories_kfv mc
WHERE 1 = 1
AND rctla.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.structure_id = mc.structure_id
AND mic.category_id = mc.category_id
AND customer_trx_id = p_trx_id
AND customer_trx_line_id = p_trx_line_id
AND category_set_name IN ('SA_VAT_CATEGORY_SET')
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
l_tax_code := NULL;
END;
END IF;
END IF;

RETURN l_tax_code;
END get_tax_classification;

FUNCTION get_customer_type (p_bill_to_customer_id NUMBER)

RETURN VARCHAR2
IS
l_customer_type VARCHAR2 (50);
l_detail_type VARCHAR2 (50);
---l_registration_country varchar2(50);
BEGIN
BEGIN
SELECT ship_cust_acct.customer_type
INTO l_customer_type
FROM hz_cust_accounts ship_cust_acct
WHERE ship_cust_acct.cust_account_id = p_bill_to_customer_id;
--8231
EXCEPTION
WHEN OTHERS
THEN
 l_customer_type := NULL;
END;

IF l_customer_type = 'I'
THEN
l_detail_type := 'Internal';
ELSE
l_detail_type := 'External';
END IF;

RETURN l_detail_type;
EXCEPTION
WHEN OTHERS
THEN
RETURN l_detail_type;
END get_customer_type;

FUNCTION get_vat_regime_code (p_le_id NUMBER)

RETURN VARCHAR2
IS
l_vat_regime_code zx_regimes_b.tax_regime_code%TYPE;
BEGIN
BEGIN
SELECT tax_regime_code
INTO l_vat_regime_code
FROM xle_etb_profiles xep,
zx_party_tax_profile zptp,
zx_registrations zr
WHERE 1 = 1
AND xep.party_id = zptp.party_id
AND zptp.party_tax_profile_id = zr.party_tax_profile_id
AND zr.effective_to IS NULL
AND xep.effective_to IS NULL
AND xep.legal_entity_id = p_le_id;
EXCEPTION
WHEN OTHERS
THEN
l_vat_regime_code := 'NA';
END;

RETURN l_vat_regime_code;
END get_vat_regime_code;

FUNCTION get_vat_reg_num (p_le_id IN NUMBER, p_regime_code IN VARCHAR2)

RETURN VARCHAR2
IS
l_vat_reg_num VARCHAR2 (60);
BEGIN
BEGIN
SELECT registration_number
INTO l_vat_reg_num
FROM xle_etb_profiles xep,
zx_party_tax_profile zptp,
zx_registrations zr
WHERE 1 = 1
AND xep.party_id = zptp.party_id
AND zptp.party_tax_profile_id = zr.party_tax_profile_id
AND zr.effective_to IS NULL
AND xep.effective_to IS NULL
 AND zr.tax_regime_code = p_regime_code
AND xep.legal_entity_id = p_le_id;
EXCEPTION
WHEN OTHERS
THEN
l_vat_reg_num := 'NA';
END;

RETURN l_vat_reg_num;
END get_vat_reg_num;

FUNCTION get_customer_exemption (p_party_type IN VARCHAR2, p_party_id NUMBER)

RETURN VARCHAR2
IS
l_party_tax_profile_id VARCHAR2 (50);
l_exemption_status VARCHAR2 (50);
BEGIN
IF p_party_type = 'PARTY'
THEN
BEGIN
SELECT zxv.party_tax_profile_id
INTO l_party_tax_profile_id
FROM zx_party_tax_profile zxp, zx_mco_eo_exemptions_v zxv
WHERE zxp.party_tax_profile_id = zxv.party_tax_profile_id
AND zxp.party_type_code = 'THIRD_PARTY'
AND zxp.party_id = p_party_id;
EXCEPTION
WHEN OTHERS
THEN
l_party_tax_profile_id := NULL;
END;
ELSIF p_party_type = 'SITE'
THEN
BEGIN
SELECT zxv.party_tax_profile_id
INTO l_party_tax_profile_id
FROM zx_party_tax_profile zxp, zx_mco_eo_exemptions_v zxv
WHERE zxp.party_tax_profile_id = zxv.party_tax_profile_id
AND zxp.party_type_code = 'THIRD_PARTY_SITE'
AND zxp.party_id = p_party_id;
EXCEPTION
WHEN OTHERS
THEN
l_party_tax_profile_id := NULL;
END;
END IF;

IF l_party_tax_profile_id IS NOT NULL

THEN
l_exemption_status := 'Exempt';
ELSE
l_exemption_status := 'Non Exempt';
END IF;

RETURN l_exemption_status;
END get_customer_exemption;

FUNCTION get_ship_to_country (
p_site_use_id IN NUMBER,
 p_customer_id IN NUMBER
)
RETURN VARCHAR2
AS
lv_ship_to_country VARCHAR2 (20);
BEGIN
BEGIN
SELECT country
INTO lv_ship_to_country
FROM hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE 1 = 1
AND site_use_code = 'SHIP_TO'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcasa.cust_account_id = p_customer_id
AND hcsua.site_use_id = p_site_use_id;
EXCEPTION
WHEN OTHERS
THEN
lv_ship_to_country := NULL;
END;

RETURN lv_ship_to_country;
END get_ship_to_country;

PROCEDURE xx_purchase_register (
p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER,
p_tax_regime_code IN VARCHAR2,
p_vat_reg_num IN VARCHAR2,
p_org_id IN NUMBER,
p_supplier_vatin IN VARCHAR2,
p_fr_invoice_date IN VARCHAR2,
p_to_invoice_date IN VARCHAR2,
p_fr_creation_date IN VARCHAR2,
p_to_creation_date IN VARCHAR2,
p_fr_gl_date IN VARCHAR2,
p_to_gl_date IN VARCHAR2
)
AS
lv_fr_invoice_date DATE
:= TO_DATE (SUBSTR (p_fr_invoice_date, 1, 10), 'yyyy/mm/dd');
lv_to_invoice_date DATE
:= TO_DATE (SUBSTR (p_to_invoice_date, 1, 10), 'yyyy/mm/dd');
lv_fr_creation_date DATE
:= TO_DATE (SUBSTR (p_fr_creation_date, 1, 10), 'yyyy/mm/dd');
lv_to_creation_date DATE
:= TO_DATE (SUBSTR (p_to_creation_date, 1, 10), 'yyyy/mm/dd');
lv_fr_gl_date DATE
:= TO_DATE (SUBSTR (p_fr_gl_date, 1, 10), 'yyyy/mm/dd');
lv_to_gl_date DATE
:= TO_DATE (SUBSTR (p_to_gl_date, 1, 10), 'yyyy/mm/dd');

CURSOR po_reg_cur
IS
SELECT aia.org_id, aia.legal_entity_id, hou.NAME operating_unit,
as1.segment1 vendor_number, as1.vendor_name,
assa.vendor_site_code, country,
aia.doc_sequence_value voucher_number,
ftt.territory_short_name supplier_site_contry, as1.party_id,
assa.party_site_id, vendor_type_lookup_code,
DECODE (vendor_type_lookup_code,
'INTERNAL', 'Internal',
'External'
) supplier_type,
CASE
WHEN assa.country IN (
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'GCC_VAT'
AND end_date_active IS NULL)
THEN 'GCC'
ELSE 'NON-GCC'
END gcc_country,
xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
) supplier_vatin,
CASE
WHEN aia.invoice_type_lookup_code =
'EXPENSE REPORT'
THEN 'REGISTERED'
ELSE xxvat_poso_reg_pkg.get_registration_value
('STATUS',
assa.party_site_id
)
END supplier_reg_status,
xxvat_poso_reg_pkg.get_registration_value
('DATE',
assa.party_site_id
) supplier_reg_date,
CASE
WHEN aia.invoice_type_lookup_code =
'EXPENSE REPORT'
THEN 'DOMESTIC'
ELSE xxvat_poso_reg_pkg.get_supply_classification
('SUPPLIER',
assa.country,
p_tax_regime_code
)
END supp_class,
aia.invoice_type_lookup_code invoice_type, aia.invoice_id,
aia.invoice_num, TRUNC (aia.invoice_date) invoice_date,
TRUNC (aia.gl_date) gl_date, aia.invoice_currency_code,
aila.line_number,
(SELECT poh.segment1
FROM po_headers_all poh
WHERE po_header_id = aila.po_header_id
AND org_id = aila.org_id) po_number,
(SELECT pol.line_num
FROM po_lines_all pol
WHERE po_header_id = aila.po_header_id
AND po_line_id = aila.po_line_id
AND org_id = aila.org_id) po_line_num,
aila.line_type_lookup_code line_type,
regexp_replace (aila.description,
'[[:space:]]+',
CHR (32)
) line_description,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id =
aila.inventory_item_id
AND ROWNUM = 1) item_number,
CASE
WHEN aia.invoice_type_lookup_code =
'EXPENSE REPORT'
THEN ((aila.amount) - (NVL (tax_amt, 0))
)
ELSE (aila.amount)
END invoice_line_amt,
CASE
WHEN aia.invoice_type_lookup_code =
'EXPENSE REPORT'
THEN ROUND
(( ((aila.amount) - (NVL (tax_amt_tax_curr, 0))
)
* NVL (aia.exchange_rate, 1)
),
2
)
ELSE ROUND ((aila.amount * NVL (aia.exchange_rate, 1)), 2)
END invoice_line_func_amt,
NVL (aila.assessable_value, 0) assessable_value,
NVL (aia.exchange_rate, 1) exchange_rate, tax.tax_rate_code,
(SELECT concatenated_segments
FROM ap_invoice_distributions_all aida,
gl_code_combinations_kfv gcc
WHERE 1 = 1
AND aida.invoice_id = aila.invoice_id
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND summary_tax_line_id = tax.summary_tax_line_id
AND NVL (reversal_flag, 'N') = 'N'
AND line_type_lookup_code = 'REC_TAX'
AND ROWNUM = 1) recovery_acct_code,
tax.tax_rate, NVL (tax_amt_tax_curr, 0) tax_amt_tax_curr,
NVL (tax.tax_amt, 0) tax_amt,
(SELECT DECODE (allow_recoverability_flag,
'Y', 'Yes',
'N', 'No'
)
FROM zx_rates_b zrb, zx_taxes_b ztb
WHERE tax_rate_id = tax.tax_rate_id
AND zrb.tax = ztb.tax
AND ztb.effective_to IS NULL
AND zrb.effective_to IS NULL
AND ztb.tax_regime_code = tax.tax_regime_code)
vat_recoverable,
aia.invoice_amount,
INITCAP
(ap_invoices_pkg.get_approval_status
(aia.invoice_id,
 aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
)
) invoice_status,
DECODE ((SELECT offset_flag
FROM zx_lines
WHERE trx_id = aia.invoice_id
AND offset_link_to_tax_line_id = tax.tax_line_id
AND entity_code = 'AP_INVOICES'),
'Y', 'Yes',
'No'
) offset_flag,
(SELECT tax_amt_tax_curr
FROM zx_lines
WHERE trx_id = aia.invoice_id
AND offset_link_to_tax_line_id = tax.tax_line_id
AND entity_code = 'AP_INVOICES') vat_rcm_amt_fun,
xxvat_poso_reg_pkg.get_tax_classification
('PURCHASE',
aila.invoice_id,
aila.line_number
) tax_classification_code,
NULL journal_name, NULL je_category, 0 distribution_amount
FROM ap_invoices_all aia,
ap_invoice_lines_all aila,
hr_operating_units hou,
ap_suppliers as1,
ap_supplier_sites_all assa,
fnd_territories_tl ftt,
(SELECT trx_id, trx_line_id, tax_line_id, tax_amt,
tax_amt_tax_curr, tax_amt_funcl_curr, taxable_amt,
tax_rate_id, tax_rate_code, tax_regime_code, tax_rate,
offset_flag, process_for_recovery_flag,
summary_tax_line_id
FROM zx_lines a
WHERE 1 = 1
AND entity_code = 'AP_INVOICES'
AND NVL (cancel_flag, 'N') = 'N'
AND offset_link_to_tax_line_id IS NULL) tax
WHERE 1 = 1
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = hou.organization_id
AND aia.vendor_id = as1.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND assa.country = ftt.territory_code(+)
AND aila.invoice_id = tax.trx_id(+)
AND aila.line_number = tax.trx_line_id(+)
AND line_type_lookup_code IN ('ITEM')
AND NVL (aila.discarded_flag, 'N') != 'Y'
AND ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) <> 'CANCELLED'
AND p_tax_regime_code IN (
SELECT tax_regime_code
FROM zx_regimes_b zrb,
 hr_locations hl,
hr_all_organization_units hou
WHERE 1 = 1
AND zrb.country_code = hl.country
AND hl.location_id = hou.location_id
AND effective_to IS NULL
AND organization_id = aia.org_id)
AND aia.org_id = NVL (p_org_id, aia.org_id)
AND NVL (xxvat_poso_reg_pkg.get_vat_reg_num (aia.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
) =
NVL
(p_vat_reg_num,
NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(aia.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
)
)
AND NVL
(xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
),
'VATREG123456'
) =
NVL
(p_supplier_vatin,
NVL
(xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
),
'VATREG123456'
)
)
AND EXISTS (
SELECT 1
FROM ap_invoice_distributions_all aida --,
--xla_ae_headers xah
WHERE invoice_id = aila.invoice_id
AND invoice_line_number = aila.line_number
-- AND aida.accounting_event_id =
xah.event_id
--
AND aia.set_of_books_id = xah.ledger_id
--
AND xah.application_id = 200
--
AND NVL (gl_transfer_status_code, 'N') = 'Y'
AND NVL (match_status_flag, 'N') = 'A'
AND TRUNC (aida.accounting_date)
BETWEEN NVL (lv_fr_gl_date, aida.accounting_date)
AND NVL (lv_to_gl_date, aida.accounting_date))
AND ftt.LANGUAGE(+) = USERENV ('LANG')
 AND TRUNC (aia.invoice_date) BETWEEN NVL (lv_fr_invoice_date,
aia.invoice_date
)
AND NVL (lv_to_invoice_date,
aia.invoice_date
)
AND TRUNC (aia.creation_date) BETWEEN NVL
(lv_fr_creation_date,
TRUNC (aia.creation_date)
)
AND NVL
(lv_to_creation_date,
TRUNC (aia.creation_date)
)
UNION ALL
SELECT aia.org_id, aia.legal_entity_id, hou.NAME operating_unit,
as1.segment1 vendor_number, as1.vendor_name,
assa.vendor_site_code, country,
aia.doc_sequence_value voucher_number,
ftt.territory_short_name supplier_site_contry, as1.party_id,
assa.party_site_id, vendor_type_lookup_code,
DECODE (vendor_type_lookup_code,
'INTERNAL', 'Internal',
'External'
) supplier_type,
CASE
WHEN assa.country IN (
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'GCC_VAT'
AND end_date_active IS NULL)
THEN 'GCC'
ELSE 'NON-GCC'
END gcc_country,
xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
) supplier_vatin,
xxvat_poso_reg_pkg.get_registration_value
('STATUS',
assa.party_site_id
) supplier_reg_status,
xxvat_poso_reg_pkg.get_registration_value
('DATE',
assa.party_site_id
) supplier_reg_date,
xxvat_poso_reg_pkg.get_supply_classification
('SUPPLIER',
assa.country,
p_tax_regime_code
) supp_class,
'PREPAYMENT APPLY' invoice_type, aia.invoice_id,
aia.invoice_num,
(SELECT TRUNC (MAX (last_update_date))
FROM ap_prepay_history_all
WHERE prepay_invoice_id =
aila.prepay_invoice_id
AND prepay_line_num = aila.prepay_line_number
AND transaction_type = 'PREPAYMENT APPLIED')
 invoice_appl_date,
TRUNC (aia1.gl_date) gl_date, aia.invoice_currency_code,
aila.prepay_line_number,
(SELECT poh.segment1
FROM po_headers_all poh
WHERE po_header_id = aila.po_header_id
AND org_id = aila.org_id) po_number,
(SELECT pol.line_num
FROM po_lines_all pol
WHERE po_header_id = aila.po_header_id
AND po_line_id = aila.po_line_id
AND org_id = aila.org_id) po_line_num,
aila.line_type_lookup_code line_type,
regexp_replace (aila.description,
'[[:space:]]+',
CHR (32)
) line_description,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id =
aila.inventory_item_id
AND ROWNUM = 1) item_number,
aila.amount invoice_line_amt,
ROUND ((aila.amount * NVL (aia.exchange_rate, 1)),
2
) invoice_line_func_amt,
NVL (aila.assessable_value, 0) assessable_value,
NVL (aia.exchange_rate, 1) exchange_rate, tax.tax_rate_code,
(SELECT concatenated_segments
FROM ap_invoice_distributions_all aida,
gl_code_combinations_kfv gcc
WHERE 1 = 1
AND aida.invoice_id = aila.invoice_id
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND summary_tax_line_id = tax.summary_tax_line_id
AND NVL (reversal_flag, 'N') = 'N'
AND line_type_lookup_code = 'REC_TAX'
AND ROWNUM = 1) recovery_acct_code,
tax.tax_rate, NVL (tax_amt_tax_curr, 0) tax_amt_tax_curr,
NVL (tax_amt, 0) tax_amt,
(SELECT DECODE (allow_recoverability_flag,
'Y', 'Yes',
'N', 'No'
)
FROM zx_rates_b zrb, zx_taxes_b ztb
WHERE tax_rate_id = tax.tax_rate_id
AND zrb.tax = ztb.tax
AND ztb.effective_to IS NULL
AND zrb.effective_to IS NULL
AND ztb.tax_regime_code = tax.tax_regime_code)
vat_recoverable,
-1 * aia.invoice_amount invoice_amount,
INITCAP
(ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
 )
) invoice_status,
DECODE ((SELECT offset_flag
FROM zx_lines
WHERE trx_id = aia.invoice_id
AND offset_link_to_tax_line_id = tax.tax_line_id
AND entity_code = 'AP_INVOICES'),
'Y', 'Yes',
'No'
) offset_flag,
(SELECT tax_amt_tax_curr
FROM zx_lines
WHERE trx_id = aia.invoice_id
AND offset_link_to_tax_line_id = tax.tax_line_id
AND entity_code = 'AP_INVOICES') vat_rcm_amt_fun,
xxvat_poso_reg_pkg.get_tax_classification
('PURCHASE',
aila.invoice_id,
aila.line_number
) tax_classification_code,
NULL journal_name, NULL je_category, 0 distribution_amount
FROM ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoices_all aia1,
hr_operating_units hou,
ap_suppliers as1,
ap_supplier_sites_all assa,
fnd_territories_tl ftt,
(SELECT trx_id, trx_line_id, tax_line_id, tax_amt,
tax_amt_tax_curr, tax_amt_funcl_curr, taxable_amt,
tax_rate_id, tax_rate_code, tax_regime_code, tax_rate,
offset_flag, process_for_recovery_flag,
summary_tax_line_id
FROM zx_lines a
WHERE 1 = 1
AND entity_code = 'AP_INVOICES'
AND NVL (cancel_flag, 'N') = 'N'
AND offset_link_to_tax_line_id IS NULL) tax
WHERE 1 = 1
AND aia.invoice_id = aila.prepay_invoice_id
AND aila.invoice_id = aia1.invoice_id
AND aia.org_id = hou.organization_id
AND aia.vendor_id = as1.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND assa.country = ftt.territory_code(+)
AND aila.invoice_id = tax.trx_id(+)
AND aila.line_number = tax.trx_line_id(+)
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND line_type_lookup_code IN ('PREPAY')
AND NVL (aila.discarded_flag, 'N') != 'Y'
AND ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) <> 'CANCELLED'
AND p_tax_regime_code IN (
SELECT tax_regime_code
FROM zx_regimes_b zrb,
 hr_locations hl,
hr_all_organization_units hou
WHERE 1 = 1
AND zrb.country_code = hl.country
AND hl.location_id = hou.location_id
AND effective_to IS NULL
AND organization_id = aia.org_id)
AND aia.org_id = NVL (p_org_id, aia.org_id)
AND NVL (xxvat_poso_reg_pkg.get_vat_reg_num (aia.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
) =
NVL
(p_vat_reg_num,
NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(aia.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
)
)
AND NVL
(xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
),
'VATREG123456'
) =
NVL
(p_supplier_vatin,
NVL
(xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
),
'VATREG123456'
)
)
AND EXISTS (
SELECT 1
FROM ap_invoice_distributions_all aida --,
--xla_ae_headers xah
WHERE invoice_id = aila.invoice_id
AND invoice_line_number = aila.line_number
-- AND aida.accounting_event_id =
xah.event_id
-- AND aia.set_of_books_id =
xah.ledger_id
-- AND xah.application_id =
200
-- AND NVL
(gl_transfer_status_code, 'N') = 'Y'
AND NVL (match_status_flag, 'N') = 'A'
AND TRUNC (aida.accounting_date)
BETWEEN NVL (lv_fr_gl_date, aida.accounting_date)
AND NVL (lv_to_gl_date, aida.accounting_date))
AND ftt.LANGUAGE(+) = USERENV ('LANG')
 AND TRUNC (aia1.invoice_date) BETWEEN NVL (lv_fr_invoice_date,
aia1.invoice_date
)
AND NVL (lv_to_invoice_date,
aia1.invoice_date
)
AND TRUNC (aia1.creation_date)
BETWEEN NVL (lv_fr_creation_date,
TRUNC (aia1.creation_date)
)
AND NVL (lv_to_creation_date,
TRUNC (aia1.creation_date)
)
UNION ALL
SELECT aia.org_id, aia.legal_entity_id, hou.NAME operating_unit,
as1.segment1 vendor_number, as1.vendor_name,
assa.vendor_site_code, country,
aia.doc_sequence_value voucher_number,
ftt.territory_short_name supplier_site_contry, as1.party_id,
assa.party_site_id, vendor_type_lookup_code,
DECODE (vendor_type_lookup_code,
'INTERNAL', 'Internal',
'External'
) supplier_type,
CASE
WHEN assa.country IN (
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'GCC_VAT'
AND end_date_active IS NULL)
THEN 'GCC'
ELSE 'NON-GCC'
END gcc_country,
xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
) supplier_vatin,
CASE
WHEN aia.invoice_type_lookup_code =
'EXPENSE REPORT'
THEN 'REGISTERED'
ELSE xxvat_poso_reg_pkg.get_registration_value
('STATUS',
assa.party_site_id
)
END supplier_reg_status,
xxvat_poso_reg_pkg.get_registration_value
('DATE',
assa.party_site_id
) supplier_reg_date,
CASE
WHEN aia.invoice_type_lookup_code =
'EXPENSE REPORT'
THEN 'DOMESTIC'
ELSE xxvat_poso_reg_pkg.get_supply_classification
('SUPPLIER',
assa.country,
p_tax_regime_code
)
END supp_class,
aia.invoice_type_lookup_code invoice_type, aia.invoice_id,
aia.invoice_num, TRUNC (aia.invoice_date) invoice_date,
TRUNC (aida.accounting_date) gl_date,
aia.invoice_currency_code, aila.line_number,
(SELECT poh.segment1
FROM po_headers_all poh
WHERE po_header_id = aila.po_header_id
AND org_id = aila.org_id) po_number,
(SELECT pol.line_num
FROM po_lines_all pol
WHERE po_header_id = aila.po_header_id
AND po_line_id = aila.po_line_id
AND org_id = aila.org_id) po_line_num,
aila.line_type_lookup_code line_type,
regexp_replace (aila.description,
'[[:space:]]+',
CHR (32)
) line_description,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id =
aila.inventory_item_id
AND ROWNUM = 1) item_number,
aida.amount invoice_line_amt,
NVL (aida.amount, 0)
* NVL (aia.exchange_rate, 1) invoice_line_func_amt,
NVL (aila.assessable_value, 0) assessable_value,
NVL (aia.exchange_rate, 1) exchange_rate, tax.tax_rate_code,
(SELECT concatenated_segments
FROM ap_invoice_distributions_all aida,
gl_code_combinations_kfv gcc
WHERE 1 = 1
AND aida.invoice_id = aila.invoice_id
AND aida.dist_code_combination_id =
gcc.code_combination_id
AND summary_tax_line_id = tax.summary_tax_line_id
AND NVL (reversal_flag, 'N') = 'N'
AND line_type_lookup_code = 'REC_TAX'
AND ROWNUM = 1) recovery_acct_code,
tax.tax_rate,
(SELECT tax_aida.amount
FROM ap_invoice_distributions_all tax_aida
WHERE tax_aida.charge_applicable_to_dist_id =
aida.invoice_distribution_id
AND tax_aida.line_type_lookup_code = 'REC_TAX')
tax_amt_tax_curr,
NVL
( (SELECT NVL (tax_aida.amount, 0)
FROM ap_invoice_distributions_all tax_aida
WHERE tax_aida.charge_applicable_to_dist_id =
aida.invoice_distribution_id
AND tax_aida.line_type_lookup_code = 'REC_TAX')
* NVL (aia.exchange_rate, 1),
0
) tax_amt,
(SELECT DECODE (allow_recoverability_flag,
'Y', 'Yes',
'N', 'No'
 )
FROM zx_rates_b zrb, zx_taxes_b ztb
WHERE tax_rate_id = tax.tax_rate_id
AND zrb.tax = ztb.tax
AND ztb.effective_to IS NULL
AND zrb.effective_to IS NULL
AND ztb.tax_regime_code = tax.tax_regime_code)
vat_recoverable,
aia.invoice_amount,
INITCAP
(ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
)
) invoice_status,
DECODE ((SELECT offset_flag
FROM zx_lines
WHERE trx_id = aia.invoice_id
AND offset_link_to_tax_line_id = tax.tax_line_id
AND entity_code = 'AP_INVOICES'),
'Y', 'Yes',
'No'
) offset_flag,
(SELECT tax_amt_tax_curr
FROM zx_lines
WHERE trx_id = aia.invoice_id
AND offset_link_to_tax_line_id = tax.tax_line_id
AND entity_code = 'AP_INVOICES') vat_rcm_amt_fun,
xxvat_poso_reg_pkg.get_tax_classification
('PURCHASE',
aila.invoice_id,
aila.line_number
) tax_classification_code,
NULL journal_name, NULL je_category, 0 distribution_amount
FROM ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
hr_operating_units hou,
ap_suppliers as1,
ap_supplier_sites_all assa,
fnd_territories_tl ftt,
(SELECT trx_id, trx_line_id, tax_line_id, tax_amt,
tax_amt_tax_curr, tax_amt_funcl_curr, taxable_amt,
tax_rate_id, tax_rate_code, tax_regime_code, tax_rate,
offset_flag, process_for_recovery_flag,
summary_tax_line_id
FROM zx_lines a
WHERE 1 = 1
AND entity_code = 'AP_INVOICES'
AND NVL (cancel_flag, 'N') = 'N'
AND offset_link_to_tax_line_id IS NULL) tax
WHERE aia.invoice_id = aila.invoice_id
AND aia.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aia.org_id = hou.organization_id
AND aia.vendor_id = as1.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND assa.country = ftt.territory_code(+)
AND ftt.LANGUAGE(+) = USERENV ('LANG')
AND aida.invoice_id = tax.trx_id(+)
AND aida.invoice_line_number = tax.trx_line_id(+)
AND aida.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) = 'CANCELLED'
AND aida.cancellation_flag = 'Y'
AND p_tax_regime_code IN (
SELECT tax_regime_code
FROM zx_regimes_b zrb,
hr_locations hl,
hr_all_organization_units hou
WHERE 1 = 1
AND zrb.country_code = hl.country
AND hl.location_id = hou.location_id
AND effective_to IS NULL
AND organization_id = aia.org_id)
AND aia.org_id = NVL (p_org_id, aia.org_id)
AND NVL (xxvat_poso_reg_pkg.get_vat_reg_num (aia.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
) =
NVL
(p_vat_reg_num,
NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(aia.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
)
)
AND NVL
(xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
),
'VATREG123456'
) =
NVL
(p_supplier_vatin,
NVL
(xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
assa.party_site_id
),
'VATREG123456'
)
)
AND EXISTS (
SELECT 1
FROM ap_invoice_distributions_all aida --,
--xla_ae_headers xah
 WHERE invoice_id = aila.invoice_id
AND invoice_line_number = aila.line_number
-- AND aida.accounting_event_id =
xah.event_id
--
AND aia.set_of_books_id = xah.ledger_id
--
AND xah.application_id = 200
--
AND NVL (gl_transfer_status_code, 'N') = 'Y'
AND NVL (match_status_flag, 'N') = 'A'
AND TRUNC (aida.accounting_date)
BETWEEN NVL (lv_fr_gl_date, aida.accounting_date)
AND NVL (lv_to_gl_date, aida.accounting_date))
AND ftt.LANGUAGE(+) = USERENV ('LANG')
AND TRUNC (aia.invoice_date) BETWEEN NVL (lv_fr_invoice_date,
aia.invoice_date
)
AND NVL (lv_to_invoice_date,
aia.invoice_date
)
AND TRUNC (aia.creation_date) BETWEEN NVL
(lv_fr_creation_date,
TRUNC (aia.creation_date)
)
AND NVL
(lv_to_creation_date,
TRUNC (aia.creation_date)
)
UNION ALL
SELECT aia1.org_id, aia1.legal_entity_id, hou.NAME operating_unit,
TO_CHAR(0) vendor_number, TO_CHAR(gjl.attribute6) vendor_name,
TO_CHAR(0) vendor_site_code, TO_CHAR(0) country, 0
voucher_number,
TO_CHAR(0) supplier_site_contry, 0 party_id, 0 party_site_id,
TO_CHAR(0) vendor_type_lookup_code, TO_CHAR(0) supplier_type,
TO_CHAR(0) gcc_country, TO_CHAR(gjl.attribute7) supplier_vatin,
TO_CHAR(0) supplier_reg_status, TO_DATE(sysdate)
supplier_reg_date,
TO_CHAR(0) supp_class, TO_CHAR(0) invoice_type, 0 invoice_id,
TO_CHAR(gjl.attribute5) invoice_num,
TRUNC (gjh.default_effective_date) invoice_date,
TRUNC (gjh.default_effective_date) gl_date,
TO_CHAR(0) invoice_currency_code, 0 line_number, TO_CHAR(0)
po_number,
0 po_line_num, TO_CHAR(0) line_type,
regexp_replace (gjl.description,
'[[:space:]]+',
CHR (32)
) line_description,
TO_CHAR(0) item_number, 0 invoice_line_amt, 0
invoice_line_func_amt,
0 assessable_value, 0 exchange_rate, to_char(0) tax_rate_code,
to_char(0 ) recovery_acct_code, to_number(gjl.attribute8) tax_rate,
0 tax_amt_tax_curr, NVL (gjl.accounted_dr, 0) tax_amt,
0 vat_recoverable, 0 invoice_amount,
TO_CHAR(0) invoice_status, TO_CHAR(0) offset_flag, 0
vat_rcm_amt_fun,
TO_CHAR(0) tax_classification_code, gjh.NAME journal_name,
 gjc.user_je_category_name je_category,
NVL (gjl.attribute4, 0) distribution_amount
FROM gl_je_headers gjh,
gl_je_lines gjl,
ap_invoices_all aia1,
gl_code_combinations gcc,
hr_operating_units hou,
gl_je_categories gjc
WHERE 1 = 1
--and gjh.name = 'Test Bank Charges'
AND gjh.je_header_id = gjl.je_header_id
AND gjl.attribute5 = aia1.invoice_num
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment3 = '1100304'
AND gjh.je_category = gjc.je_category_name
AND gjh.je_category = 42
AND aia1.org_id = hou.organization_id
AND TRUNC (gjh.default_effective_date)
BETWEEN NVL (lv_fr_gl_date, gjh.default_effective_date)
AND NVL (lv_to_gl_date, gjh.default_effective_date)
;

po_reg_rec po_reg_cur%ROWTYPE;
lv_org_name hr_operating_units.NAME%TYPE;
lv_tot_vat_amount NUMBER;
BEGIN
BEGIN
fnd_file.put_line (fnd_file.LOG,
'From Date:'
|| p_fr_creation_date
|| '-'
|| 'To Date:'
|| p_to_creation_date
);
fnd_file.put_line
(fnd_file.output,
'<?xml version="1.0" encoding="US-ASCII" standalone="no"?>'
);
fnd_file.put_line (fnd_file.output, '<PO_OUTPUT>');

/************************************parameters xml
tag*******************************/
BEGIN
SELECT NAME
INTO lv_org_name
FROM hr_operating_units
WHERE 1 = 1 AND organization_id = p_org_id;
EXCEPTION
WHEN OTHERS
THEN
lv_org_name := NULL;
END;

fnd_file.put_line (fnd_file.output,
'<P_TAX_REGIME_CODE>'
|| DBMS_XMLGEN.CONVERT (p_tax_regime_code)
|| '</P_TAX_REGIME_CODE>'
);
fnd_file.put_line (fnd_file.output,
 '<P_VAT_REG_NUM>'
|| DBMS_XMLGEN.CONVERT (p_vat_reg_num)
|| '</P_VAT_REG_NUM>'
);
fnd_file.put_line (fnd_file.output,
'<P_SUPPLIER_VATIN>'
|| DBMS_XMLGEN.CONVERT (p_supplier_vatin)
|| '</P_SUPPLIER_VATIN>'
);
fnd_file.put_line (fnd_file.output,
'<P_FR_INVOICE_DATE>'
|| DBMS_XMLGEN.CONVERT (p_fr_invoice_date)
|| '</P_FR_INVOICE_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_TO_INVOICE_DATE>'
|| DBMS_XMLGEN.CONVERT (p_to_invoice_date)
|| '</P_TO_INVOICE_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_FR_CREATION_DATE>'
|| DBMS_XMLGEN.CONVERT (p_fr_creation_date)
|| '</P_FR_CREATION_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_TO_CREATION_DATE>'
|| DBMS_XMLGEN.CONVERT (p_to_creation_date)
|| '</P_TO_CREATION_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_FR_GL_DATE>'
|| DBMS_XMLGEN.CONVERT (p_fr_gl_date)
|| '</P_FR_GL_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_TO_GL_DATE>'
|| DBMS_XMLGEN.CONVERT (p_to_gl_date)
|| '</P_TO_GL_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<LV_ORG_NAME>'
|| DBMS_XMLGEN.CONVERT (lv_org_name)
|| '</LV_ORG_NAME>'
);

OPEN po_reg_cur;

lv_tot_vat_amount := 0;

LOOP
--
FETCH po_reg_cur
INTO po_reg_rec;

EXIT WHEN po_reg_cur%NOTFOUND;

fnd_file.put_line (fnd_file.output, '<PO_ROW>');
fnd_file.put_line
(fnd_file.output,
'<OPERATING_UNIT>'
 || DBMS_XMLGEN.CONVERT (po_reg_rec.operating_unit)
|| '</OPERATING_UNIT>'
);
fnd_file.put_line (fnd_file.output,
'<VENDOR_NUMBER>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.vendor_number)
|| '</VENDOR_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<VENDOR_NAME>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.vendor_name)
|| '</VENDOR_NAME>'
);
fnd_file.put_line
(fnd_file.output,
'<VENDOR_SITE_CODE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.vendor_site_code)
|| '</VENDOR_SITE_CODE>'
);
fnd_file.put_line (fnd_file.output,
'<COUNTRY>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.country)
|| '</COUNTRY>'
);
fnd_file.put_line
(fnd_file.output,
'<VOUCHER_NUMBER>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.voucher_number)
|| '</VOUCHER_NUMBER>'
);
fnd_file.put_line
(fnd_file.output,
'<SUPPLIER_SITE_CONTRY>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.supplier_site_contry)
|| '</SUPPLIER_SITE_CONTRY>'
);
fnd_file.put_line
(fnd_file.output,
'<VENDOR_TYPE_LOOKUP_CODE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.vendor_type_lookup_code)
|| '</VENDOR_TYPE_LOOKUP_CODE>'
);
fnd_file.put_line (fnd_file.output,
'<SUPPLIER_TYPE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.supplier_type)
|| '</SUPPLIER_TYPE>'
);
fnd_file.put_line (fnd_file.output,
'<GCC_COUNTRY>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.gcc_country)
|| '</GCC_COUNTRY>'
);
fnd_file.put_line
 (fnd_file.output,
'<SUPPLIER_VATIN>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.supplier_vatin)
|| '</SUPPLIER_VATIN>'
);
fnd_file.put_line
(fnd_file.output,
'<SUPPLIER_REG_STATUS>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.supplier_reg_status)
|| '</SUPPLIER_REG_STATUS>'
);
fnd_file.put_line
(fnd_file.output,
'<SUPPLIER_REG_DATE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.supplier_reg_date)
|| '</SUPPLIER_REG_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<SUPP_CLASS>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.supp_class)
|| '</SUPP_CLASS>'
);
fnd_file.put_line (fnd_file.output,
'<INVOICE_TYPE>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.invoice_type)
|| '</INVOICE_TYPE>'
);
fnd_file.put_line (fnd_file.output,
'<INVOICE_NUM>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.invoice_num)
|| '</INVOICE_NUM>'
);
fnd_file.put_line (fnd_file.output,
'<INVOICE_DATE>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.invoice_date)
|| '</INVOICE_DATE>'
);
fnd_file.put_line
(fnd_file.output,
'<INVOICE_CURRENCY_CODE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.invoice_currency_code)
|| '</INVOICE_CURRENCY_CODE>'
);
fnd_file.put_line (fnd_file.output,
'<LINE_NUMBER>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.line_number)
|| '</LINE_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<PO_NUMBER>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.po_number)
|| '</PO_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<PO_LINE_NUM>'
 || DBMS_XMLGEN.CONVERT (po_reg_rec.po_line_num)
|| '</PO_LINE_NUM>'
);
fnd_file.put_line (fnd_file.output,
'<LINE_TYPE>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.line_type)
|| '</LINE_TYPE>'
);
fnd_file.put_line
(fnd_file.output,
'<LINE_DESCRIPTION>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.line_description)
|| '</LINE_DESCRIPTION>'
);
fnd_file.put_line (fnd_file.output,
'<ITEM_NUMBER>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.item_number)
|| '</ITEM_NUMBER>'
);
fnd_file.put_line
(fnd_file.output,
'<INVOICE_LINE_AMT>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.invoice_line_amt)
|| '</INVOICE_LINE_AMT>'
);
fnd_file.put_line
(fnd_file.output,
'<INVOICE_LINE_FUNC_AMT>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.invoice_line_func_amt)
|| '</INVOICE_LINE_FUNC_AMT>'
);
fnd_file.put_line
(fnd_file.output,
'<ASSESSABLE_VALUE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.assessable_value)
|| '</ASSESSABLE_VALUE>'
);
fnd_file.put_line (fnd_file.output,
'<EXCHANGE_RATE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.exchange_rate)
|| '</EXCHANGE_RATE>'
);
fnd_file.put_line (fnd_file.output,
'<TAX_RATE_CODE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.tax_rate_code)
|| '</TAX_RATE_CODE>'
);
fnd_file.put_line
(fnd_file.output,
'<RECOVERY_ACCT_CODE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.recovery_acct_code)
|| '</RECOVERY_ACCT_CODE>'
 );
fnd_file.put_line (fnd_file.output,
'<TAX_RATE>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.tax_rate)
|| '</TAX_RATE>'
);
fnd_file.put_line (fnd_file.output,
'<TAX_AMT>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.tax_amt)
|| '</TAX_AMT>'
);
fnd_file.put_line
(fnd_file.output,
'<TAX_AMT_TAX_CURR>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.tax_amt_tax_curr)
|| '</TAX_AMT_TAX_CURR>'
);
fnd_file.put_line
(fnd_file.output,
'<VAT_RECOVERABLE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.vat_recoverable)
|| '</VAT_RECOVERABLE>'
);
fnd_file.put_line
(fnd_file.output,
'<INVOICE_AMOUNT>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.invoice_amount)
|| '</INVOICE_AMOUNT>'
);
fnd_file.put_line
(fnd_file.output,
'<INVOICE_STATUS>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.invoice_status)
|| '</INVOICE_STATUS>'
);
fnd_file.put_line (fnd_file.output,
'<OFFSET_FLAG>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.offset_flag)
|| '</OFFSET_FLAG>'
);
fnd_file.put_line
(fnd_file.output,
'<VAT_RCM_AMT_FUN>'
|| DBMS_XMLGEN.CONVERT
(NVL (po_reg_rec.vat_rcm_amt_fun,
0
)
)
|| '</VAT_RCM_AMT_FUN>'
);
fnd_file.put_line
(fnd_file.output,
'<TAX_CLASSIFICATION_CODE>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.tax_classification_code)
 || '</TAX_CLASSIFICATION_CODE>'
);
-----------------------------------------------------------------------------------
-----------------
---new columns added
fnd_file.put_line (fnd_file.output,
'<JOURNAL_NAME>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.journal_name)
|| '</JOURNAL_NAME>'
);
fnd_file.put_line (fnd_file.output,
'<JE_CATEGORY>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.je_category)
|| '</JE_CATEGORY>'
);
fnd_file.put_line
(fnd_file.output,
'<DISTRIBUTION_AMOUNT>'
|| DBMS_XMLGEN.CONVERT
(po_reg_rec.distribution_amount)
|| '</DISTRIBUTION_AMOUNT>'
);
-- fnd_file.put_line (fnd_file.output,
-- '<JOURNAL_DESC>'
-- || DBMS_XMLGEN.CONVERT (po_reg_rec.journal_desc)
-- || '</JOURNAL_DESC>'
-- );
fnd_file.put_line
(fnd_file.output,
'<TOTAL_DISTRIBUTION>'
|| DBMS_XMLGEN.CONVERT
( po_reg_rec.distribution_amount
+ po_reg_rec.invoice_line_func_amt
)
|| '</TOTAL_DISTRIBUTION>'
);
fnd_file.put_line
(fnd_file.output,
'<TOTAL_VAT_AMOUNT>'
|| DBMS_XMLGEN.CONVERT
( po_reg_rec.tax_amt
+ po_reg_rec.tax_amt_tax_curr
)
|| '</TOTAL_VAT_AMOUNT>'
);
-----------------------------------------------------------------------------------
-----------------
fnd_file.put_line (fnd_file.output,
'<GL_DATE>'
|| DBMS_XMLGEN.CONVERT (po_reg_rec.gl_date)
|| '</GL_DATE>'
);
fnd_file.put_line
(fnd_file.output,
'<LV_TOT_VATLINE_AMT>'
|| DBMS_XMLGEN.CONVERT
( NVL
(po_reg_rec.tax_amt_tax_curr,
0
 )
+ NVL (po_reg_rec.vat_rcm_amt_fun,
0
)
+ po_reg_rec.invoice_line_func_amt
)
|| '</LV_TOT_VATLINE_AMT>'
);
lv_tot_vat_amount :=
lv_tot_vat_amount
+ NVL (po_reg_rec.tax_amt_tax_curr, 0)
+ NVL (po_reg_rec.vat_rcm_amt_fun, 0);
fnd_file.put_line (fnd_file.output, '</PO_ROW>');
END LOOP;

fnd_file.put_line (fnd_file.output,
'<LV_TOT_VAT_AMOUNT>'
|| DBMS_XMLGEN.CONVERT (lv_tot_vat_amount)
|| '</LV_TOT_VAT_AMOUNT>'
);

CLOSE po_reg_cur;

fnd_file.put_line (fnd_file.output, '</PO_OUTPUT>');

END;
END xx_purchase_register;

PROCEDURE sales_register (
p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER,
p_tax_regime_code IN VARCHAR2,
p_registration_number IN VARCHAR2,
p_org_id IN NUMBER,
p_fr_trx_date IN VARCHAR2,
p_to_trx_date IN VARCHAR2,
p_fr_gl_date IN VARCHAR2,
p_to_gl_date IN VARCHAR2,
p_fr_creation IN VARCHAR2,
p_to_creation IN VARCHAR2
)
AS
lv_fr_trx_date DATE
:= TO_DATE (SUBSTR (p_fr_trx_date, 1, 10), 'yyyy/mm/dd');
lv_to_trx_date DATE
:= TO_DATE (SUBSTR (p_to_trx_date, 1, 10), 'yyyy/mm/dd');
lv_fr_creation DATE
:= TO_DATE (SUBSTR (p_fr_creation, 1, 10), 'yyyy/mm/dd');
lv_to_creation DATE
:= TO_DATE (SUBSTR (p_to_creation, 1, 10), 'yyyy/mm/dd');
lv_fr_gl_date DATE
:= TO_DATE (SUBSTR (p_fr_gl_date, 1, 10), 'yyyy/mm/dd');
lv_to_gl_date DATE
:= TO_DATE (SUBSTR (p_to_gl_date, 1, 10), 'yyyy/mm/dd');

CURSOR so_reg_cur
IS
SELECT hou.NAME operating_unit, ---operating unit
(SELECT NAME
FROM xle_entity_profiles
 WHERE legal_entity_id = rcta.legal_entity_id) legal_entity,

--legal entity
rcta.customer_trx_id, bill_data.party_name customer_name,

--customer name
bill_data.account_number customer_number, --customer number
NVL (ship_data.state, bill_data.state) emirates, --
rcta.trx_number invoice_number, --transaction number
rcta.trx_date,

--transaction date
rcta.invoice_currency_code, --invoice currency
rctla.customer_trx_line_id,
rctla.line_number, --invoice line number
rctla.inventory_item_id,
regexp_replace (rctla.description,
'[[:space:]]+',
CHR (32)
) description, --line description
rctla.quantity_invoiced, rctla.unit_selling_price,
rctla.line_type, NVL (rcta.exchange_rate, 1) exchange_rate,

---exchange rate
rctla.extended_amount line_amount,

---line amount (invoice currency)

(NVL (rcta.exchange_rate, 1) * NVL (rctla.extended_amount, 1)
) line_amount_fun, --line amount (functional currency)
(CASE
WHEN NVL (ship_data.country, bill_data.country) IN (
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'GCC_VAT'
AND end_date_active IS NULL)
THEN 'GCC'
ELSE 'NON-GCC'
END
) country, ---gcc/ non gcc
( bill_data.address1
|| DECODE (bill_data.address1, NULL, NULL, ',')
|| bill_data.address2
|| DECODE (bill_data.address2, NULL, NULL, ',')
|| bill_data.address3
|| DECODE (bill_data.address3, NULL, NULL, ',')
|| bill_data.address4
|| DECODE (bill_data.address4, NULL, NULL, ',')
|| bill_data.city
|| DECODE (bill_data.city, NULL, NULL, ',')
|| bill_data.state
|| DECODE (bill_data.state, NULL, NULL, ',')
|| bill_data.province
|| DECODE (bill_data.province, NULL, NULL, ',')
|| bill_data.postal_code
|| DECODE (bill_data.postal_code, NULL, NULL, ',')
|| (SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = bill_data.country)
) bill_to_add, --bill to address
( ship_data.address1
|| DECODE (ship_data.address1, NULL, NULL, ',')
|| ship_data.address2
|| DECODE (ship_data.address2, NULL, NULL, ',')
|| ship_data.address3
|| DECODE (ship_data.address3, NULL, NULL, ',')
|| ship_data.address4
|| DECODE (ship_data.address4, NULL, NULL, ',')
|| ship_data.city
|| DECODE (ship_data.city, NULL, NULL, ',')
|| ship_data.state
|| DECODE (ship_data.state, NULL, NULL, ',')
|| ship_data.province
|| DECODE (ship_data.province, NULL, NULL, ',')
|| ship_data.postal_code
|| DECODE (ship_data.postal_code, NULL, NULL, ',')
|| (SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = ship_data.country)
) ship_to_add, --ship to address
(SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = bill_data.country) bill_to_country,

---customer bill to country

---ship_data.state ship_emirates,
(CASE
WHEN ship_data.country = 'AE'
THEN ship_data.state
WHEN ship_data.country = 'SA'
THEN NULL
ELSE NULL
END
) ship_emirates, --customer ship to emirate
(CASE
WHEN bill_data.country = 'AE'
THEN bill_data.state
WHEN bill_data.country = 'SA'
THEN NULL
ELSE NULL
END
) bill_emirates, --customer bill to emirate
rctt.NAME type_name, --transaction_type
(CASE
WHEN ship_data.country = 'AE'
THEN ship_data.state
WHEN ship_data.country = 'SA'
THEN ship_data.country
ELSE ship_data.country
END
) place_of_supply,

---decode (zl.tax_rate_code, 'exempt', 'exempt', 'non exempt')

xxvat_poso_reg_pkg.get_customer_exemption
('PARTY',
bill_data.party_id
) customer_classification,
--customer classification
zl.unrounded_tax_amt tax_amt,

--vat amount (invoice currency)

DECODE (zl.tax_amt_funcl_curr,
NULL, NVL (zl.unrounded_tax_amt, 0),
0, NVL (zl.unrounded_tax_amt, 0),
zl.tax_amt_funcl_curr
) tax_amt_fun, --vat amount (functional currency)
(NVL (rctla.extended_amount, 0)
+ NVL (zl.unrounded_tax_amt, 0)
) total_amt_inv, --total amount (invoice currency)
( ( NVL (rcta.exchange_rate, 1)
* NVL (rctla.extended_amount, 1)
)
+ DECODE (zl.tax_amt_funcl_curr,
NULL, NVL (zl.unrounded_tax_amt, 0),
0, NVL (zl.unrounded_tax_amt, 0),
zl.tax_amt_funcl_curr
)
) total_amt_fun, --total amount (functional currency)
(CASE
WHEN zl.tax_rate IS NOT NULL
THEN zl.tax_rate || '%'
WHEN zl.tax_rate IS NULL
THEN NULL
ELSE NULL
END
) tax_rate, --vat %
zl.tax_rate_code tax_rate_name, --vat rate name
NVL ((SELECT tax_regime_code
FROM zx_regimes_b zr
WHERE tax_regime_id = zl.tax_regime_id),
''
) tax_regime_code,
xxvat_poso_reg_pkg.get_customer_type
(rcta.bill_to_customer_id)
customer_type,

--customer type
xxvat_poso_reg_pkg.get_supply_classification
('CUSTOMER',
NVL (ship_data.country, bill_data.country),
p_tax_regime_code
) supply_classification,

--supply classification
xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
bill_data.party_site_id
) registration_number, --customer vatin
xxvat_poso_reg_pkg.get_registration_value
('STATUS',
bill_data.party_site_id
) registration_status,
msi.concatenated_segments item_number, ---item number
DECODE (rctt.TYPE,
'INV', 'Invoice',
'CM', 'Credit Memo',
 'DM', 'Debit Memo',
NULL
) invoice_type, --transaction class
rcta.org_id org_id,
(SELECT concatenated_segments
FROM gl_code_combinations_kfv glk
WHERE glk.code_combination_id =
rcgl.code_combination_id)
rev_code_combination,

---amount liability account

(SELECT gcc.concatenated_segments
FROM ra_cust_trx_line_gl_dist_all d2,
gl_code_combinations_kfv gcc
WHERE 1 = 1
AND d2.code_combination_id = gcc.code_combination_id
AND d2.cust_trx_line_gl_dist_id =
(SELECT MAX (cust_trx_line_gl_dist_id)
FROM ra_customer_trx_lines_all l1,
ra_cust_trx_line_gl_dist_all d1
WHERE l1.customer_trx_id = rctla.customer_trx_id
AND l1.customer_trx_line_id =
d1.customer_trx_line_id
AND account_class = 'TAX'
AND line_type = 'TAX'
AND event_id IS NOT NULL
AND l1.link_to_cust_trx_line_id =
rctla.customer_trx_line_id)
AND NVL (ccid_change_flag, 'Y') = 'Y')
tax_code_combination,

---vat liability account

(SELECT SUM (extended_amount)
FROM ra_customer_trx_lines_all
WHERE customer_trx_id =
rctla.customer_trx_id)
tot_invoice_val,
xxvat_poso_reg_pkg.get_tax_classification
('SALES',
rctla.customer_trx_id,
rctla.customer_trx_line_id
) tax_classification_code,
bill_data.category_code customer_category,
(SELECT trx_number
FROM ra_customer_trx_all
WHERE customer_trx_id =
rcta.previous_customer_trx_id
AND org_id = rcta.org_id) reference_inv_num,
(SELECT TRUNC (trx_date)
FROM ra_customer_trx_all
WHERE customer_trx_id =
rcta.previous_customer_trx_id
AND org_id = rcta.org_id) reference_inv_date,
TRUNC (rcgl.gl_date) gl_date
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
hr_operating_units hou,

--hz_cust_accounts hca,
 --hz_parties hp,
(SELECT hp.party_id, hps.party_site_id, hp.party_name,
hl.country, hl.state, hl.city, hl.address1,
hl.address2, hl.address3, hl.address4, hl.province,
hl.postal_code, hca.cust_account_id,
hca.account_number, hcsu.site_use_id,
hcsu.cust_acct_site_id, hp.category_code
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
hz_parties hp,
hz_party_sites hps,
hz_locations hl
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id(+)
AND hcsa.party_site_id = hps.party_site_id(+)
AND hcsa.cust_account_id = hca.cust_account_id(+)
AND hl.location_id(+) = hps.location_id
AND hca.party_id = hp.party_id
AND hcsu.site_use_code = 'BILL_TO') bill_data,
(SELECT hp.party_id, hps.party_site_id, hp.party_name,
hl.country, hl.state, hl.city, hl.address1,
hl.address2, hl.address3, hl.address4, hl.province,
hl.postal_code, hca.cust_account_id,
hca.account_number, hcsu.site_use_id,
hcsu.cust_acct_site_id
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
hz_parties hp,
hz_party_sites hps,
hz_locations hl
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id(+)
AND hcsa.party_site_id = hps.party_site_id(+)
AND hcsa.cust_account_id = hca.cust_account_id(+)
AND hl.location_id(+) = hps.location_id
AND hca.party_id = hp.party_id
AND hcsu.site_use_code = 'SHIP_TO') ship_data,
ra_cust_trx_types_all rctt,
zx_lines zl,
mtl_system_items_kfv msi,
ra_cust_trx_line_gl_dist_all rcgl
WHERE 1 = 1
--and rcta.trx_number in ('3004972', '3004971')
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rcta.org_id = hou.organization_id
AND line_type = 'LINE'
AND rcta.complete_flag = 'Y'
AND bill_data.site_use_id(+) = rcta.bill_to_site_use_id
-- and bill_data.cust_account_id = rcta.bill_to_customer_id
AND ship_data.site_use_id(+) = rcta.ship_to_site_use_id
-- and ship_data.cust_account_id = rcta.ship_to_customer_id
AND rctt.cust_trx_type_id = rcta.cust_trx_type_id
AND rctt.org_id = rcta.org_id
AND rctla.customer_trx_id = zl.trx_id(+)
AND rctla.customer_trx_line_id = zl.trx_line_id(+)
AND rctla.inventory_item_id = msi.inventory_item_id(+)
AND rctla.warehouse_id = msi.organization_id(+)
AND rcta.customer_trx_id = rcgl.customer_trx_id
AND rctla.customer_trx_line_id = rcgl.customer_trx_line_id
 AND rcgl.account_class = 'REV'
AND rcgl.event_id IS NOT NULL
AND TRUNC (rcta.trx_date) BETWEEN NVL (lv_fr_trx_date,
rcta.trx_date
)
AND NVL (lv_to_trx_date,
rcta.trx_date
)
AND TRUNC (rcgl.gl_date) BETWEEN NVL (lv_fr_gl_date, rcgl.gl_date)
AND NVL (lv_to_gl_date, rcgl.gl_date)
AND TRUNC (rcta.creation_date)
BETWEEN NVL (lv_fr_creation, TRUNC (rcta.creation_date))
AND NVL (lv_to_creation, TRUNC (rcta.creation_date))
--and rcta.customer_trx_id = 56701228
AND rcta.org_id = NVL (p_org_id, rcta.org_id)
AND p_tax_regime_code IN (
SELECT tax_regime_code
FROM zx_regimes_b zrb,
hr_locations hl,
hr_all_organization_units hou
WHERE 1 = 1
AND zrb.country_code = hl.country
AND hl.location_id = hou.location_id
AND effective_to IS NULL
AND organization_id = rcta.org_id)
AND NVL
(xxvat_poso_reg_pkg.get_vat_reg_num (rcta.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
) =
NVL
(p_registration_number,
NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(rcta.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
)
)
UNION ALL
SELECT operating_unit, legal_entity, --
cash_receipt_id customer_trx_id,
customer_name, customer_number, --
emirates,
receipt_number invoice_number, receipt_date trx_date,
currency_code invoice_currency_code,
NULL customer_trx_line_id, NULL line_number,
NULL inventory_item_id, NULL description,
NULL quantity_invoiced, NULL unit_selling_price,
NULL line_type, exchange_rate, line_amount, line_amount_fun,
country, ---gcc/ non gcc
bill_to_add, NULL ship_to_add, bill_to_country,
NULL ship_emirates, bill_emirates, type_name,
--transaction_type
place_of_supply,
customer_classification, tax_amt, tax_amt_fun, total_amt_inv,
total_amt_fun, tax_rate, tax_rate_name, tax_regime_code,
 customer_type, --
supply_classification, registration_number,
registration_status, NULL item_number, invoice_type,
--transaction
class
org_id,
rev_code_combination, ---amount liability account
tax_code_combination,
---vat liability account
tot_invoice_val,
NULL tax_classification_code, NULL customer_category,
NULL reference_inv_num, NULL reference_inv_date, gl_date
/* NULL posting_gl,
'NO' adj_flag,
NULL date_of_supply,
NULL export_inv_no,
NULL bayan_no,
NULL bayan_date,
NULL reference_inv_date,
NULL bl_ref_date */
FROM (SELECT hou.NAME operating_unit,
(SELECT NAME
FROM xle_entity_profiles
WHERE legal_entity_id =
acr.legal_entity_id)
legal_entity,
acr.cash_receipt_id cash_receipt_id,

-- bill_data.cust_account_id cust_account_id,
bill_data.party_name customer_name,
bill_data.account_number customer_number,
bill_data.state emirates,
acr.receipt_number receipt_number,
TO_DATE
(TO_CHAR (TRUNC (acr.receipt_date), 'DD-MM-YYYY'),
'DD-MM-YYYY'
) receipt_date,
acr.currency_code currency_code,
NULL customer_trx_line_id, NULL line_number,
NULL inventory_item_id, NULL description,
NULL quantity_invoiced, NULL unit_selling_price,
NULL line_type,
NVL (acr.exchange_rate, 1) exchange_rate,
( amount_applied
- (ROUND ( (amount_applied * percentage_rate)
/ (100 + percentage_rate),
2
)
)
) line_amount,
( ( amount_applied
- (ROUND ( (amount_applied * percentage_rate)
/ (100 + percentage_rate),
2
)
)
)
* (NVL (acr.exchange_rate, 1))
) line_amount_fun,
(CASE
WHEN bill_data.country IN (
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'GCC_VAT'
AND end_date_active IS NULL)
THEN 'GCC'
ELSE 'NON-GCC'
END
) country,
( bill_data.address1
|| DECODE (bill_data.address1, NULL, NULL, ',')
|| bill_data.address2
|| DECODE (bill_data.address2, NULL, NULL, ',')
|| bill_data.address3
|| DECODE (bill_data.address3, NULL, NULL, ',')
|| bill_data.address4
|| DECODE (bill_data.address4, NULL, NULL, ',')
|| bill_data.city
|| DECODE (bill_data.city, NULL, NULL, ',')
|| bill_data.state
|| DECODE (bill_data.state, NULL, NULL, ',')
|| bill_data.province
|| DECODE (bill_data.province, NULL, NULL, ',')
|| bill_data.postal_code
|| DECODE (bill_data.postal_code, NULL, NULL, ',')
|| (SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = bill_data.country)
) bill_to_add, --bill to address,
NULL ship_to_add,
(SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code =
bill_data.country)
bill_to_country,

---customer bill to country,

NULL ship_emirates,
(CASE
WHEN bill_data.country = 'AE'
THEN bill_data.state
WHEN bill_data.country = 'SA'
THEN NULL
ELSE NULL
END
) bill_emirates, --customer bill to emirate
DECODE (SIGN (amount_applied),
1, 'AR Advance Creation',
'AR Advance Reversal'
) type_name,
(CASE
WHEN bill_data.country = 'AE'
THEN bill_data.state
WHEN bill_data.country = 'SA'
THEN bill_data.country
ELSE bill_data.country
END
) place_of_supply,
 xxvat_poso_reg_pkg.get_customer_exemption
('PARTY',
bill_data.party_id
) customer_classification,

--customer classification
-- xxvat_poso_reg_pkg.get_customer_exemption (
-- 'SITE',
-- bill_data.party_site_id
-- )
-- customer_classification_site,
ROUND ( (amount_applied * percentage_rate)
/ (100 + percentage_rate),
2
) tax_amt,
( (ROUND ( (amount_applied * percentage_rate)
/ (100 + percentage_rate),
2
)
)
* (NVL (acr.exchange_rate, 1))
) tax_amt_fun,
amount_applied total_amt_inv,
((amount_applied) * (NVL (acr.exchange_rate, 1))
) total_amt_fun,
(CASE
WHEN percentage_rate IS NOT NULL
THEN percentage_rate || '%'
WHEN percentage_rate IS NULL
THEN NULL
ELSE NULL
END
) tax_rate,
tax_rate_code tax_rate_name,
arb.tax_regime_code tax_regime_code,
xxvat_poso_reg_pkg.get_customer_type
(acr.pay_from_customer)
customer_type,

--customer type
xxvat_poso_reg_pkg.get_supply_classification
('CUSTOMER',
bill_data.country,
p_tax_regime_code
) supply_classification,

--supply classification
xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
bill_data.party_site_id
) registration_number,

--customer vatin
xxvat_poso_reg_pkg.get_registration_value
('STATUS',
bill_data.party_site_id
) registration_status,
NULL item_number, 'AR Advance' invoice_type,
acr.org_id org_id,
 gcc.concatenated_segments rev_code_combination,
gcc1.concatenated_segments tax_code_combination,

-- ara.amount_applied on_account,
acr.amount tot_invoice_val,
NULL tax_classification_code, NULL customer_category,
NULL reference_inv_num, NULL reference_inv_date,
TO_DATE (TO_CHAR (TRUNC (ara.gl_date), 'DD-MM-YYYY'),
'DD-MM-YYYY'
) gl_date
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara,
gl_code_combinations_kfv gcc,
zx_rates_b arb,
zx_accounts zac,
gl_code_combinations_kfv gcc1,
hr_operating_units hou,
(SELECT hp.party_id, hps.party_site_id, hp.party_name,
hl.country, hl.state, hl.city, hl.address1,
hl.address2, hl.address3, hl.address4,
hl.province, hl.postal_code,
hca.cust_account_id, hca.account_number,
hcsu.site_use_id, hcsu.cust_acct_site_id
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
hz_parties hp,
hz_party_sites hps,
hz_locations hl
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id(+)
AND hcsa.party_site_id = hps.party_site_id(+)
AND hcsa.cust_account_id = hca.cust_account_id(+)
AND hl.location_id(+) = hps.location_id
AND hca.party_id = hp.party_id) bill_data
WHERE 1 = 1
AND acr.cash_receipt_id = ara.cash_receipt_id
AND ara.status = 'ACC'
AND arb.percentage_rate > 0
AND ara.code_combination_id = gcc.code_combination_id
AND TO_NUMBER (acr.attribute15) = arb.tax_rate_id
AND tax_account_entity_code = 'RATES'
AND tax_account_entity_id = arb.tax_rate_id
AND zac.tax_account_ccid = gcc1.code_combination_id
AND zac.internal_organization_id = acr.org_id
AND hou.organization_id = acr.org_id
AND bill_data.cust_account_id = acr.pay_from_customer
AND bill_data.site_use_id(+) = acr.customer_site_use_id
AND TRUNC (acr.receipt_date) BETWEEN NVL (lv_fr_trx_date,
acr.receipt_date
)
AND NVL (lv_to_trx_date,
acr.receipt_date
)
AND TRUNC (ara.gl_date) BETWEEN NVL (lv_fr_gl_date,
ara.gl_date
)
AND NVL (lv_to_gl_date,
ara.gl_date
)
 AND acr.org_id = NVL (p_org_id, acr.org_id)
AND p_tax_regime_code IN (
SELECT tax_regime_code
FROM zx.zx_regimes_b zrb,
hr_locations hl,
hr_all_organization_units hou
WHERE 1 = 1
AND zrb.country_code = hl.country
AND hl.location_id = hou.location_id
AND effective_to IS NULL
AND organization_id = acr.org_id)
AND NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(acr.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
) =
NVL
(p_registration_number,
NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(acr.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
)
))
UNION ALL
SELECT operating_unit, legal_entity, --
cash_receipt_id, customer_name,
customer_number, --
emirates, receipt_number, receipt_date,
currency_code, NULL customer_trx_line_id, NULL line_number,
NULL inventory_item_id, NULL description,
NULL quantity_invoiced, NULL unit_selling_price,
NULL line_type, exchange_rate, line_amount, line_amount_fun,
country, ---gcc/ non gcc
bill_to_add, NULL ship_to_add, bill_to_country,
NULL ship_emirates, bill_emirates, type_name,
--transaction_type
place_of_supply,
customer_classification, tax_amt, tax_amt_fun, total_amt_inv,
total_amt_fun, tax_rate, tax_rate_name, tax_regime_code,
customer_type, --
supply_classification, registration_number,
registration_status, NULL item_number, invoice_type,
--transaction
class
org_id,
rev_code_combination, ---amount liability account
tax_code_combination,
---vat liability account
tot_invoice_val,
NULL tax_classification_code, NULL customer_category,
NULL reference_inv_num, NULL reference_inv_date, gl_date
/*NULL posting_gl,
'NO' adj_flag,
NULL date_of_supply,
 NULL export_inv_no,
NULL bayan_no,
NULL bayan_date,*/
FROM (SELECT acr.cash_receipt_id cash_receipt_id,
hou.NAME operating_unit,
(SELECT NAME
FROM xle_entity_profiles
WHERE legal_entity_id =
acr.legal_entity_id)
legal_entity,
bill_data.cust_account_id cust_account_id,
bill_data.party_name customer_name,
bill_data.account_number customer_number,
bill_data.state emirates,
acr.receipt_number receipt_number,
TO_DATE
(TO_CHAR (TRUNC (acr.receipt_date), 'DD-MM-YYYY'),
'DD-MM-YYYY'
) receipt_date,
acr.currency_code currency_code, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NVL (acr.exchange_rate, 1) exchange_rate,
-1 * line_applied line_amount,
-1
* (line_applied * NVL (acr.exchange_rate, 1))
line_amount_fun,
-1 * amount_applied total_amt_inv,
-1
* ((amount_applied) * (NVL (acr.exchange_rate, 1)))
total_amt_fun,
(CASE
WHEN bill_data.country IN (
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'GCC_VAT'
AND end_date_active IS NULL)
THEN 'GCC'
ELSE 'NON-GCC'
END
) country,
( bill_data.address1
|| DECODE (bill_data.address1, NULL, NULL, ',')
|| bill_data.address2
|| DECODE (bill_data.address2, NULL, NULL, ',')
|| bill_data.address3
|| DECODE (bill_data.address3, NULL, NULL, ',')
|| bill_data.address4
|| DECODE (bill_data.address4, NULL, NULL, ',')
|| bill_data.city
|| DECODE (bill_data.city, NULL, NULL, ',')
|| bill_data.state
|| DECODE (bill_data.state, NULL, NULL, ',')
|| bill_data.province
|| DECODE (bill_data.province, NULL, NULL, ',')
|| bill_data.postal_code
|| DECODE (bill_data.postal_code, NULL, NULL, ',')
|| (SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = bill_data.country)
) bill_to_add, --bill to address,
NULL,
(SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code =
bill_data.country)
bill_to_country,

---customer bill to country,

NULL,
(CASE
WHEN bill_data.country = 'AE'
THEN bill_data.state
WHEN bill_data.country = 'SA'
THEN NULL
ELSE NULL
END
) bill_emirates, --customer bill to emirate
(CASE
WHEN bill_data.country = 'AE'
THEN bill_data.state
WHEN bill_data.country = 'SA'
THEN bill_data.country
ELSE bill_data.country
END
) place_of_supply,
xxvat_poso_reg_pkg.get_customer_exemption
('PARTY',
bill_data.party_id
) customer_classification,

--customer classification
xxvat_poso_reg_pkg.get_customer_exemption
('SITE',
bill_data.party_site_id
) customer_classification_site,
-1 * tax_applied tax_amt,
-1
* (tax_applied * NVL (acr.exchange_rate, 1))
tax_amt_fun,
(CASE
WHEN percentage_rate IS NOT NULL
THEN percentage_rate || '%'
WHEN percentage_rate IS NULL
THEN NULL
ELSE NULL
END
) tax_rate,
tax_rate_code tax_rate_name,
arb.tax_regime_code tax_regime_code,
xxvat_poso_reg_pkg.get_customer_type
(acr.pay_from_customer)
customer_type,

--customer type
xxvat_poso_reg_pkg.get_supply_classification
('CUSTOMER',
bill_data.country,
p_tax_regime_code
 ) supply_classification,

--supply classification
xxvat_poso_reg_pkg.get_registration_value
('NUMBER',
bill_data.party_site_id
) registration_number,

--customer vatin
xxvat_poso_reg_pkg.get_registration_value
('STATUS',
bill_data.party_site_id
) registration_status,
ara.amount_applied on_account, NULL,
'AR Advance Application' invoice_type,
DECODE (SIGN (amount_applied),
1, 'AR Advance Creation',
'AR Advance Reversal'
) type_name,
acr.org_id org_id,
gcc.concatenated_segments rev_code_combination,
gcc1.concatenated_segments tax_code_combination,
acr.amount tot_invoice_val,
TO_DATE (TO_CHAR (TRUNC (ara.gl_date), 'DD-MM-YYYY'),
'DD-MM-YYYY'
) gl_date
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara,
gl_code_combinations_kfv gcc,
zx_rates_b arb,
zx_accounts zac,
gl_code_combinations_kfv gcc1,
hr_operating_units hou,
(SELECT hp.party_id, hps.party_site_id, hp.party_name,
hl.country, hl.state, hl.city, hl.address1,
hl.address2, hl.address3, hl.address4,
hl.province, hl.postal_code,
hca.cust_account_id, hca.account_number,
hcsu.site_use_id, hcsu.cust_acct_site_id
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
hz_parties hp,
hz_party_sites hps,
hz_locations hl
WHERE hcsu.cust_acct_site_id = hcsa.cust_acct_site_id(+)
AND hcsa.party_site_id = hps.party_site_id(+)
AND hcsa.cust_account_id = hca.cust_account_id(+)
AND hl.location_id(+) = hps.location_id
AND hca.party_id = hp.party_id) bill_data
WHERE 1 = 1
AND acr.cash_receipt_id = ara.cash_receipt_id
AND ara.status = 'APP'
AND arb.percentage_rate > 0
AND ara.code_combination_id = gcc.code_combination_id
AND TO_NUMBER (acr.attribute15) = arb.tax_rate_id
AND tax_account_entity_code = 'RATES'
AND tax_account_entity_id = arb.tax_rate_id
AND zac.tax_account_ccid = gcc1.code_combination_id
 AND zac.internal_organization_id = acr.org_id
AND hou.organization_id = acr.org_id
AND bill_data.cust_account_id = acr.pay_from_customer
AND bill_data.site_use_id(+) = acr.customer_site_use_id
AND TRUNC (acr.receipt_date) BETWEEN NVL (lv_fr_trx_date,
acr.receipt_date
)
AND NVL (lv_to_trx_date,
acr.receipt_date
)
AND TRUNC (ara.gl_date) BETWEEN NVL (lv_fr_gl_date,
ara.gl_date
)
AND NVL (lv_to_gl_date,
ara.gl_date
)
AND acr.org_id = NVL (p_org_id, acr.org_id)
AND p_tax_regime_code IN (
SELECT tax_regime_code
FROM zx.zx_regimes_b zrb,
hr_locations hl,
hr_all_organization_units hou
WHERE 1 = 1
AND zrb.country_code = hl.country
AND hl.location_id = hou.location_id
AND effective_to IS NULL
AND organization_id = acr.org_id)
AND NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(acr.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
) =
NVL
(p_registration_number,
NVL
(xxvat_poso_reg_pkg.get_vat_reg_num
(acr.legal_entity_id,
p_tax_regime_code
),
'VATREG123456'
)
));

so_reg_rec so_reg_cur%ROWTYPE;
lv_org_name hr_operating_units.NAME%TYPE;
lv_tot_vat_amount NUMBER;
BEGIN
BEGIN
fnd_file.put_line
(fnd_file.output,
'<?xml version="1.0" encoding="US-ASCII" standalone="no"?>'
);
fnd_file.put_line (fnd_file.output, '<SO_OUTPUT>');

/************************************parameters xml
tag*******************************/
BEGIN
 SELECT NAME
INTO lv_org_name
FROM hr_operating_units
WHERE 1 = 1 AND organization_id = p_org_id;
EXCEPTION
WHEN OTHERS
THEN
lv_org_name := NULL;
END;

fnd_file.put_line (fnd_file.output,
'<P_TAX_REGIME_CODE>'
|| DBMS_XMLGEN.CONVERT (p_tax_regime_code)
|| '</P_TAX_REGIME_CODE>'
);
fnd_file.put_line (fnd_file.output,
'<P_REGISTRATION_NUMBER>'
|| DBMS_XMLGEN.CONVERT (p_registration_number)
|| '</P_REGISTRATION_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<P_FR_TRX_DATE>'
|| DBMS_XMLGEN.CONVERT (p_fr_trx_date)
|| '</P_FR_TRX_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_TO_TRX_DATE>'
|| DBMS_XMLGEN.CONVERT (p_to_trx_date)
|| '</P_TO_TRX_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_FR_CREATION>'
|| DBMS_XMLGEN.CONVERT (p_fr_creation)
|| '</P_FR_CREATION>'
);
fnd_file.put_line (fnd_file.output,
'<P_TO_CREATION>'
|| DBMS_XMLGEN.CONVERT (p_to_creation)
|| '</P_TO_CREATION>'
);
fnd_file.put_line (fnd_file.output,
'<P_FR_GL_DATE>'
|| DBMS_XMLGEN.CONVERT (p_fr_gl_date)
|| '</P_FR_GL_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<P_TO_GL_DATE>'
|| DBMS_XMLGEN.CONVERT (p_to_gl_date)
|| '</P_TO_GL_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<LV_ORG_NAME>'
|| DBMS_XMLGEN.CONVERT (lv_org_name)
|| '</LV_ORG_NAME>'
);

OPEN so_reg_cur;

lv_tot_vat_amount := 0;
LOOP
--
FETCH so_reg_cur
INTO so_reg_rec;

EXIT WHEN so_reg_cur%NOTFOUND;

fnd_file.put_line (fnd_file.output, '<SO_ROW>');
fnd_file.put_line
(fnd_file.output,
'<OPERATING_UNIT>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.operating_unit)
|| '</OPERATING_UNIT>'
);
fnd_file.put_line (fnd_file.output,
'<LEGAL_ENTITY>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.legal_entity)
|| '</LEGAL_ENTITY>'
);
fnd_file.put_line (fnd_file.output,
'<CUSTOMER_NAME>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.customer_name)
|| '</CUSTOMER_NAME>'
);
fnd_file.put_line
(fnd_file.output,
'<CUSTOMER_NUMBER>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.customer_number)
|| '</CUSTOMER_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<EMIRATES>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.emirates)
|| '</EMIRATES>'
);
fnd_file.put_line
(fnd_file.output,
'<INVOICE_NUMBER>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.invoice_number)
|| '</INVOICE_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<TRX_DATE>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.trx_date)
|| '</TRX_DATE>'
);
fnd_file.put_line
(fnd_file.output,
'<INVOICE_CURRENCY_CODE>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.invoice_currency_code)
|| '</INVOICE_CURRENCY_CODE>'
);
fnd_file.put_line (fnd_file.output,
'<LINE_NUMBER>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.line_number)
 || '</LINE_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<DESCRIPTION>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.description)
|| '</DESCRIPTION>'
);
fnd_file.put_line
(fnd_file.output,
'<QUANTITY_INVOICED>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.quantity_invoiced)
|| '</QUANTITY_INVOICED>'
);
fnd_file.put_line
(fnd_file.output,
'<UNIT_SELLING_PRICE>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.unit_selling_price)
|| '</UNIT_SELLING_PRICE>'
);
fnd_file.put_line (fnd_file.output,
'<LINE_TYPE>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.line_type)
|| '</LINE_TYPE>'
);
fnd_file.put_line (fnd_file.output,
'<EXCHANGE_RATE>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.exchange_rate)
|| '</EXCHANGE_RATE>'
);
fnd_file.put_line (fnd_file.output,
'<LINE_AMOUNT>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.line_amount)
|| '</LINE_AMOUNT>'
);
fnd_file.put_line
(fnd_file.output,
'<LINE_AMOUNT_FUN>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.line_amount_fun)
|| '</LINE_AMOUNT_FUN>'
);
fnd_file.put_line (fnd_file.output,
'<COUNTRY>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.country)
|| '</COUNTRY>'
);
fnd_file.put_line (fnd_file.output,
'<BILL_TO_ADD>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.bill_to_add)
|| '</BILL_TO_ADD>'
);
fnd_file.put_line (fnd_file.output,
'<SHIP_TO_ADD>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.ship_to_add)
|| '</SHIP_TO_ADD>'
);
fnd_file.put_line
(fnd_file.output,
'<BILL_TO_COUNTRY>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.bill_to_country)
|| '</BILL_TO_COUNTRY>'
);
fnd_file.put_line (fnd_file.output,
'<SHIP_EMIRATES>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.ship_emirates)
|| '</SHIP_EMIRATES>'
);
fnd_file.put_line (fnd_file.output,
'<BILL_EMIRATES>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.bill_emirates)
|| '</BILL_EMIRATES>'
);
fnd_file.put_line (fnd_file.output,
'<TYPE_NAME>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.type_name)
|| '</TYPE_NAME>'
);
fnd_file.put_line
(fnd_file.output,
'<CUSTOMER_CLASSIFICATION>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.customer_classification)
|| '</CUSTOMER_CLASSIFICATION>'
);
fnd_file.put_line (fnd_file.output,
'<TAX_AMT>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.tax_amt)
|| '</TAX_AMT>'
);
fnd_file.put_line (fnd_file.output,
'<TAX_AMT_FUN>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.tax_amt_fun)
|| '</TAX_AMT_FUN>'
);
fnd_file.put_line (fnd_file.output,
'<TOTAL_AMT_INV>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.total_amt_inv)
|| '</TOTAL_AMT_INV>'
);
fnd_file.put_line (fnd_file.output,
'<TOTAL_AMT_FUN>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.total_amt_fun)
|| '</TOTAL_AMT_FUN>'
);
fnd_file.put_line (fnd_file.output,
'<TAX_RATE>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.tax_rate)
|| '</TAX_RATE>'
);
fnd_file.put_line (fnd_file.output,
 '<TAX_RATE_NAME>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.tax_rate_name)
|| '</TAX_RATE_NAME>'
);
fnd_file.put_line
(fnd_file.output,
'<TAX_REGIME_CODE>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.tax_regime_code)
|| '</TAX_REGIME_CODE>'
);
fnd_file.put_line (fnd_file.output,
'<CUSTOMER_TYPE>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.customer_type)
|| '</CUSTOMER_TYPE>'
);
fnd_file.put_line
(fnd_file.output,
'<SUPPLY_CLASSIFICATION>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.supply_classification)
|| '</SUPPLY_CLASSIFICATION>'
);
fnd_file.put_line
(fnd_file.output,
'<REGISTRATION_NUMBER>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.registration_number)
|| '</REGISTRATION_NUMBER>'
);
fnd_file.put_line
(fnd_file.output,
'<REGISTRATION_STATUS>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.registration_status)
|| '</REGISTRATION_STATUS>'
);
fnd_file.put_line (fnd_file.output,
'<ITEM_NUMBER>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.item_number)
|| '</ITEM_NUMBER>'
);
fnd_file.put_line (fnd_file.output,
'<INVOICE_TYPE>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.invoice_type)
|| '</INVOICE_TYPE>'
);
fnd_file.put_line
(fnd_file.output,
'<REV_CODE_COMBINATION>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.rev_code_combination)
|| '</REV_CODE_COMBINATION>'
);
fnd_file.put_line
(fnd_file.output,
'<TAX_CODE_COMBINATION>'
 || DBMS_XMLGEN.CONVERT
(so_reg_rec.tax_code_combination)
|| '</TAX_CODE_COMBINATION>'
);
fnd_file.put_line
(fnd_file.output,
'<TOT_INVOICE_VAL>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.tot_invoice_val)
|| '</TOT_INVOICE_VAL>'
);
fnd_file.put_line
(fnd_file.output,
'<TAX_CLASSIFICATION_CODE>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.tax_classification_code)
|| '</TAX_CLASSIFICATION_CODE>'
);
fnd_file.put_line
(fnd_file.output,
'<CUSTOMER_CATEGORY>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.customer_category)
|| '</CUSTOMER_CATEGORY>'
);
fnd_file.put_line
(fnd_file.output,
'<REFERENCE_INV_NUM>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.reference_inv_num)
|| '</REFERENCE_INV_NUM>'
);
fnd_file.put_line
(fnd_file.output,
'<REFERENCE_INV_DATE>'
|| DBMS_XMLGEN.CONVERT
(so_reg_rec.reference_inv_date)
|| '</REFERENCE_INV_DATE>'
);
fnd_file.put_line (fnd_file.output,
'<GL_DATE>'
|| DBMS_XMLGEN.CONVERT (so_reg_rec.gl_date)
|| '</GL_DATE>'
);
lv_tot_vat_amount := lv_tot_vat_amount + so_reg_rec.tax_amt_fun;
fnd_file.put_line (fnd_file.output, '</SO_ROW>');
END LOOP;

fnd_file.put_line (fnd_file.output,
'<LV_TOT_VAT_AMOUNT>'
|| DBMS_XMLGEN.CONVERT (lv_tot_vat_amount)
|| '</LV_TOT_VAT_AMOUNT>'
);

CLOSE so_reg_cur;

fnd_file.put_line (fnd_file.output, '</SO_OUTPUT>');

END;
END sales_register;
END xxvat_poso_reg_pkg_egg;