[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_PKG_SUD
Source
1 PACKAGE BODY PO_LINES_PKG_SUD as
2 /* $Header: POXPIL4B.pls 120.3.12010000.2 2008/12/18 07:46:02 mugoel ship $ */
3
4 PROCEDURE Update_Row(X_Rowid VARCHAR2,
5 X_Po_Line_Id NUMBER,
6 X_Last_Update_Date DATE,
7 X_Last_Updated_By NUMBER,
8 X_Po_Header_Id NUMBER,
9 X_Line_Type_Id NUMBER,
10 X_Line_Num NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Item_Id NUMBER,
13 X_Item_Revision VARCHAR2,
14 X_Category_Id NUMBER,
15 X_Item_Description VARCHAR2,
16 X_Unit_Meas_Lookup_Code VARCHAR2,
17 X_Quantity_Committed NUMBER,
18 X_Committed_Amount NUMBER,
19 X_Allow_Price_Override_Flag VARCHAR2,
20 X_Not_To_Exceed_Price NUMBER,
21 X_List_Price_Per_Unit NUMBER,
22 -- <FPJ Advanced Price START>
23 X_Base_Unit_Price NUMBER,
24 -- <FPJ Advanced Price END>
25 X_Unit_Price NUMBER,
26 X_Quantity NUMBER,
27 X_Un_Number_Id NUMBER,
28 X_Hazard_Class_Id NUMBER,
29 X_Note_To_Vendor VARCHAR2,
30 X_From_Header_Id NUMBER,
31 X_From_Line_Id NUMBER,
32 X_From_Line_Location_Id NUMBER, -- <SERVICES FPJ>
33 X_Min_Order_Quantity NUMBER,
34 X_Max_Order_Quantity NUMBER,
35 X_Qty_Rcv_Tolerance NUMBER,
36 X_Over_Tolerance_Error_Flag VARCHAR2,
37 X_Market_Price NUMBER,
38 X_Unordered_Flag VARCHAR2,
39 X_Closed_Flag VARCHAR2,
40 X_User_Hold_Flag VARCHAR2,
41 X_Cancel_Flag VARCHAR2,
42 X_Cancelled_By NUMBER,
43 X_Cancel_Date DATE,
44 X_Cancel_Reason VARCHAR2,
45 X_Firm_Status_Lookup_Code VARCHAR2,
46 X_Firm_Date DATE,
47 X_Vendor_Product_Num VARCHAR2,
48 X_Contract_Num VARCHAR2,
49 X_Taxable_Flag VARCHAR2,
50 X_Tax_Code_Id NUMBER,
51 X_Type_1099 VARCHAR2,
52 X_Capital_Expense_Flag VARCHAR2,
53 X_Negotiated_By_Preparer_Flag VARCHAR2,
54 X_Attribute_Category VARCHAR2,
55 X_Attribute1 VARCHAR2,
56 X_Attribute2 VARCHAR2,
57 X_Attribute3 VARCHAR2,
58 X_Attribute4 VARCHAR2,
59 X_Attribute5 VARCHAR2,
60 X_Attribute6 VARCHAR2,
61 X_Attribute7 VARCHAR2,
62 X_Attribute8 VARCHAR2,
63 X_Attribute9 VARCHAR2,
64 X_Attribute10 VARCHAR2,
65 X_Reference_Num VARCHAR2,
66 X_Attribute11 VARCHAR2,
67 X_Attribute12 VARCHAR2,
68 X_Attribute13 VARCHAR2,
69 X_Attribute14 VARCHAR2,
70 X_Attribute15 VARCHAR2,
71 X_Min_Release_Amount NUMBER,
72 X_Price_Type_Lookup_Code VARCHAR2,
73 X_Closed_Code VARCHAR2,
74 X_Price_Break_Lookup_Code VARCHAR2,
75 X_Ussgl_Transaction_Code VARCHAR2,
76 X_Government_Context VARCHAR2,
77 X_Closed_Date DATE,
78 X_Closed_Reason VARCHAR2,
79 X_Closed_By NUMBER,
80 X_Transaction_Reason_Code VARCHAR2,
81 X_Global_Attribute_Category VARCHAR2,
82 X_Global_Attribute1 VARCHAR2,
83 X_Global_Attribute2 VARCHAR2,
84 X_Global_Attribute3 VARCHAR2,
85 X_Global_Attribute4 VARCHAR2,
86 X_Global_Attribute5 VARCHAR2,
87 X_Global_Attribute6 VARCHAR2,
88 X_Global_Attribute7 VARCHAR2,
89 X_Global_Attribute8 VARCHAR2,
90 X_Global_Attribute9 VARCHAR2,
91 X_Global_Attribute10 VARCHAR2,
92 X_Global_Attribute11 VARCHAR2,
93 X_Global_Attribute12 VARCHAR2,
94 X_Global_Attribute13 VARCHAR2,
95 X_Global_Attribute14 VARCHAR2,
96 X_Global_Attribute15 VARCHAR2,
97 X_Global_Attribute16 VARCHAR2,
98 X_Global_Attribute17 VARCHAR2,
99 X_Global_Attribute18 VARCHAR2,
100 X_Global_Attribute19 VARCHAR2,
101 X_Global_Attribute20 VARCHAR2,
102 X_Expiration_Date DATE,
103 --Preetam Bamb (GML) 21-feb-2000 Added 5 columns to the insert_row procedure
104 --Bug# 1056597
105 X_Base_Uom VARCHAR2,
106 X_Base_Qty NUMBER,
107 X_Secondary_Uom VARCHAR2,
108 X_Secondary_Qty NUMBER,
109 X_Qc_Grade VARCHAR2,
110 --togeorge 10/03/2000
111 --added oke columns
112 X_oke_contract_header_id NUMBER default null,
113 X_oke_contract_version_id NUMBER default null,
114 -- 1548597 add 3 new process fields..
115 X_Secondary_Unit_of_measure VARCHAR2 default null,
116 X_Secondary_Quantity NUMBER default null,
117 X_preferred_Grade VARCHAR2 default null,
118 p_contract_id IN NUMBER DEFAULT NULL, -- <GC FPJ>
119 X_job_id NUMBER, -- <SERVICES FPJ>
120 X_contractor_first_name VARCHAR2, -- <SERVICES FPJ>
121 X_contractor_last_name VARCHAR2, -- <SERVICES FPJ>
122 X_assignment_start_date DATE, -- <SERVICES FPJ>
123 X_amount_db NUMBER, -- <SERVICES FPJ>
124 p_manual_price_change_flag VARCHAR2, -- <Manual Price Override FPJ>
125 p_ip_category_id NUMBER -- Bug 7577670
126 ) IS
127
128 --<eTax Integration R12 Start>
129 l_tax_attribute_update_code PO_LINES_ALL.tax_attribute_update_code%type;
130 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%type;
131 --<eTax Integration R12 End>
132
133 BEGIN
134
135 --<eTax Integration R12 Start>
136 SELECT poh.type_lookup_code
137 INTO l_type_lookup_code
138 FROM po_headers_all poh, po_lines_all pol
139 WHERE pol.po_line_id = X_Po_Line_Id
140 AND pol.po_header_id = poh.po_header_id;
141
142 IF l_type_lookup_code IN ('STANDARD', 'PLANNED') AND
143 PO_TAX_INTERFACE_PVT.any_tax_attributes_updated(
144 p_doc_type=>'PO',
145 p_doc_level => 'LINE',
146 p_doc_level_id => X_Po_Line_Id,
147 p_uom=>X_Unit_Meas_Lookup_Code,
148 p_price=>X_Unit_Price
149 ) THEN
150 l_tax_attribute_update_code := 'UPDATE';
151 END IF;
152 --<eTax Integration R12 End>
153
154
155 UPDATE PO_LINES
156 SET
157 po_line_id = X_Po_Line_Id,
158 last_update_date = X_Last_Update_Date,
159 last_updated_by = X_Last_Updated_By,
160 po_header_id = X_Po_Header_Id,
161 line_type_id = X_Line_Type_Id,
162 line_num = X_Line_Num,
163 last_update_login = X_Last_Update_Login,
164 item_id = X_Item_Id,
165 item_revision = X_Item_Revision,
166 category_id = X_Category_Id,
167 item_description = X_Item_Description,
168 unit_meas_lookup_code = X_Unit_Meas_Lookup_Code,
169 quantity_committed = X_Quantity_Committed,
170 committed_amount = X_Committed_Amount,
171 allow_price_override_flag = X_Allow_Price_Override_Flag,
172 not_to_exceed_price = X_Not_To_Exceed_Price,
173 list_price_per_unit = X_List_Price_Per_Unit,
174 -- <FPJ Advanced Price START>
175 base_unit_price = X_Base_Unit_Price,
176 -- <FPJ Advanced Price END>
177 unit_price = X_Unit_Price,
178 quantity = X_Quantity,
179 un_number_id = X_Un_Number_Id,
180 hazard_class_id = X_Hazard_Class_Id,
181 note_to_vendor = X_Note_To_Vendor,
182 from_header_id = X_From_Header_Id,
183 from_line_id = X_From_Line_Id,
184 from_line_location_id = X_From_Line_Location_Id, -- <SERVICES FPJ>
185 min_order_quantity = X_Min_Order_Quantity,
186 max_order_quantity = X_Max_Order_Quantity,
187 qty_rcv_tolerance = X_Qty_Rcv_Tolerance,
188 over_tolerance_error_flag = X_Over_Tolerance_Error_Flag,
189 market_price = X_Market_Price,
190 unordered_flag = X_Unordered_Flag,
191 closed_flag = X_Closed_Flag,
192 user_hold_flag = X_User_Hold_Flag,
193 cancel_flag = X_Cancel_Flag,
194 cancelled_by = X_Cancelled_By,
195 cancel_date = X_Cancel_Date,
196 cancel_reason = X_Cancel_Reason,
197 firm_status_lookup_code = X_Firm_Status_Lookup_Code,
198 firm_date = X_Firm_Date,
199 vendor_product_num = X_Vendor_Product_Num,
200 contract_num = X_Contract_Num,
201 type_1099 = X_Type_1099,
202 capital_expense_flag = X_Capital_Expense_Flag,
203 negotiated_by_preparer_flag = X_Negotiated_By_Preparer_Flag,
204 attribute_category = X_Attribute_Category,
205 attribute1 = X_Attribute1,
206 attribute2 = X_Attribute2,
207 attribute3 = X_Attribute3,
208 attribute4 = X_Attribute4,
209 attribute5 = X_Attribute5,
210 attribute6 = X_Attribute6,
211 attribute7 = X_Attribute7,
212 attribute8 = X_Attribute8,
213 attribute9 = X_Attribute9,
214 attribute10 = X_Attribute10,
215 reference_num = X_Reference_Num,
216 attribute11 = X_Attribute11,
217 attribute12 = X_Attribute12,
218 attribute13 = X_Attribute13,
219 attribute14 = X_Attribute14,
220 attribute15 = X_Attribute15,
221 min_release_amount = X_Min_Release_Amount,
222 price_type_lookup_code = X_Price_Type_Lookup_Code,
223 closed_code = X_Closed_Code,
224 price_break_lookup_code = X_Price_Break_Lookup_Code,
225 ussgl_transaction_code = X_Ussgl_Transaction_Code,
226 government_context = X_Government_Context,
227 closed_date = X_Closed_Date,
228 closed_reason = X_Closed_Reason,
229 closed_by = X_Closed_By,
230 transaction_reason_code = X_Transaction_Reason_Code,
231 global_attribute_category = X_Global_Attribute_Category,
232 global_attribute1 = X_Global_Attribute1,
233 global_attribute2 = X_Global_Attribute2,
234 global_attribute3 = X_Global_Attribute3,
235 global_attribute4 = X_Global_Attribute4,
236 global_attribute5 = X_Global_Attribute5,
237 global_attribute6 = X_Global_Attribute6,
238 global_attribute7 = X_Global_Attribute7,
239 global_attribute8 = X_Global_Attribute8,
240 global_attribute9 = X_Global_Attribute9,
241 global_attribute10 = X_Global_Attribute10,
242 global_attribute11 = X_Global_Attribute11,
243 global_attribute12 = X_Global_Attribute12,
244 global_attribute13 = X_Global_Attribute13,
245 global_attribute14 = X_Global_Attribute14,
246 global_attribute15 = X_Global_Attribute15,
247 global_attribute16 = X_Global_Attribute16,
248 global_attribute17 = X_Global_Attribute17,
249 global_attribute18 = X_Global_Attribute18,
250 global_attribute19 = X_Global_Attribute19,
251 global_attribute20 = X_Global_Attribute20,
252 expiration_date = X_Expiration_Date,
253 --Mahesh Chandak(GML) BUG# 1548597. update secondary_unit_of_measure,secondary_quantity and preferred_grade for secondary_uom,secondary_qty and qc_grade.base_uom and base_qty won't be used in the future..
254 -- start of 1548597
255 --Preetam Bamb (GML) 21-feb-2000 Added 5 columns to the insert_row procedure
256 --Bug# 1056597
257 --INVCONV no need to update qc_grade,secondary_uom and secondary_qty.these columns no longer used
258 -- also update was failing since qc_grade is 25 and preferred_grade is 150
259 --qc_grade = X_preferred_grade,
260 --base_uom = X_Base_uom,
261 --base_qty = X_Base_qty,
262 --secondary_uom = X_Secondary_unit_of_measure,
263 --secondary_qty = X_Secondary_quantity,
264 secondary_unit_of_measure = X_secondary_unit_of_measure,
265 secondary_quantity = X_secondary_quantity,
266 preferred_grade = X_preferred_grade,
267 -- end of 1548597
268 --togeorge 10/03/2000
269 --added oke columns
270 oke_contract_header_id = X_oke_contract_header_id,
271 oke_contract_version_id = X_oke_contract_version_id,
272 contract_id = p_contract_id, -- <GC FPJ>
273 job_id = X_job_id, -- <SERVICES FPJ>
274 contractor_first_name = X_contractor_first_name, -- <SERVICES FPJ>
275 contractor_last_name = X_contractor_last_name, -- <SERVICES FPJ>
276 start_date = X_assignment_start_date, -- <SERVICES FPJ>
277 amount = X_amount_db, -- <SERVICES FPJ>
278 manual_price_change_flag = p_manual_price_change_flag, -- <Manual Price Override FPJ>
279 -- <SVC_NOTIFICATIONS FPJ START>
280 -- Reset the "Amount Billed notification sent" flag to NULL if there
281 -- is an Amount change.
282 svc_amount_notif_sent =
283 decode ( x_amount_db, amount, svc_amount_notif_sent, NULL),
284 -- Reset the "Assignment Completion notification sent" flag to NULL
285 -- if there is an Assignment End Date change.
286 svc_completion_notif_sent =
287 decode ( x_expiration_date, expiration_date, svc_completion_notif_sent, NULL) , -- <SVC_NOTIFICATIONS FPJ END>
288 tax_attribute_update_code = NVL(tax_attribute_update_code, l_tax_attribute_update_code), --<eTax Integration R12>
289 ip_category_id = p_ip_category_id -- Bug 7577670
290 WHERE rowid = X_rowid;
291
292 if (SQL%NOTFOUND) then
293 Raise NO_DATA_FOUND;
294 end if;
295
296 END Update_Row;
297
298 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
299 BEGIN
300 DELETE FROM PO_LINES
301 WHERE rowid = X_Rowid;
302
303 if (SQL%NOTFOUND) then
304 Raise NO_DATA_FOUND;
305 end if;
306 END Delete_Row;
307
308 END PO_LINES_PKG_SUD;