- 浏览: 99471 次
-
文章分类
最新评论
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;
发表评论
相关推荐
这个"PL/SQL最新中文手册"显然是一份详细解释PL/SQL 7.0版本的指南，对于学习和精通Oracle数据库编程至关重要。以下是手册可能涵盖的一些核心知识点： 1. **PL/SQL基础**：手册可能会从基础开始，介绍PL/SQL的基本...
PL/SQL，全称Procedural Language/Structured Query Language，是Oracle公司为标准SQL扩展的一种编程语言，它深度融合在Oracle数据库系统中，提供更高级别的数据处理能力。PL/SQL结合了过程化编程的特性，使得数据库...
Oracle PL/SQL是一种强大的编程语言，它结合了SQL的数据处理能力与PL的程序设计特性，是Oracle数据库系统中用于创建存储过程、函数、触发器和包的主要工具。在这个"Oracle PL/SQL实战（待续）"的主题中，我们将深入...
本压缩包"PL/SQL语法帮助"是专为初学者设计的指南，包含Sqlhelp.hlp和Plshelp.hlp两个帮助文件，旨在帮助新手快速掌握PL/SQL的基本概念和用法。 首先，让我们深入了解一下PL/SQL的主要组成部分： 1. **声明部分**...
1. **代码编辑器**：PL/SQL Developer提供了一个功能丰富的代码编辑器，支持语法高亮、自动完成、错误检测、代码折叠和多文档界面。这些特性极大地简化了代码编写和维护的过程。 2. **调试工具**：内置的调试器允许...
1. **代码编辑器**：提供语法高亮、自动完成和错误检查，使得编写PL/SQL代码更加便捷。 2. **数据库连接**：支持多种Oracle数据库版本的连接，无需复杂的配置步骤，只需输入必要的连接信息。 3. **对象浏览器**：...
手册分为两大部分，分别对SQL语法和PL/SQL语法进行了详细讲解。 第一部分，SQL语法部分，主要涉及以下关键语句和概念： 1. **CREATE TABLE** 语句：用于创建数据库中的表，定义了表的结构，包括列名、数据类型、...
PL/SQL Trigger是Oracle数据库中的一个关键特性，用于在特定的数据库事件发生时自动执行一段预定义的PL/SQL代码。这些事件可能包括对表的INSERT、UPDATE或DELETE操作，或者其他的数据库活动。触发器是数据库级别的...
综上所述，《PL/SQL开发手册》不仅涵盖了PL/SQL的基本语法和编程技巧，还深入探讨了数据库对象管理、性能优化、测试调试等多个方面，为Oracle初学者提供了一站式的开发指南。无论是希望提升数据库技能的专业人士，...
本教程将全面解析PL/SQL的语法和应用，是学习者掌握这一技能的宝贵资料。 一、PL/SQL基本结构 PL/SQL由声明部分、执行部分和异常处理部分组成。声明部分包括变量、常量、游标、记录等的定义；执行部分包含SQL语句和...
本文将讲述 PL/SQL 基础语法、结构和组件、以及如何设计并执行一个 PL/SQL 程序。 PL/SQL 的优点包括： * 高性能的基于事务处理的语言，能运行在任何 ORACLE 环境中，支持所有数据处理命令。 * 支持所有 SQL 数据...
1. **基本语法**：PL/SQL由声明部分、执行部分和异常处理部分组成。声明部分用于定义变量、游标、常量等；执行部分包含SQL语句和PL/SQL控制结构，如循环、条件判断等；异常处理部分用于捕获和处理程序运行时可能出现...
循环语句是PL/SQL编程中用来重复执行一组语句的结构，包括loop、while、for三种形式，它们各自有不同的使用场景和语法。 分支条件语句在PL/SQL中用来根据不同的条件执行不同的代码分支，主要包括CASE表达式和if-...
以下是一些关键的PL/SQL语法点： 1. **声明变量**：在PL/SQL块的声明部分，你可以声明变量，如`DECLARE var_name datatype;`。例如，`DECLARE num NUMBER;` 2. **数据类型**：PL/SQL支持多种数据类型，包括数值...
1. **PL/SQL编程支持**：PL/SQL Developer内置了强大的PL/SQL编辑器，支持语法高亮、自动完成、错误检查等功能，使开发人员能够高效编写PL/SQL代码。此外，还具备代码折叠、查找替换、书签设置等实用功能，提高了...
根据提供的文件信息，我们可以推断出本书主要关注的是Oracle SQL 和 PL/SQL 的掌握与应用。下面将基于这些信息来生成相关的知识点。 ### Oracle SQL 和 PL/SQL 知识点 #### 一、Oracle SQL 概述 1. **定义**：...
1. **基础语法**：PL/SQL是过程化SQL，它包含声明部分、执行部分和异常处理部分。声明部分用于定义变量、常量和游标；执行部分包含了SQL语句和流程控制结构，如循环、条件判断等；异常处理部分则用于捕获和处理运行...
### PL/SQL编程基础知识 #### 一、PL/SQL简介 ...以上内容详细介绍了PL/SQL的基础知识，包括其语法特点、块结构以及变量类型等方面。对于初学者来说，掌握这些基本概念是学习和应用PL/SQL的关键。