1 PACKAGE PA_TXN_ACCUMS AUTHID CURRENT_USER AS
2 /* $Header: PATXNACS.pls 120.2.12020000.2 2012/10/26 12:42:13 udshanmu ship $ */
3
4 -- Standard who
5 x_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
6 x_last_update_date NUMBER(15) := FND_GLOBAL.USER_ID;
7 x_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
8 x_last_update_login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
9 x_request_id NUMBER(15) := FND_GLOBAL.CONC_REQUEST_ID;
10 x_program_application_id NUMBER(15) := FND_GLOBAL.PROG_APPL_ID;
11 x_program_id NUMBER(15) := FND_GLOBAL.CONC_PROGRAM_ID;
12
13
14 -- Variable to store the accumulation configuration
15
16 raw_cost_flag VARCHAR2(1);
17 burdened_cost_flag VARCHAR2(1);
18 quantity_flag VARCHAR2(1);
19 labor_hours_flag VARCHAR2(1);
20 billable_raw_cost_flag VARCHAR2(1);
21 billable_burdened_cost_flag VARCHAR2(1);
22 billable_quantity_flag VARCHAR2(1);
23 billable_labor_hours_flag VARCHAR2(1);
24 revenue_flag VARCHAR2(1);
25 cmt_raw_cost_flag VARCHAR2(1);
26 cmt_burdened_cost_flag VARCHAR2(1);
27
28 /* Start of Code changes for bug 13448783 */
29
30 TYPE cdlrowid_tb is TABLE of ROWID;
31 TYPE x_txn_accum_id_tb is TABLE of NUMBER;
32 TYPE x_tot_raw_cost_tb is TABLE of NUMBER;
33 TYPE x_tot_burdened_cost_tb is TABLE of NUMBER;
34 TYPE x_tot_quantity_tb is TABLE of NUMBER;
35 TYPE x_tot_billable_raw_cost_tb is TABLE of NUMBER;
36 TYPE x_tot_billable_brdn_cost_tb is TABLE of NUMBER;
37 TYPE x_tot_billable_quantity_tb is TABLE of NUMBER;
38 TYPE x_unit_of_measure_tb is TABLE of VARCHAR2(30);
39
40 /* End of Code changes for bug 13448783 */
41
42 FUNCTION Initialize RETURN NUMBER;
43 FUNCTION cmt_line_id RETURN NUMBER;
44
45 pragma RESTRICT_REFERENCES (cmt_line_id, WNDS, WNPS );
46
47 PROCEDURE get_accum_configurations
48 ( x_project_id IN NUMBER,
49 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
50 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
51
52 PROCEDURE update_resource_flag
53 (x_start_project_id IN NUMBER,
54 x_end_project_id IN NUMBER,
55 x_start_pa_date IN DATE,
56 x_end_pa_date IN DATE,
57 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
58 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
59
60 PROCEDURE refresh_txn_accum
61 ( x_start_project_id IN NUMBER,
62 x_end_project_id IN NUMBER,
63 x_start_pa_date IN DATE,
64 x_end_pa_date IN DATE,
65 x_transaction_type IN VARCHAR2 DEFAULT NULL,
66 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
67 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
68 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
69
70 PROCEDURE refresh_act_txn_accum
71 ( x_start_project_id IN NUMBER,
72 x_end_project_id IN NUMBER,
73 x_start_pa_date IN DATE,
74 x_end_pa_date IN DATE,
75 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
76 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
77 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
78
79 PROCEDURE delete_act_txn_accum_details
80 ( x_start_project_id IN NUMBER,
81 x_end_project_id IN NUMBER,
82 x_start_pa_date IN DATE,
83 x_end_pa_date IN DATE,
84 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
85 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
86 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
87
88 PROCEDURE refresh_rev_txn_accum
89 ( x_start_project_id IN NUMBER,
90 x_end_project_id IN NUMBER,
91 x_start_pa_date IN DATE,
92 x_end_pa_date IN DATE,
93 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
94 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
95
96 PROCEDURE delete_rev_txn_accum_details
97 ( x_start_project_id IN NUMBER,
98 x_end_project_id IN NUMBER,
99 x_start_pa_date IN DATE,
100 x_end_pa_date IN DATE,
101 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
102 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
103
104 PROCEDURE refresh_cmt_txn_accum
105 ( x_start_project_id IN NUMBER,
106 x_end_project_id IN NUMBER,
107 x_start_pa_date IN DATE,
108 x_end_pa_date IN DATE,
109 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
110 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
111 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
112
113 PROCEDURE update_act_txn_accum
114 ( x_start_project_id IN NUMBER,
115 x_end_project_id IN NUMBER,
116 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
117 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
118
119 PROCEDURE delete_cmt_txn_accum_details
120 ( x_start_project_id IN NUMBER,
121 x_end_project_id IN NUMBER,
122 x_start_pa_date IN DATE,
123 x_end_pa_date IN DATE,
124 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
125 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
126 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
127
128 PROCEDURE create_txn_accum_details
129 (x_txn_accum_id IN NUMBER,
130 x_line_type IN VARCHAR2,
131 x_expenditure_item_id IN NUMBER DEFAULT NULL,
132 x_line_num IN NUMBER DEFAULT NULL,
133 x_event_num IN NUMBER DEFAULT NULL,
134 x_cmt_line_id IN NUMBER DEFAULT NULL,
135 x_project_id IN NUMBER DEFAULT NULL,
136 x_task_id IN NUMBER DEFAULT NULL,
137 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
138 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
139
140 PROCEDURE create_txn_accum
141 ( x_project_id IN NUMBER,
142 x_task_Id IN NUMBER,
143 x_pa_period IN VARCHAR2,
144 x_gl_period IN VARCHAR2,
145 x_week_ending_date IN DATE,
146 x_month_ending_date IN DATE,
147 x_person_id IN NUMBER DEFAULT NULL,
148 x_job_id IN NUMBER DEFAULT NULL,
149 x_vendor_id IN VARCHAR2 DEFAULT NULL,
150 x_expenditure_type IN VARCHAR2 DEFAULT NULL,
151 x_organization_id IN NUMBER DEFAULT NULL,
152 x_non_labor_resource IN VARCHAR2 DEFAULT NULL,
153 x_non_labor_resource_org_id IN NUMBER DEFAULT NULL,
154 x_expenditure_category IN VARCHAR2 DEFAULT NULL,
155 x_revenue_category IN VARCHAR2 DEFAULT NULL,
156 x_event_type IN VARCHAR2 DEFAULT NULL,
157 x_event_type_classification IN VARCHAR2 DEFAULT NULL,
158 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
159 x_line_type IN VARCHAR2 DEFAULT NULL,
160 x_cost_ind_compiled_set_id IN NUMBER DEFAULT NULL,
161 x_rev_ind_compiled_set_id IN NUMBER DEFAULT NULL,
162 x_inv_ind_compiled_set_id IN NUMBER DEFAULT NULL,
163 x_cmt_ind_compiled_set_id IN NUMBER DEFAULT NULL,
164 x_txn_accum_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
165 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
166 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
167
168 PROCEDURE delete_cmt_txns
169 ( x_start_project_id IN NUMBER,
170 x_end_project_id IN NUMBER,
171 x_start_pa_date IN DATE,
172 x_end_pa_date IN DATE,
173 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
174 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
175 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
176
177 PROCEDURE create_cmt_txns
178 ( x_start_project_id IN NUMBER,
179 x_end_project_id IN NUMBER,
180 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
181 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
182 x_err_code IN OUT NOCOPY NUMBER,
183 x_use_tmp_table IN VARCHAR2 DEFAULT 'N'); --File.Sql.39 bug 4440895
184
185 /* Modified proc for bug13448783 */
186
187 /* PROCEDURE accum_act_txn
188 ( x_txn_accum_id IN NUMBER,
189 x_tot_raw_cost IN NUMBER,
190 x_tot_burdened_cost IN NUMBER,
191 x_tot_quantity IN NUMBER,
192 x_tot_billable_raw_cost IN NUMBER,
193 x_tot_billable_burdened_cost IN NUMBER,
194 x_tot_billable_quantity IN NUMBER,
195 x_unit_of_measure IN VARCHAR2,
196 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
197 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895 */
198
199 PROCEDURE accum_act_txn
200 ( x_txn_accum_id_tb2 x_txn_accum_id_tb,
201 x_tot_raw_cost_tb2 x_tot_raw_cost_tb,
202 x_tot_burdened_cost_tb2 x_tot_burdened_cost_tb,
203 x_tot_quantity_tb2 x_tot_quantity_tb,
204 x_tot_billable_raw_cost_tb2 x_tot_billable_raw_cost_tb,
205 x_tot_billable_brdn_cost_tb2 x_tot_billable_brdn_cost_tb,
206 x_tot_billable_quantity_tb2 x_tot_billable_quantity_tb,
207 x_unit_of_measure_tb2 x_unit_of_measure_tb,
208 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
209 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
210
211
212 PROCEDURE accum_rev_txn
213 ( x_txn_accum_id IN NUMBER,
214 x_tot_revenue IN NUMBER,
215 x_unit_of_measure IN VARCHAR2,
216 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
217 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
218
219 PROCEDURE accum_cmt_txn
220 ( x_txn_accum_id IN NUMBER,
221 x_tot_cmt_raw_cost IN NUMBER,
222 x_tot_cmt_burdened_cost IN NUMBER,
223 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
224 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
225 PROCEDURE accum_cdls
226 ( x_start_project_id IN NUMBER,
227 x_end_project_id IN NUMBER,
228 x_start_pa_date IN DATE,
229 x_end_pa_date IN DATE,
230 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
231 x_mode IN VARCHAR2 DEFAULT 'I',
232 x_cdl_line_type IN VARCHAR2 DEFAULT 'R', -- patchSet L Reburdening
233 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
234 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
235 PROCEDURE accum_rdls
236 ( x_start_project_id IN NUMBER,
237 x_end_project_id IN NUMBER,
238 x_start_pa_date IN DATE,
239 x_end_pa_date IN DATE,
240 x_mode IN VARCHAR2 DEFAULT 'I',
241 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
242 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
243 PROCEDURE accum_erdls
244 ( x_start_project_id IN NUMBER,
245 x_end_project_id IN NUMBER,
246 x_start_pa_date IN DATE,
247 x_end_pa_date IN DATE,
248 x_mode IN VARCHAR2 DEFAULT 'I',
249 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
250 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
251 PROCEDURE accum_revenue
252 ( x_start_project_id IN NUMBER,
253 x_end_project_id IN NUMBER,
254 x_start_pa_date IN DATE,
255 x_end_pa_date IN DATE,
256 x_mode IN VARCHAR2 DEFAULT 'I',
257 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
258 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
259
260 PROCEDURE accum_commitments
261 ( x_start_project_id IN NUMBER,
262 x_end_project_id IN NUMBER,
263 x_system_linkage_function IN VARCHAR2 DEFAULT NULL,
264 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
265 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
266
267 END PA_TXN_ACCUMS;