eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OPI_DBI_JOB_TXN_STG_PKG dependencies on STANDARD

Line 1788: standard_value_b,

1784: component_item_id,
1785: uom_code,
1786: line_type,
1787: transaction_date,
1788: standard_value_b,
1789: actual_value_b,
1790: actual_value_draft_b,
1791: standard_quantity,
1792: actual_quantity,

Line 1791: standard_quantity,

1787: transaction_date,
1788: standard_value_b,
1789: actual_value_b,
1790: actual_value_draft_b,
1791: standard_quantity,
1792: actual_quantity,
1793: actual_quantity_draft,
1794: source,
1795: creation_date,

Line 1821: 0, /* This fact will no more hold Stabdard Value and Standard Qty and hence 0 */

1817: trunc(jobs_txn.transaction_date),
1818: 0,
1819: sum(transaction_value_b+transaction_value_draft_b),
1820: sum(transaction_value_draft_b),
1821: 0, /* This fact will no more hold Stabdard Value and Standard Qty and hence 0 */
1822: sum(primary_quantity+primary_quantity_draft),
1823: sum(primary_quantity_draft),
1824: source,
1825: s_sysdate,

Line 1914: 0 standard_value_b,

1910: component_item_id component_item_id,
1911: uom_code uom_code,
1912: line_type line_type,
1913: trunc(jobs_txn.transaction_date) transaction_date,
1914: 0 standard_value_b,
1915: sum(transaction_value_b+transaction_value_draft_b) actual_value_b,
1916: sum(transaction_value_draft_b) actual_value_draft_b,
1917: 0 standard_quantity,
1918: sum(primary_quantity+primary_quantity_draft) actual_quantity,

Line 1917: 0 standard_quantity,

1913: trunc(jobs_txn.transaction_date) transaction_date,
1914: 0 standard_value_b,
1915: sum(transaction_value_b+transaction_value_draft_b) actual_value_b,
1916: sum(transaction_value_draft_b) actual_value_draft_b,
1917: 0 standard_quantity,
1918: sum(primary_quantity+primary_quantity_draft) actual_quantity,
1919: sum(primary_quantity_draft) actual_quantity_draft,
1920: source source,
1921: s_sysdate creation_date,

Line 1993: standard_value_b,

1989: component_item_id,
1990: uom_code,
1991: line_type,
1992: transaction_date,
1993: standard_value_b,
1994: actual_value_b,
1995: actual_value_draft_b,
1996: standard_quantity,
1997: actual_quantity,

Line 1996: standard_quantity,

1992: transaction_date,
1993: standard_value_b,
1994: actual_value_b,
1995: actual_value_draft_b,
1996: standard_quantity,
1997: actual_quantity,
1998: actual_quantity_draft,
1999: source,
2000: creation_date,

Line 2024: stg.standard_value_b,

2020: stg.component_item_id,
2021: stg.uom_code,
2022: stg.line_type,
2023: stg.transaction_date,
2024: stg.standard_value_b,
2025: stg.actual_value_b,
2026: stg.actual_value_draft_b,
2027: stg.standard_quantity,
2028: stg.actual_quantity,

Line 2027: stg.standard_quantity,

2023: stg.transaction_date,
2024: stg.standard_value_b,
2025: stg.actual_value_b,
2026: stg.actual_value_draft_b,
2027: stg.standard_quantity,
2028: stg.actual_quantity,
2029: stg.actual_quantity_draft,
2030: stg.source,
2031: stg.creation_date,

Line 2066: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact

