[Home] [Help]
36: |
37: | 07-SEP-2004 Kiran Konada
38: | bugfix 3877097
39: | at lStmtNumber := 40,60,70,80
40: | during the insert into cst_item_costs and
41: | cst_item_cost_details
42: | mp1.cost_organization_id was being inserted
43: | BUT the NOT EXISTS condition was checking
44: | for mp1.organization_id.
917: MP1.organization_id org_id,
918: DECODE(bcso.ORGANIZATION_ID, bcolu.ship_from_org_id, get_cost_group(bcolu.ship_from_org_id, bcolu.line_id), 1) cost_group_id,
919: bcolu.config_item_id config_item_id
920: from
921: cst_item_costs c,
922: bom_cto_src_orgs bcso,
923: bom_cto_order_lines_upg bcolu,
924: mtl_parameters mp1
925: where bcolu.config_item_id is not null
1254: l_rt_cicd_summary.outside_processing_cost,
1255: l_rt_cicd_summary.overhead_cost
1256: from
1257: mtl_parameters MP1,
1258: cst_item_costs C,
1259: cst_item_cost_details CICD,
1260: bom_cto_order_lines_upg bcolu
1261: where
1262: C.organization_id = MP1.organization_id
1286: (mp2.organization_id = mp3.master_organization_id))
1287: )
1288: and NOT EXISTS
1289: (select NULL
1290: from CST_ITEM_COSTS
1291: where inventory_item_id = bcolu.config_item_id
1292: and organization_id = mp1.cost_organization_id
1293: and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id))
1294: group by bcolu.config_item_id, C.organization_id, C.cost_type_id;
1333: end if;
1334:
1335:
1336: /*-------------------------------------------------------+
1337: Insert a row into the cst_item_costs_table
1338: +------------------------------------------------------- */
1339:
1340: lStmtNumber := 40;
1341:
1338: +------------------------------------------------------- */
1339:
1340: lStmtNumber := 40;
1341:
1342: insert into CST_ITEM_COSTS
1343: (inventory_item_id,
1344: organization_id,
1345: cost_type_id,
1346: last_update_date,
1444: C.attribute14,
1445: C.attribute15
1446: from
1447: mtl_parameters MP1,
1448: cst_item_costs C,
1449: mtl_system_items S,
1450: bom_cto_src_orgs bcso,
1451: bom_cto_order_lines_upg bcolu
1452: where bcolu.config_item_id is not null
1461: and C.organization_id = mp1.organization_id
1462: and mp1.organization_id = bcso.organization_id
1463: and NOT EXISTS
1464: (select NULL
1465: from CST_ITEM_COSTS
1466: where inventory_item_id = bcolu.config_item_id
1467: and organization_id = mp1.cost_organization_id --bugfix 3877097
1468: and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id));
1469:
1466: where inventory_item_id = bcolu.config_item_id
1467: and organization_id = mp1.cost_organization_id --bugfix 3877097
1468: and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id));
1469:
1470: WriteToLog('Inserted rows into cst_item_costs:: '||sql%rowcount,2);
1471:
1472:
1473:
1474:
1476:
1477:
1478: if( l_rt_cicd_summary.cost_type_id.count> 0 ) then
1479: FORALL j IN 1..l_rt_cicd_summary.cost_type_id.last
1480: UPDATE cst_item_costs
1481: set material_cost = l_rt_cicd_summary.material_cost(j),
1482: material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
1483: resource_cost = l_rt_cicd_summary.resource_cost(j),
1484: outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
1496: and cost_type_id = l_rt_cicd_summary.cost_type_id(j) ;
1497:
1498:
1499: IF PG_DEBUG <> 0 THEN
1500: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
1501: END IF;
1502: else
1503:
1504: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
1500: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
1501: END IF;
1502: else
1503:
1504: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
1505:
1506: end if;
1507:
1508:
1561:
1562:
1563:
1564: lStmtNumber := 60;
1565: insert into CST_ITEM_COSTS
1566: (inventory_item_id,
1567: organization_id,
1568: cost_type_id,
1569: last_update_date,
1667: C.attribute14,
1668: C.attribute15
1669: from
1670: mtl_parameters MP1,
1671: cst_item_costs C,
1672: mtl_system_items S,
1673: bom_cto_src_orgs bcso,
1674: bom_cto_order_lines_upg bcolu
1675: where bcolu.config_item_id is not null
1685: and C.organization_id = bcso.organization_id
1686: and mp1.organization_id = bcso.organization_id
1687: and NOT EXISTS
1688: (select NULL
1689: from CST_ITEM_COSTS
1690: where inventory_item_id = bcolu.config_item_id
1691: and organization_id = mp1.cost_organization_id --bugfix 3877097
1692: and cost_type_id = l_cto_cost_type_id);
1693:
1690: where inventory_item_id = bcolu.config_item_id
1691: and organization_id = mp1.cost_organization_id --bugfix 3877097
1692: and cost_type_id = l_cto_cost_type_id);
1693:
1694: WriteToLog('Inserted rows into cst_item_costs:: '||sql%rowcount,2);
1695:
1696:
1697:
1698:
1700:
1701:
1702: if( l_rt_cicd_summary.cost_type_id.count > 0 ) then
1703: FORALL j IN 1..l_rt_cicd_summary.cost_type_id.last
1704: UPDATE cst_item_costs
1705: set material_cost = l_rt_cicd_summary.material_cost(j),
1706: material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
1707: resource_cost = l_rt_cicd_summary.resource_cost(j),
1708: outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
1720: and cost_type_id = l_cto_cost_type_id ;
1721:
1722:
1723: IF PG_DEBUG <> 0 THEN
1724: oe_debug_pub.add('Create_Item: ' || 'after update:cst_item_costs for CTO cost type '||to_char(sql%rowcount),2);
1725: END IF;
1726:
1727: else
1728:
1725: END IF;
1726:
1727: else
1728:
1729: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS for CTO cost type as no new records inserted ' , 1 ) ;
1730:
1731: end if;
1732:
1733: