[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Duplicate'
FROM GL_JE_LINES jel
WHERE jel.je_header_id = header_id
AND jel.je_line_num = line_num
AND ( row_id is null
OR jel.rowid <> row_id);
PROCEDURE delete_lines(header_id NUMBER) IS
BEGIN
-- Delete all of the lines in that header
DELETE gl_je_lines
WHERE je_header_id = header_id;
GL_IMPORT_REFERENCES_PKG.delete_lines (header_id);
fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.delete_lines');
END delete_lines;
PROCEDURE update_lines(header_id NUMBER,
x_period_name VARCHAR2,
x_effective_date DATE,
conversion_denom_rate NUMBER,
conversion_numer_rate NUMBER,
entered_currency VARCHAR2,
accounted_currency VARCHAR2,
ignore_ignore_flag VARCHAR2,
clear_stat VARCHAR2,
user_id NUMBER,
login_id NUMBER) IS
ext_precision NUMBER;
UPDATE gl_je_lines
SET period_name = x_period_name,
effective_date = x_effective_date,
entered_dr = decode(conversion_numer_rate, -1, entered_dr,
round(entered_dr/entered_divisor)*entered_divisor),
entered_cr = decode(conversion_numer_rate, -1, entered_cr,
round(entered_cr/entered_divisor)*entered_divisor),
accounted_dr = decode(conversion_numer_rate, -1, accounted_dr,
decode(decode(ignore_ignore_flag,
'Y', 'N',
ignore_rate_flag),
'Y', accounted_dr,
round((((round(entered_dr/entered_divisor)
*entered_divisor)
/conversion_denom_rate)
*conversion_numer_rate)
/ accounted_divisor)*accounted_divisor)),
accounted_cr = decode(conversion_numer_rate, -1, accounted_cr,
decode(decode(ignore_ignore_flag,
'Y', 'N',
ignore_rate_flag),
'Y', accounted_cr,
round((((round(entered_cr/entered_divisor)
*entered_divisor)
/conversion_denom_rate)
*conversion_numer_rate)
/ accounted_divisor)*accounted_divisor)),
ignore_rate_flag = decode(ignore_ignore_flag, 'Y', null,
ignore_rate_flag),
stat_amount = decode(clear_stat, 'Y', null, stat_amount),
last_update_date = sysdate,
last_updated_by = user_id,
last_update_login = login_id
WHERE je_header_id = header_id;
fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.update_lines');
END update_lines;
SELECT sum(nvl(entered_dr, 0)),
sum(nvl(entered_cr, 0)),
sum(nvl(accounted_dr, 0)),
sum(nvl(accounted_cr, 0))
FROM GL_JE_LINES jel
WHERE jel.je_header_id = header_id;
SELECT stat_amount
FROM GL_JE_LINES jel
WHERE jel.je_header_id = header_id
AND stat_amount IS NOT NULL;
SELECT 'Has tax'
FROM GL_JE_LINES jel
WHERE jel.je_header_id = header_id
AND tax_type_code IS NOT NULL;
SELECT max(decode(allow_rate_override_flag, 'N', 1, 0))
INTO dummy
FROM zx_account_rates rates
WHERE rates.ledger_id = x_ledger_id
AND rates.account_segment_value = x_account_num
AND rates.tax_class = 'NON_TAXABLE';
gl_stat_account_uom_pkg.select_columns(
coa_id,
acct_num,
x_unit_of_measure,
description);
SELECT jgzz_recon_flag
INTO tmp_recon_flag
FROM gl_code_combinations
WHERE code_combination_id = x_ccid;
SELECT count(*), max(decode(rates.tax_class, 'INPUT', 'I', 'OUTPUT', 'O', NULL, 'T'))
INTO num_defaults, default_tax_type_code
FROM zx_account_rates rates
WHERE rates.ledger_id = x_ledger_id
AND rates.account_segment_value = x_account_value
AND nvl(rates.tax_class,'T') <> 'NON_TAXABLE';
SELECT l.meaning
INTO default_tax_type
FROM gl_lookups l
WHERE l.lookup_type = 'TAX_TYPE'
AND l.lookup_code = default_tax_type_code;
SELECT l.lookup_code, l.meaning
INTO default_rounding_rule_code,
default_rounding_rule
FROM gl_lookups l
WHERE l.lookup_type = 'TAX_ROUNDING_RULE'
AND l.lookup_code = default_rounding_rule_code;
SELECT l.lookup_code, l.meaning
INTO default_includes_tax_flag,
default_includes_tax
FROM gl_lookups l
WHERE l.lookup_type = 'YES/NO'
AND l.lookup_code = temp_includes_tax_flag;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Je_Header_Id IN OUT NOCOPY NUMBER,
X_Je_Line_Num NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Ledger_id NUMBER,
X_Code_Combination_Id NUMBER,
X_Period_Name VARCHAR2,
X_Effective_Date DATE,
X_Status VARCHAR2,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Entered_Dr NUMBER,
X_Entered_Cr NUMBER,
X_Accounted_Dr NUMBER,
X_Accounted_Cr NUMBER,
X_Description VARCHAR2,
X_Reference_1 VARCHAR2,
X_Reference_2 VARCHAR2,
X_Reference_3 VARCHAR2,
X_Reference_4 VARCHAR2,
X_Reference_5 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_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Context VARCHAR2,
X_Context2 VARCHAR2,
X_Invoice_Date DATE,
X_Tax_Code VARCHAR2,
X_Invoice_Identifier VARCHAR2,
X_Invoice_Amount NUMBER,
X_No1 VARCHAR2,
X_Stat_Amount NUMBER,
X_Ignore_Rate_Flag VARCHAR2,
X_Context3 VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Subledger_Doc_Sequence_Id NUMBER,
X_Context4 VARCHAR2,
X_Subledger_Doc_Sequence_Value NUMBER,
X_Reference_6 VARCHAR2,
X_Reference_7 VARCHAR2,
X_Reference_8 VARCHAR2,
X_Reference_9 VARCHAR2,
X_Reference_10 VARCHAR2,
X_Recon_On_Flag VARCHAR2,
X_Recon_Rowid IN OUT NOCOPY VARCHAR2,
X_Jgzz_Recon_Status VARCHAR2,
X_Jgzz_Recon_Date DATE,
X_Jgzz_Recon_Id NUMBER,
X_Jgzz_Recon_Ref VARCHAR2,
X_Taxable_Line_Flag VARCHAR2,
X_Tax_Type_Code VARCHAR2,
X_Tax_Code_Id NUMBER,
X_Tax_Rounding_Rule_Code VARCHAR2,
X_Amount_Includes_Tax_Flag VARCHAR2,
X_Tax_Document_Identifier VARCHAR2,
X_Tax_Document_Date DATE,
X_Tax_Customer_Name VARCHAR2,
X_Tax_Customer_Reference VARCHAR2,
X_Tax_Registration_Number VARCHAR2,
X_Tax_Line_Flag VARCHAR2,
X_Tax_Group_Id NUMBER,
X_Third_Party_Id VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM GL_JE_LINES
WHERE je_header_id = X_Je_Header_Id
AND je_line_num = X_Je_Line_Num;
gl_je_segment_values_pkg.insert_ccid_segment_values(
X_Je_Header_Id,
X_Code_Combination_Id,
X_Last_Updated_By,
X_Last_Update_Login);
INSERT INTO GL_JE_LINES (
je_header_id,
je_line_num,
last_update_date,
last_updated_by,
ledger_id,
code_combination_id,
period_name,
effective_date,
status,
creation_date,
created_by,
last_update_login,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
description,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
context,
context2,
invoice_date,
tax_code,
invoice_identifier,
invoice_amount,
no1,
stat_amount,
ignore_rate_flag,
context3,
ussgl_transaction_code,
subledger_doc_sequence_id,
context4,
subledger_doc_sequence_value,
reference_6,
reference_7,
reference_8,
reference_9,
reference_10,
taxable_line_flag,
tax_type_code,
tax_code_id,
tax_rounding_rule_code,
amount_includes_tax_flag,
tax_document_identifier,
tax_document_date,
tax_customer_name,
tax_customer_reference,
tax_registration_number,
tax_line_flag,
tax_group_id,
co_third_party
) VALUES (
X_Je_Header_Id,
X_Je_Line_Num,
X_Last_Update_Date,
X_Last_Updated_By,
X_Ledger_id,
X_Code_Combination_Id,
X_Period_Name,
X_Effective_Date,
X_Status,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Entered_Dr,
X_Entered_Cr,
X_Accounted_Dr,
X_Accounted_Cr,
X_Description,
X_Reference_1,
X_Reference_2,
X_Reference_3,
X_Reference_4,
X_Reference_5,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Attribute16,
X_Attribute17,
X_Attribute18,
X_Attribute19,
X_Attribute20,
X_Context,
X_Context2,
X_Invoice_Date,
X_Tax_Code,
X_Invoice_Identifier,
X_Invoice_Amount,
X_No1,
X_Stat_Amount,
X_Ignore_Rate_Flag,
X_Context3,
X_Ussgl_Transaction_Code,
X_Subledger_Doc_Sequence_Id,
X_Context4,
X_Subledger_Doc_Sequence_Value,
X_Reference_6,
X_Reference_7,
X_Reference_8,
X_Reference_9,
X_Reference_10,
X_Taxable_Line_Flag,
X_Tax_Type_Code,
X_Tax_Code_Id,
X_Tax_Rounding_Rule_Code,
X_Amount_Includes_Tax_Flag,
X_Tax_Document_Identifier,
X_Tax_Document_Date,
X_Tax_Customer_Name,
X_Tax_Customer_Reference,
X_Tax_Registration_Number,
X_Tax_Line_Flag,
X_Tax_Group_Id,
X_Third_Party_Id
);
gl_je_lines_recon_pkg.insert_row(
X_Rowid=>X_Recon_Rowid,
X_Je_Header_Id=>X_Je_Header_id,
X_Je_Line_Num=>X_Je_Line_Num,
X_Ledger_Id=>X_Ledger_Id,
X_Jgzz_Recon_Status=>X_Jgzz_Recon_Status,
X_Jgzz_Recon_Date=>X_Jgzz_Recon_Date,
X_Jgzz_Recon_Id=>X_Jgzz_Recon_Id,
X_Jgzz_Recon_Ref=>X_Jgzz_Recon_Ref,
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_LINES
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_Je_Line_Num NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Ledger_Id NUMBER,
X_Code_Combination_Id NUMBER,
X_Period_Name VARCHAR2,
X_Effective_Date DATE,
X_Status VARCHAR2,
X_Last_Update_Login NUMBER,
X_Entered_Dr NUMBER,
X_Entered_Cr NUMBER,
X_Accounted_Dr NUMBER,
X_Accounted_Cr NUMBER,
X_Description VARCHAR2,
X_Reference_1 VARCHAR2,
X_Reference_2 VARCHAR2,
X_Reference_3 VARCHAR2,
X_Reference_4 VARCHAR2,
X_Reference_5 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_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute16 VARCHAR2,
X_Attribute17 VARCHAR2,
X_Attribute18 VARCHAR2,
X_Attribute19 VARCHAR2,
X_Attribute20 VARCHAR2,
X_Context VARCHAR2,
X_Context2 VARCHAR2,
X_Invoice_Date DATE,
X_Tax_Code VARCHAR2,
X_Invoice_Identifier VARCHAR2,
X_Invoice_Amount NUMBER,
X_No1 VARCHAR2,
X_Stat_Amount NUMBER,
X_Ignore_Rate_Flag VARCHAR2,
X_Context3 VARCHAR2,
X_Ussgl_Transaction_Code VARCHAR2,
X_Subledger_Doc_Sequence_Id NUMBER,
X_Context4 VARCHAR2,
X_Subledger_Doc_Sequence_Value NUMBER,
X_Reference_6 VARCHAR2,
X_Reference_7 VARCHAR2,
X_Reference_8 VARCHAR2,
X_Reference_9 VARCHAR2,
X_Reference_10 VARCHAR2,
X_Recon_On_Flag VARCHAR2,
X_Recon_Rowid IN OUT NOCOPY VARCHAR2,
X_Jgzz_Recon_Status VARCHAR2,
X_Jgzz_Recon_Date DATE,
X_Jgzz_Recon_Id NUMBER,
X_Jgzz_Recon_Ref VARCHAR2,
X_Taxable_Line_Flag VARCHAR2,
X_Tax_Type_Code VARCHAR2,
X_Tax_Code_Id NUMBER,
X_Tax_Rounding_Rule_Code VARCHAR2,
X_Amount_Includes_Tax_Flag VARCHAR2,
X_Tax_Document_Identifier VARCHAR2,
X_Tax_Document_Date DATE,
X_Tax_Customer_Name VARCHAR2,
X_Tax_Customer_Reference VARCHAR2,
X_Tax_Registration_Number VARCHAR2,
X_Tax_Line_Flag VARCHAR2,
X_Tax_Group_Id NUMBER,
X_Third_Party_Id VARCHAR2
) IS
BEGIN
-- Add any new segment values
gl_je_segment_values_pkg.insert_ccid_segment_values(
X_Je_Header_Id,
X_Code_Combination_Id,
X_Last_Updated_By,
X_Last_Update_Login);
UPDATE GL_JE_LINES
SET
je_header_id = X_Je_Header_Id,
je_line_num = X_Je_Line_Num,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
ledger_id = X_Ledger_Id,
code_combination_id = X_Code_Combination_Id,
period_name = X_Period_Name,
effective_date = X_Effective_Date,
status = X_Status,
last_update_login = X_Last_Update_Login,
entered_dr = X_Entered_Dr,
entered_cr = X_Entered_Cr,
accounted_dr = X_Accounted_Dr,
accounted_cr = X_Accounted_Cr,
description = X_Description,
reference_1 = X_Reference_1,
reference_2 = X_Reference_2,
reference_3 = X_Reference_3,
reference_4 = X_Reference_4,
reference_5 = X_Reference_5,
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,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
attribute16 = X_Attribute16,
attribute17 = X_Attribute17,
attribute18 = X_Attribute18,
attribute19 = X_Attribute19,
attribute20 = X_Attribute20,
context = X_Context,
context2 = X_Context2,
invoice_date = X_Invoice_Date,
tax_code = X_Tax_Code,
invoice_identifier = X_Invoice_Identifier,
invoice_amount = X_Invoice_Amount,
no1 = X_No1,
stat_amount = X_Stat_Amount,
ignore_rate_flag = X_Ignore_Rate_Flag,
context3 = X_Context3,
ussgl_transaction_code = X_Ussgl_Transaction_Code,
subledger_doc_sequence_id = X_Subledger_Doc_Sequence_Id,
context4 = X_Context4,
subledger_doc_sequence_value = X_Subledger_Doc_Sequence_Value,
reference_6 = X_Reference_6,
reference_7 = X_Reference_7,
reference_8 = X_Reference_8,
reference_9 = X_Reference_9,
reference_10 = X_Reference_10,
taxable_line_flag = X_Taxable_Line_Flag,
tax_type_code = X_Tax_Type_Code,
tax_code_id = X_Tax_Code_Id,
tax_rounding_rule_code = X_Tax_Rounding_Rule_Code,
amount_includes_tax_flag = X_Amount_Includes_Tax_Flag,
tax_document_identifier = X_Tax_Document_Identifier,
tax_document_date = X_Tax_Document_Date,
tax_customer_name = X_Tax_Customer_Name,
tax_customer_reference = X_Tax_Customer_Reference,
tax_registration_number = X_Tax_Registration_Number,
tax_line_flag = X_Tax_Line_Flag,
tax_group_id = X_Tax_Group_Id,
co_third_party = X_Third_Party_Id
WHERE rowid = X_rowid;
gl_je_lines_recon_pkg.insert_row(
X_Rowid=>X_Recon_Rowid,
X_Je_Header_Id=>X_Je_Header_id,
X_Je_Line_Num=>X_Je_Line_Num,
X_Ledger_Id=>X_Ledger_Id,
X_Jgzz_Recon_Status=>X_Jgzz_Recon_Status,
X_Jgzz_Recon_Date=>X_Jgzz_Recon_Date,
X_Jgzz_Recon_Id=>X_Jgzz_Recon_Id,
X_Jgzz_Recon_Ref=>X_Jgzz_Recon_Ref,
X_Last_Update_Date=>X_Last_Update_Date,
X_Last_Updated_By=>X_Last_Updated_By,
X_Last_Update_Login=>X_Last_Update_Login);
gl_je_lines_recon_pkg.update_row(
X_Rowid=>X_Recon_Rowid,
X_Je_Header_Id=>X_Je_Header_id,
X_Je_Line_Num=>X_Je_Line_Num,
X_Ledger_Id=>X_Ledger_Id,
X_Jgzz_Recon_Status=>X_Jgzz_Recon_Status,
X_Jgzz_Recon_Date=>X_Jgzz_Recon_Date,
X_Jgzz_Recon_Id=>X_Jgzz_Recon_Id,
X_Jgzz_Recon_Ref=>X_Jgzz_Recon_Ref,
X_Last_Update_Date=>X_Last_Update_Date,
X_Last_Updated_By=>X_Last_Updated_By,
X_Last_Update_Login=>X_Last_Update_Login);
gl_je_lines_recon_pkg.delete_row(
X_Rowid=>X_Recon_Rowid);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Recon_Rowid VARCHAR2) IS
CURSOR c_del_line (lv_row_id VARCHAR2 )IS
SELECT je_line_num,je_header_id
FROM GL_JE_LINES
WHERE rowid = lv_row_id ;
DELETE FROM GL_JE_LINES
WHERE rowid = X_Rowid;
GL_IMPORT_REFERENCES_PKG.delete_line (lv_header_id ,lv_line_num );
gl_je_lines_recon_pkg.delete_row(
X_Rowid=>X_Recon_Rowid);
END Delete_Row;