[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 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 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 ,
TAXABLE_ITEM_SOURCE_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
0, --tax_amt, --Bug 5409170
taxable_amt_funcl_curr,
0, --tax_amt_funcl_curr ,
tax_regime_code,
tax,
tax_status_code,
tax_rate_id,
ipv_priority
-- tax_type_code
FROM ( SELECT /*+ leading(zx_dtl,xla_ent,xla_event,xla_head) full(zx_dtl) parallel(zx_dtl,4) */
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.TAXABLE_ITEM_SOURCE_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,
DECODE(xla_head.gl_transfer_status_code,'Y','Y','NT','Y','N') 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
0, --zx_dtl.tax_amt, --Bug 5409170
nvl(zx_dtl.taxable_amt_funcl_curr,zx_dtl.taxable_amt) taxable_amt_funcl_curr,
0, --nvl(zx_dtl.tax_amt_funcl_curr,zx_dtl.tax_amt) tax_amt_funcl_curr,
zx_dtl.tax_regime_code,
zx_dtl.tax,
zx_dtl.tax_status_code,
zx_dtl.tax_rate_id,
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 xla_ent.application_id = 200
AND nvl(xla_ent.source_id_int_1,-99) = zx_dtl.trx_id -- Accounting Joins
AND xla_ent.ledger_id = zx_dtl.ledger_id
AND xla_ent.entity_code = 'AP_INVOICES' -- Check this condition
AND xla_event.application_id = xla_ent.application_id
AND xla_event.entity_id = xla_ent.entity_id
AND xla_head.application_id = xla_event.application_id
AND xla_head.event_id = xla_event.event_id
AND xla_head.balance_type_code = 'A'
AND xla_head.ledger_id = c_ledger_id
AND xla_line.application_id = xla_head.application_id
AND xla_line.ae_header_id = xla_head.ae_header_id
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.accounting_class_code = xla_line.accounting_class_code
AND acs.program_owner_code = asd.program_owner_code
AND acs.program_code = asd.program_code
AND acs.assignment_owner_code = asd.assignment_owner_code
AND acs.assignment_code = asd.assignment_code
AND asd.enabled_flag = 'Y'
AND zx_dtl.tax_line_id = xla_dist.tax_line_ref_id
AND zx_dtl.actg_source_id = xla_dist.tax_rec_nrec_dist_ref_id
AND xla_head.ledger_id = xla_line.ledger_id
--AND xla_line.ae_header_id = xla_dist.ae_header_id
AND xla_head.ae_header_id = xla_dist.ae_header_id
AND xla_line.ae_line_num = xla_dist.ae_line_num
AND xla_line.application_id = xla_dist.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 IN ('PREPAY_APPLICATION', 'PREPAYMENT INVOICES'))
)
) ipv
WHERE ipv.ipv_priority = 1
UNION ALL
SELECT /*+ FULL(zx_dtl) parallel(zx_dtl) */
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.TAXABLE_ITEM_SOURCE_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
0, --zx_dtl.tax_amt , --Bug 5409170
nvl(zx_dtl.taxable_amt_funcl_curr,zx_dtl.TAXABLE_AMT) ,--Bug 5405785
0, --nvl(zx_dtl.tax_amt_funcl_curr,zx_dtl.tax_amt), --Bug 5405785
zx_dtl.tax_regime_code,
zx_dtl.tax,
zx_dtl.tax_status_code,
zx_dtl.tax_rate_id,
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 in ('A', 'Y') 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));
SELECT name
INTO l_gl_name
FROM gl_ledgers
WHERE ledger_id = l_ledger_id
AND rownum=1;
UPDATE zx_rep_trx_detail_t
SET tax_amt = 0,
tax_amt_funcl_curr = 0
WHERE request_id = G_REQUEST_ID
AND extract_source_ledger = 'AP' ;
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
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',
'Populate 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)));
i,--Need to change this to j if inserting into accouting table
gt_actg_line_num(i),
GT_TRX_LINE_DIST_ID(i)) ;
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',' i : '||to_Char(i)||
'Taxable Amt : '|| to_char(GT_TAXABLE_AMT(i)) ||'TAXABLE_AMT_FUNCL_CURR : '||GT_TAXABLE_AMT_FUNCL_CURR(i));
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',' i : '||to_Char(i)||
'Tax Amt : '|| to_char(GT_TAX_AMT(i)) ||'TAX_AMT_FUNCL_CURR : '||GT_TAX_AMT_FUNCL_CURR(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 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,
DECODE(P_REPORT_NAME,'ZXCLPPLR',
NUM_1099||'-'||GLOBAL_ATTRIBUTE12,
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 /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
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_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.application_id = lnk.application_id
AND rownum =1;
SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
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.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.application_id = lnk.application_id
AND rownum =1;
SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
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 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_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.application_id = lnk.application_id
AND ael.accounting_class_code <> 'LIABILITY'
AND rownum =1;
SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
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 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_id_num_1 = zx_dist.trx_line_dist_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.application_id = lnk.application_id
AND ael.ledger_id = c_ledger_id
AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY','PURCHASE_ORDER','QV')
AND rownum =1;
SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) */
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.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.application_id = lnk.application_id
AND rownum =1;
SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) */
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.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.application_id = lnk.application_id
AND rownum =1;
SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) */
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.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.application_id = lnk.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) ))
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
AND lnk.application_id = 200
AND lnk.source_distribution_type in ('AP_INV_DIST','AP_PREPAY')
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 ael.application_id = lnk.application_id
and ael.ae_header_id = lnk.ae_header_id
and ael.ae_line_num = lnk.ae_line_num ;
SELECT SUM(decode(zx_dist.reverse_flag, 'Y',
(abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) -(nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
(decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
(nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0)))),
SUM(decode(zx_dist.reverse_flag, 'Y',
(abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)))*
(decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))))
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.rec_nrec_tax_dist_id = c_tax_dist_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_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND ael.application_id = lnk.application_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.accounting_class_code NOT IN ('NRTAX','RTAX','LIABILITY','EXCHANGE_RATE_VARIANCE');
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)))
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE zx_dist.rec_nrec_tax_dist_id = c_tax_dist_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_header_id = c_ae_header_id
AND lnk.event_id = c_event_id
AND ael.application_id = lnk.application_id
AND ael.ae_header_id = lnk.ae_header_id
AND ael.ae_line_num = lnk.ae_line_num
AND ael.accounting_class_code = 'EXCHANGE_RATE_VARIANCE';
SELECT SUM(decode(zx_dist.reverse_flag, 'Y',
(abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) -(nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
(decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
(nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0)))),
SUM(decode(zx_dist.reverse_flag, 'Y',
(abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)))*
(decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))))
-- SELECT SUM( abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) - (nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
-- (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
-- SUM( abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))) *
-- (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_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.alloc_to_dist_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.alloc_to_dist_id_num_1<>lnk.source_distribution_id_num_1
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(decode(zx_dist.reverse_flag, 'Y',
(abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) -(nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
(decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
(nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0)))),
SUM(decode(zx_dist.reverse_flag, 'Y',
(abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)))*
(decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))))
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.source_distribution_id_num_1 = zx_dist.trx_line_dist_id OR
-- lnk.alloc_to_dist_id_num_1 = zx_dist.trx_line_dist_id
-- AND lnk.alloc_to_dist_id_num_1<>lnk.source_distribution_id_num_1 )
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_header_id = c_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','PURCHASE_ORDER')
AND ael.ledger_id = c_ledger_id
AND ROWNUM = 1;
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))
FROM ap_invoice_distributions_all ap_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE ap_dist.invoice_id = c_trx_id
AND ap_dist.invoice_line_number = c_trx_line_id
AND ap_dist.invoice_distribution_id = c_trx_line_dist_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
AND lnk.source_distribution_id_num_1 = ap_dist.invoice_distribution_id
AND lnk.event_id = ap_dist.accounting_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(aphd.amount), -- discount amount (entered)
sum(aphd.paid_base_amount) -- discount amount (accounted)
FROM ap_invoice_distributions_all aid,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph,
zx_rec_nrec_dist zx_dist
WHERE aid.invoice_id = p_trx_id
AND aid.invoice_id = aip.invoice_id
AND zx_dist.recoverable_flag = p_tax_rec_flag
AND aip.invoice_payment_id = aphd.invoice_payment_id
--AND aid.line_type_lookup_code in ('REC_TAX', 'NONREC_TAX')
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
and aphd.ACCOUNTING_EVENT_ID = aph.ACCOUNTING_EVENT_ID
and aphd.PAYMENT_HISTORY_ID = aph.PAYMENT_HISTORY_ID
and zx_dist.trx_id = aid.invoice_id
and ((zx_dist.trx_line_dist_id = aid.invoice_distribution_id) OR
((aid.line_type_lookup_code='IPV') and (zx_dist.trx_line_dist_id = aid.related_id)))
and zx_dist.application_id = 200
and zx_dist.tax_regime_code = p_tax_regime_code
and zx_dist.tax = p_tax
and zx_dist.tax_status_code = p_tax_status_code
and zx_dist.tax_rate_id = p_tax_rate_id
AND zx_dist.entity_code = 'AP_INVOICES'
and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
UNION ALL
-- 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 /*+ leading (aid,aip) */
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.line_type_lookup_code = 'ITEM'
AND aip.invoice_id = aid.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 xal.application_id = 200
AND xal.ledger_id = p_ledger_id
AND xal.accounting_class_code = 'DISCOUNT'
AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
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_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph,
zx_rec_nrec_dist zx_dist
WHERE aid.invoice_id = p_trx_id
AND aid.invoice_id = aip.invoice_id
AND aid.tax_recoverable_flag = p_tax_rec_flag
AND aip.invoice_payment_id = aphd.invoice_payment_id
AND aid.line_type_lookup_code in ('REC_TAX', 'NONREC_TAX')
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
and aphd.ACCOUNTING_EVENT_ID = aph.ACCOUNTING_EVENT_ID
and aphd.PAYMENT_HISTORY_ID = aph.PAYMENT_HISTORY_ID
and zx_dist.trx_id = aid.invoice_id
and zx_dist.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
and zx_dist.recoverable_flag = aid.tax_recoverable_flag
and zx_dist.application_id = 200
and zx_dist.tax_regime_code = p_tax_regime_code
and zx_dist.tax = p_tax
and zx_dist.tax_status_code = p_tax_status_code
and zx_dist.tax_rate_id = p_tax_rate_id
AND zx_dist.entity_code = 'AP_INVOICES'
and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
UNION ALL
SELECT /*+ leading(aid,aip) */
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.line_type_lookup_code = 'TAX'
AND aip.invoice_id = aid.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 xal.application_id = 200
AND xal.accounting_class_code = 'DISCOUNT'
AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
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.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 ALL
SELECT /*+ leading (aid,aip) */
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_distribution_id = p_trx_line_id
AND aip.invoice_id = aid.invoice_id
AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
AND xal.application_id = 200
AND xal.accounting_class_code = 'DISCOUNT'
AND xal.ledger_id = p_ledger_id
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.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 ALL
SELECT /*+ leading (aid,aip) */
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_distribution_id = p_tax_line_id
AND aip.invoice_id = aid.invoice_id
AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
AND xal.application_id = 200
AND xal.ledger_id = p_ledger_id
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,
zx_rec_nrec_dist zx_dist
WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
AND aid.invoice_id = aip.invoice_id
and ((zx_dist.trx_line_dist_id = aid.invoice_distribution_id) OR
((aid.line_type_lookup_code='IPV') and (zx_dist.trx_line_dist_id = aid.related_id)))
AND zx_dist.rec_nrec_tax_dist_id = p_dist_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
and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
UNION ALL
SELECT SUM(NVL(xal.entered_dr,0) - NVL(xal.entered_cr,0)) ,
-- discount entered amount (replace this with new xla colum names)
SUM(NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0))
-- 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_distribution_id = p_trx_line_dist_id
AND aip.invoice_id = aid.invoice_id
AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
AND xal.application_id = 200
AND xal.accounting_class_code = 'DISCOUNT'
AND xal.ledger_id = p_ledger_id
AND aph.check_id = aip.check_id
AND nvl(aph.historical_flag, 'N') = 'Y';
SELECT sum(aphd.amount),
sum(aphd.paid_base_amount)
FROM ap_invoice_distributions_all aid,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd,
ap_payment_history_all aph
WHERE aid.invoice_id = p_trx_id
AND aid.invoice_id = aip.invoice_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
AND aid.line_type_lookup_code not in ('REC_TAX', 'NONREC_TAX');
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.detail_tax_dist_id = p_dist_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
AND aphd.ACCOUNTING_EVENT_ID = aph.ACCOUNTING_EVENT_ID
AND aphd.PAYMENT_HISTORY_ID = aph.PAYMENT_HISTORY_ID
and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
UNION ALL
SELECT SUM(NVL(xal.entered_dr,0) - NVL(xal.entered_cr,0)) ,
-- discount entered amount (replace this with new xla colum names)
SUM(NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0))
-- 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_distribution_id = p_tax_line_id
AND aid.detail_tax_dist_id = p_dist_id
AND aip.invoice_id = aid.invoice_id
AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
AND xal.application_id = 200
AND xal.ledger_id = p_ledger_id
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 /*+ INDEX (ZX_REP_TRX_DETAIL_T ZX_REP_TRX_DETAIL_T_U1)*/ 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),
BILL_FROM_PARTY_SITE_ID = GT_BILL_FROM_PARTY_SITE_ID(i),
HQ_ESTB_REG_NUMBER = GT_TAX_REG_NUM(i),
BILL_FROM_PARTY_ID = GT_BILL_FROM_PARTY_ID(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),
FUNCTIONAL_CURRENCY_CODE = G_FUN_CURRENCY_CODE,
LEDGER_NAME = GT_LEDGER_NAME(i),
TAXABLE_DISC_AMT = GT_TAXABLE_DISC_AMT(i),
TAXABLE_DISC_AMT_FUNCL_CURR = GT_TAXABLE_DISC_AMT_FUNCL_CURR(i),
TAX_DISC_AMT = GT_TAX_DISC_AMT(i),
TAX_DISC_AMT_FUNCL_CURR = GT_TAX_DISC_AMT_FUNCL_CURR(i),
TAX_AMT = TAX_AMT + GT_TAX_AMT(i),
TAX_AMT_FUNCL_CURR = 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);
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;
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;
SELECT meaning
INTO l_meaning
FROM fnd_lookups
WHERE lookup_type = 'ZX_TRL_REGISTER_TYPE'
AND lookup_code = p_register_type;
UPDATE zx_rep_trx_detail_t zx_dtl
SET tax_rate_code_reg_type_mng = l_meaning
,tax_rate_register_type_code =
DECODE(tax_recoverable_flag,'Y','TAX'
,'NON-RECOVERABLE')
,(tax_rate_code_vat_trx_type_mng,tax_rate_vat_trx_type_desc) =
(SELECT meaning, description
FROM fnd_lookups
WHERE lookup_type = 'ZX_JEBE_VAT_TRANS_TYPE'
AND lookup_code = zx_dtl.tax_rate_vat_trx_type_code
)
,trx_class_mng = CASE WHEN trx_line_class IS NULL THEN NULL
ELSE
(SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
AND lookup_code = zx_dtl.trx_line_class
)
END
,tax_type_mng = CASE WHEN tax_type_code IS NULL THEN NULL
ELSE
(SELECT meaning
FROM fnd_lookups
WHERE lookup_type = 'ZX_TAX_TYPE_CATEGORY'
AND lookup_code = zx_dtl.tax_type_code
)
END
WHERE zx_dtl.request_id = G_REQUEST_ID
AND zx_dtl.extract_source_ledger = 'AP';