PL/SQL语法部分

一、    PL/SQL语言简介

(本讲义之所有程序均调式通过)

首先我们看一个简单之例子,下面这个例子是统计从1至100的总和.

declare

i number:=0;          /*声明变量井给初值*/

t number:=1;

error_message exception;  /*声明一个出错处理*/

begin

  for t in 1..100 loop

    i:=i+t;

  end loop;

  if i>=5050 then

    raise error_message;  /*引发错误处理*/

  else

    insert into c_nt(c_t) values(i);

  end if;

exception

  when error_message then

  insert into c_nt(c_t) values(0);

end;

²      从上例中可以看出PL/SQL语法的一般规则.

­              PL/SQL中语句以分号(;)结尾.

­              开始程序块的PL/SQL语句(如IF…或BEGIN语句)没有分句.

­              文本值括在单引号(‘ ‘)内,而不是(“ “).

­              过程只允许最后有一个出口..

²      PL/SQL程序可以分为三个部分

­             DECLARE部分用于变量、常量、函数、过程、Cursor.

­             BEGIN部分包含PL/SQL块中要执行的代码 用于程序处理,其中可以调用函数、过程.

­             Exception 部分用于出错处理.

下面我们再看一个例子:

declare

i number :=1;

t number :=1;

p number :=1;

/*create table c_ny(c_t number,cou_t number);*/

function aa(xx number)return number is     /* define function*/

   tt number;

   ct number:=1;

    j number:=1;

begin

   while j<=xx loop

   ct:=ct+j;

   j:=j+1;

   end loop;

   return ct;

end aa;

begin

  /*create table c_nt(c_t number,cou_t number);*/

  commit;

  while i<=200 loop

    t:=t+i;

    i:=i+1;

    p:=aa(i);      /* calling function*/

    insert into c_nt values(t,p);

    commit;

  end loop;

end;

/

 

 

 

 

 

说明:

1.在定义变量可以赋初值,赋初值有两种方法,一为上程序所示,另一种为如下所示:

Declare

I number default 92;

T number default 0;

 

2.定义常量

Declare

I constant number:=1;

T constant number:=9;

 

3.定义函数

function function_name(parameter type)return type is

  …declare variant

begin

  …

  …

end function_name;

在上面的例子中我们定义了一个函数aa,在begin模块部分引用了此函数aa().

 

4.定义过程

procedure procedure_name(parameter type) is

…declare variant

begin

…

…

exception

…

…

end procedure_name;

 

 

 

 

见下例:

declare

/*t_emp  c_nt%rowtype;*/

i number:=1;

t number:=1;

procedure te_t(t_t number) is          /*定义一个函数*/

  begin

    insert into c_nt1(t_1) values(t_t);

end te_t;

begin

 

调用函数

  for i in 1..100 loop

    te_t(i);

  end loop;

end;

/

 

5.定义Cursor

declare

/*t_emp  c_nt%rowtype;*/

t_emp1 number;

t_emp2 number;

cursor tes_t1

is select * from c_nt;

begin

  open tes_t1;

  delete from c_nt1;

  commit;

  loop

    fetch tes_t1 into t_emp1,t_emp2;

    exit when tes_t1%notfound;

    insert into c_nt1 values(t_emp1,t_emp2);a

  end loop;

  close tes_t1;

  commit;

end;

/

 

 

我们在open 一个cursor时,可能会存在一种情况,即我们不需要cursor中所有之记录,我们该如何处理:

1.在定义一个cursor时,可以附带参数如下所示

declae

  cursor c1(emp_id) is

select emp_no,emp_name from dept_no ;

demp_pt c1%rowtype;

…

…

begin

 loop

 fetch c1(123) into demp_pt

 …

 

2.在将cursor中之记录项转到变量中时进行控制,如下所示:

declare

  cursor is

  select  empt_no,empt_name from dept_no;

  p_no    number;

  p_name  number;

begin

  loop

  fetch c1 into (p_no,p_name);

用于到cursor中变量进行控制

  if condition1  then

   …

   …

  end if

  

end;

 

注意:

²      因为PL/SQL不支持I/O,所以程序所有结果都是放在数据档中.

²      Delete from accts where status=’bad debt’

If sql%rowcount>10 then

   Raise out_of_bounds;

End if;

 

另:

在声明一个变量时,PL/SQL提供两种变量类型:%TYPE,%ROWTYPE.

1.%TYPE

使用%TYPE时,可以有种用法:

²      一用法见下例:

declare

Balance                number(7,2);

Minimum_balance        balance%type:=10.00;

在上例中,minimum_balance数据类型为number(7,2)具默认值为10.00.

²      二用法见下例(将数据类型与table中一column datatype相对应起来,如果table中column datatype变更,则在运行时,上数据类型会自动的变换上):

declare

my_dname            empc.empto%type;

 

2.%rowtype

使用%rowtype数据类型用于将table和cursor中一数据行相对起来.

见下例:

 

 

 

 

 

 

²      Declare

定义一个%rowtype类型,与my_cursor中记录行对应.

Cursor my_cursor is select sal+nvl(comm,0),wages,ename from emp;

My_rec my_cursor%rowtype;

Begin

Open my_cursor;

将my_cursor中之记录写到my_rec变量中去.

loop

Fetch my_cursor into my_rec ;

Exit when my_cursor%notfound;

If my_rec.wages>200 then

Insert into temp values (null,my_rec.wages,my_rec.ename);

End if;

End loop;

Close my_cursor;

End;

 

 

 

 

 

 

二、    变量说明

在PL/SQL中包括以下几种常见的变量类型:

CHAR-存储定长的ASCII字符串,允许存储数字,文本文符等,最长可255个字符.

VARCHAR2-存储变长的字符串,尽管伋按符串的最大长度来定义,但VARCHAR2和CHAR的区别在于如果达不到定义的长度,下的空间不会自动的填写为空格,VARCHAR2最大可以放入2000个字符.

