[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* been inserted into the Revenue/COGS matching data model (CRCML and *
* CCE). *
********************************************************************************/
IF ( p_phase = 1 ) THEN
--{BUG#7463298
IF p_ledger_id IS NOT NULL THEN
g_ledger_id := p_ledger_id;
Insert_SoIssues(p_request_id => l_request_id,
p_user_id => l_user_id,
p_login_id => l_login_id,
p_pgm_app_id => l_pgm_app_id,
p_pgm_id => l_pgm_id,
x_return_status => l_return_status);
insert_RmaReceipts(p_request_id => l_request_id,
p_user_id => l_user_id,
p_login_id => l_login_id,
p_pgm_app_id => l_pgm_app_id,
p_pgm_id => l_pgm_id,
x_return_status => l_return_status);
* Phase 2 - Process incoming revenue events and insert revenue recognition per *
* period by OM line into CRRL *
********************************************************************************/
IF ( p_phase = 2 ) THEN
-- convert input parameters to dates
l_low_date := fnd_date.canonical_to_date(p_low_date);
UPDATE cst_revenue_cogs_control
SET last_process_upto_date = l_high_date,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
request_id = l_request_id
WHERE control_id = l_control_id;
INSERT INTO cst_revenue_cogs_control (
CONTROL_ID,
LAST_PROCESS_UPTO_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
VALUES ( l_control_id,
l_high_date,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
l_request_id,
l_pgm_app_id,
l_pgm_id,
sysdate);
UPDATE cst_revenue_recognition_lines crrl
SET potentially_unmatched_flag = 'Y'
WHERE potentially_unmatched_flag = 'U'
AND ledger_id = p_ledger_id;
PROCEDURE Insert_SoIssues(
x_return_status OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_pgm_app_id IN NUMBER,
p_pgm_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_SoIssues';
SELECT mmt.trx_source_line_id cogs_om_line_id,
mmt.distribution_account_id cogs_acct_id,
mp.deferred_cogs_account def_cogs_acct_id,
mmt.transaction_id mmt_txn_id,
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_date,
mmt.cost_group_id,
(-1*mmt.primary_quantity) quantity
FROM mtl_material_transactions mmt,
mtl_parameters mp,
mtl_secondary_inventories msi,
mtl_system_items_b item
,cst_acct_info_v caiv --BUG#7463298
WHERE mmt.transaction_action_id in (1,7)
/* do not pick up physical SO issue in a drop shipment flow */
AND NVL(mmt.parent_transaction_id, -1) = DECODE(mmt.transaction_action_id,1,-1,mmt.parent_transaction_id)
AND mmt.transaction_type_id IN (33, 30)
AND mmt.transaction_source_type_id = 2
AND mmt.costed_flag = 'N'
AND mmt.COGS_RECOGNITION_PERCENT IS NULL
AND mmt.SO_ISSUE_ACCOUNT_TYPE = 2 /* deferred COGS */
AND mp.organization_id = mmt.organization_id
--{BUG#7463298
AND mp.organization_id = caiv.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'N' /*BUG#9306124 - Ristricted this qry for Discrete orgs*/
AND DECODE(g_ledger_id,-1
,caiv.ledger_id,g_ledger_id) = caiv.ledger_id
--}
AND mmt.subinventory_code = msi.secondary_inventory_name (+) /* Logical txn does not have sub code */
AND mmt.organization_id = msi.organization_id (+)
AND NVL(msi.asset_inventory,1) = 1
AND item.inventory_item_id = mmt.inventory_item_id
AND item.organization_id = mmt.organization_id
AND item.inventory_asset_flag = 'Y'
/* BUG#9306124 - Seperated query for Process orgs with this union(Start)*/
UNION ALL
SELECT mmt.trx_source_line_id cogs_om_line_id,
mmt.distribution_account_id cogs_acct_id,
mp.deferred_cogs_account def_cogs_acct_id,
mmt.transaction_id mmt_txn_id,
mmt.organization_id,
mmt.inventory_item_id,
mmt.transaction_date,
mmt.cost_group_id,
(-1*mmt.primary_quantity) quantity
FROM mtl_material_transactions mmt,
mtl_parameters mp,
mtl_secondary_inventories msi,
mtl_system_items_b item
,cst_acct_info_v caiv --BUG#7463298
WHERE mmt.transaction_action_id in (1,7)
/* do not pick up physical SO issue in a drop shipment flow */
AND NVL(mmt.parent_transaction_id, -1) = DECODE(mmt.transaction_action_id,1,-1,mmt.parent_transaction_id)
AND mmt.transaction_type_id IN (33, 30)
AND mmt.transaction_source_type_id = 2
AND mmt.opm_costed_flag IS NOT NULL
AND mmt.COGS_RECOGNITION_PERCENT IS NULL
AND mmt.SO_ISSUE_ACCOUNT_TYPE = 2 /* deferred COGS */
AND mp.organization_id = mmt.organization_id
--{BUG#7463298
AND mp.organization_id = caiv.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'Y' /*BUG#9306124 - Process org */
AND DECODE(g_ledger_id,-1
,caiv.ledger_id,g_ledger_id) = caiv.ledger_id
--}
AND mmt.subinventory_code = msi.secondary_inventory_name (+) /* Logical txn does not have sub code */
AND mmt.organization_id = msi.organization_id (+)
AND NVL(msi.asset_inventory,1) = 1
AND item.inventory_item_id = mmt.inventory_item_id
AND item.organization_id = mmt.organization_id
AND item.inventory_asset_flag = 'Y'
/* BUG#9306124 - Seperated query for Process orgs with this union (End)*/
;
SAVEPOINT Insert_SoIssues_PVT;
Insert_OneSoIssue(
p_api_version => 1.0,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_request_id => p_request_id,
p_pgm_app_id => p_pgm_app_id,
p_pgm_id => p_pgm_id,
x_return_status => l_return_status,
p_cogs_om_line_id => cv_so_issues.cogs_om_line_id,
p_cogs_acct_id => cv_so_issues.cogs_acct_id,
p_def_cogs_acct_id => cv_so_issues.def_cogs_acct_id,
p_mmt_txn_id => cv_so_issues.mmt_txn_id,
p_organization_id => cv_so_issues.organization_id,
p_item_id => cv_so_issues.inventory_item_id,
p_transaction_date => cv_so_issues.transaction_date,
p_cost_group_id => cv_so_issues.cost_group_id,
p_quantity => cv_so_issues.quantity
);
FND_MESSAGE.set_name('BOM', 'CST_FAILED_DEFCOGS_SO_INSERT');
l_api_message := 'Inserted '||to_char(l_so_count)||' sales order issues into CCE.';
ROLLBACK TO Insert_SoIssues_PVT;
,'Insert_SoIssues ('||to_char(l_stmt_num)||') : '||substr(SQLERRM,1,200));
FND_FILE.put_line(fnd_file.log, 'OTHERS EXCEPTION IN Insert_SoIssues:'||substrb(SQLERRM,1,250) );
END Insert_SoIssues;
PROCEDURE Insert_RmaReceipts(
x_return_status OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_pgm_app_id IN NUMBER,
p_pgm_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_RmaReceipts';
SELECT /* LEADING(mmt) */
mmt.trx_source_line_id rma_om_line_id,
ool.reference_line_id cogs_om_line_id,
mmt.transaction_id mmt_txn_id,
mmt.transaction_date transaction_date,
(-1*mmt.primary_quantity) event_quantity,
cce.parent_event_id prior_event_id,
cce.cogs_percentage cogs_percentage,
sum(cce.event_quantity) prior_event_quantity
FROM mtl_material_transactions mmt,
oe_order_lines_all ool,
cst_revenue_cogs_match_lines crcml,
cst_cogs_events cce,
mtl_system_items_b item
,cst_acct_info_v caiv --BUG#7463298
,mtl_parameters mp --BUG#9306124
WHERE mmt.transaction_source_type_id = 12
AND mmt.transaction_action_id in (26,27) -- UT: see if this forces index use, otherwise take it out since repeated below
AND mmt.costed_flag = 'N'
AND mmt.cogs_recognition_percent IS NULL
AND mmt.trx_source_line_id = ool.line_id -- this line and the next will cause this query to
AND ool.reference_line_id = crcml.cogs_om_line_id -- return rows only if crcml has a row for the orig SO
AND (
( mmt.transaction_action_id = 27
AND EXISTS
(SELECT NULL
FROM mtl_secondary_inventories msi
WHERE mmt.subinventory_code = msi.SECONDARY_INVENTORY_NAME
AND mmt.organization_id = msi.organization_id
AND msi.asset_inventory = 1)
-- Physical RMA should not have a logical RMA as child transaction
-- otherwise this drop ship return COGS is carried by the logical RMA
AND NOT EXISTS
(SELECT NULL
FROM mtl_material_transactions mmt_rma
WHERE mmt_rma.transaction_source_type_id = 12
AND mmt_rma.transaction_action_id = 26
AND mmt_rma.parent_transaction_id = mmt.transaction_id)
)
OR
( mmt.transaction_action_id = 26)
)
AND item.inventory_item_id = mmt.inventory_item_id
AND item.organization_id = mmt.organization_id
AND item.inventory_asset_flag = 'Y'
--{BUG#7463298
AND mp.organization_id = mmt.organization_id
AND mp.organization_id = caiv.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'N' /*BUG#9306124 - Ristricted this qry for Discrete orgs*/
AND DECODE(g_ledger_id,-1
,caiv.ledger_id,g_ledger_id) = caiv.ledger_id
--}
AND crcml.cogs_om_line_id = cce.cogs_om_line_id
AND crcml.pac_cost_type_id is NULL --BUG 14656876
AND cce.event_date <= mmt.transaction_date
AND NOT EXISTS (SELECT NULL
FROM cst_cogs_events
WHERE DECODE(event_type,3,TRUNC(event_date),event_date) <= mmt.transaction_date
AND cogs_om_line_id = crcml.cogs_om_line_id
AND prior_event_id = cce.parent_event_id)
GROUP BY cce.parent_event_id , ool.reference_line_id,
mmt.transaction_id , cce.cogs_percentage,
mmt.trx_source_line_id, mmt.organization_id,
mmt.inventory_item_id , mmt.transaction_date,
mmt.primary_quantity
/* BUG#9306124 - Seperated query for Process orgs with this union (Start)*/
UNION ALL
SELECT /* LEADING(mmt) */
mmt.trx_source_line_id rma_om_line_id,
ool.reference_line_id cogs_om_line_id,
mmt.transaction_id mmt_txn_id,
mmt.transaction_date transaction_date,
(-1*mmt.primary_quantity) event_quantity,
cce.parent_event_id prior_event_id,
cce.cogs_percentage cogs_percentage,
sum(cce.event_quantity) prior_event_quantity
FROM mtl_material_transactions mmt,
oe_order_lines_all ool,
cst_revenue_cogs_match_lines crcml,
cst_cogs_events cce,
mtl_system_items_b item
,cst_acct_info_v caiv --BUG#7463298
,mtl_parameters mp --BUG#9306124
WHERE mmt.transaction_source_type_id = 12
AND mmt.transaction_action_id in (26,27) -- UT: see if this forces index use, otherwise take it out since repeated below
AND mmt.opm_costed_flag IS NOT NULL
AND mmt.cogs_recognition_percent IS NULL
AND mmt.trx_source_line_id = ool.line_id -- this line and the next will cause this query to
AND ool.reference_line_id = crcml.cogs_om_line_id -- return rows only if crcml has a row for the orig SO
AND (
( mmt.transaction_action_id = 27
AND EXISTS
(SELECT NULL
FROM mtl_secondary_inventories msi
WHERE mmt.subinventory_code = msi.SECONDARY_INVENTORY_NAME
AND mmt.organization_id = msi.organization_id
AND msi.asset_inventory = 1)
-- Physical RMA should not have a logical RMA as child transaction
-- otherwise this drop ship return COGS is carried by the logical RMA
AND NOT EXISTS
(SELECT NULL
FROM mtl_material_transactions mmt_rma
WHERE mmt_rma.transaction_source_type_id = 12
AND mmt_rma.transaction_action_id = 26
AND mmt_rma.parent_transaction_id = mmt.transaction_id)
)
OR
( mmt.transaction_action_id = 26)
)
AND item.inventory_item_id = mmt.inventory_item_id
AND item.organization_id = mmt.organization_id
AND item.inventory_asset_flag = 'Y'
--{BUG#7463298
AND mp.organization_id = mmt.organization_id
AND mp.organization_id = caiv.organization_id
AND nvl(mp.process_enabled_flag,'N') = 'Y' /*BUG#9306124 - Process org */
AND DECODE(g_ledger_id,-1
,caiv.ledger_id,g_ledger_id) = caiv.ledger_id
--}
AND crcml.cogs_om_line_id = cce.cogs_om_line_id
AND cce.event_date <= mmt.transaction_date
AND NOT EXISTS (SELECT NULL
FROM cst_cogs_events
WHERE DECODE(event_type,3,TRUNC(event_date),event_date) <= mmt.transaction_date
AND cogs_om_line_id = crcml.cogs_om_line_id
AND prior_event_id = cce.parent_event_id)
GROUP BY cce.parent_event_id , ool.reference_line_id,
mmt.transaction_id , cce.cogs_percentage,
mmt.trx_source_line_id, mmt.organization_id,
mmt.inventory_item_id , mmt.transaction_date,
mmt.primary_quantity
/* BUG#9306124 - Seperated query for Process orgs with this union (End)*/
ORDER BY cogs_om_line_id, transaction_date;
debug('Insert_RmaReceipts+');
SAVEPOINT Insert_RmaReceipts_PVT;
/* For each RMA Receipt, insert 2 rows in cce - one goes in
* the string of events and the other is the quantity adjustment
* to that parent event.
*/
l_stmt_num := 20;
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
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_cogs_events_s.nextval,
l_cogs_om_line_id_tbl(i),
l_txn_date_tbl(i),
NULL, -- Quantity placeholder - no MMT transaction
l_prior_percent_tbl(i),
l_prior_percent_tbl(i),
l_prior_event_id,
RMA_RECEIPT_PLACEHOLDER,
l_rma_om_line_id_tbl(i),
l_prior_event_qty_tbl(i),
NULL, -- This event is a quantity placeholder, thus is never costed
cst_cogs_events_s.currval,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate)
RETURNING event_id INTO l_parent_event_id_tbl(i);
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
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_cogs_events_s.nextval,
l_cogs_om_line_id_tbl(i),
l_txn_date_tbl(i),
l_mmt_txn_id_tbl(i),
l_prior_percent_tbl(i), -- COGS percentage
l_prior_percent_tbl(i), -- prior COGS percentage
NULL,
RMA_RECEIPT,
l_rma_om_line_id_tbl(i),
l_event_quantity_tbl(i),
'N',
l_parent_event_id_tbl(i),
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate);
UPDATE mtl_material_transactions
SET cogs_recognition_percent = l_prior_percent_tbl(i),
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_pgm_app_id,
program_id = p_pgm_id,
program_update_date = sysdate
WHERE transaction_id = l_mmt_txn_id_tbl(i);
* CCE / MMT directly if the parent is uncosted, or inserting new
* events in CCE and MMT in the case that the parent is costed.
*/
-- First insert this new event into the linked list by setting the prior event ID of the
-- next event to this new one.
l_stmt_num := 50;
UPDATE cst_cogs_events
SET PRIOR_EVENT_ID = l_parent_event_id_tbl(i),
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id
WHERE cogs_om_line_id = l_cogs_om_line_id_tbl(i)
AND prior_event_id = l_prior_event_id_tbl(i)
AND DECODE(event_type,3,TRUNC(event_date),event_date) > l_txn_date_tbl(i)
AND l_marker(i) = 1;
INSERT INTO cst_cogs_qty_adj_events_temp (
adj_event_id,
adj_mmt_txn_id,
adj_cogs_om_line_id,
adj_rma_om_line_id,
adj_event_date,
adj_new_cogs_percentage,
adj_prior_cogs_percentage,
adj_event_quantity,
parent_event_id,
inventory_item_id,
primary_uom,
organization_id,
cost_group_id,
cogs_acct_id,
opm_org_flag,
acct_period_id
)
SELECT cst_cogs_events_s.nextval,
decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
NULL),
cce.cogs_om_line_id,
l_rma_om_line_id_tbl(i),
cce.event_date,
cogs_percentage, -- could also use cce.prior_cogs_percentage
prior_cogs_percentage,
l_event_quantity_tbl(i),
event_id,
crcml.inventory_item_id,
msi.primary_uom_code,
crcml.organization_id,
crcml.cost_group_id,
crcml.cogs_acct_id,
nvl(mp.process_enabled_flag,'N'),
oap.acct_period_id -- acct period ID, I should store this in CCE so I don't keep having to go back to OAP
FROM cst_cogs_events cce,
cst_revenue_cogs_match_lines crcml,
mtl_parameters mp,
org_acct_periods oap,
cst_acct_info_v caiv,
mtl_system_items msi
WHERE cce.cogs_om_line_id = l_cogs_om_line_id_tbl(i)
AND TRUNC(cce.event_date) > l_txn_date_tbl(i)
AND cce.event_type IN (3,4)
AND cce.event_id = cce.parent_event_id
AND cce.cogs_om_line_id = crcml.cogs_om_line_id
AND crcml.pac_cost_type_id IS NULL
AND crcml.organization_id = mp.organization_id
AND crcml.organization_id = oap.organization_id
AND crcml.inventory_item_id = msi.inventory_item_id
AND crcml.organization_id = msi.organization_id
AND crcml.organization_id = caiv.organization_id
AND inv_le_timezone_pub.get_le_day_time_for_ou(cce.event_date, caiv.operating_unit)
BETWEEN oap.period_start_date AND oap.schedule_close_date+EndDateNum;
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT adj_event_id,
adj_cogs_om_line_id,
adj_event_date,
adj_mmt_txn_id,
adj_new_cogs_percentage,
adj_prior_cogs_percentage,
NULL,
COGS_REC_QTY_ADJUSTMENT,
adj_rma_om_line_id,
adj_event_quantity,
decode(adj_mmt_txn_id, NULL, NULL, 'N'),
parent_event_id,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate
FROM cst_cogs_qty_adj_events_temp;
INSERT INTO MTL_COGS_RECOGNITION_TEMP (
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
COST_GROUP_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,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
OPM_COSTED_FLAG,
ACTUAL_COST,
TRANSACTION_COST,
PRIOR_COST,
NEW_COST,
TRX_SOURCE_LINE_ID,
RMA_LINE_ID,
LOGICAL_TRANSACTION,
COGS_RECOGNITION_PERCENT)
SELECT
ccqa.adj_mmt_txn_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
ccqa.inventory_item_id,
ccqa.organization_id,
ccqa.cost_group_id,
10008,
36,
2,
mso.sales_order_id,
mso.segment1,
ccqa.adj_event_quantity,
ccqa.primary_uom, -- Txn UOM
ccqa.adj_event_quantity,
ccqa.adj_event_date,
ccqa.acct_period_id,
ccqa.cogs_acct_id,
decode(ccqa.opm_org_flag, 'N', 'N', NULL),
decode(ccqa.opm_org_flag, 'Y', 'N', NULL),
NULL, -- Actual Cost
NULL, -- Txn Cost
NULL, -- Prior Cost
NULL, -- New Cost
ccqa.adj_cogs_om_line_id,
ccqa.adj_rma_om_line_id, -- RMA Line ID
1, -- Logical Txn
ccqa.adj_new_cogs_percentage
FROM cst_cogs_qty_adj_events_temp ccqa,
mtl_sales_orders mso,
oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott
WHERE ool.line_id = ccqa.adj_cogs_om_line_id
AND ool.header_id = ooh.header_id
AND TO_CHAR(ooh.order_number) = mso.segment1
AND ooh.order_type_id = ott.transaction_type_id
AND ott.name = mso.segment2
AND ott.language = (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B')
AND mso.segment3 = l_source_code
AND ccqa.adj_mmt_txn_id IS NOT NULL;
FND_MESSAGE.set_name('BOM', 'CST_FAILED_COGSREC_MMT_INSERT');
commit; --delete from cst_cogs_qty_adj_events_temp;
SAVEPOINT Insert_RmaReceipts_PVT;
l_api_message := 'Inserted '||to_char(l_rma_count)||' RMA Receipts into CCE.';
debug('Insert_RmaReceipts-');
ROLLBACK TO Insert_RmaReceipts_PVT;
ROLLBACK TO Insert_RmaReceipts_PVT;
,'Insert_RmaReceipts ('||to_char(l_stmt_num)||') : '||substr(SQLERRM,1,200));
FND_FILE.put_line(fnd_file.log, 'OTHERS EXCEPTION IN Insert_RmaReceipts:'||substrb(SQLERRM,1,250) );
END Insert_RmaReceipts;
SELECT DISTINCT set_of_books_id
FROM gl_sets_of_books
WHERE set_of_books_id = NVL(p_ledger_id,set_of_books_id);
SELECT crcml.cogs_om_line_id cogs_om_line_id,
crrl.last_event_date + EndDateNum + clt.number_1 last_event_date,
crrl.revenue_recognition_percent new_percentage,
cce.cogs_percentage prior_percentage,
cce.parent_event_id prior_event_id,
sum(cce.event_quantity) event_quantity,
crcml.cogs_acct_id cogs_acct_id,
crcml.inventory_item_id inventory_item_id,
msi.primary_uom_code primary_uom_code,
crcml.organization_id organization_id,
crcml.cost_group_id cost_group_id,
nvl(mp.process_enabled_flag,'N') opm_org,
(select oap.acct_period_id
from org_acct_periods oap
where oap.organization_id = crcml.organization_id
and oap.period_name = gps.period_name) acct_period_id,
clt.number_1 date_offset
--{BUG#6809034 use CCE event_date if mmt trx after rev recog
,MAX(mmt.transaction_id) mmt_transaction_id
,MAX(mmt.transaction_date) mmt_transaction_date
,MAX(mmt.acct_period_id) mmt_period_id
--}
,MAX(crrl.rowid) l_rowid
FROM cst_revenue_cogs_match_lines crcml,
cst_revenue_recognition_lines crrl,
cst_cogs_events cce,
mtl_parameters mp,
gl_period_statuses gps,
cst_lists_temp clt,
mtl_system_items msi
--{BUG#6809034
,mtl_material_transactions mmt
--}
WHERE crrl.ledger_id = p_sob_id
AND crrl.acct_period_num = p_process_acct_period_num
AND crrl.potentially_unmatched_flag = 'Y' -- Indexed column should substantially reduce the rows
--{BUG#7387575
AND DECODE(p_neg_req_id,NULL,
NVL(crrl.request_id,-99),p_neg_req_id) = NVL(crrl.request_id,-99)
--}
AND crrl.revenue_om_line_id = crcml.revenue_om_line_id
AND crcml.organization_id = mp.organization_id
AND crcml.organization_id = msi.organization_id
AND crcml.inventory_item_id = msi.inventory_item_id
AND crcml.pac_cost_type_id IS NULL
AND gps.application_id = 101
AND gps.set_of_books_id = p_sob_id
AND gps.effective_period_num = p_cogs_acct_period_num
AND cce.event_date <= gps.end_date +EndDateNum + clt.number_1 -- gps1.end_date +EndDateNum + clt.number_1
AND clt.VARCHAR_1 = 'TIMEZONE'
AND clt.list_id = crcml.organization_id
AND crcml.cogs_om_line_id = cce.cogs_om_line_id
--{BUG#6809034
AND cce.mmt_transaction_id = mmt.transaction_id(+)
-- retrieve cce which are not prior to any events -- sum of these event quantity is the quantity to recognize the cost
AND NOT EXISTS (select 'X'
from cst_cogs_events
where event_date <= gps.end_date + EndDateNum + clt.number_1 -- gps1.end_date + EndDateNum + clt.number_1
and cogs_om_line_id = crcml.cogs_om_line_id
and prior_event_id = cce.parent_event_id)
--AND cce.cogs_percentage <> crrl.revenue_recognition_percent -- AR should only send the revenue info once
GROUP BY cce.cogs_percentage,
cce.parent_event_id,
crcml.cogs_om_line_id,
crcml.cogs_acct_id,
crcml.inventory_item_id,
crcml.cost_group_id,
msi.primary_uom_code,
crrl.last_event_date,
crrl.revenue_recognition_percent,
clt.number_1,
crcml.organization_id,
mp.process_enabled_flag,
gps.period_name;
SELECT DISTINCT crrl.acct_period_num
FROM cst_revenue_recognition_lines crrl
WHERE crrl.ledger_id = p_ledger_id
AND crrl.potentially_unmatched_flag = 'Y'
ORDER BY crrl.acct_period_num ASC;
DELETE FROM cst_lists_temp WHERE VARCHAR_1 = 'TIMEZONE';
INSERT
INTO cst_lists_temp (
list_id,
number_1,
varchar_1
)
SELECT organization_id,
inv_le_timezone_pub.get_server_day_time_for_le(
l_dummy_date,
legal_entity
) - l_dummy_date,
'TIMEZONE'
FROM ( SELECT
DISTINCT caiv.organization_id,
caiv.legal_entity
FROM cst_revenue_recognition_lines crrl,
cst_revenue_cogs_match_lines crcml,
cst_acct_info_v caiv
WHERE crrl.potentially_unmatched_flag='Y'
AND crcml.revenue_om_line_id =crrl.revenue_om_line_id
AND DECODE(p_neg_req_id,NULL,NVL(crrl.request_id,-99),p_neg_req_id) = NVL(crrl.request_id,-99)
-- BUG#7387575
AND crcml.pac_cost_type_id IS NULL
AND caiv.organization_id =crcml.organization_id
AND caiv.ledger_id =p_ledger_id
);
SELECT MAX(glp.effective_period_num)
,MIN(glp.effective_period_num)
INTO l_max_period_num --l_rev_cogs_period_num
,l_min_period_num
FROM gl_period_statuses glp
,cst_revenue_recognition_lines crrl
,cst_revenue_cogs_match_lines crcml
,cst_cogs_events cce
WHERE crrl.acct_period_num = l_per_num_tab(j)
AND crrl.potentially_unmatched_flag = 'Y'
AND crrl.revenue_om_line_id = crcml.revenue_om_line_id
AND crrl.ledger_id = p_ledger_id
AND crcml.cogs_om_line_id = cce.cogs_om_line_id
AND cce.event_type = 1
AND glp.application_id = 101
AND glp.ledger_id = p_ledger_id
AND glp.adjustment_period_flag = 'N'
AND DECODE(SIGN(cce.event_date - crrl.last_event_date)
, -1, crrl.last_event_date, TRUNC(cce.event_date))
BETWEEN glp.start_date
AND glp.end_date;
SELECT closing_status,
period_name,
adjustment_period_flag --BUG7438582
INTO l_gl_period_status,
l_period_name,
l_gl_adj_flag
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = l_sob
AND effective_period_num = l_max_period_num; --l_per_num_tab(j); the period to check is the cogs period
SELECT min(effective_period_num)
INTO l_cogs_acct_period_num
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = l_sob --l_sob.set_of_books_id
AND effective_period_num > l_max_period_num --l_per_num_tab(j)
AND (closing_status = 'O'
--{BUG#7438582 UTSTAR - Allow Cogs Reco in Futur not adjustment periods
OR (closing_status = 'F' AND adjustment_period_flag = 'N')
);
SELECT start_date
INTO l_alternate_event_date
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = l_sob --l_sob.set_of_books_id
AND effective_period_num = l_cogs_acct_period_num;
-- We will use l_cogs_acct_period_num for selection of the COGS to match ONLY
--
debug('l_cogs_acct_period_num:'||l_cogs_acct_period_num);
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
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_cogs_events_s.nextval,
l_cogs_om_line_id_tbl(i),
--BUG#6809034 -- As JAN-XX is opened the transaction_date is AR recognition event date
--But as the bug is only on the material transaction, this COGS event event_date remains
--untouch to avoid breaking the current logic based on the CCE,CRCML,CRRL for CCE creation
--If the problem is hitting the accounting unproper such as to much COGS recognized or not enough
--this can be the starting point
--nvl(l_alternate_event_date+EndDateNum+l_date_offset_tbl(i),l_event_date_tbl(i)),
CST_RevenueCogsMatch_PVT.acct_date(l_mmt_transaction_date(i),l_event_date_tbl(i)),
--l_event_date_tbl(i),
--
mtl_material_transactions_s.nextval,
l_new_percentage_tbl(i),
l_prior_percentage_tbl(i),
l_prior_event_id_tbl(i),
COGS_RECOGNITION_EVENT,
NULL, -- event OM line ID
l_event_quantity_tbl(i),
'N',
cst_cogs_events_s.currval,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate
)
RETURNING event_id, mmt_transaction_id
BULK COLLECT INTO l_new_event_id_tbl, l_new_mmt_txn_id_tbl;
INSERT INTO mtl_cogs_recognition_temp (
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
COST_GROUP_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
OPM_COSTED_FLAG,
ACTUAL_COST,
TRANSACTION_COST,
PRIOR_COST,
NEW_COST,
TRX_SOURCE_LINE_ID,
RMA_LINE_ID,
LOGICAL_TRANSACTION,
COGS_RECOGNITION_PERCENT,
transaction_set_id -- BUG#7387575
)
VALUES (
l_new_mmt_txn_id_tbl(i),
sysdate,
p_user_id,
sysdate,
p_user_id,
l_item_id_tbl(i),
l_organization_id_tbl(i),
l_cost_group_id_tbl(i),
10008,
36,
2,
l_event_quantity_tbl(i),
l_primary_uom_tbl(i), -- Txn UOM
l_event_quantity_tbl(i),
--BUG#6809034
--nvl(l_alternate_event_date+EndDateNum+l_date_offset_tbl(i),l_event_date_tbl(i)),
CST_RevenueCogsMatch_PVT.acct_date(l_mmt_transaction_date(i),l_event_date_tbl(i)),
-- This inventory accounting period will be reconciled in ensure_mmt_per_and_date
-- We can use the routine inv_period_id to correct here but performance should be better in ensure_mmt_per_and_date
l_acct_period_id_tbl(i),
--CST_RevenueCogsMatch_PVT.inv_period_id(l_mmt_transaction_date(i),l_event_date_tbl(i),p_ledger_id,l_organization_id_tbl(i),l_acct_period_id_tbl(i)),
--}
l_cogs_acct_id_tbl(i),
decode(l_opm_org_flg_tbl(i), 'N', 'N', NULL),
decode(l_opm_org_flg_tbl(i), 'Y', 'N', NULL),
NULL, -- Actual Cost
NULL, -- Txn Cost
NULL, -- Prior Cost
NULL, -- New Cost
l_cogs_om_line_id_tbl(i),
NULL, -- RMA Line ID
1, -- Logical Txn
l_new_percentage_tbl(i)
,p_neg_req_id --BUG#7387575
);
UPDATE cst_cogs_events
SET PRIOR_COGS_PERCENTAGE = l_new_percentage_tbl(i),
PRIOR_EVENT_ID = l_new_event_id_tbl(i),
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id
WHERE cogs_om_line_id = l_cogs_om_line_id_tbl(i)
AND prior_event_id = l_prior_event_id_tbl(i)
AND event_id <> l_new_event_id_tbl(i)
AND l_opm_org_flg_tbl(i) = 'N'
AND costed = 'N';
INSERT INTO cst_cogs_pct_adj_events_temp (
adj_event_id,
adj_mmt_txn_id,
adj_cogs_om_line_id,
adj_event_date,
adj_new_cogs_percentage,
adj_prior_cogs_percentage,
adj_prior_event_id,
adj_event_quantity,
ftr_event_id,
inventory_item_id,
primary_uom,
organization_id,
cost_group_id,
cogs_acct_id,
opm_org_flag,
acct_period_id
)
SELECT cst_cogs_events_s.nextval,
mtl_material_transactions_s.nextval,
cogs_om_line_id,
event_date,
l_prior_percentage_tbl(i), -- could also use cce.prior_cogs_percentage
l_new_percentage_tbl(i),
l_new_event_id_tbl(i),
event_quantity,
event_id,
l_item_id_tbl(i),
l_primary_uom_tbl(i),
l_organization_id_tbl(i),
l_cost_group_id_tbl(i),
l_cogs_acct_id_tbl(i),
l_opm_org_flg_tbl(i),
oap.acct_period_id
FROM cst_cogs_events cce,
org_acct_periods oap
WHERE cogs_om_line_id = l_cogs_om_line_id_tbl(i)
AND prior_event_id = l_prior_event_id_tbl(i)
AND event_id <> l_new_event_id_tbl(i)
AND cce.event_date
BETWEEN oap.period_start_date + l_date_offset_tbl(i)
AND oap.schedule_close_date + EndDateNum + l_date_offset_tbl(i)
AND oap.organization_id = l_organization_id_tbl(i);
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT adj_event_id,
adj_cogs_om_line_id,
adj_event_date,
adj_mmt_txn_id,
adj_new_cogs_percentage,
adj_prior_cogs_percentage,
adj_prior_event_id,
COGS_REC_PERCENT_ADJUSTMENT,
NULL,
adj_event_quantity,
'N',
adj_event_id,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate
FROM cst_cogs_pct_adj_events_temp;
INSERT INTO MTL_COGS_RECOGNITION_TEMP (
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
COST_GROUP_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_QUANTITY,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
OPM_COSTED_FLAG,
ACTUAL_COST,
TRANSACTION_COST,
PRIOR_COST,
NEW_COST,
TRX_SOURCE_LINE_ID,
LOGICAL_TRANSACTION,
COGS_RECOGNITION_PERCENT
,transaction_set_id --BUG#7387575
)
SELECT
adj_mmt_txn_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
inventory_item_id,
organization_id,
cost_group_id,
10008,
36,
2,
adj_event_quantity,
primary_uom, -- Txn UOM
adj_event_quantity,
adj_event_date,
acct_period_id,
cogs_acct_id,
decode(opm_org_flag, 'N', 'N', NULL),
decode(opm_org_flag, 'Y', 'N', NULL),
NULL, -- Actual Cost
NULL, -- Txn Cost
NULL, -- Prior Cost
NULL, -- New Cost
adj_cogs_om_line_id,
1, -- Logical Txn
adj_new_cogs_percentage
,p_neg_req_id --BUG#7387575
FROM cst_cogs_pct_adj_events_temp;
UPDATE mtl_cogs_recognition_temp mcr
SET (transaction_source_id, transaction_source_name) = (
SELECT mkts.sales_order_id, mkts.segment1
FROM mtl_sales_orders mkts,
oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott
WHERE ool.line_id = mcr.trx_source_line_id
AND ool.header_id = ooh.header_id
AND to_char(ooh.order_number) = mkts.segment1
AND ooh.order_type_id = ott.transaction_type_id
AND ott.name = mkts.segment2
AND ott.language = (select language_code
from fnd_languages
where installed_flag = 'B')
AND mkts.segment3 = l_source_code);
FND_MESSAGE.set_name('BOM', 'CST_FAILED_COGSREC_MMT_INSERT');
UPDATE (
SELECT cce.prior_event_id,
cba.adj_event_id
FROM cst_cogs_events cce,
cst_cogs_pct_adj_events_temp cba
WHERE cce.event_id = cba.ftr_event_id)
SET prior_event_id = adj_event_id;
UPDATE cst_revenue_recognition_lines
SET potentially_unmatched_flag = NULL,
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_pgm_app_id,
program_id = p_pgm_id,
program_update_date = SYSDATE
WHERE ROWID = l_upd_rowid_tab(i);
COMMIT; --also deletes from cst_cogs_pct_adj_events_temp;
UPDATE cst_revenue_recognition_lines
SET potentially_unmatched_flag = 'I',
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_pgm_app_id,
program_id = p_pgm_id,
program_update_date = SYSDATE
WHERE ledger_id = l_sob
AND acct_period_num = l_per_num_tab(j)
AND potentially_unmatched_flag = 'Y';
'Ledger.PerNum.InsertedEvents = '||l_sob||'.'|| --to_char(l_sob.set_of_books_id)||'.'||
l_cogs_acct_period_num||'.'||l_cce_count);
commit; -- Need to save this update as well
l_api_message := 'Inserted '||to_char(l_cce_count)||' new COGS Recognition Events into CCE.';
PROCEDURE Insert_OneSoIssue(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_pgm_app_id IN NUMBER,
p_pgm_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_cogs_om_line_id IN NUMBER,
p_cogs_acct_id IN NUMBER,
p_def_cogs_acct_id IN NUMBER,
p_mmt_txn_id IN NUMBER,
p_organization_id IN NUMBER,
p_item_id IN NUMBER,
p_transaction_date IN DATE,
p_cost_group_id IN NUMBER,
p_quantity IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_OneSoIssue';
debug('Insert_OneSoIssue_PVT+');
SAVEPOINT Insert_OneSoIssue_PVT;
SELECT decode(fpg.multi_org_flag,'Y',TO_NUMBER(hoi.org_information3),TO_NUMBER(NULL)),
TO_NUMBER(hoi.org_information1)
INTO l_operating_unit_id,
l_ledger_id
FROM hr_organization_information hoi,
fnd_product_groups fpg
WHERE hoi.organization_id = p_organization_id
AND hoi.org_information_context = 'Accounting Information';
INSERT INTO cst_revenue_cogs_match_lines (
cogs_om_line_id,
revenue_om_line_id,
cogs_acct_id,
deferred_cogs_acct_id,
sales_order_issue_date,
organization_id,
inventory_item_id,
operating_unit_id,
ledger_id,
cost_group_id,
-- WHO COLUMNS
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (
p_cogs_om_line_id,
l_revenue_om_line_id,
p_cogs_acct_id,
p_def_cogs_acct_id,
p_transaction_date,
p_organization_id,
p_item_id,
l_operating_unit_id,
l_ledger_id,
p_cost_group_id,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate
);
SELECT event_id
INTO l_parent_cce_id
FROM cst_cogs_events
WHERE cogs_om_line_id = p_cogs_om_line_id
AND parent_event_id = event_id
AND event_type = 1;
UPDATE cst_revenue_recognition_lines
SET potentially_unmatched_flag = 'Y',
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id
WHERE revenue_om_line_id = l_revenue_om_line_id;
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
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_cogs_events_s.nextval,
p_cogs_om_line_id,
p_transaction_date,
p_mmt_txn_id,
0, -- COGS %
0, -- prior COGS %
SO_ISSUE,
p_cogs_om_line_id,
p_quantity,
'N',
nvl(l_parent_cce_id,cst_cogs_events_s.currval),
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate
);
UPDATE mtl_material_transactions
SET cogs_recognition_percent = 0,
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_pgm_app_id,
program_id = p_pgm_id,
program_update_date = sysdate
WHERE transaction_id = p_mmt_txn_id;
debug('Insert_OneSoIssue-');
ROLLBACK TO Insert_OneSoIssue_PVT; /*Bug 7384398*/
ROLLBACK TO Insert_OneSoIssue_PVT;
,'Insert_OneSoIssue ('||to_char(l_stmt_num)||') : '||substr(SQLERRM,1,200));
FND_FILE.put_line(fnd_file.log, 'OTHERS EXCEPTION IN Insert_OneSoIssue:'||substrb(SQLERRM,1,250) );
END Insert_OneSoIssue;
PROCEDURE Insert_OneRmaReceipt(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_pgm_app_id IN NUMBER,
p_pgm_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rma_om_line_id IN NUMBER,
p_cogs_om_line_id IN NUMBER,
p_mmt_txn_id IN NUMBER,
p_organization_id IN NUMBER,
p_item_id IN NUMBER,
p_transaction_date IN DATE,
p_quantity IN NUMBER,
x_event_id OUT NOCOPY NUMBER,
x_cogs_percentage OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_OneRmaReceipt';
debug('Insert_OneRmaReceipt_PVT+');
SAVEPOINT Insert_OneRmaReceipt_PVT;
SELECT cce.parent_event_id prior_event_id,
cce.cogs_percentage,
sum(cce.event_quantity) prior_event_quantity
INTO l_prior_event_id,
l_cogs_percentage,
l_prior_event_quantity
FROM cst_cogs_events cce
WHERE p_cogs_om_line_id = cce.cogs_om_line_id
AND cce.event_date <= p_transaction_date
AND cce.parent_event_id NOT IN (SELECT prior_event_id
FROM cst_cogs_events
WHERE event_date <= p_transaction_date
AND cogs_om_line_id = p_cogs_om_line_id--changed <= to = for bug 13243263
AND prior_event_id IS NOT NULL)
GROUP BY cce.parent_event_id,
cce.cogs_percentage;
/* For an RMA Receipt, insert 2 rows in cce - one goes in
* the string of events and the other is the quantity adjustment
* to that parent event.
*/
l_stmt_num := 20;
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
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_cogs_events_s.nextval,
p_cogs_om_line_id,
p_transaction_date,
NULL, -- Quantity Placeholder event - no corresponding MMT txn
l_cogs_percentage,
l_cogs_percentage,
l_prior_event_id,
RMA_RECEIPT_PLACEHOLDER,
p_rma_om_line_id,
l_prior_event_quantity,
NULL, -- RMA quantity placeholders are not costed
cst_cogs_events_s.currval,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate)
RETURNING event_id INTO l_parent_event_id;
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
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_cogs_events_s.nextval,
p_cogs_om_line_id,
p_transaction_date,
p_mmt_txn_id,
l_cogs_percentage,
l_cogs_percentage,
NULL,
RMA_RECEIPT,
p_rma_om_line_id,
p_quantity,
'N',
l_parent_event_id,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate)
RETURNING event_id INTO x_event_id;
UPDATE mtl_material_transactions
SET cogs_recognition_percent = l_cogs_percentage,
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id
WHERE transaction_id = p_mmt_txn_id;
* CCE / MMT directly if the parent is uncosted, or inserting new
* events in CCE and MMT in the case that the parent is costed.
*/
-- First insert this new event into the linked list by setting the prior event ID of the
-- next event to this new one.
l_stmt_num := 50;
UPDATE cst_cogs_events
SET PRIOR_EVENT_ID = l_parent_event_id,
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id
WHERE cogs_om_line_id = p_cogs_om_line_id
AND prior_event_id = l_prior_event_id
AND event_date > p_transaction_date;
INSERT INTO cst_cogs_qty_adj_events_temp (
adj_event_id,
adj_mmt_txn_id,
adj_cogs_om_line_id,
adj_rma_om_line_id,
adj_event_date,
adj_new_cogs_percentage,
adj_prior_cogs_percentage,
adj_event_quantity,
parent_event_id,
inventory_item_id,
primary_uom,
organization_id,
cost_group_id,
cogs_acct_id,
opm_org_flag,
acct_period_id
)
SELECT cst_cogs_events_s.nextval,
decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
NULL),
cce.cogs_om_line_id,
p_rma_om_line_id,
cce.event_date,
cogs_percentage, -- could also use cce.prior_cogs_percentage
prior_cogs_percentage,
p_quantity,
cce.event_id,
crcml.inventory_item_id,
msi.primary_uom_code,
crcml.organization_id,
crcml.cost_group_id,
crcml.cogs_acct_id,
nvl(mp.process_enabled_flag,'N'),
oap.acct_period_id
FROM cst_cogs_events cce,
cst_revenue_cogs_match_lines crcml,
mtl_parameters mp,
org_acct_periods oap,
cst_acct_info_v caiv,
mtl_system_items msi
WHERE cce.cogs_om_line_id = p_cogs_om_line_id
AND cce.event_date > p_transaction_date
AND cce.event_id = cce.parent_event_id
AND cce.cogs_om_line_id = crcml.cogs_om_line_id
AND crcml.pac_cost_type_id IS NULL
AND crcml.organization_id = mp.organization_id
AND crcml.organization_id = msi.organization_id
AND crcml.inventory_item_id = msi.inventory_item_id
AND crcml.organization_id = oap.organization_id
AND crcml.organization_id = caiv.organization_id
AND inv_le_timezone_pub.get_le_day_time_for_ou(cce.event_date, caiv.operating_unit)
BETWEEN oap.period_start_date AND oap.schedule_close_date+EndDateNum;
INSERT INTO cst_cogs_events (
event_id,
cogs_om_line_id,
event_date,
mmt_transaction_id,
cogs_percentage,
prior_cogs_percentage,
prior_event_id,
event_type,
event_om_line_id,
event_quantity,
costed,
parent_event_id,
-- WHO COLUMNS
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT adj_event_id,
adj_cogs_om_line_id,
adj_event_date,
adj_mmt_txn_id,
adj_new_cogs_percentage,
adj_prior_cogs_percentage,
NULL,
COGS_REC_QTY_ADJUSTMENT,
adj_rma_om_line_id,
adj_event_quantity,
decode(adj_mmt_txn_id, NULL, NULL, 'N'),
parent_event_id,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate
FROM cst_cogs_qty_adj_events_temp;
INSERT INTO mtl_cogs_recognition_temp (
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
COST_GROUP_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,
DISTRIBUTION_ACCOUNT_ID,
COSTED_FLAG,
OPM_COSTED_FLAG,
ACTUAL_COST,
TRANSACTION_COST,
PRIOR_COST,
NEW_COST,
TRX_SOURCE_LINE_ID,
RMA_LINE_ID,
LOGICAL_TRANSACTION,
COGS_RECOGNITION_PERCENT)
SELECT
ccqa.adj_mmt_txn_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
ccqa.inventory_item_id,
ccqa.organization_id,
ccqa.cost_group_id,
10008,
36,
2,
mso.sales_order_id,
mso.segment1,
ccqa.adj_event_quantity,
ccqa.primary_uom, -- Txn UOM
ccqa.adj_event_quantity,
ccqa.adj_event_date,
ccqa.acct_period_id,
ccqa.cogs_acct_id,
decode(ccqa.opm_org_flag, 'N', 'N', NULL),
decode(ccqa.opm_org_flag, 'Y', 'N', NULL),
NULL, -- Actual Cost
NULL, -- Txn Cost
NULL, -- Prior Cost
NULL, -- New Cost
ccqa.adj_cogs_om_line_id,
ccqa.adj_rma_om_line_id, -- RMA Line ID
1, -- Logical Txn
ccqa.adj_new_cogs_percentage
FROM cst_cogs_qty_adj_events_temp ccqa,
mtl_sales_orders mso,
oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott
WHERE ool.line_id = ccqa.adj_cogs_om_line_id
AND ool.header_id = ooh.header_id
AND TO_CHAR(ooh.order_number) = mso.segment1
AND ooh.order_type_id = ott.transaction_type_id
AND ott.name = mso.segment2
AND ott.language = (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B')
AND mso.segment3 = l_source_code
--{BUG#6909721
-- Ensure the place holder COGS event is not inserted in MMT as not effect on Cost and Accounting
AND ccqa.adj_mmt_txn_id IS NOT NULL;
FND_MESSAGE.set_name('BOM', 'CST_FAILED_COGSREC_MMT_INSERT');
DELETE FROM cst_cogs_qty_adj_events_temp;
DELETE FROM mtl_cogs_recognition_temp;
debug('Insert_OneRmaReceipt-');
ROLLBACK TO Insert_OneRmaReceipt_PVT;
ROLLBACK TO Insert_OneRmaReceipt_PVT;
,'Insert_OneRmaReceipt '||to_char(l_stmt_num)||' : '||substr(SQLERRM,1,200));
FND_FILE.put_line(fnd_file.log, 'OTHERS EXCEPTION IN Insert_OneRmaReceipt:'||substrb(SQLERRM,1,250) );
END Insert_OneRmaReceipt;
UPDATE cst_revenue_cogs_match_lines crcml
SET unit_material_cost = decode(original_shipped_qty, NULL, p_unit_material_cost,
((unit_material_cost*original_shipped_qty) +
(p_unit_material_cost*p_txn_quantity)) /
(original_shipped_qty + p_txn_quantity)),
unit_moh_cost = decode(original_shipped_qty, NULL, p_unit_moh_cost,
((unit_moh_cost*original_shipped_qty) +
(p_unit_moh_cost*p_txn_quantity)) /
(original_shipped_qty + p_txn_quantity)),
unit_resource_cost = decode(original_shipped_qty, NULL, p_unit_resource_cost,
((unit_resource_cost*original_shipped_qty) +
(p_unit_resource_cost*p_txn_quantity)) /
(original_shipped_qty + p_txn_quantity)),
unit_op_cost = decode(original_shipped_qty, NULL, p_unit_op_cost,
((unit_op_cost*original_shipped_qty) +
(p_unit_op_cost*p_txn_quantity)) /
(original_shipped_qty + p_txn_quantity)),
unit_overhead_cost = decode(original_shipped_qty, NULL, p_unit_overhead_cost,
((unit_overhead_cost*original_shipped_qty) +
(p_unit_overhead_cost*p_txn_quantity)) /
(original_shipped_qty + p_txn_quantity)),
original_shipped_qty = nvl(original_shipped_qty,0) + p_txn_quantity,
unit_cost = decode(original_shipped_qty, NULL, p_unit_cost,
((unit_cost*original_shipped_qty) +
(p_unit_cost*p_txn_quantity)) /
(original_shipped_qty + p_txn_quantity)),
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_pgm_app_id,
program_id = p_pgm_id,
program_update_date = sysdate
WHERE crcml.cogs_om_line_id = p_cogs_om_line_id
AND nvl(pac_cost_type_id,0) = nvl(p_pac_cost_type_id,0);
/* Insert the RMA Receipt into the Revenue / COGS Matching data model *
* retrieving the current cogs percentage in the process. */
Insert_OneRmaReceipt(
p_api_version => 1,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_request_id => p_req_id,
p_pgm_app_id => p_prg_appl_id,
p_pgm_id => p_prg_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rma_om_line_id => p_rma_om_line_id,
p_cogs_om_line_id => p_cogs_om_line_id,
p_mmt_txn_id => p_transaction_id,
p_organization_id => p_organization_id,
p_item_id => p_item_id,
p_transaction_date => p_txn_date,
p_quantity => p_txn_quantity,
x_event_id => l_rma_cce_id,
x_cogs_percentage => l_cogs_percentage
);
FND_MESSAGE.set_name('BOM', 'CST_FAILED_DEFCOGS_RMA_INSERT');
SELECT unit_material_cost,
unit_moh_cost,
unit_resource_cost,
unit_op_cost,
unit_overhead_cost,
unit_cost,
deferred_cogs_acct_id,
cogs_acct_id
INTO l_elemental_cost(1),
l_elemental_cost(2),
l_elemental_cost(3),
l_elemental_cost(4),
l_elemental_cost(5),
l_unit_cost,
l_def_cogs_acct_id,
l_cogs_acct_id
FROM cst_revenue_cogs_match_lines crcml
WHERE cogs_om_line_id = p_cogs_om_line_id
AND pac_cost_type_id IS NULL;
CSTPACDP.insert_account(p_organization_id, p_transaction_id, p_item_id, -1 * l_cogs_credit_amount,
p_txn_quantity, l_cogs_acct_id, p_sob_id, COGS_LINE_TYPE,
i, NULL,
p_txn_date, p_txn_src_id, p_src_type_id,
p_pri_curr, p_alt_curr, p_conv_date, p_conv_rate, p_conv_type,
1,p_user_id, p_login_id, p_req_id, p_prg_appl_id,p_prg_id,
l_err_num, l_err_code, l_err_msg);
CSTPACDP.insert_account(p_organization_id, p_transaction_id, p_item_id, -1 * l_dcogs_credit_amount,
p_txn_quantity, l_def_cogs_acct_id, p_sob_id, DEF_COGS_LINE_TYPE,
i, NULL,
p_txn_date, p_txn_src_id, p_src_type_id,
p_pri_curr, p_alt_curr, p_conv_date, p_conv_rate, p_conv_type,
1,p_user_id, p_login_id, p_req_id, p_prg_appl_id,p_prg_id,
l_err_num, l_err_code, l_err_msg);
CSTPACDP.insert_account(p_organization_id, p_transaction_id, p_item_id, 0,
p_txn_quantity, l_cogs_acct_id, p_sob_id, COGS_LINE_TYPE,
1, NULL,
p_txn_date, p_txn_src_id, p_src_type_id,
p_pri_curr, p_alt_curr, p_conv_date, p_conv_rate, p_conv_type,
1,p_user_id, p_login_id, p_req_id, p_prg_appl_id,p_prg_id,
l_err_num, l_err_code, l_err_msg);
CSTPACDP.insert_account(p_organization_id, p_transaction_id, p_item_id, 0,
p_txn_quantity, l_def_cogs_acct_id, p_sob_id, DEF_COGS_LINE_TYPE,
1, NULL,
p_txn_date, p_txn_src_id, p_src_type_id,
p_pri_curr, p_alt_curr, p_conv_date, p_conv_rate, p_conv_type,
1,p_user_id, p_login_id, p_req_id, p_prg_appl_id,p_prg_id,
l_err_num, l_err_code, l_err_msg);
UPDATE cst_cogs_events
SET costed = NULL
WHERE mmt_transaction_id = p_transaction_id;
UPDATE cst_cogs_events
SET costed = NULL
WHERE mmt_transaction_id = p_transaction_id
RETURNING cogs_percentage,
prior_cogs_percentage
INTO l_new_percentage,
l_prior_percentage;
SELECT unit_material_cost,
unit_moh_cost,
unit_resource_cost,
unit_op_cost,
unit_overhead_cost,
cogs_acct_id,
deferred_cogs_acct_id
INTO l_elemental_cost(1),
l_elemental_cost(2),
l_elemental_cost(3),
l_elemental_cost(4),
l_elemental_cost(5),
l_cogs_acct_id,
l_def_cogs_acct_id
FROM cst_revenue_cogs_match_lines crcml
WHERE cogs_om_line_id = p_cogs_om_line_id
AND pac_cost_type_id IS NULL;
CSTPACDP.insert_account(p_organization_id, p_transaction_id, p_item_id, l_adjustment_amount,
p_txn_quantity, l_cogs_acct_id, p_sob_id, COGS_LINE_TYPE,
i, NULL,
p_txn_date, p_txn_src_id, p_src_type_id,
p_pri_curr, p_alt_curr, p_conv_date, p_conv_rate, p_conv_type,
1,p_user_id, p_login_id, p_req_id, p_prg_appl_id,p_prg_id,
l_err_num, l_err_code, l_err_msg);
CSTPACDP.insert_account(p_organization_id, p_transaction_id, p_item_id, -1 * l_adjustment_amount,
-1 * p_txn_quantity, l_def_cogs_acct_id, p_sob_id, DEF_COGS_LINE_TYPE,
i, NULL,
p_txn_date, p_txn_src_id, p_src_type_id,
p_pri_curr, p_alt_curr, p_conv_date, p_conv_rate, p_conv_type,
1,p_user_id, p_login_id, p_req_id, p_prg_appl_id,p_prg_id,
l_err_num, l_err_code, l_err_msg);
SELECT cce.cogs_om_line_id,
cce.mmt_transaction_id,
cce.cogs_percentage,
cce.prior_cogs_percentage,
cce.event_quantity,
--{BUG#6980119
-- cce.event_date,
mmt.transaction_date,
--}
cce.event_id,
cce.event_type,
crcml.operating_unit_id,
crcml.organization_id,
crcml.cogs_acct_id,
crcml.deferred_cogs_acct_id,
crcml.inventory_item_id,
crcml.unit_material_cost,
crcml.unit_moh_cost,
crcml.unit_resource_cost,
crcml.unit_op_cost,
crcml.unit_overhead_cost,
crcml.unit_cost,
sob.currency_code,
NVL(mmt.transaction_source_id, -1)
FROM cst_cogs_events cce,
cst_revenue_cogs_match_lines crcml,
mtl_parameters mp,
gl_sets_of_books sob,
mtl_material_transactions mmt -- joining here to use the index on costed_flag
WHERE
cce.mmt_transaction_id = mmt.transaction_id
AND mmt.costed_flag = 'N'
AND mmt.transaction_action_id = 36
AND mmt.transaction_source_type_id = 2
---------------------------------------
-- When the Cogs Recognition program reaches this point the MMT for COGS Regnition are commited to the DB
-- The COGS Recognition program is still running and the Cost Manager should not start as it is incompatible
-- with the Cogs Recognition program. Hence normally the MMT will not be picked up by the Cost Worker
-- But in the case this phase of insertion into MTA stops by user action or by env issue
-- Next time the Cost Manager could have launch the cost worker and during its run the COGS regnition
-- program can start causing the MMT being costed by both program
-- We add the condition transaction_group_id IS NULL as this is the first action the Cost Manager does
-- stamping the transaction_group_id before assigning the job to cost worker and goes back to pending
-- If the MMT is selected by Cost Manager, COGS recognition program should not pick it up
---------------------------------------
AND mmt.transaction_group_id IS NULL --BUG#6730436
AND cce.costed = 'N'
AND cce.event_type IN (COGS_RECOGNITION_EVENT,
COGS_REC_PERCENT_ADJUSTMENT,
COGS_REC_QTY_ADJUSTMENT)
AND crcml.cogs_om_line_id = cce.cogs_om_line_id
AND crcml.pac_cost_type_id IS NULL
AND crcml.ledger_id = p_sob_id
AND crcml.original_shipped_qty IS NOT NULL -- indicator of whether the SO Issue was costed.
AND crcml.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag, 'N') = 'N' -- Cost only discrete orgs
AND crcml.ledger_id = sob.set_of_books_id
AND DECODE(p_neg_req_id,NULL,-99,p_neg_req_id)
= DECODE(p_neg_req_id,NULL,-99,mmt.transaction_set_id); --BUG#7387575
SELECT DISTINCT set_of_books_id
FROM gl_sets_of_books
WHERE set_of_books_id = NVL(p_ledger_id, set_of_books_id);
DELETE cst_lists_temp;
INSERT INTO cst_lists_temp (list_id)
VALUES (1); -- Dr.
INSERT INTO cst_lists_temp (list_id)
VALUES (-1); -- Cr.
INSERT INTO mtl_transaction_accounts mta (
TRANSACTION_ID,
REFERENCE_ACCOUNT,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
GL_BATCH_ID,
ACCOUNTING_LINE_TYPE,
BASE_TRANSACTION_VALUE,
CONTRA_SET_ID,
TRANSACTION_SOURCE_TYPE_ID,
PRIMARY_QUANTITY,
RATE_OR_AMOUNT,
BASIS_TYPE,
COST_ELEMENT_ID,
INV_SUB_LEDGER_ID,
-- WHO COLUMNS
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT l_mmt_txn_id_tbl(i),
decode(clt.list_id, 1, l_cogs_acct_id_tbl(i), -1, l_def_cogs_acct_id_tbl(i), NULL),
l_item_id_tbl(i),
l_organization_id_tbl(i),
l_event_date_tbl(i),
l_transaction_source_id(i), -- txn_source_id is not necessary, this column is NOT NULL by mistake
NULL, -- GL batch ID
decode(clt.list_id, 1, COGS_LINE_TYPE, -1, DEF_COGS_LINE_TYPE, NULL),
decode(fc.minimum_accountable_unit, NULL,
ROUND(clt.list_id * l_event_quantity_tbl(i) * (l_cogs_percentage_tbl(i) - l_prior_percentage_tbl(i)) *
decode(cce.cost_element_id, 1, l_e1_tbl(i), 2, l_e2_tbl(i), 3, l_e3_tbl(i),
4, l_e4_tbl(i), 5, l_e5_tbl(i), 0), fc.precision),
ROUND(clt.list_id * l_event_quantity_tbl(i) * (l_cogs_percentage_tbl(i) - l_prior_percentage_tbl(i)) *
decode(cce.cost_element_id, 1, l_e1_tbl(i), 2, l_e2_tbl(i), 3, l_e3_tbl(i),
4, l_e4_tbl(i), 5, l_e5_tbl(i), 0) / fc.minimum_accountable_unit)
* fc.minimum_accountable_unit),
1, -- contra set ID
2, -- transaction source type ID for COGS Recognition Events
clt.list_id * l_event_quantity_tbl(i),
clt.list_id * (l_cogs_percentage_tbl(i) - l_prior_percentage_tbl(i)) *
decode(cce.cost_element_id,
1, l_e1_tbl(i),
2, l_e2_tbl(i),
3, l_e3_tbl(i),
4, l_e4_tbl(i),
5, l_e5_tbl(i),
0),
1, -- basis type
cce.cost_element_id,
cst_inv_sub_ledger_id_s.nextval,
-- WHO COLUMNS
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_request_id,
p_pgm_app_id,
p_pgm_id,
sysdate
FROM cst_cost_elements cce,
cst_lists_temp clt,
fnd_currencies fc
WHERE fc.currency_code = l_currency_code_tbl(i)
AND l_event_quantity_tbl(i) <> 0
AND l_cogs_percentage_tbl(i) <> l_prior_percentage_tbl(i)
AND nvl(decode(cce.cost_element_id,
1, l_e1_tbl(i),
2, l_e2_tbl(i),
3, l_e3_tbl(i),
4, l_e4_tbl(i),
5, l_e5_tbl(i),
0),0) <> 0;
UPDATE cst_cogs_events
SET costed = NULL,
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id
WHERE event_id = l_event_id_tbl(i);
UPDATE mtl_material_transactions
SET costed_flag = NULL,
last_update_date = sysdate,
last_updated_by = p_user_id,
last_update_login = p_login_id,
request_id = p_request_id,
program_application_id = p_pgm_app_id,
program_id = p_pgm_id,
program_update_date = sysdate
WHERE transaction_id = l_mmt_txn_id_tbl(i);
INSERT INTO xla_events_int_gt
(
application_id,
ledger_id,
entity_code,
source_id_int_1,
source_id_int_2,
source_id_int_3,
event_class_code,
event_type_code,
event_date,
event_status_code,
security_id_int_1,
security_id_int_2,
transaction_date,
reference_date_1,
transaction_number
)
SELECT
707,
l_sob, --l_sob_id.set_of_books_id,
'MTL_ACCOUNTING_EVENTS',
l_mmt_txn_id_tbl(i),
l_organization_id_tbl(i),
2, -- source type ID for sales order related txns
'SALES_ORDER',
decode(l_event_type_tbl(i), COGS_RECOGNITION_EVENT , 'COGS_RECOGNITION',
COGS_REC_PERCENT_ADJUSTMENT, 'COGS_RECOGNITION_ADJ',
COGS_REC_QTY_ADJUSTMENT , 'COGS_RECOGNITION_ADJ',
NULL),
l_event_date_tbl(i),
XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
l_organization_id_tbl(i),
l_ou_id_tbl(i),
l_event_date_tbl(i),
l_le_date_tbl(i),
l_mmt_txn_id_tbl(i)
FROM
mtl_parameters mp,
pjm_org_parameters pop,
mtl_material_transactions mmt
WHERE mmt.transaction_id = l_mmt_txn_id_tbl(i) --bug#14180892
AND mp.organization_id = mmt.organization_id
AND pop.organization_id (+) = mp.organization_id
AND NVL(pop.pa_posting_flag,'N') <> 'Y'
AND EXISTS (SELECT NULL
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = mmt.transaction_id);
PROCEDURE Insert_PacSoIssue(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_transaction_id IN NUMBER,
p_layer_id IN NUMBER,
p_cost_type_id IN NUMBER,
p_cost_group_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_request_id IN NUMBER,
p_pgm_app_id IN NUMBER,
p_pgm_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_PacSoIssue';
SAVEPOINT Insert_PacSoIssue_PVT;
SELECT mmt.trx_source_line_id
INTO l_cogs_om_line_id
FROM mtl_material_transactions mmt,
cst_cogs_events cce
WHERE mmt.transaction_id = p_transaction_id
AND cce.mmt_transaction_id = mmt.transaction_id;
/* SELECT NVL(SUM(DECODE(cost_element_id,1,NVL(actual_cost,0),0)),0),
NVL(SUM(DECODE(cost_element_id,2,NVL(actual_cost,0),0)),0),
NVL(SUM(DECODE(cost_element_id,3,NVL(actual_cost,0),0)),0),
NVL(SUM(DECODE(cost_element_id,4,NVL(actual_cost,0),0)),0),
NVL(SUM(DECODE(cost_element_id,5,NVL(actual_cost,0),0)),0)
INTO l_mat_cost,
l_moh_cost,
l_res_cost,
l_osp_cost,
l_ovh_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id=p_transaction_id
AND cost_type_id = p_cost_type_id; */
SELECT SUM(actual_cost)
INTO l_cost
FROM mtl_pac_actual_cost_details
WHERE transaction_id=p_transaction_id
AND cost_type_id = p_cost_type_id
AND cost_element_id = i;
SELECT cogs_om_line_id,
p_cost_type_id pac_cost_type_id,
revenue_om_line_id,
deferred_cogs_acct_id,
cogs_acct_id,
organization_id,
inventory_item_id,
operating_unit_id,
ledger_id,
sales_order_issue_date,
original_shipped_qty
FROM cst_revenue_cogs_match_lines
WHERE cogs_om_line_id = l_cogs_om_line_id
AND pac_cost_type_id IS NULL
) X
ON (
crcml.cogs_om_line_id = X.cogs_om_line_id
AND crcml.pac_cost_type_id = X.pac_cost_type_id
)
WHEN MATCHED THEN
UPDATE
SET unit_material_cost = l_mat_cost, unit_moh_cost = l_moh_cost,
unit_resource_cost = l_res_cost, unit_op_cost = l_osp_cost,
unit_overhead_cost = l_ovh_cost, unit_cost = l_tot_cost,
last_update_date = sysdate, last_updated_by = p_user_id,
last_update_login = p_login_id, request_id = p_request_id,
program_application_id = p_pgm_app_id, program_id = p_pgm_id,
program_update_date = sysdate
WHEN NOT MATCHED THEN
INSERT (
COGS_OM_LINE_ID, PAC_COST_TYPE_ID, REVENUE_OM_LINE_ID,
DEFERRED_COGS_ACCT_ID, COGS_ACCT_ID, ORGANIZATION_ID,
INVENTORY_ITEM_ID, OPERATING_UNIT_ID, COST_GROUP_ID,
LEDGER_ID, SALES_ORDER_ISSUE_DATE, UNIT_MATERIAL_COST,
UNIT_MOH_COST, UNIT_RESOURCE_COST, UNIT_OP_COST,
UNIT_OVERHEAD_COST, UNIT_COST, ORIGINAL_SHIPPED_QTY,
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.cogs_om_line_id, X.pac_cost_type_id, X.revenue_om_line_id,
X.deferred_cogs_acct_id,X.cogs_acct_id, X.organization_id,
X.inventory_item_id, X.operating_unit_id, p_cost_group_id,
X.ledger_id, X.sales_order_issue_date,l_mat_cost,
l_moh_cost, l_res_cost, l_osp_cost,
l_ovh_cost, l_tot_cost, X.original_shipped_qty,
sysdate, p_user_id, sysdate,
p_user_id, p_login_id, p_request_id,
p_pgm_app_id, p_pgm_id, sysdate
);
ROLLBACK TO Insert_PacSoIssue_PVT;
,'Insert_PacSoIssue ('||to_char(l_stmt_num)||') : '||substr(SQLERRM,1,200));
END Insert_PacSoIssue;
SELECT unit_material_cost,
unit_moh_cost,
unit_resource_cost,
unit_op_cost,
unit_overhead_cost,
deferred_cogs_acct_id,
cogs_acct_id
INTO l_elemental_cost(1),
l_elemental_cost(2),
l_elemental_cost(3),
l_elemental_cost(4),
l_elemental_cost(5),
l_def_cogs_acct_id,
l_cogs_acct_id
FROM cst_revenue_cogs_match_lines crcml
WHERE cogs_om_line_id = p_cogs_om_line_id
AND pac_cost_type_id = p_ae_txn_rec.cost_type_id;
CSTPAPBR.insert_account (p_ae_txn_rec,
p_ae_curr_rec,
p_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPBR.insert_account (p_ae_txn_rec,
p_ae_curr_rec,
p_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
SELECT unit_material_cost,
unit_moh_cost,
unit_resource_cost,
unit_op_cost,
unit_overhead_cost,
deferred_cogs_acct_id,
cogs_acct_id
INTO l_elemental_cost(1),
l_elemental_cost(2),
l_elemental_cost(3),
l_elemental_cost(4),
l_elemental_cost(5),
l_def_cogs_acct_id,
l_cogs_acct_id
FROM cst_revenue_cogs_match_lines crcml
WHERE cogs_om_line_id = p_ae_txn_rec.om_line_id
AND pac_cost_type_id = p_ae_txn_rec.cost_type_id;
SELECT cogs_percentage,
prior_cogs_percentage
INTO l_new_percentage,
l_prior_percentage
FROM cst_cogs_events
WHERE mmt_transaction_id = p_ae_txn_rec.transaction_id;
CSTPAPBR.insert_account (p_ae_txn_rec,
p_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
CSTPAPBR.insert_account (p_ae_txn_rec,
p_ae_curr_rec,
l_dr_flag,
l_ae_line_rec,
l_ae_line_tbl,
l_err_rec);
open l_ref_cur for 'select :i_cost_method cost_method,
haou.name operating_unit,
gsb.name ledger,
:i_period_name period_name,
:i_sales_order_date_low sales_order_date_from,
:i_sales_order_date_high sales_order_date_to,
fl.meaning all_lines,
:i_amt_tolerance tolerance_amount
from hr_all_organization_units haou,
gl_sets_of_books gsb,
fnd_lookups fl
where haou.organization_id = :i_operating_unit
and gsb.set_of_books_id = :i_ledger_id
and fl.lookup_code = :i_all_lines
and fl.lookup_type = ''YES_NO'''
using i_cost_method,
i_period_name,
i_sales_order_date_low,
i_sales_order_date_high,
i_amt_tolerance,
i_operating_unit,
i_ledger_id,
i_all_lines;
SELECT cost_type
INTO l_cost_type
FROM cst_cost_types
WHERE cost_type_id = i_pac_cost_type;
SELECT cost_group
INTO l_cost_group
FROM cst_cost_groups
WHERE cost_group_id = i_pac_cost_group;
SELECT period_name
INTO l_period_name
FROM cst_pac_periods
WHERE pac_period_id = TO_NUMBER(i_period_name);
SELECT meaning
INTO l_meaning
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = i_all_lines;
open l_ref_cur for 'select :i_cost_method cost_method,
xle.name legal_entity,
:l_cost_type cost_type,
:l_cost_group cost_group,
:l_period_name period_name,
:i_sales_order_date_low sales_order_date_from,
:i_sales_order_date_high sales_order_date_to,
:l_meaning all_lines
from xle_firstparty_information_v xle
where xle.legal_entity_id = :i_pac_legal_entity'
using i_cost_method,
l_cost_type,
l_cost_group,
l_period_name,
i_sales_order_date_low,
i_sales_order_date_high,
l_meaning,
i_pac_legal_entity;
SELECT /* index(srclines RA_CUSTOMER_TRX_LINES_N9) LEADING (Q, srclines) use_nl(Q, srclines) */
Q.ORDER_NUMBER order_number,
Q.booked_date /* maybe ordered_date? */ order_date,
(select substrb(PARTY.PARTY_NAME,1,50)
from HZ_PARTIES party
where CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) customer_name,
Q.transactional_curr_code currency,
Q.line_number sales_order_line,
Q.REVENUE_OM_LINE_ID sales_order_line_id,
Q.ITEM_TYPE_CODE item_type_code,
msi.concatenated_segments item,
srclines.line_number invoice_line,
srclines.customer_trx_line_id,
srclines.customer_trx_id,
-------------------------
sum(cce1.EVENT_QUANTITY) total_line_quantity,
Q.COGS_BALANCE Earned_COGS,
Q2.COGS_BALANCE Total_Earned_COGS,
Q.DEF_COGS_BALANCE Deferred_COGS,
Q2.DEF_COGS_BALANCE Total_Deferred_COGS,
cogs_acct.concatenated_segments COGS_account,
dcogs_acct.concatenated_segments Deferred_COGS_account
FROM
(
SELECT /*+ leading(crcml), index(crcml CST_REV_COGS_MATCH_LINES_N2) */
OOH.ORDER_NUMBER,
OOH.BOOKED_DATE,
OOH.transactional_curr_code,
OOL.LINE_NUMBER,
OOL.SOLD_TO_ORG_ID,
OOL.ITEM_TYPE_CODE,
CRCML.COGS_OM_LINE_ID,
CRCML.REVENUE_OM_LINE_ID,
CRCML.ORGANIZATION_ID,
CRCML.inventory_item_id,
CRCML.UNIT_COST,
CRCML.COGS_ACCT_ID,
CRCML.DEFERRED_COGS_ACCT_ID,
sum(decode(mta.accounting_line_type, 35, MTA.BASE_TRANSACTION_VALUE,0)) COGS_BALANCE,
sum(decode(mta.accounting_line_type, 36, MTA.BASE_TRANSACTION_VALUE,0)) DEF_COGS_BALANCE
FROM
CST_REVENUE_COGS_MATCH_LINES CRCML,
CST_COGS_EVENTS CCE,
GL_PERIOD_STATUSES GPS,
OE_ORDER_LINES_ALL OOL,
OE_ORDER_HEADERS_ALL OOH,
MTL_TRANSACTION_ACCOUNTS MTA
WHERE
CRCML.SALES_ORDER_ISSUE_DATE BETWEEN :i_sales_order_date_low AND :i_sales_order_date_high
AND CRCML.OPERATING_UNIT_ID = :i_operating_unit
AND CRCML.PAC_COST_TYPE_ID IS NULL
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = :i_ledger_id
AND GPS.PERIOD_NAME = :i_period_name
AND CCE.EVENT_DATE <= GPS.END_DATE + (1-1/86400) - :l_date_offset
AND CCE.COGS_OM_LINE_ID = CRCML.COGS_OM_LINE_ID
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.LINE_ID = CRCML.COGS_OM_LINE_ID
AND mta.transaction_id (+) = cce.mmt_transaction_id
GROUP BY
OOH.ORDER_NUMBER,
OOH.BOOKED_DATE,
OOH.transactional_curr_code,
OOL.LINE_NUMBER,
OOL.SOLD_TO_ORG_ID,
OOL.ITEM_TYPE_CODE,
CRCML.COGS_OM_LINE_ID,
CRCML.REVENUE_OM_LINE_ID,
CRCML.ORGANIZATION_ID,
CRCML.inventory_item_id,
CRCML.UNIT_COST,
CRCML.COGS_ACCT_ID,
CRCML.DEFERRED_COGS_ACCT_ID
) Q,
(
SELECT /*+ leading(crcml), index(crcml CST_REV_COGS_MATCH_LINES_N2) */
OOH.ORDER_NUMBER,
OOH.BOOKED_DATE,
OOH.transactional_curr_code,
OOL.LINE_NUMBER,
OOL.SOLD_TO_ORG_ID,
OOL.ITEM_TYPE_CODE,
CRCML.REVENUE_OM_LINE_ID,
CRCML.ORGANIZATION_ID,
CRCML.COGS_ACCT_ID,
CRCML.DEFERRED_COGS_ACCT_ID,
sum(decode(mta.accounting_line_type, 35, MTA.BASE_TRANSACTION_VALUE,0)) COGS_BALANCE,
sum(decode(mta.accounting_line_type, 36, MTA.BASE_TRANSACTION_VALUE,0)) DEF_COGS_BALANCE
FROM
CST_REVENUE_COGS_MATCH_LINES CRCML,
CST_COGS_EVENTS CCE,
GL_PERIOD_STATUSES GPS,
OE_ORDER_LINES_ALL OOL,
OE_ORDER_HEADERS_ALL OOH,
MTL_TRANSACTION_ACCOUNTS MTA
WHERE
CRCML.SALES_ORDER_ISSUE_DATE BETWEEN :i_sales_order_date_low AND :i_sales_order_date_high
AND CRCML.OPERATING_UNIT_ID = :i_operating_unit
AND CRCML.PAC_COST_TYPE_ID IS NULL
AND GPS.APPLICATION_ID = 101
AND GPS.SET_OF_BOOKS_ID = :i_ledger_id
AND GPS.PERIOD_NAME = :i_period_name
AND CCE.EVENT_DATE <= GPS.END_DATE + (1-1/86400) - :l_date_offset
AND CCE.COGS_OM_LINE_ID = CRCML.COGS_OM_LINE_ID
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.LINE_ID = CRCML.COGS_OM_LINE_ID
AND mta.transaction_id (+) = cce.mmt_transaction_id
GROUP BY
OOH.ORDER_NUMBER,
OOH.BOOKED_DATE,
OOH.transactional_curr_code,
OOL.LINE_NUMBER,
OOL.SOLD_TO_ORG_ID,
OOL.ITEM_TYPE_CODE,
CRCML.REVENUE_OM_LINE_ID,
CRCML.ORGANIZATION_ID,
CRCML.COGS_ACCT_ID,
CRCML.DEFERRED_COGS_ACCT_ID
) Q2,
MTL_SYSTEM_ITEMS_KFV MSI,
gl_code_combinations_kfv cogs_acct,
gl_code_combinations_kfv dcogs_acct,
ra_customer_trx_lines_all srclines,
HZ_CUST_ACCOUNTS cust_acct,
cst_cogs_events cce1
WHERE
MSI.INVENTORY_ITEM_ID = Q.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = Q.ORGANIZATION_ID
AND Q2.ORGANIZATION_ID = Q.ORGANIZATION_ID
AND cogs_acct.code_combination_id = Q.cogs_acct_id
AND Q2.cogs_acct_id = Q.cogs_acct_id
AND dcogs_acct.code_combination_id = Q.deferred_cogs_acct_id
AND Q2.deferred_cogs_acct_id = Q.deferred_cogs_acct_id
AND Q.sold_to_org_id = cust_acct.CUST_ACCOUNT_ID (+)
AND Q2.sold_to_org_id = Q.sold_to_org_id
AND srclines.line_type (+) = ''LINE''
AND srclines.interface_line_context (+) = ''ORDER ENTRY''
AND srclines.interface_line_attribute6 (+) = to_char(Q.revenue_om_line_id)
AND Q2.revenue_om_line_id = Q.revenue_om_line_id
AND srclines.sales_order (+) = to_char(Q.order_number)
AND Q2.order_number = Q.order_number
AND cce1.cogs_om_line_id = Q.cogs_om_line_id
AND cce1.event_type in (1,2)
GROUP BY
Q.ORDER_NUMBER,
Q.booked_date,
CUST_ACCT.PARTY_ID,
Q.transactional_curr_code,
Q.line_number,
Q.REVENUE_OM_LINE_ID,
Q.ITEM_TYPE_CODE,
msi.concatenated_segments,
srclines.line_number,
srclines.customer_trx_line_id,
srclines.customer_trx_id,
Q.COGS_BALANCE,
Q2.COGS_BALANCE,
Q.DEF_COGS_BALANCE,
Q2.DEF_COGS_BALANCE,
cogs_acct.concatenated_segments,
dcogs_acct.concatenated_segments
)
--Bug 12409271(Added following hint to start execution with Z and use Index join)
SELECT /*+ Leading(Z) INDEX_JOIN(lines RA_CUSTOMER_TRX_LINES_U1 RA_CUSTOMER_TRX_LINES_N1)*/
:i_all_lines all_lines,
Z.order_number order_number,
Z.order_date order_date,
Z.customer_name customer,
Decode(trx.TRX_NUMBER,Min(lines1.TRX_NUMBER),Decode(Z.INVOICE_LINE,Min(lines1.MIN_INV_LINE), Z.currency, NULL), NULL) currency,
Z.sales_order_line order_line,
Z.sales_order_line_id order_line_id,
trx.trx_number invoice_number,
Z.invoice_line invoice_line,
Z.item item_number,
ROUND(DECODE(Z.item_type_code, ''CONFIG'', lines2.total_earned_revenue,
SUM (DECODE(dist.account_class, ''UNEARN'', 0,
''UNBILL'', 0, dist.acctd_amount ) ) )
*DECODE(Z.earned_cogs, Z.total_earned_cogs,1,Z.earned_cogs/DECODE(Z.total_earned_cogs,0,1,Z.total_earned_cogs)), 2) earned_revenue,
ROUND(DECODE(Z.item_type_code, ''CONFIG'', SUM (DECODE(dist.account_class, ''REV'', 0,
''UNBILL'', 0, lines2.total_earned_revenue) )
*DECODE(Z.deferred_cogs, 0, 0, Z.total_deferred_cogs,1,Z.deferred_cogs/DECODE(Z.total_deferred_cogs,0,1,Z.total_deferred_cogs)),
SUM (DECODE(dist.account_class, ''REV'', 0,
''UNBILL'', 0, dist.acctd_amount) )
*DECODE(Z.deferred_cogs, Z.total_deferred_cogs,1,Z.deferred_cogs/DECODE(Z.total_deferred_cogs,0,1,Z.total_deferred_cogs))), 2) unearned_revenue,
ROUND(DECODE(Z.item_type_code, ''CONFIG'', SUM (DECODE(dist.account_class, ''REV'', 0,
''UNEARN'', 0, lines2.total_earned_revenue) )
*DECODE(Z.deferred_cogs, 0, 0, Z.total_deferred_cogs,1,Z.deferred_cogs/DECODE(Z.total_deferred_cogs,0,1,Z.total_deferred_cogs)),
SUM (DECODE(dist.account_class, ''REV'', 0,
''UNEARN'', 0, dist.acctd_amount) )
*DECODE(Z.deferred_cogs, Z.total_deferred_cogs,1,Z.deferred_cogs/DECODE(Z.total_deferred_cogs,0,1,Z.total_deferred_cogs))), 2) unbilled_revenue,
Decode(trx.TRX_NUMBER,Min(lines1.TRX_NUMBER),Decode(Z.INVOICE_LINE,Min(lines1.MIN_INV_LINE),Z.total_line_quantity,NULL),NULL) order_quantity,
Decode(trx.TRX_NUMBER,Min(lines1.TRX_NUMBER),Decode(Z.INVOICE_LINE,Min(lines1.MIN_INV_LINE),Z.Earned_COGS,NULL),NULL) earned_cogs,
Decode(trx.TRX_NUMBER,Min(lines1.TRX_NUMBER),Decode(Z.INVOICE_LINE,Min(lines1.MIN_INV_LINE),Z.Deferred_COGS,NULL),NULL) deferred_cogs,
Decode(trx.TRX_NUMBER,Min(lines1.TRX_NUMBER),Decode(Z.INVOICE_LINE,Min(lines1.MIN_INV_LINE),Z.COGS_account,NULL),NULL) cogs_account,
Decode(trx.TRX_NUMBER,Min(lines1.TRX_NUMBER),Decode(Z.INVOICE_LINE,Min(lines1.MIN_INV_LINE),Z.Deferred_COGS_account,NULL),NULL) deferred_cogs_account
FROM
Z,
GL_PERIOD_STATUSES GPS1,
ra_cust_trx_line_gl_dist_all dist,
ra_customer_trx_lines_all lines,
ra_customer_trx_all trx,
(SELECT rctla.INTERFACE_LINE_ATTRIBUTE1,rctla.INTERFACE_LINE_ATTRIBUTE6,rcta.TRX_NUMBER,min(rctla.LINE_NUMBER) MIN_INV_LINE
FROM RA_CUSTOMER_TRX_LINES_ALL rctla,RA_CUSTOMER_TRX_ALL rcta,z
WHERE rctla.INTERFACE_LINE_CONTEXT=''ORDER ENTRY''
AND rctla.LINE_TYPE=''LINE''
AND rctla.CUSTOMER_TRX_ID=rcta.CUSTOMER_TRX_ID
AND RCTLA.SALES_ORDER = to_char(Z.ORDER_NUMBER)
AND rctla.CUSTOMER_TRX_ID=(SELECT Min(rctla1.CUSTOMER_TRX_ID)
FROM RA_CUSTOMER_TRX_LINES_ALL rctla1--,z
WHERE rctla.INTERFACE_LINE_ATTRIBUTE6=rctla1.INTERFACE_LINE_ATTRIBUTE6
AND rctla1.INTERFACE_LINE_CONTEXT=''ORDER ENTRY''
AND RCTLA1.SALES_ORDER = RCTLA.SALES_ORDER --to_char(Z.ORDER_NUMBER)
AND rctla1.LINE_TYPE=''LINE'')
GROUP BY rctla.INTERFACE_LINE_ATTRIBUTE1,rctla.INTERFACE_LINE_ATTRIBUTE6,rcta.TRX_NUMBER) lines1,
(SELECT rcta.CUSTOMER_TRX_ID,sum(rctlgda.ACCTD_AMOUNT) TOTAL_EARNED_REVENUE
FROM RA_CUSTOMER_TRX_LINES_ALL rctla,RA_CUSTOMER_TRX_ALL rcta, RA_CUST_TRX_LINE_GL_DIST_ALL rctlgda,z
WHERE rctla.INTERFACE_LINE_CONTEXT=''ORDER ENTRY''
AND rctla.LINE_TYPE=''LINE''
AND rctla.CUSTOMER_TRX_ID=rcta.CUSTOMER_TRX_ID
AND RCTLA.SALES_ORDER = to_char(Z.ORDER_NUMBER)
AND rctla.CUSTOMER_TRX_ID=(SELECT Min(rctla1.CUSTOMER_TRX_ID)
FROM RA_CUSTOMER_TRX_LINES_ALL rctla1--,z
WHERE rctla.INTERFACE_LINE_ATTRIBUTE6=rctla1.INTERFACE_LINE_ATTRIBUTE6
AND rctla1.INTERFACE_LINE_CONTEXT=''ORDER ENTRY''
AND RCTLA1.SALES_ORDER = RCTLA.SALES_ORDER --to_char(Z.ORDER_NUMBER)
AND rctla1.LINE_TYPE=''LINE'')
AND rctla.CUSTOMER_TRX_ID = rctlgda.CUSTOMER_TRX_ID
AND rctla.customer_trx_line_id = rctlgda.customer_trx_line_id
GROUP BY rcta.CUSTOMER_TRX_ID) lines2
WHERE (Z.customer_trx_line_id = lines.customer_trx_line_id
OR Z.customer_trx_line_id = lines.previous_customer_trx_line_id)
AND Z.customer_trx_id = trx.customer_trx_id
AND GPS1.APPLICATION_ID = 101
AND GPS1.SET_OF_BOOKS_ID = :i_ledger_id
AND GPS1.PERIOD_NAME = :i_period_name
AND dist.customer_trx_line_id = lines.customer_trx_line_id
AND dist.account_set_flag = ''N''
AND dist.account_class IN (''REV'', ''UNEARN'', ''UNBILL'')
AND dist.gl_date <= GPS1.END_DATE + (1-1/86400) -- or AS OF DATE
AND to_char(Z.SALES_ORDER_LINE_ID)=LINES.INTERFACE_LINE_ATTRIBUTE6
AND to_char(Z.SALES_ORDER_LINE_ID)=LINES1.INTERFACE_LINE_ATTRIBUTE6
AND Z.CUSTOMER_TRX_ID=LINES2.CUSTOMER_TRX_ID
GROUP BY
Z.ORDER_NUMBER,
Z.order_date,
Z.customer_name,
Z.currency,
Z.sales_order_line,
Z.sales_order_line_id,
Z.CUSTOMER_TRX_ID,
Z.item_type_code,
lines2.total_earned_revenue,
lines1.INTERFACE_LINE_ATTRIBUTE6,
trx.trx_number,
Z.invoice_line,
Z.item,
Z.total_line_quantity,
Z.Earned_COGS,
Z.Total_Earned_COGS,
Z.Deferred_COGS,
Z.Total_Deferred_COGS,
Z.COGS_account,
Z.Deferred_COGS_account
HAVING :i_all_lines = ''Y''
or ((decode(sum(lines.revenue_amount),0,1,
round(sum(decode(dist.account_class, ''UNEARN'', 0,
''UNBILL'', 0, dist.amount)) /
(sum(lines.revenue_amount) /
count(dist.cust_trx_line_gl_dist_id)), 3))
<>
decode(z.earned_cogs, 0, decode(z.deferred_cogs,0,1,0),
round((z.earned_cogs /
decode(z.deferred_cogs + z.earned_cogs,0,1,
z.deferred_cogs + z.earned_cogs)), 3)))
AND z.deferred_cogs NOT BETWEEN -1*:i_amt_tolerance AND :i_amt_tolerance
)
UNION
SELECT :i_all_lines all_lines,
Z.order_number order_number,
Z.order_date order_date,
Z.customer_name customer,
Z.currency currency,
Z.sales_order_line order_line,
Z.sales_order_line_id order_line_id,
NULL invoice_number,
NULL invoice_line,
Z.item item_number,
NULL earned_revenue,
NULL unearned_revenue,
NULL unbilled_revenue,
Z.total_line_quantity order_quantity,
Z.Earned_COGS earned_cogs,
Z.Deferred_COGS deferred_cogs,
Z.COGS_account cogs_account,
Z.Deferred_COGS_account deferred_cogs_account
FROM
Z
WHERE (Z.customer_trx_line_id IS NULL
OR Z.customer_trx_id IS NULL)
AND (:i_all_lines = ''Y''
OR z.deferred_cogs NOT BETWEEN -1*:i_amt_tolerance AND :i_amt_tolerance)
UNION
SELECT :i_all_lines all_lines,
Z.order_number order_number,
Z.order_date order_date,
Z.customer_name customer,
Z.currency currency,
Z.sales_order_line order_line,
Z.sales_order_line_id order_line_id,
NULL invoice_number,
NULL invoice_line,
Z.item item_number,
NULL earned_revenue,
NULL unearned_revenue,
NULL unbilled_revenue,
Z.total_line_quantity order_quantity,
Z.Earned_COGS earned_cogs,
Z.Deferred_COGS deferred_cogs,
Z.COGS_account cogs_account,
Z.Deferred_COGS_account deferred_cogs_account
FROM
Z
WHERE (Z.customer_trx_line_id IS NOT NULL AND Z.customer_trx_id IS NOT NULL)
AND NOT EXISTS
(SELECT NULL
FROM ra_customer_trx_lines_all ctl
, ra_cust_trx_line_gl_dist_all ctlgd
, gl_period_statuses g2
WHERE (Z.customer_trx_line_id = ctl.customer_trx_line_id
OR Z.customer_trx_line_id = ctl.previous_customer_trx_line_id)
AND g2.application_id = 101
AND g2.set_of_books_id = :i_ledger_id
AND g2.period_name = :i_period_name
AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
AND ctlgd.account_set_flag = ''N''
AND ctlgd.account_class IN (''REV'', ''UNEARN'', ''UNBILL'')
AND ctlgd.gl_date <= g2.END_DATE + (1-1/86400))
AND (:i_all_lines = ''Y''
OR z.deferred_cogs NOT BETWEEN -1*:i_amt_tolerance AND :i_amt_tolerance)
ORDER BY order_number'
using i_sales_order_date_low,
i_sales_order_date_high,
i_operating_unit,
i_ledger_id,
i_period_name,
l_date_offset,
i_sales_order_date_low,
i_sales_order_date_high,
i_operating_unit,
i_ledger_id,
i_period_name,
l_date_offset,
i_all_lines,
i_ledger_id,
i_period_name,
i_all_lines,
i_amt_tolerance,
i_amt_tolerance,
i_all_lines,
i_all_lines,
i_amt_tolerance,
i_amt_tolerance,
i_all_lines,
i_ledger_id,
i_period_name,
i_all_lines,
i_amt_tolerance,
i_amt_tolerance;
SELECT inv_le_timezone_pub.get_server_day_time_for_le(
l_dummy_date,
legal_entity
) - l_dummy_date
INTO l_date_offset
FROM cst_cost_groups
WHERE cost_group_id = i_pac_cost_group;
SELECT /* index(srclines RA_CUSTOMER_TRX_LINES_N9) LEADING (Q, srclines) use_nl(Q, srclines) */
Q.ORDER_NUMBER order_number,
Q.booked_date /* maybe ordered_date? */ order_date,
(select substrb(PARTY.PARTY_NAME,1,50)
from HZ_PARTIES party
where CUST_ACCT.PARTY_ID = PARTY.PARTY_ID) customer_name,
Q.transactional_curr_code currency,
Q.line_number sales_order_line,
msi.concatenated_segments item,
srclines.line_number invoice_line,
srclines.customer_trx_line_id,
srclines.customer_trx_id,
-------------------------
sum(cce1.EVENT_QUANTITY) total_line_quantity,
Q.COGS_BALANCE Earned_COGS,
Q.DEF_COGS_BALANCE Deferred_COGS,
cogs_acct.concatenated_segments COGS_account,
dcogs_acct.concatenated_segments Deferred_COGS_account
FROM
(
SELECT /* LEADING(CCGA) */
OOH.ORDER_NUMBER,
OOH.BOOKED_DATE,
OOH.transactional_curr_code,
OOL.LINE_NUMBER,
OOL.SOLD_TO_ORG_ID,
CRCML.COGS_OM_LINE_ID,
CRCML.REVENUE_OM_LINE_ID,
CRCML.ORGANIZATION_ID,
CRCML.inventory_item_id,
CRCML.UNIT_COST,
CRCML.COGS_ACCT_ID,
CRCML.DEFERRED_COGS_ACCT_ID,
sum(decode(CAL.ae_line_type_code, 35, nvl(CAL.accounted_dr,0) - nvl(CAL.accounted_cr,0),0)) COGS_BALANCE,
sum(decode(CAL.ae_line_type_code, 36, nvl(CAL.accounted_dr,0) - nvl(CAL.accounted_cr,0),0)) DEF_COGS_BALANCE
FROM
CST_REVENUE_COGS_MATCH_LINES CRCML,
CST_COGS_EVENTS CCE,
OE_ORDER_LINES_ALL OOL,
OE_ORDER_HEADERS_ALL OOH,
CST_COST_GROUP_ASSIGNMENTS CCGA,
CST_PAC_PERIODS CPP,
CST_AE_HEADERS CAH,
CST_AE_LINES CAL
WHERE
CRCML.SALES_ORDER_ISSUE_DATE BETWEEN :i_sales_order_date_low AND :i_sales_order_date_high
AND CRCML.ORGANIZATION_ID = CCGA.ORGANIZATION_ID
AND CRCML.PAC_COST_TYPE_ID IS NULL -- Want to pick up all events, not just PAC-costed events
AND CCGA.COST_GROUP_ID = :i_pac_cost_group
AND CPP.PAC_PERIOD_ID = TO_NUMBER(:i_period_name)
AND CCE.EVENT_DATE <= CPP.PERIOD_END_DATE + (1-1/86400) + :l_date_offset
AND CCE.COGS_OM_LINE_ID = CRCML.COGS_OM_LINE_ID
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOL.LINE_ID = CRCML.COGS_OM_LINE_ID
AND CCE.MMT_TRANSACTION_ID = CAH.ACCOUNTING_EVENT_ID (+)
AND CAH.AE_HEADER_ID = CAL.AE_HEADER_ID (+)
GROUP BY
OOH.ORDER_NUMBER,
OOH.BOOKED_DATE,
OOH.transactional_curr_code,
OOL.LINE_NUMBER,
OOL.SOLD_TO_ORG_ID,
CRCML.COGS_OM_LINE_ID,
CRCML.REVENUE_OM_LINE_ID,
CRCML.ORGANIZATION_ID,
CRCML.inventory_item_id,
CRCML.UNIT_COST,
CRCML.COGS_ACCT_ID,
CRCML.DEFERRED_COGS_ACCT_ID
) Q,
MTL_SYSTEM_ITEMS_KFV MSI,
gl_code_combinations_kfv cogs_acct,
gl_code_combinations_kfv dcogs_acct,
ra_customer_trx_lines_all srclines,
HZ_CUST_ACCOUNTS cust_acct,
cst_cogs_events cce1,
CST_PAC_PERIODS CPP
WHERE
MSI.INVENTORY_ITEM_ID = Q.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = Q.ORGANIZATION_ID
AND cogs_acct.code_combination_id = Q.cogs_acct_id
AND dcogs_acct.code_combination_id = Q.deferred_cogs_acct_id
AND Q.sold_to_org_id = cust_acct.CUST_ACCOUNT_ID (+)
AND srclines.line_type (+) = ''LINE''
AND srclines.interface_line_context (+) = ''ORDER ENTRY''
AND srclines.interface_line_attribute6 (+) = to_char(Q.revenue_om_line_id)
AND srclines.sales_order (+) = to_char(Q.order_number)
AND cce1.cogs_om_line_id = Q.cogs_om_line_id
AND CPP.PAC_PERIOD_ID = TO_NUMBER(:i_period_name)
AND CCE1.EVENT_DATE <= CPP.PERIOD_END_DATE + (1-1/86400) + :l_date_offset
AND cce1.event_type in (1,2)
GROUP BY
Q.ORDER_NUMBER,
Q.booked_date,
CUST_ACCT.PARTY_ID,
Q.transactional_curr_code,
Q.line_number,
msi.concatenated_segments,
srclines.line_number,
srclines.customer_trx_line_id,
srclines.customer_trx_id,
Q.COGS_BALANCE,
Q.DEF_COGS_BALANCE,
cogs_acct.concatenated_segments,
dcogs_acct.concatenated_segments
)
SELECT :i_all_lines all_lines,
Z.order_number order_number,
Z.order_date order_date,
Z.customer_name customer,
Z.currency currency,
Z.sales_order_line order_line,
trx.trx_number invoice_number,
Z.invoice_line invoice_line,
Z.item item_number,
ROUND(SUM ( DECODE(dist.account_class, ''UNEARN'', 0,
''UNBILL'', 0, dist.acctd_amount) ), 2) earned_revenue,
ROUND(SUM ( DECODE(dist.account_class, ''REV'', 0,
''UNBILL'', 0, dist.acctd_amount) ), 2) unearned_revenue,
ROUND(SUM ( DECODE(dist.account_class, ''REV'', 0,
''UNEARN'', 0, dist.acctd_amount) ), 2) unbilled_revenue,
Z.total_line_quantity order_quantity,
Z.Earned_COGS earned_cogs,
Z.Deferred_COGS deferred_cogs,
Z.COGS_account cogs_account,
Z.Deferred_COGS_account deferred_cogs_account
FROM
Z,
cst_pac_periods cpp,
ra_cust_trx_line_gl_dist_all dist,
ra_customer_trx_lines_all lines,
ra_customer_trx_all trx
WHERE (Z.customer_trx_line_id = lines.customer_trx_line_id
OR Z.customer_trx_line_id = lines.previous_customer_trx_line_id)
AND Z.customer_trx_id = trx.customer_trx_id
AND cpp.pac_period_id = TO_NUMBER(:i_period_name)
AND dist.customer_trx_line_id = lines.customer_trx_line_id
AND dist.account_set_flag = ''N''
AND dist.account_class IN (''REV'', ''UNEARN'', ''UNBILL'')
AND dist.gl_date <= cpp.period_end_date + (1-1/86400) -- or AS OF DATE
GROUP BY
Z.ORDER_NUMBER,
Z.order_date,
Z.customer_name,
Z.currency,
Z.sales_order_line,
trx.trx_number,
Z.invoice_line,
Z.item,
Z.total_line_quantity,
Z.Earned_COGS,
Z.Deferred_COGS,
Z.COGS_account,
Z.Deferred_COGS_account
HAVING :i_all_lines = ''Y''
or decode(sum(lines.revenue_amount),0,1,
round(sum(decode(dist.account_class, ''UNEARN'', 0,
''UNBILL'', 0, dist.amount)) /
(sum(lines.revenue_amount) /
count(dist.cust_trx_line_gl_dist_id)), 3))
<>
decode(z.earned_cogs, 0, decode(z.deferred_cogs,0,1,0),
round((z.earned_cogs /
decode(z.deferred_cogs + z.earned_cogs,0,1,
z.deferred_cogs + z.earned_cogs)), 3))
UNION
SELECT :i_all_lines all_lines,
Z.order_number order_number,
Z.order_date order_date,
Z.customer_name customer,
Z.currency currency,
Z.sales_order_line order_line,
NULL invoice_number,
NULL invoice_line,
Z.item item_number,
NULL earned_revenue,
NULL unearned_revenue,
NULL unbilled_revenue,
Z.total_line_quantity order_quantity,
Z.Earned_COGS earned_cogs,
Z.Deferred_COGS deferred_cogs,
Z.COGS_account cogs_account,
Z.Deferred_COGS_account deferred_cogs_account
FROM
Z
WHERE Z.customer_trx_line_id IS NULL
OR Z.customer_trx_id IS NULL
UNION
SELECT :i_all_lines all_lines,
Z.order_number order_number,
Z.order_date order_date,
Z.customer_name customer,
Z.currency currency,
Z.sales_order_line order_line,
NULL invoice_number,
NULL invoice_line,
Z.item item_number,
NULL earned_revenue,
NULL unearned_revenue,
NULL unbilled_revenue,
Z.total_line_quantity order_quantity,
Z.Earned_COGS earned_cogs,
Z.Deferred_COGS deferred_cogs,
Z.COGS_account cogs_account,
Z.Deferred_COGS_account deferred_cogs_account
FROM
Z
WHERE (Z.customer_trx_line_id IS NOT NULL AND Z.customer_trx_id IS NOT NULL)
AND NOT EXISTS
(SELECT NULL
FROM ra_customer_trx_lines_all ctl
, ra_cust_trx_line_gl_dist_all ctlgd
, CST_PAC_PERIODS CPP
WHERE (Z.customer_trx_line_id = ctl.customer_trx_line_id
OR Z.customer_trx_line_id = ctl.previous_customer_trx_line_id)
AND CPP.PAC_PERIOD_ID = TO_NUMBER(:i_period_name)
AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
AND ctlgd.account_set_flag = ''N''
AND ctlgd.account_class IN (''REV'', ''UNEARN'', ''UNBILL'')
AND ctlgd.gl_date <= cpp.period_end_date + (1-1/86400))
AND (:i_all_lines = ''Y''
OR z.deferred_cogs NOT BETWEEN -1*:i_amt_tolerance AND :i_amt_tolerance)'
using
i_sales_order_date_low,
i_sales_order_date_high,
i_pac_cost_group,
i_period_name,
l_date_offset,
i_period_name,
l_date_offset,
i_all_lines,
i_period_name,
i_all_lines,
i_all_lines,
i_all_lines,
i_period_name,
i_all_lines,
i_amt_tolerance,
i_amt_tolerance;
open l_ref_cur for ' select :l_count l_count
from dual'
using l_count;
SELECT mmtt.rowid
,mmtt.transaction_id
,mmtt.transaction_date
,mmtt.acct_period_id
,oap.open_flag
,oap.period_start_date
,oap.schedule_close_date
,oap2.acct_period_id
,oap2.open_flag
,mmtt.organization_id
FROM mtl_cogs_recognition_temp mmtt,
org_acct_periods oap,
(SELECT period_start_date,
schedule_close_date,
acct_period_id,
organization_id,
open_flag
FROM org_acct_periods) oap2
WHERE mmtt.acct_period_id = oap.acct_period_id
AND mmtt.organization_id = oap.organization_id
AND oap2.organization_id = mmtt.organization_id
--
--BUG#6873037 : Just check the inventory period mismatch
--
AND oap2.acct_period_id <> oap.acct_period_id
AND mmtt.transaction_date BETWEEN oap2.period_start_date AND oap2.schedule_close_date + (1-1/86400) --BUG#13601677
AND ( mmtt.transaction_date < oap.period_start_date OR
mmtt.transaction_date > oap.schedule_close_date );
SELECT MIN(acct_period_id),
MIN(period_start_date)
FROM org_acct_periods
WHERE organization_id = p_organization_id
AND open_flag = 'Y'
AND period_start_date >= p_transaction_date;
UPDATE mtl_cogs_recognition_temp
SET transaction_date = l_upd_transaction_date_tab(i)
,acct_period_id = l_upd_acct_period_id_tab(i)
WHERE rowid = l_mmtt_rowid_tab(i);
SELECT COUNT(*)
, ledger_id
FROM cst_revenue_recognition_lines crrl
WHERE potentially_unmatched_flag = 'Y'
GROUP BY ledger_id
HAVING COUNT(*) > 0;
SELECT ROWID,
revenue_om_line_id
FROM cst_revenue_recognition_lines crrl
WHERE potentially_unmatched_flag = 'Y'
AND ledger_id = p_ledger_id
ORDER BY revenue_om_line_id ASC;
PROCEDURE update_one_set_crrl
(p_rowid_tab IN DBMS_SQL.VARCHAR2_TABLE) IS
l_gp_id NUMBER;
SELECT cst_cogs_events_s.nextval
INTO l_gp_id
FROM dual;
UPDATE cst_revenue_recognition_lines
SET request_id = -1 * l_gp_id
WHERE rowid = p_rowid_tab(j);
debug(' CST_REV_REC_LINES updated with request_ID ='|| -1 * l_gp_id ||' for the ledger '||p_ledger_id ||
' Number of records CRRL updated is '|| l_upd_rowid_tab.COUNT);
INSERT INTO cst_lists_temp
( list_id
,number_1
,VARCHAR_1) VALUES (p_ledger_id
, -1 * l_gp_id
, 'INSERTED');
debug(' CST_LISTS_TEMP.varchar_1= STATUS of Request : INSERTED');
END update_one_set_crrl;
update_one_set_crrl(l_upd_rowid_tab);
update_one_set_crrl(l_upd_rowid_tab);
SELECT list_id -- ledger_id
,number_1 -- process_negative_request_id
,rowid
FROM cst_lists_temp
WHERE VARCHAR_1 = 'INSERTED';
SELECT COUNT(*)
FROM cst_lists_temp
WHERE VARCHAR_1 = 'SUBMITTED';
UPDATE cst_lists_temp
SET number_2 = l_req_id
,VARCHAR_1 = 'SUBMITTED'
WHERE ROWID = l_rowid;
SELECT COUNT(*)
INTO l_nb_req_sub
FROM cst_lists_temp
WHERE VARCHAR_1 = 'SUBMITTED';
SELECT number_2
,rowid
FROM cst_lists_temp
WHERE varchar_1 = 'SUBMITTED';
UPDATE cst_lists_temp
SET varchar_1 = l_dev_phase
WHERE rowid = l_rowid_tab(i);
SELECT concurrent_program_id
FROM fnd_concurrent_programs
WHERE application_id = p_app_id
AND concurrent_program_name = p_prg_name;
SELECT NULL
FROM fnd_concurrent_requests FCR
WHERE FCR.program_application_id = 702
AND FCR.concurrent_program_id = p_prg_id
AND (FCR.argument3 = TO_CHAR(p_ledger_id) OR
-- User submits a request for ledger A and CP for all ledger is running
FCR.argument3 IS NULL)
AND FCR.phase_code = 'R'
AND FCR.request_id <> FND_GLOBAL.CONC_REQUEST_ID
AND ROWNUM = 1;
SELECT NULL
FROM fnd_concurrent_requests FCR
WHERE FCR.program_application_id = 702
AND FCR.concurrent_program_id = p_prg_id
AND FCR.phase_code = 'R'
AND FCR.request_id <> FND_GLOBAL.CONC_REQUEST_ID
AND ROWNUM = 1; */
SELECT FCP.user_concurrent_program_name
FROM fnd_concurrent_requests FCR, fnd_concurrent_programs_vl FCP
WHERE FCR.program_application_id = p_app_id
AND FCP.application_id = p_app_id
AND (FCR.argument3 = TO_CHAR(p_ledger_id) OR
-- User submits a request for ledger A and CP for all ledger is running
FCR.argument3 IS NULL)
AND FCR.phase_code = 'R'
AND FCR.request_id <> FND_GLOBAL.CONC_REQUEST_ID
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCP.concurrent_program_name IN ('CSTRCMCR1','CSTRCMCR','CSTRCMCR3');
SELECT FCP.user_concurrent_program_name
FROM fnd_concurrent_requests FCR, fnd_concurrent_programs_vl FCP
WHERE FCR.program_application_id = p_app_id
AND FCP.application_id = p_app_id
AND FCR.phase_code = 'R'
AND FCR.request_id <> FND_GLOBAL.CONC_REQUEST_ID
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCP.concurrent_program_name IN ('CSTRCMCR1','CSTRCMCR','CSTRCMCR3');
SELECT crrl.rowid l_rowid
, 'P' flag
FROM cst_revenue_recognition_lines CRRL
WHERE CRRL.potentially_unmatched_flag = 'Y'
AND CRRL.ledger_id = p_ledger_id
AND (NOT EXISTS (SELECT NULL
FROM cst_revenue_cogs_match_lines CRCML
WHERE CRCML.revenue_om_line_id = CRRL.revenue_om_line_id
AND CRCML.ledger_id = p_ledger_id)
OR EXISTS
(SELECT 1
FROM mtl_transactions_interface mti , cst_revenue_cogs_match_lines crcml
WHERE mti.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
UNION
SELECT 1
FROM mtl_material_transactions_temp mmtt, cst_revenue_cogs_match_lines crcml
WHERE mmtt.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
UNION
SELECT 1
FROM mtl_material_transactions mmt, cst_revenue_cogs_match_lines crcml
WHERE mmt.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
AND mmt.cogs_recognition_percent is null
AND mmt.so_issue_account_type = 2
AND mmt.transaction_type_id = 33
AND Nvl(mmt.logical_transactions_created,2)=2
UNION
SELECT 1
FROM mtl_material_transactions mmt, cst_revenue_cogs_match_lines crcml
WHERE mmt.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
AND mmt.cogs_recognition_percent IS null
AND mmt.so_issue_account_type = 2
AND mmt.transaction_type_id = 30))
UNION ALL
SELECT crrl.rowid l_rowid
, 'Y' flag
FROM cst_revenue_recognition_lines CRRL
WHERE CRRL.potentially_unmatched_flag = 'P'
AND CRRL.ledger_id = p_ledger_id
AND EXISTS (SELECT NULL
FROM cst_revenue_cogs_match_lines CRCML
WHERE CRCML.revenue_om_line_id = CRRL.revenue_om_line_id
AND CRCML.ledger_id = p_ledger_id)
and not exists
(SELECT 1
FROM mtl_transactions_interface mti , cst_revenue_cogs_match_lines crcml
WHERE mti.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
UNION
SELECT 1
FROM mtl_material_transactions_temp mmtt, cst_revenue_cogs_match_lines crcml
WHERE mmtt.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
UNION
SELECT 1
FROM mtl_material_transactions mmt, cst_revenue_cogs_match_lines crcml
WHERE mmt.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
AND mmt.cogs_recognition_percent is null
AND mmt.so_issue_account_type = 2
AND mmt.transaction_type_id = 33
AND Nvl(mmt.logical_transactions_created,2)=2
UNION
SELECT 1
FROM mtl_material_transactions mmt, cst_revenue_cogs_match_lines crcml
WHERE mmt.trx_source_line_id = crcml.cogs_om_line_id
AND crcml.revenue_om_line_id=crrl.revenue_om_line_id
AND mmt.cogs_recognition_percent IS null
AND mmt.so_issue_account_type = 2
AND mmt.transaction_type_id = 30);
SELECT crrl.rowid l_rowid
, DECODE(crrl.acct_period_num,latest_rev.mper,'Y',NULL) flag
FROM cst_revenue_recognition_lines CRRL
, cst_revenue_cogs_match_lines crcml
, cst_cogs_events cce
, gl_period_statuses glp
, (SELECT MAX(crrl3.acct_period_num) mper
, crrl3.revenue_om_line_id
FROM cst_revenue_recognition_lines crrl3
WHERE crrl3.potentially_unmatched_flag = 'Y'
group by crrl3.revenue_om_line_id) latest_rev
WHERE CRRL.potentially_unmatched_flag = 'Y'
AND CRRL.ledger_id = p_ledger_id
AND crrl.revenue_om_line_id = crcml.revenue_om_line_id
AND crcml.cogs_om_line_id = cce.cogs_om_line_id
AND cce.event_type = 1
AND cce.event_date > crrl.last_event_date
AND crrl.revenue_om_line_id = latest_rev.revenue_om_line_id
AND glp.application_id = 101
AND glp.ledger_id = p_ledger_id
AND cce.event_date BETWEEN glp.start_date AND glp.end_date + EndDateNum
AND crrl.acct_period_num < glp.effective_period_num;
UPDATE cst_revenue_recognition_lines CRRL
SET potentially_unmatched_flag = l_flag_tab(i)
where CRRL.ROWID = l_rowid_tab(i);
UPDATE cst_revenue_recognition_lines CRRL
SET potentially_unmatched_flag = l_flag_tab(i)
where CRRL.ROWID = l_rowid_tab(i);
SELECT min(effective_period_num) per_num
,min(start_date) start_date
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = p_ledger_id
AND effective_period_num > p_per_num
AND ( closing_status = 'O' OR
(closing_status = 'F' AND adjustment_period_flag = 'N'));
SELECT TO_NUMBER(TO_CHAR(start_date,'YYYYMMDD')) NUMBER_1
,TO_NUMBER(TO_CHAR(end_date ,'YYYYMMDD')) NUMBER_2
,DECODE(closing_status,'F'
,DECODE(adjustment_period_flag
,'Y','C','F')
,closing_status) VARCHAR_1
,CST_RevenueCogsMatch_PVT.acct_date_per
(ledger_id
,closing_status
,effective_period_num
,start_date
,'PERIOD') LIST_ID
,CST_RevenueCogsMatch_PVT.acct_date_per
(ledger_id
,closing_status
,effective_period_num
,start_date
,'DATE') NUMBER_3
FROM gl_period_statuses
WHERE ledger_id = p_ledger_id
AND application_id = 101
AND EFFECTIVE_PERIOD_NUM >= p_low_per_num
AND EFFECTIVE_PERIOD_NUM <= p_hig_per_num;
DELETE FROM CST_LISTS_TEMP WHERE VARCHAR_1 <> 'TIMEZONE';
debug('4 inserting in CST_LISTS_TEMP');
INSERT INTO CST_LISTS_TEMP
( NUMBER_1 -- START_DATE YYYYMMDD
,NUMBER_2 -- END_DATE YYYYMMDD
,VARCHAR_1 -- CLOSING_STATUS
,LIST_ID -- EFFECTIVE_PERIOD_NUM
,NUMBER_3) -- ACCT_DATE YYYYMMDD
VALUES
( l_number_1_t(i)
,l_number_2_t(i)
,l_varchar_1_t(i)
,l_list_id_t(i)
,l_number_3_t(i));
SELECT VARCHAR_1 closing_status
, NUMBER_3 accounting_date
, LIST_ID per_num
FROM CST_LISTS_TEMP
WHERE VARCHAR_1 <> 'TIMEZONE'
AND p_date_num BETWEEN
NUMBER_1 -- start_date
AND NUMBER_2; -- end_date
SELECT VARCHAR_1 closing_status
, NUMBER_3 accounting_date
, LIST_ID per_num
FROM CST_LISTS_TEMP
WHERE VARCHAR_1 <> 'TIMEZONE'
AND p_date_num BETWEEN
NUMBER_1 -- start_date
AND NUMBER_2; -- end_date
SELECT oap.acct_period_id
FROM CST_LISTS_TEMP list
, gl_period_statuses glp
, org_acct_periods oap
WHERE VARCHAR_1 <> 'TIMEZONE'
AND p_date_num BETWEEN
list.NUMBER_1 -- start_date
AND list.NUMBER_2 -- end_date
AND list.list_id = glp.effective_period_num
AND glp.application_id = 101
AND glp.ledger_id = p_ledger_id
AND oap.organization_id = p_org_id
AND glp.period_name = oap.period_name;