Can not select Order or Material receipt on Create lines from on Vendor Invoice as it do not show any value

Description

Coming from:

https://docs.google.com/document/d/1ZFgVuLSdILVi-TxX3aIZKINbRVN6UgYt-YiSAuKEvi8/edit#heading=h.2nz25odez6n7

 

Test case in oracle:

  • Login Data

    • User = GardenAdmin

    • Role = GardenWorld Admin Not Advanced

    • Org = HQ

    • Warehouse = HQ Warehouse

  • Steps

    • Create a new Purchase Order (SeedFarm)

    • Create one line with a product (Mulch_Mulch 10#)

    • Create one line with a charge (Bank Charge)

    • Verify the storage quantities (on hand, reservations)

    • Verify the BP Open Balance, Actual Lifetime Value

    • Complete the order

    • Create Material Receipt

      • Here there is an error: “Create Lines From” doesn’t show the order

 

The console shows an error on this SQL:

SELECT C_Order.C_Order_ID,NULL,NVL(C_Order.DocumentNo,'-1') ||'_'|| NVL(TRIM(TO_CHAR(C_Order.DateOrdered,'MM/DD/YYYY')),'-1'),C_Order.IsActive FROM C_Order WHERE C_Order.AD_Client_ID IN (0,11) AND (C_Order.C_Order_ID IS NULL OR C_Order.C_Order_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 259 AND AD_User_ID <> 101 AND IsActive = 'Y' )) AND C_BPartner_ID=120 AND IsSOTrx='N' AND DocStatus IN ('CL','CO') AND C_Order_ID IN (SELECT C_Order_ID FROM C_OrderLine WHERE QtyOrdered-QtyDelivered!=0) AND (CASE WHEN 103>0 THEN M_Warehouse_ID=103 ELSE 1=1 END) ORDER BY 3

java.sql.SQLSyntaxErrorException: ORA-00905: Schlüsselwort fehlt

The SQL is valid in postgresql, but not in oracle. It needs to be rewritten.

Environment

None

Activity

Fixed

Details

Assignee

Reporter

Labels

Fix versions

Priority

Created December 8, 2023 at 12:52 PM
Updated February 1, 2024 at 12:04 PM
Resolved December 13, 2023 at 6:16 PM