[Home] [Help]
PACKAGE BODY: APPS.PO_REQUISITION_LINES_PKG
Source
1 PACKAGE BODY PO_REQUISITION_LINES_PKG as
2 /* $Header: POXRIL1B.pls 120.6.12020000.2 2013/02/10 12:31:50 vegajula ship $ */
3
4
5 PROCEDURE Update_Row(X_Rowid VARCHAR2,
6 X_Requisition_Line_Id NUMBER,
7 X_Requisition_Header_Id NUMBER,
8 X_Line_Num NUMBER,
9 X_Line_Type_Id NUMBER,
10 X_Category_Id NUMBER,
11 X_Item_Description VARCHAR2,
12 X_Unit_Meas_Lookup_Code VARCHAR2,
13 X_Unit_Price NUMBER,
14 X_Base_Unit_Price NUMBER, -- <FPJ Advanced Price>
15 X_Quantity NUMBER,
16 X_Amount NUMBER, -- <SERVICES FPJ>
17 X_Deliver_To_Location_Id NUMBER,
18 X_To_Person_Id NUMBER,
19 X_Last_Update_Date DATE,
20 X_Last_Updated_By NUMBER,
21 X_Source_Type_Code VARCHAR2,
22 X_Last_Update_Login NUMBER,
23 X_Item_Id NUMBER,
24 X_Item_Revision VARCHAR2,
25 X_Quantity_Delivered NUMBER,
26 X_Suggested_Buyer_Id NUMBER,
27 X_Encumbered_Flag VARCHAR2,
28 X_Rfq_Required_Flag VARCHAR2,
29 X_Need_By_Date DATE,
30 X_Line_Location_Id NUMBER,
31 X_Modified_By_Agent_Flag VARCHAR2,
32 X_Parent_Req_Line_Id NUMBER,
33 X_Justification VARCHAR2,
34 X_Note_To_Agent VARCHAR2,
35 X_Note_To_Receiver VARCHAR2,
36 X_Purchasing_Agent_Id NUMBER,
37 X_Document_Type_Code VARCHAR2,
38 X_Blanket_Po_Header_Id NUMBER,
39 X_Blanket_Po_Line_Num NUMBER,
40 X_Currency_Code VARCHAR2,
41 X_Rate_Type VARCHAR2,
42 X_Rate_Date DATE,
43 X_Rate NUMBER,
44 X_Currency_Unit_Price NUMBER,
45 X_Currency_Amount NUMBER, -- <SERVICES FPJ>
46 X_Suggested_Vendor_Name VARCHAR2,
47 X_Suggested_Vendor_Location VARCHAR2,
48 X_Suggested_Vendor_Contact VARCHAR2,
49 X_Suggested_Vendor_Phone VARCHAR2,
50 X_Sugg_Vendor_Product_Code VARCHAR2,
51 X_Un_Number_Id NUMBER,
52 X_Hazard_Class_Id NUMBER,
53 X_Must_Use_Sugg_Vendor_Flag VARCHAR2,
54 X_Reference_Num VARCHAR2,
55 X_On_Rfq_Flag VARCHAR2,
56 X_Urgent_Flag VARCHAR2,
57 X_Cancel_Flag VARCHAR2,
58 X_Source_Organization_Id NUMBER,
59 X_Source_Subinventory VARCHAR2,
60 X_Destination_Type_Code VARCHAR2,
61 X_Destination_Organization_Id NUMBER,
62 X_Destination_Subinventory VARCHAR2,
63 X_Quantity_Cancelled NUMBER,
64 X_Cancel_Date DATE,
65 X_Cancel_Reason VARCHAR2,
66 X_Closed_Code VARCHAR2,
67 X_Agent_Return_Note VARCHAR2,
68 X_Changed_After_Research_Flag VARCHAR2,
69 X_Vendor_Id NUMBER,
70 X_Vendor_Site_Id NUMBER,
71 X_Vendor_Contact_Id NUMBER,
72 X_Research_Agent_Id NUMBER,
73 X_On_Line_Flag VARCHAR2,
74 X_Wip_Entity_Id NUMBER,
75 X_Wip_Line_Id NUMBER,
76 X_Wip_Repetitive_Schedule_Id NUMBER,
77 X_Wip_Operation_Seq_Num NUMBER,
78 X_Wip_Resource_Seq_Num NUMBER,
79 X_Attribute_Category VARCHAR2,
80 X_Destination_Context VARCHAR2,
81 X_Inventory_Source_Context VARCHAR2,
82 X_Vendor_Source_Context VARCHAR2,
83 X_Attribute1 VARCHAR2,
84 X_Attribute2 VARCHAR2,
85 X_Attribute3 VARCHAR2,
86 X_Attribute4 VARCHAR2,
87 X_Attribute5 VARCHAR2,
88 X_Attribute6 VARCHAR2,
89 X_Attribute7 VARCHAR2,
90 X_Attribute8 VARCHAR2,
91 X_Attribute9 VARCHAR2,
92 X_Attribute10 VARCHAR2,
93 X_Attribute11 VARCHAR2,
94 X_Attribute12 VARCHAR2,
95 X_Attribute13 VARCHAR2,
96 X_Attribute14 VARCHAR2,
97 X_Attribute15 VARCHAR2,
98 X_Bom_Resource_Id NUMBER,
99 X_Ussgl_Transaction_Code VARCHAR2,
100 X_Government_Context VARCHAR2,
101 X_Closed_Reason VARCHAR2,
102 X_Closed_Date DATE,
103 X_Transaction_Reason_Code VARCHAR2,
104 X_Quantity_Received NUMBER,
105 X_transferred_to_oe_flag OUT NOCOPY VARCHAR2,
106 X_update_dist_quantity VARCHAR2,
107 X_Tax_Code_Id NUMBER,
108 X_Tax_User_Override_Flag VARCHAR2,
109 --togeorge 10/03/2000
110 -- added oke columns
111 x_oke_contract_header_id NUMBER default null,
112 x_oke_contract_version_id NUMBER default null,
113 -- MC bug# 1548597.. Add 3 process related columns.unit_of_measure,quantity and grade.
114 -- start of 1548597
115 X_Secondary_Unit_Of_Measure VARCHAR2 default null,
116 X_Secondary_Quantity NUMBER default null,
117 X_Preferred_Grade VARCHAR2 default null
118 -- end of 1548597
119 ) IS
120
121 x_progress VARCHAR2(3) := NULL;
122 x_return_status VARCHAR2(1);
123 BEGIN
124
125 x_progress := '010';
126
127
128 UPDATE PO_REQUISITION_LINES
129 SET
130 requisition_line_id = X_Requisition_Line_Id,
131 requisition_header_id = X_Requisition_Header_Id,
132 line_num = X_Line_Num,
133 line_type_id = X_Line_Type_Id,
134 category_id = X_Category_Id,
135 item_description = X_Item_Description,
136 unit_meas_lookup_code = X_Unit_Meas_Lookup_Code,
137 unit_price = X_Unit_Price,
138 base_unit_price = X_Base_Unit_Price, -- <FPJ Advanced Price>
139 quantity = X_Quantity,
140 amount = X_Amount, -- <SERVICES FPJ>
141 deliver_to_location_id = X_Deliver_To_Location_Id,
142 to_person_id = X_To_Person_Id,
143 last_update_date = X_Last_Update_Date,
144 last_updated_by = X_Last_Updated_By,
145 source_type_code = X_Source_Type_Code,
146 last_update_login = X_Last_Update_Login,
147 item_id = X_Item_Id,
148 item_revision = X_Item_Revision,
149 quantity_delivered = X_Quantity_Delivered,
150 suggested_buyer_id = X_Suggested_Buyer_Id,
151 encumbered_flag = X_Encumbered_Flag,
152 rfq_required_flag = X_Rfq_Required_Flag,
153 need_by_date = X_Need_By_Date,
154 line_location_id = X_Line_Location_Id,
155 modified_by_agent_flag = X_Modified_By_Agent_Flag,
156 parent_req_line_id = X_Parent_Req_Line_Id,
157 justification = X_Justification,
158 note_to_agent = X_Note_To_Agent,
159 note_to_receiver = X_Note_To_Receiver,
160 purchasing_agent_id = X_Purchasing_Agent_Id,
161 document_type_code = X_Document_Type_Code,
162 blanket_po_header_id = X_Blanket_Po_Header_Id,
163 blanket_po_line_num = X_Blanket_Po_Line_Num,
164 currency_code = X_Currency_Code,
165 rate_type = X_Rate_Type,
166 rate_date = X_Rate_Date,
167 rate = X_Rate,
168 currency_unit_price = X_Currency_Unit_Price,
169 currency_amount = X_Currency_Amount, -- <SERVICES FPJ>
170 suggested_vendor_name = X_Suggested_Vendor_Name,
171 suggested_vendor_location = X_Suggested_Vendor_Location,
172 suggested_vendor_contact = X_Suggested_Vendor_Contact,
173 suggested_vendor_phone = X_Suggested_Vendor_Phone,
174 suggested_vendor_product_code = X_Sugg_Vendor_Product_Code,
175 un_number_id = X_Un_Number_Id,
176 hazard_class_id = X_Hazard_Class_Id,
177 must_use_sugg_vendor_flag = X_Must_Use_Sugg_Vendor_Flag,
178 reference_num = X_Reference_Num,
179 on_rfq_flag = X_On_Rfq_Flag,
180 urgent_flag = X_Urgent_Flag,
181 cancel_flag = X_Cancel_Flag,
182 source_organization_id = X_Source_Organization_Id,
183 source_subinventory = X_Source_Subinventory,
184 destination_type_code = X_Destination_Type_Code,
185 destination_organization_id = X_Destination_Organization_Id,
186 destination_subinventory = X_Destination_Subinventory,
187 quantity_cancelled = X_Quantity_Cancelled,
188 cancel_date = X_Cancel_Date,
189 cancel_reason = X_Cancel_Reason,
190 closed_code = X_Closed_Code,
191 agent_return_note = X_Agent_Return_Note,
192 changed_after_research_flag = X_Changed_After_Research_Flag,
193 vendor_id = X_Vendor_Id,
194 vendor_site_id = X_Vendor_Site_Id,
195 vendor_contact_id = X_Vendor_Contact_Id,
196 research_agent_id = X_Research_Agent_Id,
197 on_line_flag = X_On_Line_Flag,
198 wip_entity_id = X_Wip_Entity_Id,
199 wip_line_id = X_Wip_Line_Id,
200 wip_repetitive_schedule_id = X_Wip_Repetitive_Schedule_Id,
201 wip_operation_seq_num = X_Wip_Operation_Seq_Num,
202 wip_resource_seq_num = X_Wip_Resource_Seq_Num,
203 attribute_category = X_Attribute_Category,
204 destination_context = X_Destination_Context,
205 inventory_source_context = X_Inventory_Source_Context,
206 vendor_source_context = X_Vendor_Source_Context,
207 attribute1 = X_Attribute1,
208 attribute2 = X_Attribute2,
209 attribute3 = X_Attribute3,
210 attribute4 = X_Attribute4,
211 attribute5 = X_Attribute5,
212 attribute6 = X_Attribute6,
213 attribute7 = X_Attribute7,
214 attribute8 = X_Attribute8,
215 attribute9 = X_Attribute9,
216 attribute10 = X_Attribute10,
217 attribute11 = X_Attribute11,
218 attribute12 = X_Attribute12,
219 attribute13 = X_Attribute13,
220 attribute14 = X_Attribute14,
221 attribute15 = X_Attribute15,
222 bom_resource_id = X_Bom_Resource_Id,
223 government_context = X_Government_Context,
224 closed_reason = X_Closed_Reason,
225 closed_date = X_Closed_Date,
226 transaction_reason_code = X_Transaction_Reason_Code,
227 quantity_received = X_Quantity_Received,
228 --togeorge 10/03/2000
229 -- added oke columns
230 oke_contract_header_id = x_oke_contract_header_id,
231 oke_contract_version_id = x_oke_contract_version_id,
232 -- start of 1548597
233 secondary_unit_of_measure = X_Secondary_Unit_Of_Measure,
234 secondary_quantity = X_Secondary_Quantity,
235 preferred_grade = X_Preferred_Grade,
236 -- end of 1548597
237 tax_attribute_update_code = NVL(tax_attribute_update_code, 'UPDATE') --<eTax Integration R12>
238 WHERE rowid = X_rowid;
239
240 if (SQL%NOTFOUND) then
241 Raise NO_DATA_FOUND;
242 end if;
243
244
245 x_progress := '020';
246 --dbms_output.put_line ('Before call to update_transferred...');
247
248 po_req_lines_sv.update_transferred_to_oe_flag (X_requisition_header_id,
249 X_transferred_to_oe_flag);
250
251 x_progress := '030';
252
253 --Fix bug14350660, comment out this code, move below call to the
254 --calling procedure (POXRILNS.pld)po_req_lns_th1.update_row
255
256 /*if (x_update_dist_quantity = 'Y') then
257
258 po_req_dist_sv1.update_dist_quantity (x_requisition_line_id,
259 x_quantity);
260 end if;*/
261
262 --bug 14350660 end
263
264 x_progress := '040';
265
266 -- begin <REQINPOOL>
267 -- bug 4931033 - incorrect parameters were passed
268 -- pass in x_requisition_header_id as well as it is available
269 po_req_lines_sv.update_reqs_in_pool_flag(x_req_line_id => x_requisition_line_id,
270 x_req_header_id => x_requisition_header_id,
271 x_return_status => x_return_status);
272
273 -- end <REQINPOOL>
274
275 x_progress := '050';
276
277 EXCEPTION
278 WHEN OTHERS then
279 po_message_s.sql_error('UPDATE_ROW',x_progress,sqlcode);
280 END Update_Row;
281
282
283
284 PROCEDURE check_unique (X_rowid VARCHAR2,
285 X_line_num VARCHAR2,
286 X_req_header_id NUMBER) IS
287
288 x_progress VARCHAR2(3) := NULL;
289 dummy NUMBER;
290
291 BEGIN
292
293 x_progress := '010';
294
295 SELECT 1
296 INTO dummy
297 FROM DUAL
298 WHERE not exists (SELECT 1
299 FROM po_requisition_lines
300 WHERE line_num = X_line_num
301 AND requisition_header_id = X_req_header_id
302 AND ((x_rowid is null) or (rowid <> x_rowid))
303 );
304
305 EXCEPTION
306 WHEN NO_DATA_FOUND THEN
307 po_message_s.app_error('PO_RQ_LINE_NUM_ALREADY_EXISTS');
308
309 END check_unique;
310
311
312
313 END PO_REQUISITION_LINES_PKG;