[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(MAX(cost_group_id),-1)
INTO l_txn_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_txn_org_id;
SELECT NVL(MAX(cost_group_id),-1)
INTO l_txfr_cost_group_id
FROM cst_cost_group_assignments
WHERE organization_id = i_txfr_org_id;
SELECT nvl(MMT.fob_point, MIP.fob_point)
INTO l_fob_point
FROM mtl_interorg_parameters MIP, mtl_material_transactions MMT
WHERE MIP.from_organization_id = i_txn_org_id
AND MIP.to_organization_id = i_txfr_org_id
AND MMT.transaction_id = i_txn_id;
SELECT nvl(MMT.fob_point, MIP.fob_point)
INTO l_fob_point
FROM mtl_interorg_parameters MIP, mtl_material_transactions MMT
WHERE MIP.from_organization_id = i_txfr_org_id
AND MIP.to_organization_id = i_txn_org_id
AND MMT.transaction_id = i_txn_id;
/* Update mmt with quantity in the master org um */
/* Bug 6751847 fix to prevent update from both shipping as well as
receiving cost groups,to avoid lock and hold when run in
parallel for multiple Cost Groups */
UPDATE mtl_material_transactions
SET periodic_primary_quantity = l_converted_txn_qty
WHERE transaction_id = i_txn_id
AND organization_id = i_txn_org_id
AND EXISTS ( SELECT 'x'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = i_cost_group_id
AND ccga.organization_id = i_txn_org_id);
| Insert into mptcd if necessary.
| Five cases :
| - Interorg Accross CG with ownership change (group 1).
| Calculate the txn cost using data from mtl_transaction_accounts,
| , then insert that into mptcd elementally.
| - PO related txns (group 1 and i_txn_src_type_id = 1 and i_txn_action_id <> 6).
| Obtain the transaction cost from po table, then insert that into mptcd.
| i_txn_action_id of 6 is an ownership txfr, which has no acquisition cost.
| Therefore these fall into "Other cost owned txns" below.
| - Periodic Cost Update.
| Update the period id into current period id. This value might be null
| when it's inserted by the form, because the period might not yet
| open at the time of form insertion.
| - Other cost owned txns (group 1).
| Copy from mctcd into mptcd.
| - Other txns
| No insertion into mptcd is done.
---------------------------------------------------------------------------*/
IF (i_process_group = 1 AND i_txn_action_id IN (3,12,21,15)) THEN -- INVCONV sikhanna 22 (removed)
l_stmt_num := 60;
Select mmt.transaction_cost into l_txn_cost
from mtl_material_transactions mmt
where mmt.transaction_id = i_txn_id ;
select nvl(rt.dropship_type_code, 3), mmt.transaction_cost, rt.parent_transaction_id
into l_dropship_type_code, l_txn_cost, l_parent_rcv_txn_id
from rcv_transactions rt, mtl_material_transactions mmt
where mmt.transaction_id = i_txn_id
and mmt.rcv_transaction_id = rt.transaction_id;
select dropship_type_code, transaction_id
into l_parent_ds_type_code, l_grandpa_rcv_txn_id
from rcv_transactions
where (transaction_type = 'RECEIVE' -- 'MATCH' cannot be parent because
and parent_transaction_id = -1)
OR transaction_type = 'MATCH'-- there won't be one in a true dropship
start with transaction_id = l_parent_rcv_txn_id
connect by transaction_id = prior parent_transaction_id;
select unit_price
into l_txn_cost
from rcv_accounting_events
where rcv_transaction_id = l_grandpa_rcv_txn_id;
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
1,
1,
i_item_id,
l_txn_cost/l_um_rate,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id);
UPDATE mtl_pac_txn_cost_details
SET pac_period_id = i_pac_period_id,
last_update_date = SYSDATE
WHERE transaction_id = i_txn_id
AND cost_type_id = i_cost_type_id
AND cost_group_id = i_cost_group_id;
SELECT RMA.reference_line_id
INTO l_so_line_id
FROM mtl_material_transactions MMT,
oe_order_lines_all RMA
WHERE MMT.transaction_id = i_txn_id
AND RMA.line_id = MMT.trx_source_line_id;
SELECT count(*)
INTO l_prior_period_shipment
FROM cst_pac_periods cpp,
oe_order_lines_all SO
WHERE cpp.pac_period_id = i_pac_period_id
AND cpp.period_start_date > SO.ACTUAL_SHIPMENT_DATE
AND SO.line_id = l_so_line_id;
SELECT count(*)
INTO l_already_processed
FROM cst_revenue_cogs_match_lines
WHERE pac_cost_type_id = i_cost_type_id
AND cogs_om_line_id = l_so_line_id;
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
SELECT i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
MPACD.cost_element_id,
MPACD.level_type,
i_item_id,
(SUM(MMT.primary_quantity*MPACD.actual_cost)/SUM(MMT.primary_quantity))/l_um_rate,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id
FROM mtl_material_transactions MMT,
mtl_pac_actual_cost_details MPACD
WHERE MMT.transaction_action_id = 1
AND MMT.transaction_source_type_id = 2
AND MMT.organization_id = i_txn_org_id
AND MMT.inventory_item_id = i_item_id
AND MMT.trx_source_line_id = l_so_line_id
AND MPACD.transaction_id = MMT.transaction_id
AND MPACD.cost_type_id = i_cost_type_id
GROUP BY
MPACD.cost_element_id,
MPACD.level_type;
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
SELECT i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
MCACD.cost_element_id,
MCACD.level_type,
i_item_id,
(SUM(MMT.primary_quantity*MCACD.actual_cost)/SUM(MMT.primary_quantity))/l_um_rate,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id
FROM mtl_material_transactions MMT,
mtl_cst_actual_cost_details MCACD
WHERE MMT.transaction_action_id = 1
AND MMT.transaction_source_type_id = 2
AND MMT.organization_id = i_txn_org_id
AND MMT.inventory_item_id = i_item_id
AND MMT.trx_source_line_id = l_so_line_id
AND MCACD.transaction_id = MMT.transaction_id
GROUP BY
MCACD.cost_element_id,
MCACD.level_type;
SELECT count(1)
INTO l_already_processed
FROM mtl_pac_txn_cost_details
WHERE pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id
AND transaction_id = i_txn_id;
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
SELECT
transaction_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost/l_um_rate,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id
FROM
mtl_cst_txn_cost_details mctcd
WHERE
mctcd.transaction_id = i_txn_id;
CST_RevenueCogsMatch_PVT.Insert_PacSoIssue( p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_id => i_txn_id,
p_layer_id => l_cost_layer_id,
p_cost_type_id => i_cost_type_id,
p_cost_group_id => i_cost_group_id,
p_user_id => i_user_id,
p_login_id => i_login_id,
p_request_id => i_request_id,
p_pgm_app_id => i_prog_appl_id,
p_pgm_id => i_prog_id);
l_err_msg := 'Failure in procedure CST_RevenueCogsMatch_PVT.Insert_PacSoIssue()';
| to be inserted into MPTCD :
| 1. Direct Transfer - Rcv txn , processed by the Rcv CG
| 2. FOB Shipment - Shipment txn , processed by the Rcv CG
| 3. FOB Receipt - Rcv txn , processed by the Rcv CG
|
| Algorithm followed for inserting into MPTCD:
| |-IF (FOB SHIPMENT/RECEIPT and Internal Order with Transfer Pricing) THEN
| | |- Create MPTCD (cost element 1) using the transfer price from MMT
| | | stamped on shipment txn)
| |
| |-ELSE --FOB or DIRECT interorg
| | |-IF (both send and receiving CGs implemented in same LE/CT) THEN
| | | |- IF (PACP iterative process was used) THEN
| | | | |- create MPTCD with sending CG cost from CPIC
| | | | |- if sending CG cost from CPIC not available, default to prior period cost
| | | | |- if no prior period cost exists, default to perpetual shipment cost
| | | |- ELSE --PACP not used
| | | | |- create MPTCD w/sending CG PWAC cost in prior period
| | | | |- if no prior period cost exists, default to perpetual shipment cost
| | | |- END IF;
SELECT decode(i_txn_action_id,
21, transaction_id,
22, transaction_id, -- INVCONV sikhanna
transfer_transaction_id),
transaction_source_type_id,
/*decode(i_txn_action_id,
3, decode(sign(i_txn_qty),
1,transfer_transaction_id,
transaction_id),
transaction_id),*/
nvl(transportation_cost,0) -- INVCONV sikhanna
INTO l_shipment_txn_id,
l_txn_src_type_id,
/*l_txn_id,*/
l_trp_cost -- INVCONV
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
SELECT nvl(mmt.fob_point,mip.fob_point)
INTO l_fob_point
FROM mtl_material_transactions mmt,
mtl_interorg_parameters mip
WHERE mmt.transaction_id = i_txn_id
AND mip.from_organization_id = l_from_org
AND mip.to_organization_id = l_to_org;
SELECT nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'), 0)
INTO l_tprice_option
FROM mtl_intercompany_parameters MIP
WHERE fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1
AND MIP.flow_type = 1
AND MIP.ship_organization_id = (SELECT to_number(HOI.org_information3)
FROM hr_organization_information HOI
WHERE HOI.organization_id = l_from_org
AND HOI.org_information_context = 'Accounting Information')
AND MIP.sell_organization_id = (SELECT to_number(HOI2.org_information3)
FROM hr_organization_information HOI2
WHERE HOI2.organization_id = l_to_org
AND HOI2.org_information_context = 'Accounting Information');
SELECT MOD(SUM(DECODE(process_enabled_flag,'Y',1,2)), 2)
INTO l_pd_txfr_ind
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = i_txn_org_id
OR MP.ORGANIZATION_ID = i_txfr_org_id;
SELECT transfer_price
INTO l_txfr_price
FROM mtl_material_transactions
WHERE transaction_id = l_shipment_txn_id;
/* Insert transfer price into MPTCD with cost element 1 */
CSTPPINV.insert_elemental_cost(
i_pac_period_id => i_pac_period_id,
i_cost_type_id => i_cost_type_id,
i_cost_group_id => i_cost_group_id,
i_txn_id => i_txn_id,
i_item_id => i_item_id,
i_cost_element_id => 1,
i_level_type => 1,
i_cost => l_txfr_price * l_conv_rate,
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg
);
SELECT legal_entity
INTO l_txfr_legal_entity
FROM cst_cost_groups
WHERE cost_group_id = decode(i_txn_action_id,
21,decode(l_fob_point,
1,i_txn_cost_group_id,
i_txfr_cost_group_id),
i_txfr_cost_group_id);
SELECT count(*)
INTO l_same_le_ct
FROM cst_le_cost_types
WHERE legal_entity = l_txfr_legal_entity
AND cost_type_id = i_cost_type_id;
SELECT TRANSFER_COST_FLAG
INTO l_transfer_cost_flag
FROM CST_LE_COST_TYPES
WHERE LEGAL_ENTITY = i_legal_entity
AND COST_TYPE_ID = i_cost_type_id;
/* There is no PACP cost, so no costs were inserted into MPTCD.
Insert perpetual shipment cost instead. */
CSTPPINV.get_perp_ship_cost(
i_pac_period_id => i_pac_period_id,
i_cost_type_id => i_cost_type_id,
i_cost_group_id => i_cost_group_id,
i_txn_id => i_txn_id,
i_mta_txn_id => l_shipment_txn_id,
i_item_id => i_item_id,
i_from_org => l_from_org,
i_conv_rate => l_conv_rate,
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
x_perp_ship_cost => l_sending_cg_cost,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg
);
/* There is no prior period cost, so no costs were inserted into MPTCD.
Insert perpetual shipment cost instead. */
CSTPPINV.get_perp_ship_cost(
i_pac_period_id => i_pac_period_id,
i_cost_type_id => i_cost_type_id,
i_cost_group_id => i_cost_group_id,
i_txn_id => i_txn_id,
i_mta_txn_id => l_shipment_txn_id,
i_item_id => i_item_id,
i_from_org => l_from_org,
i_conv_rate => l_conv_rate,
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
x_perp_ship_cost => l_sending_cg_cost,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg
);
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES(
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
1,
1,
i_item_id,
i_transfer_price_pd,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id);
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES (
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
2,
1,
i_item_id,
l_trp_cost / abs(i_txn_qty) * decode(i_txn_action_id,
15, 1,
22, 1,
12, decode(l_fob_point,
2, 1,
l_conv_rate),
l_conv_rate),
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id);
SELECT nvl(transfer_percentage,0),
nvl(transfer_cost,0),
nvl(transportation_cost,0),
primary_quantity
INTO l_txfr_percent,
l_txfr_cost,
l_trp_cost,
l_shipment_txn_qty
FROM mtl_material_transactions
WHERE transaction_id = i_source_txn_id;
| This routine inserts the specified incremental cost
| (parameter i_incr_cost) into the given cost element of MCTCD if
| the element does not yet exist, or updates the cost element
| with the incremental cost if the cost element already exists.
|
| The i_incr_cost is assumed to already be in the proper currency.
|
---------------------------------------------------------------------------*/
PROCEDURE add_elemental_cost(
i_pac_period_id IN NUMBER,
i_cost_type_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_id IN NUMBER,
i_item_id IN NUMBER,
i_cost_element_id IN NUMBER,
i_level_type IN NUMBER,
i_incr_cost IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)
IS
l_stmt_num NUMBER := 0;
SELECT count(*)
INTO l_elem_cnt
FROM mtl_pac_txn_cost_details
WHERE transaction_id = I_TXN_ID
AND pac_period_id = I_PAC_PERIOD_ID
AND cost_type_id = I_COST_TYPE_ID
AND cost_group_id = I_COST_GROUP_ID
AND cost_element_id = i_cost_element_id
AND level_type = i_level_type;
UPDATE MTL_PAC_TXN_COST_DETAILS
SET transaction_cost = (transaction_cost + i_incr_cost)
WHERE transaction_id = I_TXN_ID
AND pac_period_id = I_PAC_PERIOD_ID
AND cost_type_id = I_COST_TYPE_ID
AND cost_group_id = I_COST_GROUP_ID
AND cost_element_id = i_cost_element_id
AND level_type = i_level_type;
CSTPPINV.insert_elemental_cost(
i_pac_period_id => i_pac_period_id,
i_cost_type_id => i_cost_type_id,
i_cost_group_id => i_cost_group_id,
i_txn_id => i_txn_id,
i_item_id => i_item_id,
i_cost_element_id => i_cost_element_id,
i_level_type => i_level_type,
i_cost => i_incr_cost,
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg
);
| Procedure insert_elemental_cost()
|
| This routine inserts the specified cost
| (parameter i_incr_cost) into the given cost element of MCTCD.
|
| The i_cost is assumed to already be in the proper currency.
|
---------------------------------------------------------------------------*/
PROCEDURE insert_elemental_cost(
i_pac_period_id IN NUMBER,
i_cost_type_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_id IN NUMBER,
i_item_id IN NUMBER,
i_cost_element_id IN NUMBER,
i_level_type IN NUMBER,
i_cost IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)
IS
l_stmt_num NUMBER := 0;
l_api_name CONSTANT VARCHAR2(30) := 'CSTPPINV.insert_elemental_cost';
INSERT INTO MTL_PAC_TXN_COST_DETAILS (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login )
VALUES(
I_TXN_ID,
I_PAC_PERIOD_ID,
I_COST_TYPE_ID,
I_COST_GROUP_ID,
i_cost_element_id,
i_level_type,
i_item_id,
(i_cost),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_login_id);
o_err_msg := 'CSTPPINV.insert_elemental_cost (' || to_char(l_stmt_num) ||
'): ' || substr(SQLERRM,1,200);
END insert_elemental_cost;
| It inserts the PACP cost of i_cost_source_cost_group from CPIC into MPTCD
| plus any transfer and transportation charges as appropriate.
|
| Returns x_pacp_used = -1, if no costs were inserted into MPTCD.
---------------------------------------------------------------------------*/
PROCEDURE get_pacp_cost(
i_cost_source_cost_group IN NUMBER,
i_pac_period_id IN NUMBER,
i_cost_type_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_id IN NUMBER,
i_item_id IN NUMBER,
i_conv_rate IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
x_pacp_used OUT NOCOPY NUMBER,
x_pacp_cost OUT NOCOPY NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)
IS
l_stmt_num NUMBER := 0;
SELECT NVL(CPIC.item_cost,0)
INTO l_pacp_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_item_id
AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
AND CPIC.PAC_PERIOD_ID = i_pac_period_id;
INSERT INTO MTL_PAC_TXN_COST_DETAILS (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login )
SELECT
I_TXN_ID,
I_PAC_PERIOD_ID,
I_COST_TYPE_ID,
I_COST_GROUP_ID,
CPICD.cost_element_id,
CPICD.level_type,
i_item_id,
(CPICD.item_cost * i_conv_rate),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_login_id
FROM CST_PAC_ITEM_COSTS CPIC,
CST_PAC_ITEM_COST_DETAILS CPICD
WHERE CPICD.COST_LAYER_ID = CPIC.COST_LAYER_ID
AND CPIC.INVENTORY_ITEM_ID = i_item_id
AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
AND CPIC.PAC_PERIOD_ID = I_PAC_PERIOD_ID;
| It inserts the prior period cost from CPIC into MPTCD
| plus any transfer and transportation charges as appropriate.
|
| Returns x_prev_period_id = -1, if no costs were inserted into MPTCD.
---------------------------------------------------------------------------*/
PROCEDURE get_prev_period_cost(
i_legal_entity IN NUMBER,
i_cost_source_cost_group IN NUMBER,
i_pac_period_id IN NUMBER,
i_cost_type_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_id IN NUMBER,
i_item_id IN NUMBER,
i_conv_rate IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
x_prev_period_id OUT NOCOPY NUMBER,
x_prev_period_cost OUT NOCOPY NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)
IS
l_stmt_num NUMBER := 0;
SELECT nvl(max(cpp.pac_period_id), -1)
INTO l_prev_period_id
FROM cst_pac_periods cpp
WHERE cpp.cost_type_id = i_cost_type_id
AND cpp.legal_entity = i_legal_entity
AND cpp.pac_period_id < i_pac_period_id;
SELECT count(*)
INTO l_prev_period_pwac_cnt
FROM cst_pac_item_costs cpic
WHERE CPIC.INVENTORY_ITEM_ID = i_item_id
AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
INSERT INTO MTL_PAC_TXN_COST_DETAILS (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
SELECT
I_TXN_ID,
I_PAC_PERIOD_ID,
I_COST_TYPE_ID,
I_COST_GROUP_ID,
CPICD.cost_element_id,
CPICD.level_type,
i_item_id,
(CPICD.item_cost * i_conv_rate),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_login_id
FROM CST_PAC_ITEM_COSTS CPIC,
CST_PAC_ITEM_COST_DETAILS CPICD
WHERE CPICD.COST_LAYER_ID = CPIC.COST_LAYER_ID
AND CPIC.INVENTORY_ITEM_ID = i_item_id
AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
SELECT nvl(CPIC.item_cost,0)
INTO l_prev_period_pwac_cost
FROM CST_PAC_ITEM_COSTS CPIC
WHERE CPIC.INVENTORY_ITEM_ID = i_item_id
AND CPIC.COST_GROUP_ID = i_cost_source_cost_group
AND CPIC.PAC_PERIOD_ID = l_prev_period_id;
| Given the shipment transaction id, this procedure inserts the perpetual
| shipment cost from MTA into MPTCD,
| plus any transfer and transportation charges as appropriate.
|
---------------------------------------------------------------------------*/
PROCEDURE get_perp_ship_cost(
i_pac_period_id IN NUMBER,
i_cost_type_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_txn_id IN NUMBER,
i_mta_txn_id IN NUMBER,
i_item_id IN NUMBER,
i_from_org IN NUMBER,
i_conv_rate IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
x_perp_ship_cost OUT NOCOPY NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)
IS
l_stmt_num NUMBER := 0;
SELECT primary_quantity
INTO l_mta_txn_qty
FROM mtl_material_transactions
WHERE transaction_id = i_mta_txn_id;
/* Select the perpetual accounted value for the sending org's credit
to On-hand (either Inventory or Expense accounting line type). */
BEGIN
SELECT nvl(SUM(ABS(NVL(base_transaction_value, 0)))/abs(l_mta_txn_qty),0)
INTO l_perp_ship_cost
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = i_mta_txn_id
and mta.organization_id = i_from_org
and mta.accounting_line_type IN (1,2)
and mta.base_transaction_value < 0;
/* Insert all cost elements */
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
SELECT
i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
NVL(mta.cost_element_id, 1),
1,
i_item_id,
i_conv_rate*abs(mta.base_transaction_value/abs(l_mta_txn_qty)),
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id
FROM
mtl_transaction_accounts mta
WHERE
mta.transaction_id = i_mta_txn_id
and mta.organization_id = i_from_org
and mta.accounting_line_type IN (1,2)
and mta.base_transaction_value < 0;
/* Insert 0 Material Cost into MPTCD only. */
CSTPPINV.insert_elemental_cost(
i_pac_period_id => i_pac_period_id,
i_cost_type_id => i_cost_type_id,
i_cost_group_id => i_cost_group_id,
i_txn_id => i_txn_id,
i_item_id => i_item_id,
i_cost_element_id => 1,
i_level_type => 1,
i_cost => 0,
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_err_num,
o_err_code => l_err_code,
o_err_msg => l_err_msg
);
SELECT org_information1
INTO l_snd_sob_id
FROM hr_organization_information
WHERE organization_id = i_from_org
and org_information_context = 'Accounting Information';
SELECT currency_code
INTO l_snd_curr
FROM gl_sets_of_books
WHERE set_of_books_id = l_snd_sob_id;
SELECT org_information1
INTO l_rcv_sob_id
FROM hr_organization_information
WHERE organization_id = i_to_org
and org_information_context = 'Accounting Information';
SELECT currency_code
INTO l_rcv_curr
FROM gl_sets_of_books
WHERE set_of_books_id = l_rcv_sob_id;
SELECT currency_conversion_type, TRUNC(transaction_date)
INTO l_curr_type, l_txn_date
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
SELECT primary_uom_code
INTO o_from_uom
FROM mtl_system_items
WHERE organization_id = i_from_org
AND inventory_item_id = i_item_id;
SELECT primary_uom_code
INTO o_to_uom
FROM mtl_system_items
WHERE organization_id = i_to_org
AND inventory_item_id = i_item_id;
| Consigned price update transaction
|
| This procedure is used to cost process the logical transactions.
|
| 19-Jul-03 Anju Creation
|-----------------------------------------------------------------------------*/
PROCEDURE cost_acct_events(
i_pac_period_id IN NUMBER,
i_legal_entity IN NUMBER,
i_cost_type_id IN NUMBER,
i_cost_group_id IN NUMBER,
i_cost_method IN NUMBER,
i_txn_id IN NUMBER,
i_item_id IN NUMBER,
i_txn_qty IN NUMBER,
i_txn_org_id IN NUMBER,
i_master_org_id IN NUMBER,
i_uom_control IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_request_id IN NUMBER,
i_prog_id IN NUMBER,
i_prog_appl_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
) IS
l_err_num NUMBER;
select nvl(parent_transaction_id, -1),
transaction_source_type_id,
transaction_action_id
into l_parent_transaction_id,
l_txn_src_type_id,
l_txn_action_id
from mtl_material_transactions
where transaction_id = i_txn_id;
select organization_id,
nvl(logical_transaction, 3)
into l_parent_organization_id,
l_logical_transaction
from mtl_material_transactions
where transaction_id = l_parent_transaction_id;
/* update mmt.periodic_quantity */
/* Bug 6751847 fix: to prevent execution twice from both
shipping and receiving cost groups,to avoid lock when run in
parallel for multiple CGs */
UPDATE mtl_material_transactions
SET periodic_primary_quantity = (select mmt2.periodic_primary_quantity
from mtl_material_transactions mmt2
where mmt2.transaction_id =
l_parent_transaction_id)
WHERE transaction_id = i_txn_id
AND organization_id = i_txn_org_id
AND EXISTS (SELECT 'x'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = i_cost_group_id
AND ccga.organization_id = i_txn_org_id);
/* insert into mpacd */
l_stmt_num := 40;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_layer_id,
cost_element_id,
level_type,
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,
actual_cost,
prior_cost,
prior_buy_cost,
prior_make_cost,
new_cost,
new_buy_cost,
new_make_cost,
insertion_flag,
user_entered,
transaction_costed_date)
SELECT i_txn_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
cost_layer_id,
cost_element_id,
level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
inventory_item_id,
actual_cost,
prior_cost,
prior_buy_cost,
prior_make_cost,
new_cost,
new_buy_cost,
new_make_cost,
insertion_flag,
user_entered,
sysdate
FROM mtl_pac_actual_cost_details
WHERE transaction_id = l_parent_transaction_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id;
/* Delete the MPACD row of the parent */
Delete from mtl_pac_actual_cost_details
where transaction_id = l_parent_transaction_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id;
/* Update mmt with quantity in the master org um */
/* BUG 6751847 fix: to prevent execution twice
for both shipping and receiving cost group, to avoid
lock when run in parallel for CGs */
UPDATE mtl_material_transactions
SET periodic_primary_quantity = i_txn_qty
WHERE transaction_id = i_txn_id
AND organization_id = i_txn_org_id
AND EXISTS (SELECT 'x'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = i_cost_group_id
AND ccga.organization_id = i_txn_org_id);
INSERT INTO mtl_pac_txn_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost,
last_update_date,
last_updated_by,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
SELECT
transaction_id,
i_pac_period_id,
i_cost_type_id,
i_cost_group_id,
cost_element_id,
level_type,
inventory_item_id,
transaction_cost/l_um_rate,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE,
i_login_id
FROM
mtl_cst_txn_cost_details mctcd
WHERE
mctcd.transaction_id = i_txn_id;
INSERT INTO mtl_pac_actual_cost_details (
transaction_id,
pac_period_id,
cost_type_id,
cost_group_id,
cost_layer_id,
cost_element_id,
level_type,
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,
actual_cost,
insertion_flag,
user_entered,
transaction_costed_date)
SELECT i_txn_id,
i_pac_period_id,
cost_type_id,
i_cost_group_id,
-1,
cost_element_id,
level_type,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
inventory_item_id,
transaction_cost,
'Y',
'N',
sysdate
FROM mtl_pac_txn_cost_details
WHERE transaction_id = i_txn_id
AND pac_period_id = i_pac_period_id
AND cost_group_id = i_cost_group_id;
CST_RevenueCogsMatch_PVT.Insert_PacSoIssue( p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_id => i_txn_id,
p_layer_id => l_cost_layer_id,
p_cost_type_id => i_cost_type_id,
p_cost_group_id => i_cost_group_id,
p_user_id => i_user_id,
p_login_id => i_login_id,
p_request_id => i_request_id,
p_pgm_app_id => i_prog_appl_id,
p_pgm_id => i_prog_id);
l_err_msg := 'Failure in procedure CST_RevenueCogsMatch_PVT.Insert_PacSoIssue()';
SELECT inventory_asset_flag
INTO l_asset_item
FROM mtl_system_items
WHERE inventory_item_id = i_item_id AND organization_id = i_txn_org_id;
SELECT decode(asset_inventory,1,0,1)
INTO o_exp_flag
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = i_subinventory_code
AND organization_id = i_txn_org_id;
SELECT /*+ LEADING (mmt) */
/* Modified for fob stamping project */
mmt.transaction_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.primary_quantity,
mmt.organization_id,
nvl(mmt.transfer_organization_id,-1),
mmt.subinventory_code,
nvl(mmt.transfer_price,0) -- INVCONV
FROM
mtl_material_transactions mmt,
mtl_parameters mp --INVCONV sikhanna changes
WHERE
transaction_date between l_start_date AND l_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'N' --INVCONV sikhanna
AND NOT EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt.transaction_action_id = 3
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = i_cost_group_id
AND ccga1.organization_id = mmt.organization_id
AND mmt.primary_quantity > 0))
OR (mmt.transaction_action_id = 21
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt.organization_id
AND mip.to_organization_id = mmt.transfer_organization_id
AND NVL(mmt.fob_point,mip.fob_point) = 1
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = i_cost_group_id))
OR (mmt.transaction_action_id = 12
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt.transfer_organization_id
AND mip.to_organization_id = mmt.organization_id
AND NVL(mmt.fob_point,mip.fob_point) = 2
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = i_cost_group_id))
/* Logical Intransit Receipt for receiving organization cost group */ -- INVCONV sikhanna
OR (mmt.transaction_action_id = 15
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga0
WHERE ccga0.organization_id = mmt.organization_id
AND ccga0.cost_group_id = i_cost_group_id))
)
ORDER BY inventory_item_id;
CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
i_cost_group_id => i_cost_group_id,
i_txn_category => 3, /* Cost Owned Transactions */
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg);
CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id
,i_cost_group_id => i_cost_group_id
,i_txn_category => 3 /* cost owned transactions */
,i_user_id => i_user_id
,i_login_id => i_login_id
,i_request_id => i_request_id
,i_prog_id => i_prog_id
,i_prog_appl_id => i_prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
CSTPPWAC.update_cppb(i_pac_period_id => i_pac_period_id,
i_cost_group_id => i_cost_group_id,
i_txn_category => 3, /* Cost Owned Transactions */
i_low_level_code => -2,
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg);
fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV while insert or update CPPB');
SELECT /*+ LEADING (mmt) */
mmt.transaction_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.primary_quantity,
mmt.organization_id,
nvl(mmt.transfer_organization_id,-1),
mmt.subinventory_code,
nvl(mmt.transfer_price,0) -- INVCONV
FROM
mtl_material_transactions mmt,
mtl_parameters mp -- INCONV sikhanna
WHERE
transaction_date between l_start_date AND l_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'N'
AND ((transaction_action_id in (3,12,21)
AND EXISTS (SELECT 'EXISTS'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = i_cost_group_id
AND (ccga.organization_id = mmt.organization_id OR
ccga.organization_id = mmt.transfer_organization_id)))
/* Logical Intransit Shipment for shipping organization cost group */ -- INVCONV sikhanna
OR (mmt.transaction_action_id = 22
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga0
WHERE ccga0.organization_id = mmt.organization_id
AND ccga0.cost_group_id = i_cost_group_id)))
AND (transaction_action_id IN (3,12,21,22)
AND NOT EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt.transaction_action_id = 3
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = i_cost_group_id
AND ccga1.organization_id = mmt.organization_id
AND mmt.primary_quantity < 0))
OR (mmt.transaction_action_id = 21
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mmt.organization_id
AND ccga2.cost_group_id = i_cost_group_id))
OR (mmt.transaction_action_id = 12
AND EXISTS (
SELECT 'X'
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = mmt.transfer_organization_id
AND mip.to_organization_id = mmt.organization_id
AND (
(NVL(mmt.fob_point,mip.fob_point) = 1 AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = i_cost_group_id ))
Or (NVL(mmt.fob_point,mip.fob_point) = 2 AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments ccga3
WHERE ccga3.organization_id = mip.from_organization_id
AND ccga3.cost_group_id = i_cost_group_id )))))
/* Logical Intransit Shipment for shipping organization cost group */
OR (mmt.transaction_action_id = 22
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga0
WHERE ccga0.organization_id = mmt.organization_id
AND ccga0.cost_group_id = i_cost_group_id))
))
AND NOT EXISTS (
SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id)
ORDER BY inventory_item_id;
CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
i_cost_group_id => i_cost_group_id,
i_txn_category => 9, /* Cost Derived Transactions */
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg);
CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id
,i_cost_group_id => i_cost_group_id
,i_txn_category => 9 /* cost derived transactions */
,i_user_id => i_user_id
,i_login_id => i_login_id
,i_request_id => i_request_id
,i_prog_id => i_prog_id
,i_prog_appl_id => i_prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
CSTPPWAC.update_cppb(i_pac_period_id => i_pac_period_id,
i_cost_group_id => i_cost_group_id,
i_txn_category => 9, /* Cost Derived Transactions */
i_low_level_code => -1, /* No completions */
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg);
fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV while insert or update CPPB');
SELECT /*+ LEADING (mmt) */
mmt.transaction_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.primary_quantity,
mmt.organization_id,
nvl(mmt.transfer_organization_id,-1),
mmt.subinventory_code
FROM
mtl_material_transactions mmt
WHERE
transaction_date between l_start_date AND l_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
and transaction_action_id in (3,12,21)
AND EXISTS (SELECT 'EXISTS'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = i_cost_group_id
AND (ccga.organization_id = mmt.organization_id OR
ccga.organization_id = mmt.transfer_organization_id))
AND
(
(transaction_action_id IN (12,21)
OR (transaction_action_id = 3 AND primary_quantity < 0))
AND EXISTS (
SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id))
AND NOT EXISTS (
SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id)
union all
SELECT /*+ LEADING (mmt) */
mmt.transaction_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.inventory_item_id,
mmt.primary_quantity,
mmt.organization_id,
nvl(mmt.transfer_organization_id,-1),
mmt.subinventory_code
FROM
mtl_material_transactions mmt,
cst_cost_group_assignments ccga
WHERE
transaction_date between l_start_date AND l_end_date
AND transaction_action_id in (4,8,28,33,34,1,2,5,27) /* Added VMI Planning Transfer */
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND ccga.cost_group_id = i_cost_group_id
AND ccga.organization_id = mmt.organization_id
AND nvl(mmt.logical_transactions_created, 1) <> 2
AND nvl(mmt.logical_transaction, 3) <> 1
AND (transaction_action_id IN (4,8,33,34)
OR (transaction_action_id IN (2,5) AND primary_quantity < 0)
OR ( transaction_action_id = 28 AND
((transaction_source_type_id = 8 AND primary_quantity < 0)
OR
transaction_source_type_id <> 8))
OR (transaction_action_id in (1, 27)
AND transaction_source_type_id IN (3,6,13)
AND transaction_cost IS NULL)
OR (transaction_action_id in (1,27)
AND transaction_source_type_id NOT IN (1,3,6,13)) )
AND NOT EXISTS (
SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = i_pac_period_id
AND cpllc.cost_group_id = i_cost_group_id)
ORDER BY 4; /* order by inventory item id */
CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
i_cost_group_id => i_cost_group_id,
i_txn_category => 9, /* Cost Derived Transactions */
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg);
CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id,
i_cost_group_id => i_cost_group_id,
i_txn_category => 9, /* Cost Derived Transactions */
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg);
CSTPPWAC.insert_into_cppb(i_pac_period_id => i_pac_period_id
,i_cost_group_id => i_cost_group_id
,i_txn_category => 9 /* cost derived transactions */
,i_user_id => i_user_id
,i_login_id => i_login_id
,i_request_id => i_request_id
,i_prog_id => i_prog_id
,i_prog_appl_id => i_prog_appl_id
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
CSTPPWAC.update_cppb(i_pac_period_id => i_pac_period_id,
i_cost_group_id => i_cost_group_id,
i_txn_category => 9, /* Cost Derived Transactions */
i_low_level_code => -1, /* No completions */
i_user_id => i_user_id,
i_login_id => i_login_id,
i_request_id => i_request_id,
i_prog_id => i_prog_id,
i_prog_appl_id => i_prog_appl_id,
o_err_num => l_error_num,
o_err_code => l_error_code,
o_err_msg => l_error_msg);
fnd_file.put_line(fnd_file.log,'Errored out txns in CSTPPINV group2_trx while insert or update CPPB');