Query to Link between Order Management and Account Receivables
On request I continue to write further on Post Relation between AR Invoice and Sales Order tables and based on the standard assumption, provide a query that can be used to link Invoice and Sales Order.
Query below can be handy
Query below can be handy
SELECT ooha.order_number
, oola.line_number so_line_number
, oola.ordered_item
, oola.ordered_quantity * oola.unit_selling_price so_extended_price
, rcta.trx_number invoice_number
, rcta.trx_date
, rctla.line_number inv_line_number
, rctla.unit_selling_price inv_unit_selling_price
FROM oe_order_headers_all ooha
, oe_order_lines_all oola
, ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
WHERE ooha.header_id = oola.header_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND order_number = :p_order_numberTUESDAY, SEPTEMBER 8, 2009
Relation between AR Invoice and Sales Order tables
Many times I have seen a question being asked in the forums about the relationship between AR Invoice and Sales Order.
There are several interface_line_attribute and interface_header_attribute columns in RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL table respectively which are used to map it with other modules.
The relationship is actually mapped using the descriptive flexfield.
Query for following
Application: Receivables
Title: Line Transaction Flexfield
and then in the context field is the listed different modules.
For Sales order search for Order Management or Order Entry and click on segments to see how they are mapped. Screenshots below
There are several interface_line_attribute and interface_header_attribute columns in RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL table respectively which are used to map it with other modules.
The relationship is actually mapped using the descriptive flexfield.
Query for following
Application: Receivables
Title: Line Transaction Flexfield
and then in the context field is the listed different modules.
For Sales order search for Order Management or Order Entry and click on segments to see how they are mapped. Screenshots below
Click on the segments button to see column mappings.
The above method can also be used to find mapping of Receivables with other modules like Oracle Projects, Services, Contracts etc.
Following API's are used for creating customers
1) The first step is to create Party. hz_party_v2pub.create_organization is used to create a party.
2) Once party is created then the customer accounts should be created.hz_cust_account_v2pub.create_cust_account API is used to create Customer Accounts. The p_organization_rec should have party information. The orig_system_reference for p_cust_account_Rec should be same as parties orig_system_reference.
3) Now that party and accounts are created, customer account sites and its uses should be created. But before that location and party_sites should be created and attached to party.
4) hz_location_v2pub.create_location API is used to create location. This is a simple API that takes address table type as input and returns location_id as a OUT parameter.
5) API hz_party_site_v2pub.create_party_site is used to create party_site. The party_id created in step 1 and location_id created in step 4 is passed in the party_site_rec parameter. This will return party_site_id as a OUT parameter.
6) Now that we have created the party_sites, its time to create customer site using APIhz_cust_account_site_v2pub.create_cust_acct_site. The cust_account_id created in step 2 and party_site_id created in step 5 is inputted in the cust_acct_site_rec record type. This returns cust_acct_site_id as a OUT parameter.
7) The site use(SHIP_TO, BILL_TO etc.) can be created using APIhz_cust_account_site_v2pub.create_cust_site_use. The cust_acct_site_id created in step 6
8) For BILL_TO the customer profiles can be created using APIhz_customer_profile_v2pub.create_customer_profile
1) The first step is to create Party. hz_party_v2pub.create_organization is used to create a party.
2) Once party is created then the customer accounts should be created.hz_cust_account_v2pub.create_cust_account API is used to create Customer Accounts. The p_organization_rec should have party information. The orig_system_reference for p_cust_account_Rec should be same as parties orig_system_reference.
3) Now that party and accounts are created, customer account sites and its uses should be created. But before that location and party_sites should be created and attached to party.
4) hz_location_v2pub.create_location API is used to create location. This is a simple API that takes address table type as input and returns location_id as a OUT parameter.
5) API hz_party_site_v2pub.create_party_site is used to create party_site. The party_id created in step 1 and location_id created in step 4 is passed in the party_site_rec parameter. This will return party_site_id as a OUT parameter.
6) Now that we have created the party_sites, its time to create customer site using APIhz_cust_account_site_v2pub.create_cust_acct_site. The cust_account_id created in step 2 and party_site_id created in step 5 is inputted in the cust_acct_site_rec record type. This returns cust_acct_site_id as a OUT parameter.
7) The site use(SHIP_TO, BILL_TO etc.) can be created using APIhz_cust_account_site_v2pub.create_cust_site_use. The cust_acct_site_id created in step 6
8) For BILL_TO the customer profiles can be created using APIhz_customer_profile_v2pub.create_customer_profile
Query to get Customer Name, Number and Address
Below query can be handy to get customer related information.
The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses.
The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses.
SELECT hp.party_name
, hp.party_number
, hca.account_number
, hca.cust_account_id
, hp.party_id
, hps.party_site_id
, hps.location_id
, hl.address1
, hl.address2
, hl.address3
, hl.city
, hl.state
, hl.country
, hl.postal_code
, hcsu.site_use_code
, hcsu.site_use_id
, hcsa.bill_to_flag
FROM hz_parties hp
, hz_party_sites hps
, hz_locations hl
, hz_cust_accounts_all hca
, hz_cust_acct_sites_all hcsa
, hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hca.account_number = :customer_numberP.S. The query is not completely tested. Please let me know if you find any problem
No comments:
Post a Comment