[Home] [Help]
490: FROM oe_order_lines_all OOLA, /*BUG 5768680 Changes introduced to improve performance*/
491: oe_order_headers_all OOHA, /* of the layer cost worker*/
492: mtl_sales_orders MSO,
493: mtl_material_transactions MMT,
494: mtl_cst_actual_cost_details MCACD,
495: cst_cogs_events cce
496: WHERE OOLA.line_id = l_so_line_id
497: AND OOHA.header_id = OOLA.header_id
498: AND MSO.segment1 = TO_CHAR(OOHA.order_number) -- extraneous MSOs are possible
556: FROM oe_order_lines_all OOLA, /*BUG 5768680 Changes introduced to improve performance*/
557: oe_order_headers_all OOHA, /* of the layer cost worker*/
558: mtl_sales_orders MSO,
559: mtl_material_transactions MMT,
560: mtl_cst_actual_cost_details MCACD
561: WHERE OOLA.line_id = l_so_line_id
562: AND OOHA.header_id = OOLA.header_id
563: AND MSO.segment1 = TO_CHAR(OOHA.order_number) -- extraneous MSOs are possible
564: AND MMT.transaction_source_id = MSO.sales_order_id
850: fnd_file.put_line(fnd_file.log,'Average Cost Update <<<');
851: end if;
852:
853: l_stmt_num := 10;
854: -- First insert records into mtl_cst_actual_cost_details.
855:
856: insert into mtl_cst_actual_cost_details (
857: transaction_id,
858: organization_id,
852:
853: l_stmt_num := 10;
854: -- First insert records into mtl_cst_actual_cost_details.
855:
856: insert into mtl_cst_actual_cost_details (
857: transaction_id,
858: organization_id,
859: layer_id,
860: cost_element_id,
989: l_stmt_num := 20;
990:
991: /* select count(*)
992: into l_neg_cost
993: from mtl_cst_actual_cost_details
994: where transaction_id = i_txn_id
995: and organization_id = i_org_id
996: and layer_id = i_layer_id
997: and new_cost < 0;
1017:
1018: Above two changes will make behaviour consitent.
1019:
1020: ***************************************************/
1021: insert into mtl_cst_actual_cost_details (
1022: transaction_id,
1023: organization_id,
1024: layer_id,
1025: cost_element_id,
1066: from cst_layer_cost_details clcd
1067: where layer_id = i_layer_id
1068: and not exists
1069: (select 'this detail is not in cacd already'
1070: from mtl_cst_actual_cost_details cacd
1071: where cacd.transaction_id = i_txn_id
1072: and cacd.organization_id = i_org_id
1073: and cacd.layer_id = i_layer_id
1074: and cacd.cost_element_id = clcd.cost_element_id
1080:
1081:
1082: /********************************************************************
1083: ** Delete from cst_layer_cost_details and insert the new rows **
1084: ** from mtl_cst_actual_cost_details. **
1085: ********************************************************************/
1086: l_stmt_num := 30;
1087:
1088: Delete from cst_layer_cost_details
1115: i_prg_appl_id,
1116: i_prg_id,
1117: sysdate,
1118: cacd.new_cost
1119: from mtl_cst_actual_cost_details cacd
1120: where cacd.transaction_id = i_txn_id
1121: and cacd.organization_id = i_org_id
1122: and cacd.layer_id = i_layer_id
1123: and cacd.insertion_flag = 'Y'
1150: i_prg_appl_id,
1151: i_prg_id,
1152: sysdate,
1153: cacd.new_cost
1154: from mtl_cst_actual_cost_details cacd
1155: where cacd.transaction_id = i_txn_id
1156: and cacd.organization_id = i_org_id
1157: and cacd.layer_id = i_layer_id
1158: and cacd.insertion_flag = 'Y'
1156: and cacd.organization_id = i_org_id
1157: and cacd.layer_id = i_layer_id
1158: and cacd.insertion_flag = 'Y'
1159: and cacd.cost_element_id = (select min(cost_element_id)
1160: from mtl_cst_actual_cost_details
1161: where transaction_id = i_txn_id
1162: and organization_id = i_org_id
1163: and layer_id = i_layer_id
1164: and insertion_flag = 'Y');
1573: end if;
1574: end if;
1575:
1576: -- If we are changing layers, then need to create another layer information
1577: -- in mtl_cst_actual_cost_details.
1578: l_stmt_num := 80;
1579:
1580: if (l_layer_chg = 1) then
1581: if (g_debug = 'Y') then
1581: if (g_debug = 'Y') then
1582: fnd_file.put_line(fnd_file.log,'Layer change');
1583: end if;
1584:
1585: insert into mtl_cst_actual_cost_details(
1586: transaction_id,
1587: organization_id,
1588: layer_id,
1589: cost_element_id,
1627: cacd.actual_cost,
1628: 'Y',
1629: 0,
1630: 'N'
1631: from mtl_cst_actual_cost_details cacd
1632: where transaction_id = i_txn_id
1633: and organization_id = i_org_id
1634: and layer_id = l_from_layer;
1635:
1851: -- store the current cost of the item (from CLCD) into the return sub.
1852: --
1853: l_stmt_num := 150;
1854:
1855: delete from mtl_cst_actual_cost_details
1856: where transaction_id = i_txn_id
1857: and layer_id = l_from_layer;
1858:
1859: l_stmt_num := 160;
1856: where transaction_id = i_txn_id
1857: and layer_id = l_from_layer;
1858:
1859: l_stmt_num := 160;
1860: insert into mtl_cst_actual_cost_details(
1861: transaction_id,
1862: organization_id,
1863: layer_id,
1864: cost_element_id,
2093: SUM(NVL(MCACD.actual_cost,0)),
2094: SUM(NVL(MCACD.prior_cost,0)),
2095: SUM(NVL(MCACD.new_cost,0)),
2096: SUM(NVL(MCACD.variance_amount,0))
2097: FROM mtl_cst_actual_cost_details MCACD
2098: WHERE MCACD.transaction_id = i_txn_id
2099: AND MCACD.layer_id = l_to_layer
2100: )
2101: WHERE MMT.transaction_id = l_txf_txn_id
2247: if (l_err_num <> 0) then
2248: raise process_error;
2249: end if;
2250:
2251: insert into mtl_cst_actual_cost_details(
2252: transaction_id,
2253: organization_id,
2254: layer_id,
2255: cost_element_id,
2293: cacd.new_cost,
2294: 'N', -- check
2295: 0,
2296: 'N'
2297: from mtl_cst_actual_cost_details cacd
2298: where transaction_id = i_txn_id
2299: and organization_id = i_org_id
2300: and layer_id = i_txfr_layer_id
2301: and transaction_action_id = i_txn_action_id; */
2331:
2332: l_stmt_num := 20;
2333:
2334: -- insert a row in MCACD for WIP issue / return transaction
2335: insert into mtl_cst_actual_cost_details(
2336: transaction_id,
2337: organization_id,
2338: layer_id,
2339: cost_element_id,
2377: cacd.new_cost,
2378: 'N', -- check
2379: 0,
2380: 'N'
2381: from mtl_cst_actual_cost_details cacd
2382: where transaction_id = i_txn_id
2383: and organization_id = i_org_id
2384: and layer_id = i_txfr_layer_id
2385: and transaction_action_id = i_txn_action_id;
2604: --
2605: -- Make sure the mcacd should not have any -ve cost
2606: --
2607:
2608: insert into mtl_cst_actual_cost_details (
2609: transaction_id,
2610: organization_id,
2611: layer_id,
2612: cost_element_id,
2657: /* AND ctcd.transaction_cost >= 0 */; -- modified for bug#3835412
2658:
2659: if (g_debug = 'Y') then
2660: select count(*) into l_count
2661: from mtl_cst_actual_cost_details
2662: where transaction_id = i_txn_id
2663: and organization_id = i_org_id;
2664: 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));
2665: end if;
2669: * for scrap transactions, no transaction cost means 0 cost. */
2670:
2671: l_ret_val := 0;
2672: /********************************************************************
2673: ** Create detail rows in MTL_CST_ACTUAL_COST_DETAILS based on **
2674: ** records in CST_LAYER_COST_DETAILS. Since we are using current **
2675: ** average the actual cost, prior cost and new cost are all the **
2676: ** same. If detail rows do not exist in CST_LAYER_COST_DETAILS, **
2677: ** we will insert a TL material 0 cost layer. **
2713: FND_FILE.PUT_LINE(FND_FILE.LOG,'>>>Zero Cost Issue Flag:'|| to_char(l_zero_cost_flag));
2714: end if;
2715:
2716: l_stmt_num := 40;
2717: insert into mtl_cst_actual_cost_details(
2718: transaction_id,
2719: organization_id,
2720: layer_id,
2721: cost_element_id,
2767: if (g_debug = 'Y') then
2768: fnd_file.put_line(fnd_file.log,'>>>No cost values, Inserting zero cost in MCACD');
2769: end if;
2770:
2771: insert into mtl_cst_actual_cost_details(
2772: transaction_id,
2773: organization_id,
2774: layer_id,
2775: cost_element_id,
3001:
3002:
3003: select count(*)
3004: into l_mat_ovhds
3005: from mtl_cst_actual_cost_details cacd
3006: where transaction_id = i_txn_id
3007: and organization_id = i_org_id
3008: and layer_id = i_layer_id
3009: and cost_element_id = 2
3063: l_stmt_num := 20;
3064:
3065: select nvl(sum(actual_cost),0)
3066: into l_item_cost
3067: from mtl_cst_actual_cost_details cacd
3068: where transaction_id = i_txn_id
3069: and organization_id = i_org_id
3070: and layer_id = i_layer_id;
3071:
3273:
3274: -- check if there is data in material overhead for this transaction
3275: select count(*)
3276: into l_mcacd_ovhd
3277: from mtl_cst_actual_cost_details cacd
3278: where transaction_id = i_txn_id
3279: and organization_id = i_org_id
3280: and layer_id = i_layer_id
3281: and cost_element_id = 2
3298: and layer_id = i_layer_id
3299: and cost_element_id = 2;
3300:
3301: l_stmt_num := 87;
3302: update mtl_cst_actual_cost_details mcacd
3303: set mcacd.actual_cost = mcacd.actual_cost + l_ovhd_cost
3304: where mcacd.transaction_id = i_txn_id
3305: and mcacd.organization_id = i_org_id
3306: and mcacd.layer_id = i_layer_id
3310: -- if there is no data in MCACD but there is data in MACS then
3311: -- do an insert the sum of overhead cost into MCACD.
3312: else -- insert into MCACD.
3313: l_stmt_num := 89;
3314: insert into mtl_cst_actual_cost_details(
3315: transaction_id,
3316: organization_id,
3317: layer_id,
3318: cost_element_id,
3368: -- or interorg txn with elemental visibility disabled
3369: l_stmt_num := 90;
3370: -- if there is data in MACS, then insert into MCACD.
3371: if (l_mat_ovhds > 0) then
3372: insert into mtl_cst_actual_cost_details(
3373: transaction_id,
3374: organization_id,
3375: layer_id,
3376: cost_element_id,
3497: IF g_debug = 'Y' THEN
3498: fnd_file.put_line(fnd_file.log, '>>>Inside Calc_Average_Cost');
3499: END IF;
3500: /********************************************************************
3501: ** Update mtl_cst_actual_cost_details and update the prior cost **
3502: ** to the current average for the elements that exists and insert **
3503: ** in to mtl_cst_actual_cost_details the current average cost for **
3504: ** the elements that do not exist. **
3505: ********************************************************************/
3499: END IF;
3500: /********************************************************************
3501: ** Update mtl_cst_actual_cost_details and update the prior cost **
3502: ** to the current average for the elements that exists and insert **
3503: ** in to mtl_cst_actual_cost_details the current average cost for **
3504: ** the elements that do not exist. **
3505: ********************************************************************/
3506: l_stmt_num := 5;
3507:
3504: ** the elements that do not exist. **
3505: ********************************************************************/
3506: l_stmt_num := 5;
3507:
3508: Update mtl_cst_actual_cost_details cacd
3509: Set prior_cost = 0,
3510: new_cost = NULL
3511: Where transaction_id = i_txn_id
3512: and organization_id = i_org_id
3514: and transaction_action_id = i_txn_action_id;
3515:
3516: l_stmt_num := 10;
3517:
3518: Update mtl_cst_actual_cost_details cacd
3519: Set (prior_cost, insertion_flag) =
3520: (Select clcd.item_cost,
3521: 'N'
3522: From cst_layer_cost_details clcd
3533: where clcd.layer_id = i_layer_id
3534: and clcd.cost_element_id = cacd.cost_element_id
3535: and clcd.level_type = cacd.level_type);
3536:
3537: insert into mtl_cst_actual_cost_details (
3538: transaction_id,
3539: organization_id,
3540: layer_id,
3541: cost_element_id,
3582: from cst_layer_cost_details clcd
3583: where layer_id = i_layer_id
3584: and not exists
3585: (select 'this detail is not in cacd already'
3586: from mtl_cst_actual_cost_details cacd
3587: where cacd.transaction_id = i_txn_id
3588: and cacd.organization_id = i_org_id
3589: and cacd.layer_id = i_layer_id
3590: and cacd.cost_element_id = clcd.cost_element_id
3604: l_new_onhand := l_cur_onhand + i_txn_qty;
3605:
3606: l_stmt_num := 30;
3607:
3608: Update mtl_cst_actual_cost_details cacd
3609: Set new_cost =
3610: decode(sign(l_cur_onhand),-1,
3611: decode(sign(i_txn_qty), -1,
3612: (cacd.prior_cost*l_cur_onhand + cacd.actual_cost*i_txn_qty)/l_new_onhand,
3656: program_id = i_prg_id,
3657: program_update_date = sysdate,
3658: item_cost =
3659: (select new_cost
3660: from mtl_cst_actual_cost_details cacd
3661: where cacd.transaction_id = i_txn_id
3662: and cacd.organization_id = i_org_id
3663: and cacd.layer_id = i_layer_id
3664: and cacd.cost_element_id = clcd.cost_element_id
3693: i_prg_appl_id,
3694: i_prg_id,
3695: sysdate,
3696: cacd.new_cost
3697: from mtl_cst_actual_cost_details cacd
3698: where cacd.transaction_id = i_txn_id
3699: and cacd.organization_id = i_org_id
3700: and cacd.layer_id = i_layer_id
3701: and cacd.insertion_flag = 'Y';
4065: l_stmt_num := 20;
4066:
4067: select count(*)
4068: into l_cost_exists
4069: from mtl_cst_actual_cost_details cacd
4070: where cacd.transaction_id = i_txn_id
4071: and cacd.organization_id = i_org_id
4072: and cacd.layer_id = i_layer_id;
4073:
4133: sum(nvl(new_cost,0)),
4134: sum(nvl(variance_amount,0)),
4135: layer_qty,
4136: decode(i_cost_update,1,layer_qty,NULL)
4137: from mtl_cst_actual_cost_details cacd
4138: where cacd.transaction_id = i_txn_id
4139: and cacd.organization_id = i_org_id
4140: and cacd.layer_id = i_layer_id
4141: and cacd.transaction_action_id =
4874: end if;
4875:
4876: /***********************************************************************
4877: ** In the following conditions we will be doing distribution for the **
4878: ** standard org, so need populate mtl_cst_actual_cost_details with **
4879: ** the standard costs. **
4880: ** 1. intransit interorg and one of the orgs is standard. **
4881: ** 2. direct interorg and the txn_org_id is standard. **
4882: ***********************************************************************/
4923: l_stmt_num := 70;
4924: /* If no rows exist in cicd (item hasn't been costed), insert into */
4925: /* mcacd using 0 value of this level material */
4926: if (l_count > 0) then
4927: insert into mtl_cst_actual_cost_details (
4928: transaction_id,
4929: organization_id,
4930: layer_id,
4931: cost_element_id,
4975: and cost_type_id = 1
4976: and inventory_item_id = i_item_id
4977: group by cost_element_id, level_type;
4978: else
4979: insert into mtl_cst_actual_cost_details (
4980: transaction_id,
4981: organization_id,
4982: layer_id,
4983: cost_element_id,
5121: i_prg_appl_id,
5122: i_prg_id,
5123: sysdate,
5124: nvl(sum(actual_cost),0)
5125: from mtl_cst_actual_cost_details cacd
5126: where cacd.transaction_id = i_txn_id
5127: and cacd.organization_id = l_std_org
5128: and cacd.layer_id = -1)
5129: where mmt.transaction_id = i_txn_id;
5259: END IF;
5260:
5261: select sum(actual_cost), layer_id
5262: into l_snd_txn_cost, l_from_layer_id
5263: from mtl_cst_actual_cost_details
5264: where transaction_id= i_txn_id
5265: and organization_id= i_org_id
5266: and layer_id= i_layer_id
5267: group by layer_id;
5537: and msi.secondary_inventory_name = mmt.subinventory_code;
5538:
5539: if (l_txfr_std_exp = 1) then
5540: l_stmt_num := 200;
5541: insert into mtl_cst_actual_cost_details (
5542: transaction_id,
5543: organization_id,
5544: layer_id,
5545: cost_element_id,
5605: i_prg_appl_id,
5606: i_prg_id,
5607: sysdate,
5608: nvl(sum(actual_cost),0)
5609: from mtl_cst_actual_cost_details cacd
5610: where cacd.transaction_id = l_txn_update_id
5611: and cacd.organization_id = l_std_org
5612: and cacd.layer_id = -1)
5613: where mmt.transaction_id = l_txn_update_id;
5619: /* the receiving org is standard exp. */
5620: l_stmt_num := 210;
5621: -- if the receiving org is std exp, copy the txn info
5622: -- into MCACD from MCTCD.
5623: insert into mtl_cst_actual_cost_details (
5624: transaction_id,
5625: organization_id,
5626: layer_id,
5627: cost_element_id,
5689: i_prg_appl_id,
5690: i_prg_id,
5691: sysdate,
5692: nvl(sum(actual_cost),0)
5693: from mtl_cst_actual_cost_details cacd
5694: where cacd.transaction_id = l_txn_update_id
5695: and cacd.organization_id = l_std_org
5696: and cacd.layer_id = -1)
5697: where mmt.transaction_id = l_txn_update_id;
5713: where mmt.transaction_id = i_txn_id;
5714: end if;
5715:
5716: l_stmt_num := 260;
5717: insert into mtl_cst_actual_cost_details (
5718: transaction_id,
5719: organization_id,
5720: layer_id,
5721: cost_element_id,
5783: i_prg_appl_id,
5784: i_prg_id,
5785: sysdate,
5786: nvl(sum(actual_cost),0)
5787: from mtl_cst_actual_cost_details cacd
5788: where cacd.transaction_id = l_txn_update_id
5789: and cacd.organization_id = l_std_org
5790: and cacd.layer_id = -1)
5791: where mmt.transaction_id = l_txn_update_id;
6063: mcacd.actual_cost,
6064: mcacd.prior_cost,
6065: mcacd.new_cost,
6066: mcacd.layer_id
6067: from mtl_cst_actual_cost_details mcacd
6068: where mcacd.transaction_id = c_transaction_id
6069: and mcacd.layer_id = i_to_layer;
6070:
6071: type t_cst_element is table of number
6228:
6229: else -- if hook is used
6230: l_stmt_num := 30;
6231: if l_temp_element_cost(l_index_counter) <> 0 then -- if element cost <>0 then insert update MCACD
6232: update mtl_cst_actual_cost_details mcacd
6233: set mcacd.actual_cost = l_temp_element_cost(l_index_counter)
6234: where mcacd.transaction_id = i_txn_id
6235: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
6236: and mcacd.level_type = l_level_type
6235: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
6236: and mcacd.level_type = l_level_type
6237: and mcacd.layer_id = i_to_layer;
6238: else -- if the element cost == 0 then we need to delete MCACD.
6239: delete from mtl_cst_actual_cost_details mcacd
6240: where mcacd.transaction_id = i_txn_id
6241: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
6242: and mcacd.level_type = l_level_type
6243: and mcacd.layer_id = i_to_layer;
6298: for l_ce in 1..5 loop
6299: l_stmt_num := 10;
6300: select max(mcacd.actual_cost)
6301: into l_cur_cost
6302: from mtl_cst_actual_cost_details mcacd
6303: where mcacd.level_type = l_level_type
6304: and mcacd.cost_element_id = l_ce
6305: and mcacd.transaction_id = i_txn_id
6306: and mcacd.layer_id = i_from_layer_id;
6306: and mcacd.layer_id = i_from_layer_id;
6307: l_stmt_num := 20;
6308: select max(mcacd.actual_cost)
6309: into l_borrowed_cost
6310: from mtl_cst_actual_cost_details mcacd
6311: where mcacd.level_type = l_level_type
6312: and mcacd.cost_element_id = l_ce
6313: and mcacd.transaction_id = i_txn_id
6314: and mcacd.layer_id = i_to_layer_id;
6316: l_variance := nvl(l_cur_cost,0) - nvl(l_borrowed_cost,0);
6317:
6318: -- update the variance_amount in MCACD for the from layer.
6319: l_stmt_num := 30;
6320: update mtl_cst_actual_cost_details mcacd
6321: set mcacd.payback_variance_amount = l_variance
6322: where mcacd.transaction_id = i_txn_id
6323: and mcacd.cost_element_id = l_ce
6324: and mcacd.level_type = l_level_type
6536: mcacd.actual_cost*i_conv_rate/i_um_rate,
6537: 0,
6538: 0,
6539: 0
6540: from mtl_cst_actual_cost_details mcacd
6541: where mcacd.layer_id = i_from_layer_id
6542: and mcacd.transaction_id = i_txn_id;
6543: END IF;
6544:
7025: end if;
7026: l_stmt_num := 41;
7027:
7028:
7029: insert into mtl_cst_actual_cost_details (
7030: transaction_id,
7031: organization_id,
7032: layer_id,
7033: cost_element_id,
7101: p_prog_id,
7102: sysdate,
7103: sum(nvl(actual_cost,0)),
7104: sum(nvl(variance_amount,0))
7105: from mtl_cst_actual_cost_details cacd
7106: where cacd.transaction_id = c_mmt_txn_rec.transaction_id)
7107: where mmt.transaction_id = c_mmt_txn_rec.transaction_id;
7108:
7109: end if; /* mctcd count = 0 */
7395:
7396: if (l_count = 0) then
7397: /* Insert into MCACD using 0 cost for This Level Material */
7398:
7399: insert into mtl_cst_actual_cost_details (
7400: transaction_id,
7401: organization_id,
7402: layer_id,
7403: cost_element_id,
7440:
7441: else
7442: /* Insert into MCACD cost details from CICD */
7443: l_stmt_num := 30;
7444: insert into mtl_cst_actual_cost_details (
7445: transaction_id,
7446: organization_id,
7447: layer_id,
7448: cost_element_id,
7506:
7507: if (l_count = 0) then
7508: /* Insert into MCACD using 0 cost for This Level Material */
7509:
7510: insert into mtl_cst_actual_cost_details (
7511: transaction_id,
7512: organization_id,
7513: layer_id,
7514: cost_element_id,
7551:
7552: else
7553: /* Insert into MCACD cost details from CICD */
7554: l_stmt_num := 38;
7555: insert into mtl_cst_actual_cost_details (
7556: transaction_id,
7557: organization_id,
7558: layer_id,
7559: cost_element_id,
7945: mcacd.actual_cost,
7946: mcacd.prior_cost,
7947: mcacd.new_cost,
7948: mcacd.layer_id
7949: from mtl_cst_actual_cost_details mcacd
7950: where mcacd.transaction_id = c_transaction_id
7951: and mcacd.layer_id = i_from_layer;
7952:
7953: type t_cst_element is table of number
8020: end if;
8021:
8022: select max(mcacd.actual_cost)
8023: into l_cur_cost
8024: from mtl_cst_actual_cost_details mcacd
8025: where mcacd.level_type = l_level_type
8026: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
8027: and mcacd.transaction_id = i_txn_id
8028: and mcacd.layer_id = i_from_layer;
8034: from dual;
8035:
8036: l_variance := nvl(l_cur_cost,0) - nvl(l_borrowed_cost,0);
8037:
8038: update mtl_cst_actual_cost_details mcacd
8039: set mcacd.payback_variance_amount = l_variance
8040: where mcacd.transaction_id = i_txn_id
8041: and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
8042: and mcacd.level_type = l_level_type
8474: IF l_pd_xfer_ind = 'N'
8475: THEN
8476: SELECT SUM(ACTUAL_COST)
8477: INTO l_actual_cost
8478: FROM MTL_CST_ACTUAL_COST_DETAILS
8479: WHERE transaction_id = p_parent_txn_id;
8480: ELSE
8481: l_actual_cost := l_transfer_price;
8482: END IF;
8621:
8622: l_stmt_num := 100;
8623:
8624: BEGIN
8625: INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
8626: transaction_id,
8627: organization_id,
8628: layer_id,
8629: cost_element_id,
9290: IF g_debug = 'Y' THEN
9291: fnd_file.put_line(fnd_file.log, 'inserting to MCACD for IO Issue to exp txn');
9292: END IF;
9293:
9294: insert into mtl_cst_actual_cost_details(
9295: transaction_id,
9296: organization_id,
9297: layer_id,
9298: cost_element_id,
9773:
9774: /* Verify if the hook was called, see if it has been used and check if there is data in MCACD*/
9775: SELECT COUNT(*)
9776: INTO l_test_mcacd
9777: FROM MTL_CST_ACTUAL_COST_DETAILS MCACD
9778: WHERE TRANSACTION_ID = i_txn_id
9779: AND LAYER_ID = i_layer_id
9780: AND ORGANIZATION_ID = i_org_id;
9781:
9787: for that cost element, it will suffice that one of the cost element violates
9788: this condition to error out */
9789: SELECT Count(*)
9790: INTO l_test_clcd
9791: FROM MTL_CST_ACTUAL_COST_DETAILS MCACD
9792: WHERE MCACD.transaction_id = i_txn_id
9793: AND MCACD.organization_id = i_org_id
9794: AND MCACD.layer_id = i_layer_id
9795: AND Nvl(MCACD.insertion_flag,'N')='Y'