[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(MAX(wt.transaction_id),-1) txn_id,
wt.organization_id org_id,
wt.wip_entity_id entity_id
FROM wip_transactions wt,
wip_entities we
WHERE wt.transaction_date BETWEEN TRUNC(p_start_date)
AND (TRUNC(p_end_date) + 0.99999)
AND wt.transaction_type = 6 --Job Close
AND we.wip_entity_id = wt.wip_entity_id
AND we.entity_type <> 2 -- Not a rep schedule
AND EXISTS (
SELECT 'X'
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = wt.wip_entity_id
)
GROUP BY
wt.organization_id,
wt.wip_entity_id;
SELECT wppb.wip_entity_id entity_id,
wppb.cost_group_id cost_group_id,
wppb.line_id line_id,
wppb.operation_seq_num op_seq_num
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_type = 2; -- Rep. Svhedule
SELECT wppb.wip_entity_id entity_id,
wppb.line_id,
SUM(NVL(wppb.pl_material_in,0)) +
SUM(NVL(wppb.pl_material_overhead_in,0)) +
SUM(NVL(wppb.pl_resource_in,0)) +
SUM(NVL(wppb.pl_outside_processing_in,0)) +
SUM(NVL(wppb.pl_overhead_in,0)) +
SUM(NVL(wppb.tl_resource_in,0)) +
SUM(NVL(wppb.tl_outside_processing_in,0)) +
SUM(NVL(wppb.tl_overhead_in,0)) value_in,
SUM(NVL(pl_material_out,0)) +
SUM(NVL(wppb.pl_material_overhead_out,0)) +
SUM(NVL(wppb.pl_resource_out,0)) +
SUM(NVL(wppb.pl_outside_processing_out,0)) +
SUM(NVL(wppb.pl_overhead_out,0)) +
SUM(NVL(wppb.tl_resource_out,0)) +
SUM(NVL(wppb.tl_outside_processing_out,0)) +
SUM(NVL(wppb.tl_overhead_out,0)) value_out
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND EXISTS
( SELECT 'X'
FROM wip_entities we
WHERE we.wip_entity_id = wppb.wip_entity_id
AND we.primary_item_id IS NULL
AND we.entity_type not in (6,7) -- Added for R12 PAC eAM enhancement to
-- exclude eAM jobs at the PAC period close
)
GROUP BY
wppb.wip_entity_id,
wppb.line_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
1, -- Level Type
3, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.tl_resource_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
1, -- Level Type
4, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.tl_outside_processing_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
1, -- Level Type
5, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.tl_overhead_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
2, -- Level Type
1, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.pl_material_temp_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
2, -- Level Type
2, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.pl_material_overhead_temp_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
2, -- Level Type
3, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.pl_resource_temp_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
2, -- Level Type
4, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.pl_outside_processing_temp_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
INSERT INTO wip_pac_actual_cost_details wpacd
(
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_jobclose_rec.txn_id,
2, -- Level Type
5, -- CE
NULL, -- resource_id
NULL, -- basis_resource_id
SYSDATE,
SUM(NVL(wppb.pl_overhead_temp_var,0)),
NULL, -- 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 wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_group_id = p_cost_group_id
AND wppb.wip_entity_id = c_jobclose_rec.entity_id;
UPDATE wip_pac_period_balances wppb
SET tl_resource_var = NVL(tl_resource_in,0)
- NVL(tl_resource_out,0),
tl_outside_processing_var = NVL(tl_outside_processing_in,0)
- NVL(tl_outside_processing_out,0),
tl_overhead_var = NVL(tl_overhead_in,0) - NVL(tl_overhead_out,0),
pl_material_var = NVL(pl_material_in,0) - NVL(pl_material_out,0),
pl_material_overhead_var = NVL(pl_material_overhead_in,0) - NVL(pl_material_overhead_out,0),
pl_resource_var = NVL(pl_resource_in,0) - NVL(pl_resource_out,0),
pl_outside_processing_var = NVL(pl_outside_processing_in,0) - NVL(pl_outside_processing_out,0),
pl_overhead_var = NVL(pl_overhead_in,0) - NVL(pl_overhead_out,0),
-- Update the vartemp columns with Actual variance during job close
-- var columns contains total variance
pl_material_temp_var = NVL(pl_material_in,0) - NVL(pl_material_out,0)
- NVL(pl_material_var,0),
pl_material_overhead_temp_var = NVL(pl_material_overhead_in,0) - NVL(pl_material_overhead_out,0)
- NVL(pl_material_overhead_var,0),
pl_resource_temp_var = NVL(pl_resource_in,0) - NVL(pl_resource_out,0)
- NVL(pl_resource_var,0),
pl_outside_processing_temp_var = NVL(pl_outside_processing_in,0) - NVL(pl_outside_processing_out,0)
- NVL(pl_outside_processing_var,0),
pl_overhead_temp_var = NVL(pl_overhead_in,0) - NVL(pl_overhead_out,0)
- NVL(pl_overhead_var,0),
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 = p_entity_id;