Sunday, March 20, 2016

Query to get Oracle Receivables Details

SELECT  rcta.trx_number "INVOICE NUMBER",
        rctta.name      "TRANSACTION TYPE",
        rcta.trx_date    "INVOICE DATE",
        apsa.due_date    "DUE DATE",
        apsa.invoice_currency_code "CURRENCY",
        apsa.amount_due_original   "AMOUNT",
        apsa.amount_due_remaining  "AMOUNT DUE",
        hp_b.party_name           "BILL TO CUSTOMER NAME",
        hcsua_b.site_use_id         "BILL TO SITE USE ID",
        hl_b.address1,
        hl_b.address2,
        hl_b.address3,
        hl_b.address4,
        hl_b.city,
        hl_b.state,
        hl_b.postal_code,
        hp_s.party_name           "SHIP TO CUSTOMER NAME",
        hcsua_s.site_use_id       "SHIP TO SITE USE ID",
        hl_s.address1,
        hl_s.address2,
        hl_s.address3,
        hl_s.address4,
        hl_s.city,
        hl_s.state,
        hl_s.postal_code     
  FROM  RA_TERMS                    RT,
        RA_CUSTOMER_TRX_ALL         RCTA,
        --RA_CUSTOMER_TRX_LINES_ALL   RCTLA,
        RA_CUST_TRX_TYPES_ALL       RCTTA,
        HZ_CUST_ACCOUNTS_ALL        HCAA,
        HZ_CUST_ACCT_SITES_ALL      HCASA_S,
        HZ_CUST_SITE_USES_ALL       HCSUA_S,
        HZ_PARTIES                  HP_S,
        HZ_PARTY_SITES              HPS_S,
        HZ_LOCATIONS                HL_S,
        HZ_CUST_ACCT_SITES_ALL      HCASA_B,
        HZ_CUST_SITE_USES_ALL       HCSUA_B,
        HZ_PARTIES                  HP_B,
        HZ_PARTY_SITES              HPS_B,
        HZ_LOCATIONS                HL_B,
        AR_PAYMENT_SCHEDULES_ALL    APSA,
        HR_LEGAL_ENTITIES           HLE
WHERE  --RCTA.CUSTOMER_TRX_ID       = RCTLA.CUSTOMER_TRX_ID
       RCTA.CUST_TRX_TYPE_ID      = RCTTA.CUST_TRX_TYPE_ID
  AND  RCTA.BILL_TO_CUSTOMER_ID   = HCAA.CUST_ACCOUNT_ID
  AND  RCTA.ORG_ID                = RCTTA.ORG_ID
  AND  APSA.CUSTOMER_TRX_ID       = RCTA.CUSTOMER_TRX_ID
  AND  APSA.CUST_TRX_TYPE_ID      = RCTTA.CUST_TRX_TYPE_ID
  AND  RCTA.TERM_ID               = RT.TERM_ID(+)
  AND  HCAA.PARTY_ID              = HP_S.PARTY_ID
  AND  HCAA.PARTY_ID              = HP_B.PARTY_ID
  --SHIP TO ADDRESS LINKS
  AND  RCTA.SHIP_TO_SITE_USE_ID   = HCSUA_S.SITE_USE_ID
  AND  HCSUA_S.CUST_ACCT_SITE_ID  = HCASA_S.CUST_ACCT_SITE_ID
  AND  HCASA_S.PARTY_SITE_ID      = HPS_S.PARTY_SITE_ID
  AND  HPS_S.PARTY_ID             = HP_S.PARTY_ID
  AND  HPS_S.LOCATION_ID          = HL_S.LOCATION_ID
  --------BILL TO  ADDRESS LINKS
  AND  RCTA.BILL_TO_SITE_USE_ID   = HCSUA_B.SITE_USE_ID
  AND  HCSUA_B.CUST_ACCT_SITE_ID  = HCASA_B.CUST_ACCT_SITE_ID
  AND  HCASA_B.PARTY_SITE_ID      = HPS_B.PARTY_SITE_ID
  AND  HPS_B.PARTY_ID             = HP_B.PARTY_ID
  AND  HPS_B.LOCATION_ID          = HL_B.LOCATION_ID
  AND  HLE.ORGANIZATION_ID        = RCTA.LEGAL_ENTITY_ID
  AND  RCTA.TRX_NUMBER            ='12149'           


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect