1 package PA_FUNDS_CONTROL_UTILS AUTHID CURRENT_USER as
2 -- $Header: PAFCUTLS.pls 120.17.12020000.3 2013/03/06 09:33:09 admarath ship $
3
4 --Global variables declared to use as one level cache
5 -- this improves the performance significantly
6 g_project_id pa_bc_packets.project_id%type := null;
7 g_project_id1 pa_bc_packets.project_id%type := null;
8 g_project_id2 pa_bc_packets.project_id%type := null;
9 g_project_id3 pa_bc_packets.project_id%type := null;
10 g_bdgt_version_id pa_bc_packets.budget_version_id%type := null;
11 g_calling_mode VARCHAR2(25) := null;
12 g_calling_mode1 VARCHAR2(25) := null;
13 g_calling_mode2 VARCHAR2(25) := null;
14 g_calling_mode3 VARCHAR2(25) := null;
15 g_task_id pa_bc_packets.task_id%type := null;
16 g_exp_type pa_bc_packets.expenditure_type%type := null;
17 g_exp_item_date DATE := null;
18 g_exp_org_id pa_bc_packets.expenditure_organization_id%type := null;
19 g_budget_ccid pa_bc_packets.budget_ccid%type := null;
20 g_start_date DATE := null;
21 g_end_date DATE := null;
22 g_rlmi pa_bc_packets.resource_list_member_id%type := null;
23 g_period_name pa_bc_packets.period_name%type := null;
24 g_compiled_set_id NUMBER := null;
25 g_multiplier NUMBER := null;
26 g_fnd_reqd_flag VARCHAR2(25) := null;
27 g_encum_type_id NUMBER := null;
28 g_ext_bdgt_link VARCHAR2(25) := null;
29 g_sch_rev_id NUMBER := null;
30
31 -- Bug 5373272
32 g_event_type_code xla_events.event_type_code%TYPE := NULL ;
33 g_document_distribution_id pa_bc_packets.document_distribution_id%TYPE := NULL ;
34 g_document_distribution_type pa_bc_packets.document_distribution_type%TYPE := NULL ;
35 g_parent_distribution_id pa_bc_packets.document_distribution_id%TYPE := NULL ;
36
37 PROCEDURE init_util_variables;
38 -- this API gets the compiled multipliers for the given task and expenditure organization id
39 -- and expenditure item date for burden calculations
40 FUNCTION get_fc_compiled_multiplier
41 ( p_exp_org_id IN NUMBER,
42 p_task_id IN VARCHAR2,
43 P_exp_item_date IN date,
44 p_sch_type IN varchar2 default 'C',
45 p_exp_type IN varchar2
46 ) return NUMBER ;
47
48 -- This api gets the compiled set id for the given task,expenditure organization id
49 -- and expenditure item date for burden calculations
50 FUNCTION get_fc_compiled_set_id
51 ( p_task_id IN NUMBER
52 ,p_ei_date IN DATE
53 ,p_org_id IN NUMBER
54 ,p_sch_type IN VARCHAR2 DEFAULT 'C'
55 ,p_calling_mode IN VARCHAR2 DEFAULT 'COMPILE_SET_ID'
56 ,p_exp_type IN VARCHAR2 /** added for Burdening changes PAL */
57 ) return NUMBER ;
58
59 -- This API gets the burden cost for the given expenditure item id and cdl line number
60 -- from the cost distribution lines all
61 FUNCTION get_fc_proj_burdn_cost
62 (p_exp_item_id IN NUMBER
63 ,p_line_num IN NUMBER
64 )return NUMBER ;
65
66 -- This is a PLSQL Record for holding the information of the open and closed periods
67 TYPE rec_closed_period IS RECORD(period_name gl_period_statuses.period_name%type,
68 start_date date,
69 end_date date,
70 closing_status VARCHAR2(1)
71 );
72 -- This is a PLSQL Table of type PLSQL Record
73 TYPE tab_closed_period IS TABLE OF rec_closed_period index by binary_integer;
74
75
76 -- This Api gets the open and closed periods start date, end date, period name and status
77 -- for the given start date ( Amount type) and end date ( boundary code) and sob
78 -- the out parameter will be in form of PLSQL table and also it returns the no of rows in
79 -- plsql table
80 PROCEDURE get_gl_periods
81 (p_start_date IN date
82 ,p_end_date IN date
83 ,p_set_of_books_id IN gl_period_statuses.set_of_books_id%type
84 ,x_tab_count IN OUT NOCOPY Number
85 ,x_tab_pds IN OUT NOCOPY pa_funds_control_utils.tab_closed_period
86 ,x_return_status IN OUT NOCOPY varchar2
87 );
88
89 -----------------------------------------------------------------------------------
90 --This API is a wrapper for the get_budgt_ctrl_options This api provides differenct
91 --options for the given project_id and calling mode
92 -----------------------------------------------------------------------------------
93 -- This api checks whether the funds check required for the given
94 -- project the return value is 'Y' or 'N'
95 FUNCTION get_fnd_reqd_flag
96 (p_project_id IN NUMBER
97 ,p_calling_mode IN VARCHAR2 -- STD / CBC
98 ) return varchar2 ;
99
100 -- This api gives the budget version id for the given project id
101 FUNCTION get_bdgt_version_id
102 (p_project_id IN NUMBER
103 ,p_calling_mode IN VARCHAR2 -- STD / CBC
104 ) return PA_BUDGET_VERSIONS.budget_version_id%TYPE ;
105
106 -- this api gets the encumbrance type id for the given project id
107
108 FUNCTION get_encum_type_id
109 (p_project_id IN NUMBER
110 ,p_calling_mode IN VARCHAR2 -- STD / CBC
111 ) return NUMBER ;
112
113 -- this api checks the budget is linked with GL or not the valid
114 -- return values are 'Y' or 'N'
115
116 FUNCTION get_bdgt_link
117 (p_project_id IN NUMBER
118 ,p_calling_mode IN VARCHAR2 -- STD / CBC
119 ) return varchar2 ;
120
121 --The following API returns the Budget CCID for a given project, task,
122 --resource list member id, budget version id and start_date.
123 PROCEDURE Get_Budget_CCID (
124 p_project_id in number,
125 p_task_id in number,
126 p_top_task_id in number,
127 p_res_list_mem_id in number,
128 p_start_date in date,
129 p_budget_version_id in number,
130 p_entry_level_code in varchar2,
131 x_budget_ccid out NOCOPY number,
132 x_budget_line_id out NOCOPY number,
133 x_return_status out NOCOPY varchar2,
134 x_error_message_code out NOCOPY varchar2);
135
136 --The following API returns the Time Phased Type Code for a budget_version_id.
137 PROCEDURE Get_Time_Phased_Type_Code(
138 p_budget_version_id in number,
139 x_time_phased_type_code out NOCOPY varchar2,
140 x_return_status out NOCOPY varchar2,
141 x_error_message_code out NOCOPY varchar2);
142
143 --The following API gets the current baselined budget version id for the project id.
144 PROCEDURE Get_Baselined_Budget_Version(
145 p_calling_mode in varchar2,
146 p_project_id in number,
147 x_base_version_id out NOCOPY number,
148 x_res_list_id out NOCOPY number,
149 x_entry_level_code out NOCOPY varchar2,
150 x_return_status out NOCOPY varchar2,
151 x_error_message_code out NOCOPY varchar2);
152
153 --The following API returns the available balance for the budget_version, budget_CCID and start date
154 --from PA_BUDGET_ACCT_LINES
155 FUNCTION Get_Acct_Line_Balance(
156 p_budget_version_id in number,
157 p_start_date in date,
158 p_end_date in date,
159 p_budget_ccid in number) RETURN NUMBER;
160
161 --The following API returns true if the budget has been baselined before else false if it is the
162 --first time we are baselining.
163 FUNCTION Is_Budget_Baselined_Before(p_project_id in number) RETURN VARCHAR2;
164
165 --This function submits the sweeper process a concurrent request.
166 --This function is called at the end of the Tieback_BC_Entities procedure from
167 --the budget form during baselining.
168 FUNCTION RunSweeper RETURN NUMBER;
169
170 PROCEDURE print_message(p_msg in varchar2);
171
172
173 -- #This function has been created in base release 12 for SLA - FC integration project
174 -- #Function "Is_account_change_allowed" is the API that will be called from
175 -- #budgets form and from funds check tieback processing. This function will
176 -- #check if there exists any transaction, against any budget line, whose
177 -- #account has been modified. It will return 'N' if there exists transaction
178 -- #against a budget line, else it will return 'Y'.
179
180 FUNCTION Is_account_change_allowed (P_budget_version_id IN Number,
181 P_resource_assignment_id IN Number,
182 P_period_name IN Varchar2,
183 P_budget_entry_level_code IN Varchar2 default null)
184 return Varchar2;
185
186 -- ## Another variation of is_account_change_allowed
187 -- ## This is called from pa_budget_account_pkg and pa_funds_control_pkg
188
189 FUNCTION Is_Account_change_allowed2
190 (p_budget_version_id IN Number,
191 p_project_id IN Number,
192 p_top_task_id IN Number,
193 p_task_id IN Number,
194 p_parent_resource_id IN Number,
195 p_resource_list_member_id IN Number,
196 p_start_date IN Date,
197 p_period_name IN Varchar2,
198 p_entry_level_code IN Varchar2,
199 p_mode IN Varchar2)
200 return Varchar2;
201
202 -- #R12 Funds management enhancement
203 -- #API name : get_sla_notupgraded_flag
204 -- #Type : private
205 -- #Description : Returns Y/N depending on whether the distribution and associated
206 -- # budget passed as input are notupgraded
207
208 FUNCTION get_sla_notupgraded_flag ( p_application_id IN NUMBER,
209 p_entity_code IN VARCHAR2,
210 p_document_header_id IN NUMBER,
211 p_document_distribution_id IN NUMBER,
212 p_dist_link_type IN VARCHAR2,
213 p_budget_version_id IN NUMBER,
214 p_budget_line_id IN NUMBER ) RETURN VARCHAR2;
215
216
217 -- #R12 Funds management enhancement
218 -- #API name : Update_bvid_blid_on_cdl_bccom
219 -- #Type : private
220 -- #Description : Stamps latest budget version id and budget_line_id on
221 -- 1. CDL when called from baselining process
222 -- 2. CDL and bc commitments when called from yearend rollover process
223
224 PROCEDURE Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id IN NUMBER,
225 p_calling_mode IN VARCHAR2);
226
227 -- #Bug 5191768
228 -- #API name : Get_cost_rejection_reason
229 -- #Type : private
230 -- #Description : Returns PA/GMS lookup meaning for the failure lookup code
231
232 FUNCTION Get_cost_rejection_reason ( p_Lookup_code IN VARCHAR2,
233 p_sponsored_flag IN VARCHAR2)
234 return VARCHAR2;
235
236 -- #R12 Funds management enhancement
237 -- #API name : get_ap_acct_reversal_attr
238 -- #Type : private
239 -- #Description : Returns parent distribution id if its a AP cancel scenario and
240 -- SLA accounting reversal logic will be fired if this api returns NOT NULL
241
242 FUNCTION get_ap_acct_reversal_attr ( p_event_type_code IN VARCHAR2,
243 p_document_distribution_id IN NUMBER ,
244 p_document_distribution_type IN VARCHAR2 ) RETURN NUMBER;
245
246 -----------------------------------------------------------------------------------
247 -- #R12 Funds management enhancement
248 -- #API name : get_ap_sla_reversed_status
249 -- #Type : private
250 -- #Description : Returns 'Y' if AP is cancelled and the SLA lines associated with
251 -- AP has been reversed .Business flow cannot be used in this scenario.
252 -------------------------------------------------------------------------------------
253 FUNCTION get_ap_sla_reversed_status (p_invoice_id IN NUMBER,
254 p_invoice_distribution_id IN NUMBER ) RETURN VARCHAR2;
255
256 -----------------------------------------------------------------------------------
257 -- R12 Funds Management Uptake
258 -- Procedure to derive credit/debit side of the amount for PO and REQ distributions
259 -------------------------------------------------------------------------------------
260 FUNCTION DERIVE_PO_REQ_AMT_SIDE (p_event_type_code IN VARCHAR2,
261 p_main_or_backing_doc IN VARCHAR2,
262 p_distribution_type IN VARCHAR2 ) RETURN NUMBER;
263
264 -- Bug 5206341 : Function to check if there exists any closed periods in current budget version
265 FUNCTION CLOSED_PERIODS_EXISTS_IN_BUDG (p_budget_version_id IN NUMBER) RETURN VARCHAR2;
266
267 -- This Api returns 'Y' if project has funds check enbaled.
268 FUNCTION is_funds_check_enabled(p_proj_id IN NUMBER )return VARCHAR2;
269
270 --Bug 14765326
271 FUNCTION get_corrective_action(RESULT_CODE IN VARCHAR,p_number IN NUMBER,p_date IN DATE) RETURN VARCHAR2;
272
273 END PA_FUNDS_CONTROL_UTILS ;
[Home] [Help]