[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.
1060: MP1.organization_id org_id,
1061: DECODE(bcso.ORGANIZATION_ID, bcolu.ship_from_org_id, get_cost_group(bcolu.ship_from_org_id, bcolu.line_id), 1) cost_group_id,
1062: bcolu.config_item_id config_item_id
1063: from
1064: cst_item_costs c,
1065: bom_cto_src_orgs bcso,
1066: bom_cto_order_lines_upg bcolu,
1067: mtl_parameters mp1
1068: where bcolu.config_item_id is not null
1397: l_rt_cicd_summary.outside_processing_cost,
1398: l_rt_cicd_summary.overhead_cost
1399: from
1400: mtl_parameters MP1,
1401: cst_item_costs C,
1402: cst_item_cost_details CICD,
1403: bom_cto_order_lines_upg bcolu
1404: where
1405: C.organization_id = MP1.organization_id
1429: (mp2.organization_id = mp3.master_organization_id))
1430: )
1431: and NOT EXISTS
1432: (select NULL
1433: from CST_ITEM_COSTS
1434: where inventory_item_id = bcolu.config_item_id
1435: and organization_id = mp1.cost_organization_id
1436: and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id))
1437: group by bcolu.config_item_id, C.organization_id, C.cost_type_id;
1476: end if;
1477:
1478:
1479: /*-------------------------------------------------------+
1480: Insert a row into the cst_item_costs_table
1481: +------------------------------------------------------- */
1482:
1483: lStmtNumber := 40;
1484:
1481: +------------------------------------------------------- */
1482:
1483: lStmtNumber := 40;
1484:
1485: insert into CST_ITEM_COSTS
1486: (inventory_item_id,
1487: organization_id,
1488: cost_type_id,
1489: last_update_date,
1587: C.attribute14,
1588: C.attribute15
1589: from
1590: mtl_parameters MP1,
1591: cst_item_costs C,
1592: mtl_system_items S,
1593: bom_cto_src_orgs bcso,
1594: bom_cto_order_lines_upg bcolu
1595: where bcolu.config_item_id is not null
1604: and C.organization_id = mp1.organization_id
1605: and mp1.organization_id = bcso.organization_id
1606: and NOT EXISTS
1607: (select NULL
1608: from CST_ITEM_COSTS
1609: where inventory_item_id = bcolu.config_item_id
1610: and organization_id = mp1.cost_organization_id --bugfix 3877097
1611: and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id));
1612:
1609: where inventory_item_id = bcolu.config_item_id
1610: and organization_id = mp1.cost_organization_id --bugfix 3877097
1611: and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id));
1612:
1613: WriteToLog('Inserted rows into cst_item_costs:: '||sql%rowcount,2);
1614:
1615:
1616:
1617:
1619:
1620:
1621: if( l_rt_cicd_summary.cost_type_id.count> 0 ) then
1622: FORALL j IN 1..l_rt_cicd_summary.cost_type_id.last
1623: UPDATE cst_item_costs
1624: set material_cost = l_rt_cicd_summary.material_cost(j),
1625: material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
1626: resource_cost = l_rt_cicd_summary.resource_cost(j),
1627: outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
1639: and cost_type_id = l_rt_cicd_summary.cost_type_id(j) ;
1640:
1641:
1642: IF PG_DEBUG <> 0 THEN
1643: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
1644: END IF;
1645: else
1646:
1647: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
1643: oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
1644: END IF;
1645: else
1646:
1647: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
1648:
1649: end if;
1650:
1651:
1704:
1705:
1706:
1707: lStmtNumber := 60;
1708: insert into CST_ITEM_COSTS
1709: (inventory_item_id,
1710: organization_id,
1711: cost_type_id,
1712: last_update_date,
1810: C.attribute14,
1811: C.attribute15
1812: from
1813: mtl_parameters MP1,
1814: cst_item_costs C,
1815: mtl_system_items S,
1816: bom_cto_src_orgs bcso,
1817: bom_cto_order_lines_upg bcolu
1818: where bcolu.config_item_id is not null
1828: and C.organization_id = bcso.organization_id
1829: and mp1.organization_id = bcso.organization_id
1830: and NOT EXISTS
1831: (select NULL
1832: from CST_ITEM_COSTS
1833: where inventory_item_id = bcolu.config_item_id
1834: and organization_id = mp1.cost_organization_id --bugfix 3877097
1835: and cost_type_id = l_cto_cost_type_id);
1836:
1833: where inventory_item_id = bcolu.config_item_id
1834: and organization_id = mp1.cost_organization_id --bugfix 3877097
1835: and cost_type_id = l_cto_cost_type_id);
1836:
1837: WriteToLog('Inserted rows into cst_item_costs:: '||sql%rowcount,2);
1838:
1839:
1840:
1841:
1843:
1844:
1845: if( l_rt_cicd_summary.cost_type_id.count > 0 ) then
1846: FORALL j IN 1..l_rt_cicd_summary.cost_type_id.last
1847: UPDATE cst_item_costs
1848: set material_cost = l_rt_cicd_summary.material_cost(j),
1849: material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
1850: resource_cost = l_rt_cicd_summary.resource_cost(j),
1851: outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
1863: and cost_type_id = l_cto_cost_type_id ;
1864:
1865:
1866: IF PG_DEBUG <> 0 THEN
1867: oe_debug_pub.add('Create_Item: ' || 'after update:cst_item_costs for CTO cost type '||to_char(sql%rowcount),2);
1868: END IF;
1869:
1870: else
1871:
1868: END IF;
1869:
1870: else
1871:
1872: oe_debug_pub.add( 'No update required to CST_ITEM_COSTS for CTO cost type as no new records inserted ' , 1 ) ;
1873:
1874: end if;
1875:
1876: