[Home] [Help]
PACKAGE BODY: APPS.CSP_FAILURE_RATES_PVT
Source
1 PACKAGE BODY CSP_FAILURE_RATES_PVT as
2 /* $Header: cspvfrtb.pls 120.0 2005/05/25 11:37:49 appldev noship $ */
3 -- Start of Comments
4 -- Package name : CSP_FAILURE_RATES_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_FAILURE_RATES_PVT';
10 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvfrtb.pls';
11
12 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
13 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
14
15 PROCEDURE Failure_Rates (
16 retcode OUT NOCOPY NUMBER,
17 errbuf OUT NOCOPY VARCHAR2,
18 p_level_id IN VARCHAR2,
19 P_Api_Version_Number IN NUMBER
20 ) IS
21
22 l_history_size number;
23 l_level_id varchar2(2000);
24 l_product_id number;
25
26 cursor c_products is
27 select cfrb.history_periods * cfrb.period_size history_size,
28 cplp.level_id,
29 cpp.planning_parameters_id,
30 cpp.product_id,
31 cpp.current_population
32 from csp_product_populations cpp,
33 csp_planning_parameters cplp,
34 csp_forecast_rules_b cfrb
35 where cplp.planning_parameters_id = cpp.planning_parameters_id
36 and cplp.product_norm = 'Y'
37 and cplp.forecast_rule_id = cfrb.forecast_rule_id
38 and cplp.level_id like p_level_id||'%';
39
40 cursor c_debrief_lines is
41 select cdl.inventory_item_id,
42 sum(inv_convert.inv_um_convert(
43 cdl.inventory_item_id,
44 null,
45 cdl.quantity,
46 cdl.uom_code,
47 msib.primary_uom_code,
48 null,
49 null)) quantity
50 from csf_debrief_lines cdl,
51 csf_debrief_headers cdh,
52 jtf_task_assignments jta,
53 jtf_tasks_b jtb,
54 mtl_system_items_b msib,
55 cs_incidents_all cia,
56 csp_planning_parameters cplp
57 where cdl.issuing_sub_inventory_code is not null
58 and cdl.spare_update_status = 'SUCCEEDED'
59 and cdl.inventory_item_id = msib.inventory_item_id
60 and cdl.issuing_inventory_org_id = msib.organization_id
61 and cdh.debrief_header_id = cdl.debrief_header_id
62 and jta.task_assignment_id = cdh.task_assignment_id
63 and jtb.task_id = jta.task_id
64 and jtb.source_object_type_code = 'SR'
65 and cia.incident_id = jtb.source_object_id
66 and cia.inventory_item_id = l_product_id
67 and cdl.issuing_inventory_org_id = cplp.organization_id
68 and cdl.issuing_sub_inventory_code = nvl(cplp.secondary_inventory,cdl.issuing_sub_inventory_code)
69 and cplp.level_id like l_level_id||'%'
70 and cdl.service_date > sysdate - l_history_size
71 group by cdl.inventory_item_id;
72
73 l_api_name CONSTANT VARCHAR2(30) := 'csp_failure_rates_pvt';
74 l_api_version_number CONSTANT NUMBER := 1.0;
75 l_return_status_full VARCHAR2(1);
76 l_sqlcode NUMBER;
77 l_sqlerrm Varchar2(2000);
78
79 l_String VARCHAR2(2000);
80 l_Rollback VARCHAR2(1) := 'Y';
81
82 l_Msg_Count NUMBER;
83 l_Msg_Data Varchar2(2000);
84
85 X_Return_Status VARCHAR2(1);
86 X_Msg_Count NUMBER;
87 X_Msg_Data VARCHAR2(2000);
88
89 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
90 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
91 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
92 l_failure_rate number := 0;
93
94 BEGIN
95 -- Alter session
96 -- EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
97
98 -- Standard Start of API savepoint
99 SAVEPOINT CSP_FAILURE_RATES_PVT;
100
101 -- Standard call to check for call compatibility.
102 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
103 p_api_version_number,
104 l_api_name,
105 G_PKG_NAME)
106 THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110 -- Initialize message list if p_init_msg_list is set to TRUE.
111 IF FND_API.to_Boolean( l_init_msg_list )
112 THEN
113 FND_MSG_PUB.initialize;
114 END IF;
115
116 for cp in c_products loop
117 l_history_size := cp.history_size;
118 l_level_id := cp.level_id || '%';
119 l_product_id := cp.product_id;
120
121 delete from csp_failure_rates
122 where product_id = cp.product_id
123 and planning_parameters_id = cp.planning_parameters_id
124 and manual_failure_rate is null;
125
126 for cdl in c_debrief_lines loop
127 l_failure_rate := cdl.quantity /
128 (cp.current_population * cp.history_size / 7);
129 update csp_failure_rates cfr
130 set cfr.calculated_failure_rate = l_failure_rate,
131 cfr.last_updated_by = g_user_id,
132 cfr.last_update_date = sysdate,
133 cfr.last_update_login = g_user_id
134 where cfr.planning_parameters_id = cp.planning_parameters_id
135 and cfr.product_id = cp.product_id
136 and cfr.inventory_item_id = cdl.inventory_item_id;
137 if sql%notfound then
138 insert into csp_failure_rates(
139 failure_rate_id,
140 planning_parameters_id,
141 product_id,
142 inventory_item_id,
143 calculated_failure_rate,
144 manual_failure_rate,
145 last_update_date,
146 last_updated_by,
147 creation_date,
148 created_by,
149 last_update_login)
150 values(
151 csp_failure_rates_s1.nextval,
152 cp.planning_parameters_id,
153 cp.product_id,
154 cdl.inventory_item_id,
155 l_failure_rate,
156 null,
157 sysdate,
158 g_user_id,
159 sysdate,
160 g_user_id,
161 g_login_id);
162 end if;
163 end loop;
164 commit;
165 end loop;
166 commit;
167 End;
168 end;
169