[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 update_zx_rep_detail_t(
P_COUNT IN BINARY_INTEGER);
PROCEDURE insert_actg_info (
P_COUNT IN BINARY_INTEGER);
PROCEDURE update_additional_info(
P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE) IS
l_count number;
SELECT DETAIL_TAX_LINE_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_LEVEL_TYPE,
INTERNAL_ORGANIZATION_ID,
tax_date,
TAX_RATE_VAT_TRX_TYPE_CODE,
HQ_ESTB_REG_NUMBER,
TRX_ID,
TRX_LINE_ID ,
TAX_LINE_ID ,
TRX_LINE_TYPE,
TRX_LINE_CLASS,
BILL_FROM_PARTY_TAX_PROF_ID,
BILL_FROM_SITE_TAX_PROF_ID,
SHIP_TO_SITE_TAX_PROF_ID,
SHIP_FROM_SITE_TAX_PROF_ID,
SHIP_TO_PARTY_TAX_PROF_ID,
SHIP_FROM_PARTY_TAX_PROF_ID,
-- zx_dtl.BILL_FROM_PARTY_ID,
-- zx_dtl.BILL_FROM_PARTY_SITE_ID,
SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
HISTORICAL_FLAG,
posted_flag,
event_type_code, -- Accounting Columns
event_number,
event_status_code,
je_category_name,
accounting_date,
gl_transfer_status_flag,
description_header,
ae_line_num,
accounting_class_code,
description_line,
statistical_amount,
process_status_code,
gl_transfer_status_code,
doc_sequence_id,
doc_sequence_value,
party_id,
party_site_id,
party_type_code,
event_id,
ae_header_id,
code_combination_id,
period_name,
-- zx_dtl.trx_line_id
actg_source_id,
ledger_id,
tax_recoverable_flag,
taxable_amt , --Bug 5409170
tax_amt, --Bug 5409170
taxable_amt_funcl_curr,
tax_amt_funcl_curr ,
ipv_priority,
tax_type_code
FROM ( SELECT zx_dtl.DETAIL_TAX_LINE_ID,
zx_dtl.APPLICATION_ID,
zx_dtl.ENTITY_CODE,
zx_dtl.EVENT_CLASS_CODE,
zx_dtl.TRX_LEVEL_TYPE,
zx_dtl.INTERNAL_ORGANIZATION_ID,
zx_dtl.tax_date,
ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
ZX_DTL.HQ_ESTB_REG_NUMBER,
zx_dtl.TRX_ID,
zx_dtl.TRX_LINE_ID ,
zx_dtl.TAX_LINE_ID ,
zx_dtl.TRX_LINE_TYPE,
zx_dtl.TRX_LINE_CLASS,
zx_dtl.BILL_FROM_PARTY_TAX_PROF_ID,
zx_dtl.BILL_FROM_SITE_TAX_PROF_ID,
zx_dtl.SHIP_TO_SITE_TAX_PROF_ID,
zx_dtl.SHIP_FROM_SITE_TAX_PROF_ID,
zx_dtl.SHIP_TO_PARTY_TAX_PROF_ID,
zx_dtl.SHIP_FROM_PARTY_TAX_PROF_ID,
-- zx_dtl.BILL_FROM_PARTY_ID,
-- zx_dtl.BILL_FROM_PARTY_SITE_ID,
zx_dtl.SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
zx_dtl.BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
zx_dtl.SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
zx_dtl.BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
zx_dtl.SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
zx_dtl.BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
zx_dtl.HISTORICAL_FLAG,
zx_dtl.posted_flag,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code gl_transfer_status_flag,
xla_head.description description_header,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description description_line,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
-- zx_dtl.trx_line_id
zx_dtl.actg_source_id,
zx_dtl.ledger_id,
zx_dtl.tax_recoverable_flag,
zx_dtl.taxable_amt , --Bug 5409170
zx_dtl.tax_amt, --Bug 5409170
nvl(zx_dtl.taxable_amt_funcl_curr,zx_dtl.taxable_amt) taxable_amt_funcl_curr,
nvl(zx_dtl.tax_amt_funcl_curr,zx_dtl.tax_amt) tax_amt_funcl_curr,
row_number() over ( partition by xla_dist.event_id,
xla_dist.ae_header_id,
xla_dist.ae_line_num,
xla_dist.source_distribution_type,
xla_dist.tax_line_ref_id,
xla_dist.tax_rec_nrec_dist_ref_id
order by xla_dist.event_id,
xla_dist.ae_header_id,
xla_dist.ae_line_num,
xla_dist.source_distribution_type,
xla_dist.tax_line_ref_id,
xla_dist.tax_rec_nrec_dist_ref_id
) ipv_priority,
zx_dtl.tax_type_code
FROM zx_rep_trx_detail_t zx_dtl,
xla_transaction_entities xla_ent,
xla_events xla_event,
xla_ae_headers xla_head,
xla_ae_lines xla_line,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd,
xla_distribution_links xla_dist
WHERE zx_dtl.request_id = c_request_id
AND zx_dtl.extract_source_ledger = 'AP'
AND zx_dtl.posted_flag = 'A'
AND zx_dtl.trx_id = nvl(xla_ent.source_id_int_1,-99) -- Accounting Joins
AND xla_ent.entity_code = 'AP_INVOICES' -- Check this condition
AND xla_ent.entity_id = xla_event.entity_id
AND xla_event.event_id = xla_head.event_id
AND xla_head.ae_header_id = xla_line.ae_header_id
AND xla_head.balance_type_code = 'A'
AND xla_head.ledger_id = c_ledger_id
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.program_code = asd.program_code
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = 'Y'
AND acs.accounting_class_code = xla_line.accounting_class_code
AND zx_dtl.tax_line_id = xla_dist.tax_line_ref_id
-- AND zx_dtl.actg_source_id = xla_dist.source_distribution_id_num_1
AND zx_dtl.actg_source_id = xla_dist.tax_rec_nrec_dist_ref_id
AND xla_head.ae_header_id = xla_dist.ae_header_id
AND xla_line.ae_header_id = xla_dist.ae_header_id
AND xla_line.ae_line_num = xla_dist.ae_line_num
AND xla_head.application_id = xla_ent.application_id
AND xla_head.application_id = xla_line.application_id
AND ((substr(xla_head.event_type_code,1,10) <> 'PREPAYMENT')
OR
(substr(xla_head.event_type_code,1,10) = 'PREPAYMENT'
AND zx_dtl.trx_line_class = 'PREPAY_APPLICATION')
)
-- bug 7650289 start
AND xla_ent.application_id = 200
AND xla_event.application_id = xla_ent.application_id
AND xla_dist.application_id = xla_line.application_id
-- bug 7650289 end
--AND xla_ent.ledger_id = zx_dtl.ledger_id
) ipv
where ipv.ipv_priority = 1
UNION ALL
SELECT zx_dtl.DETAIL_TAX_LINE_ID,
zx_dtl.APPLICATION_ID,
zx_dtl.ENTITY_CODE,
zx_dtl.EVENT_CLASS_CODE,
zx_dtl.TRX_LEVEL_TYPE,
zx_dtl.INTERNAL_ORGANIZATION_ID,
zx_dtl.tax_date,
ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
ZX_DTL.HQ_ESTB_REG_NUMBER,
zx_dtl.TRX_ID,
zx_dtl.TRX_LINE_ID ,
zx_dtl.TAX_LINE_ID ,
zx_dtl.TRX_LINE_TYPE,
zx_dtl.TRX_LINE_CLASS,
zx_dtl.BILL_FROM_PARTY_TAX_PROF_ID,
zx_dtl.BILL_FROM_SITE_TAX_PROF_ID,
zx_dtl.SHIP_TO_SITE_TAX_PROF_ID,
zx_dtl.SHIP_FROM_SITE_TAX_PROF_ID,
zx_dtl.SHIP_TO_PARTY_TAX_PROF_ID,
zx_dtl.SHIP_FROM_PARTY_TAX_PROF_ID,
-- zx_dtl.BILL_FROM_PARTY_ID,
-- zx_dtl.BILL_FROM_PARTY_SITE_ID,
zx_dtl.SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
zx_dtl.BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
zx_dtl.SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
zx_dtl.BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
zx_dtl.SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
zx_dtl.BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
zx_dtl.HISTORICAL_FLAG,
zx_dtl.posted_flag,
TO_CHAR(NULL), --xla_event.event_type_code, -- Accounting Columns
TO_NUMBER(NULL), --xla_event.event_number,
TO_CHAR(NULL), --xla_event.event_status_code,
TO_CHAR(NULL), --xla_head.je_category_name,
TO_DATE(NULL), --xla_head.accounting_date,
TO_CHAR(NULL), --xla_head.gl_transfer_status_code,
TO_CHAR(NULL), --xla_head.description,
TO_NUMBER(NULL), --xla_line.ae_line_num,
TO_CHAR(NULL), --xla_line.accounting_class_code,
TO_CHAR(NULL), --xla_line.description,
TO_NUMBER(NULL), --xla_line.statistical_amount,
TO_CHAR(NULL), --xla_event.process_status_code,
TO_CHAR(NULL), --xla_head.gl_transfer_status_code,
TO_NUMBER(NULL), --xla_head.doc_sequence_id,
TO_NUMBER(NULL), --xla_head.doc_sequence_value,
TO_NUMBER(NULL), --xla_line.party_id,
TO_NUMBER(NULL), --xla_line.party_site_id,
TO_CHAR(NULL), --xla_line.party_type_code,
TO_NUMBER(NULL), --xla_event.event_id,
TO_NUMBER(NULL), --xla_head.ae_header_id,
TO_NUMBER(NULL), --xla_line.code_combination_id,
TO_CHAR(NULL), --xla_head.period_name,
TO_NUMBER(NULL), --zx_dtl.trx_line_id
zx_dtl.ledger_id,
zx_dtl.tax_recoverable_flag,
zx_dtl.TAXABLE_AMT , --Bug 5409170
zx_dtl.tax_amt , --Bug 5409170
nvl(zx_dtl.taxable_amt_funcl_curr,zx_dtl.TAXABLE_AMT) ,--Bug 5405785
nvl(zx_dtl.tax_amt_funcl_curr,zx_dtl.tax_amt), --Bug 5405785
to_number(NULL),
tax_type_code
FROM zx_rep_trx_detail_t zx_dtl
WHERE zx_dtl.request_id = c_request_id
AND zx_dtl.extract_source_ledger = 'AP'
AND ( (nvl(zx_dtl.posted_flag,'N') = 'N')
OR
(zx_dtl.posted_flag = 'A' AND zx_dtl.tax_line_id IS NULL)
);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info.BEGIN',
'update_additional_info(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'Request ID : '||to_char(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'Reporting Ledger : '||to_char(p_trl_global_variables_rec.reporting_ledger_id));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'Primary Ledger : '||to_char(p_trl_global_variables_rec.ledger_id));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'Rows fetched by rep_detail_cursor :'||to_char(l_count));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'Accouting Cursor Line Number :'||to_char(i));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' GT_BILL_FROM_PTY_TAX_PROF_ID(i) :'||to_char(GT_BILL_FROM_PTY_TAX_PROF_ID(i)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' GT_BILLING_TRADING_PARTNER_ID(i) :'||to_char(GT_BILLING_TRADING_PARTNER_ID(i)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' include_accounting_segments :'||p_trl_global_variables_rec.include_accounting_segments);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' gt_posted_flag :'||gt_posted_flag(i));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'GT_TAXABLE_AMT(i) :'||to_char(GT_TAXABLE_AMT(i)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'GT_TAX_AMT(i) :'||to_char(GT_TAX_AMT(i)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
'GT_IPV_PRIORITY(i) :'||to_char(GT_IPV_PRIORITY(i)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' Accounting API calls :');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' include_accounting_segments :'||p_trl_global_variables_rec.include_accounting_segments);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' gt_posted_flag :'||gt_posted_flag(i));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
' GT_actg_EVENT_ID :'||to_char(GT_actg_EVENT_ID(i)));
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
i,--Need to change this to j if inserting into accouting table
gt_actg_line_num(i)) ;
update_zx_rep_detail_t(l_count);
insert_actg_info(j);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info.END',
'update_additional_info(-)');
'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
g_error_buffer);
END update_additional_info;
SELECT name
FROM gl_ledgers
WHERE ledger_id = c_ledger_id
AND rownum = 1;
SELECT party_id
FROM zx_party_tax_profile
WHERE PARTY_TAX_PROFILE_ID = c_bill_from_ptp_id
AND party_type_code = 'THIRD_PARTY';
SELECT party_id
FROM zx_party_tax_profile
WHERE PARTY_TAX_PROFILE_ID = c_bill_from_stp_id
AND party_type_code = 'THIRD_PARTY_SITE';
SELECT rep_registration_number
FROM zx_party_tax_profile
WHERE party_id = c_bill_from_party_id
AND party_type_code = 'THIRD_PARTY';
SELECT rep_registration_number
FROM zx_party_tax_profile
WHERE party_id = c_bill_from_site_id
AND party_type_code = 'THIRD_PARTY_SITE';
SELECT SEGMENT1,
-- VAT_REGISTRATION_NUM,
NUM_1099||GLOBAL_ATTRIBUTE12,
VENDOR_NAME,
VENDOR_NAME_ALT,
STANDARD_INDUSTRY_CLASS,
PARTY_ID
FROM ap_suppliers
WHERE vendor_id = c_bill_from_party_id;
SELECT CITY,
COUNTY,
STATE,
PROVINCE,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
ADDRESS_LINES_ALT,
COUNTRY,
ZIP,
-- VENDOR_ID,
-- VENDOR_SITE_ID,
-- TAX_REPORTING_SITE_FLAG,
GLOBAL_ATTRIBUTE17,
VENDOR_SITE_CODE_ALT,
VENDOR_SITE_CODE,
-- VAT_REGISTRATION_NUM
PARTY_SITE_ID
FROM ap_supplier_sites_all
WHERE vendor_site_id = c_bill_from_site_id;
SELECT discount_distribution_method,
disc_is_inv_less_tax_flag,
liability_post_lookup_code
FROM ap_system_parameters_all
WHERE org_id = c_org_id;
SELECT
ael.code_combination_id
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
-- AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.application_id = ael.application_id
AND rownum =1;
SELECT
ael.code_combination_id
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.trx_line_id = c_trx_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.application_id = ael.application_id
AND rownum =1;
SELECT
ael.code_combination_id
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.trx_line_id = c_trx_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
--AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.application_id = ael.application_id
AND ael.accounting_class_code <> 'LIABILITY'
AND rownum =1;
SELECT
ael.code_combination_id
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND lnk.application_id = 200
--AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND lnk.source_distribution_type = 'AP_INV_DIST'
AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.application_id = ael.application_id
AND rownum =1;
SELECT
ael.code_combination_id
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.tax_line_id = c_tax_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.application_id = ael.application_id
AND rownum =1;
SELECT
ael.code_combination_id
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.tax_line_id = c_tax_line_id
AND zx_dist.REC_NREC_TAX_DIST_ID = c_tax_line_dist_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
--AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.application_id = ael.application_id
AND ael.accounting_class_code <> 'LIABILITY'
AND rownum =1;
L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||','||P_ACCOUNTING_SEGMENT ||
' FROM GL_CODE_COMBINATIONS '||
' WHERE CODE_COMBINATION_ID = :L_CCID ';
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.ae_header_id = ael.ae_header_id
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.ledger_id = c_ledger_id
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_id;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.ledger_id = c_ledger_id
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_id;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.trx_line_id = c_trx_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.ledger_id = c_ledger_id
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_id;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.tax_line_id = c_tax_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.ledger_id = c_ledger_id
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_id;
SELECT SUM( nvl(lnk.UNROUNDED_ENTERED_DR,0) - (nvl(lnk.UNROUNDED_ENTERED_CR,0) )) ,
SUM( nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0) ))
/*SUM( (NVL(lnk.UNROUNDED_ENTERED_CR,0) * -1) - NVL(lnk.UNROUNDED_ENTERED_DR,0)),
SUM((NVL(lnk.UNROUNDED_ACCOUNTED_CR,0) * -1) - NVL(lnk.UNROUNDED_ACCOUNTED_DR,0))
--sum(nvl(lnk.UNROUNDED_ENTERED_CR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)),
-- sum(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)) --Bug 5393051
Nvl(sum(decode(zx_dist.REVERSE_FLAG,'Y',lnk.UNROUNDED_ENTERED_CR * -1,lnk.UNROUNDED_ENTERED_DR)),0),
Nvl(sum(decode(zx_dist.REVERSE_FLAG,'Y',lnk.UNROUNDED_ACCOUNTED_CR * -1,lnk.UNROUNDED_ACCOUNTED_DR)),0) */
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.tax_line_id = c_tax_line_id
AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.ae_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND lnk.ae_line_num = c_ae_line_num
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.ledger_id = c_ledger_id
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_id;
SELECT SUM( abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) - (nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
(decode(sign(zx_dist.taxable_amt),0,1,sign(zx_dist.taxable_amt)))),
SUM( abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))) *
(decode(sign(zx_dist.taxable_amt),0,1,sign(zx_dist.taxable_amt))))
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.trx_line_id = c_trx_line_id
-- AND zx_dist.trx_line_dist_id = c_trx_line_dist_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id --Bug 5393051
AND lnk.ae_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = ael.ae_header_id
AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY')
AND aeh.ledger_id = c_ledger_id
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_id;
SELECT SUM( abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) - (nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
(decode(sign(zx_dist.taxable_amt),0,1,sign(zx_dist.taxable_amt)))),
SUM( abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))) *
(decode(sign(zx_dist.taxable_amt),0,1,sign(zx_dist.taxable_amt))))
FROM zx_rec_nrec_dist zx_dist,
xla_ae_lines ael,
xla_distribution_links lnk
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.APPLICATION_ID = c_application_id
AND zx_dist.entity_code = c_entity_code
AND zx_dist.event_class_Code = c_event_class_code
AND zx_dist.trx_level_type = c_trx_level_type
AND zx_dist.trx_line_id = c_trx_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.ae_header_id = ael.ae_header_id
AND ael.application_id = lnk.application_id
AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY')
AND ael.ledger_id = c_ledger_id
AND ROWNUM = 1;
SELECT sum(aphd.amount), -- discount amount (entered)
sum(aphd.paid_base_amount) -- discount amount (accounted)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
AND aid.distribution_line_number
IN (SELECT distribution_line_number
FROM ap_invoice_distributions_all
WHERE invoice_id = p_trx_id
AND line_type_lookup_code = 'ITEM')
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND nvl(aph.historical_flag, 'N') = 'N'
AND aph.check_id = aip.check_id
UNION
-- nipatel - I find lots of issues with this query. The main query only restricts
-- invoices based on distribution line number. We should have a condition based on
-- invoice id in the main query. The join to ap_invoices is not necessary since
-- we already have trx_id as input parameter which can be used to join to
-- ap_invoice_distributions or ap_invoice_payments. Why do we need the subquery?
-- it seems to be unncessary if we put the same conditions in the main query. Also
-- there a re no indexes based on xal.Upg_Tax_Reference_ID2/3 whihc causes FTS
-- on xla_ae_lines. Need to get this query reviewed by AP team and log a bug
-- against XLA team for indexes.
SELECT xal.entered_dr - xal.entered_cr ,
-- discount entered amount (replace this with new xla colum names)
xal.accounted_dr -xal.entered_cr
-- discount entered amount (replace this with new xla colum names)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_history_all aph,
xla_ae_lines xal
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
AND aid.distribution_line_number
IN (SELECT distribution_line_number
FROM ap_invoice_distributions_all
WHERE invoice_id = p_trx_id
AND line_type_lookup_code = 'ITEM')
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = 'DISCOUNT'
AND aph.check_id = aip.check_id
AND nvl(aph.historical_flag, 'N') = 'Y';
SELECT sum(aphd.amount), -- discount amount (entered)
sum(aphd.paid_base_amount) -- discount amount (accounted)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
AND aid.distribution_line_number
IN (SELECT distribution_line_number
FROM ap_invoice_distributions_all
WHERE invoice_id = p_trx_id
AND line_type_lookup_code = 'TAX')
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND nvl(aph.historical_flag, 'N') = 'N'
AND aph.check_id = aip.check_id
UNION
SELECT xal.entered_dr - xal.entered_cr ,
-- discount entered amount (replace this with new xla colum names)
xal.accounted_dr -xal.entered_cr
-- discount entered amount (replace this with new xla colum names)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_history_all aph,
xla_ae_lines xal
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
AND aid.distribution_line_number
IN (SELECT distribution_line_number
FROM ap_invoice_distributions_all
WHERE invoice_id = p_trx_id
AND line_type_lookup_code = 'TAX')
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = 'DISCOUNT'
AND aph.check_id = aip.check_id
AND nvl(aph.historical_flag, 'N') = 'Y';
SELECT sum(aphd.amount), -- discount amount (entered)
sum(aphd.paid_base_amount) -- discount amount (accounted)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_trx_line_id
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND nvl(aph.historical_flag, 'N') = 'N'
AND aph.check_id = aip.check_id
UNION
SELECT xal.entered_dr - xal.entered_cr ,
-- discount entered amount (replace this with new xla colum names)
xal.accounted_dr -xal.entered_cr
-- discount entered amount (replace this with new xla colum names)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_history_all aph,
xla_ae_lines xal
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_trx_line_id
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = 'DISCOUNT'
AND aph.check_id = aip.check_id
AND nvl(aph.historical_flag, 'N') = 'Y';
SELECT sum(aphd.amount), -- discount amount (entered)
sum(aphd.paid_base_amount) -- discount amount (accounted)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_tax_line_id
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND nvl(aph.historical_flag, 'N') = 'N'
AND aph.check_id = aip.check_id
UNION
SELECT xal.entered_dr - xal.entered_cr ,
-- discount entered amount (replace this with new xla colum names)
xal.accounted_dr -xal.entered_cr
-- discount entered amount (replace this with new xla colum names)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_history_all aph,
xla_ae_lines xal
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_tax_line_id
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = 'DISCOUNT'
AND aph.check_id = aip.check_id
AND nvl(aph.historical_flag, 'N') = 'Y';
SELECT sum(aphd.amount), -- discount amount (entered)
sum(aphd.paid_base_amount) -- discount amount (accounted)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_trx_line_id
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND nvl(aph.historical_flag, 'N') = 'N'
AND aph.check_id = aip.check_id
UNION
SELECT xal.entered_dr - xal.entered_cr ,
-- discount entered amount (replace this with new xla colum names)
xal.accounted_dr -xal.entered_cr
-- discount entered amount (replace this with new xla colum names)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_history_all aph,
xla_ae_lines xal
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_trx_line_id
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = 'DISCOUNT'
AND aph.check_id = aip.check_id
AND nvl(aph.historical_flag, 'N') = 'Y';
SELECT sum(aphd.amount), -- discount amount (entered)
sum(aphd.paid_base_amount) -- discount amount (accounted)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_tax_line_id
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
AND aphd.invoice_distribution_id = aid.invoice_distribution_id
AND nvl(aph.historical_flag, 'N') = 'N'
AND aph.check_id = aip.check_id
UNION
SELECT xal.entered_dr - xal.entered_cr ,
-- discount entered amount (replace this with new xla colum names)
xal.accounted_dr -xal.entered_cr
-- discount entered amount (replace this with new xla colum names)
FROM ap_invoice_distributions_all aid,
-- ap_invoices_all ai,
ap_invoice_payments_all aip,
ap_payment_history_all aph,
xla_ae_lines xal
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
-- AND aid.distribution_line_number
AND aid.invoice_distribution_id = p_tax_line_id
AND aip.invoice_payment_id = xal.Upg_Tax_Reference_ID3
AND aid.old_dist_line_number = xal.Upg_Tax_Reference_ID2
AND xal.accounting_class_code = 'DISCOUNT'
AND aph.check_id = aip.check_id
AND nvl(aph.historical_flag, 'N') = 'Y';
PROCEDURE update_zx_rep_detail_t(
P_COUNT IN BINARY_INTEGER)
IS
BEGIN
IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t.BEGIN',
'update_zx_rep_detail_t(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t',
'Rows Update by update_zx_rep_detail_t :'||to_char(p_count));
UPDATE zx_rep_trx_detail_t SET
REP_CONTEXT_ID = G_REP_CONTEXT_ID,
BILLING_TP_NUMBER = GT_BILLING_TP_NUMBER(i),
BILLING_TP_TAX_REG_NUM = GT_BILLING_TP_TAX_REG_NUM(i),
BILLING_TP_TAXPAYER_ID = GT_BILLING_TP_TAXPAYER_ID(i),
BILLING_TP_SITE_NAME_ALT = GT_BILLING_TP_SITE_NAME_ALT(i),
BILLING_TP_SITE_NAME = GT_BILLING_TP_SITE_NAME(i),
BILLING_TP_SITE_TAX_REG_NUM = GT_BILLING_SITE_TAX_REG_NUM(i),
HQ_ESTB_REG_NUMBER = GT_TAX_REG_NUM(i),
BILLING_TP_NAME = GT_BILLING_TP_NAME(i),
BILLING_TP_NAME_ALT = GT_BILLING_TP_NAME_ALT(i),
BILLING_TP_SIC_CODE = GT_BILLING_TP_SIC_CODE(i),
BILLING_TP_CITY = GT_BILLING_TP_CITY(i),
BILLING_TP_COUNTY = GT_BILLING_TP_COUNTY(i),
BILLING_TP_STATE = GT_BILLING_TP_STATE(i),
BILLING_TP_PROVINCE = GT_BILLING_TP_PROVINCE(i),
BILLING_TP_ADDRESS1 = GT_BILLING_TP_ADDRESS1(i),
BILLING_TP_ADDRESS2 = GT_BILLING_TP_ADDRESS2(i),
BILLING_TP_ADDRESS3 = GT_BILLING_TP_ADDRESS3(i),
BILLING_TP_ADDRESS_LINES_ALT = GT_BILLING_TP_ADDR_LINES_ALT(i),
BILLING_TP_COUNTRY = GT_BILLING_TP_COUNTRY(i),
BILLING_TP_POSTAL_CODE = GT_BILLING_TP_POSTAL_CODE(i),
GDF_PO_VENDOR_SITE_ATT17 = GT_GDF_PO_VENDOR_SITE_ATT17(i),
TRX_CLASS_MNG = GT_TRX_CLASS_MNG(i),
TAX_RATE_CODE_REG_TYPE_MNG = GT_TAX_RATE_CODE_REG_TYPE_MNG(i),
TAX_RATE_REGISTER_TYPE_CODE = GT_TAX_RATE_REG_TYPE_CODE(i),
TAX_RATE_VAT_TRX_TYPE_DESC = GT_TAX_RATE_VAT_TRX_TYPE_DESC(i),
FUNCTIONAL_CURRENCY_CODE = G_FUN_CURRENCY_CODE,
LEDGER_NAME = GT_LEDGER_NAME(i),
TAX_AMT = GT_TAX_AMT(i), --Bug 5393051
TAX_AMT_FUNCL_CURR = GT_TAX_AMT_FUNCL_CURR(i), --Bug 5393051
TAXABLE_AMT = GT_TAXABLE_AMT(i),--Bug 5393051
TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i), --Bug 5393051
TAX_TYPE_MNG = GT_TAX_TYPE_MNG(i)
WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t.END',
'update_zx_rep_detail_t(-)');
'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t',
g_error_buffer);
END update_zx_rep_detail_t;
| insert_actg_info |
| DESCRIPTION |
| This procedure inserts payables tax data into ZX_REP_TRX_DETAIL_T table|
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 11-Jan-2005 Srinivasa Rao Korrapati Created |
| |
+===========================================================================*/
PROCEDURE insert_actg_info(
P_COUNT IN BINARY_INTEGER)
IS
l_count NUMBER;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info.BEGIN',
'ZX_AP_ACTG_EXTRACT_PKG: insert_actg_info(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info',
' Record Count = ' ||to_char(P_COUNT));
INSERT INTO ZX_REP_ACTG_EXT_T(
actg_ext_line_id,
detail_tax_line_id,
actg_event_type_code,
actg_event_number,
actg_event_status_flag,
actg_category_code,
accounting_date,
gl_transfer_flag,
-- gl_transfer_run_id,
actg_header_description,
actg_line_num,
actg_line_type_code,
actg_line_description,
actg_stat_amt,
actg_error_code,
gl_transfer_code,
actg_doc_sequence_id,
--actg_doc_sequence_name,
actg_doc_sequence_value,
actg_party_id,
actg_party_site_id,
actg_party_type,
actg_event_id,
actg_header_id,
actg_source_id,
--actg_source_table,
actg_line_ccid,
period_name,
TRX_ARAP_BALANCING_SEGMENT,
TRX_ARAP_NATURAL_ACCOUNT,
TRX_TAXABLE_BALANCING_SEGMENT,
TRX_TAXABLE_NATURAL_ACCOUNT,
TRX_TAX_BALANCING_SEGMENT,
TRX_TAX_NATURAL_ACCOUNT,
ACCOUNT_FLEXFIELD,
ACCOUNT_DESCRIPTION,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_login_id,
request_id,
TRX_CONTROL_ACCOUNT_FLEXFIELD,
TRX_TAXABLE_ACCOUNT_DESC,--Bug 5650415
TRX_TAXABLE_BALSEG_DESC,--Bug 5650415
TRX_TAXABLE_NATACCT_SEG_DESC, --Bug 5650415
TRX_TAXABLE_ACCOUNT
)
VALUES (zx_rep_actg_ext_t_s.nextval,
agt_detail_tax_line_id(i),
agt_actg_event_type_code(i),
agt_actg_event_number(i),
agt_actg_event_status_flag(i),
agt_actg_category_code(i),
agt_accounting_date(i),
agt_gl_transfer_flag(i),
-- agt_gl_transfer_run_id(i),
agt_actg_header_description(i),
agt_actg_line_num(i),
agt_actg_line_type_code(i),
agt_actg_line_description(i),
agt_actg_stat_amt(i),
agt_actg_error_code(i),
agt_gl_transfer_code(i),
agt_actg_doc_sequence_id(i),
-- agt_actg_doc_sequence_name(i),
agt_actg_doc_sequence_value(i),
agt_actg_party_id(i),
agt_actg_party_site_id(i),
agt_actg_party_type(i),
agt_actg_event_id(i),
agt_actg_header_id(i),
agt_actg_source_id(i),
-- agt_actg_source_table(i),
agt_actg_line_ccid(i),
agt_period_name(i),
GT_TRX_ARAP_BALANCING_SEGMENT(i),
GT_TRX_ARAP_NATURAL_ACCOUNT(i),
GT_TRX_TAXABLE_BAL_SEG(i),
GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),
GT_TRX_TAX_BALANCING_SEGMENT(i),
GT_TRX_TAX_NATURAL_ACCOUNT(i),
GT_ACCOUNT_FLEXFIELD(i),
GT_ACCOUNT_DESCRIPTION(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,
GT_TRX_CONTROL_ACCFLEXFIELD(i),
GT_TRX_TAXABLE_ACCOUNT_DESC(i),--Bug 5650415
GT_TRX_TAXABLE_BALSEG_DESC(i),--Bug 5650415
GT_TRX_TAXABLE_NATACCT_DESC(i), --Bug 5650415
GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info',
'Number of Tax Lines successfully inserted = '||TO_CHAR(l_count));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info.END',
'ZX_AP_ACTG_EXTRACT_PKG: INIT_GT_VARIABLES(-)');
'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info',
g_error_buffer);
END insert_actg_info;
g_last_updated_by := fnd_global.user_id;
g_last_update_login := fnd_global.login_id;
g_last_update_date := sysdate;
/* GT_SHIPPING_TP_ADDRESS_ID.delete;
GT_BILLING_TP_ADDRESS_ID.delete;
GT_SHIPPING_TP_SITE_ID.delete;
GT_BILLING_TP_SITE_ID.delete;
GT_SHIPPING_TP_ID.delete;
GT_BILLING_TRADING_PARTNER_ID.delete;
SELECT ptp.rep_registration_number
FROM xle_tax_associations rel
,zx_party_tax_profile ptp
,xle_etb_profiles etb
WHERE rel.legal_construct_id = etb.establishment_id
AND etb.party_id = ptp.party_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND rel.entity_id = c_org_id
AND rel.legal_parent_id = c_le_id
--P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id
AND rel.LEGAL_CONSTRUCT = 'ESTABLISHMENT'
AND rel.entity_type = 'OPERATING_UNIT'
AND rel.context = 'TAX_CALCULATION'
AND c_tax_date between rel.effective_from and nvl(rel.effective_to,c_tax_date);
SELECT meaning, description
FROM fnd_lookups
WHERE lookup_type = c_lookup_type
AND lookup_code = c_lookup_code;