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