Xxca Asset Project PKG

create or replace package body XXCA_ASSET_PROJECT_PKG As
PROCEDURE XXCA_ASSETS_PROJECT(S_NO IN NUMBER,
PROJECT_NAME IN VARCHAR,
ASSET_NUMBER IN VARCHAR,
ASSET_DESCRIPTION IN VARCHAR,
PROJECT_ASSET_TYPE IN VARCHAR,
ASSET_LOCATION IN VARCHAR,
DATE_PLACED_IN_SERVICE IN DATE,
ASSET_CATEGORY IN VARCHAR,
BOOK_TYPE_CODE IN VARCHAR,
ASSET_UNITS IN NUMBER,
ESTIMATED_ASSET_UNITS IN NUMBER,
ESTIMATED_COST IN NUMBER,
DEPRECIATE_FLAG IN VARCHAR,
DEPRECIATION_EXPENSE IN VARCHAR,
AMORTIZE_FLAG IN VARCHAR,
MANUFACTURER_NAME IN VARCHAR,
MODEL_NUMBER IN VARCHAR,
SERIAL_NUMBER IN VARCHAR,
TAG_NUMBER IN VARCHAR,
TASK_NAME IN VARCHAR,
PROJECT_STATUS IN VARCHAR,
TASK_STATUS IN VARCHAR,
USER_ID IN NUMBER,
RESP_ID IN NUMBER,
SEGMENT1 IN VARCHAR2,
SEGMENT12 IN VARCHAR2
--ASSET_KEY IN VARCHAR2
) Is
--- PRAGMA AUTONOMOUS_TRANSACTION; PA_SUPERUSER_GUI (PC)
--l_APPLICATION_ID NUMBER;
l_RESP_id NUMBER;
l_user_id number := FND_PROFILE.value('USER_ID');
lc_err_msg VARCHAR2(200) ;
lc_mesg VARCHAR2(200) ;
model_no1 varchar2(200) :=MODEL_NUMBER ;

NET_BOOK_VALUE1 NUMBER :=2 ;
Begin
/*Table of asset creations and adjustments backup history */
-- insert into XXX_ASSET_PROJECT_ADD_TL2
-- select * from XXX_ASSET_PROJECT_ADD_TL;
-- commit;
/*Table of asset Assign history */
--insert into CMPAK_ASSET_GEN_TL4
-- select * from CMPAK_ASSET_GEN_TL;
--commit;
/*Delete asset creation previous records from runtime table after backup */
-- delete from XXX_ASSET_PROJECT_ADD_TL;
-- commit;
/*Delete Asset Assign table from runtime table after backup */
--delete from CMPAK_ASSET_GEN_TL;
--commit;
/* 02-06-2018 Backup of Previous Data
XXX_ASSET_PROJECT_ADD_TL_Bk
CMPAK_ASSET_GEN_TL_Bk
*/
DELETE XXX_ASSET_PROJECT_ADD_TL K
WHERE STATUS in ('NEW') --,'ERROR')
AND K.USER_ID=FND_PROFILE.value('USER_ID');
COMMIT ;
BEGIN
select q.value1 INTO NET_BOOK_VALUE1 from
SELECT distinct (BOOKS.COST - DS.DEPRN_RESERVE - nvl(DS.IMPAIRMENT_RESERVE, 0)) value1
FROM FA_BOOKS BOOKS, fa_deprn_summary DS , FA_ADDITIONS_B B

WHERE DS.ASSET_ID = BOOKS.ASSET_ID
AND DS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
--AND DS.ASSET_ID = 1852196
AND B.ASSET_ID = BOOKS.ASSET_ID
AND B.ASSET_NUMBER = to_char(model_no1 )
and books.DATE_INEFFECTIVE is null
AND DS.PERIOD_COUNTER =
(SELECT max(DS1.PERIOD_COUNTER)
FROM fa_deprn_summary DS1
WHERE DS1.ASSET_ID = DS.ASSET_ID
AND DS1.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE
and rownum = 1)
)q
EXCEPTION WHEN NO_DATA_FOUND THEN
NET_BOOK_VALUE1 :=1 ;
END ;
IF NET_BOOK_VALUE1 = 0 OR NET_BOOK_VALUE1 < 0 THEN
/* update xxx_asset_project_add_tl a
set a.project_status = 'Error: ' ||
'Asset Net Book Vlaue is Zero',
a.status ='ERROR'
where 1=1
and a.ROWID = LP.ROWID
and a.status ='NEW' ;
--and a.segment1 is not null;
COMMIT;*/
Dbms_Output.put_line ('ERROR GENERATED FOR Asset Net Book Vlaue is Zero ') ;
lc_err_msg := 'Asset Net Book Vlaue is Zero ';
fnd_message.set_name('PA', lc_err_msg);
--fnd_message.set_token('ERROR_MESSAGE', lc_err_msg);
lc_mesg := fnd_message.get;
raise_application_error(-20001, lc_mesg);
---- goto end_block ;
END IF ;
/*
BEGIN

(
AND B.ASSET_NUMBER = to_char( model_no1 )
and rownum = 1)
)q
;
END ;
END IF ;
*/
insert into XXX_ASSET_PROJECT_ADD_TL
values
(S_NO,
PROJECT_NAME,
UPPER(ASSET_NUMBER),
ASSET_DESCRIPTION,
PROJECT_ASSET_TYPE,
ASSET_LOCATION,
DATE_PLACED_IN_SERVICE,
ASSET_CATEGORY,
BOOK_TYPE_CODE,
ASSET_UNITS,
ESTIMATED_ASSET_UNITS,
ESTIMATED_COST,
DEPRECIATE_FLAG,
DEPRECIATION_EXPENSE,
AMORTIZE_FLAG,
MANUFACTURER_NAME,
UPPER (MODEL_NUMBER),
SERIAL_NUMBER,
TAG_NUMBER,
TASK_NAME,
PROJECT_STATUS,
TASK_STATUS,
l_user_id ,--USER_ID,
RESP_ID,
null ,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SYSDATE,
SYSDATE,
'NEW'
);
Commit;
declare
cursor abc is
select s_no,
project_name,
asset_number,
asset_description,
project_asset_type,
asset_location,
date_placed__service,
asset_category,
book_type_code,
asset_units,
estimated_asset_units,
estimated_cost,
depreciate_flag,
depreciation_expense,
amortize_flag,
manufacturer_name,
model_number,
serial_number,
tag_number,
task_name,
project_status,
task_status,
user_id,
resp_id,
asset_key,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
ROWID
from xxx_asset_project_add_tl
where project_name is not null
and task_name is not null
and Segment1 is not null
AND USER_ID = FND_PROFILE.value('USER_ID');
--and status ='NEW'

--AND MODEL_NUMBER = 'CM0778253'
--- ;
-- AND USER_ID = FND_PROFILE.value('USER_ID');
vv_project_id number;
vv_task_id number;
vv_category_id number;
vv_depr_exp_id number;
vv_loc_id number;
v_asset_number varchar2(200);
lc_err_msg varchar2(1000);
lc_mesg varchar2(4000);
l_asst_cnt number :=0;
--l_APPLICATION_ID NUMBER;
--l_RESPONSIBILITY_id NUMBER;
--l_user_id number;
begin
-- l_user_id := USER_ID;
-- select t.APPLICATION_ID, t.RESPONSIBILITY_id
-- into l_APPLICATION_ID, l_RESPONSIBILITY_id
-- from fnd_responsibility_tl t
-- where t.RESPONSIBILITY_NAME like 'Project Costing Super User';
for lp in abc loop
-- dbms_output.put_line('Arshad');
/*
BEGIN
SELECT (BOOKS.COST - DS.DEPRN_RESERVE - nvl(DS.IMPAIRMENT_RESERVE, 0)) INTO NET_BOOK_VALUE1
AND B.ASSET_NUMBER = lp.ASSET_NUMBER
AND DS1.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE) ;
END ;
IF NET_BOOK_VALUE1 = 0 THEN
END IF ;*/
if lp.ASSET_NUMBER is not null then
select count(*)
into l_asst_cnt
from PA_PROJECT_ASSETS_ALL a, pa_projects_all p

