[Home] [Help]
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,
791: mtl_system_items_b item
792: ,cst_acct_info_v caiv --BUG#7463298
793: WHERE mmt.transaction_source_type_id = 12
810: --}
811: AND crcml.cogs_om_line_id = cce.cogs_om_line_id
812: AND cce.event_date <= mmt.transaction_date
813: AND NOT EXISTS (SELECT 'X'
814: FROM cst_cogs_events
815: WHERE event_date <= mmt.transaction_date
816: AND cogs_om_line_id = crcml.cogs_om_line_id
817: AND prior_event_id = cce.parent_event_id)
818: GROUP BY cce.parent_event_id , ool.reference_line_id,
915: l_prior_event_id := l_prior_event_id_tbl(i);
916: END IF;
917:
918: -- Insert the RMA marker
919: INSERT INTO cst_cogs_events (
920: event_id,
921: cogs_om_line_id,
922: event_date,
923: mmt_transaction_id,
938: request_id,
939: program_application_id,
940: program_id,
941: program_update_date)
942: VALUES ( cst_cogs_events_s.nextval,
943: l_cogs_om_line_id_tbl(i),
944: l_txn_date_tbl(i),
945: NULL, -- Quantity placeholder - no MMT transaction
946: l_prior_percent_tbl(i),
949: RMA_RECEIPT_PLACEHOLDER,
950: l_rma_om_line_id_tbl(i),
951: l_prior_event_qty_tbl(i),
952: NULL, -- This event is a quantity placeholder, thus is never costed
953: cst_cogs_events_s.currval,
954: -- WHO COLUMNS
955: sysdate,
956: p_user_id,
957: sysdate,
967: l_stmt_num := 30;
968: debug(l_stmt_num);
969: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
970: -- Insert the RMA receipts as quantity events (as opposed to % events)
971: INSERT INTO cst_cogs_events (
972: event_id,
973: cogs_om_line_id,
974: event_date,
975: mmt_transaction_id,
990: request_id,
991: program_application_id,
992: program_id,
993: program_update_date)
994: VALUES ( cst_cogs_events_s.nextval,
995: l_cogs_om_line_id_tbl(i),
996: l_txn_date_tbl(i),
997: l_mmt_txn_id_tbl(i),
998: l_prior_percent_tbl(i), -- COGS percentage
1043: -- next event to this new one.
1044: l_stmt_num := 50;
1045: debug(l_stmt_num);
1046: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
1047: UPDATE cst_cogs_events
1048: SET PRIOR_EVENT_ID = l_parent_event_id_tbl(i),
1049: last_update_date = sysdate,
1050: last_updated_by = p_user_id,
1051: last_update_login = p_login_id,
1078: cogs_acct_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,
1096: crcml.cost_group_id,
1097: crcml.cogs_acct_id,
1098: nvl(mp.process_enabled_flag,'N'),
1099: oap.acct_period_id -- acct period ID, I should store this in CCE so I don't keep having to go back to OAP
1100: FROM cst_cogs_events cce,
1101: cst_revenue_cogs_match_lines crcml,
1102: mtl_parameters mp,
1103: org_acct_periods oap,
1104: cst_acct_info_v caiv,
1118:
1119: l_stmt_num := 70;
1120: debug(l_stmt_num);
1121: -- Now insert the quantity adjustment child events
1122: INSERT INTO cst_cogs_events (
1123: event_id,
1124: cogs_om_line_id,
1125: event_date,
1126: mmt_transaction_id,
1405: ,MAX(mmt.acct_period_id) mmt_period_id
1406: --}
1407: FROM cst_revenue_cogs_match_lines crcml,
1408: cst_revenue_recognition_lines crrl,
1409: cst_cogs_events cce,
1410: mtl_parameters mp,
1411: gl_period_statuses gps,
1412: cst_lists_temp clt,
1413: mtl_system_items msi
1435: --{BUG#6809034
1436: AND cce.mmt_transaction_id = mmt.transaction_id(+)
1437: --}
1438: AND NOT EXISTS (select 'X'
1439: from cst_cogs_events
1440: where event_date <= gps.end_date + .99999 + clt.number_1
1441: and cogs_om_line_id = crcml.cogs_om_line_id
1442: and prior_event_id = cce.parent_event_id)
1443: AND cce.cogs_percentage <> crrl.revenue_recognition_percent
1636: -- crrl.inventory_item_id
1637: -- FROM cst_revenue_cogs_match_lines crcml,
1638: -- cst_lists_temp clt,
1639: -- gl_period_statuses gps,
1640: -- cst_cogs_events cce,
1641: -- cst_revenue_recognition_lines crrl
1642: -- WHERE crcml.pac_cost_type_id IS NULL
1643: -- AND clt.list_id = crcml.organization_id
1644: -- AND gps.application_id = 222
1752: SELECT min(crrl.acct_period_num)
1753: INTO l_revenue_acct_period_num
1754: FROM cst_revenue_recognition_lines crrl,
1755: cst_revenue_cogs_match_lines crcml,
1756: cst_cogs_events cce,
1757: cst_lists_temp clt,
1758: gl_period_statuses gps
1759: --{
1760: -- , cst_cogs_events cce2
1756: cst_cogs_events cce,
1757: cst_lists_temp clt,
1758: gl_period_statuses gps
1759: --{
1760: -- , cst_cogs_events cce2
1761: --}
1762: WHERE crrl.ledger_id = l_sob --l_sob.set_of_books_id
1763: AND crrl.potentially_unmatched_flag = 'Y'
1764: AND crrl.revenue_om_line_id = crcml.revenue_om_line_id
1776: AND gps.set_of_books_id = l_sob --l_sob.set_of_books_id
1777: AND cce.event_date <= gps.end_date + .99999 + clt.number_1
1778: AND clt.list_id = crcml.organization_id
1779: AND NOT EXISTS (select 'X'
1780: from cst_cogs_events
1781: where event_date <= gps.end_date + .99999 + clt.number_1
1782: and cogs_om_line_id = crcml.cogs_om_line_id
1783: and prior_event_id = cce.parent_event_id)
1784: AND cce.cogs_percentage <> (
1794: SELECT min(crrl.acct_period_num)
1795: INTO l_revenue_acct_period_num
1796: FROM cst_revenue_recognition_lines crrl,
1797: cst_revenue_cogs_match_lines crcml,
1798: cst_cogs_events cce,
1799: cst_lists_temp clt,
1800: gl_period_statuses gps
1801: WHERE crrl.ledger_id = l_sob --l_sob.set_of_books_id
1802: AND crrl.potentially_unmatched_flag = 'Y'
1808: AND gps.set_of_books_id = l_sob --l_sob.set_of_books_id
1809: AND cce.event_date <= gps.end_date + .99999 + clt.number_1
1810: AND clt.list_id = crcml.organization_id
1811: AND NOT EXISTS (select 'X'
1812: from cst_cogs_events
1813: where event_date <= gps.end_date + .99999 + clt.number_1
1814: and cogs_om_line_id = crcml.cogs_om_line_id
1815: and prior_event_id = cce.parent_event_id)
1816: AND cce.cogs_percentage <> (
1957: END IF;
1958:
1959: l_stmt_num := 60;
1960: debug(l_stmt_num);
1961: -- create COGS events in cst_cogs_events
1962: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
1963: INSERT INTO cst_cogs_events (
1964: event_id,
1965: cogs_om_line_id,
1959: l_stmt_num := 60;
1960: debug(l_stmt_num);
1961: -- create COGS events in cst_cogs_events
1962: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
1963: INSERT INTO cst_cogs_events (
1964: event_id,
1965: cogs_om_line_id,
1966: event_date,
1967: mmt_transaction_id,
1983: program_application_id,
1984: program_id,
1985: program_update_date)
1986: VALUES(
1987: cst_cogs_events_s.nextval,
1988: l_cogs_om_line_id_tbl(i),
1989: --BUG#6809034 -- As JAN-XX is opened the transaction_date is AR recognition event date
1990: --But as the bug is only on the material transaction, this COGS event event_date remains
1991: --untouch to avoid breaking the current logic based on the CCE,CRCML,CRRL for CCE creation
1999: COGS_RECOGNITION_EVENT,
2000: NULL, -- event OM line ID
2001: l_event_quantity_tbl(i),
2002: 'N',
2003: cst_cogs_events_s.currval,
2004: -- WHO COLUMNS
2005: sysdate,
2006: p_user_id,
2007: sysdate,
2111: -- First adjust all future events by simply updating those directly that have not been costed yet
2112: l_stmt_num := 80;
2113: debug(l_stmt_num);
2114: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
2115: UPDATE cst_cogs_events
2116: SET PRIOR_COGS_PERCENTAGE = l_new_percentage_tbl(i),
2117: PRIOR_EVENT_ID = l_new_event_id_tbl(i),
2118: last_update_date = sysdate,
2119: last_updated_by = p_user_id,
2150: cogs_acct_id,
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
2166: l_cost_group_id_tbl(i),
2167: l_cogs_acct_id_tbl(i),
2168: l_opm_org_flg_tbl(i),
2169: oap.acct_period_id
2170: FROM cst_cogs_events cce,
2171: org_acct_periods oap
2172: WHERE cogs_om_line_id = l_cogs_om_line_id_tbl(i)
2173: AND prior_event_id = l_prior_event_id_tbl(i)
2174: AND event_id <> l_new_event_id_tbl(i)
2179:
2180: -- Now load the events into CCE from the temp table
2181: l_stmt_num := 100;
2182: debug(l_stmt_num);
2183: INSERT INTO cst_cogs_events (
2184: event_id,
2185: cogs_om_line_id,
2186: event_date,
2187: mmt_transaction_id,
2371:
2372: UPDATE (
2373: SELECT cce.prior_event_id,
2374: cba.adj_event_id
2375: FROM cst_cogs_events cce,
2376: cst_cogs_pct_adj_events_temp cba
2377: WHERE cce.event_id = cba.ftr_event_id)
2378: SET prior_event_id = adj_event_id;
2379:
2707: -- The existing event is the parent; this new sales order issue
2708: -- becomes the child of this existing one.
2709: SELECT event_id
2710: INTO l_parent_cce_id
2711: FROM cst_cogs_events
2712: WHERE cogs_om_line_id = p_cogs_om_line_id
2713: AND parent_event_id = event_id;
2714: END;
2715:
2727: l_stmt_num := 60;
2728: debug(l_stmt_num);
2729: -- Insert the sales order issue as the first event in the linked list of COGS
2730: -- events with a recognition % of 0.
2731: INSERT INTO cst_cogs_events (
2732: event_id,
2733: cogs_om_line_id,
2734: event_date,
2735: mmt_transaction_id,
2750: program_application_id,
2751: program_id,
2752: program_update_date)
2753: VALUES (
2754: cst_cogs_events_s.nextval,
2755: p_cogs_om_line_id,
2756: p_transaction_date,
2757: p_mmt_txn_id,
2758: 0, -- COGS %
2760: SO_ISSUE,
2761: p_cogs_om_line_id,
2762: p_quantity,
2763: 'N',
2764: nvl(l_parent_cce_id,cst_cogs_events_s.currval),
2765: -- WHO COLUMNS
2766: sysdate,
2767: p_user_id,
2768: sysdate,
2966: sum(cce.event_quantity) prior_event_quantity
2967: INTO l_prior_event_id,
2968: l_cogs_percentage,
2969: l_prior_event_quantity
2970: FROM cst_cogs_events cce
2971: WHERE p_cogs_om_line_id = cce.cogs_om_line_id
2972: AND cce.event_date <= p_transaction_date
2973: AND cce.parent_event_id NOT IN (SELECT prior_event_id
2974: FROM cst_cogs_events
2970: FROM cst_cogs_events cce
2971: WHERE p_cogs_om_line_id = cce.cogs_om_line_id
2972: AND cce.event_date <= p_transaction_date
2973: AND cce.parent_event_id NOT IN (SELECT prior_event_id
2974: FROM cst_cogs_events
2975: WHERE event_date <= p_transaction_date
2976: AND cogs_om_line_id <= p_cogs_om_line_id
2977: AND prior_event_id IS NOT NULL)
2978: GROUP BY cce.parent_event_id,
2984: */
2985:
2986: l_stmt_num := 20;
2987: debug(l_stmt_num);
2988: INSERT INTO cst_cogs_events (
2989: event_id,
2990: cogs_om_line_id,
2991: event_date,
2992: mmt_transaction_id,
3007: request_id,
3008: program_application_id,
3009: program_id,
3010: program_update_date)
3011: VALUES ( cst_cogs_events_s.nextval,
3012: p_cogs_om_line_id,
3013: p_transaction_date,
3014: NULL, -- Quantity Placeholder event - no corresponding MMT txn
3015: l_cogs_percentage,
3018: RMA_RECEIPT_PLACEHOLDER,
3019: p_rma_om_line_id,
3020: l_prior_event_quantity,
3021: NULL, -- RMA quantity placeholders are not costed
3022: cst_cogs_events_s.currval,
3023: -- WHO COLUMNS
3024: sysdate,
3025: p_user_id,
3026: sysdate,
3034:
3035: l_stmt_num := 30;
3036: debug(l_stmt_num);
3037: -- Now insert the actual RMA event
3038: INSERT INTO cst_cogs_events (
3039: event_id,
3040: cogs_om_line_id,
3041: event_date,
3042: mmt_transaction_id,
3057: request_id,
3058: program_application_id,
3059: program_id,
3060: program_update_date)
3061: VALUES ( cst_cogs_events_s.nextval,
3062: p_cogs_om_line_id,
3063: p_transaction_date,
3064: p_mmt_txn_id,
3065: l_cogs_percentage,
3103: -- First insert this new event into the linked list by setting the prior event ID of the
3104: -- next event to this new one.
3105: l_stmt_num := 50;
3106: debug(l_stmt_num);
3107: UPDATE cst_cogs_events
3108: SET PRIOR_EVENT_ID = l_parent_event_id,
3109: last_update_date = sysdate,
3110: last_updated_by = p_user_id,
3111: last_update_login = p_login_id,
3135: cogs_acct_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,
3153: crcml.cost_group_id,
3154: crcml.cogs_acct_id,
3155: nvl(mp.process_enabled_flag,'N'),
3156: oap.acct_period_id
3157: FROM cst_cogs_events cce,
3158: cst_revenue_cogs_match_lines crcml,
3159: mtl_parameters mp,
3160: org_acct_periods oap,
3161: cst_acct_info_v caiv,
3175:
3176: -- Then insert from the global temp table into CCE
3177: l_stmt_num := 70;
3178: debug(l_stmt_num);
3179: INSERT INTO cst_cogs_events (
3180: event_id,
3181: cogs_om_line_id,
3182: event_date,
3183: mmt_transaction_id,
3809: END LOOP;
3810:
3811: l_stmt_num := 70;
3812: -- Mark the RMA event in CCE as costed
3813: UPDATE cst_cogs_events
3814: SET costed = NULL
3815: WHERE mmt_transaction_id = p_transaction_id;
3816:
3817: -- End API Body
3982:
3983: -- Retrieve the COGS percentage and prior COGS percentage for this
3984: -- event while marking it as costed
3985: l_stmt_num := 10;
3986: UPDATE cst_cogs_events
3987: SET costed = NULL
3988: WHERE mmt_transaction_id = p_transaction_id
3989: RETURNING cogs_percentage,
3990: prior_cogs_percentage
4151: crcml.unit_overhead_cost,
4152: crcml.unit_cost,
4153: sob.currency_code,
4154: NVL(mmt.transaction_source_id, -1)
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
4420: 5, l_e5_tbl(i),
4421: 0),0) <> 0;
4422:
4423: l_stmt_num := 40;
4424: -- Update the costed flag in cst_cogs_events
4425: FORALL i IN l_event_id_tbl.FIRST..l_event_id_tbl.LAST
4426: UPDATE cst_cogs_events
4427: SET costed = NULL,
4428: last_update_date = sysdate,
4422:
4423: l_stmt_num := 40;
4424: -- Update the costed flag in cst_cogs_events
4425: FORALL i IN l_event_id_tbl.FIRST..l_event_id_tbl.LAST
4426: UPDATE cst_cogs_events
4427: SET costed = NULL,
4428: last_update_date = sysdate,
4429: last_updated_by = p_user_id,
4430: last_update_login = p_login_id,
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
4661: WHEN NO_DATA_FOUND THEN
5070: SELECT cogs_percentage,
5071: prior_cogs_percentage
5072: INTO l_new_percentage,
5073: l_prior_percentage
5074: FROM cst_cogs_events
5075: WHERE mmt_transaction_id = p_ae_txn_rec.transaction_id;
5076:
5077:
5078: -- Loop for each cost element
5965: sum(decode(mta.accounting_line_type, 35, MTA.BASE_TRANSACTION_VALUE,0)) COGS_BALANCE,
5966: sum(decode(mta.accounting_line_type, 36, MTA.BASE_TRANSACTION_VALUE,0)) DEF_COGS_BALANCE
5967: FROM
5968: CST_REVENUE_COGS_MATCH_LINES CRCML,
5969: CST_COGS_EVENTS CCE,
5970: GL_PERIOD_STATUSES GPS,
5971: OE_ORDER_LINES_ALL OOL,
5972: OE_ORDER_HEADERS_ALL OOH,
5973: MTL_TRANSACTION_ACCOUNTS MTA
6001: gl_code_combinations_kfv cogs_acct,
6002: gl_code_combinations_kfv dcogs_acct,
6003: ra_customer_trx_lines_all srclines,
6004: HZ_CUST_ACCOUNTS cust_acct,
6005: cst_cogs_events cce1
6006: WHERE
6007: MSI.INVENTORY_ITEM_ID = Q.INVENTORY_ITEM_ID
6008: AND MSI.ORGANIZATION_ID = Q.ORGANIZATION_ID
6009: AND cogs_acct.code_combination_id = Q.cogs_acct_id
6176: sum(decode(CAL.ae_line_type_code, 35, nvl(CAL.accounted_dr,0) - nvl(CAL.accounted_cr,0),0)) COGS_BALANCE,
6177: sum(decode(CAL.ae_line_type_code, 36, nvl(CAL.accounted_dr,0) - nvl(CAL.accounted_cr,0),0)) DEF_COGS_BALANCE
6178: FROM
6179: CST_REVENUE_COGS_MATCH_LINES CRCML,
6180: CST_COGS_EVENTS CCE,
6181: OE_ORDER_LINES_ALL OOL,
6182: OE_ORDER_HEADERS_ALL OOH,
6183: CST_COST_GROUP_ASSIGNMENTS CCGA,
6184: CST_PAC_PERIODS CPP,
6214: gl_code_combinations_kfv cogs_acct,
6215: gl_code_combinations_kfv dcogs_acct,
6216: ra_customer_trx_lines_all srclines,
6217: HZ_CUST_ACCOUNTS cust_acct,
6218: cst_cogs_events cce1
6219: WHERE
6220: MSI.INVENTORY_ITEM_ID = Q.INVENTORY_ITEM_ID
6221: AND MSI.ORGANIZATION_ID = Q.ORGANIZATION_ID
6222: AND cogs_acct.code_combination_id = Q.cogs_acct_id
6743: PROCEDURE update_one_set_crrl
6744: (p_rowid_tab IN DBMS_SQL.VARCHAR2_TABLE) IS
6745: l_gp_id NUMBER;
6746: BEGIN
6747: SELECT cst_cogs_events_s.nextval
6748: INTO l_gp_id
6749: FROM dual;
6750:
6751: FORALL j IN p_rowid_tab.FIRST .. p_rowid_tab.LAST