1 PACKAGE PA_CC_BL_PROCESS AUTHID CURRENT_USER AS
2 /* $Header: PAXBLPRS.pls 120.1.12010000.3 2008/10/17 05:17:36 abjacob ship $ */
3
4
5 -- Type of information retrieved from pa_cc_dist_lines_all or
6 -- expenditure item. A record of this type is defined as opposed to
7 -- defining a %ROWTYPE to take advantage of getting the rowid and
8 -- other attributes that do not belong to the pa_cc_dist_lines_all
9 -- table but to the expenditure item table eliminating the need to
10 -- have them re-read the table. The rowid is stored for better
11 -- performance and to avoid hitting the index of an already retrieved
12 -- record (for updates and deletes)
13
14 TYPE CcdRecType is RECORD (
15 EIRowId rowid,
16 CcdRowId rowid,
17 expenditure_item_id pa_cc_dist_lines_all.expenditure_item_id%TYPE,
18 cc_dist_line_id pa_cc_dist_lines_all.cc_dist_line_id%TYPE,
19 adjusted_exp_item_id pa_expenditure_items_all.adjusted_expenditure_item_id%TYPE,
20 acct_currency_code pa_cc_dist_lines_all.acct_currency_code%TYPE,
21 acct_tp_exchange_rate pa_cc_dist_lines_all.acct_tp_exchange_rate%TYPE,
22 acct_tp_rate_date pa_cc_dist_lines_all.acct_tp_rate_date%TYPE,
23 acct_tp_rate_type pa_cc_dist_lines_all.acct_tp_rate_type%TYPE,
24 amount pa_cc_dist_lines_all.amount%TYPE,
25 bill_markup_percentage pa_cc_dist_lines_all.bill_markup_percentage%TYPE,
26 bill_rate pa_cc_dist_lines_all.bill_rate%TYPE,
27 cc_rejection_code pa_expenditure_items_all.cc_rejection_code%TYPE,
28 cr_code_combination_id pa_cc_dist_lines_all.cr_code_combination_id%TYPE,
29 cross_charge_code pa_cc_dist_lines_all.cross_charge_code%TYPE,
30 denom_tp_currency_code pa_cc_dist_lines_all.denom_tp_currency_code%TYPE,
31 denom_transfer_price pa_cc_dist_lines_all.denom_transfer_price%TYPE,
32 dist_line_id_reversed pa_cc_dist_lines_all.dist_line_id_reversed%TYPE,
33 dr_code_combination_id pa_cc_dist_lines_all.dr_code_combination_id%TYPE,
34 expenditure_item_date date,
35 ind_compiled_set_id pa_cc_dist_lines_all.ind_compiled_set_id%TYPE,
36 line_num pa_cc_dist_lines_all.line_num%TYPE,
37 line_num_reversed pa_cc_dist_lines_all.line_num_reversed%TYPE,
38 line_type pa_cc_dist_lines_all.line_type%TYPE,
39 markup_calc_base_code pa_cc_dist_lines_all.markup_calc_base_code%TYPE,
40 pa_date pa_cc_dist_lines_all.pa_date%TYPE,
41 gl_date pa_cc_dist_lines_all.gl_date%TYPE, /* EPP */
42 pa_period_name pa_cc_dist_lines_all.pa_period_name%TYPE, /* EPP */
43 gl_period_name pa_cc_dist_lines_all.gl_period_name%TYPE, /* EPP */
44 project_id pa_cc_dist_lines_all.project_id%TYPE,
45 reversed_flag pa_cc_dist_lines_all.reversed_flag%TYPE,
46 rule_percentage pa_cc_dist_lines_all.rule_percentage%TYPE,
47 schedule_line_percentage pa_cc_dist_lines_all.schedule_line_percentage%TYPE,
48 task_id pa_cc_dist_lines_all.task_id%TYPE,
49 tp_base_amount pa_cc_dist_lines_all.tp_base_amount%TYPE,
50 tp_job_id pa_cc_dist_lines_all.tp_job_id%TYPE,
51 upd_type VARCHAR2(1),
52 reference_1 pa_cc_dist_lines_all.reference_1%TYPE,
53 reference_2 pa_cc_dist_lines_all.reference_2%TYPE,
54 reference_3 pa_cc_dist_lines_all.reference_3%TYPE,
55
56 /* Added for cross proj*/
57 tp_amt_type_code pa_cc_dist_lines_all.tp_amt_type_code%TYPE ,
58 project_tp_rate_type pa_cc_dist_lines_all.project_tp_rate_type%TYPE,
59 project_tp_rate_date pa_cc_dist_lines_all.project_tp_rate_date%TYPE,
60 project_tp_exchange_rate pa_cc_dist_lines_all.project_tp_exchange_rate%TYPE,
61 project_transfer_price pa_cc_dist_lines_all.project_transfer_price%TYPE,
62 projfunc_tp_rate_type pa_cc_dist_lines_all.projfunc_tp_rate_type%TYPE,
63 projfunc_tp_rate_date pa_cc_dist_lines_all.projfunc_tp_rate_date%TYPE,
64 projfunc_tp_exchange_rate pa_cc_dist_lines_all.projfunc_tp_exchange_rate%TYPE,
65 projfunc_transfer_price pa_cc_dist_lines_all.projfunc_transfer_price%TYPE,
66
67 project_tp_currency_code pa_cc_dist_lines_all.project_tp_currency_code%TYPE,
68 projfunc_tp_currency_code pa_cc_dist_lines_all.projfunc_tp_currency_code%TYPE
69 /* End for cross proj*/
70
71 );
72
73 -- Declare a type for table of above record type
74 TYPE CcdTabType IS TABLE OF CcdRecType
75 INDEX BY BINARY_INTEGER;
76
77 -- Declare a type for holding information about deleted
78 -- pa_cc_dist_lines. For deletion of records in the main schema, the
79 -- only thing required is the rowid. For the MRC schema, the line id
80 -- is required
81
82 TYPE DelRecType IS RECORD
83 (
84 CcdRowId rowid,
85 cc_dist_line_id pa_cc_dist_lines_all.cc_dist_line_id%TYPE
86 );
87
88 -- Declare a type for the table of delete record type
89 TYPE DelTabType IS TABLE OF DelRecType
90 INDEX BY BINARY_INTEGER;
91
92
93 -- Declare INSert, UPDate, DELete record types for pa_cc_dist_lines.
94 -- The table holding attributes of EIs to be updated also has the same
95 -- type as the ins/upd for pa_cc_dist_lines because most of the
96 -- attributes to be updated are the same
97
98 g_ins_rec CcdTabType;
99 g_upd_rec CcdTabType;
100 g_del_rec DelTabType;
101 g_ei_rec CcdTabType;
102
103 -- Keeps track of updates to global arrays
104 g_ucnt NUMBER; -- Update counter
105 g_icnt NUMBER; -- Insert counter
106 g_dcnt NUMBER; -- Delete counter
107
108 ----------------------------------------------------------------------------------
109 -- Start of comments
110 --
111 -- API Name : pa_cc_bl_process.pa_bl_pr
112 --
113 -- Type : Public
114 --
115 -- Pre-reqs : None
116 --
117 -- Function : This procedure is called from the Distribute Borrowed and Lent
118 -- Amounts process.
119 -- It accepts a set of Expenditure Items and its attributes from
120 -- the Pro*C program. These attributes include the accounting
121 -- for the items. It then calls the Transfer Price API to
122 -- determine the Transfer Price where required and then
123 -- creates distributions for these Items.
124 --
125 -- It also calls procedures to create records in the
126 -- reporting sets of books for the operating unit
127 --
128 -- Parameters : Please see specification below
129 --
130 -- Version : Initial version
131 --
132 -- End of comments
133 ----------------------------------------------------------------------------------
134
135 PROCEDURE pa_bl_pr
136 (
137 p_module_name IN VARCHAR2
138 ,p_debug_mode IN VARCHAR2
139 ,p_acct_currency_code IN OUT NOCOPY pa_expenditure_items_all.acct_currency_code%TYPE
140 ,p_acct_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
141 ,p_acct_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
142 ,p_acct_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
143 ,p_acct_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
144 ,p_adjusted_exp_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
148 ,p_compute_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
145 ,p_array_size IN Number
146 ,p_cc_markup_base_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp
147 ,p_cc_rejection_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
149 ,p_cr_code_combination_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
150 ,p_cross_charge_code IN PA_PLSQL_DATATYPES.Char1TabTyp
151 ,p_denom_burdened_cost_amount IN PA_PLSQL_DATATYPES.char30tabtyp
152 ,p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
153 ,p_denom_raw_cost_amount IN PA_PLSQL_DATATYPES.char30tabtyp
154 ,p_denom_tp_currency_code IN OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp
155 ,p_denom_transfer_price IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
156 ,p_dr_code_combination_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
157 ,p_exp_item_rowid IN PA_PLSQL_DATATYPES.char30tabtyp
158 ,p_expenditure_category IN PA_PLSQL_DATATYPES.char30tabtyp
159 ,p_expenditure_item_date IN PA_PLSQL_DATATYPES.char30tabtyp
160 ,p_expenditure_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
161 ,p_expenditure_type IN PA_PLSQL_DATATYPES.Char30TabTyp
162 ,p_expnd_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
163 ,p_incurred_by_person_id IN PA_PLSQL_DATATYPES.IDTabTyp
164 ,p_job_id IN PA_PLSQL_DATATYPES.IDTabTyp
165 ,p_labor_non_labor_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
166 ,p_login_id IN NUMBER
167 ,p_net_zero_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
168 ,p_nl_resource_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
169 ,p_non_labor_resource IN PA_PLSQL_DATATYPES.Char20TabTyp
170 ,p_pa_date IN PA_PLSQL_DATATYPES.char30tabtyp
171 ,p_gl_date IN PA_PLSQL_DATATYPES.char30tabtyp
172 ,p_pa_period_name IN PA_PLSQL_DATATYPES.char30tabtyp
173 ,p_gl_period_name IN PA_PLSQL_DATATYPES.char30tabtyp
174 ,p_primary_sob_id IN gl_sets_of_books.set_of_books_id%TYPE
175 ,p_processed_thru_date IN Date
176 ,p_program_application_id IN NUMBER
177 ,p_program_id IN NUMBER
178 ,p_project_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
179 ,p_project_id IN PA_PLSQL_DATATYPES.IDTabTyp
180 ,p_prvdr_org_id IN pa_implementations_all.org_id%TYPE
181 ,p_prvdr_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
182 ,p_quantity IN PA_PLSQL_DATATYPES.char30tabtyp
183 ,p_raw_revenue_amount IN PA_PLSQL_DATATYPES.char30tabtyp
184 ,p_recvr_org_id IN PA_PLSQL_DATATYPES.IDTabTyp
185 ,p_recvr_organization_id IN PA_PLSQL_DATATYPES.IDTabTyp
186 ,p_request_id IN NUMBER
187 ,p_revenue_distributed_flag IN PA_PLSQL_DATATYPES.Char1TabTyp
188 ,p_system_linkage_function IN PA_PLSQL_DATATYPES.Char30TabTyp
189 ,p_task_id IN PA_PLSQL_DATATYPES.IDTabTyp
190 ,p_tp_base_amount IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
191 ,p_tp_bill_markup_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
192 ,p_tp_bill_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
193 ,p_tp_fixed_date IN PA_PLSQL_DATATYPES.char30tabtyp
194 ,p_tp_ind_compiled_set_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
195 ,p_tp_job_id IN OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
196 ,p_tp_rule_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
197 ,p_tp_schedule_id IN PA_PLSQL_DATATYPES.IDTabTyp
198 ,p_tp_schedule_line_percentage IN OUT NOCOPY PA_PLSQL_DATATYPES.char30tabtyp
199 ,p_user_id IN NUMBER
200 /*Added for cross proj*/
201 ,p_tp_amt_type_code IN PA_PLSQL_DATATYPES.Char30TabTyp
202 ,p_assignment_id IN PA_PLSQL_DATATYPES.IDTabTyp
203 ,p_projfunc_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
204 ,p_project_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
205 ,p_project_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
206 ,p_project_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
207 ,p_projfunc_tp_rate_type IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
208 ,p_projfunc_tp_rate_date IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
209 ,p_projfunc_tp_exchange_rate IN OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp,
210
211 /* Bill rate Discount*/
212 p_dist_rule IN PA_PLSQL_DATATYPES.Char30TabTyp,
213 p_mcb_flag IN PA_PLSQL_DATATYPES.Char1TabTyp,
214 p_bill_rate_multiplier IN PA_PLSQL_DATATYPES.Char30TabTyp,
215 p_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
216 p_labor_schdl_discnt IN PA_PLSQL_DATATYPES.Char30TabTyp, /* bug#3221791 */
217 p_labor_schdl_fixed_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
218 p_bill_job_grp_id IN PA_PLSQL_DATATYPES.NumTabTyp,
219 p_labor_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
220 p_project_org_id IN PA_PLSQL_DATATYPES.NumTabTyp ,
221 p_project_type IN PA_PLSQL_DATATYPES.Char30TabTyp,
222 p_exp_func_curr_code IN PA_PLSQL_DATATYPES.Char30TabTyp,
223 p_incurred_by_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
224 p_raw_cost_rate IN PA_PLSQL_DATATYPES.Char30TabTyp,
225 p_override_to_organz_id IN PA_PLSQL_DATATYPES.NumTabTyp,
226 p_emp_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
227 p_job_bill_rate_schedule_id IN PA_PLSQL_DATATYPES.NumTabTyp,
228 p_exp_raw_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
229 p_assignment_precedes_task IN PA_PLSQL_DATATYPES.Char1TabTyp,
230
231 p_burden_cost IN PA_PLSQL_DATATYPES.Char30TabTyp,
232 p_task_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
233 p_proj_nl_bill_rate_org_id IN PA_PLSQL_DATATYPES.IdTabTyp,
234 p_task_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
235 p_proj_nl_std_bill_rate_sch IN PA_PLSQL_DATATYPES.Char30TabTyp,
236 p_nl_sch_type IN PA_PLSQL_DATATYPES.Char1TabTyp,
237 p_nl_task_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
238 p_nl_proj_sch_date IN PA_PLSQL_DATATYPES.Char30TabTyp,
239 p_nl_task_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
240 p_nl_proj_sch_discount IN PA_PLSQL_DATATYPES.NumTabTyp,
241 /* Added the last two parameters for Doosan Rate api changes */
242 p_task_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
243 p_proj_nl_std_bill_rate_sch_id IN PA_PLSQL_DATATYPES.NumTabTyp DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab,
244 /* Added for UOM enhancement */
245 p_uom_flag IN PA_PLSQL_DATATYPES.NumTabTyp
246 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
247 );
248
249 ----------------------------------------------------------------------------------
250 -- Start of comments
251 --
252 -- API Name : pa_cc_bl_process.mass_update
253 --
254 -- Type : Public
255 --
256 -- Pre-reqs : None
257 --
258 -- Function : This procedure is called from the Distribute Borrowed and Lent
259 -- Amounts package and the InterCompany Invoicing process.
260 -- The former calls it to update Borrowed and Lent lines in
261 -- the pa_cc_dist_lines_table while the latter for updating
262 -- provider reclassification entries. MRC for both processes
263 -- is performed through this API.
264 --
265 -- The API requires certain variables in the pa_cc_utils
266 -- package to be set. The records to be UPDATEd have
267 -- to be populated in the g_upd_rec table of records.
268 --
269 -- Parameters : Please see specification below
270 --
271 -- Version : Initial version
272 --
273 -- End of comments
274 ----------------------------------------------------------------------------------
275 PROCEDURE mass_update;
276
277 ----------------------------------------------------------------------------------
278 -- Start of comments
279 --
280 -- API Name : pa_cc_bl_process.mass_insert
281 --
282 -- Type : Public
283 --
284 -- Pre-reqs : None
285 --
286 -- Function : This procedure is called from the Distribute Borrowed and Lent
287 -- Amounts package and the InterCompany Invoicing process.
288 -- The former calls it to insert Borrowed and Lent lines in
289 -- the pa_cc_dist_lines_table while the latter for creating
290 -- provider reclassification entries. MRC for both processes
291 -- is performed through this API.
292 --
293 -- The API requires certain variables in the pa_cc_utils
294 -- package to be set. The records to be INSERTed have
295 -- to be populated in the g_ins_rec table of records.
296 --
297 -- Parameters : Please see specification below
298 --
299 -- Version : Initial version
300 --
301 -- End of comments
302 ----------------------------------------------------------------------------------
303 PROCEDURE mass_insert;
304
305 ----------------------------------------------------------------------------------
306 -- Start of comments
307 --
308 -- API Name : pa_cc_bl_process.mass_delete
309 --
310 -- Type : Public
311 --
312 -- Pre-reqs : None
313 --
314 -- Function : This procedure is called from the Distribute Borrowed and Lent
315 -- Amounts package and the InterCompany Invoicing process.
316 -- The former calls it to delete Borrowed and Lent lines in
317 -- the pa_cc_dist_lines_table while the latter for deleting
318 -- provider reclassification entries. MRC for both processes
319 -- is performed through this API (i.e. the corresponding MRC
320 -- records are deleted).
321 --
322 -- The API requires certain variables in the pa_cc_utils
323 -- package to be set. The records to be DELETEd have
324 -- to be populated in the g_del_rec table of records.
325 --
326 -- Parameters :
327 -- Please see specification below
328 --
329 -- Version : Initial version
330 --
331 -- End of comments
332 ----------------------------------------------------------------------------------
333 PROCEDURE mass_delete;
334 --
335
336 ----------------------------------------------------------------------------------
337 -- Start of comments
338 --
339 -- API Name : pa_cc_bl_process.initialization
340 --
341 -- Type : Public
342 --
343 -- Pre-reqs : None
344 --
345 -- Function : This procedure is called from the Distribute Borrowed and Lent
346 -- Amounts package and the InterCompany Invoicing process.
347 -- Both procedures call it prior to performing any functions.
348 -- The package initializes global variables used by other
349 -- procedures within this package. Another function of this
350 -- package is to determine the reporting sets of books for
351 -- the current operating unit.
352 --
353 -- Parameters : Please see specification below
354 --
355 -- Version : Initial version
356 --
357 -- End of comments
358 ----------------------------------------------------------------------------------
359 PROCEDURE initialization(
360 p_request_id IN NUMBER
361 ,p_program_application_id IN NUMBER
362 ,p_program_id IN NUMBER
363 ,p_user_id IN NUMBER
364 ,p_login_id IN NUMBER
365 ,p_prvdr_org_id IN NUMBER
366 ,p_primary_sob_id IN NUMBER
367 );
368
369 END PA_CC_BL_process;