[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 /*+ leading(zx_dtl,xla_ent,XLA_EVENT) parallel(zx_dtl) */
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_RATE_CODE_NAME,
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,
DECODE(xla_head.gl_transfer_status_code,'Y','Y','NT','Y','N'),
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
zx_dtl.def_rec_settlement_option_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 = 'AR'
AND ( ( zx_dtl.account_class = 'TAX'
AND xla_ent.entity_code = 'TRANSACTIONS'
AND xla_dist.tax_line_ref_id = zx_dtl.tax_line_id
)
OR
(xla_ent.entity_code IN ('RECEIPTS', 'ADJUSTMENTS', 'BILLS_RECEIVABLE')
AND NVL(ZX_DTL.AR_CASH_RECEIPT_REVERSE_STATUS,'NREV') NOT IN ('REV','CC_CHARGEBACK_REV','NSF','STOP')
)
)
AND zx_dtl.posted_date IS NOT NULL
-- AND zx_dtl.ledger_id = xla_ent.ledger_id
AND xla_ent.application_id = 222
AND xla_ent.ledger_id = c_primary_ledger_id
AND xla_ent.source_id_int_1 = zx_dtl.trx_id -- Accounting Joins
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.ledger_id = c_ledger_id
AND xla_head.balance_type_code = 'A'
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_REPORTING_LEDGER_SALES'
AND acs.accounting_class_code = xla_line.accounting_class_code
AND asd.enabled_flag = 'Y'
AND asd.program_owner_code = acs.program_owner_code
AND asd.program_code = acs.program_code
AND asd.assignment_owner_code = acs.assignment_owner_code
AND asd.assignment_code = acs.assignment_code
AND xla_dist.application_id = xla_line.application_id
AND xla_dist.ae_header_id = xla_line.ae_header_id
AND xla_dist.ae_line_num = xla_line.ae_line_num
AND xla_dist.source_distribution_id_num_1 = zx_dtl.actg_source_id
UNION
SELECT /*+ FULL(zx_dtl) parallel(zx_dtl) */
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_RATE_CODE_NAME,
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
ZX_DTL.DEF_REC_SETTLEMENT_OPTION_CODE
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
NVL(ZX_DTL.AR_CASH_RECEIPT_REVERSE_STATUS,'NREV') IN ('REV','CC_CHARGEBACK_REV','NSF','STOP')));
AND not exists(select 1 from xla_transaction_entities
where source_id_int_1 = zx_dtl.trx_id
and application_id = 2222))); */
SELECT /*+ leading(zx_dtl,xla_ent,XLA_EVENT) parallel(zx_dtl) */
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_RATE_CODE_NAME,
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,
DECODE(xla_head.gl_transfer_status_code,'Y','Y','NT','Y','N'),
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,
ZX_DTL.DEF_REC_SETTLEMENT_OPTION_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 = 'AR'
AND zx_dtl.account_class = 'TAX'
AND zx_dtl.posted_date IS NOT NULL
-- AND zx_dtl.ledger_id = xla_ent.ledger_id
AND xla_ent.application_id = 222
AND xla_ent.entity_code = 'TRANSACTIONS'
AND xla_ent.ledger_id = c_primary_ledger_id
AND xla_ent.source_id_int_1 = zx_dtl.trx_id
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.ledger_id = c_ledger_id
AND xla_head.balance_type_code = 'A'
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_REPORTING_LEDGER_SALES'
AND acs.accounting_class_code = xla_line.accounting_class_code
AND asd.enabled_flag = 'Y'
AND asd.program_owner_code = acs.program_owner_code
AND asd.program_code = acs.program_code
AND asd.assignment_owner_code = acs.assignment_owner_code
AND asd.assignment_code = acs.assignment_code
-- AND zx_dtl.actg_source_id = xla_dist.source_distribution_id_num_1
AND xla_dist.application_id = xla_line.application_id
AND xla_dist.ae_header_id = xla_line.ae_header_id
AND xla_dist.ae_line_num = xla_line.ae_line_num
AND xla_dist.tax_line_ref_id = zx_dtl.tax_line_id
UNION
SELECT /*+ FULL(zx_dtl) parallel(zx_dtl) */
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_RATE_CODE_NAME,
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,
ZX_DTL.DEF_REC_SETTLEMENT_OPTION_CODE
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));
g_last_updated_by := fnd_global.user_id;
g_last_update_login := fnd_global.login_id;
g_last_update_date := sysdate;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info.BEGIN',
'ZX_AR_POPULATE_PKG: UPDATE_ADDITIONAL_INFO(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'Request ID : '||to_char(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID) ||
' ; Reporting Ledger : '||to_char(p_trl_global_variables_rec.reporting_ledger_id) ||
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
SELECT NVL(cur.minimum_accountable_unit, power(10, (-1 * precision)))
INTO l_mau
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = p_trl_global_variables_rec.ledger_id
AND cur.currency_code = sob.currency_code;
UPDATE zx_rep_trx_detail_t DET
SET taxable_amt_funcl_curr = ROUND(((SELECT (zl.line_amt * DIST.PERCENT)/100
FROM zx_lines zl,
RA_CUST_TRX_LINE_GL_DIST_ALL DIST
WHERE ZL.TAX_LINE_ID = DET.TAX_LINE_ID
AND DIST.CUST_TRX_LINE_GL_DIST_ID = DET.ACTG_SOURCE_ID
AND DIST.CUSTOMER_TRX_ID = ZL.TRX_ID) * currency_conversion_rate) / l_mau) * l_mau
WHERE request_id = p_trl_global_variables_rec.request_id
AND extract_source_ledger = 'AR'
AND ledger_id = p_trl_global_variables_rec.ledger_id
AND NVL(functional_currency_code,'X') <> NVL(trx_currency_code,'X')
AND currency_conversion_rate <> 1
AND exists (SELECT 1 FROM zx_lines lines
WHERE lines.tax_line_id = DET.tax_line_id
AND lines.manually_entered_flag = 'Y'
AND lines.tax_amt_included_flag = 'N');
DELETE FROM zx_rep_trx_detail_t dtl1
WHERE dtl1.rowid <>( SELECT min(dtl2.rowid)
FROM zx_rep_trx_detail_t dtl2
WHERE dtl2.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl2.trx_id = dtl1.trx_id
AND dtl2.tax_line_number = dtl1.tax_line_number
AND dtl2.tax_rate_id = dtl1.tax_rate_id
and dtl2.TAXABLE_ITEM_SOURCE_ID = dtl1.TAXABLE_ITEM_SOURCE_ID
AND dtl2.EVENT_CLASS_CODE = dtl1.EVENT_CLASS_CODE
AND dtl2.APPLIED_FROM_EVENT_CLASS_CODE = dtl1.APPLIED_FROM_EVENT_CLASS_CODE
AND dtl2.ACTG_SOURCE_ID = dtl1.ACTG_SOURCE_ID
AND dtl2.application_id = dtl1.application_id
GROUP BY dtl2.request_id,dtl2.trx_id,
dtl2.EVENT_CLASS_CODE, dtl2.tax_line_number,
dtl2.ACTG_SOURCE_ID, dtl2.tax_rate_id
HAVING count(dtl2.actg_source_id) >=2
)
AND dtl1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl1.EVENT_CLASS_CODE in ('EDISC','UNEDISC','APP')
AND dtl1.APPLIED_FROM_EVENT_CLASS_CODE = 'APP'
AND dtl1.application_id = 222 ;
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'Duplicate rows deleted using 1st Query: '|| to_char(SQL%ROWCOUNT));
DELETE FROM zx_rep_trx_detail_t dtl1
WHERE trx_line_id <>( SELECT min(dtl2.trx_line_id )
FROM zx_rep_trx_detail_t dtl2
WHERE dtl2.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl2.trx_id = dtl1.trx_id
AND NVL(dtl2.tax_line_number,1) = NVL(dtl1.tax_line_number,1)
AND dtl2.tax_rate_id = dtl1.tax_rate_id
AND dtl2.actg_source_id = dtl1.actg_source_id
and dtl2.TAXABLE_ITEM_SOURCE_ID = dtl1.TAXABLE_ITEM_SOURCE_ID
AND dtl2.EVENT_CLASS_CODE = dtl1.EVENT_CLASS_CODE
AND dtl2.APPLIED_FROM_EVENT_CLASS_CODE = dtl1.APPLIED_FROM_EVENT_CLASS_CODE
AND dtl2.application_id = dtl1.application_id
GROUP BY dtl2.request_id,dtl2.trx_id,dtl2.EVENT_CLASS_CODE,
dtl2.tax_line_number,dtl2.actg_source_id,
dtl2.tax_rate_id
HAVING count(distinct dtl2.trx_line_id ) >=2
)
AND dtl1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl1.EVENT_CLASS_CODE in ('EDISC','UNEDISC','APP')
AND dtl1.APPLIED_FROM_EVENT_CLASS_CODE = 'APP'
AND dtl1.application_id = 222;
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'Duplicate rows deleted using 2nd Query: '|| to_char(SQL%ROWCOUNT));
DELETE FROM zx_rep_trx_detail_t dtl1
WHERE trx_line_id <>( SELECT min(dtl2.trx_line_id)
FROM zx_rep_trx_detail_t dtl2
WHERE dtl2.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl2.trx_id = dtl1.trx_id
and dtl2.TAXABLE_ITEM_SOURCE_ID = dtl1.TAXABLE_ITEM_SOURCE_ID
AND dtl2.EVENT_CLASS_CODE = dtl1.EVENT_CLASS_CODE
AND dtl2.application_id = dtl1.application_id
GROUP BY dtl2.request_id,dtl2.trx_id
HAVING count(distinct dtl2.applied_to_trx_line_id) >=2)
AND dtl1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl1.EVENT_CLASS_CODE ='INVOICE_ADJUSTMENT'
AND dtl1.application_id = 222;
DELETE FROM zx_rep_trx_detail_t dtl1
WHERE trx_line_id <>( SELECT min(dtl2.trx_line_id)
FROM zx_rep_trx_detail_t dtl2
WHERE dtl2.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl2.trx_id = dtl1.trx_id
AND dtl2.tax_line_number = dtl1.tax_line_number
AND dtl2.tax_rate_id = dtl1.tax_rate_id
and dtl2.TAXABLE_ITEM_SOURCE_ID = dtl1.TAXABLE_ITEM_SOURCE_ID
AND dtl2.EVENT_CLASS_CODE = dtl1.EVENT_CLASS_CODE
-- AND dtl2.APPLIED_FROM_EVENT_CLASS_CODE = dtl1.APPLIED_FROM_EVENT_CLASS_CODE
AND dtl2.application_id = dtl1.application_id
-- AND dtl2.ref_cust_trx_line_gl_dist_id <> dtl1.ref_cust_trx_line_gl_dist_id
GROUP BY dtl2.request_id,dtl2.trx_id, dtl2.EVENT_CLASS_CODE, dtl2.tax_line_number, dtl2.tax_rate_id
HAVING count(distinct dtl2.trx_line_id) >=2)
AND dtl1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl1.EVENT_CLASS_CODE ='ADJ'
--AND dtl1.APPLIED_FROM_EVENT_CLASS_CODE = 'ADJ'
AND dtl1.application_id = 222;
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'Before Dist cursor Opened -- Delete Duplicate rows for AR Adjustments : '||to_char(l_count));
DELETE FROM zx_rep_trx_detail_t dtl1
WHERE trx_line_id <>( SELECT min(dtl2.trx_line_id)
FROM zx_rep_trx_detail_t dtl2
WHERE dtl2.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl2.trx_id = dtl1.trx_id
AND NVL(dtl2.tax_line_number,0) = NVL(dtl1.tax_line_number,0)
AND NVL(dtl2.tax_rate_id,0) = NVL(dtl1.tax_rate_id,0)
AND dtl2.taxable_item_source_id = dtl1.taxable_item_source_id
AND dtl2.event_class_code = dtl1.event_class_code
AND dtl2.application_id = dtl1.application_id
GROUP BY dtl2.request_id,dtl2.trx_id, dtl2.event_class_code, dtl2.tax_line_number, dtl2.tax_rate_id
HAVING count(distinct dtl2.trx_line_id) >=2)
AND dtl1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND dtl1.event_class_code ='ADJ'
AND dtl1.application_id = 222;
SELECT name
INTO l_gl_name
FROM gl_ledgers
WHERE ledger_id = l_ledger_id;
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'Dist cursor Opened -- detail_t_cur : ');
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
'Trx / Line cursor Opened -- detail_t_cur_trx_line : ');
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'Row Count After fetch : ' ||to_char(l_count));
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'Inside Loop : i : '||to_char(i)||' ; ' ||
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'get_accounting_info Call for detail tax line id:'||to_char(gt_detail_tax_line_id(i))
);
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AR_POPULATE_PKG.update_additional_info',
'inv_actg_amounts call :GT_TAXABLE_AMT, GT_TAXABLE_AMT_FUNCL_CURR'||to_char(GT_TAXABLE_AMT(i))||
'i='||to_char(i)||' j='||to_char(i)
||'-'||to_char(GT_TAXABLE_AMT_FUNCL_CURR(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 APP.CASH_RECEIPT_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 ael.accounting_class_code = 'REVENUE'
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 ael.accounting_class_code = 'REVENUE'
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 ael.accounting_class_code = 'REVENUE'
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 ael.application_id = lnk.application_id
AND ael.accounting_class_code = 'REVENUE'
AND ael.ledger_id = c_ledger_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 = 'UNEARN'
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 ael.accounting_class_code = 'UNEARNED_REVENUE'
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 ael.accounting_class_code <> 'RECEIVABLE'
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 ael.accounting_class_code <> 'RECEIVABLE'
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 ael.accounting_class_code <> 'RECEIVABLE'
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(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 SIGN(GL_DIST_TAX.PERCENT)* V.TAXABLE_AMT,
SIGN(GL_DIST_TAX.PERCENT)* V.TAXABLE_FUNC_AMT
FROM RA_CUST_TRX_LINE_GL_DIST_ALL GL_DIST_TAX,
(SELECT SUM(NVL(LNK.UNROUNDED_ENTERED_CR,0) - NVL(LNK.UNROUNDED_ENTERED_DR,0)) TAXABLE_AMT,
sum(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)) TAXABLE_FUNC_AMT
FROM ra_cust_trx_line_gl_dist_all gl_dist_line,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE gl_dist_line.customer_trx_id = c_trx_id
AND gl_dist_line.customer_trx_line_id = c_trx_line_id
-- AND gl_dist_line.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 in ('REVENUE','UNEARNED_REVENUE','SUSPENSE','UNBILL')
AND lnk.source_distribution_id_num_1 = gl_dist_line.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
) V
WHERE gl_dist_tax.cust_trx_line_gl_dist_id = c_tax_dist_id;
SELECT
ael.code_combination_id
FROM ar_distributions_all dist,
xla_distribution_links lnk,
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 = 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,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE dist.line_id = c_trx_line_id
AND dist.source_table= 'MCD'
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,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE dist.line_id = c_trx_line_id
AND dist.source_table= 'RA'
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,
xla_distribution_links lnk,
xla_ae_lines ael
WHERE dist.line_id = c_trx_line_id
AND dist.source_table= 'ADJ'
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 = c_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 = 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 = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND ael.accounting_class_code <> 'UNAPP'
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 ||','||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 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(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))
FROM AR_DISTRIBUTIONS_ALL dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE dist.line_id = c_trx_line_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
and ael.accounting_class_code = 'MISC_CASH';
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))
FROM AR_DISTRIBUTIONS_ALL dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE dist.line_id = c_trx_line_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(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))
FROM AR_DISTRIBUTIONS_ALL dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael
WHERE dist.line_id = c_trx_line_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
AND ael.accounting_class_code = 'ADJ';
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))
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 NVL(ptp.rep_registration_number, reg.registration_number)
FROM zx_party_tax_profile ptp, zx_registrations reg
WHERE ptp.party_id = cp_party_id
AND ptp.party_type_code = cp_party_type_code
AND reg.party_tax_profile_id(+) = ptp.party_tax_profile_id
AND cp_trx_date BETWEEN NVL(REG.EFFECTIVE_FROM,cp_trx_date)
AND NVL(REG.EFFECTIVE_TO,cp_trx_date)
ORDER BY reg.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 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' ;
SELECT tax_rate_code,
tax_rate_name,
description,
percentage_rate,
def_rec_settlement_option_code,
vat_transaction_type_code,
tax_regime_code,
tax,
tax_status_code,
tax_jurisdiction_code
FROM zx_rates_vl
WHERE tax_rate_id = c_tax_rate_id;
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 /*+ 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_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),
TAX_RATE_CODE_VAT_TRX_TYPE_MNG = GT_TAX_RATE_VAT_TRX_TYPE_MNG(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),
TAX_RATE_CODE = nvl(GT_TAX_RATE_CODE(i), TAX_RATE_CODE),
TAX_RATE_CODE_NAME = nvl(GT_TAX_RATE_CODE_NAME(i), TAX_RATE_CODE_NAME),
TAX_RATE = nvl(GT_TAX_RATE(i), TAX_RATE),
DEF_REC_SETTLEMENT_OPTION_CODE = nvl(DEF_REC_SETTLEMENT_OPTION_CODE,GT_DEF_REC_SETTLEMENT_OPT_CODE(i)),
TAX_RATE_VAT_TRX_TYPE_CODE = nvl(TAX_RATE_VAT_TRX_TYPE_CODE,GT_TAX_RATE_VAT_TRX_TYPE_CODE(i)),
TAX_REGIME_CODE = nvl(TAX_REGIME_CODE, GT_TAX_REGIME_CODE(i)),
TAX = nvl(TAX,GT_TAX(i)),
TAX_STATUS_CODE = nvl(TAX_STATUS_CODE,GT_TAX_STATUS_CODE(i)),
TAX_JURISDICTION_CODE = nvl(TAX_JURISDICTION_CODE,GT_TAX_JURISDICTION_CODE(i)),
TAX_RATE_CODE_DESCRIPTION = nvl(TAX_RATE_CODE_DESCRIPTION,GT_TAX_RATE_CODE_DESCRIPTION(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_POPULATE_PKG.insert_actg_info.BEGIN',
'ZX_AR_ACTG_EXTRACT_PKG: insert_actg_info(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_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_POPULATE_PKG.insert_actg_info',
'Number of Tax Lines successfully inserted = '||TO_CHAR(l_count));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.insert_actg_info.END',
'ZX_AR_ACTG_EXTRACT_PKG: INIT_GT_VARIABLES(-)');
'ZX.TRL.ZX_AR_POPULATE_PKG.insert_actg_info',
g_error_buffer);
END insert_actg_info;