[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_PKG_SI
Source
1 PACKAGE BODY PO_LINES_PKG_SI as
2 /* $Header: POXPIL1B.pls 120.5 2005/09/20 02:39:59 sjadhav noship $ */
3
4 /*History
5 Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
6 X_Base_Uom, X_Base_Qty , X_Secondary_Uom, X_Secondary_Qty, X_Qc_Grade
7 */
8
9 -- Mahesh Chandak(GML-OPM).bug# 1548597.Add secondary_unit_of_measure,secondary_quantity,preferred_grade for CR.base_uom and base_qty won't be used in future..
10
11 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
12 X_Po_Line_Id IN OUT NOCOPY NUMBER,
13 X_Last_Update_Date DATE,
14 X_Last_Updated_By NUMBER,
15 X_Po_Header_Id NUMBER,
16 X_Line_Type_Id NUMBER,
17 X_Line_Num NUMBER,
18 X_Last_Update_Login NUMBER,
19 X_Creation_Date DATE,
20 X_Created_By NUMBER,
21 X_Item_Id NUMBER,
22 X_Item_Revision VARCHAR2,
23 X_Category_Id NUMBER,
24 X_Item_Description VARCHAR2,
25 X_Unit_Meas_Lookup_Code VARCHAR2,
26 X_Quantity_Committed NUMBER,
27 X_Committed_Amount NUMBER,
28 X_Allow_Price_Override_Flag VARCHAR2,
29 X_Not_To_Exceed_Price NUMBER,
30 X_List_Price_Per_Unit NUMBER,
31 -- <FPJ Advanced Price START>
32 X_Base_Unit_Price NUMBER,
33 -- <FPJ Advanced Price END>
34 X_Unit_Price NUMBER,
35 X_Quantity NUMBER,
36 X_Un_Number_Id NUMBER,
37 X_Hazard_Class_Id NUMBER,
38 X_Note_To_Vendor VARCHAR2,
39 X_From_Header_Id NUMBER,
40 X_From_Line_Id NUMBER,
41 x_from_line_location_id NUMBER, -- <SERVICES FPJ>
42 X_Min_Order_Quantity NUMBER,
43 X_Max_Order_Quantity NUMBER,
44 X_Qty_Rcv_Tolerance NUMBER,
45 X_Over_Tolerance_Error_Flag VARCHAR2,
46 X_Market_Price NUMBER,
47 X_Unordered_Flag VARCHAR2,
48 X_Closed_Flag VARCHAR2,
49 X_User_Hold_Flag VARCHAR2,
50 X_Cancel_Flag VARCHAR2,
51 X_Cancelled_By NUMBER,
52 X_Cancel_Date DATE,
53 X_Cancel_Reason VARCHAR2,
54 X_Firm_Status_Lookup_Code VARCHAR2,
55 X_Firm_Date DATE,
56 X_Vendor_Product_Num VARCHAR2,
57 X_Contract_Num VARCHAR2,
58 X_Taxable_Flag VARCHAR2,
59 X_Tax_Code_Id NUMBER,
60 X_Type_1099 VARCHAR2,
61 X_Capital_Expense_Flag VARCHAR2,
62 X_Negotiated_By_Preparer_Flag VARCHAR2,
63 X_Attribute_Category VARCHAR2,
64 X_Attribute1 VARCHAR2,
65 X_Attribute2 VARCHAR2,
66 X_Attribute3 VARCHAR2,
67 X_Attribute4 VARCHAR2,
68 X_Attribute5 VARCHAR2,
69 X_Attribute6 VARCHAR2,
70 X_Attribute7 VARCHAR2,
71 X_Attribute8 VARCHAR2,
72 X_Attribute9 VARCHAR2,
73 X_Attribute10 VARCHAR2,
74 X_Reference_Num VARCHAR2,
75 X_Attribute11 VARCHAR2,
76 X_Attribute12 VARCHAR2,
77 X_Attribute13 VARCHAR2,
78 X_Attribute14 VARCHAR2,
79 X_Attribute15 VARCHAR2,
80 X_Min_Release_Amount NUMBER,
81 X_Price_Type_Lookup_Code VARCHAR2,
82 X_Closed_Code VARCHAR2,
83 X_Price_Break_Lookup_Code VARCHAR2,
84 X_Ussgl_Transaction_Code VARCHAR2,
85 X_Government_Context VARCHAR2,
86 X_Closed_Date DATE,
87 X_Closed_Reason VARCHAR2,
88 X_Closed_By NUMBER,
89 X_Transaction_Reason_Code VARCHAR2,
90 X_Global_Attribute_Category VARCHAR2,
91 X_Global_Attribute1 VARCHAR2,
92 X_Global_Attribute2 VARCHAR2,
93 X_Global_Attribute3 VARCHAR2,
94 X_Global_Attribute4 VARCHAR2,
95 X_Global_Attribute5 VARCHAR2,
96 X_Global_Attribute6 VARCHAR2,
97 X_Global_Attribute7 VARCHAR2,
98 X_Global_Attribute8 VARCHAR2,
99 X_Global_Attribute9 VARCHAR2,
100 X_Global_Attribute10 VARCHAR2,
101 X_Global_Attribute11 VARCHAR2,
102 X_Global_Attribute12 VARCHAR2,
103 X_Global_Attribute13 VARCHAR2,
104 X_Global_Attribute14 VARCHAR2,
105 X_Global_Attribute15 VARCHAR2,
106 X_Global_Attribute16 VARCHAR2,
107 X_Global_Attribute17 VARCHAR2,
108 X_Global_Attribute18 VARCHAR2,
109 X_Global_Attribute19 VARCHAR2,
110 X_Global_Attribute20 VARCHAR2,
111 X_Expiration_Date DATE,
112 -- Mahesh Chandak(GML-OPM).bug# 1548597.Add secondary_unit_of_measure,secondary_quantity,preferred_grade for CR.base_uom and base_qty won't be used in future..
113 --Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
114 --Bug# 1056597
115 X_Base_Uom VARCHAR2,
116 X_Base_Qty NUMBER,
117 X_Secondary_Uom VARCHAR2,
118 X_Secondary_Qty NUMBER,
119 X_Qc_Grade VARCHAR2,
120 --togeorge 10/03/2000
121 --added oke columns
122 X_oke_contract_header_id NUMBER default null,
123 X_oke_contract_version_id NUMBER default null,
124 --mchandak 1548597
125 X_Secondary_Unit_Of_Measure VARCHAR2 default null,
126 X_Secondary_Quantity NUMBER default null,
127 X_Preferred_Grade VARCHAR2 default null,
128 p_contract_id IN NUMBER DEFAULT NULL, -- <GC FPJ>
129 X_job_id IN NUMBER DEFAULT NULL, -- <SERVICES FPJ>
130 X_contractor_first_name IN VARCHAR2 DEFAULT NULL, -- <SERVICES FPJ>
131 X_contractor_last_name IN VARCHAR2 DEFAULT NULL, -- <SERVICES FPJ>
132 X_assignment_start_date IN DATE DEFAULT NULL, -- <SERVICES FPJ>
133 X_amount_db IN NUMBER DEFAULT NULL, -- <SERVICES FPJ>
134 X_order_type_lookup_code IN VARCHAR2 DEFAULT NULL, -- <SERVICES FPJ>
135 X_purchase_basis IN VARCHAR2 DEFAULT NULL, -- <SERVICES FPJ>
136 X_matching_basis IN VARCHAR2 DEFAULT NULL, -- <SERVICES FPJ>
137 p_manual_price_change_flag IN VARCHAR2 DEFAULT NULL, -- <Manual Price Override FPJ>
138 p_org_id IN NUMBER DEFAULT NULL, -- <R12 MOAC>
139 p_ip_category_id IN NUMBER DEFAULT NULL -- <Unified Catalog R12>
140 ) IS
141 CURSOR C IS SELECT rowid FROM PO_LINES
142 WHERE po_line_id = X_Po_Line_Id;
143
144 CURSOR S IS SELECT po_lines_s.nextval FROM sys.dual;
145
146 x_progress VARCHAR2(3) := '';
147 --<eTax Integration R12 Start>
148 l_tax_attribute_update_code PO_LINES_ALL.tax_attribute_update_code%type;
149 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%type;
150 --<eTax Integration R12 End>
151
152
153 BEGIN
154 x_progress := '010';
155
156 if (X_Po_Line_Id is NULL) then
157 OPEN S;
158 FETCH S INTO X_Po_Line_Id;
159 CLOSE S;
160 end if;
161
162 x_progress := '020';
163
164 --<eTax Integration R12 Start>
165 SELECT poh.type_lookup_code
166 INTO l_type_lookup_code
167 FROM po_headers_all poh
168 WHERE poh.po_header_id = x_po_header_id;
169
170 IF l_type_lookup_code IN ('STANDARD', 'PLANNED') THEN
171 l_tax_attribute_update_code := 'CREATE';
172 END IF;
173 --<eTax Integration R12 End>
174
175 x_progress := '030';
176
177 INSERT INTO PO_LINES (
178 po_line_id,
179 last_update_date,
180 last_updated_by,
181 po_header_id,
182 line_type_id,
183 line_num,
184 last_update_login,
185 creation_date,
186 created_by,
187 item_id,
188 item_revision,
189 category_id,
190 item_description,
191 unit_meas_lookup_code,
192 quantity_committed,
193 committed_amount,
194 allow_price_override_flag,
195 not_to_exceed_price,
196 list_price_per_unit,
197 -- <FPJ Advanced Price START>
198 base_unit_price,
199 -- <FPJ Advanced Price END>
200 unit_price,
201 quantity,
202 un_number_id,
203 hazard_class_id,
204 note_to_vendor,
205 from_header_id,
206 from_line_id,
207 from_line_location_id, -- <SERVICES FPJ>
208 min_order_quantity,
209 max_order_quantity,
210 qty_rcv_tolerance,
211 over_tolerance_error_flag,
212 market_price,
213 unordered_flag,
214 closed_flag,
215 user_hold_flag,
216 cancel_flag,
217 cancelled_by,
218 cancel_date,
219 cancel_reason,
220 firm_status_lookup_code,
221 firm_date,
222 vendor_product_num,
223 contract_num,
224 taxable_flag,
225 tax_code_id,
229 attribute_category,
226 type_1099,
227 capital_expense_flag,
228 negotiated_by_preparer_flag,
230 attribute1,
231 attribute2,
232 attribute3,
233 attribute4,
234 attribute5,
235 attribute6,
236 attribute7,
237 attribute8,
238 attribute9,
239 attribute10,
240 reference_num,
241 attribute11,
242 attribute12,
243 attribute13,
244 attribute14,
245 attribute15,
246 min_release_amount,
247 price_type_lookup_code,
248 closed_code,
249 price_break_lookup_code,
250 government_context,
251 closed_date,
252 closed_reason,
253 closed_by,
254 transaction_reason_code,
255 global_attribute_category,
256 global_attribute1,
257 global_attribute2,
258 global_attribute3,
259 global_attribute4,
260 global_attribute5,
261 global_attribute6,
262 global_attribute7,
263 global_attribute8,
264 global_attribute9,
265 global_attribute10,
266 global_attribute11,
267 global_attribute12,
268 global_attribute13,
269 global_attribute14,
270 global_attribute15,
271 global_attribute16,
272 global_attribute17,
273 global_attribute18,
274 global_attribute19,
275 global_attribute20,
276 expiration_date,
277 /** Mahesh Chandak(GML)bug# 1548597 base_uom and base_qty won't be used
278 in the future.we are keeping secondary_uom,secondary_qty and qc_grade for
279 supporting Common Purchasing. we will have 3 new fields secondary_unit_of_measure, secondary_quantity and preferred_grade columns in the table **/
280 -- start of 1548597
281 --Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
282 --Bug# 1056597
283 --base_uom,
284 --base_qty,
285 -- INVCONV no need to insert into secondary_uom,qty and qc_grade.no longer used.
286 -- also preferred_grade is 150 and qc_grade is 25 which makes it incompatible.
287 --secondary_uom,
288 --secondary_qty,
289 --qc_grade,
290 --togeorge 10/03/2000
291 --added oke columns
292 oke_contract_header_id,
293 oke_contract_version_id,
294 secondary_unit_of_measure,
295 secondary_quantity,
296 preferred_grade,
297 -- end of 1548597
298 contract_id, -- <GC FPJ>
299 job_id, -- <SERVICES FPJ>
300 contractor_first_name, -- <SERVICES FPJ>
301 contractor_last_name, -- <SERVICES FPJ>
302 start_date, -- <SERVICES FPJ>
303 amount, -- <SERVICES FPJ>
304 order_type_lookup_code, -- <SERVICES FPJ>
305 purchase_basis, -- <SERVICES FPJ>
306 matching_basis, -- <SERVICES FPJ>
307 manual_price_change_flag, -- <Manual Price Override FPJ>
308 Org_Id, -- <R12 MOAC>
309 ip_category_id, -- <Unified Catalog R12>
310 tax_attribute_update_code --<eTax Integration R12>
311 ) VALUES (
312 X_Po_Line_Id,
313 X_Last_Update_Date,
314 X_Last_Updated_By,
315 X_Po_Header_Id,
316 X_Line_Type_Id,
317 X_Line_Num,
318 X_Last_Update_Login,
319 X_Creation_Date,
320 X_Created_By,
321 X_Item_Id,
322 X_Item_Revision,
323 X_Category_Id,
324 X_Item_Description,
325 X_Unit_Meas_Lookup_Code,
326 X_Quantity_Committed,
327 X_Committed_Amount,
328 X_Allow_Price_Override_Flag,
329 X_Not_To_Exceed_Price,
330 X_List_Price_Per_Unit,
331 -- <FPJ Advanced Price START>
332 X_Base_Unit_Price,
333 -- <FPJ Advanced Price END>
334 X_Unit_Price,
335 X_Quantity,
336 X_Un_Number_Id,
337 X_Hazard_Class_Id,
338 X_Note_To_Vendor,
339 X_From_Header_Id,
340 X_From_Line_Id,
341 x_from_line_location_Id, -- <SERVICES FPJ>
342 X_Min_Order_Quantity,
343 X_Max_Order_Quantity,
344 X_Qty_Rcv_Tolerance,
345 X_Over_Tolerance_Error_Flag,
346 X_Market_Price,
347 X_Unordered_Flag,
348 X_Closed_Flag,
349 X_User_Hold_Flag,
350 X_Cancel_Flag,
351 X_Cancelled_By,
352 X_Cancel_Date,
353 X_Cancel_Reason,
354 X_Firm_Status_Lookup_Code,
355 X_Firm_Date,
356 X_Vendor_Product_Num,
357 X_Contract_Num,
358 X_Taxable_Flag,
359 X_Tax_Code_Id,
360 X_Type_1099,
361 X_Capital_Expense_Flag,
362 X_Negotiated_By_Preparer_Flag,
363 X_Attribute_Category,
364 X_Attribute1,
365 X_Attribute2,
366 X_Attribute3,
367 X_Attribute4,
368 X_Attribute5,
369 X_Attribute6,
373 X_Attribute10,
370 X_Attribute7,
371 X_Attribute8,
372 X_Attribute9,
374 X_Reference_Num,
375 X_Attribute11,
376 X_Attribute12,
377 X_Attribute13,
378 X_Attribute14,
379 X_Attribute15,
380 X_Min_Release_Amount,
381 X_Price_Type_Lookup_Code,
382 X_Closed_Code,
383 X_Price_Break_Lookup_Code,
384 X_Government_Context,
385 X_Closed_Date,
386 X_Closed_Reason,
387 X_Closed_By,
388 X_Transaction_Reason_Code,
389 X_Global_Attribute_Category,
390 X_Global_Attribute1,
391 X_Global_Attribute2,
392 X_Global_Attribute3,
393 X_Global_Attribute4,
394 X_Global_Attribute5,
395 X_Global_Attribute6,
396 X_Global_Attribute7,
397 X_Global_Attribute8,
398 X_Global_Attribute9,
399 X_Global_Attribute10,
400 X_Global_Attribute11,
401 X_Global_Attribute12,
402 X_Global_Attribute13,
403 X_Global_Attribute14,
404 X_Global_Attribute15,
405 X_Global_Attribute16,
406 X_Global_Attribute17,
407 X_Global_Attribute18,
408 X_Global_Attribute19,
409 X_Global_Attribute20,
410 X_Expiration_Date,
411 --Mahesh Chandak(GML) BUG# 1548597. insert secondary_unit_of_measure,secondary_quantity and preferred_grade for secondary_uom,secondary_qty and qc_grade.
412 --Preetam Bamb (GML) 10-feb-2000 Added 5 columns to the insert_row procedure
413 --Bug# 1056597
414 -- start of 1548597
415 --X_Base_Uom,
416 --X_Base_Qty,
417 -- INVCONV comment the 3 columns
418 --X_Secondary_Unit_Of_Measure,
419 --X_Secondary_Quantity,
420 --X_Preferred_Grade,
421 -- end of 1548597
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 p_contract_id, -- <GC FPJ>
432 X_job_id, -- <SERVICES FPJ>
433 X_contractor_first_name, -- <SERVICES FPJ>
434 X_contractor_last_name, -- <SERVICES FPJ>
435 X_assignment_start_date, -- <SERVICES FPJ>
436 X_amount_db, -- <SERVICES FPJ>
437 X_order_type_lookup_code, -- <SERVICES FPJ>
438 X_purchase_basis, -- <SERVICES FPJ>
439 X_matching_basis, -- <SERVICES FPJ>
440 p_manual_price_change_flag, -- <Manual Price Override FPJ>
441 p_org_id, -- <R12 MOAC>
442 p_ip_category_id, -- <Unified Catalog R12>
443 l_tax_attribute_update_code --<eTax Integration R12>
444 );
445
446 OPEN C;
447 FETCH C INTO X_Rowid;
448 if (C%NOTFOUND) then
449 CLOSE C;
450 Raise NO_DATA_FOUND;
451 end if;
452 CLOSE C;
453
454 END Insert_Row;
455
456 END PO_LINES_PKG_SI;