2062:
2063: END GET_OPI_MTL_USAGE_ACT_INCR;
2064:
2065: /*
2066: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2067: for ODM, Initial Load procedure
2068:
2069: Parameters:
2070: retcode - 0 on successful completion, -1 on error and 1 for warning.

Line 2099: Standard_Quantity

2095: (ORGANIZATION_ID,
2096: INVENTORY_ITEM_ID,
2097: JOB_ID,
2098: JOB_TYPE,
2099: Standard_Quantity
2100: )
2101: SELECT
2102: ORGANIZATION_ID,
2103: INVENTORY_ITEM_ID,

Line 2106: Standard_Quantity

2102: ORGANIZATION_ID,
2103: INVENTORY_ITEM_ID,
2104: JOB_ID,
2105: JOB_TYPE,
2106: Standard_Quantity
2107: FROM
2108: (
2109: SELECT /* Standard Quantities for Discrete */
2110: WRO.ORGANIZATION_ID,

Line 2109: SELECT /* Standard Quantities for Discrete */

2105: JOB_TYPE,
2106: Standard_Quantity
2107: FROM
2108: (
2109: SELECT /* Standard Quantities for Discrete */
2110: WRO.ORGANIZATION_ID,
2111: WRO.INVENTORY_ITEM_ID,
2112: WRO.WIP_ENTITY_ID JOB_ID,
2113: decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,

Line 2114: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity

2110: WRO.ORGANIZATION_ID,
2111: WRO.INVENTORY_ITEM_ID,
2112: WRO.WIP_ENTITY_ID JOB_ID,
2113: decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,
2114: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2115: FROM
2116: WIP_ENTITIES WE,
2117: WIP_REQUIREMENT_OPERATIONS WRO
2118: WHERE

Line 2129: SELECT /* Standard Quantities for Repetitive */

2125: WRO.INVENTORY_ITEM_ID,
2126: WRO.WIP_ENTITY_ID,
2127: WE.ENTITY_TYPE
2128: UNION ALL
2129: SELECT /* Standard Quantities for Repetitive */
2130: WRO.ORGANIZATION_ID,
2131: WRO.INVENTORY_ITEM_ID,
2132: WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2133: 2 JOB_TYPE,

Line 2134: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity

2130: WRO.ORGANIZATION_ID,
2131: WRO.INVENTORY_ITEM_ID,
2132: WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2133: 2 JOB_TYPE,
2134: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2135: FROM
2136: WIP_ENTITIES WE,
2137: WIP_REQUIREMENT_OPERATIONS WRO
2138: WHERE

Line 2148: SELECT /* Standard Quantities for Flow

2144: WRO.ORGANIZATION_ID,
2145: WRO.INVENTORY_ITEM_ID,
2146: WRO.REPETITIVE_SCHEDULE_ID
2147: UNION ALL
2148: SELECT /* Standard Quantities for Flow
2149: Standard Qty for each component in BOM is multiplied with the planned
2150: qty from wfs for the assembly to get the standard qty for each component.
2151: */
2152: wfs.organization_id,

Line 2149: Standard Qty for each component in BOM is multiplied with the planned

2145: WRO.INVENTORY_ITEM_ID,
2146: WRO.REPETITIVE_SCHEDULE_ID
2147: UNION ALL
2148: SELECT /* Standard Quantities for Flow
2149: Standard Qty for each component in BOM is multiplied with the planned
2150: qty from wfs for the assembly to get the standard qty for each component.
2151: */
2152: wfs.organization_id,
2153: bom_join.component_item_id inventory_item_id,

Line 2150: qty from wfs for the assembly to get the standard qty for each component.

2146: WRO.REPETITIVE_SCHEDULE_ID
2147: UNION ALL
2148: SELECT /* Standard Quantities for Flow
2149: Standard Qty for each component in BOM is multiplied with the planned
2150: qty from wfs for the assembly to get the standard qty for each component.
2151: */
2152: wfs.organization_id,
2153: bom_join.component_item_id inventory_item_id,
2154: wfs.wip_entity_id JOB_ID,

Line 2156: SUM(bom_join.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity

2152: wfs.organization_id,
2153: bom_join.component_item_id inventory_item_id,
2154: wfs.wip_entity_id JOB_ID,
2155: 3 JOB_TYPE,
2156: SUM(bom_join.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity
2157: FROM
2158: ( select /*+ index(bb) */
2159: bb.organization_id organization_id,
2160: bb.assembly_item_id assembly_item_id,

Line 2172: bic.component_quantity Standard_Quantity

2168: bb.alternate_bom_designator,
2169: bic.component_item_id,
2170: bic.operation_seq_num
2171: order by effectivity_date), sysdate) last_rev,
2172: bic.component_quantity Standard_Quantity
2173: from
2174: bom_bill_of_materials bb,
2175: bom_inventory_components bic
2176: where

Line 2196: /* ODM Standards insert into fact table */

2192: bom_join.component_item_id,
2193: wfs.wip_entity_id,
2194: wfs.PLANNED_QUANTITY);
2195:
2196: /* ODM Standards insert into fact table */
2197: l_stmt_num := 30;
2198: INSERT
2199: INTO OPI_DBI_JOB_MTL_DTL_STD_F
2200: (

Line 2207: standard_quantity,

2203: job_type,
2204: assembly_item_id,
2205: component_item_id,
2206: line_type,
2207: standard_quantity,
2208: standard_value_b,
2209: source,
2210: creation_date,
2211: last_update_date,

Line 2208: standard_value_b,

2204: assembly_item_id,
2205: component_item_id,
2206: line_type,
2207: standard_quantity,
2208: standard_value_b,
2209: source,
2210: creation_date,
2211: last_update_date,
2212: created_by,

Line 2227: tmp.standard_quantity,

2223: actuals.job_type,
2224: actuals.assembly_item_id,
2225: actuals.component_item_id,
2226: actuals.line_type,
2227: tmp.standard_quantity,
2228: Decode(actuals.actual_quantity, 0,
2229: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2230: (actuals.organization_id,
2231: actuals.component_item_id),

Line 2229: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST

2225: actuals.component_item_id,
2226: actuals.line_type,
2227: tmp.standard_quantity,
2228: Decode(actuals.actual_quantity, 0,
2229: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2230: (actuals.organization_id,
2231: actuals.component_item_id),
2232: tmp.standard_quantity*(actual_value_b/actual_quantity)),
2233: actuals.source,

Line 2232: tmp.standard_quantity*(actual_value_b/actual_quantity)),

2228: Decode(actuals.actual_quantity, 0,
2229: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2230: (actuals.organization_id,
2231: actuals.component_item_id),
2232: tmp.standard_quantity*(actual_value_b/actual_quantity)),
2233: actuals.source,
2234: s_sysdate,
2235: s_sysdate,
2236: s_user_id,

Line 2276: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');

2272: tmp.job_type = actuals.job_type;
2273:
2274: l_row_count := sql%rowcount;
2275:
2276: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2277: BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2278:
2279: EXCEPTION
2280:

Line 2293: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact

2289:
2290: END GET_OPI_ODM_MTL_USAGE_STD_INIT;
2291:
2292: /*
2293: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2294: for ODM, Incremental Load procedure
2295:
2296: Parameters:
2297: retcode - 0 on successful completion, -1 on error and 1 for warning.

Line 2326: Standard_Quantity

2322: (ORGANIZATION_ID,
2323: INVENTORY_ITEM_ID,
2324: JOB_ID,
2325: JOB_TYPE,
2326: Standard_Quantity
2327: )
2328: SELECT
2329: ORGANIZATION_ID,
2330: INVENTORY_ITEM_ID,

Line 2333: Standard_Quantity

2329: ORGANIZATION_ID,
2330: INVENTORY_ITEM_ID,
2331: JOB_ID,
2332: JOB_TYPE,
2333: Standard_Quantity
2334: FROM
2335: (
2336: SELECT /* Standard Quantities for Discrete */
2337: WRO.ORGANIZATION_ID,

Line 2336: SELECT /* Standard Quantities for Discrete */

2332: JOB_TYPE,
2333: Standard_Quantity
2334: FROM
2335: (
2336: SELECT /* Standard Quantities for Discrete */
2337: WRO.ORGANIZATION_ID,
2338: WRO.INVENTORY_ITEM_ID,
2339: WRO.WIP_ENTITY_ID JOB_ID,
2340: decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,

Line 2341: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity

2337: WRO.ORGANIZATION_ID,
2338: WRO.INVENTORY_ITEM_ID,
2339: WRO.WIP_ENTITY_ID JOB_ID,
2340: decode(WE.ENTITY_TYPE,5,5,8,5,1) JOB_TYPE,
2341: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2342: FROM
2343: WIP_ENTITIES WE,
2344: WIP_REQUIREMENT_OPERATIONS WRO
2345: WHERE

Line 2356: SELECT /* Standard Quantities for Repetitive */

2352: WRO.INVENTORY_ITEM_ID,
2353: WRO.WIP_ENTITY_ID,
2354: WE.ENTITY_TYPE
2355: UNION ALL
2356: SELECT /* Standard Quantities for Repetitive */
2357: WRO.ORGANIZATION_ID,
2358: WRO.INVENTORY_ITEM_ID,
2359: WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2360: 2 JOB_TYPE,

Line 2361: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity

2357: WRO.ORGANIZATION_ID,
2358: WRO.INVENTORY_ITEM_ID,
2359: WRO.REPETITIVE_SCHEDULE_ID JOB_ID,
2360: 2 JOB_TYPE,
2361: SUM(WRO.REQUIRED_QUANTITY) Standard_Quantity
2362: FROM
2363: WIP_ENTITIES WE,
2364: WIP_REQUIREMENT_OPERATIONS WRO
2365: WHERE

Line 2375: SELECT /* Standard Quantities for Flow */

2371: WRO.ORGANIZATION_ID,
2372: WRO.INVENTORY_ITEM_ID,
2373: WRO.REPETITIVE_SCHEDULE_ID
2374: UNION ALL
2375: SELECT /* Standard Quantities for Flow */
2376: wfs.organization_id,
2377: t.component_item_id inventory_item_id,
2378: wfs.wip_entity_id JOB_ID,
2379: 3 JOB_TYPE,

Line 2380: SUM(t.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity

2376: wfs.organization_id,
2377: t.component_item_id inventory_item_id,
2378: wfs.wip_entity_id JOB_ID,
2379: 3 JOB_TYPE,
2380: SUM(t.Standard_Quantity) * wfs.PLANNED_QUANTITY Standard_Quantity
2381: FROM
2382: ( select /*+ index(bb) */
2383: bb.organization_id organization_id,
2384: bb.assembly_item_id assembly_item_id,

Line 2396: bic.component_quantity Standard_Quantity

2392: bb.alternate_bom_designator,
2393: bic.component_item_id,
2394: bic.operation_seq_num
2395: order by effectivity_date), sysdate) last_rev,
2396: bic.component_quantity Standard_Quantity
2397: from
2398: bom_bill_of_materials bb,
2399: bom_inventory_components bic
2400: where

Line 2420: /* ODM Standards merge into fact table */

2416: t.component_item_id,
2417: wfs.wip_entity_id,
2418: wfs.PLANNED_QUANTITY);
2419:
2420: /* ODM Standards merge into fact table */
2421: l_stmt_num := 30;
2422: MERGE
2423: INTO OPI_DBI_JOB_MTL_DTL_STD_F fact using
2424: (

Line 2432: tmp.standard_quantity standard_quantity,

2428: actuals.job_type job_type,
2429: actuals.assembly_item_id assembly_item_id,
2430: actuals.component_item_id component_item_id,
2431: actuals.line_type line_type,
2432: tmp.standard_quantity standard_quantity,
2433: Decode(actuals.actual_quantity, 0,
2434: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2435: (actuals.organization_id,
2436: actuals.component_item_id),

Line 2434: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST

2430: actuals.component_item_id component_item_id,
2431: actuals.line_type line_type,
2432: tmp.standard_quantity standard_quantity,
2433: Decode(actuals.actual_quantity, 0,
2434: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2435: (actuals.organization_id,
2436: actuals.component_item_id),
2437: tmp.standard_quantity*(actual_value_b/actual_quantity))
2438: standard_value_b,

Line 2437: tmp.standard_quantity*(actual_value_b/actual_quantity))

2433: Decode(actuals.actual_quantity, 0,
2434: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2435: (actuals.organization_id,
2436: actuals.component_item_id),
2437: tmp.standard_quantity*(actual_value_b/actual_quantity))
2438: standard_value_b,
2439: actuals.source source,
2440: s_sysdate creation_date,
2441: s_sysdate last_update_date,

Line 2438: standard_value_b,

2434: tmp.standard_quantity*OPI_DBI_JOBS_PKG.GET_ODM_ITEM_COST
2435: (actuals.organization_id,
2436: actuals.component_item_id),
2437: tmp.standard_quantity*(actual_value_b/actual_quantity))
2438: standard_value_b,
2439: actuals.source source,
2440: s_sysdate creation_date,
2441: s_sysdate last_update_date,
2442: s_user_id created_by,

Line 2492: fact.standard_quantity = stg.standard_quantity,

2488: fact.source = stg.source
2489: )
2490: WHEN MATCHED THEN
2491: UPDATE SET
2492: fact.standard_quantity = stg.standard_quantity,
2493: fact.standard_value_b = stg.standard_value_b,
2494: fact.creation_date = stg.creation_date,
2495: fact.last_update_date = stg.last_update_date,
2496: fact.created_by = stg.created_by,

Line 2493: fact.standard_value_b = stg.standard_value_b,

2489: )
2490: WHEN MATCHED THEN
2491: UPDATE SET
2492: fact.standard_quantity = stg.standard_quantity,
2493: fact.standard_value_b = stg.standard_value_b,
2494: fact.creation_date = stg.creation_date,
2495: fact.last_update_date = stg.last_update_date,
2496: fact.created_by = stg.created_by,
2497: fact.last_updated_by = stg.last_updated_by,

