AP JOINS IN R12

 

AP JOINS IN R12

 CE_BANK_ACCOUNTS CBA,
    CE_BANK_ACCT_USES_ALL CBAU,
    CE_BANK_BRANCHES_V CBB,
    AP_SYSTEM_PARAMETERS_ALL ASPA,
    ce_payment_documents PD,
    AP_LOOKUP_CODES ALC1,
    iby_payment_methods_vl iby1,
    iby_payment_profiles iby2,
    fnd_lookups iby3,
    fnd_lookups iby5,
    AP_LOOKUP_CODES ALC3,
    FND_DOCUMENT_SEQUENCES FDS,
    FND_DOC_SEQUENCE_CATEGORIES FDSC,
    FND_TERRITORIES_VL FT,
    GL_DAILY_CONVERSION_TYPES GDCT,
    AP_SUPPLIERS PV,
    AP_SUPPLIER_SITES_ALL PVS,
    CE_STATEMENT_RECONCILS_ALL CSR,
    CE_STATEMENT_HEADERS CSH,
    CE_STATEMENT_LINES CSL,
    AP_CHECKS AC,
    GL_DAILY_CONVERSION_TYPES GDCT1,
    HZ_PARTIES HZP,
    HZ_PARTY_SITES HPS,
    HZ_LOCATIONS HZL,
    /* Bug 8345877 */
    AP_SUPPLIERS PV1,
    AP_SUPPLIER_SITES_ALL PVS1,
    HZ_PARTY_SITES HPS1,
    HZ_LOCATIONS HZL1,
    /* Bug 8345877 */
    HZ_PARTIES HZP1
    /*Bug 8579660*/
  WHERE AC.CE_BANK_ACCT_USE_ID       = CBAU.bank_acct_use_id(+)
  AND CBAU.bank_account_id           = CBA.bank_account_id(+)
  AND CBAU.ORG_ID                    = ASPA.ORG_ID(+)
  AND AC.MATURITY_EXCHANGE_RATE_TYPE = GDCT1.CONVERSION_TYPE(+)
  AND CBB.BRANCH_PARTY_ID(+)         = CBA.BANK_BRANCH_ID
  AND AC.PAYMENT_DOCUMENT_ID         = PD.payment_document_id(+)
  AND ALC1.LOOKUP_TYPE               = 'PAYMENT TYPE'
  AND ALC1.LOOKUP_CODE               = AC.PAYMENT_TYPE_FLAG
  AND IBY1.PAYMENT_METHOD_CODE (+)   = AC.PAYMENT_METHOD_CODE
  AND iby2.payment_profile_id (+)    = AC.payment_profile_id
  AND ALC3.LOOKUP_TYPE (+)           = 'CHECK STATE'
  AND ALC3.LOOKUP_CODE (+)           = AC.STATUS_LOOKUP_CODE
  AND AC.BANK_CHARGE_BEARER          = IBY3.LOOKUP_CODE(+)
  AND IBY3.LOOKUP_TYPE (+)           = 'IBY_BANK_CHARGE_BEARER'
  AND AC.SETTLEMENT_PRIORITY         = IBY5.LOOKUP_CODE (+)
  AND IBY5.LOOKUP_TYPE (+)           = 'IBY_SETTLEMENT_PRIORITY'
  AND AC.DOC_SEQUENCE_ID             = FDS.DOC_SEQUENCE_ID (+)
  AND FDSC.APPLICATION_ID(+)         = 200
  AND AC.DOC_CATEGORY_CODE           = FDSC.CODE (+)
  AND AC.COUNTRY                     = FT.TERRITORY_CODE (+)
  AND AC.EXCHANGE_RATE_TYPE          = GDCT.CONVERSION_TYPE (+)
  AND AC.VENDOR_ID                   = PV.VENDOR_ID (+)
  AND AC.PARTY_ID                    = HZP.PARTY_ID (+)
  AND AC.VENDOR_SITE_ID              = PVS.VENDOR_SITE_ID (+)
  AND AC.PARTY_SITE_ID               = HPS.PARTY_SITE_ID (+)
  AND HPS.LOCATION_ID                = HZL.LOCATION_ID (+)
  AND CSR.REFERENCE_TYPE (+)         = 'PAYMENT'
  AND CSR.REFERENCE_ID (+)           = AC.CHECK_ID
  AND CSR.CURRENT_RECORD_FLAG (+)    = 'Y'
  AND CSR.STATEMENT_LINE_ID          = CSL.STATEMENT_LINE_ID (+)
  AND CSL.STATEMENT_HEADER_ID        = CSH.STATEMENT_HEADER_ID (+)
  AND CSR.STATUS_FLAG (+)            = 'M'
    /* Bug 8345877 */
  AND AC.REMIT_TO_SUPPLIER_ID      = PV1.VENDOR_ID (+)
  AND AC.REMIT_TO_SUPPLIER_SITE_ID = PVS1.VENDOR_SITE_ID (+)
  AND PVS1.PARTY_SITE_ID           = HPS1.PARTY_SITE_ID (+)
  AND HPS1.LOCATION_ID             = HZL1.LOCATION_ID (+)
    /* Bug 8345877 */
  AND PV1.party_id = HZP1.PARTY_ID (+)