DATE-实际上是存储时间信息的日期/时间戳,在使用日期时,应考虑怎样使用日期函数.有关日期函数的格式见函数说明部分.

127

NUMBER-存储数值数据,包括整数和浮点数、数据范围可以从1Ï10   到38Ï10   ,而且,你有很大的数据空间.

BOOLENA-存储布尔值.它表示是/否,真/假,1/0之类的东西.

 

LONG-这是一种文本字符串,其长度大于VARCHAR2字段的2000个字符.该类最多可储存2 GB个字符,与原始二进制数据相比,它只能存储字符信息.

RAW-用来存储操作系统使用的原始二进制数据,可用于存储像图像或声音记录这样的信息,但这种数据长度最长度只有255字节.

LONG RAW-与LONG类型等价,但存储二进制数据,最长可达2GB个字节.

31

31

BINARY_INTEGER-这个字段按计算器使用的二进制格式存储信息,从

       -2  到2  -1.

 

 

 

 

 

 

 

另:

PL/SQL提位两种复合类型:TABLE和RECORD

1.TABLE

²      要定义一个数组,你使用表类型定义语句,例如要定义Last_name数组,可以使用下述语句:

type last_name_list is table of varchar2(22)

index by binary_integer;

last_name last_name_list;

 

²      当定义一个长类型时,就涉及到一个删除表的问题,PL/SQL表不能用Delete语句来删除,但可以将每一行空值如下所示:

sal_tab(3):=null;

另一种法是定义两个相同类型的表类型,如果要将另一表清空,只需将空表给要清空的表即可.如下所示.

declare

type numtabtype is table of number

index by binary_integer;

sal_tab numbertype;

empty_tab numbertype;

begin

for I in 1..100 loop

     sal_tab(I):=I;

end loop;

….

End;

 

 

 

 

 

 

 

 

2.RECORD

Declare

Type deptrectype is record

(deptno              number(2),

 dname              char(14),

 loc                  char(14),);

dept_rec            deptrectype;

begin

  select deptno,dname,loc into dept_rec from dept where deptno=30;

  …

  …

end;

与所有的编程语言一样,定义一个变量时,同样存在变量作用范围问题:

如下所示:

变量x为 real类型.

declare

  x  real;

function  function_name(variant type)return type is

  declare

 

在此范围之内变量x为char

    x char;

begin

  …

end function_name;

…

…

变量x为real类型

begin

x:=expression1…

…

end;

 

如果想引用另一block之变量时,可以加上block label,如下所示:

Block label;

<<outer>>

declare

birthdate    date;

begin

   …

   declare

      birthdate   date;

 

引用outer block块所定义之变量.

begin

….

If birthdate=outer.birthdate then

….

End if;

End;

End outer;

 

 

 

 

 

 

 

 

 

 

 

 

三、    PL/SQL控制程序流

1.        IF …THEN…

 Statment1

elseif…then…

 statment2

elseif…then…

 statment3

end if;

 

例:

<<outer>>

for ctr in 1..20 loop

…

  <<inner>>

  for ctr in 1..10 loop

   if outer.ctr>ctr then…

    …

  end loop inner;

end loop outer;

 

2.loop…exit…

  loop

   sequence_of_statement;

   …

   exit;

end loop;

例:

 loop

  fetch c1 into …

exit when c1%notfound;

…

end loop;

close c1;

 

 

另:加loop label;

例:<<outer>>

   loop

    …

    loop

     …

     exit outer when…

    end loop;

     …

  end loop outer;

 

3.while…loop…结构如下所示:

例

   while total<=25000 loop

     ….

     Select sa1 into salary from emp where…

       Total:=total+salary;

   End loop;

 

4.for…loop…

   例1.

      select count(empno) into emp_count from emp;

      for I in 1..emp_count loop

          …

      end loop;

 

例2.

   <<outer>>

   for step in 1..25 loop

       for step in 1..10 loop

          …

          if outer.step>15 then…

       end loop;

   end loop outer;

 

5.cursor….loop

四、    存储过程

要创建存储过程,可以使用下面的SQL和PL/SQL语句:

 

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME

AS

…

BEGIN

…

(SQL AND PL/SQL COMMANDS)

END;

 

五、    存储函数

 

 

 

 

六、    Package

 

Package分为两部分:Specific and Body

在包说明部分中,主要将此包中所含的过程和Function的调用参数说明清楚,如:

CREATE OR REPLACE PACKAGE ZDL_JOB_PKG

AS

PROCEDURE ZDL_INSERT_JOB(

    p_bkc_id in number,

    p_item_id in number,

    p_job_number in out varchar2,

    p_group_id in number,

    p_lead_day in number,

    p_load_type in number,

    P_STATUS_TYPE IN NUMBER,

    P_USER_ID IN NUMBER,

    P_JOB_TYPE IN VARCHAR2);

PROCEDURE ZDL_UPDATE_JOB(

         P_BKC_ID IN NUMBER,

         P_GROUP_ID IN NUMBER,

         P_STATUS_TYPE IN NUMBER,

         P_USER_ID IN NUMBER);

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER;

PROCEDURE ZDL_PRE_UPDATE;

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER;

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER;

END ZDL_JOB_PKG;

在包体部分,主要将包说明部分之过程及Function之代码写出来,

如:

CREATE OR REPLACE PACKAGE BODY ZDL_JOB_PKG

AS

PROCEDURE ZDL_INSERT_JOB (

    p_bkc_id in number,    p_item_id in number,p_job_number in out varchar2,

    p_group_id in number,

    p_lead_day in number,

    P_LOAD_TYPE IN NUMBER,

    P_STATUS_TYPE IN NUMBER,

    P_USER_ID IN NUMBER,

    P_JOB_TYPE IN VARCHAR2)

is

…………

begin

…………

end ZDL_INSERT_JOB;

 

PROCEDURE ZDL_UPDATE_JOB(P_BKC_ID IN NUMBER,P_GROUP_ID IN NUMBER,P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER)

AS

…………

BEGIN

…………

END ZDL_UPDATE_JOB;

 

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER

as

…………

begin

…………

END WIP_MASS_LOAD;

 

PROCEDURE ZDL_PRE_UPDATE IS

…………

begin

…………

end ZDL_PRE_UPDATE;

 

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER

IS

…………

begin

…………

end ZDL_UPDATE_ORACLE_WIP;

 

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER

AS

…………

BEGIN

…………

END ZDL_JOB_STATUS;

 

END ZDL_JOB_PKG;

七、    触发器

²      所需系统权限

要为某表创建触发器,必须能改变这个表,因此不仅要拥有表,并且要具有这个表的 alter权限,或者具有alter any table系统权限,除此之外,必须有create triger系统权限,若要在另一个用户帐号(account)(也称之为模式(schema))上创建触发器,就必具有create any trigger系统权限.

 

²      所需表权限

触发器可以引用的表并不是初始化触发事件的表.

 

²      触发器

触发器有十二种类型.一个触发器的类型由执行触发器的层次位置和触发事务的类型定义.

 

²      行级触发器

在某个事务中,行级触发器行执行,对于上述ledger表中记例子而言,触发器.行级触发器是在create trigger命令中通过用for each row 子句创建的.

 

²      合法的触发器类型

当两种不同类型之触发动作相结合时,有十二种可能的配置:

Before insert          行级触发器

before insert          语句级触发器

after insert       行级触发器

after insert       语句级触发器

before update         行级触发器

before update         语句级触发器

after update           行级触发器

after update      语句级触发器

before delete          行级触发器

before delete          语句级触发器

after delete       行级触发器

after delete       语句级触发器

 

 

 

 

例:

CREATE OR REPLACE TRIGGER "APPS"."ZDL_BKC_JOB_BODY_AFI"

AFTER INSERT ON "APPS"."ZDL_BKC_JOB_BODY"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

   UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE

        WHERE ZDL_BKC_JOB_HEAD_ID = :NEW.ZDL_BKC_JOB_BODY_ID;

END;

 

 

 

 

 

 

 

 

 

八、    应用实例

下面以电算部开发出之程序<<MDS展开及开工日维护程序>>之各类程序为例:

1.    Create table, index, sequence, table trigger

首先清除原先已有之重名table,Sequence等:

DROP TABLE ZDL_BKC_JOB_BODY;

DROP TABLE ZDL_BKC_JOB_HEAD;

DROP TABLE ZDL_BKC_JOB_UPDATE;

drop sequence zdl_bkc_job_s;

 

建立table, sequence以及Index

create table zdl_bkc_job_head

   (zdl_bkc_job_head_id number not null,

    assembly varchar2(9) not null,

    lot_no varchar2(240),

    job_no varchar2(240),

    OL_DATE date,

    quantity number,

    line_code varchar2(240),

    created_date date,

    update_date date,

    primary key(zdl_bkc_job_head_id)

);

 create table zdl_bkc_job_body

    (zdl_bkc_job_body_id number not null references zdl_bkc_job_head(zdl_bkc_job_head_id),

    level1 VARCHAR2(15),

    job1 varchar2(240),

    level2 VARCHAR2(15),

    job2 varchar2(240),

    level3 VARCHAR2(15),

    job3 varchar2(240),

    level4 VARCHAR2(15),

    job4 varchar2(240),

    level5 VARCHAR2(15),

    job5 varchar2(240));

create table ZDL_BKC_JOB_UPDATE

( BKC_ID NUMBER NOT NULL,

LOCATION_ID NUMBER NOT NULL,

ACTION_ID NUMBER NOT NULL,

JOB_NUMBER VARCHAR2(240),

UPDATED_FLAG VARCHAR2(1),

CREATION_DATE DATE,

UPDATED_DATE DATE

);

create sequence zdl_bkc_job_s;

CREATE INDEX ZDL_BKC_JOB_HEAD_N1 ON ZDL_BKC_JOB_HEAD(ZDL_BKC_JOB_HEAD_ID,ASSEMBLY);

CREATE INDEX ZDL_BKC_JOB_BODY_N1 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL1);

CREATE INDEX ZDL_BKC_JOB_BODY_N2 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL2);

CREATE INDEX ZDL_BKC_JOB_BODY_N3 ON ZDL_BKC_jOB_BODY(ZDL_BKC_JOB_BODY_ID,LEVEL3);

CREATE INDEX ZDL_BKC_JOB_UPDATE_N1 ON ZDL_BKC_JOB_UPDATE(ACTION_ID,LOCATION_ID);

COMMIT;

 

建立table Trigger:

-- Trigger head after update

CREATE OR REPLACE TRIGGER "APPS".ZDL_BKC_JOB_HEAD_AFU

AFTER UPDATE OF "LINE_CODE", "LOT_NO", "OL_DATE", "QUANTITY" ON "APPS"."ZDL_BKC_JOB_HEAD"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

INSERT INTO ZDL_BKC_JOB_UPDATE (

       BKC_ID,

       LOCATION_ID,

       ACTION_ID,

       JOB_NUMBER,

       UPDATED_FLAG,

       CREATION_DATE,

       UPDATED_DATE)

VALUES(

       :OLD.ZDL_BKC_JOB_HEAD_ID,

       1,

       3,

       :OLD.JOB_NO,

       'N',

       SYSDATE,

       SYSDATE);

END;

 

-- Trigger body before delete

CREATE OR REPLACE TRIGGER "APPS"."ZDL_BKC_JOB_BODY_BRD"

BEFORE DELETE ON "APPS"."ZDL_BKC_JOB_BODY"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

if :old.job1 is not null then

       INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

       VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB1,'N',SYSDATE,SYSDATE);

END IF;

if :old.job2 is not null then

       INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

       VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB2,'N',SYSDATE,SYSDATE);

END IF;

if :old.job3 is not null then

       INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

       VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB3,'N',SYSDATE,SYSDATE);

END IF;

if :old.job4 is not null then

       INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

       VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB4,'N',SYSDATE,SYSDATE);

END IF;

if :old.job5 is not null then

       INSERT INTO ZDL_BKC_JOB_UPDATE(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

       VALUES(:OLD.ZDL_BKC_JOB_BODY_ID,2,7,:old.JOB5,'N',SYSDATE,SYSDATE);

END IF;

UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE

       WHERE ZDL_BKC_JOB_HEAD_ID = :OLD.ZDL_BKC_JOB_BODY_ID;

END;

 

-- Trigger head after delete

CREATE OR REPLACE TRIGGER "APPS".ZDL_BKC_JOB_HEAD_BRD

BEFORE DELETE ON "APPS"."ZDL_BKC_JOB_HEAD"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

INSERT INTO ZDL_BKC_JOB_UPDATE

(BKC_ID,LOCATION_ID,ACTION_ID,JOB_NUMBER,UPDATED_FLAG,CREATION_DATE,UPDATED_DATE)

VALUES

       (:OLD.ZDL_BKC_JOB_HEAD_ID,1,7,:OLD.JOB_NO,'N',SYSDATE,SYSDATE);

END;

 

-- Trigger body after insert

CREATE OR REPLACE TRIGGER "APPS"."ZDL_BKC_JOB_BODY_AFI"

AFTER INSERT ON "APPS"."ZDL_BKC_JOB_BODY"

REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

UPDATE ZDL_BKC_JOB_HEAD SET UPDATE_DATE = SYSDATE

       WHERE ZDL_BKC_JOB_HEAD_ID = :NEW.ZDL_BKC_JOB_BODY_ID;

END;

2.    建立两个主要之package:

A.            ZDL_BKC_APP_PKG

Package Specific

 

CREATE OR REPLACE PACKAGE ZDL_BKC_APP_PKG

AS

/*BOM之展开*/

PROCEDURE ZDL_BOM_EXPLOSION(

       P_ITEM_ID IN NUMBER,

       p_Organization_id IN NUMBER,

       P_BOM_GROUP_ID IN NUMBER,

       P_EXPL_QTY IN NUMBER,

       P_ERROR_CODE OUT NUMBER);

 

/*展开之半制品放于ZDL_JOB_BKC_HEAD AND ZDL_JOB_BKC_BODY中,并调用ZDL_JOB_PKG中的

相閞过程实现JOB之产生及LOAD入Oracle MFG中*/

PROCEDURE ZDL_PROCESS_BOM(

       P_ITEM_ID in number,

       P_BKC_ID OUT NUMBER,

       P_BOM_GROUP_ID IN NUMBER,

       P_GROUP_ID IN NUMBER,

       P_SCHEDULE_COMMENTS IN VARCHAR2,

       P_SCHEDULE_QUANTITY IN NUMBER,

       P_SCHEDULE_DATE IN DATE,

       P_ORGANIZATION_ID IN NUMBER,

       P_USER_ID IN NUMBER,

       P_ERROR_CODE OUT NUMBER,

       P_JOB_TYPE IN VARCHAR2);

END ZDL_BKC_APP_PKG;

 

 

Package Body

 

CREATE OR REPLACE PACKAGE BODY ZDL_BKC_APP_PKG

AS

PROCEDURE ZDL_BOM_EXPLOSION(

       P_ITEM_ID IN NUMBER,

       p_Organization_id IN NUMBER,

       P_BOM_GROUP_ID IN NUMBER,

       P_EXPL_QTY IN NUMBER,

       P_ERROR_CODE OUT NUMBER)

AS

  l_seq_id                NUMBER;

  l_bom_or_eng            NUMBER:=1;

  l_err_msg               VARCHAR2(80);

  l_err_code              NUMBER;

  exploder_error          EXCEPTION;

  loop_error              EXCEPTION;

  table_name            VARCHAR2(20);

  item_id_null           EXCEPTION;

  p_revision_date      varchar2(15);

  P_EXPLODE_OPTION_TYPE   varchar2(100);

BEGIN

  P_ERROR_CODE := 0;

  SELECT BOM_LISTS_S.NEXTVAL

  INTO  l_seq_id

  FROM  DUAL;

 

  TABLE_NAME := 'BUILD SQL';

  INSERT INTO BOM_LISTS (SEQUENCE_ID, ASSEMBLY_ITEM_ID,

                                ALTERNATE_DESIGNATOR)

        SELECT DISTINCT l_seq_id,P_ITEM_ID,

                bbom.alternate_bom_designator

         FROM   bom_bill_of_materials bbom

         WHERE  bbom.organization_id = 102

         AND    bbom.assembly_item_id = P_ITEM_ID

         AND    (bbom.alternate_bom_designator IS NULL)

         AND    (bbom.assembly_type = 1);

    commit;

    TABLE_NAME := 'EXECUTE SQL';

/* Call BOM exploder */

   TABLE_NAME := 'CALL EXPLODER';

   -- bug 519321

   P_REVISION_DATE := to_char(sysdate,'DD-MON-YY HH24:MI');

   bompexpl.explosion_report

        (

        org_id => p_Organization_id, 

        order_by => 2,  

        list_id => l_seq_id,   

        grp_id => P_BOM_GROUP_ID,       

        session_id => -1,

        levels_to_explode => 15,

        bom_or_eng => 1,       

        impl_flag => 1,       

        explode_option => 2,

        module => 2,

        cst_type_id => -1,

        std_comp_flag => -1,

        expl_qty => P_EXPL_QTY,           

        report_option => -1,

        req_id => 0,

        lock_flag => -1,

        rollup_option => -1,

        alt_rtg_desg => '',

        alt_desg => '',

        rev_date => P_REVISION_DATE,

        err_msg => l_err_msg,

        error_code => l_err_code,

        verify_flag =>0,

        cst_rlp_id => 0,

        plan_factor_flag => 2,

        incl_lt_flag => 2

        );

  commit;

  TABLE_NAME := 'EXPLODE COMPLETE';

  if l_err_code = 9999 then

    raise loop_error;

  end if;

  if l_err_code <0  then

    raise exploder_error;

  end if;

  commit;    --save

  DELETE FROM BOM_LISTS WHERE SEQUENCE_ID = L_SEQ_ID;

  COMMIT;

EXCEPTION

    WHEN exploder_error THEN

P_ERROR_CODE := 1;

        dbms_output.put_line(l_err_msg);  

    WHEN loop_error THEN

P_ERROR_CODE := 2;

        dbms_output.put_line('aaa');

    WHEN item_id_null THEN

P_ERROR_CODE := 3;

        dbms_output.put_line('Item is is null');

    WHEN NO_DATA_FOUND THEN

P_ERROR_CODE := 4;

        dbms_output.put_line(TABLE_NAME ||SQLERRM);

    WHEN OTHERS THEN

P_ERROR_CODE := 5;

        dbms_output.put_line(TABLE_NAME || SQLERRM);

END ZDL_BOM_EXPLOSION;

 

/* Process data of bom_explosion_temp */

PROCEDURE ZDL_PROCESS_BOM(

       P_ITEM_ID in number,

       P_BKC_ID OUT NUMBER,

       P_BOM_GROUP_ID IN NUMBER,

       P_GROUP_ID IN NUMBER,

       P_SCHEDULE_COMMENTS IN VARCHAR2,

       P_SCHEDULE_QUANTITY IN NUMBER,

       P_SCHEDULE_DATE IN DATE,

       P_ORGANIZATION_ID IN NUMBER,

       P_USER_ID IN NUMBER,

       P_ERROR_CODE OUT NUMBER,

       P_JOB_TYPE IN VARCHAR2)

AS

CURSOR C1 IS

SELECT

       BET.ASSEMBLY_ITEM_ID,

       MSI.SEGMENT1,

       BET.COMPONENT_ITEM_ID,

       BET.PLAN_LEVEL

FROM BOM.BOM_EXPLOSION_TEMP BET,

       INV.MTL_SYSTEM_ITEMS MSI

WHERE

       BET.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID AND

       BET.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND

       MSI.ORGANIZATION_ID = P_ORGANIZATION_ID AND

       BET.GROUP_ID = P_BOM_GROUP_ID AND

       BET.TOP_ITEM_ID = P_ITEM_ID AND

       (MSI.ITEM_TYPE = 'SA' OR MSI.ITEM_TYPE = 'FG')

ORDER BY BET.PLAN_LEVEL;

P_C1 C1%ROWTYPE;

R_ITEM VARCHAR2(15);

P_JOB_NUMBER NUMBER;

BEGIN

P_ERROR_CODE := 0;

OPEN C1;

LOOP

       FETCH C1 INTO P_C1;

       EXIT WHEN C1%NOTFOUND;

       IF P_C1.PLAN_LEVEL = 0 THEN

            select zdl_bkc_job_s.nextval into P_BKC_ID from sys.dual;

            insert into zdl_bkc_job_head

            (    zdl_bkc_job_head_id,

                 assembly,

                 CREATED_DATE,

                 update_date,

                 QUANTITY,

                 LOT_NO,

                 LINE_CODE,

                 OL_DATE)

            values

            (    P_BKC_ID,

                 P_item_ID,

                 SYSDATE,

                 SYSDATE,

                 P_SCHEDULE_QUANTITY,

                SUBSTR(P_SCHEDULE_COMMENTS,1,INSTR(P_SCHEDULE_COMMENTS,'/')-1),

SUBSTR(P_SCHEDULE_COMMENTS,INSTR(P_SCHEDULE_COMMENTS,'/')+1,

LENGTH(P_SCHEDULE_COMMENTS)),

                 P_SCHEDULE_DATE);

            COMMIT;

       ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

0,1,0,P_USER_ID,P_JOB_TYPE);

            UPDATE ZDL_BKC_JOB_HEAD SET JOB_NO = P_JOB_NUMBER WHERE ZDL_BKC_JOB_HEAD_ID = P_BKC_ID;

       ELSIF P_C1.PLAN_LEVEL = 1 THEN

       ZDL_JOB_PKG.zdl_insert_job(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,p_job_number,p_group_id,

-2,1,0,P_USER_ID,P_JOB_TYPE);

            insert into zdl_bkc_job_body

                 (zdl_bkc_job_body_id,level1,job1)

            values(P_BKC_ID,P_C1.SEGMENT1,p_job_number);

            COMMIT;

       ELSIF P_C1.PLAN_LEVEL = 2 THEN

            SELECT SEGMENT1 INTO R_ITEM

                 FROM MTL_SYSTEM_ITEMS

                 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

       ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-3,1,0,P_USER_ID,P_JOB_TYPE);

            UPDATE ZDL_BKC_JOB_BODY SET

                 LEVEL2 = P_C1.SEGMENT1,

                 JOB2 = P_JOB_NUMBER

                 WHERE LEVEL1 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

            COMMIT;

       ELSIF P_C1.PLAN_LEVEL = 3 THEN

            SELECT SEGMENT1 INTO R_ITEM

                 FROM MTL_SYSTEM_ITEMS

                 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

       ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-4,1,0,P_USER_ID,P_JOB_TYPE);

            UPDATE ZDL_BKC_JOB_BODY SET

                 LEVEL3 = P_C1.SEGMENT1,

                 JOB3 = P_JOB_NUMBER

                 WHERE LEVEL2 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

            COMMIT;

       ELSIF P_C1.PLAN_LEVEL = 4 THEN

            SELECT SEGMENT1 INTO R_ITEM

                 FROM MTL_SYSTEM_ITEMS

                 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

       ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-6,1,0,P_USER_ID,P_JOB_TYPE);

            UPDATE ZDL_BKC_JOB_BODY SET

                 LEVEL4 = P_C1.SEGMENT1,

                 JOB3 = P_JOB_NUMBER

                 WHERE LEVEL3 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

            COMMIT;

       ELSIF P_C1.PLAN_LEVEL = 5 THEN

            SELECT SEGMENT1 INTO R_ITEM

                 FROM MTL_SYSTEM_ITEMS

                 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID

                       AND INVENTORY_ITEM_ID = P_C1.ASSEMBLY_ITEM_ID;

       ZDL_JOB_PKG.ZDL_INSERT_JOB(P_BKC_ID,P_C1.COMPONENT_ITEM_ID,P_JOB_NUMBER,P_GROUP_ID,

-6,1,0,P_USER_ID,P_JOB_TYPE);

            UPDATE ZDL_BKC_JOB_BODY SET

                 LEVEL5 = P_C1.SEGMENT1,

                 JOB3 = P_JOB_NUMBER

                 WHERE LEVEL4 = R_ITEM AND ZDL_BKC_JOB_BODY_ID = P_BKC_ID;

            COMMIT;

       END IF;

