1 PACKAGE POR_UTIL_PKG as
2 /* $Header: PORUTILS.pls 120.7 2007/12/26 14:41:39 pilamuru noship $ */
3
4 FUNCTION bool_to_varchar(b IN BOOLEAN) RETURN VARCHAR2;
5
6 PROCEDURE delete_requisition(p_header_id IN NUMBER);
7
8 PROCEDURE purge_requisition(p_header_id IN NUMBER);
9
10 PROCEDURE delete_working_copy_req(p_req_number IN VARCHAR2);
11
12 FUNCTION get_current_approver(p_req_header_id in number) RETURN NUMBER;
13
14 FUNCTION getSitesEnabledFlagForContract(p_header_id in number) RETURN varchar2;
15
16 FUNCTION get_cost_center(p_code_combination_id in number) RETURN VARCHAR2;
17
18 FUNCTION get_document_number(table_name_p IN VARCHAR2) RETURN NUMBER;
19
20 FUNCTION get_global_document_number(table_name_p IN VARCHAR2, org_id_p IN NUMBER)
21 RETURN NUMBER;
22
23 FUNCTION get_item_cost(
24 x_item_id IN NUMBER,
25 x_organization_id IN NUMBER,
26 x_unit_of_measure IN VARCHAR2) RETURN NUMBER;
27
28 FUNCTION interface_start_workflow(
29 V_charge_success IN OUT NOCOPY VARCHAR2,
30 V_budget_success IN OUT NOCOPY VARCHAR2,
31 V_accrual_success IN OUT NOCOPY VARCHAR2,
32 V_variance_success IN OUT NOCOPY VARCHAR2,
33 x_code_combination_id IN OUT NOCOPY NUMBER,
34 x_budget_account_id IN OUT NOCOPY NUMBER,
35 x_accrual_account_id IN OUT NOCOPY NUMBER,
36 x_variance_account_id IN OUT NOCOPY NUMBER,
37 x_charge_account_flex IN OUT NOCOPY VARCHAR2,
38 x_budget_account_flex IN OUT NOCOPY VARCHAR2,
39 x_accrual_account_flex IN OUT NOCOPY VARCHAR2,
40 x_variance_account_flex IN OUT NOCOPY VARCHAR2,
41 x_charge_account_desc IN OUT NOCOPY VARCHAR2,
42 x_budget_account_desc IN OUT NOCOPY VARCHAR2,
43 x_accrual_account_desc IN OUT NOCOPY VARCHAR2,
44 x_variance_account_desc IN OUT NOCOPY VARCHAR2,
45 x_coa_id NUMBER,
46 x_bom_resource_id NUMBER,
47 x_bom_cost_element_id NUMBER,
48 x_category_id NUMBER,
49 x_destination_type_code VARCHAR2,
50 x_deliver_to_location_id NUMBER,
51 x_destination_organization_id NUMBER,
52 x_destination_subinventory VARCHAR2,
53 x_expenditure_type VARCHAR2,
54 x_expenditure_organization_id NUMBER,
55 x_expenditure_item_date DATE,
56 x_item_id NUMBER,
57 x_line_type_id NUMBER,
58 x_result_billable_flag VARCHAR2,
59 x_preparer_id NUMBER,
60 x_project_id NUMBER,
61 x_document_type_code VARCHAR2,
62 x_blanket_po_header_id NUMBER,
63 x_source_type_code VARCHAR2,
64 x_source_organization_id NUMBER,
65 x_source_subinventory VARCHAR2,
66 x_task_id NUMBER,
67 x_award_set_id NUMBER,
68 x_deliver_to_person_id NUMBER,
69 x_type_lookup_code VARCHAR2,
70 x_suggested_vendor_id NUMBER,
71 x_suggested_vendor_site_id NUMBER,
72 x_wip_entity_id NUMBER,
73 x_wip_entity_type VARCHAR2,
74 x_wip_line_id NUMBER,
75 x_wip_repetitive_schedule_id NUMBER,
76 x_wip_operation_seq_num NUMBER,
77 x_wip_resource_seq_num NUMBER,
78 x_po_encumberance_flag VARCHAR2,
79 x_gl_encumbered_date DATE,
80 wf_itemkey IN OUT NOCOPY VARCHAR2,
81 V_new_combination IN OUT NOCOPY VARCHAR2,
82 header_att1 VARCHAR2,
83 header_att2 VARCHAR2,
84 header_att3 VARCHAR2,
85 header_att4 VARCHAR2,
86 header_att5 VARCHAR2,
87 header_att6 VARCHAR2,
88 header_att7 VARCHAR2,
89 header_att8 VARCHAR2,
90 header_att9 VARCHAR2,
91 header_att10 VARCHAR2,
92 header_att11 VARCHAR2,
93 header_att12 VARCHAR2,
94 header_att13 VARCHAR2,
95 header_att14 VARCHAR2,
96 header_att15 VARCHAR2,
97 line_att1 VARCHAR2,
98 line_att2 VARCHAR2,
99 line_att3 VARCHAR2,
100 line_att4 VARCHAR2,
101 line_att5 VARCHAR2,
102 line_att6 VARCHAR2,
103 line_att7 VARCHAR2,
104 line_att8 VARCHAR2,
105 line_att9 VARCHAR2,
106 line_att10 VARCHAR2,
107 line_att11 VARCHAR2,
108 line_att12 VARCHAR2,
109 line_att13 VARCHAR2,
110 line_att14 VARCHAR2,
111 line_att15 VARCHAR2,
112 distribution_att1 VARCHAR2,
113 distribution_att2 VARCHAR2,
114 distribution_att3 VARCHAR2,
115 distribution_att4 VARCHAR2,
116 distribution_att5 VARCHAR2,
117 distribution_att6 VARCHAR2,
118 distribution_att7 VARCHAR2,
119 distribution_att8 VARCHAR2,
120 distribution_att9 VARCHAR2,
121 distribution_att10 VARCHAR2,
122 distribution_att11 VARCHAR2,
123 distribution_att12 VARCHAR2,
124 distribution_att13 VARCHAR2,
125 distribution_att14 VARCHAR2,
126 distribution_att15 VARCHAR2,
127 FB_ERROR_MSG IN OUT NOCOPY VARCHAR2,
128 p_unit_price NUMBER,
129 p_blanket_po_line_num NUMBER) RETURN VARCHAR2;
130
131 FUNCTION jumpIntoFunction(p_application_id in number,
132 p_function_code in varchar2,
133 p_parameter1 in varchar2 default null,
134 p_parameter2 in varchar2 default null,
135 p_parameter3 in varchar2 default null,
136 p_parameter4 in varchar2 default null,
137 p_parameter5 in varchar2 default null,
138 p_parameter6 in varchar2 default null,
139 p_parameter7 in varchar2 default null,
140 p_parameter8 in varchar2 default null,
141 p_parameter9 in varchar2 default null,
142 p_parameter10 in varchar2 default null,
143 p_parameter11 in varchar2 default null)
144 return varchar2;
145
146 PROCEDURE restore_working_copy_req(
147 p_origHeaderId IN NUMBER,
148 p_tempHeaderId IN NUMBER,
149 p_origLineIds IN PO_TBL_NUMBER,
150 p_tempLineIds IN PO_TBL_NUMBER,
151 p_origDistIds IN PO_TBL_NUMBER,
152 p_tempDistIds IN PO_TBL_NUMBER,
153 p_origReqSupplierIds IN PO_TBL_NUMBER,
154 p_tempReqSupplierIds IN PO_TBL_NUMBER,
155 p_origPriceDiffIds IN PO_TBL_NUMBER,
156 p_tempPriceDiffIds IN PO_TBL_NUMBER);
157
158 FUNCTION submitreq(
159 req_Header_Id IN NUMBER,
160 req_num IN varchar2,
161 preparer_id IN NUMBER,
162 note_to_approver IN varchar2,
163 approver_id IN NUMBER) RETURN VARCHAR2;
164
165 PROCEDURE validate_pjm_project_info (p_deliver_to_org_id IN NUMBER,
166 p_project_id IN NUMBER,
167 p_task_id IN NUMBER,
168 p_need_by_date IN DATE,
169 p_translated_err OUT NOCOPY VARCHAR2,
170 p_result OUT NOCOPY VARCHAR2);
171
172 FUNCTION validate_ccid(
173 X_chartOfAccountsId IN NUMBER,
174 X_ccId IN NUMBER,
175 X_validationDate IN DATE,
176 X_concatSegs OUT NOCOPY VARCHAR2,
177 X_errorMsg OUT NOCOPY VARCHAR2) RETURN NUMBER;
178
179 FUNCTION validate_open_period(
180 x_trx_date IN DATE,
181 x_sob_id IN NUMBER,
182 x_org_id IN NUMBER) RETURN NUMBER;
183
184 FUNCTION validate_segs(
185 X_chartOfAccountsId IN NUMBER,
186 X_concatSegs IN VARCHAR2,
187 X_errorMsg OUT NOCOPY VARCHAR2) RETURN NUMBER;
188
189 FUNCTION val_rcv_controls_for_date(
190 X_transaction_type IN VARCHAR2,
191 X_auto_transact_code IN VARCHAR2,
192 X_expected_receipt_date IN DATE,
193 X_transaction_date IN DATE,
194 X_routing_header_id IN NUMBER,
195 X_po_line_location_id IN NUMBER,
196 X_item_id IN NUMBER,
197 X_vendor_id IN NUMBER,
198 X_to_organization_id IN NUMBER,
199 rcv_date_exception OUT NOCOPY VARCHAR2) RETURN NUMBER;
200
201 -- API to check whether the user is associated with a employee or not.
202 -- Returns true if the user is associated with a employee, else returns false
203 FUNCTION validate_user(p_user_id in number) RETURN CHAR;
204
205 PROCEDURE withdraw_req(p_headerId in NUMBER);
206
207 PROCEDURE deactivate_active_req(p_user_id IN NUMBER);
208
209 -- API to check transaction flow for centralized procurement
210 -- checks whether a transaction flow exists between the start OU and end OU
211 -- wrapper needed since types are defined in INV package and not in the
212 -- database
213 PROCEDURE check_transaction_flow(
214 p_api_version IN NUMBER,
215 p_init_msg_list IN VARCHAR2,
216 p_start_operating_unit IN NUMBER,
217 p_end_operating_unit IN NUMBER,
218 p_flow_type IN NUMBER,
219 p_organization_id IN NUMBER,
220 p_category_id IN NUMBER,
221 p_transaction_date IN DATE,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY VARCHAR2,
224 x_msg_data OUT NOCOPY VARCHAR2,
225 x_header_id OUT NOCOPY NUMBER,
226 x_new_accounting_flag OUT NOCOPY VARCHAR2,
227 x_transaction_flow_exists OUT NOCOPY VARCHAR2);
228
229 --Begin Encumbrance APIs
230 ------------------------
231
232 -- API to truncate the PO interface table PO_ENCUMBRANCE_GT
233 PROCEDURE truncate_po_encumbrance_gt;
234
235 -- API to populate the distribution data into POs interface table
236 -- PO_ENCUMBRANCE_GT
237 PROCEDURE populate_po_encumbrance_gt(
238 p_dist_data IN ICX_ENC_IN_TYPE);
239
240 -- API to check if the funds can be reserved on the requisition
241 PROCEDURE check_reserve(
242 p_api_version IN VARCHAR2,
243 p_commit IN VARCHAR2 default FND_API.G_FALSE,
244 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
245 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
246 x_return_status OUT NOCOPY VARCHAR2,
247 p_doc_type IN VARCHAR2,
248 p_doc_subtype IN VARCHAR2,
249 p_dist_data IN ICX_ENC_IN_TYPE,
250 p_doc_level IN VARCHAR2,
251 p_doc_level_id IN NUMBER,
252 p_use_enc_gt_flag IN VARCHAR2,
253 p_override_funds IN VARCHAR2,
254 p_report_successes IN VARCHAR2,
255 x_po_return_code OUT NOCOPY VARCHAR2,
256 x_detailed_results OUT NOCOPY po_fcout_type);
257
258 -- API to check if the funds can be adjusted on the requisition
259 -- called during approver checkout
260 -- also called for just the labor and expense lines from assign contractor
261 -- during approver checkout
262 PROCEDURE check_adjust(
263 p_api_version IN VARCHAR2,
264 p_commit IN VARCHAR2 default FND_API.G_FALSE,
265 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
266 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
267 x_return_status OUT NOCOPY VARCHAR2,
268 p_doc_type IN VARCHAR2,
269 p_doc_subtype IN VARCHAR2,
270 p_dist_data IN ICX_ENC_IN_TYPE,
271 p_doc_level IN VARCHAR2,
272 p_doc_level_id_tbl IN po_tbl_number,
273 p_override_funds IN VARCHAR2,
274 p_use_gl_date IN VARCHAR2,
275 p_override_date IN DATE,
276 p_report_successes IN VARCHAR2,
277 x_po_return_code OUT NOCOPY VARCHAR2,
278 x_detailed_results OUT NOCOPY po_fcout_type);
279
280 -- API to perform reservation of funds on a contractor line
281 -- this can have just a labor line or both a labor and expense line
282 -- called from assign contractor
283 PROCEDURE do_reserve_contractor(
284 p_api_version IN VARCHAR2,
285 p_commit IN VARCHAR2 default FND_API.G_FALSE,
286 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
287 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
288 x_return_status OUT NOCOPY VARCHAR2,
289 p_doc_type IN VARCHAR2,
290 p_doc_subtype IN VARCHAR2,
291 p_doc_level IN VARCHAR2,
292 p_doc_level_id_tbl IN po_tbl_number,
293 p_prevent_partial_flag IN VARCHAR2,
294 p_employee_id IN NUMBER,
295 p_override_funds IN VARCHAR2,
296 p_report_successes IN VARCHAR2,
297 x_po_return_code OUT NOCOPY VARCHAR2,
298 x_detailed_results OUT NOCOPY po_fcout_type);
299
300 -- API to perform unreserve of funds on a contractor line
301 -- this can have just a labor line or both a labor and expense line
302 -- called from assign contractor
303 PROCEDURE do_unreserve_contractor(
304 p_api_version IN VARCHAR2,
305 p_commit IN VARCHAR2 default FND_API.G_FALSE,
306 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
307 p_validation_level IN number default FND_API.G_VALID_LEVEL_FULL,
308 x_return_status OUT NOCOPY VARCHAR2,
309 p_doc_type IN VARCHAR2,
310 p_doc_subtype IN VARCHAR2,
311 p_doc_level IN VARCHAR2,
315 p_use_gl_date IN VARCHAR2,
312 p_doc_level_id_tbl IN po_tbl_number,
313 p_override_funds IN VARCHAR2,
314 p_employee_id IN NUMBER,
316 p_override_date IN DATE,
317 p_report_successes IN VARCHAR2,
318 x_po_return_code OUT NOCOPY VARCHAR2,
319 x_detailed_results OUT NOCOPY po_fcout_type);
320
321 --End Encumbrance APIs
322 ------------------------
323
324 PROCEDURE cancel_workflow(p_headerId in NUMBER);
325
326 -- API Name : create_info_template
327 -- Type : Public
328 -- Pre-reqs : None
329 -- Function : Copies the information template data from the old_req_line to the record
330 -- corresponding new_req_line in the table POR_TEMPLATE_INFO while
331 -- creating a new req line. This will be called by Core Purchasing
332 -- This API is provide to Core Purchasing for bug 4716686
333 -- Parameters : p_old_reqline_id IN NUMBER : Corresponds to the existing requisition line id
334 -- p_new_reqline_id IN NUMBER : Corresponds to the new requisition line id
335 -- p_item_id IN NUMBER : Corresponds to the item id of the new requisiton line
336 -- p_category_id IN NUMBER : Corresponds to the category id of the new req. line
337 -- Version : Initial Verion : 1.0
338
339 PROCEDURE create_info_template
340 (p_api_version IN NUMBER,
341 x_return_status OUT NOCOPY VARCHAR2,
342 p_commit IN VARCHAR2 default FND_API.G_FALSE,
343 p_old_reqline_id IN NUMBER,
344 p_new_reqline_id IN NUMBER,
345 p_item_id IN NUMBER,
346 p_category_id IN NUMBER) ;
347
348 -- API Name : update_attachment_to_standard
349 -- Type : Public
350 -- Pre-reqs : None
351 -- Function : Updates the attachments associated with the requisition to standard attachment
352 -- Parameters : p_req_header_id IN NUMBER : Corresponds to the existing requisition line id
353
354 PROCEDURE update_attachment_to_standard(p_req_header_id in NUMBER);
355
356 END POR_UTIL_PKG;