[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
SELECT woy.wip_entity_id wip_entity_id,
woy.organization_id organization_id,
MIN(woy.operation_seq_num) starting_op_seq,
WDJ.EST_SCRAP_ACCOUNT est_scrap_account,
WDJ.EST_SCRAP_VAR_ACCOUNT est_scrap_var_account,
WDJ.PRIMARY_ITEM_ID primary_item_id
FROM wip_operation_yields woy, wip_discrete_jobs wdj
WHERE woy.status IN (1, 3)
AND woy.wip_entity_id = DECODE(NVL(i_entity_id, 0), 0, woy.wip_entity_id, i_entity_id)
AND woy.organization_id = DECODE(NVL(i_organization_id, 0), 0, woy.organization_id, i_organization_id)
AND WDJ.WIP_ENTITY_ID = WOY.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WOY.ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN ( 3,4,5,6,7,15 )
GROUP BY woy.wip_entity_id, woy.organization_id,
wdj.est_scrap_account, wdj.est_scrap_var_account,
wdj.primary_item_id
ORDER BY woy.wip_entity_id;
inserted against the last operation_seq_num in wip_operation_yields table.
Please note that it does not cause any valuation mismatch, and is not
a cause for concern, until now */
CURSOR c_opseq(p_entity_id NUMBER, p_starting_opseq NUMBER, p_organization_id NUMBER) IS
SELECT WOY.OPERATION_SEQ_NUM,
NVL(WOY.OPERATION_COST, 0) OPERATION_COST,
NVL(WOY.OPERATION_UNIT_COST, 0) OPERATION_UNIT_COST,
NVL(WOY.CUM_OPERATION_UNIT_COST, 0) CUM_OPERATION_UNIT_COST ,
NVL(WOY.EST_SCRAP_UNIT_COST, 0) EST_SCRAP_UNIT_COST,
NVL(WOY.CUM_EST_PRIOR_UNIT_COST, 0) CUM_EST_PRIOR_UNIT_COST,
NVL(WOY.EST_SCRAP_QTY_COMPLETED, 0) EST_SCRAP_QTY_COMPLETED,
NVL(WOY.EST_SCRAP_QTY_SCRAPED, 0) EST_SCRAP_QTY_SCRAPED,
WOY.SCRAP_ACCOUNT,
WOY.EST_SCRAP_ABSORB_ACCOUNT,
WOY.STATUS,
NVL(WO.WSM_COSTED_QUANTITY_COMPLETED, NVL(WO.QUANTITY_COMPLETED, 0)) QUANTITY_COMPLETED,
NVL(WO.QUANTITY_SCRAPPED, 0) QUANTITY_SCRAPPED,
DECODE (WO.OPERATION_YIELD_ENABLED, 1, NVL(WO.OPERATION_YIELD, 1),
1) OPERATION_YIELD,
NVL(WO.DEPARTMENT_ID, 0) DEPARTMENT_ID,
WO.DISABLE_DATE DISABLE_DATE
FROM WIP_OPERATION_YIELDS WOY,
WIP_OPERATIONS WO
WHERE WOY.WIP_ENTITY_ID = p_entity_id
AND WOY.OPERATION_SEQ_NUM >= p_starting_opseq
AND WO.WIP_ENTITY_ID = WOY.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WOY.OPERATION_SEQ_NUM
AND WO.ORGANIZATION_ID = WOY.ORGANIZATION_ID
AND WOY.ORGANIZATION_ID = p_organization_id
ORDER BY WOY.OPERATION_SEQ_NUM
FOR UPDATE OF woy.status;
x_last_updated_by := fnd_global.user_id;
x_last_update_login := fnd_global.login_id;
SELECT COD.CURRENCY_CODE, COD.OPERATING_UNIT
INTO x_currency_code, l_operating_unit
FROM CST_ORGANIZATION_DEFINITIONS COD
WHERE COD.ORGANIZATION_ID = rec_wip_entity.organization_id;
SELECT acct_period_id
INTO x_acct_period_id
FROM org_acct_periods
WHERE organization_id = x_organization_id
AND l_legal_entity_date BETWEEN period_start_date AND schedule_close_date;
SELECT bd.scrap_account,bd.est_absorption_account
INTO l_scrap_acct, l_est_scrap_abs_acct
FROM bom_departments bd, wip_operations wo
WHERE wo.operation_seq_num = rec_opseq.operation_seq_num
AND wo.wip_entity_id = rec_wip_entity.wip_entity_id
AND wo.organization_id = rec_wip_entity.organization_id
AND bd.department_id = wo.department_id
AND bd.organization_id = wo.organization_id;
UPDATE wip_operation_yields woy
SET SCRAP_ACCOUNT = l_scrap_acct,
EST_SCRAP_ABSORB_ACCOUNT = l_est_scrap_abs_acct
WHERE woy.operation_seq_num = rec_opseq.operation_seq_num
AND woy.wip_entity_id = rec_wip_entity.wip_entity_id
AND woy.organization_id = rec_wip_entity.organization_id;
update assembly transactions. The obsoleted operation is determined
by a valid disable date */
x_statement := 200;
SELECT count(*)
INTO x_count
FROM WIP_OPERATION_YIELDS woy,
WIP_OPERATIONS wo
WHERE woy.wip_entity_id = rec_wip_entity.wip_entity_id
and wo.wip_entity_id = woy.wip_entity_id
and woy.organization_id = x_organization_id
and wo.organization_id = x_organization_id
and woy.operation_seq_num = wo.operation_seq_num
and woy.operation_seq_num < rec_opseq.operation_seq_num
and wo.disable_date is null;
SELECT NVL(CUM_OPERATION_UNIT_COST, 0)
INTO x_unit_cost
FROM WIP_OPERATION_YIELDS
WHERE wip_entity_id = rec_wip_entity.wip_entity_id
and organization_id = x_organization_id
and operation_seq_num = (select max(woy.operation_seq_num)
from wip_operation_yields woy,
wip_operations wo
where woy.wip_entity_id = rec_wip_entity.wip_entity_id
and woy.organization_id = x_organization_id
and woy.operation_seq_num < rec_opseq.operation_seq_num
and woy.operation_seq_num = wo.operation_seq_num
and woy.wip_entity_id = wo.wip_entity_id
and woy.organization_id = wo.organization_id
and wo.disable_date is null);
SELECT SUM( NVL(EST_SCRAP_UNIT_COST, 0))
INTO x_cum_pr_est_scp_per_unit
FROM WIP_OPERATION_YIELDS WOY,
WIP_OPERATIONS WO
WHERE woy.wip_entity_id = rec_wip_entity.wip_entity_id
and woy.organization_id = x_organization_id
and woy.operation_seq_num < rec_opseq.operation_seq_num
and woy.operation_seq_num = wo.operation_seq_num
and woy.wip_entity_id = wo.wip_entity_id
and woy.organization_id = wo.organization_id
and wo.disable_date is null;
SELECT SUM( NVL(EST_SCRAP_UNIT_COST, 0))
INTO x_cum_pr_est_scp_per_unit
FROM WIP_OPERATION_YIELDS WOY,
WIP_OPERATIONS WO
WHERE woy.wip_entity_id = rec_wip_entity.wip_entity_id
and woy.organization_id = x_organization_id
and woy.operation_seq_num < rec_opseq.operation_seq_num
and woy.operation_seq_num = wo.operation_seq_num
and woy.wip_entity_id = wo.wip_entity_id
and woy.organization_id = wo.organization_id
and wo.disable_date is null;
select wsm_enabled_flag
into x_wsm_enabled_flag
from mtl_parameters
where organization_id = rec_wip_entity.organization_id;
select NVL(last_operation_seq_num,9999)
into x_last_opseq_num
from wsm_parameters
where organization_id = rec_wip_entity.organization_id;
SELECT BONUS_ACCT_ID
INTO x_abs_account
FROM WSM_SM_RESULTING_JOBS
WHERE TRANSACTION_ID = i_sm_txn_id
AND WIP_ENTITY_ID = i_entity_id;
SELECT wip_transactions_s.nextval
INTO x_transaction_id
FROM dual;
fnd_file.put_line(fnd_file.log,'Inserting into WT transaction : '||to_char(x_transaction_id));
INSERT INTO WIP_TRANSACTIONS(transaction_id,
organization_id,
wip_entity_id,
acct_period_id,
department_id,
transaction_type,
transaction_date,
operation_seq_num,
primary_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES(x_transaction_id,
x_organization_id,
rec_wip_entity.wip_entity_id,
x_acct_period_id,
rec_opseq.department_id,
15,
l_transaction_date,
rec_opseq.operation_seq_num,
rec_wip_entity.primary_item_id,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
fnd_file.put_line(fnd_file.log,'Inserting into WTA transaction : '||to_char(x_transaction_id));
INSERT INTO
WIP_TRANSACTION_ACCOUNTS
(
wip_sub_ledger_id, /* R12 - SLA Distribution Link */
transaction_id,
reference_account,
organization_id,
transaction_date,
wip_entity_id,
accounting_line_type,
base_transaction_value,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES
(
CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
x_transaction_id,
rec_wip_entity.est_scrap_account,
x_organization_id,
l_transaction_date,
rec_wip_entity.wip_entity_id,
7,
decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_absorption, x_precision),
ROUND (x_net_absorption / x_min_acct_unit) * x_min_acct_unit),
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
INSERT INTO
WIP_TRANSACTION_ACCOUNTS
(
wip_sub_ledger_id, /* R12 - SLA Distribution Link */
transaction_id,
reference_account,
organization_id,
transaction_date,
wip_entity_id,
accounting_line_type,
base_transaction_value,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES
(
CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
x_transaction_id,
x_abs_account,
x_organization_id,
l_transaction_date,
rec_wip_entity.wip_entity_id,
29,
decode(NVL(x_min_acct_unit, 0), 0, ROUND(-1 *(x_net_absorption), x_precision),
ROUND (-1 *(x_net_absorption) / x_min_acct_unit) * x_min_acct_unit),
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
SELECT mtl_material_transactions_s.nextval
INTO x_transaction_id
FROM dual;
SELECT muom.uom_code
INTO l_uom
FROM
mtl_system_items msi, mtl_units_of_measure muom
WHERE msi.inventory_item_id = rec_wip_entity.primary_item_id
AND msi.organization_id = x_organization_id
AND msi.primary_unit_of_measure = muom.unit_of_measure;
fnd_file.put_line(fnd_file.log,'Inserting into MMT transaction : '||to_char(x_transaction_id));
INSERT into
MTL_MATERIAL_TRANSACTIONS(
transaction_id,
inventory_item_id,
organization_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_date,
acct_period_id,
department_id,
operation_seq_num,
transaction_source_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(x_transaction_id,
rec_wip_entity.primary_item_id,
x_organization_id,
92, /* Est Scrap Txn in MMT (new type) */
30,
5,
(rec_opseq.quantity_scrapped
- rec_opseq.est_scrap_qty_scraped),
l_uom,
(rec_opseq.quantity_scrapped
- rec_opseq.est_scrap_qty_scraped),
l_transaction_date,
x_acct_period_id,
rec_opseq.department_id,
rec_opseq.operation_seq_num,
rec_wip_entity.wip_entity_id,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
INSERT into MTL_TRANSACTION_ACCOUNTS
(
inv_sub_ledger_id, /* R12 - SLA Distribution Link */
transaction_id,
reference_account,
inventory_item_id,
organization_id,
transaction_date,
transaction_source_id,
transaction_source_type_id,
primary_quantity,
accounting_line_type,
base_transaction_value,
contra_set_id,
rate_or_amount,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES(
CST_INV_SUB_LEDGER_ID_S.NEXTVAL,
x_transaction_id,
l_scrap_rev_acct,
rec_wip_entity.primary_item_id,
x_organization_id,
l_transaction_date,
rec_wip_entity.wip_entity_id,
5,
(rec_opseq.quantity_scrapped - rec_opseq.est_scrap_qty_scraped),
2,
decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_reversal, x_precision),
ROUND (x_net_reversal / x_min_acct_unit) * x_min_acct_unit),
1,
x_cum_pr_est_scp_per_unit,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
INSERT into MTL_TRANSACTION_ACCOUNTS
(
inv_sub_ledger_id, /* R12 - SLA Distribution Link */
transaction_id,
reference_account,
inventory_item_id,
organization_id,
transaction_date,
transaction_source_id,
transaction_source_type_id,
primary_quantity,
accounting_line_type,
base_transaction_value,
contra_set_id,
rate_or_amount,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES(
CST_INV_SUB_LEDGER_ID_S.NEXTVAL,
x_transaction_id,
rec_wip_entity.est_scrap_account,
rec_wip_entity.primary_item_id,
x_organization_id,
l_transaction_date,
rec_wip_entity.wip_entity_id,
5,
(rec_opseq.quantity_scrapped - rec_opseq.est_scrap_qty_scraped),
7,
decode(NVL(x_min_acct_unit, 0), 0, ROUND(-1 *(x_net_reversal), x_precision),
ROUND (-1 *(x_net_reversal) / x_min_acct_unit) * x_min_acct_unit),
1,
x_cum_pr_est_scp_per_unit,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
UPDATE WIP_OPERATION_YIELDS
SET operation_unit_cost = x_op_unit_cost,
cum_operation_unit_cost = x_unit_cost,
est_scrap_unit_cost = x_est_scrap_per_unit,
cum_est_prior_unit_cost = x_cum_pr_est_scp_per_unit,
est_scrap_qty_completed = rec_opseq.quantity_completed,
est_scrap_qty_scraped = rec_opseq.quantity_scrapped,
status = 2,
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = x_sysdate
WHERE organization_id = x_organization_id
AND wip_entity_id = rec_wip_entity.wip_entity_id
AND operation_seq_num = rec_opseq.operation_seq_num;
SELECT x_tl_scrap_in + decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_absorption, x_precision),
ROUND (x_net_absorption / x_min_acct_unit) * x_min_acct_unit),
x_tl_scrap_out + decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_net_reversal, x_precision),
ROUND (x_net_reversal / x_min_acct_unit) * x_min_acct_unit)
INTO x_tl_scrap_in,
x_tl_scrap_out
FROM dual;
UPDATE WIP_OPERATION_YIELDS
SET status = 3,
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = x_sysdate
WHERE organization_id = x_organization_id
AND wip_entity_id = rec_wip_entity.wip_entity_id
AND operation_seq_num = rec_opseq.operation_seq_num;
UPDATE WIP_OPERATION_YIELDS
SET status = 3,
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = x_sysdate
WHERE organization_id = x_organization_id
AND wip_entity_id = rec_wip_entity.wip_entity_id
AND operation_seq_num = rec_opseq.operation_seq_num;
/* Update WOY if x_tl_scrap_in or x_tl_scrap_out <> 0 */
IF (x_tl_scrap_in <> 0 OR x_tl_scrap_out <> 0) THEN
x_statement := 130;
UPDATE WIP_PERIOD_BALANCES
SET tl_scrap_in = NVL(tl_scrap_in, 0) + x_tl_scrap_in,
tl_scrap_out = NVL(tl_scrap_out, 0) + x_tl_scrap_out,
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
request_id = x_request_id,
program_application_id = x_program_application_id,
program_id = x_program_id,
program_update_date = x_sysdate
WHERE organization_id = x_organization_id
AND wip_entity_id = rec_wip_entity.wip_entity_id
AND acct_period_id = x_acct_period_id;
SELECT distinct wcti.transaction_id,
wcti.wip_entity_id,
wcti.acct_period_id,
wcti.organization_id,
wcti.transaction_date, /* Bug 4757384 */
wdj.est_scrap_account,
wdj.est_scrap_var_account,
wdj.primary_item_id
FROM WIP_COST_TXN_INTERFACE wcti,
WIP_DISCRETE_JOBS wdj,
WIP_ENTITIES we
WHERE wcti.group_id = i_group_id
AND we.entity_type = 5
and we.wip_entity_id = wcti.wip_entity_id
and we.organization_id = wcti.organization_id
AND wcti.wip_entity_id = wdj.wip_entity_id;
/* The following lines in the select clause has been replaced with
the reference to"CST_ORGANIZATION_DEFINITIONS" as an impact of the
HR-PROFILE option" */
SELECT COD.CURRENCY_CODE
INTO x_currency_code
FROM CST_ORGANIZATION_DEFINITIONS COD
WHERE COD.ORGANIZATION_ID = rec_wip_entity.organization_id;
SELECT
NVL(SUM(NVL(tl_scrap_in,0)), 0),
NVL(SUM(NVL(TL_SCRAP_OUT,0)), 0),
NVL(SUM(NVL(TL_SCRAP_VAR,0)), 0)
INTO
x_tl_scrap_in,
x_tl_scrap_out,
x_tl_scrap_var
FROM
WIP_PERIOD_BALANCES
WHERE
wip_entity_id=rec_wip_entity.wip_entity_id
AND organization_id=rec_wip_entity.organization_id
AND acct_period_id <= rec_wip_entity.acct_period_id;
INSERT INTO
WIP_TRANSACTION_ACCOUNTS
(
wip_sub_ledger_id, /* R12 - SLA Distribution Link */
transaction_id,
reference_account,
organization_id,
transaction_date,
wip_entity_id,
accounting_line_type,
base_transaction_value,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES
(
CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
rec_wip_entity.transaction_id,
rec_wip_entity.est_scrap_var_account,
x_organization_id,
rec_wip_entity.transaction_date,
rec_wip_entity.wip_entity_id,
8,
decode(NVL(x_min_acct_unit, 0), 0, ROUND(x_scrap_variance, x_precision),
ROUND (x_scrap_variance / x_min_acct_unit) * x_min_acct_unit),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id ,
i_req_id ,
i_prg_appl_id,
i_prg_id,
sysdate);
INSERT INTO
WIP_TRANSACTION_ACCOUNTS
(
wip_sub_ledger_id, /* R12 - SLA Distribution Link */
transaction_id,
reference_account,
organization_id,
transaction_date,
wip_entity_id,
accounting_line_type,
base_transaction_value,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date )
VALUES
(
CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
rec_wip_entity.transaction_id,
rec_wip_entity.est_scrap_account,
x_organization_id,
rec_wip_entity.transaction_date,
rec_wip_entity.wip_entity_id,
7,
decode(NVL(x_min_acct_unit, 0), 0, ROUND(-1 *(x_scrap_variance), x_precision),
ROUND (-1 * (x_scrap_variance) / x_min_acct_unit) * x_min_acct_unit),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id ,
i_req_id ,
i_prg_appl_id,
i_prg_id,
sysdate);
UPDATE WIP_PERIOD_BALANCES wpb
SET
TL_SCRAP_VAR =
(SELECT SUM( NVL(TL_SCRAP_IN,0)
- NVL(TL_SCRAP_OUT,0)
- decode(wpb2.acct_period_id,wpb.acct_period_id,0,
NVL(TL_SCRAP_VAR,0)))
FROM WIP_PERIOD_BALANCES wpb2
WHERE wpb2.wip_entity_id = wpb.wip_entity_id
AND wpb2.acct_period_id <= wpb.acct_period_id),
last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_req_id ,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate
WHERE
organization_id = x_organization_id
AND acct_period_id = rec_wip_entity.acct_period_id
AND wip_entity_id= rec_wip_entity.wip_entity_id;
SELECT count(*)
INTO x_history_count
FROM WIP_OP_YIELD_HISTORY
WHERE wip_entity_id = rec_wip_entity.wip_entity_id
AND organization_id = x_organization_id
AND acct_period_id = rec_wip_entity.acct_period_id;
INSERT INTO WIP_OP_YIELD_HISTORY
(wip_entity_id,
organization_id,
acct_period_id,
est_scrap_absorb_amt,
est_scrap_reverse_amt,
est_scrap_var_amt,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES
(rec_wip_entity.wip_entity_id,
x_organization_id,
rec_wip_entity.acct_period_id,
x_tl_scrap_in,
x_tl_scrap_out,
(x_scrap_variance + x_tl_scrap_var),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id ,
i_req_id ,
i_prg_appl_id,
i_prg_id,
sysdate);
UPDATE WIP_OP_YIELD_HISTORY
SET
est_scrap_absorb_amt = x_tl_scrap_in,
est_scrap_reverse_amt = x_tl_scrap_out,
est_scrap_var_amt = (x_scrap_variance + x_tl_scrap_var),
last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_req_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate
WHERE wip_entity_id = rec_wip_entity.wip_entity_id
AND organization_id = x_organization_id
AND acct_period_id = rec_wip_entity.acct_period_id;
SELECT sj.wip_entity_id wip_entity_id,
sj.operation_seq_num op_seq_num,
sj.intraoperation_step intra_op_step,
smt.organization_id organization_id,
sj.routing_seq_id routing_seq_id,
smt.transaction_type_id txn_type_id
FROM wsm_sm_starting_jobs sj,
wsm_split_merge_transactions smt
WHERE smt.transaction_id = i_txn_id
AND smt.transaction_type_id In (1, 2, 6)
AND smt.transaction_id = sj.transaction_id
UNION
select rj.wip_entity_id wip_entity_id,
nvl(rj.starting_operation_seq_num,sj.operation_seq_num) op_seq_num,
rj.starting_intraoperation_step intra_op_step,
smt.organization_id organization_id,
rj.common_routing_sequence_id routing_seq_id,
smt.transaction_type_id txn_type_id
from wsm_sm_resulting_jobs rj,
wsm_split_merge_transactions smt,
wsm_sm_starting_jobs sj
where smt.transaction_id = i_txn_id
and smt.transaction_type_id in (1,2,6)
and smt.transaction_id = rj.transaction_id
and smt.transaction_id = sj.transaction_id
and sj.representative_flag = 'Y'
UNION
Select rj.wip_entity_id wip_entity_id,
rj.job_operation_seq_num op_seq_num,
nvl(rj.starting_intraoperation_step, WIP_CONSTANTS.QUEUE) intra_op_step,
smt.organization_id organization_id,
rj.common_routing_sequence_id routing_seq_id,
smt.transaction_type_id txn_type_id
from wsm_sm_resulting_jobs rj,
wsm_split_merge_transactions smt
where smt.transaction_id = i_txn_id
and smt.transaction_type_id = 4
and smt.transaction_id = rj.transaction_id
and rj.job_operation_seq_num is not NULL
/* Jobs prior to 11i.8 would not have JOB_OPERATION_SEQ_NUM
populated. Not modifying the above since this will not
happen in most cases and also is much cleaner performance
wise */
UNION
Select rj.wip_entity_id wip_entity_id,
wo.operation_seq_num op_seq_num,
nvl(rj.starting_intraoperation_step, WIP_CONSTANTS.QUEUE) intra_op_step,
smt.organization_id organization_id,
rj.common_routing_sequence_id routing_seq_id,
smt.transaction_type_id txn_type_id
from wsm_sm_resulting_jobs rj,
wsm_split_merge_transactions smt,
wip_operations wo,
bom_operation_sequences bos
where smt.transaction_id = i_txn_id
and smt.transaction_type_id = 4
and smt.transaction_id = rj.transaction_id
and rj.starting_operation_seq_num = bos.operation_seq_num
and rj.common_routing_sequence_id = bos.routing_sequence_id
and bos.operation_sequence_id = wo.operation_sequence_id
AND bos.EFFECTIVITY_DATE <= smt.transaction_date
AND NVL( bos.DISABLE_DATE, smt.transaction_date + 1) > smt.transaction_date
and wo.wip_entity_id = rj.wip_entity_id
and wo.organization_id = smt.organization_id
and rj.job_operation_seq_num is NULL
order by wip_entity_id;
SELECT wo.operation_seq_num,
wdj.start_quantity
FROM wip_operations wo,
wip_discrete_jobs wdj
WHERE wo.wip_entity_id = p_wip_entity_id
AND wo.operation_seq_num <= p_op_seq_num
AND wo.organization_id = p_organization_id
AND wo.wip_entity_id = wdj.wip_entity_id
AND wo.organization_id = wdj.organization_id
ORDER BY wo.operation_seq_num;
SELECT
NVL(SUM ((NVL(CIC.MATERIAL_COST,0) +
NVL(CIC.MATERIAL_OVERHEAD_COST,0) +
NVL(CIC.RESOURCE_COST,0) +
NVL(CIC.OUTSIDE_PROCESSING_COST,0) +
NVL(CIC.OVERHEAD_COST,0)) * NVL(WRO.COSTED_QUANTITY_ISSUED, 0)), 0)
INTO
x_pl_cost
FROM
wip_requirement_operations WRO,
cst_item_costs CIC
WHERE
CIC.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND CIC.COST_TYPE_ID = 1
AND WRO.WIP_ENTITY_ID = rec_wip_entity.wip_entity_id
AND WRO.OPERATION_SEQ_NUM = rec_opseq.operation_seq_num
AND WRO.ORGANIZATION_ID = rec_wip_entity.organization_id;
SELECT
NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
3, DECODE(BR.STANDARD_RATE_FLAG,
1, decode(BR.functional_currency_flag,
1,nvl(WOR.APPLIED_RESOURCE_UNITS,0),
nvl(CRC.RESOURCE_RATE*WOR.APPLIED_RESOURCE_UNITS,0)),
2, nvl(WOR.APPLIED_RESOURCE_VALUE,0)),
0)),0),
NVL(SUM(DECODE(BR.COST_ELEMENT_ID,
4, DECODE(BR.STANDARD_RATE_FLAG,
1, decode(BR.functional_currency_flag,
1,nvl(WOR.APPLIED_RESOURCE_UNITS,0),
nvl(CRC.RESOURCE_RATE*WOR.APPLIED_RESOURCE_UNITS,0)),
2, nvl(WOR.APPLIED_RESOURCE_VALUE,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 = rec_opseq.operation_seq_num
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND WOR.WIP_ENTITY_ID = rec_wip_entity.wip_entity_id
AND WOR.ORGANIZATION_ID = rec_wip_entity.organization_id;
SELECT nvl(sum(WOO.applied_ovhd_value),0)
INTO x_ovhd_cost
FROM wip_operation_overheads WOO
WHERE
WOO.wip_entity_id = rec_wip_entity.wip_entity_id
and WOO.operation_seq_num = rec_opseq.operation_seq_num
and WOO.organization_id = rec_wip_entity.organization_id;
UPDATE wip_operation_yields
SET operation_cost = x_operation_cost,
status = 1,
last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_req_id ,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate
WHERE wip_entity_id = rec_wip_entity.wip_entity_id
AND operation_seq_num = rec_opseq.operation_seq_num
AND organization_id = rec_wip_entity.organization_id;
Function cost_update_adjustment (i_org_id IN NUMBER,
i_update_id IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_prg_appl_id IN NUMBER,
i_prg_id IN NUMBER,
i_req_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2)
return NUMBER IS
l_adj_value NUMBER;
SELECT cscav.WIP_ENTITY,
cscav.OP_SEQ_NUM,
cscav.ADJ_VALUE
FROM ( SELECT wip_entity_id WIP_ENTITY,
operation_seq_num OP_SEQ_NUM,
SUM((NVL(new_unit_cost,0) - NVL(old_unit_cost,0)) * adjustment_quantity) adj_value
FROM cst_std_cost_adj_values
WHERE organization_id = i_org_id
AND cost_update_id = i_update_id
AND transaction_type NOT IN (1, 2, 4, 5)
GROUP BY wip_entity_id, operation_seq_num
HAVING SUM((NVL(new_unit_cost,0) - NVL(old_unit_cost,0)) * adjustment_quantity) <> 0
) cscav,
wip_entities we
WHERE cscav.wip_entity = we.wip_entity_id
AND we.organization_id = i_org_id
AND we.entity_type = 5
ORDER BY wip_entity, op_seq_num;
/* Update operation cost in WOY only if ESA is enabled */
IF x_est_scrap_acct_flag = 1 THEN
l_stmt_num := 20;
update wip_operation_yields
set last_update_date = sysdate,
last_updated_by = i_user_id,
last_update_login = i_login_id,
request_id = i_req_id,
program_application_id = i_prg_appl_id,
program_id = i_prg_id,
program_update_date = sysdate,
operation_cost = nvl(operation_cost,0) + nvl(opseq_rec.ADJ_VALUE,0),
status = 1
where organization_id = i_org_id
and wip_entity_id = opseq_rec.WIP_ENTITY
and operation_seq_num = opseq_rec.OP_SEQ_NUM;
o_err_msg := 'CSTPOYLD.cost_update_adjustment: (' || to_char(l_stmt_num) || '):' || substrb(SQLERRM,1,200);
END cost_update_adjustment;