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