Drop Ship Order - Requisition Import 失敗

最近遇到Drop Ship的訂單，在開出PR時會失敗，此前的模組負責人都是請user重新開立，這次模組換人維護後，才知道user一直有這個困擾，查了一下metalink後，找到了解決方法。

1.利用SQL查詢出Requisition Import的原因：

select pri.transaction_id req_tranx_id
, msi.segment1 item_number
, oeh.order_number sales_order
, oel.LINE_NUMBER
, pri.process_flag process_flag
, pri.request_id conc_request_id
, poi.error_message
, ipr.ORGANIZATION_CODE
from mtl_system_items_b msi,
mtl_parameters ipr,
po_interface_errors poi,
oe_drop_ship_sources oed,
po_requisitions_interface_all pri,
oe_order_headers_all oeh,
oe_order_lines_all oel
where oeh.header_id = oed.header_id and
oel.header_id = oed.header_id and
oel.line_id = oed.line_id and
pri.transaction_id = poi.interface_transaction_id and
pri.interface_source_line_id = oed.drop_ship_source_id and
pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID and
pri.item_id = msi.inventory_item_id and
msi.organization_id = ipr.ORGANIZATION_ID and
oeh.order_number = :p_order_number

2.修正問題後（一般都是漏掉採購員等），update UPDATE PO_REQUISITIONS_INTERFACE_ALL

UPDATE PO_REQUISITIONS_INTERFACE_ALL
SET PROCESS_FLAG = NULL,
REQUISITION_HEADER_ID = NULL,
REQUISITION_LINE_ID = NULL,
REQ_DISTRIBUTION_ID = NULL
WHERE PROCESS_FLAG = 'ERROR'
AND INTERFACE_SOURCE_CODE = 'ORDER ENTRY'
and REQUISITION_LINE_ID = :P_REQUISITION_ID -- 條件依據個人習慣

3.重送Requisition Import，完成。

MRP - mrp_forecast_interface

mrp_forecast_interface方便整批建立forecast料號
1. 新增forecast

2. Insert mrp_forecast_interface

insert into mrp_forecast_interface
(ORGANIZATION_ID, -- INV ORG.
INVENTORY_ITEM_ID, -- ITEM ID
FORECAST_DESIGNATOR, -- forecast name ex. test1
FORECAST_DATE,
QUANTITY,
PROCESS_STATUS, -- 2
CONFIDENCE_PERCENTAGE, -- 100
LAST_UPDATE_DATE, -- sysdate
LAST_UPDATED_BY, -- user id
CREATION_DATE, -- sysdate
CREATED_BY) -- user id

3. commit 後既執行排程匯入動作.

Oracle Forms Trigger 執行順序

出處：https://metalink.oracle.com/

Action                 Trigger Firing Order                    Level
------                 --------------------                    -----
Runform                 1.  Pre-Logon                          Form
                        2.  On-Logon                           Form
                        3.  Post-Logon                         Form
                        4.  Pre-Form                           Form
                        5.  When-Create-Record                 Block
                        6.  Pre-Block                          Block
                        7.  Pre-Record                         Block
                        8.  Pre-Text-Item                      Item
                        9.  When-New-Form-Instance             Form
                        10. When-New-Block-Instance            Block
                        11. When-New-Record-Instance           Block
                        12. When-New-Item-Instance             Item

***********
Enter Query             1.  Post-Text-Item                     Item
                        2.  Post-Record                        Block
                        3.  When-Clear-Block                   Block
                        4.  When-New-Record-Instance           Block
                        5.  When-New-Item-Instance             Item

Note: If you define the Key-ENTQRY trigger, this is the only trigger
      which fires in an Enter Query situation.

*****************
Execute Query
After Enter Query       1.  Pre-Query                          Block
                        2.  Pre-Select                         Block
                        3.  On-Select                          Block
                        4.  When-Clear-Block                   Block
                        5.  Post-Select                        Block
                        6.  On-Fetch                           Block
                        7.  On-Close                           Form
                        8.  When-Clear-Block                   Block

Note: If you define the Key-EXEQRY trigger, this is the only trigger
      which fires in an Execute Query situation.

*************
Execute Query
Without Enter
Query                   1.  Post-Text-Item                     Block
                        2.  Pre-Query                          Block
                        3.  Pre-Select                         Block
                        4.  On-Select                          Block
                        5.  Post-Select                        Block
                        6.  On-Fetch                           Block
                        7.  On-Close                           Form
                        8.  When_Create_Record                 Block
                        9.  Pre-Record                         Block
                        10. Pre-Text-Item                      Item
                        11. When-New-Record-Instance           Block
                        12. When-New-Item-Instance             Item

