[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
C_LINES_PER_INSERT CONSTANT NUMBER := 1000;
g_last_updated_by number(15);
g_last_update_date date;
g_last_update_login number(15);
PROCEDURE INSERT_GL_SUB_ITF;
| INSERT_TAX_DATA |
| |
| DESCRIPTION |
| This procedure takes the input parameters from ZX_EXTRACT_PKG |
| and builds dynamic SQL statement clauses based on the parameters, |
| supplies them as output parameters. |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 18-Aug-99 Nilesh Patel, created |
| |
+===========================================================================*/
PROCEDURE INSERT_TAX_DATA (
P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
) IS
BEGIN
g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_TAX_DATA.BEGIN',
'ZX_GL_EXTRACT_PKG: INSERT_TAX_DATA(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_TAX_DATA.END',
'ZX_GL_EXTRACT_PKG: INSERT_TAX_DATA(+)');
END INSERT_TAX_DATA;
SELECT CHART_OF_ACCOUNTS_ID
INTO L_CHART_OF_ACCOUNTS_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
' SELECT
gjh.ledger_id,
gjh.je_header_id,
gjh.doc_sequence_id,
gjh.doc_sequence_value,
seq.name,
gjl1.tax_code_id,
to_number(NULL), --tax line id
decode(gjl1.tax_type_code,''I'',-1,1) *
ZX_GL_EXTRACT_PKG.prorated_tax(
gjh.je_header_id,
gjh.ledger_id,
gjh.doc_sequence_id,
gjl1.tax_code_id,
gjl2.code_combination_id,
gjl1.tax_document_date,
gjl1.tax_type_code,--zx_rate.tax_class, --Bug 5338150
gjl1.tax_document_identifier,
gjl1.tax_customer_name,
gjl1.tax_customer_reference,
gjl1.tax_registration_number,
seq.name,
''EAM''),
decode(gjl1.tax_type_code,''I'',-1,1) *
ZX_GL_EXTRACT_PKG.prorated_tax(
gjh.je_header_id,
gjh.ledger_id,
gjh.doc_sequence_id,
gjl1.tax_code_id,
gjl2.code_combination_id,
gjl1.tax_document_date,
gjl1.tax_type_code,--zx_rate.tax_class,--Bug 5338150
gjl1.tax_document_identifier,
gjl1.tax_customer_name,
gjl1.tax_customer_reference,
gjl1.tax_registration_number,
seq.name,
''AAM''), -- tax accounted amount
to_number(NULL), --tax line number
(sum(nvl(gjl1.entered_cr,0) - nvl(gjl1.entered_dr,0)))*
decode(gjl1.tax_type_code,''I'',-1,1), --taxable entered amount
(sum(nvl(gjl1.accounted_cr,0) - nvl(gjl1.accounted_dr,0)))*
decode(gjl1.tax_type_code,''I'',-1,1), --taxable accounted amount
-- min(gjl1.je_line_num), --acctg_dist_id
-- gjl2.code_combination_id,
sum(gjl1.stat_amount),'|| --taxable_line_qty
-- gjh.period_name,'||
'TO_DATE(NULL),'|| --gjl1.tax_document_identifier,
'gjh.description,
gjh.currency_code,
TO_DATE(NULL), --gjl1.tax_document_date,
TO_DATE(NULL), --gjl1.tax_document_date,
gjh.currency_conversion_type,
gjh.currency_conversion_date,
gjh.currency_conversion_rate,
TO_CHAR(NULL), --gjl1.context,
TO_CHAR(NULL), --gjl1.attribute1,
TO_CHAR(NULL), --gjl1.attribute2,
TO_CHAR(NULL), --gjl1.attribute3,
TO_CHAR(NULL), --gjl1.attribute4,
TO_CHAR(NULL), --gjl1.attribute5,
TO_CHAR(NULL), --gjl1.attribute6,
TO_CHAR(NULL), --gjl1.attribute7,
TO_CHAR(NULL), --gjl1.attribute8,
TO_CHAR(NULL), --gjl1.attribute9,
TO_CHAR(NULL), --gjl1.attribute10,
TO_CHAR(NULL), --gjl1.attribute11,
TO_CHAR(NULL), --gjl1.attribute12,
TO_CHAR(NULL), --gjl1.attribute13,
TO_CHAR(NULL), --gjl1.attribute14,
TO_CHAR(NULL), --gjl1.attribute15,
TO_CHAR(NULL), --gjl1.tax_customer_name,
TO_CHAR(NULL), --gjl1.tax_customer_reference,
TO_CHAR(NULL), --gjl1.tax_registration_number,
decode(gjh.posted_date,NULL,''N'',''Y''),
zx_rate.tax_rate_code,
zx_rate.description,
zx_rate.PERCENTAGE_RATE,
zx_rate.vat_transaction_type_code,
gjl1.tax_type_code, --zx_tax.tax_type_code,
zx_rate.tax_rate_name,
--zx_rate.tax_rate_register_type_code,
zx_rate.tax_regime_code,
zx_rate.tax,
zx_rate.tax_jurisdiction_code,
zx_rate.tax_status_code,
TO_CHAR(NULL), --tax_currency_code
TO_NUMBER(NULL), --tax_amt_tax_curr
TO_NUMBER(NULL), --taxable_amt_tax_curr
TO_NUMBER(NULL), --orig_taxable_amt
TO_NUMBER(NULL), --orig_taxable_amt_tax_curr
TO_NUMBER(NULL), --orig_tax_amt
TO_NUMBER(NULL), --orig_tax_amt_tax_curr
TO_NUMBER(NULL), --precision
TO_NUMBER(NULL), --minimum_accountable_unit
-- TO_CHAR(NULL), --functional_currency_code
TO_NUMBER(NULL), --gjl1.je_line_num,
TO_NUMBER(NULL), --gjl1.je_line_num,
TO_CHAR(NULL), --gjl1.description,
TO_CHAR(NULL), --gjl1.line_type_code,
TO_NUMBER(NULL), --establishment id
TO_NUMBER(NULL), --internal organization id
--NULL,
''GL'',
NULL,
TO_CHAR(NULL), -- gjl1.invoice_identifier,
gjl2.code_combination_id,
gjh.period_name ,
TO_DATE(NULL)'; --gjl1.effective_date
' SELECT
gjh.ledger_id,
gjh.je_header_id,
gjh.doc_sequence_id,
gjh.doc_sequence_value,
seq.name,
gjl1.tax_code_id,
gjl2.je_line_num,
(sum(nvl(gjl2.entered_cr,0) - nvl(gjl2.entered_dr,0)))*
decode(gjl1.tax_type_code,''I'',-1,1),
(sum(nvl(gjl2.accounted_cr,0) - nvl(gjl2.accounted_dr,0)))*
decode(gjl1.tax_type_code,''I'',-1,1),
gjl2.je_line_num,
(sum(nvl(gjl1.entered_cr,0) - nvl(gjl1.entered_dr,0)))*
decode(gjl1.tax_type_code,''I'',-1,1),
(sum(nvl(gjl1.accounted_cr,0) - nvl(gjl1.accounted_dr,0)))*
decode(gjl1.tax_type_code,''I'',-1,1),
-- min(gjl1.je_line_num),
-- gjl2.code_combination_id,
sum(gjl1.stat_amount),'||
-- gjh.period_name, '||
'gjl1.tax_document_identifier,
gjh.description,
gjh.currency_code,
gjl1.tax_document_date,
gjl1.tax_document_date,
gjh.currency_conversion_type,
gjh.currency_conversion_date,
gjh.currency_conversion_rate,
gjl1.context,
gjl1.attribute1,
gjl1.attribute2,
gjl1.attribute3,
gjl1.attribute4,
gjl1.attribute5,
gjl1.attribute6,
gjl1.attribute7,
gjl1.attribute8,
gjl1.attribute9,
gjl1.attribute10,
gjl1.attribute11,
gjl1.attribute12,
gjl1.attribute13,
gjl1.attribute14,
gjl1.attribute15,
gjl1.tax_customer_name,
gjl1.tax_customer_reference,
gjl1.tax_registration_number,
decode(gjh.posted_date,NULL,''N'',''Y''),
zx_rate.tax_rate_code,
zx_rate.description,
zx_rate.PERCENTAGE_RATE,
zx_rate.vat_transaction_type_code,
gjl1.tax_type_code, --zx_tax.tax_type_code,
zx_rate.tax_rate_name,
--zx_rate.tax_rate_register_type_code, --Check this
zx_rate.tax_regime_code,
zx_rate.tax,
zx_rate.tax_jurisdiction_code,
zx_rate.tax_status_code,
NULL, --tax_currency_code
NULL, --tax_amt_tax_curr
NULL, --taxable_amt_tax_curr
NULL, --orig_taxable_amt
NULL, --orig_taxable_amt_tax_curr
NULL, --orig_tax_amt
NULL, --orig_tax_amt_tax_curr
NULL, --precision
NULL, --minimum_accountable_unit
-- NULL, --functional_currency_code
gjl1.je_line_num,
gjl1.je_line_num,
gjl1.description,
gjl1.line_type_code,
NULL, --establishment id
NULL, --internal organization id
-- NULL,
''GL'',
NULL,
gjl1.invoice_identifier,
gjl2.code_combination_id,
gjh.period_name ,
gjl1.effective_date ';
select sum((nvl(GJL.ENTERED_CR,0)-nvl(GJL.ENTERED_DR,0)))
from GL_JE_LINES GJL
where gjl.je_header_id = p_trx_id and
gjl.tax_group_id = p_tax_group_id and
gjl.tax_code_id is not null;
select SUM(nvl(GJL.ENTERED_CR,0))
from GL_JE_LINES GJL
where gjl.je_header_id = p_trx_id and
gjl.tax_group_id = p_tax_group_id and
gjl.tax_code_id is not null;
select GJL2.ENTERED_DR EDR,
GJL2.ENTERED_CR ECR,
(nvl(GJL2.ENTERED_CR,0)-nvl(GJL2.ENTERED_DR,0)) EAM,
GJL2.ACCOUNTED_DR ADR,
GJL2.ACCOUNTED_CR ACR,
(nvl(GJL2.ACCOUNTED_CR,0)-nvl(GJL2.ACCOUNTED_DR,0)) AAM,
GJL1.TAX_GROUP_ID TAX_GROUP_ID,
(nvl(GJL1.ENTERED_CR,0)-nvl(GJL1.ENTERED_DR,0)) ETAXABLEAM
from FND_DOCUMENT_SEQUENCES SEQ,
GL_JE_LINES GJL2,
-- AR_AP_TAX_CODES_V TAX,
ZX_RATES_VL ZX_RATE,
GL_JE_LINES GJL1,
GL_JE_HEADERS GJH
where gjh.je_header_id = p_trx_id and
gjh.je_header_id = gjl1.je_header_id and
gjl1.tax_code_id = NVL(ZX_RATE.SOURCE_ID,ZX_RATE.TAX_RATE_ID)
AND ((zx_rate.tax_class in ('OUTPUT','INPUT')) or
(zx_rate.tax_class is null))
AND gjh.je_header_id = gjl2.je_header_id and
gjl2.tax_group_id = gjl1.tax_group_id and
gjl2.tax_code_id is null and
gjh.doc_sequence_id = seq.doc_sequence_id(+) and
((gjh.ledger_id = p_ledger_id) or
(gjh.ledger_id is null and p_ledger_id is null)) and
((gjh.doc_sequence_id = p_doc_seq_id) or
(gjh.doc_sequence_id is null and p_doc_seq_id is null)) and
((seq.name = p_seq_name ) or
(seq.name is null and p_seq_name is null)) and
((gjl1.tax_code_id = p_tax_code_id) or
(gjl1.tax_code_id is null and p_tax_code_id is null)) and
((gjl1.tax_type_code = p_tax_class) or
(gjl1.tax_type_code is null and p_tax_class is null)) and
((gjl1.tax_document_identifier = p_tax_doc_identifier) or
(gjl1.tax_document_identifier is null and p_tax_doc_identifier is null)) and
((gjl1.tax_document_date = p_tax_doc_date) or
(gjl1.tax_document_date is null and p_tax_doc_date is null)) and
((gjl1.tax_customer_name = p_tax_cust_name) or
(gjl1.tax_customer_name is null and p_tax_cust_name is null)) and
((gjl1.tax_customer_reference = p_tax_cust_reference) or
(gjl1.tax_customer_reference is null and p_tax_cust_reference is null)) and
((gjl1.tax_registration_number = p_tax_reg_number) or
(gjl1.tax_registration_number is null and p_tax_reg_number is null)) and
((gjl2.code_combination_id = p_code_comb_id) or
(gjl2.code_combination_id is null and p_code_comb_id is null));
| INSERT_GL_SUB_ITF |
| DESCRIPTION |
| This procedure inserts GL data into AR_TAX_EXTRACT_SUB_ITF table |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
+===========================================================================*/
PROCEDURE INSERT_GL_SUB_ITF
IS
m NUMBER;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
'INSERT_GL_SUB_ITF(+)');
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
'Taxable Amt '||to_char(GT_TAXABLE_AMT_FUNCL_CURR(m)));
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
'Tax Amt '||to_char(GT_TAX_AMT(m)));
INSERT into zx_rep_trx_detail_t
(application_id,
detail_tax_line_id,
ledger_id,
trx_id,
doc_seq_id,
doc_seq_value,
doc_seq_name,
tax_rate_id,
tax_line_id,
tax_amt,
tax_amt_funcl_curr,
tax_line_number,
taxable_amt,
taxable_amt_funcl_curr,
-- xla_code_combination_id,
trx_line_quantity,
-- xla_period_name,
trx_number,
trx_line_class,
trx_description,
trx_currency_code,
trx_date,
trx_communicated_date,
tax_invoice_date,
currency_conversion_type,
currency_conversion_date,
currency_conversion_rate,
tax_line_user_category,
tax_line_user_attribute1,
tax_line_user_attribute2,
tax_line_user_attribute3,
tax_line_user_attribute4,
tax_line_user_attribute5,
tax_line_user_attribute6,
tax_line_user_attribute7,
tax_line_user_attribute8,
tax_line_user_attribute9,
tax_line_user_attribute10,
tax_line_user_attribute11,
tax_line_user_attribute12,
tax_line_user_attribute13,
tax_line_user_attribute14,
tax_line_user_attribute15,
billing_tp_name,
billing_tp_number,
billing_tp_tax_reg_num,
posted_flag,
tax_rate_code,
tax_rate_code_description,
tax_rate,
tax_rate_vat_trx_type_code,
tax_type_code,
tax_rate_code_name,
tax_rate_register_type_code,
tax_rate_code_reg_type_mng,
tax_regime_code,
tax,
tax_jurisdiction_code,
tax_status_code,
tax_currency_code,
tax_amt_tax_curr,
taxable_amt_tax_curr,
orig_taxable_amt,
orig_taxable_amt_tax_curr,
orig_tax_amt,
orig_tax_amt_tax_curr,
precision,
minimum_accountable_unit,
-- functional_currency_code,
trx_line_id,
trx_line_number,
trx_line_description,
trx_line_type,
establishment_id,
internal_organization_id,
--ledger_name,
extract_source_ledger,
doc_event_status,
sub_ledger_invoice_identifier,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
request_id,
entity_code,
event_class_code,
rep_context_id,
functional_currency_code,
ledger_name)
VALUES (
101,
gt_detail_tax_line_id(i),
gt_ledger_id(i),
gt_trx_id(i),
gt_doc_seq_id(i),
gt_doc_seq_value(i),
gt_doc_seq_name(i),
gt_tax_rate_id(i),
gt_tax_line_id (i),
gt_tax_amt(i),
gt_tax_amt_funcl_curr(i),
gt_tax_line_number(i),
gt_taxable_amt(i),
gt_taxable_amt_funcl_curr(i),
-- gt_xla_code_combination_id(i),
gt_trx_line_quantity(i),
-- gt_xla_period_name(i),
gt_trx_number(i),
'GLMJE',
gt_trx_description(i),
gt_trx_currency_code(i),
gt_trx_date(i),
gt_trx_communicated_date(i),
gt_trx_communicated_date(i),
gt_currency_conversion_type(i),
gt_currency_conversion_date(i),
gt_currency_conversion_rate(i),
gt_tax_line_user_category(i),
gt_tax_line_user_attribute1(i),
gt_tax_line_user_attribute2(i),
gt_tax_line_user_attribute3(i),
gt_tax_line_user_attribute4(i),
gt_tax_line_user_attribute5(i),
gt_tax_line_user_attribute6(i),
gt_tax_line_user_attribute7(i),
gt_tax_line_user_attribute8(i),
gt_tax_line_user_attribute9(i),
gt_tax_line_user_attribute10(i),
gt_tax_line_user_attribute11(i),
gt_tax_line_user_attribute12(i),
gt_tax_line_user_attribute13(i),
gt_tax_line_user_attribute14(i),
gt_tax_line_user_attribute15(i),
gt_billing_tp_name(i),
gt_billing_tp_number(i),
gt_billing_tp_tax_reg_num(i),
gt_posted_flag(i),
gt_tax_rate_code(i),
gt_tax_rate_code_description(i),
gt_tax_rate(i),
gt_tax_rate_vat_trx_type_code(i),
gt_tax_type_code(i),
gt_tax_rate_code_name(i),
-- gt_tax_rate_reg_type_code(i),
'TAX',
g_tax_register_type_mng,
gt_tax_regime_code(i),
gt_tax(i),
gt_tax_jurisdiction_code(i),
gt_tax_status_code(i),
gt_tax_currency_code(i),
gt_tax_amt_tax_curr(i),
gt_taxable_amt_tax_curr(i),
gt_orig_taxable_amt(i),
gt_orig_taxable_amt_tax_curr(i),
gt_orig_tax_amt(i),
gt_orig_tax_amt_tax_curr(i),
gt_precision(i),
gt_minimum_accountable_unit(i),
-- gt_functional_currency_code(i),
gt_trx_line_id(i),
gt_trx_line_number(i),
gt_trx_line_description(i),
gt_trx_line_type(i),
gt_establishment_id(i),
gt_internal_organization_id(i),
--gt_ledger_name(i),
gt_extract_source_ledger(i),
gt_doc_event_status(i),
gt_sub_ledger_inv_identifier(i),
g_created_by ,
g_creation_date ,
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_request_id,
'GL_JE_LINES',
'MANUAL_JOURNALS',
g_rep_context_id,
g_fun_currency_code,
g_ledger_name );
INSERT INTO ZX_REP_ACTG_EXT_T(
actg_ext_line_id,
detail_tax_line_id,
actg_line_ccid,
period_name,
gl_transfer_flag,
accounting_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_login_id,
request_id)
VALUES (
zx_rep_actg_ext_t_s.nextval,
gt_detail_tax_line_id(i),
gt_actg_line_ccid(i),
gt_period_name(i),
'Y',
gt_accounting_date(i),
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_program_application_id,
g_program_id,
g_program_login_id,
g_request_id);
INSERT INTO AR_TAX_EXTRACT_SUB_ITF
(
EXTRACT_LINE_ID,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
CHART_OF_ACCOUNTS_ID,
REP_CONTEXT_ID,
EXTRACT_REPORT_LINE_NUMBER,
ledger_id,
EXTRACT_SOURCE_LEDGER,
TRX_ID,
TRX_DOC_SEQUENCE_ID,
TRX_DOC_SEQUENCE_NAME,
TRX_CLASS_CODE,
TAX_CODE_ID,
TAX_CODE_REGISTER_TYPE_CODE,
TAX_LINE_ID,
TAX_ENTERED_DR,
TAX_ENTERED_CR,
TAX_ENTERED_AMOUNT,
TAX_ACCOUNTED_DR,
TAX_ACCOUNTED_CR,
TAX_ACCOUNTED_AMOUNT,
TAX_LINE_NUMBER,
TAXABLE_ENTERED_DR,
TAXABLE_ENTERED_CR,
TAXABLE_AMOUNT,
TAXABLE_ACCOUNTED_DR,
TAXABLE_ACCOUNTED_CR,
TAXABLE_ACCOUNTED_AMOUNT,
ACCTG_DIST_ID,
AL_ACCOUNT_CCID,
TAXABLE_LINE_QUANTITY,
RECONCILIATION_FLAG,
AH_PERIOD_NAME
)
VALUES
(
AR_TAX_EXTRACT_SUB_ITF_S.nextval,
1,
SYSDATE,
1,
SYSDATE,
1,
G_REQUEST_ID,
G_CHART_OF_ACCOUNTS_ID,
G_REP_CONTEXT_ID, --BUG 2610643
PG_EXTRACT_REPORT_LINE_NUM_TAB(i),
PG_ledger_id(i),
'GL',
PG_TRX_ID_TAB(i),
PG_TRX_DOC_SEQUENCE_ID_TAB(i),
PG_TRX_DOC_SEQUENCE_NAME_TAB(i),
'GLMJE',
PG_TAX_CODE_ID_TAB(i),
'TAX',
PG_TAX_LINE_ID_TAB(i),
PG_TAX_ENTERED_DR_TAB(i),
PG_TAX_ENTERED_CR_TAB(i),
PG_TAX_ENTERED_AMOUNT_TAB(i),
PG_TAX_ACCOUNTED_DR_TAB(i),
PG_TAX_ACCOUNTED_CR_TAB(i),
PG_TAX_ACCOUNTED_AMOUNT_TAB(i),
PG_TAX_LINE_NUMBER_TAB(i),
PG_TAXABLE_ENTERED_DR_TAB(i),
PG_TAXABLE_ENTERED_CR_TAB(i),
PG_TAXABLE_AMOUNT_TAB(i),
PG_TAXABLE_ACCOUNTED_DR_TAB(i),
PG_TAXABLE_ACCOUNTED_CR_TAB(i),
PG_TAXABLE_ACCOUNTED_AMT_TAB(i),
PG_ACCTG_DIST_ID_TAB(i),
PG_AL_ACCOUNT_CCID_TAB(i),
PG_TAXABLE_LINE_QUANTITY_TAB(i),
'N',
PG_PERIOD_NAME_TAB(i)
);
arp_util_tax.debug('Number of GL rows successfully inserted = ' ||
TO_CHAR(nvl(gt_ledger_id.COUNT, 0)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
'Number of GL rows successfully inserted ='||TO_CHAR(nvl(gt_ledger_id.COUNT, 0)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF.END',
'INSERT_GL_SUB_ITF(-)');
'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
g_error_buffer);
END INSERT_GL_SUB_ITF;
g_last_updated_by := fnd_global.user_id;
g_last_update_login := fnd_global.login_id;
g_last_update_date := sysdate;
'Before insert GL Tax lines :' ||to_char(GT_TRX_ID.count));
SELECT zx_rep_trx_detail_t_s.nextval
INTO gt_detail_tax_line_id(i)
FROM DUAL;
IF (i >= C_LINES_PER_INSERT) THEN
INSERT_GL_SUB_ITF;
INSERT_GL_SUB_ITF;
gt_ledger_id.DELETE;
gt_sub_ledger_inv_identifier.DELETE;
gt_actg_line_ccid.DELETE;
gt_period_name.DELETE;
gt_detail_tax_line_id.DELETE;
/* PG_TRX_ID_TAB.DELETE;
PG_TRX_DOC_SEQUENCE_ID_TAB.DELETE;
PG_TRX_DOC_SEQUENCE_NAME_TAB.DELETE;
PG_TAX_CODE_ID_TAB.DELETE;
PG_TAX_LINE_ID_TAB.DELETE;
PG_TAX_ENTERED_DR_TAB.DELETE;
PG_TAX_ENTERED_CR_TAB.DELETE;
PG_TAX_ENTERED_AMOUNT_TAB.DELETE;
PG_TAX_ACCOUNTED_DR_TAB.DELETE;
PG_TAX_ACCOUNTED_CR_TAB.DELETE;
PG_TAX_ACCOUNTED_AMOUNT_TAB.DELETE;
PG_TAX_LINE_NUMBER_TAB.DELETE;
PG_TAXABLE_ENTERED_DR_TAB.DELETE;
PG_TAXABLE_ENTERED_CR_TAB.DELETE;
PG_TAXABLE_AMOUNT_TAB.DELETE;
PG_TAXABLE_ACCOUNTED_DR_TAB.DELETE;
PG_TAXABLE_ACCOUNTED_CR_TAB.DELETE;
PG_TAXABLE_ACCOUNTED_AMT_TAB.DELETE;
PG_ACCTG_DIST_ID_TAB.DELETE;
PG_AL_ACCOUNT_CCID_TAB.DELETE;
PG_TAXABLE_LINE_QUANTITY_TAB.DELETE;
PG_PERIOD_NAME_TAB.DELETE;
PG_EXTRACT_REPORT_LINE_NUM_TAB.DELETE;
PROCEDURE UPDATE_ADDITIONAL_INFO(
P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
IS
CURSOR detail_t_cur(c_request_id IN NUMBER) IS
SELECT DET.DETAIL_TAX_LINE_ID,
DET.LEDGER_ID,
DET.INTERNAL_ORGANIZATION_ID,
DET.TRX_ID ,
-- TRX_TYPE_ID ,
DET.TRX_LINE_CLASS,
DET.TAX_RATE_VAT_TRX_TYPE_CODE,
-- TAX_RATE_REGISTER_TYPE_CODE,
DET.TAX_LINE_ID ,
DET.TRX_LINE_ID ,
-- RECONCILIATION_FLAG ,
DET.TAX_REGIME_CODE,
DET.TAX,
DET.TAX_JURISDICTION_CODE,
DET.TAX_RATE,
DET.TAX_RATE_ID,
DET.TAX_RATE_CODE,
DET.TAX_TYPE_CODE,
DET.TRX_DATE,
DET.TRX_CURRENCY_CODE,
DET.CURRENCY_CONVERSION_RATE,
DET.APPLICATION_ID,
DET.TAX_AMT,
DET.TAX_AMT_FUNCL_CURR,
det.taxable_amt,
det.taxable_amt_funcl_curr,
ACT.ACTG_LINE_CCID,
ACT.ACTG_EXT_LINE_ID
FROM zx_rep_trx_detail_t det,
zx_rep_actg_ext_t act
WHERE det.extract_source_ledger = 'GL'
AND det.request_id = c_request_id
AND act.detail_tax_line_id(+) = det.detail_tax_line_id;
SELECT account_type
FROM gl_code_combinations
WHERE code_combination_id = c_ccid;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO.BEGIN',
'ZX_GL_EXTRACT_PKG:UPDATE_ADDITIONAL_INFO(+)');
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO',
'Account Flexfield = '||GT_ACCOUNT_FLEXFIELD(i));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO',
'Account Description = '||GT_ACCOUNT_DESCRIPTION(i));
UPDATE ZX_REP_TRX_DETAIL_T SET
TAX_RATE_VAT_TRX_TYPE_DESC = GT_TAX_RATE_VAT_TRX_TYPE_DESC(i),
TRX_CLASS_MNG = GT_TRX_CLASS_MNG(i),
TAX_RATE_CODE_VAT_TRX_TYPE_MNG = GT_TAX_RATE_VAT_TRX_TYPE_MNG(i),
TAX_TYPE_MNG = GT_TAX_TYPE_MNG(i),
HQ_ESTB_REG_NUMBER = GT_TAX_REG_NUM(i),
TAX_TYPE_CODE = GT_TAX_TYPE_CODE(i),
TAX_AMT = GT_TAX_AMT(i),
TAX_AMT_FUNCL_CURR = GT_TAX_AMT_FUNCL_CURR(i),
TAXABLE_AMT = GT_TAXABLE_AMT(i),
TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i)
WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
UPDATE ZX_REP_ACTG_EXT_T
SET trx_arap_balancing_segment = gt_trx_arap_balancing_segment(i),
trx_arap_natural_account = gt_trx_arap_natural_account(i),
trx_taxable_balancing_segment = gt_trx_taxable_bal_seg(i),
trx_taxable_natural_account = gt_trx_taxable_natural_account(i),
trx_tax_balancing_segment = gt_trx_tax_balancing_segment(i),
trx_tax_natural_account = gt_trx_tax_natural_account(i),
account_flexfield = gt_account_flexfield(i),
account_description = gt_account_description(i),
TRX_CONTROL_ACCOUNT_FLEXFIELD = GT_TRX_CONTROL_ACCFLEXFIELD(i),
TRX_TAXABLE_ACCOUNT_DESC = GT_TRX_TAXABLE_ACCOUNT_DESC(i)
WHERE actg_ext_line_id = gt_actg_ext_line_id(i);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO.END',
'ZX_GL_EXTRACT_PKG:UPDATE_ADDITIONAL_INFO(-)');
END UPDATE_ADDITIONAL_INFO;
SELECT CODE_COMBINATION_ID, TAX_GROUP_ID
FROM GL_JE_LINES
WHERE JE_HEADER_ID = C_TRX_ID
AND TAX_CODE_ID = C_TAX_CODE_ID
AND JE_LINE_NUM = C_TRX_LINE_ID;
SELECT CODE_COMBINATION_ID
FROM GL_JE_LINES
WHERE JE_HEADER_ID = C_TRX_ID
AND TAX_GROUP_ID = C_TAX_GROUP_ID
AND TAX_CODE_ID IS NULL
AND JE_LINE_NUM = C_TRX_LINE_ID;
SELECT CODE_COMBINATION_ID
FROM GL_JE_LINES
WHERE JE_HEADER_ID = C_TRX_ID
AND TAX_GROUP_ID IS NULL
AND JE_LINE_NUM = C_TRX_LINE_ID;
L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||
' FROM GL_CODE_COMBINATIONS '||
' WHERE CODE_COMBINATION_ID = :L_CCID ';
L_SQL_STATEMENT2 := ' SELECT '||P_ACCOUNTING_SEGMENT ||
' FROM GL_CODE_COMBINATIONS '||
' WHERE CODE_COMBINATION_ID = :L_CCID ';
SELECT registration_number
INTO GT_TAX_REG_NUM(i)
FROM zx_registrations reg
WHERE reg.party_tax_profile_id = l_ptp_id
AND nvl(reg.tax_regime_code,1) = nvl(p_tax_regime_code,1)
AND nvl(reg.tax,nvl(p_tax,1)) = nvl(p_tax,1)
AND nvl(reg.tax_jurisdiction_code,nvl(p_tax_jurisdiction_code,1)) = nvl(p_tax_jurisdiction_code,1)
AND sysdate >= reg.effective_from
AND (sysdate <= reg.effective_to OR reg.effective_to IS NULL);