[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_REP_DETAIL_T(p_count IN NUMBER);
PROCEDURE UPDATE_REP_ACTG_T(p_count IN NUMBER);
| UPDATE_ADDITIONAL_INFO |
| |
| DESCRIPTION |
| This procedure populates additional extract information |
| AR_TAX_EXTRACT_SUB_ITF |
| |
| Called from |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
+===========================================================================*/
PROCEDURE UPDATE_ADDITIONAL_INFO(
P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
P_MRC_SOB_TYPE IN VARCHAR2)
IS
CURSOR detail_t_cur(c_request_id IN NUMBER) IS
SELECT ZX_DTL.DETAIL_TAX_LINE_ID,
ZX_DTL.LEDGER_ID,
ZX_DTL.INTERNAL_ORGANIZATION_ID,
ZX_DTL.TRX_ID ,
ZX_DTL.TRX_TYPE_ID ,
ZX_DTL.TRX_LINE_CLASS,
ZX_DTL.TRX_BATCH_SOURCE_ID,
ZX_DTL.TAX_RATE_ID ,
ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
ZX_DTL.TAX_RATE_REGISTER_TYPE_CODE,
ZX_DTL.TAX_EXEMPTION_ID ,
ZX_DTL.TAX_EXCEPTION_ID ,
ZX_DTL.TAX_LINE_ID ,
ZX_DTL.TAX_AMT ,
ZX_DTL.TAX_AMT_FUNCL_CURR ,
ZX_DTL.TAX_LINE_NUMBER ,
ZX_DTL.TAXABLE_AMT ,
ZX_DTL.TAXABLE_AMT_FUNCL_CURR ,
ZX_DTL.TRX_LINE_ID ,
ZX_DTL.TAX_EXCEPTION_REASON_CODE ,
ZX_DTL.EXEMPT_REASON_CODE,
ZX_DTL.RECONCILIATION_FLAG ,
ZX_DTL.INTERNAL_ORGANIZATION_ID,
ZX_DTL.BR_REF_CUSTOMER_TRX_ID,
ZX_DTL.REVERSE_FLAG,
ZX_DTL.AMOUNT_APPLIED,
ZX_DTL.TAX_RATE,
ZX_DTL.TAX_RATE_CODE,
ZX_DTL.TAX_TYPE_CODE,
ZX_DTL.TRX_DATE,
ZX_DTL.TRX_CURRENCY_CODE,
ZX_DTL.CURRENCY_CONVERSION_RATE,
ZX_DTL.APPLICATION_ID,
ZX_DTL.DOC_EVENT_STATUS,
ZX_DTL.EXTRACT_SOURCE_LEDGER ,
ZX_DTL.FUNCTIONAL_CURRENCY_CODE,
ZX_DTL.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DTL.PRECISION,
ZX_DTL.RECEIPT_CLASS_ID ,
ZX_DTL.EXCEPTION_RATE,
ZX_DTL.SHIP_FROM_PARTY_TAX_PROF_ID,
ZX_DTL.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DTL.SHIP_TO_PARTY_TAX_PROF_ID ,
ZX_DTL.SHIP_TO_SITE_TAX_PROF_ID ,
ZX_DTL.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DTL.BILL_TO_SITE_TAX_PROF_ID,
ZX_DTL.BILL_FROM_PARTY_TAX_PROF_ID,
ZX_DTL.BILL_FROM_SITE_TAX_PROF_ID,
ZX_DTL.BILLING_TRADING_PARTNER_ID,
ZX_DTL.BILLING_TP_SITE_ID,
ZX_DTL.BILLING_TP_ADDRESS_ID,
ZX_DTL.SHIPPING_TRADING_PARTNER_ID,
ZX_DTL.SHIPPING_TP_SITE_ID,
ZX_DTL.SHIPPING_TP_ADDRESS_ID,
ZX_DTL.BILL_TO_PARTY_ID,
ZX_DTL.BILL_TO_PARTY_SITE_ID,
ZX_DTL.SHIP_TO_PARTY_ID,
ZX_DTL.SHIP_TO_PARTY_SITE_ID,
ZX_DTL.HISTORICAL_FLAG,
ZX_ACTG.ACTG_SOURCE_ID,
ZX_ACTG.ACTG_HEADER_ID,
ZX_ACTG.ACTG_EVENT_ID,
-- ZX_ACTG.ACTG_ENTITY_ID,
ZX_ACTG.ACTG_LINE_CCID
FROM zx_rep_trx_detail_t zx_dtl,
zx_rep_actg_ext_t zx_actg
WHERE EXTRACT_SOURCE_LEDGER = 'AR'
AND zx_dtl.detail_tax_line_id = zx_actg.detail_tax_line_id
AND zx_dtl.request_id = c_request_id;
SELECT chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = P_TRL_GLOBAL_VARIABLES_REC.ledger_id;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.BEGIN',
'ZX_AR_ACTG_POPULATE_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_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
'G_REP_CONTEXT_ID :' ||to_char(G_REP_CONTEXT_ID)||'---'
||to_char(GT_INTERNAL_ORGANIZATION_ID(i)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
'GT_TRX_ID :' ||to_char(GT_TRX_ID(i)));
UPDATE_REP_DETAIL_T(l_count);
UPDATE_REP_ACTG_T(l_count);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.END',
'ZX_AR_ACTG_POPULATE_PKG: UPDATE_ADDITIONAL_INFO(-)');
'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
g_error_buffer);
END UPDATE_ADDITIONAL_INFO;
SELECT SUM(NVL(ARDTAX.AMOUNT_CR,0) - NVL(ARDTAX.AMOUNT_DR,0)),
SUM(NVL(ARDTAX.TAXABLE_ENTERED_CR,0) -
NVL(ARDTAX.TAXABLE_ENTERED_DR,0))
FROM AR_DISTRIBUTIONS_ALL ARDTAX,
AR_RECEIVABLE_APPLICATIONS_ALL APP,
RA_CUSTOMER_TRX_ALL TRXCM
WHERE TRXCM.CUSTOMER_TRX_ID = C_TRX_ID
AND APP.APPLIED_CUSTOMER_TRX_ID = TRXCM.CUSTOMER_TRX_ID
AND APP.RECEIVABLE_APPLICATION_ID = ARDTAX.SOURCE_ID
AND ARDTAX.SOURCE_TABLE = 'RA'
AND ARDTAX.SOURCE_TYPE = DECODE(C_REGISTER_TYPE,'TAX','TAX',
'INTERIM','DEFERRED_TAX',NULL)
AND ARDTAX.TAX_CODE_ID = C_TAX_ID
AND ARDTAX.SOURCE_TABLE_SECONDARY = 'CT'
AND ARDTAX.SOURCE_TYPE_SECONDARY = 'RECONCILE'
GROUP BY C_TRX_ID, C_TAX_ID ;
SELECT
ael.code_combination_id
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.account_class = 'LINE'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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 rownum =1;
SELECT
ael.code_combination_id
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_trx_line_id
AND gl_dist.account_class = 'LINE'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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 rownum =1;
SELECT
ael.code_combination_id
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_trx_line_id
AND gl_dist.account_class = 'LINE'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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 rownum =1;
SELECT
ael.code_combination_id
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.account_class = 'TAX'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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 rownum =1;
SELECT
ael.code_combination_id
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_tax_line_id
AND gl_dist.account_class = 'TAX'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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 rownum =1;
SELECT
ael.code_combination_id
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_tax_line_id
AND gl_dist.cust_trx_line_gl_dist_id = c_tax_line_dist_id
AND gl_dist.account_class = 'TAX'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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 rownum =1;
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 sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND lnk.application_id = 222
AND gl_dist.account_class = 'REV'
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.account_class = 'TAX'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_trx_line_id
AND gl_dist.account_class = 'REV'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_tax_line_id
AND gl_dist.account_class = 'TAX'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
AND lnk.event_id = c_event_id
AND lnk.ae_header_id = c_ae_header_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;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_tax_line_id
AND gl_dist.cust_trx_line_gl_dist_id = c_tax_dist_id
AND gl_dist.account_class = 'TAX'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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 aeh.ae_header_id = ael.ae_header_id
AND aeh.ledger_id = c_ledger_id;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ra_cust_trx_line_gl_dist_all gl_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE gl_dist.customer_trx_id = c_trx_id
AND gl_dist.customer_trx_line_id = c_trx_line_id
AND gl_dist.account_class = 'LINE'
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_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;
SELECT
ael.code_combination_id
FROM ar_distributions_all dist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE dist.line_id = p_actg_source_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND lnk.source_distribution_id_num_1 = dist.line_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 rownum =1;
SELECT ael.code_combination_id
FROM ar_distributions_all dist,
ar_distributions_all taxdist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE taxdist.line_id = p_actg_source_id
AND NVL(dist.source_table,'X') = NVL(taxdist.source_table_secondary,'X')
AND dist.tax_link_id = taxdist.tax_link_id
AND dist.source_id = taxdist.source_id
AND lnk.source_distribution_id_num_1 = dist.line_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
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 rownum =1;
SELECT ael.code_combination_id
FROM ar_distributions_all dist,
ar_distributions_all taxdist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE dist.line_id = p_actg_source_id
AND NVL(dist.source_table,'X') = NVL(taxdist.source_table_secondary,'X')
AND dist.tax_link_id = taxdist.tax_link_id
AND dist.source_id = taxdist.source_id
AND lnk.source_distribution_id_num_1 = taxdist.line_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
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 rownum =1;
SELECT
ael.code_combination_id
FROM ar_distributions_all taxdist,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE taxdist.line_id = p_actg_source_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND lnk.source_distribution_id_num_1 = taxdist.line_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 rownum =1;
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 sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ar_distributions_all dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE dist.line_id = c_actg_source_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND lnk.source_distribution_id_num_1 = dist.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;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM AR_DISTRIBUTIONS_ALL dist,
AR_DISTRIBUTIONS_ALL taxdist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE dist.line_id = c_actg_source_id
AND taxdist.tax_link_id = dist.tax_link_id
AND NVL(taxdist.source_type_secondary,'X') = NVL(dist.source_type,'X')
AND taxdist.source_id = dist.source_id
AND lnk.source_distribution_id_num_1 = taxdist.line_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
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;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM AR_DISTRIBUTIONS_ALL dist,
AR_DISTRIBUTIONS_ALL taxdist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE taxdist.line_id = c_actg_source_id
AND taxdist.tax_link_id = dist.tax_link_id
AND NVL(taxdist.source_type_secondary,'X') = NVL(dist.source_type,'X')
AND taxdist.source_id = dist.source_id
AND lnk.source_distribution_id_num_1 = dist.line_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
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;
SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
FROM ar_distributions_all taxdist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE taxdist.line_id = c_actg_source_id
AND lnk.application_id = 222
AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND lnk.source_distribution_id_num_1 = taxdist.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;
SELECT party_id
FROM zx_party_tax_profile
WHERE PARTY_TAX_PROFILE_ID = c_ptp_id
AND party_type_code = 'THIRD_PARTY';
SELECT party_id
FROM zx_party_tax_profile
WHERE PARTY_TAX_PROFILE_ID = c_ptp_site_id
AND party_type_code = 'THIRD_PARTY_SITE';
SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = c_party_id
AND party_type_code = 'THIRD_PARTY';
SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = c_party_site_id
AND party_type_code = 'THIRD_PARTY_SITE';
select SUBSTRB(PARTY.PARTY_NAME,1,240) ,
DECODE(PARTY.PARTY_TYPE,
'ORGANIZATION',
PARTY.ORGANIZATION_NAME_PHONETIC,
NULL) ,
DECODE(PARTY.PARTY_TYPE,
'ORGANIZATION',
PARTY.SIC_CODE,
NULL) ,
PARTY.PARTY_NUMBER
FROM HZ_PARTIES PARTY
WHERE PARTY.PARTY_ID = c_party_id;
select LOC.CITY,
LOC.COUNTY,
LOC.STATE,
LOC.PROVINCE,
LOC.ADDRESS1,
LOC.ADDRESS2,
LOC.ADDRESS3,
LOC.ADDRESS_LINES_PHONETIC,
LOC.COUNTRY,
LOC.POSTAL_CODE
FROM HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC
WHERE party_site.party_site_id = c_party_site_id
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
SELECT acct.account_number,
acct.global_attribute10,
acct.global_attribute12,
acct_site.global_attribute8,
acct_site.global_attribute9,
site_use.location,
site_use.tax_reference
FROM hz_cust_accounts acct,
hz_cust_site_uses_all site_use ,
hz_cust_acct_sites_all acct_site
WHERE acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID
and acct_site.CUST_ACCT_SITE_ID = site_use.CUST_ACCT_SITE_ID
and acct_site.PARTY_SITE_ID = c_party_site_id
and ACCT.PARTY_ID = c_party_id
and site_use.site_use_code = c_ship_bill;
SELECT party.party_id
FROM HZ_PARTIES PARTY,
hz_cust_accounts acct
WHERE PARTY.PARTY_ID = acct.party_id
And acct.cust_account_id = c_cust_acct_id;
SELECT PARTY_SITE.party_site_id
FROM HZ_PARTY_SITES PARTY_SITE,
hz_cust_acct_sites_all acct_site
WHERE acct_site.cust_acct_site_id = c_cust_acct_site_id
AND party_site.party_site_id = acct_site. party_site_id;
SELECT party_id
FROM zx_party_tax_profile
WHERE PARTY_TAX_PROFILE_ID = c_ptp_id
AND party_type_code = 'THIRD_PARTY';
SELECT party_id
FROM zx_party_tax_profile
WHERE PARTY_TAX_PROFILE_ID = c_ptp_site_id
AND party_type_code = 'THIRD_PARTY_SITE';
SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = c_party_id
AND party_type_code = 'THIRD_PARTY';
SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = c_party_site_id
AND party_type_code = 'THIRD_PARTY_SITE';
select SUBSTRB(PARTY.PARTY_NAME,1,240) ,
DECODE(PARTY.PARTY_TYPE,
'ORGANIZATION',
PARTY.ORGANIZATION_NAME_PHONETIC,
NULL) ,
DECODE(PARTY.PARTY_TYPE,
'ORGANIZATION',
PARTY.SIC_CODE,
NULL) ,
PARTY.PARTY_NUMBER,
PARTY.JGZZ_FISCAL_CODE,
PARTY.TAX_REFERENCE
FROM HZ_PARTIES PARTY
WHERE PARTY.PARTY_ID = c_party_id;
select LOC.CITY,
LOC.COUNTY,
LOC.STATE,
LOC.PROVINCE,
LOC.ADDRESS1,
LOC.ADDRESS2,
LOC.ADDRESS3,
LOC.ADDRESS_LINES_PHONETIC,
LOC.COUNTRY,
LOC.POSTAL_CODE
FROM HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC
WHERE party_site.party_site_id = c_party_site_id
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
SELECT acct.account_number,
acct.global_attribute10,
acct.global_attribute12,
acct_site.global_attribute8,
acct_site.global_attribute9,
site_use.location,
site_use.tax_reference
FROM hz_cust_accounts acct,
hz_cust_site_uses_all site_use ,
hz_cust_acct_sites_all acct_site
WHERE acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID
and acct_site.CUST_ACCT_SITE_ID = site_use.CUST_ACCT_SITE_ID
and acct_site.PARTY_SITE_ID = c_party_site_id
and ACCT.PARTY_ID = c_party_id
and site_use.site_use_code = c_ship_bill;
SELECT acct.party_id
FROM hz_cust_accounts acct
WHERE acct.cust_account_id = c_cust_acct_id;
SELECT acct_site.party_site_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use
WHERE acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND site_use.site_use_id = c_cust_site_use_id;
PROCEDURE UPDATE_REP_DETAIL_T(p_count IN NUMBER) IS
i number;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_DETAIL_T.BEGIN',
'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_DETAIL_T(+)');
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_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),
BILLING_TP_PARTY_NUMBER = GT_BILLING_TP_PARTY_NUMBER(i),
BILLING_TRADING_PARTNER_ID = GT_BILLING_TP_ID(i),
BILLING_TP_SITE_ID = GT_BILLING_TP_SITE_ID(i),
BILLING_TP_ADDRESS_ID = GT_BILLING_TP_ADDRESS_ID(i),
-- BILLING_TP_TAX_REP_FLAG = GT_BILLING_TP_TAX_REP_FLAG(i),
BILLING_TP_SITE_NAME = GT_BILLING_TP_SITE_NAME(i),
GDF_RA_ADDRESSES_BILL_ATT9 = GT_GDF_RA_ADDRESSES_BILL_ATT9(i),
GDF_PARTY_SITES_BILL_ATT8 = GT_GDF_PARTY_SITES_BILL_ATT8(i),
GDF_RA_CUST_BILL_ATT10 = GT_GDF_RA_CUST_BILL_ATT10(i),
GDF_RA_CUST_BILL_ATT12 = GT_GDF_RA_CUST_BILL_ATT12(i),
GDF_RA_ADDRESSES_BILL_ATT8 = GT_GDF_RA_ADDRESSES_BILL_ATT8(i),
SHIPPING_TP_NUMBER = GT_SHIPPING_TP_NUMBER(i),
SHIPPING_TP_TAX_REG_NUM = GT_SHIPPING_TP_TAX_REG_NUM(i),
SHIPPING_TP_TAXPAYER_ID = GT_SHIPPING_TP_TAXPAYER_ID(i),
-- SHIPPING_TP_SITE_NAME_ALT = GT_SHIPPING_TP_SITE_NAME_ALT(i),
SHIPPING_TP_NAME = GT_SHIPPING_TP_NAME(i),
SHIPPING_TP_NAME_ALT = GT_SHIPPING_TP_NAME_ALT(i),
SHIPPING_TP_SIC_CODE = GT_SHIPPING_TP_SIC_CODE(i),
SHIPPING_TP_CITY = GT_SHIPPING_TP_CITY(i),
SHIPPING_TP_COUNTY = GT_SHIPPING_TP_COUNTY(i),
SHIPPING_TP_STATE = GT_SHIPPING_TP_STATE(i),
SHIPPING_TP_PROVINCE = GT_SHIPPING_TP_PROVINCE(i),
SHIPPING_TP_ADDRESS1 = GT_SHIPPING_TP_ADDRESS1(i),
SHIPPING_TP_ADDRESS2 = GT_SHIPPING_TP_ADDRESS2(i),
SHIPPING_TP_ADDRESS3 = GT_SHIPPING_TP_ADDRESS3(i),
-- SHIPPING_TP_ADDR_LINES_ALT = GT_SHIPPING_TP_ADDR_LINES_ALT(i),
SHIPPING_TP_COUNTRY = GT_SHIPPING_TP_COUNTRY(i),
SHIPPING_TP_POSTAL_CODE = GT_SHIPPING_TP_POSTAL_CODE(i),
-- SHIPPING_TP_PARTY_NUMBER = GT_SHIPPING_TP_PARTY_NUMBER(i),
-- SHIPPING_TRADING_PARTNER_ID = GT_SHIPPING_TRADING_PARTNER_ID(i),
SHIPPING_TP_SITE_ID = GT_SHIPPING_TP_SITE_ID(i),
SHIPPING_TP_ADDRESS_ID = GT_SHIPPING_TP_ADDRESS_ID(i),
-- SHIPPING_TP_TAX_REP_FLAG = GT_SHIPPING_TP_TAX_REP_FLAG(i),
SHIPPING_TP_SITE_NAME = GT_SHIPPING_TP_SITE_NAME(i),
GDF_RA_ADDRESSES_SHIP_ATT9 = GT_GDF_RA_ADDRESSES_SHIP_ATT9(i),
GDF_PARTY_SITES_SHIP_ATT8 = GT_GDF_PARTY_SITES_SHIP_ATT8(i),
GDF_RA_CUST_SHIP_ATT10 = GT_GDF_RA_CUST_SHIP_ATT10(i),
GDF_RA_CUST_SHIP_ATT12 = GT_GDF_RA_CUST_SHIP_ATT12(i),
GDF_RA_ADDRESSES_SHIP_ATT8 = GT_GDF_RA_ADDRESSES_SHIP_ATT8(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_VAT_TRX_TYPE_DESC = GT_TAX_RATE_VAT_TRX_TYPE_DESC(i),
TAXABLE_AMT = GT_TAXABLE_AMT(i),
TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i),
TAX_AMT = GT_TAX_AMT(i),
TAX_AMT_FUNCL_CURR = GT_TAX_AMT_FUNCL_CURR(i)
WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_DETAIL_T.END',
'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_DETAIL_T(-)');
'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_DETAIL_T',
g_error_buffer);
END UPDATE_REP_DETAIL_T;
PROCEDURE UPDATE_REP_ACTG_T(p_count IN NUMBER) IS
i number;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG_T.BEGIN',
'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_ACTG_T(+)');
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)
WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG__T.END',
'ZX_AR_ACTG_POPULATE_PKG: UPDATE_REP_ACTG_T(-)');
'ZX.TRL.ZX_AR_ACTG_POPULATE_PKG.UPDATE_REP_ACTG_T',
g_error_buffer);
END UPDATE_REP_ACTG_T;