[Home] [Help]
31: or filled*/
32:
33: CURSOR CUR_REQUIREMENTS_CLOSED IS
34: SELECT assignment_id
35: FROM pa_project_assignments pa WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
36: AND pa.project_id = P_PROJECT_ID;
37:
38:
39: /*This cursor will select all the assignments, which are either cancelled or filled
41: applicable for open indirect project purge.*/
42:
43: CURSOR CUR_REQUIREMENTS_ACTIVE IS
44: SELECT pa.assignment_id
45: FROM pa_project_assignments pa, pa_project_statuses ps
46: WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
47: AND pa.status_code = ps.project_status_code
48: AND ps.status_type='OPEN_ASGMT'
49: AND ps.project_system_status_code <> 'OPEN_ASGMT'
49: AND ps.project_system_status_code <> 'OPEN_ASGMT'
50: AND pa.project_id = P_PROJECT_ID
51: AND pa.end_date <= P_txn_to_date
52: AND NOT EXISTS (Select NULL
53: From pa_project_assignments pa1,pa_project_statuses pps
54: where pa1.source_assignment_id=pa.assignment_id
55: AND pa1.end_date >P_txn_to_date
56: AND pa1.status_code = pps.project_status_code
57: AND pps.status_type = 'STAFFED_ASGMT'
363: conditions are mentioned as the purge program will purge closed project
364: assignments only if the all the assignmnets are eligible for purging.*/
365:
366: CURSOR CUR_ASSIGNMENTS_CLOSED IS
367: SELECT assignment_id FROM Pa_project_assignments pa
368: WHERE pa.project_id = P_Project_Id
369: AND pa.assignment_type <>'OPEN_ASSIGNMENT';
370:
371:
373: falling before purge till date. This cursor is applicable in case of open project
374: purge. In this case, we purge all the assignments falling before purge till date.*/
375:
376: CURSOR CUR_ASSIGNMENTS_ACTIVE IS
377: SELECT assignment_id FROM Pa_project_assignments pa
378: WHERE pa.project_id = P_Project_Id
379: AND pa.assignment_type <>'OPEN_ASSIGNMENT'
380: AND p_txn_to_date >= pa.end_date;
381:
2001: ----------------------------------------------------------------------------------
2002: -- Cursor to fetch all the conflicting projects based on the assignment_id passed.
2003: -- Added for Bug 2979944
2004: ----------------------------------------------------------------------------------
2005: CURSOR c1 (x_assignment_id pa_project_assignments.assignment_id%TYPE) IS
2006: SELECT DISTINCT asgn.project_id
2007: FROM pa_project_assignments asgn,
2008: pa_assignment_conflict_hist hist
2009: WHERE asgn.assignment_id = hist.conflict_assignment_id
2003: -- Added for Bug 2979944
2004: ----------------------------------------------------------------------------------
2005: CURSOR c1 (x_assignment_id pa_project_assignments.assignment_id%TYPE) IS
2006: SELECT DISTINCT asgn.project_id
2007: FROM pa_project_assignments asgn,
2008: pa_assignment_conflict_hist hist
2009: WHERE asgn.assignment_id = hist.conflict_assignment_id
2010: AND hist.assignment_id = x_assignment_id;
2011:
2182: -- Start of comments
2183: -- API name : PA_PROJECT_ASSIGNMENT_PURGE
2184: -- Type : Public
2185: -- Pre-reqs : None
2186: -- Function : Main purge procedure for Purging records from PA_PROJECT_ASSIGNMENTS table
2187:
2188:
2189: Procedure PA_PROJECT_ASSIGNMENT_PURGE ( p_purge_batch_id in NUMBER,
2190: p_project_id in NUMBER,
2479: rate_disc_reason_Code,
2480: Transfer_price_Rate, -- Added for bug 3051110
2481: Transfer_pr_rate_curr,
2482: Resource_List_Member_Id
2483: FROM pa_project_assignments where assignment_id=l_assignment_id;
2484:
2485: /*Increase the value of l_nos_assignments_inserted to indicate number of records inserted in assignments_ar table.
2486: The value will increase for each loop(assignment id)*/
2487: l_nos_assignments_inserted := l_nos_assignments_inserted + SQL%ROWCOUNT;
2687: l_nos_assignments_his_inserted := l_nos_assignments_his_inserted + SQL%ROWCOUNT;
2688:
2689: END IF;
2690:
2691: /*To keep the count of no of records deleted from pa_project_assignments and pa_assignments_history, manipulate the
2692: count of l_nos_assignments_deleted and l_nos_assignments_his_deleted. */
2693:
2694: pa_debug.debug('Deleting Records from pa_assignments_history table ') ;
2695: x_err_stage := 'Deleting Records from pa_assignments_history table for id '||to_char(l_assignment_id) ;
2701:
2702: pa_debug.debug('Deleting Records from pa_assignments table ') ;
2703: x_err_stage := 'Deleting Records from pa_assignments table for id '||to_char(l_assignment_id) ;
2704:
2705: DELETE PA_PROJECT_ASSIGNMENTS
2706: WHERE assignment_id =l_assignment_id;
2707:
2708: l_nos_assignments_deleted :=l_nos_assignments_deleted + SQL%ROWCOUNT;
2709:
2711:
2712:
2713: pa_purge.CommitProcess(p_purge_batch_id,
2714: p_project_id,
2715: 'PA_PROJECT_ASSIGNMENTS',
2716: l_nos_assignments_inserted,
2717: l_nos_assignments_deleted,
2718: x_err_code,
2719: x_err_stack,
2834: Last_Update_Date,
2835: Last_Updated_By,
2836: Last_Update_Login,
2837: Grant_Id
2838: FROM PA_PROJECT_PARTIES WHERE project_party_id = (SELECT project_party_id from pa_project_assignments
2839: WHERE assignment_id=l_assignment_id);
2840:
2841: /*Increase the value of l_assignments_his_inserted to indicate number of records inserted in assignments_history_ar
2842: table. The value will increase for each loop*/
2851: pa_debug.debug('Deleting Records from pa_project_parties table ') ;
2852: x_err_stage := 'Deleting Records from pa_project_parties table for id '||to_char(l_assignment_id) ;
2853:
2854: DELETE PA_PROJECT_PARTIES
2855: WHERE project_party_id =(SELECT project_party_id FROM pa_project_assignments
2856: WHERE assignment_id=l_assignment_id);
2857:
2858: l_nos_parties_deleted :=l_nos_parties_deleted + SQL%ROWCOUNT;
2859:
2917: l_nos_set_lines_deleted NUMBER;
2918: l_nos_lines_cond_deleted NUMBER;
2919: l_nos_lines_aud_deleted NUMBER;
2920:
2921: CURSOR Cur_action_sets (x_assignment_id IN pa_project_assignments.assignment_id%TYPE) IS
2922: SELECT action_set_id From Pa_action_sets
2923: WHERE action_set_type_code = 'ADVERTISEMENT'
2924: AND object_type ='OPEN_ASSIGNMENT'
2925: AND object_id=x_assignment_id;