END LOOP;

CLOSE C1;

DELETE FROM BOM_EXPLOSION_TEMP WHERE GROUP_ID = P_BOM_GROUP_ID;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

       P_ERROR_CODE := 6;

END ZDL_PROCESS_BOM;

 

END ZDL_BKC_APP_PKG;

 

 

B.            ZDL_JOB_PKG

 

Package Specific

 

CREATE OR REPLACE PACKAGE ZDL_JOB_PKG

AS

 

/*给每个展开之半制品分配一个JOB号,并将其存储于WIP_JOB_SCHEDULE_INTERFACE中*/

PROCEDURE ZDL_INSERT_JOB(

p_bkc_id in number,

p_item_id in number,

p_job_number in out varchar2,

p_group_id in number,

p_lead_day in number,

p_load_type in number,

P_STATUS_TYPE IN NUMBER,

P_USER_ID IN NUMBER,

P_JOB_TYPE IN VARCHAR2);

 

/*维护O/L日期等时自动更新Oracle MFG之WIP*/

PROCEDURE ZDL_UPDATE_JOB(

       P_BKC_ID IN NUMBER,

       P_GROUP_ID IN NUMBER,

       P_STATUS_TYPE IN NUMBER,

       P_USER_ID IN NUMBER);

 

/*触发WIP JOB MASS LOAD程序*/

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER;

 

