[Home] [Help]
243: l_event_id NUMBER := NULL;
244: l_return_status VARCHAR2(1);
245: l_msg_code VARCHAR2(100);
246: l_processing_code VARCHAR2(12000);
247: l_event_rec pa_pji_proj_events_log%ROWTYPE;
248:
249: BEGIN
250:
251: Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
327: BEGIN
328:
329: SELECT event_object
330: INTO l_plan_version_id
331: FROM PA_PJI_PROJ_EVENTS_LOG
332: WHERE event_id = p_event_id;
333:
334: EXCEPTION
335: WHEN OTHERS THEN
444: , created_by
445: , last_update_login)
446: VALUES
447: ( 'Create'
448: , pa_pji_proj_events_log_s.NEXTVAL
449: , 'x'
450: , 'x'
451: , 'x'
452: , SYSDATE
643: , created_by
644: , last_update_login)
645: VALUES
646: ( 'Update'
647: , pa_pji_proj_events_log_s.NEXTVAL
648: , 'x'
649: , 'x'
650: , 'x'
651: , SYSDATE
1211: , created_by
1212: , last_update_login)
1213: VALUES
1214: ( 'Update ACT ETC'
1215: , pa_pji_proj_events_log_s.NEXTVAL
1216: , 'x'
1217: , 'x'
1218: , 'x'
1219: , SYSDATE
1414: l_processing_code VARCHAR2(100);
1415: l_msg_code VARCHAR2(100);
1416: l_s_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1417: l_d_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1418: l_event_rec pa_pji_proj_events_log%ROWTYPE;
1419:
1420: l_num_src_fp_ver_ids NUMBER := NULL;
1421: l_num_dest_fp_ver_ids NUMBER := NULL;
1422: l_num_src_fp_ver_types NUMBER := NULL;
1625: print_time ( ' plan copy PVT api .. 001 ' );
1626:
1627: SELECT attribute1, event_object, attribute2
1628: INTO l_s_plan_version_id, l_d_plan_version_id, l_working_or_baselined
1629: FROM PA_PJI_PROJ_EVENTS_LOG
1630: WHERE event_id = p_event_id;
1631:
1632: l_s_fp_version_ids := SYSTEM.pa_num_tbl_type (l_s_plan_version_id);
1633: l_d_fp_version_ids := SYSTEM.pa_num_tbl_type (l_d_plan_version_id);
1700: l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1701: l_return_status VARCHAR2(1);
1702: l_msg_code VARCHAR2(100);
1703: l_event_id NUMBER;
1704: l_event_rec pa_pji_proj_events_log%ROWTYPE;
1705: l_project_id NUMBER := NULL;
1706: l_curr_bl_ver_id NUMBER := NULL;
1707: l_plan_type_id NUMBER := NULL;
1708: l_new_project_type pa_projects_all.project_type%TYPE;
1933: , l_new_version_id
1934: , l_old_baseline_version_id
1935: , l_plan_type_id
1936: , l_plan_type_code /*4771527*/
1937: FROM PA_PJI_PROJ_EVENTS_LOG
1938: WHERE event_id = p_event_id;
1939:
1940:
1941: l_dest_fp_version_ids := SYSTEM.pa_num_tbl_type (l_new_version_id);
2043: l_wp_flag VARCHAR2(1);
2044: l_return_status VARCHAR2(1);
2045: l_msg_code VARCHAR2(100);
2046: l_event_id NUMBER;
2047: l_event_rec pa_pji_proj_events_log%ROWTYPE;
2048: l_project_id NUMBER := NULL;
2049: l_curr_or_ver_id NUMBER := NULL;
2050: l_plan_type_id NUMBER := NULL;
2051: l_plan_type_code char(1);
2169: l_wp_flag VARCHAR2(1);
2170: l_return_status VARCHAR2(1);
2171: l_msg_code VARCHAR2(100);
2172: l_event_id NUMBER;
2173: l_event_rec pa_pji_proj_events_log%ROWTYPE;
2174: l_project_id NUMBER := NULL;
2175: l_old_orig_ver_id NUMBER := NULL;
2176: l_new_orig_ver_id NUMBER := NULL;
2177: l_plan_type_id NUMBER := NULL;
2199: -----------------------------------------------------------------------------------
2200:
2201: SELECT attribute1, attribute3, event_object, attribute2,attribute4
2202: INTO l_project_id, l_old_orig_ver_id, l_new_orig_ver_id, l_plan_type_id,l_plan_type_code /*4771527 */
2203: FROM PA_PJI_PROJ_EVENTS_LOG
2204: WHERE event_id = p_event_id;
2205:
2206:
2207: ----------------------------------------------------------------------------------------
2323: p_prg_grp_id IN NUMBER,
2324: x_return_status OUT NOCOPY VARCHAR2,
2325: x_msg_code OUT NOCOPY VARCHAR2 ) IS
2326:
2327: l_event_rec pa_pji_proj_events_log%ROWTYPE;
2328: l_return_status VARCHAR2(1);
2329: l_msg_code VARCHAR2(100);
2330:
2331: BEGIN
2368: , p_program_flag IN VARCHAR2 DEFAULT 'N'
2369: , x_return_status OUT NOCOPY VARCHAR2
2370: , x_msg_code OUT NOCOPY VARCHAR2 ) IS
2371:
2372: l_event_rec pa_pji_proj_events_log%ROWTYPE;
2373: l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2374: l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2375: l_return_status VARCHAR2(1);
2376: l_msg_code VARCHAR2(100);
2462: , p_project_id IN NUMBER
2463: , x_return_status OUT NOCOPY VARCHAR2
2464: , x_msg_code OUT NOCOPY VARCHAR2 ) IS
2465:
2466: l_event_rec pa_pji_proj_events_log%ROWTYPE;
2467: l_fp_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2468: l_event_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2469: l_return_status VARCHAR2(1);
2470: l_msg_code VARCHAR2(100);
2520: END;
2521:
2522:
2523:
2524: PROCEDURE CREATE_EVENT( p_event_rec IN OUT NOCOPY pa_pji_proj_events_log%ROWTYPE) IS
2525: -- l_event_rec pa_pji_proj_events_log%ROWTYPE;
2526: event_already_exists VARCHAR2(1) := 'N'; -- Added for Bug#6145813 by vvjoshi
2527: l_rbs_header_id PA_RBS_VERSIONS_B.rbs_header_id%TYPE; -- Added for Bug#6145813 by vvjoshi
2528: l_return_status VARCHAR2(1);
2521:
2522:
2523:
2524: PROCEDURE CREATE_EVENT( p_event_rec IN OUT NOCOPY pa_pji_proj_events_log%ROWTYPE) IS
2525: -- l_event_rec pa_pji_proj_events_log%ROWTYPE;
2526: event_already_exists VARCHAR2(1) := 'N'; -- Added for Bug#6145813 by vvjoshi
2527: l_rbs_header_id PA_RBS_VERSIONS_B.rbs_header_id%TYPE; -- Added for Bug#6145813 by vvjoshi
2528: l_return_status VARCHAR2(1);
2529: BEGIN
2535: p_event_rec.created_by := Fnd_Global.USER_ID;
2536: p_event_rec.last_update_login := Fnd_Global.LOGIN_ID;
2537:
2538: IF (p_event_rec.event_id IS NULL) THEN
2539: SELECT pa_pji_proj_events_log_s.NEXTVAL
2540: INTO p_event_rec.event_id
2541: FROM DUAL;
2542: END IF;
2543:
2548: begin
2549: select 'Y'
2550: into event_already_exists
2551: from (select attribute2
2552: from pa_pji_proj_events_log
2553: where event_type = 'RBS_PUSH'
2554: )a ,
2555: pa_rbs_versions_b b
2556: where to_number(a.attribute2) = b.rbs_version_id
2572: into l_rbs_header_id
2573: from pa_rbs_versions_b
2574: where rbs_version_id = to_number(p_event_rec.attribute2);
2575:
2576: UPDATE pa_pji_proj_events_log
2577: SET event_object = p_event_rec.event_object,
2578: last_update_date = p_event_rec.last_update_date,
2579: last_updated_by = p_event_rec.last_updated_by
2580: WHERE event_type = 'RBS_PUSH'
2584:
2585: ELSE
2586: /* Added for Bug#6145813 by vvjoshi - END*/
2587:
2588: INSERT INTO pa_pji_proj_events_log
2589: ( event_type
2590: , event_id
2591: , event_object
2592: , operation_type
2655: END IF; -- Added for Bug#6145813 by vvjoshi
2656:
2657: -- p_event_rec := l_event_rec ;
2658:
2659: -- INSERT INTO pa_pji_proj_events_log
2660: -- VALUES l_event_rec;
2661:
2662: EXCEPTION
2663: WHEN OTHERS THEN
3440: p_event_object NUMBER
3441: , p_operation_type VARCHAR2
3442: ) IS
3443: SELECT event_id
3444: FROM pa_pji_proj_events_log
3445: WHERE event_object = p_event_object
3446: AND operation_type = p_operation_type;
3447:
3448: BEGIN
3489:
3490: print_time ( ' delete events 001 ' );
3491:
3492: FORALL i IN p_event_ids.FIRST..p_event_ids.LAST
3493: DELETE FROM pa_pji_proj_events_log
3494: WHERE event_id = p_event_ids(i);
3495:
3496: print_time ( ' delete events 002 ' );
3497:
3813: SELECT
3814: head.PLAN_VERSION_ID
3815: FROM
3816: pji_pjp_wbs_header head,
3817: PA_PJI_PROJ_EVENTS_LOG elog
3818: WHERE
3819: elog.EVENT_ID = l_event_id AND
3820: head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3821: head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3833: SELECT
3834: head.PLAN_VERSION_ID
3835: FROM
3836: pji_pjp_wbs_header head,
3837: PA_PJI_PROJ_EVENTS_LOG elog
3838: WHERE
3839: elog.EVENT_ID = l_event_id AND
3840: head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3841: head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3863: index(prg PJI_XBS_DENORM_N1) */
3864: prg.SUP_PROJECT_ID,
3865: prg.SUP_ID
3866: from
3867: PA_PJI_PROJ_EVENTS_LOG log,
3868: PJI_PJP_WBS_HEADER sub_wbs_hdr,
3869: PA_PROJ_ELEM_VER_STRUCTURE ppevs2,
3870: PJI_XBS_DENORM prg
3871: where
3925: sub_wbs_hdr.PLAN_VERSION_ID,
3926: sub_wbs_hdr.PLAN_TYPE_ID,
3927: sub_wbs_hdr.PLAN_TYPE_CODE
3928: from
3929: PA_PJI_PROJ_EVENTS_LOG log,
3930: PJI_PJP_WBS_HEADER wbs_hdr,
3931: PJI_PJP_WBS_HEADER sub_wbs_hdr,
3932: PJI_XBS_DENORM prg
3933: where
3953: SELECT
3954: head.PLAN_VERSION_ID
3955: FROM
3956: pji_pjp_wbs_header head,
3957: PA_PJI_PROJ_EVENTS_LOG elog
3958: WHERE
3959: head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3960: head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3961: head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
3969: SELECT
3970: head.PLAN_VERSION_ID
3971: FROM
3972: pji_pjp_wbs_header head,
3973: PA_PJI_PROJ_EVENTS_LOG elog
3974: WHERE
3975: head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
3976: head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
3977: head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
3995: , PA_PROJ_ELEMENTS ppe2
3996: , PA_PROJ_STRUCTURE_TYPES ppst2
3997: , PA_STRUCTURE_TYPES pst2
3998: , PA_PROJ_ELEM_VER_STRUCTURE ppevs2
3999: , PA_PJI_PROJ_EVENTS_LOG elog
4000: WHERE
4001: den.struct_version_id IS NULL
4002: AND hd2.wbs_version_id = den.sub_id -- struct_version_id
4003: AND hd1.wbs_version_id = den.sup_id
4079: INTO
4080: l_project_id,
4081: l_struct_version_id
4082: FROM
4083: pa_pji_proj_events_log elog
4084: WHERE
4085: elog.EVENT_ID = p_event_id AND
4086: ROWNUM <= 1;
4087:
4107: WHERE
4108: head.PLAN_VERSION_ID IN (SELECT
4109: TO_NUMBER(elog.ATTRIBUTE3)
4110: FROM
4111: pa_pji_proj_events_log elog
4112: WHERE
4113: head.PROJECT_ID = TO_NUMBER(elog.ATTRIBUTE1) AND
4114: head.WBS_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE2) AND
4115: head.PLAN_VERSION_ID = TO_NUMBER(elog.ATTRIBUTE3) AND
4189: l_project_id,
4190: l_new_wbs_version_id,
4191: l_old_wbs_version_id
4192: FROM
4193: pa_pji_proj_events_log elog
4194: WHERE
4195: elog.EVENT_ID = p_event_id AND
4196: ROWNUM <= 1;
4197: IF g_debug_mode='Y' THEN
4240: and log.event_object =to_char(ver.prg_group)
4241: and ver.project_id=l_project_id
4242: union all
4243: select log.event_id
4244: from pa_pji_proj_events_log log,pa_proj_element_versions ver
4245: where log.event_type='PRG_CHANGE'
4246: and log.event_object =to_char(ver.prg_group)
4247: and ver.project_id=l_project_id);
4248: else
4249: select 1
4250: into l_prg_event_id
4251: from dual
4252: where exists (select log.event_id
4253: from pa_pji_proj_events_log log,pa_proj_element_versions ver
4254: where log.event_type='PRG_CHANGE'
4255: and log.event_object =to_char(ver.prg_group)
4256: and ver.project_id=l_project_id);
4257:
4498: x_msg_count => l_msg_count,
4499: x_msg_data => l_msg_data );
4500: */
4501: DELETE
4502: FROM pa_pji_proj_events_log LOG
4503: WHERE LOG.EVENT_ID = p_event_id;
4504:
4505: cleanup_temp_tables;
4506: /* bug5353559*/
4660: INTO
4661: l_project_id,
4662: l_struct_version_id
4663: FROM
4664: pa_pji_proj_events_log elog
4665: WHERE
4666: elog.EVENT_ID = p_event_id AND
4667: ROWNUM <= 1;
4668:
4787: --------------------------------------
4788: --The first step is to LOG the event
4789: -------------------------------------
4790: --Initialize Event sequence
4791: SELECT pa_pji_proj_events_log_s.NEXTVAL
4792: INTO l_event_id
4793: FROM sys.dual;
4794:
4795: IF l_versioned_flag = 'N' THEN
4794:
4795: IF l_versioned_flag = 'N' THEN
4796: --CASE 1: Non-Versioned (Work plans + actuals + fin plans)
4797:
4798: INSERT INTO pa_pji_proj_events_log (
4799: EVENT_TYPE,
4800: EVENT_ID,
4801: EVENT_OBJECT,
4802: OPERATION_TYPE,
4829: head.PROJECT_ID = p_project_id AND
4830: head.WBS_VERSION_ID = p_old_struct_ver_id;
4831:
4832: IF SQL%rowcount = 0 THEN
4833: INSERT INTO pa_pji_proj_events_log (
4834: EVENT_TYPE,
4835: EVENT_ID,
4836: EVENT_OBJECT,
4837: OPERATION_TYPE,
4912: (l_versioned_flag = 'Y' AND l_shared_flag = 'Y' AND
4913: p_publish_flag = 'N') OR
4914: l_struct_type = 'WORKPLAN') THEN
4915: --CASE 2: Only Workplans
4916: INSERT INTO pa_pji_proj_events_log (
4917: EVENT_TYPE,
4918: EVENT_ID,
4919: EVENT_OBJECT,
4920: OPERATION_TYPE,
4948: head.WBS_VERSION_ID = p_old_struct_ver_id AND
4949: head.WP_FLAG = 'Y';
4950:
4951: IF SQL%rowcount = 0 THEN
4952: INSERT INTO pa_pji_proj_events_log (
4953: EVENT_TYPE,
4954: EVENT_ID,
4955: EVENT_OBJECT,
4956: OPERATION_TYPE,
5013: ELSIF ((l_versioned_flag = 'Y' AND l_shared_flag = 'Y' AND
5014: p_publish_flag = 'Y') OR
5015: l_struct_type = 'FINANCIAL' ) THEN
5016: --CASE 3: Only for Financial plans and Actuals
5017: INSERT INTO pa_pji_proj_events_log (
5018: EVENT_TYPE,
5019: EVENT_ID,
5020: EVENT_OBJECT,
5021: OPERATION_TYPE,
5049: head.WBS_VERSION_ID = p_old_struct_ver_id AND
5050: head.WP_FLAG = 'N';
5051:
5052: IF SQL%rowcount = 0 THEN
5053: INSERT INTO pa_pji_proj_events_log (
5054: EVENT_TYPE,
5055: EVENT_ID,
5056: EVENT_OBJECT,
5057: OPERATION_TYPE,
5186: l_project_id,
5187: l_publish_flag,
5188: l_struct_version_id
5189: FROM
5190: pa_pji_proj_events_log elog
5191: WHERE
5192: elog.EVENT_ID = p_event_id AND
5193: ROWNUM <= 1;
5194: EXCEPTION
5325: DISTINCT
5326: elog.EVENT_TYPE,
5327: elog.EVENT_ID
5328: FROM
5329: pa_pji_proj_events_log elog
5330: WHERE
5331: elog.EVENT_TYPE IN ('WBS_CHANGE',
5332: 'WBS_PUBLISH',
5333: 'PLAN_DELETE',
5414: TO_NUMBER(elog.ATTRIBUTE1)
5415: INTO
5416: l_project_id
5417: FROM
5418: pa_pji_proj_events_log elog
5419: WHERE
5420: elog.EVENT_ID = p_event_id AND
5421: ROWNUM <= 1;
5422: EXCEPTION
5548: SELECT
5549: evt.EVENT_ID,
5550: evt.EVENT_TYPE
5551: FROM
5552: pa_pji_proj_events_log evt
5553: WHERE
5554: evt.EVENT_TYPE IN ( 'PLAN_DELETE',
5555: 'PLAN_BASELINE',
5556: 'PLAN_ORIGINAL',
5563: SELECT
5564: evt.EVENT_ID,
5565: evt.EVENT_TYPE
5566: FROM
5567: pa_pji_proj_events_log evt,
5568: pji_pjp_wbs_header head
5569: WHERE
5570: evt.EVENT_TYPE IN ( 'WBS_CHANGE',
5571: 'WBS_PUBLISH') AND