德意志血統 iDempiere 超越土產ERP 困境的最佳選擇: 2月 2015

2015年2月10日星期二

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 v$locked_object

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 v$locked_object

SELECT vs.USERNAME, lo.OBJECT_ID, sob.NAEM, lo.SESSION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS
FROM V$LOCKED_OBJECT lo
INNER JOIN V$SESSION vs ON lo.SESSION_ID = vs.SID
INNER JOIN SYS.OBJ$ sob ON sob.obj# = lo.OBJECT_ID;

-- iDempiere 去抓取 Oracle EBS 送出解開被鎖死的程序 Interrupt the process deadlock,

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

SELECT c.owner, c.object_name, c.object_type, fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time,
vs.module, vs.machine, vs.osuser,vlocked.oracle_username,
vs.SID, vp.pid, vp.spid AS os_process,
vs.serial#, vs.status, vs.saddr, vs.audsid, vs.process
FROM v$locked_object vlocked
INNER JOIN dba_objects c ON vlocked.object_id = c.object_id
INNER JOIN v$session vs ON vs.SID = vlocked.session_id
INNER JOIN v$process vp ON vs.paddr = vp.addr
LEFT OUTER JOIN fnd_logins fl ON vp.spid = fl.process_spid AND vp.pid = fl.pid
LEFT OUTER JOIN fnd_user fu ON fl.user_id = fu.user_id
WHERE 1=1
AND NVL (vs.status, 'XX')<> 'KILLED';

-- iDempiere 去抓取 Oracle EBS 送出解開被鎖死的程序 Interrupt the process deadlock,

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 Find a deadlock process

SELECT vs.USERNAME, lo.OBJECT_ID, sob.NAEM, lo.SESSION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS
FROM V$LOCKED_OBJECT lo
INNER JOIN V$SESSION vs ON lo.SESSION_ID = vs.SID
INNER JOIN SYS.OBJ$ sob ON sob.obj# = lo.OBJECT_ID;

-- iDempiere 去抓取 Oracle EBS 送出解開被鎖死的程序 Interrupt the process deadlock,

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 Find a deadlock process

2015年2月1日星期日

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AP_CAL_WEEK_START_DATES_V

DROP VIEW APPS.MRP_AP_CAL_WEEK_START_DATES_V;

