[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_count
FROM fnd_concurrent_requests FCR
WHERE FCR.program_application_id = l_prog_app_id
AND FCR.concurrent_program_id = l_prog_id
AND FCR.argument1 = to_char(p_legal_entity_id)
AND FCR.argument2 = to_char(p_cost_type_id)
AND FCR.argument4 = to_char(p_cost_group_id) -- Also adding CG for check
-- Adding the condions as estimating all jobs will not estimate an already
-- estimated job. So estimaing a specific job should have no concurreny problem.
AND FCR.argument6 = to_char(p_job_option)
AND nvl(FCR.argument8,-999) = to_char(nvl(p_wip_entity_id,-999))
AND FCR.phase_code = 'R';
SELECT -1 * cst_wip_cost_estimates_s.NEXTVAL
INTO l_estimation_group_id
FROM DUAL;
/* Insert all discrete jobs not present in estimation table i.e. All
WorkOrders that are being estimated for a cost type for the first time
Also the stsus of WorkOrder should
AND then
Update status flag in PAC estimate status table for all jobs to be
estimated for each job option */
IF p_job_option=1 then
l_stmt_num := 25;
INSERT INTO CST_PAC_EAM_WO_EST_STATUSES es
( legal_entity_id,
cost_group_id,
wip_entity_id,
organization_id,
cost_type_id,
estimation_status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_estimation_req_id,
LAST_ESTIMATION_DATE
)
( SELECT
p_legal_entity_id,
p_cost_group_id,
wdj.wip_entity_id,
wdj.organization_id,
p_cost_type_id,
NULL,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_request_id,
SYSDATE
FROM wip_discrete_jobs wdj,
wip_entities we,
cst_cost_group_assignments ccga
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = ccga.organization_id
AND ccga.cost_group_id = p_cost_group_id
AND we.entity_type = 6
AND NOT EXISTS ( SELECT 'Not existing jobs'
FROM CST_PAC_EAM_WO_EST_STATUSES es1
WHERE es1.wip_entity_id = wdj.wip_entity_id
AND es1.legal_entity_id = p_legal_entity_id
AND es1.cost_type_id = p_cost_type_id
AND es1.cost_group_id = p_cost_group_id
)
AND wdj.status_type IN (1,3,4,6,17)
AND p_job_option = 1
AND p_entity_type = 6
AND EXISTS ( SELECT 'X'
FROM wip_entities we
WHERE we.wip_entity_id = wdj.wip_entity_id
AND we.entity_type = p_entity_type)
);
UPDATE CST_PAC_EAM_WO_EST_STATUSES es
SET es.estimation_status = l_estimation_group_id,
es.last_update_date = SYSDATE,
es.last_updated_by = l_user_id,
es.last_estimation_req_id = l_request_id
WHERE es.legal_entity_id = p_legal_entity_id
AND es.cost_type_id = p_cost_type_id
AND es.cost_group_id = p_cost_group_id
AND p_job_option = 1
AND p_entity_type = 6
AND NVL(es.estimation_status,1) <> 7 -- for all jobs do not re-estimate
AND EXISTS ( SELECT 'X'
FROM wip_entities we
WHERE we.wip_entity_id = es.wip_entity_id
AND we.entity_type = p_entity_type
)
AND NVL(es.estimation_status,1) > 0
AND EXISTS ( SELECT 'Status Check for WO'
FROM wip_discrete_jobs wdj
WHERE wdj.status_type IN (1,3,4,6,17)
AND wdj.wip_entity_id = nvl(p_wip_entity_id,wdj.wip_entity_id)
AND wdj.wip_entity_id = es.wip_entity_id
)
RETURNING es.wip_entity_id BULK COLLECT INTO l_wip_entity_id_tab;
INSERT INTO CST_PAC_EAM_WO_EST_STATUSES es
( legal_entity_id,
cost_group_id,
wip_entity_id,
organization_id,
cost_type_id,
estimation_status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_estimation_req_id,
LAST_ESTIMATION_DATE
)
( SELECT p_legal_entity_id,
p_cost_group_id,
wdj.wip_entity_id,
wdj.organization_id,
p_cost_type_id,
NULL,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_request_id,
SYSDATE
FROM wip_discrete_jobs wdj, wip_entities we
WHERE wdj.wip_entity_id = we.wip_entity_id
AND we.entity_type = 6
AND NOT EXISTS
( SELECT 'Not existing jobs'
FROM CST_PAC_EAM_WO_EST_STATUSES es1
WHERE es1.wip_entity_id = p_wip_entity_id
AND es1.legal_entity_id = p_legal_entity_id
AND es1.cost_type_id = p_cost_type_id
AND es1.cost_group_id = p_cost_group_id
)
AND wdj.status_type IN (1,3,4,6,17)
AND p_job_option = 2
AND wdj.wip_entity_id = p_wip_entity_id );
UPDATE CST_PAC_EAM_WO_EST_STATUSES es
SET es.estimation_status = l_estimation_group_id,
es.last_update_date = SYSDATE,
es.last_updated_by = l_user_id,
es.last_estimation_req_id = l_request_id
WHERE es.legal_entity_id = p_legal_entity_id
AND es.cost_group_id = p_cost_group_id
AND es.cost_type_id = p_cost_type_id
AND p_job_option = 2
AND es.wip_entity_id = p_wip_entity_id
AND NVL(es.estimation_status,1) > 0
AND EXISTS ( SELECT 'Status Check for WO'
FROM wip_discrete_jobs wdj
WHERE wdj.status_type IN (1,3,4,6,17)
AND wdj.wip_entity_id = nvl(p_wip_entity_id,wdj.wip_entity_id)
AND wdj.wip_entity_id = es.wip_entity_id
)
RETURNING es.wip_entity_id BULK COLLECT INTO l_wip_entity_id_tab;
TO_CHAR(SQL%ROWCOUNT) ||' Job Record(s) Updated with Group Id: '
||TO_CHAR(l_estimation_group_id));
Delete_PAC_EamPerBal(
p_api_version => 1.0,
p_wip_entity_id_tab => l_wip_entity_id_tab,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_api_message := 'CST_PacEamCost_GRP.delete_PacEamPerBal() failed';
|| 'Delete/Update successful in delete_eamperbal';
/* Added the call to Delete_PAC_eamBalAcct as part of
eAM enhancements Project - R12 */
Delete_PAC_eamBalAcct (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_wip_entity_id_tab => l_wip_entity_id_tab,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_api_message := 'CST_PacEamCost_GRP.delete_PAC_EamBalAcct() failed';
l_api_message := 'CST_PacEamCost_GRP. delete_PAC_EamBalAcct ('
|| to_char(l_stmt_num) || '): '
|| 'Delete/Update successful in delete_pac_eambalacct';
/* Delete from the global temp table just to make sure it is empty */
DELETE FROM cst_eam_direct_items_temp;
CST_eamCost_PUB.Insert_tempEstimateDetails (
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_entity_id_tab => l_entity_id_tab
);
l_api_message := 'Error: CST_eamCost_PUB.Insert_tempEstimateDetails()';
l_api_message := 'CST_eamCost_PUB.Insert_tempEstimateDetails ('
|| to_char(l_stmt_num) || '): '
|| 'Insert int CEDIV successful Insert_tempEstimateDetails';
UPDATE CST_PAC_EAM_WO_EST_STATUSES
SET estimation_status = 7,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id,
last_update_date = SYSDATE
WHERE wip_entity_id = l_wip_entity_id_tab(l_index)
AND legal_entity_id = p_legal_entity_id
AND cost_type_id = p_cost_type_id
AND cost_group_id = p_cost_group_id;
UPDATE CST_PAC_EAM_WO_EST_STATUSES
SET estimation_status = 3,
last_update_date = SYSDATE,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id
WHERE wip_entity_id = l_wip_entity_id_tab(l_index)
AND legal_entity_id = p_legal_entity_id
AND cost_type_id = p_cost_type_id
AND cost_group_id = p_cost_group_id;
WHEN OTHERS THEN -- Error in delete is caught here too
ROLLBACK TO Estimate_PAC_WipJobs_PUB;
UPDATE CST_PAC_EAM_WO_EST_STATUSES
SET estimation_status = 1,
last_update_date = SYSDATE,
last_estimation_date = SYSDATE,
last_estimation_req_id = l_request_id
WHERE estimation_status = l_estimation_group_id
AND wip_entity_id = l_wip_entity_id_tab(l_index)
AND legal_entity_id = p_legal_entity_id
AND cost_type_id = p_cost_type_id
AND cost_group_id = p_cost_group_id;
PROCEDURE Delete_PAC_EamPerBal (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_legal_entity_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_cost_type_id IN NUMBER,
p_wip_entity_id_tab IN CST_PacEamCost_GRP.G_WIP_ENTITY_TYP
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_PAC_EamPerBal';
SELECT period_set_name,
period_name,
maint_cost_category,
sum(NVL(system_estimated_mat_cost,0)) sys_mat,
sum(NVL(system_estimated_lab_cost,0)) sys_lab,
sum(NVL(system_estimated_eqp_cost,0)) sys_eqp
FROM cst_pac_eam_period_balances
WHERE wip_entity_id = c_wip_entity_id
AND organization_id = c_organization_id
AND legal_entity_id = p_legal_entity_id
AND cost_group_id = p_cost_group_id
AND cost_type_id = p_cost_type_id
GROUP BY period_set_name,
period_name,
maint_cost_category;
'Delete_PAC_EamPerBal <<');
SAVEPOINT Delete_PAC_EamPerBal_PUB;
SELECT asset_group_id,
asset_number,
organization_id
INTO l_asset_group_id,
l_asset_number,
l_organization_id
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id_tab(l_index);
UPDATE cst_pac_eam_asset_per_balances
SET system_estimated_mat_cost = system_estimated_mat_cost -
v_est_rec.sys_mat,
system_estimated_lab_cost = system_estimated_lab_cost -
v_est_rec.sys_lab,
system_estimated_eqp_cost = system_estimated_eqp_cost -
v_est_rec.sys_eqp
WHERE legal_entity_id = p_legal_entity_id
AND cost_group_id = p_cost_group_id
AND cost_type_id = p_cost_type_id
AND period_set_name = v_est_rec.period_set_name
AND period_name = v_est_rec.period_name
AND inventory_item_id = l_asset_group_id
AND serial_number = l_asset_number
AND maint_cost_category = v_est_rec.maint_cost_category;
DELETE from cst_pac_eam_asset_per_balances
WHERE NVL(actual_mat_cost,0) = 0
AND NVL(actual_lab_cost,0) = 0
AND NVL(actual_eqp_cost,0) = 0
AND NVL(system_estimated_mat_cost,0) = 0
AND NVL(system_estimated_lab_cost,0) = 0
AND NVL(system_estimated_eqp_cost,0) = 0
AND inventory_item_id = l_asset_group_id
AND serial_number = l_asset_number
AND legal_entity_id = p_legal_entity_id
AND cost_group_id = p_cost_group_id
AND cost_type_id = p_cost_type_id ;
'Delete/Update CPEAPB successful for ' || TO_CHAR(p_wip_entity_id_tab(l_index)));
UPDATE cst_pac_eam_period_balances
SET system_estimated_mat_cost = 0,
system_estimated_lab_cost = 0,
system_estimated_eqp_cost = 0
WHERE wip_entity_id = p_wip_entity_id_tab(l_index)
AND legal_entity_id = p_legal_entity_id
AND cost_group_id = p_cost_group_id
AND cost_type_id = p_cost_type_id ;
DELETE FROM cst_pac_eam_period_balances
WHERE actual_mat_cost = 0
AND NVL(actual_lab_cost,0) = 0
AND NVL(actual_eqp_cost,0) = 0
AND NVL(system_estimated_mat_cost,0) = 0
AND NVL(system_estimated_lab_cost,0) = 0
AND NVL(system_estimated_eqp_cost,0) = 0
AND wip_entity_id = p_wip_entity_id_tab(l_index)
AND legal_entity_id = p_legal_entity_id
AND cost_group_id = p_cost_group_id
AND cost_type_id = p_cost_type_id ;
'Delete_PAC_EamPerBal >>'
);
ROLLBACK TO Delete_PAC_EamPerBal_PUB;
ROLLBACK TO Delete_PAC_EamPerBal_PUB;
ROLLBACK TO Delete_PAC_EamPerBal_PUB;
END Delete_PAC_EamPerBal;
SELECT wor.operation_seq_num operation_seq_num,
crc.resource_rate resource_rate,
wor.uom_code uom,
wor.usage_rate_or_amount resource_usage,
DECODE(br.functional_currency_flag,
1, 1,
NVL(crc.resource_rate,0))
* wor.usage_rate_or_amount
* DECODE(wor.basis_type,
1, l_lot_size, 2, 1, 1) raw_resource_value,
ROUND(DECODE(br.functional_currency_flag,
1, 1,
NVL(crc.resource_rate,0))
* wor.usage_rate_or_amount
* DECODE(wor.basis_type,
1, l_lot_size,
2, 1, 1) ,l_ext_precision) resource_value,
wor.resource_id resource_id,
wor.resource_seq_num resource_seq_num,
wor.basis_type basis_type,
wor.usage_rate_or_amount
* DECODE(wor.basis_type,
1, l_lot_size,
2, 1, 1) usage_rate_or_amount,
wor.standard_rate_flag standard_flag,
wor.department_id department_id,
br.functional_currency_flag functional_currency_flag,
br.cost_element_id cost_element_id,
br.resource_type resource_type
FROM wip_operation_resources wor,
bom_resources br,
cst_resource_costs crc
WHERE wor.wip_entity_id = p_wip_entity_id
AND br.resource_id = wor.resource_id
AND br.organization_id = wor.organization_id
AND crc.resource_id = wor.resource_id
AND crc.cost_type_id = l_pac_rates_id;
SELECT cdo.overhead_id ovhd_id,
cdo.rate_or_amount actual_cost,
cdo.basis_type basis_type,
ROUND(cdo.rate_or_amount
* DECODE(cdo.basis_type,
3, p_res_units, p_res_value),
l_ext_precision) rbo_value,
cdo.department_id
FROM cst_resource_overheads cro,
cst_department_overheads cdo
WHERE cdo.department_id = p_dept_id
AND cdo.organization_id = p_organization_id
AND cdo.cost_type_id = l_pac_rates_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 the materials reqt from WRO for the wip Entity */
CURSOR c_wro IS
SELECT wro.operation_seq_num operation_seq_num,
wro.department_id department_id,
ROUND(SUM(NVL(wro.required_quantity,0)
* DECODE(msi.eam_item_type,
3, decode(wdj.issue_zero_cost_flag,
'Y',0,
nvl(cpic.item_cost,0)),
NVL(cpic.item_cost,0))), l_ext_precision) mat_value,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,
'Y',0,
nvl(cpic.material_cost,0)),
NVL(cpic.material_cost,0))), l_ext_precision) material_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,
'Y',0,
nvl(cpic.material_overhead_cost,0)),
NVL(cpic.material_overhead_cost,0))), l_ext_precision)
material_overhead_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,
'Y',0,
nvl(cpic.resource_cost,0)),
NVL(cpic.resource_cost,0))), l_ext_precision)
resource_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,
'Y',0,
nvl(cpic.outside_processing_cost,0)),
NVL(cpic.outside_processing_cost,0))), l_ext_precision)
outside_processing_cost,
ROUND(SUM(NVL(wro.required_quantity,0) *
decode(msi.eam_item_type,
3,decode(wdj.issue_zero_cost_flag,
'Y',0,
nvl(cpic.overhead_cost,0)),
NVL(cpic.overhead_cost,0))), l_ext_precision) overhead_cost
FROM wip_requirement_operations wro,
cst_pac_item_costs cpic,
mtl_system_items_b msi,
wip_discrete_jobs wdj
WHERE wro.wip_entity_id = p_wip_entity_id
AND wdj.wip_entity_id = wro.wip_entity_id
AND cpic.inventory_item_id = wro.inventory_item_id
AND cpic.cost_group_id = p_cost_group_id
AND cpic.pac_period_id = l_prior_period_id /* Prior period id */
AND wro.wip_supply_type IN (1,4)
AND nvl(wro.released_quantity,-1) <> 0
/* Non stockable items will be included in c_wrodi */
AND msi.organization_id = wro.organization_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.stock_enabled_flag = 'Y'
GROUP BY wro.operation_seq_num,
wro.department_id;
/* Cursor to select any non-stockable based direct items, exclude
those which have REQ or PO to be picked by c_pda*/
CURSOR c_wrodi IS
SELECT wro.operation_seq_num operation_seq_num,
wro.department_id department_id,
msi.inventory_item_id item_id,
mic.category_id category_id,
ROUND(SUM(DECODE(SIGN(NVL(wro.required_quantity,0)
- NVL(cediv.quantity_ordered,0)),
1, NVL(wro.required_quantity,0)
- NVL(cediv.quantity_ordered,0),
0)
* NVL(wro.unit_price,0)), l_ext_precision) mat_value
FROM wip_requirement_operations wro,
(SELECT ced.work_order_number,
ced.organization_id,
ced.task_number,
ced.item_id,
SUM(inv_convert.inv_um_convert(ced.item_id,
NULL,
ced.quantity_ordered,
ced.uom_code,
msi.primary_uom_code,
NULL,
NULL)
) quantity_ordered
/* We convert to primary_uom because the required_quantity in WRO is
always in the primary unit of measure. Sum is needed because there
could be multiple POs/Reqs for the same non-stockable item */
FROM cst_eam_direct_items_temp ced,
mtl_system_items_b msi
WHERE ced.item_id = msi.inventory_item_id
AND ced.organization_id = msi.organization_id
AND ced.work_order_number = p_wip_entity_id
GROUP BY ced.work_order_number,
ced.organization_id,
ced.task_number,
ced.item_id
) cediv,
mtl_system_items_b msi,
mtl_item_categories mic,
mtl_default_category_sets mdcs
WHERE wro.wip_entity_id = p_wip_entity_id
AND cediv.work_order_number(+) = wro.wip_entity_id
AND cediv.item_id(+) = wro.inventory_item_id
AND cediv.organization_id(+) = wro.organization_id
AND cediv.task_number(+) = wro.operation_seq_num
AND wro.wip_supply_type IN (1,4)
AND msi.organization_id = wro.organization_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.stock_enabled_flag = 'N'
AND msi.inventory_item_id = mic.inventory_item_id
AND mic.category_set_id = mdcs.category_set_id
AND mic.organization_id = wro.organization_id
AND mdcs.functional_area_id = 2
GROUP BY wro.operation_seq_num,
wro.department_id,
msi.inventory_item_id,
mic.category_id;
/* Cursor to select any description based direct items, exclude those which have
REQ or PO to be picked by c_pda*/
CURSOR c_wedi IS
SELECT wedi.operation_seq_num operation_seq_num,
wedi.department_id department_id,
wedi.purchasing_category_id category_id,
wedi.direct_item_sequence_id direct_item_id,
ROUND(
DECODE(cedit.order_type_lookup_code,
'FIXED PRICE', NVL(wedi.amount,0) * NVL(cedit.currency_rate,1) - sum( NVL(cedit.amount_delivered ,0)),
'RATE', NVL(wedi.amount,0) * NVL(cedit.currency_rate,1) - sum(NVL(cedit.amount_delivered ,0)),
DECODE(SIGN(NVL(wedi.required_quantity,0)
- SUM(inv_convert.inv_um_convert(NULL,
NULL,
NVL(cedit.quantity_ordered,0),
NVL(cedit.uom_code, wedi.uom),
wedi.uom,
NULL,
NULL))),
1, (NVL(wedi.required_quantity,0)
- SUM(inv_convert.inv_um_convert(NULL,
NULL,
NVL(cedit.quantity_ordered,0),
NVL(cedit.uom_code, wedi.uom),
wedi.uom,
NULL,
NULL))),
0) * NVL(wedi.unit_price, 0) * NVL(cedit.currency_rate,1)), l_ext_precision) wedi_value
FROM wip_eam_direct_items wedi,
cst_eam_direct_items_temp cedit
WHERE wedi.wip_entity_id = p_wip_entity_id
AND cedit.work_order_number(+) = wedi.wip_entity_id
AND cedit.organization_id(+) = wedi.organization_id
AND cedit.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
AND cedit.task_number(+) = wedi.operation_seq_num
/* AND cedit.category_id(+) = wedi.purchasing_category_id Commented for bug 5478136 */
GROUP BY wedi.operation_seq_num,
wedi.department_id,
wedi.purchasing_category_id,
wedi.direct_item_sequence_id,
NVL(wedi.required_quantity,0),
NVL(wedi.unit_price,0),
cedit.order_type_lookup_code,
NVL(wedi.amount,0),
NVL(cedit.currency_rate,1);
SELECT ROUND(SUM(decode(NVL(pla.order_type_lookup_code,'QUANTITY'),
'RATE',NVL(cedit.amount,0) - (NVL(pda.amount_cancelled,0)
+ /* Tax */ PO_TAX_SV.get_tax('PO',pda.po_distribution_id))* NVL(cedit.currency_rate,1),
'FIXED PRICE',NVL(cedit.amount,0) - (NVL(pda.amount_cancelled,0)
+ /* Tax */ PO_TAX_SV.get_tax('PO',pda.po_distribution_id))* NVL(cedit.currency_rate,1),
NVL(plla.price_override,0) *
(NVL(pda.quantity_ordered,0) - NVL(pda.quantity_cancelled,0)
+ /* Tax */ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)) * NVL(cedit.currency_rate,1))
), l_ext_precision
) pda_value,
pda.wip_operation_seq_num operation_seq_num,
pla.category_id category_id,
nvl(pha.approved_date, pha.last_update_date) category_date
FROM po_distributions_all pda,
po_line_locations_all plla,
po_headers_all pha,
po_lines_all pla,
cst_eam_direct_items_temp cedit
WHERE cedit.work_order_number = p_wip_entity_id
AND cedit.organization_id = l_organization_id
AND cedit.task_number = pda.wip_operation_seq_num
AND cedit.category_id = pla.category_id
AND pha.po_header_id = cedit.po_header_id
AND pla.po_line_id = cedit.po_line_id
AND pda.wip_entity_id = cedit.work_order_number
AND pda.po_header_id = cedit.po_header_id
AND pda.destination_organization_id = cedit.organization_id
AND pda.po_line_id = pla.po_line_id
AND plla.line_location_id = pda.line_location_id
GROUP BY pda.wip_operation_seq_num,
pla.category_id,
pha.approved_date,
pha.last_update_date,
cedit.currency_rate
UNION ALL
SELECT ROUND(SUM(
DECODE(NVL(prla.order_type_lookup_code,'QUANTITY'),
'RATE', NVL(cedit.amount,NVL(prla.amount * cedit.currency_rate,0)),
'FIXED PRICE', NVL(cedit.amount,NVL(prla.amount * cedit.currency_rate,0)),
NVL(prla.unit_price,0) * NVL(prla.quantity,0))
* NVL(cedit.currency_rate,1)), l_ext_precision) pda_value,
prla.wip_operation_seq_num operation_seq_num,
prla.category_id category_id,
prha.last_update_date category_date
FROM po_requisition_lines_all prla,
po_requisition_headers_all prha,
cst_eam_direct_items_temp cedit
WHERE cedit.work_order_number = p_wip_entity_id
AND cedit.organization_id = l_organization_id
AND cedit.task_number = prla.wip_operation_seq_num
AND cedit.category_id = prla.category_id
/*to ensure that we do not double count*/
AND cedit.po_header_id IS NULL
AND prha.requisition_header_id = cedit.requisition_header_id
AND prla.destination_organization_id = cedit.organization_id
AND prla.wip_entity_id = cedit.work_order_number
AND prla.requisition_line_id = cedit.requisition_line_id
GROUP BY prla.wip_operation_seq_num,
prla.category_id,
prha.last_update_date,
cedit.currency_rate;
select material_account,
material_overhead_account,
resource_account,
outside_processing_account,
overhead_account,
class_code wip_acct_class
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
SELECT entity_type,
organization_id
INTO l_entity_type,
l_organization_id
FROM wip_entities we
WHERE we.wip_entity_id = p_wip_entity_id;
SELECT start_quantity,
scheduled_completion_date
INTO l_lot_size,
l_scheduled_completion_date
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = p_wip_entity_id;
SELECT count(*)
INTO l_dummy
FROM cst_pac_periods cpp
WHERE cpp.pac_period_id = p_period_id
AND LEGAL_ENTITY = p_legal_entity_id
AND COST_TYPE_ID = p_cost_type_id
AND l_trunc_le_sched_comp_date BETWEEN cpp.PERIOD_START_DATE
AND cpp.PERIOD_END_DATE;
SELECT cpp.PAC_PERIOD_ID,
cpp.period_set_name,
cpp.period_name,
cpp.period_start_date
INTO l_acct_period_id,
l_period_set_name,
l_period_name,
l_period_start_date
FROM CST_PAC_periods cpp
WHERE cpp.pac_period_id = p_period_id
AND l_trunc_le_sched_comp_date BETWEEN cpp.period_start_date
AND cpp.period_end_date;
SELECT gp.period_set_name, gp.period_name,
gp.start_date
INTO l_period_set_name, l_period_name,
l_period_start_date
FROM gl_periods gp,
gl_sets_of_books gsob,
cst_organization_definitions ood
WHERE ood.organization_id = l_organization_id
AND gsob.set_of_books_id = ood.set_of_books_id
AND gp.period_set_name = gsob.period_set_name
AND gp.adjustment_period_flag = 'N'
AND gp.period_type = gsob.accounted_period_type
AND l_trunc_le_sched_comp_date BETWEEN gp.start_date
AND gp.end_date;
SELECT NVL(MAX(cpp.pac_period_id), -1)
INTO l_prior_period_id
FROM cst_pac_periods cpp
WHERE cpp.cost_type_id = p_cost_type_id
AND cpp.legal_entity = p_legal_entity_id
AND cpp.pac_period_id < p_period_id;
SELECT nvl(max(pac_rates_cost_type_id),-1)
INTO l_pac_rates_id
FROM cst_le_cost_types
WHERE legal_entity = p_legal_entity_id
AND cost_type_id = p_cost_type_id;
/* Insert estimated resource values into WPEPB and CPEAPB */
InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wor_rec.resource_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_eamperbal() returned error ';
Insert_PAC_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_wor_rec.resource_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => c_wor_rec.cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_PAC_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_rbo_rec.rbo_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_eamperbal() returned error';
/* Insert Resource based overheads only if the value is greater than 0 */
IF ( l_sum_rbo <> 0 ) THEN
l_stmt_num := 283;
Insert_PAC_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wor_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_overhead_account,
p_value => l_sum_rbo,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => 5, /* Overhead cost Element*/
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_PAC_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => c_wro_rec.department_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wro_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wro_rec.mat_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_eamperbal() returned error';
l_api_message :=' Calling Insert_eamBalAcct... ';
Insert_PAC_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wro_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_account,
p_value => l_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_PAC_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
select cceea.mnt_cost_element_id, cceea.mfg_cost_element_id
into l_eam_cost_element, l_mfg_cost_element_id
from cst_cat_ele_exp_assocs cceea
where cceea.category_id = c_wrodi_rec.category_id
and NVL(cceea.end_date, SYSDATE) + 1 > SYSDATE
and cceea.start_date <= sysdate;
/* Insert estimated material values into WPEPB and CPEAPB */
InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => c_wrodi_rec.department_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wrodi_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wrodi_rec.mat_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_eamperbal() returned error';
Insert_PAC_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wrodi_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_wrodi_rec.mat_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_PAC_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
SELECT cceea.mnt_cost_element_id, cceea.mfg_cost_element_id
INTO l_eam_cost_element, l_mfg_cost_element_id
FROM cst_cat_ele_exp_assocs cceea
WHERE cceea.category_id = c_wedi_rec.category_id
AND NVL(cceea.end_date, SYSDATE) + 1 > SYSDATE
and cceea.start_date <= sysdate;
InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => c_wedi_rec.department_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wedi_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_wedi_rec.wedi_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_eamperbal() returned error';
Insert_PAC_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_wedi_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_wedi_rec.wedi_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_PAC_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
SELECT department_id
INTO l_dept_id
FROM wip_operations wo
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.operation_seq_num = c_pda_rec.operation_seq_num;
SELECT cceea.mnt_cost_element_id, cceea.mfg_cost_element_id
INTO l_eam_cost_element, l_mfg_cost_element_id
FROM cst_cat_ele_exp_assocs cceea
WHERE cceea.category_id = c_pda_rec.category_id
AND c_pda_rec.category_date >= cceea.start_date
AND c_pda_rec.category_date < (nvl(cceea.end_date, sysdate) + 1);
/* Insert estimated material values into WPEPB and CPEAPB */
InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_pda_rec.operation_seq_num,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 2,
p_value => c_pda_rec.pda_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_eamperbal() returned error';
Insert_PAC_eamBalAcct(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_acct_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_org_id => l_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => c_pda_rec.operation_seq_num,
p_period_start_date => l_period_start_date,
p_account_ccid => l_acct_id,
p_value => c_pda_rec.pda_value,
p_txn_type => l_eam_cost_element,
p_wip_acct_class => l_wip_acct_class,
p_mfg_cost_element_id => l_mfg_cost_element_id,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'Insert_PAC_eamBalAcct error';
FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
||TO_CHAR(l_stmt_num)
||'): ', l_api_message);
PROCEDURE InsertUpdate_PAC_eamPerBal (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_legal_entity_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_cost_type_id IN NUMBER,
p_period_id IN NUMBER := null,
p_period_set_name IN VARCHAR2 := null,
p_period_name IN VARCHAR2 := null,
p_organization_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_owning_dept_id IN NUMBER,
p_dept_id IN NUMBER,
p_maint_cost_cat IN NUMBER,
p_opseq_num IN NUMBER,
p_eam_cost_element IN NUMBER,
p_asset_group_id IN NUMBER,
p_asset_number IN VARCHAR2,
p_value_type IN NUMBER,
p_value IN NUMBER,
p_user_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER,
p_prog_app_id IN NUMBER,
p_login_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate_PAC_eamPerBal';
'InsertUpdate_PAC_eamPerBal <<');
SAVEPOINT InsertUpdate_PAC_eamPerBal_PUB;
SELECT pac_period_id,
period_set_name,
period_name,
period_start_date
INTO l_period_id,
l_period_set_name,
l_period_name,
l_period_start_date
FROM CST_PAC_PERIODS
WHERE cost_type_id = p_cost_type_id
AND (pac_period_id = p_period_id
OR (period_set_name = p_period_set_name
AND period_name = p_period_name));
SELECT 0,
period_set_name,
period_name,
start_date
INTO l_period_id,
l_period_set_name,
l_period_name,
l_period_start_date
FROM gl_periods
WHERE period_set_name = l_period_set_name
AND period_name = l_period_name;
SELECT count(*)
INTO l_count
FROM cst_pac_eam_period_balances
WHERE period_set_name = l_period_set_name
AND period_name = l_period_name
AND pac_period_id = l_period_id
AND organization_id = p_organization_id
AND wip_entity_id = p_wip_entity_id
AND maint_cost_category = p_maint_cost_cat
AND owning_dept_id = p_owning_dept_id
AND operations_dept_id = p_dept_id
AND operation_seq_num = p_opseq_num
AND cost_group_id = p_cost_group_id
AND cost_type_id = p_cost_type_id
AND legal_entity_id = p_legal_entity_id;
IF l_count <> 0 THEN /* If records already exist, Update */
l_stmt_num := 420;
l_statement := 'UPDATE cst_pac_eam_period_balances SET '
|| l_column || '=' || 'nvl('|| l_column || ',0) + nvl(:p_value,0)'
|| ', last_update_date = sysdate'
|| ', last_updated_by = :p_user_id'
|| ', last_update_login = :p_login_id'
|| ' WHERE period_set_name = :l_period_set_name'
|| ' AND cost_type_id = :p_cost_type_id'
|| ' AND cost_group_id = :p_cost_group_id'
|| ' AND legal_entity_id = :p_legal_entity_id'
|| ' AND period_name = :l_period_name'
|| ' AND organization_id = :p_organization_id'
|| ' AND wip_entity_id = :p_wip_entity_id'
|| ' AND maint_cost_category = :p_maint_cost_cat'
|| ' AND owning_dept_id = :p_owning_dept_id'
|| ' AND operations_dept_id = :p_dept_id'
|| ' AND operation_seq_num = :p_opseq_num';
'Update Successful for Job id: ' || TO_CHAR(p_wip_entity_id));
ELSE -- Else, no records found, so Insert
l_stmt_num := 430;
INSERT INTO cst_pac_eam_period_balances (
legal_entity_id,
cost_group_id,
cost_type_id,
period_set_name,
period_name,
pac_period_id,
wip_entity_id,
organization_id,
owning_dept_id,
operations_dept_id,
operation_seq_num,
maint_cost_category,
actual_mat_cost,
actual_lab_cost,
actual_eqp_cost,
system_estimated_mat_cost,
system_estimated_lab_cost,
system_estimated_eqp_cost,
period_start_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id
)
VALUES (
p_legal_entity_id,
p_cost_group_id,
p_cost_type_id,
l_period_set_name,
l_period_name,
l_period_id,
p_wip_entity_id,
p_organization_id,
p_owning_dept_id,
p_dept_id,
p_opseq_num,
p_maint_cost_cat,
DECODE(l_col_type, 13, NVL(p_value,0),0), -- actual mat
DECODE(l_col_type, 12, NVL(p_value,0),0), -- actual lab
DECODE(l_col_type, 11, NVL(p_value,0),0), -- actual eqp
DECODE(l_col_type, 23, NVL(p_value,0),0), -- sys est
DECODE(l_col_type, 22, NVL(p_value,0),0), -- sys est
DECODE(l_col_type, 21, NVL(p_value,0),0), -- sys est
l_period_start_date,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_app_id,
p_prog_id
);
'Insert Successful for Job id: ' || TO_CHAR(p_wip_entity_id));
select maintenance_object_id, maintenance_object_type
into l_maint_obj_id, l_maint_obj_type
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
So directly insert into asset_per_bal table
------------------------------------------------------------*/
l_stmt_num := 440;
InsertUpdate_pac_assetPerBal(p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_period_id => l_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => p_organization_id,
p_maint_cost_cat => p_maint_cost_cat,
p_asset_group_id => p_asset_group_id,
p_asset_number => p_asset_number,
p_value => p_value,
p_column => l_column,
p_col_type => l_col_type,
p_period_start_date => l_period_start_date,
p_maintenance_object_id => l_maint_obj_id,
p_maintenance_object_type => l_maint_obj_type,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_assetperbal() returned error';
'InsertUpdate_PAC_eamPerBal >>'
);
ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
END InsertUpdate_PAC_eamPerBal;
PROCEDURE InsertUpdate_PAC_assetPerBal (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_legal_entity_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_cost_type_id IN NUMBER,
p_period_id IN NUMBER := null,
p_period_set_name IN VARCHAR2 := null,
p_period_name IN VARCHAR2 := null,
p_organization_id IN NUMBER,
p_maint_cost_cat IN NUMBER,
p_asset_group_id IN NUMBER,
p_asset_number IN VARCHAR2,
p_value IN NUMBER,
p_column IN VARCHAR2,
p_col_type IN NUMBER,
p_period_start_date IN DATE,
p_maintenance_object_id IN NUMBER,
p_maintenance_object_type IN NUMBER,
p_user_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER,
p_prog_app_id IN NUMBER,
p_login_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate_PAC_assetPerBal';
'InsertUpdate_PAC_assetPerBal <<');
SAVEPOINT InsertUpdate_PAC_astPerBal_PUB;
SELECT count(*)
INTO l_count
FROM cst_pac_eam_asset_per_balances
WHERE period_set_name = p_period_set_name
AND period_name = p_period_name
AND organization_id = p_organization_id
AND inventory_item_id = p_asset_group_id
AND serial_number = p_asset_number
AND maint_cost_category = p_maint_cost_cat
AND cost_group_id = p_cost_group_id
AND cost_type_id = p_cost_type_id
AND legal_entity_id = p_legal_entity_id;
IF l_count > 0 THEN -- If records already exist then Update
l_stmt_num := 505;
l_statement := 'UPDATE cst_pac_eam_asset_per_balances SET '
|| p_column || '='
|| 'nvl('|| p_column || ',0) + nvl(:p_value,0)'
|| ', last_update_date = sysdate'
|| ', last_updated_by = :p_user_id'
|| ' WHERE period_set_name = :p_period_set_name'
|| ' AND period_name = :p_period_name'
|| ' AND organization_id = :p_organization_id'
|| ' AND inventory_item_id = :p_asset_group_id'
|| ' AND serial_number = :p_asset_number'
|| ' AND maint_cost_category = :p_maint_cost_cat'
|| ' AND cost_group_id = :p_cost_group_id'
|| ' AND cost_type_id = :p_cost_type_id'
|| ' AND legal_entity_id = :p_legal_entity_id';
'Update Successful for Serial Number ' || TO_CHAR(p_asset_number));
ELSE -- If no records exist, then Insert
l_stmt_num := 515;
INSERT INTO cst_pac_eam_asset_per_balances (
legal_entity_id,
cost_group_id,
cost_type_id,
period_set_name,
period_name,
pac_period_id,
organization_id,
inventory_item_id,
serial_number,
maint_cost_category,
actual_mat_cost,
actual_lab_cost,
actual_eqp_cost,
system_estimated_mat_cost,
system_estimated_lab_cost,
system_estimated_eqp_cost,
period_start_date,
maintenance_object_id,
maintenance_object_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id
)
VALUES (
p_legal_entity_id,
p_cost_group_id,
p_cost_type_id,
p_period_set_name,
p_period_name,
p_period_id,
p_organization_id,
p_asset_group_id,
p_asset_number,
p_maint_cost_cat,
DECODE(p_col_type, 13, NVL(p_value,0),0), -- actual mat
DECODE(p_col_type, 12, NVL(p_value,0),0), -- actual lab
DECODE(p_col_type, 11, NVL(p_value,0),0), -- actual eqp
DECODE(p_col_type, 23, NVL(p_value,0),0), -- sys est
DECODE(p_col_type, 22, NVL(p_value,0),0), -- sys est
DECODE(p_col_type, 21, NVL(p_value,0),0), -- sys est
p_period_start_date,
p_maintenance_object_id,
p_maintenance_object_type,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_request_id,
p_prog_app_id
);
'Insert Successful for Serial Number ' || TO_CHAR(p_asset_number));
'InsertUpdate_PAC_assetPerBal >>'
);
ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
END InsertUpdate_PAC_assetPerBal;
SELECT cpp.PAC_PERIOD_ID,
cpp.period_set_name,
cpp.period_name
INTO l_pac_period_id,
l_period_set_name,
l_period_name
FROM CST_PAC_periods cpp
WHERE cpp.pac_period_id = p_pac_period_id;
InsertUpdate_PAC_eamPerBal(
p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_legal_entity_id => p_legal_entity_id,
p_cost_group_id => p_cost_group_id,
p_cost_type_id => p_cost_type_id,
p_period_id => l_pac_period_id,
p_period_set_name => l_period_set_name,
p_period_name => l_period_name,
p_organization_id => p_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_owning_dept_id => l_owning_dept_id,
p_dept_id => l_operation_dept_id,
p_maint_cost_cat => l_maint_cost_category,
p_opseq_num => p_op_seq,
p_eam_cost_element => l_eam_cost_element,
p_asset_group_id => l_asset_group_item_id,
p_asset_number => l_asset_number,
p_value_type => 1, --Actuals
p_value => p_value,
p_user_id => p_user_id,
p_request_id => p_request_id,
p_prog_id => p_prog_id,
p_prog_app_id => p_prog_app_id,
p_login_id => p_login_id);
l_api_message := 'insertupdate_PAC_eamperbal() returned error';
'Insert/Update successful for Actuals');
PROCEDURE Insert_PAC_eamBalAcct
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_legal_entity_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_cost_type_id IN NUMBER,
p_period_id IN NUMBER,
p_period_set_name IN VARCHAR2,
p_period_name IN VARCHAR2,
p_org_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_owning_dept_id IN NUMBER,
p_dept_id IN NUMBER,
p_maint_cost_cat IN NUMBER,
p_opseq_num IN NUMBER,
p_period_start_date IN DATE,
p_account_ccid IN NUMBER,
p_value IN NUMBER,
p_txn_type IN NUMBER,
p_wip_acct_class IN VARCHAR2,
p_mfg_cost_element_id IN NUMBER,
p_user_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_id IN NUMBER,
p_prog_app_id IN NUMBER,
p_login_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_PAC_eamBalAcct';
SAVEPOINT Insert_PAC_eamBalAcct_PUB;
/* Update the record if already exists else insert a new one */
MERGE INTO CST_PAC_EAM_BALANCE_BY_ACCTS cebba
USING
(
SELECT NULL FROM DUAL
) temp
ON
(
cebba.legal_entity_id = p_legal_entity_id AND
cebba.cost_group_id = p_cost_group_id AND
cebba.cost_type_id = p_cost_type_id AND
cebba.period_set_name = p_period_set_name AND
cebba.period_name = p_period_name AND
cebba.wip_entity_id = p_wip_entity_id AND
cebba.organization_id = p_org_id AND
cebba.maint_cost_category = p_maint_cost_cat AND
cebba.owning_dept_id = p_owning_dept_id AND
cebba.period_start_date = p_period_start_date AND
cebba.account_id = p_account_ccid AND
cebba.txn_type = p_txn_type AND
cebba.wip_acct_class_code = p_wip_acct_class AND
cebba.mfg_cost_element_id = p_mfg_cost_element_id
)
WHEN MATCHED THEN
UPDATE
SET cebba.acct_value = cebba.acct_value + p_value,
cebba.LAST_UPDATE_DATE = sysdate,
cebba.LAST_UPDATED_BY = p_user_id,
cebba.LAST_UPDATE_LOGIN = p_login_id
WHEN NOT MATCHED THEN
INSERT
(
LEGAL_ENTITY_ID,
COST_GROUP_ID,
COST_TYPE_ID,
PERIOD_SET_NAME,
PERIOD_NAME,
ACCT_PERIOD_ID,
WIP_ENTITY_ID,
ORGANIZATION_ID,
OPERATIONS_DEPT_ID,
OPERATIONS_SEQ_NUM,
MAINT_COST_CATEGORY,
OWNING_DEPT_ID,
PERIOD_START_DATE,
ACCOUNT_ID,
ACCT_VALUE,
TXN_TYPE,
WIP_ACCT_CLASS_CODE,
MFG_COST_ELEMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)VALUES
(
p_legal_entity_id,
p_cost_group_id,
p_cost_type_id,
p_period_set_name,
p_period_name ,
p_period_id ,
p_wip_entity_id,
p_org_id ,
p_dept_id,
p_opseq_num ,
p_maint_cost_cat,
p_owning_dept_id,
p_period_start_date,
p_account_ccid,
p_value ,
p_txn_type,
p_wip_acct_class,
p_mfg_cost_element_id,
sysdate,
p_user_id ,
sysdate,
p_prog_app_id ,
p_login_id
);
'.updated/inserted the record for :' || to_char(p_wip_entity_id)
);
ROLLBACK TO Insert_PAC_eamBalAcct_PUB;
ROLLBACK TO Insert_PAC_eamBalAcct_PUB;
END Insert_PAC_eamBalAcct;
PROCEDURE Delete_PAC_eamBalAcct
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_wip_entity_id_tab IN CST_PacEamCost_GRP.G_WIP_ENTITY_TYP,
p_legal_entity_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_cost_type_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_PAC_eamBalAcct';
SAVEPOINT Delete_PAC_eamBalAcct_PUB;
/* Delete data from CST_PAC_EAM_BALANCE_BY_ACCTS */
FORALL l_index IN p_wip_entity_id_tab.FIRST..p_wip_entity_id_tab.LAST
Delete from CST_PAC_EAM_BALANCE_BY_ACCTS
where wip_entity_id = p_wip_entity_id_tab(l_index)
-- and organization_id=p_org_id -- sikhanna not required
and legal_entity_id = p_legal_entity_id
and cost_group_id = p_cost_group_id
and cost_type_id = p_cost_type_id;
ROLLBACK TO Delete_PAC_eamBalAcct_PUB;
ROLLBACK TO Delete_PAC_eamBalAcct_PUB;
END Delete_PAC_eamBalAcct;