Wednesday, January 30, 2013

Oracle Receivables SHIP TO query



SELECT DISTINCT
  HCA.ACCOUNT_NUMBER "Legacy_customer_number" ,
  HPS.PARTY_SITE_NUMBER "Legacy_Customer_No_part2"          ,
  NULL "Systemid"                                           ,
  NULL "Sap_Hierarchy"                                      ,
  NULL "AcctGroup"                                          ,
  hp.party_name "Name1"                                     ,
  NULL "Name2"                                              ,
  NULL "Name3"                                              ,
  NULL "Name4 Do Not Use"                                   ,
  HL1.ADDRESS1 street1                                      ,
  HL1.ADDRESS2 street2                                      ,
  hl1.address3 street3                                      ,
  hl1.address4 street4                                      ,
  hl1.house_number house                                    ,
  HL1.CITY                                                  ,
  NULL "District"                                           ,
  HL1.POSTAL_CODE                                           ,
  DECODE(HCP.PHONE_LINE_TYPE,'GEN',HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER,NULL) PHONE_NUMBER,
  NULL "VatReg"                        ,
  rt.name pay_term                     ,
  HL1.COUNTRY                          ,
  HL1.STATE REGION                     ,
  NULL IncoTerms                       ,
  NULL Currency                        ,
  HCA.ACCOUNT_NUMBER "Customer Number" ,
  NULL "PayerLoc"                      ,
  NULL "ACCTATCUST"                    ,
  NULL "KVGR3"                         ,
  NULL "Industry_code"                 ,
  NULL "Language"                      ,
  NULL "NaturalPerson"                 ,
  DECODE(HCP.PHONE_LINE_TYPE,'FAX',HCP.PHONE_AREA_CODE ||HCP.PHONE_NUMBER,NULL) FAX_NUMBER,
  NULL "SearchTerm"                  ,
  NULL "Vat/TaxRate"                 ,
  NULL "PriceList"                   ,
  NULL "Industry_Code_1"             ,
  NULL "IncoTerms_2"                 ,
  NULL "SalesOffice"                 ,
  NULL "SalesGroup"                  ,
  NULL "SAP_Hierarchy_Name"          ,
  NULL "Additional Payers"           ,
  NULL "Tax_number_1"                ,
  NULL "Tax_number_2"                ,
  NULL "Tax_number_3"                ,
  NULL "Tax_number_4"                ,
  NULL "ICMS law"                    ,
  NULL "IPI Law"                     ,
  NULL "CFOP Category"               ,
  NULL "Subtrib group"               ,
  NULL "ICMS Exempt"                 ,
  NULL "IPI Exempt"                  ,
  HP.EMAIL_ADDRESS "Email"           ,
  NULL "Note"                        ,
  NULL "Reconciliation Account"
FROM hz_parties hp         ,
  hz_locations hl1            ,
  hz_cust_site_uses_all hcsu  ,
  hz_cust_acct_sites_all hcas ,
  hz_party_sites hps          ,
  HZ_CUST_ACCOUNTS hca        ,
  HZ_CONTACT_POINTS HCP       ,
  ra_terms rt
WHERE 1 = 1
   AND hcsu.cust_acct_site_id =hcas.cust_Acct_Site_id
   AND hcas.party_site_id     =hps.party_site_id
   AND hps.location_id        = hl1.location_id
   AND hp.party_id            =hcp.owner_table_id(+)
   AND hp.party_id            =hps.party_id
   AND HCA.CUST_ACCOUNT_ID    =HCAS.CUST_ACCOUNT_ID
   AND hcsu.PAYMENT_TERM_ID   = rt.TERM_ID(+)
   AND hcsu.site_use_code     ='SHIP_TO'
   AND HCSU.STATUS            = 'A'
   AND HCA.ACCOUNT_NUMBER LIKE '26210'
   AND hcas.org_id =736;
--AND hcas.org_id =457;

----> Sponsored by Nisha Ellora

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect