Skip to main content
AR Bill To and Ship To Customer Scripts



-----------------------------------------------------------------------------------------------------------------------------
AR Bill To Customer Query

SELECT rct.customer_trx_id,
  rct.bill_to_customer_id,
  rct.bill_to_site_use_id,
  bill_party.party_name bill_customer,
  bill_cus.account_number customer_number,
  hcs_bill.location,
  hl_bill.address1,
  hl_bill.address2,
  hl_bill.city
  ||', '
  ||hl_bill.state
  ||' '
  ||hl_bill.postal_code
  ||' '
  ||hl_bill.country
FROM ra_customer_trx_all rct,
  hz_cust_accounts bill_cus,
  hz_parties bill_party,
  HZ_CUST_SITE_USES_ALL hcs_bill,
  HZ_CUST_ACCT_SITES_ALL hca_bill,
  hz_party_sites hps_bill,
  hz_locations hl_bill
WHERE customer_trx_id         =79364
AND rct.bill_to_customer_id   =bill_cus.cust_account_id
AND bill_party.party_id       =bill_cus.party_id
AND rct.bill_to_site_use_id   =hcs_bill.site_use_id
AND hcs_bill.site_use_code    ='BILL_TO'
AND hca_bill.cust_acct_site_id=hcs_bill.cust_acct_site_id
AND hps_bill.party_site_id    =hca_bill.party_site_id
AND hl_bill.location_id       =hps_bill.location_id;

AR Ship To Customer Query

SELECT rct.customer_trx_id,
  rct.ship_to_customer_id,
  rct.ship_to_site_use_id,
  ship_party.party_name bill_customer,
  ship_cus.account_number customer_number,
  hcs_ship.location,
  hl_ship.address1,
  hl_ship.address2,
  hl_ship.city
  ||', '
  ||hl_ship.state
  ||' '
  ||hl_ship.postal_code
  ||' '
  ||hl_ship.country
  FROM ra_customer_trx_all rct,
  hz_cust_accounts ship_cus,
  hz_parties ship_party,
  HZ_CUST_SITE_USES_ALL hcs_ship,
  HZ_CUST_ACCT_SITES_ALL hca_ship,
  hz_party_sites hps_ship,
  hz_locations hl_ship
WHERE customer_trx_id         =79364
AND rct.ship_to_customer_id   =ship_cus.cust_account_id
AND ship_party.party_id       =ship_cus.party_id
AND rct.ship_to_site_use_id   =hcs_ship.site_use_id
AND hcs_ship.site_use_code    ='SHIP_TO'
AND hca_ship.cust_acct_site_id=hcs_ship.cust_acct_site_id
AND hps_ship.party_site_id    =hca_ship.party_site_id
AND hl_ship.location_id       =hps_ship.location_id;


-----------------------------------------------------------------------------------------------------------------------------

Comments

Post a Comment

Popular posts from this blog

FND_PROFILE API In Oracle Apps

 FND_PROFILE API In Oracle Apps --------------------------------------------------------------------------------------------------- FND_PROFILE API is used very frequently in oracle apps development. The major purpose of this API is to: ·                      Retrieve user profile values for the current run-time environment ·                      Set user profile values for the current run-time environment FND_PROFILE API has different procedures and functions which are most frequently used. Following are the brief explanation of them: 1. FND_PROFILE.PUT:  This can be used to put a value to the specified user profile option. Example:  FND_Profile.Put(‘PROFILE_NAME’, ‘New_Value’) FND_Profile.Put(’USERNAME’, Usr_Name) FND_Profile.Pu...
Post a Comment
Read more

PO Matching Setting in Purchase Order Shipment Line(2-way,3-way,4-way)

[PO] Matching Setting in Purchase Order Shipment Line Invoice matching can be set in five different areas of Oracle Purchasing: In the list below, a setting at any level will override the settings above it. 1. Oracle Purchasing Options a. Navigate to: Setup > Organizations > Purchasing Options b. Select Default Alternative Region 2. Supplier Information a. Navigate to: Supply Base > Suppliers b. Query on specific supplier c. Click on Open d. Select Receiving Alternative Region 3. Line Types a. Navigate to: Setup > Purchasing > Line Types b. In the Receipt Required field: Yes = 3-way, No = 2-way 4. Items a. Navigate to: Items > Master Items b. Query on specific item c. Select Purchasing Alternative Region d. In the Invoice Matching section: Yes = 3-way, No = 2-way 5. Purchase Order Shipments a. Navigate to: Purchase Orders > Purchase Orders b. Enter (header and) line information c. Click on Shipments button d. Select More Alternative Region Ramification of Invoice ...
Post a Comment
Read more

Query To get the SMTP Server Details for a database server

Query To get the SMTP Server Details for a database server -------------------------------------------------------------------------------------------------------------------------------------- select fscpv.parameter_value "SMTP Host Name"       --SMTP protocol uses default port number 25 for outgoing emails       ,25                    "SMTP Port Number"       ,fscpt.description   from fnd_svc_comp_params_tl fscpt       ,fnd_svc_comp_param_vals fscpv  where fscpt.parameter_id = fscpv.parameter_id    and fscpt.display_name = 'Outbound Server Name' --'Inbound Server Name'    and fscpt.language = 'US'; --------------------------------------------------------------------------------------------------------------------------------------
Post a Comment
Read more