[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(sort_sequence, 0, NULL, sort_sequence) sort_sequence
FROM cm_cmpt_mst
WHERE cost_cmpntcls_id = p_ccc_id ;
SELECT cost_ORGANIZATION_ID
FROM cm_whse_asc
WHERE ORGANIZATION_ID = V_ORGANIZATION_ID
AND eff_start_date <= v_trans_date
AND eff_end_date >= v_trans_date
AND delete_mark = 0;
SELECT o.organization_id,
f.legal_entity_id,
f.cost_type_id,
f.cost_basis
FROM hr_organization_information o,
gmf_fiscal_policies f
WHERE o.organization_id = v_organization_id
AND o.org_information_context = 'Accounting Information'
AND o.org_information2 = f.LEGAL_ENTITY_ID
AND f.delete_mark = 0;
SELECT mst.calendar_code,
mst.period_code,
mst.end_date,
mst.period_id
FROM cm_cldr_mst_v mst
WHERE mst.delete_mark = 0
AND mst.end_date < v_trans_date
AND mst.cost_type_id = v_cost_type_id
AND mst.legal_entity_id = v_legal_entity_id
ORDER BY 3 desc;
SELECT acctg_cost,
cost_type,
fmeff_id,
itemcost_id
FROM gl_item_cst
WHERE organization_id = v_organization_id
AND inventory_item_id = v_item_id
AND cost_type_id = v_cost_type_id
AND period_id = v_period_id;
SELECT acctg_cost,
cost_type,
fmeff_id,
itemcost_id
FROM gl_item_cst
WHERE organization_id = v_organization_id
AND inventory_item_id = v_item_id
AND cost_type_id = v_cost_type_id
AND end_date >= v_trans_date
AND start_date <= v_trans_date;
SELECT cmptcost_amt
FROM gl_item_dtl
WHERE itemcost_id = v_itemcost_id
AND cost_cmpntcls_id = v_cmpntcls_id
AND cost_analysis_code = v_analysis_code;
SELECT cmptcost_amt,
i.cost_cmpntcls_id,
i.cost_analysis_code
FROM gl_item_dtl i,
cm_cmpt_mst c
WHERE i.itemcost_id = v_itemcost_id
AND i.cost_cmpntcls_id = c.cost_cmpntcls_id
AND c.ppv_ind = 1;
SELECT SUM(cmptcost_amt)
FROM gl_item_dtl i,
cm_cmpt_mst c
WHERE i.itemcost_id = v_itemcost_id
AND i.cost_cmpntcls_id = c.cost_cmpntcls_id
AND c.ppv_ind = 1;
* Retrieves Cost Tpe FOR the Cost TYPE Id Selected *
***************************************************/
CURSOR Cur_Get_mthd_type
(
v_cost_type_id IN NUMBER
)
IS
SELECT cost_type
FROM cm_mthd_mst
WHERE cost_type_id = v_cost_type_id;
SELECT nvl(sum(moqd.primary_transaction_quantity), 0) qty, lot_number
FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
WHERE moqd.organization_id = v_organization_id
AND moqd.inventory_item_id = v_inventory_item_id
AND moqd.is_consigned = 2
AND msi.secondary_inventory_name = moqd.subinventory_code
AND moqd.organization_id = msi.organization_id
AND msi.asset_inventory = 1
GROUP BY inventory_item_id, lot_number;
SELECT nvl(sum(gpb.primary_quantity), 0) qty, lot_number
FROM gmf_period_balances gpb, org_acct_periods oap, mtl_secondary_inventories msi
WHERE gpb.organization_id = v_organization_id
AND gpb.inventory_item_id = v_inventory_item_id
AND gpb.acct_period_id = oap.acct_period_id
AND oap.organization_id = gpb.organization_id
AND oap.period_year = v_fiscal_year
AND oap.period_num = v_period
AND gpb.subinventory_code (+)= msi.secondary_inventory_name
AND gpb.organization_id = msi.organization_id
AND msi.asset_inventory = 1
GROUP BY inventory_item_id, lot_number;
SELECT cost_mthd_code
INTO p_cost_method
FROM cm_mthd_mst
WHERE cost_type_id = P_cached_cost_type_id;
SELECT cost_type_id
INTO p_cost_type_id
FROM cm_mthd_mst
WHERE cost_mthd_code = p_cost_method;
* Now select the cost based on the prior calendar and period selected above *
****************************************************************************/
OPEN Cur_get_pr_cost (
P_cached_cost_organization_id,
p_inventory_item_id,
p_cost_type_id,
Cur_get_calprd_tmp.period_id
);
* select co cmptcost_amt from gl_item_dtl *
******************************************/
x_no_recs := 0;
* Added delete mark in the where clause while fetching default cost method *
* Added RAISE exception to avoid circular calls to get_process_item_cost. if alternate *
* cost method is marked for purge. *
***********************************************************************************************/
FUNCTION Get_Process_Item_Cost
(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_inventory_item_id IN NUMBER /* Item_Id */
, p_organization_id IN NUMBER /* Inventory Organization Id */
, p_transaction_date IN DATE /* Cost as on date */
, p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
SELECT
cost_type,
default_lot_cost_type_id
FROM cm_mthd_mst
WHERE cost_type_id = v_cost_type_id;
SELECT m.cost_type_id,
m.default_lot_cost_type_id
FROM cm_mthd_mst m,
gmf_fiscal_policies plc,
hr_organization_information o
WHERE o.organization_id = v_organization_id
AND o.org_information_context = 'Accounting Information'
AND plc.legal_entity_id = o.org_information2
AND plc.cost_type_id = m.cost_type_id
AND m.delete_mark = 0
AND plc.delete_mark = 0 ;
SELECT cost_cmpntcls_id,
cost_analysis_code,
component_cost
FROM gmf_lot_cost_details
WHERE header_id = v_header_id
AND cost_cmpntcls_id = NVL(v_cost_cmpntcls_id,cost_cmpntcls_id)
AND cost_analysis_code = NVL(v_cost_analysis_code,cost_analysis_code);
SELECT sum(component_cost)
FROM gmf_lot_cost_details d,
gmf_lot_costs h
WHERE h.header_id = v_header_id
AND h.header_id = d.header_id
AND h.lot_number = nvl(v_lot_number ,h.lot_number);
SELECT cost_header_id
FROM gmf_material_lot_cost_txns
WHERE transaction_id = v_trans_id
AND cost_type_id = v_cost_type_id;
SELECT MAX(header_id)
FROM gmf_lot_costs
WHERE inventory_item_id = p_inventory_item_id
AND cost_type_id = v_cost_type_id
AND lot_number = v_lot_number
AND cost_date <= v_trans_date
AND organization_id = v_organization_id;
SELECT SUM(onhand_qty*unit_cost)/SUM(onhand_qty)
FROM (
SELECT onhand_qty,
unit_cost,
RANK() OVER (
PARTITION BY lot_number
ORDER BY cost_date desc,
header_id desc
) as rank
FROM gmf_lot_costs
WHERE cost_date <= v_trans_date
AND inventory_item_id = v_item_id
AND organization_id = v_organization_id
AND cost_type_id = v_cost_type_id
)
WHERE rank = 1;
SELECT cost_type_id
INTO l_cost_type_id
FROM cm_mthd_mst
WHERE cost_mthd_code = p_cost_method
AND delete_mark = 0;
SELECT cost_mthd_code INTO l_default_cost_mthd_code
FROM cm_mthd_mst
WHERE cost_type_id = l_default_lot_cost_type_id AND
delete_mark = 0;
SELECT count(1)
FROM cm_cmpt_dtl cst,
cm_acst_led aled,
gme_material_details md,
gme_batch_header bh
WHERE bh.batch_id = l_batch_id
AND bh.batch_id = md.batch_id
AND md.material_detail_id = aled.transline_id
AND aled.source_ind = 0
AND aled.cmpntcost_id = cst.cmpntcost_id
AND cst.rollover_ind = 1;
SELECT to_number(src.org_information2) src_ou, to_number(dest.org_information2) dest_ou
INTO l_from_ou, l_to_ou
FROM hr_organization_information src, hr_organization_information dest
WHERE src.organization_id = p_src_organization_id
AND src.org_information_context = 'Accounting Information'
AND dest.organization_id = p_dest_organization_id
AND dest.org_information_context = 'Accounting Information'
;
SELECT primary_unit_of_measure
INTO l_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_src_organization_id;
SELECT uom_code
INTO l_primary_uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure_tl = l_primary_uom;
SELECT uom_code
INTO l_trans_uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure_tl = p_trans_uom;
SELECT nvl(sum(cst.cmpnt_cost), 0)
FROM cm_cmpt_dtl cst,
gmf_organization_definitions god,
gmf_fiscal_policies f,
gmf_period_statuses gps,
(
select nvl (
(
SELECT x.cost_organization_id
FROM cm_whse_asc x
WHERE x.organization_id = v_organization_id
AND x.eff_start_date <= v_transaction_date
AND x.eff_end_date >= v_transaction_date
AND x.delete_mark = 0
), v_organization_id) organization_id
from dual
) oasc
WHERE god.organization_id = nvl(oasc.organization_id, v_organization_id)
AND f.legal_entity_id = god.legal_entity_id
AND f.delete_mark = 0
AND gps.delete_mark = 0
AND gps.legal_entity_id = f.legal_entity_id
AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
AND v_transaction_date BETWEEN gps.START_DATE AND gps.end_date
AND cst.inventory_item_id = v_inventory_item_id
AND cst.organization_id = NVL(oasc.organization_id, v_organization_id)
AND cst.period_id = gps.period_id
AND cst.cost_type_id = nvl(v_cost_type_id, f.cost_type_id);
SELECT gps.end_date
FROM gmf_organization_definitions god,
gmf_fiscal_policies f,
gmf_period_statuses gps,
(
select nvl (
(
SELECT x.cost_organization_id
FROM cm_whse_asc x
WHERE x.organization_id = v_organization_id
AND x.eff_start_date <= v_transaction_date
AND x.eff_end_date >= v_transaction_date
AND x.delete_mark = 0
), v_organization_id) organization_id
from dual
) oasc
WHERE god.organization_id = nvl(oasc.organization_id, v_organization_id)
AND f.legal_entity_id = god.legal_entity_id
AND f.delete_mark = 0
AND gps.delete_mark = 0
AND gps.legal_entity_id = f.legal_entity_id
AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
AND gps.end_date < v_transaction_date
ORDER BY gps.end_date desc;
SELECT gps.end_date
FROM gmf_organization_definitions god,
gmf_fiscal_policies f,
gmf_period_statuses gps
WHERE god.organization_id = v_organization_id
AND f.legal_entity_id = god.legal_entity_id
AND f.delete_mark = 0
AND gps.delete_mark = 0
AND gps.legal_entity_id = f.legal_entity_id
AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
AND gps.end_date < v_transaction_date
ORDER BY gps.end_date desc;
SELECT nvl(sum(cst.nominal_cost), 0)
FROM cm_rsrc_dtl cst,
gmf_organization_definitions god,
gmf_fiscal_policies f,
gmf_period_statuses gps
WHERE god.organization_id = v_organization_id
AND f.legal_entity_id = god.legal_entity_id
AND f.delete_mark = 0
AND gps.delete_mark = 0
AND gps.legal_entity_id = f.legal_entity_id
AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
AND v_transaction_date BETWEEN gps.START_DATE AND gps.end_date
AND cst.resources = v_resources
AND (cst.organization_id = v_organization_id OR cst.organization_id IS NULL)
AND cst.period_id = gps.period_id
AND cst.cost_type_id = nvl(v_cost_type_id, f.cost_type_id);