AP 1099 Payments (1)

INSERT INTO AP_1099_TAPE_DATA ( VENDOR_ID , REGION_CODE , ORG_ID ) SELECT /*+  ordered  */ P.VENDOR_ID , '' , I.ORG_ID FROM AP_INVOICE_PAYMENTS IP , AP_CHECKS C , CE_BANK_ACCOUNTS ABA , CE_BANK_ACCT_USES_ALL BAU , AP_INVOICES I , PO_VENDORS P , AP_INVOICE_DISTRIBUTIONS ID , GL_CODE_COMBINATIONS CC , AP_REPORTING_ENTITY_LINES REL , (SELECT INVOICE_ID , COUNT (*) IP_COUNT FROM AP_INVOICE_PAYMENTS_ALL AIP   WHERE AIP.AMOUNT !=  0  AND AIP.ACCOUNTING_DATE BETWEEN :b1 AND :b2   GROUP BY INVOICE_ID  ) IP2   WHERE P.VENDOR_ID =  I.VENDOR_ID  AND ( ( :b3 =  'Y'  AND P.FEDERAL_REPORTABLE_FLAG =  'Y' )  OR ( :b3 =  'N'  OR :b3 IS NULL  ) )  AND I.INVOICE_ID =  IP.INVOICE_ID  AND IP.INVOICE_ID =  IP2.INVOICE_ID (+)    AND I.INVOICE_ID =  ID.INVOICE_ID  AND ( C.VOID_DATE IS NULL   OR C.VOID_DATE NOT  BETWEEN :b1 AND :b2 )  AND IP.ACCOUNTING_DATE BETWEEN :b1 AND :b2  AND ID.TYPE_1099 IS NOT NULL   AND ABA.ASSET_CODE_COMBINATION_ID =  CC.CODE_COMBINATION_ID  AND C.CE_BANK_ACCT_USE_ID =  BAU.BANK_ACCT_USE_ID  AND BAU.BANK_ACCOUNT_ID =  ABA.BANK_ACCOUNT_ID  AND C.ORG_ID =  MO_GLOBAL.GET_CURRENT_ORG_ID   AND C.CHECK_ID =  IP.CHECK_ID  AND REL.TAX_ENTITY_ID =  :b4  AND CC.CHART_OF_ACCOUNTS_ID =  :b5  AND DECODE(:b6 , 'SEGMENT1' , CC.SEGMENT1 , 'SEGMENT2' , CC.SEGMENT2 , 'SEGMENT3' , CC.SEGMENT3 , 'SEGMENT4' , CC.SEGMENT4 , 'SEGMENT5' , CC.SEGMENT5 , 'SEGMENT6' , CC.SEGMENT6 , 'SEGMENT7' , CC.SEGMENT7 , 'SEGMENT8' , CC.SEGMENT8 , 'SEGMENT9' , CC.SEGMENT9 , 'SEGMENT10' , CC.SEGMENT10 , 'SEGMENT11' , CC.SEGMENT11 , 'SEGMENT12' , CC.SEGMENT12 , 'SEGMENT13' , CC.SEGMENT13 , 'SEGMENT14' , CC.SEGMENT14 , 'SEGMENT15' , CC.SEGMENT15 , 'SEGMENT16' , CC.SEGMENT16 , 'SEGMENT17' , CC.SEGMENT17 , 'SEGMENT18' , CC.SEGMENT18 , 'SEGMENT19' , CC.SEGMENT19 , 'SEGMENT20' , CC.SEGMENT20 , 'SEGMENT21' , CC.SEGMENT21 , 'SEGMENT22' , CC.SEGMENT22 , 'SEGMENT23' , CC.SEGMENT23 , 'SEGMENT24' , CC.SEGMENT24 , 'SEGMENT25' , CC.SEGMENT25 , 'SEGMENT26' , CC.SEGMENT26 , 'SEGMENT27' , CC.SEGMENT27 , 'SEGMENT28' , CC.SEGMENT28 , 'SEGMENT29' , CC.SEGMENT29 , 'SEGMENT30' , CC.SEGMENT30 )  =  REL.BALANCING_SEGMENT_VALUE   GROUP BY P.VENDOR_ID , I.ORG_ID  HAVING :b7 =  'N'  OR ( SUM (DECODE(ID.TYPE_1099 , 'MISC1' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) ) +  SUM (DECODE(ID.TYPE_1099 , 'MISC3' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  +  SUM (DECODE(ID.TYPE_1099 , 'MISC6' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  +  SUM (DECODE(ID.TYPE_1099 , 'MISC7' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  +  SUM (DECODE(ID.TYPE_1099 , 'MISC9' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  +  SUM (DECODE(ID.TYPE_1099 , 'MISC10' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  >=  600  OR SUM (DECODE(ID.TYPE_1099 , 'MISC2' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) ) >=  10  OR SUM (DECODE(ID.TYPE_1099 , 'MISC8' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) ) >=  10  OR SUM (DECODE(ID.TYPE_1099 , 'MISC15a T' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) ) +  SUM (DECODE(ID.TYPE_1099 , 'MISC15a NT' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  >=  600  OR SUM (DECODE(ID.TYPE_1099 , 'MISC13' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) ) +  SUM (DECODE(ID.TYPE_1099 , 'MISC5' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  >  0  OR SUM (DECODE(ID.TYPE_1099 , 'MISC14' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) ) >=  600  OR SUM (DECODE(ID.TYPE_1099 , 'MISC15b' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) ) >  0  OR -1 *  SUM (DECODE(ID.TYPE_1099 , 'MISC4' , ( DECODE(I.CANCELLED_AMOUNT ,  NULL  , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 +  1  )  , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 -  1  )  , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT )  )  )  /  DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT ,  NULL  , 1 , 0 , 1 , I.CANCELLED_AMOUNT )  , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID )  )  )   *  DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT ,  NULL  , 1 , 0 )  , IP.AMOUNT )  )  , 0 ) )  >  0 )

INSERT INTO AP_1099_TAPE_DATA ( VENDOR_ID , REGION_CODE , ORG_ID ) SELECT /*+ ordered */ P.VENDOR_ID , '' , I.ORG_ID FROM AP_INVOICE_PAYMENTS IP , AP_CHECKS C , CE_BANK_ACCOUNTS ABA , CE_BANK_ACCT_USES_ALL BAU , AP_INVOICES I , PO_VENDORS P , AP_INVOICE_DISTRIBUTIONS ID , GL_CODE_COMBINATIONS CC , AP_REPORTING_ENTITY_LINES REL , (SELECT INVOICE_ID , COUNT (*) IP_COUNT FROM AP_INVOICE_PAYMENTS_ALL AIP WHERE AIP.AMOUNT != 0 AND AIP.ACCOUNTING_DATE BETWEEN :b1 AND :b2 GROUP BY INVOICE_ID ) IP2 WHERE P.VENDOR_ID = I.VENDOR_ID AND ( ( :b3 = 'Y' AND P.FEDERAL_REPORTABLE_FLAG = 'Y' ) OR ( :b3 = 'N' OR :b3 IS NULL ) ) AND I.INVOICE_ID = IP.INVOICE_ID AND IP.INVOICE_ID = IP2.INVOICE_ID (+) AND I.INVOICE_ID = ID.INVOICE_ID AND ( C.VOID_DATE IS NULL OR C.VOID_DATE NOT BETWEEN :b1 AND :b2 ) AND IP.ACCOUNTING_DATE BETWEEN :b1 AND :b2 AND ID.TYPE_1099 IS NOT NULL AND ABA.ASSET_CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID AND C.CE_BANK_ACCT_USE_ID = BAU.BANK_ACCT_USE_ID AND BAU.BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID AND C.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID AND C.CHECK_ID = IP.CHECK_ID AND REL.TAX_ENTITY_ID = :b4 AND CC.CHART_OF_ACCOUNTS_ID = :b5 AND DECODE(:b6 , 'SEGMENT1' , CC.SEGMENT1 , 'SEGMENT2' , CC.SEGMENT2 , 'SEGMENT3' , CC.SEGMENT3 , 'SEGMENT4' , CC.SEGMENT4 , 'SEGMENT5' , CC.SEGMENT5 , 'SEGMENT6' , CC.SEGMENT6 , 'SEGMENT7' , CC.SEGMENT7 , 'SEGMENT8' , CC.SEGMENT8 , 'SEGMENT9' , CC.SEGMENT9 , 'SEGMENT10' , CC.SEGMENT10 , 'SEGMENT11' , CC.SEGMENT11 , 'SEGMENT12' , CC.SEGMENT12 , 'SEGMENT13' , CC.SEGMENT13 , 'SEGMENT14' , CC.SEGMENT14 , 'SEGMENT15' , CC.SEGMENT15 , 'SEGMENT16' , CC.SEGMENT16 , 'SEGMENT17' , CC.SEGMENT17 , 'SEGMENT18' , CC.SEGMENT18 , 'SEGMENT19' , CC.SEGMENT19 , 'SEGMENT20' , CC.SEGMENT20 , 'SEGMENT21' , CC.SEGMENT21 , 'SEGMENT22' , CC.SEGMENT22 , 'SEGMENT23' , CC.SEGMENT23 , 'SEGMENT24' , CC.SEGMENT24 , 'SEGMENT25' , CC.SEGMENT25 , 'SEGMENT26' , CC.SEGMENT26 , 'SEGMENT27' , CC.SEGMENT27 , 'SEGMENT28' , CC.SEGMENT28 , 'SEGMENT29' , CC.SEGMENT29 , 'SEGMENT30' , CC.SEGMENT30 ) = REL.BALANCING_SEGMENT_VALUE GROUP BY P.VENDOR_ID , I.ORG_ID HAVING :b7 = 'N' OR ( SUM (DECODE(ID.TYPE_1099 , 'MISC1' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) + SUM (DECODE(ID.TYPE_1099 , 'MISC3' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) + SUM (DECODE(ID.TYPE_1099 , 'MISC6' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) + SUM (DECODE(ID.TYPE_1099 , 'MISC7' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) + SUM (DECODE(ID.TYPE_1099 , 'MISC9' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) + SUM (DECODE(ID.TYPE_1099 , 'MISC10' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) >= 600 OR SUM (DECODE(ID.TYPE_1099 , 'MISC2' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) >= 10 OR SUM (DECODE(ID.TYPE_1099 , 'MISC8' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) >= 10 OR SUM (DECODE(ID.TYPE_1099 , 'MISC15a T' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) + SUM (DECODE(ID.TYPE_1099 , 'MISC15a NT' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) >= 600 OR SUM (DECODE(ID.TYPE_1099 , 'MISC13' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) + SUM (DECODE(ID.TYPE_1099 , 'MISC5' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) > 0 OR SUM (DECODE(ID.TYPE_1099 , 'MISC14' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) >= 600 OR SUM (DECODE(ID.TYPE_1099 , 'MISC15b' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) > 0 OR -1 * SUM (DECODE(ID.TYPE_1099 , 'MISC4' , ( DECODE(I.CANCELLED_AMOUNT , NULL , ID.AMOUNT , DECODE(GREATEST(ID.ACCOUNTING_DATE , :b2 + 1 ) , ID.ACCOUNTING_DATE , 0 , DECODE(LEAST(ID.ACCOUNTING_DATE , :b1 - 1 ) , ID.ACCOUNTING_DATE , 0 , ID.AMOUNT ) ) ) / DECODE(I.INVOICE_AMOUNT , 0 , DECODE(I.CANCELLED_AMOUNT , NULL , 1 , 0 , 1 , I.CANCELLED_AMOUNT ) , DECODE(AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) , 0 , 1 , AP_UTILITIES_PKG.NET_INVOICE_AMOUNT(I.INVOICE_ID ) ) ) * DECODE(IP.AMOUNT , 0 , DECODE(IP2.IP_COUNT , NULL , 1 , 0 ) , IP.AMOUNT ) ) , 0 ) ) > 0 )