Import Standard Purchase Order (标准ＰＯ订单导入接口)ＢＵＧ集合(1)
作者: moonsoft(http://moonsoft.itpub.net)发表于: 2007.01.26 16:05
分类: 分销 , Oracle基础/数据仓库/BI , EBS
出处: http://moonsoft.itpub.net/post/15182/255753
---------------------------------------------------------------
[心血原创,转载注明]
Import Standard Purchase Order (标准ＰＯ订单导入接口)ＢＵＧ集合(1)
this week , I was puzzled by this PO interface program .Up to now , I find two big bugs in this program . 希望不再发现任何bug in this program .也希望不再有Import Standard Purchase Order (标准ＰＯ订单导入接口)ＢＵＧ集合(2)
Oracle , 原来你的代码也这样地烂......
bug 1:Import Standard Purchase Order , Find 'REJECTED' in po_headers_interface table , But couldn't find records in PO_interface_errors
bug2:ORA-1403 ORA-6512
DECLARE*ERROR at line 1:ORA-01403: no data foundORA-06512: at line 55 bug 1:Import Standard Purchase Order , Find 'REJECTED' in po_headers_interface table , But couldn't find records in PO_interface_errors
我看了PO_DOCS_INTERFACE_SV5,找了两天 ,问题在这里
IF (x_header_processable_flag <> 'Y') THEN -- Bug 3428196
X_progress := '190';
ROLLBACK TO SAVEPOINT header_record_savepoint;
UPDATE po_headers_interface
SET process_code = 'REJECTED'
WHERE interface_header_id = X_headers_interface.interface_header_id;
END IF; -- x_header_processable_flag <> 'Y'
这里没有向po_interface_errors 里面写数据.但是如果完全弄明白x_header_processable_flag 为什么变成了'N'还需要时间把相关地几个包读明白.时间不允许了,后来干脆把line 也导进去,死马当活马医.
解决办法 ,不能只导入PO HEADERS ,如果单独导po headers ,会出现这个错误
如果不可以单独导po headers , oracle 应该在documention 里面注明!可是现在oracle 在.10连接口文档都不加了,shit
bug2:ORA-1403 ORA-6512
DECLARE*ERROR at line 1:ORA-01403: no data foundORA-06512: at line 55 首先骂一下,oracle 不能把错误文件写到output file 里面.output 是不该给用户看的,应该放到log 里面
其次,ora-1403是个极其常见的错误，一般写隐性cursor（select into ）才会出现这个错误，如果写程序连这个都考虑不到，可以说水平比较差，可是这却是oracle写的，Oracle ，让我怎么信仰你？！
解决方法：CauseInvalid term defined in the Vendor or vendor site.
If terms_id or payments terms is not populated, then system try to default the terms id from vendor site and if null then from vendor.SolutionChange the setup for the supplier and Site
Use a Valid Payment Term in the vendor and Vendor Site used in the importReferencesBug 5690677 - ''IMPORT STANDARD PURCHASE ORDERS'' PROGRAM IS THROWING AN ERROR ''ORA-01403
po_headers_interface
po_lines_interface
po_distributions_interface -- :The PO_DISTRIBUTIONS_INTERFACE is used only for standard purchase orders
PO_INTERFACE_ERRORS
--launch the Purchasing Interface Errors Report in Purchasing to view the rows that were not imported
/*the Purchasing Documents Open Interface programs first process a
record from the PO_HEADERS_INTERFACE table. Then, the program processes the
child records in the PO_LINES_INTERFACE table and, for standard purchase
orders, the PO_DISTRIBUTIONS_INTERFACE table, before going on to the next
PO_HEADERS_INTERFACE record. */
--errors :
/*
If an error is found in a header, none of its lines are processed. The Purchasing
Documents Open Interface rolls back the header, does not process its lines, and does
the following:
¡ö Sets the PROCESS_CODE column value to REJECTED in the PO_HEADERS_
INTERFACE table.
¡ö Writes out the record identification number and the details of the error to the
PO_INTERFACE_ERRORS table.
¡ö Begins processing the next header record.
non errors PROCESS_CODE column to ACCEPTED.
when the supplier sends an updated price/sales catalog, the Purchasing Documents Open
Interface sets the PROCESS_CODE column to NOTIFIED for those lines with prices
that exceed your price tolerance.
*/
--concurrent program name : Import Standard Purchase Orders
POXPOPDOI
--parameters :
default buyer
create or update item --required
approval status --required
batch_id
po_headers_interface_s
--vendor
-- get vendors
SELECT v.vendor_name,v.vendor_id,
s.vendor_site_code, s.vendor_site_id
FROM PO_VENDORS v, po_vendor_sites_all s
WHERE v.VENDOR_NAME LIKE '3M Health Care%'
and v.vendor_id= s.vendor_id
-- get employee id (agent id ) person_id 13579
SELECT p.*
FROM per_all_people_f p
WHERE p.full_name LIKE 'SU%'
--get records in
select h.vendor_id, v.vendor_name, vendor_site_id
from po_headers_all h ,
po_vendors v
where h.segment1='4452' and
h.vendor_id=v.vendor_id
/* Formatted on 2007/01/26 13:34 (Formatter Plus v4.8.7) */
INSERT INTO po_headers_interface
(interface_header_id, action, org_id, document_type_code,
po_header_id, vendor_id, vendor_site_code, vendor_doc_num,
creation_date
)
VALUES (po_headers_interface_s.NEXTVAL, 'UPDATE', 83, 'QUOTATION',
55001, 3338, 'NON PRODUCT', 'PWM681',
SYSDATE
);
/* Formatted on 2007/01/26 13:43 (Formatter Plus v4.8.7) */
-- get item
SELECT i.*
FROM mtl_system_items_b i
WHERE i.organization_id = 204 and i.enabled_flag='Y' and i.segment1='10-40W Oil'
/* Formatted on 2007/01/24 15:51 (Formatter Plus v4.8.7) */
INSERT INTO po_headers_interface
(interface_header_id, action, document_type_code, vendor_id,
vendor_site_code, vendor_site_id, effective_date,
expiration_date, vendor_doc_num, org_id, agent_id--new method
,po_header_id, creation_date
)
VALUES (po_headers_interface_s.nextval,'ORIGINAL' , 'STANDARD', 600,
'3M Health Care', 1414, sysdate,
sysdate+720, 4655, 204, 13579,po_headers_s.nextval,sysdate
)
/* Formatted on 2007/01/26 13:41 (Formatter Plus v4.8.7) */
INSERT INTO po_lines_interface
(interface_line_id, interface_header_id,
action, item_id, unit_price, organization_id,
creation_date, sourcing_rule_name, po_line_id
)
VALUES (po_lines_interface_s.NEXTVAL, po_headers_interface_s.CURRVAL,
'ADD', 8063,5.1, 204,
SYSDATE,8063, po_lines_s.nextval
)
/* Formatted on 2007/01/24 16:08 (Formatter Plus v4.8.7) */
SELECT p.process_code, p.*
FROM po_headers_interface p
WHERE p.vendor_id = 600
SELECT l.process_code, l.*
FROM po_lines_interface l ,
po_headers_interface h
WHERE l.interface_header_id= h.interface_header_id
and h.vendor_id=3921
SELECT error_message_name, error_message
FROM po_interface_errors
WHERE request_id=2785782
select p.*
from po_headers_all p
where p.vendor_id=600
/* Formatted on 2007/01/25 17:56 (Formatter Plus v4.8.7) */
DELETE FROM po_headers_interface
WHERE vendor_id =
insert into po_headers_interface (
INTERFACE_HEADER_ID , --r po_headers_interface_s
BATCH_ID ,
INTERFACE_SOURCE_CODE ,
PROCESS_CODE ,
ACTION ,--r
GROUP_CODE ,
ORG_ID ,
DOCUMENT_TYPE_CODE , --r
DOCUMENT_SUBTYPE ,
DOCUMENT_NUM ,
PO_HEADER_ID ,
RELEASE_NUM ,
PO_RELEASE_ID ,
RELEASE_DATE ,
CURRENCY_CODE ,
RATE_TYPE ,
RATE_TYPE_CODE ,
RATE_DATE ,
RATE ,
AGENT_NAME ,
AGENT_ID ,
VENDOR_NAME ,
VENDOR_ID ,--r
VENDOR_SITE_CODE ,--r
VENDOR_SITE_ID ,--r
VENDOR_CONTACT ,
VENDOR_CONTACT_ID ,
SHIP_TO_LOCATION ,
SHIP_TO_LOCATION_ID ,
BILL_TO_LOCATION ,
BILL_TO_LOCATION_ID ,
PAYMENT_TERMS ,
TERMS_ID ,
FREIGHT_CARRIER ,
FOB ,
FREIGHT_TERMS ,
APPROVAL_STATUS ,
APPROVED_DATE ,
REVISED_DATE ,
REVISION_NUM ,
NOTE_TO_VENDOR ,
NOTE_TO_RECEIVER ,
CONFIRMING_ORDER_FLAG ,
COMMENTS ,
ACCEPTANCE_REQUIRED_FLAG ,
ACCEPTANCE_DUE_DATE ,
AMOUNT_AGREED ,
AMOUNT_LIMIT ,
MIN_RELEASE_AMOUNT ,
EFFECTIVE_DATE ,--r c
EXPIRATION_DATE , --r c
PRINT_COUNT ,
PRINTED_DATE ,
FIRM_FLAG ,
FROZEN_FLAG ,
CLOSED_CODE ,
CLOSED_DATE ,
REPLY_DATE ,
REPLY_METHOD ,
RFQ_CLOSE_DATE ,
QUOTE_WARNING_DELAY ,
VENDOR_DOC_NUM , --r
APPROVAL_REQUIRED_FLAG ,
VENDOR_LIST ,
VENDOR_LIST_HEADER_ID ,
FROM_HEADER_ID ,
FROM_TYPE_LOOKUP_CODE ,
USSGL_TRANSACTION_CODE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
REFERENCE_NUM ,
LOAD_SOURCING_RULES_FLAG ,
VENDOR_NUM ,
FROM_RFQ_NUM ,
WF_GROUP_ID ,
PCARD_ID ,
PAY_ON_CODE ,
GLOBAL_AGREEMENT_FLAG ,
CONSUME_REQ_DEMAND_FLAG ,
SHIPPING_CONTROL ,
ENCUMBRANCE_REQUIRED_FLAG ,
AMOUNT_TO_ENCUMBER ,
CHANGE_SUMMARY ,
BUDGET_ACCOUNT_SEGMENT1 ,
BUDGET_ACCOUNT_SEGMENT2 ,
BUDGET_ACCOUNT_SEGMENT3 ,
BUDGET_ACCOUNT_SEGMENT4 ,
BUDGET_ACCOUNT_SEGMENT5 ,
BUDGET_ACCOUNT_SEGMENT6 ,
BUDGET_ACCOUNT_SEGMENT7 ,
BUDGET_ACCOUNT_SEGMENT8 ,
BUDGET_ACCOUNT_SEGMENT9 ,
BUDGET_ACCOUNT_SEGMENT10 ,
BUDGET_ACCOUNT_SEGMENT11 ,
BUDGET_ACCOUNT_SEGMENT12 ,
BUDGET_ACCOUNT_SEGMENT13 ,
BUDGET_ACCOUNT_SEGMENT14 ,
BUDGET_ACCOUNT_SEGMENT15 ,
BUDGET_ACCOUNT_SEGMENT16 ,
BUDGET_ACCOUNT_SEGMENT17 ,
BUDGET_ACCOUNT_SEGMENT18 ,
BUDGET_ACCOUNT_SEGMENT19 ,
BUDGET_ACCOUNT_SEGMENT20 ,
BUDGET_ACCOUNT_SEGMENT21 ,
BUDGET_ACCOUNT_SEGMENT22 ,
BUDGET_ACCOUNT_SEGMENT23 ,
BUDGET_ACCOUNT_SEGMENT24 ,
BUDGET_ACCOUNT_SEGMENT25 ,
BUDGET_ACCOUNT_SEGMENT26 ,
BUDGET_ACCOUNT_SEGMENT27 ,
BUDGET_ACCOUNT_SEGMENT28 ,
BUDGET_ACCOUNT_SEGMENT29 ,
BUDGET_ACCOUNT_SEGMENT30 ,
BUDGET_ACCOUNT ,
BUDGET_ACCOUNT_ID ,
GL_ENCUMBERED_DATE ,
GL_ENCUMBERED_PERIOD_NAME
)
re: Import Standard Purchase Order (标准ＰＯ订单导入接口)ＢＵＧ集合(1) 
