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 ASSET_LOCATION

IN VARCHAR, 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 MODEL_NUMBER SERIAL_NUMBER TAG_NUMBER TASK_NAME PROJECT_STATUS TASK_STATUS

IN VARCHAR,

IN VARCHAR, IN VARCHAR, IN VARCHAR, IN VARCHAR, IN VARCHAR, IN VARCHAR,

USER_ID

IN NUMBER,

RESP_ID

IN NUMBER,

SEGMENT1

IN VARCHAR2,

SEGMENT2

IN VARCHAR2,

SEGMENT3

IN VARCHAR2,

SEGMENT4

IN VARCHAR2,

SEGMENT5

IN VARCHAR2,

SEGMENT6

IN VARCHAR2,

SEGMENT7

IN VARCHAR2,

SEGMENT8

IN VARCHAR2,

SEGMENT9

IN VARCHAR2,

SEGMENT10

IN VARCHAR2,

SEGMENT11

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 and a.status

= LP.ROWID ='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

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

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 ;

*/

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

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 = lp.ASSET_NUMBER

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) ;

EXCEPTION WHEN NO_DATA_FOUND THEN

NET_BOOK_VALUE1 :=1 ;

END ;

IF NET_BOOK_VALUE1 = 0 THEN

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 ;*/

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

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 = lp.ASSET_NUMBER

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) ;

EXCEPTION WHEN NO_DATA_FOUND THEN

NET_BOOK_VALUE1 :=1 ;

END ;

IF NET_BOOK_VALUE1 = 0 THEN

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 ;*/

if l_asst_cnt > 0 then

lc_err_msg := 'Asset Aready Exists against this project number'; 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);

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'; 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);

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.SEGMENT4 || '-' || a.SEGMENT5 || '-' || a.SEGMENT6 || '-' || a.SEGMENT7 || '-' || a.SEGMENT8 || '-' || a.SEGMENT9 || '-' || 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

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( LP.MODEL_NUMBER )

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 and a.status

= LP.ROWID ='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 ; */

--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'; fnd_message.set_name('PA', lc_err_msg); lc_mesg := fnd_message.get; raise_application_error(-20001, lc_mesg);

-- 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 lc_er_msg

VARCHAR2(2000);

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 p_commit

=> '1.0',

=> FND_API.G_FALSE,

p_init_msg_list

=> FND_API.G_FALSE,

p_msg_count

=> l_msg_count,

p_msg_data

=> l_msg_data,

p_return_status

=> l_return_status,

p_pm_product_code

=> 'Test Lagacy System',

p_pm_project_reference p_pa_project_id

=> vv_project_id, --n, --'1',

=> 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 p_location_id

=> lp.project_asset_type, --'AS-BUILT', => 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 p_attribute1

=> NULL, => 'Test API By Arshad Hashmi 1',

p_manufacturer_name p_model_number

=> lp.manufacturer_name, --n, --'1', => 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 update xxx_asset_project_add_tl a set a.project_status = 'Error: ' || 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 and a.status

= lp.ROWID ='NEW'

and a.segment1 is not null; COMMIT;

lc_er_msg := l_output; fnd_message.set_name('PA', lc_er_msg); lc_mesg := fnd_message.get; raise_application_error(-20001, lc_mesg);

/* 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

update xxx_asset_project_add_tl a set a.project_status = 'Sucessfully Update the Asset' || l_pa_project_asset_id_out, a.status

='SUCCESS'

where a.project_name = lp.project_name -- and a.asset_number = lp.asset_number and a.task_name = lp.task_name and a.ROWID and a.status

= lp.ROWID ='NEW'

and a.segment1 is not null;

if lp.asset_number is not null then

insert into CMPAK_ASSET_GEN_TL (pa_project_asset, pa_project_id, pa_task_id, pa_project_asset_id_out, status, project_name, task_name, user_id, resp_id, RECORD_STATUS) Values (

lp.asset_number,

vv_project_id, vv_task_id, l_pa_project_asset_id_out, '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

insert into CMPAK_ASSET_GEN_TL (pa_project_asset, pa_project_id, pa_task_id, pa_project_asset_id_out, status, project_name, task_name, user_id, resp_id, RECORD_STATUS)

Values (

lp.model_number, vv_project_id, vv_task_id, l_pa_project_asset_id_out, '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

update xxx_asset_project_add_tl a set a.project_status = 'Error: ' || 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 and a.status

= lp.ROWID ='NEW'

and a.segment1 is not null; COMMIT;

lc_er_msg := l_output; fnd_message.set_name('PA', lc_er_msg); lc_mesg := fnd_message.get; raise_application_error(-20001, lc_mesg); --- DBMS_OUTPUT.put_line('Other Error in Project: ' || SQLERRM);

end; end if;

NET_BOOK_VALUE1 :=2 ;

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 l_pa_task_id_out

NUMBER; 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 l_msg_dummy

VARCHAR2(2000); 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_msg_count

=> l_msg_count,

p_msg_data

=> l_msg_data,

p_return_status => l_return_status);

PA_PROJECT_ASSETS_PUB.add_asset_assignment(p_api_version_number p_commit

=> FND_API.G_FALSE,

p_init_msg_list

=> FND_API.G_FALSE,

p_msg_count

=> l_msg_count,

p_msg_data

=> l_msg_data,

p_return_status p_pm_product_code

=> l_return_status, => 'Test Lagacy System',

p_pm_project_reference => 1, --v_pa_project_id, p_pa_project_id

=> t.pa_project_id, --12547,

=> '1.0',

p_pm_task_reference p_pa_task_id

=> t.pa_task_id, --170811, -- 170811,

=> t.pa_task_id, --170811,

p_pm_asset_reference

=> t.pa_project_asset_id_out --8582 --8574 --8561

, p_pa_project_asset_id p_pa_task_id_out

=> t.pa_project_asset_id_out, --8582 --8574 --8561 => 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' and a.segment1 is not null;

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; 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); */

END LOOP;

ELSE -- DBMS_OUTPUT.put_line('Sucessfully Update the task');

update xxx_asset_project_add_tl a set a.task_status = 'Sucessfully Update the task' || l_output || l_pa_task_id_out 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 a.segment1 is not null;

update CMPAK_ASSET_GEN_TL b set Task_Status = 'Sucessfully Update the task', b.record_status ='SUCCESS' 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; END IF; EXCEPTION /* WHEN api_error THEN DBMS_OUTPUT.put_line('API Error in Project: ');*/ WHEN OTHERS THEN

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;

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' and a.segment1 is not null;

lc_e_msg := l_output; fnd_message.set_name('PA', lc_e_msg); lc_mesg := fnd_message.get; raise_application_error(-20001, lc_mesg); ---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 ;