SQL PL/SQL语法手册

八、 应用实例

下面以电算部开发出之程序<<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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值