[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_SEC_LOCATORS_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_SEC_LOCATORS_EVENT_PKG AS
2 /* $Header: csmemslb.pls 120.1 2005/07/25 00:13:47 trajasek noship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS';
15 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS_ACC';
16 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_SEC_LOCATORS_ACC_S';
17 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SECONDARY_LOCATORS');
19 g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
20 g_pk2_name1 CONSTANT VARCHAR2(30) := 'SECONDARY_LOCATOR';
21 g_pk3_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
22 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS';
23
24 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
25 p_message OUT NOCOPY VARCHAR2)
26 IS
27 PRAGMA AUTONOMOUS_TRANSACTION;
28 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
29 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
30 l_dsql varchar2(2048);
31 l_pub_item VARCHAR2(30) := 'CSM_MTL_SECONDARY_LOCATORS';
32 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
33 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
34 l_mark_dirty boolean;
35 l_all_omfs_palm_resource_list asg_download.user_list;
36 l_null_resource_list asg_download.user_list;
37 l_single_access_id_list asg_download.access_list;
38 --a null list
39 l_null_access_list asg_download.access_list;
40 l_run_date date;
41 l_sqlerrno varchar2(20);
42 l_sqlerrmsg varchar2(2000);
43
44 CURSOR l_last_run_date_csr
45 IS
46 SELECT nvl(last_run_date, (sysdate - 365*50))
47 FROM jtm_con_request_data
48 WHERE package_name = 'CSM_MTL_SEC_LOCATORS_EVENT_PKG'
49 AND procedure_name = 'REFRESH_ACC';
50
51 -- process inserts
52 CURSOR l_mtl_sec_loc_ins_csr
53 IS
54 SELECT msi_acc.user_id, msl.inventory_item_id,
55 msl.organization_id, msl.secondary_locator
56 FROM csm_mtl_system_items_acc msi_acc,
57 csm_mtl_item_locations_acc mil_acc,
58 mtl_secondary_locators msl
59 WHERE msl.inventory_item_id = msi_acc.inventory_item_id
60 AND msl.organization_id = msi_acc.organization_id
61 AND msl.secondary_locator = mil_acc.inventory_location_id
62 AND msl.organization_id = mil_acc.organization_id
63 AND msi_acc.user_id = mil_acc.user_id
64 AND NOT EXISTS
65 (SELECT 1
66 FROM csm_mtl_secondary_locators_acc acc
67 WHERE acc.user_id = msi_acc.user_id
68 AND acc.inventory_item_id = msi_acc.inventory_item_id
69 AND acc.organization_id = msi_acc.organization_id
70 AND acc.secondary_locator = msl.secondary_locator
71 );
72
73 -- process updates
74 CURSOR l_mtl_sec_loc_upd_csr(p_last_upd_date DATE)
75 IS
76 SELECT acc.access_id, acc.secondary_locator, acc.organization_id,
77 acc.inventory_item_id, acc.user_id
78 FROM csm_mtl_secondary_locators_acc acc,
79 mtl_secondary_locators msl
80 WHERE msl.secondary_locator = acc.secondary_locator
81 AND msl.organization_id = acc.organization_id
82 AND msl.inventory_item_id = acc.inventory_item_id
83 AND msl.last_update_date >= p_last_upd_date;
84
85 -- process deletes
86 CURSOR l_mtl_sec_loc_del_csr
87 IS
88 SELECT acc.user_id, acc.secondary_locator, acc.organization_id, acc.inventory_item_id
89 FROM csm_mtl_secondary_locators_acc acc
90 WHERE NOT EXISTS
91 (SELECT 1
92 FROM csm_mtl_system_items_acc msi_acc,
93 csm_mtl_item_locations_acc mil_acc,
94 mtl_secondary_locators msl
95 WHERE msl.inventory_item_id = msi_acc.inventory_item_id
96 AND msl.organization_id = msi_acc.organization_id
97 AND msl.secondary_locator = mil_acc.inventory_location_id
98 AND msl.organization_id = mil_acc.organization_id
99 AND msi_acc.user_id = mil_acc.user_id
100 AND acc.secondary_locator = msl.secondary_locator
101 AND acc.organization_id = msl.organization_id
102 AND acc.inventory_item_id = msl.inventory_item_id
103 AND acc.user_id = msi_acc.user_id
104 );
105
106 BEGIN
107 -- data program is run
108 l_run_date := SYSDATE;
109
110 -- get last conc program update date
111 OPEN l_last_run_date_csr;
112 FETCH l_last_run_date_csr INTO l_prog_update_date;
113 CLOSE l_last_run_date_csr;
114
115 -- process deletes
116 FOR r_mtl_sec_loc_del_rec IN l_mtl_sec_loc_del_csr LOOP
117 CSM_ACC_PKG.Delete_acc
118 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
119 ,P_ACC_TABLE_NAME => g_acc_table_name1
120 ,P_PK1_NAME => g_pk1_name1
121 ,P_PK1_NUM_VALUE => r_mtl_sec_loc_del_rec.inventory_item_id
122 ,P_PK2_NAME => g_pk2_name1
123 ,P_PK2_NUM_VALUE => r_mtl_sec_loc_del_rec.secondary_locator
124 ,P_PK3_NAME => g_pk3_name1
125 ,P_PK3_NUM_VALUE => r_mtl_sec_loc_del_rec.organization_id
126 ,P_USER_ID => r_mtl_sec_loc_del_rec.user_id
127 );
128 END LOOP;
129
130 -- process updates
131 FOR r_mtl_sec_loc_upd_rec IN l_mtl_sec_loc_upd_csr(l_prog_update_date) LOOP
132 CSM_ACC_PKG.Update_acc
133 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
134 ,P_ACC_TABLE_NAME => g_acc_table_name1
135 ,P_ACCESS_ID => r_mtl_sec_loc_upd_rec.access_id
136 ,P_USER_ID => r_mtl_sec_loc_upd_rec.user_id
137 );
138 END LOOP;
139
140 -- process inserts
141 FOR r_mtl_sec_loc_ins_rec IN l_mtl_sec_loc_ins_csr LOOP
142 CSM_ACC_PKG.Insert_Acc
143 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
144 ,P_ACC_TABLE_NAME => g_acc_table_name1
145 ,P_SEQ_NAME => g_acc_sequence_name1
146 ,P_PK1_NAME => g_pk1_name1
147 ,P_PK1_NUM_VALUE => r_mtl_sec_loc_ins_rec.inventory_item_id
148 ,P_PK2_NAME => g_pk2_name1
149 ,P_PK2_NUM_VALUE => r_mtl_sec_loc_ins_rec.secondary_locator
150 ,P_PK3_NAME => g_pk3_name1
151 ,P_PK3_NUM_VALUE => r_mtl_sec_loc_ins_rec.organization_id
152 ,P_USER_ID => r_mtl_sec_loc_ins_rec.user_id
153 );
154 END LOOP;
155
156 -- set the program update date in jtm_con_request_data to sysdate
157 UPDATE jtm_con_request_data
158 SET last_run_date = l_run_date
159 WHERE package_name = 'CSM_MTL_SEC_LOCATORS_EVENT_PKG'
160 AND procedure_name = 'REFRESH_ACC';
161
162 COMMIT;
163
164 p_status := 'FINE';
165 p_message := 'CSM_MTL_SEC_LOCATORS_EVENT_PKG.Refresh_Acc Executed successfully';
166
167 EXCEPTION
168 WHEN others THEN
169 l_sqlerrno := to_char(SQLCODE);
170 l_sqlerrmsg := substr(SQLERRM, 1,2000);
171 p_status := 'ERROR';
172 p_message := 'Error in CSM_MTL_SEC_LOCATORS_EVENT_PKG.Refresh_Acc:' || l_sqlerrno || ':' || l_sqlerrmsg;
173 ROLLBACK;
174 CSM_UTIL_PKG.LOG('CSM_MTL_SEC_LOCATORS_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_MTL_SEC_LOCATORS_EVENT_PKG.Refresh_acc',FND_LOG.LEVEL_EXCEPTION);
175 END Refresh_Acc;
176
177 END CSM_MTL_SEC_LOCATORS_EVENT_PKG;