eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.GL_SUBR_TST_VW

Source

View Text - Preformatted


SELECT 1 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, b.plant_code orgn_code, b.batch_no doc_no, b.actual_start_date doc_date, to_char(d.line_no) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, b.actual_start_date trans_date, b.wip_whse_code whse_code, d.actual_qty trans_qty_usage, d.item_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, gme_batch_header b, gme_material_details d, ic_item_mst i WHERE s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.sub_event_type <> 50050 and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'PROD' and b.batch_id = s.doc_id and s.line_id = d.material_detail_id(+) and d.item_id = i.item_id(+) UNION ALL select 2 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, b.plant_code orgn_code, b.batch_no doc_no, b.actual_start_date doc_date, to_char(d.batchstep_resource_id) line_no, d.resources resource_item_no, cr.resource_desc resource_item_no_desc, b.actual_start_date trans_date, b.wip_whse_code whse_code, d.actual_rsrc_usage trans_qty_usage, d.usage_uom trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, gme_batch_header b, gme_batch_step_resources d, cr_rsrc_mst cr where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.sub_event_type = 50050 and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'PROD' and b.batch_id = s.doc_id and s.line_id = d.batchstep_resource_id and d.resources = cr.resources UNION ALL select 3 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, r.orgn_code orgn_code, r.recv_no doc_no, r.recv_date doc_date, to_char(d.line_no) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, d.recv_date, d.to_whse, d.recv_qty1 trans_qty_usage, d.recv_um1 trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, po_recv_dtl d, po_recv_hdr r, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'RECV' and d.recv_id = s.doc_id and d.line_id = s.line_id and d.recv_id = r.recv_id and d.item_id = i.item_id UNION ALL select 4 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, rh.orgn_code orgn_code, rh.return_no doc_no, rh.return_date doc_date, to_char(rd.line_no) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, rh.return_date, ' ', rd.return_qty1 trans_qty_usage, rd.return_um1 trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, po_rtrn_dtl rd, po_rtrn_hdr rh, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'RTRN' and rd.return_id = s.doc_id and rd.line_id = s.line_id and rd.return_id = rh.return_id and rd.item_id = i.item_id UNION ALL select 5 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, b.orgn_code orgn_code, b.bol_no doc_no, d.actual_shipdate doc_date, to_char(d.line_no) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, d.actual_shipdate, d.from_whse, d.ship_qty1 trans_qty_usage, d.order_um1 trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, op_ordr_dtl d, op_bill_lad b, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'OPSP' and d.bol_id = s.doc_id and d.bol_id = b.bol_id and d.line_id = s.line_id and d.item_id = i.item_id UNION ALL select 6 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, j.orgn_code orgn_code, j.journal_no doc_no, s.gl_trans_date doc_date, to_char(t.doc_line) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, t.trans_date trans_date, t.whse_code whse_code, t.trans_qty trans_qty_usage, t.trans_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_jrnl_mst j, ic_adjs_jnl d, ic_tran_cmp t, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type not in ('PIPH', 'PICY') and sr.trans_source_code = 'IC' and d.doc_id = s.doc_id and d.line_id = s.line_id and d.journal_id = j.journal_id and t.doc_type = s.doc_type and t.doc_id = s.doc_id and t.line_id = s.line_id and t.item_id = i.item_id UNION ALL select 6 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, dx.orgn_code orgn_code, dx.transfer_number doc_no, dx.trans_date doc_date, to_char(dxl.line_no) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, dx.trans_date trans_date, dxl.opm_whse_code whse_code, dxl.quantity trans_qty_usage, dxl.quantity_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, gmi_discrete_transfers dx, gmi_discrete_transfer_lines dxl, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'DXFR' and sr.trans_source_code = 'IC' and dx.transfer_id = s.doc_id and dx.transfer_id = dxl.transfer_id and dxl.line_id = s.line_id and dxl.opm_item_id = i.item_id UNION ALL select 6 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, t.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, t.orgn_code orgn_code, to_char(t.doc_id) doc_no, s.gl_trans_date doc_date, to_char(1) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, t.trans_date trans_date, t.whse_code whse_code, t.trans_qty trans_qty_usage, t.trans_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_tran_cmp t, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'CONV' and sr.trans_source_code = 'IC' and t.doc_type = s.doc_type and t.doc_id = s.doc_id and t.line_detail_id = s.line_id and t.item_id = i.item_id UNION ALL select 7 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, TO_CHAR(NULL) orgn_code, wh.whse_code doc_no, s.gl_trans_date doc_date, '1' line_no, im.item_no resource_item_no, im.item_desc1 resource_item_no_desc, s.gl_trans_date trans_date, wh.whse_code whse_code, to_number(NULL) trans_qty_usage, im.item_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_item_mst im, ic_whse_mst wh where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'RVAL' and s.doc_id <> -9 and s.line_id = im.item_id and s.doc_id = wh.mtl_organization_id UNION ALL select 7 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, TO_CHAR(NULL) orgn_code, to_char(lca.adjustment_id) doc_no, s.gl_trans_date doc_date, '1' line_no, im.item_no resource_item_no, im.item_desc1 resource_item_no_desc, lca.adjustment_date trans_date, lca.whse_code whse_code, to_number(NULL) trans_qty_usage, im.item_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_item_mst im, gmf_lot_cost_adjustments lca where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'RVAL' and s.doc_id = -9 and s.line_id = lca.adjustment_id and lca.item_id = im.item_id UNION ALL select 7 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, s.co_code orgn_code, to_char(s.reference_no) doc_no, s.gl_trans_date doc_date, to_char(NULL) line_no, to_char(NULL) resource_item_no, to_char(NULL) resource_item_no_desc, s.gl_trans_date trans_date, to_char(NULL) whse_code, to_number(NULL) trans_qty_usage, to_char(NULL) trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type in ( 'PICY', 'PIPH') UNION ALL select 8 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, t.trans_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, x.orgn_code orgn_code, x.transfer_no doc_no, decode( x.transfer_status, '2', x.actual_release_date, '3', x.actual_receive_date, '5',x.cancel_date, to_date(NULL) ) doc_date, '1' line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, t.trans_date trans_date, t.whse_code whse_code, t.trans_qty trans_qty_usage, t.trans_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_xfer_mst x, ic_tran_pnd t, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'XFER' and sr.trans_source_code = 'IC' and x.transfer_id = s.doc_id and t.doc_type = s.doc_type and t.doc_id = s.doc_id and t.line_id = s.line_id and t.item_id = i.item_id UNION ALL select 9 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, j.orgn_code orgn_code, j.journal_no doc_no, s.gl_trans_date doc_date, to_char(t.doc_line) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, t.trans_date trans_date, t.whse_code whse_code, t.trans_qty trans_qty_usage, t.trans_um trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_jrnl_mst j, ic_adjs_jnl d, ic_tran_pnd t, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type not in ('PIPH', 'PICY') and sr.trans_source_code = 'IC' and d.doc_id = s.doc_id and d.line_id = s.line_id and d.journal_id = j.journal_id and t.doc_type = s.doc_type and t.doc_id = s.doc_id and t.line_id = s.line_id and t.item_id = i.item_id UNION ALL select 11 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, t.orgn_code orgn_code, t.name doc_no, s.gl_trans_date doc_date, t.line_no line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, s.gl_trans_date trans_date, t.whse_code, t.trans_qty_usage, t.trans_qty_usage_um, s.reference_no from gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_tran_pnd_om_vw1 t, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'OMSO' and s.doc_type = t.doc_type and s.line_id = t.line_id and t.item_id = i.item_id and s.doc_id = t.delivery_id UNION ALL select 11 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, '' orgn_code, RPAD(pnd.whse_code,4,' ') || ' ' || rsh.receipt_num doc_no, rsh.shipped_date doc_date, to_char(rsl.line_num) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, t.transaction_date recv_date, pnd.whse_code to_whse, t.quantity trans_qty_usage, uom.um_code trans_qty_usage_um, s.reference_no From gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_tran_pnd pnd, rcv_transactions t, rcv_shipment_headers rsh, rcv_shipment_lines rsl, sy_uoms_mst uom, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'PORC' and pnd.doc_type = s.doc_type and pnd.doc_id = s.doc_id and pnd.line_id = s.line_id and pnd.line_id = t.transaction_id and t.shipment_header_id = rsh.shipment_header_id and t.shipment_line_id = rsl.shipment_line_id and rsh.receipt_source_code in ('CUSTOMER') and t.source_document_code in ('RMA') and pnd.item_id = i.item_id and t.unit_of_measure = uom.unit_of_measure group by s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity , s.quantity_um , sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, RPAD(pnd.whse_code,4,' ') || ' ' || rsh.receipt_num , rsh.shipped_date , rsl.line_num , i.item_no , i.item_desc1 , t.transaction_date , pnd.whse_code , t.quantity , uom.um_code , s.reference_no UNION ALL select 12 as source, s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity jv_quantity, s.quantity_um jv_quantity_um, sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, '' orgn_code, RPAD(pnd.whse_code,4,' ') || ' ' || rsh.receipt_num doc_no, rsh.shipped_date doc_date, to_char(rsl.line_num) line_no, i.item_no resource_item_no, i.item_desc1 resource_item_no_desc, t.transaction_date recv_date, pnd.whse_code to_whse, t.quantity trans_qty_usage, uom.um_code trans_qty_usage_um, s.reference_no From gl_subr_tst s, gl_acct_mst a, gl_accu_mst au, gl_acct_ttl at, gl_sevt_mst sb, gl_srce_mst sr, ic_tran_pnd pnd, rcv_transactions t, rcv_shipment_headers rsh, rcv_shipment_lines rsl, sy_uoms_mst uom, ic_item_mst i where s.acct_ttl_type = at.acct_ttl_type and s.sub_event_type = sb.sub_event_type and s.trans_source_type = sr.trans_source_type and s.acct_id = a.acct_id and s.acctg_unit_id = au.acctg_unit_id and s.doc_type = 'PORC' and pnd.doc_type = s.doc_type and pnd.doc_id = s.doc_id and pnd.line_id = s.line_id and pnd.line_id = t.transaction_id and t.shipment_header_id = rsh.shipment_header_id and t.shipment_line_id = rsl.shipment_line_id and rsh.receipt_source_code in ('VENDOR','INTERNAL ORDER') and t.source_document_code in ('PO','REQ') and pnd.item_id = i.item_id and t.unit_of_measure = uom.unit_of_measure group by s.subledger_id, s.co_code, s.fiscal_year, s.period, sb.sub_event_code, s.voucher_id, s.doc_type, s.doc_id, s.line_id, at.acct_ttl_code, au.acctg_unit_no, a.acct_no, s.debit_credit_sign, au.acctg_unit_desc, a.acct_desc, s.amount_base, s.amount_trans, s.currency_base, s.currency_trans, s.quantity , s.quantity_um , sb.sub_event_desc, sr.trans_source_code, sr.trans_source_desc, s.gl_trans_date, RPAD(pnd.whse_code,4,' ') || ' ' || rsh.receipt_num , rsh.shipped_date , rsl.line_num , i.item_no , i.item_desc1 , t.transaction_date , pnd.whse_code , t.quantity , uom.um_code , s.reference_no

