1) Import 11g data dump into 10g
You can use Oracle data dump to export data dump from 11g server, and import into 10g using the data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version
So, If your database is 11g and you want to export 11g and import into 10g
from 11g db ,
$ expdp Test_schema_name/passs directory=datapump schemas=Test_schema_name Version=10.2.0.4.0.
Once the export is done, you do the regular import from 10g server.
2) Import multiple dump files
If the size of the dump file is large, usually they will be split into smaller chunks for easier ftp upload/download.
If you are trying to import a dump file that is split into many chunks, then you need to modify the DUMPFILE paratermeter to include %U
Ex: If the dump files are named EXP_PROD_1.dmp, EXP_PROD_2.dmp etc , then DUMPFILE=EXP_PROD_%U.DMP
3) How to kill data pump jobs
When you import or export using data pump impdp or expdp commands, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too
Following sql will give you the list of data pump jobs
select * from dba_datapump_jobs
If you want to kill your impdp or expdp
1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y
If by mistake, you closed the window and your import/export job is still running,
1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job
3) Once you are attached to job, Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs
4) REUSE_DUMPFILE :( Overwrite existing dumpfile)
This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.
$ expdp scott/tiger directory=exp_dir dumpfile = x.dmp table s= example reuse_dumpfiles = y
Showing posts with label base tables. Show all posts
Showing posts with label base tables. Show all posts
Base Tables of O2C Process:
When you entered the Order and Booked the Order following table will store the Information:
SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = 55950
AND HEADER_ID = 82465
SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = 82465
AND LINE_ID IN (161391, 161393)
SELECT * FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID = 82465
AND SOURCE_LINE_ID in (161391, 161393)
Ø Release Status is ‘R’ (Ready to Release)
SELECT * FROM WSH_DELIVERY_ASSIGNMENTS
WHERE DELIVERY_DETAIL_ID IN (179553,179554)
Ø When u Create Delivery Details that time this table will populate the record.
Ø AND DELIVERY_ID COLUMN ALSO WILL UPDATED IN WSH_DELIVERY_ASSIGNMENTS TABLE
SELECT * FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID IN (48152,48153)
After “Launch Pick Release” Following table will populate:
SELECT * FROM WSH_PICKING_BATCHES
WHERE BATCH_ID IN (33867,33868)
Move Order Number = 33868
SELECT * FROM MTL_TXN_REQUEST_HEADERS
WHERE REQUEST_NUMBER = 34003
Ø Here Header_id is Batch Number
SELECT * FROM MTL_TXN_REQUEST_LINES
WHERE TXN_SOURCE_LINE_ID = 161391
Ø Here TXN_SOURCE_LINE_ID is the Line_ID of the OE_ORDER_LINES_TABLES.
After Ship Confirm Following table will populate:
SELECT * FROM MTL_SALES_ORDERS
WHERE SEGMENT1 = 55950
AND SALES_ORDER_ID = 42058
SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE INVENTORY_ITEM_ID = 149
AND TRANSACTION_REFERENCE = '82465'
AND TRANSACTION_SOURCE_ID = 42058
Ø Where TRANSACTION_REFERENCE is storing the HEADER_ID.
SELECT * FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID IN (48152)
select * from MTL_ONHAND_QUANTITIES
where inventory_item_id = 149
After Running the “Workflow Background Process” Programme Invoice will Generate and that time following table will populate:
SELECT * FROM RA_CUSTOMER_TRX_ALL
WHERE INTERFACE_HEADER_ATTRIBUTE1 = '55950'
Ø Here INTERFACE_HEADER_ATTRIBUTE1 is the Order_Number.
Ø And TRX_COLUMN is the Invoice Number.
SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = 118416
Ø Here INTERFACE_LINE_ATTRIBUTE6 Column is the LINE_ID.
Ø SALES_ORDER Column is also there which is storing ORDER_NUMBER.
SELECT * FROM AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_ID = 5093
and CUSTOMER_TRX_ID = 118416
Ø To get the Outstanding of the Customer
After Transfer into the GL tables through “General Ledger Transfer Program” concurrent Programe:
SELECT * FROM GL_JE_BATCHES
WHERE NAME = 'AR 26137 Receivables 2202288: A 26137'
SELECT * FROM GL_JE_HEADERS
WHERE JE_BATCH_ID = '72750'
AND JE_HEADER_ID = 62120
SELECT * FROM GL_JE_LINES
WHERE JE_HEADER_ID = 62120
--AND REFERENCE_5 = 10016902
Here Reference_5 is the Invoice Number.
Receipt Transaction :
SELECT * FROM AR_CASH_RECEIPTS_ALL
WHERE RECEIPT_NUMBER = 'NKREC_240105'
Ø Before Adjustment STATUS is UNAPP Or UNID but Once u Applied with Invoice It will change into “APP”.
SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE CASH_RECEIPT_ID = 21116
Ø In this Table APPLIED_CUSTOMER_TRX_ID store the CUSTOMER_TRX_ID of the RA_CUSTOMER_TRX_ALL table.
Order Type is Return Only:
SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = 55987
SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = 82634
Ø LINE STATUS IS “AWAITING_RETURNS”.
Ø Order Line Type should be “Return (Receipt)”.
SELECT * FROM RCV_SHIPMENT_HEADERS
WHERE RECEIPT_NUM = 7607
AND SHIPMENT_HEADER_ID = 30539
SELECT * FROM RCV_SHIPMENT_LINES
WHERE SHIPMENT_HEADER_ID = 30539
SELECT * FROM RCV_TRANSACTIONS
WHERE SHIPMENT_HEADER_ID = 30539
Ø IN RCV_SHIPMENT_LINES Table OE_ORDER_HEADER_ID AND OE_ORDER_LINE_ID Column is Link with OE_ORDER_HEADER AND OE_ORDER_LINE Tables.
Ø After Receiving the Material at “Receiving Stage” at that time Order Line Status will be “Awaiting Return Disposition”.
Ø After Deliver the Material to “Inventory” at that time Order Line Status will be “Returned” and One More Line will be Created with “Remaining Quantity”
For Example: If your Order line quantity is 3 and you have received 1 quantity than one More line will be created with 2 quantity in OE_OREDER_LINES table with status “AWAITING_RETURNS”
Base Tables of List of LOV in Sales Order Screen:
Customer Name:
PARTY_NAME Column of HZ_PARTIES table.
Customer Number:
ACCOUNT_NUMBER Column of HZ_CUST_ACCOUNTS table.
Ø Where PARTY_ID Column is the Link between HZ_PARTIES and HZ_CUST_ACCOUNTS tables.
Customer Contact:
FIRST_NAMELAST_NAME Column of AR_CONTACTS_V view.
Bill To and Ship to Locations:
CUST_ACCT_SITE_ID Column of HZ_CUST_SITE_USES_ALL table.
Order Type:
Name and Description Column of OE_TRANSACTION_TYPES_V View
To Get the Payment Terms:
SELECT * FROM RA_TERMS
Look Up Values:
Ø REQUEST_DATE_TYPE For Line Set
Ø SALES_CHANNEL
Ø FREIGHT_TERMS
Ø SHIPMENT_PRIORITY
Ø PAYMENT TYPE
To Get the Shipping Method:
SELECT * FROM WSH_CARRIER_SERVICES
SELECT * FROM WSH_ORG_CARRIER_SERVICES
WHERE ORGANIZATION_ID = '207'
To get the FOB:
Value is storing into the AR_LOOKUP where LOOKUP_TYPE = ‘FOB’
Drop Shipment Process:
Ø Once you booked the Order with type “Mixed” and line source type is “External” in “Shipping” tab that time the line status will be “Booked” in both the levels Header as well as line.
Ø After booked the Order we have to do “Purchase Release” or run the “Workflow Background Process”.
Workflow back ground process transfer the Sales Order Data into the PO_REQUISITIONS_INTERFACE_ALL Interface table to create the Purchase Requisition.
Once the Data is populated in Interface table then Run “Requisition Import” program to transfer the data into the base tables.
After that Line Status will be “Awaiting Receipt”.
Purchase Requisition Number can see at Line level in Additional Information option under that “Drop Ship” tab.
Link between Sales Order, Purchase Requisition and Purchase Order we can fine in
SELECT * FROM OE_DROP_SHIP_SOURCES
Here you will get ORDER_HEADER_ID, REQUISITION_HEADER_ID, REQUISITION_LINE_ID, PO_HEADER_ID, and PO_LINE_ID
Ø After Creating the Requisition Run the Auto creates option to create the Purchase Order against that purchases requisition.
Ø Receipt the Material :
If you received partially then Order Line status will be remain same like “Awaiting Receipt” Once you completely receipt the material then only status will changed into “Shipped”.
Ø Then after one Purchase Invoice will be created in Payables Module against the Purchase order and once sales invoice will be created in Receivables module against the sales order.
Thanks
http://prasanthapps.blogspot.com/
SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = 55950
AND HEADER_ID = 82465
SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = 82465
AND LINE_ID IN (161391, 161393)
SELECT * FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID = 82465
AND SOURCE_LINE_ID in (161391, 161393)
Ø Release Status is ‘R’ (Ready to Release)
SELECT * FROM WSH_DELIVERY_ASSIGNMENTS
WHERE DELIVERY_DETAIL_ID IN (179553,179554)
Ø When u Create Delivery Details that time this table will populate the record.
Ø AND DELIVERY_ID COLUMN ALSO WILL UPDATED IN WSH_DELIVERY_ASSIGNMENTS TABLE
SELECT * FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID IN (48152,48153)
After “Launch Pick Release” Following table will populate:
SELECT * FROM WSH_PICKING_BATCHES
WHERE BATCH_ID IN (33867,33868)
Move Order Number = 33868
SELECT * FROM MTL_TXN_REQUEST_HEADERS
WHERE REQUEST_NUMBER = 34003
Ø Here Header_id is Batch Number
SELECT * FROM MTL_TXN_REQUEST_LINES
WHERE TXN_SOURCE_LINE_ID = 161391
Ø Here TXN_SOURCE_LINE_ID is the Line_ID of the OE_ORDER_LINES_TABLES.
After Ship Confirm Following table will populate:
SELECT * FROM MTL_SALES_ORDERS
WHERE SEGMENT1 = 55950
AND SALES_ORDER_ID = 42058
SELECT * FROM MTL_MATERIAL_TRANSACTIONS
WHERE INVENTORY_ITEM_ID = 149
AND TRANSACTION_REFERENCE = '82465'
AND TRANSACTION_SOURCE_ID = 42058
Ø Where TRANSACTION_REFERENCE is storing the HEADER_ID.
SELECT * FROM WSH_DELIVERY_LEGS
WHERE DELIVERY_ID IN (48152)
select * from MTL_ONHAND_QUANTITIES
where inventory_item_id = 149
After Running the “Workflow Background Process” Programme Invoice will Generate and that time following table will populate:
SELECT * FROM RA_CUSTOMER_TRX_ALL
WHERE INTERFACE_HEADER_ATTRIBUTE1 = '55950'
Ø Here INTERFACE_HEADER_ATTRIBUTE1 is the Order_Number.
Ø And TRX_COLUMN is the Invoice Number.
SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID = 118416
Ø Here INTERFACE_LINE_ATTRIBUTE6 Column is the LINE_ID.
Ø SALES_ORDER Column is also there which is storing ORDER_NUMBER.
SELECT * FROM AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_ID = 5093
and CUSTOMER_TRX_ID = 118416
Ø To get the Outstanding of the Customer
After Transfer into the GL tables through “General Ledger Transfer Program” concurrent Programe:
SELECT * FROM GL_JE_BATCHES
WHERE NAME = 'AR 26137 Receivables 2202288: A 26137'
SELECT * FROM GL_JE_HEADERS
WHERE JE_BATCH_ID = '72750'
AND JE_HEADER_ID = 62120
SELECT * FROM GL_JE_LINES
WHERE JE_HEADER_ID = 62120
--AND REFERENCE_5 = 10016902
Here Reference_5 is the Invoice Number.
Receipt Transaction :
SELECT * FROM AR_CASH_RECEIPTS_ALL
WHERE RECEIPT_NUMBER = 'NKREC_240105'
Ø Before Adjustment STATUS is UNAPP Or UNID but Once u Applied with Invoice It will change into “APP”.
SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE CASH_RECEIPT_ID = 21116
Ø In this Table APPLIED_CUSTOMER_TRX_ID store the CUSTOMER_TRX_ID of the RA_CUSTOMER_TRX_ALL table.
Order Type is Return Only:
SELECT * FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = 55987
SELECT * FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = 82634
Ø LINE STATUS IS “AWAITING_RETURNS”.
Ø Order Line Type should be “Return (Receipt)”.
SELECT * FROM RCV_SHIPMENT_HEADERS
WHERE RECEIPT_NUM = 7607
AND SHIPMENT_HEADER_ID = 30539
SELECT * FROM RCV_SHIPMENT_LINES
WHERE SHIPMENT_HEADER_ID = 30539
SELECT * FROM RCV_TRANSACTIONS
WHERE SHIPMENT_HEADER_ID = 30539
Ø IN RCV_SHIPMENT_LINES Table OE_ORDER_HEADER_ID AND OE_ORDER_LINE_ID Column is Link with OE_ORDER_HEADER AND OE_ORDER_LINE Tables.
Ø After Receiving the Material at “Receiving Stage” at that time Order Line Status will be “Awaiting Return Disposition”.
Ø After Deliver the Material to “Inventory” at that time Order Line Status will be “Returned” and One More Line will be Created with “Remaining Quantity”
For Example: If your Order line quantity is 3 and you have received 1 quantity than one More line will be created with 2 quantity in OE_OREDER_LINES table with status “AWAITING_RETURNS”
Base Tables of List of LOV in Sales Order Screen:
Customer Name:
PARTY_NAME Column of HZ_PARTIES table.
Customer Number:
ACCOUNT_NUMBER Column of HZ_CUST_ACCOUNTS table.
Ø Where PARTY_ID Column is the Link between HZ_PARTIES and HZ_CUST_ACCOUNTS tables.
Customer Contact:
FIRST_NAMELAST_NAME Column of AR_CONTACTS_V view.
Bill To and Ship to Locations:
CUST_ACCT_SITE_ID Column of HZ_CUST_SITE_USES_ALL table.
Order Type:
Name and Description Column of OE_TRANSACTION_TYPES_V View
To Get the Payment Terms:
SELECT * FROM RA_TERMS
Look Up Values:
Ø REQUEST_DATE_TYPE For Line Set
Ø SALES_CHANNEL
Ø FREIGHT_TERMS
Ø SHIPMENT_PRIORITY
Ø PAYMENT TYPE
To Get the Shipping Method:
SELECT * FROM WSH_CARRIER_SERVICES
SELECT * FROM WSH_ORG_CARRIER_SERVICES
WHERE ORGANIZATION_ID = '207'
To get the FOB:
Value is storing into the AR_LOOKUP where LOOKUP_TYPE = ‘FOB’
Drop Shipment Process:
Ø Once you booked the Order with type “Mixed” and line source type is “External” in “Shipping” tab that time the line status will be “Booked” in both the levels Header as well as line.
Ø After booked the Order we have to do “Purchase Release” or run the “Workflow Background Process”.
Workflow back ground process transfer the Sales Order Data into the PO_REQUISITIONS_INTERFACE_ALL Interface table to create the Purchase Requisition.
Once the Data is populated in Interface table then Run “Requisition Import” program to transfer the data into the base tables.
After that Line Status will be “Awaiting Receipt”.
Purchase Requisition Number can see at Line level in Additional Information option under that “Drop Ship” tab.
Link between Sales Order, Purchase Requisition and Purchase Order we can fine in
SELECT * FROM OE_DROP_SHIP_SOURCES
Here you will get ORDER_HEADER_ID, REQUISITION_HEADER_ID, REQUISITION_LINE_ID, PO_HEADER_ID, and PO_LINE_ID
Ø After Creating the Requisition Run the Auto creates option to create the Purchase Order against that purchases requisition.
Ø Receipt the Material :
If you received partially then Order Line status will be remain same like “Awaiting Receipt” Once you completely receipt the material then only status will changed into “Shipped”.
Ø Then after one Purchase Invoice will be created in Payables Module against the Purchase order and once sales invoice will be created in Receivables module against the sales order.
Thanks
http://prasanthapps.blogspot.com/
Subscribe to:
Posts (Atom)