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