[Home] [Help]
PACKAGE BODY: APPS.PO_REQUISITION_LINES_PKG2
Source
1 PACKAGE BODY PO_REQUISITION_LINES_PKG2 as
2 /* $Header: POXRIL3B.pls 120.3.12010000.2 2008/09/22 18:13:38 rohbansa ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Requisition_Line_Id IN OUT NOCOPY NUMBER,
6 X_Requisition_Header_Id NUMBER,
7 X_Line_Num NUMBER,
8 X_Line_Type_Id NUMBER,
9 X_Category_Id NUMBER,
10 X_Item_Description VARCHAR2,
11 X_Unit_Meas_Lookup_Code VARCHAR2,
12 X_Unit_Price NUMBER,
13 X_Base_Unit_Price NUMBER, -- <FPJ Advanced Price>
14 X_Quantity NUMBER,
15 X_Amount NUMBER, -- <SERVICES FPJ>
16 X_Deliver_To_Location_Id NUMBER,
17 X_To_Person_Id NUMBER,
18 X_Last_Update_Date DATE,
19 X_Last_Updated_By NUMBER,
20 X_Source_Type_Code VARCHAR2,
21 X_Last_Update_Login NUMBER,
22 X_Creation_Date DATE,
23 X_Created_By NUMBER,
24 X_Item_Id NUMBER,
25 X_Item_Revision VARCHAR2,
26 X_Quantity_Delivered NUMBER,
27 X_Suggested_Buyer_Id NUMBER,
28 X_Encumbered_Flag VARCHAR2,
29 X_Rfq_Required_Flag VARCHAR2,
30 X_Need_By_Date DATE,
31 X_Line_Location_Id NUMBER,
32 X_Modified_By_Agent_Flag VARCHAR2,
33 X_Parent_Req_Line_Id NUMBER,
34 X_Justification VARCHAR2,
35 X_Note_To_Agent VARCHAR2,
36 X_Note_To_Receiver VARCHAR2,
37 X_Purchasing_Agent_Id NUMBER,
38 X_Document_Type_Code VARCHAR2,
39 X_Blanket_Po_Header_Id NUMBER,
40 X_Blanket_Po_Line_Num NUMBER,
41 X_Currency_Code VARCHAR2,
42 X_Rate_Type VARCHAR2,
43 X_Rate_Date DATE,
44 X_Rate NUMBER,
45 X_Currency_Unit_Price NUMBER,
46 X_Currency_Amount NUMBER, -- <SERVICES FPJ>
47 X_Suggested_Vendor_Name VARCHAR2,
48 X_Suggested_Vendor_Location VARCHAR2,
49 X_Suggested_Vendor_Contact VARCHAR2,
50 X_Suggested_Vendor_Phone VARCHAR2,
51 X_Sugg_Vendor_Product_Code VARCHAR2,
52 X_Un_Number_Id NUMBER,
53 X_Hazard_Class_Id NUMBER,
54 X_Must_Use_Sugg_Vendor_Flag VARCHAR2,
55 X_Reference_Num VARCHAR2,
56 X_On_Rfq_Flag VARCHAR2,
57 X_Urgent_Flag VARCHAR2,
58 X_Cancel_Flag VARCHAR2,
59 X_Source_Organization_Id NUMBER,
60 X_Source_Subinventory VARCHAR2,
61 X_Destination_Type_Code VARCHAR2,
62 X_Destination_Organization_Id NUMBER,
63 X_Destination_Subinventory VARCHAR2,
64 X_Quantity_Cancelled NUMBER,
65 X_Cancel_Date DATE,
66 X_Cancel_Reason VARCHAR2,
67 X_Closed_Code VARCHAR2,
68 X_Agent_Return_Note VARCHAR2,
69 X_Changed_After_Research_Flag VARCHAR2,
70 X_Vendor_Id NUMBER,
71 X_Vendor_Site_Id NUMBER,
72 X_Vendor_Contact_Id NUMBER,
73 X_Research_Agent_Id NUMBER,
74 X_On_Line_Flag VARCHAR2,
75 X_Wip_Entity_Id NUMBER,
76 X_Wip_Line_Id NUMBER,
77 X_Wip_Repetitive_Schedule_Id NUMBER,
78 X_Wip_Operation_Seq_Num NUMBER,
79 X_Wip_Resource_Seq_Num NUMBER,
80 X_Attribute_Category VARCHAR2,
81 X_Destination_Context VARCHAR2,
82 X_Inventory_Source_Context VARCHAR2,
83 X_Vendor_Source_Context VARCHAR2,
84 X_Attribute1 VARCHAR2,
85 X_Attribute2 VARCHAR2,
86 X_Attribute3 VARCHAR2,
87 X_Attribute4 VARCHAR2,
88 X_Attribute5 VARCHAR2,
89 X_Attribute6 VARCHAR2,
90 X_Attribute7 VARCHAR2,
91 X_Attribute8 VARCHAR2,
92 X_Attribute9 VARCHAR2,
93 X_Attribute10 VARCHAR2,
94 X_Attribute11 VARCHAR2,
95 X_Attribute12 VARCHAR2,
96 X_Attribute13 VARCHAR2,
97 X_Attribute14 VARCHAR2,
98 X_Attribute15 VARCHAR2,
99 X_Bom_Resource_Id NUMBER,
100 X_Ussgl_Transaction_Code VARCHAR2,
101 X_Government_Context VARCHAR2,
102 X_Closed_Reason VARCHAR2,
103 X_Closed_Date DATE,
104 X_Transaction_Reason_Code VARCHAR2,
105 X_Quantity_Received NUMBER,
106 X_Tax_Code_Id NUMBER,
107 X_Tax_User_Override_Flag VARCHAR2,
108 X_transferred_to_oe_flag OUT NOCOPY 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 X_order_type_lookup_code VARCHAR2 default null, -- <SERVICES FPJ>
120 X_purchase_basis VARCHAR2 default null, -- <SERVICES FPJ>
121 X_matching_basis VARCHAR2 default null, -- <SERVICES FPJ>
122
123 -- Bug #3161499
124 p_negotiated_by_preparer_flag in VARCHAR2 DEFAULT NULL, --<DBI FPJ>
125 p_org_id IN NUMBER DEFAULT NULL -- <R12 MOAC>
126 ) IS
127 CURSOR C IS SELECT rowid FROM PO_REQUISITION_LINES
128 WHERE requisition_line_id = X_Requisition_Line_Id;
129
130 CURSOR C2 IS SELECT po_requisition_lines_s.nextval FROM sys.dual;
131
132 --<REQINPOOL> begin
133 x_reqs_in_pool_flag PO_REQUISITION_LINES_ALL.REQS_IN_POOL_FLAG%TYPE;
134 x_auth_status PO_REQUISITION_HEADERS_ALL.AUTHORIZATION_STATUS%TYPE;
135 x_contractor_status PO_REQUISITION_HEADERS_ALL.CONTRACTOR_STATUS%TYPE;
136 x_progress VARCHAR2(3) := NULL;
137 x_return_status VARCHAR2(1);
138
139 l_manufacturer_id po_requisition_lines_All.MANUFACTURER_ID%TYPE;
140 l_manufacturer_name PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE;
141 l_manufacturer_pn PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE;
142 l_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
143
144 BEGIN
145 if (X_Requisition_Line_Id is NULL) then
146 OPEN C2;
147 FETCH C2 INTO X_Requisition_Line_Id;
148 CLOSE C2;
149 end if;
150
151 x_progress := '010';
152
153 --<REQINPOOL> Begin: determine what the value of reqs_in_pool_flag
154 --should be based on the other parameters
155 BEGIN
156
157 x_progress := '011';
158
159 SELECT prh.authorization_status
160 , prh.contractor_status
161 INTO x_auth_status
162 , x_contractor_status
163 FROM po_requisition_headers_all prh
164 WHERE prh.requisition_header_id = X_Requisition_Header_Id;
165
166 x_progress := '012';
167
168 IF ( NVL(X_Cancel_Flag,'N') = 'N'
169 AND NVL(X_Closed_Code,'OPEN') <> 'FINALLY CLOSED'
170 AND NVL(X_Modified_By_Agent_Flag,'N') = 'N'
171 AND X_Source_Type_Code <> 'INVENTORY'
172 AND X_Line_Location_Id IS NULL
173 AND NVL(x_auth_status,'INCOMPLETE') = 'APPROVED'
174 AND NVL(x_contractor_status,'NOT_APPLICABLE') <> 'PENDING'
175 )
176 THEN
177 x_reqs_in_pool_flag := 'Y';
178 ELSE
179 x_reqs_in_pool_flag := NULL;
180 END IF;
181
182 x_progress := '013';
183
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 x_reqs_in_pool_flag := NULL;
187 END;
188 --<REQINPOOL> End
189
190 IF (x_item_id IS NOT NULL) Then
191 po_attribute_values_pvt.get_item_attributes_values(X_Item_Id, l_manufacturer_pn,l_manufacturer_name,
192 l_lead_time,l_manufacturer_id) ;
193
194 END IF ;
195
196 INSERT INTO PO_REQUISITION_LINES(
197 requisition_line_id,
198 requisition_header_id,
199 line_num,
200 line_type_id,
201 category_id,
202 item_description,
203 unit_meas_lookup_code,
204 unit_price,
205 base_unit_price, -- <FPJ Advanced Price>
206 quantity,
207 amount, -- <SERVICES FPJ>
208 deliver_to_location_id,
209 to_person_id,
210 last_update_date,
211 last_updated_by,
212 source_type_code,
213 last_update_login,
214 creation_date,
215 created_by,
216 item_id,
217 item_revision,
218 quantity_delivered,
219 suggested_buyer_id,
220 encumbered_flag,
221 rfq_required_flag,
222 need_by_date,
223 line_location_id,
224 modified_by_agent_flag,
225 parent_req_line_id,
226 justification,
227 note_to_agent,
228 note_to_receiver,
229 purchasing_agent_id,
230 document_type_code,
231 blanket_po_header_id,
232 blanket_po_line_num,
233 currency_code,
234 rate_type,
235 rate_date,
236 rate,
237 currency_unit_price,
238 currency_amount, -- <SERVICES FPJ>
239 suggested_vendor_name,
240 suggested_vendor_location,
241 suggested_vendor_contact,
242 suggested_vendor_phone,
243 suggested_vendor_product_code,
244 un_number_id,
245 hazard_class_id,
246 must_use_sugg_vendor_flag,
247 reference_num,
248 on_rfq_flag,
249 urgent_flag,
250 cancel_flag,
251 source_organization_id,
252 source_subinventory,
253 destination_type_code,
254 destination_organization_id,
255 destination_subinventory,
256 quantity_cancelled,
257 cancel_date,
258 cancel_reason,
259 closed_code,
260 agent_return_note,
261 changed_after_research_flag,
262 vendor_id,
263 vendor_site_id,
264 vendor_contact_id,
265 research_agent_id,
266 on_line_flag,
267 wip_entity_id,
268 wip_line_id,
269 wip_repetitive_schedule_id,
270 wip_operation_seq_num,
271 wip_resource_seq_num,
272 attribute_category,
273 destination_context,
274 inventory_source_context,
275 vendor_source_context,
276 attribute1,
277 attribute2,
278 attribute3,
279 attribute4,
280 attribute5,
281 attribute6,
282 attribute7,
283 attribute8,
284 attribute9,
285 attribute10,
286 attribute11,
287 attribute12,
288 attribute13,
289 attribute14,
290 attribute15,
291 bom_resource_id,
292 government_context,
293 closed_reason,
294 closed_date,
295 transaction_reason_code,
296 quantity_received,
297 tax_code_id,
298 tax_user_override_flag,
299 --togeorge 10/03/2000
300 -- added oke columns
301 oke_contract_header_id,
302 oke_contract_version_id,
303 -- start of 1548597
304 secondary_unit_of_measure,
305 secondary_quantity,
306 preferred_grade,
307 -- end of 1548597
308 order_type_lookup_code, -- <SERVICES FPJ>
309 purchase_basis, -- <SERVICES FPJ>
310 matching_basis, -- <SERVICES FPJ>
311 negotiated_by_preparer_flag, -- <DBI FPJ> 3161499
312 reqs_in_pool_flag, -- <REQINPOOL>
313 Org_Id, -- <R12 MOAC>
314 tax_attribute_update_code, --<eTax Integration R12>
315 MANUFACTURER_ID, --bug 7387487
316 MANUFACTURER_NAME,
317 MANUFACTURER_PART_NUMBER
318
319 ) VALUES (
320 X_Requisition_Line_Id,
321 X_Requisition_Header_Id,
322 X_Line_Num,
323 X_Line_Type_Id,
324 X_Category_Id,
325 X_Item_Description,
326 X_Unit_Meas_Lookup_Code,
327 X_Unit_Price,
328 X_Base_Unit_Price, -- <FPJ Advanced Price>
329 X_Quantity,
330 X_Amount, -- <SERVICES FPJ>
331 X_Deliver_To_Location_Id,
332 X_To_Person_Id,
333 X_Last_Update_Date,
334 X_Last_Updated_By,
335 X_Source_Type_Code,
336 X_Last_Update_Login,
337 X_Creation_Date,
338 X_Created_By,
339 X_Item_Id,
340 X_Item_Revision,
341 X_Quantity_Delivered,
342 X_Suggested_Buyer_Id,
343 X_Encumbered_Flag,
344 nvl(X_Rfq_Required_Flag, 'N'),
345 X_Need_By_Date,
346 X_Line_Location_Id,
347 X_Modified_By_Agent_Flag,
348 X_Parent_Req_Line_Id,
349 X_Justification,
350 X_Note_To_Agent,
351 X_Note_To_Receiver,
352 X_Purchasing_Agent_Id,
353 X_Document_Type_Code,
354 X_Blanket_Po_Header_Id,
355 X_Blanket_Po_Line_Num,
356 X_Currency_Code,
357 X_Rate_Type,
358 X_Rate_Date,
359 X_Rate,
360 X_Currency_Unit_Price,
361 X_Currency_Amount, -- <SERVICES FPJ>
362 X_Suggested_Vendor_Name,
363 X_Suggested_Vendor_Location,
364 X_Suggested_Vendor_Contact,
365 X_Suggested_Vendor_Phone,
366 X_Sugg_Vendor_Product_Code,
367 X_Un_Number_Id,
368 X_Hazard_Class_Id,
369 X_Must_Use_Sugg_Vendor_Flag,
370 X_Reference_Num,
371 X_On_Rfq_Flag,
372 X_Urgent_Flag,
373 X_Cancel_Flag,
374 X_Source_Organization_Id,
375 X_Source_Subinventory,
376 X_Destination_Type_Code,
377 X_Destination_Organization_Id,
378 X_Destination_Subinventory,
379 X_Quantity_Cancelled,
380 X_Cancel_Date,
381 X_Cancel_Reason,
382 X_Closed_Code,
383 X_Agent_Return_Note,
384 X_Changed_After_Research_Flag,
385 X_Vendor_Id,
386 X_Vendor_Site_Id,
387 X_Vendor_Contact_Id,
388 X_Research_Agent_Id,
389 X_On_Line_Flag,
390 X_Wip_Entity_Id,
391 X_Wip_Line_Id,
392 X_Wip_Repetitive_Schedule_Id,
393 X_Wip_Operation_Seq_Num,
394 X_Wip_Resource_Seq_Num,
395 X_Attribute_Category,
396 X_Destination_Context,
397 X_Inventory_Source_Context,
398 X_Vendor_Source_Context,
399 X_Attribute1,
400 X_Attribute2,
401 X_Attribute3,
402 X_Attribute4,
403 X_Attribute5,
404 X_Attribute6,
405 X_Attribute7,
406 X_Attribute8,
407 X_Attribute9,
408 X_Attribute10,
409 X_Attribute11,
410 X_Attribute12,
411 X_Attribute13,
412 X_Attribute14,
413 X_Attribute15,
414 X_Bom_Resource_Id,
415 X_Government_Context,
416 X_Closed_Reason,
417 X_Closed_Date,
418 X_Transaction_Reason_Code,
419 X_Quantity_Received,
420 X_tax_code_id,
421 X_Tax_User_Override_Flag,
422 --togeorge 10/03/2000
423 -- added oke columns
424 X_oke_contract_header_id,
425 X_oke_contract_version_id,
426 -- start of 1548597
427 X_secondary_unit_of_measure,
428 X_secondary_quantity,
429 X_preferred_grade,
430 -- end of 1548597
431 X_order_type_lookup_code, -- <SERVICES FPJ>
432 X_purchase_basis, -- <SERVICES FPJ>
433 X_matching_basis, -- <SERVICES FPJ>
434 p_negotiated_by_preparer_flag, -- <DBI FPJ>
435 x_reqs_in_pool_flag, --<REQINPOOL>
436 p_org_id, -- <R12 MOAC>
437 'CREATE' , --<eTax Integration R12>
438 l_manufacturer_id,
439 l_manufacturer_name,
440 l_manufacturer_pn
441
442 );
443
444 x_progress := '020';
445
446 --togeorge 10/26/2000 commented out due to arcs in problems
447 --dbms_output.put_line ('Before call to update_transferred...');
448
449 po_req_lines_sv.update_transferred_to_oe_flag (X_requisition_header_id,
450 X_transferred_to_oe_flag);
451
452 OPEN C;
453 FETCH C INTO X_Rowid;
454 if (C%NOTFOUND) then
455 CLOSE C;
456 Raise NO_DATA_FOUND;
457 end if;
458 CLOSE C;
459
460 EXCEPTION
461 WHEN OTHERS then
462 po_message_s.sql_error('INSERT_ROW',x_progress,sqlcode);
463 raise;
464
465 END Insert_Row;
466
467
468 END PO_REQUISITION_LINES_PKG2;