****
Exit                    1.  Post-Text-Item                     Item
                        2.  Post-Record                        Block
                        3.  Post-Block                         Block
                        4.  Post-Form                          Form
                        5.  On-Rollback                        Form
                        6.  Pre-Logout                         Form
                        7.  On-Logout                          Form
                        8.  Post-Logout                        Form

**************
Next Field and
Previous field          1.  When-New-Item-Instance             Item

***************
Next Record and
Previous Record         1.  When-New-Record-Instance           Block
                        2.  When-New-Item-Instance             Item

**************
Next Block and
Previous Block          1.  Post-Text-Item                     Item
                        2.  Post-Record                        Block
                        3.  Post-Block                         Block
                        4.  When-Create-Record                 Block
                        5.  Pre-Block                          Block
                        6.  Pre-Record                         Block
                        7.  Pre-Text-Item                      Block
                        8.  When-New-Block-Instance            Block
                        9.  When-New-Record-Instance           Block
                        10. When-New-Item-Instance             Form

*******************
Records Are Queried     1.  Post-Query                         Block
                        2.  Post-Change                        Block
                        3.  Post-Change                        Item
                        4.  Post-Change                        Block
                        5.  Post-Change                        Block
                        Go back to Post-Query

NOTE: This cycle is repeated for each record retrieved.

**********************
No Records Are Queried  1.  When-New-Record-Instance           Block
                        2.  When-New-Item-Instance             Item

NOTE: To observe this Trigger Firing Order:
        a.  Enter a query.
        b.  Enter a nonexistent record.
        c.  Execute the query.

      The two triggers listed above, the Enter Query triggers, and
      the Execute Query triggers fire.

*************
Create Record           1.  Post-Change                         Block
                        2.  When-Validate-Item                  Block
                        3.  Post-Text-Item                      Block
                        4.  When-Validate-Record                Block
                        5.  Post-Record                         Block
                        6.  Post-Block                          Block
                        7.  On-Savepoint                        Form
                        8.  Pre-Commit                          Form
                        9.  Pre-Insert                          Block
                        10. On-Insert                           Form
                        11. Post-Insert                         Block
                        12. Post-Forms-Commit                   Form
                        13. On-Commit                           Form
                        14. Post-Database-Commit                Form
                        15. Pre-Block                           Block
                        16. Pre-Record                          Block
                        17. Pre-Text-Item                       Block
                        18. When-New-Item-Instance              Form

*************
Update Record           1.  When-Database-Record                Block
                        2.  Post-Change                         Block
                        3.  When-Validate-Item                  Block
                        4.  Post-Text-Item                      Block
                        5.  When-Validate-Record                Block
                        6.  Post-Record                         Block
                        7.  Post-Block                          Block
                        8.  On-Savepoint                        Form
                        9.  Pre-Commit                          Form
                        10. Pre-Update                          Block
                        11. On-Update                           Block
                        12. Post-Update                         Block
                        13. Post-Forms-Commit                   Form
                        14. On-Commit                           Form

                        Here the transaction is complete and one record added.

                        15. Post-Database-Commit                Form
                        16. Pre-Text-Item                       Block
                        17. When-New-Item-Instance              Form

NOTE: To observe this Trigger Firing Order:

        a.  Execute a query.
        b.  Change a value.
        c.  Choose Action->Save from the menu.
        d.  Record the triggers from that point.

*************
Delete Record           1.  On-Lock                             Block
                        2.  When-Remove-Record                  Block
                        3.  Post-Change                         Block
                        4.  Post-Change                         Block
                        5.  Post-Change                         Block
                        6.  Post-Change                         Block
                        7.  Post-Change                         Block
                        8.  Post-Change                         Block
                        9.  Post-Change                         Item
                        10. Post-Query                          Block
                        11. Post-Text-Item                      Block
                        12. Post-Record                         Block
                        13. Pre-Record                          Block
                        14. Pre-Text-Item                       Block
                        15. When-New-Record-Instance            Block
                        16. When-New-Item-Instance              Form

INV - 料號轉檔（新增/更新）

利用 mtl_system_items_interface 達到新增或更新料號目的。
1.新增
a. 將需新增的品號匯入mtl_system_items_interface：
insert into mtl_system_items_interface
(Organization_id, Segment1, Description, process_flag, Transaction_type...)
values (x_Org_id, '00-00-001', '品號說明', 1, 'CREATE'....
b.執行INV/Import/Import Items

c.查看log及mtl_interface_errors看看有無錯誤或警告。

2.更新
a. 將需更新的品號欄位匯入mtl_system_items_interface：
insert into mtl_system_items_interface
(Organization_id, Segment1, Description, process_flag, Transaction_type...)
values (x_Org_id, '00-00-001', '品號說明', 1, 'UPDATE'....

b.執行INV/Import/Import Items

c.查看log及mtl_interface_errors看看有無錯誤或警告。