[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (MP.organization_code||'-'||HAOU.name)
INTO x_owning_organization_name
FROM HR_ALL_ORGANIZATION_UNITS HAOU
, MTL_PARAMETERS MP
WHERE HAOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = p_owning_organization_id;
SELECT (pov.vendor_name||'-'||povs.vendor_site_code)
INTO x_owning_organization_name
FROM po_vendor_sites_all povs, po_vendors POV
WHERE povs.vendor_site_id = p_owning_organization_id
AND povs.vendor_id = pov.vendor_id;
SELECT (MP.organization_code||'-'||HAOU.name)
INTO x_xfr_owning_organization_name
FROM HR_ALL_ORGANIZATION_UNITS HAOU
, MTL_PARAMETERS MP
WHERE HAOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = p_xfr_owning_organization_id;
SELECT (pov.vendor_name||'-'||povs.vendor_site_code)
INTO x_xfr_owning_organization_name
FROM po_vendor_sites_all povs, po_vendors POV
WHERE povs.vendor_site_id = p_xfr_owning_organization_id
AND povs.vendor_id = pov.vendor_id;
SELECT (MP.organization_code||'-'||HAOU.name), NULL
INTO x_supplier_site_name, x_supplier
FROM HR_ALL_ORGANIZATION_UNITS HAOU
, MTL_PARAMETERS MP
WHERE HAOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = p_planning_organization_id;
SELECT (pov.vendor_name||'-'||povs.vendor_site_code),pov.vendor_name
INTO x_supplier_site_name, x_supplier
FROM po_vendor_sites_all povs, po_vendors POV
WHERE povs.vendor_site_id = p_planning_organization_id
AND povs.vendor_id = pov.vendor_id;
SELECT TRANSACTION_TYPE_NAME
INTO x_TRANSACTION_TYPE_NAME
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID;
SELECT MEANING
INTO x_TRANSACTION_ACTION
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE ='MTL_TRANSACTION_ACTION'
AND LOOKUP_CODE =p_TRANSACTION_ACTION_ID;
SELECT MEANING,LOOKUP_CODE
INTO x_COSTED_FLAG_1,
x_COSTED_LOOKUP_CODE
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE ='INV_YES_NO_ERROR'
AND LOOKUP_CODE =DECODE(p_COSTED_FLAG,NULL,1,'Y',1,'N',2,'E',3);
SELECT MEANING,LOOKUP_CODE
INTO x_PM_COST_COLLECTED_1,
x_PM_COST_COLLECTED_LK_CODE
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE ='INV_YES_NO_ERROR_NA'
AND LOOKUP_CODE =DECODE (p_PM_COST_COLLECTED, NULL,
DECODE(p_PM_COST_COLLECTOR_GROUP_ID,
NULL,4,1),
'Y', 1,
'N', 2,
'E', 3
);
SELECT TRANSACTION_SOURCE_TYPE_NAME,
TRANSACTION_SOURCE_TYPE_NAME
INTO x_TRANSACTION_SOURCE_TYPE_NAME,
x_TRANSACTION_SOURCE_NAME_DB
FROM mtl_txn_source_types
WHERE TRANSACTION_SOURCE_TYPE_ID =p_TRANSACTION_SOURCE_TYPE_ID ;
SELECT REASON_NAME
INTO x_REASON_NAME
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID =p_REASON_ID;
SELECT DEPARTMENT_CODE
INTO x_DEPARTMENT_CODE
FROM BOM_DEPARTMENTS
WHERE DEPARTMENT_ID =p_DEPARTMENT_ID;
SELECT DISTINCT ORGANIZATION_CODE
INTO x_TRANSFER_ORGANIZATION_NAME
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_TRANSFER_ORGANIZATION_ID;
SELECT OOD. ORGANIZATION_NAME, HOU.NAME
INTO X_ORGANIZATION_CODE , X_OPERATING_UNIT
FROM ORG_ORGANIZATION_DEFINITIONS OOD, HR_OPERATING_UNITS HOU
WHERE OOD.ORGANIZATION_ID = P_ORGANIZATION_ID AND
OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID;
SELECT LICENSE_PLATE_NUMBER
INTO x_LPN
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_LPN_ID ;
SELECT LICENSE_PLATE_NUMBER
INTO x_TRANSFER_LPN
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_TRANSFER_LPN_ID ;
SELECT LICENSE_PLATE_NUMBER
INTO x_CONTENT_LPN
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_CONTENT_LPN_ID ;
SELECT COST_GROUP
INTO x_COST_GROUP_NAME
FROM CST_COST_GROUPS
WHERE COST_GROUP_ID = p_COST_GROUP_ID;
SELECT COST_GROUP
INTO x_TRANSFER_COST_GROUP_NAME
FROM CST_COST_GROUPS
WHERE COST_GROUP_ID = p_TRANSFER_COST_GROUP_ID;
select name
into x_put_away_strategy_name
from wms_strategies
where strategy_id = p_put_away_strategy_id;
select name
into x_put_away_rule_name
from wms_rules
where rule_id = p_put_away_rule_id;
select name
into x_pick_strategy_name
from wms_strategies
where strategy_id = p_pick_strategy_id;
select name
into x_pick_rule_name
from wms_rules
where rule_id = p_pick_rule_id;
** Procedure :update_mmt_process_cost
** Decription: This procedure updates the mtl_material_transactions table
** with the cost fetched from the GMF api for the items
** in process enabled organizations. It updates for the all items
** having transactions in the given organization between the
** transaction dates used in the reports, Transaction Register
** report and Lot Transaction register report.The following
** columns are passed as input parameters from the reports.
**
** p_organization_id This is the context organization selected while running
** the reports.
** p_trans_date_from This is the report parameter "From Transaction date"
** p_trans_date_to This is the report parameter "To Transaction date"
** p_report This value would be T from Transaction register report
** And L from Lot Transaction register report.
** --------------------------------------------------------------------------
*/
PROCEDURE update_mmt_process_cost
(
p_organization_id number
,p_trans_date_from DATE
,p_trans_date_to DATE
,p_report VARCHAR2 DEFAULT 'T')
IS
--Bug#10080406 changing the varchar2(4) to varchar2(10)
v_gl_cost_mthd VARCHAR2(10) ;
select 1
from mtl_parameters
where organization_id = p_organization_id
and process_enabled_flag = 'Y';
sqlstmt := 'select distinct i.organization_id ,i.inventory_item_id,
i.transaction_date '
||' from mtl_material_Transactions i '
||' where i.organization_id = :org_id '
||' and i.transaction_date between :from_date '
||' and :to_date ';
sqlstmt := 'select distinct i.organization_id ,
i.inventory_item_id, i.transaction_date '
||' from mtl_material_Transactions i, mtl_transaction_lot_numbers
l '
||' where i.transaction_id = l.transaction_id '
||' and l.organization_id = :org_id '
||' and l.transaction_date between :from_date '
||' and :to_date ';
update mtl_material_transactions
set actual_cost = v_cost
where organization_id = l_organization_id
and inventory_item_id = l_inventory_item_id
and transaction_date = l_transaction_date;
END update_mmt_process_cost;