[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date DATE := FND_API.G_MISS_DATE;
l_last_updated_by NUMBER := FND_API.G_MISS_NUM;
l_last_update_login NUMBER := FND_API.G_MISS_NUM;
SELECT wip_entity_id
, wip_line_id
, wip_repetitive_schedule_id
, wip_operation_seq_num
, wip_resource_seq_num
, transaction_date
, creation_date
, created_by
, item_id
, last_update_date
, last_updated_by
, last_update_login
, to_organization_id
, reason_id
, source_doc_unit_of_measure
, comments
, po_header_id
, po_line_id
, po_unit_price
, quantity
, unit_of_measure
, primary_unit_of_measure
, currency_code
, currency_conversion_type
, currency_conversion_rate
, currency_conversion_date
INTO l_wip_entity_id
, l_wip_line_id
, l_wip_repetitive_schedule_id
, l_wip_operation_seq_num
, l_wip_resource_seq_num
, l_transaction_date
, l_creation_date
, l_created_by
, l_item_id
, l_last_update_date
, l_last_updated_by
, l_last_update_login
, l_organization_id
, l_reason_id
, l_source_doc_unit_of_measure
, l_comments
, l_po_header_id
, l_po_line_id
, l_po_unit_price
, l_quantity
, l_unit_of_measure
, l_primary_unit_of_measure
, l_currency_code
, l_currency_conversion_type
, l_currency_conversion_rate
, l_currency_conversion_date
FROM RCV_TRANSACTIONS_INTERFACE
WHERE interface_transaction_id = g_Res_rec.source_line_id;
IF g_RCV_txn.last_update_date IS NULL THEN
g_RCV_txn.Last_Update_Date := l_last_update_date;
IF g_RCV_txn.last_updated_by IS NULL THEN
g_RCV_txn.Last_Updated_By := l_last_updated_by;
IF g_RCV_txn.last_update_login IS NULL THEN
g_RCV_txn.Last_Update_Login := l_last_update_login;
SELECT activity_id
, resource_id
, usage_rate_or_amount
, basis_type
, autocharge_type
, uom_code
, standard_rate_flag
INTO l_activity_id
, l_resource_id
, l_usage_rate_or_amount
, l_basis_type
, l_autocharge_type
, l_uom_code
, l_std_rate_flag
FROM wip_operation_resources
WHERE wip_entity_id = g_Res_rec.wip_entity_id
AND organization_id = g_Res_rec.organization_id
AND operation_seq_num = g_Res_rec.operation_seq_num
AND resource_seq_num = g_Res_rec.resource_seq_num
AND (repetitive_schedule_id IS NULL
OR repetitive_schedule_id = g_Res_rec.repetitive_schedule_id);
SELECT pd.project_id
, pd.task_id
, nonrecoverable_tax
, quantity_ordered
INTO l_project_id
, l_task_id
, l_nonrecoverable_tax
, l_quantity_ordered
FROM po_distributions_all pd,
rcv_transactions_interface rti
WHERE rti.po_distribution_id = pd.po_distribution_id
AND rti.interface_transaction_id = g_Res_rec.source_line_id;
select UNIT_MEAS_LOOKUP_CODE
into l_po_uom
from po_lines_all
where po_line_id = g_RCV_txn.po_line_id;
SELECT resource_code
, resource_type
INTO l_resource_code
, l_resource_type
FROM bom_resources br
WHERE br.resource_id = g_Res_rec.resource_id;
SELECT oap.acct_period_id
INTO l_acct_period_id
FROM org_acct_periods oap
WHERE oap.organization_id = g_Res_rec.organization_id
AND oap.period_close_date is null
AND trunc(g_Res_rec.transaction_date) between
trunc(oap.period_start_date) and
trunc(oap.schedule_close_date);
SELECT activity
INTO g_Res_rec.activity_name
FROM cst_activities
WHERE activity_id = g_Res_rec.activity_id;
SELECT msi.outside_operation_uom_type
INTO l_uom_basis
FROM mtl_system_items msi
WHERE msi.inventory_item_id = g_RCV_txn.item_id
AND msi.organization_id = g_RCV_txn.organization_id;
select UNIT_MEAS_LOOKUP_CODE
into l_po_uom
from po_lines_all
where po_line_id = g_RCV_txn.po_line_id;
SELECT rti.primary_quantity
INTO l_po_qty
FROM mtl_system_items msi, rcv_transactions_interface rti
WHERE msi.inventory_item_id = rti.item_id
AND msi.organization_id = rti.to_organization_id
AND rti.interface_transaction_id = g_Res_rec.source_line_id;
select round(l_actual_resource_rate,nvl(fc.extended_precision,5))
into l_actual_resource_rate
from fnd_currencies fc
where currency_code = g_RCV_txn.currency_code;*/
select we.hourly_labor_rate
into l_actual_resource_rate
from wip_employee_labor_rates we
where we.employee_id = g_Res_rec.employee_id
and we.organization_id = g_Res_rec.organization_id
and we.effective_date = (select max(we1.effective_date)
from wip_employee_labor_rates we1
where we1.effective_date < sysdate
and we1.employee_id = g_Res_rec.employee_id
and we1.organization_id = g_Res_rec.organization_id);
SELECT user_name
INTO g_Res_rec.created_by_name
FROM fnd_user
WHERE user_id = g_Res_rec.created_by;
SELECT department_code
INTO l_dept_code
FROM bom_departments
WHERE department_id = g_Res_rec.department_id;
SELECT department_id
INTO l_dept_id
FROM wip_operations
WHERE wip_entity_id = g_Res_rec.wip_entity_id
AND operation_seq_num = g_Res_rec.operation_seq_num
AND organization_id = g_Res_rec.organization_id
AND (repetitive_schedule_id IS NULL
OR repetitive_schedule_id = g_Res_rec.repetitive_schedule_id);
FUNCTION Get_Last_Updated_By
RETURN VARCHAR2
IS
BEGIN
IF g_Res_rec.last_updated_by IS NOT NULL THEN
RETURN g_Res_rec.last_updated_by;
IF g_RCV_txn.last_updated_by IS NOT NULL THEN
RETURN g_RCV_txn.last_updated_by;
RETURN g_RCV_txn.last_updated_by;
END Get_Last_Updated_By;
FUNCTION Get_Last_Updated_By_Name
RETURN VARCHAR2
IS
BEGIN
IF g_Res_rec.last_updated_by_name IS NOT NULL THEN
RETURN g_Res_rec.last_updated_by_name;
IF g_Res_rec.last_updated_by IS NOT NULL THEN
SELECT user_name
INTO g_Res_rec.last_updated_by_name
FROM fnd_user
WHERE user_id = g_Res_rec.last_updated_by;
RETURN g_Res_rec.last_updated_by_name;
END Get_Last_Updated_By_Name;
FUNCTION Get_Last_Update_Date
RETURN DATE /*Fix Bug 8971751(FP 8933207)*/
IS
BEGIN
IF g_Res_rec.last_update_date IS NOT NULL THEN
RETURN g_Res_rec.last_update_date;
IF g_RCV_txn.last_update_date IS NOT NULL THEN
RETURN g_RCV_txn.last_update_date;
RETURN g_RCV_txn.last_update_date;
END Get_Last_Update_Date;
SELECT line_code
INTO l_line_code
FROM wip_lines
WHERE line_id = g_Res_rec.line_id
AND organization_id = g_Res_rec.organization_id;
SELECT organization_code
INTO g_Res_rec.organization_code
FROM mtl_parameters
WHERE organization_id = g_Res_rec.organization_id;
SELECT reason_name
INTO l_reason_name
FROM mtl_transaction_reasons
WHERE reason_id = g_Res_rec.reason_id;
SELECT rp.receiving_account_id
INTO g_Res_rec.receiving_account_id
FROM rcv_parameters rp
WHERE rp.organization_id = g_Res_rec.organization_id;
SELECT msi.outside_operation_uom_type, rti.primary_quantity
INTO l_uom_basis, l_po_qty
FROM mtl_system_items msi, rcv_transactions_interface rti
WHERE msi.inventory_item_id = rti.item_id
AND msi.organization_id = rti.to_organization_id
AND rti.interface_transaction_id = g_Res_rec.source_line_id;
g_Res_rec.last_updated_by := Get_Last_Updated_By;
g_Res_rec.last_updated_by_name := Get_Last_Updated_By_Name;
g_Res_rec.last_update_date := Get_Last_Update_Date;
IF g_Res_rec.last_updated_by IS NULL THEN
g_Res_rec.last_updated_by := NULL;
IF g_Res_rec.last_update_date IS NULL THEN
g_Res_rec.last_update_date := Sysdate;
IF g_Res_rec.last_update_login IS NULL THEN
g_Res_rec.last_update_login := NULL;
IF g_Res_rec.program_update_date IS NULL THEN
g_Res_rec.program_update_date := Sysdate;