[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/***Bug# 2933915:Cursor for selecting impacted cost bases for which :
****the organization/cost code has ready_to_compile_flag as 'Y' or 'X' i.e the multiplier is modified or deleted respectively in
pa_ind_cost_multipliers
OR
****G_MODULE ='NEW_ORG' i.e when we need to generate new compiled set ids in all the revisions for a new organization even when there
is no change to the burden schedule******************************/
CURSOR impacted_cost_bases(rate_sch_rev_id NUMBER)
IS
SELECT pcb.COST_BASE
FROM PA_COST_BASES pcb
WHERE pcb.COST_BASE_TYPE = INDIRECT_COST_CODE
AND EXISTS
(
SELECT 1 /* Bug# 4527736 */
FROM PA_COST_BASE_COST_CODES CBICC,
PA_IND_COST_MULTIPLIERS ICM,
PA_IND_RATE_SCH_REVISIONS IRSR
WHERE IRSR.IND_RATE_SCH_REVISION_ID = ICM.IND_RATE_SCH_REVISION_ID
AND IRSR.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id
AND (NVL(ICM.READY_TO_COMPILE_FLAG,'N') IN ('Y','X')
OR NVL(G_MODULE ,'XXX') = 'NEW_ORG')
AND IRSR.COST_PLUS_STRUCTURE = CBICC.COST_PLUS_STRUCTURE
AND CBICC.IND_COST_CODE = ICM.IND_COST_CODE
AND CBICC.COST_BASE = PCB.COST_BASE
AND CBICC.COST_BASE_TYPE = PCB.COST_BASE_TYPE );
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
/*2933915 :Modified the existing ind_cost_code_cursor to select for impacted cost bases ONLY and not for all the
cost bases as was earlier*/
/* Replaced this cursor with the below defined cursor for the bug 4527736
CURSOR ind_cost_code_cursor(x_base VARCHAR2) IS -- 2933915
SELECT
cbicc.cost_base_cost_code_id,
cbicc.cost_base,
cbicc.ind_cost_code,
cbicc.precedence
FROM pa_cost_base_cost_codes cbicc,
pa_ind_rate_sch_revisions irsr
WHERE irsr.ind_rate_sch_revision_id = rate_sch_rev_id
AND irsr.cost_plus_structure = cbicc.cost_plus_structure
AND cbicc.cost_base = x_base -- 2933915
AND cbicc.cost_base_type = INDIRECT_COST_CODE
ORDER BY
cbicc.cost_base, cbicc.precedence;
SELECT
cbicc.cost_base_cost_code_id,
cbicc.cost_base,
cbicc.ind_cost_code,
cbicc.precedence
FROM pa_cost_base_cost_codes cbicc
WHERE cbicc.cost_plus_structure = G_CP_STRUCTURE
AND cbicc.cost_base = x_base
AND cbicc.cost_base_type = INDIRECT_COST_CODE
ORDER BY
cbicc.cost_base, cbicc.precedence;
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
ready to compile flag i.e 'N','Y','X' respectively depending on whether the multiplier is deleted ,changed or not changed
for that ORG . BUT we also need to ensure that if EXPLICIT multipliers are defined for an org for ALL
cost codes belonging to AFFECTED cost bases then new CSID should not be generated for that ie it should not be recompiled
If multipliers are not found for ANY of the cost code then we should go ahead with compiling new one*/
BEGIN
SELECT /*+ FIRST_ROWS */
1
INTO org_override
FROM sys.dual WHERE EXISTS
(SELECT /*+ FIRST_ROWS */
1
FROM pa_ind_cost_multipliers
WHERE ind_rate_sch_revision_id = rate_sch_rev_id
AND organization_id = org_id
AND nvl(ready_to_compile_flag,'N') <> 'X') ;
SELECT /*+ ORDERED
INDEX(ose PER_ORG_STRUCTURE_ELEMENTS_FK4)
INDEX(ics PA_IND_COMPILED_SETS_N1) */
ics.ind_compiled_set_id
INTO compiled_set_id
FROM per_org_structure_elements ose,
pa_ind_compiled_sets ics
WHERE ose.organization_id_child = org_id
AND ose.org_structure_version_id = org_struc_ver_id
AND ose.organization_id_parent = ics.organization_id
AND ics.ind_rate_sch_revision_id = rate_sch_rev_id
--4527736
-- AND ics.cost_base = cost_base_rec.cost_base /*2933915*/
AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND ics.status = 'A';
INSERT INTO pa_ind_compiled_sets
(ind_compiled_set_id,
ind_rate_sch_revision_id,
organization_id,
cost_base, /*2933915*
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
status)
VALUES(compiled_set_id,
rate_sch_rev_id,
org_id,
--4527736
-- cost_base_rec.cost_base, /*2933915
G_IMPACTED_COST_BASES_TAB(i),
SYSDATE,
x_last_updated_by,
x_created_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
SYSDATE,
'A');
INSERT INTO pa_ind_compiled_sets
(ind_compiled_set_id,
ind_rate_sch_revision_id,
organization_id,
cost_base,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
status)
SELECT compiled_set_id,
rate_sch_rev_id,
org_id,
--4527736
-- cost_base_rec.cost_base,
G_IMPACTED_COST_BASES_TAB(i),
SYSDATE,
x_last_updated_by,
x_created_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
SYSDATE,
'A'
FROM DUAL
WHERE NOT EXISTS
( SELECT 1 from pa_ind_compiled_sets ics
WHERE ics.ind_rate_sch_revision_id =rate_sch_rev_id
AND ics.organization_id = org_id
AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND ics.status='A'
) ;
SELECT /*+ FIRST_ROWS */
1
INTO l_org_override
FROM sys.dual WHERE EXISTS
(SELECT /*+ FIRST_ROWS */
1
FROM pa_ind_cost_multipliers icm,
pa_ind_compiled_sets ics
WHERE icm.ind_rate_sch_revision_id =ics.ind_rate_sch_revision_id
AND icm.ind_rate_sch_revision_id = rate_sch_rev_id
AND icm.organization_id =ics.organization_id
AND icm.organization_id = org_id
--AND ics.cost_base = cost_base_rec.cost_base --4527736
AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND ics.status ='A'
AND icm.ind_cost_code =cost_code_rec.ind_cost_code
AND nvl(icm.ready_to_compile_flag,'N') <>'X'); /*Should not consider 'X' records as they are actually
deleted records */
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_org_override := 0;
SELECT pa_ind_compiled_sets_s.NEXTVAL into compiled_set_id FROM sys.dual;
SELECT multiplier
INTO ind_cost_multiplier
FROM
pa_ind_cost_multipliers
WHERE
organization_id = defined_org_id
AND ind_cost_code = icc_row.ind_cost_code
AND ind_rate_sch_revision_id = rate_sch_rev_id
AND nvl(ready_to_compile_flag,'N') <> 'X' ; /*3005954 :Multipliers of deleted(i.e marked for deletion
SELECT organization_id_parent
INTO defined_org_id
FROM per_org_structure_elements
WHERE
organization_id_child = defined_org_id
AND org_structure_version_id = org_struc_ver_id;
INSERT INTO pa_compiled_multipliers
(ind_compiled_set_id,
cost_base_cost_code_id,
cost_base,
ind_cost_code,
precedence,
compiled_multiplier,
multiplier,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(compiled_set_id,
icc_row.cost_base_cost_code_id,
icc_row.cost_base,
icc_row.ind_cost_code,
icc_row.precedence,
base * ind_cost_multiplier,
ind_cost_multiplier,
SYSDATE,
x_last_updated_by,
x_created_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
SYSDATE
);
/*Bug# 2933915 : Insert Compiled sets ids for organization_id/Cost_base combination .
Earlier CSID was inserted for organization.Now it has to be inserted for organization_id/Cost_base combination */
IF (old_cost_base is NULL) OR (icc_row.cost_base <> old_cost_base) THEN /*Bug 2933915*/
/*S.N. 4888548
INSERT INTO pa_ind_compiled_sets
(ind_compiled_set_id,
ind_rate_sch_revision_id,
organization_id,
cost_base, /*Bug# 2933915
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
status)
VALUES
(compiled_set_id,
rate_sch_rev_id,
org_id,
icc_row.cost_base, /*Bug# 2933915
SYSDATE,
x_last_updated_by,
x_created_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
SYSDATE,
'A'
);
INSERT INTO pa_ind_compiled_sets
(ind_compiled_set_id,
ind_rate_sch_revision_id,
organization_id,
cost_base,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
status)
SELECT
compiled_set_id,
rate_sch_rev_id,
org_id,
icc_row.cost_base,
SYSDATE,
x_last_updated_by,
x_created_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
SYSDATE,
'A'
FROM DUAL
WHERE NOT EXISTS
( SELECT 1 from pa_ind_compiled_sets ics
WHERE ics.ind_rate_sch_revision_id =rate_sch_rev_id
AND ics.organization_id = org_id
AND ics.cost_base =icc_row.cost_base
AND ics.status='A'
) ;
SELECT organization_id_child
FROM per_org_structure_elements
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = ver_id
START WITH organization_id_parent = org_id
AND org_structure_version_id = ver_id;
UPDATE pa_ind_rate_sch_revisions
SET
compiled_flag = 'Y',
compiled_date = SYSDATE
WHERE
ind_rate_sch_revision_id = rate_sch_rev_id;
SELECT irsr.ind_rate_sch_revision_id
FROM pa_ind_rate_sch_revisions irsr
WHERE irsr.compiled_flag = 'Y' -- revision has been compiled before
AND irsr.ready_to_compile_flag = 'Y'; -- compilation is not on hold
SELECT '1'
FROM pa_ind_compiled_sets cmp
WHERE cmp.organization_id = p_org_id
AND cmp.ind_rate_sch_revision_id = p_rev_id
AND status = 'A';
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
SELECT ind_rate_sch_revision_id, org_structure_version_id,cost_plus_structure,start_organization_id /*4590268*/
FROM pa_ind_rate_sch_revisions
WHERE compiled_flag = 'Y'
AND ready_to_compile_flag = 'Y'
AND org_structure_version_id in
(select org_structure_version_id
from per_org_structure_elements
where organization_id_child = l_org_id
or organization_id_parent = l_org_id);
SELECT level, organization_id_child organization_id
FROM per_org_structure_elements
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = ver_id
START WITH organization_id_parent = l_org_id
AND org_structure_version_id = ver_id
UNION ALL
SELECT 0,l_org_id organization_id FROM dual
ORDER BY 1;
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
UPDATE pa_ind_rate_sch_revisions
SET
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE
ind_rate_sch_revision_id = rev_struct_row.ind_rate_sch_revision_id;
/*Bug# 1851731:Commented the SELECT and INSERT below as they are redundant.*/
/*The code in SELECT and INSERT was carried over from R11.0 and introduced in*/
/*this procedure as part of code fix for bug# 1163654. But it is not required*/
/*here as code in R11i is restructured.*/
/*
* Get compiled set id of the parent.
*/
/*Bug# 1851731:
SELECT + ORDERED
INDEX(ose PER_ORG_STRUCTURE_ELEMENTS_FK4)
INDEX(ics PA_IND_COMPILED_SETS_N1)
ics.ind_compiled_set_id
INTO l_compiled_set_id
FROM per_org_structure_elements ose,
pa_ind_compiled_sets ics
WHERE ose.organization_id_child = l_org_id
AND ose.org_structure_version_id = l_org_struc_ver_id
AND ose.organization_id_parent = ics.organization_id
AND ics.ind_rate_sch_revision_id = rev_row.ind_rate_sch_revision_id
AND ics.status = 'A'; Commented for bug# 1851731*/
* Insert compiled set information
*/
/* Bug# 1851731 : INSERT INTO pa_ind_compiled_sets
(ind_compiled_set_id,
ind_rate_sch_revision_id,
organization_id,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
status)
VALUES(l_compiled_set_id,
rev_row.ind_rate_sch_revision_id,
l_org_id,
SYSDATE,
x_last_updated_by,
x_created_by,
SYSDATE,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
SYSDATE,
'A'); Commented for bug# 1851731 */
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
/*3055700 :cursor to select all the cost bases */
CURSOR all_cost_bases
IS
SELECT
distinct cost_base
FROM pa_cost_base_cost_codes
WHERE cost_plus_structure =l_cp_structure
AND cost_base_type = INDIRECT_COST_CODE ;
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
UPDATE pa_ind_rate_sch_revisions
SET
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE
--ind_rate_sch_revision_id = rate_sch_rev_id; 4527736
Inserting dummy enteries of 0 in pa_ind_cost_multipliers for Start Org and all the cost
bases when the version is compiled .
This has to be done when there exists no compiled set ids for ALL the cost bases with
status 'A' for start_org .
This will ensure that the compiled set ids are generated for all the orgs in hierarchy
and all the cost bases .
Subsequently any changes in multiplies will affect only impacted org and
impacted cost base as per enhancement .
*********************************************************************************************/
pa_cost_plus.get_cost_plus_structure(rate_sch_rev_id,
l_cp_structure,
status,
stage);
select 1
into l_check
from sys.dual
where exists(select 1
from pa_ind_compiled_sets
where ind_rate_sch_revision_id =G_RATE_SCH_REVISION_ID /* 4527736 */
and organization_id =start_org
and cost_base = base_rec.cost_base
and status ='A');
/*If explicit multipliers are not defined for start org for ALL cost codes then only insert*/
IF pa_cost_plus.check_for_explicit_multiplier(G_RATE_SCH_REVISION_ID ,start_org) =0 THEN /* 0 means not present*/
Begin
INSERT into pa_ind_cost_multipliers (ind_rate_sch_revision_id,
organization_id,
ind_cost_code,
multiplier,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
ready_to_compile_flag)
select G_RATE_SCH_REVISION_ID,
start_org,
cbicc.ind_cost_code,
0,
SYSDATE,
x_last_updated_by,
l_created_by,
SYSDATE,
x_last_update_login,
'Y'
from pa_cost_base_cost_codes cbicc
where cbicc.cost_plus_structure = G_CP_STRUCTURE /* 4527736 */
and cbicc.cost_base_type = INDIRECT_COST_CODE
and cbicc.ind_cost_code not in (select m.ind_cost_code
from pa_ind_cost_multipliers m
where m.ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID /* 4527736 */
and m.organization_id =start_org)
group by cbicc.ind_cost_code;
/*2933915 : FOR SELECTIVE DELETION AND OBSOLETION */
/***Added a LOOP for the processing to go for each TOP IMPACTED organization returned by the above procedure in org_tab
We are going to process for all the children of the top affected org **********/
IF org_tab.exists(1) THEN
FOR i in org_tab.first..org_tab.last
LOOP
/*4642011 if (lstatus = 0) then /*2933915*/
/*4642011 delete_rate_sch_revision(G_RATE_SCH_REVISION_ID,G_ORG_STRUC_VER_ID,org_tab(i),status,stage); --2933915
/************************MOVED ALL THIS to new procedure delete_rate_sch_revision()
-
if (status = 0) then
--
-- Remove redundant compiled sets and multipiers.
--
DELETE pa_compiled_multipliers
WHERE ind_compiled_set_id IN
(SELECT ind_compiled_set_id
FROM pa_ind_compiled_sets
WHERE ind_rate_sch_revision_id = rate_sch_rev_id);
DELETE pa_ind_compiled_sets
WHERE ind_rate_sch_revision_id = rate_sch_rev_id;
delete the records having ready_to_compile_flag as 'X' .
For reference :Ready_to_compile_flag 'X' records are actually deleted records but they were retained till this point of time for
processing and treating them as impacted records***/
/*3055700 :Deleting the dummy entries inserted earlier since by now processing is done*/
DELETE pa_ind_cost_multipliers
where ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID
and organization_id = start_org
and created_by = l_created_by ;
DELETE pa_ind_cost_multipliers
where ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID
and nvl(ready_to_compile_flag,'N') ='X';
UPDATE pa_ind_cost_multipliers
set ready_to_compile_flag ='N'
where ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID
and nvl(ready_to_compile_flag,'N') ='Y';
SELECT ind_rate_sch_revision_id
FROM pa_ind_rate_sch_revisions irsr
WHERE irsr.compiled_flag = 'N'
AND nvl(irsr.ready_to_compile_flag,'N') = 'Y'
AND EXISTS (Select 1
from pa_ind_cost_multipliers icm
WHERE icm.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND nvl(icm.ready_to_compile_flag,'N') in ('Y','X'));
Select additional columns
denom_raw_cost, Acct_raw_cost
denom_burdened_cost,Acct_burdened_cost
*/
SELECT expenditure_type,
raw_cost,
denom_raw_cost,
acct_raw_cost,
project_raw_cost,
quantity,
burden_cost,
denom_burdened_cost,
acct_burdened_cost,
project_burdened_cost, /* ProjCurr Changes */
projfunc_currency_code, /* ProjCurr Changes */
acct_currency_code,
denom_currency_code,
project_currency_code,
system_linkage_function
INTO exp_type,
direct_cost,
direct_cost_denom,
direct_cost_acct,
direct_cost_project,
quantity,
burden_cost,
burden_cost_denom,
burden_cost_acct,
burden_cost_project, /* ProjCurr Changes */
l_projfunc_currency_code, /* ProjCurr Changes */
l_acct_currency_code,
l_denom_currency_code,
l_project_currency_code,
system_linkage
FROM pa_expenditure_items_all
WHERE expenditure_item_id = exp_item_id;
SELECT ind_compiled_set_id
INTO compiled_set_id
FROM pa_ind_compiled_sets
WHERE ind_rate_sch_revision_id = rate_sch_rev_id
AND organization_id = org_id
AND cost_base = c_base /*Bug# 2933915*/
AND STATUS = 'A';
PROCEDURE populate_indirect_cost(update_count IN OUT NOCOPY NUMBER)
IS
-- Cursor definition
/*
Multi-Currency Related Changes:
Acct_Raw_Cost and Denom_Raw_Cost picked up; also the check is done on the basis
SELECT ITEM.Expenditure_Item_ID,
ITEM.Raw_Cost,
ITEM.Acct_Raw_Cost,
ITEM.Project_Raw_Cost, /* ProjCurr Changes */
ITEM.Denom_Raw_Cost,
ITEM.Denom_Burdened_Cost,
ITEM.Burden_Cost,
ITEM.Acct_burdened_Cost,
ITEM.Project_burdened_Cost,
ITEM.Transaction_Source,
ITEM.Quantity,
ITEM.Raw_Cost_Rate,
ITEM.System_Linkage_Function,
ITEM.cost_ind_compiled_set_id,
nvl(ITEM.net_zero_adjustment_flag,'N') net_zero_adjustment_flag,
TYPE.burden_amt_display_method,
ITEM.adjusted_expenditure_item_id -- Bug 3893837
FROM PA_Expenditure_Items ITEM,
--PA_TASKS TASK, /* Bug 3458139 */
PA_PROJECTS_ALL PROJ,
PA_PROJECT_TYPES_ALL TYPE
WHERE ITEM.Cost_Distributed_Flag = 'S'
AND ITEM.Denom_Raw_Cost IS NOT NULL
AND (ITEM.Denom_Burdened_Cost IS NULL
OR
ITEM.Acct_Burdened_Cost IS NULL
OR
ITEM.Burden_Cost IS NULL
OR
ITEM.Transferred_from_exp_item_id IS NOT NULL /*2217540*/
OR /* 2328366 */
EXISTS (SELECT 1
FROM PA_TRANSACTION_SOURCES PTS
WHERE PTS.Transaction_source = ITEM.Transaction_source
AND PTS.Allow_Burden_Flag = 'Y')
OR ITEM.cost_ind_compiled_set_id is null) /* 3008365 */
AND ITEM.Cost_Dist_Rejection_Code IS NULL
--AND ITEM.Task_ID = TASK.Task_ID /* Bug 3458139 */
--AND TASK.Project_ID = PROJ.Project_ID /* Bug 3458139 */
AND ITEM.Project_Id = PROJ.Project_Id /* Bug 3458139 */
AND PROJ.Project_Type = TYPE.Project_Type
/* AND nvl(TYPE.Org_Id, -99) = nvl(PROJ.Org_Id, -99) bug 5374745 */
AND TYPE.Org_id = PROJ.Org_id -- bug 5374745
AND TYPE.burden_cost_flag = 'Y'
-- AND ITEM.System_Linkage_Function NOT IN ('BTC','OT'); /* Commented for Bug#1946968 */
SELECT cost_ind_compiled_set_id,
-1 * Burden_Cost ,
-1 * Denom_Burdened_Cost,
-1 * Acct_Burdened_Cost ,
-1 * Project_burdened_Cost,
Burden_Cost_Rate
FROM pa_expenditure_items_all
WHERE expenditure_item_id = p_exp_item_id
AND Denom_Raw_Cost = -1 * p_denom_raw_cost
AND Denom_Burdened_Cost IS NOT NULL
AND Acct_Burdened_Cost IS NOT NULL
AND Burden_Cost IS NOT NULL ;
update_count := 0;
SELECT Allow_Burden_Flag
INTO l_allow_burden_flag
FROM PA_TRANSACTION_SOURCES
WHERE Transaction_Source = Exp_Item_Row.transaction_source;
Update Denom_burdened_cost and Acct_burdened_cost
*/
/*
Burdening related changes:
Set the value of that bucket which is null.
*/
-- Bug 3893837 : Moved the logic of deriving rates for net zero items in the starting.
-- Bug fixes 3617506 and 3834184 are obsoleted as its incorrect to
-- copy the same burden costs in case compiled_set_id is same or net zero is yes.
-- Reason : As per main query ,this code gets fired only if any of the burden costs are NULL/
-- compiled set is NULL.So in case burden costs are null ,compiled set is NOT NULL and
-- net zero is Yes then the burden costs should be copied from original item if
-- already costed else rederive .
-- Note : Please test all scenarios mentioned in bug 3893837 if this logic is modified in future.
UPDATE PA_Expenditure_Items
SET Burden_Cost_Rate = x_Burden_Cost_Rate,
Burden_Cost = total_burden_cost,
Denom_burdened_Cost = total_burden_cost_denom,
Acct_burdened_Cost = total_burden_cost_Acct,
Project_burdened_Cost = total_burden_cost_Project, /* ProjCurr Changes */
Cost_Ind_Compiled_Set_Id = compiled_set_id,
Ind_Cost_Dist_Rejection_Code = NULL,
cost_burden_distributed_flag = decode(l_api_call_reqd,'N','Z',cost_burden_distributed_flag) /*2450423*/
WHERE Expenditure_Item_ID = Exp_Item_Row.Expenditure_Item_ID;
update_count := update_count + 1;
Update Denom_burdened_cost and Acct_burdened_cost
*/
/*
Bug# 805725
Set cost_burden_distributed_flag to some impossible value ('Z')
*/
/*
Burdening related changes:
Set the value of that bucket which is null.
*/
UPDATE PA_Expenditure_Items
SET Burden_Cost_Rate = Raw_Cost_Rate,
Burden_Cost = NVL(Burden_Cost,Raw_Cost),
Denom_Burdened_Cost = NVL(Denom_Burdened_Cost,Denom_Raw_Cost),
Acct_Burdened_Cost = NVL(Acct_Burdened_Cost,Acct_Raw_Cost),
Project_Burdened_Cost = NVL(Project_Burdened_Cost,Project_Raw_Cost), /* epp */
Cost_Burden_Distributed_Flag = 'Z',
Cost_Ind_Compiled_Set_Id = NULL
WHERE Expenditure_Item_ID = Exp_Item_Row.Expenditure_Item_ID;
update_count := update_count + 1;
Update Denom_burdened_cost and Acct_burdened_cost
*/
/*
Burdening related changes:
Dont reset burdened cost.
*/
UPDATE PA_Expenditure_Items
SET Cost_Dist_Rejection_Code = reason
/*****************************
***** Burden_Cost_Rate = NULL,
***** Burden_Cost = NULL,
***** Denom_Burdened_Cost = NULL,
***** Acct_Burdened_Cost = NULL,
***** Cost_Ind_Compiled_Set_Id = NULL
******************************/
WHERE Expenditure_Item_ID = Exp_Item_Row.Expenditure_Item_ID;
SELECT SUM(PA_CURRENCY.ROUND_CURRENCY_AMT((direct_cost * icpm.compiled_multiplier)))
into indirect_cost_sum
FROM pa_ind_compiled_sets ics,
pa_compiled_multipliers icpm
WHERE
ics.ind_rate_sch_revision_id = rate_sch_rev_id
AND ics.organization_id = org_id
AND ics.status = 'A'
AND ics.ind_compiled_set_id =
icpm.ind_compiled_set_id
AND ics.cost_base =icpm.cost_base /*Bug# 2933915*/
AND icpm.cost_base = c_base;
SELECT SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost * icpm.compiled_multiplier),
l_projfunc_currency_code)),
SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost_denom * icpm.compiled_multiplier),
l_denom_currency_code)),
SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost_acct * icpm.compiled_multiplier),
l_acct_currency_code)),
SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost_project * icpm.compiled_multiplier),
l_project_currency_code))
into indirect_cost_sum,
indirect_cost_denom_sum,
indirect_cost_acct_sum,
indirect_cost_project_sum
FROM pa_ind_compiled_sets ics,
pa_compiled_multipliers icpm
WHERE
ics.ind_rate_sch_revision_id = rate_sch_rev_id
AND ics.organization_id = org_id
AND ics.status = 'A'
AND ics.ind_compiled_set_id = icpm.ind_compiled_set_id
AND ics.cost_base =icpm.cost_base /*Bug# 2933915*/
AND icpm.cost_base = c_base;
SELECT TYPE.burden_amt_display_method
FROM PA_TASKS TASK,
PA_PROJECTS_ALL PROJ,
PA_PROJECT_TYPES_ALL TYPE
WHERE
TASK.Task_ID = l_task_id
AND TASK.Project_ID = PROJ.Project_ID
AND PROJ.Project_Type = TYPE.Project_Type
/* AND nvl(TYPE.Org_Id, -99) = nvl(PROJ.Org_Id, -99) bug 5374745 */
AND TYPE.Org_Id = PROJ.Org_id -- bug 5374745
AND TYPE.burden_cost_flag = 'Y';
select org_structure_version_id,start_organization_id
into x_org_struc_ver_id,x_start_org
from pa_ind_rate_sch_revisions
where ind_rate_sch_revision_id = p_sch_rev_id;
SELECT irs.ind_rate_sch_id,
t.cost_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = t_id
AND t.task_id = irs.task_id
AND irs.cost_ovr_sch_flag = 'Y';
SELECT irs.ind_rate_sch_id,
t.rev_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = t_id
AND t.task_id = irs.task_id
AND irs.rev_ovr_sch_flag = 'Y';
SELECT irs.ind_rate_sch_id,
t.inv_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = t_id
AND t.task_id = irs.task_id
AND irs.inv_ovr_sch_flag = 'Y';
SELECT irs.ind_rate_sch_id,
p.cost_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks t,
pa_projects_all p,
pa_ind_rate_schedules irs
WHERE t.task_id = t_id
AND t.project_id = p.project_id
AND t.project_id = irs.project_id
AND irs.cost_ovr_sch_flag = 'Y'
AND irs.task_id is null;
SELECT irs.ind_rate_sch_id,
p.rev_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks t,
pa_projects_all p,
pa_ind_rate_schedules irs
WHERE t.task_id = t_id
AND t.project_id = p.project_id
AND t.project_id = irs.project_id
AND irs.rev_ovr_sch_flag = 'Y'
AND irs.task_id is null;
SELECT irs.ind_rate_sch_id,
p.inv_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks t,
pa_projects_all p,
pa_ind_rate_schedules irs
WHERE t.task_id = t_id
AND t.project_id = p.project_id
AND t.project_id = irs.project_id
AND irs.inv_ovr_sch_flag = 'Y'
AND irs.task_id is null;
SELECT cost_ind_rate_sch_id,
cost_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks
WHERE task_id = t_id;
SELECT rev_ind_rate_sch_id,
rev_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks
WHERE task_id = t_id;
SELECT inv_ind_rate_sch_id,
inv_ind_sch_fixed_date
INTO sch_id,
sch_fixed_date
FROM pa_tasks
WHERE task_id = t_id;
SELECT task_id,
expenditure_item_date
INTO t_id,
exp_item_date
FROM pa_expenditure_items_all
WHERE expenditure_item_id = exp_item_id;
SELECT
cbet.cost_base
INTO c_base
FROM
pa_cost_base_exp_types cbet
WHERE
cbet.cost_plus_structure = cp_structure
AND cbet.expenditure_type = exp_type
AND cbet.cost_base_type = INDIRECT_COST_CODE;
SELECT
cost_plus_structure
INTO cp_structure
FROM
pa_ind_rate_sch_revisions
WHERE
ind_rate_sch_revision_id = rate_sch_rev_id;
SELECT override_to_organization_id
INTO organization_id
FROM pa_expenditure_items_all
WHERE expenditure_item_id = exp_item_id;
SELECT incurred_by_organization_id
INTO organization_id
FROM pa_expenditures_all exp,
pa_expenditure_items_all exp_item
WHERE exp_item.expenditure_item_id = exp_item_id
AND exp_item.expenditure_id = exp.expenditure_id
AND ( (exp_item.org_id is null) or (exp_item.org_id = exp.org_id));
SELECT ics.ind_compiled_set_id
INTO compiled_set_id
FROM pa_ind_compiled_sets ics
WHERE ics.ind_rate_sch_revision_id = rate_sch_rev_id
AND ics.organization_id = org_id
AND cost_base =c_base /*2933915*/
AND ics.status = 'A';
SELECT ind_rate_schedule_type
INTO x_ind_rate_schedule_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = sch_id;
SELECT PERIOD.end_date
INTO base_date
FROM GL_Period_Statuses PERIOD,
PA_Implementations IMP
WHERE PERIOD.Application_ID = 101
AND PERIOD.Set_Of_Books_ID = IMP.Set_Of_Books_ID
AND PERIOD.ADJUSTMENT_PERIOD_FLAG = 'N'
AND TRUNC(exp_item_date) BETWEEN
TRUNC(PERIOD.start_date) and TRUNC(PERIOD.end_date);*/
SELECT irsr.ind_rate_sch_revision_id
INTO rate_sch_rev_id
FROM pa_ind_rate_sch_revisions irsr
WHERE irsr.ind_rate_sch_id = sch_id
AND TRUNC(base_date) BETWEEN
TRUNC(irsr.start_date_active) AND
TRUNC(NVL(irsr.end_date_active, base_date))
AND irsr.ind_rate_sch_revision_type = 'A';
SELECT irsr.ind_rate_sch_revision_id
INTO rate_sch_rev_id
FROM pa_ind_rate_sch_revisions irsr
WHERE irsr.ind_rate_sch_id = sch_id
AND TRUNC(base_date) BETWEEN
TRUNC(irsr.start_date_active) AND
TRUNC(NVL(irsr.end_date_active, base_date));
SELECT 1 INTO dummy FROM SYS.DUAL
WHERE NOT EXISTS
(SELECT 1
FROM PA_IND_COMPILED_SETS ICS
WHERE ICS.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id
AND EXISTS
(SELECT ITEM.COST_IND_COMPILED_SET_ID
FROM PA_EXPENDITURE_ITEMS_ALL ITEM
WHERE ICS.IND_COMPILED_SET_ID = ITEM.COST_IND_COMPILED_SET_ID
UNION ALL
SELECT ITEM.REV_IND_COMPILED_SET_ID
FROM PA_EXPENDITURE_ITEMS_ALL ITEM
WHERE ICS.IND_COMPILED_SET_ID = ITEM.REV_IND_COMPILED_SET_ID
UNION ALL
SELECT ITEM.TP_IND_COMPILED_SET_ID
FROM PA_EXPENDITURE_ITEMS_ALL ITEM
WHERE ICS.IND_COMPILED_SET_ID = ITEM.TP_IND_COMPILED_SET_ID
UNION ALL
SELECT ITEM.INV_IND_COMPILED_SET_ID
FROM PA_EXPENDITURE_ITEMS_ALL ITEM
WHERE ICS.IND_COMPILED_SET_ID = ITEM.INV_IND_COMPILED_SET_ID
)
);
SELECT ind_rate_sch_revision_id
FROM pa_ind_rate_sch_revisions
WHERE cost_plus_structure = structure;
SELECT cost_base,
cost_base_type,
ind_cost_code,
precedence
FROM pa_cost_base_cost_codes
WHERE cost_plus_structure = source;
SELECT cost_base,
cost_base_type,
expenditure_type
FROM pa_cost_base_exp_types
WHERE cost_plus_structure = source;
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT cost_plus_structure_type
INTO structure_type
FROM pa_cost_plus_structures
WHERE cost_plus_structure = destination;
SELECT pa_cost_base_cost_codes_s.nextval into cbicc_id FROM sys.dual;
INSERT INTO pa_cost_base_cost_codes
(cost_base_cost_code_id,
cost_plus_structure,
cost_base,
cost_base_type,
ind_cost_code,
precedence,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES
(cbicc_id,
destination,
icc_row.cost_base,
icc_row.cost_base_type,
icc_row.ind_cost_code,
NVL(icc_precedence,icc_row.precedence),
SYSDATE,
x_last_updated_by,
SYSDATE,
x_created_by,
x_last_update_login);
INSERT INTO pa_cost_base_exp_types
(cost_plus_structure,
cost_base,
cost_base_type,
expenditure_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
VALUES
(destination,
et_row.cost_base,
et_row.cost_base_type,
et_row.expenditure_type,
SYSDATE,
x_last_updated_by,
SYSDATE,
x_created_by,
x_last_update_login);
/****PA L Enhancement :Modified this procedure to mark the eis selectively on the
basis of impacted cost bases ****************************/
procedure mark_impacted_exp_items(rate_sch_rev_id IN number,
status IN OUT NOCOPY number,
stage IN OUT NOCOPY number)
is
--
-- Local variables
--
sch_id number;
GMS_INSERT_ENC_ITEM_ERROR exception; --Bug 5726575
SELECT
DISTINCT cbicc.cost_base ,cbicc.cost_plus_structure
FROM pa_cost_base_cost_codes cbicc,
pa_ind_cost_multipliers icm
WHERE icm.ind_rate_sch_revision_id = rate_sch_rev_id --3054111
AND (nvl(icm.ready_to_compile_flag,'N') in ('Y','X') OR nvl(G_MODULE,'XXX') ='NEW_ORG')
AND cbicc.cost_plus_structure = l_cp_structure --3054111
AND cbicc.ind_cost_code = icm.ind_cost_code
AND cbicc.cost_base_type = INDIRECT_COST_CODE ;
SELECT DISTINCT ind_compiled_set_id
FROM pa_ind_compiled_sets
WHERE ind_rate_sch_revision_id = rate_sch_rev_id
AND status = 'H';
SELECT DISTINCT ICS.ind_compiled_set_id
FROM pa_ind_compiled_sets ICS
WHERE ICS.ind_rate_sch_revision_id = rate_sch_rev_id
AND ICS.cost_base = c_base
AND ICS.status = 'H';
SELECT /*+ ORDERED
INDEX(irsr PA_IND_RATE_SCH_REVISIONS_N1) */
DISTINCT ics.ind_compiled_set_id
FROM pa_ind_rate_sch_revisions irsr,
pa_ind_compiled_sets ics
WHERE ics.status = 'A'
AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND irsr.ind_rate_sch_id = sch_id
AND irsr.start_date_active < l_start_date
AND irsr.ind_rate_sch_revision_type <> 'A';
SELECT /*+ ORDERED */
DISTINCT ics.ind_compiled_set_id
FROM pa_ind_rate_sch_revisions irsr,
pa_ind_compiled_sets ics
WHERE ics.status = 'A'
AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND irsr.actual_sch_revision_id = rate_sch_rev_id;
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT ind_rate_sch_id, start_date_active, end_date_active,
ind_rate_sch_revision_type,cost_plus_structure
INTO sch_id, l_start_date, l_end_date, rev_type,l_cp_structure
FROM pa_ind_rate_sch_revisions irsr
WHERE ind_rate_sch_revision_id = rate_sch_rev_id;
| This update handles the following cases. |
| o [Cost/TP] Same and Separate line burdening transactions - when enhanced burdening |
| profile option is not enabled. |
| o [Cost/TP] Same line burdening transactions when enahanced burdening profile option |
| is enabled. |
| o [Revenue] Capital Projects with revenue based on burdened cost - for same line |
| burdening transactions. |
+======================================================================================*/
G_EXPENDITURE_ITEM_ID_TAB.DELETE; /*4456789*/
G_ADJ_TYPE_TAB.DELETE; /*4456789*/
UPDATE pa_expenditure_items_all ITEM
SET ITEM.cost_distributed_flag =
DECODE(ITEM.cost_distributed_flag, 'Y',
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
'N',ITEM.cost_distributed_flag), ITEM.cost_distributed_flag),
ITEM.revenue_distributed_flag =
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(pa_utils2.get_capital_cost_type_code(ITEM.project_id),'B', 'N',ITEM.revenue_distributed_flag)
,ITEM.revenue_distributed_flag),
ITEM.adjustment_type =
DECODE(ITEM.cost_distributed_flag, 'Y',
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
'BURDEN_RECOMPILE',ITEM.adjustment_type),ITEM.adjustment_type),
ITEM.cost_burden_distributed_flag =
DECODE(ITEM.cost_distributed_flag, 'Y',
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
'N',ITEM.cost_burden_distributed_flag),ITEM.cost_burden_distributed_flag),
ITEM.last_update_date = SYSDATE,
ITEM.last_updated_by = x_last_updated_by,
ITEM.last_update_login = x_last_update_login,
ITEM.request_id = x_request_id,
ITEM.program_application_id = x_program_application_id,
ITEM.program_id = x_program_id,
ITEM.program_update_date = SYSDATE,
ITEM.project_burdened_cost =
DECODE(ITEM.cost_distributed_flag, 'Y',
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.project_burdened_cost), ITEM.project_burdened_cost),
ITEM.denom_burdened_cost =
DECODE(ITEM.cost_distributed_flag, 'Y',
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.denom_burdened_cost), ITEM.denom_burdened_cost),
ITEM.acct_burdened_cost =
DECODE(ITEM.cost_distributed_flag, 'Y',
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_burdened_cost), ITEM.acct_burdened_cost),
ITEM.burden_cost =
DECODE(ITEM.cost_distributed_flag, 'Y',
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.burden_cost), ITEM.burden_cost),
ITEM.cc_bl_distributed_code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cc_cross_charge_code,'B',
'N',
ITEM.cc_bl_distributed_code),
ITEM.cc_bl_distributed_code),
ITEM.cc_ic_processed_code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cc_cross_charge_code,'I',
'N',
ITEM.cc_ic_processed_code),
ITEM.cc_ic_processed_code),
ITEM.Denom_Tp_Currency_Code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.denom_tp_currency_code),
ITEM.Denom_Transfer_Price =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.denom_transfer_price),
ITEM.Acct_Tp_Rate_Type =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_tp_rate_type),
ITEM.Acct_Tp_Rate_Date =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_tp_rate_date),
ITEM.Acct_Tp_Exchange_Rate =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_tp_exchange_rate),
ITEM.Acct_Transfer_Price =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_transfer_price),
ITEM.Projacct_Transfer_Price =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.projacct_transfer_price),
ITEM.Cc_Markup_Base_Code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.cc_markup_base_code),
ITEM.Tp_Base_Amount =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_base_amount),
ITEM.Tp_Bill_Rate =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_bill_rate),
ITEM.Tp_Bill_Markup_Percentage =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_bill_markup_percentage),
ITEM.Tp_Schedule_line_Percentage =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_schedule_line_percentage),
ITEM.Tp_Rule_percentage =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_rule_percentage)
WHERE ((ITEM.cost_ind_compiled_set_id = cs1_row.ind_compiled_set_id AND ITEM.cost_distributed_flag = 'Y')
OR ITEM.tp_ind_compiled_set_id = cs1_row.ind_compiled_set_id)
AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ITEM.expenditure_item_id,ITEM.task_id),'N') = 'N'
AND exists (select /*+ NO_UNNEST */ null
from pa_cost_base_exp_types cbet
--where cbet.cost_base = cost_base_rec.cost_base
where cbet.cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND cbet.cost_plus_structure = G_CP_STRUCTURE
AND cbet.cost_base_type = INDIRECT_COST_CODE
AND cbet.expenditure_type = ITEM.expenditure_type
)
/*3055700 :Added this exist clause for bug# 3016281 :to mark selectively if explicit multipliers
are defined for an org for all the cost codes */
/* 4527736
AND exists (SELECT NULL
FROM pa_expenditures_all exp
,pa_ind_compiled_sets ics
WHERE exp.expenditure_id = ITEM.expenditure_id
AND ((ics.ind_compiled_set_id = ITEM.cost_ind_compiled_set_id)
or (ics.ind_compiled_set_id = ITEM.tp_ind_compiled_set_id))
AND nvl(ITEM.override_to_organization_id,exp.incurred_by_organization_id) =ics.organization_id
AND ics.status = 'H'
* AND pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ) =0 -- Bug# 3134445
AND decode(rate_sch_rev_id,g_rate_sch_rev_id,decode(ics.organization_id,g_org_id,g_org_override,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id))
,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ))=0 -- Bug# 3134445 and Bug 3938479
) */
AND EXISTS ((SELECT NULL
FROM PA_EXPENDITURES_ALL EXP
,PA_IND_COMPILED_SETS ICS
WHERE EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID
AND (ICS.IND_COMPILED_SET_ID = ITEM.COST_IND_COMPILED_SET_ID)
AND NVL(ITEM.OVERRIDE_TO_ORGANIZATION_ID, EXP.INCURRED_BY_ORGANIZATION_ID) =ICS.ORGANIZATION_ID
AND ICS.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id /* Added for Bug 5683523 */
AND ICS.STATUS = 'H'
AND DECODE(rate_sch_rev_id ,g_rate_sch_rev_id ,DECODE(ICS.ORGANIZATION_ID,g_org_id ,g_org_override
, PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID))
, PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID ))=0
)
UNION ALL (SELECT NULL
FROM PA_EXPENDITURES_ALL EXP
,PA_IND_COMPILED_SETS ICS
WHERE EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID
AND (ICS.IND_COMPILED_SET_ID = ITEM.TP_IND_COMPILED_SET_ID)
AND NVL(ITEM.OVERRIDE_TO_ORGANIZATION_ID, EXP.INCURRED_BY_ORGANIZATION_ID) =ICS.ORGANIZATION_ID
AND ICS.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id /* Added for Bug 5683523 */
AND ICS.STATUS = 'H'
AND DECODE(rate_sch_rev_id ,g_rate_sch_rev_id ,DECODE(ICS.ORGANIZATION_ID,g_org_id ,g_org_override
, PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID))
, PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID ))=0
)
)
AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d') AND l_burden_profile ='N')
OR (pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s') AND l_burden_profile ='Y'))
RETURNING expenditure_item_id, decode(cs1_row.ind_compiled_set_id,ITEM.tp_ind_compiled_set_id,'UPDATE TP SCHEDULE REVISION','UPDATE COST SCHEDULE REVISION')
BULK COLLECT INTO G_EXPENDITURE_ITEM_ID_TAB,G_ADJ_TYPE_TAB;
raise GMS_INSERT_ENC_ITEM_ERROR;
| This update handles the following cases. |
| o [Cost/TP] Separate line burdening transactions when enahanced burdening profile |
| option is enabled. |
| o [Revenue] Capital Projects with revenue based on burdened cost - for separate line |
| burdening transactions. |
+======================================================================================*/
IF l_burden_profile ='Y' THEN
/*===============================================================+
| M - All pre-cost distributed transactions with separate line |
| burdening are set for BURDEN_RESUMMARIZE - if |
| Enhanced Burdening is SET. |
| Cost Distributed Flag is left untouched. |
| Earlier, supplier invoice transactions with budgetory |
| control were being routed via the distribution process. |
+===============================================================*/
l_row_count :=0;
G_EXPENDITURE_ITEM_ID_TAB.DELETE; /*4527736*/
G_ADJ_TYPE_TAB.DELETE; /*4527736*/
UPDATE pa_expenditure_items_all ITEM
SET ITEM.last_update_date = SYSDATE,
ITEM.last_updated_by = x_last_updated_by,
ITEM.last_update_login = x_last_update_login,
ITEM.request_id = x_request_id,
ITEM.program_application_id = x_program_application_id,
ITEM.program_id = x_program_id,
ITEM.program_update_date = SYSDATE,
/*************************
ITEM.cost_distributed_flag = DECODE(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id
, DECODE(ITEM.cost_distributed_flag , 'Y'
, DECODE(ITEM.system_linkage_function, 'VI'
, DECODE(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', 'N'
,ITEM.cost_distributed_flag)
,ITEM.cost_distributed_flag)
,ITEM.cost_distributed_flag)
,ITEM.cost_distributed_flag),
******************/
ITEM.revenue_distributed_flag =
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(pa_utils2.get_capital_cost_type_code(ITEM.project_id),'B', 'N',ITEM.revenue_distributed_flag)
,ITEM.revenue_distributed_flag),
ITEM.cc_bl_distributed_code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cc_cross_charge_code,'B',
'N',
ITEM.cc_bl_distributed_code),
ITEM.cc_bl_distributed_code),
ITEM.cc_ic_processed_code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cc_cross_charge_code,'I',
'N',
ITEM.cc_ic_processed_code),
ITEM.cc_ic_processed_code),
ITEM.Denom_Tp_Currency_Code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.denom_tp_currency_code),
ITEM.Denom_Transfer_Price =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.denom_transfer_price),
ITEM.Acct_Tp_Rate_Type =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_tp_rate_type),
ITEM.Acct_Tp_Rate_Date =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_tp_rate_date),
ITEM.Acct_Tp_Exchange_Rate =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_tp_exchange_rate),
ITEM.Acct_Transfer_Price =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.acct_transfer_price),
ITEM.Projacct_Transfer_Price =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.projacct_transfer_price),
ITEM.Cc_Markup_Base_Code =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.cc_markup_base_code),
ITEM.Tp_Base_Amount =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_base_amount),
ITEM.Tp_Bill_Rate =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_bill_rate),
ITEM.Tp_Bill_Markup_Percentage =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_bill_markup_percentage),
ITEM.Tp_Schedule_line_Percentage =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_schedule_line_percentage),
ITEM.Tp_Rule_percentage =
decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
NULL, ITEM.tp_rule_percentage),
ITEM.adjustment_type = DECODE(ITEM.cost_distributed_flag, 'Y', 'BURDEN_RESUMMARIZE', ITEM.adjustment_type)
/********************
,ITEM.denom_burdened_cost =
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cost_distributed_flag ,'Y'
,decode(ITEM.system_linkage_function, 'VI'
,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
ITEM.denom_burdened_cost), ITEM.denom_burdened_cost), ITEM.denom_burdened_cost),
ITEM.denom_burdened_cost)
,ITEM.acct_burdened_cost =
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cost_distributed_flag ,'Y'
,decode(ITEM.system_linkage_function, 'VI'
,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
ITEM.acct_burdened_cost), ITEM.acct_burdened_cost), ITEM.acct_burdened_cost),
ITEM.acct_burdened_cost)
,ITEM.project_burdened_cost =
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cost_distributed_flag ,'Y'
,decode(ITEM.system_linkage_function, 'VI'
,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
ITEM.project_burdened_cost), ITEM.project_burdened_cost), ITEM.project_burdened_cost),
ITEM.project_burdened_cost)
,ITEM.burden_cost =
decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
decode(ITEM.cost_distributed_flag ,'Y'
,decode(ITEM.system_linkage_function, 'VI'
,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
ITEM.burden_cost), ITEM.burden_cost), ITEM.burden_cost),
ITEM.burden_cost)
***************************/
WHERE (ITEM.tp_ind_compiled_set_id = cs1_row.ind_compiled_set_id OR
ITEM.cost_ind_compiled_set_id = cs1_row.ind_compiled_set_id )
AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ITEM.expenditure_item_id,ITEM.task_id),'N') = 'N'
AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
AND exists (select /*+ NO_UNNEST */ null
from pa_cost_base_exp_types cbet
-- where cbet.cost_base = cost_base_rec.cost_base -- 4527736
where cbet.cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND cbet.cost_plus_structure = l_cp_structure
AND cbet.cost_base_type = INDIRECT_COST_CODE
AND cbet.expenditure_type = ITEM.expenditure_type
)
/*Bug# 3055700 ::Added this exist clause back for bug# 3016281*/
AND exists (SELECT NULL
FROM pa_expenditures_all exp
,pa_ind_compiled_sets ics
WHERE exp.expenditure_id = ITEM.expenditure_id
AND ((ics.ind_compiled_set_id = ITEM.cost_ind_compiled_set_id)
or (ics.ind_compiled_set_id = ITEM.tp_ind_compiled_set_id))
AND nvl(ITEM.override_to_organization_id,exp.incurred_by_organization_id) =ics.organization_id
AND ics.status = 'H'
/*AND pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ) =0 /*3134445*/
AND decode(rate_sch_rev_id,g_rate_sch_rev_id,decode(ics.organization_id,g_org_id,g_org_override,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id))
,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ))=0 /*Bug# 3134445 and Bug 3938479*/
)
/*****************************************************************
AND exists (select 1
from pa_project_types_all pt,
pa_projects_all pp
where pp.project_id =ITEM.project_id
AND pp.project_type =pt.project_type
AND pt.burden_amt_display_method in ('D','d')
AND nvl(pt.org_id,-99) =nvl(pp.org_id,-99) )
*************************************************************/
AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('D','d')
RETURNING expenditure_item_id, decode(cs1_row.ind_compiled_set_id,ITEM.tp_ind_compiled_set_id,'UPDATE TP SCHEDULE REVISION','UPDATE COST SCHEDULE REVISION')
BULK COLLECT INTO G_EXPENDITURE_ITEM_ID_TAB,G_ADJ_TYPE_TAB;
,'UPDATE COST SCHEDULE REVISION'
,NULL
,NULL
,'UPDATE TP SCHEDULE REVISION'
,status
,stage
,G_EXPENDITURE_ITEM_ID_TAB
,G_ADJ_TYPE_TAB);
| This update handles the following cases. |
| o [Rev/Inv] Same and Separate line burdening transactions - irrespective of profile |
| option. |
+======================================================================================*/
stage := 400;
G_EXPENDITURE_ITEM_ID_TAB.DELETE; /*4527736*/
G_ADJ_TYPE_TAB.DELETE; /*4527736*/
UPDATE pa_expenditure_items_all ITEM
SET ITEM.revenue_distributed_flag = 'N',
ITEM.last_update_date = SYSDATE,
ITEM.last_updated_by = x_last_updated_by,
ITEM.last_update_login = x_last_update_login,
ITEM.request_id = x_request_id,
ITEM.program_application_id = x_program_application_id,
ITEM.program_id = x_program_id,
ITEM.program_update_date = SYSDATE
WHERE (ITEM.rev_ind_compiled_set_id = cs1_row.ind_compiled_set_id
OR ITEM.inv_ind_compiled_set_id = cs1_row.ind_compiled_set_id)
AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ITEM.expenditure_item_id,ITEM.task_id),'N') = 'N'
AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y' /* missing condition added for bug 4574721 */
AND EXISTS (select /*+ NO_UNNEST */ 1
from pa_cost_base_exp_types cbet
--where cbet.cost_base = cost_base_rec.cost_base
where cbet.cost_base = G_IMPACTED_COST_BASES_TAB(i)
and cbet.cost_plus_structure = l_cp_structure
and cbet.cost_base_type = INDIRECT_COST_CODE
and cbet.expenditure_type = ITEM.expenditure_type
)
/*Bug# 3055700 : Added this exist clause back for bug# 3016281*/
AND EXISTS (SELECT /*+ index(ics PA_IND_COMPILED_SETS_N6) */ NULL /*Added index hint for Bug 5683523 */
FROM pa_expenditures_all exp
,pa_ind_compiled_sets ics
WHERE exp.expenditure_id = ITEM.expenditure_id
AND ((ics.ind_compiled_set_id = ITEM.rev_ind_compiled_set_id)
or (ics.ind_compiled_set_id = ITEM.inv_ind_compiled_set_id))
AND nvl(ITEM.override_to_organization_id,exp.incurred_by_organization_id) =ics.organization_id
AND ics.ind_rate_sch_revision_id = rate_sch_rev_id /* Added for Bug 5683523 */
AND ics.status = 'H'
/*AND pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ) =0 /*Bug# 3134445*/
AND decode(rate_sch_rev_id,g_rate_sch_rev_id,decode(ics.organization_id,g_org_id,g_org_override,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id))
,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ))=0 /*Bug# 3134445 and Bug 3938479*/
)
RETURNING expenditure_item_id, decode(cs1_row.ind_compiled_set_id,ITEM.rev_ind_compiled_set_id,'UPDATE REV SCHEDULE REVISION','UPDATE INV SCHEDULE REVISION')
BULK COLLECT INTO G_EXPENDITURE_ITEM_ID_TAB,G_ADJ_TYPE_TAB;
,'UPDATE REV SCHEDULE REVISION'
,'UPDATE INV SCHEDULE REVISION'
,NULL
,status
,stage
,G_EXPENDITURE_ITEM_ID_TAB
,G_ADJ_TYPE_TAB);
*** merged with a pervious select
SELECT ind_rate_sch_id, start_date_active, end_date_active,
ind_rate_sch_revision_type
INTO sch_id, l_start_date, l_end_date, rev_type
FROM pa_ind_rate_sch_revisions irsr
WHERE ind_rate_sch_revision_id = rate_sch_rev_id;
when GMS_INSERT_ENC_ITEM_ERROR then --Bug 5726575
stage := 110;
GMS_INSERT_ENC_ITEM_ERROR exception; --Bug 5726575
INSERT_ADJ_ACTIVITY_ERROR exception;
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE cost_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.cost_ind_sch_fixed_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, cost_ind_sch_fixed_date))
AND nvl(ei.net_zero_adjustment_flag,'N') <>'Y'
/******** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N'; /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE rev_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND (task.rev_ind_sch_fixed_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, rev_ind_sch_fixed_date)))
AND nvl(ei.net_zero_adjustment_flag ,'N')<>'Y'
/********AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE inv_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND (task.inv_ind_sch_fixed_date BETWEEN --Bug 5917245Removed TRUNC
l_start_date AND
NVL(l_end_date, inv_ind_sch_fixed_date)))
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/***** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE cost_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.cost_ind_sch_fixed_date IS NULL)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/****** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE rev_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.rev_ind_sch_fixed_date IS NULL)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/****** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE inv_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.inv_ind_sch_fixed_date IS NULL)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/****** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE cost_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 removed TRUNC
l_start_date AND NVL(l_end_date, expenditure_item_date)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/****** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND (decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE rev_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/*****AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE inv_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/***** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915 */
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE cost_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT t1.task_id
FROM pa_project_types_all pt,
pa_projects_all p,
pa_tasks t1
WHERE pt.project_type = p.project_type
/* AND nvl(pt.org_id, -99) = nvl(p.org_id, -99) Bug 5374745 */
AND pt.org_id = p.org_id -- bug 5374745
AND p.project_id = t1.project_id
AND t1.cost_ind_sch_fixed_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, t1.rev_ind_sch_fixed_date)
AND t1.task_id = ei.task_id
AND pt.project_type_class_code = 'CAPITAL'
AND pt.capital_cost_type_code = 'B')
AND nvl(ei.net_zero_adjustment_flag,'N') <>'Y'
/**** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE cost_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT t1.task_id
FROM pa_project_types_all pt,
pa_projects_all p,
pa_tasks t1
WHERE pt.project_type = p.project_type
/* AND nvl(pt.org_id, -99) = nvl(p.org_id, -99) bug 5374745 */
AND pt.org_id = p.org_id -- bug 5374745
AND p.project_id = t1.project_id
AND t1.cost_ind_sch_fixed_date is NULL
AND t1.task_id = ei.task_id
AND pt.project_type_class_code = 'CAPITAL'
AND pt.capital_cost_type_code = 'B')
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/***** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
WHERE cost_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT t1.task_id
FROM pa_project_types_all pt,
pa_projects_all p,
pa_tasks t1
WHERE pt.project_type = p.project_type
/* AND nvl(pt.org_id, -99) = nvl(p.org_id, -99) Bug 5374745 */
AND pt.org_id = p.org_id -- bug 5374745
AND p.project_id = t1.project_id
AND t1.task_id = ei.task_id
AND pt.project_type_class_code = 'CAPITAL'
AND pt.capital_cost_type_code = 'B')
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/****** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei,
pa_system_linkages syslink,
pa_tasks task,
pa_projects_all proj
WHERE ei.tp_ind_compiled_set_id = compiled_set_id
AND ei.system_linkage_function = syslink.function
AND task.task_id = ei.task_id
AND (
( TRUNC(NVL(task.labor_tp_fixed_date, ei.expenditure_item_date)) BETWEEN
TRUNC(l_start_date) AND
TRUNC(NVL(l_end_date, NVL(task.labor_tp_fixed_date,ei.expenditure_item_date)))
AND
syslink.labor_non_labor_flag = 'Y')
OR
( TRUNC(NVL(task.nl_tp_fixed_date, ei.expenditure_item_date)) BETWEEN
TRUNC(l_start_date) AND
TRUNC(NVL(l_end_date, NVL(task.nl_tp_fixed_date,ei.expenditure_item_date)))
AND
syslink.labor_non_labor_flag = 'N')
)
AND nvl(ei.net_zero_adjustment_flag, 'N') <> 'Y'
AND proj.project_id = task.project_id
AND pa_project_stus_utils.Is_Project_Status_Closed(proj.project_status_code) <> 'Y';*/
SELECT EXPENDITURE_ITEM_ID
FROM PA_EXPENDITURE_ITEMS_ALL EI
WHERE tp_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT task_id
FROM pa_tasks task, pa_system_linkages syslink /*2933915 : ,pa_projects_all proj :Join with pa_projects is not required here */
WHERE task.task_id = ei.task_id
AND ei.system_linkage_function = syslink.function
/*AND task.project_id = proj.project_id 2933915*/
AND task.project_id = ei.project_id /*2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ) /*3059344*/
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
AND ((NVL(task.labor_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
l_start_date
AND NVL(l_end_date, NVL(task.labor_tp_fixed_date,ei.expenditure_item_date))
AND syslink.labor_non_labor_flag = 'Y')
OR (NVL(task.nl_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, NVL(task.nl_tp_fixed_date,ei.expenditure_item_date))
AND syslink.labor_non_labor_flag = 'N')
));
SELECT expenditure_item_id
FROM pa_expenditure_items_all ei
/* pa_tasks task -- Commented for Bug#3585192 */
/* pa_projects_all proj :2933915 :Redundant join and hence can be removed */
WHERE tp_ind_compiled_set_id = compiled_set_id
AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, ei.expenditure_item_date)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/**** AND ei.task_id = task.task_id
AND proj.project_id = task.project_id
AND pa_project_stus_utils.Is_Project_Status_Closed(proj.project_status_code) <> 'Y' ****2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
/***Bug 2933915 : Added the exists clause in this update to indicate that except profile ='Y' and display_method ='D' -for all
cases we will be marking ei for cost reprocessing */
/*If Burdening is on same ei then update adjustment_type as BURDEN_COMPILE else update it as BURDEN_RESUMMARIZE .*/
UPDATE pa_expenditure_items_all ei
SET cost_distributed_flag = 'N',
adjustment_type ='BURDEN_RECOMPILE', /*2933915*/
cost_burden_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE,
denom_burdened_cost = NULL,
project_burdened_cost = NULL,
acct_burdened_cost = NULL,
burden_cost = NULL
WHERE cost_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.cost_ind_sch_fixed_date BETWEEN
l_start_date AND
NVL(l_end_date, cost_ind_sch_fixed_date))
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/****** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*2933915*/
AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s','D','d') AND l_burden_profile ='N') /*Added for 2933915*/
OR (pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s') AND l_burden_profile ='Y'))
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
raise GMS_INSERT_ENC_ITEM_ERROR;
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE rev_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.rev_ind_sch_fixed_date BETWEEN --Bug#5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, rev_ind_sch_fixed_date))
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/****2933915 AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commenteed for bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE inv_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.inv_ind_sch_fixed_date BETWEEN --Bug#5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, inv_ind_sch_fixed_date))
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/***** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE cost_ind_compiled_set_id = compiled_set_id
AND EXISTS
(SELECT t1.task_id
FROM pa_project_types_all pt,
pa_projects_all p,
pa_tasks t1
WHERE pt.project_type = p.project_type
AND nvl(pt.org_id, -99) = nvl(p.org_id, -99)
AND p.project_id = t1.project_id
AND TRUNC(t1.cost_ind_sch_fixed_date) BETWEEN
TRUNC(l_start_date) AND
TRUNC(NVL(l_end_date, t1.rev_ind_sch_fixed_date))
AND t1.task_id = ei.task_id
AND pt.project_type_class_code = 'CAPITAL'
AND pt.capital_cost_type_code = 'B')
AND nvl(ei.net_zero_adjustment_flag,'N') <>'Y'
/**** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET cost_distributed_flag = 'N' ,
adjustment_type ='BURDEN_RECOMPILE', /*2933915*/
cost_burden_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE,
denom_burdened_cost = NULL,
project_burdened_cost = NULL,
acct_burdened_cost = NULL,
burden_cost = NULL
WHERE cost_ind_compiled_set_id = compiled_set_id
AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND NVL(l_end_date, ei.expenditure_item_date)
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.cost_ind_sch_fixed_date IS NULL)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/***** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' )
AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s','D','d') AND l_burden_profile ='N') /*Added for 2933915*/
OR (pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s') AND l_burden_profile ='Y')) ;
raise GMS_INSERT_ENC_ITEM_ERROR;
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE rev_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.rev_ind_sch_fixed_date IS NULL)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/*** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE inv_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT task_id
FROM pa_tasks task
WHERE task.task_id = ei.task_id
AND task.inv_ind_sch_fixed_date IS NULL)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/*** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE cost_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT t1.task_id
FROM pa_project_types_all pt,
pa_projects_all p,
pa_tasks t1
WHERE pt.project_type = p.project_type
AND nvl(pt.org_id, -99) = nvl(p.org_id, -99)
AND p.project_id = t1.project_id
AND t1.cost_ind_sch_fixed_date is NULL
AND t1.task_id = ei.task_id
AND pt.project_type_class_code = 'CAPITAL'
AND pt.capital_cost_type_code = 'B')
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/* AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
* updates and activity logging done for TP schedule change.
* Note: explain plan is fine in RBO, cant test it in CBO because of
* non-availability of volume data.
*/
/*
* Bug 4885396 : Moved 3 EI based checks from Exists subquery to the
* main query.
*/
UPDATE pa_expenditure_items_all ei
SET cc_bl_distributed_code =
decode( cc_cross_charge_code,'B',
'N',
cc_bl_distributed_code),
cc_ic_processed_code =
decode( cc_cross_charge_code,'I',
'N',
cc_ic_processed_code),
Denom_Tp_Currency_Code = NULL,
Denom_Transfer_Price = NULL,
Acct_Tp_Rate_Type = NULL,
Acct_Tp_Rate_Date = NULL,
Acct_Tp_Exchange_Rate = NULL,
Acct_Transfer_Price = NULL,
Projacct_Transfer_Price = NULL,
Cc_Markup_Base_Code = NULL,
Tp_Base_Amount = NULL,
Tp_Bill_Rate = NULL,
Tp_Bill_Markup_Percentage = NULL,
Tp_Schedule_line_Percentage = NULL,
Tp_Rule_percentage = NULL,
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE tp_ind_compiled_set_id = compiled_set_id
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y'
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' )
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
AND EXISTS
(SELECT task_id
FROM pa_tasks task, pa_system_linkages syslink /*, pa_projects_all proj :Redundant :2933915*/
WHERE task.task_id = ei.task_id
AND ei.system_linkage_function = syslink.function
/* AND task.project_id = proj.project_id Commented for 2933915*/
AND task.project_id = ei.project_id /*2933915*/
AND (
( NVL(task.labor_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, NVL(task.labor_tp_fixed_date,ei.expenditure_item_date))
AND
syslink.labor_non_labor_flag = 'Y')
OR
( NVL(task.nl_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, NVL(task.nl_tp_fixed_date,ei.expenditure_item_date))
AND
syslink.labor_non_labor_flag = 'N')
));
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET cost_distributed_flag = 'N' ,
adjustment_type ='BURDEN_RECOMPILE',
cost_burden_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE,
denom_burdened_cost = NULL,
project_burdened_cost = NULL,
acct_burdened_cost = NULL,
burden_cost = NULL
WHERE ei.cost_ind_compiled_set_id = compiled_set_id
AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND NVL(l_end_date, ei.expenditure_item_date)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/* AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s','D','d') AND l_burden_profile ='N') /*Added for 2933915*/
OR (pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s') AND l_burden_profile ='Y'))
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
/*2933915 :Added the exists clause in above update to indicate that except profile ='Y' and display_method ='D' -for all cases we will
be marking ei for cost reprocessing */
-- consider volume of expenditure items having the same compiled set id
COMMIT;
raise GMS_INSERT_ENC_ITEM_ERROR;
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE ( (rev_ind_compiled_set_id = compiled_set_id)
OR (inv_ind_compiled_set_id = compiled_set_id))
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/***** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
UPDATE pa_expenditure_items_all ei
SET revenue_distributed_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE cost_ind_compiled_set_id = compiled_set_id
AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, expenditure_item_date)
AND EXISTS
(SELECT t1.task_id
FROM pa_project_types_all pt,
pa_projects_all p,
pa_tasks t1
WHERE pt.project_type = p.project_type
AND nvl(pt.org_id, -99) = nvl(p.org_id, -99)
AND p.project_id = t1.project_id
AND t1.task_id = ei.task_id
AND pt.project_type_class_code = 'CAPITAL'
AND pt.capital_cost_type_code = 'B')
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/**** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
* updates and activity logging done for TP schedule change.
* Note: explain plan is fine in RBO, cant test it in CBO because of
* non-availability of volume data.
*/
UPDATE pa_expenditure_items_all ei
SET cc_bl_distributed_code =
decode( cc_cross_charge_code,'B',
'N',
cc_bl_distributed_code),
cc_ic_processed_code =
decode( cc_cross_charge_code,'I',
'N',
cc_ic_processed_code),
Denom_Tp_Currency_Code = NULL,
Denom_Transfer_Price = NULL,
Acct_Tp_Rate_Type = NULL,
Acct_Tp_Rate_Date = NULL,
Acct_Tp_Exchange_Rate = NULL,
Acct_Transfer_Price = NULL,
Projacct_Transfer_Price = NULL,
Cc_Markup_Base_Code = NULL,
Tp_Base_Amount = NULL,
Tp_Bill_Rate = NULL,
Tp_Bill_Markup_Percentage = NULL,
Tp_Schedule_line_Percentage = NULL,
Tp_Rule_percentage = NULL,
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE ei.tp_ind_compiled_set_id = compiled_set_id
AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, ei.expenditure_item_date)
AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
/*** AND ei.task_id NOT IN
(select t.task_id
FROM pa_projects_all p, pa_tasks t
WHERE t.project_id=p.project_id AND
ei.task_id = t.task_id AND
pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
/***2933915:UPDATE eis for 'BURDEN_RESUMMARIZE' ONLY if cost_distributed_flag ='Y' and profile option is enabled and burdening is
on separate ei */
/*====================================================================+
| M - If Enhanced Burdening is SET, for Separate line burdening |
| transactions the adjustment_type is set to BURDEN_RESUMMARIZE |
| - if cost_distributed_flag is Y. |
+====================================================================*/
UPDATE pa_expenditure_items_all ITEM
SET ITEM.adjustment_type = decode(ITEM.cost_ind_compiled_set_id, compiled_set_id, 'BURDEN_RESUMMARIZE' ,ITEM.adjustment_type)
WHERE ITEM.cost_distributed_flag ='Y'
AND ITEM.adjustment_type IS NULL
AND exists ( select 1 from pa_cost_distribution_lines_all cdl
where cdl.burden_sum_source_run_id >0
AND cdl.expenditure_item_id =ITEM.expenditure_item_id
AND cdl.line_type ='R'
AND nvl(cdl.reversed_flag,'N') ='N'
AND cdl.line_num_reversed is NULL)
AND ITEM.cost_ind_compiled_set_id = compiled_set_id
/*S.N. Bug4560630*/
AND (ITEM.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
l_start_date AND
NVL(l_end_date, ITEM.expenditure_item_date))
/*E.N. Bug4560630*/
AND l_burden_profile ='Y'
AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('D','d');
x_last_updated_by,
x_last_update_login,
status,
x_request_id,
x_program_id,
x_program_application_id,
SYSDATE);
raise INSERT_ADJ_ACTIVITY_ERROR;
when INSERT_ADJ_ACTIVITY_ERROR then
return;
when GMS_INSERT_ENC_ITEM_ERROR then --Bug 5726575
stage := 120;
INSERT_ADJ_ACTIVITY_ERROR exception;
SELECT expenditure_item_id
FROM pa_expenditure_items_all ITEM
WHERE cost_ind_compiled_set_id = compiled_set_id
AND adjustment_type in ('BURDEN_RECOMPILE','BURDEN_RESUMMARIZE','RECALC_BURDEN')
AND request_id = x_request_id
AND EXISTS (SELECT NULL
FROM pa_cost_base_exp_types cbet
WHERE cbet.cost_base = p_cost_base
AND cbet.cost_plus_structure = p_cost_plus_structure
AND cbet.cost_base_type = INDIRECT_COST_CODE
AND cbet.expenditure_type = ITEM.expenditure_type
)
;
SELECT expenditure_item_id
FROM pa_expenditure_items_all ITEM
WHERE rev_ind_compiled_set_id = compiled_set_id
AND request_id = x_request_id
AND EXISTS (SELECT NULL
FROM pa_cost_base_exp_types cbet
WHERE cbet.cost_base = p_cost_base
AND cbet.cost_plus_structure = p_cost_plus_structure
AND cbet.cost_base_type = INDIRECT_COST_CODE
AND cbet.expenditure_type = ITEM.expenditure_type
)
;
SELECT expenditure_item_id
FROM pa_expenditure_items_all ITEM
WHERE inv_ind_compiled_set_id = compiled_set_id
AND request_id = x_request_id
AND EXISTS (SELECT NULL
FROM pa_cost_base_exp_types cbet
WHERE cbet.cost_base = p_cost_base
AND cbet.cost_plus_structure = p_cost_plus_structure
AND cbet.cost_base_type = INDIRECT_COST_CODE
AND cbet.expenditure_type = ITEM.expenditure_type
)
; End Comment bug4527736 */
SELECT expenditure_item_id
FROM pa_expenditure_items_all ITEM
WHERE tp_ind_compiled_set_id = compiled_set_id
AND request_id = x_request_id
AND EXISTS (SELECT NULL
FROM pa_cost_base_exp_types cbet
WHERE cbet.cost_base = p_cost_base
AND cbet.cost_plus_structure = p_cost_plus_structure
AND cbet.cost_base_type = INDIRECT_COST_CODE
AND cbet.expenditure_type = ITEM.expenditure_type
)
;*/
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
l_eid_tbl.DELETE;
INSERT INTO pa_expend_item_adj_activities (
expenditure_item_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, activity_date
, exception_activity_code
, module_code
, request_id
, program_application_id
, program_id
, program_update_date )
VALUES (
l_eid_tbl(i) -- expenditure_item_id
, sysdate -- last_update_date
, x_last_updated_by -- last_updated_by
, sysdate -- creation_date
, x_last_updated_by -- created_by
, x_last_update_login -- last_update_login
, sysdate -- activity_date
, cost_adj_reason -- exception_activity_code
, adj_module -- module_code
, x_request_id -- request_id
, x_program_application_id -- program_application_id
, x_program_id -- program_id
, sysdate ); -- program_update_date
raise INSERT_ADJ_ACTIVITY_ERROR;
l_eid_tbl.DELETE;
INSERT INTO pa_expend_item_adj_activities (
expenditure_item_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, activity_date
, exception_activity_code
, module_code
, request_id
, program_application_id
, program_id
, program_update_date )
VALUES (
l_eid_tbl(i) -- expenditure_item_id
, sysdate -- last_update_date
, x_last_updated_by -- last_updated_by
, sysdate -- creation_date
, x_last_updated_by -- created_by
, x_last_update_login -- last_update_login
, sysdate -- activity_date
, rev_adj_reason -- exception_activity_code
, adj_module -- module_code
, x_request_id -- request_id
, x_program_application_id -- program_application_id
, x_program_id -- program_id
, sysdate ); -- program_update_date
raise INSERT_ADJ_ACTIVITY_ERROR;
l_eid_tbl.DELETE;
INSERT INTO pa_expend_item_adj_activities (
expenditure_item_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, activity_date
, exception_activity_code
, module_code
, request_id
, program_application_id
, program_id
, program_update_date )
VALUES (
l_eid_tbl(i) -- expenditure_item_id
, sysdate -- last_update_date
, x_last_updated_by -- last_updated_by
, sysdate -- creation_date
, x_last_updated_by -- created_by
, x_last_update_login -- last_update_login
, sysdate -- activity_date
, inv_adj_reason -- exception_activity_code
, adj_module -- module_code
, x_request_id -- request_id
, x_program_application_id -- program_application_id
, x_program_id -- program_id
, sysdate ); -- program_update_date
raise INSERT_ADJ_ACTIVITY_ERROR;
l_eid_tbl.DELETE;
INSERT INTO pa_expend_item_adj_activities (
expenditure_item_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, activity_date
, exception_activity_code
, module_code
, request_id
, program_application_id
, program_id
, program_update_date )
VALUES (
l_eid_tbl(i) -- expenditure_item_id
, sysdate -- last_update_date
, x_last_updated_by -- last_updated_by
, sysdate -- creation_date
, x_last_updated_by -- created_by
, x_last_update_login -- last_update_login
, sysdate -- activity_date
, tp_adj_reason -- exception_activity_code
, adj_module -- module_code
, x_request_id -- request_id
, x_program_application_id -- program_application_id
, x_program_id -- program_id
, sysdate ); -- program_update_date
raise INSERT_ADJ_ACTIVITY_ERROR;
INSERT INTO pa_expend_item_adj_activities (
expenditure_item_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, activity_date
, exception_activity_code
, module_code
, request_id
, program_application_id
, program_id
, program_update_date )
VALUES (
l_expenditure_item_id_tab(i) -- expenditure_item_id
, sysdate -- last_update_date
, x_last_updated_by -- last_updated_by
, sysdate -- creation_date
, x_last_updated_by -- created_by
, x_last_update_login -- last_update_login
, sysdate -- activity_date
, l_adj_type_tab(i) -- exception_activity_code
, adj_module -- module_code
, x_request_id -- request_id
, x_program_application_id -- program_application_id
, x_program_id -- program_id
, sysdate ); -- program_update_date
/****2933915 : Restructured this procedure to do Selective Obsoletion .
Selective obsoletion implies that only those compiled set ids will be obsoleted
for which the cost base is impacted i.e if any of the cost code is modified/deleted
for any org . in a particular revision .
If multipliers are present explicitly for any org for ALL the cost codes -that have
not changed then we should not be obsoleting the compiled set id for this org/cost base .
************************************************************************************/
procedure disable_rate_sch_revision(rate_sch_rev_id IN number,
ver_id IN number, /*2933915**/
org_id IN number, /**2933915**/
status IN OUT NOCOPY number,
stage IN OUT NOCOPY number)
is
CURSOR org_cursor
IS
SELECT organization_id_child org_id_child
FROM per_org_structure_elements
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = ver_id
START WITH organization_id_parent = org_id
AND org_structure_version_id = ver_id
UNION
select org_id from dual ;
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
UPDATE pa_ind_compiled_sets
SET status = 'H',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE ind_rate_sch_revision_id = rate_sch_rev_id
AND organization_id = org_id_rec.org_id_child
--AND cost_base = cost_base_rec.cost_base
AND cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND status = 'A' ;
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := FND_GLOBAL.USER_ID;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
UPDATE pa_ind_compiled_sets
SET status = 'H',
last_update_date = SYSDATE,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = SYSDATE
WHERE ind_rate_sch_revision_id = rate_sch_rev_id
AND organization_id = org_id
--AND cost_base = cost_base_rec.cost_base
AND cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND status = 'A' ; /*2933915*/
select ind_compiled_set_id
into x_ind_compiled_set_id
from pa_ind_compiled_sets
where ind_rate_sch_revision_id = x_rate_sch_rev_id
and organization_id = x_organization_id
and status = 'A';
select s.ind_rate_sch_name,
sr.ind_rate_sch_revision,
pl.meaning,
sr.start_date_active,
sr.end_date_active
into x_ind_rate_sch_name,
x_ind_rate_sch_revision,
x_ind_rate_sch_revision_type,
x_start_date_active,
x_end_date_active
from pa_ind_rate_schedules s,
pa_ind_rate_sch_revisions sr,
pa_lookups pl
where s.ind_rate_sch_id = sr.ind_rate_sch_id
and sr.ind_rate_sch_revision_type = pl.lookup_code
and pl.lookup_type = 'IND RATE SCHEDULE REV TYPE'
and sr.ind_rate_sch_revision_id = x_rate_sch_rev_id;
SELECT SUM(icpm.compiled_multiplier)
INTO P_Compiled_multiplier
FROM pa_ind_compiled_sets ics,
pa_compiled_multipliers icpm
WHERE ics.ind_rate_sch_revision_id = P_Rate_sch_rev_id
AND ics.organization_id = P_Org_id
AND ics.status = 'A'
AND ics.ind_compiled_set_id = icpm.ind_compiled_set_id
AND icpm.cost_base =ics.cost_base /*2933915*/
AND icpm.cost_base = P_C_base;
SELECT SUM(icpm.compiled_multiplier)
INTO l_Compiled_multiplier
FROM pa_compiled_multipliers icpm
WHERE icpm.ind_compiled_set_id = P_Ind_Compiled_Set_Id;
/************2933915 :New procedure to do Selective Deletion now *****************************************************/
/*
PROCEDURE
delete_rate_sch_revision
PURPOSE
The objective of this procedure is to delete only the
impacted compiled sets i.e for impacted organizations and impacted cost bases ,
for which no ei exists . */
/***********************************************************************************************************************/
procedure delete_rate_sch_revision(rate_sch_rev_id IN number,
ver_id IN number,
org_id IN number,
status IN OUT NOCOPY number,
stage IN OUT NOCOPY number)
is
CURSOR org_cursor
IS
SELECT organization_id_child org_id_child
FROM per_org_structure_elements
CONNECT BY PRIOR organization_id_child = organization_id_parent
AND org_structure_version_id = ver_id
START WITH organization_id_parent = org_id
AND org_structure_version_id = ver_id
UNION
select org_id from dual ;
DELETE pa_compiled_multipliers
WHERE ind_compiled_set_id IN
(SELECT ind_compiled_set_id
FROM pa_ind_compiled_sets
WHERE ind_rate_sch_revision_id = rate_sch_rev_id
and organization_id = ORG_REC.org_id_child
and cost_base = cost_base_rec.cost_base) ;
DELETE pa_ind_compiled_sets
WHERE ind_rate_sch_revision_id = rate_sch_rev_id
and organization_id =ORG_REC.org_id_child
and cost_base =G_IMPACTED_COST_BASES_TAB(i)
Bug# 4527736 */
NULL;
END delete_rate_sch_revision;
/*Cursor to select distinct organizations having ready_to_compile_flag as 'Y' */
Cursor ready_to_compile_orgs is
select DISTINCT organization_id
from pa_ind_cost_multipliers
where ind_rate_sch_revision_id = rate_sch_rev_id
and nvl(ready_to_compile_flag,'N') in ('Y','X') ;
SELECT count(a.organization_id_parent)
into l_count FROM
( SELECT organization_id_parent
FROM per_org_structure_elements
CONNECT BY PRIOR organization_id_parent = organization_id_child
AND org_structure_version_id = ver_id
START WITH organization_id_child = org.organization_id
AND org_structure_version_id = ver_id) a
WHERE a.organization_id_parent in (select DISTINCT organization_id
from pa_ind_cost_multipliers
where ind_rate_sch_revision_id = rate_sch_rev_id
and nvl(ready_to_compile_flag,'N') in ('Y','X') );
SELECT b.organization_id_parent
into l_parent
FROM (SELECT organization_id_parent
FROM per_org_structure_elements
CONNECT BY PRIOR organization_id_parent = organization_id_child
AND org_structure_version_id = ver_id
START WITH organization_id_child = org.organization_id
AND org_structure_version_id = ver_id) b
WHERE b.organization_id_parent in (select DISTINCT organization_id
from pa_ind_cost_multipliers
where ind_rate_sch_revision_id = rate_sch_rev_id
and nvl(ready_to_compile_flag,'N') in ('Y','X'));
SELECT distinct cbicc.ind_cost_code
FROM pa_cost_base_cost_codes cbicc,
pa_ind_rate_sch_revisions irsr
WHERE irsr.ind_rate_sch_revision_id = rate_sch_rev_id
AND irsr.cost_plus_structure = cbicc.cost_plus_structure
AND cbicc.cost_base = x_base
AND cbicc.cost_base_type = INDIRECT_COST_CODE ;
SELECT distinct cbicc.ind_cost_code
FROM pa_cost_base_cost_codes cbicc
WHERE cbicc.cost_plus_structure = G_CP_STRUCTURE
AND cbicc.cost_base = x_base
AND cbicc.cost_base_type = INDIRECT_COST_CODE ;
select 1 into org_override
from sys.dual
where exists(select 1
from pa_ind_cost_multipliers icm ,
pa_ind_compiled_sets ics
where icm.ind_rate_sch_revision_id =ics.ind_rate_sch_revision_id
and icm.ind_rate_sch_revision_id = rate_sch_rev_id
AND icm.organization_id =ics.organization_id
and icm.organization_id = org_id
--AND ics.cost_base = cost_base_rec.cost_base
AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
AND ics.status ='A'
and icm.ind_cost_code = cost_code_rec.ind_cost_code
and nvl(icm.ready_to_compile_flag,'N') ='N');
select 0 into org_override
from sys.dual
where exists
(
select 1
from pa_ind_cost_multipliers icm ,
pa_ind_compiled_sets ics
where icm.ind_rate_sch_revision_id =ics.ind_rate_sch_revision_id
and icm.ind_rate_sch_revision_id = rate_sch_rev_id
and icm.organization_id =ics.organization_id
and icm.organization_id = org_id
and nvl(icm.ready_to_compile_flag,'N') <> 'N'
);
SELECT organization_id_parent into l_org_id_parent
FROM per_org_structure_elements
WHERE organization_id_child = org_id
AND org_structure_version_id = G_ORG_STRUC_VER_ID;
SELECT pcb.COST_BASE
FROM PA_COST_BASES pcb
WHERE pcb.COST_BASE_TYPE = INDIRECT_COST_CODE
AND nvl(G_MODULE ,'XXX') <> 'NEW_ORG' /*4870539*/
AND EXISTS
(
SELECT 1
FROM PA_COST_BASE_COST_CODES CBICC,
PA_IND_COST_MULTIPLIERS ICM
WHERE ICM.IND_RATE_SCH_REVISION_ID = P_Ind_Rate_Sch_Revision_Id
AND (NVL(ICM.READY_TO_COMPILE_FLAG,'N') IN ('Y','X') AND NVL(G_MODULE ,'XXX') <> 'NEW_ORG')/*4870539*/
AND CBICC.COST_PLUS_STRUCTURE = P_Cp_Structure
AND CBICC.IND_COST_CODE = ICM.IND_COST_CODE
AND CBICC.COST_BASE = PCB.COST_BASE
AND CBICC.COST_BASE_TYPE = PCB.COST_BASE_TYPE )
UNION /*4870539 :Added union*/
SELECT pcb.COST_BASE
FROM PA_COST_BASES pcb
WHERE pcb.COST_BASE_TYPE = INDIRECT_COST_CODE
AND nvl(G_MODULE ,'XXX') = 'NEW_ORG'
AND EXISTS
(
SELECT 1
FROM PA_COST_BASE_COST_CODES CBICC,
PA_IND_rate_sch_revisions IRSR
WHERE IRSR.IND_RATE_SCH_REVISION_ID = P_Ind_Rate_Sch_Revision_Id
AND nvl(G_MODULE ,'XXX') = 'NEW_ORG'
AND IRSR.COST_PLUS_STRUCTURE= CBICC.COST_PLUS_STRUCTURE
AND CBICC.COST_PLUS_STRUCTURE = P_Cp_Structure
AND CBICC.COST_BASE = PCB.COST_BASE
AND CBICC.COST_BASE_TYPE = PCB.COST_BASE_TYPE ); /*End of changes for 4870539*/
G_IMPACTED_COST_BASES_TAB.DELETE;