eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OPI_DBI_RES_VAR_JOB_DTL_PKG

Source


1 PACKAGE BODY OPI_DBI_RES_VAR_JOB_DTL_PKG AS
2 /*$Header: OPIDRRSVJDB.pls 120.3 2006/03/27 21:17:18 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   IS
8 
9     l_org 		  VARCHAR2(255);
10     l_resource_group	  VARCHAR2(255);
11     l_resource_dept       VARCHAR2(255);
12     l_resource		  VARCHAR2(255);
13     l_currency            VARCHAR2(30);
14     l_currency_code       VARCHAR2(2);
15     l_flag                VARCHAR2(1);
16     l_jobstatus		  VARCHAR2(255);
17     l_resource_where	  VARCHAR2(2000);
18     l_resource_grp_where  VARCHAR2(2000);
19     l_resource_dept_where VARCHAR2(2000);
20     l_org_where		  VARCHAR2(255);
21     l_jobstatus_where 	  VARCHAR2(1500);
22     l_job_info_drill      VARCHAR2(255);
23     l_respid 		  NUMBER;
24 
25   BEGIN
26 
27     --Initialization
28     l_org 		   := NULL;
29     l_resource_group	   := NULL;
30     l_resource_dept        := NULL;
31     l_resource		   := NULL;
32     l_currency             := '';
33     l_currency_code        := 'B';
34     l_flag                 := NULL;
35     l_jobstatus		   := NULL;
36     l_resource_where	   := NULL;
37     l_resource_grp_where   := NULL;
38     l_resource_dept_where  := NULL;
39     l_org_where		   := NULL;
40     l_jobstatus_where 	   := NULL;
41     l_job_info_drill       := NULL;
42     l_respid 		   := -1;
43 
44     FOR i IN 1..p_param.COUNT
45     LOOP
46     	IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION') THEN
47     		l_org :=  p_param(i).parameter_id;
48     	END IF;
49 
50     	IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE_GROUP') THEN
51     	  IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL'
52     	      or p_param(i).parameter_id = '-1') THEN
53     		l_resource_group :=  p_param(i).parameter_id;
54           ELSE
55                 l_resource_group :=  'Selected';
56           END IF;
57     	END IF;
58 
59     	IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE_DEPARTMENT') THEN
60     	  IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL'
61     	      or p_param(i).parameter_id = '-1') THEN
62     		l_resource_dept :=  p_param(i).parameter_id;
63           ELSE
64                 l_resource_dept :=  'Selected';
65           END IF;
66     	END IF;
67 
68     	IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE') THEN
69     	   IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
70     	       l_resource :=  p_param(i).parameter_id;
71     	   ELSE
72     	       l_resource :=  'Selected';
73     	   END IF;
74     	END IF;
75 
76     	IF(p_param(i).parameter_name = 'OPI_MFG_WO_ATTRIB+OPI_MFG_WO_STATUS_LVL') THEN
77     	   IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
78     	       l_jobstatus := p_param(i).parameter_id;
79     	   ELSE
80     	       l_jobstatus := 'Selected';
81     	   END IF;
82     	END IF;
83 
84     	IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') THEN
85     	   l_currency := p_param(i).parameter_id;
86     	END IF;
87 
88 
89      END LOOP;
90 
91 
92      IF(l_currency = '''FII_GLOBAL1''') THEN
93     	 l_currency_code := 'G';
94      ELSE
95        IF(l_currency = '''FII_GLOBAL2''') THEN
96            l_currency_code := 'SG';
97        END IF;
98      END IF;
99 
100      IF (UPPER(l_resource) <> 'ALL') THEN
101      	l_resource_where := ' and resview.id in (&RESOURCE+ENI_RESOURCE)';
102      END IF;
103 
104      IF (UPPER(l_resource_group) <> 'ALL') THEN
105      	IF(UPPER(l_resource_group) <> '-1') THEN
106      		l_resource_grp_where := ' and resview.resource_group_fk in (&RESOURCE+ENI_RESOURCE_GROUP) ';
107      	ELSE
108      		l_resource_grp_where := ' and resview.resource_group_fk in (''-1'')';
109      	END IF;
110      END IF;
111 
112      IF (UPPER(l_resource_dept) <> 'ALL') THEN
113         IF(UPPER(l_resource_dept) <> '-1') THEN
114      		l_resource_dept_where := ' and resview.department_fk in (&RESOURCE+ENI_RESOURCE_DEPARTMENT) ';
115      	ELSE
116      		l_resource_dept_where := ' and resview.department_fk in (''-1'')';
117      	END IF;
118      END IF;
119 
120      IF (UPPER(l_jobstatus) <> 'ALL') THEN
121      	l_jobstatus_where := ' and
122      	decode (job.status,''Closed'',''12'',''Cancelled'',''7'',''Complete - No Charges'',''5'',
123      		           ''Complete'',''4'',''Pending Close'',''14'',''Failed Close'',''15'',''Released'',''3'',
124      		           ''On Hold'',''6'',''-1'') in (&OPI_MFG_WO_ATTRIB+OPI_MFG_WO_STATUS_LVL) ';
125      END IF;
126 
127      IF (l_org is NULL) THEN
128 
129      select fnd_global.resp_id into l_respid from dual ;
130      select id into l_org from (select id from bis_organizations_v where responsibility_id = l_respid order by value asc) where rownum=1;
131 
132      ELSE
133 
134      select process_enabled_flag
135      into l_flag
136      from mtl_parameters mp
137      where mp.organization_id = trim(both '''' from l_org);
138 
139      END IF;
140 
141      l_org_where := 'and job.organization_id = '||l_org;
142 
143      IF (l_flag = 'Y') THEN
144 
145         l_job_info_drill := 'OPI_DBI_JOB_INFO_PROC_REP&addBreadCrumb=Y&cloInd=1';
146      	-- Construct the URL for OPM Job info OA page
147      ELSE
148 
149         l_job_info_drill := 'OPI_DBI_JOB_INFO_DISC_REP&addBreadCrumb=Y&cloInd=1';
150         -- Construct the URL for Discrete Job info OA Page
151      END IF;
152 
153 
154      x_custom_sql :=
155      'SELECT
156          fact.job_name ||''(''||mtp.organization_code||'')'' OPI_ATTRIBUTE1,
157  	 jobstatus.value 					    OPI_ATTRIBUTE2,
158  	 fact.opi_date1 				    OPI_DATE1,
159  	 ''pFunctionName='||l_job_info_drill ||'&jobId=''||fact.job_id||
160  	                                       ''&orgId=''||fact.organization_id ||
161  	                                       ''&jobName=''|| mtp.organization_code ||
162  	                                       ''&repId=''|| fact.job_name ||
163  	                                       ''&jobType=''|| fact.job_type OPI_ATTRIBUTE4,
164  	 fact.opi_measure1                                  OPI_MEASURE1,
165          fact.opi_measure2                                  OPI_MEASURE2,
166  	 fact.opi_measure3                                  OPI_MEASURE3,
167  	 fact.opi_measure4                                  OPI_MEASURE4,
168  	 fact.opi_measure5                                  OPI_MEASURE5,
169  	 fact.opi_measure6				    OPI_MEASURE6,
170  	 fact.opi_measure7				    OPI_MEASURE7,
171  	 fact.opi_measure8                                  OPI_MEASURE8,
172 	 fact.opi_measure9                                  OPI_MEASURE9,
173 	 fact.opi_measure10                                 OPI_MEASURE10,
174 	 fact.opi_measure11                                 OPI_MEASURE11,
175 	 fact.opi_measure12				    OPI_MEASURE12,
176  	 fact.opi_measure13				    OPI_MEASURE13
177  	 FROM
178         (SELECT
179            (rank() over
180            (&ORDER_BY_CLAUSE nulls last,organization_id,job_name)) - 1 rnk,
181   	   organization_id,
182   	   job_status_code,
183   	   job_name,
184   	   job_type,
185   	   job_id,
186            opi_date1,
187            opi_measure1,
188            opi_measure2,
189            opi_measure3,
190            opi_measure4,
191            opi_measure5,
192            opi_measure6,
193            opi_measure7,
194            opi_measure8,
195            opi_measure9,
196            opi_measure10,
197            opi_measure11,
198            (opi_measure8-opi_measure10) opi_measure12,
199            (opi_measure8-opi_measure10)/decode(opi_measure10,0,null,opi_measure10)*100 opi_measure13
200          FROM
201            (select
202             	organization_id,
203             	job_status_code,
204             	job_name,
205             	job_type,
206             	job_id,
207             	opi_date1,
208                 opi_measure1,
209                 decode('''|| l_currency_code || ''', ''B'',opi_measure2_b,''G'',opi_measure2_g,''SG'',opi_measure2_sg) opi_measure2,
210         	opi_measure3,
211         	decode('''|| l_currency_code || ''', ''B'',opi_measure4_b,''G'',opi_measure4_g,''SG'',opi_measure4_sg) opi_measure4,
212         	opi_measure5,
213         	decode('''|| l_currency_code || ''', ''B'',opi_measure6_b,''G'',opi_measure6_g,''SG'',opi_measure6_sg) opi_measure6,
214         	decode('''|| l_currency_code || ''', ''B'',opi_measure7_b,''G'',opi_measure7_g,''SG'',opi_measure7_sg) opi_measure7,
215         	decode('''|| l_currency_code || ''', ''B'',opi_measure8_b,''G'',opi_measure8_g,''SG'',opi_measure8_sg) opi_measure8,
216         	opi_measure9,
217         	decode('''|| l_currency_code || ''', ''B'',opi_measure10_b,''G'',opi_measure10_g,''SG'',opi_measure10_sg) opi_measure10,
218         	opi_measure11
219             from
220   		(select
221 		   job.organization_id organization_id,
222 		   job.job_status_code job_status_code,
223 		   job.job_name job_name,
224 		   job.job_type job_type,
225 		   job.job_id job_id,
226 		   job.completion_date opi_date1,
227 		   job.actual_qty_completed opi_measure1,
228 		   sum(act.actual_val_g) opi_measure2_g,
229 		   sum(act.actual_val_b) opi_measure2_b,
230 		   sum(act.actual_val_sg) opi_measure2_sg,
231 		   sum(act.actual_qty) opi_measure3,
232 		   sum(std.std_usage_val_g) opi_measure4_g,
233 		   sum(std.std_usage_val_b) opi_measure4_b,
234 		   sum(std.std_usage_val_sg) opi_measure4_sg,
235 		   sum(std.std_usage_qty) opi_measure5,
236 		   sum(act.actual_val_g) - sum(std.std_usage_val_g) opi_measure6_g,
237 		   sum(act.actual_val_b) - sum(std.std_usage_val_b) opi_measure6_b,
238 		   sum(act.actual_val_sg) - sum(std.std_usage_val_sg) opi_measure6_sg,
239 		   (sum(act.actual_val_g) - sum(std.std_usage_val_g))/(decode(sum(std.std_usage_val_g),0,
240 		   null,sum(std.std_usage_val_g)))*100 opi_measure7_g,
241 		   (sum(act.actual_val_b) - sum(std.std_usage_val_b))/(decode(sum(std.std_usage_val_b),0,
242 		   null,sum(std.std_usage_val_b)))*100 opi_measure7_b,
243 		   (sum(act.actual_val_sg) - sum(std.std_usage_val_sg))/(decode(sum(std.std_usage_val_sg),0,
244 		   null,sum(std.std_usage_val_sg)))*100 opi_measure7_sg,
245 		   sum(sum(act.actual_val_sg)) over() opi_measure8_sg,
246 		   sum(sum(act.actual_val_b)) over() opi_measure8_b,
247 		   sum(sum(act.actual_val_g)) over() opi_measure8_g,
248 		   sum(sum(act.actual_qty)) over() opi_measure9,
249 		   sum(sum(std.std_usage_val_g)) over() opi_measure10_g,
250 		   sum(sum(std.std_usage_val_b)) over() opi_measure10_b,
251 		   sum(sum(std.std_usage_val_sg)) over() opi_measure10_sg,
252 		   sum(sum(std.std_usage_qty)) over() opi_measure11
253 		 from
254 		   opi_dbi_res_std_f std,
255 		   opi_dbi_res_actual_f act,
256 		   opi_dbi_jobs_f job,
257 		   eni_resource_v resview
258 		 where
259 		   job.completion_date between &BIS_CURRENT_EFFECTIVE_START_DATE and
260 		   &BIS_CURRENT_ASOF_DATE and
261 		   job.job_id = act.job_id	and
262 		   job.source = act.source and
263 		   job.organization_id = act.organization_id and
264 		   job.assembly_item_id = act.assembly_item_id and
265 		   job.job_type = act.job_type and
266 		   job.job_id = std.job_id	and
267 		   job.source = std.source and
268 		   job.organization_id = std.organization_id and
269 		   job.assembly_item_id = std.assembly_item_id and
270 		   job.job_type = std.job_type and
271 		   std.resource_id = act.resource_id and
272 		   job.organization_id = resview.organization_id and
273 		   job.status IN (''Cancelled'', ''Complete - No Charges'',
274                                   ''Closed'') and
275 		   resview.resource_id = act.resource_id and
276 		   job.job_type <> 5'
277 		   		|| l_resource_where
278 		    		|| l_resource_grp_where
279 		    		|| l_resource_dept_where
280 		    		|| l_jobstatus_where
281 		    		|| l_org_where
282 		    		|| '
283 		group by
284 		   job.organization_id,
285 		   job.job_status_code,
286 		   job.completion_date,
287 		   job.actual_qty_completed,
288 		   job.job_name,
289 		   job.job_type,
290 		   job.job_id,
291 		   job.assembly_item_id
292      		)))fact,
293         	   fii_time_day        time,
294         	   mtl_parameters          mtp,
295                opi_mfg_wo_status_lvl_v jobstatus
296         	 where
297                 jobstatus.id = fact.job_status_code and
298         	     mtp.organization_id = fact.organization_id and
299   		     time.report_date = fact.opi_date1 and
300 		     (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
301 		      &ORDER_BY_CLAUSE nulls last';
302   	x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
303 
304    END get_dtl_sql;
305    END OPI_DBI_RES_VAR_JOB_DTL_PKG;