[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT parent_level
INTO l_maximum_level
FROM pa_org_hierarchy_denorm
WHERE parent_organization_id = l_start_org_id
AND pa_org_use_type = 'REPORTING'
AND ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
AND NVL(org_id, -99) = l_org_id
AND ROWNUM = 1;
* The following cursor - selects a set of objects the parent organizations
* should have based on the objects its child organizations have.
*/
CURSOR cur_unique_objects_per_parent( p_level IN PLS_INTEGER
,p_balance_type_code IN pa_objects.balance_type_code%TYPE
)
IS
SELECT obj.object_type_code
,obj.org_util_category_id
,obj.work_type_id
,hier.parent_organization_id
FROM pa_objects obj
,pa_org_hierarchy_denorm hier
WHERE hier.child_organization_id = obj.expenditure_organization_id
AND hier.pa_org_use_type = 'REPORTING'
AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
AND hier.parent_level = p_level
AND hier.parent_level = hier.child_level + 1
AND NVL(hier.org_id,-99) = l_org_id
AND obj.expenditure_org_id = l_org_id
AND obj.balance_type_code = p_balance_type_code
AND obj.object_type_code IN ( l_obj_type_orgwt
,l_obj_type_orguc
,l_obj_type_org
)
AND obj.project_org_id = -1
AND obj.project_organization_id = -1
AND obj.project_id = -1
AND obj.task_id = -1
AND obj.person_id = -1
GROUP BY obj.object_type_code
,obj.org_util_category_id
,obj.work_type_id
,hier.parent_organization_id
;
* If it exists, do nothing. Else insert.
*/
SELECT 'X'
INTO l_dummy
FROM pa_objects obj
WHERE obj.object_type_code = l_object_type_code
AND obj.balance_type_code = p_balance_type_code
AND obj.work_type_id = l_work_type_id
AND obj.org_util_category_id = l_org_util_category_id
AND obj.expenditure_organization_id = l_parent_organization_id;
* If control comes here, object doesnt exists. Insert.
*/
INSERT
INTO pa_objects( object_id
,object_type_code
,balance_type_code
,project_org_id
,project_organization_id
,project_id
,task_id
,expenditure_org_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
VALUES( pa_objects_s.nextval
,l_object_type_code
,p_balance_type_code
,-1
,-1
,-1
,-1
,l_org_id
,l_parent_organization_id
,-1
,-1
,l_work_type_id
,l_org_util_category_id
,-1
,-1
,-1
,SYSDATE
,-1
,SYSDATE
,-1
,-1
,-1
,-1
,-1
,SYSDATE
);
* Cursor to insert tot_num records same as that of
* dir_num records.
*/
CURSOR cur_org_dir_balances_pagl( p_start_org_id IN pa_implementations_all.start_organization_id%TYPE
,p_balance_type_code IN pa_objects.balance_type_code%TYPE
,p_maximum_level IN PLS_INTEGER
,p_period_type IN VARCHAR2
,p_effective_start_period_num IN PLS_INTEGER
,p_org_id IN pa_implementations_all.org_id%TYPE
)
IS SELECT bal.object_id
,bal.object_type_code
,bal.period_name
,bal.period_year
,bal.quarter_or_month_number
,bal.amount_type_id
,bal.period_num
,bal.period_balance
FROM pa_org_hierarchy_denorm hier
,pa_summ_balances bal
,pa_objects obj
WHERE hier.parent_organization_id = p_start_org_id
AND hier.parent_level = p_maximum_level
AND NVL(hier.org_id,-99) = p_org_id
AND hier.pa_org_use_type = 'REPORTING'
AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
AND bal.object_id = obj.object_id
AND bal.version_id = -1
AND bal.period_num >= p_effective_start_period_num
AND bal.period_type = p_period_type
-- AND bal.period_set_name = l_period_set_name
AND bal.period_set_name = decode(bal.period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
AND bal.object_type_code = obj.object_type_code
AND obj.expenditure_organization_id = hier.child_organization_id
AND obj.expenditure_org_id = p_org_id
AND obj.balance_type_code = p_balance_type_code
AND obj.project_org_id = -1
AND obj.project_organization_id = -1
AND obj.project_id = -1
AND obj.task_id = -1
AND obj.assignment_id = -1
AND obj.person_id = -1
AND obj.object_type_code IN ( l_obj_type_orgwt
,l_obj_type_orguc
,l_obj_type_org
)
AND bal.amount_type_id IN ( l_org_dir_hrs_id
,l_org_dir_wtdhrs_org_id
,l_org_dir_prvhrs_id
,l_org_dir_prvwtdhrs_org_id
,l_org_dir_cap_id
,l_org_dir_reducedcap_id
);
IS SELECT MAX(obj1.object_id)
,obj.object_type_code
,bal.period_name
,MAX(bal.period_year)
,MAX(bal.quarter_or_month_number)
,bal.amount_type_id
,MAX(bal.period_num)
,SUM(bal.period_balance)
FROM pa_summ_balances bal
,pa_objects obj
,pa_objects obj1
,pa_org_hierarchy_denorm hier
WHERE obj1.object_type_code = obj.object_type_code
AND obj1.balance_type_code = obj.balance_type_code
AND obj1.project_org_id = obj.project_org_id
AND obj1.project_organization_id = obj.project_organization_id
AND obj1.project_id = obj.project_id
AND obj1.task_id = obj.task_id
AND obj1.expenditure_organization_id = hier.parent_organization_id
AND obj1.expenditure_org_id = obj.expenditure_org_id
AND obj1.assignment_id = obj.assignment_id
AND obj1.person_id = obj.person_id
AND obj1.org_util_category_id = obj.org_util_category_id
AND obj1.work_type_id = obj.work_type_id
AND obj.balance_type_code = p_balance_type_code
AND obj.project_org_id = -1
AND obj.project_organization_id = -1
AND obj.project_id = -1
AND obj.task_id = -1
AND obj.expenditure_org_id = p_org_id
AND obj.expenditure_organization_id = hier.child_organization_id
AND obj.assignment_id = -1
AND obj.person_id = -1
AND NVL(hier.org_id,-99) = p_org_id
AND hier.pa_org_use_type = 'REPORTING'
AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
AND hier.parent_level = p_level_number
AND hier.parent_level = hier.child_level + 1
AND bal.object_id = obj.object_id
AND bal.object_type_code = obj.object_type_code
AND bal.version_id = -1
AND bal.period_num >= p_effective_start_period_num
AND bal.period_type = p_period_type
-- AND bal.period_set_name = l_period_set_name
AND bal.period_set_name = decode(bal.period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
AND bal.amount_type_id IN ( l_org_tot_hrs_id
,l_org_tot_wtdhrs_org_id
,l_org_tot_prvhrs_id
,l_org_tot_prvwtdhrs_org_id
,l_org_tot_cap_id
,l_org_tot_reducedcap_id
)
AND obj.object_type_code IN ( l_obj_type_orgwt
,l_obj_type_orguc
,l_obj_type_org
)
GROUP BY hier.parent_organization_id
,obj.object_type_code
,obj.org_util_category_id
,obj.work_type_id
,bal.period_name
-- ,bal.global_exp_period_end_date
,bal.amount_type_id;
* Delete all total and sub-org numbers from pa_summ_balances.
*/
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.g_err_stage := '100:Deleting Total and Sub-org Records';
DELETE
FROM pa_summ_balances bal
WHERE bal.amount_type_id IN ( l_org_sub_hrs_id
,l_org_sub_wtdhrs_org_id
,l_org_sub_prvhrs_id
,l_org_sub_prvwtdhrs_org_id
,l_org_sub_cap_id
,l_org_sub_reducedcap_id
,l_org_tot_hrs_id
,l_org_tot_wtdhrs_org_id
,l_org_tot_prvhrs_id
,l_org_tot_prvwtdhrs_org_id
,l_org_tot_cap_id
,l_org_tot_reducedcap_id
)
AND bal.object_type_code IN ( l_obj_type_orgwt
,l_obj_type_orguc
,l_obj_type_org
)
AND bal.period_num >= p_effective_start_period_num
AND period_type = p_period_type
AND ROWNUM <= l_fetch_size
AND EXISTS ( SELECT NULL
FROM pa_objects obj
WHERE obj.balance_type_code = p_balance_type_code
AND obj.object_id = bal.object_id
AND obj.expenditure_org_id = l_org_id
);
PA_DEBUG.g_err_stage := '150:Deleted [' || TO_CHAR(l_rowcount) || '] records';
* Exit when no more records left to delete.
*/
IF (l_rowcount = 0 OR l_rowcount < l_fetch_size) THEN
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.g_err_stage := '200:All Total and Sub-org records deleted.';
* into plsql tables and then they are bulk inserted into
* the table.
* Since, we have already deleted all the total records,
* 'upsert' is NOT needed. ONLY insert.
*/
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.g_err_stage := '250:Opening Direct-number Cursor.';
INSERT
INTO pa_summ_balances( object_id
,version_id
,object_type_code
,period_type
,period_set_name
,period_name
,global_exp_period_end_date
,period_year
,quarter_or_month_number
,amount_type_id
,period_num
,unit_of_measure
,period_balance
,pvdr_currency_code
,pvdr_period_balance
)
VALUES( l_object_id_tab(i)
,-1
,l_object_type_code_tab(i)
,p_period_type
-- ,l_period_set_name
,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
,l_period_name_tab(i)
,l_dummy_date
,l_period_year_tab(i)
,l_quarter_or_month_number_tab(i)
,DECODE(l_amount_type_id_tab(i)
,l_org_dir_hrs_id,l_org_tot_hrs_id
,l_org_dir_wtdhrs_org_id, l_org_tot_wtdhrs_org_id
,l_org_dir_prvhrs_id, l_org_tot_prvhrs_id
,l_org_dir_prvwtdhrs_org_id, l_org_tot_prvwtdhrs_org_id
,l_org_dir_cap_id, l_org_tot_cap_id
,l_org_dir_reducedcap_id, l_org_tot_reducedcap_id
,-1 --is this ok??
)
,l_period_num_tab(i)
,l_unit_of_measure
,l_period_balance_tab(i)
,NULL
,NULL
);
PA_DEBUG.g_err_stage := '450:Inserted [' || SQL%ROWCOUNT || '] Total-number records';
* Commit if no. of records inserted is more than or
* equal to the fetch size.
*/
IF (l_this_commit_cycle >= l_commit_size) THEN
COMMIT;
END LOOP; -- End of loop to insert total number records.
* Insert the sub-org number records.
*/
FOR l_level IN 2 .. l_maximum_level
LOOP
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.g_err_stage := '550:Opening Sub-org Cursor for Level [' || l_level || ']';
INSERT
INTO pa_summ_balances( object_id
,version_id
,object_type_code
,period_type
,period_set_name
,period_name
,global_exp_period_end_date
,period_year
,quarter_or_month_number
,amount_type_id
,period_num
,unit_of_measure
,period_balance
,pvdr_currency_code
,pvdr_period_balance
)
VALUES( l_object_id_tab(i)
,-1
,l_object_type_code_tab(i)
,p_period_type
-- ,l_period_set_name
,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
,l_period_name_tab(i)
,l_dummy_date
,l_period_year_tab(i)
,l_quarter_or_month_number_tab(i)
,DECODE(l_amount_type_id_tab(i)
,l_org_tot_hrs_id ,l_org_sub_hrs_id
,l_org_tot_wtdhrs_org_id ,l_org_sub_wtdhrs_org_id
,l_org_tot_prvhrs_id ,l_org_sub_prvhrs_id
,l_org_tot_prvwtdhrs_org_id ,l_org_sub_prvwtdhrs_org_id
,l_org_tot_cap_id ,l_org_sub_cap_id
,l_org_tot_reducedcap_id ,l_org_sub_reducedcap_id
,-1
)
,l_period_num_tab(i)
,l_unit_of_measure
,l_sub_org_total_tab(i)
,NULL
,NULL
);
PA_DEBUG.g_err_stage := '700:Inserted [' || SQL%ROWCOUNT || '] Sub-org records';
* Update the tot_num records with tot_num = tot_num + sub_org
*/
FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
UPDATE pa_summ_balances bal
SET bal.period_balance = bal.period_balance + l_sub_org_total_tab(i)
WHERE bal.object_id = l_object_id_tab(i)
AND bal.version_id = -1
AND bal.object_type_code = l_object_type_code_tab(i)
AND bal.period_type = p_period_type
-- AND bal.period_set_name = l_period_set_name
AND bal.period_set_name = decode(bal.period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
AND bal.period_name = l_period_name_tab(i)
-- AND bal.global_exp_period_end_date = l_ge_period_end_date_tab(i)
AND bal.global_exp_period_end_date = l_dummy_date
AND bal.period_year = l_period_year_tab(i)
AND bal.quarter_or_month_number = l_quarter_or_month_number_tab(i)
AND bal.amount_type_id = l_amount_type_id_tab(i)
AND bal.period_num = l_period_num_tab(i)
AND bal.unit_of_measure = l_unit_of_measure
AND bal.pvdr_currency_code IS NULL
AND bal.pvdr_period_balance IS NULL;
PA_DEBUG.g_err_stage := '750:Updated [' || SQL%ROWCOUNT || '] Total Records with T=T+S';
* If UPDATE didnt' go thro for a particular combination, that means
* that that particular combination doesnt already exist in the table.
* so INSERT.
*/
FOR i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
LOOP
IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
/*
* Update didnt' go thro' so, INSERT.
*/
INSERT
INTO pa_summ_balances( object_id
,version_id
,object_type_code
,period_type
,period_set_name
,period_name
,global_exp_period_end_date
,period_year
,quarter_or_month_number
,amount_type_id
,period_num
,unit_of_measure
,period_balance
,pvdr_currency_code
,pvdr_period_balance
)
VALUES( l_object_id_tab(i)
,-1
,l_object_type_code_tab(i)
,p_period_type
-- ,l_period_set_name
,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
,l_period_name_tab(i)
,l_dummy_date
,l_period_year_tab(i)
,l_quarter_or_month_number_tab(i)
,l_amount_type_id_tab(i)
,l_period_num_tab(i)
,l_unit_of_measure
,l_sub_org_total_tab(i)
,NULL
,NULL
);
END LOOP; -- Loop to check whether the record was updated.
PA_DEBUG.g_err_stage := '800:After Inserting Total Records.';
* Commit if no. of records inserted is more than or
* equal to the fetch size.
*/
IF (l_this_commit_cycle >= l_commit_size) THEN
l_this_commit_cycle := 0;
END LOOP; -- End of loop to insert sub-org number records.
* Cursor to insert tot_num records same as that of
* dir_num records.
*/
CURSOR cur_org_dir_balances_ge( p_start_org_id IN pa_implementations_all.start_organization_id%TYPE
,p_balance_type_code IN pa_objects.balance_type_code%TYPE
,p_maximum_level IN PLS_INTEGER
,p_period_type IN VARCHAR2
,p_start_date IN DATE
,p_end_date IN DATE
,p_org_id IN pa_implementations_all.org_id%TYPE
)
IS SELECT bal.object_id
,bal.object_type_code
,bal.period_name
,bal.global_exp_period_end_date
,bal.period_year
,bal.quarter_or_month_number
,bal.amount_type_id
,bal.period_balance
FROM pa_org_hierarchy_denorm hier
,pa_summ_balances bal
,pa_objects obj
WHERE hier.parent_organization_id = p_start_org_id
AND hier.parent_level = p_maximum_level
AND NVL(hier.org_id,-99) = p_org_id
AND hier.pa_org_use_type = 'REPORTING'
AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
AND bal.object_id = obj.object_id
AND bal.version_id = -1
AND bal.global_exp_period_end_date >= p_start_date
AND bal.period_type = p_period_type
AND bal.object_type_code = obj.object_type_code
AND obj.expenditure_organization_id = hier.child_organization_id
AND obj.expenditure_org_id = p_org_id
AND obj.balance_type_code = p_balance_type_code
AND obj.project_org_id = -1
AND obj.project_organization_id = -1
AND obj.project_id = -1
AND obj.task_id = -1
AND obj.assignment_id = -1
AND obj.person_id = -1
AND obj.object_type_code IN ( l_obj_type_orgwt
,l_obj_type_orguc
,l_obj_type_org
)
AND bal.amount_type_id IN ( l_org_dir_hrs_id
,l_org_dir_wtdhrs_org_id
,l_org_dir_prvhrs_id
,l_org_dir_prvwtdhrs_org_id
,l_org_dir_cap_id
,l_org_dir_reducedcap_id
);
IS SELECT MAX(obj1.object_id)
,obj.object_type_code
,bal.period_name
,bal.global_exp_period_end_date
,MAX(bal.period_year)
,MAX(bal.quarter_or_month_number)
,bal.amount_type_id
,SUM(bal.period_balance)
FROM pa_summ_balances bal
,pa_objects obj
,pa_objects obj1
,pa_org_hierarchy_denorm hier
WHERE obj1.object_type_code = obj.object_type_code
AND obj1.balance_type_code = obj.balance_type_code
AND obj1.project_org_id = obj.project_org_id
AND obj1.project_organization_id = obj.project_organization_id
AND obj1.project_id = obj.project_id
AND obj1.task_id = obj.task_id
AND obj1.expenditure_organization_id = hier.parent_organization_id
AND obj1.expenditure_org_id = obj.expenditure_org_id
AND obj1.assignment_id = obj.assignment_id
AND obj1.person_id = obj.person_id
AND obj1.org_util_category_id = obj.org_util_category_id
AND obj1.work_type_id = obj.work_type_id
AND obj.balance_type_code = p_balance_type_code
AND obj.project_org_id = -1
AND obj.project_organization_id = -1
AND obj.project_id = -1
AND obj.task_id = -1
AND obj.expenditure_org_id = p_org_id
AND obj.expenditure_organization_id = hier.child_organization_id
AND obj.assignment_id = -1
AND obj.person_id = -1
AND NVL(hier.org_id,-99) = p_org_id
AND hier.pa_org_use_type = 'REPORTING'
AND hier.ORG_HIERARCHY_VERSION_ID = l_org_structure_version_id
AND hier.parent_level = p_level_number
AND hier.parent_level = hier.child_level + 1
AND bal.object_id = obj.object_id
AND bal.object_type_code = obj.object_type_code
AND bal.version_id = -1
AND bal.global_exp_period_end_date >= p_start_date
AND bal.period_type = p_period_type
AND bal.amount_type_id IN ( l_org_tot_hrs_id
,l_org_tot_wtdhrs_org_id
,l_org_tot_prvhrs_id
,l_org_tot_prvwtdhrs_org_id
,l_org_tot_cap_id
,l_org_tot_reducedcap_id
)
AND obj.object_type_code IN ( l_obj_type_orgwt
,l_obj_type_orguc
,l_obj_type_org
)
GROUP BY hier.parent_organization_id
,obj.object_type_code
,obj.org_util_category_id
,obj.work_type_id
,bal.period_name
,bal.global_exp_period_end_date
,bal.amount_type_id;
* Delete all total and sub-org numbers from pa_summ_balances.
*/
LOOP
DELETE
FROM pa_summ_balances bal
WHERE bal.amount_type_id IN ( l_org_sub_hrs_id
,l_org_sub_wtdhrs_org_id
,l_org_sub_prvhrs_id
,l_org_sub_prvwtdhrs_org_id
,l_org_sub_cap_id
,l_org_sub_reducedcap_id
,l_org_tot_hrs_id
,l_org_tot_wtdhrs_org_id
,l_org_tot_prvhrs_id
,l_org_tot_prvwtdhrs_org_id
,l_org_tot_cap_id
,l_org_tot_reducedcap_id
)
AND bal.object_type_code IN ( l_obj_type_orgwt
,l_obj_type_orguc
,l_obj_type_org
)
AND bal.global_exp_period_end_date >= p_start_date
AND bal.period_type = p_period_type
AND ROWNUM <= l_fetch_size
AND EXISTS ( SELECT NULL
FROM pa_objects obj
WHERE obj.balance_type_code = p_balance_type_code
AND obj.object_id = bal.object_id
AND obj.expenditure_org_id = l_org_id
);
* Exit when no more records left to delete.
*/
IF (l_rowcount = 0) THEN
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.g_err_stage := '200:All Total and Sub-org records deleted.';
PA_DEBUG.g_err_stage := '150:Deleted [' || TO_CHAR(l_rowcount) || '] records';
* and then do a bulk insert into the table.
* Since, we have already deleted all the total records,
* 'upsert' is NOT needed. ONLY insert.
*/
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.g_err_stage := '250:Opening Direct-number Cursor.';
INSERT
INTO pa_summ_balances( object_id
,version_id
,object_type_code
,period_type
,period_set_name
,period_name
,global_exp_period_end_date
,period_year
,quarter_or_month_number
,amount_type_id
,period_num
,unit_of_measure
,period_balance
,pvdr_currency_code
,pvdr_period_balance
)
VALUES( l_object_id_tab(i)
,-1
,l_object_type_code_tab(i)
,p_period_type
-- ,l_dummy_period_set_name
,decode(p_period_type, pa_rep_util_glob.G_PERIOD_TYPE_C.G_PA_C, l_pa_period_set_name, l_gl_period_set_name) -- bug 3434019
,l_period_name_tab(i)
,l_ge_period_end_date_tab(i)
,l_period_year_tab(i)
,l_quarter_or_month_number_tab(i)
,DECODE(l_amount_type_id_tab(i)
,l_org_dir_hrs_id,l_org_tot_hrs_id
,l_org_dir_wtdhrs_org_id, l_org_tot_wtdhrs_org_id
,l_org_dir_prvhrs_id, l_org_tot_prvhrs_id
,l_org_dir_prvwtdhrs_org_id, l_org_tot_prvwtdhrs_org_id
,l_org_dir_cap_id, l_org_tot_cap_id
,l_org_dir_reducedcap_id, l_org_tot_reducedcap_id
,-1 --is this ok??
)
,-1
,l_unit_of_measure
,l_period_balance_tab(i)
,NULL
,NULL
);
PA_DEBUG.g_err_stage := '450:Inserted [' || SQL%ROWCOUNT || '] Total-number records';
* Commit if no. of records inserted is more than or
* equal to the fetch size.
*/
IF (l_this_commit_cycle >= l_commit_size) THEN
COMMIT;
END LOOP; -- End of loop to insert total number records.
* Insert the sub-org number records.
*/
FOR l_level IN 2 .. l_maximum_level
LOOP
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.g_err_stage := '550:Opening Sub-org Cursor for Level [' || l_level || ']';
INSERT
INTO pa_summ_balances( object_id
,version_id
,object_type_code
,period_type
,period_set_name
,period_name
,global_exp_period_end_date
,period_year
,quarter_or_month_number
,amount_type_id
,period_num
,unit_of_measure
,period_balance
,pvdr_currency_code
,pvdr_period_balance
)
VALUES( l_object_id_tab(i)
,-1
,l_object_type_code_tab(i)
,p_period_type
,l_dummy_period_set_name
,l_period_name_tab(i)
,l_ge_period_end_date_tab(i)
,l_period_year_tab(i)
,l_quarter_or_month_number_tab(i)
,DECODE(l_amount_type_id_tab(i)
,l_org_tot_hrs_id ,l_org_sub_hrs_id
,l_org_tot_wtdhrs_org_id ,l_org_sub_wtdhrs_org_id
,l_org_tot_prvhrs_id ,l_org_sub_prvhrs_id
,l_org_tot_prvwtdhrs_org_id ,l_org_sub_prvwtdhrs_org_id
,l_org_tot_cap_id ,l_org_sub_cap_id
,l_org_tot_reducedcap_id ,l_org_sub_reducedcap_id
,-1 -- is this ok??
)
,-1
,l_unit_of_measure
,l_sub_org_total_tab(i)
,NULL
,NULL
);
PA_DEBUG.g_err_stage := '700:Inserted [' || SQL%ROWCOUNT || '] Sub-org records';
* Update the tot_num records with tot_num = tot_num + sub_org
* Some of the checks in the where clause may not be required.
*/
FORALL i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
UPDATE pa_summ_balances bal
SET bal.period_balance = bal.period_balance + l_sub_org_total_tab(i)
WHERE bal.object_id = l_object_id_tab(i)
AND bal.version_id = -1
AND bal.object_type_code = l_object_type_code_tab(i)
AND bal.period_type = p_period_type
AND bal.period_set_name = l_dummy_period_set_name
AND bal.period_name = l_period_name_tab(i)
AND bal.global_exp_period_end_date = l_ge_period_end_date_tab(i)
AND bal.period_year = l_period_year_tab(i)
AND bal.quarter_or_month_number = l_quarter_or_month_number_tab(i)
AND bal.amount_type_id = l_amount_type_id_tab(i)
AND bal.period_num = -1
AND bal.unit_of_measure = l_unit_of_measure
AND bal.pvdr_currency_code IS NULL
AND bal.pvdr_period_balance IS NULL;
PA_DEBUG.g_err_stage := '750:Updated [' || SQL%ROWCOUNT || '] Total Records with T=T+S';
* If UPDATE didnt' go thro for a particular combination, that means
* that that particular combination doesnt already exist in the table.
* so INSERT.
*/
FOR i IN l_object_id_tab.FIRST .. l_object_id_tab.LAST
LOOP
IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
/*
* Update didnt' go thro' so, INSERT.
*/
INSERT
INTO pa_summ_balances( object_id
,version_id
,object_type_code
,period_type
,period_set_name
,period_name
,global_exp_period_end_date
,period_year
,quarter_or_month_number
,amount_type_id
,period_num
,unit_of_measure
,period_balance
,pvdr_currency_code
,pvdr_period_balance
)
VALUES( l_object_id_tab(i)
,-1
,l_object_type_code_tab(i)
,p_period_type
,l_dummy_period_set_name
,l_period_name_tab(i)
,l_ge_period_end_date_tab(i)
,l_period_year_tab(i)
,l_quarter_or_month_number_tab(i)
,l_amount_type_id_tab(i)
,-1
,l_unit_of_measure
,l_sub_org_total_tab(i)
,NULL
,NULL
);
END LOOP; -- Loop to chek whether the record was updated.
PA_DEBUG.g_err_stage := '800:After Inserting Total Records.';
* Commit if no. of records inserted is more than or
* equal to the fetch size.
*/
IF (l_this_commit_cycle >= l_commit_size) THEN
l_this_commit_cycle := 0;
END LOOP; -- End of loop to insert sub-org number records.