where a.project_id = p.project_id
-- and upper(p.name) = upper(lp.project_name) --changed by umair siddique
and p.SEGMENT1 = lp.project_name
and a.asset_number = lp.ASSET_NUMBER;
end if ;
/*
BEGIN
SELECT (BOOKS.COST - DS.DEPRN_RESERVE - nvl(DS.IMPAIRMENT_RESERVE, 0)) INTO NET_BOOK_VALUE1
AND B.ASSET_NUMBER = lp.ASSET_NUMBER

AND DS1.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE) ;
END ;
IF NET_BOOK_VALUE1 = 0 THEN
END IF ;*/
if l_asst_cnt > 0 then

lc_err_msg := 'Asset Aready Exists against this project number';
else
select distinct a.PROJECT_ID
into vv_project_id
from pa_projects_all a, pa_tasks_all_v b
where a.PROJECT_ID = b.project_id
and b.parent_task_id is null
--and a.ORG_ID = 458
and a.SEGMENT1 like lp.project_name
and a.ENABLED_FLAG like 'Y';
BEGIN
select distinct b.task_id
into vv_task_id
from pa_projects_all a, pa_tasks_all_v b
where a.PROJECT_ID = b.project_id

and b.parent_task_id is null
--and a.ORG_ID = 458
and a.SEGMENT1 || ' - ' || b.task_number || ' - ' || b.task_name like
lp.task_name
and a.SEGMENT1 like lp.project_name
and a.ENABLED_FLAG like 'Y';
Exception when others then
lc_err_msg := 'Please Select Valid Task Related to Project';
END;
select l.LOCATION_ID
into vv_loc_id
from FA_LOCATIONS l
where l.ENABLED_FLAG like 'Y'
and l.SEGMENT1 =lp.asset_location;
--|| '-' || l.SEGMENT2 || '-' || l.SEGMENT3 || '-' ||
--l.SEGMENT4
-- like lp.asset_location;
select c.CATEGORY_ID
into vv_category_id --c.CATEGORY_ID,c.SEGMENT1 || '.' || c.SEGMENT2 ASSET_CAT,b.BOOK_TYPE_CODE, B.ORG_ID
from FA_CATEGORY_BOOKS a,
FA_BOOK_CONTROLS b,
FA_CATEGORIES_VL c
where a.BOOK_TYPE_CODE = b.BOOK_TYPE_CODE
and b.BOOK_CLASS like 'CORPORATE'
and a.CATEGORY_ID = c.CATEGORY_ID
and c.ENABLED_FLAG like 'Y'
--AND B.ORG_ID IS NOT NULL
and b.DATE_INEFFECTIVE is null
and b.BOOK_TYPE_CODE = lp.BOOK_TYPE_CODE
and c.SEGMENT1 || '-' || c.SEGMENT2 || '-' || c.SEGMENT3 || '-' ||
c.SEGMENT4 like LP.ASSET_CATEGORY
order by c.SEGMENT1 || '-' || c.SEGMENT2;
select a.CODE_COMBINATION_ID
into vv_depr_exp_id
from gl_code_combinations a
where --a.SEGMENT1 =lp.depreciation_expense;
a.SEGMENT1 || '-' || a.SEGMENT2 || '-' || a.SEGMENT3 || '-' ||
a.SEGMENT10 = lp.SEGMENT1 || '-' || lp.SEGMENT2 || '-' || lp.SEGMENT3 || '-' ||

