1 PACKAGE BODY FA_CUA_EXT_TRANSFERS_PKG AS
2 /* $Header: FACETFRMB.pls 120.5.12010000.1 2008/07/28 13:10:51 appldev ship $ */
3 --
4 --
5 PROCEDURE Lock_Row(
6 X_Rowid VARCHAR2,
7 X_Mass_External_Transfer_Id NUMBER DEFAULT NULL,
8 X_From_Asset_Id NUMBER DEFAULT NULL,
9 X_Transaction_Status VARCHAR2 DEFAULT NULL,
10 X_Book_Type_Code VARCHAR2 DEFAULT NULL,
11 X_Transaction_Type VARCHAR2 DEFAULT NULL,
12 X_Batch_Name VARCHAR2 DEFAULT NULL,
13 X_Description VARCHAR2 DEFAULT NULL,
14 X_Transaction_Date_Entered DATE DEFAULT NULL,
15 X_External_Reference_Num VARCHAR2 DEFAULT NULL,
16 X_To_Asset_Id NUMBER DEFAULT NULL,
17 X_To_Location_Id NUMBER DEFAULT NULL,
18 X_To_GL_CCID NUMBER DEFAULT NULL,
19 X_To_Employee_Id NUMBER DEFAULT NULL,
20 X_Transfer_Units NUMBER DEFAULT NULL,
21 X_Source_Line_Id NUMBER DEFAULT NULL,
22 X_Transfer_Amount NUMBER DEFAULT NULL,
23 X_From_Distribution_Id NUMBER DEFAULT NULL
24 ) IS
25
26 CURSOR C_EXT_TRF IS
27 SELECT BATCH_NAME,
28 MASS_EXTERNAL_TRANSFER_ID,
29 EXTERNAL_REFERENCE_NUM,
30 TRANSACTION_TYPE,
31 FROM_ASSET_ID,
32 TO_ASSET_ID,
33 BOOK_TYPE_CODE,
34 TRANSACTION_STATUS,
35 TRANSACTION_DATE_ENTERED,
36 FROM_DISTRIBUTION_ID,
37 TO_LOCATION_ID,
38 TO_GL_CCID,
39 TO_EMPLOYEE_ID,
40 DESCRIPTION,
41 TRANSFER_UNITS,
42 TRANSFER_AMOUNT,
43 SOURCE_LINE_ID,
44 CREATED_BY,
45 CREATION_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_DATE,
48 LAST_UPDATE_LOGIN
49 FROM fa_mass_external_transfers
50 WHERE rowid = X_Rowid
51 FOR UPDATE OF mass_external_transfer_id NOWAIT;
52
53 recinfo C_EXT_TRF%ROWTYPE;
54
55 BEGIN
56
57 OPEN C_EXT_TRF;
58 FETCH C_EXT_TRF INTO recinfo;
59 if (C_EXT_TRF%NOTFOUND) then
60 CLOSE C_EXT_TRF;
61 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
62 APP_EXCEPTION.Raise_Exception;
63 end if;
64 CLOSE C_EXT_TRF;
65
66 if(((Recinfo.mass_external_transfer_id = X_mass_external_transfer_id)
67 OR ((Recinfo.mass_external_transfer_id is NULL)
68 AND (X_mass_external_transfer_id is NULL)))
69 AND ((Recinfo.from_asset_id = X_from_asset_id)
70 OR ((Recinfo.from_asset_id is NULL)
71 AND (X_from_asset_id is NULL)))
72 AND ((Recinfo.transaction_status = X_transaction_status)
73 OR ((Recinfo.transaction_status IS NULL)
74 AND (X_transaction_status IS NULL)))
75 AND ((Recinfo.book_type_code = X_book_type_code)
76 OR ((Recinfo.book_type_code IS NULL)
77 AND (X_book_type_code IS NULL)))
78 AND ((Recinfo.transaction_type = X_transaction_type)
79 OR ((Recinfo.transaction_type IS NULL)
80 AND (X_transaction_type IS NULL)))
81 -- Bug:5745560
82 /*AND ((Recinfo.batch_name = X_batch_name)
83 OR ((Recinfo.batch_name IS NULL)
84 AND (X_batch_name IS NULL)))
85 AND ((Recinfo.description = X_description)
86 OR ((Recinfo.description IS NULL)
87 AND (X_description IS NULL)))
88 AND ((Recinfo.transaction_date_entered = X_transaction_date_entered)
89 OR ((Recinfo.transaction_date_entered IS NULL)
90 AND (X_transaction_type IS NULL)))*/
91 AND ((Recinfo.batch_name = X_batch_name)
92 OR ((Recinfo.batch_name IS NULL)
93 AND (X_batch_name IS NULL)))
94 AND ((Recinfo.description = X_description)
95 OR ((Recinfo.description IS NULL)
96 AND (X_description IS NULL)))
97 AND ((Recinfo.transaction_date_entered = X_transaction_date_entered)
98 OR ((Recinfo.transaction_date_entered IS NULL)
99 AND (X_transaction_date_entered IS NULL)))
100 AND ((Recinfo.external_reference_num = X_external_reference_num)
101 OR ((Recinfo.external_reference_num IS NULL)
102 AND (X_external_reference_num IS NULL)))
103 AND ((Recinfo.to_asset_id = X_to_asset_id)
104 OR ((Recinfo.to_asset_id IS NULL)
105 AND (X_to_asset_id IS NULL)))
106 AND ((Recinfo.to_location_id = X_to_location_id)
107 OR ((Recinfo.to_location_id IS NULL)
108 AND (X_to_location_id IS NULL)))
109 AND ((Recinfo.to_gl_ccid = X_to_gl_ccid)
110 OR ((Recinfo.to_gl_ccid IS NULL)
111 AND (X_to_gl_ccid IS NULL)))
112 AND ((Recinfo.to_employee_id = X_to_employee_id)
113 OR ((Recinfo.to_employee_id IS NULL)
114 AND (X_to_employee_id IS NULL)))
115 AND ((Recinfo.transfer_units = X_transfer_units)
116 OR ((Recinfo.transfer_units IS NULL)
117 AND (X_transfer_units IS NULL)))
118 AND ((Recinfo.source_line_id = X_source_line_id)
119 OR ((Recinfo.source_line_id IS NULL)
120 AND (X_source_line_id IS NULL)))
121 AND ((Recinfo.transfer_amount = X_transfer_amount)
122 OR ((Recinfo.transfer_amount IS NULL)
123 AND (X_transfer_amount IS NULL)))
124 AND ((Recinfo.from_distribution_id = X_from_distribution_id)
125 OR ((Recinfo.from_distribution_id IS NULL)
126 AND (X_from_distribution_id IS NULL))) )THEN
127 null;
128 else
129 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
130 APP_EXCEPTION.Raise_Exception;
131 end if;
132 END Lock_Row;
133 --
134 --
135 PROCEDURE Update_Row(
136 X_Rowid VARCHAR2,
137 X_Mass_External_Transfer_Id NUMBER DEFAULT NULL,
138 X_From_Asset_Id NUMBER DEFAULT NULL,
139 X_Transaction_Status VARCHAR2 DEFAULT NULL,
140 X_Book_Type_Code VARCHAR2 DEFAULT NULL,
141 X_Transaction_Type VARCHAR2 DEFAULT NULL,
142 X_Batch_Name VARCHAR2 DEFAULT NULL,
143 X_Description VARCHAR2 DEFAULT NULL,
144 X_Transaction_Date_Entered DATE DEFAULT NULL,
145 X_External_Reference_Num VARCHAR2 DEFAULT NULL,
146 X_To_Asset_Id NUMBER DEFAULT NULL,
147 X_To_Location_Id NUMBER DEFAULT NULL,
148 X_To_GL_CCID NUMBER DEFAULT NULL,
149 X_To_Employee_Id NUMBER DEFAULT NULL,
150 X_Transfer_Units NUMBER DEFAULT NULL,
151 X_Source_Line_Id NUMBER DEFAULT NULL,
152 X_Transfer_Amount NUMBER DEFAULT NULL,
153 X_From_Distribution_Id NUMBER DEFAULT NULL
154 ) IS
155
156 BEGIN
157 UPDATE fa_mass_external_transfers
158 SET
159 mass_external_transfer_id = X_Mass_External_Transfer_Id,
160 from_asset_id = X_From_Asset_Id,
161 transaction_status = X_Transaction_Status,
162 book_type_code = X_Book_Type_Code,
163 transaction_type = X_Transaction_Type,
164 batch_name = X_Batch_Name,
165 description = X_Description,
166 transaction_date_entered = X_Transaction_Date_Entered,
167 external_reference_num = X_External_Reference_Num,
168 to_asset_id = X_To_Asset_Id,
169 to_location_id = X_To_Location_Id,
170 to_gl_ccid = X_To_GL_CCID,
171 to_employee_id = X_To_Employee_Id,
172 transfer_units = X_Transfer_Units,
173 source_line_id = X_Source_Line_Id,
174 transfer_amount = X_Transfer_Amount,
175 from_distribution_id = X_From_Distribution_Id
176 WHERE rowid = X_Rowid;
177
178 if (SQL%NOTFOUND) then
179 RAISE NO_DATA_FOUND;
180 end if;
181 END Update_Row;
182 --
183 --
184 END FA_CUA_EXT_TRANSFERS_PKG;