1 PACKAGE BODY PO_REQUISITION_LINES_PKG3 as
2 /* $Header: POXRIL4B.pls 115.3 2003/07/30 21:52:40 anhuang ship $ */
3
4 PROCEDURE Lock3_Row(X_Rowid VARCHAR2,
5 X_Rate_Type VARCHAR2,
6 X_Rate_Date DATE,
7 X_Rate NUMBER,
8 X_Currency_Unit_Price NUMBER,
9 X_Currency_Amount NUMBER, -- <SERVICES FPJ>
10 X_Suggested_Vendor_Name VARCHAR2,
11 X_Suggested_Vendor_Location VARCHAR2,
12 X_Suggested_Vendor_Contact VARCHAR2,
13 X_Suggested_Vendor_Phone VARCHAR2,
14 X_Sugg_Vendor_Product_Code VARCHAR2,
15 X_Un_Number_Id NUMBER,
16 X_Hazard_Class_Id NUMBER,
17 X_Must_Use_Sugg_Vendor_Flag VARCHAR2,
18 X_Reference_Num VARCHAR2,
19 X_On_Rfq_Flag VARCHAR2,
20 X_Urgent_Flag VARCHAR2,
21 X_Cancel_Flag VARCHAR2,
22 X_Source_Organization_Id NUMBER,
23 X_Source_Subinventory VARCHAR2,
24 X_Destination_Type_Code VARCHAR2,
25 X_Destination_Organization_Id NUMBER,
26 X_Destination_Subinventory VARCHAR2,
27 X_Quantity_Cancelled NUMBER,
28 X_Cancel_Date DATE,
29 X_Cancel_Reason VARCHAR2,
30 X_Closed_Code VARCHAR2,
31 X_Agent_Return_Note VARCHAR2,
32 X_Changed_After_Research_Flag VARCHAR2,
33 X_Vendor_Id NUMBER,
34 X_Vendor_Site_Id NUMBER,
35 X_Vendor_Contact_Id NUMBER
36 ) IS
37
38 CURSOR C IS
39 SELECT *
40 FROM PO_REQUISITION_LINES
41 WHERE rowid = X_Rowid
42 FOR UPDATE of Requisition_Line_Id NOWAIT;
43
44 Recinfo C%ROWTYPE;
45 BEGIN
46 OPEN C;
47 FETCH C INTO Recinfo;
48 if (C%NOTFOUND) then
49 CLOSE C;
50 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
51 APP_EXCEPTION.Raise_Exception;
52 end if;
53 CLOSE C;
54 /* Bug 2679432 - Added the trunc function for the rate_date column
55 as the date coming from REQIMPORT could have
56 time component and the lock_row would fail */
57 if (
58 ( (Recinfo.rate_type = X_Rate_Type)
59 OR ( (Recinfo.rate_type IS NULL)
60 AND (X_Rate_Type IS NULL)))
61 AND ( (trunc(Recinfo.rate_date) = trunc(X_Rate_Date))
62 OR ( (Recinfo.rate_date IS NULL)
63 AND (X_Rate_Date IS NULL)))
64 AND ( (Recinfo.rate = X_Rate)
65 OR ( (Recinfo.rate IS NULL)
66 AND (X_Rate IS NULL)))
67 AND ( (Recinfo.currency_unit_price = X_Currency_Unit_Price)
68 OR ( (Recinfo.currency_unit_price IS NULL)
69 AND (X_Currency_Unit_Price IS NULL)))
70 -- <SERVICES FPJ START>
71 AND ( (Recinfo.currency_amount = X_Currency_Amount)
72 OR ( (Recinfo.currency_amount IS NULL)
73 AND (X_Currency_Amount IS NULL)))
74 -- <SERVICES FPJ END>
75 AND ( (Recinfo.suggested_vendor_name = X_Suggested_Vendor_Name)
76 OR ( (Recinfo.suggested_vendor_name IS NULL)
77 AND (X_Suggested_Vendor_Name IS NULL)))
78 AND ( (Recinfo.suggested_vendor_location = X_Suggested_Vendor_Location)
79 OR ( (Recinfo.suggested_vendor_location IS NULL)
80 AND (X_Suggested_Vendor_Location IS NULL)))
81 AND ( (Recinfo.suggested_vendor_contact = X_Suggested_Vendor_Contact)
82 OR ( (Recinfo.suggested_vendor_contact IS NULL)
83 AND (X_Suggested_Vendor_Contact IS NULL)))
84 AND ( (Recinfo.suggested_vendor_phone = X_Suggested_Vendor_Phone)
85 OR ( (Recinfo.suggested_vendor_phone IS NULL)
86 AND (X_Suggested_Vendor_Phone IS NULL)))
87 AND ( (Recinfo.suggested_vendor_product_code = X_Sugg_Vendor_Product_Code)
88 OR ( (Recinfo.suggested_vendor_product_code IS NULL)
89 AND (X_Sugg_Vendor_Product_Code IS NULL)))
90 AND ( (Recinfo.un_number_id = X_Un_Number_Id)
91 OR ( (Recinfo.un_number_id IS NULL)
92 AND (X_Un_Number_Id IS NULL)))
93 AND ( (Recinfo.hazard_class_id = X_Hazard_Class_Id)
94 OR ( (Recinfo.hazard_class_id IS NULL)
95 AND (X_Hazard_Class_Id IS NULL)))
96 AND ( (Recinfo.must_use_sugg_vendor_flag = X_Must_Use_Sugg_Vendor_Flag)
97 OR ( (Recinfo.must_use_sugg_vendor_flag IS NULL)
98 AND (X_Must_Use_Sugg_Vendor_Flag IS NULL)))
99 AND ( (Recinfo.reference_num = X_Reference_Num)
100 OR ( (Recinfo.reference_num IS NULL)
101 AND (X_Reference_Num IS NULL)))
102 AND ( (Recinfo.on_rfq_flag = X_On_Rfq_Flag)
103 OR ( (Recinfo.on_rfq_flag IS NULL)
104 AND (X_On_Rfq_Flag IS NULL)))
105 AND ( (Recinfo.urgent_flag = X_Urgent_Flag)
106 OR ( (Recinfo.urgent_flag IS NULL)
107 AND (X_Urgent_Flag IS NULL)))
108 AND ( (Recinfo.cancel_flag = X_Cancel_Flag)
109 OR ( (Recinfo.cancel_flag IS NULL)
110 AND (X_Cancel_Flag IS NULL)))
111 AND ( (Recinfo.source_organization_id = X_Source_Organization_Id)
112 OR ( (Recinfo.source_organization_id IS NULL)
113 AND (X_Source_Organization_Id IS NULL)))
114 AND ( (Recinfo.source_subinventory = X_Source_Subinventory)
115 OR ( (Recinfo.source_subinventory IS NULL)
116 AND (X_Source_Subinventory IS NULL)))
117 AND ( (Recinfo.destination_type_code = X_Destination_Type_Code)
118 OR ( (Recinfo.destination_type_code IS NULL)
119 AND (X_Destination_Type_Code IS NULL)))
120 AND ( (Recinfo.destination_organization_id = X_Destination_Organization_Id)
121 OR ( (Recinfo.destination_organization_id IS NULL)
122 AND (X_Destination_Organization_Id IS NULL)))
123 AND ( (Recinfo.destination_subinventory = X_Destination_Subinventory)
124 OR ( (Recinfo.destination_subinventory IS NULL)
125 AND (X_Destination_Subinventory IS NULL)))
126 AND ( (Recinfo.quantity_cancelled = X_Quantity_Cancelled)
127 OR ( (Recinfo.quantity_cancelled IS NULL)
128 AND (X_Quantity_Cancelled IS NULL)))
129 AND ( (Recinfo.cancel_date = X_Cancel_Date)
130 OR ( (Recinfo.cancel_date IS NULL)
131 AND (X_Cancel_Date IS NULL)))
132 AND ( (Recinfo.cancel_reason = X_Cancel_Reason)
133 OR ( (Recinfo.cancel_reason IS NULL)
134 AND (X_Cancel_Reason IS NULL)))
135 AND ( (Recinfo.closed_code = X_Closed_Code)
136 OR ( (Recinfo.closed_code IS NULL)
137 AND (X_Closed_Code IS NULL)))
138 AND ( (Recinfo.agent_return_note = X_Agent_Return_Note)
139 OR ( (Recinfo.agent_return_note IS NULL)
140 AND (X_Agent_Return_Note IS NULL)))
141 AND ( (Recinfo.changed_after_research_flag = X_Changed_After_Research_Flag)
142 OR ( (Recinfo.changed_after_research_flag IS NULL)
143 AND (X_Changed_After_Research_Flag IS NULL)))
144 AND ( (Recinfo.vendor_id = X_Vendor_Id)
145 OR ( (Recinfo.vendor_id IS NULL)
146 AND (X_Vendor_Id IS NULL)))
147 AND ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
148 OR ( (Recinfo.vendor_site_id IS NULL)
149 AND (X_Vendor_Site_Id IS NULL)))
150 AND ( (Recinfo.vendor_contact_id = X_Vendor_Contact_Id)
151 OR ( (Recinfo.vendor_contact_id IS NULL)
152 AND (X_Vendor_Contact_Id IS NULL)))
153 ) then
154 return;
155 else
156 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
157 APP_EXCEPTION.RAISE_EXCEPTION;
158 end if;
159 END Lock3_Row;
160
161 END PO_REQUISITION_LINES_PKG3;