lp.SEGMENT4 || '-' || lp.SEGMENT5 || '-' || lp.SEGMENT6 || '-' ||
lp.SEGMENT7 || '-' || lp.SEGMENT8 || '-' || lp.SEGMENT9 || '-' || lp.SEGMENT10;
BEGIN
IF LP.ASSET_NUMBER IS NOT NULL AND LP.MODEL_NUMBER IS NULL THEN
v_asset_number :=LP.ASSET_NUMBER;
ELSIF LP.MODEL_NUMBER IS NOT NULL AND LP.ASSET_NUMBER IS NULL THEN
v_asset_number := /*'DUM-'|| */ XXCA_ASSET_MODEL_N (LP.MODEL_NUMBER) ;
/*
BEGIN

(
AND B.ASSET_NUMBER = to_char( LP.MODEL_NUMBER )
and rownum = 1)
)q
;
END ;
update xxx_asset_project_add_tl a
'Asset Net Book Vlaue is Zero',
a.status ='ERROR'
where 1=1
and a.ROWID = LP.ROWID
and a.status ='NEW' ;
--and a.segment1 is not null;
COMMIT;
Dbms_Output.put_line ('ERROR GENERATED FOR Asset Net Book Vlaue is Zero ') ;
END IF ;
*/
--LP.MODEL_NUMBER; (COMENTED IT DUE TO ERROR OF DUPLICATE ASSETS)

--SELECT 'DUM-'||XXCA_ASSET_PROJECT_S.NEXTVAL INTO v_asset_number FROM DUAL;
ELSE
lc_err_msg := 'Both Asset Number field and old Asset Number should not null';
-- v_asset_number :=LP.ASSET_NUMBER;
END IF;
END;
-- v_asset_number :=LP.ASSET_NUMBER;
declare
v_number number;
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_return_status VARCHAR2(2000);
l_pa_project_id_out NUMBER;
l_pa_project_number_out VARCHAR2(2000);
l_pa_project_asset_id_out NUMBER;
l_pm_asset_reference_out VARCHAR2(2000);
l_output VARCHAR2(2000);
l_msg_dummy VARCHAR2(2000);
lc_er_msg varchar2(1000);
-- lc_mesg varchar2(1000);
--l_asst_cnt number :=0;
-- api_error EXCEPTION;
-- n NUMBER := 0;
begin
--select A.* from pa_project_assets_all a
--where TO_DATE(a.CREATION_DATE) >= to_date('01-SEP-2015')
pa_interface_utils_pub.set_global_info(p_api_version_number => 1.0,
p_responsibility_id => lp.resp_id, --50065, --:Responsibility_id,
p_user_id => lp.user_id,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,

p_return_status => l_return_status);
PA_PROJECT_ASSETS_PUB.add_project_asset(p_api_version_number => '1.0',
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_return_status => l_return_status,
p_pm_product_code => 'Test Lagacy System',
p_pm_project_reference => vv_project_id, --n, --'1',
p_pa_project_id => vv_project_id, --v_pa_project_id, --12547, -- 11547,
p_pm_asset_reference => v_asset_number, --n, --'1',
p_pa_asset_name => v_asset_number, --n, --'1',
p_asset_number => v_asset_number, --n, --'1',
p_asset_description => lp.asset_description, --n, --'1',
p_project_asset_type => lp.project_asset_type, --'AS-BUILT',
p_location_id => vv_loc_id, --3,
p_assigned_to_person_id => null,
p_date_placed_in_service => to_date(lp.date_placed__service),
p_asset_category_id => vv_category_id, --13,
p_book_type_code => lp.book_type_code, --'SVCS CORP',
p_asset_units => lp.asset_units, --1,
p_estimated_asset_units => lp.estimated_asset_units, --1,
p_estimated_cost => lp.estimated_cost, --500,
p_depreciate_flag => lp.depreciate_flag, --'Y',

p_depreciation_expense_ccid => vv_depr_exp_id, --496339,
p_amortize_flag => lp.amortize_flag, --'N',
p_estimated_in_service_date => to_date(lp.date_placed__service),
p_asset_key_ccid => 1,
p_attribute_category => NULL,
p_attribute1 => 'Test API By Arshad Hashmi 1',
p_manufacturer_name => lp.manufacturer_name, --n, --'1',
p_model_number => lp.model_number, --n, --'1',
p_serial_number => lp.serial_number, --n, --'1',
p_tag_number => lp.tag_number, --n, --'1',
p_ret_target_asset_id => NULL,
p_pa_project_id_out => l_pa_project_id_out,
p_pa_project_number_out => l_pa_project_number_out,
p_pa_project_asset_id_out => l_pa_project_asset_id_out,
p_pm_asset_reference_out => l_pm_asset_reference_out);
COMMIT;
-- dbms_output.put_line(l_pa_project_asset_id_out);
IF l_return_status <> 'S' THEN
FOR n IN 1 .. l_msg_count LOOP
fnd_msg_pub.get(n,
fnd_api.g_false,
l_msg_data,
l_msg_dummy);
l_output := (TO_CHAR(n) || ': ' || l_msg_data);

/* DBMS_OUTPUT.put_line('Error: API Error while updating the Task: ' ||
l_output);
*/
-- just check for testing
l_output,
a.status ='ERROR'
where a.project_name = lp.project_name
--and nvl(a.asset_number,a.ROWID = NVL(lp.asset_number,
and a.task_name = lp.task_name
and a.ROWID = lp.ROWID
and a.status ='NEW'
and a.segment1 is not null;
COMMIT;
lc_er_msg := l_output;
fnd_message.set_name('PA', lc_er_msg);
/* if error i donot want to insert into next task assign table 18-12-2018*/
/*
insert into CMPAK_ASSET_GEN_TL

(pa_project_asset,
pa_project_id,
pa_task_id,
pa_project_asset_id_out,
status)
Values
lp.asset_number,
vv_project_id,
vv_task_id,
l_pa_project_asset_id_out,
'Error: API Error while creating the : ' || l_output);
*/
END LOOP;
ELSE
set a.project_status = 'Sucessfully Update the Asset' ||
a.status ='SUCCESS'
-- and a.asset_number = lp.asset_number
and a.status ='NEW'
if lp.asset_number is not null then
(pa_project_asset,
pa_project_id,
pa_task_id,
status,
project_name,
task_name,
user_id,
resp_id,
RECORD_STATUS)
Values
lp.asset_number,
vv_project_id,
vv_task_id,
'Sucessfully Update the Asset',
lp.project_name,
lp.task_name,
lp.user_id,
lp.resp_id,
'NEW');
COMMIT;
ELSIF lp.model_number is not null then
(pa_project_asset,
pa_project_id,
pa_task_id,
status,
project_name,
task_name,
user_id,
resp_id,
RECORD_STATUS)
Values
lp.model_number,
vv_project_id,
vv_task_id,
'Sucessfully Update the Asset',
lp.project_name,
lp.task_name,
lp.user_id,
lp.resp_id,
'NEW');
COMMIT;
END IF ;
END IF;
EXCEPTION
/* WHEN api_error THEN
DBMS_OUTPUT.put_line('API Error in Project: ');*/
WHEN OTHERS THEN

