[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_SCRAP_JOB_DTL_PKG
Source
1 PACKAGE BODY OPI_DBI_SCRAP_JOB_DTL_PKG AS
2 /* $Header: OPIRSCRJDB.pls 120.5 2006/09/14 02:08:17 asparama noship $ */
3
4
5 PROCEDURE get_dtl_sql ( p_param in BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql out nocopy VARCHAR2,
7 x_custom_output out nocopy BIS_QUERY_ATTRIBUTES_TBL)
8 IS
9 g_global_start_date DATE;
10 l_period_type VARCHAR2(255);
11 l_job_info_drill VARCHAR2(255);
12 l_org VARCHAR2(255);
13 l_item_org VARCHAR2(255);
14 l_item_cat VARCHAR2(255);
15 l_org_id NUMBER;
16 l_currency VARCHAR2(30);
17 l_item_org_where VARCHAR2(2000);
18 l_item_cat_where VARCHAR2(2000);
19 l_jobstatus_where VARCHAR2(1500);
20 l_lang_code VARCHAR2(20);
21 l_currency_code VARCHAR2(2);
22 l_flag mtl_parameters.process_enabled_flag%type ;
23 l_job_status VARCHAR2(1000);
24 l_respid VARCHAR(100);
25
26 BEGIN
27
28 l_period_type := NULL;
29 l_job_info_drill := NULL;
30 l_org := NULL;
31 l_item_org := NULL;
32 l_item_cat := NULL;
33 l_org_id := NULL;
34 l_currency := '';
35 l_item_org_where := ' ';
36 l_item_cat_where := '';
37 l_jobstatus_where := '';
38 l_lang_code := NULL;
39 l_currency_code := 'B';
40 l_flag := 'Y';
41 l_job_status := NULL;
42 l_respid := NULL;
43 g_global_start_date := bis_common_parameters.get_global_start_date;
44
45
46 FOR i IN 1..p_param.COUNT
47 LOOP
48 --{
49 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION') THEN
50 --{
51 l_org := p_param(i).parameter_id;
52 --}
53 END IF;
54
55 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
56 --{
57 IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
58 --{
59 l_item_cat := p_param(i).parameter_id;
60 --}
61 ELSE
62 --{
63 l_item_cat := 'Selcted';
64 --}
65 END IF;
66 --}
67 END IF;
68
69 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
70 --{
71 IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
72 --{
73 l_item_org := p_param(i).parameter_id;
74 --}
75 ELSE
76 --{
77 l_item_org := 'Selected';
78 --}
79 END IF;
80 --}
81 END IF;
82
83 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') THEN
84 --{
85 l_currency := p_param(i).parameter_id;
86 --}
87 END IF;
88
89
90 IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
91 --{
92 l_period_type := p_param(i).parameter_value;
93 --}
94 END IF;
95
96 IF(p_param(i).parameter_name = 'OPI_MFG_WO_ATTRIB+OPI_MFG_WO_STATUS_LVL') THEN
97 --{
98 IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
99 --{
100 l_job_status := p_param(i).parameter_ID;
101 --}
102 ELSE
103 --{
104 l_job_status := 'Selected';
105 --}
106 END IF;
107 --}
108 END IF;
109
110 END LOOP;
111
112 IF(l_currency = '''FII_GLOBAL1''') THEN
113 --{
114 l_currency_code := 'G';
115 --}
116 ELSIF (l_currency = '''FII_GLOBAL2''') THEN
117 --{
118 l_currency_code := 'SG';
119 --}
120 ELSE
121 --{
122 l_currency_code := 'B';
123 --}
124 END IF;
125
126 l_lang_code := USERENV('LANG');
127
128
129 IF (upper(l_org)<>'ALL') THEN
130 --{
131 SELECT trim(both '''' from l_org)
132 INTO l_org
133 FROM dual;
134 --}
135 END IF;
136
137 IF (l_org is NULL) THEN
138 --{
139 SELECT fnd_global.resp_id INTO l_respid from dual ;
140
141 SELECT id INTO l_org
142 FROM (SELECT id
143 FROM bis_organizations_v
144 WHERE responsibility_id = l_respid
145 ORDER BY value asc)
146 WHERE rownum=1;
147 --}
148 END IF;
149
150
151 IF (UPPER(l_item_org) <> 'ALL') THEN
152 --{
153 l_item_org_where :=' and scrap.inventory_item_id || ''-'' || scrap.organization_id in '
154 || ' (&ITEM+ENI_ITEM_ORG)' || ' and scrap.organization_id = ' || l_org;
155 --{
156 ELSE
157 --{
158 l_item_org_where :=' and scrap.organization_id = ' || l_org;
159 --}
160 END IF;
161
162 IF (UPPER(l_job_status) <> 'ALL') THEN
163 --{
164 l_jobstatus_where := ' and job.job_status_code in (&OPI_MFG_WO_ATTRIB+OPI_MFG_WO_STATUS_LVL)';
165 --}
166 END IF;
167
168 IF(UPPER(l_item_cat) <> 'ALL') THEN
169 --{
170 l_item_cat_where := ' and itemorg.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
171 --}
172 END IF;
173
174 l_job_info_drill := 'OPI_DBI_JOB_INFO_DISC_REP&addBreadCrumb=Y&cloInd=1';
175
176 SELECT process_enabled_flag
177 INTO l_flag
178 FROM mtl_parameters mp
179 WHERE mp.organization_id = trim(both '''' from l_org);
180
181 IF(l_flag<>'Y') THEN
182 --{
183 x_custom_sql := '
184 SELECT fact.job_name ||''(''|| mtp.organization_code || '')''
185 OPI_ATTRIBUTE1,
186 jobstatus.value OPI_ATTRIBUTE2,
187 itemorg.value OPI_ATTRIBUTE3,
188 itemorg.description OPI_ATTRIBUTE4,
189 uom.unit_of_measure OPI_ATTRIBUTE5,
190 ''pFunctionName='||l_job_info_drill ||'&jobId=''||fact.job_id||
191 ''&orgId=''||fact.organization_id ||
192 ''&jobName=''|| mtp.organization_code ||
193 ''&repId=''|| fact.job_name ||
194 ''&jobType=''|| fact.job_type OPI_ATTRIBUTE6,
195 fact.opi_measure1 OPI_MEASURE1,
196 fact.opi_measure2 OPI_MEASURE2,
197 fact.opi_measure3 OPI_MEASURE3,
198 fact.opi_measure4 OPI_MEASURE4,
199 fact.opi_measure5 OPI_MEASURE5,
200 fact.opi_measure6 OPI_MEASURE6,
201 fact.opi_measure7 OPI_MEASURE7,
202 fact.opi_measure8 OPI_MEASURE8,
203 fact.opi_measure9 OPI_MEASURE9,
204 fact.opi_measure10 OPI_MEASURE10
205 FROM
206 (SELECT
207 (rank() over
208 (&ORDER_BY_CLAUSE nulls last, job_name, job_id, job_type, inventory_item_id, organization_id)) -1 rnk,
209 job_name,
210 job_id,
211 job_type,
212 job_status_code,
213 organization_id,
214 inventory_item_id,
215 uom_code,
216 opi_measure1,
217 opi_measure2,
218 opi_measure3,
219 opi_measure4,
220 opi_measure5,
221 opi_measure6,
222 opi_measure7,
223 opi_measure8,
224 opi_measure9,
225 opi_measure10
226 FROM
227 (SELECT
228 job_name,
229 job_id,
230 job_type,
231 job_status_code,
232 organization_id,
233 inventory_item_id,
234 uom_code,
235 prod_qty - scrap_qty opi_measure1,
236 scrap_qty opi_measure2,
237 scrap_val opi_measure3,
238 prod_val - scrap_val opi_measure4,
239 prod_val opi_measure5,
240 scrap_val/decode(prod_val,0,null,prod_val)*100 opi_measure6,
241 sum(scrap_val) over() opi_measure7,
242 sum(prod_val - scrap_val) over() opi_measure8,
243 sum(prod_val) over() opi_measure9,
244 sum(scrap_val/decode(prod_val,0,null,prod_val)*100) over() opi_measure10
245 FROM
246 (SELECT job.job_name,
247 scrap.job_id,
248 scrap.job_type,
249 job.job_status_code,
250 scrap.organization_id,
251 scrap.inventory_item_id,
252 job.uom_code,
253 sum(scrap.production_qty) prod_qty,
254 sum(decode(''' || l_currency_code || ''',
255 ''B'', scrap.production_val_b,
256 ''G'', scrap.production_val_g,
257 ''SG'', scrap.production_val_sg))
258 prod_val,
259 sum(scrap.scrap_qty) scrap_qty,
260 sum(decode(''' || l_currency_code || ''',
261 ''B'', scrap.scrap_val_b,
262 ''G'', scrap.scrap_val_g,
263 ''SG'', scrap.scrap_val_sg))
264 scrap_val
265 FROM opi_prod_scr_mv scrap,
266 opi_dbi_jobs_f job
267 WHERE scrap.transaction_date between &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_ASOF_DATE
268 AND scrap.job_id = job.job_id
269 AND scrap.job_type = job.job_type
270 AND scrap.inventory_item_id = job.assembly_item_id
271 AND scrap.organization_id = job.organization_id
272 AND scrap.transaction_date >= ''' || g_global_start_date || ''''
273 || l_jobstatus_where || l_item_org_where || '
274 GROUP BY
275 job.job_name,
276 scrap.job_id,
277 scrap.job_type,
278 job.job_status_code,
279 scrap.organization_id,
280 scrap.inventory_item_id,
281 job.uom_code))) fact,
282 mtl_parameters mtp,
283 opi_mfg_wo_status_lvl_v jobstatus,
284 eni_item_org_v itemorg,
285 mtl_units_of_measure_vl uom
286 WHERE jobstatus.id = CASE WHEN fact.job_type=3 THEN DECODE (fact.job_status_code,2,12,1,3)
287 ELSE fact.job_status_code
288 END
289 AND mtp.organization_id = fact.organization_id
290 AND uom.uom_code = fact.uom_code
291 AND itemorg.inventory_item_id = fact.inventory_item_id
292 AND itemorg.organization_id = fact.organization_id'
293 || l_item_cat_where || '
294 AND (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
295 &ORDER_BY_CLAUSE nulls last';
296 --}
297 ELSE
298 --{
299 x_custom_sql :='
300 SELECT
301 NULL OPI_ATTRIBUTE1
302 ,NULL OPI_ATTRIBUTE2
303 ,NULL OPI_ATTRIBUTE3
304 ,NULL OPI_ATTRIBUTE4
305 ,NULL OPI_ATTRIBUTE5
306 ,NULL OPI_ATTRIBUTE6
307 ,NULL OPI_MEASURE1
308 ,NULL OPI_MEASURE2
309 ,NULL OPI_MEASURE3
310 ,NULL OPI_MEASURE4
311 ,NULL OPI_MEASURE5
312 ,NULL OPI_MEASURE6
313 ,NULL OPI_MEASURE7
314 ,NULL OPI_MEASURE8
315 ,NULL OPI_MEASURE9
316 ,NULL OPI_MEASURE10
317 FROM dual WHERE 1=2';
318 --}
319 END IF;
320
321
322 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
323
324
325 END get_dtl_sql;
326
327 END OPI_DBI_SCRAP_JOB_DTL_PKG;