31:
32: --needed to get the field values associated to a particular project
33:
34: CURSOR l_project_csr
35: (p_pa_project_id pa_projects.project_id%type)
36: IS
37: SELECT *
38: FROM pa_projects p
39: WHERE p.project_id = p_pa_project_id;
34: CURSOR l_project_csr
35: (p_pa_project_id pa_projects.project_id%type)
36: IS
37: SELECT *
38: FROM pa_projects p
39: WHERE p.project_id = p_pa_project_id;
40:
41: /*new cursor for bug no 2413400*/
42: Cursor p_product_code_csr (p_pm_product_code IN VARCHAR2)
168:
169: --needed for validation of distribution rule
170: CURSOR l_source_project_type_csr (p_source_template_id IN NUMBER )IS
171: SELECT project_type
172: FROM pa_projects
173: WHERE project_id = p_source_template_id;
174:
175: CURSOR l_get_wf_status_csr (l_project_id IN NUMBER)
176: IS
174:
175: CURSOR l_get_wf_status_csr (l_project_id IN NUMBER)
176: IS
177: SELECT wf_status_code
178: FROM pa_projects
179: WHERE project_id = l_project_id;
180:
181: -- IC and Multi National Currency changes Starts ...
182:
197: -- below cursor added for bug 2254661
198: Cursor l_temp_flag_csr(p_proj_id NUMBER)
199: Is
200: select NVL(template_flag,'N')
201: from pa_projects
202: where project_id = p_proj_id;
203:
204: -- 5182868 Added below cursor to validate template for effectiveness
205:
205:
206: Cursor l_temp_eff_flag_csr(p_proj_id NUMBER)
207: Is
208: select 'N'
209: from pa_projects_all
210: where project_id = p_proj_id
211: and template_flag = 'Y'
212: and ((nvl(trunc(template_start_date_active), trunc(sysdate)) > trunc(sysdate)) or
213: (nvl(trunc(template_end_date_active), trunc(sysdate)) < trunc(sysdate)));
239: i NUMBER := 0; --counter
240: j NUMBER := 0; --counter
241: l_is_overridable BOOLEAN := FALSE;
242: l_project_type_rec pa_project_types%rowtype;
243: l_project_rec pa_projects%rowtype;
244: l_project_in_rec project_in_rec_type;
245: l_override_fields_rec l_override_fields_csr%rowtype;
246: l_override_fields_tab override_fields_tbl_type;
247: l_key_members project_role_tbl_type;
431:
432: --Bug 3279981
433: CURSOR cur_get_orig_tt_cust_flag( c_project_id IN NUMBER ) IS
434: SELECT enable_top_task_customer_flag
435: FROM pa_projects_all
436: WHERE project_id = c_project_id ;
437:
438: l_orig_def_tt_flag VARCHAR2(1);
439: l_cust_bill_split NUMBER;
462: -- Commented this cursor for Performance Bug 4878696
463: -- CURSOR c_prj(c_project_id IN NUMBER ) IS
464: -- select project_system_status_code
465: -- ,project_type_class_code
466: -- from pa_projects_v
467: -- where project_id = c_project_id;
468:
469: -- Added the following cursor instead of the commented one as above for Performance Bug 4878696
470: CURSOR c_prj(c_project_id IN NUMBER ) IS
468:
469: -- Added the following cursor instead of the commented one as above for Performance Bug 4878696
470: CURSOR c_prj(c_project_id IN NUMBER ) IS
471: select PROJECT_SYSTEM_STATUS_CODE , PROJECT_TYPE_CLASS_CODE
472: from pa_projects_all ppa,pa_project_statuses pps,pa_project_types ppt
473: where ppa.project_id = c_project_id
474: and ppa.PROJECT_STATUS_CODE = pps.PROJECT_STATUS_CODE
475: and ppa.PROJECT_TYPE = ppt.PROJECT_TYPE
476: and pps.STATUS_TYPE = 'PROJECT'
483: from pa_project_players
484: where project_id = c_project_id
485: and project_role_type='PROJECT MANAGER';
486:
487: l_proj_status pa_projects_all.project_status_code%TYPE;
488: l_proj_type_class pa_project_types_all.project_type_class_code%TYPE;
489: l_prj_mgr_count NUMBER;
490: /* End of code for bug #2111806. */
491:
550: SELECT
551: NULL
552: FROM
553: pa_probability_members ppm,
554: pa_projects pp,
555: pa_project_types ppt
556: WHERE
557: pp.project_type = ppt.project_type AND
558: pp.org_id = ppt.org_id AND
571: IS
572: SELECT
573: nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
574: FROM
575: pa_projects_all
576: WHERE project_id = c_project_id ;
577:
578: /* 7508661 : EnC : Start */
579:
584: IS
585: SELECT
586: nvl(AR_REC_NOTIFY_FLAG,'N')
587: FROM
588: pa_projects_all
589: WHERE project_id = c_project_id ;
590: /* Added for 12.2Payroll billing ER 11899223 */
591: l_auto_release_pwp_inv VARCHAR2(1);
592: l_orig_auto_release_pwp_inv VARCHAR2(1);
599: IS
600: SELECT
601: nvl(adj_on_std_inv,'N')
602: FROM
603: pa_projects_all
604: WHERE project_id = c_project_id ;
605:
606: CURSOR get_bill_labor_accrual( c_project_id IN NUMBER )
607: IS
607: IS
608: SELECT
609: nvl(bill_labor_accrual,'N')
610: FROM
611: pa_projects_all
612: WHERE project_id = c_project_id ;
613:
614: /* Added for 12.2Payroll billing ER 11899223 */
615: CURSOR get_auto_release_pwp_inv( c_project_id IN NUMBER )
616: IS
617: SELECT
618: nvl(AUTO_RELEASE_PWP_INV,'Y')
619: FROM
620: pa_projects_all
621: WHERE project_id = c_project_id ;
622:
623: /* 7508661 : EnC : End */
624:
625: CURSOR cur_enable_funds_flag(p_project_id IN VARCHAR2)
626: IS
627: SELECT 'Y'
628: FROM
629: pa_projects_all p,
630: pa_project_types_all pt
631: WHERE
632: p.project_id = p_project_id AND
633: pt.project_type_class_code = 'CONTRACT' AND
691:
692: /*Added for bug 7830143*/
693: CURSOR c_get_org_id(c_project_id NUMBER) IS
694: select org_id
695: from pa_projects_all
696: where project_id = c_project_id;
697:
698: l_org_id NUMBER ;
699: /*Added for bug 7830143*/
1199:
1200: l_created_from_proj_id := l_project_in_rec.created_from_project_id;
1201: else
1202: select created_from_project_id into l_created_from_proj_id
1203: from pa_projects_all
1204: where project_id = l_project_in_rec.created_from_project_id;
1205: end if;
1206: --code addition for bug 2254661 ends
1207:
3410: END IF;
3411: end if; -- end of 14539941
3412:
3413: /* Not required anymore since they are passed as input to copy project
3414: UPDATE pa_projects
3415: SET pm_project_reference = l_project_in_rec.pm_project_reference
3416: , pm_product_code = p_pm_product_code
3417: WHERE project_id = l_project_id;
3418: */
4145:
4146: PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
4147: p_validate_only => FND_API.G_FALSE
4148: , p_object_id => l_project_id
4149: , p_OBJECT_TYPE => 'PA_PROJECTS'
4150: , p_project_role_id => 100
4151: , p_project_role_type => 'CUSTOMER_ORG'
4152: , p_RESOURCE_TYPE_ID => 112
4153: , p_resource_source_id => l_party_id
5460: --end: cklee fix bug: 9065594
5461:
5462: -- Bug 7335526
5463: IF G_PROJECT_NUMBER_GEN_MODE = 'AUTOMATIC' THEN
5464: PA_UTILS_SQNUM_PKG.revert_unique_proj_num(p_table_name => 'PA_PROJECTS',
5465: p_user_id => FND_GLOBAL.USER_ID,
5466: p_unique_number => to_number(l_project_number_out));
5467: END IF;
5468:
5486: --end: cklee fix bug: 9065594
5487:
5488: -- Bug 7335526
5489: IF G_PROJECT_NUMBER_GEN_MODE = 'AUTOMATIC' THEN
5490: PA_UTILS_SQNUM_PKG.revert_unique_proj_num(p_table_name => 'PA_PROJECTS',
5491: p_user_id => FND_GLOBAL.USER_ID,
5492: p_unique_number => to_number(l_project_number_out));
5493: END IF;
5494:
5512: --end: cklee fix bug: 9065594
5513:
5514: -- Bug 7335526
5515: IF G_PROJECT_NUMBER_GEN_MODE = 'AUTOMATIC' THEN
5516: PA_UTILS_SQNUM_PKG.revert_unique_proj_num(p_table_name => 'PA_PROJECTS',
5517: p_user_id => FND_GLOBAL.USER_ID,
5518: p_unique_number => to_number(l_project_number_out));
5519: END IF;
5520:
5756: l_d_struct_ver_id NUMBER;
5757:
5758: --custom parameters
5759: l_project_type_rec pa_project_types%rowtype;
5760: l_project_rec pa_projects%rowtype;
5761: l_task_in_rec task_in_rec_type;
5762: l_project_id pa_projects.project_id%type;
5763: l_task_id pa_tasks.task_id%type;
5764: l_msg_count NUMBER ;
5758: --custom parameters
5759: l_project_type_rec pa_project_types%rowtype;
5760: l_project_rec pa_projects%rowtype;
5761: l_task_in_rec task_in_rec_type;
5762: l_project_id pa_projects.project_id%type;
5763: l_task_id pa_tasks.task_id%type;
5764: l_msg_count NUMBER ;
5765: l_msg_data VARCHAR2(2000);
5766: l_function_allowed VARCHAR2(1);
5773: l_customer_name VARCHAR2(360); -- Added for Bug 4689888 -- Modified to VARCHAR2(360) for bug 5856469
5774: --needed to get the field values associated to a AMG message
5775:
5776: CURSOR l_amg_project_csr
5777: (p_pa_project_id pa_projects.project_id%type)
5778: IS
5779: SELECT segment1
5780: FROM pa_projects p
5781: WHERE p.project_id = p_pa_project_id;
5776: CURSOR l_amg_project_csr
5777: (p_pa_project_id pa_projects.project_id%type)
5778: IS
5779: SELECT segment1
5780: FROM pa_projects p
5781: WHERE p.project_id = p_pa_project_id;
5782:
5783: --Added for bug 3280610
5784: --needed to get task_id of a task using project_id and task_reference.
5794: --Bug 3279981. Fetch project information
5795: CURSOR cur_get_proj_billing_info IS
5796: SELECT enable_top_task_customer_flag, enable_top_task_inv_mth_flag,
5797: revenue_accrual_method, invoice_method, project_type
5798: FROM pa_projects_all
5799: WHERE project_id = p_pa_project_id;
5800:
5801: CURSOR cur_check_proj_cust_exist (c_customer_id IN NUMBER ) IS
5802: SELECT 'Y'
7307: END IF; --<< n_structure_type >>
7308:
7309: p_task_id := l_task_id;
7310: p_pa_project_id_out := l_project_rec.project_id;
7311: p_pa_project_number_out := l_project_rec.segment1; --this is the project_number field in PA_PROJECTS
7312:
7313: --Begin Add by rtarway FP.M Development
7314: G_PROJECT_SHARING_TYPE := PA_PROJ_TASK_STRUC_PUB.GET_SHARE_TYPE(l_project_id);
7315: IF (G_PROJECT_SHARING_TYPE='SPLIT_MAPPING' AND l_structure_type = 'WORKPLAN' ) THEN
9820: CURSOR l_project_csr
9821: (l_pa_project_id NUMBER)
9822: IS
9823: SELECT *
9824: FROM pa_projects p
9825: WHERE p.project_id = l_pa_project_id;
9826:
9827: /**************************************
9828: ** The following cursor l_get_org_csr is not used anywhere in this
9869: , pt.service_type_code
9870: , pt.revaluate_funding_flag /*Bug 2891513 : selecting the columns revaluate_funding_flag and include_gains_losses_flag*/
9871: , pt.include_gains_losses_flag
9872: FROM pa_project_types pt
9873: , pa_projects p
9874: WHERE p.project_type = pt.project_type
9875: AND p.project_id = p_pa_project_id;
9876:
9877: --needed to check whether given project number is unique
9877: --needed to check whether given project number is unique
9878: CURSOR l_project_number_unique_csr( p_project_number VARCHAR2)
9879: IS
9880: SELECT 'x'
9881: FROM pa_projects_all
9882: WHERE segment1 = p_project_number;
9883:
9884: --needed to check whether given project name is unique
9885: CURSOR l_project_name_unique_csr( p_project_name VARCHAR2)
9884: --needed to check whether given project name is unique
9885: CURSOR l_project_name_unique_csr( p_project_name VARCHAR2)
9886: IS
9887: SELECT 'x'
9888: FROM pa_projects_all
9889: WHERE name = p_project_name;
9890:
9891: --needed to check whether given long name is unique
9892: CURSOR l_long_name_unique_csr( p_long_name VARCHAR2)
9891: --needed to check whether given long name is unique
9892: CURSOR l_long_name_unique_csr( p_long_name VARCHAR2)
9893: IS
9894: SELECT 'x'
9895: FROM pa_projects_all
9896: WHERE long_name = p_long_name;
9897:
9898: /* Modidified the cursor l_key_member_csr to add start_date_active as logical
9899: primary key */
9949: /* bug 4541103
9950: CURSOR l_lock_rows_csr( p_project_id NUMBER)
9951: IS
9952: SELECT 'x'
9953: FROM pa_projects p
9954: WHERE p.project_id = p_project_id
9955: FOR UPDATE OF p.project_id NOWAIT;
9956: */
9957:
10013: NULL USER_NAME,
10014: -999 USER_ID,
10015: ppp.resource_source_id RESOURCE_PARTY_ID
10016: FROM pa_project_parties ppp,
10017: pa_projects_all ppa,
10018: pa_project_role_types_vl pprt,
10019: hz_parties hzo,
10020: hz_contact_points hzcp
10021: WHERE ppp.resource_type_id = 112
10075: NULL USER_NAME,
10076: -999 USER_ID,
10077: ppp.resource_source_id RESOURCE_PARTY_ID
10078: FROM pa_project_parties ppp,
10079: pa_projects_all ppa,
10080: pa_project_role_types_vl pprt,
10081: hz_parties hzo
10082: WHERE ppp.resource_type_id = 112
10083: AND ppp.project_id = ppa.project_id
10092:
10093: -- 3693229 end
10094:
10095: --Cursor to get the current published version when structure type and project id is known
10096: CURSOR cur_struc_ver_fin(c_project_id pa_projects_all.project_id%TYPE, c_structure_type pa_structure_types.structure_type%TYPE)
10097: IS
10098: SELECT c.element_version_id
10099: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
10100: ,pa_proj_elem_ver_structure d
10107: AND d.status_code = 'STRUCTURE_PUBLISHED'
10108: AND d.latest_eff_published_flag = 'Y';
10109:
10110: --Cursor to get the working version when structure type and project id is known
10111: CURSOR cur_struc_ver_wp(c_project_id pa_projects_all.project_id%TYPE, c_structure_type pa_structure_types.structure_type%TYPE)
10112: IS
10113: SELECT c.element_version_id
10114: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
10115: ,pa_proj_elem_ver_structure d
10123:
10124: -- Cursor to get the Project's System Status Code Bug 3611598
10125: CURSOR c_project_sys_status_code IS
10126: SELECT project_system_status_code
10127: FROM PA_PROJECT_STATUSES sta,PA_PROJECTS_ALL pa
10128: WHERE pa.project_id = p_project_in.pa_project_id
10129: AND sta.project_status_code = pa.project_status_code;
10130:
10131: Cursor c_adj_on_std_inv (p_adj_on_std_inv IN VARCHAR2)
10149: l_dummy_cur_output_tax_code VARCHAR2(1) :='N'; -- Added For Bug 5188246.
10150: l_status_code PA_PROJECT_STATUSES.project_system_status_code%TYPE; -- Bug 3611598
10151: l_deferred_tasks_index_tbl deferred_tasks_tbl_typ;
10152:
10153: l_project_rec pa_projects%ROWTYPE;
10154: l_project_in_rec pa_project_pub.project_in_rec_type;
10155: l_temp_org_id NUMBER;
10156: l_temp VARCHAR2(1);
10157:
10285: --added by rtarway FP.M Devlopment
10286: --needed to get the field values associated to a AMG message
10287:
10288: CURSOR l_amg_project_csr
10289: (p_pa_project_id pa_projects.project_id%type)
10290: IS
10291: SELECT segment1
10292: FROM pa_projects p
10293: WHERE p.project_id = p_pa_project_id;
10288: CURSOR l_amg_project_csr
10289: (p_pa_project_id pa_projects.project_id%type)
10290: IS
10291: SELECT segment1
10292: FROM pa_projects p
10293: WHERE p.project_id = p_pa_project_id;
10294:
10295: l_amg_segment1 VARCHAR2(25);
10296:
10498: -- Commented this cursor for Performance Bug 4878696
10499: -- CURSOR c_prj(c_project_id IN NUMBER ) IS
10500: -- select project_system_status_code
10501: -- ,project_type_class_code
10502: -- from pa_projects_v
10503: -- where project_id = c_project_id;
10504:
10505: -- Added the following cursor instead of the commented one as above for Performance Bug 4878696
10506: CURSOR c_prj(c_project_id IN NUMBER ) IS
10504:
10505: -- Added the following cursor instead of the commented one as above for Performance Bug 4878696
10506: CURSOR c_prj(c_project_id IN NUMBER ) IS
10507: select PROJECT_SYSTEM_STATUS_CODE , PROJECT_TYPE_CLASS_CODE
10508: from pa_projects_all ppa,pa_project_statuses pps,pa_project_types ppt
10509: where ppa.project_id = c_project_id
10510: and ppa.PROJECT_STATUS_CODE = pps.PROJECT_STATUS_CODE
10511: and ppa.PROJECT_TYPE = ppt.PROJECT_TYPE
10512: and pps.STATUS_TYPE = 'PROJECT'
10519: from pa_project_players
10520: where project_id = c_project_id
10521: and project_role_type='PROJECT MANAGER';
10522:
10523: l_proj_status pa_projects_all.project_status_code%TYPE;
10524: l_proj_type_class pa_project_types_all.project_type_class_code%TYPE;
10525: l_prj_mgr_count NUMBER;
10526: /* End of code for bug #2111806. */
10527:
10570:
10571:
10572: CURSOR l_get_details_for_opp_csr (c_project_id IN NUMBER) IS
10573: SELECT expected_approval_date, projfunc_currency_code, project_currency_code
10574: FROM pa_projects
10575: WHERE project_id = c_project_id;
10576:
10577: CURSOR l_get_details_for_opp_csr2 (c_project_id IN NUMBER) IS
10578: SELECT opportunity_value, opp_value_currency_code
10595: -- Bug # 4329284.
10596:
10597: cursor cur_proj_name (c_project_id NUMBER) is
10598: select ppa.name
10599: from pa_projects_all ppa
10600: where ppa.project_id = c_project_id;
10601:
10602: cursor cur_prog_name (c_project_id NUMBER) is
10603: select ppa.name
10600: where ppa.project_id = c_project_id;
10601:
10602: cursor cur_prog_name (c_project_id NUMBER) is
10603: select ppa.name
10604: from pa_projects_all ppa, pa_object_relationships por
10605: where ppa.project_id = por.object_id_to2
10606: and por.object_id_to2 = c_project_id
10607: and por.relationship_type in ('LW','LF')
10608: union all
10606: and por.object_id_to2 = c_project_id
10607: and por.relationship_type in ('LW','LF')
10608: union all
10609: select ppa.name
10610: from pa_projects_all ppa, pa_object_relationships por
10611: where ppa.project_id = por.object_id_from2
10612: and por.object_id_from2 = c_project_id
10613: and por.relationship_type in ('LW','LF');
10614:
10676: SELECT
10677: NULL
10678: FROM
10679: pa_probability_members ppm,
10680: pa_projects pp,
10681: pa_project_types ppt
10682: WHERE
10683: pp.project_type = ppt.project_type AND
10684: pp.org_id = ppt.org_id AND
10695: SELECT
10696: 'Y'
10697: FROM
10698: pa_work_types_vl pwt,
10699: pa_projects pp,
10700: pa_project_types ppt
10701: WHERE
10702: trunc(sysdate) BETWEEN pwt.start_date_active AND nvl(pwt.end_date_active,trunc(SYSDATE)) AND
10703: pp.project_type = ppt.project_type AND
10728: IS
10729: SELECT
10730: ppt.cc_prvdr_flag
10731: FROM
10732: pa_projects pp,
10733: pa_project_types ppt
10734: WHERE
10735: pp.project_type = ppt.project_type AND
10736: pp.org_id = ppt.org_id AND --this condition is not mandatory
10748: IS
10749: select 'x'
10750: from pa_project_role_types_v roles,
10751: pa_role_controls controls,
10752: pa_projects_all proj
10753: where proj.project_id=p_project_id
10754: and proj.ROLE_LIST_ID is null
10755: and roles.project_role_id=controls.project_role_id
10756: and roles.PROJECT_ROLE_TYPE=p_role_type
10759: union all
10760: select 'x'
10761: from pa_project_role_types_v roles,
10762: pa_role_controls controls,
10763: pa_projects_all proj,
10764: pa_role_list_members prlm
10765: where proj.project_id=p_project_id
10766: and proj.ROLE_LIST_ID =prlm.role_list_id
10767: and prlm.project_role_id = roles.project_role_id
10780: CURSOR cur_enable_funds_flag(p_project_id IN VARCHAR2)
10781: IS
10782: SELECT 'Y'
10783: FROM
10784: pa_projects_all p,
10785: pa_project_types_all pt
10786: WHERE
10787: p.project_id = p_project_id AND
10788: pt.project_type_class_code = 'CONTRACT' AND
11528:
11529:
11530:
11531: l_update_yes_flag := 'N';
11532: l_statement := 'UPDATE PA_PROJECTS SET ';
11533:
11534: --PROJECT NUMBER
11535: -- Check for implementation defined Project number generation mode
11536: -- If project number generation is automatic then ignore the input
15461: --End PA L changes
15462:
15463: ------------------- FP_M changes begin (venkat) 2 -----------------------------------------------
15464:
15465: /* ------ Begin Commented for now as the column sys_program_flag not exist in the pa_projects -----
15466:
15467: IF (p_project_in.sys_program_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
15468: (p_project_in.sys_program_flag IS NOT NULL)
15469: THEN
16329:
16330: -- FP_M changes begin (venkat) 3
16331:
16332: -- COMMENT BEGIN --
16333: -------------- Begin Comment : Diable for now as the col sys_program_flag not in pa_projects -------
16334: -- IF l_sys_program_flag = 'Y' THEN
16335: -- DBMS_SQL.BIND_VARIABLE(l_cursor, ':sys_program_flg', RTRIM(p_project_in.sys_program_flag));
16336: -- END IF;
16337: -------------- end Comment : Diable for now as the col sys_program_flag not in pa_projects ---------
16333: -------------- Begin Comment : Diable for now as the col sys_program_flag not in pa_projects -------
16334: -- IF l_sys_program_flag = 'Y' THEN
16335: -- DBMS_SQL.BIND_VARIABLE(l_cursor, ':sys_program_flg', RTRIM(p_project_in.sys_program_flag));
16336: -- END IF;
16337: -------------- end Comment : Diable for now as the col sys_program_flag not in pa_projects ---------
16338: -- COMMENT END --
16339:
16340: -- Bug # 5072032.
16341:
16552: --Update the distribution rule with the correct invoice method, if no distribution rule has been passed
16553: --Call the api to default the invoice method to the tasks
16554: IF 'Y' = l_enable_top_task_inv_mth_flag AND 'N' = p_project_in.enable_top_task_inv_mth_flag THEN
16555: IF nvl(p_project_in.distribution_rule,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
16556: UPDATE pa_projects_all
16557: SET distribution_rule =
16558: substr(distribution_rule,1,instr(distribution_rule,'/')-1)||'/'||l_proj_invoice_method
16559: WHERE project_id = l_project_id;
16560: END IF;
16589:
16590: --IF the invoice method at top task flag is checked,
16591: --invoice method of the distribution rule must internally be populated as WORK
16592: IF 'Y' = l_proj_top_task_inv_mth_flag THEN
16593: UPDATE pa_projects_all
16594: SET distribution_rule =
16595: substr(distribution_rule,1,instr(distribution_rule,'/')-1)||'/'||'WORK'
16596: WHERE project_id = l_project_id;
16597: END IF;
18983: -- updating START_DATE_ACTIVE and END_DATE_ACTIVE of
18984: -- PA_PROJECT_PLAYERS for the rowid in the cursor
18985: SELECT COMPLETION_DATE
18986: INTO v_completion_date
18987: FROM pa_projects p
18988: WHERE p.project_id = l_key_members_rec.project_id;
18989: /*Added the OR condition in the below statement for the bug 2846478*/
18990: if l_key_members_rec.scheduled_flag = 'N' OR l_key_members_rec.scheduled_flag IS NULL
18991: then
18996: , p_validate_only => FND_API.G_FALSE -- p_validate_only
18997: , p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
18998: , p_debug_mode => 'N' -- p_debug_mode
18999: , p_object_id => l_key_members_rec.project_id -- p_object_id
19000: , p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
19001: , p_project_role_id => NULL -------Bug 2100142l_key_members_rec.project_id -- p_project_role_id
19002: , p_project_role_type => l_key_members_rec.project_role_type -- p_project_role_type
19003: , p_resource_type_id => l_key_members_rec.resource_type_id -- p_resource_type_id
19004: , p_resource_source_id => l_key_members_rec.person_id -- p_resource_source_id
19199:
19200:
19201: SELECT COMPLETION_DATE
19202: INTO v_completion_date
19203: FROM pa_projects p
19204: WHERE p.project_id = l_org_roles_rec.project_id;
19205: if l_org_roles_rec.scheduled_flag = 'N'
19206: then
19207: PA_PROJECT_PARTIES_PUB.UPDATE_PROJECT_PARTY(
19211: , p_validate_only => FND_API.G_FALSE -- p_validate_only
19212: , p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
19213: , p_debug_mode => 'N' -- p_debug_mode
19214: , p_object_id => l_org_roles_rec.project_id -- p_object_id
19215: , p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
19216: , p_project_role_id => NULL -------Bug 2100142l_key_members_rec.project_id -- p_project_role_id
19217: , p_project_role_type => l_org_roles_rec.project_role_type -- p_project_role_type
19218: , p_resource_type_id => l_org_roles_rec.resource_type_id -- p_resource_type_id
19219: , p_resource_source_id => l_org_roles_rec.resource_source_id -- p_resource_source_id
20449: end if;
20450:
20451: Select pt.CC_PRVDR_FLAG,pa.multi_currency_billing_flag
20452: into l_cc_prvdr_flag,l_mcb_flag
20453: From pa_project_types pt, pa_projects pa
20454: where pa.project_type=pt.project_type AND
20455: pt.org_id = pa.org_id and
20456: pa.project_id=l_project_id;
20457:
21043: END if;
21044:
21045: Select pt.CC_PRVDR_FLAG,pa.multi_currency_billing_flag
21046: into l_cc_prvdr_flag,l_mcb_flag
21047: From pa_project_types pt, pa_projects pa
21048: where pa.project_type=pt.project_type
21049: AND pa.org_id = pt.org_id
21050: and pa.project_id=l_project_id;
21051:
21658: --For inserting status change comment into the status history table
21659: PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW
21660: ( X_ROWID => l_rowid,
21661: X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
21662: X_OBJECT_TYPE => 'PA_PROJECTS',
21663: X_OBJECT_ID => l_project_rec.project_id,
21664: X_STATUS_TYPE => 'PROJECT',
21665: X_NEW_PROJECT_STATUS_CODE => p_project_in.project_status_code,
21666: X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sys_status,
21673: X_LAST_UPDATE_DATE => sysdate,
21674: X_LAST_UPDATE_LOGIN => fnd_global.user_id );
21675:
21676: IF (l_debug_mode = 'Y') THEN
21677: pa_debug.debug(' Updated PA_PROJECTS_ALL for status history');
21678: END IF;
21679:
21680: --Fix for bug#9110781 Ends
21681:
21679:
21680: --Fix for bug#9110781 Ends
21681:
21682: END IF;
21683: l_statement := 'UPDATE PA_PROJECTS SET ';
21684: -- special case for update to and from 'CLOSED'
21685: IF l_closing_project = 'Y' THEN
21686: l_statement := l_statement ||
21687: ' PROJECT_STATUS_CODE = ' ||
22802: --Bug 3279981. Fetch project information
22803: CURSOR cur_get_proj_billing_info(c_project_id IN NUMBER) IS
22804: SELECT enable_top_task_customer_flag, enable_top_task_inv_mth_flag,
22805: revenue_accrual_method, invoice_method, project_type
22806: FROM pa_projects_all
22807: WHERE project_id = c_project_id;
22808:
22809: CURSOR cur_check_proj_cust_exist (c_project_id IN NUMBER , c_customer_id IN NUMBER ) IS
22810: SELECT 'Y'
22849: l_customer_name VARCHAR2(360); -- Added for bug 4689888 -- Modified to VARCHAR2(360) for bug 5856469
22850: l_top_task_customer_id NUMBER; -- Added for Bug 5242015
22851: /* Added for bug 3705333 */
22852: CURSOR l_amg_project_csr
22853: (p_pa_project_id pa_projects.project_id%type)
22854: IS
22855: SELECT segment1
22856: FROM pa_projects p
22857: WHERE p.project_id = l_project_id ; --Bug#3747312 p_pa_project_id;
22852: CURSOR l_amg_project_csr
22853: (p_pa_project_id pa_projects.project_id%type)
22854: IS
22855: SELECT segment1
22856: FROM pa_projects p
22857: WHERE p.project_id = l_project_id ; --Bug#3747312 p_pa_project_id;
22858: /* changes end for bug 3705333 */
22859: --Bug 3279981
22860: --rtarway, BUG 3908013
30087: IS
30088:
30089: l_project_id NUMBER;
30090: l_task_id NUMBER;
30091: l_project_number Pa_Projects_All.Segment1%TYPE;
30092: l_task_number Pa_Proj_Elements.Name%TYPE;
30093: l_task_source_reference VARCHAR2(240);
30094: l_deliverable_element_id NUMBER;
30095: l_deliverable_version_id NUMBER;
30312: l_task_number := null;
30313: END IF;
30314:
30315: SELECT segment1 INTO l_project_number
30316: FROM Pa_Projects_All
30317: WHERE project_id = l_project_id;
30318:
30319: IF p_debug_mode = 'Y' THEN
30320: pa_debug.g_err_stage := 'Creating deliverable ['|| p_deliverable_short_name||']['||p_pm_deliverable_reference||']['
30764: l_action_out_tbl action_out_tbl_type ;
30765:
30766: l_project_id NUMBER;
30767: l_task_id NUMBER;
30768: l_project_number Pa_Projects_All.Segment1%TYPE;
30769: l_task_number Pa_Proj_Elements.Name%TYPE;
30770: l_dlvr_type_id Pa_Proj_Elements.type_id%TYPE;
30771:
30772: l_dlvr_prg_enabled VARCHAR2(1) := NULL;
33995: p_debug_mode => l_debug_mode );
33996: END IF;
33997:
33998: IF l_debug_mode = 'Y' THEN
33999: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : DELETE_MAPPING : Printing Input parameters';
34000: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34001: l_debug_level3);
34002: Pa_Debug.WRITE(g_pkg_name,'p_wp_task_version_id'||':'||p_wp_task_version_id,
34003: l_debug_level3);
34018:
34019: END IF;
34020: --Validate for input parameters
34021: IF l_debug_mode = 'Y' THEN
34022: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : DELETE_MAPPING : Validating Input parameters';
34023: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34024: l_debug_level3);
34025: END IF;
34026:
34057: l_project_id := p_project_id;
34058: END IF;
34059:
34060: IF p_debug_mode = 'Y' THEN
34061: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'DELETE_MAPPING'||'Project ID:'||l_project_id;
34062: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34063: END IF;
34064: --1.1 Get the task ids if these are null
34065: --WP TASK ID
34166: x_msg_count := l_msg_count;
34167: x_msg_data := l_msg_data;
34168:
34169: IF p_debug_mode = 'Y' THEN
34170: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'DELETE_MAPPING'||'After Calling PA_PROJ_STRUC_MAPPING_PUB.DELETE_MAPPING:'||x_return_status;
34171: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34172: END IF;
34173:
34174: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
34280: p_debug_mode => l_debug_mode );
34281: END IF;
34282:
34283: IF l_debug_mode = 'Y' THEN
34284: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : CREATE_MAPPING : Printing Input parameters';
34285: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34286: l_debug_level3);
34287: Pa_Debug.WRITE(g_pkg_name,'px_mapped_task_id'||':'||px_mapped_task_id,
34288: l_debug_level3);
34300: l_debug_level3);
34301: END IF;
34302: --Validate for input parameters
34303: IF l_debug_mode = 'Y' THEN
34304: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : CREATE_MAPPING : Validating Input parameters';
34305: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34306: l_debug_level3);
34307: END IF;
34308:
34311: (p_proj_source_reference IS NULL OR p_proj_source_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
34312: )
34313: THEN
34314: IF l_debug_mode = 'Y' THEN
34315: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : CREATE_MAPPING : At least one of the parameters for deriving Project id should be passed';
34316: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34317: l_debug_level3);
34318: END IF;
34319: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
34325: ( p_mapped_task_name IS NULL OR p_mapped_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
34326: )
34327: THEN
34328: IF l_debug_mode = 'Y' THEN
34329: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : CREATE_MAPPING : At least one of the parameters for deriving financial task version id should be passed';
34330: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34331: l_debug_level3);
34332: END IF;
34333: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
34339: (p_wkp_task_name IS NULL OR p_wkp_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR )
34340: )
34341: THEN
34342: IF l_debug_mode = 'Y' THEN
34343: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : CREATE_MAPPING : At least one of the parameters for deriving workplan task version id should be passed';
34344: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34345: l_debug_level3);
34346: END IF;
34347: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
34407: );
34408: END IF;
34409:
34410: IF p_debug_mode = 'Y' THEN
34411: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'CREATE_MAPPING'||'Project ID:'||l_project_id;
34412: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34413: END IF;
34414:
34415: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
34432: , p_out_task_id => l_wkp_task_id
34433: , p_return_status => x_return_status );
34434:
34435: IF p_debug_mode = 'Y' THEN
34436: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'CREATE_MAPPING'||'wkp_task_id:'||l_wkp_task_id;
34437: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34438: END IF;
34439:
34440: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
34458: , p_return_status => x_return_status );
34459:
34460: --Assign mapped task_id to px_mapped_task_id as it is in out parameter
34461: IF p_debug_mode = 'Y' THEN
34462: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'CREATE_MAPPING'||'mapped_task_id:'||l_mapped_task_id;
34463: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34464: END IF;
34465: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
34466: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
34493: fetch cur_get_mapped_task_version_id into l_mapped_task_version_id;
34494: close cur_get_mapped_task_version_id ;
34495: END IF;
34496: IF p_debug_mode = 'Y' THEN
34497: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'CREATE_MAPPING'||'Before Calling PA_PROJ_STRUC_MAPPING_PUB.CREATE_MAPPING:';
34498: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34499: END IF;
34500:
34501:
34522: x_msg_count := l_msg_count;
34523: x_msg_data := l_msg_data;
34524:
34525: IF p_debug_mode = 'Y' THEN
34526: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'CREATE_MAPPING'||'After Calling PA_PROJ_STRUC_MAPPING_PUB.CREATE_MAPPING:'||x_return_status;
34527: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34528: END IF;
34529:
34530: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
34642: p_debug_mode => l_debug_mode );
34643: END IF;
34644:
34645: IF l_debug_mode = 'Y' THEN
34646: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : UPDATE_MAPPING : Printing Input parameters';
34647: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34648: l_debug_level3);
34649: Pa_Debug.WRITE(g_pkg_name,'px_mapped_task_id'||':'||px_mapped_task_id,
34650: l_debug_level3);
34662: l_debug_level3);
34663: END IF;
34664: --Validate for input parameters
34665: IF l_debug_mode = 'Y' THEN
34666: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : UPDATE_MAPPING : Validating Input parameters';
34667: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34668: l_debug_level3);
34669: END IF;
34670:
34673: (p_proj_source_reference IS NULL OR p_proj_source_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
34674: )
34675: THEN
34676: IF l_debug_mode = 'Y' THEN
34677: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : UPDATE_MAPPING : At least one of the parameters for deriving Project id should be passed';
34678: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34679: l_debug_level3);
34680: END IF;
34681: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
34690: ( p_wkp_task_name IS NULL OR p_wkp_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR )
34691: )
34692: THEN
34693: IF l_debug_mode = 'Y' THEN
34694: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : UPDATE_MAPPING : At least one of the parameters for deriving workplan task version id should be passed';
34695: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34696: l_debug_level3);
34697: END IF;
34698: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
34758: );
34759: END IF;
34760:
34761: IF p_debug_mode = 'Y' THEN
34762: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'UPDATE_MAPPING'||'Project ID:'||l_project_id;
34763: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34764: END IF;
34765:
34766: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
34781: , p_out_task_id => l_wkp_task_id
34782: , p_return_status => x_return_status );
34783:
34784: IF p_debug_mode = 'Y' THEN
34785: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'UPDATE_MAPPING'||'wkp_task_id:'||l_wkp_task_id;
34786: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34787: END IF;
34788:
34789: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
34806: , p_out_task_id => l_mapped_task_id
34807: , p_return_status => x_return_status );
34808:
34809: IF p_debug_mode = 'Y' THEN
34810: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'UPDATE_MAPPING'||'mapped_task_id:'||l_mapped_task_id;
34811: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34812: END IF;
34813: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
34814: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
34840:
34841: END IF;
34842:
34843: IF p_debug_mode = 'Y' THEN
34844: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'UPDATE_MAPPING'||'Before Calling PA_PROJ_STRUC_MAPPING_PUB.UPDATE_MAPPING:';
34845: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34846: END IF;
34847:
34848:
34870: x_msg_count := l_msg_count;
34871: x_msg_data := l_msg_data;
34872:
34873: IF p_debug_mode = 'Y' THEN
34874: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'UPDATE_MAPPING'||'After Calling PA_PROJ_STRUC_MAPPING_PUB.UPDATE_MAPPING:'||x_return_status;
34875: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
34876: END IF;
34877:
34878: -- 10053423
34979: p_debug_mode => l_debug_mode );
34980: END IF;
34981:
34982: IF l_debug_mode = 'Y' THEN
34983: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : PROCESS_TASK_TABLE : Printing Input parameters';
34984: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34985: l_debug_level3);
34986: Pa_Debug.WRITE(g_pkg_name,'p_project_id'||':'||p_project_id,
34987: l_debug_level3);
34989: l_debug_level3);
34990: END IF;
34991: --Validate for input parameters
34992: IF l_debug_mode = 'Y' THEN
34993: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : PROCESS_TASK_TABLE : Validating Input parameters';
34994: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
34995: l_debug_level3);
34996: END IF;
34997:
35000: (p_proj_source_reference IS NULL OR p_proj_source_reference = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
35001: )
35002: THEN
35003: IF l_debug_mode = 'Y' THEN
35004: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : PROCESS_TASK_TABLE : At least one of the parameters for deriving Project id should be passed';
35005: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
35006: l_debug_level3);
35007: END IF;
35008: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
35010:
35011: IF ( p_tasks_in_tbl IS NULL OR p_tasks_in_tbl.count <= 0 )
35012: THEN
35013: IF l_debug_mode = 'Y' THEN
35014: Pa_Debug.g_err_stage:= 'PA_PROJECTS_PUB : PROCESS_TASK_TABLE : Empty or NULL Task Table';
35015: Pa_Debug.WRITE(g_pkg_name,Pa_Debug.g_err_stage,
35016: l_debug_level3);
35017: END IF;
35018: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
35034: );
35035: END IF;
35036:
35037: IF p_debug_mode = 'Y' THEN
35038: pa_debug.g_err_stage := 'PA_PROJECTS_PUB:'||'PROCESS_TASK_TABLE'||'Project ID:'||l_project_id;
35039: pa_debug.write(g_pkg_name,pa_debug.g_err_stage,3);
35040: END IF;
35041:
35042: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
35161:
35162: CURSOR get_template_flag(c_project_id NUMBER)
35163: IS
35164: select template_flag
35165: from pa_projects_all
35166: where project_id = c_project_id;
35167:
35168: CURSOR get_workplan_status(c_project_id NUMBER, c_structure_version_id NUMBER)
35169: IS
35413:
35414: CURSOR get_template_flag(c_project_id NUMBER)
35415: IS
35416: select template_flag
35417: from pa_projects_all
35418: where project_id = c_project_id;
35419:
35420: CURSOR get_workplan_status(c_project_id NUMBER, c_structure_version_id NUMBER)
35421: IS
35674:
35675: CURSOR get_template_flag(c_project_id NUMBER)
35676: IS
35677: select template_flag
35678: from pa_projects_all
35679: where project_id = c_project_id;
35680:
35681: CURSOR get_workplan_status(c_project_id NUMBER, c_structure_version_id NUMBER)
35682: IS
36383: l_user_id NUMBER := 0;
36384: l_module_name VARCHAR2(80);
36385: l_function_allowed VARCHAR2(1);
36386:
36387: l_project_id pa_projects.project_id%type;
36388:
36389: l_update_wbs_flag VARCHAR2(1);
36390:
36391: l_str_ver_id NUMBER:= null;
36654: ,ppes.element_version_id structure_version_id
36655: ,ppa.segment1 project_number
36656: ,ppes.name structure_name
36657: from pa_proj_elem_ver_structure ppes,
36658: pa_projects_all ppa
36659: where ppa.project_id = ppes.project_id
36660: and ppes.process_update_wbs_flag = 'Y'
36661: and NVL(ppes.process_code,'WUE') = 'WUE'
36662: and ppa.segment1 between nvl(p_project_num_from,ppa.segment1)
36831: l_allow_multi_program_rollup VARCHAR2(1) := NULL;
36832:
36833: cursor l_cur_sys_program_flag(c_project_id NUMBER) is
36834: select ppa.sys_program_flag
36835: from pa_projects_all ppa
36836: where ppa.project_id = c_project_id;
36837:
36838: cursor l_cur_allow_multi_prog_rollup(c_project_id NUMBER) is
36839: select ppa.allow_multi_program_rollup
36836: where ppa.project_id = c_project_id;
36837:
36838: cursor l_cur_allow_multi_prog_rollup(c_project_id NUMBER) is
36839: select ppa.allow_multi_program_rollup
36840: from pa_projects_all ppa
36841: where ppa.project_id = c_project_id;
36842:
36843: l_msg_index_out NUMBER := NULL;
36844: l_return_status VARCHAR2(1) := NULL;
37062: l_msg_data VARCHAR2(2000) := NULL;
37063:
37064: cursor l_cur_prog_name (c_task_version_id NUMBER) is
37065: select ppa.name
37066: from pa_projects_all ppa, pa_proj_element_versions ppev
37067: where ppa.project_id = ppev.project_id
37068: and ppev.element_version_id = c_task_version_id;
37069:
37070: cursor l_cur_prog_name2 (c_project_id NUMBER) is
37068: and ppev.element_version_id = c_task_version_id;
37069:
37070: cursor l_cur_prog_name2 (c_project_id NUMBER) is
37071: select ppa.name
37072: from pa_projects_all ppa
37073: where ppa.project_id = c_project_id;
37074:
37075: cursor l_cur_proj_name (c_project_id NUMBER) is
37076: select ppa.name
37073: where ppa.project_id = c_project_id;
37074:
37075: cursor l_cur_proj_name (c_project_id NUMBER) is
37076: select ppa.name
37077: from pa_projects_all ppa
37078: where ppa.project_id = c_project_id;
37079:
37080: cursor l_cur_task_name (c_task_ver_id NUMBER) is
37081: select ppe.name
37085: and ppev.element_version_id = c_task_ver_id;
37086:
37087: cursor l_cur_sub_proj1(c_sub_project_reference VARCHAR2, c_sub_project_id NUMBER) is
37088: select ppa.project_id
37089: from pa_projects_all ppa
37090: where pm_project_reference = c_sub_project_reference
37091: and ppa.project_id = c_sub_project_id;
37092:
37093: cursor l_cur_sub_proj2(c_sub_project_reference VARCHAR2) is
37091: and ppa.project_id = c_sub_project_id;
37092:
37093: cursor l_cur_sub_proj2(c_sub_project_reference VARCHAR2) is
37094: select ppa.project_id
37095: from pa_projects_all ppa
37096: where pm_project_reference = c_sub_project_reference;
37097:
37098: cursor l_cur_sub_proj3(c_sub_project_id NUMBER) is
37099: select ppa.project_id
37096: where pm_project_reference = c_sub_project_reference;
37097:
37098: cursor l_cur_sub_proj3(c_sub_project_id NUMBER) is
37099: select ppa.project_id
37100: from pa_projects_all ppa
37101: where ppa.project_id = c_sub_project_id;
37102:
37103: l_parent_sub_flag VARCHAR2(1) := NULL;
37104:
37642: and por.object_relationship_id = c_object_relationship_id;
37643:
37644: cursor l_cur_prog_name (c_task_version_id NUMBER) is
37645: select ppa.name
37646: from pa_projects_all ppa, pa_proj_element_versions ppev
37647: where ppa.project_id = ppev.project_id
37648: and ppev.element_version_id = c_task_version_id;
37649:
37650: cursor l_cur_prog_name2 (c_project_id NUMBER) is
37648: and ppev.element_version_id = c_task_version_id;
37649:
37650: cursor l_cur_prog_name2 (c_project_id NUMBER) is
37651: select ppa.name
37652: from pa_projects_all ppa
37653: where ppa.project_id = c_project_id;
37654:
37655: cursor l_cur_proj_name (c_project_id NUMBER) is
37656: select ppa.name
37653: where ppa.project_id = c_project_id;
37654:
37655: cursor l_cur_proj_name (c_project_id NUMBER) is
37656: select ppa.name
37657: from pa_projects_all ppa
37658: where ppa.project_id = c_project_id;
37659:
37660: cursor l_cur_task_name (c_task_ver_id NUMBER) is
37661: select ppe.name
37670: where ppev.element_version_id = c_task_version_id;
37671:
37672: cursor l_cur_sub_proj1(c_sub_project_reference VARCHAR2, c_sub_project_id NUMBER) is
37673: select ppa.project_id
37674: from pa_projects_all ppa
37675: where pm_project_reference = c_sub_project_reference
37676: and ppa.project_id = c_sub_project_id;
37677:
37678: cursor l_cur_sub_proj2(c_sub_project_reference VARCHAR2) is
37676: and ppa.project_id = c_sub_project_id;
37677:
37678: cursor l_cur_sub_proj2(c_sub_project_reference VARCHAR2) is
37679: select ppa.project_id
37680: from pa_projects_all ppa
37681: where pm_project_reference = c_sub_project_reference;
37682:
37683: cursor l_cur_sub_proj3(c_sub_project_id NUMBER) is
37684: select ppa.project_id
37681: where pm_project_reference = c_sub_project_reference;
37682:
37683: cursor l_cur_sub_proj3(c_sub_project_id NUMBER) is
37684: select ppa.project_id
37685: from pa_projects_all ppa
37686: where ppa.project_id = c_sub_project_id;
37687:
37688: l_parent_sub_flag VARCHAR2(1) := NULL;
37689:
38232: and por.object_relationship_id = c_object_relationship_id;
38233:
38234: cursor l_cur_prog_name (c_task_version_id NUMBER) is
38235: select ppa.name
38236: from pa_projects_all ppa, pa_proj_element_versions ppev
38237: where ppa.project_id = ppev.project_id
38238: and ppev.element_version_id = c_task_version_id;
38239:
38240: cursor l_cur_proj_name (c_project_id NUMBER) is
38238: and ppev.element_version_id = c_task_version_id;
38239:
38240: cursor l_cur_proj_name (c_project_id NUMBER) is
38241: select ppa.name
38242: from pa_projects_all ppa
38243: where ppa.project_id = c_project_id;
38244:
38245: cursor l_cur_task_name (c_task_ver_id NUMBER) is
38246: select ppe.name
38255: where ppev.element_version_id = c_task_version_id;
38256:
38257: cursor l_cur_sub_proj1(c_sub_project_reference VARCHAR2, c_sub_project_id NUMBER) is
38258: select ppa.project_id
38259: from pa_projects_all ppa
38260: where pm_project_reference = c_sub_project_reference
38261: and ppa.project_id = c_sub_project_id;
38262:
38263: cursor l_cur_sub_proj2(c_sub_project_reference VARCHAR2) is
38261: and ppa.project_id = c_sub_project_id;
38262:
38263: cursor l_cur_sub_proj2(c_sub_project_reference VARCHAR2) is
38264: select ppa.project_id
38265: from pa_projects_all ppa
38266: where pm_project_reference = c_sub_project_reference;
38267:
38268: cursor l_cur_sub_proj3(c_sub_project_id NUMBER) is
38269: select ppa.project_id
38266: where pm_project_reference = c_sub_project_reference;
38267:
38268: cursor l_cur_sub_proj3(c_sub_project_id NUMBER) is
38269: select ppa.project_id
38270: from pa_projects_all ppa
38271: where ppa.project_id = c_sub_project_id;
38272:
38273: l_program_name VARCHAR2(30) := NULL;
38274: l_project_name VARCHAR2(30) := NULL;
38724:
38725: CURSOR get_project_details_csr (c_project_id NUMBER) IS
38726: SELECT project_id, name, segment1, pm_project_reference,
38727: start_date, completion_date, project_type, structure_sharing_code
38728: FROM PA_PROJECTS_ALL
38729: WHERE project_id = c_project_id;
38730:
38731: CURSOR get_task_details_csr (c_project_id NUMBER) IS
38732: SELECT element_version_id,proj_element_id,display_sequence,wbs_level,wbs_number
39037:
39038: CURSOR get_project_details_csr (c_project_id NUMBER) IS
39039: SELECT project_id, name, segment1, pm_project_reference,
39040: start_date, completion_date, project_type, structure_sharing_code
39041: FROM PA_PROJECTS_ALL
39042: WHERE project_id = c_project_id;
39043:
39044: CURSOR check_tasks_exists_csr(c_project_id NUMBER) IS
39045: SELECT 'Y'
39631: l_err_code NUMBER := 0;
39632: l_err_stack VARCHAR2(200) := NULL;
39633: l_err_stage VARCHAR2(200) := NULL;
39634:
39635: l_pm_product_code pa_projects_all.pm_product_code%TYPE;
39636:
39637: l_cur_project_id NUMBER;
39638: CURSOR cur_proj_id
39639: IS
39642: WHERE element_version_id = p_structure_version_id;
39643:
39644: CURSOR get_product_code ( c_project_id NUMBER ) IS
39645: SELECT pm_product_code
39646: FROM PA_PROJECTS_ALL
39647: WHERE project_id = c_project_id;
39648:
39649: CURSOR cur_struc_type( c_structure_id NUMBER )
39650: IS
40119: l_err_code NUMBER := 0;
40120: l_err_stack VARCHAR2(200) := NULL;
40121: l_err_stage VARCHAR2(200) := NULL;
40122:
40123: l_pm_product_code pa_projects_all.pm_product_code%TYPE;
40124:
40125: CURSOR get_product_code ( c_project_id NUMBER ) IS
40126: SELECT pm_product_code
40127: FROM PA_PROJECTS_ALL
40123: l_pm_product_code pa_projects_all.pm_product_code%TYPE;
40124:
40125: CURSOR get_product_code ( c_project_id NUMBER ) IS
40126: SELECT pm_product_code
40127: FROM PA_PROJECTS_ALL
40128: WHERE project_id = c_project_id;
40129:
40130: l_cur_project_id NUMBER;
40131: CURSOR cur_proj_id
41569: l_err_code NUMBER := 0;
41570: l_err_stack VARCHAR2(200) := NULL;
41571: l_err_stage VARCHAR2(200) := NULL;
41572:
41573: l_pm_product_code pa_projects_all.pm_product_code%TYPE;
41574:
41575: CURSOR get_product_code ( c_project_id NUMBER ) IS
41576: SELECT pm_product_code
41577: FROM PA_PROJECTS_ALL
41573: l_pm_product_code pa_projects_all.pm_product_code%TYPE;
41574:
41575: CURSOR get_product_code ( c_project_id NUMBER ) IS
41576: SELECT pm_product_code
41577: FROM PA_PROJECTS_ALL
41578: WHERE project_id = c_project_id;
41579:
41580: l_cur_project_id NUMBER;
41581: CURSOR cur_proj_id
43301: l_count Number :=0;
43302:
43303: Cursor C1(c_proj_id Number) IS
43304: Select NVL(CBS_ENABLE_FLAG,'N')
43305: FROM PA_PROJECTS_ALL
43306: WHERE PROJECT_ID=c_proj_id;
43307:
43308: Cursor C2(c_proj_id Number , c_number varchar2) IS
43309: Select proj_element_id
43330: and task_id=c_task_id;
43331:
43332: Cursor Check_cost_code_valid(c_proj_id Number ,c_cbs_lem_id Number) IS
43333: SELECT COUNT(1)
43334: FROM PA_PROJECTS_ALL PA,PA_RBS_ELEMENTS PRE
43335: WHERE PA.CBS_VERSION_ID=PRE.RBS_VERSION_ID
43336: AND PA.PROJECT_ID=c_proj_id
43337: AND PRE.RBS_ELEMENT_ID=c_cbs_lem_id;
43338:
43599: l_count Number :=0;
43600:
43601: Cursor C1(c_proj_id Number) IS
43602: Select NVL(CBS_ENABLE_FLAG,'N')
43603: FROM PA_PROJECTS_ALL
43604: WHERE PROJECT_ID=c_proj_id;
43605:
43606: Cursor C2(c_proj_id Number , c_number varchar2) IS
43607: Select proj_element_id
43628: and task_id=c_task_id;
43629:
43630: Cursor Check_cost_code_valid(c_proj_id Number ,c_cbs_lem_id Number) IS
43631: SELECT COUNT(1)
43632: FROM PA_PROJECTS_ALL PA,PA_RBS_ELEMENTS PRE
43633: WHERE PA.CBS_VERSION_ID=PRE.RBS_VERSION_ID
43634: AND PA.PROJECT_ID=c_proj_id
43635: AND PRE.RBS_ELEMENT_ID=c_cbs_lem_id;
43636: