[Home] [Help]
876: sum(cce.event_quantity) prior_event_quantity
877: FROM mtl_material_transactions mmt,
878: oe_order_lines_all ool,
879: cst_revenue_cogs_match_lines crcml,
880: cst_cogs_events cce,
881: mtl_system_items_b item
882: ,cst_acct_info_v caiv --BUG#7463298
883: ,mtl_parameters mp --BUG#9306124
884: WHERE mmt.transaction_source_type_id = 12
920: AND crcml.cogs_om_line_id = cce.cogs_om_line_id
921: AND crcml.pac_cost_type_id is NULL --BUG 14656876
922: AND cce.event_date <= mmt.transaction_date
923: AND NOT EXISTS (SELECT NULL
924: FROM cst_cogs_events
925: WHERE DECODE(event_type,3,TRUNC(event_date),event_date) <= mmt.transaction_date
926: AND cogs_om_line_id = crcml.cogs_om_line_id
927: AND prior_event_id = cce.parent_event_id)
928: GROUP BY cce.parent_event_id , ool.reference_line_id,
944: sum(cce.event_quantity) prior_event_quantity
945: FROM mtl_material_transactions mmt,
946: oe_order_lines_all ool,
947: cst_revenue_cogs_match_lines crcml,
948: cst_cogs_events cce,
949: mtl_system_items_b item
950: ,cst_acct_info_v caiv --BUG#7463298
951: ,mtl_parameters mp --BUG#9306124
952: WHERE mmt.transaction_source_type_id = 12
987: --}
988: AND crcml.cogs_om_line_id = cce.cogs_om_line_id
989: AND cce.event_date <= mmt.transaction_date
990: AND NOT EXISTS (SELECT NULL
991: FROM cst_cogs_events
992: WHERE DECODE(event_type,3,TRUNC(event_date),event_date) <= mmt.transaction_date
993: AND cogs_om_line_id = crcml.cogs_om_line_id
994: AND prior_event_id = cce.parent_event_id)
995: GROUP BY cce.parent_event_id , ool.reference_line_id,
1092: l_prior_event_id := l_prior_event_id_tbl(i);
1093: END IF;
1094:
1095: -- Insert the RMA marker
1096: INSERT INTO cst_cogs_events (
1097: event_id,
1098: cogs_om_line_id,
1099: event_date,
1100: mmt_transaction_id,
1115: request_id,
1116: program_application_id,
1117: program_id,
1118: program_update_date)
1119: VALUES ( cst_cogs_events_s.nextval,
1120: l_cogs_om_line_id_tbl(i),
1121: l_txn_date_tbl(i),
1122: NULL, -- Quantity placeholder - no MMT transaction
1123: l_prior_percent_tbl(i),
1126: RMA_RECEIPT_PLACEHOLDER,
1127: l_rma_om_line_id_tbl(i),
1128: l_prior_event_qty_tbl(i),
1129: NULL, -- This event is a quantity placeholder, thus is never costed
1130: cst_cogs_events_s.currval,
1131: -- WHO COLUMNS
1132: sysdate,
1133: p_user_id,
1134: sysdate,
1144: l_stmt_num := 30;
1145: debug(l_stmt_num);
1146: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
1147: -- Insert the RMA receipts as quantity events (as opposed to % events)
1148: INSERT INTO cst_cogs_events (
1149: event_id,
1150: cogs_om_line_id,
1151: event_date,
1152: mmt_transaction_id,
1167: request_id,
1168: program_application_id,
1169: program_id,
1170: program_update_date)
1171: VALUES ( cst_cogs_events_s.nextval,
1172: l_cogs_om_line_id_tbl(i),
1173: l_txn_date_tbl(i),
1174: l_mmt_txn_id_tbl(i),
1175: l_prior_percent_tbl(i), -- COGS percentage
1220: -- next event to this new one.
1221: l_stmt_num := 50;
1222: debug(l_stmt_num);
1223: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
1224: UPDATE cst_cogs_events
1225: SET PRIOR_EVENT_ID = l_parent_event_id_tbl(i),
1226: last_update_date = sysdate,
1227: last_updated_by = p_user_id,
1228: last_update_login = p_login_id,
1256: cogs_acct_id,
1257: opm_org_flag,
1258: acct_period_id
1259: )
1260: SELECT cst_cogs_events_s.nextval,
1261: decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
1262: COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
1263: NULL),
1264: cce.cogs_om_line_id,
1274: crcml.cost_group_id,
1275: crcml.cogs_acct_id,
1276: nvl(mp.process_enabled_flag,'N'),
1277: oap.acct_period_id -- acct period ID, I should store this in CCE so I don't keep having to go back to OAP
1278: FROM cst_cogs_events cce,
1279: cst_revenue_cogs_match_lines crcml,
1280: mtl_parameters mp,
1281: org_acct_periods oap,
1282: cst_acct_info_v caiv,
1297:
1298: l_stmt_num := 70;
1299: debug(l_stmt_num);
1300: -- Now insert the quantity adjustment child events
1301: INSERT INTO cst_cogs_events (
1302: event_id,
1303: cogs_om_line_id,
1304: event_date,
1305: mmt_transaction_id,
1588: --}
1589: ,MAX(crrl.rowid) l_rowid
1590: FROM cst_revenue_cogs_match_lines crcml,
1591: cst_revenue_recognition_lines crrl,
1592: cst_cogs_events cce,
1593: mtl_parameters mp,
1594: gl_period_statuses gps,
1595: cst_lists_temp clt,
1596: mtl_system_items msi
1619: --{BUG#6809034
1620: AND cce.mmt_transaction_id = mmt.transaction_id(+)
1621: -- retrieve cce which are not prior to any events -- sum of these event quantity is the quantity to recognize the cost
1622: AND NOT EXISTS (select 'X'
1623: from cst_cogs_events
1624: where event_date <= gps.end_date + EndDateNum + clt.number_1 -- gps1.end_date + EndDateNum + clt.number_1
1625: and cogs_om_line_id = crcml.cogs_om_line_id
1626: and prior_event_id = cce.parent_event_id)
1627: --AND cce.cogs_percentage <> crrl.revenue_recognition_percent -- AR should only send the revenue info once
1831: ,l_min_period_num
1832: FROM gl_period_statuses glp
1833: ,cst_revenue_recognition_lines crrl
1834: ,cst_revenue_cogs_match_lines crcml
1835: ,cst_cogs_events cce
1836: WHERE crrl.acct_period_num = l_per_num_tab(j)
1837: AND crrl.potentially_unmatched_flag = 'Y'
1838: AND crrl.revenue_om_line_id = crcml.revenue_om_line_id
1839: AND crrl.ledger_id = p_ledger_id
2019: END IF;
2020:
2021: l_stmt_num := 60;
2022: debug(l_stmt_num);
2023: -- create COGS events in cst_cogs_events
2024: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
2025: INSERT INTO cst_cogs_events (
2026: event_id,
2027: cogs_om_line_id,
2021: l_stmt_num := 60;
2022: debug(l_stmt_num);
2023: -- create COGS events in cst_cogs_events
2024: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
2025: INSERT INTO cst_cogs_events (
2026: event_id,
2027: cogs_om_line_id,
2028: event_date,
2029: mmt_transaction_id,
2045: program_application_id,
2046: program_id,
2047: program_update_date)
2048: VALUES(
2049: cst_cogs_events_s.nextval,
2050: l_cogs_om_line_id_tbl(i),
2051: --BUG#6809034 -- As JAN-XX is opened the transaction_date is AR recognition event date
2052: --But as the bug is only on the material transaction, this COGS event event_date remains
2053: --untouch to avoid breaking the current logic based on the CCE,CRCML,CRRL for CCE creation
2064: COGS_RECOGNITION_EVENT,
2065: NULL, -- event OM line ID
2066: l_event_quantity_tbl(i),
2067: 'N',
2068: cst_cogs_events_s.currval,
2069: -- WHO COLUMNS
2070: sysdate,
2071: p_user_id,
2072: sysdate,
2160: -- First adjust all future events by simply updating those directly that have not been costed yet
2161: l_stmt_num := 80;
2162: debug(l_stmt_num);
2163: FORALL i IN l_cogs_om_line_id_tbl.FIRST..l_cogs_om_line_id_tbl.LAST
2164: UPDATE cst_cogs_events
2165: SET PRIOR_COGS_PERCENTAGE = l_new_percentage_tbl(i),
2166: PRIOR_EVENT_ID = l_new_event_id_tbl(i),
2167: last_update_date = sysdate,
2168: last_updated_by = p_user_id,
2199: cogs_acct_id,
2200: opm_org_flag,
2201: acct_period_id
2202: )
2203: SELECT cst_cogs_events_s.nextval,
2204: mtl_material_transactions_s.nextval,
2205: cogs_om_line_id,
2206: event_date,
2207: l_prior_percentage_tbl(i), -- could also use cce.prior_cogs_percentage
2215: l_cost_group_id_tbl(i),
2216: l_cogs_acct_id_tbl(i),
2217: l_opm_org_flg_tbl(i),
2218: oap.acct_period_id
2219: FROM cst_cogs_events cce,
2220: org_acct_periods oap
2221: WHERE cogs_om_line_id = l_cogs_om_line_id_tbl(i)
2222: AND prior_event_id = l_prior_event_id_tbl(i)
2223: AND event_id <> l_new_event_id_tbl(i)
2228:
2229: -- Now load the events into CCE from the temp table
2230: l_stmt_num := 100;
2231: debug(l_stmt_num);
2232: INSERT INTO cst_cogs_events (
2233: event_id,
2234: cogs_om_line_id,
2235: event_date,
2236: mmt_transaction_id,
2419:
2420: UPDATE (
2421: SELECT cce.prior_event_id,
2422: cba.adj_event_id
2423: FROM cst_cogs_events cce,
2424: cst_cogs_pct_adj_events_temp cba
2425: WHERE cce.event_id = cba.ftr_event_id)
2426: SET prior_event_id = adj_event_id;
2427:
2744: -- The existing event is the parent; this new sales order issue
2745: -- becomes the child of this existing one.
2746: SELECT event_id
2747: INTO l_parent_cce_id
2748: FROM cst_cogs_events
2749: WHERE cogs_om_line_id = p_cogs_om_line_id
2750: AND parent_event_id = event_id
2751: AND event_type = 1;
2752: END;
2765: l_stmt_num := 60;
2766: debug(l_stmt_num);
2767: -- Insert the sales order issue as the first event in the linked list of COGS
2768: -- events with a recognition % of 0.
2769: INSERT INTO cst_cogs_events (
2770: event_id,
2771: cogs_om_line_id,
2772: event_date,
2773: mmt_transaction_id,
2788: program_application_id,
2789: program_id,
2790: program_update_date)
2791: VALUES (
2792: cst_cogs_events_s.nextval,
2793: p_cogs_om_line_id,
2794: p_transaction_date,
2795: p_mmt_txn_id,
2796: 0, -- COGS %
2798: SO_ISSUE,
2799: p_cogs_om_line_id,
2800: p_quantity,
2801: 'N',
2802: nvl(l_parent_cce_id,cst_cogs_events_s.currval),
2803: -- WHO COLUMNS
2804: sysdate,
2805: p_user_id,
2806: sysdate,
2990: sum(cce.event_quantity) prior_event_quantity
2991: INTO l_prior_event_id,
2992: l_cogs_percentage,
2993: l_prior_event_quantity
2994: FROM cst_cogs_events cce
2995: WHERE p_cogs_om_line_id = cce.cogs_om_line_id
2996: AND cce.event_date <= p_transaction_date
2997: AND cce.parent_event_id NOT IN (SELECT prior_event_id
2998: FROM cst_cogs_events
2994: FROM cst_cogs_events cce
2995: WHERE p_cogs_om_line_id = cce.cogs_om_line_id
2996: AND cce.event_date <= p_transaction_date
2997: AND cce.parent_event_id NOT IN (SELECT prior_event_id
2998: FROM cst_cogs_events
2999: WHERE event_date <= p_transaction_date
3000: AND cogs_om_line_id = p_cogs_om_line_id--changed <= to = for bug 13243263
3001: AND prior_event_id IS NOT NULL)
3002: GROUP BY cce.parent_event_id,
3008: */
3009:
3010: l_stmt_num := 20;
3011: debug(l_stmt_num);
3012: INSERT INTO cst_cogs_events (
3013: event_id,
3014: cogs_om_line_id,
3015: event_date,
3016: mmt_transaction_id,
3031: request_id,
3032: program_application_id,
3033: program_id,
3034: program_update_date)
3035: VALUES ( cst_cogs_events_s.nextval,
3036: p_cogs_om_line_id,
3037: p_transaction_date,
3038: NULL, -- Quantity Placeholder event - no corresponding MMT txn
3039: l_cogs_percentage,
3042: RMA_RECEIPT_PLACEHOLDER,
3043: p_rma_om_line_id,
3044: l_prior_event_quantity,
3045: NULL, -- RMA quantity placeholders are not costed
3046: cst_cogs_events_s.currval,
3047: -- WHO COLUMNS
3048: sysdate,
3049: p_user_id,
3050: sysdate,
3058:
3059: l_stmt_num := 30;
3060: debug(l_stmt_num);
3061: -- Now insert the actual RMA event
3062: INSERT INTO cst_cogs_events (
3063: event_id,
3064: cogs_om_line_id,
3065: event_date,
3066: mmt_transaction_id,
3081: request_id,
3082: program_application_id,
3083: program_id,
3084: program_update_date)
3085: VALUES ( cst_cogs_events_s.nextval,
3086: p_cogs_om_line_id,
3087: p_transaction_date,
3088: p_mmt_txn_id,
3089: l_cogs_percentage,
3127: -- First insert this new event into the linked list by setting the prior event ID of the
3128: -- next event to this new one.
3129: l_stmt_num := 50;
3130: debug(l_stmt_num);
3131: UPDATE cst_cogs_events
3132: SET PRIOR_EVENT_ID = l_parent_event_id,
3133: last_update_date = sysdate,
3134: last_updated_by = p_user_id,
3135: last_update_login = p_login_id,
3159: cogs_acct_id,
3160: opm_org_flag,
3161: acct_period_id
3162: )
3163: SELECT cst_cogs_events_s.nextval,
3164: decode(event_type, COGS_RECOGNITION_EVENT, mtl_material_transactions_s.nextval,
3165: COGS_REC_PERCENT_ADJUSTMENT, mtl_material_transactions_s.nextval,
3166: NULL),
3167: cce.cogs_om_line_id,
3177: crcml.cost_group_id,
3178: crcml.cogs_acct_id,
3179: nvl(mp.process_enabled_flag,'N'),
3180: oap.acct_period_id
3181: FROM cst_cogs_events cce,
3182: cst_revenue_cogs_match_lines crcml,
3183: mtl_parameters mp,
3184: org_acct_periods oap,
3185: cst_acct_info_v caiv,
3199:
3200: -- Then insert from the global temp table into CCE
3201: l_stmt_num := 70;
3202: debug(l_stmt_num);
3203: INSERT INTO cst_cogs_events (
3204: event_id,
3205: cogs_om_line_id,
3206: event_date,
3207: mmt_transaction_id,
3873: End if;
3874:
3875: l_stmt_num := 70;
3876: -- Mark the RMA event in CCE as costed
3877: UPDATE cst_cogs_events
3878: SET costed = NULL
3879: WHERE mmt_transaction_id = p_transaction_id;
3880:
3881: -- End API Body
4046:
4047: -- Retrieve the COGS percentage and prior COGS percentage for this
4048: -- event while marking it as costed
4049: l_stmt_num := 10;
4050: UPDATE cst_cogs_events
4051: SET costed = NULL
4052: WHERE mmt_transaction_id = p_transaction_id
4053: RETURNING cogs_percentage,
4054: prior_cogs_percentage
4215: crcml.unit_overhead_cost,
4216: crcml.unit_cost,
4217: sob.currency_code,
4218: NVL(mmt.transaction_source_id, -1)
4219: FROM cst_cogs_events cce,
4220: cst_revenue_cogs_match_lines crcml,
4221: mtl_parameters mp,
4222: gl_sets_of_books sob,
4223: mtl_material_transactions mmt -- joining here to use the index on costed_flag
4484: 5, l_e5_tbl(i),
4485: 0),0) <> 0;
4486:
4487: l_stmt_num := 40;
4488: -- Update the costed flag in cst_cogs_events
4489: FORALL i IN l_event_id_tbl.FIRST..l_event_id_tbl.LAST
4490: UPDATE cst_cogs_events
4491: SET costed = NULL,
4492: last_update_date = sysdate,
4486:
4487: l_stmt_num := 40;
4488: -- Update the costed flag in cst_cogs_events
4489: FORALL i IN l_event_id_tbl.FIRST..l_event_id_tbl.LAST
4490: UPDATE cst_cogs_events
4491: SET costed = NULL,
4492: last_update_date = sysdate,
4493: last_updated_by = p_user_id,
4494: last_update_login = p_login_id,
4730: BEGIN
4731: SELECT mmt.trx_source_line_id
4732: INTO l_cogs_om_line_id
4733: FROM mtl_material_transactions mmt,
4734: cst_cogs_events cce
4735: WHERE mmt.transaction_id = p_transaction_id
4736: AND cce.mmt_transaction_id = mmt.transaction_id;
4737: EXCEPTION
4738: WHEN NO_DATA_FOUND THEN
5184: SELECT cogs_percentage,
5185: prior_cogs_percentage
5186: INTO l_new_percentage,
5187: l_prior_percentage
5188: FROM cst_cogs_events
5189: WHERE mmt_transaction_id = p_ae_txn_rec.transaction_id;
5190:
5191:
5192: -- Loop for each cost element
6085: sum(decode(mta.accounting_line_type, 35, MTA.BASE_TRANSACTION_VALUE,0)) COGS_BALANCE,
6086: sum(decode(mta.accounting_line_type, 36, MTA.BASE_TRANSACTION_VALUE,0)) DEF_COGS_BALANCE
6087: FROM
6088: CST_REVENUE_COGS_MATCH_LINES CRCML,
6089: CST_COGS_EVENTS CCE,
6090: GL_PERIOD_STATUSES GPS,
6091: OE_ORDER_LINES_ALL OOL,
6092: OE_ORDER_HEADERS_ALL OOH,
6093: MTL_TRANSACTION_ACCOUNTS MTA
6133: sum(decode(mta.accounting_line_type, 35, MTA.BASE_TRANSACTION_VALUE,0)) COGS_BALANCE,
6134: sum(decode(mta.accounting_line_type, 36, MTA.BASE_TRANSACTION_VALUE,0)) DEF_COGS_BALANCE
6135: FROM
6136: CST_REVENUE_COGS_MATCH_LINES CRCML,
6137: CST_COGS_EVENTS CCE,
6138: GL_PERIOD_STATUSES GPS,
6139: OE_ORDER_LINES_ALL OOL,
6140: OE_ORDER_HEADERS_ALL OOH,
6141: MTL_TRANSACTION_ACCOUNTS MTA
6167: gl_code_combinations_kfv cogs_acct,
6168: gl_code_combinations_kfv dcogs_acct,
6169: ra_customer_trx_lines_all srclines,
6170: HZ_CUST_ACCOUNTS cust_acct,
6171: cst_cogs_events cce1
6172: WHERE
6173: MSI.INVENTORY_ITEM_ID = Q.INVENTORY_ITEM_ID
6174: AND MSI.ORGANIZATION_ID = Q.ORGANIZATION_ID
6175: AND Q2.ORGANIZATION_ID = Q.ORGANIZATION_ID
6465: sum(decode(CAL.ae_line_type_code, 35, nvl(CAL.accounted_dr,0) - nvl(CAL.accounted_cr,0),0)) COGS_BALANCE,
6466: sum(decode(CAL.ae_line_type_code, 36, nvl(CAL.accounted_dr,0) - nvl(CAL.accounted_cr,0),0)) DEF_COGS_BALANCE
6467: FROM
6468: CST_REVENUE_COGS_MATCH_LINES CRCML,
6469: CST_COGS_EVENTS CCE,
6470: OE_ORDER_LINES_ALL OOL,
6471: OE_ORDER_HEADERS_ALL OOH,
6472: CST_COST_GROUP_ASSIGNMENTS CCGA,
6473: CST_PAC_PERIODS CPP,
6503: gl_code_combinations_kfv cogs_acct,
6504: gl_code_combinations_kfv dcogs_acct,
6505: ra_customer_trx_lines_all srclines,
6506: HZ_CUST_ACCOUNTS cust_acct,
6507: cst_cogs_events cce1,
6508: CST_PAC_PERIODS CPP
6509: WHERE
6510: MSI.INVENTORY_ITEM_ID = Q.INVENTORY_ITEM_ID
6511: AND MSI.ORGANIZATION_ID = Q.ORGANIZATION_ID
7087: PROCEDURE update_one_set_crrl
7088: (p_rowid_tab IN DBMS_SQL.VARCHAR2_TABLE) IS
7089: l_gp_id NUMBER;
7090: BEGIN
7091: SELECT cst_cogs_events_s.nextval
7092: INTO l_gp_id
7093: FROM dual;
7094:
7095: FORALL j IN p_rowid_tab.FIRST .. p_rowid_tab.LAST
7568: SELECT crrl.rowid l_rowid
7569: , DECODE(crrl.acct_period_num,latest_rev.mper,'Y',NULL) flag
7570: FROM cst_revenue_recognition_lines CRRL
7571: , cst_revenue_cogs_match_lines crcml
7572: , cst_cogs_events cce
7573: , gl_period_statuses glp
7574: , (SELECT MAX(crrl3.acct_period_num) mper
7575: , crrl3.revenue_om_line_id
7576: FROM cst_revenue_recognition_lines crrl3