DBA Data[Home] [Help]

APPS.ENG_ECO_PVT dependencies on ENG_CHANGE_ROUTE_PEOPLE

Line 17188: FROM ENG_CHANGE_ROUTE_PEOPLE

17184: -- When Eng_Change_Route_Util.COPY_ROUTE, it only copied role_id or group_id, but not doing the people assginment.
17185: -- Hence, we need to find out these id to do people assignment
17186: CURSOR C_GET_ROUTE_STEP_ASSIGNEE(cp_route_id IN NUMBER) IS
17187: SELECT *
17188: FROM ENG_CHANGE_ROUTE_PEOPLE
17189: WHERE STEP_ID IN (SELECT STEP_ID
17190: FROM ENG_CHANGE_ROUTE_STEPS
17191: WHERE ROUTE_ID IN (cp_route_id))
17192: AND ORIGINAL_ASSIGNEE_TYPE_CODE IS NULL

Line 17262: UPDATE ENG_CHANGE_ROUTE_PEOPLE -- bug 13860012

17258: OWNER_ID = FND_GLOBAL.USER_ID,
17259: APPLIED_TEMPLATE_ID = C_CHANGES_REC.CHANGE_WF_ROUTE_TEMPLATE_ID
17260: WHERE ROUTE_ID = l_to_route_id;
17261:
17262: UPDATE ENG_CHANGE_ROUTE_PEOPLE -- bug 13860012
17263: SET ORIGINAL_ASSIGNEE_TYPE_CODE = 'PERSON'
17264: WHERE ASSIGNEE_TYPE_CODE = 'PERSON'
17265: AND STEP_ID IN ( SELECT STEP_ID FROM ENG_CHANGE_ROUTE_STEPS_VL WHERE ROUTE_ID = l_to_route_id );
17266: /** === Part: Populate Route People === **/

Line 17317: FROM ENG_CHANGE_ROUTE_PEOPLE

17313: IF (l_assignee_id is not null) THEN
17314: -- check if the person is created in current step, if l_people_existed_flag = 0, then create
17315: SELECT COUNT(1)
17316: INTO l_people_existed_flag
17317: FROM ENG_CHANGE_ROUTE_PEOPLE
17318: WHERE step_id = cr.step_id
17319: AND assignee_id = l_assignee_id;
17320:
17321: IF (l_people_existed_flag = 0) THEN

Line 17323: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17319: AND assignee_id = l_assignee_id;
17320:
17321: IF (l_people_existed_flag = 0) THEN
17322: -- generate new people id
17323: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17324: into l_people_id
17325: FROM DUAL;
17326:
17327: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

Line 17327: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17323: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17324: into l_people_id
17325: FROM DUAL;
17326:
17327: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17328: (route_people_id,
17329: step_id,
17330: assignee_id,
17331: assignee_type_code,

Line 17374: insert into ENG_CHANGE_ROUTE_PEOPLE_TL

17370: cr.assignee_type_code,
17371: cr.response_condition_code,
17372: cr.route_people_id);
17373:
17374: insert into ENG_CHANGE_ROUTE_PEOPLE_TL
17375: (ROUTE_PEOPLE_ID,
17376: CREATION_DATE,
17377: CREATED_BY,
17378: LAST_UPDATE_DATE,

Line 17397: from ENG_CHANGE_ROUTE_PEOPLE_TL T

17393: from FND_LANGUAGES L
17394: where L.INSTALLED_FLAG in ('I', 'B')
17395: and not exists
17396: (select NULL
17397: from ENG_CHANGE_ROUTE_PEOPLE_TL T
17398: where T.ROUTE_PEOPLE_ID = l_people_id
17399: and T.LANGUAGE = L.LANGUAGE_CODE);
17400:
17401: END IF;

Line 17405: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17401: END IF;
17402:
17403: ELSE
17404: -- generate new people id
17405: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17406: into l_people_id
17407: FROM DUAL;
17408:
17409: --insert an 'Unassigned' assignee record

Line 17410: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17406: into l_people_id
17407: FROM DUAL;
17408:
17409: --insert an 'Unassigned' assignee record
17410: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17411: (route_people_id,
17412: step_id,
17413: assignee_id,
17414: assignee_type_code,

Line 17457: insert into ENG_CHANGE_ROUTE_PEOPLE_TL

17453: cr.assignee_type_code,
17454: cr.response_condition_code,
17455: cr.route_people_id);
17456:
17457: insert into ENG_CHANGE_ROUTE_PEOPLE_TL
17458: (ROUTE_PEOPLE_ID,
17459: CREATION_DATE,
17460: CREATED_BY,
17461: LAST_UPDATE_DATE,

Line 17480: from ENG_CHANGE_ROUTE_PEOPLE_TL T

17476: from FND_LANGUAGES L
17477: where L.INSTALLED_FLAG in ('I', 'B')
17478: and not exists
17479: (select NULL
17480: from ENG_CHANGE_ROUTE_PEOPLE_TL T
17481: where T.ROUTE_PEOPLE_ID = l_people_id
17482: and T.LANGUAGE = L.LANGUAGE_CODE);
17483:
17484: END IF;

Line 17499: FROM ENG_CHANGE_ROUTE_PEOPLE

17495: IF (l_requestor_id is not null) THEN
17496: -- check if the person is created in current step, if l_people_existed_flag = 0, then create
17497: SELECT COUNT(1)
17498: INTO l_people_existed_flag
17499: FROM ENG_CHANGE_ROUTE_PEOPLE
17500: WHERE step_id = cr.step_id
17501: AND assignee_id = l_requestor_id;
17502:
17503: IF (l_people_existed_flag = 0) THEN

Line 17505: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17501: AND assignee_id = l_requestor_id;
17502:
17503: IF (l_people_existed_flag = 0) THEN
17504: -- generate new people id
17505: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17506: into l_people_id
17507: FROM DUAL;
17508:
17509: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

Line 17509: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17505: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17506: into l_people_id
17507: FROM DUAL;
17508:
17509: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17510: (route_people_id,
17511: step_id,
17512: assignee_id,
17513: assignee_type_code,

Line 17556: insert into ENG_CHANGE_ROUTE_PEOPLE_TL

17552: cr.assignee_type_code,
17553: cr.response_condition_code,
17554: cr.route_people_id);
17555:
17556: insert into ENG_CHANGE_ROUTE_PEOPLE_TL
17557: (ROUTE_PEOPLE_ID,
17558: CREATION_DATE,
17559: CREATED_BY,
17560: LAST_UPDATE_DATE,

Line 17579: from ENG_CHANGE_ROUTE_PEOPLE_TL T

17575: from FND_LANGUAGES L
17576: where L.INSTALLED_FLAG in ('I', 'B')
17577: and not exists
17578: (select NULL
17579: from ENG_CHANGE_ROUTE_PEOPLE_TL T
17580: where T.ROUTE_PEOPLE_ID = l_people_id
17581: and T.LANGUAGE = L.LANGUAGE_CODE);
17582:
17583: END IF;

Line 17587: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17583: END IF;
17584:
17585: ELSE
17586: -- generate new people id
17587: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17588: into l_people_id
17589: FROM DUAL;
17590:
17591: --insert an 'Unassigned' assignee record

Line 17592: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17588: into l_people_id
17589: FROM DUAL;
17590:
17591: --insert an 'Unassigned' assignee record
17592: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17593: (route_people_id,
17594: step_id,
17595: assignee_id,
17596: assignee_type_code,

Line 17639: insert into ENG_CHANGE_ROUTE_PEOPLE_TL

17635: cr.assignee_type_code,
17636: cr.response_condition_code,
17637: cr.route_people_id);
17638:
17639: insert into ENG_CHANGE_ROUTE_PEOPLE_TL
17640: (ROUTE_PEOPLE_ID,
17641: CREATION_DATE,
17642: CREATED_BY,
17643: LAST_UPDATE_DATE,

Line 17662: from ENG_CHANGE_ROUTE_PEOPLE_TL T

17658: from FND_LANGUAGES L
17659: where L.INSTALLED_FLAG in ('I', 'B')
17660: and not exists
17661: (select NULL
17662: from ENG_CHANGE_ROUTE_PEOPLE_TL T
17663: where T.ROUTE_PEOPLE_ID = l_people_id
17664: and T.LANGUAGE = L.LANGUAGE_CODE);
17665:
17666: END IF;

Line 17680: FROM ENG_CHANGE_ROUTE_PEOPLE

17676:
17677: -- check if the person is created in current step, if l_people_existed_flag = 0, then create
17678: SELECT COUNT(1)
17679: INTO l_people_existed_flag
17680: FROM ENG_CHANGE_ROUTE_PEOPLE
17681: WHERE step_id = cr.step_id
17682: AND assignee_id = cr2.member_person_id;
17683:
17684: IF (l_people_existed_flag = 0) THEN

Line 17686: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17682: AND assignee_id = cr2.member_person_id;
17683:
17684: IF (l_people_existed_flag = 0) THEN
17685: -- generate new people id
17686: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17687: into l_people_id
17688: FROM DUAL;
17689:
17690: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

Line 17690: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17686: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17687: into l_people_id
17688: FROM DUAL;
17689:
17690: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17691: (route_people_id,
17692: step_id,
17693: assignee_id,
17694: assignee_type_code,

Line 17738: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (

17734: cr.response_condition_code,
17735: --cr.route_people_id
17736: null);
17737:
17738: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
17739: ROUTE_PEOPLE_ID,
17740: CREATION_DATE,
17741: CREATED_BY,
17742: LAST_UPDATE_DATE,

Line 17762: from ENG_CHANGE_ROUTE_PEOPLE_TL T

17758: from FND_LANGUAGES L
17759: where L.INSTALLED_FLAG in ('I', 'B')
17760: and not exists
17761: (select NULL
17762: from ENG_CHANGE_ROUTE_PEOPLE_TL T
17763: where T.ROUTE_PEOPLE_ID = l_people_id
17764: and T.LANGUAGE = L.LANGUAGE_CODE);
17765:
17766: END IF;

Line 17782: FROM ENG_CHANGE_ROUTE_PEOPLE

17778:
17779: -- check if the person is created in current step, if l_people_existed_flag = 0, then create
17780: SELECT COUNT(1)
17781: INTO l_people_existed_flag
17782: FROM ENG_CHANGE_ROUTE_PEOPLE
17783: WHERE step_id = cr.step_id
17784: AND assignee_id = l_items_org_role_table(t_index).party_id;
17785:
17786: IF (l_people_existed_flag = 0) THEN

Line 17788: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17784: AND assignee_id = l_items_org_role_table(t_index).party_id;
17785:
17786: IF (l_people_existed_flag = 0) THEN
17787: -- generate new people id
17788: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17789: into l_people_id
17790: FROM DUAL;
17791:
17792: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

Line 17792: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17788: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17789: into l_people_id
17790: FROM DUAL;
17791:
17792: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17793: (route_people_id,
17794: step_id,
17795: assignee_id,
17796: assignee_type_code,

Line 17839: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (

17835: cr.assignee_type_code,
17836: cr.response_condition_code,
17837: cr.route_people_id);
17838:
17839: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
17840: ROUTE_PEOPLE_ID,
17841: CREATION_DATE,
17842: CREATED_BY,
17843: LAST_UPDATE_DATE,

Line 17863: from ENG_CHANGE_ROUTE_PEOPLE_TL T

17859: from FND_LANGUAGES L
17860: where L.INSTALLED_FLAG in ('I', 'B')
17861: and not exists
17862: (select NULL
17863: from ENG_CHANGE_ROUTE_PEOPLE_TL T
17864: where T.ROUTE_PEOPLE_ID = l_people_id
17865: and T.LANGUAGE = L.LANGUAGE_CODE);
17866:
17867: l_row_inserted_flag := 1; -- bug 13860012

Line 17875: FROM ENG_CHANGE_ROUTE_PEOPLE

17871: FOR cr2 IN C_GET_GROUP_MEMBER(cp_group_id => l_items_org_role_table(t_index).party_id) LOOP
17872: -- check if the person is created in current step, if l_people_existed_flag = 0, then create
17873: SELECT COUNT(1)
17874: INTO l_people_existed_flag
17875: FROM ENG_CHANGE_ROUTE_PEOPLE
17876: WHERE step_id = cr.step_id
17877: AND assignee_id = cr2.member_person_id;
17878:
17879: IF (l_people_existed_flag = 0) THEN

Line 17881: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17877: AND assignee_id = cr2.member_person_id;
17878:
17879: IF (l_people_existed_flag = 0) THEN
17880: -- generate new people id
17881: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17882: into l_people_id
17883: FROM DUAL;
17884:
17885: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

Line 17885: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17881: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17882: into l_people_id
17883: FROM DUAL;
17884:
17885: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17886: (route_people_id,
17887: step_id,
17888: assignee_id,
17889: assignee_type_code,

Line 17932: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (

17928: cr.assignee_type_code,
17929: cr.response_condition_code,
17930: cr.route_people_id);
17931:
17932: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
17933: ROUTE_PEOPLE_ID,
17934: CREATION_DATE,
17935: CREATED_BY,
17936: LAST_UPDATE_DATE,

Line 17956: from ENG_CHANGE_ROUTE_PEOPLE_TL T

17952: from FND_LANGUAGES L
17953: where L.INSTALLED_FLAG in ('I', 'B')
17954: and not exists
17955: (select NULL
17956: from ENG_CHANGE_ROUTE_PEOPLE_TL T
17957: where T.ROUTE_PEOPLE_ID = l_people_id
17958: and T.LANGUAGE = L.LANGUAGE_CODE);
17959:
17960: l_row_inserted_flag := 1; -- bug 13860012

Line 17978: FROM ENG_CHANGE_ROUTE_PEOPLE

17974: -- if the role's type = 'PERSON', then start to create the people
17975: -- check if the person is created in current step, if l_people_existed_flag = 0, then create
17976: SELECT COUNT(1)
17977: INTO l_people_existed_flag
17978: FROM ENG_CHANGE_ROUTE_PEOPLE
17979: WHERE step_id = cr.step_id
17980: AND assignee_id = l_items_org_role_table(t_index).party_id;
17981:
17982: IF (l_people_existed_flag = 0) THEN

Line 17984: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

17980: AND assignee_id = l_items_org_role_table(t_index).party_id;
17981:
17982: IF (l_people_existed_flag = 0) THEN
17983: -- generate new people id
17984: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17985: into l_people_id
17986: FROM DUAL;
17987:
17988: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

Line 17988: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

17984: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
17985: into l_people_id
17986: FROM DUAL;
17987:
17988: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
17989: (route_people_id,
17990: step_id,
17991: assignee_id,
17992: assignee_type_code,

Line 18035: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (

18031: cr.assignee_type_code,
18032: cr.response_condition_code,
18033: cr.route_people_id);
18034:
18035: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
18036: ROUTE_PEOPLE_ID,
18037: CREATION_DATE,
18038: CREATED_BY,
18039: LAST_UPDATE_DATE,

Line 18059: from ENG_CHANGE_ROUTE_PEOPLE_TL T

18055: from FND_LANGUAGES L
18056: where L.INSTALLED_FLAG in ('I', 'B')
18057: and not exists
18058: (select NULL
18059: from ENG_CHANGE_ROUTE_PEOPLE_TL T
18060: where T.ROUTE_PEOPLE_ID = l_people_id
18061: and T.LANGUAGE = L.LANGUAGE_CODE);
18062:
18063: l_row_inserted_flag := 1; -- bug 13860012

Line 18071: FROM ENG_CHANGE_ROUTE_PEOPLE

18067: FOR cr2 IN C_GET_GROUP_MEMBER(cp_group_id => l_items_org_role_table(t_index).party_id) LOOP
18068: -- check if the person is created in current step, if l_people_existed_flag = 0, then create
18069: SELECT COUNT(1)
18070: INTO l_people_existed_flag
18071: FROM ENG_CHANGE_ROUTE_PEOPLE
18072: WHERE step_id = cr.step_id
18073: AND assignee_id = cr2.member_person_id;
18074:
18075: IF (l_people_existed_flag = 0) THEN

Line 18077: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

18073: AND assignee_id = cr2.member_person_id;
18074:
18075: IF (l_people_existed_flag = 0) THEN
18076: -- generate new people id
18077: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
18078: into l_people_id
18079: FROM DUAL;
18080:
18081: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

Line 18081: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

18077: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
18078: into l_people_id
18079: FROM DUAL;
18080:
18081: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
18082: (route_people_id,
18083: step_id,
18084: assignee_id,
18085: assignee_type_code,

Line 18128: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (

18124: cr.assignee_type_code,
18125: cr.response_condition_code,
18126: cr.route_people_id);
18127:
18128: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
18129: ROUTE_PEOPLE_ID,
18130: CREATION_DATE,
18131: CREATED_BY,
18132: LAST_UPDATE_DATE,

Line 18152: from ENG_CHANGE_ROUTE_PEOPLE_TL T

18148: from FND_LANGUAGES L
18149: where L.INSTALLED_FLAG in ('I', 'B')
18150: and not exists
18151: (select NULL
18152: from ENG_CHANGE_ROUTE_PEOPLE_TL T
18153: where T.ROUTE_PEOPLE_ID = l_people_id
18154: and T.LANGUAGE = L.LANGUAGE_CODE);
18155:
18156: l_row_inserted_flag := 1; -- bug 13860012

Line 18169: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL

18165: END IF;
18166: END LOOP; -- FOR t_index in 1..l_items_org_role_table.last
18167: IF(l_row_inserted_flag = 0) THEN -- bug 13860012
18168: -- generate new people id
18169: SELECT ENG_CHANGE_ROUTE_PEOPLE_S.NEXTVAL
18170: into l_people_id
18171: FROM DUAL;
18172:
18173: --insert an 'Unassigned' assignee record

Line 18174: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE

18170: into l_people_id
18171: FROM DUAL;
18172:
18173: --insert an 'Unassigned' assignee record
18174: INSERT INTO ENG_CHANGE_ROUTE_PEOPLE
18175: (route_people_id,
18176: step_id,
18177: assignee_id,
18178: assignee_type_code,

Line 18221: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (

18217: cr.assignee_type_code,
18218: cr.response_condition_code,
18219: cr.route_people_id);
18220:
18221: insert into ENG_CHANGE_ROUTE_PEOPLE_TL (
18222: ROUTE_PEOPLE_ID,
18223: CREATION_DATE,
18224: CREATED_BY,
18225: LAST_UPDATE_DATE,

Line 18245: from ENG_CHANGE_ROUTE_PEOPLE_TL T

18241: from FND_LANGUAGES L
18242: where L.INSTALLED_FLAG in ('I', 'B')
18243: and not exists
18244: (select NULL
18245: from ENG_CHANGE_ROUTE_PEOPLE_TL T
18246: where T.ROUTE_PEOPLE_ID = l_people_id
18247: and T.LANGUAGE = L.LANGUAGE_CODE);
18248: END IF;
18249: END IF; -- IF GROUP/ROLE

Line 18253: DELETE FROM ENG_CHANGE_ROUTE_PEOPLE

18249: END IF; -- IF GROUP/ROLE
18250: END IF; --IF Assignee: bug 13921167
18251:
18252: -- Remove the original copied assignee object (Group/Role)
18253: DELETE FROM ENG_CHANGE_ROUTE_PEOPLE
18254: WHERE route_people_id = cr.route_people_id;
18255:
18256: DELETE FROM ENG_CHANGE_ROUTE_PEOPLE_TL
18257: WHERE route_people_id = cr.route_people_id;

Line 18256: DELETE FROM ENG_CHANGE_ROUTE_PEOPLE_TL

18252: -- Remove the original copied assignee object (Group/Role)
18253: DELETE FROM ENG_CHANGE_ROUTE_PEOPLE
18254: WHERE route_people_id = cr.route_people_id;
18255:
18256: DELETE FROM ENG_CHANGE_ROUTE_PEOPLE_TL
18257: WHERE route_people_id = cr.route_people_id;
18258:
18259: END LOOP; -- end cursor C_GET_ROUTE_STEP_ASSIGNEE
18260: