ITPub博客

首页 > 数据库 > Oracle > 详解EBS接口开发之WIP模块接口

详解EBS接口开发之WIP模块接口

原创 Oracle 作者:box 时间:2016-01-04 13:11:05 0 删除 编辑

详解EBS接口开发之WIP模块接口


本文档针对WIP模块业务功能和接口进行分析和研究,对采用并发请求方式和调用API方式分别进行介绍

WIP模块常用标准表简介

WIP事物处理组成

WIP相关业务流程

WIP相关API研究事例

(十)参考文档(七)采购相关的一些知识

WIP模块常用标准表简介

1.1   常用标准表

如下表中列出了与WIP导入相关的表和说明:

表名

说明

其他信息

BOM_STRUCTURES_B

BOM头信息

BOM_COMPONENTS_B

BOM 组件信息

BOM_OPERATIONAL_ROUTINGS

BOM 工艺路线头信息

BOM_OPERATION_SEQUENCES

BOM 工艺路线生产信息

WIP_ENTITIES

工单信息表

WIP_DISCRETE_JOBS

离散工单信息表

WIP_REQUIREMENT_OPERATIONS

任务领料需求发放表

WIP_OPERATIONS

离散作业工序(操作)表

WIP_OPERATION_RESOURCES

生产资源表

MTL_MATERIAL_TRANSACTIONS

事物处理表

WIP_TRANSACTIONS

WIP事物处理表

1.2  接口表大体介绍

如下表中列出了与WIP导入相关的接口表和说明:

表名

说明

其他信息

mtl_transactions_interface

事物处理接口表

mtl_serial_numbers_interface

事物处理序列表

mtl_transaction_lots_interface

事物处理批次表

cst_comp_snap_interface

wip_move_txn_interface

移动事物处理接口表

1.3   说明

这里只列出了WIP相关的大多数常用表。还有一些不经常用到的没有涉及,具体可以参考oracle网站上的

WIP事物处理组成

WIP事物处理主要包括:工单生成,工单更改,投料退料,工单移动,工单完工

2.1  WIP事物处理组成

工单创建:生成工单

工单更改:更改工单信息

投料退料:工单的投料以及退料

工单移动:工单的正向移动和逆向移动

投料退料:工单的完工事物处理

WIP相关业务流程

3.1   创建BOM

路径:Bill Of Materials > Bills > Bills

后台数据

SELECT * FROM bom_structures_b l WHERE l.assembly_item_id = 24815 ;

SELECT *

                  FROM bom_components_b

                   WHERE bill_sequence_id IN

                        ( SELECT bill_sequence_id

                  FROM bom_structures_b

                  WHERE assembly_item_id = 24815 );

发料方式

Push 推式发料,必须手工通过界面做发料 需严格控制数量或者波动比较大的物料

Assembly Pull 拉式发料,装配件完工或报废时自动按标准消耗量触发 消耗比较稳定的物料

Operation Pull 拉式发料,工序移动至To Move时自动按标准消耗量触发 消耗比较稳定的物料

BOM_COMPONENTS_B.WIP_SUPPLY_TYPE存储发料方式

Value

Meaning

1

Push

2

Assembly Pull

3

Operation Pull

4

Bulk

5

Supplier

6

Phantom

7

Based on Bill

3.2   定义ROUTING

路径:Bill Of Materials > Routings> Routings

Routings(工艺路线)最终解决的问题是生产过程中加工顺序、资源和用量的标准化。Routing是产品/半成品的生产步骤图,定义了生产特定物料所要经历的工序、加工部门(工作中心)、提前期、耗用的资源及其额定数量。

Routing头信息存储在表BOM_OPERATIONAL_ROUTINGS中,Routing的Operations信息存储在BOM_OPERATION_SEQUENCES表中,两个表通过字段ROUTING_SEQUENCE_ID字段关联

SELECT * FROM BOM_OPERATIONAL_ROUTINGS WHEREASSEMBLY_ITEM_ID =  24815 ;     

SELECT *

                  FROM bom_operation_sequences

                   WHERE routing_sequence_id IN

                        ( SELECT routing_sequence_id

                    FROM bom_operational_routings

                  WHERE assembly_item_id = 24815 );

3.2   定义离散工单

路径:WIP > Discrete > DiscreteJobs

Discrete,离散式,一种制造方法,用于装配件的分组或成批制造。制定标准离散任务,输入核心内容:任务名称(工单号)、生产类型、装配件、工单类型、生产数量、开工时间或完工时间。

这里在Discrete Job里定义job,保存,并release这个job。

工序

组件

涉及到几张表WIP_ENTITIES,WIP_DISCRETE_JOBS,WIP_REQUIREMENT_OPERATIONS和,这几张表都是通过WIP_ENTITY_ID相互关联.

<>

WIP_ENTITIES stores information about jobs,repetitive assemblies, and flow schedules. Each row includes a unique entityname, the entity type, and the assembly being built. Oracle Work in Process uses this information to controlproduction activities and to ensure that entities with duplicate names are notcreated.

Key Fields:

WIP_ENTITY_ID:Job or schedule Identifier

ORGANIZATION_ID:Organization Identifier

WIP_ENTITY_NAME:WIP job or repetitiveassembly name or flow schedule reference code

ENTITY_TYPE:WIP entity type code

1

Discrete job

2

Repetitive assembly

3

Closed discrete job

4

Flow schedule

PRIMARY_ITEM_ID:Assembly Item Item

<>

WIP_DISCRETE_JOBS stores discrete jobinformation. Each row represents a discrete job, and contains information aboutthe assembly being built, the revision of the assembly, the job quantity, thestatus of the job, the material control method, accounting information, and jobschedule dates. Oracle Work in Process uses this information to controldiscrete production.

Key Fields:

WIP_ENTITY_ID:Job or schedule Identifier

ORGANIZATION_ID:Organization Identifier

PRIMARY_ITEM_ID:Assembly Item Item

STATUS_TYPE :Status of job 可以通过下边的SQL查得code的意义

SELECT lookup_code,meaning FROMFND_LOOKUP_VALUES where LANGUAGE = 'US' AND Upper(lookup_type) LIKE Upper('WIP_JOB_STATUS')

Value

Meaning

7

Cancelled

8

Pending Bill Load

9

Failed Bill Load

10

Pending Routing Load

11

Failed Routing Load

12

Closed

13

Pending - Mass Loaded

14

Pending Close

15

Failed Close

1

Unreleased

3

Released

4

Complete

5

Complete - No Charges

6

On Hold

JOB_TYPE :Type of discrete job

Value

Meaning

1

Standard

3

Non-standard

WIP_SUPPLY_TYPE :Method of material consumption within WIP

Value

Meaning

1

Push

2

Assembly Pull

3

Operation Pull

4

Bulk

5

Supplier

6

Phantom

7

Based on Bill

START_QUANTITY:Job start quantity

QUANTITY_COMPLETED:Current job quantity completed

COMMON_BOM_SEQUENCE_ID:-->BOM_COMPONENTS_B.BILL_SEQUENCE_ID

COMMON_ROUTING_SEQUENCE_ID:-->BOM_OPERATIONAL_ROUTINGS.ROUTING_SEQUENCE_ID

<>

WIP_REQUIREMENT_OPERATIONS storesinformation about the material requirements of jobs and schedules. Each rowrepresents a material requirement and contains information about the componentitem, its usage quantities, the using department, requirement date, and thematerial control method. Oracle Work in Process uses this information to trackthe material usage of jobs and schedules.

Key Fields:

WIP_ENTITY_ID:Job or schedule Identifier

ORGANIZATION_ID:Organization Identifier

INVENTORY_ITEM_ID:Component Item Id

COMPONENT_SEQUENCE_ID:-->BOM_COMPONENTS_B.COMPONENT_SEQUENCE_ID

WIP_SUPPLY_TYPE :Method of material consumption within WIP

Value

Meaning

1

Push

2

Assembly Pull

3

Operation Pull

4

Bulk

5

Supplier

6

Phantom

7

Based on Bill

REQUIRED_QUANTITY:Component quantityrequired

QUANTITY_ISSUED:Component quantity issued

SUPPLY_SUBINVENTORY:Subinventory used tosupply component to WIP

SUPPLY_LOCATOR_ID:Locator used to supplycomponent to WIP

SEGMENT1:Component Item segment

QUANTITY_ALLOCATED:Quantity allocated

QUANTITY_BACKORDERED:Quantity backordered

SELECT * FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'J2010113' ;

WIP_ENTITIES.PRIMARY_ITEM_ID= 24815 与 BOM 关联

     

SELECT *

               FROM wip_discrete_jobs

             WHERE wip_entity_id =

                  ( SELECT wip_entity_id

            FROM wip_entities

            WHERE wip_entity_name = 'J2010113' );

SELECT *

               FROM wip_requirement_operations

  WHERE wip_entity_id =

                  ( SELECT wip_entity_id

            FROM wip_entities

            WHERE wip_entity_name = 'J2010113' );

3.2   发料

接下来我们就要从库存中给工单发货,这里有两种方法发料到工单

方法1:WIP > Material Transactions > WIP Material Transactions

这种方式比较简单,直接指定库存并发料

方法2:WIP > Discrete > Component Pick Release > Component Pick Releas(Form) / (SRS)

当BOM的组件很多,使用方法一发料的话,一个个指定库存会很麻烦, Oracle EBS提供了Pick Release的方式来发料,这样用户只要指定合适的Picking Rule,那么系统会自动帮你挑库。

系统会产生一个Move Order(Move Order Type:Manufacturing Pick),这一步实际上非常类似于订单的Pick Release。

接下来去Transact Move Order界面,Transact这个Move Order就完成发料动作了。

注意使用方式一发料的话,后台是不会产生Move Order的,只有方式二才会有Move Order产生。

3.2   事物处理

这个时候可以去Material Transaction form里查看,组件已经从库存中扣减,Transaction Type=WIP Issue(Transaction_Type_id:35,WIPcomponent issue)

这个时候MTL_MATERIAL_TRANSACTIONS表中几个JOB相关的字段

MMT.Transaction_Type_id:35

MMT.TRANSACTION_ACTION_ID:1

MMT.TRANSACTION_SOURCE_TYPE_ID:5

MMT.TRANSACTION_SOURCE_ID-->WIP_DISCRETE_JOBS.WIP_ENTITY_ID

MMT.TRX_SOURCE_LINE_ID-->WIP_REQUIREMENT_OPERATIONS.OPERATION_SEQ_NUM MMT.SOURCE_LINE_ID -->MTL_TXN_REQUEST_LINES.LINE_ID

MMT.Move_Order_line_ID-->MTL_TXN_REQUEST_LINES.LINE_ID

3.2   退料

物料发到工单后,有可能因为各种原因要把料退回到仓库,可以使用WIP Return

路径:WIP > Material Transactions > WIPMaterial Transactions,Type选择 WIP Return

查看Material Transaction,就可以看到一条WIP Return的Transaction发生了,并且组件库存数量又恢复到发料前的数量。

Transaction_TYPE_ID:43(WIP Component Return)

ps:不管是上边所说的WIP Issue还是WIP Return,在WIP_TRANSACTIONS表中是没有数据产生的。

WIP_TRANSACTIONS stores information aboutWIP resource transactions.Each row represents a single resource transaction andincludes a uniquetransaction Identifier, a transaction date, the job orrepetitiveschedule charged, the WIP operation and resource charges, andthenumber of units of measure applied. Oracle Work in Process uses thisinformation to track resourcecharges and to calculate the values stored in WIP_TRANSACTION_ACCOUNTS.

WIP相关API研究事例

相关接口字段说明参见参考例子里的说明

3.1  工单创建

PROCEDUREnew_work_order(p_wip_entity_id NUMBER,


                         p_organization_idNUMBER,


                         x_error_status OUTVARCHAR2,


                         x_error_message OUTVARCHAR2) IS

   l_iface_rec wip.wip_job_schedule_interface%ROWTYPE; -- 工单任务接口表

        FROM cux_wip_discrete_jobs_temp cwdj

       WHERE cwdj.wip_entity_id =p_wip_entity_id

         AND cwdj.organization_id =p_organization_id;

BEGIN

   l_iface_rec.last_update_date := SYSDATE;

   l_iface_rec.last_updated_by := fnd_global.user_id;

   l_iface_rec.creation_date := SYSDATE;

   l_iface_rec.created_by := fnd_global.user_id;

   l_iface_rec.group_id := wip.wip_job_schedule_interface_s.nextval;

   /*================================================

   WIP_LOAD_TYPE MFG_LOOKUPS

   ------------ --------------

   1 Create Standard Job

   2 Create Repetitive Schedule

   3 Update Discrete Job

   4 Create Non–standard Job

   ================================================*/

   FOR rec_wdj IN c_wdj LOOP

      g_status_type := rec_wdj.status_type;

      -- 根据业务逻辑判断标准非标准工单

      IF rec_wdj.job_type = 1 THEN

         l_iface_rec.load_type := 1; --1 标准

         l_iface_rec.load_type := 4; -- 非标准

      l_iface_rec.allow_explosion := 'Y';

      l_iface_rec.process_phase := '2';

      l_iface_rec.process_status := '1';

      l_iface_rec.status_type := '3'/*rec_wdj.status_type*/

       ; -- 已发放

      l_iface_rec.job_name := rec_wdj.job_num;

      l_iface_rec.organization_id := rec_wdj.organization_id;

      l_iface_rec.class_code :=rec_wdj.class_code;

      l_iface_rec.primary_item_id :=rec_wdj.primary_item_id;

      l_iface_rec.start_quantity :=rec_wdj.plan_quantity;

      l_iface_rec.scheduling_method := '1';

      l_iface_rec.first_unit_start_date :=rec_wdj.scheduled_start_date;

      l_iface_rec.first_unit_completion_date :=rec_wdj.scheduled_completion_date;

      l_iface_rec.attribute_category :=rec_wdj.temp_attribute_category;

      l_iface_rec.attribute1 :=rec_wdj.temp_attribute1;

      l_iface_rec.attribute2 :=rec_wdj.temp_attribute2;

      l_iface_rec.attribute3 :=rec_wdj.temp_attribute3;

      l_iface_rec.attribute4 :=rec_wdj.temp_attribute4;

      l_iface_rec.attribute5 :=rec_wdj.temp_attribute5;

      l_iface_rec.attribute6 :=rec_wdj.temp_attribute6;

      l_iface_rec.attribute7 :=rec_wdj.temp_attribute7;

      l_iface_rec.attribute8 :=rec_wdj.temp_attribute8;

      l_iface_rec.attribute9 := rec_wdj.temp_attribute9;

      l_iface_rec.attribute10 :=rec_wdj.temp_attribute10;

      l_iface_rec.attribute11 :=rec_wdj.temp_attribute11;

      l_iface_rec.attribute12 :=rec_wdj.temp_attribute12;

      l_iface_rec.attribute13 := rec_wdj.temp_attribute13;

      l_iface_rec.attribute14 :=rec_wdj.temp_attribute14;

      l_iface_rec.attribute15 :=rec_wdj.temp_attribute15;

      l_iface_rec.source_code := 'wip test';

      l_iface_rec.source_line_id :=rec_wdj.job_id;

      INSERT INTO wip.wip_job_schedule_interfaceVALUES l_iface_rec;

   wip_massload_pub.massloadjobs(p_groupid => l_iface_rec.group_id, --Group ID


                                 p_validationlevel => 2, -- Validation Level


                                 p_commitflag=> 0, -- Commit 1 =Yes , 0 =' no '


                                 x_returnstatus=> x_error_status,


                                 x_errormsg=> x_error_message);

END;

2   工单更改

PROCEDUREchange_work_order_status(p_wip_entity_id NUMBER,


                                   p_organization_id NUMBER,


                                   p_status_type NUMBER,


                                   p_group_idNUMBER := wip.wip_job_schedule_interface_s.nextval,


                                   x_error_status OUT VARCHAR2,


                                   x_error_message OUT VARCHAR2) IS

   l_schedule_iface_rec wip.wip_job_schedule_interface%ROWTYPE; -- 工单任务接口表

   --l_group_id NUMBER;

   /*l_returnstatus VARCHAR2(40);

   l_errormsg VARCHAR2(2000);*/

BEGIN

        FROM wip_discrete_jobs wdj

       WHERE wdj.wip_entity_id =p_wip_entity_id

         AND wdj.organization_id =p_organization_id

         AND wdj.status_type = p_status_type;

      x_error_status := 'S';

      WHEN no_data_found THEN

   l_schedule_iface_rec.group_id := p_group_id;

   l_schedule_iface_rec.last_update_date := SYSDATE;

   l_schedule_iface_rec.last_updated_by := fnd_global.user_id;

   l_schedule_iface_rec.creation_date := SYSDATE;

   l_schedule_iface_rec.created_by := fnd_global.user_id;

   -- l_schedule_iface_rec.group_id := l_group_id;

   /*================================================

   WIP_LOAD_TYPE MFG_LOOKUPS

   ------------ --------------

   1 Create Standard Job

   2 Create Repetitive Schedule

   3 Update Discrete Job

   4 Create Non–standard Job

   ================================================*/

   l_schedule_iface_rec.load_type := 3; --Update standard or non-standardDiscrete Job

   l_schedule_iface_rec.process_phase := 2; --Validation

   l_schedule_iface_rec.process_status := 1; --Pending

   l_schedule_iface_rec.wip_entity_id := p_wip_entity_id;

   l_schedule_iface_rec.organization_id := p_organization_id;

   l_schedule_iface_rec.status_type := p_status_type; -- 目标状态

   INSERT INTO wip.wip_job_schedule_interface VALUES l_schedule_iface_rec;

   wip_massload_pub.massloadjobs(p_groupid => p_group_id, -- Group ID


                                 p_validationlevel => 2, -- Validation Level


                                 p_commitflag=> 0, -- Commit 1 =Yes , 0 ='No'


                                 x_returnstatus=> x_error_status,


                                  x_errormsg=> x_error_message);

END;

3.3  工单移动

工单创建完成后,若不进行移动事务处理,则无法进行完工事务处理。移动数量需要按照完工数量的多少进行移动。完工事务处理中有超量完工的概念,因此,移动事务处理也有超量移动。

需要一个function 获取 可移动数量

 

 

 

 

 

 

/*=============================================

   *  FUNCTION / PROCEDURE

   *      get_available_to_move_qty

   *  DESCRIPTION:

   *       获取可移动数量

   *  ARGUMENT:

         

   *  RETURN:

   *      

   *  HISTORY:

   *       1.00 2014-7-11 cxy

*=============================================*/

FUNCTIONget_available_to_move_qty(p_wip_entity_id   IN NUMBER,

                                      p_opr_seq_num     IN NUMBER,

                                      p_organization_id IN NUMBER,

                                      p_intraopr_step   IN NUMBER)

      l_available_to_move_qty NUMBER;

      CURSOR csr_wip_operations IS

         SELECT decode(p_intraopr_step,

                       1,

                       wo.quantity_in_queue,

                       2,

                       wo.quantity_running,

                       3,

                       wo.quantity_waiting_to_move,

                       4,

                       wo.quantity_rejected,

                       5,

                       wo.quantity_scrapped,

                       wo.quantity_completed)

           FROM wip_operations wo

          WHERE wo.wip_entity_id = p_wip_entity_id

            AND wo.operation_seq_num =p_opr_seq_num

            AND wo.organization_id =p_organization_id

            AND wo.repetitive_schedule_id ISNULL;

      OPEN csr_wip_operations;

      FETCH csr_wip_operations

         INTO l_available_to_move_qty;

      CLOSE csr_wip_operations;

      RETURN l_available_to_move_qty;

   END get_available_to_move_qty;

  --Call API Process WIP Move Transaction

   --Fixed:

   --TRANSACTION_ID/GROUP_ID/PROCESS_PHASE/PROCESS_STATUS not valid. Please re-enter

   --SELECT group_id

   --    FROM wip_move_txn_interface

   --   WHERE transaction_id = p_txn_id

   --     AND process_phase = WIP_CONSTANTS.MOVE_VAL

   --     AND process_status = WIP_CONSTANTS.RUNNING

   --     AND group_id IS NOT NULL;

   --Doc ID:  363753.1

   --

PROCEDUREprocess_wip_move_txn(p_transaction_id IN NUMBER,

                                  p_commit         IN VARCHAR2 := fnd_api.g_false,

                                  x_return_status  IN OUT VARCHAR2,

                                  x_error_message  IN OUT VARCHAR2)IS

      --PRAGMA AUTONOMOUS_TRANSACTION;

      l_groupid NUMBER;

      l_errbuf VARCHAR2(1000);

      l_retcode NUMBER;

      -- Call the procedure

      /***************************************************************************

      * This procedure will be used to do move,easy-return, easy-completion, and

      * scrap transaction for Discrete, OSFMjobs, and Repetitive Schedule. The

      * caller need to insert the record intoWIP_MOVE_TXN_INTERFACE before calling

      * this routine. Caller can generatewmti.transaction_id and wmti.group_id

      * from the sequence wip_transactions_s.Caller need to insert both group_id

      * and transaction_id before calling theprocedure below. These 2 columns

      * should have to same value. Callershould always insert wmti.process_phase =

      * 1(Validation) to make sure that thedata inserted is valid, and insert

      * wmti.process_status = 2(Running) toprevent Move Manager from picking up

      * p_txn_id             transaction_id inWIP_MOVE_TXN_INTERFACE

      * p_do_backflush       this parameter determine whether moveprocesor has to

      *                      backflush pull componentor not. Some customers use

      *                      third party software toinsert backflush components, so

      *                      they do not want moveprocessor to backflush them

      *                     again. The defaultvalue is null. If the callers do not

      *                      pass this parameter orpass fnd_api.g_true, we will

      *                      backflush pull component.Otherwise, we will not

      *                      backflush them. pass fnd_api.g_true orfnd_api.g_false.

      * p_commmit            commit the change to the databaseif succesfully

      *                      processing ? pass

      *                      fnd_api.g_true orfnd_api.g_false

      * x_returnStatus       There are 2 possible values

      *                     *fnd_api.g_ret_sts_success*

      *                      means the movetransaction succesfully processed

      *                     *fnd_api.g_ret_sts_unexp_error*

      *                     means anexception occurred

      *                      The size of this variableshould be VARCHAR2(1)

      * x_errorMsg           contains the error message onfailure. null on success.

      *                      The size of this variableshould be VARCHAR2(1000)

      *                      because there may beerrors in several columns.

      * 1. This procedure should be called ifcaller want to process one record at

      *   a time such as Online transaction.

      * 2. This procedure will returnfnd_api.g_ret_sts_unexp_error if this records

      *   failed. The caller can check the error message from x_errorMsg.

      * 3. The caller does not have to insertchild record for overmove/

      *   overcompletion. This API will take care everything. The caller does not

      *   have to call QA API either.

      ***************************************************************************/

      cux_conc_utl.log_msg(p_msg => '   Begin Call Wip_movproc_pub.processinterface()');

      l_groupid := p_transaction_id;

      /*SELECT group_id

       FROM wip_move_txn_interface

      WHERE transaction_id = p_transaction_id

        AND process_phase =wip_constants.move_val

        AND process_status =wip_constants.running

        AND group_id IS NOT NULL;*/

      --wip_move_validator.validate(p_group_id=> l_groupid, p_initmsglist => fnd_api.g_true);

      /*--Wip_Movproc_Priv.move_worker()

      wip_movproc_priv.move_worker(errbuf       => l_errbuf,

                                   retcode      => l_retcode,

                                   p_group_id   => l_groupid,

                                   p_proc_phase=> wip_constants.move_proc,

                                   p_time_out   => 30,

                                   p_seq_move   =>wip_constants.yes);*/

      -- This procedure should be called if caller want to do batch processing formultiple records in WMTI.

      /*wip_movproc_pub.processinterface(p_group_id     => l_groupid,

      p_do_backflush => fnd_api.g_false,

      p_commit       => fnd_api.g_false,

      x_returnstatus => x_return_status);*/

      -- This procedure should be called if caller want to process one record at atime.

      wip_movproc_pub.processinterface(p_txn_id       => p_transaction_id,

                                       p_do_backflush => fnd_api.g_false,

                                       p_commit       =>fnd_api.g_false,

                                       x_returnstatus => x_return_status,

                                       x_errormsg     =>x_error_message);

      IF x_return_status =fnd_api.g_ret_sts_success THEN

         cux_conc_utl.log_msg(p_msg =>'   The Transaction_ID : ' ||

                                       p_transaction_id ||

                                       'Succesfully Processed');

         cux_conc_utl.log_msg(p_msg =>'   The Transaction_ID : ' ||

                                       p_transaction_id ||

                                       ' WIP APIError : ' ||

                                       x_error_message);

         cux_conc_utl.out_msg(p_msg =>'   The Transaction_ID : ' ||

                                       p_transaction_id ||

                                       ' WIP API Error : '||

                                       x_error_message);

         --RAISE fnd_api.g_exc_error;

      cux_conc_utl.log_msg(p_msg => '   End Call Wip_movproc_pub.processinterface()');

   END process_wip_move_txn;

/*=============================================

   *  FUNCTION / PROCEDURE

   *      process_wip_move

   *  DESCRIPTION:

   *       工单移动 事物处理

   *  ARGUMENT:

         

   *  RETURN:

   *      

   *  HISTORY:

   *       1.00 2014-7-18 cxy

  *=============================================*/

FUNCTIONprocess_wip_move(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,

                             p_commit        IN VARCHAR2 DEFAULT fnd_api.g_false,

                             x_return_status OUT NOCOPYVARCHAR2,

                             x_msg_count     OUT NOCOPY NUMBER,

                             x_msg_data      OUT NOCOPY VARCHAR2,

                             p_parameter1    IN VARCHAR2) RETURN VARCHAR2 IS

      l_api_name       CONSTANT VARCHAR2(30) :='process_wip_move';

      l_savepoint_name CONSTANT VARCHAR2(30) :='sp_process_wip_move';

      l_return_status VARCHAR2(1);

      l_msg_data      VARCHAR2(2000);

      l_rec_move_txn  wip_move_txn_interface%ROWTYPE;

      l_group_id  NUMBER;

      l_txn_id    NUMBER;

      l_available NUMBER;

      l_trn_intermtl_transactions_interface%ROWTYPE;

      v_transaction_header_id NUMBER DEFAULTNULL;

      v_transaction_action_id NUMBER DEFAULTNULL;

      v_quantity_flag         NUMBER DEFAULT 1;

      v_return_status  VARCHAR2(3) DEFAULT NULL;

      v_return_message VARCHAR2(6000) DEFAULTNULL;

      v_on_hand_enough VARCHAR2(1) DEFAULT 'N';

      x_over_qty       NUMBER;

      l_wip_entity_id        NUMBER := 265057;

      l_organization_id      NUMBER := 353;

      l_transaction_quantity NUMBER := 3;

      -- v_lot_number              VARCHAR2(15) := 'zhw007';*\

      -- start activity to create savepoint,check compatibility

      -- and initialize message list, includedebug message hint to enter api

      x_return_status :=cux_api.start_activity(p_pkg_name      => g_pkg_name,

                                                p_api_name       => l_api_name,

                                                p_savepoint_name=> l_savepoint_name,

                                                p_init_msg_list  =>p_init_msg_list);

      IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN

         RAISE fnd_api.g_exc_unexpected_error;

      ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN

         RAISE fnd_api.g_exc_error;

      -- logging parameters

      IF l_debug = 'Y' THEN

         cux_log.debug('p_parameter1 : ' ||p_parameter1);

      SELECT wip_transactions_s.nextval INTOl_group_id FROM dual;

      l_txn_id       := NULL;

      l_rec_move_txn := NULL;

      SELECT wip_transactions_s.nextval INTOl_txn_id FROM dual;

      l_rec_move_txn.transaction_id :=l_txn_id;

      l_rec_move_txn.group_id       := l_group_id;

      l_rec_move_txn.process_phase  := 1;

      l_rec_move_txn.process_status := 2;--runing

      l_rec_move_txn.created_by        := fnd_global.user_id;

      l_rec_move_txn.creation_date     := SYSDATE;

      l_rec_move_txn.last_updated_by   := fnd_global.user_id;

      l_rec_move_txn.last_update_date  := SYSDATE;

      l_rec_move_txn.last_update_login :=fnd_global.login_id;

      l_rec_move_txn.wip_entity_id :=l_wip_entity_id;

      --l_rec_move_txn.wip_entity_name :=rec_grp.wo_no;

      l_rec_move_txn.organization_id      := l_organization_id;

      l_rec_move_txn.transaction_date     := SYSDATE;

      l_rec_move_txn.transaction_quantity := abs(l_transaction_quantity);-- 不管怎样,事务处理数量始终取正值

      l_rec_move_txn.transaction_uom      := ' 个 ';

      IF l_transaction_quantity > 0 THEN

         -- 移动数量大于 0 时,正向移动,由 排队 到 移动工序号由最小走到最大      

         l_rec_move_txn.transaction_type := 1;--1.normal move;2.combination move or completion/return transaction

         SELECT MIN(wo.operation_seq_num),MAX(wo.operation_seq_num)

           INTOl_rec_move_txn.fm_operation_seq_num,

                l_rec_move_txn.to_operation_seq_num

           FROM wip_operations wo

          WHERE wo.wip_entity_id =l_wip_entity_id

            AND wo.organization_id =l_organization_id;

         l_rec_move_txn.fm_intraoperation_step_type := 1; -- 排队      

         l_rec_move_txn.to_intraoperation_step_type := 3; -- 移动      

         l_available                                :=get_available_to_move_qty(p_wip_entity_id  => l_wip_entity_id,

                                                                                 p_opr_seq_num     => l_rec_move_txn.fm_operation_seq_num,

                                                                                 p_organization_id => l_organization_id,

                                                                                 p_intraopr_step   => 1);

         IF l_available <l_transaction_quantity then

            -- 如果可移动数量小于移动数量,则启用超量移动         

            l_rec_move_txn.overcompletion_transaction_qty := l_transaction_quantity-

                                                             l_available;

            l_rec_move_txn.overcompletion_primary_qty     := l_transaction_quantity -

                                                             l_available;

            x_over_qty :=l_transaction_quantity - l_available;

         END IF; --IF l_available <p_move_qty then

         -- 移动数量小于 0 时,反向移动,由 移动 到 排队 工序号由最大走到最小

         l_rec_move_txn.transaction_type := 1;--1.normal move;2.combination move or completion/return transaction

         SELECT MAX(wo.operation_seq_num),MIN(wo.operation_seq_num)

           INTO l_rec_move_txn.fm_operation_seq_num,

                l_rec_move_txn.to_operation_seq_num

           FROM wip_operations wo

          WHERE wo.wip_entity_id =l_wip_entity_id

            AND wo.organization_id =l_organization_id;

         l_rec_move_txn.fm_intraoperation_step_type:= 3; -- 移动   

         l_rec_move_txn.to_intraoperation_step_type := 1; -- 排队

      END IF; -- IF p_move_qty > 0 THEN

      l_rec_move_txn.last_updated_by_name :=fnd_global.user_name;

      l_rec_move_txn.created_by_name      := fnd_global.user_name;

      -- 以下两字段视业务逻辑取数   

      l_rec_move_txn.source_code    := 'CXY_MES_WIP_MOVE_TEST';

      l_rec_move_txn.source_line_id := 265057;

         INSERT INTO wip_move_txn_interfaceVALUES l_rec_move_txn;

         WHEN OTHERS THEN

            cux_conc_utl.log_msg('insert intowip_move_txn_interface error : ' ||

                                 SQLERRM);

            RAISE fnd_api.g_exc_error;

      wip_movproc_pub.processinterface(p_txn_id       => l_txn_id,

                                       p_commit       => 'F',

                                       x_returnstatus => l_return_status,

                                       x_errormsg     => l_msg_data);

      -- end activity, include debug messagehint to exit api

      x_return_status :=cux_api.end_activity(p_pkg_name  =>g_pkg_name,

                                              p_api_name  => l_api_name,

                                              p_commit    => p_commit,

                                              x_msg_count => x_msg_count,

                                              x_msg_data  => x_msg_data);

      RETURN l_return_status || l_msg_data;

      WHEN fnd_api.g_exc_error THEN

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       =>cux_api.g_exc_name_error,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       =>x_msg_data);

      WHEN fnd_api.g_exc_unexpected_error THEN

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name=> l_savepoint_name,

                                                      p_exc_name       =>cux_api.g_exc_name_unexp,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       =>x_msg_data);

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       =>cux_api.g_exc_name_others,

                                                      x_msg_count      => x_msg_count,

                                                      x_msg_data       =>x_msg_data);

   END process_wip_move;

4   工单投料退料

/*=============================================

   *  FUNCTION / PROCEDURE

   *      process_wip_issues_returns

   *  DESCRIPTION:

   *       工单投料 & 退料 事物处理

   *  ARGUMENT:

         

   *  RETURN:

   *      

   *  HISTORY:

   *       1.00 2014-7-18 cxy

   *=============================================*/

FUNCTIONprocess_wip_issues_returns(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,

                                       p_commit        IN VARCHAR2DEFAULT fnd_api.g_false,

                                       x_return_status OUT NOCOPY VARCHAR2,

                                       x_msg_count     OUT NOCOPY NUMBER,

                                       x_msg_data      OUT NOCOPYVARCHAR2,

                                       p_parameter1    IN VARCHAR2)

      RETURN VARCHAR2 IS

      l_api_name       CONSTANT VARCHAR2(30) :='process_wip_issues_returns';

      l_savepoint_name CONSTANT VARCHAR2(30) :='sp_process_wip_issues_returns';

      l_iface_recinv.mtl_transactions_interface%ROWTYPE;

      l_trn_serial    mtl_serial_numbers_interface%ROWTYPE;

      x_error_message VARCHAR2(2000);

      l_request_id    NUMBER;

      l_return_status VARCHAR2(2);

      x_error_status  VARCHAR(2);

      l_msg_count   NUMBER;

      l_msg_data    VARCHAR2(2000);

      l_trans_count VARCHAR2(20000);

      l_transaction_source_id NUMBER := 265056;--WIP_DISCRETE_JOBS.WIP_ENTITY_ID

      l_inventory_item_id     NUMBER := 325265; -- 投的料

      l_organization_id       NUMBER := 353;

      l_subinventory_code     VARCHAR2(30) := 'YC01';

      l_transaction_quantity  NUMBER := -4;

      -- start activity to create savepoint,check compatibility

      -- and initialize message list, includedebug message hint to enter api

      x_return_status :=cux_api.start_activity(p_pkg_name      => g_pkg_name,

                                                p_api_name       => l_api_name,

                                                p_savepoint_name => l_savepoint_name,

                                                p_init_msg_list  =>p_init_msg_list);

      IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN

         RAISE fnd_api.g_exc_unexpected_error;

      ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN

         RAISE fnd_api.g_exc_error;

      -- logging parameters

      IF l_debug = 'Y' THEN

         cux_log.debug('p_parameter1 : ' ||p_parameter1);

      --cux_debug.test_log('1', '1', 'beginprocess');

      SELECTmtl_material_transactions_s.nextval

        INTO l_iface_rec.transaction_interface_id

      /* l_trn_serial                          := NULL;

      l_trn_serial.transaction_interface_id :=l_iface_rec.transaction_interface_id;

      l_trn_serial.source_code              := v_source_code;

      l_trn_serial.source_line_id           := v_source_line_id;

      l_trn_serial.creation_date            := SYSDATE;

      l_trn_serial.created_by               := g_user_id;

      l_trn_serial.last_update_date         := SYSDATE;

      l_trn_serial.last_updated_by          := g_user_id;

      l_trn_serial.last_update_login        := g_login_id;

      l_trn_serial.program_application_id   := g_prog_appl_id;

      l_trn_serial.program_id               := g_conc_program_id;

      l_trn_serial.program_update_date      := SYSDATE;

      l_trn_serial.request_id               := g_request_id;

      l_trn_serial.fm_serial_number         := NULL; --redo

      l_trn_serial.to_serial_number         := NULL; -- redo

      l_trn_serial.process_flag             := 1;

         INSERT INTOmtl_serial_numbers_interface VALUES l_trn_serial;

         WHEN OTHERS THEN

            cux_conc_utl.log_msg('insert intomtl_serial_numbers_interface error : ' ||

                                 SQLERRM);

            RAISE fnd_api.g_exc_error;

      l_iface_rec.last_update_date      := SYSDATE;

      l_iface_rec.last_updated_by       := fnd_global.user_id;

      l_iface_rec.creation_date         := SYSDATE;

      l_iface_rec.created_by            := fnd_global.user_id;

      l_iface_rec.last_update_login     := fnd_global.user_id;

      l_iface_rec.transaction_header_id :=l_iface_rec.transaction_interface_id;

      /************transaction_mode*********************

      * transaction_mode

      *2 Concurrent, Process transactioninterface 不处理,

      * 需要程序调用 Inventory transaction worker 处理

      *3 Background, 由 Process transaction interface 处理

      ***************************************************/

      l_iface_rec.transaction_mode := 3;

      /************process_flag*********************

      ***************************************************/

      l_iface_rec.process_flag := 1;

      IF l_transaction_quantity >= 0 THEN

         l_iface_rec.transaction_type_id := 35;--mtl_transaction_types--WIP Issue-- 投料    

         l_iface_rec.transaction_type_id := 43;--wip return 退料      

      l_iface_rec.transaction_source_type_id :=5; --mtl_txn_source_types-- 生产管理

      /*************************************************

       * 账户别名杂项,用别名 ID 即 disposition_id

      * 账户杂项,用账户 ID 即 code_combination_id

      *WIP 物料事务,用任务 ID 即 wip_entity_id

      *SO 物料事务,用 mtl_sales_orders.sales_order_id

      * 子库转移或组织间转移,为空

      ************************************************/

      /*SELECT wdj.wip_entity_id

       INTO l_iface_rec.transaction_source_id

       FROM wip_discrete_jobs wdj, wip_entitieswe

      WHERE (wdj.source_line_id =rec_issue.job_id OR

            (wdj.wip_entity_id,wdj.organization_id) IN

            (SELECTcwdj.wip_entity_id, cwdj.organization_id

                FROM cux_wip_discrete_jobs_vcwdj

               WHERE cwdj.job_id =rec_issue.job_id

                 AND cwdj.order_wip_entity_idIS NULL))

        AND we.wip_entity_id = wdj.wip_entity_id

        AND we.organization_id =wdj.organization_id;*/

      l_iface_rec.transaction_source_id :=l_transaction_source_id;

      l_iface_rec.inventory_item_id     := l_inventory_item_id;

      l_iface_rec.organization_id       := l_organization_id;

      l_iface_rec.subinventory_code     := l_subinventory_code;

      l_iface_rec.locator_id            := NULL;

      l_iface_rec.transaction_quantity  := -l_transaction_quantity;

      l_iface_rec.transaction_uom       := ' 个 ';

      l_iface_rec.reason_id             := NULL;

      l_iface_rec.transaction_date      := SYSDATE;

      l_iface_rec.source_code           := 'CXY_MES_WIP_TEST_CODE';

      l_iface_rec.source_header_id      := 4;

      l_iface_rec.source_line_id        := 4;

      l_iface_rec.final_completion_flag := 'N';

      --cux_debug.test_log('1', '1', 'begininsert mtl');

         INSERT INTOinv.mtl_transactions_interface VALUES l_iface_rec;

         WHEN OTHERS THEN

            cux_conc_utl.log_msg('insert intomtl_transactions_interface error : ' ||

                                 SQLERRM);

            RAISE fnd_api.g_exc_error;

      --cux_debug.test_log('1', '1', 'endinsert mtl');

      l_request_id   := inv_txn_manager_pub.process_transactions(p_api_version      => 1,

                                                                 p_init_msg_list    =>fnd_api.g_false,

                                                                 p_commit           => fnd_api.g_true,

                                                                 p_validation_level => fnd_api.g_valid_level_full,

                                                                 x_return_status    =>l_return_status,

                                                                 x_msg_count        => l_msg_count,

                                                                 x_msg_data         =>l_msg_data,

                                                                 x_trans_count      =>l_trans_count,

                                                                 p_table            => 1,

                                                                 p_header_id        =>l_iface_rec.transaction_header_id);

      x_error_status := l_return_status;

         SELECT mti.error_code || ',' ||mti.error_explanation || ',' ||

                mli.error_code

           INTO x_error_message

           FROM mtl_transactions_interface     mti,

                mtl_transaction_lots_interfacemli

          WHERE mti.transaction_interface_id =

                l_iface_rec.transaction_interface_id

            AND mli.transaction_interface_id(+)=

                mti.transaction_interface_id;

         WHEN no_data_found THEN

      IF x_error_status != 'S' THEN

         /*DELETEmtl_transaction_lots_interface t

         WHERE t.source_code ='cux_wip_issue_txn'

           AND t.source_line_id =rec_issue.wip_issue_txn_id;*/

         /*DELETE mtl_transactions_interface t

         WHERE t.source_code ='CXY_MES_WIP_TEST_CODE'

           AND t.source_line_id = 4

           AND t.source_header_id = 4;*/

      RETURN x_error_status || x_error_message;

      -- end activity, include debug messagehint to exit api

      x_return_status :=cux_api.end_activity(p_pkg_name  =>g_pkg_name,

                                              p_api_name  => l_api_name,

                                              p_commit    => p_commit,

                                              x_msg_count => x_msg_count,

                                              x_msg_data  => x_msg_data);

      WHEN fnd_api.g_exc_error THEN

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       =>cux_api.g_exc_name_error,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       => x_msg_data);

      WHEN fnd_api.g_exc_unexpected_error THEN

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       =>cux_api.g_exc_name_unexp,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       =>x_msg_data);

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       => cux_api.g_exc_name_others,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       =>x_msg_data);

   END process_wip_issues_returns;

