Order Management in Oracle Applications
Booking Order: Go with the Navigation to open sales order window Orders, Returns Sales->Orders. Then we will get the Sales Order – (NEW) Form as shown below.
Note: When we open the sales order form the order is created and the status of the order is in Entered state after that we update the order only. Don’t think that the order is created when we click on the Book Order button.
Enter the customer name which exists in the database here we take CDS, INC as example. In this form there are two mandatory fields those are bolded in the figureabove. Here when we click save then we will get the Order_Number By moving to the Line-items tab we will get another view of the screen given below.
Enter the customer name which exists in the database here we take CDS, INC as example. In this form there are two mandatory fields those are bolded in the figureabove. Here when we click save then we will get the Order_Number By moving to the Line-items tab we will get another view of the screen given below.
Now enter the Ordered Item and Quantity. To check weather the Ordered_item is existing then go with Availability button. if we book the order with out the availability of item then the backorder will be created automatically. The Availability window is shown below.
In this window the ATP Details button is Available To Promise that means how many items can we give to the customer and Global Availability button is used for check weather there is availability of the item in any other ware house or not. By clicking on the Global Availability button we will get this dialog box.
Here in this stage two tables will get affected. The complete header information will store in the OE_ORDER_HEADERS_ALL and line information will store in the OE_ORDER_LINES_ALL now the status of the line is awaiting shipping.Then go with Book Order button to book the Order then the status of your order is changed to BOOKED from the ENTERED state here WSH_DELIVERY_DETAILS table will be affected. Copy the Order_Number then close the Sales Order Form. Now go with navigation to pick Release the order.
Shipping Release Sales Orders Release Sales Orders Then we will get another window “Release Sales Orders for Picking “\
Shipping Release Sales Orders Release Sales Orders Then we will get another window “Release Sales Orders for Picking “\
Enter the Order Number which we created in the Sales Order form above and go with online button. Then we will get the message that Online completed successfully. Then go with the navigation. Here the status is in pick release the tables affected are
MTL_RESERVATIONS and MTL_ONHAND_QUANTITIES.Shipping ->Transactions
MTL_RESERVATIONS and MTL_ONHAND_QUANTITIES.Shipping ->Transactions
Then we will get the Query Manager form shown below in the form enter the Order Numbers fields.
Then go with the find button we will get now Shipping Transactions Form as shown below.
Go with Delivery tab and change the Actions to Ship Confirm and go with Go(B) Button then other dialog box will open.
Now a document set will run to view the result go with view Requests. Here fiveprograms will run those are Interface Trip Stop, Commercial Invoice, Packing Slip Report, Bill of Lading, Pick Slip Report. These all programs must be completed normal other wise our order go wrong.
Creating Invoice: To create the invoice after all the five programs completed normal we have to run manually a request named “Workflow Background Process”.
After we submit the request Workflow Background Process then “Auto Invoice Import Program” will run automatically and the Invoice will generate based on the reference of the Order_Number. Here RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL will get affected.
After the auto invoice program status come to completed normal then go with the following navigation to view the invoice.
Receivables-> Transactions ->Transactions
Then we will get the Transactions(Vision Operations) Form as shown below. By this window we can retrieve the Invoice Number. Press F11 by enters into the query mode we have to enter the Order number in the Reference field. Press ctrl+F11 to retrieve the invoice number.
Receivables-> Transactions ->Transactions
Then we will get the Transactions(Vision Operations) Form as shown below. By this window we can retrieve the Invoice Number. Press F11 by enters into the query mode we have to enter the Order number in the Reference field. Press ctrl+F11 to retrieve the invoice number.
To view the balance of the customer goes with balances button according to the requirements. The given below is the balances screen where the customer payment details are in this window. Based on the amount He pay we made a receipt for him. Here according to the below form the customer has to pay 100,000 we are making receipt forthat amount only if he pay.
Making Receipts: After taking amount from the customer, we have to make a receipt. Copy the Invoice number and close the Transactions form. Now go with the following navigation.
Receivables-> Receipts->Receipts
Then we get a Receipts form. Enter the Invoice number at the Receipt number field and Trans number which we round in the below figure observe that because based on the invoice number only we have to raise the receipt to the customer.
Receivables-> Receipts->Receipts
Then we get a Receipts form. Enter the Invoice number at the Receipt number field and Trans number which we round in the below figure observe that because based on the invoice number only we have to raise the receipt to the customer.
After entering all the mandatory fields in the form then go with Applications button. Then we get Application window just save it then your receipt is generated based on the Invoice Number.
Here in the Applications form there Chargebacks and Adjustments buttons. If there any Adjustments of any reductions in the amount then we go with Adjustments button.
Transfer to General Ledger:
Run the request General Ledger Transfer Program to create a general in GL and also to post it. The navigation for this is Interfaces -> General Ledger in the Receivable Responsibility.
Run the request General Ledger Transfer Program to create a general in GL and also to post it. The navigation for this is Interfaces -> General Ledger in the Receivable Responsibility.
Then it will ask for the Request, enter the request name as General Ledger Transfer Program then it will ask for the parameters the given below screen will appear. The mandatory parameters for this request are GL Posted Date and Post in Summary.
By clicking on the OK button then the request will be submitted then go for menu view requests to view the status of the request which we submitted then the current created receipt will be submitted to the GL successfully.
Returning Order: Enter the company name and other details same like booking the order as we mentioned above. There is a change in the Line items tab. Change the line type as Return Receipt because we taking returns from the customer.
Now go with Returns tab in the Line Items tab. The Returns view is shown below. Here the Return Reason is the mandatory column on what purpose they are returning the goods we have to enter here we take Damaged goods. Now go to the reference field here rounded in the figure.
Then another dialog box will open given below. Enter the field’s reference type as Customer PO or Invoice or Sales Order or Serial number. Here we take Customer PO as reference we have to enter the Customer PO when we book the sales order. At the same
time when we choose the reference as Invoice or any other options we mentioned above also the same process.
time when we choose the reference as Invoice or any other options we mentioned above also the same process.
By Pressing ok here and click on the Book Order button then the Return order is booked successfully message will be displayed.
Now go with the navigation Purchasing ->Receiving ->Receipts then we will ask for the choose the Organization here we will select M1 as we are working with M1 organization.
Then another window Find Expected Receipts(M1) will open given below. Go to the Customer tab enter the order number which we create there for the sales return order. After entering the RMA Num (Return Order Number).
Now go with the navigation Purchasing ->Receiving ->Receipts then we will ask for the choose the Organization here we will select M1 as we are working with M1 organization.
Then another window Find Expected Receipts(M1) will open given below. Go to the Customer tab enter the order number which we create there for the sales return order. After entering the RMA Num (Return Order Number).
Go with the find button then another window Receipts(M1) will open. Check the rounded check box and enter the mandatory field Sub inventory and other if necessary then go with save button then automatically the receipt number will be generated.
Click on the Header button then receipt header form will be open. Now the receipt number is generated then close the form.
When the Receipt was generated then two Requests will run in the background automatically they are shown below in the Request window.
Order to Cash Flow with Order Management and Process Inventory:
Entering and managing orders is performed in Order Management. Shipping Execution allows you to plan and confirm your shipments. Inventory to satisfy your order requirements is allocated and picked in Process Inventory.
The following diagram illustrates the components of the Order Management for Process flow and is described in the following steps. Note that this is a sample flow and can be modified.
1. Sales orders are entered and booked through Order Management. Reserving the order creates a high-level allocation in Process Inventory.
2. Before an order can be picked and shipped, it must be pick released. The Pick Release process can invoke automatic inventory allocation and creates a process move order.
3. Process move orders allow you to manually assign available Process Inventory to a move order or pick from a list of available lots. Lines that have been automatically allocated may be viewed and edited. Once an order line is allocated (detailed), it is pick confirmed.
4. Deliveries and trips are created in Shipping Execution. Containers can also be used.
5. Once you have verified the allocation of inventory to an order, it needs to be Pick Confirmed. This step can be automated or done through the process move orders form in Process Inventory. After the pick confirm, allocated inventory is marked as staged.
6. Ship confirm is the final process in Shipping Execution which records the actual shipped amounts and creates backorders, if necessary. At the completion of ship confirm, on hand inventory is decremented for the shipped quantity.
7. Auto Invoicing allows billing of confirmed shipments. This process can be setup to run automatically upon shipment of an order or be invoked manually. Invoicing and cash receipt are handled within Oracle Receivables.
8. The final step in the process is the running of the subsidiary ledger update to create the entries for Inventory and Cost of Goods Sold.
The following diagram illustrates the components of the Order Management for Process flow and is described in the following steps. Note that this is a sample flow and can be modified.
1. Sales orders are entered and booked through Order Management. Reserving the order creates a high-level allocation in Process Inventory.
2. Before an order can be picked and shipped, it must be pick released. The Pick Release process can invoke automatic inventory allocation and creates a process move order.
3. Process move orders allow you to manually assign available Process Inventory to a move order or pick from a list of available lots. Lines that have been automatically allocated may be viewed and edited. Once an order line is allocated (detailed), it is pick confirmed.
4. Deliveries and trips are created in Shipping Execution. Containers can also be used.
5. Once you have verified the allocation of inventory to an order, it needs to be Pick Confirmed. This step can be automated or done through the process move orders form in Process Inventory. After the pick confirm, allocated inventory is marked as staged.
6. Ship confirm is the final process in Shipping Execution which records the actual shipped amounts and creates backorders, if necessary. At the completion of ship confirm, on hand inventory is decremented for the shipped quantity.
7. Auto Invoicing allows billing of confirmed shipments. This process can be setup to run automatically upon shipment of an order or be invoked manually. Invoicing and cash receipt are handled within Oracle Receivables.
8. The final step in the process is the running of the subsidiary ledger update to create the entries for Inventory and Cost of Goods Sold.
Diagrammatic Representation:
Order Management:
Table Relation Diagram
Image ppt page no:134
Flow How the Tables Effected
ENTERED:
NAV-> ORDERS. RETURNS -> SALES ORDERS
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = 56728
— HEADER_ID = 94100 (FLOW_STATUS_CODE = ENTERED, OPEN_FLAG=Y, BOOKED_FLAG=N)
SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID = 94187
— LINE_ID = 195947(FLOW_STATUS_CODE=ENTERED)
NOTE: – If U have set the auto schedule option your order should have a schedule date at this point.
NAV-> ORDERS. RETURNS -> SALES ORDERS
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = 56728
— HEADER_ID = 94100 (FLOW_STATUS_CODE = ENTERED, OPEN_FLAG=Y, BOOKED_FLAG=N)
SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID = 94187
— LINE_ID = 195947(FLOW_STATUS_CODE=ENTERED)
NOTE: – If U have set the auto schedule option your order should have a schedule date at this point.
BOOKED:
NAV-> ORDERS. RETURNS -> SALES ORDERS -> HIT BOOKED TAB
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = 56728
— HEADER_ID = 94100 (FLOW_STATUS_CODE = BOOKED, OPEN_FLAG=Y,BOOKED_FLAG=Y)
SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID = 94100
— LINE_ID = 195947(FLOW_STATUS_CODE=AWAITING_SHIPPING)
SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100
— (RELEASED_STATUS = R)
NAV-> ORDERS. RETURNS -> SALES ORDERS -> HIT BOOKED TAB
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = 56728
— HEADER_ID = 94100 (FLOW_STATUS_CODE = BOOKED, OPEN_FLAG=Y,BOOKED_FLAG=Y)
SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID = 94100
— LINE_ID = 195947(FLOW_STATUS_CODE=AWAITING_SHIPPING)
SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100
— (RELEASED_STATUS = R)
PICK RELEASED:
NAV-> SHIPPING -> RELEASE SALES ORDERS -> RELEASE SALES ORDERS
SELECT “BASE ON RULE” AS –> STANDARD
PROVIDE THE “ORDER NUMBER” THEN HIT “EXECUTE NOW”
HERE U CAN CHECK THROUGH THE FRONT END IN LINE LEVEL STATUS WILL BE “PICKED”.
SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100
— (RELEASED_STATUS = Y)
SELECT * FROM WSH_PICKING_BATCHES WHERE ORDER_HEADER_ID = 94100
SELECT * FROM WSH_NEW_DELIVERIES WHERE SOURCE_HEADER_ID = 94100
NAV-> SHIPPING -> RELEASE SALES ORDERS -> RELEASE SALES ORDERS
SELECT “BASE ON RULE” AS –> STANDARD
PROVIDE THE “ORDER NUMBER” THEN HIT “EXECUTE NOW”
HERE U CAN CHECK THROUGH THE FRONT END IN LINE LEVEL STATUS WILL BE “PICKED”.
SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100
— (RELEASED_STATUS = Y)
SELECT * FROM WSH_PICKING_BATCHES WHERE ORDER_HEADER_ID = 94100
SELECT * FROM WSH_NEW_DELIVERIES WHERE SOURCE_HEADER_ID = 94100
SHIP CONFIRMED:
NAV-> SHIPPING -> TRANSACTIONS
SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100
— (RELEASED_STATUS = C)
NAV-> SHIPPING -> TRANSACTIONS
SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100
— (RELEASED_STATUS = C)
EXAMPLE:
select
ooh.order_number,
ooh.ORDERED_DATE,
ooh.FLOW_STATUS_CODE,
ool.line_number,
msi.SEGMENT1,
msi.DESCRIPTION,
ool.ordered_quantity,
ool.UNIT_SELLING_price,
ool.UNIT_SELLING_price*ool.ordered_quantity total,
ooh.sold_to_org_id,
ooh.ship_to_org_id,
ooh.invoice_to_org_id,
ool.inventory_item_id
from
oe_order_headers_all ooh,
oe_order_lines_all ool,
hz_cust_accounts hca,
mtl_system_items msi
where
ooh.header_id=ool.header_id
and ooh.sold_to_org_id=hca.cust_account_id
and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID
and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID
and ooh.order_number=56308
order by ool.line_number
select
ooh.order_number,
ooh.ORDERED_DATE,
ooh.FLOW_STATUS_CODE,
ool.line_number,
msi.SEGMENT1,
msi.DESCRIPTION,
ool.ordered_quantity,
ool.UNIT_SELLING_price,
ool.UNIT_SELLING_price*ool.ordered_quantity total,
ooh.sold_to_org_id,
ooh.ship_to_org_id,
ooh.invoice_to_org_id,
ool.inventory_item_id
from
oe_order_headers_all ooh,
oe_order_lines_all ool,
hz_cust_accounts hca,
mtl_system_items msi
where
ooh.header_id=ool.header_id
and ooh.sold_to_org_id=hca.cust_account_id
and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID
and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID
and ooh.order_number=56308
order by ool.line_number
TCA – Trading Community Architecture
Parties are entities, of type Person, Organization, or Group, that can enter into business relationships.
A relationship represents the way two entities interact with each other, based on the role that each entity takes with respect to the other. For example, the employment relationship between a person and an organization is defined by the role of the person as
the employee and the organization as the employer.
Relation ship type
Each relationship phrase and role pair belongs to a relationship type, which categorizes the types of relationships that you can create.
Relationship Group
Relationship groups are used to determine which relationship roles and phrases are displayed in specific application user interfaces. Groups can also be used to categorize roles and phrases for other functional uses.
TCA Registry
The TCA Registry is the central repository of party and other information for all Oracle applications. The party information includes details about organizations and people, the relationships among the parties, and the places where the parties do business.
Hz_parties –
Party_id — This table mainly stores the party_id, party_number,
Party_type(Person/Organization) and the name(First Name/Last Name and
Party_name(first-last name together)) of the party/customer. The first name or last
name should be given to create a party.
Hz_cust_accounts/Hz_cust_accounts_all –
This has mainly the party_id and Cust_account_id — (called as Customer_id) Account_number — (called as Customer_number)
(The hz_cust_accounts_all(till version 11.5.9), is not populating the org_id column. So this table is anyway meaning less for these versions. Not sure though there is a set up related to it which can enable this field.)
This table can have multiple records pertaining to one party_id. This means a party can have multiple accounts. Ie; ,we can have multiple cust_account_ids created for a single party in hz_cust_account table. And also under each cust_account_id we can have multiple addresses,
To create another customer_account(differenct cust_account_id), select the customer with the Name from the standard customer creation search screen,(Take care not to select the customer from LOV, since it directly selects the cust_account_id/account_number and once you press OK, you get only that cust_account_id/account number and not all the customer accounts. So enter manuallythe customer name, press OK and this takes you to the screen,
Parties are entities, of type Person, Organization, or Group, that can enter into business relationships.
A relationship represents the way two entities interact with each other, based on the role that each entity takes with respect to the other. For example, the employment relationship between a person and an organization is defined by the role of the person as
the employee and the organization as the employer.
Relation ship type
Each relationship phrase and role pair belongs to a relationship type, which categorizes the types of relationships that you can create.
Relationship Group
Relationship groups are used to determine which relationship roles and phrases are displayed in specific application user interfaces. Groups can also be used to categorize roles and phrases for other functional uses.
TCA Registry
The TCA Registry is the central repository of party and other information for all Oracle applications. The party information includes details about organizations and people, the relationships among the parties, and the places where the parties do business.
Hz_parties –
Party_id — This table mainly stores the party_id, party_number,
Party_type(Person/Organization) and the name(First Name/Last Name and
Party_name(first-last name together)) of the party/customer. The first name or last
name should be given to create a party.
Hz_cust_accounts/Hz_cust_accounts_all –
This has mainly the party_id and Cust_account_id — (called as Customer_id) Account_number — (called as Customer_number)
(The hz_cust_accounts_all(till version 11.5.9), is not populating the org_id column. So this table is anyway meaning less for these versions. Not sure though there is a set up related to it which can enable this field.)
This table can have multiple records pertaining to one party_id. This means a party can have multiple accounts. Ie; ,we can have multiple cust_account_ids created for a single party in hz_cust_account table. And also under each cust_account_id we can have multiple addresses,
To create another customer_account(differenct cust_account_id), select the customer with the Name from the standard customer creation search screen,(Take care not to select the customer from LOV, since it directly selects the cust_account_id/account_number and once you press OK, you get only that cust_account_id/account number and not all the customer accounts. So enter manuallythe customer name, press OK and this takes you to the screen,
Select the top most records. Here we can see 4 records. 1st is the Main Line, which needs to be selected for creating new customer accounts for this customer. We can see that there are 2 different customer numbers(account number, in this screen cust_account_ids
won’t be shown) here(1441, and 1442). 1442 is repeating because that account is having 2 different sites(address sites).
Hz_cust_acct_sites_all –
HZ_CUST_ACCT_SITES_ALL stores information about customer sites. One customer account can have multiple sites. The physical address is maintained in HZ_LOCATIONS.
Gives you all the sites or address defined pertaining to an account. (although the cust_account_id is same, the org_id’s might be different.) [ If the records are related to an order, you can take the ship_to_org_id, which is nothing but the cust_account_id related to the Bill_to address and link it with the table, hz_cust_accounts (using cust_account_id) and then link it with the cust_account_id of
hz_cust_acct_sites_all table like this,
FROM OE_ORDER_HEADERS_ALL OH,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
hz_cust_site_uses_all HCSU
WHERE OH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS. CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCAS.BILL_TO_FLAG = ‘P’
won’t be shown) here(1441, and 1442). 1442 is repeating because that account is having 2 different sites(address sites).
Hz_cust_acct_sites_all –
HZ_CUST_ACCT_SITES_ALL stores information about customer sites. One customer account can have multiple sites. The physical address is maintained in HZ_LOCATIONS.
Gives you all the sites or address defined pertaining to an account. (although the cust_account_id is same, the org_id’s might be different.) [ If the records are related to an order, you can take the ship_to_org_id, which is nothing but the cust_account_id related to the Bill_to address and link it with the table, hz_cust_accounts (using cust_account_id) and then link it with the cust_account_id of
hz_cust_acct_sites_all table like this,
FROM OE_ORDER_HEADERS_ALL OH,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
hz_cust_site_uses_all HCSU
WHERE OH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS. CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCAS.BILL_TO_FLAG = ‘P’
Hz_cust_site_uses_all —
HZ_CUST_SITE_USES_ALL stores information about site uses or business purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table. Link this table with hz_cust_account_site_all with the field, CUST_ACCT_SITE_ID. Here is where the and each address can have one(only one primary) Bill_to or Ship_to address. The primary bill_to, ship_to will be the ones which are normally used for ‘site use’ level transactions. For one cust_account_id with different addresses,(see it in hz_cust_acct_sites_all table for these different address lines), we can make only one bill_to or ship_to as primary. If we get multiple records pertaining to an account, for a Bill_to, check for the field,BILL_TO_FLAG = ‘P’ (Means primary). IF we have multiple address/site for a particular cust_account_id, Only one bill_to address can be ‘P’, under a cust_account_id, although we can define multiple bill_to addresses(Note : one address/site can have only one bill_to)and all the transactions through the system will be done with the primary bill_to address.
HZ_CUST_SITE_USES_ALL stores information about site uses or business purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table. Link this table with hz_cust_account_site_all with the field, CUST_ACCT_SITE_ID. Here is where the and each address can have one(only one primary) Bill_to or Ship_to address. The primary bill_to, ship_to will be the ones which are normally used for ‘site use’ level transactions. For one cust_account_id with different addresses,(see it in hz_cust_acct_sites_all table for these different address lines), we can make only one bill_to or ship_to as primary. If we get multiple records pertaining to an account, for a Bill_to, check for the field,BILL_TO_FLAG = ‘P’ (Means primary). IF we have multiple address/site for a particular cust_account_id, Only one bill_to address can be ‘P’, under a cust_account_id, although we can define multiple bill_to addresses(Note : one address/site can have only one bill_to)and all the transactions through the system will be done with the primary bill_to address.
Hz_contact_points –
This table stores the contact details of the customer. The contact details are stored against a site, if owner_table_name is equated against the hz_party_sites table. If it is equated against the hz_parties then the contacts are against the customer not against
the customer site.
This table stores the contact details of the customer. The contact details are stored against a site, if owner_table_name is equated against the hz_party_sites table. If it is equated against the hz_parties then the contacts are against the customer not against
the customer site.
If a contact point is created against a customer site(address).
SELECT LTRIM(hcp.phone_country_code||’-‘||hcp.phone_area_code||’-
‘||hcp.phone_number,’-‘) Home_Phone, hcp.PRIMARY_BY_PURPOSE,hcas.org_id,
hcas.party_site_id,hca.cust_account_id,hcas.cust_acct_site_id,
hcp.CONTACT_POINT_ID,hcp.CONTACT_POINT_TYPE,hcp.PHONE_LINE_TYPE,
hcp.OBJECT_VERSION_NUMBER
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_contact_points hcp
WHERE hp.party_id = hps.party_id
AND hca.party_id = hp.party_id
AND hp.party_type IN (‘ORGANIZATION’, ‘PERSON’)
AND hp.status = ‘A’
AND hcas.party_site_id = hps.party_site_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcp.contact_point_type = ‘PHONE’
AND (hcp.OWNER_TABLE_NAME = ‘HZ_PARTY_SITES’ AND hps.party_site_id =
hcp.owner_table_id)
AND hcp.phone_line_type = ‘TELHOME’
AND hcp.status = ‘A’
SELECT LTRIM(hcp.phone_country_code||’-‘||hcp.phone_area_code||’-
‘||hcp.phone_number,’-‘) Home_Phone, hcp.PRIMARY_BY_PURPOSE,hcas.org_id,
hcas.party_site_id,hca.cust_account_id,hcas.cust_acct_site_id,
hcp.CONTACT_POINT_ID,hcp.CONTACT_POINT_TYPE,hcp.PHONE_LINE_TYPE,
hcp.OBJECT_VERSION_NUMBER
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_contact_points hcp
WHERE hp.party_id = hps.party_id
AND hca.party_id = hp.party_id
AND hp.party_type IN (‘ORGANIZATION’, ‘PERSON’)
AND hp.status = ‘A’
AND hcas.party_site_id = hps.party_site_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcp.contact_point_type = ‘PHONE’
AND (hcp.OWNER_TABLE_NAME = ‘HZ_PARTY_SITES’ AND hps.party_site_id =
hcp.owner_table_id)
AND hcp.phone_line_type = ‘TELHOME’
AND hcp.status = ‘A’
If a contact point is create against a customer.
SELECT LTRIM(hcp.phone_country_code||’-‘||hcp.phone_area_code||’-
‘||hcp.phone_number,’-‘) Home_Phone, hcp.PRIMARY_BY_PURPOSE,
hca.cust_account_id,hcp.CONTACT_POINT_ID,hcp.CONTACT_POINT_TYPE,hcp.PHONE_L
INE_TYPE,hcp.OBJECT_VERSION_NUMBER
FROM hz_parties hp,
hz_cust_accounts hca,
hz_contact_points hcp
WHERE hca.party_id = hp.party_id
AND hp.party_type IN (‘ORGANIZATION’, ‘PERSON’)
AND hp.status = ‘A’
AND hcp.contact_point_type = ‘PHONE’
AND (hcp.OWNER_TABLE_NAME = ‘HZ_PARTIES’ AND hp.party_id =
hcp.owner_table_id)
AND hcp.status = ‘A’
SELECT LTRIM(hcp.phone_country_code||’-‘||hcp.phone_area_code||’-
‘||hcp.phone_number,’-‘) Home_Phone, hcp.PRIMARY_BY_PURPOSE,
hca.cust_account_id,hcp.CONTACT_POINT_ID,hcp.CONTACT_POINT_TYPE,hcp.PHONE_L
INE_TYPE,hcp.OBJECT_VERSION_NUMBER
FROM hz_parties hp,
hz_cust_accounts hca,
hz_contact_points hcp
WHERE hca.party_id = hp.party_id
AND hp.party_type IN (‘ORGANIZATION’, ‘PERSON’)
AND hp.status = ‘A’
AND hcp.contact_point_type = ‘PHONE’
AND (hcp.OWNER_TABLE_NAME = ‘HZ_PARTIES’ AND hp.party_id =
hcp.owner_table_id)
AND hcp.status = ‘A’
HZ_customer_profiles —
There will normally be a default profile attached against a customer as soon as the Since the default profile is attached against the customer(and not against the site_use level), when you query the hz_customer_profiles tables we can see that that site_use_id
for this particular profile would be null.
If the profile is created against the customer site_use level, then the corresponding site_use_id will be populated for this customer profile record in the hz_customer_profiles table.
See the below query for reference,
[ select hcp.CUST_ACCOUNT_PROFILE_ID,
hcp.OBJECT_VERSION_NUMBER,
hcp.cust_account_id,
hcp.ATTRIBUTE1 LTV_Veh_sales,
hcp.ATTRIBUTE2 LTV_Number_Of_Veh,
hcp.ATTRIBUTE3 LTV_Service_Sales,
hcp.attribute4 LTV_Service_In,
hcp.attribute5 Cust_Cate_RFM_Veh,
hcp.attribute6 Cust_Cate_RFM_Service_In,
hcp.ATTRIBUTE7 Customer_Remark,
hcp.ATTRIBUTE8 Latest_Vehicle_Sales_Date,
hcp.ATTRIBUTE9 Latest_Service_In_date,
hcp.ATTRIBUTE10 Latest_Contact_Date,
hcp.ATTRIBUTE11 Expected_Contact_Date,
hcp.ATTRIBUTE12 Expected_Sales_Date,
hcp.ATTRIBUTE13 Expected_service_Date,
hcp.ATTRIBUTE14 Hobby1,
hcp.ATTRIBUTE15 Hobby2
FROM
hz_customer_profiles hcp, hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu where hcas.cust_account_id = hcp.cust_account_id and
hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hcp.site_use_id = hcsu.site_use_id
and hcp.status = ‘A’ ]
There will normally be a default profile attached against a customer as soon as the Since the default profile is attached against the customer(and not against the site_use level), when you query the hz_customer_profiles tables we can see that that site_use_id
for this particular profile would be null.
If the profile is created against the customer site_use level, then the corresponding site_use_id will be populated for this customer profile record in the hz_customer_profiles table.
See the below query for reference,
[ select hcp.CUST_ACCOUNT_PROFILE_ID,
hcp.OBJECT_VERSION_NUMBER,
hcp.cust_account_id,
hcp.ATTRIBUTE1 LTV_Veh_sales,
hcp.ATTRIBUTE2 LTV_Number_Of_Veh,
hcp.ATTRIBUTE3 LTV_Service_Sales,
hcp.attribute4 LTV_Service_In,
hcp.attribute5 Cust_Cate_RFM_Veh,
hcp.attribute6 Cust_Cate_RFM_Service_In,
hcp.ATTRIBUTE7 Customer_Remark,
hcp.ATTRIBUTE8 Latest_Vehicle_Sales_Date,
hcp.ATTRIBUTE9 Latest_Service_In_date,
hcp.ATTRIBUTE10 Latest_Contact_Date,
hcp.ATTRIBUTE11 Expected_Contact_Date,
hcp.ATTRIBUTE12 Expected_Sales_Date,
hcp.ATTRIBUTE13 Expected_service_Date,
hcp.ATTRIBUTE14 Hobby1,
hcp.ATTRIBUTE15 Hobby2
FROM
hz_customer_profiles hcp, hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu where hcas.cust_account_id = hcp.cust_account_id and
hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hcp.site_use_id = hcsu.site_use_id
and hcp.status = ‘A’ ]
No comments:
Post a Comment