[Home] [Help]
603: mmt.inventory_item_id,
604: mmt.transaction_date,
605: mmt.cost_group_id,
606: (-1*mmt.primary_quantity) quantity
607: FROM mtl_material_transactions mmt,
608: mtl_parameters mp,
609: mtl_secondary_inventories msi,
610: mtl_system_items_b item
611: ,cst_acct_info_v caiv --BUG#7463298
782: (-1*mmt.primary_quantity) event_quantity,
783: cce.parent_event_id prior_event_id,
784: cce.cogs_percentage cogs_percentage,
785: sum(cce.event_quantity) prior_event_quantity
786: FROM mtl_material_transactions mmt,
787: oe_order_lines_all ool,
788: cst_revenue_cogs_match_lines crcml,
789: cst_cogs_events cce,
790: mtl_secondary_inventories msi,
1020: -- to the revenue / COGS matching data model.
1021: l_stmt_num := 40;
1022: debug(l_stmt_num);
1023: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
1024: UPDATE mtl_material_transactions
1025: SET cogs_recognition_percent = l_prior_percent_tbl(i),
1026: last_update_date = sysdate,
1027: last_updated_by = p_user_id,
1028: last_update_login = p_login_id,
1079: opm_org_flag,
1080: acct_period_id
1081: )
1082: SELECT cst_cogs_events_s.nextval,
1083: decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
1084: COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
1085: NULL),
1086: cce.cogs_om_line_id,
1087: l_rma_om_line_id_tbl(i),
1080: acct_period_id
1081: )
1082: SELECT cst_cogs_events_s.nextval,
1083: decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
1084: COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
1085: NULL),
1086: cce.cogs_om_line_id,
1087: l_rma_om_line_id_tbl(i),
1088: cce.event_date,
1411: gl_period_statuses gps,
1412: cst_lists_temp clt,
1413: mtl_system_items msi
1414: --{BUG#6809034
1415: ,mtl_material_transactions mmt
1416: --}
1417: WHERE crrl.ledger_id = p_sob_id
1418: AND crrl.acct_period_num = p_process_acct_period_num
1419: AND crrl.potentially_unmatched_flag = 'Y' -- Indexed column should substantially reduce the rows
1991: --untouch to avoid breaking the current logic based on the CCE,CRCML,CRRL for CCE creation
1992: --If the problem is hitting the accounting unproper such as to much COGS recognized or not enough
1993: --this can be the starting point
1994: nvl(l_alternate_event_date+.99999+l_date_offset_tbl(i),l_event_date_tbl(i)),
1995: mtl_material_transactions_s.nextval,
1996: l_new_percentage_tbl(i),
1997: l_prior_percentage_tbl(i),
1998: l_prior_event_id_tbl(i),
1999: COGS_RECOGNITION_EVENT,
2151: opm_org_flag,
2152: acct_period_id
2153: )
2154: SELECT cst_cogs_events_s.nextval,
2155: mtl_material_transactions_s.nextval,
2156: cogs_om_line_id,
2157: event_date,
2158: l_prior_percentage_tbl(i), -- could also use cce.prior_cogs_percentage
2159: l_new_percentage_tbl(i),
2777: l_stmt_num := 70;
2778: debug(l_stmt_num);
2779: -- Mark the cogs percentage column in the MMT transaction, thus indicating that it has been added
2780: -- to the revenue / COGS matching data model.
2781: UPDATE mtl_material_transactions
2782: SET cogs_recognition_percent = 0,
2783: last_update_date = sysdate,
2784: last_updated_by = p_user_id,
2785: last_update_login = p_login_id,
3085: -- Mark the cogs percentage column in the MMT transaction, thus indicating that it has been added
3086: -- to the revenue / COGS matching data model.
3087: l_stmt_num := 40;
3088: debug(l_stmt_num);
3089: UPDATE mtl_material_transactions
3090: SET cogs_recognition_percent = l_cogs_percentage,
3091: last_update_date = sysdate,
3092: last_updated_by = p_user_id,
3093: last_update_login = p_login_id,
3136: opm_org_flag,
3137: acct_period_id
3138: )
3139: SELECT cst_cogs_events_s.nextval,
3140: decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
3141: COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
3142: NULL),
3143: cce.cogs_om_line_id,
3144: p_rma_om_line_id,
3137: acct_period_id
3138: )
3139: SELECT cst_cogs_events_s.nextval,
3140: decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
3141: COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
3142: NULL),
3143: cce.cogs_om_line_id,
3144: p_rma_om_line_id,
3145: cce.event_date,
4155: FROM cst_cogs_events cce,
4156: cst_revenue_cogs_match_lines crcml,
4157: mtl_parameters mp,
4158: gl_sets_of_books sob,
4159: mtl_material_transactions mmt -- joining here to use the index on costed_flag
4160: WHERE
4161: cce.mmt_transaction_id = mmt.transaction_id
4162: AND mmt.costed_flag = 'N'
4163: AND mmt.transaction_action_id = 36
4433:
4434: l_stmt_num := 50;
4435: -- Update the costed flag in MMT
4436: FORALL i IN l_event_id_tbl.FIRST..l_event_id_tbl.LAST
4437: UPDATE mtl_material_transactions
4438: SET costed_flag = NULL,
4439: last_update_date = sysdate,
4440: last_updated_by = p_user_id,
4441: last_update_login = p_login_id,
4652: -- been inserted into CCE, and should not be inserted into CRCML.
4653: BEGIN
4654: SELECT mmt.trx_source_line_id
4655: INTO l_cogs_om_line_id
4656: FROM mtl_material_transactions mmt,
4657: cst_cogs_events cce
4658: WHERE mmt.transaction_id = p_transaction_id
4659: AND cce.mmt_transaction_id = mmt.transaction_id;
4660: EXCEPTION