[Home] [Help]
416: return g_error;
417:
418: END get_conversion_rate;
419:
420: -- Replace standard costs with get_cost for rows where zero std cost
421: -- was caused by actual qty = 0 ans std qty <> 0
422: procedure Fix_OPM_Std_costs
423: is
424: l_stmt_num Number;
463: -- bulk costing results to update fact
464:
465:
466: update /*+ parallel(f) */ OPI_DBI_JOB_MTL_DETAILS_F f
467: set STANDARD_VALUE_B =
468: (select f.standard_quantity * costs.total_cost
469: from opi_pmi_cost_result_gtmp costs,
470: OPI_DBI_JOBS_F jobs,
471: ic_whse_mst whse,
464:
465:
466: update /*+ parallel(f) */ OPI_DBI_JOB_MTL_DETAILS_F f
467: set STANDARD_VALUE_B =
468: (select f.standard_quantity * costs.total_cost
469: from opi_pmi_cost_result_gtmp costs,
470: OPI_DBI_JOBS_F jobs,
471: ic_whse_mst whse,
472: ic_item_mst_b item,
483: and f.organization_id = msi.organization_id
484: and msi.segment1 = item.item_no
485: and costs.item_id = item.item_id
486: )
487: where f.standard_value_b = 0
488: and f.actual_value_b = 0
489: and f.actual_quantity = 0
490: and f.standard_quantity <> 0
491: and f.job_type= 4 -- OPM jobs
486: )
487: where f.standard_value_b = 0
488: and f.actual_value_b = 0
489: and f.actual_quantity = 0
490: and f.standard_quantity <> 0
491: and f.job_type= 4 -- OPM jobs
492: and f.source = 2; -- OPM source
493:
494:
527: ,Job_Id
528: ,Job_Type
529: ,Assembly_Item_id
530: ,Closed_date
531: ,standard_value_b
532: ,actual_value_b
533: ,standard_value_g
534: ,actual_value_g
535: ,standard_value_sg
529: ,Assembly_Item_id
530: ,Closed_date
531: ,standard_value_b
532: ,actual_value_b
533: ,standard_value_g
534: ,actual_value_g
535: ,standard_value_sg
536: ,actual_value_sg
537: ,Actual_Qty_Completed
531: ,standard_value_b
532: ,actual_value_b
533: ,standard_value_g
534: ,actual_value_g
535: ,standard_value_sg
536: ,actual_value_sg
537: ,Actual_Qty_Completed
538: ,UOM_Code
539: ,Conversion_rate
554: var.job_id job_id,
555: var.job_type job_type,
556: var.assembly_item_id assembly_item_id,
557: var.closed_date closed_date,
558: sum (standard_value_b * nvl(dtl.cost_alloc, 0)) standard_value_b,
559: sum (actual_value_b * nvl(dtl.cost_alloc, 0)) actual_value_b,
560: sum (standard_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) standard_value_g,
561: sum (standard_value_b * nvl(dtl.cost_alloc, 0)*sec_conversion_rate) standard_value_sg,
562: sum (actual_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) actual_value_g,
556: var.assembly_item_id assembly_item_id,
557: var.closed_date closed_date,
558: sum (standard_value_b * nvl(dtl.cost_alloc, 0)) standard_value_b,
559: sum (actual_value_b * nvl(dtl.cost_alloc, 0)) actual_value_b,
560: sum (standard_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) standard_value_g,
561: sum (standard_value_b * nvl(dtl.cost_alloc, 0)*sec_conversion_rate) standard_value_sg,
562: sum (actual_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) actual_value_g,
563: sum (actual_value_b * nvl(dtl.cost_alloc, 0)*sec_conversion_rate) actual_value_sg,
564: sum (actual_qty_completed) actual_qty_completed,
557: var.closed_date closed_date,
558: sum (standard_value_b * nvl(dtl.cost_alloc, 0)) standard_value_b,
559: sum (actual_value_b * nvl(dtl.cost_alloc, 0)) actual_value_b,
560: sum (standard_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) standard_value_g,
561: sum (standard_value_b * nvl(dtl.cost_alloc, 0)*sec_conversion_rate) standard_value_sg,
562: sum (actual_value_b * nvl(dtl.cost_alloc, 0)*conversion_rate) actual_value_g,
563: sum (actual_value_b * nvl(dtl.cost_alloc, 0)*sec_conversion_rate) actual_value_sg,
564: sum (actual_qty_completed) actual_qty_completed,
565: uom_code uom_code,
583: led.job_type job_type,
584: led.assembly_item_id assembly_item_id,
585: led.completion_date closed_date,
586: sum (led.actual_value_b) actual_value_b,
587: sum (led.standard_value_b) standard_value_b,
588: led.actual_qty_completed actual_qty_completed,
589: led.uom_code uom_code,
590: rates.conversion_rate conversion_rate,
591: rates.sec_conversion_rate sec_conversion_rate
615: decode (sub_event_type, 50040,
616: decode(jobs.line_type,1,gsl.amount_base * gsl.debit_credit_sign,0),
617: /* decode else */
618: 0),
619: /* decode else */ 0 ) ) Standard_Value_B
620: FROM
621: GL_SUBR_LED gsl,
622: (
623: select
778: ,Job_Id
779: ,Job_Type
780: ,Assembly_Item_id
781: ,Closed_date
782: ,standard_value_b
783: ,actual_value_b
784: ,standard_value_g
785: ,actual_value_g
786: ,standard_value_sg
780: ,Assembly_Item_id
781: ,Closed_date
782: ,standard_value_b
783: ,actual_value_b
784: ,standard_value_g
785: ,actual_value_g
786: ,standard_value_sg
787: ,actual_value_sg
788: ,Actual_Qty_Completed
782: ,standard_value_b
783: ,actual_value_b
784: ,standard_value_g
785: ,actual_value_g
786: ,standard_value_sg
787: ,actual_value_sg
788: ,Actual_Qty_Completed
789: ,UOM_Code
790: ,Conversion_rate
810: nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
811: nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
812: nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
813: nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
814: nvl(pl_outside_processing_out,0) ) standard_value_b,
815: sum (nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
816: nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
817: nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
818: nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
821: nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
822: nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
823: nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
824: nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
825: nvl(pl_outside_processing_out,0))*jobs.conversion_rate) standard_value_g,
826: sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
827: nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
828: nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
829: nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
832: nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
833: nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
834: nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
835: nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
836: nvl(pl_outside_processing_out,0))*jobs.sec_conversion_rate) standard_value_sg,
837: sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
838: nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
839: nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
840: nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
879: var.job_id job_id,
880: var.job_type job_type,
881: var.assembly_item_id assembly_item_id,
882: var.closed_date closed_date,
883: var.standard_value_b,
884: var.actual_value_b,
885: var.standard_value_b*var.conversion_rate standard_value_g,
886: var.actual_value_b*var.conversion_rate actual_value_g,
887: var.standard_value_b*var.sec_conversion_rate standard_value_sg,
881: var.assembly_item_id assembly_item_id,
882: var.closed_date closed_date,
883: var.standard_value_b,
884: var.actual_value_b,
885: var.standard_value_b*var.conversion_rate standard_value_g,
886: var.actual_value_b*var.conversion_rate actual_value_g,
887: var.standard_value_b*var.sec_conversion_rate standard_value_sg,
888: var.actual_value_b*var.sec_conversion_rate actual_value_sg,
889: var.actual_qty_completed,
883: var.standard_value_b,
884: var.actual_value_b,
885: var.standard_value_b*var.conversion_rate standard_value_g,
886: var.actual_value_b*var.conversion_rate actual_value_g,
887: var.standard_value_b*var.sec_conversion_rate standard_value_sg,
888: var.actual_value_b*var.sec_conversion_rate actual_value_sg,
889: var.actual_qty_completed,
890: var.uom_code uom_code,
891: var.conversion_rate conversion_rate,
912: jobs.completion_date closed_date,
913: jobs.conversion_rate,
914: jobs.sec_conversion_rate,
915: -sum(Decode(jobs.line_type,1,decode(jobs.line_id, mtl_dtl.material_detail_id,
916: -txn_base_value,0),0)) standard_Value_b,
917: -sum(Decode(jobs.line_type,-1,txn_base_value*mtl_dtl.cost_alloc,
918: 2,txn_base_value*mtl_dtl.cost_alloc, 0)) actual_Value_b
919: from
920: GMF_TRANSACTION_VALUATION GTV,
1077: nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
1078: nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
1079: nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
1080: nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
1081: nvl(pl_outside_processing_out,0) ) Standard_Value_B,
1082: sum (nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
1083: nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
1084: nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
1085: nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
1088: nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
1089: nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
1090: nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
1091: nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
1092: nvl(pl_outside_processing_out,0))*rates.Conversion_Rate) Standard_Value_G,
1093: sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
1094: nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
1095: nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
1096: nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
1099: nvl(tl_resource_out,0) + nvl(tl_overhead_out,0) +
1100: nvl(tl_outside_processing_out,0) + nvl(tl_scrap_out,0) +
1101: nvl(pl_material_out,0) + nvl(pl_material_overhead_out,0) +
1102: nvl(pl_resource_out,0) + nvl(pl_overhead_out,0) +
1103: nvl(pl_outside_processing_out,0))*rates.sec_Conversion_Rate) Standard_Value_SG,
1104: sum ((nvl(tl_resource_in,0) + nvl(tl_overhead_in,0) +
1105: nvl(tl_outside_processing_in,0) + nvl(pl_material_in,0) +
1106: nvl(pl_material_overhead_in,0) + nvl(pl_resource_in,0) +
1107: nvl(pl_overhead_in,0) + nvl(pl_outside_processing_in,0) +
1216: AND F.Assembly_Item_id = V.Assembly_Item_id)
1217: WHEN MATCHED THEN
1218: UPDATE SET
1219: F.Closed_date = V.Closed_date
1220: ,F.Standard_Value_B = V.Standard_Value_B
1221: ,F.Actual_Value_B = V.Actual_Value_B
1222: ,F.Actual_Qty_Completed = V.Actual_Qty_Completed
1223: ,F.Conversion_rate = V.Conversion_rate
1224: ,F.Sec_Conversion_rate = V.Sec_Conversion_rate
1231: Job_Id,
1232: Job_Type,
1233: Assembly_Item_id,
1234: Closed_date,
1235: Standard_Value_B,
1236: Actual_Value_B,
1237: Standard_Value_G,
1238: Actual_Value_G,
1239: Standard_Value_SG,
1233: Assembly_Item_id,
1234: Closed_date,
1235: Standard_Value_B,
1236: Actual_Value_B,
1237: Standard_Value_G,
1238: Actual_Value_G,
1239: Standard_Value_SG,
1240: Actual_Value_SG,
1241: Actual_Qty_Completed,
1235: Standard_Value_B,
1236: Actual_Value_B,
1237: Standard_Value_G,
1238: Actual_Value_G,
1239: Standard_Value_SG,
1240: Actual_Value_SG,
1241: Actual_Qty_Completed,
1242: UOM_Code,
1243: Conversion_rate,
1257: V.Job_Id,
1258: V.Job_Type,
1259: V.Assembly_Item_id,
1260: V.Closed_date,
1261: V.Standard_Value_B,
1262: V.Actual_Value_B,
1263: V.Standard_Value_G,
1264: V.Actual_Value_G,
1265: V.Standard_Value_SG,
1259: V.Assembly_Item_id,
1260: V.Closed_date,
1261: V.Standard_Value_B,
1262: V.Actual_Value_B,
1263: V.Standard_Value_G,
1264: V.Actual_Value_G,
1265: V.Standard_Value_SG,
1266: V.Actual_Value_SG,
1267: V.Actual_Qty_Completed,
1261: V.Standard_Value_B,
1262: V.Actual_Value_B,
1263: V.Standard_Value_G,
1264: V.Actual_Value_G,
1265: V.Standard_Value_SG,
1266: V.Actual_Value_SG,
1267: V.Actual_Qty_Completed,
1268: V.UOM_Code,
1269: V.Conversion_rate,
1293: var.job_id job_id,
1294: var.job_type job_type,
1295: var.assembly_item_id assembly_item_id,
1296: var.closed_date closed_date,
1297: var.standard_value_b,
1298: var.actual_value_b,
1299: var.actual_qty_completed,
1300: var.uom_code uom_code,
1301: rates.conversion_rate conversion_rate,
1299: var.actual_qty_completed,
1300: var.uom_code uom_code,
1301: rates.conversion_rate conversion_rate,
1302: rates.sec_conversion_rate sec_conversion_rate,
1303: var.standard_value_b*rates.conversion_rate standard_value_g,
1304: var.actual_value_b*conversion_rate actual_value_g,
1305: var.standard_value_b*sec_conversion_rate standard_value_sg,
1306: var.actual_value_b*sec_conversion_rate actual_value_sg,
1307: 2 source,
1301: rates.conversion_rate conversion_rate,
1302: rates.sec_conversion_rate sec_conversion_rate,
1303: var.standard_value_b*rates.conversion_rate standard_value_g,
1304: var.actual_value_b*conversion_rate actual_value_g,
1305: var.standard_value_b*sec_conversion_rate standard_value_sg,
1306: var.actual_value_b*sec_conversion_rate actual_value_sg,
1307: 2 source,
1308: g_sysdate creation_date,
1309: g_sysdate last_update_date,
1324: jobs.actual_qty_completed,
1325: jobs.uom_code,
1326: jobs.completion_date closed_date,
1327: -sum(Decode(jobs.line_type,1,decode(jobs.line_id, mtl_dtl.material_detail_id,
1328: -txn_base_value,0),0)) standard_Value_b,
1329: -sum(Decode(jobs.line_type,-1,txn_base_value*mtl_dtl.cost_alloc,
1330: 2,txn_base_value*mtl_dtl.cost_alloc, 0)) actual_Value_b
1331: from
1332: (
1414: AND F.Assembly_Item_id = V.Assembly_Item_id)
1415: WHEN MATCHED THEN
1416: UPDATE SET
1417: F.Closed_date = V.Closed_date
1418: ,F.Standard_Value_B = V.Standard_Value_B
1419: ,F.Actual_Value_B = V.Actual_Value_B
1420: ,F.Actual_Qty_Completed = V.Actual_Qty_Completed
1421: ,F.Conversion_rate = V.Conversion_rate
1422: ,F.Sec_Conversion_rate = V.Sec_Conversion_rate
1429: Job_Id,
1430: Job_Type,
1431: Assembly_Item_id,
1432: Closed_date,
1433: Standard_Value_B,
1434: Actual_Value_B,
1435: Actual_Qty_Completed,
1436: UOM_Code,
1437: Conversion_rate,
1451: V.Job_Id,
1452: V.Job_Type,
1453: V.Assembly_Item_id,
1454: V.Closed_date,
1455: V.Standard_Value_B,
1456: V.Actual_Value_B,
1457: V.Actual_Qty_Completed,
1458: V.UOM_Code,
1459: V.Conversion_rate,
1564: organization_id
1565: ,inventory_item_id
1566: ,item_org_id
1567: ,inv_category_id
1568: ,standard_value_b
1569: ,standard_value_g
1570: ,standard_value_sg
1571: ,actual_value_b
1572: ,actual_value_g
1565: ,inventory_item_id
1566: ,item_org_id
1567: ,inv_category_id
1568: ,standard_value_b
1569: ,standard_value_g
1570: ,standard_value_sg
1571: ,actual_value_b
1572: ,actual_value_g
1573: ,actual_value_sg
1566: ,item_org_id
1567: ,inv_category_id
1568: ,standard_value_b
1569: ,standard_value_g
1570: ,standard_value_sg
1571: ,actual_value_b
1572: ,actual_value_g
1573: ,actual_value_sg
1574: ,actual_prd_qty
1590: act_std.organization_id organization_id,
1591: act_std.inventory_item_id inventory_item_id,
1592: act_std.inventory_item_id||'-'||act_std.organization_id item_org_id,
1593: nvl(cat.inv_category_id,-1) inv_category_id,
1594: act_std.std_val_b standard_value_b,
1595: act_std.std_val_b * conv.conversion_rate standard_value_g,
1596: act_std.std_val_b * conv.sec_conversion_rate standard_value_sg,
1597: act_std.act_val_b actual_value_b,
1598: act_std.act_val_b * conv.conversion_rate actual_value_g,
1591: act_std.inventory_item_id inventory_item_id,
1592: act_std.inventory_item_id||'-'||act_std.organization_id item_org_id,
1593: nvl(cat.inv_category_id,-1) inv_category_id,
1594: act_std.std_val_b standard_value_b,
1595: act_std.std_val_b * conv.conversion_rate standard_value_g,
1596: act_std.std_val_b * conv.sec_conversion_rate standard_value_sg,
1597: act_std.act_val_b actual_value_b,
1598: act_std.act_val_b * conv.conversion_rate actual_value_g,
1599: act_std.act_val_b * conv.sec_conversion_rate actual_value_sg,
1592: act_std.inventory_item_id||'-'||act_std.organization_id item_org_id,
1593: nvl(cat.inv_category_id,-1) inv_category_id,
1594: act_std.std_val_b standard_value_b,
1595: act_std.std_val_b * conv.conversion_rate standard_value_g,
1596: act_std.std_val_b * conv.sec_conversion_rate standard_value_sg,
1597: act_std.act_val_b actual_value_b,
1598: act_std.act_val_b * conv.conversion_rate actual_value_g,
1599: act_std.act_val_b * conv.sec_conversion_rate actual_value_sg,
1600: act_std.act_prd_qty act_prd_qty,
1662: wip_discrete_jobs wdj,
1663: wip_entities we
1664: WHERE wdj.Status_Type <> 12 -- Not closed
1665: AND we.entity_type IN (1,3, 5)
1666: AND wdj.job_type = 1 -- Standard Jobs only
1667: AND wdj.wip_entity_id = we.wip_entity_id
1668: AND wdj.organization_id = we.organization_id
1669: AND wdj.date_released >= g_global_start_date
1670: UNION
1696: icosts.organization_id
1697: AND not_cl_jobs.inventory_item_id =
1698: icosts.inventory_item_id
1699: AND icosts.cost_type_id in (1,2,5,6)
1700: ) std_val_and_qty, -- Standard Values and Start and Completed Quantities
1701: (
1702: SELECT /*+ no_merge parallel(WPB) */
1703: wpb.organization_id organization_id,
1704: wpb.wip_entity_id || decode(wpb.repetitive_schedule_id, null, null, '-'||wpb.repetitive_schedule_id) job_id,
1742: var.organization_id,
1743: var.assembly_item_id,
1744: var.assembly_item_id||'-'||var.organization_id,
1745: cat.inv_category_id,
1746: standard_value_b,
1747: standard_value_b*conversion_rate standard_value_g,
1748: standard_value_b*sec_conversion_rate standard_value_sg,
1749: actual_value_b,
1750: actual_value_b*conversion_rate standard_value_g,
1743: var.assembly_item_id,
1744: var.assembly_item_id||'-'||var.organization_id,
1745: cat.inv_category_id,
1746: standard_value_b,
1747: standard_value_b*conversion_rate standard_value_g,
1748: standard_value_b*sec_conversion_rate standard_value_sg,
1749: actual_value_b,
1750: actual_value_b*conversion_rate standard_value_g,
1751: actual_value_b*sec_conversion_rate standard_value_sg,
1744: var.assembly_item_id||'-'||var.organization_id,
1745: cat.inv_category_id,
1746: standard_value_b,
1747: standard_value_b*conversion_rate standard_value_g,
1748: standard_value_b*sec_conversion_rate standard_value_sg,
1749: actual_value_b,
1750: actual_value_b*conversion_rate standard_value_g,
1751: actual_value_b*sec_conversion_rate standard_value_sg,
1752: var.actual_qty_completed,
1746: standard_value_b,
1747: standard_value_b*conversion_rate standard_value_g,
1748: standard_value_b*sec_conversion_rate standard_value_sg,
1749: actual_value_b,
1750: actual_value_b*conversion_rate standard_value_g,
1751: actual_value_b*sec_conversion_rate standard_value_sg,
1752: var.actual_qty_completed,
1753: var.uom_code,
1754: 2 source,
1747: standard_value_b*conversion_rate standard_value_g,
1748: standard_value_b*sec_conversion_rate standard_value_sg,
1749: actual_value_b,
1750: actual_value_b*conversion_rate standard_value_g,
1751: actual_value_b*sec_conversion_rate standard_value_sg,
1752: var.actual_qty_completed,
1753: var.uom_code,
1754: 2 source,
1755: g_sysdate creation_date,
1775: jobs.organization_id,
1776: jobs.assembly_item_id,
1777: sysdate)*
1778: decode(jobs.status,'Complete',jobs.actual_qty_completed,
1779: greatest(Jobs.planned_qty,jobs.actual_qty_completed)) standard_value_b,
1780: Sum(Decode(jobs.line_type,-1,txn_base_value*mtl_dtl.cost_alloc,
1781: 2,txn_base_value*mtl_dtl.cost_alloc, 0)) Actual_Value_b,
1782: jobs.planned_qty planned_qty,
1783: jobs.actual_qty_completed,
1860: where
1861: var.organization_id = rates.organization_id and
1862: var.assembly_item_id = cat.inventory_item_id and
1863: var.organization_id = cat.organization_id and
1864: var.actual_value_b > var.standard_value_b;
1865:
1866: l_row_count := sql%rowcount;
1867:
1868: commit;