[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_MTL_VAR_JOB_DTL_PKG
Source
1 PACKAGE BODY OPI_DBI_MTL_VAR_JOB_DTL_PKG AS
2 /*$Header: OPIDRMUVJDB.pls 120.7 2006/02/19 22:30:32 vganeshk noship $ */
3
4 PROCEDURE get_dtl_sql (p_param in BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql out nocopy VARCHAR2,
6 x_custom_output out nocopy BIS_QUERY_ATTRIBUTES_TBL)
7
8 IS
9
10 l_period_type VARCHAR2(255);
11 l_org VARCHAR2(255);
12 l_item_org VARCHAR2(255);
13 l_item_cat VARCHAR2(255);
14 l_currency VARCHAR2(30);
15 l_jobstatus VARCHAR2(255);
16 l_item_org_where VARCHAR2(2000);
17 l_item_cat_where VARCHAR2(2000);
18 l_jobstatus_where VARCHAR2(2000);
19 l_lang_code VARCHAR2(20);
20 l_currency_code VARCHAR2(2);
21 l_respid NUMBER;
22 l_job_info_drill VARCHAR2(255);
23 l_flag VARCHAR2(1);
24 l_org_where VARCHAR2(2000);
25
26
27 BEGIN
28
29 --Initialization
30 l_period_type := NULL;
31 l_org := NULL;
32 l_item_org := NULL;
33 l_item_cat := NULL;
34 l_currency := '';
35 l_jobstatus := NULL;
36 l_item_org_where := ' ';
37 l_item_cat_where := ' ';
38 l_jobstatus_where := ' ';
39 l_lang_code := NULL;
40 l_currency_code := 'B';
41 l_respid :=-1;
42 l_job_info_drill := NULL;
43 l_flag := NULL;
44 l_org_where := NULL;
45
46 FOR i IN 1..p_param.COUNT
47 LOOP
48 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
49 THEN l_org := p_param(i).parameter_id;
50 END IF;
51
52 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
53 IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
54 l_item_org := p_param(i).parameter_id;
55 else
56 l_item_org := 'Selected';
57 END IF;
58 END IF;
59
60 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
61 IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
62 l_item_cat := p_param(i).parameter_id;
63 ELSE
64 l_item_cat := 'Selected';
65 END IF;
66 END IF;
67
68 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
69 l_currency := p_param(i).parameter_id;
70 END IF;
71
72 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
73 THEN l_period_type := p_param(i).parameter_value;
74 END IF;
75
76 IF(p_param(i).parameter_name = 'OPI_MFG_WO_ATTRIB+OPI_MFG_WO_STATUS_LVL')
77 THEN l_jobstatus := p_param(i).parameter_id;
78 END IF;
79
80 END LOOP;
81
82 IF(l_currency = '''FII_GLOBAL1''') then
83 l_currency_code := 'G';
84 ELSE
85 IF
86 (l_currency = '''FII_GLOBAL2''') then
87 l_currency_code := 'SG';
88 END IF;
89 END IF;
90
91
92 IF (UPPER(l_item_org) <> 'ALL') THEN
93 l_item_org_where :='
94 AND fact.assembly_item_id||''-''|| fact.organization_id in' ||'(&ITEM+ENI_ITEM_ORG)';
95 --||'AND fact.organization_id = '||l_org;
96 END IF;
97
98 IF(UPPER(l_item_cat) <> 'ALL') THEN
99 l_item_cat_where := ' and itemorg.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
100 END IF;
101
102 IF (UPPER(l_jobstatus) <> 'ALL') THEN
103 l_jobstatus_where := ' and
104 decode (jobs.status,''Closed'',12,''Cancelled'',7,''Complete - No Charges'',5,
105 ''Complete'',4,''Pending Close'',14,''Failed Close'',15,''Released'',3,
106 ''On Hold'',6,-1) in ( ' || l_jobstatus || ') ';
107 END IF;
108
109 IF (l_org is NULL) THEN
110
111 select fnd_global.resp_id into l_respid from dual ;
112 select id into l_org from (select id from bis_organizations_v where responsibility_id = l_respid order by value asc) where rownum=1;
113
114 ELSE
115
116 select process_enabled_flag
117 into l_flag
118 from mtl_parameters mp
119 where mp.organization_id = trim(both '''' from l_org);
120
121 END IF;
122
123 l_org_where := ' and fact.organization_id = '||l_org;
124
125 IF (l_flag = 'Y') THEN
126
127 l_job_info_drill := 'OPI_DBI_JOB_INFO_PROC_REP&cloInd=1';
128
129 ELSE
130
131 l_job_info_drill := 'OPI_DBI_JOB_INFO_DISC_REP&cloInd=1';
132
133 END IF;
134
135
136 x_custom_sql :=
137 'SELECT
138 f.job_name ||''(''||mtp.organization_code||'')'' OPI_ATTRIBUTE1
139 , f.opi_attribute2 OPI_ATTRIBUTE2
140 , itemorg.value OPI_ATTRIBUTE3
141 , itemorg.description OPI_ATTRIBUTE4
142 , v2.unit_of_measure OPI_ATTRIBUTE5
143 , f.opi_date1 OPI_DATE1
144 , ''pFunctionName='||l_job_info_drill ||'&jobId=''||f.job_id||
145 ''&orgId=''||f.org_id ||
146 ''&jobName=''|| mtp.organization_code ||
147 ''&repId=''|| f.job_name ||
148 ''&jobType=''|| f.job_type OPI_ATTRIBUTE7
149
150
151 , f.opi_measure1 OPI_MEASURE1
152 , f.opi_measure2 OPI_MEASURE2
153 , f.opi_measure3 OPI_MEASURE3
154 , f.opi_measure4 OPI_MEASURE4
155 , f.opi_measure5 OPI_MEASURE5
156 , f.opi_measure6 OPI_MEASURE6
157 , f.opi_measure7 OPI_MEASURE7
158 , f.opi_measure8 OPI_MEASURE8
159 , f.opi_measure9 OPI_MEASURE9
160
161 from
162 (select
163 (rank() over (&ORDER_BY_CLAUSE nulls last,org_id,job_id,completion_date)) - 1 rnk,
164 org_id
165 ,assembly_item_id
166 ,job_id
167 ,job_type
168 ,job_name
169 ,job_status_code
170 ,completion_date
171 ,opi_attribute2
172 ,opi_date1
173 ,opi_measure1
174 ,opi_measure2
175 ,opi_measure3
176 ,opi_measure4
177 ,opi_measure5
178 ,opi_measure6
179 ,opi_measure7
180 ,(opi_measure7-opi_measure6) opi_measure8
181 ,((opi_measure7-opi_measure6)/decode(opi_measure6,0,null,opi_measure6))*100 opi_measure9
182 from
183
184 (select
185 org_id
186 ,assembly_item_id
187 ,job_id
188 ,job_type
189 ,job_name
190 ,job_status_code
191 ,completion_date
192 ,opi_attribute2
193 ,opi_date1
194 ,opi_measure1
195 ,opi_measure2_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure2
196 ,opi_measure3_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure3
197 ,opi_measure4_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure4
198 ,opi_measure5
199 ,opi_measure6_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure6
200 ,opi_measure7_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure7
201 from
202 (select
203 actfact.organization_id org_id
204 ,actfact.job_id
205 ,actfact.job_type
206 ,actfact.job_name
207 ,actfact.completion_date
208 ,actfact.conversion_rate
209 ,actfact.sec_conversion_rate
210 ,actfact.assembly_item_id
211 ,actfact.organization_id
212 ,actfact.job_status_code
213 ,actfact.status opi_attribute2
214 ,actfact.completion_date opi_date1
215 ,actfact.actual_qty_completed opi_measure1
216 ,SUM(decode(actfact.start_quantity, 0, 1, decode(actfact.Source, 1, decode(sign(actfact.START_QUANTITY - actfact.ACTUAL_QTY_COMPLETED),
217 1, actfact.ACTUAL_QTY_COMPLETED / actfact.START_QUANTITY, 1), 1) * stdfact.Standard_Value_B)) opi_measure2_b
218 ,sum(actfact.actual_value_b) opi_measure3_b
219 ,sum(actfact.actual_value_b) -
220 SUM(decode(actfact.start_quantity, 0, 1, decode(actfact.Source, 1, decode(sign(actfact.START_QUANTITY - actfact.ACTUAL_QTY_COMPLETED),
221 1, actfact.ACTUAL_QTY_COMPLETED / actfact.START_QUANTITY, 1), 1) * stdfact.Standard_Value_B))
222 opi_measure4_b
223 ,((sum(actfact.actual_value_b)-SUM(decode(actfact.start_quantity, 0, 1, decode(actfact.Source,
224 1, decode(sign(actfact.START_QUANTITY - actfact.ACTUAL_QTY_COMPLETED),
225 1, actfact.ACTUAL_QTY_COMPLETED / actfact.START_QUANTITY, 1), 1) * stdfact.Standard_Value_B)))
226 /decode(SUM(decode(actfact.start_quantity, 0, 1, decode(actfact.Source, 1, decode(sign(actfact.START_QUANTITY - actfact.ACTUAL_QTY_COMPLETED),
227 1, actfact.ACTUAL_QTY_COMPLETED / actfact.START_QUANTITY, 1), 1) * stdfact.Standard_Value_B)),
228 0, null,
229 SUM(decode(actfact.start_quantity, 0, 1, decode(actfact.Source, 1, decode(sign(actfact.START_QUANTITY - actfact.ACTUAL_QTY_COMPLETED),
230 1, actfact.ACTUAL_QTY_COMPLETED / actfact.START_QUANTITY, 1), 1) * stdfact.Standard_Value_B))))*100
231 opi_measure5
232 ,sum(SUM(decode(actfact.start_quantity, 0, 1, decode(actfact.Source, 1, decode(sign(actfact.START_QUANTITY - actfact.ACTUAL_QTY_COMPLETED),
233 1, actfact.ACTUAL_QTY_COMPLETED / actfact.START_QUANTITY, 1), 1) * stdfact.Standard_Value_B))) over() opi_measure6_b
234 ,sum(sum(actfact.actual_value_b)) over() opi_measure7_b
235 from
236 (
237 select
238 jobs.organization_id,
239 jobs.assembly_item_id,
240 fact.component_item_id,
241 jobs.job_id,
242 jobs.job_type,
243 jobs.job_name,
244 jobs.completion_date,
245 jobs.conversion_rate,
246 jobs.sec_conversion_rate,
247 jobs.job_status_code,
248 jobs.status,
249 jobs.source,
250 jobs.start_quantity,
251 jobs.actual_qty_completed,
252 sum(actual_value_b) actual_value_b,
253 sum(actual_quantity) actual_quantity
254 from
255 OPI_DBI_JOB_MTL_DETAILS_F fact,
256 OPI_DBI_JOBS_F jobs
257 where
258 jobs.completion_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
259 and fact.job_id = jobs.job_id
260 and fact.assembly_item_id = jobs.assembly_item_id
261 and fact.organization_id = jobs.organization_id
262 and fact.job_type = jobs.job_type
263 and jobs.status IN ( ''Closed'', ''Complete - No Charges'', ''Cancelled'')
264 and jobs.Include_Job = 1
265 and jobs.job_type <> 5'
266 || l_item_org_where
267 ||l_jobstatus_where
268 || l_org_where ||'
269 group by
270 jobs.organization_id,
271 jobs.assembly_item_id,
272 fact.component_item_id,
273 jobs.job_id,
274 jobs.job_type,
275 jobs.job_name,
276 jobs.completion_date,
277 jobs.conversion_rate,
278 jobs.sec_conversion_rate,
279 jobs.actual_qty_completed,
280 jobs.job_status_code,
281 jobs.status,
282 jobs.source,
283 jobs.start_quantity
284 )actfact,
285 OPI_DBI_JOB_MTL_DTL_STD_F stdfact
286 where
287 actfact.job_id = stdfact.job_id and
288 actfact.job_type = stdfact.job_type and
289 actfact.organization_id = stdfact.organization_id and
290 actfact.assembly_item_id = stdfact.assembly_item_id and
291 actfact.component_item_id = stdfact.component_item_id
292 group by
293 actfact.organization_id
294 ,actfact.job_id
295 ,actfact.job_type
296 ,actfact.job_name
297 ,actfact.completion_date
298 ,actfact.conversion_rate
299 ,actfact.sec_conversion_rate
300 ,actfact.assembly_item_id
301 ,actfact.organization_id
302 ,actfact.status
303 ,actfact.job_status_code
304 ,actfact.completion_date
305 ,actfact.actual_qty_completed
306 ))) f
307 ,eni_item_org_v itemorg
308 ,mtl_units_of_measure_vl v2
309 ,fii_time_day time
310 ,mtl_parameters mtp
311 ,OPI_MFG_WO_STATUS_LVL_V job_status
312 where
313 mtp.organization_id = f.org_id
314 and itemorg.id = f.assembly_item_id||''-''|| f.org_id
315 and itemorg.organization_id = f.org_id
316 and itemorg.primary_uom_code = V2.uom_code
317 and f.job_status_code = job_status.id
318 and time.report_date = f.completion_date
319 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1) ' ||
320 l_item_cat_where || '
321 &ORDER_BY_CLAUSE nulls last';
322
323
324
325 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
326
327 END get_dtl_sql;
328 END OPI_DBI_MTL_VAR_JOB_DTL_PKG;