/*在使用ZDL_BKC_JOB_UPDATE檔更新Oracle MFG时,将其中之相关资料织一下*/

PROCEDURE ZDL_PRE_UPDATE;

 

/*调用前面之相关程序,并根据ZDL_BKC_JOB_UPDATE文件更新Oracle MFG*/

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER;

 

/*检查Oracle JOB之状态,并返回*/

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER;

END ZDL_JOB_PKG;

 

Package Body

 

CREATE OR REPLACE PACKAGE BODY ZDL_JOB_PKG

AS

PROCEDURE ZDL_INSERT_JOB (

p_bkc_id in number,

p_item_id in number,

p_job_number in out varchar2,

p_group_id in number,

p_lead_day in number,

P_LOAD_TYPE IN NUMBER,

P_STATUS_TYPE IN NUMBER,

P_USER_ID IN NUMBER,

P_JOB_TYPE IN VARCHAR2)

is

p_completion_date date;

r_schedule_date date;

p_start_quantity number;

l_seq_num number;

l_next_seq_num number;

p_lot_number varchar2(240);

P_LINE_CODE VARCHAR2(240);

p_wip_entity_id number;

begin

/* P_BKC_ID = 0 MEAN THAT THE BKC ID HAS BEEN DELETE FROM TABLE */

IF NOT (P_BKC_ID = 0 AND P_STATUS_TYPE = 7) THEN

select OL_DATE,quantity,lot_no,line_code into p_completion_date,p_start_quantity,p_lot_number,P_LINE_CODE

       from zdl_bkc_job_head where ZDL_BKC_JOB_HEAD_ID = p_bkc_id;

END IF;

/* P_STSTUS_TYPE = 7 MEAD THAT THIS JOB MUST BE CANCELLED */

IF P_STATUS_TYPE <> 7 THEN

select seq_num,next_seq_num into l_seq_num,l_next_seq_num

       from bom_calendar_dates

       where trunc(calendar_date) = trunc(p_completion_date);

if l_seq_num is null then

       l_seq_num:=l_next_seq_num-1;

end if;

l_seq_num:=l_seq_num+p_lead_day;

select calendar_date into r_schedule_date from bom_calendar_dates where seq_num=l_seq_num;

END IF;

/* P_LOAD_TYPE = 1 : ADD A JOB INTO ORACLE WIP

   P_LOAD_TYPE = 3 : UPDATE A JOB OF ORACLE WIP*/

if p_load_type = 1 then

select wip_job_number_s.nextval into p_job_number from sys.dual;

elsif p_load_type = 3 then

select wip_entity_id into p_wip_entity_id from wip_entities where wip_entity_name = p_job_number;

end if;

insert into wip_job_schedule_interface

(

last_update_date,

creation_date,

created_by,

last_updated_by,

group_id,

process_phase,

process_status,

load_type,

job_name,

wip_entity_id,

LAST_UNIT_COMPLETION_DATE,

organization_id,

primary_item_id,

description,

start_quantity,

STATUS_TYPE,

ATTRIBUTE_CATEGORY,

ATTRIBUTE1,

ATTRIBUTE2,

ATTRIBUTE3)

values

