Query to get details of WIP discrete jobs
Oracle apps EBS sql to get details of WIP discrete jobs
oracle wip tables
oracle wip query
SELECT wdj.wip_entity_id cum_entity_id,
wdj.organization_id cum_org_id,
ml_elem_type.meaning elem_var_type,
SUM (
ROUND (
DECODE (
ml_elem_type.lookup_code,
1, NVL (wpb.pl_material_in, 0),
2, NVL (wpb.pl_material_overhead_in, 0),
3, (NVL (wpb.pl_resource_in, 0) + NVL (wpb.tl_resource_in, 0)),
4, ( NVL (wpb.pl_outside_processing_in, 0)
+ NVL (wpb.tl_outside_processing_in, 0)),
5, (NVL (wpb.pl_overhead_in, 0) + NVL (wpb.tl_overhead_in, 0)),
12, NVL (wpb.tl_scrap_in, 0),
NULL),
:PRECISION))
costs_in,
SUM (
ROUND (
DECODE (
ml_elem_type.lookup_code,
1, ( NVL (wpb.pl_material_out, 0)
+ NVL (wpb.tl_material_out, 0)),
2, ( NVL (wpb.pl_material_overhead_out, 0)
+ NVL (wpb.tl_material_overhead_out, 0)),
3, ( NVL (wpb.pl_resource_out, 0)
+ NVL (wpb.tl_resource_out, 0)),
4, ( NVL (wpb.pl_outside_processing_out, 0)
+ NVL (wpb.tl_outside_processing_out, 0)),
5, ( NVL (wpb.pl_overhead_out, 0)
+ NVL (wpb.tl_overhead_out, 0)),
12, NVL (wpb.tl_scrap_out, 0),
NULL),
:PRECISION))
costs_out,
SUM (
ROUND (
DECODE (
ml_elem_type.lookup_code,
1, ( NVL (wpb.pl_material_var, 0)
+ NVL (wpb.tl_material_var, 0)),
2, ( NVL (wpb.pl_material_overhead_var, 0)
+ NVL (wpb.tl_material_overhead_var, 0)),
3, ( NVL (wpb.pl_resource_var, 0)
+ NVL (wpb.tl_resource_var, 0)),
4, ( NVL (wpb.pl_outside_processing_var, 0)
+ NVL (wpb.tl_outside_processing_var, 0)),
5, ( NVL (wpb.pl_overhead_var, 0)
+ NVL (wpb.tl_overhead_var, 0)),
12, NVL (wpb.tl_scrap_var, 0),
NULL),
:PRECISION))
costs_var,
SUM (
ROUND (
DECODE (
ml_elem_type.lookup_code,
1, ( NVL (wpb.pl_material_in, 0)
- NVL (wpb.pl_material_out, 0)
- NVL (wpb.tl_material_out, 0)),
2, ( NVL (wpb.pl_material_overhead_in, 0)
- NVL (wpb.pl_material_overhead_out, 0)
- NVL (wpb.tl_material_overhead_out, 0)),
3, ( NVL (wpb.pl_resource_in, 0)
+ NVL (wpb.tl_resource_in, 0)
- NVL (wpb.pl_resource_out, 0)
- NVL (wpb.tl_resource_out, 0)),
4, ( NVL (wpb.pl_outside_processing_in, 0)
+ NVL (wpb.tl_outside_processing_in, 0)
- NVL (wpb.pl_outside_processing_out, 0)
- NVL (wpb.tl_outside_processing_out, 0)),
5, ( NVL (wpb.pl_overhead_in, 0)
+ NVL (wpb.tl_overhead_in, 0)
- NVL (wpb.pl_overhead_out, 0)
- NVL (wpb.tl_overhead_out, 0)),
12, (NVL (wpb.tl_scrap_in, 0) - NVL (wpb.tl_scrap_out, 0)),
NULL),
:PRECISION))
period_act,
SUM (
ROUND (
DECODE (
ml_elem_type.lookup_code,
6, NVL (wpb.tl_material_var, 0)
+ NVL (wpb.tl_material_overhead_var, 0)
+ NVL (wpb.pl_material_var, 0)
+ NVL (wpb.pl_material_overhead_var, 0)
+ NVL (wpb.pl_resource_var, 0)
+ NVL (wpb.pl_overhead_var, 0)
+ NVL (wpb.pl_outside_processing_var, 0),
7, NVL (wpb.tl_resource_var, 0),
8, NVL (wpb.tl_outside_processing_var, 0),
9, NVL (wpb.tl_overhead_var, 0),
13, NVL (wpb.tl_scrap_var, 0),
NULL),
:PRECISION))
single_level
FROM wip_period_balances wpb,
wip_discrete_jobs wdj,
gl_code_combinations gcc,
mfg_lookups ml_elem_type
WHERE wdj.wip_entity_id = wpb.wip_entity_id
AND wdj.WIP_ENTITY_ID = 101791
AND wpb.organization_id = 123
AND ml_elem_type.lookup_type = 'WIP_ELEMENT_VAR_TYPE'
AND DECODE (ml_elem_type.lookup_code,
1, wdj.material_account,
2, wdj.material_overhead_account,
3, wdj.resource_account,
4, wdj.outside_processing_account,
5, wdj.overhead_account,
6, wdj.material_variance_account,
7, wdj.resource_variance_account,
8, wdj.outside_proc_variance_account,
9, wdj.overhead_variance_account,
12, wdj.est_scrap_account,
13, wdj.est_scrap_var_account) = gcc.code_combination_id
GROUP BY wdj.wip_entity_id,
wdj.organization_id,
ml_elem_type.meaning,
ml_elem_type.lookup_code
ORDER BY ml_elem_type.lookup_code
Related posts:
Upload your own post and refer it anywhere anytime:
Like this:
Like Loading...