Line 2512: standard_quantity,

2508: job_type,
2509: assembly_item_id,
2510: component_item_id,
2511: line_type,
2512: standard_quantity,
2513: standard_value_b,
2514: source,
2515: creation_date,
2516: last_update_date,

Line 2513: standard_value_b,

2509: assembly_item_id,
2510: component_item_id,
2511: line_type,
2512: standard_quantity,
2513: standard_value_b,
2514: source,
2515: creation_date,
2516: last_update_date,
2517: created_by,

Line 2533: stg.standard_quantity,

2529: stg.job_type,
2530: stg.assembly_item_id,
2531: stg.component_item_id,
2532: stg.line_type,
2533: stg.standard_quantity,
2534: stg.standard_value_b,
2535: stg.source,
2536: stg.creation_date,
2537: stg.last_update_date,

Line 2534: stg.standard_value_b,

2530: stg.assembly_item_id,
2531: stg.component_item_id,
2532: stg.line_type,
2533: stg.standard_quantity,
2534: stg.standard_value_b,
2535: stg.source,
2536: stg.creation_date,
2537: stg.last_update_date,
2538: stg.created_by,

Line 2549: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');

2545: );
2546:
2547: l_row_count := sql%rowcount;
2548:
2549: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of ODM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2550: BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2551:
2552: EXCEPTION
2553:

