[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT to_char(SYSDATE, 'MM/DD/YYYY HH:MM:SS')
INTO l_time
FROM dual;
select chart_of_accounts_id,currency_code into p_coa_id , p_currency
from gl_ledgers_public_v
where ledger_id = p_ledger_id;
SELECT fcr.concurrent_program_id,
fcr.parent_request_id,
fr.responsibility_name,
fa.description,
fcr.requested_start_date,
TO_CHAR(fcr.RESUBMIT_INTERVAL)||' '||fcr.RESUBMIT_INTERVAL_UNIT_CODE,
fcr.actual_start_date,
fcr.printer,
fcr.number_of_copies,
fcr.save_output_flag
FROM FND_CONCURRENT_REQUESTS FCR,
FND_APPLICATION_VL FA,
FND_RESPONSIBILITY_VL FR
WHERE fcr.responsibility_id = fr.responsibility_id
AND fcr.program_application_id = fa.application_id
and fcr.request_id = p_request_id;
SELECT parent_request_id,
request_type, description
FROM fnd_concurrent_requests
WHERE request_id = cp_parent_id;
select substr(legal_entity_name,1,60)
into v_organization_name
from GL_LEDGER_LE_V
where ledger_id = v_set_of_books_id
and rownum = 1
order by legal_entity_name;
SELECT a.period_name
INTO tfirst_period
FROM gl_period_statuses a, gl_period_statuses b
WHERE a.application_id = 101
AND b.application_id = 101
AND a.ledger_id = tset_of_books_id
AND b.ledger_id = tset_of_books_id
AND a.period_type = b.period_type
AND a.period_year = b.period_year
AND b.period_name = tperiod_name
AND a.period_num =
(SELECT min(c.period_num)
FROM gl_period_statuses c
WHERE c.application_id = 101
AND c.ledger_id = tset_of_books_id
AND c.period_year = a.period_year
AND c.period_type = a.period_type
GROUP BY c.period_year);
SELECT flex_value, summary_flag, flex_value_set_id, parent_flex_value
FROM fnd_flex_value_children_v
WHERE (flex_value_set_id = p_flex_value_set_id) AND (parent_flex_value = p_parent_flex_value)
ORDER BY flex_value;
SELECT flexvalue.flex_value, flexvalue.summary_flag, fndidflex.flex_value_set_id
FROM fnd_segment_attribute_values fndseg,
fnd_id_flex_segments_vl fndidflex,
fnd_flex_values_vl flexvalue
WHERE fndseg.id_flex_num = p_coa_id
AND fndseg.segment_attribute_type = 'GL_ACCOUNT'
AND fndseg.id_flex_code = 'GL#'
AND fndseg.attribute_value = 'Y'
AND fndseg.application_column_name = fndidflex.application_column_name
AND fndidflex.id_flex_num = p_coa_id
AND fndidflex.id_flex_code = 'GL#'
AND fndidflex.flex_value_set_id = flexvalue.flex_value_set_id
AND flexvalue.enabled_flag = 'Y'
and flexvalue.flex_value = p_flex_value;
SELECT period_set_name
INTO vl_period_set_name
FROM Gl_Sets_Of_Books
WHERE set_of_books_id = sob_id;
SELECT period_year
INTO period_year
FROM Gl_Periods
WHERE period_set_name = vl_period_set_name
AND period_name = period_from;
SELECT start_date
INTO period_start_date
FROM Gl_Period_Statuses
WHERE ledger_id = sob_id
AND application_id = 101
AND period_year = period_year
AND period_name = period_from
AND adjustment_period_flag = 'N';
SELECT end_date
INTO period_end_date
FROM Gl_Period_Statuses
WHERE ledger_id = sob_id
AND application_id = 101
AND period_year = period_year
AND period_name = period_to
AND adjustment_period_flag = 'N';
SELECT papf.national_identifier
INTO l_tin
FROM per_all_people_f papf
WHERE person_id = p_employee_id
AND ROWNUM < 2;
SELECT accrued_code_combination_id
INTO l_default_accrual_acct_id
FROM po_system_parameters;
*Procedure to delete orphan BC events.
*Called from psa_ap_bc_pvt.delete_events.
*Returns 'S' for success and 'E' for error.
*
*-----------------------------------------------------------*/
PROCEDURE delete_fv_bc_orphan
( p_ledger_id IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_status OUT NOCOPY VARCHAR2
) IS
l_module VARCHAR2(200) := g_module_name||'delete_fv_bc_orphan.';
SELECT xte.transaction_number, xla.application_id, xla.event_id,
xla.event_type_code,
xla.event_date,
xla.event_status_code,
xla.process_status_code,
xte.entity_id,
xte.legal_entity_id,
xte.entity_code,
xte.source_id_int_1,
xte.source_id_int_2,
xte.source_id_int_3,
xte.source_id_int_4,
xte.source_id_char_1
FROM xla_events xla,
xla_transaction_entities xte
WHERE NVL(xla.budgetary_control_flag, 'N') ='Y'
AND xla.application_id = 8901
AND xla.event_date BETWEEN p_start_date AND p_end_date
AND xla.event_status_code in ('U','I')
AND xla.process_status_code <> 'P'
AND xla.entity_id = xte.entity_id
AND xla.application_id = xte.application_id
AND xte.ledger_id = p_ledger_id;
DELETE FROM XLA_EVENTS_INT_GT;
'# of rows deleted from xla_events_int_gt: '|| SQL%ROWCOUNT );
INSERT INTO XLA_EVENTS_INT_GT
VALUES l_events_tab(i) ;
fnd_file.put_line(fnd_file.log,' # of rows inserted into xla_events_int_gt table: ' || l_event_count);
fnd_file.put_line(fnd_file.log,'Calling XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENT ');
XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 8901);
fnd_file.put_line(fnd_file.log,'The following Federal BC unprocessed/Error events have been deleted');
UPDATE fv_be_trx_dtls
SET event_id = NULL
WHERE event_id = l_events_tab(i).event_id;
'# distributions in fv_be_trx_dtls that have been updated to NULL: '||SQL%ROWCOUNT);
fnd_file.put_line(fnd_file.log,'**** No Federal events found to delete ****');
END delete_fv_bc_orphan;
DELETE FROM XLA_AE_HEADERS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '1 XLA_AE_HEADERS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_AE_LINES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '2 XLA_AE_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_VALIDATION_HDRS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '3 XLA_VALIDATION_HDRS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_VALIDATION_LINES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '4 XLA_VALIDATION_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_CTRL_CTRBS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '5 XLA_BAL_CTRL_CTRBS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_PERIOD_STATS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '6 XLA_BAL_PERIOD_STATS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_RECREATE_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '7 XLA_BAL_RECREATE_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_ANACRI_LINES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '8 XLA_BAL_ANACRI_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_ANACRI_CTRBS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '9 XLA_BAL_ANACRI_CTRBS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_SYNCHRONIZE_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '10 XLA_BAL_SYNCHRONIZE_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_STATUSES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '11 XLA_BAL_STATUSES_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_CTRL_LINES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '12 XLA_BAL_CTRL_LINES_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVENTS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '13 XLA_EVENTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVT_CLASS_SOURCES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '14 XLA_EVT_CLASS_SOURCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVT_CLASS_ORDERS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '15 XLA_EVT_CLASS_ORDERS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TAB_ERRORS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '16 XLA_TAB_ERRORS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_SEQ_JE_HEADERS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '17 XLA_SEQ_JE_HEADERS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TAB_NEW_CCIDS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '18 XLA_TAB_NEW_CCIDS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EXTRACT_OBJECTS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '19 XLA_EXTRACT_OBJECTS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_REFERENCE_OBJECTS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '20 XLA_REFERENCE_OBJECTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TRANSACTION_ACCTS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '21 XLA_TRANSACTION_ACCTS_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_UPG_LINE_CRITERIA_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '22 XLA_UPG_LINE_CRITERIA_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TRIAL_BALANCES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '23 XLA_TRIAL_BALANCES_GT : Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_ACCT_PROG_EVENTS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '24 XLA_ACCT_PROG_EVENTS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_ACCT_PROG_DOCS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '25 XLA_ACCT_PROG_DOCS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_MERGE_SEG_MAPS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '26 XLA_MERGE_SEG_MAPS_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_EVENTS_INT_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '27 XLA_EVENTS_INT_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_REPORT_BALANCES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '28 XLA_REPORT_BALANCES_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_TB_BALANCES_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '29 XLA_TB_BALANCES_GT: Deleted Row count :'||SQL%ROWCOUNT);
DELETE FROM XLA_BAL_AC_CTRBS_GT;
log_mesg(fnd_log.LEVEL_STATEMENT, l_module, '30 XLA_BAL_AC_CTRBS_GT: Deleted Row count :'||SQL%ROWCOUNT);