[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_SEC_INV_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_SEC_INV_EVENT_PKG
2 /* $Header: csmemsb.pls 120.1 2005/07/25 00:12:26 trajasek noship $*/
3 AS
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_SEC_INVENTORIES';
16 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INV_ACC';
17 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INV_ACC_S';
18 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SEC_INVENTORIES');
20 g_pk1_name1 CONSTANT VARCHAR2(30) := 'SECONDARY_INVENTORY_NAME';
21 g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
22 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_INVENTORIES';
23
24 PROCEDURE insert_mtl_sec_inventory( p_user_id NUMBER
25 , p_subinventory_code VARCHAR2
26 , p_organization_id NUMBER)
27 IS
28 l_sqlerrno varchar2(20);
29 l_sqlerrmsg varchar2(2000);
30
31 BEGIN
32 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory ',
33 'CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
34
35 CSM_ACC_PKG.Insert_Acc
36 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
37 ,P_ACC_TABLE_NAME => g_acc_table_name1
38 ,P_SEQ_NAME => g_acc_sequence_name1
39 ,P_PK1_NAME => g_pk1_name1
40 ,P_PK1_CHAR_VALUE => p_subinventory_code
41 ,P_PK2_NAME => g_pk2_name1
42 ,P_PK2_NUM_VALUE => p_organization_id
43 ,P_USER_ID => p_user_id
44 );
45
46 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory ',
47 'CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
48
49 EXCEPTION
50 WHEN others THEN
51 l_sqlerrno := to_char(SQLCODE);
52 l_sqlerrmsg := substr(SQLERRM, 1,2000);
53 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory: ' || l_sqlerrno || ':' || l_sqlerrmsg,
54 'CSM_MTL_SEC_INV_EVENT_PKG.insert_mtl_sec_inventory', FND_LOG.LEVEL_EXCEPTION);
55
56 END insert_mtl_sec_inventory;
57
58 PROCEDURE update_mtl_sec_inventory( p_user_id NUMBER
59 , p_subinventory_code VARCHAR2
60 , p_organization_id NUMBER)
61 IS
62 l_sqlerrno varchar2(20);
63 l_sqlerrmsg varchar2(2000);
64 l_access_id number;
65
66 BEGIN
67 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory ',
68 'CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
69
70 l_access_id := CSM_ACC_PKG.Get_Acc_Id
71 ( P_ACC_TABLE_NAME => g_acc_table_name1
72 ,P_PK1_NAME => g_pk1_name1
73 ,P_PK1_CHAR_VALUE => p_subinventory_code
74 ,P_PK2_NAME => g_pk2_name1
75 ,P_PK2_NUM_VALUE => p_organization_id
76 ,P_USER_ID => p_user_id
77 );
78
79 CSM_ACC_PKG.Update_Acc
80 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
81 ,P_ACC_TABLE_NAME => g_acc_table_name1
82 ,P_ACCESS_ID => l_access_id
83 ,P_USER_ID => p_user_id
84 );
85
86 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory ',
87 'CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
88
89 EXCEPTION
90 WHEN others THEN
91 l_sqlerrno := to_char(SQLCODE);
92 l_sqlerrmsg := substr(SQLERRM, 1,2000);
93 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory: ' || l_sqlerrno || ':' || l_sqlerrmsg,
94 'CSM_MTL_SEC_INV_EVENT_PKG.update_mtl_sec_inventory', FND_LOG.LEVEL_EXCEPTION);
95
96 END update_mtl_sec_inventory;
97
98 PROCEDURE delete_mtl_sec_inventory( p_user_id NUMBER
99 , p_subinventory_code VARCHAR2
100 , p_organization_id NUMBER)
101 IS
102 l_sqlerrno varchar2(20);
103 l_sqlerrmsg varchar2(2000);
104
105 BEGIN
106 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory ',
107 'CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
108
109 CSM_ACC_PKG.Delete_Acc
110 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
111 ,P_ACC_TABLE_NAME => g_acc_table_name1
112 ,P_PK1_NAME => g_pk1_name1
113 ,P_PK1_CHAR_VALUE => p_subinventory_code
114 ,P_PK2_NAME => g_pk2_name1
115 ,P_PK2_NUM_VALUE => p_organization_id
116 ,P_USER_ID => p_user_id
117 );
118
119 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory ',
120 'CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory',FND_LOG.LEVEL_PROCEDURE);
121
122 EXCEPTION
123 WHEN others THEN
124 l_sqlerrno := to_char(SQLCODE);
125 l_sqlerrmsg := substr(SQLERRM, 1,2000);
126 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory: ' || l_sqlerrno || ':' || l_sqlerrmsg,
127 'CSM_MTL_SEC_INV_EVENT_PKG.delete_mtl_sec_inventory', FND_LOG.LEVEL_EXCEPTION);
128
129 END delete_mtl_sec_inventory;
130
131 PROCEDURE refresh_acc(p_status OUT NOCOPY VARCHAR2,
132 p_message OUT NOCOPY VARCHAR2)
133 IS
134 PRAGMA AUTONOMOUS_TRANSACTION;
135 l_sqlerrno varchar2(20);
136 l_sqlerrmsg varchar2(2000);
137 l_mark_dirty boolean;
138 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
139 l_run_date date;
140
141 CURSOR l_last_run_date_csr
142 IS
143 SELECT nvl(last_run_date, (sysdate - 365*50))
144 FROM jtm_con_request_data
145 WHERE package_name = 'CSM_MTL_SEC_INV_EVENT_PKG'
146 AND procedure_name = 'REFRESH_ACC';
147
148 CURSOR l_upd_mtl_sec_inv_csr(p_last_upd_date IN date)
149 IS
150 SELECT acc.access_id, acc.user_id
151 FROM mtl_secondary_inventories msi,
152 csm_mtl_sec_inv_acc acc
153 WHERE msi.secondary_inventory_name = acc.secondary_inventory_name
154 AND msi.organization_id = acc.organization_id
155 AND msi.last_update_date >= p_last_upd_date;
156
157 BEGIN
158 CSM_UTIL_PKG.LOG('Entering CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc ',
159 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_PROCEDURE);
160
161 -- data program is run
162 l_run_date := SYSDATE;
163
164 -- get last conc program update date
165 OPEN l_last_run_date_csr;
166 FETCH l_last_run_date_csr INTO l_prog_update_date;
167 CLOSE l_last_run_date_csr;
168
169 -- get all updated records from the backend table and post them to olite
170 FOR r_upd_mtl_sec_inv_rec IN l_upd_mtl_sec_inv_csr(l_prog_update_date) LOOP
171 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForUser(g_pub_item,
172 r_upd_mtl_sec_inv_rec.access_id,
173 r_upd_mtl_sec_inv_rec.user_id,
174 ASG_DOWNLOAD.UPD,
175 l_run_date);
176 END LOOP;
177
178 -- set the program update date in jtm_con_request_data to sysdate
179 UPDATE jtm_con_request_data
180 SET last_run_date = l_run_date
181 WHERE package_name = 'CSM_MTL_SEC_INV_EVENT_PKG'
182 AND procedure_name = 'REFRESH_ACC';
183
184 COMMIT;
185
186 p_status := 'FINE';
187 p_message := 'CSM_MTL_SEC_INV_EVENT_PKG.Refresh_Acc Executed successfully';
188
189 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc ',
190 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_PROCEDURE);
191
192 EXCEPTION
193 WHEN others THEN
194 l_sqlerrno := to_char(SQLCODE);
195 l_sqlerrmsg := substr(SQLERRM, 1,2000);
196 p_status := 'ERROR';
197 p_message := 'Error in CSM_MTL_SEC_INV_EVENT_PKG.Refresh_Acc:' || l_sqlerrno || ':' || l_sqlerrmsg;
198 ROLLBACK;
199 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc: ' || l_sqlerrno || ':' || l_sqlerrmsg,
200 'CSM_MTL_SEC_INV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EXCEPTION);
201 END refresh_acc;
202
203 END CSM_MTL_SEC_INV_EVENT_PKG;