[Home] [Help]
257:
258: IS
259: CURSOR c1 IS
260: SELECT object_type_to, object_id_to1, object_id_to2, object_id_to3, object_id_to4, object_id_to5
261: FROM pa_object_relationships
262: WHERE relationship_type = 'A'
263: AND relationship_subtype = 'PROJECT_REQUEST'
264: START WITH (object_type_from = p_object_type_from
265: AND object_id_from1 = p_object_id_from1)
518: -- Cursor to get the source lead_id for the passed in project request.
519:
520: CURSOR cur_lead_id IS
521: SELECT object_id_to1
522: FROM pa_object_relationships
523: WHERE relationship_type = 'A'
524: AND relationship_subtype = 'PROJECT_REQUEST'
525: AND object_type_from = 'PA_PROJECT_REQUESTS'
526: AND object_id_from1 = p_request_id
1190: --Modified stmt_Class5 for bug 5728842. Added o.object_type_to = 'PA_PROJECT_REQUESTS' condition.
1191: stmt_class5 :='AND l.lead_id NOT IN'
1192: ||' (SELECT l.lead_id'
1193: ||' FROM as_leads l,'
1194: ||' pa_object_relationships o,'
1195: ||' pa_project_requests r'
1196: ||' WHERE l.lead_id = o.object_id_from1'
1197: ||' AND o.object_id_to1 = r.request_id'
1198: ||' AND o.object_type_to = ''PA_PROJECT_REQUESTS'''
1259:
1260: stmt_categ5 :=' AND l.lead_id NOT IN'
1261: ||' (SELECT l.lead_id'
1262: ||' FROM as_leads l,'
1263: ||' pa_object_relationships o,'
1264: ||' pa_project_requests r'
1265: ||' WHERE l.lead_id = o.object_id_from1'
1266: ||' AND o.object_id_to1 = r.request_id'
1267: ||' AND r.request_type =:11 '
1769: || 'r.request_id request_id,'
1770: || 'l.lead_id lead_id,'
1771: || 'null as category ' ;
1772:
1773: stmt_class2 := ' FROM as_leads l, as_interests i, pa_project_requests r, pa_object_relationships o,pa_project_statuses s '
1774: || ' WHERE '
1775: || ' (l.win_probability >= nvl(:1, 0) OR'
1776: || ' (l.win_probability IS NULL AND :1 IS NULL)'
1777: ||' ) '
1827: || ' r.request_id request_id,'
1828: || 'l.lead_id lead_id,'
1829: || 'h.category category ' ;
1830:
1831: stmt_categ2 := ' FROM as_leads l,as_lead_lines ll,pa_lead_categories_v h,pa_project_requests r, pa_object_relationships o,pa_project_statuses s '
1832: || ' WHERE '
1833: || 'll.product_category_id = h.category_id '
1834: || ' AND l.lead_id = ll.lead_id '
1835: || ' AND ('
2049: --get the project ID for a passed in request ID, relationship direction: Request -> Project
2050:
2051: SELECT object_id_to1
2052: INTO l_project_id
2053: FROM pa_object_relationships
2054: WHERE relationship_type='A'
2055: AND relationship_subtype ='PROJECT_REQUEST'
2056: AND object_type_from ='PA_PROJECT_REQUESTS'
2057: AND object_type_to = 'PA_PROJECTS'
2536: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2537: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2538: x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2539:
2540: l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
2541: l_new_obj_rel_id2 PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
2542:
2543: BEGIN
2544: x_return_status := FND_API.G_RET_STS_SUCCESS;
2537: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2538: x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2539:
2540: l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
2541: l_new_obj_rel_id2 PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
2542:
2543: BEGIN
2544: x_return_status := FND_API.G_RET_STS_SUCCESS;
2545:
2544: x_return_status := FND_API.G_RET_STS_SUCCESS;
2545:
2546: -- Form the relationship: from the project request to the created project.
2547:
2548: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
2549: p_user_id => FND_GLOBAL.USER_ID
2550: ,p_object_type_from => 'PA_PROJECT_REQUESTS'
2551: ,p_object_id_from1 => p_request_id
2552: ,p_object_id_from2 => NULL
2571:
2572: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2573: -- Form the relationship: from the created project to the source request.
2574:
2575: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
2576: p_user_id => FND_GLOBAL.USER_ID
2577: ,p_object_type_from => 'PA_PROJECTS'
2578: ,p_object_id_from1 => p_project_id
2579: ,p_object_id_from2 => NULL