[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT period_set_name, accounted_period_type
INTO l_period_set_name, l_accounted_period_type
FROM gl_sets_of_books
WHERE set_of_books_id = g_sob_id;
UPDATE igc_cbc_je_lines
SET mig_result_code = NULL,
mig_request_id = NULL
WHERE set_of_books_id = g_sob_id
AND substr(mig_result_code, 0, 1) = 'F'
AND mig_result_code IS NOT NULL
AND period_year = p_fiscal_year
AND actual_flag = 'E';
SELECT DISTINCT reference_1, effective_date, je_category, cbc_je_batch_id,
decode(je_category, 'Provisional', '1', 'Confirmed', '2', 'Budget', '3', 'Requisitions', '1', 'Purchases', '2', '99') seq
FROM igc_cbc_je_lines
WHERE mig_result_code IS NULL
AND mig_request_id IS NULL
AND period_year = p_fiscal_year
AND set_of_books_id = g_sob_id
ORDER BY reference_1, seq;
/*Update Result Codes in igc_cbc_je_lines for Exception reporting and to ensure CP rerun does not cause issues*/
/*Update Request ID of the CBC Upgrade Concurrent program*/
UPDATE igc_cbc_je_lines
SET mig_request_id = FND_GLOBAL.CONC_REQUEST_ID
WHERE reference_1 = l_cbc_line.reference_1
AND je_category = l_cbc_line.je_category
AND effective_date = l_cbc_line.effective_date
AND cbc_je_batch_id = l_cbc_line.cbc_je_batch_id;
/*Update CBC result Codes into IGC_CBC_JE_LINES from IGC_CC_INTERFACE to print Exception Report*/
UPDATE igc_cbc_je_lines ijl
SET (mig_result_code) = (SELECT cbc_result_code
FROM igc_cc_interface ict
WHERE ijl.reference_1 = ict.cc_header_id
AND ijl.effective_date = ict.cc_transaction_date
AND NVL(ijl.je_category, '') = NVL(ict.je_category_name, '')
AND ijl.code_combination_id = ict.code_combination_id)
WHERE ijl.reference_1 = l_cbc_line.reference_1
AND je_category = l_cbc_line.je_category
AND effective_date = l_cbc_line.effective_date
AND cbc_je_batch_id = l_cbc_line.cbc_je_batch_id;
Put_Debug_Msg(l_full_path, 'Updating Result Code. Number of rows updated: ' || SQL%ROWCOUNT);
DELETE FROM igc_cc_interface
WHERE reference_8 = 'MIG';
SELECT icj.reference_1,
icj.reference_3,
icj.reference_2,
icj.code_combination_id,
icj.cbc_je_line_num,
icj.effective_date,
icj.entered_dr,
icj.entered_cr,
icj.je_source,
icj.je_category,
icj.period_name,
icj.actual_flag,
'C',
icj.set_of_books_id,
icj.description,
icj.posted_date,
DECODE(icj.je_source,'Contract Commitment','CC','Project Accounting','PA','Requisitions','REQ','Purchasing','PO','INV'),
icj.currency_code
FROM igc_cbc_je_lines icj, gl_code_combinations gcc
WHERE icj.set_of_books_id = g_sob_id
AND icj.reference_1 = p_cc_header_id
AND icj.je_category = p_category_name
AND icj.effective_date = p_transaction_date
AND icj.cbc_je_batch_id = p_cbc_je_batch_id
AND icj.period_year = p_fiscal_year
AND icj.actual_flag = 'E'
AND icj.mig_result_code IS NULL /*Only Records NOT migrated are processed*/
AND icj.code_combination_id = gcc.code_combination_id
AND gcc.summary_flag = 'N'; /*Migrate only for Detailed Records*/
SELECT decode(l_cbc_je_lines.JE_SOURCE, 'Contract Commitment', 'CC', 'Project Accounting', 'PA', 'Requisitions', 'REQ', 'Purchasing', 'PO', 'INV')
INTO x_doc_type
FROM DUAL;
INSERT INTO igc_cc_interface(
CC_HEADER_ID,
CC_VERSION_NUM,
CC_ACCT_LINE_ID,
CC_DET_PF_LINE_ID,
CODE_COMBINATION_ID,
BATCH_LINE_NUM,
CC_TRANSACTION_DATE,
CC_FUNC_DR_AMT,
CC_FUNC_CR_AMT,
JE_SOURCE_NAME,
JE_CATEGORY_NAME,
PERIOD_SET_NAME,
PERIOD_NAME,
ACTUAL_FLAG,
BUDGET_DEST_FLAG,
SET_OF_BOOKS_ID,
CBC_RESULT_CODE,
STATUS_CODE,
REFERENCE_1,
REFERENCE_2,
REFERENCE_3,
REFERENCE_8,
BATCH_ID,
BUDGET_VERSION_ID,
TRANSACTION_DESCRIPTION,
CC_ENCMBRNC_DATE,
DOCUMENT_TYPE,
CURRENCY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
VALUES(
l_cbc_je_lines.REFERENCE_1,
l_cbc_je_lines.REFERENCE_3,
l_cbc_je_lines.REFERENCE_2,
NULL,
l_cbc_je_lines.CODE_COMBINATION_ID,
l_cbc_je_lines.CBC_JE_LINE_NUM,
l_cbc_je_lines.EFFECTIVE_DATE,
l_cbc_je_lines.ENTERED_DR,
l_cbc_je_lines.ENTERED_CR,
l_cbc_je_lines.JE_SOURCE,
l_cbc_je_lines.JE_CATEGORY,
NULL,
l_cbc_je_lines.PERIOD_NAME,
l_cbc_je_lines.ACTUAL_FLAG,
'C',
l_cbc_je_lines.SET_OF_BOOKS_ID,
NULL,
NULL,
l_cbc_je_lines.REFERENCE_1,
l_cbc_je_lines.REFERENCE_2,
l_cbc_je_lines.REFERENCE_3,
'MIG',
NULL,
NULL,
l_cbc_je_lines.DESCRIPTION,
l_cbc_je_lines.POSTED_DATE,
x_doc_type,
l_cbc_je_lines.CURRENCY_CODE ,
sysdate,
-1,
sysdate,
-1
);
Put_Debug_Msg(l_full_path, 'Number of rows Inserted: ' || SQL%ROWCOUNT);
SELECT distinct segment1 INTO l_reference_4 FROM pa_projects_all p, pa_budget_versions bv
WHERE p.project_id = bv.project_id AND bv.budget_version_id = p_cc_header_id;
SELECT distinct segment1 INTO l_reference_4 FROM PO_HEADERS_ALL
WHERE po_header_id = p_cc_header_id;
SELECT distinct segment1 INTO l_reference_4 FROM PO_REQUISITION_HEADERS_ALL
WHERE requisition_header_id = p_cc_header_id;
SELECT distinct cc_num INTO l_reference_4 FROM igc_cc_headers_all
WHERE cc_header_id = p_cc_header_id;
UPDATE igc_cc_interface
SET reference_4 = l_reference_4
WHERE cc_header_id = p_cc_header_id;
cursor c_budget_cur is Select budget_name
from gl_budgets_v
where ledger_id = g_sob_id
and first_valid_period_name = v_period_start_name
and last_valid_period_name = v_period_end_name
and master_budget_version_id is null;
cursor c_master_cur is Select budget_name,master_budget_version_id
from gl_budgets_v
where ledger_id = g_sob_id
and first_valid_period_name = v_period_start_name
and last_valid_period_name = v_period_end_name
and master_budget_version_id is not null;
select min(period_num),max(period_num)
into v_period_start_num,v_period_end_num
from gl_periods
where period_set_name = p_period_set_name
and period_year = p_fiscal_year
and Period_type = p_accounted_period_type ;
select period_name into v_period_start_name from gl_periods where period_num = v_period_start_num
and period_set_name = p_period_set_name
and period_year = p_fiscal_year
and Period_type = p_accounted_period_type;
select period_name into v_period_end_name from gl_periods where period_num = v_period_end_num
and period_set_name = p_period_set_name
and period_year = p_fiscal_year
and Period_type = p_accounted_period_type;
select p_old_name||'_MIG' into p_new_name from dual;
select substrb(p_old_name,1,decode(instrb(p_old_name,'_',-1),p_len,p_len-1,p_len))||'_MIG' into p_new_name
from dual;
select count(*) into cnt from gl_budgets
where budget_name = p_new_name
and description not like '%R12_MIG_'||p_old_name;
select count(*) into cnt from gl_budget_entities
Where name = p_new_name
and description not like '%R12_MIG_'||p_old_name;
select budget_version_id, latest_opened_year
FROM gl_budgets_v
WHERE budget_name = c_new_budget;
select * into BUDGET
from gl_budgets
where budget_name = p_old_bud_name;
GL_BUDGETS_PKG.Insert_Row(
X_Rowid => v_row_id,
X_Budget_Type => BUDGET.Budget_Type,
X_Budget_Name => v_new_budget,
X_ledger_Id => g_cbc_ledger_id,
X_Status => 'O', --BUDGET.Status,
X_Date_Created => BUDGET.Date_Created,
X_Require_Budget_Journals_flag => BUDGET.Require_Budget_Journals_Flag,
X_Current_Version_Id => BUDGET.Current_Version_Id,
X_Latest_Opened_Year => NULL,
X_First_Valid_Period_Name => BUDGET.First_Valid_Period_Name,
X_Last_Valid_Period_Name => BUDGET.Last_Valid_Period_Name,
X_Description => BUDGET.Description,
X_Date_Closed => BUDGET.Date_Closed,
X_Attribute1 => BUDGET.Attribute1,
X_Attribute2 => BUDGET.Attribute2,
X_Attribute3 => BUDGET.Attribute3,
X_Attribute4 => BUDGET.Attribute4,
X_Attribute5 => BUDGET.Attribute5,
X_Attribute6 => BUDGET.Attribute6,
X_Attribute7 => BUDGET.Attribute7,
X_Attribute8 => BUDGET.Attribute8,
X_Context => BUDGET.Context,
X_User_Id => BUDGET.Created_By,
X_Login_Id => BUDGET.Last_Update_Login,
X_Date => BUDGET.Creation_Date,
X_Budget_Version_Id => p_budget_version_id,
X_Master_Budget_Version_Id => p_Master_Bud_Ver_Id);
Cursor range_cur is SELECT *
FROM GL_BUDGET_ASSIGNMENT_RANGES
WHERE budget_entity_id = v_budget_entity_id;
Select * from GL_BUDORG_BC_OPTIONS
Where RANGE_ID = p_range_id;
SELECT budget_entity_id from gl_budget_entities where name = c_new_org_name;
SELECT distinct budget_entity_id into v_budget_entity_id
FROM GL_BUDGET_ASSIGNMENT_RANGES_V
WHERE range_id IN
( Select range_id From GL_BUDORG_BC_OPTIONS_V
Where FUNDING_BUDGET_NAME= P_BUDGET_NAME);
select * into bud_org_rec from gl_budget_entities
where budget_entity_id = v_budget_entity_id;
/* Insert only if new bud org is not migrated already */
BEGIN
If lengthb(bud_org_rec.Description) > 207 then
V_Org_Description := substrb(bud_org_rec.Description,1,207)||'R12_MIG_'||bud_org_rec.name;
/************ Insert the corresponding rows in gl_entity_budgets. */
p_budget_entity_id := gl_budget_entities_pkg.get_unique_id;
INSERT INTO GL_ENTITY_BUDGETS
(budget_entity_id, budget_version_id, frozen_flag,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT p_budget_entity_id, bv.budget_version_id, 'N',
bud_org_rec.last_updated_by, sysdate,
bud_org_rec.last_updated_by, sysdate,
bud_org_rec.last_update_login
FROM gl_budgets b, gl_budget_versions bv
WHERE b.ledger_id = bud_org_rec.Ledger_Id
AND bv.budget_name = b.budget_name
AND bv.budget_type = b.budget_type;
/************ Insert Budget Organization */
INSERT INTO gl_budget_entities(
budget_entity_id,
name,
ledger_id,
last_update_date,
last_updated_by,
budget_password_required_flag,
status_code,
creation_date,
created_by,
last_update_login,
encrypted_budget_password,
description,
start_date,
end_date,
segment1_type,
segment2_type,
segment3_type,
segment4_type,
segment5_type,
segment6_type,
segment7_type,
segment8_type,
segment9_type,
segment10_type,
segment11_type,
segment12_type,
segment13_type,
segment14_type,
segment15_type,
segment16_type,
segment17_type,
segment18_type,
segment19_type,
segment20_type,
segment21_type,
segment22_type,
segment23_type,
segment24_type,
segment25_type,
segment26_type,
segment27_type,
segment28_type,
segment29_type,
segment30_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
context,
security_flag)
VALUES (
p_budget_entity_id,
X_Name,
g_cbc_ledger_id,
bud_org_rec.Last_Update_Date,
bud_org_rec.Last_Updated_By,
bud_org_rec.Budget_Password_Required_Flag,
bud_org_rec.Status_Code,
bud_org_rec.Creation_Date,
bud_org_rec.Created_By,
bud_org_rec.Last_Update_Login,
bud_org_rec.Encrypted_Budget_Password,
V_Org_Description,
bud_org_rec.Start_Date,
bud_org_rec.End_Date,
bud_org_rec.Segment1_Type,
bud_org_rec.Segment2_Type,
bud_org_rec.Segment3_Type,
bud_org_rec.Segment4_Type,
bud_org_rec.Segment5_Type,
bud_org_rec.Segment6_Type,
bud_org_rec.Segment7_Type,
bud_org_rec.Segment8_Type,
bud_org_rec.Segment9_Type,
bud_org_rec.Segment10_Type,
bud_org_rec.Segment11_Type,
bud_org_rec.Segment12_Type,
bud_org_rec.Segment13_Type,
bud_org_rec.Segment14_Type,
bud_org_rec.Segment15_Type,
bud_org_rec.Segment16_Type,
bud_org_rec.Segment17_Type,
bud_org_rec.Segment18_Type,
bud_org_rec.Segment19_Type,
bud_org_rec.Segment20_Type,
bud_org_rec.Segment21_Type,
bud_org_rec.Segment22_Type,
bud_org_rec.Segment23_Type,
bud_org_rec.Segment24_Type,
bud_org_rec.Segment25_Type,
bud_org_rec.Segment26_Type,
bud_org_rec.Segment27_Type,
bud_org_rec.Segment28_Type,
bud_org_rec.Segment29_Type,
bud_org_rec.Segment30_Type,
bud_org_rec.Attribute1,
bud_org_rec.Attribute2,
bud_org_rec.Attribute3,
bud_org_rec.Attribute4,
bud_org_rec.Attribute5,
bud_org_rec.Attribute6,
bud_org_rec.Attribute7,
bud_org_rec.Attribute8,
bud_org_rec.Attribute9,
bud_org_rec.Attribute10,
bud_org_rec.Context,
bud_org_rec.Security_Flag);
/********** Open Ranges cursor to insert ranges */
FOR Ranges in range_cur
LOOP
select chart_of_accounts_id into v_coa_id
from gl_ledgers where ledger_id = g_cbc_ledger_id;
select gl_budget_assignment_ranges_s.NEXTVAL into v_new_range_id
from dual;
GL_BUD_ASSIGN_RANGE_PKG.Insert_Row(
X_Rowid => v_range_id,
X_Budget_Entity_Id => p_budget_entity_id,
X_Ledger_Id => g_cbc_ledger_id,
X_Currency_Code => RANGES.Currency_Code,
X_Entry_Code => RANGES.Entry_Code,
X_Range_Id => v_new_range_id,
X_Status => 'A', --RANGES.Status,
X_Last_Update_Date => RANGES.Last_Update_Date,
X_Created_By => RANGES.Created_By,
X_Creation_Date => RANGES.Creation_Date,
X_Last_Updated_By => RANGES.Last_Updated_By,
X_Last_Update_Login => RANGES.Last_Update_Login,
X_Sequence_Number => RANGES.Sequence_Number,
X_Segment1_Low => RANGES.Segment1_Low,
X_Segment1_High => RANGES.Segment1_High,
X_Segment2_Low => RANGES.Segment2_Low,
X_Segment2_High => RANGES.Segment2_High,
X_Segment3_Low => RANGES.Segment3_Low,
X_Segment3_High => RANGES.Segment3_High,
X_Segment4_Low => RANGES.Segment4_Low,
X_Segment4_High => RANGES.Segment4_High,
X_Segment5_Low => RANGES.Segment5_Low,
X_Segment5_High => RANGES.Segment5_High,
X_Segment6_Low => RANGES.Segment6_Low,
X_Segment6_High => RANGES.Segment6_High,
X_Segment7_Low => RANGES.Segment7_Low,
X_Segment7_High => RANGES.Segment7_High,
X_Segment8_Low => RANGES.Segment8_Low,
X_Segment8_High => RANGES.Segment8_High,
X_Segment9_Low => RANGES.Segment9_Low,
X_Segment9_High => RANGES.Segment9_High,
X_Segment10_Low => RANGES.Segment10_Low,
X_Segment10_High => RANGES.Segment10_High,
X_Segment11_Low => RANGES.Segment11_Low,
X_Segment11_High => RANGES.Segment11_High,
X_Segment12_Low => RANGES.Segment12_Low,
X_Segment12_High => RANGES.Segment12_High,
X_Segment13_Low => RANGES.Segment13_Low,
X_Segment13_High => RANGES.Segment13_High,
X_Segment14_Low => RANGES.Segment14_Low,
X_Segment14_High => RANGES.Segment14_High,
X_Segment15_Low => RANGES.Segment15_Low,
X_Segment15_High => RANGES.Segment15_High,
X_Segment16_Low => RANGES.Segment16_Low,
X_Segment16_High => RANGES.Segment16_High,
X_Segment17_Low => RANGES.Segment17_Low,
X_Segment17_High => RANGES.Segment17_High,
X_Segment18_Low => RANGES.Segment18_Low,
X_Segment18_High => RANGES.Segment18_High,
X_Segment19_Low => RANGES.Segment19_Low,
X_Segment19_High => RANGES.Segment19_High,
X_Segment20_Low => RANGES.Segment20_Low,
X_Segment20_High => RANGES.Segment20_High,
X_Segment21_Low => RANGES.Segment21_Low,
X_Segment21_High => RANGES.Segment21_High,
X_Segment22_Low => RANGES.Segment22_Low,
X_Segment22_High => RANGES.Segment22_High,
X_Segment23_Low => RANGES.Segment23_Low,
X_Segment23_High => RANGES.Segment23_High,
X_Segment24_Low => RANGES.Segment24_Low,
X_Segment24_High => RANGES.Segment24_High,
X_Segment25_Low => RANGES.Segment25_Low,
X_Segment25_High => RANGES.Segment25_High,
X_Segment26_Low => RANGES.Segment26_Low,
X_Segment26_High => RANGES.Segment26_High,
X_Segment27_Low => RANGES.Segment27_Low,
X_Segment27_High => RANGES.Segment27_High,
X_Segment28_Low => RANGES.Segment28_Low,
X_Segment28_High => RANGES.Segment28_High,
X_Segment29_Low => RANGES.Segment29_Low,
X_Segment29_High => RANGES.Segment29_High,
X_Segment30_Low => RANGES.Segment30_Low,
X_Segment30_High => RANGES.Segment30_High,
X_Context => RANGES.Context,
X_Attribute1 => RANGES.Attribute1,
X_Attribute2 => RANGES.Attribute2,
X_Attribute3 => RANGES.Attribute3,
X_Attribute4 => RANGES.Attribute4,
X_Attribute5 => RANGES.Attribute5,
X_Attribute6 => RANGES.Attribute6,
X_Attribute7 => RANGES.Attribute7,
X_Attribute8 => RANGES.Attribute8,
X_Attribute9 => RANGES.Attribute9,
X_Attribute10 => RANGES.Attribute10,
X_Attribute11 => RANGES.Attribute11,
X_Attribute12 => RANGES.Attribute12,
X_Attribute13 => RANGES.Attribute13,
X_Attribute14 => RANGES.Attribute14,
X_Attribute15 => RANGES.Attribute15,
X_Chart_Of_Accounts_Id => v_coa_id);
/********** Insert in to GL_BUDORG_BC_OPTIONS */
FOR budctrl_rec in BC_CUR(RANGES.range_id)
LOOP
BEGIN
SELECT CBC_OVERRIDE into v_cbc_override
FROM IGC_CBC_BA_RANGES
WHERE CBC_RANGE_ID= RANGES.range_id
AND SET_OF_BOOKS_ID = g_sob_id
AND BUDGET_ENTITY_ID = v_budget_entity_id;
GL_BUDORG_BC_OPTIONS_PKG.Insert_Row(
X_Rowid => v_range_id,
X_Range_Id => v_new_range_id,
X_Last_Update_Date => budctrl_rec.Last_Update_Date,
X_Created_By => budctrl_rec.Created_By,
X_Creation_Date => budctrl_rec.Creation_Date,
X_Funds_Check_Level_Code=> v_cbc_override ,
X_Last_Updated_By => budctrl_rec.Last_Updated_By,
X_Last_Update_Login => budctrl_rec.Last_Update_Login,
X_Amount_Type => budctrl_rec.Amount_Type,
X_Boundary_Code => budctrl_rec.Boundary_Code,
X_Funding_Budget_Version_Id=> p_budget_version_id);
select template_id, CBC_OVERRIDE
from IGC_CBC_SUMMARY_TEMPLATES
where set_of_books_id = g_sob_id
and nvl(MIG_RESULT_CODE,'F') <> 'T';
Select * from GL_SUMMARY_BC_OPTIONS
where template_id = c_template_id;
select chart_of_accounts_id into v_chart_of_accounts_id from gl_ledgers where ledger_id = g_cbc_ledger_id;
/* Inserting template information in GL_SUMMARY_TEMPLATES for which setup is made in CBC */
For i in template_cur
loop
v_template_id := GL_SUMMARY_TEMPLATES_PKG.get_unique_id;
select * into gl_temp_rec from GL_SUMMARY_TEMPLATES where template_id = i.template_id;
INSERT INTO GL_SUMMARY_TEMPLATES(
template_id,
ledger_id,
status,
last_update_date,
last_updated_by,
template_name,
concatenated_description,
account_category_code,
max_code_combination_id,
start_actuals_period_name,
created_by,
creation_date,
last_update_login,
segment1_type,
segment2_type,
segment3_type,
segment4_type,
segment5_type,
segment6_type,
segment7_type,
segment8_type,
segment9_type,
segment10_type,
segment11_type,
segment12_type,
segment13_type,
segment14_type,
segment15_type,
segment16_type,
segment17_type,
segment18_type,
segment19_type,
segment20_type,
segment21_type,
segment22_type,
segment23_type,
segment24_type,
segment25_type,
segment26_type,
segment27_type,
segment28_type,
segment29_type,
segment30_type,
description,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
context)
VALUES (
V_Template_Id,
g_cbc_Ledger_id,
'A',
gl_temp_rec.Last_Update_Date,
gl_temp_rec.Last_Updated_By,
gl_temp_rec.Template_Name,
gl_temp_rec.Concatenated_Description,
gl_temp_rec.Account_Category_Code,
gl_temp_rec.MAX_Code_Combination_Id,
gl_temp_rec.Start_Actuals_Period_Name,
gl_temp_rec.Created_By,
gl_temp_rec.Creation_Date,
gl_temp_rec.Last_Update_Login,
gl_temp_rec.Segment1_Type,
gl_temp_rec.Segment2_Type,
gl_temp_rec.Segment3_Type,
gl_temp_rec.Segment4_Type,
gl_temp_rec.Segment5_Type,
gl_temp_rec.Segment6_Type,
gl_temp_rec.Segment7_Type,
gl_temp_rec.Segment8_Type,
gl_temp_rec.Segment9_Type,
gl_temp_rec.Segment10_Type,
gl_temp_rec.Segment11_Type,
gl_temp_rec.Segment12_Type,
gl_temp_rec.Segment13_Type,
gl_temp_rec.Segment14_Type,
gl_temp_rec.Segment15_Type,
gl_temp_rec.Segment16_Type,
gl_temp_rec.Segment17_Type,
gl_temp_rec.Segment18_Type,
gl_temp_rec.Segment19_Type,
gl_temp_rec.Segment20_Type,
gl_temp_rec.Segment21_Type,
gl_temp_rec.Segment22_Type,
gl_temp_rec.Segment23_Type,
gl_temp_rec.Segment24_Type,
gl_temp_rec.Segment25_Type,
gl_temp_rec.Segment26_Type,
gl_temp_rec.Segment27_Type,
gl_temp_rec.Segment28_Type,
gl_temp_rec.Segment29_Type,
gl_temp_rec.Segment30_Type,
gl_temp_rec.Description,
gl_temp_rec.Attribute1,
gl_temp_rec.Attribute2,
gl_temp_rec.Attribute3,
gl_temp_rec.Attribute4,
gl_temp_rec.Attribute5,
gl_temp_rec.Attribute6,
gl_temp_rec.Attribute7,
gl_temp_rec.Attribute8,
gl_temp_rec.Context);
/* Inserting budgetary control options into GL_SUMMARY_BC_OPTIONS */
FOR BUDCTRL_OPTIONS IN bcoptions_cur(i.template_id)
LOOP
select budget_name into v_old_budget_name
from gl_budgets_v where budget_version_id = BUDCTRL_OPTIONS.funding_budget_version_id;
select budget_version_id into v_new_bud_ver_id
from gl_budgets_v where description like '%R12_MIG_'||v_old_budget_name;
GL_SUMMARY_BC_OPTIONS_PKG.Insert_Row(
X_Rowid => v_row_id,
X_Funds_Check_Level_Code => i.CBC_OVERRIDE ,
X_Dr_Cr_Code => BUDCTRL_OPTIONS.dr_cr_code,
X_Amount_Type => BUDCTRL_OPTIONS.amount_type,
X_Boundary_Code => BUDCTRL_OPTIONS.boundary_code,
X_Template_Id => v_template_id ,
X_Last_Update_Date => BUDCTRL_OPTIONS.last_update_date,
X_Last_Updated_By => BUDCTRL_OPTIONS.last_updated_by,
X_Created_By => BUDCTRL_OPTIONS.created_by,
X_Creation_Date => BUDCTRL_OPTIONS.creation_date,
X_Last_Update_Login => BUDCTRL_OPTIONS.last_update_login,
X_Funding_Budget_Version_Id => v_new_bud_ver_id
);
update igc_cbc_summary_templates set MIG_RESULT_CODE = 'T',
MIG_REQUEST_ID = fnd_global.conc_request_id
WHERE template_id = i.template_id;
/* Submitting "Add/Delete Summary Accounts" concurrent program */
BEGIN
req_id := fnd_request.submit_request(
'SQLGL',
'GLSTPM',
'',
'',
FALSE,
'A',
to_char(V_Template_Id),
to_char(g_cbc_ledger_id),
to_char(v_chart_of_accounts_id),
chr(0),
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '');
Raise_application_error(20030,'Add/Delete Summary Accounts concurrent request has failed');
SELECT Distinct
je_source
FROM IGC_CBC_JE_LINES cbl
WHERE set_of_books_id = g_sob_id
AND cbl.period_year = p_fiscal_year
AND mig_request_id is null
AND actual_flag = 'B'
AND detail_summary_code = 'D';
SELECT
'NEW' status
,g_cbc_ledger_id ledger_id
,igc.je_source user_je_source_name
,igc.je_category user_je_category_name
,igc.effective_date accounting_date
,igc.currency_code currency_code
,sysdate date_created
,l_user_id created_by
,'B' actual_flag
,igc.budget_version_id old_budget_version_id
,BV.BUDGET_VERSION_ID new_budget_version_id
,decode(igc.entered_dr,0,null,entered_dr) entered_dr
,decode(igc.entered_cr,0,null,entered_cr) entered_cr
,igc.period_name period_name
,igc.code_combination_id code_combination_id
,'MIG-'||cbc_je_batch_id reference1
,'R11i MIGRATION - '||cbc_je_batch_id||' - '||cbc_je_line_num reference5
,'R11i MIGRATION - '||cbc_je_batch_id||' - '||cbc_je_line_num reference6
,igc.cbc_je_batch_id reference21
,igc.cbc_je_line_num reference22
FROM igc_cbc_je_lines igc,
gl_budget_assignments asg,
GL_BUDORG_BC_OPTIONS boc,
GL_BUDGET_VERSIONS BV ,
gl_budgets bud,
gl_period_statuses per_f,
gl_period_statuses per_s
WHERE asg.range_id = boc.range_id
AND BV.BUDGET_VERSION_ID = BOC.FUNDING_BUDGET_VERSION_ID
AND bud.budget_name = BV.budget_name
AND asg.code_combination_id = igc.code_combination_id
AND per_s.ledger_id = bud.ledger_id
AND per_f.ledger_id = bud.ledger_id
AND per_s.application_id = 101
AND per_f.application_id = 101
AND per_s.period_name = bud.first_valid_period_name
AND per_f.period_name = bud.last_valid_period_name
AND igc.effective_date between per_s.start_date and per_f.end_date
AND bud.ledger_id = g_cbc_ledger_id
AND igc.set_of_books_id = g_sob_id
AND igc.period_year = p_fiscal_year
AND igc.mig_result_code IS NULL
AND igc.actual_flag = 'B'
AND igc.detail_summary_code = 'D'
AND igc.mig_request_id is NULL
AND igc.budget_version_id IS NOT NULL;
SELECT '1'
INTO l_dummy
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM igc_cbc_je_lines igc
where budget_version_id is NOT null
AND igc.actual_flag = 'B'
AND igc.DETAIL_SUMMARY_CODE = 'D'
AND igc.period_year = p_fiscal_year
AND set_of_books_id = g_sob_id
AND NOT EXISTS
( SELECT 1
FROM gl_budget_assignments asg,
GL_BUDORG_BC_OPTIONS boc,
GL_BUDGET_VERSIONS BV ,
gl_budgets bud,
gl_period_statuses per_f,
gl_period_statuses per_s
WHERE asg.range_id = boc.range_id
AND BV.BUDGET_VERSION_ID = BOC.FUNDING_BUDGET_VERSION_ID
AND bud.budget_name = BV.budget_name
AND asg.code_combination_id = igc.code_combination_id
AND per_s.ledger_id = bud.ledger_id
AND per_f.ledger_id = bud.ledger_id
AND per_s.application_id = 101
AND per_f.application_id = 101
AND per_s.period_name = bud.first_valid_period_name
AND per_f.period_name = bud.last_valid_period_name
AND igc.effective_date between per_s.start_date and per_f.end_date
AND bud.ledger_id = g_cbc_ledger_id
)
);
INSERT INTO GL_INTERFACE
(
status
,ledger_id
,user_je_source_name
,user_je_category_name
,accounting_date
,currency_code
,date_created
,created_by
,actual_flag
,budget_version_id
,entered_dr
,entered_cr
,period_name
,code_combination_id
,reference1
,reference5
,reference6
,reference21
,reference22
)
VALUES
(
l_tbl_bud_journals(i_ind).status
,l_tbl_bud_journals(i_ind).ledger_id
,l_tbl_bud_journals(i_ind).user_je_source_name
,l_tbl_bud_journals(i_ind).user_je_category_name
,l_tbl_bud_journals(i_ind).accounting_date
,l_tbl_bud_journals(i_ind).currency_code
,l_tbl_bud_journals(i_ind).date_created
,l_tbl_bud_journals(i_ind).created_by
,l_tbl_bud_journals(i_ind).actual_flag
,l_tbl_bud_journals(i_ind).new_budget_version_id
,l_tbl_bud_journals(i_ind).entered_dr
,l_tbl_bud_journals(i_ind).entered_cr
,l_tbl_bud_journals(i_ind).period_name
,l_tbl_bud_journals(i_ind).code_combination_id
,l_tbl_bud_journals(i_ind).reference1
,l_tbl_bud_journals(i_ind).reference5
,l_tbl_bud_journals(i_ind).reference6
,l_tbl_bud_journals(i_ind).reference21
,l_tbl_bud_journals(i_ind).reference22
);
Put_Debug_Msg(l_full_path, 'Inserted records into GL_INTERFACE table');
UPDATE igc_cbc_je_lines cbc
SET mig_result_code = 'P00'
,mig_request_id = l_request_id_current
WHERE set_of_books_id = g_sob_id
AND period_year = p_fiscal_year
AND mig_result_code IS NULL
AND actual_flag = 'B'
AND detail_summary_code = 'D'
AND mig_request_id is NULL
AND budget_version_id IS NOT NULL
AND je_source = l_tbl_cbc_source(j).je_source
AND EXISTS
(SELECT 1
FROM gl_je_lines l, gl_je_headers h, gl_je_batches b
WHERE h.je_batch_id = b.je_batch_id
AND h.je_header_id = l.je_header_id
AND h.je_source = l_tbl_cbc_source(j).je_source
AND h.ledger_id = g_cbc_ledger_id
AND l.reference_1 = to_char(cbc.cbc_je_batch_id)
AND l.reference_2 = to_char(cbc.cbc_je_line_num)
);
SELECT budget_name
INTO l_cbc_budget_name
FROM gl_budgets
WHERE description like '%R12_MIG_'||p_pri_budget_name;
SELECT meaning
INTO l_option_name
FROM igi_lookups
WHERE lookup_code = 'CBC'
AND lookup_type = 'GCC_DESCRIPTION';
SELECT sum(decode(closing_status,'O',1,0)),sum(1)
INTO l_open_count,l_tot_count
FROM gl_period_statuses
WHERE application_id = 101
AND period_year = p_fin_year
AND ledger_id = g_cbc_ledger_id
AND adjustment_period_flag = 'N';
SELECT distinct '1'
INTO l_dummy
FROM gl_access_set_ledgers acc, gl_ledgers lgr
WHERE acc.access_set_id = l_gl_data_access_set
AND lgr.ledger_id = acc.ledger_id
AND lgr.object_type_code = 'L'
AND acc.access_privilege_code in ('B','F')
AND lgr.ledger_id = g_cbc_ledger_id;
SELECT '1'
INTO l_dummy
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM igc_cbc_je_lines igc
where budget_version_id is NOT null
AND igc.actual_flag = 'E'
AND igc.DETAIL_SUMMARY_CODE = 'D'
AND igc.period_year = p_fin_year
AND set_of_books_id = g_sob_id
AND mig_request_id IS NULL
AND NOT EXISTS
( SELECT 1
FROM gl_budget_assignments asg,
GL_BUDORG_BC_OPTIONS boc,
GL_BUDGET_VERSIONS BV ,
gl_budgets bud,
gl_period_statuses per_f,
gl_period_statuses per_s
WHERE asg.range_id = boc.range_id
AND BV.BUDGET_VERSION_ID = BOC.FUNDING_BUDGET_VERSION_ID
AND bud.budget_name = BV.budget_name
AND asg.code_combination_id = igc.code_combination_id
AND per_s.ledger_id = bud.ledger_id
AND per_f.ledger_id = bud.ledger_id
AND per_s.application_id = 101
AND per_f.application_id = 101
AND per_s.period_name = bud.first_valid_period_name
AND per_f.period_name = bud.last_valid_period_name
AND igc.effective_date between per_s.start_date and per_f.end_date
AND bud.ledger_id = g_cbc_ledger_id
)
);
SELECT parent_request_id,
request_id,level
FROM fnd_concurrent_requests
CONNECT BY PRIOR request_id = parent_request_id
START with request_id = c_request_id
order by request_id;
SELECT concurrent_program_name,
user_concurrent_program_name
INTO l_conc_pr_name,
g_conc_pr_user
FROM fnd_concurrent_programs_vl
WHERE concurrent_program_id = l_conc_id;
SELECT period_name,
SUM(1) total_count,
SUM(decode(mig_request_id,fnd_global.CONC_REQUEST_ID,
decode(substr(mig_result_code,1,1),'P',1,0)
,0)) migrated_count,
SUM(decode(substr(mig_result_code,1,1),'P',0,1)) pending_count
FROM igc_cbc_je_lines
WHERE period_year = p_fiscal_year
AND set_of_books_id = g_sob_id
AND actual_flag = 'E'
AND DETAIL_SUMMARY_CODE = 'D'
group by period_name;
SELECT b.name,to_char(l.cbc_je_line_num) cbc_je_line_num,
gl.meaning
FROM igc_cbc_je_lines l,igc_cbc_je_batches b,
gl_lookups gl
WHERE b.cbc_je_batch_id = l.cbc_je_batch_id
AND gl.lookup_type LIKE 'FUNDS_CHECK_RESULT_CODE'
AND l.mig_result_code like 'F%'
AND gl.lookup_code = l.mig_result_code
AND l.period_year = p_fiscal_year
AND l.actual_flag = 'E';