1 package PA_FUNDS_CONTROL_PKG AUTHID CURRENT_USER as
2 -- $Header: PABCFCKS.pls 120.4 2012/02/20 12:03:18 mokukuma ship $
3
4 -- Declare Global Variables
5 g_debug_mode VARCHAR2(10):=NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); -- Modified for the bug 13702416
6 -- Moved from body to spec so that PABCPKTB.pls can access ..
7
8 /*---------------------------------------------------------------------------------------------------------- +
9 --This is the Main funds check function which calls all the other functions and procedures.
10 --This API is called from the following places
11 -- GL - Funds check process
12 -- CBC - Funds check process
13 -- Costing - During Expenditure Cost Distribution process
14 -- Transaction Import Process
15 -- Baseline of Budget
16 --
17 -- Parameters :
18 -- p_set_of_books_id : Set of Books ID in GL accounts for the packet to funds checked.
19 -- p_calling_module : Identifier of the module from which the funds checker will be invoked
20 -- The valid values are
21 -- GL - General ledger
22 -- CBC - Contract Conmmitment
23 -- CHECK_BASELINE - Budget Baselining
24 -- RESERVE_BASELINE - Budget Baselining
25 -- TRXNIMPORT - Transaction Import
26 -- DISTVIADJ - Invoice Adjustments
27 -- DISTERADJ - Expense Report Adjustments
28 -- INTERFACVI - Interface VI to payables
29 -- INTERFACER - Interface ER to payables
30 -- EXPENDITURE - For actuals entering through Projects
31 --
32 -- P_packet_id : Packet ID of the packet to be funds checked.
33 -- P_mode : Funds Checker Operation Mode.
34 -- C - Check funds
35 -- R - Reserve funds.
36 -- U - Un-reserve (only for REQ,PO and AP)
37 -- B - Called from budget baseline process (Processed like check funds)
38 -- S - Called from Budget submission (Processed like check funds)
39 -- P_partial_flag : Indicates the packet can be fundschecked/reserverd partially or not
40 -- Y - Partial
41 -- N - Full mode, default is N
42 -- P_reference1 If the p_mode is 'R',U,C,F' and p_calling_module = 'CBC'or 'EXP' then
43 -- this parameter holds the document type info Document Type
44 -- EXP - Expenditures originating from project
45 -- CC - Contract Commitments
46 -- Elsif p_mode is B, S and p_calling_module = 'BASELINE' then
47 -- this parameter holds the ext_bdgt_link_flag
48 -- End if;
49 -- * This param is not null for EXP , CC document type and Base line mode
50 -- P_reference2 If the p_mode is 'R',U,C,F' and p_calling_module = 'CBC' then
51 -- this parameter holds the document header info for Contract Commitment
52 -- document Header Id from Contract Commitments
53 -- IGC_CC_INTERFACE.CC_HEADER_ID
54 -- Elsif p_mode is B, S and p_calling_module = 'BASELINE' then
55 -- this parameter holds the project_id
56 -- End if;
57 -- * This param is not null for CC document type and Base line mode
58 -- P_reference3 If p_mode is B, S and p_calling_module = 'BASELINE' then
59 -- this parameter holds the budget_version_id
60 -- End if;
61 -- * This param is not null for Base line mode and Contract commitments
62 --
63 -- p_conc_flag : identifies when funds check is invoked from concurrent program.
64 -- The valid values are
65 -- 'N' default
66 -- 'Y' - concurrent programm
67 --
68 -- x_return_status : Fudscheck return status
69 -- Valid Status are
70 -- S - Success
71 -- F - Failure
72 -- T - Fatal
73 -- x_error_stage :Identifies the place where funds check process failed
74 --
75 -- x_error_messagee :defines the type of error : SQLerror||sqlcode
76 -------------------------------------------------------------------------------------------------------------+ */
77 FUNCTION pa_funds_check
78 (p_calling_module IN VARCHAR2
79 ,p_conc_flag IN VARCHAR2 DEFAULT 'N'
80 ,p_set_of_book_id IN NUMBER
81 ,p_packet_id IN NUMBER
82 ,p_mode IN VARCHAR2 DEFAULT 'C'
83 ,p_partial_flag IN VARCHAR2 DEFAULT 'N'
84 ,p_reference1 IN VARCHAR2 DEFAULT NULL
85 ,p_reference2 IN VARCHAR2 DEFAULT NULL
86 ,p_reference3 IN VARCHAR2 DEFAULT NULL
87 ,x_return_status OUT NOCOPY VARCHAR2
88 ,x_error_msg OUT NOCOPY VARCHAR2
89 ,x_error_stage OUT NOCOPY VARCHAR2
90 ) RETURN BOOLEAN ;
91
92 -- This is an overloaded api in turn calls the main fund check function
93 PROCEDURE pa_funds_check
94 (p_calling_module IN VARCHAR2
95 ,p_set_of_book_id IN NUMBER
96 ,p_packet_id IN NUMBER
97 ,p_mode IN VARCHAR2 DEFAULT 'C'
98 ,p_partial_flag IN VARCHAR2 DEFAULT 'N'
99 ,p_reference1 IN VARCHAR2 DEFAULT NULL
100 ,p_reference2 IN VARCHAR2 DEFAULT NULL
101 ,x_return_status OUT NOCOPY VARCHAR2
102 ,x_error_msg OUT NOCOPY VARCHAR2
103 ,x_error_stage OUT NOCOPY VARCHAR2
104 ) ;
105 /*---------------------------------------------------------------------------------------------------------+
106 -- This is the Tie back api which updates the status of pa_bc_packets table after confirming the funds checking
107 -- status of GL / Contract Commitments
108 --Parameters:
109 -- P_packet_id : Packet Identifier of the funds check process
110 -- P_mode :Funds Checker Operation Mode
111 -- R - Reserve Default
112 -- B - Base line
113 -- P_calling_module :This holds the info of budget type
114 -- GL --- Standard Default
115 -- CBC --- Contract Commitments
116 -- P_reference1 :This Param is not null in case of Contract Commitment
117 -- If P_ext_bdgt_type = CBC
118 -- This param holds the information of document type
119 -- P_reference2 = Igc_cc_interface.document_type
120 -- elsif p_mode = B then
121 -- P_reference1 = project_id
122 -- Else
123 -- P_reference1 = NULL;
124 -- End if;
125 -- P_reference2 :This Param is not null in case of Contract Commitment
126 -- If P_ext_bdgt_type = CBC
127 -- This param holds the information of document Header Id
128 -- P_reference2 = Igc_cc_interface.CC_HEADER_ID
129 -- elsif p_mode = B then
130 -- P_reference2 = budget_version_id
131 -- Else
132 -- P_reference2 = NULL;
133 -- End if;
134 -- p_partial_flag :Partial reservation flag
135 -- Y - partial mode
136 -- N - full Mode default
137 -- P_gl_cbc_return_code :The return status of the GL /CBC funds check process
138 ---------------------------------------------------------------------------------------------------------+ */
139
140 PROCEDURE PA_GL_CBC_CONFIRMATION
141 (p_calling_module IN VARCHAR2
142 ,p_packet_id IN NUMBER
143 ,p_mode IN VARCHAR2 DEFAULT 'C'
144 ,p_partial_flag IN VARCHAR2 DEFAULT 'N'
145 ,p_reference1 IN VARCHAR2 DEFAULT NULL -- doc type = 'CC'
146 ,p_reference2 IN VARCHAR2 DEFAULT NULL ---- CC_HEADER_ID
147 ,p_gl_cbc_return_code IN OUT NOCOPY VARCHAR2
148 ,x_return_status OUT NOCOPY VARCHAR2
149 ) ;
150
151 ---------------------------------------------------------------------------------------------------+
152 --This api is used to update the encumbrance balances and budget account balances after the
153 -- funds check process is complete and result of the funds check process is successful
154 -- this api will be called from Base line process during tie back process
155 ---------------------------------------------------------------------------------------------------+
156 PROCEDURE upd_bdgt_encum_bal(
157 p_packet_id IN NUMBER,
158 p_calling_module IN VARCHAR2,
159 p_mode IN VARCHAR2,
160 p_packet_status IN VARCHAR2,
161 x_return_status OUT NOCOPY VARCHAR2);
162
163 --------------------------------------------------------------------------------------------+
164 -- This api updates the status of bc packets based on the result code
165 -- and calling mode and partial flag
166 -- The valid status code values are
167 -- A - Approved
168 -- B - Base lined -- Intermediate status
169 -- R - Rejected
170 -- C - Checked -- Intermediate status
171 -- F - Failed Check
172 -- S - Passed Check
173 -- E - Error
174 -- T - Fatal
175 -- V - Vendor Invoice - Intermediate status to avoid sweeper to pick
176 -- L - Intermediate status for Expense report to liquidate but avoid sweeper to pick
177 -- if the calling module is BASELINE then use BULK FETCH AND BULK
178 -- update logic since the volume of records is more.
179 -----------------------------------------------------------------------------------------------+
180 PROCEDURE status_code_update (
181 p_calling_module IN VARCHAR2,
182 p_packet_id IN NUMBER,
183 p_mode IN VARCHAR2,
184 p_partial IN VARCHAR2 DEFAULT 'N',
185 p_packet_status IN VARCHAR2 DEFAULT 'S',
186 x_return_status OUT NOCOPY varchar2 );
187
188 -----------------------------------------------------------------------------------------------+
189 -- This procedure is the autonomous version of status_code_udpate. Basically, this procedure
190 -- will call status_code_update
191 -- main procedure status_code_update is being made non-autonomous
192 -----------------------------------------------------------------------------------------------+
193 PROCEDURE status_code_update_autonomous (
194 p_calling_module IN VARCHAR2,
195 p_packet_id IN NUMBER,
196 p_mode IN VARCHAR2,
197 p_partial IN VARCHAR2 DEFAULT 'N',
198 p_packet_status IN VARCHAR2 DEFAULT 'S',
199 x_return_status OUT NOCOPY varchar2 );
200
201 ----------------------------------------------------------------+
202 -- This api writes message to log file / buffer / dummy table
203 -- and initalizes the final out NOCOPY params with values
204 ----------------------------------------------------------------+
205 PROCEDURE log_message(
206 p_stage IN VARCHAR2 default null,
207 p_error_msg IN VARCHAR2 default null,
208 p_return_status IN varchar2 default null,
209 p_msg_token1 IN VARCHAR2 default null,
210 p_msg_token2 IN VARCHAR2 default null
211 ) ;
212
213 ------------------------------------------------------------------+
214 -- This api updates the result and status code in pa bc packets
215 -- whenever there is error while processing
216 -----------------------------------------------------------------+
217 PROCEDURE result_status_code_update
218 ( p_status_code IN VARCHAR2 default null
219 ,p_result_code IN VARCHAR2 default null
220 ,p_res_result_code IN VARCHAR2 default null
221 ,p_res_grp_result_code IN VARCHAR2 default null
222 ,p_task_result_code IN VARCHAR2 default null
223 ,p_top_task_result_code IN VARCHAR2 default null
224 ,p_project_result_code IN VARCHAR2 default null
225 ,p_proj_acct_result_code IN VARCHAR2 default null
226 ,p_bc_packet_id IN NUMBER default null
227 ,p_packet_id IN NUMBER ) ;
228
229 -------------------------------------------------------------------------+
230 -- This api checks whether the Invoice is coming after the
231 -- interface from projects if the invoice is already interfaced
232 -- from projects then donot derive burden components
233 -- if the invoice system_linkage function is 'VI' then
234 -- derive budget ccid, encum type id, etc and DONOT do funds check
235 -- mark the invoice as approved and donot create encum liqd
236 -- if the invoice system linkage func is 'ER' then
237 -- derive budget ccid, encum type id, etc and DONOT funds check
238 -- mark the invoice as approved and create encum liqd for raw only
239 -------------------------------------------------------------------------+
240 PROCEDURE is_ap_from_project
241 (p_packet_id IN NUMBER,
242 p_calling_module IN VARCHAR2,
243 x_return_status OUT NOCOPY VARCHAR2) ;
244
245 ----------------------------------------------------------------------------+
246 -- This api checks whether the project is of burden on same or different
247 -- expenditure items
248 ---------------------------------------------------------------------------+
249 FUNCTION check_bdn_on_sep_item(p_project_id In number) return varchar2;
250 PRAGMA RESTRICT_REFERENCES (check_bdn_on_sep_item, WNDS);
251
252 /* The following API is added to tie back the status code of the
253 * bc packets during the distribute vendor invoice adjustments
254 * This API will be called from PABCCSTB.pls package and pro*c
255 */
256 PROCEDURE tieback_pkt_status
257 (p_calling_module in varchar2
258 ,p_packet_id in number
259 ,p_partial_flag in varchar2 default 'N'
260 ,p_mode in varchar2 default 'R'
261 ,p_tieback_status in varchar2 default 'T' --'S' for Success, 'T' -- fatal Error
262 ,p_request_id in number
263 ,x_return_status OUT NOCOPY varchar2);
264
265 /* This api derives the resource list member id for the given packet id
266 * this api should be used only for project funds check records which are
267 * inserted into pa_bc_packets */
268 PROCEDURE DERIVE_RLMI
269 ( p_packet_id IN pa_bc_packets.packet_id%type,
270 p_mode IN varchar2,
271 p_sob IN NUMBER,
272 p_reference1 IN varchar2 default null,
273 p_reference2 IN varchar2 default null,
274 p_calling_module IN varchar2 default 'GL'
275 ) ;
276
277 /* This Api derives all the required funds check setup params required for projects funds check
278 * if the mode is ForcePass then the transactions will be marked as Pass
279 */
280 FUNCTION funds_check_setup
281 ( p_packet_id IN pa_bc_packets.packet_id%type,
282 p_mode IN varchar2,
283 p_sob IN NUMBER,
284 p_reference1 IN varchar2 default null,
285 p_reference2 IN varchar2 default null,
286 p_calling_module IN varchar2
287 ) return boolean ;
288
289
290 /* Function is_baseline_progress being made public as it will be accessed in
291 * pa_trx_import.tieback_fc_records - Bug 3981458
292 */
293
294 FUNCTION is_baseline_progress(p_project_id number)
295 return varchar2;
296
297 -- Bug 5354715 : This function is made public so that it can be used in other packages to check if the project is installed in this OU.
298 FUNCTION IS_PA_INSTALL_IN_OU RETURN VARCHAR2 ;
299
300 END PA_FUNDS_CONTROL_PKG;
[Home] [Help]