[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Duplicate'
FROM GL_JE_HEADERS jeh
WHERE jeh.je_batch_id = batch_id
AND jeh.name = header_name
AND ( row_id is null
OR jeh.rowid <> row_id);
SELECT gl_je_headers_s.NEXTVAL
FROM dual;
PROCEDURE delete_headers(batch_id NUMBER) IS
dummy NUMBER;
DELETE gl_je_lines
WHERE je_header_id IN (SELECT je_header_id
FROM gl_je_headers
WHERE je_batch_id = batch_id);
dummy := gl_je_segment_values_pkg.delete_batch_segment_values(batch_id);
DELETE gl_je_lines_recon
WHERE je_header_id IN (SELECT je_header_id
FROM gl_je_headers
WHERE je_batch_id = batch_id);
UPDATE gl_je_headers
SET reversed_je_header_id = null,
accrual_rev_je_header_id = decode(accrual_rev_status,
'R', accrual_rev_je_header_id,
null)
WHERE je_header_id IN
(SELECT accrual_rev_je_header_id
FROM gl_je_headers
WHERE je_batch_id = batch_id
AND accrual_rev_status = 'R');
UPDATE gl_je_headers
SET accrual_rev_status = null,
accrual_rev_je_header_id = null,
accrual_rev_flag = 'Y'
WHERE je_header_id IN
(SELECT reversed_je_header_id
FROM gl_je_headers
WHERE je_batch_id = batch_id
AND reversed_je_header_id IS NOT NULL );
GL_IMPORT_REFERENCES_PKG.delete_batch ( batch_id);
DELETE gl_je_headers
WHERE je_batch_id = batch_id;
fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.delete_headers');
END delete_headers;
UPDATE gl_je_lines
SET effective_date = new_effective_date
WHERE je_header_id IN (SELECT je_header_id
FROM gl_je_headers
WHERE je_batch_id = batch_id);
UPDATE gl_je_headers jeh
SET default_effective_date = new_effective_date,
currency_conversion_date =
(select decode(jeh.currency_code,
'STAT', new_effective_date,
lgr.currency_code, new_effective_date,
jeh.currency_conversion_date)
from gl_ledgers lgr
where lgr.ledger_id = jeh.ledger_id),
accrual_rev_effective_date
= (select decode(jeh.accrual_rev_status,
null, ps.start_date,
jeh.accrual_rev_effective_date)
from gl_period_statuses ps
where ps.application_id = 101
and ps.ledger_id = jeh.ledger_id
and ps.period_name = jeh.accrual_rev_period_name)
WHERE je_batch_id = batch_id;
SELECT sum(nvl(jeh.running_total_dr, 0)),
sum(nvl(jeh.running_total_cr, 0)),
sum(nvl(jeh.running_total_accounted_dr, 0)),
sum(nvl(jeh.running_total_accounted_cr, 0))
FROM GL_JE_HEADERS jeh
WHERE jeh.je_batch_id = batch_id
AND (jeh.display_alc_journal_flag is null or jeh.display_alc_journal_flag = 'Y');
SELECT jeh.je_header_id, jeh.currency_code, jeh.actual_flag,
jeh.currency_conversion_type, jeh.je_category,
jeh.ledger_id, lgr.currency_code, jeh.reversed_je_header_id
FROM gl_je_headers jeh, gl_ledgers lgr
WHERE jeh.je_batch_id = batch_id
AND lgr.ledger_id = jeh.ledger_id
ORDER BY jeh.name || to_char(jeh.ledger_id)
FOR UPDATE OF jeh.default_effective_date, jeh.period_name,
jeh.last_update_date, jeh.last_updated_by,
jeh.last_update_login, jeh.running_total_accounted_dr,
jeh.running_total_accounted_cr,
jeh.currency_conversion_rate;
SELECT jeh.je_header_id, jeh.currency_code, jeh.actual_flag,
jeh.currency_conversion_type, jeh.je_category,
jeh.ledger_id, lgr.currency_code, jeh.reversed_je_header_id
FROM gl_je_headers jeh, gl_ledgers lgr
WHERE jeh.je_batch_id = batch_id
AND lgr.ledger_id = jeh.ledger_id
AND jeh.name > (SELECT name || to_char(ledger_id)
FROM gl_je_headers
WHERE je_header_id = header_id)
ORDER BY jeh.name || to_char(jeh.ledger_id)
FOR UPDATE OF jeh.default_effective_date, jeh.period_name,
jeh.last_update_date, jeh.last_updated_by,
jeh.last_update_login, jeh.running_total_accounted_dr,
jeh.running_total_accounted_cr,
jeh.currency_conversion_rate;
DELETE FROM GL_JE_LINES
WHERE je_header_id in
(SELECT je_header_id
FROM gl_je_headers
WHERE je_batch_id = batch_id
AND display_alc_journal_flag = 'N');
DELETE FROM GL_JE_HEADERS
WHERE je_batch_id = batch_id
AND display_alc_journal_flag = 'N';
SELECT jeh.je_category, jeh.actual_flag, jeh.ledger_id,
lgr.currency_code, jeh.reversed_je_header_id
INTO cje_category, cje_actual_flag, cledger_id,
cfunct_curr, crev_jeh_id
FROM gl_je_headers jeh, gl_ledgers lgr
WHERE jeh.je_header_id = header_id
AND lgr.ledger_id = jeh.ledger_id;
gl_je_lines_pkg.update_lines(
cheader_id,
period_name,
effective_date,
-1,
-1,
null,
null,
'N',
clear_stat,
user_id,
login_id);
UPDATE gl_je_headers jeh
SET period_name = change_period.period_name,
default_effective_date = effective_date,
currency_conversion_date = cconversion_date,
accrual_rev_period_name = decode(accrual_rev_status,
NULL, reversal_period,
accrual_rev_period_name),
accrual_rev_flag = decode(accrual_rev_status,
NULL, decode(reversal_period,
NULL, 'N', 'Y'),
accrual_rev_flag),
accrual_rev_effective_date = decode(accrual_rev_status,
NULL, reversal_date,
accrual_rev_effective_date),
accrual_rev_change_sign_flag = decode(accrual_rev_status,
NULL, reversal_change_sign_flag,
accrual_rev_change_sign_flag),
last_update_date = sysdate,
last_updated_by = user_id,
last_update_login = login_id
WHERE jeh.je_header_id = cheader_id;
gl_je_lines_pkg.update_lines(
cheader_id,
period_name,
effective_date,
1,
cconversion_rate,
'STAT',
'STAT',
'Y',
clear_stat,
user_id,
login_id);
gl_je_lines_pkg.update_lines(
cheader_id,
period_name,
effective_date,
1,
cconversion_rate,
cfunct_curr,
cfunct_curr,
'Y',
clear_stat,
user_id,
login_id);
gl_je_lines_pkg.update_lines(
cheader_id,
period_name,
effective_date,
denom_rate,
numer_rate,
ccurrency_code,
cfunct_curr,
'Y',
clear_stat,
user_id,
login_id);
gl_je_lines_pkg.update_lines(
cheader_id,
period_name,
effective_date,
1,
cconversion_rate,
ccurrency_code,
cfunct_curr,
'N',
clear_stat,
user_id,
login_id);
UPDATE gl_je_headers jeh
SET (running_total_dr, running_total_cr,
running_total_accounted_dr, running_total_accounted_cr,
period_name, default_effective_date,
currency_code, currency_conversion_date,
currency_conversion_type, currency_conversion_rate,
last_update_date, last_updated_by, last_update_login)
= (SELECT sum(nvl(jel.entered_dr, 0)),
sum(nvl(jel.entered_cr, 0)),
sum(nvl(jel.accounted_dr, 0)),
sum(nvl(jel.accounted_cr, 0)),
change_period.period_name, change_period.effective_date,
ccurrency_code, cconversion_date,
cconversion_type, cconversion_rate,
sysdate, user_id, login_id
FROM gl_je_lines jel
WHERE jel.je_header_id = jeh.je_header_id),
accrual_rev_period_name = decode(accrual_rev_status,
NULL, reversal_period,
accrual_rev_period_name),
accrual_rev_flag = decode(accrual_rev_status,
NULL, decode(reversal_period,
NULL, 'N', 'Y'),
accrual_rev_flag),
accrual_rev_effective_date = decode(accrual_rev_status,
NULL, reversal_date,
accrual_rev_effective_date),
accrual_rev_change_sign_flag = decode(accrual_rev_status,
NULL, reversal_change_sign_flag,
accrual_rev_change_sign_flag)
WHERE jeh.je_header_id = cheader_id;
SELECT max(default_effective_date)
FROM gl_je_headers jeh
WHERE jeh.je_batch_id = batch_id
AND (jeh.display_alc_journal_flag is null or jeh.display_alc_journal_flag = 'Y')
AND jeh.accrual_rev_status IS NULL;
SELECT max(decode(jeh.tax_status_code, 'R', 1, 0))
FROM gl_je_headers jeh
WHERE jeh.je_batch_id = batch_id;
SELECT 1
FROM dual
WHERE EXISTS (
SELECT 'has seqnum'
FROM gl_je_headers jeh
WHERE jeh.je_batch_id = batch_id
AND jeh.doc_sequence_value IS NOT NULL);
gl_ledgers_pkg.select_columns(
ledger_id,
ledger_name);
gl_je_sources_pkg.select_columns(
je_source_name,
user_je_source_name,
effective_date_rule_code,
frozen_source_flag,
journal_approval_flag);
gl_je_categories_pkg.select_columns(
je_category_name,
user_je_category_name);
gl_period_statuses_pkg.select_columns(
101,
ledger_id,
period_name,
closing_status,
start_date,
end_date,
period_num,
period_year);
gl_period_statuses_pkg.select_columns(
101,
ledger_id,
accrual_rev_period_name,
tmp_closing_status,
accrual_rev_start_date,
accrual_rev_end_date,
tmp_period_num,
tmp_period_year);
gl_daily_conv_types_pkg.select_columns(
currency_conversion_type,
user_currency_conv_type);
gl_budget_versions_pkg.select_columns(
budget_version_id,
budget_name);
gl_encumbrance_types_pkg.select_columns(
encumbrance_type_id,
encumbrance_type);
SELECT header_name || ':' || version_name
INTO posting_acct_seq_name
FROM fun_seq_versions
WHERE seq_version_id = posting_acct_seq_version_id;
SELECT header_name || ':' || version_name
INTO close_acct_seq_name
FROM fun_seq_versions
WHERE seq_version_id = close_acct_seq_version_id;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Je_Header_Id IN OUT NOCOPY NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Ledger_Id NUMBER,
X_Je_Category VARCHAR2,
X_Je_Source VARCHAR2,
X_Period_Name VARCHAR2,
X_Name VARCHAR2,
X_Currency_Code VARCHAR2,
X_Status VARCHAR2,
X_Date_Created DATE,
X_Accrual_Rev_Flag VARCHAR2,
X_Multi_Bal_Seg_Flag VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Default_Effective_Date DATE,
X_Conversion_Flag VARCHAR2,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Encumbrance_Type_Id NUMBER,
X_Budget_Version_Id NUMBER,
X_Balanced_Je_Flag VARCHAR2,
X_Balancing_Segment_Value VARCHAR2,
X_Je_Batch_Id IN OUT NOCOPY NUMBER,
X_From_Recurring_Header_Id NUMBER,
X_Unique_Date VARCHAR2,
X_Earliest_Postable_Date DATE,
X_Posted_Date DATE,
X_Accrual_Rev_Effective_Date DATE,
X_Accrual_Rev_Period_Name VARCHAR2,
X_Accrual_Rev_Status VARCHAR2,
X_Accrual_Rev_Je_Header_Id NUMBER,
X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
X_Description VARCHAR2,
X_Tax_Status_Code VARCHAR2,
X_Control_Total NUMBER,
X_Running_Total_Dr NUMBER,
X_Running_Total_Cr NUMBER,
X_Running_Total_Accounted_Dr NUMBER,
X_Running_Total_Accounted_Cr NUMBER,
X_Currency_Conversion_Rate NUMBER,
X_Currency_Conversion_Type VARCHAR2,
X_Currency_Conversion_Date DATE,
X_External_Reference VARCHAR2,
X_Originating_Bal_Seg_Value VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Context VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Context2 VARCHAR2,
X_Doc_Sequence_Id NUMBER,
X_Doc_Sequence_Value NUMBER,
X_Header_Mode VARCHAR2,
X_Batch_Row_Id IN OUT NOCOPY VARCHAR2,
X_Batch_Name VARCHAR2,
X_Chart_of_Accounts_ID NUMBER,
X_Period_Set_Name VARCHAR2,
X_Accounted_Period_Type VARCHAR2,
X_Batch_Status VARCHAR2,
X_Status_Verified VARCHAR2,
X_Batch_Default_Effective_Date DATE,
X_Batch_Posted_Date DATE,
X_Batch_Date_Created DATE,
X_Budgetary_Control_Status VARCHAR2,
X_Approval_Status_Code VARCHAR2,
X_Batch_Control_Total IN OUT NOCOPY NUMBER,
X_Batch_Running_Total_Dr IN OUT NOCOPY NUMBER,
X_Batch_Running_Total_Cr IN OUT NOCOPY NUMBER,
X_Average_Journal_Flag VARCHAR2,
X_Org_Id NUMBER,
X_Posting_Run_Id NUMBER,
X_Request_Id NUMBER,
X_Packet_Id NUMBER,
X_Unreservation_Packet_Id NUMBER,
X_Jgzz_Recon_Context VARCHAR2,
X_Jgzz_Recon_Ref VARCHAR2,
X_Reference_Date DATE
) IS
CURSOR C IS SELECT rowid FROM GL_JE_HEADERS
WHERE je_header_id = X_Je_Header_Id;
SELECT 'Y'
INTO has_line
FROM gl_je_lines
WHERE je_header_id = X_Je_Header_Id
AND rownum = 1;
INSERT INTO GL_JE_HEADERS(
je_header_id,
last_update_date,
last_updated_by,
ledger_id,
je_category,
je_source,
period_name,
name,
currency_code,
status,
date_created,
accrual_rev_flag,
multi_bal_seg_flag,
actual_flag,
default_effective_date,
conversion_flag,
creation_date,
created_by,
last_update_login,
encumbrance_type_id,
budget_version_id,
balanced_je_flag,
balancing_segment_value,
je_batch_id,
from_recurring_header_id,
unique_date,
earliest_postable_date,
posted_date,
accrual_rev_effective_date,
accrual_rev_period_name,
accrual_rev_status,
accrual_rev_je_header_id,
accrual_rev_change_sign_flag,
description,
tax_status_code,
control_total,
running_total_dr,
running_total_cr,
running_total_accounted_dr,
running_total_accounted_cr,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
external_reference,
originating_bal_seg_value,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
context,
ussgl_transaction_code,
context2,
doc_sequence_id,
doc_sequence_value,
jgzz_recon_context,
jgzz_recon_ref,
reference_date
) VALUES (
X_Je_Header_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Ledger_Id,
X_Je_Category,
X_Je_Source,
X_Period_Name,
X_Name,
X_Currency_Code,
X_Status,
X_Date_Created,
X_Accrual_Rev_Flag,
X_Multi_Bal_Seg_Flag,
X_Actual_Flag,
X_Default_Effective_Date,
X_Conversion_Flag,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Encumbrance_Type_Id,
X_Budget_Version_Id,
X_Balanced_Je_Flag,
X_Balancing_Segment_Value,
X_Je_Batch_Id,
X_From_Recurring_Header_Id,
X_Unique_Date,
X_Earliest_Postable_Date,
X_Posted_Date,
X_Accrual_Rev_Effective_Date,
X_Accrual_Rev_Period_Name,
X_Accrual_Rev_Status,
X_Accrual_Rev_Je_Header_Id,
X_Accrual_Rev_Change_Sign_Flag,
X_Description,
X_Tax_Status_Code,
X_Control_Total,
X_Running_Total_Dr,
X_Running_Total_Cr,
X_Running_Total_Accounted_Dr,
X_Running_Total_Accounted_Cr,
X_Currency_Conversion_Rate,
X_Currency_Conversion_Type,
X_Currency_Conversion_Date,
X_External_Reference,
X_Originating_Bal_Seg_Value,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Context,
X_Ussgl_Transaction_Code,
X_Context2,
X_Doc_Sequence_Id,
X_Doc_Sequence_Value,
X_Jgzz_Recon_Context,
X_Jgzz_Recon_Ref,
X_Reference_Date
);
GL_JE_BATCHES_PKG.Insert_Row(
X_Rowid => X_Batch_Row_Id,
X_Je_Batch_Id => X_Je_Batch_Id,
X_Name => X_Batch_Name,
X_Chart_of_Accounts_Id => X_Chart_of_Accounts_Id,
X_Period_Set_Name => X_Period_Set_Name,
X_Accounted_Period_Type => X_Accounted_Period_Type,
X_Status => X_Batch_Status,
X_Budgetary_Control_Status=>
X_Budgetary_Control_Status,
X_Approval_Status_Code => X_Approval_Status_Code,
X_Status_Verified => X_Status_Verified,
X_Actual_Flag => X_Actual_Flag,
X_Default_Period_Name => X_Period_Name,
X_Default_Effective_Date=>
X_Batch_Default_Effective_Date,
X_Posted_Date => X_Batch_Posted_Date,
X_Date_Created =>
X_Batch_Date_Created,
X_Control_Total => X_Batch_Control_Total,
X_Running_Total_Dr => X_Batch_Running_Total_Dr,
X_Running_Total_Cr => X_Batch_Running_Total_Cr,
X_Running_Total_Accounted_Dr =>
X_Running_Total_Accounted_Dr,
X_Running_Total_Accounted_Cr =>
X_Running_Total_Accounted_Cr,
X_Average_Journal_Flag => X_Average_Journal_Flag,
X_Org_Id => X_Org_Id,
X_Posting_Run_Id => X_Posting_Run_Id,
X_Request_Id => X_Request_Id,
X_Packet_Id => X_Packet_Id,
X_Unreservation_Packet_Id=>
X_Unreservation_Packet_Id,
X_Creation_Date => X_Creation_Date,
X_Created_By => X_Created_By,
X_Last_Update_Date => X_Last_Update_Date,
X_Last_Updated_By => X_Last_Updated_By,
X_Last_Update_Login => X_Last_Update_Login);
END Insert_Row;
SELECT *
FROM GL_JE_HEADERS
WHERE rowid = X_Rowid
FOR UPDATE of Je_Header_Id NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
SELECT *
FROM GL_JE_HEADERS
WHERE rowid = X_Rowid
FOR UPDATE of Je_Header_Id NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Je_Header_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Ledger_Id NUMBER,
X_Je_Category VARCHAR2,
X_Je_Source VARCHAR2,
X_Period_Name VARCHAR2,
X_Name VARCHAR2,
X_Currency_Code VARCHAR2,
X_Status VARCHAR2,
X_Date_Created DATE,
X_Accrual_Rev_Flag VARCHAR2,
X_Multi_Bal_Seg_Flag VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Default_Effective_Date DATE,
X_Conversion_Flag VARCHAR2,
X_Last_Update_Login NUMBER,
X_Encumbrance_Type_Id NUMBER,
X_Budget_Version_Id NUMBER,
X_Balanced_Je_Flag VARCHAR2,
X_Balancing_Segment_Value VARCHAR2,
X_Je_Batch_Id NUMBER,
X_From_Recurring_Header_Id NUMBER,
X_Unique_Date VARCHAR2,
X_Earliest_Postable_Date DATE,
X_Posted_Date DATE,
X_Accrual_Rev_Effective_Date DATE,
X_Accrual_Rev_Period_Name VARCHAR2,
X_Accrual_Rev_Status VARCHAR2,
X_Accrual_Rev_Je_Header_Id NUMBER,
X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
X_Description VARCHAR2,
X_Tax_Status_Code VARCHAR2,
X_Control_Total NUMBER,
X_Running_Total_Dr NUMBER,
X_Running_Total_Cr NUMBER,
X_Running_Total_Accounted_Dr NUMBER,
X_Running_Total_Accounted_Cr NUMBER,
X_Currency_Conversion_Rate NUMBER,
X_Currency_Conversion_Type VARCHAR2,
X_Currency_Conversion_Date DATE,
X_External_Reference VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Context VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Context2 VARCHAR2,
X_Doc_Sequence_Id NUMBER,
X_Doc_Sequence_Value NUMBER
) IS
has_line VARCHAR2(1);
SELECT 'Y'
INTO has_line
FROM gl_je_lines
WHERE je_header_id = X_Je_Header_Id
AND rownum = 1;
UPDATE GL_JE_HEADERS
SET
je_header_id = X_Je_Header_Id,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
ledger_id = X_Ledger_Id,
je_category = X_Je_Category,
je_source = X_Je_Source,
period_name = X_Period_Name,
name = X_Name,
currency_code = X_Currency_Code,
status = X_Status,
date_created = X_Date_Created,
accrual_rev_flag = X_Accrual_Rev_Flag,
multi_bal_seg_flag = X_Multi_Bal_Seg_Flag,
actual_flag = X_Actual_Flag,
default_effective_date = X_Default_Effective_Date,
conversion_flag = X_Conversion_Flag,
last_update_login = X_Last_Update_Login,
encumbrance_type_id = X_Encumbrance_Type_Id,
budget_version_id = X_Budget_Version_Id,
balanced_je_flag = X_Balanced_Je_Flag,
balancing_segment_value = X_Balancing_Segment_Value,
je_batch_id = X_Je_Batch_Id,
from_recurring_header_id = X_From_Recurring_Header_Id,
unique_date = X_Unique_Date,
earliest_postable_date = X_Earliest_Postable_Date,
posted_date = X_Posted_Date,
accrual_rev_effective_date = X_Accrual_Rev_Effective_Date,
accrual_rev_period_name = X_Accrual_Rev_Period_Name,
accrual_rev_status = X_Accrual_Rev_Status,
accrual_rev_je_header_id = X_Accrual_Rev_Je_Header_Id,
accrual_rev_change_sign_flag = X_Accrual_Rev_Change_Sign_Flag,
description = X_Description,
tax_status_code = X_Tax_Status_Code,
control_total = X_Control_Total,
running_total_dr = X_Running_Total_Dr,
running_total_cr = X_Running_Total_Cr,
running_total_accounted_dr = X_Running_Total_Accounted_Dr,
running_total_accounted_cr = X_Running_Total_Accounted_Cr,
currency_conversion_rate = X_Currency_Conversion_Rate,
currency_conversion_type = X_Currency_Conversion_Type,
currency_conversion_date = X_Currency_Conversion_Date,
external_reference = X_External_Reference,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
context = X_Context,
ussgl_transaction_code = X_Ussgl_Transaction_Code,
context2 = X_Context2,
doc_sequence_id = X_Doc_Sequence_Id,
doc_sequence_value = X_Doc_Sequence_Value
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Je_Header_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Ledger_Id NUMBER,
X_Je_Category VARCHAR2,
X_Je_Source VARCHAR2,
X_Period_Name VARCHAR2,
X_Name VARCHAR2,
X_Currency_Code VARCHAR2,
X_Status VARCHAR2,
X_Date_Created DATE,
X_Accrual_Rev_Flag VARCHAR2,
X_Multi_Bal_Seg_Flag VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Default_Effective_Date DATE,
X_Conversion_Flag VARCHAR2,
X_Last_Update_Login NUMBER,
X_Encumbrance_Type_Id NUMBER,
X_Budget_Version_Id NUMBER,
X_Balanced_Je_Flag VARCHAR2,
X_Balancing_Segment_Value VARCHAR2,
X_Je_Batch_Id NUMBER,
X_From_Recurring_Header_Id NUMBER,
X_Unique_Date VARCHAR2,
X_Earliest_Postable_Date DATE,
X_Posted_Date DATE,
X_Accrual_Rev_Effective_Date DATE,
X_Accrual_Rev_Period_Name VARCHAR2,
X_Accrual_Rev_Status VARCHAR2,
X_Accrual_Rev_Je_Header_Id NUMBER,
X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
X_Description VARCHAR2,
X_Tax_Status_Code VARCHAR2,
X_Control_Total NUMBER,
X_Running_Total_Dr NUMBER,
X_Running_Total_Cr NUMBER,
X_Running_Total_Accounted_Dr NUMBER,
X_Running_Total_Accounted_Cr NUMBER,
X_Currency_Conversion_Rate NUMBER,
X_Currency_Conversion_Type VARCHAR2,
X_Currency_Conversion_Date DATE,
X_External_Reference VARCHAR2,
X_Originating_Bal_Seg_Value VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Context VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Context2 VARCHAR2,
X_Doc_Sequence_Id NUMBER,
X_Doc_Sequence_Value NUMBER,
X_Effective_Date_Changed VARCHAR2,
X_Header_Mode VARCHAR2,
X_Batch_Row_Id VARCHAR2,
X_Batch_Name VARCHAR2,
X_Chart_of_Accounts_ID NUMBER,
X_Period_Set_Name VARCHAR2,
X_Accounted_Period_Type VARCHAR2,
X_Batch_Status VARCHAR2,
X_Status_Verified VARCHAR2,
X_Batch_Default_Effective_Date DATE,
X_Batch_Posted_Date DATE,
X_Batch_Date_Created DATE,
X_Budgetary_Control_Status VARCHAR2,
X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
X_Batch_Control_Total IN OUT NOCOPY NUMBER,
X_Batch_Running_Total_Dr IN OUT NOCOPY NUMBER,
X_Batch_Running_Total_Cr IN OUT NOCOPY NUMBER,
X_Average_Journal_Flag VARCHAR2,
X_Posting_Run_Id NUMBER,
X_Request_Id NUMBER,
X_Packet_Id NUMBER,
X_Unreservation_Packet_Id NUMBER,
Update_Effective_Date_Flag VARCHAR2,
Update_Approval_Stat_Flag VARCHAR2,
X_Jgzz_Recon_Context VARCHAR2,
X_Jgzz_Recon_Ref VARCHAR2,
X_Reference_Date DATE
) IS
has_line VARCHAR2(1);
SELECT 'Y'
INTO has_line
FROM gl_je_lines
WHERE je_header_id = X_Je_Header_Id
AND rownum = 1;
gl_je_lines_pkg.update_lines(
X_Je_Header_Id,
X_Period_Name,
X_Default_Effective_Date,
-1,
-1,
null,
null,
'N',
'N',
X_Last_Updated_By,
X_Last_Update_Login);
UPDATE GL_JE_HEADERS
SET
je_header_id = X_Je_Header_Id,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
ledger_id = X_Ledger_Id,
je_category = X_Je_Category,
je_source = X_Je_Source,
period_name = X_Period_Name,
name = X_Name,
currency_code = X_Currency_Code,
status = X_Status,
date_created = X_Date_Created,
accrual_rev_flag = X_Accrual_Rev_Flag,
multi_bal_seg_flag = X_Multi_Bal_Seg_Flag,
actual_flag = X_Actual_Flag,
default_effective_date = X_Default_Effective_Date,
conversion_flag = X_Conversion_Flag,
last_update_login = X_Last_Update_Login,
encumbrance_type_id = X_Encumbrance_Type_Id,
budget_version_id = X_Budget_Version_Id,
balanced_je_flag = X_Balanced_Je_Flag,
balancing_segment_value = X_Balancing_Segment_Value,
je_batch_id = X_Je_Batch_Id,
from_recurring_header_id = X_From_Recurring_Header_Id,
unique_date = X_Unique_Date,
earliest_postable_date = X_Earliest_Postable_Date,
posted_date = X_Posted_Date,
accrual_rev_effective_date = X_Accrual_Rev_Effective_Date,
accrual_rev_period_name = X_Accrual_Rev_Period_Name,
accrual_rev_status = X_Accrual_Rev_Status,
accrual_rev_je_header_id = X_Accrual_Rev_Je_Header_Id,
accrual_rev_change_sign_flag = X_Accrual_Rev_Change_Sign_Flag,
description = X_Description,
tax_status_code = X_Tax_Status_Code,
control_total = X_Control_Total,
running_total_dr = X_Running_Total_Dr,
running_total_cr = X_Running_Total_Cr,
running_total_accounted_dr = X_Running_Total_Accounted_Dr,
running_total_accounted_cr = X_Running_Total_Accounted_Cr,
currency_conversion_rate = X_Currency_Conversion_Rate,
currency_conversion_type = X_Currency_Conversion_Type,
currency_conversion_date = X_Currency_Conversion_Date,
external_reference = X_External_Reference,
originating_bal_seg_value = X_Originating_Bal_Seg_Value,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
context = X_Context,
ussgl_transaction_code = X_Ussgl_Transaction_Code,
context2 = X_Context2,
doc_sequence_id = X_Doc_Sequence_Id,
doc_sequence_value = X_Doc_Sequence_Value,
jgzz_recon_context = X_Jgzz_Recon_Context,
jgzz_recon_ref = X_Jgzz_Recon_Ref,
reference_date = X_Reference_Date
WHERE rowid = X_rowid;
GL_JE_BATCHES_PKG.Update_Row(
X_Rowid => X_Batch_Row_Id,
X_Je_Batch_Id => X_Je_Batch_Id,
X_Name => X_Batch_Name,
X_Chart_of_Accounts_Id => X_Chart_of_Accounts_Id,
X_Period_Set_Name => X_Period_Set_Name,
X_Accounted_Period_Type => X_Accounted_Period_Type,
X_Status => X_Batch_Status,
X_Budgetary_Control_Status=>
X_Budgetary_Control_Status,
X_Approval_Status_Code => X_Approval_Status_Code,
X_Status_Verified => X_Status_Verified,
X_Actual_Flag => X_Actual_Flag,
X_Default_Period_Name => X_Period_Name,
X_Default_Effective_Date=>
X_Batch_Default_Effective_Date,
X_Posted_Date => X_Batch_Posted_Date,
X_Date_Created => X_Batch_Date_Created,
X_Control_Total => X_Batch_Control_Total,
X_Running_Total_Dr => X_Batch_Running_Total_Dr,
X_Running_Total_Cr => X_Batch_Running_Total_Cr,
X_Average_Journal_Flag => X_Average_Journal_Flag,
X_Posting_Run_Id => X_Posting_Run_Id,
X_Request_Id => X_Request_Id,
X_Packet_Id => X_Packet_Id,
X_Unreservation_Packet_Id=>
X_Unreservation_Packet_Id,
X_Last_Update_Date => X_Last_Update_Date,
X_Last_Updated_By => X_Last_Updated_By,
X_Last_Update_Login => X_Last_Update_Login,
Update_Effective_Date_Flag => Update_Effective_Date_Flag,
Update_Approval_Stat_Flag => Update_Approval_Stat_Flag);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Je_Header_Id NUMBER,
X_Header_Mode VARCHAR2,
X_Batch_Row_Id VARCHAR2,
X_Je_Batch_Id NUMBER,
X_Ledger_Id NUMBER,
X_Actual_Flag VARCHAR2,
X_Period_Name VARCHAR2,
X_Batch_Name VARCHAR2,
X_Chart_of_Accounts_ID NUMBER,
X_Period_Set_Name VARCHAR2,
X_Accounted_Period_Type VARCHAR2,
X_Batch_Status VARCHAR2,
X_Status_Verified VARCHAR2,
X_Batch_Default_Effective_Date DATE,
X_Batch_Posted_Date DATE,
X_Batch_Date_Created DATE,
X_Budgetary_Control_Status VARCHAR2,
X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
X_Batch_Control_Total IN OUT NOCOPY NUMBER,
X_Batch_Running_Total_Dr IN OUT NOCOPY NUMBER,
X_Batch_Running_Total_Cr IN OUT NOCOPY NUMBER,
X_Average_Journal_Flag VARCHAR2,
X_Posting_Run_Id NUMBER,
X_Request_Id NUMBER,
X_Packet_Id NUMBER,
X_Unreservation_Packet_Id NUMBER,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER
) IS
CURSOR count_headers IS
SELECT count(*)
FROM gl_je_headers
WHERE je_batch_id = X_Je_Batch_Id;
SELECT je_header_id
FROM gl_je_headers
WHERE parent_je_header_id = X_Je_Header_Id;
gl_je_lines_pkg.delete_lines(X_Je_Header_Id);
dummy := gl_je_segment_values_pkg.delete_segment_values(X_Je_Header_Id);
DELETE gl_je_lines_recon
WHERE je_header_id = X_Je_Header_Id;
gl_je_lines_pkg.delete_lines(l_je_header_id);
dummy := gl_je_segment_values_pkg.delete_segment_values(l_je_header_id);
DELETE gl_je_lines_recon
WHERE je_header_id = l_je_header_id;
UPDATE gl_je_headers
SET reversed_je_header_id = null,
accrual_rev_je_header_id = decode(accrual_rev_status,
'R', accrual_rev_je_header_id,
null)
WHERE je_header_id =
(SELECT accrual_rev_je_header_id
FROM gl_je_headers
WHERE rowid = X_Rowid
AND accrual_rev_status = 'R');
UPDATE gl_je_headers
SET accrual_rev_status = null,
accrual_rev_je_header_id =null,
accrual_rev_flag = 'Y'
WHERE je_header_id =
( SELECT reversed_je_header_id
FROM gl_je_headers
WHERE je_header_id = X_Je_Header_Id
AND reversed_je_header_id IS NOT NULL);
UPDATE gl_je_headers
SET reversed_je_header_id = null,
accrual_rev_je_header_id = decode(accrual_rev_status,
'R', accrual_rev_je_header_id,
null)
WHERE je_header_id =
(SELECT accrual_rev_je_header_id
FROM gl_je_headers
WHERE parent_je_header_id = X_Je_Header_Id
AND accrual_rev_status = 'R');
UPDATE gl_je_headers
SET accrual_rev_status = null,
accrual_rev_je_header_id =null,
accrual_rev_flag = 'Y'
WHERE je_header_id =
( SELECT reversed_je_header_id
FROM gl_je_headers
WHERE parent_je_header_id = X_Je_Header_Id
AND reversed_je_header_id IS NOT NULL);
DELETE FROM gl_je_headers
WHERE parent_je_header_id = X_Je_Header_Id;
DELETE FROM GL_JE_HEADERS
WHERE rowid = X_Rowid;
GL_JE_BATCHES_PKG.delete_row(X_Batch_Row_id, X_Je_Batch_Id);
GL_JE_BATCHES_PKG.Update_Row(
X_Rowid => X_Batch_Row_Id,
X_Je_Batch_Id => X_Je_Batch_Id,
X_Name => X_Batch_Name,
X_Chart_of_Accounts_Id => X_Chart_of_Accounts_Id,
X_Period_Set_Name => X_Period_Set_Name,
X_Accounted_Period_Type => X_Accounted_Period_Type,
X_Status => X_Batch_Status,
X_Budgetary_Control_Status=>
X_Budgetary_Control_Status,
X_Approval_Status_Code => X_Approval_Status_Code,
X_Status_Verified => 'N',
X_Actual_Flag => X_Actual_Flag,
X_Default_Period_Name => X_Period_Name,
X_Default_Effective_Date=>
X_Batch_Default_Effective_Date,
X_Posted_Date => X_Batch_Posted_Date,
X_Date_Created => X_Batch_Date_Created,
X_Control_Total => X_Batch_Control_Total,
X_Running_Total_Dr => X_Batch_Running_Total_Dr,
X_Running_Total_Cr => X_Batch_Running_Total_Cr,
X_Average_Journal_Flag => X_Average_Journal_Flag,
X_Posting_Run_Id => X_Posting_Run_Id,
X_Request_Id => X_Request_Id,
X_Packet_Id => X_Packet_Id,
X_Unreservation_Packet_Id=>
X_Unreservation_Packet_Id,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => X_Last_Updated_By,
X_Last_Update_Login => X_Last_Update_Login,
Update_Effective_Date_Flag => 'N',
Update_Approval_Stat_Flag => 'D');
END Delete_Row;