[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(FIXED_ASSETS_COST, 0),
nvl(PAYABLES_COST, 0),
nvl(SALVAGE_VALUE, 0),
nvl(allowed_DEPRN_LIMIT_AMOUNT,0),
nvl(PRODUCTION_CAPACITY, 0),
nvl(REVAL_AMORTIZATION_BASIS, 0),
nvl(REVAL_RESERVE, 0),
nvl(UNREVALUED_COST, 0),
nvl(YTD_REVAL_DEPRN_EXPENSE, 0),
nvl(DEPRN_RESERVE, 0),
nvl(YTD_DEPRN, 0),
nvl(BEGINNING_NBV, 0),
nvl(FIXED_ASSETS_UNITS, 0),
Merged_Code,
merge_parent_mass_additions_id,
Mass_Addition_ID
FROM FA_MASS_ADDITIONS
WHERE Merge_Parent_MASS_ADDITIONs_ID = X_mass_addition_id
and Book_Type_Code = X_Book_Type_Code
and MERGED_CODE = 'MC'
For UPDATE ;
CURSOR C2 is Select Fa_MAss_Additions_S.nextval From sys.dual;
Select count(*) into Total_Child_Records
From FA_MASS_ADDITIONS
Where Merge_Parent_MASS_ADDITIONS_ID = x_mass_addition_id
and MERGED_CODE = 'MC';
SELECT sum_units into Y_Sum_Units
FROM fa_mass_additions
WHERE mass_addition_id = x_mass_addition_id;
SELECT sum(fixed_assets_units) into Y_Merged_Child_FA_Units
FROM fa_mass_additions
WHERE merge_parent_mass_additions_id = x_mass_addition_id
AND merged_code = 'MC';
Select Precision, Extended_precision, MINIMUM_ACCOUNTABLE_UNIT
Into precision, ext_precision, min_acct_unit
from fnd_Currencies
Where Currency_code = X_Currency_Code;
Select SUM(Round(ma.fixed_assets_Cost, precision)),
Sum(Round(ma.payables_Cost, precision)),
Sum(Round(ma.salvage_value, precision)),
Sum(Round(ma.ALLOWED_DEPRN_LIMIT_AMOUNT, precision)),
sum(round(ma.production_capacity, precision)),
sum(round(ma.reval_amortization_basis, precision)),
sum(round(ma.reval_reserve, precision)),
sum(round(ma.unrevalued_cost, precision)),
sum(round(ma.ytd_reval_deprn_expense, precision)),
sum(round(ma.deprn_reserve, precision)),
sum(round(ma.ytd_deprn, precision)),
sum(round(ma.beginning_nbv, precision)) Into Total_Merged_Children_Cost_fa,
Total_Merged_Children_cost_ap,
Total_Merged_Salvage_Value,
TOT_MERGED_DEPR_LIMIT_AMT,
Total_Merged_Prod_Cap,
Total_Merged_Rev_Amort_B, Total_Merged_Reval_Reserve,
Total_Merged_Unrev_Cost, Total_Merged_Ytd_RDE,
Total_Merged_Deprn_Reserve, Total_Merged_YTD_Deprn,
Total_Merged_Beginning_NBV
From FA_MAss_Additions ma
where ma.merge_parent_mass_additions_id = x_mass_addition_id;
-- Loop through to insert child records equal in number to the X_Total_fa_Units
For record_num In 1 .. X_Total_fa_Units Loop
-- Create the split children.
Temp_Split_Unit_Cost_fa := Split_Unit_cost_fa;
-- When the last child is being inserted check for the rounding problem
if (record_num = X_Total_fa_Units) then
Last_Split_Unit_Cost_fa := X_Parent_Unit_Cost_fa - Split_Unit_Cost_fa * (X_Total_fa_Units -1);
Insert_Split_Child(I_Mass_Additions_ID => X_Mass_Addition_Id,
I_New_Mass_Addition_Id => New_Parent_Mass_Addition_Id,
I_Split_Parent_MAss_Add_Id => X_Mass_Addition_Id,
I_Merge_Parent_MAss_Add_ID => NULL,
I_Fa_Cost => Last_Split_Unit_Cost_fa,
I_Pa_Cost => Last_Split_Unit_cost_ap,
I_Salvage_Value => Last_Split_Salvage_Value,
I_DEPRN_LIMIT_AMOUNT => Last_Split_DEPRN_LIMIT_AMOUNT,
I_Production_Capacity => Last_Split_Prod_Cap,
I_Reval_Amortization_Basis => Last_Split_Rev_Amort_B,
I_Reval_Reserve => Last_Split_Reval_Reserve,
I_Unrevalued_Cost => Last_Split_Unrev_Cost,
I_Ytd_Reval_Deprn_Expense => Last_Split_Ytd_RDE,
I_Deprn_Reserve => Last_Split_Deprn_Reserve,
I_YTD_Deprn => Last_Split_YTD_Deprn,
I_Beginning_NBV => Last_Split_Beginning_NBV,
I_Total_fa_Units => X_Total_fa_Units,
I_Merge_Child_fa_Units => Y_Merged_Child_FA_Units,
I_Sum_Units => Y_Sum_Units
,p_log_level_rec => p_log_level_rec);
-- Make the Total_split_Unit_cost for the last set of records being inserted as
-- Last_Total_Split_Unit_Cost_Fa so that when the last merged child row is selected
-- it will take into account the roundng problem
Total_Split_Unit_Cost_Fa := Last_Total_Split_Unit_Cost_Fa;
-- Insert the single row.
Insert_Split_Child(I_Mass_Additions_ID => X_Mass_Addition_Id,
I_New_Mass_Addition_Id => New_Parent_Mass_Addition_Id,
I_Split_Parent_MAss_Add_Id => X_Mass_Addition_Id,
I_Merge_Parent_MAss_Add_ID => NULL,
I_Fa_Cost => Split_Unit_Cost_fa,
I_Pa_Cost => Split_Unit_cost_ap,
I_Salvage_Value => Split_Salvage_Value,
I_DEPRN_LIMIT_AMOUNT => Split_DEPRN_LIMIT_AMOUNT,
I_Production_Capacity => Split_Prod_Cap,
I_Reval_Amortization_Basis => Split_Rev_Amort_B,
I_Reval_Reserve => Split_Reval_Reserve,
I_Unrevalued_Cost => Split_Unrev_Cost,
I_Ytd_Reval_Deprn_Expense => Split_Ytd_RDE,
I_Deprn_Reserve => Split_Deprn_Reserve,
I_Ytd_Deprn => Split_Ytd_Deprn,
I_Beginning_NBV => Split_Beginning_NBV,
I_Total_fa_Units => X_Total_fa_Units,
I_Merge_Child_fa_Units => Y_Merged_Child_FA_Units,
I_Sum_Units => Y_Sum_Units
,p_log_level_rec => p_log_level_rec);
-- Fetch the Merged Child record and insert
-- their corresponding SPLIT Children
Open C2;
-- If the last set of records are being inserted then calculate
-- the Last_Child_Split_Unit_Cost_fa
if (record_num = X_Total_fa_Units) then
Child_Split_Unit_Cost_Fa := Child_Unit_Cost_fa - Child_Split_Unit_Cost_Fa * (X_Total_fa_Units - 1);
Insert_Split_Child(I_Mass_Additions_ID => Child_Mass_Add_Id,
I_New_Mass_Addition_Id => New_Child_Mass_Addition_Id,
I_Split_Parent_MAss_Add_Id => Child_Mass_Add_Id,
I_Merge_Parent_MAss_Add_ID => New_Parent_Mass_Addition_Id,
I_Fa_Cost => Child_Split_Unit_Cost_fa,
I_Pa_Cost => Child_Split_Unit_cost_ap,
I_Salvage_Value => Child_Split_Salvage_Value,
I_DEPRN_LIMIT_AMOUNT => Child_Split_DEPRN_LIMIT_AMOUNT,
I_Production_Capacity => Child_Split_Prod_Cap,
I_Reval_Amortization_Basis => Child_Split_Rev_Amort_B,
I_Reval_Reserve => Child_Split_Reval_Reserve,
I_Unrevalued_Cost => Child_Split_Unrev_Cost,
I_Ytd_Reval_Deprn_Expense => Child_Split_Ytd_RDE,
I_Deprn_Reserve => Child_Split_Deprn_Reserve,
I_Ytd_Deprn => Child_Split_Ytd_Deprn,
I_Beginning_NBV => Child_Split_Beginning_NBV,
I_Total_fa_Units => X_Total_fa_Units,
I_Merge_Child_fa_Units => Y_zero,
I_Sum_Units => Y_Sum_Units
,p_log_level_rec => p_log_level_rec);
Insert_Split_Child(I_Mass_Additions_ID => Child_Mass_Add_Id,
I_New_Mass_Addition_Id => New_Child_Mass_Addition_Id,
I_Split_Parent_MAss_Add_Id => Child_Mass_Add_Id,
I_Merge_Parent_MAss_Add_ID => New_Parent_Mass_Addition_Id,
I_Fa_Cost => Child_Split_Unit_Cost_fa,
I_Pa_Cost => Child_Split_Unit_cost_ap,
I_Salvage_Value => Child_Split_Salvage_Value,
I_DEPRN_LIMIT_AMOUNT => Child_Split_DEPRN_LIMIT_AMOUNT,
I_Production_Capacity => Child_Split_Prod_Cap,
I_Reval_Amortization_Basis => Child_Split_Rev_Amort_B,
I_Reval_Reserve => Child_Split_Reval_Reserve,
I_Unrevalued_Cost => Child_Split_Unrev_Cost,
I_Ytd_Reval_Deprn_Expense => Child_Split_Ytd_RDE,
I_Deprn_Reserve => Child_Split_Deprn_Reserve,
I_Ytd_Deprn => Child_Split_Ytd_Deprn,
I_Beginning_NBV => Child_Split_Beginning_NBV,
I_Total_fa_Units => X_Total_fa_Units,
I_Merge_Child_fa_Units => Y_zero,
I_Sum_Units => Y_Sum_Units
,p_log_level_rec => p_log_level_rec);
-- Update the Parent Mass Addition
UPDATE FA_MASS_ADDITIONS
SET
SPLIT_CODE = 'SP',
SPLIT_MERGED_CODE = NVL(MERGED_CODE, 'SP'),
POSTING_STATUS = 'SPLIT'
WHERE
MASS_ADDITION_ID = X_mass_addition_id;
UPDATE FA_MASS_ADDITIONS
SET
SPLIT_CODE = 'SP',
SPLIT_MERGED_CODE = NVL(MERGED_CODE, 'SP'),
POSTING_STATUS = 'SPLIT'
WHERE
Merge_Parent_MASS_ADDITIONs_ID = X_mass_addition_id;
--Insert Into temp_sv Values(Message_Number, 'App_exception',Message_Text);
Procedure Insert_Split_Child(I_Mass_Additions_ID NUMBER DEFAULT NULL,
I_New_Mass_Addition_Id NUMBER DEFAULT NULL,
I_Split_Parent_MAss_Add_Id NUMBER DEFAULT NULL,
I_Merge_Parent_MAss_Add_ID NUMBER DEFAULT NULL,
I_FA_COST NUMBER DEFAULT NULL,
I_PA_COST NUMBER DEFAULT NULL,
I_Salvage_Value NUMBER DEFAULT NULL,
I_DEPRN_LIMIT_AMOUNT NUMBER DEFAULT NULL,
I_Production_Capacity NUMBER DEFAULT NULL,
I_Reval_Amortization_Basis NUMBER DEFAULT NULL,
I_Reval_Reserve NUMBER DEFAULT NULL,
I_Unrevalued_Cost NUMBER DEFAULT NULL,
I_Ytd_Reval_Deprn_Expense NUMBER DEFAULT NULL,
I_Deprn_Reserve NUMBER DEFAULT NULL,
I_YTD_Deprn NUMBER DEFAULT NULL,
I_Beginning_NBV NUMBER DEFAULT NULL,
I_Total_fa_Units NUMBER DEFAULT NULL,
I_Merge_Child_fa_Units NUMBER DEFAULT NULL,
I_Sum_Units VARCHAR2 DEFAULT NULL,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
h_total_units number;
select units, deprn_expense_ccid, location_id, employee_id
from fa_massadd_distributions
where mass_addition_id = I_Split_Parent_Mass_Add_Id;
INSERT INTO FA_MASS_ADDITIONS
(MASS_ADDITION_ID,
ASSET_NUMBER, TAG_NUMBER,
DESCRIPTION, ASSET_CATEGORY_ID,
MANUFACTURER_NAME, SERIAL_NUMBER,
MODEL_NUMBER, BOOK_TYPE_CODE,
DATE_PLACED_IN_SERVICE, FIXED_ASSETS_COST,
PAYABLES_UNITS, FIXED_ASSETS_UNITS,
PAYABLES_CODE_COMBINATION_ID, EXPENSE_CODE_COMBINATION_ID,
LOCATION_ID, ASSIGNED_TO,
FEEDER_SYSTEM_NAME, CREATE_BATCH_DATE,
CREATE_BATCH_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, REVIEWER_COMMENTS,
INVOICE_NUMBER, VENDOR_NUMBER,
MERGE_INVOICE_NUMBER, MERGE_VENDOR_NUMBER,
PO_VENDOR_ID, PO_NUMBER,
POSTING_STATUS, QUEUE_NAME,
INVOICE_DATE, INVOICE_CREATED_BY,
INVOICE_UPDATED_BY, PAYABLES_COST,
INVOICE_ID, PAYABLES_BATCH_NAME,
DEPRECIATE_FLAG, PARENT_MASS_ADDITION_ID,
PARENT_ASSET_ID, SPLIT_MERGED_CODE,
AP_DISTRIBUTION_LINE_NUMBER, POST_BATCH_ID,
ADD_TO_ASSET_ID, AMORTIZE_FLAG,amortization_start_date,-- added this for bug 2972724
NEW_MASTER_FLAG, ASSET_KEY_CCID,
ASSET_TYPE, DEPRN_RESERVE,
YTD_DEPRN, BEGINNING_NBV,
CREATED_BY, CREATION_DATE,
LAST_UPDATE_LOGIN,
SALVAGE_VALUE,
ALLOWED_DEPRN_LIMIT_AMOUNT,
ACCOUNTING_DATE,
ATTRIBUTE_CATEGORY_CODE,
FULLY_RSVD_REVALS_COUNTER,
PRODUCTION_CAPACITY,
REVAL_AMORTIZATION_BASIS,
REVAL_RESERVE,
UNIT_OF_MEASURE,
UNREVALUED_COST,
YTD_REVAL_DEPRN_EXPENSE,
SPLIT_PARENT_MASS_ADDITIONS_ID,
MERGE_PARENT_MASS_ADDITIONS_ID,
SPLIT_CODE, MERGED_CODE,
SUM_UNITS,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24,
ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30,
GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY,
CONTEXT, INVENTORIAL,
TRANSACTION_TYPE_CODE,TRANSACTION_DATE, WARRANTY_ID, LEASE_ID,
LESSOR_ID, PROPERTY_TYPE_CODE, PROPERTY_1245_1250_CODE,
IN_USE_FLAG,OWNED_LEASED, NEW_USED, ASSET_ID
-- bugfix 1839692
, project_id, task_id, project_asset_line_id,
invoice_distribution_id,
invoice_line_number,
po_distribution_id,
warranty_number,
dePrn_limit_type,
ALLOWED_DEPRN_LIMIT,
salvage_type,
PERCENT_SALVAGe_VALUE)
Select
I_New_MASS_ADDITION_ID,
NULL, NULL,
DESCRIPTION, ASSET_CATEGORY_ID,
MANUFACTURER_NAME, NULL,
MODEL_NUMBER, BOOK_TYPE_CODE,
DATE_PLACED_IN_SERVICE, I_FA_COST, 1,
decode(MERGED_CODE,
'MP', 1,
'MC', decode(NVL(I_Sum_Units, 'NO'),
'YES', round((FIXED_ASSETS_UNITS-I_Merge_Child_fa_Units)/I_Total_fa_Units,2),
1),
1),
PAYABLES_CODE_COMBINATION_ID, EXPENSE_CODE_COMBINATION_ID,
LOCATION_ID, ASSIGNED_TO,
FEEDER_SYSTEM_NAME, CREATE_BATCH_DATE,
CREATE_BATCH_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, REVIEWER_COMMENTS,
INVOICE_NUMBER, VENDOR_NUMBER,
NVL(MERGE_INVOICE_NUMBER, INVOICE_NUMBER),
NVL(MERGE_VENDOR_NUMBER, VENDOR_NUMBER),
PO_VENDOR_ID, PO_NUMBER,
-- BUG# 1294559: Parents in 'NEW' queue should create children in 'ON HOLD'
-- bridgway 07/05/00
decode(POSTING_STATUS, 'NEW', 'ON HOLD', POSTING_STATUS),
decode(QUEUE_NAME, 'NEW', 'ON HOLD', QUEUE_NAME),
INVOICE_DATE, INVOICE_CREATED_BY,
INVOICE_UPDATED_BY, I_PA_COST,
INVOICE_ID, PAYABLES_BATCH_NAME,
DEPRECIATE_FLAG, decode(NVL(MERGED_CODE, 'SC'), 'SC', I_Mass_Additions_ID, NULL),
PARENT_ASSET_ID, NVL(MERGED_CODE, 'SC'),
AP_DISTRIBUTION_LINE_NUMBER, POST_BATCH_ID,
ADD_TO_ASSET_ID, AMORTIZE_FLAG,amortization_start_date,-- added this for bug 2972724
NEW_MASTER_FLAG, ASSET_KEY_CCID,
ASSET_TYPE,
I_Deprn_Reserve,
I_YTD_Deprn,
I_Beginning_NBV,
CREATED_BY, CREATION_DATE,
LAST_UPDATE_LOGIN,
I_Salvage_Value,
I_DEPRN_LIMIT_AMOUNT,
ACCOUNTING_DATE,
ATTRIBUTE_CATEGORY_CODE,
FULLY_RSVD_REVALS_COUNTER,
I_Production_Capacity,
I_Reval_Amortization_Basis,
I_Reval_Reserve,
UNIT_OF_MEASURE,
-- fix for 1461477 set back to null if no unrevalued cost
-- as this creates problem in mass add post
decode(I_Unrevalued_Cost,0,NULL,I_Unrevalued_Cost),
I_Ytd_Reval_Deprn_Expense,
I_Split_Parent_Mass_Add_id,
I_Merge_Parent_Mass_Add_Id,
'SC', MERGED_CODE,
NVL(I_Sum_Units, 'NO'),
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24,
ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30,
GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY,
CONTEXT, INVENTORIAL,
TRANSACTION_TYPE_CODE,TRANSACTION_DATE, WARRANTY_ID, LEASE_ID,
LESSOR_ID, PROPERTY_TYPE_CODE, PROPERTY_1245_1250_CODE,
IN_USE_FLAG,OWNED_LEASED, NEW_USED, NULL
-- bugfix 1839692 msiddiqu
, project_id, task_id, project_asset_line_id,
invoice_distribution_id,
invoice_line_number,
po_distribution_id,
warranty_number,
dePrn_limit_type,
ALLOWED_DEPRN_LIMIT,
salvage_type,
PERCENT_SALVAGe_VALUE
FROM
FA_MASS_ADDITIONS
WHERE MASS_ADDITION_ID = I_Mass_Additions_ID;
select fixed_assets_units into h_total_units from fa_mass_additions
where mass_addition_id = I_Split_Parent_Mass_Add_Id;
select count(*) into h_num_dists from fa_massadd_distributions
where mass_addition_id = I_Split_Parent_Mass_Add_Id;
select round((FIXED_ASSETS_UNITS-I_Merge_Child_fa_Units)/I_Total_fa_Units,2)
into h_child_total_units from fa_mass_additions
WHERE MASS_ADDITION_ID = I_Mass_Additions_ID;
select fixed_assets_units into h_child_total_units from fa_mass_additions
where mass_addition_id = I_New_MASS_ADDITION_ID;
select fa_massadd_distributions_s.nextval into h_dist_id from dual;
insert into fa_massadd_distributions (massadd_dist_id, mass_addition_id,
units, deprn_expense_ccid, location_id, employee_id) values (
h_dist_id, I_New_Mass_Addition_Id,
h_child_dist_units, h_ccid, h_location, h_employee);
FND_Message.Set_Token('PROCEDURE_NAME', 'Insert_Row');
End Insert_Split_Child;
Select Count(*) Into Child_records_Num
From Fa_Mass_Additions
Where POSTING_STATUS = 'POSTED'
And Split_parent_MAss_Additions_ID = X_Mass_Addition_ID
And Split_Code = 'SC';
Select Count(*) Into Child_records_Num
From Fa_Mass_Additions
Where Split_parent_MAss_Additions_ID = X_Mass_Addition_ID
And Split_Code = 'SC';
Select nvl(fixed_assets_units, 0) into h_units -- new variable
from fa_mass_additions
where mass_addition_id = X_Mass_Addition_ID;
-- Delete all the split Children which also happen to be Merged records.
-- The ones with Split_Code = 'SC' and Merged_Code = 'MC'
If (X_MERGED_CODE = 'MP') then
Delete from FA_MAss_Additions
Where Merge_Parent_Mass_Additions_Id In (Select Mass_Addition_ID
from FA_MAss_Additions
Where Split_Parent_Mass_Additions_Id = X_Mass_Addition_Id);
-- Delete all the records which are split parents
Delete from FA_MAss_Additions
Where Split_Parent_Mass_Additions_Id = X_Mass_Addition_Id;
-- Update the Split_code of the Parent record
Update FA_MAss_Additions
Set Split_Code = NULL,
Split_Merged_Code = NVL(MERGED_CODE, NULL),
POSTING_STATUS = decode (QUEUE_NAME, 'NEW', 'NEW',
'POST', 'POST',
'DELETE', 'DELETE',
'POST', 'POST',
'ADD TO ASSET', 'POST', 'ON HOLD')
Where Mass_addition_ID = X_Mass_Addition_ID;
Update FA_MAss_Additions
Set Split_Code = NULL,
Split_Merged_Code = NVL(MERGED_CODE, NULL),
POSTING_STATUS = 'MERGED'
Where merge_parent_Mass_additions_ID = X_Mass_Addition_ID;