[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_RES_EFF_JOB_DTL_PKG
Source
1 PACKAGE BODY OPI_DBI_RES_EFF_JOB_DTL_PKG AS
2 /*$Header: OPIDRRSEJDB.pls 120.1 2005/08/26 12:10:00 julzhang 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(1);
15 l_flag VARCHAR2(1);
16 l_org_where VARCHAR2(255);
17 l_jobstatus VARCHAR2(255);
18 l_resource_where VARCHAR2(2000);
19 l_resource_grp_where VARCHAR2(2000);
20 l_resource_dept_where VARCHAR2(2000);
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 := 'Y';
35 l_org_where := NULL;
36 l_jobstatus :=NULL;
37 l_resource_where := NULL;
38 l_resource_grp_where := NULL;
39 l_resource_dept_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 END LOOP;
89
90 IF (UPPER(l_resource) <> 'ALL') THEN
91 l_resource_where := ' and resview.id in (&RESOURCE+ENI_RESOURCE)'
92 ||' and job.organization_id = '||l_org;
93 END IF;
94
95 IF (UPPER(l_resource_group) <> 'ALL') THEN
96 IF(UPPER(l_resource_group) <> '-1') THEN
97 l_resource_grp_where := ' and resview.resource_group_fk in (&RESOURCE+ENI_RESOURCE_GROUP) ';
98 ELSE
99 l_resource_grp_where := ' and resview.resource_group_fk in (''-1'')';
100 END IF;
101
102 END IF;
103
104 IF (UPPER(l_resource_dept) <> 'ALL') THEN
105 IF(UPPER(l_resource_dept) <> '-1') THEN
106 l_resource_dept_where := ' and resview.department_fk in (&RESOURCE+ENI_RESOURCE_DEPARTMENT) ';
107 ELSE
108 l_resource_dept_where := ' and resview.department_fk in (''-1'')';
109 END IF;
110 END IF;
111
112 IF (UPPER(l_jobstatus) <> 'ALL') THEN
113 l_jobstatus_where := ' and
114 decode (job.status,''Closed'',''12'',''Cancelled'',''7'',''Complete - No Charges'',''5'',
115 ''Complete'',''4'',''Pending Close'',''14'',''Failed Close'',''15'',''Released'',''3'',
116 ''On Hold'',''6'',''-1'') in (&OPI_MFG_WO_ATTRIB+OPI_MFG_WO_STATUS_LVL) ';
117 END IF;
118
119 IF (l_org is NULL) THEN
120
121 select fnd_global.resp_id into l_respid from dual ;
122 select id into l_org from (select id from bis_organizations_v where responsibility_id = l_respid order by value asc) where rownum=1;
123
124 ELSE
125
126 select process_enabled_flag
127 into l_flag
128 from mtl_parameters mp
129 where mp.organization_id = trim(both '''' from l_org);
130
131 END IF;
132
133 l_org_where := 'and job.organization_id = '||l_org;
134
135 IF (l_flag = 'Y') THEN
136
137 l_job_info_drill := 'OPI_DBI_JOB_INFO_PROC_REP&addBreadCrumb=Y&cloInd=1';
138 -- Construct the URL for OPM Job info OA page
139 ELSE
140
141 l_job_info_drill := 'OPI_DBI_JOB_INFO_DISC_REP&addBreadCrumb=Y&cloInd=1';
142 -- Construct the URL for Discrete Job info OA Page
143 END IF;
144
145 x_custom_sql :=
146 'SELECT
147 fact.job_name ||''(''||mtp.organization_code||'')'' OPI_ATTRIBUTE1,
148 jobstatus.value OPI_ATTRIBUTE2,
149 fact.opi_date1 OPI_DATE1,
150 ''pFunctionName='||l_job_info_drill ||'&jobId=''||fact.job_id||
151 ''&orgId=''||fact.organization_id ||
152 ''&jobName=''|| mtp.organization_code ||
153 ''&repId=''|| fact.job_name ||
154 ''&jobType=''|| fact.job_type OPI_ATTRIBUTE4,
155 fact.opi_measure1 OPI_MEASURE1,
156 fact.opi_measure3 OPI_MEASURE3,
157 fact.opi_measure5 OPI_MEASURE5,
158 fact.opi_measure6 OPI_MEASURE6,
159 fact.opi_measure8 OPI_MEASURE8,
160 fact.opi_measure9 OPI_MEASURE9,
161 fact.opi_measure10 OPI_MEASURE10
162 from
163 (select
164 (rank() over
165 (&ORDER_BY_CLAUSE nulls last,organization_id,job_name)) - 1 rnk,
166 organization_id,
167 job_status_code,
168 job_name,
169 job_id,
170 job_type,
171 opi_date1,
172 opi_measure1,
173 opi_measure3,
174 opi_measure5,
175 opi_measure6,
176 opi_measure8,
177 opi_measure9,
178 opi_measure10
179 from
180 (select
181 organization_id,
182 job_status_code,
183 job_name,
184 job_id,
185 job_type,
186 opi_date1,
187 opi_measure1,
188 opi_measure3,
189 opi_measure5,
190 opi_measure6,
191 opi_measure8,
192 opi_measure9,
193 opi_measure8/opi_measure9*100 opi_measure10
194 from
195 (select
196 job.organization_id organization_id,
197 job.job_status_code job_status_code,
198 job.job_name job_name,
199 job.job_id job_id,
200 job.job_type job_type,
201 job.completion_date opi_date1,
202 job.actual_qty_completed opi_measure1,
203 sum(std.std_usage_qty) opi_measure3,
204 sum(act.actual_qty) opi_measure5,
205 sum(std.std_usage_qty)*100/decode(sum(act.actual_qty),0,null,sum(act.actual_qty)) opi_measure6,
206 sum(sum(std.std_usage_qty)) over() opi_measure8,
207 sum(sum(act.actual_qty)) over() opi_measure9
208 from
209 opi_dbi_res_std_f std,
210 opi_dbi_res_actual_f act,
211 opi_dbi_jobs_f job,
212 eni_resource_v resview
213 where
214 job.completion_date between &BIS_CURRENT_EFFECTIVE_START_DATE and
215 &BIS_CURRENT_ASOF_DATE and
216 job.job_id = act.job_id and
217 job.source = act.source and
218 job.organization_id = act.organization_id and
219 job.assembly_item_id = act.assembly_item_id and
220 job.job_type = act.job_type and
221 job.job_id = std.job_id and
222 job.source = std.source and
223 job.organization_id = std.organization_id and
224 job.assembly_item_id = std.assembly_item_id and
225 job.job_type = std.job_type and
226 std.resource_id = act.resource_id and
227 job.organization_id = resview.organization_id and
228 job.status IN (''Cancelled'', ''Complete - No Charges'',
229 ''Closed'') and
230 resview.resource_id = act.resource_id '
231 || l_resource_where
232 || l_resource_grp_where
233 || l_resource_dept_where
234 || l_jobstatus_where
235 || l_org_where
236 || '
237 group by
238 job.organization_id,
239 job.job_status_code,
240 job.job_name,
241 job.job_id,
242 job.job_type,
243 job.completion_date,
244 job.actual_qty_completed,
245 job.assembly_item_id
246 )))fact,
247 fii_time_day time,
248 mtl_parameters mtp,
249 opi_mfg_wo_status_lvl_v jobstatus
250 where
251 fact.job_status_code = jobstatus.id and
252 mtp.organization_id = fact.organization_id and
253 time.report_date = fact.opi_date1 and
254 (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
255 &ORDER_BY_CLAUSE nulls last
256 ';
257
258 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
259
260 END get_dtl_sql;
261 END OPI_DBI_RES_EFF_JOB_DTL_PKG;