[Home] [Help]
487: FROM oe_order_lines_all OOLA, /*BUG 5768680 Changes introduced to improve performance*/
488: oe_order_headers_all OOHA, /* of the layer cost worker*/
489: mtl_sales_orders MSO,
490: mtl_material_transactions MMT,
491: mtl_cst_actual_cost_details MCACD
492: WHERE OOLA.line_id = l_so_line_id
493: AND OOHA.header_id = OOLA.header_id
494: AND MSO.segment1 = TO_CHAR(OOHA.order_number) -- extraneous MSOs are possible
495: AND MMT.transaction_source_id = MSO.sales_order_id
781: fnd_file.put_line(fnd_file.log,'Average Cost Update <<<');
782: end if;
783:
784: l_stmt_num := 10;
785: -- First insert records into mtl_cst_actual_cost_details.
786:
787: insert into mtl_cst_actual_cost_details (
788: transaction_id,
789: organization_id,
783:
784: l_stmt_num := 10;
785: -- First insert records into mtl_cst_actual_cost_details.
786:
787: insert into mtl_cst_actual_cost_details (
788: transaction_id,
789: organization_id,
790: layer_id,
791: cost_element_id,
920: l_stmt_num := 20;
921:
922: /* select count(*)
923: into l_neg_cost
924: from mtl_cst_actual_cost_details
925: where transaction_id = i_txn_id
926: and organization_id = i_org_id
927: and layer_id = i_layer_id
928: and new_cost < 0;
932: end if; */ -- removed for bug #4005770
933:
934: /********************************************************************
935: ** Delete from cst_layer_cost_details and insert the new rows **
936: ** from mtl_cst_actual_cost_details. **
937: ********************************************************************/
938: l_stmt_num := 30;
939:
940: Delete from cst_layer_cost_details
967: i_prg_appl_id,
968: i_prg_id,
969: sysdate,
970: cacd.new_cost
971: from mtl_cst_actual_cost_details cacd
972: where cacd.transaction_id = i_txn_id
973: and cacd.organization_id = i_org_id
974: and cacd.layer_id = i_layer_id
975: and cacd.insertion_flag = 'Y'
1002: i_prg_appl_id,
1003: i_prg_id,
1004: sysdate,
1005: cacd.new_cost
1006: from mtl_cst_actual_cost_details cacd
1007: where cacd.transaction_id = i_txn_id
1008: and cacd.organization_id = i_org_id
1009: and cacd.layer_id = i_layer_id
1010: and cacd.insertion_flag = 'Y'
1008: and cacd.organization_id = i_org_id
1009: and cacd.layer_id = i_layer_id
1010: and cacd.insertion_flag = 'Y'
1011: and cacd.cost_element_id = (select min(cost_element_id)
1012: from mtl_cst_actual_cost_details
1013: where transaction_id = i_txn_id
1014: and organization_id = i_org_id
1015: and layer_id = i_layer_id
1016: and insertion_flag = 'Y');
1421: end if;
1422: end if;
1423:
1424: -- If we are changing layers, then need to create another layer information
1425: -- in mtl_cst_actual_cost_details.
1426: l_stmt_num := 80;
1427:
1428: if (l_layer_chg = 1) then
1429: if (g_debug = 'Y') then
1429: if (g_debug = 'Y') then
1430: fnd_file.put_line(fnd_file.log,'Layer change');
1431: end if;
1432:
1433: insert into mtl_cst_actual_cost_details(
1434: transaction_id,
1435: organization_id,
1436: layer_id,
1437: cost_element_id,
1475: cacd.actual_cost,
1476: 'Y',
1477: 0,
1478: 'N'
1479: from mtl_cst_actual_cost_details cacd
1480: where transaction_id = i_txn_id
1481: and organization_id = i_org_id
1482: and layer_id = l_from_layer;
1483:
1694: -- store the current cost of the item (from CLCD) into the return sub.
1695: --
1696: l_stmt_num := 150;
1697:
1698: delete from mtl_cst_actual_cost_details
1699: where transaction_id = i_txn_id
1700: and layer_id = l_from_layer;
1701:
1702: l_stmt_num := 160;
1699: where transaction_id = i_txn_id
1700: and layer_id = l_from_layer;
1701:
1702: l_stmt_num := 160;
1703: insert into mtl_cst_actual_cost_details(
1704: transaction_id,
1705: organization_id,
1706: layer_id,
1707: cost_element_id,
1936: SUM(NVL(MCACD.actual_cost,0)),
1937: SUM(NVL(MCACD.prior_cost,0)),
1938: SUM(NVL(MCACD.new_cost,0)),
1939: SUM(NVL(MCACD.variance_amount,0))
1940: FROM mtl_cst_actual_cost_details MCACD
1941: WHERE MCACD.transaction_id = i_txn_id
1942: AND MCACD.layer_id = l_to_layer
1943: )
1944: WHERE MMT.transaction_id = l_txf_txn_id
2086: if (l_err_num <> 0) then
2087: raise process_error;
2088: end if;
2089:
2090: insert into mtl_cst_actual_cost_details(
2091: transaction_id,
2092: organization_id,
2093: layer_id,
2094: cost_element_id,
2132: cacd.new_cost,
2133: 'N', -- check
2134: 0,
2135: 'N'
2136: from mtl_cst_actual_cost_details cacd
2137: where transaction_id = i_txn_id
2138: and organization_id = i_org_id
2139: and layer_id = i_txfr_layer_id
2140: and transaction_action_id = i_txn_action_id;
2168:
2169: l_stmt_num := 20;
2170:
2171: -- insert a row in MCACD for WIP issue transaction
2172: insert into mtl_cst_actual_cost_details(
2173: transaction_id,
2174: organization_id,
2175: layer_id,
2176: cost_element_id,
2214: cacd.new_cost,
2215: 'N', -- check
2216: 0,
2217: 'N'
2218: from mtl_cst_actual_cost_details cacd
2219: where transaction_id = i_txn_id
2220: and organization_id = i_org_id
2221: and layer_id = i_txfr_layer_id
2222: and transaction_action_id = i_txn_action_id;
2437: --
2438: -- Make sure the mcacd should not have any -ve cost
2439: --
2440:
2441: insert into mtl_cst_actual_cost_details (
2442: transaction_id,
2443: organization_id,
2444: layer_id,
2445: cost_element_id,
2490: /* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412
2491:
2492: if (g_debug = 'Y') then
2493: select count(*) into l_count
2494: from mtl_cst_actual_cost_details
2495: where transaction_id = i_txn_id
2496: and organization_id = i_org_id;
2497: fnd_file.put_line(fnd_file.log,'>>>MCACD: '||to_char(l_count)||' i_txn_id: '||to_char(i_txn_id)||' i_org_id:'||to_char(i_org_id));
2498: end if;
2502: * for scrap transactions, no transaction cost means 0 cost. */
2503:
2504: l_ret_val := 0;
2505: /********************************************************************
2506: ** Create detail rows in MTL_CST_ACTUAL_COST_DETAILS based on **
2507: ** records in CST_LAYER_COST_DETAILS. Since we are using current **
2508: ** average the actual cost, prior cost and new cost are all the **
2509: ** same. If detail rows do not exist in CST_LAYER_COST_DETAILS, **
2510: ** we will insert a TL material 0 cost layer. **
2546: FND_FILE.PUT_LINE(FND_FILE.LOG,'>>>Zero Cost Issue Flag:'|| to_char(l_zero_cost_flag));
2547: end if;
2548:
2549: l_stmt_num := 40;
2550: insert into mtl_cst_actual_cost_details(
2551: transaction_id,
2552: organization_id,
2553: layer_id,
2554: cost_element_id,
2600: if (g_debug = 'Y') then
2601: fnd_file.put_line(fnd_file.log,'>>>No cost values, Inserting zero cost in MCACD');
2602: end if;
2603:
2604: insert into mtl_cst_actual_cost_details(
2605: transaction_id,
2606: organization_id,
2607: layer_id,
2608: cost_element_id,
2834:
2835:
2836: select count(*)
2837: into l_mat_ovhds
2838: from mtl_cst_actual_cost_details cacd
2839: where transaction_id = i_txn_id
2840: and organization_id = i_org_id
2841: and layer_id = i_layer_id
2842: and cost_element_id = 2
2896: l_stmt_num := 20;
2897:
2898: select nvl(sum(actual_cost),0)
2899: into l_item_cost
2900: from mtl_cst_actual_cost_details cacd
2901: where transaction_id = i_txn_id
2902: and organization_id = i_org_id
2903: and layer_id = i_layer_id;
2904:
3102:
3103: -- check if there is data in material overhead for this transaction
3104: select count(*)
3105: into l_mcacd_ovhd
3106: from mtl_cst_actual_cost_details cacd
3107: where transaction_id = i_txn_id
3108: and organization_id = i_org_id
3109: and layer_id = i_layer_id
3110: and cost_element_id = 2
3127: and layer_id = i_layer_id
3128: and cost_element_id = 2;
3129:
3130: l_stmt_num := 87;
3131: update mtl_cst_actual_cost_details mcacd
3132: set mcacd.actual_cost = mcacd.actual_cost + l_ovhd_cost
3133: where mcacd.transaction_id = i_txn_id
3134: and mcacd.organization_id = i_org_id
3135: and mcacd.layer_id = i_layer_id
3139: -- if there is no data in MCACD but there is data in MACS then
3140: -- do an insert the sum of overhead cost into MCACD.
3141: else -- insert into MCACD.
3142: l_stmt_num := 89;
3143: insert into mtl_cst_actual_cost_details(
3144: transaction_id,
3145: organization_id,
3146: layer_id,
3147: cost_element_id,
3197: -- or interorg txn with elemental visibility disabled
3198: l_stmt_num := 90;
3199: -- if there is data in MACS, then insert into MCACD.
3200: if (l_mat_ovhds > 0) then
3201: insert into mtl_cst_actual_cost_details(
3202: transaction_id,
3203: organization_id,
3204: layer_id,
3205: cost_element_id,
3326: IF g_debug = 'Y' THEN
3327: fnd_file.put_line(fnd_file.log, '>>>Inside Calc_Average_Cost');
3328: END IF;
3329: /********************************************************************
3330: ** Update mtl_cst_actual_cost_details and update the prior cost **
3331: ** to the current average for the elements that exists and insert **
3332: ** in to mtl_cst_actual_cost_details the current average cost for **
3333: ** the elements that do not exist. **
3334: ********************************************************************/
3328: END IF;
3329: /********************************************************************
3330: ** Update mtl_cst_actual_cost_details and update the prior cost **
3331: ** to the current average for the elements that exists and insert **
3332: ** in to mtl_cst_actual_cost_details the current average cost for **
3333: ** the elements that do not exist. **
3334: ********************************************************************/
3335: l_stmt_num := 5;
3336:
3333: ** the elements that do not exist. **
3334: ********************************************************************/
3335: l_stmt_num := 5;
3336:
3337: Update mtl_cst_actual_cost_details cacd
3338: Set prior_cost = 0,
3339: new_cost = NULL
3340: Where transaction_id = i_txn_id
3341: and organization_id = i_org_id
3343: and transaction_action_id = i_txn_action_id;
3344:
3345: l_stmt_num := 10;
3346:
3347: Update mtl_cst_actual_cost_details cacd
3348: Set (prior_cost, insertion_flag) =
3349: (Select clcd.item_cost,
3350: 'N'
3351: From cst_layer_cost_details clcd
3362: where clcd.layer_id = i_layer_id
3363: and clcd.cost_element_id = cacd.cost_element_id
3364: and clcd.level_type = cacd.level_type);
3365:
3366: insert into mtl_cst_actual_cost_details (
3367: transaction_id,
3368: organization_id,
3369: layer_id,
3370: cost_element_id,
3411: from cst_layer_cost_details clcd
3412: where layer_id = i_layer_id
3413: and not exists
3414: (select 'this detail is not in cacd already'
3415: from mtl_cst_actual_cost_details cacd
3416: where cacd.transaction_id = i_txn_id
3417: and cacd.organization_id = i_org_id
3418: and cacd.layer_id = i_layer_id
3419: and cacd.cost_element_id = clcd.cost_element_id
3433: l_new_onhand := l_cur_onhand + i_txn_qty;
3434:
3435: l_stmt_num := 30;
3436:
3437: Update mtl_cst_actual_cost_details cacd
3438: Set new_cost =
3439: decode(sign(l_cur_onhand),-1,
3440: decode(sign(i_txn_qty), -1,
3441: (cacd.prior_cost*l_cur_onhand + cacd.actual_cost*i_txn_qty)/l_new_onhand,
3485: program_id = i_prg_id,
3486: program_update_date = sysdate,
3487: item_cost =
3488: (select new_cost
3489: from mtl_cst_actual_cost_details cacd
3490: where cacd.transaction_id = i_txn_id
3491: and cacd.organization_id = i_org_id
3492: and cacd.layer_id = i_layer_id
3493: and cacd.cost_element_id = clcd.cost_element_id
3522: i_prg_appl_id,
3523: i_prg_id,
3524: sysdate,
3525: cacd.new_cost
3526: from mtl_cst_actual_cost_details cacd
3527: where cacd.transaction_id = i_txn_id
3528: and cacd.organization_id = i_org_id
3529: and cacd.layer_id = i_layer_id
3530: and cacd.insertion_flag = 'Y';
3894: l_stmt_num := 20;
3895:
3896: select count(*)
3897: into l_cost_exists
3898: from mtl_cst_actual_cost_details cacd
3899: where cacd.transaction_id = i_txn_id
3900: and cacd.organization_id = i_org_id
3901: and cacd.layer_id = i_layer_id;
3902:
3962: sum(nvl(new_cost,0)),
3963: sum(nvl(variance_amount,0)),
3964: layer_qty,
3965: decode(i_cost_update,1,layer_qty,NULL)
3966: from mtl_cst_actual_cost_details cacd
3967: where cacd.transaction_id = i_txn_id
3968: and cacd.organization_id = i_org_id
3969: and cacd.layer_id = i_layer_id
3970: and cacd.transaction_action_id =
4699: end if;
4700:
4701: /***********************************************************************
4702: ** In the following conditions we will be doing distribution for the **
4703: ** standard org, so need populate mtl_cst_actual_cost_details with **
4704: ** the standard costs. **
4705: ** 1. intransit interorg and one of the orgs is standard. **
4706: ** 2. direct interorg and the txn_org_id is standard. **
4707: ***********************************************************************/
4748: l_stmt_num := 70;
4749: /* If no rows exist in cicd (item hasn't been costed), insert into */
4750: /* mcacd using 0 value of this level material */
4751: if (l_count > 0) then
4752: insert into mtl_cst_actual_cost_details (
4753: transaction_id,
4754: organization_id,
4755: layer_id,
4756: cost_element_id,
4800: and cost_type_id = 1
4801: and inventory_item_id = i_item_id
4802: group by cost_element_id, level_type;
4803: else
4804: insert into mtl_cst_actual_cost_details (
4805: transaction_id,
4806: organization_id,
4807: layer_id,
4808: cost_element_id,
4946: i_prg_appl_id,
4947: i_prg_id,
4948: sysdate,
4949: nvl(sum(actual_cost),0)
4950: from mtl_cst_actual_cost_details cacd
4951: where cacd.transaction_id = i_txn_id
4952: and cacd.organization_id = l_std_org
4953: and cacd.layer_id = -1)
4954: where mmt.transaction_id = i_txn_id;
5084: END IF;
5085:
5086: select sum(actual_cost), layer_id
5087: into l_snd_txn_cost, l_from_layer_id
5088: from mtl_cst_actual_cost_details
5089: where transaction_id= i_txn_id
5090: and organization_id= i_org_id
5091: and layer_id= i_layer_id
5092: group by layer_id;
5344: and msi.secondary_inventory_name = mmt.subinventory_code;
5345:
5346: if (l_txfr_std_exp = 1) then
5347: l_stmt_num := 200;
5348: insert into mtl_cst_actual_cost_details (
5349: transaction_id,
5350: organization_id,
5351: layer_id,
5352: cost_element_id,
5412: i_prg_appl_id,
5413: i_prg_id,
5414: sysdate,
5415: nvl(sum(actual_cost),0)
5416: from mtl_cst_actual_cost_details cacd
5417: where cacd.transaction_id = l_txn_update_id
5418: and cacd.organization_id = l_std_org
5419: and cacd.layer_id = -1)
5420: where mmt.transaction_id = l_txn_update_id;
5426: /* the receiving org is standard exp. */
5427: l_stmt_num := 210;
5428: -- if the receiving org is std exp, copy the txn info
5429: -- into MCACD from MCTCD.
5430: insert into mtl_cst_actual_cost_details (
5431: transaction_id,
5432: organization_id,
5433: layer_id,
5434: cost_element_id,
5496: i_prg_appl_id,
5497: i_prg_id,
5498: sysdate,
5499: nvl(sum(actual_cost),0)
5500: from mtl_cst_actual_cost_details cacd
5501: where cacd.transaction_id = l_txn_update_id
5502: and cacd.organization_id = l_std_org
5503: and cacd.layer_id = -1)
5504: where mmt.transaction_id = l_txn_update_id;
5520: where mmt.transaction_id = i_txn_id;
5521: end if;
5522:
5523: l_stmt_num := 260;
5524: insert into mtl_cst_actual_cost_details (
5525: transaction_id,
5526: organization_id,
5527: layer_id,
5528: cost_element_id,
5590: i_prg_appl_id,
5591: i_prg_id,
5592: sysdate,
5593: nvl(sum(actual_cost),0)
5594: from mtl_cst_actual_cost_details cacd
5595: where cacd.transaction_id = l_txn_update_id
5596: and cacd.organization_id = l_std_org
5597: and cacd.layer_id = -1)
5598: where mmt.transaction_id = l_txn_update_id;
5870: mcacd.actual_cost,
5871: mcacd.prior_cost,
5872: mcacd.new_cost,
5873: mcacd.layer_id
5874: from mtl_cst_actual_cost_details mcacd
5875: where mcacd.transaction_id = c_transaction_id
5876: and mcacd.layer_id = i_to_layer;
5877:
5878: type t_cst_element is table of number
6035:
6036: else -- if hook is used
6037: l_stmt_num := 30;
6038: if l_temp_element_cost(l_index_counter) <> 0 then -- if element cost <>0 then insert update MCACD
6039: update mtl_cst_actual_cost_details mcacd
6040: set mcacd.actual_cost = l_temp_element_cost(l_index_counter)
6041: where mcacd.transaction_id = i_txn_id
6042: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
6043: and mcacd.level_type = l_level_type
6042: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
6043: and mcacd.level_type = l_level_type
6044: and mcacd.layer_id = i_to_layer;
6045: else -- if the element cost == 0 then we need to delete MCACD.
6046: delete from mtl_cst_actual_cost_details mcacd
6047: where mcacd.transaction_id = i_txn_id
6048: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
6049: and mcacd.level_type = l_level_type
6050: and mcacd.layer_id = i_to_layer;
6105: for l_ce in 1..5 loop
6106: l_stmt_num := 10;
6107: select max(mcacd.actual_cost)
6108: into l_cur_cost
6109: from mtl_cst_actual_cost_details mcacd
6110: where mcacd.level_type = l_level_type
6111: and mcacd.cost_element_id = l_ce
6112: and mcacd.transaction_id = i_txn_id
6113: and mcacd.layer_id = i_from_layer_id;
6113: and mcacd.layer_id = i_from_layer_id;
6114: l_stmt_num := 20;
6115: select max(mcacd.actual_cost)
6116: into l_borrowed_cost
6117: from mtl_cst_actual_cost_details mcacd
6118: where mcacd.level_type = l_level_type
6119: and mcacd.cost_element_id = l_ce
6120: and mcacd.transaction_id = i_txn_id
6121: and mcacd.layer_id = i_to_layer_id;
6123: l_variance := nvl(l_cur_cost,0) - nvl(l_borrowed_cost,0);
6124:
6125: -- update the variance_amount in MCACD for the from layer.
6126: l_stmt_num := 30;
6127: update mtl_cst_actual_cost_details mcacd
6128: set mcacd.payback_variance_amount = l_variance
6129: where mcacd.transaction_id = i_txn_id
6130: and mcacd.cost_element_id = l_ce
6131: and mcacd.level_type = l_level_type
6343: mcacd.actual_cost*i_conv_rate/i_um_rate,
6344: 0,
6345: 0,
6346: 0
6347: from mtl_cst_actual_cost_details mcacd
6348: where mcacd.layer_id = i_from_layer_id
6349: and mcacd.transaction_id = i_txn_id;
6350: END IF;
6351:
6832: end if;
6833: l_stmt_num := 41;
6834:
6835:
6836: insert into mtl_cst_actual_cost_details (
6837: transaction_id,
6838: organization_id,
6839: layer_id,
6840: cost_element_id,
6908: p_prog_id,
6909: sysdate,
6910: sum(nvl(actual_cost,0)),
6911: sum(nvl(variance_amount,0))
6912: from mtl_cst_actual_cost_details cacd
6913: where cacd.transaction_id = c_mmt_txn_rec.transaction_id)
6914: where mmt.transaction_id = c_mmt_txn_rec.transaction_id;
6915:
6916: end if; /* mctcd count = 0 */
7200:
7201: if (l_count = 0) then
7202: /* Insert into MCACD using 0 cost for This Level Material */
7203:
7204: insert into mtl_cst_actual_cost_details (
7205: transaction_id,
7206: organization_id,
7207: layer_id,
7208: cost_element_id,
7245:
7246: else
7247: /* Insert into MCACD cost details from CICD */
7248: l_stmt_num := 30;
7249: insert into mtl_cst_actual_cost_details (
7250: transaction_id,
7251: organization_id,
7252: layer_id,
7253: cost_element_id,
7311:
7312: if (l_count = 0) then
7313: /* Insert into MCACD using 0 cost for This Level Material */
7314:
7315: insert into mtl_cst_actual_cost_details (
7316: transaction_id,
7317: organization_id,
7318: layer_id,
7319: cost_element_id,
7356:
7357: else
7358: /* Insert into MCACD cost details from CICD */
7359: l_stmt_num := 38;
7360: insert into mtl_cst_actual_cost_details (
7361: transaction_id,
7362: organization_id,
7363: layer_id,
7364: cost_element_id,
7750: mcacd.actual_cost,
7751: mcacd.prior_cost,
7752: mcacd.new_cost,
7753: mcacd.layer_id
7754: from mtl_cst_actual_cost_details mcacd
7755: where mcacd.transaction_id = c_transaction_id
7756: and mcacd.layer_id = i_from_layer;
7757:
7758: type t_cst_element is table of number
7825: end if;
7826:
7827: select max(mcacd.actual_cost)
7828: into l_cur_cost
7829: from mtl_cst_actual_cost_details mcacd
7830: where mcacd.level_type = l_level_type
7831: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
7832: and mcacd.transaction_id = i_txn_id
7833: and mcacd.layer_id = i_from_layer;
7839: from dual;
7840:
7841: l_variance := nvl(l_cur_cost,0) - nvl(l_borrowed_cost,0);
7842:
7843: update mtl_cst_actual_cost_details mcacd
7844: set mcacd.payback_variance_amount = l_variance
7845: where mcacd.transaction_id = i_txn_id
7846: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
7847: and mcacd.level_type = l_level_type
8277: IF l_pd_xfer_ind = 'N'
8278: THEN
8279: SELECT SUM(ACTUAL_COST)
8280: INTO l_actual_cost
8281: FROM MTL_CST_ACTUAL_COST_DETAILS
8282: WHERE transaction_id = p_parent_txn_id;
8283: ELSE
8284: l_actual_cost := l_transfer_price;
8285: END IF;
8424:
8425: l_stmt_num := 100;
8426:
8427: BEGIN
8428: INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
8429: transaction_id,
8430: organization_id,
8431: layer_id,
8432: cost_element_id,
9093: IF g_debug = 'Y' THEN
9094: fnd_file.put_line(fnd_file.log, 'inserting to MCACD for IO Issue to exp txn');
9095: END IF;
9096:
9097: insert into mtl_cst_actual_cost_details(
9098: transaction_id,
9099: organization_id,
9100: layer_id,
9101: cost_element_id,
9576:
9577: /* Verify if the hook was called, see if it has been used and check if there is data in MCACD*/
9578: SELECT COUNT(*)
9579: INTO l_test_mcacd
9580: FROM MTL_CST_ACTUAL_COST_DETAILS MCACD
9581: WHERE TRANSACTION_ID = i_txn_id
9582: AND LAYER_ID = i_layer_id
9583: AND ORGANIZATION_ID = i_org_id;
9584:
9590: for that cost element, it will suffice that one of the cost element violates
9591: this condition to error out */
9592: SELECT SUM(decode(MCACD.insertion_flag, 'Y', 1, 0))
9593: INTO l_test_clcd
9594: FROM MTL_CST_ACTUAL_COST_DETAILS MCACD
9595: WHERE MCACD.transaction_id = i_txn_id
9596: AND MCACD.organization_id = i_org_id
9597: AND MCACD.layer_id = i_layer_id
9598: AND EXISTS (SELECT 'X'