八、 应用实例
下面以电算部开发出之程序<<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;