AIRS_CI-382 Contimual Improvement of DA related SQL statements

AIRS_CI-382 =
AIRS_CI-380 SQL Performance Improvement for DA Creation Quicksearch Count +
AIRS_CI-381 SQL Performance Improvement for DA Creation Quicksearch Data

Change in Gerrit

• There are different statements for seller and buyer users, which contain only those predicates, which are necessary to select the seller or the buyer specific data ⇒ no predicates like (cCustomerCGId = :11 OR cSupplierCGId = :12)
  • this means
    • if supplier add '' AND (cSupplierCPId IN (-1000002, 10006106, 10006107)) AND (cSupplierCGId = 100027) AND CISUNREGSUPPLIER = 0''
    • if buyer add AND (cCustomerCGId = 100011)
  • Frank did this work already for “ASN Overview”. Check his changes:
    • https://git.seeburger.de/r/c/supplyon/airsupply/+/94452/
• Eliminate repetitions of identical predicates like

AND ROWNUM <= 5000
AND (cCustomerPortalGroup IN (:21 ,:22 ))
AND (cSupplierCPId IN (:23 ,:24 ,:25 ,:26 ))
AND (cSupplierCGId = :27)
AND CISUNREGSUPPLIER = :28

• Eliminate repetitions of identical predicates like

AND ROWNUM <= 5000
AND (cCustomerPortalGroup IN (:21 ,:22 ))
AND (cSupplierCPId IN (:23 ,:24 ,:25 ,:26 ))
AND (cSupplierCGId = :27)
AND CISUNREGSUPPLIER = :28

• Missing selective conditions for the joins of the sub queries in the first query block (for ASN_SOURCE_TYPE 2) are present.

• The SQL is built in ASNDAOImpl.getASNCreateDataStmtPerf()
• The statement is built (filled with parameters) in ASNDAOImpl.buildASNCreatePerformanceStmt()
• To fetch VMIs use quick search '600251'