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);
PROCEDURE insert_sources_27(
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_27';
(p_msg => 'BEGIN of insert_sources_27'
,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_RCV_HEADERS_V'
WHEN 2 THEN 'CST_XLA_RCV_HEADERS_V'
WHEN 3 THEN 'PO_HEADERS_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_REF_V'
WHEN 8 THEN 'PO_DISTS_REF_V'
WHEN 9 THEN 'CST_XLA_RCV_REF_V'
WHEN 10 THEN 'CST_XLA_RCV_REF_V'
WHEN 11 THEN 'PSA_CST_XLA_UPG_V'
WHEN 12 THEN 'PO_HEADERS_REF_V'
WHEN 13 THEN 'CST_XLA_RCV_REF_V'
WHEN 14 THEN 'PO_DISTS_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 '707'
WHEN 2 THEN '707'
WHEN 3 THEN '201'
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 '201'
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 'DISTRIBUTION_TYPE'
WHEN 2 THEN 'CURRENCY_CODE'
WHEN 3 THEN 'PURCH_ENCUMBRANCE_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 'APPLIED_TO_PO_DOC_ID'
WHEN 8 THEN 'PO_BUDGET_ACCOUNT'
WHEN 9 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 10 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 11 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 12 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 13 THEN 'PO_DISTRIBUTION_ID'
WHEN 14 THEN 'RESERVED_FLAG'
WHEN 15 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.CURRENCY_CODE)
WHEN 3 THEN TO_CHAR(h5.PURCH_ENCUMBRANCE_FLAG)
WHEN 4 THEN TO_CHAR(h3.APPLIED_TO_APPL_ID)
WHEN 5 THEN TO_CHAR(h3.APPLIED_TO_DIST_LINK_TYPE)
WHEN 6 THEN TO_CHAR(h3.APPLIED_TO_ENTITY_CODE)
WHEN 7 THEN TO_CHAR(h3.APPLIED_TO_PO_DOC_ID)
WHEN 8 THEN TO_CHAR(h4.PO_BUDGET_ACCOUNT)
WHEN 9 THEN TO_CHAR(h3.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 10 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 11 THEN TO_CHAR(h6.CST_ENCUM_UPG_OPTION)
WHEN 12 THEN TO_CHAR(h5.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 13 THEN TO_CHAR(h3.PO_DISTRIBUTION_ID)
WHEN 14 THEN TO_CHAR(h4.RESERVED_FLAG)
WHEN 15 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 1 THEN fvl9.meaning
WHEN 15 THEN fvl42.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 fvl9
, fnd_lookup_values fvl42
,(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 fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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_27'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_27');
END insert_sources_27;
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
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h1.CURRENCY_CODE source_11
, h5.PURCH_ENCUMBRANCE_FLAG source_16
, h3.APPLIED_TO_APPL_ID source_19
, h3.APPLIED_TO_DIST_LINK_TYPE source_20
, h3.APPLIED_TO_ENTITY_CODE source_21
, h3.APPLIED_TO_PO_DOC_ID source_23
, h4.PO_BUDGET_ACCOUNT source_24
, h3.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h3.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h6.CST_ENCUM_UPG_OPTION source_27
, h5.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h3.PO_DISTRIBUTION_ID source_30
, h4.RESERVED_FLAG source_31
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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 fvl9
, fnd_lookup_values fvl42
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 fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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_8
, l2.RCV_ACCOUNTING_LINE_TYPE source_29
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_28(
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_28';
(p_msg => 'BEGIN of insert_sources_28'
,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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'CST_XLA_INV_ORG_PARAMS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_REF_V'
WHEN 9 THEN 'CST_XLA_INV_REF_V'
WHEN 10 THEN 'CST_XLA_INV_REF_V'
WHEN 11 THEN 'PSA_CST_XLA_UPG_V'
WHEN 12 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 13 THEN 'PO_REQ_DISTS_REF_V'
WHEN 14 THEN 'CST_XLA_INV_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'CST_XLA_INV_REF_V'
WHEN 18 THEN 'PO_REQ_DISTS_REF_V'
WHEN 19 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 8 THEN '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '201'
WHEN 13 THEN '201'
WHEN 14 THEN '707'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 8 THEN 'APPLIED_TO_APPL_ID'
WHEN 9 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 10 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 11 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 12 THEN 'REQ_ENCUMBRANCE_FLAG'
WHEN 13 THEN 'REQ_RESERVED_FLAG'
WHEN 14 THEN 'BUS_FLOW_REQ_DIST_TYPE'
WHEN 15 THEN 'BUS_FLOW_REQ_ENTITY_CODE'
WHEN 16 THEN 'BUS_FLOW_REQ_DIST_ID'
WHEN 17 THEN 'BUS_FLOW_REQ_ID'
WHEN 18 THEN 'REQ_BUDGET_ACCOUNT'
WHEN 19 THEN 'REQ_ENCUMBRANCE_TYPE_ID'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 8 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 9 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 10 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 11 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 12 THEN TO_CHAR(h8.REQ_ENCUMBRANCE_FLAG)
WHEN 13 THEN TO_CHAR(h7.REQ_RESERVED_FLAG)
WHEN 14 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_TYPE)
WHEN 15 THEN TO_CHAR(h4.BUS_FLOW_REQ_ENTITY_CODE)
WHEN 16 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_ID)
WHEN 17 THEN TO_CHAR(h4.BUS_FLOW_REQ_ID)
WHEN 18 THEN TO_CHAR(h7.REQ_BUDGET_ACCOUNT)
WHEN 19 THEN TO_CHAR(h8.REQ_ENCUMBRANCE_TYPE_ID)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_REQ_DISTS_REF_V h7
, PO_REQ_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 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 h5.inventory_item_id = h1.inventory_item_id and h5.organization_id = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.bus_flow_req_dist_id=h7.req_distribution_id (+) AND h4.bus_flow_req_id = h8.req_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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_28'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_28');
END insert_sources_28;
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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.REQ_ENCUMBRANCE_FLAG source_33
, h7.REQ_RESERVED_FLAG source_34
, h4.BUS_FLOW_REQ_DIST_TYPE source_35
, h4.BUS_FLOW_REQ_ENTITY_CODE source_36
, h4.BUS_FLOW_REQ_DIST_ID source_37
, h4.BUS_FLOW_REQ_ID source_38
, h7.REQ_BUDGET_ACCOUNT source_39
, h8.REQ_ENCUMBRANCE_TYPE_ID source_40
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_REQ_DISTS_REF_V h7
, PO_REQ_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
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 h5.INVENTORY_ITEM_ID = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.BUS_FLOW_REQ_DIST_ID=h7.REQ_DISTRIBUTION_ID (+) AND h4.BUS_FLOW_REQ_ID = h8.REQ_ID (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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_8
, l2.CURRENCY_CODE source_11
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_29(
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) := 'EXP_REQ_RCPT_NO_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_29';
(p_msg => 'BEGIN of insert_sources_29'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 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'
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'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h4.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h3.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h3.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h5.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 7 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 7 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_CODE
AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.inventory_item_id = h1.inventory_item_id and h4.organization_id = h1.organization_id AND h5.inv_transaction_type_id = h1.transaction_type_id AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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 'ENTERED_AMOUNT'
WHEN 5 THEN 'CURRENCY_CODE'
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.ENTERED_AMOUNT)
WHEN 5 THEN TO_CHAR(l2.CURRENCY_CODE)
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_type_code = C_EVENT_TYPE_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_29'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_29');
END insert_sources_29;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: EXP_REQ_RCPT_NO_TP
-- 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.TRANSACTION_ID source_1
, h4.ITEM_CONCATENATED_SEGMENTS source_2
, h3.TRANSACTION_QUANTITY source_3
, h3.TRANSACTION_UOM source_4
, h5.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_CODE
and xet.event_status_code <> 'N' AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.INVENTORY_ITEM_ID = h1.INVENTORY_ITEM_ID AND h4.ORGANIZATION_ID = h1.ORGANIZATION_ID AND h5.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: EXP_REQ_RCPT_NO_TP
-- 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_6
, l2.ACCOUNTING_LINE_TYPE_CODE source_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.ENTERED_AMOUNT source_10
, l2.CURRENCY_CODE source_11
, l2.CURRENCY_CONVERSION_DATE source_12
, l2.CURRENCY_CONVERSION_RATE source_13
, l2.CURRENCY_CONVERSION_TYPE source_14
, l2.ACCOUNTED_AMOUNT source_15
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_type_code = C_EVENT_TYPE_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_30(
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) := 'EXP_REQ_RCPT_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_30';
(p_msg => 'BEGIN of insert_sources_30'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 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'
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'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h4.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h3.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h3.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h5.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 7 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 7 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_CODE
AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.inventory_item_id = h1.inventory_item_id and h4.organization_id = h1.organization_id AND h5.inv_transaction_type_id = h1.transaction_type_id AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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 'ENTERED_AMOUNT'
WHEN 5 THEN 'CURRENCY_CODE'
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.ENTERED_AMOUNT)
WHEN 5 THEN TO_CHAR(l2.CURRENCY_CODE)
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_type_code = C_EVENT_TYPE_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_30'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_30');
END insert_sources_30;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: EXP_REQ_RCPT_TP
-- 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.TRANSACTION_ID source_1
, h4.ITEM_CONCATENATED_SEGMENTS source_2
, h3.TRANSACTION_QUANTITY source_3
, h3.TRANSACTION_UOM source_4
, h5.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_CODE
and xet.event_status_code <> 'N' AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.INVENTORY_ITEM_ID = h1.INVENTORY_ITEM_ID AND h4.ORGANIZATION_ID = h1.ORGANIZATION_ID AND h5.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: EXP_REQ_RCPT_TP
-- 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_6
, l2.ACCOUNTING_LINE_TYPE_CODE source_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.ENTERED_AMOUNT source_10
, l2.CURRENCY_CODE source_11
, l2.CURRENCY_CONVERSION_DATE source_12
, l2.CURRENCY_CONVERSION_RATE source_13
, l2.CURRENCY_CONVERSION_TYPE source_14
, l2.ACCOUNTED_AMOUNT source_15
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_type_code = C_EVENT_TYPE_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_31(
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_NO_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_31';
(p_msg => 'BEGIN of insert_sources_31'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'CST_XLA_INV_ORG_PARAMS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_REF_V'
WHEN 9 THEN 'CST_XLA_INV_REF_V'
WHEN 10 THEN 'CST_XLA_INV_REF_V'
WHEN 11 THEN 'PSA_CST_XLA_UPG_V'
WHEN 12 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 13 THEN 'PO_REQ_DISTS_REF_V'
WHEN 14 THEN 'CST_XLA_INV_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'CST_XLA_INV_REF_V'
WHEN 18 THEN 'PO_REQ_DISTS_REF_V'
WHEN 19 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 8 THEN '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '201'
WHEN 13 THEN '201'
WHEN 14 THEN '707'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 8 THEN 'APPLIED_TO_APPL_ID'
WHEN 9 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 10 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 11 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 12 THEN 'REQ_ENCUMBRANCE_FLAG'
WHEN 13 THEN 'REQ_RESERVED_FLAG'
WHEN 14 THEN 'BUS_FLOW_REQ_DIST_TYPE'
WHEN 15 THEN 'BUS_FLOW_REQ_ENTITY_CODE'
WHEN 16 THEN 'BUS_FLOW_REQ_DIST_ID'
WHEN 17 THEN 'BUS_FLOW_REQ_ID'
WHEN 18 THEN 'REQ_BUDGET_ACCOUNT'
WHEN 19 THEN 'REQ_ENCUMBRANCE_TYPE_ID'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 8 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 9 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 10 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 11 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 12 THEN TO_CHAR(h8.REQ_ENCUMBRANCE_FLAG)
WHEN 13 THEN TO_CHAR(h7.REQ_RESERVED_FLAG)
WHEN 14 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_TYPE)
WHEN 15 THEN TO_CHAR(h4.BUS_FLOW_REQ_ENTITY_CODE)
WHEN 16 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_ID)
WHEN 17 THEN TO_CHAR(h4.BUS_FLOW_REQ_ID)
WHEN 18 THEN TO_CHAR(h7.REQ_BUDGET_ACCOUNT)
WHEN 19 THEN TO_CHAR(h8.REQ_ENCUMBRANCE_TYPE_ID)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_REQ_DISTS_REF_V h7
, PO_REQ_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id = h1.inventory_item_id and h5.organization_id = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.bus_flow_req_dist_id=h7.req_distribution_id (+) AND h4.bus_flow_req_id = h8.req_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_31'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_31');
END insert_sources_31;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_RCPT_RECIPIENT_RCPT_NO_TP
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.REQ_ENCUMBRANCE_FLAG source_33
, h7.REQ_RESERVED_FLAG source_34
, h4.BUS_FLOW_REQ_DIST_TYPE source_35
, h4.BUS_FLOW_REQ_ENTITY_CODE source_36
, h4.BUS_FLOW_REQ_DIST_ID source_37
, h4.BUS_FLOW_REQ_ID source_38
, h7.REQ_BUDGET_ACCOUNT source_39
, h8.REQ_ENCUMBRANCE_TYPE_ID source_40
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_REQ_DISTS_REF_V h7
, PO_REQ_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.BUS_FLOW_REQ_DIST_ID=h7.REQ_DISTRIBUTION_ID (+) AND h4.BUS_FLOW_REQ_ID = h8.REQ_ID (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_RCPT_RECIPIENT_RCPT_NO_TP
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_32(
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_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_32';
(p_msg => 'BEGIN of insert_sources_32'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'CST_XLA_INV_ORG_PARAMS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_REF_V'
WHEN 9 THEN 'CST_XLA_INV_REF_V'
WHEN 10 THEN 'CST_XLA_INV_REF_V'
WHEN 11 THEN 'PSA_CST_XLA_UPG_V'
WHEN 12 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 13 THEN 'PO_REQ_DISTS_REF_V'
WHEN 14 THEN 'CST_XLA_INV_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'CST_XLA_INV_REF_V'
WHEN 18 THEN 'PO_REQ_DISTS_REF_V'
WHEN 19 THEN 'PO_REQ_HEADERS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 8 THEN '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '201'
WHEN 13 THEN '201'
WHEN 14 THEN '707'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 8 THEN 'APPLIED_TO_APPL_ID'
WHEN 9 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 10 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 11 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 12 THEN 'REQ_ENCUMBRANCE_FLAG'
WHEN 13 THEN 'REQ_RESERVED_FLAG'
WHEN 14 THEN 'BUS_FLOW_REQ_DIST_TYPE'
WHEN 15 THEN 'BUS_FLOW_REQ_ENTITY_CODE'
WHEN 16 THEN 'BUS_FLOW_REQ_DIST_ID'
WHEN 17 THEN 'BUS_FLOW_REQ_ID'
WHEN 18 THEN 'REQ_BUDGET_ACCOUNT'
WHEN 19 THEN 'REQ_ENCUMBRANCE_TYPE_ID'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 8 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 9 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 10 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 11 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 12 THEN TO_CHAR(h8.REQ_ENCUMBRANCE_FLAG)
WHEN 13 THEN TO_CHAR(h7.REQ_RESERVED_FLAG)
WHEN 14 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_TYPE)
WHEN 15 THEN TO_CHAR(h4.BUS_FLOW_REQ_ENTITY_CODE)
WHEN 16 THEN TO_CHAR(h4.BUS_FLOW_REQ_DIST_ID)
WHEN 17 THEN TO_CHAR(h4.BUS_FLOW_REQ_ID)
WHEN 18 THEN TO_CHAR(h7.REQ_BUDGET_ACCOUNT)
WHEN 19 THEN TO_CHAR(h8.REQ_ENCUMBRANCE_TYPE_ID)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_REQ_DISTS_REF_V h7
, PO_REQ_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id = h1.inventory_item_id and h5.organization_id = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.bus_flow_req_dist_id=h7.req_distribution_id (+) AND h4.bus_flow_req_id = h8.req_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_32'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_32');
END insert_sources_32;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_RCPT_RECIPIENT_RCPT_TP
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.REQ_ENCUMBRANCE_FLAG source_33
, h7.REQ_RESERVED_FLAG source_34
, h4.BUS_FLOW_REQ_DIST_TYPE source_35
, h4.BUS_FLOW_REQ_ENTITY_CODE source_36
, h4.BUS_FLOW_REQ_DIST_ID source_37
, h4.BUS_FLOW_REQ_ID source_38
, h7.REQ_BUDGET_ACCOUNT source_39
, h8.REQ_ENCUMBRANCE_TYPE_ID source_40
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_REQ_DISTS_REF_V h7
, PO_REQ_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.BUS_FLOW_REQ_DIST_ID=h7.REQ_DISTRIBUTION_ID (+) AND h4.BUS_FLOW_REQ_ID = h8.REQ_ID (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_RCPT_RECIPIENT_RCPT_TP
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_33(
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_NO_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_33';
(p_msg => 'BEGIN of insert_sources_33'
,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 --hdr1
(
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 fvl9.meaning
WHEN 2 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(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_type_code = C_EVENT_TYPE_CODE
AND h1.event_id = xet.event_id
AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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 'ENTERED_AMOUNT'
WHEN 5 THEN 'CURRENCY_CODE'
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.ENTERED_AMOUNT)
WHEN 5 THEN TO_CHAR(l2.CURRENCY_CODE)
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_type_code = C_EVENT_TYPE_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_33'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_33');
END insert_sources_33;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_SHIP_SENDER_SHIP_NO_TP
-- 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_9
, fvl9.meaning source_9_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_CODE
and xet.event_status_code <> 'N' AND h1.event_id = xet.event_id
AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_SHIP_SENDER_SHIP_NO_TP
-- 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_6
, l2.ACCOUNTING_LINE_TYPE_CODE source_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.ENTERED_AMOUNT source_10
, l2.CURRENCY_CODE source_11
, l2.CURRENCY_CONVERSION_DATE source_12
, l2.CURRENCY_CONVERSION_RATE source_13
, l2.CURRENCY_CONVERSION_TYPE source_14
, l2.ACCOUNTED_AMOUNT source_15
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_type_code = C_EVENT_TYPE_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_34(
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_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_34';
(p_msg => 'BEGIN of insert_sources_34'
,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 --hdr1
(
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 fvl9.meaning
WHEN 2 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(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_type_code = C_EVENT_TYPE_CODE
AND h1.event_id = xet.event_id
AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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 'ENTERED_AMOUNT'
WHEN 5 THEN 'CURRENCY_CODE'
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.ENTERED_AMOUNT)
WHEN 5 THEN TO_CHAR(l2.CURRENCY_CODE)
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_type_code = C_EVENT_TYPE_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_34'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_34');
END insert_sources_34;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_SHIP_SENDER_SHIP_TP
-- 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_9
, fvl9.meaning source_9_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_CODE
and xet.event_status_code <> 'N' AND h1.event_id = xet.event_id
AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: FOB_SHIP_SENDER_SHIP_TP
-- 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_6
, l2.ACCOUNTING_LINE_TYPE_CODE source_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.ENTERED_AMOUNT source_10
, l2.CURRENCY_CODE source_11
, l2.CURRENCY_CONVERSION_DATE source_12
, l2.CURRENCY_CONVERSION_RATE source_13
, l2.CURRENCY_CONVERSION_TYPE source_14
, l2.ACCOUNTED_AMOUNT source_15
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_type_code = C_EVENT_TYPE_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_35(
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_ISSUE_NO_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_35';
(p_msg => 'BEGIN of insert_sources_35'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 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'
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'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h4.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h3.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h3.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h5.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 7 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 7 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_CODE
AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.inventory_item_id = h1.inventory_item_id and h4.organization_id = h1.organization_id AND h5.inv_transaction_type_id = h1.transaction_type_id AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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 'ENTERED_AMOUNT'
WHEN 5 THEN 'CURRENCY_CODE'
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.ENTERED_AMOUNT)
WHEN 5 THEN TO_CHAR(l2.CURRENCY_CODE)
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_type_code = C_EVENT_TYPE_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_35'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_35');
END insert_sources_35;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: INT_ORDER_ISSUE_NO_TP
-- 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.TRANSACTION_ID source_1
, h4.ITEM_CONCATENATED_SEGMENTS source_2
, h3.TRANSACTION_QUANTITY source_3
, h3.TRANSACTION_UOM source_4
, h5.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_CODE
and xet.event_status_code <> 'N' AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.INVENTORY_ITEM_ID = h1.INVENTORY_ITEM_ID AND h4.ORGANIZATION_ID = h1.ORGANIZATION_ID AND h5.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: INT_ORDER_ISSUE_NO_TP
-- 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_6
, l2.ACCOUNTING_LINE_TYPE_CODE source_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.ENTERED_AMOUNT source_10
, l2.CURRENCY_CODE source_11
, l2.CURRENCY_CONVERSION_DATE source_12
, l2.CURRENCY_CONVERSION_RATE source_13
, l2.CURRENCY_CONVERSION_TYPE source_14
, l2.ACCOUNTED_AMOUNT source_15
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_type_code = C_EVENT_TYPE_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_36(
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_ISSUE_TP';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_36';
(p_msg => 'BEGIN of insert_sources_36'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 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'
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'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h4.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h3.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h3.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h5.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 7 THEN fvl42.meaning
ELSE null
END source_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 7 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_CODE
AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.inventory_item_id = h1.inventory_item_id and h4.organization_id = h1.organization_id AND h5.inv_transaction_type_id = h1.transaction_type_id AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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 'ENTERED_AMOUNT'
WHEN 5 THEN 'CURRENCY_CODE'
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.ENTERED_AMOUNT)
WHEN 5 THEN TO_CHAR(l2.CURRENCY_CODE)
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_type_code = C_EVENT_TYPE_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_36'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_36');
END insert_sources_36;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: INT_ORDER_ISSUE_TP
-- 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.TRANSACTION_ID source_1
, h4.ITEM_CONCATENATED_SEGMENTS source_2
, h3.TRANSACTION_QUANTITY source_3
, h3.TRANSACTION_UOM source_4
, h5.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_meaning
FROM xla_events_gt xet
, CST_XLA_INV_HEADERS_V h1
, CST_XLA_INV_REF_V h3
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h4
, CST_XLA_INV_TXN_TYPES_REF_V h5
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_CODE
and xet.event_status_code <> 'N' AND h1.event_id = xet.event_id
AND h3.ref_transaction_id = h1.transaction_id AND h4.INVENTORY_ITEM_ID = h1.INVENTORY_ITEM_ID AND h4.ORGANIZATION_ID = h1.ORGANIZATION_ID AND h5.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: INT_ORDER_ISSUE_TP
-- 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_6
, l2.ACCOUNTING_LINE_TYPE_CODE source_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.ENTERED_AMOUNT source_10
, l2.CURRENCY_CODE source_11
, l2.CURRENCY_CONVERSION_DATE source_12
, l2.CURRENCY_CONVERSION_RATE source_13
, l2.CURRENCY_CONVERSION_TYPE source_14
, l2.ACCOUNTED_AMOUNT source_15
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_type_code = C_EVENT_TYPE_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_37(
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) := 'LOG_PO_DEL_ADJ';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_37';
(p_msg => 'BEGIN of insert_sources_37'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'PO_HEADERS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_ORG_PARAMS_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 'CST_XLA_INV_REF_V'
WHEN 14 THEN 'PO_DISTS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'PSA_CST_XLA_UPG_V'
WHEN 18 THEN 'PO_HEADERS_REF_V'
WHEN 19 THEN 'PO_DISTS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 9 THEN 'APPLIED_TO_APPL_ID'
WHEN 10 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 11 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 12 THEN 'TXN_PO_DISTRIBUTION_ID'
WHEN 13 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 14 THEN 'PO_BUDGET_ACCOUNT'
WHEN 15 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 16 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 17 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 18 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 19 THEN 'RESERVED_FLAG'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 9 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 10 THEN TO_CHAR(h4.APPLIED_TO_DIST_LINK_TYPE)
WHEN 11 THEN TO_CHAR(h4.APPLIED_TO_ENTITY_CODE)
WHEN 12 THEN TO_CHAR(h4.TXN_PO_DISTRIBUTION_ID)
WHEN 13 THEN TO_CHAR(h4.APPLIED_TO_PO_DOC_ID)
WHEN 14 THEN TO_CHAR(h7.PO_BUDGET_ACCOUNT)
WHEN 15 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 16 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 17 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 18 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 19 THEN TO_CHAR(h7.RESERVED_FLAG)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id (+) = h1.inventory_item_id and h5.organization_id (+) = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.txn_po_header_id = h7.po_header_id (+) and h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_37'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_37');
END insert_sources_37;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: LOG_PO_DEL_ADJ
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h8.PURCH_ENCUMBRANCE_FLAG source_16
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.APPLIED_TO_DIST_LINK_TYPE source_20
, h4.APPLIED_TO_ENTITY_CODE source_21
, h4.TXN_PO_DISTRIBUTION_ID source_22
, h4.APPLIED_TO_PO_DOC_ID source_23
, h7.PO_BUDGET_ACCOUNT source_24
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h7.RESERVED_FLAG source_31
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID (+) = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.txn_po_header_id = h7.po_header_id (+) AND h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: LOG_PO_DEL_ADJ
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_38(
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) := 'LOG_PO_DEL_INV';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_38';
(p_msg => 'BEGIN of insert_sources_38'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'PO_HEADERS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_ORG_PARAMS_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 'CST_XLA_INV_REF_V'
WHEN 14 THEN 'PO_DISTS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'PSA_CST_XLA_UPG_V'
WHEN 18 THEN 'PO_HEADERS_REF_V'
WHEN 19 THEN 'PO_DISTS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 9 THEN 'APPLIED_TO_APPL_ID'
WHEN 10 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 11 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 12 THEN 'TXN_PO_DISTRIBUTION_ID'
WHEN 13 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 14 THEN 'PO_BUDGET_ACCOUNT'
WHEN 15 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 16 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 17 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 18 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 19 THEN 'RESERVED_FLAG'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 9 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 10 THEN TO_CHAR(h4.APPLIED_TO_DIST_LINK_TYPE)
WHEN 11 THEN TO_CHAR(h4.APPLIED_TO_ENTITY_CODE)
WHEN 12 THEN TO_CHAR(h4.TXN_PO_DISTRIBUTION_ID)
WHEN 13 THEN TO_CHAR(h4.APPLIED_TO_PO_DOC_ID)
WHEN 14 THEN TO_CHAR(h7.PO_BUDGET_ACCOUNT)
WHEN 15 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 16 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 17 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 18 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 19 THEN TO_CHAR(h7.RESERVED_FLAG)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id (+) = h1.inventory_item_id and h5.organization_id (+) = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.txn_po_header_id = h7.po_header_id (+) and h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_38'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_38');
END insert_sources_38;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: LOG_PO_DEL_INV
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h8.PURCH_ENCUMBRANCE_FLAG source_16
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.APPLIED_TO_DIST_LINK_TYPE source_20
, h4.APPLIED_TO_ENTITY_CODE source_21
, h4.TXN_PO_DISTRIBUTION_ID source_22
, h4.APPLIED_TO_PO_DOC_ID source_23
, h7.PO_BUDGET_ACCOUNT source_24
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h7.RESERVED_FLAG source_31
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID (+) = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.txn_po_header_id = h7.po_header_id (+) AND h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: LOG_PO_DEL_INV
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_39(
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) := 'LOG_RET_RI_INV';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_39';
(p_msg => 'BEGIN of insert_sources_39'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'PO_HEADERS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_ORG_PARAMS_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 'CST_XLA_INV_REF_V'
WHEN 14 THEN 'PO_DISTS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'PSA_CST_XLA_UPG_V'
WHEN 18 THEN 'PO_HEADERS_REF_V'
WHEN 19 THEN 'PO_DISTS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 9 THEN 'APPLIED_TO_APPL_ID'
WHEN 10 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 11 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 12 THEN 'TXN_PO_DISTRIBUTION_ID'
WHEN 13 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 14 THEN 'PO_BUDGET_ACCOUNT'
WHEN 15 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 16 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 17 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 18 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 19 THEN 'RESERVED_FLAG'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 9 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 10 THEN TO_CHAR(h4.APPLIED_TO_DIST_LINK_TYPE)
WHEN 11 THEN TO_CHAR(h4.APPLIED_TO_ENTITY_CODE)
WHEN 12 THEN TO_CHAR(h4.TXN_PO_DISTRIBUTION_ID)
WHEN 13 THEN TO_CHAR(h4.APPLIED_TO_PO_DOC_ID)
WHEN 14 THEN TO_CHAR(h7.PO_BUDGET_ACCOUNT)
WHEN 15 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 16 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 17 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 18 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 19 THEN TO_CHAR(h7.RESERVED_FLAG)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id (+) = h1.inventory_item_id and h5.organization_id (+) = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.txn_po_header_id = h7.po_header_id (+) and h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_39'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_39');
END insert_sources_39;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: LOG_RET_RI_INV
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h8.PURCH_ENCUMBRANCE_FLAG source_16
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.APPLIED_TO_DIST_LINK_TYPE source_20
, h4.APPLIED_TO_ENTITY_CODE source_21
, h4.TXN_PO_DISTRIBUTION_ID source_22
, h4.APPLIED_TO_PO_DOC_ID source_23
, h7.PO_BUDGET_ACCOUNT source_24
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h7.RESERVED_FLAG source_31
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID (+) = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.txn_po_header_id = h7.po_header_id (+) AND h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: LOG_RET_RI_INV
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_40(
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_40';
(p_msg => 'BEGIN of insert_sources_40'
,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_RCV_HEADERS_V'
WHEN 2 THEN 'CST_XLA_RCV_HEADERS_V'
WHEN 3 THEN 'PO_HEADERS_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_REF_V'
WHEN 8 THEN 'PO_DISTS_REF_V'
WHEN 9 THEN 'CST_XLA_RCV_REF_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 '707'
WHEN 2 THEN '707'
WHEN 3 THEN '201'
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 '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 'DISTRIBUTION_TYPE'
WHEN 2 THEN 'CURRENCY_CODE'
WHEN 3 THEN 'PURCH_ENCUMBRANCE_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 'APPLIED_TO_PO_DOC_ID'
WHEN 8 THEN 'PO_BUDGET_ACCOUNT'
WHEN 9 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
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(h1.DISTRIBUTION_TYPE)
WHEN 2 THEN TO_CHAR(h1.CURRENCY_CODE)
WHEN 3 THEN TO_CHAR(h5.PURCH_ENCUMBRANCE_FLAG)
WHEN 4 THEN TO_CHAR(h3.APPLIED_TO_APPL_ID)
WHEN 5 THEN TO_CHAR(h3.APPLIED_TO_DIST_LINK_TYPE)
WHEN 6 THEN TO_CHAR(h3.APPLIED_TO_ENTITY_CODE)
WHEN 7 THEN TO_CHAR(h3.APPLIED_TO_PO_DOC_ID)
WHEN 8 THEN TO_CHAR(h4.PO_BUDGET_ACCOUNT)
WHEN 9 THEN TO_CHAR(h3.ENCUM_REVERSAL_AMOUNT_ENTERED)
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 1 THEN fvl9.meaning
WHEN 14 THEN fvl42.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 fvl9
, fnd_lookup_values fvl42
,(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 fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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_40'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_40');
END insert_sources_40;
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
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h1.CURRENCY_CODE source_11
, h5.PURCH_ENCUMBRANCE_FLAG source_16
, h3.APPLIED_TO_APPL_ID source_19
, h3.APPLIED_TO_DIST_LINK_TYPE source_20
, h3.APPLIED_TO_ENTITY_CODE source_21
, h3.APPLIED_TO_PO_DOC_ID source_23
, h4.PO_BUDGET_ACCOUNT source_24
, h3.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h3.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h5.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h3.PO_DISTRIBUTION_ID source_30
, h6.CST_PEA_ENC_UPG_OPTION source_32
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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 fvl9
, fnd_lookup_values fvl42
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 fvl9.lookup_type(+) = 'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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_8
, l2.RCV_ACCOUNTING_LINE_TYPE source_29
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_41(
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) := 'PO_DEL_ADJ';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_41';
(p_msg => 'BEGIN of insert_sources_41'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'PO_HEADERS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_ORG_PARAMS_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 'CST_XLA_INV_REF_V'
WHEN 14 THEN 'PO_DISTS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'PSA_CST_XLA_UPG_V'
WHEN 18 THEN 'PO_HEADERS_REF_V'
WHEN 19 THEN 'PO_DISTS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 9 THEN 'APPLIED_TO_APPL_ID'
WHEN 10 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 11 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 12 THEN 'TXN_PO_DISTRIBUTION_ID'
WHEN 13 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 14 THEN 'PO_BUDGET_ACCOUNT'
WHEN 15 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 16 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 17 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 18 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 19 THEN 'RESERVED_FLAG'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 9 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 10 THEN TO_CHAR(h4.APPLIED_TO_DIST_LINK_TYPE)
WHEN 11 THEN TO_CHAR(h4.APPLIED_TO_ENTITY_CODE)
WHEN 12 THEN TO_CHAR(h4.TXN_PO_DISTRIBUTION_ID)
WHEN 13 THEN TO_CHAR(h4.APPLIED_TO_PO_DOC_ID)
WHEN 14 THEN TO_CHAR(h7.PO_BUDGET_ACCOUNT)
WHEN 15 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 16 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 17 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 18 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 19 THEN TO_CHAR(h7.RESERVED_FLAG)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id (+) = h1.inventory_item_id and h5.organization_id (+) = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.txn_po_header_id = h7.po_header_id (+) and h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_41'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_41');
END insert_sources_41;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: PO_DEL_ADJ
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h8.PURCH_ENCUMBRANCE_FLAG source_16
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.APPLIED_TO_DIST_LINK_TYPE source_20
, h4.APPLIED_TO_ENTITY_CODE source_21
, h4.TXN_PO_DISTRIBUTION_ID source_22
, h4.APPLIED_TO_PO_DOC_ID source_23
, h7.PO_BUDGET_ACCOUNT source_24
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h7.RESERVED_FLAG source_31
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID (+) = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.txn_po_header_id = h7.po_header_id (+) AND h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: PO_DEL_ADJ
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_42(
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) := 'PO_DEL_INV';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_42';
(p_msg => 'BEGIN of insert_sources_42'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'PO_HEADERS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_ORG_PARAMS_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 'CST_XLA_INV_REF_V'
WHEN 14 THEN 'PO_DISTS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'PSA_CST_XLA_UPG_V'
WHEN 18 THEN 'PO_HEADERS_REF_V'
WHEN 19 THEN 'PO_DISTS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 9 THEN 'APPLIED_TO_APPL_ID'
WHEN 10 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 11 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 12 THEN 'TXN_PO_DISTRIBUTION_ID'
WHEN 13 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 14 THEN 'PO_BUDGET_ACCOUNT'
WHEN 15 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 16 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 17 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 18 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 19 THEN 'RESERVED_FLAG'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 9 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 10 THEN TO_CHAR(h4.APPLIED_TO_DIST_LINK_TYPE)
WHEN 11 THEN TO_CHAR(h4.APPLIED_TO_ENTITY_CODE)
WHEN 12 THEN TO_CHAR(h4.TXN_PO_DISTRIBUTION_ID)
WHEN 13 THEN TO_CHAR(h4.APPLIED_TO_PO_DOC_ID)
WHEN 14 THEN TO_CHAR(h7.PO_BUDGET_ACCOUNT)
WHEN 15 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 16 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 17 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 18 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 19 THEN TO_CHAR(h7.RESERVED_FLAG)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id (+) = h1.inventory_item_id and h5.organization_id (+) = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.txn_po_header_id = h7.po_header_id (+) and h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_42'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_42');
END insert_sources_42;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: PO_DEL_INV
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h8.PURCH_ENCUMBRANCE_FLAG source_16
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.APPLIED_TO_DIST_LINK_TYPE source_20
, h4.APPLIED_TO_ENTITY_CODE source_21
, h4.TXN_PO_DISTRIBUTION_ID source_22
, h4.APPLIED_TO_PO_DOC_ID source_23
, h7.PO_BUDGET_ACCOUNT source_24
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h7.RESERVED_FLAG source_31
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID (+) = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.txn_po_header_id = h7.po_header_id (+) AND h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: PO_DEL_INV
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_43(
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) := 'RET_RI_INV';
l_log_module := C_DEFAULT_MODULE||'.insert_sources_43';
(p_msg => 'BEGIN of insert_sources_43'
,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 --hdr1
(
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_SYSTEM_ITEMS_REF_V'
WHEN 3 THEN 'CST_XLA_INV_REF_V'
WHEN 4 THEN 'CST_XLA_INV_REF_V'
WHEN 5 THEN 'CST_XLA_INV_TXN_TYPES_REF_V'
WHEN 6 THEN 'CST_XLA_INV_HEADERS_V'
WHEN 7 THEN 'PO_HEADERS_REF_V'
WHEN 8 THEN 'CST_XLA_INV_ORG_PARAMS_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 'CST_XLA_INV_REF_V'
WHEN 14 THEN 'PO_DISTS_REF_V'
WHEN 15 THEN 'CST_XLA_INV_REF_V'
WHEN 16 THEN 'CST_XLA_INV_REF_V'
WHEN 17 THEN 'PSA_CST_XLA_UPG_V'
WHEN 18 THEN 'PO_HEADERS_REF_V'
WHEN 19 THEN 'PO_DISTS_REF_V'
WHEN 20 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'
WHEN 16 THEN 'HEADER'
WHEN 17 THEN 'HEADER'
WHEN 18 THEN 'HEADER'
WHEN 19 THEN 'HEADER'
WHEN 20 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 '707'
WHEN 9 THEN '707'
WHEN 10 THEN '707'
WHEN 11 THEN '707'
WHEN 12 THEN '707'
WHEN 13 THEN '707'
WHEN 14 THEN '201'
WHEN 15 THEN '707'
WHEN 16 THEN '707'
WHEN 17 THEN '707'
WHEN 18 THEN '201'
WHEN 19 THEN '201'
WHEN 20 THEN '707'
ELSE null
END source_application_id
, 'S' source_type_code
, CASE r
WHEN 1 THEN 'TRANSACTION_ID'
WHEN 2 THEN 'ITEM_CONCATENATED_SEGMENTS'
WHEN 3 THEN 'TRANSACTION_QUANTITY'
WHEN 4 THEN 'TRANSACTION_UOM'
WHEN 5 THEN 'INV_TXN_TYPE_DESCRIPTION'
WHEN 6 THEN 'DISTRIBUTION_TYPE'
WHEN 7 THEN 'PURCH_ENCUMBRANCE_FLAG'
WHEN 8 THEN 'ENCUMBRANCE_REVERSAL_FLAG'
WHEN 9 THEN 'APPLIED_TO_APPL_ID'
WHEN 10 THEN 'APPLIED_TO_DIST_LINK_TYPE'
WHEN 11 THEN 'APPLIED_TO_ENTITY_CODE'
WHEN 12 THEN 'TXN_PO_DISTRIBUTION_ID'
WHEN 13 THEN 'APPLIED_TO_PO_DOC_ID'
WHEN 14 THEN 'PO_BUDGET_ACCOUNT'
WHEN 15 THEN 'ENCUM_REVERSAL_AMOUNT_ENTERED'
WHEN 16 THEN 'ENCUMBRANCE_REVERSAL_AMOUNT'
WHEN 17 THEN 'CST_ENCUM_UPG_OPTION'
WHEN 18 THEN 'PURCH_ENCUMBRANCE_TYPE_ID'
WHEN 19 THEN 'RESERVED_FLAG'
WHEN 20 THEN 'TRANSFER_TO_GL_INDICATOR'
ELSE null
END source_code
, CASE r
WHEN 1 THEN TO_CHAR(h1.TRANSACTION_ID)
WHEN 2 THEN TO_CHAR(h5.ITEM_CONCATENATED_SEGMENTS)
WHEN 3 THEN TO_CHAR(h4.TRANSACTION_QUANTITY)
WHEN 4 THEN TO_CHAR(h4.TRANSACTION_UOM)
WHEN 5 THEN TO_CHAR(h6.INV_TXN_TYPE_DESCRIPTION)
WHEN 6 THEN TO_CHAR(h1.DISTRIBUTION_TYPE)
WHEN 7 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_FLAG)
WHEN 8 THEN TO_CHAR(h3.ENCUMBRANCE_REVERSAL_FLAG)
WHEN 9 THEN TO_CHAR(h4.APPLIED_TO_APPL_ID)
WHEN 10 THEN TO_CHAR(h4.APPLIED_TO_DIST_LINK_TYPE)
WHEN 11 THEN TO_CHAR(h4.APPLIED_TO_ENTITY_CODE)
WHEN 12 THEN TO_CHAR(h4.TXN_PO_DISTRIBUTION_ID)
WHEN 13 THEN TO_CHAR(h4.APPLIED_TO_PO_DOC_ID)
WHEN 14 THEN TO_CHAR(h7.PO_BUDGET_ACCOUNT)
WHEN 15 THEN TO_CHAR(h4.ENCUM_REVERSAL_AMOUNT_ENTERED)
WHEN 16 THEN TO_CHAR(h4.ENCUMBRANCE_REVERSAL_AMOUNT)
WHEN 17 THEN TO_CHAR(h9.CST_ENCUM_UPG_OPTION)
WHEN 18 THEN TO_CHAR(h8.PURCH_ENCUMBRANCE_TYPE_ID)
WHEN 19 THEN TO_CHAR(h7.RESERVED_FLAG)
WHEN 20 THEN TO_CHAR(h1.TRANSFER_TO_GL_INDICATOR)
ELSE null
END source_value
, CASE r
WHEN 6 THEN fvl9.meaning
WHEN 20 THEN fvl42.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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
,(select rownum r from all_objects where rownum <= 20 and owner = p_apps_owner)
WHERE xet.event_date between p_pad_start_date AND p_pad_end_date
AND xet.event_type_code = C_EVENT_TYPE_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 h5.inventory_item_id (+) = h1.inventory_item_id and h5.organization_id (+) = h1.organization_id AND h6.inv_transaction_type_id = h1.transaction_type_id AND h4.txn_po_header_id = h7.po_header_id (+) and h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.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 --line1
(
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_type_code = C_EVENT_TYPE_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_43'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_00707_AAD_S_000020_BC_PKG.insert_sources_43');
END insert_sources_43;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: RET_RI_INV
-- 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
, h1.TRANSACTION_ID source_1
, h5.ITEM_CONCATENATED_SEGMENTS source_2
, h4.TRANSACTION_QUANTITY source_3
, h4.TRANSACTION_UOM source_4
, h6.INV_TXN_TYPE_DESCRIPTION source_5
, h1.DISTRIBUTION_TYPE source_9
, fvl9.meaning source_9_meaning
, h8.PURCH_ENCUMBRANCE_FLAG source_16
, h3.ENCUMBRANCE_REVERSAL_FLAG source_17
, h4.APPLIED_TO_APPL_ID source_19
, h4.APPLIED_TO_DIST_LINK_TYPE source_20
, h4.APPLIED_TO_ENTITY_CODE source_21
, h4.TXN_PO_DISTRIBUTION_ID source_22
, h4.APPLIED_TO_PO_DOC_ID source_23
, h7.PO_BUDGET_ACCOUNT source_24
, h4.ENCUM_REVERSAL_AMOUNT_ENTERED source_25
, h4.ENCUMBRANCE_REVERSAL_AMOUNT source_26
, h9.CST_ENCUM_UPG_OPTION source_27
, h8.PURCH_ENCUMBRANCE_TYPE_ID source_28
, h7.RESERVED_FLAG source_31
, h1.TRANSFER_TO_GL_INDICATOR source_42
, fvl42.meaning source_42_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
, CST_XLA_INV_SYSTEM_ITEMS_REF_V h5
, CST_XLA_INV_TXN_TYPES_REF_V h6
, PO_DISTS_REF_V h7
, PO_HEADERS_REF_V h8
, PSA_CST_XLA_UPG_V h9
, fnd_lookup_values fvl9
, fnd_lookup_values fvl42
WHERE xet.event_date between p_pad_start_date and p_pad_end_date
and xet.event_type_code = C_EVENT_TYPE_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 h5.INVENTORY_ITEM_ID (+) = h1.INVENTORY_ITEM_ID AND h5.ORGANIZATION_ID (+) = h1.ORGANIZATION_ID AND h6.INV_TRANSACTION_TYPE_ID = h1.TRANSACTION_TYPE_ID AND h4.txn_po_header_id = h7.po_header_id (+) AND h4.txn_po_distribution_id = h7.po_distribution_id (+) AND h4.txn_po_header_id = h8.po_header_id (+) AND h4.rcv_transaction_id = h9.transaction_id (+) AND fvl9.lookup_type(+) =
'CST_DISTRIBUTION_TYPE'
AND fvl9.lookup_code(+) = h1.DISTRIBUTION_TYPE
AND fvl9.view_application_id(+) = 700
AND fvl9.language(+) = USERENV('LANG')
AND fvl42.lookup_type(+) = 'YES_NO'
AND fvl42.lookup_code(+) = h1.TRANSFER_TO_GL_INDICATOR
AND fvl42.view_application_id(+) = 0
AND fvl42.language(+) = USERENV('LANG')
ORDER BY event_id
;
SELECT /*+ leading(xet) cardinality(xet,1) */
-- Event Type Code: RET_RI_INV
-- 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_7
, l2.DISTRIBUTION_IDENTIFIER source_8
, l2.CURRENCY_CODE source_11
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_type_code = C_EVENT_TYPE_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_27(
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_28(
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_29(
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_30(
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_31(
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_32(
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_33(
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_34(
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_35(
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_36(
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_37(
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_38(
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_39(
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_40(
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_41(
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_42(
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_43(
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
);