l_output,
a.status ='ERROR'
--and nvl(a.asset_number,a.ROWID = NVL(lp.asset_number,
and a.status ='NEW'
COMMIT;
lc_er_msg := l_output;
fnd_message.set_name('PA', lc_er_msg);
--- DBMS_OUTPUT.put_line('Other Error in Project: ' || SQLERRM);
end;
end if;
end loop;
declare
cursor abc is
select *
from CMPAK_ASSET_GEN_TL k
where k.pa_project_asset is not null
and k.pa_project_asset_id_out is not null
and k.record_status='NEW';
begin
for t in abc loop
declare
v_number number;
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_return_status VARCHAR2(2000);
l_pa_project_id_out NUMBER;
l_pa_task_id_out NUMBER;
l_pa_project_number_out VARCHAR2(2000);
-- L_pa_project_asset_id_out NUMBER;
l_pa_project_asset_id_out NUMBER;
l_pm_asset_reference_out VARCHAR2(2000);
l_output VARCHAR2(2000);
l_msg_dummy VARCHAR2(2000);
lc_e_msg VARCHAR2(2000);
-- api_error EXCEPTION;
n NUMBER := 0;
begin
pa_interface_utils_pub.set_global_info(p_api_version_number => 1.0,
p_responsibility_id => t.resp_id, --50065, --:Responsibility_id,
p_user_id => t.user_id,
--:User_id,
p_return_status => l_return_status);
PA_PROJECT_ASSETS_PUB.add_asset_assignment(p_api_version_number => '1.0',
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_return_status => l_return_status,
p_pm_product_code => 'Test Lagacy System',
p_pm_project_reference => 1, --v_pa_project_id,
p_pa_project_id => t.pa_project_id, --12547,

p_pm_task_reference => t.pa_task_id, --170811, -- 170811,
p_pa_task_id => t.pa_task_id, --170811,
p_pm_asset_reference => t.pa_project_asset_id_out --8582 --8574 --8561
p_pa_project_asset_id => t.pa_project_asset_id_out, --8582 --8574 --8561
p_pa_task_id_out => l_pa_task_id_out,
p_pa_project_asset_id_out => l_pa_project_asset_id_out);
COMMIT;
IF l_return_status <> 'S' THEN
FOR n IN 1 .. l_msg_count LOOP
fnd_msg_pub.get(n,
fnd_api.g_false,
l_msg_data,
l_msg_dummy);
l_output := (TO_CHAR(n) || ': ' || l_msg_data);
/* update xxx_asset_project_add_tl a
set a.task_status = 'Error: API Error while updating the Task: ' ||
l_output || l_pa_task_id_out,
STATUS ='ERROR'
where a.project_name = t.project_name
and NVL(a.asset_number,a.model_number) = t.PA_PROJECT_ASSET
and a.task_name = t.task_name
AND STATUS ='NEW'
update CMPAK_ASSET_GEN_TL b
set Task_Status = 'Error: API Error while updating the Task: ' ||
l_output,
record_status ='ERROR'
where b.pa_project_asset = t.pa_project_asset
and b.pa_project_id = t.pa_project_id
and b.pa_task_id = t.pa_task_id
and b.record_status ='NEW'
and b.pa_project_asset_id_out =
t.pa_project_asset_id_out;
COMMIT;
lc_err_msg :=l_output;
raise_application_error(-20001, lc_mesg); */
END LOOP;
ELSE
-- DBMS_OUTPUT.put_line('Sucessfully Update the task');
set a.task_status = 'Sucessfully Update the task' ||
l_output || l_pa_task_id_out
and NVL(a.asset_number,a.model_number) = t.pa_project_asset
set Task_Status = 'Sucessfully Update the task',
b.record_status ='SUCCESS'
and b.pa_project_asset_id_out = t.pa_project_asset_id_out;

COMMIT;
END IF;
EXCEPTION
/* WHEN api_error THEN
DBMS_OUTPUT.put_line('API Error in Project: ');*/
WHEN OTHERS THEN
set Task_Status = 'Error: API Error while updating the Task: ' ||
l_output,
record_status ='ERROR'
and b.pa_project_asset_id_out =
t.pa_project_asset_id_out;
set a.task_status = 'Error: API Error while updating the Task: ' ||
l_output || l_pa_task_id_out,
STATUS ='ERROR'
and NVL(a.asset_number,a.model_number) = t.PA_PROJECT_ASSET

AND STATUS ='NEW'
lc_e_msg := l_output;
fnd_message.set_name('PA', lc_e_msg);
---DBMS_OUTPUT.put_line('Other Error in Project: ' || SQLERRM);*/
end;
end loop;
end;
end;
end;
FUNCTION XXCA_ASSET_MODEL_N (P_ASSET_NUM IN VARCHAR2 ) RETURN VARCHAR2 AS
VA_ASSET_NUM VARCHAR2 (100);
VA_CNT NUMBER;
--VA_TM VARCHAR2 (2);
BEGIN
select COUNT(*) INTO VA_CNT from pa_project_assets_all where asset_number LIKE '%' || P_ASSET_NUM || '%' ;
--SELECT SUBSTR(to_char(systimestamp,'FF'), 1, 2) INTO VA_TM FROM DUAL;
IF
VA_CNT >= 1 THEN
VA_ASSET_NUM := P_ASSET_NUM ||'-D'|| VA_CNT ;--|| --VA_TM ;
ELSE
VA_ASSET_NUM := P_ASSET_NUM;
END IF;
RETURN VA_ASSET_NUM;
END ;
End XXCA_ASSET_PROJECT_PKG ;

Xxca Asset Project PKG

Uploaded by

Copyright:

Available Formats

Xxca Asset Project PKG

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Xxca Asset Project PKG

Uploaded by

Copyright:

Available Formats

What is the purpose of the procedure XXCA_ASSET_PROJECT_PKG?

What is the purpose of the procedure XXCA_ASSET_PROJECT_PKG?

What are some of the main variables and parameters used in the procedure?

What are some of the main variables and parameters used in the procedure?

create or replace package body XXCA_ASSET_PROJECT_PKG As

PROCEDURE XXCA_ASSETS_PROJECT(S_NO IN NUMBER,

--- PRAGMA AUTONOMOUS_TRANSACTION; PA_SUPERUSER_GUI (PC)

l_user_id number := FND_PROFILE.value('USER_ID');

model_no1 varchar2(200) :=MODEL_NUMBER ;

/*Table of asset creations and adjustments backup history */

-- insert into XXX_ASSET_PROJECT_ADD_TL2

-- select * from XXX_ASSET_PROJECT_ADD_TL;

/*Table of asset Assign history */

--insert into CMPAK_ASSET_GEN_TL4

-- select * from CMPAK_ASSET_GEN_TL;

-- delete from XXX_ASSET_PROJECT_ADD_TL;

/*Delete Asset Assign table from runtime table after backup */

--delete from CMPAK_ASSET_GEN_TL;

WHERE STATUS in ('NEW') --,'ERROR')

select q.value1 INTO NET_BOOK_VALUE1 from

SELECT distinct (BOOKS.COST - DS.DEPRN_RESERVE - nvl(DS.IMPAIRMENT_RESERVE, 0)) value1

FROM FA_BOOKS BOOKS, fa_deprn_summary DS , FA_ADDITIONS_B B

AND DS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE

--AND DS.ASSET_ID = 1852196

AND B.ASSET_ID = BOOKS.ASSET_ID

AND B.ASSET_NUMBER = to_char(model_no1 )

and books.DATE_INEFFECTIVE is null

FROM fa_deprn_summary DS1

WHERE DS1.ASSET_ID = DS.ASSET_ID

AND DS1.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE

EXCEPTION WHEN NO_DATA_FOUND THEN

IF NET_BOOK_VALUE1 = 0 OR NET_BOOK_VALUE1 < 0 THEN

set a.project_status = 'Error: ' ||

'Asset Net Book Vlaue is Zero',

and a.ROWID = LP.ROWID

and a.status ='NEW' ;

--and a.segment1 is not null;

lc_err_msg := 'Asset Net Book Vlaue is Zero ';

---- goto end_block ;

select q.value1 INTO NET_BOOK_VALUE1 from

SELECT distinct (BOOKS.COST - DS.DEPRN_RESERVE - nvl(DS.IMPAIRMENT_RESERVE, 0)) value1

FROM FA_BOOKS BOOKS, fa_deprn_summary DS , FA_ADDITIONS_B B

WHERE DS.ASSET_ID = BOOKS.ASSET_ID

AND DS.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE

--AND DS.ASSET_ID = 1852196

AND B.ASSET_ID = BOOKS.ASSET_ID

AND B.ASSET_NUMBER = to_char( model_no1 )

and books.DATE_INEFFECTIVE is null

FROM fa_deprn_summary DS1

WHERE DS1.ASSET_ID = DS.ASSET_ID

AND DS1.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE

EXCEPTION WHEN NO_DATA_FOUND THEN

IF NET_BOOK_VALUE1 = 0 OR NET_BOOK_VALUE1 < 0 THEN

lc_err_msg := 'Asset Net Book Vlaue is Zero ';

---- goto end_block ;

insert into XXX_ASSET_PROJECT_ADD_TL

where project_name is not null

and task_name is not null

/Table of asset creations and adjustments backup history /

/Table of asset Assign history /

/Delete Asset Assign table from runtime table after backup /