[Home] [Help]
PACKAGE BODY: APPS.PO_REQUISITION_LINES_PKG1
Source
1 PACKAGE BODY PO_REQUISITION_LINES_PKG1 as
2 /* $Header: POXRIL2B.pls 120.1.12020000.2 2013/02/10 15:54:30 vegajula ship $ */
3
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_PKG1.';
7 PROCEDURE Lock1_Row(X_Rowid VARCHAR2,
8 X_Requisition_Line_Id NUMBER,
9 X_Requisition_Header_Id NUMBER,
10 X_Line_Num NUMBER,
11 X_Line_Type_Id NUMBER,
12 X_Category_Id NUMBER,
13 X_Item_Description VARCHAR2,
14 X_Unit_Meas_Lookup_Code VARCHAR2,
15 X_Unit_Price NUMBER,
16 X_Quantity NUMBER,
17 X_Amount NUMBER, -- <SERVICES FPJ>
18 X_Deliver_To_Location_Id NUMBER,
19 X_To_Person_Id NUMBER,
20 X_Source_Type_Code VARCHAR2,
21 X_Item_Id NUMBER,
22 X_Tax_Code_Id NUMBER,
23 X_Tax_User_Override_Flag VARCHAR2,
24 -- MC bug# 1548597.. Add 3 process related columns.unit_of_measure,quantity and grade.
25 -- start of 1548597
26 X_Secondary_Unit_Of_Measure VARCHAR2 default null,
27 X_Secondary_Quantity NUMBER default null,
28 X_Preferred_Grade VARCHAR2 default null
29 -- end of 1548597
30 ) IS
31
32 CURSOR C IS
33 SELECT *
34 FROM PO_REQUISITION_LINES
35 WHERE rowid = X_Rowid
36 FOR UPDATE of Requisition_Line_Id NOWAIT;
37
38 Recinfo C%ROWTYPE;
39 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
40 BEGIN
41 OPEN C;
42 FETCH C INTO Recinfo;
43 if (C%NOTFOUND) then
44 CLOSE C;
45 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
46 APP_EXCEPTION.Raise_Exception;
47 -- raise app_exception.record_lock_exception;
48 end if;
49 CLOSE C;
50 if (
51
52 (Recinfo.requisition_line_id = X_Requisition_Line_Id)
53 AND (Recinfo.requisition_header_id = X_Requisition_Header_Id)
54 AND (Recinfo.line_num = X_Line_Num)
55 AND (Recinfo.line_type_id = X_Line_Type_Id)
56 AND (Recinfo.category_id = X_Category_Id)
57 AND (TRIM(Recinfo.item_description) = TRIM(X_Item_Description))
58
59 -- <SERVICES FPJ START>
60 AND ( ( TRIM(recinfo.unit_meas_lookup_code) = TRIM(x_unit_meas_lookup_code ))
61 OR ( (TRIM( recinfo.unit_meas_lookup_code) IS NULL )
62 AND ( TRIM(x_unit_meas_lookup_code) IS NULL ) ) )
63
64 AND ( ( recinfo.unit_price = x_unit_price )
65 OR ( ( recinfo.unit_price IS NULL )
66 AND ( x_unit_price IS NULL ) ) )
67
68 AND ( ( recinfo.quantity = x_quantity )
69 OR ( ( recinfo.quantity IS NULL )
70 AND ( x_quantity IS NULL ) ) )
71
72 AND ( ( recinfo.amount = x_amount )
73 OR ( ( recinfo.amount IS NULL )
74 AND ( x_amount IS NULL ) ) )
75 -- <SERVICES FPJ END>
76
77 AND (Recinfo.deliver_to_location_id = X_Deliver_To_Location_Id)
78 AND (Recinfo.to_person_id = X_To_Person_Id)
79 AND (TRIM(Recinfo.source_type_code) = TRIM(X_Source_Type_Code))
80 AND ( (Recinfo.item_id = X_Item_Id)
81 OR ( (Recinfo.item_id IS NULL)
82 AND (X_Item_Id IS NULL)))
83 -- start of 1548597
84 AND ((TRIM(Recinfo.secondary_unit_of_measure) = TRIM(X_Secondary_Unit_Of_Measure))
85 OR ( (TRIM(Recinfo.secondary_unit_of_measure) IS NULL)
86 AND (TRIM(X_Secondary_Unit_Of_Measure) IS NULL)))
87 AND ((Recinfo.secondary_quantity = X_Secondary_Quantity)
88 OR ( (Recinfo.secondary_quantity IS NULL)
89 AND (X_Secondary_quantity IS NULL)))
90 AND ((TRIM(Recinfo.preferred_grade)= TRIM(X_Preferred_Grade))
91 OR ( (TRIM(Recinfo.preferred_grade) IS NULL)
92 AND (TRIM(X_Preferred_Grade) IS NULL)))
93 -- end of 1548597
94 ) then
95 return;
96 else
97 IF (g_fnd_debug = 'Y') THEN
98 IF (NVL(X_Requisition_Line_Id,-999) <> NVL(Recinfo.requisition_line_id,-999)) THEN
99 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form requisition_line_id'||X_Requisition_Line_Id ||' Database requisition_line_id '|| Recinfo.requisition_line_id);
100 END IF;
101 IF (NVL(X_Requisition_Header_Id,-999) <> NVL(Recinfo.requisition_header_id,-999)) THEN
102 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form requisition_header_id'||X_Requisition_Header_Id ||' Database requisition_header_id '|| Recinfo.requisition_header_id);
103 END IF;
104 IF (NVL(X_Line_Num,-999) <> NVL(Recinfo.line_num,-999)) THEN
105 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form line_num'||X_Line_Num ||' Database line_num '|| Recinfo.line_num);
106 END IF;
107 IF (NVL(X_Line_Type_Id,-999) <> NVL(Recinfo.line_type_id,-999)) THEN
108 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form line_type_id'||X_Line_Type_Id ||' Database line_type_id '|| Recinfo.line_type_id);
109 END IF;
110 IF (NVL(X_Category_Id,-999) <> NVL(Recinfo.category_id,-999)) THEN
111 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form category_id'||X_Category_Id ||' Database category_id '|| Recinfo.category_id);
112 END IF;
113 IF (NVL(TRIM(X_Item_Description),'-999') <> NVL( TRIM(Recinfo.item_description),'-999')) THEN
114 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form item_description '||X_Item_Description ||' Database item_description '||Recinfo.item_description);
115 END IF;
116 IF (NVL(TRIM(X_Unit_Meas_Lookup_Code),'-999') <> NVL( TRIM(Recinfo.unit_meas_lookup_code),'-999')) THEN
117 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form unit_meas_lookup_code '||X_Unit_Meas_Lookup_Code ||' Database unit_meas_lookup_code '||Recinfo.unit_meas_lookup_code);
118 END IF;
119 IF (NVL(X_Unit_Price,-999) <> NVL(Recinfo.unit_price,-999)) THEN
120 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form unit_price'||X_Unit_Price ||' Database unit_price '|| Recinfo.unit_price);
121 END IF;
122 IF (NVL(X_Quantity,-999) <> NVL(Recinfo.quantity,-999)) THEN
123 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form quantity'||X_Quantity ||' Database quantity '|| Recinfo.quantity);
124 END IF;
125 IF (NVL(X_Amount,-999) <> NVL(Recinfo.amount,-999)) THEN
126 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form amount'||X_Amount ||' Database amount '|| Recinfo.amount);
127 END IF;
128 IF (NVL(X_Deliver_To_Location_Id,-999) <> NVL(Recinfo.deliver_to_location_id,-999)) THEN
129 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form deliver_to_location_id'||X_Deliver_To_Location_Id ||' Database deliver_to_location_id '|| Recinfo.deliver_to_location_id);
130 END IF;
131 IF (NVL(X_To_Person_Id,-999) <> NVL(Recinfo.to_person_id,-999)) THEN
132 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form to_person_id'||X_To_Person_Id ||' Database to_person_id '|| Recinfo.to_person_id);
133 END IF;
134 IF (NVL(TRIM(X_Source_Type_Code),'-999') <> NVL( TRIM(Recinfo.source_type_code),'-999')) THEN
135 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form source_type_code '||X_Source_Type_Code ||' Database source_type_code '||Recinfo.source_type_code);
136 END IF;
137 IF (NVL(X_Item_Id,-999) <> NVL(Recinfo.item_id,-999)) THEN
138 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form item_id'||X_Item_Id ||' Database item_id '|| Recinfo.item_id);
139 END IF;
140
141 IF (NVL(TRIM(X_Secondary_Unit_Of_Measure),'-999') <> NVL( TRIM(Recinfo.secondary_unit_of_measure),'-999')) THEN
142 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form secondary_unit_of_measure '||X_Secondary_Unit_Of_Measure ||' Database secondary_unit_of_measure '||Recinfo.secondary_unit_of_measure);
143 END IF;
144 IF (NVL(X_Secondary_Quantity,-999) <> NVL(Recinfo.secondary_quantity,-999)) THEN
145 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form secondary_quantity'||X_Secondary_Quantity ||' Database secondary_quantity '|| Recinfo.secondary_quantity);
146 END IF;
147 IF (NVL(TRIM(X_Preferred_Grade),'-999') <> NVL( TRIM(Recinfo.preferred_grade),'-999')) THEN
148 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name,' Form preferred_grade '||X_Preferred_Grade ||' Database preferred_grade '||Recinfo.preferred_grade);
149 END IF;
150
151 END IF;
152 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
153 APP_EXCEPTION.RAISE_EXCEPTION;
154 --raise app_exception.record_lock_exception;
155 end if;
156 END Lock1_Row;
157
158
159
160 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
161 X_transferred_to_oe_flag OUT NOCOPY VARCHAR2) IS
162 x_progress VARCHAR2(3) := NULL;
163 x_requisition_header_id NUMBER;
164
165 BEGIN
166
167 x_progress := '010';
168
169 SELECT requisition_header_id
170 INTO x_requisition_header_id
171 FROM po_requisition_lines
172 WHERE rowid = X_rowid;
173
174 if (SQL%NOTFOUND) then
175 Raise NO_DATA_FOUND;
176 end if;
177
178 x_progress := '020';
179
180 DELETE FROM PO_REQUISITION_LINES
181 WHERE rowid = X_Rowid;
182
183 if (SQL%NOTFOUND) then
184 Raise NO_DATA_FOUND;
185 end if;
186
187
188 x_progress := '030';
189 --dbms_output.put_line ('Before call to update_transferred...');
190
191 po_req_lines_sv.update_transferred_to_oe_flag (X_requisition_header_id,
192 X_transferred_to_oe_flag);
193
194
195 EXCEPTION
196 WHEN OTHERS then
197 po_message_s.sql_error('DELETE_ROW',x_progress,sqlcode);
198
199 END Delete_Row;
200
201
202 END PO_REQUISITION_LINES_PKG1;