Line 2566: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact

2562:
2563: END GET_OPI_ODM_MTL_USAGE_STD_INCR;
2564:
2565: /*
2566: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2567: for OPM, Initial Load procedure
2568:
2569: Parameters:
2570: retcode - 0 on successful completion, -1 on error and 1 for warning.

Line 2591: /* OPM Standards insert into fact table */

2587: l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_OPM_MTL_USAGE_STD_INIT';
2588:
2589: BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2590:
2591: /* OPM Standards insert into fact table */
2592: /* Actuals fact which is at transaction date level is summarised and joined with the
2593: scaled mtl table, standard value is calculated as actual-value* std_qty/actual_qty */
2594: l_stmt_num := 40;
2595: INSERT

Line 2593: scaled mtl table, standard value is calculated as actual-value* std_qty/actual_qty */

2589: BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2590:
2591: /* OPM Standards insert into fact table */
2592: /* Actuals fact which is at transaction date level is summarised and joined with the
2593: scaled mtl table, standard value is calculated as actual-value* std_qty/actual_qty */
2594: l_stmt_num := 40;
2595: INSERT
2596: INTO OPI_DBI_JOB_MTL_DTL_STD_F
2597: (

Line 2604: standard_quantity,

2600: job_type,
2601: assembly_item_id,
2602: component_item_id,
2603: line_type,
2604: standard_quantity,
2605: standard_value_b,
2606: source,
2607: creation_date,
2608: last_update_date,

Line 2605: standard_value_b,

2601: assembly_item_id,
2602: component_item_id,
2603: line_type,
2604: standard_quantity,
2605: standard_value_b,
2606: source,
2607: creation_date,
2608: last_update_date,
2609: created_by,

Line 2685: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');

2681: actuals.source;
2682:
2683: l_row_count := sql%rowcount;
2684:
2685: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2686: BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2687:
2688: EXCEPTION
2689:

Line 2702: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact

2698:
2699: END GET_OPI_OPM_MTL_USAGE_STD_INIT;
2700:
2701: /*
2702: Procedure populates the Material Usage Standards Fact from the Material Usage Actuals fact
2703: for OPM, Incremental Load procedure
2704:
2705: Parameters:
2706: retcode - 0 on successful completion, -1 on error and 1 for warning.

Line 2727: /* OPM Standards insert into fact table */

2723: l_proc_name := 'OPI_DBI_JOB_TXN_STG_PKG.GET_OPI_OPM_MTL_USAGE_STD_INCR';
2724:
2725: BIS_COLLECTION_UTILITIES.PUT_LINE('Entering Procedure '|| l_proc_name);
2726:
2727: /* OPM Standards insert into fact table */
2728: l_stmt_num := 40;
2729: MERGE
2730: INTO OPI_DBI_JOB_MTL_DTL_STD_F fact using
2731: (

Line 2739: sum(tmp.scaled_plan_qty*decode(actuals.line_type,2,-1,1)) standard_quantity,

2735: tmp.job_type job_type,
2736: tmp.coproduct_id assembly_item_id,
2737: tmp.item_id component_item_id,
2738: actuals.line_type line_type,
2739: sum(tmp.scaled_plan_qty*decode(actuals.line_type,2,-1,1)) standard_quantity,
2740: sum(decode(actuals.actual_qty,0,
2741: OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(tmp.organization_id,
2742: tmp.item_id,
2743: tmp.completion_date),

Line 2745: *decode(actuals.line_type,2,-1,1)) standard_value_b,

2741: OPI_DBI_JOBS_PKG.GET_OPM_ITEM_COST(tmp.organization_id,
2742: tmp.item_id,
2743: tmp.completion_date),
2744: actuals.actual_value_b*tmp.scaled_plan_qty/actuals.actual_qty)
2745: *decode(actuals.line_type,2,-1,1)) standard_value_b,
2746: actuals.source source,
2747: s_sysdate creation_date,
2748: s_sysdate last_update_date,
2749: s_user_id created_by,

Line 2809: fact.standard_quantity = stg.standard_quantity,

2805: fact.source = stg.source
2806: )
2807: WHEN MATCHED THEN
2808: UPDATE SET
2809: fact.standard_quantity = stg.standard_quantity,
2810: fact.standard_value_b = stg.standard_value_b,
2811: fact.last_update_date = stg.last_update_date,
2812: fact.last_updated_by = stg.last_updated_by,
2813: fact.last_update_login = stg.last_update_login

