[Home] [Help]
PACKAGE BODY: APPS.CSP_PORTAL_EXCESS_PARTS_PVT
Source
1 PACKAGE BODY CSP_PORTAL_EXCESS_PARTS_PVT AS
2 /* $Header: cspppexb.pls 120.1.12000000.2 2007/07/26 00:26:27 hhaugeru ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_PORTAL_EXCESS_PARTS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(30):='cspppexb.pls';
6
7 PROCEDURE Portal_Excess_Parts
8 (errbuf OUT NOCOPY varchar2
9 ,retcode OUT NOCOPY number
10 ,p_resource_id IN NUMBER
11 ,P_resource_type IN VARCHAR2
12 ,p_condition_type IN VARCHAR2
13 ) is
14
15 l_api_version_number CONSTANT NUMBER := 1.0;
16 p_api_version CONSTANT NUMBER := 1.0;
17 l_api_name CONSTANT VARCHAR2(30) := 'Portal_Excess_Parts';
18 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
19 l_msg_count NUMBER;
20 l_msg_data VARCHAR2(2000);
21 l_sqlcode Number;
22 l_sqlerrm Varchar2(2000);
23
24 Cursor subinv(p_resource_id NUMBER, p_resource_type VARCHAR2, p_condition_type VARCHAR2)
25 is
26 select distinct organization_id,subinventory_code secondary_inventory_name,condition_type
27 from csp_rs_subinventories_v
28 where owner_resource_id = p_resource_id and
29 owner_resource_type = p_resource_type and
30 condition_type = p_condition_type and
31 (EFFECTIVE_DATE_END is null or trunc(EFFECTIVE_DATE_END) > trunc(sysdate));
32
33 Cursor planning(p_organization_id NUMBER,p_subinventory_code VARCHAR2,p_condition_type VARCHAR2)
34 is
35 select level_id from csp_planning_parameters
36 where organization_id = p_organization_id and
37 secondary_inventory = p_subinventory_code and
38 condition_type = p_condition_type;
39
40 Begin
41
42 SAVEPOINT Portal_Excess_Parts_PVT;
43 FND_MSG_PUB.initialize;
44
45 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
46 p_api_version,
47 l_api_name,
48 G_PKG_NAME)
49 THEN
50 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
51 END IF;
52
53 FOR SUBINVREC in subinv(p_resource_id, p_resource_type, p_condition_type)
54 Loop
55
56 CSP_EXCESS_PARTS_PVT.clean_up(p_organization_id => SUBINVREC.organization_id,
57 p_subinventory_code => SUBINVREC.secondary_inventory_name,
58 p_condition_type => SUBINVREC.condition_type);
59
60 FOR PLANNINGREC in planning(SUBINVREC.organization_id,SUBINVREC.secondary_inventory_name,SUBINVREC.condition_type)
61 Loop
62
63 CSP_EXCESS_PARTS_PVT.excess_parts
64 (errbuf
65 ,retcode
66 ,SUBINVREC.organization_id
67 ,PLANNINGREC.level_id
68 ,2
69 ,1
70 ,SUBINVREC.secondary_inventory_name
71 ,2
72 ,null
73 ,null
74 ,null
75 ,null
76 ,null
77 ,null
78 ,null
79 ,null
80 ,null
81 ,null
82 ,1
83 ,SYSDATE
84 ,0
85 ,SYSDATE
86 ,0
87 ,1318
88 ,2
89 ,Null
90 ,Null
91 ,1
92 ,1
93 ,1
94 ,1
95 ,1
96 ,1
97 ,2
98 ,3
99 ,1
100 ,2
101 ,2
102 );
103
104 IF nvl(retcode,0) <> 0 THEN
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107
108 End Loop;
109
110 End Loop;
111
112 COMMIT WORK;
113 l_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 FND_MSG_PUB.Count_And_Get
116 (p_count => l_msg_count,
117 p_data => l_msg_data
118 );
119
120 retcode := 0;
121
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 retcode := 2;
125 errbuf := l_Msg_Data;
126 l_return_status := FND_API.G_RET_STS_ERROR;
127 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
128 P_API_NAME => L_API_NAME
129 ,P_PKG_NAME => G_PKG_NAME
130 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
131 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
132 ,X_MSG_COUNT => l_MSG_COUNT
133 ,X_MSG_DATA => l_MSG_DATA
134 ,X_RETURN_STATUS => l_RETURN_STATUS);
135
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137 retcode := 2;
138 errbuf := l_Msg_Data;
139 l_return_status := FND_API.G_RET_STS_ERROR;
140 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
141 P_API_NAME => L_API_NAME
142 ,P_PKG_NAME => G_PKG_NAME
143 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
144 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
145 ,X_MSG_COUNT => l_MSG_COUNT
146 ,X_MSG_DATA => l_MSG_DATA
147 ,X_RETURN_STATUS => l_RETURN_STATUS);
148
149 WHEN OTHERS THEN
150
151 retcode := 2;
152 errbuf := l_Msg_Data;
153 l_sqlcode := SQLCODE;
154 l_sqlerrm := SQLERRM;
155 l_return_status := FND_API.G_RET_STS_ERROR;
156
157 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
158 P_API_NAME => L_API_NAME
159 ,P_PKG_NAME => G_PKG_NAME
160 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
161 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
162 ,P_SQLCODE => l_sqlcode
163 ,P_SQLERRM => l_sqlerrm
164 ,X_MSG_COUNT => l_MSG_COUNT
165 ,X_MSG_DATA => l_MSG_DATA
166 ,X_RETURN_STATUS => l_RETURN_STATUS);
167 End;
168
169 END;