The following lines contain the word 'select', 'insert', 'update' or 'delete':
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('header_index')
,p_balance_type_code => l_balance_type_code
);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus
(p_hdr_idx => g_array_event(p_event_id).array_value_num('acc_rev_header_index')
,p_balance_type_code => l_balance_type_code);
PROCEDURE insert_sources_60(
p_target_ledger_id IN NUMBER
, p_language IN VARCHAR2
, p_sla_ledger_id IN NUMBER
, p_pad_start_date IN DATE
, p_pad_end_date IN DATE
)
IS
C_EVENT_TYPE_CODE CONSTANT VARCHAR2(30) := 'CREDIT MEMOS_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_60';
(p_msg => 'BEGIN of insert_sources_60'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT oracle_username
INTO p_apps_owner
FROM fnd_oracle_userid
WHERE read_only_flag = 'U'
;
INSERT INTO xla_diag_sources --hdr2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT
event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value ,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, 0 line_number
, CASE r
WHEN 1 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 2 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 3 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 4 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 5 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 6 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 7 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 8 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 9 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 10 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 11 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 12 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 13 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'HEADER'
WHEN 2 THEN 'HEADER'
WHEN 3 THEN 'HEADER'
WHEN 4 THEN 'HEADER'
WHEN 5 THEN 'HEADER'
WHEN 6 THEN 'HEADER'
WHEN 7 THEN 'HEADER'
WHEN 8 THEN 'HEADER'
WHEN 9 THEN 'HEADER'
WHEN 10 THEN 'HEADER'
WHEN 11 THEN 'HEADER'
WHEN 12 THEN 'HEADER'
WHEN 13 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '200'
WHEN 4 THEN '200'
WHEN 5 THEN '200'
WHEN 6 THEN '200'
WHEN 7 THEN '200'
WHEN 8 THEN '200'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '200'
WHEN 13 THEN '200'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AI_PAYMENT_STATUS_FLAG'
WHEN 2 THEN 'AI_INVOICE_TYPE_LOOKUP_CODE'
WHEN 3 THEN 'AI_INVOICE_ID'
WHEN 4 THEN 'AI_INVOICE_CURRENCY_CODE'
WHEN 5 THEN 'INV_EXCHANGE_DATE'
WHEN 6 THEN 'INV_EXCHANGE_RATE'
WHEN 7 THEN 'INV_EXCHANGE_RATE_TYPE'
WHEN 8 THEN 'AI_VENDOR_ID'
WHEN 9 THEN 'AI_VENDOR_SITE_ID'
WHEN 10 THEN 'THIRD_PARTY_TYPE'
WHEN 11 THEN 'INV_DOC_SEQUENCE_CATEGORY'
WHEN 12 THEN 'INV_DOC_SEQUENCE_IDENTIFIER'
WHEN 13 THEN 'INV_DOC_SEQUENCE_VALUE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h2.AI_PAYMENT_STATUS_FLAG)
WHEN 2 THEN TO_CHAR(h2.AI_INVOICE_TYPE_LOOKUP_CODE)
WHEN 3 THEN TO_CHAR(h2.AI_INVOICE_ID)
WHEN 4 THEN TO_CHAR(h2.AI_INVOICE_CURRENCY_CODE)
WHEN 5 THEN TO_CHAR(h2.INV_EXCHANGE_DATE)
WHEN 6 THEN TO_CHAR(h2.INV_EXCHANGE_RATE)
WHEN 7 THEN TO_CHAR(h2.INV_EXCHANGE_RATE_TYPE)
WHEN 8 THEN TO_CHAR(h2.AI_VENDOR_ID)
WHEN 9 THEN TO_CHAR(h2.AI_VENDOR_SITE_ID)
WHEN 10 THEN TO_CHAR(h2.THIRD_PARTY_TYPE)
WHEN 11 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_CATEGORY)
WHEN 12 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_IDENTIFIER)
WHEN 13 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_VALUE)
ELSE null
END source_value
, CASE r
WHEN 2 THEN fvl13.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, fnd_lookup_values fvl13
,(select rownum r from all_objects where rownum <= 13 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND h2.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of header sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_diag_sources --line2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, l1.line_number line_number
, CASE r
WHEN 1 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 2 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 3 THEN 'FV_XLA_AP_REF_V'
WHEN 4 THEN 'FV_XLA_AP_REF_V'
WHEN 5 THEN 'FV_XLA_AP_REF_V'
WHEN 6 THEN 'FV_XLA_AP_REF_V'
WHEN 7 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 8 THEN 'FV_XLA_AP_REF_V'
WHEN 9 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 10 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 11 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 12 THEN 'FV_XLA_AP_REF_V'
WHEN 13 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 14 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 15 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 16 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 17 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 18 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 19 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 20 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 21 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 22 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 23 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 24 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 25 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 26 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 27 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 28 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 29 THEN 'FV_XLA_AP_REF_V'
WHEN 30 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 31 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 32 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 33 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 34 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 35 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 36 THEN 'ZX_AP_DEF_TAX_EXTRACT_V'
WHEN 37 THEN 'ZX_AP_TAX_JRNL_LINE_DESC_V'
WHEN 38 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 39 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 40 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 41 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 42 THEN 'FV_XLA_AP_REF_V'
WHEN 43 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 44 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
WHEN 3 THEN 'LINE'
WHEN 4 THEN 'LINE'
WHEN 5 THEN 'LINE'
WHEN 6 THEN 'LINE'
WHEN 7 THEN 'LINE'
WHEN 8 THEN 'LINE'
WHEN 9 THEN 'LINE'
WHEN 10 THEN 'LINE'
WHEN 11 THEN 'LINE'
WHEN 12 THEN 'LINE'
WHEN 13 THEN 'LINE'
WHEN 14 THEN 'LINE'
WHEN 15 THEN 'LINE'
WHEN 16 THEN 'LINE'
WHEN 17 THEN 'LINE'
WHEN 18 THEN 'LINE'
WHEN 19 THEN 'LINE'
WHEN 20 THEN 'LINE'
WHEN 21 THEN 'LINE'
WHEN 22 THEN 'LINE'
WHEN 23 THEN 'LINE'
WHEN 24 THEN 'LINE'
WHEN 25 THEN 'LINE'
WHEN 26 THEN 'LINE'
WHEN 27 THEN 'LINE'
WHEN 28 THEN 'LINE'
WHEN 29 THEN 'LINE'
WHEN 30 THEN 'LINE'
WHEN 31 THEN 'LINE'
WHEN 32 THEN 'LINE'
WHEN 33 THEN 'LINE'
WHEN 34 THEN 'LINE'
WHEN 35 THEN 'LINE'
WHEN 36 THEN 'LINE'
WHEN 37 THEN 'LINE'
WHEN 38 THEN 'LINE'
WHEN 39 THEN 'LINE'
WHEN 40 THEN 'LINE'
WHEN 41 THEN 'LINE'
WHEN 42 THEN 'LINE'
WHEN 43 THEN 'LINE'
WHEN 44 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '8901'
WHEN 4 THEN '8901'
WHEN 5 THEN '8901'
WHEN 6 THEN '8901'
WHEN 7 THEN '200'
WHEN 8 THEN '8901'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '8901'
WHEN 13 THEN '200'
WHEN 14 THEN '200'
WHEN 15 THEN '200'
WHEN 16 THEN '200'
WHEN 17 THEN '200'
WHEN 18 THEN '200'
WHEN 19 THEN '200'
WHEN 20 THEN '200'
WHEN 21 THEN '200'
WHEN 22 THEN '200'
WHEN 23 THEN '200'
WHEN 24 THEN '200'
WHEN 25 THEN '200'
WHEN 26 THEN '200'
WHEN 27 THEN '200'
WHEN 28 THEN '200'
WHEN 29 THEN '8901'
WHEN 30 THEN '200'
WHEN 31 THEN '200'
WHEN 32 THEN '200'
WHEN 33 THEN '200'
WHEN 34 THEN '200'
WHEN 35 THEN '200'
WHEN 36 THEN '200'
WHEN 37 THEN '200'
WHEN 38 THEN '200'
WHEN 39 THEN '200'
WHEN 40 THEN '200'
WHEN 41 THEN '200'
WHEN 42 THEN '8901'
WHEN 43 THEN '200'
WHEN 44 THEN '200'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AID_DESCRIPTION'
WHEN 2 THEN 'AID_DIST_CCID'
WHEN 3 THEN 'FEDERAL_ANTICIPATION'
WHEN 4 THEN 'FEDERAL_FUND_CATEGORY'
WHEN 5 THEN 'FEDERAL_FUND_EXPIRED_STATUS'
WHEN 6 THEN 'FEDERAL_FUND_CAT_DESC'
WHEN 7 THEN 'AID_LINE_TYPE_LOOKUP_CODE'
WHEN 8 THEN 'FEDERAL_PRIOR_YEAR_FLAG'
WHEN 9 THEN 'ENCUMBRANCE_AMOUNT'
WHEN 10 THEN 'POD_ACCRUE_ON_RECEIPT_FLAG'
WHEN 11 THEN 'PO_DISTRIBUTION_ID'
WHEN 12 THEN 'FEDERAL_ADJUSTMENT_TYPE'
WHEN 13 THEN 'DIST_ACCOUNT_REVERSAL_OPTION'
WHEN 14 THEN 'DISTRIBUTION_LINK_TYPE'
WHEN 15 THEN 'ALLOC_TO_MAIN_DIST_ID'
WHEN 16 THEN 'BUS_FLOW_AP_APP_ID'
WHEN 17 THEN 'BUS_FLOW_INV_DIST_TYPE'
WHEN 18 THEN 'BUS_FLOW_INV_ENTITY_CODE'
WHEN 19 THEN 'BUS_FLOW_INV_DIST_ID'
WHEN 20 THEN 'BUS_FLOW_INV_ID'
WHEN 21 THEN 'AID_INVOICE_DIST_ID'
WHEN 22 THEN 'UPG_ENC_CR_CCID'
WHEN 23 THEN 'UPG_ENC_CR_AMT'
WHEN 24 THEN 'UPG_ENC_CR_BASE_AMT'
WHEN 25 THEN 'UPG_ENC_DR_CCID'
WHEN 26 THEN 'UPG_ENC_DR_AMT'
WHEN 27 THEN 'UPG_ENC_DR_BASE_AMT'
WHEN 28 THEN 'UPG_AP_ENCUM_OPTION'
WHEN 29 THEN 'FEDERAL_ANTICIPATED_AMT'
WHEN 30 THEN 'DEFERRED_END_DATE'
WHEN 31 THEN 'DEFERRED_OPTION'
WHEN 32 THEN 'DEFERRED_START_DATE'
WHEN 33 THEN 'OVERRIDE_ACCTD_AMT_FLAG'
WHEN 34 THEN 'AID_PARENT_REVERSAL_ID'
WHEN 35 THEN 'AID_STAT_AMOUNT'
WHEN 36 THEN 'TAX_LINE_ID'
WHEN 37 THEN 'REC_NREC_TAX_DIST_ID'
WHEN 38 THEN 'SUMMARY_TAX_LINE_ID'
WHEN 39 THEN 'UPG_CR_ENC_TYPE_ID'
WHEN 40 THEN 'UPG_DR_ENC_TYPE_ID'
WHEN 41 THEN 'ENCUMBRANCE_BASE_AMOUNT'
WHEN 42 THEN 'FEDERAL_UNANTICIPATED_AMT'
WHEN 43 THEN 'AID_AMOUNT'
WHEN 44 THEN 'INV_DIST_BASE_AMOUNT'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l1.AID_DESCRIPTION)
WHEN 2 THEN TO_CHAR(l1.AID_DIST_CCID)
WHEN 3 THEN TO_CHAR(l3.FEDERAL_ANTICIPATION)
WHEN 4 THEN TO_CHAR(l3.FEDERAL_FUND_CATEGORY)
WHEN 5 THEN TO_CHAR(l3.FEDERAL_FUND_EXPIRED_STATUS)
WHEN 6 THEN TO_CHAR(l3.FEDERAL_FUND_CAT_DESC)
WHEN 7 THEN TO_CHAR(l1.AID_LINE_TYPE_LOOKUP_CODE)
WHEN 8 THEN TO_CHAR(l3.FEDERAL_PRIOR_YEAR_FLAG)
WHEN 9 THEN TO_CHAR(l1.ENCUMBRANCE_AMOUNT)
WHEN 10 THEN TO_CHAR(l1.POD_ACCRUE_ON_RECEIPT_FLAG)
WHEN 11 THEN TO_CHAR(l1.PO_DISTRIBUTION_ID)
WHEN 12 THEN TO_CHAR(l3.FEDERAL_ADJUSTMENT_TYPE)
WHEN 13 THEN TO_CHAR(l1.DIST_ACCOUNT_REVERSAL_OPTION)
WHEN 14 THEN TO_CHAR(l1.DISTRIBUTION_LINK_TYPE)
WHEN 15 THEN TO_CHAR(l1.ALLOC_TO_MAIN_DIST_ID)
WHEN 16 THEN TO_CHAR(l1.BUS_FLOW_AP_APP_ID)
WHEN 17 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_TYPE)
WHEN 18 THEN TO_CHAR(l1.BUS_FLOW_INV_ENTITY_CODE)
WHEN 19 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_ID)
WHEN 20 THEN TO_CHAR(l1.BUS_FLOW_INV_ID)
WHEN 21 THEN TO_CHAR(l1.AID_INVOICE_DIST_ID)
WHEN 22 THEN TO_CHAR(l1.UPG_ENC_CR_CCID)
WHEN 23 THEN TO_CHAR(l1.UPG_ENC_CR_AMT)
WHEN 24 THEN TO_CHAR(l1.UPG_ENC_CR_BASE_AMT)
WHEN 25 THEN TO_CHAR(l1.UPG_ENC_DR_CCID)
WHEN 26 THEN TO_CHAR(l1.UPG_ENC_DR_AMT)
WHEN 27 THEN TO_CHAR(l1.UPG_ENC_DR_BASE_AMT)
WHEN 28 THEN TO_CHAR(l1.UPG_AP_ENCUM_OPTION)
WHEN 29 THEN TO_CHAR(l3.FEDERAL_ANTICIPATED_AMT)
WHEN 30 THEN TO_CHAR(l1.DEFERRED_END_DATE)
WHEN 31 THEN TO_CHAR(l1.DEFERRED_OPTION)
WHEN 32 THEN TO_CHAR(l1.DEFERRED_START_DATE)
WHEN 33 THEN TO_CHAR(l1.OVERRIDE_ACCTD_AMT_FLAG)
WHEN 34 THEN TO_CHAR(l1.AID_PARENT_REVERSAL_ID)
WHEN 35 THEN TO_CHAR(l1.AID_STAT_AMOUNT)
WHEN 36 THEN TO_CHAR(l4.TAX_LINE_ID)
WHEN 37 THEN TO_CHAR(l5.REC_NREC_TAX_DIST_ID)
WHEN 38 THEN TO_CHAR(l1.SUMMARY_TAX_LINE_ID)
WHEN 39 THEN TO_CHAR(l1.UPG_CR_ENC_TYPE_ID)
WHEN 40 THEN TO_CHAR(l1.UPG_DR_ENC_TYPE_ID)
WHEN 41 THEN TO_CHAR(l1.ENCUMBRANCE_BASE_AMOUNT)
WHEN 42 THEN TO_CHAR(l3.FEDERAL_UNANTICIPATED_AMT)
WHEN 43 THEN TO_CHAR(l1.AID_AMOUNT)
WHEN 44 THEN TO_CHAR(l1.INV_DIST_BASE_AMOUNT)
ELSE null
END source_value
, CASE r
WHEN 7 THEN fvl8.meaning
WHEN 10 THEN fvl11.meaning
WHEN 33 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l3
, ZX_AP_DEF_TAX_EXTRACT_V l4
, ZX_AP_TAX_JRNL_LINE_DESC_V l5
, fnd_lookup_values fvl8
, fnd_lookup_values fvl11
, fnd_lookup_values fvl42
, (select rownum r from all_objects where rownum <= 44 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND l1.event_id = xet.event_id
AND l3.federal_event_id (+) = l1.event_id and l3.federal_line_number (+) = l1.line_number AND l1.zrnd_tax_dist_id = l4.rec_nrec_tax_dist_id (+) AND l1.zrnd_tax_dist_id = l5.rec_nrec_tax_dist_id (+) AND fvl8.lookup_type(+) =
'INVOICE DISTRIBUTION TYPE'
AND fvl8.lookup_code(+) = l1.AID_LINE_TYPE_LOOKUP_CODE
AND fvl8.view_application_id(+) = 200
AND fvl8.language(+) = USERENV('LANG')
AND fvl11.lookup_type(+) = 'YES_NO'
AND fvl11.lookup_code(+) = l1.POD_ACCRUE_ON_RECEIPT_FLAG
AND fvl11.view_application_id(+) = 0
AND fvl11.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of line sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END of insert_sources_60'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00200_AAD_S_000012_BC_PKG.insert_sources_60');
END insert_sources_60;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: CREDIT MEMOS
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, h2.AI_PAYMENT_STATUS_FLAG source_7
, h2.AI_INVOICE_TYPE_LOOKUP_CODE source_13
, fvl13.meaning source_13_meaning
, h2.AI_INVOICE_ID source_20
, h2.AI_INVOICE_CURRENCY_CODE source_29
, h2.INV_EXCHANGE_DATE source_36
, h2.INV_EXCHANGE_RATE source_37
, h2.INV_EXCHANGE_RATE_TYPE source_38
, h2.AI_VENDOR_ID source_43
, h2.AI_VENDOR_SITE_ID source_44
, h2.THIRD_PARTY_TYPE source_45
, h2.INV_DOC_SEQUENCE_CATEGORY source_71
, h2.INV_DOC_SEQUENCE_IDENTIFIER source_72
, h2.INV_DOC_SEQUENCE_VALUE source_73
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, fnd_lookup_values fvl13
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND h2.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: CREDIT MEMOS
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, l1.LINE_NUMBER
, l1.AID_DESCRIPTION source_1
, l1.AID_DIST_CCID source_2
, l3.FEDERAL_ANTICIPATION source_3
, l3.FEDERAL_FUND_CATEGORY source_4
, l3.FEDERAL_FUND_EXPIRED_STATUS source_5
, l3.FEDERAL_FUND_CAT_DESC source_6
, l1.AID_LINE_TYPE_LOOKUP_CODE source_8
, fvl8.meaning source_8_meaning
, l3.FEDERAL_PRIOR_YEAR_FLAG source_9
, l1.ENCUMBRANCE_AMOUNT source_10
, l1.POD_ACCRUE_ON_RECEIPT_FLAG source_11
, fvl11.meaning source_11_meaning
, l1.PO_DISTRIBUTION_ID source_12
, l3.FEDERAL_ADJUSTMENT_TYPE source_14
, l1.DIST_ACCOUNT_REVERSAL_OPTION source_15
, l1.DISTRIBUTION_LINK_TYPE source_17
, l1.ALLOC_TO_MAIN_DIST_ID source_19
, l1.BUS_FLOW_AP_APP_ID source_21
, l1.BUS_FLOW_INV_DIST_TYPE source_22
, l1.BUS_FLOW_INV_ENTITY_CODE source_23
, l1.BUS_FLOW_INV_DIST_ID source_24
, l1.BUS_FLOW_INV_ID source_25
, l1.AID_INVOICE_DIST_ID source_26
, l1.UPG_ENC_CR_CCID source_27
, l1.UPG_ENC_CR_AMT source_28
, l1.UPG_ENC_CR_BASE_AMT source_30
, l1.UPG_ENC_DR_CCID source_31
, l1.UPG_ENC_DR_AMT source_32
, l1.UPG_ENC_DR_BASE_AMT source_33
, l1.UPG_AP_ENCUM_OPTION source_34
, l3.FEDERAL_ANTICIPATED_AMT source_35
, l1.DEFERRED_END_DATE source_39
, l1.DEFERRED_OPTION source_40
, l1.DEFERRED_START_DATE source_41
, l1.OVERRIDE_ACCTD_AMT_FLAG source_42
, fvl42.meaning source_42_meaning
, l1.AID_PARENT_REVERSAL_ID source_46
, l1.AID_STAT_AMOUNT source_47
, l4.TAX_LINE_ID source_48
, l5.REC_NREC_TAX_DIST_ID source_49
, l1.SUMMARY_TAX_LINE_ID source_50
, l1.UPG_CR_ENC_TYPE_ID source_51
, l1.UPG_DR_ENC_TYPE_ID source_52
, l1.ENCUMBRANCE_BASE_AMOUNT source_53
, l3.FEDERAL_UNANTICIPATED_AMT source_54
, l1.AID_AMOUNT source_55
, l1.INV_DIST_BASE_AMOUNT source_56
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l3
, ZX_AP_DEF_TAX_EXTRACT_V l4
, ZX_AP_TAX_JRNL_LINE_DESC_V l5
, fnd_lookup_values fvl8
, fnd_lookup_values fvl11
, fnd_lookup_values fvl42
WHERE xet.event_id between x_first_event_id and x_last_event_id
and xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND l1.event_id = xet.event_id
AND l3.Federal_Event_Id (+) = l1.event_Id AND l3.Federal_line_number (+) = l1.line_number AND l1.ZRND_tax_dist_id = l4.REC_NREC_TAX_DIST_ID (+) AND l1.ZRND_tax_dist_id = l5.REC_NREC_TAX_DIST_ID (+) AND fvl8.lookup_type(+) =
'INVOICE DISTRIBUTION TYPE'
AND fvl8.lookup_code(+) = l1.AID_LINE_TYPE_LOOKUP_CODE
AND fvl8.view_application_id(+) = 200
AND fvl8.language(+) = USERENV('LANG')
AND fvl11.lookup_type(+) = 'YES_NO'
AND fvl11.lookup_code(+) = l1.POD_ACCRUE_ON_RECEIPT_FLAG
AND fvl11.view_application_id(+) = 0
AND fvl11.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
;
g_array_event.DELETE;
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => hdr_idx);
l_result := XLA_AE_LINES_PKG.InsertLines;
l_result := XLA_AE_LINES_PKG.InsertLines ;
l_result := XLA_AE_HEADER_PKG.InsertHeaders ;
PROCEDURE insert_sources_61(
p_target_ledger_id IN NUMBER
, p_language IN VARCHAR2
, p_sla_ledger_id IN NUMBER
, p_pad_start_date IN DATE
, p_pad_end_date IN DATE
)
IS
C_EVENT_TYPE_CODE CONSTANT VARCHAR2(30) := 'DEBIT MEMOS_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_61';
(p_msg => 'BEGIN of insert_sources_61'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT oracle_username
INTO p_apps_owner
FROM fnd_oracle_userid
WHERE read_only_flag = 'U'
;
INSERT INTO xla_diag_sources --hdr2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT
event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value ,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, 0 line_number
, CASE r
WHEN 1 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 2 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 3 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 4 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 5 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 6 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 7 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 8 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 9 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 10 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 11 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 12 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 13 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'HEADER'
WHEN 2 THEN 'HEADER'
WHEN 3 THEN 'HEADER'
WHEN 4 THEN 'HEADER'
WHEN 5 THEN 'HEADER'
WHEN 6 THEN 'HEADER'
WHEN 7 THEN 'HEADER'
WHEN 8 THEN 'HEADER'
WHEN 9 THEN 'HEADER'
WHEN 10 THEN 'HEADER'
WHEN 11 THEN 'HEADER'
WHEN 12 THEN 'HEADER'
WHEN 13 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '200'
WHEN 4 THEN '200'
WHEN 5 THEN '200'
WHEN 6 THEN '200'
WHEN 7 THEN '200'
WHEN 8 THEN '200'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '200'
WHEN 13 THEN '200'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AI_PAYMENT_STATUS_FLAG'
WHEN 2 THEN 'AI_INVOICE_TYPE_LOOKUP_CODE'
WHEN 3 THEN 'AI_INVOICE_ID'
WHEN 4 THEN 'AI_INVOICE_CURRENCY_CODE'
WHEN 5 THEN 'INV_EXCHANGE_DATE'
WHEN 6 THEN 'INV_EXCHANGE_RATE'
WHEN 7 THEN 'INV_EXCHANGE_RATE_TYPE'
WHEN 8 THEN 'AI_VENDOR_ID'
WHEN 9 THEN 'AI_VENDOR_SITE_ID'
WHEN 10 THEN 'THIRD_PARTY_TYPE'
WHEN 11 THEN 'INV_DOC_SEQUENCE_CATEGORY'
WHEN 12 THEN 'INV_DOC_SEQUENCE_IDENTIFIER'
WHEN 13 THEN 'INV_DOC_SEQUENCE_VALUE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h2.AI_PAYMENT_STATUS_FLAG)
WHEN 2 THEN TO_CHAR(h2.AI_INVOICE_TYPE_LOOKUP_CODE)
WHEN 3 THEN TO_CHAR(h2.AI_INVOICE_ID)
WHEN 4 THEN TO_CHAR(h2.AI_INVOICE_CURRENCY_CODE)
WHEN 5 THEN TO_CHAR(h2.INV_EXCHANGE_DATE)
WHEN 6 THEN TO_CHAR(h2.INV_EXCHANGE_RATE)
WHEN 7 THEN TO_CHAR(h2.INV_EXCHANGE_RATE_TYPE)
WHEN 8 THEN TO_CHAR(h2.AI_VENDOR_ID)
WHEN 9 THEN TO_CHAR(h2.AI_VENDOR_SITE_ID)
WHEN 10 THEN TO_CHAR(h2.THIRD_PARTY_TYPE)
WHEN 11 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_CATEGORY)
WHEN 12 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_IDENTIFIER)
WHEN 13 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_VALUE)
ELSE null
END source_value
, CASE r
WHEN 2 THEN fvl13.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, fnd_lookup_values fvl13
,(select rownum r from all_objects where rownum <= 13 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND h2.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of header sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_diag_sources --line2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, l1.line_number line_number
, CASE r
WHEN 1 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 2 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 3 THEN 'FV_XLA_AP_REF_V'
WHEN 4 THEN 'FV_XLA_AP_REF_V'
WHEN 5 THEN 'FV_XLA_AP_REF_V'
WHEN 6 THEN 'FV_XLA_AP_REF_V'
WHEN 7 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 8 THEN 'FV_XLA_AP_REF_V'
WHEN 9 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 10 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 11 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 12 THEN 'FV_XLA_AP_REF_V'
WHEN 13 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 14 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 15 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 16 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 17 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 18 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 19 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 20 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 21 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 22 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 23 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 24 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 25 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 26 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 27 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 28 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 29 THEN 'FV_XLA_AP_REF_V'
WHEN 30 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 31 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 32 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 33 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 34 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 35 THEN 'ZX_AP_DEF_TAX_EXTRACT_V'
WHEN 36 THEN 'ZX_AP_TAX_JRNL_LINE_DESC_V'
WHEN 37 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 38 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 39 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 40 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 41 THEN 'FV_XLA_AP_REF_V'
WHEN 42 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 43 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 44 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
WHEN 3 THEN 'LINE'
WHEN 4 THEN 'LINE'
WHEN 5 THEN 'LINE'
WHEN 6 THEN 'LINE'
WHEN 7 THEN 'LINE'
WHEN 8 THEN 'LINE'
WHEN 9 THEN 'LINE'
WHEN 10 THEN 'LINE'
WHEN 11 THEN 'LINE'
WHEN 12 THEN 'LINE'
WHEN 13 THEN 'LINE'
WHEN 14 THEN 'LINE'
WHEN 15 THEN 'LINE'
WHEN 16 THEN 'LINE'
WHEN 17 THEN 'LINE'
WHEN 18 THEN 'LINE'
WHEN 19 THEN 'LINE'
WHEN 20 THEN 'LINE'
WHEN 21 THEN 'LINE'
WHEN 22 THEN 'LINE'
WHEN 23 THEN 'LINE'
WHEN 24 THEN 'LINE'
WHEN 25 THEN 'LINE'
WHEN 26 THEN 'LINE'
WHEN 27 THEN 'LINE'
WHEN 28 THEN 'LINE'
WHEN 29 THEN 'LINE'
WHEN 30 THEN 'LINE'
WHEN 31 THEN 'LINE'
WHEN 32 THEN 'LINE'
WHEN 33 THEN 'LINE'
WHEN 34 THEN 'LINE'
WHEN 35 THEN 'LINE'
WHEN 36 THEN 'LINE'
WHEN 37 THEN 'LINE'
WHEN 38 THEN 'LINE'
WHEN 39 THEN 'LINE'
WHEN 40 THEN 'LINE'
WHEN 41 THEN 'LINE'
WHEN 42 THEN 'LINE'
WHEN 43 THEN 'LINE'
WHEN 44 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '8901'
WHEN 4 THEN '8901'
WHEN 5 THEN '8901'
WHEN 6 THEN '8901'
WHEN 7 THEN '200'
WHEN 8 THEN '8901'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '8901'
WHEN 13 THEN '200'
WHEN 14 THEN '200'
WHEN 15 THEN '200'
WHEN 16 THEN '200'
WHEN 17 THEN '200'
WHEN 18 THEN '200'
WHEN 19 THEN '200'
WHEN 20 THEN '200'
WHEN 21 THEN '200'
WHEN 22 THEN '200'
WHEN 23 THEN '200'
WHEN 24 THEN '200'
WHEN 25 THEN '200'
WHEN 26 THEN '200'
WHEN 27 THEN '200'
WHEN 28 THEN '200'
WHEN 29 THEN '8901'
WHEN 30 THEN '200'
WHEN 31 THEN '200'
WHEN 32 THEN '200'
WHEN 33 THEN '200'
WHEN 34 THEN '200'
WHEN 35 THEN '200'
WHEN 36 THEN '200'
WHEN 37 THEN '200'
WHEN 38 THEN '200'
WHEN 39 THEN '200'
WHEN 40 THEN '200'
WHEN 41 THEN '8901'
WHEN 42 THEN '200'
WHEN 43 THEN '200'
WHEN 44 THEN '200'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AID_DESCRIPTION'
WHEN 2 THEN 'AID_DIST_CCID'
WHEN 3 THEN 'FEDERAL_ANTICIPATION'
WHEN 4 THEN 'FEDERAL_FUND_CATEGORY'
WHEN 5 THEN 'FEDERAL_FUND_EXPIRED_STATUS'
WHEN 6 THEN 'FEDERAL_FUND_CAT_DESC'
WHEN 7 THEN 'AID_LINE_TYPE_LOOKUP_CODE'
WHEN 8 THEN 'FEDERAL_PRIOR_YEAR_FLAG'
WHEN 9 THEN 'ENCUMBRANCE_AMOUNT'
WHEN 10 THEN 'POD_ACCRUE_ON_RECEIPT_FLAG'
WHEN 11 THEN 'PO_DISTRIBUTION_ID'
WHEN 12 THEN 'FEDERAL_ADJUSTMENT_TYPE'
WHEN 13 THEN 'DIST_ACCOUNT_REVERSAL_OPTION'
WHEN 14 THEN 'DISTRIBUTION_LINK_TYPE'
WHEN 15 THEN 'ALLOC_TO_MAIN_DIST_ID'
WHEN 16 THEN 'BUS_FLOW_AP_APP_ID'
WHEN 17 THEN 'BUS_FLOW_INV_DIST_TYPE'
WHEN 18 THEN 'BUS_FLOW_INV_ENTITY_CODE'
WHEN 19 THEN 'BUS_FLOW_INV_DIST_ID'
WHEN 20 THEN 'BUS_FLOW_INV_ID'
WHEN 21 THEN 'AID_INVOICE_DIST_ID'
WHEN 22 THEN 'UPG_ENC_CR_CCID'
WHEN 23 THEN 'UPG_ENC_CR_AMT'
WHEN 24 THEN 'UPG_ENC_CR_BASE_AMT'
WHEN 25 THEN 'UPG_ENC_DR_CCID'
WHEN 26 THEN 'UPG_ENC_DR_AMT'
WHEN 27 THEN 'UPG_ENC_DR_BASE_AMT'
WHEN 28 THEN 'UPG_AP_ENCUM_OPTION'
WHEN 29 THEN 'FEDERAL_ANTICIPATED_AMT'
WHEN 30 THEN 'DEFERRED_END_DATE'
WHEN 31 THEN 'DEFERRED_OPTION'
WHEN 32 THEN 'DEFERRED_START_DATE'
WHEN 33 THEN 'OVERRIDE_ACCTD_AMT_FLAG'
WHEN 34 THEN 'AID_PARENT_REVERSAL_ID'
WHEN 35 THEN 'TAX_LINE_ID'
WHEN 36 THEN 'REC_NREC_TAX_DIST_ID'
WHEN 37 THEN 'SUMMARY_TAX_LINE_ID'
WHEN 38 THEN 'UPG_CR_ENC_TYPE_ID'
WHEN 39 THEN 'UPG_DR_ENC_TYPE_ID'
WHEN 40 THEN 'ENCUMBRANCE_BASE_AMOUNT'
WHEN 41 THEN 'FEDERAL_UNANTICIPATED_AMT'
WHEN 42 THEN 'AID_AMOUNT'
WHEN 43 THEN 'INV_DIST_BASE_AMOUNT'
WHEN 44 THEN 'AID_BASE_AMT_NO_ROUND'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l1.AID_DESCRIPTION)
WHEN 2 THEN TO_CHAR(l1.AID_DIST_CCID)
WHEN 3 THEN TO_CHAR(l3.FEDERAL_ANTICIPATION)
WHEN 4 THEN TO_CHAR(l3.FEDERAL_FUND_CATEGORY)
WHEN 5 THEN TO_CHAR(l3.FEDERAL_FUND_EXPIRED_STATUS)
WHEN 6 THEN TO_CHAR(l3.FEDERAL_FUND_CAT_DESC)
WHEN 7 THEN TO_CHAR(l1.AID_LINE_TYPE_LOOKUP_CODE)
WHEN 8 THEN TO_CHAR(l3.FEDERAL_PRIOR_YEAR_FLAG)
WHEN 9 THEN TO_CHAR(l1.ENCUMBRANCE_AMOUNT)
WHEN 10 THEN TO_CHAR(l1.POD_ACCRUE_ON_RECEIPT_FLAG)
WHEN 11 THEN TO_CHAR(l1.PO_DISTRIBUTION_ID)
WHEN 12 THEN TO_CHAR(l3.FEDERAL_ADJUSTMENT_TYPE)
WHEN 13 THEN TO_CHAR(l1.DIST_ACCOUNT_REVERSAL_OPTION)
WHEN 14 THEN TO_CHAR(l1.DISTRIBUTION_LINK_TYPE)
WHEN 15 THEN TO_CHAR(l1.ALLOC_TO_MAIN_DIST_ID)
WHEN 16 THEN TO_CHAR(l1.BUS_FLOW_AP_APP_ID)
WHEN 17 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_TYPE)
WHEN 18 THEN TO_CHAR(l1.BUS_FLOW_INV_ENTITY_CODE)
WHEN 19 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_ID)
WHEN 20 THEN TO_CHAR(l1.BUS_FLOW_INV_ID)
WHEN 21 THEN TO_CHAR(l1.AID_INVOICE_DIST_ID)
WHEN 22 THEN TO_CHAR(l1.UPG_ENC_CR_CCID)
WHEN 23 THEN TO_CHAR(l1.UPG_ENC_CR_AMT)
WHEN 24 THEN TO_CHAR(l1.UPG_ENC_CR_BASE_AMT)
WHEN 25 THEN TO_CHAR(l1.UPG_ENC_DR_CCID)
WHEN 26 THEN TO_CHAR(l1.UPG_ENC_DR_AMT)
WHEN 27 THEN TO_CHAR(l1.UPG_ENC_DR_BASE_AMT)
WHEN 28 THEN TO_CHAR(l1.UPG_AP_ENCUM_OPTION)
WHEN 29 THEN TO_CHAR(l3.FEDERAL_ANTICIPATED_AMT)
WHEN 30 THEN TO_CHAR(l1.DEFERRED_END_DATE)
WHEN 31 THEN TO_CHAR(l1.DEFERRED_OPTION)
WHEN 32 THEN TO_CHAR(l1.DEFERRED_START_DATE)
WHEN 33 THEN TO_CHAR(l1.OVERRIDE_ACCTD_AMT_FLAG)
WHEN 34 THEN TO_CHAR(l1.AID_PARENT_REVERSAL_ID)
WHEN 35 THEN TO_CHAR(l4.TAX_LINE_ID)
WHEN 36 THEN TO_CHAR(l5.REC_NREC_TAX_DIST_ID)
WHEN 37 THEN TO_CHAR(l1.SUMMARY_TAX_LINE_ID)
WHEN 38 THEN TO_CHAR(l1.UPG_CR_ENC_TYPE_ID)
WHEN 39 THEN TO_CHAR(l1.UPG_DR_ENC_TYPE_ID)
WHEN 40 THEN TO_CHAR(l1.ENCUMBRANCE_BASE_AMOUNT)
WHEN 41 THEN TO_CHAR(l3.FEDERAL_UNANTICIPATED_AMT)
WHEN 42 THEN TO_CHAR(l1.AID_AMOUNT)
WHEN 43 THEN TO_CHAR(l1.INV_DIST_BASE_AMOUNT)
WHEN 44 THEN TO_CHAR(l1.AID_BASE_AMT_NO_ROUND)
ELSE null
END source_value
, CASE r
WHEN 7 THEN fvl8.meaning
WHEN 10 THEN fvl11.meaning
WHEN 33 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l3
, ZX_AP_DEF_TAX_EXTRACT_V l4
, ZX_AP_TAX_JRNL_LINE_DESC_V l5
, fnd_lookup_values fvl8
, fnd_lookup_values fvl11
, fnd_lookup_values fvl42
, (select rownum r from all_objects where rownum <= 44 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND l1.event_id = xet.event_id
AND l3.federal_event_id (+) = l1.event_id and l3.federal_line_number (+) = l1.line_number AND l1.zrnd_tax_dist_id = l4.rec_nrec_tax_dist_id (+) AND l1.zrnd_tax_dist_id = l5.rec_nrec_tax_dist_id (+) AND fvl8.lookup_type(+) =
'INVOICE DISTRIBUTION TYPE'
AND fvl8.lookup_code(+) = l1.AID_LINE_TYPE_LOOKUP_CODE
AND fvl8.view_application_id(+) = 200
AND fvl8.language(+) = USERENV('LANG')
AND fvl11.lookup_type(+) = 'YES_NO'
AND fvl11.lookup_code(+) = l1.POD_ACCRUE_ON_RECEIPT_FLAG
AND fvl11.view_application_id(+) = 0
AND fvl11.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of line sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END of insert_sources_61'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00200_AAD_S_000012_BC_PKG.insert_sources_61');
END insert_sources_61;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: DEBIT MEMOS
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, h2.AI_PAYMENT_STATUS_FLAG source_7
, h2.AI_INVOICE_TYPE_LOOKUP_CODE source_13
, fvl13.meaning source_13_meaning
, h2.AI_INVOICE_ID source_20
, h2.AI_INVOICE_CURRENCY_CODE source_29
, h2.INV_EXCHANGE_DATE source_36
, h2.INV_EXCHANGE_RATE source_37
, h2.INV_EXCHANGE_RATE_TYPE source_38
, h2.AI_VENDOR_ID source_43
, h2.AI_VENDOR_SITE_ID source_44
, h2.THIRD_PARTY_TYPE source_45
, h2.INV_DOC_SEQUENCE_CATEGORY source_71
, h2.INV_DOC_SEQUENCE_IDENTIFIER source_72
, h2.INV_DOC_SEQUENCE_VALUE source_73
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, fnd_lookup_values fvl13
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND h2.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: DEBIT MEMOS
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, l1.LINE_NUMBER
, l1.AID_DESCRIPTION source_1
, l1.AID_DIST_CCID source_2
, l3.FEDERAL_ANTICIPATION source_3
, l3.FEDERAL_FUND_CATEGORY source_4
, l3.FEDERAL_FUND_EXPIRED_STATUS source_5
, l3.FEDERAL_FUND_CAT_DESC source_6
, l1.AID_LINE_TYPE_LOOKUP_CODE source_8
, fvl8.meaning source_8_meaning
, l3.FEDERAL_PRIOR_YEAR_FLAG source_9
, l1.ENCUMBRANCE_AMOUNT source_10
, l1.POD_ACCRUE_ON_RECEIPT_FLAG source_11
, fvl11.meaning source_11_meaning
, l1.PO_DISTRIBUTION_ID source_12
, l3.FEDERAL_ADJUSTMENT_TYPE source_14
, l1.DIST_ACCOUNT_REVERSAL_OPTION source_15
, l1.DISTRIBUTION_LINK_TYPE source_17
, l1.ALLOC_TO_MAIN_DIST_ID source_19
, l1.BUS_FLOW_AP_APP_ID source_21
, l1.BUS_FLOW_INV_DIST_TYPE source_22
, l1.BUS_FLOW_INV_ENTITY_CODE source_23
, l1.BUS_FLOW_INV_DIST_ID source_24
, l1.BUS_FLOW_INV_ID source_25
, l1.AID_INVOICE_DIST_ID source_26
, l1.UPG_ENC_CR_CCID source_27
, l1.UPG_ENC_CR_AMT source_28
, l1.UPG_ENC_CR_BASE_AMT source_30
, l1.UPG_ENC_DR_CCID source_31
, l1.UPG_ENC_DR_AMT source_32
, l1.UPG_ENC_DR_BASE_AMT source_33
, l1.UPG_AP_ENCUM_OPTION source_34
, l3.FEDERAL_ANTICIPATED_AMT source_35
, l1.DEFERRED_END_DATE source_39
, l1.DEFERRED_OPTION source_40
, l1.DEFERRED_START_DATE source_41
, l1.OVERRIDE_ACCTD_AMT_FLAG source_42
, fvl42.meaning source_42_meaning
, l1.AID_PARENT_REVERSAL_ID source_46
, l4.TAX_LINE_ID source_48
, l5.REC_NREC_TAX_DIST_ID source_49
, l1.SUMMARY_TAX_LINE_ID source_50
, l1.UPG_CR_ENC_TYPE_ID source_51
, l1.UPG_DR_ENC_TYPE_ID source_52
, l1.ENCUMBRANCE_BASE_AMOUNT source_53
, l3.FEDERAL_UNANTICIPATED_AMT source_54
, l1.AID_AMOUNT source_55
, l1.INV_DIST_BASE_AMOUNT source_56
, l1.AID_BASE_AMT_NO_ROUND source_58
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l3
, ZX_AP_DEF_TAX_EXTRACT_V l4
, ZX_AP_TAX_JRNL_LINE_DESC_V l5
, fnd_lookup_values fvl8
, fnd_lookup_values fvl11
, fnd_lookup_values fvl42
WHERE xet.event_id between x_first_event_id and x_last_event_id
and xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND l1.event_id = xet.event_id
AND l3.Federal_Event_Id (+) = l1.event_Id AND l3.Federal_line_number (+) = l1.line_number AND l1.ZRND_tax_dist_id = l4.REC_NREC_TAX_DIST_ID (+) AND l1.ZRND_tax_dist_id = l5.REC_NREC_TAX_DIST_ID (+) AND fvl8.lookup_type(+) =
'INVOICE DISTRIBUTION TYPE'
AND fvl8.lookup_code(+) = l1.AID_LINE_TYPE_LOOKUP_CODE
AND fvl8.view_application_id(+) = 200
AND fvl8.language(+) = USERENV('LANG')
AND fvl11.lookup_type(+) = 'YES_NO'
AND fvl11.lookup_code(+) = l1.POD_ACCRUE_ON_RECEIPT_FLAG
AND fvl11.view_application_id(+) = 0
AND fvl11.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
;
g_array_event.DELETE;
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => hdr_idx);
l_result := XLA_AE_LINES_PKG.InsertLines;
l_result := XLA_AE_LINES_PKG.InsertLines ;
l_result := XLA_AE_HEADER_PKG.InsertHeaders ;
PROCEDURE insert_sources_62(
p_target_ledger_id IN NUMBER
, p_language IN VARCHAR2
, p_sla_ledger_id IN NUMBER
, p_pad_start_date IN DATE
, p_pad_end_date IN DATE
)
IS
C_EVENT_TYPE_CODE CONSTANT VARCHAR2(30) := 'INVOICES_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_62';
(p_msg => 'BEGIN of insert_sources_62'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT oracle_username
INTO p_apps_owner
FROM fnd_oracle_userid
WHERE read_only_flag = 'U'
;
INSERT INTO xla_diag_sources --hdr2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT
event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value ,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, 0 line_number
, CASE r
WHEN 1 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 2 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 3 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 4 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 5 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 6 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 7 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 8 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 9 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 10 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 11 THEN 'AP_SYSTEM_PARAMETERS_EXTRACT_V'
WHEN 12 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 13 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 14 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'HEADER'
WHEN 2 THEN 'HEADER'
WHEN 3 THEN 'HEADER'
WHEN 4 THEN 'HEADER'
WHEN 5 THEN 'HEADER'
WHEN 6 THEN 'HEADER'
WHEN 7 THEN 'HEADER'
WHEN 8 THEN 'HEADER'
WHEN 9 THEN 'HEADER'
WHEN 10 THEN 'HEADER'
WHEN 11 THEN 'HEADER'
WHEN 12 THEN 'HEADER'
WHEN 13 THEN 'HEADER'
WHEN 14 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '200'
WHEN 4 THEN '200'
WHEN 5 THEN '200'
WHEN 6 THEN '200'
WHEN 7 THEN '200'
WHEN 8 THEN '200'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '200'
WHEN 13 THEN '200'
WHEN 14 THEN '200'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AI_PAYMENT_STATUS_FLAG'
WHEN 2 THEN 'AI_INVOICE_TYPE_LOOKUP_CODE'
WHEN 3 THEN 'AI_INVOICE_ID'
WHEN 4 THEN 'AI_INVOICE_CURRENCY_CODE'
WHEN 5 THEN 'INV_EXCHANGE_DATE'
WHEN 6 THEN 'INV_EXCHANGE_RATE'
WHEN 7 THEN 'INV_EXCHANGE_RATE_TYPE'
WHEN 8 THEN 'AI_VENDOR_ID'
WHEN 9 THEN 'AI_VENDOR_SITE_ID'
WHEN 10 THEN 'THIRD_PARTY_TYPE'
WHEN 11 THEN 'FSP_PURCH_ENCUMBRANCE_FLAG'
WHEN 12 THEN 'INV_DOC_SEQUENCE_CATEGORY'
WHEN 13 THEN 'INV_DOC_SEQUENCE_IDENTIFIER'
WHEN 14 THEN 'INV_DOC_SEQUENCE_VALUE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h2.AI_PAYMENT_STATUS_FLAG)
WHEN 2 THEN TO_CHAR(h2.AI_INVOICE_TYPE_LOOKUP_CODE)
WHEN 3 THEN TO_CHAR(h2.AI_INVOICE_ID)
WHEN 4 THEN TO_CHAR(h2.AI_INVOICE_CURRENCY_CODE)
WHEN 5 THEN TO_CHAR(h2.INV_EXCHANGE_DATE)
WHEN 6 THEN TO_CHAR(h2.INV_EXCHANGE_RATE)
WHEN 7 THEN TO_CHAR(h2.INV_EXCHANGE_RATE_TYPE)
WHEN 8 THEN TO_CHAR(h2.AI_VENDOR_ID)
WHEN 9 THEN TO_CHAR(h2.AI_VENDOR_SITE_ID)
WHEN 10 THEN TO_CHAR(h2.THIRD_PARTY_TYPE)
WHEN 11 THEN TO_CHAR(h3.FSP_PURCH_ENCUMBRANCE_FLAG)
WHEN 12 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_CATEGORY)
WHEN 13 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_IDENTIFIER)
WHEN 14 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_VALUE)
ELSE null
END source_value
, CASE r
WHEN 2 THEN fvl13.meaning
WHEN 11 THEN fvl60.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, AP_SYSTEM_PARAMETERS_EXTRACT_V h3
, fnd_lookup_values fvl13
, fnd_lookup_values fvl60
,(select rownum r from all_objects where rownum <= 14 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND h2.event_id = xet.event_id
AND h3.asp_org_id = h2.ai_org_id AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
AND fvl60.lookup_type(+) = 'YES_NO'
AND fvl60.lookup_code(+) = h3.FSP_PURCH_ENCUMBRANCE_FLAG
AND fvl60.view_application_id(+) = 0
AND fvl60.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of header sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_diag_sources --line2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, l1.line_number line_number
, CASE r
WHEN 1 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 2 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 3 THEN 'FV_XLA_AP_REF_V'
WHEN 4 THEN 'FV_XLA_AP_REF_V'
WHEN 5 THEN 'FV_XLA_AP_REF_V'
WHEN 6 THEN 'FV_XLA_AP_REF_V'
WHEN 7 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 8 THEN 'FV_XLA_AP_REF_V'
WHEN 9 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 10 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 11 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 12 THEN 'FV_XLA_AP_REF_V'
WHEN 13 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 14 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 15 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 16 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 17 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 18 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 19 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 20 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 21 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 22 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 23 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 24 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 25 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 26 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 27 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 28 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 29 THEN 'FV_XLA_AP_REF_V'
WHEN 30 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 31 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 32 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 33 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 34 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 35 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 36 THEN 'ZX_AP_DEF_TAX_EXTRACT_V'
WHEN 37 THEN 'ZX_AP_TAX_JRNL_LINE_DESC_V'
WHEN 38 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 39 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 40 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 41 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 42 THEN 'FV_XLA_AP_REF_V'
WHEN 43 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 44 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 45 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 46 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 47 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 48 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 49 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 50 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 51 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 52 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 53 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 54 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 55 THEN 'FV_XLA_AP_REF_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
WHEN 3 THEN 'LINE'
WHEN 4 THEN 'LINE'
WHEN 5 THEN 'LINE'
WHEN 6 THEN 'LINE'
WHEN 7 THEN 'LINE'
WHEN 8 THEN 'LINE'
WHEN 9 THEN 'LINE'
WHEN 10 THEN 'LINE'
WHEN 11 THEN 'LINE'
WHEN 12 THEN 'LINE'
WHEN 13 THEN 'LINE'
WHEN 14 THEN 'LINE'
WHEN 15 THEN 'LINE'
WHEN 16 THEN 'LINE'
WHEN 17 THEN 'LINE'
WHEN 18 THEN 'LINE'
WHEN 19 THEN 'LINE'
WHEN 20 THEN 'LINE'
WHEN 21 THEN 'LINE'
WHEN 22 THEN 'LINE'
WHEN 23 THEN 'LINE'
WHEN 24 THEN 'LINE'
WHEN 25 THEN 'LINE'
WHEN 26 THEN 'LINE'
WHEN 27 THEN 'LINE'
WHEN 28 THEN 'LINE'
WHEN 29 THEN 'LINE'
WHEN 30 THEN 'LINE'
WHEN 31 THEN 'LINE'
WHEN 32 THEN 'LINE'
WHEN 33 THEN 'LINE'
WHEN 34 THEN 'LINE'
WHEN 35 THEN 'LINE'
WHEN 36 THEN 'LINE'
WHEN 37 THEN 'LINE'
WHEN 38 THEN 'LINE'
WHEN 39 THEN 'LINE'
WHEN 40 THEN 'LINE'
WHEN 41 THEN 'LINE'
WHEN 42 THEN 'LINE'
WHEN 43 THEN 'LINE'
WHEN 44 THEN 'LINE'
WHEN 45 THEN 'LINE'
WHEN 46 THEN 'LINE'
WHEN 47 THEN 'LINE'
WHEN 48 THEN 'LINE'
WHEN 49 THEN 'LINE'
WHEN 50 THEN 'LINE'
WHEN 51 THEN 'LINE'
WHEN 52 THEN 'LINE'
WHEN 53 THEN 'LINE'
WHEN 54 THEN 'LINE'
WHEN 55 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '8901'
WHEN 4 THEN '8901'
WHEN 5 THEN '8901'
WHEN 6 THEN '8901'
WHEN 7 THEN '200'
WHEN 8 THEN '8901'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '8901'
WHEN 13 THEN '200'
WHEN 14 THEN '200'
WHEN 15 THEN '200'
WHEN 16 THEN '200'
WHEN 17 THEN '200'
WHEN 18 THEN '200'
WHEN 19 THEN '200'
WHEN 20 THEN '200'
WHEN 21 THEN '200'
WHEN 22 THEN '200'
WHEN 23 THEN '200'
WHEN 24 THEN '200'
WHEN 25 THEN '200'
WHEN 26 THEN '200'
WHEN 27 THEN '200'
WHEN 28 THEN '200'
WHEN 29 THEN '8901'
WHEN 30 THEN '200'
WHEN 31 THEN '200'
WHEN 32 THEN '200'
WHEN 33 THEN '200'
WHEN 34 THEN '200'
WHEN 35 THEN '200'
WHEN 36 THEN '200'
WHEN 37 THEN '200'
WHEN 38 THEN '200'
WHEN 39 THEN '200'
WHEN 40 THEN '200'
WHEN 41 THEN '200'
WHEN 42 THEN '8901'
WHEN 43 THEN '200'
WHEN 44 THEN '200'
WHEN 45 THEN '200'
WHEN 46 THEN '200'
WHEN 47 THEN '200'
WHEN 48 THEN '200'
WHEN 49 THEN '200'
WHEN 50 THEN '200'
WHEN 51 THEN '200'
WHEN 52 THEN '200'
WHEN 53 THEN '200'
WHEN 54 THEN '200'
WHEN 55 THEN '8901'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AID_DESCRIPTION'
WHEN 2 THEN 'AID_DIST_CCID'
WHEN 3 THEN 'FEDERAL_ANTICIPATION'
WHEN 4 THEN 'FEDERAL_FUND_CATEGORY'
WHEN 5 THEN 'FEDERAL_FUND_EXPIRED_STATUS'
WHEN 6 THEN 'FEDERAL_FUND_CAT_DESC'
WHEN 7 THEN 'AID_LINE_TYPE_LOOKUP_CODE'
WHEN 8 THEN 'FEDERAL_PRIOR_YEAR_FLAG'
WHEN 9 THEN 'ENCUMBRANCE_AMOUNT'
WHEN 10 THEN 'POD_ACCRUE_ON_RECEIPT_FLAG'
WHEN 11 THEN 'PO_DISTRIBUTION_ID'
WHEN 12 THEN 'FEDERAL_ADJUSTMENT_TYPE'
WHEN 13 THEN 'DIST_ACCOUNT_REVERSAL_OPTION'
WHEN 14 THEN 'DISTRIBUTION_LINK_TYPE'
WHEN 15 THEN 'ALLOC_TO_MAIN_DIST_ID'
WHEN 16 THEN 'BUS_FLOW_AP_APP_ID'
WHEN 17 THEN 'BUS_FLOW_INV_DIST_TYPE'
WHEN 18 THEN 'BUS_FLOW_INV_ENTITY_CODE'
WHEN 19 THEN 'BUS_FLOW_INV_DIST_ID'
WHEN 20 THEN 'BUS_FLOW_INV_ID'
WHEN 21 THEN 'AID_INVOICE_DIST_ID'
WHEN 22 THEN 'UPG_ENC_CR_CCID'
WHEN 23 THEN 'UPG_ENC_CR_AMT'
WHEN 24 THEN 'UPG_ENC_CR_BASE_AMT'
WHEN 25 THEN 'UPG_ENC_DR_CCID'
WHEN 26 THEN 'UPG_ENC_DR_AMT'
WHEN 27 THEN 'UPG_ENC_DR_BASE_AMT'
WHEN 28 THEN 'UPG_AP_ENCUM_OPTION'
WHEN 29 THEN 'FEDERAL_ANTICIPATED_AMT'
WHEN 30 THEN 'DEFERRED_END_DATE'
WHEN 31 THEN 'DEFERRED_OPTION'
WHEN 32 THEN 'DEFERRED_START_DATE'
WHEN 33 THEN 'OVERRIDE_ACCTD_AMT_FLAG'
WHEN 34 THEN 'AID_PARENT_REVERSAL_ID'
WHEN 35 THEN 'AID_STAT_AMOUNT'
WHEN 36 THEN 'TAX_LINE_ID'
WHEN 37 THEN 'REC_NREC_TAX_DIST_ID'
WHEN 38 THEN 'SUMMARY_TAX_LINE_ID'
WHEN 39 THEN 'UPG_CR_ENC_TYPE_ID'
WHEN 40 THEN 'UPG_DR_ENC_TYPE_ID'
WHEN 41 THEN 'ENCUMBRANCE_BASE_AMOUNT'
WHEN 42 THEN 'FEDERAL_UNANTICIPATED_AMT'
WHEN 43 THEN 'AID_AMOUNT'
WHEN 44 THEN 'INV_DIST_BASE_AMOUNT'
WHEN 45 THEN 'AID_BASE_AMT_NO_ROUND'
WHEN 46 THEN 'AID_AMOUNT_VARIANCE'
WHEN 47 THEN 'AID_BASE_AMOUNT_VARIANCE'
WHEN 48 THEN 'AID_QUANTITY_VARIANCE'
WHEN 49 THEN 'AID_BASE_QUANTITY_VARIANCE'
WHEN 50 THEN 'BUS_FLOW_PO_APP_ID'
WHEN 51 THEN 'BUS_FLOW_PO_DIST_TYPE'
WHEN 52 THEN 'BUS_FLOW_PO_ENTITY_CODE'
WHEN 53 THEN 'BUS_FLOW_PO_DIST_ID'
WHEN 54 THEN 'BUS_FLOW_PO_DOC_ID'
WHEN 55 THEN 'FEDERAL_PAID_UNEXP_OBG_AMT'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l1.AID_DESCRIPTION)
WHEN 2 THEN TO_CHAR(l1.AID_DIST_CCID)
WHEN 3 THEN TO_CHAR(l4.FEDERAL_ANTICIPATION)
WHEN 4 THEN TO_CHAR(l4.FEDERAL_FUND_CATEGORY)
WHEN 5 THEN TO_CHAR(l4.FEDERAL_FUND_EXPIRED_STATUS)
WHEN 6 THEN TO_CHAR(l4.FEDERAL_FUND_CAT_DESC)
WHEN 7 THEN TO_CHAR(l1.AID_LINE_TYPE_LOOKUP_CODE)
WHEN 8 THEN TO_CHAR(l4.FEDERAL_PRIOR_YEAR_FLAG)
WHEN 9 THEN TO_CHAR(l1.ENCUMBRANCE_AMOUNT)
WHEN 10 THEN TO_CHAR(l1.POD_ACCRUE_ON_RECEIPT_FLAG)
WHEN 11 THEN TO_CHAR(l1.PO_DISTRIBUTION_ID)
WHEN 12 THEN TO_CHAR(l4.FEDERAL_ADJUSTMENT_TYPE)
WHEN 13 THEN TO_CHAR(l1.DIST_ACCOUNT_REVERSAL_OPTION)
WHEN 14 THEN TO_CHAR(l1.DISTRIBUTION_LINK_TYPE)
WHEN 15 THEN TO_CHAR(l1.ALLOC_TO_MAIN_DIST_ID)
WHEN 16 THEN TO_CHAR(l1.BUS_FLOW_AP_APP_ID)
WHEN 17 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_TYPE)
WHEN 18 THEN TO_CHAR(l1.BUS_FLOW_INV_ENTITY_CODE)
WHEN 19 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_ID)
WHEN 20 THEN TO_CHAR(l1.BUS_FLOW_INV_ID)
WHEN 21 THEN TO_CHAR(l1.AID_INVOICE_DIST_ID)
WHEN 22 THEN TO_CHAR(l1.UPG_ENC_CR_CCID)
WHEN 23 THEN TO_CHAR(l1.UPG_ENC_CR_AMT)
WHEN 24 THEN TO_CHAR(l1.UPG_ENC_CR_BASE_AMT)
WHEN 25 THEN TO_CHAR(l1.UPG_ENC_DR_CCID)
WHEN 26 THEN TO_CHAR(l1.UPG_ENC_DR_AMT)
WHEN 27 THEN TO_CHAR(l1.UPG_ENC_DR_BASE_AMT)
WHEN 28 THEN TO_CHAR(l1.UPG_AP_ENCUM_OPTION)
WHEN 29 THEN TO_CHAR(l4.FEDERAL_ANTICIPATED_AMT)
WHEN 30 THEN TO_CHAR(l1.DEFERRED_END_DATE)
WHEN 31 THEN TO_CHAR(l1.DEFERRED_OPTION)
WHEN 32 THEN TO_CHAR(l1.DEFERRED_START_DATE)
WHEN 33 THEN TO_CHAR(l1.OVERRIDE_ACCTD_AMT_FLAG)
WHEN 34 THEN TO_CHAR(l1.AID_PARENT_REVERSAL_ID)
WHEN 35 THEN TO_CHAR(l1.AID_STAT_AMOUNT)
WHEN 36 THEN TO_CHAR(l5.TAX_LINE_ID)
WHEN 37 THEN TO_CHAR(l6.REC_NREC_TAX_DIST_ID)
WHEN 38 THEN TO_CHAR(l1.SUMMARY_TAX_LINE_ID)
WHEN 39 THEN TO_CHAR(l1.UPG_CR_ENC_TYPE_ID)
WHEN 40 THEN TO_CHAR(l1.UPG_DR_ENC_TYPE_ID)
WHEN 41 THEN TO_CHAR(l1.ENCUMBRANCE_BASE_AMOUNT)
WHEN 42 THEN TO_CHAR(l4.FEDERAL_UNANTICIPATED_AMT)
WHEN 43 THEN TO_CHAR(l1.AID_AMOUNT)
WHEN 44 THEN TO_CHAR(l1.INV_DIST_BASE_AMOUNT)
WHEN 45 THEN TO_CHAR(l1.AID_BASE_AMT_NO_ROUND)
WHEN 46 THEN TO_CHAR(l1.AID_AMOUNT_VARIANCE)
WHEN 47 THEN TO_CHAR(l1.AID_BASE_AMOUNT_VARIANCE)
WHEN 48 THEN TO_CHAR(l1.AID_QUANTITY_VARIANCE)
WHEN 49 THEN TO_CHAR(l1.AID_BASE_QUANTITY_VARIANCE)
WHEN 50 THEN TO_CHAR(l1.BUS_FLOW_PO_APP_ID)
WHEN 51 THEN TO_CHAR(l1.BUS_FLOW_PO_DIST_TYPE)
WHEN 52 THEN TO_CHAR(l1.BUS_FLOW_PO_ENTITY_CODE)
WHEN 53 THEN TO_CHAR(l1.BUS_FLOW_PO_DIST_ID)
WHEN 54 THEN TO_CHAR(l1.BUS_FLOW_PO_DOC_ID)
WHEN 55 THEN TO_CHAR(l4.FEDERAL_PAID_UNEXP_OBG_AMT)
ELSE null
END source_value
, CASE r
WHEN 7 THEN fvl8.meaning
WHEN 10 THEN fvl11.meaning
WHEN 33 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l4
, ZX_AP_DEF_TAX_EXTRACT_V l5
, ZX_AP_TAX_JRNL_LINE_DESC_V l6
, fnd_lookup_values fvl8
, fnd_lookup_values fvl11
, fnd_lookup_values fvl42
, (select rownum r from all_objects where rownum <= 55 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND l1.event_id = xet.event_id
AND l4.federal_event_id (+) = l1.event_id and l4.federal_line_number (+) = l1.line_number AND l1.zrnd_tax_dist_id = l5.rec_nrec_tax_dist_id (+) AND l1.zrnd_tax_dist_id = l6.rec_nrec_tax_dist_id (+) AND fvl8.lookup_type(+) =
'INVOICE DISTRIBUTION TYPE'
AND fvl8.lookup_code(+) = l1.AID_LINE_TYPE_LOOKUP_CODE
AND fvl8.view_application_id(+) = 200
AND fvl8.language(+) = USERENV('LANG')
AND fvl11.lookup_type(+) = 'YES_NO'
AND fvl11.lookup_code(+) = l1.POD_ACCRUE_ON_RECEIPT_FLAG
AND fvl11.view_application_id(+) = 0
AND fvl11.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of line sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END of insert_sources_62'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00200_AAD_S_000012_BC_PKG.insert_sources_62');
END insert_sources_62;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: INVOICES
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, h2.AI_PAYMENT_STATUS_FLAG source_7
, h2.AI_INVOICE_TYPE_LOOKUP_CODE source_13
, fvl13.meaning source_13_meaning
, h2.AI_INVOICE_ID source_20
, h2.AI_INVOICE_CURRENCY_CODE source_29
, h2.INV_EXCHANGE_DATE source_36
, h2.INV_EXCHANGE_RATE source_37
, h2.INV_EXCHANGE_RATE_TYPE source_38
, h2.AI_VENDOR_ID source_43
, h2.AI_VENDOR_SITE_ID source_44
, h2.THIRD_PARTY_TYPE source_45
, h3.FSP_PURCH_ENCUMBRANCE_FLAG source_60
, fvl60.meaning source_60_meaning
, h2.INV_DOC_SEQUENCE_CATEGORY source_71
, h2.INV_DOC_SEQUENCE_IDENTIFIER source_72
, h2.INV_DOC_SEQUENCE_VALUE source_73
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, AP_SYSTEM_PARAMETERS_EXTRACT_V h3
, fnd_lookup_values fvl13
, fnd_lookup_values fvl60
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND h2.event_id = xet.event_id
AND h3.asp_org_id = h2.ai_org_id AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
AND fvl60.lookup_type(+) = 'YES_NO'
AND fvl60.lookup_code(+) = h3.FSP_PURCH_ENCUMBRANCE_FLAG
AND fvl60.view_application_id(+) = 0
AND fvl60.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: INVOICES
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, l1.LINE_NUMBER
, l1.AID_DESCRIPTION source_1
, l1.AID_DIST_CCID source_2
, l4.FEDERAL_ANTICIPATION source_3
, l4.FEDERAL_FUND_CATEGORY source_4
, l4.FEDERAL_FUND_EXPIRED_STATUS source_5
, l4.FEDERAL_FUND_CAT_DESC source_6
, l1.AID_LINE_TYPE_LOOKUP_CODE source_8
, fvl8.meaning source_8_meaning
, l4.FEDERAL_PRIOR_YEAR_FLAG source_9
, l1.ENCUMBRANCE_AMOUNT source_10
, l1.POD_ACCRUE_ON_RECEIPT_FLAG source_11
, fvl11.meaning source_11_meaning
, l1.PO_DISTRIBUTION_ID source_12
, l4.FEDERAL_ADJUSTMENT_TYPE source_14
, l1.DIST_ACCOUNT_REVERSAL_OPTION source_15
, l1.DISTRIBUTION_LINK_TYPE source_17
, l1.ALLOC_TO_MAIN_DIST_ID source_19
, l1.BUS_FLOW_AP_APP_ID source_21
, l1.BUS_FLOW_INV_DIST_TYPE source_22
, l1.BUS_FLOW_INV_ENTITY_CODE source_23
, l1.BUS_FLOW_INV_DIST_ID source_24
, l1.BUS_FLOW_INV_ID source_25
, l1.AID_INVOICE_DIST_ID source_26
, l1.UPG_ENC_CR_CCID source_27
, l1.UPG_ENC_CR_AMT source_28
, l1.UPG_ENC_CR_BASE_AMT source_30
, l1.UPG_ENC_DR_CCID source_31
, l1.UPG_ENC_DR_AMT source_32
, l1.UPG_ENC_DR_BASE_AMT source_33
, l1.UPG_AP_ENCUM_OPTION source_34
, l4.FEDERAL_ANTICIPATED_AMT source_35
, l1.DEFERRED_END_DATE source_39
, l1.DEFERRED_OPTION source_40
, l1.DEFERRED_START_DATE source_41
, l1.OVERRIDE_ACCTD_AMT_FLAG source_42
, fvl42.meaning source_42_meaning
, l1.AID_PARENT_REVERSAL_ID source_46
, l1.AID_STAT_AMOUNT source_47
, l5.TAX_LINE_ID source_48
, l6.REC_NREC_TAX_DIST_ID source_49
, l1.SUMMARY_TAX_LINE_ID source_50
, l1.UPG_CR_ENC_TYPE_ID source_51
, l1.UPG_DR_ENC_TYPE_ID source_52
, l1.ENCUMBRANCE_BASE_AMOUNT source_53
, l4.FEDERAL_UNANTICIPATED_AMT source_54
, l1.AID_AMOUNT source_55
, l1.INV_DIST_BASE_AMOUNT source_56
, l1.AID_BASE_AMT_NO_ROUND source_58
, l1.AID_AMOUNT_VARIANCE source_59
, l1.AID_BASE_AMOUNT_VARIANCE source_61
, l1.AID_QUANTITY_VARIANCE source_62
, l1.AID_BASE_QUANTITY_VARIANCE source_63
, l1.BUS_FLOW_PO_APP_ID source_64
, l1.BUS_FLOW_PO_DIST_TYPE source_65
, l1.BUS_FLOW_PO_ENTITY_CODE source_66
, l1.BUS_FLOW_PO_DIST_ID source_67
, l1.BUS_FLOW_PO_DOC_ID source_68
, l4.FEDERAL_PAID_UNEXP_OBG_AMT source_69
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l4
, ZX_AP_DEF_TAX_EXTRACT_V l5
, ZX_AP_TAX_JRNL_LINE_DESC_V l6
, fnd_lookup_values fvl8
, fnd_lookup_values fvl11
, fnd_lookup_values fvl42
WHERE xet.event_id between x_first_event_id and x_last_event_id
and xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND l1.event_id = xet.event_id
AND l4.Federal_Event_Id (+) = l1.event_Id AND l4.Federal_line_number (+) = l1.line_number AND l1.ZRND_tax_dist_id = l5.REC_NREC_TAX_DIST_ID (+) AND l1.ZRND_tax_dist_id = l6.REC_NREC_TAX_DIST_ID (+) AND fvl8.lookup_type(+) =
'INVOICE DISTRIBUTION TYPE'
AND fvl8.lookup_code(+) = l1.AID_LINE_TYPE_LOOKUP_CODE
AND fvl8.view_application_id(+) = 200
AND fvl8.language(+) = USERENV('LANG')
AND fvl11.lookup_type(+) = 'YES_NO'
AND fvl11.lookup_code(+) = l1.POD_ACCRUE_ON_RECEIPT_FLAG
AND fvl11.view_application_id(+) = 0
AND fvl11.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
;
g_array_event.DELETE;
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => hdr_idx);
l_result := XLA_AE_LINES_PKG.InsertLines;
l_result := XLA_AE_LINES_PKG.InsertLines ;
l_result := XLA_AE_HEADER_PKG.InsertHeaders ;
PROCEDURE insert_sources_63(
p_target_ledger_id IN NUMBER
, p_language IN VARCHAR2
, p_sla_ledger_id IN NUMBER
, p_pad_start_date IN DATE
, p_pad_end_date IN DATE
)
IS
C_EVENT_TYPE_CODE CONSTANT VARCHAR2(30) := 'PREPAYMENTS_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_63';
(p_msg => 'BEGIN of insert_sources_63'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT oracle_username
INTO p_apps_owner
FROM fnd_oracle_userid
WHERE read_only_flag = 'U'
;
INSERT INTO xla_diag_sources --hdr2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT
event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value ,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, 0 line_number
, CASE r
WHEN 1 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 2 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 3 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 4 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 5 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 6 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 7 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 8 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 9 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 10 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 11 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
WHEN 12 THEN 'AP_INVOICE_EXTRACT_HEADER_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'HEADER'
WHEN 2 THEN 'HEADER'
WHEN 3 THEN 'HEADER'
WHEN 4 THEN 'HEADER'
WHEN 5 THEN 'HEADER'
WHEN 6 THEN 'HEADER'
WHEN 7 THEN 'HEADER'
WHEN 8 THEN 'HEADER'
WHEN 9 THEN 'HEADER'
WHEN 10 THEN 'HEADER'
WHEN 11 THEN 'HEADER'
WHEN 12 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '200'
WHEN 4 THEN '200'
WHEN 5 THEN '200'
WHEN 6 THEN '200'
WHEN 7 THEN '200'
WHEN 8 THEN '200'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '200'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AI_INVOICE_TYPE_LOOKUP_CODE'
WHEN 2 THEN 'AI_INVOICE_ID'
WHEN 3 THEN 'AI_INVOICE_CURRENCY_CODE'
WHEN 4 THEN 'INV_EXCHANGE_DATE'
WHEN 5 THEN 'INV_EXCHANGE_RATE'
WHEN 6 THEN 'INV_EXCHANGE_RATE_TYPE'
WHEN 7 THEN 'AI_VENDOR_ID'
WHEN 8 THEN 'AI_VENDOR_SITE_ID'
WHEN 9 THEN 'THIRD_PARTY_TYPE'
WHEN 10 THEN 'INV_DOC_SEQUENCE_CATEGORY'
WHEN 11 THEN 'INV_DOC_SEQUENCE_IDENTIFIER'
WHEN 12 THEN 'INV_DOC_SEQUENCE_VALUE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h2.AI_INVOICE_TYPE_LOOKUP_CODE)
WHEN 2 THEN TO_CHAR(h2.AI_INVOICE_ID)
WHEN 3 THEN TO_CHAR(h2.AI_INVOICE_CURRENCY_CODE)
WHEN 4 THEN TO_CHAR(h2.INV_EXCHANGE_DATE)
WHEN 5 THEN TO_CHAR(h2.INV_EXCHANGE_RATE)
WHEN 6 THEN TO_CHAR(h2.INV_EXCHANGE_RATE_TYPE)
WHEN 7 THEN TO_CHAR(h2.AI_VENDOR_ID)
WHEN 8 THEN TO_CHAR(h2.AI_VENDOR_SITE_ID)
WHEN 9 THEN TO_CHAR(h2.THIRD_PARTY_TYPE)
WHEN 10 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_CATEGORY)
WHEN 11 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_IDENTIFIER)
WHEN 12 THEN TO_CHAR(h2.INV_DOC_SEQUENCE_VALUE)
ELSE null
END source_value
, CASE r
WHEN 1 THEN fvl13.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, fnd_lookup_values fvl13
,(select rownum r from all_objects where rownum <= 12 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND h2.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of header sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_diag_sources --line2
(
event_id
, ledger_id
, sla_ledger_id
, description_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, source_value
, source_meaning
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, program_update_date
, program_application_id
, program_id
, request_id
)
SELECT event_id
, p_target_ledger_id
, p_sla_ledger_id
, p_language
, object_name
, object_type_code
, line_number
, source_application_id
, source_type_code
, source_code
, SUBSTR(source_value,1,1996)
, SUBSTR(source_meaning ,1,200)
, xla_environment_pkg.g_Usr_Id
, TRUNC(SYSDATE)
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Usr_Id
, xla_environment_pkg.g_Login_Id
, TRUNC(SYSDATE)
, xla_environment_pkg.g_Prog_Appl_Id
, xla_environment_pkg.g_Prog_Id
, xla_environment_pkg.g_Req_Id
FROM (
SELECT xet.event_id event_id
, l1.line_number line_number
, CASE r
WHEN 1 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 2 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 3 THEN 'FV_XLA_AP_REF_V'
WHEN 4 THEN 'FV_XLA_AP_REF_V'
WHEN 5 THEN 'FV_XLA_AP_REF_V'
WHEN 6 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 7 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 8 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 9 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 10 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 11 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 12 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 13 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 14 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 15 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 16 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 17 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 18 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 19 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 20 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 21 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 22 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 23 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 24 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 25 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 26 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 27 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 28 THEN 'ZX_AP_DEF_TAX_EXTRACT_V'
WHEN 29 THEN 'ZX_AP_TAX_JRNL_LINE_DESC_V'
WHEN 30 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 31 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 32 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 33 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 34 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
WHEN 35 THEN 'AP_EXTRACT_INVOICE_DTLS_BC_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
WHEN 3 THEN 'LINE'
WHEN 4 THEN 'LINE'
WHEN 5 THEN 'LINE'
WHEN 6 THEN 'LINE'
WHEN 7 THEN 'LINE'
WHEN 8 THEN 'LINE'
WHEN 9 THEN 'LINE'
WHEN 10 THEN 'LINE'
WHEN 11 THEN 'LINE'
WHEN 12 THEN 'LINE'
WHEN 13 THEN 'LINE'
WHEN 14 THEN 'LINE'
WHEN 15 THEN 'LINE'
WHEN 16 THEN 'LINE'
WHEN 17 THEN 'LINE'
WHEN 18 THEN 'LINE'
WHEN 19 THEN 'LINE'
WHEN 20 THEN 'LINE'
WHEN 21 THEN 'LINE'
WHEN 22 THEN 'LINE'
WHEN 23 THEN 'LINE'
WHEN 24 THEN 'LINE'
WHEN 25 THEN 'LINE'
WHEN 26 THEN 'LINE'
WHEN 27 THEN 'LINE'
WHEN 28 THEN 'LINE'
WHEN 29 THEN 'LINE'
WHEN 30 THEN 'LINE'
WHEN 31 THEN 'LINE'
WHEN 32 THEN 'LINE'
WHEN 33 THEN 'LINE'
WHEN 34 THEN 'LINE'
WHEN 35 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '200'
WHEN 2 THEN '200'
WHEN 3 THEN '8901'
WHEN 4 THEN '8901'
WHEN 5 THEN '8901'
WHEN 6 THEN '200'
WHEN 7 THEN '200'
WHEN 8 THEN '200'
WHEN 9 THEN '200'
WHEN 10 THEN '200'
WHEN 11 THEN '200'
WHEN 12 THEN '200'
WHEN 13 THEN '200'
WHEN 14 THEN '200'
WHEN 15 THEN '200'
WHEN 16 THEN '200'
WHEN 17 THEN '200'
WHEN 18 THEN '200'
WHEN 19 THEN '200'
WHEN 20 THEN '200'
WHEN 21 THEN '200'
WHEN 22 THEN '200'
WHEN 23 THEN '200'
WHEN 24 THEN '200'
WHEN 25 THEN '200'
WHEN 26 THEN '200'
WHEN 27 THEN '200'
WHEN 28 THEN '200'
WHEN 29 THEN '200'
WHEN 30 THEN '200'
WHEN 31 THEN '200'
WHEN 32 THEN '200'
WHEN 33 THEN '200'
WHEN 34 THEN '200'
WHEN 35 THEN '200'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'AID_DESCRIPTION'
WHEN 2 THEN 'AID_DIST_CCID'
WHEN 3 THEN 'FEDERAL_FUND_CATEGORY'
WHEN 4 THEN 'FEDERAL_FUND_EXPIRED_STATUS'
WHEN 5 THEN 'FEDERAL_FUND_CAT_DESC'
WHEN 6 THEN 'DIST_ACCOUNT_REVERSAL_OPTION'
WHEN 7 THEN 'DISTRIBUTION_LINK_TYPE'
WHEN 8 THEN 'ALLOC_TO_MAIN_DIST_ID'
WHEN 9 THEN 'BUS_FLOW_AP_APP_ID'
WHEN 10 THEN 'BUS_FLOW_INV_DIST_TYPE'
WHEN 11 THEN 'BUS_FLOW_INV_ENTITY_CODE'
WHEN 12 THEN 'BUS_FLOW_INV_DIST_ID'
WHEN 13 THEN 'BUS_FLOW_INV_ID'
WHEN 14 THEN 'AID_INVOICE_DIST_ID'
WHEN 15 THEN 'UPG_ENC_CR_CCID'
WHEN 16 THEN 'UPG_ENC_CR_AMT'
WHEN 17 THEN 'UPG_ENC_CR_BASE_AMT'
WHEN 18 THEN 'UPG_ENC_DR_CCID'
WHEN 19 THEN 'UPG_ENC_DR_AMT'
WHEN 20 THEN 'UPG_ENC_DR_BASE_AMT'
WHEN 21 THEN 'UPG_AP_ENCUM_OPTION'
WHEN 22 THEN 'DEFERRED_END_DATE'
WHEN 23 THEN 'DEFERRED_OPTION'
WHEN 24 THEN 'DEFERRED_START_DATE'
WHEN 25 THEN 'OVERRIDE_ACCTD_AMT_FLAG'
WHEN 26 THEN 'AID_PARENT_REVERSAL_ID'
WHEN 27 THEN 'AID_STAT_AMOUNT'
WHEN 28 THEN 'TAX_LINE_ID'
WHEN 29 THEN 'REC_NREC_TAX_DIST_ID'
WHEN 30 THEN 'SUMMARY_TAX_LINE_ID'
WHEN 31 THEN 'UPG_CR_ENC_TYPE_ID'
WHEN 32 THEN 'UPG_DR_ENC_TYPE_ID'
WHEN 33 THEN 'AID_AMOUNT'
WHEN 34 THEN 'INV_DIST_BASE_AMOUNT'
WHEN 35 THEN 'POD_PO_DISTRIBUTION_ID'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l1.AID_DESCRIPTION)
WHEN 2 THEN TO_CHAR(l1.AID_DIST_CCID)
WHEN 3 THEN TO_CHAR(l3.FEDERAL_FUND_CATEGORY)
WHEN 4 THEN TO_CHAR(l3.FEDERAL_FUND_EXPIRED_STATUS)
WHEN 5 THEN TO_CHAR(l3.FEDERAL_FUND_CAT_DESC)
WHEN 6 THEN TO_CHAR(l1.DIST_ACCOUNT_REVERSAL_OPTION)
WHEN 7 THEN TO_CHAR(l1.DISTRIBUTION_LINK_TYPE)
WHEN 8 THEN TO_CHAR(l1.ALLOC_TO_MAIN_DIST_ID)
WHEN 9 THEN TO_CHAR(l1.BUS_FLOW_AP_APP_ID)
WHEN 10 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_TYPE)
WHEN 11 THEN TO_CHAR(l1.BUS_FLOW_INV_ENTITY_CODE)
WHEN 12 THEN TO_CHAR(l1.BUS_FLOW_INV_DIST_ID)
WHEN 13 THEN TO_CHAR(l1.BUS_FLOW_INV_ID)
WHEN 14 THEN TO_CHAR(l1.AID_INVOICE_DIST_ID)
WHEN 15 THEN TO_CHAR(l1.UPG_ENC_CR_CCID)
WHEN 16 THEN TO_CHAR(l1.UPG_ENC_CR_AMT)
WHEN 17 THEN TO_CHAR(l1.UPG_ENC_CR_BASE_AMT)
WHEN 18 THEN TO_CHAR(l1.UPG_ENC_DR_CCID)
WHEN 19 THEN TO_CHAR(l1.UPG_ENC_DR_AMT)
WHEN 20 THEN TO_CHAR(l1.UPG_ENC_DR_BASE_AMT)
WHEN 21 THEN TO_CHAR(l1.UPG_AP_ENCUM_OPTION)
WHEN 22 THEN TO_CHAR(l1.DEFERRED_END_DATE)
WHEN 23 THEN TO_CHAR(l1.DEFERRED_OPTION)
WHEN 24 THEN TO_CHAR(l1.DEFERRED_START_DATE)
WHEN 25 THEN TO_CHAR(l1.OVERRIDE_ACCTD_AMT_FLAG)
WHEN 26 THEN TO_CHAR(l1.AID_PARENT_REVERSAL_ID)
WHEN 27 THEN TO_CHAR(l1.AID_STAT_AMOUNT)
WHEN 28 THEN TO_CHAR(l4.TAX_LINE_ID)
WHEN 29 THEN TO_CHAR(l5.REC_NREC_TAX_DIST_ID)
WHEN 30 THEN TO_CHAR(l1.SUMMARY_TAX_LINE_ID)
WHEN 31 THEN TO_CHAR(l1.UPG_CR_ENC_TYPE_ID)
WHEN 32 THEN TO_CHAR(l1.UPG_DR_ENC_TYPE_ID)
WHEN 33 THEN TO_CHAR(l1.AID_AMOUNT)
WHEN 34 THEN TO_CHAR(l1.INV_DIST_BASE_AMOUNT)
WHEN 35 THEN TO_CHAR(l1.POD_PO_DISTRIBUTION_ID)
ELSE null
END source_value
, CASE r
WHEN 25 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l3
, ZX_AP_DEF_TAX_EXTRACT_V l4
, ZX_AP_TAX_JRNL_LINE_DESC_V l5
, fnd_lookup_values fvl42
, (select rownum r from all_objects where rownum <= 35 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_class_code = C_EVENT_CLASS_CODE
AND l1.event_id = xet.event_id
AND l3.federal_event_id (+) = l1.event_id and l3.federal_line_number (+) = l1.line_number AND l1.zrnd_tax_dist_id = l4.rec_nrec_tax_dist_id (+) AND l1.zrnd_tax_dist_id = l5.rec_nrec_tax_dist_id (+) AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
)
;
(p_msg => 'number of line sources inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
(p_msg => 'END of insert_sources_63'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00200_AAD_S_000012_BC_PKG.insert_sources_63');
END insert_sources_63;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: PREPAYMENTS
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, h2.AI_INVOICE_TYPE_LOOKUP_CODE source_13
, fvl13.meaning source_13_meaning
, h2.AI_INVOICE_ID source_20
, h2.AI_INVOICE_CURRENCY_CODE source_29
, h2.INV_EXCHANGE_DATE source_36
, h2.INV_EXCHANGE_RATE source_37
, h2.INV_EXCHANGE_RATE_TYPE source_38
, h2.AI_VENDOR_ID source_43
, h2.AI_VENDOR_SITE_ID source_44
, h2.THIRD_PARTY_TYPE source_45
, h2.INV_DOC_SEQUENCE_CATEGORY source_71
, h2.INV_DOC_SEQUENCE_IDENTIFIER source_72
, h2.INV_DOC_SEQUENCE_VALUE source_73
FROM xla_events_gt xet
, AP_INVOICE_EXTRACT_HEADER_V h2
, fnd_lookup_values fvl13
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND h2.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'INVOICE TYPE'
AND fvl13.lookup_code(+) = h2.AI_INVOICE_TYPE_LOOKUP_CODE
AND fvl13.view_application_id(+) = 200
AND fvl13.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: PREPAYMENTS
xet.entity_id
,xet.legal_entity_id
,xet.entity_code
,xet.transaction_number
,xet.event_id
,xet.event_class_code
,xet.event_type_code
,xet.event_number
,xet.event_date
,xet.transaction_date
,xet.reference_num_1
,xet.reference_num_2
,xet.reference_num_3
,xet.reference_num_4
,xet.reference_char_1
,xet.reference_char_2
,xet.reference_char_3
,xet.reference_char_4
,xet.reference_date_1
,xet.reference_date_2
,xet.reference_date_3
,xet.reference_date_4
,xet.event_created_by
,xet.budgetary_control_flag
, l1.LINE_NUMBER
, l1.AID_DESCRIPTION source_1
, l1.AID_DIST_CCID source_2
, l3.FEDERAL_FUND_CATEGORY source_4
, l3.FEDERAL_FUND_EXPIRED_STATUS source_5
, l3.FEDERAL_FUND_CAT_DESC source_6
, l1.DIST_ACCOUNT_REVERSAL_OPTION source_15
, l1.DISTRIBUTION_LINK_TYPE source_17
, l1.ALLOC_TO_MAIN_DIST_ID source_19
, l1.BUS_FLOW_AP_APP_ID source_21
, l1.BUS_FLOW_INV_DIST_TYPE source_22
, l1.BUS_FLOW_INV_ENTITY_CODE source_23
, l1.BUS_FLOW_INV_DIST_ID source_24
, l1.BUS_FLOW_INV_ID source_25
, l1.AID_INVOICE_DIST_ID source_26
, l1.UPG_ENC_CR_CCID source_27
, l1.UPG_ENC_CR_AMT source_28
, l1.UPG_ENC_CR_BASE_AMT source_30
, l1.UPG_ENC_DR_CCID source_31
, l1.UPG_ENC_DR_AMT source_32
, l1.UPG_ENC_DR_BASE_AMT source_33
, l1.UPG_AP_ENCUM_OPTION source_34
, l1.DEFERRED_END_DATE source_39
, l1.DEFERRED_OPTION source_40
, l1.DEFERRED_START_DATE source_41
, l1.OVERRIDE_ACCTD_AMT_FLAG source_42
, fvl42.meaning source_42_meaning
, l1.AID_PARENT_REVERSAL_ID source_46
, l1.AID_STAT_AMOUNT source_47
, l4.TAX_LINE_ID source_48
, l5.REC_NREC_TAX_DIST_ID source_49
, l1.SUMMARY_TAX_LINE_ID source_50
, l1.UPG_CR_ENC_TYPE_ID source_51
, l1.UPG_DR_ENC_TYPE_ID source_52
, l1.AID_AMOUNT source_55
, l1.INV_DIST_BASE_AMOUNT source_56
, l1.POD_PO_DISTRIBUTION_ID source_70
FROM xla_events_gt xet
, AP_EXTRACT_INVOICE_DTLS_BC_V l1
, FV_XLA_AP_REF_V l3
, ZX_AP_DEF_TAX_EXTRACT_V l4
, ZX_AP_TAX_JRNL_LINE_DESC_V l5
, fnd_lookup_values fvl42
WHERE xet.event_id between x_first_event_id and x_last_event_id
and xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_class_code = C_EVENT_CLASS_CODE
and xet.event_status_code <> 'N' AND l1.event_id = xet.event_id
AND l3.federal_event_id (+) = l1.event_id AND l3.federal_line_number (+) = l1.line_number AND l1.ZRND_tax_dist_id = l4.REC_NREC_TAX_DIST_ID (+) AND l1.ZRND_tax_dist_id = l5.REC_NREC_TAX_DIST_ID (+) AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = l1.OVERRIDE_ACCTD_AMT_FLAG
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
;
g_array_event.DELETE;
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => hdr_idx);
l_result := XLA_AE_LINES_PKG.InsertLines;
l_result := XLA_AE_LINES_PKG.InsertLines ;
l_result := XLA_AE_HEADER_PKG.InsertHeaders ;
insert_sources_60(
p_target_ledger_id => p_target_ledger_id
, p_language => l_language
, p_sla_ledger_id => l_sla_ledger_id
, p_pad_start_date => p_pad_start_date
, p_pad_end_date => p_pad_end_date
);
insert_sources_61(
p_target_ledger_id => p_target_ledger_id
, p_language => l_language
, p_sla_ledger_id => l_sla_ledger_id
, p_pad_start_date => p_pad_start_date
, p_pad_end_date => p_pad_end_date
);
insert_sources_62(
p_target_ledger_id => p_target_ledger_id
, p_language => l_language
, p_sla_ledger_id => l_sla_ledger_id
, p_pad_start_date => p_pad_start_date
, p_pad_end_date => p_pad_end_date
);
insert_sources_63(
p_target_ledger_id => p_target_ledger_id
, p_language => l_language
, p_sla_ledger_id => l_sla_ledger_id
, p_pad_start_date => p_pad_start_date
, p_pad_end_date => p_pad_end_date
);