[Home] [Help]
PACKAGE BODY: APPS.CS_INVENTORY_TXNS
Source
1 package body cs_inventory_txns as
2 /* $Header: csdrtxnb.pls 115.5 99/07/16 08:57:07 porting ship $ */
3 /*******************************************************************************
4 --
5 --Private global variables and functions
6 --
7 *******************************************************************************/
8
9 procedure get_who_info
10 (
11 p_login_id out number,
12 p_user_id out number,
13 p_sys_dt out date
14 ) is
15 begin
16 p_login_id := FND_GLOBAL.Login_Id;
17 p_user_id := FND_GLOBAL.User_Id;
18 p_sys_dt := sysdate;
19 end get_who_info;
20 --
21 --
22 /*******************************************************************************
23 --
24 --Public functions
25 --
26 *******************************************************************************/
27 -- PROCEDURE to process all the interface records :
28 --
29 PROCEDURE Get_Default_Values (p_mfg_org_Id OUT NUMBER,
30 p_Error_Flag OUT VARCHAR2,
31 P_Error_Profile OUT VARCHAR2,
32 p_transaction_Type OUT NUMBER,
33 p_service_item_flex_code OUT VARCHAR2,
34 P_Subinventory_Code IN OUT VARCHAR2,
35 P_Source_Id IN OUT NUMBER) IS
36
37 p_transaction_action_id NUMBER;
38
39 BEGIN
40
41 p_mfg_org_id :=
42 FND_Profile.Value_Specific('CS_FIELD_SERVICE_ORGANIZATION');
43
44 IF (NVL(p_mfg_org_id,0) = 0) THEN
45 p_error_flag := 'Y';
46 p_error_profile := 'CS_FIELD_SERVICE_ORGANIZATION';
47 return;
48 END IF;
49
50 /* Get Transaction Type profile. If it is not set, error out **/
51
52 p_transaction_type :=
53 FND_Profile.Value_Specific('CS_FIELD_SERVICE_TRANSACTION_TYPE');
54 IF (NVL(p_transaction_type,0) = 0) THEN
55 p_error_flag := 'Y';
56 p_error_profile := 'CS_FIELD_SERVICE_TRANSACTION_TYPE';
57 return;
58 END IF;
59
60 -- Added by Gmahajan 18th Nov'98
61 -- If transaction_action_id = 1 for the transaction type defined,
62 -- specifies Issue from Inventory. Only when
63 -- transaction_action_id = 1, the user should be allowed to inventory transactions
64
65 SELECT transaction_action_id
66 INTO p_transaction_action_id
67 FROM mtl_transaction_types
68 WHERE transaction_type_id = to_number(fnd_profile.value_specific('CS_FIELD_SERVICE_TRANSACTION_TYPE'));
69
70 IF p_transaction_action_id <> 1 THEN
71 p_error_flag := 'Y';
72 return;
73 END IF;
74
75 p_service_item_flex_code := FND_PROFILE.Value('SERVICE_ITEM_FLEX_CODE');
76
77 /* Get Profile value of subinventory. If it exists,
78 copy it as default value
79 to the transactions block and set the corresponding
80 locator properties. **/
81
82 IF (p_Subinventory_Code IS NULL) THEN
83 p_subinventory_Code :=
84 FND_Profile.Value_Specific('CS_FIELD_SERVICE_SUBINVENTORY');
85 END IF;
86
87 p_error_flag := 'N';
88
89 End Get_Default_Values;
90
91 /*****************************************************************************/
92
93 Procedure Insert_Mtl_Interface_Records (
94 P_Detail_Txn_Id IN NUMBER,
95 P_Estimate_Id IN NUMBER,
96 P_Estimate_Detail_Id IN NUMBER,
97 P_Organization_Id IN NUMBER,
98 P_Inventory_Item_Id IN NUMBER,
99 P_Uom_Code IN VARCHAR2,
100 P_Quantity IN NUMBER,
101 P_Revision IN VARCHAR2,
102 p_serial_number IN VARCHAR2,
103 p_lot_number IN NUMBER,
104 P_Subinventory_Code IN VARCHAR2,
105 P_Locator_Id IN NUMBER,
106 p_transaction_type_id IN NUMBER) IS
107
108 CURSOR Mtl_Txn_Details IS
109 SELECT Transaction_Source_Type_Id,
110 Transaction_Action_Id
111 FROM MTL_TRANSACTION_TYPES
112 WHERE TRANSACTION_TYPE_ID = p_transaction_Type_Id;
113
114 CURSOR MTl_Item_Account IS
115 SELECT Cost_Of_Sales_Account
116 FROM MTL_SYSTEM_ITEMS
117 WHERE Inventory_Item_Id = P_Inventory_Item_Id
118 AND Organization_Id = P_Organization_Id;
119
120 l_transaction_interface_id NUMBER;
121 l_quantity NUMBER;
122 l_user_id NUMBER;
123 l_login_id NUMBER;
124 l_serial_number_ref NUMBER;
125 l_transaction_source_type_id NUMBER;
126 l_transaction_action_id NUMBER;
127 l_cost_of_sales_account NUMBER;
128 l_sys_dt DATE;
129
130 BEGIN
131 get_who_info(l_login_id,l_user_id,l_sys_dt);
132
133 SELECT Mtl_Material_Transactions_S.NextVal
134 INTO l_transaction_interface_id
135 FROM DUAL;
136
137 /** Since it is an issue, Inventory expects a negative
138 quantity. **/
139 l_quantity := p_quantity * -1;
140
141 OPEN Mtl_Txn_Details;
142 FETCH Mtl_Txn_Details
143 INTO l_transaction_source_type_id,
144 l_transaction_action_id;
145
146 CLOSE Mtl_txn_Details;
147
148 OPEN Mtl_Item_Account;
149 FETCH Mtl_Item_Account
150 INTO l_cost_of_sales_account;
151 CLOSE MTL_Item_Account;
152
153 Insert into MTL_TRANSACTIONS_INTERFACE (
154 Transaction_Interface_Id,
155 Source_Code, Organization_Id,
156 Source_Line_Id, Source_Header_Id,
157 Process_flag, Transaction_Mode,
158 Lock_Flag, Last_Update_Date,
159 Last_Updated_By, Creation_Date,
160 Created_By, Last_Update_Login,
161 Inventory_Item_Id, Revision,
162 Transaction_Quantity,
163 Transaction_UOM, Transaction_Date,
164 Subinventory_Code, Locator_Id,
165 Transaction_Source_Type_Id, Transaction_Action_Id,
166 Transaction_Type_Id,
167 Transaction_Reference, Distribution_Account_Id)
168 Values (
169 l_transaction_interface_Id,
170 'SERVICE', p_organization_id,
171 p_Estimate_Detail_Id, p_estimate_id,
172 1, 3,
173 2, sysdate,
174 l_user_id, sysdate,
175 l_user_id, l_login_id,
176 p_inventory_Item_id, p_revision,
177 l_quantity, p_uom_code,
178 sysdate,
179 p_subinventory_code, p_locator_id,
180 l_transaction_source_type_id,
181 l_transaction_action_id, p_transaction_type_id,
182 p_estimate_id, l_cost_of_sales_account);
183
184 /** If the item is under lot control, enter a record in
185 MTL_TRANSACTION_LOTS_INTERFACE table.
186 01/23/98 - skolhatk **/
187
188 IF (p_serial_number IS NOT NULL) THEN
189 l_serial_number_ref := l_transaction_interface_id;
190 ELSE
191 l_serial_number_ref := NULL;
192 END IF;
193
194 IF (p_lot_number IS NOT NULL) THEN
195 Insert into MTL_Transaction_Lots_Interface(
196 Transaction_Interface_Id, Source_Code,
197 Source_Line_Id, Lot_Number,
198 Transaction_Quantity,
199 Serial_Transaction_Temp_Id, Last_Update_Date,
200 Last_Updated_By, Creation_Date,
201 Created_By, Last_Update_Login,
202 Process_Flag)
203 Values(
204 l_transaction_interface_Id, 'SERVICE',
205 p_Estimate_Detail_Id, p_lot_number,
206 -1,
207 l_serial_number_ref, sysdate,
208 l_user_id, sysdate,
209 l_user_id, l_login_id,
210 1);
211 END IF;
212
213 /** If the item is serialized, enter a record in
214 MTL_SERIAL_NUMBERS_INTERFACE table.
215 11/21/97 - skolhatk **/
216
217 IF (p_serial_Number IS NOT NULL) THEN
218 Insert into Mtl_Serial_Numbers_Interface (
219 Transaction_Interface_Id, Source_Code,
220 Source_Line_Id, Last_Update_Date,
221 Last_Updated_By, Creation_Date,
222 Created_By, Last_Update_Login,
223 Fm_Serial_Number, Process_Flag)
224 Values(
225 l_transaction_interface_Id, 'SERVICE',
226 p_Estimate_Detail_Id, sysdate,
227 l_user_id, sysdate,
228 l_user_id, l_login_id,
229 p_serial_number, 1);
230 END IF;
231
232
233 Update Cs_Est_Details_Mtl_Txns
234 Set Interface_to_Inventory_Flag = 'Y'
235 Where Detail_Transaction_Id = p_Detail_Txn_Id;
236
237 End Insert_Mtl_Interface_Records ;
238
239 end cs_inventory_txns;