[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE wip_pac_period_balances wppb
SET wppb.operation_completed_units
= NVL(wppb.operation_completed_units,0) + l_pri_qty,
wppb.last_update_date = SYSDATE,
wppb.request_id = p_request_id,
wppb.program_update_date = SYSDATE
WHERE
wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = p_entity_id
AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
AND wppb.operation_seq_num = p_op_seq;
INSERT INTO wip_pac_period_balances
(
pac_period_id,
cost_group_id,
cost_type_id,
organization_id,
wip_entity_id,
wip_entity_type,
line_id,
operation_seq_num,
operation_completed_units,
relieved_assembly_units,
tl_resource_in,
tl_resource_out,
tl_outside_processing_in,
tl_outside_processing_out,
tl_overhead_in,
tl_overhead_out,
pl_material_in,
pl_material_out,
pl_resource_in,
pl_resource_out,
pl_overhead_in,
pl_overhead_out,
pl_outside_processing_in,
pl_outside_processing_out,
pl_material_overhead_in,
pl_material_overhead_out,
/*added for _apull columns for bug#3229515*/
pl_material_in_apull,
pl_resource_in_apull,
pl_overhead_in_apull,
pl_outside_processing_in_apull,
pl_material_overhead_in_apull,
/*end of addition for bug#3229515*/
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
SELECT
p_pac_period_id,
p_cost_group_id,
p_cost_type_id,
p_org_id,
p_entity_id,
p_entity_type,
decode(p_entity_type, 4, null, p_line_id),
p_op_seq,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id
FROM DUAL
WHERE NOT EXISTS
( SELECT 'X'
FROM wip_pac_period_balances wppb2
WHERE wppb2.pac_period_id = p_pac_period_id
AND wppb2.cost_group_id = p_cost_group_id
AND wppb2.wip_entity_id = p_entity_id
AND NVL(wppb2.line_id,-99) = decode(p_entity_type,4,-99,NVL(p_line_id,-99))
AND wppb2.operation_seq_num = p_op_seq
);
SELECT wo.previous_operation_seq_num prev_op_seq,
wo.next_operation_seq_num next_op_seq
FROM wip_operations wo
WHERE wo.wip_entity_id = p_entity_id
AND NVL(wo.repetitive_schedule_id,-99)
= NVL(p_rep_sched_id,-99)
AND wo.operation_seq_num = p_op_seq;
current op. is deleted*/
IF c_ops%NOTFOUND THEN /*current op has been deleted.*/
/*Next op. is the min(op_seq). with op_seq > current op.*/
SELECT MIN(wo.operation_seq_num) next_op_seq
INTO x_next_op
FROM wip_operations wo
WHERE wo.wip_entity_id = p_entity_id
AND NVL(wo.repetitive_schedule_id,-99)
= NVL(p_rep_sched_id,-99)
AND wo.operation_seq_num > p_op_seq;
SELECT wo.operation_seq_num prev_op_seq
INTO x_prev_op
FROM wip_operations wo
WHERE wo.wip_entity_id = p_entity_id
AND NVL(wo.repetitive_schedule_id,-99)
= NVL(p_rep_sched_id,-99)
AND nvl(wo.next_operation_seq_num,-99) = nvl(x_next_op,-99);
SELECT count(*)
INTO l_count
FROM wip_operations wo
WHERE wo.wip_entity_id = p_entity_id
AND NVL(wo.repetitive_schedule_id,-99)
= NVL(p_rep_sched_id,-99)
AND wo.operation_seq_num = p_op_seq;
SELECT wmt.organization_id org_id,
wmt.transaction_id move_txn_id,
wmt.wip_entity_id entity_id,
wmt.line_id line_id,
wmt.fm_operation_seq_num fm_op_seq,
wmt.to_operation_seq_num to_op_seq,
wmt.fm_intraoperation_step_type fm_step,
wmt.to_intraoperation_step_type to_step,
wmt.primary_quantity pri_qty
FROM
wip_move_transactions wmt
WHERE wmt.wip_entity_id = p_entity_id
AND wmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND NOT ( wmt.fm_operation_seq_num = wmt.to_operation_seq_num
AND ( (wmt.fm_intraoperation_step_type <=2
AND wmt.to_intraoperation_step_type <= 2)
OR (wmt.fm_intraoperation_step_type > 2
AND wmt.to_intraoperation_step_type > 2)
)
);
SELECT NVL(MAX(wt.transaction_id),-99)
INTO l_wt
FROM wip_transactions wt
WHERE wt.wip_entity_id = p_entity_id
AND wt.line_id = c_rec.line_id
AND wt.move_transaction_id = c_rec.move_txn_id;
SELECT NVL(MAX(wip_alloc.repetitive_schedule_id),-99)
INTO l_rep_sched_id
FROM wip_txn_allocations wip_alloc
WHERE wip_alloc.transaction_id = l_wt;
This scenario is possible if first/last op was deleted.*/
IF ((l_forward_flag=1 AND l_next_op IS NULL)
OR
(l_forward_flag=-1 AND l_prev_op IS NULL))
THEN
l_done := 1;
SELECT we.wip_entity_id entity_id,
we.entity_type entity_type
FROM wip_entities we,
cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = p_cost_group_id
AND we.organization_id = ccga.organization_id
AND we.entity_type <> 4 -- NOT CFM
AND we.primary_item_id IS NOT NULL
AND EXISTS
( SELECT 'X'
FROM wip_move_transactions wmt
WHERE wmt.wip_entity_id = we.wip_entity_id
AND wmt.transaction_date BETWEEN
TRUNC(p_start_date) AND
(TRUNC(p_end_date) + 0.99999)
);
SELECT we.wip_entity_id entity_id,
we.entity_type entity_type,
we.organization_id org_id
FROM wip_entities we,
cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = p_cost_group_id
AND we.organization_id = ccga.organization_id
AND EXISTS
( SELECT 'X'
FROM wip_transactions wt
WHERE wt.wip_entity_id = we.wip_entity_id
AND wt.transaction_type IN (1,2,3,17)
-- Direct Item txns should be picked too.
AND wt.transaction_date BETWEEN
TRUNC(p_start_date) AND
(TRUNC(p_end_date) + 0.99999)
AND ccga.organization_id = wt.organization_id
);
SELECT wt.transaction_id txn_id,
wt.organization_id org_id,
wt.wip_entity_id entity_id,
wt.line_id line_id,
wt.department_id dept_id,
wt.operation_seq_num op_seq,
wt.resource_id resource_id,
wt.resource_seq_num resource_seq_num, -- Added for eAM project
crc.resource_rate actual_cost,
wt.transaction_type wip_txn_type,
wt.primary_quantity applied_units,
wt.primary_quantity*nvl(crc.resource_rate,0) applied_value,
wt.rcv_transaction_id -- Added for 4735668
FROM wip_transactions wt,
cst_resource_costs crc,
bom_resources br /* Bug 4641635; Include resources which are to be costed */
SELECT wt.transaction_id txn_id,
wt.organization_id org_id,
wt.wip_entity_id entity_id,
wt.line_id line_id,
wt.department_id dept_id,
wt.operation_seq_num op_seq,
wt.resource_id resource_id,
wt.resource_seq_num resource_seq_num, -- Added for eAM project
0 actual_cost,
wt.transaction_type wip_txn_type,
wt.primary_quantity applied_units,
0 applied_value,
wt.rcv_transaction_id -- Added for 4735668
FROM wip_transactions wt,
bom_resources br /* Bug 4641635; Include resources which are to be costed */
SELECT wt.transaction_id txn_id,
wt.organization_id org_id,
wt.wip_entity_id entity_id,
wt.line_id line_id,
wt.department_id dept_id,
wt.operation_seq_num op_seq,
wt.resource_id resource_id,
wt.resource_seq_num resource_seq_num, -- Added for eAM project
0 actual_cost,
wt.transaction_type wip_txn_type,
nvl(wt.primary_quantity, rt.amount) applied_units, /* Bug 4180323*/
0 applied_value,
wt.rcv_transaction_id -- Added for 4735668
FROM wip_transactions wt,
rcv_transactions rt
WHERE wt.wip_entity_id = p_entity_id
AND rt.transaction_id = wt.rcv_transaction_id
AND wt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND wt.transaction_type = 17; -- Direct Items
SELECT cdo.overhead_id ovhd_id,
cdo.rate_or_amount actual_cost,
cdo.basis_type basis_type,
cdo.rate_or_amount * decode(cdo.basis_type,
3, p_res_units,
p_res_value) applied_value,
decode(cdo.basis_type, 3, p_res_units,
p_res_value) basis_units
FROM cst_resource_overheads cro,
cst_department_overheads cdo
WHERE cdo.department_id = p_dept_id
AND cdo.organization_id = p_org_id
AND cdo.cost_type_id = p_pac_ct_id
AND cdo.basis_type IN (3,4)
AND cro.cost_type_id = cdo.cost_type_id
AND cro.resource_id = p_resource_id
AND cro.overhead_id = cdo.overhead_id
AND cro.organization_id = cdo.organization_id;
SELECT wt.transaction_id txn_id,
wt.organization_id org_id,
wt.wip_entity_id entity_id,
wt.line_id line_id,
wt.operation_seq_num op_seq,
wt.department_id dept_id,
wta.resource_id overhead_id,
cdo.rate_or_amount actual_cost,
wt.basis_type basis_type,
wt.primary_quantity*cdo.rate_or_amount applied_value
FROM
wip_transactions wt,
wip_transaction_accounts wta,
cst_department_overheads cdo
WHERE wt.wip_entity_id = p_entity_id
AND wt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND wt.transaction_type = 2 -- MBO
AND wta.transaction_id = wt.transaction_id
AND wta.accounting_line_type = 3
AND wta.cost_element_id = 5
AND cdo.department_id = wt.department_id
AND cdo.overhead_id = wta.resource_id
AND cdo.cost_type_id = p_pac_ct_id
AND NVL(wta.repetitive_schedule_id,-99) =
(SELECT NVL(MAX(wip_alloc.repetitive_schedule_id),-99)
FROM wip_txn_allocations wip_alloc
WHERE wip_alloc.transaction_id = wt.transaction_id
AND rownum = 1
)
AND cdo.rate_or_amount <> 0
ORDER BY wt.transaction_id;
SELECT count(*)
INTO l_pending_txns
FROM wip_cost_txn_interface wcti
WHERE wcti.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND wcti.transaction_type IN (1,2,3,6)
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = p_cost_group_id
AND ccga.organization_id = wcti.organization_id
);
SELECT legal_entity
INTO l_legal_entity_id
FROM cst_pac_periods
WHERE pac_period_id = p_pac_period_id;
UPDATE wip_pac_period_balances wppb
SET tl_resource_in = NVL(tl_resource_in,0) +
c_res_rec.applied_value,
request_id = p_request_id,
last_update_date = SYSDATE,
program_update_date = SYSDATE
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_res_rec.entity_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
AND wppb.operation_seq_num = c_res_rec.op_seq;
/* Bug 4180323- Initializing the variables so that correct value is inserted into
wip_pac_actual_cost_details table
*/
l_actual_cost := c_res_rec.actual_cost;
SELECT rt.primary_quantity, rt.transaction_type
INTO l_del_qty, l_rcv_txn_type
FROM RCV_TRANSACTIONS RT
WHERE rt.transaction_id = c_res_rec.rcv_transaction_id;
UPDATE wip_pac_period_balances wppb
SET tl_outside_processing_in =
NVL(tl_outside_processing_in,0) +
l_applied_value,
request_id = p_request_id,
last_update_date = SYSDATE,
program_update_date = SYSDATE
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_res_rec.entity_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
AND wppb.operation_seq_num = c_res_rec.op_seq;
/* Check for cost element of direct item to update the required
colimns. */
IF l_eam_cost_element = 1 THEN
/* Update PL-material for materials */
UPDATE wip_pac_period_balances wppb
SET pl_material_in =
NVL(pl_material_in,0) +
l_applied_value,
request_id = p_request_id,
last_update_date = SYSDATE,
program_update_date = SYSDATE
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_res_rec.entity_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
AND wppb.operation_seq_num = c_res_rec.op_seq;
/* Update PL-resource for Resources */
UPDATE wip_pac_period_balances wppb
SET pl_resource_in = NVL(pl_resource_in,0)
+ l_applied_value,
request_id = p_request_id,
last_update_date = SYSDATE,
program_update_date = SYSDATE
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_res_rec.entity_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
AND wppb.operation_seq_num = c_res_rec.op_seq;
/* Update PL-OSP cols for OSP */
UPDATE wip_pac_period_balances wppb
SET pl_outside_processing_in =
NVL (pl_outside_processing_in,0) +
l_applied_value,
request_id = p_request_id,
last_update_date = SYSDATE,
program_update_date = SYSDATE
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_res_rec.entity_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
AND wppb.operation_seq_num = c_res_rec.op_seq;
INSERT INTO wip_pac_actual_cost_details
(
pac_period_id,
cost_group_id,
cost_type_id,
transaction_id,
level_type,
cost_element_id,
resource_id,
basis_resource_id,
transaction_costed_date,
actual_cost,
actual_value,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
SELECT
p_pac_period_id,
p_cost_group_id,
p_cost_type_id,
c_res_rec.txn_id,
1, -- Level Type
decode(c_res_rec.wip_txn_type,
1, 3,
3, 4,
17,l_eam_cost_element), -- Cost element_id
-- Insert Direct Item cost element id here too.
c_res_rec.resource_id,
NULL, -- basis_res_id
SYSDATE,
l_actual_cost,
l_applied_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id
FROM DUAL;
UPDATE wip_pac_period_balances wppb
SET tl_overhead_in = NVL(tl_overhead_in,0) +
c_rbo_rec.applied_value,
request_id = p_request_id,
last_update_date = SYSDATE,
program_update_date = SYSDATE
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_res_rec.entity_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
AND wppb.operation_seq_num = c_res_rec.op_seq;
INSERT INTO wip_pac_actual_cost_details
(
pac_period_id,
cost_group_id,
cost_type_id,
transaction_id,
level_type,
cost_element_id,
resource_id,
basis_resource_id,
transaction_costed_date,
actual_cost,
actual_value,
basis_type,
basis_units,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
SELECT
p_pac_period_id,
p_cost_group_id,
p_cost_type_id,
c_res_rec.txn_id,
1, -- Level Type
5, -- CE
c_rbo_rec.ovhd_id,
c_res_rec.resource_id,
SYSDATE,
c_rbo_rec.actual_cost,
c_rbo_rec.applied_value,
c_rbo_rec.basis_type,
c_rbo_rec.basis_units,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id
FROM DUAL;
UPDATE wip_pac_period_balances wppb
SET tl_overhead_in = NVL(tl_overhead_in,0) +
c_mbo_rec.applied_value,
request_id = p_request_id,
last_update_date = SYSDATE,
program_update_date = SYSDATE
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_mbo_rec.entity_id
AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_mbo_rec.line_id,-99))
AND wppb.operation_seq_num = c_mbo_rec.op_seq;
INSERT INTO wip_pac_actual_cost_details
(
pac_period_id,
cost_group_id,
cost_type_id,
transaction_id,
level_type,
cost_element_id,
resource_id,
basis_resource_id,
transaction_costed_date,
actual_cost,
actual_value,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
SELECT
p_pac_period_id,
p_cost_group_id,
p_cost_type_id,
c_mbo_rec.txn_id,
1, -- Level Type
5, -- CE
c_mbo_rec.overhead_id,
NULL, -- basis_resource_id
SYSDATE,
c_mbo_rec.actual_cost,
c_mbo_rec.applied_value,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id
FROM DUAL;
SELECT wmt.transaction_id wip_txn_id,
wmt.wip_entity_id wip_entity,
wmt.line_id line,
wmt.fm_operation_seq_num from_op_seq,
wmt.fm_intraoperation_step_type from_op_step,
wmt.to_operation_seq_num to_op_seq,
wmt.to_intraoperation_step_type to_op_step,
wmt.primary_quantity pri_qty
FROM cst_cost_group_assignments ccga,
wip_move_transactions wmt
WHERE wmt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND wmt.organization_id = ccga.organization_id
AND ccga.cost_group_id = p_cost_group_id
AND wmt.primary_item_id is not NULL
AND wmt.scrap_account_id is null
AND (wmt.fm_intraoperation_step_type = 5 OR
wmt.to_intraoperation_step_type = 5);
UPDATE WIP_PAC_PERIOD_BALANCES
SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
WHERE wip_entity_id = c_scrap_rec.wip_entity
AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
AND operation_seq_num <= c_scrap_rec.to_op_seq
AND cost_type_id = p_cost_type_id
AND pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id;
UPDATE WIP_PAC_PERIOD_BALANCES
SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
WHERE wip_entity_id = c_scrap_rec.wip_entity
AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
AND operation_seq_num <= c_scrap_rec.from_op_seq
AND cost_type_id = p_cost_type_id
AND pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id;
UPDATE WIP_PAC_PERIOD_BALANCES
SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
WHERE wip_entity_id = c_scrap_rec.wip_entity
AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
AND operation_seq_num > c_scrap_rec.to_op_seq
AND operation_seq_num <= c_scrap_rec.from_op_seq
AND cost_type_id = p_cost_type_id
AND pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id;
UPDATE WIP_PAC_PERIOD_BALANCES
SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
WHERE wip_entity_id = c_scrap_rec.wip_entity
AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
AND operation_seq_num > c_scrap_rec.from_op_seq
AND operation_seq_num <= c_scrap_rec.to_op_seq
AND cost_type_id = p_cost_type_id
AND pac_period_id = p_pac_period_id
AND cost_group_id = p_cost_group_id;