Line 2810: fact.standard_value_b = stg.standard_value_b,

2806: )
2807: WHEN MATCHED THEN
2808: UPDATE SET
2809: fact.standard_quantity = stg.standard_quantity,
2810: fact.standard_value_b = stg.standard_value_b,
2811: fact.last_update_date = stg.last_update_date,
2812: fact.last_updated_by = stg.last_updated_by,
2813: fact.last_update_login = stg.last_update_login
2814: WHEN NOT MATCHED THEN

Line 2823: standard_quantity,

2819: job_type,
2820: assembly_item_id,
2821: component_item_id,
2822: line_type,
2823: standard_quantity,
2824: standard_value_b,
2825: source,
2826: creation_date,
2827: last_update_date,

Line 2824: standard_value_b,

2820: assembly_item_id,
2821: component_item_id,
2822: line_type,
2823: standard_quantity,
2824: standard_value_b,
2825: source,
2826: creation_date,
2827: last_update_date,
2828: created_by,

Line 2844: stg.standard_quantity,

2840: stg.job_type,
2841: stg.assembly_item_id,
2842: stg.component_item_id,
2843: stg.line_type,
2844: stg.standard_quantity,
2845: stg.standard_value_b,
2846: stg.source,
2847: stg.creation_date,
2848: stg.last_update_date,

