Oracle EBS: PO approval API

     SELECT  i_po_header_id
           || '-' || TO_CHAR(PO_WF_ITEMKEY_S.NEXTVAL)
      --INTO V_ITEM_KEY
      FROM DUAL;

 

     mo_global.init('PO');
     mo_global.set_policy_context('S', V_ORG_ID);
 
 
      PO_REQAPPROVAL_INIT1.START_WF_PROCESS(ItemType               => V_ITEM_TYPE, --'POAPPRV',
                                            ItemKey                => V_ITEM_KEY,
                                            WorkflowProcess        => 'POAPPRV_TOP',
                                            ActionOriginatedFrom   => 'PO_FORM',
                                            DocumentID             => I_HEADER_ID,
                                            DocumentNumber         => V_SEGMENT1, -- Purchase Order Number
                                            PreparerID             => V_PREPARER_ID, -- Buyer/Preparer_id
                                            DocumentTypeCode       => V_DOCUMENT_TYPE_CODE,
                                            DocumentSubtype        => V_DOCUMENT_SUBTYPE,
                                            SubmitterAction        => 'APPROVE',
                                            forwardToID            => NULL,
                                            forwardFromID          => NULL,
                                            DefaultApprovalPathID  => NULL,
                                            Note                   => NULL,
                                            PrintFlag              => 'N',
                                            FaxFlag                => 'N',
                                            FaxNumber              => NULL,
                                            EmailFlag              => 'N',
                                            EmailAddress           => NULL,
                                            CreateSourcingRule     => 'N',
                                            ReleaseGenMethod       => 'N',
                                            UpdateSourcingRule     => 'N',
                                            MassUpdateReleases     => 'N',
                                            RetroactivePriceChange => 'N',
                                            OrgAssignChange        => 'N',
                                            CommunicatePriceChange => 'N',
                                            p_Background_Flag      => 'N', --N:標準API會COMMIT
                                            p_Initiator            => NULL,
                                            p_xml_flag             => NULL) --,
                                            --FpdsngFlag             => 'N',   --12.0.6無此參數
                                            --p_source_type_code     => NULL); --12.0.6無此參數


備註: 執行API時不可開著PO, 否則雖然狀態變為IN PROCESS, 但workflow會有exception.

Ref:
Oracle ERP R12. - Approval PO By API
http://doggww.blogspot.com/2012/12/oracle-erp-r12-approval-po-by-api.html


依官方說法, 不提供此API:
Is There An API To Submit Incomplete PO's, Releases And Requisitions For Approval? (Doc ID 1326541.1)

Oracle EBS: Close Discrete Jobs error: Can not delete more than one reservation at a time

狀況:
測試指定上期日期作為Actual close date, 執行Close Discrete Jobs時, 出現error: Can not delete more than one reservation at a time


log中的訊息:
Can not delete more than one reservation at a time
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
WIP DISCRETE JOB CLOSE
GROUP ID 1101014
Number of jobs failed because release date before close date : 0
Number of jobs failed due to Pending Clocks : 0
Cancelling Move Orders if any exists
return status S
Pending Txns Check
Number of jobs failed due to Pending txns : 0
Inside Procedure Close Date
Number of failed jobs because of past close date : 0
lot validate
delete Existing reservations
Exception has occured
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

試了幾張工單都是相同問題.


解決方式:
有篇文章說是12.1的bug, 但狀況不太像.
瘋狂測試不同方法, 當把本期的期間打開後, 就正常執行完成了...


Oracle的錯誤訊息本身就是錯誤... Orz...

Oracle EBS: 無法收貨, 出現Error APP-PO-14142

狀況: 收貨時出現error :

  APP-PO-14142: rcv_receipts_th.insert_transaction-001:
  ORA-0000: normal, successful completion
  Cause: A SQL error has occured in rcv_receipts_th.insert_transaction.


原因: uncompiled libraries / forms 造成, 包括:

  Libraries :
    RCVRCERH.pld
    RCVRCERL.pld
    RCVCOTRX.pld

  Forms:
    RCVRCERC.fmb
    RCVCOFND.fmb


解決方式: recompile有問題的libraries / forms


Ref:
R12 Cannot Enter Any Receipt APP-PO-14142 rcv_receipts_th.insert_transaction-001: ORA-0000: (Doc ID 945443.1)


附註: 有看到另一可能原因是客製程式造成
Unable to Create Receipt APP-PO-14142: rcv_receipts_th.insert_header-000: ORA-0000 RCVRCERC (Doc ID 1072428.1)

Oracle EBS: 工單狀態

使用者控制之狀態:
1.Released: 已可供生產及進行交易
2.Unreleased: 還不能有交易活動
3.Complete: 已完工, 但仍接受交易. 當完工入庫數量與工單數量相同時, 就會自動改為此狀態
4.Complete-No Charges: 已完工, 且不再接受交易
5.On Hold: 可更改工單, 但不能進行交易
6.Cancelled: 工單在完工前被取消
7.Closed: 工單已關結, 且不再接受任何活動

處理中狀態:
1.Pending Bill Load: 正在載入工單的BOM
2.Pending Routing Load: BOM已成功載入, 正在載入工單的途程
3.Failed Bill Load: 無法載入工單的BOM
4.Failed Routing load: 無法載入工單的途程
5.Pending Close: 工單正在關結中
6.Failed Close: 無法關結工單

Ref:
Discrete Job Statuses
https://docs.oracle.com/cd/A60725_05/html/comnls/us/wip/djstat.htm#r_discstat

Oracle EBS: 展BOM之SQL (II)

    SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
           LEVEL component_level,
           c.component_item_id,
           a.organization_id,
           c.item_num assm_item_num,
           c.operation_seq_num assm_operation_seq_num,
           c.effectivity_date assm_effectivity_date,
           c.disable_date assm_disable_date,
           c.component_quantity assm_component_quantity,
           (SELECT MAX (revision)
              FROM mtl_item_revisions
             WHERE     organization_id = 318
                   AND effectivity_date <= SYSDATE
                   AND inventory_item_id = c.component_item_id)
              assm_item_rev
      FROM (SELECT *
              FROM apps.bom_inventory_components
             WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effectivity_date)
                                       AND TRUNC (NVL (disable_date, SYSDATE + 1))) c,
           (SELECT *
              FROM apps.bom_bill_of_materials
             WHERE alternate_bom_designator IS NULL
               AND organization_id = 318) a
     WHERE 1 = 1 AND a.bill_sequence_id = c.bill_sequence_id
START WITH a.assembly_item_id = 5435108   --P_ITEM_ID
CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;


Ref:
BOM Bill of Materials explosion query in BOM Oracle Apps
https://sivakandigatla.blogspot.com/2015/08/bom-bill-of-materials-explosion-query.html