View Text - HTML Formatted

SELECT 1 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, B.PLANT_CODE ORGN_CODE
, B.BATCH_NO DOC_NO
, B.ACTUAL_START_DATE DOC_DATE
, TO_CHAR(D.LINE_NO) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, B.ACTUAL_START_DATE TRANS_DATE
, B.WIP_WHSE_CODE WHSE_CODE
, D.ACTUAL_QTY TRANS_QTY_USAGE
, D.ITEM_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, GME_BATCH_HEADER B
, GME_MATERIAL_DETAILS D
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.SUB_EVENT_TYPE <> 50050
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'PROD'
AND B.BATCH_ID = S.DOC_ID
AND S.LINE_ID = D.MATERIAL_DETAIL_ID(+)
AND D.ITEM_ID = I.ITEM_ID(+) UNION ALL SELECT 2 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, B.PLANT_CODE ORGN_CODE
, B.BATCH_NO DOC_NO
, B.ACTUAL_START_DATE DOC_DATE
, TO_CHAR(D.BATCHSTEP_RESOURCE_ID) LINE_NO
, D.RESOURCES RESOURCE_ITEM_NO
, CR.RESOURCE_DESC RESOURCE_ITEM_NO_DESC
, B.ACTUAL_START_DATE TRANS_DATE
, B.WIP_WHSE_CODE WHSE_CODE
, D.ACTUAL_RSRC_USAGE TRANS_QTY_USAGE
, D.USAGE_UOM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, GME_BATCH_HEADER B
, GME_BATCH_STEP_RESOURCES D
, CR_RSRC_MST CR
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.SUB_EVENT_TYPE = 50050
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'PROD'
AND B.BATCH_ID = S.DOC_ID
AND S.LINE_ID = D.BATCHSTEP_RESOURCE_ID
AND D.RESOURCES = CR.RESOURCES UNION ALL SELECT 3 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, R.ORGN_CODE ORGN_CODE
, R.RECV_NO DOC_NO
, R.RECV_DATE DOC_DATE
, TO_CHAR(D.LINE_NO) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, D.RECV_DATE
, D.TO_WHSE
, D.RECV_QTY1 TRANS_QTY_USAGE
, D.RECV_UM1 TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, PO_RECV_DTL D
, PO_RECV_HDR R
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'RECV'
AND D.RECV_ID = S.DOC_ID
AND D.LINE_ID = S.LINE_ID
AND D.RECV_ID = R.RECV_ID
AND D.ITEM_ID = I.ITEM_ID UNION ALL SELECT 4 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, RH.ORGN_CODE ORGN_CODE
, RH.RETURN_NO DOC_NO
, RH.RETURN_DATE DOC_DATE
, TO_CHAR(RD.LINE_NO) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, RH.RETURN_DATE
, ' '
, RD.RETURN_QTY1 TRANS_QTY_USAGE
, RD.RETURN_UM1 TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, PO_RTRN_DTL RD
, PO_RTRN_HDR RH
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'RTRN'
AND RD.RETURN_ID = S.DOC_ID
AND RD.LINE_ID = S.LINE_ID
AND RD.RETURN_ID = RH.RETURN_ID
AND RD.ITEM_ID = I.ITEM_ID UNION ALL SELECT 5 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, B.ORGN_CODE ORGN_CODE
, B.BOL_NO DOC_NO
, D.ACTUAL_SHIPDATE DOC_DATE
, TO_CHAR(D.LINE_NO) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, D.ACTUAL_SHIPDATE
, D.FROM_WHSE
, D.SHIP_QTY1 TRANS_QTY_USAGE
, D.ORDER_UM1 TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, OP_ORDR_DTL D
, OP_BILL_LAD B
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'OPSP'
AND D.BOL_ID = S.DOC_ID
AND D.BOL_ID = B.BOL_ID
AND D.LINE_ID = S.LINE_ID
AND D.ITEM_ID = I.ITEM_ID UNION ALL SELECT 6 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, J.ORGN_CODE ORGN_CODE
, J.JOURNAL_NO DOC_NO
, S.GL_TRANS_DATE DOC_DATE
, TO_CHAR(T.DOC_LINE) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, T.TRANS_DATE TRANS_DATE
, T.WHSE_CODE WHSE_CODE
, T.TRANS_QTY TRANS_QTY_USAGE
, T.TRANS_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_JRNL_MST J
, IC_ADJS_JNL D
, IC_TRAN_CMP T
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE NOT IN ('PIPH'
, 'PICY')
AND SR.TRANS_SOURCE_CODE = 'IC'
AND D.DOC_ID = S.DOC_ID
AND D.LINE_ID = S.LINE_ID
AND D.JOURNAL_ID = J.JOURNAL_ID
AND T.DOC_TYPE = S.DOC_TYPE
AND T.DOC_ID = S.DOC_ID
AND T.LINE_ID = S.LINE_ID
AND T.ITEM_ID = I.ITEM_ID UNION ALL SELECT 6 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, DX.ORGN_CODE ORGN_CODE
, DX.TRANSFER_NUMBER DOC_NO
, DX.TRANS_DATE DOC_DATE
, TO_CHAR(DXL.LINE_NO) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, DX.TRANS_DATE TRANS_DATE
, DXL.OPM_WHSE_CODE WHSE_CODE
, DXL.QUANTITY TRANS_QTY_USAGE
, DXL.QUANTITY_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, GMI_DISCRETE_TRANSFERS DX
, GMI_DISCRETE_TRANSFER_LINES DXL
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'DXFR'
AND SR.TRANS_SOURCE_CODE = 'IC'
AND DX.TRANSFER_ID = S.DOC_ID
AND DX.TRANSFER_ID = DXL.TRANSFER_ID
AND DXL.LINE_ID = S.LINE_ID
AND DXL.OPM_ITEM_ID = I.ITEM_ID UNION ALL SELECT 6 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, T.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, T.ORGN_CODE ORGN_CODE
, TO_CHAR(T.DOC_ID) DOC_NO
, S.GL_TRANS_DATE DOC_DATE
, TO_CHAR(1) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, T.TRANS_DATE TRANS_DATE
, T.WHSE_CODE WHSE_CODE
, T.TRANS_QTY TRANS_QTY_USAGE
, T.TRANS_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_TRAN_CMP T
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'CONV'
AND SR.TRANS_SOURCE_CODE = 'IC'
AND T.DOC_TYPE = S.DOC_TYPE
AND T.DOC_ID = S.DOC_ID
AND T.LINE_DETAIL_ID = S.LINE_ID
AND T.ITEM_ID = I.ITEM_ID UNION ALL SELECT 7 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, TO_CHAR(NULL) ORGN_CODE
, WH.WHSE_CODE DOC_NO
, S.GL_TRANS_DATE DOC_DATE
, '1' LINE_NO
, IM.ITEM_NO RESOURCE_ITEM_NO
, IM.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, S.GL_TRANS_DATE TRANS_DATE
, WH.WHSE_CODE WHSE_CODE
, TO_NUMBER(NULL) TRANS_QTY_USAGE
, IM.ITEM_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_ITEM_MST IM
, IC_WHSE_MST WH
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'RVAL'
AND S.DOC_ID <> -9
AND S.LINE_ID = IM.ITEM_ID
AND S.DOC_ID = WH.MTL_ORGANIZATION_ID UNION ALL SELECT 7 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, TO_CHAR(NULL) ORGN_CODE
, TO_CHAR(LCA.ADJUSTMENT_ID) DOC_NO
, S.GL_TRANS_DATE DOC_DATE
, '1' LINE_NO
, IM.ITEM_NO RESOURCE_ITEM_NO
, IM.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, LCA.ADJUSTMENT_DATE TRANS_DATE
, LCA.WHSE_CODE WHSE_CODE
, TO_NUMBER(NULL) TRANS_QTY_USAGE
, IM.ITEM_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_ITEM_MST IM
, GMF_LOT_COST_ADJUSTMENTS LCA
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'RVAL'
AND S.DOC_ID = -9
AND S.LINE_ID = LCA.ADJUSTMENT_ID
AND LCA.ITEM_ID = IM.ITEM_ID UNION ALL SELECT 7 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, S.CO_CODE ORGN_CODE
, TO_CHAR(S.REFERENCE_NO) DOC_NO
, S.GL_TRANS_DATE DOC_DATE
, TO_CHAR(NULL) LINE_NO
, TO_CHAR(NULL) RESOURCE_ITEM_NO
, TO_CHAR(NULL) RESOURCE_ITEM_NO_DESC
, S.GL_TRANS_DATE TRANS_DATE
, TO_CHAR(NULL) WHSE_CODE
, TO_NUMBER(NULL) TRANS_QTY_USAGE
, TO_CHAR(NULL) TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE IN ( 'PICY'
, 'PIPH') UNION ALL SELECT 8 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, T.TRANS_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, X.ORGN_CODE ORGN_CODE
, X.TRANSFER_NO DOC_NO
, DECODE( X.TRANSFER_STATUS
, '2'
, X.ACTUAL_RELEASE_DATE
, '3'
, X.ACTUAL_RECEIVE_DATE
, '5'
, X.CANCEL_DATE
, TO_DATE(NULL) ) DOC_DATE
, '1' LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, T.TRANS_DATE TRANS_DATE
, T.WHSE_CODE WHSE_CODE
, T.TRANS_QTY TRANS_QTY_USAGE
, T.TRANS_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_XFER_MST X
, IC_TRAN_PND T
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'XFER'
AND SR.TRANS_SOURCE_CODE = 'IC'
AND X.TRANSFER_ID = S.DOC_ID
AND T.DOC_TYPE = S.DOC_TYPE
AND T.DOC_ID = S.DOC_ID
AND T.LINE_ID = S.LINE_ID
AND T.ITEM_ID = I.ITEM_ID UNION ALL SELECT 9 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, J.ORGN_CODE ORGN_CODE
, J.JOURNAL_NO DOC_NO
, S.GL_TRANS_DATE DOC_DATE
, TO_CHAR(T.DOC_LINE) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, T.TRANS_DATE TRANS_DATE
, T.WHSE_CODE WHSE_CODE
, T.TRANS_QTY TRANS_QTY_USAGE
, T.TRANS_UM TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_JRNL_MST J
, IC_ADJS_JNL D
, IC_TRAN_PND T
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE NOT IN ('PIPH'
, 'PICY')
AND SR.TRANS_SOURCE_CODE = 'IC'
AND D.DOC_ID = S.DOC_ID
AND D.LINE_ID = S.LINE_ID
AND D.JOURNAL_ID = J.JOURNAL_ID
AND T.DOC_TYPE = S.DOC_TYPE
AND T.DOC_ID = S.DOC_ID
AND T.LINE_ID = S.LINE_ID
AND T.ITEM_ID = I.ITEM_ID UNION ALL SELECT 11 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, T.ORGN_CODE ORGN_CODE
, T.NAME DOC_NO
, S.GL_TRANS_DATE DOC_DATE
, T.LINE_NO LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, S.GL_TRANS_DATE TRANS_DATE
, T.WHSE_CODE
, T.TRANS_QTY_USAGE
, T.TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_TRAN_PND_OM_VW1 T
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'OMSO'
AND S.DOC_TYPE = T.DOC_TYPE
AND S.LINE_ID = T.LINE_ID
AND T.ITEM_ID = I.ITEM_ID
AND S.DOC_ID = T.DELIVERY_ID UNION ALL SELECT 11 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, '' ORGN_CODE
, RPAD(PND.WHSE_CODE
, 4
, ' ') || ' ' || RSH.RECEIPT_NUM DOC_NO
, RSH.SHIPPED_DATE DOC_DATE
, TO_CHAR(RSL.LINE_NUM) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, T.TRANSACTION_DATE RECV_DATE
, PND.WHSE_CODE TO_WHSE
, T.QUANTITY TRANS_QTY_USAGE
, UOM.UM_CODE TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_TRAN_PND PND
, RCV_TRANSACTIONS T
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, SY_UOMS_MST UOM
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'PORC'
AND PND.DOC_TYPE = S.DOC_TYPE
AND PND.DOC_ID = S.DOC_ID
AND PND.LINE_ID = S.LINE_ID
AND PND.LINE_ID = T.TRANSACTION_ID
AND T.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND T.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('CUSTOMER')
AND T.SOURCE_DOCUMENT_CODE IN ('RMA')
AND PND.ITEM_ID = I.ITEM_ID
AND T.UNIT_OF_MEASURE = UOM.UNIT_OF_MEASURE GROUP BY S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY
, S.QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, RPAD(PND.WHSE_CODE
, 4
, ' ') || ' ' || RSH.RECEIPT_NUM
, RSH.SHIPPED_DATE
, RSL.LINE_NUM
, I.ITEM_NO
, I.ITEM_DESC1
, T.TRANSACTION_DATE
, PND.WHSE_CODE
, T.QUANTITY
, UOM.UM_CODE
, S.REFERENCE_NO UNION ALL SELECT 12 AS SOURCE
, S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY JV_QUANTITY
, S.QUANTITY_UM JV_QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, '' ORGN_CODE
, RPAD(PND.WHSE_CODE
, 4
, ' ') || ' ' || RSH.RECEIPT_NUM DOC_NO
, RSH.SHIPPED_DATE DOC_DATE
, TO_CHAR(RSL.LINE_NUM) LINE_NO
, I.ITEM_NO RESOURCE_ITEM_NO
, I.ITEM_DESC1 RESOURCE_ITEM_NO_DESC
, T.TRANSACTION_DATE RECV_DATE
, PND.WHSE_CODE TO_WHSE
, T.QUANTITY TRANS_QTY_USAGE
, UOM.UM_CODE TRANS_QTY_USAGE_UM
, S.REFERENCE_NO
FROM GL_SUBR_TST S
, GL_ACCT_MST A
, GL_ACCU_MST AU
, GL_ACCT_TTL AT
, GL_SEVT_MST SB
, GL_SRCE_MST SR
, IC_TRAN_PND PND
, RCV_TRANSACTIONS T
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, SY_UOMS_MST UOM
, IC_ITEM_MST I
WHERE S.ACCT_TTL_TYPE = AT.ACCT_TTL_TYPE
AND S.SUB_EVENT_TYPE = SB.SUB_EVENT_TYPE
AND S.TRANS_SOURCE_TYPE = SR.TRANS_SOURCE_TYPE
AND S.ACCT_ID = A.ACCT_ID
AND S.ACCTG_UNIT_ID = AU.ACCTG_UNIT_ID
AND S.DOC_TYPE = 'PORC'
AND PND.DOC_TYPE = S.DOC_TYPE
AND PND.DOC_ID = S.DOC_ID
AND PND.LINE_ID = S.LINE_ID
AND PND.LINE_ID = T.TRANSACTION_ID
AND T.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND T.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('VENDOR'
, 'INTERNAL ORDER')
AND T.SOURCE_DOCUMENT_CODE IN ('PO'
, 'REQ')
AND PND.ITEM_ID = I.ITEM_ID
AND T.UNIT_OF_MEASURE = UOM.UNIT_OF_MEASURE GROUP BY S.SUBLEDGER_ID
, S.CO_CODE
, S.FISCAL_YEAR
, S.PERIOD
, SB.SUB_EVENT_CODE
, S.VOUCHER_ID
, S.DOC_TYPE
, S.DOC_ID
, S.LINE_ID
, AT.ACCT_TTL_CODE
, AU.ACCTG_UNIT_NO
, A.ACCT_NO
, S.DEBIT_CREDIT_SIGN
, AU.ACCTG_UNIT_DESC
, A.ACCT_DESC
, S.AMOUNT_BASE
, S.AMOUNT_TRANS
, S.CURRENCY_BASE
, S.CURRENCY_TRANS
, S.QUANTITY
, S.QUANTITY_UM
, SB.SUB_EVENT_DESC
, SR.TRANS_SOURCE_CODE
, SR.TRANS_SOURCE_DESC
, S.GL_TRANS_DATE
, RPAD(PND.WHSE_CODE
, 4
, ' ') || ' ' || RSH.RECEIPT_NUM
, RSH.SHIPPED_DATE
, RSL.LINE_NUM
, I.ITEM_NO
, I.ITEM_DESC1
, T.TRANSACTION_DATE
, PND.WHSE_CODE
, T.QUANTITY
, UOM.UM_CODE
, S.REFERENCE_NO