Line 2845: stg.standard_value_b,

2841: stg.assembly_item_id,
2842: stg.component_item_id,
2843: stg.line_type,
2844: stg.standard_quantity,
2845: stg.standard_value_b,
2846: stg.source,
2847: stg.creation_date,
2848: stg.last_update_date,
2849: stg.created_by,

Line 2860: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');

2856: );
2857:
2858: l_row_count := sql%rowcount;
2859:
2860: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished extraction of OPM MTL USAGE Standards Table: '|| l_row_count || ' rows inserted');
2861: BIS_COLLECTION_UTILITIES.PUT_LINE('Exiting Procedure '|| l_proc_name);
2862:
2863: EXCEPTION
2864:

Line 4526: --Populate ODM MU Standards to fact Table

4522: --Populate MU Actuals to fact Table
4523: l_stmt_num := 70;
4524: GET_OPI_MTL_USAGE_ACT_INIT(errbuf => errbuf,retcode => retcode);
4525:
4526: --Populate ODM MU Standards to fact Table
4527: l_stmt_num := 80;
4528: GET_OPI_ODM_MTL_USAGE_STD_INIT(errbuf => errbuf,retcode => retcode);
4529:
4530: --Populate OPM MU Standards to fact Table

Line 4530: --Populate OPM MU Standards to fact Table

