SQL PL/SQL语法手册

最新推荐文章于 2024-09-25 09:21:39 发布
最新推荐文章于 2024-09-25 09:21:39 发布
阅读量107 收藏
点赞数
文章标签： 数据库

八、 应用实例

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

iteye_7332
关注
PLSQL基本操作手册
Link的专栏
06-23 11万+
公众号：池哥搬砖 QQ1群:745460286 个人WX 公号： 目录 第1章 用PLSQL连接Oracle数据库 §1.2登录信息保存功能设置： §1.3进入PLSQL后切换数据库连接： 第2章 PLSQL中编写SQL语句并执行 第3章 PLSQL中查看数据库表结构 §3.1查看表结构： §3.2表结构窗口和SQL编写窗口切换： 第4章 ...
PL/SQL Trigger PL/SQL TriggerPL/SQL TriggerPL/SQL Trigger
08-02
PL/SQL Trigger是Oracle数据库中的一个关键特性，用于在特定的数据库事件发生时自动执行一段预定义的PL/SQL代码。这些事件可能包括对表的INSERT、UPDATE或DELETE操作，或者其他的数据库活动。触发器是数据库级别的...
Oracle+PLSQL+语法手册 全面 详细 chm
05-06
Oracle+PLSQL+语法手册 全面 详细
PLSQL基本语法
u011521890的专栏
09-13 9404
–使用plsql输出hello worlddeclare begin dbms_output.put_line('Hello World'); end;–定义记录型变量set serveroutput on; declare --定义记录型变量 emp_rec emp%rowtype; begin select * into emp_rec from emp wh
很多表里面的用户created_by , updated_by 在哪个表里面?
06-28 1600
select * from fnd_user where user_NAME='TN1133' [@more@]user_id=7149 这个id 在我们每个表单里存在于 created_by , u...
PLSQL的基本语法及实现
热门推荐
shaoduo的博客
04-07 1万+
1.PL/SQL语言的组成PL/SQL是一种块结构的语言，这意味着PL/SQL程序被划分和编写代码的逻辑块。每块由三个子部分组成：DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling> END;DECLARE 声明部分 此部分开头使用关键字DECLA
pl/sql基础语法操作
qq_43583887的博客
05-23 693
oracle pl/sql语言（procedural language/sql）是结合了结构化查询与oracle自身过程控制为一体的强大语言。
SQL PL/SQL 语法手册.doc
fulei_master的专栏
07-03 688
SQL PL/SQL 语法手册.doc http://www.open-open.com/doc/view/ab3fcde50b4a429faa0c4365760c73dd
Oracle PL/SQL基础语法学习16：CASE Expression（CASE表达式）
SQLplusDB的小院
05-09 1305
在Oracle PL/SQL中，CASE表达式（CASE Expression）是一个非常强大且常用的工具，可以用于在SQL或PL/SQL中执行基于条件的操作。CASE表达式类似于IF-THEN-ELSE语句，但使用起来更加灵活，易于阅读和编写。
Oracle PL/SQL基础语法学习13：比较运算符
SQLplusDB的小院
05-03 2105
比较运算符是 PL/SQL 语言中的基本元素之一，它们被用于比较两个变量或表达式的值。在 PL/SQL 中，比较运算符是十分常见的，因此熟练运用它们能够提高代码的开发效率。!（或），=。这些运算符可以用在各种数据类型上，包括数字、字符、日期等类型。END IF;IF c!END IF;END IF;END;SQL> /PL/SQL 过程已成功完成。在上面的示例中，我们分别使用了!（或）和>=三种比较运算符，用于比较数字、字符和日期类型的数据。当条件成立时，输出相应的信息。
pl/sql64位
01-15
1. **代码编辑器**：提供语法高亮、自动完成和错误检查，使得编写PL/SQL代码更加便捷。 2. **数据库连接**：支持多种Oracle数据库版本的连接，无需复杂的配置步骤，只需输入必要的连接信息。 3. **对象浏览器**：...
pl/sql developer11.0
03-26
1. **代码编辑器**：PL/SQL Developer提供了一个功能丰富的代码编辑器，支持语法高亮、自动完成、错误检测、代码折叠和多文档界面。这些特性极大地简化了代码编写和维护的过程。 2. **调试工具**：内置的调试器允许...
Oracle PL/SQL实战（待续）
07-27
Oracle PL/SQL是一种强大的编程语言，它结合了SQL的数据处理能力与PL的程序设计特性，是Oracle数据库系统中用于创建存储过程、函数、触发器和包的主要工具。在这个"Oracle PL/SQL实战（待续）"的主题中，我们将深入...
pl/sql 免安装，绿色版pl/sql
02-25
1. **PL/SQL编程支持**：PL/SQL Developer内置了强大的PL/SQL编辑器，支持语法高亮、自动完成、错误检查等功能，使开发人员能够高效编写PL/SQL代码。此外，还具备代码折叠、查找替换、书签设置等实用功能，提高了...
PL/SQL 基本语法
DMS程序猿
04-06 1万+
PL/SQL介绍PL/SQL是Oracle对标准数据库语言SQL的过程化扩充，它将数据库技术和过程化程序设计语言联系起来，是一种应用开发语言，可使用循环，分支处理数据，将SQL的数据操纵功能与过程化语言数据处理功能结合起来。PL/SQL的使用，使SQL成为一种高级程序设计语言，支持高级语言的块操作，条件判断，循环语句，嵌套等，与数据库核心的数据类型集成，使SQL 的程序设计效率更高。PL/SQL作用
计算机毕业设计之：基于微信小程序的诗词智能学习系统（源码+文档+解答）
程序员阿龙的博客
09-22 4181
博主介绍： ✌我是阿龙，一名专注于Java技术领域的程序员，全网拥有10W+粉丝。作为CSDN特邀作者、博客专家、新星计划导师，我在计算机毕业设计开发方面积累了丰富的经验。同时，我也是掘金、华为云、阿里云、InfoQ等平台的优质作者。通过长期分享和实战指导，我致力于帮助更多学生完成毕业项目和技术提升。 技术范围： 我熟悉的技术领域涵盖SpringBoot、Vue、SSM、HLMT、Jsp、PHP、Nodejs、Python、爬虫、数据可视化、小程序、安卓app、大数据、物联网、机器学习等方
Online DDL (Data Definition Language)
09-23 232
是MySQL中的一个功能，允许在修改表结构（如添加、删除或修改列）的同时，允许对表进行并发读写操作。在传统的DDL操作中，对表结构的修改会导致整个表被锁定，从而阻止其他事务对该表进行读写操作，这可能导致长时间的锁定和性能问题。Online DDL的引入解决了这个问题，它允许在修改表结构的同时，保持对表的并发访问。MySQL从5.6版本开始引入了Online DDL的部分功能，而在8.0版本中对Online DDL进行了进一步的优化。
Postgresql特殊关键字
最新发布
czjnoe的博客
09-25 165
表字段名称如果出现这些特殊关键字，则需要通过单引号访问字段，如offset、group。PostgreSQL特殊关键字。
Java Alibaba Druid 数据库连接池
miracle的专栏
09-24 561
在Java开发中，数据库连接池是性能优化的重要一环。而作为一款强大的数据库连接池解决方案，凭借其和对多种数据库的支持，成为了许多企业级应用的首选。本篇文章将介绍Druid的核心特性，并结合不同数据库的实际用法。
Oracle SQL & PL/SQL语法手册：从基础到实践
"这是一份详细的PLSQL语法手册，涵盖了SQL和PL/SQL的主要语法结构，包括创建表、序列、视图，以及插入、更新、删除等操作。此外，手册还涉及了各种函数的使用，如转换函数、日期函数、字符函数、数值函数等，并深入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值