[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LAST_UPDATED_BY NUMBER(15);
G_LAST_UPDATE_DATE DATE;
G_LAST_UPDATE_LOGIN NUMBER(15);
PROCEDURE insert_actg_info (
P_COUNT IN BINARY_INTEGER);
PROCEDURE UPDATE_REP_DETAIL_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)
IS
/*CURSOR detail_t_cur(c_request_id IN NUMBER) IS
SELECT DETAIL_TAX_LINE_ID,
LEDGER_ID,
INTERNAL_ORGANIZATION_ID,
TRX_ID ,
TRX_TYPE_ID ,
TRX_LINE_CLASS,
TRX_BATCH_SOURCE_ID,
TAX_RATE_ID ,
TAX_RATE_VAT_TRX_TYPE_CODE,
TAX_RATE_REGISTER_TYPE_CODE,
TAX_EXEMPTION_ID ,
TAX_EXCEPTION_ID ,
TAX_LINE_ID ,
TAX_AMT ,
TAX_AMT_FUNCL_CURR ,
TAX_LINE_NUMBER ,
TAXABLE_AMT ,
TAXABLE_AMT_FUNCL_CURR ,
TRX_LINE_ID ,
TAX_EXCEPTION_REASON_CODE ,
EXEMPT_REASON_CODE,
RECONCILIATION_FLAG ,
INTERNAL_ORGANIZATION_ID,
BR_REF_CUSTOMER_TRX_ID,
REVERSE_FLAG,
AMOUNT_APPLIED,
TAX_RATE,
TAX_RATE_CODE,
TAX_TYPE_CODE,
TRX_DATE,
TRX_CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
APPLICATION_ID,
DOC_EVENT_STATUS,
EXTRACT_SOURCE_LEDGER ,
FUNCTIONAL_CURRENCY_CODE,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION,
RECEIPT_CLASS_ID ,
EXCEPTION_RATE,
SHIP_FROM_PARTY_TAX_PROF_ID,
SHIP_FROM_SITE_TAX_PROF_ID,
SHIP_TO_PARTY_TAX_PROF_ID ,
SHIP_TO_SITE_TAX_PROF_ID ,
BILL_TO_PARTY_TAX_PROF_ID,
BILL_TO_SITE_TAX_PROF_ID,
BILL_FROM_PARTY_TAX_PROF_ID,
BILL_FROM_SITE_TAX_PROF_ID,
BILLING_TRADING_PARTNER_ID,
BILLING_TP_SITE_ID,
BILLING_TP_ADDRESS_ID,
SHIPPING_TRADING_PARTNER_ID,
SHIPPING_TP_SITE_ID,
SHIPPING_TP_ADDRESS_ID,
BILL_TO_PARTY_ID,
BILL_TO_PARTY_SITE_ID,
SHIP_TO_PARTY_ID,
SHIP_TO_PARTY_SITE_ID,
HISTORICAL_FLAG
FROM zx_rep_trx_detail_t
WHERE EXTRACT_SOURCE_LEDGER = 'AR'
AND request_id = c_request_id;
SELECT DISTINCT ZX_DTL.DETAIL_TAX_LINE_ID,
ZX_DTL.LEDGER_ID,
ZX_DTL.INTERNAL_ORGANIZATION_ID,
ZX_DTL.TAX_DATE,
ZX_DTL.HQ_ESTB_REG_NUMBER,
ZX_DTL.TRX_ID ,
ZX_DTL.TRX_TYPE_ID ,
ZX_DTL.DOC_SEQ_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 ,
nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
ZX_DTL.TAX_LINE_NUMBER ,
ZX_DTL.TAXABLE_AMT ,
nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
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_DTL.POSTED_DATE,
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,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
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.actg_source_id,
zx_dtl.bank_account_id,
ZX_DTL.tax_determine_date--Bug 5622686
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 = 'AR'
AND zx_dtl.posted_date IS NOT NULL
AND zx_dtl.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
AND xla_ent.entity_code = 'TRANSACTIONS' -- 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 acs.program_code = 'TAX_REPORTING_LEDGER_SALES'
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 xla_ent.application_id = 222
AND xla_head.application_id = xla_ent.application_id
AND xla_head.application_id = xla_line.application_id
AND xla_head.ledger_id = c_ledger_id
-- AND xla_ent.ledger_id = zx_dtl.ledger_id
AND zx_dtl.account_class = 'TAX'
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 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
-- can we get header_id as input parameter to the cursor? In that case we can add following join
-- AND xla_head.ae_header_id = :c_header_id
-- AND xla_dist.tax_line_ref_id IS NOT NULL
-- AND xla_dist.accounting_line_code = 'TAX'
UNION
SELECT ZX_DTL.DETAIL_TAX_LINE_ID,
ZX_DTL.LEDGER_ID,
ZX_DTL.INTERNAL_ORGANIZATION_ID,
ZX_DTL.TAX_DATE,
ZX_DTL.HQ_ESTB_REG_NUMBER,
ZX_DTL.TRX_ID ,
ZX_DTL.TRX_TYPE_ID ,
ZX_DTL.DOC_SEQ_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 ,
nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
ZX_DTL.TAX_LINE_NUMBER ,
ZX_DTL.TAXABLE_AMT ,
nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
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_DTL.POSTED_DATE,
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,
ZX_DTL.POSTED_FLAG, --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,
ZX_DTL.ACTG_SOURCE_ID,
zx_dtl.bank_account_id,
ZX_DTL.tax_determine_date --Bug 5622686
FROM zx_rep_trx_detail_t zx_dtl
WHERE zx_dtl.request_id = c_request_id
AND zx_dtl.extract_source_ledger = 'AR'
AND ((zx_dtl.posted_date IS NULL) OR
(zx_dtl.posted_date IS NOT NULL AND zx_dtl.tax_line_id is NULL));
AND not exists(select 1 from xla_transaction_entities
where source_id_int_1 = zx_dtl.trx_id
and application_id = 2222))); */
SELECT DISTINCT ZX_DTL.DETAIL_TAX_LINE_ID,
ZX_DTL.LEDGER_ID,
ZX_DTL.INTERNAL_ORGANIZATION_ID,
ZX_DTL.TAX_DATE,
ZX_DTL.HQ_ESTB_REG_NUMBER,
ZX_DTL.TRX_ID ,
ZX_DTL.TRX_TYPE_ID ,
ZX_DTL.DOC_SEQ_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 ,
nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
ZX_DTL.TAX_LINE_NUMBER ,
ZX_DTL.TAXABLE_AMT ,
nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
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_DTL.POSTED_DATE,
xla_event.event_type_code,
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
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.actg_source_id,
zx_dtl.bank_account_id,
ZX_DTL.tax_determine_date
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 = 'AR'
AND zx_dtl.posted_date IS NOT NULL
AND zx_dtl.trx_id = xla_ent.source_id_int_1
AND xla_ent.entity_code = 'TRANSACTIONS'
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 acs.program_code = 'TAX_REPORTING_LEDGER_SALES'
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 xla_ent.application_id = 222
AND xla_head.application_id = xla_ent.application_id
AND xla_head.application_id = xla_line.application_id
AND xla_head.ledger_id = c_ledger_id
AND zx_dtl.account_class = 'TAX'
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 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
UNION
SELECT ZX_DTL.DETAIL_TAX_LINE_ID,
ZX_DTL.LEDGER_ID,
ZX_DTL.INTERNAL_ORGANIZATION_ID,
ZX_DTL.TAX_DATE,
ZX_DTL.HQ_ESTB_REG_NUMBER,
ZX_DTL.TRX_ID ,
ZX_DTL.TRX_TYPE_ID ,
ZX_DTL.DOC_SEQ_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 ,
nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
ZX_DTL.TAX_LINE_NUMBER ,
ZX_DTL.TAXABLE_AMT ,
nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
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_DTL.POSTED_DATE,
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,
ZX_DTL.POSTED_FLAG, --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,
ZX_DTL.ACTG_SOURCE_ID,
zx_dtl.bank_account_id,
ZX_DTL.tax_determine_date
FROM zx_rep_trx_detail_t zx_dtl
WHERE zx_dtl.request_id = c_request_id
AND zx_dtl.extract_source_ledger = 'AR'
AND ((zx_dtl.posted_date IS NULL) OR
(zx_dtl.posted_date IS NOT NULL AND zx_dtl.tax_line_id is NULL));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.BEGIN',
'ZX_AR_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_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
'Inside Loop : detail tax line id:'||to_char(gt_detail_tax_line_id(i)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
'i : '||to_char(i)||' L_TRX_CLASS : '||L_TRX_CLASS);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
'get_accounting_info Call:');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
'detail tax line id:'||to_char(gt_detail_tax_line_id(i)));
UPDATE_REP_DETAIL_T(l_count);
insert_actg_info(j);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.END',
'ZX_AR_POPULATE_PKG: UPDATE_ADDITIONAL_INFO(-)');
'ZX.TRL.ZX_AR_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 = '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 = 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 ael.application_id = lnk.application_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 = '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 = 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 ael.application_id = lnk.application_id
AND rownum =1;
SELECT
/*+ leading(gl_dist)*/ 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 = '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 = 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 ael.application_id = lnk.application_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 ael.application_id = lnk.application_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 ael.application_id = lnk.application_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 ael.application_id = lnk.application_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(nvl(lnk.UNROUNDED_ENTERED_DR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_CR,0)),
sum(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))
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 = lnk.ae_header_id
AND aeh.ae_header_id = ael.ae_header_id
AND aeh.ledger_id = c_ledger_id
AND aeh.application_id = lnk.application_id
and ael.application_id = aeh.application_id;
SELECT sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_CR,0)),
sum(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))
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
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 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
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 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
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_CR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)),
sum(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0))
--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 ael.accounting_class_code = 'TAX'
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
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_CR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)),
sum(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0))
--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 ael.accounting_class_code = 'REVENUE'
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
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_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 lnk.application_id = ael.application_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 lnk.application_id = ael.application_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 lnk.application_id = ael.application_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 lnk.application_id = ael.application_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 CODE_COMBINATION_ID INTO l_ccid
FROM AR_DISTRIBUTIONS_ALL
WHERE LINE_ID = P_ACTG_SOURCE_ID;
SELECT CODE_COMBINATION_ID INTO l_ccid
FROM AR_DISTRIBUTIONS_ALL
WHERE LINE_ID = P_TRX_LINE_ID;
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
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 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
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 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
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 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
AND aeh.ae_header_id = lnk.ae_header_id
AND aeh.application_id = lnk.application_id
AND ael.application_id = aeh.application_id;
SELECT name
FROM gl_ledgers
WHERE ledger_id = c_ledger_id
AND rownum = 1;
SELECT rep_registration_number
FROM zx_party_tax_profile
WHERE party_id = c_party_id
AND party_type_code = 'THIRD_PARTY';
SELECT registration_number
FROM zx_party_tax_profile ptp,
zx_registrations reg
WHERE ptp.party_id = c_party_id
AND ptp.party_type_code = 'THIRD_PARTY'
AND reg.party_tax_profile_id = ptp.party_tax_profile_id
ORDER BY default_registration_flag DESC;
SELECT rep_registration_number
FROM zx_party_tax_profile
WHERE party_id = c_party_site_id
AND party_type_code = 'THIRD_PARTY_SITE';
SELECT registration_number
FROM zx_party_tax_profile ptp,
zx_registrations reg
WHERE ptp.party_id = c_party_site_id
AND ptp.party_type_code = 'THIRD_PARTY_SITE'
AND reg.party_tax_profile_id = ptp.party_tax_profile_id
ORDER BY default_registration_flag DESC;
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
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
acct.party_id,
acct_site.party_site_id
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 site_use.site_use_id = c_site_use_id
and ACCT.CUST_ACCOUNT_ID = c_cust_account_id
and site_use.site_use_code = c_ship_bill;
SELECT NVL(br_party.jgzz_fiscal_code, ba_party.jgzz_fiscal_code)
FROM hz_parties br_party,
hz_parties ba_party,
ce_bank_branches_v ce_branch,
ce_bank_accounts ce_accts
WHERE ce_accts.bank_account_id = c_bank_account_id
AND ce_accts.bank_branch_id = ce_branch.branch_party_id
AND ce_branch.branch_party_id = br_party.party_id
AND ce_branch.bank_party_id = ba_party.party_id;
SELECT name
FROM fnd_document_sequences
WHERE doc_sequence_id = c_doc_seq_id;
select decode(GT_APPLICATION_ID(i),222,'CUSTOMER',200,'SUPPLIER')
into p_account_Type_Code
from dual ;
select decode(GT_APPLICATION_ID(i),222,'CUSTOMER',200,'SUPPLIER')
into p_account_Type_Code
from dual ;
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
INTO l_meaning, l_description
FROM ar_lookups
WHERE lookup_code = GT_TAX_TYPE_CODE(i)
AND lookup_type = 'TAX_TYPE' ;
PROCEDURE UPDATE_REP_DETAIL_T(p_count IN NUMBER) IS
i number;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_REP_DETAIL_T.BEGIN',
'ZX_AR_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_SITE_TAX_REG_NUM = GT_BILLING_SITE_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),
HQ_ESTB_REG_NUMBER = GT_TAX_REG_NUM(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),
DOC_SEQ_NAME = GT_DOC_SEQ_NAME(i),
SHIPPING_TP_TAX_REG_NUM = GT_SHIPPING_TP_TAX_REG_NUM(i),
SHIPPING_TP_SITE_TAX_REG_NUM = GT_SHIPPING_SITE_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),
FUNCTIONAL_CURRENCY_CODE = G_FUN_CURRENCY_CODE,
LEDGER_NAME = GT_LEDGER_NAME(i),
BANKING_TP_TAXPAYER_ID = GT_BANKING_TP_TAXPAYER_ID(i),
TAX_AMT = GT_TAX_AMT(i),
TAX_AMT_FUNCL_CURR = GT_TAX_AMT_FUNCL_CURR(i),
TAXABLE_AMT = GT_TAXABLE_AMT(i),
TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i),
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_AR_POPULATE_PKG.UPDATE_REP_DETAIL_T.END',
'ZX_AR_POPULATE_PKG: UPDATE_REP_DETAIL_T(-)');
'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_REP_DETAIL_T',
g_error_buffer);
END UPDATE_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_AR_ACTG_EXTRACT_PKG.insert_actg_info.BEGIN',
'ZX_AR_ACTG_EXTRACT_PKG: insert_actg_info(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_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_BALSEG_DESC,
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) --Bug 5510907
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_BALSEG_DESC(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)); --Bug 5510907
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_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_AR_ACTG_EXTRACT_PKG.insert_actg_info.END',
'ZX_AR_ACTG_EXTRACT_PKG: INIT_GT_VARIABLES(-)');
'ZX.TRL.ZX_AR_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;