4526: --Populate ODM MU Standards to fact Table
4527: l_stmt_num := 80;
4528: GET_OPI_ODM_MTL_USAGE_STD_INIT(errbuf => errbuf,retcode => retcode);
4529:
4530: --Populate OPM MU Standards to fact Table
4531: l_stmt_num := 90;
4532: GET_OPI_OPM_MTL_USAGE_STD_INIT(errbuf => errbuf,retcode => retcode);
4533:
4534: --Populate WIP Completions Fact

Line 4643: --Populate ODM MU Standards to fact Table

4639: --Populate MU Actuals to fact Table
4640: l_stmt_num := 70;
4641: GET_OPI_MTL_USAGE_ACT_INCR(errbuf => errbuf,retcode => retcode);
4642:
4643: --Populate ODM MU Standards to fact Table
4644: l_stmt_num := 80;
4645: GET_OPI_ODM_MTL_USAGE_STD_INCR(errbuf => errbuf,retcode => retcode);
4646:
4647: --Populate OPM MU Standards to fact Table

Line 4647: --Populate OPM MU Standards to fact Table

4643: --Populate ODM MU Standards to fact Table
4644: l_stmt_num := 80;
4645: GET_OPI_ODM_MTL_USAGE_STD_INCR(errbuf => errbuf,retcode => retcode);
4646:
4647: --Populate OPM MU Standards to fact Table
4648: l_stmt_num := 90;
4649: GET_OPI_OPM_MTL_USAGE_STD_INCR(errbuf => errbuf,retcode => retcode);
4650:
4651: --Populate WIP Completions Fact