[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_MFG_JOB_DTL_PKG
Source
1 PACKAGE BODY OPI_DBI_MFG_JOB_DTL_PKG AS
2 /*$Header: OPIDRMCVJDB.pls 120.1 2006/03/19 22:07:16 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_item_org VARCHAR2(255);
11 l_item_cat VARCHAR2(255);
12 l_currency VARCHAR2(30);
13 l_item_org_where VARCHAR2(2000);
14 l_item_cat_where VARCHAR2(2000);
15 l_currency_code VARCHAR2(2);
16 l_flag VARCHAR2(1);
17 l_org_where VARCHAR2(2000);
18 l_respid NUMBER;
19 l_job_info_drill VARCHAR2(255);
20
21 BEGIN
22
23 --Initialization
24 l_org := NULL;
25 l_item_org := NULL;
26 l_item_cat := NULL;
27 l_currency := '';
28 l_item_org_where := NULL;
29 l_item_cat_where := NULL;
30 l_currency_code := 'B';
31 l_flag := NULL;
32 l_org_where := NULL;
33 l_respid := -1;
34 l_job_info_drill := NULL;
35
36 FOR i IN 1..p_param.COUNT
37 LOOP
38 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
39 THEN l_org := p_param(i).parameter_id;
40 END IF;
41
42 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
43 IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
44 l_item_org := p_param(i).parameter_id;
45 ELSE
46 l_item_org := 'Selected';
47 END IF;
48 END IF;
49
50 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
51 IF (p_param(i).parameter_id IS NULL or upper(p_param(i).parameter_id) = 'ALL') THEN
52 l_item_cat := p_param(i).parameter_id;
53 ELSE
54 l_item_cat := 'Selected';
55 END IF;
56 END IF;
57
58 IF(p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
59 l_currency := p_param(i).parameter_id;
60 END IF;
61
62 END LOOP;
63
64 IF(l_currency = '''FII_GLOBAL1''') then
65 l_currency_code := 'G';
66 ELSE
67 IF
68 (l_currency = '''FII_GLOBAL2''') then
69 l_currency_code := 'SG';
70 END IF;
71 END IF;
72
73
74 IF (UPPER(l_item_org) <> 'ALL') THEN
75 l_item_org_where :='
76 AND fact.assembly_item_id||''-''|| fact.organization_id in' ||'(&ITEM+ENI_ITEM_ORG)';
77 END IF;
78
79 IF(UPPER(l_item_cat) <> 'ALL') THEN
80 l_item_cat_where := ' and itemorg.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
81 END IF;
82
83 IF (l_org is NULL) THEN
84
85 select fnd_global.resp_id into l_respid from dual ;
86 select id into l_org from (select id from bis_organizations_v where responsibility_id = l_respid order by value asc) where rownum=1;
87
88 ELSE
89
90 select process_enabled_flag
91 into l_flag
92 from mtl_parameters mp
93 where mp.organization_id = trim(both '''' from l_org);
94
95 END IF;
96
97 l_org_where := ' and jobs.organization_id = '||l_org;
98
99 IF (l_flag = 'Y') THEN
100
101 l_job_info_drill := 'OPI_DBI_JOB_INFO_PROC_REP&addBreadCrumb=Y&cloInd=1';
102
103 ELSE
104
105 l_job_info_drill := 'OPI_DBI_JOB_INFO_DISC_REP&addBreadCrumb=Y&cloInd=1';
106
107 END IF;
108
109 x_custom_sql :=
110 'SELECT
111 f.job_name ||''(''||mtp.organization_code||'')'' OPI_ATTRIBUTE1
112 , itemorg.value OPI_ATTRIBUTE2
113 , itemorg.description OPI_ATTRIBUTE3
114 , v2.unit_of_measure OPI_ATTRIBUTE4
115 , ''pFunctionName='||l_job_info_drill ||'&jobId=''||f.job_id||
116 ''&orgId=''||f.org_id ||
117 ''&jobName=''|| mtp.organization_code ||
118 ''&repId=''|| f.job_name ||
119 ''&jobType=''|| f.job_type OPI_ATTRIBUTE5
120 , f.opi_measure1 OPI_MEASURE1
121 , f.opi_measure2 OPI_MEASURE2
122 , f.opi_measure3 OPI_MEASURE3
123 , f.opi_measure4 OPI_MEASURE4
124 , f.opi_measure5 OPI_MEASURE5
125 , f.opi_measure6 OPI_MEASURE6
126 , f.opi_measure7 OPI_MEASURE7
127 , f.opi_measure8 OPI_MEASURE8
128 , f.opi_measure9 OPI_MEASURE9
129 , f.opi_measure10 OPI_MEASURE10
130
131 from
132 (select
133 (rank() over
134 (&ORDER_BY_CLAUSE nulls last,org_id,job_id,closed_date)) - 1 rnk
135 ,org_id
136 ,assembly_item_id
137 ,uom_code
138 ,job_id
139 ,job_type
140 ,job_name
141 ,closed_date
142 ,opi_measure1
143 ,opi_measure2
144 ,opi_measure3
145 ,opi_measure4
146 ,opi_measure5
147 ,opi_measure6
148 ,opi_measure7
149 ,opi_measure8
150 ,(opi_measure8-opi_measure7) opi_measure9
151 ,((opi_measure8-opi_measure7)/decode(opi_measure7,0,null,opi_measure7))*100 opi_measure10
152
153 from
154
155 (select
156 org_id
157 ,assembly_item_id
158 ,uom_code
159 ,job_id
160 ,job_type
161 ,job_name
162 ,closed_date
163 ,opi_measure1
164 ,opi_measure2
165 ,opi_measure3_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure3
166 ,opi_measure4_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure4
167 ,opi_measure5_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure5
168 ,opi_measure6
169 ,opi_measure7_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure7
170 ,opi_measure8_b*decode(''' || l_currency_code || ''', ''B'',1, ''G'', conversion_rate, ''SG'', sec_conversion_rate) opi_measure8
171 from
172 (select
173 fact.organization_id org_id
174 ,fact.job_id job_id
175 ,fact.job_type job_type
176 ,jobs.job_name job_name
177 ,fact.closed_date
178 ,fact.conversion_rate
179 ,fact.sec_conversion_rate
180 ,fact.uom_code
181 ,fact.assembly_item_id
182 ,fact.organization_id
183 ,sum(jobs.start_quantity) opi_measure1
184 ,sum(fact.actual_qty_completed) opi_measure2
185 ,sum(fact.standard_value_b) opi_measure3_b
186 ,sum(fact.actual_value_b) opi_measure4_b
187 ,sum(fact.actual_value_b)- sum(fact.standard_value_b) opi_measure5_b
188 ,((sum(fact.actual_value_b)-sum(fact.standard_value_b))/decode(sum(fact.standard_value_b),0,null,sum(fact.standard_value_b)))*100 opi_measure6
189 ,sum(sum(fact.standard_value_b)) over() opi_measure7_b
190 ,sum(sum(fact.actual_value_b)) over() opi_measure8_b
191
192 from
193 opi_dbi_mfg_cst_var_f fact
194 ,opi_dbi_jobs_f jobs
195 where
196 fact.closed_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
197 and fact.job_id = jobs.job_id
198 and fact.assembly_item_id = jobs.assembly_item_id
199 and fact.organization_id = jobs.organization_id
200 and fact.job_type = jobs.job_type
201 and fact.source = jobs.source
202 and jobs.job_status_code = 12'
203 || l_item_org_where
204 || l_org_where ||'
205
206 group by
207 fact.organization_id
208 ,fact.job_id
209 ,fact.job_type
210 ,jobs.job_name
211 ,fact.closed_date
212 ,fact.conversion_rate
213 ,fact.sec_conversion_rate
214 ,fact.uom_code
215 ,fact.assembly_item_id
216 ,fact.organization_id
217 )))f
218 ,eni_item_org_v itemorg
219 ,mtl_units_of_measure_vl v2
220 ,fii_time_day time
221 ,mtl_parameters mtp
222 where
223 mtp.organization_id = f.org_id
224 and itemorg.id = f.assembly_item_id||''-''|| f.org_id
225 and itemorg.organization_id = f.org_id
226 and itemorg.primary_uom_code = V2.uom_code
227 and time.report_date = f.closed_date
228 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1) ' ||
229 l_item_cat_where || '
230
231 &ORDER_BY_CLAUSE nulls last';
232
233 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
234
235 END get_dtl_sql;
236 END OPI_DBI_MFG_JOB_DTL_PKG;