[Home] [Help]
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);
PROCEDURE insert_sources_12(
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) := 'DELIVER_EXPENSE_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_12';
(p_msg => 'BEGIN of insert_sources_12'
,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 'PO_HEADERS_REF_V'
WHEN 2 THEN 'PO_DISTS_REF_V'
WHEN 3 THEN 'CST_XLA_RCV_REF_V'
WHEN 4 THEN 'CST_XLA_RCV_REF_V'
WHEN 5 THEN 'CST_XLA_RCV_REF_V'
WHEN 6 THEN 'CST_XLA_RCV_REF_V'
WHEN 7 THEN 'CST_XLA_RCV_HEADERS_V'
WHEN 8 THEN 'PO_DISTS_REF_V'
WHEN 9 THEN 'CST_XLA_RCV_REF_V'
WHEN 10 THEN 'CST_XLA_RCV_HEADERS_V'
WHEN 11 THEN 'CST_XLA_RCV_REF_V'
WHEN 12 THEN 'PSA_CST_XLA_UPG_V'
WHEN 13 THEN 'PO_HEADERS_REF_V'
WHEN 14 THEN 'CST_XLA_RCV_REF_V'
WHEN 15 THEN 'CST_XLA_RCV_HEADERS_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'
WHEN 15 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '201'
WHEN 2 THEN '201'
WHEN 3 THEN '707'
WHEN 4 THEN '707'
WHEN 5 THEN '707'
WHEN 6 THEN '707'
WHEN 7 THEN '707'
WHEN 8 THEN '201'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '201'
WHEN 14 THEN '707'
WHEN 15 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 2 THEN 'RESERVED_FLAG'
WHEN 3 THEN 'APPLIED_TO_APPL_ID'
WHEN 4 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 5 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 6 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 7 THEN 'DISTRIBUTION_TYPE'
WHEN 8 THEN 'PO_BUDGET_ACCOUNT'
WHEN 9 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 10 THEN 'CURRENCY_CODE'
WHEN 11 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 12 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 13 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 14 THEN 'PO_DISTRIBUTION_ID'
WHEN 15 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h5.PURCH_ENCUMBRANCE_FLAG)
WHEN 2 THEN TO_CHAR(h4.RESERVED_FLAG)
WHEN 3 THEN TO_CHAR(h3.APPLIED_TO_APPL_ID)
WHEN 4 THEN TO_CHAR(h3.APPLIED_TO_DIST_LINK_TYPE)
WHEN 5 THEN TO_CHAR(h3.APPLIED_TO_ENTITY_CODE)
WHEN 6 THEN TO_CHAR(h3.APPLIED_TO_PO_DOC_ID)
WHEN 7 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 8 THEN TO_CHAR(h4.PO_BUDGET_ACCOUNT)
WHEN 9 THEN TO_CHAR(h3.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 10 THEN TO_CHAR(h1.CURRENCY_CODE)
WHEN 11 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 12 THEN TO_CHAR(h6.CST_ENCUM_UPG_OPTION)
WHEN 13 THEN TO_CHAR(h5.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 14 THEN TO_CHAR(h3.PO_DISTRIBUTION_ID)
WHEN 15 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 7 THEN fvl13.meaning
WHEN 15 THEN fvl37.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_RCV_HEADERS_V h1
, CST_XLA_RCV_REF_V h3
, PO_DISTS_REF_V h4
, PO_HEADERS_REF_V h5
, PSA_CST_XLA_UPG_V h6
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
,(select rownum r from all_objects where rownum <= 15 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 h1.event_id = xet.event_id
AND h3.ref_rcv_accounting_event_id = h1.rcv_accounting_event_id AND h3.po_header_id = h4.po_header_id (+) and h3.po_distribution_id = h4.po_distribution_id (+) AND h3.po_header_id = h5.po_header_id (+) AND h3.rcv_transaction_id = h6.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.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
, l2.line_number line_number
, CASE r
WHEN 1 THEN 'CST_XLA_RCV_LINES_V'
WHEN 2 THEN 'CST_XLA_RCV_LINES_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'DISTRIBUTION_IDENTIFIER'
WHEN 2 THEN 'RCV_ACCOUNTING_LINE_TYPE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l2.DISTRIBUTION_IDENTIFIER)
WHEN 2 THEN TO_CHAR(l2.RCV_ACCOUNTING_LINE_TYPE)
ELSE null
END source_value
, null source_meaning
FROM xla_events_gt xet
, CST_XLA_RCV_LINES_V l2
, (select rownum r from all_objects where rownum <= 2 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 l2.event_id = xet.event_id
)
;
(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_12'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000005_BC_PKG.insert_sources_12');
END insert_sources_12;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: DELIVER_EXPENSE
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
, h5.PURCH_ENCUMBRANCE_FLAG source_2
, h4.RESERVED_FLAG source_3
, h3.APPLIED_TO_APPL_ID source_7
, h3.APPLIED_TO_DIST_LINK_TYPE source_8
, h3.APPLIED_TO_ENTITY_CODE source_9
, h3.APPLIED_TO_PO_DOC_ID source_11
, h1.DISTRIBUTION_TYPE source_13
, fvl13.meaning source_13_meaning
, h4.PO_BUDGET_ACCOUNT source_14
, h3.ENCUM_REVERSAL_AMOUNT_ENTERED source_15
, h1.CURRENCY_CODE source_16
, h3.ENCUMBRANCE_REVERSAL_AMOUNT source_17
, h6.CST_ENCUM_UPG_OPTION source_18
, h5.PURCH_ENCUMBRANCE_TYPE_ID source_19
, h3.PO_DISTRIBUTION_ID source_26
, h1.TRANSFER_TO_GL_INDICATOR source_37
, fvl37.meaning source_37_meaning
FROM xla_events_gt xet
, CST_XLA_RCV_HEADERS_V h1
, CST_XLA_RCV_REF_V h3
, PO_DISTS_REF_V h4
, PO_HEADERS_REF_V h5
, PSA_CST_XLA_UPG_V h6
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
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 h1.event_id = xet.event_id
AND h3.REF_RCV_ACCOUNTING_EVENT_ID = h1.RCV_ACCOUNTING_EVENT_ID AND h3.po_header_id = h4.po_header_id (+) AND h3.po_distribution_id = h4.po_distribution_id (+) AND h3.po_header_id = h5.po_header_id (+) AND h3.rcv_transaction_id = h6.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: DELIVER_EXPENSE
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
, l2.LINE_NUMBER
, l2.DISTRIBUTION_IDENTIFIER source_12
, l2.RCV_ACCOUNTING_LINE_TYPE source_25
FROM xla_events_gt xet
, CST_XLA_RCV_LINES_V l2
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 l2.event_id = xet.event_id
;
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_13(
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) := 'DIR_INTERORG_RCPT_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_13';
(p_msg => 'BEGIN of insert_sources_13'
,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 'CST_XLA_INV_ORG_PARAMS_REF_V'
WHEN 2 THEN 'CST_XLA_INV_REF_V'
WHEN 3 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_REF_V'
WHEN 6 THEN 'PSA_CST_XLA_UPG_V'
WHEN 7 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 8 THEN 'PO_REQ_DISTS_REF_V'
WHEN 9 THEN 'CST_XLA_INV_REF_V'
WHEN 10 THEN 'CST_XLA_INV_REF_V'
WHEN 11 THEN 'CST_XLA_INV_REF_V'
WHEN 12 THEN 'CST_XLA_INV_REF_V'
WHEN 13 THEN 'PO_REQ_DISTS_REF_V'
WHEN 14 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_HEADERS_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'
WHEN 15 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
WHEN 3 THEN '707'
WHEN 4 THEN '707'
WHEN 5 THEN '707'
WHEN 6 THEN '707'
WHEN 7 THEN '201'
WHEN 8 THEN '201'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '201'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 2 THEN 'APPLIED_TO_APPL_ID'
WHEN 3 THEN 'DISTRIBUTION_TYPE'
WHEN 4 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 5 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 6 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 7 THEN 'REQ_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'REQ_RESERVED_FLAG'
WHEN 9 THEN 'BUS_FLOW_REQ_DIST_TYPE'
WHEN 10 THEN 'BUS_FLOW_REQ_ENTITY_CODE'
WHEN 11 THEN 'BUS_FLOW_REQ_DIST_ID'
WHEN 12 THEN 'BUS_FLOW_REQ_ID'
WHEN 13 THEN 'REQ_BUDGET_ACCOUNT'
WHEN 14 THEN 'REQ_ENCUMBRANCE_TYPE_ID'
WHEN 15 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 2 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 3 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 4 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 5 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 6 THEN TO_CHAR(h7.CST_ENCUM_UPG_OPTION)
WHEN 7 THEN TO_CHAR(h6.REQ_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h5.REQ_RESERVED_FLAG)
WHEN 9 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_TYPE)
WHEN 10 THEN TO_CHAR(h4.BUS_FLOW_REQ_ENTITY_CODE)
WHEN 11 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_ID)
WHEN 12 THEN TO_CHAR(h4.BUS_FLOW_REQ_ID)
WHEN 13 THEN TO_CHAR(h5.REQ_BUDGET_ACCOUNT)
WHEN 14 THEN TO_CHAR(h6.REQ_ENCUMBRANCE_TYPE_ID)
WHEN 15 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 3 THEN fvl13.meaning
WHEN 15 THEN fvl37.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_ORG_PARAMS_REF_V h3
, CST_XLA_INV_REF_V h4
, PO_REQ_DISTS_REF_V h5
, PO_REQ_HEADERS_REF_V h6
, PSA_CST_XLA_UPG_V h7
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
,(select rownum r from all_objects where rownum <= 15 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 h1.event_id = xet.event_id
AND h3.inv_organization_id (+) = h1.organization_id AND h4.ref_transaction_id = h1.transaction_id AND h4.bus_flow_req_dist_id=h5.req_distribution_id (+) AND h4.bus_flow_req_id = h6.req_id (+) AND h4.rcv_transaction_id = h7.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.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
, l2.line_number line_number
, CASE r
WHEN 1 THEN 'CST_XLA_INV_LINES_V'
WHEN 2 THEN 'CST_XLA_INV_LINES_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'DISTRIBUTION_IDENTIFIER'
WHEN 2 THEN 'CURRENCY_CODE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l2.DISTRIBUTION_IDENTIFIER)
WHEN 2 THEN TO_CHAR(l2.CURRENCY_CODE)
ELSE null
END source_value
, null source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
, (select rownum r from all_objects where rownum <= 2 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 l2.event_id = xet.event_id
)
;
(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_13'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000005_BC_PKG.insert_sources_13');
END insert_sources_13;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: DIR_INTERORG_RCPT
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
, h3.ENCUMBRANCE_REVERSAL_FLAG source_4
, h4.APPLIED_TO_APPL_ID source_7
, h1.DISTRIBUTION_TYPE source_13
, fvl13.meaning source_13_meaning
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_15
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_17
, h7.CST_ENCUM_UPG_OPTION source_18
, h6.REQ_ENCUMBRANCE_FLAG source_28
, h5.REQ_RESERVED_FLAG source_29
, h4.BUS_FLOW_REQ_DIST_TYPE source_30
, h4.BUS_FLOW_REQ_ENTITY_CODE source_31
, h4.BUS_FLOW_REQ_DIST_ID source_32
, h4.BUS_FLOW_REQ_ID source_33
, h5.REQ_BUDGET_ACCOUNT source_34
, h6.REQ_ENCUMBRANCE_TYPE_ID source_35
, h1.TRANSFER_TO_GL_INDICATOR source_37
, fvl37.meaning source_37_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_ORG_PARAMS_REF_V h3
, CST_XLA_INV_REF_V h4
, PO_REQ_DISTS_REF_V h5
, PO_REQ_HEADERS_REF_V h6
, PSA_CST_XLA_UPG_V h7
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
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 h1.event_id = xet.event_id
AND h3.INV_ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h4.ref_transaction_id = h1.transaction_id AND h4.BUS_FLOW_REQ_DIST_ID=h5.REQ_DISTRIBUTION_ID (+) AND h4.BUS_FLOW_REQ_ID = h6.REQ_ID (+) AND h4.rcv_transaction_id = h7.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: DIR_INTERORG_RCPT
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
, l2.LINE_NUMBER
, l2.DISTRIBUTION_IDENTIFIER source_12
, l2.CURRENCY_CODE source_16
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
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 l2.event_id = xet.event_id
;
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_14(
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) := 'FOB_RCPT_RECIPIENT_RCPT_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_14';
(p_msg => 'BEGIN of insert_sources_14'
,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 'CST_XLA_INV_ORG_PARAMS_REF_V'
WHEN 2 THEN 'CST_XLA_INV_REF_V'
WHEN 3 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_REF_V'
WHEN 6 THEN 'PSA_CST_XLA_UPG_V'
WHEN 7 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 8 THEN 'PO_REQ_DISTS_REF_V'
WHEN 9 THEN 'CST_XLA_INV_REF_V'
WHEN 10 THEN 'CST_XLA_INV_REF_V'
WHEN 11 THEN 'CST_XLA_INV_REF_V'
WHEN 12 THEN 'CST_XLA_INV_REF_V'
WHEN 13 THEN 'PO_REQ_DISTS_REF_V'
WHEN 14 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_HEADERS_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'
WHEN 15 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
WHEN 3 THEN '707'
WHEN 4 THEN '707'
WHEN 5 THEN '707'
WHEN 6 THEN '707'
WHEN 7 THEN '201'
WHEN 8 THEN '201'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '201'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 2 THEN 'APPLIED_TO_APPL_ID'
WHEN 3 THEN 'DISTRIBUTION_TYPE'
WHEN 4 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 5 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 6 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 7 THEN 'REQ_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'REQ_RESERVED_FLAG'
WHEN 9 THEN 'BUS_FLOW_REQ_DIST_TYPE'
WHEN 10 THEN 'BUS_FLOW_REQ_ENTITY_CODE'
WHEN 11 THEN 'BUS_FLOW_REQ_DIST_ID'
WHEN 12 THEN 'BUS_FLOW_REQ_ID'
WHEN 13 THEN 'REQ_BUDGET_ACCOUNT'
WHEN 14 THEN 'REQ_ENCUMBRANCE_TYPE_ID'
WHEN 15 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 2 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 3 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 4 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 5 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 6 THEN TO_CHAR(h7.CST_ENCUM_UPG_OPTION)
WHEN 7 THEN TO_CHAR(h6.REQ_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h5.REQ_RESERVED_FLAG)
WHEN 9 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_TYPE)
WHEN 10 THEN TO_CHAR(h4.BUS_FLOW_REQ_ENTITY_CODE)
WHEN 11 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_ID)
WHEN 12 THEN TO_CHAR(h4.BUS_FLOW_REQ_ID)
WHEN 13 THEN TO_CHAR(h5.REQ_BUDGET_ACCOUNT)
WHEN 14 THEN TO_CHAR(h6.REQ_ENCUMBRANCE_TYPE_ID)
WHEN 15 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 3 THEN fvl13.meaning
WHEN 15 THEN fvl37.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_ORG_PARAMS_REF_V h3
, CST_XLA_INV_REF_V h4
, PO_REQ_DISTS_REF_V h5
, PO_REQ_HEADERS_REF_V h6
, PSA_CST_XLA_UPG_V h7
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
,(select rownum r from all_objects where rownum <= 15 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 h1.event_id = xet.event_id
AND h3.inv_organization_id (+) = h1.organization_id AND h4.ref_transaction_id = h1.transaction_id AND h4.bus_flow_req_dist_id=h5.req_distribution_id (+) AND h4.bus_flow_req_id = h6.req_id (+) AND h4.rcv_transaction_id = h7.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.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
, l2.line_number line_number
, CASE r
WHEN 1 THEN 'CST_XLA_INV_LINES_V'
WHEN 2 THEN 'CST_XLA_INV_LINES_V'
WHEN 3 THEN 'CST_XLA_INV_LINES_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
WHEN 3 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
WHEN 3 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'ACCOUNTING_LINE_TYPE_CODE'
WHEN 2 THEN 'DISTRIBUTION_IDENTIFIER'
WHEN 3 THEN 'CURRENCY_CODE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l2.ACCOUNTING_LINE_TYPE_CODE)
WHEN 2 THEN TO_CHAR(l2.DISTRIBUTION_IDENTIFIER)
WHEN 3 THEN TO_CHAR(l2.CURRENCY_CODE)
ELSE null
END source_value
, null source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
, (select rownum r from all_objects where rownum <= 3 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 l2.event_id = xet.event_id
)
;
(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_14'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000005_BC_PKG.insert_sources_14');
END insert_sources_14;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: FOB_RCPT_RECIPIENT_RCPT
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
, h3.ENCUMBRANCE_REVERSAL_FLAG source_4
, h4.APPLIED_TO_APPL_ID source_7
, h1.DISTRIBUTION_TYPE source_13
, fvl13.meaning source_13_meaning
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_15
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_17
, h7.CST_ENCUM_UPG_OPTION source_18
, h6.REQ_ENCUMBRANCE_FLAG source_28
, h5.REQ_RESERVED_FLAG source_29
, h4.BUS_FLOW_REQ_DIST_TYPE source_30
, h4.BUS_FLOW_REQ_ENTITY_CODE source_31
, h4.BUS_FLOW_REQ_DIST_ID source_32
, h4.BUS_FLOW_REQ_ID source_33
, h5.REQ_BUDGET_ACCOUNT source_34
, h6.REQ_ENCUMBRANCE_TYPE_ID source_35
, h1.TRANSFER_TO_GL_INDICATOR source_37
, fvl37.meaning source_37_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_ORG_PARAMS_REF_V h3
, CST_XLA_INV_REF_V h4
, PO_REQ_DISTS_REF_V h5
, PO_REQ_HEADERS_REF_V h6
, PSA_CST_XLA_UPG_V h7
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
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 h1.event_id = xet.event_id
AND h3.INV_ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h4.ref_transaction_id = h1.transaction_id AND h4.BUS_FLOW_REQ_DIST_ID=h5.REQ_DISTRIBUTION_ID (+) AND h4.BUS_FLOW_REQ_ID = h6.REQ_ID (+) AND h4.rcv_transaction_id = h7.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: FOB_RCPT_RECIPIENT_RCPT
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
, l2.LINE_NUMBER
, l2.ACCOUNTING_LINE_TYPE_CODE source_6
, l2.DISTRIBUTION_IDENTIFIER source_12
, l2.CURRENCY_CODE source_16
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
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 l2.event_id = xet.event_id
;
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_15(
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) := 'FOB_SHIP_SENDER_SHIP_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_15';
(p_msg => 'BEGIN of insert_sources_15'
,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 'CST_XLA_INV_HEADERS_V'
WHEN 2 THEN 'CST_XLA_INV_HEADERS_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'HEADER'
WHEN 2 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'DISTRIBUTION_TYPE'
WHEN 2 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 2 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 1 THEN fvl13.meaning
WHEN 2 THEN fvl37.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
,(select rownum r from all_objects where rownum <= 2 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 h1.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.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
, l2.line_number line_number
, CASE r
WHEN 1 THEN 'CST_XLA_INV_LINES_V'
WHEN 2 THEN 'CST_XLA_INV_LINES_V'
WHEN 3 THEN 'CST_XLA_INV_LINES_V'
WHEN 4 THEN 'CST_XLA_INV_LINES_V'
WHEN 5 THEN 'CST_XLA_INV_LINES_V'
WHEN 6 THEN 'CST_XLA_INV_LINES_V'
WHEN 7 THEN 'CST_XLA_INV_LINES_V'
WHEN 8 THEN 'CST_XLA_INV_LINES_V'
WHEN 9 THEN 'CST_XLA_INV_LINES_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'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
WHEN 3 THEN '707'
WHEN 4 THEN '707'
WHEN 5 THEN '707'
WHEN 6 THEN '707'
WHEN 7 THEN '707'
WHEN 8 THEN '707'
WHEN 9 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'CODE_COMBINATION_ID'
WHEN 2 THEN 'ACCOUNTING_LINE_TYPE_CODE'
WHEN 3 THEN 'DISTRIBUTION_IDENTIFIER'
WHEN 4 THEN 'CURRENCY_CODE'
WHEN 5 THEN 'ENTERED_AMOUNT'
WHEN 6 THEN 'CURRENCY_CONVERSION_DATE'
WHEN 7 THEN 'CURRENCY_CONVERSION_RATE'
WHEN 8 THEN 'CURRENCY_CONVERSION_TYPE'
WHEN 9 THEN 'ACCOUNTED_AMOUNT'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l2.CODE_COMBINATION_ID)
WHEN 2 THEN TO_CHAR(l2.ACCOUNTING_LINE_TYPE_CODE)
WHEN 3 THEN TO_CHAR(l2.DISTRIBUTION_IDENTIFIER)
WHEN 4 THEN TO_CHAR(l2.CURRENCY_CODE)
WHEN 5 THEN TO_CHAR(l2.ENTERED_AMOUNT)
WHEN 6 THEN TO_CHAR(l2.CURRENCY_CONVERSION_DATE)
WHEN 7 THEN TO_CHAR(l2.CURRENCY_CONVERSION_RATE)
WHEN 8 THEN TO_CHAR(l2.CURRENCY_CONVERSION_TYPE)
WHEN 9 THEN TO_CHAR(l2.ACCOUNTED_AMOUNT)
ELSE null
END source_value
, null source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
, (select rownum r from all_objects where rownum <= 9 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 l2.event_id = xet.event_id
)
;
(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_15'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000005_BC_PKG.insert_sources_15');
END insert_sources_15;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: FOB_SHIP_SENDER_SHIP
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
, h1.DISTRIBUTION_TYPE source_13
, fvl13.meaning source_13_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_37
, fvl37.meaning source_37_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
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 h1.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: FOB_SHIP_SENDER_SHIP
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
, l2.LINE_NUMBER
, l2.CODE_COMBINATION_ID source_1
, l2.ACCOUNTING_LINE_TYPE_CODE source_6
, l2.DISTRIBUTION_IDENTIFIER source_12
, l2.CURRENCY_CODE source_16
, l2.ENTERED_AMOUNT source_20
, l2.CURRENCY_CONVERSION_DATE source_21
, l2.CURRENCY_CONVERSION_RATE source_22
, l2.CURRENCY_CONVERSION_TYPE source_23
, l2.ACCOUNTED_AMOUNT source_24
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
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 l2.event_id = xet.event_id
;
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_16(
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) := 'INT_ORDER_TO_EXP_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_16';
(p_msg => 'BEGIN of insert_sources_16'
,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 'CST_XLA_INV_HEADERS_V'
WHEN 2 THEN 'CST_XLA_INV_HEADERS_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'HEADER'
WHEN 2 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'DISTRIBUTION_TYPE'
WHEN 2 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 2 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 1 THEN fvl13.meaning
WHEN 2 THEN fvl37.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
,(select rownum r from all_objects where rownum <= 2 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 h1.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.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
, l2.line_number line_number
, CASE r
WHEN 1 THEN 'CST_XLA_INV_LINES_V'
WHEN 2 THEN 'CST_XLA_INV_LINES_V'
WHEN 3 THEN 'CST_XLA_INV_LINES_V'
WHEN 4 THEN 'CST_XLA_INV_LINES_V'
WHEN 5 THEN 'CST_XLA_INV_LINES_V'
WHEN 6 THEN 'CST_XLA_INV_LINES_V'
WHEN 7 THEN 'CST_XLA_INV_LINES_V'
WHEN 8 THEN 'CST_XLA_INV_LINES_V'
WHEN 9 THEN 'CST_XLA_INV_LINES_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'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
WHEN 3 THEN '707'
WHEN 4 THEN '707'
WHEN 5 THEN '707'
WHEN 6 THEN '707'
WHEN 7 THEN '707'
WHEN 8 THEN '707'
WHEN 9 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'CODE_COMBINATION_ID'
WHEN 2 THEN 'ACCOUNTING_LINE_TYPE_CODE'
WHEN 3 THEN 'DISTRIBUTION_IDENTIFIER'
WHEN 4 THEN 'CURRENCY_CODE'
WHEN 5 THEN 'ENTERED_AMOUNT'
WHEN 6 THEN 'CURRENCY_CONVERSION_DATE'
WHEN 7 THEN 'CURRENCY_CONVERSION_RATE'
WHEN 8 THEN 'CURRENCY_CONVERSION_TYPE'
WHEN 9 THEN 'ACCOUNTED_AMOUNT'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l2.CODE_COMBINATION_ID)
WHEN 2 THEN TO_CHAR(l2.ACCOUNTING_LINE_TYPE_CODE)
WHEN 3 THEN TO_CHAR(l2.DISTRIBUTION_IDENTIFIER)
WHEN 4 THEN TO_CHAR(l2.CURRENCY_CODE)
WHEN 5 THEN TO_CHAR(l2.ENTERED_AMOUNT)
WHEN 6 THEN TO_CHAR(l2.CURRENCY_CONVERSION_DATE)
WHEN 7 THEN TO_CHAR(l2.CURRENCY_CONVERSION_RATE)
WHEN 8 THEN TO_CHAR(l2.CURRENCY_CONVERSION_TYPE)
WHEN 9 THEN TO_CHAR(l2.ACCOUNTED_AMOUNT)
ELSE null
END source_value
, null source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
, (select rownum r from all_objects where rownum <= 9 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 l2.event_id = xet.event_id
)
;
(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_16'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000005_BC_PKG.insert_sources_16');
END insert_sources_16;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: INT_ORDER_TO_EXP
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
, h1.DISTRIBUTION_TYPE source_13
, fvl13.meaning source_13_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_37
, fvl37.meaning source_37_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
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 h1.event_id = xet.event_id
AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: INT_ORDER_TO_EXP
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
, l2.LINE_NUMBER
, l2.CODE_COMBINATION_ID source_1
, l2.ACCOUNTING_LINE_TYPE_CODE source_6
, l2.DISTRIBUTION_IDENTIFIER source_12
, l2.CURRENCY_CODE source_16
, l2.ENTERED_AMOUNT source_20
, l2.CURRENCY_CONVERSION_DATE source_21
, l2.CURRENCY_CONVERSION_RATE source_22
, l2.CURRENCY_CONVERSION_TYPE source_23
, l2.ACCOUNTED_AMOUNT source_24
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
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 l2.event_id = xet.event_id
;
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_17(
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) := 'PERIOD_END_ACCRUAL_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_17';
(p_msg => 'BEGIN of insert_sources_17'
,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 'PO_HEADERS_REF_V'
WHEN 2 THEN 'CST_XLA_RCV_REF_V'
WHEN 3 THEN 'CST_XLA_RCV_REF_V'
WHEN 4 THEN 'CST_XLA_RCV_REF_V'
WHEN 5 THEN 'CST_XLA_RCV_REF_V'
WHEN 6 THEN 'CST_XLA_RCV_HEADERS_V'
WHEN 7 THEN 'PO_DISTS_REF_V'
WHEN 8 THEN 'CST_XLA_RCV_REF_V'
WHEN 9 THEN 'CST_XLA_RCV_HEADERS_V'
WHEN 10 THEN 'CST_XLA_RCV_REF_V'
WHEN 11 THEN 'PO_HEADERS_REF_V'
WHEN 12 THEN 'CST_XLA_RCV_REF_V'
WHEN 13 THEN 'PSA_CST_XLA_PEA_UPG_V'
WHEN 14 THEN 'CST_XLA_RCV_HEADERS_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 '201'
WHEN 2 THEN '707'
WHEN 3 THEN '707'
WHEN 4 THEN '707'
WHEN 5 THEN '707'
WHEN 6 THEN '707'
WHEN 7 THEN '201'
WHEN 8 THEN '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '201'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 2 THEN 'APPLIED_TO_APPL_ID'
WHEN 3 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 4 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 5 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'PO_BUDGET_ACCOUNT'
WHEN 8 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 9 THEN 'CURRENCY_CODE'
WHEN 10 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 11 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 12 THEN 'PO_DISTRIBUTION_ID'
WHEN 13 THEN 'CST_PEA_ENC_UPG_OPTION'
WHEN 14 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h5.PURCH_ENCUMBRANCE_FLAG)
WHEN 2 THEN TO_CHAR(h3.APPLIED_TO_APPL_ID)
WHEN 3 THEN TO_CHAR(h3.APPLIED_TO_DIST_LINK_TYPE)
WHEN 4 THEN TO_CHAR(h3.APPLIED_TO_ENTITY_CODE)
WHEN 5 THEN TO_CHAR(h3.APPLIED_TO_PO_DOC_ID)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h4.PO_BUDGET_ACCOUNT)
WHEN 8 THEN TO_CHAR(h3.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 9 THEN TO_CHAR(h1.CURRENCY_CODE)
WHEN 10 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 11 THEN TO_CHAR(h5.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 12 THEN TO_CHAR(h3.PO_DISTRIBUTION_ID)
WHEN 13 THEN TO_CHAR(h6.CST_PEA_ENC_UPG_OPTION)
WHEN 14 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl13.meaning
WHEN 14 THEN fvl37.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_RCV_HEADERS_V h1
, CST_XLA_RCV_REF_V h3
, PO_DISTS_REF_V h4
, PO_HEADERS_REF_V h5
, PSA_CST_XLA_PEA_UPG_V h6
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
,(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 h1.event_id = xet.event_id
AND h3.ref_rcv_accounting_event_id = h1.rcv_accounting_event_id AND h3.po_header_id = h4.po_header_id (+) and h3.po_distribution_id = h4.po_distribution_id (+) AND h3.po_header_id = h5.po_header_id (+) AND h3.ref_rcv_accounting_event_id = h6.accounting_event_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.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
, l2.line_number line_number
, CASE r
WHEN 1 THEN 'CST_XLA_RCV_LINES_V'
WHEN 2 THEN 'CST_XLA_RCV_LINES_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'DISTRIBUTION_IDENTIFIER'
WHEN 2 THEN 'RCV_ACCOUNTING_LINE_TYPE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l2.DISTRIBUTION_IDENTIFIER)
WHEN 2 THEN TO_CHAR(l2.RCV_ACCOUNTING_LINE_TYPE)
ELSE null
END source_value
, null source_meaning
FROM xla_events_gt xet
, CST_XLA_RCV_LINES_V l2
, (select rownum r from all_objects where rownum <= 2 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 l2.event_id = xet.event_id
)
;
(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_17'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000005_BC_PKG.insert_sources_17');
END insert_sources_17;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: PERIOD_END_ACCRUAL
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
, h5.PURCH_ENCUMBRANCE_FLAG source_2
, h3.APPLIED_TO_APPL_ID source_7
, h3.APPLIED_TO_DIST_LINK_TYPE source_8
, h3.APPLIED_TO_ENTITY_CODE source_9
, h3.APPLIED_TO_PO_DOC_ID source_11
, h1.DISTRIBUTION_TYPE source_13
, fvl13.meaning source_13_meaning
, h4.PO_BUDGET_ACCOUNT source_14
, h3.ENCUM_REVERSAL_AMOUNT_ENTERED source_15
, h1.CURRENCY_CODE source_16
, h3.ENCUMBRANCE_REVERSAL_AMOUNT source_17
, h5.PURCH_ENCUMBRANCE_TYPE_ID source_19
, h3.PO_DISTRIBUTION_ID source_26
, h6.CST_PEA_ENC_UPG_OPTION source_27
, h1.TRANSFER_TO_GL_INDICATOR source_37
, fvl37.meaning source_37_meaning
FROM xla_events_gt xet
, CST_XLA_RCV_HEADERS_V h1
, CST_XLA_RCV_REF_V h3
, PO_DISTS_REF_V h4
, PO_HEADERS_REF_V h5
, PSA_CST_XLA_PEA_UPG_V h6
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
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 h1.event_id = xet.event_id
AND h3.REF_RCV_ACCOUNTING_EVENT_ID = h1.RCV_ACCOUNTING_EVENT_ID AND h3.po_header_id = h4.po_header_id (+) AND h3.po_distribution_id = h4.po_distribution_id (+) AND h3.po_header_id = h5.po_header_id (+) AND h3.ref_rcv_accounting_event_id = h6.accounting_event_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: PERIOD_END_ACCRUAL
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
, l2.LINE_NUMBER
, l2.DISTRIBUTION_IDENTIFIER source_12
, l2.RCV_ACCOUNTING_LINE_TYPE source_25
FROM xla_events_gt xet
, CST_XLA_RCV_LINES_V l2
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 l2.event_id = xet.event_id
;
g_array_event.DELETE;
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => hdr_idx);
XLA_AE_JOURNAL_ENTRY_PKG.updateJournalEntryStatus (p_hdr_idx => g_last_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_18(
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) := 'PURCHASE_ORDER_ALL';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_18';
(p_msg => 'BEGIN of insert_sources_18'
,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 'PO_HEADERS_REF_V'
WHEN 2 THEN 'PO_DISTS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_ORG_PARAMS_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_REF_V'
WHEN 6 THEN 'CST_XLA_INV_REF_V'
WHEN 7 THEN 'CST_XLA_INV_REF_V'
WHEN 8 THEN 'CST_XLA_INV_REF_V'
WHEN 9 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 10 THEN 'PO_DISTS_REF_V'
WHEN 11 THEN 'CST_XLA_INV_REF_V'
WHEN 12 THEN 'CST_XLA_INV_REF_V'
WHEN 13 THEN 'PSA_CST_XLA_UPG_V'
WHEN 14 THEN 'PO_HEADERS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_HEADERS_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'
WHEN 15 THEN 'HEADER'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '201'
WHEN 2 THEN '201'
WHEN 3 THEN '707'
WHEN 4 THEN '707'
WHEN 5 THEN '707'
WHEN 6 THEN '707'
WHEN 7 THEN '707'
WHEN 8 THEN '707'
WHEN 9 THEN '707'
WHEN 10 THEN '201'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 2 THEN 'RESERVED_FLAG'
WHEN 3 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 4 THEN 'APPLIED_TO_APPL_ID'
WHEN 5 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 6 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 7 THEN 'TXN_PO_DISTRIBUTION_ID'
WHEN 8 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 9 THEN 'DISTRIBUTION_TYPE'
WHEN 10 THEN 'PO_BUDGET_ACCOUNT'
WHEN 11 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 12 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 13 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 14 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 15 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h6.PURCH_ENCUMBRANCE_FLAG)
WHEN 2 THEN TO_CHAR(h5.RESERVED_FLAG)
WHEN 3 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 4 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 5 THEN TO_CHAR(h4.APPLIED_TO_DIST_LINK_TYPE)
WHEN 6 THEN TO_CHAR(h4.APPLIED_TO_ENTITY_CODE)
WHEN 7 THEN TO_CHAR(h4.TXN_PO_DISTRIBUTION_ID)
WHEN 8 THEN TO_CHAR(h4.APPLIED_TO_PO_DOC_ID)
WHEN 9 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 10 THEN TO_CHAR(h5.PO_BUDGET_ACCOUNT)
WHEN 11 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 12 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 13 THEN TO_CHAR(h7.CST_ENCUM_UPG_OPTION)
WHEN 14 THEN TO_CHAR(h6.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 15 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 9 THEN fvl13.meaning
WHEN 15 THEN fvl37.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_ORG_PARAMS_REF_V h3
, CST_XLA_INV_REF_V h4
, PO_DISTS_REF_V h5
, PO_HEADERS_REF_V h6
, PSA_CST_XLA_UPG_V h7
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
,(select rownum r from all_objects where rownum <= 15 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 h1.event_id = xet.event_id
AND h3.inv_organization_id (+) = h1.organization_id AND h4.ref_transaction_id = h1.transaction_id AND h4.txn_po_header_id = h5.po_header_id (+) and h4.txn_po_distribution_id = h5.po_distribution_id (+) AND h4.txn_po_header_id = h6.po_header_id (+) AND h4.rcv_transaction_id = h7.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.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
, l2.line_number line_number
, CASE r
WHEN 1 THEN 'CST_XLA_INV_LINES_V'
WHEN 2 THEN 'CST_XLA_INV_LINES_V'
WHEN 3 THEN 'CST_XLA_INV_LINES_V'
ELSE null
END object_name
, CASE r
WHEN 1 THEN 'LINE'
WHEN 2 THEN 'LINE'
WHEN 3 THEN 'LINE'
ELSE null
END object_type_code
, CASE r
WHEN 1 THEN '707'
WHEN 2 THEN '707'
WHEN 3 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'ACCOUNTING_LINE_TYPE_CODE'
WHEN 2 THEN 'DISTRIBUTION_IDENTIFIER'
WHEN 3 THEN 'CURRENCY_CODE'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(l2.ACCOUNTING_LINE_TYPE_CODE)
WHEN 2 THEN TO_CHAR(l2.DISTRIBUTION_IDENTIFIER)
WHEN 3 THEN TO_CHAR(l2.CURRENCY_CODE)
ELSE null
END source_value
, null source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
, (select rownum r from all_objects where rownum <= 3 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 l2.event_id = xet.event_id
)
;
(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_18'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000005_BC_PKG.insert_sources_18');
END insert_sources_18;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: PURCHASE_ORDER
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
, h6.PURCH_ENCUMBRANCE_FLAG source_2
, h5.RESERVED_FLAG source_3
, h3.ENCUMBRANCE_REVERSAL_FLAG source_4
, h4.APPLIED_TO_APPL_ID source_7
, h4.APPLIED_TO_DIST_LINK_TYPE source_8
, h4.APPLIED_TO_ENTITY_CODE source_9
, h4.TXN_PO_DISTRIBUTION_ID source_10
, h4.APPLIED_TO_PO_DOC_ID source_11
, h1.DISTRIBUTION_TYPE source_13
, fvl13.meaning source_13_meaning
, h5.PO_BUDGET_ACCOUNT source_14
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_15
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_17
, h7.CST_ENCUM_UPG_OPTION source_18
, h6.PURCH_ENCUMBRANCE_TYPE_ID source_19
, h1.TRANSFER_TO_GL_INDICATOR source_37
, fvl37.meaning source_37_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_ORG_PARAMS_REF_V h3
, CST_XLA_INV_REF_V h4
, PO_DISTS_REF_V h5
, PO_HEADERS_REF_V h6
, PSA_CST_XLA_UPG_V h7
, fnd_lookup_values fvl13
, fnd_lookup_values fvl37
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 h1.event_id = xet.event_id
AND h3.INV_ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h4.ref_transaction_id = h1.transaction_id AND h4.txn_po_header_id = h5.po_header_id (+) AND h4.txn_po_distribution_id = h5.po_distribution_id (+) AND h4.txn_po_header_id = h6.po_header_id (+) AND h4.rcv_transaction_id = h7.transaction_id (+) AND fvl13.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl13.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl13.view_application_id(+) = 700
AND fvl13.language(+) = USERENV('LANG')
AND fvl37.lookup_type(+) = 'YES_NO'
AND fvl37.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl37.view_application_id(+) = 0
AND fvl37.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Class Code: PURCHASE_ORDER
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
, l2.LINE_NUMBER
, l2.ACCOUNTING_LINE_TYPE_CODE source_6
, l2.DISTRIBUTION_IDENTIFIER source_12
, l2.CURRENCY_CODE source_16
FROM xla_events_gt xet
, CST_XLA_INV_LINES_V l2
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 l2.event_id = xet.event_id
;
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_12(
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_13(
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_14(
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_15(
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_16(
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_17(
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_18(
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
);