1 PACKAGE BODY PO_REQUISITION_LINES_PKG3 as
2 /* $Header: POXRIL4B.pls 120.0.12020000.2 2013/02/10 14:15:37 vegajula ship $ */
3 -- Read the profile option that enables/disables the debug log
4 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 c_log_head CONSTANT VARCHAR2(35) := 'po.plsql.PO_REQUISITION_LINES_PKG3.';
7
8 PROCEDURE Lock3_Row(X_Rowid VARCHAR2,
9 X_Rate_Type VARCHAR2,
10 X_Rate_Date DATE,
11 X_Rate NUMBER,
12 X_Currency_Unit_Price NUMBER,
13 X_Currency_Amount NUMBER, -- <SERVICES FPJ>
14 X_Suggested_Vendor_Name VARCHAR2,
15 X_Suggested_Vendor_Location VARCHAR2,
16 X_Suggested_Vendor_Contact VARCHAR2,
17 X_Suggested_Vendor_Phone VARCHAR2,
18 X_Sugg_Vendor_Product_Code VARCHAR2,
19 X_Un_Number_Id NUMBER,
20 X_Hazard_Class_Id NUMBER,
21 X_Must_Use_Sugg_Vendor_Flag VARCHAR2,
22 X_Reference_Num VARCHAR2,
23 X_On_Rfq_Flag VARCHAR2,
24 X_Urgent_Flag VARCHAR2,
25 X_Cancel_Flag VARCHAR2,
26 X_Source_Organization_Id NUMBER,
27 X_Source_Subinventory VARCHAR2,
28 X_Destination_Type_Code VARCHAR2,
29 X_Destination_Organization_Id NUMBER,
30 X_Destination_Subinventory VARCHAR2,
31 X_Quantity_Cancelled NUMBER,
32 X_Cancel_Date DATE,
33 X_Cancel_Reason VARCHAR2,
34 X_Closed_Code VARCHAR2,
35 X_Agent_Return_Note VARCHAR2,
36 X_Changed_After_Research_Flag VARCHAR2,
37 X_Vendor_Id NUMBER,
38 X_Vendor_Site_Id NUMBER,
39 X_Vendor_Contact_Id NUMBER
40 ) IS
41
42 CURSOR C IS
43 SELECT *
44 FROM PO_REQUISITION_LINES
45 WHERE rowid = X_Rowid
46 FOR UPDATE of Requisition_Line_Id NOWAIT;
47
48 Recinfo C%ROWTYPE;
49 -- For debug purposes
50 l_api_name CONSTANT VARCHAR2(30) := 'Lock3_Row';
51 BEGIN
52 OPEN C;
53 FETCH C INTO Recinfo;
54 if (C%NOTFOUND) then
55 CLOSE C;
56 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
57 APP_EXCEPTION.Raise_Exception;
58 end if;
59 CLOSE C;
60 /* Bug 2679432 - Added the trunc function for the rate_date column
61 as the date coming from REQIMPORT could have
62 time component and the lock_row would fail */
63 if (
64 ( (TRIM(Recinfo.rate_type) = TRIM(X_Rate_Type))
65 OR ( (TRIM(Recinfo.rate_type) IS NULL)
66 AND (TRIM(X_Rate_Type) IS NULL)))
67 AND ( (trunc(Recinfo.rate_date) = trunc(X_Rate_Date))
68 OR ( (Recinfo.rate_date IS NULL)
69 AND (X_Rate_Date IS NULL)))
70 AND ( (Recinfo.rate = X_Rate)
71 OR ( (Recinfo.rate IS NULL)
72 AND (X_Rate IS NULL)))
73 AND ( (Recinfo.currency_unit_price = X_Currency_Unit_Price)
74 OR ( (Recinfo.currency_unit_price IS NULL)
75 AND (X_Currency_Unit_Price IS NULL)))
76 -- <SERVICES FPJ START>
77 AND ( (Recinfo.currency_amount = X_Currency_Amount)
78 OR ( (Recinfo.currency_amount IS NULL)
79 AND (X_Currency_Amount IS NULL)))
80 -- <SERVICES FPJ END>
81 AND ( (TRIM(Recinfo.suggested_vendor_name) = TRIM(X_Suggested_Vendor_Name))
82 OR ( (TRIM(Recinfo.suggested_vendor_name) IS NULL)
83 AND (TRIM(X_Suggested_Vendor_Name) IS NULL)))
84 AND ( (TRIM(Recinfo.suggested_vendor_location) = TRIM(X_Suggested_Vendor_Location))
85 OR ( (TRIM(Recinfo.suggested_vendor_location) IS NULL)
86 AND (TRIM(X_Suggested_Vendor_Location) IS NULL)))
87 AND ( (TRIM(Recinfo.suggested_vendor_contact) = TRIM(X_Suggested_Vendor_Contact))
88 OR ( (TRIM(Recinfo.suggested_vendor_contact) IS NULL)
89 AND (TRIM(X_Suggested_Vendor_Contact) IS NULL)))
90 AND ( (TRIM(Recinfo.suggested_vendor_phone) = TRIM(X_Suggested_Vendor_Phone))
91 OR ( (TRIM(Recinfo.suggested_vendor_phone) IS NULL)
92 AND (TRIM(X_Suggested_Vendor_Phone) IS NULL)))
93 AND ( (TRIM(Recinfo.suggested_vendor_product_code) = TRIM(X_Sugg_Vendor_Product_Code))
94 OR ( (TRIM(Recinfo.suggested_vendor_product_code) IS NULL)
95 AND (TRIM(X_Sugg_Vendor_Product_Code) IS NULL)))
96 AND ( (Recinfo.un_number_id = X_Un_Number_Id)
97 OR ( (Recinfo.un_number_id IS NULL)
98 AND (X_Un_Number_Id IS NULL)))
99 AND ( (Recinfo.hazard_class_id = X_Hazard_Class_Id)
100 OR ( (Recinfo.hazard_class_id IS NULL)
101 AND (X_Hazard_Class_Id IS NULL)))
102 AND ( (TRIM(Recinfo.must_use_sugg_vendor_flag) = TRIM(X_Must_Use_Sugg_Vendor_Flag))
103 OR ( (TRIM(Recinfo.must_use_sugg_vendor_flag) IS NULL)
104 AND (TRIM(X_Must_Use_Sugg_Vendor_Flag) IS NULL)))
105 AND ( (TRIM(Recinfo.reference_num) = TRIM(X_Reference_Num))
106 OR ( (TRIM(Recinfo.reference_num) IS NULL)
107 AND (TRIM(X_Reference_Num) IS NULL)))
108 AND ( (TRIM(Recinfo.on_rfq_flag) = TRIM(X_On_Rfq_Flag))
109 OR ( (TRIM(Recinfo.on_rfq_flag) IS NULL)
110 AND (TRIM(X_On_Rfq_Flag) IS NULL)))
111 AND ( (TRIM(Recinfo.urgent_flag) = TRIM(X_Urgent_Flag))
112 OR ( (TRIM(Recinfo.urgent_flag) IS NULL)
113 AND (TRIM(X_Urgent_Flag) IS NULL)))
114 AND ( (TRIM(Recinfo.cancel_flag) = TRIM(X_Cancel_Flag))
115 OR ( (TRIM(Recinfo.cancel_flag) IS NULL)
116 AND (TRIM(X_Cancel_Flag) IS NULL)))
117 AND ( (Recinfo.source_organization_id = X_Source_Organization_Id)
118 OR ( (Recinfo.source_organization_id IS NULL)
119 AND (X_Source_Organization_Id IS NULL)))
120 AND ( (TRIM(Recinfo.source_subinventory) = TRIM(X_Source_Subinventory))
121 OR ( (TRIM(Recinfo.source_subinventory) IS NULL)
122 AND (TRIM(X_Source_Subinventory) IS NULL)))
123 AND ( (TRIM(Recinfo.destination_type_code) = TRIM(X_Destination_Type_Code))
124 OR ( (TRIM(Recinfo.destination_type_code) IS NULL)
125 AND (TRIM(X_Destination_Type_Code) IS NULL)))
126 AND ( (Recinfo.destination_organization_id = X_Destination_Organization_Id)
127 OR ( (Recinfo.destination_organization_id IS NULL)
128 AND (X_Destination_Organization_Id IS NULL)))
129 AND ( (TRIM(Recinfo.destination_subinventory) = TRIM(X_Destination_Subinventory))
130 OR ( (TRIM(Recinfo.destination_subinventory) IS NULL)
131 AND (TRIM(X_Destination_Subinventory) IS NULL)))
132 AND ( (Recinfo.quantity_cancelled = X_Quantity_Cancelled)
133 OR ( (Recinfo.quantity_cancelled IS NULL)
134 AND (X_Quantity_Cancelled IS NULL)))
135 AND ( (Recinfo.cancel_date = X_Cancel_Date)
136 OR ( (Recinfo.cancel_date IS NULL)
137 AND (X_Cancel_Date IS NULL)))
138 AND ( (TRIM(Recinfo.cancel_reason) = TRIM(X_Cancel_Reason))
139 OR ( (TRIM(Recinfo.cancel_reason) IS NULL)
140 AND (TRIM(X_Cancel_Reason) IS NULL)))
141 AND ( (TRIM(Recinfo.closed_code) = TRIM(X_Closed_Code))
142 OR ( (TRIM(Recinfo.closed_code) IS NULL)
143 AND (TRIM(X_Closed_Code) IS NULL)))
144 AND ( (TRIM(Recinfo.agent_return_note) = TRIM(X_Agent_Return_Note))
145 OR ( (TRIM(Recinfo.agent_return_note) IS NULL)
146 AND (TRIM(X_Agent_Return_Note) IS NULL)))
147 AND ( (TRIM(Recinfo.changed_after_research_flag) = TRIM(X_Changed_After_Research_Flag))
148 OR ( (TRIM(Recinfo.changed_after_research_flag) IS NULL)
149 AND (TRIM(X_Changed_After_Research_Flag) IS NULL)))
150 AND ( (Recinfo.vendor_id = X_Vendor_Id)
151 OR ( (Recinfo.vendor_id IS NULL)
152 AND (X_Vendor_Id IS NULL)))
153 AND ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
154 OR ( (Recinfo.vendor_site_id IS NULL)
155 AND (X_Vendor_Site_Id IS NULL)))
156 AND ( (Recinfo.vendor_contact_id = X_Vendor_Contact_Id)
157 OR ( (Recinfo.vendor_contact_id IS NULL)
158 AND (X_Vendor_Contact_Id IS NULL)))
159 ) then
160 return;
161 else
162 IF (g_fnd_debug = 'Y') THEN
163 IF (NVL(TRIM(X_Rate_Type),'-999') <> NVL( TRIM(Recinfo.rate_type),'-999')) THEN
164 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form rate_type '||X_Rate_Type ||' Database rate_type '||Recinfo.rate_type);
165 END IF;
166 IF (X_Rate_Date <> Recinfo.rate_date ) THEN
167 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form rate_date '||X_Rate_Date ||' Database rate_date '||Recinfo.rate_date);
168 END IF;
169 IF (NVL(X_Rate,-999) <> NVL(Recinfo.rate,-999)) THEN
170 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form rate'||X_Rate ||' Database rate '|| Recinfo.rate);
171 END IF;
172 IF (NVL(X_Currency_Unit_Price,-999) <> NVL(Recinfo.currency_unit_price,-999)) THEN
173 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form currency_unit_price'||X_Currency_Unit_Price ||' Database currency_unit_price '|| Recinfo.currency_unit_price);
174 END IF;
175 IF (NVL(X_Currency_Amount,-999) <> NVL(Recinfo.currency_amount,-999)) THEN
176 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form currency_amount'||X_Currency_Amount ||' Database currency_amount '|| Recinfo.currency_amount);
177 END IF;
178 IF (NVL(TRIM(X_Suggested_Vendor_Name),'-999') <> NVL( TRIM(Recinfo.suggested_vendor_name),'-999')) THEN
179 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_name '||X_Suggested_Vendor_Name ||' Database suggested_vendor_name '||Recinfo.suggested_vendor_name);
180 END IF;
181 IF (NVL(TRIM(X_Suggested_Vendor_Location),'-999') <> NVL( TRIM(Recinfo.suggested_vendor_location),'-999')) THEN
182 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_location '||X_Suggested_Vendor_Location ||' Database suggested_vendor_location '||Recinfo.suggested_vendor_location);
183 END IF;
184 IF (NVL(TRIM(X_Suggested_Vendor_Contact),'-999') <> NVL( TRIM(Recinfo.suggested_vendor_contact),'-999')) THEN
185 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_contact '||X_Suggested_Vendor_Contact ||' Database suggested_vendor_contact '||Recinfo.suggested_vendor_contact);
186 END IF;
187 IF (NVL(TRIM(X_Suggested_Vendor_Phone),'-999') <> NVL( TRIM(Recinfo.suggested_vendor_phone),'-999')) THEN
188 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form suggested_vendor_phone '||X_Suggested_Vendor_Phone ||' Database suggested_vendor_phone '||Recinfo.suggested_vendor_phone);
189 END IF;
190 IF (NVL(TRIM(X_Sugg_Vendor_Product_Code),'-999') <> NVL( TRIM(Recinfo.suggested_vendor_product_code),'-999')) THEN
191 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form sugg_vendor_product_code '||X_Sugg_Vendor_Product_Code ||' Database sugg_vendor_product_code '||Recinfo.suggested_vendor_product_code);
192 END IF;
193 IF (NVL(X_Un_Number_Id,-999) <> NVL(Recinfo.un_number_id,-999)) THEN
194 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form un_number_id'||X_Un_Number_Id ||' Database un_number_id '|| Recinfo.un_number_id);
195 END IF;
196 IF (NVL(X_Hazard_Class_Id,-999) <> NVL(Recinfo.hazard_class_id,-999)) THEN
197 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form hazard_class_id'||X_Hazard_Class_Id ||' Database hazard_class_id '|| Recinfo.hazard_class_id);
198 END IF;
199 IF (NVL(TRIM(X_Must_Use_Sugg_Vendor_Flag),'-999') <> NVL( TRIM(Recinfo.must_use_sugg_vendor_flag),'-999')) THEN
200 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form must_use_sugg_vendor_flag '||X_Must_Use_Sugg_Vendor_Flag ||' Database must_use_sugg_vendor_flag '||Recinfo.must_use_sugg_vendor_flag);
201 END IF;
202 IF (NVL(TRIM(X_Reference_Num),'-999') <> NVL( TRIM(Recinfo.reference_num),'-999')) THEN
203 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form reference_num '||X_Reference_Num ||' Database reference_num '||Recinfo.reference_num);
204 END IF;
205 IF (NVL(TRIM(X_On_Rfq_Flag),'-999') <> NVL( TRIM(Recinfo.on_rfq_flag),'-999')) THEN
206 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form on_rfq_flag '||X_On_Rfq_Flag ||' Database on_rfq_flag '||Recinfo.on_rfq_flag);
207 END IF;
208 IF (NVL(TRIM(X_Urgent_Flag),'-999') <> NVL( TRIM(Recinfo.urgent_flag),'-999')) THEN
209 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form urgent_flag '||X_Urgent_Flag ||' Database urgent_flag '||Recinfo.urgent_flag);
210 END IF;
211 IF (NVL(TRIM(X_Cancel_Flag),'-999') <> NVL( TRIM(Recinfo.cancel_flag),'-999')) THEN
212 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form cancel_flag '||X_Cancel_Flag ||' Database cancel_flag '||Recinfo.cancel_flag);
213 END IF;
214 IF (NVL(X_Source_Organization_Id,-999) <> NVL(Recinfo.source_organization_id,-999)) THEN
215 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form source_organization_id'||X_Source_Organization_Id ||' Database source_organization_id '|| Recinfo.source_organization_id);
216 END IF;
217 IF (NVL(TRIM(X_Source_Subinventory),'-999') <> NVL( TRIM(Recinfo.source_subinventory),'-999')) THEN
218 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form source_subinventory '||X_Source_Subinventory ||' Database source_subinventory '||Recinfo.source_subinventory);
219 END IF;
220 IF (NVL(TRIM(X_Destination_Type_Code),'-999') <> NVL( TRIM(Recinfo.destination_type_code),'-999')) THEN
221 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form destination_type_code '||X_Destination_Type_Code ||' Database destination_type_code '||Recinfo.destination_type_code);
222 END IF;
223 IF (NVL(X_Destination_Organization_Id,-999) <> NVL(Recinfo.destination_organization_id,-999)) THEN
224 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form destination_organization_id'||X_Destination_Organization_Id ||' Database destination_organization_id '|| Recinfo.destination_organization_id);
225 END IF;
226 IF (NVL(TRIM(X_Destination_Subinventory),'-999') <> NVL( TRIM(Recinfo.destination_subinventory),'-999')) THEN
227 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form destination_subinventory '||X_Destination_Subinventory ||' Database destination_subinventory '||Recinfo.destination_subinventory);
228 END IF;
229 IF (NVL(X_Quantity_Cancelled,-999) <> NVL(Recinfo.quantity_cancelled,-999)) THEN
230 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form quantity_cancelled'||X_Quantity_Cancelled ||' Database quantity_cancelled '|| Recinfo.quantity_cancelled);
231 END IF;
232 IF (X_Cancel_Date <> Recinfo.cancel_date ) THEN
233 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form cancel_date '||X_Cancel_Date ||' Database cancel_date '||Recinfo.cancel_date);
234 END IF;
235 IF (NVL(TRIM(X_Cancel_Reason),'-999') <> NVL( TRIM(Recinfo.cancel_reason),'-999')) THEN
236 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form cancel_reason '||X_Cancel_Reason ||' Database cancel_reason '||Recinfo.cancel_reason);
237 END IF;
238 IF (NVL(TRIM(X_Closed_Code),'-999') <> NVL( TRIM(Recinfo.closed_code),'-999')) THEN
239 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form closed_code '||X_Closed_Code ||' Database closed_code '||Recinfo.closed_code);
240 END IF;
241 IF (NVL(TRIM(X_Agent_Return_Note),'-999') <> NVL( TRIM(Recinfo.agent_return_note),'-999')) THEN
242 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form agent_return_note '||X_Agent_Return_Note ||' Database agent_return_note '||Recinfo.agent_return_note);
243 END IF;
244 IF (NVL(TRIM(X_Changed_After_Research_Flag),'-999') <> NVL( TRIM(Recinfo.changed_after_research_flag),'-999')) THEN
245 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form changed_after_research_flag '||X_Changed_After_Research_Flag ||' Database changed_after_research_flag '||Recinfo.changed_after_research_flag);
246 END IF;
247 IF (NVL(X_Vendor_Id,-999) <> NVL(Recinfo.vendor_id,-999)) THEN
248 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form vendor_id'||X_Vendor_Id ||' Database vendor_id '|| Recinfo.vendor_id);
249 END IF;
250 IF (NVL(X_Vendor_Site_Id,-999) <> NVL(Recinfo.vendor_site_id,-999)) THEN
251 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form vendor_site_id'||X_Vendor_Site_Id ||' Database vendor_site_id '|| Recinfo.vendor_site_id);
252 END IF;
253 IF (NVL(X_Vendor_Contact_Id,-999) <> NVL(Recinfo.vendor_contact_id,-999)) THEN
254 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form vendor_contact_id'||X_Vendor_Contact_Id ||' Database vendor_contact_id '|| Recinfo.vendor_contact_id);
255 END IF;
256 END IF;
257 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
258 APP_EXCEPTION.RAISE_EXCEPTION;
259 end if;
260 END Lock3_Row;
261
262 END PO_REQUISITION_LINES_PKG3;
[Home] [Help]