[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pmald.Set_Of_Books_Id,
pmald.Proj_Asset_Line_Dtl_Uniq_Id,
pmald.Project_Asset_Line_Detail_Id,
pmald.Cip_Cost,
pmald.Currency_Code,
pmald.Exchange_Rate,
pmald.Conversion_Date
FROM
PA_Expenditure_Items_All pei ,
PA_Project_Asset_Line_Details pald,
pa_implementations_all pia ,
-- gl_mc_reporting_options gmc ,
GL_ALC_LEDGER_RSHIPS_V gmc, -- R12 Ledger changes
PA_MC_Prj_Ast_Line_Dtls pmald
WHERE
pald.proj_asset_line_dtl_uniq_id = pmald.proj_asset_line_dtl_uniq_id
AND pald.Expenditure_Item_id = pei.Expenditure_Item_id
AND pei.project_id = p_project_id
AND NVL(pia.ORG_ID,-99) = NVL(pei.ORG_ID,-99)
AND gmc.SOURCE_LEDGER_ID = pia.SET_OF_BOOKS_ID
AND gmc.APPLICATION_ID = 275
AND gmc.Org_Id = pia.ORG_ID
AND pmald.SET_OF_BOOKS_ID = gmc.LEDGER_ID;
p_err_stack := p_err_stack || ' ->Before insert into PA_MC_PRJ_AST_LINE_DETS_AR' ;
l_err_stage := 'Before Inserting into PA_MC_PRJ_AST_LN_DET_AR table';
INSERT INTO PA_MC_PRJ_AST_LN_DET_AR
( Set_Of_Books_Id,
Proj_Asset_Line_Dtl_Uniq_Id,
Project_Asset_Line_Detail_Id,
Cip_Cost,
Currency_Code,
Exchange_Rate,
Conversion_Date,
Purge_Release,
Purge_Batch_Id ,
Purge_Project_Id)
VALUES(
l_sob(i) ,
l_asset_line_uniq_id(i) ,
l_asset_line_id(i) ,
l_cip_cost(i) ,
l_cur_code(i) ,
l_exc_rate(i) ,
l_conv_date(i) ,
P_Purge_Release,
P_Purge_Batch_Id,
p_project_id
);
Delete From PA_MC_Prj_Ast_line_Dtls
Where SET_OF_BOOKS_ID = l_sob(i)
And PROJ_ASSET_LINE_DTL_UNIQ_ID = l_asset_line_uniq_id(i);
-- After "deleting" or "deleting and inserting" a set of records
-- the transaction is commited. This also creates a record in the
-- Pa_Purge_Project_details which will show the no. of records
-- that are purged from each table.
l_err_stage := 'Before Calling PA_Purge.CommitProcess';
l_sob.delete;
l_asset_line_uniq_id.delete;
l_asset_line_id.delete;
l_cip_cost.delete;
l_cur_code.delete;
l_exc_rate.delete;
l_conv_date.delete;
p_err_stack := p_err_stack || ' ->Before insert into PA_MC_PRJ_AST_LINE_DETS_AR' ;
l_err_stage := 'Before Inserting into PA_MC_PRJ_AST_LN_DET_AR table';
INSERT INTO PA_MC_PRJ_AST_LN_DET_AR
( Set_Of_Books_Id,
Proj_Asset_Line_Dtl_Uniq_Id,
Project_Asset_Line_Detail_Id,
Cip_Cost,
Currency_Code,
Exchange_Rate,
Conversion_Date,
Purge_Release,
Purge_Batch_Id ,
Purge_Project_Id)
SELECT
pmald.Set_Of_Books_Id,
pmald.Proj_Asset_Line_Dtl_Uniq_Id,
pmald.Project_Asset_Line_Detail_Id,
pmald.Cip_Cost,
pmald.Currency_Code,
pmald.Exchange_Rate,
pmald.Conversion_Date,
p_purge_release,
p_purge_batch_id,
p_project_id
FROM
PA_MC_Prj_Ast_Line_Dtls pmald,
PA_Project_Asset_Line_Details pald,
PA_Expenditure_Items_All pei
WHERE
pald.proj_asset_line_dtl_uniq_id = pmald.proj_asset_line_dtl_uniq_id
AND pald.Expenditure_Item_id = pei.Expenditure_Item_id
AND pei.project_id = p_project_id
AND rownum < l_commit_size ;
DELETE FROM PA_MC_Prj_Ast_line_Dtls pmald
WHERE (pmald.Proj_Asset_Line_Dtl_Uniq_Id) in
( SELECT pmaldar.Proj_Asset_Line_Dtl_Uniq_Id
FROM PA_MC_PRJ_AST_LN_DET_AR pmaldar,
PA_Project_Asset_Line_Details pald,
PA_Expenditure_Items_All pei
WHERE
pald.Proj_Asset_Line_Dtl_Uniq_Id =
pmaldar.Proj_Asset_Line_Dtl_Uniq_Id
AND pald.Expenditure_Item_id = pei.Expenditure_Item_id
AND pei.project_id = p_project_id);
DELETE FROM PA_MC_Prj_Ast_line_Dtls pmald
WHERE Exists ---- Bug 3613739 proj_asset_line_dtl_uniq_id IN
(SELECT proj_asset_line_dtl_uniq_id
FROM PA_Project_Asset_Line_Details pald,
PA_Expenditure_Items_All pei
WHERE pald.Expenditure_Item_id = pei.Expenditure_Item_id
AND pmald.proj_asset_line_dtl_uniq_id = pald.proj_asset_line_dtl_uniq_id -- Bug 3613739
AND pei.project_id = p_project_id)
AND rownum < l_commit_size ;
p_err_stack := p_err_stack || ' ->Before insert into PA_PRJ_ASSET_LN_DETS_AR' ;
l_err_stage := 'Before Inserting into PA_PRJ_ASSET_LN_DETS_AR table';
INSERT INTO PA_PRJ_ASSET_LN_DETS_AR
( Expenditure_Item_Id,
Line_Num,
Project_Asset_Line_Detail_Id,
Cip_Cost,
Reversed_Flag,
Last_Update_Date,
Last_Updated_By,
Created_By,
Creation_Date,
Last_Update_Login,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date,
Purge_Release,
Purge_Batch_Id,
Purge_Project_id,
PROJ_ASSET_LINE_DTL_UNIQ_ID) /* Bug#2385541 */
SELECT
pald.Expenditure_Item_Id,
pald.Line_Num,
pald.Project_Asset_Line_Detail_Id,
pald.Cip_Cost,
pald.Reversed_Flag,
pald.Last_Update_Date,
pald.Last_Updated_By,
pald.Created_By,
pald.Creation_Date,
pald.Last_Update_Login,
pald.Request_Id,
pald.Program_Application_Id,
pald.Program_Id,
pald.Program_Update_Date,
p_purge_release,
p_purge_batch_id,
p_project_id,
pald.PROJ_ASSET_LINE_DTL_UNIQ_ID /* Bug#2385541 */
FROM
PA_Project_Asset_Line_Details pald,
PA_Expenditure_Items_All pei
WHERE
pald.Expenditure_Item_id = pei.Expenditure_Item_id
AND pei.project_id = p_project_id
AND rownum < l_commit_size ;
Commented the delete statement and added the modified delete statement below.
DELETE FROM PA_Project_Asset_line_Details pald
WHERE (pald.Project_Asset_Line_Detail_Id) IN
( SELECT pald1.Proj_Asset_Line_Dtl_Uniq_Id
FROM PA_Project_Asset_line_Details pald1,
PA_PRJ_ASSET_LN_DETS_AR paldar,
PA_Expenditure_Items_All pei
WHERE
pald1.Project_Asset_Line_Detail_Id = paldar.Project_Asset_Line_Detail_Id
AND paldar.Expenditure_Item_Id = pei.Expenditure_Item_Id
and pei.project_id = p_project_id ) ;
DELETE FROM PA_Project_Asset_line_Details pald
WHERE (pald.PROJ_ASSET_LINE_DTL_UNIQ_ID) IN
( SELECT paldar.PROJ_ASSET_LINE_DTL_UNIQ_ID
FROM PA_Project_Asset_line_Details pald1,
PA_PRJ_ASSET_LN_DETS_AR paldar
WHERE
pald1.PROJ_ASSET_LINE_DTL_UNIQ_ID = paldar.PROJ_ASSET_LINE_DTL_UNIQ_ID
AND paldar.Purge_project_id = p_project_id
AND pald1.Project_Asset_Line_Detail_Id = paldar.Project_Asset_Line_Detail_Id) ;
DELETE FROM PA_Project_Asset_line_Details pald
WHERE Expenditure_Item_Id in (SELECT pei.Expenditure_Item_Id
FROM PA_Expenditure_Items_All pei
WHERE pei.project_id = p_project_id)
AND rownum < l_commit_size ;