1 PACKAGE po_negotiations_sv1 AS
2 /* $Header: POXNEG1S.pls 120.7 2007/12/19 14:14:11 vdurbhak 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_line_type_id_tbl IN PO_TBL_NUMBER,
32 p_item_id_tbl IN PO_TBL_NUMBER,
33 p_item_revision_tbl IN PO_TBL_VARCHAR5, -- <ACHTML R12>
34 p_category_id_tbl IN PO_TBL_NUMBER,
35 p_quantity_tbl IN PO_TBL_NUMBER,
36 p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30,-- <ACHTML R12>
37 p_job_id_tbl IN PO_TBL_NUMBER,
38 -- some more input params
39 p_neg_outcome IN varchar2,
40 p_document_org_id IN number,
41 p_neg_style_id IN NUMBER, -- <ACHTML R12>
42 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
43 -- output params
44 x_negotiation_id IN OUT NOCOPY number,
45 x_doc_url_params IN OUT NOCOPY varchar2
46 );
47 -- <HTMLAC END>
48
49 /*============================================================================
50 Name: CREATE_NEGOTIATION
51 DESC: Create document from requisition data in the autocreate
52 input parameters :
53 x_negotiation_type : type of the negotiation to be autocreated
54 x_grouping_method : grouping method selected in the autocreate form
55 t_req_lines : plsql table containing the requisition details
56 output parameters :
57 x_negotiation_number : negotiation number returned by the sourcing api
58 x_doc_url_params : params to be passed to the funcion call to open
59 negotiation page url, returned by the sourcing api
60 x_error_code, x_error_message : errors if any returned by the api
61 p_neg_outcome: negotiation outcome expected
62
63 <RENEG BLANKET FPI>
64 Added p_neg_outcome input parameter which tells Sourcing what is the
65 outcome of negotiation.
66 ==============================================================================*/
67
68 PROCEDURE create_negotiation(x_negotiation_type IN varchar2 ,
69 x_grouping_method IN varchar2 ,
70 t_req_lines IN PO_TBL_NUMBER, /* Changed the po_tbl_number to upper case for uniformity - bug 6631173 */ --bug5841426
71 p_neg_style_id IN NUMBER, -- <ACHTML R12>
72 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
73 x_negotiation_id IN OUT NOCOPY number,
74 x_doc_url_params IN OUT NOCOPY varchar2,
75 x_result IN OUT NOCOPY number,
76 x_error_code IN OUT NOCOPY varchar2,
77 x_error_message IN OUT NOCOPY varchar2,
78 --<RENEG BLANKET FPI>
79 p_neg_outcome IN varchar2,
80 --<HTMLAC>
81 p_document_org_id IN number DEFAULT null);
82
83 /*============================================================================
84 Name: DELETE_NEGOTIATION_REF
85 DESC: Delete negotiation reference from the backing requisition
86 input parameters :
87 x_negotiation_id : negotiation whose reference has to be deleted
88 x_negotiation_line_num : negotiation line whose reference has to be deleted
89 output parameters :
90 x_error_code : errors if any returned by the api
91 ==============================================================================*/
92
93 PROCEDURE DELETE_NEGOTIATION_REF (x_negotiation_id in number,
94 x_negotiation_line_num in number,
95 x_error_code out NOCOPY varchar2) ;
96
97
98
99 /*============================================================================
100 Name: UPDATE_NEGOTIATION_REF
101 DESC: Update negotiation reference in the backing requisition
102 input parameters :
103 x_old_negotiation_id : negotiation whose reference has to be replaced
104 x_new_negotiation_num/id : new negotiation reference
105 output parameters :
106 x_error_code : errors if any returned by the api
107 ==============================================================================*/
108
109 PROCEDURE UPDATE_NEGOTIATION_REF (x_old_negotiation_id in number ,
110 x_new_negotiation_id in number ,
111 x_new_negotiation_num in varchar2 ,
112 x_error_code out NOCOPY varchar2);
113
114 --<Bug 2440254 mbhargav START>
115 /*============================================================================
116 Name: UPDATE_NEGOTIATION_LINE_REF
117 DESC: Update negotiation reference in the backing requisition line to
118 point to another negotiation line.
119 ==============================================================================*/
120 PROCEDURE UPDATE_NEGOTIATION_LINE_REF (
121 p_api_version IN NUMBER,
122 p_old_negotiation_id IN NUMBER,
123 p_old_negotiation_line_num IN NUMBER,
124 p_new_negotiation_id IN NUMBER,
125 p_new_negotiation_line_num IN NUMBER,
126 p_new_negotiation_num IN varchar2,
127 x_return_status OUT NOCOPY varchar2,
128 x_error_message OUT NOCOPY varchar2);
129
130 --<Bug 2440254 mbhargav END>
131
132 /*============================================================================
133 Name: UPDATE_NEGOTIATION_REF REQ_POOL
134 DESC: Update requisition pool flag in the backing requisition
135 input parameters :
136 x_negotiation_id : req line with this negotiation has to be updated
137 with the req pool
138 x_negotiation_line_num : req line with this negotiation line has to be
139 updated with the req pool
140 x_flag_value : req pool flag value
141 output parameters :
142 x_error_code : errors if any returned by the api
143 ==============================================================================*/
144
145 PROCEDURE UPDATE_REQ_POOL (x_negotiation_id in number,
146 x_negotiation_line_num in number,
147 x_flag_value in varchar2,
148 x_error_code out NOCOPY varchar2);
149
150 /*============================================================================
151 Name: check_negotiation_ref
152 DESC: checks if a req line/header has negotiation reference
153 input params:
154 x_doc_level - the doc level req line or header
155 x_doc_id - req line id or header id
156 output params:
157 x_negotiation_ref_flag - 'Y' or 'N'
158 ==============================================================================*/
159
160 PROCEDURE check_negotiation_ref(x_doc_level IN VARCHAR2,
161 x_doc_id IN NUMBER,
162 x_negotiation_ref_flag IN OUT NOCOPY varchar2);
163
164
165
166 --<RENEG BLANKET FPI START>
167 /*============================================================================
168 Name : RENEGOTIATE_BLANKET
169 Type : Private
170 Function : This procedure
171 a. populates the Sourcing Interface tables
172 b. Calls Sourcing APIs for creating draft_negotiation and purging interface tables
173 Version : Current Version 1.0
174 Changed: Initial design 10/1/2002
175 Previous Version 1.0
176 ==============================================================================*/
177 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
178 p_commit IN varchar2,
179 p_po_header_id IN NUMBER,
180 p_negotiation_type IN varchar2,
181 x_negotiation_id OUT NOCOPY NUMBER,
182 x_doc_url_params OUT NOCOPY varchar2,
183 x_return_status OUT NOCOPY varchar2,
184 x_error_code OUT NOCOPY varchar2,
185 x_error_message OUT NOCOPY varchar2,
186 x_large_negotiation OUT NOCOPY varchar2,
187 x_large_neg_request_id OUT NOCOPY NUMBER);
188 --<RENEG BLANKET FPI END>
189
190 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
191 p_commit IN varchar2,
192 p_po_header_id IN NUMBER,
193 p_negotiation_type IN varchar2,
194 x_negotiation_id OUT NOCOPY NUMBER,
195 x_doc_url_params OUT NOCOPY varchar2,
196 x_return_status OUT NOCOPY varchar2,
197 x_error_code OUT NOCOPY varchar2,
198 x_error_message OUT NOCOPY varchar2
199 );
200 --<RENEG BLANKET FPI END>
201 -- Bug 3780359
202 PROCEDURE get_auction_display_line_num(
203 p_auction_header_id IN NUMBER,
204 p_auction_line_number IN NUMBER,
205 x_auction_display_line_num OUT NOCOPY VARCHAR2);
206
207 END po_negotiations_sv1;