example for receive

最新推荐文章于 2023-12-01 15:36:33 发布

cuandan9874

最新推荐文章于 2023-12-01 15:36:33 发布

阅读量191

点赞数

DECLARE
v_sysdate DATE := SYSDATE;
v_vendor_id NUMBER :=3;
v_vendor_site_id NUMBER :=5;
v_user_id NUMBER :=1095;
v_employee_id NUMBER :=61;
v_expected_receipt_date DATE :=SYSDATE;
v_comments varchar2(1000) :='zhxiang test';
c_validation_flag VARCHAR2 (1) := 'Y';
v_quantity NUMBER :=10;
v_unit_of_measure varchar2(5) :='PCE';
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;

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';

BEGIN
INSERT INTO rcv_headers_interface
(header_interface_id
,group_id
,processing_status_code
,receipt_source_code
,transaction_type
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,vendor_id
,vendor_site_id
,validation_flag
,employee_id
,expected_receipt_date
,comments )
VALUES ( rcv_headers_interface_s.NEXTVAL --header_interface_id
,rcv_interface_groups_s.NEXTVAL--group_id
,c_processing_status_code
,c_receipt_source_code
,c_head_transaction_type
,v_sysdate --last_update_date
,v_user_id --last_update_by
,v_user_id --last_update_login
,v_sysdate --creation_date
,v_user_id --created_by
,v_vendor_id
,v_vendor_site_id
,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
,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
)
VALUES (rcv_transactions_interface_s.NEXTVAL --interface_transaction_id
,rcv_headers_interface_s.CURRVAL --header_interface_id
,rcv_interface_groups_s.CURRVAL --group_id
,v_interface_source_code
,v_sysdate --last_update_date
,v_user_id --last_update_by
,v_user_id --last_update_login
,sysdate --creation_date
,v_user_id --created_by
,c_line_transaction_type
,v_sysdate --transaction_date
,c_transaction_status_code
,c_processing_status_code
,c_processing_mode_code
,v_quantity
,v_unit_of_measure
,v_item_id
--,v_item_description
,c_l_auto_transact_code
,c_receipt_source_code
,v_vendor_id
,v_vendor_site_id
,c_source_document_code
,v_po_header_id
,v_po_line_id
,v_po_line_location_id
,c_validation_flag
,v_ship_to_location_id
,v_org_id
--,v_sysdate
,v_employee_id
,c_destination_type_code
--,v_deliver_to_person_id
,v_deliver_to_location_id
);

COMMIT;
END;

新增資料到 rcv_headers_interface 與 rcv_transactions_interface 之後
需跑一支concurrent program "Receiving Transaction Processor"
才能將interface的資料真正拋入RCV的相關Table.

我也在研究中,以下可以参考
insert into rcv_headers_interface
(header_interface_id , -- RCV_INTERFACE_GROUPS_S.next
group_id , -- po_headers_all.segment1
processing_status_code , -- 'PENDING'
receipt_source_code , -- 'VENDOR'
transaction_type , -- 'NEW'
auto_transact_code , -- 'RECEIVE'
last_update_date , -- sysdate
last_updated_by , -- -1
creation_date , -- sysdate
created_by , -- -1 'receipt_header_id'
vendor_id , --
ship_to_organization_id , --
expected_receipt_date , -- sysdate
validation_flag -- 'Y'
)
values
(RCV_INTERFACE_GROUPS_S.next ,
po_headers_all.segment1,
'PENDING',
'VENDOR',
'NEW',
'RECEIVE',
sysdate,
-1,
sysdate,
-1,
?供应商ID?,
?组织ID?,
sysdate,
'Y'
)

insert into rcv_transactions_interface
(interface_transaction_id , -- RCV_TRANSACTIONS_INTERFACE_S.nextval
group_id , -- rcv_headers_interface.group_id
last_update_date , -- sysdate
last_updated_by , -- -1
creation_date , -- sysdate
created_by , -- -1
transaction_type , -- 'RECEIVE'
transaction_date , -- sysdate
processing_status_code , -- 'PENDING'
processing_mode_code , -- 'BATCH'
transaction_status_code , -- 'PENDING' ? COMPLETED
quantity ,
unit_of_measure , -- '吨'
item_id ,
auto_transact_code , -- 'RECEIVE'
receipt_source_code , -- 'VENDOR'
vendor_id ,
source_document_code , -- 'PO'
po_header_id , -- po_headers_all.po_header_id
po_line_id , -- po_lines_all.po_header_id
expected_receipt_date , -- sysdate
header_interface_id , -- rcv_headers_interface.header_interface_id
validation_flag , -- Y process_flag , -- Y
to_organization_id )
values
(RCV_TRANSACTIONS_INTERFACE_S.nextval,
420100967,
sysdate,
-1,
sysdate,
-1,
'RECEIVE',
sysdate,
'PENDING',
'BATCH',
'PENDING',
4,
'吨',
19938,
'RECEIVE',
'VENDOR',
8612,
'PO',
25505,
66190,
sysdate,
0,
'Y',
66
);