Diego79
04-03-2007, 01:40 AM
How do I get the batch header information?
SELECT
bhdr.plant_code,
bhdr.batch_no,
bhdr.batch_id,
nvl(bhdr.routing_id,0),
bhdr.batch_close_date,
bhdr.wip_whse_code,
decode(nvl(bhdr.poc_ind, 'N'), 'N', 0, 'Y', 1, 0) poc_indicator
FROM
gme_batch_header bhdr
WHERE
bhdr.plant_code = ‘&plant_code’
bhdr.batch_no = ‘&batch_no’
bhdr.batch_close_date is not null AND
bhdr.batch_status = 4 AND
bhdr.gl_posted_ind = 0 AND
nvl(bhdr.update_inventory_ind, 'N') = 'Y';
How do I get the batch material details?
SELECT
md.batch_id,
md.material_detail_id,
nvl(md.formulaline_id,0),
md.item_id,
md.line_type,
md.line_no,
md.plan_qty,
md.item_um,
md.actual_qty,
md.cost_alloc
FROM
gme_batch_header bh,
gme_material_details md
WHERE
bh.batch_id = md.batch_id AND
bh.batch_id = &batch_id
ORDER BY
md.batch_id,
md.line_type,
md.line_no
;
How do I get the batch WIP postings?
SELECT
led.doc_id,
led.ledger_code,
led.acctg_unit_id,
led.acct_id,
sum(led.debit_credit_sign * led.amount_base),
FROM
gl_subr_led led
WHERE
led.co_code = ‘&company_code’ AND
led.doc_type = 'PROD' AND
led.acct_ttl_type = 1530 AND /* GL_AT_WIP */
led.doc_id = &batch_id
GROUP BY
led.doc_id,
led.ledger_code,
led.acctg_unit_id,
led.acct_id;
How do I get formula effectivities?
SELECT
rvr.recipe_validity_rule_id,
r.recipe_id,
nvl(r.routing_id, 0),
r.formula_id,
nvl(rvr.planned_process_loss, 101)
FROM
gmd_recipe_validity_rules rvr, gmd_recipes_b r
WHERE rvr.recipe_id = r.recipe_id AND
r.delete_mark = 0 AND
rvr.recipe_validity_rule_id in (&formula effectivity id’s)
ORDER BY
rvr.recipe_validity_rule_id,
r.recipe_id
;
How do I get formula material details of costing formula and production formula?
SELECT
fm.formula_no,
fm.formula_vers,
md.formula_id,
md.formulaline_id,
md.line_type,
md.line_no,
md.item_id,
md.qty,
md.item_um,
md.scale_type,
md.scrap_factor,
nvl(md.contribute_yield_ind, 'Y'),
nvl(md.scale_multiple, 0),
nvl(md.scale_rounding_variance, 1),
nvl(md.rounding_direction, 0)
FROM
fm_form_mst_b fm,
fm_matl_dtl md
WHERE
fm.formula_id = md.formula_id AND
fm.formula_id in (&formula id’s)
ORDER BY
md.formula_id,
md.line_type,
md.line_no
;
How do I get formula operation details?SELECT
rh.routing_no,
rh.routing_vers,
rh.routing_qty,
rh.item_um,
rh.routing_id,
rh.process_loss,
rh.routing_class,
rd.routingstep_no,
rd.routingsy_um,
oa.activity_factor,
ors.oprn_line_id,
ors.resources,
ors.resource_count,
ors.resource_usage,
ors.process_qty,
ors.cost_cmpntcls_id,
ors.cost_analysis_code,
ors.usage_um,
ors.scale_type,
ors.min_capacity,
ors.max_capacity,
ors.capacity_uom
FROM
fm_rout_hdr rh,
fm_rout_dtl rd,
gmd_operations op,
gmd_operation_activities oa,
gmd_operation_resources ors,
gmd_status_b gs
WHERE
rh.routing_id = rd.routing_id AND
rh.routing_status = gs.status_code AND
rd.oprn_id = op.oprn_id AND
rd.oprn_id = oa.oprn_id AND
oa.oprn_line_id = ors.oprn_line_id and
rh.delete_mark = 0 and
oa.delete_mark = 0 and
rh.routing_id in (&Costing_routing_id, &Production_routing_id)
ORDER BY
rh.routing_id,
rd.routingstep_id,
rd.oprn_id,
oa.oprn_line_id,
ors.resources
;
How do I get the batch operation details?
SELECT
bh.batch_id,
nvl(bs.routingstep_id,0),
bs.oprn_id,
nvl(bs.actual_step_qty,0),
nvl(bsa.oprn_line_id,0),
nvl(bsa.plan_activity_factor,0),
nvl(bsa.actual_activity_factor,0),
bsr.resources,
bsr.cost_cmpntcls_id,
bsr.cost_analysis_code,
nvl(bsr.actual_rsrc_count,0),
nvl(bsr.actual_rsrc_qty,0),
nvl(bsr.actual_rsrc_usage,0) / decode(bsr.actual_rsrc_count,0,1,null,1,bsr.actual _rsrc_count) actual_rsrc_usage,
bsr.plan_rsrc_count,
bsr.plan_rsrc_qty,
bsr.plan_rsrc_usage,
bsr.usage_uom
FROM
gme_batch_header bh,
gme_batch_steps bs,
gme_batch_step_activities bsa,
gme_batch_step_resources bsr
WHERE
bh.batch_id = &batch_id AND
bh.batch_id = bs.batch_id AND
bs.batch_id = bsa.batch_id AND
bs.batchstep_id = bsa.batchstep_id AND
bsa.batchstep_activity_id = bsr.batchstep_activity_id AND
bh.batch_close_date is not null AND
bh.batch_status = 4 AND
bsa.delete_mark = 0
ORDER BY
bs.batch_id,
nvl(bs.routingstep_id,0),
bs.oprn_id,
bsa.oprn_line_id,
bsr.resources
;
How do I get the recipe details?
SELECT
rc.recipe_id,
rc.recipe_no,
rc.recipe_version,
rc.recipe_status,
NVL(rc.planned_process_loss, 101),
rc.calculate_step_quantity
FROM
gmd_status_b gs,
gmd_recipes_b rc
WHERE
rc.recipe_status = gs.status_code AND
rc.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rc.recipe_id
;
How do I get recipe process loss details?
SELECT DISTINCT
rpl.recipe_id,
rpl.orgn_code,
NVL(rpl.process_loss, 101)
FROM
gmd_recipe_process_loss rpl AND
WHERE
rpl.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rpl.recipe_id, rpl.orgn_code
;
How do I get recipe activity details?
SELECT
ra.recipe_id,
ra.orgn_code,
ra.routingstep_id,
ra.oprn_line_id,
ra.activity_factor WHERE
ra.recipe_amp;production_recie_r>ra._id
;
How do I get recipe resource details?
r>rr.oprn_line_id,
rr.resources,
rr.min_capacity,
rr.max_capacity,
rr.resource_usage,
rr.usage_um,
rr.process_qty,
rr.process_uom
FROM
gmd_recipe_orgn_resources rr
WHERE
rr.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rr.recipe_id,
rr.orgn_code, rr.oprn_liner;
How do I get recipe steps?
SELECT
rrs.recipe_id,
rrs.routingstep_id,
rrs.step_qty
FROM
gmd_recipe_routing_steps rrs
WHERE
rrs.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rrs.recipe_id,
rrs.routingstep_id
;
How do I get organization specific resource details?
SELECT
rd.orgn_code,
rd.resources,
rd.min_capacity,
rd.max_capacity,
rd.capacity_constraint,
rd.capacity_uom
FROM
cr_rsrc_dtl rd
ORDER BY
rd.orgn_code,
rd.resources
;
How do I get resource details?
SELECT
r.resources,
r.min_capacity,
r.max_capacity,
r.capacity_constraint,
r.capacity_uom
FROM
cr_rsrc_mst r
ORDER BY
r.resources
;
How do I get routing class process loss details?
SELECT
pl.routing_class,
NVL(pl.max_quantity, -1),
pl.process_loss
FROM
gmd_process_loss pl
ORDER BY
pl.routing_class, NVL(pl.max_quantity, -1) DESC
;
How do I get the item cost details?
SELECT
orgn_code,
whse_code,
item_id,
cost_mthd_code,
calendar_code,
period_code,
acctg_cost,
nvl(fmeff_id,0),
itemcost_id
FROM
gl_item_cst
WHERE
whse_code = ‘&whse_code’
AND item_id = ‘&item_id’
AND cost_mthd_code = ‘&cost_mthd_code’
AND calendar_code = ‘&cost_calendar_code’
AND period_code = ‘&cost_period_code’
AND delete_mark = 0
ORDER BY
orgn_code, whse_code, item_id, cost_mthd_code, calendar_code,
period_code;
How do I get the resource cost details?
SELECT
r.orgn_code,
r.resources,
r.calendar_code,
r.period_code,
r.cost_mthd_code,
r.usage_um,
r.nominal_cost
FROM
cm_rsrc_dtl r,
cm_cldr_dtl d
WHERE
r.resources = &resource_name and
r.resources = &plant_code and
r.calendar_code = d.calendar_code and
r.period_code = d.period_code and
d.start_date >= TO_DATE(&transaction_date, ‘dd-mon-yy hh24:mi:ss’) and
d.end_date <= TO_DATE(&transaction_date, ‘dd-mon-yy hh24:mi:ss’) and
r.delete_mark = 0 and
d.delete_mark = 0;
SELECT
bhdr.plant_code,
bhdr.batch_no,
bhdr.batch_id,
nvl(bhdr.routing_id,0),
bhdr.batch_close_date,
bhdr.wip_whse_code,
decode(nvl(bhdr.poc_ind, 'N'), 'N', 0, 'Y', 1, 0) poc_indicator
FROM
gme_batch_header bhdr
WHERE
bhdr.plant_code = ‘&plant_code’
bhdr.batch_no = ‘&batch_no’
bhdr.batch_close_date is not null AND
bhdr.batch_status = 4 AND
bhdr.gl_posted_ind = 0 AND
nvl(bhdr.update_inventory_ind, 'N') = 'Y';
How do I get the batch material details?
SELECT
md.batch_id,
md.material_detail_id,
nvl(md.formulaline_id,0),
md.item_id,
md.line_type,
md.line_no,
md.plan_qty,
md.item_um,
md.actual_qty,
md.cost_alloc
FROM
gme_batch_header bh,
gme_material_details md
WHERE
bh.batch_id = md.batch_id AND
bh.batch_id = &batch_id
ORDER BY
md.batch_id,
md.line_type,
md.line_no
;
How do I get the batch WIP postings?
SELECT
led.doc_id,
led.ledger_code,
led.acctg_unit_id,
led.acct_id,
sum(led.debit_credit_sign * led.amount_base),
FROM
gl_subr_led led
WHERE
led.co_code = ‘&company_code’ AND
led.doc_type = 'PROD' AND
led.acct_ttl_type = 1530 AND /* GL_AT_WIP */
led.doc_id = &batch_id
GROUP BY
led.doc_id,
led.ledger_code,
led.acctg_unit_id,
led.acct_id;
How do I get formula effectivities?
SELECT
rvr.recipe_validity_rule_id,
r.recipe_id,
nvl(r.routing_id, 0),
r.formula_id,
nvl(rvr.planned_process_loss, 101)
FROM
gmd_recipe_validity_rules rvr, gmd_recipes_b r
WHERE rvr.recipe_id = r.recipe_id AND
r.delete_mark = 0 AND
rvr.recipe_validity_rule_id in (&formula effectivity id’s)
ORDER BY
rvr.recipe_validity_rule_id,
r.recipe_id
;
How do I get formula material details of costing formula and production formula?
SELECT
fm.formula_no,
fm.formula_vers,
md.formula_id,
md.formulaline_id,
md.line_type,
md.line_no,
md.item_id,
md.qty,
md.item_um,
md.scale_type,
md.scrap_factor,
nvl(md.contribute_yield_ind, 'Y'),
nvl(md.scale_multiple, 0),
nvl(md.scale_rounding_variance, 1),
nvl(md.rounding_direction, 0)
FROM
fm_form_mst_b fm,
fm_matl_dtl md
WHERE
fm.formula_id = md.formula_id AND
fm.formula_id in (&formula id’s)
ORDER BY
md.formula_id,
md.line_type,
md.line_no
;
How do I get formula operation details?SELECT
rh.routing_no,
rh.routing_vers,
rh.routing_qty,
rh.item_um,
rh.routing_id,
rh.process_loss,
rh.routing_class,
rd.routingstep_no,
rd.routingsy_um,
oa.activity_factor,
ors.oprn_line_id,
ors.resources,
ors.resource_count,
ors.resource_usage,
ors.process_qty,
ors.cost_cmpntcls_id,
ors.cost_analysis_code,
ors.usage_um,
ors.scale_type,
ors.min_capacity,
ors.max_capacity,
ors.capacity_uom
FROM
fm_rout_hdr rh,
fm_rout_dtl rd,
gmd_operations op,
gmd_operation_activities oa,
gmd_operation_resources ors,
gmd_status_b gs
WHERE
rh.routing_id = rd.routing_id AND
rh.routing_status = gs.status_code AND
rd.oprn_id = op.oprn_id AND
rd.oprn_id = oa.oprn_id AND
oa.oprn_line_id = ors.oprn_line_id and
rh.delete_mark = 0 and
oa.delete_mark = 0 and
rh.routing_id in (&Costing_routing_id, &Production_routing_id)
ORDER BY
rh.routing_id,
rd.routingstep_id,
rd.oprn_id,
oa.oprn_line_id,
ors.resources
;
How do I get the batch operation details?
SELECT
bh.batch_id,
nvl(bs.routingstep_id,0),
bs.oprn_id,
nvl(bs.actual_step_qty,0),
nvl(bsa.oprn_line_id,0),
nvl(bsa.plan_activity_factor,0),
nvl(bsa.actual_activity_factor,0),
bsr.resources,
bsr.cost_cmpntcls_id,
bsr.cost_analysis_code,
nvl(bsr.actual_rsrc_count,0),
nvl(bsr.actual_rsrc_qty,0),
nvl(bsr.actual_rsrc_usage,0) / decode(bsr.actual_rsrc_count,0,1,null,1,bsr.actual _rsrc_count) actual_rsrc_usage,
bsr.plan_rsrc_count,
bsr.plan_rsrc_qty,
bsr.plan_rsrc_usage,
bsr.usage_uom
FROM
gme_batch_header bh,
gme_batch_steps bs,
gme_batch_step_activities bsa,
gme_batch_step_resources bsr
WHERE
bh.batch_id = &batch_id AND
bh.batch_id = bs.batch_id AND
bs.batch_id = bsa.batch_id AND
bs.batchstep_id = bsa.batchstep_id AND
bsa.batchstep_activity_id = bsr.batchstep_activity_id AND
bh.batch_close_date is not null AND
bh.batch_status = 4 AND
bsa.delete_mark = 0
ORDER BY
bs.batch_id,
nvl(bs.routingstep_id,0),
bs.oprn_id,
bsa.oprn_line_id,
bsr.resources
;
How do I get the recipe details?
SELECT
rc.recipe_id,
rc.recipe_no,
rc.recipe_version,
rc.recipe_status,
NVL(rc.planned_process_loss, 101),
rc.calculate_step_quantity
FROM
gmd_status_b gs,
gmd_recipes_b rc
WHERE
rc.recipe_status = gs.status_code AND
rc.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rc.recipe_id
;
How do I get recipe process loss details?
SELECT DISTINCT
rpl.recipe_id,
rpl.orgn_code,
NVL(rpl.process_loss, 101)
FROM
gmd_recipe_process_loss rpl AND
WHERE
rpl.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rpl.recipe_id, rpl.orgn_code
;
How do I get recipe activity details?
SELECT
ra.recipe_id,
ra.orgn_code,
ra.routingstep_id,
ra.oprn_line_id,
ra.activity_factor WHERE
ra.recipe_amp;production_recie_r>ra._id
;
How do I get recipe resource details?
r>rr.oprn_line_id,
rr.resources,
rr.min_capacity,
rr.max_capacity,
rr.resource_usage,
rr.usage_um,
rr.process_qty,
rr.process_uom
FROM
gmd_recipe_orgn_resources rr
WHERE
rr.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rr.recipe_id,
rr.orgn_code, rr.oprn_liner;
How do I get recipe steps?
SELECT
rrs.recipe_id,
rrs.routingstep_id,
rrs.step_qty
FROM
gmd_recipe_routing_steps rrs
WHERE
rrs.recipe_id in (&costing_recipe_id, &production_recipe_id)
ORDER BY
rrs.recipe_id,
rrs.routingstep_id
;
How do I get organization specific resource details?
SELECT
rd.orgn_code,
rd.resources,
rd.min_capacity,
rd.max_capacity,
rd.capacity_constraint,
rd.capacity_uom
FROM
cr_rsrc_dtl rd
ORDER BY
rd.orgn_code,
rd.resources
;
How do I get resource details?
SELECT
r.resources,
r.min_capacity,
r.max_capacity,
r.capacity_constraint,
r.capacity_uom
FROM
cr_rsrc_mst r
ORDER BY
r.resources
;
How do I get routing class process loss details?
SELECT
pl.routing_class,
NVL(pl.max_quantity, -1),
pl.process_loss
FROM
gmd_process_loss pl
ORDER BY
pl.routing_class, NVL(pl.max_quantity, -1) DESC
;
How do I get the item cost details?
SELECT
orgn_code,
whse_code,
item_id,
cost_mthd_code,
calendar_code,
period_code,
acctg_cost,
nvl(fmeff_id,0),
itemcost_id
FROM
gl_item_cst
WHERE
whse_code = ‘&whse_code’
AND item_id = ‘&item_id’
AND cost_mthd_code = ‘&cost_mthd_code’
AND calendar_code = ‘&cost_calendar_code’
AND period_code = ‘&cost_period_code’
AND delete_mark = 0
ORDER BY
orgn_code, whse_code, item_id, cost_mthd_code, calendar_code,
period_code;
How do I get the resource cost details?
SELECT
r.orgn_code,
r.resources,
r.calendar_code,
r.period_code,
r.cost_mthd_code,
r.usage_um,
r.nominal_cost
FROM
cm_rsrc_dtl r,
cm_cldr_dtl d
WHERE
r.resources = &resource_name and
r.resources = &plant_code and
r.calendar_code = d.calendar_code and
r.period_code = d.period_code and
d.start_date >= TO_DATE(&transaction_date, ‘dd-mon-yy hh24:mi:ss’) and
d.end_date <= TO_DATE(&transaction_date, ‘dd-mon-yy hh24:mi:ss’) and
r.delete_mark = 0 and
d.delete_mark = 0;