[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Dynamically order the report based upon the parameter selected.
Cursor for running report on actual subledger table */
CURSOR c_gl_subr_led_vw IS
SELECT
co_code,
fiscal_year,
period,
sub_event_code,
voucher_id,
doc_type,
doc_id,
line_id,
acct_ttl_code,
acctg_unit_no,
acct_no,
DECODE(SUM(amount_base*debit_credit_sign), 0, 1,
(SUM(amount_base*debit_credit_sign)/ABS(SUM(amount_base*debit_credit_sign)))) debit_credit_sign,
acctg_unit_desc,
acct_desc,
ABS(SUM(amount_base*debit_credit_sign)) amount_base,
ABS(SUM(amount_trans*debit_credit_sign)) amount_trans,
currency_base,
currency_trans,
SUM(jv_quantity) jv_quantity,
jv_quantity_um,
sub_event_desc,
trans_source_code,
trans_source_desc,
-- gl_trans_date,
orgn_code,
doc_no,
doc_date,
line_no,
resource_item_no,
resource_item_no_desc,
trans_date,
whse_code,
trans_qty_usage,
trans_qty_usage_um,
reference_no
FROM gl_subr_led_vw
WHERE
reference_no = nvl(preference_no,reference_no) and --bug# 1801491
co_code = pco_code and
fiscal_year = to_number(pfiscal_year) and
period = to_number(pperiod) and
gl_trans_date >= vstart_date and
gl_trans_date <= vend_date and
trans_source_code >= nvl(pfrom_source_code, trans_source_code) and
trans_source_code <= nvl(pto_source_code, trans_source_code) and
sub_event_code >= nvl(pfrom_sub_event_code, sub_event_code) and
sub_event_code <= nvl(pto_sub_event_code, sub_event_code) and
nvl(voucher_id,-99) >= nvl(to_number(pfrom_voucher_no), nvl(voucher_id,-99)) and
nvl(voucher_id,-99) <= nvl(to_number(pto_voucher_no), nvl(voucher_id,-99))
GROUP BY
co_code,
fiscal_year,
period,
decode(rep_mode, 'SDV',sub_event_code, 'SVD',sub_event_code,
'VSD',nvl(voucher_id,0), 'VDS',nvl(voucher_id,0),
'DSV',doc_type||doc_no, 'DVS',doc_type||doc_no),
decode(rep_mode, 'SDV',doc_type||doc_no, 'SVD',nvl(voucher_id,0),
'VSD',sub_event_code, 'VDS', doc_type||doc_no,
'DSV',sub_event_code, 'DVS',nvl(voucher_id,0)),
decode(rep_mode, 'SVD',doc_type||doc_no, 'SDV',nvl(voucher_id,0),
'VSD',doc_type||doc_no, 'VDS',sub_event_code,
'DSV',nvl(voucher_id,0), 'DVS',sub_event_code),
line_id, /* Everything from this point onwards will have no effect on the ordering */
sub_event_code,
voucher_id,
doc_type,
doc_no,
doc_id,
acct_ttl_code,
acctg_unit_no,
acct_no,
acctg_unit_desc,
acct_desc,
currency_base,
currency_trans,
jv_quantity_um,
sub_event_desc,
trans_source_code,
trans_source_desc,
-- gl_trans_date,
orgn_code,
doc_date,
line_no,
resource_item_no,
resource_item_no_desc,
trans_date,
whse_code,
trans_qty_usage,
trans_qty_usage_um,
reference_no;
SELECT
co_code,
fiscal_year,
period,
sub_event_code,
voucher_id,
doc_type,
doc_id,
line_id,
acct_ttl_code,
acctg_unit_no,
acct_no,
DECODE(SUM(amount_base*debit_credit_sign), 0, 1,
(SUM(amount_base*debit_credit_sign)/ABS(SUM(amount_base*debit_credit_sign)))) debit_credit_sign,
acctg_unit_desc,
acct_desc,
ABS(SUM(amount_base*debit_credit_sign)) amount_base,
ABS(SUM(amount_trans*debit_credit_sign)) amount_trans,
currency_base,
currency_trans,
SUM(jv_quantity) jv_quantity,
jv_quantity_um,
sub_event_desc,
trans_source_code,
trans_source_desc,
-- gl_trans_date,
orgn_code,
doc_no,
doc_date,
line_no,
resource_item_no,
resource_item_no_desc,
trans_date,
whse_code,
trans_qty_usage,
trans_qty_usage_um,
reference_no
FROM gl_subr_tst_vw
WHERE
reference_no = nvl(preference_no,reference_no) and --bug# 1801491
co_code = pco_code and
fiscal_year = to_number(pfiscal_year) and
period = to_number(pperiod) and
gl_trans_date >= vstart_date and
gl_trans_date <= vend_date and
trans_source_code >= nvl(pfrom_source_code, trans_source_code) and
trans_source_code <= nvl(pto_source_code, trans_source_code) and
sub_event_code >= nvl(pfrom_sub_event_code, sub_event_code) and
sub_event_code <= nvl(pto_sub_event_code, sub_event_code) and
nvl(voucher_id,-99) >= nvl(to_number(pfrom_voucher_no), nvl(voucher_id,-99)) and
nvl(voucher_id,-99) <= nvl(to_number(pto_voucher_no), nvl(voucher_id,-99))
GROUP BY
co_code,
fiscal_year,
period,
decode(rep_mode, 'SDV',sub_event_code, 'SVD',sub_event_code,
'VSD',nvl(voucher_id,0), 'VDS',nvl(voucher_id,0),
'DSV',doc_type||doc_no, 'DVS',doc_type||doc_no),
decode(rep_mode, 'SDV',doc_type||doc_no, 'SVD',nvl(voucher_id,0),
'VSD',sub_event_code, 'VDS', doc_type||doc_no,
'DSV',sub_event_code, 'DVS',nvl(voucher_id,0)),
decode(rep_mode, 'SVD',doc_type||doc_no, 'SDV',nvl(voucher_id,0),
'VSD',doc_type||doc_no, 'VDS',sub_event_code,
'DSV',nvl(voucher_id,0), 'DVS',sub_event_code),
line_id, /* Everything from this point onwards will have no effect on the ordering */
sub_event_code,
voucher_id,
doc_type,
doc_no,
doc_id,
acct_ttl_code,
acctg_unit_no,
acct_no,
acctg_unit_desc,
acct_desc,
currency_base,
currency_trans,
jv_quantity_um,
sub_event_desc,
trans_source_code,
trans_source_desc,
-- gl_trans_date,
orgn_code,
doc_date,
line_no,
resource_item_no,
resource_item_no_desc,
trans_date,
whse_code,
trans_qty_usage,
trans_qty_usage_um,
reference_no;
SELECT
h.billing_currency billing_currency,
h.receipt_exchange_rate receipt_exchange_rate,
v.vendor_no vendor_no,
v.vendor_name vendor_name,
nvl(d.po_id,0) po_id,
nvl(d.poline_id,0) poline_id
FROM
po_recv_dtl d,
po_recv_hdr h,
po_vend_mst v
WHERE
d.recv_id = v_doc_id
AND d.line_id = v_line_id
AND d.recv_id = h.recv_id
AND d.shipvend_id = v.vendor_id;
SELECT
h.orgn_code orgn_code,
h.po_no po_no
FROM
po_ordr_hdr h
WHERE
h.po_id = v_po_id;
SELECT
r.orgn_code orgn_code,
r.recv_no recv_no,
r.recv_date recv_date,
r.billing_currency billing_currency,
r.receipt_exchange_rate receipt_exchange_rate,
v.vendor_no vendor_no,
v.vendor_name vendor_name,
nvl(rd.po_id, 0) po_id,
nvl(rd.poline_id,0) poline_id
FROM
po_rtrn_dtl rd,
po_rtrn_hdr rh,
po_recv_hdr r,
po_vend_mst v
WHERE
rd.line_id = v_line_id
AND rd.return_id = v_doc_id
AND rd.recv_id = r.recv_id
AND rh.return_vendor_id = v.vendor_id;
SELECT
h.orgn_code orgn_code,
h.order_no order_no,
h.order_date order_date,
c.cust_no as cust_no,
c.cust_name cust_name
FROM
op_ordr_dtl d,
op_ordr_hdr h,
op_cust_mst c
WHERE
d.line_id = v_line_id
AND d.bol_id = v_doc_id
AND d.order_id = h.order_id
AND d.shipcust_id = c.cust_id;
SELECT
b.plant_code ,
b.batch_no ,
b.wip_whse_code,
b.actual_start_date,
b.actual_cmplt_date,
nvl(b.routing_id,0) routing_id,
f.formula_no,
f.formula_vers,
t.meaning
FROM
gme_batch_header b,
fm_form_mst f,
gem_lookups t
WHERE
b.batch_id = v_batch_id
AND b.formula_id = f.formula_id
AND to_char(b.batch_status) = t.lookup_code
AND t.lookup_type = upper('batch_status');
SELECT
r.routing_no,
r.routing_vers
FROM
fm_rout_hdr r
WHERE
r.routing_id = v_routing_id;
SELECT
nvl(t.currency_conversion_rate, 1.0) exchange_rate,
t.currency_code billing_currency,
NVL(poh.segment1,' ') po_no,
NVL(v.vendor_no,' ') vendor_no,
NVL(v.vendor_name,' ') vendor_name,
nvl(t.po_unit_price, 0.0) po_unit_price,
uom1.um_code price_um
FROM
rcv_transactions t,
sy_uoms_mst uom1,
po_headers_all poh,
po_vend_mst v
WHERE
t.shipment_header_id = v_doc_id
AND t.transaction_id = v_line_id
AND t.source_doc_unit_of_measure = uom1.unit_of_measure
AND t.po_header_id = poh.po_header_id (+)
AND t.vendor_site_id = v.of_vendor_site_id (+)
AND nvl(v.co_code, pco_code) = pco_code ;
SELECT
h.net_price,
h.price_um
FROM
po_recv_hst h,
gl_sevt_mst sb
WHERE
h.recv_id = v_recv_id
AND h.recv_line_id = v_recv_line_id
AND sb.sub_event_type = h.sub_event_type
AND sb.sub_event_code = v_sub_event_code;
SELECT
reason_code
FROM
ic_tran_pnd
WHERE
doc_type = v_doc_type
AND doc_id = v_doc_id
AND line_id = v_line_id
UNION ALL
SELECT
reason_code
FROM
ic_tran_cmp
WHERE
doc_type = v_doc_type
AND doc_id = v_doc_id
AND line_id = v_line_id;
SELECT
reason_code
FROM
ic_tran_pnd
WHERE
trans_id = v_trans_id;
SELECT
d.wip_plan_qty,
d.original_qty, -- Bug# 3772552 - Fwd port for 3544905
t.whse_code,
t.trans_um
FROM
ic_tran_pnd t,
gme_material_details d
WHERE
t.doc_type = 'PROD'
AND t.line_id = v_line_id
AND d.material_detail_id = t.line_id;
SELECT
t.doc_id,
t.line_id,
t.whse_code,
i.item_no,
i.item_desc1,
sum(t.trans_qty) trans_qty,
t.trans_um
FROM
ic_tran_cmp t,
ic_item_mst i
WHERE
t.doc_type = v_doc_type
AND t.doc_id = v_doc_id
AND t.line_id = v_line_id
AND t.item_id = i.item_id
GROUP BY
t.doc_id,
t.line_id,
t.whse_code,
i.item_no,
i.item_desc1,
t.trans_um;
SELECT c.orgn_code, c.cycle_no
FROM ic_cycl_hdr c
WHERE c.cycle_id = v_doc_id;
SELECT sum(ic.loct_onhand)
FROM ic_perd_bal ic
,ic_whse_mst wh
,gl_subr_sta st
WHERE ic.whse_code = wh.whse_code
AND wh.mtl_organization_id = v_doc_id
AND ic.item_id = v_line_id
AND st.reference_no = v_reference_no
AND st.crev_inv_prev_cal = ic.fiscal_year
AND st.crev_inv_prev_per = ic.period
GROUP BY ic.item_id,ic.whse_code
HAVING SUM(ic.loct_onhand) <> 0;
/*SELECT sum(ic.loct_onhand)
INTO l_quantity
FROM ic_perd_bal ic
,ic_whse_mst wh
,gl_subr_sta st
WHERE ic.whse_code = wh.whse_code
AND wh.mtl_organization_id = r.doc_id
AND ic.item_id = r.line_id
AND st.reference_no = r.reference_no
AND st.crev_inv_prev_cal = ic.fiscal_year
AND st.crev_inv_prev_per = ic.period
GROUP BY ic.item_id,ic.whse_code
HAVING SUM(ic.loct_onhand) <> 0; */ -- Commented and added a cursor c_ic_rval_quantity
SELECT onhand_qty
INTO l_quantity
FROM gmf_lot_cost_adjustments
WHERE adjustment_id = r.line_id;