3.4   工单完工

/*=============================================

   *  FUNCTION / PROCEDURE

   *      process_wip_completions

   *  DESCRIPTION:

   *       工单完成 事物处理

   *  ARGUMENT:

         

   *  RETURN:

   *      

   *  HISTORY:

   *       1.00 2014-7-18 cxy

   *=============================================*/

FUNCTIONprocess_wip_completions(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,

                                    p_commit        IN VARCHAR2DEFAULT fnd_api.g_false,

                                    x_return_status OUT NOCOPYVARCHAR2,

                                    x_msg_count     OUT NOCOPY NUMBER,

                                    x_msg_data      OUT NOCOPYVARCHAR2,

                                    p_parameter1    IN VARCHAR2)

      RETURN VARCHAR2 IS

      l_api_name       CONSTANT VARCHAR2(30) :='process_wip_completions';

      l_savepoint_name CONSTANT VARCHAR2(30) :='sp_process_wip_completions';

      l_iface_rec  inv.mtl_transactions_interface%ROWTYPE;

      l_cst_rec    cst_comp_snap_interface%ROWTYPE;

      l_trn_serialmtl_serial_numbers_interface%ROWTYPE;

      l_return_status VARCHAR2(1);

      l_msg_count     NUMBER;

      l_msg_data      VARCHAR2(2000);

      l_trans_count   NUMBER;

      x_error_message VARCHAR2(2000);

      l_transaction_quantity  NUMBER := 3;

      l_transaction_source_id NUMBER := 265057;--wip_entity_id

      l_inventory_item_id     NUMBER := 325264; --CXY_ 制造件

      l_organization_id       NUMBER := 353;

      l_subinventory_code     VARCHAR2(30) := 'CP01';

      l_request_id            NUMBER;

      -- start activity to create savepoint,check compatibility

      -- and initialize message list, includedebug message hint to enter api

      x_return_status := cux_api.start_activity(p_pkg_name       => g_pkg_name,

                                                p_api_name       => l_api_name,

                                                p_savepoint_name => l_savepoint_name,

                                                p_init_msg_list  => p_init_msg_list);

      IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN

         RAISE fnd_api.g_exc_unexpected_error;

      ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN

         RAISE fnd_api.g_exc_error;

      -- logging parameters

      IF l_debug = 'Y' THEN

         cux_log.debug('p_parameter1 : ' ||p_parameter1);

      SELECTmtl_material_transactions_s.nextval

        INTO l_iface_rec.transaction_interface_id

      l_iface_rec.transaction_header_id :=l_iface_rec.transaction_interface_id;

      /* l_trn_serial                          := NULL;

      l_trn_serial.transaction_interface_id :=l_iface_rec.transaction_interface_id;

      l_trn_serial.source_code              := v_source_code;

      l_trn_serial.source_line_id           := v_source_line_id;

      l_trn_serial.creation_date            := SYSDATE;

      l_trn_serial.created_by               := g_user_id;

      l_trn_serial.last_update_date         := SYSDATE;

      l_trn_serial.last_updated_by          := g_user_id;

      l_trn_serial.last_update_login        := g_login_id;

      l_trn_serial.program_application_id   := g_prog_appl_id;

      l_trn_serial.program_id               := g_conc_program_id;

      l_trn_serial.program_update_date      := SYSDATE;

      l_trn_serial.request_id               := g_request_id;

      l_trn_serial.fm_serial_number         := NULL; --redo

      l_trn_serial.to_serial_number         := NULL; -- redo

      l_trn_serial.process_flag             := 1;

         INSERT INTOmtl_serial_numbers_interface VALUES l_trn_serial;

         WHEN OTHERS THEN

            cux_conc_utl.log_msg('insert intomtl_serial_numbers_interface error : ' ||

                                 SQLERRM);

            RAISE fnd_api.g_exc_error;

      l_iface_rec.last_update_date  := SYSDATE;

      l_iface_rec.last_updated_by   := fnd_global.user_id;

      l_iface_rec.creation_date     := SYSDATE;

      l_iface_rec.created_by        := fnd_global.user_id;

      l_iface_rec.last_update_login :=fnd_global.user_id;

      /************transaction_mode*********************

      * transaction_mode

      *2 Concurrent, Process transactioninterface 不处理,

      * 需要程序调用 Inventory transaction worker 处理

      *3 Background, 由 Process transaction interface 处理

      ***************************************************/

      l_iface_rec.transaction_mode := 3;

      /************process_flag*********************

      ***************************************************/

      l_iface_rec.process_flag := 1;

      IF l_transaction_quantity >= 0 THEN

         l_iface_rec.transaction_type_id := 44;--mtl_transaction_types--WIP Completion完工事务处理      

         l_iface_rec.transaction_type_id := 17;-- 如果数量为负数 17 WIP Completion Return 完工事务处理退回      

      l_iface_rec.transaction_source_type_id :=5; --mtl_txn_source_types-- 生产管理

      /*************************************************

      * 账户别名杂项,用别名 ID 即 disposition_id

      * 账户杂项,用账户 ID 即 code_combination_id

      *WIP 物料事务,用任务 ID 即 wip_entity_id

      *SO 物料事务,用 mtl_sales_orders.sales_order_id

      * 子库转移或组织间转移,为空

      ************************************************/

         SELECT wdj.wip_entity_id,we.primary_item_id

           INTOl_iface_rec.transaction_source_id,

                l_iface_rec.inventory_item_id

           FROM wip_discrete_jobs wdj, wip_entitieswe

          WHERE (wdj.source_line_id =rec_completion.job_id AND

                wdj.source_code = 'wipplatform')

            AND we.wip_entity_id =wdj.wip_entity_id

            AND we.organization_id =wdj.organization_id;

         WHEN no_data_found THEN

      l_iface_rec.transaction_source_id :=l_transaction_source_id;

      l_iface_rec.inventory_item_id     := l_inventory_item_id;

      IF l_transaction_quantity > 0 THEN

         -- 若数量大于零,需要先进行移动事务处理

         l_msg_data :=process_wip_move(p_init_msg_list => fnd_api.g_false,

                                        p_commit        =>fnd_api.g_false,

                                        x_return_status => l_return_status,

                                        x_msg_count     => l_msg_count,

                                        x_msg_data      => l_msg_data,

                                        p_parameter1    => NULL);

         IF l_return_status != 'S' THEN

            cux_debug.test_log('MOVE_COM' ||l_return_status,

                               l_msg_count,

                               l_msg_data);

         END IF; --IF x_error_status != 'S'THEN

      END IF; -- IF rec_completion.quantity> 0 THEN

      -- 移动成功 则 继续        

      l_iface_rec.organization_id       := l_organization_id;

      l_iface_rec.subinventory_code     := l_subinventory_code;

      l_iface_rec.locator_id            := NULL;

      l_iface_rec.transaction_quantity  := l_transaction_quantity;

      l_iface_rec.transaction_uom       := ' 个 ';

      l_iface_rec.transaction_date      := SYSDATE;

      l_iface_rec.final_completion_flag := 'N';

      l_iface_rec.wip_entity_type := 1;

      --l_iface_rec.transaction_header_id :=l_iface_rec.transaction_interface_id;

      -- 以下三字段,视业务逻辑不同取数

      l_iface_rec.source_code :='CXY_MES_WIP_COM_TEST1';

      l_iface_rec.source_header_id := 265057;

      l_iface_rec.source_line_id := 265057;

         INSERT INTOinv.mtl_transactions_interface VALUES l_iface_rec;

         WHEN OTHERS THEN

            cux_conc_utl.log_msg('insert intomtl_transactions_interface error : ' ||

                                 SQLERRM);

            RAISE fnd_api.g_exc_error;

      l_cst_rec.transaction_interface_id :=l_iface_rec.transaction_header_id;

      l_cst_rec.wip_entity_id            := 265057;

      l_cst_rec.operation_seq_num        := 10;

      l_cst_rec.last_update_date         := SYSDATE;

      l_cst_rec.last_updated_by          := fnd_global.user_id;

      l_cst_rec.creation_date            := SYSDATE;

      l_cst_rec.created_by               := fnd_global.user_id;

      l_cst_rec.quantity_completed       := l_transaction_quantity; --QuantityCompleted

      l_cst_rec.primary_quantity         := l_transaction_quantity; --PriamryQuantity

         INSERT INTO cst_comp_snap_interfaceVALUES l_cst_rec;

         WHEN OTHERS THEN

            cux_conc_utl.log_msg('insert intocst_comp_snap_interface error : ' ||

                                 SQLERRM);

            RAISE fnd_api.g_exc_error;

      l_request_id :=inv_txn_manager_pub.process_transactions(p_api_version      => 1,

                                                               p_init_msg_list    =>fnd_api.g_false,

                                                               p_commit           =>fnd_api.g_true,

                                                               p_validation_level=> fnd_api.g_valid_level_full,

                                                               x_return_status    =>l_return_status,

                                                               x_msg_count        => l_msg_count,

                                                               x_msg_data         =>l_msg_data,

                                                               x_trans_count      =>l_trans_count,

                                                               p_table            => 1,

                                                               p_header_id        =>l_iface_rec.transaction_header_id);

      x_return_status := l_return_status;

         SELECT mti.error_code || ',' ||mti.error_explanation || ',' ||

                mli.error_code

           INTO x_error_message

           FROM mtl_transactions_interface     mti,

                mtl_transaction_lots_interfacemli

          WHERE mti.transaction_interface_id =

                l_iface_rec.transaction_interface_id

            AND mli.transaction_interface_id(+)=

                mti.transaction_interface_id;

         WHEN no_data_found THEN

      IF x_return_status != 'S' THEN

         /*DELETE mtl_transactions_interface t

         WHERE t.source_code ='cux_wip_issue_txn'

           AND t.source_line_id =rec_issue.wip_issue_txn_id

           AND t.source_header_id =rec_issue.job_id;*/

       ENDIF; --IF x_error_status != 'S' THEN

      -- end activity, include debug messagehint to exit api

      x_return_status :=cux_api.end_activity(p_pkg_name  =>g_pkg_name,

                                              p_api_name  => l_api_name,

                                              p_commit    => p_commit,

                                              x_msg_count => x_msg_count,

                                              x_msg_data  => x_msg_data);

      RETURN l_return_status ||x_error_message;

      WHEN fnd_api.g_exc_error THEN

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       =>cux_api.g_exc_name_error,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       =>x_msg_data);

      WHEN fnd_api.g_exc_unexpected_error THEN

         x_return_status := cux_api.handle_exceptions(p_pkg_name       => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       => cux_api.g_exc_name_unexp,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       =>x_msg_data);

         x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                      p_api_name       => l_api_name,

                                                      p_savepoint_name => l_savepoint_name,

                                                      p_exc_name       =>cux_api.g_exc_name_others,

                                                      x_msg_count      =>x_msg_count,

                                                      x_msg_data       => x_msg_data);

   END process_wip_completions;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/121967/viewspace-1970667/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    77
  • 访问量
    186232