/* Formatted on 2015/2/1 下午 05:04:47 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AP_CAL_WEEK_START_DATES_V
(
   CALENDAR_CODE,
   EXCEPTION_SET_ID,
   WEEK_START_DATE,
   NEXT_DATE,
   PRIOR_DATE,
   SEQ_NUM,
   RN3,
   RN2,
   RN1
)
AS
   SELECT x.CALENDAR_CODE,
          x.EXCEPTION_SET_ID,
          x.WEEK_START_DATE,
          x.NEXT_DATE,
          x.PRIOR_DATE,
          x.SEQ_NUM,
          0 RN3,
          0 RN2,
          0 RN1
     FROM BOM_CAL_WEEK_START_DATES x,
          (SELECT DISTINCT Calendar_Code, Calendar_Exception_Set_ID
             FROM MTL_PARAMETERS mtlp
           UNION
           SELECT DISTINCT
                  Delivery_Calendar Calendar_Code,
                  -1 Calendar_Exception_Set_ID
             FROM PO_ASL_ATTRIBUTES) y
    WHERE     x.Calendar_Code = y.Calendar_Code
          AND x.Exception_Set_ID = y.Calendar_Exception_Set_ID;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AP_BIS_PERIODS_V

DROP VIEW APPS.MRP_AP_BIS_PERIODS_V;

/* Formatted on 2015/2/1 下午 05:04:08 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AP_BIS_PERIODS_V
(
   ORGANIZATION_ID,
   PERIOD_SET_NAME,
   PERIOD_NAME,
   START_DATE,
   END_DATE,
   PERIOD_TYPE,
   PERIOD_YEAR,
   PERIOD_NUM,
   QUARTER_NUM,
   ENTERED_PERIOD_NAME,
   ADJUSTMENT_PERIOD_FLAG,
   DESCRIPTION,
   CONTEXT,
   YEAR_START_DATE,
   QUARTER_START_DATE
)
AS
   SELECT ood.ORGANIZATION_ID,
          gp.PERIOD_SET_NAME,
          gp.PERIOD_NAME,
          gp.START_DATE,
          gp.END_DATE,
          gp.PERIOD_TYPE,
          gp.PERIOD_YEAR,
          gp.PERIOD_NUM,
          gp.QUARTER_NUM,
          gp.ENTERED_PERIOD_NAME,
          gp.ADJUSTMENT_PERIOD_FLAG,
          gp.DESCRIPTION,
          gp.CONTEXT,
          gp.YEAR_START_DATE,
          gp.QUARTER_START_DATE
     FROM GL_PERIODS gp,
          ORG_ORGANIZATION_DEFINITIONS ood,
          gl_sets_of_books gsob
    WHERE     ood.set_of_books_id = gsob.set_of_books_id
          AND gsob.period_set_name = gp.period_set_name
          AND gsob.ACCOUNTED_PERIOD_TYPE = gp.PERIOD_TYPE;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AP_BILL_OF_RESOURCES_V

DROP VIEW APPS.MRP_AP_BILL_OF_RESOURCES_V;

/* Formatted on 2015/2/1 下午 05:03:35 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AP_BILL_OF_RESOURCES_V
(
   BILL_OF_RESOURCES,
   ORGANIZATION_ID,
   DESCRIPTION,
   DISABLE_DATE,
   ROLLUP_START_DATE,
   ROLLUP_COMPLETION_DATE,
   RN2,
   RN1
)
AS
   SELECT x.BILL_OF_RESOURCES,
          x.ORGANIZATION_ID,
          x.DESCRIPTION,
          x.DISABLE_DATE,
          x.ROLLUP_START_DATE,
          x.ROLLUP_COMPLETION_DATE,
          0 RN2,
          0 RN1
     FROM CRP_BILLS_OF_RESOURCES x;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AP_ATP_RULES_V

DROP VIEW APPS.MRP_AP_ATP_RULES_V;

/* Formatted on 2015/2/1 下午 05:02:56 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AP_ATP_RULES_V
(
   RULE_ID,
   RULE_NAME,
   DESCRIPTION,
   ACCUMULATE_AVAILABLE_FLAG,
   BACKWARD_CONSUMPTION_FLAG,
   FORWARD_CONSUMPTION_FLAG,
   PAST_DUE_DEMAND_CUTOFF_FENCE,
   PAST_DUE_SUPPLY_CUTOFF_FENCE,
   INFINITE_SUPPLY_FENCE_CODE,
   INFINITE_SUPPLY_TIME_FENCE,
   ACCEPTABLE_EARLY_FENCE,
   ACCEPTABLE_LATE_FENCE,
   DEFAULT_ATP_SOURCES,
   DEMAND_CLASS_ATP_FLAG,
   INCLUDE_SALES_ORDERS,
   INCLUDE_DISCRETE_WIP_DEMAND,
   INCLUDE_REP_WIP_DEMAND,
   INCLUDE_NONSTD_WIP_DEMAND,
   INCLUDE_DISCRETE_MPS,
   INCLUDE_USER_DEFINED_DEMAND,
   INCLUDE_PURCHASE_ORDERS,
   INCLUDE_DISCRETE_WIP_RECEIPTS,
   INCLUDE_REP_WIP_RECEIPTS,
   INCLUDE_NONSTD_WIP_RECEIPTS,
   INCLUDE_INTERORG_TRANSFERS,
   INCLUDE_ONHAND_AVAILABLE,
   INCLUDE_USER_DEFINED_SUPPLY,
   ACCUMULATION_WINDOW,
   INCLUDE_REP_MPS,
   INCLUDE_INTERNAL_REQS,
   INCLUDE_SUPPLIER_REQS,
   INCLUDE_INTERNAL_ORDERS,
   INCLUDE_FLOW_SCHEDULE_DEMAND,
   INCLUDE_FLOW_SCHEDULE_RECEIPTS,
   USER_ATP_SUPPLY_TABLE_NAME,
   USER_ATP_DEMAND_TABLE_NAME,
   MPS_DESIGNATOR
)
AS
   SELECT RULE_ID,
          RULE_NAME,
          DESCRIPTION,
          ACCUMULATE_AVAILABLE_FLAG,
          BACKWARD_CONSUMPTION_FLAG,
          FORWARD_CONSUMPTION_FLAG,
          PAST_DUE_DEMAND_CUTOFF_FENCE,
          PAST_DUE_SUPPLY_CUTOFF_FENCE,
          INFINITE_SUPPLY_FENCE_CODE,
          INFINITE_SUPPLY_TIME_FENCE,
          ACCEPTABLE_EARLY_FENCE,
          ACCEPTABLE_LATE_FENCE,
          DEFAULT_ATP_SOURCES,
          DEMAND_CLASS_ATP_FLAG,
          INCLUDE_SALES_ORDERS,
          INCLUDE_DISCRETE_WIP_DEMAND,
          INCLUDE_REP_WIP_DEMAND,
          INCLUDE_NONSTD_WIP_DEMAND,
          INCLUDE_DISCRETE_MPS,
          INCLUDE_USER_DEFINED_DEMAND,
          INCLUDE_PURCHASE_ORDERS,
          INCLUDE_DISCRETE_WIP_RECEIPTS,
          INCLUDE_REP_WIP_RECEIPTS,
          INCLUDE_NONSTD_WIP_RECEIPTS,
          INCLUDE_INTERORG_TRANSFERS,
          INCLUDE_ONHAND_AVAILABLE,
          INCLUDE_USER_DEFINED_SUPPLY,
          ACCUMULATION_WINDOW,
          INCLUDE_REP_MPS,
          INCLUDE_INTERNAL_REQS,
          INCLUDE_VENDOR_REQS INCLUDE_SUPPLIER_REQS,
          INCLUDE_INTERNAL_ORDERS,
          INCLUDE_FLOW_SCHEDULE_DEMAND,
          INCLUDE_FLOW_SCHEDULE_RECEIPTS,
          USER_ATP_SUPPLY_TABLE_NAME,
          USER_ATP_DEMAND_TABLE_NAME,
          MPS_DESIGNATOR
     FROM MTL_ATP_RULES;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AP_ASSIGNMENT_SETS_V

DROP VIEW APPS.MRP_AP_ASSIGNMENT_SETS_V;

/* Formatted on 2015/2/1 下午 05:02:21 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AP_ASSIGNMENT_SETS_V
(
   ASSIGNMENT_SET_ID,
   ASSIGNMENT_SET_NAME,
   DESCRIPTION,
   RN1
)
AS
   SELECT mas.Assignment_Set_ID,
          mas.Assignment_Set_Name,
          mas.Description,
          0 RN1
     FROM MRP_ASSIGNMENT_SETS mas;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AP_APPS_INSTANCES

DROP VIEW APPS.MRP_AP_APPS_INSTANCES;

/* Formatted on 2015/2/1 下午 05:01:41 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AP_APPS_INSTANCES
(
   INSTANCE_ID,
   LRN,
   LRD,
   INSTANCE_CODE,
   M2A_DBLINK,
   A2M_DBLINK,
   SN_STATUS,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_LOGIN,
   BOM_HOUR_UOM_CODE,
   MRP_MPS_CONSUMPTION,
   MRP_SHIP_ARRIVE_FLAG,
   CRP_SPREAD_LOAD,
   MSO_ITEM_DMD_PENALTY,
   MSO_ITEM_CAP_PENALTY,
   MSO_ORG_DMD_PENALTY,
   MSO_ORG_ITEM_PENALTY,
   MSO_ORG_RES_PENALTY,
   MSO_ORG_TRSP_PENALTY,
   MSC_AGGREG_RES_NAME,
   MSO_RES_PENALTY,
   MSO_SUP_CAP_PENALTY,
   MSC_BOM_SUBST_PRIORITY,
   MSO_TRSP_PENALTY,
   MSC_ALT_BOM_COST,
   MSC_FCST_PENALTY,
   MSO_SO_PENALTY,
   MSC_ALT_OP_RES,
   MSC_ALT_RES_PRIORITY,
   MSC_SIMUL_RES_SEQ,
   MRP_BIS_AV_DISCOUNT,
   MRP_BIS_PRICE_LIST,
   MSC_DMD_PRIORITY_FLEX_NUM,
   MSC_BATCHABLE_FLAG,
   MSC_BATCHING_WINDOW,
   MSC_MIN_CAPACITY,
   MSC_MAX_CAPACITY,
   MSC_UNIT_OF_MEASURE,
   MSC_FCST_PRIORITY_FLEX_NUM,
   ALLOW_ATP_FLAG,
   ALLOW_RELEASE_FLAG,
   MSC_SO_OFFSET_DAYS,
   ITEM_NAME_FROM_KFV
)
AS
   SELECT instance_id,
          lrn,
          lrd,
          instance_code,
          m2a_dblink,
          a2m_dblink,
          sn_status,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          last_update_login,
          bom_hour_uom_code,
          mrp_mps_consumption,
          mrp_ship_arrive_flag,
          crp_spread_load,
          mso_item_dmd_penalty,
          mso_item_cap_penalty,
          mso_org_dmd_penalty,
          mso_org_item_penalty,
          mso_org_res_penalty,
          mso_org_trsp_penalty,
          msc_aggreg_res_name,
          mso_res_penalty,
          mso_sup_cap_penalty,
          msc_bom_subst_priority,
          mso_trsp_penalty,
          msc_alt_bom_cost,
          mso_fcst_penalty,
          mso_so_penalty,
          msc_alt_op_res,
          msc_alt_res_priority,
          msc_simul_res_seq,
          mrp_bis_av_discount,
          mrp_bis_price_list,
          msc_dmd_priority_flex_num,
          msc_batchable_flag,
          msc_batching_window,
          msc_min_capacity,
          msc_max_capacity,
          msc_unit_of_measure,
          msc_fcst_priority_flex_num,
          allow_atp_flag,
          allow_release_flag,
          msc_so_offset_days,
          item_name_from_kfv
     FROM mrp_ap_apps_instances_all
    WHERE allow_atp_flag = 1;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_WIP_JOB_SUPPLIES_V

DROP VIEW APPS.MRP_AD_WIP_JOB_SUPPLIES_V;

/* Formatted on 2015/2/1 下午 05:01:08 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_WIP_JOB_SUPPLIES_V
(
   WIP_ENTITY_ID,
   WIP_JOB_TYPE,
   ORDER_TYPE,
   WJS_NET_QTY_FLAG,
   WJS_MPS_NET_QTY_FLAG,
   RN
)
AS
   SELECT WIP_ENTITY_ID * 2 wip_entity_id,
          JOB_TYPE WIP_JOB_TYPE,
          DECODE (JOB_TYPE, 1, 3, 7) ORDER_TYPE,
          WJS_NET_QTY_FLAG,
          WJS_MPS_NET_QTY_FLAG,
          RN
     FROM MRP_AD_DSCR_JOBS
   UNION ALL
   SELECT WIP_ENTITY_ID * 2 wip_entity_id,
          4 WIP_JOB_TYPE,
          27 ORDER_TYPE,
          WJS_NET_QTY_FLAG,
          TO_NUMBER (NULL) WJS_MPS_NET_QTY_FLAG,
          RN
     FROM MRP_AD_FLOW_SCHDS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_WIP_FLOW_DEMANDS_V

DROP VIEW APPS.MRP_AD_WIP_FLOW_DEMANDS_V;

/* Formatted on 2015/2/1 下午 05:00:35 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_WIP_FLOW_DEMANDS_V
(
   WIP_ENTITY_ID,
   ORIGINATION_TYPE,
   RN
)
AS
   SELECT wfs.WIP_ENTITY_ID, 25 ORIGINATION_TYPE, wfs.RN
     FROM MRP_AD_FLOW_SCHDS wfs
   UNION ALL
   SELECT wfd.WIP_ENTITY_ID, 25, wfd.RN
     FROM MRP_FLOW_DEMANDS wfd;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_USER_SUPPLIES_V

DROP VIEW APPS.MRP_AD_USER_SUPPLIES_V;

/* Formatted on 2015/2/1 下午 05:00:11 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_USER_SUPPLIES_V
(
   TRANSACTION_ID,
   ORDER_TYPE,
   RN
)
AS
   SELECT ud.TRANSACTION_ID, 41 ORDER_TYPE, ud.RN
     FROM MRP_AD_U_SUPPLY ud;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_USER_DEMANDS_V

DROP VIEW APPS.MRP_AD_USER_DEMANDS_V;

/* Formatted on 2015/2/1 下午 04:59:45 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_USER_DEMANDS_V
(
   TRANSACTION_ID,
   ORIGINATION_TYPE,
   RN
)
AS
   SELECT ud.TRANSACTION_ID, 42 ORIGINATION_TYPE, ud.RN
     FROM MRP_AD_U_DEMAND ud;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_SUB_OPER_RESS_V

DROP VIEW APPS.MRP_AD_SUB_OPER_RESS_V;

/* Formatted on 2015/2/1 下午 04:59:11 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_SUB_OPER_RESS_V
(
   ROUTING_SEQUENCE_ID,
   OPERATION_SEQUENCE_ID,
   RESOURCE_SEQ_NUM,
   RESOURCE_ID,
   ALTERNATE_NUMBER,
   RN
)
AS
   SELECT Routing_Sequence_ID * 2 Routing_Sequence_ID,
          Operation_Sequence_ID * 2 Operation_Sequence_ID,
          Resource_Seq_Num,
          Resource_ID * 2 Resource_ID,
          1 Alternate_Number,
          RN
     FROM MRP_AD_SUB_OPR_RESS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_SALES_ORDERS_V

DROP VIEW APPS.MRP_AD_SALES_ORDERS_V;

/* Formatted on 2015/2/1 下午 04:58:40 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_SALES_ORDERS_V
(
   DEMAND_ID,
   PARENT_DEMAND_ID,
   RN
)
AS
   SELECT md.Line_ID Demand_ID, 0 Parent_Demand_ID, md.RN
     FROM MRP_AD_ORDER_LINES_ALL md;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_ROUTINGS_V

DROP VIEW APPS.MRP_AD_ROUTINGS_V;

/* Formatted on 2015/2/1 下午 04:54:22 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_ROUTINGS_V
(
   ROUTING_SEQUENCE_ID,
   RN
)
AS
   SELECT ROUTING_SEQUENCE_ID * 2 ROUTING_SEQUENCE_ID, RN
     FROM MRP_AD_OPR_RTNS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_ROUTING_OPERATIONS_V

DROP VIEW APPS.MRP_AD_ROUTING_OPERATIONS_V;

/* Formatted on 2015/2/1 下午 04:53:54 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_ROUTING_OPERATIONS_V
(
   ROUTING_SEQUENCE_ID,
   OPERATION_SEQUENCE_ID,
   RN
)
AS
   SELECT ROUTING_SEQUENCE_ID * 2 ROUTING_SEQUENCE_ID,
          OPERATION_SEQUENCE_ID * 2 OPERATION_SEQUENCE_ID,
          RN
     FROM MRP_AD_OPR_SEQS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_RESOURCE_REQUIREMENTS_V

DROP VIEW APPS.MRP_AD_RESOURCE_REQUIREMENTS_V;

/* Formatted on 2015/2/1 下午 04:53:18 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_RESOURCE_REQUIREMENTS_V
(
   WIP_ENTITY_ID,
   WIP_JOB_TYPE,
   OPERATION_SEQ_NUM,
   WJS_NET_QTY_FLAG,
   WJS_MPS_NET_QTY_FLAG,
   RN
)
AS
   SELECT WIP_ENTITY_ID * 2 wip_entity_id,
          JOB_TYPE WIP_JOB_TYPE,
          Operation_Seq_Num,
          WJS_NET_QTY_FLAG,
          WJS_MPS_NET_QTY_FLAG,
          RN
     FROM MRP_AD_RESOURCE_REQUIREMENTS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_RESOURCE_CHANGES_V

DROP VIEW APPS.MRP_AD_RESOURCE_CHANGES_V;

/* Formatted on 2015/2/1 下午 04:52:45 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_RESOURCE_CHANGES_V
(
   DEPARTMENT_ID,
   RESOURCE_ID,
   SHIFT_NUM,
   SIMULATION_SET,
   ACTION_TYPE,
   FROM_DATE,
   TO_DATE,
   FROM_TIME,
   TO_TIME,
   RN
)
AS
   SELECT rc.DEPARTMENT_ID * 2 DEPARTMENT_ID,
          rc.RESOURCE_ID * 2 RESOURCE_ID,
          rc.SHIFT_NUM,
          rc.SIMULATION_SET,
          rc.ACTION_TYPE,
          rc.FROM_DATE,
          rc.TO_DATE,
          rc.FROM_TIME,
          rc.TO_TIME,
          rc.RN
     FROM MRP_AD_RES_CHNGS rc;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_REPT_ITEM_SUPPLIES_V

DROP VIEW APPS.MRP_AD_REPT_ITEM_SUPPLIES_V;

/* Formatted on 2015/2/1 下午 04:52:06 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_REPT_ITEM_SUPPLIES_V
(
   REPETITIVE_SCHEDULE_ID,
   ORDER_TYPE,
   RN
)
AS
   SELECT REPETITIVE_SCHEDULE_ID, 16 ORDER_TYPE, RN
     FROM MRP_AD_REPT_ITEM_SUPPLIES;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_REPT_ITEM_DEMANDS_V

DROP VIEW APPS.MRP_AD_REPT_ITEM_DEMANDS_V;

/* Formatted on 2015/2/1 下午 04:51:00 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_REPT_ITEM_DEMANDS_V
(
   WIP_ENTITY_ID,
   REPETITIVE_SCHEDULE_ID,
   INVENTORY_ITEM_ID,
   OPERATION_SEQ_NUM,
   ORIGINATION_TYPE,
   RN
)
AS
   SELECT WIP_ENTITY_ID * 2 wip_entity_id,
          REPETITIVE_SCHEDULE_ID,
          INVENTORY_ITEM_ID,
          OPERATION_SEQ_NUM,
          4 Origination_Type,
          RN
     FROM MRP_AD_REPT_ITEM_DEMANDS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_PROCESS_EFFECTIVITY_V

DROP VIEW APPS.MRP_AD_PROCESS_EFFECTIVITY_V;

/* Formatted on 2015/2/1 下午 04:50:34 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_PROCESS_EFFECTIVITY_V
(
   INVENTORY_ITEM_ID,
   ORGANIZATION_ID,
   BILL_SEQUENCE_ID,
   ROUTING_SEQUENCE_ID,
   LINE_ID,
   RN
)
AS
   SELECT INVENTORY_ITEM_ID,
          ORGANIZATION_ID,
          BILL_SEQUENCE_ID * 2 BILL_SEQUENCE_ID,
          ROUTING_SEQUENCE_ID * 2 ROUTING_SEQUENCE_ID,
          LINE_ID,
          RN
     FROM MRP_AD_PROCESS_EFFECTIVITY;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_PO_SUPPLIES_V

DROP VIEW APPS.MRP_AD_PO_SUPPLIES_V;

/* Formatted on 2015/2/1 下午 04:50:03 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_PO_SUPPLIES_V
(
   TRANSACTION_ID,
   RN
)
AS
   SELECT TRANSACTION_ID, RN FROM MRP_AD_SUPPLY;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_OPERATION_RESOURCES_V

DROP VIEW APPS.MRP_AD_OPERATION_RESOURCES_V;

/* Formatted on 2015/2/1 下午 04:49:18 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_OPERATION_RESOURCES_V
(
   ROUTING_SEQUENCE_ID,
   OPERATION_SEQUENCE_ID,
   RESOURCE_SEQ_NUM,
   RESOURCE_ID,
   ALTERNATE_NUMBER,
   RN
)
AS
   SELECT Routing_Sequence_ID * 2 Routing_Sequence_ID,
          Operation_Sequence_ID * 2 Operation_Sequence_ID,
          Resource_Seq_Num,
          Resource_ID * 2 Resource_ID,
          1 Alternate_Number,
          RN
     FROM MRP_AD_OPR_RESS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_OPERATION_COMPONENTS_V

DROP VIEW APPS.MRP_AD_OPERATION_COMPONENTS_V;

/* Formatted on 2015/2/1 下午 04:48:46 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_OPERATION_COMPONENTS_V
(
   BILL_SEQUENCE_ID,
   ROUTING_SEQUENCE_ID,
   COMPONENT_SEQUENCE_ID,
   OPERATION_SEQUENCE_ID,
   RN
)
AS
   SELECT BILL_SEQUENCE_ID * 2 BILL_SEQUENCE_ID,
          ROUTING_SEQUENCE_ID * 2 ROUTING_SEQUENCE_ID,
          COMPONENT_SEQUENCE_ID * 2 COMPONENT_SEQUENCE_ID,
          OPERATION_SEQUENCE_ID * 2 OPERATION_SEQUENCE_ID,
          RN
     FROM MRP_AD_OPERATION_COMPONENTS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_OP_RESOURCE_SEQS_V

DROP VIEW APPS.MRP_AD_OP_RESOURCE_SEQS_V;

/* Formatted on 2015/2/1 下午 04:48:13 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_OP_RESOURCE_SEQS_V
(
   ROUTING_SEQUENCE_ID,
   OPERATION_SEQUENCE_ID,
   RESOURCE_SEQ_NUM,
   RN
)
AS
   SELECT Routing_Sequence_ID * 2 Routing_Sequence_ID,
          Operation_Sequence_ID * 2 Operation_Sequence_ID,
          Resource_Seq_Num,
          RN
     FROM MRP_AD_OPR_RESS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_ONHAND_SUPPLIES_V

DROP VIEW APPS.MRP_AD_ONHAND_SUPPLIES_V;

/* Formatted on 2015/2/1 下午 04:47:38 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_ONHAND_SUPPLIES_V
(
   ORGANIZATION_ID,
   INVENTORY_ITEM_ID,
   SUBINVENTORY_CODE,
   LOT_NUMBER,
   RN
)
AS
   SELECT Organization_ID,
          Inventory_Item_ID,
          Subinventory_Code,
          Lot_Number,
          RN
     FROM mrp_ad_oh_qtys
   UNION ALL
   SELECT Organization_ID,
          Inventory_Item_ID,
          Subinventory_Code,
          TO_CHAR (NULL) lot_number,
          RN
     FROM mrp_ad_mtrx_tmp;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_MPS_SUPPLIES_V

DROP VIEW APPS.MRP_AD_MPS_SUPPLIES_V;

/* Formatted on 2015/2/1 下午 04:46:57 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_MPS_SUPPLIES_V
(
   DISPOSITION_ID,
   SCHEDULE_ORIGINATION_TYPE,
   INVENTORY_ITEM_ID,
   ORGANIZATION_ID,
   ORDER_TYPE,
   RN
)
AS
   SELECT sd.MPS_TRANSACTION_ID DISPOSITION_ID,
          sd.SCHEDULE_ORIGINATION_TYPE,
          sd.INVENTORY_ITEM_ID,
          sd.ORGANIZATION_ID,
          5 ORDER_TYPE,
          sd.RN
     FROM MRP_AD_SCHD_DATES sd
    WHERE sd.SUPPLY_DEMAND_TYPE = 2;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_MDS_DEMANDS_V

DROP VIEW APPS.MRP_AD_MDS_DEMANDS_V;

/* Formatted on 2015/2/1 下午 04:46:19 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_MDS_DEMANDS_V
(
   DISPOSITION_ID,
   INVENTORY_ITEM_ID,
   ORGANIZATION_ID,
   USING_ASSEMBLY_ID,
   ORIGINATION_TYPE,
   RN
)
AS
   SELECT sd.MPS_TRANSACTION_ID DISPOSITION_ID,
          sd.INVENTORY_ITEM_ID,
          sd.ORGANIZATION_ID,
          sd.INVENTORY_ITEM_ID USING_ASSEMBLY_ID,
          DECODE (sd.SCHEDULE_ORIGINATION_TYPE,
                  1, 8,
                  3, 6,
                  2, 7,
                  8, 7,
                  4, 15,
                  11, 24,
                  7, 8,
                  NULL)
             Origination_Type,
          sd.RN
     FROM MRP_AD_SCHD_DATES sd
    WHERE sd.SUPPLY_DEMAND_TYPE = 1;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_HARD_RESERVATIONS_V

DROP VIEW APPS.MRP_AD_HARD_RESERVATIONS_V;

/* Formatted on 2015/2/1 下午 04:45:39 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_HARD_RESERVATIONS_V
(
   DEMAND_ID,
   PARENT_DEMAND_ID,
   RN
)
AS
   SELECT md.Demand_ID, 0 Parent_Demand_ID, md.RN
     FROM MRP_AD_DEMAND md;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_BOMS_V

DROP VIEW APPS.MRP_AD_BOMS_V;

/* Formatted on 2015/2/1 下午 04:44:55 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_BOMS_V
(
   BILL_SEQUENCE_ID,
   RN
)
AS
   SELECT BILL_SEQUENCE_ID * 2 BILL_SEQUENCE_ID, RN FROM MRP_AD_BOMS;

iDempiere 抓取 Oracle ERP View資料 APPS.MRP_AD_BOM_COMPONENTS_V

DROP VIEW APPS.MRP_AD_BOM_COMPONENTS_V;

/* Formatted on 2015/2/1 下午 04:41:22 (QP5 v5.256.13226.35538) */
CREATE OR REPLACE FORCE VIEW APPS.MRP_AD_BOM_COMPONENTS_V
(
   BILL_SEQUENCE_ID,
   COMPONENT_SEQUENCE_ID,
   RN
)
AS
   SELECT BILL_SEQUENCE_ID * 2 BILL_SEQUENCE_ID,
          COMPONENT_SEQUENCE_ID * 2 COMPONENT_SEQUENCE_ID,
          RN
     FROM MRP_AD_INV_COMPS;

訂閱：文章 (Atom)

2015年2月10日 星期二

2015年2月1日 星期日

2015年2月10日星期二

2015年2月1日星期日