[Home] [Help]
PACKAGE BODY: APPS.CSM_CSP_REQ_HEADERS_EVENT_PKG
Source
1 PACKAGE BODY CSM_CSP_REQ_HEADERS_EVENT_PKG
2 /* $Header: csmerhb.pls 120.1.12020000.2 2013/04/09 06:24:10 saradhak ship $*/
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) := 'CSP_REQUIREMENT_HEADERS';
16 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_REQ_HEADERS_ACC';
17 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_REQ_HEADERS_ACC_S';
18 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSM_REQ_HEADERS');
20 g_pk1_name1 CONSTANT VARCHAR2(30) := 'REQUIREMENT_HEADER_ID';
21
22 --Bug 16164491
23 CURSOR c_shipToHZPS(b_rhId NUMBER,b_user_id NUMBER)
24 IS
25 SELECT cas.party_site_id,hrl.org_id,b_user_id
26 FROM po_location_associations_all hrl, HZ_CUST_ACCT_SITES_ALL cas
27 ,CSP_REQUIREMENT_HEADERS rh
28 WHERE REQUIREMENT_HEADER_ID=b_rhId AND hrl.location_id=rh.ship_to_location_id
29 and hrl.address_id=cas.cust_acct_site_id AND hrl.org_id=cas.org_id
30 AND NOT EXISTS(SELECT 1 FROM CSM_PARTY_SITES_ACC acc
31 WHERE acc.party_site_id=cas.party_site_id
32 AND acc.user_id=b_user_id);
33
34 PROCEDURE CSP_REQ_HEADERS_MDIRTY_I(p_requirement_header_id IN NUMBER,
35 p_user_id IN NUMBER)
36 IS
37 l_sqlerrno VARCHAR2(20);
38 l_sqlerrmsg VARCHAR2(4000);
39 l_error_msg VARCHAR2(4000);
40 l_return_status VARCHAR2(2000);
41 l_ps_id NUMBER;
42 BEGIN
43 CSM_UTIL_PKG.LOG('Entering CSP_REQ_HEADERS_MDIRTY_I for requirement_header_id: ' || p_requirement_header_id,
44 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
45
46 CSM_ACC_PKG.Insert_Acc
47 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
48 ,P_ACC_TABLE_NAME => g_acc_table_name1
49 ,P_SEQ_NAME => g_acc_sequence_name1
50 ,P_PK1_NAME => g_pk1_name1
51 ,P_PK1_NUM_VALUE => p_requirement_header_id
52 ,P_USER_ID => p_user_id
53 );
54
55 --Bug 16164491
56 FOR rec IN c_shipToHZPS(p_requirement_header_id,p_user_id)
57 LOOP
58 l_ps_id:=rec.party_site_id;
59 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS(p_party_site_id=> rec.party_site_id,p_org_id =>rec.org_id ,p_user_id =>p_user_id);
60 END LOOP;
61
62 IF l_ps_id IS NOT NULL THEN
63 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_ps_id,
64 p_user_id => p_user_id,
65 p_flowtype => NULL,
66 p_error_msg => l_error_msg,
67 x_return_status => l_return_status);
68 END IF;
69
70 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_HEADERS_MDIRTY_I for requirement_header_id: ' || p_requirement_header_id,
71 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
72 EXCEPTION
73 WHEN OTHERS THEN
74 l_sqlerrno := to_char(SQLCODE);
75 l_sqlerrmsg := substr(SQLERRM, 1,2000);
76 l_error_msg := ' Exception in CSP_REQ_HEADERS_MDIRTY_I for requirement_header_id:'
77 || to_char(p_requirement_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
78 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
79 RAISE;
80 END CSP_REQ_HEADERS_MDIRTY_I;
81
82 PROCEDURE CSP_REQ_HEADERS_MDIRTY_D(p_requirement_header_id IN NUMBER,
83 p_user_id IN NUMBER)
84 IS
85 l_sqlerrno VARCHAR2(20);
86 l_sqlerrmsg VARCHAR2(4000);
87 l_error_msg VARCHAR2(4000);
88 l_return_status VARCHAR2(2000);
89
90 BEGIN
91 CSM_UTIL_PKG.LOG('Entering CSP_REQ_HEADERS_MDIRTY_D for requirement_header_id: ' || p_requirement_header_id,
92 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
93
94 CSM_ACC_PKG.Delete_Acc
95 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
96 ,P_ACC_TABLE_NAME => g_acc_table_name1
97 ,P_PK1_NAME => g_pk1_name1
98 ,P_PK1_NUM_VALUE => p_requirement_header_id
99 ,P_USER_ID => p_user_id
100 );
101
102 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_HEADERS_MDIRTY_D for requirement_header_id: ' || p_requirement_header_id,
103 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
104 EXCEPTION
105 WHEN OTHERS THEN
106 l_sqlerrno := to_char(SQLCODE);
107 l_sqlerrmsg := substr(SQLERRM, 1,2000);
108 l_error_msg := ' Exception in CSP_REQ_HEADERS_MDIRTY_D for requirement_header_id:'
109 || to_char(p_requirement_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
110 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
111 RAISE;
112 END CSP_REQ_HEADERS_MDIRTY_D;
113
114 PROCEDURE CSP_REQ_HEADERS_MDIRTY_U(p_requirement_header_id IN NUMBER,
115 p_user_id IN NUMBER)
116 IS
117 l_sqlerrno VARCHAR2(20);
118 l_sqlerrmsg VARCHAR2(4000);
119 l_error_msg VARCHAR2(4000);
120 l_return_status VARCHAR2(2000);
121 l_access_id NUMBER;
122 l_ps_id NUMBER;
123 BEGIN
124 CSM_UTIL_PKG.LOG('Entering CSP_REQ_HEADERS_MDIRTY_U for requirement_header_id: ' || p_requirement_header_id,
125 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
126
127 l_access_id := CSM_ACC_PKG.Get_Acc_Id
128 ( P_ACC_TABLE_NAME => g_acc_table_name1
129 ,P_PK1_NAME => g_pk1_name1
130 ,P_PK1_NUM_VALUE => p_requirement_header_id
131 ,P_USER_ID => p_user_id
132 );
133
134 IF l_access_id <> -1 THEN
135 CSM_ACC_PKG.Update_Acc
136 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
137 ,P_ACC_TABLE_NAME => g_acc_table_name1
138 ,P_ACCESS_ID => l_access_id
139 ,P_USER_ID => p_user_id
140 );
141 END IF;
142
143 --Bug 16164491
144 FOR rec IN c_shipToHZPS(p_requirement_header_id,p_user_id)
145 LOOP
146 l_ps_id:=rec.party_site_id;
147 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS(p_party_site_id=> rec.party_site_id,p_org_id =>rec.org_id ,p_user_id =>p_user_id);
148 END LOOP;
149
150
151 IF l_ps_id IS NOT NULL THEN
152 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_ps_id,
153 p_user_id => p_user_id,
154 p_flowtype => NULL,
155 p_error_msg => l_error_msg,
156 x_return_status => l_return_status);
157 END IF;
158
159 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_HEADERS_MDIRTY_U for requirement_header_id: ' || p_requirement_header_id,
160 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
161 EXCEPTION
162 WHEN OTHERS THEN
163 l_sqlerrno := to_char(SQLCODE);
164 l_sqlerrmsg := substr(SQLERRM, 1,2000);
165 l_error_msg := ' Exception in CSP_REQ_HEADERS_MDIRTY_U for requirement_header_id:'
166 || to_char(p_requirement_header_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
167 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_HEADERS_EVENT_PKG.CSP_REQ_HEADERS_MDIRTY_U',FND_LOG.LEVEL_EXCEPTION);
168 RAISE;
169 END CSP_REQ_HEADERS_MDIRTY_U;
170
171 END CSM_CSP_REQ_HEADERS_EVENT_PKG;