DBA Data[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;