DECLARE
l_model_id NUMBER;
l_user_id number;
CURSOR ui_name_cur
IS
SELECT ui_def_id,
name
FROM cz_ui_Defs
WHERE devl_project_id=l_model_id
AND deleted_flag =0
AND name LIKE 'MODEL%SUBTAB_UI';
CURSOR pub_data_cur
IS
SELECT publication_id,
cs.instance_name
FROM cz_model_publications mp,
cz_servers cs
WHERE object_id =l_model_id
AND source_target_flag='S'
AND deleted_Flag =0
AND disabled_flag =0
AND mp.server_id =cs.server_local_id
AND cs.instance_name ='MYINSTANCE' ;
CURSOR fnd_user_data_cur
IS
SELECT user_id from fnd_user where user_name = 'PANKAJ_MANDALIYA';
CURSOR model_data_cur
IS
SELECT pn.name model_name,
dp.name Description,
dp.checkout_time,
dp.checkout_user,
rp.enclosing_folder folder_id,
pn.devl_project_id model_id
FROM cz_ps_nodes pn,
cz_rp_entries rp,
cz_devl_projects dp
WHERE pn.ps_node_id = rp.object_id
AND rp.deleted_flag = 0
AND rp.object_type = 'PRJ'
AND dp.devl_project_id=pn.devl_project_id
AND dp.devl_project_id=rp.object_id
AND dp.deleted_flag =0
AND dp.name NOT LIKE 'Copy%'
AND rp.name NOT LIKE 'Copy%'
AND (pn.name LIKE 'MODEL1%')
AND pn.deleted_flag = 0
ORDER BY pn.name;
v_model_data model_data_cur%rowtype;
v_ui_data ui_name_cur%rowtype;
v_pub_data pub_data_cur%rowtype;
l_request_id NUMBER;
v_return_status VARCHAR2(10);
v_msg_count number;
v_msg_data VARCHAR2(255);
v_output_line VARCHAR2(512);
v_ui_def_id NUMBER;
v_new_pub_id number;
BEGIN
-- Initialize Apps
OPEN fnd_user_data_cur;
FETCH fnd_user_data_cur INTO L_USER_ID;
fnd_global.apps_initialize (L_USER_ID ,22687 ,708 );
OPEN model_data_cur;
FETCH model_data_cur INTO v_model_data;
WHILE model_data_cur%FOUND
LOOP
l_model_id := v_model_data.model_id;
v_output_line := v_model_data.Model_Name || ',' || REPLACE(v_model_data.Description,',',' ') ||',' || v_model_data.Folder_ID || ',' || v_model_data.Model_ID ||',' || NVL(v_model_data.checkout_user, 'Unlocked') || ',' || v_model_data.checkout_time;
--DBMS_OUTPUT.PUT ('Model name '||v_model_data.Model_Name);
--DBMS_OUTPUT.PUT (', Folder '||v_model_data.Folder_ID);
--DBMS_OUTPUT.PUT_LINE (', Model ID is '||v_model_data.Model_ID);
-- l_request_id := fnd_request.submit_request ('CZ' ,--IN VARCHAR2 DEFAULT NULL, -- application
-- 'CZBOMSIM', -- IN VARCHAR2 DEFAULT NULL,-- program short name
-- '', -- description
-- '', -- start time
-- FALSE, -- IN BOOLEAN DEFAULT FALSE, -- sub request
-- 'MYINSTANCE', -- IN VARCHAR2 DEFAULT CHR (0), -- argument1
-- v_model_data.Folder_ID, -- IN VARCHAR2 DEFAULT CHR (0), -- argument2
-- v_model_data.Model_ID, -- IN VARCHAR2 DEFAULT CHR (0), -- argument3
-- CHR (0) -- represents end of arguments
-- );
-- COMMIT;
CZ_PB_MGR.IS_MODEL_UPTO_DATE( P_MODEL_ID =>l_model_id, X_RETURN_STATUS =>v_return_status, X_MSG_DATA =>v_msg_data);
IF v_return_status ='1' THEN
v_output_line := v_output_line || ',' || 'Logic is upto date';
ELSE
v_output_line := v_output_line || ',' || 'Generate Logic is Required';
END IF;
OPEN ui_name_cur;
FETCH ui_name_cur INTO v_ui_data;
IF ui_name_cur%NOTFOUND THEN
v_output_line := v_output_line || ',' || 'UI Not Found';
ELSE
CZ_PB_MGR.is_ui_upto_date (p_ui_def_id => v_ui_data.ui_def_id, X_RETURN_STATUS =>v_return_status, X_MSG_DATA =>v_msg_data);
IF v_return_status<>'0' THEN
v_output_line := v_output_line || ',' || v_ui_data.name || ' Requires UI Refresh';
ELSE
v_output_line := v_output_line || ',' || v_ui_data.name || ' is upto date';
END IF;
END IF;
CLOSE ui_name_cur;
OPEN pub_data_cur;
FETCH pub_data_cur INTO v_pub_data;
IF pub_data_cur%NOTFOUND THEN
v_output_line := v_output_line || ',' || 'No Existing Publication Found';
ELSE
--cz_pb_mgr.create_republish_publication(p_publication_id=> v_pub_data.publication_id, x_new_publication_id=> v_new_pub_id, x_return_status=>v_return_status, x_msg_count=>v_msg_count, x_msg_data=>v_msg_data);
IF v_return_status<>FND_API.G_RET_STS_SUCCESS THEN
v_output_line := v_output_line || ',' || 'Republish Failed:' || v_msg_data;
ELSE
v_output_line := v_output_line || ',' || v_new_pub_id;
--run publication concurrent
END IF;
END IF;
CLOSE pub_data_cur;
dbms_output.put_line(v_output_line);
FETCH model_data_cur INTO v_model_data;
END LOOP;
CLOSE model_data_cur;
END;
------------------------------------------------------------------------------------------