( sysdate,

sysdate,

P_USER_ID,

P_USER_ID,

p_group_id,

2,

1,

P_LOAD_TYPE,

p_job_number,

decode(p_load_type,1,null,p_wip_entity_id),

DECODE(P_STATUS_TYPE,0,to_date(to_char(r_schedule_date,'DD-MON-YYYY HH24:MI:SS'),

'DD-MON-YYYY HH24:MI:SS'),NULL),

102,

decode(p_load_type,1,p_item_id,null),

decode(p_load_type,1,'Created By ZDL BKC Program',DECODE(P_STATUS_TYPE,0,

'Updated by ZDL BKC Program','Cancelled by ZDL BKC Program')),

DECODE(P_STATUS_TYPE,0,p_start_quantity,NULL),

DECODE(P_STATUS_TYPE,0,NULL,P_STATUS_TYPE),

'JOB',

DECODE(P_LOAD_TYPE,1,P_JOB_TYPE,NULL),

p_lot_number,

P_LINE_CODE

);

commit;

end ZDL_INSERT_JOB;

 

PROCEDURE ZDL_UPDATE_JOB(P_BKC_ID IN NUMBER,P_GROUP_ID IN NUMBER,

P_STATUS_TYPE IN NUMBER,P_USER_ID IN NUMBER)

AS

P_JOB_NO VARCHAR2(240);

P_LOT_NO VARCHAR2(240);

P_QUANTITY NUMBER;

P_LINE_CODE VARCHAR2(240);

P_OL_DATE DATE;

p_job1 zdl_bkc_job_body.job1%type;    

p_job2 zdl_bkc_job_body.job2%type;

p_job3 zdl_bkc_job_body.job3%type;

p_job4 zdl_bkc_job_body.job4%type;

p_job5 zdl_bkc_job_body.job5%type;

cursor l_bkc is

select job1,job2,job3,job4,job5 from zdl_bkc_job_body where zdl_bkc_job_body_id=p_bkc_id;

BEGIN

SELECT     JOB_NO,LOT_NO,QUANTITY,LINE_CODE,OL_DATE

INTO P_JOB_NO,P_LOT_NO,P_QUANTITY,P_LINE_CODE,P_OL_DATE

FROM ZDL_BKC_JOB_HEAD

WHERE ZDL_BKC_JOB_HEAD_ID = P_BKC_ID;

zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,p_job_no,p_group_id,0,3,P_STATUS_TYPE,P_USER_ID,NULL);

open l_bkc;

loop

fetch l_bkc into p_job1,p_job2,p_job3,p_job4,p_job5;

       exit when l_bkc%notfound;

       if P_job1 is not null then

       zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB1,P_GROUP_ID,-2,3,P_STATUS_TYPE,P_USER_ID,NULL);

       end if;

       IF P_JOB2 IS NOT NULL THEN

       zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB2,P_GROUP_ID,-3,3,P_STATUS_TYPE,P_USER_ID,NULL);

       END IF;

       IF P_JOB3 IS NOT NULL THEN

       zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB3,P_GROUP_ID,-4,3,P_STATUS_TYPE,P_USER_ID,NULL);

       END IF;

       IF P_JOB4 IS NOT NULL THEN

       zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB4,P_GROUP_ID,-6,3,P_STATUS_TYPE,P_USER_ID,NULL);

       END IF;

       IF P_JOB5 IS NOT NULL THEN

       zdl_job_pkg.ZDL_INSERT_JOB(P_BKC_ID,0,P_JOB5,P_GROUP_ID,-8,3,P_STATUS_TYPE,P_USER_ID,NULL);

       END IF;     

end loop;

END ZDL_UPDATE_JOB;

 

FUNCTION WIP_MASS_LOAD(P_GROUP_ID IN NUMBER,P_USER_ID IN NUMBER) RETURN NUMBER

as

req_id number;

LOGINID NUMBER;

begin

SELECT FND_CONCURRENT_REQUESTS_S.NEXTVAL INTO REQ_ID FROM DUAL;

SELECT FND_LOGINS_S.NEXTVAL INTO LOGINID FROM DUAL;

insert into   FND_CONCURRENT_REQUESTS (

REQUEST_ID,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_LOGIN,

REQUEST_DATE,

REQUESTED_BY,

PHASE_CODE,

STATUS_CODE,

PRIORITY_REQUEST_ID,

PRIORITY,

REQUESTED_START_DATE,

HOLD_FLAG,

ENFORCE_SERIALITY_FLAG,

SINGLE_THREAD_FLAG,

HAS_SUB_REQUEST,

IS_SUB_REQUEST,

IMPLICIT_CODE,

UPDATE_PROTECTED,

QUEUE_METHOD_CODE,

ARGUMENT_INPUT_METHOD_CODE,

ORACLE_ID,

PROGRAM_APPLICATION_ID,

CONCURRENT_PROGRAM_ID,

RESPONSIBILITY_APPLICATION_ID,

RESPONSIBILITY_ID,

NUMBER_OF_ARGUMENTS,

NUMBER_OF_COPIES,

SAVE_OUTPUT_FLAG,

NLS_LANGUAGE,

NLS_TERRITORY,

PRINTER,

PRINT_STYLE,

PRINT_GROUP,

REQUEST_CLASS_APPLICATION_ID,

CONCURRENT_REQUEST_CLASS_ID,

PARENT_REQUEST_ID,

CONC_LOGIN_ID,

LANGUAGE_ID,

DESCRIPTION,

REQ_INFORMATION,

RESUBMIT_INTERVAL,

RESUBMIT_INTERVAL_UNIT_CODE,

RESUBMIT_INTERVAL_TYPE_CODE,

RESUBMIT_TIME,

RESUBMIT_END_DATE,

RESUBMITTED,

CONTROLLING_MANAGER,

ACTUAL_START_DATE,

ACTUAL_COMPLETION_DATE,

COMPLETION_TEXT,

OUTCOME_PRODUCT,

OUTCOME_CODE,

CPU_SECONDS,

LOGICAL_IOS,

PHYSICAL_IOS,

LOGFILE_NAME,

LOGFILE_NODE_NAME,

OUTFILE_NAME,

OUTFILE_NODE_NAME,

ARGUMENT_TEXT,

ARGUMENT1,

ARGUMENT2,

ARGUMENT3,

ARGUMENT4,

ARGUMENT5,

ARGUMENT6,

ARGUMENT7,

ARGUMENT8,

ARGUMENT9,

ARGUMENT10,

ARGUMENT11,

ARGUMENT12,

ARGUMENT13,

ARGUMENT14,

ARGUMENT15,

ARGUMENT16,

ARGUMENT17,

ARGUMENT18,

ARGUMENT19,

ARGUMENT20,

ARGUMENT21,

ARGUMENT22,

ARGUMENT23,

ARGUMENT24,

ARGUMENT25,

CRM_THRSHLD,

CRM_TSTMP

)

