[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT_MMT_FAILURE EXCEPTION;
UPDATE_QUANTITY_ISSUE_FAILURE EXCEPTION;
SELECT transaction_id,
transaction_type_id,
organization_id,
transaction_date
FROM wsm_split_merge_transactions
WHERE costed = WIP_CONSTANTS.PENDING
AND status = WIP_CONSTANTS.COMPLETED
AND organization_id = p_org_id
AND group_id = p_group_id
ORDER BY transaction_date,transaction_id;
SELECT nvl(max(fcr.request_id), -1)
INTO l_req_id
FROM fnd_concurrent_requests fcr
WHERE program_application_id = 702
AND concurrent_program_id = l_program_id
AND argument1 = TO_CHAR(p_org_id)
AND phase_code <> 'C'
AND fcr.request_id <> l_request_id;
/* Check if a Standard Cost Update is Running */
l_stmt_num := 20;
SELECT concurrent_program_id
INTO l_cmcicu_prog_id
FROM fnd_concurrent_programs fcp
WHERE fcp.application_id = 702
AND fcp.concurrent_program_name = 'CMCICU';
SELECT nvl(max(fcr.request_id), -1)
INTO l_req_id
FROM fnd_concurrent_requests fcr
WHERE program_application_id = 702
AND concurrent_program_id = l_cmcicu_prog_id
AND argument1 = TO_CHAR(p_org_id)
AND phase_code = 'R';
/* All transactions of type update_assembly,update_routing must
be set to costed for the given organization and group
These have no costing impact */
l_stmt_num := 50;
UPDATE wsm_split_merge_transactions
SET costed = WIP_CONSTANTS.COMPLETED
WHERE transaction_type_id in (3,5,7)
AND costed = WIP_CONSTANTS.PENDING
AND status = WIP_CONSTANTS.COMPLETED
AND organization_id = p_org_id
AND group_id = p_group_id;
SELECT count(*)
INTO l_txn_mmt_flag
FROM mtl_material_transactions mmt,
wsm_split_merge_transactions wsmt
WHERE
(transaction_source_id in
(select wip_entity_id
from wsm_sm_resulting_jobs wsrj
where wsrj.transaction_id = wsmt.transaction_id)
or
transaction_source_id in
(select wip_entity_id
from wsm_sm_starting_jobs wssj
where wssj.transaction_id = wsmt.transaction_id))
AND mmt.organization_id = wsmt.organization_id
AND mmt.source_line_id = wsmt.transaction_id
AND wsmt.transaction_id = c_uncost_rec.transaction_id;
/* If it doesn't, call API's to insert transaction into MMT
and update quantity */
IF l_txn_mmt_flag = 0 THEN
IF ( l_debug_flag = 'Y' ) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting Transaction into MMT');
WSM_JobCosting_GRP.Insert_MaterialTxn (
c_uncost_rec.transaction_id,
l_error_code,
l_error_buf );
RAISE INSERT_MMT_FAILURE;
SELECT TRANSACTION_TYPE_ID
INTO l_txn_type_id
FROM WSM_SPLIT_MERGE_TRANSACTIONS
WHERE transaction_id = c_uncost_rec.transaction_id;
WSM_JobCosting_GRP.Update_QtyIssued (
c_uncost_rec.transaction_id,
l_txn_type_id,
l_error_code,
l_error_buf );
RAISE UPDATE_QUANTITY_ISSUE_FAILURE;
WHEN UPDATE_QUANTITY_ISSUE_FAILURE THEN
ROLLBACK;
WHEN INSERT_MMT_FAILURE THEN
ROLLBACK;
FUNCTION UPDATE_WSMT_TXN_STATUS
( p_txn_id IN NUMBER,
p_costed IN NUMBER,
p_error_message IN VARCHAR2,
p_request_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_prog_id IN NUMBER )
RETURN BOOLEAN IS
BEGIN
UPDATE wsm_split_merge_transactions
SET costed = p_costed,
error_message = p_error_message,
request_id = p_request_id,
program_application_id = p_prog_appl_id,
program_id = p_prog_id,
program_update_date = sysdate
WHERE transaction_id = p_txn_id;
END UPDATE_WSMT_TXN_STATUS;
l_ret_update BOOLEAN;
UPDATE_JOB_QUANTITY_FAILURE EXCEPTION;
SELECT transaction_source_id,
transaction_date,
organization_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
nvl(source_line_id, -1)
INTO l_wip_entity_id,
l_transaction_date,
l_organization_id,
l_transaction_type_id,
l_txn_action_id,
l_txn_source_type_id,
l_wsmt_transaction_id
FROM MTL_MATERIAL_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
SELECT program_application_id,
concurrent_program_id,
conc_login_id,
requested_by
INTO l_prog_application_id,
l_program_id,
l_login_id,
l_user_id
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = p_request_id;
UPDATE wsm_split_merge_transactions txn
SET costed = WIP_CONSTANTS.COMPLETED
WHERE transaction_type_id = 4
AND transaction_id = l_wsmt_transaction_id /* Added for bug 5008413 */
AND costed = WIP_CONSTANTS.PENDING
AND status = WIP_CONSTANTS.COMPLETED
AND EXISTS ( SELECT 'Queue Intraop'
FROM wsm_sm_resulting_jobs rj,
bom_operation_sequences bos,
wip_operations wo
WHERE rj.transaction_id = txn.transaction_id
AND rj.starting_intraoperation_step = 1
AND (nvl(rj.job_operation_seq_num,
wo.operation_seq_num), wo.organization_id) =
(SELECT min(operation_seq_num), wo2.organization_id
FROM wip_operations wo2
WHERE wo2.wip_entity_id = rj.wip_entity_id
AND wo2.organization_id = rj.organization_id
GROUP BY wo2.organization_id)
AND rj.transaction_id = txn.transaction_id
AND rj.starting_intraoperation_step = 1
AND rj.common_routing_sequence_id = bos.routing_sequence_id
AND rj.starting_operation_seq_num = bos.operation_seq_num
AND bos.operation_sequence_id = wo.operation_sequence_id
AND bos.EFFECTIVITY_DATE <= txn.transaction_date
AND NVL( bos.DISABLE_DATE, txn.transaction_date + 1
) > txn.transaction_date
AND wo.wip_entity_id = rj.wip_entity_id
);
CSTPSMUT.COST_UPDATE_QTY_TXN
( p_api_version => 1.0,
p_transaction_id => l_wsmt_transaction_id,
p_mmt_transaction_id => p_transaction_id,
p_transaction_date => l_transaction_date,
p_prog_application_id => l_prog_application_id,
p_program_id => l_program_id,
p_request_id => p_request_id,
p_login_id => l_login_id,
p_user_id => l_user_id,
x_err_num => x_err_num,
x_err_code => x_err_code,
x_err_msg => l_err_msg );
Update WRO, WOR for the jobs involved in the transaction.
*/
l_stmt_num := 60;
CSTPSMUT.UPDATE_JOB_QUANTITY
( p_api_version => 1.0,
p_txn_id => l_wsmt_transaction_id,
x_err_num => x_err_num,
x_err_code => x_err_code,
x_err_msg => l_err_msg );
RAISE UPDATE_JOB_QUANTITY_FAILURE;
/* Update Successful, Do Operation Yield Costing.
Call CSTPOYLD.process_sm_op_yld to populate WOY for the jobs.
The Operation Yield Processor then picks it up separately for
calculating the yielded costs.
For Bonus and Update Qty Txns call CSTPOYLD.process_op_yield
online to calculate the yielded costs since the accounting for
these transactions is different.
(They use the Bonus account specified on the transaction)
*/
l_stmt_num := 70;
SELECT wip_entity_id,
operation_seq_num
INTO l_resulting_wip_id,
l_starting_op_seq
FROM wsm_sm_starting_jobs
WHERE transaction_id = l_wsmt_transaction_id;
SELECT wip_entity_id,
job_operation_seq_num
INTO l_resulting_wip_id,
l_starting_op_seq
FROM WSM_SM_RESULTING_JOBS WSRJ
WHERE transaction_id = l_wsmt_transaction_id;
SELECT wo.operation_seq_num
INTO l_starting_op_seq
FROM WIP_OPERATIONS WO,
WSM_SM_RESULTING_JOBS WSRJ,
BOM_OPERATION_SEQUENCES BOS
WHERE WSRJ.transaction_id = l_wsmt_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 <= 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_organization_id;
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.COMPLETED,
NULL, -- Error Message
p_request_id,
l_prog_application_id,
l_program_id );
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.ERROR,
l_err_msg,
p_request_id,
l_prog_application_id,
l_program_id );
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.ERROR,
l_err_msg,
p_request_id,
l_prog_application_id,
l_program_id );
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.ERROR,
l_err_msg,
p_request_id,
l_prog_application_id,
l_program_id );
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.ERROR,
l_err_msg,
p_request_id,
l_prog_application_id,
l_program_id );
WHEN UPDATE_JOB_QUANTITY_FAILURE THEN
x_err_num := -1;
x_err_code := 'Failed to Update Job Info: Transaction Costing Failed';
FND_FILE.put_line(fnd_file.log, 'Failed to Update Job Info: Transaction Costing Failed'|| l_err_msg );
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.ERROR,
l_err_msg,
p_request_id,
l_prog_application_id,
l_program_id );
x_err_code := 'Update of Wip Operation Yields Failed';
FND_FILE.put_line(fnd_file.log, 'Update of Wip Operation Yields Failed'|| l_err_msg );
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.ERROR,
l_err_msg,
p_request_id,
l_prog_application_id,
l_program_id );
l_ret_update := UPDATE_WSMT_TXN_STATUS ( l_wsmt_transaction_id,
WIP_CONSTANTS.ERROR,
l_err_msg,
p_request_id,
l_prog_application_id,
l_program_id );