1 Package pa_purge as
2 /* $Header: PAXPRMNS.pls 120.1 2005/08/19 17:17:39 mwasowic noship $ */
3
4 -- Start of comments
5 -- API name : Purge
6 -- Type : Public
7 -- Pre-reqs : None
8 -- Function : This procedure is called from the form.
9 -- Main purge procedure.
10 -- Invokes the purge_project procedure for each project
11 -- in the purge batch
12 -- Parameters p_batch_id IN NUMBER
13 -- The purge batch id for which rows have
14 -- to be purged/archived.
15 -- p_Commit_Size IN NUMBER,
16 -- The commit size
17 -- errbuf IN OUT VARCHAR2,
18 -- error buffer containing the SQLERRM
19 -- ret_code IN OUT NUMBER
20 -- Standard error code returned from the procedure
21 -- = 0 SUCCESS
22 -- < 0 Oracle error
23 -- End of comments
24 Procedure Purge (
25 p_Batch_Id IN NUMBER ,
26 p_Commit_Size IN NUMBER ,
27 ret_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 errbuf IN OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
29
30 -- Start of comments
31 -- API name : Purge_Project
32 -- Type : Private
33 -- Pre-reqs : None
34 -- Function : Invokes the procedure for purge for a specific project for the
35 -- various modules ( Costing , billing ,Project tracking , capital
36 -- projects) based on the option selection during the purge batch
37 -- creation.
38 -- In addition also invokes a client extension
39 -- for any customer specific purge procedures
40 --
41 -- Parameters p_batch_id IN NUMBER
42 -- The purge batch id for which rows have
43 -- to be purged/archived.
44 -- p_project_Id IN NUMBER,
45 -- The project id for which records have
46 -- to be purged/archived.
47 -- p_Active_Closed_Flag IN VARCHAR2,
48 -- Indicates if batch contains ACTIVE or CLOSED projects
49 -- ( 'A' - Active , 'C' - Closed)
50 -- p_Purge_Release IN VARCHAR2,
51 -- Oracle Projects release (10.7 , 11.0)
52 -- p_Purge_Summary_Flag IN VARCHAR2,
53 -- Purge Summary tables data
54 -- p_Purge_Capital_Flag IN VARCHAR2,
55 -- Purge Capital projects tables data
56 -- p_Purge_Budgets_Flag IN VARCHAR2,
57 -- Purge Budget tables data
58 -- p_Purge_Actuals_Flag IN VARCHAR2,
59 -- Purge Actuals tables data i.e. Costing and Billing tables
60 -- p_Archive_Summary_Flag IN VARCHAR2,
61 -- Archive Summary tables data
62 -- p_Archive_Capital_Flag IN VARCHAR2,
63 -- Purge Capital projects tables data
64 -- p_Archive_Budgets_Flag IN VARCHAR2,
65 -- Archive Budget tables data
66 -- p_Archive_Actuals_Flag IN VARCHAR2,
67 -- Archive Actuals tables data i.e. Costing and Billing tables
68 -- p_Txn_To_Date IN DATE,
69 -- Date on or before which all transactions are to be purged
70 -- (Will be used by Costing only)
71 -- p_Commit_Size IN NUMBER,
72 -- The commit size
73 -- X_Err_Stack IN OUT VARCHAR2,
74 -- Error stack
75 -- X_Err_Stage IN OUT VARCHAR2,
76 -- Stage in the procedure where error occurred
77 -- X_Err_Code IN OUT NUMBER
78 -- Error code returned from the procedure
79 -- = 0 SUCCESS
80 -- > 0 Application error
81 -- < 0 Oracle error
82 -- End of comments
83 Procedure Purge_Project(
84 p_batch_id IN NUMBER,
85 p_project_Id IN NUMBER,
86 p_Active_Closed_Flag IN VARCHAR2,
87 p_Purge_release IN VARCHAR2,
88 p_Purge_Summary_Flag IN VARCHAR2,
89 p_Purge_Capital_Flag IN VARCHAR2,
90 p_Purge_Budgets_Flag IN VARCHAR2,
91 p_Purge_Actuals_Flag IN VARCHAR2,
92 p_Archive_Summary_Flag IN VARCHAR2,
93 p_Archive_Capital_Flag IN VARCHAR2,
94 p_Archive_Budgets_Flag IN VARCHAR2,
95 p_Archive_Actuals_Flag IN VARCHAR2,
96 p_Txn_To_Date IN DATE,
97 p_Commit_Size IN NUMBER,
98 X_Err_Stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
99 X_Err_Stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
100 X_Err_Code IN OUT NOCOPY NUMBER) ; --File.Sql.39 bug 4440895
101
102
103 -- Start of comments
104 -- API name : CommitProcess
105 -- Type : Public
106 -- Pre-reqs : None
107 -- Function : Common procedure for commit.
108 -- Will be invoked from the various purge procedures
109 --
110 -- Parameters p_batch_id IN NUMBER
111 -- The purge batch id for which rows have
112 -- been purged/archived.
113 -- p_project_Id IN NUMBER,
114 -- The project id for which records have
115 -- been purged/archived.
116 -- p_Commit_Size IN NUMBER,
117 -- The commit size
118 -- p_table_name IN VARCHAR2,
119 -- The table for which rows have been purged
120 -- p_NoOfRecordsIns IN NUMBER,
121 -- No. of records inserted into the archive table
122 -- p_NoOfRecordsDel IN NUMBER,
123 -- No. of records deleted from table
124 -- X_Err_Stack IN OUT VARCHAR2,
125 -- Error stack
126 -- X_Err_Stage IN OUT VARCHAR2,
127 -- Stage in the procedure where error occurred
128 -- X_Err_Code IN OUT NUMBER,
129 -- Error code returned from the procedure
130 -- = 0 SUCCESS
131 -- > 0 Application error
132 -- < 0 Oracle error
133 -- p_MRC_table_name IN VARCHAR2,
134 -- The MRC table for which rows have been purged
135 -- p_MRC_NoOfRecordsIns IN NUMBER,
136 -- No. of records inserted into the MRC archive table
137 -- p_MRC_NoOfRecordsDel IN NUMBER
138 -- No. of records deleted from MRC table
139 -- End of comments
140 Procedure CommitProcess(p_purge_batch_id IN NUMBER,
141 p_project_id IN NUMBER,
142 p_table_name IN VARCHAR2,
143 p_NoOfRecordsIns IN NUMBER,
144 p_NoOfRecordsDel IN NUMBER,
145 x_err_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
146 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
147 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
148 p_MRC_table_name IN VARCHAR2 DEFAULT NULL,
149 p_MRC_NoOfRecordsIns IN NUMBER DEFAULT NULL,
150 p_MRC_NoOfRecordsDel IN NUMBER DEFAULT NULL
151 );
152
153 -- Start of comments
154 -- API name : get_post_purge_status
155 -- Type : Private
156 -- Pre-reqs : None
157 -- Function : This function checks if the project is fully purged or
158 -- partially purged for close projects and returns
159 -- Returns the project status code ' Fully_Purged' or 'Partially_Purged'
160 -- For active projects returns the old project status code
161 -- Parameters
162 -- p_project_Id IN NUMBER,
163 -- The project id for which purge status is to be determined
164 -- p_batch_id IN NUMBER,
165 -- The purge batch id
166 -- End of comments
167
168 Function get_post_purge_status ( p_project_id IN NUMBER , p_batch_id IN NUMBER) Return VARCHAR2;
169
170 end pa_purge; /*Specification*/