[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION INSERT_WOO (p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_program_id IN NUMBER )
RETURN BOOLEAN IS
l_num_rows NUMBER;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'INSERT_WOO <<<');
INSERT INTO WIP_OPERATION_OVERHEADS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
OVERHEAD_ID,
BASIS_TYPE,
APPLIED_OVHD_UNITS,
APPLIED_OVHD_VALUE,
RELIEVED_OVHD_COMPLETION_UNITS,
RELIEVED_OVHD_SCRAP_UNITS,
RELIEVED_OVHD_COMPLETION_VALUE,
RELIEVED_OVHD_SCRAP_VALUE,
TEMP_RELIEVED_VALUE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE)
(SELECT /* Resource Unit And Value Based Overheads */
WO.wip_entity_id,
WO.operation_seq_num,
WOR.resource_seq_num,
WO.organization_id,
CDO.overhead_id,
CDO.basis_type,
0,
0,
0,
0,
0,
0,
0,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_appl_id,
p_program_id,
sysdate,
sysdate
FROM
cst_department_overheads CDO,
cst_resource_overheads CRO,
wip_operation_resources WOR,
wip_operations WO
WHERE
WO.wip_entity_id = p_wip_entity_id
AND WOR.wip_entity_id = WO.wip_entity_id
AND WOR.organization_id = WO.organization_id
AND CDO.organization_id = WO.organization_id
AND WO.organization_id = p_organization_id
AND CDO.department_id = WO.department_id
AND CDO.overhead_id = CRO.overhead_id
AND CRO.resource_id = WOR.resource_id
AND CRO.cost_type_id = 1
AND CDO.cost_type_id = 1
AND CDO.basis_type in (3,4)
AND WO.operation_seq_num = WOR.operation_seq_num
AND WO.operation_seq_num <= p_operation_seq_num
/* Don't insert if a row already exists 5364135 */
AND NOT EXISTS (SELECT 'Not exists'
FROM wip_operation_overheads woo
WHERE woo.wip_entity_id = WO.wip_entity_id
AND woo.operation_seq_num = WO.operation_seq_num
AND woo.resource_seq_num = WOR.resource_seq_num
AND woo.organization_id = WO.organization_id
AND woo.overhead_id = CDO.overhead_id
AND woo.basis_type = CDO.basis_type)
UNION ALL
SELECT /* Department Based Overheads */
WO.wip_entity_id,
WO.operation_seq_num,
-1,
WO.organization_id,
CDO.overhead_id,
CDO.basis_type,
0,
0,
0,
0,
0,
0,
0,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_prog_appl_id,
p_program_id,
sysdate,
sysdate
FROM
cst_department_overheads CDO,
wip_operations WO
WHERE
WO.wip_entity_id = p_wip_entity_id
AND CDO.department_id = WO.department_id
AND CDO.organization_id = WO.organization_id
AND CDO.cost_type_id = 1
AND CDO.basis_type in (1,2)
AND WO.organization_id = p_organization_id
AND WO.operation_seq_num <= p_operation_seq_num
/* Don't insert if a row already exists 5364135 */
AND NOT EXISTS (SELECT 'Not exists'
FROM wip_operation_overheads woo
WHERE woo.wip_entity_id = WO.wip_entity_id
AND woo.operation_seq_num = WO.operation_seq_num
AND woo.resource_seq_num = -1
AND woo.organization_id = WO.organization_id
AND woo.overhead_id = CDO.overhead_id
AND woo.basis_type = CDO.basis_type));
FND_FILE.put_line(fnd_file.log, to_char(l_num_rows)||' rows inserted into WOO for Job: '||to_char(p_wip_entity_id));
FND_FILE.PUT_LINE (FND_FILE.LOG, 'INSERT_WOO >>>');
FND_FILE.put_line(fnd_file.log, 'Failed to Insert into WOO: '||SQLERRM);
END INSERT_WOO;
FAILED_INSERTING_START_LOT EXCEPTION;
FAILED_INSERTING_WT EXCEPTION;
FAILED_INSERTING_WTA EXCEPTION;
FAILED_INSERTING_MTA EXCEPTION;
FAILED_INSERTING_RESULT_LOT EXCEPTION;
INSERT_WOO_ERROR EXCEPTION;
SELECT available_quantity, wip_entity_id
FROM wsm_sm_starting_jobs
WHERE transaction_id = p_transaction_id;
SELECT start_quantity, wip_entity_id,
nvl(starting_operation_seq_num,10) starting_operation_seq_num,
nvl(starting_intraoperation_step, WIP_CONSTANTS.QUEUE) starting_intraoperation_step,
common_routing_sequence_id
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_transaction_id;
SELECT wip_entity_id
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_transaction_id
AND wip_entity_id not in
( SELECT wip_entity_id
FROM wsm_sm_starting_jobs
WHERE transaction_id = p_transaction_id );
SELECT organization_id,
transaction_type_id,
transaction_date
INTO l_organization_id,
l_transaction_type,
l_transaction_date
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
NVL(FC.extended_precision,NVL(FC.precision,0))
INTO l_min_acct_unit,
l_ext_prec
FROM fnd_currencies FC,
CST_ORGANIZATION_DEFINITIONS O
WHERE O.organization_id = l_organization_id
AND O.currency_code = FC.currency_code;
SELECT acct_period_id
INTO l_acct_period_id
FROM org_acct_periods
WHERE organization_id = l_organization_id
AND l_le_transaction_date
between period_start_date and schedule_close_date;
SELECT wip_entity_id,
operation_seq_num,
intraoperation_step,
job_start_quantity,
available_quantity
INTO l_rep_wip_entity_id,
l_operation_seq_num,
l_intraoperation_step,
l_job_start_quantity,
l_available_quantity
FROM WSM_SM_STARTING_JOBS
WHERE transaction_id = p_transaction_id
AND representative_flag = 'Y';
SELECT SUM(start_quantity)
INTO l_total_resulting_qty
FROM WSM_SM_RESULTING_JOBS
WHERE transaction_id = p_transaction_id;
CSTPSMUT.INSERT_MAT_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
l_transaction_type,
p_mmt_transaction_id,
l_organization_id,
c_result.wip_entity_id,
SPLIT_RESULT_ACT_LTYPE,
c_result.start_quantity,
(c_result.start_quantity / l_total_resulting_qty * l_pl_mtl_net),
(c_result.start_quantity / l_total_resulting_qty * l_pl_mto_net),
(c_result.start_quantity / l_total_resulting_qty * l_pl_res_net),
(c_result.start_quantity / l_total_resulting_qty * l_pl_ovh_net),
(c_result.start_quantity / l_total_resulting_qty * l_pl_osp_net),
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_MTA;
/* Insert into WTA */
l_stmt_num := 110;
CSTPSMUT.INSERT_WIP_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
p_transaction_id,
l_transaction_type,
l_wip_transaction_id,
l_organization_id,
c_result.wip_entity_id,
SPLIT_RESULT_ACT_LTYPE,
c_result.start_quantity,
0, -- This Level Material Cost
0, -- This Level Material Ovhd Cost
(c_result.start_quantity / l_total_resulting_qty * l_tl_res_net),
(c_result.start_quantity / l_total_resulting_qty * l_tl_ovh_net),
(c_result.start_quantity / l_total_resulting_qty * l_tl_osp_net),
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_WTA;
/* Update WPB of resulting Lot */
l_stmt_num := 120;
RAISE FAILED_INSERTING_RESULT_LOT;
/* Update the Amount to be relieved from the parent */
l_total_tl_res := l_total_tl_res + (ROUND(c_result.start_quantity / l_total_resulting_qty * l_tl_res_net / l_min_acct_unit) * l_min_acct_unit);
/* Insert into MTA for Representative Lot */
CSTPSMUT.INSERT_MAT_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
l_transaction_type,
p_mmt_transaction_id,
l_organization_id,
c_start.wip_entity_id,
SPLIT_START_ACT_LTYPE,
-l_total_qty,
-l_total_pl_mtl,
-l_total_pl_mto,
-l_total_pl_res,
-l_total_pl_ovh,
-l_total_pl_osp,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_MTA;
/* Insert TL Accounting into WTA */
CSTPSMUT.INSERT_WIP_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
p_transaction_id,
l_transaction_type,
l_wip_transaction_id,
l_organization_id,
c_start.wip_entity_id,
SPLIT_START_ACT_LTYPE,
-l_total_qty,
0, -- This Level Material Cost
0, -- This Level Material Ovhd Cost
-l_total_tl_res,
-l_total_tl_ovh,
-l_total_tl_osp,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_WTA;
/* Insert Transaction into WT */
l_stmt_num := 160;
CSTPSMUT.INSERT_WIP_TXN(
l_transaction_date,
p_transaction_id,
l_wip_transaction_id,
l_acct_period_id,
c_start.wip_entity_id,
l_operation_seq_num,
11, -- WIP Transaction type
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg,
p_mmt_transaction_id); -- Added for Bug#4307365
RAISE FAILED_INSERTING_WT;
/* Update WPB of Representative Lot */
l_stmt_num := 180;
RAISE FAILED_INSERTING_START_LOT;
l_ins_woo := INSERT_WOO (
new_job.wip_entity_id,
l_organization_id,
l_operation_seq_num,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id );
RAISE INSERT_WOO_ERROR;
SELECT count(*)
INTO l_wta_exists
FROM WIP_TRANSACTION_ACCOUNTS
WHERE transaction_id = l_wip_transaction_id
and rownum=1;
WHEN FAILED_INSERTING_START_LOT THEN
x_err_num := -1;
x_err_code := 'Error Inserting WPB Information for Starting Lot';
x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WT THEN
x_err_num := -1;
x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into Wip Transaction Accounts';
x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_MTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into MTL Transaction Accounts';
x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_RESULT_LOT THEN
x_err_num := -1;
x_err_code := 'Error inserting into WPB for Resulting Lot';
x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN INSERT_WOO_ERROR THEN
x_err_num := -1;
x_err_code := 'Error inserting into WOO';
x_err_msg := 'Error inserting into WOO: CSTPSMUT.COST_SPLIT_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
FAILED_INSERTING_START_LOT EXCEPTION;
FAILED_INSERTING_WT EXCEPTION;
FAILED_INSERTING_WTA EXCEPTION;
FAILED_INSERTING_MTA EXCEPTION;
FAILED_INSERTING_RESULT_LOT EXCEPTION;
INSERT_WOO_ERROR EXCEPTION;
SELECT available_quantity, wip_entity_id
FROM wsm_sm_starting_jobs
WHERE transaction_id = p_transaction_id;
SELECT start_quantity, wip_entity_id,
nvl(starting_operation_seq_num,10) starting_operation_seq_num,
nvl(starting_intraoperation_step, WIP_CONSTANTS.QUEUE) starting_intraoperation_step,
common_routing_sequence_id
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_transaction_id;
SELECT wip_entity_id
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_transaction_id
AND wip_entity_id not in
( SELECT wip_entity_id
FROM wsm_sm_starting_jobs
WHERE transaction_id = p_transaction_id );
SELECT organization_id,
transaction_type_id,
transaction_date
INTO l_organization_id,
l_transaction_type,
l_transaction_date
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
NVL(FC.extended_precision,NVL(FC.precision,0))
INTO l_min_acct_unit,
l_ext_prec
FROM fnd_currencies FC,
CST_ORGANIZATION_DEFINITIONS O
WHERE O.organization_id = l_organization_id
AND O.currency_code = FC.currency_code;
SELECT acct_period_id
INTO l_acct_period_id
FROM org_acct_periods
WHERE organization_id = l_organization_id
AND l_le_transaction_date
between period_start_date and schedule_close_date;
SELECT wip_entity_id,
operation_seq_num,
intraoperation_step,
job_start_quantity,
available_quantity
INTO l_rep_wip_entity_id,
l_operation_seq_num,
l_intraoperation_step,
l_job_start_quantity,
l_available_quantity
FROM WSM_SM_STARTING_JOBS
WHERE transaction_id = p_transaction_id
AND representative_flag = 'Y';
SELECT wip_entity_id
INTO l_result_wip_entity_id
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_transaction_id;
CSTPSMUT.INSERT_MAT_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
l_transaction_type,
p_mmt_transaction_id,
l_organization_id,
c_start.wip_entity_id,
MERGE_START_ACT_LTYPE,
-c_start.available_quantity,
-c_start.available_quantity / l_available_quantity * l_pl_mtl_net,
-c_start.available_quantity / l_available_quantity * l_pl_mto_net,
-c_start.available_quantity / l_available_quantity * l_pl_res_net,
-c_start.available_quantity / l_available_quantity * l_pl_ovh_net,
-c_start.available_quantity / l_available_quantity * l_pl_osp_net,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_MTA;
/* Insert into WTA */
l_stmt_num := 110;
CSTPSMUT.INSERT_WIP_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
p_transaction_id,
l_transaction_type,
l_wip_transaction_id,
l_organization_id,
c_start.wip_entity_id,
MERGE_START_ACT_LTYPE,
-c_start.available_quantity,
0, -- This Level Material Cost
0, -- This Level Material Ovhd Cost
-c_start.available_quantity / l_available_quantity * l_tl_res_net,
-c_start.available_quantity / l_available_quantity * l_tl_ovh_net,
-c_start.available_quantity / l_available_quantity * l_tl_osp_net,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_WTA;
/* Update WPB */
l_stmt_num := 120;
RAISE FAILED_INSERTING_RESULT_LOT;
/* Update the Amount to be relieved */
l_total_tl_res := l_total_tl_res + (ROUND(c_start.available_quantity / l_available_quantity * l_tl_res_net / l_min_acct_unit) * l_min_acct_unit);
/* Update MTA and WTA for representative start lot */
/* Relieve everything that has been charged */
CSTPSMUT.INSERT_MAT_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
l_transaction_type,
p_mmt_transaction_id,
l_organization_id,
l_rep_wip_entity_id,
MERGE_START_ACT_LTYPE,
-l_available_quantity,
-(l_pl_rep_mtl_cost_in - l_pl_rep_mtl_cost_out),
-(l_pl_rep_mto_cost_in - l_pl_rep_mto_cost_out),
-(l_pl_rep_res_cost_in - l_pl_rep_res_cost_out),
-(l_pl_rep_ovh_cost_in - l_pl_rep_ovh_cost_out),
-(l_pl_rep_osp_cost_in - l_pl_rep_osp_cost_out),
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_MTA;
/* Insert into WTA */
l_stmt_num := 160;
CSTPSMUT.INSERT_WIP_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
p_transaction_id,
l_transaction_type,
l_wip_transaction_id,
l_organization_id,
l_rep_wip_entity_id,
MERGE_START_ACT_LTYPE,
-l_available_quantity,
0, -- This Level Material Cost
0, -- This Level Material Ovhd Cost
-(l_tl_rep_res_cost_in - l_tl_rep_res_cost_out),
-(l_tl_rep_ovh_cost_in - l_tl_rep_ovh_cost_out),
-(l_tl_rep_osp_cost_in - l_tl_rep_osp_cost_out),
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_WTA;
/* Update WPB */
l_stmt_num := 170;
RAISE FAILED_INSERTING_RESULT_LOT;
/* Insert into MTA for Result Lot */
CSTPSMUT.INSERT_MAT_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
l_transaction_type,
p_mmt_transaction_id,
l_organization_id,
l_result_wip_entity_id,
MERGE_RESULT_ACT_LTYPE,
l_total_qty,
l_total_pl_mtl,
l_total_pl_mto,
l_total_pl_res,
l_total_pl_ovh,
l_total_pl_osp,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_MTA;
/* Insert TL Accounting into WTA */
CSTPSMUT.INSERT_WIP_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
p_transaction_id,
l_transaction_type,
l_wip_transaction_id,
l_organization_id,
l_result_wip_entity_id,
MERGE_RESULT_ACT_LTYPE,
l_total_qty,
0, -- This Level Material Cost
0, -- This Level Material Ovhd Cost
l_total_tl_res,
l_total_tl_ovh,
l_total_tl_osp,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_WTA;
/* Insert Transaction into WT */
l_stmt_num := 220;
CSTPSMUT.INSERT_WIP_TXN(
l_transaction_date,
p_transaction_id,
l_wip_transaction_id,
l_acct_period_id,
l_result_wip_entity_id,
l_operation_seq_num,
12, -- WIP Transaction type
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg,
p_mmt_transaction_id); -- Added for Bug#4307365
RAISE FAILED_INSERTING_WT;
/* Update WPB of Result Lot */
l_stmt_num := 200;
RAISE FAILED_INSERTING_START_LOT;
/* Insert into WOO for each of the new jobs created due to the split */
FOR new_job in c_new_jobs LOOP
l_ins_woo := INSERT_WOO (
new_job.wip_entity_id,
l_organization_id,
l_operation_seq_num,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id );
RAISE INSERT_WOO_ERROR;
SELECT count(*)
INTO l_wta_exists
FROM WIP_TRANSACTION_ACCOUNTS
WHERE transaction_id = l_wip_transaction_id
and rownum=1;
WHEN FAILED_INSERTING_START_LOT THEN
x_err_num := -1;
x_err_code := 'Error Inserting WPB Information for Starting Lot';
x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WT THEN
x_err_num := -1;
x_err_code := 'Error inserting into Wip Transactions';
x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into Wip Transaction Accounts';
x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_MTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into MTL Transaction Accounts';
x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_RESULT_LOT THEN
x_err_num := -1;
x_err_code := 'Error inserting into WPB for Resulting Lot';
x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN INSERT_WOO_ERROR THEN
x_err_num := -1;
x_err_code := 'Error inserting into WOO';
x_err_msg := 'Error inserting into WOO: CSTPSMUT.COST_MERGE_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
PROCEDURE COST_UPDATE_QTY_TXN
(p_api_version IN NUMBER,
p_transaction_id IN NUMBER,
p_mmt_transaction_id IN NUMBER,
p_transaction_date IN DATE,
p_prog_application_id IN NUMBER,
p_program_id IN NUMBER,
p_request_id IN NUMBER,
p_login_id IN NUMBER,
p_user_id IN NUMBER,
x_err_num IN OUT NOCOPY NUMBER,
x_err_code IN OUT NOCOPY VARCHAR2,
x_err_msg IN OUT NOCOPY VARCHAR2) IS
/* Parameters */
l_transaction_type NUMBER;
l_api_name CONSTANT VARCHAR2(240) := 'COST_UPDATE_QTY_TXN';
FAILED_INSERTING_START_LOT EXCEPTION;
FAILED_INSERTING_WT EXCEPTION;
FAILED_INSERTING_WTA EXCEPTION;
FAILED_INSERTING_MTA EXCEPTION;
FAILED_INSERTING_RESULT_LOT EXCEPTION;
FAILED_INSERTING_BONUS_WTA EXCEPTION;
FAILED_INSERTING_BONUS_MTA EXCEPTION;
SELECT organization_id,
transaction_type_id,
transaction_date
INTO l_organization_id,
l_transaction_type,
l_transaction_date
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
NVL(FC.extended_precision,NVL(FC.precision,0))
INTO l_min_acct_unit,
l_ext_prec
FROM fnd_currencies FC,
CST_ORGANIZATION_DEFINITIONS O
WHERE O.organization_id = l_organization_id
AND O.currency_code = FC.currency_code;
SELECT acct_period_id
INTO l_acct_period_id
FROM org_acct_periods
WHERE organization_id = l_organization_id
AND l_le_transaction_date
between period_start_date and schedule_close_date;
SELECT wip_entity_id,
operation_seq_num,
intraoperation_step,
available_quantity,
job_start_quantity
INTO l_wip_entity_id,
l_operation_seq_num,
l_intraoperation_step,
l_available_quantity,
l_job_start_quantity
FROM WSM_SM_STARTING_JOBS
WHERE transaction_id = p_transaction_id;
SELECT start_quantity
INTO l_start_quantity
FROM WSM_SM_RESULTING_JOBS
WHERE transaction_id = p_transaction_id;
p_txn_type => WSMPCNST.UPDATE_QUANTITY,
p_org_id => l_organization_id,
x_err_num => x_err_num,
x_err_code => x_err_code,
x_err_msg => x_err_msg,
x_pl_mtl_cost => l_pl_mtl_cost_in,
x_pl_mto_cost => l_pl_mto_cost_in,
x_pl_res_cost => l_pl_res_cost_in,
x_pl_ovh_cost => l_pl_ovh_cost_in,
x_pl_osp_cost => l_pl_osp_cost_in,
x_tl_res_cost => l_tl_res_cost_in,
x_tl_ovh_cost => l_tl_ovh_cost_in,
x_tl_osp_cost => l_tl_osp_cost_in );
p_txn_type => WSMPCNST.UPDATE_QUANTITY,
p_org_id => l_organization_id,
x_err_num => x_err_num,
x_err_code => x_err_code,
x_err_msg => x_err_msg,
x_pl_mtl_cost => l_pl_mtl_cost_out,
x_pl_mto_cost => l_pl_mto_cost_out,
x_pl_res_cost => l_pl_res_cost_out,
x_pl_ovh_cost => l_pl_ovh_cost_out,
x_pl_osp_cost => l_pl_osp_cost_out,
x_tl_res_cost => l_tl_res_cost_out,
x_tl_ovh_cost => l_tl_ovh_cost_out,
x_tl_osp_cost => l_tl_osp_cost_out );
CSTPSMUT.INSERT_MAT_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
l_transaction_type,
p_mmt_transaction_id,
l_organization_id,
l_wip_entity_id,
UPD_QTY_RESULT_ACT_LTYPE, -- Accounting Line Type
(l_start_quantity - l_available_quantity),
l_pl_mtl_net * l_factor,
l_pl_mto_net * l_factor,
l_pl_res_net * l_factor,
l_pl_ovh_net * l_factor,
l_pl_osp_net * l_factor,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_MTA;
/* Insert into MTA */
l_stmt_num := 100;
CSTPSMUT.INSERT_WIP_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
p_transaction_id,
l_transaction_type,
l_wip_transaction_id,
l_organization_id,
l_wip_entity_id,
UPD_QTY_RESULT_ACT_LTYPE, -- Accounting Line Type
(l_start_quantity - l_available_quantity),
0, -- This Level Material Cost
0, -- This Level Material Ovhd Cost
l_factor * l_tl_res_net,
l_factor * l_tl_ovh_net,
l_factor * l_tl_osp_net,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_WTA;
/* Insert Transaction into WT */
l_stmt_num := 110;
CSTPSMUT.INSERT_WIP_TXN(
l_transaction_date,
p_transaction_id,
l_wip_transaction_id,
l_acct_period_id,
l_wip_entity_id,
l_operation_seq_num,
14, -- WIP Transaction type
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg,
p_mmt_transaction_id); -- Added for Bug#4307365
RAISE FAILED_INSERTING_WT;
/* Insert credit into Bonus Account */
l_stmt_num := 120;
/* Insert credit into Bonus Account */
l_stmt_num := 130;
/* Update WPB of Representative Lot */
l_stmt_num := 150;
RAISE FAILED_INSERTING_START_LOT;
SELECT count(*)
INTO l_wta_exists
FROM WIP_TRANSACTION_ACCOUNTS
WHERE transaction_id = l_wip_transaction_id
and rownum=1;
x_err_msg := 'Inconsistent API Version: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
x_err_msg := 'Error getting Job Charges/Scrap: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_START_LOT THEN
x_err_num := -1;
x_err_code := 'Error Inserting WPB Information for Starting Lot';
x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
x_err_msg := 'Error Balancing Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WT THEN
x_err_num := -1;
x_err_code := 'Error inserting into Wip Transactions';
x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into Wip Transaction Accounts';
x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_MTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into MTL Transaction Accounts';
x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_BONUS_MTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into MTL Bonus Accounts';
x_err_msg := 'Error inserting into MTL Bonus Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_BONUS_WTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into WIP Bonus Accounts';
x_err_msg := 'Error inserting into WIP Bonus Accounts: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_RESULT_LOT THEN
x_err_num := -1;
x_err_code := 'Error inserting into WPB for Resulting Lot';
x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_UPDATE_QTY_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
END COST_UPDATE_QTY_TXN;
FAILED_INSERTING_START_LOT EXCEPTION;
FAILED_INSERTING_WT EXCEPTION;
FAILED_INSERTING_WTA EXCEPTION;
FAILED_INSERTING_MTA EXCEPTION;
FAILED_INSERTING_RESULT_LOT EXCEPTION;
FAILED_INSERTING_BONUS_WTA EXCEPTION;
FAILED_INSERTING_BONUS_MTA EXCEPTION;
INSERT_WOO_ERROR EXCEPTION;
SELECT organization_id,
transaction_type_id,
transaction_date
INTO l_organization_id,
l_transaction_type,
l_transaction_date
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0))),
NVL(FC.extended_precision,NVL(FC.precision,0))
INTO l_min_acct_unit,
l_ext_prec
FROM fnd_currencies FC,
CST_ORGANIZATION_DEFINITIONS O
WHERE O.organization_id = l_organization_id
AND O.currency_code = FC.currency_code;
SELECT acct_period_id
INTO l_acct_period_id
FROM org_acct_periods
WHERE organization_id = l_organization_id
AND l_le_transaction_date
between period_start_date and schedule_close_date;
SELECT wip_entity_id,
job_operation_seq_num,
nvl(starting_intraoperation_step, WIP_CONSTANTS.QUEUE),
start_quantity
INTO l_wip_entity_id,
l_operation_seq_num,
l_intraoperation_step,
l_start_quantity
FROM WSM_SM_RESULTING_JOBS
WHERE transaction_id = p_transaction_id;
SELECT wo.operation_seq_num
INTO l_operation_seq_num
FROM WIP_OPERATIONS WO,
WSM_SM_RESULTING_JOBS WSRJ,
BOM_OPERATION_SEQUENCES BOS
WHERE WSRJ.transaction_id = p_transaction_id
AND nvl(wsrj.starting_intraoperation_step, 1) = 1
AND wsrj.common_routing_sequence_id = bos.routing_sequence_id
AND wsrj.starting_operation_seq_num = bos.operation_seq_num
AND bos.operation_sequence_id = wo.operation_sequence_id
AND bos.EFFECTIVITY_DATE <= p_transaction_date
AND NVL( bos.DISABLE_DATE, p_transaction_date + 1) > p_transaction_date
AND wo.wip_entity_id = wsrj.wip_entity_id
AND wo.organization_id = l_organization_id;
SELECT min(operation_seq_num)
INTO l_min_op_seq_num
FROM wip_operations
WHERE wip_entity_id = l_wip_entity_id
AND organization_id = l_organization_id;
CSTPSMUT.INSERT_MAT_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
l_transaction_type,
p_mmt_transaction_id,
l_organization_id,
l_wip_entity_id,
BONUS_RESULT_ACT_LTYPE, -- Accounting Line Type for Bonus
l_start_quantity,
l_pl_mtl_cost_in * l_start_quantity,
l_pl_mto_cost_in * l_start_quantity,
l_pl_res_cost_in * l_start_quantity,
l_pl_ovh_cost_in * l_start_quantity,
l_pl_osp_cost_in * l_start_quantity,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_MTA;
/* Insert into MTA */
l_stmt_num := 90;
CSTPSMUT.INSERT_WIP_TXN_ACCT(
l_transaction_date,
l_min_acct_unit,
l_ext_prec,
p_transaction_id,
l_transaction_type,
l_wip_transaction_id,
l_organization_id,
l_wip_entity_id,
BONUS_RESULT_ACT_LTYPE, -- Accounting Line Type for Bonus
l_start_quantity,
0, -- This Level Material Cost
0, -- This Level Material Ovhd Cost
(l_start_quantity * l_tl_res_cost_in),
(l_start_quantity * l_tl_ovh_cost_in),
(l_start_quantity * l_tl_osp_cost_in),
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg);
RAISE FAILED_INSERTING_WTA;
/* Insert Transaction into WT */
l_stmt_num := 100;
CSTPSMUT.INSERT_WIP_TXN(
l_transaction_date,
p_transaction_id,
l_wip_transaction_id,
l_acct_period_id,
l_wip_entity_id,
l_operation_seq_num,
13, -- WIP Transaction type
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id,
l_debug_flag,
x_err_num,
x_err_code,
x_err_msg,
p_mmt_transaction_id); -- Added for Bug#4307365
RAISE FAILED_INSERTING_WT;
/* Insert Credit Information */
l_stmt_num := 110;
RAISE FAILED_INSERTING_BONUS_MTA;
RAISE FAILED_INSERTING_BONUS_WTA;
/* Update WPB of Representative Lot */
l_stmt_num := 140;
RAISE FAILED_INSERTING_START_LOT;
ins_woo := INSERT_WOO (
l_wip_entity_id,
l_organization_id,
l_operation_seq_num,
p_user_id,
p_login_id,
p_request_id,
p_prog_application_id,
p_program_id );
RAISE INSERT_WOO_ERROR;
SELECT count(*)
INTO l_wta_exists
FROM WIP_TRANSACTION_ACCOUNTS
WHERE transaction_id = l_wip_transaction_id
and rownum=1;
WHEN FAILED_INSERTING_START_LOT THEN
x_err_num := -1;
x_err_code := 'Error Inserting WPB Information for Starting Lot';
x_err_msg := 'Error Inserting WPB Information for Starting Lot: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WT THEN
x_err_num := -1;
x_err_code := 'Error inserting into Wip Transactions';
x_err_msg := 'Error inserting into Wip Transactions: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_WTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into Wip Transaction Accounts';
x_err_msg := 'Error inserting into Wip Transaction Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_MTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into MTL Transaction Accounts';
x_err_msg := 'Error inserting into MTL Transaction Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_BONUS_MTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into MTL Bonus Accounts';
x_err_msg := 'Error inserting into MTL Bonus Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_BONUS_WTA THEN
x_err_num := -1;
x_err_code := 'Error inserting into WIP Bonus Accounts';
x_err_msg := 'Error inserting into WIP Bonus Accounts: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN FAILED_INSERTING_RESULT_LOT THEN
x_err_num := -1;
x_err_code := 'Error inserting into WPB for Resulting Lot';
x_err_msg := 'Error inserting into WPB for Resulting Lot: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
WHEN INSERT_WOO_ERROR THEN
x_err_num := -1;
x_err_code := 'Error inserting into WOO';
x_err_msg := 'Error inserting into WOO: CSTPSMUT.COST_BONUS_TXN('||to_char(l_stmt_num)||'):' || x_err_msg || substr(SQLERRM, 1, 200);
SELECT wip_transactions_s.nextval
INTO x_wip_txn_id
FROM dual;
SELECT operation_seq_num
INTO l_first_op_seq_num
FROM wip_operations
WHERE wip_entity_id = p_entity_id
AND previous_operation_seq_num is null;
SELECT NVL(include_component_yield, 1)
INTO l_include_comp_yield
FROM wip_parameters
WHERE organization_id = p_org_id;
SELECT transaction_date
INTO l_transaction_date
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
SELECT MAX( OPERATION_SEQ_NUM )
INTO l_operation_seq_num
FROM wip_operations
WHERE wip_entity_id = p_entity_id
AND operation_seq_num < p_operation_seq_num
AND organization_id = p_org_id;
SELECT
nvl(SUM(NVL(DECODE(p_run_mode, 1,
DECODE(p_txn_type, 4, /* LBM project Changes */
(DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
WRO.quantity_per_assembly)/
DECODE(l_include_comp_yield,
1, nvl(WRO.component_yield_factor,1),
1)),
nvl(WRO.COSTED_QUANTITY_ISSUED, 0)) ,
DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
* CIC.MATERIAL_COST,0)),0),
nvl(SUM(NVL(DECODE(p_run_mode, 1,
DECODE(p_txn_type, 4, /* LBM project Changes */
(DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
WRO.quantity_per_assembly)/
DECODE(l_include_comp_yield,
1, nvl(WRO.component_yield_factor,1),
1)),
nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
* CIC.MATERIAL_OVERHEAD_COST,0)),0),
nvl(SUM(NVL(DECODE(p_run_mode, 1,
DECODE(p_txn_type, 4, /* LBM project Changes */
(DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
WRO.quantity_per_assembly)/
DECODE(l_include_comp_yield,
1, nvl(WRO.component_yield_factor,1),
1)),
nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
* CIC.RESOURCE_COST,0)),0),
nvl(SUM(NVL(DECODE(p_run_mode, 1,
DECODE(p_txn_type, 4, /* LBM project Changes */
(DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
WRO.quantity_per_assembly)/
DECODE(l_include_comp_yield,
1, nvl(WRO.component_yield_factor,1),
1)),
nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
* CIC.OUTSIDE_PROCESSING_COST,0)),0),
nvl(SUM(NVL(DECODE(p_run_mode, 1,
DECODE(p_txn_type, 4, /* LBM project Changes */
(DECODE(NVL(WRO.basis_type,1),2, WRO.quantity_per_assembly/p_lot_size,
WRO.quantity_per_assembly)/
DECODE(l_include_comp_yield,
1, nvl(WRO.component_yield_factor,1),
1)),
nvl(WRO.COSTED_QUANTITY_ISSUED, 0)),
DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1,
NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
* CIC.OVERHEAD_COST,0)),0)
INTO
x_pl_mtl_cost,
x_pl_mto_cost,
x_pl_res_cost,
x_pl_osp_cost,
x_pl_ovh_cost
FROM
wip_requirement_operations WRO,
cst_item_costs CIC
WHERE
CIC.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = p_org_id
AND CIC.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND CIC.COST_TYPE_ID = 1
AND WRO.WIP_ENTITY_ID = p_entity_id
AND WRO.OPERATION_SEQ_NUM <= l_operation_seq_num
/* Changes for Lot Based Materials project */
AND (NVL(WRO.BASIS_TYPE,1) <> 2 OR (p_txn_type IN (1,4)))
AND ((p_txn_type = 4) OR
(abs(nvl(WRO.COSTED_QUANTITY_ISSUED, 0)) >= abs(nvl(WRO.COSTED_QUANTITY_RELIEVED, 0)))) /* Added abs() for bug 6774122 */
/* LBM Changes end*/
AND ( WRO.WIP_SUPPLY_TYPE not in (2, 4, 5, 6) or p_txn_type <> 4 )
AND not exists (select 'obsolete operation'
from wip_operations WO
where WO.wip_entity_id = WRO.wip_entity_id
and WO.organization_id = WRO.organization_id
and WO.operation_seq_num = WRO.operation_seq_num
and WO.disable_date <= l_transaction_date );
The following select statement is to calculate this level resource charge
and this level outside processing charge.
*/
SELECT
NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
3, DECODE(p_run_mode,
1, DECODE(p_txn_type, 4,
NVL((DECODE(WOR.basis_type,
1,WOR.usage_rate_or_amount,
2,WOR.usage_rate_or_amount/p_lot_size,
WOR.usage_rate_or_amount) *
DECODE(BR.functional_currency_flag,
1,1, nvl(CRC.resource_rate,0))),0),
NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
DECODE(sign(nvl(WOR.relieved_res_value, 0)), 1, nvl(WOR.relieved_res_value, 0), 0)),
0)),0),
NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
4, DECODE(p_run_mode,
1, DECODE(p_txn_type, 4,
NVL((DECODE(WOR.basis_type,
1,WOR.usage_rate_or_amount,
2,WOR.usage_rate_or_amount/p_lot_size,
WOR.usage_rate_or_amount) *
DECODE(BR.functional_currency_flag,
1,1,nvl(CRC.resource_rate,0))),0),
NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
DECODE(sign(nvl(WOR.relieved_res_value, 0)), 1, nvl(WOR.relieved_res_value, 0), 0)),
0)),0)
INTO
x_tl_res_cost,
x_tl_osp_cost
FROM cst_resource_costs CRC,
wip_operation_resources WOR,
bom_resources BR
WHERE
CRC.COST_TYPE_ID(+) = 1
AND CRC.RESOURCE_ID(+) = WOR.RESOURCE_ID
AND WOR.OPERATION_SEQ_NUM <= l_operation_seq_num
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND WOR.WIP_ENTITY_ID = p_entity_id
AND WOR.ORGANIZATION_ID = p_org_id
AND (WOR.basis_type <> 2 or p_txn_type in (1, 4))
AND ((p_txn_type = 4) OR
nvl(WOR.applied_resource_value, 0) >= nvl(WOR.relieved_res_value, 0))
AND not exists (select 'obsolete operation'
from wip_operations WO
where WO.wip_entity_id = WOR.wip_entity_id
and WO.organization_id = WOR.organization_id
and WO.operation_seq_num = WOR.operation_seq_num
and WO.disable_date <= l_transaction_date );
SELECT NVL(SUM(NVL(CDO.rate_or_amount *
DECODE(WOR.basis_type,
1,WOR.usage_rate_or_amount,
2,WOR.usage_rate_or_amount/p_lot_size,
WOR.usage_rate_or_amount) *
DECODE(CDO.basis_type, 3, 1,
DECODE(BR.functional_currency_flag,
1,1,nvl(CRC.resource_rate,0))), 0)), 0)
INTO
x_tl_ovh_cost
FROM
wip_operations WO,
wip_operation_resources WOR,
cst_resource_overheads CRO,
cst_department_overheads CDO,
bom_resources BR,
cst_resource_costs CRC
WHERE
WO.wip_entity_id = p_entity_id
AND WOR.resource_id = BR.resource_id
AND CRC.resource_id(+) = BR.resource_id
AND WO.operation_seq_num = WOR.operation_seq_num
AND NVL(WO.DISABLE_DATE, l_transaction_date) <= l_transaction_date
AND WOR.organization_id = p_org_id
AND WOR.wip_entity_id = p_entity_id
AND WOR.operation_seq_num <= l_operation_seq_num
AND WOR.organization_id = p_org_id
AND CDO.department_id = WO.department_id
AND CDO.basis_type in (3, 4)
AND CDO.overhead_id = CRO.overhead_id
AND CRO.resource_id = WOR.resource_id
AND CRC.cost_type_id(+) = 1
AND CRO.cost_type_id = 1
AND CDO.cost_type_id = 1;
SELECT NVL(SUM(NVL(DECODE(CDO.basis_type,
1,CDO.rate_or_amount,
2,CDO.rate_or_amount/p_lot_size),0)), 0)
INTO l_tl_ovh_dept_cost
FROM wip_operations WO,
cst_department_overheads CDO
WHERE
WO.wip_entity_id = p_entity_id
AND WO.operation_seq_num <= l_operation_seq_num
AND WO.organization_id = p_org_id
AND nvl(WO.DISABLE_DATE, l_transaction_date) <= l_transaction_date
AND CDO.department_id = WO.department_id
AND CDO.organization_id = WO.organization_id
AND CDO.basis_type in (1,2)
AND CDO.cost_type_id = 1;
SELECT nvl(DECODE(p_run_mode,
1, SUM(NVL(WOO.applied_ovhd_value,0)),
2, SUM(NVL(WOO.relieved_ovhd_value,0))),0)
INTO x_tl_ovh_cost
FROM wip_operation_overheads WOO,
cst_resource_overheads CRO,
wip_operation_resources WOR
WHERE
WOO.operation_seq_num <= l_operation_seq_num
AND WOO.wip_entity_id = p_entity_id
AND WOO.organization_id = p_org_id
AND CRO.overhead_id = WOO.overhead_id
AND CRO.resource_id = WOR.resource_id
AND WOR.operation_seq_num = WOO.operation_seq_num
AND WOR.resource_seq_num = WOO.resource_seq_num
AND WOR.wip_entity_id = WOO.wip_entity_id
AND WOR.organization_id = p_org_id
AND CRO.cost_type_id = 1
AND WOO.basis_type in (3, 4)
AND nvl(WOR.applied_resource_value, 0) >= nvl(WOR.relieved_res_value, 0)
AND not exists
( SELECT 1
FROM wip_operations WO
WHERE WO.DISABLE_DATE <= l_transaction_date
AND WO.operation_seq_num = WOO.operation_seq_num
AND WO.wip_entity_id = p_entity_id )
AND ( WOR.basis_type <> 2 or p_txn_type = 1 );
SELECT nvl(DECODE(p_run_mode,
1, SUM(NVL(WOO.applied_ovhd_value,0)),
2, SUM(NVL(WOO.relieved_ovhd_value,0))),0)
INTO l_tl_ovh_dept_cost
FROM wip_operation_overheads WOO
WHERE
WOO.operation_seq_num <= l_operation_seq_num
AND WOO.wip_entity_id = p_entity_id
AND WOO.organization_id = p_org_id
AND WOO.basis_type in (1, 2)
AND not exists
( SELECT 1
FROM wip_operations WO
WHERE WO.DISABLE_DATE <= l_transaction_date
AND WO.operation_seq_num = WOO.operation_seq_num
AND WO.wip_entity_id = p_entity_id )
AND ( WOO.basis_type <> 2 or p_txn_type = 1 );
PROCEDURE UPDATE_JOB_QUANTITY ( p_api_version IN NUMBER,
p_txn_id IN NUMBER,
x_err_num IN OUT NOCOPY NUMBER,
x_err_code IN OUT NOCOPY VARCHAR2,
x_err_msg IN OUT NOCOPY VARCHAR2 ) IS
l_api_name CONSTANT VARCHAR2(240) := 'UPDATE_JOB_QUANTITY';
CURSOR C_RJ IS SELECT *
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_txn_id;
CURSOR C_SJ IS SELECT *
FROM wsm_sm_starting_jobs
WHERE transaction_id = p_txn_id;
FND_FILE.put_line(fnd_file.log,'CSTPSMUT.UPDATE_JOB_QUANTITY <<<');
SELECT organization_id,
transaction_type_id,
transaction_date
INTO l_org_id,
l_txn_type,
l_transaction_date
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE transaction_id = p_txn_id;
SELECT NVL(include_component_yield, 1)
INTO l_include_comp_yield
FROM wip_parameters
WHERE organization_id = l_org_id;
SELECT NVL(FC.minimum_accountable_unit, POWER(10,NVL(-precision,0)))
INTO l_min_acct_unit
FROM fnd_currencies fc,
cst_organization_definitions o
WHERE o.organization_id = l_org_id
AND o.currency_code = fc.currency_code;
SELECT wo.operation_seq_num
INTO l_operation_seq_num
FROM WIP_OPERATIONS WO,
WSM_SM_RESULTING_JOBS WSRJ,
BOM_OPERATION_SEQUENCES BOS
WHERE WSRJ.transaction_id = p_txn_id
AND nvl(wsrj.starting_intraoperation_step, 1) = 1
AND wsrj.common_routing_sequence_id = bos.routing_sequence_id
AND wsrj.starting_operation_seq_num = bos.operation_seq_num
AND bos.operation_sequence_id = wo.operation_sequence_id
AND bos.EFFECTIVITY_DATE <= l_transaction_date
AND NVL( bos.DISABLE_DATE, l_transaction_date + 1) > l_transaction_date
AND wo.wip_entity_id = wsrj.wip_entity_id
AND wo.organization_id = l_org_id;
/* Update applied resource units and applied resource value. */
l_stmt_num := 40;
UPDATE wip_operation_resources wor
SET wor.applied_resource_units = ROUND((C_result.start_quantity *
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/C_result.start_quantity,
wor.usage_rate_or_amount)), 6),
wor.applied_resource_value = (SELECT
ROUND((nvl(max(C_result.start_quantity *
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/C_result.start_quantity,
wor.usage_rate_or_amount)*
decode (br.functional_currency_flag,
1, 1,
nvl(crc.resource_rate,0)))
,0))/l_min_acct_unit) * l_min_acct_unit
FROM bom_resources br,
cst_resource_costs crc
WHERE
br.resource_id = wor.resource_id
AND br.organization_id = l_org_id
AND crc.cost_type_id = 1
AND crc.organization_id = l_org_id
AND crc.resource_id = wor.resource_id )
WHERE wor.wip_entity_id = C_result.wip_entity_id
AND wor.operation_seq_num < l_operation_seq_num;
/* Update quantity issued. */
l_stmt_num := 50;
UPDATE wip_requirement_operations wro
SET costed_quantity_issued = ROUND((C_result.start_quantity *
DECODE(nvl(wro.basis_type,1),
2, wro.quantity_per_assembly/C_result.start_quantity,
wro.quantity_per_assembly) /
DECODE(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1)), 6)
WHERE wip_entity_id = C_result.wip_entity_id
AND operation_seq_num < l_operation_seq_num
AND wip_supply_type not in (2, 4, 5, 6);
/* Update WOO */
l_stmt_num := 55;
UPDATE wip_operation_overheads woo
SET ( applied_ovhd_units,
applied_ovhd_value ) =
( SELECT decode(woo.basis_type,
3, NVL(WOR.applied_resource_units,0),
4, NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
decode(woo.basis_type,
3, NVL(CDO.rate_or_amount* WOR.applied_resource_units,0),
4, NVL(CDO.rate_or_amount*
NVL(WOR.APPLIED_RESOURCE_VALUE,0), 0))
FROM wip_operation_resources WOR,
cst_resource_overheads CRO,
cst_department_overheads CDO,
wip_operations WO
WHERE
WOR.wip_entity_id = C_result.wip_entity_id
AND WOR.organization_id = l_org_id
AND WOR.operation_seq_num = WOO.operation_seq_num
AND WOR.resource_seq_num = WOO.resource_seq_num
AND WOR.resource_id = CRO.resource_id
AND CRO.overhead_id = CDO.overhead_id
AND CDO.overhead_id = WOO.overhead_id
AND CDO.department_id = WO.department_id
AND WO.wip_entity_id = C_result.wip_entity_id
AND WO.organization_id = l_org_id
AND WO.operation_seq_num = WOO.operation_seq_num
AND CRO.cost_type_id = 1
AND CDO.cost_type_id = 1
AND CDO.basis_type = WOO.basis_type
)
WHERE
wip_entity_id = C_result.wip_entity_id
AND organization_id = l_org_id
AND operation_seq_num < l_operation_seq_num
AND basis_type in (3,4);
UPDATE wip_operation_overheads woo
SET ( applied_ovhd_units,
applied_ovhd_value ) =
( SELECT decode(woo.basis_type, 1, C_result.start_quantity,
2, 1),
decode(woo.basis_type, 1, CDO.rate_or_amount * C_result.start_quantity,
2, CDO.rate_or_amount)
FROM wip_operations WO,
cst_department_overheads CDO
WHERE
woo.operation_seq_num = wo.operation_seq_num
AND WO.wip_entity_id = C_result.wip_entity_id
AND WO.organization_id = l_org_id
AND CDO.department_id = WO.department_id
AND CDO.overhead_id = WOO.overhead_id
AND CDO.cost_type_id = 1
AND CDO.basis_type = WOO.basis_type )
WHERE wip_entity_id = C_result.wip_entity_id
AND organization_id = l_org_id
AND operation_seq_num < l_operation_seq_num
AND basis_type in (1,2);
/* Update Quantity/Split/Merge */
/* Obtain Information from WSM_SM_STARTING_JOBS
for representative Lot. */
l_stmt_num := 60;
SELECT operation_seq_num,
intraoperation_step,
wip_entity_id,
available_quantity,
job_start_quantity
INTO l_operation_seq_num,
l_intraoperation_step,
l_rep_wip_entity_id,
l_available_quantity,
l_job_start_quantity
FROM WSM_SM_STARTING_JOBS
WHERE transaction_id = p_txn_id
AND representative_flag = 'Y';
UPDATE wip_operation_resources wor
SET ( wor.applied_resource_units,
wor.applied_resource_value ) =
( SELECT (nvl(wor1.applied_resource_units, 0) - DECODE(sign(nvl(wor1.relieved_res_units, 0)), 1, nvl(wor1.relieved_res_units, 0), 0)) *
DECODE(sign(nvl(wor1.applied_resource_units, 0) - DECODE(sign(nvl(wor1.relieved_res_units, 0)), 1, nvl(wor1.relieved_res_units, 0), 0)),
1, 1, 0),
(nvl(wor1.applied_resource_value,0) - DECODE(sign(nvl(wor1.relieved_res_value, 0)), 1, nvl(wor1.relieved_res_value, 0), 0)) *
DECODE(sign(nvl(wor1.applied_resource_value,0) - DECODE(sign(nvl(wor1.relieved_res_value, 0)), 1, nvl(wor1.relieved_res_value, 0), 0)),
1, 1, 0)
FROM wip_operation_resources wor1
WHERE wor1.operation_seq_num = wor.operation_seq_num
AND wor1.wip_entity_id = l_rep_wip_entity_id
AND wor1.organization_id = wor.organization_id
AND wor1.resource_seq_num = wor.resource_seq_num )
WHERE wor.wip_entity_id = C_rec.wip_entity_id
AND wor.organization_id = l_org_id
AND wor.wip_entity_id <> l_rep_wip_entity_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and wo.disable_date <= l_transaction_date )
/* Make sure the operation exists in the Parent */
AND exists (select 'operation exists'
from wip_operation_resources wor2
WHERE wor2.operation_seq_num = wor.operation_seq_num
AND wor2.wip_entity_id = l_rep_wip_entity_id
AND wor2.organization_id = wor.organization_id
AND wor2.resource_seq_num = wor.resource_seq_num);
UPDATE wip_requirement_operations wro
SET wro.costed_quantity_issued =
( SELECT (NVL(wro1.costed_quantity_issued,0) - DECODE(sign(NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0), 0) )*
/* LBM changes (This is cond like basis_type<>2 or l_txn_type=1) Bugs 5202282*/
decode(l_txn_type, 1, l_scale_factor, decode(nvl(wro.basis_type,1), 2, 1, l_scale_factor)) *
DECODE(sign(nvl(wro1.costed_quantity_issued,0) - DECODE(sign(NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO1.COSTED_QUANTITY_RELIEVED, 0), 0)),
1, 1, 0)
FROM wip_requirement_operations wro1
WHERE wro1.wip_entity_id = l_rep_wip_entity_id
AND wro1.inventory_item_id = wro.inventory_item_id
AND wro1.organization_id = wro.organization_id
AND wro1.operation_seq_num = wro.operation_seq_num )
WHERE wro.wip_entity_id = C_rec.wip_entity_id
AND wro.organization_id = l_org_id
AND wro.wip_entity_id <> l_rep_wip_entity_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wro.wip_entity_id
and wo.organization_id = wro.organization_id
and wo.operation_seq_num = wro.operation_seq_num
and wo.disable_date <= l_transaction_date )
/* Make sure the operation exists in the Parent */
AND exists (select 'operation exists'
from wip_requirement_operations wro2
WHERE wro2.wip_entity_id = l_rep_wip_entity_id
AND wro2.inventory_item_id = wro.inventory_item_id
AND wro2.organization_id = wro.organization_id
AND wro2.operation_seq_num = wro.operation_seq_num );
Update WOO using the values for corresponding fields in the
representative lot
For resource unit and resource value based overheads,
set applied_ovhd_units = applied_res_units (Res Unit based ovhd)
= applied_res_value (Res Value based ovhd)
from WOR.
applied_ovhd_value = CDO.rate_or_amount * applied_ovhd_units
*/
/* For Item and Lot based Ovhd's, Initialize the Ovhd's */
UPDATE wip_operation_overheads woo
SET ( woo.applied_ovhd_units,
woo.applied_ovhd_value ) =
( SELECT (NVL(woo1.applied_ovhd_units,0) - DECODE(sign(nvl(woo1.relieved_ovhd_units, 0)), 1, nvl(woo1.relieved_ovhd_units, 0), 0) ) *
DECODE(sign(nvl(woo1.applied_ovhd_units,0) - DECODE(sign(nvl(woo1.relieved_ovhd_units, 0)), 1, nvl(woo1.relieved_ovhd_units, 0), 0)),
1, 1, 0),
(NVL(woo1.applied_ovhd_value,0) - DECODE(sign(nvl(woo1.relieved_ovhd_value, 0)), 1, nvl(woo1.relieved_ovhd_value, 0), 0) ) *
DECODE(sign(nvl(woo1.applied_ovhd_value,0) - DECODE(sign(nvl(woo1.relieved_ovhd_value, 0)), 1, nvl(woo1.relieved_ovhd_value, 0), 0)),
1, 1, 0)
FROM wip_operation_overheads woo1
WHERE woo1.wip_entity_id = l_rep_wip_entity_id
AND woo1.overhead_id = woo.overhead_id
AND woo1.organization_id = woo.organization_id
AND woo1.operation_seq_num = woo.operation_seq_num
AND woo1.resource_seq_num = woo.resource_seq_num)
WHERE woo.wip_entity_id = C_rec.wip_entity_id
AND woo.organization_id = l_org_id
AND woo.wip_entity_id <> l_rep_wip_entity_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = woo.wip_entity_id
and wo.organization_id = woo.organization_id
and wo.operation_seq_num = woo.operation_seq_num
and wo.disable_date <= l_transaction_date )
AND woo.basis_type in (1,2)
/* Make sure the operation and overhead exist in the Parent */
AND exists (select 'operation exists'
from wip_operation_overheads woo2
WHERE woo2.wip_entity_id = l_rep_wip_entity_id
AND woo2.overhead_id = woo.overhead_id
AND woo2.organization_id = woo.organization_id
AND woo2.operation_seq_num = woo.operation_seq_num
AND woo2.resource_seq_num = woo.resource_seq_num);
UPDATE wip_operation_resources wor
SET wor.applied_resource_units = wor.applied_resource_units * l_scale_factor,
wor.applied_resource_value = wor.applied_resource_value * l_scale_factor
WHERE wor.wip_entity_id = C_rec1.wip_entity_id
AND wor.organization_id = l_org_id
AND wor.wip_entity_id <> l_rep_wip_entity_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and wo.disable_date <= l_transaction_date )
AND ( basis_type <> 2 or l_txn_type = 1 );
Lot based ovhds not scaled for merge/update_qty
*/
UPDATE wip_operation_overheads woo
SET woo.applied_ovhd_units = woo.applied_ovhd_units * l_scale_factor,
woo.applied_ovhd_value = woo.applied_ovhd_value * l_scale_factor
WHERE woo.wip_entity_id = C_rec1.wip_entity_id
AND woo.organization_id = l_org_id
AND woo.wip_entity_id <> l_rep_wip_entity_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = woo.wip_entity_id
and wo.organization_id = woo.organization_id
and wo.operation_seq_num = woo.operation_seq_num
and wo.disable_date <= l_transaction_date )
AND ( basis_type <> 2 or l_txn_type = 1 )
AND basis_type in (1, 2);
/* Update WOO for Resource Unit and Value based Ovhds */
l_stmt_num := 98;
UPDATE wip_operation_overheads woo
SET ( applied_ovhd_units,
applied_ovhd_value ) =
( SELECT decode(woo.basis_type,
3, NVL(WOR.applied_resource_units,0),
4, NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
decode(woo.basis_type,
3, NVL(CDO.rate_or_amount* NVL(WOR.applied_resource_units, 0) , 0),
4, NVL(CDO.rate_or_amount*
NVL(WOR.APPLIED_RESOURCE_VALUE,0), 0))
FROM wip_operation_resources WOR,
cst_resource_overheads CRO,
cst_department_overheads CDO,
wip_operations WO
WHERE
WOR.wip_entity_id = C_rec1.wip_entity_id
AND WOR.organization_id = l_org_id
AND WOR.operation_seq_num = WOO.operation_seq_num
AND WOR.resource_seq_num = WOO.resource_seq_num
AND WOR.resource_id = CRO.resource_id
AND CRO.overhead_id = CDO.overhead_id
AND CDO.overhead_id = WOO.overhead_id
AND CDO.department_id = WO.department_id
AND WO.wip_entity_id = C_rec1.wip_entity_id
AND WO.organization_id = l_org_id
AND WO.operation_seq_num = WOO.operation_seq_num
AND CRO.cost_type_id = 1
AND CDO.cost_type_id = 1
AND CDO.basis_type = WOO.basis_type
)
WHERE
wip_entity_id = C_rec1.wip_entity_id
AND wip_entity_id <> l_rep_wip_entity_id
AND organization_id = l_org_id
AND basis_type in (3, 4);
UPDATE wip_operation_resources wor
SET wor.applied_resource_units = (NVL(wor.applied_resource_units,0) -
DECODE(sign(nvl(wor.relieved_res_units, 0)), 1, nvl(wor.relieved_res_units, 0), 0))
* l_resulting_scale_factor +
DECODE(sign(nvl(wor.relieved_res_units, 0)), 1, nvl(wor.relieved_res_units, 0), 0),
wor.applied_resource_value = (NVL(wor.applied_resource_value,0) -
DECODE(sign(nvl(wor.relieved_res_value, 0)), 1, nvl(wor.relieved_res_value, 0), 0))
* l_resulting_scale_factor +
DECODE(sign(nvl(wor.relieved_res_value, 0)), 1, nvl(wor.relieved_res_value, 0), 0)
WHERE wor.wip_entity_id = l_rep_wip_entity_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and wo.disable_date <= l_transaction_date )
AND nvl(wor.applied_resource_units, 0) >= nvl(wor.relieved_res_units, 0)
AND nvl(wor.applied_resource_value, 0) >= nvl(wor.relieved_res_value, 0)
AND (wor.basis_type <> 2 or l_txn_type = 1 );
UPDATE wip_requirement_operations wro
SET wro.costed_quantity_issued = (NVL(wro.costed_quantity_issued, 0) -
DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0))
* l_resulting_scale_factor +
DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0)
WHERE wro.wip_entity_id = l_rep_wip_entity_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wro.wip_entity_id
and wo.organization_id = wro.organization_id
and wo.operation_seq_num = wro.operation_seq_num
and wo.disable_date <= l_transaction_date )
AND nvl(wro.costed_quantity_issued, 0) >= nvl(wro.costed_quantity_relieved, 0)
AND (nvl(wro.basis_type,1) <> 2 or l_txn_type = 1 ); /* LBM Changes for Merge Bug 5202282 */
/* Update WOO in a similar manner */
UPDATE wip_operation_overheads woo
SET applied_ovhd_units =
(NVL(woo.applied_ovhd_units, 0) - DECODE(sign(nvl(relieved_ovhd_units, 0)), 1, nvl(relieved_ovhd_units, 0), 0)) * l_resulting_scale_factor
+ DECODE(sign(nvl(relieved_ovhd_units, 0)), 1, nvl(relieved_ovhd_units, 0), 0),
applied_ovhd_value =
(NVL(woo.applied_ovhd_value, 0) - DECODE(sign(nvl(relieved_ovhd_value, 0)), 1, nvl(relieved_ovhd_value, 0), 0)) * l_resulting_scale_factor
+ DECODE(sign(nvl(relieved_ovhd_value, 0)), 1, nvl(relieved_ovhd_value, 0), 0)
WHERE woo.wip_entity_id = l_rep_wip_entity_id
AND woo.organization_id = l_org_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = woo.wip_entity_id
and wo.organization_id = woo.organization_id
and wo.operation_seq_num = woo.operation_seq_num
and wo.disable_date <= l_transaction_date )
AND (NVL(woo.applied_ovhd_units, 0) - NVL(relieved_ovhd_units, 0)) >= 0
AND (NVL(woo.applied_ovhd_value, 0) - NVL(relieved_ovhd_value, 0)) >= 0
AND (woo.basis_type <> 2 or l_txn_type = 1 )
AND woo.basis_type in (1, 2);
UPDATE wip_operation_overheads woo
SET ( applied_ovhd_units,
applied_ovhd_value ) =
(SELECT decode(woo.basis_type,
3, NVL(WOR.applied_resource_units,0),
4, NVL(WOR.APPLIED_RESOURCE_VALUE,0)),
decode(woo.basis_type,
3, NVL(CDO.rate_or_amount* WOR.applied_resource_units,0),
4, NVL(CDO.rate_or_amount*
NVL(WOR.APPLIED_RESOURCE_VALUE,0), 0))
FROM wip_operation_resources WOR,
cst_resource_overheads CRO,
cst_department_overheads CDO,
wip_operations WO
WHERE
WOR.wip_entity_id = l_rep_wip_entity_id
AND WOR.organization_id = l_org_id
AND WOR.operation_seq_num = WOO.operation_seq_num
AND WOR.resource_seq_num = WOO.resource_seq_num
AND WOR.resource_id = CRO.resource_id
AND CRO.overhead_id = CDO.overhead_id
AND CDO.overhead_id = WOO.overhead_id
AND CDO.department_id = WO.department_id
AND WO.wip_entity_id = l_rep_wip_entity_id
AND WO.organization_id = l_org_id
AND WO.operation_seq_num = WOO.operation_seq_num
AND CRO.cost_type_id = 1
AND CDO.cost_type_id = 1
AND CDO.basis_type = WOO.basis_type
)
WHERE
wip_entity_id = l_rep_wip_entity_id
AND organization_id = l_org_id
AND basis_type in (3,4);
SELECT 1
INTO l_resulting_job
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_txn_id
AND wip_entity_id = S_rec.wip_entity_id);
UPDATE wip_operation_resources wor
SET applied_resource_units = round(DECODE(sign(nvl(relieved_res_units, 0)), 1, nvl(relieved_res_units, 0), 0),6),
applied_resource_value = round(DECODE(sign(nvl(relieved_res_value, 0)), 1, nvl(relieved_res_value, 0), 0),6)
WHERE wip_entity_id = S_rec.wip_entity_id
AND nvl(applied_resource_units, 0) >= nvl(relieved_res_units, 0)
AND nvl(applied_resource_value, 0) >= nvl(relieved_res_value, 0)
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and wo.disable_date <= l_transaction_date );
UPDATE wip_requirement_operations wro
SET costed_quantity_issued = round(DECODE(sign(NVL(WRO.COSTED_QUANTITY_RELIEVED, 0)), 1, NVL(WRO.COSTED_QUANTITY_RELIEVED, 0), 0),6)
WHERE wip_entity_id = S_rec.wip_entity_id
AND nvl(costed_quantity_issued, 0) >= nvl(costed_quantity_relieved, 0)
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = wro.wip_entity_id
and wo.organization_id = wro.organization_id
and wo.operation_seq_num = wro.operation_seq_num
and wo.disable_date <= l_transaction_date );
UPDATE wip_operation_overheads woo
SET applied_ovhd_units = DECODE(sign(nvl(relieved_ovhd_units, 0)), 1, nvl(relieved_ovhd_units, 0), 0),
applied_ovhd_value = DECODE(sign(nvl(relieved_ovhd_value, 0)), 1, nvl(relieved_ovhd_value, 0), 0)
WHERE woo.wip_entity_id = S_rec.wip_entity_id
AND woo.organization_id= l_org_id
AND not exists (select 'obsolete operation'
from wip_operations wo
where wo.wip_entity_id = woo.wip_entity_id
and wo.organization_id = woo.organization_id
and wo.operation_seq_num = woo.operation_seq_num
and wo.disable_date <= l_transaction_date )
AND (NVL(woo.applied_ovhd_units, 0) - NVL(relieved_ovhd_units, 0)) >= 0
AND (NVL(woo.applied_ovhd_value, 0) - NVL(relieved_ovhd_value, 0)) >= 0;
x_err_msg := 'CSTPSMUT.UPDATE_JOB_QUANTITY('||to_char(l_stmt_num)||'):'|| x_err_msg || substr(SQLERRM, 1, 200);
x_err_msg := 'CSTPSMUT.UPDATE_JOB_QUANTITY('||to_char(l_stmt_num)||'): ' || substr(SQLERRM, 1, 200);
END update_job_quantity;
SELECT sum(base_transaction_value)
INTO l_mta_total_sum
FROM mtl_transaction_accounts
WHERE transaction_id in (p_mtl_txn_id);
SELECT sum(base_transaction_value)
INTO l_mta_temp_value
FROM mtl_transaction_accounts
WHERE transaction_id in (p_mtl_txn_id)
AND NOT (cost_element_id = (SELECT MAX(mta1.cost_element_id)
FROM mtl_transaction_accounts mta1
WHERE mta1.transaction_id = p_mtl_txn_id
AND mta1.base_transaction_value < 0 )
AND base_transaction_value < 0);
UPDATE mtl_transaction_accounts mta
SET base_transaction_value = -1 * (l_mta_temp_value)
WHERE mta.transaction_id = p_mtl_txn_id
AND mta.cost_element_id = (SELECT MAX(mta1.cost_element_id)
FROM mtl_transaction_accounts mta1
WHERE mta1.transaction_id = p_mtl_txn_id
AND mta.base_transaction_value < 0)
AND mta.base_transaction_value < 0;
SELECT sum(base_transaction_value)
INTO l_mta_temp_value
FROM mtl_transaction_accounts
WHERE transaction_id in (p_mtl_txn_id)
AND NOT (cost_element_id = (SELECT MAX(mta1.cost_element_id)
FROM mtl_transaction_accounts mta1
WHERE mta1.transaction_id = p_mtl_txn_id
AND mta1.base_transaction_value > 0 )
AND base_transaction_value > 0);
UPDATE mtl_transaction_accounts mta
SET base_transaction_value = -1 * (l_mta_temp_value)
WHERE mta.transaction_id = p_mtl_txn_id
AND mta.cost_element_id = (SELECT MAX(mta1.cost_element_id)
FROM mtl_transaction_accounts mta1
WHERE mta1.transaction_id = p_mtl_txn_id
AND mta1.base_transaction_value > 0)
AND mta.base_transaction_value > 0;
ELSIF p_txn_type in( 4, 6) THEN -- update qty, bonus transaction
-- if not balanced, get the sum of all debit
l_stmt_num := 80;
SELECT sum(base_transaction_value)
INTO l_mta_temp_value
FROM mtl_transaction_accounts
WHERE transaction_id in (p_mtl_txn_id)
AND base_transaction_value > 0;
UPDATE mtl_transaction_accounts mta
SET base_transaction_value = -1 * (l_mta_temp_value)
WHERE mta.transaction_id = p_mtl_txn_id
AND mta.cost_element_id is null;
SELECT sum(base_transaction_value)
INTO l_wta_total_sum
FROM wip_transaction_accounts wta
WHERE transaction_id in (p_wip_txn_id);
SELECT sum(base_transaction_value)
INTO l_wta_temp_value
FROM wip_transaction_accounts wta
WHERE transaction_id in (p_wip_txn_id)
AND NOT ( cost_element_id = (SELECT MAX(cost_element_id)
FROM wip_transaction_accounts wta1
WHERE wta1.transaction_id = p_wip_txn_id
AND wta1.base_transaction_value < 0 )
AND wta.base_transaction_value < 0);
UPDATE wip_transaction_accounts wta
SET wta.base_transaction_value = -1*(l_wta_temp_value)
WHERE transaction_id in (p_wip_txn_id)
AND (base_transaction_value < 0
and cost_element_id=(SELECT MAX(cost_element_id)
FROM wip_transaction_accounts wta1
WHERE wta1.transaction_id = p_wip_txn_id
AND wta1.base_transaction_value < 0 ));
SELECT sum(base_transaction_value)
INTO l_wta_temp_value
FROM wip_transaction_accounts wta
WHERE transaction_id in (p_wip_txn_id)
AND NOT ( cost_element_id = (SELECT MAX(cost_element_id)
FROM wip_transaction_accounts wta1
WHERE wta1.transaction_id = p_wip_txn_id
AND wta1.base_transaction_value > 0)
AND base_transaction_value > 0);
UPDATE wip_transaction_accounts wta
SET wta.base_transaction_value = -1*(l_wta_temp_value)
WHERE transaction_id in (p_wip_txn_id)
AND (base_transaction_value > 0
and cost_element_id=(SELECT MAX(cost_element_id)
FROM wip_transaction_accounts wta1
WHERE wta1.transaction_id = p_wip_txn_id));
SELECT sum(base_transaction_value)
INTO l_wta_temp_value
FROM wip_transaction_accounts
WHERE transaction_id in (p_wip_txn_id)
AND base_transaction_value > 0;
UPDATE wip_transaction_accounts wta
SET base_transaction_value = -1 * (l_wta_temp_value)
WHERE wta.transaction_id = p_wip_txn_id
AND wta.cost_element_id is null;
PROCEDURE INSERT_MAT_TXN( p_date IN DATE,
p_sm_txn_id IN NUMBER,
p_mtl_txn_id IN NUMBER,
p_acct_period_id IN NUMBER,
p_txn_qty IN NUMBER,
p_action_id IN NUMBER,
p_source_type_id IN NUMBER,
p_txn_type_name IN VARCHAR2,
p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_program_id IN NUMBER,
p_debug IN VARCHAR2,
p_err_num IN OUT NOCOPY NUMBER,
p_err_code IN OUT NOCOPY VARCHAR2,
p_err_msg IN OUT NOCOPY VARCHAR2) IS
l_rows_inserted number;
INSERT INTO mtl_material_transactions
(TRANSACTION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_NAME,
TRANSACTION_QUANTITY, TRANSACTION_UOM, PRIMARY_QUANTITY,
TRANSACTION_DATE, ACCT_PERIOD_ID, COSTED_FLAG, OPERATION_SEQ_NUM,
SOURCE_LINE_ID)
SELECT
p_mtl_txn_id, sysdate, p_user_id, sysdate,
p_user_id, p_login_id, p_request_id,
p_prog_appl_id, p_program_id, sysdate,
we.primary_item_id, we.organization_id, mtt.transaction_type_id,
mtt.transaction_action_id, mtt.transaction_source_type_id,
p_wip_entity_id, we.wip_entity_name,
p_txn_qty, msi.primary_uom_code, p_txn_qty,
p_date, p_acct_period_id, null, p_operation_seq_num,
p_sm_txn_id
FROM
mtl_transaction_types mtt,
mtl_system_items msi,
wip_entities we
WHERE we.wip_entity_id = p_wip_entity_id
AND we.primary_item_id = msi.inventory_item_id
AND we.organization_id = msi.organization_id
AND mtt.transaction_action_id = p_action_id
AND mtt.transaction_source_type_id = p_source_type_id
AND exists
( SELECT null
FROM mtl_transaction_accounts
WHERE transaction_id = p_mtl_txn_id);
l_rows_inserted := SQL%ROWCOUNT;
IF (l_rows_inserted > 0 ) and (p_debug = 'Y') THEN
FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
|| ' row(s) inserted '
|| 'into mtl_material_transactions.'
|| ', mtl_txn_id: '
|| p_mtl_txn_id
|| ', action_id: '
|| p_action_id
|| ', wip_entity_id: '
|| p_wip_entity_id);
l_rows_inserted := 0;
p_err_msg := 'CSTPSMUT.INSERT_MAT_TXN: '
|| to_char (l_stmt_num) || ');';
END INSERT_MAT_TXN;
PROCEDURE INSERT_WIP_TXN( p_date IN DATE,
p_sm_txn_id IN NUMBER,
p_wip_txn_id IN NUMBER,
p_acct_period_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_lookup_code IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_program_id IN NUMBER,
p_debug IN VARCHAR2,
p_err_num IN OUT NOCOPY NUMBER,
p_err_code IN OUT NOCOPY VARCHAR2,
p_err_msg IN OUT NOCOPY VARCHAR2,
p_txn_id IN NUMBER) IS -- Added for bug#4307365
l_rows_inserted number;
SAVEPOINT insert_wip_txn;
SELECT MMT.TRANSACTION_UOM, MSI.PRIMARY_UOM_CODE
INTO l_txn_uom, l_pr_uom
FROM MTL_SYSTEM_ITEMS MSI,
MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.TRANSACTION_ID = p_txn_id
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID;
INSERT INTO wip_transactions
(TRANSACTION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE,
ORGANIZATION_ID, WIP_ENTITY_ID, ACCT_PERIOD_ID,
TRANSACTION_TYPE, TRANSACTION_DATE, OPERATION_SEQ_NUM,
SOURCE_LINE_ID, TRANSACTION_UOM, PRIMARY_UOM,
--{BUG#7314513
primary_item_id
--}
)
SELECT
p_wip_txn_id, sysdate, p_user_id,
sysdate, p_user_id, p_login_id,
p_request_id, p_prog_appl_id, p_program_id,
sysdate,
we.organization_id, we.wip_entity_id, p_acct_period_id,
p_lookup_code, p_date, p_operation_seq_num,
p_sm_txn_id, l_txn_uom, l_pr_uom,
we.primary_item_id
FROM wip_entities we
WHERE we.wip_entity_id = p_wip_entity_id
AND exists
( SELECT null
FROM wip_transaction_accounts
WHERE transaction_id = p_wip_txn_id);
l_rows_inserted := SQL%ROWCOUNT;
IF (l_rows_inserted > 0 and p_debug = 'Y') THEN
FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
|| ' row(s) inserted ' ||
'into wip_transactions.');
l_rows_inserted := 0;
ROLLBACK TO insert_wip_txn;
p_err_msg := 'CSTPSMUT.INSERT_WIP_TXN: (' || to_char (l_stmt_num) || ');';
END INSERT_WIP_TXN;
PROCEDURE INSERT_MTA(
p_date IN DATE,
p_min_acct_unit IN NUMBER,
p_ext_prec IN NUMBER,
p_sm_txn_type IN NUMBER,
p_mtl_txn_id IN NUMBER,
p_org_id IN NUMBER,
p_wip_id IN NUMBER,
p_acct_ltype IN NUMBER,
p_txn_qty IN NUMBER,
p_tl_mtl_cost IN NUMBER,
p_tl_mto_cost IN NUMBER,
p_tl_res_cost IN NUMBER,
p_tl_ovh_cost IN NUMBER,
p_tl_osp_cost IN NUMBER,
p_cost_element_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_program_id IN NUMBER,
p_debug IN VARCHAR2,
p_err_num IN OUT NOCOPY NUMBER,
p_err_code IN OUT NOCOPY VARCHAR2,
p_err_msg IN OUT NOCOPY VARCHAR2) is
l_rows_inserted number := 0;
INSERT into mtl_transaction_accounts (
TRANSACTION_ID,
REFERENCE_ACCOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
COST_ELEMENT_ID,
ACCOUNTING_LINE_TYPE,
CONTRA_SET_ID,
BASE_TRANSACTION_VALUE,
PRIMARY_QUANTITY)
SELECT
p_mtl_txn_id,
DECODE(p_cost_element_id,
1,dj.material_account,
2,dj.material_overhead_account,
3,dj.resource_account,
4,dj.outside_processing_account,
5,dj.overhead_account),
sysdate,
p_user_id, sysdate, p_user_id,
p_login_id, p_request_id, p_prog_appl_id,
p_program_id, sysdate, mmt.inventory_item_id,
p_org_id, p_date, p_wip_id,
5, p_cost_element_id, p_acct_ltype, 1,
ROUND(DECODE(p_cost_element_id,
1, p_tl_mtl_cost,
2, p_tl_mto_cost,
3, p_tl_res_cost,
4, p_tl_osp_cost,
5, p_tl_ovh_cost)/p_min_acct_unit) * p_min_acct_unit,
p_txn_qty
FROM wip_discrete_jobs dj,
mtl_material_transactions mmt
WHERE dj.wip_entity_id = p_wip_id
AND mmt.transaction_id = p_mtl_txn_id
HAVING ROUND(DECODE(p_cost_element_id,
1, p_tl_mtl_cost,
2, p_tl_mto_cost,
3, p_tl_res_cost,
4, p_tl_osp_cost,
5, p_tl_ovh_cost)/p_min_acct_unit) * p_min_acct_unit <> 0;
l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
UPDATE MTL_TRANSACTION_ACCOUNTS
SET INV_SUB_LEDGER_ID = CST_INV_SUB_LEDGER_ID_S.NEXTVAL
WHERE TRANSACTION_ID = p_mtl_txn_id;
IF (l_rows_inserted > 0) and (p_debug = 'Y')THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(l_rows_inserted)
|| ' row(s) inserted '
|| 'into mtl_transaction_accounts.'
|| ',mtl_txn_id: '
|| p_mtl_txn_id
|| ', cost element id: '
|| p_cost_element_id
);
p_err_msg := 'CSTPSMUT.INSERT_MAT: ' || to_char (l_stmt_num) || ');';
END INSERT_MTA;
PROCEDURE INSERT_MAT_TXN_ACCT(p_date IN DATE,
p_min_acct_unit IN NUMBER,
p_ext_prec IN NUMBER,
p_sm_txn_type IN NUMBER,
p_mtl_txn_id IN NUMBER,
p_org_id IN NUMBER,
p_wip_id IN NUMBER,
p_acct_ltype IN NUMBER,
p_txn_qty IN NUMBER,
p_tl_mtl_cost IN NUMBER,
p_tl_mto_cost IN NUMBER,
p_tl_res_cost IN NUMBER,
p_tl_ovh_cost IN NUMBER,
p_tl_osp_cost IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_program_id IN NUMBER,
p_debug IN VARCHAR2,
p_err_num IN OUT NOCOPY NUMBER,
p_err_code IN OUT NOCOPY VARCHAR2,
p_err_msg IN OUT NOCOPY VARCHAR2) IS
l_stmt_num number;
SELECT cost_element_id
FROM cst_cost_elements;
INSERT_MTA(p_date,
p_min_acct_unit,
p_ext_prec,
p_sm_txn_type,
p_mtl_txn_id,
p_org_id,
p_wip_id,
p_acct_ltype,
p_txn_qty,
p_tl_mtl_cost,
p_tl_mto_cost,
p_tl_res_cost,
p_tl_ovh_cost,
p_tl_osp_cost,
c1.cost_element_id,
p_user_id,
p_login_id,
p_request_id,
p_prog_appl_id,
p_program_id,
p_debug,
p_err_num,
p_err_code,
p_err_msg);
p_err_msg := 'CSTPSMUT.INSERT_MAT_TXN_ACCT: ' || to_char (l_stmt_num) || ');';
END INSERT_MAT_TXN_ACCT;
PROCEDURE INSERT_WIP_TXN_ACCT (p_date IN DATE,
p_min_acct_unit IN NUMBER,
p_ext_prec IN NUMBER,
p_sm_txn_id IN NUMBER,
p_sm_txn_type IN NUMBER,
p_wip_txn_id IN NUMBER,
p_org_id IN NUMBER,
p_wip_id IN NUMBER,
p_acct_ltype IN NUMBER,
p_txn_qty IN NUMBER,
p_pl_mtl_cost IN NUMBER,
p_pl_mto_cost IN NUMBER,
p_pl_res_cost IN NUMBER,
p_pl_ovh_cost IN NUMBER,
p_pl_osp_cost IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_program_id IN NUMBER,
p_debug IN VARCHAR2,
p_err_num IN OUT NOCOPY NUMBER,
p_err_code IN OUT NOCOPY VARCHAR2,
p_err_msg IN OUT NOCOPY VARCHAR2) IS
l_rows_inserted number := 0;
SELECT cost_element_id
FROM cst_cost_elements;
FND_FILE.put_line(FND_FILE.LOG,'CSTPSMUT.INSERT_WIP_TXN_ACCT: wip_txn_id: '|| p_wip_txn_id);
INSERT INTO WIP_TRANSACTION_ACCOUNTS
(
TRANSACTION_ID, REFERENCE_ACCOUNT, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, ORGANIZATION_ID,
TRANSACTION_DATE, WIP_ENTITY_ID, ACCOUNTING_LINE_TYPE,
BASE_TRANSACTION_VALUE, COST_ELEMENT_ID,
PRIMARY_QUANTITY
)
SELECT
p_wip_txn_id,
DECODE(c1.cost_element_id,
1,dj.material_account,
2,dj.material_overhead_account,
3,dj.resource_account,
4,dj.outside_processing_account,
5,dj.overhead_account),
sysdate,
p_user_id, sysdate, p_user_id,
p_login_id, p_request_id, p_prog_appl_id, p_program_id,
sysdate, p_org_id, p_date,
p_wip_id, p_acct_ltype,
ROUND(DECODE(c1.cost_element_id,
1,p_pl_mtl_cost,
2,p_pl_mto_cost,
3,p_pl_res_cost,
4,p_pl_osp_cost,
5,p_pl_ovh_cost)/p_min_acct_unit)*p_min_acct_unit,
c1.cost_element_id,
p_txn_qty
FROM wip_discrete_jobs dj
WHERE dj.wip_entity_id = p_wip_id
HAVING ROUND(DECODE(c1.cost_element_id,
1,p_pl_mtl_cost,
2,p_pl_mto_cost,
3,p_pl_res_cost,
4,p_pl_osp_cost,
5,p_pl_ovh_cost)/p_min_acct_unit)*p_min_acct_unit <> 0;
UPDATE WIP_TRANSACTION_ACCOUNTS
SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
WHERE TRANSACTION_ID = p_wip_txn_id;
l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
IF (l_rows_inserted > 0) and (p_debug = 'Y') THEN
FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
|| 'row(s) inserted '
|| 'into wip_transaction_accounts.'
|| ', wip_entity_id: '
|| p_wip_id);
p_err_msg := 'CSTPSMUT.INSERT_WIP_TXN_ACCT: '
|| to_char (l_stmt_num)
|| '); ,'
fnd_file.put_line(fnd_file.log, 'Insert into MTA Failed: '||p_err_msg || substr(SQLERRM, 1, 250));
END INSERT_WIP_TXN_ACCT;
l_rows_inserted NUMBER;
INSERT into mtl_transaction_accounts
(
TRANSACTION_ID, REFERENCE_ACCOUNT, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, INVENTORY_ITEM_ID,
ORGANIZATION_ID, TRANSACTION_DATE, TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID, COST_ELEMENT_ID,
ACCOUNTING_LINE_TYPE, CONTRA_SET_ID, BASE_TRANSACTION_VALUE)
SELECT
p_mtl_txn_id,
bonus_acct_id,
sysdate,
p_user_id, sysdate, p_user_id,
p_login_id, p_request_id, p_prog_appl_id,
p_program_id, sysdate, primary_item_id,
p_org_id, p_date, p_wip_id,
5, NULL, p_acct_ltype, 1,
ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit
FROM wsm_sm_resulting_jobs
WHERE transaction_id = p_sm_txn_id
HAVING ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit <> 0;
l_rows_inserted := SQL%ROWCOUNT;
UPDATE MTL_TRANSACTION_ACCOUNTS
SET INV_SUB_LEDGER_ID = CST_INV_SUB_LEDGER_ID_S.NEXTVAL
WHERE TRANSACTION_ID = p_mtl_txn_id;
IF (l_rows_inserted > 0 and p_debug = 'Y') THEN
FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
|| ' row(s) inserted '
|| 'into mtl_transaction_accounts '
|| ', mtl_txn_id: '
|| p_mtl_txn_id);
l_rows_inserted NUMBER;
INSERT INTO WIP_TRANSACTION_ACCOUNTS
(
TRANSACTION_ID, REFERENCE_ACCOUNT, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE, ORGANIZATION_ID,
TRANSACTION_DATE, WIP_ENTITY_ID, ACCOUNTING_LINE_TYPE,
BASE_TRANSACTION_VALUE
)
SELECT p_wip_txn_id, res.bonus_acct_id, sysdate,
p_user_id, sysdate, p_user_id,
p_login_id, p_request_id, p_prog_appl_id, p_program_id,
sysdate, p_org_id, p_date,
p_wip_id, p_acct_ltype,
NVL(ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit,0)
FROM wsm_sm_resulting_jobs res
WHERE res.transaction_id = p_sm_txn_id
HAVING NVL(ROUND(p_total_cost/p_min_acct_unit)*p_min_acct_unit,0) <> 0;
UPDATE WIP_TRANSACTION_ACCOUNTS
SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
WHERE TRANSACTION_ID = p_wip_txn_id;
l_rows_inserted := SQL%ROWCOUNT;
IF (l_rows_inserted > 0 and p_debug = 'Y') THEN
FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted) || 'row(s) inserted ' ||
'into wip_transaction_accounts.');
l_rows_inserted number := 0;
UPDATE wip_period_balances wpb
SET ( request_id, program_application_id, program_id,
program_update_date, last_update_date, last_updated_by,
last_update_login, pl_material_out, pl_material_overhead_out,
pl_resource_out, pl_outside_processing_out,
pl_overhead_out) = (
SELECT p_request_id, p_prog_appl_id, p_program_id,
sysdate, sysdate, p_user_id,
p_login_id,
nvl(wpb.pl_material_out, 0) +
nvl(SUM(DECODE(mta.cost_element_id,1,
-mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_material_overhead_out, 0) +
nvl(SUM(DECODE(mta.cost_element_id,2,
-mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_resource_out, 0) +
nvl(SUM(DECODE(mta.cost_element_id,3,
-mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_outside_processing_out, 0) +
nvl(SUM(DECODE(mta.cost_element_id,4,
-mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_overhead_out, 0) +
nvl(SUM(DECODE(mta.cost_element_id,5,
-mta.base_transaction_value, 0)), 0)
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = p_sl_mtl_txn_id
AND mta.transaction_source_id = p_sl_wip_id)
-- AND mta.accounting_line_type <> 31)
WHERE wpb.wip_entity_id = p_sl_wip_id
AND wpb.acct_period_id = p_acct_period_id
AND exists
( SELECT null
FROM mtl_transaction_accounts
WHERE transaction_id = p_sl_mtl_txn_id);
l_rows_inserted := SQL%ROWCOUNT;
l_rows_inserted := 0;
UPDATE wip_period_balances wpb
SET ( request_id, program_application_id, program_id,
program_update_date, last_update_date, last_updated_by,
last_update_login, tl_material_out, tl_material_overhead_out,
tl_resource_out, tl_outside_processing_out,
tl_overhead_out ) =
( SELECT p_request_id, p_prog_appl_id, p_program_id,
sysdate, sysdate, p_user_id,
p_login_id,
nvl(wpb.tl_material_out, 0) +
nvl(SUM(DECODE(wta.cost_element_id,1,
-wta.base_transaction_value, 0)), 0),
nvl(wpb.tl_material_overhead_out, 0) +
nvl(SUM(DECODE(wta.cost_element_id,2,
-wta.base_transaction_value, 0)), 0),
nvl(wpb.tl_resource_out, 0) +
nvl(SUM(DECODE(wta.cost_element_id,3,
-wta.base_transaction_value, 0)), 0),
nvl(wpb.tl_outside_processing_out, 0) +
nvl(SUM(DECODE(wta.cost_element_id,4,
-wta.base_transaction_value, 0)), 0),
nvl(wpb.tl_overhead_out, 0) +
nvl(SUM(DECODE(wta.cost_element_id,5,
-wta.base_transaction_value, 0)), 0)
FROM wip_transaction_accounts wta
WHERE wta.transaction_id = p_sl_wip_txn_id
AND wta.wip_entity_id = p_sl_wip_id)
-- AND wta.accounting_line_type <> 31)
WHERE wpb.wip_entity_id = p_sl_wip_id
AND wpb.acct_period_id = p_acct_period_id
AND exists
( SELECT null
FROM wip_transaction_accounts
WHERE transaction_id = p_sl_wip_txn_id);
l_rows_inserted := SQL%ROWCOUNT;
l_rows_inserted := 0;
This procedure updates WPB of the resulting lots. It handles
the *_in of the WPB, not *_outs.
-------------------------------------------------------------------*/
PROCEDURE RESULT_LOT(
p_rl_mtl_txn_id IN NUMBER,
p_rl_wip_txn_id IN NUMBER,
p_rl_wip_id IN NUMBER,
p_acct_period_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_program_id IN NUMBER,
p_debug IN VARCHAR2,
p_err_num in OUT NOCOPY number,
p_err_code in OUT NOCOPY varchar2,
p_err_msg in OUT NOCOPY varchar2) IS
l_rows_inserted number;
-- Update resulting lot period balances
--
p_err_num := 0;
UPDATE wip_period_balances wpb
SET ( request_id, program_application_id, program_id,
program_update_date, last_update_date, last_updated_by,
last_update_login, pl_material_in, pl_material_overhead_in,
pl_resource_in, pl_outside_processing_in,
pl_overhead_in) = (
SELECT p_request_id, p_prog_appl_id, p_program_id,
sysdate, sysdate, p_user_id,
p_login_id,
nvl(wpb.pl_material_in, 0) +
nvl(SUM(DECODE(mta.cost_element_id,1,
mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_material_overhead_in, 0) +
nvl(SUM(DECODE(mta.cost_element_id,2,
mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_resource_in, 0) +
nvl(SUM(DECODE(mta.cost_element_id,3,
mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_outside_processing_in, 0) +
nvl(SUM(DECODE(mta.cost_element_id,4,
mta.base_transaction_value, 0)), 0),
nvl(wpb.pl_overhead_in, 0) +
nvl(SUM(DECODE(mta.cost_element_id,5,
mta.base_transaction_value, 0)), 0)
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = p_rl_mtl_txn_id
AND mta.transaction_source_id = p_rl_wip_id)
WHERE wpb.wip_entity_id = p_rl_wip_id
AND wpb.acct_period_id = p_acct_period_id
AND exists
( SELECT null
FROM mtl_transaction_accounts
WHERE transaction_id = p_rl_mtl_txn_id);
l_rows_inserted := SQL%ROWCOUNT;
IF (p_debug = 'Y') and (l_rows_inserted > 0) THEN
FND_FILE.put_line(FND_FILE.LOG,to_char(l_rows_inserted)
|| ' row(s) updated '
|| 'in wip_period_balances for mtl txn acct.');
l_rows_inserted := 0;
UPDATE wip_period_balances wpb
SET ( request_id, program_application_id, program_id,
program_update_date, last_update_date, last_updated_by,
last_update_login,
tl_resource_in, tl_outside_processing_in,
tl_overhead_in ) = (
SELECT p_request_id, p_prog_appl_id, p_program_id,
sysdate, sysdate, p_user_id,
p_login_id,
nvl(wpb.tl_resource_in, 0) +
nvl(SUM(DECODE(wta.cost_element_id,3,
wta.base_transaction_value, 0)), 0),
nvl(wpb.tl_outside_processing_in, 0) +
nvl(SUM(DECODE(wta.cost_element_id,4,
wta.base_transaction_value, 0)), 0),
nvl(wpb.tl_overhead_in, 0) +
nvl(SUM(DECODE(wta.cost_element_id,5,
wta.base_transaction_value, 0)), 0)
FROM wip_transaction_accounts wta
WHERE wta.transaction_id = p_rl_wip_txn_id
AND wta.wip_entity_id = p_rl_wip_id)
-- AND wta.accounting_line_type <> 31)
WHERE wpb.wip_entity_id = p_rl_wip_id
AND wpb.acct_period_id = p_acct_period_id
AND exists
( SELECT null
FROM wip_transaction_accounts
WHERE transaction_id = p_rl_wip_txn_id);
l_rows_inserted := SQL%ROWCOUNT;
IF (p_debug = 'Y') and (l_rows_inserted > 0 ) THEN
FND_FILE.put_line(FND_FILE.log,to_char(l_rows_inserted)
|| ' row(s) updated '
|| 'in wip_period_balances for wip txn acct.');
l_rows_inserted := 0;