关于自动收料,检验,入库的三段trigger代码给大家分享一下
1/收料
CREATE OR REPLACE TRIGGER PO_APPROVED_AUTO_RECEIVE_sx
after UPDATE OF APPROVED_FLAG on po_line_locations_all
REFERENCING
for each row
WHEN (OLD.ship_to_organization_id = 1934
and new.approved_flag ='Y')
declare
v_sysdate DATE := SYSDATE;
v_vendor_id NUMBER;
v_vendor_site_id NUMBER;
--v_vendor_name varchar2(200);
--v_user_id NUMBER;
v_employee_id NUMBER;
v_expected_receipt_date DATE :=SYSDATE;
v_comments varchar2(1000):='XUAN.SHA test';
c_validation_flag VARCHAR2 (1) := 'Y';
v_quantity NUMBER; --:=10;
v_unit_of_measure varchar2(10);
v_item_id NUMBER; --:=38023;
v_po_header_id NUMBER; --:=542;
v_po_line_id NUMBER; --:=523;
v_po_line_location_id NUMBER; --:=474;
--v_org_id NUMBER; --:=104;
v_ship_to_location_id NUMBER; --:=142;
v_deliver_to_location_id NUMBER; --:=142;
V_CREATED_BY NUMBER;
V_LAST_UPDATED_BY NUMBER;
V_SHIP_TO_ORG_ID NUMBER;
V_VENDOR_NAME VARCHAR2(200);
V_ORG_CODE VARCHAR2(20);
V_PO_NO VARCHAR2(20);
v_po_distribution_id NUMBER;
V_GROUP_ID NUMBER;
V_HEADER_INTERFACE_ID NUMBER;
v_transaction_interface_id NUMBER;
v_ITEM_REVISION varchar2(10);
v_item_desc varchar2(200);
v_tax_name varchar2(20);
c_processing_status_code rcv_headers_interface.processing_status_code%TYPE := 'PENDING';
c_receipt_source_code rcv_headers_interface.receipt_source_code%TYPE := 'VENDOR';
c_head_transaction_type rcv_headers_interface.transaction_type%TYPE := 'NEW';
c_line_transaction_type rcv_headers_interface.transaction_type%TYPE := 'RECEIVE';
--c_h_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'RECEIVE';
c_l_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'RECEIVE';
c_processing_mode_code rcv_transactions_interface.processing_mode_code%TYPE := 'BATCH';
c_source_document_code rcv_transactions_interface.source_document_code%TYPE := 'PO';
c_transaction_status_code rcv_transactions_interface.transaction_status_code%TYPE := 'PENDING';
c_destination_type_code rcv_transactions_interface.destination_type_code%TYPE := 'RECEIVING'; --'EXPENSE';
v_interface_source_code varchar2(50):='RCV';
-- local variables here
begin
select item_id,
UNIT_MEAS_LOOKUP_CODE,
ITEM_REVISION,
item_description into v_item_id,v_unit_of_measure,v_ITEM_REVISION,v_item_desc
from po_lines_all
where po_line_id = :new.po_line_id;
--if v_item_id in (391716,392294) then
--v_user_id = fnd_global.user_id;
v_quantity :=:new.quantity;
v_po_header_id :=:new.po_header_id;
v_po_line_id := :new.po_line_id;
v_po_line_location_id := :new.line_location_id;
--v_org_id := :new.org_id;
v_ship_to_location_id := :new.ship_to_location_id;
v_deliver_to_location_id := :new.ship_to_location_id;
V_CREATED_BY :=:NEW.CREATED_BY;
V_LAST_UPDATED_BY :=:NEW.LAST_UPDATED_BY;
V_SHIP_TO_ORG_ID :=:NEW.SHIP_TO_ORGANIZATION_ID;
SELECT PO_DISTRIBUTION_ID INTO v_po_distribution_id
FROM PO_DISTRIBUTIONS_ALL
WHERE LINE_LOCATION_ID =:NEW.LINE_LOCATION_ID;
SELECT rcv_headers_interface_s.NEXTVAL INTO V_HEADER_INTERFACE_ID FROM DUAL;
SELECT rcv_interface_groups_s.NEXTVAL INTO V_GROUP_ID FROM DUAL;
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL INTO v_transaction_interface_id FROM DUAL;
--------------------取PO_HEADER資料-----------
select vendor_id,
vendor_site_id,
agent_id,
CREATED_BY,
SEGMENT1
into
v_vendor_id,
v_vendor_site_id,
v_employee_id,
V_CREATED_BY,
V_PO_NO
from po_headers_all
where po_header_id = :new.po_header_id;
SELECT ORGANIZATION_CODE INTO V_ORG_CODE FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = V_SHIP_TO_ORG_ID;
select name into v_tax_name from ap_tax_codes_all
where tax_id = :new.tax_code_id;
--------------------取PO_LINE資料-----------
------------------取VENDOR NAME-------
SELECT VENDOR_NAME INTO V_VENDOR_NAME FROM PO_VENDORS
WHERE VENDOR_ID = v_vendor_id;
if :new.APPROVED_FLAG <>
ld.APPROVED_FLAG
and :new.approved_flag ='Y'
andld.org_id = 911
AND :new.QUANTITY - :new.QUANTITY_RECEIVED - :new.QUANTITY_CANCELLED >0
then
INSERT INTO rcv_headers_interface
(header_interface_id
,group_id
,auto_transact_code
,processing_status_code
,receipt_source_code
,transaction_type
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,vendor_id
,ship_to_organization_id
,vendor_site_id
,VENDOR_NAME
,validation_flag
,employee_id
,expected_receipt_date
,comments
--,RECEIPT_NUM
--,SHIPMENT_NUM
)
VALUES ( V_HEADER_INTERFACE_ID--header_interface_id
,v_group_id--group_id
,c_l_auto_transact_code---RECEIVE
,c_processing_status_code
,c_receipt_source_code
,c_head_transaction_type
,v_sysdate --last_update_date
,V_CREATED_BY--last_update_by
,0 --last_update_login
,v_sysdate --creation_date
,V_CREATED_BY--created_by
,v_vendor_id
,V_SHIP_TO_ORG_ID
,v_vendor_site_id
,V_VENDOR_NAME
,c_validation_flag
,v_employee_id
,v_expected_receipt_date
,v_comments
);
INSERT INTO rcv_transactions_interface
(interface_transaction_id
,header_interface_id
,group_id
,interface_source_code
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,transaction_type
,transaction_date
,transaction_status_code
,processing_status_code
,processing_mode_code
,quantity
,unit_of_measure
,item_id
--,item_description
,auto_transact_code
,receipt_source_code
,vendor_id
,vendor_site_id
,source_document_code
,po_header_id
,po_line_id
,po_line_location_id
,po_distribution_id
,validation_flag
,ship_to_location_id
,to_organization_id
--,expected_receipt_date
,employee_id
,destination_type_code
--,deliver_to_person_id
,deliver_to_location_id
,expected_receipt_date
,to_organization_code
,DOCUMENT_NUM
,ITEM_REVISION
,item_description
,tax_name
)
VALUES (v_transaction_interface_id --interface_transaction_id
,v_header_interface_id --header_interface_id
,v_group_id --group_id
,v_interface_source_code-----RCV
,v_sysdate --last_update_date
,V_LAST_UPDATED_BY --last_update_by
,0--,V_LAST_UPDATED_BY --last_update_login
,sysdate --creation_date
,V_CREATED_BY --created_by
,c_line_transaction_type---RECEIVE
,v_sysdate --transaction_date
,c_transaction_status_code---PENDING
,c_processing_status_code---PENDING
,c_processing_mode_code---BATCH
,v_quantity
,v_unit_of_measure
,v_item_id
--,v_item_description
,c_l_auto_transact_code----RECEIVE
,c_receipt_source_code----VENDOR
,v_vendor_id
,v_vendor_site_id
,c_source_document_code----PO
,v_po_header_id
,v_po_line_id
,v_po_line_location_id
,v_po_distribution_id
,c_validation_flag------------Y
,v_ship_to_location_id
,v_ship_to_ORG_id
,v_employee_id
,c_destination_type_code-----------RECEIVING
,v_deliver_to_location_id
,sysdate
,v_org_code
,v_po_no
,v_ITEM_REVISION
,v_item_desc
,v_tax_name
--,3547071
--,3829518
);
--end if;
END IF;
END;
--end PO_APPROVED_AUTO_RECEIVE_SX;
2/检验
CREATE OR REPLACE TRIGGER PO_RECEIVE_AUTO_ACCPET_sx
after insert on rcv_transactions
--REFERENCING
for each row
WHEN (new.LAST_UPDATE_LOGIN = 0
and new.TRANSACTION_TYPE ='RECEIVE'
and new.organization_id = 1934)
declare
v_item_id NUMBER;
v_item_desc varchar2(200);
v_item_rev varchar2(10);
c_processing_status_code rcv_headers_interface.processing_status_code%TYPE := 'PENDING';
c_receipt_source_code rcv_headers_interface.receipt_source_code%TYPE := 'VENDOR';
--c_source_document_code rcv_transactions_interface.source_document_code%TYPE := 'PO';
c_transaction_status_code rcv_transactions_interface.transaction_status_code%TYPE := 'PENDING';
--c_destination_type_code rcv_transactions_interface.destination_type_code%TYPE := 'INVENTORY';
--c_l_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'ACCEPT';
begin
select item_id,item_description,ITEM_REVISION into v_item_id,v_item_desc,v_item_rev from po_lines_all
where po_line_id =:new.po_line_id;
--if v_item_id = 391716 then--------42.52J03GA01
/*INSERT INTO rcv_transactions_interface
(interface_transaction_id
,group_id
,interface_source_code
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,transaction_type
,transaction_date
,transaction_status_code
,processing_status_code
,processing_mode_code
,quantity
,unit_of_measure
,item_id
,receipt_source_code
,vendor_id
,vendor_site_id
,source_document_code
,po_header_id
,po_line_id
,po_line_location_id
,po_distribution_id
--,validation_flag
--,ship_to_location_id
,to_organization_id
,employee_id
,destination_type_code
,PARENT_TRANSACTION_ID
,SHIPMENT_HEADER_ID
,SHIPMENT_LINE_ID
,PRIMARY_QUANTITY
,PRIMARY_UNIT_OF_MEASURE
,ROUTING_HEADER_ID
,PO_REVISION_NUM
,PO_UNIT_PRICE
,CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,INSPECTION_STATUS_CODE
,LOCATION_ID
,DESTINATION_CONTEXT
,USE_MTL_LOT
,USE_MTL_SERIAL
,auto_transact_code
)*/
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
--category_id,
quantity,
unit_of_measure,
interface_source_code,
item_id,
item_revision,
uom_code,
employee_id,
shipment_header_id,
shipment_line_id,
primary_quantity,
primary_unit_of_measure,
receipt_source_code,
vendor_id,
vendor_site_id,
to_organization_id,
routing_header_id,
routing_step_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_unit_price,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
po_distribution_id,
inspection_status_code,
--inspection_quality_code,
destination_type_code,
--deliver_to_person_id,
location_id,
deliver_to_location_id,
--subinventory,
--attribute1,
--attribute2,
--attribute3,
--attribute5,
--attribute6,
--reason_id,
--destination_context,
use_mtl_lot,
use_mtl_serial,
validation_flag,
item_description,
AUTO_TRANSACT_CODE
--oe_order_header_id,
--oe_order_line_id,
--customer_id
)
VALUES (rcv_transactions_interface_s.NEXTVAL --interface_transaction_id
,rcv_interface_groups_s.NEXTVAL --group_id
--,'RCV'
--,sysdate --last_update_date
--last_update_by
,sysdate
,:new.LAST_UPDATED_BY--,V_LAST_UPDATED_BY
,sysdate --creation_date
,:new.created_by --created_by
,:new.last_update_login--last_update_login
,'ACCEPT'--transaction_type,
,sysdate-- transaction_date,
,c_processing_status_code-- rocessing_status_code,'PENDING'
,'BATCH'-- processing_mode_code,
,c_transaction_status_code-- transaction_status_code,PENDING
,:new.quantity-- quantity,
,:new.unit_of_measure-- unit_of_measure,
,'RCV' -- interface_source_code,
,v_item_id-- item_id,
,v_item_rev
,:new.UOM_CODE-- uom_code,
,:new.EMPLOYEE_ID-- employee_id,
,:new.shipment_header_id-- shipment_header_id,
,:new.shipment_line_id-- shipment_line_id,
,:new.primary_quantity-- primary_quantity,
,:new.primary_unit_of_measure-- primary_unit_of_measure,
,c_receipt_source_code-- receipt_source_code,
,:new.vendor_id-- vendor_id,
,:new.vendor_site_id-- vendor_site_id,
,:new.organization_id-- to_organization_id,
,:new.routing_header_id-- routing_header_id,
,:new.routing_step_id-- routing_step_id,
,'PO'-- source_document_code,
,:new.transaction_id-- parent_transaction_id,
,:new.po_header_id-- po_header_id,
,:new.po_line_id-- po_line_id,
,:new.po_line_location_id-- po_line_location_id,
,:new.PO_UNIT_PRICE-- po_unit_price,
,:new.CURRENCY_CODE-- currency_code,
,:new.CURRENCY_CONVERSION_TYPE-- currency_conversion_type,
,:new.CURRENCY_CONVERSION_RATE-- currency_conversion_rate,
,:new.CURRENCY_CONVERSION_DATE-- currency_conversion_date,
,:new.po_distribution_id-- po_distribution_id,
,'ACCEPTED'-- inspection_status_code,
--inspection_quality_code,
,'RECEIVING'-- destination_type_code,
--deliver_to_person_id,
,:new.LOCATION_ID-- location_id,
,:new.DELIVER_TO_LOCATION_ID-- deliver_to_location_id,
--subinventory,
--attribute1,
---attribute2,
--attribute3,
--attribute5,
--attribute6,
--reason_id,
--destination_context,
,2-- use_mtl_lot,
,1 -- use_mtl_serial,
,'Y'-- validation_flag,
,v_item_desc
,'RECEIVE' );
/*--,v_item_description
--,c_l_auto_transact_code
,c_source_document_code
,:new.employee_id
,c_destination_type_code
,:new.shipment_header_id
,:new.shipment_line_id
,:new.PRIMARY_QUANTITY
,:new.PRIMARY_UNIT_OF_MEASURE
,:new.ROUTING_HEADER_ID
,:new.PO_REVISION_NUM
,c_l_auto_transact_code
);*/
--end if;
end PO_RECEIVE_AUTO_ACCPET_sx;
3/入库
CREATE OR REPLACE TRIGGER po_receive_auto_delivery_sx
after insert on rcv_transactions
for each row
WHEN ( new.organization_id = 1934
and new.transaction_type ='ACCEPT')
Declare
v_interface_transaction_id number;
v_group_id number;
v_shipment_header_id number;
v_shipment_line_id number;
v_line_location_id number;
v_po_distribution_id number;
--v_header_interface_id number;
v_deliver_to_person_id number;
l_request_id number;
l_request_id2 number;
v_destination_type_code varchar2(100);
v_expected_receipt_date date;
v_country_of_origin_code varchar2(100);
v_lot_control number;
v_use_mtl_serial number;
v_rcv_qty number;
--v_trans_qty number;
l_phase varchar2(10);
l_status varchar2(10);
l_dev_phase varchar2(10);
l_dev_status varchar2(10);
l_message varchar2(10);
l_request_lauch1_status boolean;
v_interface_id number;
v_item_id number;
v_UNIT_OF_MEASURE varchar2(20);
v_po_no varchar2(20);
v_location_id number;
Begin
select item_id,unit_meas_lookup_code into v_item_id,v_UNIT_OF_MEASURE from po_lines_all
where po_line_id =:new.po_line_id;
--if v_item_id = 391716 then --------42.52J03GA01測試此料自動入庫
select segment1 into v_po_no from po_headers_all where po_header_id = :new.po_header_id;
Select rcv_transactions_interface_s.nextval into v_interface_transaction_id from dual;
Select RCV_INTERFACE_GROUPS_S.nextval into v_group_id from dual;
select rcv_shipment_headers_s.nextval into v_shipment_header_id from dual;
select rcv_shipment_lines_s.nextval into v_shipment_line_id from dual;
--select rcv_headers_interface_s.nextval into v_header_interface_id from dual;
select MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL into v_interface_id from dual;
select item_id,unit_meas_lookup_code into v_item_id,v_UNIT_OF_MEASURE from po_lines_all
where po_line_id =:new.po_line_id;
select location_id into v_location_id from hr_locations_all
where inventory_ORGANIZATION_id = :new.organization_id;
Begin
select msib.lot_control_code
into v_lot_control
from mtl_system_items_b msib
where msib.inventory_item_id = v_item_id
and msib.organization_id = :new.organization_id;--HUTS
Exception when others then
v_lot_control := null;
End;
If v_lot_control is null then
v_use_mtl_serial := null;
else
v_use_mtl_serial := 1;
End if;
--Get po line location informations
Begin
select plla.line_location_id,
plla.need_by_date,
plla.country_of_origin_code
into v_line_location_id,
v_expected_receipt_date,
v_country_of_origin_code
from po_line_locations_all plla
where plla.po_header_id = :new.po_header_id
and plla.po_line_id = :new.po_line_id
and rownum = 1;
Exception when others then
v_line_location_id := null;
v_expected_receipt_date := null;
v_country_of_origin_code := null;
End;
--Get po_distribution_id
Begin
select pda.po_distribution_id ,
pda.destination_type_code,
pda.deliver_to_person_id
into v_po_distribution_id ,
v_destination_type_code,
v_deliver_to_person_id
from po_distributions_all pda
where pda.po_header_id = :new.po_header_id
and pda.po_line_id = :new.po_line_id
and rownum = 1;
Exception when others then
v_po_distribution_id := null;
v_destination_type_code := null;
End;
If :new.quantity > 0 then
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIP_TO_LOCATION_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
SOURCE_DOCUMENT_CODE,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
SUBINVENTORY,
locator_id,
--HEADER_INTERFACE_ID,
DOCUMENT_NUM,
TO_ORGANIZATION_ID,
VALIDATION_FLAG,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
parent_transaction_id
)
Values
(
v_interface_transaction_id,--INTERFACE_TRANSACTION_ID,
v_group_id,--GROUP_ID,
sysdate,--LAST_UPDATE_DATE,
:new.last_updated_by,--LAST_UPDATED_BY,
sysdate,--CREATION_DATE,
:new.created_by,--CREATED_BY,
:new.last_update_login,--LAST_UPDATE_LOGIN,
'DELIVER',--TRANSACTION_TYPE,
sysdate,--TRANSACTION_DATE,
'PENDING',--PROCESSING_STATUS_CODE,
'BATCH',--PROCESSING_MODE_CODE,
'PENDING',--TRANSACTION_STATUS_CODE,
:new.quantity,--QUANTITY,
v_UNIT_OF_MEASURE,--UNIT_OF_MEASURE,
v_item_id,--ITEM_ID,
:new.employee_id,--EMPLOYEE_ID,
'DELIVER',--AUTO_TRANSACT_CODE,
:new.location_id,--SHIP_TO_LOCATION_ID,
'VENDOR',--RECEIPT_SOURCE_CODE,
:new.vendor_id,--VENDOR_ID,
'PO',--SOURCE_DOCUMENT_CODE,
:new.po_header_id,--PO_HEADER_ID,
:new.PO_LINE_ID,--PO_LINE_ID,
v_line_location_id,--PO_LINE_LOCATION_ID,
v_destination_type_code,--DESTINATION_TYPE_CODE,
v_DELIVER_TO_PERSON_ID,--DELIVER_TO_PERSON_ID,
v_location_id,--LOCATION_ID,
v_location_id,--DELIVER_TO_LOCATION_ID,
'3MR1',--SUBINVENTORY,
206535,--locator_id,
--:new.header_interface_id,--HEADER_INTERFACE_ID,
v_po_no,--DOCUMENT_NUM,
:NEW.ORGANIZATION_ID,--TO_ORGANIZATION_ID,
'Y',--VALIDATION_FLAG
:new.SHIPMENT_header_ID,
:new.SHIPMENT_LINE_ID,
:new.transaction_id
);
INSERT INTO mtl_transaction_lots_interface
(
TRANSACTION_INTERFACE_ID,
source_code,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
--SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID,
process_flag,
source_line_id
)
VALUES
(v_interface_id,--TRANSACTION_INTERFACE_ID
'PO',--ORIGINATION_TYPE
SYSDATE, --LAST_UPDATE_DATE
:new.LAST_UPDATED_BY, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
:new.created_by, --CREATED_BY
:new.last_update_login, --LAST_UPDATE_LOGIN
'0', --LOT_NUMBER
:new.quantity, --TRANSACTION_QUANTITY
:new.primary_quantity, --PRIMARY_QUANTITY
--0,--MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
v_interface_transaction_id, --PRODUCT_TRANSACTION_ID
1,
-1
);
--End if;
end if;
End;
CREATE OR REPLACE TRIGGER PO_APPROVED_AUTO_RECEIVE_sx
after UPDATE OF APPROVED_FLAG on po_line_locations_all
REFERENCING
for each row
WHEN (OLD.ship_to_organization_id = 1934
and new.approved_flag ='Y')
declare
v_sysdate DATE := SYSDATE;
v_vendor_id NUMBER;
v_vendor_site_id NUMBER;
--v_vendor_name varchar2(200);
--v_user_id NUMBER;
v_employee_id NUMBER;
v_expected_receipt_date DATE :=SYSDATE;
v_comments varchar2(1000):='XUAN.SHA test';
c_validation_flag VARCHAR2 (1) := 'Y';
v_quantity NUMBER; --:=10;
v_unit_of_measure varchar2(10);
v_item_id NUMBER; --:=38023;
v_po_header_id NUMBER; --:=542;
v_po_line_id NUMBER; --:=523;
v_po_line_location_id NUMBER; --:=474;
--v_org_id NUMBER; --:=104;
v_ship_to_location_id NUMBER; --:=142;
v_deliver_to_location_id NUMBER; --:=142;
V_CREATED_BY NUMBER;
V_LAST_UPDATED_BY NUMBER;
V_SHIP_TO_ORG_ID NUMBER;
V_VENDOR_NAME VARCHAR2(200);
V_ORG_CODE VARCHAR2(20);
V_PO_NO VARCHAR2(20);
v_po_distribution_id NUMBER;
V_GROUP_ID NUMBER;
V_HEADER_INTERFACE_ID NUMBER;
v_transaction_interface_id NUMBER;
v_ITEM_REVISION varchar2(10);
v_item_desc varchar2(200);
v_tax_name varchar2(20);
c_processing_status_code rcv_headers_interface.processing_status_code%TYPE := 'PENDING';
c_receipt_source_code rcv_headers_interface.receipt_source_code%TYPE := 'VENDOR';
c_head_transaction_type rcv_headers_interface.transaction_type%TYPE := 'NEW';
c_line_transaction_type rcv_headers_interface.transaction_type%TYPE := 'RECEIVE';
--c_h_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'RECEIVE';
c_l_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'RECEIVE';
c_processing_mode_code rcv_transactions_interface.processing_mode_code%TYPE := 'BATCH';
c_source_document_code rcv_transactions_interface.source_document_code%TYPE := 'PO';
c_transaction_status_code rcv_transactions_interface.transaction_status_code%TYPE := 'PENDING';
c_destination_type_code rcv_transactions_interface.destination_type_code%TYPE := 'RECEIVING'; --'EXPENSE';
v_interface_source_code varchar2(50):='RCV';
-- local variables here
begin
select item_id,
UNIT_MEAS_LOOKUP_CODE,
ITEM_REVISION,
item_description into v_item_id,v_unit_of_measure,v_ITEM_REVISION,v_item_desc
from po_lines_all
where po_line_id = :new.po_line_id;
--if v_item_id in (391716,392294) then
--v_user_id = fnd_global.user_id;
v_quantity :=:new.quantity;
v_po_header_id :=:new.po_header_id;
v_po_line_id := :new.po_line_id;
v_po_line_location_id := :new.line_location_id;
--v_org_id := :new.org_id;
v_ship_to_location_id := :new.ship_to_location_id;
v_deliver_to_location_id := :new.ship_to_location_id;
V_CREATED_BY :=:NEW.CREATED_BY;
V_LAST_UPDATED_BY :=:NEW.LAST_UPDATED_BY;
V_SHIP_TO_ORG_ID :=:NEW.SHIP_TO_ORGANIZATION_ID;
SELECT PO_DISTRIBUTION_ID INTO v_po_distribution_id
FROM PO_DISTRIBUTIONS_ALL
WHERE LINE_LOCATION_ID =:NEW.LINE_LOCATION_ID;
SELECT rcv_headers_interface_s.NEXTVAL INTO V_HEADER_INTERFACE_ID FROM DUAL;
SELECT rcv_interface_groups_s.NEXTVAL INTO V_GROUP_ID FROM DUAL;
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL INTO v_transaction_interface_id FROM DUAL;
--------------------取PO_HEADER資料-----------
select vendor_id,
vendor_site_id,
agent_id,
CREATED_BY,
SEGMENT1
into
v_vendor_id,
v_vendor_site_id,
v_employee_id,
V_CREATED_BY,
V_PO_NO
from po_headers_all
where po_header_id = :new.po_header_id;
SELECT ORGANIZATION_CODE INTO V_ORG_CODE FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = V_SHIP_TO_ORG_ID;
select name into v_tax_name from ap_tax_codes_all
where tax_id = :new.tax_code_id;
--------------------取PO_LINE資料-----------
------------------取VENDOR NAME-------
SELECT VENDOR_NAME INTO V_VENDOR_NAME FROM PO_VENDORS
WHERE VENDOR_ID = v_vendor_id;
if :new.APPROVED_FLAG <>
ld.APPROVED_FLAGand :new.approved_flag ='Y'
and
ld.org_id = 911AND :new.QUANTITY - :new.QUANTITY_RECEIVED - :new.QUANTITY_CANCELLED >0
then
INSERT INTO rcv_headers_interface
(header_interface_id
,group_id
,auto_transact_code
,processing_status_code
,receipt_source_code
,transaction_type
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,vendor_id
,ship_to_organization_id
,vendor_site_id
,VENDOR_NAME
,validation_flag
,employee_id
,expected_receipt_date
,comments
--,RECEIPT_NUM
--,SHIPMENT_NUM
)
VALUES ( V_HEADER_INTERFACE_ID--header_interface_id
,v_group_id--group_id
,c_l_auto_transact_code---RECEIVE
,c_processing_status_code
,c_receipt_source_code
,c_head_transaction_type
,v_sysdate --last_update_date
,V_CREATED_BY--last_update_by
,0 --last_update_login
,v_sysdate --creation_date
,V_CREATED_BY--created_by
,v_vendor_id
,V_SHIP_TO_ORG_ID
,v_vendor_site_id
,V_VENDOR_NAME
,c_validation_flag
,v_employee_id
,v_expected_receipt_date
,v_comments
);
INSERT INTO rcv_transactions_interface
(interface_transaction_id
,header_interface_id
,group_id
,interface_source_code
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,transaction_type
,transaction_date
,transaction_status_code
,processing_status_code
,processing_mode_code
,quantity
,unit_of_measure
,item_id
--,item_description
,auto_transact_code
,receipt_source_code
,vendor_id
,vendor_site_id
,source_document_code
,po_header_id
,po_line_id
,po_line_location_id
,po_distribution_id
,validation_flag
,ship_to_location_id
,to_organization_id
--,expected_receipt_date
,employee_id
,destination_type_code
--,deliver_to_person_id
,deliver_to_location_id
,expected_receipt_date
,to_organization_code
,DOCUMENT_NUM
,ITEM_REVISION
,item_description
,tax_name
)
VALUES (v_transaction_interface_id --interface_transaction_id
,v_header_interface_id --header_interface_id
,v_group_id --group_id
,v_interface_source_code-----RCV
,v_sysdate --last_update_date
,V_LAST_UPDATED_BY --last_update_by
,0--,V_LAST_UPDATED_BY --last_update_login
,sysdate --creation_date
,V_CREATED_BY --created_by
,c_line_transaction_type---RECEIVE
,v_sysdate --transaction_date
,c_transaction_status_code---PENDING
,c_processing_status_code---PENDING
,c_processing_mode_code---BATCH
,v_quantity
,v_unit_of_measure
,v_item_id
--,v_item_description
,c_l_auto_transact_code----RECEIVE
,c_receipt_source_code----VENDOR
,v_vendor_id
,v_vendor_site_id
,c_source_document_code----PO
,v_po_header_id
,v_po_line_id
,v_po_line_location_id
,v_po_distribution_id
,c_validation_flag------------Y
,v_ship_to_location_id
,v_ship_to_ORG_id
,v_employee_id
,c_destination_type_code-----------RECEIVING
,v_deliver_to_location_id
,sysdate
,v_org_code
,v_po_no
,v_ITEM_REVISION
,v_item_desc
,v_tax_name
--,3547071
--,3829518
);
--end if;
END IF;
END;
--end PO_APPROVED_AUTO_RECEIVE_SX;
2/检验
CREATE OR REPLACE TRIGGER PO_RECEIVE_AUTO_ACCPET_sx
after insert on rcv_transactions
--REFERENCING
for each row
WHEN (new.LAST_UPDATE_LOGIN = 0
and new.TRANSACTION_TYPE ='RECEIVE'
and new.organization_id = 1934)
declare
v_item_id NUMBER;
v_item_desc varchar2(200);
v_item_rev varchar2(10);
c_processing_status_code rcv_headers_interface.processing_status_code%TYPE := 'PENDING';
c_receipt_source_code rcv_headers_interface.receipt_source_code%TYPE := 'VENDOR';
--c_source_document_code rcv_transactions_interface.source_document_code%TYPE := 'PO';
c_transaction_status_code rcv_transactions_interface.transaction_status_code%TYPE := 'PENDING';
--c_destination_type_code rcv_transactions_interface.destination_type_code%TYPE := 'INVENTORY';
--c_l_auto_transact_code rcv_headers_interface.auto_transact_code%TYPE := 'ACCEPT';
begin
select item_id,item_description,ITEM_REVISION into v_item_id,v_item_desc,v_item_rev from po_lines_all
where po_line_id =:new.po_line_id;
--if v_item_id = 391716 then--------42.52J03GA01
/*INSERT INTO rcv_transactions_interface
(interface_transaction_id
,group_id
,interface_source_code
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,transaction_type
,transaction_date
,transaction_status_code
,processing_status_code
,processing_mode_code
,quantity
,unit_of_measure
,item_id
,receipt_source_code
,vendor_id
,vendor_site_id
,source_document_code
,po_header_id
,po_line_id
,po_line_location_id
,po_distribution_id
--,validation_flag
--,ship_to_location_id
,to_organization_id
,employee_id
,destination_type_code
,PARENT_TRANSACTION_ID
,SHIPMENT_HEADER_ID
,SHIPMENT_LINE_ID
,PRIMARY_QUANTITY
,PRIMARY_UNIT_OF_MEASURE
,ROUTING_HEADER_ID
,PO_REVISION_NUM
,PO_UNIT_PRICE
,CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,INSPECTION_STATUS_CODE
,LOCATION_ID
,DESTINATION_CONTEXT
,USE_MTL_LOT
,USE_MTL_SERIAL
,auto_transact_code
)*/
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
--category_id,
quantity,
unit_of_measure,
interface_source_code,
item_id,
item_revision,
uom_code,
employee_id,
shipment_header_id,
shipment_line_id,
primary_quantity,
primary_unit_of_measure,
receipt_source_code,
vendor_id,
vendor_site_id,
to_organization_id,
routing_header_id,
routing_step_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_unit_price,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
po_distribution_id,
inspection_status_code,
--inspection_quality_code,
destination_type_code,
--deliver_to_person_id,
location_id,
deliver_to_location_id,
--subinventory,
--attribute1,
--attribute2,
--attribute3,
--attribute5,
--attribute6,
--reason_id,
--destination_context,
use_mtl_lot,
use_mtl_serial,
validation_flag,
item_description,
AUTO_TRANSACT_CODE
--oe_order_header_id,
--oe_order_line_id,
--customer_id
)
VALUES (rcv_transactions_interface_s.NEXTVAL --interface_transaction_id
,rcv_interface_groups_s.NEXTVAL --group_id
--,'RCV'
--,sysdate --last_update_date
--last_update_by
,sysdate
,:new.LAST_UPDATED_BY--,V_LAST_UPDATED_BY
,sysdate --creation_date
,:new.created_by --created_by
,:new.last_update_login--last_update_login
,'ACCEPT'--transaction_type,
,sysdate-- transaction_date,
,c_processing_status_code-- rocessing_status_code,'PENDING'
,'BATCH'-- processing_mode_code,
,c_transaction_status_code-- transaction_status_code,PENDING
,:new.quantity-- quantity,
,:new.unit_of_measure-- unit_of_measure,
,'RCV' -- interface_source_code,
,v_item_id-- item_id,
,v_item_rev
,:new.UOM_CODE-- uom_code,
,:new.EMPLOYEE_ID-- employee_id,
,:new.shipment_header_id-- shipment_header_id,
,:new.shipment_line_id-- shipment_line_id,
,:new.primary_quantity-- primary_quantity,
,:new.primary_unit_of_measure-- primary_unit_of_measure,
,c_receipt_source_code-- receipt_source_code,
,:new.vendor_id-- vendor_id,
,:new.vendor_site_id-- vendor_site_id,
,:new.organization_id-- to_organization_id,
,:new.routing_header_id-- routing_header_id,
,:new.routing_step_id-- routing_step_id,
,'PO'-- source_document_code,
,:new.transaction_id-- parent_transaction_id,
,:new.po_header_id-- po_header_id,
,:new.po_line_id-- po_line_id,
,:new.po_line_location_id-- po_line_location_id,
,:new.PO_UNIT_PRICE-- po_unit_price,
,:new.CURRENCY_CODE-- currency_code,
,:new.CURRENCY_CONVERSION_TYPE-- currency_conversion_type,
,:new.CURRENCY_CONVERSION_RATE-- currency_conversion_rate,
,:new.CURRENCY_CONVERSION_DATE-- currency_conversion_date,
,:new.po_distribution_id-- po_distribution_id,
,'ACCEPTED'-- inspection_status_code,
--inspection_quality_code,
,'RECEIVING'-- destination_type_code,
--deliver_to_person_id,
,:new.LOCATION_ID-- location_id,
,:new.DELIVER_TO_LOCATION_ID-- deliver_to_location_id,
--subinventory,
--attribute1,
---attribute2,
--attribute3,
--attribute5,
--attribute6,
--reason_id,
--destination_context,
,2-- use_mtl_lot,
,1 -- use_mtl_serial,
,'Y'-- validation_flag,
,v_item_desc
,'RECEIVE' );
/*--,v_item_description
--,c_l_auto_transact_code
,c_source_document_code
,:new.employee_id
,c_destination_type_code
,:new.shipment_header_id
,:new.shipment_line_id
,:new.PRIMARY_QUANTITY
,:new.PRIMARY_UNIT_OF_MEASURE
,:new.ROUTING_HEADER_ID
,:new.PO_REVISION_NUM
,c_l_auto_transact_code
);*/
--end if;
end PO_RECEIVE_AUTO_ACCPET_sx;
3/入库
CREATE OR REPLACE TRIGGER po_receive_auto_delivery_sx
after insert on rcv_transactions
for each row
WHEN ( new.organization_id = 1934
and new.transaction_type ='ACCEPT')
Declare
v_interface_transaction_id number;
v_group_id number;
v_shipment_header_id number;
v_shipment_line_id number;
v_line_location_id number;
v_po_distribution_id number;
--v_header_interface_id number;
v_deliver_to_person_id number;
l_request_id number;
l_request_id2 number;
v_destination_type_code varchar2(100);
v_expected_receipt_date date;
v_country_of_origin_code varchar2(100);
v_lot_control number;
v_use_mtl_serial number;
v_rcv_qty number;
--v_trans_qty number;
l_phase varchar2(10);
l_status varchar2(10);
l_dev_phase varchar2(10);
l_dev_status varchar2(10);
l_message varchar2(10);
l_request_lauch1_status boolean;
v_interface_id number;
v_item_id number;
v_UNIT_OF_MEASURE varchar2(20);
v_po_no varchar2(20);
v_location_id number;
Begin
select item_id,unit_meas_lookup_code into v_item_id,v_UNIT_OF_MEASURE from po_lines_all
where po_line_id =:new.po_line_id;
--if v_item_id = 391716 then --------42.52J03GA01測試此料自動入庫
select segment1 into v_po_no from po_headers_all where po_header_id = :new.po_header_id;
Select rcv_transactions_interface_s.nextval into v_interface_transaction_id from dual;
Select RCV_INTERFACE_GROUPS_S.nextval into v_group_id from dual;
select rcv_shipment_headers_s.nextval into v_shipment_header_id from dual;
select rcv_shipment_lines_s.nextval into v_shipment_line_id from dual;
--select rcv_headers_interface_s.nextval into v_header_interface_id from dual;
select MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL into v_interface_id from dual;
select item_id,unit_meas_lookup_code into v_item_id,v_UNIT_OF_MEASURE from po_lines_all
where po_line_id =:new.po_line_id;
select location_id into v_location_id from hr_locations_all
where inventory_ORGANIZATION_id = :new.organization_id;
Begin
select msib.lot_control_code
into v_lot_control
from mtl_system_items_b msib
where msib.inventory_item_id = v_item_id
and msib.organization_id = :new.organization_id;--HUTS
Exception when others then
v_lot_control := null;
End;
If v_lot_control is null then
v_use_mtl_serial := null;
else
v_use_mtl_serial := 1;
End if;
--Get po line location informations
Begin
select plla.line_location_id,
plla.need_by_date,
plla.country_of_origin_code
into v_line_location_id,
v_expected_receipt_date,
v_country_of_origin_code
from po_line_locations_all plla
where plla.po_header_id = :new.po_header_id
and plla.po_line_id = :new.po_line_id
and rownum = 1;
Exception when others then
v_line_location_id := null;
v_expected_receipt_date := null;
v_country_of_origin_code := null;
End;
--Get po_distribution_id
Begin
select pda.po_distribution_id ,
pda.destination_type_code,
pda.deliver_to_person_id
into v_po_distribution_id ,
v_destination_type_code,
v_deliver_to_person_id
from po_distributions_all pda
where pda.po_header_id = :new.po_header_id
and pda.po_line_id = :new.po_line_id
and rownum = 1;
Exception when others then
v_po_distribution_id := null;
v_destination_type_code := null;
End;
If :new.quantity > 0 then
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIP_TO_LOCATION_ID,
RECEIPT_SOURCE_CODE,
VENDOR_ID,
SOURCE_DOCUMENT_CODE,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
SUBINVENTORY,
locator_id,
--HEADER_INTERFACE_ID,
DOCUMENT_NUM,
TO_ORGANIZATION_ID,
VALIDATION_FLAG,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
parent_transaction_id
)
Values
(
v_interface_transaction_id,--INTERFACE_TRANSACTION_ID,
v_group_id,--GROUP_ID,
sysdate,--LAST_UPDATE_DATE,
:new.last_updated_by,--LAST_UPDATED_BY,
sysdate,--CREATION_DATE,
:new.created_by,--CREATED_BY,
:new.last_update_login,--LAST_UPDATE_LOGIN,
'DELIVER',--TRANSACTION_TYPE,
sysdate,--TRANSACTION_DATE,
'PENDING',--PROCESSING_STATUS_CODE,
'BATCH',--PROCESSING_MODE_CODE,
'PENDING',--TRANSACTION_STATUS_CODE,
:new.quantity,--QUANTITY,
v_UNIT_OF_MEASURE,--UNIT_OF_MEASURE,
v_item_id,--ITEM_ID,
:new.employee_id,--EMPLOYEE_ID,
'DELIVER',--AUTO_TRANSACT_CODE,
:new.location_id,--SHIP_TO_LOCATION_ID,
'VENDOR',--RECEIPT_SOURCE_CODE,
:new.vendor_id,--VENDOR_ID,
'PO',--SOURCE_DOCUMENT_CODE,
:new.po_header_id,--PO_HEADER_ID,
:new.PO_LINE_ID,--PO_LINE_ID,
v_line_location_id,--PO_LINE_LOCATION_ID,
v_destination_type_code,--DESTINATION_TYPE_CODE,
v_DELIVER_TO_PERSON_ID,--DELIVER_TO_PERSON_ID,
v_location_id,--LOCATION_ID,
v_location_id,--DELIVER_TO_LOCATION_ID,
'3MR1',--SUBINVENTORY,
206535,--locator_id,
--:new.header_interface_id,--HEADER_INTERFACE_ID,
v_po_no,--DOCUMENT_NUM,
:NEW.ORGANIZATION_ID,--TO_ORGANIZATION_ID,
'Y',--VALIDATION_FLAG
:new.SHIPMENT_header_ID,
:new.SHIPMENT_LINE_ID,
:new.transaction_id
);
INSERT INTO mtl_transaction_lots_interface
(
TRANSACTION_INTERFACE_ID,
source_code,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
--SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID,
process_flag,
source_line_id
)
VALUES
(v_interface_id,--TRANSACTION_INTERFACE_ID
'PO',--ORIGINATION_TYPE
SYSDATE, --LAST_UPDATE_DATE
:new.LAST_UPDATED_BY, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
:new.created_by, --CREATED_BY
:new.last_update_login, --LAST_UPDATE_LOGIN
'0', --LOT_NUMBER
:new.quantity, --TRANSACTION_QUANTITY
:new.primary_quantity, --PRIMARY_QUANTITY
--0,--MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
v_interface_transaction_id, --PRODUCT_TRANSACTION_ID
1,
-1
);
--End if;
end if;
End;
