[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_ITEM_LOC_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_ITEM_LOC_EVENT_PKG AS
2 /* $Header: csmemilb.pls 120.1 2005/07/25 00:11:28 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_ITEM_LOCATIONS';
15 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_ACC';
16 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_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_ITEM_LOCATIONS');
19 g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
20 g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
21 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
22
23 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
24 p_message OUT NOCOPY VARCHAR2)
25 IS
26 PRAGMA AUTONOMOUS_TRANSACTION;
27 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
28 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
29 l_dsql varchar2(2048);
30 l_pub_item VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
31 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
32 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
33 l_mark_dirty boolean;
34 l_all_omfs_palm_resource_list asg_download.user_list;
35 l_null_resource_list asg_download.user_list;
36 l_single_access_id_list asg_download.access_list;
37 --a null list
38 l_null_access_list asg_download.access_list;
39 l_run_date date;
40 l_sqlerrno varchar2(20);
41 l_sqlerrmsg varchar2(2000);
42
43 CURSOR l_last_run_date_csr
44 IS
45 SELECT nvl(last_run_date, (sysdate - 365*50))
46 FROM jtm_con_request_data
47 WHERE package_name = 'CSM_MTL_ITEM_LOC_EVENT_PKG'
48 AND procedure_name = 'REFRESH_ACC';
49
50 -- process inserts
51 CURSOR l_mtl_item_loc_ins_csr
52 IS
53 SELECT acc.user_id,
54 mil.inventory_location_id,
55 mil.organization_id
56 FROM csm_inv_loc_ass_acc acc,
57 csp_inv_loc_assignments cila,
58 mtl_item_locations mil
59 WHERE cila.csp_inv_loc_assignment_id = acc.csp_inv_loc_assignment_id
60 AND mil.subinventory_code = cila.subinventory_code
61 AND mil.organization_id = cila.organization_id
62 AND NOT EXISTS
63 (SELECT 1
64 FROM csm_mtl_item_locations_acc mil_acc
65 WHERE mil_acc.user_id = acc.user_id
66 AND mil_acc.inventory_location_id = mil.inventory_location_id
67 AND mil_acc.organization_id = mil.organization_id
68 );
69
70 --process updates
71 CURSOR l_mtl_item_loc_upd_csr(p_last_upd_date DATE)
72 IS
73 SELECT acc.access_id,
74 acc.user_id
75 FROM csm_mtl_item_locations_acc acc,
76 mtl_item_locations mil
77 WHERE acc.inventory_location_id = mil.inventory_location_id
78 AND acc.organization_id = mil.organization_id
79 AND mil.last_update_date > p_last_upd_date;
80
81 --process deletes
82 CURSOR l_mtl_item_loc_del_csr
83 IS
84 SELECT acc.access_id,
85 acc.inventory_location_id,
86 acc.organization_id,
87 acc.user_id
88 FROM csm_mtl_item_locations_acc acc
89 WHERE NOT EXISTS
90 (SELECT 1
91 FROM csm_inv_loc_ass_acc cila_acc,
92 csp_inv_loc_assignments cila,
93 mtl_item_locations mil
94 WHERE cila_acc.user_id = acc.user_id
95 AND cila_acc.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
96 AND mil.inventory_location_id = acc.inventory_location_id
97 AND mil.organization_id = acc.organization_id
98 AND mil.subinventory_code = cila.subinventory_code
99 AND mil.organization_id = cila.organization_id
100 );
101
102 BEGIN
103 -- data program is run
104 l_run_date := SYSDATE;
105
106 -- get last conc program update date
107 OPEN l_last_run_date_csr;
108 FETCH l_last_run_date_csr INTO l_prog_update_date;
109 CLOSE l_last_run_date_csr;
110
111 -- process deletes
112 FOR r_mtl_item_loc_del_rec IN l_mtl_item_loc_del_csr LOOP
113 CSM_ACC_PKG.Delete_acc
114 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
115 ,P_ACC_TABLE_NAME => g_acc_table_name1
116 ,P_PK1_NAME => g_pk1_name1
117 ,P_PK1_NUM_VALUE => r_mtl_item_loc_del_rec.inventory_location_id
118 ,P_PK2_NAME => g_pk2_name1
119 ,P_PK2_NUM_VALUE => r_mtl_item_loc_del_rec.organization_id
120 ,P_USER_ID => r_mtl_item_loc_del_rec.user_id
121 );
122 END LOOP;
123
124 -- process updates
125 FOR r_mtl_item_loc_upd_rec IN l_mtl_item_loc_upd_csr(l_prog_update_date) LOOP
126 CSM_ACC_PKG.Update_acc
127 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
128 ,P_ACC_TABLE_NAME => g_acc_table_name1
129 ,P_ACCESS_ID => r_mtl_item_loc_upd_rec.access_id
130 ,P_USER_ID => r_mtl_item_loc_upd_rec.user_id
131 );
132 END LOOP;
133
134 -- process inserts
135 FOR r_mtl_item_loc_ins_rec IN l_mtl_item_loc_ins_csr LOOP
136 CSM_ACC_PKG.Insert_Acc
137 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
138 ,P_ACC_TABLE_NAME => g_acc_table_name1
139 ,P_SEQ_NAME => g_acc_sequence_name1
140 ,P_PK1_NAME => g_pk1_name1
141 ,P_PK1_NUM_VALUE => r_mtl_item_loc_ins_rec.inventory_location_id
142 ,P_PK2_NAME => g_pk2_name1
143 ,P_PK2_NUM_VALUE => r_mtl_item_loc_ins_rec.organization_id
144 ,P_USER_ID => r_mtl_item_loc_ins_rec.user_id
145 );
146 END LOOP;
147
148 -- set the program update date in jtm_con_request_data to sysdate
149 UPDATE jtm_con_request_data
150 SET last_run_date = l_run_date
151 WHERE package_name = 'CSM_MTL_ITEM_LOC_EVENT_PKG'
152 AND procedure_name = 'REFRESH_ACC';
153
154 COMMIT;
155
156 p_status := 'FINE';
157 p_message := 'CSM_MTL_ITEM_LOC_EVENT_PKG.Refresh_Acc Executed successfully';
158
159 EXCEPTION
160 WHEN others THEN
161 l_sqlerrno := to_char(SQLCODE);
162 l_sqlerrmsg := substr(SQLERRM, 1,2000);
163 p_status := 'ERROR';
164 p_message := 'Error in CSM_MTL_ITEM_LOC_EVENT_PKG.Refresh_Acc: '|| l_sqlerrno || ':' || l_sqlerrmsg;
165 ROLLBACK;
166 CSM_UTIL_PKG.LOG('CSM_MTL_ITEM_LOC_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_MTL_ITEM_LOC_EVENT_PKG.Refresh_acc',FND_LOG.LEVEL_EXCEPTION);
167 END Refresh_Acc;
168
169 END CSM_MTL_ITEM_LOC_EVENT_PKG;