[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER(15);
g_last_update_date DATE;
g_last_update_login NUMBER(15);
PROCEDURE Insert_tax_data(
p_vat_reporting_entity_id IN NUMBER,
p_year_of_declaration IN NUMBER,
p_app_id IN NUMBER);
SELECT status_code
FROM je_it_list_hdr_all
WHERE vat_reporting_entity_id= P_VAT_REPORTING_ENTITY_ID
AND year_of_declaration = P_YEAR_OF_DECLARATION;
SELECT freeze_indicator_flag
FROM je_it_setup_hdr_all
WHERE vat_reporting_entity_id= P_VAT_REPORTING_ENTITY_ID
AND year_of_declaration = P_YEAR_OF_DECLARATION;
SELECT LEGAL.ENTITY_IDENTIFIER,LEGAL.VAT_REPORTING_ENTITY_ID
FROM jg_zz_vat_rep_entities LEGAL,
jg_zz_vat_rep_entities ACC
WHERE ACC.VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
AND ((ACC.ENTITY_TYPE_CODE = 'ACCOUNTING'
AND ACC.MAPPING_VAT_REP_ENTITY_ID = LEGAL.VAT_REPORTING_ENTITY_ID)
OR
(ACC.ENTITY_TYPE_CODE = 'LEGAL'
AND ACC.VAT_REPORTING_ENTITY_ID = LEGAL.VAT_REPORTING_ENTITY_ID)
);
SELECT NVL(MAX(TRANSMISSION_NUM),0) --Bug:8274173
INTO l_eft_count
FROM JE_IT_LIST_PARTIES_ALL
WHERE vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND year_of_declaration = P_YEAR_OF_DECLARATION;
SELECT status_code
FROM je_it_list_hdr_all
WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
ANd year_of_declaration = p_year_of_declaration;
UPDATE JE_IT_LIST_HDR_ALL
SET STATUS_CODE = 'F'
WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
AND year_of_declaration = p_year_of_declaration;
| This procedure deletes all the rows from JE_IT_LIST_LINES_ALL, |
| JE_IT_LIST_PARTIES_ALL , JE_IT_LIST_HDR_ALL for a given period. |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description |
| ============ ============== ================================= |
| 03-Oct-2007 HBALIJEP Initial Version. |
| |
+===========================================================================*/
PROCEDURE Purge_trx_data(p_vat_reporting_entity_id IN NUMBER,P_YEAR_OF_DECLARATION IN NUMBER) IS
BEGIN
IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Start PROCEDURE Purge_trx_data');
DELETE JE_IT_LIST_LINES_ALL
WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
AND year_of_declaration = p_year_of_declaration;
DELETE JE_IT_LIST_PARTIES_ALL
WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
AND year_of_declaration = p_year_of_declaration;
DELETE JE_IT_LIST_HDR_ALL
WHERE vat_reporting_entity_id = p_vat_reporting_entity_id
AND year_of_declaration = p_year_of_declaration;
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','End PROCEDURE Purge_trx_data');
FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Exception in PROCEDURE Purge_trx_data');
| using bulk fetch and calls the procedure insert_tax_data |
| to insert data into the JE_IT_LIST_LINES table |
| Called from JE_IT_LISTING_PKG.Extract_Data() |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description |
| ============ ============== ================================= |
| 03-Oct-2007 HBALIJEP Initial Version. |
| |
+===========================================================================*/
PROCEDURE Fetch_trx_data_ap( P_VAT_REPORTING_ENTITY_ID IN NUMBER,
P_YEAR_OF_DECLARATION IN NUMBER,
P_VAT_REG IN VARCHAR2) IS
CURSOR trl_tax_data_csr
IS
SELECT ih.vendor_id , -- Supplier ID
ih.invoice_type_lookup_code, -- Invoice Type
NULL , -- Transaction Type ID - AR
ih.doc_sequence_id , -- Document sequence ID
ih.doc_sequence_value , -- Dcoument sequence value
ih.invoice_date , -- Invoice Date
ih.invoice_id , -- Invoice ID
ih.invoice_num , -- Invoice_Number
id.invoice_distribution_id , -- Invoive Tax Line Distribution ID
iditem.invoice_distribution_id , -- Invoive Item Line Distribution ID
il.line_type_lookup_code , -- Invoice Tax Line Type (allways TAX)
ilitem.line_type_lookup_code , -- Iteam Line - Line Type Lookup Code (always ITEM
il.tax_rate_id , -- Tax Rate ID
-- Tax Amount for other than Credit Memo Invoices
DECODE(ih.invoice_type_lookup_code,'CREDIT',0,
DECODE(ih.invoice_currency_code,gv_currency_code, id.amount, id.base_amount)) amount_tax,
-- Tax Amount for Credit Memo Invoices
DECODE(ih.invoice_type_lookup_code,'CREDIT',DECODE(ih.invoice_currency_code,gv_currency_code, id.amount, id.base_amount)
,0) cm_amount_tax,
-- Item Line Amount for other than Credit Memo Invoices
DECODE(ih.invoice_type_lookup_code,'CREDIT',0,
DECODE(ih.invoice_currency_code,gv_currency_code, iditem.amount, iditem.base_amount)) amount_item,
-- Item Line Amount for Credit Memo Invoices
DECODE(ih.invoice_type_lookup_code,'CREDIT',DECODE(ih.invoice_currency_code,gv_currency_code, iditem.amount, iditem.base_amount)
,0) cm_amount_item
FROM ap_invoices_all ih ,
ap_invoice_lines_all il ,
ap_invoice_distributions_all id ,
ap_invoice_lines_all ilitem ,
ap_invoice_distributions_all iditem ,
ap_suppliers pv ,
ap_supplier_sites_all pvs ,
jg_zz_vat_rep_entities repent ,
zx_rates_b zxrates ,
zx_taxes_b zxtaxes ,
zx_report_codes_assoc zxass ,
(SELECT distinct person_id
,national_identifier
FROM per_all_people_f
WHERE nvl(effective_end_date,sysdate) >= sysdate ) papf
WHERE repent.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND ( ( repent.entity_type_code = 'LEGAL'
AND ih.legal_entity_id = gn_legal_entity_id )
OR(repent.entity_type_code = 'ACCOUNTING'
AND repent.entity_level_code = 'LEDGER'
AND ih.set_of_books_id = gv_ledger_id)
OR(repent.entity_type_code = 'ACCOUNTING'
AND repent.entity_level_code = 'BSV'
AND ih.set_of_books_id = gv_ledger_id
AND get_bsv(id.dist_code_combination_id) = gv_balancing_segment_value )
)
AND ih.invoice_id = il.invoice_id
AND ih.invoice_id = id.invoice_id
AND il.line_number = id.invoice_line_number
AND id.posted_flag IN ('P', 'Y')
AND il.line_type_lookup_code = 'TAX'
AND ilitem.line_type_lookup_code = 'ITEM'
AND ih.invoice_id = ilitem.invoice_id
AND ih.invoice_id = iditem.invoice_id
AND ilitem.line_number = iditem.invoice_line_number
AND id.charge_applicable_to_dist_id = iditem.invoice_distribution_id
--In Case of Credit Memo Transaction, The following logic check credit memos lines issued
-- during the year but applied to invoices issued in the previous years.
AND ( ( ih.invoice_type_lookup_code <> 'CREDIT')
or( ih.invoice_type_lookup_code = 'CREDIT'
and EXISTS (SELECT 1
FROM ap_invoices_all tih
WHERe tih.invoice_id = id.parent_invoice_id
AND TO_CHAR(tih.invoice_date, 'YYYY') =TO_CHAR(add_months(gd_period_end_date,-12), 'YYYY') --bug 7031451
))
)
AND TO_CHAR(ih.invoice_date, 'YYYY') = TO_CHAR(gd_period_end_date, 'YYYY')
AND ih.vendor_id = pv.vendor_id
AND pvs.vendor_id = pv.vendor_id
AND pvs.tax_reporting_site_flag = 'Y'
AND pv.federal_reportable_flag = 'Y'
AND pvs.country = gv_vat_country_code
AND pv.employee_id = papf.person_id (+)
AND NVL(NVL(pvs.vat_registration_num, pv.vat_registration_num),'-99') <> gv_repent_trn --bug 7018923
AND ((P_VAT_REG = 'N') OR
(P_VAT_REG = 'Y' AND NVL(pvs.vat_registration_num, pv.vat_registration_num) IS NOT NULL))
AND il.tax_rate_id = zxrates.tax_rate_id
AND zxrates.content_owner_id = zxtaxes.content_owner_id
AND zxrates.tax_regime_code = zxtaxes.tax_regime_code
AND zxrates.tax = zxtaxes.tax
AND zxrates.tax_rate_id = zxass.entity_id(+)
AND zxass.entity_code(+) = 'ZX_RATES'
AND DECODE(zxtaxes.offset_tax_flag , 'Y', 'OFFSET',
DECODE(zxrates.def_rec_settlement_option_code, 'DEFERRED','DEFERRED',
zxass.REPORTING_CODE_CHAR_VALUE))<> 'CUSTOM BILL'
AND il.tax_rate_id IN (SELECT tax_rate_id
FROM je_it_setup_lines_all
WHERE year_of_declaration = P_YEAR_OF_DECLARATION
AND application_id = gn_ap_app_id
AND vat_reporting_entity_id = gn_legal_vat_rep_entity_id);
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Calling insert_tax_data');
SAVEPOINT before_insert_lines;
insert_tax_data(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,gn_ap_app_id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Number of AP records inserted into JE_IT_LIST_LINES :'||l_record_count);
ROLLBACK TO before_insert_lines;
| using bulk fetch and calls the procedure insert_tax_data |
| to insert data into the JE_IT_LIST_LINES table |
| Called from JE_IT_LISTING_PKG.Extract_Data() |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description |
| ============ ============== ================================= |
| 14-Dec-2007 spasupun Initial Version. |
| |
+===========================================================================*/
PROCEDURE Fetch_trx_data_ar( P_VAT_REPORTING_ENTITY_ID IN NUMBER,
P_YEAR_OF_DECLARATION IN NUMBER,
P_VAT_REG IN VARCHAR2) IS
CURSOR trl_tax_data_csr IS
SELECT
NVL(rth.sold_to_customer_id, rth.bill_to_customer_id), --PARTY_ID - Third party ID
rtp.type, --TRX_TYPE_CODE -Transaction_Type - AP --bug 7031451
rth.cust_trx_type_id, --TRX_TYPE_ID - Transaction Type ID - AR
rth.doc_sequence_id, --DOC_SEQ_NUM - Sequence_Number
rth.doc_sequence_value, --DOC_SEQ_VAL
rth.trx_date, --TRX_DATE - Invoice_Date
rth.customer_trx_id, --TRX_ID
rth.trx_number, --TRX_NUM - Invoice_Number --TRX_NUM - Invoice_Number
rcgl.cust_trx_line_gl_dist_id, --TAX_DIST_ID
rcglitem.cust_trx_line_gl_dist_id, --LINE_DIST_ID
rtl.line_type, --TRX_LINE_TYPE_CODE - Inv_Line_Type
rtlitem.line_type, --TRX_LINE_TYPE_CODE - Inv_Line_Type
rtl.vat_tax_id, --TRX_LINE_TAX_CODE_ID - Inv_Line_Tax_Code
DECODE(rtp.type,'CM',0,ROUND(rcgl.amount*NVL(rth.exchange_rate, 1),2)) amount_tax, --Inv_Line_Amt
DECODE(rtp.type,'CM',ROUND(rcgl.amount*NVL(rth.exchange_rate, 1),2),0) cm_amount_tax, --Inv_Line_Amt
DECODE(rtp.type,'CM',0,ROUND(rcglitem.amount*NVL(rth.exchange_rate, 1),2)) amount_item, --Inv_Line_Amt
decode(rtp.type,'CM',ROUND(rcglitem.amount*NVL(rth.exchange_rate, 1),2),0) cm_amount_item
FROM ra_customer_trx_all rth,
ra_customer_trx_lines_all rtl,
ra_cust_trx_line_gl_dist_all rcgl,
ra_customer_trx_lines_all rtlitem,
ra_cust_trx_line_gl_dist_all rcglitem,
hz_cust_site_uses_all hzcsu,
hz_cust_acct_sites_all hzcas,
hz_cust_accounts hzca,
hz_parties hzp,
jg_zz_vat_rep_entities repent,
zx_rates_b zxrates,
zx_taxes_b zxtaxes,
zx_report_codes_assoc zxass,
ra_cust_trx_types_all rtp
WHERE repent.vat_reporting_entity_id = p_vat_reporting_entity_id
AND ( ( repent.entity_type_code = 'LEGAL'
AND rth.legal_entity_id = gn_legal_entity_id )
OR( repent.entity_type_code = 'ACCOUNTING'
AND repent.entity_level_code = 'LEDGER'
AND rth.set_of_books_id = gv_ledger_id)
OR( repent.entity_type_code = 'ACCOUNTING'
AND repent.entity_level_code = 'BSV'
AND rth.set_of_books_id = gv_ledger_id
AND get_bsv(rcgl.code_combination_id) = gv_balancing_segment_value)
)
AND rtl.customer_trx_id = rth.customer_trx_id
AND TO_CHAR(rth.trx_date, 'YYYY') = TO_CHAR(gd_period_end_date, 'YYYY')
AND rcgl.customer_trx_id = rtl.customer_trx_id
AND rcgl.customer_trx_line_id = rtl.customer_trx_line_id
AND rtl.line_type = 'TAX'
AND rtlitem.customer_trx_id = rtl.customer_trx_id
AND rtlitem.line_type = 'LINE'
AND rtl.link_to_cust_trx_line_id = rtlitem.customer_trx_line_id (+)
AND rcglitem.customer_trx_line_id = rtlitem.customer_trx_line_id
AND rcglitem.customer_trx_id = rtlitem.customer_trx_id
AND rcgl.posting_control_id <> -3
AND NVL(rth.sold_to_customer_id, rth.bill_to_customer_id) = hzca.cust_account_id
AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
AND hzcas.cust_account_id = hzca.cust_account_id
AND hzca.party_id = hzp.party_id
AND upper(hzcsu.site_use_code) = 'LEGAL'
AND hzcsu.primary_flag = 'Y'
AND hzcsu.status = 'A'
AND hzp.country = gv_vat_country_code
AND NVL(decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),'-99') <> TO_CHAR(gv_repent_trn)
AND ((P_VAT_REG = 'N') OR
(P_VAT_REG = 'Y' AND decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference) IS NOT NULL))
AND rtl.vat_tax_id = zxrates.tax_rate_id
AND zxrates.content_owner_id = zxtaxes.content_owner_id
AND zxrates.tax_regime_code = zxtaxes.tax_regime_code
AND zxrates.tax = zxtaxes.tax
AND zxrates.tax_rate_id = zxass.entity_id(+)
AND zxass.entity_code(+) = 'ZX_RATES'
AND DECODE(zxtaxes.offset_tax_flag , 'Y', 'OFFSET', DECODE(zxrates.def_rec_settlement_option_code, 'DEFERRED','DEFERRED',zxass.REPORTING_CODE_CHAR_VALUE))<> 'CUSTOM BILL'
AND rtp.cust_trx_type_id = rth.cust_trx_type_id
AND rtp.org_id = rth.org_id --bug 7031451
AND ( (rtp.type <> 'CM') or
(rtp.type = 'CM'
and EXISTS(SELECT arct.customer_trx_id
FROM ar_receivable_applications_all arap,
ra_customer_trx_all arct
WHERE arap.customer_trx_id = rth.customer_trx_id
AND application_type ='CM'
AND arap.applied_customer_trx_id = arct.customer_trx_id
AND TO_CHAR(arct.trx_date, 'YYYY') = TO_CHAR(add_months(gd_period_end_date,-12), 'YYYY')
))
)
AND rtl.vat_tax_id IN (SELECT tax_rate_id FROM JE_IT_SETUP_LINES_ALL
WHERE vat_reporting_entity_id = gn_legal_vat_rep_entity_id
AND year_of_declaration = P_YEAR_OF_DECLARATION
AND application_id= gn_ar_app_id);
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ap','Calling insert_tax_data');
SAVEPOINT before_insert_lines;
insert_tax_data(P_VAT_REPORTING_ENTITY_ID,P_YEAR_OF_DECLARATION,gn_ar_app_id);
FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Fetch_trx_data_ar','Number of AR records inserted into JE_IT_LIST_LINES :'||l_record_count);
ROLLBACK TO before_insert_lines;
| This procedure inserts data into the JE_IT_LIST_LINES table |
| Called from JE_IT_LISTING_PKG.Fetch_trx_data_ap() and |
| JE_IT_LISTING_PKG.Fetch_trx_data_ar() |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Date Author Description |
| ============ ============== ================================= |
| 14-Dec-2007 spasupun Initial Version. |
| |
+===========================================================================*/
PROCEDURE Insert_tax_data( P_VAT_REPORTING_ENTITY_ID IN NUMBER,
P_YEAR_OF_DECLARATION IN NUMBER,
P_APP_ID IN NUMBER) IS
--Variable for Tax Line
v_taxable_t NUMBER;
SELECT listing_column_code
FROM je_it_setup_lines_all
WHERE vat_reporting_entity_id=P_VAT_REPORTING_ENTITY_ID
AND year_of_declaration = P_YEAR_OF_DECLARATION
AND application_id = P_APP_ID
AND tax_rate_id = P_TAX_RATE_ID;
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Start PROCEDURE Insert_tax_data');
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Parameters are :');
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','p_vat_reporting_entity_id ='||p_vat_reporting_entity_id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','p_year_of_declaration ='||p_year_of_declaration);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JE_IT_LISTING_PKG.Insert_tax_data','p_app_id ='||p_app_id);
SELECT 'Y' INTO available_flag FROM JE_IT_LIST_LINES_ALL
WHERE
VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
AND YEAR_OF_DECLARATION = P_YEAR_OF_DECLARATION
AND APPLICATION_ID = p_app_id
AND PARTY_ID = gt_party_id(i)
AND TRX_DIST_ID = gt_trx_line_dist_id(i);
-- ITEM line insertion
INSERT INTO JE_IT_LIST_LINES_ALL(
VAT_REPORTING_ENTITY_ID,
YEAR_OF_DECLARATION,
APPLICATION_ID,
PARTY_ID,
TRX_TYPE_CODE,
TRX_TYPE_ID,
DOC_SEQ_ID,
DOC_SEQ_NUM,
TRX_DATE,
TRX_ID,
TRX_NUM,
TRX_DIST_ID,
TRX_LINE_TYPE_CODE,
TAX_RATE_ID,
TAXABLE_AMT,
VAT_AMT,
NON_TAXABLE_AMT,
EXEMPT_AMT,
TAXABLE_VAT_AMT,
TAXABLE_VAT_INV_AMT,
CM_TAXABLE_AMT,
CM_VAT_AMT,
CM_NON_TAXABLE_AMT,
CM_EXEMPT_AMT,
CM_TAXABLE_VAT_AMT,
CM_TAXABLE_VAT_INV_AMT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
VALUES (P_VAT_REPORTING_ENTITY_ID,
P_YEAR_OF_DECLARATION,
p_app_id, --APPLICATION_ID
gt_party_id(i), --PARTY_ID
gt_trx_type_code(i), --TRX_TYPE_CODE
gt_trx_type_id(i), --TRX_TYPE_ID
gt_doc_seq_num(i), --DOC_SEQ_ID
gt_doc_seq_val(i), --DOC_SEQ_NUM
gt_trx_date(i), --TRX_DATE
gt_trx_id(i), --TRX_ID
gt_trx_num(i), --TRX_NUM
gt_trx_line_dist_id(i), --TRX_DIST_ID
gt_trx_line_type_code(i), --TRX_LINE_TYPE_CODE
gt_trx_line_tax_rate_id(i), --TRX_LINE_TAX_CODE_ID
v_taxable_l, --TAXABLE_AMT
v_vat_l, --VAT_AMT
v_non_taxable_l, --NON_TAXABLE_AMT
v_exempt_l, --EXEMPT_AMT
v_tax_vat_l, --TAXABLE_VAT_AMT
v_tax_vat_inv_l, --TAXABLE_VAT_INV_AMT
v_taxable_cm_l, --TAXABLE_AMT
v_vat_cm_l, --VAT_AMT
v_non_taxable_cm_l, --NON_TAXABLE_AMT
v_exempt_cm_l, --EXEMPT_AMT
v_tax_vat_cm_l, --TAXABLE_VAT_AMT
v_tax_vat_inv_cm_l, --TAXABLE_VAT_INV_AMT
g_last_update_date, --LAST_UPDATE_DATE
g_last_updated_by, --LAST_UPDATED_BY
g_last_update_login, --LAST_UPDATE_LOGIN
g_creation_date, --CREATION_DATE
g_created_by); --CREATED_BY
-- tax line insertion
INSERT INTO JE_IT_LIST_LINES_ALL(
VAT_REPORTING_ENTITY_ID,
YEAR_OF_DECLARATION,
APPLICATION_ID,
PARTY_ID,
TRX_TYPE_CODE,
TRX_TYPE_ID,
DOC_SEQ_ID,
DOC_SEQ_NUM,
TRX_DATE,
TRX_ID,
TRX_NUM,
TRX_DIST_ID,
TRX_LINE_TYPE_CODE,
TAX_RATE_ID,
TAXABLE_AMT,
VAT_AMT,
NON_TAXABLE_AMT,
EXEMPT_AMT,
TAXABLE_VAT_AMT,
TAXABLE_VAT_INV_AMT,
CM_TAXABLE_AMT,
CM_VAT_AMT,
CM_NON_TAXABLE_AMT,
CM_EXEMPT_AMT,
CM_TAXABLE_VAT_AMT,
CM_TAXABLE_VAT_INV_AMT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
VALUES (P_VAT_REPORTING_ENTITY_ID,
P_YEAR_OF_DECLARATION,
p_app_id, --APPLICATION_ID
gt_party_id(i), --PARTY_ID
gt_trx_type_code(i), --TRX_TYPE_CODE
gt_trx_type_id(i), --TRX_TYPE_ID
gt_doc_seq_num(i), --DOC_SEQ_ID
gt_doc_seq_val(i), --DOC_SEQ_NUM
gt_trx_date(i), --TRX_DATE
gt_trx_id(i), --TRX_ID
gt_trx_num(i), --TRX_NUM
gt_trx_tax_dist_id(i), --TRX_DIST_ID
gt_trx_tax_line_type_code(i), --TRX_LINE_TYPE_CODE
gt_trx_line_tax_rate_id(i), --TRX_LINE_TAX_CODE_ID
v_taxable_t, --TAXABLE_AMT
v_vat_t, --VAT_AMT
v_non_taxable_t, --NON_TAXABLE_AMT
v_exempt_t, --EXEMPT_AMT
v_tax_vat_t, --TAXABLE_VAT_AMT
v_tax_vat_inv_t, --TAXABLE_VAT_INV_AMT
v_taxable_cm_t, --TAXABLE_AMT
v_vat_cm_t, --VAT_AMT
v_non_taxable_cm_t, --NON_TAXABLE_AMT
v_exempt_cm_t, --EXEMPT_AMT
v_tax_vat_cm_t, --TAXABLE_VAT_AMT
v_tax_vat_inv_cm_t, --TAXABLE_VAT_INV_AMT
g_last_update_date, --LAST_UPDATE_DATE
g_last_updated_by, --LAST_UPDATED_BY
g_last_update_login, --LAST_UPDATE_LOGIN
g_creation_date, --CREATION_DATE
g_created_by); --CREATED_BY
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','End PROCEDURE Insert_tax_data');
g_errbuf :='Exception in JE_IT_LISTING_PKG.Insert_tax_data';
FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Insert_tax_data','Exception in PROCEDURE Insert_tax_data');
END Insert_tax_data;
gt_party_id.DELETE;
gt_trx_type_code.DELETE;
gt_trx_type_id.DELETE;
gt_doc_seq_num.DELETE;
gt_doc_seq_val.DELETE;
gt_trx_date.DELETE;
gt_trx_id.DELETE;
gt_trx_num.DELETE;
gt_trx_line_dist_id.DELETE;
gt_trx_tax_dist_id.DELETE;
gt_trx_line_type_code.DELETE;
gt_trx_tax_line_type_code.DELETE;
gt_trx_line_tax_rate_id.DELETE;
gt_inv_line_amount.DELETE;
gt_inv_tax_line_amount.DELETE;
gt_inv_line_amount_cm.DELETE;
gt_inv_tax_line_amount_cm.DELETE;
gt_trx_type.DELETE;
gt_party_vat_reg_num.DELETE;
gt_party_fiscal_id_num.DELETE;
FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_IT_LISTING_PKG.Init_gt_variables','Exception in PROCEDURE Insert_tax_data');
SELECT jit.application_id application_id,
jit.PARTY_ID party_id, --PARTY_ID
DECODE(P_CUST_SORT_COL,'C',hzp.party_name,'T',hzp.jgzz_fiscal_code
,'R',DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference)) sort_column,
SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
hzp.jgzz_fiscal_code tax_payer_id, -- Customer Tax Payer ID
DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference) vat_reg_num-- Customer Tax Registration Number
FROM JE_IT_LIST_LINES_ALL jit,
hz_cust_site_uses_all hzcsu,
hz_cust_acct_sites_all hzcas,
hz_cust_accounts hzca,
hz_parties hzp
WHERE jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jit.year_of_declaration = P_YEAR_OF_DECLARATION
AND jit.APPLICATION_ID = 222
AND jit.party_id = hzca.cust_account_id
AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
AND hzcas.cust_account_id = hzca.cust_account_id
AND hzca.party_id = hzp.party_id
AND upper(hzcsu.site_use_code) = 'LEGAL'
AND hzcsu.primary_flag = 'Y'
AND hzcsu.status = 'A'
AND hzp.country = gv_vat_country_code
AND NVL(decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),'-99') <> TO_CHAR(gv_repent_trn)
GROUP BY jit.PARTY_ID,
jit.APPLICATION_ID,
hzp.jgzz_fiscal_code,
DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),
DECODE(P_CUST_SORT_COL,'C',hzp.party_name,'T',hzp.jgzz_fiscal_code
,'R',DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference))
UNION ALL
SELECT jit.APPLICATION_ID application_id,
jit.PARTY_ID party_id, --PARTY_ID
DECODE(P_VEND_SORT_COL,'V',pv.vendor_name,'T',NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)),
'R',NVL(pvs.vat_registration_num, pv.vat_registration_num)) sort_column,
SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)), --Supplier Tax Payer ID
NVL(pvs.vat_registration_num, pv.vat_registration_num) --Supplier Tax Registration Number
FROM je_it_list_lines_all jit,
ap_suppliers pv,
ap_supplier_sites_all pvs,
(SELECT distinct person_id
,national_identifier
FROM per_all_people_f
WHERE nvl(effective_end_date,sysdate) >= sysdate ) papf
WHERE jit.year_of_declaration = P_YEAR_OF_DECLARATION
AND jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jit.APPLICATION_ID = 200
AND jit.party_id = pv.vendor_id
AND pvs.vendor_id = pv.vendor_id
AND pvs.tax_reporting_site_flag = 'Y'
AND pv.federal_reportable_flag = 'Y'
AND pvs.country = gv_vat_country_code
AND NVL(NVL(pvs.vat_registration_num, pv.vat_registration_num),'-99') <> gv_repent_trn
AND pv.employee_id = papf.person_id (+)
GROUP BY jit.PARTY_ID,
jit.APPLICATION_ID,
NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)), --Supplier Tax Payer ID
NVL(pvs.vat_registration_num, pv.vat_registration_num),
DECODE(P_VEND_SORT_COL,'V',pv.vendor_name,'T',NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)),
'R',NVL(pvs.vat_registration_num, pv.vat_registration_num))
ORDER BY application_id DESC,sort_column;
SELECT *
FROM
(
SELECT COLLECTION.*,
hzp.party_name party_name
FROM
(SELECT jit.application_id application_id,
MAX(jit.PARTY_ID) party_id, --MAX PARTY_ID
SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
hzp.jgzz_fiscal_code tax_payer_id, -- Customer Tax Payer ID
DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference) vat_reg_num-- Customer Tax Registration Number
FROM JE_IT_LIST_LINES_ALL jit,
hz_cust_site_uses_all hzcsu,
hz_cust_acct_sites_all hzcas,
hz_cust_accounts hzca,
hz_parties hzp
WHERE jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jit.year_of_declaration = P_YEAR_OF_DECLARATION
AND jit.APPLICATION_ID = 222
AND jit.party_id = hzca.cust_account_id
AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
AND hzcas.cust_account_id = hzca.cust_account_id
AND hzca.party_id = hzp.party_id
AND upper(hzcsu.site_use_code) = 'LEGAL'
AND hzcsu.primary_flag = 'Y'
AND hzcsu.status = 'A'
AND hzp.country = gv_vat_country_code
AND NVL(decode(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference),'-99') <> TO_CHAR(gv_repent_trn)
GROUP BY jit.APPLICATION_ID, hzp.jgzz_fiscal_code, DECODE(hzcsu.tax_reference, null,hzp.tax_reference,hzcsu.tax_reference)
) COLLECTION,
hz_cust_accounts hzca,
hz_parties hzp
WHERE
COLLECTION.party_id = hzca.cust_account_id
AND hzca.party_id = hzp.party_id
UNION ALL
SELECT COLLECTION.*,
pv.vendor_name party_name
FROM
(SELECT jit.APPLICATION_ID application_id,
MAX(jit.PARTY_ID) party_id, --MAX PARTY_ID
SUM(jit.TAXABLE_AMT) tot_taxable_amt, --TAXABLE_AMT
SUM(jit.VAT_AMT) tot_vat_amt, --VAT_AMT
SUM(jit.NON_TAXABLE_AMT) tot_non_taxable_amt, --NON_TAXABLE_AMT
SUM(jit.EXEMPT_AMT) tot_exempt_amt, --EXEMPT_AMT
SUM(jit.TAXABLE_VAT_AMT) tot_taxable_vat_amt, --TAXABLE_VAT_AMT
SUM(jit.TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
SUM(jit.CM_TAXABLE_AMT) tot_taxable_amt_cm, --TAXABLE_AMT
SUM(jit.CM_VAT_AMT) tot_vat_amt_cm, --VAT_AMT
SUM(jit.CM_NON_TAXABLE_AMT) tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
SUM(jit.CM_EXEMPT_AMT) tot_exempt_amt_cm, --EXEMPT_AMT
SUM(jit.CM_TAXABLE_VAT_AMT) tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
SUM(jit.CM_TAXABLE_VAT_INV_AMT) tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)) tax_payer_id, --Supplier Tax Payer ID
NVL(pvs.vat_registration_num, pv.vat_registration_num) vat_reg_num --Supplier Tax Registration Number
FROM je_it_list_lines_all jit,
ap_suppliers pv,
ap_supplier_sites_all pvs,
(SELECT distinct person_id
,national_identifier
FROM per_all_people_f
WHERE nvl(effective_end_date,sysdate) >= sysdate ) papf
WHERE jit.year_of_declaration = P_YEAR_OF_DECLARATION
AND jit.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jit.APPLICATION_ID = 200
AND jit.party_id = pv.vendor_id
AND pvs.vendor_id = pv.vendor_id
AND pvs.tax_reporting_site_flag = 'Y'
AND pv.federal_reportable_flag = 'Y'
AND pvs.country = gv_vat_country_code
AND NVL(NVL(pvs.vat_registration_num, pv.vat_registration_num),'-99') <> gv_repent_trn
AND pv.employee_id = papf.person_id (+)
GROUP BY jit.APPLICATION_ID,
NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)),
NVL(pvs.vat_registration_num, pv.vat_registration_num)
) COLLECTION,
ap_suppliers pv
WHERE
COLLECTION.party_id = pv.vendor_id
) OUTERQ
ORDER BY OUTERQ.application_id DESC,
DECODE (OUTERQ.application_id,
222,
DECODE(P_CUST_SORT_COL,'C',OUTERQ.party_name,'T',OUTERQ.tax_payer_id,'R',OUTERQ.vat_reg_num),
200,
DECODE(P_VEND_SORT_COL,'V',OUTERQ.party_name,'T',OUTERQ.tax_payer_id,'R',OUTERQ.vat_reg_num));
SAVEPOINT before_insert_parties;
INSERT INTO JE_IT_LIST_PARTIES_ALL
(VAT_REPORTING_ENTITY_ID,
YEAR_OF_DECLARATION,
TRANSMISSION_NUM,
APPLICATION_ID,
PARTY_ID,
TAXABLE_AMT,
VAT_AMT,
NON_TAXABLE_AMT,
EXEMPT_AMT,
TAXABLE_VAT_AMT,
TAXABLE_VAT_INV_AMT,
CM_TAXABLE_AMT,
CM_VAT_AMT,
CM_NON_TAXABLE_AMT,
CM_EXEMPT_AMT,
CM_TAXABLE_VAT_AMT,
CM_TAXABLE_VAT_INV_AMT,
FISCAL_ID_NUM,
VAT_REGISTRATION_NUM,
PARTY_SEQUENCE_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
VALUES
(P_VAT_REPORTING_ENTITY_ID,
P_YEAR_OF_DECLARATION,
l_transnum, --TRANSMISSION_NUM
rec_lines.APPLICATION_ID, --APPLICATION_ID
rec_lines.PARTY_ID, --PARTY_ID
rec_lines.tot_taxable_amt, --TAXABLE_AMT
rec_lines.tot_vat_amt, --VAT_AMT
rec_lines.tot_non_taxable_amt, --NON_TAXABLE_AMT
rec_lines.tot_exempt_amt, --EXEMPT_AMT
rec_lines.tot_taxable_vat_amt, --TAXABLE_VAT_AMT
rec_lines.tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
rec_lines.tot_taxable_amt_cm, --TAXABLE_AMT
rec_lines.tot_vat_amt_cm, --VAT_AMT
rec_lines.tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
rec_lines.tot_exempt_amt_cm, --EXEMPT_AMT
rec_lines.tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
rec_lines.tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
rec_lines.tax_payer_id,
rec_lines.vat_reg_num,
l_seq_num,
g_last_update_date, --LAST_UPDATE_DATE
g_last_updated_by, --LAST_UPDATED_BY
g_last_update_login, --LAST_UPDATE_LOGIN
g_creation_date, --CREATION_DATE
g_created_by --CREATED_BY
);
INSERT INTO JE_IT_LIST_PARTIES_ALL
(VAT_REPORTING_ENTITY_ID,
YEAR_OF_DECLARATION,
TRANSMISSION_NUM,
APPLICATION_ID,
PARTY_ID,
TAXABLE_AMT,
VAT_AMT,
NON_TAXABLE_AMT,
EXEMPT_AMT,
TAXABLE_VAT_AMT,
TAXABLE_VAT_INV_AMT,
CM_TAXABLE_AMT,
CM_VAT_AMT,
CM_NON_TAXABLE_AMT,
CM_EXEMPT_AMT,
CM_TAXABLE_VAT_AMT,
CM_TAXABLE_VAT_INV_AMT,
FISCAL_ID_NUM,
VAT_REGISTRATION_NUM,
PARTY_SEQUENCE_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY
)
VALUES
(P_VAT_REPORTING_ENTITY_ID,
P_YEAR_OF_DECLARATION,
l_transnum, --TRANSMISSION_NUM
rec_lines.APPLICATION_ID, --APPLICATION_ID
rec_lines.PARTY_ID, --PARTY_ID
rec_lines.tot_taxable_amt, --TAXABLE_AMT
rec_lines.tot_vat_amt, --VAT_AMT
rec_lines.tot_non_taxable_amt, --NON_TAXABLE_AMT
rec_lines.tot_exempt_amt, --EXEMPT_AMT
rec_lines.tot_taxable_vat_amt, --TAXABLE_VAT_AMT
rec_lines.tot_taxable_vat_inv_amt, --TAXABLE_VAT_INV_AMT
rec_lines.tot_taxable_amt_cm, --TAXABLE_AMT
rec_lines.tot_vat_amt_cm, --VAT_AMT
rec_lines.tot_non_taxable_amt_cm, --NON_TAXABLE_AMT
rec_lines.tot_exempt_amt_cm, --EXEMPT_AMT
rec_lines.tot_taxable_vat_amt_cm, --TAXABLE_VAT_AMT
rec_lines.tot_taxable_vat_inv_amt_cm, --TAXABLE_VAT_INV_AMT
rec_lines.tax_payer_id,
rec_lines.vat_reg_num,
l_seq_num,
g_last_update_date, --LAST_UPDATE_DATE
g_last_updated_by, --LAST_UPDATED_BY
g_last_update_login, --LAST_UPDATE_LOGIN
g_creation_date, --CREATION_DATE
g_created_by --CREATED_BY
);
INSERT INTO JE_IT_LIST_HDR_ALL(
VAT_REPORTING_ENTITY_ID,
YEAR_OF_DECLARATION,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
VALUES
(P_VAT_REPORTING_ENTITY_ID,
P_YEAR_OF_DECLARATION,
'P',
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_creation_date,
g_created_by);
ROLLBACK TO before_insert_parties;
SELECT repent.ledger_id,
repent.balancing_segment_value,
gl.chart_of_accounts_id
FROM jg_zz_vat_rep_entities repent
,gl_ledgers gl
WHERE vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND gl.ledger_id = repent.ledger_id;
SELECT nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
,nvl(cfg.tax_registration_number,cfgd.tax_registration_number) repent_trn
,min(glp.start_date)
,max(glp.end_date)
FROM jg_zz_vat_rep_entities cfg
,jg_zz_vat_rep_entities cfgd
,gl_periods glp
WHERE cfg.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
and (
( cfg.entity_type_code = 'ACCOUNTING'
and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
)
or
( cfg.entity_type_code = 'LEGAL'
and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
)
)
AND glp.period_set_name = nvl(cfg.tax_calendar_name,cfgd.tax_calendar_name)
AND glp.period_year = P_YEAR_OF_DECLARATION
GROUP BY nvl(cfg.legal_entity_id,cfgd.legal_entity_id)
,nvl(cfg.tax_registration_number,cfgd.tax_registration_number)
,nvl(cfg.entity_identifier,cfgd.entity_identifier);
SELECT gllev.currency_code
,hl.country
FROM gl_ledger_le_v gllev
,gl_ledgers gl
,xle_registrations xr
,xle_entity_profiles xep
,hr_locations_all hl
WHERE gllev.ledger_category_code='PRIMARY'
AND gllev.legal_entity_id = gn_legal_entity_id
AND gl.ledger_id = gllev.ledger_id
AND xep.legal_entity_id = gllev.legal_entity_id
AND xr.source_id = xep.legal_entity_id
AND xr.source_table = 'XLE_ENTITY_PROFILES'
AND xr.location_id = hl.location_id
AND xr.identifying_flag = 'Y';
g_last_updated_by := NVL(fnd_profile.value('USER_ID'),1);
g_last_update_date := SYSDATE;
g_last_update_login := 1;
SELECT application_column_name
INTO l_segment
FROM fnd_segment_attribute_values ,
gl_ledgers gl
WHERE id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_BALANCING'
AND application_id = 101
AND gl.chart_of_accounts_id = gv_chart_of_accounts_id
AND gl.ledger_id = gv_ledger_id;
EXECUTE IMMEDIATE 'SELECT '||l_segment ||
' FROM gl_code_combinations '||
' WHERE code_combination_id = '||ccid
INTO bal_segment_value;