[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT /*+ append */ INTO opi_dbi_res_actual_stg (
resource_id,
organization_id,
transaction_date,
uom,
actual_qty_draft,
actual_qty,
actual_qty_g_draft,
actual_qty_g,
actual_val_b_draft,
actual_val_b,
source,
job_id,
job_type,
assembly_item_id,
department_id)
SELECT /*+ ORDERED */
rdtl.resource_id resource_id,
rdtl.organization_id organization_id,
TRUNC(rtran.trans_date) transaction_date,
rtran.trans_um uom,
0 actual_qty_draft,
sum(rtran.resource_usage * prod.cost_alloc) actual_qty,
0 actual_qty_g_draft,
sum(rtran.resource_usage * prod.cost_alloc * hruom.std_factor / ruom.std_factor) actual_qtg_g,
0 actual_val_b_draft,
sum(led.amount_base * led.debit_credit_sign) actual_val_b,
PRE_R12_opm_source source,
rtran.doc_id job_id,
4 job_type,
prod.inventory_item_id assembly_item_id,
rmst.resource_class department_id
FROM sy_uoms_mst hruom,
sy_uoms_mst ruom,
gme_resource_txns rtran,
cr_rsrc_dtl rdtl,
cr_rsrc_mst_b rmst,
gme_material_details prod,
gl_subr_led led
WHERE hruom.um_code = g_hr_uom
AND ruom.um_code = rtran.trans_um
AND rtran.completed_ind = 1
AND rdtl.orgn_code = rtran.orgn_code
AND rdtl.resources = rtran.resources
AND rmst.resources = rdtl.resources
AND prod.batch_id = rtran.doc_id
AND prod.line_type = 1
AND rtran.doc_id = led.doc_id -- new
AND rtran.line_id = led.line_id -- new
AND rtran.doc_type = led.doc_type -- new
AND rtran.trans_date = led.gl_trans_date --new
AND led.acct_ttl_type = 1530 --new (WIP)
AND led.sub_event_type = 50050 -- new (resource step ceritification)
GROUP BY
prod.inventory_item_id,
rtran.doc_id,
rdtl.resource_id,
rmst.resource_class,
rdtl.organization_id,
TRUNC(rtran.trans_date),
rtran.trans_um;