VALUES

(

REQ_ID,

SYSDATE,

P_USER_ID,

LOGINID,

SYSDATE,

P_USER_ID,

'P',

'I',

REQ_ID,

50,

SYSDATE,

'N',

'Y',

'N',

'N',

'N',

'N',

'N',

'I',

'S',

900,

706,

34291,

706,

20560,

3,

0,

'Y',

'AMERICAN',

'AMERICA',

NULL,

'LANDSCAPE',

'N',

NULL,

NULL,

-1,

LOGINID,

0,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

'N',

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

RTRIM(TO_CHAR(P_GROUP_ID))||', 0, 1',

P_GROUP_ID,

0,

1,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

NULL,

0,

NULL

);

COMMIT;

return(req_id);

END WIP_MASS_LOAD;

 

PROCEDURE ZDL_PRE_UPDATE IS

cursor c1 is

       select rowid,bkc_id,location_id,action_id,job_number

from zdl_bkc_job_update where updated_flag = 'N';

p_c1_rec c1%rowtype;

p_count number default 0;

begin

delete

       from zdl_bkc_job_update z1

       where rowid !=

            (select

                 max(rowid)

            from

                 zdl_bkc_job_update z2

            where

                 z1.bkc_id = z2.bkc_id and

                 z1.location_id = z2.location_id and

                 z1.action_id = z2.action_id and

                 z1.job_number = z2.job_number AND

                 Z1.UPDATED_FLAG = z2.updated_flag)

       and z1.updated_flag = 'N';

commit;

open c1;

loop

       fetch c1 into p_c1_rec;

       exit when c1%notfound;

       if p_c1_rec.action_id = 3 then

            select count(*) into p_count

                 from zdl_bkc_job_update

                 where bkc_id = p_c1_rec.bkc_id and

                       location_id = p_c1_rec.location_id and

                       action_id = 7 and

                       job_number = p_c1_rec.job_number AND UPDATED_FLAG = 'N';

            if p_count > 0 then

                 delete from zdl_bkc_job_update

                       where rowid = p_c1_rec.rowid;

                 commit;

            end if;       

       end if; 

end loop;

end ZDL_PRE_UPDATE;

 

FUNCTION ZDL_UPDATE_ORACLE_WIP(P_USER_ID IN NUMBER) RETURN NUMBER

IS

P_GROUP_ID NUMBER;

P_REQ_ID NUMBER;

p_bkc_id number;

p_action_id number;

p_location_id number;

p_job_number varchar2(240);

p_count boolean default false;

p_count_item number;

cursor c1 is

select   bkc_id,action_id,location_id,job_number

from    zdl_bkc_job_update

where   action_id = 3 and location_id = 1 AND UPDATED_FLAG = 'N';

cursor c2 is

select   bkc_id,action_id,location_id,job_number

from    zdl_bkc_job_update

where   action_id = 7 AND UPDATED_FLAG = 'N';

begin

ZDL_JOB_PKG.ZDL_PRE_UPDATE;

select wip_job_schedule_interface_s.nextval into p_group_id from sys.dual;

open c1;

loop

       fetch c1 into p_bkc_id,p_action_id,p_location_id,p_job_number;

       exit when c1%notfound;

       zdl_job_pkg.zdl_update_job(p_bkc_id,p_group_id,0,P_USER_ID);

end loop;

if c1%rowcount > 0 then

       UPDATE zdl_bkc_job_update SET UPDATED_FLAG = 'Y',UPDATED_DATE = SYSDATE

            where action_id = 3 and location_id = 1 AND UPDATED_FLAG = 'N';

       p_count := true;

end if;

close c1;

commit;

open c2;

loop

       fetch c2 into p_bkc_id,p_action_id,p_location_id,p_job_number;

       exit when c2%notfound;

       zdl_job_pkg.zdl_insert_job(0,0,p_job_number,p_group_id,0,3,7,P_USER_ID,NULL);

end loop;

if c2%rowcount > 0 then

       p_count := true;

       UPDATE zdl_bkc_job_update SET UPDATED_FLAG = 'Y',UPDATED_DATE = SYSDATE

            where action_id = 7 AND UPDATED_FLAG = 'N';

end if;

close c2;

commit;

 

if p_count then

       P_REQ_ID := ZDL_JOB_PKG.WIP_MASS_LOAD(P_GROUP_ID,P_USER_ID);

else

       p_req_id := 0;

end if;

RETURN(P_REQ_ID);

end ZDL_UPDATE_ORACLE_WIP;

 

FUNCTION ZDL_JOB_STATUS (P_JOB_NUMBER IN VARCHAR2) RETURN NUMBER

AS

P_STATUS_TYPE NUMBER;

BEGIN

SELECT

STATUS_TYPE INTO P_STATUS_TYPE

FROM

WIP_DISCRETE_JOBS WDJ,

WIP_ENTITIES WE

WHERE  WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND

WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID AND

WE.WIP_ENTITY_NAME = P_JOB_NUMBER;

RETURN (P_STATUS_TYPE);

END ZDL_JOB_STATUS;

 

END ZDL_JOB_PKG;