1 PACKAGE po_negotiations_sv1 AUTHID CURRENT_USER AS
2 /* $Header: POXNEG1S.pls 120.17 2012/02/14 05:33:33 kkolukul ship $*/
3
4 /*============================================================================
5 PLSQL table defined to pass the requisition information form the autocreate
6 frontend to the backend sourcing wrapper
7 ==============================================================================*/
8
9 /* Bug 6631173 The below declaration was introduced in the bug 5841426,
10 the fix was working in Forms but not in the OA poages as the OA was not able to identify the
11 construct declared in a pls.
12 Using the standard db table type PO_TBL_NUMBER will fix the issue.
13 Commented the local object po_tble
14 TYPE po_tbl_number IS TABLE OF NUMBER; */ --bug5841426
15
16 /*TYPE req_lines_table_type IS TABLE OF po_requisition_lines%rowtype
17 index by binary_integer;*/ --Bug5841426
18
19
20 PROCEDURE create_negotiation_bulk
21 (
22 -- standard API params
23 p_api_version IN NUMBER,
24 x_result IN OUT NOCOPY NUMBER,
25 x_error_message IN OUT NOCOPY VARCHAR2,
26 -- input params
27 p_negotiation_type IN varchar2,
28 p_grouping_method IN varchar2,
29 -- table params in
30 p_req_line_id_tbl IN PO_TBL_NUMBER,
31 p_neg_line_id_tbl IN PO_TBL_NUMBER,
32 p_neg_line_num_disp_tbl IN PO_TBL_VARCHAR100,
33 /*p_line_type_id_tbl IN PO_TBL_NUMBER,
34 p_item_id_tbl IN PO_TBL_NUMBER,
35 p_item_revision_tbl IN PO_TBL_VARCHAR5, -- <ACHTML R12>
36 p_category_id_tbl IN PO_TBL_NUMBER,
37 p_quantity_tbl IN PO_TBL_NUMBER,
38 p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30,-- <ACHTML R12>
39 p_job_id_tbl IN PO_TBL_NUMBER,*/
40
41 -- some more input params
42 p_neg_outcome IN varchar2,
43 p_document_org_id IN number,
44 p_neg_style_id IN NUMBER, -- <ACHTML R12>
45 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
46 -- output params
47
48 --umbrella program
49 p_fair_opp_notice_flag IN VARCHAR2 DEFAULT NULL,
50
51 x_negotiation_id IN OUT NOCOPY number,
52 x_doc_url_params IN OUT NOCOPY varchar2
53 );
54 -- <HTMLAC END>
55
56 -- OverLoading the above method for CLIN_SLIN Changes project. The above method can be removed after all the dependencies have been removed.
57
58 PROCEDURE create_negotiation_bulk
59 (
60 -- standard API params
61 p_api_version IN NUMBER,
62 x_result IN OUT NOCOPY NUMBER,
63 x_error_message IN OUT NOCOPY VARCHAR2,
64 -- input params
65 p_negotiation_type IN varchar2,
66 p_grouping_method IN varchar2,
67 -- table params in
68 p_req_line_id_tbl IN PO_TBL_NUMBER,
69 p_neg_line_id_tbl IN PO_TBL_NUMBER,
70 p_neg_line_num_disp_tbl IN PO_TBL_VARCHAR100,
71 /* p_line_type_id_tbl IN PO_TBL_NUMBER,
72 p_item_id_tbl IN PO_TBL_NUMBER,
73 p_item_revision_tbl IN PO_TBL_VARCHAR5, -- <ACHTML R12>
74 p_category_id_tbl IN PO_TBL_NUMBER,
75 p_quantity_tbl IN PO_TBL_NUMBER,
76 p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30,-- <ACHTML R12>
77 p_job_id_tbl IN PO_TBL_NUMBER,
78 p_line_num_display IN PO_TBL_VARCHAR30,
79 p_group_line_id IN PO_TBL_NUMBER,
80 p_clm_info_flag IN PO_TBL_VARCHAR5,
81 p_clm_option_indicator IN PO_TBL_VARCHAR5,
82 p_clm_option_num IN PO_TBL_NUMBER,
83 p_clm_option_from_date IN PO_TBL_DATE,
84 p_clm_option_to_date IN PO_TBL_DATE,
85 p_clm_funded_flag IN PO_TBL_VARCHAR5,
86 p_clm_base_line_num IN PO_TBL_NUMBER,*/
87
88 -- some more input params
89 p_neg_outcome IN varchar2,
90 p_document_org_id IN number,
91 p_neg_style_id IN NUMBER, -- <ACHTML R12>
92 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
93 -- output params
94 x_negotiation_id IN OUT NOCOPY number,
95 x_doc_url_params IN OUT NOCOPY varchar2
96 );
97
98
99
100
101
102
103
104 /*============================================================================
105 Name: CREATE_NEGOTIATION
106 DESC: Create document from requisition data in the autocreate
107 input parameters :
108 x_negotiation_type : type of the negotiation to be autocreated
109 x_grouping_method : grouping method selected in the autocreate form
110 t_req_lines : plsql table containing the requisition details
111 output parameters :
112 x_negotiation_number : negotiation number returned by the sourcing api
113 x_doc_url_params : params to be passed to the funcion call to open
114 negotiation page url, returned by the sourcing api
115 x_error_code, x_error_message : errors if any returned by the api
116 p_neg_outcome: negotiation outcome expected
117
118 <RENEG BLANKET FPI>
119 Added p_neg_outcome input parameter which tells Sourcing what is the
120 outcome of negotiation.
121 ==============================================================================*/
122
123 PROCEDURE create_negotiation(x_negotiation_type IN varchar2 ,
124 x_grouping_method IN varchar2 ,
125 t_req_lines IN PO_TBL_NUMBER, /* Changed the po_tbl_number to upper case for uniformity - bug 6631173 */ --bug5841426
126 p_neg_style_id IN NUMBER, -- <ACHTML R12>
127 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
128 x_negotiation_id IN OUT NOCOPY number,
129 x_doc_url_params IN OUT NOCOPY varchar2,
130 x_result IN OUT NOCOPY number,
131 x_error_code IN OUT NOCOPY varchar2,
132 x_error_message IN OUT NOCOPY varchar2,
133 --<RENEG BLANKET FPI>
134 p_neg_outcome IN varchar2,
135 --<HTMLAC>
136 p_document_org_id IN number DEFAULT null,
137 --umbrella program
138 p_fair_opp_notice_flag IN VARCHAR2 DEFAULT NULL
139 , t_neg_lines IN PO_TBL_NUMBER DEFAULT NULL
140 , t_neg_line_num IN PO_TBL_VARCHAR100 DEFAULT NULL
141 );
142
143 /*============================================================================
144 Name: DELETE_NEGOTIATION_REF
145 DESC: Delete negotiation reference from the backing requisition
146 input parameters :
147 x_negotiation_id : negotiation whose reference has to be deleted
148 x_negotiation_line_num : negotiation line whose reference has to be deleted
149 output parameters :
150 x_error_code : errors if any returned by the api
151 ==============================================================================*/
152
153 PROCEDURE DELETE_NEGOTIATION_REF (x_negotiation_id in number,
154 x_negotiation_line_num in number,
155 x_error_code out NOCOPY varchar2) ;
156
157
158
159 /*============================================================================
160 Name: UPDATE_NEGOTIATION_REF
161 DESC: Update negotiation reference in the backing requisition
162 input parameters :
163 x_old_negotiation_id : negotiation whose reference has to be replaced
164 x_new_negotiation_num/id : new negotiation reference
165 output parameters :
166 x_error_code : errors if any returned by the api
167 ==============================================================================*/
168
169 PROCEDURE UPDATE_NEGOTIATION_REF (x_old_negotiation_id in number ,
170 x_new_negotiation_id in number ,
171 x_new_negotiation_num in varchar2 ,
172 x_error_code out NOCOPY varchar2);
173
174 --<Bug 2440254 mbhargav START>
175 /*============================================================================
176 Name: UPDATE_NEGOTIATION_LINE_REF
177 DESC: Update negotiation reference in the backing requisition line to
178 point to another negotiation line.
179 ==============================================================================*/
180 PROCEDURE UPDATE_NEGOTIATION_LINE_REF (
181 p_api_version IN NUMBER,
182 p_old_negotiation_id IN NUMBER,
183 p_old_negotiation_line_num IN NUMBER,
184 p_new_negotiation_id IN NUMBER,
185 p_new_negotiation_line_num IN NUMBER,
186 p_new_negotiation_num IN varchar2,
187 x_return_status OUT NOCOPY varchar2,
188 x_error_message OUT NOCOPY varchar2);
189
190 --<Bug 2440254 mbhargav END>
191
192 /*============================================================================
193 Name: UPDATE_NEGOTIATION_REF REQ_POOL
194 DESC: Update requisition pool flag in the backing requisition
195 input parameters :
196 x_negotiation_id : req line with this negotiation has to be updated
197 with the req pool
198 x_negotiation_line_num : req line with this negotiation line has to be
199 updated with the req pool
200 x_flag_value : req pool flag value
201 output parameters :
202 x_error_code : errors if any returned by the api
203 ==============================================================================*/
204
205 PROCEDURE UPDATE_REQ_POOL (x_negotiation_id in number,
206 x_negotiation_line_num in number,
207 x_flag_value in varchar2,
208 x_error_code out NOCOPY varchar2);
209
210 /*============================================================================
211 Name: check_negotiation_ref
212 DESC: checks if a req line/header has negotiation reference
213 input params:
214 x_doc_level - the doc level req line or header
215 x_doc_id - req line id or header id
216 output params:
217 x_negotiation_ref_flag - 'Y' or 'N'
218 ==============================================================================*/
219
220 PROCEDURE check_negotiation_ref(x_doc_level IN VARCHAR2,
221 x_doc_id IN NUMBER,
222 x_negotiation_ref_flag IN OUT NOCOPY varchar2);
223
224
225
226 --<RENEG BLANKET FPI START>
227 /*============================================================================
228 Name : RENEGOTIATE_BLANKET
229 Type : Private
230 Function : This procedure
231 a. populates the Sourcing Interface tables
232 b. Calls Sourcing APIs for creating draft_negotiation and purging interface tables
233 Version : Current Version 1.0
234 Changed: Initial design 10/1/2002
235 Previous Version 1.0
236 ==============================================================================*/
237 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
238 p_commit IN varchar2,
239 p_po_header_id IN NUMBER,
240 p_negotiation_type IN varchar2,
241 x_negotiation_id OUT NOCOPY NUMBER,
242 x_doc_url_params OUT NOCOPY varchar2,
243 x_return_status OUT NOCOPY varchar2,
244 x_error_code OUT NOCOPY varchar2,
245 x_error_message OUT NOCOPY varchar2,
246 x_large_negotiation OUT NOCOPY varchar2,
247 x_large_neg_request_id OUT NOCOPY NUMBER);
248 --<RENEG BLANKET FPI END>
249
250 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
251 p_commit IN varchar2,
252 p_po_header_id IN NUMBER,
253 p_negotiation_type IN varchar2,
254 x_negotiation_id OUT NOCOPY NUMBER,
255 x_doc_url_params OUT NOCOPY varchar2,
256 x_return_status OUT NOCOPY varchar2,
257 x_error_code OUT NOCOPY varchar2,
258 x_error_message OUT NOCOPY varchar2
259 );
260 --<RENEG BLANKET FPI END>
261 -- Bug 3780359
262 PROCEDURE get_auction_display_line_num(
263 p_auction_header_id IN NUMBER,
264 p_auction_line_number IN NUMBER,
268 PROCEDURE add_line_to_negotiation(p_auction_header_id IN Number,
265 x_auction_display_line_num OUT NOCOPY VARCHAR2);
266
267
269 p_req_line_id IN NUMBER,
270 x_result IN OUT NOCOPY number,
271 x_error_code IN OUT NOCOPY varchar2,
272 x_error_message IN OUT NOCOPY varchar2);
273
274 /* Bug 9881440 - Start */
275 /* Added the procedure COPY_REQ_UDA_TO_SOL to copy UDA data from
276 Requisition to Solicitation. This API copies the following UDA
277 data from Requisition to Solicitation.
278 Requesting Office => PR -> SOL
279 Suggested COTR Office => PR -> SOL
280 Suggested Property Administration Office => PR -> SOL
281 Issuing Office => User Preferences / PR -> SOL
282 Business Priority and Project Information => PR -> SOL
283 */
284 PROCEDURE COPY_REQ_UDA_TO_SOL(p_req_lines IN PO_TBL_NUMBER,
285 p_auction_header_id IN NUMBER);
286 /* Bug 9881440 - End */
287
288 --the procedure to remove the requisitions from the req pool
289 PROCEDURE REMOVE_FROM_REQ_POOL(p_line_type IN VARCHAR2,
290 p_auction_header_id IN NUMBER,
291 p_requisition_line_id IN PO_TBL_NUMBER,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_error_msg OUT NOCOPY VARCHAR2);
294
295 g_log_module_name varchar2(30) := 'po_negotiations_sv1';
296
297 TYPE number_table IS TABLE OF po_requisition_lines_all.requisition_line_id%TYPE index by binary_integer;
298
299 PROCEDURE return_reqs_to_pool(p_req_line_ids IN number_table,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_error_msg OUT NOCOPY VARCHAR2,
302 x_error_code OUT NOCOPY VARCHAR2);
303
304 PROCEDURE update_sol_ref_delete_all(p_auction_header_id IN NUMBER,
305 p_delete_pbr_yn IN VARCHAR2 DEFAULT 'Y',
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_error_msg OUT NOCOPY VARCHAR2,
308 x_error_code OUT NOCOPY VARCHAR2);
312 p_bid_number IN NUMBER DEFAULT NULL,
309
310 PROCEDURE update_sol_ref_delete_line(p_auction_header_id IN NUMBER,
311 p_auction_line_number IN NUMBER,
313 p_bid_line_number IN NUMBER DEFAULT NULL,
314 p_requisition_line_id IN NUMBER DEFAULT NULL,
315 p_table_name IN VARCHAR2 DEFAULT 'PON_BACKING_REQUISITIONS',
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_error_msg OUT NOCOPY VARCHAR2,
318 x_error_code OUT NOCOPY VARCHAR2);
319
320 PROCEDURE update_sol_ref_amend(p_old_auction_id IN NUMBER,
321 p_new_auction_id IN NUMBER,
322 x_return_status OUT NOCOPY VARCHAR2,
323 x_error_msg OUT NOCOPY VARCHAR2,
324 x_error_code OUT NOCOPY VARCHAR2);
325
326
327 END po_negotiations_sv1;
[Home] [Help]