[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_CYCLE_COUNT
Source
1 PACKAGE BODY INV_MWB_CYCLE_COUNT AS
2 /* $Header: INVMWBCB.pls 120.1 2005/06/18 01:07:07 appldev $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_CYCLE_COUNT';
5
6 procedure create_cc_details (
7 X_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
8 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
9 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
10 p_organization_id NUMBER,
11 p_cycle_count_header_id NUMBER,
12 p_abc_class_id NUMBER,
13 p_schedule_date DATE,
14 p_inventory_item_id NUMBER,
15 p_revision VARCHAR2,
16 p_subinventory_code VARCHAR2,
17 p_locator_id NUMBER,
18 p_lot_number VARCHAR2,
19 p_serial_number VARCHAR2,
20 p_userid VARCHAR2
21 ) is
22
23 l_cycle_count_schedule_id NUMBER;
24 l_rowid1 VARCHAR2(42);
25 l_rowid2 VARCHAR2(42);
26 l_creation_date DATE := sysdate;
27 l_created_by NUMBER := to_number(p_userid);
28 l_last_updated_by NUMBER := to_number(p_userid);
29 l_last_update_date DATE := sysdate;
30 l_cc_items_ct NUMBER;
31 l_subs_ct NUMBER;
32
33 BEGIN
34 -- Initialize API return status to success
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36
37 SAVEPOINT MWB_CREATE_CC;
38
39 select count(*)
40 into l_cc_items_ct
41 from mtl_cycle_count_items
42 where inventory_item_id = p_inventory_item_id
43 and cycle_count_header_id = p_cycle_count_header_id
44 and abc_class_id = p_abc_class_id;
45
46 select count(*)
47 into l_subs_ct
48 from mtl_cc_subinventories
49 where cycle_count_header_id = p_cycle_count_header_id
50 and subinventory = p_subinventory_code;
51
52 select mtl_cc_schedule_requests_s.nextval
53 into l_cycle_count_schedule_id
54 from dual;
55
56 IF l_subs_ct = 0 then
57 insert into mtl_cc_subinventories
58 ( cycle_count_header_id ,
59 subinventory ,
60 last_update_date ,
61 last_updated_by ,
62 creation_date ,
63 created_by
64 )
65 values
66 ( p_Cycle_Count_Header_Id ,
67 p_subinventory_code ,
68 l_last_update_date ,
69 l_last_updated_by ,
70 l_creation_date ,
71 l_created_by
72 );
73 END IF;
74
75 IF l_cc_items_ct = 0 then
76 MTL_CYCLE_COUNT_ITEMS_PKG.Insert_Row(
77 X_Rowid => l_RowId2,
78 X_Cycle_Count_Header_Id => p_cycle_count_header_id,
79 X_Inventory_Item_Id => p_Inventory_Item_Id,
80 X_Last_Update_Date => l_last_update_date,
81 X_Last_Updated_By => l_last_updated_by,
82 X_Creation_Date => l_creation_date,
83 X_Created_By => l_created_by,
84 X_Last_Update_Login => NULL,
85 X_Abc_Class_Id => p_Abc_Class_Id,
86 X_Item_Last_Schedule_Date => NULL,
87 X_Schedule_Order => NULL,
88 X_Approval_Tolerance_Positive => NULL,
89 X_Approval_Tolerance_Negative => NULL,
90 X_Control_Group_Flag => 2
91 );
92 end if;
93
94 insert into mtl_cc_schedule_requests (
95 cycle_count_schedule_id ,
96 last_update_date ,
97 last_updated_by ,
98 creation_date ,
99 created_by ,
100 cycle_count_header_id ,
101 request_source_type ,
102 schedule_date ,
103 schedule_status ,
104 subinventory ,
105 inventory_item_id ,
106 revision ,
107 lot_number ,
108 serial_number ,
109 locator_id )
110 values (
111 l_cycle_count_schedule_id,
112 l_last_update_date ,
113 l_last_updated_by ,
114 l_creation_date ,
115 l_created_by ,
116 p_cycle_count_header_id ,
117 2 ,
118 p_schedule_date ,
119 1 ,
120 p_subinventory_code ,
121 p_inventory_item_id ,
122 p_revision ,
123 p_lot_number ,
124 p_serial_number ,
125 p_locator_id
126 );
127
128 EXCEPTION
129
130 WHEN fnd_api.g_exc_error THEN
131
132 x_return_status := fnd_api.g_ret_sts_error;
133 ROLLBACK TO mwb_create_cc;
134 fnd_msg_pub.count_and_get
135 ( p_count => x_msg_count,
136 p_data => x_msg_data
137 );
138 WHEN fnd_api.g_exc_unexpected_error THEN
139 x_return_status := fnd_api.g_ret_sts_unexp_error ;
140 ROLLBACK TO mwb_create_cc;
141 fnd_msg_pub.count_and_get
142 ( p_count => x_msg_count,
143 p_data => x_msg_data
144 );
145 WHEN OTHERS THEN
146 x_return_status := fnd_api.g_ret_sts_unexp_error;
147 ROLLBACK TO mwb_create_cc;
148 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
149 THEN
150 fnd_msg_pub.add_exc_msg
151 ( g_pkg_name
152 , 'mwb_create_cc_details'
153 );
154 END IF;
155 fnd_msg_pub.count_and_get
156 ( p_count => x_msg_count,
157 p_data => x_msg_data
158 );
159
160 END create_cc_details;
161
162
163 procedure commit_data is
164 begin
165 commit;
166 end commit_data;
167 END INV_MWB_CYCLE_COUNT;