[Home] [Help]
PACKAGE BODY: APPS.CSM_CNTR_RELATION_EVENT_PKG
Source
1 PACKAGE BODY CSM_CNTR_RELATION_EVENT_PKG AS
2 /* $Header: csmecrlb.pls 120.0 2005/11/23 06:35:32 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 g_cst_accnt_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_CNTR_RELATIONSHIPS_ACC';
14 g_cst_accnt_table_name CONSTANT VARCHAR2(30) := 'CSI_COUNTER_RELATIONSHIPS';
15 g_cst_accnt_seq_name CONSTANT VARCHAR2(30) := 'CSM_CNTR_RELATIONSHIPS_ACC_S' ;
16 g_cst_accnt_pk1_name CONSTANT VARCHAR2(30) := 'RELATIONSHIP_ID';
17 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_CNTR_RELATIONSHIPS';
18 g_accnt_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSM_CNTR_RELATIONSHIPS');
20
21
22 PROCEDURE COUNTER_RELATION_INS(p_counter_id NUMBER,
23 p_user_id NUMBER)
24 IS
25 --variable declarations
26 l_sqlerrno VARCHAR2(20);
27 l_sqlerrmsg varchar2(2000);
28 l_mark_dirty boolean;
29 l_relationship_id CSI_COUNTER_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
30 --Cursor Declarations
31 --Insert Cursor
32 CURSOR csr_cntr_rel_ins(l_counter_id NUMBER,l_user_id NUMBER)
33 IS
34 SELECT b.relationship_id
35 FROM csi_counter_relationships b
36 WHERE source_counter_id = l_counter_id
37 AND NOT EXISTS
38 (
39 SELECT 1
40 FROM CSM_CNTR_RELATIONSHIPS_ACC acc
41 WHERE acc.relationship_id = b.relationship_id
42 AND acc.user_id = l_user_id
43 );
44 BEGIN
45
46 CSM_UTIL_PKG.LOG('Entering CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_INS ',
47 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_INS', FND_LOG.LEVEL_PROCEDURE);
48 --process inserts
49 FOR l_cntr_rel_rec IN csr_cntr_rel_ins(p_counter_id,p_user_id)
50 LOOP
51
52 CSM_ACC_PKG.Insert_Acc
53 (P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
54 ,P_ACC_TABLE_NAME => g_cst_accnt_acc_table_name
55 ,P_SEQ_NAME => g_cst_accnt_seq_name
56 ,P_PK1_NAME => g_cst_accnt_pk1_name
57 ,P_PK1_NUM_VALUE => l_cntr_rel_rec.relationship_id
58 ,P_USER_ID => p_user_id
59 );
60 CSM_UTIL_PKG.LOG('Inserting counter relationship id ' || TO_CHAR(l_cntr_rel_rec.relationship_id) || ' for user '||TO_CHAR(p_user_id) , 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_INS',FND_LOG.LEVEL_PROCEDURE);
61
62 END LOOP;
63 COMMIT;
64 CSM_UTIL_PKG.LOG('Leaving CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_INS ',
65 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_INS',FND_LOG.LEVEL_PROCEDURE);
66 EXCEPTION
67 WHEN others THEN
68 l_sqlerrno := to_char(SQLCODE);
69 l_sqlerrmsg := substr(SQLERRM, 1,2000);
70 ROLLBACK;
71 CSM_UTIL_PKG.LOG('Exception in CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_INS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
72 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_INS',FND_LOG.LEVEL_EXCEPTION);
73 END COUNTER_RELATION_INS;
74
75 --Deleting relationships
76 PROCEDURE COUNTER_RELATION_DEL(p_counter_id NUMBER,
77 p_user_id NUMBER)
78 IS
79 --variable declarations
80 l_sqlerrno VARCHAR2(20);
81 l_sqlerrmsg varchar2(2000);
82 l_mark_dirty boolean;
83 l_relationship_id CSI_COUNTER_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
84 --Cursor Declarations
85 --Insert Cursor
86 --delete the relationship for the counter only if the counter access table does not contain
87 --the counter id that is corresponding to the mapping(source_counter_id)
88 CURSOR csr_cntr_rel_del(l_counter_id NUMBER,l_user_id NUMBER)
89 IS
90 SELECT acc.relationship_id
91 FROM csi_counter_relationships b,
92 CSM_CNTR_RELATIONSHIPS_ACC acc
93 WHERE b.source_counter_id = l_counter_id
94 AND acc.user_id = l_user_id
95 AND acc.relationship_id = b.relationship_id
96 AND NOT EXISTS
97 (
98 SELECT 1
99 FROM CSM_COUNTERS_ACC cacc
100 WHERE cacc.counter_id = l_counter_id
101 AND cacc.user_id = l_user_id
102 );
103 BEGIN
104
105 CSM_UTIL_PKG.LOG('Entering CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_DEL ',
106 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_DEL', FND_LOG.LEVEL_PROCEDURE);
107 --process inserts
108 FOR l_cntr_rel_rec IN csr_cntr_rel_del(p_counter_id,p_user_id)
109 LOOP
110
111 CSM_ACC_PKG.Delete_Acc
112 (P_PUBLICATION_ITEM_NAMES => g_accnt_pubi_name
113 ,P_ACC_TABLE_NAME => g_cst_accnt_acc_table_name
114 ,P_PK1_NAME => g_cst_accnt_pk1_name
115 ,P_PK1_NUM_VALUE => l_cntr_rel_rec.relationship_id
116 ,P_USER_ID => p_user_id
117 );
118 CSM_UTIL_PKG.LOG('Deleting counter relationship id ' || TO_CHAR(l_cntr_rel_rec.relationship_id) || ' for user '||TO_CHAR(p_user_id) , 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_DEL',FND_LOG.LEVEL_PROCEDURE);
119
120 END LOOP;
121 COMMIT;
122 CSM_UTIL_PKG.LOG('Leaving CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_DEL ',
123 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_DEL',FND_LOG.LEVEL_PROCEDURE);
124 EXCEPTION
125 WHEN others THEN
126 l_sqlerrno := to_char(SQLCODE);
127 l_sqlerrmsg := substr(SQLERRM, 1,2000);
128 ROLLBACK;
129 CSM_UTIL_PKG.LOG('Exception in CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_DEL: ' || l_sqlerrno || ':' || l_sqlerrmsg,
130 'CSM_CNTR_RELATIONSHIPS_EVENT_PKG.COUNTER_RELATION_DEL',FND_LOG.LEVEL_EXCEPTION);
131 END COUNTER_RELATION_DEL;
132
133 END CSM_CNTR_RELATION_EVENT_PKG;