[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x' FROM GL_LEDGERS L1
WHERE L1.LEDGER_ID = X_To_Ledger_Id
AND L1.PERIOD_SET_NAME =
(SELECT L2.PERIOD_SET_NAME FROM GL_LEDGERS L2
WHERE L2.LEDGER_ID = X_From_Ledger_Id);
select GL_CONSOLIDATION_HISTORY_S.NEXTVAL
into next_val
from dual;
SELECT period_name, start_date, closing_status
INTO PeriodName, StartDate, ClosingStatus
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = LedgerId
AND adjustment_period_flag = 'N'
AND quarter_num = QuarterNum
AND period_year = QuarterYear
AND quarter_start_date = start_date;
SELECT period_name, start_date, closing_status
INTO PeriodName, StartDate, ClosingStatus
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = LedgerId
AND adjustment_period_flag = 'N'
AND period_year = PeriodYear
AND year_start_date = start_date;
/* Name: insert_average_record
* Desc: Copy the standard consolidation record for average consolidation.
*/
PROCEDURE insert_average_record(
SourceRunId NUMBER,
TargetRunId NUMBER,
AverageToPeriodName VARCHAR2,
AvgAmountType VARCHAR2,
FromDateEntered DATE
) IS
BEGIN
INSERT INTO GL_CONSOLIDATION_HISTORY(
consolidation_run_id,
consolidation_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
from_period_name,
to_period_name,
to_currency_code,
method_flag,
run_easylink_flag,
run_posting_flag,
actual_flag,
from_budget_name,
to_budget_name,
from_budget_version_id,
to_budget_version_id,
average_consolidation_flag,
amount_type,
from_date,
target_resp_name,
target_user_name,
target_database_name
) SELECT
TargetRunId,
consolidation_id,
last_update_date,
last_updated_by,
last_updated_by,
last_update_date,
last_updated_by,
from_period_name,
AverageToPeriodName,
to_currency_code,
method_flag,
run_easylink_flag,
run_posting_flag,
actual_flag,
NULL,
NULL,
NULL,
NULL,
'Y',
AvgAmountType,
FromDateEntered,
target_resp_name,
target_user_name,
target_database_name
FROM gl_consolidation_history
WHERE NOT EXISTS (SELECT 1
FROM gl_consolidation_history
WHERE consolidation_run_id = TargetRunId)
AND consolidation_run_id = SourceRunId;
END insert_average_record;
/* Name: insert_row
* Desc: Table handler for insertion.
*/
PROCEDURE Insert_Row(
X_Usage_Code VARCHAR2,
X_Rowid IN OUT NOCOPY VARCHAR2,
X_Consolidation_Run_Id NUMBER,
X_StdRunId IN OUT NOCOPY NUMBER,
X_AvgRunId IN OUT NOCOPY NUMBER,
X_Consolidation_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_From_Period_Name VARCHAR2,
X_Standard_To_Period_Name VARCHAR2,
X_To_Period_Name IN OUT NOCOPY VARCHAR2,
X_To_Currency_Code VARCHAR2,
X_Method_Flag VARCHAR2,
X_Run_Easylink_Flag VARCHAR2,
X_Run_Posting_Flag VARCHAR2,
X_Actual_Flag VARCHAR2,
X_From_Budget_Name VARCHAR2,
X_To_Budget_Name VARCHAR2,
X_From_Budget_Version_Id NUMBER,
X_To_Budget_Version_Id NUMBER,
X_Amount_Type_Code VARCHAR2,
X_Amount_Type VARCHAR2,
X_StdAmountType VARCHAR2,
X_AvgAmountType VARCHAR2,
X_From_Date_Entered DATE,
X_From_Date IN OUT NOCOPY DATE,
X_Average_To_Period_Name VARCHAR2,
X_Target_Resp_Name VARCHAR2,
X_Target_User_Name VARCHAR2,
X_Target_DB_Name VARCHAR2
) IS
CURSOR C(Run_Id NUMBER) IS SELECT rowid FROM GL_CONSOLIDATION_HISTORY
WHERE consolidation_run_id = Run_Id;
CURSOR C2 IS SELECT gl_consolidation_history_s.nextval FROM sys.dual;
INSERT INTO GL_CONSOLIDATION_HISTORY(
consolidation_run_id,
consolidation_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
from_period_name,
to_period_name,
to_currency_code,
method_flag,
run_easylink_flag,
run_posting_flag,
actual_flag,
from_budget_name,
to_budget_name,
from_budget_version_id,
to_budget_version_id,
average_consolidation_flag,
amount_type,
from_date,
target_resp_name,
target_user_name,
target_database_name
) VALUES (
X_Consolidation_Run_Id,
X_Consolidation_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Updated_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_From_Period_Name,
X_Standard_To_Period_Name,
X_To_Currency_Code,
X_Method_Flag,
X_Run_Easylink_Flag,
X_Run_Posting_Flag,
X_Actual_Flag,
X_From_Budget_Name,
X_To_Budget_Name,
X_From_Budget_Version_Id,
X_To_Budget_Version_Id,
'N',
X_StdAmountType,
X_From_Date,
X_Target_Resp_Name,
X_Target_User_Name,
X_Target_DB_Name
);
INSERT INTO GL_CONSOLIDATION_HISTORY(
consolidation_run_id,
consolidation_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
from_period_name,
to_period_name,
to_currency_code,
method_flag,
run_easylink_flag,
run_posting_flag,
actual_flag,
from_budget_name,
to_budget_name,
from_budget_version_id,
to_budget_version_id,
average_consolidation_flag,
amount_type,
from_date,
target_resp_name,
target_user_name,
target_database_name
) VALUES (
TempRunId,
X_Consolidation_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Updated_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_From_Period_Name,
X_Average_To_Period_Name,
X_To_Currency_Code,
X_Method_Flag,
X_Run_Easylink_Flag,
X_Run_Posting_Flag,
X_Actual_Flag,
NULL,
NULL,
NULL,
NULL,
'Y',
X_AvgAmountType,
X_From_Date_Entered,
X_Target_Resp_Name,
X_Target_User_Name,
X_Target_DB_Name
);
END Insert_Row;
PROCEDURE Insert_Cons_Set_Row(
X_Usage_Code VARCHAR2,
X_Rowid VARCHAR2,
X_Std_Amounttype VARCHAR2,
X_Avg_Amounttype VARCHAR2,
X_Consolidation_Id NUMBER,
X_Consolidation_Set_Id NUMBER,
X_Last_Updated_By NUMBER,
X_From_Period_Name VARCHAR2,
X_Standard_To_Period_Name VARCHAR2,
X_Average_To_Period_Name VARCHAR2,
X_Average_To_Start_Date DATE,
X_To_Currency_Code VARCHAR2,
X_Method_Flag VARCHAR2,
X_Run_Journal_Import_Flag VARCHAR2,
X_Audit_Mode_Flag VARCHAR2,
X_Summary_Journals_Flag VARCHAR2,
X_Run_Posting_Flag VARCHAR2,
X_Actual_Flag VARCHAR2,
X_Consolidation_Name VARCHAR2,
X_From_Date_Entered DATE,
X_From_Ledger_Id NUMBER,
X_To_Ledger_Id NUMBER,
X_Check_Batches IN OUT NOCOPY VARCHAR2,
X_num_conc_requests IN OUT NOCOPY NUMBER,
X_Target_Resp_Name VARCHAR2,
X_Target_User_Name VARCHAR2,
X_Target_DB_Name VARCHAR2,
X_first_request_Id IN OUT NOCOPY number,
X_last_request_Id IN OUT NOCOPY number,
X_access_set_id NUMBER
) IS
CURSOR Select_Rowid(Runid NUMBER) IS
SELECT rowid
FROM GL_CONSOLIDATION_HISTORY
WHERE consolidation_run_id = Runid;
IF NOT(GL_CONS_BATCHES_PKG.Insert_Consolidation_Batches(
'U',
X_Consolidation_Id,
std_runid,
X_Last_Updated_By,
X_From_Ledger_Id,
X_To_Ledger_Id,
X_From_Period_Name,
X_To_Currency_Code)) THEN
X_Check_Batches := 'N';
INSERT INTO GL_CONSOLIDATION_HISTORY(
consolidation_run_id,
consolidation_id,
consolidation_set_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
from_period_name,
to_period_name,
to_currency_code,
method_flag,
run_easylink_flag,
run_posting_flag,
actual_flag,
average_consolidation_flag,
amount_type,
from_date,
status,
request_id,
target_resp_name,
target_user_name,
target_database_name
) VALUES (
std_runid,
X_Consolidation_Id,
X_Consolidation_Set_Id,
sysdate,
X_Last_Updated_By,
X_Last_Updated_By,
sysdate,
X_Last_Updated_By,
X_From_Period_Name,
X_Standard_To_Period_Name,
X_To_Currency_Code,
X_Method_Flag,
X_Run_Journal_Import_Flag,
X_Run_Posting_Flag,
X_Actual_Flag,
'N',
X_Std_AmountType,
X_From_Date_Entered,
'TS',
Std_Request_Id,
X_Target_Resp_Name,
X_Target_User_Name,
X_Target_DB_Name
);
OPEN Select_Rowid(std_runid);
FETCH Select_Rowid INTO temp_rowid;
IF (Select_Rowid%NOTFOUND) THEN
CLOSE Select_Rowid;
CLOSE Select_Rowid;
INSERT INTO GL_CONSOLIDATION_HISTORY(
consolidation_run_id,
consolidation_id,
consolidation_set_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
from_period_name,
to_period_name,
to_currency_code,
method_flag,
run_easylink_flag,
run_posting_flag,
actual_flag,
average_consolidation_flag,
amount_type,
from_date,
status,
request_id,
target_resp_name,
target_user_name,
target_database_name
) VALUES (
avg_runid,
X_Consolidation_Id,
X_Consolidation_Set_Id,
sysdate,
X_Last_Updated_By,
X_Last_Updated_By,
sysdate,
X_Last_Updated_By,
X_From_Period_Name,
X_Average_To_Period_Name,
X_To_Currency_Code,
X_Method_Flag,
X_Run_Journal_Import_Flag,
X_Run_Posting_Flag,
X_Actual_Flag,
'Y',
X_Avg_AmountType,
X_From_Date_Entered,
'TS',
avg_request_id,
X_Target_Resp_Name,
X_Target_User_Name,
X_Target_DB_Name
);
OPEN Select_Rowid(avg_runid);
FETCH Select_Rowid INTO temp_rowid;
IF (Select_Rowid%NOTFOUND) THEN
CLOSE Select_Rowid;
CLOSE Select_Rowid;
fnd_message.set_token('PROCEDURE', 'Insert_Cons_Set_Row');
END Insert_Cons_Set_Row;
PROCEDURE Insert_For_Budgetyear(
X_Consolidation_Run_Id NUMBER,
X_Consolidation_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_From_Period_Name VARCHAR2,
X_To_Period_Name VARCHAR2,
X_To_Currency_Code VARCHAR2,
X_Method_Flag VARCHAR2,
X_Run_Easylink_Flag VARCHAR2,
X_Run_Posting_Flag VARCHAR2,
X_Actual_Flag VARCHAR2,
X_From_Budget_Name VARCHAR2,
X_To_Budget_Name VARCHAR2,
X_From_Budget_Version_Id NUMBER,
X_To_Budget_Version_Id NUMBER,
X_Consolidation_Set_Id NUMBER,
X_Status VARCHAR2,
X_Request_Id NUMBER,
X_Amount_Type_Code VARCHAR2,
X_ledger_id NUMBER,
X_Period_Year NUMBER,
X_Target_Resp_Name VARCHAR2,
X_Target_User_Name VARCHAR2,
X_Target_DB_Name VARCHAR2) IS
BEGIN
INSERT INTO GL_CONSOLIDATION_HISTORY(
consolidation_run_id,
consolidation_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
from_period_name,
to_period_name,
to_currency_code,
method_flag,
run_easylink_flag,
run_posting_flag,
actual_flag,
from_budget_name,
to_budget_name,
from_budget_version_id,
to_budget_version_id,
consolidation_set_id,
status,
request_id,
average_consolidation_flag,
amount_type,
target_resp_name,
target_user_name,
target_database_name
)
SELECT X_Consolidation_Run_Id,
X_Consolidation_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Updated_By,
X_Last_Update_Date,
X_Last_Updated_By,
PS.period_name,
PS.period_name,
X_To_Currency_Code,
X_Method_Flag,
X_Run_Easylink_Flag,
X_Run_Posting_Flag,
X_Actual_Flag,
X_From_Budget_Name,
X_To_Budget_Name,
X_From_Budget_Version_Id,
X_To_Budget_Version_Id,
X_Consolidation_Set_Id,
X_Status,
X_Request_Id,
'N',
X_Amount_Type_Code,
X_Target_Resp_Name,
X_Target_User_Name,
X_Target_DB_Name
FROM
GL_PERIOD_STATUSES PS,
GL_BUDGET_PERIOD_RANGES BPR
WHERE BPR.budget_version_id = X_From_Budget_Version_Id
AND BPR.period_year = X_Period_Year
AND BPR.open_flag = 'O'
AND BPR.period_year = ps.period_year
AND PS.application_id = 101
AND PS.ledger_id = X_ledger_id
AND PS.effective_period_num
BETWEEN BPR.period_year * 10000 + BPR.start_period_num
AND BPR.period_year * 10000 + BPR.end_period_num ;
END Insert_For_Budgetyear;
PROCEDURE Insert_Status_ReqId(
X_StdRunId NUMBER,
X_AvgRunId NUMBER,
X_StdReqId NUMBER,
X_AvgReqId NUMBER) IS
BEGIN
IF (X_StdRunId IS NOT NULL) THEN
UPDATE GL_CONSOLIDATION_HISTORY
SET status = 'TS',
request_id = X_StdReqId
WHERE consolidation_run_id = X_StdRunId;
UPDATE GL_CONSOLIDATION_HISTORY
SET status = 'TS',
request_id = X_AvgReqId
WHERE consolidation_run_id = X_AvgRunId;
END Insert_Status_ReqId;